[Home] [Help]
PACKAGE BODY: APPS.OKI_LOAD_YRA_PVT
Source
1 PACKAGE BODY oki_load_yra_pvt AS
2 /* $Header: OKIRYRAB.pls 115.9 2003/11/24 08:25:20 kbajaj ship $ */
3
4 --------------------------------------------------------------------------------
5 -- Modification History
6 -- 19-Sep-2001 mezra Initial version
7 -- 25-Sep-2001 mezra Change usd_ columns to base_.
8 -- 22-Oct-2001 mezra Changed All Categories value to -1.
9 -- 26-Nov-2002 rpotnuru NOCOPY Changes
10 -- 19-Dec-2002 brrao UTF-8 Changes to Org Name
11 -- 30-Dec-2002 mezra Change logic for populating month value from the
12 -- meaning to the numeric value.
13 --
14 -- 29-Oct-2003 axraghav Modified l_org_id_csr in calc_yra_dtl1 to
15 -- populate null for organization_name
16 --------------------------------------------------------------------------------
17
18 -- Global exception declaration
19
20 -- Generic exception to immediately exit the procedure
21 g_excp_exit_immediate EXCEPTION ;
22
23
24 -- Global constant delcaration
25
26 -- Constants for the all organization and caetgory record
27 g_all_org_id CONSTANT NUMBER := -1 ;
28 g_all_org_name CONSTANT VARCHAR2(240) := 'All Organizations' ;
29 g_all_scs_code CONSTANT VARCHAR2(30) := '-1' ;
30
31
32 -- Global cursor declaration
33
34 -- Cusror to retrieve the rowid for the selected record
35 CURSOR g_yra_csr
36 ( p_period_set_name IN VARCHAR2
37 , p_period_name IN VARCHAR2
38 , p_authoring_org_id IN VARCHAR2
39 , p_year IN VARCHAR2
40 , p_month IN VARCHAR2
41 , p_scs_code IN VARCHAR2
42 ) IS
43 SELECT rowid
44 FROM oki_yoy_renewal_amt yyr
45 WHERE yyr.period_set_name = p_period_set_name
46 AND yyr.period_name = p_period_name
47 AND yyr.authoring_org_id = p_authoring_org_id
48 AND yyr.year = p_year
49 AND yyr.month = p_month
50 AND yyr.scs_code = p_scs_code
51 ;
52 rec_g_yra_csr g_yra_csr%ROWTYPE ;
53
54 --------------------------------------------------------------------------------
55 -- Procedure to insert records into the oki_yoy_renewal_amt table.
56
57 --------------------------------------------------------------------------------
58 PROCEDURE ins_yoy_rnwl
59 ( p_period_set_name IN VARCHAR2
60 , p_period_name IN VARCHAR2
61 , p_period_type IN VARCHAR2
62 , p_authoring_org_id IN NUMBER
63 , p_authoring_org_name IN VARCHAR2
64 , p_year IN VARCHAR2
65 , p_month IN VARCHAR2
66 , p_scs_code IN VARCHAR2
67 , p_base_contract_amount IN NUMBER
68 , x_retcode OUT NOCOPY VARCHAR2
69 ) IS
70
71 -- Local variable declaration
72
73 -- For error handling
74 l_sqlcode VARCHAR2(100) ;
75 l_sqlerrm VARCHAR2(1000) ;
76
77 l_sequence NUMBER := NULL ;
78
79 -- Cursor declaration
80 CURSOR l_seq_num IS
81 SELECT oki_yoy_renewal_amt_s1.nextval seq
82 FROM dual
83 ;
84 rec_l_seq_num l_seq_num%ROWTYPE ;
85
86 BEGIN
87
88 -- initialize return code to success
89 x_retcode := '0';
90
91 OPEN l_seq_num ;
92 FETCH l_seq_num INTO rec_l_seq_num ;
93 -- unable to generate sequence number, exit immediately
94 IF l_seq_num%NOTFOUND THEN
95 RAISE g_excp_exit_immediate ;
96 END IF ;
97 l_sequence := rec_l_seq_num.seq ;
98 CLOSE l_seq_num ;
99
100 INSERT INTO oki_yoy_renewal_amt
101 ( id
102 , period_set_name
103 , period_name
104 , period_type
105 , authoring_org_id
106 , authoring_org_name
107 , year
108 , month
109 , scs_code
110 , base_contract_amount
111 , request_id
112 , program_application_id
113 , program_id
114 , program_update_date )
115 VALUES ( l_sequence
116 , p_period_set_name
117 , p_period_name
118 , p_period_type
119 , p_authoring_org_id
120 , p_authoring_org_name
121 , p_year
122 , p_month
123 , p_scs_code
124 , p_base_contract_amount
125 , oki_load_yra_pvt.g_request_id
126 , oki_load_yra_pvt.g_program_application_id
127 , oki_load_yra_pvt.g_program_id
128 , oki_load_yra_pvt.g_program_update_date ) ;
129
130 EXCEPTION
131 WHEN OTHERS THEN
132 l_sqlcode := SQLCODE ;
133 l_sqlerrm := SQLERRM ;
134
135 -- Set return code TO error
136 x_retcode := '2';
137
138 fnd_message.set_name( application => 'OKI'
139 , name => 'OKI_TABLE_LOAD_FAILURE' );
140
141 fnd_message.set_token( token => 'TABLE_NAME'
142 , value => 'OKI_YOY_RENEWAL_AMT' );
143
144 fnd_file.put_line( which => fnd_file.log
145 , buff => fnd_message.get );
146
147 fnd_file.put_line( which => fnd_file.log
148 , buff => l_sqlcode||' '||l_sqlerrm );
149 END ins_yoy_rnwl ;
150
151 --------------------------------------------------------------------------------
152 -- Procedure to update records in the oki_yoy_renewal_amt table.
153
154 --------------------------------------------------------------------------------
155 PROCEDURE upd_yoy_rnwl
156 ( p_base_contract_amount IN NUMBER
157 , p_yra_rowid IN ROWID
158 , x_retcode OUT NOCOPY VARCHAR2
159 ) IS
160
161 -- Local variable declaration
162
163 -- For error handling
164 l_sqlcode VARCHAR2(100) ;
165 l_sqlerrm VARCHAR2(1000) ;
166
167
168 BEGIN
169
170 -- initialize return code to success
171 x_retcode := '0';
172
173 UPDATE oki_yoy_renewal_amt SET
174 base_contract_amount = p_base_contract_amount
175 , request_id = oki_load_yra_pvt.g_request_id
176 , program_application_id = oki_load_yra_pvt.g_program_application_id
177 , program_id = oki_load_yra_pvt.g_program_id
178 , program_update_date = oki_load_yra_pvt.g_program_update_date
179 WHERE ROWID = p_yra_rowid ;
180
181 EXCEPTION
182 WHEN OTHERS THEN
183 l_sqlcode := SQLCODE ;
184 l_sqlerrm := SQLERRM ;
185
186 -- Set return code to error
187 x_retcode := '2';
188
189 fnd_message.set_name( application => 'OKI'
190 , name => 'OKI_UNEXPECTED_FAILURE' );
191
192 fnd_message.set_token( token => 'OBJECT_NAME'
193 , value => 'OKI_LOAD_YRA_PVT.UPD_YOY_RNWL' );
194
195 fnd_file.put_line( which => fnd_file.log
196 , buff => fnd_message.get );
197
198 fnd_file.put_line( which => fnd_file.log
199 , buff => l_sqlcode||' '||l_sqlerrm );
200 END upd_yoy_rnwl ;
201
202 --------------------------------------------------------------------------------
203 -- Procedure to calcuate the contract amount for the current and previous
204 -- year.
205
206 --------------------------------------------------------------------------------
207 PROCEDURE calc_yra_dtl1
208 ( p_period_set_name IN VARCHAR2
209 , p_period_type IN VARCHAR2
210 , p_summary_build_date IN DATE
211 , x_retcode OUT NOCOPY VARCHAR2
212 ) IS
213
214 -- Local variable declaration
215
216 -- For capturing the return code, 0 = success, 1 = warning, 2 = error
217 l_retcode VARCHAR2(1) := NULL ;
218
219 -- For error handling
220 l_sqlcode VARCHAR2(100) := NULL ;
221 l_sqlerrm VARCHAR2(1000) := NULL ;
222
223 l_base_contract_amount NUMBER := 0 ;
224 l_year VARCHAR2(4) := NULL ;
225 l_month VARCHAR2 (20) := NULL ;
226
227 -- Location within the program before the error was encountered.
228 l_loc VARCHAR2(100) ;
229
230 -- Holds the truncated start and end dates from gl_periods
231 l_glpr_start_date DATE ;
232 l_glpr_end_date DATE ;
233
234 -- Cusor declaration
235
236 -- Cursor to get all the organizations and subclasses
237 CURSOR l_org_id_csr IS
238 SELECT DISTINCT shd.authoring_org_id authoring_org_id
239 , /*11510 change*/ NULL organization_name
240 , shd.scs_code
241 FROM oki_sales_k_hdrs shd
242 ;
243
244 -- Cursor that calculates the contract amount for a
245 -- particular organization and subclass
246 CURSOR l_yoy_rnwl_csr
247 ( p_glpr_start_date IN DATE
248 , p_glpr_end_date IN DATE
249 , p_authoring_org_id IN NUMBER
250 , p_scs_code IN VARCHAR2
251 ) IS
252 SELECT TO_CHAR(LEAST(NVL(shd.date_signed, shd.start_date),
253 shd.start_date), 'RRRR') year
254 , TO_CHAR(LEAST(NVL(shd.date_signed, shd.start_date),
255 shd.start_date), 'FMMM') Month
256 , SUM(shd.base_contract_amount ) base_contract_amount
257 FROM oki_sales_k_hdrs shd
258 WHERE LEAST(NVL(shd.date_signed, shd.start_date), shd.start_date)
259 BETWEEN ADD_MONTHS((last_day(p_glpr_start_date) + 1), -24)
260 AND last_day(p_glpr_end_date)
261 AND shd.is_new_yn IS NULL
262 AND shd.date_signed IS NOT NULL
263 AND shd.authoring_org_id = p_authoring_org_id
264 AND shd.scs_code = p_scs_code
265 GROUP BY TO_CHAR(LEAST(NVL(shd.date_signed, shd.start_date),
266 shd.start_date), 'RRRR')
267 , TO_CHAR(least(nvl(shd.date_signed, shd.start_date),
268 shd.start_date), 'FMMM')
269 ;
270 rec_l_yoy_rnwl_csr l_yoy_rnwl_csr%ROWTYPE ;
271
272 BEGIN
273
274 -- initialize return code to success
275 l_retcode := '0';
276
277 l_loc := 'Looping through valid organizations.' ;
278 << l_org_id_csr_loop >>
279 -- Loop through all the organizations to calcuate the
280 -- appropriate amounts
281 FOR rec_l_org_id_csr IN l_org_id_csr LOOP
282
283 l_loc := 'Looping through valid periods.' ;
284 << g_glpr_csr_loop >>
285 -- Loop through all the periods
286 FOR rec_g_glpr_csr IN oki_utl_pvt.g_glpr_csr(
287 p_period_set_name, p_period_type, p_summary_build_date ) LOOP
288
289 -- Get the truncated gl_periods start and end dates
290 l_glpr_start_date := trunc(rec_g_glpr_csr.start_date );
291 l_glpr_end_date := trunc(rec_g_glpr_csr.end_date );
292
293 -- Re-initialize the amounts before calculating
294 l_base_contract_amount := 0 ;
295 l_year := NULL ;
296 l_month := NULL ;
297
298 l_loc := 'Opening cursor to determine the yoy renewal amount.' ;
299 << l_yoy_rnwl_csr_loop >>
300 -- Calculate the yoy renewal amount
301 FOR rec_l_yoy_rnwl_csr IN l_yoy_rnwl_csr ( l_glpr_start_date,
302 l_glpr_end_date, rec_l_org_id_csr.authoring_org_id,
303 rec_l_org_id_csr.scs_code ) LOOP
304 l_base_contract_amount := rec_l_yoy_rnwl_csr.base_contract_amount ;
305 l_year := rec_l_yoy_rnwl_csr.year ;
306 l_month := rec_l_yoy_rnwl_csr.month ;
307
308 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
309 -- Determine if the record is a new one or an existing one
310 OPEN oki_load_yra_pvt.g_yra_csr ( rec_g_glpr_csr.period_set_name,
311 rec_g_glpr_csr.period_name, rec_l_org_id_csr.authoring_org_id,
312 l_year, l_month, rec_l_org_id_csr.scs_code ) ;
313 FETCH oki_load_yra_pvt.g_yra_csr INTO rec_g_yra_csr ;
314 IF oki_load_yra_pvt.g_yra_csr%NOTFOUND THEN
315 l_loc := 'Insert the new record.' ;
316 -- Insert the current period data for the period
317 oki_load_yra_pvt.ins_yoy_rnwl (
318 p_period_set_name => rec_g_glpr_csr.period_set_name
319 , p_period_name => rec_g_glpr_csr.period_name
320 , p_period_type => rec_g_glpr_csr.period_type
321 , p_authoring_org_id => rec_l_org_id_csr.authoring_org_id
322 , p_authoring_org_name => rec_l_org_id_csr.organization_name
323 , p_year => l_year
324 , p_month => l_month
325 , p_scs_code => rec_l_org_id_csr.scs_code
326 , p_base_contract_amount => l_base_contract_amount
327 , x_retcode => l_retcode ) ;
328 IF l_retcode = '2' THEN
329 -- Load failed, exit immediately.
330 RAISE oki_load_yra_pvt.g_excp_exit_immediate ;
331 END IF ;
332 ELSE
333 l_loc := 'Update the existing record.' ;
334 -- Record already exists, so perform an update
335 oki_load_yra_pvt.upd_yoy_rnwl (
336 p_base_contract_amount => l_base_contract_amount
337 , p_yra_rowid => rec_g_yra_csr.rowid
338 , x_retcode => l_retcode ) ;
339 IF l_retcode = '2' THEN
340 -- Load failed, exit immediately.
341 RAISE oki_load_yra_pvt.g_excp_exit_immediate ;
342 END IF ;
343 END IF ;
344 CLOSE oki_load_yra_pvt.g_yra_csr ;
345
346 END LOOP l_yoy_rnwl_csr_loop ;
347 END LOOP g_glpr_csr_loop ;
348 END LOOP l_org_id_csr_loop ;
349
350 EXCEPTION
351 WHEN oki_load_yra_pvt.g_excp_exit_immediate THEN
352 -- Do not log an error ; It has already been logged.
353 -- Set return code to error
354 x_retcode := '2' ;
355
356
357 WHEN OTHERS THEN
358 l_sqlcode := SQLCODE ;
359 l_sqlerrm := SQLERRM ;
360
361 -- Set return code TO error
362 x_retcode := '2' ;
363
364 fnd_message.set_name( application => 'OKI'
365 , name => 'OKI_UNEXPECTED_FAILURE');
366
367 fnd_message.set_token( token => 'OBJECT_NAME'
368 , value => 'OKI_LOAD_YRA_PVT.CALC_YRA_DTL1');
369
370 fnd_file.put_line( which => fnd_file.log
371 , buff => fnd_message.get);
372
373 -- Log the location within the procedure where the error occurred
374 fnd_message.set_name( application => 'OKI'
375 , name => 'OKI_LOC_IN_PROG_FAILURE');
376
377 fnd_message.set_token( token => 'LOCATION'
378 , value => l_loc);
379
380 fnd_file.put_line( which => fnd_file.log
381 , buff => fnd_message.get);
382
383 fnd_file.put_line( which => fnd_file.log
384 , buff => l_sqlcode||' '||l_sqlerrm );
385 END calc_yra_dtl1 ;
386
387
388 --------------------------------------------------------------------------------
389 -- Procedure to calcuate the contract amount for the current and previous
390 -- year.
391
392 --------------------------------------------------------------------------------
393 PROCEDURE calc_yra_dtl2
394 ( p_period_set_name IN VARCHAR2
395 , p_period_type IN VARCHAR2
396 , p_summary_build_date IN DATE
397 , x_retcode OUT NOCOPY VARCHAR2
401
398 ) IS
399
400 -- Local variable declaration
402 -- For capturing the return code, 0 = success, 1 = warning, 2 = error
403 l_retcode VARCHAR2(1) := NULL ;
404
405 -- For error handling
406 l_sqlcode VARCHAR2(100) := NULL ;
407 l_sqlerrm VARCHAR2(1000) := NULL ;
408
409 l_base_contract_amount NUMBER := 0 ;
410 l_year VARCHAR2(4) := NULL ;
411 l_month VARCHAR2 (20) := NULL ;
412
413 -- Location within the program before the error was encountered.
414 l_loc VARCHAR2(100) ;
415
416 -- Holds the truncated start and end dates from gl_periods
417 l_glpr_start_date DATE ;
418 l_glpr_end_date DATE ;
419
420 -- Cusor declaration
421
422 -- Cursor to get all the organizations and subclasses
423 CURSOR l_scs_csr IS
424 SELECT distinct shd.scs_code
425 FROM oki_sales_k_hdrs shd
426 ;
427
428 -- Cursor that calculates the contract amount for a
429 -- particular subclass
430 CURSOR l_yoy_rnwl_csr
431 ( p_glpr_start_date IN DATE
432 , p_glpr_end_date IN DATE
433 , p_scs_code IN VARCHAR2
434 ) IS
435 SELECT TO_CHAR(LEAST(NVL(shd.date_signed, shd.start_date),
436 shd.start_date), 'RRRR') year
437 , TO_CHAR(LEAST(NVL(shd.date_signed, shd.start_date),
438 shd.start_date), 'FMMM') Month
439 , SUM(shd.base_contract_amount ) base_contract_amount
440 FROM oki_sales_k_hdrs shd
441 WHERE LEAST(NVL(shd.date_signed, shd.start_date), shd.start_date)
442 BETWEEN ADD_MONTHS((last_day(p_glpr_start_date) + 1), -24)
443 AND last_day(p_glpr_end_date)
444 AND shd.is_new_yn IS NULL
445 AND shd.date_signed IS NOT NULL
446 AND shd.scs_code = p_scs_code
447 GROUP BY TO_CHAR(LEAST(NVL(shd.date_signed, shd.start_date),
448 shd.start_date), 'RRRR')
449 , TO_CHAR(least(nvl(shd.date_signed, shd.start_date),
450 shd.start_date), 'FMMM')
451 ;
452 rec_l_yoy_rnwl_csr l_yoy_rnwl_csr%ROWTYPE ;
453
454 BEGIN
455
456 -- initialize return code to success
457 l_retcode := '0';
458
459 l_loc := 'Looping through valid organizations.' ;
460 << l_org_id_csr_loop >>
461 -- Loop through all the organizations to calcuate the
462 -- appropriate amounts
463 FOR rec_l_scs_csr IN l_scs_csr LOOP
464
465 l_loc := 'Looping through valid periods.' ;
466 << g_glpr_csr_loop >>
467 -- Loop through all the periods
468 FOR rec_g_glpr_csr IN oki_utl_pvt.g_glpr_csr(
469 p_period_set_name, p_period_type, p_summary_build_date ) LOOP
470
471 -- Get the truncated gl_periods start and end dates
472 l_glpr_start_date := trunc(rec_g_glpr_csr.start_date );
473 l_glpr_end_date := trunc(rec_g_glpr_csr.end_date );
474
475 -- Re-initialize the amounts before calculating
476 l_base_contract_amount := 0 ;
477 l_year := NULL ;
478 l_month := NULL ;
479
480 l_loc := 'Opening cursor to determine the yoy renewal amount.' ;
481 << l_yoy_rnwl_csr_loop >>
482 -- Calculate the yoy renewal amount
483 FOR rec_l_yoy_rnwl_csr IN l_yoy_rnwl_csr ( l_glpr_start_date,
484 l_glpr_end_date,
485 rec_l_scs_csr.scs_code ) LOOP
486 l_base_contract_amount := rec_l_yoy_rnwl_csr.base_contract_amount ;
487 l_year := rec_l_yoy_rnwl_csr.year ;
488 l_month := rec_l_yoy_rnwl_csr.month ;
489
490 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
491 -- Determine if the record is a new one or an existing one
492 OPEN oki_load_yra_pvt.g_yra_csr ( rec_g_glpr_csr.period_set_name,
493 rec_g_glpr_csr.period_name, oki_load_yra_pvt.g_all_org_id,
494 l_year, l_month, rec_l_scs_csr.scs_code ) ;
495 FETCH oki_load_yra_pvt.g_yra_csr INTO rec_g_yra_csr ;
496 IF oki_load_yra_pvt.g_yra_csr%NOTFOUND THEN
497 l_loc := 'Insert the new record.' ;
498 -- Insert the current period data for the period
499 oki_load_yra_pvt.ins_yoy_rnwl (
500 p_period_set_name => rec_g_glpr_csr.period_set_name
501 , p_period_name => rec_g_glpr_csr.period_name
502 , p_period_type => rec_g_glpr_csr.period_type
503 , p_authoring_org_id => oki_load_yra_pvt.g_all_org_id
504 , p_authoring_org_name => oki_load_yra_pvt.g_all_org_name
505 , p_year => l_year
506 , p_month => l_month
507 , p_scs_code => rec_l_scs_csr.scs_code
508 , p_base_contract_amount => l_base_contract_amount
509 , x_retcode => l_retcode ) ;
510 IF l_retcode = '2' THEN
511 -- Load failed, exit immediately.
512 RAISE oki_load_yra_pvt.g_excp_exit_immediate ;
513 END IF ;
514 ELSE
518 p_base_contract_amount => l_base_contract_amount
515 l_loc := 'Update the existing record.' ;
516 -- Record already exists, so perform an update
517 oki_load_yra_pvt.upd_yoy_rnwl (
519 , p_yra_rowid => rec_g_yra_csr.rowid
520 , x_retcode => l_retcode ) ;
521 IF l_retcode = '2' THEN
522 -- Load failed, exit immediately.
523 RAISE oki_load_yra_pvt.g_excp_exit_immediate ;
524 END IF ;
525 END IF ;
526 CLOSE oki_load_yra_pvt.g_yra_csr ;
527
528 END LOOP l_yoy_rnwl_csr_loop ;
529 END LOOP g_glpr_csr_loop ;
530 END LOOP l_org_id_csr_loop ;
531
532 EXCEPTION
533 WHEN oki_load_yra_pvt.g_excp_exit_immediate THEN
534 -- Do not log an error ; It has already been logged.
535 -- Set return code to error
536 x_retcode := '2' ;
537
538
539 WHEN OTHERS THEN
540 l_sqlcode := SQLCODE ;
541 l_sqlerrm := SQLERRM ;
542
543 -- Set return code TO error
544 x_retcode := '2' ;
545
546 fnd_message.set_name( application => 'OKI'
547 , name => 'OKI_UNEXPECTED_FAILURE');
548
549 fnd_message.set_token( token => 'OBJECT_NAME'
550 , value => 'OKI_LOAD_YRA_PVT.CALC_YRA_DTL2');
551
552 fnd_file.put_line( which => fnd_file.log
553 , buff => fnd_message.get);
554
555 -- Log the location within the procedure where the error occurred
556 fnd_message.set_name( application => 'OKI'
557 , name => 'OKI_LOC_IN_PROG_FAILURE');
558
559 fnd_message.set_token( token => 'LOCATION'
560 , value => l_loc);
561
562 fnd_file.put_line( which => fnd_file.log
563 , buff => fnd_message.get);
564
565 fnd_file.put_line( which => fnd_file.log
566 , buff => l_sqlcode||' '||l_sqlerrm );
567 END calc_yra_dtl2 ;
568
569 --------------------------------------------------------------------------------
570 -- Procedure to calcuate the contract amount for the current and previous
571 -- year.
572
573 --------------------------------------------------------------------------------
574 PROCEDURE calc_yra_sum
575 ( p_period_set_name IN VARCHAR2
576 , p_period_type IN VARCHAR2
577 , p_summary_build_date IN DATE
578 , x_retcode OUT NOCOPY VARCHAR2
579 ) IS
580
581 -- Local variable declaration
582
583 -- For capturing the return code, 0 = success, 1 = warning, 2 = error
584 l_retcode VARCHAR2(1) := NULL ;
585
586 -- For error handling
587 l_sqlcode VARCHAR2(100) := NULL ;
588 l_sqlerrm VARCHAR2(1000) := NULL ;
589
590 l_base_contract_amount NUMBER := 0 ;
591 l_year VARCHAR2(4) := NULL ;
592 l_month VARCHAR2 (20) := NULL ;
593
594 -- Location within the program before the error was encountered.
595 l_loc VARCHAR2(100) ;
596
597 -- Holds the truncated start and end dates from gl_periods
598 l_glpr_start_date DATE ;
599 l_glpr_end_date DATE ;
600
601 -- Cusor declaration
602
603 -- Cursor that calculates the contract amount
604 CURSOR l_yoy_rnwl_csr
605 ( p_glpr_start_date IN DATE
606 , p_glpr_end_date IN DATE
607 ) IS
608 SELECT TO_CHAR(LEAST(NVL(shd.date_signed, shd.start_date),
609 shd.start_date), 'RRRR') year
610 , TO_CHAR(LEAST(NVL(shd.date_signed, shd.start_date),
611 shd.start_date), 'FMMM') Month
612 , SUM(shd.base_contract_amount ) base_contract_amount
613 FROM oki_sales_k_hdrs shd
614 WHERE LEAST(NVL(shd.date_signed, shd.start_date), shd.start_date)
615 BETWEEN ADD_MONTHS((last_day(p_glpr_start_date) + 1), -24)
616 AND last_day(p_glpr_end_date)
617 AND shd.is_new_yn IS NULL
618 AND shd.date_signed IS NOT NULL
619 GROUP BY TO_CHAR(LEAST(NVL(shd.date_signed, shd.start_date),
620 shd.start_date), 'RRRR')
621 , TO_CHAR(least(nvl(shd.date_signed, shd.start_date),
622 shd.start_date), 'FMMM')
623 ;
624 rec_l_yoy_rnwl_csr l_yoy_rnwl_csr%ROWTYPE ;
625
626 BEGIN
627
628 -- initialize return code to success
629 l_retcode := '0';
630
631 l_loc := 'Looping through valid organizations.' ;
632 -- Loop through all the organizations to calcuate the
633 -- appropriate amounts
634
635 l_loc := 'Looping through valid periods.' ;
636 << g_glpr_csr_loop >>
637 -- Loop through all the periods
638 FOR rec_g_glpr_csr IN oki_utl_pvt.g_glpr_csr(
639 p_period_set_name, p_period_type, p_summary_build_date ) LOOP
640
641 -- Get the truncated gl_periods start and end dates
642 l_glpr_start_date := trunc(rec_g_glpr_csr.start_date );
643 l_glpr_end_date := trunc(rec_g_glpr_csr.end_date );
644
645 -- Re-initialize the amounts before calculating
646 l_base_contract_amount := 0 ;
650 l_loc := 'Opening cursor to determine the yoy renewal amount.' ;
647 l_year := NULL ;
648 l_month := NULL ;
649
651 << l_yoy_rnwl_csr_loop >>
652 -- Calculate the yoy renewal amount
653 FOR rec_l_yoy_rnwl_csr IN l_yoy_rnwl_csr ( l_glpr_start_date,
654 l_glpr_end_date ) LOOP
655 l_base_contract_amount := rec_l_yoy_rnwl_csr.base_contract_amount ;
656 l_year := rec_l_yoy_rnwl_csr.year ;
657 l_month := rec_l_yoy_rnwl_csr.month ;
658
659 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
660 -- Determine if the record is a new one or an existing one
661 OPEN oki_load_yra_pvt.g_yra_csr ( rec_g_glpr_csr.period_set_name,
662 rec_g_glpr_csr.period_name, oki_load_yra_pvt.g_all_org_id,
663 l_year, l_month, oki_load_yra_pvt.g_all_scs_code ) ;
664 FETCH oki_load_yra_pvt.g_yra_csr INTO rec_g_yra_csr ;
665 IF oki_load_yra_pvt.g_yra_csr%NOTFOUND THEN
666 l_loc := 'Insert the new record.' ;
667 -- Insert the current period data for the period
668 oki_load_yra_pvt.ins_yoy_rnwl (
669 p_period_set_name => rec_g_glpr_csr.period_set_name
670 , p_period_name => rec_g_glpr_csr.period_name
671 , p_period_type => rec_g_glpr_csr.period_type
672 , p_authoring_org_id => oki_load_yra_pvt.g_all_org_id
673 , p_authoring_org_name => oki_load_yra_pvt.g_all_org_name
674 , p_year => l_year
675 , p_month => l_month
676 , p_scs_code => oki_load_yra_pvt.g_all_scs_code
677 , p_base_contract_amount => l_base_contract_amount
678 , x_retcode => l_retcode ) ;
679 IF l_retcode = '2' THEN
680 -- Load failed, exit immediately.
681 RAISE oki_load_yra_pvt.g_excp_exit_immediate ;
682 END IF ;
683 ELSE
684 l_loc := 'Update the existing record.' ;
685 -- Record already exists, so perform an update
686 oki_load_yra_pvt.upd_yoy_rnwl (
687 p_base_contract_amount => l_base_contract_amount
688 , p_yra_rowid => rec_g_yra_csr.rowid
689 , x_retcode => l_retcode ) ;
690 IF l_retcode = '2' THEN
691 -- Load failed, exit immediately.
692 RAISE oki_load_yra_pvt.g_excp_exit_immediate ;
693 END IF ;
694 END IF ;
695 CLOSE oki_load_yra_pvt.g_yra_csr ;
696
697 END LOOP l_yoy_rnwl_csr_loop ;
698
699 END LOOP g_glpr_csr_loop ;
700
701
702 EXCEPTION
703 WHEN oki_load_yra_pvt.g_excp_exit_immediate THEN
704 -- Do not log an error ; It has already been logged.
705 -- Set return code to error
706 x_retcode := '2' ;
707
708
709 WHEN OTHERS THEN
710 l_sqlcode := SQLCODE ;
711 l_sqlerrm := SQLERRM ;
712
713 -- Set return code TO error
714 x_retcode := '2' ;
715
716 fnd_message.set_name( application => 'OKI'
717 , name => 'OKI_UNEXPECTED_FAILURE');
718
719 fnd_message.set_token( token => 'OBJECT_NAME'
720 , value => 'OKI_LOAD_YRA_PVT.CALC_YRA_SUM');
721
722 fnd_file.put_line( which => fnd_file.log
723 , buff => fnd_message.get);
724
725 -- Log the location within the procedure where the error occurred
726 fnd_message.set_name( application => 'OKI'
727 , name => 'OKI_LOC_IN_PROG_FAILURE');
728
729 fnd_message.set_token( token => 'LOCATION'
730 , value => l_loc);
731
732 fnd_file.put_line( which => fnd_file.log
733 , buff => fnd_message.get);
734
735 fnd_file.put_line( which => fnd_file.log
736 , buff => l_sqlcode||' '||l_sqlerrm );
737 END calc_yra_sum ;
738 --------------------------------------------------------------------------------
739 -- Procedure to create all the yoy renewal records. If an
740 -- error is encountered in this procedure or subsequent procedures then
741 -- rollback all changes. Once the table is loaded and the data is committed
742 -- the load is considered successful even if update of the oki_refreshs
743 -- table failed.
744 --------------------------------------------------------------------------------
745 PROCEDURE crt_yoy_rnwl
746 ( p_period_set_name IN VARCHAR2
747 , p_period_type IN VARCHAR2
748 , p_summary_build_date IN DATE
749 , x_errbuf OUT NOCOPY VARCHAR2
750 , x_retcode OUT NOCOPY VARCHAR2
751 ) IS
752
753
754 -- Local exception declaration
755
756 -- Exception to immediately exit the procedure
757 l_excp_upd_refresh EXCEPTION ;
758
759
760 -- Constant declaration
761
762 -- Name of the table for which data is being inserted
763 l_table_name CONSTANT VARCHAR2(30) := 'OKI_YOY_RENEWAL_AMT' ;
764
765
769 l_retcode VARCHAR2(1) := NULL ;
766 -- Local variable declaration
767
768 -- For capturing the return code, 0 = success, 1 = warning, 2 = error
770
771 -- For error handling
772 l_sqlcode VARCHAR2(100) ;
773 l_sqlerrm VARCHAR2(1000) ;
774
775
776 BEGIN
777
778 SAVEPOINT oki_load_yra_pvt_crt_yoy_rnwl ;
779
780 -- initialize return code to success
781 l_retcode := '0' ;
782 x_retcode := '0' ;
783
784 -- Procedure to calculate the amounts for each dimension
785 oki_load_yra_pvt.calc_yra_dtl1 (
786 p_period_set_name => p_period_set_name
787 , p_period_type => p_period_type
788 , p_summary_build_date => p_summary_build_date
789 , x_retcode => l_retcode ) ;
790
791 IF l_retcode = '2' THEN
792 -- Load failed, exit immediately.
793 RAISE oki_load_yra_pvt.g_excp_exit_immediate ;
794 END IF ;
795
796 -- Procedure to calculate the amounts across organizations
797 oki_load_yra_pvt.calc_yra_dtl2 (
798 p_period_set_name => p_period_set_name
799 , p_period_type => p_period_type
800 , p_summary_build_date => p_summary_build_date
801 , x_retcode => l_retcode ) ;
802
803 IF l_retcode = '2' THEN
804 -- Load failed, exit immediately.
805 RAISE oki_load_yra_pvt.g_excp_exit_immediate ;
806 END IF ;
807
808 -- Procedure to calculate the amounts amounts across organizations,
809 -- subclasses
810 oki_load_yra_pvt.calc_yra_sum (
811 p_period_set_name => p_period_set_name
812 , p_period_type => p_period_type
813 , p_summary_build_date => p_summary_build_date
814 , x_retcode => l_retcode ) ;
815
816 IF l_retcode = '2' THEN
817 -- Load failed, exit immediately.
818 RAISE oki_load_yra_pvt.g_excp_exit_immediate ;
819 END IF ;
820
821 COMMIT;
822
823 SAVEPOINT oki_load_yra_pvt_upd_refresh ;
824
825
826 -- Table loaded successfully. Log message IN concurrent manager
827 -- log indicating successful load.
828 fnd_message.set_name( application => 'OKI'
829 , name => 'OKI_TABLE_LOAD_SUCCESS');
830
831 fnd_message.set_token( token => 'TABLE_NAME'
832 , value => l_table_name );
833
834 fnd_file.put_line( which => fnd_file.log
835 , buff => fnd_message.get);
836
837 oki_refresh_pvt.update_oki_refresh( l_table_name, l_retcode ) ;
838
839 IF l_retcode in ('1', '2') THEN
840 -- Update to OKI_REFRESHS failed, exit immediately.
841 RAISE l_excp_upd_refresh ;
842 END IF ;
843
844 COMMIT ;
845
846 EXCEPTION
847 WHEN l_excp_upd_refresh THEN
848 -- Do not log error; It has already been logged by the refreshs
849 -- program
850 x_retcode := l_retcode ;
851
852 ROLLBACK to oki_load_yra_pvt_upd_refresh ;
853
854 WHEN oki_load_yra_pvt.g_excp_exit_immediate THEN
855 -- Do not log an error ; It has already been logged.
856 -- Set return code to error
857 x_retcode := '2' ;
858
859 ROLLBACK TO oki_load_yra_pvt_crt_yoy_rnwl ;
860
861 WHEN OTHERS THEN
862
863 l_sqlcode := SQLCODE ;
864 l_sqlerrm := SQLERRM ;
865
866 -- Set return code TO error
867 x_retcode := '2' ;
868
869 -- ROLLBACK all transactions
870 ROLLBACK TO oki_load_yra_pvt_crt_yoy_rnwl ;
871
872
873 fnd_message.set_name( application => 'OKI'
874 , name => 'OKI_UNEXPECTED_FAILURE');
875
876 fnd_message.set_token( token => 'OBJECT_NAME'
877 , value => 'OKI_LOAD_YRA_PVT.CRT_YOY_RNWL');
878
879 fnd_file.put_line( which => fnd_file.log
880 , buff => fnd_message.get);
881
882 fnd_file.put_line( which => fnd_file.log
883 , buff => l_sqlcode||' '||l_sqlerrm );
884
885 END crt_yoy_rnwl ;
886
887
888 BEGIN
889 -- Initialize the global variables used TO log this job run
890 -- FROM concurrent manager
891 g_request_id := fnd_global.conc_request_id ;
892 g_program_application_id := fnd_global.prog_appl_id ;
893 g_program_id := fnd_global.conc_program_id ;
894 g_program_update_date := SYSDATE ;
895
896 END oki_load_yra_pvt ;