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.IC_TRAN_PND_OM_VW1

Source

View Text - Preformatted


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

View Text - HTML Formatted

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