Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

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

1 comment:

Anonymous said...

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

Post a Comment

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect