P_PDOCITEMWITHOUTACCTASSGMT

@AbapCatalog.sqlViewName: 'PPOIWITHOUTASGMT'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.preserveKey:true
//@EndUserText.label: 'Purchase Order Item Account Assignment'

@VDM.viewType : #CONSUMPTION
@VDM.private: true
@AccessControl.personalData.blocking: #NOT_REQUIRED

define view P_PDOCITEMWITHOUTACCTASSGMT

  with parameters
    P_DisplayCurrency : displaycurrency,
    P_StartDate       : bedat,
    P_EndDate         : bedat

  as select from P_PURORDSCHDLNWITHOUTACCTASSMT ( P_StartDate  : $parameters.P_StartDate,
                                P_EndDate   : $parameters.P_EndDate ) as PurchaseOrderItem


{
  key PurchaseOrder,
  key PurchaseOrderItem.PurchaseOrderItem                                                as PurchaseOrderItem,
      cast ('X' as migration_completed)                                                  as PurgDocMigrtnIsCmpltdForAnlyts,
      PurchaseOrderItem.PurchaseRequisition,
      PurchaseOrderItem.PurchaseRequisitionItem,
      PurchaseOrderItem.Supplier,
      PurchaseOrderItem.PurchasingOrganization,
      PurchaseOrderItem.PurchasingGroup,
      PurchaseOrderItem.CompanyCode,
      PurchaseOrderItem.PurgDocHdrCompanyCode,
      PurchaseOrderItem.PurchaseOrderType,
      IsCompletelyDelivered,

      PurchaseOrderItem.PurchaseOrderDate,
      PurchaseOrderItem.CreatedByUser,
      PurchaseOrderItem.DocumentCurrency                                                 as Currency,

      PurchaseOrderItem.MaterialGroup,
      PurchaseOrderItem.Material,
      PurchaseOrderItem.Plant,
      PurchaseOrderItem.PurchaseOrderItemCategory,
      PurchaseOrderItem.PurchaseOrderCategory,

      PurchaseOrderItem.PurchaseContract,
      PurchaseOrderItem.PurchaseContractItem,

      PurchaseOrderItem.ProductType,
      PurchaseOrderItem.ServicePerformer,

      PurchaseOrderItem.MultipleAcctAssgmtDistribution,
      PurchaseOrderItem.AccountAssignmentCategory,
      PurchaseOrderItem.OrderQuantity,
      PurchaseOrderItem.NetPriceQuantity,
      NetAmountInReportingCurrency,
      NetAmountInReportingCurrency2,
      cast(NetAmount as abap.curr( 21, 5 ) )                                             as NetAmount,
      cast( case when PurchaseOrderItem.IsReturnsItem = 'X' then -1 else 1 end as int2 ) as IsReturnsItem,
      cast(NetPriceAmount as abap.curr( 21, 5 ) )                                        as NetPriceAmount,
      PurchasingDocumentDeletionCode,
      ReleaseIsNotCompleted,
      PurchasingCompletenessStatus,

      @Semantics.unitOfMeasure: true
      PurchaseOrderItem.PurchaseOrderQuantityUnit,
      AnalyticalReportingCurrency,
      AnalyticalReportingCurrency2

}


// if display currency is not same as the either of the two reporting currency


union all select from I_PurchaseOrder     as PurchaseOrder

  inner join          I_PurchaseOrderItem as PurchaseOrderItem on PurchaseOrder.PurchaseOrder = PurchaseOrderItem.PurchaseOrder

association [0..1] to I_PurchasingDocumentType as _PurchaseOrderType on  _PurchaseOrderType.PurchasingDocumentCategory = 'F'
                                                                     and _PurchaseOrderType.PurchasingDocumentType     = $projection.PurchaseOrderType
{

  key PurchaseOrder.PurchaseOrder,
  key PurchaseOrderItem                                                as PurchaseOrderItem,
      cast ('' as migration_completed)                                 as PurgDocMigrtnIsCmpltdForAnlyts,

      PurchaseRequisition,
      PurchaseRequisitionItem,

      Supplier,
      PurchasingOrganization,
      PurchasingGroup,
      PurchaseOrderItem.CompanyCode,
      PurchaseOrder.CompanyCode                                        as PurgDocHdrCompanyCode,
      @ObjectModel.foreignKey.association: '_PurchaseOrderType'
      PurchaseOrderType,
      IsCompletelyDelivered,

      PurchaseOrderDate,
      CreatedByUser,


      PurchaseOrder.DocumentCurrency                                   as Currency,

      MaterialGroup,
      Material,
      Plant,
      PurchaseOrderItemCategory,
      PurchaseOrderCategory,

      PurchaseOrder.PurchaseContract,
      PurchaseContractItem,

      ProductType,
      ServicePerformer,

      MultipleAcctAssgmtDistribution,
      AccountAssignmentCategory,
      OrderQuantity,
      NetPriceQuantity,
      cast ( 0 as purchase_order_net_amount )                          as NetAmountInReportingCurrency,
      cast ( 0 as purchase_order_net_amount )                          as NetAmountInReportingCurrency2,


      NetAmount,

      cast( case when IsReturnsItem = 'X' then -1 else 1 end as int2 ) as IsReturnsItem,


      NetPriceAmount,
      PurchaseOrder.PurchasingDocumentDeletionCode,
      ReleaseIsNotCompleted,
      PurchasingCompletenessStatus,

      @Semantics.unitOfMeasure: true
      PurchaseOrderQuantityUnit,
      cast ('' as waers_repcurr1)                                      as AnalyticalReportingCurrency,
      cast ('' as waers_repcurr1)                                      as AnalyticalReportingCurrency2

}
where
        PurchaseOrder.PurchasingDocumentDeletionCode     = ''
  and   PurchaseOrder.ReleaseIsNotCompleted              = ''
  and   PurchaseOrder.PurchasingCompletenessStatus       = ''
  and   PurchaseOrderItem.PurchasingDocumentDeletionCode = ''
  and   PurchaseOrder.DocumentCurrency != ''
  and   PurchaseOrder.PurchaseOrderDate                  <= $parameters.P_EndDate
  and   PurchaseOrder.PurchaseOrderDate                  >= $parameters.P_StartDate
  and   not(
      IsStatisticalItem                                  = 'X'
      and Material                                       = ''
    )
  and   SupplyingPlant                                   is initial
  and   MultipleAcctAssgmtDistribution                   = ''
//  and ( AccountAssignmentCategory is initial or AccountAssignmentCategory = 'U' ) // This CDS return Purchase Orders without account assignments

/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_PURCHASEORDER",
"I_PURCHASEORDERITEM",
"P_PURORDSCHDLNWITHOUTACCTASSMT"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/