P_ACCOUNTING_DOC

@AbapCatalog.sqlViewName: 'PBOPACCDOC'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@VDM.viewType: #COMPOSITE
@VDM.private: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ObjectModel.usageType: { dataClass: #MIXED, sizeCategory: #XL, serviceQuality: #X }
@ClientHandling: { type: #INHERITED, algorithm: #SESSION_VARIABLE }
@EndUserText.label: 'Accounting document'
define view P_ACCOUNTING_DOC
  as select from    I_AccountingDocument      as doc

    join            I_OperationalAcctgDocItem as doc_itm       on  doc.CompanyCode                = doc_itm.CompanyCode
                                                               and doc.FiscalYear                 = doc_itm.FiscalYear
                                                               and doc.AccountingDocument         = doc_itm.AccountingDocument
                                                               and doc_itm.AccountingDocumentItem = '001' //We are interested only in first item of FI document


    left outer to one join P_CATotalsRecord          as ca_tot_direct on  doc.CompanyCode                            = ca_tot_direct.CompanyCode
                                                               and ca_tot_direct.CAReconciliationKey          = substring( doc.OriginalReferenceDocument, 1, 12 )

                                                               and doc_itm.DebitCreditCode                    = ca_tot_direct.DebitCreditCode
                                                               and doc_itm.GLAccount                          = ca_tot_direct.GLAccount
                                                               and doc_itm.PostingDate                        = ca_tot_direct.CAPostingDate
                                                               and doc_itm.Reference1IDByBusinessPartner      = ca_tot_direct.CATotalsRecordSequenceNumber
                                                               and ca_tot_direct.CATotalsRecdIsDrctTrnsfdToGL = 'X'
                                                               and ca_tot_direct.CANrOfDocsTrnsfdToGLRefVal   = right(  doc_itm.OriginalReferenceDocument, 7  )
                                                               and ca_tot_direct.CADocumentNumber             is not initial

    left outer to one join P_CATotalsRecord          as ca_tot_single on  doc.CompanyCode                            = ca_tot_single.CompanyCode
                                                               and ca_tot_single.CAReconciliationKey          = substring( doc.OriginalReferenceDocument, 1, 12 )
                                                               and doc_itm.DebitCreditCode                    = ca_tot_single.DebitCreditCode
                                                               and doc_itm.GLAccount                          = ca_tot_single.GLAccount
                                                               and doc_itm.PostingDate                        = ca_tot_single.CAPostingDate
                                                               and doc_itm.Reference1IDByBusinessPartner      = ca_tot_single.CATotalsRecordSequenceNumber
                                                               and ca_tot_direct.CADocumentNumber     is null
                                                               and ca_tot_single.CATotalsRecdIsDrctTrnsfdToGL  is initial
                                                               and ca_tot_single.CADocumentNumber             is not initial


    left outer to one join P_CATotalsRecord          as ca_tot_normal on  doc.CompanyCode                       = ca_tot_normal.CompanyCode
                                                               and ca_tot_normal.CAReconciliationKey     = substring( doc.OriginalReferenceDocument, 1, 12 )
                                                               and doc_itm.DebitCreditCode               = ca_tot_normal.DebitCreditCode
                                                               and doc_itm.GLAccount                     = ca_tot_normal.GLAccount
                                                               and doc_itm.PostingDate                   = ca_tot_normal.CAPostingDate
                                                               and doc_itm.Reference1IDByBusinessPartner = ca_tot_normal.CATotalsRecordSequenceNumber
                                                               and ca_tot_direct.CADocumentNumber     is null
                                                               and ca_tot_single.CADocumentNumber     is null

{

  key doc.CompanyCode,
  key doc.FiscalYear,
  key doc.AccountingDocument,
  key substring( doc.OriginalReferenceDocument, 1, 12 ) as CAReconciliationKey,
      case
          when ca_tot_direct.CADocumentNumber is not null then ca_tot_direct.CADocumentTypeForGLTransfer
          when ( ca_tot_single.CADocumentNumber is not null ) then ca_tot_single.CADocumentTypeForGLTransfer
          else ca_tot_normal.CADocumentTypeForGLTransfer
      end                                               as CADocumentTypeForGLTransfer,
      doc.AccountingDocumentType,
      ca_tot_direct.CATotalsRecdIsDrctTrnsfdToGL,
       case
          when ca_tot_direct.CADocumentNumber is not null then ca_tot_direct.CADocumentNumber
          when ( ca_tot_single.CADocumentNumber is not null ) then ca_tot_single.CADocumentNumber
          else ca_tot_normal.CADocumentNumber
      end                                               as   CADocumentNumber,
      doc.PostingDate,
      doc.DocumentDate,
      case
          when ca_tot_direct.CADocumentNumber is not null then ca_tot_direct.TaxReportingDate
          when ( ca_tot_single.CADocumentNumber is not null ) then ca_tot_single.TaxReportingDate
          else ca_tot_normal.TaxReportingDate
      end                                               as   TaxReportingDate,      
      doc.LedgerGroup,
      case
          when ca_tot_direct.CADocumentNumber is not null then ca_tot_direct.CAIsCashFlowItem
          when (  ca_tot_single.CADocumentNumber is not null ) then ca_tot_single.CAIsCashFlowItem
          else ca_tot_normal.CAIsCashFlowItem
      end                                               as CAIsCashFlowItem,
      doc_itm.TransactionCurrency,
      //Transfer type can be either DIRECT, SINGLE or NORMAL

      cast(
           case
               when ca_tot_direct.CADocumentNumber is not null then 'DIRECT'
               when (  ca_tot_single.CADocumentNumber is not null ) then 'SINGLE'
               else 'NORMAL'
           end
            as char6 )                                  as CATransferType
}
// Consider only FI documnets that are comming from FI-CA

where
  doc.ReferenceDocumentType like 'FKK%'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_ACCOUNTINGDOCUMENT",
"I_OPERATIONALACCTGDOCITEM",
"P_CATOTALSRECORD"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/