P_FINS_GL_OPENITEMS

@AbapCatalog.sqlViewName: 'PFINSGLOPENITEMS'
@AbapCatalog.preserveKey:true
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.authorizationCheck: #NOT_REQUIRED
@AbapCatalog.compiler.compareFilter:true
@VDM.private: true
@VDM.viewType: #BASIC

define view P_FINS_GL_OPENITEMS
  as select from acdoca

    left outer to one join bkpf                       on  bkpf.bukrs = acdoca.rbukrs
                                                      and bkpf.gjahr = acdoca.gjahr
                                                      and bkpf.belnr = acdoca.belnr
                                                      and bkpf.budat = acdoca.budat

    inner join   P_FINS_REPRESENTIVE_LEDGER as ledger on  ledger.ldgrp = bkpf.ldgrp
                                                      and ledger.rldnr = acdoca.rldnr

    inner join             skb1                       on  skb1.bukrs    = acdoca.rbukrs
                                                      and skb1.saknr    = acdoca.racct
                                                      and skb1.x_uj_clr = 'X'
                                                      
    association[0..1] to t001                         on t001.bukrs = acdoca.rbukrs


{
  key acdoca.rbukrs                           as CompanyCode,
  key acdoca.belnr                            as AccountingDocument,
  key acdoca.gjahr                            as FiscalYear,
  key acdoca.awitem                           as AccountingDocumentItem,

      // TMPID is a transient field for the session context. We need to bind it in SADL to be able to retrieve the data in GET_ENTITY in the session context.

      // Here we just set an initial value, the transient value is set in GET_ENTITYSET.

      cast (''      as fdc_accdoc_tmp_doc_id) as tmpid,
      acdoca.racct                            as GeneralLedgerAccount,
      acdoca.blart                            as AccountingDocumentType,
      acdoca.zuonr                            as AssignmentReference,
      acdoca.rbusa                            as BusinessArea,
      acdoca.drcrk                            as DebitCreditCode,
      acdoca.bldat                            as DocumentDate,
      acdoca.sgtxt                            as DocumentItemText,
      acdoca.koart                            as FinancialAccountType,
      acdoca.rebzt                            as FollowOnDocumentType,
      acdoca.rfarea                           as FunctionalArea,
      acdoca.hbkid                            as HouseBank,
      acdoca.hktid                            as HouseBannkAccount,
      acdoca.rebzz                            as InvoiceItemReference,
      acdoca.rebzg                            as InvoiceReference,
      acdoca.rebzj                            as InvoiceReferenceFiscalYear,
      acdoca.rassc                            as PartnerCompany,
      acdoca.psegment                         as PartnerProfitCenter,
      acdoca.psegment                         as PartnerSegment,
      acdoca.werks                            as Plant,
      acdoca.budat                            as PostingDate,
      acdoca.prctr                            as ProfitCenter,
      acdoca.segment                          as Segment,
      acdoca.mwskz                            as TaxCode,
      bkpf.monat                              as FiscalPeriod,
      bkpf.bstat                              as AccountingDocumentCategory,
      acdoca.aufnr                            as Orders, //to be renamed

      t001.butxt                              as CompanyCodeName,
      acdoca.rtcur                            as BalanceTransCrcy,
      'X'                                     as IsOpenItemManaged,

      //Those five fields are not in ACDOCA

      ' '                                     as ClearingIsLedgerGroupSpecific,
      cast('' as xref1)                       as Reference1IDByBusinessPartner,
      cast('' as xref2)                       as Reference2IDByBusinessPartner,
      cast('' as xref3)                       as Reference3IDByBusinessPartner,
      acdoca.vname                            as JointVenture,
      acdoca.recid                            as RecoveryIndicator,
      acdoca.egrup                            as EquityGroup,
      acdoca.rmvct                            as FinancialTransactionType,

      bkpf.ldgrp                              as LedgerGroup,
      bkpf.xblnr                              as DocumentReference,
      bkpf.bktxt                              as AccountingDocumentHeaderText,

      bkpf.waers                              as TransactionCurrency,
      acdoca.wsl                              as AmountInTransCrcy,
      abs(acdoca.wsl)                         as AmountInTransCrcyWithNoSign,  //to sort by absolute amount

      bkpf.hwaer                              as CompanyCodeCurrency,
      acdoca.hsl                              as AmountInCoCodeCrcy,
      abs(acdoca.hsl)                         as AmountInCoCodeCrcyWithNoSign, //to sort by absolute amount

      acdoca.rfccur                           as FunctionalCurrency,
      acdoca.fcsl                             as AmountInFunctionalCurrency,
      abs(acdoca.fcsl)                        as AmountInFunctCrcyWithNoSign, 

      skb1.begru                              as GLAccountAuthorizationGroup,

      //Those two fields are not in ACDOCA

      cast('' as chect)                       as Cheque,
      cast('' as vblnr)                       as PaymentReference,

      skb1.altkt                              as AlternativeGLAccount,

      //used for authorization check

      ledger.rldnr                            as RepresentativeLedger,
      acdoca.valut                            as ValueDate          
}
where
      acdoca.koart =  'S'
  and acdoca.augbl =  ''
  and bkpf.bstat   <> 'D' //Recurring entry document

  and bkpf.bstat   <> 'M' //Sample document

  and bkpf.bstat   <> 'O' //Financial Statements: Open Entry

  and bkpf.bstat   <> 'J' //Closing Operations

  and bkpf.awtyp   <> 'GLYEC' //Year-End Closing Doc

  and acdoca.augdt =  '00000000'
  and bkpf.xstov   <> 'X'