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_INV_MV#

Source

View Text - Preformatted


select                   
  inv.BSNS_WK_ID,
  inv.BSNS_MNTH_ID,
  inv.ORG_RGN_ID,
  inv.ORG_CHAIN_ID,
  inv.MFG_ITEM_CLASS_ID,
  inv.RTL_ITEM_CLASS_ID,
  inv.ITM_TYP,
  decode (grouping_id (inv.BSNS_MNTH_ID, inv.BSNS_WK_ID),
          2, inv.BSNS_WK_ID,
          1, inv.BSNS_MNTH_ID) as BSNS_CLNDR_DIM_ID,
  decode (grouping_id (inv.BSNS_MNTH_ID, inv.BSNS_WK_ID), 
          2, 'WK',
          1, 'MNTH') as BSNS_CLNDR_DIM_LVL,
  decode (grouping_id (inv.ORG_CHAIN_ID, inv.ORG_RGN_ID),
          2, inv.ORG_RGN_ID,
          1, inv.ORG_CHAIN_ID) as ORG_DIM_ID,
  decode (grouping_id (inv.ORG_CHAIN_ID, inv.ORG_RGN_ID),
          2, 'RGN',
          1, 'CHN') as ORG_DIM_LVL,
  inv.MFG_ITEM_CLASS_ID as MFG_ITEM_DIM_ID,
  decode (inv.MFG_ITEM_CLASS_ID, 
          NULL, NULL,
          'CLS') as MFG_ITEM_DIM_LVL,
  inv.RTL_ITEM_CLASS_ID as RTL_ITEM_DIM_ID,
  decode (inv.RTL_ITEM_CLASS_ID, 
          NULL, NULL,
          'CLS') as RTL_ITEM_DIM_LVL,
  count(*) as CNT_TOTAL,
  sum(inv.IN_STCK_BU_ITM_DAYS) as IN_STCK_BU_ITM_DAYS,
  count(inv.IN_STCK_BU_ITM_DAYS) as CNT_IN_STCK_BU_ITM_DAYS,
  sum(inv.UNDR_STCK_BU_ITM_DAYS) as UNDR_STCK_BU_ITM_DAYS,
  count(inv.UNDR_STCK_BU_ITM_DAYS) as CNT_UNDR_STCK_BU_ITM_DAYS,
  sum(nvl(inv.ON_HAND_QTY_PRMRY, 0)) as ON_HAND_QTY_PRMRY,
  sum(nvl(inv.ON_HAND_QTY_ALT, 0)) as ON_HAND_QTY_ALT,
  sum(inv.RECVD_QTY_PRMRY) as RECVD_QTY_PRMRY,
  sum(inv.RECVD_QTY_ALT) as RECVD_QTY_ALT,
  sum(nvl(inv.IN_TRANSIT_QTY_PRMRY, 0)) as IN_TRANSIT_QTY_PRMRY,
  sum(nvl(inv.IN_TRANSIT_QTY_ALT, 0)) as IN_TRANSIT_QTY_ALT,
  sum(nvl(inv.BCK_ORDR_QTY_PRMRY, 0)) as BCK_ORDR_QTY_PRMRY,
  sum(nvl(inv.BCK_ORDR_QTY_ALT, 0)) as BCK_ORDR_QTY_ALT,
  sum(nvl(inv.QLTY_HOLD_QTY_PRMRY, 0)) as QLTY_HOLD_QTY_PRMRY,
  sum(nvl(inv.QLTY_HOLD_QTY_ALT, 0)) as QLTY_HOLD_QTY_ALT,
  sum(nvl(inv.ON_HAND_NET_COST_AMT, 0)) as ON_HAND_NET_COST_AMT,
  sum(nvl(inv.ON_HAND_NET_COST_AMT_RPT, 0)) as ON_HAND_NET_COST_AMT_RPT,
  sum(inv.RECVD_NET_COST_AMT) as RECVD_NET_COST_AMT,
  sum(inv.RECVD_NET_COST_AMT_RPT) as RECVD_NET_COST_AMT_RPT,
  sum(nvl(inv.IN_TRANSIT_NET_COST_AMT, 0)) as IN_TRANSIT_NET_COST_AMT,
  sum(nvl(inv.IN_TRANSIT_NET_COST_AMT_RPT, 0)) as IN_TRANSIT_NET_COST_AMT_RPT,
  sum(nvl(inv.BCKORDR_NET_COST_AMT, 0)) as BCKORDR_NET_COST_AMT,
  sum(nvl(inv.BCKORDR_NET_COST_AMT_RPT, 0)) as BCKORDR_NET_COST_AMT_RPT,
  sum(nvl(inv.QLTY_HOLD_NET_COST_AMT, 0)) as QLTY_HOLD_NET_COST_AMT,
  sum(nvl(inv.QLTY_HOLD_NET_COST_AMT_RPT, 0)) as QLTY_HOLD_NET_COST_AMT_RPT,
  sum(nvl(inv.ON_HAND_RTL_AMT, 0)) as ON_HAND_RTL_AMT,
  sum(nvl(inv.ON_HAND_RTL_AMT_RPT, 0)) as ON_HAND_RTL_AMT_RPT,
  sum(inv.RECVD_RTL_AMT) as RECVD_RTL_AMT,
  sum(inv.RECVD_RTL_AMT_RPT) as RECVD_RTL_AMT_RPT,
  sum(nvl(inv.IN_TRANSIT_RTL_AMT, 0)) as IN_TRANSIT_RTL_AMT,
  sum(nvl(inv.IN_TRANSIT_RTL_AMT_RPT, 0)) as IN_TRANSIT_RTL_AMT_RPT,
  sum(nvl(inv.BCKORDR_RTL_AMT, 0)) as BCKORDR_RTL_AMT,
  sum(nvl(inv.BCKORDR_RTL_AMT_RPT, 0)) as BCKORDR_RTL_AMT_RPT,
  sum(nvl(inv.QLTY_HOLD_RTL_AMT, 0)) as QLTY_HOLD_RTL_AMT,
  sum(nvl(inv.QLTY_HOLD_RTL_AMT_RPT, 0)) as QLTY_HOLD_RTL_AMT_RPT,
  count(nvl(inv.ON_HAND_QTY_PRMRY, 0)) as CNT_ON_HAND_QTY_PRMRY,
  count(nvl(inv.ON_HAND_QTY_ALT, 0)) as CNT_ON_HAND_QTY_ALT,
  count(inv.RECVD_QTY_PRMRY) as CNT_RECVD_QTY_PRMRY,
  count(inv.RECVD_QTY_ALT) as CNT_RECVD_QTY_ALT,
  count(nvl(inv.IN_TRANSIT_QTY_PRMRY, 0)) as CNT_IN_TRANSIT_QTY_PRMRY,
  count(nvl(inv.IN_TRANSIT_QTY_ALT, 0)) as CNT_IN_TRANSIT_QTY_ALT,
  count(nvl(inv.BCK_ORDR_QTY_PRMRY, 0)) as CNT_BCK_ORDR_QTY_PRMRY,
  count(nvl(inv.BCK_ORDR_QTY_ALT, 0)) as CNT_BCK_ORDR_QTY_ALT,
  count(nvl(inv.QLTY_HOLD_QTY_PRMRY, 0)) as CNT_QLTY_HOLD_QTY_PRMRY,
  count(nvl(inv.QLTY_HOLD_QTY_ALT, 0)) as CNT_QLTY_HOLD_QTY_ALT,
  count(nvl(inv.ON_HAND_NET_COST_AMT, 0)) as CNT_ON_HAND_NET_COST_AMT,
  count(nvl(inv.ON_HAND_NET_COST_AMT_RPT, 0)) as CNT_ON_HAND_NET_COST_AMT_RPT,
  count(inv.RECVD_NET_COST_AMT) as CNT_RECVD_NET_COST_AMT,
  count(inv.RECVD_NET_COST_AMT_RPT) as CNT_RECVD_NET_COST_AMT_RPT,
  count(nvl(inv.IN_TRANSIT_NET_COST_AMT, 0)) as CNT_IN_TRANSIT_NET_COST_AMT,
  count(nvl(inv.IN_TRANSIT_NET_COST_AMT_RPT, 0)) as CNT_IN_TRANSIT_NET_CST_AMT_RPT,
  count(nvl(inv.BCKORDR_NET_COST_AMT, 0)) as CNT_BCKORDR_NET_COST_AMT,
  count(nvl(inv.BCKORDR_NET_COST_AMT_RPT, 0)) as CNT_BCKORDR_NET_COST_AMT_RPT,
  count(nvl(inv.QLTY_HOLD_NET_COST_AMT, 0)) as CNT_QLTY_HOLD_NET_COST_AMT,
  count(nvl(inv.QLTY_HOLD_NET_COST_AMT_RPT, 0)) as CNT_QLTY_HOLD_NET_COST_AMT_RPT,
  count(nvl(inv.ON_HAND_RTL_AMT, 0)) as CNT_ON_HAND_RTL_AMT,
  count(nvl(inv.ON_HAND_RTL_AMT_RPT, 0)) as CNT_ON_HAND_RTL_AMT_RPT,
  count(inv.RECVD_RTL_AMT) as CNT_RECVD_RTL_AMT,
  count(inv.RECVD_RTL_AMT_RPT) as CNT_RECVD_RTL_AMT_RPT,
  count(nvl(inv.IN_TRANSIT_RTL_AMT, 0)) as CNT_IN_TRANSIT_RTL_AMT,
  count(nvl(inv.IN_TRANSIT_RTL_AMT_RPT, 0)) as CNT_IN_TRANSIT_RTL_AMT_RPT,
  count(nvl(inv.BCKORDR_RTL_AMT, 0)) as CNT_BCKORDR_RTL_AMT,
  count(nvl(inv.BCKORDR_RTL_AMT_RPT, 0)) as CNT_BCKORDR_RTL_AMT_RPT,
  count(nvl(inv.QLTY_HOLD_RTL_AMT, 0)) as CNT_QLTY_HOLD_RTL_AMT,
  count(nvl(inv.QLTY_HOLD_RTL_AMT_RPT, 0)) as CNT_QLTY_HOLD_RTL_AMT_RPT,
  grouping_id (inv.ITM_TYP, inv.MFG_ITEM_CLASS_ID, inv.RTL_ITEM_CLASS_ID, 
               inv.ORG_RGN_ID, inv.ORG_CHAIN_ID, inv.BSNS_WK_ID, 
               inv.BSNS_MNTH_ID) GROUPING_ID
