Tuesday, 12 February 2019

Query to get PO created but RECEIPT/GRN not done.

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;

No comments:

Post a Comment