VIEW: APPS.PMIBV_LOT_SOURCE_PURCH_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, SUM(event.trans_qty), SUM(event.trans_qty2), whse.orgn_code, rech.receipt_num, po.segment1, vnd.vendor_number, vnd.vendor_name, i_item.item_no, i_item.item_desc1, i_item.item_um, i_item.item_um2 , i_item.inv_class , i_item.planning_class, i_item.sales_class, 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_lot.lot_no , p_lot.sublot_no FROM ic_item_mst p_item, ic_lots_mst p_lot, pmi_lot_genealogy gen, ic_tran_pnd event, ic_item_mst i_item, ic_lots_mst i_lot, PO_HEADERS_ALL po, RCV_SHIPMENT_HEADERS rech, RCV_SHIPMENT_LINES rec, RCV_TRANSACTIONS rcvtran, IC_WHSE_MST whse, po_vendor_sites_all vs, po_vendors_view vnd WHERE p_lot.item_id = p_item.item_id AND gen.product_item_id = p_lot.item_id AND gen.product_lot_id = p_lot.lot_id AND event.item_id = gen.ingred_item_id AND event.lot_id = gen.ingred_lot_id AND event.doc_type = 'PORC' AND rech.shipment_header_id = event.doc_id AND i_item.item_id = gen.ingred_item_id AND i_lot.item_id = gen.ingred_item_id AND i_lot.lot_id = gen.ingred_lot_id AND rec.shipment_header_id = event.doc_id AND rcvtran.transaction_id = event.line_id AND rcvtran.shipment_line_id = rec.shipment_line_id AND vs.vendor_site_id(+) = po.vendor_site_id AND vs.vendor_id = vnd.vendor_id(+) AND po.po_header_id(+) = rec.po_header_id AND whse.mtl_organization_id = rech.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, whse.orgn_code, rech.receipt_num, po.segment1, vnd.vendor_number, vnd.vendor_name, i_item.item_no, i_item.item_desc1, i_item.item_um, i_item.item_um2 , i_item.inv_class , i_item.planning_class, i_item.sales_class, 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_lot.lot_no , p_lot.sublot_no 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, SUM(event.trans_qty), SUM(event.trans_qty2), rech.orgn_code, rech.recv_no, po.po_no, vend.vendor_no, vend.vendor_name, i_item.item_no, i_item.item_desc1, i_item.item_um, i_item.item_um2 , i_item.inv_class , i_item.planning_class, i_item.sales_class, 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_lot.lot_no , p_lot.sublot_no FROM ic_item_mst p_item, ic_lots_mst p_lot, pmi_lot_genealogy gen, ic_tran_pnd event, ic_item_mst i_item, ic_lots_mst i_lot, po_recv_dtl rec, po_recv_hdr rech, po_ordr_hdr po, po_vend_mst vend WHERE p_lot.item_id = p_item.item_id AND gen.product_item_id = p_lot.item_id AND gen.product_lot_id = p_lot.lot_id AND event.item_id = gen.ingred_item_id AND event.lot_id = gen.ingred_lot_id AND event.doc_type = 'RECV' AND rech.recv_id = event.doc_id AND i_item.item_id = gen.ingred_item_id AND i_lot.item_id = gen.ingred_item_id AND i_lot.lot_id = gen.ingred_lot_id AND rec.recv_id = event.doc_id AND rec.line_id = event.line_id AND vend.vendor_id(+) = rec.shipvend_id AND po.po_id(+) = rec.po_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, rech.orgn_code, rech.recv_no, po.po_no, vend.vendor_no, vend.vendor_name, i_item.item_no, i_item.item_desc1, i_item.item_um, i_item.item_um2 , i_item.inv_class , i_item.planning_class, i_item.sales_class, 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_lot.lot_no , p_lot.sublot_no 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, SUM(event.trans_qty), SUM(event.trans_qty2), NULL, NULL, NULL, vend.vendor_no, vend.vendor_name, i_item.item_no , i_item.item_desc1 , i_item.item_um , i_item.item_um2 , i_item.inv_class, i_item.planning_class, i_item.sales_class, 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_lot.lot_no, p_lot.sublot_no FROM pmi_lot_source_leaf_v 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, po_vend_mst vend WHERE event.item_id = gen.ingred_item_id AND event.lot_id = gen.ingred_lot_id AND event.doc_type NOT IN ('RECV','PORC') AND i_item.item_id = gen.ingred_item_id AND i_lot.item_id = gen.ingred_item_id AND i_lot.lot_id = gen.ingred_lot_id AND p_item.item_id = gen.product_item_id AND p_lot.item_id = p_item.item_id AND p_lot.item_id = gen.product_item_id AND p_lot.lot_id = gen.product_lot_id AND vend.vendor_id(+) = i_lot.shipvend_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, vend.vendor_no, vend.vendor_name, i_item.item_no , i_item.item_desc1 , i_item.item_um , i_item.item_um2 , i_item.inv_class, i_item.planning_class, i_item.sales_class, 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_lot.lot_no, p_lot.sublot_no 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, SUM(event.trans_qty), SUM(event.trans_qty2), NULL, NULL, NULL, vend.vendor_no, vend.vendor_name, i_item.item_no , i_item.item_desc1 , i_item.item_um , i_item.item_um2 , i_item.inv_class, i_item.planning_class, i_item.sales_class, 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_lot.lot_no, p_lot.sublot_no FROM pmi_lot_source_leaf_v gen, ic_tran_cmp event, ic_item_mst i_item, ic_lots_mst i_lot, ic_item_mst p_item, ic_lots_mst p_lot, po_vend_mst vend WHERE event.item_id = gen.ingred_item_id AND event.lot_id = gen.ingred_lot_id AND event.doc_type NOT IN ('RECV','PORC') AND i_item.item_id = gen.ingred_item_id AND i_lot.item_id = gen.ingred_item_id AND i_lot.lot_id = gen.ingred_lot_id AND p_item.item_id = gen.product_item_id AND p_lot.item_id = p_item.item_id AND p_lot.item_id = gen.product_item_id AND p_lot.lot_id = gen.product_lot_id AND vend.vendor_id(+) = i_lot.shipvend_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, vend.vendor_no, vend.vendor_name, i_item.item_no , i_item.item_desc1 , i_item.item_um , i_item.item_um2 , i_item.inv_class, i_item.planning_class, i_item.sales_class, 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_lot.lot_no, p_lot.sublot_no 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, TO_CHAR(NULL), TO_CHAR(NULL), TO_DATE(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_CHAR(NULL), TO_CHAR(NULL), TO_CHAR(NULL), vend.vendor_no, vend.vendor_name, i_item.item_no , i_item.item_desc1 , i_item.item_um , i_item.item_um2 , i_item.inv_class, i_item.planning_class, i_item.sales_class, 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_lot.lot_no, p_lot.sublot_no FROM pmi_lot_source_leaf_v gen, ic_item_mst i_item, ic_lots_mst i_lot, ic_item_mst p_item, ic_lots_mst p_lot, po_vend_mst vend WHERE NOT EXISTS ( SELECT 'RECEIPT' FROM ic_tran_pnd WHERE item_id = gen.ingred_item_id AND lot_id = gen.ingred_lot_id GROUP BY doc_type, doc_id, whse_code, location, trans_date HAVING SUM(trans_qty) > 0 ) AND i_item.item_id = gen.ingred_item_id AND i_lot.item_id = gen.ingred_item_id AND i_lot.lot_id = gen.ingred_lot_id AND p_item.item_id = gen.product_item_id AND p_lot.item_id = p_item.item_id AND p_lot.item_id = gen.product_item_id AND p_lot.lot_id = gen.product_lot_id AND vend.vendor_id(+) = i_lot.shipvend_id 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
, SUM(EVENT.TRANS_QTY)
, SUM(EVENT.TRANS_QTY2)
, WHSE.ORGN_CODE
, RECH.RECEIPT_NUM
, PO.SEGMENT1
, VND.VENDOR_NUMBER
, VND.VENDOR_NAME
, I_ITEM.ITEM_NO
, I_ITEM.ITEM_DESC1
, I_ITEM.ITEM_UM
, I_ITEM.ITEM_UM2
, I_ITEM.INV_CLASS
, I_ITEM.PLANNING_CLASS
, I_ITEM.SALES_CLASS
, 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_LOT.LOT_NO
, P_LOT.SUBLOT_NO
FROM IC_ITEM_MST P_ITEM
, IC_LOTS_MST P_LOT
, PMI_LOT_GENEALOGY GEN
, IC_TRAN_PND EVENT
, IC_ITEM_MST I_ITEM
, IC_LOTS_MST I_LOT
, PO_HEADERS_ALL PO
, RCV_SHIPMENT_HEADERS RECH
, RCV_SHIPMENT_LINES REC
, RCV_TRANSACTIONS RCVTRAN
, IC_WHSE_MST WHSE
, PO_VENDOR_SITES_ALL VS
, PO_VENDORS_VIEW VND
WHERE P_LOT.ITEM_ID = P_ITEM.ITEM_ID
AND GEN.PRODUCT_ITEM_ID = P_LOT.ITEM_ID
AND GEN.PRODUCT_LOT_ID = P_LOT.LOT_ID
AND EVENT.ITEM_ID = GEN.INGRED_ITEM_ID
AND EVENT.LOT_ID = GEN.INGRED_LOT_ID
AND EVENT.DOC_TYPE = 'PORC'
AND RECH.SHIPMENT_HEADER_ID = EVENT.DOC_ID
AND I_ITEM.ITEM_ID = GEN.INGRED_ITEM_ID
AND I_LOT.ITEM_ID = GEN.INGRED_ITEM_ID
AND I_LOT.LOT_ID = GEN.INGRED_LOT_ID
AND REC.SHIPMENT_HEADER_ID = EVENT.DOC_ID
AND RCVTRAN.TRANSACTION_ID = EVENT.LINE_ID
AND RCVTRAN.SHIPMENT_LINE_ID = REC.SHIPMENT_LINE_ID
AND VS.VENDOR_SITE_ID(+) = PO.VENDOR_SITE_ID
AND VS.VENDOR_ID = VND.VENDOR_ID(+)
AND PO.PO_HEADER_ID(+) = REC.PO_HEADER_ID
AND WHSE.MTL_ORGANIZATION_ID = RECH.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
, WHSE.ORGN_CODE
, RECH.RECEIPT_NUM
, PO.SEGMENT1
, VND.VENDOR_NUMBER
, VND.VENDOR_NAME
, I_ITEM.ITEM_NO
, I_ITEM.ITEM_DESC1
, I_ITEM.ITEM_UM
, I_ITEM.ITEM_UM2
, I_ITEM.INV_CLASS
, I_ITEM.PLANNING_CLASS
, I_ITEM.SALES_CLASS
, 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_LOT.LOT_NO
, P_LOT.SUBLOT_NO 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
, SUM(EVENT.TRANS_QTY)
, SUM(EVENT.TRANS_QTY2)
, RECH.ORGN_CODE
, RECH.RECV_NO
, PO.PO_NO
, VEND.VENDOR_NO
, VEND.VENDOR_NAME
, I_ITEM.ITEM_NO
, I_ITEM.ITEM_DESC1
, I_ITEM.ITEM_UM
, I_ITEM.ITEM_UM2
, I_ITEM.INV_CLASS
, I_ITEM.PLANNING_CLASS
, I_ITEM.SALES_CLASS
, 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_LOT.LOT_NO
, P_LOT.SUBLOT_NO
FROM IC_ITEM_MST P_ITEM
, IC_LOTS_MST P_LOT
, PMI_LOT_GENEALOGY GEN
, IC_TRAN_PND EVENT
, IC_ITEM_MST I_ITEM
, IC_LOTS_MST I_LOT
, PO_RECV_DTL REC
, PO_RECV_HDR RECH
, PO_ORDR_HDR PO
, PO_VEND_MST VEND
WHERE P_LOT.ITEM_ID = P_ITEM.ITEM_ID
AND GEN.PRODUCT_ITEM_ID = P_LOT.ITEM_ID
AND GEN.PRODUCT_LOT_ID = P_LOT.LOT_ID
AND EVENT.ITEM_ID = GEN.INGRED_ITEM_ID
AND EVENT.LOT_ID = GEN.INGRED_LOT_ID
AND EVENT.DOC_TYPE = 'RECV'
AND RECH.RECV_ID = EVENT.DOC_ID
AND I_ITEM.ITEM_ID = GEN.INGRED_ITEM_ID
AND I_LOT.ITEM_ID = GEN.INGRED_ITEM_ID
AND I_LOT.LOT_ID = GEN.INGRED_LOT_ID
AND REC.RECV_ID = EVENT.DOC_ID
AND REC.LINE_ID = EVENT.LINE_ID
AND VEND.VENDOR_ID(+) = REC.SHIPVEND_ID
AND PO.PO_ID(+) = REC.PO_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
, RECH.ORGN_CODE
, RECH.RECV_NO
, PO.PO_NO
, VEND.VENDOR_NO
, VEND.VENDOR_NAME
, I_ITEM.ITEM_NO
, I_ITEM.ITEM_DESC1
, I_ITEM.ITEM_UM
, I_ITEM.ITEM_UM2
, I_ITEM.INV_CLASS
, I_ITEM.PLANNING_CLASS
, I_ITEM.SALES_CLASS
, 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_LOT.LOT_NO
, P_LOT.SUBLOT_NO 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
, SUM(EVENT.TRANS_QTY)
, SUM(EVENT.TRANS_QTY2)
, NULL
, NULL
, NULL
, VEND.VENDOR_NO
, VEND.VENDOR_NAME
, I_ITEM.ITEM_NO
, I_ITEM.ITEM_DESC1
, I_ITEM.ITEM_UM
, I_ITEM.ITEM_UM2
, I_ITEM.INV_CLASS
, I_ITEM.PLANNING_CLASS
, I_ITEM.SALES_CLASS
, 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_LOT.LOT_NO
, P_LOT.SUBLOT_NO
FROM PMI_LOT_SOURCE_LEAF_V 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
, PO_VEND_MST VEND
WHERE EVENT.ITEM_ID = GEN.INGRED_ITEM_ID
AND EVENT.LOT_ID = GEN.INGRED_LOT_ID
AND EVENT.DOC_TYPE NOT IN ('RECV'
, 'PORC')
AND I_ITEM.ITEM_ID = GEN.INGRED_ITEM_ID
AND I_LOT.ITEM_ID = GEN.INGRED_ITEM_ID
AND I_LOT.LOT_ID = GEN.INGRED_LOT_ID
AND P_ITEM.ITEM_ID = GEN.PRODUCT_ITEM_ID
AND P_LOT.ITEM_ID = P_ITEM.ITEM_ID
AND P_LOT.ITEM_ID = GEN.PRODUCT_ITEM_ID
AND P_LOT.LOT_ID = GEN.PRODUCT_LOT_ID
AND VEND.VENDOR_ID(+) = I_LOT.SHIPVEND_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
, VEND.VENDOR_NO
, VEND.VENDOR_NAME
, I_ITEM.ITEM_NO
, I_ITEM.ITEM_DESC1
, I_ITEM.ITEM_UM
, I_ITEM.ITEM_UM2
, I_ITEM.INV_CLASS
, I_ITEM.PLANNING_CLASS
, I_ITEM.SALES_CLASS
, 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_LOT.LOT_NO
, P_LOT.SUBLOT_NO 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
, SUM(EVENT.TRANS_QTY)
, SUM(EVENT.TRANS_QTY2)
, NULL
, NULL
, NULL
, VEND.VENDOR_NO
, VEND.VENDOR_NAME
, I_ITEM.ITEM_NO
, I_ITEM.ITEM_DESC1
, I_ITEM.ITEM_UM
, I_ITEM.ITEM_UM2
, I_ITEM.INV_CLASS
, I_ITEM.PLANNING_CLASS
, I_ITEM.SALES_CLASS
, 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_LOT.LOT_NO
, P_LOT.SUBLOT_NO
FROM PMI_LOT_SOURCE_LEAF_V GEN
, IC_TRAN_CMP EVENT
, IC_ITEM_MST I_ITEM
, IC_LOTS_MST I_LOT
, IC_ITEM_MST P_ITEM
, IC_LOTS_MST P_LOT
, PO_VEND_MST VEND
WHERE EVENT.ITEM_ID = GEN.INGRED_ITEM_ID
AND EVENT.LOT_ID = GEN.INGRED_LOT_ID
AND EVENT.DOC_TYPE NOT IN ('RECV'
, 'PORC')
AND I_ITEM.ITEM_ID = GEN.INGRED_ITEM_ID
AND I_LOT.ITEM_ID = GEN.INGRED_ITEM_ID
AND I_LOT.LOT_ID = GEN.INGRED_LOT_ID
AND P_ITEM.ITEM_ID = GEN.PRODUCT_ITEM_ID
AND P_LOT.ITEM_ID = P_ITEM.ITEM_ID
AND P_LOT.ITEM_ID = GEN.PRODUCT_ITEM_ID
AND P_LOT.LOT_ID = GEN.PRODUCT_LOT_ID
AND VEND.VENDOR_ID(+) = I_LOT.SHIPVEND_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
, VEND.VENDOR_NO
, VEND.VENDOR_NAME
, I_ITEM.ITEM_NO
, I_ITEM.ITEM_DESC1
, I_ITEM.ITEM_UM
, I_ITEM.ITEM_UM2
, I_ITEM.INV_CLASS
, I_ITEM.PLANNING_CLASS
, I_ITEM.SALES_CLASS
, 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_LOT.LOT_NO
, P_LOT.SUBLOT_NO 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
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, VEND.VENDOR_NO
, VEND.VENDOR_NAME
, I_ITEM.ITEM_NO
, I_ITEM.ITEM_DESC1
, I_ITEM.ITEM_UM
, I_ITEM.ITEM_UM2
, I_ITEM.INV_CLASS
, I_ITEM.PLANNING_CLASS
, I_ITEM.SALES_CLASS
, 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_LOT.LOT_NO
, P_LOT.SUBLOT_NO
FROM PMI_LOT_SOURCE_LEAF_V GEN
, IC_ITEM_MST I_ITEM
, IC_LOTS_MST I_LOT
, IC_ITEM_MST P_ITEM
, IC_LOTS_MST P_LOT
, PO_VEND_MST VEND
WHERE NOT EXISTS ( SELECT 'RECEIPT'
FROM IC_TRAN_PND
WHERE ITEM_ID = GEN.INGRED_ITEM_ID
AND LOT_ID = GEN.INGRED_LOT_ID GROUP BY DOC_TYPE
, DOC_ID
, WHSE_CODE
, LOCATION
, TRANS_DATE HAVING SUM(TRANS_QTY) > 0 )
AND I_ITEM.ITEM_ID = GEN.INGRED_ITEM_ID
AND I_LOT.ITEM_ID = GEN.INGRED_ITEM_ID
AND I_LOT.LOT_ID = GEN.INGRED_LOT_ID
AND P_ITEM.ITEM_ID = GEN.PRODUCT_ITEM_ID
AND P_LOT.ITEM_ID = P_ITEM.ITEM_ID
AND P_LOT.ITEM_ID = GEN.PRODUCT_ITEM_ID
AND P_LOT.LOT_ID = GEN.PRODUCT_LOT_ID
AND VEND.VENDOR_ID(+) = I_LOT.SHIPVEND_ID WITH READ ONLY