from ddr_bs_inv_itm_bu_wk_mv        inv
group by
  Grouping sets((inv.BSNS_MNTH_ID, inv.ORG_RGN_ID,   inv.MFG_ITEM_CLASS_ID, inv.RTL_ITEM_CLASS_ID, inv.ITM_TYP),
                (inv.BSNS_MNTH_ID, inv.ORG_CHAIN_ID, inv.MFG_ITEM_CLASS_ID, inv.RTL_ITEM_CLASS_ID, inv.ITM_TYP),
                (inv.BSNS_WK_ID,   inv.ORG_RGN_ID,   inv.ITM_TYP),
                (inv.BSNS_WK_ID,   inv.ORG_CHAIN_ID, inv.ITM_TYP),
                (inv.BSNS_WK_ID,   inv.ORG_CHAIN_ID, inv.RTL_ITEM_CLASS_ID, inv.ITM_TYP))

View Text - HTML Formatted

SELECT INV.BSNS_WK_ID
, INV.BSNS_MNTH_ID
, INV.ORG_RGN_ID
, INV.ORG_CHAIN_ID
, INV.MFG_ITEM_CLASS_ID
, INV.RTL_ITEM_CLASS_ID
, INV.ITM_TYP
, DECODE (GROUPING_ID (INV.BSNS_MNTH_ID
, INV.BSNS_WK_ID)
, 2
, INV.BSNS_WK_ID
, 1
, INV.BSNS_MNTH_ID) AS BSNS_CLNDR_DIM_ID
, DECODE (GROUPING_ID (INV.BSNS_MNTH_ID
, INV.BSNS_WK_ID)
, 2
, 'WK'
, 1
, 'MNTH') AS BSNS_CLNDR_DIM_LVL
, DECODE (GROUPING_ID (INV.ORG_CHAIN_ID
, INV.ORG_RGN_ID)
, 2
, INV.ORG_RGN_ID
, 1
, INV.ORG_CHAIN_ID) AS ORG_DIM_ID
, DECODE (GROUPING_ID (INV.ORG_CHAIN_ID
, INV.ORG_RGN_ID)
, 2
, 'RGN'
, 1
, 'CHN') AS ORG_DIM_LVL
, INV.MFG_ITEM_CLASS_ID AS MFG_ITEM_DIM_ID
, DECODE (INV.MFG_ITEM_CLASS_ID
, NULL
, NULL
, 'CLS') AS MFG_ITEM_DIM_LVL
, INV.RTL_ITEM_CLASS_ID AS RTL_ITEM_DIM_ID
, DECODE (INV.RTL_ITEM_CLASS_ID
, NULL
, NULL
, 'CLS') AS RTL_ITEM_DIM_LVL
, COUNT(*) AS CNT_TOTAL
, SUM(INV.IN_STCK_BU_ITM_DAYS) AS IN_STCK_BU_ITM_DAYS
, COUNT(INV.IN_STCK_BU_ITM_DAYS) AS CNT_IN_STCK_BU_ITM_DAYS
, SUM(INV.UNDR_STCK_BU_ITM_DAYS) AS UNDR_STCK_BU_ITM_DAYS
, COUNT(INV.UNDR_STCK_BU_ITM_DAYS) AS CNT_UNDR_STCK_BU_ITM_DAYS
, SUM(NVL(INV.ON_HAND_QTY_PRMRY
, 0)) AS ON_HAND_QTY_PRMRY
, SUM(NVL(INV.ON_HAND_QTY_ALT
, 0)) AS ON_HAND_QTY_ALT
, SUM(INV.RECVD_QTY_PRMRY) AS RECVD_QTY_PRMRY
, SUM(INV.RECVD_QTY_ALT) AS RECVD_QTY_ALT
, SUM(NVL(INV.IN_TRANSIT_QTY_PRMRY
, 0)) AS IN_TRANSIT_QTY_PRMRY
, SUM(NVL(INV.IN_TRANSIT_QTY_ALT
, 0)) AS IN_TRANSIT_QTY_ALT
, SUM(NVL(INV.BCK_ORDR_QTY_PRMRY
, 0)) AS BCK_ORDR_QTY_PRMRY
, SUM(NVL(INV.BCK_ORDR_QTY_ALT
, 0)) AS BCK_ORDR_QTY_ALT
, SUM(NVL(INV.QLTY_HOLD_QTY_PRMRY
, 0)) AS QLTY_HOLD_QTY_PRMRY
, SUM(NVL(INV.QLTY_HOLD_QTY_ALT
, 0)) AS QLTY_HOLD_QTY_ALT
, SUM(NVL(INV.ON_HAND_NET_COST_AMT
, 0)) AS ON_HAND_NET_COST_AMT
, SUM(NVL(INV.ON_HAND_NET_COST_AMT_RPT
, 0)) AS ON_HAND_NET_COST_AMT_RPT
, SUM(INV.RECVD_NET_COST_AMT) AS RECVD_NET_COST_AMT
, SUM(INV.RECVD_NET_COST_AMT_RPT) AS RECVD_NET_COST_AMT_RPT
, SUM(NVL(INV.IN_TRANSIT_NET_COST_AMT
, 0)) AS IN_TRANSIT_NET_COST_AMT
, SUM(NVL(INV.IN_TRANSIT_NET_COST_AMT_RPT
, 0)) AS IN_TRANSIT_NET_COST_AMT_RPT
, SUM(NVL(INV.BCKORDR_NET_COST_AMT
, 0)) AS BCKORDR_NET_COST_AMT
, SUM(NVL(INV.BCKORDR_NET_COST_AMT_RPT
, 0)) AS BCKORDR_NET_COST_AMT_RPT
, SUM(NVL(INV.QLTY_HOLD_NET_COST_AMT
, 0)) AS QLTY_HOLD_NET_COST_AMT
, SUM(NVL(INV.QLTY_HOLD_NET_COST_AMT_RPT
, 0)) AS QLTY_HOLD_NET_COST_AMT_RPT
, SUM(NVL(INV.ON_HAND_RTL_AMT
, 0)) AS ON_HAND_RTL_AMT
, SUM(NVL(INV.ON_HAND_RTL_AMT_RPT
, 0)) AS ON_HAND_RTL_AMT_RPT
, SUM(INV.RECVD_RTL_AMT) AS RECVD_RTL_AMT
, SUM(INV.RECVD_RTL_AMT_RPT) AS RECVD_RTL_AMT_RPT
, SUM(NVL(INV.IN_TRANSIT_RTL_AMT
, 0)) AS IN_TRANSIT_RTL_AMT
, SUM(NVL(INV.IN_TRANSIT_RTL_AMT_RPT
, 0)) AS IN_TRANSIT_RTL_AMT_RPT
, SUM(NVL(INV.BCKORDR_RTL_AMT
, 0)) AS BCKORDR_RTL_AMT
, SUM(NVL(INV.BCKORDR_RTL_AMT_RPT
, 0)) AS BCKORDR_RTL_AMT_RPT
, SUM(NVL(INV.QLTY_HOLD_RTL_AMT
, 0)) AS QLTY_HOLD_RTL_AMT
, SUM(NVL(INV.QLTY_HOLD_RTL_AMT_RPT
, 0)) AS QLTY_HOLD_RTL_AMT_RPT
, COUNT(NVL(INV.ON_HAND_QTY_PRMRY
, 0)) AS CNT_ON_HAND_QTY_PRMRY
, COUNT(NVL(INV.ON_HAND_QTY_ALT
, 0)) AS CNT_ON_HAND_QTY_ALT
, COUNT(INV.RECVD_QTY_PRMRY) AS CNT_RECVD_QTY_PRMRY
, COUNT(INV.RECVD_QTY_ALT) AS CNT_RECVD_QTY_ALT
, COUNT(NVL(INV.IN_TRANSIT_QTY_PRMRY
, 0)) AS CNT_IN_TRANSIT_QTY_PRMRY
, COUNT(NVL(INV.IN_TRANSIT_QTY_ALT
, 0)) AS CNT_IN_TRANSIT_QTY_ALT
, COUNT(NVL(INV.BCK_ORDR_QTY_PRMRY
, 0)) AS CNT_BCK_ORDR_QTY_PRMRY
, COUNT(NVL(INV.BCK_ORDR_QTY_ALT
, 0)) AS CNT_BCK_ORDR_QTY_ALT
, COUNT(NVL(INV.QLTY_HOLD_QTY_PRMRY
, 0)) AS CNT_QLTY_HOLD_QTY_PRMRY
, COUNT(NVL(INV.QLTY_HOLD_QTY_ALT
, 0)) AS CNT_QLTY_HOLD_QTY_ALT
, COUNT(NVL(INV.ON_HAND_NET_COST_AMT
, 0)) AS CNT_ON_HAND_NET_COST_AMT
, COUNT(NVL(INV.ON_HAND_NET_COST_AMT_RPT
, 0)) AS CNT_ON_HAND_NET_COST_AMT_RPT
, COUNT(INV.RECVD_NET_COST_AMT) AS CNT_RECVD_NET_COST_AMT
, COUNT(INV.RECVD_NET_COST_AMT_RPT) AS CNT_RECVD_NET_COST_AMT_RPT
, COUNT(NVL(INV.IN_TRANSIT_NET_COST_AMT
, 0)) AS CNT_IN_TRANSIT_NET_COST_AMT
, COUNT(NVL(INV.IN_TRANSIT_NET_COST_AMT_RPT
, 0)) AS CNT_IN_TRANSIT_NET_CST_AMT_RPT
, COUNT(NVL(INV.BCKORDR_NET_COST_AMT
, 0)) AS CNT_BCKORDR_NET_COST_AMT
, COUNT(NVL(INV.BCKORDR_NET_COST_AMT_RPT
, 0)) AS CNT_BCKORDR_NET_COST_AMT_RPT
, COUNT(NVL(INV.QLTY_HOLD_NET_COST_AMT
, 0)) AS CNT_QLTY_HOLD_NET_COST_AMT
, COUNT(NVL(INV.QLTY_HOLD_NET_COST_AMT_RPT
, 0)) AS CNT_QLTY_HOLD_NET_COST_AMT_RPT
, COUNT(NVL(INV.ON_HAND_RTL_AMT
, 0)) AS CNT_ON_HAND_RTL_AMT
, COUNT(NVL(INV.ON_HAND_RTL_AMT_RPT
, 0)) AS CNT_ON_HAND_RTL_AMT_RPT
, COUNT(INV.RECVD_RTL_AMT) AS CNT_RECVD_RTL_AMT
, COUNT(INV.RECVD_RTL_AMT_RPT) AS CNT_RECVD_RTL_AMT_RPT
, COUNT(NVL(INV.IN_TRANSIT_RTL_AMT
, 0)) AS CNT_IN_TRANSIT_RTL_AMT
, COUNT(NVL(INV.IN_TRANSIT_RTL_AMT_RPT
, 0)) AS CNT_IN_TRANSIT_RTL_AMT_RPT
, COUNT(NVL(INV.BCKORDR_RTL_AMT
, 0)) AS CNT_BCKORDR_RTL_AMT
, COUNT(NVL(INV.BCKORDR_RTL_AMT_RPT
, 0)) AS CNT_BCKORDR_RTL_AMT_RPT
, COUNT(NVL(INV.QLTY_HOLD_RTL_AMT
, 0)) AS CNT_QLTY_HOLD_RTL_AMT
, COUNT(NVL(INV.QLTY_HOLD_RTL_AMT_RPT
, 0)) AS CNT_QLTY_HOLD_RTL_AMT_RPT
, GROUPING_ID (INV.ITM_TYP
, INV.MFG_ITEM_CLASS_ID
, INV.RTL_ITEM_CLASS_ID
, INV.ORG_RGN_ID
, INV.ORG_CHAIN_ID
, INV.BSNS_WK_ID
, INV.BSNS_MNTH_ID) GROUPING_ID FROM DDR_BS_INV_ITM_BU_WK_MV INV GROUP BY GROUPING SETS((INV.BSNS_MNTH_ID
, INV.ORG_RGN_ID
, INV.MFG_ITEM_CLASS_ID
, INV.RTL_ITEM_CLASS_ID
, INV.ITM_TYP)
, (INV.BSNS_MNTH_ID
, INV.ORG_CHAIN_ID
, INV.MFG_ITEM_CLASS_ID
, INV.RTL_ITEM_CLASS_ID
, INV.ITM_TYP)
, (INV.BSNS_WK_ID
, INV.ORG_RGN_ID
, INV.ITM_TYP)
, (INV.BSNS_WK_ID
, INV.ORG_CHAIN_ID
, INV.ITM_TYP)
, (INV.BSNS_WK_ID
, INV.ORG_CHAIN_ID
, INV.RTL_ITEM_CLASS_ID
, INV.ITM_TYP))