Saturday, August 27, 2016

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

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect