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,
    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

  }
  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'
    )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"P_CNSLDTNGROUPJRNLENTRYITEMHC",
"P_STANDARDIZEDDATA"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/