C_SalesOrderAgedDebtDetails

@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.sqlViewName: 'CSOAGEDDEBTDETLS'
@EndUserText.label: 'Aged Debt Details for a Sales Order'
@VDM.viewType: #CONSUMPTION
@AccessControl.authorizationCheck:#CHECK
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.dataClass:  #MIXED
@ObjectModel.usageType.serviceQuality: #C
define view C_SalesOrderAgedDebtDetails
  with parameters
    P_CalendarDate : dats

  as select from I_CustomerProjectItem        as WorkPackages
    inner join   I_BillingDocumentItem        as BillingDocumentItem on BillingDocumentItem.SalesDocument = WorkPackages.CustomerProjectItem
    inner join   C_SalesDocumentBillingStatus as BillingStatus       on BillingStatus.BillingDocument = BillingDocumentItem.BillingDocument
  association [0..1] to I_EngagementProject as _CustomerProject on $projection.CustomerProjectUUID = _CustomerProject.EngagementProjectUUID

{
  key WorkPackages.CustomerProjectUUID                                        as CustomerProjectUUID,
  key BillingDocumentItem.SalesDocument                                       as SalesOrder,
  key BillingDocumentItem.SalesDocumentItem                                   as SalesOrderItem,
  @Consumption.hidden: true
  key BillingDocumentItem.BillingDocument,
  @Consumption.hidden: true
  key BillingDocumentItem.BillingDocumentItem,
      @Semantics.currencyCode: true
      BillingDocumentItem.TransactionCurrency,
      @Semantics.amount.currencyCode: 'TransactionCurrency'
      case
          when BillingDocumentItem.ReturnItemProcessingType = 'X'
               then BillingDocumentItem.NetAmount * -1

          when BillingDocumentItem.ReturnItemProcessingType = 'B'
               then BillingDocumentItem.NetAmount * -1

          else BillingDocumentItem.NetAmount
          end                                                                 as CustProjWrkPckgAgedDebt,
      DATS_DAYS_BETWEEN(:P_CalendarDate,_BillingDocument.BillingDocumentDate) as DaysFromToday,
      _CustomerProject.EngagementProject                                      as CustomerProject,
      BillingDocumentItem._BillingDocument.BillingDocumentType                as BillingDocumentType,
      BillingDocumentItem._BillingDocument.SalesOrganization                  as SalesOrganization,
      _CustomerProject

}
where
      WorkPackages.CustomerProjectItemType        =  '0SOH'
  and _BillingDocument.BillingDocumentIsCancelled =  ''
  and _BillingDocument.CancelledBillingDocument   =  ''
  and BillingStatus.AccountingDocumentStatus      =  'I001'
  and BillingDocumentItem.Material                <> 'A001' //Filter the entries of On-Account Assignment

  and BillingDocumentItem.Material                <> 'A002' //Filter the entries of On-Account Assignment

group by
  WorkPackages.CustomerProjectUUID,
  BillingDocumentItem.SalesDocument,
  BillingDocumentItem.SalesDocumentItem,
  BillingDocumentItem.BillingDocument,
  BillingDocumentItem.BillingDocumentItem,
  BillingDocumentItem.TransactionCurrency,
  BillingDocumentItem.ReturnItemProcessingType,
  BillingDocumentItem.NetAmount,
  _BillingDocument.BillingDocumentDate,
  _CustomerProject.EngagementProject,
  BillingDocumentItem._BillingDocument.BillingDocumentType,
  BillingDocumentItem._BillingDocument.SalesOrganization
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"C_SALESDOCUMENTBILLINGSTATUS",
"I_BILLINGDOCUMENT",
"I_BILLINGDOCUMENTITEM",
"I_CUSTOMERPROJECTITEM",
"I_ENGAGEMENTPROJECT"
],
"ASSOCIATED":
[
"I_ENGAGEMENTPROJECT"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/