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

Source

View Text - Preformatted


SELECT gen.product_item_id, gen.product_lot_id, gen.ingred_item_id, gen.ingred_lot_id, event.whse_code, event.location, event.trans_date, ABS(SUM(event.trans_qty)), ABS(SUM(event.trans_qty2)), ordr.orgn_code, ordr.order_no, cust.cust_no, cust.cust_name, i_item.item_no, i_item.item_desc1, i_item.item_um, i_item.item_um2, i_lot.lot_no, i_lot.sublot_no, p_item.item_no, p_item.item_desc1, p_item.item_um , p_item.item_um2 , p_item.inv_class, p_item.planning_class, p_item.sales_class, p_lot.lot_no , p_lot.sublot_no, event.completed_ind FROM pmi_lot_genealogy gen, ic_tran_pnd event, ic_item_mst i_item, ic_lots_mst i_lot, ic_item_mst p_item, ic_lots_mst p_lot, op_ordr_hdr ordr, op_cust_mst cust WHERE event.item_id = gen.product_item_id and event.lot_id = gen.product_lot_id and event.doc_type = 'OPSO' and i_lot.item_id = gen.ingred_item_id and i_lot.lot_id = gen.ingred_lot_id and i_item.item_id = i_lot.item_id and p_item.item_id = gen.product_item_id and p_lot.item_id = gen.product_item_id and p_lot.lot_id = gen.product_lot_id and ordr.order_id = event.doc_id and cust.cust_id = ordr.shipcust_id GROUP BY gen.product_item_id, gen.product_lot_id, gen.ingred_item_id, gen.ingred_lot_id, event.whse_code, event.location, event.trans_date, ordr.orgn_code, ordr.order_no, cust.cust_no, cust.cust_name, i_item.item_no, i_item.item_desc1, i_item.item_um, i_item.item_um2, i_lot.lot_no, i_lot.sublot_no, p_item.item_no, p_item.item_desc1, p_item.item_um , p_item.item_um2 , p_item.inv_class, p_item.planning_class, p_item.sales_class, p_lot.lot_no , p_lot.sublot_no, event.completed_ind HAVING SUM(event.trans_qty) <> 0 UNION ALL SELECT gen.product_item_id, gen.product_lot_id, gen.ingred_item_id, gen.ingred_lot_id, event.whse_code, event.location, event.trans_date, ABS(SUM(event.trans_qty)), ABS(SUM(event.trans_qty2)), Org.NAME, to_char(ordr.order_number), cust.PARTY_NUMBER, cust.PARTY_NAME, i_item.item_no, i_item.item_desc1, i_item.item_um, i_item.item_um2, i_lot.lot_no, i_lot.sublot_no, p_item.item_no, p_item.item_desc1, p_item.item_um , p_item.item_um2 , p_item.inv_class, p_item.planning_class, p_item.sales_class, p_lot.lot_no , p_lot.sublot_no, event.completed_ind FROM pmi_lot_genealogy gen, ic_tran_pnd event, ic_item_mst i_item, ic_lots_mst i_lot, ic_item_mst p_item, ic_lots_mst p_lot, oe_order_headers_all ordr, oe_order_lines_all lines, pmi_hz_party_v cust, HR_ALL_ORGANIZATION_UNITS_VL Org WHERE event.item_id = gen.product_item_id and event.lot_id = gen.product_lot_id and event.doc_type = 'OMSO' and i_lot.item_id = gen.ingred_item_id and i_lot.lot_id = gen.ingred_lot_id and i_item.item_id = i_lot.item_id and p_item.item_id = gen.product_item_id and p_lot.item_id = gen.product_item_id and p_lot.lot_id = gen.product_lot_id and lines.line_id = event.line_id and ordr.header_id = lines.header_id and ordr.org_id = org.organization_id and cust.SITE_USE_ID = lines.ship_to_org_id GROUP BY gen.product_item_id, gen.product_lot_id, gen.ingred_item_id, gen.ingred_lot_id, event.whse_code, event.location, event.trans_date, Org.NAME, ordr.order_number, cust.PARTY_NUMBER, cust.PARTY_NAME, i_item.item_no, i_item.item_desc1, i_item.item_um, i_item.item_um2, i_lot.lot_no, i_lot.sublot_no, p_item.item_no, p_item.item_desc1, p_item.item_um , p_item.item_um2 , p_item.inv_class, p_item.planning_class, p_item.sales_class, p_lot.lot_no , p_lot.sublot_no, event.completed_ind HAVING SUM(event.trans_qty) <> 0 with READ ONLY

