1 PACKAGE BODY OKC_QP_UPGRADE_PUB AS
2 /* $Header: OKCPQPUB.pls 120.0 2005/05/25 22:36:14 appldev noship $ */
3 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' OKC_QP_UPGRADE_PUB.'; -- Global package name
9 --
10 g_error_exception exception;
11 g_category_code okc_subclasses_b.code%TYPE;
12 g_dflt_price_list_id number;
13 g_k_price_list_id number;
14
15 FUNCTION get_seq_id RETURN NUMBER;
16
17 FUNCTION get_item_to_price_flag (p_lse_id IN NUMBER ) RETURN VARCHAR2;
18
19 /*----------------------------------------------------------------------------
20 PROCEDURE upgrade_contracts
21 ----------------------------------------------------------------------------*/
22 PROCEDURE upgrade_contracts
23 (
24 errbuf OUT NOCOPY VARCHAR2,
25 retcode OUT NOCOPY VARCHAR2,
26 p_dflt_price_list_id IN NUMBER,
27 p_category_code IN okc_subclasses_b.code%TYPE ,
28 p_enable_qp_profile IN VARCHAR2 ,
29 p_rpt_upgrade_status IN VARCHAR2
30 )
31 IS
32 /*
33 This is the concurrent program called with the following parameters :
34
35 1. p_dflt_price_list_id : This is a Required Parameter. If the priced lines don't have any pricing
36 rules attached to them or any level above them and if there is no pricing rule attached at the
37 contract header , price_list_id for the priced lines will be defaulted to the user entered
38 parameter p_dflt_price_list_id
39
40 Validation :
41 This parameter is required.
42
43 2. p_category_code : The user can specify the category of the contracts which he wants to upgrade.
44 If he user does not specify the category, the concurrent program will fetch
45 ALL categories for core contracts of classes belonging to OKC or OKO and run the upgrade.
46
47 Validation:
48 At any given point of time, only one concurrent program can be in progress for a category.
49 If the user specifies a category whose upgrade is in Progress i.e completion_flag = P
50 we will skip that category
51
52 3. p_enable_qp_profile : After all the contracts have been upgraded and the user has run
53 the exceptions scripts and verified the report, the user can use this option to enable
54 the OKC Advanced Pricing Profile option.
55
56 Validation:
57 This option can only be used only after all the contracts have been successfully upgraded.
58
59 4. p_rpt_upgrade_status : This will Report all the categories that have been upgraded and
60 list any categories that are not yet upgraded.
61
62 Validation:
63 None
64
65 */
66
67 -- local variables and cursors
68
69 l_proc varchar2(72) := g_package||'upgrade_contracts';
70
71 -- sessionid for log
72 CURSOR csr_sessionid IS
73 SELECT USERENV('sessionid')
74 FROM dual;
75
76 l_sessionid number;
77
78 BEGIN
79
80 IF (l_debug = 'Y') THEN
81 okc_debug.Set_Indentation(l_proc);
82 okc_debug.Log('10: Entering ',2);
83 END IF;
84
85 IF NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N') = 'Y' THEN
86 okc_util.init_trace;
87 fnd_file.put_line(FND_FILE.LOG,'Trace Mode is Enabled');
88 END IF;
89
90 fnd_file.put_line(FND_FILE.LOG,' ---------------------------------------------------------- ');
91 fnd_file.put_line(FND_FILE.LOG,' Starting Concurrent Program ... ');
92 fnd_file.put_line(FND_FILE.LOG,' ---------------------------------------------------------- ');
93 fnd_file.put_line(FND_FILE.LOG,' ');
94 fnd_file.put_line(FND_FILE.LOG,' ');
95
96 IF (l_debug = 'Y') THEN
97 okc_debug.Log('20: p_dflt_price_list_id : '||p_dflt_price_list_id,2);
98 okc_debug.Log('20: p_category_code : '||p_category_code,2);
99 okc_debug.Log('20: p_enable_qp_profile : '||p_enable_qp_profile,2);
100 okc_debug.Log('20: p_rpt_upgrade_status : '||p_rpt_upgrade_status,2);
101 END IF;
102
103
104 -- get the sessionid
105 OPEN csr_sessionid;
106 FETCH csr_sessionid INTO l_sessionid;
107 CLOSE csr_sessionid;
108
109 IF (l_debug = 'Y') THEN
110 okc_debug.Log('25: Session ID : '||l_sessionid,2);
111 okc_debug.Log('25: User ID : '||fnd_global.user_id,2);
112 okc_debug.Log('25: Conc Req ID : '||fnd_global.conc_request_id,2);
113 END IF;
114
115 -- log session details
116 fnd_file.put_line(FND_FILE.LOG,' ********** DATABASE TRACE INFORMATION*************** ');
117 fnd_file.put_line(FND_FILE.LOG,'Session id : '||l_sessionid);
118 fnd_file.put_line(FND_FILE.LOG,'User id : '||fnd_global.user_id);
119 fnd_file.put_line(FND_FILE.LOG,'Conc Req id : '||fnd_global.conc_request_id);
120 fnd_file.put_line(FND_FILE.LOG,' **************************************************** ');
121 fnd_file.put_line(FND_FILE.LOG,' ');
122 fnd_file.put_line(FND_FILE.LOG,' ');
123
124 -- log messages with parameters
125 fnd_file.put_line(FND_FILE.LOG,' ********* Program Parameters **************** ');
126 fnd_file.put_line(FND_FILE.LOG,'Default Price List : '||p_dflt_price_list_id);
127 fnd_file.put_line(FND_FILE.LOG,'Category Code : '||p_category_code);
128 fnd_file.put_line(FND_FILE.LOG,'Enable QP Profile : '||p_enable_qp_profile);
129 fnd_file.put_line(FND_FILE.LOG,'Upgrade Status Rpt : '||p_rpt_upgrade_status);
130 fnd_file.put_line(FND_FILE.LOG,' **************************************************** ');
131 fnd_file.put_line(FND_FILE.LOG,' ');
132 fnd_file.put_line(FND_FILE.LOG,' ');
133
134 -- assign the global variable value for default price list
135 g_dflt_price_list_id := p_dflt_price_list_id;
136
137 -- check if profile value and abort if Y
138 IF NVL(check_qp_profile,'N') = 'N' THEN
139
140 -- check if OKC_QP_UPGRADE modifier is defined
141 IF NVL(check_modifier,'N') = 'Y' THEN
142
143 -- process params
144 /*
145 Parameters are processed in the following order :
146 1. Category
147 2. Enable Profile
148 3. Run Report
149 */
150
151 -- Parameter 1 : Category
152
153 -- Run Upgrade for category
154
155 IF (l_debug = 'Y') THEN
156 okc_debug.Log('500: Calling call_qp_upgrade ',2);
157 END IF;
158
159 call_qp_upgrade
160 (
161 p_category_code => p_category_code
162 );
163
164 -- Parameter 2 : Enable Profile
165 IF NVL(p_enable_qp_profile,'N') = 'Y' THEN
166 -- update the summary to complete and enable the profile
167 upd_summary_rec;
168 END IF;
169
170
171 END IF; -- modifier is defined
172
173 END IF; -- profile value is N
174
175 -- Upgrade Report Can always be run even if the Profile is already set to Y
176
177 -- Parameter 3 : Upgrade Report
178 IF NVL(p_rpt_upgrade_status,'N') = 'Y' THEN
179 -- process report
180 process_report;
181 END IF;
182
183
184
185 fnd_file.put_line(FND_FILE.LOG,' ---------------------------------------------------------- ');
186 fnd_file.put_line(FND_FILE.LOG,'Completed Concurrent Program. ');
187 fnd_file.put_line(FND_FILE.LOG,' ---------------------------------------------------------- ');
188
189 IF (l_debug = 'Y') THEN
190 okc_debug.Log('500: Calling Stop Trace and Leaving ',2);
191 okc_debug.Log('1000: Leaving ',2);
192 END IF;
193
194 IF NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N') = 'Y' THEN
195 -- print debug information
196 fnd_file.put_line(FND_FILE.LOG,' ');
197 fnd_file.put_line(FND_FILE.LOG,' ');
198 fnd_file.put_line(FND_FILE.LOG,' ');
199 fnd_file.put_line(FND_FILE.LOG,' ');
200 fnd_file.put_line(FND_FILE.LOG,' ********** Debug Messages ********************* ');
201 fnd_file.put_line(FND_FILE.LOG,'For Debug Messages run the following SQL : ');
202 fnd_file.put_line(FND_FILE.LOG,'SELECT * FROM fnd_log_messages WHERE user_id = '||fnd_global.user_id||
203 ' AND session_id = '||l_sessionid||' ORDER BY log_sequence; ');
204 fnd_file.put_line(FND_FILE.LOG,' *********************************************** ');
205 fnd_file.put_line(FND_FILE.LOG,' ');
206 fnd_file.put_line(FND_FILE.LOG,' ');
207 -- stop trace mode
208 okc_util.stop_trace;
209 END IF;
210
211 IF (l_debug = 'Y') THEN
212 okc_debug.Reset_Indentation;
213 END IF;
214
215
216 EXCEPTION
217 WHEN g_error_exception THEN
218 IF (l_debug = 'Y') THEN
219 okc_debug.Log('2000: Leaving ',2);
220 okc_debug.Reset_Indentation;
221 END IF;
222 -- update the Category record as done with error
223 upd_category_rec
224 (
225 p_category_code => g_category_code,
226 p_status => 'N'
227 );
228 raise;
229 WHEN others THEN
230 IF (l_debug = 'Y') THEN
231 okc_debug.Log('3000: Leaving ',2);
232 okc_debug.Reset_Indentation;
233 END IF;
234 raise;
235 END upgrade_contracts;
236
237
238
239 /*----------------------------------------------------------------------------
240 PROCEDURE ins_summary_rec
241 ----------------------------------------------------------------------------*/
242 PROCEDURE ins_summary_rec
243 IS
244 /*
245 This procedure will insert record in okc_qp_upgrade process with line_type
246 as SUMMARY.
247 There can be only one record in okc_qp_upgrade with line_type=SUMMARY.
248 If the record already exists then this procedure will not do any thing.
249 */
250 -- local variables and cursors
251
252 l_proc varchar2(72) := g_package||'ins_summary_rec';
253 l_qp_upgrade_rec okc_qp_upgrade%ROWTYPE;
254
255 CURSOR csr_summary_rec IS
256 SELECT *
257 FROM okc_qp_upgrade
258 WHERE line_type='SUMMARY';
259
260 BEGIN
261
262 IF (l_debug = 'Y') THEN
263 okc_debug.Set_Indentation(l_proc);
264 okc_debug.Log('10: Entering ',2);
265 END IF;
266 /*
267 fnd_file.put_line(FND_FILE.LOG,' ');
268 fnd_file.put_line(FND_FILE.LOG,' ');
269 fnd_file.put_line(FND_FILE.LOG,' ================================================ ');
270 fnd_file.put_line(FND_FILE.LOG,' ins_summary_rec ');
271 fnd_file.put_line(FND_FILE.LOG,' ================================================ ');
272 fnd_file.put_line(FND_FILE.LOG,' ');
273 fnd_file.put_line(FND_FILE.LOG,' ');
274 */
275
276 OPEN csr_summary_rec;
277 FETCH csr_summary_rec INTO l_qp_upgrade_rec;
278 IF csr_summary_rec%NOTFOUND THEN
279 IF (l_debug = 'Y') THEN
280 okc_debug.Log('20: Summary Record Not Found, Inserting ... ',2);
281 END IF;
282 -- record does not exist, so insert
283 INSERT INTO okc_qp_upgrade
284 (
285 LINE_TYPE,
286 CREATION_DATE,
287 LAST_UPDATE_DATE,
288 COMPLETION_FLAG,
289 SCS_CODE,
290 CHR_ID,
291 REQUEST_ID,
292 CREATED_BY,
293 LAST_UPDATED_BY,
294 LAST_UPDATE_LOGIN
295 )
296 VALUES
297 (
298 'SUMMARY',
299 sysdate,
300 sysdate,
301 'N',
302 NULL,
303 NULL,
304 fnd_global.conc_request_id,
305 fnd_global.user_id,
306 fnd_global.user_id,
307 fnd_global.conc_login_id
308 );
309
310 -- commit the record
311 commit;
312
313 IF (l_debug = 'Y') THEN
314 okc_debug.Log('30: Inserted Summary Record ',2);
315 END IF;
316 -- fnd_file.put_line(FND_FILE.LOG,'Inserted Summary Record');
317
318 ELSE
319 -- Summary Record found, don't insert
320 IF (l_debug = 'Y') THEN
321 okc_debug.Log('40: Summary Record FOUND, skipping insert ',2);
322 END IF;
323 -- fnd_file.put_line(FND_FILE.LOG,'Summary Record Already Exits');
324 END IF; -- record does not exist
325 CLOSE csr_summary_rec;
326
327
328 IF (l_debug = 'Y') THEN
329 okc_debug.Log('1000: Leaving ',2);
330 okc_debug.Reset_Indentation;
331 END IF;
332
333 EXCEPTION
334 WHEN others THEN
335 IF (l_debug = 'Y') THEN
336 okc_debug.Log('2000: Leaving ',2);
337 okc_debug.Reset_Indentation;
338 END IF;
339 fnd_message.set_name('OKC','OKC_CATASTROPHIC_ERROR');
340 fnd_message.set_token('ROUTINE',l_proc);
341 fnd_message.set_token('REASON',SQLERRM);
342 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
343 raise g_error_exception;
344 END ins_summary_rec;
345
346
347 /*----------------------------------------------------------------------------
348 PROCEDURE ins_category_rec
349 ----------------------------------------------------------------------------*/
350 PROCEDURE ins_category_rec
351 (
352 p_category_code IN okc_subclasses_b.code%TYPE
353 )
354 IS
355 /*
356 This procedure will insert record into okc_qp_upgrade with line_type=CATEGORY
357 There will be only ONE ROW in okc_qp_upgrade for each category.
358 This proc will check if the if record for the category exists
359 ----------------
360 Record Not Found:
361 ----------------
362 1. Insert record for the category
363 2. Commit record
364 3. Call the upgrade of K for this category.
365
366 ----------------
367 Record Found:
368 ----------------
369 Case 1 : completion_flag = 'Y'
370 In this case this category was already upgraded
371 skip this category
372 Case 2 : completion_flag = 'N'
373 In this case there was an error when the conc. pgm was run for the category
374 1. update completion_flag = 'P' -- In Progress
375 2. Commit record
376 3. Call the upgrade of K for this category.
377 Case 3 : completion_flag = 'P'
378 In this case there is another concurrent pgm being run for this category.
379 So we skip this category as only ONE conc. pgm can run at any point of time for a
380 given category
381
382 */
383
384 -- local variables and cursors
385
386 l_proc varchar2(72) := g_package||'ins_category_rec';
387 l_qp_upgrade_rec okc_qp_upgrade%ROWTYPE;
388
389 CURSOR csr_category_rec IS
390 SELECT *
391 FROM okc_qp_upgrade
392 WHERE line_type = 'CATEGORY'
393 AND scs_code = p_category_code
394 FOR UPDATE OF completion_flag;
395
396 l_status varchar2(10);
397
398 BEGIN
399
400 IF (l_debug = 'Y') THEN
401 okc_debug.Set_Indentation(l_proc);
402 okc_debug.Log('10: Entering ',2);
403 okc_debug.Log('15: Category Code : '||p_category_code ,2);
404 END IF;
405 /*
406 fnd_file.put_line(FND_FILE.LOG,' ');
407 fnd_file.put_line(FND_FILE.LOG,' ');
408 fnd_file.put_line(FND_FILE.LOG,' ================================================ ');
409 fnd_file.put_line(FND_FILE.LOG,' ins_category_rec ');
410 fnd_file.put_line(FND_FILE.LOG,' ================================================ ');
411 fnd_file.put_line(FND_FILE.LOG,' ');
412 fnd_file.put_line(FND_FILE.LOG,' ');
413 */
414
415 OPEN csr_category_rec;
416 FETCH csr_category_rec INTO l_qp_upgrade_rec;
417 IF (l_debug = 'Y') THEN
418 okc_debug.set_trace_off;
419 END IF;
420 IF csr_category_rec%NOTFOUND THEN
421 IF (l_debug = 'Y') THEN
422 okc_debug.Log('20: Category Record Not Found, Inserting ... ',2);
423 END IF;
424 -- record does not exist, so insert
425 INSERT INTO okc_qp_upgrade
426 (
427 LINE_TYPE,
428 CREATION_DATE,
429 LAST_UPDATE_DATE,
430 COMPLETION_FLAG,
431 SCS_CODE,
432 CHR_ID,
433 REQUEST_ID,
434 CREATED_BY,
435 LAST_UPDATED_BY,
436 LAST_UPDATE_LOGIN
437 )
438 VALUES
439 (
440 'CATEGORY',
441 sysdate,
442 sysdate,
443 'P',
444 p_category_code,
445 NULL,
446 fnd_global.conc_request_id,
447 fnd_global.user_id,
448 fnd_global.user_id,
449 fnd_global.conc_login_id
450 );
451
452 -- commit the record
453 commit;
454
455 IF (l_debug = 'Y') THEN
456 okc_debug.Log('30: Inserted Category Record for : '||p_category_code,2);
457 okc_debug.Log('35: Calling Upgrade for category : '||p_category_code,2);
458 END IF;
459
460 -- populate the global variable with this cat_code it is processing
461 g_category_code := p_category_code ;
462
463 -- fnd_file.put_line(FND_FILE.LOG,'Starting Upgrade For Category : '||p_category_code);
464
465 -- call upgrade for this category
466 start_category_upgrade
467 (
468 p_category_code => p_category_code
469 );
470 IF (l_debug = 'Y') THEN
471 okc_debug.set_trace_off;
472 END IF;
473
474 l_status := 'Y';
475
476 -- update the Category record as done
477 upd_category_rec
478 (
479 p_category_code => p_category_code,
480 p_status => l_status
481 );
482 IF (l_debug = 'Y') THEN
483 okc_debug.set_trace_off;
484 END IF;
485 fnd_file.put_line(FND_FILE.LOG,'Completed Upgrade For Category : '||p_category_code);
486
487 ELSE
488 -- Category Record found, don't insert
489
490 IF (l_debug = 'Y') THEN
491 okc_debug.Log('40: Category Record FOUND ',2);
492 okc_debug.Log('40: completion_flag : '||l_qp_upgrade_rec.completion_flag,2);
493 END IF;
494
495 IF l_qp_upgrade_rec.completion_flag = 'Y' THEN
496 /*
497 Case 1 : completion_flag = 'Y'
498 In this case this category was already upgraded, so skip
499 */
500 IF (l_debug = 'Y') THEN
501 okc_debug.Log('50: Skipping as category is already Upgraded : '||p_category_code,2);
502 END IF;
503 fnd_message.set_name('OKC','OKC_CATEGORY_UPGRADED');
504 fnd_message.set_token('CATEGORY',p_category_code);
505 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
506
507 ELSIF l_qp_upgrade_rec.completion_flag = 'N' THEN
508 /*
509 Case 2 : completion_flag = 'N'
510 In this case there was an error when the conc. pgm was run for the category
511 */
512 -- 1. update completion_flag = P ,
513 UPDATE okc_qp_upgrade
514 SET completion_flag = 'P'
515 WHERE CURRENT OF csr_category_rec;
516
517
518 -- 2. Commit record
519 commit;
520
521 IF (l_debug = 'Y') THEN
522 okc_debug.Log('60: Updated completion_flag to P for : '||p_category_code,2);
523 okc_debug.Log('70: Calling Upgrade for category : '||p_category_code,2);
524 END IF;
525
526 -- populate the global variable with this cat_code it is processing
527 g_category_code := p_category_code ;
528
529
530 -- fnd_file.put_line(FND_FILE.LOG,'3: Starting Upgrade For Category : '||p_category_code);
531
532 -- 3. Call the upgrade of K for this category.
533 start_category_upgrade
534 (
535 p_category_code => p_category_code
536 );
537
538 IF (l_debug = 'Y') THEN
539 okc_debug.set_trace_off;
540 END IF;
541 -- we ran for all contracts
542 l_status := 'Y';
543
544 -- update the Category record as done
545 upd_category_rec
546 (
547 p_category_code => p_category_code,
548 p_status => l_status
549 );
550 IF (l_debug = 'Y') THEN
551 okc_debug.set_trace_off;
552 END IF;
553 fnd_file.put_line(FND_FILE.LOG,':Completed Upgrade For Category : '||p_category_code);
554
555 ELSE
556 /*
557 Case 3 : completion_flag = 'P'
558 In this case there is another concurrent pgm being run for this category.
559 So we skip this category as only ONE conc. pgm can run at any point of time for a
560 given category
561 */
562 IF (l_debug = 'Y') THEN
563 okc_debug.Log('80: Another conc pgm IN PROGRESS for : '||p_category_code,2);
564 okc_debug.Log('100: Skipping Category : '||p_category_code,2);
565 END IF;
566 fnd_file.put_line(FND_FILE.LOG,' *********************************************** ');
567 fnd_message.set_name('OKC','OKC_CATEGORY_UPG_PROGESS');
568 fnd_message.set_token('CATEGORY',p_category_code);
569 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
570 fnd_file.put_line(FND_FILE.LOG,' *********************************************** ');
571
572 END IF; -- completion_flag
573
574 END IF; -- record does not exist
575 CLOSE csr_category_rec;
576 IF (l_debug = 'Y') THEN
577 okc_debug.set_trace_on;
578 okc_debug.Log('1000: Leaving ',2);
579 okc_debug.Reset_Indentation;
580 END IF;
581
582 EXCEPTION
583 WHEN others THEN
584 IF (l_debug = 'Y') THEN
585 okc_debug.set_trace_on;
586 okc_debug.Log('2000: Leaving ',2);
587 okc_debug.Reset_Indentation;
588 END IF;
589 fnd_message.set_name('OKC','OKC_CATASTROPHIC_ERROR');
590 fnd_message.set_token('ROUTINE',l_proc);
591 fnd_message.set_token('REASON',SQLERRM);
592 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
593 raise g_error_exception;
594 END ins_category_rec;
595
596
597 /*----------------------------------------------------------------------------
598 PROCEDURE start_category_upgrade
599 ----------------------------------------------------------------------------*/
600 PROCEDURE start_category_upgrade
601 (
602 p_category_code IN okc_subclasses_b.code%TYPE
603 )
604 IS
605 /*
606 This procedure will call the upgrade script .
607 we will run upgrade for ALL contracts in p_category_code
608 which have NOT BEEN UPGRADED i.e no entry in okc_qp_upgrade table.
609
610 */
611 -- local variables and cursors
612
613 l_proc varchar2(72) := g_package||'start_category_upgrade';
614
615 --select all the contracts of application OKC and OKO which are of SELL Intent
616 CURSOR chr_cursor IS
617 SELECT chr1.rowid,
618 chr1.id id,
619 chr1.contract_number,
620 chr1.contract_number_modifier,
621 chr1.estimated_amount ,
622 chr1.object_version_number obj
623 FROM okc_k_headers_b chr1
624 WHERE chr1.application_id IN (510,871)
625 AND NVL(chr1.buy_or_sell,'X') = 'S'
626 AND chr1.scs_code = p_category_code
627 AND chr1.id NOT IN
628 (
629 SELECT NVL(chr_id,0)
630 FROM okc_qp_upgrade
631 WHERE line_type = 'CONTRACT'
632 AND scs_code = p_category_code
633 )
634 ;
635
636
637 TYPE num_tbl_type is table of number index by binary_integer;
638 TYPE varchar_tbl_type is table of varchar2(30) index by binary_integer;
639 TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
640
641 l_batch_size number:= 100;
642 l_counter number:= 0;
643 --
644 l_hdr_list_price number; -- holds total list price for the header
645 l_line_list_price number :=0; -- holds list price for the current priced line
646 l_cle_id number; -- holds next line id to be traversed
647 l_qty number :=0;
648 i pls_integer; --used as index for the main loop
649 j pls_integer; --used as index for the inner loop
650 k pls_integer; -- used to hold the index for the priced line
651 l pls_integer; -- generic index, used in searching for the rule on a line
652 l_price_list_id number;
653 l_obj_code varchar2(30);
654 l_hdr_price_list number; -- holds pricelist id for the header, if any
655
656 l_line_rowid_tbl RowIDTab;
657 l_line_id_tbl num_tbl_type; -- holds line ids for a contract header
658 l_level_tbl num_tbl_type; -- holds row level for lines
659 l_cle_id_tbl num_tbl_type; -- holds parent id of the lines
660 l_line_list_price_tbl num_tbl_type; -- holds list price of the line
661 l_price_unit_tbl num_tbl_type; -- holds unit price for the priced line
662 l_priced_flag_tbl varchar_tbl_type; -- holds priced flag value for lines
663 l_price_list_tbl num_tbl_type; -- holds pricelist for the line
664 l_obj_code_tbl varchar_tbl_type; -- holds object version number for the line. needed in update
665 l_rul_line_id_tbl num_tbl_type;
666 l_rul_header_id_tbl num_tbl_type;
667
668 l_rul_pricelist_tbl num_tbl_type;
669 l_rul_object_code_tbl varchar_tbl_type;
670
671 -- skekkar
672 l_itm_to_price_tbl varchar_tbl_type; -- holds item_to_price_yn for lse_id for lines in okc_k_lines_b
673 l_lse_id_tbl num_tbl_type; -- holds lse_id for lines in okc_k_lines_b
674 l_user_estimated_amount number := 0;
675 l_estimated_amount number := 0;
676 -- skekkar
677
678 BEGIN
679
680 IF (l_debug = 'Y') THEN
681 okc_debug.Set_Indentation(l_proc);
682 okc_debug.Log('10: Entering ',2);
683 END IF;
684 /*
685 fnd_file.put_line(FND_FILE.LOG,' ');
686 fnd_file.put_line(FND_FILE.LOG,' ');
687 fnd_file.put_line(FND_FILE.LOG,' ================================================ ');
688 fnd_file.put_line(FND_FILE.LOG,' start_category_upgrade ');
689 fnd_file.put_line(FND_FILE.LOG,' ================================================ ');
690 fnd_file.put_line(FND_FILE.LOG,' ');
691 fnd_file.put_line(FND_FILE.LOG,' ');
692 */
693
694 --for all the contracts selected, rollup list prices and get pricelists for lines
695 FOR chr_rec IN chr_cursor LOOP --#1
696 --
697 l_hdr_price_list := null;
698 l_hdr_list_price:=0;
699 l_level_tbl.delete;
700 l_line_id_tbl.delete;
701 l_cle_id_tbl.delete;
702 l_line_list_price_tbl.delete;
703 l_price_unit_tbl.delete;
704 l_priced_flag_tbl.delete;
705 l_price_list_tbl.delete;
706 l_rul_line_id_tbl .delete;
707 l_rul_pricelist_tbl.delete;
708 l_rul_object_code_tbl.delete;
709
710 l_itm_to_price_tbl.delete;
711 l_lse_id_tbl.delete;
712
713 IF (l_debug = 'Y') THEN
714 okc_debug.Log('20: Contract Record PROCESSING ... ',2);
715 okc_debug.Log('30: Contract Category : '||p_category_code,2);
716 okc_debug.Log('40: Contract Id : '||chr_rec.id,2);
717 okc_debug.Log('50: Contract Number : '||chr_rec.contract_number,2);
718 okc_debug.Log('60: Contract Number Mod : '||chr_rec.contract_number_modifier,2);
719 END IF;
720
721 -- compute the new estimated amount for the contract
722
723 l_user_estimated_amount := NVL(chr_rec.estimated_amount,0);
724 l_estimated_amount := compute_estimated_amt(p_chr_id => chr_rec.id);
725
726 IF (l_debug = 'Y') THEN
727 okc_debug.Log('70: user_estimated_amount : '||l_user_estimated_amount,2);
728 okc_debug.Log('80: estimated_amount : '||l_estimated_amount,2);
729 END IF;
730
731 --???? asumming non configurator rows with only priced flag.
732 --make a heirarachy of lines with for each contract, going from top line to sub lines(leaf node last)
733 -- That means Top line will be level one. Parsing this way as then each line will come only once.
734 -- If the other way round that is leaf node first, top lines with more than one children will come
735 -- multiple times
736
737 SELECT ROWID
738 ,level
739 ,id
740 ,cle_id
741 ,line_list_price
742 ,price_unit
743 ,price_level_ind
744 ,price_list_id
745 ,lse_id
746 BULK COLLECT INTO
747 l_line_rowid_tbl
748 ,l_level_tbl
749 ,l_line_id_tbl
750 ,l_cle_id_tbl
751 ,l_line_list_price_tbl
752 ,l_price_unit_tbl
753 ,l_priced_flag_tbl
754 ,l_price_list_tbl
755 ,l_lse_id_tbl
756 FROM okc_k_lines_b
757 CONNECT BY (prior id = cle_id AND dnz_chr_id=chr_rec.id )
758 START WITH chr_id = chr_rec.id;
759
760
761 -- select the object code and id from rule 'PRE' defined at header and lines
762 SELECT rul.object1_id1
763 ,rul.jtot_object1_code
764 ,rgp.chr_id
765 ,rgp.cle_id
766 BULK COLLECT INTO
767 l_rul_pricelist_tbl
768 ,l_rul_object_code_tbl
769 ,l_rul_header_id_tbl
770 ,l_rul_line_id_tbl
771 FROM okc_rules_b rul,
772 okc_rule_groups_b rgp
773 WHERE rul.rgp_id = rgp.id
774 AND rul.rule_information_category = 'PRE'
775 AND rul.dnz_chr_id = chr_rec.id;
776
777 -- skekkar
778 -- for each line fetched get the item_to_price_yn flag
779 FOR i IN NVL(l_lse_id_tbl.FIRST,0)..NVL(l_lse_id_tbl.LAST,-1)
780 LOOP
781 l_itm_to_price_tbl(i) := get_item_to_price_flag(l_lse_id_tbl(i));
782 END LOOP;
783 -- skekkar
784
785 --it is possible to get too many rows for a header or a line
786 --we will only pick up the first record fetched as we are not expecting
787 --more than one pricing rule on header/line
788 -- get the pricelist for the header
789
790 l_price_list_id:=null;
791
792 l:= l_rul_header_id_tbl.first;
793
794 WHILE l IS NOT NULL LOOP -- look for header pricelist
795 IF chr_rec.id = l_rul_header_id_tbl(l) THEN -- if id found
796 l_price_list_id := l_rul_pricelist_tbl(l);
797 l_obj_code := l_rul_object_code_tbl(l);
798 EXIT;
799 END IF; --end if id found
800
801 l:=l_rul_header_id_tbl.next(l);
802
803 END LOOP; --end of loop for header pricelist
804
805 -- if the object_code for the rule value is from okx_price and price_list_id is not null
806 --we will store the current pricelist as header price list
807
808 If l_price_list_id IS NOT NULL AND l_obj_code = 'OKX_PRICE' THEN --#If1
809 l_hdr_price_list:= l_price_list_id;
810 ELSE
811 l_hdr_price_list:= null;
812
813 END IF; --#end If1
814
815 IF (l_debug = 'Y') THEN
816 okc_debug.Log('100: HEADER Price List Id : '||l_hdr_price_list ,2);
817 END IF;
818
819
820 -- For each line defined for this contract, search for priced lines. The while loop below
821 -- will go through each line in the table looking for priced lines
822
823 i:= l_line_id_tbl.FIRST;
824
825 WHILE i IS NOT NULL LOOP --#2 while
826
827 -- If found a priced line, travel up the heirarchy for its parents
828 IF l_priced_flag_tbl(i) = 'Y' THEN -- #if2
829 -- start traversing backwards from the priced line towards the top line
830 j:=i;
831 l_cle_id := l_line_id_tbl(j); -- make looking_for_cle_id (l_cle_id) same as current id
832
833 -- initialize qty and added list price.l_line_list_price hold the list price added
834
835 l_line_list_price:=0; -- initailize priced line list price holder
836 l_qty:=0; -- initalize qty
837 k:=null; -- initialize k
838
839 WHILE j IS NOT NULL LOOP --#3 while
840
841 -- If found the line we are searching for
842 IF l_cle_id = l_line_id_tbl(j) THEN --#if3
843 -- after we are done processing the found line, we will search for its parent
844 --so make the l_cle_id equal to its parent
845 l_cle_id:=l_cle_id_tbl(j);
846
847
848 -----------get list price----------------------------------------------------
849 -- If priced line then calculate list price by multiplying qty and list price
850 IF l_priced_flag_tbl(j) = 'Y' THEN --#if4
851 k:=j; -- holding the index of current priced line in question.needed if
852 -- if pricelist not found on priced line itself, then if found on
853 -- any of its parent, we will use that. hence keeping track of priced line
854
855 IF l_price_unit_tbl(j) IS NOT NULL THEN --#if5
856
857 SELECT NVL(number_of_items,0)
858 INTO l_qty
859 FROM okc_k_items
860 WHERE cle_id=l_line_id_tbl(j);
861
862 l_line_list_price_tbl(j):= NVL(l_qty,0) * NVL(l_price_unit_tbl(j),0);
863
864 IF (l_debug = 'Y') THEN
865 okc_debug.Log('150: Quantity Selected : '||l_qty,2);
866 END IF;
867
868 END IF; --#end if5 price_unit is not null
869
870 l_line_list_price := l_line_list_price_tbl(j);
871
872
873 -- else rollup the list price
874 ELSE -- priced_flag is N
875
876 IF l_line_list_price IS NOT NULL THEN
877 l_line_list_price_tbl(j):=nvl(l_line_list_price_tbl(j),0)+ l_line_list_price;
878 END IF;
879
880 END IF; --#end if4 i.e l_priced_flag_tbl(j) = 'Y'
881
882 IF (l_debug = 'Y') THEN
883 okc_debug.Log('200: Line List Price : '||l_line_list_price,2);
884 END IF;
885
886 -----------end get list price---------------------------------------------------
887
888
889 -----------get pricelist--------------------------------------------------------
890 --get pricelist if not already there. it can be there if the line has alraedy been
891 -- parsed by another sub line
892 IF l_price_list_tbl(j) IS NULL THEN --#if6
893 -- search for the first pricelist for the current line
894 l:= l_rul_line_id_tbl.FIRST;
895
896 WHILE l IS NOT NULL LOOP --#4 while
897
898 IF l_line_id_tbl(j) = l_rul_line_id_tbl(l) AND
899 l_rul_object_code_tbl(l)='OKX_PRICE' THEN --#if 8
900 l_price_list_tbl(j) := l_rul_pricelist_tbl(l);
901 EXIT;
902 END IF; --end If8
903
904 l:=l_rul_line_id_tbl.next(l);
905 END LOOP; -- #4 end while
906
907 END IF; --#end If6
908
909 -- if the current line has apricelist and its priced child doesnot have
910 -- a pricelist, copy this pricelist to the priced child
911 IF l_price_list_tbl(j) IS NOT NULL AND --# if9
912 k IS NOT NULL AND
913 l_price_list_tbl(k) IS NULL THEN
914
915 l_price_list_tbl(k):= l_price_list_tbl(j);
916
917 END IF; --#end if9
918
919 ------------end get pricelist-------------------------------------------------------
920
921 END IF; --#end if3
922
923
924
925 --If already got the top line in the upward traversing from priced line, then come out of loop
926 IF l_level_tbl(j) = 1 THEN --#if10
927
928 -- if the priced line had no pricelist and none of its parents had
929 -- a pricelist, copy the pricelist at header if there is one
930 -- else copy the default price_list_id which is a parameter
931
932 IF l_price_list_tbl(k) IS NULL THEN --#if 11
933
934 IF l_hdr_price_list IS NOT NULL THEN -- skekkar
935 l_price_list_tbl(k) := l_hdr_price_list;
936 ELSE
937 l_price_list_tbl(k) := g_dflt_price_list_id;
938 g_k_price_list_id := g_dflt_price_list_id;
939 IF (l_debug = 'Y') THEN
940 okc_debug.Log('210: Using Default Price list Id ',2);
941 END IF;
942 END IF; -- skekkar
943
944 END IF; --#end if11
945
946 -- quit going up as already reached the top node
947 EXIT;
948
949 END IF; --#end If10
950
951 j:= l_line_id_tbl.prior(j);
952
953 END LOOP; --#3 end while
954
955 -- list price to be rolled up to header
956 IF l_line_list_price IS NOT NULL THEN --#if12
957 l_hdr_list_price:=nvl(l_hdr_list_price,0)+l_line_list_price;
958 END IF; --#end if12
959
960 END IF; --#end if2
961
962 i:= l_line_id_tbl.next(i);
963
964 END LOOP; --#2 end while
965
966
967 -- forall changed lines
968
969 IF l_line_id_tbl.count > 0 THEN --#if12
970
971 FORALL j IN NVL(l_line_id_tbl.FIRST,0)..NVL(l_line_id_tbl.LAST,-1)
972 UPDATE okc_k_lines_b
973 SET object_version_number = object_version_number+1,
974 last_updated_by = -1901903 ,--bug number
975 last_update_date = sysdate ,
976 line_list_price = l_line_list_price_tbl(j),
977 price_list_id = l_price_list_tbl(j),
978 item_to_price_yn = l_itm_to_price_tbl(j),
979 program_application_id = fnd_global.prog_appl_id,
980 program_id = fnd_global.conc_program_id,
981 program_update_date = sysdate,
982 request_id = fnd_global.conc_request_id,
983 pricing_date = sysdate
984 WHERE rowid = l_line_rowid_tbl(j);
985
986 END IF; --#end if12
987
988
989 -- IF (l_hdr_list_price > 0 OR l_hdr_price_list IS NOT NULL ) THEN --#if13
990
991 -- we will update the header even if the l_hdr_list_price and l_hdr_price_list are null
992 -- we update the header with the estimated amt and user estimated amt
993
994 UPDATE okc_k_headers_b
995 SET object_version_number = object_version_number+1,
996 last_updated_by = -1901903 ,--bug number
997 last_update_date = sysdate,
998 total_line_list_price = l_hdr_list_price,
999 price_list_id = l_hdr_price_list,
1000 estimated_amount = NVL(l_estimated_amount,0),
1001 user_estimated_amount = NVL(l_user_estimated_amount,0),
1002 program_application_id = fnd_global.prog_appl_id,
1003 program_id = fnd_global.conc_program_id,
1004 program_update_date = sysdate,
1005 request_id = fnd_global.conc_request_id,
1006 pricing_date = sysdate
1007 WHERE rowid = chr_rec.rowid;
1008
1009
1010 IF (l_debug = 'Y') THEN
1011 okc_debug.Log('400: Updated Header : '||chr_rec.contract_number,2);
1012 END IF;
1013
1014 l_counter:=l_counter+1;
1015
1016 IF (l_debug = 'Y') THEN
1017 okc_debug.Log('410: Counter is : '||l_counter,2);
1018 END IF;
1019
1020 -- END IF; --#end if13
1021
1022 -- check the difference between line_list_price and price_negotiated
1023 -- if there is a difference, create a manual adjustment transaction
1024 create_manual_adjustment
1025 (
1026 p_chr_id => chr_rec.id
1027 );
1028
1029 -- Insert into okc_qp_upgrade the chr_id we processed
1030 ins_contract_rec
1031 (
1032 p_category_code => p_category_code,
1033 p_chr_id => chr_rec.id,
1034 p_contract_number => chr_rec.contract_number,
1035 p_contract_number_modifier => chr_rec.contract_number_modifier
1036 );
1037
1038
1039 IF l_counter >= 100 THEN --#if14
1040
1041 IF (l_debug = 'Y') THEN
1042 okc_debug.Log('420: Commiting Work as Counter is : '||l_counter,2);
1043 END IF;
1044
1045 -- commit work
1046 commit;
1047
1048 IF (l_debug = 'Y') THEN
1049 okc_debug.Log('430: Initializing Counter Again to 0',2);
1050 END IF;
1051
1052 -- initialize counter
1053 l_counter := 0;
1054
1055 END IF; --#end if14 i.e counter > 100
1056
1057
1058 END LOOP; --#1 end for loop
1059
1060
1061 -- commit work
1062 commit;
1063
1064 IF (l_debug = 'Y') THEN
1065 okc_debug.Log('1000: Leaving ',2);
1066 okc_debug.Reset_Indentation;
1067 END IF;
1068
1069 EXCEPTION
1070 WHEN others THEN
1071 IF (l_debug = 'Y') THEN
1072 okc_debug.Log('2000: Leaving ',2);
1073 okc_debug.Reset_Indentation;
1074 END IF;
1075 fnd_message.set_name('OKC','OKC_CATASTROPHIC_ERROR');
1076 fnd_message.set_token('ROUTINE',l_proc);
1077 fnd_message.set_token('REASON',SQLERRM);
1078 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
1079 raise g_error_exception;
1080 END start_category_upgrade;
1081
1082
1083
1084 /*----------------------------------------------------------------------------
1085 PROCEDURE ins_contract_rec
1086 ----------------------------------------------------------------------------*/
1087 PROCEDURE ins_contract_rec
1088 (
1089 p_category_code IN okc_subclasses_b.code%TYPE,
1090 p_chr_id IN okc_k_headers_b.id%TYPE ,
1091 p_contract_number IN okc_k_headers_b.contract_number%TYPE,
1092 p_contract_number_modifier IN okc_k_headers_b.contract_number_modifier%TYPE
1093 )
1094 IS
1095 /*
1096 This procedure will insert record into okc_qp_upgrade table with line_type=CONTRACT
1097 We will insert a row for each contract that we upgrade.
1098 For contracts that have used default price list id at any of the line level, we will
1099 also store the default price list id for those contracts
1100 */
1101
1102 -- local variables and cursors
1103
1104 l_proc varchar2(72) := g_package||'ins_contract_rec';
1105
1106 BEGIN
1107
1108 IF (l_debug = 'Y') THEN
1109 okc_debug.Set_Indentation(l_proc);
1110 okc_debug.Log('10: Entering ',2);
1111 END IF;
1112
1113 IF (l_debug = 'Y') THEN
1114 okc_debug.Log('20: Contract Record Inserting ... ',2);
1115 okc_debug.Log('30: Contract Category : '||p_category_code,2);
1116 okc_debug.Log('40: Contract Id : '||p_chr_id,2);
1117 okc_debug.Log('50: Contract Number : '||p_contract_number,2);
1118 okc_debug.Log('60: Contract Number Mod : '||p_contract_number_modifier,2);
1119 okc_debug.Log('70: Default Price List Id : '||g_k_price_list_id,2);
1120 END IF;
1121
1122 /*
1123 fnd_file.put_line(FND_FILE.LOG,'Processed Contract : '||p_contract_number||
1124 ' '|| p_contract_number_modifier||' '||p_chr_id);
1125 */
1126
1127 -- insert
1128 INSERT INTO okc_qp_upgrade
1129 (
1130 LINE_TYPE,
1131 CREATION_DATE,
1132 LAST_UPDATE_DATE,
1133 COMPLETION_FLAG,
1134 SCS_CODE,
1135 CHR_ID,
1136 DFLT_PRICE_LIST_ID,
1137 REQUEST_ID,
1138 CREATED_BY,
1139 LAST_UPDATED_BY,
1140 LAST_UPDATE_LOGIN
1141 )
1142 VALUES
1143 (
1144 'CONTRACT',
1145 sysdate,
1146 sysdate,
1147 'Y',
1148 p_category_code,
1149 p_chr_id,
1150 g_k_price_list_id,
1151 fnd_global.conc_request_id,
1152 fnd_global.user_id,
1153 fnd_global.user_id,
1154 fnd_global.conc_login_id
1155 );
1156
1157
1158 -- initialize the g_k_price_list_id
1159 g_k_price_list_id := '';
1160
1161
1162 IF (l_debug = 'Y') THEN
1163 okc_debug.Log('1000: Leaving ',2);
1164 okc_debug.Reset_Indentation;
1165 END IF;
1166
1167 EXCEPTION
1168 WHEN others THEN
1169 IF (l_debug = 'Y') THEN
1170 okc_debug.Log('2000: Leaving ',2);
1171 okc_debug.Reset_Indentation;
1172 END IF;
1173 fnd_message.set_name('OKC','OKC_CATASTROPHIC_ERROR');
1174 fnd_message.set_token('ROUTINE',l_proc);
1175 fnd_message.set_token('REASON',SQLERRM);
1176 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
1177 raise g_error_exception;
1178 END ins_contract_rec;
1179
1180
1181 /*----------------------------------------------------------------------------
1182 PROCEDURE call_qp_upgrade
1183 ----------------------------------------------------------------------------*/
1184 PROCEDURE call_qp_upgrade
1185 (
1186 p_category_code IN okc_subclasses_b.code%TYPE
1187 )
1188 IS
1189 /*
1190 This procedure will do the following
1191 1. Check if the upgrade already done successfully, if Yes then abort
1192 2. Insert the Summary Record
1193 3. Call the start_category_upgrade with the p_category_code
1194 4. Update the category record as complete after upgrade
1195 */
1196
1197 -- local variables and cursors
1198
1199 l_proc varchar2(72) := g_package||'call_qp_upgrade';
1200
1201 CURSOR csr_summary_rec IS
1202 SELECT *
1203 FROM okc_qp_upgrade
1204 WHERE line_type='SUMMARY'
1205 AND completion_flag = 'Y';
1206
1207 -- category cursor for all/ specific categories in OKC and OKO
1208 CURSOR csr_category IS
1209 SELECT scs.code
1210 FROM okc_subclasses_b scs,
1211 okc_classes_b cs
1212 WHERE scs.cls_code = cs.code
1213 AND cs.application_id IN (510,871)
1214 AND scs.code = NVL(p_category_code,scs.code) ;
1215
1216
1217 l_code okc_subclasses_b.code%TYPE;
1218 l_qp_upgrade_rec okc_qp_upgrade%ROWTYPE;
1219
1220
1221 BEGIN
1222
1223 IF (l_debug = 'Y') THEN
1224 okc_debug.Set_Indentation(l_proc);
1225 okc_debug.Log('10: Entering ',2);
1226 END IF;
1227 /*
1228 fnd_file.put_line(FND_FILE.LOG,' ');
1229 fnd_file.put_line(FND_FILE.LOG,' ');
1230 fnd_file.put_line(FND_FILE.LOG,' ================================================ ');
1231 fnd_file.put_line(FND_FILE.LOG,' call_qp_upgrade ');
1232 fnd_file.put_line(FND_FILE.LOG,' ================================================ ');
1233 fnd_file.put_line(FND_FILE.LOG,' ');
1234 fnd_file.put_line(FND_FILE.LOG,' ');
1235 */
1236
1237 IF (l_debug = 'Y') THEN
1238 okc_debug.Log('20: p_category_code : '||p_category_code,2);
1239 END IF;
1240
1241 -- check if the upgrade is already done, if Y then abort
1242 OPEN csr_summary_rec;
1243 FETCH csr_summary_rec INTO l_qp_upgrade_rec;
1244 IF csr_summary_rec%NOTFOUND THEN
1245 -- start processing here
1246
1247 IF (l_debug = 'Y') THEN
1248 okc_debug.Log('60: Category Id is : '||p_category_code,2);
1249 END IF;
1250
1251 -- insert the summary record
1252 ins_summary_rec;
1253
1254 -- Start Process for each category
1255 OPEN csr_category;
1256 LOOP
1257 FETCH csr_category INTO l_code;
1258 EXIT WHEN csr_category%NOTFOUND;
1259
1260 -- insert the Category record and start the upgrade
1261 ins_category_rec
1262 (
1263 p_category_code => l_code
1264 );
1265
1266 END LOOP; -- for each category
1267 CLOSE csr_category;
1268
1269
1270 ELSE -- csr_summary_rec FOUND, upgrade already done abort
1271 IF (l_debug = 'Y') THEN
1272 okc_debug.Log('500: Aborting as the Upgrade is already Done ... ',2);
1273 END IF;
1274 fnd_file.put_line(FND_FILE.LOG,' ');
1275 fnd_file.put_line(FND_FILE.LOG,' ');
1276 fnd_message.set_name('OKC','OKC_QP_ALREADY_DONE');
1277 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
1278 fnd_file.put_line(FND_FILE.LOG,' ');
1279 fnd_file.put_line(FND_FILE.LOG,' ');
1280 END IF;
1281
1282
1283 IF (l_debug = 'Y') THEN
1284 okc_debug.Log('1000: Leaving ',2);
1285 okc_debug.Reset_Indentation;
1286 END IF;
1287
1288 EXCEPTION
1289 WHEN others THEN
1290 IF (l_debug = 'Y') THEN
1291 okc_debug.Log('2000: Leaving ',2);
1292 okc_debug.Reset_Indentation;
1293 END IF;
1294 fnd_message.set_name('OKC','OKC_CATASTROPHIC_ERROR');
1295 fnd_message.set_token('ROUTINE',l_proc);
1296 fnd_message.set_token('REASON',SQLERRM);
1297 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
1298 raise g_error_exception;
1299 END call_qp_upgrade;
1300
1301
1302
1303 /*----------------------------------------------------------------------------
1304 PROCEDURE upd_category_rec
1305 ----------------------------------------------------------------------------*/
1306 PROCEDURE upd_category_rec
1307 (
1308 p_category_code IN okc_subclasses_b.code%TYPE,
1309 p_status IN varchar2
1310 )
1311 IS
1312 /*
1313 This procedure will update the category record currently processed as complete
1314
1315 */
1316
1317 -- local variables and cursors
1318
1319 l_proc varchar2(72) := g_package||'upd_category_rec';
1320
1321 CURSOR csr_category_rec IS
1322 SELECT *
1323 FROM okc_qp_upgrade
1324 WHERE line_type = 'CATEGORY'
1325 AND scs_code = p_category_code
1326 AND completion_flag = 'P'
1327 FOR UPDATE OF completion_flag;
1328
1329 l_qp_upgrade_rec okc_qp_upgrade%ROWTYPE;
1330
1331 BEGIN
1332
1333 IF (l_debug = 'Y') THEN
1334 okc_debug.Set_Indentation(l_proc);
1335 okc_debug.Log('10: Entering ',2);
1336 END IF;
1337
1338 /*
1339 fnd_file.put_line(FND_FILE.LOG,' ');
1340 fnd_file.put_line(FND_FILE.LOG,' ');
1341 fnd_file.put_line(FND_FILE.LOG,' ================================================ ');
1342 fnd_file.put_line(FND_FILE.LOG,' upd_category_rec ');
1343 fnd_file.put_line(FND_FILE.LOG,' ================================================ ');
1344 fnd_file.put_line(FND_FILE.LOG,' ');
1345 fnd_file.put_line(FND_FILE.LOG,' ');
1346 */
1347
1348 OPEN csr_category_rec;
1349 LOOP
1350 FETCH csr_category_rec INTO l_qp_upgrade_rec;
1351 IF (l_debug = 'Y') THEN
1352 okc_debug.set_trace_off;
1353 END IF;
1354 EXIT WHEN csr_category_rec%NOTFOUND;
1355 UPDATE okc_qp_upgrade
1356 SET completion_flag = p_status,
1357 last_update_date = sysdate
1358 WHERE CURRENT OF csr_category_rec;
1359 END LOOP;
1360 CLOSE csr_category_rec;
1361 IF (l_debug = 'Y') THEN
1362 okc_debug.set_trace_on;
1363 END IF;
1364 -- commit work
1365 commit;
1366
1367 -- initialize the global variable as this category id done and we move to next
1368 g_category_code := '' ;
1369
1370
1371 IF (l_debug = 'Y') THEN
1372 okc_debug.Log('1000: Leaving ',2);
1373 okc_debug.Reset_Indentation;
1374 END IF;
1375
1376 EXCEPTION
1377 WHEN others THEN
1378 IF (l_debug = 'Y') THEN
1379 okc_debug.set_trace_on;
1380 okc_debug.Log('2000: Leaving ',2);
1381 okc_debug.Reset_Indentation;
1382 END IF;
1383 fnd_message.set_name('OKC','OKC_CATASTROPHIC_ERROR');
1384 fnd_message.set_token('ROUTINE',l_proc);
1385 fnd_message.set_token('REASON',SQLERRM);
1386 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
1387 raise g_error_exception;
1388 END upd_category_rec;
1389
1390
1391 /*----------------------------------------------------------------------------
1392 PROCEDURE upd_summary_rec
1393 ----------------------------------------------------------------------------*/
1394 PROCEDURE upd_summary_rec
1395 IS
1396 /*
1397 This Procedure will be called when the user wants to enable the QP Profile.
1398 It will check if all the Categories have been successfully completed.
1399 If No, it will abort
1400 If Yes, it will update the SUMMARY record as done and enable the QP Profile
1401 */
1402
1403 -- local variables and cursors
1404
1405 l_proc varchar2(72) := g_package||'upd_summary_rec';
1406 l_total_categories number(9);
1407 l_categories_done number(9);
1408
1409 CURSOR csr_total_categories IS
1410 SELECT COUNT(scs.code)
1411 FROM okc_subclasses_b scs, okc_classes_b cs
1412 WHERE scs.cls_code = cs.code
1413 AND cs.application_id IN ( 510, 871 );
1414
1415 CURSOR csr_categories_done IS
1416 SELECT COUNT(scs_code)
1417 FROM okc_qp_upgrade
1418 WHERE line_type='CATEGORY'
1419 and completion_flag = 'Y';
1420
1421 CURSOR csr_summary_rec IS
1422 SELECT *
1423 FROM okc_qp_upgrade
1424 WHERE line_type='SUMMARY'
1425 AND completion_flag= 'N'
1426 FOR UPDATE OF completion_flag;
1427
1428 l_qp_upgrade_rec okc_qp_upgrade%ROWTYPE;
1429 l_prof_val varchar2(10);
1430
1431 BEGIN
1432
1433 IF (l_debug = 'Y') THEN
1434 okc_debug.Set_Indentation(l_proc);
1435 okc_debug.Log('10: Entering ',2);
1436 END IF;
1437
1438 /*
1439 fnd_file.put_line(FND_FILE.LOG,' ');
1440 fnd_file.put_line(FND_FILE.LOG,' ');
1441 fnd_file.put_line(FND_FILE.LOG,' ================================================ ');
1442 fnd_file.put_line(FND_FILE.LOG,' upd_summary_rec ');
1443 fnd_file.put_line(FND_FILE.LOG,' ================================================ ');
1444 fnd_file.put_line(FND_FILE.LOG,' ');
1445 fnd_file.put_line(FND_FILE.LOG,' ');
1446 */
1447
1448 OPEN csr_total_categories;
1449 FETCH csr_total_categories INTO l_total_categories;
1450 CLOSE csr_total_categories;
1451
1452 IF (l_debug = 'Y') THEN
1453 okc_debug.Log('50: Total Categories : '||l_total_categories,2);
1454 END IF;
1455 fnd_file.put_line(FND_FILE.LOG,' *********************************************** ');
1456 fnd_message.set_name('OKC','OKC_TOT_CONTRACT_CATS');
1457 fnd_message.set_token('TOTCATS',l_total_categories);
1458 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
1459
1460 OPEN csr_categories_done;
1461 FETCH csr_categories_done INTO l_categories_done;
1462 CLOSE csr_categories_done;
1463
1464 IF (l_debug = 'Y') THEN
1465 okc_debug.Log('100: Categories Upgraded : '||l_categories_done,2);
1466 END IF;
1467 fnd_message.set_name('OKC','OKC_TOT_CATEGORIES_UPG');
1468 fnd_message.set_token('TOTUPG',l_categories_done);
1469 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
1470 fnd_file.put_line(FND_FILE.LOG,' ');
1471 fnd_file.put_line(FND_FILE.LOG,' ');
1472 fnd_file.put_line(FND_FILE.LOG,' *********************************************** ');
1473
1474 IF NVL(l_total_categories,0) <> NVL(l_categories_done,0) THEN
1475 -- cannot enable profile as all categories are not processed
1476 IF (l_debug = 'Y') THEN
1477 okc_debug.Log('200: cannot enable profile as all categories are not processed ',2);
1478 END IF;
1479 fnd_message.set_name('OKC','OKC_QP_PROFILE_FAIL');
1480 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
1481 fnd_file.put_line(FND_FILE.LOG,' *********************************************** ');
1482 fnd_file.put_line(FND_FILE.LOG,' ');
1483 fnd_file.put_line(FND_FILE.LOG,' ');
1484 ELSE
1485 -- update the SUMMARY Record
1486 OPEN csr_summary_rec;
1487 IF (l_debug = 'Y') THEN
1488 okc_debug.set_trace_off;
1489 END IF;
1490 LOOP
1491 FETCH csr_summary_rec INTO l_qp_upgrade_rec;
1492 EXIT WHEN csr_summary_rec%NOTFOUND;
1493 UPDATE okc_qp_upgrade
1494 SET completion_flag ='Y',
1495 last_update_date = sysdate
1496 WHERE CURRENT OF csr_summary_rec;
1497 END LOOP;
1498 CLOSE csr_summary_rec;
1499 IF (l_debug = 'Y') THEN
1500 okc_debug.set_trace_on;
1501 END IF;
1502 -- commit work
1503 commit;
1504
1505 -- enable the Profile Option OKC_ADVANCED_PRICING to 'Y'
1506 -- check the current value of OKC_ADVANCED_PRICING
1507 l_prof_val := fnd_profile.value('OKC_ADVANCED_PRICING');
1508
1509 IF (l_debug = 'Y') THEN
1510 okc_debug.Log('400: Current OKC_ADVANCED_PRICING Profile Value : '||l_prof_val,2);
1511 END IF;
1512
1513 IF NVL(l_prof_val,'N') = 'N' THEN
1514 -- set profile to Y
1515 IF (l_debug = 'Y') THEN
1516 okc_debug.Log('450: Setting OKC_ADVANCED_PRICING to Y ',2);
1517 END IF;
1518
1519 -- check if profile successfully set to Y
1520 IF fnd_profile.save('OKC_ADVANCED_PRICING', 'Y', 'SITE') THEN
1521 -- commit changes
1522 commit;
1523 IF (l_debug = 'Y') THEN
1524 okc_debug.Log('500: OKC_ADVANCED_PRICING Successfully Set to Y',2);
1525 END IF;
1526 fnd_file.put_line(FND_FILE.LOG,' ');
1527 fnd_file.put_line(FND_FILE.LOG,' ');
1528 fnd_file.put_line(FND_FILE.LOG,' *********************************************** ');
1529 fnd_message.set_name('OKC','OKC_QP_PROFILE_SUCCESS');
1530 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
1531 fnd_file.put_line(FND_FILE.LOG,' *********************************************** ');
1532 fnd_file.put_line(FND_FILE.LOG,' ');
1533 fnd_file.put_line(FND_FILE.LOG,' ');
1534 ELSE
1535 -- give error here
1536 IF (l_debug = 'Y') THEN
1537 okc_debug.Log('600: Error Setting OKC_ADVANCED_PRICING to Y');
1538 END IF;
1539 fnd_message.set_name('OKC','OKC_CATASTROPHIC_ERROR');
1540 fnd_message.set_token('ROUTINE',l_proc);
1541 fnd_message.set_token('REASON',SQLERRM);
1542 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
1543 APP_EXCEPTION.RAISE_EXCEPTION;
1544 END IF; -- for setting profile
1545
1546
1547
1548 END IF; -- l_prof_val is N
1549
1550
1551 END IF; -- l_total_categories <> l_categories_done
1552
1553 IF (l_debug = 'Y') THEN
1554 okc_debug.Log('1000: Leaving ',2);
1555 okc_debug.Reset_Indentation;
1556 END IF;
1557
1558 EXCEPTION
1559 WHEN others THEN
1560 IF (l_debug = 'Y') THEN
1561 okc_debug.set_trace_on;
1562 okc_debug.Log('2000: Leaving ',2);
1563 okc_debug.Reset_Indentation;
1564 END IF;
1565 fnd_message.set_name('OKC','OKC_CATASTROPHIC_ERROR');
1566 fnd_message.set_token('ROUTINE',l_proc);
1567 fnd_message.set_token('REASON',SQLERRM);
1568 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
1569 raise;
1570 END upd_summary_rec;
1571
1572
1573 /*----------------------------------------------------------------------------
1574 PROCEDURE process_report
1575 ----------------------------------------------------------------------------*/
1576 PROCEDURE process_report
1577 IS
1578 /*
1579 This Procedure will process the upgrade_status_rpt
1580 */
1581
1582 -- local variables and cursors
1583
1584 l_proc varchar2(72) := g_package||'process_report';
1585
1586 BEGIN
1587
1588
1589 IF (l_debug = 'Y') THEN
1590 okc_debug.Set_Indentation(l_proc);
1591 okc_debug.Log('10: Entering ',2);
1592 END IF;
1593
1594 /*
1595 fnd_file.put_line(FND_FILE.LOG,' ');
1596 fnd_file.put_line(FND_FILE.LOG,' ');
1597 fnd_file.put_line(FND_FILE.LOG,' ================================================ ');
1598 fnd_file.put_line(FND_FILE.LOG,' process_report ');
1599 fnd_file.put_line(FND_FILE.LOG,' ================================================ ');
1600 fnd_file.put_line(FND_FILE.LOG,' ');
1601 fnd_file.put_line(FND_FILE.LOG,' ');
1602 */
1603
1604 IF (l_debug = 'Y') THEN
1605 okc_debug.Log('50: Calling upgrade_status_rpt ',2);
1606 END IF;
1607 fnd_file.put_line(FND_FILE.LOG,'Processing Upgrade Status report');
1608 upgrade_status_rpt;
1609
1610 IF (l_debug = 'Y') THEN
1611 okc_debug.Log('1000: Leaving ',2);
1612 okc_debug.Reset_Indentation;
1613 END IF;
1614
1615 EXCEPTION
1616 WHEN others THEN
1617 IF (l_debug = 'Y') THEN
1618 okc_debug.Log('2000: Leaving ',2);
1619 okc_debug.Reset_Indentation;
1620 END IF;
1621 fnd_message.set_name('OKC','OKC_CATASTROPHIC_ERROR');
1622 fnd_message.set_token('ROUTINE',l_proc);
1623 fnd_message.set_token('REASON',SQLERRM);
1624 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
1625 raise;
1626 END process_report;
1627
1628
1629 /*----------------------------------------------------------------------------
1630 PROCEDURE upgrade_status_rpt
1631 ----------------------------------------------------------------------------*/
1632 PROCEDURE upgrade_status_rpt
1633 IS
1634 /*
1635 This Procedure will be report the upgrade status.
1636 It will list the the following :
1637 1. Categories that have been successfully upgraded.
1638 2. Categories that have not been successfully or completely upgraded.
1639 3. Categories that have never been upgraded.
1640
1641 */
1642
1643 -- local variables and cursors
1644
1645 l_proc varchar2(72) := g_package||'upgrade_status_rpt';
1646
1647 l_star_line varchar2(120) := '*************************************************************************************';
1648 l_dash_line varchar2(120) := '-------------------------------------------------------------------------------------';
1649 l_equl_line varchar2(120) := '=====================================================================================';
1650 l_space varchar2(80) := ' ';
1651
1652 l_rpt_title1 varchar2(120) := ' LIST OF CATEGORIES SUCCESSFULLY UPGRADED : ';
1653 l_rpt_title2 varchar2(120) := ' LIST OF CATEGORIES PARTIALLY UPGRADED : ';
1654 l_rpt_title3 varchar2(120) := ' LIST OF CATEGORIES NOT UPGRADED : ';
1655
1656 -- l_cat_comp_header varchar2(120) := 'Category Name Upgrade Start Upgrade End ';
1657 l_cat_comp_header varchar2(120) := 'Category Name';
1658 l_cat_incomp_header varchar2(120) := 'Category Name';
1659 l_category_name varchar2(80);
1660 l_start_date date;
1661 l_end_date date;
1662
1663 CURSOR category_compeleted_csr IS
1664 SELECT RPAD(scs.meaning,40),
1665 RPAD(qp.CREATION_DATE,13),
1666 qp.LAST_UPDATE_DATE
1667 FROM okc_subclasses_v scs, okc_qp_upgrade qp
1668 WHERE qp.scs_code = scs.code
1669 AND qp.line_type = 'CATEGORY'
1670 AND qp.completion_flag = 'Y'
1671 ORDER BY scs.meaning;
1672
1673 CURSOR category_incompeleted_csr IS
1674 SELECT scs.meaning
1675 FROM okc_subclasses_v scs, okc_qp_upgrade qp
1676 WHERE qp.scs_code = scs.code
1677 AND qp.line_type = 'CATEGORY'
1678 AND qp.completion_flag = 'N'
1679 ORDER BY scs.meaning;
1680
1681 CURSOR category_pending_csr IS
1682 SELECT scs.meaning
1683 FROM okc_subclasses_v scs, okc_classes_b cs
1684 WHERE scs.cls_code = cs.code
1685 AND cs.application_id IN (510,871)
1686 AND scs.code NOT IN (
1687 SELECT scs_code
1688 FROM okc_qp_upgrade
1689 WHERE line_type='CATEGORY'
1690 )
1691 ORDER BY scs.meaning;
1692
1693 BEGIN
1694
1695
1696 IF (l_debug = 'Y') THEN
1697 okc_debug.Set_Indentation(l_proc);
1698 okc_debug.Log('10: Entering ',2);
1699 END IF;
1700
1701 /*
1702 fnd_file.put_line(FND_FILE.LOG,' ');
1703 fnd_file.put_line(FND_FILE.LOG,' ');
1704 fnd_file.put_line(FND_FILE.LOG,' ================================================ ');
1705 fnd_file.put_line(FND_FILE.LOG,' upgrade_status_rpt ');
1706 fnd_file.put_line(FND_FILE.LOG,' ================================================ ');
1707 fnd_file.put_line(FND_FILE.LOG,' ');
1708 fnd_file.put_line(FND_FILE.LOG,' ');
1709 */
1710
1711 fnd_file.put_line(FND_FILE.OUTPUT,l_equl_line);
1712 fnd_file.put_line(FND_FILE.OUTPUT,l_space);
1713 fnd_file.put_line(FND_FILE.OUTPUT,' UPGRADE STATUS REPORT ');
1714 fnd_file.put_line(FND_FILE.OUTPUT,l_space);
1715 fnd_file.put_line(FND_FILE.OUTPUT,l_equl_line);
1716 fnd_file.put_line(FND_FILE.OUTPUT,l_space);
1717
1718 -- Successful Categories
1719
1720 fnd_file.put_line(FND_FILE.OUTPUT,l_star_line);
1721 fnd_file.put_line(FND_FILE.OUTPUT,l_rpt_title1);
1722 fnd_file.put_line(FND_FILE.OUTPUT,l_star_line);
1723 fnd_file.put_line(FND_FILE.OUTPUT,l_space);
1724 fnd_file.put_line(FND_FILE.OUTPUT,l_dash_line);
1725 fnd_file.put_line(FND_FILE.OUTPUT,l_cat_comp_header);
1726 fnd_file.put_line(FND_FILE.OUTPUT,l_dash_line);
1727
1728 OPEN category_compeleted_csr;
1729 LOOP
1730 FETCH category_compeleted_csr INTO l_category_name, l_start_date, l_end_date;
1731 EXIT WHEN category_compeleted_csr%NOTFOUND;
1732 fnd_file.put_line(FND_FILE.OUTPUT, l_category_name);
1733 END LOOP;
1734 CLOSE category_compeleted_csr;
1735
1736 fnd_file.put_line(FND_FILE.OUTPUT,l_space);
1737 fnd_file.put_line(FND_FILE.OUTPUT,l_space);
1738 fnd_file.put_line(FND_FILE.OUTPUT,l_star_line);
1739
1740 -- blank lines
1741 fnd_file.put_line(FND_FILE.OUTPUT,l_space);
1742 fnd_file.put_line(FND_FILE.OUTPUT,l_space);
1743 fnd_file.put_line(FND_FILE.OUTPUT,l_space);
1744 fnd_file.put_line(FND_FILE.OUTPUT,l_space);
1745 -- end blank lines
1746
1747 -- Incomplete Categories
1748
1749 fnd_file.put_line(FND_FILE.OUTPUT,l_star_line);
1750 fnd_file.put_line(FND_FILE.OUTPUT,l_rpt_title2);
1751 fnd_file.put_line(FND_FILE.OUTPUT,l_star_line);
1752 fnd_file.put_line(FND_FILE.OUTPUT,l_space);
1753 fnd_file.put_line(FND_FILE.OUTPUT,l_space);
1754 fnd_file.put_line(FND_FILE.OUTPUT,l_dash_line);
1755 fnd_file.put_line(FND_FILE.OUTPUT,l_cat_incomp_header);
1756 fnd_file.put_line(FND_FILE.OUTPUT,l_dash_line);
1757
1758 OPEN category_incompeleted_csr;
1759 LOOP
1760 FETCH category_incompeleted_csr INTO l_category_name;
1761 EXIT WHEN category_incompeleted_csr%NOTFOUND;
1762 fnd_file.put_line(FND_FILE.OUTPUT, l_category_name);
1763 END LOOP;
1764 CLOSE category_incompeleted_csr;
1765
1766 fnd_file.put_line(FND_FILE.OUTPUT,l_space);
1767 fnd_file.put_line(FND_FILE.OUTPUT,l_space);
1768 fnd_file.put_line(FND_FILE.OUTPUT,l_star_line);
1769
1770 -- blank lines
1771 fnd_file.put_line(FND_FILE.OUTPUT,l_space);
1772 fnd_file.put_line(FND_FILE.OUTPUT,l_space);
1773 fnd_file.put_line(FND_FILE.OUTPUT,l_space);
1774 fnd_file.put_line(FND_FILE.OUTPUT,l_space);
1775 -- end blank lines
1776
1777 -- Categories not upgraded
1778 fnd_file.put_line(FND_FILE.OUTPUT,l_star_line);
1779 fnd_file.put_line(FND_FILE.OUTPUT,l_rpt_title3);
1780 fnd_file.put_line(FND_FILE.OUTPUT,l_star_line);
1781 fnd_file.put_line(FND_FILE.OUTPUT,l_space);
1782 fnd_file.put_line(FND_FILE.OUTPUT,l_space);
1783 fnd_file.put_line(FND_FILE.OUTPUT,l_dash_line);
1784 fnd_file.put_line(FND_FILE.OUTPUT,l_cat_incomp_header);
1785 fnd_file.put_line(FND_FILE.OUTPUT,l_dash_line);
1786
1787 OPEN category_pending_csr;
1788 LOOP
1789 FETCH category_pending_csr INTO l_category_name;
1790 EXIT WHEN category_pending_csr%NOTFOUND;
1791 fnd_file.put_line(FND_FILE.OUTPUT, l_category_name);
1792 END LOOP;
1793 CLOSE category_pending_csr;
1794
1795 fnd_file.put_line(FND_FILE.OUTPUT,l_space);
1796 fnd_file.put_line(FND_FILE.OUTPUT,l_space);
1797 fnd_file.put_line(FND_FILE.OUTPUT,l_star_line);
1798
1799 -- blank lines
1800 fnd_file.put_line(FND_FILE.OUTPUT,l_space);
1801 fnd_file.put_line(FND_FILE.OUTPUT,l_space);
1802 fnd_file.put_line(FND_FILE.OUTPUT,l_space);
1803 fnd_file.put_line(FND_FILE.OUTPUT,l_space);
1804 -- end blank lines
1805
1806 fnd_file.put_line(FND_FILE.OUTPUT,l_equl_line);
1807 fnd_file.put_line(FND_FILE.OUTPUT,l_space);
1808 fnd_file.put_line(FND_FILE.OUTPUT,' END UPGRADE STATUS REPORT ');
1809 fnd_file.put_line(FND_FILE.OUTPUT,l_space);
1810 fnd_file.put_line(FND_FILE.OUTPUT,l_equl_line);
1811 fnd_file.put_line(FND_FILE.OUTPUT,l_space);
1812
1813
1814
1815 IF (l_debug = 'Y') THEN
1816 okc_debug.Log('1000: Leaving ',2);
1817 okc_debug.Reset_Indentation;
1818 END IF;
1819
1820 EXCEPTION
1821 WHEN others THEN
1822 IF (l_debug = 'Y') THEN
1823 okc_debug.Log('2000: Leaving ',2);
1824 okc_debug.Reset_Indentation;
1825 END IF;
1826 fnd_message.set_name('OKC','OKC_CATASTROPHIC_ERROR');
1827 fnd_message.set_token('ROUTINE',l_proc);
1828 fnd_message.set_token('REASON',SQLERRM);
1829 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
1830 raise;
1831 END upgrade_status_rpt;
1832
1833 /*----------------------------------------------------------------------------
1834 FUNCTION check_qp_profile
1835 ----------------------------------------------------------------------------*/
1836 FUNCTION check_qp_profile
1837 RETURN VARCHAR2
1838 IS
1839 /*
1840 This Function will check the OKC_ADVANCED_PRICING Profile Value before starting
1841 conc. pgm and will abort if the OKC_ADVANCED_PRICING Profile Value is Y
1842 */
1843
1844 -- local variables and cursors
1845
1846 l_proc varchar2(72) := g_package||'check_qp_profile';
1847 l_prof_val varchar2(10):= 'N';
1848
1849 BEGIN
1850
1851 IF (l_debug = 'Y') THEN
1852 okc_debug.Set_Indentation(l_proc);
1853 okc_debug.Log('10: Entering ',2);
1854 END IF;
1855
1856 -- check the current value of OKC_ADVANCED_PRICING
1857 l_prof_val := fnd_profile.value('OKC_ADVANCED_PRICING');
1858
1859 IF (l_debug = 'Y') THEN
1860 okc_debug.Log('100: Current OKC_ADVANCED_PRICING Profile Value : '||l_prof_val,2);
1861 END IF;
1862
1863 IF NVL(l_prof_val,'N') = 'Y' THEN
1864 -- upgrade already done, abort
1865 IF (l_debug = 'Y') THEN
1866 okc_debug.Log('200: Aborting as the Upgrade is already Done ... ',2);
1867 END IF;
1868 fnd_file.put_line(FND_FILE.LOG,' ');
1869 fnd_file.put_line(FND_FILE.LOG,' ');
1870 fnd_file.put_line(FND_FILE.LOG,' *********************************************** ');
1871 fnd_message.set_name('OKC','OKC_QP_ALREADY_DONE');
1872 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
1873 fnd_file.put_line(FND_FILE.LOG,' *********************************************** ');
1874 fnd_file.put_line(FND_FILE.LOG,' ');
1875 fnd_file.put_line(FND_FILE.LOG,' ');
1876 END IF;
1877
1878 IF (l_debug = 'Y') THEN
1879 okc_debug.Log('1000: Leaving ',2);
1880 okc_debug.Reset_Indentation;
1881 END IF;
1882
1883 RETURN l_prof_val;
1884
1885 EXCEPTION
1886 WHEN others THEN
1887 IF (l_debug = 'Y') THEN
1888 okc_debug.Log('2000: Leaving ',2);
1889 okc_debug.Reset_Indentation;
1890 END IF;
1891 fnd_message.set_name('OKC','OKC_CATASTROPHIC_ERROR');
1892 fnd_message.set_token('ROUTINE',l_proc);
1893 fnd_message.set_token('REASON',SQLERRM);
1894 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
1895 RETURN l_prof_val;
1896 END check_qp_profile;
1897
1898 /*----------------------------------------------------------------------------
1899 FUNCTION compute_estimated_amt
1900 ----------------------------------------------------------------------------*/
1901 FUNCTION compute_estimated_amt
1902 (
1903 p_chr_id IN okc_k_headers_b.id%TYPE
1904 )
1905 RETURN number
1906 IS
1907 /*
1908 This will then compute the estimated_amount as sum of price_negotiated for all lines at
1909 level 1 for the contract
1910 */
1911
1912 -- local variables and cursors
1913
1914 l_proc varchar2(72) := g_package||'compute_estimated_amt';
1915 l_estimated_amt number := 0;
1916
1917 CURSOR csr_estimated_amt IS
1918 SELECT SUM(NVL(price_negotiated,0))
1919 FROM okc_k_lines_b
1920 WHERE chr_id = p_chr_id;
1921
1922 BEGIN
1923
1924 IF (l_debug = 'Y') THEN
1925 okc_debug.Set_Indentation(l_proc);
1926 okc_debug.Log('10: Entering ',2);
1927 END IF;
1928
1929 OPEN csr_estimated_amt;
1930 FETCH csr_estimated_amt INTO l_estimated_amt;
1931 CLOSE csr_estimated_amt;
1932
1933 IF (l_debug = 'Y') THEN
1934 okc_debug.Log('100: New Estimated Amount : '||NVL(l_estimated_amt,0),2);
1935 END IF;
1936
1937 IF (l_debug = 'Y') THEN
1938 okc_debug.Log('1000: Leaving ',2);
1939 okc_debug.Reset_Indentation;
1940 END IF;
1941
1942 RETURN NVL(l_estimated_amt,0);
1943
1944 EXCEPTION
1945 WHEN others THEN
1946 IF (l_debug = 'Y') THEN
1947 okc_debug.Log('2000: Leaving ',2);
1948 okc_debug.Reset_Indentation;
1949 END IF;
1950 fnd_message.set_name('OKC','OKC_CATASTROPHIC_ERROR');
1951 fnd_message.set_token('ROUTINE',l_proc);
1952 fnd_message.set_token('REASON',SQLERRM);
1953 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
1954 RETURN NVL(l_estimated_amt,0);
1955 raise;
1956 END compute_estimated_amt;
1957
1958
1959
1960 /*----------------------------------------------------------------------------
1961 PROCEDURE create_manual_adjustment
1962 ----------------------------------------------------------------------------*/
1963 PROCEDURE create_manual_adjustment
1964 (
1965 p_chr_id IN okc_k_headers_b.id%TYPE
1966 )
1967 IS
1968 /*
1969 This procedure will check if there is any difference between line_list_price and
1970 price_negotiated for priced lines and if yes it will create a manual adjustment
1971 transaction
1972 Assumption : There is a Modifier created with name OKC_QP_UPGRADE
1973 This Modifier has lines : Discount and Surcharge.
1974 We pick up the Surcharge Line if price_negotiated > line_list_price
1975 We pick up the Discount Line if price_negotiated < line_list_price
1976 */
1977
1978 -- local variables and cursors
1979
1980 l_proc varchar2(72) := g_package||'create_manual_adjustment';
1981
1982 CURSOR csr_man_adj IS
1983 SELECT *
1984 FROM okc_k_lines_b
1985 WHERE dnz_chr_id = p_chr_id
1986 AND NVL(price_level_ind,'N') = 'Y'
1987 AND NVL(line_list_price,0) <> NVL(price_negotiated,0);
1988
1989 CURSOR csr_list_lines(p_list_line_type_code IN VARCHAR2) IS
1990 SELECT *
1991 FROM qp_list_lines
1992 WHERE list_line_type_code = p_list_line_type_code
1993 AND list_header_id IN (
1994 SELECT list_header_id
1995 FROM qp_list_headers
1996 WHERE name = 'OKC_QP_UPGRADE'
1997 );
1998
1999 l_k_lines_rec okc_k_lines_b%ROWTYPE;
2000 l_qp_list_lines_rec qp_list_lines%ROWTYPE;
2001 l_adj_amt number;
2002 l_id number;
2003
2004 BEGIN
2005
2006 IF (l_debug = 'Y') THEN
2007 okc_debug.Set_Indentation(l_proc);
2008 okc_debug.Log('10: Entering ',2);
2009 okc_debug.Log('50: p_chr_id : '||p_chr_id,2);
2010 END IF;
2011
2012 OPEN csr_man_adj;
2013 LOOP
2014 FETCH csr_man_adj INTO l_k_lines_rec;
2015 EXIT WHEN csr_man_adj%NOTFOUND;
2016
2017 -- create man adj here
2018 l_adj_amt := NVL(l_k_lines_rec.line_list_price,0) - NVL(l_k_lines_rec.price_negotiated,0) ;
2019 IF (l_debug = 'Y') THEN
2020 okc_debug.Log('100: Adjusted Amt : '||l_adj_amt,2);
2021 END IF;
2022
2023 IF l_adj_amt > 0 THEN
2024 -- this is discount as list pr > negotiated pr
2025 OPEN csr_list_lines(p_list_line_type_code => 'DIS');
2026 FETCH csr_list_lines INTO l_qp_list_lines_rec;
2027 ELSE
2028 -- this is surcharge
2029 OPEN csr_list_lines(p_list_line_type_code => 'SUR');
2030 FETCH csr_list_lines INTO l_qp_list_lines_rec;
2031 END IF;
2032
2033 -- generate Primary Key
2034 l_id := get_seq_id;
2035 IF (l_debug = 'Y') THEN
2036 okc_debug.Log('200: Primary Key : '||l_id,2);
2037 END IF;
2038
2039 -- insert adj into okc_price_adjustments
2040 INSERT INTO okc_price_adjustments
2041 (
2042 ID,
2043 CHR_ID,
2044 CLE_ID,
2045 ACCRUAL_CONVERSION_RATE,
2046 ACCRUAL_FLAG,
2047 ADJUSTED_AMOUNT,
2048 APPLIED_FLAG,
2049 ARITHMETIC_OPERATOR,
2050 AUTOMATIC_FLAG,
2051 BENEFIT_QTY,
2052 BENEFIT_UOM_CODE,
2053 CHARGE_SUBTYPE_CODE,
2054 CHARGE_TYPE_CODE ,
2055 EXPIRATION_DATE ,
2056 INCLUDE_ON_RETURNS_FLAG ,
2057 LIST_HEADER_ID ,
2058 LIST_LINE_ID ,
2059 LIST_LINE_NO ,
2060 LIST_LINE_TYPE_CODE ,
2061 MODIFIER_LEVEL_CODE ,
2062 MODIFIER_MECHANISM_TYPE_CODE ,
2063 OPERAND ,
2064 PRICE_BREAK_TYPE_CODE ,
2065 PRICING_GROUP_SEQUENCE ,
2066 PRICING_PHASE_ID ,
2067 PRORATION_TYPE_CODE ,
2068 REBATE_TRANSACTION_TYPE_CODE ,
2069 RANGE_BREAK_QUANTITY ,
2070 SOURCE_SYSTEM_CODE ,
2071 SUBSTITUTION_ATTRIBUTE ,
2072 UPDATE_ALLOWED ,
2073 UPDATED_FLAG ,
2074 OBJECT_VERSION_NUMBER ,
2075 CREATED_BY,
2076 CREATION_DATE,
2077 LAST_UPDATED_BY,
2078 LAST_UPDATE_DATE
2079 )
2080 VALUES
2081 (
2082 l_id , -- ID
2083 p_chr_id, -- CHR_ID
2084 l_k_lines_rec.id, -- CLE_ID
2085 l_qp_list_lines_rec.accrual_conversion_rate, -- ACCRUAL_CONVERSION_RATE
2086 l_qp_list_lines_rec.accrual_flag,-- ACCRUAL_FLAG
2087 -1*(l_adj_amt),-- ADJUSTED_AMOUNT
2088 'Y',-- APPLIED_FLAG
2089 l_qp_list_lines_rec.arithmetic_operator, -- ARITHMETIC_OPERATOR
2090 l_qp_list_lines_rec.automatic_flag,-- AUTOMATIC_FLAG
2091 l_qp_list_lines_rec.benefit_qty , -- BENEFIT_QTY
2092 l_qp_list_lines_rec.benefit_uom_code , -- BENEFIT_UOM_CODE
2093 l_qp_list_lines_rec.charge_subtype_code , -- CHARGE_SUBTYPE_CODE
2094 l_qp_list_lines_rec.charge_type_code , -- CHARGE_TYPE_CODE
2095 l_qp_list_lines_rec.expiration_date , -- EXPIRATION_DATE
2096 l_qp_list_lines_rec.include_on_returns_flag , -- INCLUDE_ON_RETURNS_FLAG
2097 l_qp_list_lines_rec.list_header_id , -- LIST_HEADER_ID
2098 l_qp_list_lines_rec.list_line_id , -- LIST_LINE_ID
2099 l_qp_list_lines_rec.list_line_no , -- LIST_LINE_NO
2100 l_qp_list_lines_rec.list_line_type_code , -- LIST_LINE_TYPE_CODE
2101 l_qp_list_lines_rec.modifier_level_code , -- MODIFIER_LEVEL_CODE
2102 'DLT' , -- MODIFIER_MECHANISM_TYPE_CODE
2103 l_adj_amt , -- OPERAND this is reverse of ADJUSTED_AMOUNT
2104 l_qp_list_lines_rec.price_break_type_code , -- PRICE_BREAK_TYPE_CODE
2105 l_qp_list_lines_rec.pricing_group_sequence , -- PRICING_GROUP_SEQUENCE
2106 l_qp_list_lines_rec.pricing_phase_id , -- PRICING_PHASE_ID
2107 l_qp_list_lines_rec.proration_type_code , -- PRORATION_TYPE_CODE
2108 l_qp_list_lines_rec.rebate_transaction_type_code , -- REBATE_TRANSACTION_TYPE_CODE
2109 NULL , -- RANGE_BREAK_QUANTITY
2110 NULL , -- SOURCE_SYSTEM_CODE
2111 l_qp_list_lines_rec.substitution_attribute , -- SUBSTITUTION_ATTRIBUTE
2112 l_qp_list_lines_rec.override_flag , -- UPDATE_ALLOWED
2113 'Y' , -- UPDATED_FLAG
2114 1, -- OBJECT_VERSION_NUMBER
2115 fnd_global.user_id,
2116 sysdate,
2117 fnd_global.user_id,
2118 sysdate
2119 );
2120
2121 CLOSE csr_list_lines; -- close the list line cursor
2122
2123 END LOOP;
2124 CLOSE csr_man_adj;
2125
2126 IF (l_debug = 'Y') THEN
2127 okc_debug.Log('1000: Leaving ',2);
2128 okc_debug.Reset_Indentation;
2129 END IF;
2130
2131 EXCEPTION
2132 WHEN others THEN
2133 IF (l_debug = 'Y') THEN
2134 okc_debug.Log('2000: Leaving ',2);
2135 okc_debug.Reset_Indentation;
2136 END IF;
2137 fnd_message.set_name('OKC','OKC_CATASTROPHIC_ERROR');
2138 fnd_message.set_token('ROUTINE',l_proc);
2139 fnd_message.set_token('REASON',SQLERRM);
2140 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
2141 raise;
2142 END create_manual_adjustment;
2143
2144
2145 /*----------------------------------------------------------------------------
2146 FUNCTION check_modifier
2147 ----------------------------------------------------------------------------*/
2148 FUNCTION check_modifier RETURN varchar2
2149 IS
2150 /*
2151 This function will check if there is a Modifier created with name OKC_QP_UPGRADE
2152 Returns Y if Modifier defined
2153 Returns N if Modifier not defined
2154 Assumption : Pre req for upgrade that Modifier created with name OKC_QP_UPGRADE
2155 */
2156
2157 -- local variables and cursors
2158
2159 l_proc varchar2(72) := g_package||'check_modifier';
2160 l_status varchar2(10) := 'N';
2161 l_qp_list_lines_rec qp_list_lines%ROWTYPE;
2162
2163 CURSOR csr_list_lines IS
2164 SELECT *
2165 FROM qp_list_lines
2166 WHERE list_header_id IN (
2167 SELECT list_header_id
2168 FROM qp_list_headers
2169 WHERE name = 'OKC_QP_UPGRADE'
2170 );
2171
2172 BEGIN
2173
2174 IF (l_debug = 'Y') THEN
2175 okc_debug.Set_Indentation(l_proc);
2176 okc_debug.Log('10: Entering ',2);
2177 END IF;
2178
2179 OPEN csr_list_lines;
2180 -- only one line for the modifier OKC_QP_UPGRADE
2181 FETCH csr_list_lines INTO l_qp_list_lines_rec;
2182 -- check if the above Modifier is defined else give message
2183 IF csr_list_lines%NOTFOUND THEN
2184 IF (l_debug = 'Y') THEN
2185 okc_debug.Log('100: Aborting as the Modifier OKC_QP_UPGRADE is not defined ',2);
2186 END IF;
2187 fnd_file.put_line(FND_FILE.LOG,' ');
2188 fnd_file.put_line(FND_FILE.LOG,' ');
2189 fnd_file.put_line(FND_FILE.LOG,' *********************************************** ');
2190 fnd_message.set_name('OKC','OKC_MOD_NOT_DEFINED');
2191 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
2192 fnd_file.put_line(FND_FILE.LOG,' *********************************************** ');
2193 fnd_file.put_line(FND_FILE.LOG,' ');
2194 fnd_file.put_line(FND_FILE.LOG,' ');
2195 l_status := 'N';
2196 ELSE
2197 -- modifier is defined
2198 l_status := 'Y';
2199 END IF;
2200
2201 CLOSE csr_list_lines;
2202
2203
2204 IF (l_debug = 'Y') THEN
2205 okc_debug.Log('1000: Leaving ',2);
2206 okc_debug.Reset_Indentation;
2207 END IF;
2208
2209 RETURN l_status;
2210
2211 EXCEPTION
2212 WHEN others THEN
2213 IF (l_debug = 'Y') THEN
2214 okc_debug.Log('2000: Leaving ',2);
2215 okc_debug.Reset_Indentation;
2216 END IF;
2217 fnd_message.set_name('OKC','OKC_CATASTROPHIC_ERROR');
2218 fnd_message.set_token('ROUTINE',l_proc);
2219 fnd_message.set_token('REASON',SQLERRM);
2220 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
2221 RETURN l_status;
2222 END check_modifier;
2223
2224 /*----------------------------------------------------------------------------
2225 FUNCTION get_seq_id
2226 ----------------------------------------------------------------------------*/
2227 FUNCTION get_seq_id RETURN NUMBER
2228 IS
2229
2230 -- local variables and cursors
2231
2232 l_proc varchar2(72) := g_package||'get_seq_id';
2233 l_id number;
2234
2235 BEGIN
2236
2237
2238 IF (l_debug = 'Y') THEN
2239 okc_debug.Set_Indentation(l_proc);
2240 okc_debug.Log('10: Entering ',2);
2241 END IF;
2242
2243 IF (l_debug = 'Y') THEN
2244 okc_debug.Log('1000: Leaving ',2);
2245 okc_debug.Reset_Indentation;
2246 END IF;
2247
2248 RETURN(okc_p_util.raw_to_number(sys_guid()));
2249
2250 EXCEPTION
2251 WHEN others THEN
2252 IF (l_debug = 'Y') THEN
2253 okc_debug.Log('2000: Leaving ',2);
2254 okc_debug.Reset_Indentation;
2255 END IF;
2256 fnd_message.set_name('OKC','OKC_CATASTROPHIC_ERROR');
2257 fnd_message.set_token('ROUTINE',l_proc);
2258 fnd_message.set_token('REASON',SQLERRM);
2259 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
2260 RETURN l_id;
2261 END get_seq_id;
2262
2263
2264 /*----------------------------------------------------------------------------
2265 FUNCTION get_item_to_price_flag
2266 ----------------------------------------------------------------------------*/
2267 FUNCTION get_item_to_price_flag
2268 (p_lse_id IN NUMBER )
2269 RETURN VARCHAR2
2270 IS
2271
2272 -- local variables and cursors
2273
2274 l_proc varchar2(72) := g_package||'get_item_to_price_flag';
2275 l_flag varchar2(10) := 'N';
2276
2277 CURSOR csr_item_to_price_flag IS
2278 SELECT NVL(item_to_price_yn,'N')
2279 FROM okc_line_styles_b
2280 WHERE id = p_lse_id;
2281
2282 BEGIN
2283
2284 IF (l_debug = 'Y') THEN
2285 okc_debug.Set_Indentation(l_proc);
2286 okc_debug.Log('10: Entering ',2);
2287 END IF;
2288
2289 OPEN csr_item_to_price_flag;
2290 FETCH csr_item_to_price_flag INTO l_flag;
2291 CLOSE csr_item_to_price_flag;
2292
2293 IF (l_debug = 'Y') THEN
2294 okc_debug.Log('100: l_flag : '||l_flag,2);
2295 okc_debug.Log('1000: Leaving ',2);
2296 okc_debug.Reset_Indentation;
2297 END IF;
2298
2299 RETURN l_flag;
2300
2301 EXCEPTION
2302 WHEN others THEN
2303 IF (l_debug = 'Y') THEN
2304 okc_debug.Log('2000: Leaving ',2);
2305 okc_debug.Reset_Indentation;
2306 END IF;
2307 fnd_message.set_name('OKC','OKC_CATASTROPHIC_ERROR');
2308 fnd_message.set_token('ROUTINE',l_proc);
2309 fnd_message.set_token('REASON',SQLERRM);
2310 fnd_file.put_line(FND_FILE.LOG,fnd_message.get);
2311 RETURN l_flag;
2312 END get_item_to_price_flag;
2313
2314
2315
2316
2317 END OKC_QP_UPGRADE_PUB; -- Package Body OKC_QP_UPGRADE_PUB
[Home] [Help]