P_CycleSegmentHierSourceParam

@AbapCatalog.sqlViewName: 'PALLOCYC2SEGSRC'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
//@EndUserText.label: 'Cycle Segment Hierarchy Source'


@ClientHandling.algorithm: #SESSION_VARIABLE

@VDM: {
  viewType: #CONSUMPTION,
  private: true
}

@ObjectModel.usageType:{
  serviceQuality: #C,
  sizeCategory: #M,
  dataClass: #MIXED
}

define view P_CycleSegmentHierSourceParam
  with parameters
    P_AllocationTagID :fco_tag_id
  as select from           I_AllocationSegment
    inner join             I_AllocationContextType                  on I_AllocationContextType.AllocationType = I_AllocationSegment.AllocationType
    inner join             I_AllocationCycle                        on  I_AllocationSegment.AllocationType           = I_AllocationCycle.AllocationType
                                                                    and I_AllocationSegment.AllocationCycle          = I_AllocationCycle.AllocationCycle
                                                                    and I_AllocationSegment.AllocationCycleStartDate = I_AllocationCycle.AllocationCycleStartDate

    left outer to one join I_AllocationKeyField       as CompanyCode      on  I_AllocationSegment.AllocationType           = CompanyCode.AllocationType
                                                                          and I_AllocationSegment.AllocationCycle          = CompanyCode.AllocationCycle
                                                                          and I_AllocationSegment.AllocationCycleStartDate = CompanyCode.AllocationCycleStartDate
                                                                          and CompanyCode.AllocationSetType                = '0'
                                                                          and CompanyCode.AllocationFieldName              = 'RBUKRS'

    left outer to one join I_AllocationKeyField       as PlanCategory     on  I_AllocationSegment.AllocationType           = CompanyCode.AllocationType
                                                                          and I_AllocationSegment.AllocationCycle          = CompanyCode.AllocationCycle
                                                                          and I_AllocationSegment.AllocationCycleStartDate = CompanyCode.AllocationCycleStartDate
                                                                          and CompanyCode.AllocationSetType                = '0'
                                                                          and CompanyCode.AllocationFieldName              = 'CATEGORY'

    left outer to one join I_CompanyCode                            on CompanyCode = CompanyCode.AllocationFieldMinimumValue

    left outer to one join I_AllocTagObjectAssignment as Assignment on  I_AllocationSegment.AllocationType           = Assignment.AllocationType
                                                                    and I_AllocationSegment.AllocationCycle          = Assignment.AllocationCycle
                                                                    and I_AllocationSegment.AllocationCycleStartDate = Assignment.AllocationCycleStartDate
                                                                    and I_AllocationSegment.AllocationCycleSegment   = Assignment.AllocationCycleSegment
                                                                    and Assignment.AllocationTagID                   = $parameters.P_AllocationTagID

