View: PMIBV_LOT_SHARED_SHIP_V

Product:	PMI - Process Manufacturing Intelligence (Obsolete)
Description:	provides the relationship between a lot and the shipments that its destination lots have been involved in. A Shared Lot here refers to the lots that shared the same source lots.
Implementation/DBA Data:	APPS.PMIBV_LOT_SHARED_SHIP_V

View Text

SELECT SHARED_V.START_ITEM_ID
, SHARED_V.START_LOT_ID
, SHARED_V.SHARED_ITEM_ID
, SHARED_V.SHARED_LOT_ID
, START_ITEM.ITEM_NO
, START_ITEM.ITEM_DESC1
, START_LOT.LOT_NO
, START_LOT.SUBLOT_NO
, SHARED_ITEM.ITEM_NO
, SHARED_ITEM.ITEM_DESC1
, SHARED_ITEM.ITEM_UM
, SHARED_ITEM.ITEM_UM2
, SHARED_ITEM.INV_CLASS
, SHARED_ITEM.PLANNING_CLASS
, SHARED_ITEM.SALES_CLASS
, SHARED_LOT.LOT_NO
, SHARED_LOT.SUBLOT_NO
, SHARED_ORDR.ORGN_CODE
, SHARED_EVENT.WHSE_CODE
, SHARED_EVENT.LOCATION
, SHARED_ORDR.ORDER_NO
, CUST.CUST_NO
, CUST.CUST_NAME
, SHARED_EVENT.TRANS_DATE
, ABS(SUM(SHARED_EVENT.TRANS_QTY))
, ABS(SUM(SHARED_EVENT.TRANS_QTY2))
, SHARED_EVENT.COMPLETED_IND
FROM PMI_LOT_SHARED_V SHARED_V
, IC_ITEM_MST START_ITEM
, IC_LOTS_MST START_LOT
, IC_ITEM_MST SHARED_ITEM
, IC_LOTS_MST SHARED_LOT
, IC_TRAN_PND SHARED_EVENT
, OP_ORDR_HDR SHARED_ORDR
, OP_CUST_MST CUST
WHERE START_ITEM.ITEM_ID = START_LOT.ITEM_ID
AND START_LOT.ITEM_ID = SHARED_V.START_ITEM_ID
AND START_LOT.LOT_ID = SHARED_V.START_LOT_ID
AND SHARED_ITEM.ITEM_ID = SHARED_V.SHARED_ITEM_ID
AND SHARED_LOT.ITEM_ID = SHARED_V.SHARED_ITEM_ID
AND SHARED_LOT.LOT_ID = SHARED_V.SHARED_LOT_ID
AND SHARED_EVENT.ITEM_ID = SHARED_V.SHARED_ITEM_ID
AND SHARED_EVENT.LOT_ID = SHARED_V.SHARED_LOT_ID
AND SHARED_EVENT.DOC_TYPE = 'OPSO'
AND SHARED_ORDR.ORDER_ID = SHARED_EVENT.DOC_ID
AND CUST.CUST_ID = SHARED_ORDR.SHIPCUST_ID GROUP BY SHARED_V.START_ITEM_ID
, SHARED_V.START_LOT_ID
, SHARED_V.SHARED_ITEM_ID
, SHARED_V.SHARED_LOT_ID
, START_ITEM.ITEM_NO
, START_ITEM.ITEM_DESC1
, START_LOT.LOT_NO
, START_LOT.SUBLOT_NO
, SHARED_ITEM.ITEM_NO
, SHARED_ITEM.ITEM_DESC1
, SHARED_ITEM.ITEM_UM
, SHARED_ITEM.ITEM_UM2
, SHARED_ITEM.INV_CLASS
, SHARED_ITEM.PLANNING_CLASS
, SHARED_ITEM.SALES_CLASS
, SHARED_LOT.LOT_NO
, SHARED_LOT.SUBLOT_NO
, SHARED_ORDR.ORGN_CODE
, SHARED_EVENT.WHSE_CODE
, SHARED_EVENT.LOCATION
, SHARED_ORDR.ORDER_NO
, CUST.CUST_NO
, CUST.CUST_NAME
, SHARED_EVENT.TRANS_DATE
, SHARED_EVENT.COMPLETED_IND HAVING SUM(SHARED_EVENT.TRANS_QTY) <> 0 UNION ALL SELECT SHARED_V.START_ITEM_ID
, SHARED_V.START_LOT_ID
, SHARED_V.SHARED_ITEM_ID
, SHARED_V.SHARED_LOT_ID
, START_ITEM.ITEM_NO
, START_ITEM.ITEM_DESC1
, START_LOT.LOT_NO
, START_LOT.SUBLOT_NO
, SHARED_ITEM.ITEM_NO
, SHARED_ITEM.ITEM_DESC1
, SHARED_ITEM.ITEM_UM
, SHARED_ITEM.ITEM_UM2
, SHARED_ITEM.INV_CLASS
, SHARED_ITEM.PLANNING_CLASS
, SHARED_ITEM.SALES_CLASS
, SHARED_LOT.LOT_NO
, SHARED_LOT.SUBLOT_NO
, ORG.NAME
, SHARED_EVENT.WHSE_CODE
, SHARED_EVENT.LOCATION
, TO_CHAR(SHARED_ORDR.ORDER_NUMBER)
, CUST.PARTY_NUMBER
, CUST.PARTY_NAME
, SHARED_EVENT.TRANS_DATE
, ABS(SUM(SHARED_EVENT.TRANS_QTY))
, ABS(SUM(SHARED_EVENT.TRANS_QTY2))
, SHARED_EVENT.COMPLETED_IND
FROM PMI_LOT_SHARED_V SHARED_V
, IC_ITEM_MST START_ITEM
, IC_LOTS_MST START_LOT
, IC_ITEM_MST SHARED_ITEM
, IC_LOTS_MST SHARED_LOT
, IC_TRAN_PND SHARED_EVENT
, OE_ORDER_HEADERS_ALL SHARED_ORDR
, OE_ORDER_LINES_ALL LINES
, PMI_HZ_PARTY_V CUST
, HR_ALL_ORGANIZATION_UNITS_VL ORG
WHERE START_ITEM.ITEM_ID = START_LOT.ITEM_ID
AND START_LOT.ITEM_ID = SHARED_V.START_ITEM_ID
AND START_LOT.LOT_ID = SHARED_V.START_LOT_ID
AND SHARED_ITEM.ITEM_ID = SHARED_V.SHARED_ITEM_ID
AND SHARED_LOT.ITEM_ID = SHARED_V.SHARED_ITEM_ID
AND SHARED_LOT.LOT_ID = SHARED_V.SHARED_LOT_ID
AND SHARED_EVENT.ITEM_ID = SHARED_V.SHARED_ITEM_ID
AND SHARED_EVENT.LOT_ID = SHARED_V.SHARED_LOT_ID
AND SHARED_EVENT.DOC_TYPE = 'OMSO'
AND SHARED_ORDR.HEADER_ID = LINES.HEADER_ID
AND LINES.LINE_ID = SHARED_EVENT.LINE_ID
AND SHARED_ORDR.ORG_ID = ORG.ORGANIZATION_ID
AND CUST.SITE_USE_ID = LINES.SHIP_TO_ORG_ID GROUP BY SHARED_V.START_ITEM_ID
, SHARED_V.START_LOT_ID
, SHARED_V.SHARED_ITEM_ID
, SHARED_V.SHARED_LOT_ID
, START_ITEM.ITEM_NO
, START_ITEM.ITEM_DESC1
, START_LOT.LOT_NO
, START_LOT.SUBLOT_NO
, SHARED_ITEM.ITEM_NO
, SHARED_ITEM.ITEM_DESC1
, SHARED_ITEM.ITEM_UM
, SHARED_ITEM.ITEM_UM2
, SHARED_ITEM.INV_CLASS
, SHARED_ITEM.PLANNING_CLASS
, SHARED_ITEM.SALES_CLASS
, SHARED_LOT.LOT_NO
, SHARED_LOT.SUBLOT_NO
, ORG.NAME
, SHARED_EVENT.WHSE_CODE
, SHARED_EVENT.LOCATION
, SHARED_ORDR.ORDER_NUMBER
, CUST.PARTY_NUMBER
, CUST.PARTY_NAME
, SHARED_EVENT.TRANS_DATE
, SHARED_EVENT.COMPLETED_IND HAVING SUM(SHARED_EVENT.TRANS_QTY) <> 0 WITH READ ONLY

Columns