SELECT pha.po_Header_id
,pha.segment1 PO_NUMBER
,pha.CLOSED_CODE
,to_char(pha.CREATION_DATE,'DD-MON-YYYY') CREATION_DATE
--,To_char(pha.CREATION_DATE,'DD-MON-YYYY HH24:MI:SS') Creation_Date_Time
,hou.name OPERATING_UNIT
,aps.vendor_id
,aps.segment1 vendor_number
,aps.vendor_name
FROM po_Headers_all pha
,ap_suppliers aps
,hr_operating_units hou
WHERE 1=1
--AND pha.vendor_order_num LIKE '%SA%'
AND pha.vendor_id = aps.vendor_id
AND hou.business_group_id = 81
AND pha.AUTHORIZATION_STATUS = 'APPROVED'
AND pha.org_id = hou.organization_id
AND cancel_flag <> 'Y'
AND pha.APPROVED_DATE BETWEEN fnd_date.canonical_to_date (:P_DT_FRM) AND fnd_date.canonical_to_date (:P_DT_TO)
AND NOT EXISTS
(SELECT 1
FROM rcv_shipment_lines rsl
WHERE PHA.PO_HEADER_ID = RSL.PO_HEADER_ID)
ORDER BY pha.CREATION_DATE DESC;
,pha.segment1 PO_NUMBER
,pha.CLOSED_CODE
,to_char(pha.CREATION_DATE,'DD-MON-YYYY') CREATION_DATE
--,To_char(pha.CREATION_DATE,'DD-MON-YYYY HH24:MI:SS') Creation_Date_Time
,hou.name OPERATING_UNIT
,aps.vendor_id
,aps.segment1 vendor_number
,aps.vendor_name
FROM po_Headers_all pha
,ap_suppliers aps
,hr_operating_units hou
WHERE 1=1
--AND pha.vendor_order_num LIKE '%SA%'
AND pha.vendor_id = aps.vendor_id
AND hou.business_group_id = 81
AND pha.AUTHORIZATION_STATUS = 'APPROVED'
AND pha.org_id = hou.organization_id
AND cancel_flag <> 'Y'
AND pha.APPROVED_DATE BETWEEN fnd_date.canonical_to_date (:P_DT_FRM) AND fnd_date.canonical_to_date (:P_DT_TO)
AND NOT EXISTS
(SELECT 1
FROM rcv_shipment_lines rsl
WHERE PHA.PO_HEADER_ID = RSL.PO_HEADER_ID)
ORDER BY pha.CREATION_DATE DESC;
No comments:
Post a Comment