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.

[Home] [Help]

View: CST_EDW_BACKLOG_V

Product:	BOM - Bills of Material
Description:
Implementation/DBA Data:	Not implemented in this database

View Text

SELECT SYSDATE || '-' || L.ORG_ID || '-' || H.CUSTOMER_ID || '-' || H.SALES_CHANNEL_CODE || '-' || H.SALESREP_ID || '-' || L.SHIP_TO_SITE_USE_ID || '-' || H.INVOICE_TO_SITE_USE_ID || '-' || L.PROJECT_ID || '-' || L.TASK_ID || '-' || NVL(LD.WAREHOUSE_ID
, NVL(L.WAREHOUSE_ID
, H.WAREHOUSE_ID)) || '-' || NVL(LD.DEMAND_CLASS_CODE
, NVL(L.DEMAND_CLASS_CODE
, H.DEMAND_CLASS_CODE)) || '-' || H.CURRENCY_CODE || '-' || PL.INVENTORY_ITEM_ID || '-' || L.INVENTORY_ITEM_ID || '-' || RT.NAME || '-' || INST.INSTANCE_CODE BACKLOG_PK
, RT.NAME TERRITORY_FK
, SYSDATE DATE_BALANCE_FK
, SUBSTR(BIS_EDW_INSTANCE.GET_CODE
, 0
, 40) INSTANCE_FK
, EDW_DIM_FK.ITEM_ORG(PL.INVENTORY_ITEM_ID
, NVL(LD.WAREHOUSE_ID
, NVL(L.WAREHOUSE_ID
, H.WAREHOUSE_ID))) TOP_MODEL_ITEM_FK
, EDW_DIM_FK.ITEM_ORG(L.INVENTORY_ITEM_ID
, NVL(LD.WAREHOUSE_ID
, NVL(L.WAREHOUSE_ID
, H.WAREHOUSE_ID))) ITEM_ORG_FK
, EDW_UOM_PKG.GET_BASE_UOM_CODE(LD.UNIT_CODE
, NULL) BASE_UOM_FK
, EDW_DIM_FK.OPERATING_UNIT(L.ORG_ID) OPERATING_UNIT_FK
, EDW_DIM_FK.TRADING_PARTNER(H.CUSTOMER_ID) CUSTOMER_FK
, NVL(H.SALES_CHANNEL_CODE
, 'NA_EDW') || '-SALES_CHANNEL-SO_LOOKUPS' SALES_CHANNEL_FK
, EDW_DIM_FK.EMPLOYEE(3
, SALESREP.SALESREP_ID) PRIM_SALES_REP_FK
, EDW_DIM_FK.PROJECT(L.PROJECT_ID) PROJECT_FK
, EDW_DIM_FK.TASK(L.TASK_ID) TASK_FK
, EDW_DIM_FK.BUS_UNIT(1
, NVL(LD.WAREHOUSE_ID
, NVL(L.WAREHOUSE_ID
, H.WAREHOUSE_ID))) INV_ORG_FK
, EDW_DIM_FK.BUS_UNIT(2
, H.INVOICE_TO_SITE_USE_ID) BILL_TO_SITE_FK
, EDW_DIM_FK.BUS_UNIT(2
, L.SHIP_TO_SITE_USE_ID) SHIP_TO_SITE_FK
, NVL(LD.DEMAND_CLASS_CODE
, NVL(L.DEMAND_CLASS_CODE
, NVL(H.DEMAND_CLASS_CODE
, 'NA_EDW'))) DEMAND_CLASS_FK
, H.CURRENCY_CODE TRX_CURRENCY_FK
, -- MEASURES IN TRANSACTION CURRENCY SUM(DECODE(H.ORDER_CATEGORY
, 'R'
, DECODE(CA.ACTION_ID
, 7
, (NVL(L.SELLING_PRICE
, 0) - EDW_UTIL.GET_ITEM_COST(L.INVENTORY_ITEM_ID
, NVL(LD.WAREHOUSE_ID
, NVL(L.WAREHOUSE_ID
, H.WAREHOUSE_ID)))) * DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 1
, DECODE(LD.INCLUDED_ITEM_FLAG
, 'Y'
, 0
, 1)) * DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 1
, DECODE(L.ATO_FLAG
, 'Y'
, DECODE(L.INVENTORY_ITEM_ID
, LD.INVENTORY_ITEM_ID
, 1
, 0)
, 1)) * DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 1
, (LD.QUANTITY / L.ORDERED_QUANTITY)) * (NVL(L.ORDERED_QUANTITY
, 0) - NVL(L.CANCELLED_QUANTITY
, 0) - NVL(L.INVOICED_QUANTITY
, 0))
, 0)
, 0)) BILL_BKLG_MRG_T
, SUM(DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 0
, DECODE(CA.ACTION_ID
, 2
, (NVL(L.SELLING_PRICE
, 0) - EDW_UTIL.GET_ITEM_COST(L.INVENTORY_ITEM_ID
, LD.WAREHOUSE_ID)) * DECODE(LD.INCLUDED_ITEM_FLAG
, 'Y'
, 0
, 1) * DECODE(L.ATO_FLAG
, 'Y'
, DECODE(L.INVENTORY_ITEM_ID
, LD.INVENTORY_ITEM_ID
, 1
, 0)
, 1) * (LD.QUANTITY / L.ORDERED_QUANTITY)*(NVL(L.ORDERED_QUANTITY
, 0) - NVL(L.CANCELLED_QUANTITY
, 0) - NVL(L.SHIPPED_QUANTITY
, 0))
, 0))) SHIP_BKLG_MRG_T
, SUM(DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 0
, DECODE(CA.ACTION_ID
, 2
, (NVL(L.SELLING_PRICE
, 0) - EDW_UTIL.GET_ITEM_COST(L.INVENTORY_ITEM_ID
, LD.WAREHOUSE_ID)) * DECODE(LD.INCLUDED_ITEM_FLAG
, 'Y'
, 0
, 1) * DECODE(L.ATO_FLAG
, 'Y'
, DECODE(L.INVENTORY_ITEM_ID
, LD.INVENTORY_ITEM_ID
, 1
, 0)
, 1) * (LD.QUANTITY / L.ORDERED_QUANTITY)*DECODE(SIGN(NVL(LD.SCHEDULE_DATE
, SYSDATE) - SYSDATE)
, -1
, (NVL(L.ORDERED_QUANTITY
, 0) - NVL(L.CANCELLED_QUANTITY
, 0) - NVL(L.SHIPPED_QUANTITY
, 0))
, 0)
, 0))) DLQT_BKLG_MRG_T
, SUM(DECODE(H.ORDER_CATEGORY
, 'R'
, DECODE(CA.ACTION_ID
, 7
, (NVL(L.SELLING_PRICE
, 0)) * DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 1
, DECODE(LD.INCLUDED_ITEM_FLAG
, 'Y'
, 0
, 1)) * DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 1
, DECODE(L.ATO_FLAG
, 'Y'
, DECODE(L.INVENTORY_ITEM_ID
, LD.INVENTORY_ITEM_ID
, 1
, 0)
, 1)) * DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 1
, (LD.QUANTITY / L.ORDERED_QUANTITY))*(NVL(L.ORDERED_QUANTITY
, 0) - NVL(L.CANCELLED_QUANTITY
, 0) - NVL(L.INVOICED_QUANTITY
, 0))
, 0)
, 0)) BILL_BKLG_REV_T
, SUM(DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 0
, DECODE(CA.ACTION_ID
, 2
, (NVL(L.SELLING_PRICE
, 0)) * DECODE(LD.INCLUDED_ITEM_FLAG
, 'Y'
, 0
, 1) * DECODE(L.ATO_FLAG
, 'Y'
, DECODE(L.INVENTORY_ITEM_ID
, LD.INVENTORY_ITEM_ID
, 1
, 0)
, 1) * (LD.QUANTITY / L.ORDERED_QUANTITY)*(NVL(L.ORDERED_QUANTITY
, 0) - NVL(L.CANCELLED_QUANTITY
, 0) - NVL(L.SHIPPED_QUANTITY
, 0))
, 0))) SHIP_BKLG_REV_T
, SUM(DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 0
, DECODE(CA.ACTION_ID
, 2
, (NVL(L.SELLING_PRICE
, 0)) * DECODE(LD.INCLUDED_ITEM_FLAG
, 'Y'
, 0
, 1) * DECODE(L.ATO_FLAG
, 'Y'
, DECODE(L.INVENTORY_ITEM_ID
, LD.INVENTORY_ITEM_ID
, 1
, 0)
, 1) * (LD.QUANTITY / L.ORDERED_QUANTITY)*DECODE(SIGN(NVL(LD.SCHEDULE_DATE
, SYSDATE) - SYSDATE)
, -1
, (NVL(L.ORDERED_QUANTITY
, 0) - NVL(L.CANCELLED_QUANTITY
, 0) - NVL(L.SHIPPED_QUANTITY
, 0))
, 0)
, 0))) DLQT_BKLG_REV_T
, SUM(DECODE(H.ORDER_CATEGORY
, 'R'
, DECODE(CA.ACTION_ID
, 7
, (EDW_UTIL.GET_ITEM_COST(L.INVENTORY_ITEM_ID
, NVL(LD.WAREHOUSE_ID
, NVL(L.WAREHOUSE_ID
, H.WAREHOUSE_ID)))) * DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 1
, DECODE(LD.INCLUDED_ITEM_FLAG
, 'Y'
, 0
, 1)) * DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 1
, DECODE(L.ATO_FLAG
, 'Y'
, DECODE(L.INVENTORY_ITEM_ID
, LD.INVENTORY_ITEM_ID
, 1
, 0)
, 1)) * DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 1
, (LD.QUANTITY / L.ORDERED_QUANTITY))*(NVL(L.ORDERED_QUANTITY
, 0) - NVL(L.CANCELLED_QUANTITY
, 0) - NVL(L.INVOICED_QUANTITY
, 0))
, 0)
, 0)) BILL_BKLG_COST_T
, SUM(DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 0
, DECODE(CA.ACTION_ID
, 2
, (EDW_UTIL.GET_ITEM_COST(L.INVENTORY_ITEM_ID
, LD.WAREHOUSE_ID)) * DECODE(LD.INCLUDED_ITEM_FLAG
, 'Y'
, 0
, 1) * DECODE(L.ATO_FLAG
, 'Y'
, DECODE(L.INVENTORY_ITEM_ID
, LD.INVENTORY_ITEM_ID
, 1
, 0)
, 1) * (LD.QUANTITY / L.ORDERED_QUANTITY)*(NVL(L.ORDERED_QUANTITY
, 0) - NVL(L.CANCELLED_QUANTITY
, 0) - NVL(L.SHIPPED_QUANTITY
, 0))
, 0))) SHIP_BKLG_COST_T
, SUM(DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 0
, DECODE(CA.ACTION_ID
, 2
, (EDW_UTIL.GET_ITEM_COST(L.INVENTORY_ITEM_ID
, LD.WAREHOUSE_ID)) * DECODE(LD.INCLUDED_ITEM_FLAG
, 'Y'
, 0
, 1) * DECODE(L.ATO_FLAG
, 'Y'
, DECODE(L.INVENTORY_ITEM_ID
, LD.INVENTORY_ITEM_ID
, 1
, 0)
, 1) * (LD.QUANTITY / L.ORDERED_QUANTITY)*DECODE(SIGN(NVL(LD.SCHEDULE_DATE
, SYSDATE) - SYSDATE)
, -1
, (NVL(L.ORDERED_QUANTITY
, 0) - NVL(L.CANCELLED_QUANTITY
, 0) - NVL(L.SHIPPED_QUANTITY
, 0))
, 0)
, 0))) DLQT_BKLG_COST_T
, -- MEASURES IN GLOBAL CURRENCY SUM(EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( DECODE(H.ORDER_CATEGORY
, 'R'
, DECODE(CA.ACTION_ID
, 7
, ((NVL(L.SELLING_PRICE
, 0) - EDW_UTIL.GET_ITEM_COST(L.INVENTORY_ITEM_ID
, NVL(LD.WAREHOUSE_ID
, NVL(L.WAREHOUSE_ID
, H.WAREHOUSE_ID)))) * DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 1
, DECODE(LD.INCLUDED_ITEM_FLAG
, 'Y'
, 0
, 1)) * DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 1
, DECODE(L.ATO_FLAG
, 'Y'
, DECODE(L.INVENTORY_ITEM_ID
, LD.INVENTORY_ITEM_ID
, 1
, 0)
, 1)) * DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 1
, (LD.QUANTITY / L.ORDERED_QUANTITY)) * (NVL(L.ORDERED_QUANTITY
, 0) - NVL(L.CANCELLED_QUANTITY
, 0) - NVL(L.INVOICED_QUANTITY
, 0)))
, 0)
, 0)
, NULL
, H.CURRENCY_CODE
, EDW_DIM_FK.BUS_UNIT_ID(4
, NVL(LD.WAREHOUSE_ID
, NVL(L.WAREHOUSE_ID
, H.WAREHOUSE_ID)))
, H.CONVERSION_DATE
, H.CONVERSION_TYPE_CODE )) BILL_BKLG_MRG_G
, SUM(EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 0
, DECODE(CA.ACTION_ID
, 2
, ((NVL(L.SELLING_PRICE
, 0) - EDW_UTIL.GET_ITEM_COST(L.INVENTORY_ITEM_ID
, LD.WAREHOUSE_ID)) * DECODE(LD.INCLUDED_ITEM_FLAG
, 'Y'
, 0
, 1) * DECODE(L.ATO_FLAG
, 'Y'
, DECODE(L.INVENTORY_ITEM_ID
, LD.INVENTORY_ITEM_ID
, 1
, 0)
, 1) * (LD.QUANTITY / L.ORDERED_QUANTITY)*(NVL(L.ORDERED_QUANTITY
, 0) - NVL(L.CANCELLED_QUANTITY
, 0) - NVL(L.SHIPPED_QUANTITY
, 0)))
, 0))
, NULL
, H.CURRENCY_CODE
, EDW_DIM_FK.BUS_UNIT_ID(4
, LD.WAREHOUSE_ID)
, H.CONVERSION_DATE
, H.CONVERSION_TYPE_CODE )) SHIP_BKLG_MRG_G
, SUM(EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 0
, DECODE(CA.ACTION_ID
, 2
, ((NVL(L.SELLING_PRICE
, 0) - EDW_UTIL.GET_ITEM_COST(L.INVENTORY_ITEM_ID
, LD.WAREHOUSE_ID)) * DECODE(LD.INCLUDED_ITEM_FLAG
, 'Y'
, 0
, 1) * DECODE(L.ATO_FLAG
, 'Y'
, DECODE(L.INVENTORY_ITEM_ID
, LD.INVENTORY_ITEM_ID
, 1
, 0)
, 1) * (LD.QUANTITY / L.ORDERED_QUANTITY)*DECODE(SIGN(NVL(LD.SCHEDULE_DATE
, SYSDATE) - SYSDATE)
, -1
, (NVL(L.ORDERED_QUANTITY
, 0) - NVL(L.CANCELLED_QUANTITY
, 0) - NVL(L.SHIPPED_QUANTITY
, 0))
, 0))
, 0))
, NULL
, H.CURRENCY_CODE
, EDW_DIM_FK.BUS_UNIT_ID(4
, LD.WAREHOUSE_ID)
, H.CONVERSION_DATE
, H.CONVERSION_TYPE_CODE )) DLQT_BKLG_MRG_G
, SUM(EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( DECODE(H.ORDER_CATEGORY
, 'R'
, DECODE(CA.ACTION_ID
, 7
, ((NVL(L.SELLING_PRICE
, 0)) * DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 1
, DECODE(LD.INCLUDED_ITEM_FLAG
, 'Y'
, 0
, 1)) * DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 1
, DECODE(L.ATO_FLAG
, 'Y'
, DECODE(L.INVENTORY_ITEM_ID
, LD.INVENTORY_ITEM_ID
, 1
, 0)
, 1)) * DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 1
, (LD.QUANTITY / L.ORDERED_QUANTITY))*(NVL(L.ORDERED_QUANTITY
, 0) - NVL(L.CANCELLED_QUANTITY
, 0) - NVL(L.INVOICED_QUANTITY
, 0)))
, 0)
, 0)
, NULL
, H.CURRENCY_CODE
, EDW_DIM_FK.BUS_UNIT_ID(4
, NVL(LD.WAREHOUSE_ID
, NVL(L.WAREHOUSE_ID
, H.WAREHOUSE_ID)))
, H.CONVERSION_DATE
, H.CONVERSION_TYPE_CODE )) BILL_BKLG_REV_G
, SUM(EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 0
, DECODE(CA.ACTION_ID
, 2
, ((NVL(L.SELLING_PRICE
, 0)) * DECODE(LD.INCLUDED_ITEM_FLAG
, 'Y'
, 0
, 1) * DECODE(L.ATO_FLAG
, 'Y'
, DECODE(L.INVENTORY_ITEM_ID
, LD.INVENTORY_ITEM_ID
, 1
, 0)
, 1) * (LD.QUANTITY / L.ORDERED_QUANTITY)*(NVL(L.ORDERED_QUANTITY
, 0) - NVL(L.CANCELLED_QUANTITY
, 0) - NVL(L.SHIPPED_QUANTITY
, 0)))
, 0))
, NULL
, H.CURRENCY_CODE
, EDW_DIM_FK.BUS_UNIT_ID(4
, LD.WAREHOUSE_ID)
, H.CONVERSION_DATE
, H.CONVERSION_TYPE_CODE )) SHIP_BKLG_REV_G
, SUM(EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 0
, DECODE(CA.ACTION_ID
, 2
, ((NVL(L.SELLING_PRICE
, 0)) * DECODE(LD.INCLUDED_ITEM_FLAG
, 'Y'
, 0
, 1) * DECODE(L.ATO_FLAG
, 'Y'
, DECODE(L.INVENTORY_ITEM_ID
, LD.INVENTORY_ITEM_ID
, 1
, 0)
, 1) * (LD.QUANTITY / L.ORDERED_QUANTITY)*DECODE(SIGN(NVL(LD.SCHEDULE_DATE
, SYSDATE) - SYSDATE)
, -1
, (NVL(L.ORDERED_QUANTITY
, 0) - NVL(L.CANCELLED_QUANTITY
, 0) - NVL(L.SHIPPED_QUANTITY
, 0))
, 0))
, 0))
, NULL
, H.CURRENCY_CODE
, EDW_DIM_FK.BUS_UNIT_ID(4
, LD.WAREHOUSE_ID)
, H.CONVERSION_DATE
, H.CONVERSION_TYPE_CODE )) DLQT_BKLG_REV_G
, SUM(EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( DECODE(H.ORDER_CATEGORY
, 'R'
, DECODE(CA.ACTION_ID
, 7
, ((EDW_UTIL.GET_ITEM_COST(L.INVENTORY_ITEM_ID
, NVL(LD.WAREHOUSE_ID
, NVL(L.WAREHOUSE_ID
, H.WAREHOUSE_ID)))) * DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 1
, DECODE(LD.INCLUDED_ITEM_FLAG
, 'Y'
, 0
, 1)) * DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 1
, DECODE(L.ATO_FLAG
, 'Y'
, DECODE(L.INVENTORY_ITEM_ID
, LD.INVENTORY_ITEM_ID
, 1
, 0)
, 1)) * DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 1
, (LD.QUANTITY / L.ORDERED_QUANTITY))*(NVL(L.ORDERED_QUANTITY
, 0) - NVL(L.CANCELLED_QUANTITY
, 0) - NVL(L.INVOICED_QUANTITY
, 0)))
, 0)
, 0)
, NULL
, H.CURRENCY_CODE
, EDW_DIM_FK.BUS_UNIT_ID(4
, NVL(LD.WAREHOUSE_ID
, NVL(L.WAREHOUSE_ID
, H.WAREHOUSE_ID)))
, H.CONVERSION_DATE
, H.CONVERSION_TYPE_CODE )) BILL_BKLG_COST_G
, SUM(EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 0
, DECODE(CA.ACTION_ID
, 2
, ((EDW_UTIL.GET_ITEM_COST(L.INVENTORY_ITEM_ID
, LD.WAREHOUSE_ID)) * DECODE(LD.INCLUDED_ITEM_FLAG
, 'Y'
, 0
, 1) * DECODE(L.ATO_FLAG
, 'Y'
, DECODE(L.INVENTORY_ITEM_ID
, LD.INVENTORY_ITEM_ID
, 1
, 0)
, 1) * (LD.QUANTITY / L.ORDERED_QUANTITY)*(NVL(L.ORDERED_QUANTITY
, 0) - NVL(L.CANCELLED_QUANTITY
, 0) - NVL(L.SHIPPED_QUANTITY
, 0)))
, 0))
, NULL
, H.CURRENCY_CODE
, EDW_DIM_FK.BUS_UNIT_ID(4
, LD.WAREHOUSE_ID)
, H.CONVERSION_DATE
, H.CONVERSION_TYPE_CODE )) SHIP_BKLG_COST_G
, SUM(EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 0
, DECODE(CA.ACTION_ID
, 2
, ((EDW_UTIL.GET_ITEM_COST(L.INVENTORY_ITEM_ID
, LD.WAREHOUSE_ID)) * DECODE(LD.INCLUDED_ITEM_FLAG
, 'Y'
, 0
, 1) * DECODE(L.ATO_FLAG
, 'Y'
, DECODE(L.INVENTORY_ITEM_ID
, LD.INVENTORY_ITEM_ID
, 1
, 0)
, 1) * (LD.QUANTITY / L.ORDERED_QUANTITY)*DECODE(SIGN(NVL(LD.SCHEDULE_DATE
, SYSDATE) - SYSDATE)
, -1
, (NVL(L.ORDERED_QUANTITY
, 0) - NVL(L.CANCELLED_QUANTITY
, 0) - NVL(L.SHIPPED_QUANTITY
, 0))
, 0))
, 0))
, NULL
, H.CURRENCY_CODE
, EDW_DIM_FK.BUS_UNIT_ID(4
, LD.WAREHOUSE_ID)
, H.CONVERSION_DATE
, H.CONVERSION_TYPE_CODE )) DLQT_BKLG_COST_G
, -- ONLY RETURNS QUANTITY OF TOP MODEL ITEM; COMPONENTS QUANTITIES ARE ZEROS SUM(DECODE(H.ORDER_CATEGORY
, 'R'
, DECODE(CA.ACTION_ID
, 7
, DECODE(PL.INVENTORY_ITEM_ID
, L.INVENTORY_ITEM_ID
, (NVL(L.ORDERED_QUANTITY
, 0) - NVL(L.CANCELLED_QUANTITY
, 0) - NVL(L.INVOICED_QUANTITY
, 0)) * DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 1
, DECODE(LD.INCLUDED_ITEM_FLAG
, 'Y'
, 0
, 1)) * DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 1
, DECODE(L.ATO_FLAG
, 'Y'
, DECODE(L.INVENTORY_ITEM_ID
, LD.INVENTORY_ITEM_ID
, 1
, 0)
, 1)) * DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 1
, (LD.QUANTITY / L.ORDERED_QUANTITY)) * EDW_UOM_PKG.GET_UOM_CONVERSION_RATE(LD.UNIT_CODE
, L.INVENTORY_ITEM_ID
, NULL)
, 0)
, 0)
, 0)) QTY_BILL_BKLG_B
, SUM(DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 0
, DECODE(CA.ACTION_ID
, 2
, DECODE(PL.INVENTORY_ITEM_ID
, L.INVENTORY_ITEM_ID
, (NVL(L.ORDERED_QUANTITY
, 0) - NVL(L.CANCELLED_QUANTITY
, 0) - NVL(L.SHIPPED_QUANTITY
, 0)) * DECODE(LD.INCLUDED_ITEM_FLAG
, 'Y'
, 0
, 1) * DECODE(L.ATO_FLAG
, 'Y'
, DECODE(L.INVENTORY_ITEM_ID
, LD.INVENTORY_ITEM_ID
, 1
, 0)
, 1) * (LD.QUANTITY / L.ORDERED_QUANTITY) * EDW_UOM_PKG.GET_UOM_CONVERSION_RATE(LD.UNIT_CODE
, L.INVENTORY_ITEM_ID
, NULL)
, 0)
, 0))) QTY_SHIP_BKLG_B
, SUM(DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 0
, DECODE(CA.ACTION_ID
, 2
, DECODE(PL.INVENTORY_ITEM_ID
, L.INVENTORY_ITEM_ID
, DECODE(SIGN(NVL(LD.SCHEDULE_DATE
, SYSDATE) - SYSDATE)
, -1
, (NVL(L.ORDERED_QUANTITY
, 0) - NVL(L.CANCELLED_QUANTITY
, 0) - NVL(L.SHIPPED_QUANTITY
, 0)) * DECODE(LD.INCLUDED_ITEM_FLAG
, 'Y'
, 0
, 1) * DECODE(L.ATO_FLAG
, 'Y'
, DECODE(L.INVENTORY_ITEM_ID
, LD.INVENTORY_ITEM_ID
, 1
, 0)
, 1) * (LD.QUANTITY / L.ORDERED_QUANTITY) * EDW_UOM_PKG.GET_UOM_CONVERSION_RATE(LD.UNIT_CODE
, L.INVENTORY_ITEM_ID
, NULL)
, 0)
, 0)
, 0))) QTY_DLQT_BKLG_B
, SUM(DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 0
, DECODE(H.ORDER_CATEGORY
, 'R'
, DECODE(CA.ACTION_ID
, 2
, DECODE( PL.INVENTORY_ITEM_ID
, L.INVENTORY_ITEM_ID
, DECODE(SIGN(NVL(L.SHIPPED_QUANTITY
, 0) - NVL(L.INVOICED_QUANTITY
, 0))
, 1
, (NVL(L.SHIPPED_QUANTITY
, 0) - NVL(L.INVOICED_QUANTITY
, 0))
, 0) * DECODE(LD.INCLUDED_ITEM_FLAG
, 'Y'
, 0
, 1) * DECODE(L.ATO_FLAG
, 'Y'
, DECODE(L.INVENTORY_ITEM_ID
, LD.INVENTORY_ITEM_ID
, 1
, 0)
, 1) * (LD.QUANTITY / L.ORDERED_QUANTITY) * EDW_UOM_PKG.GET_UOM_CONVERSION_RATE(LD.UNIT_CODE
, L.INVENTORY_ITEM_ID
, NULL)
, 0)
, 0)
, 0))) QTY_UNBILL_SHIP_B
, -- ZERO IF NEGATIVE FOR UNBILLED SHIPMENT IN TXN OR GLOBAL CURRENCY SUM(DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 0
, DECODE(H.ORDER_CATEGORY
, 'R'
, DECODE(CA.ACTION_ID
, 2
, (NVL(L.SELLING_PRICE
, 0)) * DECODE(LD.INCLUDED_ITEM_FLAG
, 'Y'
, 0
, 1) * DECODE(L.ATO_FLAG
, 'Y'
, DECODE(L.INVENTORY_ITEM_ID
, LD.INVENTORY_ITEM_ID
, 1
, 0)
, 1) * (LD.QUANTITY / L.ORDERED_QUANTITY)*DECODE(SIGN(NVL(L.SHIPPED_QUANTITY
, 0) - NVL(L.INVOICED_QUANTITY
, 0))
, 1
, (NVL(L.SHIPPED_QUANTITY
, 0) - NVL(L.INVOICED_QUANTITY
, 0))
, 0)
, 0)
, 0))) UNBILL_SHIP_REV_T
, SUM(EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 0
, DECODE(H.ORDER_CATEGORY
, 'R'
, DECODE(CA.ACTION_ID
, 2
, ((NVL(L.SELLING_PRICE
, 0)) * DECODE(LD.INCLUDED_ITEM_FLAG
, 'Y'
, 0
, 1) * DECODE(L.ATO_FLAG
, 'Y'
, DECODE(L.INVENTORY_ITEM_ID
, LD.INVENTORY_ITEM_ID
, 1
, 0)
, 1) * (LD.QUANTITY / L.ORDERED_QUANTITY)*DECODE(SIGN(NVL(L.SHIPPED_QUANTITY
, 0) - NVL(L.INVOICED_QUANTITY
, 0))
, 1
, (NVL(L.SHIPPED_QUANTITY
, 0) - NVL(L.INVOICED_QUANTITY
, 0))
, 0))
, 0)
, 0))
, NULL
, H.CURRENCY_CODE
, EDW_DIM_FK.BUS_UNIT_ID(4
, LD.WAREHOUSE_ID)
, H.CONVERSION_DATE
, H.CONVERSION_TYPE_CODE )) UNBILL_SHIP_REV_G
, SUM(DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 0
, DECODE(CA.ACTION_ID
, 2
, (EDW_UTIL.GET_ITEM_COST(L.INVENTORY_ITEM_ID
, LD.WAREHOUSE_ID)) * DECODE(LD.INCLUDED_ITEM_FLAG
, 'Y'
, 0
, 1) * DECODE(L.ATO_FLAG
, 'Y'
, DECODE(L.INVENTORY_ITEM_ID
, LD.INVENTORY_ITEM_ID
, 1
, 0)
, 1) * (LD.QUANTITY / L.ORDERED_QUANTITY)*DECODE(SIGN(NVL(L.SHIPPED_QUANTITY
, 0) - NVL(L.INVOICED_QUANTITY
, 0))
, 1
, (NVL(L.SHIPPED_QUANTITY
, 0) - NVL(L.INVOICED_QUANTITY
, 0))
, 0)
, 0))) UNBIL_SHIP_COST_T
, SUM(EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 0
, DECODE(CA.ACTION_ID
, 2
, ((EDW_UTIL.GET_ITEM_COST(L.INVENTORY_ITEM_ID
, LD.WAREHOUSE_ID)) * DECODE(LD.INCLUDED_ITEM_FLAG
, 'Y'
, 0
, 1) * DECODE(L.ATO_FLAG
, 'Y'
, DECODE(L.INVENTORY_ITEM_ID
, LD.INVENTORY_ITEM_ID
, 1
, 0)
, 1) * (LD.QUANTITY / L.ORDERED_QUANTITY)*DECODE(SIGN(NVL(L.SHIPPED_QUANTITY
, 0) - NVL(L.INVOICED_QUANTITY
, 0))
, 1
, (NVL(L.SHIPPED_QUANTITY
, 0) - NVL(L.INVOICED_QUANTITY
, 0))
, 0))
, 0))
, NULL
, H.CURRENCY_CODE
, EDW_DIM_FK.BUS_UNIT_ID(4
, LD.WAREHOUSE_ID)
, H.CONVERSION_DATE
, H.CONVERSION_TYPE_CODE )) UNBIL_SHIP_COST_G
, SUM(DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 0
, DECODE(H.ORDER_CATEGORY
, 'R'
, DECODE(CA.ACTION_ID
, 2
, (NVL(L.SELLING_PRICE
, 0) - EDW_UTIL.GET_ITEM_COST(L.INVENTORY_ITEM_ID
, LD.WAREHOUSE_ID)) * DECODE(LD.INCLUDED_ITEM_FLAG
, 'Y'
, 0
, 1) * DECODE(L.ATO_FLAG
, 'Y'
, DECODE(L.INVENTORY_ITEM_ID
, LD.INVENTORY_ITEM_ID
, 1
, 0)
, 1) * (LD.QUANTITY / L.ORDERED_QUANTITY)*DECODE(SIGN(NVL(L.SHIPPED_QUANTITY
, 0) - NVL(L.INVOICED_QUANTITY
, 0))
, 1
, (NVL(L.SHIPPED_QUANTITY
, 0) - NVL(L.INVOICED_QUANTITY
, 0))
, 0)
, 0)
, 0))) UNBILL_SHIP_MRG_T
, SUM(EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( DECODE(L.ITEM_TYPE_CODE
, 'SERVICE'
, 0
, DECODE(H.ORDER_CATEGORY
, 'R'
, DECODE(CA.ACTION_ID
, 2
, ((NVL(L.SELLING_PRICE
, 0) - EDW_UTIL.GET_ITEM_COST(L.INVENTORY_ITEM_ID
, LD.WAREHOUSE_ID)) * DECODE(LD.INCLUDED_ITEM_FLAG
, 'Y'
, 0
, 1) * DECODE(L.ATO_FLAG
, 'Y'
, DECODE(L.INVENTORY_ITEM_ID
, LD.INVENTORY_ITEM_ID
, 1
, 0)
, 1) * (LD.QUANTITY / L.ORDERED_QUANTITY)*DECODE(SIGN(NVL(L.SHIPPED_QUANTITY
, 0) - NVL(L.INVOICED_QUANTITY
, 0))
, 1
, (NVL(L.SHIPPED_QUANTITY
, 0) - NVL(L.INVOICED_QUANTITY
, 0))
, 0))
, 0)
, 0))
, NULL
, H.CURRENCY_CODE
, EDW_DIM_FK.BUS_UNIT_ID(4
, LD.WAREHOUSE_ID)
, H.CONVERSION_DATE
, H.CONVERSION_TYPE_CODE )) UNBILL_SHIP_MRG_G
FROM RA_SALESREPS_ALL SALESREP
, EDW_LOCAL_INSTANCE INST
, RA_SITE_USES_ALL SHIP_TO_SITE
, RA_TERRITORIES RT
, SO_ORDER_TYPES_ALL OT
, SO_CYCLE_ACTIONS CA
, SO_LINES_ALL PL
, SO_HEADERS_ALL H
, SO_LINES_ALL L
, SO_LINE_DETAILS LD
WHERE L.HEADER_ID = H.HEADER_ID
AND LD.LINE_ID = L.LINE_ID (+) -- SERVICE ITEMS HAVE NO LINE DETAILS
AND H.HEADER_ID = PL.HEADER_ID
AND PL.LINE_ID = NVL(L.PARENT_LINE_ID
, L.LINE_ID) -- GET TOP PARENT ITEM
AND SALESREP.SALESREP_ID (+) = H.SALESREP_ID
AND SALESREP.ORG_ID (+) = H.ORG_ID
AND SHIP_TO_SITE.SITE_USE_ID (+) = L.SHIP_TO_SITE_USE_ID
AND SHIP_TO_SITE.TERRITORY_ID = RT.TERRITORY_ID (+)
AND H.S1 = 1 -- ORDER HAS TO BE BOOKED
AND H.OPEN_FLAG = 'Y' -- ORDER MUST BE OPEN
AND L.LINE_TYPE_CODE IN ('REGULAR'
, 'DETAIL') -- CONSIDER LINES WITH OR WITHOUT SCHEDULES
AND H.ORDER_CATEGORY IN ('R'
, 'P') -- CONSIDER BOTH REGULAR ORDERS
AND INTERNAL -- SALES ORDERS. THE LATTER IS CONSIDERED -- FOR SHIPPING
AND DELINQUENT BACKLOG ONLY
AND H.ORDER_TYPE_ID = OT.ORDER_TYPE_ID
AND OT.CYCLE_ID = CA.CYCLE_ID
AND CA.ACTION_ID IN (2
, 7) -- REQUIRES RECEIVABLE OR PICK RELEASE CYCLES GROUP BY SYSDATE || '-' || L.ORG_ID || '-' || H.CUSTOMER_ID || '-' || H.SALES_CHANNEL_CODE || '-' || H.SALESREP_ID || '-' || L.SHIP_TO_SITE_USE_ID || '-' || H.INVOICE_TO_SITE_USE_ID || '-' || L.PROJECT_ID || '-' || L.TASK_ID || '-' || NVL(LD.WAREHOUSE_ID
, NVL(L.WAREHOUSE_ID
, H.WAREHOUSE_ID))|| '-' || NVL(LD.DEMAND_CLASS_CODE
, NVL(L.DEMAND_CLASS_CODE
, H.DEMAND_CLASS_CODE)) || '-' || H.CURRENCY_CODE || '-' || PL.INVENTORY_ITEM_ID || '-' || L.INVENTORY_ITEM_ID || '-' || RT.NAME || '-' || INST.INSTANCE_CODE
, SUBSTR(BIS_EDW_INSTANCE.GET_CODE
, 0
, 40)
, EDW_DIM_FK.ITEM_ORG(PL.INVENTORY_ITEM_ID
, NVL(LD.WAREHOUSE_ID
, NVL(L.WAREHOUSE_ID
, H.WAREHOUSE_ID)))
, EDW_DIM_FK.ITEM_ORG(L.INVENTORY_ITEM_ID
, NVL(LD.WAREHOUSE_ID
, NVL(L.WAREHOUSE_ID
, H.WAREHOUSE_ID)))
, EDW_DIM_FK.OPERATING_UNIT(L.ORG_ID)
, EDW_DIM_FK.TRADING_PARTNER(H.CUSTOMER_ID)
, NVL(H.SALES_CHANNEL_CODE
, 'NA_EDW')|| '-SALES_CHANNEL-SO_LOOKUPS'
, EDW_DIM_FK.EMPLOYEE(3
, SALESREP.SALESREP_ID)
, EDW_DIM_FK.PROJECT(L.PROJECT_ID)
, EDW_DIM_FK.TASK(L.TASK_ID)
, EDW_DIM_FK.BUS_UNIT(1
, NVL(LD.WAREHOUSE_ID
, NVL(L.WAREHOUSE_ID
, H.WAREHOUSE_ID)))
, EDW_DIM_FK.BUS_UNIT(2
, H.INVOICE_TO_SITE_USE_ID)
, EDW_DIM_FK.BUS_UNIT(2
, L.SHIP_TO_SITE_USE_ID)
, NVL(LD.DEMAND_CLASS_CODE
, NVL(L.DEMAND_CLASS_CODE
, NVL(H.DEMAND_CLASS_CODE
, 'NA_EDW')))
, H.CURRENCY_CODE
, RT.NAME
, EDW_UOM_PKG.GET_BASE_UOM_CODE(LD.UNIT_CODE
, NULL)

