1 PACKAGE BODY oki_load_rbs_pvt as
2 /* $Header: OKIRRBSB.pls 115.24 2003/11/24 08:25:10 kbajaj ship $ */
3
4 --------------------------------------------------------------------------------
5 -- Modification History
6 -- 25-Aug-2001 mezra Changed program to reflect the addition of
7 -- new columns: authoring_org_id,
8 -- authoring_org_name, and subclass code.
9 -- 10-Sep-2001 mezra Added column value, All Categories, for summary
10 -- level of all scs_code; All Organizations, for
11 -- summary level of all organizations.
12 -- 18-Sep-2001 mezra Moved rbs_csr from local cursor to global
13 -- cursor since it is used by all the calc
14 -- procedures.
15 -- 25-Sep-2001 mezra Change usd_ columns to base_.
16 -- 22-Oct-2001 mezra Changed All Categories value to -1.
17 -- 24-Oct-2001 mezra Removed trunc on date columns to increase
18 -- performance since index will be used.
19 -- 26-NOV-2002 rpotnuru NOCOPY Changes
20 -- 19-Dec-2002 brrao UTF-8 Changes to Org Name
21 --
22 -- 29-Oct-2003 axraghav Modified calc_rbs_dtl1,calc_rbs_dtl2,calc_rbs_sum
23 -- to join to oki_cov_prd_lines and also to populate
24 -- null values for organization_name
25 --------------------------------------------------------------------------------
26
27 -- Global exception declaration
28
29 -- Generic exception to immediately exit the procedure
30 g_excp_exit_immediate EXCEPTION ;
31
32
33 -- Global constant delcaration
34
35 -- Constants for the "All" organization and subclass record
36 g_all_org_id CONSTANT NUMBER := -1 ;
37 g_all_org_name CONSTANT VARCHAR2(240) := 'All Organizations' ;
38 g_all_scs_code CONSTANT VARCHAR2(30) := '-1' ;
39
40
41 -- Global cursor declaration
42
43 -- Cursor to retrieve the rowid for the selected record
44 -- If a rowid is retrieved, then the record will be updated,
45 -- else the record will be inserted.
46 CURSOR g_rbs_csr
47 ( p_period_set_name IN VARCHAR2
48 , p_period_name IN VARCHAR2
49 , p_authoring_org_id IN NUMBER
50 , p_status_code IN VARCHAR2
51 , p_scs_code IN VARCHAr2
52 ) IS
53 SELECT rowid
54 FROM oki_renew_by_statuses rbs
55 WHERE rbs.period_set_name = p_period_set_name
56 AND rbs.period_name = p_period_name
57 AND rbs.authoring_org_id = p_authoring_org_id
58 AND rbs.status_code = p_status_code
59 AND rbs.scs_code = p_scs_code
60 ;
61
62 --------------------------------------------------------------------------------
63 -- Procedure to insert records into the oki_renew_by_statuses table.
64
65 --------------------------------------------------------------------------------
66 PROCEDURE ins_rnwl_by_stat
67 ( p_period_name IN VARCHAR2
68 , p_period_set_name IN VARCHAR2
69 , p_period_type IN VARCHAR2
70 , p_authoring_org_id IN NUMBER
71 , p_authoring_org_name IN VARCHAR2
72 , p_status_code IN VARCHAR2
73 , p_scs_code IN VARCHAR2
74 , p_base_amount IN NUMBER
75 , p_contract_count IN NUMBER
76 , x_retcode OUT NOCOPY VARCHAR2
77 ) IS
78
79 -- Local variable declaration
80
81 -- For error handling
82 l_sqlcode VARCHAR2(100) ;
83 l_sqlerrm VARCHAR2(1000) ;
84
85 l_sequence NUMBER := NULL ;
86
87 -- Cursor declaration
88 CURSOR l_seq_num IS
89 SELECT oki_renew_by_statuses_s1.nextval seq
90 FROM dual ;
91 rec_l_seq_num l_seq_num%ROWTYPE ;
92
93 BEGIN
94
95 OPEN l_seq_num ;
96 FETCH l_seq_num INTO rec_l_seq_num ;
97 -- unable to generate sequence number, exit immediately
98 IF l_seq_num%NOTFOUND THEN
99 RAISE g_excp_exit_immediate ;
100 END IF ;
101 l_sequence := rec_l_seq_num.seq ;
102 CLOSE l_seq_num ;
103
104 -- initialize return code to success
105 x_retcode := '0';
106
107 INSERT INTO oki_renew_by_statuses
108 ( id
109 , period_set_name
110 , period_name
111 , period_type
112 , authoring_org_id
113 , authoring_org_name
114 , status_code
115 , scs_code
116 , base_amount
117 , contract_count
118 , request_id
119 , program_application_id
120 , program_id
121 , program_update_date )
122 VALUES ( l_sequence
123 , p_period_set_name
124 , p_period_name
125 , p_period_type
126 , p_authoring_org_id
127 , p_authoring_org_name
128 , p_status_code
129 , p_scs_code
130 , p_base_amount
131 , p_contract_count
132 , oki_load_rbs_pvt.g_request_id
133 , oki_load_rbs_pvt.g_program_application_id
134 , oki_load_rbs_pvt.g_program_id
135 , oki_load_rbs_pvt.g_program_update_date ) ;
136
137
138 EXCEPTION
139 WHEN oki_load_rbs_pvt.g_excp_exit_immediate THEN
140 l_sqlcode := SQLCODE ;
141 l_sqlerrm := SQLERRM ;
142
143 -- Set return code to error
144 x_retcode := '2';
145
146 fnd_message.set_name( application => 'OKI'
147 , name => 'OKI_TABLE_LOAD_FAILURE') ;
148
149 fnd_message.set_token( token => 'TABLE_NAME'
150 , value => 'OKI_RENEW_BY_STATUSES' ) ;
151
152 fnd_file.put_line( which => fnd_file.log
153 , buff => fnd_message.get) ;
154
155 fnd_file.put_line( which => fnd_file.log
156 , buff => l_sqlcode||' '||l_sqlerrm ) ;
157
158 WHEN OTHERS THEN
159 l_sqlcode := SQLCODE ;
160 l_sqlerrm := SQLERRM ;
161
162 -- Set return code to error
163 x_retcode := '2';
164
165 fnd_message.set_name( application => 'OKI'
166 , name => 'OKI_TABLE_LOAD_FAILURE') ;
167
168 fnd_message.set_token( token => 'TABLE_NAME'
169 , value => 'OKI_RENEW_BY_STATUSES' ) ;
170
171 fnd_file.put_line( which => fnd_file.log
172 , buff => fnd_message.get) ;
173
174 fnd_file.put_line( which => fnd_file.log
175 , buff => l_sqlcode||' '||l_sqlerrm ) ;
176 END ins_rnwl_by_stat ;
177
178
179 --------------------------------------------------------------------------------
180 -- Procedure to update records in the oki_renew_by_statuses table.
181
182 --------------------------------------------------------------------------------
183 PROCEDURE upd_rnwl_by_stat
184 ( p_base_amount IN NUMBER
185 , p_contract_count IN NUMBER
186 , p_rowid IN ROWID
187 , x_retcode OUT NOCOPY VARCHAR2
188 ) IS
189
190 -- Local variable declaration
191
192 -- For error handling
193 l_sqlcode VARCHAR2(100) ;
194 l_sqlerrm VARCHAR2(1000) ;
195
196
197 BEGIN
198
199 -- initialize return code to success
200 x_retcode := '0';
201
202 UPDATE oki_renew_by_statuses SET
203 base_amount = p_base_amount
204 , contract_count = p_contract_count
205 , request_id = oki_load_rbs_pvt.g_request_id
206 , program_application_id = oki_load_rbs_pvt.g_program_application_id
207 , program_id = oki_load_rbs_pvt.g_program_id
208 , program_update_date = oki_load_rbs_pvt.g_program_update_date
209 WHERE ROWID = p_rowid ;
210
211
212 EXCEPTION
213 WHEN OTHERS THEN
214 l_sqlcode := SQLCODE ;
215 l_sqlerrm := SQLERRM ;
216
217 -- Set return code to error
218 x_retcode := '2';
219
220 fnd_message.set_name( application => 'OKI'
221 , name => 'OKI_UNEXPECTED_FAILURE');
222
223 fnd_message.set_token( token => 'OBJECT_NAME'
224 , value => 'OKI_LOAD_RBS_PVT.UPD_RNWL_BY_STAT');
225
226 fnd_file.put_line( which => fnd_file.log
227 , buff => fnd_message.get);
228
229 fnd_file.put_line( which => fnd_file.log
230 , buff => l_sqlcode||' '||l_sqlerrm );
231 END upd_rnwl_by_stat ;
232
233
234 --------------------------------------------------------------------------------
235 -- Procedure to calculate the counts and amounts of expired, WIP,
236 -- signed, and forecasted contracts.
237 -- Calculates the counts and amounts by each dimension:
238 -- period set name
239 -- period type
240 -- period name
241 -- status
242 -- subclass
243 -- organization
244 --
245 --------------------------------------------------------------------------------
246 PROCEDURE calc_rbs_dtl1
247 ( p_period_set_name IN VARCHAR2
248 , p_period_type IN VARCHAR2
249 , p_summary_build_date IN DATE
250 , x_retcode OUT NOCOPY VARCHAR2
251 ) IS
252
253
254 -- Local variable declaration
255
256 -- For capturing the return code, 0 = success, 1 = warning, 2 = error
257 l_retcode VARCHAR2(1) := NULL ;
258
259 -- For error handling
260 l_sqlcode VARCHAR2(100) := NULL ;
261 l_sqlerrm VARCHAR2(1000) := NULL ;
262
263 -- Holds the calculated contract amount and counts
264 l_contract_count NUMBER ;
265 l_base_contract_amount NUMBER ;
266
267 -- holds the rowid of the record in the oki_renew_by_statuses table
268 l_rbs_rowid ROWID := null ;
269
270 -- Location within the program before the error was encountered.
271 l_loc VARCHAR2(100) ;
272
273 -- Holds the truncated start and end dates from gl_periods
274 l_glpr_start_date DATE ;
275 l_glpr_end_date DATE ;
276
277 -- Cursor declaration
278
279 -- Cursor to get all the organizations and subclasses
280 CURSOR l_org_id_csr IS
281 SELECT DISTINCT shd.authoring_org_id authoring_org_id
282 , /*11510 change*/ NULL authoring_org_name
283 , shd.scs_code scs_code
284 FROM oki_sales_k_hdrs shd
285 ;
286
287 -- Cursor to count the number of contracts with expired lines
288 -- for a particular organization and subclass
289 CURSOR l_expired_cnt_csr
290 ( p_start_date IN DATE
291 , p_end_date IN DATE
292 , p_authoring_org_id IN NUMBER
293 , p_scs_code IN VARCHAR2
294 ) IS
295 SELECT COUNT(DISTINCT(shd.chr_id)) contract_count
296 FROM /*11510 change removed oki_Expired_lines and added oki_cov_prd_lines */
297 oki_cov_prd_lines cpl
298 , oki_sales_k_hdrs shd
299 WHERE cpl.end_date BETWEEN p_start_date AND p_end_date
300 /*11510 change start*/
301 AND cpl.is_exp_not_renewed_yn='Y'
302 /*11510 change end*/
303 AND cpl.chr_id = shd.chr_id
304 AND shd.authoring_org_id = p_authoring_org_id
305 AND shd.scs_code = p_scs_code
306 ;
307 rec_l_expired_cnt_csr l_expired_cnt_csr%ROWTYPE ;
308
309 -- Cursor to sum the amount of the expired lines
310 -- for a particular organization and subclass
311 CURSOR l_expired_amt_csr
312 ( p_start_date IN DATE
313 , p_end_date IN DATE
314 , p_authoring_org_id IN NUMBER
315 , p_scs_code IN vARCHAR2
316 ) IS
317 SELECT NVL(SUM(cpl.base_price_negotiated), 0) base_price_negotiated
318 /*11510 change removed oki_Expired_lines and added oki_cov_prd_lines */
319 FROM oki_cov_prd_lines cpl
320 , oki_sales_k_hdrs shd
321 WHERE cpl.end_date BETWEEN p_start_date AND p_end_date
322 /*11510 change start*/
323 AND cpl.is_exp_not_renewed_yn='Y'
324 /*11510 change end*/
325 AND cpl.chr_id = shd.chr_id
326 AND shd.authoring_org_id = p_authoring_org_id
327 AND shd.scs_code = p_scs_code
328 ;
329 rec_l_expired_amt_csr l_expired_amt_csr%ROWTYPE ;
330
331 -- Cursor to count and sum the amount of the WIP contracts
332 -- for a particular organization and subclass
333 CURSOR l_wip_csr
334 ( p_start_date IN DATE
335 , p_end_date IN DATE
336 , p_authoring_org_id IN NUMBER
337 , p_scs_code IN vARCHAR2
338 ) IS
339 SELECT COUNT(*) contract_count
340 , NVL(SUM(shd.base_contract_amount), 0) base_contract_amount
341 FROM oki_sales_k_hdrs shd
342 WHERE shd.ste_code = 'ENTERED'
343 AND NVL(shd.close_date, shd.start_date)
344 BETWEEN p_start_date AND p_end_date
345 AND shd.is_new_yn IS NULL
346 AND shd.authoring_org_id = p_authoring_org_id
347 AND shd.scs_code = p_scs_code
348 ;
349 rec_l_wip_csr l_wip_csr%ROWTYPE ;
350
351 -- Cursor to count and sum the amount of the signed contracts
352 -- for a particular organization and subclass
353 CURSOR l_signed_csr
354 ( p_start_date IN DATE
355 , p_end_date IN DATE
356 , p_authoring_org_id IN NUMBER
357 , p_scs_code IN VARCHAR2
358 ) IS
359 SELECT COUNT(*) contract_count
360 , NVL(SUM(shd.base_contract_amount), 0) base_contract_amount
361 FROM oki_sales_k_hdrs shd
362 WHERE shd.ste_code IN ('ACTIVE','SIGNED')
363 AND LEAST(NVL(shd.date_signed, shd.start_date), shd.start_date)
364 BETWEEN p_start_date AND p_end_date
365 AND shd.is_new_yn IS NULL
366 AND shd.authoring_org_id = p_authoring_org_id
367 AND shd.scs_code = p_scs_code
368 ;
369 rec_l_signed_csr l_signed_csr%ROWTYPE ;
370
371 -- Cursor to count and sum the amount of the forecasted contracts
372 -- for a particular organization and subclass
373 CURSOR l_forecast_csr
374 ( p_start_date IN DATE
375 , p_end_date IN DATE
376 , p_authoring_org_id IN NUMBER
377 , p_scs_code IN VARCHAR2
378 ) IS
379 SELECT COUNT(*) contract_count
380 , NVL(SUM(base_forecast_amount), 0) base_contract_amount
381 FROM oki_sales_k_hdrs shd
382 WHERE shd.close_date BETWEEN p_start_date AND p_end_date
383 AND shd.win_percent IS NOT NULL
384 AND shd.close_date IS NOT NULL
385 AND shd.is_new_yn IS NULL
386 AND shd.authoring_org_id = p_authoring_org_id
387 AND shd.scs_code = p_scs_code
388 ;
389 rec_l_forecast_csr l_forecast_csr%ROWTYPE ;
390
391 BEGIN
392
393 -- initialize return code to success
394 l_retcode := '0' ;
395
396 l_loc := 'Looping through valid organizations.' ;
397 << l_org_id_csr_loop >>
398 -- Loop through all the organizations to calcuate the
399 -- appropriate amounts
400 FOR rec_l_org_id_csr IN l_org_id_csr LOOP
401
402 l_loc := 'Looping through valid periods.' ;
403 << rec_g_glpr_csr_loop >>
404 -- Loop through all the periods
405 FOR rec_g_glpr_csr IN oki_utl_pvt.g_glpr_csr(
406 p_period_set_name, p_period_type, p_summary_build_date ) LOOP
407
408 -- Get the truncated gl_periods start and end dates
409 l_glpr_start_date := trunc(rec_g_glpr_csr.start_date );
410 l_glpr_end_date := trunc(rec_g_glpr_csr.end_date );
411
412 -- Re-initialize the counts and amounts before calculating
413 l_base_contract_amount := 0 ;
414 l_contract_count := 0 ;
415
416 l_loc := 'Opening cursor to determine the expired count.' ;
417
418 -- Calculate expired amounts and counts
419 -- Fetch count of expired contracts
420 OPEN l_expired_cnt_csr ( l_glpr_start_date, l_glpr_end_date,
421 rec_l_org_id_csr.authoring_org_id, rec_l_org_id_csr.scs_code ) ;
422 FETCH l_expired_cnt_csr INTO rec_l_expired_cnt_csr ;
423 IF l_expired_cnt_csr%FOUND THEN
424 l_contract_count := rec_l_expired_cnt_csr.contract_count ;
425 END IF ;
426 CLOSE l_expired_cnt_csr ;
427
428 l_loc := 'Opening cursor to determine the expired sum.' ;
429 -- Fetch the sum of the amount of the expired lines
430 OPEN l_expired_amt_csr( l_glpr_start_date, l_glpr_end_date,
431 rec_l_org_id_csr.authoring_org_id, rec_l_org_id_csr.scs_code ) ;
432 FETCH l_expired_amt_csr INTO rec_l_expired_amt_csr ;
433 IF l_expired_amt_csr%FOUND THEN
434 l_base_contract_amount := rec_l_expired_amt_csr.base_price_negotiated ;
435 END IF ;
436 CLOSE l_expired_amt_csr ;
437
438 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
439 -- Determine if the record is a new one or an existing one
440 OPEN oki_load_rbs_pvt.g_rbs_csr( rec_g_glpr_csr.period_set_name,
441 rec_g_glpr_csr.period_name, rec_l_org_id_csr.authoring_org_id,
442 'EXPIRED', rec_l_org_id_csr.scs_code ) ;
443 FETCH oki_load_rbs_pvt.g_rbs_csr INTO l_rbs_rowid ;
444 IF oki_load_rbs_pvt.g_rbs_csr%NOTFOUND THEN
445 l_loc := 'Insert the new record.' ;
446 -- Insert the current period data for the period
447 oki_load_rbs_pvt.ins_rnwl_by_stat(
448 p_period_name => rec_g_glpr_csr.period_name
449 , p_period_set_name => rec_g_glpr_csr.period_set_name
450 , p_period_type => rec_g_glpr_csr.period_type
451 , p_authoring_org_id => rec_l_org_id_csr.authoring_org_id
452 , p_authoring_org_name => rec_l_org_id_csr.authoring_org_name
453 , p_status_code => 'EXPIRED'
454 , p_scs_code => rec_l_org_id_csr.scs_code
455 , p_base_amount => l_base_contract_amount
456 , p_contract_count => l_contract_count
457 , x_retcode => l_retcode ) ;
458
459 IF l_retcode = '2' THEN
460 -- Load failed, exit immediately.
461 RAISE oki_load_rbs_pvt.g_excp_exit_immediate ;
462 END IF ;
463
464 ELSE
465 l_loc := 'Update the existing record.' ;
466 -- Record already exists, so perform an update
467 oki_load_rbs_pvt.upd_rnwl_by_stat(
468 p_base_amount => l_base_contract_amount
469 , p_contract_count => l_contract_count
470 , p_rowid => l_rbs_rowid
471 , x_retcode => l_retcode ) ;
472
473 IF l_retcode = '2' THEN
474 -- Load failed, exit immediately.
475 RAISE oki_load_rbs_pvt.g_excp_exit_immediate ;
476 END IF ;
477
478 END IF ;
479 CLOSE oki_load_rbs_pvt.g_rbs_csr;
480
481
482 -- Re-initialize the counts and amounts before calculating
483 l_base_contract_amount := 0 ;
484 l_contract_count := 0 ;
485
486 l_loc := 'Opening cursor to determine the WIP count and sum.' ;
487 -- Calculate WIP amounts and counts
488 OPEN l_wip_csr( l_glpr_start_date, l_glpr_end_date,
489 rec_l_org_id_csr.authoring_org_id, rec_l_org_id_csr.scs_code ) ;
490 FETCH l_wip_csr INTO rec_l_wip_csr ;
491 IF l_wip_csr%FOUND THEN
492 l_contract_count := rec_l_wip_csr.contract_count ;
493 l_base_contract_amount := rec_l_wip_csr.base_contract_amount ;
494 END IF ;
495 CLOSE l_wip_csr;
496
497 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
498 -- Determine if the record is a new one or an existing one
499 OPEN oki_load_rbs_pvt.g_rbs_csr( rec_g_glpr_csr.period_set_name,
500 rec_g_glpr_csr.period_name, rec_l_org_id_csr.authoring_org_id,
501 'WIP', rec_l_org_id_csr.scs_code ) ;
502 FETCH oki_load_rbs_pvt.g_rbs_csr INTO l_rbs_rowid ;
503 IF oki_load_rbs_pvt.g_rbs_csr%NOTFOUND THEN
504 l_loc := 'Insert the new record.' ;
505 -- Insert the current period data for the period
506 oki_load_rbs_pvt.ins_rnwl_by_stat(
507 p_period_set_name => rec_g_glpr_csr.period_set_name
508 , p_period_name => rec_g_glpr_csr.period_name
509 , p_period_type => rec_g_glpr_csr.period_type
510 , p_authoring_org_id => rec_l_org_id_csr.authoring_org_id
511 , p_authoring_org_name => rec_l_org_id_csr.authoring_org_name
512 , p_status_code => 'WIP'
513 , p_scs_code => rec_l_org_id_csr.scs_code
514 , p_base_amount => l_base_contract_amount
515 , p_contract_count => l_contract_count
516 , x_retcode => l_retcode ) ;
517
518 IF l_retcode = '2' THEN
519 -- Load failed, exit immediately.
520 RAISE oki_load_rbs_pvt.g_excp_exit_immediate ;
521 END IF ;
522
523 ELSE
524 l_loc := 'Update the existing record.' ;
525 -- Record already exists, so perform an update
526 oki_load_rbs_pvt.upd_rnwl_by_stat(
527 p_base_amount => l_base_contract_amount
528 , p_contract_count => l_contract_count
529 , p_rowid => l_rbs_rowid
530 , x_retcode => l_retcode ) ;
531 IF l_retcode = '2' THEN
532 -- Load failed, exit immediately.
533 RAISE oki_load_rbs_pvt.g_excp_exit_immediate ;
534 END IF ;
535
536 END IF;
537 CLOSE oki_load_rbs_pvt.g_rbs_csr;
538
539 -- Re-initialize the counts and amounts before calculating
540 l_base_contract_amount := 0 ;
541 l_contract_count := 0 ;
542
543 l_loc := 'Opening cursor to determine the signed count and sum.' ;
544 -- Calculate signed amounts and counts
545 OPEN l_signed_csr( l_glpr_start_date, l_glpr_end_date,
546 rec_l_org_id_csr.authoring_org_id, rec_l_org_id_csr.scs_code ) ;
547 FETCH l_signed_csr INTO rec_l_signed_csr ;
548 IF l_signed_csr%FOUND THEN
549 l_contract_count := rec_l_signed_csr.contract_count ;
550 l_base_contract_amount := rec_l_signed_csr.base_contract_amount ;
551 END IF ;
552 CLOSE l_signed_csr ;
553
554 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
555 -- Determine if the record is a new one or an existing one
556 OPEN oki_load_rbs_pvt.g_rbs_csr( rec_g_glpr_csr.period_set_name,
557 rec_g_glpr_csr.period_name, rec_l_org_id_csr.authoring_org_id,
558 'SIGNED', rec_l_org_id_csr.scs_code ) ;
559 FETCH oki_load_rbs_pvt.g_rbs_csr INTO l_rbs_rowid ;
560 IF oki_load_rbs_pvt.g_rbs_csr%NOTFOUND THEN
561 l_loc := 'Insert the new record.' ;
562 -- Insert the current period data for the period
563 oki_load_rbs_pvt.ins_rnwl_by_stat(
564 p_period_set_name => rec_g_glpr_csr.period_set_name
565 , p_period_name => rec_g_glpr_csr.period_name
566 , p_period_type => rec_g_glpr_csr.period_type
567 , p_authoring_org_id => rec_l_org_id_csr.authoring_org_id
568 , p_authoring_org_name => rec_l_org_id_csr.authoring_org_name
569 , p_status_code => 'SIGNED'
570 , p_scs_code => rec_l_org_id_csr.scs_code
571 , p_base_amount => l_base_contract_amount
572 , p_contract_count => l_contract_count
573 , x_retcode => l_retcode ) ;
574
575 IF l_retcode = '2' THEN
576 -- Load failed, exit immediately.
577 RAISE oki_load_rbs_pvt.g_excp_exit_immediate ;
578 END IF ;
579
580 ELSE
581 l_loc := 'Update the existing record.' ;
582 -- Record already exists, so perform an update
583 oki_load_rbs_pvt.upd_rnwl_by_stat(
584 p_base_amount => l_base_contract_amount
585 , p_contract_count => l_contract_count
586 , p_rowid => l_rbs_rowid
587 , x_retcode => l_retcode ) ;
588
589 IF l_retcode = '2' THEN
590 -- Load failed, exit immediately.
591 RAISE oki_load_rbs_pvt.g_excp_exit_immediate ;
592 END IF ;
593
594 END IF;
595 CLOSE oki_load_rbs_pvt.g_rbs_csr;
596
597 -- Re-initialize the counts and amounts before calculating
598 l_base_contract_amount := 0 ;
599 l_contract_count := 0 ;
600
601 l_loc := 'Opening cursor to determine the forecast count and sum.' ;
602 -- Calculate forecast amounts and counts
603 OPEN l_forecast_csr( l_glpr_start_date, l_glpr_end_date,
604 rec_l_org_id_csr.authoring_org_id, rec_l_org_id_csr.scs_code ) ;
605 FETCH l_forecast_csr into rec_l_forecast_csr ;
606 IF l_forecast_csr%FOUND THEN
607 l_contract_count := rec_l_forecast_csr.contract_count ;
608 l_base_contract_amount := rec_l_forecast_csr.base_contract_amount ;
609 END IF ;
610 CLOSE l_forecast_csr ;
611
612 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
613 -- Determine if the record is a new one or an existing one
614 OPEN oki_load_rbs_pvt.g_rbs_csr( rec_g_glpr_csr.period_set_name,
615 rec_g_glpr_csr.period_name, rec_l_org_id_csr.authoring_org_id,
616 'FORECAST', rec_l_org_id_csr.scs_code ) ;
617 FETCH oki_load_rbs_pvt.g_rbs_csr INTO l_rbs_rowid ;
618 IF oki_load_rbs_pvt.g_rbs_csr%NOTFOUND THEN
619 -- Insert the current period data for the period
620 l_loc := 'Insert the new record.' ;
621 oki_load_rbs_pvt.ins_rnwl_by_stat(
622 p_period_name => rec_g_glpr_csr.period_name
623 , p_period_set_name => rec_g_glpr_csr.period_set_name
624 , p_period_type => rec_g_glpr_csr.period_type
625 , p_authoring_org_id => rec_l_org_id_csr.authoring_org_id
626 , p_authoring_org_name => rec_l_org_id_csr.authoring_org_name
627 , p_status_code => 'FORECAST'
628 , p_scs_code => rec_l_org_id_csr.scs_code
629 , p_base_amount => l_base_contract_amount
630 , p_contract_count => l_contract_count
631 , x_retcode => l_retcode ) ;
632
633 IF l_retcode = '2' THEN
634 -- Load failed, exit immediately.
635 RAISE oki_load_rbs_pvt.g_excp_exit_immediate ;
636 END IF ;
637
638 ELSE
639 l_loc := 'Update the existing record.' ;
640 -- Record already exists, so perform an update
641 oki_load_rbs_pvt.upd_rnwl_by_stat(
642 p_base_amount => l_base_contract_amount
643 , p_contract_count => l_contract_count
644 , p_rowid => l_rbs_rowid
645 , x_retcode => l_retcode ) ;
646
647 IF l_retcode = '2' THEN
648 -- Load failed, exit immediately.
649 RAISE oki_load_rbs_pvt.g_excp_exit_immediate ;
650 END IF ;
651
652 END IF ;
653 CLOSE oki_load_rbs_pvt.g_rbs_csr ;
654 END LOOP rec_g_glpr_csr_loop ;
655 END LOOP l_org_id_csr_loop ;
656
657 EXCEPTION
658 WHEN oki_load_rbs_pvt.g_excp_exit_immediate THEN
659 -- Do not log an error ; It has already been logged.
660 -- Set return code to error
661 x_retcode := '2' ;
662
663 WHEN OTHERS THEN
664 l_sqlcode := SQLCODE ;
665 l_sqlerrm := SQLERRM ;
666
667 -- Set return code to error
668 x_retcode := '2';
669
670 fnd_message.set_name( application => 'OKI'
671 , name => 'OKI_UNEXPECTED_FAILURE');
672
673 fnd_message.set_token( token => 'OBJECT_NAME'
674 , value => 'OKI_LOAD_RBS_PVT.OKI_CALC_RBS_DTL1' );
675
676 fnd_file.put_line( which => fnd_file.log
677 , buff => fnd_message.get);
678
679 -- Log the location within the procedure where the error occurred
680 fnd_message.set_name( application => 'OKI'
681 , name => 'OKI_LOC_IN_PROG_FAILURE');
682
683 fnd_message.set_token( token => 'LOCATION'
684 , value => l_loc);
685
686 fnd_file.put_line( which => fnd_file.log
687 , buff => fnd_message.get);
688
689
690 fnd_file.put_line( which => fnd_file.log
691 , buff => l_sqlcode||' '||l_sqlerrm );
692
693 end calc_rbs_dtl1 ;
694
695 --------------------------------------------------------------------------------
696 -- Procedure to calculate the counts and amounts of expired, WIP,
697 -- signed, and forecasted contracts.
698 -- Calculates the counts and amounts across organizations:
699 -- each period set name
700 -- each period type
701 -- each period name
702 -- each status
703 -- each subclass
704 -- all organizations
705 --
706 --------------------------------------------------------------------------------
707 PROCEDURE calc_rbs_dtl2
708 ( p_period_set_name IN VARCHAR2
709 , p_period_type IN VARCHAR2
710 , p_summary_build_date IN DATE
711 , x_retcode OUT NOCOPY VARCHAR2
712 ) IS
713
714
715 -- Local variable declaration
716
717 -- For capturing the return code, 0 = success, 1 = warning, 2 = error
718 l_retcode VARCHAR2(1) := NULL ;
719
720 -- For error handling
721 l_sqlcode VARCHAR2(100) := NULL ;
722 l_sqlerrm VARCHAR2(1000) := NULL ;
723
724 -- Holds the contract amount and counts
725 l_contract_count NUMBER ;
726 l_base_contract_amount NUMBER ;
727
728 -- holds the rowid of the record in the oki_renew_by_statuses table
729 l_rbs_rowid ROWID := null ;
730
731 -- Location within the program before the error was encountered.
732 l_loc VARCHAR2(100) ;
733
734 -- Holds the truncated start and end dates from gl_periods
735 l_glpr_start_date DATE ;
736 l_glpr_end_date DATE ;
737
738 -- Cusror declaration
739
740 -- Cursor to get all the organizations
741 CURSOR l_scs_csr IS
742 SELECT DISTINCT shd.scs_code
743 FROM oki_sales_k_hdrs shd
744 ;
745
746 -- Cursor to count the number of contracts with expired lines
747 -- for each organization
748 CURSOR l_expired_cnt_csr
749 ( p_start_date IN DATE
750 , p_end_date IN DATE
751 , p_scs_code IN VARCHAR2
752 ) IS
753 SELECT COUNT(DISTINCT(shd.chr_id)) contract_count
754 /*11510 change removed oki_Expired_lines and added oki_cov_prd_lines */
755 FROM oki_cov_prd_lines cpl
756 , oki_sales_k_hdrs shd
757 WHERE cpl.end_date BETWEEN p_start_date AND p_end_date
758 /*11510 change start*/
759 AND cpl.is_exp_not_renewed_yn='Y'
760 /*11510 change end*/
761 AND cpl.chr_id = shd.chr_id
762 AND shd.scs_code = p_scs_code
763 ;
764 rec_l_expired_cnt_csr l_expired_cnt_csr%ROWTYPE ;
765
766 -- Cursor to sum the amount of the expired lines
767 -- for each subclass
768 CURSOR l_expired_amt_csr
769 ( p_start_date IN DATE
770 , p_end_date IN DATE
771 , p_scs_code IN VARCHAR2
772
773 ) IS
774 SELECT NVL(SUM(cpl.base_price_negotiated), 0) base_price_negotiated
775 /*11510 change removed oki_Expired_lines and added oki_cov_prd_lines */
776 FROM oki_cov_prd_lines cpl
777 , oki_sales_k_hdrs shd
778 WHERE cpl.end_date BETWEEN p_start_date AND p_end_date
779 /*11510 change start*/
780 AND cpl.is_exp_not_renewed_yn='Y'
781 /*11510 change end*/
782 AND cpl.chr_id = shd.chr_id
783 AND shd.scs_code = p_scs_code
784 ;
785 rec_l_expired_amt_csr l_expired_amt_csr%ROWTYPE ;
786
787 -- Cursor to count and sum the amount of the WIP contracts
788 -- for each subclass
789 CURSOR l_wip_csr
790 ( p_start_date IN DATE
791 , p_end_date IN DATE
792 , p_scs_code IN VARCHAR2
793 ) IS
794 SELECT COUNT(*) contract_count
795 , NVL(SUM(shd.base_contract_amount), 0) base_contract_amount
796 FROM oki_sales_k_hdrs shd
797 WHERE shd.ste_code = 'ENTERED'
798 AND NVL(shd.close_date, shd.start_date) BETWEEN p_start_date AND p_end_date
799 AND shd.is_new_yn IS NULL
800 AND shd.scs_code = p_scs_code
801 ;
802 rec_l_wip_csr l_wip_csr%ROWTYPE ;
803
804 -- Cursor to count and sum the amount of the signed contracts
805 -- for each subclass
806 CURSOR l_signed_csr
807 ( p_start_date IN DATE
808 , p_end_date IN DATE
809 , p_scs_code IN VARCHAR2
810 ) IS
811 SELECT COUNT(*) contract_count
812 , NVL(SUM(shd.base_contract_amount), 0) base_contract_amount
813 FROM oki_sales_k_hdrs shd
814 WHERE shd.ste_code IN ('ACTIVE','SIGNED')
815 AND LEAST(NVL(shd.date_signed, shd.start_date), shd.start_date)
816 BETWEEN p_start_date AND p_end_date
817 AND shd.is_new_yn IS NULL
818 AND shd.scs_code = p_scs_code
819 ;
820 rec_l_signed_csr l_signed_csr%ROWTYPE ;
821
822 -- Cursor to count and sum the amount of the forecasted contracts
823 -- for each subclass
824 CURSOR l_forecast_csr
825 ( p_start_date IN DATE
826 , p_end_date IN DATE
827 , p_scs_code IN VARCHAR2
828 ) IS
829 SELECT COUNT(*) contract_count
830 , NVL(SUM(base_forecast_amount), 0) base_contract_amount
831 FROM oki_sales_k_hdrs shd
832 WHERE shd.close_date BETWEEN p_start_date AND p_end_date
833 AND shd.win_percent IS NOT NULL
834 AND shd.close_date IS NOT NULL
835 AND shd.is_new_yn IS NULL
836 AND shd.scs_code = p_scs_code
837 ;
838 rec_l_forecast_csr l_forecast_csr%ROWTYPE ;
839
840 begin
841
842 -- initialize return code to success
843 l_retcode := '0' ;
844
845 l_loc := 'Looping through valid organizations.' ;
846 << l_scs_csr_loop >>
847 -- Loop through all the organizations to calcuate the
848 -- appropriate amounts
849 FOR rec_l_scs_csr IN l_scs_csr LOOP
850
851 l_loc := 'Looping through valid periods.' ;
852 << rec_g_glpr_csr_loop >>
853 -- Loop through all the periods
854 FOR rec_g_glpr_csr IN oki_utl_pvt.g_glpr_csr(
855 p_period_set_name, p_period_type, p_summary_build_date ) LOOP
856
857 -- Get the truncated gl_periods start and end dates
858 l_glpr_start_date := trunc(rec_g_glpr_csr.start_date );
859 l_glpr_end_date := trunc(rec_g_glpr_csr.end_date );
860
861 -- Re-initialize the counts and amounts before calculating
862 l_base_contract_amount := 0 ;
863 l_contract_count := 0 ;
864
865 l_loc := 'Opening cursor to determine the expired count.' ;
866 -- Calculate expired amounts and counts
867 -- Fetch count of expired contracts
868
869 OPEN l_expired_cnt_csr ( l_glpr_start_date, l_glpr_end_date,
870 rec_l_scs_csr.scs_code ) ;
871 FETCH l_expired_cnt_csr INTO rec_l_expired_cnt_csr ;
872 IF l_expired_cnt_csr%FOUND THEN
873 l_contract_count := rec_l_expired_cnt_csr.contract_count ;
874 END IF ;
875 CLOSE l_expired_cnt_csr ;
876
877 l_loc := 'Opening cursor to determine the expired sum.' ;
878 -- Fetch the sum of the amount of the expired lines
879 OPEN l_expired_amt_csr( l_glpr_start_date, l_glpr_end_date,
880 rec_l_scs_csr.scs_code ) ;
881 FETCH l_expired_amt_csr INTO rec_l_expired_amt_csr ;
882 IF l_expired_amt_csr%FOUND THEN
883 l_base_contract_amount := rec_l_expired_amt_csr.base_price_negotiated ;
884 END IF ;
885 CLOSE l_expired_amt_csr ;
886
887 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
888 -- Determine if the record is a new one or an existing one
889 OPEN oki_load_rbs_pvt.g_rbs_csr( rec_g_glpr_csr.period_set_name,
890 rec_g_glpr_csr.period_name, oki_load_rbs_pvt.g_all_org_id,
891 'EXPIRED', rec_l_scs_csr.scs_code ) ;
892 FETCH oki_load_rbs_pvt.g_rbs_csr INTO l_rbs_rowid ;
893 IF oki_load_rbs_pvt.g_rbs_csr%NOTFOUND THEN
894 l_loc := 'Insert the new record.' ;
895 -- Insert the current period data for the period
896 oki_load_rbs_pvt.ins_rnwl_by_stat(
897 p_period_name => rec_g_glpr_csr.period_name
898 , p_period_set_name => rec_g_glpr_csr.period_set_name
899 , p_period_type => rec_g_glpr_csr.period_type
900 , p_authoring_org_id => oki_load_rbs_pvt.g_all_org_id
901 , p_authoring_org_name => oki_load_rbs_pvt.g_all_org_name
902 , p_status_code => 'EXPIRED'
903 , p_scs_code => rec_l_scs_csr.scs_code
904 , p_base_amount => l_base_contract_amount
905 , p_contract_count => l_contract_count
906 , x_retcode => l_retcode ) ;
907
908 IF l_retcode = '2' THEN
909 -- Load failed, exit immediately.
910 RAISE oki_load_rbs_pvt.g_excp_exit_immediate ;
911 END IF ;
912
913 ELSE
914 l_loc := 'Update the existing record.' ;
915 -- Record already exists, so perform an update
916 oki_load_rbs_pvt.upd_rnwl_by_stat(
917 p_base_amount => l_base_contract_amount
918 , p_contract_count => l_contract_count
919 , p_rowid => l_rbs_rowid
920 , x_retcode => l_retcode ) ;
921
922 IF l_retcode = '2' THEN
923 -- Load failed, exit immediately.
924 RAISE oki_load_rbs_pvt.g_excp_exit_immediate ;
925 END IF ;
926
927 END IF ;
928 CLOSE oki_load_rbs_pvt.g_rbs_csr;
929
930
931 -- Re-initialize the counts and amounts before calculating
932 l_base_contract_amount := 0 ;
933 l_contract_count := 0 ;
934
935 l_loc := 'Opening cursor to determine the WIP count and sum.' ;
936 -- Calculate WIP amounts and counts
937 OPEN l_wip_csr( l_glpr_start_date, l_glpr_end_date,
938 rec_l_scs_csr.scs_code ) ;
939 FETCH l_wip_csr INTO rec_l_wip_csr ;
940 IF l_wip_csr%FOUND THEN
941 l_contract_count := rec_l_wip_csr.contract_count ;
942 l_base_contract_amount := rec_l_wip_csr.base_contract_amount ;
943 END IF ;
944 CLOSE l_wip_csr;
945
946 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
947 -- Determine if the record is a new one or an existing one
948 OPEN oki_load_rbs_pvt.g_rbs_csr( rec_g_glpr_csr.period_set_name,
949 rec_g_glpr_csr.period_name, oki_load_rbs_pvt.g_all_org_id,
950 'WIP', rec_l_scs_csr.scs_code ) ;
951 FETCH oki_load_rbs_pvt.g_rbs_csr INTO l_rbs_rowid ;
952 IF oki_load_rbs_pvt.g_rbs_csr%NOTFOUND THEN
953 l_loc := 'Insert the new record.' ;
954 -- Insert the current period data for the period
955 oki_load_rbs_pvt.ins_rnwl_by_stat(
956 p_period_set_name => rec_g_glpr_csr.period_set_name
957 , p_period_name => rec_g_glpr_csr.period_name
958 , p_period_type => rec_g_glpr_csr.period_type
959 , p_authoring_org_id => oki_load_rbs_pvt.g_all_org_id
960 , p_authoring_org_name => oki_load_rbs_pvt.g_all_org_name
961 , p_status_code => 'WIP'
962 , p_scs_code => rec_l_scs_csr.scs_code
963 , p_base_amount => l_base_contract_amount
964 , p_contract_count => l_contract_count
965 , x_retcode => l_retcode ) ;
966
967 IF l_retcode = '2' THEN
968 -- Load failed, exit immediately.
969 RAISE oki_load_rbs_pvt.g_excp_exit_immediate ;
970 END IF ;
971
972 ELSE
973 l_loc := 'Update the existing record.' ;
974 -- Record already exists, so perform an update
975 oki_load_rbs_pvt.upd_rnwl_by_stat(
976 p_base_amount => l_base_contract_amount
977 , p_contract_count => l_contract_count
978 , p_rowid => l_rbs_rowid
979 , x_retcode => l_retcode ) ;
980 IF l_retcode = '2' THEN
981 -- Load failed, exit immediately.
982 RAISE oki_load_rbs_pvt.g_excp_exit_immediate ;
983 END IF ;
984
985 END IF;
986 CLOSE oki_load_rbs_pvt.g_rbs_csr;
987
988
989 -- Re-initialize the counts and amounts before calculating
990 l_base_contract_amount := 0 ;
991 l_contract_count := 0 ;
992
993 l_loc := 'Opening cursor to determine the signed count and sum.' ;
994 -- Calculate signed amounts and counts
995 OPEN l_signed_csr( l_glpr_start_date, l_glpr_end_date,
996 rec_l_scs_csr.scs_code ) ;
997 FETCH l_signed_csr INTO rec_l_signed_csr ;
998 IF l_signed_csr%FOUND THEN
999 l_contract_count := rec_l_signed_csr.contract_count ;
1000 l_base_contract_amount := rec_l_signed_csr.base_contract_amount ;
1001 END IF ;
1002 CLOSE l_signed_csr ;
1003
1004 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
1005 -- Determine if the record is a new one or an existing one
1006 OPEN oki_load_rbs_pvt.g_rbs_csr( rec_g_glpr_csr.period_set_name,
1007 rec_g_glpr_csr.period_name, oki_load_rbs_pvt.g_all_org_id,
1008 'SIGNED', rec_l_scs_csr.scs_code ) ;
1009 FETCH oki_load_rbs_pvt.g_rbs_csr INTO l_rbs_rowid ;
1010 IF oki_load_rbs_pvt.g_rbs_csr%NOTFOUND THEN
1011 l_loc := 'Insert the new record.' ;
1012 -- Insert the current period data for the period
1013 oki_load_rbs_pvt.ins_rnwl_by_stat(
1014 p_period_set_name => rec_g_glpr_csr.period_set_name
1015 , p_period_name => rec_g_glpr_csr.period_name
1016 , p_period_type => rec_g_glpr_csr.period_type
1017 , p_authoring_org_id => oki_load_rbs_pvt.g_all_org_id
1018 , p_authoring_org_name => oki_load_rbs_pvt.g_all_org_name
1019 , p_status_code => 'SIGNED'
1020 , p_scs_code => rec_l_scs_csr.scs_code
1021 , p_base_amount => l_base_contract_amount
1022 , p_contract_count => l_contract_count
1023 , x_retcode => l_retcode ) ;
1024
1025 IF l_retcode = '2' THEN
1026 -- Load failed, exit immediately.
1027 RAISE oki_load_rbs_pvt.g_excp_exit_immediate ;
1028 END IF ;
1029
1030 ELSE
1031 l_loc := 'Update the existing record.' ;
1032 -- Record already exists, so perform an update
1033 oki_load_rbs_pvt.upd_rnwl_by_stat(
1034 p_base_amount => l_base_contract_amount
1035 , p_contract_count => l_contract_count
1036 , p_rowid => l_rbs_rowid
1037 , x_retcode => l_retcode ) ;
1038
1039 IF l_retcode = '2' THEN
1040 -- Load failed, exit immediately.
1041 RAISE oki_load_rbs_pvt.g_excp_exit_immediate ;
1042 END IF ;
1043
1044 END IF;
1045 CLOSE oki_load_rbs_pvt.g_rbs_csr ;
1046
1047 -- Re-initialize the counts and amounts before calculating
1048 l_base_contract_amount := 0 ;
1049 l_contract_count := 0 ;
1050
1051 l_loc := 'Opening cursor to determine the forecast count and sum.' ;
1052 -- Calculate forecast amounts and counts
1053 OPEN l_forecast_csr( l_glpr_start_date, l_glpr_end_date,
1054 rec_l_scs_csr.scs_code ) ;
1055 FETCH l_forecast_csr into rec_l_forecast_csr ;
1056 IF l_forecast_csr%FOUND THEN
1057 l_contract_count := rec_l_forecast_csr.contract_count ;
1058 l_base_contract_amount := rec_l_forecast_csr.base_contract_amount ;
1059 END IF ;
1060 CLOSE l_forecast_csr ;
1061
1062 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
1063 -- Determine if the record is a new one or an existing one
1064 OPEN oki_load_rbs_pvt.g_rbs_csr( rec_g_glpr_csr.period_set_name,
1065 rec_g_glpr_csr.period_name, oki_load_rbs_pvt.g_all_org_id,
1066 'FORECAST', rec_l_scs_csr.scs_code ) ;
1067 FETCH oki_load_rbs_pvt.g_rbs_csr INTO l_rbs_rowid ;
1068 IF oki_load_rbs_pvt.g_rbs_csr%NOTFOUND THEN
1069 -- Insert the current period data for the period
1070 l_loc := 'Insert the new record.' ;
1071 oki_load_rbs_pvt.ins_rnwl_by_stat(
1072 p_period_name => rec_g_glpr_csr.period_name
1073 , p_period_set_name => rec_g_glpr_csr.period_set_name
1074 , p_period_type => rec_g_glpr_csr.period_type
1075 , p_authoring_org_id => oki_load_rbs_pvt.g_all_org_id
1076 , p_authoring_org_name => oki_load_rbs_pvt.g_all_org_name
1077 , p_status_code => 'FORECAST'
1078 , p_scs_code => rec_l_scs_csr.scs_code
1079 , p_base_amount => l_base_contract_amount
1080 , p_contract_count => l_contract_count
1081 , x_retcode => l_retcode ) ;
1082
1083 IF l_retcode = '2' THEN
1084 -- Load failed, exit immediately.
1085 RAISE oki_load_rbs_pvt.g_excp_exit_immediate ;
1086 END IF ;
1087
1088 ELSE
1089 l_loc := 'Update the existing record.' ;
1090 -- Record already exists, so perform an update
1091 oki_load_rbs_pvt.upd_rnwl_by_stat(
1092 p_base_amount => l_base_contract_amount
1093 , p_contract_count => l_contract_count
1094 , p_rowid => l_rbs_rowid
1095 , x_retcode => l_retcode ) ;
1096
1097 IF l_retcode = '2' THEN
1098 -- Load failed, exit immediately.
1099 RAISE oki_load_rbs_pvt.g_excp_exit_immediate ;
1100 END IF ;
1101
1102 END IF ;
1103 CLOSE oki_load_rbs_pvt.g_rbs_csr ;
1104 END LOOP rec_g_glpr_csr_loop ;
1105 END LOOP l_scs_csr_loop ;
1106
1107 EXCEPTION
1108 WHEN oki_load_rbs_pvt.g_excp_exit_immediate THEN
1109 -- Do not log an error ; It has already been logged.
1110 -- Set return code to error
1111 x_retcode := '2' ;
1112
1113 WHEN OTHERS THEN
1114 l_sqlcode := SQLCODE ;
1115 l_sqlerrm := SQLERRM ;
1116
1117 -- Set return code to error
1118 x_retcode := '2';
1119
1120 fnd_message.set_name( application => 'OKI'
1121 , name => 'OKI_UNEXPECTED_FAILURE');
1122
1123 fnd_message.set_token( token => 'OBJECT_NAME'
1124 , value => 'OKI_LOAD_RBS_PVT.OKI_CALC_RBS_DTL1');
1125
1126 fnd_file.put_line( which => fnd_file.log
1127 , buff => fnd_message.get);
1128
1129 -- Log the location within the procedure where the error occurred
1130 fnd_message.set_name( application => 'OKI'
1131 , name => 'OKI_LOC_IN_PROG_FAILURE');
1132
1133 fnd_message.set_token( token => 'LOCATION'
1134 , value => l_loc);
1135
1136 fnd_file.put_line( which => fnd_file.log
1137 , buff => fnd_message.get);
1138
1139
1140 fnd_file.put_line( which => fnd_file.log
1141 , buff => l_sqlcode||' '||l_sqlerrm );
1142
1143 end calc_rbs_dtl2 ;
1144
1145 --------------------------------------------------------------------------------
1146 -- Procedure to calculate the counts and amounts of expired, WIP,
1147 -- signed, and forecasted contracts.
1148 -- Calculates the counts and amounts across organizations and subclasses
1149 -- each period set name
1150 -- each period type
1151 -- each period name
1152 -- each status
1153 -- all subclasses
1154 -- all organizations
1155 --
1156 --------------------------------------------------------------------------------
1157 PROCEDURE calc_rbs_sum
1158 ( p_period_set_name IN VARCHAR2
1159 , p_period_type IN VARCHAR2
1160 , p_summary_build_date IN DATE
1161 , x_retcode OUT NOCOPY VARCHAR2
1162 ) IS
1163
1164
1165 -- Local variable declaration
1166
1167 -- For capturing the return code, 0 = success, 1 = warning, 2 = error
1168 l_retcode VARCHAR2(1) := NULL ;
1169
1170 -- For error handling
1171 l_sqlcode VARCHAR2(100) := NULL ;
1172 l_sqlerrm VARCHAR2(1000) := NULL ;
1173
1174 -- Holds the contract amount and counts
1175 l_contract_count NUMBER ;
1176 l_base_contract_amount NUMBER ;
1177
1178 -- holds the rowid of the record in the oki_renew_by_statuses table
1179 l_rbs_rowid ROWID := null ;
1180
1181 -- Location within the program before the error was encountered.
1182 l_loc VARCHAR2(100) ;
1183
1184 -- Holds the truncated start and end dates from gl_periods
1185 l_glpr_start_date DATE ;
1186 l_glpr_end_date DATE ;
1187
1188 -- Cusror declaration
1189
1190 -- Cursor to sum the amount of the expired lines
1191 CURSOR l_expired_amt_csr
1192 ( p_start_date IN DATE
1193 , p_end_date IN DATE
1194 ) IS
1195 SELECT NVL(SUM(cpl.base_price_negotiated), 0) base_price_negotiated
1196 /*11510 change removed oki_Expired_lines and added oki_cov_prd_lines */
1197 FROM oki_cov_prd_lines cpl
1198 WHERE cpl.end_date between p_start_date AND p_end_date
1199 /*11510 change*/
1200 AND cpl.is_exp_not_renewed_yn='Y' ;
1201 rec_l_expired_amt_csr l_expired_amt_csr%ROWTYPE ;
1202
1203 -- Cursor to count the number of contracts with expired lines
1204 CURSOR l_expired_cnt_csr
1205 ( p_start_date IN DATE
1206 , p_end_date IN DATE) IS
1207 SELECT COUNT(DISTINCT(shd.chr_id)) contract_count
1208 /*11510 change removed oki_Expired_lines and added oki_cov_prd_lines */
1209 FROM oki_cov_prd_lines cpl
1210 , oki_sales_k_hdrs shd
1211 WHERE cpl.end_date BETWEEN p_start_date and p_end_date
1212 /*11510 change*/
1213 AND cpl.is_exp_not_renewed_yn='Y'
1214 AND cpl.chr_id = shd.chr_id ;
1215
1216 rec_l_expired_cnt_csr l_expired_cnt_csr%ROWTYPE ;
1217
1218 -- Cursor to count and sum the amount of the WIP contracts
1219 CURSOR l_wip_csr
1220 ( p_start_date IN DATE
1221 , p_end_date IN DATE
1222 ) IS
1223 SELECT COUNT(*) contract_count
1224 , NVL(SUM(shd.base_contract_amount), 0) base_contract_amount
1225 FROM oki_sales_k_hdrs shd
1226 WHERE shd.ste_code = 'ENTERED'
1227 AND NVL(shd.close_date, shd.start_date)
1228 BETWEEN p_start_date AND p_end_date
1229 AND shd.is_new_yn IS NULL
1230 ;
1231 rec_l_wip_csr l_wip_csr%ROWTYPE ;
1232
1233 -- Cursor to count and sum the amount of the signed contracts
1234 CURSOR l_signed_csr
1235 ( p_start_date IN DATE
1236 , p_end_date IN DATE
1237 ) IS
1238 SELECT count(*) contract_count
1239 , NVL(SUM(shd.base_contract_amount), 0) base_contract_amount
1240 FROM oki_sales_k_hdrs shd
1241 WHERE shd.ste_code IN ('ACTIVE','SIGNED')
1242 AND LEAST(NVL(shd.date_signed, shd.start_date), shd.start_date)
1243 BETWEEN p_start_date AND p_end_date
1244 AND shd.is_new_yn IS NULL
1245 ;
1246 rec_l_signed_csr l_signed_csr%ROWTYPE ;
1247
1248 -- Cursor to count and sum the amount of the forecasted contracts
1249 CURSOR l_forecast_csr
1250 ( p_start_date IN DATE
1251 , p_end_date IN DATE) IS
1252 SELECT count(*) contract_count
1253 , NVL(SUM(base_forecast_amount), 0) base_contract_amount
1254 FROM oki_sales_k_hdrs shd
1255 WHERE shd.close_date BETWEEN p_start_date AND p_end_date
1256 AND shd.win_percent IS NOT NULL
1257 AND shd.close_date IS NOT NULL
1258 AND shd.is_new_yn IS NULL
1259 ;
1260 rec_l_forecast_csr l_forecast_csr%ROWTYPE ;
1261
1262 begin
1263
1264 -- initialize return code to success
1265 l_retcode := '0' ;
1266
1267 l_loc := 'Looping through valid periods.' ;
1268 -- Loop through all the periods
1269 FOR rec_g_glpr_csr IN oki_utl_pvt.g_glpr_csr(
1270 p_period_set_name, p_period_type, p_summary_build_date ) LOOP
1271
1272 -- Get the truncated gl_periods start and end dates
1273 l_glpr_start_date := trunc(rec_g_glpr_csr.start_date );
1274 l_glpr_end_date := trunc(rec_g_glpr_csr.end_date );
1275
1276 -- Re-initialize the counts and amounts before calculating
1277 l_base_contract_amount := 0 ;
1278 l_contract_count := 0 ;
1279
1280 l_loc := 'Opening cursor to determine the expired count.' ;
1281 -- Calculate expired amounts and counts
1282 -- Fetch count of expired contracts
1283 OPEN l_expired_cnt_csr ( l_glpr_start_date, l_glpr_end_date ) ;
1284 FETCH l_expired_cnt_csr INTO rec_l_expired_cnt_csr ;
1285 IF l_expired_cnt_csr%FOUND THEN
1286 l_contract_count := rec_l_expired_cnt_csr.contract_count ;
1287 END IF ;
1288 CLOSE l_expired_cnt_csr ;
1289
1290 l_loc := 'Opening cursor to determine the expired sum.' ;
1291 -- Fetch the sum of the amount of the expired lines
1292 OPEN l_expired_amt_csr( l_glpr_start_date, l_glpr_end_date ) ;
1293 FETCH l_expired_amt_csr INTO rec_l_expired_amt_csr ;
1294 IF l_expired_amt_csr%FOUND THEN
1295 l_base_contract_amount := rec_l_expired_amt_csr.base_price_negotiated ;
1296 END IF ;
1297 CLOSE l_expired_amt_csr ;
1298
1299 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
1300 -- Determine if the record is a new one or an existing one
1301 OPEN oki_load_rbs_pvt.g_rbs_csr( rec_g_glpr_csr.period_set_name,
1302 rec_g_glpr_csr.period_name, oki_load_rbs_pvt.g_all_org_id,
1303 'EXPIRED', oki_load_rbs_pvt.g_all_scs_code) ;
1304 FETCH oki_load_rbs_pvt.g_rbs_csr INTO l_rbs_rowid ;
1305 IF oki_load_rbs_pvt.g_rbs_csr%NOTFOUND THEN
1306 l_loc := 'Insert the new record.' ;
1307 -- Insert the current period data for the period
1308 oki_load_rbs_pvt.ins_rnwl_by_stat(
1309 p_period_name => rec_g_glpr_csr.period_name
1310 , p_period_set_name => rec_g_glpr_csr.period_set_name
1311 , p_period_type => rec_g_glpr_csr.period_type
1312 , p_authoring_org_id => oki_load_rbs_pvt.g_all_org_id
1313 , p_authoring_org_name => oki_load_rbs_pvt.g_all_org_name
1314 , p_status_code => 'EXPIRED'
1315 , p_scs_code => oki_load_rbs_pvt.g_all_scs_code
1316 , p_base_amount => l_base_contract_amount
1317 , p_contract_count => l_contract_count
1318 , x_retcode => l_retcode ) ;
1319
1320 IF l_retcode = '2' THEN
1321 -- Load failed, exit immediately.
1322 RAISE oki_load_rbs_pvt.g_excp_exit_immediate ;
1323 END IF ;
1324
1325 ELSE
1326 l_loc := 'Update the existing record.' ;
1327 -- Record already exists, so perform an update
1328 oki_load_rbs_pvt.upd_rnwl_by_stat(
1329 p_base_amount => l_base_contract_amount
1330 , p_contract_count => l_contract_count
1331 , p_rowid => l_rbs_rowid
1332 , x_retcode => l_retcode ) ;
1333
1334 IF l_retcode = '2' THEN
1335 -- Load failed, exit immediately.
1336 RAISE oki_load_rbs_pvt.g_excp_exit_immediate ;
1337 END IF ;
1338
1339 END IF ;
1340 CLOSE oki_load_rbs_pvt.g_rbs_csr;
1341
1342 -- Re-initialize the counts and amounts before calculating
1343 l_base_contract_amount := 0 ;
1344 l_contract_count := 0 ;
1345
1346 l_loc := 'Opening cursor to determine the WIP count and sum.' ;
1347 -- Calculate WIP amounts and counts
1348 OPEN l_wip_csr( l_glpr_start_date, l_glpr_end_date ) ;
1349 FETCH l_wip_csr INTO rec_l_wip_csr ;
1350 IF l_wip_csr%FOUND THEN
1351 l_contract_count := rec_l_wip_csr.contract_count ;
1352 l_base_contract_amount := rec_l_wip_csr.base_contract_amount ;
1353 END IF ;
1354 CLOSE l_wip_csr;
1355
1356 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
1357 -- Determine if the record is a new one or an existing one
1358 OPEN oki_load_rbs_pvt.g_rbs_csr( rec_g_glpr_csr.period_set_name,
1359 rec_g_glpr_csr.period_name, oki_load_rbs_pvt.g_all_org_id,
1360 'WIP', oki_load_rbs_pvt.g_all_scs_code ) ;
1361 FETCH oki_load_rbs_pvt.g_rbs_csr INTO l_rbs_rowid;
1362 IF oki_load_rbs_pvt.g_rbs_csr%NOTFOUND THEN
1363 l_loc := 'Insert the new record.' ;
1364 -- Insert the current period data for the period
1365 oki_load_rbs_pvt.ins_rnwl_by_stat(
1366 p_period_set_name => rec_g_glpr_csr.period_set_name
1367 , p_period_name => rec_g_glpr_csr.period_name
1368 , p_period_type => rec_g_glpr_csr.period_type
1369 , p_authoring_org_id => oki_load_rbs_pvt.g_all_org_id
1370 , p_authoring_org_name => oki_load_rbs_pvt.g_all_org_name
1371 , p_status_code => 'WIP'
1372 , p_scs_code => oki_load_rbs_pvt.g_all_scs_code
1373 , p_base_amount => l_base_contract_amount
1374 , p_contract_count => l_contract_count
1375 , x_retcode => l_retcode ) ;
1376
1377 IF l_retcode = '2' THEN
1378 -- Load failed, exit immediately.
1379 RAISE oki_load_rbs_pvt.g_excp_exit_immediate ;
1380 END IF ;
1381
1382 ELSE
1383 l_loc := 'Update the existing record.' ;
1384 -- Record already exists, so perform an update
1385 oki_load_rbs_pvt.upd_rnwl_by_stat(
1386 p_base_amount => l_base_contract_amount
1387 , p_contract_count => l_contract_count
1388 , p_rowid => l_rbs_rowid
1389 , x_retcode => l_retcode ) ;
1390 IF l_retcode = '2' THEN
1391 -- Load failed, exit immediately.
1392 RAISE oki_load_rbs_pvt.g_excp_exit_immediate ;
1393 END IF ;
1394
1395 END IF;
1396 CLOSE oki_load_rbs_pvt.g_rbs_csr;
1397
1398 -- Re-initialize the counts and amounts before calculating
1399 l_base_contract_amount := 0 ;
1400 l_contract_count := 0 ;
1401
1402 l_loc := 'Opening cursor to determine the signed count and sum.' ;
1403 -- Calculate signed amounts and counts
1404 OPEN l_signed_csr( l_glpr_start_date, l_glpr_end_date ) ;
1405 FETCH l_signed_csr INTO rec_l_signed_csr ;
1406 IF l_signed_csr%FOUND THEN
1407 l_contract_count := rec_l_signed_csr.contract_count ;
1408 l_base_contract_amount := rec_l_signed_csr.base_contract_amount ;
1409 END IF ;
1410 CLOSE l_signed_csr ;
1411
1412 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
1413 -- Determine if the record is a new one or an existing one
1414 OPEN oki_load_rbs_pvt.g_rbs_csr( rec_g_glpr_csr.period_set_name,
1415 rec_g_glpr_csr.period_name, oki_load_rbs_pvt.g_all_org_id,
1416 'SIGNED', oki_load_rbs_pvt.g_all_scs_code ) ;
1417 FETCH oki_load_rbs_pvt.g_rbs_csr INTO l_rbs_rowid ;
1418 IF oki_load_rbs_pvt.g_rbs_csr%NOTFOUND THEN
1419 l_loc := 'Insert the new record.' ;
1420 -- Insert the current period data for the period
1421 oki_load_rbs_pvt.ins_rnwl_by_stat(
1422 p_period_set_name => rec_g_glpr_csr.period_set_name
1423 , p_period_name => rec_g_glpr_csr.period_name
1424 , p_period_type => rec_g_glpr_csr.period_type
1425 , p_authoring_org_id => oki_load_rbs_pvt.g_all_org_id
1426 , p_authoring_org_name => oki_load_rbs_pvt.g_all_org_name
1427 , p_status_code => 'SIGNED'
1428 , p_scs_code => oki_load_rbs_pvt.g_all_scs_code
1429 , p_base_amount => l_base_contract_amount
1430 , p_contract_count => l_contract_count
1431 , x_retcode => l_retcode ) ;
1432
1433 IF l_retcode = '2' THEN
1434 -- Load failed, exit immediately.
1435 RAISE oki_load_rbs_pvt.g_excp_exit_immediate ;
1436 END IF ;
1437
1438 ELSE
1439 l_loc := 'Update the existing record.' ;
1440 -- Record already exists, so perform an update
1441 oki_load_rbs_pvt.upd_rnwl_by_stat(
1442 p_base_amount => l_base_contract_amount
1443 , p_contract_count => l_contract_count
1444 , p_rowid => l_rbs_rowid
1445 , x_retcode => l_retcode ) ;
1446
1447 IF l_retcode = '2' THEN
1448 -- Load failed, exit immediately.
1449 RAISE oki_load_rbs_pvt.g_excp_exit_immediate ;
1450 END IF ;
1451
1452 END IF;
1453 CLOSE oki_load_rbs_pvt.g_rbs_csr;
1454
1455 -- Re-initialize the counts and amounts before calculating
1456 l_base_contract_amount := 0 ;
1457 l_contract_count := 0 ;
1458
1459 l_loc := 'Opening cursor to determine the forecast count and sum.' ;
1460 -- Calculate forecast amounts and counts
1461 OPEN l_forecast_csr( l_glpr_start_date, l_glpr_end_date ) ;
1462 FETCH l_forecast_csr into rec_l_forecast_csr ;
1463 IF l_forecast_csr%FOUND THEN
1464 l_contract_count := rec_l_forecast_csr.contract_count ;
1465 l_base_contract_amount := rec_l_forecast_csr.base_contract_amount ;
1466 END IF ;
1467 CLOSE l_forecast_csr ;
1468
1469 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
1470 -- Determine if the record is a new one or an existing one
1471 OPEN oki_load_rbs_pvt.g_rbs_csr( rec_g_glpr_csr.period_set_name,
1472 rec_g_glpr_csr.period_name, oki_load_rbs_pvt.g_all_org_id,
1473 'FORECAST', oki_load_rbs_pvt.g_all_scs_code ) ;
1474 FETCH oki_load_rbs_pvt.g_rbs_csr INTO l_rbs_rowid ;
1475 IF oki_load_rbs_pvt.g_rbs_csr%NOTFOUND THEN
1476 -- Insert the current period data for the period
1477 l_loc := 'Insert the new record.' ;
1478 oki_load_rbs_pvt.ins_rnwl_by_stat(
1479 p_period_name => rec_g_glpr_csr.period_name
1480 , p_period_set_name => rec_g_glpr_csr.period_set_name
1481 , p_period_type => rec_g_glpr_csr.period_type
1482 , p_authoring_org_id => oki_load_rbs_pvt.g_all_org_id
1483 , p_authoring_org_name => oki_load_rbs_pvt.g_all_org_name
1484 , p_status_code => 'FORECAST'
1485 , p_scs_code => oki_load_rbs_pvt.g_all_scs_code
1486 , p_base_amount => l_base_contract_amount
1487 , p_contract_count => l_contract_count
1488 , x_retcode => l_retcode ) ;
1489
1490 IF l_retcode = '2' THEN
1491 -- Load failed, exit immediately.
1492 RAISE oki_load_rbs_pvt.g_excp_exit_immediate ;
1493 END IF ;
1494
1495 ELSE
1496 l_loc := 'Update the existing record.' ;
1497 -- Record already exists, so perform an update
1498 oki_load_rbs_pvt.upd_rnwl_by_stat(
1499 p_base_amount => l_base_contract_amount
1500 , p_contract_count => l_contract_count
1501 , p_rowid => l_rbs_rowid
1502 , x_retcode => l_retcode ) ;
1503
1504 IF l_retcode = '2' THEN
1505 -- Load failed, exit immediately.
1506 RAISE oki_load_rbs_pvt.g_excp_exit_immediate ;
1507 END IF ;
1508
1509 END IF ;
1510 CLOSE oki_load_rbs_pvt.g_rbs_csr ;
1511 END LOOP ;
1512
1513 EXCEPTION
1514 WHEN oki_load_rbs_pvt.g_excp_exit_immediate THEN
1515 -- Do not log an error ; It has already been logged.
1516 -- Set return code to error
1517 x_retcode := '2' ;
1518
1519 WHEN OTHERS THEN
1520 l_sqlcode := SQLCODE ;
1521 l_sqlerrm := SQLERRM ;
1522
1523 -- Set return code to error
1524 x_retcode := '2';
1525
1526 fnd_message.set_name( application => 'OKI'
1527 , name => 'OKI_UNEXPECTED_FAILURE');
1528
1529 fnd_message.set_token( token => 'OBJECT_NAME'
1530 , value => 'OKI_LOAD_RBS_PVT.OKI_CALC_RBS_SUM');
1531
1532 fnd_file.put_line( which => fnd_file.log
1533 , buff => fnd_message.get);
1534
1535 -- Log the location within the procedure where the error occurred
1536 fnd_message.set_name( application => 'OKI'
1537 , name => 'OKI_LOC_IN_PROG_FAILURE');
1538
1539 fnd_message.set_token( token => 'LOCATION'
1540 , value => l_loc);
1541
1542 fnd_file.put_line( which => fnd_file.log
1543 , buff => fnd_message.get);
1544
1545
1546 fnd_file.put_line( which => fnd_file.log
1547 , buff => l_sqlcode||' '||l_sqlerrm );
1548
1549 end calc_rbs_sum ;
1550
1551 --------------------------------------------------------------------------------
1552 -- Procedure to create all the renewal by statuses records.
1553 -- If an error is encountered in this procedure or subsequent procedures then
1554 -- rollback all changes. Once the table is loaded and the data is committed
1555 -- the load is considered successful even if update of the oki_refreshs
1556 -- table failed.
1557 --------------------------------------------------------------------------------
1558 PROCEDURE crt_rnwl_by_stat
1559 ( p_period_set_name IN VARCHAR2
1560 , p_period_type IN VARCHAR2
1561 , p_summary_build_date IN DATE
1562 , x_errbuf OUT NOCOPY VARCHAR2
1563 , x_retcode OUT NOCOPY VARCHAR2
1564 ) IS
1565
1566 -- Local exception declaration
1567
1568 -- Exception to immediately exit the procedure
1569 l_excp_upd_refresh EXCEPTION ;
1570
1571
1572 -- Constant declaration
1573
1574 -- Name of the table for which data is being inserted
1575 l_table_name CONSTANT VARCHAR2(30) := 'OKI_RENEW_BY_STATUSES' ;
1576
1577
1578 -- Local variable declaration
1579
1580 -- For capturing the return code, 0 = success, 1 = warning, 2 = error
1581 l_retcode VARCHAR2(1) := NULL ;
1582
1583 -- For error handling
1584 l_sqlcode VARCHAR2(100) ;
1585 l_sqlerrm VARCHAR2(1000) ;
1586
1587
1588 BEGIN
1589
1590 SAVEPOINT oki_load_rbs_pvt_crt_rnwl_cst ;
1591
1592 -- initialize return code to success
1593 l_retcode := '0' ;
1594 x_retcode := '0' ;
1595
1596 -- Procedure to calculate the counts and amounts for each dimension
1597 oki_load_rbs_pvt.calc_rbs_dtl1(
1598 p_period_set_name => p_period_set_name
1599 , p_period_type => p_period_type
1600 , p_summary_build_date => p_summary_build_date
1601 , x_retcode => l_retcode ) ;
1602
1603 IF l_retcode = '2' THEN
1604 -- Load failed, exit immediately.
1605 RAISE oki_load_rbs_pvt.g_excp_exit_immediate ;
1606 END IF ;
1607
1608 -- Procedure to calculate the counts and amounts across organizations
1609 oki_load_rbs_pvt.calc_rbs_dtl2(
1610 p_period_set_name => p_period_set_name
1611 , p_period_type => p_period_type
1612 , p_summary_build_date => p_summary_build_date
1613 , x_retcode => l_retcode ) ;
1614
1615 IF l_retcode = '2' THEN
1616 -- Load failed, exit immediately.
1617 RAISE oki_load_rbs_pvt.g_excp_exit_immediate ;
1618 END IF ;
1619
1620 -- Procedure to calculate the counts and amounts across organizations
1621 -- and subclasses
1622 oki_load_rbs_pvt.calc_rbs_sum(
1623 p_period_set_name => p_period_set_name
1624 , p_period_type => p_period_type
1625 , p_summary_build_date => p_summary_build_date
1626 , x_retcode => l_retcode ) ;
1627
1628 IF l_retcode = '2' THEN
1629 -- Load failed, exit immediately.
1630 RAISE oki_load_rbs_pvt.g_excp_exit_immediate ;
1631 END IF ;
1632
1633 COMMIT ;
1634
1635 SAVEPOINT oki_load_rbs_pvt_upd_refresh ;
1636
1637 -- Table loaded successfully. Log message in concurrent manager
1638 -- log indicating successful load.
1639 fnd_message.set_name( application => 'OKI'
1640 , name => 'OKI_TABLE_LOAD_SUCCESS');
1641
1642 fnd_message.set_token( token => 'TABLE_NAME'
1643 , value => l_table_name );
1644
1645 fnd_file.put_line( which => fnd_file.log
1646 , buff => fnd_message.get);
1647
1648 oki_refresh_pvt.update_oki_refresh( l_table_name, l_retcode ) ;
1649
1650 IF l_retcode in ('1', '2') THEN
1651 -- Update to OKI_REFRESHS failed, exit immediately.
1652 RAISE l_excp_upd_refresh ;
1653 END IF ;
1654
1655
1656 COMMIT ;
1657
1658 EXCEPTION
1659 WHEN l_excp_upd_refresh THEN
1660 -- Do not log error; It has already been logged by the refreshs
1661 -- program
1662 x_retcode := l_retcode ;
1663
1664 ROLLBACK to oki_load_rbs_pvt_upd_refresh ;
1665
1666 WHEN oki_load_rbs_pvt.g_excp_exit_immediate THEN
1667 -- Do not log an error ; It has already been logged.
1668 -- Set return code to error
1669 x_retcode := '2' ;
1670
1671 ROLLBACK TO oki_load_rbs_pvt_crt_rnwl_cst ;
1672
1673 WHEN OTHERS THEN
1674
1675 l_sqlcode := sqlcode ;
1676 l_sqlerrm := sqlerrm ;
1677
1678 -- Set return code to error
1679 x_retcode := '2' ;
1680
1681 -- rollback all transactions
1682 ROLLBACK to oki_load_rbs_pvt_crt_rnwl_cst ;
1683
1684
1685 fnd_message.set_name( application => 'OKI'
1686 , name => 'OKI_UNEXPECTED_FAILURE');
1687
1688 fnd_message.set_token( token => 'OBJECT_NAME'
1689 , value => 'OKI_LOAD_RBS_PVT.CRT_RNWL_BY_STAT');
1690
1691 fnd_file.put_line( which => fnd_file.log
1692 , buff => fnd_message.get);
1693
1694 fnd_file.put_line( which => fnd_file.log
1695 , buff => l_sqlcode||' '||l_sqlerrm );
1696 end crt_rnwl_by_stat ;
1697
1698
1699 BEGIN
1700 -- Initialize the global variables used to log this job run
1701 -- from concurrent manager
1702 g_request_id := fnd_global.conc_request_id ;
1703 g_program_application_id := fnd_global.prog_appl_id ;
1704 g_program_id := fnd_global.conc_program_id ;
1705 g_program_update_date := SYSDATE ;
1706
1707 END oki_load_rbs_pvt ;
[Home] [Help]