VIEW: APPS.DDR_SV_MFG_SHIP_C_MV#

Source

View Text - Preformatted

SELECT
    /*+ NO_STATS_GSETS */
    "A4"."CLNDR_MNTH_ID" "CLNDR_MNTH_ID",
    "A2"."ORG_CHAIN_ID" "ORG_CHAIN_ID",
    "A1"."ORG_CHAIN_ID" "SHIP_TO_CHAIN_ID",
    "A3"."MFG_ITEM_CLASS_ID" "MFG_ITEM_CLASS_ID",
    "A4"."CLNDR_MNTH_ID" "CLNDR_DIM_ID",
    'MNTH' "CLNDR_DIM_LVL",
    "A2"."ORG_CHAIN_ID" "ORG_DIM_ID",
    'CHN' "ORG_DIM_LVL",
    "A3"."MFG_ITEM_CLASS_ID" "MFG_ITEM_DIM_ID",
    'CLS' "MFG_ITEM_DIM_LVL",
    COUNT(*) "CNT_TOTAL",
    SUM("A5"."SHIP_QTY_PRMRY") "SHIP_QTY_PRMRY",
    SUM("A5"."SHIP_QTY_ALT") "SHIP_QTY_ALT",
    SUM("A5"."SHIP_AMT") "SHIP_AMT",
    SUM("A5"."SHIP_COST") "SHIP_COST",
    COUNT("A5"."SHIP_QTY_PRMRY") "CNT_SHIP_QTY_PRMRY",
    COUNT("A5"."SHIP_QTY_ALT") "CNT_SHIP_QTY_ALT",
    COUNT("A5"."SHIP_AMT") "CNT_SHIP_AMT",
    COUNT("A5"."SHIP_COST") "CNT_SHIP_COST",
    GROUPING_ID("A3"."MFG_ITEM_CLASS_ID","A4"."CLNDR_MNTH_ID","A2"."ORG_CHAIN_ID","A1"."ORG_CHAIN_ID") "GROUPING_ID"
  FROM "DDR_B_MFG_SHIP_ITEM_DAY" "A5",
    "DDR_R_CLNDR_DAY_DN_MV" "A4",
    "DDR_R_MFG_ITEM_SKU_DN_MV" "A3",
    "DDR_R_ORG_BU_DN_MV" "A2",
    "DDR_R_ORG_BU_DN_MV" "A1"
  WHERE "A4"."DAY_CD"  ="A5"."DAY_CD"
  AND "A4"."CLNDR_DESC"="A3"."MFG_ORG_CD"
    ||'-CLNDR'
  AND "A3"."MFG_SKU_ITEM_ID" ="A5"."MFG_SKU_ITEM_ID"
  AND "A2"."ORG_BSNS_UNIT_ID"="A5"."BSNS_UNIT_ID"
  and "A1"."ORG_BSNS_UNIT_ID"="A5"."SHIP_TO_BSNS_UNIT_ID"
  GROUP BY GROUPING SETS (("A1"."ORG_CHAIN_ID", "A2"."ORG_CHAIN_ID", "A4"."CLNDR_MNTH_ID", "A3"."MFG_ITEM_CLASS_ID"), ("A1"."ORG_CHAIN_ID", "A4"."CLNDR_MNTH_ID", "A3"."MFG_ITEM_CLASS_ID"), ("A2"."ORG_CHAIN_ID", "A4"."CLNDR_MNTH_ID", "A3"."MFG_ITEM_CLASS_ID"))

View Text - HTML Formatted

SELECT /*+ NO_STATS_GSETS */ "A4"."CLNDR_MNTH_ID" "CLNDR_MNTH_ID"
, "A2"."ORG_CHAIN_ID" "ORG_CHAIN_ID"
, "A1"."ORG_CHAIN_ID" "SHIP_TO_CHAIN_ID"
, "A3"."MFG_ITEM_CLASS_ID" "MFG_ITEM_CLASS_ID"
, "A4"."CLNDR_MNTH_ID" "CLNDR_DIM_ID"
, 'MNTH' "CLNDR_DIM_LVL"
, "A2"."ORG_CHAIN_ID" "ORG_DIM_ID"
, 'CHN' "ORG_DIM_LVL"
, "A3"."MFG_ITEM_CLASS_ID" "MFG_ITEM_DIM_ID"
, 'CLS' "MFG_ITEM_DIM_LVL"
, COUNT(*) "CNT_TOTAL"
, SUM("A5"."SHIP_QTY_PRMRY") "SHIP_QTY_PRMRY"
, SUM("A5"."SHIP_QTY_ALT") "SHIP_QTY_ALT"
, SUM("A5"."SHIP_AMT") "SHIP_AMT"
, SUM("A5"."SHIP_COST") "SHIP_COST"
, COUNT("A5"."SHIP_QTY_PRMRY") "CNT_SHIP_QTY_PRMRY"
, COUNT("A5"."SHIP_QTY_ALT") "CNT_SHIP_QTY_ALT"
, COUNT("A5"."SHIP_AMT") "CNT_SHIP_AMT"
, COUNT("A5"."SHIP_COST") "CNT_SHIP_COST"
, GROUPING_ID("A3"."MFG_ITEM_CLASS_ID"
, "A4"."CLNDR_MNTH_ID"
, "A2"."ORG_CHAIN_ID"
, "A1"."ORG_CHAIN_ID") "GROUPING_ID"
FROM "DDR_B_MFG_SHIP_ITEM_DAY" "A5"
, "DDR_R_CLNDR_DAY_DN_MV" "A4"
, "DDR_R_MFG_ITEM_SKU_DN_MV" "A3"
, "DDR_R_ORG_BU_DN_MV" "A2"
, "DDR_R_ORG_BU_DN_MV" "A1"
WHERE "A4"."DAY_CD" ="A5"."DAY_CD"
AND "A4"."CLNDR_DESC"="A3"."MFG_ORG_CD" ||'-CLNDR'
AND "A3"."MFG_SKU_ITEM_ID" ="A5"."MFG_SKU_ITEM_ID"
AND "A2"."ORG_BSNS_UNIT_ID"="A5"."BSNS_UNIT_ID"
AND "A1"."ORG_BSNS_UNIT_ID"="A5"."SHIP_TO_BSNS_UNIT_ID" GROUP BY GROUPING SETS (("A1"."ORG_CHAIN_ID"
, "A2"."ORG_CHAIN_ID"
, "A4"."CLNDR_MNTH_ID"
, "A3"."MFG_ITEM_CLASS_ID")
, ("A1"."ORG_CHAIN_ID"
, "A4"."CLNDR_MNTH_ID"
, "A3"."MFG_ITEM_CLASS_ID")
, ("A2"."ORG_CHAIN_ID"
, "A4"."CLNDR_MNTH_ID"
, "A3"."MFG_ITEM_CLASS_ID"))