View: PMIBV_LOT_SOURCE_PURCH_V

Product:	PMI - Process Manufacturing Intelligence (Obsolete)
Description:	This view lists the raw material lots that went into a product lot.
Implementation/DBA Data:	APPS.PMIBV_LOT_SOURCE_PURCH_V

View Text

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

Columns