Pending Purchase Orders Report Code

select sysdate REP_DATE,

pv.SEGMENT1 VENDOR_NUM,

ph.segment1 PO_NUM,

PH.REVISION_NUM,

ph.type_lookup_code PO_TYPE,

pol.ITEM_DESCRIPTION,

pol.UNIT_MEAS_LOOKUP_CODE,

pol.UNIT_PRICE,hr.name,

pds.QUANTITY_ORDERED,

PDS.QUANTITY_CANCELLED,

pds.QUANTITY_DELIVERED,

pds.QUANTITY_BILLED,

pds.AMOUNT_BILLED,

pol.CANCEL_DATE,

pds.QUANTITY_ORDERED-pds.QUANTITY_DELIVERED REMANING_QTY,

pv.vendor_name Supplier,

PH.AUTHORIZATION_STATUS STATUS,

PH.CREATION_DATE po_date

from po_headers_all ph,

po_lines_all pol,

po_distributions_all pds,

HR_OPERATING_UNITS hr,

po_vendors pv

where ph.PO_HEADER_ID=pol.PO_HEADER_ID

and ph.PO_HEADER_ID=pds.PO_HEADER_ID

and ph.VENDOR_ID=pv.VENDOR_ID

and hr.ORGANIZATION_ID=ph.ORG_ID

AND PdS.QUANTITY_ORDERED<>PdS.QUANTITY_DELIVERED

AND ph.TYPE_LOOKUP_CODE<>'RFQ'

AND ph.TYPE_LOOKUP_CODE<>'QUOTATION'

AND PH.AUTHORIZATION_STATUS='APPROVED'

and hr.NAME=:org

and trunc(ph.creation_date)=trunc(:dt)

and to_char(ph.creation_date,initcap('mon')||'-'||'yy')=:mon

and trunc(ph.creation_date)  between trunc(:inp) - 7   and trunc(:inp)