VALUATIONACDOCAMDCON

@AbapCatalog.viewEnhancementCategory: [ #NONE ]

@AccessControl.authorizationCheck: #NOT_REQUIRED

@EndUserText.label: 'ACDOCA view for valuation'

@Metadata.ignorePropagatedAnnotations: true

@ObjectModel.usageType: { serviceQuality: #X, sizeCategory: #S, dataClass: #MIXED }

define view entity VALUATIONACDOCAMDCON


  // MDC on for normal customer items


  as select from           I_GLAccountLineItem as acdoca

    left outer to one join P_BPCreditRiskClass as _CustomerRiskClass
      on acdoca.Customer = _CustomerRiskClass.Customer

{
  key acdoca.CompanyCode,
  key acdoca.FiscalYear,
  key acdoca.AccountingDocument,
  key acdoca.LedgerGLLineItem,
  key acdoca.Ledger,
  key acdoca.SourceLedger,

      cast('X' as boole_d)                                                                                              as p_is_mdc_on,
      acdoca.AccountingDocumentItem,


      acdoca.ClearingAccountingDocument,
      acdoca.ClearingDocFiscalYear,
      acdoca.ClearingDate,
      acdoca.NetDueDate,
      acdoca.ChartOfAccounts,
      acdoca.FiscalYearPeriod,
      acdoca.FinancialAccountType,
      acdoca.SalesDocument                                                                                              as SalesOrder,
      acdoca.SalesDocumentItem                                                                                          as SalesOrderItem,
      acdoca.InvoiceReference,
      acdoca.InvoiceItemReference,
      acdoca.InvoiceReferenceFiscalYear,
      acdoca.FollowOnDocumentType,
      acdoca._PostingKey,
      acdoca.IsReversed,
      // filtering

      acdoca.IsReversal,
      acdoca.OrganizationalChange,
      acdoca._SupplierCompany,
      acdoca._CustomerCompany,

      cast(
      coalesce(acdoca._CustomerCompany.ReconciliationAccount, '') as akont
      )                                                                                                                 as GLAccount,

      acdoca.GLAccount                                                                                                  as OriginalGlAccount,

      acdoca.CreationDateTime,
      acdoca.SubLedgerAcctLineItemType,
      acdoca.DocumentDate,
      acdoca.PostingDate,
      acdoca.AccountingDocumentCategory,
      acdoca.LedgerFiscalYear,
      acdoca.ReferenceDocumentType,
      acdoca.IsOpenItemManaged,
      acdoca._PostingKey.DebitCreditCode,
      acdoca.PostingKey,
      acdoca.BusinessTransactionCategory,
      acdoca.GLAccountType,
      acdoca.AssignmentReference,

      acdoca._Customer,
      acdoca._Supplier,

      acdoca.FiscalPeriod,
      acdoca.OffsettingAccount,
      acdoca.WBSElement,

      acdoca.BalanceTransactionCurrency,
      acdoca.CompanyCodeCurrency,
      acdoca.GlobalCurrency,
      acdoca.FreeDefinedCurrency1,
      acdoca.FreeDefinedCurrency2,
      acdoca.FreeDefinedCurrency3,
      acdoca.FreeDefinedCurrency4,
      acdoca.FreeDefinedCurrency5,
      acdoca.FreeDefinedCurrency6,
      acdoca.FreeDefinedCurrency7,
      acdoca.FreeDefinedCurrency8,

      @Semantics.amount.currencyCode: 'BalanceTransactionCurrency'
      acdoca.AmountInBalanceTransacCrcy,

      @Semantics.amount.currencyCode: 'CompanyCodeCurrency'
      acdoca.AmountInCompanyCodeCurrency,

      @Semantics.amount.currencyCode: 'GlobalCurrency'
      acdoca.AmountInGlobalCurrency,

      @Semantics.amount.currencyCode: 'FreeDefinedCurrency1'
      acdoca.AmountInFreeDefinedCurrency1,

      @Semantics.amount.currencyCode: 'FreeDefinedCurrency2'
      acdoca.AmountInFreeDefinedCurrency2,

      @Semantics.amount.currencyCode: 'FreeDefinedCurrency3'
      acdoca.AmountInFreeDefinedCurrency3,

      @Semantics.amount.currencyCode: 'FreeDefinedCurrency4'
      acdoca.AmountInFreeDefinedCurrency4,

      @Semantics.amount.currencyCode: 'FreeDefinedCurrency5'
      acdoca.AmountInFreeDefinedCurrency5,

      @Semantics.amount.currencyCode: 'FreeDefinedCurrency6'
      acdoca.AmountInFreeDefinedCurrency6,

      @Semantics.amount.currencyCode: 'FreeDefinedCurrency7'
      acdoca.AmountInFreeDefinedCurrency7,

      @Semantics.amount.currencyCode: 'FreeDefinedCurrency8'
      acdoca.AmountInFreeDefinedCurrency8,

      acdoca.TaxCode,
      acdoca.TaxCountry,
      cast(case acdoca.GLAccountType when 'C' then acdoca.HouseBank else '' end  as farp_hbkid)                         as HouseBank,
      cast(case acdoca.GLAccountType when 'C' then acdoca.HouseBankAccount else '' end as fac_hktid)                    as HouseBankAccount,
      acdoca.CostCtrActivityType,
      acdoca.WBSElementInternalID,
      acdoca.FinancialTransactionType,
      acdoca.JointVenture,
      acdoca.JointVentureEquityGroup,
      acdoca.PartnerVenture,
      acdoca.JointVenturePartner,
      acdoca.JointVentureCostRecoveryCode,
      acdoca.JointVentureEquityType,
      acdoca.ProjectNetwork,
      acdoca.RelatedNetworkActivity,
      acdoca.OrderID,
      acdoca.BusinessProcess,
      acdoca.CostObject,
      acdoca.CostCenter,
      acdoca.ProfitCenter,
      acdoca.FunctionalArea,
      acdoca.BusinessArea,
      acdoca.ControllingArea,
      acdoca.Segment,
      acdoca.PartnerCostCenter,
      acdoca.PartnerProfitCenter,
      acdoca.PartnerFunctionalArea,
      acdoca.PartnerBusinessArea,

      cast(coalesce(acdoca._Customer._CustomerToBusinessPartner._BusinessPartner._BPFinancialServicesExt.TradingPartner, '')  as rassc)
                                                                                                                        as PartnerCompany,

      acdoca.PartnerSegment,
      acdoca.Supplier,
      acdoca.Customer,
      acdoca.FinancialValuationObjectType,
      acdoca.FinancialValuationObject,
      acdoca.FinancialValuationSubobject,
      acdoca.REBusinessEntity,
      acdoca.RealEstateBuilding,
      acdoca.RealEstateProperty,
      acdoca.Fund,
      acdoca.BudgetPeriod,
      acdoca.GrantID,
      acdoca.PubSecBudgetAccount,
      acdoca.CashLedgerAccount,
      acdoca.SponsoredProgram,
      acdoca.SponsoredClass,
      acdoca.Plant,
      acdoca.Product,
      acdoca.ShipToParty,
      acdoca.PartnerFund,
      acdoca.PartnerGrant,
      acdoca.CashLedgerCompanyCode,
      acdoca.FundsCenter,
      acdoca.PartnerEquityGroup,
      acdoca.FundedProgram,
      acdoca.PartnerBudgetPeriod,
      acdoca.AccrualObjectType,
      acdoca.AccrualObject,
      acdoca.AccrualSubobject,
      acdoca.AccrualItemType,
      acdoca.AccrualReferenceObject,
      acdoca.AccrualObjectLogicalSystem,
      acdoca.PubSecBudgetAccountCoCode,
      acdoca.ServiceDocument,
      acdoca.ServiceDocumentItem,
      acdoca.ServiceDocumentType,
      acdoca.Material,
      acdoca.PredecessorJournalEntry,
      acdoca.PrdcssrJournalEntryFiscalYear,
      acdoca.PredecessorJournalEntryItem,
      acdoca.PredecessorReferenceDocCntxt,
      acdoca.PredecessorReferenceDocument,
      acdoca.PredecessorReferenceDocItem,
      acdoca.PrdcssrJournalEntryCompanyCode,
      acdoca.GLRecordType,

      cast(coalesce(acdoca._Customer.CustomerCorporateGroup, ' ') as konzs)                                             as CUSTOMER_SUPPLIER_GROUP_KEY,

      cast(
      case when acdoca.RelatedNetworkActivity is not initial
      then concat(concat(acdoca.OrderCategory, '/'), acdoca.AccountAssignment)
      else '' end        as cls_run_nplnr_vorgn_helper)                                                                 as relatednetworkactivityinternal,

      cast(coalesce(acdoca._Customer._CustomerToBusinessPartner._BusinessPartner.BusinessPartner, '') as bu_partner)    as BusinessPartner,

      cast(coalesce(_CustomerRiskClass._CreditManagement.CreditRiskClass, '') as ukm_risk_class)                        as CreditRiskClass,
      acdoca.CreditRiskClass                                                                                            as OriginalCreditRiskClass,

      acdoca.SpecialGLCode                                                                                              as SpecialGLCode,

      cast(coalesce(acdoca._CustomerCompany.ReconciliationAccount, '') as akont)                                        as MASTERDATARECNCLNACCT,

      cast(coalesce(acdoca._Customer._CustomerToBusinessPartner._BusinessPartner._BPFinancialServicesExt.TradingPartner, '') as rassc)
                                                                                                                        as MASTERDATAPARTNERCOMPANY
}

where acdoca.Customer      is not initial
  and acdoca.SpecialGLCode is initial

// MDC on for normal supplier items

union all

  select from I_GLAccountLineItem as acdoca

{
  key acdoca.CompanyCode,
  key acdoca.FiscalYear,
  key acdoca.AccountingDocument,
  key acdoca.LedgerGLLineItem,
  key acdoca.Ledger,
  key acdoca.SourceLedger,

      cast('X' as boole_d)                                                                                              as p_is_mdc_on,
      acdoca.AccountingDocumentItem,


      acdoca.ClearingAccountingDocument,
      acdoca.ClearingDocFiscalYear,
      acdoca.ClearingDate,
      acdoca.NetDueDate,
      acdoca.ChartOfAccounts,
      acdoca.FiscalYearPeriod,
      acdoca.FinancialAccountType,
      acdoca.SalesDocument                                                                                              as SalesOrder,
      acdoca.SalesDocumentItem                                                                                          as SalesOrderItem,
      acdoca.InvoiceReference,
      acdoca.InvoiceItemReference,
      acdoca.InvoiceReferenceFiscalYear,
      acdoca.FollowOnDocumentType,
      acdoca._PostingKey,
      acdoca.IsReversed,
      // filtering

      acdoca.IsReversal,
      acdoca.OrganizationalChange,
      acdoca._SupplierCompany,
      acdoca._CustomerCompany,

      cast(
    coalesce(acdoca._SupplierCompany.ReconciliationAccount, '') as akont
    )                                                                                                                   as GLAccount,

      acdoca.GLAccount                                                                                                  as OriginalGlAccount,

      acdoca.CreationDateTime,
      acdoca.SubLedgerAcctLineItemType,
      acdoca.DocumentDate,
      acdoca.PostingDate,
      acdoca.AccountingDocumentCategory,
      acdoca.LedgerFiscalYear,
      acdoca.ReferenceDocumentType,
      acdoca.IsOpenItemManaged,
      acdoca._PostingKey.DebitCreditCode,
      acdoca.PostingKey,
      acdoca.BusinessTransactionCategory,
      acdoca.GLAccountType,
      acdoca.AssignmentReference,

      acdoca._Customer,
      acdoca._Supplier,

      acdoca.FiscalPeriod,
      acdoca.OffsettingAccount,
      acdoca.WBSElement,

      acdoca.BalanceTransactionCurrency,
      acdoca.CompanyCodeCurrency,
      acdoca.GlobalCurrency,
      acdoca.FreeDefinedCurrency1,
      acdoca.FreeDefinedCurrency2,
      acdoca.FreeDefinedCurrency3,
      acdoca.FreeDefinedCurrency4,
      acdoca.FreeDefinedCurrency5,
      acdoca.FreeDefinedCurrency6,
      acdoca.FreeDefinedCurrency7,
      acdoca.FreeDefinedCurrency8,

      acdoca.AmountInBalanceTransacCrcy,
      acdoca.AmountInCompanyCodeCurrency,
      acdoca.AmountInGlobalCurrency,
      acdoca.AmountInFreeDefinedCurrency1,
      acdoca.AmountInFreeDefinedCurrency2,
      acdoca.AmountInFreeDefinedCurrency3,
      acdoca.AmountInFreeDefinedCurrency4,
      acdoca.AmountInFreeDefinedCurrency5,
      acdoca.AmountInFreeDefinedCurrency6,
      acdoca.AmountInFreeDefinedCurrency7,
      acdoca.AmountInFreeDefinedCurrency8,

      acdoca.TaxCode,
      acdoca.TaxCountry,
      cast(case acdoca.GLAccountType when 'C' then acdoca.HouseBank else '' end  as farp_hbkid)                         as HouseBank,
      cast(case acdoca.GLAccountType when 'C' then acdoca.HouseBankAccount else '' end as fac_hktid)                    as HouseBankAccount,
      acdoca.CostCtrActivityType,
      acdoca.WBSElementInternalID,
      acdoca.FinancialTransactionType,
      acdoca.JointVenture,
      acdoca.JointVentureEquityGroup,
      acdoca.PartnerVenture,
      acdoca.JointVenturePartner,
      acdoca.JointVentureCostRecoveryCode,
      acdoca.JointVentureEquityType,
      acdoca.ProjectNetwork,
      acdoca.RelatedNetworkActivity,
      acdoca.OrderID,
      acdoca.BusinessProcess,
      acdoca.CostObject,
      acdoca.CostCenter,
      acdoca.ProfitCenter,
      acdoca.FunctionalArea,
      acdoca.BusinessArea,
      acdoca.ControllingArea,
      acdoca.Segment,
      acdoca.PartnerCostCenter,
      acdoca.PartnerProfitCenter,
      acdoca.PartnerFunctionalArea,
      acdoca.PartnerBusinessArea,

      cast(coalesce(acdoca._Supplier._SupplierToBusinessPartner._BusinessPartner._BPFinancialServicesExt.TradingPartner, '') as rassc)
                                                                                                                        as PartnerCompany,

      acdoca.PartnerSegment,
      acdoca.Supplier,
      acdoca.Customer,
      acdoca.FinancialValuationObjectType,
      acdoca.FinancialValuationObject,
      acdoca.FinancialValuationSubobject,
      acdoca.REBusinessEntity,
      acdoca.RealEstateBuilding,
      acdoca.RealEstateProperty,
      acdoca.Fund,
      acdoca.BudgetPeriod,
      acdoca.GrantID,
      acdoca.PubSecBudgetAccount,
      acdoca.CashLedgerAccount,
      acdoca.SponsoredProgram,
      acdoca.SponsoredClass,
      acdoca.Plant,
      acdoca.Product,
      acdoca.ShipToParty,
      acdoca.PartnerFund,
      acdoca.PartnerGrant,
      acdoca.CashLedgerCompanyCode,
      acdoca.FundsCenter,
      acdoca.PartnerEquityGroup,
      acdoca.FundedProgram,
      acdoca.PartnerBudgetPeriod,
      acdoca.AccrualObjectType,
      acdoca.AccrualObject,
      acdoca.AccrualSubobject,
      acdoca.AccrualItemType,
      acdoca.AccrualReferenceObject,
      acdoca.AccrualObjectLogicalSystem,
      acdoca.PubSecBudgetAccountCoCode,
      acdoca.ServiceDocument,
      acdoca.ServiceDocumentItem,
      acdoca.ServiceDocumentType,
      acdoca.Material,
      acdoca.PredecessorJournalEntry,
      acdoca.PrdcssrJournalEntryFiscalYear,
      acdoca.PredecessorJournalEntryItem,
      acdoca.PredecessorReferenceDocCntxt,
      acdoca.PredecessorReferenceDocument,
      acdoca.PredecessorReferenceDocItem,
      acdoca.PrdcssrJournalEntryCompanyCode,
      acdoca.GLRecordType,

      cast(coalesce(acdoca._Supplier.SupplierCorporateGroup, ' ') as konzs)                                             as CUSTOMER_SUPPLIER_GROUP_KEY,

      cast(
      case when acdoca.RelatedNetworkActivity is not initial
      then concat(concat(acdoca.OrderCategory, '/'), acdoca.AccountAssignment)
      else '' end        as cls_run_nplnr_vorgn_helper)                                                                 as relatednetworkactivityinternal,

      cast(coalesce(acdoca._Supplier._SupplierToBusinessPartner._BusinessPartner.BusinessPartner, '') as bu_partner)    as BusinessPartner,

      cast('' as ukm_risk_class)                                                                                        as CreditRiskClass,
      acdoca.CreditRiskClass                                                                                            as OriginalCreditRiskClass,

      acdoca.SpecialGLCode                                                                                              as SpecialGLCode,

      cast(coalesce(acdoca._SupplierCompany.ReconciliationAccount, '') as     akont)                                    as MASTERDATARECNCLNACCT,

      cast(coalesce(acdoca._Supplier._SupplierToBusinessPartner._BusinessPartner._BPFinancialServicesExt.TradingPartner, '') as rassc)
                                                                                                                        as MASTERDATAPARTNERCOMPANY
}

where acdoca.Supplier      is not initial
  and acdoca.SpecialGLCode is initial

// MDC on customer special GL

union all
  select from              I_GLAccountLineItem         as acdoca

    left outer to one join P_BPCreditRiskClass         as _CustomerRiskClass
      on acdoca.Customer = _CustomerRiskClass.Customer

    left outer to one join P_FinClsgMDChgCusSpclGLAcct as _specialglaccount
      on  acdoca.CompanyCode   = _specialglaccount.CompanyCode
      and acdoca.SpecialGLCode = _specialglaccount.SpecialGLCode
      and acdoca.Customer      = _specialglaccount.Customer

{
  key acdoca.CompanyCode,
  key acdoca.FiscalYear,
  key acdoca.AccountingDocument,
  key acdoca.LedgerGLLineItem,
  key acdoca.Ledger,
  key acdoca.SourceLedger,

      cast('X' as boole_d)                                                                                              as p_is_mdc_on,
      acdoca.AccountingDocumentItem,


      acdoca.ClearingAccountingDocument,
      acdoca.ClearingDocFiscalYear,
      acdoca.ClearingDate,
      acdoca.NetDueDate,
      acdoca.ChartOfAccounts,
      acdoca.FiscalYearPeriod,
      acdoca.FinancialAccountType,
      acdoca.SalesDocument                                                                                              as SalesOrder,
      acdoca.SalesDocumentItem                                                                                          as SalesOrderItem,
      acdoca.InvoiceReference,
      acdoca.InvoiceItemReference,
      acdoca.InvoiceReferenceFiscalYear,
      acdoca.FollowOnDocumentType,
      acdoca._PostingKey,
      acdoca.IsReversed,
      // filtering

      acdoca.IsReversal,
      acdoca.OrganizationalChange,
      acdoca._SupplierCompany,
      acdoca._CustomerCompany,

      cast(
    coalesce(_specialglaccount.SpecialGLAccount, '') as akont
    )                                                                                                                   as GLAccount,

      acdoca.GLAccount                                                                                                  as OriginalGlAccount,

      acdoca.CreationDateTime,
      acdoca.SubLedgerAcctLineItemType,
      acdoca.DocumentDate,
      acdoca.PostingDate,
      acdoca.AccountingDocumentCategory,
      acdoca.LedgerFiscalYear,
      acdoca.ReferenceDocumentType,
      acdoca.IsOpenItemManaged,
      acdoca._PostingKey.DebitCreditCode,
      acdoca.PostingKey,
      acdoca.BusinessTransactionCategory,
      acdoca.GLAccountType,
      acdoca.AssignmentReference,

      acdoca._Customer,
      acdoca._Supplier,

      acdoca.FiscalPeriod,
      acdoca.OffsettingAccount,
      acdoca.WBSElement,

      acdoca.BalanceTransactionCurrency,
      acdoca.CompanyCodeCurrency,
      acdoca.GlobalCurrency,
      acdoca.FreeDefinedCurrency1,
      acdoca.FreeDefinedCurrency2,
      acdoca.FreeDefinedCurrency3,
      acdoca.FreeDefinedCurrency4,
      acdoca.FreeDefinedCurrency5,
      acdoca.FreeDefinedCurrency6,
      acdoca.FreeDefinedCurrency7,
      acdoca.FreeDefinedCurrency8,

      acdoca.AmountInBalanceTransacCrcy,
      acdoca.AmountInCompanyCodeCurrency,
      acdoca.AmountInGlobalCurrency,
      acdoca.AmountInFreeDefinedCurrency1,
      acdoca.AmountInFreeDefinedCurrency2,
      acdoca.AmountInFreeDefinedCurrency3,
      acdoca.AmountInFreeDefinedCurrency4,
      acdoca.AmountInFreeDefinedCurrency5,
      acdoca.AmountInFreeDefinedCurrency6,
      acdoca.AmountInFreeDefinedCurrency7,
      acdoca.AmountInFreeDefinedCurrency8,

      acdoca.TaxCode,
      acdoca.TaxCountry,
      cast(case acdoca.GLAccountType when 'C' then acdoca.HouseBank else '' end  as farp_hbkid)                         as HouseBank,
      cast(case acdoca.GLAccountType when 'C' then acdoca.HouseBankAccount else '' end as fac_hktid)                    as HouseBankAccount,
      acdoca.CostCtrActivityType,
      acdoca.WBSElementInternalID,
      acdoca.FinancialTransactionType,
      acdoca.JointVenture,
      acdoca.JointVentureEquityGroup,
      acdoca.PartnerVenture,
      acdoca.JointVenturePartner,
      acdoca.JointVentureCostRecoveryCode,
      acdoca.JointVentureEquityType,
      acdoca.ProjectNetwork,
      acdoca.RelatedNetworkActivity,
      acdoca.OrderID,
      acdoca.BusinessProcess,
      acdoca.CostObject,
      acdoca.CostCenter,
      acdoca.ProfitCenter,
      acdoca.FunctionalArea,
      acdoca.BusinessArea,
      acdoca.ControllingArea,
      acdoca.Segment,
      acdoca.PartnerCostCenter,
      acdoca.PartnerProfitCenter,
      acdoca.PartnerFunctionalArea,
      acdoca.PartnerBusinessArea,

      cast(coalesce(acdoca._Customer._CustomerToBusinessPartner._BusinessPartner._BPFinancialServicesExt.TradingPartner, ' ') as rassc)
                                                                                                                        as PartnerCompany,

      acdoca.PartnerSegment,
      acdoca.Supplier,
      acdoca.Customer,
      acdoca.FinancialValuationObjectType,
      acdoca.FinancialValuationObject,
      acdoca.FinancialValuationSubobject,
      acdoca.REBusinessEntity,
      acdoca.RealEstateBuilding,
      acdoca.RealEstateProperty,
      acdoca.Fund,
      acdoca.BudgetPeriod,
      acdoca.GrantID,
      acdoca.PubSecBudgetAccount,
      acdoca.CashLedgerAccount,
      acdoca.SponsoredProgram,
      acdoca.SponsoredClass,
      acdoca.Plant,
      acdoca.Product,
      acdoca.ShipToParty,
      acdoca.PartnerFund,
      acdoca.PartnerGrant,
      acdoca.CashLedgerCompanyCode,
      acdoca.FundsCenter,
      acdoca.PartnerEquityGroup,
      acdoca.FundedProgram,
      acdoca.PartnerBudgetPeriod,
      acdoca.AccrualObjectType,
      acdoca.AccrualObject,
      acdoca.AccrualSubobject,
      acdoca.AccrualItemType,
      acdoca.AccrualReferenceObject,
      acdoca.AccrualObjectLogicalSystem,
      acdoca.PubSecBudgetAccountCoCode,
      acdoca.ServiceDocument,
      acdoca.ServiceDocumentItem,
      acdoca.ServiceDocumentType,
      acdoca.Material,
      acdoca.PredecessorJournalEntry,
      acdoca.PrdcssrJournalEntryFiscalYear,
      acdoca.PredecessorJournalEntryItem,
      acdoca.PredecessorReferenceDocCntxt,
      acdoca.PredecessorReferenceDocument,
      acdoca.PredecessorReferenceDocItem,
      acdoca.PrdcssrJournalEntryCompanyCode,
      acdoca.GLRecordType,

      coalesce(acdoca._Customer.CustomerCorporateGroup, ' ')                                                            as CUSTOMER_SUPPLIER_GROUP_KEY,

      cast(
      case when acdoca.RelatedNetworkActivity is not initial
      then concat(concat(acdoca.OrderCategory, '/'), acdoca.AccountAssignment)
      else '' end        as cls_run_nplnr_vorgn_helper)                                                                 as relatednetworkactivityinternal,

      cast(coalesce(acdoca._Customer._CustomerToBusinessPartner._BusinessPartner.BusinessPartner, '') as bu_partner)    as BusinessPartner,

      cast(coalesce(_CustomerRiskClass._CreditManagement.CreditRiskClass, '') as ukm_risk_class)                        as CreditRiskClass,
      acdoca.CreditRiskClass                                                                                            as OriginalCreditRiskClass,

      acdoca.SpecialGLCode                                                                                              as SpecialGLCode,

      cast(coalesce(_specialglaccount.SpecialGLAccount,  '')  as saknr)
                                                                                                                        as MASTERDATARECNCLNACCT,

      cast(coalesce(acdoca._Customer._CustomerToBusinessPartner._BusinessPartner._BPFinancialServicesExt.TradingPartner, '') as rassc)
                                                                                                                        as MASTERDATAPARTNERCOMPANY
}

where acdoca.SpecialGLCode is not initial
  and acdoca.Customer      is not initial

// MDC on for special supplier items

union all

  select from              I_GLAccountLineItem         as acdoca

    left outer to one join P_FinClsgMDChgSupSpclGLAcct as _specialglaccount
      on  acdoca.CompanyCode   = _specialglaccount.CompanyCode
      and acdoca.SpecialGLCode = _specialglaccount.SpecialGLCode
      and acdoca.Supplier      = _specialglaccount.Supplier

{
  key acdoca.CompanyCode,
  key acdoca.FiscalYear,
  key acdoca.AccountingDocument,
  key acdoca.LedgerGLLineItem,
  key acdoca.Ledger,
  key acdoca.SourceLedger,

      cast('X' as boole_d)                                                                                              as p_is_mdc_on,
      acdoca.AccountingDocumentItem,


      acdoca.ClearingAccountingDocument,
      acdoca.ClearingDocFiscalYear,
      acdoca.ClearingDate,
      acdoca.NetDueDate,
      acdoca.ChartOfAccounts,
      acdoca.FiscalYearPeriod,
      acdoca.FinancialAccountType,
      acdoca.SalesDocument                                                                                              as SalesOrder,
      acdoca.SalesDocumentItem                                                                                          as SalesOrderItem,
      acdoca.InvoiceReference,
      acdoca.InvoiceItemReference,
      acdoca.InvoiceReferenceFiscalYear,
      acdoca.FollowOnDocumentType,
      acdoca._PostingKey,
      acdoca.IsReversed,
      // filtering

      acdoca.IsReversal,
      acdoca.OrganizationalChange,
      acdoca._SupplierCompany,
      acdoca._CustomerCompany,


      cast(
    coalesce(_specialglaccount.SpecialGLAccount, '') as akont
    )                                                                                                                   as GLAccount,

      acdoca.GLAccount                                                                                                  as OriginalGlAccount,

      acdoca.CreationDateTime,
      acdoca.SubLedgerAcctLineItemType,
      acdoca.DocumentDate,
      acdoca.PostingDate,
      acdoca.AccountingDocumentCategory,
      acdoca.LedgerFiscalYear,
      acdoca.ReferenceDocumentType,
      acdoca.IsOpenItemManaged,
      acdoca._PostingKey.DebitCreditCode,
      acdoca.PostingKey,
      acdoca.BusinessTransactionCategory,
      acdoca.GLAccountType,
      acdoca.AssignmentReference,

      acdoca._Customer,
      acdoca._Supplier,

      acdoca.FiscalPeriod,
      acdoca.OffsettingAccount,
      acdoca.WBSElement,

      acdoca.BalanceTransactionCurrency,
      acdoca.CompanyCodeCurrency,
      acdoca.GlobalCurrency,
      acdoca.FreeDefinedCurrency1,
      acdoca.FreeDefinedCurrency2,
      acdoca.FreeDefinedCurrency3,
      acdoca.FreeDefinedCurrency4,
      acdoca.FreeDefinedCurrency5,
      acdoca.FreeDefinedCurrency6,
      acdoca.FreeDefinedCurrency7,
      acdoca.FreeDefinedCurrency8,

      acdoca.AmountInBalanceTransacCrcy,
      acdoca.AmountInCompanyCodeCurrency,
      acdoca.AmountInGlobalCurrency,
      acdoca.AmountInFreeDefinedCurrency1,
      acdoca.AmountInFreeDefinedCurrency2,
      acdoca.AmountInFreeDefinedCurrency3,
      acdoca.AmountInFreeDefinedCurrency4,
      acdoca.AmountInFreeDefinedCurrency5,
      acdoca.AmountInFreeDefinedCurrency6,
      acdoca.AmountInFreeDefinedCurrency7,
      acdoca.AmountInFreeDefinedCurrency8,

      acdoca.TaxCode,
      acdoca.TaxCountry,
      cast(case acdoca.GLAccountType when 'C' then acdoca.HouseBank else '' end  as farp_hbkid)                         as HouseBank,
      cast(case acdoca.GLAccountType when 'C' then acdoca.HouseBankAccount else '' end as fac_hktid)                    as HouseBankAccount,
      acdoca.CostCtrActivityType,
      acdoca.WBSElementInternalID,
      acdoca.FinancialTransactionType,
      acdoca.JointVenture,
      acdoca.JointVentureEquityGroup,
      acdoca.PartnerVenture,
      acdoca.JointVenturePartner,
      acdoca.JointVentureCostRecoveryCode,
      acdoca.JointVentureEquityType,
      acdoca.ProjectNetwork,
      acdoca.RelatedNetworkActivity,
      acdoca.OrderID,
      acdoca.BusinessProcess,
      acdoca.CostObject,
      acdoca.CostCenter,
      acdoca.ProfitCenter,
      acdoca.FunctionalArea,
      acdoca.BusinessArea,
      acdoca.ControllingArea,
      acdoca.Segment,
      acdoca.PartnerCostCenter,
      acdoca.PartnerProfitCenter,
      acdoca.PartnerFunctionalArea,
      acdoca.PartnerBusinessArea,

      cast(coalesce(acdoca._Supplier._SupplierToBusinessPartner._BusinessPartner._BPFinancialServicesExt.TradingPartner, '') as rassc)
                                                                                                                        as PartnerCompany,

      acdoca.PartnerSegment,
      acdoca.Supplier,
      acdoca.Customer,
      acdoca.FinancialValuationObjectType,
      acdoca.FinancialValuationObject,
      acdoca.FinancialValuationSubobject,
      acdoca.REBusinessEntity,
      acdoca.RealEstateBuilding,
      acdoca.RealEstateProperty,
      acdoca.Fund,
      acdoca.BudgetPeriod,
      acdoca.GrantID,
      acdoca.PubSecBudgetAccount,
      acdoca.CashLedgerAccount,
      acdoca.SponsoredProgram,
      acdoca.SponsoredClass,
      acdoca.Plant,
      acdoca.Product,
      acdoca.ShipToParty,
      acdoca.PartnerFund,
      acdoca.PartnerGrant,
      acdoca.CashLedgerCompanyCode,
      acdoca.FundsCenter,
      acdoca.PartnerEquityGroup,
      acdoca.FundedProgram,
      acdoca.PartnerBudgetPeriod,
      acdoca.AccrualObjectType,
      acdoca.AccrualObject,
      acdoca.AccrualSubobject,
      acdoca.AccrualItemType,
      acdoca.AccrualReferenceObject,
      acdoca.AccrualObjectLogicalSystem,
      acdoca.PubSecBudgetAccountCoCode,
      acdoca.ServiceDocument,
      acdoca.ServiceDocumentItem,
      acdoca.ServiceDocumentType,
      acdoca.Material,
      acdoca.PredecessorJournalEntry,
      acdoca.PrdcssrJournalEntryFiscalYear,
      acdoca.PredecessorJournalEntryItem,
      acdoca.PredecessorReferenceDocCntxt,
      acdoca.PredecessorReferenceDocument,
      acdoca.PredecessorReferenceDocItem,
      acdoca.PrdcssrJournalEntryCompanyCode,
      acdoca.GLRecordType,

      cast(coalesce(acdoca._Supplier.SupplierCorporateGroup, ' ') as konzs)                                             as CUSTOMER_SUPPLIER_GROUP_KEY,

      cast(
      case when acdoca.RelatedNetworkActivity is not initial
      then concat(concat(acdoca.OrderCategory, '/'), acdoca.AccountAssignment)
      else '' end        as cls_run_nplnr_vorgn_helper)                                                                 as relatednetworkactivityinternal,

      cast(coalesce(acdoca._Supplier._SupplierToBusinessPartner._BusinessPartner.BusinessPartner, '') as bu_partner)    as BusinessPartner,

      cast('' as ukm_risk_class)                                                                                        as CreditRiskClass,
      acdoca.CreditRiskClass                                                                                            as OriginalCreditRiskClass,

      acdoca.SpecialGLCode                                                                                              as SpecialGLCode,

      cast(coalesce(_specialglaccount.SpecialGLAccount, '')  as saknr)
                                                                                                                        as MASTERDATARECNCLNACCT,

      cast(coalesce(acdoca._Supplier._SupplierToBusinessPartner._BusinessPartner._BPFinancialServicesExt.TradingPartner, '') as rassc)
                                                                                                                        as MASTERDATAPARTNERCOMPANY
}

where acdoca.Supplier      is not initial
  and acdoca.SpecialGLCode is not initial

// normal OIM without customer or supplier

union all
  select from I_GLAccountLineItem as acdoca

{
  key acdoca.CompanyCode,
  key acdoca.FiscalYear,
  key acdoca.AccountingDocument,
  key acdoca.LedgerGLLineItem,
  key acdoca.Ledger,
  key acdoca.SourceLedger,

      cast('X' as boole_d)                                                                           as p_is_mdc_on,
      acdoca.AccountingDocumentItem,


      acdoca.ClearingAccountingDocument,
      acdoca.ClearingDocFiscalYear,
      acdoca.ClearingDate,
      acdoca.NetDueDate,
      acdoca.ChartOfAccounts,
      acdoca.FiscalYearPeriod,
      acdoca.FinancialAccountType,
      acdoca.SalesDocument                                                                           as SalesOrder,
      acdoca.SalesDocumentItem                                                                       as SalesOrderItem,
      acdoca.InvoiceReference,
      acdoca.InvoiceItemReference,
      acdoca.InvoiceReferenceFiscalYear,
      acdoca.FollowOnDocumentType,
      acdoca._PostingKey,
      acdoca.IsReversed,
      // filtering

      acdoca.IsReversal,
      acdoca.OrganizationalChange,
      acdoca._SupplierCompany,
      acdoca._CustomerCompany,

      acdoca.GLAccount                                                                               as GLAccount,

      acdoca.GLAccount                                                                               as OriginalGlAccount,

      acdoca.CreationDateTime,
      acdoca.SubLedgerAcctLineItemType,
      acdoca.DocumentDate,
      acdoca.PostingDate,
      acdoca.AccountingDocumentCategory,
      acdoca.LedgerFiscalYear,
      acdoca.ReferenceDocumentType,
      acdoca.IsOpenItemManaged,
      acdoca._PostingKey.DebitCreditCode,
      acdoca.PostingKey,
      acdoca.BusinessTransactionCategory,
      acdoca.GLAccountType,
      acdoca.AssignmentReference,

      acdoca._Customer,
      acdoca._Supplier,

      acdoca.FiscalPeriod,
      acdoca.OffsettingAccount,
      acdoca.WBSElement,

      acdoca.BalanceTransactionCurrency,
      acdoca.CompanyCodeCurrency,
      acdoca.GlobalCurrency,
      acdoca.FreeDefinedCurrency1,
      acdoca.FreeDefinedCurrency2,
      acdoca.FreeDefinedCurrency3,
      acdoca.FreeDefinedCurrency4,
      acdoca.FreeDefinedCurrency5,
      acdoca.FreeDefinedCurrency6,
      acdoca.FreeDefinedCurrency7,
      acdoca.FreeDefinedCurrency8,

      acdoca.AmountInBalanceTransacCrcy,

      acdoca.AmountInCompanyCodeCurrency,

      acdoca.AmountInGlobalCurrency,

      acdoca.AmountInFreeDefinedCurrency1,

      acdoca.AmountInFreeDefinedCurrency2,

      acdoca.AmountInFreeDefinedCurrency3,

      acdoca.AmountInFreeDefinedCurrency4,

      acdoca.AmountInFreeDefinedCurrency5,

      acdoca.AmountInFreeDefinedCurrency6,

      acdoca.AmountInFreeDefinedCurrency7,

      acdoca.AmountInFreeDefinedCurrency8,

      acdoca.TaxCode,
      acdoca.TaxCountry,
      cast(case acdoca.GLAccountType when 'C' then acdoca.HouseBank else '' end  as farp_hbkid)      as HouseBank,
      cast(case acdoca.GLAccountType when 'C' then acdoca.HouseBankAccount else '' end as fac_hktid) as HouseBankAccount,
      acdoca.CostCtrActivityType,
      acdoca.WBSElementInternalID,
      acdoca.FinancialTransactionType,
      acdoca.JointVenture,
      acdoca.JointVentureEquityGroup,
      acdoca.PartnerVenture,
      acdoca.JointVenturePartner,
      acdoca.JointVentureCostRecoveryCode,
      acdoca.JointVentureEquityType,
      acdoca.ProjectNetwork,
      acdoca.RelatedNetworkActivity,
      acdoca.OrderID,
      acdoca.BusinessProcess,
      acdoca.CostObject,
      acdoca.CostCenter,
      acdoca.ProfitCenter,
      acdoca.FunctionalArea,
      acdoca.BusinessArea,
      acdoca.ControllingArea,
      acdoca.Segment,
      acdoca.PartnerCostCenter,
      acdoca.PartnerProfitCenter,
      acdoca.PartnerFunctionalArea,
      acdoca.PartnerBusinessArea,


      acdoca.PartnerCompany                                                                          as PartnerCompany,

      acdoca.PartnerSegment,
      acdoca.Supplier,
      acdoca.Customer,
      acdoca.FinancialValuationObjectType,
      acdoca.FinancialValuationObject,
      acdoca.FinancialValuationSubobject,
      acdoca.REBusinessEntity,
      acdoca.RealEstateBuilding,
      acdoca.RealEstateProperty,
      acdoca.Fund,
      acdoca.BudgetPeriod,
      acdoca.GrantID,
      acdoca.PubSecBudgetAccount,
      acdoca.CashLedgerAccount,
      acdoca.SponsoredProgram,
      acdoca.SponsoredClass,
      acdoca.Plant,
      acdoca.Product,
      acdoca.ShipToParty,
      acdoca.PartnerFund,
      acdoca.PartnerGrant,
      acdoca.CashLedgerCompanyCode,
      acdoca.FundsCenter,
      acdoca.PartnerEquityGroup,
      acdoca.FundedProgram,
      acdoca.PartnerBudgetPeriod,
      acdoca.AccrualObjectType,
      acdoca.AccrualObject,
      acdoca.AccrualSubobject,
      acdoca.AccrualItemType,
      acdoca.AccrualReferenceObject,
      acdoca.AccrualObjectLogicalSystem,
      acdoca.PubSecBudgetAccountCoCode,
      acdoca.ServiceDocument,
      acdoca.ServiceDocumentItem,
      acdoca.ServiceDocumentType,
      acdoca.Material,
      acdoca.PredecessorJournalEntry,
      acdoca.PrdcssrJournalEntryFiscalYear,
      acdoca.PredecessorJournalEntryItem,
      acdoca.PredecessorReferenceDocCntxt,
      acdoca.PredecessorReferenceDocument,
      acdoca.PredecessorReferenceDocItem,
      acdoca.PrdcssrJournalEntryCompanyCode,
      acdoca.GLRecordType,

      cast(' '  as konzs)                                                                            as CUSTOMER_SUPPLIER_GROUP_KEY,

      cast(
      case when acdoca.RelatedNetworkActivity is not initial
      then concat(concat(acdoca.OrderCategory, '/'), acdoca.AccountAssignment)
      else '' end        as cls_run_nplnr_vorgn_helper)                                              as relatednetworkactivityinternal,

      cast(''   as bu_partner)                                                                       as BusinessPartner,

      cast('' as ukm_risk_class)                                                                     as CreditRiskClass,
      acdoca.CreditRiskClass                                                                         as OriginalCreditRiskClass,

      acdoca.SpecialGLCode                                                                           as SpecialGLCode,

      acdoca.GLAccount                                                                               as MASTERDATARECNCLNACCT,

      acdoca.PartnerCompany                                                                          as MASTERDATAPARTNERCOMPANY
}

where Customer is initial and Supplier is initial