View: RLM_OE_ORDER_LINES_V

Product:	RLM - Release Management
Description:
Implementation/DBA Data:	APPS.RLM_OE_ORDER_LINES_V

View Text

SELECT SHIP_FROM_ORG_ID
, SHIP_TO_ORG_ID
, CUSTOMER_ITEM_ID
, INVENTORY_ITEM_ID
, EVENT_DATE
, DECODE(AUTHORIZED_TO_SHIP_FLAG
, 'Y'
, 1
, 'N'
, 2
, 1) AUTHORIZED_TO_SHIP_FLAG
, DEMAND_BUCKET_TYPE_CODE
, UOM_CODE
, SUM( DECODE(FLG
, 'O'
, QTY
, 0) ) AS ORDERED_QTY
, SUM( DECODE(FLG
, 'S'
, QTY
, 0) ) AS SHIPPED_QTY
, SUM( DECODE(FLG
, 'O'
, OPEN_QTY
, 0) ) AS OPEN_QTY
, SUM( TOLERANCE_QTY ) AS TOLERANCE_QTY
FROM ( SELECT SHIP_FROM_ORG_ID
, SHIP_TO_ORG_ID
, ORDERED_ITEM_ID CUSTOMER_ITEM_ID
, INVENTORY_ITEM_ID
, TRUNC(SCHEDULE_SHIP_DATE) EVENT_DATE
, AUTHORIZED_TO_SHIP_FLAG
, DEMAND_BUCKET_TYPE_CODE
, ORDER_QUANTITY_UOM UOM_CODE
, SUM( NVL(ORDERED_QUANTITY
, 0) ) QTY
, SUM( DECODE ( SHIPPED_QUANTITY
, NULL
, NVL(ORDERED_QUANTITY
, 0)
, 0 ) ) OPEN_QTY
, SUM( DECODE ( SIGN ( TRUNC(SCHEDULE_SHIP_DATE) - TRUNC(ACTUAL_SHIPMENT_DATE) )
, NULL
, 0
, -1
, 0
, 0
, 0
, 1
, NVL(ORDERED_QUANTITY
, 0) - DECODE( ORDER_QUANTITY_UOM
, NVL(SHIPPING_QUANTITY_UOM
, ORDER_QUANTITY_UOM)
, NVL(SHIPPED_QUANTITY
, 0)
, INV_CONVERT.INV_UM_CONVERT ( INVENTORY_ITEM_ID
, 20
, NVL(SHIPPED_QUANTITY
, 0)
, SHIPPING_QUANTITY_UOM
, ORDER_QUANTITY_UOM
, NULL
, NULL ) ) ) ) TOLERANCE_QTY
, 'O' FLG FROM OE_ORDER_LINES_ALL WHERE ITEM_IDENTIFIER_TYPE = 'CUST'
AND SHIPPABLE_FLAG ='Y'
AND LINE_CATEGORY_CODE <> 'RETURN' GROUP BY SHIP_FROM_ORG_ID
, SHIP_TO_ORG_ID
, ORDERED_ITEM_ID
, INVENTORY_ITEM_ID
, TRUNC(SCHEDULE_SHIP_DATE)
, AUTHORIZED_TO_SHIP_FLAG
, DEMAND_BUCKET_TYPE_CODE
, ORDER_QUANTITY_UOM UNION ALL SELECT SHIP_FROM_ORG_ID
, SHIP_TO_ORG_ID
, ORDERED_ITEM_ID CUSTOMER_ITEM_ID
, INVENTORY_ITEM_ID
, TRUNC(ACTUAL_SHIPMENT_DATE) EVENT_DATE
, AUTHORIZED_TO_SHIP_FLAG
, DEMAND_BUCKET_TYPE_CODE
, ORDER_QUANTITY_UOM UOM_CODE
, SUM( DECODE( ORDER_QUANTITY_UOM
, NVL(SHIPPING_QUANTITY_UOM
, ORDER_QUANTITY_UOM)
, NVL(SHIPPED_QUANTITY
, 0)
, INV_CONVERT.INV_UM_CONVERT ( INVENTORY_ITEM_ID
, 20
, NVL(SHIPPED_QUANTITY
, 0)
, SHIPPING_QUANTITY_UOM
, ORDER_QUANTITY_UOM
, NULL
, NULL ) ) ) QTY
, 0 OPEN_QTY
, SUM( DECODE ( SIGN ( TRUNC(SCHEDULE_SHIP_DATE) - TRUNC(ACTUAL_SHIPMENT_DATE) )
, NULL
, 0
, 1
, 0
, NVL(ORDERED_QUANTITY
, 0)- DECODE( ORDER_QUANTITY_UOM
, NVL(SHIPPING_QUANTITY_UOM
, ORDER_QUANTITY_UOM)
, NVL(SHIPPED_QUANTITY
, 0)
, INV_CONVERT.INV_UM_CONVERT ( INVENTORY_ITEM_ID
, 20
, NVL(SHIPPED_QUANTITY
, 0)
, SHIPPING_QUANTITY_UOM
, ORDER_QUANTITY_UOM
, NULL
, NULL ) ) ) ) TOLERANCE_QTY
, 'S' FLG FROM OE_ORDER_LINES_ALL WHERE ACTUAL_SHIPMENT_DATE IS NOT NULL AND ITEM_IDENTIFIER_TYPE = 'CUST'
AND SHIPPABLE_FLAG ='Y'
AND LINE_CATEGORY_CODE <> 'RETURN' GROUP BY SHIP_FROM_ORG_ID
, SHIP_TO_ORG_ID
, ORDERED_ITEM_ID
, INVENTORY_ITEM_ID
, TRUNC(ACTUAL_SHIPMENT_DATE)
, AUTHORIZED_TO_SHIP_FLAG
, DEMAND_BUCKET_TYPE_CODE
, ORDER_QUANTITY_UOM ) GROUP BY SHIP_FROM_ORG_ID
, SHIP_TO_ORG_ID
, CUSTOMER_ITEM_ID
, INVENTORY_ITEM_ID
, EVENT_DATE
, AUTHORIZED_TO_SHIP_FLAG
, DEMAND_BUCKET_TYPE_CODE
, UOM_CODE

Columns