P_PayReqnLineItems

@AbapCatalog.sqlViewName: 'PPAYREQLINEITEM'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@VDM.private: true
@VDM.viewType: #COMPOSITE
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #M
@AccessControl.authorizationCheck: #CHECK
@ClientHandling.algorithm: #SESSION_VARIABLE
define view P_PayReqnLineItems
as 

select from I_OperationalAcctgDocItem
  association [1..1] to I_JournalEntry  as _JournalEntry  on  $projection.CompanyCode        = _JournalEntry.CompanyCode
                                                          and $projection.FiscalYear         = _JournalEntry.FiscalYear
                                                          and $projection.AccountingDocument = _JournalEntry.AccountingDocument
  association [0..1] to I_Currency as _TransactionCurrency on  $projection.TransactionCurrency = _TransactionCurrency.Currency
{
    key CompanyCode            as CompanyCode,
    key AccountingDocument     as AccountingDocument,
    key FiscalYear             as FiscalYear,
    key AccountingDocumentItem as AccountingDocumentItem,
        AccountingDocumentCategory,
        FinancialAccountType,
        NetDueDate,
        @ObjectModel.foreignKey.association: '_TransactionCurrency'
        @Semantics.currencyCode:true
        TransactionCurrency,
        @Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
        AmountInTransactionCurrency,
        @Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
        abs(AmountInTransactionCurrency) as AbsAmountInTransactionCurrency,
        Supplier,
        BPBankAccountInternalID as SupplierBankType,
        
        div( dats_days_between( cast( concat( FiscalYear , '0101' ) as abap.dats ), cast($session.system_date as abap.dats) ), 7) as TodayWeekOfYear,
        div( dats_days_between( cast( concat( FiscalYear , '0101' ) as abap.dats ), NetDueDate ), 7) as DueDateWeekOfYear,
        
        DATS_DAYS_BETWEEN(cast($session.system_date as abap.dats), cast(NetDueDate as abap.dats)) as NetDueArrearsDays,
        case when ClearingAccountingDocument <> ''
          then 'C' //cleared

          else 'O' // open item

        end  as ClearingStatus,
        ClearingAccountingDocument,
        ClearingIsReversed,
        DocumentDate,
        PostingDate,
        SpecialGLCode,
        PaymentMethod,
        CostCenter,
        PurchasingDocument,
        PurchasingDocumentItem,
        SalesDocument,
        SalesDocumentItem,
        CashDiscount1DueDate,
        CashDiscount1Percent,
        CashDiscount1Days,
        CashDiscount2Percent,
        CashDiscount2Days,
        DueCalculationBaseDate,
        WBSElementInternalID,
        _WBSElementBasicData.WBSElementExternalID,
        ControllingArea,
        ProfitCenter,
        BranchCode,
        DocumentItemText,
        AssignmentReference,
        _JournalEntry.ReferenceDocumentType as ReferenceDocumentType,
        _JournalEntry.OriginalReferenceDocument as OriginalReferenceDocument,
        case when _JournalEntry.ReferenceDocumentType = 'RMRP'
          then substring(_JournalEntry.OriginalReferenceDocument, 1, 10)
        end as PurchasingHistoryDocument,
        case when _JournalEntry.ReferenceDocumentType = 'RMRP'
          then substring(_JournalEntry.OriginalReferenceDocument, 11, 4)
        end as PurchasingHistoryDocumentYear,
        I_OperationalAcctgDocItem.PaymentBlockingReason as PaymentBlockingReason,
        Reference1IDByBusinessPartner,
        Reference2IDByBusinessPartner,
        Reference3IDByBusinessPartner,
        case AccountingDocumentCategory 
          when 'S' then 'S' //Noted items

          when ''  then 'N' // normal items

          else 'S' // with special GL

        end as ItemType,
        _JournalEntry,
        _TransactionCurrency
}
where FinancialAccountType = 'K'
  and ( ( AccountingDocumentCategory = ''
       and SpecialGLCode = '' 
       and InvoiceReference = '' )  //normal items, exclude clearing document

     or 
     ( AccountingDocumentCategory = 'S' and SpecialGLCode = 'F' )  //down payment request

   )
        
