View: PMIBV_LOT_DEST_SHIP_V

Product:	PMI - Process Manufacturing Intelligence (Obsolete)
Description:	This view provides the relationship between a lot and the shipments that its destination lots have been involved in.
Implementation/DBA Data:	APPS.PMIBV_LOT_DEST_SHIP_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
, 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

Columns