[Home] [Help]
PACKAGE BODY: APPS.OKI_LOAD_RAG_PVT
Source
1 PACKAGE BODY oki_load_rag_pvt AS
2 /* $Header: OKIRRAGB.pls 115.8 2003/11/24 08:25:03 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 --
11 -- 29-Oct-2003 axraghav Modified l_org_id_csr in calc_rag_dtl1,
12 -- calc_rag_dtl2 and calc_rag_sum to populate null for
13 -- organization and customer name
14 --------------------------------------------------------------------------------
15
16 -- Global exception declaration
17
18 -- Generic exception to immediately exit the procedure
19 g_excp_exit_immediate EXCEPTION ;
20
21 -- Global variable declaration
22
23 -- Start and end ranges for the aging buckets
24 g_start_age_group1 NUMBER := 0 ;
25 g_end_age_group1 NUMBER := 0 ;
26 g_start_age_group2 NUMBER := 0 ;
27 g_end_age_group2 NUMBER := 0 ;
28 g_start_age_group3 NUMBER := 0 ;
29 g_end_age_group3 NUMBER := 0 ;
30 g_start_age_group4 NUMBER := 0 ;
31
32
33 -- Global constant delcaration
34
35 -- Constants for the "All" organization, subclass, and customer record
36 g_all_scs_code CONSTANT VARCHAR2(30) := '-1' ;
37 g_all_cst_id CONSTANT NUMBER := -1 ;
38 g_all_cst_name CONSTANT VARCHAR2(30) := 'All Customers' ;
39
40
41 -- Global cursor declaration
42
43 -- Cusror to retrieve the rowid for the selected record.
44 -- If a rowid is retrieved, then the record is updated,
45 -- else the record is inserted.
46 CURSOR g_rag_csr
47 ( p_period_set_name IN VARCHAR2
48 , p_period_name IN VARCHAR2
49 , p_authoring_org_id IN NUMBER
50 , p_customer_party_id IN NUMBER
51 , p_scs_code IN VARCHAR2
52 ) IS
53 SELECT rowid
54 FROM oki_renewal_aging rag
55 WHERE rag.period_set_name = p_period_set_name
56 AND rag.period_name = p_period_name
57 AND rag.authoring_org_id = p_authoring_org_id
58 AND rag.customer_party_id = p_customer_party_id
59 AND rag.scs_code = p_scs_code
60 ;
61 rec_g_rag_csr g_rag_csr%ROWTYPE ;
62
63 --------------------------------------------------------------------------------
64 -- Procedure to insert records into the oki_rnwl_age_by_orgs table.
65
66 --------------------------------------------------------------------------------
67 PROCEDURE ins_rnwl_aging
68 ( p_period_set_name IN VARCHAR2
69 , p_period_name IN VARCHAR2
70 , p_period_type IN VARCHAR2
71 , p_authoring_org_id IN NUMBER
72 , p_authoring_org_name IN VARCHAR2
73 , p_customer_party_id IN NUMBER
74 , p_customer_name IN VARCHAR2
75 , p_scs_code IN VARCHAR2
76 , p_age_group1 IN NUMBER
77 , p_age_group2 IN NUMBER
78 , p_age_group3 IN NUMBER
79 , p_age_group4 IN NUMBER
80 , x_retcode OUT NOCOPY VARCHAR2
81 ) IS
82
83 -- Local variable declaration
84
85 -- For error handling
86 l_sqlcode VARCHAR2(100) ;
87 l_sqlerrm VARCHAR2(1000) ;
88
89 l_sequence NUMBER := NULL ;
90
91 -- Cursor declaration
92 CURSOR l_seq_num IS
93 SELECT oki_renewal_aging_s1.nextval seq
94 FROM dual ;
95 rec_l_seq_num l_seq_num%ROWTYPE ;
96
97 BEGIN
98
99 -- initialize return code to success
100 x_retcode := '0';
101
102 OPEN l_seq_num ;
103 FETCH l_seq_num INTO rec_l_seq_num ;
104 -- unable to generate sequence number, exit immediately
105 IF l_seq_num%NOTFOUND THEN
106 RAISE g_excp_exit_immediate ;
107 END IF ;
108 l_sequence := rec_l_seq_num.seq ;
109 CLOSE l_seq_num ;
110
111 INSERT INTO oki_renewal_aging
112 ( id
113 , period_set_name
114 , period_name
115 , period_type
116 , authoring_org_id
117 , authoring_org_name
118 , customer_party_id
119 , customer_name
120 , scs_code
121 , age_group1
122 , age_group2
123 , age_group3
124 , age_group4
125 , request_id
126 , program_application_id
127 , program_id
128 , program_update_date )
129 VALUES ( l_sequence
130 , p_period_set_name
131 , p_period_name
132 , p_period_type
133 , p_authoring_org_id
134 , p_authoring_org_name
135 , p_customer_party_id
136 , p_customer_name
137 , p_scs_code
138 , p_age_group1
139 , p_age_group2
140 , p_age_group3
141 , p_age_group4
142 , oki_load_rag_pvt.g_request_id
143 , oki_load_rag_pvt.g_program_application_id
144 , oki_load_rag_pvt.g_program_id
145 , oki_load_rag_pvt.g_program_update_date ) ;
146
147 EXCEPTION
148 WHEN OTHERS THEN
149 l_sqlcode := SQLCODE ;
150 l_sqlerrm := SQLERRM ;
151
152 -- Set return code TO error
153 x_retcode := '2';
154
155 fnd_message.set_name( application => 'OKI'
156 , name => 'OKI_TABLE_LOAD_FAILURE');
157
158 fnd_message.set_token( token => 'TABLE_NAME'
159 , value => 'OKI_RENEWAL_AGING');
160
161 fnd_file.put_line( which => fnd_file.log
162 , buff => fnd_message.get);
163
164 fnd_file.put_line( which => fnd_file.log
165 , buff => l_sqlcode||' '||l_sqlerrm );
166 END ins_rnwl_aging ;
167
168 --------------------------------------------------------------------------------
169 -- Procedure to update records in the oki_rnwl_age_by_orgs table.
170
171 --------------------------------------------------------------------------------
172 PROCEDURE upd_rnwl_aging
173 ( p_age_group1 IN NUMBER
174 , p_age_group2 IN NUMBER
175 , p_age_group3 IN NUMBER
176 , p_age_group4 IN NUMBER
177 , p_rag_rowid IN ROWID
178 , x_retcode OUT NOCOPY VARCHAR2
179 ) IS
180
181 -- Local variable declaration
182
183 -- For error handling
184 l_sqlcode VARCHAR2(100) ;
185 l_sqlerrm VARCHAR2(1000) ;
186
187 BEGIN
188
189 -- initialize return code to success
190 x_retcode := '0';
191
192 UPDATE oki_renewal_aging SET
193 age_group1 = p_age_group1
194 , age_group2 = p_age_group2
195 , age_group3 = p_age_group3
196 , age_group4 = p_age_group4
197 , request_id = oki_load_rag_pvt.g_request_id
198 , program_application_id = oki_load_rag_pvt.g_program_application_id
199 , program_id = oki_load_rag_pvt.g_program_id
200 , program_update_date = oki_load_rag_pvt.g_program_update_date
201 WHERE ROWID = p_rag_rowid ;
202
203 EXCEPTION
204 WHEN OTHERS THEN
205 l_sqlcode := SQLCODE ;
206 l_sqlerrm := SQLERRM ;
207
208 -- Set return code to error
209 x_retcode := '2';
210
211 fnd_message.set_name( application => 'OKI'
212 , name => 'OKI_UNEXPECTED_FAILURE' );
213
214 fnd_message.set_token( token => 'OBJECT_NAME'
215 , value => 'OKI_LOAD_RAG_PVT.OKI_UPD_RNWL_AGING' );
216
217 fnd_file.put_line( which => fnd_file.log
218 , buff => fnd_message.get);
219
220 fnd_file.put_line( which => fnd_file.log
221 , buff => l_sqlcode||' '||l_sqlerrm );
222 END upd_rnwl_aging ;
223
224 --------------------------------------------------------------------------------
225 -- Procedure to calculate the start and end ranges for each
226 -- age grouping.
227 --------------------------------------------------------------------------------
228 PROCEDURE calc_age_group
229 ( x_start_age_group1 OUT NOCOPY NUMBER
230 , x_end_age_group1 OUT NOCOPY NUMBER
231 , x_start_age_group2 OUT NOCOPY NUMBER
232 , x_end_age_group2 OUT NOCOPY NUMBER
233 , x_start_age_group3 OUT NOCOPY NUMBER
234 , x_end_age_group3 OUT NOCOPY NUMBER
235 , x_start_age_group4 OUT NOCOPY NUMBER
236 , x_retcode OUT NOCOPY VARCHAR2
237 ) IS
238
239 -- Local variable declaration
240
241 -- For error handling
242 l_sqlcode VARCHAR2(100) ;
243 l_sqlerrm VARCHAR2(1000) ;
244
245 -- The range for each aging bucket
246 l_aging_range NUMBER :=
247 to_number(fnd_profile.value('OKI_AGING_RANGE'), 9999) ;
248
249 -- constant declaration
250
251 -- The number of aging buckets
252 l_num_of_aging_buckets CONSTANT NUMBER := 4 ;
253
254 BEGIN
255
256 -- initialize return code to success
257 x_retcode := '0';
258
259 FOR i in 1 .. l_num_of_aging_buckets loop
260 IF i = 1 THEN
261 x_start_age_group1 := 0 ;
262 x_end_age_group1 := x_start_age_group1 + l_aging_range ;
263
264 ELSIF i = 2 THEN
265 x_start_age_group2 := x_end_age_group1 + 1 ;
266 x_end_age_group2 := x_end_age_group1 + l_aging_range ;
267
268 ELSIF i = 3 THEN
269 x_start_age_group3 := x_end_age_group2 + 1 ;
270 x_end_age_group3 := x_end_age_group2 + l_aging_range ;
271
272 ELSE
273 x_start_age_group4 := x_end_age_group3 + 1 ;
274 END IF ;
275 END LOOP ;
276
277 EXCEPTION
278 WHEN OTHERS THEN
279
280 l_sqlcode := SQLCODE ;
281 l_sqlerrm := SQLERRM ;
282
283 -- Set return code to error
284 x_retcode := '2' ;
285
286
287 fnd_message.set_name( application => 'OKI'
288 , name => 'OKI_UNEXPECTED_FAILURE');
289
290 fnd_message.set_token( token => 'OBJECT_NAME'
291 , value => 'OKI_LOAD_RAG_PVT.CALC_AGE_GROUP');
292
293 fnd_file.put_line( which => fnd_file.log
294 , buff => fnd_message.get);
295
296 fnd_file.put_line( which => fnd_file.log
297 , buff => l_sqlcode||' '||l_sqlerrm );
298 END calc_age_group ;
299
300 --------------------------------------------------------------------------------
301 -- Procedure to calcuate the age of the renewal for the organizations.
302 -- Calculates the amounts by each dimension:
303 -- period set name
304 -- period type
305 -- period name
306 -- customer
307 -- subclass
308 -- organization
309 --
310 --------------------------------------------------------------------------------
311 PROCEDURE calc_rag_dtl1
312 ( p_period_set_name IN VARCHAR2
313 , p_period_type IN VARCHAR2
314 , p_summary_build_date IN DATE
315 , x_retcode OUT NOCOPY VARCHAR2
316 ) IS
317
318 -- Local variable declaration
319
320 -- For capturing the return code, 0 = success, 1 = warning, 2 = error
321 l_retcode VARCHAR2(1) := NULL ;
322
323 -- For error handling
324 l_sqlcode VARCHAR2(100) := NULL ;
325 l_sqlerrm VARCHAR2(1000) := NULL ;
326
327 l_base_fcst_age_group1 NUMBER := 0 ;
328 l_base_fcst_age_group2 NUMBER := 0 ;
329 l_base_fcst_age_group3 NUMBER := 0 ;
330 l_base_fcst_age_group4 NUMBER := 0 ;
331
332 -- holds the rowid of the record in the oki_rnwl_age_by_orgs table
333 l_rag_rowid ROWID ;
334
335 -- Location within the program before the error was encountered.
336 l_loc VARCHAR2(100) ;
337
338 -- Holds the truncated start and end dates from gl_periods
339 l_glpr_start_date DATE ;
340 l_glpr_end_date DATE ;
341
342 -- Cusor declaration
343
344 -- Cursor to get all the organizations
345 CURSOR l_org_id_csr IS
346 SELECT DISTINCT shd.authoring_org_id org_id
347 , /*11510 changes*/ NULL organization_name
348 , shd.customer_party_id customer_id
349 , /*11510 changes*/ NULL customer_name
350 , shd.scs_code
351 FROM oki_sales_k_hdrs shd ;
352
353 -- Cursor to calculate the age of the renewals for each
354 -- organization, customer and subclass
355 CURSOR l_rnwl_age_csr
356 ( p_summary_build_date IN DATE
357 , p_org_id IN NUMBER
358 , p_customer_party_id IN NUMBER
359 , p_scs_code IN VARCHAR2
360 , p_start_age_group1 IN NUMBER
361 , p_end_age_group1 IN NUMBER
362 , p_start_age_group2 IN NUMBER
363 , p_end_age_group2 IN NUMBER
364 , p_start_age_group3 IN NUMBER
365 , p_end_age_group3 IN NUMBER
366 , p_start_age_group4 IN NUMBER
367 ) IS
368 SELECT
369 NVL(SUM(DECODE(SIGN( (TRUNC(p_summary_build_date) -
370 TRUNC(shd.creation_date)) - p_start_age_group1),
371 1, DECODE(SIGN( p_end_age_group1 -
372 (TRUNC(p_summary_build_date) - trunc(shd.creation_date))),
373 1, shd.base_forecast_amount,
374 0, shd.base_forecast_amount,
375 0),
376 0, shd.base_forecast_amount,
377 0)), 0) age_group1,
378 NVL(SUM(DECODE(SIGN( (TRUNC(p_summary_build_date) -
379 TRUNC(shd.creation_date)) - p_start_age_group2),
380 1, DECODE(SIGN( p_end_age_group2 -
381 (TRUNC(p_summary_build_date) - TRUNC(shd.creation_date))),
382 1, shd.base_forecast_amount,
383 0, shd.base_forecast_amount,
384 0),
385 0, shd.base_forecast_amount,
386 0)), 0) age_group2,
387 NVL(SUM(DECODE(SIGN( (TRUNC(p_summary_build_date) -
388 TRUNC(shd.creation_date)) - p_start_age_group3),
389 1, decode(sign( p_end_age_group3 -
390 (trunc(p_summary_build_date) - trunc(shd.creation_date))),
391 1, shd.base_forecast_amount,
392 0, shd.base_forecast_amount,
393 0),
394 0, shd.base_forecast_amount,
395 0)), 0) age_group3,
396 NVL(SUM(DECODE(SIGN( (TRUNC(p_summary_build_date) -
397 TRUNC(shd.creation_date)) - p_start_age_group4),
398 1, shd.base_forecast_amount,
399 0, shd.base_forecast_amount,
400 0)), 0) age_group4
401 FROM oki_sales_k_hdrs shd
402 -- contract has undergone forecasting
403 WHERE shd.win_percent is not null
404 AND shd.close_date is not null
405 -- renewal contract is entered
406 AND shd.ste_code = 'ENTERED'
407 AND is_new_yn IS NULL
408 AND shd.authoring_org_id = p_org_id
409 AND shd.customer_party_id = p_customer_party_id
410 AND shd.scs_code = p_scs_code
411 ;
412 rec_l_rnwl_age_csr l_rnwl_age_csr%ROWTYPE ;
413
414 BEGIN
415
416 -- Initialize return code to success
417 l_retcode := '0';
418
419 -- Get the ranges for the aging buckets
420 oki_load_rag_pvt.calc_age_group (
424 , x_end_age_group2 => oki_load_rag_pvt.g_end_age_group2
421 x_start_age_group1 => oki_load_rag_pvt.g_start_age_group1
422 , x_end_age_group1 => oki_load_rag_pvt.g_end_age_group1
423 , x_start_age_group2 => oki_load_rag_pvt.g_start_age_group2
425 , x_start_age_group3 => oki_load_rag_pvt.g_start_age_group3
426 , x_end_age_group3 => oki_load_rag_pvt.g_end_age_group3
427 , x_start_age_group4 => oki_load_rag_pvt.g_start_age_group4
428 , x_retcode => l_retcode );
429
430 IF l_retcode = '2' THEN
431 -- Load failed, exit immediately.
432 RAISE oki_load_rag_pvt.g_excp_exit_immediate ;
433 END IF ;
434 COMMIT;
435
436 l_loc := 'Looping through valid organizations.' ;
437 << l_org_id_csr_loop >>
438 -- Loop through all the organizations to calcuate the
439 -- appropriate amounts
440 FOR rec_l_org_id_csr IN l_org_id_csr LOOP
441
442 l_loc := 'Looping through valid periods.' ;
443 << g_glpr_csr_loop >>
444 -- Loop through all the periods
445 FOR rec_g_glpr_csr IN oki_utl_pvt.g_glpr_csr(
446 p_period_set_name, p_period_type, p_summary_build_date ) LOOP
447
448 -- Get the truncated gl_periods start and end dates
449 l_glpr_start_date := trunc(rec_g_glpr_csr.start_date );
450 l_glpr_end_date := trunc(rec_g_glpr_csr.end_date );
451
452 -- Re-initialize the amounts before calculating
453 l_base_fcst_age_group1 := 0 ;
454 l_base_fcst_age_group2 := 0 ;
455 l_base_fcst_age_group3 := 0 ;
456 l_base_fcst_age_group4 := 0 ;
457
458 l_loc := 'Opening cursor to determine the age.' ;
459 -- Calculate the forecast amount for a given organization
460 OPEN l_rnwl_age_csr (p_summary_build_date,
461 rec_l_org_id_csr.org_id, rec_l_org_id_csr.customer_id,
462 rec_l_org_id_csr.scs_code, oki_load_rag_pvt.g_start_age_group1,
463 oki_load_rag_pvt.g_end_age_group1,
464 oki_load_rag_pvt.g_start_age_group2,
465 oki_load_rag_pvt.g_end_age_group2,
466 oki_load_rag_pvt.g_start_age_group3,
467 oki_load_rag_pvt.g_end_age_group3,
468 oki_load_rag_pvt.g_start_age_group4 ) ;
469 FETCH l_rnwl_age_csr INTO rec_l_rnwl_age_csr ;
470 IF l_rnwl_age_csr%FOUND THEN
471 l_base_fcst_age_group1 := rec_l_rnwl_age_csr.age_group1 ;
472 l_base_fcst_age_group2 := rec_l_rnwl_age_csr.age_group2 ;
473 l_base_fcst_age_group3 := rec_l_rnwl_age_csr.age_group3 ;
474 l_base_fcst_age_group4 := rec_l_rnwl_age_csr.age_group4 ;
475 END IF ;
476 CLOSE l_rnwl_age_csr ;
477
478 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
479 -- Determine if the record is a new one or an existing one
480 OPEN oki_load_rag_pvt.g_rag_csr ( rec_g_glpr_csr.period_set_name,
481 rec_g_glpr_csr.period_name, rec_l_org_id_csr.org_id,
482 rec_l_org_id_csr.customer_id, rec_l_org_id_csr.scs_code ) ;
483 FETCH oki_load_rag_pvt.g_rag_csr INTO rec_g_rag_csr ;
484 IF oki_load_rag_pvt.g_rag_csr%NOTFOUND THEN
485 l_loc := 'Insert the new record.' ;
486
487 -- Insert the current period data for the period
488 oki_load_rag_pvt.ins_rnwl_aging (
489 p_period_set_name => rec_g_glpr_csr.period_set_name
490 , p_period_name => rec_g_glpr_csr.period_name
491 , p_period_type => rec_g_glpr_csr.period_type
492 , p_authoring_org_id => rec_l_org_id_csr.org_id
493 , p_authoring_org_name => rec_l_org_id_csr.organization_name
494 , p_customer_party_id => rec_l_org_id_csr.customer_id
495 , p_customer_name => rec_l_org_id_csr.customer_name
496 , p_scs_code => rec_l_org_id_csr.scs_code
497 , p_age_group1 => l_base_fcst_age_group1
498 , p_age_group2 => l_base_fcst_age_group2
499 , p_age_group3 => l_base_fcst_age_group3
500 , p_age_group4 => l_base_fcst_age_group4
501 , x_retcode => l_retcode ) ;
502
503 IF l_retcode = '2' THEN
504 -- Load failed, exit immediately.
505 RAISE oki_load_rag_pvt.g_excp_exit_immediate ;
506 END IF ;
507 ELSE
508 l_loc := 'Update the existing record.' ;
509 -- Record already exists, so perform an update
510 oki_load_rag_pvt.upd_rnwl_aging (
511 p_age_group1 => l_base_fcst_age_group1
512 , p_age_group2 => l_base_fcst_age_group2
513 , p_age_group3 => l_base_fcst_age_group3
514 , p_age_group4 => l_base_fcst_age_group4
515 , p_rag_rowid => rec_g_rag_csr.rowid
516 , x_retcode => l_retcode ) ;
517
518 IF l_retcode = '2' THEN
519 -- Load failed, exit immediately.
520 RAISE oki_load_rag_pvt.g_excp_exit_immediate ;
521 END IF ;
522 END IF ;
523 CLOSE oki_load_rag_pvt.g_rag_csr ;
524
525 END LOOP g_glpr_csr_loop ;
526 END LOOP l_org_id_csr_loop ;
527
528
529 EXCEPTION
530 WHEN oki_load_rag_pvt.g_excp_exit_immediate THEN
534
531 -- Do not log an error ; It has already been logged.
532 -- Set return code to error
533 x_retcode := '2' ;
535 WHEN OTHERS THEN
536
537 l_sqlcode := SQLCODE ;
538 l_sqlerrm := SQLERRM ;
539
540 -- Set return code TO error
541 x_retcode := '2' ;
542
543 fnd_message.set_name( application => 'OKI'
544 , name => 'OKI_UNEXPECTED_FAILURE');
545
546 fnd_message.set_token( token => 'OBJECT_NAME'
547 , value => 'OKI_LOAD_rag_PVT.CALC_RAG_DTL1');
548
549 fnd_file.put_line( which => fnd_file.log
550 , buff => fnd_message.get);
551
552 -- Log the location within the procedure where the error occurred
553 fnd_message.set_name( application => 'OKI'
554 , name => 'OKI_LOC_IN_PROG_FAILURE');
555
556 fnd_message.set_token( token => 'LOCATION'
557 , value => l_loc);
558
559 fnd_file.put_line( which => fnd_file.log
560 , buff => fnd_message.get);
561
562 fnd_file.put_line( which => fnd_file.log
563 , buff => l_sqlcode||' '||l_sqlerrm );
564
565 END calc_rag_dtl1 ;
566
567 --------------------------------------------------------------------------------
568 -- Procedure to calcuate the age of the renewal for the organizations.
569 -- Calculates the amounts across categories:
570 -- each period set name
571 -- each period type
572 -- each period name
573 -- each customer
574 -- all subclass
575 -- each organizations
576 --
577 --------------------------------------------------------------------------------
578 PROCEDURE calc_rag_dtl2
579 ( p_period_set_name IN VARCHAR2
580 , p_period_type IN VARCHAR2
581 , p_summary_build_date IN DATE
582 , x_retcode OUT NOCOPY VARCHAR2
583 ) IS
584
585 -- Local variable declaration
586
587 -- For capturing the return code, 0 = success, 1 = warning, 2 = error
588 l_retcode VARCHAR2(1) := NULL ;
589
590 -- For error handling
591 l_sqlcode VARCHAR2(100) := NULL ;
592 l_sqlerrm VARCHAR2(1000) := NULL ;
593
594 l_base_fcst_age_group1 NUMBER := 0 ;
595 l_base_fcst_age_group2 NUMBER := 0 ;
596 l_base_fcst_age_group3 NUMBER := 0 ;
597 l_base_fcst_age_group4 NUMBER := 0 ;
598
599 -- holds the rowid of the record in the oki_rnwl_age_by_orgs table
600 l_rag_rowid ROWID ;
601
602 -- Location within the program before the error was encountered.
603 l_loc VARCHAR2(100) ;
604
605 -- Holds the truncated start and end dates from gl_periods
606 l_glpr_start_date DATE ;
607 l_glpr_end_date DATE ;
608
609 -- Cusor declaration
610
611 -- Cursor to get all the organizations
612 CURSOR l_org_id_csr IS
613 SELECT DISTINCT shd.authoring_org_id org_id
614 , /*11510 changes*/ NULL organization_name
615 , shd.customer_party_id
616 , /*11510 changes*/ NULL customer_name
617 FROM oki_sales_k_hdrs shd ;
618
619 -- Cursor to calculate the age of the renewals for each
620 -- orgnization and customer
621 CURSOR l_rnwl_age_csr
622 ( p_summary_build_date IN DATE
623 , p_org_id IN NUMBER
624 , p_customer_party_id IN NUMBER
625 , p_start_age_group1 IN NUMBER
626 , p_end_age_group1 IN NUMBER
627 , p_start_age_group2 IN NUMBER
628 , p_end_age_group2 IN NUMBER
629 , p_start_age_group3 IN NUMBER
630 , p_end_age_group3 IN NUMBER
631 , p_start_age_group4 IN NUMBER
632 ) IS
633 SELECT
634 NVL(SUM(DECODE(SIGN( (TRUNC(p_summary_build_date) -
635 TRUNC(shd.creation_date)) - p_start_age_group1),
636 1, DECODE(SIGN( p_end_age_group1 -
637 (TRUNC(p_summary_build_date) - trunc(shd.creation_date))),
638 1, shd.base_forecast_amount,
639 0, shd.base_forecast_amount,
640 0),
641 0, shd.base_forecast_amount,
642 0)), 0) age_group1,
643 NVL(SUM(DECODE(SIGN( (TRUNC(p_summary_build_date) -
644 TRUNC(shd.creation_date)) - p_start_age_group2),
645 1, DECODE(SIGN( p_end_age_group2 -
646 (TRUNC(p_summary_build_date) - TRUNC(shd.creation_date))),
647 1, shd.base_forecast_amount,
648 0, shd.base_forecast_amount,
649 0),
650 0, shd.base_forecast_amount,
651 0)), 0) age_group2,
652 NVL(SUM(DECODE(SIGN( (TRUNC(p_summary_build_date) -
653 TRUNC(shd.creation_date)) - p_start_age_group3),
654 1, decode(sign( p_end_age_group3 -
655 (trunc(p_summary_build_date) - trunc(shd.creation_date))),
656 1, shd.base_forecast_amount,
657 0, shd.base_forecast_amount,
658 0),
659 0, shd.base_forecast_amount,
660 0)), 0) age_group3,
661 NVL(SUM(DECODE(SIGN( (TRUNC(p_summary_build_date) -
665 0)), 0) age_group4
662 TRUNC(shd.creation_date)) - p_start_age_group4),
663 1, shd.base_forecast_amount,
664 0, shd.base_forecast_amount,
666 FROM oki_sales_k_hdrs shd
667 -- contract has undergone forecasting
668 WHERE shd.win_percent is not null
669 AND shd.close_date is not null
670 -- renewal contract is entered
671 AND shd.ste_code = 'ENTERED'
672 AND is_new_yn IS NULL
673 AND shd.authoring_org_id = p_org_id
674 AND shd.customer_party_id = p_customer_party_id
675 ;
676 rec_l_rnwl_age_csr l_rnwl_age_csr%ROWTYPE ;
677
678
679 BEGIN
680
681 -- Initialize return code to success
682 l_retcode := '0';
683
684 l_loc := 'Looping through valid organizations.' ;
685 << l_org_id_csr_loop >>
686 -- Loop through all the organizations to calcuate the
687 -- appropriate amounts
688 FOR rec_l_org_id_csr IN l_org_id_csr LOOP
689
690 l_loc := 'Looping through valid periods.' ;
691 << g_glpr_csr_loop >>
692 -- Loop through all the periods
693 FOR rec_g_glpr_csr IN oki_utl_pvt.g_glpr_csr(
694 p_period_set_name, p_period_type, p_summary_build_date ) LOOP
695
696 -- Get the truncated gl_periods start and end dates
697 l_glpr_start_date := trunc(rec_g_glpr_csr.start_date );
698 l_glpr_end_date := trunc(rec_g_glpr_csr.end_date );
699
700 -- Re-initialize the amounts before calculating
701 l_base_fcst_age_group1 := 0 ;
702 l_base_fcst_age_group2 := 0 ;
703 l_base_fcst_age_group3 := 0 ;
704 l_base_fcst_age_group4 := 0 ;
705
706 l_loc := 'Opening cursor to determine the age.' ;
707 -- Calculate the forecast amount for a given organization
708 OPEN l_rnwl_age_csr (p_summary_build_date,
709 rec_l_org_id_csr.org_id, rec_l_org_id_csr.customer_party_id,
710 oki_load_rag_pvt.g_start_age_group1,
711 oki_load_rag_pvt.g_end_age_group1,
712 oki_load_rag_pvt.g_start_age_group2,
713 oki_load_rag_pvt.g_end_age_group2,
714 oki_load_rag_pvt.g_start_age_group3,
715 oki_load_rag_pvt.g_end_age_group3,
716 oki_load_rag_pvt.g_start_age_group4 ) ;
717 FETCH l_rnwl_age_csr INTO rec_l_rnwl_age_csr ;
718 IF l_rnwl_age_csr%FOUND THEN
719 l_base_fcst_age_group1 := rec_l_rnwl_age_csr.age_group1 ;
720 l_base_fcst_age_group2 := rec_l_rnwl_age_csr.age_group2 ;
721 l_base_fcst_age_group3 := rec_l_rnwl_age_csr.age_group3 ;
722 l_base_fcst_age_group4 := rec_l_rnwl_age_csr.age_group4 ;
723 END IF ;
724 CLOSE l_rnwl_age_csr ;
725
726 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
727 -- Determine if the record is a new one or an existing one
728 OPEN oki_load_rag_pvt.g_rag_csr ( rec_g_glpr_csr.period_set_name,
729 rec_g_glpr_csr.period_name, rec_l_org_id_csr.org_id,
730 rec_l_org_id_csr.customer_party_id,
731 oki_load_rag_pvt.g_all_scs_code ) ;
732 FETCH oki_load_rag_pvt.g_rag_csr INTO rec_g_rag_csr ;
733 IF oki_load_rag_pvt.g_rag_csr%NOTFOUND THEN
734 l_loc := 'Insert the new record.' ;
735
736 -- Insert the current period data for the period
737 oki_load_rag_pvt.ins_rnwl_aging (
738 p_period_set_name => rec_g_glpr_csr.period_set_name
739 , p_period_name => rec_g_glpr_csr.period_name
740 , p_period_type => rec_g_glpr_csr.period_type
741 , p_authoring_org_id => rec_l_org_id_csr.org_id
742 , p_authoring_org_name => rec_l_org_id_csr.organization_name
743 , p_customer_party_id => rec_l_org_id_csr.customer_party_id
744 , p_customer_name => rec_l_org_id_csr.customer_name
745 , p_scs_code => oki_load_rag_pvt.g_all_scs_code
746 , p_age_group1 => l_base_fcst_age_group1
747 , p_age_group2 => l_base_fcst_age_group2
748 , p_age_group3 => l_base_fcst_age_group3
749 , p_age_group4 => l_base_fcst_age_group4
750 , x_retcode => l_retcode ) ;
751
752 IF l_retcode = '2' THEN
753 -- Load failed, exit immediately.
754 RAISE oki_load_rag_pvt.g_excp_exit_immediate ;
755 END IF ;
756 ELSE
757 l_loc := 'Update the existing record.' ;
758 -- Record already exists, so perform an update
759 oki_load_rag_pvt.upd_rnwl_aging (
760 p_age_group1 => l_base_fcst_age_group1
761 , p_age_group2 => l_base_fcst_age_group2
762 , p_age_group3 => l_base_fcst_age_group3
763 , p_age_group4 => l_base_fcst_age_group4
764 , p_rag_rowid => rec_g_rag_csr.rowid
765 , x_retcode => l_retcode ) ;
766
767 IF l_retcode = '2' THEN
768 -- Load failed, exit immediately.
769 RAISE oki_load_rag_pvt.g_excp_exit_immediate ;
770 END IF ;
771 END IF ;
772 CLOSE oki_load_rag_pvt.g_rag_csr ;
773
774 END LOOP g_glpr_csr_loop ;
778 EXCEPTION
775 END LOOP l_org_id_csr_loop ;
776
777
779 WHEN oki_load_rag_pvt.g_excp_exit_immediate THEN
780 -- Do not log an error ; It has already been logged.
781 -- Set return code to error
782 x_retcode := '2' ;
783
784 WHEN OTHERS THEN
785
786 l_sqlcode := SQLCODE ;
787 l_sqlerrm := SQLERRM ;
788
789 -- Set return code TO error
790 x_retcode := '2' ;
791
792 fnd_message.set_name( application => 'OKI'
793 , name => 'OKI_UNEXPECTED_FAILURE');
794
795 fnd_message.set_token( token => 'OBJECT_NAME'
796 , value => 'OKI_LOAD_rag_PVT.CALC_RAG_DTL2');
797
798 fnd_file.put_line( which => fnd_file.log
799 , buff => fnd_message.get);
800
801 -- Log the location within the procedure where the error occurred
802 fnd_message.set_name( application => 'OKI'
803 , name => 'OKI_LOC_IN_PROG_FAILURE');
804
805 fnd_message.set_token( token => 'LOCATION'
806 , value => l_loc);
807
808 fnd_file.put_line( which => fnd_file.log
809 , buff => fnd_message.get);
810
811 fnd_file.put_line( which => fnd_file.log
812 , buff => l_sqlcode||' '||l_sqlerrm );
813
814 END calc_rag_dtl2 ;
815
816 --------------------------------------------------------------------------------
817 -- Procedure to calcuate the age of the renewal for the organizations.
818 -- Calculates the amounts across customers and subclasses
819 -- each period set name
820 -- each period type
821 -- each period name
822 -- all customers
823 -- all subclasses
824 -- each organization
825 --
826 --------------------------------------------------------------------------------
827 PROCEDURE calc_rag_sum
828 ( p_period_set_name IN VARCHAR2
829 , p_period_type IN VARCHAR2
830 , p_summary_build_date IN DATE
831 , x_retcode OUT NOCOPY VARCHAR2
832 ) IS
833
834 -- Local variable declaration
835
836 -- For capturing the return code, 0 = success, 1 = warning, 2 = error
837 l_retcode VARCHAR2(1) := NULL ;
838
839 -- For error handling
840 l_sqlcode VARCHAR2(100) := NULL ;
841 l_sqlerrm VARCHAR2(1000) := NULL ;
842
843 l_base_fcst_age_group1 NUMBER := 0 ;
844 l_base_fcst_age_group2 NUMBER := 0 ;
845 l_base_fcst_age_group3 NUMBER := 0 ;
846 l_base_fcst_age_group4 NUMBER := 0 ;
847
848 -- holds the rowid of the record in the oki_rnwl_age_by_orgs table
849 l_rag_rowid ROWID ;
850
851 -- Location within the program before the error was encountered.
852 l_loc VARCHAR2(100) ;
853
854 -- Holds the truncated start and end dates from gl_periods
855 l_glpr_start_date DATE ;
856 l_glpr_end_date DATE ;
857
858 -- Cusor declaration
859
860 -- Cursor to get all the organizations
861 CURSOR l_org_id_csr IS
862 SELECT DISTINCT shd.authoring_org_id org_id
863 , /*11510 changes*/ NULL organization_name
864 FROM oki_sales_k_hdrs shd
865 ;
866
867 -- Cursor to calculate the age of the renewals for each
868 -- organization
869 CURSOR l_rnwl_age_csr
870 ( p_summary_build_date IN DATE
871 , p_org_id IN NUMBER
872 , p_start_age_group1 IN NUMBER
873 , p_end_age_group1 IN NUMBER
874 , p_start_age_group2 IN NUMBER
875 , p_end_age_group2 IN NUMBER
876 , p_start_age_group3 IN NUMBER
877 , p_end_age_group3 IN NUMBER
878 , p_start_age_group4 IN NUMBER
879 ) IS
880 SELECT
881 NVL(SUM(DECODE(SIGN( (TRUNC(p_summary_build_date) -
882 TRUNC(shd.creation_date)) - p_start_age_group1),
883 1, DECODE(SIGN( p_end_age_group1 -
884 (TRUNC(p_summary_build_date) - trunc(shd.creation_date))),
885 1, shd.base_forecast_amount,
886 0, shd.base_forecast_amount,
887 0),
888 0, shd.base_forecast_amount,
889 0)), 0) age_group1,
890 NVL(SUM(DECODE(SIGN( (TRUNC(p_summary_build_date) -
891 TRUNC(shd.creation_date)) - p_start_age_group2),
892 1, DECODE(SIGN( p_end_age_group2 -
893 (TRUNC(p_summary_build_date) - TRUNC(shd.creation_date))),
894 1, shd.base_forecast_amount,
895 0, shd.base_forecast_amount,
896 0),
897 0, shd.base_forecast_amount,
898 0)), 0) age_group2,
899 NVL(SUM(DECODE(SIGN( (TRUNC(p_summary_build_date) -
900 TRUNC(shd.creation_date)) - p_start_age_group3),
901 1, decode(sign( p_end_age_group3 -
902 (trunc(p_summary_build_date) - trunc(shd.creation_date))),
903 1, shd.base_forecast_amount,
904 0, shd.base_forecast_amount,
905 0),
906 0, shd.base_forecast_amount,
907 0)), 0) age_group3,
908 NVL(SUM(DECODE(SIGN( (TRUNC(p_summary_build_date) -
912 0)), 0) age_group4
909 TRUNC(shd.creation_date)) - p_start_age_group4),
910 1, shd.base_forecast_amount,
911 0, shd.base_forecast_amount,
913 FROM oki_sales_k_hdrs shd
914 -- contract has undergone forecasting
915 WHERE shd.win_percent is not null
916 AND shd.close_date is not null
917 -- renewal contract is entered
918 AND shd.ste_code = 'ENTERED'
919 AND is_new_yn IS NULL
920 AND shd.authoring_org_id = p_org_id
921 ;
922 rec_l_rnwl_age_csr l_rnwl_age_csr%ROWTYPE ;
923
924
925 BEGIN
926
927 -- Initialize return code to success
928 l_retcode := '0';
929
930 l_loc := 'Looping through valid organizations.' ;
931 << l_org_id_csr_loop >>
932 -- Loop through all the organizations to calcuate the
933 -- appropriate amounts
934 FOR rec_l_org_id_csr IN l_org_id_csr LOOP
935
936 l_loc := 'Looping through valid periods.' ;
937 << g_glpr_csr_loop >>
938 -- Loop through all the periods
939 FOR rec_g_glpr_csr IN oki_utl_pvt.g_glpr_csr(
940 p_period_set_name, p_period_type, p_summary_build_date ) LOOP
941
942 -- Get the truncated gl_periods start and end dates
943 l_glpr_start_date := trunc(rec_g_glpr_csr.start_date );
944 l_glpr_end_date := trunc(rec_g_glpr_csr.end_date );
945
946 -- Re-initialize the amounts before calculating
947 l_base_fcst_age_group1 := 0 ;
948 l_base_fcst_age_group2 := 0 ;
949 l_base_fcst_age_group3 := 0 ;
950 l_base_fcst_age_group4 := 0 ;
951
952 l_loc := 'Opening cursor to determine the age.' ;
953 -- Calculate the forecast amount for a given organization
954 OPEN l_rnwl_age_csr (p_summary_build_date,
955 rec_l_org_id_csr.org_id, oki_load_rag_pvt.g_start_age_group1,
956 oki_load_rag_pvt.g_end_age_group1,
957 oki_load_rag_pvt.g_start_age_group2,
958 oki_load_rag_pvt.g_end_age_group2,
959 oki_load_rag_pvt.g_start_age_group3,
960 oki_load_rag_pvt.g_end_age_group3,
961 oki_load_rag_pvt.g_start_age_group4 ) ;
962 FETCH l_rnwl_age_csr INTO rec_l_rnwl_age_csr ;
963 IF l_rnwl_age_csr%FOUND THEN
964 l_base_fcst_age_group1 := rec_l_rnwl_age_csr.age_group1 ;
965 l_base_fcst_age_group2 := rec_l_rnwl_age_csr.age_group2 ;
966 l_base_fcst_age_group3 := rec_l_rnwl_age_csr.age_group3 ;
967 l_base_fcst_age_group4 := rec_l_rnwl_age_csr.age_group4 ;
968 END IF ;
969 CLOSE l_rnwl_age_csr ;
970
971 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
972 -- Determine if the record is a new one or an existing one
973 OPEN oki_load_rag_pvt.g_rag_csr ( rec_g_glpr_csr.period_set_name,
974 rec_g_glpr_csr.period_name, rec_l_org_id_csr.org_id,
975 oki_load_rag_pvt.g_all_cst_id, oki_load_rag_pvt.g_all_scs_code ) ;
976 FETCH oki_load_rag_pvt.g_rag_csr INTO rec_g_rag_csr ;
977 IF oki_load_rag_pvt.g_rag_csr%NOTFOUND THEN
978 l_loc := 'Insert the new record.' ;
979
980 -- Insert the current period data for the period
981 oki_load_rag_pvt.ins_rnwl_aging (
982 p_period_set_name => rec_g_glpr_csr.period_set_name
983 , p_period_name => rec_g_glpr_csr.period_name
984 , p_period_type => rec_g_glpr_csr.period_type
985 , p_authoring_org_id => rec_l_org_id_csr.org_id
986 , p_authoring_org_name => rec_l_org_id_csr.organization_name
987 , p_customer_party_id => oki_load_rag_pvt.g_all_cst_id
988 , p_customer_name => oki_load_rag_pvt.g_all_cst_name
989 , p_scs_code => oki_load_rag_pvt.g_all_scs_code
990 , p_age_group1 => l_base_fcst_age_group1
991 , p_age_group2 => l_base_fcst_age_group2
992 , p_age_group3 => l_base_fcst_age_group3
993 , p_age_group4 => l_base_fcst_age_group4
994 , x_retcode => l_retcode ) ;
995
996 IF l_retcode = '2' THEN
997 -- Load failed, exit immediately.
998 RAISE oki_load_rag_pvt.g_excp_exit_immediate ;
999 END IF ;
1000 ELSE
1001 l_loc := 'Update the existing record.' ;
1002 -- Record already exists, so perform an update
1003 oki_load_rag_pvt.upd_rnwl_aging (
1004 p_age_group1 => l_base_fcst_age_group1
1005 , p_age_group2 => l_base_fcst_age_group2
1006 , p_age_group3 => l_base_fcst_age_group3
1007 , p_age_group4 => l_base_fcst_age_group4
1008 , p_rag_rowid => rec_g_rag_csr.rowid
1009 , x_retcode => l_retcode ) ;
1010
1011 IF l_retcode = '2' THEN
1012 -- Load failed, exit immediately.
1013 RAISE oki_load_rag_pvt.g_excp_exit_immediate ;
1014 END IF ;
1015 END IF ;
1016 CLOSE oki_load_rag_pvt.g_rag_csr ;
1017
1018 END LOOP g_glpr_csr_loop ;
1019 END LOOP l_org_id_csr_loop ;
1023 WHEN oki_load_rag_pvt.g_excp_exit_immediate THEN
1020
1021
1022 EXCEPTION
1024 -- Do not log an error ; It has already been logged.
1025 -- Set return code to error
1026 x_retcode := '2' ;
1027
1028 WHEN OTHERS THEN
1029
1030 l_sqlcode := SQLCODE ;
1031 l_sqlerrm := SQLERRM ;
1032
1033 -- Set return code TO error
1034 x_retcode := '2' ;
1035
1036 fnd_message.set_name( application => 'OKI'
1037 , name => 'OKI_UNEXPECTED_FAILURE');
1038
1039 fnd_message.set_token( token => 'OBJECT_NAME'
1040 , value => 'OKI_LOAD_rag_PVT.CALC_RAG_SUM');
1041
1042 fnd_file.put_line( which => fnd_file.log
1043 , buff => fnd_message.get);
1044
1045 -- Log the location within the procedure where the error occurred
1046 fnd_message.set_name( application => 'OKI'
1047 , name => 'OKI_LOC_IN_PROG_FAILURE');
1048
1049 fnd_message.set_token( token => 'LOCATION'
1050 , value => l_loc);
1051
1052 fnd_file.put_line( which => fnd_file.log
1053 , buff => fnd_message.get);
1054
1055 fnd_file.put_line( which => fnd_file.log
1056 , buff => l_sqlcode||' '||l_sqlerrm );
1057 END calc_rag_sum ;
1058
1059 --------------------------------------------------------------------------------
1060 -- Procedure to create all the renewal aging by organization records. If an
1061 -- error is encountered in this procedure or subsequent procedures then
1062 -- rollback all changes. Once the table is loaded and the data is committed
1063 -- the load is considered successful even if update of the oki_refreshs
1064 -- table failed.
1065 --------------------------------------------------------------------------------
1066
1067 PROCEDURE crt_rnwl_aging
1068 ( p_period_set_name IN VARCHAR2
1069 , p_period_type IN VARCHAR2
1070 , p_summary_build_date IN DATE
1071 , x_errbuf OUT NOCOPY VARCHAR2
1072 , x_retcode OUT NOCOPY VARCHAR2
1073 ) IS
1074
1075 -- Local exception declaration
1076
1077 -- Exception to immediately exit the procedure
1078 l_excp_upd_refresh EXCEPTION ;
1079
1080
1081 -- Constant declaration
1082
1083 -- Name of the table for which data is being inserted
1084 l_table_name CONSTANT VARCHAR2(30) := 'OKI_RENEWAL_AGING' ;
1085
1086
1087
1088 -- Local variable declaration
1089
1090 -- For capturing the return code, 0 = success, 1 = warning, 2 = error
1091 l_retcode VARCHAR2(1) := NULL ;
1092
1093 -- For error handling
1094 l_sqlcode VARCHAR2(100) ;
1095 l_sqlerrm VARCHAR2(1000) ;
1096
1097
1098 BEGIN
1099
1100 SAVEPOINT oki_load_rag_pvt_crt_rnwl_age ;
1101
1102 -- initialize return code to success
1103 l_retcode := '0' ;
1104 x_retcode := '0' ;
1105
1106 -- Procedure to calculate the amounts for each dimension
1107 oki_load_rag_pvt.calc_rag_dtl1 (
1108 p_period_set_name => p_period_set_name
1109 , p_period_type => p_period_type
1110 , p_summary_build_date => p_summary_build_date
1111 , x_retcode => l_retcode ) ;
1112
1113 IF l_retcode = '2' THEN
1114 -- Load failed, exit immediately.
1115 RAISE oki_load_rag_pvt.g_excp_exit_immediate ;
1116 END IF ;
1117
1118 -- Procedure to calculate the amounts across subclasses
1119 oki_load_rag_pvt.calc_rag_dtl2 (
1120 p_period_set_name => p_period_set_name
1121 , p_period_type => p_period_type
1122 , p_summary_build_date => p_summary_build_date
1123 , x_retcode => l_retcode ) ;
1124
1125 IF l_retcode = '2' THEN
1126 -- Load failed, exit immediately.
1127 RAISE oki_load_rag_pvt.g_excp_exit_immediate ;
1128 END IF ;
1129
1130 -- Procedure to calculate the amounts across customers
1131 -- and subclasses
1132 oki_load_rag_pvt.calc_rag_sum (
1133 p_period_set_name => p_period_set_name
1134 , p_period_type => p_period_type
1135 , p_summary_build_date => p_summary_build_date
1136 , x_retcode => l_retcode ) ;
1137
1138 IF l_retcode = '2' THEN
1139 -- Load failed, exit immediately.
1140 RAISE oki_load_rag_pvt.g_excp_exit_immediate ;
1141 END IF ;
1142
1143 COMMIT;
1144
1145 SAVEPOINT oki_load_rag_pvt_upd_refresh ;
1146
1147 -- Table loaded successfully. Log message IN concurrent manager
1148 -- log indicating successful load.
1149 fnd_message.set_name( application => 'OKI'
1150 , name => 'OKI_TABLE_LOAD_SUCCESS');
1151
1152 fnd_message.set_token( token => 'TABLE_NAME'
1153 , value => l_table_name );
1154
1155 fnd_file.put_line( which => fnd_file.log
1156 , buff => fnd_message.get);
1157
1158 oki_refresh_pvt.update_oki_refresh( l_table_name, l_retcode ) ;
1159
1160 IF l_retcode in ('1', '2') THEN
1161 -- Update to OKI_REFRESHS failed, exit immediately.
1162 RAISE l_excp_upd_refresh ;
1163 END IF ;
1164
1165 COMMIT ;
1166
1167 EXCEPTION
1168 WHEN l_excp_upd_refresh THEN
1169 -- Do not log error; It has already been logged by the refreshs
1170 -- program
1171 x_retcode := l_retcode ;
1172
1173 ROLLBACK to oki_load_rag_pvt_upd_refresh ;
1174
1175 WHEN oki_load_rag_pvt.g_excp_exit_immediate THEN
1176 -- Do not log an error ; It has already been logged.
1177 -- Set return code to error
1178 x_retcode := '2' ;
1179
1180 ROLLBACK TO oki_load_rag_pvt_crt_rnwl_age ;
1181
1182 WHEN OTHERS THEN
1183
1184 l_sqlcode := SQLCODE ;
1185 l_sqlerrm := SQLERRM ;
1186
1187 -- Set return code to error
1188 x_retcode := '2' ;
1189
1190 -- Rollback all transactions
1191 ROLLBACK TO oki_load_rag_pvt_crt_rnwl_age ;
1192
1193
1194 fnd_message.set_name( application => 'OKI'
1195 , name => 'OKI_UNEXPECTED_FAILURE');
1196
1197 fnd_message.set_token( token => 'OBJECT_NAME'
1198 , value => 'OKI_LOAD_RAG_PVT.CRT_RNWL_AGE_BY_ORGS');
1199
1200 fnd_file.put_line( which => fnd_file.log
1201 , buff => fnd_message.get);
1202
1203 fnd_file.put_line( which => fnd_file.log
1204 , buff => l_sqlcode||' '||l_sqlerrm );
1205 END crt_rnwl_aging ;
1206
1207 BEGIN
1208 -- Initialize the global variables used to log this job run
1209 -- FROM concurrent manager
1210 g_request_id := fnd_global.conc_request_id ;
1211 g_program_application_id := fnd_global.prog_appl_id ;
1212 g_program_id := fnd_global.conc_program_id ;
1213 g_program_update_date := SYSDATE ;
1214
1215 END oki_load_rag_pvt ;