Query to Filter PO in oracle apps
select distinct b.CREATION_DATE "PO_CREATION_DATE",B.SEGMENT1
"PO#",e.SEGMENT1
"VENDOR_NUMBER",e.vendor_name,f.VENDOR_SITE_CODE,B.AUTHORIZATION_ST
ATUS,
C.LINE_NUM "PO Line#",k.line_type,C.VENDOR_PRODUCT_NUM "PO Supplier
Item",d.QUANTITY "PO Line Qty",D.UNIT_MEAS_LOOKUP_CODE "PO
UOM",d.QUANTITY_RECEIVED,D.QUANTITY_CANCELLED,
d.QUANTITY_BILLED,g.QUANTITY_DELIVERED,H.ORGANIZATION_CODE,I.LOCATION
_CODE "SHIP TO LOCATION",G.DESTINATION_TYPE_CODE,j.LOCATION_CODE
"DELIVER_TO_LOCATION",
g.DESTINATION_SUBINVENTORY,d.closed_code
from po_headers_all b, po_lines_all c, po_line_locations_all d, ap_suppliers e,
ap_supplier_sites_all f, po_distributions_all g,ORG_ORGANIZATION_DEFINITIONS
h,
hr_locations i, hr_locations j, po_line_types_tl k
where 1=1
and d.RECEIVING_ROUTING_ID=3
and k.LINE_TYPE_ID=c.LINE_TYPE_ID
and
nvl(d.QUANTITY,0)=nvl(d.QUANTITY_RECEIVED,0)+nvl(D.QUANTITY_CANCELLED,
0)
and G.DESTINATION_TYPE_CODE='EXPENSE'
and d.closed_code not in ('CLOSED','CLOSED FOR RECEIVING','FINALLY CLOSED')
and NVL(UPPER(d.CANCEL_FLAG),'N') <> 'Y'
and j.LOCATION_ID=g.DELIVER_TO_LOCATION_ID
and I.LOCATION_ID=D.SHIP_TO_LOCATION_ID
and H.ORGANIZATION_ID=D.SHIP_TO_ORGANIZATION_ID
and g.line_location_id=D.LINE_LOCATION_ID
and g.po_header_id=d.PO_HEADER_ID
and g.po_line_id=d.po_line_id
and f.VENDOR_SITE_ID=b.VENDOR_SITE_ID
and e.vendor_id=b.vendor_id
and d.po_header_id=C.PO_HEADER_ID
and d.po_line_id=c.po_line_id
and c.po_header_id=B.PO_HEADER_ID
and b.AUTHORIZATION_STATUS='APPROVED'
and b.type_lookup_code = 'STANDARD'
and b.CREATION_DATE between '01-DEC-2016' AND sysdate+1
order by b.CREATION_DATE,B.SEGMENT1,C.LINE_NUM;
