View: IC_TRAN_PND_OM_VW1

Product:	GMF - Process Manufacturing Financials
Description:	OBSOLETE: Pending inventory transactions for OM view
Implementation/DBA Data:	APPS.IC_TRAN_PND_OM_VW1

View Text

SELECT T.DOC_TYPE
, T.DOC_ID
, T.LINE_ID
, T.ITEM_ID
, T.ORGN_CODE
, T.WHSE_CODE
, T.GL_POSTED_IND
, D.NAME
, D.DELIVERY_ID
, WD.SOURCE_LINE_NUMBER LINE_NO
, SUM(WD.SHIPPED_QUANTITY) TRANS_QTY_USAGE
, OL.SHIPPING_QUANTITY_UOM TRANS_QTY_USAGE_UM
, OL.SHIP_FROM_ORG_ID
FROM IC_TRAN_PND T
, WSH_NEW_DELIVERIES D
, WSH_DELIVERY_DETAILS WD
, WSH_DELIVERY_ASSIGNMENTS WA
, OE_ORDER_LINES_ALL OL
WHERE T.DOC_TYPE = 'OMSO'
AND T.COMPLETED_IND = 1
AND T.DELETE_MARK = 0
AND T.LINE_DETAIL_ID <> -999
AND D.DELIVERY_ID = WA.DELIVERY_ID
AND WA.DELIVERY_DETAIL_ID = WD.DELIVERY_DETAIL_ID
AND T.LINE_ID = WD.SOURCE_LINE_ID
AND WD.SOURCE_CODE = 'OE'
AND WD.OE_INTERFACED_FLAG = 'Y'
AND WD.SOURCE_LINE_ID = OL.LINE_ID
AND WD.DELIVERY_DETAIL_ID = T.LINE_DETAIL_ID GROUP BY T.DOC_TYPE
, T.DOC_ID
, T.LINE_ID
, T.ITEM_ID
, T.ORGN_CODE
, T.WHSE_CODE
, T.GL_POSTED_IND
, D.NAME
, D.DELIVERY_ID
, WD.SOURCE_LINE_NUMBER
, OL.SHIPPING_QUANTITY_UOM
, OL.SHIP_FROM_ORG_ID UNION ALL SELECT T.DOC_TYPE
, T.DOC_ID
, T.LINE_ID
, T.ITEM_ID
, '(BILL-ONLY) ' || T.ORGN_CODE ORGN_CODE
, T.WHSE_CODE
, T.GL_POSTED_IND
, TO_CHAR(OH.ORDER_NUMBER) NAME
, OH.HEADER_ID DELIVERY_ID
, TO_CHAR(OL.LINE_NUMBER) || '.' || TO_CHAR(OL.SHIPMENT_NUMBER) LINE_NO
, AVG(OL.ORDERED_QUANTITY) TRANS_QTY_USAGE
, OL.ORDER_QUANTITY_UOM TRANS_QTY_USAGE_UM
, OL.SHIP_FROM_ORG_ID
FROM IC_TRAN_PND T
, OE_ORDER_HEADERS_ALL OH
, OE_ORDER_LINES_ALL OL
WHERE T.DOC_TYPE = 'OMSO'
AND T.COMPLETED_IND = 1
AND T.DELETE_MARK = 0
AND T.LINE_ID = OL.LINE_ID
AND T.LINE_DETAIL_ID = -999
AND OH.HEADER_ID = OL.HEADER_ID GROUP BY T.DOC_TYPE
, T.DOC_ID
, T.LINE_ID
, T.ITEM_ID
, T.ORGN_CODE
, T.WHSE_CODE
, T.GL_POSTED_IND
, OH.ORDER_NUMBER
, OH.HEADER_ID
, TO_CHAR(OL.LINE_NUMBER) || '.' || TO_CHAR(OL.SHIPMENT_NUMBER)
, OL.ORDER_QUANTITY_UOM
, OL.SHIP_FROM_ORG_ID UNION ALL SELECT T.DOC_TYPE
, T.DOC_ID
, T.LINE_ID
, T.ITEM_ID
, '(DROP-SHIP) ' || T.ORGN_CODE ORGN_CODE
, T.WHSE_CODE
, T.GL_POSTED_IND
, TO_CHAR(OH.ORDER_NUMBER) NAME
, OH.HEADER_ID DELIVERY_ID
, TO_CHAR(OL.LINE_NUMBER) || '.' || TO_CHAR(OL.SHIPMENT_NUMBER) LINE_NO
, AVG(OL.ORDERED_QUANTITY) TRANS_QTY_USAGE
, OL.ORDER_QUANTITY_UOM TRANS_QTY_USAGE_UM
, OL.SHIP_FROM_ORG_ID
FROM IC_TRAN_PND T
, OE_ORDER_HEADERS_ALL OH
, OE_ORDER_LINES_ALL OL
WHERE T.DOC_TYPE = 'OMSO'
AND T.COMPLETED_IND = 1
AND T.DELETE_MARK = 0
AND T.LINE_ID = OL.LINE_ID
AND T.LINE_DETAIL_ID IS NULL
AND OH.HEADER_ID = OL.HEADER_ID
AND EXISTS (SELECT 1 FROM OE_DROP_SHIP_SOURCES S WHERE OL.LINE_ID = S.LINE_ID
AND OL.HEADER_ID = S.HEADER_ID ) GROUP BY T.DOC_TYPE
, T.DOC_ID
, T.LINE_ID
, T.ITEM_ID
, T.ORGN_CODE
, T.WHSE_CODE
, T.GL_POSTED_IND
, OH.ORDER_NUMBER
, OH.HEADER_ID
, TO_CHAR(OL.LINE_NUMBER) || '.' || TO_CHAR(OL.SHIPMENT_NUMBER)
, OL.ORDER_QUANTITY_UOM
, OL.SHIP_FROM_ORG_ID

Columns