Oracle Blanket Purchase Agreement Query

SELECT  distinct

  po.segment1 po_num,

  to_date(po.creation_date,'DD-MON-RRRR') po_date,

  po.type_lookup_code ,decode(po.type_lookup_code,'BLANKET',null,nvl(reqh.type,'Material Competitive Bidding')) type,

  /* nvl(reqh.description,po.COMMENTS) description,requestor.full_name requestor, */

  po.currency_code,

  decode(po.type_lookup_code,'BLANKET',BLANKET_TOTAL_AMOUNT,PO_INQ_SV.get_po_total(po.type_lookup_code,po.po_header_id,null)) PO_Total,

  -- decode(po.type_lookup_code,'STANDARD',sum(nvl((nvl(podis.quantity_ordered,1)*pol.unit_price),nvl(podis.quantity_ordered,1)*podis.AMOUNT_ORDERED)),BLANKET_TOTAL_AMOUNT) PO_total,

  po.rate,

  poven.vendor_name,

  per.full_name buyer_name

FROM

  po_headers_all po,

  po_vendors poven,

  per_all_people_f per,

  (SELECT * FROM per_all_people_f WHERE SYSDATE BETWEEN effective_start_date AND effective_end_date) requestor,

  po_lines_all pol,

  gl_code_combinations gcc,

  po_line_locations_all poloc,

  po_distributions_all podis,

  (SELECT  

     porh.segment1,

     pord.distribution_id,

     porh.creation_date,

     to_person_id requestor_id,

     porh.ATTRIBUTE_CATEGORY type,

     porh.DESCRIPTION

   FROM  

     po_requisition_headers_all porh,

     po_requisition_lines_all porl,

     po_req_distributions_all pord

   WHERE porh.requisition_header_id = porl.requisition_header_id

     AND porl.requisition_line_id = pord.requisition_line_id

     AND (porh.cancel_flag <> 'Y' OR porh.cancel_flag IS NULL)

     AND (porl.cancel_flag <> 'Y' OR porl.cancel_flag IS NULL)

   ) reqh

WHERE  1=1

  AND po.po_header_id = pol.po_header_id

  AND po.po_header_id = poloc.po_header_id

  AND per.person_id = po.agent_id

  AND poven.vendor_id = po.vendor_id

  -- AND po.po_header_id = podis.po_header_id

  AND pol.po_line_id = poloc.po_line_id

  AND pol.po_line_id  = podis.po_line_id (+)

  AND reqh.distribution_id(+) = podis.req_distribution_id

  AND gcc.code_combination_id = podis.code_combination_id

  AND (pol.cancel_flag <> 'Y' OR pol.cancel_flag IS NULL)

  AND (poloc.cancel_flag <> 'Y' OR poloc.cancel_flag IS NULL)

  AND (po.cancel_flag <> 'Y' OR po.cancel_flag IS NULL)

  -- and nvl(pol.closed_code, 'OPEN') = 'OPEN'

  AND SYSDATE BETWEEN per.effective_start_date AND  per.effective_end_date

  AND requestor.person_id(+) = requestor_id

  and po.creation_date between  '01-Jan-2016' and '31-dec-2016'

  and pol.FROM_HEADER_ID  is null

 

UNION ALL

SELECT unique

  po.segment1 po_num,

  to_date(po.creation_date,'DD-MON-RRRR') po_date,

  po.type_lookup_code,--nvl(po.COMMENTS,ITEM_DESCRIPTION) descr,

  null,

  po.currency_code,

  BLANKET_TOTAL_AMOUNT,

  po.rate,

  poven.vendor_name,

  per.full_name buyer_name

FROM

  po_headers_all po,

  po_vendors poven,

  per_all_people_f per,

  po_lines_all pol

WHERE 1=1

  AND po.po_header_id=pol.po_header_id

  AND per.person_id = po.agent_id

  AND po.type_lookup_code = 'BLANKET'

  AND poven.vendor_id = po.vendor_id

  AND (pol.cancel_flag <> 'Y' OR pol.cancel_flag IS NULL)

  AND (po.cancel_flag <> 'Y' OR po.cancel_flag IS NULL)

  AND SYSDATE BETWEEN per.effective_start_date AND  per.effective_end_date

  and po.creation_date >= '01-Jan-2016'

  -- and po.org_id=86;

ORDER BY 2 asc NULLS LAST