P_ProjPlnBudgetLineItms

@AbapCatalog.sqlViewName: 'PPRJBYPLNBGT'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@AbapCatalog.buffering.status: #NOT_ALLOWED
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.dataClass:  #MIXED
@ObjectModel.usageType.serviceQuality: #D
@VDM.viewType: #COMPOSITE
@VDM.private: true
@ClientHandling.algorithm: #SESSION_VARIABLE

define view P_ProjPlnBudgetLineItms
  as select from           P_ProjectsWithProjCat6       as Project
    inner join             I_FinancialPlanningEntryItem as _ProjCstLineItm      on _ProjCstLineItm.ProjectInternalID = Project.ProjectInternalID
    inner join             I_ControllingArea            as _ContArea            on _ContArea.ControllingArea = _ProjCstLineItm.ControllingArea
    inner join             P_PROJECTSEMTAGACC           as _SemTagGL            on  _ProjCstLineItm.ControllingArea = _SemTagGL.ControllingArea
                                                                                and _ProjCstLineItm.GLAccount       = _SemTagGL.HierarchyNodeVal
                                                                                and _ProjCstLineItm.ChartOfAccounts = _SemTagGL.HierarchyNodeClass
    inner join             P_GetRlvtSemanticTagForProj  as _AllCost             on _AllCost.ProjectInternalID = _ProjCstLineItm.ProjectInternalID
    left outer to one join I_WBSElementBasicData        as _WBSElementBasicData on _ProjCstLineItm.WBSElementInternalID = _WBSElementBasicData.WBSElementInternalID
    left outer to one join I_EnterpriseProject          as _ProjectSummary      on _ProjectSummary.ProjectInternalID = _ProjCstLineItm.ProjectInternalID
  association [0..1] to I_FiscalPeriodForVariant as _FiscalPeriod on  $projection.LedgerFiscalYear  = _FiscalPeriod.FiscalYear
                                                                  and $projection.FiscalPeriod      = _FiscalPeriod.FiscalPeriod
                                                                  and $projection.FiscalYearVariant = _FiscalPeriod.FiscalYearVariant
{
  key  cast(cast('' as abap.char (2)) as fins_ledger_pers )                                                                                as SourceLedger,
  key  _ProjCstLineItm.Ledger,

  key  case
       when _ProjCstLineItm.CompanyCode is initial or _ProjCstLineItm.CompanyCode is null
       then _WBSElementBasicData.CompanyCode
       else _ProjCstLineItm.CompanyCode end                                                                                                as CompanyCode,
  key  _ProjCstLineItm.FiscalYear,

  key  cast(cast('' as abap.char (10)) as farp_belnr_d )                                                                                   as AccountingDocument,

  key  _ProjCstLineItm.FinancialPlanningReqTransSqnc,
  key  _ProjCstLineItm.FinancialPlanningDataPacket,
  key  cast(abs(_ProjCstLineItm.FinancialPlanningEntryItem) as  fis_act_pln_docln )                                                        as ActualPlanJournalEntryItem,


       _ProjCstLineItm.LedgerFiscalYear,

       cast(cast('' as abap.char (6)) as fis_docln )                                                                                       as LedgerGLLineItem,

       _ProjCstLineItm.FinancialPlanningEntryItem,

       _ProjCstLineItm.GLAccount,
       _ProjCstLineItm.ChartOfAccounts,

       _ProjCstLineItm.FinPlngEntryItemCreatedByUser                                                                                       as AccountingDocCreatedByUser,

       _SemTagGL.GLAccountHierarchy,
       _ProjCstLineItm.BusinessArea,
       _ProjCstLineItm.CostCenter,

       _ProjCstLineItm.BudgetProcess,

       // S/4 Hana CE2302- Field Deprecation - Begin

       //  _ProjCstLineItm.PartnerOrder,

       _ProjCstLineItm.PartnerOrder_2                                                                                                      as PartnerOrder,
       // S/4 Hana CE2302- Field Deprecation - End


       cast(_ProjCstLineItm._PartnerOrder.OrderCategory as fis_par_auftyp preserving type )                                                as PartnerOrderCategory,

       // S/4 Hana CE2302- Field Deprecation - Begin

       // _ProjCstLineItm.PartnerWBSElement,

       _ProjCstLineItm._PartnerWBSElementBasicData.WBSElementExternalID                                                                    as PartnerWBSElement,

       // _ProjCstLineItm.PartnerProject,

       _ProjCstLineItm._PartnerProjectBasicData.ProjectExternalID                                                                          as PartnerProject,
       // S/4 Hana CE2302- Field Deprecation - End

       _ProjCstLineItm.BusinessTransactionType,

       cast( ( case _ProjCstLineItm.ControllingDebitCreditCode
                when 'S' then 'S'
                         else 'H'
          end )
        as fis_shkzg preserving type )                                                                                                     as DebitCreditCode,

       _ProjCstLineItm.ControllingDebitCreditCode,

       case
       when _ProjCstLineItm.ProfitCenter is initial or _ProjCstLineItm.ProfitCenter is null
       then _WBSElementBasicData.ProfitCenter
       else _ProjCstLineItm.ProfitCenter end                                                                                               as ProfitCenter,

       _WBSElementBasicData.ProfitCenter                                                                                                   as WBSElementProfitCenter,
       _WBSElementBasicData.WBSDescription,

       _WBSElementBasicData.WBSElementObject                                                                                               as ProjectObjectNode,

       //   S/4 HANA CE2208:  mapping change for coding mask - start

       // _ProjCstLineItm.WBSElement,

       _WBSElementBasicData.WBSElementExternalID                                                                                           as WBSElement,
       //   S/4 HANA CE2208:  mapping change for coding mask - end


       _WBSElementBasicData.ResponsibleCostCenter,
       //S/4HANA CE2202: Project Type normalization - Begin

       _WBSElementBasicData.ProjectType,
       //S/4HANA CE2202: Project Type normalization - End

       _ProjCstLineItm.WBSElementInternalID,

       // S/4 HANA CE2208:  mapping change for coding mask - start

       //Project.ControllingArea,

       _ProjCstLineItm.ControllingArea,

       // _ProjCstLineItm.Project,

       Project.ProjectExternalID                                                                                                           as Project,

       //_ProjCstLineItm.ProjectInternalID,

       Project.ProjectInternalID,
       //   S/4 HANA CE2208:  mapping change for coding mask - end


       _ProjectSummary.Project                                                                                                             as ProjectExternalID,

       Project.ProjectDescription,
       Project.ProjectProfileCode,
       Project.ProjectObject,
       Project.AvailabilityControlProfile,
       Project.AvailabilityControlIsActive,
       Project._AvailabilityCtrlProfile._SemanticTag.SemanticTag                                                                           as ParentSemanticTag,
       Project._AvailabilityCtrlProfile.AvailyCtrlTimeRangeType,
       Project._AvailabilityCtrlProfile.BudgetCurrencyRole,
       Project.ProjectCurrency,
       _ProjectSummary.ProjectSummaryTaskUUID,
       '6'                                                                                                                                 as ProjectCategory,
       _ProjectSummary.ProjectUUID,
       _ProjectSummary.ProcessingStatus,
       _ProjectSummary.ProjectSummaryTaskUUID                                                                                              as TaskUUID,

       cast( cast(substring( cast(_ProjCstLineItm.FinPlngEntryItemCrtnDateTime as abap.char(30)) , 1 , 8) as abap.dats(8))  as fis_cpdat ) as CreationDate,

       _ProjCstLineItm.FunctionalArea,
       _ProjCstLineItm.CostCtrActivityType,

       @Semantics.currencyCode: true
       _ProjCstLineItm.ControllingObjectCurrency,

       @DefaultAggregation: #SUM
       @Semantics.amount.currencyCode: 'ControllingObjectCurrency'
       _ProjCstLineItm.AmountInObjectCurrency,

       @Semantics.currencyCode:true
       _ProjCstLineItm.TransactionCurrency,

       @DefaultAggregation: #SUM
       @Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
       _ProjCstLineItm.AmountInTransactionCurrency,

       @Semantics.currencyCode:true
       _ProjCstLineItm.CompanyCodeCurrency,

       @DefaultAggregation: #SUM
       @Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
       _ProjCstLineItm.AmountInCompanyCodeCurrency,

       @Semantics.currencyCode:true
       _ProjCstLineItm.GlobalCurrency,

       @DefaultAggregation: #SUM
       @Semantics: { amount : {currencyCode: 'GlobalCurrency'} }
       _ProjCstLineItm.AmountInGlobalCurrency,

       @Semantics.currencyCode:true
       _ProjCstLineItm.FunctionalCurrency,

       @Semantics: { amount : {currencyCode: 'FunctionalCurrency'} }
       @DefaultAggregation: #SUM
       _ProjCstLineItm.AmountInFunctionalCurrency,

       @DefaultAggregation: #SUM
       @Semantics: { amount : {currencyCode: 'GlobalCurrency'} }

       _ProjCstLineItm.PlanningCategory,

       _ProjCstLineItm.PostingDate,

       cast( '00000000' as bldat )                                                                                                         as DocumentDate,

       _ProjCstLineItm.FiscalPeriod,
       _ProjCstLineItm.FiscalYearPeriod,
       _ProjCstLineItm.FiscalYearVariant,

       'X'                                                                                                                                 as IsSettled,
       cast('' as fins_src_awtyp   )                                                                                                       as SourceReferenceDocumentType,
       cast('' as fins_src_awsys   )                                                                                                       as SourceLogicalSystem,
       cast('' as fins_src_aworg   )                                                                                                       as SourceReferenceDocumentCntxt,
       cast('' as fins_src_awref   )                                                                                                       as SourceReferenceDocument,
       cast('000000' as fins_src_awitem preserving type )                                                                                  as SourceReferenceDocumentItem,
       cast('000000' as fins_src_awsubit preserving type )                                                                                 as SourceReferenceDocSubitem,
       cast(' ' as fins_xcommitment preserving type )                                                                                      as IsCommitment,

       //S/4HANA CE2302 - Maintenance Order enablement-Begin

       _ProjCstLineItm.OrderID,
       cast('' as fcom_category )                                                                                                          as OrderPlanningCategory,
       
       cast( 'X' as boolean )                                                                                                              as MaintenanceOrderIsSelected,
       //S/4HANA CE2302 - Maintenance Order enablement-End

       _SemTagGL.SemanticTag,
       _AllCost.SemanticTagIsBudgetRelevant,
       _AllCost.SemanticTagIsBudgetRelevant                                                                                                as ObjectIsBudgeted,
       _AllCost.ProjectIsBudgetRelevant,

       _FiscalPeriod
}