{
  key concat( I_AllocationSegment.AllocationCycleStartDate, concat(I_AllocationSegment.AllocationCycle, I_AllocationSegment.AllocationType) )                                                      as AllocationCycleHier,
  key concat( I_AllocationSegment.AllocationCycleStartDate, concat(I_AllocationSegment.AllocationCycle, concat(I_AllocationSegment.AllocationType, I_AllocationSegment.AllocationCycleSegment) ) ) as AllocationSegmentHier,

      cast( 'leaf' as abap.char(10) )                                                                                                                                                              as DrillState,
      cast( 0001 as abap.int4 )                                                                                                                                                                    as HierarchyLevel,

      cast( I_AllocationSegment.AllocationType as fco_allocation_context )                                                                                                                         as AllocationType,
      I_AllocationSegment.AllocationCycle,
      I_AllocationSegment.AllocationCycleStartDate,
      I_AllocationCycle.AllocationCycleName,

      I_AllocationCycle.AllocationActualPlanVariant,
      cast( I_AllocationCycle.AllocationPostingType as fco_alloc_posting_type)                                                                                                                     as AllocationPostingType,
      I_AllocationCycle.Ledger,
      I_AllocationCycle.AllocationStatus,
      I_AllocationCycle.LastChangedByUser,
      I_AllocationCycle.AllocationCycleEndDate,
      I_AllocationCycle.LastChangeDate,
      I_AllocationCycle.CreatedByUser,
      I_AllocationCycle.AllocationCreateDate,
      I_AllocationCycle.AllocationFrequency,

      cast(CompanyCode.AllocationFieldMinimumValue as fis_bukrs)                                                                                                                                   as CompanyCode,
      cast(PlanCategory.AllocationFieldMinimumValue as fcom_category)                                                                                                                              as AllocationCycleCategory,

      I_AllocationSegment.AllocationCycleSegment,
      I_AllocationSegment.SegmentName,

      I_CompanyCode.CompanyCodeName,

      I_AllocationCycle._Text.AllocationCycleNameText,

      Assignment.AllocationTagID,
      cast(case when Assignment.AllocationTagID is null then 'U' else 'A' end as fco_alloc_tag_assignment)                                                                                         as AllocTagAssignmentStatus
      //      cast('' as fco_tag_id) as AllocationTagID,

      //      cast('U' as fco_alloc_tag_assignment) as AllocTagAssignmentStatus

}
union select from        I_AllocationCycle
  inner join             I_AllocationContextType                  on I_AllocationContextType.AllocationType = I_AllocationCycle.AllocationType

  left outer to one join I_AllocationKeyField       as CompanyCode      on  I_AllocationCycle.AllocationType           = CompanyCode.AllocationType
                                                                        and I_AllocationCycle.AllocationCycle          = CompanyCode.AllocationCycle
                                                                        and I_AllocationCycle.AllocationCycleStartDate = CompanyCode.AllocationCycleStartDate
                                                                        and CompanyCode.AllocationSetType              = '0'
                                                                        and CompanyCode.AllocationFieldName            = 'RBUKRS'

  left outer to one join I_AllocationKeyField       as PlanCategory     on  I_AllocationCycle.AllocationType           = CompanyCode.AllocationType
                                                                        and I_AllocationCycle.AllocationCycle          = CompanyCode.AllocationCycle
                                                                        and I_AllocationCycle.AllocationCycleStartDate = CompanyCode.AllocationCycleStartDate
                                                                        and CompanyCode.AllocationSetType              = '0'
                                                                        and CompanyCode.AllocationFieldName            = 'CATEGORY'

  left outer to one join I_CompanyCode                            on CompanyCode = CompanyCode.AllocationFieldMinimumValue

  left outer to one join I_AllocTagObjectAssignment as Assignment on  I_AllocationCycle.AllocationType           = Assignment.AllocationType
                                                                  and I_AllocationCycle.AllocationCycle          = Assignment.AllocationCycle
                                                                  and I_AllocationCycle.AllocationCycleStartDate = Assignment.AllocationCycleStartDate
                                                                  and Assignment.AllocationTagID                 = $parameters.P_AllocationTagID
{
  key cast('' as cycle)                                                                                                                 as AllocationCycleHier,
  key concat( I_AllocationCycle.AllocationCycleStartDate, concat(I_AllocationCycle.AllocationCycle, I_AllocationCycle.AllocationType) ) as AllocationSegmentHier,

      cast( 'expanded' as abap.char(10) )                                                                                               as DrillState,
      cast( '0000' as abap.int4 )                                                                                                       as HierarchyLevel,

      I_AllocationCycle.AllocationType,
      I_AllocationCycle.AllocationCycle,
      I_AllocationCycle.AllocationCycleStartDate,
      I_AllocationCycle.AllocationCycleName,

      I_AllocationCycle.AllocationActualPlanVariant,
      I_AllocationCycle.AllocationPostingType,
      I_AllocationCycle.Ledger,
      I_AllocationCycle.AllocationStatus,
      I_AllocationCycle.LastChangedByUser,
      I_AllocationCycle.AllocationCycleEndDate,
      I_AllocationCycle.LastChangeDate,
      I_AllocationCycle.CreatedByUser,
      I_AllocationCycle.AllocationCreateDate,
      I_AllocationCycle.AllocationFrequency,

      cast(CompanyCode.AllocationFieldMinimumValue as fis_bukrs)                                                                        as CompanyCode,
      cast(PlanCategory.AllocationFieldMinimumValue as fcom_category)                                                                   as AllocationCycleCategory,

      cast('' as fco_alloc_segment_number)                                                                                              as AllocationCycleSegment,
      cast('' as fco_segment_name)                                                                                                      as SegmentName,

      I_CompanyCode.CompanyCodeName,

      I_AllocationCycle._Text.AllocationCycleNameText,

      Assignment.AllocationTagID, //

      cast(case when Assignment.AllocationTagID is null then 'U' else 'A' end as fco_alloc_tag_assignment)                              as AllocTagAssignmentStatus
      //      cast('' as fco_tag_id) as AllocationTagID,

      //      cast('U' as fco_alloc_tag_assignment) as AllocTagAssignmentStatus

}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_ALLOCASSMTDISTRLONGTEXT",
"I_ALLOCATIONCONTEXTTYPE",
"I_ALLOCATIONCYCLE",
"I_ALLOCATIONKEYFIELD",
"I_ALLOCATIONSEGMENT",
"I_ALLOCTAGOBJECTASSIGNMENT",
"I_COMPANYCODE"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/