[Home] [Help]
PACKAGE BODY: APPS.PON_CLO_RENUMBER_PKG
Source
1 PACKAGE BODY pon_clo_renumber_pkg AS
2 /* $Header: PONCLORNB.pls 120.27.12020000.2 2013/02/09 08:50:06 hvutukur ship $ */
3
4 -----------------------------------------------------------------
5 FUNCTION sort_table(LineArray PO_TBL_VARCHAR100)
6 RETURN PO_TBL_VARCHAR100
7 IS
8 l_sortedArray PO_TBL_VARCHAR100;
9 BEGIN
10
11 SELECT a.column_value line
12 BULK COLLECT INTO l_sortedArray
13 FROM TABLE(LineArray) a
14 WHERE a.column_value IS NOT NULL
15 ORDER BY line;
16
17
18 RETURN l_sortedArray;
19
20 END;
21
22 -----------------------------------------------------------------
23
24 /************************
25 Name : INCREMENT_CLIN_NUM (Only PLSQL Implementation required)
26 Input : ClinNum
27 OutPut: Next Clin num in sequential order
28
29 Algorithm (O(1)).
30 1. covert the input ClinNum to number
31 2. temp = increment ClinNum by 1.
32 3. if temp > 9999 throw exception NO_MORE_LINES_CAN_BE_ADDED
33 4. convert temp to_char, left padding by 0 , with length = 4
34 ***********************/
35 FUNCTION INCREMENT_CLIN_NUMBER (ClinNum IN VARCHAR2)
36 RETURN varchar2
37 IS
38
39 temp VARCHAR2(100);
40 retValue VARCHAR2(100);
41
42 BEGIN
43
44 -- Step 1 : covert the input ClinNum to number
45 begin
46 SELECT To_Number(ClinNum) INTO temp FROM dual;
47 EXCEPTION
48 WHEN Value_Error OR invalid_number THEN
49 RAISE INVALID_CLIN_NUMBER;
50 END;
51
52 -- Step 2 : temp = increment ClinNum by 1.
53 temp := temp + 1;
54
55 -- Step 3 : if temp > 9999 throw exception NO_MORE_LINES_CAN_BE_ADDED
56 IF temp > 9999 THEN
57 RAISE CLIN_NUMBERS_EXHAUSTED;
58 END IF;
59 -- Step 4 : convert temp to_char, left padding by 0 , with length = 4
60 SELECT LPad(To_Char(temp),4,'0') INTO retValue FROM dual;
61
62 -- Return the next Clin number
63
64 RETURN retValue;
65 EXCEPTION
66 WHEN CLIN_NUMBERS_EXHAUSTED THEN
67 Raise_Application_Error (-20900, 'PON_CLIN_NUMBERS_EXHAUSTED');
68
69 WHEN INVALID_CLIN_NUMBER THEN
70 Raise_Application_Error (-20901, 'PON_INVALID_CLIN_NUMBER');
71 END INCREMENT_CLIN_NUMBER;
72
73
74 FUNCTION INCREMENT_ELIN_NUMBER (ElinNum IN VARCHAR2)
75 RETURN varchar2
76 IS
77
78 temp NUMBER;
79 retValue VARCHAR2(3);
80
81 BEGIN
82
83 -- Step 1 : covert the input ClinNum to number
84 begin
85 SELECT ELIN_TO_DECIMAL(ElinNum) INTO temp FROM dual;
86 EXCEPTION
87 WHEN Value_Error OR invalid_number THEN
88 RAISE INVALID_ELIN_NUMBER;
89 END;
90
91 -- Step 2 : temp = increment ClinNum by 1.
92 temp := temp + 1;
93
94 -- Step 3 : if temp > 9999 throw exception NO_MORE_LINES_CAN_BE_ADDED
95 IF temp > 39303 THEN
96 RAISE ELIN_NUMBERS_EXHAUSTED;
97 END IF;
98 -- Step 4 : convert temp to_char, left padding by 0 , with length = 4
99 SELECT DECIMAL_TO_ELIN(temp) INTO retValue FROM dual;
100
101 -- Return the next Clin number
102
103 RETURN retValue;
104 EXCEPTION
105 WHEN ELIN_NUMBERS_EXHAUSTED THEN
106 Raise_Application_Error (-20900, 'PON_ELIN_NUMBERS_EXHAUSTED');
107
108 WHEN INVALID_ELIN_NUMBER THEN
109 Raise_Application_Error (-20901, 'PON_INVALID_ELIN_NUMBER');
110 END INCREMENT_ELIN_NUMBER;
111
112 /*************************************************************************************
113 Name : INCREMENT_INFO_SLIN_NUM ( Both Java and PLSQL implementation required)
114 Input : SlinNum
115 OutPut: Next Clin num in sequential order
116
117 Algorithm(O(1)).
118 1. Store the input slin number in the SlinNum variable
119 2. lastChar = Extract the last Char of the SlinNum
120 3. asciiValue = ascii(lastChar) + 1=> convert char to ascii and increment
121 4. if asciiValue > 57 then asciiValue = 48 and set variable carryOver = true
122 5. nextChar = char(asciiValue) => Char corresponding to the next ascii value
123 6. replace the last char of SlinNum with the nextChar.
124 7. if carryOver = true then
125 a. Second_last_char = extract the second last char of SlinNum
126 b. If second_last_char = '9' return exception "INFO_SLIN_NUM_EXHAUSTED"
127 c. Next_second_last_char = char ( ascii(second_last_char) + 1)
128 d. replace the second last char of SlinNum with the nextChar
129 8. return SlinNum
130
131 Note : Ascii code of 0 = 48 , Ascii Code of 9 = 57
132 Assumption : The input number is a valid info Slin Num
133 ***********************************************************************************/
134
135 FUNCTION INCREMENT_INFO_SLIN_NUMBER (SlinNum IN VARCHAR2)
136 RETURN VARCHAR2
137 IS
138 ----- temporary variables -----
139 l_lastChar VARCHAR2(1);
140 l_asciiValue NUMBER;
141 l_nextChar VARCHAR2(1);
142 l_carryOver BOOLEAN;
143 l_second_last_char VARCHAR2(1);
144 l_next_second_last_char VARCHAR2(1);
145 ret_SlinNum VARCHAR2(100);
146
147 BEGIN
148 ret_SlinNum := SlinNum;
149
150 -- Step 2 : lastChar = Extract the last Char of the SlinNum
151 l_lastChar := SubStr(SlinNum, Length(SlinNum), 1);
152
153 -- Step 3 : asciiValue = ascii(lastChar) + 1=> convert char to ascii and increment
154 l_asciiValue := Ascii(l_lastChar) + 1;
155
156 -- Step 4 : if asciiValue > 57 then asciiValue = 48 and set variable carryOver = true
157 IF (l_asciiValue > 57) THEN
158 l_asciiValue := 48;
159 l_carryOver := TRUE;
160 END IF;
161
162 -- Step 5 : nextChar = char(asciiValue) => Char corresponding to the next ascii value
163 l_nextChar := fnd_global.local_chr(l_asciiValue);
164
165 -- Step 6 : replace the last char of SlinNum with the nextChar.
166
167 ret_SlinNum := SubStr(SlinNum, 1, Length(SlinNum) - 1) || l_nextChar;
168
169 -- Step 7 : if carryOver = true then
170 -- a. Second_last_char = extract the second last char of SlinNum
171 -- b. If second_last_char = '9' return exception "INFO_SLIN_NUM_EXHAUSTED"
172 -- c. Next_second_last_char = char ( ascii(second_last_char) + 1)
173 -- d. replace the second last char of SlinNum with the nextChar
174
175 IF (l_carryOver = TRUE ) THEN
176 l_second_last_char := SubStr(SlinNum, Length(SlinNum) - 1 , 1);
177 IF (l_second_last_char = '9') THEN
178 RAISE INFO_SLIN_NUM_EXHAUSTED;
179 END IF;
180 l_next_second_last_char := fnd_global.local_chr( Ascii(l_second_last_char) + 1 );
181 ret_SlinNum := SubStr(SlinNum, 1, Length(SlinNum) - 2) || l_next_second_last_Char || SubStr(ret_SlinNum, Length(ret_SlinNum) , 1);
182 END IF;
183
184 RETURN ret_SlinNum;
185 EXCEPTION
186 WHEN INFO_SLIN_NUM_EXHAUSTED THEN
187 Raise_Application_Error (-20904, 'INFO_SLIN_NUM_EXHAUSTED');
188 END INCREMENT_INFO_SLIN_NUMBER;
189
190 /***********************************************************************************
191 Name : INCREMENT_PRICED_SLIN_NUM ( Both Java and PLSQL implementation required)
192 Input : SlinNum
193 OutPut: Next Clin num in sequential order
194
195 Algorithm(O(1)).
196 1. Store the input slin number in the SlinNum variable
197 2. lastChar = Extract the last Char of the SlinNum
198 3. asciiValue = ascii(lastChar) + 1=> convert char to ascii and increment
199 4. if asciiValue > 90 then asciiValue = 65 and set variable carryOver = true
200 5. if asciiValue = 73 or asciiValue = 79 then increment asciiValue by 1
201 6. nextChar = char(asciiValue) => Char corresponding to the next ascii value
202 7. replace the last char of SlinNum with the nextChar.
203 8. if carryOver = true then
204 a. Second_last_char = extract the second last char of SlinNum
205 b. If second_last_char = 'Z' return exception "PRICED_SLIN_NUM_EXHAUSTED"
206 c. Next_second_last_char = char ( ascii(second_last_char) + 1)
207 d. replace the second last char of SlinNum with the nextChar
208 9. return SlinNum
209
210 Note : Ascii code of A = 65 , Ascii Code of Z = 90, Ascii Code of I = 73, Ascii Code of O = 79
211 ***********************************************************************************/
212 FUNCTION INCREMENT_PRICED_SLIN_NUMBER (SlinNum IN VARCHAR2)
213 RETURN VARCHAR2
214 IS
215 ----- temporary variables -----
216 l_lastChar VARCHAR2(1);
217 l_asciiValue NUMBER;
218 l_nextChar VARCHAR2(1);
219 l_carryOver BOOLEAN;
220 l_second_last_char VARCHAR2(1);
221 l_next_second_last_char VARCHAR2(1);
222 ret_SlinNum VARCHAR2(100);
223
224 BEGIN
225 ret_SlinNum := SlinNum;
226
227 -- Step 2 : lastChar = Extract the last Char of the SlinNum
228 l_lastChar := SubStr(SlinNum, Length(SlinNum), 1);
229
230 -- Step 3 : asciiValue = ascii(lastChar) + 1 => convert char to ascii and increment
231 l_asciiValue := Ascii(l_lastChar) + 1;
232
233
234 -- Step 4 : if asciiValue > 57 then asciiValue = 48 and set variable carryOver = true
235 IF (l_asciiValue > 90) THEN
236 l_asciiValue := 65;
237 l_carryOver := TRUE;
238 END IF;
239
240 -- Skip the 'I' and 'O'
241 IF (l_asciiValue = 73 OR l_asciiValue = 79) THEN
242 l_asciiValue := l_asciiValue + 1;
243 END IF;
244
245
246
247 -- Step 5 : nextChar = char(asciiValue) => Char corresponding to the next ascii value
248 l_nextChar := fnd_global.local_chr(l_asciiValue) ;
249
250 -- Step 6 : replace the last char of SlinNum with the nextChar.
251
252 ret_SlinNum := SubStr(SlinNum, 1, Length(SlinNum) - 1) || l_nextChar;
253
254 -- Step 7 : if carryOver = true then
255 -- a. Second_last_char = extract the second last char of SlinNum
256 -- b. If second_last_char = '9' return exception "INFO_SLIN_NUM_EXHAUSTED"
257 -- c. Next_second_last_char = char ( ascii(second_last_char) + 1)
258 -- d. replace the second last char of SlinNum with the nextChar
259
260 IF (l_carryOver = TRUE ) THEN
261 l_second_last_char := SubStr(SlinNum, Length(SlinNum) - 1 , 1);
262 IF (l_second_last_char = 'Z') THEN
263 RAISE PRICED_SLIN_NUM_EXHAUSTED;
264 END IF;
265 l_asciiValue := Ascii(l_second_last_char) + 1;
266 IF (l_asciiValue = 73 OR l_asciiValue = 79) THEN
267 l_asciiValue := l_asciiValue + 1;
268 END IF;
269
270 l_next_second_last_char := fnd_global.local_chr(l_asciiValue);
271 ret_SlinNum := SubStr(SlinNum, 1, Length(SlinNum) - 2) || l_next_second_last_Char || SubStr(ret_SlinNum, Length(ret_SlinNum) , 1);
272 END IF;
273
274 RETURN ret_SlinNum;
275 EXCEPTION
276 WHEN PRICED_SLIN_NUM_EXHAUSTED THEN
277 Raise_Application_Error (-20905, 'PRICED_SLIN_NUM_EXHAUSTED');
278 END INCREMENT_PRICED_SLIN_NUMBER;
279
280
281
282 /************************************************************************************
283 Name : NEXT_CLIN_NUM (Only PLSQL Implementation required)
284 Input : Array / PLSQL table of already assigned Clin numbers
285 Output : Next lowest Clin number available.
286
287 Algorithm (O(n)).
288 1. Lets call the input array as ASSIGNED_NUM_ARRAY
289 2. Create a new array ALL_NUMBERS. Initialized as 0001,0002...9999
290 a. This can be done using query :
291 Select LPad(to_char(LEVEL),4,'0') from dual
292 connect by level < 10000
293 3. Maintain ASSIGNED_NUM_ARRAY_PTR = 0 as pointer to first element of ASSIGNED_NUM_ARRAY
294 4. Maintain ALL_NUMBERS_PTR = 0 as pointer to the first element of ALL_NUMBERS.
295 5. While ASSIGNED_NUM_ARRAY_PTR < length(ASSIGNED_NUM_ARRAY_PTR) loop
296
297 7. If ASSIGNED_NUM_ARRAY[ASSIGNED_NUM_ARRAY_PTR] greater than
298 ALL_NUMBERS[ALL_NUMBERS_PTR] then return
299 ALL_NUMBERS[ALL_NUMBERS_PTR] and exit the function as we have found a
300 gap in the numbering.
301
302
303 6. If ASSIGNED_NUM_ARRAY[ASSIGNED_NUM_ARRAY_PTR] equals to
304 ALL_NUMBERS[ALL_NUMBERS_PTR] then increment
305 ASSIGNED_NUM_ARRAY_PTR and ALL_NUMBERS_PTR by 1
306
307 8. End While Loop
308
309 9. If ALL_NUMBERS_PTR < 9999
310 then return ALL_NUMBERS[ALL_NUMBERS_PTR]
311
312 10. Return exception NO_MORE_LINES_CAN_BE_ADDED
313
314 Note: In the while loop, ASSIGNED_NUM_ARRAY[ASSIGNED_NUM_ARRAY_PTR] can never be less than ALL_NUMBERS[ALL_NUMBERS_PTR],
315 since the ALL_NUMBERS array is gapless.
316
317 Assumption - The input Array is already in sorted order.
318 ***************************************************************************************/
319 FUNCTION NEXT_CLIN_NUM (ASSIGNED_NUM_ARRAY PO_TBL_VARCHAR100)
320 return VARCHAR2
321 IS
322
323 ALL_NUMBERS PO_TBL_VARCHAR100;
324 ASSIGNED_NUM_ARRAY_PTR NUMBER;
325 ALL_NUMBERS_PTR NUMBER;
326 SORTED_ASSIGNED_NUM_ARRAY PO_TBL_VARCHAR100;
327
328 BEGIN
329 IF ASSIGNED_NUM_ARRAY.Count > 9999 THEN
330 RAISE INVALID_INPUT_CLIN_ARRAY;
331 END IF;
332
333 SORTED_ASSIGNED_NUM_ARRAY := sort_table(ASSIGNED_NUM_ARRAY);
334
335 --- Step 2 : Initializing the ALL_NUMBERS Array
336 SELECT LPad(To_Char(LEVEL),4,'0')
337 BULK COLLECT INTO ALL_NUMBERS
338 FROM dual
339 CONNECT BY LEVEL <10000;
340
341 --- Step 3 and 4 : Initializing the pointers to 2 arrays
342 ASSIGNED_NUM_ARRAY_PTR := 1;
343 ALL_NUMBERS_PTR := 1;
344
345 --- Step 5 : Loop thru Assigned num array
346
347 WHILE ASSIGNED_NUM_ARRAY_PTR <= SORTED_ASSIGNED_NUM_ARRAY.Count LOOP
348
349 --- Step 7 : If the assigned_num_array has greater number than the all_number array
350 --- then we have have got the least available clin number
351
352 IF SORTED_ASSIGNED_NUM_ARRAY(ASSIGNED_NUM_ARRAY_PTR) > ALL_NUMBERS(ALL_NUMBERS_PTR) THEN
353 RETURN ALL_NUMBERS(ALL_NUMBERS_PTR);
354 END IF;
355
356
357 --- Step 6 : If both the numbers are equal then move to next number in both array's
358 IF SORTED_ASSIGNED_NUM_ARRAY(ASSIGNED_NUM_ARRAY_PTR) = ALL_NUMBERS(ALL_NUMBERS_PTR) THEN
359 ASSIGNED_NUM_ARRAY_PTR := ASSIGNED_NUM_ARRAY_PTR + 1;
360 ALL_NUMBERS_PTR := ALL_NUMBERS_PTR + 1;
361 END IF;
362
363 END LOOP;
364
365 --- Step 8 : if we reached here, then there are no gaps in the assigned_num_array
366 --- We have to get the next available number from the all_numbers.
367
368 IF ALL_NUMBERS_PTR > 9999 THEN
369 RAISE NO_MORE_LINES_CAN_BE_ADDED;
370 END IF;
371
372 RETURN ALL_NUMBERS(ALL_NUMBERS_PTR);
373 EXCEPTION
374 WHEN INVALID_INPUT_CLIN_ARRAY THEN
375 Raise_Application_Error(-20902,'PON_INVALID_INPUT_CLIN_ARRAY');
376
377 WHEN NO_MORE_LINES_CAN_BE_ADDED THEN
378 Raise_Application_Error(-20903,'PON_NO_MORE_LINES_CAN_BE_ADDED');
379 END;
380
381 procedure setProductContext(product IN VARCHAR2 ,
382 lines_tbl_name OUT NOCOPY VARCHAR2,
383 header_col OUT NOCOPY VARCHAR2,
384 lineid_col OUT NOCOPY VARCHAR2,
385 exhibit_col OUT NOCOPY varchar2
386 )
387
388 IS
389
390 BEGIN
391 IF product = 'PON' THEN
392 lines_tbl_name := 'PON_AUCTION_ITEM_PRICES_ALL';
393 header_col := 'AUCTION_HEADER_ID';
394 lineid_col := 'LINE_NUMBER';
395 exhibit_col := 'EXHIBIT_NUMBER';
396 RETURN;
397 END IF;
398 IF product = 'PO' THEN
399 lines_tbl_name := 'PO_LINES_ALL';
400 header_col := 'PO_HEADER_ID';
401 lineid_col := 'PO_LINE_ID';
402 exhibit_col := 'CLM_EXHIBIT_NAME';
403 RETURN;
404 END IF;
405 IF product = 'ICX' THEN
406 lines_tbl_name := 'PO_REQUISITION_LINES_ALL';
407 header_col := 'REQUISITION_HEADER_ID';
408 lineid_col := 'REQUISITION_LINE_ID';
409 exhibit_col := 'CLM_EXHIBIT_NAME';
410 RETURN;
411 END IF;
412 --Unsolicited Lines Project
413 IF product = 'OFFER' THEN
414 lines_tbl_name := 'PON_BID_ITEM_PRICES';
415 header_col := 'BID_NUMBER';
416 lineid_col := 'LINE_NUMBER';
417 exhibit_col := 'EXHIBIT_NUMBER';
418 END IF;
419
420 END setProductContext;
421
422 FUNCTION getLineid(p_product_code IN VARCHAR2,p_Document_ID IN NUMBER,p_ClinNumber IN VARCHAR2)
423 RETURN NUMBER
424 IS
425 lines_tbl_name VARCHAR2(50);
426 doc_id_col VARCHAR2(50);
427 lineid_col VARCHAR2(50);
428 exhibit_col VARCHAR2(50);
429
430 l_line_id_qry VARCHAR2(1000);
431
432 l_Line_id NUMBER := NULL;
433
434 BEGIN
435 setProductContext(p_product_code,lines_tbl_name,doc_id_col,lineid_col,exhibit_col);
436 l_line_id_qry := 'select ' || lineid_col ||' from ' || lines_tbl_name
437 || ' where ' || doc_id_col || ' = ' || P_Document_ID
438 || ' and LINE_NUM_DISPLAY = '''|| p_ClinNumber||'''';
439 EXECUTE IMMEDIATE l_line_id_qry INTO l_Line_id;
440 RETURN l_Line_id;
441 END getLineid;
442
443 FUNCTION NEXT_CLIN_NUM_WRAPPER(p_Document_ID IN NUMBER,
444 p_product_code IN VARCHAR2) RETURN VARCHAR2
445 IS
446 tbl PO_TBL_VARCHAR100;
447 next_clin VARCHAR2(6);
448 lines_tbl_name VARCHAR2(50);
449 doc_id_col VARCHAR2(50);
450 lineid_col VARCHAR2(50);
451 exhibit_col VARCHAR2(50);
452
453 line_num_tbl_qry VARCHAR2(1000);
454 BEGIN
455 setProductContext(p_product_code,lines_tbl_name,doc_id_col,lineid_col,exhibit_col);
456 IF(p_product_code = 'PO' OR p_product_code = 'PON') then
457 line_num_tbl_qry := 'select line_num_display from ' || lines_tbl_name
458 || ' where group_line_id is null and '|| exhibit_col ||' is null and '
459 || doc_id_col ||' = ' || p_Document_ID
460 || ' order by line_num_display';
461 else
462 line_num_tbl_qry := 'select line_num_display from ' || lines_tbl_name
463 || ' where group_line_id is null and ' || doc_id_col || ' = ' || p_Document_ID
464 || ' order by line_num_display';
465 END IF;
466 EXECUTE IMMEDIATE line_num_tbl_qry BULK COLLECT INTO tbl;
467
468 next_clin := pon_clo_renumber_pkg.next_clin_num(tbl);
469 RETURN next_clin;
470 END;
471
472 FUNCTION ELIN_TO_DECIMAL(linNum VARCHAR2) RETURN NUMBER
473 IS
474
475 l_asciiValue NUMBER:=0;
476 temp NUMBER;
477 base_size NUMBER;
478 ret_linNum VARCHAR2(100);
479 c_base32_digits CONSTANT VARCHAR2(34) := '0123456789ABCDEFGHJKLMNPQRSTUVWXYZ';
480
481 BEGIN
482
483 base_size := Length(c_base32_digits);
484 FOR i IN 1 .. Length(linNum) loop
485 temp := InStr(c_base32_digits,SubStr(linNum, i, 1))-1;
486 l_asciiValue:=l_asciiValue+temp*Power(base_size,(Length(linNum)-i));
487 END LOOP;
488
489
490 RETURN l_asciiValue;
491
492
493 END ;
494
495 FUNCTION DECIMAL_TO_ELIN(elin_dec NUMBER) RETURN VARCHAR2
496 IS
497 v_modulo INTEGER;
498 v_temp_int INTEGER := elin_dec;
499 v_temp_val VARCHAR2(256);
500 v_temp_char VARCHAR2(1);
501 c_base32_digits CONSTANT VARCHAR2(34) := '0123456789ABCDEFGHJKLMNPQRSTUVWXYZ';
502
503 BEGIN
504
505 IF ( elin_dec = 0 ) THEN
506 v_temp_val := '0';
507 END IF;
508
509 WHILE ( v_temp_int <> 0 ) LOOP
510 v_modulo := v_temp_int MOD 34;
511 v_temp_char := SUBSTR( c_base32_digits, v_modulo + 1, 1 );
512 v_temp_val := v_temp_char || v_temp_val;
513 v_temp_int := floor(v_temp_int / 34);
514 END LOOP;
515
516 RETURN v_temp_val;
517
518 END ;
519
520 FUNCTION NEXT_ELIN_NUM_WRAPPER2 ( p_Document_ID IN NUMBER, p_product_code IN VARCHAR2, p_exhibit_number IN VARCHAR2) return VARCHAR2
521 IS
522 linNumDisplay VARCHAR2(3);
523 lineNumber NUMBER;
524 exhibit_len NUMBER;
525 lines_tbl_name VARCHAR2(50);
526 doc_id_col VARCHAR2(50);
527 lineid_col VARCHAR2(50);
528 exhibit_col VARCHAR2(50);
529 line_num_tbl_qry VARCHAR2(1000);
530 BEGIN
531
532
533 exhibit_len:=Length(p_exhibit_number);
534 setProductContext(p_product_code,lines_tbl_name,doc_id_col,lineid_col,exhibit_col);
535
536 SELECT Nvl(Max(ROWNUM),0)+1
537 INTO lineNumber
538 FROM
539 (SELECT pon_clo_renumber_pkg.ELIN_TO_DECIMAL(SubStr(line_num_display,exhibit_len+1,4-exhibit_len)) elin_decimal
540 FROM
541 pon_auction_item_prices_all where auction_header_id = p_Document_ID
542 and exhibit_number = p_exhibit_number AND line_num_display IS NOT null order by elin_decimal)
543 WHERE ELIN_DECIMAL=rownum;
544
545
546 IF((exhibit_len=2 AND lineNumber>1155) OR (exhibit_len=1 AND lineNumber>39303)) THEN
547 RAISE ELIN_NUMBERS_EXHAUSTED;
548 END IF;
549
550 linNumDisplay:=DECIMAL_TO_ELIN(lineNumber);
551
552 IF(exhibit_len=1) THEN
553 IF(Length(linNumDisplay)=1) THEN
554 linNumDisplay:='00'||linNumDisplay;
555 ELSIF(Length(linNumDisplay)=2) then
556 linNumDisplay:='0'||linNumDisplay;
557 END IF;
558 ELSIF(exhibit_len=2) THEN
559 IF(Length(linNumDisplay)=1) THEN
560 linNumDisplay:='0'||linNumDisplay;
561 END IF;
562
563 END IF;
564
565 RETURN p_exhibit_number||linNumDisplay;
566
567 EXCEPTION
568 WHEN ELIN_NUMBERS_EXHAUSTED THEN
569 Raise_Application_Error (-20900, 'PON_ELIN_NUMBERS_EXHAUSTED');
570
571 END;
572
573
574
575
576 FUNCTION NEXT_ELIN_NUM_WRAPPER ( p_Document_ID IN NUMBER, p_product_code IN VARCHAR2, p_exhibit_number IN VARCHAR2) return VARCHAR2
577 IS
578 linNumDisplay VARCHAR2(3);
579 lineNumber NUMBER;
580 exhibit_len NUMBER;
581 lines_tbl_name VARCHAR2(50);
582 doc_id_col VARCHAR2(50);
583 lineid_col VARCHAR2(50);
584 exhibit_col VARCHAR2(50);
585 line_num_tbl_qry VARCHAR2(1000);
586 BEGIN
587
588 exhibit_len:=Length(p_exhibit_number);
589
590 setProductContext(p_product_code,lines_tbl_name,doc_id_col,lineid_col,exhibit_col);
591
592 line_num_tbl_qry := 'SELECT Nvl(Max(ROWNUM),0)+1 FROM (SELECT pon_clo_renumber_pkg.ELIN_TO_DECIMAL(SubStr(line_num_display,'
593 || exhibit_len||'+1,4-'||exhibit_len||')) elin_decimal FROM '
594 || lines_tbl_name
595 || ' where ' || doc_id_col || ' = ' || p_Document_ID
596 || ' and '||exhibit_col||' ='''||p_exhibit_number||''''
597 || ' order by elin_decimal) '
598 || ' WHERE ELIN_DECIMAL=rownum';
599
600
601 EXECUTE IMMEDIATE line_num_tbl_qry INTO lineNumber;
602
603 IF((exhibit_len=2 AND lineNumber>1155) OR (exhibit_len=1 AND lineNumber>39303)) THEN
604 RAISE ELIN_NUMBERS_EXHAUSTED;
605 END IF;
606
607 linNumDisplay:=DECIMAL_TO_ELIN(lineNumber);
608
609 IF(exhibit_len=1) THEN
610 IF(Length(linNumDisplay)=1) THEN
611 linNumDisplay:='00'||linNumDisplay;
612 ELSIF(Length(linNumDisplay)=2) then
613 linNumDisplay:='0'||linNumDisplay;
614 END IF;
615 ELSIF(exhibit_len=2) THEN
616 IF(Length(linNumDisplay)=1) THEN
617 linNumDisplay:='0'||linNumDisplay;
618 END IF;
619
620 END IF;
621
622
623 RETURN p_exhibit_number||linNumDisplay;
624
625 EXCEPTION
626 WHEN ELIN_NUMBERS_EXHAUSTED THEN
627 Raise_Application_Error (-20900, 'PON_ELIN_NUMBERS_EXHAUSTED');
628 END;
629
630 /*
631 This function takes table of newly created line numbers in this session.
632 This is to get latest possible next clin number.
633 */
634 FUNCTION NEXT_CLIN_NUM_WRAPPER2(p_Document_ID IN NUMBER,
635 p_product_code IN VARCHAR2,
636 new_lines IN PO_TBL_VARCHAR100) RETURN VARCHAR2
637 IS
638 tbl PO_TBL_VARCHAR100;
639 next_clin VARCHAR2(6);
640 lines_tbl_name VARCHAR2(50);
641 doc_id_col VARCHAR2(50);
642 lineid_col VARCHAR2(50);
643 exhibit_col VARCHAR2(50);
644
645 line_num_tbl_qry VARCHAR2(1000);
646
647 union_table PO_TBL_VARCHAR100;
648 BEGIN
649 setProductContext(p_product_code,lines_tbl_name,doc_id_col,lineid_col,exhibit_col);
650 IF(p_product_code = 'PO' OR p_product_code = 'PON') then
651 line_num_tbl_qry := 'select line_num_display from ' || lines_tbl_name
652 || ' where group_line_id is null and '|| exhibit_col ||' is null and '
653 || doc_id_col || ' = ' || p_Document_ID
654 || ' order by line_num_display';
655 else
656 line_num_tbl_qry := 'select line_num_display from ' || lines_tbl_name
657 || ' where group_line_id is null and ' || doc_id_col || ' = ' || p_Document_ID
658 || ' order by line_num_display';
659 END IF;
660 EXECUTE IMMEDIATE line_num_tbl_qry BULK COLLECT INTO tbl;
661
662 union_table := tbl multiset UNION new_lines;
663
664 next_clin := pon_clo_renumber_pkg.next_clin_num(union_table);
665 RETURN next_clin;
666 END;
667
668
669
670
671 PROCEDURE VALIDATE_CLIN_NUMBER( p_line_id IN NUMBER, p_doc_id IN NUMBER, p_new_value IN varchar2,
672 p_product_code IN VARCHAR2,p_error_message OUT NOCOPY VARCHAR2)
673 IS
674 p_old_value VARCHAR2(100);
675 next_clin VARCHAR2(6);
676 lines_tbl_name VARCHAR2(50);
677 doc_id_col VARCHAR2(50);
678 lineid_col VARCHAR2(50);
679 exhibit_col VARCHAR2(50);
680
681 sel_oldnum_qry VARCHAR2(1000);
682 dup_check_qry VARCHAR2(1000);
683 invalid_clin_num EXCEPTION;
684
685 BEGIN
686 setProductContext(p_product_code,lines_tbl_name,doc_id_col,lineid_col,exhibit_col);
687
688 BEGIN
689 sel_oldnum_qry := 'SELECT line_num_display
690 FROM ' || lines_tbl_name || ' where ' || lineid_col || ' = ' || p_line_id || ' and '
691 || doc_id_col || ' = ' || p_doc_id;
692
693 EXECUTE IMMEDIATE sel_oldnum_qry INTO p_old_value;
694
695 IF(p_old_value = p_new_value) THEN
696 p_error_message := 'N';
697 RETURN;
698 END IF;
699
700 IF(To_Number(p_new_value) > 9999) THEN
701 p_error_message := 'I';
702 RAISE invalid_clin_num;
703 END IF;
704 EXCEPTION
705 WHEN No_Data_Found THEN
706 NULL; -- this means the line is added for the first time
707 END;
708
709 dup_check_qry := 'SELECT ''D''
710 FROM ' || lines_tbl_name || ' where group_line_id is null
711 and line_num_display = :new_num and ' || doc_id_col || ' = :doc_id';
712
713 EXECUTE IMMEDIATE dup_check_qry INTO p_error_message USING p_new_value,p_doc_id;
714
715
716 EXCEPTION
717 WHEN Invalid_Number THEN
718 p_error_message := 'I';
719 WHEN invalid_clin_num THEN
720 p_error_message := 'I';
721 WHEN No_Data_Found THEN
722 p_error_message := 'S';
723 WHEN OTHERS THEN
724 p_error_message := 'I';
725 END;
726
727 /*********************************
728 Name : RenumberAllLines()
729 Input : Document_ID
730 Product_Code,
731 Lines_table_name,
732 doc_id_col -- name of the column which stores the header id in the lines table.
733 Output : Status
734 Exception_message
735 Desc: Can be used when renumbering the entire document, at a time when no lines have been manually renumbered by user. Eg. At the time of autocreate or automatic creation of new docs.
736
737 Algorithm :
738 1. Maintain 1 variable <LastClinNum> having the last Clin number, initialized to "0001"
739 2. Loop Thru all the CLINs for the given document_id
740
741 4. Assign the <LastClinNum> to current clin.
742 5. Initialize 2 variables for line numbers of Info and priced Slins, initialized as
743 a. <LastInfoSlin> = "<LastClinNum>01"
744 b. and
745 c. <LastPricedSlin> = "<LastClinNum>AA"
746 6. Loop thru all the slins
747 7. if the slin is a priced Slin then
748
749 b. assign the <LastPricedSlin> to this slin and call <LastPricedSlin> = INCREMENT_PRICED_SLIN_NUM(<LastPricedSlin>)
750 8. if the slin is an Info Slin,
751
752 b. assign the <LastInfoSlin> to this slin and call <LastInfoSlin> = INCREMENT_INFO_SLIN_NUM(<LastInfoSlin>)
753
754 10. End loop at line 5
755 11. <LastPricedSlin> = INCREMENT_CLIN_NUM(<LastClinNum>)
756 12. End loop at line 2
757 *********************************/
758 PROCEDURE RenumberAllLines (
759 p_Document_ID IN NUMBER,
760 p_product_code IN VARCHAR2,
761 x_status OUT NOCOPY varchar2) IS
762
763 lastClinNum VARCHAR2(10) := '0001';
764 lastInfoSlin VARCHAR2(10) := '000101';
765 lastPricedSlin VARCHAR2(10) := '0001AA';
766
767 TYPE t_refcrsr IS REF CURSOR;
768
769 all_clins t_refcrsr;
770 all_slins t_refcrsr;
771
772
773 l_clin_query VARCHAR2(5000);
774 l_slin_query VARCHAR2(5000);
775 l_updt_query VARCHAR2(5000);
776
777 tmpLineNum VARCHAR2(100);
778 tmpLineId NUMBER;
779 slnLineId NUMBER;
780 slnInfoFlg VARCHAR2(1);
781
782 lines_tbl_name VARCHAR2(50);
783 doc_id_col VARCHAR2(50);
784 lineid_col VARCHAR2(50);
785 exhibit_col VARCHAR2(50);
786
787 BEGIN
788 x_status := NULL;
789 setProductContext(p_product_code,lines_tbl_name,doc_id_col,lineid_col,exhibit_col);
790 -- Step 2 : Loop Thru all the CLINs for the given document_id
791 IF(p_product_code='PON') THEN
792 l_clin_query := 'Select line_num_display, ' || lineid_col || ' from ' || lines_tbl_name ||
793 ' where ' || doc_id_col || ' = ' || p_Document_ID ||
794 ' and '|| exhibit_col ||' is null and '||'group_line_id is null order by line_number ';
795
796 ELSIF(p_product_code = 'PO') THEN
797 l_clin_query := 'Select line_num_display, ' || lineid_col || ' from ' || lines_tbl_name ||
798 ' where ' || doc_id_col || ' = ' || p_Document_ID ||
799 ' and '|| exhibit_col ||' is null '|| 'and group_line_id is null order by line_number ';
800 ELSE
801 l_clin_query := 'Select line_num_display, ' || lineid_col || ' from ' || lines_tbl_name ||
802 ' where ' || doc_id_col || ' = ' || p_Document_ID ||
803 ' and group_line_id is null'
804 || ' and nvl(LINE_DISP_EDITED_FLAG,''N'') = ''N''';
805 END IF;
806 -- -- logme(l_clin_query);
807 OPEN all_clins FOR l_clin_query;
808
809 LOOP
810 FETCH all_clins INTO tmpLineNum, tmpLineId;
811
812 IF all_clins%NOTFOUND THEN
813 EXIT;
814 END IF;
815
816 -- Step 3 : find all the options of this CLIN and update the clm_base line_num to <LastClinNum>
817 /*
818 l_option_query := 'update ' || lines_tbl_name ||
819 ' set clm_base_line_num = ' || lastClinNum ||
820 ' where ' || doc_id_col || ' = ' || Document_ID ||
821 ' and clm_base_line_num = ' || tmpLineId;
822 -- -- logme(l_option_query);
823 -- OPEN all_options FOR l_option_query;
824 --
825 Guess this wont be needed since the group_line_id and clm_base_line_num stores the
826 internal id's and not the display line num
827 */
828
829 -- Step 4 : Assign the <LastClinNum> to current clin.
830
831 l_updt_query := 'update ' || lines_tbl_name ||
832 ' set line_num_display = ''' || lastClinNum || '''' ||
833 ' where ' || doc_id_col || ' = ' || p_Document_ID ||
834 ' and ' || lineid_col || ' = ' || tmpLineId;
835
836 BEGIN
837
838 EXECUTE IMMEDIATE l_updt_query;
839 -- -- logme('SQL count ' || SQL%ROWCOUNT);
840
841 EXCEPTION
842 WHEN OTHERS THEN
843 -- -- logme(SQLERRM);
844 NULL;
845 END;
846
847
848 -- -- logme(l_updt_query);
849 -- OPEN updt_crsr FOR l_updt_query;
850 --close updt_crsr;
851
852 /* -- Step 5 : Initialize 2 variables for line numbers of Info and priced Slins, initialized as
853 a. <LastInfoSlin> = "<LastClinNum>01"
854 b. and
855 c. <LastPricedSlin> = "<LastClinNum>AA"
856 */
857 lastInfoSlin := lastClinNum || '01';
858 lastPricedSlin := lastClinNum || 'AA';
859 -- Step 6 : Loop thru all the slins
860 IF(p_product_code='PON')
861 THEN
862 l_slin_query := 'select ' || lineid_col || ', clm_info_flag FROM ' || lines_tbl_name
863 || ' where group_line_id = ' || tmpLineId
864 || ' and ' || doc_id_col || ' = ' || p_Document_ID
865 || ' order by line_number ';
866 ELSE
867 l_slin_query := 'select ' || lineid_col || ', clm_info_flag FROM ' || lines_tbl_name
868 || ' where group_line_id = ' || tmpLineId
869 || ' and ' || doc_id_col || ' = ' || p_Document_ID
870 || ' and nvl(LINE_DISP_EDITED_FLAG,''N'') = ''N''';
871 END IF;
872
873 -- -- logme(l_slin_query);
874
875 OPEN all_slins FOR l_slin_query;
876 LOOP
877
878 FETCH all_slins INTO slnLineId, slnInfoFlg;
879
880 IF all_slins%NOTFOUND THEN
881 EXIT;
882 END IF;
883
884 -- Step 7 : If the slin is a priced Slin then
885 IF Nvl(slnInfoFlg,'N') = 'N' THEN -- bug 9858984
886 -- Step 8 : assign the <LastPricedSlin> to this slin and call <LastPricedSlin> = INCREMENT_PRICED_SLIN_NUM(<LastPricedSlin>)
887 l_updt_query := 'update ' || lines_tbl_name ||
888 ' set line_num_display = ''' || lastPricedSlin || '''' ||
889 ' where ' || doc_id_col || ' = ' || p_Document_ID ||
890 ' and ' || lineid_col || ' = ' || slnLineId;
891
892 -- -- logme(l_updt_query);
893
894
895 BEGIN
896
897 EXECUTE IMMEDIATE l_updt_query;
898 -- logme('SQL count ' || SQL%ROWCOUNT);
899
900 EXCEPTION
901 WHEN OTHERS THEN
902 -- logme(SQLERRM);
903 NULL;
904 END;
905
906
907 -- OPEN updt_crsr FOR l_updt_query;
908 -- close updt_crsr;
909
910 lastPricedSlin := INCREMENT_PRICED_SLIN_NUMBER(lastPricedSlin);
911
912
913
914 ELSE
915 l_updt_query := 'update ' || lines_tbl_name ||
916 ' set line_num_display = ''' || lastInfoSlin || '''' ||
917 ' where ' || doc_id_col || ' = ' || p_Document_ID ||
918 ' and ' || lineid_col || ' = ' || slnLineId;
919
920 -- -- logme(l_updt_query);
921
922 BEGIN
923
924 EXECUTE IMMEDIATE l_updt_query;
925 -- -- logme('SQL count ' || SQL%ROWCOUNT);
926 EXCEPTION
927 WHEN OTHERS THEN
928 -- -- logme(SQLERRM);
929 NULL;
930
931 END;
932
933
934
935 -- OPEN updt_crsr FOR l_updt_query;
936 -- close updt_crsr;
937
938 lastInfoSlin := INCREMENT_INFO_SLIN_NUMBER(lastInfoSlin);
939
940 END IF;
941 END LOOP;
942 lastClinNum := INCREMENT_CLIN_NUMBER(lastClinNum);
943 END LOOP;
944 x_status :='S';
945 EXCEPTION WHEN OTHERS
946 THEN
947 x_status := 'F';
948 END RenumberAllLines;
949
950 FUNCTION GetNextSlinNumber(p_product_code IN VARCHAR2
951 ,P_Document_ID IN NUMBER
952 ,p_ClinNumber IN VARCHAR2
953 ,p_ClmInfo_Flag IN VARCHAR2)
954 return VARCHAR2
955 IS
956 lines_tbl_name VARCHAR2(50);
957 doc_id_col VARCHAR2(50);
958 lineid_col VARCHAR2(50);
959 exhibit_col VARCHAR2(50);
960
961 l_info_slin_query VARCHAR2(1000);
962 l_price_slin_query VARCHAR2(1000);
963
964 l_Line_id NUMBER;
965
966 l_infoSlinNum VARCHAR2(100);
967 l_PriceSlinNum VARCHAR2(100);
968
969 slins po_tbl_varchar100 := po_tbl_varchar100();
970 newslin VARCHAR2(6);
971
972 l_fnd_debug CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
973 l_pkg_name CONSTANT VARCHAR2(30) := 'PON_CLO_RENUMBER_PKG';
974 l_module_prefix CONSTANT VARCHAR2(50) := 'pon.plsql.' || l_pkg_name || '.';
975 l_module CONSTANT VARCHAR2(32) := 'GetNextSlinNumber';
976 l_message VARCHAR2(1000);
977
978
979
980 BEGIN
981 setProductContext(p_product_code,lines_tbl_name,doc_id_col,lineid_col,exhibit_col);
982
983 -- Get the Line Id of the given CLIN
984 l_Line_id := getLineid(p_product_code,p_Document_ID,p_ClinNumber);
985
986 IF (p_ClmInfo_Flag = 'Y') THEN
987
988 BEGIN
989 -- ltrim( LINE_NUM_DISPLAY,''0123456789'')
990 l_info_slin_query := 'select line_num_display FROM ' || lines_tbl_name
991 || ' where group_line_id = ' || l_Line_id
992 || ' and ' || doc_id_col || ' = ' || P_Document_ID
993 || ' and CLM_INFO_FLAG=''Y''
994 order by line_num_display';
995
996 EXECUTE IMMEDIATE l_info_slin_query BULK COLLECT INTO slins;
997
998 newslin := slins(1);
999 IF(newslin IS NULL) THEN
1000 RETURN (p_ClinNumber||'01');
1001 END IF;
1002
1003 newslin := p_ClinNumber||'01';
1004
1005 FOR i IN 1..slins.Count LOOP
1006
1007 IF(newslin <> slins(i)) THEN
1008 --Dbms_Output.put_line(' got hole ' || newslin);
1009 /*IF(InStr(newslin,'I')<>0 OR InStr(newslin,'O') <> 0) THEN
1010 newslin := pon_clo_renumber_pkg.INCREMENT_PRICED_SLIN_NUMBER(newslin);
1011 CONTINUE;
1012 END IF;*/
1013 RETURN newslin;
1014 END IF;
1015
1016 newslin := pon_clo_renumber_pkg.INCREMENT_INFO_SLIN_NUMBER(newslin);
1017 END LOOP;
1018
1019 RETURN newslin;
1020 -- l_nextNum := pon_clo_renumber_pkg.testIncrementInfoSlinNum(l_infoSlinNum);
1021 -- Dbms_Output.put_line(l_nextNum);
1022
1023 EXCEPTION
1024 WHEN OTHERS THEN
1025 IF (l_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1026 l_message := 'Exception in new slin number logic: document '||P_Document_ID||
1027 SUBSTR(SQLERRM, 1, 50) || ' ' || SQLCODE ;
1028 FND_LOG.string(log_level => FND_LOG.level_statement,
1029 module => l_module_prefix || l_module,
1030 message => l_message);
1031 END IF;
1032
1033 RETURN (p_ClinNumber||'01');
1034 --Dbms_Output.put_line('AA');
1035 END;
1036 ELSE
1037 BEGIN
1038 l_price_slin_query := 'select LINE_NUM_DISPLAY FROM ' || lines_tbl_name
1039 || ' where group_line_id = ' || l_Line_id
1040 || ' and ' || doc_id_col || ' = ' || P_Document_ID
1041 || ' and CLM_INFO_FLAG <> ''Y''
1042 order by line_num_display';
1043
1044
1045 EXECUTE IMMEDIATE l_price_slin_query BULK COLLECT INTO slins;
1046 newslin := slins(1);
1047 IF(newslin IS NULL) THEN
1048 RETURN (p_ClinNumber||'AA');
1049 END IF;
1050
1051 newslin := p_ClinNumber||'AA';
1052
1053 FOR i IN 1..slins.Count LOOP
1054
1055 IF(newslin <> slins(i)) THEN
1056 --Dbms_Output.put_line(' got hole ' || newslin);
1057 /*IF(InStr(newslin,'I')<>0 OR InStr(newslin,'O') <> 0) THEN
1058 newslin := pon_clo_renumber_pkg.INCREMENT_PRICED_SLIN_NUMBER(newslin);
1059 CONTINUE;
1060 END IF;*/
1061 RETURN newslin;
1062 END IF;
1063
1064 newslin := pon_clo_renumber_pkg.INCREMENT_PRICED_SLIN_NUMBER(newslin);
1065 END LOOP;
1066
1067 RETURN newslin;
1068 -- l_nextNum := pon_clo_renumber_pkg.testIncrementInfoSlinNum(l_infoSlinNum);
1069 -- Dbms_Output.put_line(l_nextNum);
1070
1071 EXCEPTION
1072 WHEN OTHERS THEN
1073 IF (l_fnd_debug = 'Y' and FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1074 l_message := 'Exception in new slin number logic: document '||P_Document_ID||
1075 SUBSTR(SQLERRM, 1, 50) || ' ' || SQLCODE ;
1076 FND_LOG.string(log_level => FND_LOG.level_statement,
1077 module => l_module_prefix || l_module,
1078 message => l_message);
1079 END IF;
1080
1081 RETURN (p_ClinNumber||'AA');
1082 --Dbms_Output.put_line('AA');
1083 END;
1084
1085
1086 END IF;
1087
1088 END GetNextSlinNumber;
1089
1090
1091
1092 PROCEDURE RenumberSlinStructure(
1093 p_product_code IN VARCHAR2,
1094 p_Document_ID IN NUMBER,
1095 p_ClinNumber IN VARCHAR2,
1096 x_status OUT NOCOPY varchar2)
1097 IS
1098
1099 lastClinNum VARCHAR2(100) := p_ClinNumber;
1100 lastInfoSlin VARCHAR2(100);
1101 lastPricedSlin VARCHAR2(100);
1102
1103 l_group_line_id NUMBER;
1104
1105 TYPE t_refcrsr IS REF CURSOR;
1106
1107 all_slins t_refcrsr;
1108
1109
1110 l_slin_query VARCHAR2(5000);
1111 l_updt_query VARCHAR2(5000);
1112
1113 slnLineId NUMBER;
1114 slnInfoFlg VARCHAR2(1);
1115
1116 lines_tbl_name VARCHAR2(50);
1117 doc_id_col VARCHAR2(50);
1118 lineid_col VARCHAR2(50);
1119 exhibit_col VARCHAR2(50);
1120
1121 BEGIN
1122 x_status := NULL;
1123 setProductContext(p_product_code,lines_tbl_name,doc_id_col,lineid_col,exhibit_col);
1124 l_group_line_id := getLineid(p_product_code,p_Document_ID,p_ClinNumber);
1125
1126 lastInfoSlin := lastClinNum || '01';
1127 lastPricedSlin := lastClinNum || 'AA';
1128
1129
1130 l_slin_query := 'select ' || lineid_col || ', clm_info_flag FROM ' || lines_tbl_name
1131 || ' where group_line_id = ' || l_group_line_id
1132 || ' and ' || doc_id_col || ' = ' || p_Document_ID
1133 || ' order by ' || lineid_col;
1134 -- || ' and nvl(LINE_DISP_EDITED_FLAG,''N'') = ''N''';
1135
1136
1137 OPEN all_slins FOR l_slin_query;
1138 LOOP
1139
1140 FETCH all_slins INTO slnLineId, slnInfoFlg;
1141
1142 IF all_slins%NOTFOUND THEN
1143 EXIT;
1144 END IF;
1145
1146 -- Step 7 : If the slin is a priced Slin then
1147 IF Nvl(slnInfoFlg,'N') = 'N' THEN
1148 -- Step 8 : assign the <LastPricedSlin> to this slin and call <LastPricedSlin> = INCREMENT_PRICED_SLIN_NUM(<LastPricedSlin>)
1149 l_updt_query := 'update ' || lines_tbl_name ||
1150 ' set line_num_display = ''' || lastPricedSlin || '''' ||
1151 ' where ' || doc_id_col || ' = ' || p_Document_ID ||
1152 ' and ' || lineid_col || ' = ' || slnLineId;
1153
1154 -- -- logme(l_updt_query);
1155
1156
1157 BEGIN
1158
1159 EXECUTE IMMEDIATE l_updt_query;
1160 -- logme('SQL count ' || SQL%ROWCOUNT);
1161
1162 EXCEPTION
1163 WHEN OTHERS THEN
1164 -- logme(SQLERRM);
1165 NULL;
1166 END;
1167
1168
1169 -- OPEN updt_crsr FOR l_updt_query;
1170 -- close updt_crsr;
1171
1172 lastPricedSlin := INCREMENT_PRICED_SLIN_NUMBER(lastPricedSlin);
1173
1174
1175
1176 ELSE
1177 l_updt_query := 'update ' || lines_tbl_name ||
1178 ' set line_num_display = ''' || lastInfoSlin || '''' ||
1179 ' where ' || doc_id_col || ' = ' || p_Document_ID ||
1180 ' and ' || lineid_col || ' = ' || slnLineId;
1181
1182 -- -- logme(l_updt_query);
1183
1184 BEGIN
1185
1186 EXECUTE IMMEDIATE l_updt_query;
1187 -- -- logme('SQL count ' || SQL%ROWCOUNT);
1188 EXCEPTION
1189 WHEN OTHERS THEN
1190 -- -- logme(SQLERRM);
1191 NULL;
1192
1193 END;
1194
1195
1196
1197 -- OPEN updt_crsr FOR l_updt_query;
1198 -- close updt_crsr;
1199
1200 lastInfoSlin := INCREMENT_INFO_SLIN_NUMBER(lastInfoSlin);
1201
1202 END IF;
1203 END LOOP;
1204 x_status := 'S';
1205 EXCEPTION
1206 WHEN OTHERS THEN
1207 x_status := 'F';
1208 RAISE;
1209 END RenumberSlinStructure;
1210
1211 PROCEDURE RenumberSlin(p_auction_header_id IN Number,
1212 p_line_number IN Number,
1213 x_status OUT NOCOPY varchar2 ) is
1214
1215 l_lineNum VARCHAR2(10);
1216 l_clinNumDsp VARCHAR2(10);
1217 l_infoFlag VARCHAR2(1);
1218 l_clinNum Number;
1219 begin
1220
1221 select nvl(clm_info_flag,'N'),group_line_id into l_infoFlag,l_clinNum
1222 from pon_auction_item_prices_all
1223 where auction_header_id = p_auction_header_id
1224 and line_number = p_line_number;
1225
1226 if ( l_clinNum is not null ) then
1227
1228 select line_num_display into l_clinNumDsp
1229 from pon_auction_item_prices_all
1230 where auction_header_id = p_auction_header_id
1231 and line_number = l_clinNum;
1232
1233 l_lineNum := getnextslinnumber('PON',p_auction_header_id,l_clinNumDsp,l_infoFlag);
1234
1235 update pon_auction_item_prices_all
1236 set line_num_display = l_lineNum
1237 where auction_header_id = p_auction_header_id
1238 and line_number = p_line_number;
1239
1240 end if;
1241
1242 x_status := 'S';
1243 exception
1244 when others then
1245 x_status := 'E';
1246
1247 end RenumberSlin;
1248 --CLM_SPLIT
1249 FUNCTION FIRST_INFO_SLIN_NUMBER (CLINNUM IN VARCHAR2) RETURN VARCHAR2 IS
1250
1251 BEGIN
1252 RETURN CLINNUM||'01';
1253 END;
1254 --END
1255
1256 /*************************** Test Functions *****************************************/
1257
1258 FUNCTION testIncrementClinNum(ClinNum IN VARCHAR2) return VARCHAR2 IS
1259
1260 BEGIN
1261 RETURN INCREMENT_CLIN_NUMBER(ClinNum);
1262 END;
1263
1264
1265
1266 FUNCTION testIncrementInfoSlinNum(SlinNum IN VARCHAR2) return VARCHAR2 IS
1267
1268 BEGIN
1269 RETURN INCREMENT_INFO_SLIN_NUMBER(SlinNum);
1270 END;
1271
1272
1273
1274 FUNCTION testIncrementPricedSlinNum(SlinNum IN VARCHAR2) return VARCHAR2 IS
1275
1276 BEGIN
1277 RETURN INCREMENT_PRICED_SLIN_NUMBER(SlinNum);
1278 END;
1279
1280 /* Bug 9883780 - Start */
1281 /* Added the following procedure to regenerate SLINs for Solicitations
1282 during Solicitation to Award Creation Flow. */
1283
1284 PROCEDURE GenerateSlinStructure(
1285 p_product_code IN VARCHAR2,
1286 p_interface_header_id IN NUMBER,
1287 p_clinnumber IN VARCHAR2,
1288 x_status OUT NOCOPY varchar2)
1289 IS
1290
1291 lastClinNum VARCHAR2(100) := p_clinnumber;
1292 lastInfoSlin VARCHAR2(100);
1293 lastPricedSlin VARCHAR2(100);
1294
1295 l_group_line_id NUMBER;
1296
1297 TYPE t_refcrsr IS REF CURSOR;
1298
1299 all_slins t_refcrsr;
1300
1301
1302 l_slin_query VARCHAR2(5000);
1303 l_updt_query VARCHAR2(5000);
1304
1305 slnLineId NUMBER;
1306 slnInfoFlg VARCHAR2(1);
1307
1308 lines_tbl_name VARCHAR2(50);
1309 doc_id_col VARCHAR2(50);
1310 lineid_col VARCHAR2(50);
1311
1312 BEGIN
1313 x_status := NULL;
1314
1315 SELECT INTERFACE_LINE_ID
1316 INTO l_group_line_id
1317 FROM PO_LINES_INTERFACE
1318 WHERE INTERFACE_HEADER_ID = p_interface_header_id AND
1319 LINE_NUM_DISPLAY = p_clinnumber;
1320
1321 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1322 FND_LOG.string(log_level => FND_LOG.level_statement,
1323 module => 'pon.plsql.pon_clo_renumber_pkg',
1324 message => substr('l_group_line_id : '||l_group_line_id, 0, 4000));
1325 END IF;
1326
1327
1328 lastInfoSlin := lastClinNum || '01';
1329 lastPricedSlin := lastClinNum || 'AA';
1330
1331
1332 l_slin_query := 'SELECT INTERFACE_LINE_ID,
1333 CLM_INFO_FLAG
1334 FROM PO_LINES_INTERFACE
1335 WHERE GROUP_LINE_ID = ' || l_group_line_id || ' AND
1336 INTERFACE_HEADER_ID = ' || p_interface_header_id ||
1337 ' ORDER BY INTERFACE_LINE_ID';
1338 OPEN all_slins FOR l_slin_query;
1339 LOOP
1340 FETCH all_slins INTO slnLineId, slnInfoFlg;
1341 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1342 FND_LOG.string(log_level => FND_LOG.level_statement,
1343 module => 'pon.plsql.pon_clo_renumber_pkg',
1344 message => substr('slnLineId : '||slnLineId, 0, 4000));
1345 FND_LOG.string(log_level => FND_LOG.level_statement,
1346 module => 'pon.plsql.pon_clo_renumber_pkg',
1347 message => substr('slnInfoFlg : '||slnInfoFlg, 0, 4000));
1348 END IF;
1349
1350 IF all_slins%NOTFOUND THEN
1351 EXIT;
1352 END IF;
1353 -- If the slin is a priced Slin then
1354 IF Nvl(slnInfoFlg,'N') = 'N' THEN
1355 -- Assign the <LastPricedSlin> to this slin and call <LastPricedSlin> = INCREMENT_PRICED_SLIN_NUM(<LastPricedSlin>)
1356 l_updt_query := ' UPDATE PO_LINES_INTERFACE
1357 SET LINE_NUM_DISPLAY = ''' || lastPricedSlin || '''' ||
1358 ' WHERE INTERFACE_HEADER_ID = ' || p_interface_header_id ||
1359 ' AND INTERFACE_LINE_ID = ' || slnLineId;
1360 BEGIN
1361 EXECUTE IMMEDIATE l_updt_query;
1362 EXCEPTION WHEN OTHERS THEN
1363 NULL;
1364 END;
1365 lastPricedSlin := INCREMENT_PRICED_SLIN_NUMBER(lastPricedSlin);
1366 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1367 FND_LOG.string(log_level => FND_LOG.level_statement,
1368 module => 'pon.plsql.pon_clo_renumber_pkg',
1369 message => substr('lastPricedSlin : '||lastPricedSlin, 0, 4000));
1370 END IF;
1371 ELSE
1372 l_updt_query := ' UPDATE PO_LINES_INTERFACE
1373 SET LINE_NUM_DISPLAY = ''' || lastInfoSlin || '''' ||
1374 ' WHERE INTERFACE_HEADER_ID = ' || p_interface_header_id ||
1375 ' AND INTERFACE_LINE_ID = ' || slnLineId;
1376 BEGIN
1377 EXECUTE IMMEDIATE l_updt_query;
1378 EXCEPTION WHEN OTHERS THEN
1379 NULL;
1380 END;
1381 lastInfoSlin := INCREMENT_INFO_SLIN_NUMBER(lastInfoSlin);
1382 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1383 FND_LOG.string(log_level => FND_LOG.level_statement,
1384 module => 'pon.plsql.pon_clo_renumber_pkg',
1385 message => substr('lastInfoSlin : '||lastInfoSlin, 0, 4000));
1386 END IF;
1387 END IF;
1388 END LOOP;
1389 x_status := 'S';
1390 EXCEPTION WHEN OTHERS THEN
1391 x_status := 'F';
1392 RAISE;
1393 END GenerateSlinStructure;
1394
1395 /* Bug 9883780 - End */
1396
1397 /*PROCEDURE debug_clin_number(err_msg VARCHAR2)
1398 AS PRAGMA autonomous_transaction;
1399 BEGIN
1400 INSERT INTO vhk_dummy VALUES (err_msg);
1401 COMMIT;
1402 END;*/
1403
1404
1405 --Unsolicited Lines Project
1406 FUNCTION NEXT_CLIN_NUM_WRAPPER3(p_bid_number IN NUMBER,
1407 P_auction_id IN NUMBER,
1408 p_product_code IN VARCHAR2) RETURN VARCHAR2
1409 IS
1410 tbl PO_TBL_VARCHAR100;
1411 next_clin VARCHAR2(6);
1412 lines_tbl_name VARCHAR2(50);
1413 doc_id_col VARCHAR2(50);
1414 lineid_col VARCHAR2(50);
1415 exhibit_col VARCHAR2(50);
1416
1417 line_num_tbl_qry VARCHAR2(1000);
1418 BEGIN
1419 setProductContext(p_product_code,lines_tbl_name,doc_id_col,lineid_col,exhibit_col);
1420 line_num_tbl_qry := 'select line_num_display from ' || lines_tbl_name
1421 || ' where group_line_id is null and ' || doc_id_col || ' = ' || p_bid_number
1422 || ' and auction_line_number = -1 ' || ' union ' ||
1423 ' SELECT LPad(To_Char(LEVEL),4,0) FROM dual '||
1424 ' CONNECT BY LEVEL <= (SELECT to_NUMBER(Max(line_num_display)) '||
1425 ' FROM pon_auction_item_prices_all WHERE AUCTION_HEADER_ID = '|| p_auction_id ||
1426 ' AND GROUP_LINE_ID is null and exhibit_number is null)' ;
1427
1428 EXECUTE IMMEDIATE line_num_tbl_qry BULK COLLECT INTO tbl;
1429
1430 /*FOR i IN 1..tbl.Count LOOP
1431 debug_clin_number(tbl(i));
1432 END LOOP; */
1433
1434 next_clin := pon_clo_renumber_pkg.next_clin_num(tbl);
1435 RETURN next_clin;
1436
1437 END NEXT_CLIN_NUM_WRAPPER3;
1438
1439
1440 END PON_CLO_RENUMBER_PKG;