[Home] [Help]
PACKAGE BODY: APPS.PON_CLM_CLO_UTIL_PKG
Source
1 PACKAGE BODY pon_clm_clo_util_pkg AS
2 --$Header: PONCLOUTILB.pls 120.31.12020000.3 2013/04/15 05:20:55 sgulkota ship $
3
4 -- Forward declarations of procedures that will be used in copying a clin/slin
5 --and creating an optional line flows
6 procedure copy_line_information(p_auction_header_id IN NUMBER,
7 p_src_line_number IN NUMBER,
8 p_dest_line_number IN NUMBER,
9 p_group_line_id IN NUMBER,
10 p_user_id IN NUMBER );
11
12 procedure copy_line_attr_information(p_auction_header_id IN NUMBER,
13 p_src_line_number IN NUMBER,
14 p_dest_line_number IN NUMBER,
15 p_user_id IN NUMBER );
16
17 procedure copy_price_diff_information(p_auction_header_id IN NUMBER,
18 p_src_line_number IN NUMBER,
19 p_dest_line_number IN NUMBER,
20 p_user_id IN NUMBER );
21
22 procedure copy_shipments_information(p_auction_header_id IN NUMBER,
23 p_src_line_number IN NUMBER,
24 p_dest_line_number IN NUMBER,
25 p_user_id IN NUMBER );
26
27 procedure copy_price_factors_information(p_auction_header_id IN NUMBER,
28 p_src_line_number IN NUMBER,
29 p_dest_line_number IN NUMBER,
30 p_user_id IN NUMBER );
31 procedure copy_payments_information(p_auction_header_id IN NUMBER,
32 p_src_line_number IN NUMBER,
33 p_dest_line_number IN NUMBER,
34 p_user_id IN NUMBER );
35
36
37 -- End: Forward declarations of procedures that will be used in copying a clin/slin
38 --and creating an optional line flows
39
40 procedure copy_line_uda(p_auction_header_id IN NUMBER,
41 p_src_line_number IN NUMBER,
42 p_dest_line_number IN NUMBER);
43
44
45
46 PROCEDURE delete_single_line (
47 x_result OUT NOCOPY VARCHAR2, --1
48 x_error_code OUT NOCOPY VARCHAR2, --2
49 x_error_message OUT NOCOPY VARCHAR2, --3
50 p_auction_header_id IN NUMBER, --4
51 p_line_number IN NUMBER, --5
52 p_delete_linked_option IN VARCHAR2 DEFAULT 'Y', --6
53 x_number_of_lines_deleted IN OUT NOCOPY NUMBER --11
54 ) IS
55
56 l_module_name VARCHAR2 (30);
57
58
59 CURSOR OptionLines IS
60 SELECT
61 line_number,
62 group_type,
63 line_origination_code,
64 org_id,
65 parent_line_number,
66 sub_line_sequence_number,
67 clm_base_line_num,
68 line_num_display,
69 clm_option_indicator
70 FROM pon_auction_item_prices_all
71 WHERE auction_header_id =p_auction_header_id
72 AND (line_number = p_line_number
73 OR clm_base_line_num = p_line_number);
74
75 CURSOR SlinLines (i_parent_line NUMBER) IS
76 SELECT
77 line_number,
78 group_type,
79 line_origination_code,
80 org_id,
81 parent_line_number,
82 sub_line_sequence_number,
83 clm_option_indicator
84 FROM pon_auction_item_prices_all
85 WHERE auction_header_id =p_auction_header_id
86 AND group_line_id = i_parent_line;
87
88 CURSOR CrossLinkedOptionLines(i_base_line_num NUMBER) IS
89 SELECT
90 line_number,
91 group_type,
92 line_origination_code,
93 org_id,
94 parent_line_number,
95 sub_line_sequence_number,
96 clm_option_indicator
97 FROM pon_auction_item_prices_all
98 WHERE auction_header_id =p_auction_header_id
99 AND clm_base_line_num = i_base_line_num;
100
101 CURSOR OptionsForRenum(i_parent_line NUMBER) IS
102 SELECT
103 ROWNUM AS rownumber,
104 auction_header_id,
105 line_number
106 FROM pon_auction_item_prices_all
107 WHERE auction_header_id = p_auction_header_id
108 AND clm_base_line_num = i_parent_line;
109
110
111 total_lines NUMBER;
112 temp NUMBER;
113 opcount NUMBER;
114
115 x_slin_flag VARCHAR2(1);
116 x_option_flag VARCHAR2(1); -- bug 9935917
117 x_itemtype VARCHAR2(7) := 'PONAUCT';
118 x_itemkey VARCHAR2(50);
119 x_sequence NUMBER;
120 x_preparer_id NUMBER;
121 x_username VARCHAR2(100);
122 x_user_display_name VARCHAR2(240);
123 x_auction_number VARCHAR2(20);
124 x_slin_num VARCHAR2(10);
125 x_req_num VARCHAR2(20);
126 x_req_line_num po_requisition_lines_all.line_num_display%TYPE;
127 x_document_number VARCHAR2(20);
128 x_req_hdr_id po_requisition_lines_all.requisition_header_id%TYPE;
129 x_req_line_id po_requisition_lines_all.requisition_line_id%TYPE;
130 x_is_req_backed VARCHAR2(1) := 'N';
131 l_return_status varchar2(1);
132 l_msg_count number;
133 l_msg_data varchar2(200);
134
135
136 BEGIN
137
138 l_module_name := 'delete_single_line';
139 x_result := FND_API.g_ret_sts_success;
140
141 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
142 FND_LOG.string (log_level => FND_LOG.level_procedure,
143 module => 'PON_CLM_CLO_UTIL_PKG.' || l_module_name,
144 message => 'Entered procedure = ' || l_module_name ||
145 ', p_auction_header_id = ' || p_auction_header_id ||
146 ', p_line_number = ' || p_line_number);
147 END IF;
148
149 total_lines:=0;
150
151 IF(p_delete_linked_option <> 'Y') THEN
152 --If linked option lines are not to be deleted, then unlink them first so that they
153 -- will not get picked by the op_line cursor for deletion
154 UPDATE pon_auction_item_prices_all
155 SET clm_base_line_num = null,
156 clm_option_num = null
157 WHERE auction_header_id =p_auction_header_id
158 AND clm_base_line_num = p_line_number;
159 END IF;
160
161 FOR op_line IN OptionLines LOOP
162 temp:=0;
163 FOR sl_line IN SlinLines(op_line.line_number) LOOP
164 IF (p_delete_linked_option = 'Y') THEN
165 FOR cross_linked_options IN CrossLinkedOptionLines(sl_line.line_number) LOOP
166 temp :=0;
167 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
168 FND_LOG.string (log_level => FND_LOG.level_procedure,
169 module => 'PON_CLM_CLO_UTIL_PKG.' || l_module_name,
170 message => 'Inside cross_linked_options loop: Calling delete_single_line for ' || cross_linked_options.line_number);
171 END IF;
172
173 pon_clm_clo_util_pkg.SEND_LINE_DELETED_NOTIF(p_caller=>'PON',
174 p_document_id => p_auction_header_id,
175 p_document_line_id => cross_linked_options.line_number,
176 x_return_status=>l_return_status ,
177 x_msg_count=>l_msg_count,
178 x_msg_data =>l_msg_data );
179
180
181 pon_negotiation_helper_pvt.delete_single_line(
182 x_result => x_result,
183 x_error_code => x_error_code,
184 x_error_message => x_error_message,
185 p_auction_header_id => p_auction_header_id,
186 p_line_number => cross_linked_options.line_number,
187 p_group_type => cross_linked_options.group_type,
188 p_origination_code => cross_linked_options.line_origination_code,
189 p_org_id => cross_linked_options.org_id,
190 p_parent_line_number => cross_linked_options.parent_line_number,
191 p_sub_line_sequence_number => cross_linked_options.SUB_LINE_SEQUENCE_NUMBER,
192 x_number_of_lines_deleted => temp
193 );
194 total_lines := total_lines + temp;
195
196 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
197 FND_LOG.string (log_level => FND_LOG.level_procedure,
198 module => 'PON_CLM_CLO_UTIL_PKG.' || l_module_name,
199 message => 'Deleted cross linked option : ' || cross_linked_options.line_number);
200 END IF;
201 END LOOP;
202 ELSE
203 --Unlink the option lines
204 UPDATE pon_auction_item_prices_all
205 SET clm_base_line_num = null,
206 clm_option_num = null
207 WHERE auction_header_id =p_auction_header_id
208 AND clm_base_line_num = sl_line.line_number;
209
210 END IF;
211
212 temp :=0;
213 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
214 FND_LOG.string (log_level => FND_LOG.level_procedure,
215 module => 'PON_CLM_CLO_UTIL_PKG.' || l_module_name,
216 message => 'Inside slinLines loop: Calling delete_single_line for ' || sl_line.line_number);
217 END IF;
218
219 pon_clm_clo_util_pkg.SEND_LINE_DELETED_NOTIF(p_caller=>'PON',
220 p_document_id => p_auction_header_id,
221 p_document_line_id => sl_line.line_number,
222 x_return_status=>l_return_status ,
223 x_msg_count=>l_msg_count,
224 x_msg_data =>l_msg_data );
225
226 pon_negotiation_helper_pvt.delete_single_line(
227 x_result => x_result,
228 x_error_code => x_error_code,
229 x_error_message => x_error_message,
230 p_auction_header_id => p_auction_header_id,
231 p_line_number => sl_line.line_number,
232 p_group_type => sl_line.group_type,
233 p_origination_code => sl_line.line_origination_code,
234 p_org_id => sl_line.org_id,
235 p_parent_line_number => sl_line.parent_line_number,
236 p_sub_line_sequence_number => sl_line.SUB_LINE_SEQUENCE_NUMBER,
237 x_number_of_lines_deleted => temp
238 );
239 total_lines := total_lines + temp;
240
241 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
242 FND_LOG.string (log_level => FND_LOG.level_procedure,
243 module => 'PON_CLM_CLO_UTIL_PKG.' || l_module_name,
244 message => 'Deleted slin ' || sl_line.line_number);
245 END IF;
246 END LOOP;
247
248 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
249 FND_LOG.string (log_level => FND_LOG.level_procedure,
250 module => 'PON_CLM_CLO_UTIL_PKG.' || l_module_name,
251 message => 'after slinLines loop: Calling delete_single_line for ' || op_line.line_number);
252 END IF;
253
254 pon_clm_clo_util_pkg.SEND_LINE_DELETED_NOTIF(p_caller=>'PON',
255 p_document_id => p_auction_header_id,
256 p_document_line_id => op_line.line_number,
257 x_return_status=>l_return_status ,
258 x_msg_count=>l_msg_count,
259 x_msg_data =>l_msg_data );
260
261
262 pon_negotiation_helper_pvt.delete_single_line(
263 x_result => x_result,
264 x_error_code => x_error_code,
265 x_error_message => x_error_message,
266 p_auction_header_id => p_auction_header_id,
267 p_line_number => op_line.line_number,
268 p_group_type => op_line.group_type,
269 p_origination_code => op_line.line_origination_code,
270 p_org_id => op_line.org_id,
271 p_parent_line_number => op_line.parent_line_number,
272 p_sub_line_sequence_number => op_line.SUB_LINE_SEQUENCE_NUMBER,
273 x_number_of_lines_deleted => temp
274 );
275 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
276 FND_LOG.string (log_level => FND_LOG.level_procedure,
277 module => 'PON_CLM_CLO_UTIL_PKG.' || l_module_name,
278 message => 'Deleted clin/option ' || op_line.line_number);
279 END IF;
280
281 IF ( nvl(op_line.clm_base_line_num,-1) <> -1) THEN
282 opcount:=0;
283 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
284 FND_LOG.string (log_level => FND_LOG.level_procedure,
285 module => 'PON_CLM_CLO_UTIL_PKG.' || l_module_name,
286 message => 'Renumbering options for ' || op_line.clm_base_line_num);
287 END IF;
288
289 FOR options IN OptionsForRenum(op_line.clm_base_line_num) LOOP
290 UPDATE pon_auction_item_prices_all SET clm_option_num = options.rownumber
291 WHERE auction_header_id = p_auction_header_id
292 AND line_number = options.line_number;
293 opcount := opcount + 1;
294 END LOOP;
295
296 IF (opcount = 0 ) THEN
297 UPDATE pon_auction_item_prices_all SET clm_option_indicator = NULL
298 WHERE auction_header_id = p_auction_header_id
299 AND line_number = op_line.clm_base_line_num;
300 END IF;
301 END IF;
302
303 total_lines := total_lines + temp;
304 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
305 FND_LOG.string (log_level => FND_LOG.level_procedure,
306 module => 'PON_CLM_CLO_UTIL_PKG.' || l_module_name,
307 message => 'total_lines ' || total_lines);
308 END IF;
309 END LOOP;
310
311 /*
312 bug 9936466
313 For requisition based clins, we need to find previously deleted slins also
314 for this clin and return them back to pool
315 */
316 IF( x_slin_flag = 'N' and x_option_flag = 'N' AND x_is_req_backed='Y') THEN
317 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
318 FND_LOG.string (log_level => FND_LOG.level_procedure,
319 module => 'PON_CLM_CLO_UTIL_PKG.' || l_module_name,
320 message => 'This is a clin.Checking previously deleted slins/options to return them to req pool ');
321 END IF;
322 FOR rec IN (SELECT requisition_line_id FROM po_requisition_lines_all prl WHERE requisition_header_id = x_req_hdr_id
323 AND (group_line_id = x_req_line_id OR clm_base_line_num = x_req_line_id)
324 AND NOT EXISTS(SELECT 'Y' FROM pon_backing_requisitions
325 WHERE requisition_header_id = x_req_hdr_id
326 AND requisition_line_id = prl.requisition_line_id)) LOOP
327 update po_requisition_lines_all prla --Bug 4001965: use _all
328 set auction_header_id = null,
329 auction_display_number = null,
330 auction_line_number = null,
331 at_sourcing_flag = null, --<REQINPOOL>
332 on_rfq_flag = null, -- bug 5370213
333 --<Begin Bug#: 5203799> We don't want to set the reqs_in_pool_flag to 'Y'
334 --if any of the following conditions are met.
335 reqs_in_pool_flag =
336 (CASE
337 WHEN (nvl(modified_by_agent_flag,'N') = 'Y'
338 or NVL(cancel_flag,'N') IN ('Y', 'I')
339 or NVL(closed_code,'OPEN') = 'FINALLY CLOSED'
340 or source_type_code = 'INVENTORY'
341 or NVL(line_location_id, -999) <> -999
342 or exists
343 (select 'Req Header auth_status is not approved or contractor_status is pending'
344 from po_requisition_headers_all prha
345 where prha.requisition_header_id = prla.requisition_header_id
346 and (NVL(prha.authorization_status,'INCOMPLETE') <> 'APPROVED'
347 or NVL(prha.contractor_status,'NOT_APPLICABLE') = 'PENDING')))
348 THEN null
349 ELSE 'Y'
350 END
351 ), --<End Bug#: 5203799>
352 last_update_date = SYSDATE,
353 last_updated_by = FND_GLOBAL.USER_ID,
354 last_update_login = FND_GLOBAL.LOGIN_ID
355 where requisition_header_id = x_req_hdr_id
356 and requisition_line_id = rec.requisition_line_id;
357
358 END LOOP;
359 END IF;
360
361 x_number_of_lines_deleted := total_lines;
362 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
363 FND_LOG.string (log_level => FND_LOG.level_procedure,
364 module => 'PON_CLM_CLO_UTIL_PKG.' || l_module_name,
365 message => 'Number of lines deleted ' || x_number_of_lines_deleted);
366 END IF;
367
368
369 EXCEPTION
370 WHEN OTHERS THEN
371 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
372 FND_LOG.string (log_level => FND_LOG.level_procedure,
373 module => 'PON_CLM_CLO_UTIL_PKG.' || l_module_name,
374 message => 'Exception' || SQLERRM );
375 END IF;
376 END delete_single_line;
377
378
379
380
381 PROCEDURE copy_and_create_option(p_auction_header_id IN NUMBER,
382 p_src_line_number IN NUMBER,
383 x_new_line_number OUT NOCOPY NUMBER,
384 x_result OUT NOCOPY VARCHAR2,
385 x_error_code OUT NOCOPY VARCHAR2,
386 x_error_message OUT NOCOPY VARCHAR2
387 )
388 AS
389 l_new_line_number NUMBER;
390 l_new_group_line_id NUMBER;
391 l_info_flag VARCHAR2(1);
392 l_is_clin VARCHAR2(1);
393 l_clin_num VARCHAR2(4);
394 l_option_num NUMBER;
395 l_user_id NUMBER;
396 l_line_num_disp VARCHAR2(4);
397 l_module_name VARCHAR2(50);
398
399 BEGIN
400
401 l_module_name := 'copy_and_create_option';
402
403 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
404 FND_LOG.string (log_level => FND_LOG.level_procedure,
405 module => 'PON_CLM_CLO_UTIL_PKG.' || l_module_name,
406 message => 'Entered procedure = ' || l_module_name ||
407 ', p_auction_header_id = ' || p_auction_header_id ||
408 ', p_line_number = ' || p_src_line_number);
409 END IF;
410
411 -- determine the new line number
412 SELECT (Max(line_number) + 1) INTO l_new_line_number FROM pon_auction_item_prices_all
413 WHERE auction_header_id = p_auction_header_id;
414
415 SELECT group_line_id,clm_info_flag,Nvl2(group_line_id,'N','Y'),SubStr(line_num_display,1,4)
416 INTO l_new_group_line_id,l_info_flag,l_is_clin, l_clin_num
417 FROM pon_auction_item_prices_all
418 WHERE auction_header_id = p_auction_header_id
419 AND line_number = p_src_line_number;
420
421 SELECT (Count(clm_option_num) + 1) INTO l_option_num FROM pon_auction_item_prices_all
422 WHERE auction_header_id = p_auction_header_id
423 AND clm_base_line_num = p_src_line_number;
424
425 SELECT fnd_global.user_id INTO l_user_id FROM dual;
426
427
428
429 copy_line_information(p_auction_header_id,
430 p_src_line_number,
431 l_new_line_number,
432 l_new_group_line_id,
433 l_user_id);
434
435 UPDATE pon_auction_item_prices_all
436 SET clm_option_Num = l_option_num,
437 clm_option_indicator = 'O',
438 clm_base_line_num = p_src_line_number
439 WHERE auction_header_id = p_auction_header_id
440 AND line_Number = l_new_line_number;
441
442 UPDATE pon_auction_item_prices_all
443 SET clm_option_indicator = 'B'
444 WHERE auction_header_id = p_auction_header_id
445 AND line_Number = p_src_line_number;
446
447
448 IF (l_is_clin = 'Y') THEN
449
450 l_line_num_disp := pon_clo_renumber_pkg.NEXT_CLIN_NUM_WRAPPER(p_auction_header_id,'PON');
451
452 UPDATE pon_auction_item_prices_all
453 SET line_num_display = l_line_num_disp
454 WHERE auction_header_id = p_auction_header_id
455 AND line_Number = l_new_line_number;
456
457 ELSE
458 pon_clo_renumber_pkg.RenumberSlinStructure('PON',p_auction_header_id,l_clin_num,x_result);
459 END IF;
460
461 IF(Nvl(l_info_flag,'N') = 'N') THEN
462 /*
463 copying attributes
464 */
465
466 copy_line_attr_information(p_auction_header_id ,
467 p_src_line_number,
468 l_new_line_number ,
469 l_user_id );
470
471 copy_price_diff_information(p_auction_header_id ,
472 p_src_line_number,
473 l_new_line_number ,
474 l_user_id );
475
476 copy_shipments_information(p_auction_header_id ,
477 p_src_line_number,
478 l_new_line_number ,
479 l_user_id );
480
481 copy_price_factors_information(p_auction_header_id ,
482 p_src_line_number,
483 l_new_line_number ,
484 l_user_id );
485
486 END IF;
487
488 x_new_line_number := l_new_line_number;
489
490 --Fix for bug 16372466
491 --Commit the changes for them to be available in the java layer.
492 commit;
493
494 END copy_and_create_option;
495
496
497
498 PROCEDURE copy_clin(p_auction_header_id IN NUMBER,
499 p_src_line_number IN NUMBER,
500 x_max_line_number IN OUT NOCOPY NUMBER,
501 x_result OUT NOCOPY VARCHAR2,
502 x_error_code OUT NOCOPY VARCHAR2,
503 x_error_message OUT NOCOPY VARCHAR2
504 )
505 AS
506 TYPE numbers IS TABLE OF number;
507 TYPE flags IS table OF VARCHAR2(1);
508
509 l_line_numbers numbers;
510 l_max_line_number NUMBER;
511 l_info_flags flags;
512 l_user_id NUMBER;
513 l_line_number NUMBER;
514 l_clin_flag VARCHAR2(1);
515 l_clin_num VARCHAR2(4);
516 l_max_clin VARCHAR2(10);
517 --p_auction_header_id NUMBER := 123985;
518 --p_clin_line_number NUMBER := 1;
519
520 -- following fields used for clin numbering after copying the record
521 clin_num_tbl po_tbl_varchar100 := po_tbl_varchar100();
522 next_clin_num VARCHAR2(10);
523
524 base_line_numbers numbers := numbers();
525 copied_base_line_numbers numbers := numbers();
526 len NUMBER;
527 l_clm_base_option pon_auction_item_prices_all.clm_option_indicator%TYPE;
528 l_clm_base_line pon_auction_item_prices_all.clm_base_line_num%TYPE;
529 l_next_option_num pon_auction_item_prices_all.clm_option_num%TYPE;
530 l_is_option_copy VARCHAR2(1);
531 l_new_group_line_id number;
532
533 l_new_clins numbers;
534 l_new_exhibits po_tbl_varchar3;
535 l_module_name VARCHAR2(50);
536
537
538 BEGIN
539
540 l_module_name := 'copy_clin';
541
542 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
543 FND_LOG.string (log_level => FND_LOG.level_procedure,
544 module => 'PON_CLM_CLO_UTIL_PKG.' || l_module_name,
545 message => 'Entered procedure = ' || l_module_name ||
546 ', p_auction_header_id = ' || p_auction_header_id ||
547 ', p_line_number = ' || p_src_line_number);
548 END IF;
549
550 -- Changes for bug 10119899 start. First copy parent line before processing its options/slins.
551 -- following query selects given source line
552 SELECT line_number, clm_info_flag BULK COLLECT INTO l_line_numbers, l_info_flags
553 FROM pon_auction_item_prices_all
554 WHERE auction_header_id = p_auction_header_id
555 AND line_number = p_src_line_number;
556
557 -- we will return x_max_line_number to the calling method so it will be used
558 -- in copying subsequent lines if user had selected more than one line to copy.
559 IF x_max_line_number IS NULL THEN
560 SELECT Max(line_number) INTO l_max_line_number FROM pon_auction_item_prices_all
561 WHERE auction_header_id = p_auction_header_id;
562 ELSE
563 l_max_line_number := x_max_line_number;
564 END IF;
565
566 SELECT fnd_global.user_id INTO l_user_id FROM dual;
567
568 -- check whether source line is a clin and whethere it is an option
569 SELECT Nvl2(group_line_id,'N','Y'),nvl(group_line_id,l_max_line_number+1), decode(Nvl(clm_option_indicator,'N'),'O','Y','N') INTO l_clin_flag,l_new_group_line_id,l_is_option_copy
570 FROM pon_auction_item_prices_all
571 WHERE auction_header_id = p_auction_header_id
572 AND line_number = p_src_line_number;
573
574 IF l_clin_flag = 'Y' THEN
575 SELECT pon_clo_renumber_pkg.NEXT_CLIN_NUM_WRAPPER(p_auction_header_id,'PON') INTO l_clin_num FROM dual;
576
577 ELSIF l_clin_flag = 'N' THEN
578 SELECT SubStr(line_num_display,1,4) INTO l_clin_num FROM pon_auction_item_prices_all
579 WHERE auctioN_header_id = p_auction_header_id
580 AND line_number = p_src_line_number;
581 END IF;
582
583
584 l_line_number := l_max_line_Number + 1; -- calculate line_number for the line to be inserted.
585
586 copy_line_information(p_auction_header_id,
587 l_line_numbers(1),
588 l_line_number,
589 l_new_group_line_id,
590 l_user_id);
591
592
593 -- if current copied line is a base or option then, corresponding option num and base_line_num will have to be updated
594 SELECT clm_option_indicator, clm_base_line_num INTO l_clm_base_option, l_clm_base_line
595 FROM pon_auction_item_prices_all
596 WHERE auction_header_id = p_auction_header_id
597 AND line_number = l_line_number;
598
599 IF (l_clm_base_option = 'B') THEN
600 --if it is a base, then we try to maintain a map between source base line
601 -- and copied base line. This will be used in updating clm_base_line field of copied optional lines.
602 base_line_numbers.extend();
603 copied_base_line_numbers.extend();
604 len := base_line_numbers.Count;
605 base_line_numbers(len) := l_line_numbers(1);
606 copied_base_line_numbers(len) := l_line_number;
607 ELSIF (l_clm_base_option = 'O' AND l_is_option_copy = 'Y') THEN
608 -- if source line is optional line, we only need to update option number.
609 SELECT (Max(clm_option_num) + 1) INTO l_next_option_num FROM pon_auction_item_prices_all
610 WHERE auction_header_id = p_auction_header_id
611 AND clm_base_line_num = l_clm_base_line;
612
613 UPDATE pon_auction_item_prices_all
614 SET clm_option_num = l_next_option_num
615 WHERE auction_header_id = p_auction_header_id
616 AND line_number = l_line_number;
617 END IF;
618
619
620 FND_ATTACHED_DOCUMENTS2_PKG.COPY_ATTACHMENTS (
621 X_from_entity_name => 'PON_AUCTION_ITEM_PRICES_ALL',
622 X_from_pk1_value => to_char(p_auction_header_id),
623 X_from_pk2_value => to_char(l_line_numbers(1)),
624 X_to_entity_name => 'PON_AUCTION_ITEM_PRICES_ALL',
625 X_to_pk1_value => to_char(p_auction_header_id), -- PK1_VALUE
626 X_to_pk2_value => to_char(l_line_number),
627 X_created_by => l_user_id, -- CREATED_BY
628 X_last_update_login => fnd_global.login_id -- LAST_UPDATE_LOGIN
629 );
630
631
632
633 IF(Nvl(l_info_flags(1),'N') = 'N') THEN
634 /*
635 copying attributes
636 */
637
638 copy_line_attr_information(p_auction_header_id ,
639 l_line_numbers(1),
640 l_line_number ,
641 l_user_id );
642
643 copy_price_diff_information(p_auction_header_id ,
644 l_line_numbers(1),
645 l_line_number ,
646 l_user_id );
647
648 copy_shipments_information(p_auction_header_id ,
649 l_line_numbers(1),
650 l_line_number ,
651 l_user_id );
652
653 copy_price_factors_information(p_auction_header_id ,
654 l_line_numbers(1),
655 l_line_number ,
656 l_user_id );
657 copy_payments_information(p_auction_header_id ,
658 l_line_numbers(1),
659 l_line_number ,
660 l_user_id );
661 END IF;
662
663 --END LOOP; -- loop over lines in the clin
664 -- Changes for bug 10119899 end.
665
666 /*******************************************************************/
667
668 -- bug 10119899. After base line is copied now process slins and options
669 -- following query selects sub lines and options for the input source line
670 SELECT line_number, clm_info_flag BULK COLLECT INTO l_line_numbers, l_info_flags
671 FROM pon_auction_item_prices_all
672 WHERE auction_header_id = p_auction_header_id
673 AND (group_line_id = p_src_line_number
674 OR clm_base_line_num = p_src_line_number)
675 ORDER BY line_num_display;
676
677 --l_max_line_Number := l_max_line_Number + 1;
678
679 FOR i IN 1..l_line_numbers.Count LOOP
680 l_line_number := l_max_line_Number + i + 1; -- calculate line_number for the line to be inserted.
681
682 copy_line_information(p_auction_header_id,
683 l_line_numbers(i),
684 l_line_number,
685 l_new_group_line_id,
686 l_user_id);
687
688
689 -- if current copied line is a base or option then, corresponding option num and base_line_num will have to be updated
690 SELECT clm_option_indicator, clm_base_line_num INTO l_clm_base_option, l_clm_base_line
691 FROM pon_auction_item_prices_all
692 WHERE auction_header_id = p_auction_header_id
693 AND line_number = l_line_number;
694
695 IF (l_clm_base_option = 'B') THEN
696 --if it is a base, then we try to maintain a map between source base line
697 -- and copied base line. This will be used in updating clm_base_line field of copied optional lines.
698 base_line_numbers.extend();
699 copied_base_line_numbers.extend();
700 len := base_line_numbers.Count;
701 base_line_numbers(len) := l_line_numbers(i);
702 copied_base_line_numbers(len) := l_line_number;
703 ELSIF (l_clm_base_option = 'O' AND l_is_option_copy <> 'Y') THEN
704 -- for copied optional lines, find the corresponding base line and update.
705 FOR i IN base_line_numbers.first..base_line_numbers.last LOOP
706 IF (base_line_numbers(i) = l_clm_base_line) THEN
707 UPDATE pon_auction_item_prices_all
708 SET CLM_BASE_LINE_NUM = copied_base_line_numbers(i)
709 WHERE auction_header_id = p_auction_header_id
710 AND line_number = l_line_number;
711 END IF;
712 END LOOP;
713 ELSIF (l_clm_base_option = 'O' AND l_is_option_copy = 'Y') THEN
714 -- if source line is optional line, we only need to update option number.
715 SELECT (Max(clm_option_num) + 1) INTO l_next_option_num FROM pon_auction_item_prices_all
716 WHERE auction_header_id = p_auction_header_id
717 AND clm_base_line_num = l_clm_base_line;
718
719 UPDATE pon_auction_item_prices_all
720 SET clm_option_num = l_next_option_num
721 WHERE auction_header_id = p_auction_header_id
722 AND line_number = l_line_number;
723 END IF;
724
725
726 FND_ATTACHED_DOCUMENTS2_PKG.COPY_ATTACHMENTS (
727 X_from_entity_name => 'PON_AUCTION_ITEM_PRICES_ALL',
728 X_from_pk1_value => to_char(p_auction_header_id),
729 X_from_pk2_value => to_char(l_line_numbers(i)),
730 X_to_entity_name => 'PON_AUCTION_ITEM_PRICES_ALL',
731 X_to_pk1_value => to_char(p_auction_header_id), -- PK1_VALUE
732 X_to_pk2_value => to_char(l_line_number),
733 X_created_by => l_user_id, -- CREATED_BY
734 X_last_update_login => fnd_global.login_id -- LAST_UPDATE_LOGIN
735 );
736
737
738
739 IF(Nvl(l_info_flags(i),'N') = 'N') THEN
740 /*
741 copying attributes
742 */
743
744 copy_line_attr_information(p_auction_header_id ,
745 l_line_numbers(i),
746 l_line_number ,
747 l_user_id );
748
749 copy_price_diff_information(p_auction_header_id ,
750 l_line_numbers(i),
751 l_line_number ,
752 l_user_id );
753
754 copy_shipments_information(p_auction_header_id ,
755 l_line_numbers(i),
756 l_line_number ,
757 l_user_id );
758
759 copy_price_factors_information(p_auction_header_id ,
760 l_line_numbers(i),
761 l_line_number ,
762 l_user_id );
763 copy_payments_information(p_auction_header_id ,
764 l_line_numbers(i),
765 l_line_number ,
766 l_user_id );
767 END IF;
768
769 END LOOP; -- loop over lines in the clin
770
771 IF l_clin_flag = 'Y' THEN
772 -- if line being copied is a clin, then we need to find next clin number for it.
773
774 BEGIN
775
776 SELECT line_num_display BULK COLLECT INTO clin_num_tbl
777 FROM pon_auction_item_prices_all
778 WHERE auctioN_header_id = p_auction_header_id
779 AND group_line_id IS NULL
780 order by line_num_display;
781
782 EXCEPTION
783 WHEN No_Data_Found THEN
784 --l_max_clin := '0000';
785 clin_num_tbl := po_tbl_varchar100();
786 END;
787
788 next_clin_num := pon_clo_renumber_pkg.next_clin_num(clin_num_tbl);
789
790 -- this query selects new copied clin and its optional clins if any
791 SELECT line_number BULK COLLECT INTO l_new_clins
792 FROM pon_auction_item_prices_all
793 WHERE auction_header_id = p_auction_header_id
794 AND line_number > l_max_line_Number
795 AND group_line_id IS NULL;
796 --ORDER BY line_number;
797
798 FOR i IN 1..l_new_clins.Count LOOP
799
800 UPDATE pon_auction_item_prices_all
801 SET line_num_display = next_clin_num
802 WHERE auction_header_id = p_auction_header_id
803 AND line_number = l_new_clins(i);
804
805 pon_clo_renumber_pkg.RenumberSlinStructure('PON',p_auction_header_id,next_clin_num,x_result);
806
807 -- get subsequent clin numbers
808 clin_num_tbl.extend();
809 len := clin_num_tbl.Count ;
810 clin_num_tbl(len) := next_clin_num;
811 next_clin_num := pon_clo_renumber_pkg.next_clin_num(clin_num_tbl);
812
813 END LOOP;
814
815 /* UPDATE pon_auction_item_prices_all
816 SET line_num_display = next_clin_num
817 WHERE auction_header_id = p_auction_header_id
818 AND line_number = l_max_line_Number + 1; -- this value would have been assigned to the new copied clin
819 */
820 --pon_clo_renumber_pkg.RenumberSlinStructure('PON',p_auction_header_id,next_clin_num,x_result);
821
822 ELSE
823 -- line being copied is a slin
824 pon_clo_renumber_pkg.RenumberSlinStructure('PON',p_auction_header_id,l_clin_num,x_result);
825
826
827 END IF;
828
829 x_max_line_number := Nvl(x_max_line_number,l_max_line_number) + l_line_numbers.Count + 1;
830
831
832 END copy_clin;
833
834
835
836 procedure copy_line_information(p_auction_header_id IN NUMBER,
837 p_src_line_number IN NUMBER,
838 p_dest_line_number IN NUMBER,
839 p_group_line_id IN NUMBER,
840 p_user_id IN NUMBER )
841 IS
842
843 BEGIN
844
845 /* Bug 9787380
846 Added NEED BY DATE column to be copied when the new line is created
847 using copy action */
848
849 INSERT INTO pon_auction_item_prices_all
850 (AUCTION_HEADER_ID,
851 --AWARD_STATUS,
852 LINE_NUMBER,
853 ITEM_DESCRIPTION,
854 CATEGORY_ID,
855 CATEGORY_NAME,
856 IP_CATEGORY_ID,
857 UOM_CODE,
858 QUANTITY,
859 NEED_BY_DATE,
860 SHIP_TO_LOCATION_ID,
861 --NUMBER_OF_BIDS,
862 --LOWEST_BID_PRICE,
863 --LOWEST_BID_QUANTITY,
864 --LOWEST_BID_PROMISED_DATE,
865 --LOWEST_BID_NUMBER,
866 --CLOSEST_PROMISED_DATE,
867 --CLOSEST_BID_PRICE,
868 --CLOSEST_BID_QUANTITY,
869 --CLOSEST_BID_NUMBER,
870 TARGET_PRICE,
871 THRESHOLD_PRICE,
872 BID_START_PRICE,
873 NOTE_TO_BIDDERS,
874 ATTACHMENT_FLAG,
875 LANGUAGE_CODE,
876 CREATION_DATE,
877 CREATED_BY,
878 LAST_UPDATE_DATE,
879 LAST_UPDATED_BY,
880 AUCTION_CREATION_DATE,
881 CLOSE_BIDDING_DATE,
882 NUMBER_OF_EXTENSIONS,
883 RESERVE_PRICE,
884 DISPLAY_TARGET_PRICE_FLAG,
885 CURRENT_PRICE,
886 --BEST_BID_PRICE,
887 --BEST_BID_QUANTITY,
888 --BEST_BID_PROMISED_DATE,
889 --BEST_BID_NUMBER,
890 TYPE,
891 --LOT_LINE_NUMBER,
892 MIN_BID_INCREMENT,
893 MIN_BID_DECREMENT,
894 --BEST_BID_PROXY_LIMIT_PRICE,
895 --BEST_BID_CURRENCY_PRICE,
896 --BEST_BID_CURRENCY_CODE,
897 PO_MIN_REL_AMOUNT,
898 --BEST_BID_FIRST_BID_PRICE,
899 UNIT_OF_MEASURE,
900 HAS_ATTRIBUTES_FLAG,
901 TRANSPORTATION_ORIGIN,
902 TRANSPORTATION_DEST,
903 AUCTION_HEADER_ID_ORIG_ROUND,
904 AUCTION_HEADER_ID_PREV_ROUND,
905 --LINE_NUMBER_ORIGINAL_ROUND,
906 --LINE_NUMBER_PREV_ROUND,
907 MULTIPLE_PRICES_FLAG,
908 --RESIDUAL_QUANTITY,
909 --PENDING_QUANTITY,
910 --CANCEL_QUANTITY,
911 --NUMBER_OF_COMMITMENTS,
912 --NUMBER_OF_PENDING_COMMITMENTS,
913 TBD_PRICING_FLAG,
914 NEED_BY_START_DATE,
915 PRICE,
916 FREIGHT_TERMS_CODE,
917 --AWARDED_QUANTITY,
918 MODIFIED_FLAG,
919 --BEST_BID_BID_PRICE,
920 --BEST_BID_SCORE,
921 --BEST_BID_BID_NUMBER,
922 --BEST_BID_BID_CURRENCY_PRICE,
923 --BEST_BID_BID_CURRENCY_CODE,
924 ORG_ID,
925 HAS_PRICE_ELEMENTS_FLAG,
926 LINE_TYPE_ID,
927 ORDER_TYPE_LOOKUP_CODE,
928 --LINE_ORIGINATION_CODE,
929 --REQUISITION_NUMBER,
930 ITEM_REVISION,
931 ITEM_ID,
932 ITEM_NUMBER,
933 PRICE_BREAK_TYPE,
934 PRICE_BREAK_NEG_FLAG,
935 HAS_SHIPMENTS_FLAG,
936 SOURCE_DOC_NUMBER,
937 SOURCE_LINE_NUMBER,
938 SOURCE_DOC_ID,
939 SOURCE_LINE_ID,
940 --ALLOCATION_STATUS,
941 PRICE_DISABLED_FLAG,
942 QUANTITY_DISABLED_FLAG,
943 JOB_ID,
944 ADDITIONAL_JOB_DETAILS,
945 PO_AGREED_AMOUNT,
946 HAS_PRICE_DIFFERENTIALS_FLAG,
947 DIFFERENTIAL_RESPONSE_TYPE,
948 PURCHASE_BASIS,
949 IS_QUANTITY_SCORED,
950 IS_NEED_BY_DATE_SCORED,
951 DISP_LINE_NUMBER,
952 LAST_UPDATE_LOGIN,
953 LAST_AMENDMENT_UPDATE,
954 MODIFIED_DATE,
955 PRICE_DIFF_SHIPMENT_NUMBER,
956 GROUP_TYPE,
957 PARENT_LINE_NUMBER,
958 DOCUMENT_DISP_LINE_NUMBER,
959 MAX_SUB_LINE_SEQUENCE_NUMBER,
960 SUB_LINE_SEQUENCE_NUMBER,
961 HAS_BUYER_PFS_FLAG,
962 UNIT_TARGET_PRICE,
963 UNIT_DISPLAY_TARGET_FLAG,
964 HAS_PAYMENTS_FLAG,
965 ADVANCE_AMOUNT,
966 RECOUPMENT_RATE_PERCENT,
967 PROGRESS_PYMT_RATE_PERCENT,
968 RETAINAGE_RATE_PERCENT,
969 MAX_RETAINAGE_AMOUNT,
970 WORK_APPROVER_USER_ID,
971 PROJECT_ID,
972 PROJECT_TASK_ID,
973 PROJECT_AWARD_ID,
974 PROJECT_EXPENDITURE_TYPE,
975 PROJECT_EXP_ORGANIZATION_ID,
976 PROJECT_EXPENDITURE_ITEM_DATE,
977 HAS_QUANTITY_TIERS,
978 --- CLM - Clin Slin project Changes Start.
979 LINE_NUM_DISPLAY,
980 GROUP_LINE_ID,
981 CLM_INFO_FLAG,
982 CLM_OPTION_INDICATOR,
983 CLM_BASE_LINE_NUM,
984 CLM_OPTION_NUM,
985 CLM_OPTION_FROM_DATE,
986 CLM_OPTION_TO_DATE,
987 CLM_FUNDED_FLAG,
988 --- CLM - Clin Slin project Changes End.
989 --<Sol Project>
990 UDA_TEMPLATE_ID,
991 CLM_CONTRACT_TYPE,
992 CLM_COST_CONSTRAINT,
993 CLM_IDC_TYPE,
994 CLM_AMOUNT,
995 CLM_NEED_BY_DATE,
996 EXHIBIT_NUMBER
997 )
998 SELECT
999 AUCTION_HEADER_ID,
1000 --AWARD_STATUS,
1001 p_dest_line_number, -- line_number
1002 ITEM_DESCRIPTION,
1003 CATEGORY_ID,
1004 CATEGORY_NAME,
1005 IP_CATEGORY_ID,
1006 UOM_CODE,
1007 QUANTITY,
1008 NEED_BY_DATE,
1009 SHIP_TO_LOCATION_ID,
1010 --NUMBER_OF_BIDS,
1011 --LOWEST_BID_PRICE,
1012 --LOWEST_BID_QUANTITY,
1013 --LOWEST_BID_PROMISED_DATE,
1014 --LOWEST_BID_NUMBER,
1015 --CLOSEST_PROMISED_DATE,
1016 --CLOSEST_BID_PRICE,
1017 --CLOSEST_BID_QUANTITY,
1018 --CLOSEST_BID_NUMBER,
1019 TARGET_PRICE,
1020 THRESHOLD_PRICE,
1021 BID_START_PRICE,
1022 NOTE_TO_BIDDERS,
1023 ATTACHMENT_FLAG,
1024 LANGUAGE_CODE,
1025 sysdate,
1026 p_user_id,
1027 sysdate,
1028 p_user_id,
1029 AUCTION_CREATION_DATE,
1030 CLOSE_BIDDING_DATE,
1031 NUMBER_OF_EXTENSIONS,
1032 RESERVE_PRICE,
1033 DISPLAY_TARGET_PRICE_FLAG,
1034 CURRENT_PRICE,
1035 --BEST_BID_PRICE,
1036 --BEST_BID_QUANTITY,
1037 --BEST_BID_PROMISED_DATE,
1038 --BEST_BID_NUMBER,
1039 TYPE,
1040 --LOT_LINE_NUMBER,
1041 MIN_BID_INCREMENT,
1042 MIN_BID_DECREMENT,
1043 --BEST_BID_PROXY_LIMIT_PRICE,
1044 --BEST_BID_CURRENCY_PRICE,
1045 --BEST_BID_CURRENCY_CODE,
1046 PO_MIN_REL_AMOUNT,
1047 --BEST_BID_FIRST_BID_PRICE,
1048 UNIT_OF_MEASURE,
1049 HAS_ATTRIBUTES_FLAG,
1050 TRANSPORTATION_ORIGIN,
1051 TRANSPORTATION_DEST,
1052 AUCTION_HEADER_ID_ORIG_ROUND,
1053 AUCTION_HEADER_ID_PREV_ROUND,
1054 --LINE_NUMBER_ORIGINAL_ROUND,
1055 --LINE_NUMBER_PREV_ROUND,
1056 MULTIPLE_PRICES_FLAG,
1057 --RESIDUAL_QUANTITY,
1058 --PENDING_QUANTITY,
1059 --CANCEL_QUANTITY,
1060 --NUMBER_OF_COMMITMENTS,
1061 --NUMBER_OF_PENDING_COMMITMENTS,
1062 TBD_PRICING_FLAG,
1063 NEED_BY_START_DATE,
1064 PRICE,
1065 FREIGHT_TERMS_CODE,
1066 --AWARDED_QUANTITY,
1067 MODIFIED_FLAG,
1068 --BEST_BID_BID_PRICE,
1069 --BEST_BID_SCORE,
1070 --BEST_BID_BID_NUMBER,
1071 --BEST_BID_BID_CURRENCY_PRICE,
1072 --BEST_BID_BID_CURRENCY_CODE,
1073 ORG_ID,
1074 HAS_PRICE_ELEMENTS_FLAG,
1075 LINE_TYPE_ID,
1076 ORDER_TYPE_LOOKUP_CODE,
1077 --LINE_ORIGINATION_CODE,
1078 --REQUISITION_NUMBER,
1079 ITEM_REVISION,
1080 ITEM_ID,
1081 ITEM_NUMBER,
1082 PRICE_BREAK_TYPE,
1083 PRICE_BREAK_NEG_FLAG,
1084 HAS_SHIPMENTS_FLAG,
1085 null,
1086 null,
1087 NULL,--SOURCE_DOC_ID,
1088 NULL,--SOURCE_LINE_ID,
1089 --ALLOCATION_STATUS,
1090 PRICE_DISABLED_FLAG,
1091 QUANTITY_DISABLED_FLAG,
1092 JOB_ID,
1093 ADDITIONAL_JOB_DETAILS,
1094 PO_AGREED_AMOUNT,
1095 HAS_PRICE_DIFFERENTIALS_FLAG,
1096 DIFFERENTIAL_RESPONSE_TYPE,
1097 PURCHASE_BASIS,
1098 IS_QUANTITY_SCORED,
1099 IS_NEED_BY_DATE_SCORED,
1100 p_dest_line_number, -- disp_line_number
1101 p_user_id,
1102 0, -- last_amendment_update
1103 MODIFIED_DATE,
1104 PRICE_DIFF_SHIPMENT_NUMBER,
1105 GROUP_TYPE,
1106 PARENT_LINE_NUMBER,
1107 p_dest_line_number,
1108 p_dest_line_number,
1109 p_dest_line_number,
1110 HAS_BUYER_PFS_FLAG,
1111 UNIT_TARGET_PRICE,
1112 UNIT_DISPLAY_TARGET_FLAG,
1113 HAS_PAYMENTS_FLAG,
1114 ADVANCE_AMOUNT,
1115 RECOUPMENT_RATE_PERCENT,
1116 PROGRESS_PYMT_RATE_PERCENT,
1117 RETAINAGE_RATE_PERCENT,
1118 MAX_RETAINAGE_AMOUNT,
1119 WORK_APPROVER_USER_ID,
1120 PROJECT_ID,
1121 PROJECT_TASK_ID,
1122 PROJECT_AWARD_ID,
1123 PROJECT_EXPENDITURE_TYPE,
1124 PROJECT_EXP_ORGANIZATION_ID,
1125 PROJECT_EXPENDITURE_ITEM_DATE,
1126 HAS_QUANTITY_TIERS,
1127 --- CLM - Clin Slin project Changes Start.
1128 NULL, --line_num_display - this will be calculated later
1129
1130 /*CASE WHEN l_clin_flag='N' THEN group_line_id -- if a slin is copied, then group_line_id wont change
1131 WHEN (i>1 AND group_line_id IS NOT NULL) THEN l_max_line_number+1 end, */
1132 Nvl2(group_line_id,p_group_line_id,null),
1133 CLM_INFO_FLAG,
1134
1135 CLM_OPTION_INDICATOR, --CLM_OPTION_INDICATOR,
1136 CLM_BASE_LINE_NUM,--CLM_BASE_LINE_NUM,
1137 CLM_OPTION_NUM,--CLM_OPTION_NUM,
1138 CLM_OPTION_FROM_DATE,--CLM_OPTION_FROM_DATE,
1139 CLM_OPTION_TO_DATE,--CLM_OPTION_TO_DATE,
1140 CLM_FUNDED_FLAG,
1141 --- CLM - Clin Slin project Changes End.
1142 --<Sol Project>
1143 UDA_TEMPLATE_ID,
1144 CLM_CONTRACT_TYPE,
1145 CLM_COST_CONSTRAINT,
1146 CLM_IDC_TYPE,
1147 CLM_AMOUNT,
1148 CLM_NEED_BY_DATE,
1149 EXHIBIT_NUMBER
1150 FROM pon_auction_item_prices_all
1151 WHERE auction_header_id = p_auction_header_id
1152 AND line_number = p_src_line_number;
1153
1154 --Invoke UDA Copy for Line
1155 copy_line_uda(p_auction_header_id,
1156 p_src_line_number,
1157 p_dest_line_number);
1158
1159 END copy_line_information;
1160
1161
1162 -- Procedure to copy UDA information from Source to Target Line
1163 -- Paramters
1164 -- p_auction_header_id IN Auction Header Id
1165 -- p_src_line_number IN Source Line Number
1166 -- p_dest_line_number IN Destination Line Number
1167
1168 procedure copy_line_uda(p_auction_header_id IN NUMBER,
1169 p_src_line_number IN NUMBER,
1170 p_dest_line_number IN NUMBER)
1171 IS
1172 l_msg_count Number;
1173 l_return_status Varchar2(1);
1174 l_msg_data varchar2(400);
1175 BEGIN
1176 PON_COPY_UDAS_GRP.COPY_LINE_UDAS(
1177 p_source_auction_header_id=> p_auction_header_id,
1178 p_target_auction_header_id=> p_auction_header_id,
1179 p_source_line_number=>p_src_line_number,
1180 p_target_line_number=>p_dest_line_number,
1181 x_return_status=>l_return_status,
1182 x_msg_count=>l_msg_count,
1183 x_msg_data=>l_msg_data);
1184 END copy_line_uda;
1185
1186
1187 procedure copy_line_attr_information(p_auction_header_id IN NUMBER,
1188 p_src_line_number IN NUMBER,
1189 p_dest_line_number IN NUMBER,
1190 p_user_id IN NUMBER )
1191 IS
1192
1193 BEGIN
1194
1195 INSERT INTO pon_auction_attributes(
1196 auction_header_id,
1197 LINE_NUMBER,
1198 ATTRIBUTE_NAME,
1199 DESCRIPTION,
1200 DATATYPE ,
1201 MANDATORY_FLAG,
1202 VALUE,
1203 DISPLAY_PROMPT,
1204 HELP_TEXT,
1205 DISPLAY_TARGET_FLAG,
1206 CREATION_DATE,
1207 CREATED_BY,
1208 LAST_UPDATE_DATE,
1209 LAST_UPDATED_BY,
1210 ATTRIBUTE_LIST_ID,
1211 DISPLAY_ONLY_FLAG,
1212 SEQUENCE_NUMBER,
1213 COPIED_FROM_CAT_FLAG,
1214 WEIGHT,
1215 SCORING_TYPE,
1216 ATTR_LEVEL ,
1217 ATTR_GROUP,
1218 ATTR_MAX_SCORE,
1219 INTERNAL_ATTR_FLAG,
1220 ATTR_GROUP_SEQ_NUMBER,
1221 ATTR_DISP_SEQ_NUMBER ,
1222 MODIFIED_FLAG,
1223 MODIFIED_DATE,
1224 LAST_AMENDMENT_UPDATE,
1225 IP_CATEGORY_ID,
1226 IP_DESCRIPTOR_ID,
1227 SECTION_NAME,
1228 KNOCKOUT_SCORE,
1229 SCORING_METHOD)
1230 SELECT p_auction_header_id,
1231 p_dest_line_number,
1232 ATTRIBUTE_NAME,
1233 DESCRIPTION,
1234 DATATYPE ,
1235 MANDATORY_FLAG,
1236 VALUE,
1237 DISPLAY_PROMPT,
1238 HELP_TEXT,
1239 DISPLAY_TARGET_FLAG,
1240 sysdate,
1241 p_user_id,
1242 sysdate,
1243 p_user_id,
1244 ATTRIBUTE_LIST_ID,
1245 DISPLAY_ONLY_FLAG,
1246 SEQUENCE_NUMBER,
1247 COPIED_FROM_CAT_FLAG,
1248 WEIGHT,
1249 SCORING_TYPE,
1250 ATTR_LEVEL ,
1251 ATTR_GROUP,
1252 ATTR_MAX_SCORE,
1253 INTERNAL_ATTR_FLAG,
1254 ATTR_GROUP_SEQ_NUMBER,
1255 ATTR_DISP_SEQ_NUMBER ,
1256 MODIFIED_FLAG,
1257 MODIFIED_DATE,
1258 LAST_AMENDMENT_UPDATE,
1259 IP_CATEGORY_ID,
1260 IP_DESCRIPTOR_ID,
1261 SECTION_NAME,
1262 KNOCKOUT_SCORE,
1263 SCORING_METHOD
1264 FROM pon_auction_attributes paa
1265 WHERE paa.auction_header_id = p_auction_header_id
1266 AND paa.ATTR_LEVEL = 'LINE'
1267 AND paa.line_number = p_src_line_number;
1268
1269 -- ATtribute scores
1270 INSERT
1271 INTO PON_ATTRIBUTE_SCORES
1272 ( AUCTION_HEADER_ID,
1273 LINE_NUMBER,
1274 ATTRIBUTE_SEQUENCE_NUMBER,
1275 VALUE,
1276 FROM_RANGE,
1277 TO_RANGE,
1278 SCORE,
1279 ATTRIBUTE_LIST_ID,
1280 SEQUENCE_NUMBER,
1281 CREATION_DATE,
1282 CREATED_BY,
1283 LAST_UPDATE_DATE,
1284 LAST_UPDATED_BY
1285 )
1286 (SELECT
1287 p_auction_header_id,
1288 p_dest_line_number,
1289 pas.ATTRIBUTE_SEQUENCE_NUMBER,
1290 pas.VALUE,
1291 pas.FROM_RANGE,
1292 pas.TO_RANGE,
1293 pas.SCORE,
1294 pas.ATTRIBUTE_LIST_ID,
1295 pas.SEQUENCE_NUMBER,
1296 SYSDATE, -- CREATION_DATE
1297 p_user_id, -- CREATED_BY
1298 SYSDATE, -- LAST_UPDATE_DATE
1299 p_user_id -- LAST_UPDATED_BY
1300 FROM PON_ATTRIBUTE_SCORES pas,
1301 PON_AUCTION_ATTRIBUTES paa
1302 WHERE pas.AUCTION_HEADER_ID = p_auction_header_id
1303 AND pas.auction_header_id = paa.auction_header_id
1304 AND pas.line_number = paa.line_number
1305 AND paa.attr_level = 'LINE'
1306 AND pas.attribute_sequence_number = paa.sequence_number
1307 AND pas.line_number = p_src_line_number
1308 ) ;
1309
1310
1311 END copy_line_attr_information;
1312
1313 procedure copy_price_diff_information(p_auction_header_id IN NUMBER,
1314 p_src_line_number IN NUMBER,
1315 p_dest_line_number IN NUMBER,
1316 p_user_id IN NUMBER )
1317 IS
1318
1319 BEGIN
1320
1321 INSERT
1322 INTO PON_PRICE_DIFFERENTIALS
1323 ( AUCTION_HEADER_ID,
1324 LINE_NUMBER,
1325 SHIPMENT_NUMBER,
1326 PRICE_DIFFERENTIAL_NUMBER,
1327 PRICE_TYPE,
1328 MULTIPLIER,
1329 CREATION_DATE,
1330 CREATED_BY,
1331 LAST_UPDATE_DATE,
1332 LAST_UPDATED_BY,
1333 LAST_UPDATE_LOGIN
1334 )
1335 (SELECT
1336 p_auction_header_id,
1337 p_dest_line_number,
1338 SHIPMENT_NUMBER,
1339 PRICE_DIFFERENTIAL_NUMBER,
1340 PRICE_TYPE,
1341 MULTIPLIER,
1342 SYSDATE, -- CREATION_DATE
1343 p_user_id, -- CREATED_BY
1344 SYSDATE, -- LAST_UPDATE_DATE
1345 p_user_id, -- LAST_UPDATED_BY
1346 p_user_id -- LAST_UPDATE_LOGIN
1347 FROM PON_PRICE_DIFFERENTIALS
1348 WHERE AUCTION_HEADER_ID = p_auction_header_id
1349 AND LINE_NUMBER = p_src_line_number) ;
1350
1351 END copy_price_diff_information;
1352
1353 procedure copy_shipments_information(p_auction_header_id IN NUMBER,
1354 p_src_line_number IN NUMBER,
1355 p_dest_line_number IN NUMBER,
1356 p_user_id IN NUMBER )
1357 IS
1358
1359 BEGIN
1360
1361 -- price breaks / qty tiers
1362 INSERT INTO
1363 PON_AUCTION_SHIPMENTS_ALL
1364 ( AUCTION_HEADER_ID,
1365 LINE_NUMBER,
1366 SHIPMENT_NUMBER,
1367 SHIPMENT_TYPE,
1368 SHIP_TO_ORGANIZATION_ID,
1369 SHIP_TO_LOCATION_ID,
1370 QUANTITY,
1371 PRICE,
1372 EFFECTIVE_START_DATE,
1373 EFFECTIVE_END_DATE,
1374 ORG_ID,
1375 CREATION_DATE,
1376 CREATED_BY,
1377 LAST_UPDATE_DATE,
1378 LAST_UPDATED_BY,
1379 LAST_UPDATE_LOGIN,
1380 HAS_PRICE_DIFFERENTIALS_FLAG,
1381 DIFFERENTIAL_RESPONSE_TYPE,
1382 MAX_QUANTITY
1383 )
1384 (SELECT p_auction_header_id,
1385 p_dest_line_number,
1386 SHIPMENT_NUMBER,
1387 SHIPMENT_TYPE,
1388 SHIP_TO_ORGANIZATION_ID,
1389 SHIP_TO_LOCATION_ID,
1390 QUANTITY,
1391 PRICE,
1392 EFFECTIVE_START_DATE, -- EFFECTIVE_START_DATE
1393 EFFECTIVE_END_DATE, -- EFFECTIVE_END_DATE
1394 ORG_ID, -- Do we need to set thi OrgId to the current one
1395 SYSDATE, -- CREATION_DATE
1396 p_user_id, -- CREATED_BY
1397 SYSDATE, -- LAST_UPDATE_DATE
1398 p_user_id, -- LAST_UPDATED_BY
1399 p_user_id, -- LAST_UPDATE_LOGIN
1400 HAS_PRICE_DIFFERENTIALS_FLAG, -- HAS_PRICE_DIFFERENTIALS_FLAG
1401 DIFFERENTIAL_RESPONSE_TYPE, -- DIFFERENTIAL_RESPONSE_TYPE
1402 MAX_QUANTITY
1403 FROM PON_AUCTION_SHIPMENTS_ALL
1404 WHERE AUCTION_HEADER_ID = p_auction_header_id
1405 AND SHIPMENT_TYPE in ('PRICE BREAK','QUANTITY BASED')
1406 AND line_number = p_src_line_number) ;
1407
1408 END copy_shipments_information;
1409
1410 procedure copy_price_factors_information(p_auction_header_id IN NUMBER,
1411 p_src_line_number IN NUMBER,
1412 p_dest_line_number IN NUMBER,
1413 p_user_id IN NUMBER )
1414 IS
1415
1416 BEGIN
1417
1418 INSERT INTO
1419 PON_PRICE_ELEMENTS
1420 ( AUCTION_HEADER_ID,
1421 LINE_NUMBER,
1422 LIST_ID,
1423 PRICE_ELEMENT_TYPE_ID,
1424 PRICING_BASIS,
1425 VALUE,
1426 DISPLAY_TARGET_FLAG,
1427 SEQUENCE_NUMBER,
1428 CREATION_DATE,
1429 CREATED_BY,
1430 LAST_UPDATE_DATE,
1431 LAST_UPDATED_BY,
1432 PF_TYPE,
1433 DISPLAY_TO_SUPPLIERS_FLAG
1434 )
1435 (SELECT
1436 p_auction_header_id,
1437 p_dest_line_number,
1438 P.LIST_ID,
1439 P.PRICE_ELEMENT_TYPE_ID,
1440 P.PRICING_BASIS,
1441 P.VALUE,
1442 P.DISPLAY_TARGET_FLAG,
1443 P.SEQUENCE_NUMBER,
1444 SYSDATE,
1445 p_user_id,
1446 SYSDATE,
1447 p_user_id,
1448 PF_TYPE, -- Tranformation project related column
1449 DISPLAY_TO_SUPPLIERS_FLAG -- Tranformation project related column
1450 FROM PON_PRICE_ELEMENTS P,
1451 PON_PRICE_ELEMENT_TYPES_VL VL
1452 WHERE P.AUCTION_HEADER_ID = p_auction_header_id
1453 AND P.PRICE_ELEMENT_TYPE_ID <> -10
1454 AND P.PRICE_ELEMENT_TYPE_ID = VL.PRICE_ELEMENT_TYPE_ID
1455 AND VL.ENABLED_FLAG = 'Y'
1456 AND P.line_number= p_src_line_number) ;
1457
1458 END copy_price_factors_information;
1459
1460 procedure copy_payments_information(p_auction_header_id IN NUMBER,
1461 p_src_line_number IN NUMBER,
1462 p_dest_line_number IN NUMBER,
1463 p_user_id IN NUMBER )
1464 IS
1465 BEGIN
1466 INSERT INTO
1467 PON_AUC_PAYMENTS_SHIPMENTS
1468 ( AUCTION_HEADER_ID,
1469 PAYMENT_ID,
1470 LINE_NUMBER,
1471 PAYMENT_DISPLAY_NUMBER,
1472 PAYMENT_TYPE_CODE,
1473 PAYMENT_DESCRIPTION,
1474 SHIP_TO_LOCATION_ID,
1475 QUANTITY,
1476 UOM_CODE,
1477 TARGET_PRICE,
1478 NEED_BY_DATE,
1479 WORK_APPROVER_USER_ID,
1480 NOTE_TO_BIDDERS,
1481 PROJECT_ID,
1482 PROJECT_TASK_ID,
1483 PROJECT_AWARD_ID,
1484 PROJECT_EXPENDITURE_TYPE,
1485 PROJECT_EXP_ORGANIZATION_ID,
1486 PROJECT_EXPENDITURE_ITEM_DATE,
1487 CREATION_DATE,
1488 CREATED_BY,
1489 LAST_UPDATE_DATE,
1490 LAST_UPDATED_BY,
1491 LAST_UPDATE_LOGIN
1492 )
1493 (SELECT
1494 p_auction_header_id,
1495 PON_AUC_PAYMENTS_SHIPMENTS_S1.NEXTVAL,
1496 p_dest_line_number,
1497 PAYMENT_DISPLAY_NUMBER,
1498 PAYMENT_TYPE_CODE,
1499 PAYMENT_DESCRIPTION,
1500 SHIP_TO_LOCATION_ID,
1501 QUANTITY,
1502 UOM_CODE,
1503 TARGET_PRICE,
1504 NEED_BY_DATE,
1505 WORK_APPROVER_USER_ID,
1506 NOTE_TO_BIDDERS,
1507 PROJECT_ID,
1508 PROJECT_TASK_ID,
1509 PROJECT_AWARD_ID,
1510 PROJECT_EXPENDITURE_TYPE,
1511 PROJECT_EXP_ORGANIZATION_ID,
1512 PROJECT_EXPENDITURE_ITEM_DATE,
1513 SYSDATE,
1514 p_user_id,
1515 SYSDATE,
1516 p_user_id,
1517 fnd_global.login_id
1518 FROM pon_auc_payments_shipments
1519 WHERE auction_header_id = p_auction_header_id
1520 AND line_number =p_src_line_number );
1521
1522 END;
1523
1524 PROCEDURE POPULATE_CROSSLINK_MAP(p_auction_header_id IN NUMBER,
1525 x_option_num OUT NOCOPY PO_TBL_NUMBER,
1526 x_base_num OUT NOCOPY PO_TBL_NUMBER) IS
1527 Cursor CrossLinkedOptions is
1528 select DISTINCT aip1.group_line_id option_number, aip2.group_line_id base_number
1529 from pon_auction_item_prices_all aip1,
1530 pon_auction_item_prices_all aip2
1531 where aip1.auction_header_id = p_auction_header_id
1532 AND aip1.clm_option_indicator = 'O'
1533 AND Nvl(aip1.clm_info_flag,'N') = 'N'
1534 and aip1.clm_base_line_num is not NULL
1535 AND aip1.group_line_id IS NOT NULL
1536 and aip1.auction_header_id = aip2.auction_header_id
1537 AND aip2.group_line_id IS NOT null
1538 and aip1.clm_base_line_num = aip2.line_number
1539 and aip1.group_line_id <> aip2.group_line_id;
1540 BEGIN
1541
1542 open CrossLinkedOptions;
1543 fetch CrossLinkedOptions bulk collect into x_option_num, x_base_num;
1544
1545 close CrossLinkedOptions;
1546
1547 END POPULATE_CROSSLINK_MAP;
1548
1549 PROCEDURE SEND_LINE_DELETED_NOTIF(p_caller IN VARCHAR2,
1550 p_document_id IN NUMBER,
1551 p_document_line_id IN NUMBER,
1552 x_return_status OUT NOCOPY VARCHAR2,
1553 x_msg_count OUT NOCOPY NUMBER,
1554 x_msg_data OUT NOCOPY VARCHAR2
1555 ) IS
1556 l_slin_flag VARCHAR2(1);
1557 l_option_flag VARCHAR2(1);
1558 l_itemtype VARCHAR2(8) := 'PONLDELE';
1559 l_itemkey VARCHAR2(50);
1560 l_sequence NUMBER;
1561 l_preparer_id NUMBER;
1562 l_username VARCHAR2(100);
1563 l_user_display_name VARCHAR2(240);
1564 l_line_num_display VARCHAR2(10);
1565 l_req_num po_requisition_headers_all.segment1%type;
1566 l_req_line_num po_requisition_lines_all.line_num_display%TYPE;
1567 l_document_number VARCHAR2(100);
1568 l_req_hdr_id po_requisition_lines_all.requisition_header_id%TYPE;
1569 l_req_line_id po_requisition_lines_all.requisition_line_id%TYPE;
1570 l_is_req_backed VARCHAR2(1) := 'N';
1571 l_module_name VARCHAR2(100) := 'SEND_LINE_DELETED_NOTIF';
1572 type l_req_lines_type is REF cursor;
1573 l_req_lines_csr l_req_lines_type;
1574 l_type_lookup_code VARCHAR2(100);
1575 l_req_line_description po_requisition_lines_all.item_description%TYPE;
1576 l_buyer_name VARCHAR2(200);
1577 l_buyer_id NUMBER;
1578 l_ownerName varchar2(200) := null;
1579 l_error_code varchar2(200) := null;
1580 l_error_msg varchar2(200) := null;
1581 l_result number := 0;
1582 l_doc_type_dsp VARCHAR2(100);
1583
1584
1585
1586 BEGIN
1587 x_return_status := FND_API.g_ret_sts_success;
1588 x_msg_count := 0;
1589
1590 IF(l_req_lines_csr%ISOPEN) THEN
1591 CLOSE l_req_lines_csr;
1592 END IF;
1593
1594 -- One auction line can have many backing req lines. so notify for each of the
1595 -- backing req lines through cursor.
1596 IF(p_caller='PON') THEN
1597
1598 OPEN l_req_lines_csr FOR
1599 SELECT pbr.REQUISITION_HEADER_ID,
1600 pbr.REQUISITION_LINE_ID,
1601 paip.requisition_number,
1602 Nvl2(paip.group_line_id,'Y','N'),
1603 Nvl2(paip.clm_base_line_num,'Y','N'),
1604 'Y',
1605 pah.document_number,
1606 paip.line_num_display,
1607 prl.line_num_display,
1608 prh.preparer_id,
1609 'SOLICITATION',
1610 prl.item_description,
1611 NULL -- Buyer ID
1612 FROM pon_backing_requisitions pbr,
1613 pon_auction_item_prices_all paip,
1614 pon_auction_headers_all pah,
1615 po_requisition_lines_all prl,
1616 po_requisition_headers_all prh
1617 WHERE pbr.auction_header_id = p_document_id
1618 AND pbr.line_number = p_document_line_id
1619 AND paip.auction_header_id = pbr.auction_header_id
1620 AND paip.line_number = pbr.line_number
1621 AND Nvl(paip.line_origination_code,'N') = 'REQUISITION'
1622 AND pah.auction_header_id = paip.auction_header_id
1623 AND prl.auction_header_id = pah.auction_header_id
1624 AND prl.auction_line_number = paip.line_number
1625 AND prl.requisition_header_id = prh.requisition_header_id;
1626
1627 ELSE -- p_caller = 'PO' or 'PO_MOD'
1628 OPEN l_req_lines_csr FOR
1629 -- Priced Slins will be linked to backing Req Slins via req distribution id
1630 SELECT DISTINCT
1631 prh.REQUISITION_HEADER_ID,
1632 prl.REQUISITION_LINE_ID,
1633 prh.segment1,
1634 Nvl2(pol.group_line_id,'Y','N'),
1635 Nvl2(pol.clm_base_line_num,'Y','N'),
1636 'Y',
1637 Decode(p_caller,'PO_MOD',PD.MODIFICATION_NUMBER,poh.clm_document_number) ,
1638 pol.line_num_display,
1639 prl.line_num_display,
1640 prh.preparer_id,
1641 poh.type_lookup_code,
1642 prl.item_description,
1643 poh.agent_id
1644 FROM po_requisition_lines_all prl,
1645 po_requisition_headers_all prh,
1646 po_req_distributions_all prd,
1647 po_headers_draft_all poh,
1648 po_lines_draft_all pol,
1649 po_distributions_draft_all pod,
1650 po_drafts pd
1651
1652 WHERE
1653 Nvl(pol.draft_id,-1)=Nvl(poh.draft_id,-1)
1654 AND Nvl(pd.draft_id,-1)=Nvl(pol.draft_id,-1)
1655 AND Nvl(pod.draft_id,-1)=Nvl(pol.draft_id,-1)
1656 AND Nvl(pol.draft_id,-1)=p_document_id
1657 AND pol.po_header_id = poh.po_header_id
1658 AND pod.po_line_id = pol.po_line_id
1659 AND prl.requisition_line_id= prd.requisition_line_id
1660 AND prl.requisition_header_id = prh.requisition_header_id
1661 AND pod.req_distribution_id = prd.distribution_id
1662 AND Nvl(pol.delete_flag,'N') = 'Y'
1663 AND (pol.group_line_id IS NOT NULL
1664 AND NOT EXISTS (SELECT 'Parent Clin Deleted'
1665 FROM po_lines_draft_all
1666 WHERE po_header_id=pol.po_header_id
1667 AND draft_id=pol.draft_id
1668 AND Nvl(delete_flag,'N')='Y'
1669 AND po_line_id=pol.group_line_id ))
1670
1671
1672 UNION
1673 -- Info Slins and Option lines will be linked to backing Req via po_line_id
1674 SELECT DISTINCT
1675 prh.REQUISITION_HEADER_ID,
1676 prl.REQUISITION_LINE_ID,
1677 prh.segment1,
1678 Nvl2(pol.group_line_id,'Y','N'),
1679 Nvl2(pol.clm_base_line_num,'Y','N'),
1680 'Y',
1681 Decode(p_caller,'PO_MOD',PD.MODIFICATION_NUMBER,poh.clm_document_number) ,
1682 pol.line_num_display,
1683 prl.line_num_display,
1684 prh.preparer_id,
1685 poh.type_lookup_code,
1686 prl.item_description,
1687 poh.agent_id
1688 FROM po_requisition_lines_all prl,
1689 po_requisition_headers_all prh,
1690 po_headers_draft_all poh,
1691 po_lines_draft_all pol,
1692 po_drafts pd
1693
1694 WHERE
1695 Nvl(pol.draft_id,-1)=Nvl(poh.draft_id,-1)
1696 AND Nvl(pd.draft_id,-1)=Nvl(pol.draft_id,-1)
1697 AND Nvl(pol.draft_id,-1)=p_document_id
1698 AND pol.po_header_id = poh.po_header_id
1699 AND prl.requisition_header_id = prh.requisition_header_id
1700 AND pol.po_line_id = prl.po_line_id
1701 AND Nvl(pol.delete_flag,'N') = 'Y'
1702 AND (Nvl(pol.clm_info_flag,'N')='Y'
1703 OR Nvl(pol.clm_option_indicator,'B')='O')
1704 AND ((pol.group_line_id IS NOT NULL
1705 AND NOT EXISTS (SELECT 'Parent Clin Deleted'
1706 FROM po_lines_draft_all
1707 WHERE po_header_id=pol.po_header_id
1708 AND draft_id=pol.draft_id
1709 AND Nvl(delete_flag,'N')='Y'
1710 AND po_line_id=pol.group_line_id ))
1711 OR (pol.clm_base_line_num IS NOT NULL
1712 AND NOT EXISTS ( SELECT 'Base Line Deleted'
1713 FROM po_lines_draft_all
1714 WHERE po_header_id=pol.po_header_id
1715 AND draft_id=pol.draft_id
1716 AND Nvl(delete_flag,'N')='Y'
1717 AND po_line_id=pol.clm_base_line_num ))
1718 );
1719
1720 END IF;
1721
1722 LOOP
1723 FETCH l_req_lines_csr INTO l_req_hdr_id,
1724 l_req_line_id,
1725 l_req_num,
1726 l_slin_flag,
1727 l_option_flag,
1728 l_is_req_backed,
1729 l_document_number,
1730 l_line_num_display,
1731 l_req_line_num,
1732 l_preparer_id,
1733 l_type_lookup_code,
1734 l_req_line_description,
1735 l_buyer_id;
1736 EXIT WHEN l_req_lines_csr%NOTFOUND;
1737
1738
1739 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1740 FND_LOG.string (log_level => FND_LOG.level_procedure,
1741 module => 'PON_CLM_CLO_UTIL_PKG.' || l_module_name,
1742 message => 'Entered procedure = ' || l_module_name ||
1743 ', l_slin_flag = ' || l_slin_flag ||
1744 ', l_req_num = ' || l_req_num ||
1745 ', l_option_flag = ' || l_option_flag ||
1746 ', l_is_req_backed = ' || l_is_req_backed ||
1747 ', l_req_line_id = ' || l_req_line_id);
1748 END IF;
1749
1750 IF( l_is_req_backed = 'Y' AND (NVL(l_slin_flag,'N') = 'Y' OR NVL(l_option_flag,'N') = 'Y') ) THEN
1751
1752 PO_REQAPPROVAL_INIT1.get_user_name(l_preparer_id, l_username, l_user_display_name);
1753
1754 IF(p_caller ='PON') THEN
1755 update po_requisition_lines_all prla
1756 set auction_header_id = null,
1757 auction_display_number = null,
1758 auction_line_number = null,
1759 po_line_id = null,
1760 line_location_id=null,
1761 at_sourcing_flag = null,
1762 on_rfq_flag = null,
1763 reqs_in_pool_flag = NULL
1764 WHERE requisition_line_id=l_req_line_id;
1765 END IF;
1766 SELECT PON_LINE_DELETE_WF_S.nextval
1767 INTO l_sequence
1768 FROM dual;
1769
1770 BEGIN
1771 IF(p_caller <>'PON') THEN
1772 SELECT papf.full_name
1773 INTO l_buyer_name
1774 FROM PER_ALL_PEOPLE_F papf
1775 where papf.person_id =l_buyer_id
1776 AND TRUNC(SYSDATE)
1777 BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE;
1778 ELSE
1779 PON_AUCTION_INTERFACE_PKG.Get_Negotiation_Owner(p_document_id,
1780 l_ownerName,
1781 l_result,
1782 l_error_code,
1783 l_error_msg);
1784 IF(l_ownerName is not null) THEN
1785
1786 select full_name
1787 into l_buyer_name
1788 from fnd_user usr,
1789 per_all_people_f papf
1790 where usr.user_name = l_ownerName
1791 AND usr.employee_id = papf.person_id
1792 AND TRUNC(SYSDATE)
1793 BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE;
1794
1795
1796 END IF;
1797 END IF;
1798 EXCEPTION
1799 WHEN OTHERS THEN
1800 NULL;
1801 END;
1802
1803
1804 l_itemkey := to_char(p_document_id)||'-'||to_char(l_sequence);
1805
1806 wf_engine.CreateProcess(itemtype => l_itemtype,
1807 itemkey => l_itemkey,
1808 process => 'NEG_LINE_DELETED');
1809
1810 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1811 itemkey => l_itemkey,
1812 aname => 'REQ_NUM',
1813 avalue => l_req_num);
1814
1815 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1816 itemkey => l_itemkey,
1817 aname => 'REQ_LINE_NUM',
1818 avalue => l_req_line_num);
1819
1820 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1821 itemkey => l_itemkey,
1822 aname => 'REQ_LINE_DESCRIPTION',
1823 avalue => l_req_line_description);
1824
1825 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1826 itemkey => l_itemkey,
1827 aname => 'BUYER_NAME',
1828 avalue => l_buyer_name);
1829
1830 IF(p_caller='PON') THEN
1831
1832 SELECT type_name
1833 INTO l_doc_type_dsp
1834 FROM po_document_types_vl
1835 WHERE document_type_code = 'SOLICITATION'
1836 AND document_subtype = 'SOLICITATION'
1837 AND ORG_ID = Nvl(PO_MOAC_UTILS_PVT.GET_CURRENT_ORG_ID, FND_GLOBAL.ORG_ID);
1838
1839 ELSE
1840
1841 SELECT Decode(p_caller,'PO_MOD', type_name ||' '|| FND_MESSAGE.GET_STRING('PO','PO_MODIFICATION') ,type_name)
1842 INTO l_doc_type_dsp
1843 FROM po_document_types_vl pdt,
1844 po_headers_draft_all poh
1845 WHERE pdt.document_type_code IN ('PO', 'PA')
1846 AND pdt.ORG_ID =Nvl(PO_MOAC_UTILS_PVT.GET_CURRENT_ORG_ID,
1847 FND_GLOBAL.ORG_ID)
1848 AND pdt.document_subtype = POH.type_lookup_code
1849 AND poh.draft_id=p_document_id;
1850
1851
1852 END IF;
1853
1854
1855 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1856 itemkey => l_itemkey,
1857 aname => 'DOC_TYPE',
1858 avalue => l_doc_type_dsp);
1859
1860 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1861 itemkey => l_itemkey,
1862 aname => 'DOC_NUMBER',
1863 avalue => l_document_number);
1864
1865 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1866 itemkey => l_itemkey,
1867 aname => 'REQ_OWNER',
1868 avalue => l_username);
1869
1870 wf_engine.SetItemAttrText (itemtype => l_itemtype,
1871 itemkey => l_itemkey,
1872 aname => 'ORIGIN_USER_NAME',
1873 avalue => fnd_global.user_name);
1874
1875 wf_engine.SetItemOwner(itemtype => l_itemtype,
1876 itemkey => l_itemkey,
1877 owner => fnd_global.user_name );
1878
1879 wf_engine.StartProcess(itemtype => l_itemtype,
1880 itemkey => l_itemkey );
1881
1882
1883
1884
1885 END IF;
1886
1887 END LOOP;
1888 EXCEPTION
1889 WHEN OTHERS THEN
1890 x_return_status := FND_API.g_ret_sts_unexp_error;
1891 x_msg_count :=1;
1892 x_msg_data := sqlerrm;
1893 END SEND_LINE_DELETED_NOTIF;
1894
1895
1896 PROCEDURE copy_lines(p_auction_header_id IN NUMBER,
1897 p_src_line_numbers IN PO_TBL_NUMBER,
1898 p_user_pref IN copy_user_preferences,
1899 x_max_line_number OUT NOCOPY NUMBER,
1900 x_result OUT NOCOPY VARCHAR2,
1901 x_error_code OUT NOCOPY VARCHAR2,
1902 x_error_message OUT NOCOPY VARCHAR2) AS
1903 TYPE numbers IS TABLE OF NUMBER;
1904 TYPE flags IS TABLE OF VARCHAR2(1);
1905 l_copied_line_numbers PO_TBL_NUMBER := PO_TBL_NUMBER();
1906 l_src_line_number NUMBER;
1907 count_copy NUMBER := 1;
1908 k NUMBER := 0;
1909
1910 l_need_by_date pon_auction_item_prices_all.clm_need_by_date%TYPE;
1911 l_pop_start_date pon_auction_item_prices_all.NEED_BY_START_DATE%TYPE;
1912 l_pop_end_date pon_auction_item_prices_all.NEED_BY_DATE%TYPE;
1913 l_option_from_date pon_auction_item_prices_all.clm_option_from_date%TYPE;
1914 l_option_to_date pon_auction_item_prices_all.clm_option_to_date%TYPE;
1915 l_clm_option_num pon_auction_item_prices_all.clm_option_num%TYPE;
1916 l_clm_option_indicator pon_auction_item_prices_all.clm_option_indicator%TYPE;
1917 l_clm_info_flag pon_auction_item_prices_all.clm_info_flag%TYPE;
1918
1919 l_line_numbers numbers;
1920 l_max_line_number NUMBER := NULL;
1921 l_info_flags flags;
1922 l_user_id NUMBER;
1923 l_line_number NUMBER;
1924 l_clin_flag VARCHAR2(1);
1925 l_clin_num VARCHAR2(4);
1926 l_max_clin VARCHAR2(10);
1927 l_exhibit_number VARCHAR2(2);
1928 --p_auction_header_id NUMBER := 123985;
1929 --p_clin_line_number NUMBER := 1;
1930 -- following fields used for clin numbering after copying the record
1931 clin_num_tbl po_tbl_varchar100 := po_tbl_varchar100();
1932 next_clin_num VARCHAR2(10);
1933 base_line_numbers numbers := numbers();
1934 copied_base_line_numbers numbers := numbers();
1935 LEN NUMBER;
1936 l_clm_base_option pon_auction_item_prices_all.clm_option_indicator%TYPE;
1937
1938 l_clm_base_line pon_auction_item_prices_all.clm_base_line_num%TYPE;
1939
1940 l_next_option_num pon_auction_item_prices_all.clm_option_num%TYPE;
1941 l_is_option_copy VARCHAR2(1);
1942 l_new_group_line_id NUMBER;
1943 l_new_clins numbers;
1944 l_module_name VARCHAR2(50);
1945 TYPE exhibit_type IS TABLE OF VARCHAR2(2);
1946 l_new_exhibits exhibit_type;
1947 l_exhibits exhibit_type;
1948 exhibit VARCHAR2(2);
1949 BEGIN
1950 l_module_name := 'copy_clin';
1951 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1952 FND_LOG.string (log_level => FND_LOG.level_procedure, module => 'PON_CLM_CLO_UTIL_PKG.' || l_module_name, MESSAGE => 'Entered procedure = '
1953 || l_module_name || ', p_auction_header_id = ' || p_auction_header_id );
1954 END IF;
1955 -- we will return x_max_line_number to the calling method so it will be used
1956 -- in copying subsequent lines if user had selected more than one line to copy.
1957 --IF l_max_line_number IS NULL THEN
1958 SELECT MAX(line_number)
1959 INTO l_max_line_number
1960 FROM pon_auction_item_prices_all
1961 WHERE auction_header_id = p_auction_header_id;
1962
1963 FOR i IN p_src_line_numbers.first .. p_src_line_numbers.last LOOP
1964
1965 IF p_src_line_numbers(i) NOT member OF l_copied_line_numbers THEN
1966 l_src_line_number := p_src_line_numbers(i);
1967
1968 -- Changes for bug 10119899 start. First copy parent line before processing its options/slins.
1969 -- following query selects given source line
1970 SELECT line_number, clm_info_flag,exhibit_number BULK COLLECT
1971 INTO l_line_numbers, l_info_flags,l_exhibits
1972 FROM pon_auction_item_prices_all
1973 WHERE auction_header_id = p_auction_header_id
1974 AND line_number = l_src_line_number;
1975
1976 SELECT fnd_global.user_id INTO l_user_id FROM dual;
1977
1978 -- check whether source line is a clin and whethere it is an option
1979 SELECT Nvl2(group_line_id, 'N', 'Y'),
1980 NVL(group_line_id, l_max_line_number + 1),
1981 DECODE(NVL(clm_option_indicator, 'N'), 'O', 'Y', 'N')
1982 INTO l_clin_flag, l_new_group_line_id, l_is_option_copy
1983 FROM pon_auction_item_prices_all
1984 WHERE auction_header_id = p_auction_header_id
1985 AND line_number = l_src_line_number;
1986
1987 IF l_clin_flag = 'Y' THEN
1988 SELECT pon_clo_renumber_pkg.NEXT_CLIN_NUM_WRAPPER(p_auction_header_id,
1989 'PON')
1990 INTO l_clin_num
1991 FROM dual;
1992
1993 ELSIF l_clin_flag = 'N' THEN
1994 SELECT SUBSTR(line_num_display, 1, 4)
1995 INTO l_clin_num
1996 FROM pon_auction_item_prices_all
1997 WHERE auctioN_header_id = p_auction_header_id
1998 AND line_number = l_src_line_number;
1999 END IF;
2000
2001 l_line_number := l_max_line_Number + 1; -- calculate line_number for the line to be inserted.
2002
2003 copy_line_information(p_auction_header_id,
2004 l_src_line_number,
2005 l_line_number,
2006 l_new_group_line_id,
2007 l_user_id);
2008
2009 /* FND_ATTACHED_DOCUMENTS2_PKG.COPY_ATTACHMENTS(X_from_entity_name => 'PON_AUCTION_ITEM_PRICES_ALL',
2010 X_from_pk1_value => TO_CHAR(p_auction_header_id),
2011 X_from_pk2_value => TO_CHAR(l_src_line_number),
2012 X_to_entity_name => 'PON_AUCTION_ITEM_PRICES_ALL',
2013 X_to_pk1_value => TO_CHAR(p_auction_header_id), -- PK1_VALUE
2014 X_to_pk2_value => TO_CHAR(l_line_number),
2015 X_created_by => l_user_id, -- CREATED_BY
2016 X_last_update_login => fnd_global.login_id -- LAST_UPDATE_LOGIN
2017 ); */
2018
2019 IF (NVL(l_info_flags(1), 'N') = 'N') THEN
2020 /*
2021 copying attributes
2022 */
2023 copy_line_attr_information(p_auction_header_id,
2024 l_src_line_number,
2025 l_line_number,
2026 l_user_id);
2027 copy_price_diff_information(p_auction_header_id,
2028 l_src_line_number,
2029 l_line_number,
2030 l_user_id);
2031 copy_shipments_information(p_auction_header_id,
2032 l_src_line_number,
2033 l_line_number,
2034 l_user_id);
2035 copy_price_factors_information(p_auction_header_id,
2036 l_src_line_number,
2037 l_line_number,
2038 l_user_id);
2039 copy_payments_information(p_auction_header_id,
2040 l_src_line_number,
2041 l_line_number,
2042 l_user_id);
2043
2044 -- if current copied line is a base or option then, corresponding option num and base_line_num will have to be updated
2045 SELECT clm_need_by_date,
2046 need_by_start_date,
2047 need_by_date,
2048 clm_option_from_date,
2049 clm_option_to_date,
2050 clm_option_num,
2051 clm_option_indicator,
2052 clm_info_flag
2053 INTO l_need_by_date,
2054 l_pop_start_date,
2055 l_pop_end_date,
2056 l_option_from_date,
2057 l_option_to_date,
2058 l_clm_option_num,
2059 l_clm_option_indicator,
2060 l_clm_info_flag
2061 FROM pon_auction_item_prices_all
2062 WHERE auction_header_id = p_auction_header_id
2063 AND line_number = l_line_number;
2064
2065 redefault_tx(l_need_by_date,
2066 l_pop_start_date,
2067 l_pop_end_date,
2068 l_option_from_date,
2069 l_option_to_date,
2070 l_clm_option_num,
2071 l_clm_option_indicator,
2072 l_clm_info_flag,
2073 p_user_pref);
2074
2075 IF (Nvl(p_user_pref.maintain_base_line_ref, 'N') = 'N') then
2076 l_clm_base_line := NULL;
2077 l_clm_option_num := NULL;
2078
2079 ELSE
2080 l_clm_base_line := l_src_line_number;
2081 END IF;
2082
2083 UPDATE pon_auction_item_prices_all
2084 SET clm_need_by_date = l_need_by_date,
2085 need_by_start_date = l_pop_start_date,
2086 need_by_date = l_pop_end_date,
2087
2088 clm_option_from_date = Nvl2(l_exhibits(1),NULL,l_option_from_date),
2089 clm_option_to_date = Nvl2(l_exhibits(1),NULL,l_option_to_date),
2090 clm_option_num = Nvl2(l_exhibits(1),NULL,l_clm_option_num),
2091 clm_option_indicator = Nvl2(l_exhibits(1),NULL,l_clm_option_indicator),
2092 -- clm_info_flag = l_clm_info_flag ,
2093 clm_base_line_num = Nvl2(l_exhibits(1),NULL,l_clm_base_line)
2094 WHERE auction_header_id = p_auction_header_id
2095 AND line_number = l_line_number;
2096
2097 END IF;
2098
2099 l_copied_line_numbers.extend();
2100 count_copy := l_copied_line_numbers.count();
2101 l_copied_line_numbers(count_copy) := l_src_line_number;
2102
2103 --END LOOP; -- loop over lines in the clin
2104 -- Changes for bug 10119899 end.
2105
2106 IF (Nvl(p_user_pref.copy_as_structure, 'N') = 'Y') then
2107
2108 /*******************************************************************/
2109 -- bug 10119899. After base line is copied now process slins and options
2110 -- following query selects sub lines and options for the input source line
2111 SELECT line_number, clm_info_flag BULK COLLECT
2112 INTO l_line_numbers, l_info_flags
2113 FROM pon_auction_item_prices_all
2114 WHERE auction_header_id = p_auction_header_id
2115 AND (group_line_id = l_src_line_number)
2116 ORDER BY line_num_display;
2117 --l_max_line_Number := l_max_line_Number + 1;
2118
2119 k := 0;
2120
2121 FOR j IN 1 .. l_line_numbers.Count LOOP
2122
2123 k := k + 1;
2124 l_line_number := l_max_line_Number + j + 1; -- calculate line_number for the line to be inserted.
2125
2126 copy_line_information(p_auction_header_id,
2127 l_line_numbers(j),
2128 l_line_number,
2129 l_new_group_line_id,
2130 l_user_id);
2131
2132 l_copied_line_numbers.extend();
2133 count_copy := l_copied_line_numbers.count();
2134 l_copied_line_numbers(count_copy) := l_line_numbers(j);
2135
2136 /* FND_ATTACHED_DOCUMENTS2_PKG.COPY_ATTACHMENTS(X_from_entity_name => 'PON_AUCTION_ITEM_PRICES_ALL',
2137 X_from_pk1_value => TO_CHAR(p_auction_header_id),
2138 X_from_pk2_value => TO_CHAR(l_line_numbers(i)),
2139 X_to_entity_name => 'PON_AUCTION_ITEM_PRICES_ALL',
2140 X_to_pk1_value => TO_CHAR(p_auction_header_id), -- PK1_VALUE
2141 X_to_pk2_value => TO_CHAR(l_line_number),
2142 X_created_by => l_user_id, -- CREATED_BY
2143 X_last_update_login => fnd_global.login_id -- LAST_UPDATE_LOGIN
2144 ); */
2145 IF (NVL(l_info_flags(j), 'N') = 'N') THEN
2146 /*
2147 copying attributes
2148 */
2149 copy_line_attr_information(p_auction_header_id,
2150 l_line_numbers(j),
2151 l_line_number,
2152 l_user_id);
2153 copy_price_diff_information(p_auction_header_id,
2154 l_line_numbers(j),
2155 l_line_number,
2156 l_user_id);
2157 copy_shipments_information(p_auction_header_id,
2158 l_line_numbers(j),
2159 l_line_number,
2160 l_user_id);
2161 copy_price_factors_information(p_auction_header_id,
2162 l_line_numbers(j),
2163 l_line_number,
2164 l_user_id);
2165 copy_payments_information(p_auction_header_id,
2166 l_line_numbers(j),
2167 l_line_number,
2168 l_user_id);
2169
2170 -- if current copied line is a base or option then,corresponding option num and base_line_num will have to be updated
2171 SELECT clm_need_by_date,
2172 need_by_start_date,
2173 need_by_date,
2174 clm_option_from_date,
2175 clm_option_to_date,
2176 clm_option_num,
2177 clm_option_indicator,
2178 clm_info_flag
2179 INTO l_need_by_date,
2180 l_pop_start_date,
2181 l_pop_end_date,
2182 l_option_from_date,
2183 l_option_to_date,
2184 l_clm_option_num,
2185 l_clm_option_indicator,
2186 l_clm_info_flag
2187 FROM pon_auction_item_prices_all
2188 WHERE auction_header_id = p_auction_header_id
2189 AND line_number = l_line_number;
2190
2191 redefault_tx(l_need_by_date,
2192 l_pop_start_date,
2193 l_pop_end_date,
2194 l_option_from_date,
2195 l_option_to_date,
2196 l_clm_option_num,
2197 l_clm_option_indicator,
2198 l_clm_info_flag,
2199 p_user_pref);
2200
2201 IF (Nvl(p_user_pref.maintain_base_line_ref, 'N') = 'N') THEN
2202 l_clm_base_line := NULL;
2203 l_clm_option_num := NULL;
2204 ELSE
2205 l_clm_base_line := l_line_numbers(j);
2206 END IF;
2207
2208 UPDATE pon_auction_item_prices_all
2209 SET clm_need_by_date = l_need_by_date,
2210 need_by_start_date = l_pop_start_date,
2211 need_by_date = l_pop_end_date,
2212
2213 clm_option_from_date = l_option_from_date,
2214 clm_option_to_date = l_option_to_date,
2215 clm_option_num = l_clm_option_num,
2216 clm_option_indicator = l_clm_option_indicator,
2217 -- clm_info_flag = l_clm_info_flag ,
2218 clm_base_line_num = l_clm_base_line
2219 WHERE auction_header_id = p_auction_header_id
2220 AND line_number = l_line_number;
2221
2222 END IF;
2223
2224 -- END IF;
2225
2226 END LOOP; -- loop over lines in the clin
2227
2228 END IF;
2229
2230 IF l_clin_flag = 'Y' THEN
2231 -- if line being copied is a clin, then we need to find next clin number for it.
2232 BEGIN
2233 SELECT line_num_display BULK COLLECT
2234 INTO clin_num_tbl
2235 FROM pon_auction_item_prices_all
2236 WHERE auctioN_header_id = p_auction_header_id
2237 AND group_line_id IS NULL
2238 ORDER BY line_num_display;
2239 EXCEPTION
2240 WHEN No_Data_Found THEN
2241 --l_max_clin := '0000';
2242 clin_num_tbl := po_tbl_varchar100();
2243 END;
2244 next_clin_num := pon_clo_renumber_pkg.next_clin_num(clin_num_tbl);
2245 -- this query selects new copied clin and its optional clins if any
2246 SELECT line_number,exhibit_number BULK COLLECT
2247 INTO l_new_clins,l_new_exhibits
2248 FROM pon_auction_item_prices_all
2249 WHERE auction_header_id = p_auction_header_id
2250 AND line_number > l_max_line_Number
2251 AND group_line_id IS NULL;
2252 --ORDER BY line_number;
2253 FOR i IN 1 .. l_new_clins.Count LOOP
2254
2255
2256 IF(l_new_exhibits(i) IS NOT NULL) THEN
2257 exhibit := l_new_exhibits(i);
2258 next_clin_num := pon_clo_renumber_pkg.NEXT_ELIN_NUM_WRAPPER2(p_auction_header_id,'PON',exhibit);
2259 END IF;
2260
2261
2262 UPDATE pon_auction_item_prices_all
2263 SET line_num_display = next_clin_num
2264 WHERE auction_header_id = p_auction_header_id
2265 AND line_number = l_new_clins(i);
2266
2267 IF(l_new_exhibits(i) IS NULL) THEN
2268 pon_clo_renumber_pkg.RenumberSlinStructure('PON',
2269 p_auction_header_id,
2270 next_clin_num,
2271 x_result);
2272 -- get subsequent clin numbers
2273 clin_num_tbl.extend();
2274 LEN := clin_num_tbl.Count;
2275 clin_num_tbl(LEN) := next_clin_num;
2276 next_clin_num := pon_clo_renumber_pkg.next_clin_num(clin_num_tbl);
2277 END IF;
2278 END LOOP;
2279 /* UPDATE pon_auction_item_prices_all
2280 SET line_num_display = next_clin_num
2281 WHERE auction_header_id = p_auction_header_id
2282 AND line_number = l_max_line_Number + 1; -- this value would have been assigned to the new copied clin
2283 */
2284 --pon_clo_renumber_pkg.RenumberSlinStructure('PON',p_auction_header_id,next_clin_num,x_result);
2285 ELSE
2286 -- line being copied is a slin
2287 pon_clo_renumber_pkg.RenumberSlinStructure('PON',
2288 p_auction_header_id,
2289 l_clin_num,
2290 x_result);
2291 END IF;
2292 --x_max_line_number := Nvl(x_max_line_number,l_max_line_number) + l_line_numbers.Count + 1;
2293 l_max_line_number := l_max_line_number + k + 1;
2294 END IF;
2295 END LOOP;
2296
2297 SELECT Count(*)
2298 INTO x_max_line_number
2299 FROM pon_auction_item_prices_all
2300 WHERE auction_header_id = p_auction_header_id;
2301
2302 END copy_lines;
2303
2304 PROCEDURE redefault_tx(x_need_by_date IN OUT NOCOPY DATE,
2305 x_pop_start_date IN OUT NOCOPY DATE,
2306 x_pop_end_date IN OUT NOCOPY DATE,
2307 x_option_from_date IN OUT NOCOPY DATE,
2308 x_option_to_date IN OUT NOCOPY DATE,
2309 x_clm_option_num IN OUT NOCOPY NUMBER,
2310 x_clm_option_indicator IN OUT NOCOPY VARCHAR2,
2311 x_clm_info_flag IN OUT NOCOPY VARCHAR2,
2312 p_user_pref IN copy_user_preferences) AS
2313 l_date DATE;
2314 l_unit VARCHAR2(10);
2315 l_incr_value NUMBER;
2316 BEGIN
2317
2318 IF (p_user_pref.needbypop_def_pref = 'SPECIFIC') THEN
2319 IF x_need_by_date IS NOT NULL THEN
2320 x_need_by_date := p_user_pref.needbydate_spec;
2321 ELSE
2322 IF x_pop_start_date IS NOT NULL THEN
2323 x_pop_start_date := p_user_pref.popstartdate_spec;
2324 END IF;
2325 IF x_pop_end_date IS NOT NULL THEN
2326 x_pop_end_date := p_user_pref.popenddate_spec;
2327 END IF;
2328 END IF;
2329 elsif (p_user_pref.needbypop_def_pref = 'DERIVED') THEN
2330 IF x_need_by_date IS NOT NULL THEN
2331 l_unit := p_user_pref.needbydate_incr_unit;
2332 l_incr_value := p_user_pref.needbydate_incr_value;
2333 l_date := calculateDates(x_need_by_date,
2334 l_unit,
2335 l_incr_value,
2336 TRUE);
2337 x_need_by_date := l_date;
2338 END IF;
2339 IF (x_pop_start_date IS NOT NULL AND x_pop_end_date IS NOT NULL) THEN
2340 l_unit := p_user_pref.popdate_incr_unit;
2341 l_incr_value := p_user_pref.popdate_incr_value;
2342 l_date := calculateDates(x_pop_start_date,
2343 l_unit,
2344 l_incr_value,
2345 TRUE);
2346 x_pop_start_date := l_date;
2347 l_date := calculateDates(x_pop_end_date,
2348 l_unit,
2349 l_incr_value,
2350 TRUE);
2351 x_pop_end_date := l_date;
2352 END IF;
2353 END IF;
2354
2355 --IF(p_user_pref.copy_as_option !='Y') THEN
2356 /* IF (Nvl(p_user_pref.copy_as_option, 'N') = 'N') THEN
2357 IF (x_clm_option_indicator = 'O') THEN
2358 x_option_from_date := NULL;
2359 x_option_to_date := NULL;
2360 x_clm_option_num := NULL;
2361 x_clm_option_indicator := NULL;
2362
2363 END IF; */
2364
2365 IF (Nvl(p_user_pref.copy_as_option, 'N') = 'Y') THEN
2366 IF (p_user_pref.optiondate_def_pref = 'SPECIFIC') THEN
2367 x_option_from_date := p_user_pref.optionfromdate_spec;
2368 x_option_to_date := p_user_pref.optiontodate_spec;
2369 elsif (p_user_pref.optiondate_def_pref = 'DERIVED') THEN
2370 IF x_need_by_date IS NOT NULL THEN
2371 l_unit := p_user_pref.needbydate_incr_unit;
2372 l_incr_value := p_user_pref.needbydate_incr_value;
2373 l_date := calculateDates(x_need_by_date,
2374 l_unit,
2375 l_incr_value,
2376 FALSE);
2377 x_option_from_date := l_date;
2378 --end if;
2379 elsif (x_pop_start_date IS NOT NULL) THEN
2380 l_unit := p_user_pref.optionfrompop_incr_unit;
2381 l_incr_value := p_user_pref.optionfrompop_incr_value;
2382 l_date := calculateDates(x_pop_start_date,
2383 l_unit,
2384 l_incr_value,
2385 FALSE);
2386 x_option_from_date := l_date;
2387 END IF;
2388 /* IF x_option_from_date IS NULL THEN
2389 x_option_from_date := SYSDATE;
2390 END IF; */
2391
2392 --if x_option_from_date IS NOT NULL then
2393 l_unit := p_user_pref.optionto_incr_unit;
2394 l_incr_value := p_user_pref.optionto_incr_value;
2395 l_date := calculateDates(x_option_from_date,
2396 l_unit,
2397 l_incr_value,
2398 TRUE);
2399 x_option_to_date := l_date;
2400
2401 -- END IF;
2402 END IF;
2403
2404 IF (Nvl(x_clm_info_flag, 'N') = 'N') THEN
2405 x_clm_option_indicator := 'O';
2406 END IF;
2407 END IF;
2408 END redefault_tx;
2409
2410 Function calculatedates(p_user_date IN DATE,
2411 p_unit IN VARCHAR2,
2412 p_incr_value IN NUMBER,
2413 p_flag IN BOOLEAN)
2414 RETURN date AS
2415
2416 l_user_date DATE;
2417 l_incr_value number;
2418 BEGIN
2419
2420 IF p_user_date IS NOT NULL THEN
2421 l_incr_value := p_incr_value;
2422 IF NOT p_flag THEN
2423 l_incr_value := -p_incr_value;
2424 END IF;
2425 IF p_unit = 'DAY' THEN
2426 l_user_date := p_user_date + l_incr_value;
2427 elsif p_unit = 'MONTH' THEN
2428 l_user_date := ADD_MONTHS(p_user_date, l_incr_value);
2429 elsif p_unit = 'YEAR' THEN
2430 l_user_date := ADD_MONTHS(p_user_date, (12 * l_incr_value));
2431 END IF;
2432 END IF;
2433
2434 RETURN l_user_date;
2435
2436 END calculateDates;
2437
2438
2439
2440
2441 END PON_CLM_CLO_UTIL_PKG;