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,

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

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

  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
}
where 
  rcongr = $parameters.P_ConsolidationGroup