VIEW: APPS.PMIBV_LOT_SHARED_SHIP_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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