P_PurOrdMaintainRefDocumentAll

@VDM.viewType: #CONSUMPTION
@AbapCatalog.sqlViewName: 'PPOMAINTREFDOCAL'
@VDM.private: true
@AccessControl.authorizationCheck: #PRIVILEGED_ONLY

define view P_PurOrdMaintainRefDocumentAll

  //=======================================================================================================//

  //  Purchase Order Items

  //=======================================================================================================//

  as select from    P_PurOrdMaintainItemActive     as po_item
    left outer join P_PurOrdMaintainHeaderActive   as po_header  on po_item.PurchaseOrder = po_header.PurchaseOrder
    left outer join I_SalesOrderItem               as so_item    on  po_item.SalesOrder     = so_item.SalesOrder
                                                                 and po_item.SalesOrderItem = so_item.SalesOrderItem
    left outer join P_PurOrdMaintainPDocWAccAsLine as AccAssLine on  po_item.PurchaseOrder     = AccAssLine.PurchasingDocument
                                                                 and po_item.PurchaseOrderItem = AccAssLine.PurchasingDocumentItem
{
  key  po_item.PurchaseOrder                    as PurchaseOrder,
  key  po_item.PurchaseOrderItem                as PurchaseOrderItem,
  key  cast('' as abap.char(10))                as PurchasingInfoRecord,
  key  po_header.PurchasingOrganization         as PurchasingOrganization,
  key  cast('' as abap.char(1))                 as PurchasingInfoRecordCategory,
  key  po_item.Plant                            as Plant,
       'F'                                      as PurchasingDocumentCategory,
       po_item.StorageLocation                  as StorageLocation,
       po_item.Material                         as Material,
       po_item.MaterialType                     as MaterialType,
       po_item.MaterialGroup                    as MaterialGroup,
       po_header.Supplier                       as Supplier,
       po_header.PurchasingDocumentType         as PurchasingDocumentType,
       po_item.PurchasingDocumentItemText       as PurchasingDocumentItemText,
       po_item.PurchaseOrderQty                 as PurchaseOrderQty,
       po_item.PurchaseOrderQuantityUnit        as PurchaseOrderQuantityUnit,
       po_item.PurchaseOrderNetPriceAmount      as PurchaseOrderNetPriceAmount,
       po_item.TaxCode                          as TaxCode,
       po_item.TaxJurisdiction                  as TaxJurisdiction,
       po_header.Currency                       as Currency,
       po_item.PurchaseOrderNetPriceQuantity    as PurchaseOrderNetPriceQuantity,
       po_item.PurchaseOrderPriceUnit           as PurchaseOrderPriceUnit,
       po_header.PurchasingGroup                as PurchasingGroup,
       po_header.CompanyCode                    as CompanyCode,
       cast('' as abap.char(25))                as CompanyCodeName,
       po_header.PurchasingDocumentCondition    as PurchasingDocumentCondition,
       po_header.PricingProcedure               as PricingProcedure,
       po_header.ValidityEndDate                as ValidityEndDate,
       po_item.GoodsReceiptIsExpected           as GoodsReceiptIsExpected,
       po_item.GoodsReceiptIsNonValuated        as GoodsReceiptIsNonValuated,
       po_item.InvoiceIsExpected                as InvoiceIsExpected,
       po_item.InvoiceIsGoodsReceiptBased       as InvoiceIsGoodsReceiptBased,
       po_item.OverdelivTolrtdLmtRatioInPct     as OverdelivTolrtdLmtRatioInPct,
       po_item.UnlimitedOverdeliveryIsAllowed   as UnlimitedOverdeliveryIsAllowed,
       po_item.UnderdelivTolrtdLmtRatioInPct    as UnderdelivTolrtdLmtRatioInPct,
       po_item.OrderItemQtyToBaseQtyNmrtr       as OrderItemQtyToBaseQtyNmrtr,
       po_item.OrderItemQtyToBaseQtyDnmntr      as OrderItemQtyToBaseQtyDnmntr,
       po_item.PurchaseOrderItemCategory        as PurchaseOrderItemCategory,
       po_item.AcctAssignmentCategory           as AcctAssignmentCategory,
       po_item.IsDeleted                        as IsDeleted,
       po_item.ServicePerformer                 as ServicePerformer,
       po_item.ProductType                      as ProductType,
       po_item.RefPurchaseOutlineAgreement      as RefPurchaseOutlineAgreement,
       po_item.RefPurchOutlineAgrmtItem         as RefPurchOutlineAgrmtItem,
       po_item.SupplierMaterialNumber           as SupplierMaterialNumber,
       po_header.IncotermsVersion               as IncotermsVersion,
       po_header.IncotermsClassification        as HeaderIncotermsClassification,
       po_header.IncotermsTransferLocation      as HeaderIncotermsTransferLoc,
       po_header.IncotermsLocation1             as HeaderIncotermsLocation1,
       po_header.IncotermsLocation2             as HeaderIncotermsLocation2,
       po_item.IncotermsClassification          as IncotermsClassification,
       po_item.IncotermsTransferLocation        as IncotermsTransferLocation,
       po_item.IncotermsLocation1               as IncotermsLocation1,
       po_item.IncotermsLocation2               as IncotermsLocation2,
       po_item.DeliveryDate                     as DeliveryDate,
       po_item.PerformancePeriodStartDate       as PerformancePeriodStartDate,
       po_item.PerformancePeriodEndDate         as PerformancePeriodEndDate,
       po_item.OrderPriceUnitToOrderUnitNmrtr   as OrderPriceUnitToOrderUnitNmrtr,
       po_item.OrdPriceUnitToOrderUnitDnmntr    as OrdPriceUnitToOrderUnitDnmntr,
       AccAssLine.ProfitCenter                  as ProfitCenter,
       AccAssLine.WBSElementInternalID          as WBSElementInternalID,
       AccAssLine.FunctionalArea                as FunctionalArea,
       AccAssLine.GLAccount                     as GLAccount,
       AccAssLine.CostCenter                    as CostCenter,
       AccAssLine.ControllingArea               as ControllingArea,
       AccAssLine.SalesOrder                    as SalesOrder,
       AccAssLine.SalesOrderItem                as SalesOrderItem,
       so_item.CreatedByUser                    as SalesOrderCreatedByUser,
       so_item._SalesOrder.SoldToParty          as SalesOrderSoldToParty,
       so_item._SalesOrder.SalesOrganization    as SalesOrderSalesOrganization,
       so_item._SalesOrder.DistributionChannel  as SalesOrderDistributionChannel,
       so_item._SalesOrder.OrganizationDivision as SalesOrderOrganizationDivision

}


//=======================================================================================================//

//  Purchase Contract Items

//=======================================================================================================//

union all

select from       I_PurchaseContractItem         as ctr_item
  left outer join I_PurchaseContract             as ctr_header on ctr_item.PurchaseContract = ctr_header.PurchaseContract
  left outer join P_PurOrdMaintainPDocWAccAsLine as AccAssLine on  ctr_item.PurchaseContract     = AccAssLine.PurchasingDocument
                                                               and ctr_item.PurchaseContractItem = AccAssLine.PurchasingDocumentItem
{
  key  ctr_item.PurchaseContract            as PurchaseOrder,
  key  ctr_item.PurchaseContractItem        as PurchaseOrderItem,
  key  cast('' as abap.char(10))            as PurchasingInfoRecord,
  key  ctr_header.PurchasingOrganization    as PurchasingOrganization,
  key  cast('' as abap.char(1))             as PurchasingInfoRecordCategory,
  key  ctr_item.Plant                       as Plant,
       'K'                                  as PurchasingDocumentCategory,
       ctr_item.StorageLocation             as StorageLocation,
       ctr_item.Material                    as Material,
       ''                                   as MaterialType,
       ctr_item.MaterialGroup               as MaterialGroup,
       ctr_header.Supplier                  as Supplier,
       ctr_header.PurchaseContractType      as PurchasingDocumentType,
       ctr_item.PurchaseContractItemText    as PurchasingDocumentItemText,
       cast(0 as abap.dec( 13, 3 ))         as PurchaseOrderQty,
       ctr_item.OrderQuantityUnit           as PurchaseOrderQuantityUnit,
       ctr_item.ContractNetPriceAmount      as PurchaseOrderNetPriceAmount,
       ''                                   as TaxCode,
       ''                                   as TaxJurisdiction,
       ctr_header.DocumentCurrency          as Currency,
       ctr_item.NetPriceQuantity            as PurchaseOrderNetPriceQuantity,
       ctr_item.OrderPriceUnit              as PurchaseOrderPriceUnit,
       ctr_header.PurchasingGroup           as PurchasingGroup,
       ctr_header.CompanyCode               as CompanyCode,
       cast('' as abap.char(25))            as CompanyCodeName,
       ''                                   as PurchasingDocumentCondition,
       ''                                   as PricingProcedure,
       ctr_header.ValidityEndDate           as ValidityEndDate,
       ''                                   as GoodsReceiptIsExpected,
       ''                                   as GoodsReceiptIsNonValuated,
       ''                                   as InvoiceIsExpected,
       ''                                   as InvoiceIsGoodsReceiptBased,
       cast(0 as abap.dec( 5, 1 ))          as OverdelivTolrtdLmtRatioInPct,
       ''                                   as UnlimitedOverdeliveryIsAllowed,
       cast(0 as abap.dec( 5, 1 ))          as UnderdelivTolrtdLmtRatioInPct,
       cast(0 as abap.dec( 5, 0 ))          as OrderItemQtyToBaseQtyNmrtr,
       cast(0 as abap.dec( 5, 0 ))          as OrderItemQtyToBaseQtyDnmntr,
       ''                                   as PurchaseOrderItemCategory,
       ''                                   as AcctAssignmentCategory,
       ''                                   as IsDeleted,
       ''                                   as ServicePerformer,
       ''                                   as ProductType,
       ''                                   as RefPurchaseOutlineAgreement,
       cast('00000' as abap.numc(5))        as RefPurchOutlineAgrmtItem,
       ''                                   as SupplierMaterialNumber,
       cast('' as abap.char(4))             as IncotermsVersion,
       cast('' as abap.char(3))             as HeaderIncotermsClassification,
       cast('' as abap.char(28))            as HeaderIncotermsTransferLoc,
       cast('' as abap.char(70))            as HeaderIncotermsLocation1,
       cast('' as abap.char(70))            as HeaderIncotermsLocation2,
       ''                                   as IncotermsClassification,
       ''                                   as IncotermsTransferLocation,
       cast('' as abap.char(70))            as IncotermsLocation1,
       cast('' as abap.char(70))            as IncotermsLocation2,
       cast('' as abap.dats)                as DeliveryDate,
       cast('' as abap.dats)                as PerformancePeriodStartDate,
       cast('' as abap.dats)                as PerformancePeriodEndDate,
       cast(0 as abap.dec( 5, 0 ))          as OrderPriceUnitToOrderUnitNmrtr,
       cast(0 as abap.dec( 5, 0 ))          as OrdPriceUnitToOrderUnitDnmntr,
       AccAssLine.ProfitCenter              as ProfitCenter,
       AccAssLine.WBSElementInternalID      as WBSElementInternalID,
       AccAssLine.FunctionalArea            as FunctionalArea,
       AccAssLine.GLAccount                 as GLAccount,
       AccAssLine.CostCenter                as CostCenter,
       AccAssLine.ControllingArea           as ControllingArea,
       AccAssLine.SalesOrder                as SalesOrder,
       AccAssLine.SalesOrderItem            as SalesOrderItem,
       ''                                   as SalesOrderCreatedByUser,
       ''                                   as SalesOrderSoldToParty,
       ''                                   as SalesOrderSalesOrganization,
       ''                                   as SalesOrderDistributionChannel,
       ''                                   as SalesOrderOrganizationDivision

}


//=======================================================================================================//

//  Inforecords

//=======================================================================================================//

union all select from I_PurgInfoRecdOrgPlantData as eine
  left outer join     I_PurchasingInfoRecord     as eina     on eine.PurchasingInfoRecord = eina.PurchasingInfoRecord
  left outer join     I_Material                 as material on eina.Material = material.Material
{
  key  ''                                   as PurchaseOrder,
  key  cast('' as abap.numc(5))             as PurchaseOrderItem,
  key  eine.PurchasingInfoRecord            as PurchasingInfoRecord,
  key  eine.PurchasingOrganization          as PurchasingOrganization,
  key  eine.PurchasingInfoRecordCategory    as PurchasingInfoRecordCategory,
  key  eine.Plant                           as Plant,
       'I'                                  as PurchasingDocumentCategory,
       ''                                   as StorageLocation, //will be taken from I_MaterialPlant (=MARC-LGFSB) once available

       eina.Material                        as Material,
       material._MaterialType.MaterialType  as MaterialType,
       case eina.MaterialGroup
         when '' then eina._Material.MaterialGroup
         else         eina.MaterialGroup
       end                                  as MaterialGroup,
       eina.Supplier                        as Supplier,
       cast('' as abap.char(4))             as PurchasingDocumentType,
       eina.PurchasingInfoRecordDesc        as PurchasingDocumentItemText,
       eine.StandardPurchaseOrderQuantity   as PurchaseOrderQty,
       eina.PurgDocOrderQuantityUnit        as PurchaseOrderQuantityUnit,
       eine.NetPriceAmount                  as PurchaseOrderNetPriceAmount,
       eine.TaxCode                         as Taxcode,
       cast('' as abap.char(15))            as TaxJurisdiction,
       eine.Currency                        as Currency,
       eine.MaterialPriceUnitQty            as PurchaseOrderNetPriceQuantity,
       eine.PurchaseOrderPriceUnit          as PurchaseOrderPriceUnit,
       eine.PurchasingGroup                 as PurchasingGroup,
       cast('' as abap.char(4))             as CompanyCode,
       cast('' as abap.char(25))            as CompanyCodeName,
       cast('' as abap.char(10))            as PurchasingDocumentCondition,
       ''                                   as PricingProcedure,
       eine.PriceValidityEndDate            as ValidityEndDate,
       cast('' as abap.char(1))             as GoodsReceiptIsExpected,
       cast('' as abap.char(1))             as GoodsReceiptIsNonValuated,
       cast('' as abap.char(1))             as InvoiceIsExpected,
       eine.InvoiceIsGoodsReceiptBased      as InvoiceIsGoodsReceiptBased,
       eine.OverdelivTolrtdLmtRatioInPct    as OverdelivTolrtdLmtRatioInPct,
       eine.UnlimitedOverdeliveryIsAllowed  as UnlimitedOverdeliveryIsAllowed,
       eine.UnderdelivTolrtdLmtRatioInPct   as UnderdelivTolrtdLmtRatioInPct,
       eina.OrderItemQtyToBaseQtyNmrtr      as OrderItemQtyToBaseQtyNmrtr,
       eina.OrderItemQtyToBaseQtyDnmntr     as OrderItemQtyToBaseQtyDnmntr,
       ''                                   as PurchaseOrderItemCategory,
       ''                                   as AcctAssignmentCategory,
       eine.IsMarkedForDeletion             as IsDeleted,
       ''                                   as ServicePerformer,
       ''                                   as ProductType,
       cast('' as abap.char(10))            as RefPurchaseOutlineAgreement,
       cast('00000' as abap.numc(5))        as RefPurchOutlineAgrmtItem,
       eina.SupplierMaterialNumber          as SupplierMaterialNumber,
       eine.IncotermsVersion                as IncotermsVersion,
       cast('' as abap.char(3))             as HeaderIncotermsClassification,
       cast('' as abap.char(28))            as HeaderIncotermsTransferLoc,
       cast('' as abap.char(70))            as HeaderIncotermsLocation1,
       cast('' as abap.char(70))            as HeaderIncotermsLocation2,
       eine.IncotermsClassification         as IncotermsClassification,
       eine.IncotermsTransferLocation       as IncotermsTransferLocation,
       eine.IncotermsLocation1              as IncotermsLocation1,
       eine.IncotermsLocation2              as IncotermsLocation2,
       cast('' as abap.dats)                as DeliveryDate,
       cast('' as abap.dats)                as PerformancePeriodStartDate,
       cast('' as abap.dats)                as PerformancePeriodEndDate,
       eine.OrderPriceUnitToOrderUnitNmrtr  as OrderPriceUnitToOrderUnitNmrtr,
       eine.OrdPriceUnitToOrderUnitDnmntr   as OrdPriceUnitToOrderUnitDnmntr,
       cast('' as abap.char(10))            as ProfitCenter,
       cast('' as abap.numc(8))             as WBSElementInternalID,
       cast('' as abap.char(16))            as FunctionalArea,
       cast('' as abap.char(10))            as GLAccount,
       cast('' as abap.char(10))            as CostCenter,
       cast('' as abap.char(4))             as ControllingArea,
       cast('' as abap.char(10))            as SalesOrder,
       cast('' as abap.numc(6))             as SalesOrderItem,
       ''                                   as SalesOrderCreatedByUser,
       ''                                   as SalesOrderSoldToParty,
       ''                                   as SalesOrderSalesOrganization,
       ''                                   as SalesOrderDistributionChannel,
       ''                                   as SalesOrderOrganizationDivision
}
where
  PurchasingInfoRecordCategory = '0' //only IRs with cat. "Standard" allowed as reference (due to pricing issues with other categories)                                                                                                                                                                                                                              

  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
 /*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_MATERIAL",
"I_MATERIALTYPE",
"I_PURCHASECONTRACT",
"I_PURCHASECONTRACTITEM",
"I_PURCHASINGINFORECORD",
"I_PURGINFORECDORGPLANTDATA",
"I_SALESORDER",
"I_SALESORDERITEM",
"P_PURORDMAINTAINHEADERACTIVE",
"P_PURORDMAINTAINITEMACTIVE",
"P_PURORDMAINTAINPDOCWACCASLINE"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/