FACVD_GLMC_OIT

@AbapCatalog.sqlViewName: 'FACV_GLMC_OIT'
@EndUserText.label: 'GL Open Items select to clearing'

@ObjectModel: {
    usageType.serviceQuality: #C,
    usageType.sizeCategory: #XXL,
    usageType.dataClass: #TRANSACTIONAL
}
@ClientHandling.algorithm: #SESSION_VARIABLE
@Metadata.ignorePropagatedAnnotations: true
@AccessControl.authorizationCheck: #CHECK
@Search.searchable: true
// This annotation is not taken into account -> DPC_EXT implementation necessary

// See ticket: https://support.wdf.sap.corp/sap/support/message/1980378622

// As soon as HANA has solved Bug 231457 (https://support.wdf.sap.corp/sap/support/message/1980331755 ) remove first hint here and in DPC_EXT

@Consumption.dbHints: ['NO_JOIN_THRU_UNION', 'NO_RECOMPILE_WITH_SQL_PARAMETERS']  


define view FACVD_GLMC_OIT
  as select from           bseg

    left outer to one join bkpf              on  bkpf.bukrs = bseg.bukrs
                                             and bkpf.gjahr = bseg.gjahr
                                             and bkpf.belnr = bseg.belnr
                                             and bkpf.budat = bseg.h_budat

    left outer to one join t001              on t001.bukrs = bseg.bukrs

    inner join             skb1              on  skb1.bukrs    = bseg.bukrs
                                             and skb1.saknr    = bseg.hkont
                                             and skb1.x_uj_clr = ''
                                             
    /*left outer join*/    
    //There is exactly one representative ledger for a ledger group    

    association[0..1]  to  fagl_tldgrp_map   on  fagl_tldgrp_map.ldgrp     = bkpf.ldgrp
                                             and fagl_tldgrp_map.represent = 'X'                                             

    /*left outer join*/        
    association[0..1]  to  ska1              on  ska1.ktopl = t001.ktopl
                                             and ska1.saknr = bseg.hkont

    /*left outer join*/        
    association[0..1] to   FACTF_GLMC_OIT_01 on  FACTF_GLMC_OIT_01.AccountingDocument = bseg.belnr
                                             and FACTF_GLMC_OIT_01.CompanyCode        = bseg.bukrs
                                             and FACTF_GLMC_OIT_01.FiscalYear         = bseg.gjahr

    /*left outer join*/        
    association[0..1] to   FACVD_GLMC_OIT_02 on  FACVD_GLMC_OIT_02.AccountingDocument = bseg.belnr
                                             and FACVD_GLMC_OIT_02.CompanyCode        = bseg.bukrs
                                             and FACVD_GLMC_OIT_02.HouseBank          = bseg.hbkid
                                             and FACVD_GLMC_OIT_02.HouseBankAccount   = bseg.hktid
                                             and FACVD_GLMC_OIT_02.GLAccount          = bseg.hkont

    association[0..1] to   I_HouseBankAccount on  I_HouseBankAccount.CompanyCode      = bseg.bukrs
                                             and I_HouseBankAccount.HouseBank         = bseg.hbkid
                                             and I_HouseBankAccount.HouseBankAccount  = bseg.hktid

