[Home] [Help]
293: */
294:
295: /* Bug# 1121317: kagarwal
296: ** Desc: The cursor c1 in launch_req_line_processing() of POXWATCB.pls
297: ** is fetching the requisition_line_id using the po_requisition_lines_inq_v
298: ** view which is a join of more than 20 tables. The cursor has been modified
299: ** to improve performance.
300: */
301:
304: the same and change the cursor c1 to increase the performance
305:
306: cursor c1 is x_req_header_id is a parameter
307: select pol.requisition_line_id
308: from po_requisition_headers poh, po_requisition_lines pol
309: where line_location_id is null AND
310: nvl(pol.cancel_flag,'N') ='N' AND
311: nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
312: nvl(modified_by_agent_flag,'N') ='N' AND
326: /*The first part of this query cause performance hit for bug 10243160. splite the cursor
327: cursor c1 is -- x_req_header_id is a parameter
328: select pol.requisition_line_id
329: from po_requisition_headers_all poh, --
330: po_requisition_lines pol
331: where x_req_header_id is null AND
332: line_location_id is null AND
333: nvl(pol.cancel_flag,'N') ='N' AND
334: nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
338: poh.requisition_header_id = pol.requisition_header_id
339: union all
340: select pol.requisition_line_id
341: from po_requisition_headers_all poh, --
342: po_requisition_lines pol
343: where x_req_header_id is not null AND
344: poh.requisition_header_id = x_req_header_id AND
345: line_location_id is null AND
346: nvl(pol.cancel_flag,'N') ='N' AND
354: --Use c2 only if it needs. so that reduce the overhead for regular autocreate flow.
355: cursor c1 is --c1 is for autocreate flow. x_req_header_id is a parameter
356: select pol.requisition_line_id
357: from po_requisition_headers_all poh, --
358: po_requisition_lines pol
359: where poh.requisition_header_id = x_req_header_id AND
360: line_location_id is null AND
361: nvl(pol.cancel_flag,'N') ='N' AND
362: nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
369: --c2 is for concurrent program flow.
370: cursor c2 is
371: select pol.requisition_line_id
372: from po_requisition_headers_all poh, --
373: po_requisition_lines pol
374: where line_location_id is null AND
375: nvl(pol.cancel_flag,'N') ='N' AND
376: nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
377: nvl(modified_by_agent_flag,'N') ='N' AND
390: -- is the blanket document id passed into created document workflow
391: -- from PO approval workflow.
392: --sql join: find all the blanket lines of l_consume_req_demand_doc_id,
393: -- equate the bid_number,bid_line_number and auction_header_id
394: -- of these lines to requisition lines from po_requisition_lines.
395: -- Also ensures these lines are not placed on another PO, they are
396: -- still in approved status, not modified by the buyer, source type is
397: -- vendor and not finally closed
398:
398:
399: CURSOR C_ConsumeReqLines is
400: SELECT prl.requisition_line_id
401: FROM po_lines pol,
402: po_requisition_lines prl,
403: po_requisition_headers_all prh --
404: WHERE pol.po_header_id=l_consume_req_demand_doc_id
405: AND prl.auction_header_id = pol.auction_header_id
406: AND prl.bid_line_number = pol.bid_line_number
417: --
418: cursor C_CLMReqLines is
419: select pol.requisition_line_id
420: from po_requisition_headers_all poh, --
421: po_requisition_lines pol
422: where poh.requisition_header_id = x_req_header_id AND
423: line_location_id is null AND
424: nvl(pol.cancel_flag,'N') ='N' AND
425: nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
430: pol.GROUP_LINE_ID IS NULL AND
431: pol.CLM_BASE_LINE_NUM is NULL AND
432: Nvl(pol.CLM_INFO_FLAG,'N') = 'N' AND
433: NOT EXISTS
434: (SELECT 'Y' FROM po_requisition_lines_all prl1
435: WHERE prl1.requisition_header_id = poh.requisition_header_id
436: AND prl1.GROUP_LINE_ID = pol.REQUISITION_LINE_ID
437: OR prl1.CLM_BASE_LINE_NUM = pol.REQUISITION_LINE_ID)
438: order by pol.requisition_line_id asc;
884: resultout OUT NOCOPY VARCHAR2 ) is
885:
886: x_req_line_id number;
887: x_suggested_buyer_id number;
888: x_suggested_vendor_name po_requisition_lines_all.suggested_vendor_name%type;
889: x_suggested_vendor_location varchar2(240);
890:
891: /* Bug 2577940 The vendor id and vendor site id should also be populated from Req line */
892: x_suggested_vendor_id po_requisition_lines_all.vendor_id%type;
888: x_suggested_vendor_name po_requisition_lines_all.suggested_vendor_name%type;
889: x_suggested_vendor_location varchar2(240);
890:
891: /* Bug 2577940 The vendor id and vendor site id should also be populated from Req line */
892: x_suggested_vendor_id po_requisition_lines_all.vendor_id%type;
893: x_suggested_vendor_site_id po_requisition_lines_all.vendor_site_id%type;
894: /* Bug 2577940 */
895:
896: x_source_doc_type_code varchar2(25);
889: x_suggested_vendor_location varchar2(240);
890:
891: /* Bug 2577940 The vendor id and vendor site id should also be populated from Req line */
892: x_suggested_vendor_id po_requisition_lines_all.vendor_id%type;
893: x_suggested_vendor_site_id po_requisition_lines_all.vendor_site_id%type;
894: /* Bug 2577940 */
895:
896: x_source_doc_type_code varchar2(25);
897: x_source_doc_po_header_id number;
915: x_vendor_site_id number;
916:
917: x_ga_flag varchar2(1) := 'N'; -- FPI GA
918: l_job_id number := null; --
919: l_labor_req_line_id po_requisition_lines_all.labor_req_line_id%TYPE; --
921:
922: begin
923:
978: l_job_id, --
979: l_labor_req_line_id, --
980: x_federal_flag -- Bug 14314684
981: from po_requisition_headers_all phs, --
982: po_requisition_lines pls
983: where pls.requisition_line_id = x_req_line_id
984: and phs.requisition_header_id = pls.requisition_header_id;
985:
986:
1245: actid IN NUMBER,
1246: funcmode IN VARCHAR2,
1247: resultout OUT NOCOPY VARCHAR2 ) is
1248:
1249: x_suggested_vendor_name po_requisition_lines_all.suggested_vendor_name%type;
1250: x_suggested_vendor_site varchar2(240);
1251:
1252: /* Bug 2577940 */
1253: x_suggested_vendor_id number;
1251:
1252: /* Bug 2577940 */
1253: x_suggested_vendor_id number;
1254: x_suggested_vendor_site_id number;
1255: x_vendor po_requisition_lines_all.suggested_vendor_name%type;
1256: x_vendor_site varchar2(240);
1257: /* Bug 2577940 */
1258:
1259: x_valid_vendor varchar2(1);
1627: x_supplier_id number;
1628: x_supplier_site_id number;
1629:
1630: x_use_contract_flag varchar2(1);
1631: x_item_currency PO_REQUISITION_LINES_ALL.currency_code%TYPE;
1632:
1633: x_progress varchar2(300) := '000';
1634:
1635: --
3085: c1_pcard_id number;
3086: c1_contract_id number;
3087: c1_deliver_to_location_code hr_locations_all.location_code%type;
3088: c1_dest_org_id number; -- Consigned FPI
3089: c1_dest_type_code po_requisition_lines.destination_type_code%TYPE; -- Consigned FPI
3090: c1_cons_from_supp_flag varchar2(1); -- Consigned FPI
3091: c1_labor_req_line_id number; --
3092:
3093: c2_rowid rowid;
3123: l_last_billing_date po_asl_attributes.last_billing_date%TYPE; -- Consigned FPI
3124: l_cons_billing_cycle po_asl_attributes.consigned_billing_cycle%TYPE; -- Consigned FPI
3125:
3126: c2_dest_org_id number; -- Consigned FPI
3127: c2_dest_type_code po_requisition_lines.destination_type_code%TYPE; -- Consigned FPI
3128: c2_cons_from_supp_flag varchar2(1); -- Consigned FPI
3129:
3130: x_group_id number;
3131: x_first_time_for_this_comb varchar2(5);
3200: ,prl.line_type_id,
3201: prl.purchase_basis
3202: --
3203: from po_wf_candidate_req_lines_temp prlt,
3204: po_requisition_lines prl,
3205: hr_locations_all hrl
3206: where prlt.process_code = 'PENDING'
3207: and prlt.group_id = x_group_id
3208: and prlt.requisition_line_id = prl.requisition_line_id
3246: prl.purchase_basis
3247: --
3248:
3249: from po_wf_candidate_req_lines_temp prlt,
3250: po_requisition_lines prl,
3251: hr_locations_all hrl
3252: where prlt.process_code = 'PENDING'
3253: and prlt.group_id = x_group_id
3254: and prlt.requisition_line_id = prl.requisition_line_id
6007: l_last_billing_date po_asl_attributes.last_billing_date%TYPE; -- Consigned FPI
6008: l_cons_billing_cycle po_asl_attributes.consigned_billing_cycle%TYPE; -- Consigned FPI
6009:
6010: c_dest_org_id number; -- Consigned FPI
6011: c_dest_type_code po_requisition_lines.destination_type_code%TYPE; -- Consigned FPI
6012: c_cons_from_supp_flag varchar2(1); -- Consigned FPI
6013:
6014: x_progress varchar2(300);
6015: x_group_id number;
6064: prl.vendor_contact_id,
6065: prl.destination_organization_id,
6066: prl.destination_type_code
6067: from po_wf_candidate_req_lines_temp prlt,
6068: po_requisition_lines prl
6069: where prlt.process_code = 'PENDING'
6070: and prlt.group_id = x_group_id
6071: and prlt.requisition_header_id = prl.requisition_header_id
6072: and prlt.requisition_line_id = prl.requisition_line_id;
6850: x_source_doc_line_num number;
6851: x_progress varchar2(300) := '000';
6852: x_contract_id_valid number;
6853: x_contract_currency_code varchar2(25);
6854: x_source_currency_code PO_REQUISITION_LINES_ALL.currency_code%TYPE;
6855: l_vendor_site_id PO_HEADERS_ALL.vendor_site_id%TYPE; --
6856: l_base_currency PO_HEADERS_ALL.currency_code%TYPE;
6857: x_is_clm_flow VARCHAR2(1);
6858: begin
7691: --SQL WHY: This information is needed for passing dest inv org
7692: -- to get_asl_attributes
7693: SELECT prl.destination_organization_id
7694: INTO l_dest_org_id
7695: FROM po_requisition_lines_all prl
7696: WHERE requisition_line_id = l_req_line_id;
7697:
7698: PO_THIRD_PARTY_STOCK_GRP.Get_Asl_Attributes(
7699: p_api_version => 1.0
7978: -- Delete all expense lines from the temporary table
7979: DELETE FROM po_wf_candidate_req_lines_temp
7980: WHERE requisition_line_id = (
7981: SELECT requisition_line_id
7982: FROM po_requisition_lines
7983: WHERE labor_req_line_id = l_req_line_id);
7984:
7985: EXCEPTION
7986: WHEN OTHERS THEN