[Home] [Help]
1: PACKAGE BODY PO_NEGOTIATIONS4_PVT AS
2: /* $Header: POXVNG4B.pls 120.23.12020000.3 2013/02/10 13:21:13 vegajula ship $ */
3:
4: TYPE auction_header_id_tbl_type is TABLE OF
5: po_req_split_lines_gt.auction_header_id%type INDEX BY BINARY_INTEGER;
6: TYPE bid_number_tbl_type is TABLE OF
7: po_req_split_lines_gt.bid_number%type INDEX BY BINARY_INTEGER;
8: TYPE bid_line_number_tbl_type is TABLE OF
9: po_req_split_lines_gt.bid_line_number%type INDEX BY BINARY_INTEGER;
3:
4: TYPE auction_header_id_tbl_type is TABLE OF
5: po_req_split_lines_gt.auction_header_id%type INDEX BY BINARY_INTEGER;
6: TYPE bid_number_tbl_type is TABLE OF
7: po_req_split_lines_gt.bid_number%type INDEX BY BINARY_INTEGER;
8: TYPE bid_line_number_tbl_type is TABLE OF
9: po_req_split_lines_gt.bid_line_number%type INDEX BY BINARY_INTEGER;
10: TYPE requisition_header_id_tbl_type is TABLE OF
11: po_req_split_lines_gt.requisition_header_id%type INDEX BY BINARY_INTEGER;
5: po_req_split_lines_gt.auction_header_id%type INDEX BY BINARY_INTEGER;
6: TYPE bid_number_tbl_type is TABLE OF
7: po_req_split_lines_gt.bid_number%type INDEX BY BINARY_INTEGER;
8: TYPE bid_line_number_tbl_type is TABLE OF
9: po_req_split_lines_gt.bid_line_number%type INDEX BY BINARY_INTEGER;
10: TYPE requisition_header_id_tbl_type is TABLE OF
11: po_req_split_lines_gt.requisition_header_id%type INDEX BY BINARY_INTEGER;
12: TYPE requisition_line_id_tbl_type is TABLE OF
13: po_req_split_lines_gt.requisition_line_id%type INDEX BY BINARY_INTEGER;
7: po_req_split_lines_gt.bid_number%type INDEX BY BINARY_INTEGER;
8: TYPE bid_line_number_tbl_type is TABLE OF
9: po_req_split_lines_gt.bid_line_number%type INDEX BY BINARY_INTEGER;
10: TYPE requisition_header_id_tbl_type is TABLE OF
11: po_req_split_lines_gt.requisition_header_id%type INDEX BY BINARY_INTEGER;
12: TYPE requisition_line_id_tbl_type is TABLE OF
13: po_req_split_lines_gt.requisition_line_id%type INDEX BY BINARY_INTEGER;
14: TYPE allocated_qty_tbl_type is TABLE OF
15: po_req_split_lines_gt.allocated_qty%type INDEX BY BINARY_INTEGER;
9: po_req_split_lines_gt.bid_line_number%type INDEX BY BINARY_INTEGER;
10: TYPE requisition_header_id_tbl_type is TABLE OF
11: po_req_split_lines_gt.requisition_header_id%type INDEX BY BINARY_INTEGER;
12: TYPE requisition_line_id_tbl_type is TABLE OF
13: po_req_split_lines_gt.requisition_line_id%type INDEX BY BINARY_INTEGER;
14: TYPE allocated_qty_tbl_type is TABLE OF
15: po_req_split_lines_gt.allocated_qty%type INDEX BY BINARY_INTEGER;
16: TYPE new_req_line_id_tbl_type is TABLE OF
17: po_req_split_lines_gt.new_req_line_id%type INDEX BY BINARY_INTEGER;
11: po_req_split_lines_gt.requisition_header_id%type INDEX BY BINARY_INTEGER;
12: TYPE requisition_line_id_tbl_type is TABLE OF
13: po_req_split_lines_gt.requisition_line_id%type INDEX BY BINARY_INTEGER;
14: TYPE allocated_qty_tbl_type is TABLE OF
15: po_req_split_lines_gt.allocated_qty%type INDEX BY BINARY_INTEGER;
16: TYPE new_req_line_id_tbl_type is TABLE OF
17: po_req_split_lines_gt.new_req_line_id%type INDEX BY BINARY_INTEGER;
18: TYPE new_line_num_tbl_type is TABLE OF
19: po_req_split_lines_gt.new_line_num%type INDEX BY BINARY_INTEGER;
13: po_req_split_lines_gt.requisition_line_id%type INDEX BY BINARY_INTEGER;
14: TYPE allocated_qty_tbl_type is TABLE OF
15: po_req_split_lines_gt.allocated_qty%type INDEX BY BINARY_INTEGER;
16: TYPE new_req_line_id_tbl_type is TABLE OF
17: po_req_split_lines_gt.new_req_line_id%type INDEX BY BINARY_INTEGER;
18: TYPE new_line_num_tbl_type is TABLE OF
19: po_req_split_lines_gt.new_line_num%type INDEX BY BINARY_INTEGER;
20: TYPE totallc_req_line_qty_tbl_type is TABLE OF
21: po_req_split_lines_gt.total_alloc_req_line_qty%type INDEX BY BINARY_INTEGER;
15: po_req_split_lines_gt.allocated_qty%type INDEX BY BINARY_INTEGER;
16: TYPE new_req_line_id_tbl_type is TABLE OF
17: po_req_split_lines_gt.new_req_line_id%type INDEX BY BINARY_INTEGER;
18: TYPE new_line_num_tbl_type is TABLE OF
19: po_req_split_lines_gt.new_line_num%type INDEX BY BINARY_INTEGER;
20: TYPE totallc_req_line_qty_tbl_type is TABLE OF
21: po_req_split_lines_gt.total_alloc_req_line_qty%type INDEX BY BINARY_INTEGER;
22: TYPE requisition_line_qty_tbl_type is TABLE OF
23: po_req_split_lines_gt.requisition_line_qty%type INDEX BY BINARY_INTEGER;
17: po_req_split_lines_gt.new_req_line_id%type INDEX BY BINARY_INTEGER;
18: TYPE new_line_num_tbl_type is TABLE OF
19: po_req_split_lines_gt.new_line_num%type INDEX BY BINARY_INTEGER;
20: TYPE totallc_req_line_qty_tbl_type is TABLE OF
21: po_req_split_lines_gt.total_alloc_req_line_qty%type INDEX BY BINARY_INTEGER;
22: TYPE requisition_line_qty_tbl_type is TABLE OF
23: po_req_split_lines_gt.requisition_line_qty%type INDEX BY BINARY_INTEGER;
24: TYPE min_bid_number_tbl_type is TABLE OF
25: po_req_split_lines_gt.min_bid_number%type INDEX BY BINARY_INTEGER;
19: po_req_split_lines_gt.new_line_num%type INDEX BY BINARY_INTEGER;
20: TYPE totallc_req_line_qty_tbl_type is TABLE OF
21: po_req_split_lines_gt.total_alloc_req_line_qty%type INDEX BY BINARY_INTEGER;
22: TYPE requisition_line_qty_tbl_type is TABLE OF
23: po_req_split_lines_gt.requisition_line_qty%type INDEX BY BINARY_INTEGER;
24: TYPE min_bid_number_tbl_type is TABLE OF
25: po_req_split_lines_gt.min_bid_number%type INDEX BY BINARY_INTEGER;
26: TYPE record_status_tbl_type is TABLE OF
27: po_req_split_lines_gt.record_status%type INDEX BY BINARY_INTEGER;
21: po_req_split_lines_gt.total_alloc_req_line_qty%type INDEX BY BINARY_INTEGER;
22: TYPE requisition_line_qty_tbl_type is TABLE OF
23: po_req_split_lines_gt.requisition_line_qty%type INDEX BY BINARY_INTEGER;
24: TYPE min_bid_number_tbl_type is TABLE OF
25: po_req_split_lines_gt.min_bid_number%type INDEX BY BINARY_INTEGER;
26: TYPE record_status_tbl_type is TABLE OF
27: po_req_split_lines_gt.record_status%type INDEX BY BINARY_INTEGER;
28: TYPE row_id_tbl_type is TABLE OF rowid INDEX BY BINARY_INTEGER;
29: TYPE min_dist_id_tbl_type is TABLE OF
23: po_req_split_lines_gt.requisition_line_qty%type INDEX BY BINARY_INTEGER;
24: TYPE min_bid_number_tbl_type is TABLE OF
25: po_req_split_lines_gt.min_bid_number%type INDEX BY BINARY_INTEGER;
26: TYPE record_status_tbl_type is TABLE OF
27: po_req_split_lines_gt.record_status%type INDEX BY BINARY_INTEGER;
28: TYPE row_id_tbl_type is TABLE OF rowid INDEX BY BINARY_INTEGER;
29: TYPE min_dist_id_tbl_type is TABLE OF
30: po_req_distributions.distribution_id%type INDEX BY BINARY_INTEGER;
31: TYPE org_id_tbl_type is TABLE OF
84: new_line_num_dbg_tbl,
85: totalloc_req_line_qty_dbg_tbl,
86: min_bid_number_dbg_tbl,
87: record_status_dbg_tbl
88: FROM po_req_split_lines_gt prs
89: ORDER BY prs.requisition_header_id,prs.requisition_line_id;
90:
91: FOR l_dbg_index in 1.. requisition_line_id_dbg_tbl.COUNT loop
92: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
126: * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
127: * x_msg_count returns count of messages in the stack.
128: * x_msg_data returns message only if 1 message.
129: *
130: * Possible values for PO_REQ_SPLIT_LINES_GT.record_status:
131: * 'S' - Split;
132: * 'N' - New Line(for the remainder which will go back to pool);
133: * 'C' - Cancelled, finally closed, withdrawn, and Req lines with 0 quantity;
134: * 'E' - Equal Allocation;
200: -- transaction control happens.
201: CURSOR LockReqLines_Cursor IS
202: SELECT prl.requisition_line_id,quantity
203: FROM po_requisition_lines_all prl, --
204: po_req_split_lines_gt prs
205: WHERE prl.requisition_line_id = prs.requisition_line_id
206: FOR UPDATE OF prl.quantity NOWAIT;
207:
208: old_requisition_header_id po_req_split_lines_gt.requisition_header_id%type:=0;
204: po_req_split_lines_gt prs
205: WHERE prl.requisition_line_id = prs.requisition_line_id
206: FOR UPDATE OF prl.quantity NOWAIT;
207:
208: old_requisition_header_id po_req_split_lines_gt.requisition_header_id%type:=0;
209: l_serial_num number;
210: l_line_num_index number;
211: l_req_encumbrance_flag financials_system_parameters.req_encumbrance_flag%type;
212:
245: --
246: -- requisition lines being processed
247: CURSOR req_header_id_csr IS
248: SELECT DISTINCT prs.requisition_header_id
249: FROM po_req_split_lines_gt prs;
250:
251: l_recrt_req_bal VARCHAR2(1); --
252:
253: BEGIN
303: l_progress :='030';
304: l_module := G_MODULE_PREFIX||l_api_name||'.'||'030'||'.';
305: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
306: FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
307: 'before update po_req_split_lines_gt for quantity and
308: status ');
309: END IF;
310:
311: --bug# 2729465 mark finally closed lines also with a status 'C'
311: --bug# 2729465 mark finally closed lines also with a status 'C'
312: --Code impact is minimal when all these lines to be discarded are marked
313: --with a single status.
314: --Also removed an unnecessary join using requisition_header_id
315: UPDATE po_req_split_lines_gt prs
316: SET (prs.requisition_line_qty,
317: prs.record_status)=
318: (SELECT quantity,
319: --decode(cancel_flag,'Y','C',null)
326: l_progress :='031';
327: l_module := G_MODULE_PREFIX||l_api_name||'.'||'031'||'.';
328: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
329: FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
330: 'after update po_req_split_lines_gt for quantity and
331: status; updated '||sql%rowcount||' rows');
332: END IF;
333:
334:
339: l_progress , sqlcode);
340: FND_MSG_PUB.Add;
341: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
342: FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module,
343: 'Exception: update po_req_split_lines_gt for
344: quantity and status ');
345: END IF;
346: RAISE;
347:
362: l_progress :='035';
363: l_module := G_MODULE_PREFIX||l_api_name||'.'||'035'||'.';
364: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
365: FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
366: 'before update po_req_split_lines_gt for withdrawn lines');
367: END IF;
368:
369: -- SQL What:update the withdrawn lines in the temp table as 'C'
370: -- SQL Why :These lines are not processed.
367: END IF;
368:
369: -- SQL What:update the withdrawn lines in the temp table as 'C'
370: -- SQL Why :These lines are not processed.
371: UPDATE po_req_split_lines_gt prs
372: SET prs.record_status='C'
373: WHERE NOT EXISTS
374: (SELECT requisition_line_id
375: FROM po_requisition_lines_all prl --
379: l_progress :='036';
380: l_module := G_MODULE_PREFIX||l_api_name||'.'||'036'||'.';
381: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
382: FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
383: 'after update po_req_split_lines_gt for withdrawn lines;
384: updated '||sql%rowcount||' rows');
385: END IF;
386:
387: EXCEPTION
391: l_progress , sqlcode);
392: FND_MSG_PUB.Add;
393: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
394: FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module,
395: 'Exception: update po_req_split_lines_gt for
396: withdrawn lines ');
397: END IF;
398: RAISE;
399:
410: l_progress :='037';
411: l_module := G_MODULE_PREFIX||l_api_name||'.'||'037'||'.';
412: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
413: FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
414: 'before update po_req_split_lines_gt for Services lines');
415: END IF;
416:
417: -- SQL What: Update Services Lines in the Global Temp Table as 'T'.
418: -- SQL Why : Services Lines are bypassed during the splitting.
416:
417: -- SQL What: Update Services Lines in the Global Temp Table as 'T'.
418: -- SQL Why : Services Lines are bypassed during the splitting.
419: --
420: UPDATE po_req_split_lines_gt PRS
421: SET ( PRS.record_status
422: , PRS.new_req_line_id ) = ( SELECT 'T'
423: , PRL.requisition_line_id
424: FROM po_requisition_lines_all PRL
434: l_progress :='038';
435: l_module := G_MODULE_PREFIX||l_api_name||'.'||'038'||'.';
436: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
437: FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
438: 'after update po_req_split_lines_gt for Services lines;
439: updated '||sql%rowcount||' rows');
440: END IF;
441:
442: EXCEPTION
444: WHEN OTHERS THEN
445: po_message_s.sql_error('Exception of Split_requisitionLines()', l_progress , sqlcode);
446: FND_MSG_PUB.Add;
447: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
448: FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module, 'Exception: update po_req_split_lines_gt for Services lines');
449: END IF;
450: RAISE;
451:
452: END;
462: l_progress :='039';
463: l_module := G_MODULE_PREFIX||l_api_name||'.'||'039'||'.';
464: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
465: FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
466: 'before update po_req_split_lines_gt for CLM lines');
467: END IF;
468:
469: -- SQL What: Update Services Lines in the Global Temp Table as 'T'.
470: -- SQL Why : Services Lines are bypassed during the splitting.
468:
469: -- SQL What: Update Services Lines in the Global Temp Table as 'T'.
470: -- SQL Why : Services Lines are bypassed during the splitting.
471: --
472: UPDATE po_req_split_lines_gt PRS
473: SET ( PRS.record_status
474: , PRS.new_req_line_id ) = ( SELECT 'T'
475: , PRL.requisition_line_id
476: FROM po_requisition_lines_all PRL
488: l_progress :='039';
489: l_module := G_MODULE_PREFIX||l_api_name||'.'||'039'||'.';
490: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
491: FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
492: 'after update po_req_split_lines_gt for CLM lines;
493: updated '||sql%rowcount||' rows');
494: END IF;
495:
496: EXCEPTION
498: WHEN OTHERS THEN
499: po_message_s.sql_error('Exception of Split_requisitionLines()', l_progress , sqlcode);
500: FND_MSG_PUB.Add;
501: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
502: FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module, 'Exception: update po_req_split_lines_gt for CLM lines');
503: END IF;
504: RAISE;
505:
506: END;
527: BULK COLLECT INTO
528: requisition_line_id_tbl,
529: total_alloc_req_line_qty_tbl,
530: min_bid_number_tbl
531: FROM po_req_split_lines_gt
532: WHERE nvl(record_status,'NOVAL') NOT IN ('C','T') --
533: GROUP BY requisition_line_id;
534:
535: l_progress :='041';
562: l_progress :='050';
563: l_module := G_MODULE_PREFIX||l_api_name||'.'||'050'||'.';
564: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
565: FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
566: 'before updating po_req_split_lines_gt with sum of
567: allocated_qty and min of bid_number etc..
568: ');
569: END IF;
570:
573: -- SQL Why :This is required later in the process.
574: -- especially to help bulk processing.
575:
576: FORALL qty_rollup_index in 1.. requisition_line_id_tbl.COUNT
577: UPDATE po_req_split_lines_gt
578: SET min_bid_number= min_bid_number_tbl(qty_rollup_index),
579: total_alloc_req_line_qty=
580: total_alloc_req_line_qty_tbl(qty_rollup_index),
581: record_status =decode(nvl(allocated_qty,0),requisition_line_qty,
590: l_progress :='051';
591: l_module := G_MODULE_PREFIX||l_api_name||'.'||'050'||'.';
592: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
593: FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
594: 'after updating po_req_split_lines_gt with sum of
595: allocated_qty and min of bid_number etc..; updated '
596: || sql%rowcount ||' rows');
597: END IF;
598:
604: l_progress , sqlcode);
605: FND_MSG_PUB.Add;
606: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
607: FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module,
608: 'Exception: updating po_req_split_lines_gt with sum of
609: allocated_qty and min of bid_number etc.. ');
610: END IF;
611: RAISE;
612:
619: l_module := G_MODULE_PREFIX||l_api_name||'.'||'060'||'.';
620: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
621: FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
622: 'before inserting remainder req lines into
623: po_req_split_lines_gt ');
624: END IF;
625:
626: --
627: -- Get the value of profile PO_RECRT_POST_AWRD_AMT_REQ_BAL, i.e.
636: -- For Amount based lines, new req line should be created only if profile
637: -- PO_RECRT_POST_AWRD_AMT_REQ_BAL value is Y.
638: -- Added join conditions with tables po_requisition_lines_all and
639: -- po_line_types_b.
640: INSERT INTO po_req_split_lines_gt
641: ( auction_header_id,
642: bid_number,
643: bid_line_number,
644: requisition_header_id,
660: NULL,
661: prsl.requisition_line_qty,
662: NULL,
663: 'N'
664: FROM po_req_split_lines_gt prsl,
665: po_requisition_lines_all prl,
666: po_line_types_b plt
667: WHERE prsl.total_alloc_req_line_qty < prsl.requisition_line_qty
668: AND prsl.record_status NOT IN ('I','T') --
677: l_module := G_MODULE_PREFIX||l_api_name||'.'||'060'||'.';
678: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
679: FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
680: 'after inserting remainder req lines into
681: po_req_split_lines_gt inserted '||sql%rowcount||' rows');
682: END IF;
683:
684: EXCEPTION
685:
689: FND_MSG_PUB.Add;
690: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
691: FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module,
692: 'Exception: inserting remainder req lines into
693: po_req_split_lines_gt ');
694: END IF;
695: RAISE;
696: END;
697:
713: SET (bid_number,
714: bid_line_number)=
715: (SELECT prs.bid_number,
716: prs.bid_line_number
717: FROM po_req_split_lines_gt prs
718: WHERE prl.requisition_line_id=prs.requisition_line_id
719: AND prs.record_status IN ('E','T') --
720: )
721: WHERE prl.requisition_line_id in
719: AND prs.record_status IN ('E','T') --
720: )
721: WHERE prl.requisition_line_id in
722: (SELECT prs1.requisition_line_id
723: FROM po_req_split_lines_gt prs1
724: WHERE prs1.record_status IN ('E','T') ); --
725:
726: l_progress :='071';
727: l_module := G_MODULE_PREFIX||l_api_name||'.'||'071'||'.';
752: l_progress :='080';
753: l_module := G_MODULE_PREFIX||l_api_name||'.'||'080'||'.';
754: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
755: FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
756: 'before updating po_req_split_lines_gt with max linenum');
757: END IF;
758:
759: --SQL What:update the temp table with the max line number for each
760: -- requisition_header_id
759: --SQL What:update the temp table with the max line number for each
760: -- requisition_header_id
761: --SQL Why :This is required to calculate the line numbers when creating
762: -- the new requisition lines
763: UPDATE po_req_split_lines_gt prs
764: SET prs.new_line_num=
765: (SELECT max(prl.line_num)
766: FROM po_requisition_lines_all prl --
767: WHERE prl.requisition_header_id=prs.requisition_header_id)
770: l_progress :='081';
771: l_module := G_MODULE_PREFIX||l_api_name||'.'||'081'||'.';
772: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
773: FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
774: 'after updating po_req_split_lines_gt with max linenum;
775: Updated '|| sql%rowcount||' rows ');
776: END IF;
777:
778: EXCEPTION
782: l_progress , sqlcode);
783: FND_MSG_PUB.Add;
784: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
785: FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module,
786: 'Exception: updating po_req_split_lines_gt with max
787: line num');
788: END IF;
789: RAISE;
790:
821: bid_number_lnm_tbl,
822: bid_line_number_lnm_tbl,
823: new_line_num_lnm_tbl,
824: row_id_lnm_tbl
825: FROM po_req_split_lines_gt prs
826: WHERE prs.record_status in ('S','N')
827: ORDER BY prs.requisition_header_id,prs.requisition_line_id;
828:
829: l_progress :='091';
916: l_progress :='110';
917: l_module := G_MODULE_PREFIX||l_api_name||'.'||'110'||'.';
918: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
919: FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
920: 'Before: update po_req_split_lines_gt toset new_line_num');
921: END IF;
922:
923: FORALL l_line_num_upd_index in 1.. requisition_line_id_lnm_tbl.COUNT
924: UPDATE po_req_split_lines_gt
920: 'Before: update po_req_split_lines_gt toset new_line_num');
921: END IF;
922:
923: FORALL l_line_num_upd_index in 1.. requisition_line_id_lnm_tbl.COUNT
924: UPDATE po_req_split_lines_gt
925: SET new_line_num=new_line_num_lnm_tbl(l_line_num_upd_index)
926: WHERE rowid=row_id_lnm_tbl(l_line_num_upd_index);
927: l_progress :='111';
928: l_module := G_MODULE_PREFIX||l_api_name||'.'||'110'||'.';
927: l_progress :='111';
928: l_module := G_MODULE_PREFIX||l_api_name||'.'||'110'||'.';
929: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
930: FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
931: 'After: update po_req_split_lines_gt toset new_line_num;
932: Updated '||sql%rowcount||' rows');
933: END IF;
934:
935: EXCEPTION
939: l_progress , sqlcode);
940: FND_MSG_PUB.Add;
941: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
942: FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module,
943: 'Exception: update po_req_split_lines_gt to set
944: new_line_num');
945: END IF;
946: RAISE;
947:
988: requisition_line_qty_rslt_tbl,
989: min_bid_number_rslt_tbl,
990: record_status_rslt_tbl,
991: encumbrance_flag_rslt_tbl -- Bug 4723367
992: FROM po_req_split_lines_gt prs,
993: po_requisition_lines_all prl, --
994: financials_system_parameters fsp -- Bug 4723367
995: -- Bug 5467617: Removed the joins to PO_VENDORS and PO_VENDOR_SITES_ALL
996: -- These are not required anymore because they we used to fetch the
1871: min_dist_id_round_tbl,
1872: sum_req_line_qty_round_tbl,
1873: req_line_qty_round_tbl
1874: FROM po_req_distributions_all prd, --
1875: po_req_split_lines_gt prs
1876: WHERE prd.requisition_line_id = prs.new_req_line_id
1877: AND prs.record_status in ('S','N')
1878: GROUP BY prd.requisition_line_id;
1879:
2661: INTO l_req_org_id
2662: FROM po_requisition_headers_all
2663: WHERE requisition_header_id IN
2664: (SELECT DISTINCT requisition_header_id
2665: FROM po_req_split_lines_gt);
2666:
2667: IF G_FND_DEBUG = 'Y' THEN
2668: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2669: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module,
2700: SELECT prd.distribution_id
2701: BULK COLLECT INTO
2702: l_before_dist_ids_tbl
2703: FROM po_req_distributions prd,
2704: po_req_split_lines_gt prs
2705: WHERE prd.requisition_line_id = prs.requisition_line_id
2706: AND (prs.bid_number = prs.min_bid_number
2707: OR
2708: -- when called from autocreate req modify bid number
2744: SELECT prd.distribution_id
2745: BULK COLLECT INTO
2746: l_after_dist_ids_tbl
2747: FROM po_req_distributions prd,
2748: po_req_split_lines_gt prs
2749: WHERE prd.requisition_line_id = prs.new_req_line_id
2750: AND prs.record_status in ('S','N')
2751: AND nvl(prd.prevent_encumbrance_flag,'N') <> 'Y'
2752: AND nvl(prd.encumbered_flag,'N') ='Y';
3012: BULK COLLECT INTO
3013: sum_new_line_r_tax_tbl,
3014: sum_new_line_nr_tax_tbl,
3015: min_dist_id_tax_tbl
3016: FROM po_req_distributions prd, po_req_split_lines_gt prs
3017: WHERE prd.requisition_line_id = prs.new_req_line_id
3018: AND prs.record_status in ('S','N')
3019: GROUP BY prs.requisition_line_id,
3020: prd.code_combination_id;
3041: BULK COLLECT INTO
3042: req_line_id_tax_tbl,
3043: sum_orig_line_r_tax_tbl,
3044: sum_orig_line_nr_tax_tbl
3045: FROM po_req_distributions prd, po_req_split_lines_gt prs
3046: WHERE prd.requisition_line_id = prs.requisition_line_id
3047: AND (prs.bid_number = prs.min_bid_number OR
3048: -- when called from autocreate req modify bid number would be null
3049: prs.bid_number IS NULL)