where
        _SemTagGL.GLAccountHierarchy            =  _ContArea.CtrlgStdFinStatementVersion
  and   _ContArea.CtrlgStdFinStatementVersion   is not initial
  and(
        _ProjCstLineItm._Ledger.IsLeadingLedger =  'X'
  )
  and   Project.Project != ''
  and(
        _SemTagGL.ValidityStartDate             <= $session.system_date
    and _SemTagGL.ValidityEndDate               >= $session.system_date
  )
  and   _SemTagGL.SemanticTag                   =  _AllCost.SemanticTag
  and   _AllCost.FinancialStatementVariant      =  _ContArea.CtrlgStdFinStatementVersion
  and   Project.ProjectProfileCode              <> 'YP01'

  and   _ProjCstLineItm.AccountAssignmentType != 'OR'

//S/4HANA CE2302 - Maintenance Order enablement-Begin

  union all 
   select from            P_ProjectsWithProjCat6       as Project
    inner join            I_FinancialPlanningEntryItem as _ProjCstLineItm      on _ProjCstLineItm.ProjectInternalID = Project.ProjectInternalID
    inner join            I_MaintenanceOrder           as _Orders              on _ProjCstLineItm.OrderID          = _Orders.MaintenanceOrder
                                                                                                                          and  _ProjCstLineItm.ProjectInternalID     = _Orders.ProjectInternalID
    inner join            I_ControllingArea            as _ContArea            on _ContArea.ControllingArea = _ProjCstLineItm.ControllingArea
    inner join            P_PROJECTSEMTAGACC           as _SemTagGL            on  _ProjCstLineItm.ControllingArea = _SemTagGL.ControllingArea
                                                                               and _ProjCstLineItm.GLAccount       = _SemTagGL.HierarchyNodeVal
                                                                               and _ProjCstLineItm.ChartOfAccounts = _SemTagGL.HierarchyNodeClass
    inner join            P_GetRlvtSemanticTagForProj  as _AllCost             on _AllCost.ProjectInternalID = _ProjCstLineItm.ProjectInternalID
    inner join            I_OrderPlanningCategoryMap   as _OrderCategoryMap    on _OrderCategoryMap.OrderCategory    = _Orders.MaintenanceOrderCategory
                                                                               and _ProjCstLineItm.PlanningCategory    = _OrderCategoryMap.PlanningCategory1
    left outer to one join I_WBSElementBasicData        as _WBSElementBasicData on  _Orders.ProjectInternalID    = _WBSElementBasicData.ProjectInternalID
                                                                               and  _Orders.WBSElementInternalID = _WBSElementBasicData .WBSElementInternalID
    left outer to one join I_EnterpriseProject          as _ProjectSummary      on _ProjectSummary.ProjectInternalID = _Orders.ProjectInternalID
    association [0..1] to I_FiscalPeriodForVariant as _FiscalPeriod on  $projection.LedgerFiscalYear  = _FiscalPeriod.FiscalYear
                                                                    and $projection.FiscalPeriod      = _FiscalPeriod.FiscalPeriod
                                                                    and $projection.FiscalYearVariant = _FiscalPeriod.FiscalYearVariant
{
  key  cast(cast('' as abap.char (2)) as fins_ledger_pers )                                                                                as SourceLedger,
  key  _ProjCstLineItm.Ledger,

  key  case
       when _ProjCstLineItm.CompanyCode is initial or _ProjCstLineItm.CompanyCode is null
       then _WBSElementBasicData.CompanyCode
       else _ProjCstLineItm.CompanyCode end                                                                                                as CompanyCode,
  key  _ProjCstLineItm.FiscalYear,

  key  cast(cast('' as abap.char (10)) as farp_belnr_d )                                                                                   as AccountingDocument,

  key  _ProjCstLineItm.FinancialPlanningReqTransSqnc,
  key  _ProjCstLineItm.FinancialPlanningDataPacket,
  key  cast(abs(_ProjCstLineItm.FinancialPlanningEntryItem) as  fis_act_pln_docln )                                                        as ActualPlanJournalEntryItem,


       _ProjCstLineItm.LedgerFiscalYear,

       cast(cast('' as abap.char (6)) as fis_docln )                                                                                       as LedgerGLLineItem,

       _ProjCstLineItm.FinancialPlanningEntryItem,

       _ProjCstLineItm.GLAccount,
       _ProjCstLineItm.ChartOfAccounts,

       _ProjCstLineItm.FinPlngEntryItemCreatedByUser                                                                                       as AccountingDocCreatedByUser,

       _SemTagGL.GLAccountHierarchy,
       _ProjCstLineItm.BusinessArea,
       _ProjCstLineItm.CostCenter,

       _ProjCstLineItm.BudgetProcess,

       // S/4 Hana CE2302- Field Deprecation - Begin

       //  _ProjCstLineItm.PartnerOrder,

       _ProjCstLineItm.PartnerOrder_2                                                                                                      as PartnerOrder,
       // S/4 Hana CE2302- Field Deprecation - End


       cast(_ProjCstLineItm._PartnerOrder.OrderCategory as fis_par_auftyp preserving type )                                                as PartnerOrderCategory,

       // S/4 Hana CE2302- Field Deprecation - Begin

       // _ProjCstLineItm.PartnerWBSElement,

       _ProjCstLineItm._PartnerWBSElementBasicData.WBSElementExternalID                                                                    as PartnerWBSElement,

       // _ProjCstLineItm.PartnerProject,

       _ProjCstLineItm._PartnerProjectBasicData.ProjectExternalID                                                                          as PartnerProject,
       // S/4 Hana CE2302- Field Deprecation - End

       _ProjCstLineItm.BusinessTransactionType,

       cast( ( case _ProjCstLineItm.ControllingDebitCreditCode
                when 'S' then 'S'
                         else 'H'
          end )
        as fis_shkzg preserving type )                                                                                                     as DebitCreditCode,

       _ProjCstLineItm.ControllingDebitCreditCode,

       case
       when _ProjCstLineItm.ProfitCenter is initial or _ProjCstLineItm.ProfitCenter is null
       then _WBSElementBasicData.ProfitCenter
       else _ProjCstLineItm.ProfitCenter end                                                                                               as ProfitCenter,

       _WBSElementBasicData.ProfitCenter                                                                                                   as WBSElementProfitCenter,
       _WBSElementBasicData.WBSDescription,

      _ProjCstLineItm.OrderID                                                            as ProjectObjectNode,

       //   S/4 HANA CE2208:  mapping change for coding mask - start

       // _ProjCstLineItm.WBSElement,

       _WBSElementBasicData.WBSElementExternalID                                                                                           as WBSElement,
       //   S/4 HANA CE2208:  mapping change for coding mask - end


       _WBSElementBasicData.ResponsibleCostCenter,
       //S/4HANA CE2202: Project Type normalization - Begin

       _WBSElementBasicData.ProjectType,
       //S/4HANA CE2202: Project Type normalization - End

       _ProjCstLineItm.WBSElementInternalID,

       // S/4 HANA CE2208:  mapping change for coding mask - start

       //Project.ControllingArea,

       _ProjCstLineItm.ControllingArea,

       // _ProjCstLineItm.Project,

       Project.ProjectExternalID                                                                                                           as Project,

       //_ProjCstLineItm.ProjectInternalID,

       Project.ProjectInternalID,
       //   S/4 HANA CE2208:  mapping change for coding mask - end


       _ProjectSummary.Project                                                                                                             as ProjectExternalID,

       Project.ProjectDescription,
       Project.ProjectProfileCode,
       Project.ProjectObject,
       Project.AvailabilityControlProfile,
       Project.AvailabilityControlIsActive,
       Project._AvailabilityCtrlProfile._SemanticTag.SemanticTag                                                                           as ParentSemanticTag,
       Project._AvailabilityCtrlProfile.AvailyCtrlTimeRangeType,
       Project._AvailabilityCtrlProfile.BudgetCurrencyRole,
       Project.ProjectCurrency,
       _ProjectSummary.ProjectSummaryTaskUUID,
       '6'                                                                                                                                 as ProjectCategory,
       _ProjectSummary.ProjectUUID,
       _ProjectSummary.ProcessingStatus,
       _ProjectSummary.ProjectSummaryTaskUUID                                                                                              as TaskUUID,

       cast( cast(substring( cast(_ProjCstLineItm.FinPlngEntryItemCrtnDateTime as abap.char(30)) , 1 , 8) as abap.dats(8))  as fis_cpdat ) as CreationDate,

       _ProjCstLineItm.FunctionalArea,
       _ProjCstLineItm.CostCtrActivityType,

       @Semantics.currencyCode: true
       _ProjCstLineItm.ControllingObjectCurrency,

       @DefaultAggregation: #SUM
       @Semantics.amount.currencyCode: 'ControllingObjectCurrency'
       _ProjCstLineItm.AmountInObjectCurrency,

       @Semantics.currencyCode:true
       _ProjCstLineItm.TransactionCurrency,

       @DefaultAggregation: #SUM
       @Semantics: { amount : {currencyCode: 'TransactionCurrency'} }
       _ProjCstLineItm.AmountInTransactionCurrency,

       @Semantics.currencyCode:true
       _ProjCstLineItm.CompanyCodeCurrency,

       @DefaultAggregation: #SUM
       @Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
       _ProjCstLineItm.AmountInCompanyCodeCurrency,

       @Semantics.currencyCode:true
       _ProjCstLineItm.GlobalCurrency,

       @DefaultAggregation: #SUM
       @Semantics: { amount : {currencyCode: 'GlobalCurrency'} }
       _ProjCstLineItm.AmountInGlobalCurrency,

       @Semantics.currencyCode:true
       _ProjCstLineItm.FunctionalCurrency,

       @Semantics: { amount : {currencyCode: 'FunctionalCurrency'} }
       @DefaultAggregation: #SUM
       _ProjCstLineItm.AmountInFunctionalCurrency,

       @DefaultAggregation: #SUM
       @Semantics: { amount : {currencyCode: 'GlobalCurrency'} }

       _ProjCstLineItm.PlanningCategory,

       _ProjCstLineItm.PostingDate,

       cast( '00000000' as bldat )                                                                                                         as DocumentDate,

       _ProjCstLineItm.FiscalPeriod,
       _ProjCstLineItm.FiscalYearPeriod,
       _ProjCstLineItm.FiscalYearVariant,

       'X'                                                                                                                                 as IsSettled,
       cast('' as fins_src_awtyp   )                                                                                                       as SourceReferenceDocumentType,
       cast('' as fins_src_awsys   )                                                                                                       as SourceLogicalSystem,
       cast('' as fins_src_aworg   )                                                                                                       as SourceReferenceDocumentCntxt,
       cast('' as fins_src_awref   )                                                                                                       as SourceReferenceDocument,
       cast('000000' as fins_src_awitem preserving type )                                                                                  as SourceReferenceDocumentItem,
       cast('000000' as fins_src_awsubit preserving type )                                                                                 as SourceReferenceDocSubitem,
       cast(' ' as fins_xcommitment preserving type )                                                                                      as IsCommitment,
       
       //S/4HANA CE2302 - Maintenance Order enablement-Begin

       _ProjCstLineItm.OrderID,
       _OrderCategoryMap.PlanningCategory1                                                                                                 as OrderPlanningCategory,
       
       cast( ' ' as boolean )                                                                                                              as MaintenanceOrderIsSelected,
       //S/4HANA CE2302 - Maintenance Order enablement - End

       
       _SemTagGL.SemanticTag,
       _AllCost.SemanticTagIsBudgetRelevant,
       _AllCost.SemanticTagIsBudgetRelevant                                                                                                as ObjectIsBudgeted,
       _AllCost.ProjectIsBudgetRelevant,

       _FiscalPeriod
}

