P_CONSOLIDATEDDATA

@AbapCatalog.preserveKey: true
@AbapCatalog.sqlViewName: 'PCONSOLIDTEDDATA'
@AbapCatalog.compiler.compareFilter: true

@VDM.viewType: #COMPOSITE
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.private:true
@ClientHandling.algorithm:#SESSION_VARIABLE
//@AccessControl.authorizationCheck: #NOT_ALLOWED

//@EndUserText.label: 'EC-CS real-time access to ACDOCA'

@ObjectModel.usageType.serviceQuality: #X
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.sizeCategory: #XL
@AccessControl.personalData.blocking: #NOT_REQUIRED
@Metadata.ignorePropagatedAnnotations: true


define view P_ConsolidatedData
  // This view provides all data from consolidation (all posting levels) WITHOUT reporting logic


  //  1. Posting level <= 10

  as select
    *
  from P_StandardizedData

  //   2.  Posting level > 10

  //   2a. ACDOCU


  union all select from P_CnsldtnGroupJrnlEntryItemHC
  {
    rclnt,
    cast('' as gu_recid)           as gl_sirid,
    ConsolidationLedger            as rldnr,
    GLRecordType                   as rrcty,
    ConsolidationVersion           as rvers,
    FiscalYear                     as ryear,

    TransactionCurrency            as rtcur,
    LocalCurrency                  as rhcur,
    GroupCurrency                  as rkcur,

    BaseUnit                       as runit,
    cast('' as shkzg)              as drcrk,
    FiscalPeriod                   as poper,
    FiscalYearPeriod               as fiscyearper, //bbrCC1908

    DocumentCategory               as docct,
    CnsldtnGroupJrnlEntry          as docnr,
    CnsldtnGroupJrnlEntryItem      as docln,
    Company                        as rcomp,
    ConsolidationDimension         as rdimen,
    ConsolidationUnit              as rbunit,
    ConsolidationChartOfAccounts   as ritclg,
    FinancialStatementItem         as ritem,
    PartnerConsolidationUnit       as rbuptr,
    ConsolidationGroup             as rcongr,
    CompanyCode                    as robukrs,
    SubItemCategory                as sityp,
    SubItem                        as subit,
    PostingLevel                   as plevl,
    ConsolidationApportionment     as rpflg,
    CurrencyConversionsDiffType    as rtflg,
    ConsolidationDocumentType      as docty,
    ConsolidationAcquisitionYear   as yracq,
    ConsolidationAcquisitionPeriod as pracq,
    InvesteeConsolidationUnit      as coicu,
    InvestorConsolidationUnit      as uppcu,
    cast('000' as updhp)           as updhp,

    AmountInTransactionCurrency    as tsl,
    AmountInLocalCurrency          as hsl,
    AmountInGroupCurrency          as ksl,

    CnsldtnQuantityInBaseUnit      as msl,
    CnsldtnCstmSignAmtInTransCrcy  as tsl_csign,
    CnsldtnCstmSignAmtInLoclCrcy   as hsl_csign,
    CnsldtnCstmSignAmtInGrpCrcy    as ksl_csign,
    CnsldtnCstmSignLongQtyInBsUnit as msl_csign,
    DocumentItemText               as sgtxt,
    ConsolidationPostgItemAutoFlag as autom,
    BusinessTransactionType        as activ,
    bvorg,
    PostingDate                    as budat,
    CurrencyTranslationDate        as wsdat,
    RefConsolidationDocumentNumber as refdocnr,
    ReferenceFiscalYear            as refryear,
    RefConsolidationPostingItem    as refdocln,
    RefConsolidationDocumentType   as refdocct,
    RefBusinessTransactionType     as refactiv,
    CreationDate                   as cpudt,
    CreationTime                   as cputm,
    UserID                         as usnam,
    ReverseDocument                as rvsdocnr,
    ReversedDocument               as orndocnr,
    InvestmentActivityType         as coiac,
    InvestmentActivity             as coinr,
    ConsolidationDocReversalYear   as revyear,
    ReferenceDocumentType          as awtyp,
    ReferenceDocumentContext       as aworg,
    LogicalSystem                  as logsys,
    CreationDateTime               as timestamp,
    GLAccount                      as racct,

    CostCenter                     as rcntr,
    ProfitCenter                   as prctr,
    FunctionalArea                 as rfarea,
    BusinessArea                   as rbusa,
    ControllingArea                as kokrs,
    Segment                        as segment,
    PartnerCostCenter              as scntr,
    PartnerProfitCenter            as pprctr,
    PartnerFunctionalArea          as sfarea,
    PartnerBusinessArea            as sbusa,
    PartnerCompany                 as rassc,
    PartnerSegment                 as psegment,

    FinancialTransactionType       as rmvct,
    Material                       as matnr,
    Plant                          as werks,
    Customer                       as kunnr,
    Supplier                       as lifnr,
    WBSElementExternalID           as ps_posid,
    Project                        as ps_pspid,

    BillingDocumentType            as fkart,
    SalesOrganization              as vkorg,
    DistributionChannel            as vtweg,
    OrganizationDivision           as spart,
    SoldProduct                    as matnr_copa,
    SoldProductGroup               as matkl,
    CustomerGroup                  as kdgrp,
    CustomerSupplierCountry        as land1,
    CustomerSupplierIndustry       as brsch,
    SalesDistrict                  as bzirk,
    BillToParty                    as kunre,
    ShipToParty                    as kunwe,
    CustomerSupplierCorporateGroup as konzs,

    AssignmentReference            as zuonr,
    OrderID                        as aufnr,
    xblnr,
    MaterialGroup                  as matkl_mm,
    WBSElementInternalID           as ps_psp_pnr,
    ChartOfAccounts                as ktopl,

    //Additional Fields: AdHoc Item

    CnsldtnAdhocItem               as adhocitem,
    CnsldtnAdhocSet                as adhocset,
    CnsldtnAdhocSetItem            as adhocsetitem,

    MatchingReasonCode             as rcode,
    OriginType                     as orig_type,
    OriginReference                as orig_ref,
    CnsldtnGroupJrnlEntryBundle    as bunnr,

    // industry-specific fields  Financial services: Banking Fields

    FinancialServicesProductGroup  as fs_product_group,
    FinancialServicesBranch        as branch_id,
    FinancialDataSource            as datasource_id,

    // industry-specific fields  Financial services: Green House Fields

    GHGScope                       as ghgscope,
    GHGCategory                    as ghgcategory,
    GHGScope2CalculationMethod     as ghgscp2calcmeth,
    GHGScope2ContractualInstrument as ghgscp2contrinstr,
    GHGDataQualityCharacteristic   as ghgdtaqltycharc,
    GHGEnergyClassification        as ghgenergyclass,
    GHGEnergyMix                   as ghgenergymix,
    GHGEnergySourcingType          as ghgengysrcgtype,
    SustainabilityModeOfTransport  as susmodeoftransport,

    // industry-specific fields  Financial services: Public Sector Fields

    FinancialManagementArea        as fikrs,
    Fund                           as rfund,
    PartnerFund                    as sfund,
    GrantID                        as rgrant_nbr,
    CnsldtnCashLedgerChartOfAccts  as re_ktopl,
    CashLedgerAccount              as re_account,
    PartnerFund                    as bp_p_geber

  }
  where
         GLRecordType = '0'
    and(
         PostingLevel = '02'
      or PostingLevel = '12'
      or PostingLevel = '20'
      or PostingLevel = '21'
      or PostingLevel = '22'
      or PostingLevel = '23'
      or PostingLevel = '24'
      or PostingLevel = '30'
    )