union all 

// supplier and customer mixed accounts

select from I_OperationalAcctgDocItem
  inner join I_Supplier as Supplier on I_OperationalAcctgDocItem.Customer = Supplier.Customer
  association [1..1] to I_JournalEntry  as _JournalEntry  on  $projection.CompanyCode        = _JournalEntry.CompanyCode
                                                          and $projection.FiscalYear         = _JournalEntry.FiscalYear
                                                          and $projection.AccountingDocument = _JournalEntry.AccountingDocument                             
  association [0..1] to I_Currency as _TransactionCurrency on  $projection.TransactionCurrency = _TransactionCurrency.Currency
{
    key I_OperationalAcctgDocItem.CompanyCode            as CompanyCode,
    key AccountingDocument     as AccountingDocument,
    key FiscalYear             as FiscalYear,
    key AccountingDocumentItem as AccountingDocumentItem,
        AccountingDocumentCategory,
        FinancialAccountType,
        NetDueDate,
        @ObjectModel.foreignKey.association: '_TransactionCurrency'
        @Semantics.currencyCode:true
        TransactionCurrency,
        @Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
        AmountInTransactionCurrency,
        @Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
        abs(AmountInTransactionCurrency) as AbsAmountInTransactionCurrency,
        Supplier.Supplier as Supplier,
        I_OperationalAcctgDocItem.BPBankAccountInternalID as SupplierBankType,
        
        div( dats_days_between( cast( concat( FiscalYear , '0101' ) as abap.dats ), cast($session.system_date as abap.dats) ), 7) as TodayWeekOfYear,
        div( dats_days_between( cast( concat( FiscalYear , '0101' ) as abap.dats ), NetDueDate ), 7) as DueDateWeekOfYear,

        DATS_DAYS_BETWEEN(cast($session.system_date as abap.dats), cast(NetDueDate as abap.dats)) as NetDueArrearsDays,
        case when ClearingAccountingDocument <> ''
          then 'C' //cleared

          else 'O' // open item

        end  as ClearingStatus,
        ClearingAccountingDocument,
        ClearingIsReversed,
        DocumentDate,
        PostingDate,
        SpecialGLCode,
        PaymentMethod,
        CostCenter,
        PurchasingDocument,
        PurchasingDocumentItem,
        SalesDocument,
        SalesDocumentItem,
        CashDiscount1DueDate,
        CashDiscount1Percent,
        CashDiscount1Days,
        CashDiscount2Percent,
        CashDiscount2Days,
        DueCalculationBaseDate,
        WBSElementInternalID,
        _WBSElementBasicData.WBSElementExternalID,
        ControllingArea,
        ProfitCenter,
        BranchCode,
        DocumentItemText,
        AssignmentReference,
        _JournalEntry.ReferenceDocumentType as ReferenceDocumentType,
        _JournalEntry.OriginalReferenceDocument as OriginalReferenceDocument,
        case when _JournalEntry.ReferenceDocumentType = 'RMRP'
          then substring(_JournalEntry.OriginalReferenceDocument, 1, 10)
        end as PurchasingHistoryDocument,
        case when _JournalEntry.ReferenceDocumentType = 'RMRP'
          then substring(_JournalEntry.OriginalReferenceDocument, 11, 4)
        end as PurchasingHistoryDocumentYear,
        I_OperationalAcctgDocItem.PaymentBlockingReason as PaymentBlockingReason,
        Reference1IDByBusinessPartner,
        Reference2IDByBusinessPartner,
        Reference3IDByBusinessPartner,        
        'M' as ItemType,
        _JournalEntry,
        _TransactionCurrency
}
where FinancialAccountType = 'D'  // customer items

  and AccountingDocumentCategory = ''
  and SpecialGLCode = ''
  and InvoiceReference = ''
  and Supplier.Customer != ''
;
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_JOURNALENTRY",
"I_OPERATIONALACCTGDOCITEM",
"I_SUPPLIER",
"I_WBSELEMENTBASICDATA"
],
"ASSOCIATED":
[
"I_CURRENCY",
"I_JOURNALENTRY"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/