ZSF_ITEMADJ_FACT_ROLLUP

Details

Schema: FUSION
Object owner: ZSF
Object type: VIEW

Columns

Name
FCST_HEADER_ID VIEW_FROM_PARTICIPANT_ID PARTICIPANT_ID FCST_PRODUCT_ID CHANNEL_TYPE_CODE FCST_ADJUST_PERIOD_ID UNADJUSTED_AMT BESTCASE_AMT WORSTCASE_AMT

Name

FCST_HEADER_ID

VIEW_FROM_PARTICIPANT_ID

PARTICIPANT_ID

FCST_PRODUCT_ID

CHANNEL_TYPE_CODE

FCST_ADJUST_PERIOD_ID

UNADJUSTED_AMT

BESTCASE_AMT

WORSTCASE_AMT

Query

SQL_Statement
SELECT PART.FCST_HEADER_ID, PART1.FCST_PARTICIPANT_ID AS VIEW_FROM_PARTICIPANT_ID ,PART2.FCST_PARTICIPANT_ID AS PARTICIPANT_ID,ADJ.FCST_PRODUCT_ID,ADJ.CHANNEL_TYPE_CODE,ADJ.ADJUST_PERIOD_ID AS FCST_ADJUST_PERIOD_ID, SUM(ADJ.CORP_ADJUSTMENT_AMT- ADJ.CORP_SUB_ADJ_AMT) AS UNADJUSTED_AMT, SUM(ADJ.CORP_BEST_CASE_ADJ_AMT- ADJ.CORP_SUB_BEST_CASE_ADJ_AMT) AS BESTCASE_AMT, SUM(ADJ.CORP_WORST_CASE_ADJ_AMT- ADJ.CORP_SUB_WORST_CASE_ADJ_AMT) AS WORSTCASE_AMT FROM FUSION.ZSF_FCST_ITEM_ADJUSTMENT ADJ , FUSION.ZSF_FCST_TERR_HIER_DN DN ,FUSION.ZSF_FCST_TERR_HIER_DN DN2 , FUSION.ZSF_FCST_PARTICIPANT PART , FUSION.ZSF_FCST_PARTICIPANT PART1, FUSION.ZSF_FCST_PARTICIPANT PART2 , FUSION.ZSF_FCST_HEADER H WHERE ADJ.FCST_PARTICIPANT_ID = PART.FCST_PARTICIPANT_ID AND PART.FCST_HEADER_ID = PART1.FCST_HEADER_ID AND PART.FCST_HEADER_ID = PART2.FCST_HEADER_ID AND PART2.FCST_HEADER_ID = H.FCST_HEADER_ID AND H.DENORM_VERSION_ID = DN.DENORM_VERSION_ID AND H.DENORM_VERSION_ID = DN2.DENORM_VERSION_ID AND PART.TERRITORY_ID = DN.DESCENDANT_TERRITORY_ID AND ADJ.FCST_ITEM_TERRITORY_ID = DN2.DESCENDANT_TERRITORY_ID AND DN.TERRITORY_ID= PART1.TERRITORY_ID AND DN2.TERRITORY_ID = PART2.TERRITORY_ID AND PART1.TERRITORY_LEVEL <= PART2.TERRITORY_LEVEL AND (PART.MANAGER_TERRITORY_FLAG <> 'Y' OR DN.TERRITORY_ID = PART.TERRITORY_ID ) AND (PART2.MANAGER_TERRITORY_FLAG <> 'Y' OR PART2.TERRITORY_ID = ADJ.FCST_ITEM_TERRITORY_ID ) AND PART1.MANAGER_TERRITORY_FLAG <> 'Y' GROUP BY PART.FCST_HEADER_ID, PART1.FCST_PARTICIPANT_ID, PART2.FCST_PARTICIPANT_ID,ADJ.FCST_PRODUCT_ID,ADJ.CHANNEL_TYPE_CODE, ADJ.ADJUST_PERIOD_ID

SQL_Statement

SELECT PART.FCST_HEADER_ID, PART1.FCST_PARTICIPANT_ID AS VIEW_FROM_PARTICIPANT_ID ,PART2.FCST_PARTICIPANT_ID AS PARTICIPANT_ID,ADJ.FCST_PRODUCT_ID,ADJ.CHANNEL_TYPE_CODE,ADJ.ADJUST_PERIOD_ID AS FCST_ADJUST_PERIOD_ID,

SUM(ADJ.CORP_ADJUSTMENT_AMT- ADJ.CORP_SUB_ADJ_AMT) AS UNADJUSTED_AMT,

SUM(ADJ.CORP_BEST_CASE_ADJ_AMT- ADJ.CORP_SUB_BEST_CASE_ADJ_AMT) AS BESTCASE_AMT,

SUM(ADJ.CORP_WORST_CASE_ADJ_AMT- ADJ.CORP_SUB_WORST_CASE_ADJ_AMT) AS WORSTCASE_AMT

FROM FUSION.ZSF_FCST_ITEM_ADJUSTMENT ADJ , FUSION.ZSF_FCST_TERR_HIER_DN DN ,FUSION.ZSF_FCST_TERR_HIER_DN DN2 , FUSION.ZSF_FCST_PARTICIPANT PART , FUSION.ZSF_FCST_PARTICIPANT PART1, FUSION.ZSF_FCST_PARTICIPANT PART2 , FUSION.ZSF_FCST_HEADER H

WHERE ADJ.FCST_PARTICIPANT_ID = PART.FCST_PARTICIPANT_ID

AND PART.FCST_HEADER_ID = PART1.FCST_HEADER_ID

AND PART.FCST_HEADER_ID = PART2.FCST_HEADER_ID

AND PART2.FCST_HEADER_ID = H.FCST_HEADER_ID

AND H.DENORM_VERSION_ID = DN.DENORM_VERSION_ID

AND H.DENORM_VERSION_ID = DN2.DENORM_VERSION_ID

AND PART.TERRITORY_ID = DN.DESCENDANT_TERRITORY_ID

AND ADJ.FCST_ITEM_TERRITORY_ID = DN2.DESCENDANT_TERRITORY_ID

AND DN.TERRITORY_ID= PART1.TERRITORY_ID

AND DN2.TERRITORY_ID = PART2.TERRITORY_ID

AND PART1.TERRITORY_LEVEL <= PART2.TERRITORY_LEVEL

AND (PART.MANAGER_TERRITORY_FLAG <> 'Y' OR DN.TERRITORY_ID = PART.TERRITORY_ID )

AND (PART2.MANAGER_TERRITORY_FLAG <> 'Y' OR PART2.TERRITORY_ID = ADJ.FCST_ITEM_TERRITORY_ID )

AND PART1.MANAGER_TERRITORY_FLAG <> 'Y'

GROUP BY PART.FCST_HEADER_ID, PART1.FCST_PARTICIPANT_ID, PART2.FCST_PARTICIPANT_ID,ADJ.FCST_PRODUCT_ID,ADJ.CHANNEL_TYPE_CODE, ADJ.ADJUST_PERIOD_ID