SELECT *
FROM (SELECT POH.SEGMENT1 PO_NUMBER,
PRL.RELEASE_NUM RELEASE_NUM,
DECODE (
PRL.PO_RELEASE_ID,
NULL,
POH.clm_document_number,
POH.clm_document_number
|| ‘-‘
|| TO_CHAR (PRL.RELEASE_NUM)
)
DISPLAY_PO_NUMBER,
POL.LINE_NUM LINE_NUM,
POL.ITEM_ID ITEM_ID,
POL.ITEM_REVISION ITEM_REVISION,
POL.ITEM_DESCRIPTION ITEM_DESCRIPTION,
POL.VENDOR_PRODUCT_NUM SUPPLIER_ITEM_NUMBER,
NVL (PLL.PROMISED_DATE, PLL.NEED_BY_DATE) DUE_DATE,
PLL.RECEIPT_DAYS_EXCEPTION_CODE
RECEIPT_DAYS_EXCEPTION_CODE,
PLL.DAYS_EARLY_RECEIPT_ALLOWED DAYS_EARLY_RECEIPT_ALLOWED,
PLL.DAYS_LATE_RECEIPT_ALLOWED DAYS_LATE_RECEIPT_ALLOWED,
POL.UNIT_MEAS_LOOKUP_CODE UNIT_MEAS_LOOKUP_CODE,
TO_NUMBER (NULL) SHIPMENT_QUANTITY,
PLL.QUANTITY QUANTITY_ORDERED,
PLL.QUANTITY_RECEIVED QUANTITY_RECEIVED,
NVL (
HRL.LOCATION_CODE,
SUBSTR (RTRIM (HZ.ADDRESS1) || ‘-‘ || RTRIM (HZ.CITY),
1,
20)
)
SHIP_TO_LOCATION,
PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE,
PVS.PAY_SITE_FLAG PAY_SITE_FLAG,
POH.PO_HEADER_ID PO_HEADER_ID,
POH.CURRENCY_CODE CURRENCY_CODE,
POH.RATE_TYPE RATE_TYPE,
POH.RATE RATE,
POH.RATE_DATE RATE_DATE,
POL.PO_LINE_ID PO_LINE_ID,
PLL.LINE_LOCATION_ID LINE_LOCATION_ID,
PLL.SHIP_TO_ORGANIZATION_ID ORG_ID,
POL.UNIT_PRICE UNIT_PRICE,
POH.VENDOR_ID VENDOR_ID,
POH.VENDOR_SITE_ID VENDOR_SITE_ID,
POV.VENDOR_NAME VENDOR_NAME,
PLL.SHIPMENT_NUM SHIPMENT_NUM,
POH.VENDOR_CONTACT_ID VENDOR_CONTACT_ID,
MSI.CONCATENATED_SEGMENTS ITEM_NUM,
PLL.TAXABLE_FLAG TAXABLE_FLAG,
” TAX_NAME,
DECODE (prl.po_release_id,
NULL, POH.REVISION_NUM,
prl.revision_num)
REVISION_NUM,
TO_NUMBER (NULL) TOLERABLE_QUANTITY,
NULL ORGANIZATION_CODE,
HRO2.NAME SHIP_TO_ORG_NAME,
HRO.NAME ORGANIZATION_NAME,
MUM.UOM_CLASS UOM_CLASS,
PLL.ORG_ID OPERATING_UNIT_ID,
PLL.PO_RELEASE_ID PO_RELEASE_ID,
PLL.MATCH_OPTION MATCH_OPTION,
PVS.PAY_ON_CODE PAY_ON_CODE,
PLL.CONSIGNED_FLAG CONSIGNED_FLAG,
PLL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID,
POL.SUPPLIER_REF_NUMBER SUPPLIER_REF_NUMBER,
NVL (POH.SHIPPING_CONTROL, ‘SUPPLIER’)
TRANSPORTATION_ARRANGED_BY,
PLC.DISPLAYED_FIELD TAB_Display,
NVL (MSI.PRIMARY_UNIT_OF_MEASURE,
POL.UNIT_MEAS_LOOKUP_CODE),
PLL.QTY_RCV_EXCEPTION_CODE,
NVL (POL.LINE_NUM_DISPLAY, POL.LINE_NUM) LINE_NUMBER,
NVL (PDSH.CLM_FLAG, ‘N’) CLM_FLAG,
TO_NUMBER (NULL) QUANTITY_SHIPPED,
DECODE (POL.CLM_UNDEF_FLAG, ‘Y’, ‘Yes’, ‘No’) UNDEF_STS,
POL.ORDER_TYPE_LOOKUP_CODE
FROM PO_HEADERS_ALL POH,
PO_LINES_ALL POL,
PO_LINE_LOCATIONS_ALL PLL,
PO_RELEASES_ALL PRL,
PO_VENDOR_SITES_ALL PVS,
HR_LOCATIONS_ALL_TL HRL,
HZ_LOCATIONS HZ,
PO_VENDORS POV,
MTL_SYSTEM_ITEMS_KFV MSI,
MTL_UNITS_OF_MEASURE MUM,
HR_ALL_ORGANIZATION_UNITS_TL HRO,
HR_ALL_ORGANIZATION_UNITS_TL HRO2,
PO_LOOKUP_CODES PLC,
PO_DOC_STYLE_HEADERS PDSH
WHERE POH.PO_HEADER_ID = PLL.PO_HEADER_ID
AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POL.PO_LINE_ID = PLL.PO_LINE_ID
AND POL.UNIT_MEAS_LOOKUP_CODE = MUM.UNIT_OF_MEASURE(+)
AND PLL.PO_RELEASE_ID = PRL.PO_RELEASE_ID(+)
AND POV.VENDOR_ID = POH.VENDOR_ID
AND PVS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID
AND HRL.LOCATION_ID(+) = PLL.SHIP_TO_LOCATION_ID
AND HRL.LANGUAGE(+) = USERENV (‘LANG’)
AND HZ.LOCATION_ID(+) = PLL.SHIP_TO_LOCATION_ID
AND NVL (PLL.APPROVED_FLAG, ‘N’) = ‘Y’
AND NVL (PLL.CANCEL_FLAG, ‘N’) = ‘N’
AND NVL (POH.FROZEN_FLAG, ‘N’) = ‘N’
AND NVL (PLL.CLOSED_CODE, ‘OPEN’) NOT IN
(‘FINALLY CLOSED’,
‘CLOSED’,
‘CLOSED FOR RECEIVING’,
‘CANCELLED’)
AND (PLL.QUANTITY
* (1 + NVL (PLL.QTY_RCV_TOLERANCE, 0) / 100)) >
( NVL (PLL.QUANTITY_RECEIVED, 0)
+ NVL (PLL.QUANTITY_SHIPPED, 0)
+ NVL (PLL.QUANTITY_CANCELLED, 0)
+ NVL (
POS_ASN_CREATE_PVT.get_po_pending_asn_quantity(LINE_LOCATION_ID),
0
))
AND PLL.SHIPMENT_TYPE IN
(‘STANDARD’, ‘BLANKET’, ‘SCHEDULED’)
AND NVL (PLL.payment_type, ‘ ‘) NOT IN
(‘ADVANCE’,
‘DELIVERY’,
‘MILESTONE’,
‘RATE’,
‘LUMPSUM’)
AND POL.ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND NVL (MSI.ORGANIZATION_ID, PLL.SHIP_TO_ORGANIZATION_ID) =
PLL.SHIP_TO_ORGANIZATION_ID
AND HRO2.ORGANIZATION_ID(+) = PLL.SHIP_TO_ORGANIZATION_ID
AND HRO2.LANGUAGE(+) = USERENV (‘LANG’)
AND HRO.ORGANIZATION_ID(+) = POH.ORG_ID
AND HRO.LANGUAGE(+) = USERENV (‘LANG’)
AND DECODE (PLL.PO_RELEASE_ID,
NULL,
NVL (POH.CONSIGNED_CONSUMPTION_FLAG, ‘N’),
NVL (PRL.CONSIGNED_CONSUMPTION_FLAG, ‘N’)) !=
‘Y’
AND PLC.LOOKUP_TYPE = ‘SHIPPING CONTROL’
AND NVL (POH.SHIPPING_CONTROL, ‘SUPPLIER’) =
PLC.LOOKUP_CODE
AND PLL.OUTSOURCED_ASSEMBLY <> 1
AND PLL.PAYMENT_TYPE IS NULL
AND DECODE (
NVL (POL.CLM_OPTION_INDICATOR, ‘Y’),
‘Y’,
‘Y’,
‘B’,
‘Y’,
‘O’,
DECODE (NVL (POL.CLM_EXERCISED_FLAG, ‘N’),
‘Y’, ‘Y’,
‘N’)
) = ‘Y’
AND NVL (POL.CLM_INFO_FLAG, ‘N’) = ‘N’
AND NVL (POH.STYLE_ID, 1) = PDSH.STYLE_ID(+)
AND PDSH.STATUS(+) = ‘ACTIVE’) QRSLT
WHERE (due_date BETWEEN SYSDATE – 365 AND SYSDATE + 365
AND VENDOR_SITE_ID IN
(SELECT VENDOR_SITE_ID
FROM PO_VENDOR_SITES_ALL
WHERE 1 = 1 AND VENDOR_ID = :1 AND STATUS = ‘A’)
AND CLM_FLAG IN (‘N’))
ORDER BY DUE_DATE ASC
Recent Posts