eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.DDR_SV_FRCST_C_MV#

Source

View Text - Preformatted


SELECT
    /*+ NO_STATS_GSETS */
    "A5"."CLNDR_MNTH_ID" "CLNDR_MNTH_ID",
    "A2"."ORG_RGN_ID" "ORG_RGN_ID",
    "A2"."ORG_CHAIN_ID" "ORG_CHAIN_ID",
    "A4"."MFG_ITEM_CLASS_ID" "MFG_ITEM_CLASS_ID",
    "A3"."RTL_ITEM_CLASS_ID" "RTL_ITEM_CLASS_ID",
    "A7"."ITM_TYP" "ITM_TYP",
    "A7"."FRCST_PURP" "FRCST_PURP",
    "A7"."FRCST_TYP" "FRCST_TYP",
    "A5"."CLNDR_MNTH_ID" "CLNDR_DIM_ID",
    'MNTH' "CLNDR_DIM_LVL",
    DECODE(GROUPING_ID("A2"."ORG_CHAIN_ID","A2"."ORG_RGN_ID"),2,"A2"."ORG_RGN_ID",1,"A2"."ORG_CHAIN_ID") "ORG_DIM_ID",
    DECODE(GROUPING_ID("A2"."ORG_CHAIN_ID","A2"."ORG_RGN_ID"),2,'RGN',1,'CHN') "ORG_DIM_LVL",
    "A4"."MFG_ITEM_CLASS_ID" "MFG_ITEM_DIM_ID",
    DECODE("A4"."MFG_ITEM_CLASS_ID",NULL,NULL,'CLS') "MFG_ITEM_DIM_LVL",
    "A3"."RTL_ITEM_CLASS_ID" "RTL_ITEM_DIM_ID",
    DECODE("A3"."RTL_ITEM_CLASS_ID",NULL,NULL,'CLS') "RTL_ITEM_DIM_LVL",
    COUNT(*) "CNT_TOTAL",
    SUM("A1"."ACT_FRCST_SLS_AMT_RPT") "ACT_FRCST_SLS_AMT_RPT",
    SUM("A1"."ACT_FRCST_SLS_QTY_PRMRY") "ACT_FRCST_SLS_QTY_PRMRY",
    SUM("A7"."FRCST_SLS_QTY_PRMRY") "FRCST_SLS_QTY_PRMRY",
    SUM("A7"."FRCST_SLS_QTY_ALT") "FRCST_SLS_QTY_ALT",
    SUM("A7"."FRCST_SLS_AMT") "FRCST_SLS_AMT",
    SUM("A7"."FRCST_SLS_AMT_RPT") "FRCST_SLS_AMT_RPT",
    COUNT("A1"."ACT_FRCST_SLS_AMT_RPT") "CNT_ACT_FRCST_SLS_AMT_RPT",
    COUNT("A1"."ACT_FRCST_SLS_QTY_PRMRY") "CNT_ACT_FRCST_SLS_QTY_PRMRY",
    COUNT("A7"."FRCST_SLS_QTY_PRMRY") "CNT_FRCST_SLS_QTY_PRMRY",
    COUNT("A7"."FRCST_SLS_QTY_ALT") "CNT_FRCST_SLS_QTY_ALT",
    COUNT("A7"."FRCST_SLS_AMT") "CNT_FRCST_SLS_AMT",
    COUNT("A7"."FRCST_SLS_AMT_RPT") "CNT_FRCST_SLS_AMT_RPT",
    GROUPING_ID("A4"."MFG_ITEM_CLASS_ID","A3"."RTL_ITEM_CLASS_ID","A5"."CLNDR_MNTH_ID","A2"."ORG_RGN_ID","A2"."ORG_CHAIN_ID","A7"."ITM_TYP","A7"."FRCST_PURP","A7"."FRCST_TYP") "GROUPING_ID"
  FROM "DDR_B_SLS_FRCST_ITEM_DAY" "A7",
    "DDR_BS_FRCST_VRSN_MV" "A6",
    "DDR_R_CLNDR_DAY_DN_MV" "A5",
    "DDR_R_MFG_ITEM_SKU_DN_MV" "A4",
    "DDR_R_RTL_ITEM_SKU_DN_MV" "A3",
    "DDR_R_ORG_BU_DN_MV" "A2",
    "DDR_BS_FRCST_LST_SLS_MV" "A1"
  WHERE "A5"."DAY_CD"  ="A7"."DAY_CD"
  AND "A5"."CLNDR_DESC"="A7"."MFG_ORG_CD"
    ||'-CLNDR'
  AND "A6"."MAX_FRCST_VRSN"  ="A7"."FRCST_VRSN"
  AND "A6"."DAY_CD"          ="A7"."DAY_CD"
  AND "A6"."MFG_SKU_ITEM_ID" ="A7"."MFG_SKU_ITEM_ID"
  AND "A6"."ORG_BSNS_UNIT_ID"="A7"."ORG_BSNS_UNIT_ID"
  AND "A6"."RTL_SKU_ITEM_ID" ="A7"."RTL_SKU_ITEM_ID"
  AND "A6"."FRCST_PURP"      ="A7"."FRCST_PURP"
  AND "A6"."FRCST_TYP"       ="A7"."FRCST_TYP"
  AND "A4"."MFG_SKU_ITEM_ID" ="A7"."MFG_SKU_ITEM_ID"
  AND "A3"."RTL_SKU_ITEM_ID" ="A7"."RTL_SKU_ITEM_ID"
  AND "A2"."ORG_BSNS_UNIT_ID"="A7"."ORG_BSNS_UNIT_ID"
  AND "A7"."FRCST_VRSN"      ="A1"."MAX_FRCST_VRSN"
  AND "A7"."DAY_CD"          ="A1"."DAY_CD"
  AND "A7"."MFG_SKU_ITEM_ID" ="A1"."MFG_SKU_ITEM_ID"
  AND "A7"."ORG_BSNS_UNIT_ID"="A1"."ORG_BSNS_UNIT_ID"
  AND "A7"."RTL_SKU_ITEM_ID" ="A1"."RTL_SKU_ITEM_ID"
  AND "A7"."FRCST_PURP"      ="A1"."FRCST_PURP"
  and "A7"."FRCST_TYP"       ="A1"."FRCST_TYP"
  GROUP BY GROUPING SETS (("A5"."CLNDR_MNTH_ID", "A2"."ORG_RGN_ID", "A4"."MFG_ITEM_CLASS_ID", "A3"."RTL_ITEM_CLASS_ID", "A7"."ITM_TYP", "A7"."FRCST_PURP", "A7"."FRCST_TYP"), ("A5"."CLNDR_MNTH_ID", "A2"."ORG_CHAIN_ID", "A4"."MFG_ITEM_CLASS_ID", "A3"."RTL_ITEM_CLASS_ID", "A7"."ITM_TYP", "A7"."FRCST_PURP", "A7"."FRCST_TYP"))

