[Home] [Help]
276: */
277:
278: /* Bug# 1121317: kagarwal
279: ** Desc: The cursor c1 in launch_req_line_processing() of POXWATCB.pls
280: ** is fetching the requisition_line_id using the po_requisition_lines_inq_v
281: ** view which is a join of more than 20 tables. The cursor has been modified
282: ** to improve performance.
283: */
284:
287: the same and change the cursor c1 to increase the performance
288:
289: cursor c1 is x_req_header_id is a parameter
290: select pol.requisition_line_id
291: from po_requisition_headers poh, po_requisition_lines pol
292: where line_location_id is null AND
293: nvl(pol.cancel_flag,'N') ='N' AND
294: nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
295: nvl(modified_by_agent_flag,'N') ='N' AND
309:
310: cursor c1 is /* x_req_header_id is a parameter*/
311: select pol.requisition_line_id
312: from po_requisition_headers_all poh, --
313: po_requisition_lines pol
314: where x_req_header_id is null AND
315: line_location_id is null AND
316: nvl(pol.cancel_flag,'N') ='N' AND
317: nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
321: poh.requisition_header_id = pol.requisition_header_id
322: union all
323: select pol.requisition_line_id
324: from po_requisition_headers_all poh, --
325: po_requisition_lines pol
326: where x_req_header_id is not null AND
327: poh.requisition_header_id = x_req_header_id AND
328: line_location_id is null AND
329: nvl(pol.cancel_flag,'N') ='N' AND
344: -- is the blanket document id passed into created document workflow
345: -- from PO approval workflow.
346: --sql join: find all the blanket lines of l_consume_req_demand_doc_id,
347: -- equate the bid_number,bid_line_number and auction_header_id
348: -- of these lines to requisition lines from po_requisition_lines.
349: -- Also ensures these lines are not placed on another PO, they are
350: -- still in approved status, not modified by the buyer, source type is
351: -- vendor and not finally closed
352:
352:
353: CURSOR C_ConsumeReqLines is
354: SELECT prl.requisition_line_id
355: FROM po_lines pol,
356: po_requisition_lines prl,
357: po_requisition_headers_all prh --
358: WHERE pol.po_header_id=l_consume_req_demand_doc_id
359: AND prl.auction_header_id = pol.auction_header_id
360: AND prl.bid_line_number = pol.bid_line_number
695: resultout OUT NOCOPY VARCHAR2 ) is
696:
697: x_req_line_id number;
698: x_suggested_buyer_id number;
699: x_suggested_vendor_name po_requisition_lines_all.suggested_vendor_name%type;
700: x_suggested_vendor_location varchar2(240);
701:
702: /* Bug 2577940 The vendor id and vendor site id should also be populated from Req line */
703: x_suggested_vendor_id po_requisition_lines_all.vendor_id%type;
699: x_suggested_vendor_name po_requisition_lines_all.suggested_vendor_name%type;
700: x_suggested_vendor_location varchar2(240);
701:
702: /* Bug 2577940 The vendor id and vendor site id should also be populated from Req line */
703: x_suggested_vendor_id po_requisition_lines_all.vendor_id%type;
704: x_suggested_vendor_site_id po_requisition_lines_all.vendor_site_id%type;
705: /* Bug 2577940 */
706:
707: x_source_doc_type_code varchar2(25);
700: x_suggested_vendor_location varchar2(240);
701:
702: /* Bug 2577940 The vendor id and vendor site id should also be populated from Req line */
703: x_suggested_vendor_id po_requisition_lines_all.vendor_id%type;
704: x_suggested_vendor_site_id po_requisition_lines_all.vendor_site_id%type;
705: /* Bug 2577940 */
706:
707: x_source_doc_type_code varchar2(25);
708: x_source_doc_po_header_id number;
726: x_vendor_site_id number;
727:
728: x_ga_flag varchar2(1) := 'N'; -- FPI GA
729: l_job_id number := null; --
730: l_labor_req_line_id po_requisition_lines_all.labor_req_line_id%TYPE; --
732: begin
733:
734: x_req_line_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,
784: x_suggested_vendor_site_id,
785: l_job_id, --
786: l_labor_req_line_id --
787: from po_requisition_headers_all phs, --
788: po_requisition_lines pls
789: where pls.requisition_line_id = x_req_line_id
790: and phs.requisition_header_id = pls.requisition_header_id;
791:
792:
1044: actid IN NUMBER,
1045: funcmode IN VARCHAR2,
1046: resultout OUT NOCOPY VARCHAR2 ) is
1047:
1048: x_suggested_vendor_name po_requisition_lines_all.suggested_vendor_name%type;
1049: x_suggested_vendor_site varchar2(240);
1050:
1051: /* Bug 2577940 */
1052: x_suggested_vendor_id number;
1050:
1051: /* Bug 2577940 */
1052: x_suggested_vendor_id number;
1053: x_suggested_vendor_site_id number;
1054: x_vendor po_requisition_lines_all.suggested_vendor_name%type;
1055: x_vendor_site varchar2(240);
1056: /* Bug 2577940 */
1057:
1058: x_valid_vendor varchar2(1);
1410: x_supplier_id number;
1411: x_supplier_site_id number;
1412:
1413: x_use_contract_flag varchar2(1);
1414: x_item_currency PO_REQUISITION_LINES_ALL.currency_code%TYPE;
1415:
1416: x_progress varchar2(300) := '000';
1417:
1418: --
2842: c1_pcard_id number;
2843: c1_contract_id number;
2844: c1_deliver_to_location_code hr_locations_all.location_code%type;
2845: c1_dest_org_id number; -- Consigned FPI
2846: c1_dest_type_code po_requisition_lines.destination_type_code%TYPE; -- Consigned FPI
2847: c1_cons_from_supp_flag varchar2(1); -- Consigned FPI
2848: c1_labor_req_line_id number; --
2849:
2850: c2_rowid rowid;
2880: l_last_billing_date po_asl_attributes.last_billing_date%TYPE; -- Consigned FPI
2881: l_cons_billing_cycle po_asl_attributes.consigned_billing_cycle%TYPE; -- Consigned FPI
2882:
2883: c2_dest_org_id number; -- Consigned FPI
2884: c2_dest_type_code po_requisition_lines.destination_type_code%TYPE; -- Consigned FPI
2885: c2_cons_from_supp_flag varchar2(1); -- Consigned FPI
2886:
2887: x_group_id number;
2888: x_first_time_for_this_comb varchar2(5);
2955: ,prl.line_type_id,
2956: prl.purchase_basis
2957: --
2958: from po_wf_candidate_req_lines_temp prlt,
2959: po_requisition_lines prl,
2960: hr_locations_all hrl
2961: where prlt.process_code = 'PENDING'
2962: and prlt.group_id = x_group_id
2963: and prlt.requisition_line_id = prl.requisition_line_id
3000: prl.purchase_basis
3001: --
3002:
3003: from po_wf_candidate_req_lines_temp prlt,
3004: po_requisition_lines prl,
3005: hr_locations_all hrl
3006: where prlt.process_code = 'PENDING'
3007: and prlt.group_id = x_group_id
3008: and prlt.requisition_line_id = prl.requisition_line_id
5626: l_last_billing_date po_asl_attributes.last_billing_date%TYPE; -- Consigned FPI
5627: l_cons_billing_cycle po_asl_attributes.consigned_billing_cycle%TYPE; -- Consigned FPI
5628:
5629: c_dest_org_id number; -- Consigned FPI
5630: c_dest_type_code po_requisition_lines.destination_type_code%TYPE; -- Consigned FPI
5631: c_cons_from_supp_flag varchar2(1); -- Consigned FPI
5632:
5633: x_progress varchar2(300);
5634: x_group_id number;
5683: prl.vendor_contact_id,
5684: prl.destination_organization_id,
5685: prl.destination_type_code
5686: from po_wf_candidate_req_lines_temp prlt,
5687: po_requisition_lines prl
5688: where prlt.process_code = 'PENDING'
5689: and prlt.group_id = x_group_id
5690: and prlt.requisition_header_id = prl.requisition_header_id
5691: and prlt.requisition_line_id = prl.requisition_line_id;
6414: x_source_doc_line_num number;
6415: x_progress varchar2(300) := '000';
6416: x_contract_id_valid number;
6417: x_contract_currency_code varchar2(25);
6418: x_source_currency_code PO_REQUISITION_LINES_ALL.currency_code%TYPE;
6419: l_vendor_site_id PO_HEADERS_ALL.vendor_site_id%TYPE; --
6420: l_base_currency PO_HEADERS_ALL.currency_code%TYPE;
6421: begin
6422:
7238: --SQL WHY: This information is needed for passing dest inv org
7239: -- to get_asl_attributes
7240: SELECT prl.destination_organization_id
7241: INTO l_dest_org_id
7242: FROM po_requisition_lines_all prl
7243: WHERE requisition_line_id = l_req_line_id;
7244:
7245: PO_THIRD_PARTY_STOCK_GRP.Get_Asl_Attributes(
7246: p_api_version => 1.0
7525: -- Delete all expense lines from the temporary table
7526: DELETE FROM po_wf_candidate_req_lines_temp
7527: WHERE requisition_line_id = (
7528: SELECT requisition_line_id
7529: FROM po_requisition_lines
7530: WHERE labor_req_line_id = l_req_line_id);
7531:
7532: EXCEPTION
7533: WHEN OTHERS THEN