tag:blogger.com,1999:blog-3748123311723907043.post2363578622512924931..comments2024-03-28T10:15:27.244+05:30Comments on Oracle Applications: Oracle Blanket Purchase Agreement QueryUnknownnoreply@blogger.comBlogger1125tag:blogger.com,1999:blog-3748123311723907043.post-53937410689635969022020-01-14T23:56:28.991+05:302020-01-14T23:56:28.991+05:30SELECT distinct
po.segment1 po_num,
to_date(p...SELECT distinct<br /> po.segment1 po_num,<br /> to_date(po.creation_date,'DD-MON-RRRR') po_date,<br /> po.type_lookup_code ,decode(po.type_lookup_code,'BLANKET',null,nvl(reqh.type,'Material Competitive Bidding')) type,<br /> /* nvl(reqh.description,po.COMMENTS) description,requestor.full_name requestor, */<br /> po.currency_code,<br /> 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,<br /> -- 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,<br /> po.rate,<br /> poven.vendor_name,<br /> per.full_name buyer_name<br />FROM<br /> po_headers_all po,<br /> po_vendors poven,<br /> per_all_people_f per,<br /> (SELECT * FROM per_all_people_f WHERE SYSDATE BETWEEN effective_start_date AND effective_end_date) requestor,<br /> po_lines_all pol,<br /> gl_code_combinations gcc,<br /> po_line_locations_all poloc,<br /> po_distributions_all podis,<br /> (SELECT <br /> porh.segment1,<br /> pord.distribution_id,<br /> porh.creation_date,<br /> to_person_id requestor_id,<br /> porh.ATTRIBUTE_CATEGORY type,<br /> porh.DESCRIPTION<br /> FROM <br /> po_requisition_headers_all porh,<br /> po_requisition_lines_all porl,<br /> po_req_distributions_all pord<br /> WHERE porh.requisition_header_id = porl.requisition_header_id<br /> AND porl.requisition_line_id = pord.requisition_line_id<br /> AND (porh.cancel_flag <> 'Y' OR porh.cancel_flag IS NULL)<br /> AND (porl.cancel_flag <> 'Y' OR porl.cancel_flag IS NULL)<br /> ) reqh<br />WHERE 1=1<br /> AND po.po_header_id = pol.po_header_id<br /> AND po.po_header_id = poloc.po_header_id<br /> AND per.person_id = po.agent_id<br /> AND poven.vendor_id = po.vendor_id<br /> -- AND po.po_header_id = podis.po_header_id<br /> AND pol.po_line_id = poloc.po_line_id<br /> AND pol.po_line_id = podis.po_line_id (+)<br /> AND reqh.distribution_id(+) = podis.req_distribution_id<br /> AND gcc.code_combination_id = podis.code_combination_id<br /> AND (pol.cancel_flag <> 'Y' OR pol.cancel_flag IS NULL)<br /> AND (poloc.cancel_flag <> 'Y' OR poloc.cancel_flag IS NULL)<br /> AND (po.cancel_flag <> 'Y' OR po.cancel_flag IS NULL)<br /> -- and nvl(pol.closed_code, 'OPEN') = 'OPEN'<br /> AND SYSDATE BETWEEN per.effective_start_date AND per.effective_end_date<br /> AND requestor.person_id(+) = requestor_id<br /> and po.creation_date between '01-Jan-2016' and '31-dec-2016'<br /> and pol.FROM_HEADER_ID is null<br /> <br />UNION ALL<br /><br />SELECT unique<br /> po.segment1 po_num,<br /> to_date(po.creation_date,'DD-MON-RRRR') po_date,<br /> po.type_lookup_code,--nvl(po.COMMENTS,ITEM_DESCRIPTION) descr,<br /> null,<br /> po.currency_code,<br /> BLANKET_TOTAL_AMOUNT,<br /> po.rate,<br /> poven.vendor_name,<br /> per.full_name buyer_name<br />FROM<br /> po_headers_all po,<br /> po_vendors poven,<br /> per_all_people_f per,<br /> po_lines_all pol<br />WHERE 1=1<br /> AND po.po_header_id=pol.po_header_id<br /> AND per.person_id = po.agent_id<br /> AND po.type_lookup_code = 'BLANKET'<br /> AND poven.vendor_id = po.vendor_id<br /> AND (pol.cancel_flag <> 'Y' OR pol.cancel_flag IS NULL)<br /> AND (po.cancel_flag <> 'Y' OR po.cancel_flag IS NULL)<br /> AND SYSDATE BETWEEN per.effective_start_date AND per.effective_end_date<br /> and po.creation_date >= '01-Jan-2016'<br /> -- and po.org_id=86;<br />ORDER BY 2 asc NULLS LASTAnonymousnoreply@blogger.com