SELECT "A6"."MFG_ORG_CD" "MFG_ORG_CD",
"A6"."RTL_ORG_CD" "RTL_ORG_CD",
"A6"."ORG_BSNS_UNIT_ID" "ORG_BSNS_UNIT_ID",
"A6"."ITM_TYP" "ITM_TYP",
"A2"."ORG_RGN_ID" "ORG_RGN_ID",
"A2"."ORG_CHAIN_ID" "ORG_CHAIN_ID",
"A4"."MFG_ITEM_ID" "MFG_ITEM_ID",
"A4"."MFG_ITEM_CLASS_ID" "MFG_ITEM_CLASS_ID",
"A3"."RTL_ITEM_ID" "RTL_ITEM_ID",
"A3"."RTL_ITEM_CLASS_ID" "RTL_ITEM_CLASS_ID",
"A5"."CLNDR_WK_ID" "CLNDR_WK_ID",
COUNT(*) "CNT_TOTAL",
COUNT(DECODE(SIGN("A6"."ON_HAND_QTY_PRMRY"),1,1,NULL)) "IN_STCK_BU_ITM_DAYS",
COUNT(DECODE(SIGN("A6"."ON_HAND_QTY_PRMRY"-"A1"."MIN_STCK_ON_HAND_QTY_PRMRY"),(-1),1,NULL)) "UNDR_STCK_BU_ITM_DAYS",
AVG("A6"."ON_HAND_QTY_PRMRY" *7) "ON_HAND_QTY_PRMRY",
AVG("A6"."ON_HAND_QTY_ALT" *7) "ON_HAND_QTY_ALT",
SUM(NVL("A6"."RECVD_QTY_PRMRY",0)) "RECVD_QTY_PRMRY",
SUM(NVL("A6"."RECVD_QTY_ALT",0)) "RECVD_QTY_ALT",
AVG("A6"."IN_TRANSIT_QTY_PRMRY" *7) "IN_TRANSIT_QTY_PRMRY",
AVG("A6"."IN_TRANSIT_QTY_ALT" *7) "IN_TRANSIT_QTY_ALT",
AVG("A6"."BCK_ORDR_QTY_PRMRY" *7) "BCK_ORDR_QTY_PRMRY",
AVG("A6"."BCK_ORDR_QTY_ALT" *7) "BCK_ORDR_QTY_ALT",
AVG("A6"."QLTY_HOLD_QTY_PRMRY" *7) "QLTY_HOLD_QTY_PRMRY",
AVG("A6"."QLTY_HOLD_QTY_ALT" *7) "QLTY_HOLD_QTY_ALT",
AVG("A6"."ON_HAND_NET_COST_AMT" *7) "ON_HAND_NET_COST_AMT",
AVG("A6"."ON_HAND_NET_COST_AMT_RPT"*7) "ON_HAND_NET_COST_AMT_RPT",
SUM(NVL("A6"."RECVD_NET_COST_AMT",0)) "RECVD_NET_COST_AMT",
SUM(NVL("A6"."RECVD_NET_COST_AMT_RPT",0)) "RECVD_NET_COST_AMT_RPT",
AVG("A6"."IN_TRANSIT_NET_COST_AMT" *7) "IN_TRANSIT_NET_COST_AMT",
AVG("A6"."IN_TRANSIT_NET_COST_AMT_RPT"*7) "IN_TRANSIT_NET_COST_AMT_RPT",
AVG("A6"."BCKORDR_NET_COST_AMT" *7) "BCKORDR_NET_COST_AMT",
AVG("A6"."BCKORDR_NET_COST_AMT_RPT" *7) "BCKORDR_NET_COST_AMT_RPT",
AVG("A6"."QLTY_HOLD_NET_COST_AMT" *7) "QLTY_HOLD_NET_COST_AMT",
AVG("A6"."QLTY_HOLD_NET_COST_AMT_RPT" *7) "QLTY_HOLD_NET_COST_AMT_RPT",
AVG("A6"."ON_HAND_RTL_AMT" *7) "ON_HAND_RTL_AMT",
AVG("A6"."ON_HAND_RTL_AMT_RPT" *7) "ON_HAND_RTL_AMT_RPT",
SUM(NVL("A6"."RECVD_RTL_AMT",0)) "RECVD_RTL_AMT",
SUM(NVL("A6"."RECVD_RTL_AMT_RPT",0)) "RECVD_RTL_AMT_RPT",
AVG("A6"."IN_TRANSIT_RTL_AMT" *7) "IN_TRANSIT_RTL_AMT",
AVG("A6"."IN_TRANSIT_RTL_AMT_RPT"*7) "IN_TRANSIT_RTL_AMT_RPT",
AVG("A6"."BCKORDR_RTL_AMT" *7) "BCKORDR_RTL_AMT",
AVG("A6"."BCKORDR_RTL_AMT_RPT" *7) "BCKORDR_RTL_AMT_RPT",
AVG("A6"."QLTY_HOLD_RTL_AMT" *7) "QLTY_HOLD_RTL_AMT",
AVG("A6"."QLTY_HOLD_RTL_AMT_RPT" *7) "QLTY_HOLD_RTL_AMT_RPT",
COUNT("A6"."ON_HAND_QTY_PRMRY" *7) "CNT_ON_HAND_QTY_PRMRY",
COUNT("A6"."ON_HAND_QTY_ALT" *7) "CNT_ON_HAND_QTY_ALT",
COUNT(NVL("A6"."RECVD_QTY_PRMRY",0)) "CNT_RECVD_QTY_PRMRY",
COUNT(NVL("A6"."RECVD_QTY_ALT",0)) "CNT_RECVD_QTY_ALT",
COUNT("A6"."IN_TRANSIT_QTY_PRMRY" *7) "CNT_IN_TRANSIT_QTY_PRMRY",
COUNT("A6"."IN_TRANSIT_QTY_ALT" *7) "CNT_IN_TRANSIT_QTY_ALT",
COUNT("A6"."BCK_ORDR_QTY_PRMRY" *7) "CNT_BCK_ORDR_QTY_PRMRY",
COUNT("A6"."BCK_ORDR_QTY_ALT" *7) "CNT_BCK_ORDR_QTY_ALT",
COUNT("A6"."QLTY_HOLD_QTY_PRMRY" *7) "CNT_QLTY_HOLD_QTY_PRMRY",
COUNT("A6"."QLTY_HOLD_QTY_ALT" *7) "CNT_QLTY_HOLD_QTY_ALT",
COUNT("A6"."ON_HAND_NET_COST_AMT" *7) "CNT_ON_HAND_NET_COST_AMT",
COUNT("A6"."ON_HAND_NET_COST_AMT_RPT"*7) "CNT_ON_HAND_NET_COST_AMT_RPT",
COUNT(NVL("A6"."RECVD_NET_COST_AMT",0)) "CNT_RECVD_NET_COST_AMT",
COUNT(NVL("A6"."RECVD_NET_COST_AMT_RPT",0)) "CNT_RECVD_NET_COST_AMT_RPT",
COUNT("A6"."IN_TRANSIT_NET_COST_AMT" *7) "CNT_IN_TRANSIT_NET_COST_AMT",
COUNT("A6"."IN_TRANSIT_NET_COST_AMT_RPT"*7) "CNT_IN_TRANSIT_NET_CST_AMT_RPT",
COUNT("A6"."BCKORDR_NET_COST_AMT" *7) "CNT_BCKORDR_NET_COST_AMT",
COUNT("A6"."BCKORDR_NET_COST_AMT_RPT" *7) "CNT_BCKORDR_NET_COST_AMT_RPT",
COUNT("A6"."QLTY_HOLD_NET_COST_AMT" *7) "CNT_QLTY_HOLD_NET_COST_AMT",
COUNT("A6"."QLTY_HOLD_NET_COST_AMT_RPT" *7) "CNT_QLTY_HOLD_NET_COST_AMT_RPT",
COUNT("A6"."ON_HAND_RTL_AMT" *7) "CNT_ON_HAND_RTL_AMT",
COUNT("A6"."ON_HAND_RTL_AMT_RPT" *7) "CNT_ON_HAND_RTL_AMT_RPT",
COUNT(NVL("A6"."RECVD_RTL_AMT",0)) "CNT_RECVD_RTL_AMT",
COUNT(NVL("A6"."RECVD_RTL_AMT_RPT",0)) "CNT_RECVD_RTL_AMT_RPT",
COUNT("A6"."IN_TRANSIT_RTL_AMT" *7) "CNT_IN_TRANSIT_RTL_AMT",
COUNT("A6"."IN_TRANSIT_RTL_AMT_RPT"*7) "CNT_IN_TRANSIT_RTL_AMT_RPT",
COUNT("A6"."BCKORDR_RTL_AMT" *7) "CNT_BCKORDR_RTL_AMT",
COUNT("A6"."BCKORDR_RTL_AMT_RPT" *7) "CNT_BCKORDR_RTL_AMT_RPT",
COUNT("A6"."QLTY_HOLD_RTL_AMT" *7) "CNT_QLTY_HOLD_RTL_AMT",
COUNT("A6"."QLTY_HOLD_RTL_AMT_RPT" *7) "CNT_QLTY_HOLD_RTL_AMT_RPT"
FROM "APPS"."DDR_BS_INV_SKU_BU_DAY_MV" "A6",
"APPS"."DDR_R_CLNDR_DAY_DN_MV" "A5",
"APPS"."DDR_R_MFG_ITEM_SKU_DN_MV" "A4",
"APPS"."DDR_R_RTL_ITEM_SKU_DN_MV" "A3",
"APPS"."DDR_R_ORG_BU_DN_MV" "A2",
"APPS"."DDR_R_INV_RULE_DN_MV" "A1"
WHERE "A5"."DAY_CD" ="A6"."DAY_CD"
AND "A5"."CLNDR_DESC" = "A6"."MFG_ORG_CD"
||'-CLNDR'
AND "A4"."MFG_SKU_ITEM_ID" ="A6"."MFG_SKU_ITEM_ID"
AND "A3"."RTL_SKU_ITEM_ID" ="A6"."RTL_SKU_ITEM_ID"
AND "A2"."ORG_BSNS_UNIT_ID"="A6"."ORG_BSNS_UNIT_ID"
AND "A2"."ORG_CD" ="A6"."RTL_ORG_CD"
AND "A1"."RTL_ORG_CD" ="A2"."ORG_CD"
AND "A1"."BSNS_UNIT_CD" ="A2"."BSNS_UNIT_CD"
AND "A1"."GLBL_ITEM_ID" ="A6"."GLBL_ITEM_ID"
AND "A1"."GLBL_ITEM_ID_TYP"="A6"."GLBL_ITEM_ID_TYP"
AND "A1"."RTL_SKU_ITEM_NBR"="A3"."RTL_SKU_ITEM_NBR"
GROUP BY "A5"."CLNDR_WK_ID",
"A6"."MFG_ORG_CD",
"A6"."RTL_ORG_CD",
"A6"."ORG_BSNS_UNIT_ID",
"A6"."ITM_TYP",
"A2"."ORG_RGN_ID",
"A2"."ORG_CHAIN_ID",
"A4"."MFG_ITEM_ID",
"A4"."MFG_ITEM_CLASS_ID",
"A3"."RTL_ITEM_ID",
"A3"."RTL_ITEM_CLASS_ID"
SELECT "A6"."MFG_ORG_CD" "MFG_ORG_CD"
,
"A6"."RTL_ORG_CD" "RTL_ORG_CD"
,
"A6"."ORG_BSNS_UNIT_ID" "ORG_BSNS_UNIT_ID"
,
"A6"."ITM_TYP" "ITM_TYP"
,
"A2"."ORG_RGN_ID" "ORG_RGN_ID"
,
"A2"."ORG_CHAIN_ID" "ORG_CHAIN_ID"
,
"A4"."MFG_ITEM_ID" "MFG_ITEM_ID"
,
"A4"."MFG_ITEM_CLASS_ID" "MFG_ITEM_CLASS_ID"
,
"A3"."RTL_ITEM_ID" "RTL_ITEM_ID"
,
"A3"."RTL_ITEM_CLASS_ID" "RTL_ITEM_CLASS_ID"
,
"A5"."CLNDR_WK_ID" "CLNDR_WK_ID"
,
COUNT(*) "CNT_TOTAL"
,
COUNT(DECODE(SIGN("A6"."ON_HAND_QTY_PRMRY")
, 1
, 1
, NULL)) "IN_STCK_BU_ITM_DAYS"
,
COUNT(DECODE(SIGN("A6"."ON_HAND_QTY_PRMRY"-"A1"."MIN_STCK_ON_HAND_QTY_PRMRY")
, (-1)
, 1
, NULL)) "UNDR_STCK_BU_ITM_DAYS"
,
AVG("A6"."ON_HAND_QTY_PRMRY" *7) "ON_HAND_QTY_PRMRY"
,
AVG("A6"."ON_HAND_QTY_ALT" *7) "ON_HAND_QTY_ALT"
,
SUM(NVL("A6"."RECVD_QTY_PRMRY"
, 0)) "RECVD_QTY_PRMRY"
,
SUM(NVL("A6"."RECVD_QTY_ALT"
, 0)) "RECVD_QTY_ALT"
,
AVG("A6"."IN_TRANSIT_QTY_PRMRY" *7) "IN_TRANSIT_QTY_PRMRY"
,
AVG("A6"."IN_TRANSIT_QTY_ALT" *7) "IN_TRANSIT_QTY_ALT"
,
AVG("A6"."BCK_ORDR_QTY_PRMRY" *7) "BCK_ORDR_QTY_PRMRY"
,
AVG("A6"."BCK_ORDR_QTY_ALT" *7) "BCK_ORDR_QTY_ALT"
,
AVG("A6"."QLTY_HOLD_QTY_PRMRY" *7) "QLTY_HOLD_QTY_PRMRY"
,
AVG("A6"."QLTY_HOLD_QTY_ALT" *7) "QLTY_HOLD_QTY_ALT"
,
AVG("A6"."ON_HAND_NET_COST_AMT" *7) "ON_HAND_NET_COST_AMT"
,
AVG("A6"."ON_HAND_NET_COST_AMT_RPT"*7) "ON_HAND_NET_COST_AMT_RPT"
,
SUM(NVL("A6"."RECVD_NET_COST_AMT"
, 0)) "RECVD_NET_COST_AMT"
,
SUM(NVL("A6"."RECVD_NET_COST_AMT_RPT"
, 0)) "RECVD_NET_COST_AMT_RPT"
,
AVG("A6"."IN_TRANSIT_NET_COST_AMT" *7) "IN_TRANSIT_NET_COST_AMT"
,
AVG("A6"."IN_TRANSIT_NET_COST_AMT_RPT"*7) "IN_TRANSIT_NET_COST_AMT_RPT"
,
AVG("A6"."BCKORDR_NET_COST_AMT" *7) "BCKORDR_NET_COST_AMT"
,
AVG("A6"."BCKORDR_NET_COST_AMT_RPT" *7) "BCKORDR_NET_COST_AMT_RPT"
,
AVG("A6"."QLTY_HOLD_NET_COST_AMT" *7) "QLTY_HOLD_NET_COST_AMT"
,
AVG("A6"."QLTY_HOLD_NET_COST_AMT_RPT" *7) "QLTY_HOLD_NET_COST_AMT_RPT"
,
AVG("A6"."ON_HAND_RTL_AMT" *7) "ON_HAND_RTL_AMT"
,
AVG("A6"."ON_HAND_RTL_AMT_RPT" *7) "ON_HAND_RTL_AMT_RPT"
,
SUM(NVL("A6"."RECVD_RTL_AMT"
, 0)) "RECVD_RTL_AMT"
,
SUM(NVL("A6"."RECVD_RTL_AMT_RPT"
, 0)) "RECVD_RTL_AMT_RPT"
,
AVG("A6"."IN_TRANSIT_RTL_AMT" *7) "IN_TRANSIT_RTL_AMT"
,
AVG("A6"."IN_TRANSIT_RTL_AMT_RPT"*7) "IN_TRANSIT_RTL_AMT_RPT"
,
AVG("A6"."BCKORDR_RTL_AMT" *7) "BCKORDR_RTL_AMT"
,
AVG("A6"."BCKORDR_RTL_AMT_RPT" *7) "BCKORDR_RTL_AMT_RPT"
,
AVG("A6"."QLTY_HOLD_RTL_AMT" *7) "QLTY_HOLD_RTL_AMT"
,
AVG("A6"."QLTY_HOLD_RTL_AMT_RPT" *7) "QLTY_HOLD_RTL_AMT_RPT"
,
COUNT("A6"."ON_HAND_QTY_PRMRY" *7) "CNT_ON_HAND_QTY_PRMRY"
,
COUNT("A6"."ON_HAND_QTY_ALT" *7) "CNT_ON_HAND_QTY_ALT"
,
COUNT(NVL("A6"."RECVD_QTY_PRMRY"
, 0)) "CNT_RECVD_QTY_PRMRY"
,
COUNT(NVL("A6"."RECVD_QTY_ALT"
, 0)) "CNT_RECVD_QTY_ALT"
,
COUNT("A6"."IN_TRANSIT_QTY_PRMRY" *7) "CNT_IN_TRANSIT_QTY_PRMRY"
,
COUNT("A6"."IN_TRANSIT_QTY_ALT" *7) "CNT_IN_TRANSIT_QTY_ALT"
,
COUNT("A6"."BCK_ORDR_QTY_PRMRY" *7) "CNT_BCK_ORDR_QTY_PRMRY"
,
COUNT("A6"."BCK_ORDR_QTY_ALT" *7) "CNT_BCK_ORDR_QTY_ALT"
,
COUNT("A6"."QLTY_HOLD_QTY_PRMRY" *7) "CNT_QLTY_HOLD_QTY_PRMRY"
,
COUNT("A6"."QLTY_HOLD_QTY_ALT" *7) "CNT_QLTY_HOLD_QTY_ALT"
,
COUNT("A6"."ON_HAND_NET_COST_AMT" *7) "CNT_ON_HAND_NET_COST_AMT"
,
COUNT("A6"."ON_HAND_NET_COST_AMT_RPT"*7) "CNT_ON_HAND_NET_COST_AMT_RPT"
,
COUNT(NVL("A6"."RECVD_NET_COST_AMT"
, 0)) "CNT_RECVD_NET_COST_AMT"
,
COUNT(NVL("A6"."RECVD_NET_COST_AMT_RPT"
, 0)) "CNT_RECVD_NET_COST_AMT_RPT"
,
COUNT("A6"."IN_TRANSIT_NET_COST_AMT" *7) "CNT_IN_TRANSIT_NET_COST_AMT"
,
COUNT("A6"."IN_TRANSIT_NET_COST_AMT_RPT"*7) "CNT_IN_TRANSIT_NET_CST_AMT_RPT"
,
COUNT("A6"."BCKORDR_NET_COST_AMT" *7) "CNT_BCKORDR_NET_COST_AMT"
,
COUNT("A6"."BCKORDR_NET_COST_AMT_RPT" *7) "CNT_BCKORDR_NET_COST_AMT_RPT"
,
COUNT("A6"."QLTY_HOLD_NET_COST_AMT" *7) "CNT_QLTY_HOLD_NET_COST_AMT"
,
COUNT("A6"."QLTY_HOLD_NET_COST_AMT_RPT" *7) "CNT_QLTY_HOLD_NET_COST_AMT_RPT"
,
COUNT("A6"."ON_HAND_RTL_AMT" *7) "CNT_ON_HAND_RTL_AMT"
,
COUNT("A6"."ON_HAND_RTL_AMT_RPT" *7) "CNT_ON_HAND_RTL_AMT_RPT"
,
COUNT(NVL("A6"."RECVD_RTL_AMT"
, 0)) "CNT_RECVD_RTL_AMT"
,
COUNT(NVL("A6"."RECVD_RTL_AMT_RPT"
, 0)) "CNT_RECVD_RTL_AMT_RPT"
,
COUNT("A6"."IN_TRANSIT_RTL_AMT" *7) "CNT_IN_TRANSIT_RTL_AMT"
,
COUNT("A6"."IN_TRANSIT_RTL_AMT_RPT"*7) "CNT_IN_TRANSIT_RTL_AMT_RPT"
,
COUNT("A6"."BCKORDR_RTL_AMT" *7) "CNT_BCKORDR_RTL_AMT"
,
COUNT("A6"."BCKORDR_RTL_AMT_RPT" *7) "CNT_BCKORDR_RTL_AMT_RPT"
,
COUNT("A6"."QLTY_HOLD_RTL_AMT" *7) "CNT_QLTY_HOLD_RTL_AMT"
,
COUNT("A6"."QLTY_HOLD_RTL_AMT_RPT" *7) "CNT_QLTY_HOLD_RTL_AMT_RPT"
FROM "APPS"."DDR_BS_INV_SKU_BU_DAY_MV" "A6"
,
"APPS"."DDR_R_CLNDR_DAY_DN_MV" "A5"
,
"APPS"."DDR_R_MFG_ITEM_SKU_DN_MV" "A4"
,
"APPS"."DDR_R_RTL_ITEM_SKU_DN_MV" "A3"
,
"APPS"."DDR_R_ORG_BU_DN_MV" "A2"
,
"APPS"."DDR_R_INV_RULE_DN_MV" "A1"
WHERE "A5"."DAY_CD" ="A6"."DAY_CD"
AND "A5"."CLNDR_DESC" = "A6"."MFG_ORG_CD"
||'-CLNDR'
AND "A4"."MFG_SKU_ITEM_ID" ="A6"."MFG_SKU_ITEM_ID"
AND "A3"."RTL_SKU_ITEM_ID" ="A6"."RTL_SKU_ITEM_ID"
AND "A2"."ORG_BSNS_UNIT_ID"="A6"."ORG_BSNS_UNIT_ID"
AND "A2"."ORG_CD" ="A6"."RTL_ORG_CD"
AND "A1"."RTL_ORG_CD" ="A2"."ORG_CD"
AND "A1"."BSNS_UNIT_CD" ="A2"."BSNS_UNIT_CD"
AND "A1"."GLBL_ITEM_ID" ="A6"."GLBL_ITEM_ID"
AND "A1"."GLBL_ITEM_ID_TYP"="A6"."GLBL_ITEM_ID_TYP"
AND "A1"."RTL_SKU_ITEM_NBR"="A3"."RTL_SKU_ITEM_NBR"
GROUP BY "A5"."CLNDR_WK_ID"
,
"A6"."MFG_ORG_CD"
,
"A6"."RTL_ORG_CD"
,
"A6"."ORG_BSNS_UNIT_ID"
,
"A6"."ITM_TYP"
,
"A2"."ORG_RGN_ID"
,
"A2"."ORG_CHAIN_ID"
,
"A4"."MFG_ITEM_ID"
,
"A4"."MFG_ITEM_CLASS_ID"
,
"A3"."RTL_ITEM_ID"
,
"A3"."RTL_ITEM_CLASS_ID"
|
|
|