where
        _SemTagGL.GLAccountHierarchy            =  _ContArea.CtrlgStdFinStatementVersion
  and   _ContArea.CtrlgStdFinStatementVersion   is not initial
  and(  _ProjCstLineItm._Ledger.IsLeadingLedger =  'X' )
  and   Project.Project != ''
  and(  _SemTagGL.ValidityStartDate             <= $session.system_date
        and _SemTagGL.ValidityEndDate           >= $session.system_date )
  and   _SemTagGL.SemanticTag                   =  _AllCost.SemanticTag
  and   _AllCost.FinancialStatementVariant      =  _ContArea.CtrlgStdFinStatementVersion
  and   Project.ProjectProfileCode              <> 'YP01'
  and   _ProjCstLineItm.AccountAssignmentType   = 'OR'
  
//S/4HANA CE2302 - Maintenance Order enablement-Begin

/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_AVAILABILITYCTRLPROFILE",
"I_AVAILYCTRLPROFILESEMANTICTAG",
"I_CONTROLLINGAREA",
"I_ENTERPRISEPROJECT",
"I_FINANCIALPLANNINGENTRYITEM",
"I_INTERNALORDER",
"I_LEDGER",
"I_MAINTENANCEORDER",
"I_ORDERPLANNINGCATEGORYMAP",
"I_PROJECTBASICDATA",
"I_WBSELEMENTBASICDATA",
"P_GETRLVTSEMANTICTAGFORPROJ",
"P_PROJECTSEMTAGACC",
"P_PROJECTSWITHPROJCAT6"
],
"ASSOCIATED":
[
"I_FISCALPERIODFORVARIANT"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/