VIEW: APPS.PO_SOURCE_SHIPMENT_NUM_V

Source

View Text - Preformatted

SELECT PLL.SHIPMENT_NUM , HRL.LOCATION_CODE , OOD.ORGANIZATION_CODE||'-'|| OOD.ORGANIZATION_NAME ORGANIZATION , (PLL.QUANTITY - NVL(PLL.QUANTITY_CANCELLED,0)) - NVL(SUM(PLL2.QUANTITY - NVL(PLL2.QUANTITY_CANCELLED,0)),0) QUANTITY , PLL.UNIT_MEAS_LOOKUP_CODE , PLL.LINE_LOCATION_ID , PLL.PO_LINE_ID , PLL.SHIP_TO_LOCATION_ID , PLL.SHIP_TO_ORGANIZATION_ID , OOD.ORGANIZATION_CODE , PLL.SECONDARY_UNIT_OF_MEASURE , (PLL.SECONDARY_QUANTITY - NVL(PLL.SECONDARY_QUANTITY_CANCELLED,0)) - NVL(SUM(PLL2.SECONDARY_QUANTITY - NVL(PLL2.SECONDARY_QUANTITY_CANCELLED,0)),0) SECONDARY_QUANTITY , PLL.PREFERRED_GRADE , (PLL.QUANTITY - NVL(PLL.QUANTITY_CANCELLED,0)) SHIPMENT_QUANTITY , PLL.ORG_ID FROM PO_LINE_LOCATIONS_ALL PLL2, ORG_ORGANIZATION_DEFINITIONS OOD, HR_LOCATIONS HRL, PO_LINE_LOCATIONS PLL, FINANCIALS_SYSTEM_PARAMS_ALL FSP WHERE PLL.LINE_LOCATION_ID = PLL2.SOURCE_SHIPMENT_ID (+) AND NVL(PLL.CANCEL_FLAG,'N') = 'N' AND NVL(PLL.CLOSED_CODE,'OPEN') != 'FINALLY CLOSED' AND PLL.SHIP_TO_LOCATION_ID = HRL.LOCATION_ID AND PLL.SHIPMENT_TYPE = 'PLANNED' AND PLL.SHIP_TO_ORGANIZATION_ID = OOD.ORGANIZATION_ID AND OOD.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID AND FSP.ORG_ID = PLL.ORG_ID AND PLL.ORG_ID = PLL2.ORG_ID(+) GROUP BY PLL.SHIPMENT_NUM, HRL.LOCATION_CODE, OOD.ORGANIZATION_CODE||'-'||OOD.ORGANIZATION_NAME, PLL.QUANTITY, PLL.QUANTITY_CANCELLED, PLL.UNIT_MEAS_LOOKUP_CODE, PLL.LINE_LOCATION_ID, PLL.PO_LINE_ID, PLL.SHIP_TO_LOCATION_ID, PLL.SHIP_TO_ORGANIZATION_ID, OOD.ORGANIZATION_CODE, PLL.SECONDARY_UNIT_OF_MEASURE, PLL.SECONDARY_QUANTITY, PLL.SECONDARY_QUANTITY_CANCELLED, PLL.PREFERRED_GRADE, PLL.ORG_ID HAVING (PLL.QUANTITY - NVL(PLL.QUANTITY_CANCELLED,0)) - NVL(SUM(PLL2.QUANTITY - NVL(PLL2.QUANTITY_CANCELLED,0)),0) > 0

View Text - HTML Formatted

SELECT PLL.SHIPMENT_NUM
, HRL.LOCATION_CODE
, OOD.ORGANIZATION_CODE||'-'|| OOD.ORGANIZATION_NAME ORGANIZATION
, (PLL.QUANTITY - NVL(PLL.QUANTITY_CANCELLED
, 0)) - NVL(SUM(PLL2.QUANTITY - NVL(PLL2.QUANTITY_CANCELLED
, 0))
, 0) QUANTITY
, PLL.UNIT_MEAS_LOOKUP_CODE
, PLL.LINE_LOCATION_ID
, PLL.PO_LINE_ID
, PLL.SHIP_TO_LOCATION_ID
, PLL.SHIP_TO_ORGANIZATION_ID
, OOD.ORGANIZATION_CODE
, PLL.SECONDARY_UNIT_OF_MEASURE
, (PLL.SECONDARY_QUANTITY - NVL(PLL.SECONDARY_QUANTITY_CANCELLED
, 0)) - NVL(SUM(PLL2.SECONDARY_QUANTITY - NVL(PLL2.SECONDARY_QUANTITY_CANCELLED
, 0))
, 0) SECONDARY_QUANTITY
, PLL.PREFERRED_GRADE
, (PLL.QUANTITY - NVL(PLL.QUANTITY_CANCELLED
, 0)) SHIPMENT_QUANTITY
, PLL.ORG_ID
FROM PO_LINE_LOCATIONS_ALL PLL2
, ORG_ORGANIZATION_DEFINITIONS OOD
, HR_LOCATIONS HRL
, PO_LINE_LOCATIONS PLL
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE PLL.LINE_LOCATION_ID = PLL2.SOURCE_SHIPMENT_ID (+)
AND NVL(PLL.CANCEL_FLAG
, 'N') = 'N'
AND NVL(PLL.CLOSED_CODE
, 'OPEN') != 'FINALLY CLOSED'
AND PLL.SHIP_TO_LOCATION_ID = HRL.LOCATION_ID
AND PLL.SHIPMENT_TYPE = 'PLANNED'
AND PLL.SHIP_TO_ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND OOD.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID
AND FSP.ORG_ID = PLL.ORG_ID
AND PLL.ORG_ID = PLL2.ORG_ID(+) GROUP BY PLL.SHIPMENT_NUM
, HRL.LOCATION_CODE
, OOD.ORGANIZATION_CODE||'-'||OOD.ORGANIZATION_NAME
, PLL.QUANTITY
, PLL.QUANTITY_CANCELLED
, PLL.UNIT_MEAS_LOOKUP_CODE
, PLL.LINE_LOCATION_ID
, PLL.PO_LINE_ID
, PLL.SHIP_TO_LOCATION_ID
, PLL.SHIP_TO_ORGANIZATION_ID
, OOD.ORGANIZATION_CODE
, PLL.SECONDARY_UNIT_OF_MEASURE
, PLL.SECONDARY_QUANTITY
, PLL.SECONDARY_QUANTITY_CANCELLED
, PLL.PREFERRED_GRADE
, PLL.ORG_ID HAVING (PLL.QUANTITY - NVL(PLL.QUANTITY_CANCELLED
, 0)) - NVL(SUM(PLL2.QUANTITY - NVL(PLL2.QUANTITY_CANCELLED
, 0))
, 0) > 0