C_PurDocListInvoiceDetails

@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.sqlViewName: 'CDOCLINVDTL'
@VDM.viewType: #CONSUMPTION
@VDM.private : true
@AccessControl.authorizationCheck: #CHECK
@AccessControl.personalData.blocking: #REQUIRED

define view C_PurDocListInvoiceDetails
  as select distinct from SupplierInvoice as rbkp
    inner join            rseg                     as rseg     on  rbkp.belnr = rseg.belnr
                                                               and rbkp.gjahr = rseg.gjahr
    left outer join       I_PurchaseOrderItem      as ekpo     on  rseg.ebeln = ekpo.PurchaseOrder
                                                               and rseg.ebelp = ekpo.PurchaseOrderItem
    left outer join       I_PurchaseOrder          as ekko     on  ekpo.PurchaseOrder = ekko.PurchaseOrder
    left outer join       C_Purdoclistgrquantity   as ekbe_mp  on  rseg.belnr = ekbe_mp.InvoiceReference
                                                               and rseg.gjahr = ekbe_mp.InvoiceReferenceFiscalYear
                                                               and rseg.buzei = ekbe_mp.AccountingDocumentItem
    left outer join       C_Purdoclistinvoicestat  as st_cds   on  rbkp.belnr = st_cds.InvoiceReference
                                                               and rbkp.gjahr = st_cds.InvoiceReferenceFiscalYear
    left outer join       C_PURDOCLISTSIWBSELEMENT as wbsobjsi on  rseg.belnr = wbsobjsi.InvoiceReference
                                                               and rseg.gjahr = wbsobjsi.InvoiceReferenceFiscalYear
                                                               and rseg.buzei = wbsobjsi.AccountingDocumentItem
    left outer join       I_Supplier       as invoice_supplier on  rbkp.lifnr = invoice_supplier.Supplier                                                               
  association [0..*] to t003t                          as _InvoiceTypeText            on  $projection.AccountingDocumentType = _InvoiceTypeText.blart
  association [0..*] to I_MaterialText                 as _MatText                    on  $projection.Material = _MatText.Material
  association [0..*] to I_MaterialGroupText            as _MatGroupText               on  $projection.MaterialGroup = _MatGroupText.MaterialGroup
  association [0..1] to dd07t                          as _StatusText                 on  $projection.InvoicingStatus = _StatusText.domvalue_l
                                                                                      and _StatusText.domname         = 'MMPUR_DOC_STATUS'
                                                                                      and _StatusText.as4local        = 'A'
  association [1..1] to I_Plant                        as _SupplyingPlant             on  $projection.SupplyingPlant = _SupplyingPlant.Plant
  association [0..*] to I_PurgDocumentItemCategoryText as _ItemCategoryText           on  $projection.PurchaseOrderItemCategory = _ItemCategoryText.PurchasingDocumentItemCategory
  association [0..*] to I_AcctAssignmentCategoryText   as _AcctAssignmentCategoryText on  $projection.AccountAssignmentCategory = _AcctAssignmentCategoryText.AccountAssignmentCategory
  association [0..*] to I_PurchasingDocumentTypeText   as _PurchasingDocumentTypeText on  $projection.PurchasingDocumentType     = _PurchasingDocumentTypeText.PurchasingDocumentType
                                                                                      and $projection.PurchasingDocumentCategory = _PurchasingDocumentTypeText.PurchasingDocumentCategory
  association [0..1] to C_Purdoclistsuppaddr           as _Sup_addr                   on  ekko.Supplier = _Sup_addr.Supplier 
  association [0..*] to I_CountryText                  as _CountryName                on  $projection.SupplierAddressCountry = _CountryName.Country
  association [0..1] to I_CostCenterText               as _CCText                     on  _CCText.CostCenter = $projection.CostCenter
                                                                                      and _CCText.ControllingArea = $projection.ControllingArea
                                                                                      and _CCText.Language = $session.system_language
                                                                                      and _CCText.ValidityStartDate <= $session.system_date
                                                                                      and _CCText.ValidityEndDate >= $session.system_date    
{
  key rbkp.belnr                                              as PurchasingDocument,
  key rbkp.gjahr                                              as InvoiceReferenceFiscalYear,
  key rseg.buzei                                              as PurchasingDocumentItem,
  key wbsobjsi.AccountingDocumentItem                         as AccountingDocumentItem,
  key wbsobjsi.OrdinalNumber                                  as AccountingOrdinalNumber, 
      ekpo.PurchaseOrder                                      as PurchaseOrder,
      ekpo.PurchaseOrderItem                                  as PurchaseOrderItem,
      rseg.matnr                                              as Material,                   -- Material
      rseg.matnr                                              as ManufacturerMaterial,       -- Same as Material in Invoice
      //rseg.menge as Quantity,                --Invoiced Quantity

                      case 
                      when rseg.shkzg is not null and rseg.shkzg = 'H'
                      then cast(-rseg.menge as menge_d )
                      else 
                      rseg.menge
                      end as Quantity,      --Invoiced Quantity
      rseg.meins                                              as QuantityUnit,               -- Invoiced Quantity Unit
      ekbe_mp.GoodsMovementQuantity,                                                         -- Quantity Delivered
      //rseg.wrbtr as AmountInTranactionCurrency,   --- Invoiced value  

                      case 
                      when rseg.shkzg is not null and rseg.shkzg = 'H'
                      then cast(-rseg.wrbtr as wrbtr )
                      else 
                      rseg.wrbtr
                      end as AmountInTranactionCurrency,      --Invoiced value
      rbkp.waers                                              as CompanyCodeCurrency,        -- Invoice value Currency
      ekpo.OrderQuantity                                      as PurchaseOrderItemQuantity,  -- OrderedQuantity
      ekpo.NetAmount,                                                                        -- Ordered Value Need to check
      ekko.DocumentCurrency                                   as PurchaseOrderCurrency,      -- Ordered ValueUnit
      //Invoicing Status'

      case
       when rseg.buzei is null then cast('36' as abap.char(10))
         else cast(st_cds.item_status as abap.char(10))
      end                                                     as InvoicingStatus, 
      rbkp.bukrs                                              as CompanyCode,
      ekko.PurchasingGroup                                    as PurchasingGroup,
      ekko.SupplyingPlant                                     as SupplyingPlant,
      _SupplyingPlant.PlantName                               as SupplyingPlantName,
      ekpo.MaterialGroup                                      as MaterialGroup,
      case 
        when ekko.Supplier is null or ekko.Supplier = ''
          then   rbkp.lifnr
        else ekko.Supplier
      end                                                     as Supplier,
      case 
        when ekko.Supplier is null or ekko.Supplier = ''
          then   invoice_supplier.SupplierName
        else ekko._Supplier.SupplierName
      end                                                     as SupplierName, 
      _Sup_addr.SupplierAddressCountry                        as SupplierAddressCountry,
      _Sup_addr.SupplierAddressCity                           as SupplierAddressCity,
      ekko.PurchasingOrganization,
      ekko._PurchasingOrganization.PurchasingOrganizationName as PurchasingOrganisationName,
      ekpo.Plant                                              as Plant,
      ekpo._Plant.PlantName                                   as PlantName,
      ekpo.PurchaseOrderItemText                              as PurchasingDocumentItemText,
      cast('' as abap.char(1))                                as PurchaseOrderItemCategory,
      ekko.PurchaseOrderType                                  as PurchasingDocumentType,
      ekko._PurchasingGroup.PurchasingGroupName               as PurchasingGroupName,
      'S'                                                     as PurchasingDocumentCategory,
      cast('' as abap.char(10))                               as ServicePerformer,
      cast('' as abap.char(10))                               as ServicePerformerName,
      cast('' as abap.char(10))                               as WorkItem,
      cast('19000101' as abap.dats(8))                        as DeliveryDate,
      cast('' as abap.char(1))                                as AccountAssignmentCategory,
      -------Search Scope Field
      rbkp.bldat                                              as DocumentDate,
      rbkp.cpudt                                              as CreationDate,
      rbkp.lifnr                                              as PurchaseOrderVendor,
      rbkp.usnam                                              as EnteredByUser,
      rbkp.bktxt                                              as InvoiceReceiptHeadertext,
      rbkp.rebzg                                              as InvoiceReference1,
      rbkp.brnch                                              as MfgOrderSequenceBranchOpearat,
      rbkp.reindat                                            as InvoiceReceiptDate,
      rbkp.gsber                                              as BusinessArea,
      rseg.erp_contract_id,
      rbkp.blart                                              as AccountingDocumentType,
      rbkp.zterm                                              as PaymentTerm,
      rbkp.budat                                              as PostingDate,
      rbkp.iseopblocked                                       as IsEndOfPurposeBlocked,

      -- Fields from WBSELEMENT
      COALESCE( wbsobjsi.WBSElementInternalID, '')            as WBSElementInternalID,
      COALESCE( wbsobjsi.WBSElementExternalID, '')            as WBSElementExternalID,
      wbsobjsi.SalesOrder,
      wbsobjsi.SalesOrderItem,
      COALESCE( wbsobjsi.WorkPackageFullName, '')             as WorkPackageFullName,
      wbsobjsi.WBSDescription,
      wbsobjsi.CostCenter,
      _CCText.CostCenterName,
      wbsobjsi.ControllingArea,
      cast(1 as abap.int4)                                    as cnts,
      _InvoiceTypeText,
      _MatText,
      _MatGroupText,
      _StatusText,
      _PurchasingDocumentTypeText,
      _ItemCategoryText,
      _AcctAssignmentCategoryText,
      _CountryName
}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"C_PURDOCLISTGRQUANTITY",
"C_PURDOCLISTINVOICESTAT",
"C_PURDOCLISTSIWBSELEMENT",
"C_PURDOCLISTSUPPADDR",
"I_COSTCENTERTEXT",
"I_PLANT",
"I_PURCHASEORDER",
"I_PURCHASEORDERITEM",
"I_PURCHASINGGROUP",
"I_PURCHASINGORGANIZATION",
"I_SUPPLIER",
"SUPPLIERINVOICE",
"RSEG"
],
"ASSOCIATED":
[
"C_PURDOCLISTSUPPADDR",
"I_ACCTASSIGNMENTCATEGORYTEXT",
"I_COSTCENTERTEXT",
"I_COUNTRYTEXT",
"I_MATERIALGROUPTEXT",
"I_MATERIALTEXT",
"I_PLANT",
"I_PURCHASINGDOCUMENTTYPETEXT",
"I_PURGDOCUMENTITEMCATEGORYTEXT",
"DD07T",
"T003T"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/