P_Matrix_CP_GroupFilled_2

@AbapCatalog.sqlViewName: 'PMCPGRPF2'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@AccessControl.personalData.blocking: #NOT_REQUIRED
@ClientHandling.algorithm:#SESSION_VARIABLE
@ObjectModel.usageType: { sizeCategory: #XL, dataClass: #MIXED, serviceQuality: #X }
@Metadata.ignorePropagatedAnnotations: true
@VDM.private:true
@VDM.viewType: #COMPOSITE
define view P_Matrix_CP_GroupFilled_2
  with parameters
    P_ConsolidationGroup   : fc_congr,
    P_FromFiscalYearPeriod : fincs_fiscyearper,
    P_ToFiscalYearPeriod   : fincs_fiscyearper
  as select from P_CnsldtnGroupJrnlEntryItem(
                   P_FromFiscalYearPeriod : $parameters.P_FromFiscalYearPeriod,
                   P_ToFiscalYearPeriod   : $parameters.P_ToFiscalYearPeriod ) as transaction_data

    inner join   I_CnsldtnVersion                                              as version_assignment on transaction_data.ConsolidationVersion = version_assignment.ConsolidationVersion

    inner join   I_CnsldtnGroupStructure                                       as group_structure    on  group_structure.ConsolidationDimension        =       transaction_data.ConsolidationDimension
                                                                                                     and group_structure.ConsolidationStructureVersion =       version_assignment.ConsolidationStructureVersion
                                                                                                     and group_structure.ConsolidationUnit             =       transaction_data.ConsolidationUnit
                                                                                                     and group_structure.ConsolidationGroup            =       transaction_data.ConsolidationGroup
                                                                                                     and transaction_data.FiscalYearPeriod             between group_structure.FromFiscalYearPeriod and group_structure.ToFiscalYearPeriod

{
  $session.client                         as mandt,
  // Key Fields

  $session.client                         as rclnt,
  transaction_data.ConsolidationLedger    as rldnr,
  transaction_data.ConsolidationDimension as rdimen,
  transaction_data.FiscalYear             as ryear,
  CnsldtnGroupJrnlEntry                   as docnr,
  CnsldtnGroupJrnlEntryItem               as docln,

  // Common Fields

  GLRecordType                            as rrcty,
  transaction_data.ConsolidationVersion   as rvers,

  TransactionCurrency                     as rtcur,
  LocalCurrency                           as rhcur,
  transaction_data.GroupCurrency          as rkcur,
  BaseUnit                                as runit,

  cast('' as shkzg preserving type)       as drcrk,
  FiscalPeriod                            as poper,
  FiscalYearPeriod                        as fiscyearper,
  cast('PER' as fincs_period_mode)        as period_mode,
  FiscalPeriod                            as poper_posting,

  DocumentCategory                        as docct,
  Company                                 as rcomp,
  transaction_data.ConsolidationUnit      as rbunit,
  ConsolidationChartOfAccounts            as ritclg,
  FinancialStatementItem                  as ritem,
  PartnerConsolidationUnit                as rbuptr,
  transaction_data.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,

  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,
  ''                                      as bvorg, // missing in I_MatrixCnsldtnFoundation

  PostingDate                             as budat,
  CurrencyTranslationDate                 as wsdat,
  RefConsolidationDocumentNumber          as refdocnr,
  ReferenceFiscalYear                     as refryear,
  RefConsolidationPostingItem             as refdocln,
  RefConsolidationDocumentType            as refdocct,
  RefBusinessTransactionType              as refactiv,
  CreationDateTime                        as timestamp,
  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,

  // Additional Fields

  ChartOfAccounts                         as ktopl,
  GLAccount                               as racct,
  ''                                      as xblnr, // missing in I_MatrixCnsldtnFoundation

  AssignmentReference                     as zuonr,

  // Additional Fields - Universal Journal Entry: G/L additional account assignments

  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,
  OrderID                                 as aufnr,
  Customer                                as kunnr,
  Supplier                                as lifnr,
  Material                                as matnr,
  MaterialGroup                           as matkl_mm,
  Plant                                   as werks,
  FinancialTransactionType                as rmvct,
  WBSElementInternalID                    as ps_psp_pnr,
  WBSElementExternalID                    as ps_posid,
  Project                                 as ps_pspid,

  // Additional Fields - SFIN: Universal Journal Entry - Fixed CO-PA fields

  BillingDocumentType                     as fkart,
  SalesOrganization                       as vkorg,
  DistributionChannel                     as vtweg,
  OrganizationDivision                    as spart,
  SoldMaterial                            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,

  // Additional Fields - Ad Hoc Items

  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,
  GrantID                                 as rgrant_nbr,
  CnsldtnCashLedgerChartOfAccts           as re_ktopl,
  CashLedgerAccount                       as re_account,
  PartnerFund                             as bp_p_geber

}
where
  (
       GLRecordType                        = '0'
    or GLRecordType                        = 'A'
    or GLRecordType                        = 'D'
    or GLRecordType                        = 'E'
  )
  and(
       PostingLevel                        = '02'
    or PostingLevel                        = '12'
  )
  and  transaction_data.ConsolidationGroup = $parameters.P_ConsolidationGroup

union all select from P_CnsldtnGroupJrnlEntryItem(
                        P_FromFiscalYearPeriod : $parameters.P_FromFiscalYearPeriod,
                        P_ToFiscalYearPeriod   : $parameters.P_ToFiscalYearPeriod ) as transaction_data

  inner join          I_CnsldtnVersion                                              as version_assignment      on transaction_data.ConsolidationVersion = version_assignment.ConsolidationVersion

  inner join          I_CnsldtnGroupStructure                                       as unit_group_structure    on  unit_group_structure.ConsolidationDimension        =       transaction_data.ConsolidationDimension
                                                                                                               and unit_group_structure.ConsolidationStructureVersion =       version_assignment.ConsolidationStructureVersion
                                                                                                               and unit_group_structure.ConsolidationUnit             =       transaction_data.ConsolidationUnit
                                                                                                               and unit_group_structure.ConsolidationGroup            =       transaction_data.ConsolidationGroup
                                                                                                               and transaction_data.FiscalYearPeriod                  between unit_group_structure.FromFiscalYearPeriod and unit_group_structure.ToFiscalYearPeriod

  inner join          I_CnsldtnGroupStructure                                       as partner_group_structure on  partner_group_structure.ConsolidationDimension        =       transaction_data.ConsolidationDimension
                                                                                                               and partner_group_structure.ConsolidationStructureVersion =       version_assignment.ConsolidationStructureVersion
                                                                                                               and partner_group_structure.ConsolidationUnit             =       transaction_data.PartnerConsolidationUnit
                                                                                                               and partner_group_structure.ConsolidationGroup            =       transaction_data.ConsolidationGroup
                                                                                                               and transaction_data.FiscalYearPeriod                     between partner_group_structure.FromFiscalYearPeriod and partner_group_structure.ToFiscalYearPeriod
{
  $session.client                         as mandt,
  $session.client                         as rclnt,
  transaction_data.ConsolidationLedger    as rldnr,
  transaction_data.ConsolidationDimension as rdimen,
  transaction_data.FiscalYear             as ryear,
  CnsldtnGroupJrnlEntry                   as docnr,
  CnsldtnGroupJrnlEntryItem               as docln,

  // Common Fields

  GLRecordType                            as rrcty,
  transaction_data.ConsolidationVersion   as rvers,

  TransactionCurrency                     as rtcur,
  LocalCurrency                           as rhcur,
  transaction_data.GroupCurrency          as rkcur,
  BaseUnit                                as runit,

  cast('' as shkzg preserving type)       as drcrk,
  FiscalPeriod                            as poper,
  FiscalYearPeriod                        as fiscyearper,
  cast('PER' as fincs_period_mode)        as period_mode,
  FiscalPeriod                            as poper_posting,

  DocumentCategory                        as docct,
  Company                                 as rcomp,
  transaction_data.ConsolidationUnit      as rbunit,
  ConsolidationChartOfAccounts            as ritclg,
  FinancialStatementItem                  as ritem,
  PartnerConsolidationUnit                as rbuptr,
  transaction_data.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,

  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,
  ''                                      as bvorg, // missing in I_MatrixCnsldtnFoundation

  PostingDate                             as budat,
  CurrencyTranslationDate                 as wsdat,
  RefConsolidationDocumentNumber          as refdocnr,
  ReferenceFiscalYear                     as refryear,
  RefConsolidationPostingItem             as refdocln,
  RefConsolidationDocumentType            as refdocct,
  RefBusinessTransactionType              as refactiv,
  CreationDateTime                        as timestamp,
  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,

  // Additional Fields

  ChartOfAccounts                         as ktopl,
  GLAccount                               as racct,
  ''                                      as xblnr, // missing in I_MatrixCnsldtnFoundation

  AssignmentReference                     as zuonr,

  // Additional Fields - Universal Journal Entry: G/L additional account assignments

  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,
  OrderID                                 as aufnr,
  Customer                                as kunnr,
  Supplier                                as lifnr,
  Material                                as matnr,
  MaterialGroup                           as matkl_mm,
  Plant                                   as werks,
  FinancialTransactionType                as rmvct,
  WBSElementInternalID                    as ps_psp_pnr,
  WBSElementExternalID                    as ps_posid,
  Project                                 as ps_pspid,

  // Additional Fields - SFIN: Universal Journal Entry - Fixed CO-PA fields

  BillingDocumentType                     as fkart,
  SalesOrganization                       as vkorg,
  DistributionChannel                     as vtweg,
  OrganizationDivision                    as spart,
  SoldMaterial                            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,

  // Additional Fields - Ad Hoc Items

  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,
  GrantID                                 as rgrant_nbr,
  CnsldtnCashLedgerChartOfAccts           as re_ktopl,
  CashLedgerAccount                       as re_account,
  PartnerFund                             as bp_p_geber
}
where
  (
       GLRecordType                        = '0'
    or GLRecordType                        = 'A'
    or GLRecordType                        = 'D'
    or GLRecordType                        = 'E'
  )
  and  PostingLevel                        = '22'
  and  transaction_data.ConsolidationGroup = $parameters.P_ConsolidationGroup

union all select from P_Matrix_CP_GroupDerived_2(
                        P_FromFiscalYearPeriod : $parameters.P_FromFiscalYearPeriod,
                        P_ToFiscalYearPeriod   : $parameters.P_ToFiscalYearPeriod )
{
  $session.client as mandt,
  $session.client as rclnt,
  rldnr,
  rdimen,
  ryear,
  docnr,
  docln,

  // Common Fields

  rrcty,
  rvers,
  rtcur,
  rhcur,
  rkcur,
  runit,

  drcrk,
  poper,
  fiscyearper,
  period_mode,
  poper_posting,

  docct,
  rcomp,
  rbunit,
  ritclg,
  ritem,
  rbuptr,
  rcongr,
  robukrs,
  sityp,
  subit,
  plevl,
  rpflg,
  rtflg,
  docty,
  yracq,
  pracq,
  coicu,
  uppcu,

  tsl,
  hsl,
  ksl,
  msl,

  tsl_csign,
  hsl_csign,
  ksl_csign,
  msl_csign,

  sgtxt,
  autom,
  activ,
  bvorg,
  budat,
  wsdat,
  refdocnr,
  refryear,
  refdocln,
  refdocct,
  refactiv,
  timestamp,
  cpudt,
  cputm,
  usnam,
  rvsdocnr,
  orndocnr,
  coiac,
  coinr,
  revyear,
  awtyp,
  aworg,
  logsys,

  // Additional Fields

  ktopl,
  racct,
  xblnr,
  zuonr,

  // Additional Fields - Universal Journal Entry: G/L additional account assignments

  rcntr,
  prctr,
  rfarea,
  rbusa,
  kokrs,
  segment,
  scntr,
  pprctr,
  sfarea,
  sbusa,
  rassc,
  psegment,
  aufnr,
  kunnr,
  lifnr,
  matnr,
  matkl_mm,
  werks,
  rmvct,
  ps_psp_pnr,
  ps_posid,
  ps_pspid,

  // Additional Fields - SFIN: Universal Journal Entry - Fixed CO-PA fields

  fkart,
  vkorg,
  vtweg,
  spart,
  matnr_copa,
  matkl,
  kdgrp,
  land1,
  brsch,
  bzirk,
  kunre,
  kunwe,
  konzs,

  // Additional Fields - Ad Hoc Items

  adhocitem,
  adhocset,
  adhocsetitem,

  rcode,
  orig_type,
  orig_ref,
  bunnr,

  // industry-specific fields  Financial services: Banking Fields

  fs_product_group,
  branch_id,
  datasource_id,

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

  ghgscope,
  ghgcategory,
  ghgscp2calcmeth,
  ghgscp2contrinstr,
  ghgdtaqltycharc,
  ghgenergyclass,
  ghgenergymix,
  ghgengysrcgtype,
  susmodeoftransport,


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

  fikrs,
  rfund,
  rgrant_nbr,
  re_ktopl,
  re_account,
  bp_p_geber
}
where
  rcongr = $parameters.P_ConsolidationGroup