View Text - HTML Formatted

SELECT /*+ NO_STATS_GSETS */ "A5"."CLNDR_MNTH_ID" "CLNDR_MNTH_ID"
, "A2"."ORG_RGN_ID" "ORG_RGN_ID"
, "A2"."ORG_CHAIN_ID" "ORG_CHAIN_ID"
, "A4"."MFG_ITEM_CLASS_ID" "MFG_ITEM_CLASS_ID"
, "A3"."RTL_ITEM_CLASS_ID" "RTL_ITEM_CLASS_ID"
, "A7"."ITM_TYP" "ITM_TYP"
, "A7"."FRCST_PURP" "FRCST_PURP"
, "A7"."FRCST_TYP" "FRCST_TYP"
, "A5"."CLNDR_MNTH_ID" "CLNDR_DIM_ID"
, 'MNTH' "CLNDR_DIM_LVL"
, DECODE(GROUPING_ID("A2"."ORG_CHAIN_ID"
, "A2"."ORG_RGN_ID")
, 2
, "A2"."ORG_RGN_ID"
, 1
, "A2"."ORG_CHAIN_ID") "ORG_DIM_ID"
, DECODE(GROUPING_ID("A2"."ORG_CHAIN_ID"
, "A2"."ORG_RGN_ID")
, 2
, 'RGN'
, 1
, 'CHN') "ORG_DIM_LVL"
, "A4"."MFG_ITEM_CLASS_ID" "MFG_ITEM_DIM_ID"
, DECODE("A4"."MFG_ITEM_CLASS_ID"
, NULL
, NULL
, 'CLS') "MFG_ITEM_DIM_LVL"
, "A3"."RTL_ITEM_CLASS_ID" "RTL_ITEM_DIM_ID"
, DECODE("A3"."RTL_ITEM_CLASS_ID"
, NULL
, NULL
, 'CLS') "RTL_ITEM_DIM_LVL"
, COUNT(*) "CNT_TOTAL"
, SUM("A1"."ACT_FRCST_SLS_AMT_RPT") "ACT_FRCST_SLS_AMT_RPT"
, SUM("A1"."ACT_FRCST_SLS_QTY_PRMRY") "ACT_FRCST_SLS_QTY_PRMRY"
, SUM("A7"."FRCST_SLS_QTY_PRMRY") "FRCST_SLS_QTY_PRMRY"
, SUM("A7"."FRCST_SLS_QTY_ALT") "FRCST_SLS_QTY_ALT"
, SUM("A7"."FRCST_SLS_AMT") "FRCST_SLS_AMT"
, SUM("A7"."FRCST_SLS_AMT_RPT") "FRCST_SLS_AMT_RPT"
, COUNT("A1"."ACT_FRCST_SLS_AMT_RPT") "CNT_ACT_FRCST_SLS_AMT_RPT"
, COUNT("A1"."ACT_FRCST_SLS_QTY_PRMRY") "CNT_ACT_FRCST_SLS_QTY_PRMRY"
, COUNT("A7"."FRCST_SLS_QTY_PRMRY") "CNT_FRCST_SLS_QTY_PRMRY"
, COUNT("A7"."FRCST_SLS_QTY_ALT") "CNT_FRCST_SLS_QTY_ALT"
, COUNT("A7"."FRCST_SLS_AMT") "CNT_FRCST_SLS_AMT"
, COUNT("A7"."FRCST_SLS_AMT_RPT") "CNT_FRCST_SLS_AMT_RPT"
, GROUPING_ID("A4"."MFG_ITEM_CLASS_ID"
, "A3"."RTL_ITEM_CLASS_ID"
, "A5"."CLNDR_MNTH_ID"
, "A2"."ORG_RGN_ID"
, "A2"."ORG_CHAIN_ID"
, "A7"."ITM_TYP"
, "A7"."FRCST_PURP"
, "A7"."FRCST_TYP") "GROUPING_ID"
FROM "DDR_B_SLS_FRCST_ITEM_DAY" "A7"
, "DDR_BS_FRCST_VRSN_MV" "A6"
, "DDR_R_CLNDR_DAY_DN_MV" "A5"
, "DDR_R_MFG_ITEM_SKU_DN_MV" "A4"
, "DDR_R_RTL_ITEM_SKU_DN_MV" "A3"
, "DDR_R_ORG_BU_DN_MV" "A2"
, "DDR_BS_FRCST_LST_SLS_MV" "A1"
WHERE "A5"."DAY_CD" ="A7"."DAY_CD"
AND "A5"."CLNDR_DESC"="A7"."MFG_ORG_CD" ||'-CLNDR'
AND "A6"."MAX_FRCST_VRSN" ="A7"."FRCST_VRSN"
AND "A6"."DAY_CD" ="A7"."DAY_CD"
AND "A6"."MFG_SKU_ITEM_ID" ="A7"."MFG_SKU_ITEM_ID"
AND "A6"."ORG_BSNS_UNIT_ID"="A7"."ORG_BSNS_UNIT_ID"
AND "A6"."RTL_SKU_ITEM_ID" ="A7"."RTL_SKU_ITEM_ID"
AND "A6"."FRCST_PURP" ="A7"."FRCST_PURP"
AND "A6"."FRCST_TYP" ="A7"."FRCST_TYP"
AND "A4"."MFG_SKU_ITEM_ID" ="A7"."MFG_SKU_ITEM_ID"
AND "A3"."RTL_SKU_ITEM_ID" ="A7"."RTL_SKU_ITEM_ID"
AND "A2"."ORG_BSNS_UNIT_ID"="A7"."ORG_BSNS_UNIT_ID"
AND "A7"."FRCST_VRSN" ="A1"."MAX_FRCST_VRSN"
AND "A7"."DAY_CD" ="A1"."DAY_CD"
AND "A7"."MFG_SKU_ITEM_ID" ="A1"."MFG_SKU_ITEM_ID"
AND "A7"."ORG_BSNS_UNIT_ID"="A1"."ORG_BSNS_UNIT_ID"
AND "A7"."RTL_SKU_ITEM_ID" ="A1"."RTL_SKU_ITEM_ID"
AND "A7"."FRCST_PURP" ="A1"."FRCST_PURP"
AND "A7"."FRCST_TYP" ="A1"."FRCST_TYP" GROUP BY GROUPING SETS (("A5"."CLNDR_MNTH_ID"
, "A2"."ORG_RGN_ID"
, "A4"."MFG_ITEM_CLASS_ID"
, "A3"."RTL_ITEM_CLASS_ID"
, "A7"."ITM_TYP"
, "A7"."FRCST_PURP"
, "A7"."FRCST_TYP")
, ("A5"."CLNDR_MNTH_ID"
, "A2"."ORG_CHAIN_ID"
, "A4"."MFG_ITEM_CLASS_ID"
, "A3"."RTL_ITEM_CLASS_ID"
, "A7"."ITM_TYP"
, "A7"."FRCST_PURP"
, "A7"."FRCST_TYP"))