P_CN_CADEAcctgDocRblItem

@AbapCatalog.preserveKey: true
@AbapCatalog.sqlViewName: 'PCNCADEARITEMS'
@VDM.private: true
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.sizeCategory: #XXL
@VDM.viewType: #COMPOSITE
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.dataClass: #MIXED
define view P_CN_CADEAcctgDocRblItem
  with parameters
    @EndUserText.label: 'Ledger'
    P_Ledger           : fis_rldnr,
    @EndUserText.label: 'Fiscal Year'
    P_FiscalYear       : fis_gjahr,
    @EndUserText.label: 'From Fiscal Period'
    P_FromFiscalPeriod : fins_fiscalperiod,
    @EndUserText.label: 'To Fiscal Period'
    P_ToFiscalPeriod   : fins_fiscalperiod,
    @EndUserText.label: 'Company Code'
    P_CompanyCode      : bukrs,
    @EndUserText.label: 'Language'
    @Environment.systemField: #SYSTEM_LANGUAGE
    P_Language         : sylangu
  as select from I_OperationalAcctgDocItem as receivablePayableItem

  association [0..1] to I_CurrencyText                as _TransactionCrcyText  on  receivablePayableItem.TransactionCurrency = _TransactionCrcyText.Currency
                                                                               and _TransactionCrcyText.Language             = :P_Language
  association [0..1] to I_CurrencyText                as _CompanyCodeCrcyText  on  receivablePayableItem.CompanyCodeCurrency = _CompanyCodeCrcyText.Currency
                                                                               and _CompanyCodeCrcyText.Language             = :P_Language
  association [0..1] to P_CN_CADEINCOMINGCHECK        as _IncgChk              on  (
                   receivablePayableItem.AccountingDocument                                                          = _IncgChk.BankLedgerDocument
                   or receivablePayableItem.AccountingDocument                                                       = _IncgChk.SubledgerDocument
                 )
                                                                               and receivablePayableItem.FiscalYear  = _IncgChk.FiscalYear
                                                                               and receivablePayableItem.CompanyCode = _IncgChk.CompanyCode
  association [0..1] to P_CN_CADEBillofExchangeInfo2  as _BOE                  on  _BOE.gjahr = receivablePayableItem.FiscalYear
                                                                               and _BOE.belnr = receivablePayableItem.AccountingDocument
                                                                               and _BOE.bukrs = receivablePayableItem.CompanyCode
                                                                               and _BOE.buzei = receivablePayableItem.AccountingDocumentItem
  association [0..1] to I_JournalEntry                as _ClearDocJournalEntry on  receivablePayableItem.ClearingAccountingDocument = _ClearDocJournalEntry.AccountingDocument
                                                                               and receivablePayableItem.CompanyCode                = _ClearDocJournalEntry.CompanyCode
                                                                               and receivablePayableItem.FiscalYear                 = _ClearDocJournalEntry.FiscalYear
  association [0..1] to I_FiscCalendarDateForCompCode as _CalendarDate         on  _CalendarDate.CalendarDate = receivablePayableItem.ClearingDate
                                                                               and _CalendarDate.CompanyCode  = receivablePayableItem.CompanyCode
  association [0..1] to I_GLAccount                  as _AlternativeGLAccount       on  receivablePayableItem.AlternativeGLAccount = _AlternativeGLAccount.GLAccount
                                                                                    and receivablePayableItem.CompanyCode          = _AlternativeGLAccount.CompanyCode
{
  key receivablePayableItem.CompanyCode,
  key receivablePayableItem.FiscalYear,
  key receivablePayableItem._JournalEntry.AccountingDocumentType,
  key receivablePayableItem.AccountingDocument,
  key receivablePayableItem.AccountingDocumentItem,
  key receivablePayableItem.GLAccount,
  key receivablePayableItem._CompanyCode.ChartOfAccounts,
      ltrim(receivablePayableItem.Customer,'0')                            as Debtor,
      receivablePayableItem.DocumentDate,
      receivablePayableItem.PostingDate,
      ltrim(receivablePayableItem.FiscalPeriod,'0')                      as FiscalPeriod,
      _CompanyCodeCrcyText.CurrencyShortName                             as CurrencyShortName,
      case when receivablePayableItem._JournalEntry.ExchangeRate = 0
      then cast('1' as cade_kursf )
      else cast(receivablePayableItem._JournalEntry.ExchangeRate as cade_kursf)
      end                                                                as ExchangeRate,
      receivablePayableItem.AmountInCompanyCodeCurrency,
      receivablePayableItem.TransactionCurrency  as TransactionCurrency,
      receivablePayableItem.CompanyCodeCurrency  as CompanyCodeCurrency,
      _TransactionCrcyText.CurrencyShortName                             as TransactionCurrencyName,
      receivablePayableItem.AmountInTransactionCurrency,      
      case 
        when receivablePayableItem.DocumentItemText = ''
          then receivablePayableItem._JournalEntry.AccountingDocumentHeaderText
        when receivablePayableItem.DocumentItemText != ''
          then  receivablePayableItem.DocumentItemText
      end as DocumentItemText,
      case when receivablePayableItem.CashDiscount2Days != 0
        then DATS_ADD_DAYS(receivablePayableItem.DueCalculationBaseDate,ceil(receivablePayableItem.CashDiscount2Days),'NULL')
        else
          case when receivablePayableItem.CashDiscount1Days != 0
          then DATS_ADD_DAYS(receivablePayableItem.DueCalculationBaseDate,ceil(receivablePayableItem.CashDiscount1Days),'NULL')
          else receivablePayableItem.DueCalculationBaseDate
          end
      end                                                                as ValueDate,
      case when receivablePayableItem.ClearingAccountingDocument != '' and receivablePayableItem.ClearingAccountingDocument != receivablePayableItem.AccountingDocument
        then case when _ClearDocJournalEntry.AlternativeReferenceDocument = ''
              then ltrim(receivablePayableItem.ClearingAccountingDocument,'0')
              else ltrim(_ClearDocJournalEntry.AlternativeReferenceDocument,'0')
        end
        else ''
      end                                                                as ClearingAccountingDocument,
      receivablePayableItem.ClearingDate                                 as ClearingDate,
      ltrim(receivablePayableItem.BillingDocument,'0')                   as BillingDocument,
      ltrim(receivablePayableItem._JournalEntry.DocumentReferenceID,'0') as DocumentReferenceID,
      ltrim(receivablePayableItem.SalesDocument,'0')                     as SalesOrder,
      receivablePayableItem.PaymentMethod,
      case when receivablePayableItem.ClearingAccountingDocument != ''
            and _CalendarDate.FiscalYear = :P_FiscalYear
            and _CalendarDate.FiscalPeriod >= :P_FromFiscalPeriod
            and _CalendarDate.FiscalPeriod <= :P_ToFiscalPeriod
        then '1'
        else '0'
      end                                                                as IsCleared,
      case when _IncgChk.Cheque != ''
        then ltrim(_IncgChk.Cheque,'0')
        else case when _BOE.boe_number != ''
              then ltrim(_BOE.boe_number,'0')
              else ''
              end
      end                                                                as BillOfExchange,
      case when receivablePayableItem.AlternativeGLAccount = ''
       then case when _AlternativeGLAccount.AlternativeGLAccount = ''
          then ltrim(_AlternativeGLAccount.AlternativeGLAccount,'0')
          else ltrim(receivablePayableItem.GLAccount,'0')
          end
       else ltrim(receivablePayableItem.AlternativeGLAccount,'0')
      end                                                                as AlternativeGLAccount,
      case when receivablePayableItem._JournalEntry.AlternativeReferenceDocument != ''
         then ltrim(receivablePayableItem._JournalEntry.AlternativeReferenceDocument,'0')
         else ltrim(receivablePayableItem.AccountingDocument,'0')
      end                                                                as AlternativeReferenceDocument,
      ltrim(receivablePayableItem.GLAccount,'0')                         as GLAccountInfo,
      ltrim(receivablePayableItem.AccountingDocument,'0')                as CAClearingDocumentNumber,
      receivablePayableItem.DebitCreditCode,
      IsNegativePosting
}
where
        receivablePayableItem.FinancialAccountType =  'D'
  and   receivablePayableItem.CompanyCode          =  :P_CompanyCode
  and(
        receivablePayableItem.FiscalYear           =  :P_FiscalYear
    and receivablePayableItem.FiscalPeriod         >= :P_FromFiscalPeriod
    and receivablePayableItem.FiscalPeriod         <= :P_ToFiscalPeriod
  )

/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_COMPANYCODE",
"I_CURRENCYTEXT",
"I_FISCCALENDARDATEFORCOMPCODE",
"I_GLACCOUNT",
"I_JOURNALENTRY",
"I_OPERATIONALACCTGDOCITEM",
"P_CN_CADEBILLOFEXCHANGEINFO2",
"P_CN_CADEINCOMINGCHECK"
],
"ASSOCIATED":
[
"I_CURRENCYTEXT",
"I_FISCCALENDARDATEFORCOMPCODE",
"I_GLACCOUNT",
"I_JOURNALENTRY",
"P_CN_CADEBILLOFEXCHANGEINFO2",
"P_CN_CADEINCOMINGCHECK"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/