P_PurOrdItmAcctAssgmtDualStack

@AbapCatalog.sqlViewName: 'PMMPOIDSTACK'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Purchase order item from Dual stack'

@VDM.viewType : #CONSUMPTION
@VDM.private: true
@AccessControl.personalData.blocking: #NOT_REQUIRED
define view P_PurOrdItmAcctAssgmtDualStack
  with parameters
    P_StartDate : bedat,
    P_EndDate   : bedat
  //  as

  as select from I_PurgAnalyticsConfiguration as MigrationStatus

    inner join   I_PurOrdScheduleLineEnhanced on MigrationStatus.IsIntrastatReportingRelevant = 'X'

{
  key  PurchaseOrder                                      as PurchaseOrder,
  key  cast( PurchaseOrderItem as vdm_purchaseorderitem ) as PurchaseOrderItem,
       PurchaseRequisition,
       PurchaseRequisitionItem,
       Supplier,
       PurchasingOrganization,
       PurchasingGroup,
       CompanyCode,
       PurchaseOrderType,
       PurchaseOrderDate,
       CreatedByUser,
       DocumentCurrency                                   as Currency,
       MaterialGroup,
       Material,
       Plant,
       PurchaseOrderItemCategory,
       PurchaseOrderCategory,
       PurchaseContract,
       PurchaseContractItem,
       ProductTypeCode                                    as ProductType,
       ServicePerformer,
       MultipleAcctAssgmtDistribution,
       AccountAssignmentCategory,
       PurchaseOrderQuantityUnit,
       DocumentCurrency,
       cast(NetAmount as abap.curr( 21, 5 ) )             as NetAmount,
       NetAmountInReportingCurrency,
       NetAmountInReportingCurrency2,
       NetAmountInCoCodeCurrency,
       cast(NetPriceAmount as abap.curr( 21, 5 ) )        as NetPriceAmount,
       NetPriceInReportingCurrency,
       NetPriceInReportingCurrency2,
       NetPriceInCompanyCodeCrcy,
       IsReturnsItem,
       PurchasingDocumentDeletionCode,
       ReleaseIsNotCompleted,
       PurchasingCompletenessStatus,
       OrderQuantity,
       AnalyticalReportingCurrency,
       AnalyticalReportingCurrency2,
       _Plant

}
where
      PurgDocumentItemDeletionCode   = ''
  and ReleaseIsNotCompleted          = ''
  and PurchasingCompletenessStatus   = ''
  and PurchasingDocumentDeletionCode = ''
  and DocumentCurrency != ''
  and PurchaseOrderDate              <= $parameters.P_EndDate
  and PurchaseOrderDate              >= $parameters.P_StartDate

group by
  PurchaseOrder,
  PurchaseOrderItem,
  PurchaseRequisition,
  PurchaseRequisitionItem,
  PurchaseOrderDate,
  Supplier,
  PurchasingOrganization,
  PurchasingGroup,
  CompanyCode,
  PurchaseOrderType,
  PurchaseOrderDate,
  CreatedByUser,
  DocumentCurrency,
  MaterialGroup,
  Material,
  Plant,
  PurchaseOrderItemCategory,
  PurchaseOrderCategory,
  PurchaseContract,
  PurchaseContractItem,
  ProductTypeCode,
  ServicePerformer,
  MultipleAcctAssgmtDistribution,
  AccountAssignmentCategory,
  PurchaseOrderQuantityUnit,
  DocumentCurrency,
  NetAmount,
  NetAmountInReportingCurrency,
  NetAmountInReportingCurrency2,
  NetAmountInCoCodeCurrency,
  NetPriceAmount,
  NetPriceInReportingCurrency,
  NetPriceInReportingCurrency2,
  NetPriceInCompanyCodeCrcy,
  IsReturnsItem,
  PurchasingDocumentDeletionCode,
  ReleaseIsNotCompleted,
  PurchasingCompletenessStatus,
  OrderQuantity,
  AnalyticalReportingCurrency,
  AnalyticalReportingCurrency2

union all select from I_PurgAnalyticsConfiguration as MigrationStatus

  inner join          I_PurchaseOrderItem          as PurchaseOrderItem on MigrationStatus.IsIntrastatReportingRelevant = ''

{
  key PurchaseOrder,
  key cast( PurchaseOrderItem.PurchaseOrderItem as vdm_purchaseorderitem ) as PurchaseOrderItem,
      PurchaseOrderItem.PurchaseRequisition,
      PurchaseOrderItem.PurchaseRequisitionItem,
      PurchaseOrderItem._PurchaseOrder.Supplier,
      PurchaseOrderItem._PurchaseOrder.PurchasingOrganization,
      PurchaseOrderItem._PurchaseOrder.PurchasingGroup,
      PurchaseOrderItem._PurchaseOrder.CompanyCode,
      PurchaseOrderItem._PurchaseOrder.PurchaseOrderType,
      PurchaseOrderItem._PurchaseOrder.PurchaseOrderDate,
      PurchaseOrderItem._PurchaseOrder.CreatedByUser,
      PurchaseOrderItem._PurchaseOrder.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.PurchaseOrderQuantityUnit,
      PurchaseOrderItem._PurchaseOrder.DocumentCurrency,
      PurchaseOrderItem.NetAmount,
      cast ( 0 as obmng_repcurr1 )                                         as NetAmountInReportingCurrency, // Open Quantity Amount in Reporting Currency 1,

      cast ( 0 as obmng_repcurr2 )                                         as NetAmountInReportingCurrency2, // Open Quantity Amount in Reporting Currency 2

      cast ( 0 as obmng_basecurr )                                         as NetAmountInCoCodeCurrency, // Open Amount in Company Code Currency

      PurchaseOrderItem.NetPriceAmount,
      cast ( 0 as netpr_repcrcy1 )                                         as NetPriceInReportingCurrency,  // Open Quantity Amount in Reporting Currency 1,

      cast ( 0 as netpr_repcrcy2)                                          as NetPriceInReportingCurrency2, // Open Quantity Amount in Reporting Currency 2

      cast ( 0 as netpr_basecurr )                                         as NetPriceInCompanyCodeCrcy,
      IsReturnsItem,
      PurchaseOrderItem.PurchasingDocumentDeletionCode,
      PurchaseOrderItem._PurchaseOrder.ReleaseIsNotCompleted,
      PurchaseOrderItem._PurchaseOrder.PurchasingCompletenessStatus,
      PurchaseOrderItem.OrderQuantity,
      cast ( '' as abap.cuky(5) )                                          as AnalyticalReportingCurrency, // Reporting Currency 1

      cast ( '' as abap.cuky(5) )                                          as AnalyticalReportingCurrency2, // Reporting Currency 2

      PurchaseOrderItem._Plant
}
where
      PurchaseOrderItem._PurchaseOrder.PurchasingDocumentDeletionCode = ''
  and PurchaseOrderItem._PurchaseOrder.ReleaseIsNotCompleted          = ''
  and PurchaseOrderItem._PurchaseOrder.PurchasingCompletenessStatus   = ''
  and PurchaseOrderItem.PurchasingDocumentDeletionCode                = ''
  and PurchaseOrderItem._PurchaseOrder.DocumentCurrency != ''
  and PurchaseOrderItem._PurchaseOrder.PurchaseOrderDate              <= $parameters.P_EndDate
  and PurchaseOrderItem._PurchaseOrder.PurchaseOrderDate              >= $parameters.P_StartDate