View Text - HTML Formatted

SELECT GEN.PRODUCT_ITEM_ID
, GEN.PRODUCT_LOT_ID
, GEN.INGRED_ITEM_ID
, GEN.INGRED_LOT_ID
, EVENT.WHSE_CODE
, EVENT.LOCATION
, EVENT.TRANS_DATE
, ABS(SUM(EVENT.TRANS_QTY))
, ABS(SUM(EVENT.TRANS_QTY2))
, ORDR.ORGN_CODE
, ORDR.ORDER_NO
, CUST.CUST_NO
, CUST.CUST_NAME
, I_ITEM.ITEM_NO
, I_ITEM.ITEM_DESC1
, I_ITEM.ITEM_UM
, I_ITEM.ITEM_UM2
, I_LOT.LOT_NO
, I_LOT.SUBLOT_NO
, P_ITEM.ITEM_NO
, P_ITEM.ITEM_DESC1
, P_ITEM.ITEM_UM
, P_ITEM.ITEM_UM2
, P_ITEM.INV_CLASS
, P_ITEM.PLANNING_CLASS
, P_ITEM.SALES_CLASS
, P_LOT.LOT_NO
, P_LOT.SUBLOT_NO
, EVENT.COMPLETED_IND
FROM PMI_LOT_GENEALOGY GEN
, IC_TRAN_PND EVENT
, IC_ITEM_MST I_ITEM
, IC_LOTS_MST I_LOT
, IC_ITEM_MST P_ITEM
, IC_LOTS_MST P_LOT
, OP_ORDR_HDR ORDR
, OP_CUST_MST CUST
WHERE EVENT.ITEM_ID = GEN.PRODUCT_ITEM_ID
AND EVENT.LOT_ID = GEN.PRODUCT_LOT_ID
AND EVENT.DOC_TYPE = 'OPSO'
AND I_LOT.ITEM_ID = GEN.INGRED_ITEM_ID
AND I_LOT.LOT_ID = GEN.INGRED_LOT_ID
AND I_ITEM.ITEM_ID = I_LOT.ITEM_ID
AND P_ITEM.ITEM_ID = GEN.PRODUCT_ITEM_ID
AND P_LOT.ITEM_ID = GEN.PRODUCT_ITEM_ID
AND P_LOT.LOT_ID = GEN.PRODUCT_LOT_ID
AND ORDR.ORDER_ID = EVENT.DOC_ID
AND CUST.CUST_ID = ORDR.SHIPCUST_ID GROUP BY GEN.PRODUCT_ITEM_ID
, GEN.PRODUCT_LOT_ID
, GEN.INGRED_ITEM_ID
, GEN.INGRED_LOT_ID
, EVENT.WHSE_CODE
, EVENT.LOCATION
, EVENT.TRANS_DATE
, ORDR.ORGN_CODE
, ORDR.ORDER_NO
, CUST.CUST_NO
, CUST.CUST_NAME
, I_ITEM.ITEM_NO
, I_ITEM.ITEM_DESC1
, I_ITEM.ITEM_UM
, I_ITEM.ITEM_UM2
, I_LOT.LOT_NO
, I_LOT.SUBLOT_NO
, P_ITEM.ITEM_NO
, P_ITEM.ITEM_DESC1
, P_ITEM.ITEM_UM
, P_ITEM.ITEM_UM2
, P_ITEM.INV_CLASS
, P_ITEM.PLANNING_CLASS
, P_ITEM.SALES_CLASS
, P_LOT.LOT_NO
, P_LOT.SUBLOT_NO
, EVENT.COMPLETED_IND HAVING SUM(EVENT.TRANS_QTY) <> 0 UNION ALL SELECT GEN.PRODUCT_ITEM_ID
, GEN.PRODUCT_LOT_ID
, GEN.INGRED_ITEM_ID
, GEN.INGRED_LOT_ID
, EVENT.WHSE_CODE
, EVENT.LOCATION
, EVENT.TRANS_DATE
, ABS(SUM(EVENT.TRANS_QTY))
, ABS(SUM(EVENT.TRANS_QTY2))
, ORG.NAME
, TO_CHAR(ORDR.ORDER_NUMBER)
, CUST.PARTY_NUMBER
, CUST.PARTY_NAME
, I_ITEM.ITEM_NO
, I_ITEM.ITEM_DESC1
, I_ITEM.ITEM_UM
, I_ITEM.ITEM_UM2
, I_LOT.LOT_NO
, I_LOT.SUBLOT_NO
, P_ITEM.ITEM_NO
, P_ITEM.ITEM_DESC1
, P_ITEM.ITEM_UM
, P_ITEM.ITEM_UM2
, P_ITEM.INV_CLASS
, P_ITEM.PLANNING_CLASS
, P_ITEM.SALES_CLASS
, P_LOT.LOT_NO
, P_LOT.SUBLOT_NO
, EVENT.COMPLETED_IND
FROM PMI_LOT_GENEALOGY GEN
, IC_TRAN_PND EVENT
, IC_ITEM_MST I_ITEM
, IC_LOTS_MST I_LOT
, IC_ITEM_MST P_ITEM
, IC_LOTS_MST P_LOT
, OE_ORDER_HEADERS_ALL ORDR
, OE_ORDER_LINES_ALL LINES
, PMI_HZ_PARTY_V CUST
, HR_ALL_ORGANIZATION_UNITS_VL ORG
WHERE EVENT.ITEM_ID = GEN.PRODUCT_ITEM_ID
AND EVENT.LOT_ID = GEN.PRODUCT_LOT_ID
AND EVENT.DOC_TYPE = 'OMSO'
AND I_LOT.ITEM_ID = GEN.INGRED_ITEM_ID
AND I_LOT.LOT_ID = GEN.INGRED_LOT_ID
AND I_ITEM.ITEM_ID = I_LOT.ITEM_ID
AND P_ITEM.ITEM_ID = GEN.PRODUCT_ITEM_ID
AND P_LOT.ITEM_ID = GEN.PRODUCT_ITEM_ID
AND P_LOT.LOT_ID = GEN.PRODUCT_LOT_ID
AND LINES.LINE_ID = EVENT.LINE_ID
AND ORDR.HEADER_ID = LINES.HEADER_ID
AND ORDR.ORG_ID = ORG.ORGANIZATION_ID
AND CUST.SITE_USE_ID = LINES.SHIP_TO_ORG_ID GROUP BY GEN.PRODUCT_ITEM_ID
, GEN.PRODUCT_LOT_ID
, GEN.INGRED_ITEM_ID
, GEN.INGRED_LOT_ID
, EVENT.WHSE_CODE
, EVENT.LOCATION
, EVENT.TRANS_DATE
, ORG.NAME
, ORDR.ORDER_NUMBER
, CUST.PARTY_NUMBER
, CUST.PARTY_NAME
, I_ITEM.ITEM_NO
, I_ITEM.ITEM_DESC1
, I_ITEM.ITEM_UM
, I_ITEM.ITEM_UM2
, I_LOT.LOT_NO
, I_LOT.SUBLOT_NO
, P_ITEM.ITEM_NO
, P_ITEM.ITEM_DESC1
, P_ITEM.ITEM_UM
, P_ITEM.ITEM_UM2
, P_ITEM.INV_CLASS
, P_ITEM.PLANNING_CLASS
, P_ITEM.SALES_CLASS
, P_LOT.LOT_NO
, P_LOT.SUBLOT_NO
, EVENT.COMPLETED_IND HAVING SUM(EVENT.TRANS_QTY) <> 0 WITH READ ONLY