Columns

Name
BACKLOG_PK
TERRITORY_FK
DATE_BALANCE_FK
INSTANCE_FK
TOP_MODEL_ITEM_FK
ITEM_ORG_FK
BASE_UOM_FK
OPERATING_UNIT_FK
CUSTOMER_FK
SALES_CHANNEL_FK
PRIM_SALES_REP_FK
PROJECT_FK
TASK_FK
INV_ORG_FK
BILL_TO_SITE_FK
SHIP_TO_SITE_FK
DEMAND_CLASS_FK
TRX_CURRENCY_FK
BILL_BKLG_MRG_T
SHIP_BKLG_MRG_T
DLQT_BKLG_MRG_T
BILL_BKLG_REV_T
SHIP_BKLG_REV_T
DLQT_BKLG_REV_T
BILL_BKLG_COST_T
SHIP_BKLG_COST_T
DLQT_BKLG_COST_T
BILL_BKLG_MRG_G
SHIP_BKLG_MRG_G
DLQT_BKLG_MRG_G
BILL_BKLG_REV_G
SHIP_BKLG_REV_G
DLQT_BKLG_REV_G
BILL_BKLG_COST_G
SHIP_BKLG_COST_G
DLQT_BKLG_COST_G
QTY_BILL_BKLG_B
QTY_SHIP_BKLG_B
QTY_DLQT_BKLG_B
QTY_UNBILL_SHIP_B
UNBILL_SHIP_REV_T
UNBILL_SHIP_REV_G
UNBIL_SHIP_COST_T
UNBIL_SHIP_COST_G
UNBILL_SHIP_MRG_T
UNBILL_SHIP_MRG_G