{
  key bseg.bukrs                                   as CompanyCode,
      @SearchIndex : { text : { enabled, async: false, textAnalysis.mode: #EXTENDED },
                       fuzzy.enabled : true }
  key bseg.belnr                                   as AccountingDocument,
  key bseg.gjahr                                   as FiscalYear,
  key cast(lpad(bseg.buzei,6,'0') as fins_awitem ) as AccountingDocumentItem,

      //Workaround because LPAD causes performance issue

      buzei                                        as buzei,

      // 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,
      bseg.hkont                                   as GeneralLedgerAccount,
      @Search.defaultSearchElement
      @Search.ranking : #HIGH
      @SearchIndex : { text : { enabled, async: false, textAnalysis.mode: #EXTENDED },
                       fuzzy.enabled : true }
      bseg.h_blart                                 as AccountingDocumentType,
      @Search.defaultSearchElement
      @Search.ranking : #HIGH
      @SearchIndex : { text : { enabled, async: false, textAnalysis.mode: #EXTENDED },
                       fuzzy.enabled : true }
      bseg.zuonr                                   as AssignmentReference,
      bseg.gsber                                   as BusinessArea,
      bseg.shkzg                                   as DebitCreditCode,
      bseg.h_bldat                                 as DocumentDate,
      @Search.defaultSearchElement
      @Search.ranking : #HIGH
      @SearchIndex : { text : { enabled, async: false, textAnalysis.mode: #EXTENDED },
                       fuzzy.enabled : true }
      bseg.sgtxt                                   as DocumentItemText,
      bseg.koart                                   as FinancialAccountType,
      bseg.rebzt                                   as FollowOnDocumentType,
      bseg.fkber                                   as FunctionalArea,
      bseg.hbkid                                   as HouseBank,
      bseg.hktid                                   as HouseBannkAccount,
      bseg.rebzz                                   as InvoiceItemReference,
      bseg.rebzg                                   as InvoiceReference,
      bseg.rebzj                                   as InvoiceReferenceFiscalYear,
      bseg.vbund                                   as PartnerCompany,
      bseg.pprct                                   as PartnerProfitCenter,
      bseg.psegment                                as PartnerSegment,
      bseg.werks                                   as Plant,
      bseg.h_budat                                 as PostingDate,
      bseg.prctr                                   as ProfitCenter,
      bseg.segment                                 as Segment,
      bseg.mwskz                                   as TaxCode,
      bseg.h_monat                                 as FiscalPeriod,
      bseg.h_bstat                                 as AccountingDocumentCategory,
      bseg.aufnr                                   as Orders, //to be renamed

      t001.butxt                                   as CompanyCodeName,
      bseg.pswsl                                   as BalanceTransCrcy,
      bseg.xopvw                                   as IsOpenItemManaged,
      bseg.xlgclr                                  as ClearingIsLedgerGroupSpecific,
      bseg.xref1                                   as Reference1IDByBusinessPartner,
      bseg.xref2                                   as Reference2IDByBusinessPartner,
      bseg.xref3                                   as Reference3IDByBusinessPartner,
      bseg.bewar                                   as FinancialTransactionType,
      bseg.vname                                   as JointVenture,
      bseg.recid                                   as RecoveryIndicator,
      bseg.egrup                                   as EquityGroup,
      bkpf.ldgrp                                   as LedgerGroup,

      @Search.defaultSearchElement
      @Search.ranking : #HIGH
      @SearchIndex : { text : { enabled, async: false, textAnalysis.mode: #EXTENDED },
                       fuzzy.enabled : true }
      bkpf.xblnr                                   as DocumentReference,
      @Search.defaultSearchElement
      @Search.ranking : #HIGH
      @SearchIndex : { text : { enabled, async: false, textAnalysis.mode: #EXTENDED },
                       fuzzy.enabled : true }
      bkpf.bktxt                                   as AccountingDocumentHeaderText,

      @Semantics.currencyCode: true
      bseg.h_waers                                 as TransactionCurrency,
      @Semantics.amount.currencyCode: 'TransactionCurrency'
      case bseg.shkzg  when 'H' then -bseg.wrbtr
                       when 'S' then  bseg.wrbtr
                       else           bseg.wrbtr
      end                                          as AmountInTransCrcy,
      @Semantics.amount.currencyCode: 'TransactionCurrency'
      bseg.wrbtr                                   as AmountInTransCrcyWithNoSign,

      @Semantics.currencyCode: true
      bseg.h_hwaer                                 as CompanyCodeCurrency,
      @Semantics.amount.currencyCode: 'CompanyCodeCurrency'
      case bseg.shkzg  when 'H' then -bseg.dmbtr
                       when 'S' then  bseg.dmbtr
                       else           bseg.dmbtr
      end                                          as AmountInCoCodeCrcy,
      @Semantics.amount.currencyCode: 'CompanyCodeCurrency'
      bseg.dmbtr                                   as AmountInCoCodeCrcyWithNoSign,
      
      @Semantics.currencyCode: true
      bseg.rfccur                                  as FunctionalCurrency,
      @Semantics.amount.currencyCode: 'FunctionalCurrency'
      case bseg.shkzg  when 'H' then -bseg.fcsl
                           when 'S' then  bseg.fcsl
                           else           bseg.fcsl
      end                                          as AmountInFunctionalCurrency,
      @Semantics.amount.currencyCode: 'FunctionalCurrency'
      bseg.fcsl                                    as AmountInFunctCrcyWithNoSign,

      @Semantics.currencyCode: true
      bseg.pycur                                   as PaymentCurrency,
      @Semantics.amount.currencyCode: 'PaymentCurrency'
      case bseg.shkzg  when 'H' then -bseg.pyamt
                       when 'S' then  bseg.pyamt
                       else           bseg.pyamt
      end                                          as AmountInPaymentCurrency,
      @Semantics.amount.currencyCode: 'PaymentCurrency'
      bseg.pyamt                                   as AmountInPaytCrcyWithNoSign,
      
      skb1.begru                                   as GLAccountAuthorizationGroup,
      FACTF_GLMC_OIT_01.Cheque                     as Cheque,
      FACVD_GLMC_OIT_02.PaymentReference           as PaymentReference,

      skb1.altkt                                   as AlternativeGLAccount,

      //representative ledger is used for authorization check

      case
           when fagl_tldgrp_map.rldnr is null
               then ''
           else fagl_tldgrp_map.rldnr
      end                                          as RepresentativeLedger,
      bseg.valut                                   as ValueDate,

      //house bank account auth. group is used for authorization check

      //it is relevant for cash accounts only

      case
        when ska1.glaccount_type = 'C'
            then I_HouseBankAccount.AuthorizationGroup
        else ''
      end                                          as HouseBankAcctAuthznGroup

}
where
      bseg.koart   =  'S'
  and bseg.augbl   =  ''
  and bseg.h_bstat <> 'D' //Recurring entry document

  and bseg.h_bstat <> 'M' //Sample document

  and bseg.h_bstat <> 'O' //Financial Statements: Open Entry

  and bseg.h_bstat <> 'J' //Closing Operations

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

  and bseg.augdt   =  '00000000'
  and bkpf.xstov   <> 'X'
//no restriction to xopvw = 'X' or xlgclr = 'X'; the caller decides which open items shall be selected


union all

select from              bseg_add

  left outer to one join bkpf              on  bkpf.bukrs = bseg_add.bukrs
                                           and bkpf.gjahr = bseg_add.gjahr
                                           and bkpf.belnr = bseg_add.belnr
                                           
  inner join             skb1              on  skb1.bukrs    = bseg_add.bukrs
                                           and skb1.saknr    = bseg_add.hkont   
                                           and skb1.x_uj_clr = ''                                                                                

  /*left outer join*/        
  association[0..*] to   fagl_tldgrp_map   on  fagl_tldgrp_map.ldgrp     = bkpf.ldgrp
                                           and fagl_tldgrp_map.represent = 'X'

  /*left outer to one join*/ 
  association[0..1] to   t001              on t001.bukrs = bseg_add.bukrs //get the company information




  /*left outer join*/       
  association[0..1] to   FACTF_GLMC_OIT_01 on  FACTF_GLMC_OIT_01.AccountingDocument = bseg_add.belnr
                                           and FACTF_GLMC_OIT_01.CompanyCode        = bseg_add.bukrs
                                           and FACTF_GLMC_OIT_01.FiscalYear         = bseg_add.gjahr

{
  key bseg_add.bukrs                               as CompanyCode,
      @SearchIndex : { text : { enabled, async: false, textAnalysis.mode: #EXTENDED },
                       fuzzy.enabled : true }
  key bseg_add.belnr                               as AccountingDocument,
  key bseg_add.gjahr                               as FiscalYear,
  key bseg_add.buzei                               as AccountingDocumentItem,

      //Workaround (see above)

      cast (right(bseg_add.buzei, 3) as fis_buzei) as buzei,

      // 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,
      bseg_add.hkont                               as GeneralLedgerAccount,

      @Search.defaultSearchElement
      @Search.ranking : #HIGH
      @SearchIndex : { text : { enabled, async: false, textAnalysis.mode: #EXTENDED },
                       fuzzy.enabled : true }
      bkpf.blart                                   as AccountingDocumentType,
      @Search.defaultSearchElement
      @Search.ranking : #HIGH
      @SearchIndex : { text : { enabled, async: false, textAnalysis.mode: #EXTENDED },
                       fuzzy.enabled : true }
      bseg_add.zuonr                               as AssignmentReference,
      bseg_add.gsber                               as BusinessArea,
      bseg_add.shkzg                               as DebitCreditCode,
      bkpf.bldat                                   as DocumentDate,
      @Search.defaultSearchElement
      @Search.ranking : #HIGH
      @SearchIndex : { text : { enabled, async: false, textAnalysis.mode: #EXTENDED },
                       fuzzy.enabled : true }
      bseg_add.sgtxt                               as DocumentItemText,
      bseg_add.koart                               as FinancialAccountType,
      bseg_add.rebzt                               as FollowOnDocumentType,
      bseg_add.fkber                               as FunctionalArea,
      cast ('' as hbkid )                          as HouseBank,
      cast ('' as hktid )                          as HouseBannkAccount,
      bseg_add.rebzz                               as InvoiceItemReference,
      bseg_add.rebzg                               as InvoiceReference,
      bseg_add.rebzj                               as InvoiceReferenceFiscalYear,
      bseg_add.vbund                               as PartnerCompany,
      bseg_add.pprct                               as PartnerProfitCenter,
      bseg_add.psegment                            as PartnerSegment,
      bseg_add.werks                               as Plant,
      bkpf.budat                                   as PostingDate,
      bseg_add.prctr                               as ProfitCenter,
      bseg_add.segment                             as Segment,
      bseg_add.mwskz                               as TaxCode,
      bkpf.monat                                   as FiscalPeriod,
      bkpf.bstat                                   as AccountingDocumentCategory,
      bseg_add.aufnr                               as Orders, //to be renamed

      t001.butxt                                   as CompanyCodeName,
      bseg_add.pswsl                               as BalanceTransCrcy,
      bseg_add.xopvw                               as IsOpenItemManaged,
      bseg_add.xlgclr                              as ClearingIsLedgerGroupSpecific,
      bseg_add.xref1                               as Reference1IDByBusinessPartner,
      bseg_add.xref2                               as Reference2IDByBusinessPartner,
      bseg_add.xref3                               as Reference3IDByBusinessPartner,
      bseg_add.bewar                               as FinancialTransactionType,
      bseg_add.vname                               as JointVenture,
      bseg_add.recid                               as RecoveryIndicator,
      bseg_add.egrup                               as EquityGroup,
      bkpf.ldgrp                                   as LedgerGroup,

      @Search.defaultSearchElement
      @Search.ranking : #HIGH
      @SearchIndex : { text : { enabled, async: false, textAnalysis.mode: #EXTENDED },
                       fuzzy.enabled : true }
      bkpf.xblnr                                   as DocumentReference,
      @Search.defaultSearchElement
      @Search.ranking : #HIGH
      @SearchIndex : { text : { enabled, async: false, textAnalysis.mode: #EXTENDED },
                       fuzzy.enabled : true }
      bkpf.bktxt                                   as AccountingDocumentHeaderText,

      @Semantics.currencyCode: true
      bkpf.waers                                   as TransactionCurrency,
      @Semantics.amount.currencyCode: 'TransactionCurrency'
      case bseg_add.shkzg  when 'H' then -bseg_add.wrbtr
                       when 'S' then  bseg_add.wrbtr
                       else           bseg_add.wrbtr
      end                                          as AmountInTransCrcy,
      @Semantics.amount.currencyCode: 'TransactionCurrency'
      bseg_add.wrbtr                               as AmountInTransCrcyWithNoSign,

      @Semantics.currencyCode: true
      bkpf.hwaer                                   as CompanyCodeCurrency,
      @Semantics.amount.currencyCode: 'CompanyCodeCurrency'
      case bseg_add.shkzg  when 'H' then -bseg_add.dmbtr
                       when 'S' then  bseg_add.dmbtr
                       else           bseg_add.dmbtr
      end                                          as AmountInCoCodeCrcy,
      @Semantics.amount.currencyCode: 'CompanyCodeCurrency'
      bseg_add.dmbtr                               as AmountInCoCodeCrcyWithNoSign,
      
      @Semantics.currencyCode: true
      bseg_add.rfccur                              as FunctionalCurrency,
      @Semantics.amount.currencyCode: 'FunctionalCurrency'
      case bseg_add.shkzg  when 'H' then -bseg_add.fcsl
                           when 'S' then  bseg_add.fcsl
                           else           bseg_add.fcsl
      end                                          as AmountInFunctionalCurrency,
      @Semantics.amount.currencyCode: 'FunctionalCurrency'
      bseg_add.fcsl                                as AmountInFunctCrcyWithNoSign,
            
      @Semantics.currencyCode: true
      cast ('' as pycur )                          as PaymentCurrency,
      @Semantics.amount.currencyCode: 'PaymentCurrency'
      cast (0 as pyamt )                           as AmountInPaymentCurrency,
      @Semantics.amount.currencyCode: 'PaymentCurrency'
      cast (0 as pyamt )                           as AmountInPaytCrcyWithNoSign,     

      skb1.begru                                   as GLAccountAuthorizationGroup,
      FACTF_GLMC_OIT_01.Cheque                     as Cheque,
      cast ('' as renum )                          as PaymentReference,

      skb1.altkt                                   as AlternativeGLAccount,

      //representative ledger is used for authorization check

      case
           when fagl_tldgrp_map.rldnr is null
               then ''
           else fagl_tldgrp_map.rldnr
      end                                          as RepresentativeLedger,
      bseg_add.valut                               as ValueDate,
      
      cast ('' as bank_account_auth_group )        as HouseBankAcctAuthznGroup

}
where
      bseg_add.koart  =  'S'
  and bseg_add.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 bseg_add.augdt  =  '00000000'
  and bseg_add.xlgclr =  'X'
  and bkpf.xstov      <> 'X'

union all

//--------------------------------------------------------------------------------------------------------------

// Those items come frpm accounts where the open item management is done on universal journal entries only.

// This view restrict to items on accounts where X_UJ_CLR is set. The two views above select from accounts

// where this flag is not set.

//--------------------------------------------------------------------------------------------------------------

select from P_FINS_GL_OPENITEMS as gl_open_item
{

  key CompanyCode,
  key AccountingDocument,
  key FiscalYear,
      @SearchIndex : { text : { enabled, async: false, textAnalysis.mode: #EXTENDED },
                     fuzzy.enabled : true }
  key AccountingDocumentItem,

      //Workaround (see above)

      '000' as buzei,

      tmpid,
      GeneralLedgerAccount,

      @Search.defaultSearchElement
      @Search.ranking : #HIGH
      @SearchIndex : { text : { enabled, async: false, textAnalysis.mode: #EXTENDED },
                       fuzzy.enabled : true }
      AccountingDocumentType,

      @Search.defaultSearchElement
      @Search.ranking : #HIGH
      @SearchIndex : { text : { enabled, async: false, textAnalysis.mode: #EXTENDED },
                       fuzzy.enabled : true }
      AssignmentReference,

      BusinessArea,
      DebitCreditCode,
      DocumentDate,

      @Search.defaultSearchElement
      @Search.ranking : #HIGH
      @SearchIndex : { text : { enabled, async: false, textAnalysis.mode: #EXTENDED },
                       fuzzy.enabled : true }
      DocumentItemText,

      FinancialAccountType,
      FollowOnDocumentType,
      FunctionalArea,
      HouseBank,
      HouseBannkAccount,
      InvoiceItemReference,
      InvoiceReference,
      InvoiceReferenceFiscalYear,
      PartnerCompany,
      PartnerProfitCenter,
      PartnerSegment,
      Plant,
      PostingDate,
      ProfitCenter,
      Segment,
      TaxCode,
      FiscalPeriod,
      AccountingDocumentCategory,
      Orders,
      CompanyCodeName,
      BalanceTransCrcy,
      IsOpenItemManaged,
      ClearingIsLedgerGroupSpecific,
      Reference1IDByBusinessPartner,
      Reference2IDByBusinessPartner,
      Reference3IDByBusinessPartner,      
      FinancialTransactionType,
      JointVenture,
      RecoveryIndicator,
      EquityGroup,
      LedgerGroup,

      @Search.defaultSearchElement
      @Search.ranking : #HIGH
      @SearchIndex : { text : { enabled, async: false, textAnalysis.mode: #EXTENDED },
                       fuzzy.enabled : true }
      DocumentReference,

      @Search.defaultSearchElement
      @Search.ranking : #HIGH
      @SearchIndex : { text : { enabled, async: false, textAnalysis.mode: #EXTENDED },
                       fuzzy.enabled : true }
      AccountingDocumentHeaderText,

      @Semantics.currencyCode: true
      TransactionCurrency,
      @Semantics.amount.currencyCode: 'TransactionCurrency'
      AmountInTransCrcy,
      @Semantics.amount.currencyCode: 'TransactionCurrency'
      AmountInTransCrcyWithNoSign,

      @Semantics.currencyCode: true
      CompanyCodeCurrency,
      @Semantics.amount.currencyCode: 'CompanyCodeCurrency'
      AmountInCoCodeCrcy,
      @Semantics.amount.currencyCode: 'CompanyCodeCurrency'
      AmountInCoCodeCrcyWithNoSign,
      
      @Semantics.currencyCode: true
      FunctionalCurrency,
      @Semantics.amount.currencyCode: 'FunctionalCurrency'
      AmountInFunctionalCurrency,
      @Semantics.amount.currencyCode: 'FunctionalCurrency'
      AmountInFunctCrcyWithNoSign, 

      @Semantics.currencyCode: true
      cast ('' as pycur )                          as PaymentCurrency,
      @Semantics.amount.currencyCode: 'PaymentCurrency'
      cast (0 as pyamt )                           as AmountInPaymentCurrency,
      @Semantics.amount.currencyCode: 'PaymentCurrency'
      cast (0 as pyamt )                           as AmountInPaytCrcyWithNoSign,   
      
      GLAccountAuthorizationGroup,
      Cheque,
      PaymentReference,
      AlternativeGLAccount,
      RepresentativeLedger,
      ValueDate,
      cast ('' as bank_account_auth_group ) as HouseBankAcctAuthznGroup
}