VIEW: APPS.RLM_OE_ORDER_LINES_V

Source

View Text - Preformatted

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, ORG_ID 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, ORG_ID 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, org_id 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, ORG_ID 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, org_id ) 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, org_id ORDER 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

View Text - HTML Formatted

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
, ORG_ID
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
, ORG_ID
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
, ORG_ID 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
, ORG_ID
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
, ORG_ID ) 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
, ORG_ID ORDER 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