1. Tables and Views for Sales and Fusion Service
  2. ZSF_SUMADJ_FACT_ROLLUP_V

ZSF_SUMADJ_FACT_ROLLUP_V

Details

  • Schema: FUSION

  • Object owner: ZSF

  • Object type: VIEW

Columns

Name

FCST_HEADER_ID

FCST_PARTICIPANT_ID

FCST_ADJUST_PERIOD_ID

FCST_PRODUCT_ID

CHANNEL_TYPE_CODE

UNADJUSTED_AMT

BESTCASE_AMT

WORSTCASE_AMT

Query

SQL_Statement

SELECT P1.FCST_HEADER_ID,P2.FCST_PARTICIPANT_ID,PADJ.FCST_ADJUST_PERIOD_ID,PADJ.FCST_PRODUCT_ID,PADJ.CHANNEL_TYPE_CODE,

SUM(PADJ.CORP_ADJUSTMENT_AMT-(PADJ.CORP_SUB_ADJUST_AMT+CORP_SUB_PROD_ADJUST_AMT)) AS UNADJUSTED_AMT,

SUM(PADJ.CORP_BESTCASE_ADJUST_AMT-(PADJ.CORP_SUB_BESTCASE_ADJUST_AMT+CORP_SUB_PROD_BEST_ADJUST_AMT)) AS BESTCASE_AMT,

SUM(PADJ.CORP_WORSTCASE_ADJUST_AMT-(PADJ.CORP_SUB_WORSTCASE_ADJUST_AMT+CORP_SUB_PROD_WORST_ADJUST_AMT)) AS WORSTCASE_AMT

FROM ZSF_FCST_PARTICIPANT P1,ZSF_FCST_PARTICIPANT P2,ZSF_FCST_TERR_HIER_DN DN, ZSF_FCST_ADJUSTMENT ADJ , ZSF_FCST_PERIOD_ADJUSTMENT PADJ , ZSF_FCST_HEADER H

WHERE ADJ.FCST_ADJUSTMENT_ID = PADJ.FCST_ADJUSTMENT_ID

AND P1.FCST_HEADER_ID = P2.FCST_HEADER_ID

AND H.FCST_HEADER_ID = P1.FCST_HEADER_ID

AND ADJ.FCST_PARTICIPANT_ID = P1.FCST_PARTICIPANT_ID

AND H.DENORM_VERSION_ID = DN.DENORM_VERSION_ID

AND P1.TERRITORY_ID = DN.DESCENDANT_TERRITORY_ID

AND DN.TERRITORY_ID = P2.TERRITORY_ID

AND ( P2.MANAGER_TERRITORY_FLAG = 'N' OR (P1.FCST_PARTICIPANT_ID = P2.FCST_PARTICIPANT_ID AND P1.TERRITORY_ID = DN.TERRITORY_ID ))

GROUP BY P1.FCST_HEADER_ID, P2.FCST_PARTICIPANT_ID,PADJ.FCST_PRODUCT_ID,PADJ.CHANNEL_TYPE_CODE, PADJ.FCST_ADJUST_PERIOD_ID