P_FinCorrCustomerDownPayment

@AccessControl.authorizationCheck: #MANDATORY
@VDM.private: true
@VDM.viewType: #COMPOSITE

define view entity P_FinCorrCustomerDownPayment

  as select from           P_FinCorr1DownPaymentItem     as FirstDPItem

    inner join             P_FinCorr1TaxItem             as FirstTaxItem on  FirstTaxItem.CompanyCode        = FirstDPItem.CompanyCode
                                                                         and FirstTaxItem.AccountingDocument = FirstDPItem.AccountingDocument
                                                                         and FirstTaxItem.FiscalYear         = FirstDPItem.FiscalYear

    inner join             I_OperationalAcctgDocItem     as AcctgDocItem on  AcctgDocItem.CompanyCode            = FirstDPItem.CompanyCode
                                                                         and AcctgDocItem.AccountingDocument     = FirstDPItem.AccountingDocument
                                                                         and AcctgDocItem.FiscalYear             = FirstDPItem.FiscalYear
                                                                         and AcctgDocItem.AccountingDocumentItem = FirstDPItem.AccountingDocumentItem

    left outer to one join I_ClrgInfoRelationForAcctgDoc as ClrgInfo     on  ClrgInfo.ClearingInfoCompanyCode    = FirstDPItem.CompanyCode
                                                                         and ClrgInfo.ClearingAccountingDocument = FirstDPItem.AccountingDocument
                                                                         and ClrgInfo.ClearingFiscalYear         = FirstDPItem.FiscalYear
                                                                         and ClrgInfo.DownPaymentItem            = FirstDPItem.AccountingDocumentItem

  association [0..1] to I_ClrgInfoRelationForAcctgDoc as _ClrgInfoReturn on  _ClrgInfoReturn.ClearingInfoCompanyCode    = ClrgInfo.CompanyCode
                                                                         and _ClrgInfoReturn.ClearingAccountingDocument = ClrgInfo.AccountingDocument
                                                                         and _ClrgInfoReturn.ClearingFiscalYear         = ClrgInfo.FiscalYear
                                                                         and _ClrgInfoReturn.DownPaymentItem            = ClrgInfo.AccountingDocumentItem

{
  key AcctgDocItem.CompanyCode,
  key AcctgDocItem.AccountingDocument,
  key AcctgDocItem.FiscalYear,

      FirstTaxItem.ReportingCountry,
      AcctgDocItem.AccountingDocumentType,
      AcctgDocItem.PostingDate,
      AcctgDocItem.DocumentDate,

      cast (case
        when AcctgDocItem.DebitCreditCode = 'H'     // down payment

          then
            case
              when ClrgInfo._AccountingDocument.ReferenceDocumentType = 'VBRK' or  // SD

                   ClrgInfo._AccountingDocument.ReferenceDocumentType = 'CF3PS'    // SD CFin

                then AcctgDocItem.AssignmentReference
              else AcctgDocItem._JournalEntry.DocumentReferenceID
            end
        when AcctgDocItem.DebitCreditCode = 'S'     // down payment return

          then
            case
              when _ClrgInfoReturn._AccountingDocument.ReferenceDocumentType = 'VBRK' or  // SD

                   _ClrgInfoReturn._AccountingDocument.ReferenceDocumentType = 'CF3PS'    // SD CFin

                then AcctgDocItem.AssignmentReference
              else AcctgDocItem._JournalEntry.DocumentReferenceID
            end
        else ''
      end as odn_value)        as OfficialDocumentNumber,

      AcctgDocItem.Customer,

      cast (case
        when AcctgDocItem._Customer.IsOneTimeAccount = 'X'
          then concat_with_space(AcctgDocItem._JournalEntryItemOneTimeData.BusinessPartnerName1,
                                 AcctgDocItem._JournalEntryItemOneTimeData.BusinessPartnerName2,
                                 1)
        else AcctgDocItem._Customer.CustomerName
      end as md_customer_name) as CustomerName,

      FirstTaxItem.VATRegistration,
      FirstTaxItem.VATRegistrationCountry,

      AcctgDocItem._CompanyCode,
      AcctgDocItem._Customer
}

where
      FirstTaxItem.ReportingCountry         =  FirstTaxItem.VATRegistrationCountry // local customer


  and AcctgDocItem.FinancialAccountType     =  'D'
  and AcctgDocItem.Customer                 <> ''

  and AcctgDocItem._JournalEntry.IsReversed =  ''
  and AcctgDocItem._JournalEntry.IsReversal =  ''
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_ACCOUNTINGDOCUMENT",
"I_CLRGINFORELATIONFORACCTGDOC",
"I_CUSTOMER",
"I_JOURNALENTRY",
"I_JOURNALENTRYITEMONETIMEDATA",
"I_OPERATIONALACCTGDOCITEM",
"P_FINCORR1DOWNPAYMENTITEM",
"P_FINCORR1TAXITEM"
],
"ASSOCIATED":
[
"I_COMPANYCODE",
"I_CUSTOMER"
],
"BASE":
[
"I_OPERATIONALACCTGDOCITEM"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/