[Home] [Help]
PACKAGE BODY: APPS.OKI_LOAD_RBK_PVT
Source
1 PACKAGE BODY oki_load_rbk_pvt AS
2 /* $Header: OKIRRBKB.pls 115.7 2002/12/01 17:52:54 rpotnuru noship $ */
3
4 --------------------------------------------------------------------------------
5 -- Modification History
6 -- 26-Dec-2001 mezra Initial version
7 -- 18-Jan-2001 mezra Corrected code passed when checking if quarter
8 -- activity record exists.
9 -- 20-Mar-2002 mezra Added logic to retrieve previous year and
10 -- change percent
11 -- 08-Apr-2002 mezra Added logic to load organization_name,
12 -- customer_name, measure_code_meaning, bin_code_seq.
13 -- 26-NOV-2002 rpotnuru NOCOPY Changes
14 --
15 --------------------------------------------------------------------------------
16
17 --
18 -- Global constant delcaration
19 --
20 g_b_act_k_code CONSTANT VARCHAR2(30) := 'BACTK' ;
21 g_exp_in_qtr_code CONSTANT VARCHAR2(30) := 'EXPINQTR' ;
22 g_bklg_k_to_rnw_code CONSTANT VARCHAR2(30) := 'BKLGKTORWN' ;
23 g_b_rnwl_oppty_code CONSTANT VARCHAR2(30) := 'BRNWLOPPTY' ;
24 g_qtr_actvty_code CONSTANT VARCHAR2(30) := 'QTRACTVTY' ;
25 g_bklg_k_rnwed_code CONSTANT VARCHAR2(30) := 'BKLGKRNW' ;
26 g_k_rnwed_code CONSTANT VARCHAR2(30) := 'KRNW' ;
27 g_new_bsn_code CONSTANT VARCHAR2(30) := 'NEWBUS' ;
28 g_trmn_k_code CONSTANT VARCHAR2(30) := 'TRMNK' ;
29 g_e_act_k_code CONSTANT VARCHAR2(30) := 'ENDACTK' ;
30
31 g_bin_id VARCHAR2(30) := 'OKI_RNWL_BKNG_RPT' ;
32
33 --
34 -- Global cursor declaration
35 --
36
37 -- Cursor to retrieve the rowid for the selected record
38 -- If the rowid exists, then the selected record will be
39 -- updated, else it is inserted into the table.
40 CURSOR g_rbk_csr
41 (
42 p_summary_build_date IN DATE
43 , p_authoring_org_id IN NUMBER
44 , p_customer_party_id IN NUMBER
45 , p_scs_code IN VARCHAR2
46 , p_measure_code IN VARCHAR2
47 ) IS
48 SELECT rowid
49 FROM oki_rnwl_bookings rbk
50 WHERE rbk.summary_build_date = p_summary_build_date
51 AND rbk.authoring_org_id = p_authoring_org_id
52 AND rbk.customer_party_id = p_customer_party_id
53 AND rbk.scs_code = p_scs_code
54 AND rbk.measure_code = p_measure_code
55 ;
56 rec_g_rbk_csr g_rbk_csr%ROWTYPE ;
57
58 --------------------------------------------------------------------------------
59 --
60 --Procedure to insert records into the oki_rnwl_bookings table.
61 --
62 --------------------------------------------------------------------------------
63 PROCEDURE ins_rnwl_bkng
64 (
65 p_summary_build_date IN DATE
66 , p_authoring_org_id IN NUMBER
67 , p_organization_name IN VARCHAR2
68 , p_customer_party_id IN NUMBER
69 , p_customer_name IN VARCHAR2
70 , p_scs_code IN VARCHAR2
71 , p_measure_code IN VARCHAR2
72 , p_measure_code_meaning IN VARCHAR2
73 , p_bin_code_seq IN NUMBER
74 , p_curr_base_contract_amount IN NUMBER
75 , p_prev_base_contract_amount 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 BEGIN
86
87 -- initialize return code to success
88 x_retcode := '0';
89
90 INSERT INTO oki_rnwl_bookings
91 (
92 summary_build_date
93 , authoring_org_id
94 , organization_name
95 , customer_party_id
96 , customer_name
97 , scs_code
98 , measure_code
99 , measure_code_meaning
100 , bin_code_seq
101 , curr_base_contract_amount
102 , prev_base_contract_amount
103 , request_id
104 , program_application_id
105 , program_id
106 , program_update_date )
107 VALUES (
108 p_summary_build_date
109 , p_authoring_org_id
110 , p_organization_name
111 , p_customer_party_id
112 , p_customer_name
113 , p_scs_code
114 , p_measure_code
115 , p_measure_code_meaning
116 , p_bin_code_seq
117 , p_curr_base_contract_amount
118 , p_prev_base_contract_amount
119 , oki_load_rbk_pvt.g_request_id
120 , oki_load_rbk_pvt.g_program_application_id
121 , oki_load_rbk_pvt.g_program_id
122 , oki_load_rbk_pvt.g_program_update_date ) ;
123
124 EXCEPTION
125 WHEN OTHERS THEN
126 l_sqlcode := SQLCODE ;
127 l_sqlerrm := SQLERRM ;
128
129 -- Set return code TO error
130 x_retcode := '2';
131
132 fnd_message.set_name( application => 'OKI'
133 , name => 'OKI_TABLE_LOAD_FAILURE' ) ;
134
135 fnd_message.set_token( token => 'TABLE_NAME'
136 , value => 'OKI_RNWL_BOOKINGS' ) ;
137
138 fnd_file.put_line( which => fnd_file.log
139 , buff => fnd_message.get ) ;
140
141 fnd_file.put_line( which => fnd_file.log
142 , buff => l_sqlcode||' '|| l_sqlerrm ) ;
143 END ins_rnwl_bkng ;
144 --------------------------------------------------------------------------------
145 --
146 -- Procedure to update records in the oki_rnwl_bookings table.
147 --
148 --------------------------------------------------------------------------------
149 PROCEDURE upd_rnwl_bkng
150 (
151 p_curr_base_contract_amount IN NUMBER
152 , p_prev_base_contract_amount IN NUMBER
153 , p_measure_code_meaning IN VARCHAR2
154 , p_bin_code_seq IN NUMBER
155 , p_organization_name IN VARCHAR2
156 , p_customer_name IN VARCHAR2
157 , p_rbk_rowid IN ROWID
158 , x_retcode OUT NOCOPY VARCHAR2
159
160 ) IS
161
162 -- Local variable declaration
163
164 -- For error handling
165 l_sqlcode VARCHAR2(100) ;
166 l_sqlerrm VARCHAR2(1000) ;
167
168 BEGIN
169
170 -- initialize return code to success
171 x_retcode := '0';
172
173 UPDATE oki_rnwl_bookings SET
174 curr_base_contract_amount = p_curr_base_contract_amount
175 , prev_base_contract_amount = p_prev_base_contract_amount
176 , measure_code_meaning = p_measure_code_meaning
177 , bin_code_seq = p_bin_code_seq
178 , organization_name = p_organization_name
179 , customer_name = p_customer_name
180 , request_id = oki_load_rbk_pvt.g_request_id
181 , program_application_id = oki_load_rbk_pvt.g_program_application_id
182 , program_id = oki_load_rbk_pvt.g_program_id
183 , program_update_date = oki_load_rbk_pvt.g_program_update_date
184 WHERE ROWID = p_rbk_rowid ;
185
186 EXCEPTION
187 WHEN OTHERS THEN
188 l_sqlcode := SQLCODE ;
189 l_sqlerrm := SQLERRM ;
190
191 -- Set return code to error
192 x_retcode := '2';
193
194 fnd_message.set_name( application => 'OKI'
195 , name => 'OKI_UNEXPECTED_FAILURE' ) ;
196
197 fnd_message.set_token( token => 'OBJECT_NAME'
198 , value => 'OKI_LOAD_RBK_PVT.UPD_RNWL_BKNG' ) ;
199
200 fnd_file.put_line( which => fnd_file.log
201 , buff => fnd_message.get );
202
203 fnd_file.put_line( which => fnd_file.log
204 , buff => l_sqlcode||' '|| l_sqlerrm ) ;
205 END upd_rnwl_bkng ;
206
207 --------------------------------------------------------------------------------
208 --
209 -- Procedure to calculate the renewal bookings at the organization level.
210 --
211 --------------------------------------------------------------------------------
212 PROCEDURE calc_rbk_dtl1
213 (
214 x_retcode OUT NOCOPY VARCHAR2
215 ) IS
216
217 --
218 -- Local variable declaration
219 --
220
221 -- For capturing the return code, 0 = success, 1 = warning, 2 = error
222 l_retcode VARCHAR2(1) := NULL ;
223
224 -- For error handling
225 l_sqlcode VARCHAR2(100) := NULL ;
226 l_sqlerrm VARCHAR2(1000) := NULL ;
227
228 -- Location within the program before the error was encountered.
229 l_loc VARCHAR2(200) ;
230
231 l_measure_type VARCHAR2(60) := NULL ;
232
233 l_bin_code_meaning VARCHAR2(240) := NULL ;
234 l_bin_code_seq NUMBER := NULL ;
235
236 rec_l_tactk_by_org_csr oki_utl_pvt.g_tactk_by_org_csr_row ;
237 rec_l_k_exp_in_qtr_by_org_csr oki_utl_pvt.g_k_exp_in_qtr_by_org_csr_row ;
238 rec_l_bin_disp_lkup_csr oki_utl_pvt.g_bin_disp_lkup_csr_row ;
239
240 -- Holds the current measure values for the renewal bookings bin
241 l_curr_b_act_k NUMBER := 0 ;
242 l_curr_k_exp_in_qtr NUMBER := 0 ;
243 l_curr_bklg_k_to_rnw NUMBER := 0 ;
244 l_curr_b_rnwl_oppty NUMBER := 0 ;
245 l_curr_bklg_k_rwned NUMBER := 0 ;
246 l_curr_k_rnwed NUMBER := 0 ;
247 l_curr_new_bus NUMBER := 0 ;
248 l_curr_trmn_k NUMBER := 0 ;
249 l_curr_e_act_k NUMBER := 0 ;
250
251 -- Holds the previous measure values for the renewal bookings bin
252 l_prev_b_act_k NUMBER := 0 ;
253 l_prev_k_exp_in_qtr NUMBER := 0 ;
254 l_prev_bklg_k_to_rnw NUMBER := 0 ;
255 l_prev_b_rnwl_oppty NUMBER := 0 ;
256 l_prev_bklg_k_rwned NUMBER := 0 ;
257 l_prev_k_rnwed NUMBER := 0 ;
258 l_prev_new_bus NUMBER := 0 ;
259 l_prev_trmn_k NUMBER := 0 ;
260 l_prev_e_act_k NUMBER := 0 ;
261
262
263 -- Contracts that have should have been renewed (booked) by the start of
264 -- the quarter but were not. These contracts are the considered
265 -- the backlog contracts to be renewed this quarter
266 CURSOR l_bklg_k_to_rnw_csr
267 (
268 p_qtr_start_date IN DATE
269 , p_authoring_org_id IN NUMBER
270 ) IS
271 SELECT NVL(SUM(base_contract_amount), 0) value
272 , COUNT(shd.chr_id) contract_count
273 FROM oki_sales_k_hdrs shd
274 WHERE shd.is_new_yn IS NULL
275 AND ( shd.date_canceled IS NULL
276 OR shd.date_canceled >= p_qtr_start_date )
277 AND ( shd.date_signed IS NULL
278 OR shd.date_signed >= p_qtr_start_date )
279 AND shd.start_date < p_qtr_start_date
280 AND shd.base_contract_amount BETWEEN 0
281 AND oki_utl_pub.g_contract_limit
282 AND shd.authoring_org_id = p_authoring_org_id
283 ;
284 rec_l_bklg_k_to_rnw_csr l_bklg_k_to_rnw_csr%ROWTYPE ;
285
286 -- Contracts that have been renewed (booked) in this quarter but should have
287 -- been booked before this quarter
288 CURSOR l_bklg_k_rwned_csr
289 (
290 p_summary_build_date IN DATE
291 , p_qtr_start_date IN DATE
292 , p_authoring_org_id IN NUMBER
293 ) IS
294 SELECT NVL(SUM(base_contract_amount), 0) value
295 , COUNT(shd.chr_id) contract_count
296 FROM oki_sales_k_hdrs shd
297 WHERE shd.is_new_yn IS NULL
298 AND shd.date_signed IS NOT NULL
299 AND shd.date_approved IS NOT NULL
300 AND shd.start_date < p_qtr_start_date
301 AND GREATEST(shd.date_signed, shd.date_approved )
302 BETWEEN p_qtr_start_date AND p_summary_build_date
303 AND shd.base_contract_amount BETWEEN 0
304 AND oki_utl_pub.g_contract_limit
305 AND shd.authoring_org_id = p_authoring_org_id
306 ;
307 rec_l_bklg_k_rwned_csr l_bklg_k_rwned_csr%ROWTYPE ;
308
309 -- Contracts that have been renewed in the quarter
310 CURSOR l_k_rnwed_csr
311 (
312 p_qtr_start_date IN DATE
313 , p_qtr_end_date IN DATE
314 , p_authoring_org_id IN NUMBER
315
316 ) IS
317 SELECT NVL(SUM(base_contract_amount), 0) value
318 , COUNT(shd.chr_id) contract_count
319 FROM oki_sales_k_hdrs shd
320 WHERE shd.is_new_yn IS NULL
321 AND shd.date_signed IS NOT NULL
322 AND shd.start_date BETWEEN p_qtr_start_date AND p_qtr_end_date
323 AND GREATEST(shd.date_signed, shd.date_approved) <= p_qtr_end_date
324 AND shd.base_contract_amount BETWEEN 0
325 AND oki_utl_pub.g_contract_limit
326 AND shd.authoring_org_id = p_authoring_org_id
327 ;
328 rec_l_k_rnwed_csr l_k_rnwed_csr%ROWTYPE ;
329
330 -- Contracts that became active between the start of quarter
331 -- and the summary build date and are not a result of a renewal
332 CURSOR l_new_bus_csr
333 (
334 p_summary_build_date IN DATE
335 , p_qtr_start_date IN DATE
336 , p_authoring_org_id IN NUMBER
337 ) IS
338 SELECT NVL(SUM(base_contract_amount), 0) value
339 , COUNT(shd.chr_id) contract_count
340 FROM oki_sales_k_hdrs shd
341 WHERE shd.date_signed <= p_summary_build_date
342 AND shd.date_approved <= p_summary_build_date
343 AND shd.is_new_yn = 'Y'
347 OR shd.date_terminated > p_summary_build_date )
344 AND shd.start_date BETWEEN p_qtr_start_date
345 AND p_summary_build_date
346 AND ( shd.date_terminated IS NULL
348 AND shd.base_contract_amount BETWEEN 0
349 AND oki_utl_pub.g_contract_limit
350 AND shd.authoring_org_id = p_authoring_org_id
351 ;
352 rec_l_new_bus_csr l_new_bus_csr%ROWTYPE ;
353
354 -- Contracts that were terminated between the start of the quarter and the
355 -- summary build date
356 CURSOR l_trmn_k_csr
357 (
358 p_summary_build_date IN DATE
359 , p_qtr_start_date IN DATE
360 , p_authoring_org_id IN NUMBER
361 ) IS
362 SELECT NVL(SUM(base_contract_amount), 0) value
363 , COUNT(shd.chr_id) contract_count
364 FROM oki_sales_k_hdrs shd
365 WHERE date_terminated BETWEEN p_qtr_start_date
366 AND p_summary_build_date
367 AND shd.date_signed IS NOT NULL
368 AND shd.date_approved IS NOT NULL
369 AND shd.base_contract_amount BETWEEN 0
370 AND oki_utl_pub.g_contract_limit
371 AND shd.authoring_org_id = p_authoring_org_id
372 ;
373 rec_l_trmn_k_csr l_trmn_k_csr%ROWTYPE ;
374
375 BEGIN
376
377 -- initialize return code to success
378 l_retcode := '0';
379
380 << g_org_csr_loop >>
381 -- Loop through all the organizations to calculate the
382 -- appropriate amounts
383 FOR rec_g_org_csr IN oki_utl_pvt.g_org_csr LOOP
384
385 --
386 -- Process Beginning Active Contracts record
387 --
388
389 l_measure_type := 'Beginning Active Contracts By Organization' ;
390
391 -- Get the bin display lookup values
392 l_loc := 'Opening cursor to retrieve bin display lookup values for ' ||
393 l_measure_type || '.' ;
394 OPEN oki_utl_pvt.g_bin_disp_lkup_csr ( oki_load_rbk_pvt.g_bin_id
395 , oki_load_rbk_pvt.g_b_act_k_code ) ;
396 FETCH oki_utl_pvt.g_bin_disp_lkup_csr INTO rec_l_bin_disp_lkup_csr ;
397 IF oki_utl_pvt.g_bin_disp_lkup_csr%FOUND THEN
398 l_bin_code_meaning := rec_l_bin_disp_lkup_csr.bin_code_meaning ;
399 l_bin_code_seq := rec_l_bin_disp_lkup_csr.bin_code_seq ;
400 ELSE
401 RAISE NO_DATA_FOUND ;
402 END IF ;
403 CLOSE oki_utl_pvt.g_bin_disp_lkup_csr ;
404
405 -- Get the current value
406 l_loc := 'Opening cursor to determine current ' ||
407 l_measure_type || '.' ;
408 OPEN oki_utl_pvt.g_tactk_by_org_csr (
409 oki_utl_pub.g_glpr_qtr_start_date
410 , rec_g_org_csr.authoring_org_id ) ;
411 FETCH oki_utl_pvt.g_tactk_by_org_csr INTO rec_l_tactk_by_org_csr ;
412 IF oki_utl_pvt.g_tactk_by_org_csr%FOUND THEN
413 l_curr_b_act_k := rec_l_tactk_by_org_csr.value ;
414 END IF ;
415 CLOSE oki_utl_pvt.g_tactk_by_org_csr ;
416
417 -- Get the previous value
418 l_loc := 'Opening Cursor to determine previous ' ||
419 l_measure_type || '.' ;
420 OPEN oki_utl_pvt.g_tactk_by_org_csr (
421 oki_utl_pub.g_py_glpr_qtr_start_date,
422 rec_g_org_csr.authoring_org_id ) ;
423 FETCH oki_utl_pvt.g_tactk_by_org_csr INTO rec_l_tactk_by_org_csr ;
424 IF oki_utl_pvt.g_tactk_by_org_csr%FOUND THEN
425 l_prev_b_act_k := rec_l_tactk_by_org_csr.value ;
426 END IF ;
427 CLOSE oki_utl_pvt.g_tactk_by_org_csr ;
428
429 l_loc := 'Inserting / updating ' || l_measure_type || '.' ;
430 -- Determine if the record is a new one or an existing one
431 OPEN oki_load_rbk_pvt.g_rbk_csr (
432 oki_utl_pub.g_summary_build_date
433 , rec_g_org_csr.authoring_org_id
434 , oki_utl_pub.g_all_customer_id
435 , oki_utl_pub.g_all_k_category_code
436 , oki_load_rbk_pvt.g_b_act_k_code ) ;
437 FETCH oki_load_rbk_pvt.g_rbk_csr INTO rec_g_rbk_csr ;
438 IF oki_load_rbk_pvt.g_rbk_csr%NOTFOUND THEN
439 l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;
440 -- Insert the new record
441 oki_load_rbk_pvt.ins_rnwl_bkng (
442 p_summary_build_date => oki_utl_pub.g_summary_build_date
443 , p_authoring_org_id => rec_g_org_csr.authoring_org_id
444 , p_organization_name => rec_g_org_csr.organization_name
445 , p_customer_party_id => oki_utl_pub.g_all_customer_id
446 , p_customer_name => oki_utl_pub.g_all_customer_name
447 , p_scs_code => oki_utl_pub.g_all_k_category_code
448 , p_measure_code => oki_load_rbk_pvt.g_b_act_k_code
449 , p_measure_code_meaning => l_bin_code_meaning
450 , p_bin_code_seq => l_bin_code_seq
451 , p_curr_base_contract_amount => l_curr_b_act_k
452 , p_prev_base_contract_amount => l_prev_b_act_k
453 , x_retcode => l_retcode ) ;
454 IF l_retcode = '2' THEN
455 -- Load failed, exit immediately.
456 RAISE oki_utl_pub.g_excp_exit_immediate ;
457 END IF ;
458 ELSE
459 l_loc := 'Update the record -- ' || l_measure_type || '.' ;
463 , p_prev_base_contract_amount => l_prev_b_act_k
460 -- Update the existing record
461 oki_load_rbk_pvt.upd_rnwl_bkng (
462 p_curr_base_contract_amount => l_curr_b_act_k
464 , p_measure_code_meaning => l_bin_code_meaning
465 , p_bin_code_seq => l_bin_code_seq
466 , p_organization_name => rec_g_org_csr.organization_name
467 , p_customer_name => oki_utl_pub.g_all_customer_name
468 , p_rbk_rowid => rec_g_rbk_csr.rowid
469 , x_retcode => l_retcode ) ;
470
471 IF l_retcode = '2' THEN
472 -- Load failed, exit immediately.
473 RAISE oki_utl_pub.g_excp_exit_immediate ;
474 END IF ;
475 END IF ;
476 CLOSE oki_load_rbk_pvt.g_rbk_csr ;
477
478 --
479 -- Process Expiring During Quarter record
480 --
481
482 l_measure_type := 'Expiring During Quarter By Organization' ;
483
484 -- Get the bin display lookup values
485 l_loc := 'Opening cursor to retrieve bin display lookup values for ' ||
486 l_measure_type || '.' ;
487 OPEN oki_utl_pvt.g_bin_disp_lkup_csr ( oki_load_rbk_pvt.g_bin_id
488 , oki_load_rbk_pvt.g_exp_in_qtr_code ) ;
489 FETCH oki_utl_pvt.g_bin_disp_lkup_csr INTO rec_l_bin_disp_lkup_csr ;
490 IF oki_utl_pvt.g_bin_disp_lkup_csr%FOUND THEN
491 l_bin_code_meaning := rec_l_bin_disp_lkup_csr.bin_code_meaning ;
492 l_bin_code_seq := rec_l_bin_disp_lkup_csr.bin_code_seq ;
493 ELSE
494 RAISE NO_DATA_FOUND ;
495 END IF ;
496 CLOSE oki_utl_pvt.g_bin_disp_lkup_csr ;
497
498 -- Get the current value
499 l_loc := 'Opening cursor to determine current ' ||
500 l_measure_type || '.' ;
501 OPEN oki_utl_pvt.g_k_exp_in_qtr_by_org_csr (
502 oki_utl_pub.g_glpr_qtr_start_date
503 , oki_utl_pub.g_glpr_qtr_end_date
504 , rec_g_org_csr.authoring_org_id ) ;
505 FETCH oki_utl_pvt.g_k_exp_in_qtr_by_org_csr INTO
506 rec_l_k_exp_in_qtr_by_org_csr ;
507 IF oki_utl_pvt.g_k_exp_in_qtr_by_org_csr%FOUND THEN
508 l_curr_k_exp_in_qtr := rec_l_k_exp_in_qtr_by_org_csr.value ;
509 END IF ;
510 CLOSE oki_utl_pvt.g_k_exp_in_qtr_by_org_csr ;
511
512 -- Get the previous value
513 l_loc := 'Opening Cursor to determine previous ' ||
514 l_measure_type || '.' ;
515 OPEN oki_utl_pvt.g_k_exp_in_qtr_by_org_csr (
516 oki_utl_pub.g_py_glpr_qtr_start_date
517 , oki_utl_pub.g_py_glpr_qtr_end_date
518 , rec_g_org_csr.authoring_org_id ) ;
519 FETCH oki_utl_pvt.g_k_exp_in_qtr_by_org_csr INTO
520 rec_l_k_exp_in_qtr_by_org_csr ;
521 IF oki_utl_pvt.g_k_exp_in_qtr_by_org_csr%FOUND THEN
522 l_prev_k_exp_in_qtr := rec_l_k_exp_in_qtr_by_org_csr.value ;
523 END IF ;
524 CLOSE oki_utl_pvt.g_k_exp_in_qtr_by_org_csr ;
525
526 l_loc := 'Inserting / updating ' || l_measure_type || '.' ;
527 -- Determine if the record is a new one or an existing one
528 OPEN oki_load_rbk_pvt.g_rbk_csr (
529 oki_utl_pub.g_summary_build_date
530 , rec_g_org_csr.authoring_org_id
531 , oki_utl_pub.g_all_customer_id
532 , oki_utl_pub.g_all_k_category_code
533 , oki_load_rbk_pvt.g_exp_in_qtr_code ) ;
534 FETCH oki_load_rbk_pvt.g_rbk_csr INTO rec_g_rbk_csr ;
535 IF oki_load_rbk_pvt.g_rbk_csr%NOTFOUND THEN
536 l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;
537 -- Insert the new record
538 oki_load_rbk_pvt.ins_rnwl_bkng (
539 p_summary_build_date => oki_utl_pub.g_summary_build_date
540 , p_authoring_org_id => rec_g_org_csr.authoring_org_id
541 , p_organization_name => rec_g_org_csr.organization_name
542 , p_customer_party_id => oki_utl_pub.g_all_customer_id
543 , p_customer_name => oki_utl_pub.g_all_customer_name
544 , p_scs_code => oki_utl_pub.g_all_k_category_code
545 , p_measure_code => oki_load_rbk_pvt.g_exp_in_qtr_code
546 , p_measure_code_meaning => l_bin_code_meaning
547 , p_bin_code_seq => l_bin_code_seq
548 , p_curr_base_contract_amount => l_curr_k_exp_in_qtr
549 , p_prev_base_contract_amount => l_prev_k_exp_in_qtr
550 , x_retcode => l_retcode ) ;
551 IF l_retcode = '2' THEN
552 -- Load failed, exit immediately.
553 RAISE oki_utl_pub.g_excp_exit_immediate ;
554 END IF ;
555 ELSE
556 l_loc := 'Update the record -- ' || l_measure_type || '.' ;
557 -- Update the existing record
558 oki_load_rbk_pvt.upd_rnwl_bkng (
559 p_curr_base_contract_amount => l_curr_k_exp_in_qtr
560 , p_prev_base_contract_amount => l_prev_k_exp_in_qtr
561 , p_measure_code_meaning => l_bin_code_meaning
565 , p_rbk_rowid => rec_g_rbk_csr.rowid
562 , p_bin_code_seq => l_bin_code_seq
563 , p_organization_name => rec_g_org_csr.organization_name
564 , p_customer_name => oki_utl_pub.g_all_customer_name
566 , x_retcode => l_retcode ) ;
567
568 IF l_retcode = '2' THEN
569 -- Load failed, exit immediately.
570 RAISE oki_utl_pub.g_excp_exit_immediate ;
571 END IF ;
572 END IF ;
573 CLOSE oki_load_rbk_pvt.g_rbk_csr ;
574
575 --
576 -- Process Backlog Renewals record
577 --
578
579 l_measure_type := 'Backlog Renewals By Organization' ;
580
581 -- Get the bin display lookup values
582 l_loc := 'Opening cursor to retrieve bin display lookup values for ' ||
583 l_measure_type || '.' ;
584 OPEN oki_utl_pvt.g_bin_disp_lkup_csr ( oki_load_rbk_pvt.g_bin_id
585 , oki_load_rbk_pvt.g_bklg_k_to_rnw_code ) ;
586 FETCH oki_utl_pvt.g_bin_disp_lkup_csr INTO rec_l_bin_disp_lkup_csr ;
587 IF oki_utl_pvt.g_bin_disp_lkup_csr%FOUND THEN
588 l_bin_code_meaning := rec_l_bin_disp_lkup_csr.bin_code_meaning ;
589 l_bin_code_seq := rec_l_bin_disp_lkup_csr.bin_code_seq ;
590 ELSE
591 RAISE NO_DATA_FOUND ;
592 END IF ;
593 CLOSE oki_utl_pvt.g_bin_disp_lkup_csr ;
594
595 -- Get the current value
596 l_loc := 'Opening cursor to determine current ' ||
597 l_measure_type || '.' ;
598 OPEN l_bklg_k_to_rnw_csr ( oki_utl_pub.g_glpr_qtr_start_date,
599 rec_g_org_csr.authoring_org_id ) ;
600 FETCH l_bklg_k_to_rnw_csr INTO rec_l_bklg_k_to_rnw_csr ;
601 IF l_bklg_k_to_rnw_csr%FOUND THEN
602 l_curr_bklg_k_to_rnw := rec_l_bklg_k_to_rnw_csr.value ;
603 END IF ;
604 CLOSE l_bklg_k_to_rnw_csr ;
605
606 -- Get the previous value
607 l_loc := 'Opening Cursor to determine previous ' ||
608 l_measure_type || '.' ;
609 OPEN l_bklg_k_to_rnw_csr ( oki_utl_pub.g_py_glpr_qtr_start_date,
610 rec_g_org_csr.authoring_org_id ) ;
611 FETCH l_bklg_k_to_rnw_csr INTO rec_l_bklg_k_to_rnw_csr ;
612 IF l_bklg_k_to_rnw_csr%FOUND THEN
613 l_prev_bklg_k_to_rnw := rec_l_bklg_k_to_rnw_csr.value ;
614 END IF ;
615 CLOSE l_bklg_k_to_rnw_csr ;
616
617 l_loc := 'Inserting / updating ' || l_measure_type || '.' ;
618 -- Determine if the record is a new one or an existing one
619 OPEN oki_load_rbk_pvt.g_rbk_csr ( oki_utl_pub.g_summary_build_date
620 , rec_g_org_csr.authoring_org_id, oki_utl_pub.g_all_customer_id
621 , oki_utl_pub.g_all_k_category_code
622 , oki_load_rbk_pvt.g_bklg_k_to_rnw_code ) ;
623 FETCH oki_load_rbk_pvt.g_rbk_csr INTO rec_g_rbk_csr ;
624 IF oki_load_rbk_pvt.g_rbk_csr%NOTFOUND THEN
625 l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;
626 -- Insert the new record
627 oki_load_rbk_pvt.ins_rnwl_bkng (
628 p_summary_build_date => oki_utl_pub.g_summary_build_date
629 , p_authoring_org_id => rec_g_org_csr.authoring_org_id
630 , p_organization_name => rec_g_org_csr.organization_name
631 , p_customer_party_id => oki_utl_pub.g_all_customer_id
632 , p_customer_name => oki_utl_pub.g_all_customer_name
633 , p_scs_code => oki_utl_pub.g_all_k_category_code
634 , p_measure_code => oki_load_rbk_pvt.g_bklg_k_to_rnw_code
635 , p_measure_code_meaning => l_bin_code_meaning
636 , p_bin_code_seq => l_bin_code_seq
637 , p_curr_base_contract_amount => l_curr_bklg_k_to_rnw
638 , p_prev_base_contract_amount => l_prev_bklg_k_to_rnw
639 , x_retcode => l_retcode ) ;
640 IF l_retcode = '2' THEN
641 -- Load failed, exit immediately.
642 RAISE oki_utl_pub.g_excp_exit_immediate ;
643 END IF ;
644 ELSE
645 l_loc := 'Update the record -- ' || l_measure_type || '.' ;
646 -- Update the existing record
647 oki_load_rbk_pvt.upd_rnwl_bkng (
648 p_curr_base_contract_amount => l_curr_bklg_k_to_rnw
649 , p_prev_base_contract_amount => l_prev_bklg_k_to_rnw
650 , p_measure_code_meaning => l_bin_code_meaning
651 , p_bin_code_seq => l_bin_code_seq
655 , x_retcode => l_retcode ) ;
652 , p_organization_name => rec_g_org_csr.organization_name
653 , p_customer_name => oki_utl_pub.g_all_customer_name
654 , p_rbk_rowid => rec_g_rbk_csr.rowid
656
657 IF l_retcode = '2' THEN
658 -- Load failed, exit immediately.
659 RAISE oki_utl_pub.g_excp_exit_immediate ;
660 END IF ;
661 END IF ;
662 CLOSE oki_load_rbk_pvt.g_rbk_csr ;
663
664 --
665 -- Process Beginning Renewal Opportunity record
666 --
667
668 l_measure_type := 'Beginning Renewal Opportunity By Organization' ;
669
670 -- Get the bin display lookup values
671 l_loc := 'Opening cursor to retrieve bin display lookup values for ' ||
672 l_measure_type || '.' ;
673 OPEN oki_utl_pvt.g_bin_disp_lkup_csr ( oki_load_rbk_pvt.g_bin_id
674 , oki_load_rbk_pvt.g_b_rnwl_oppty_code ) ;
675 FETCH oki_utl_pvt.g_bin_disp_lkup_csr INTO rec_l_bin_disp_lkup_csr ;
676 IF oki_utl_pvt.g_bin_disp_lkup_csr%FOUND THEN
677 l_bin_code_meaning := rec_l_bin_disp_lkup_csr.bin_code_meaning ;
678 l_bin_code_seq := rec_l_bin_disp_lkup_csr.bin_code_seq ;
679 ELSE
680 RAISE NO_DATA_FOUND ;
681 END IF ;
682 CLOSE oki_utl_pvt.g_bin_disp_lkup_csr ;
683
684 l_curr_b_rnwl_oppty := l_curr_k_exp_in_qtr + l_curr_bklg_k_to_rnw ;
685 l_prev_b_rnwl_oppty := l_prev_k_exp_in_qtr + l_prev_bklg_k_to_rnw ;
686
687 l_loc := 'Inserting / updating ' || l_measure_type || '.' ;
688 -- Determine if the record is a new one or an existing one
689 OPEN oki_load_rbk_pvt.g_rbk_csr ( oki_utl_pub.g_summary_build_date
690 , rec_g_org_csr.authoring_org_id, oki_utl_pub.g_all_customer_id
691 , oki_utl_pub.g_all_k_category_code
692 , oki_load_rbk_pvt.g_b_rnwl_oppty_code ) ;
693 FETCH oki_load_rbk_pvt.g_rbk_csr INTO rec_g_rbk_csr ;
694 IF oki_load_rbk_pvt.g_rbk_csr%NOTFOUND THEN
695 l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;
696 -- Insert the new record
697 oki_load_rbk_pvt.ins_rnwl_bkng (
698 p_summary_build_date => oki_utl_pub.g_summary_build_date
699 , p_authoring_org_id => rec_g_org_csr.authoring_org_id
700 , p_organization_name => rec_g_org_csr.organization_name
701 , p_customer_party_id => oki_utl_pub.g_all_customer_id
702 , p_customer_name => oki_utl_pub.g_all_customer_name
703 , p_scs_code => oki_utl_pub.g_all_k_category_code
704 , p_measure_code => oki_load_rbk_pvt.g_b_rnwl_oppty_code
705 , p_measure_code_meaning => l_bin_code_meaning
706 , p_bin_code_seq => l_bin_code_seq
707 , p_curr_base_contract_amount => l_curr_b_rnwl_oppty
708 , p_prev_base_contract_amount => l_prev_b_rnwl_oppty
709 , x_retcode => l_retcode ) ;
710 IF l_retcode = '2' THEN
711 -- Load failed, exit immediately.
712 RAISE oki_utl_pub.g_excp_exit_immediate ;
713 END IF ;
714 ELSE
715 l_loc := 'Update the record -- ' || l_measure_type || '.' ;
716 -- Update the existing record
717 oki_load_rbk_pvt.upd_rnwl_bkng (
718 p_curr_base_contract_amount => l_curr_b_rnwl_oppty
719 , p_prev_base_contract_amount => l_prev_b_rnwl_oppty
720 , p_measure_code_meaning => l_bin_code_meaning
721 , p_bin_code_seq => l_bin_code_seq
722 , p_organization_name => rec_g_org_csr.organization_name
723 , p_customer_name => oki_utl_pub.g_all_customer_name
724 , p_rbk_rowid => rec_g_rbk_csr.rowid
725 , x_retcode => l_retcode ) ;
726
727 IF l_retcode = '2' THEN
728 -- Load failed, exit immediately.
729 RAISE oki_utl_pub.g_excp_exit_immediate ;
730 END IF ;
731 END IF ;
732 CLOSE oki_load_rbk_pvt.g_rbk_csr ;
733
734 --
735 -- Process Backlog Contracts Renewed record
736 --
737
738 l_measure_type := 'Backlog Contracts Renewed By Organization' ;
739
740 -- Get the bin display lookup values
741 l_loc := 'Opening cursor to retrieve bin display lookup values for ' ||
742 l_measure_type || '.' ;
743 OPEN oki_utl_pvt.g_bin_disp_lkup_csr ( oki_load_rbk_pvt.g_bin_id
744 , oki_load_rbk_pvt.g_bklg_k_rnwed_code ) ;
745 FETCH oki_utl_pvt.g_bin_disp_lkup_csr INTO rec_l_bin_disp_lkup_csr ;
746 IF oki_utl_pvt.g_bin_disp_lkup_csr%FOUND THEN
747 l_bin_code_meaning := rec_l_bin_disp_lkup_csr.bin_code_meaning ;
748 l_bin_code_seq := rec_l_bin_disp_lkup_csr.bin_code_seq ;
749 ELSE
750 RAISE NO_DATA_FOUND ;
751 END IF ;
752 CLOSE oki_utl_pvt.g_bin_disp_lkup_csr ;
753
754 -- Get the current value
755 l_loc := 'Opening cursor to determine current ' ||
756 l_measure_type || '.' ;
757 OPEN l_bklg_k_rwned_csr( oki_utl_pub.g_summary_build_date,
758 oki_utl_pub.g_glpr_qtr_start_date
759 , rec_g_org_csr.authoring_org_id ) ;
763 END IF ;
760 FETCH l_bklg_k_rwned_csr INTO rec_l_bklg_k_rwned_csr ;
761 IF l_bklg_k_rwned_csr%FOUND THEN
762 l_curr_bklg_k_rwned := rec_l_bklg_k_rwned_csr.value ;
764 CLOSE l_bklg_k_rwned_csr;
765
766 -- Get the previous value
767 l_loc := 'Opening cursor to determine previous ' ||
768 l_measure_type || '.' ;
769 OPEN l_bklg_k_rwned_csr( oki_utl_pub.g_py_summary_build_date,
770 oki_utl_pub.g_py_glpr_qtr_start_date
771 , rec_g_org_csr.authoring_org_id ) ;
772 FETCH l_bklg_k_rwned_csr INTO rec_l_bklg_k_rwned_csr ;
773 IF l_bklg_k_rwned_csr%FOUND THEN
774 l_prev_bklg_k_rwned := rec_l_bklg_k_rwned_csr.value ;
775 END IF ;
776 CLOSE l_bklg_k_rwned_csr ;
777
778 l_loc := 'Inserting / updating ' || l_measure_type || '.' ;
779 -- Determine if the record is a new one or an existing one
780 OPEN oki_load_rbk_pvt.g_rbk_csr ( oki_utl_pub.g_summary_build_date
781 , rec_g_org_csr.authoring_org_id, oki_utl_pub.g_all_customer_id
782 , oki_utl_pub.g_all_k_category_code
783 , oki_load_rbk_pvt.g_bklg_k_rnwed_code ) ;
784 FETCH oki_load_rbk_pvt.g_rbk_csr INTO rec_g_rbk_csr ;
785 IF oki_load_rbk_pvt.g_rbk_csr%NOTFOUND THEN
786 l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;
787 -- Insert the new record
788 oki_load_rbk_pvt.ins_rnwl_bkng (
789 p_summary_build_date => oki_utl_pub.g_summary_build_date
790 , p_authoring_org_id => rec_g_org_csr.authoring_org_id
791 , p_organization_name => rec_g_org_csr.organization_name
792 , p_customer_party_id => oki_utl_pub.g_all_customer_id
793 , p_customer_name => oki_utl_pub.g_all_customer_name
794 , p_scs_code => oki_utl_pub.g_all_k_category_code
795 , p_measure_code => oki_load_rbk_pvt.g_bklg_k_rnwed_code
796 , p_measure_code_meaning => l_bin_code_meaning
797 , p_bin_code_seq => l_bin_code_seq
798 , p_curr_base_contract_amount => l_curr_bklg_k_rwned
799 , p_prev_base_contract_amount => l_prev_bklg_k_rwned
800 , x_retcode => l_retcode ) ;
801 IF l_retcode = '2' THEN
802 -- Load failed, exit immediately.
803 RAISE oki_utl_pub.g_excp_exit_immediate ;
804 END IF ;
805 ELSE
806 l_loc := 'Update the record -- ' || l_measure_type || '.' ;
807 -- Update the existing record
808 oki_load_rbk_pvt.upd_rnwl_bkng (
809 p_curr_base_contract_amount => l_curr_bklg_k_rwned
810 , p_prev_base_contract_amount => l_prev_bklg_k_rwned
811 , p_measure_code_meaning => l_bin_code_meaning
812 , p_bin_code_seq => l_bin_code_seq
813 , p_organization_name => rec_g_org_csr.organization_name
814 , p_customer_name => oki_utl_pub.g_all_customer_name
815 , p_rbk_rowid => rec_g_rbk_csr.rowid
816 , x_retcode => l_retcode ) ;
817
818 IF l_retcode = '2' THEN
819 -- Load failed, exit immediately.
820 RAISE oki_utl_pub.g_excp_exit_immediate ;
821 END IF ;
822 END IF ;
823 CLOSE oki_load_rbk_pvt.g_rbk_csr ;
824
825 --
826 -- Process Contracts Renewed record
827 --
828
829 l_measure_type := 'Contracts Renewed By Organization' ;
830
831 -- Get the bin display lookup values
832 l_loc := 'Opening cursor to retrieve bin display lookup values for ' ||
833 l_measure_type || '.' ;
834 OPEN oki_utl_pvt.g_bin_disp_lkup_csr ( oki_load_rbk_pvt.g_bin_id
835 , oki_load_rbk_pvt.g_k_rnwed_code ) ;
836 FETCH oki_utl_pvt.g_bin_disp_lkup_csr INTO rec_l_bin_disp_lkup_csr ;
837 IF oki_utl_pvt.g_bin_disp_lkup_csr%FOUND THEN
838 l_bin_code_meaning := rec_l_bin_disp_lkup_csr.bin_code_meaning ;
839 l_bin_code_seq := rec_l_bin_disp_lkup_csr.bin_code_seq ;
840 ELSE
841 RAISE NO_DATA_FOUND ;
842 END IF ;
843 CLOSE oki_utl_pvt.g_bin_disp_lkup_csr ;
844
845 -- Get the current value
846 l_loc := 'Opening cursor to determine current ' ||
847 l_measure_type || '.' ;
848 OPEN l_k_rnwed_csr( oki_utl_pub.g_glpr_qtr_start_date
849 , oki_utl_pub.g_glpr_qtr_end_date
850 , rec_g_org_csr.authoring_org_id ) ;
851 FETCH l_k_rnwed_csr INTO rec_l_k_rnwed_csr ;
852 IF l_k_rnwed_csr%FOUND THEN
853 l_curr_k_rnwed := rec_l_k_rnwed_csr.value ;
854 END IF ;
855 CLOSE l_k_rnwed_csr ;
856
857 -- Get the previous value
858 l_loc := 'Opening Cursor to determine previous ' ||
859 l_measure_type || '.' ;
860 OPEN l_k_rnwed_csr( oki_utl_pub.g_py_glpr_qtr_start_date
861 , oki_utl_pub.g_py_glpr_qtr_end_date
862 , rec_g_org_csr.authoring_org_id ) ;
863 FETCH l_k_rnwed_csr INTO rec_l_k_rnwed_csr ;
864 IF l_k_rnwed_csr%FOUND THEN
865 l_prev_k_rnwed := rec_l_k_rnwed_csr.value ;
866 END IF ;
867 CLOSE l_k_rnwed_csr ;
868
869 l_loc := 'Inserting / updating ' || l_measure_type || '.' ;
870 -- Determine if the record is a new one or an existing one
874 , oki_load_rbk_pvt.g_k_rnwed_code ) ;
871 OPEN oki_load_rbk_pvt.g_rbk_csr ( oki_utl_pub.g_summary_build_date
872 , rec_g_org_csr.authoring_org_id, oki_utl_pub.g_all_customer_id
873 , oki_utl_pub.g_all_k_category_code
875 FETCH oki_load_rbk_pvt.g_rbk_csr INTO rec_g_rbk_csr ;
876 IF oki_load_rbk_pvt.g_rbk_csr%NOTFOUND THEN
877 l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;
878 -- Insert the new record
879 oki_load_rbk_pvt.ins_rnwl_bkng (
880 p_summary_build_date => oki_utl_pub.g_summary_build_date
881 , p_authoring_org_id => rec_g_org_csr.authoring_org_id
882 , p_organization_name => rec_g_org_csr.organization_name
883 , p_customer_party_id => oki_utl_pub.g_all_customer_id
884 , p_customer_name => oki_utl_pub.g_all_customer_name
885 , p_scs_code => oki_utl_pub.g_all_k_category_code
886 , p_measure_code => oki_load_rbk_pvt.g_k_rnwed_code
887 , p_measure_code_meaning => l_bin_code_meaning
888 , p_bin_code_seq => l_bin_code_seq
889 , p_curr_base_contract_amount => l_curr_k_rnwed
890 , p_prev_base_contract_amount => l_prev_k_rnwed
891 , x_retcode => l_retcode ) ;
892 IF l_retcode = '2' THEN
893 -- Load failed, exit immediately.
894 RAISE oki_utl_pub.g_excp_exit_immediate ;
895 END IF ;
896 ELSE
897 l_loc := 'Update the record -- ' || l_measure_type || '.' ;
898 -- Update the existing record
899 oki_load_rbk_pvt.upd_rnwl_bkng (
900 p_curr_base_contract_amount => l_curr_k_rnwed
901 , p_prev_base_contract_amount => l_prev_k_rnwed
902 , p_measure_code_meaning => l_bin_code_meaning
903 , p_bin_code_seq => l_bin_code_seq
904 , p_organization_name => rec_g_org_csr.organization_name
905 , p_customer_name => oki_utl_pub.g_all_customer_name
906 , p_rbk_rowid => rec_g_rbk_csr.rowid
907 , x_retcode => l_retcode ) ;
908
909 IF l_retcode = '2' THEN
910 -- Load failed, exit immediately.
911 RAISE oki_utl_pub.g_excp_exit_immediate ;
912 END IF ;
913 END IF ;
914 CLOSE oki_load_rbk_pvt.g_rbk_csr ;
915
916 --
917 -- Process New Business record
918 --
919
920 l_measure_type := 'New Business By Organization' ;
921
922 -- Get the bin display lookup values
923 l_loc := 'Opening cursor to retrieve bin display lookup values for ' ||
924 l_measure_type || '.' ;
925 OPEN oki_utl_pvt.g_bin_disp_lkup_csr ( oki_load_rbk_pvt.g_bin_id
926 , oki_load_rbk_pvt.g_new_bsn_code ) ;
927 FETCH oki_utl_pvt.g_bin_disp_lkup_csr INTO rec_l_bin_disp_lkup_csr ;
928 IF oki_utl_pvt.g_bin_disp_lkup_csr%FOUND THEN
929 l_bin_code_meaning := rec_l_bin_disp_lkup_csr.bin_code_meaning ;
930 l_bin_code_seq := rec_l_bin_disp_lkup_csr.bin_code_seq ;
931 ELSE
932 RAISE NO_DATA_FOUND ;
933 END IF ;
934 CLOSE oki_utl_pvt.g_bin_disp_lkup_csr ;
935
936 -- Get the current value
937 l_loc := 'Opening cursor to determine current ' ||
938 l_measure_type || '.' ;
939 OPEN l_new_bus_csr( oki_utl_pub.g_summary_build_date
940 , oki_utl_pub.g_glpr_qtr_start_date
941 , rec_g_org_csr.authoring_org_id ) ;
942 FETCH l_new_bus_csr INTO rec_l_new_bus_csr ;
943 IF l_new_bus_csr%FOUND THEN
944 l_curr_new_bus := rec_l_new_bus_csr.value ;
945 END IF ;
946 CLOSE l_new_bus_csr ;
947
948 -- Get the previous value
949 l_loc := 'Opening Cursor to determine previous ' ||
950 l_measure_type || '.' ;
951 OPEN l_new_bus_csr( oki_utl_pub.g_py_summary_build_date
952 , oki_utl_pub.g_py_glpr_qtr_start_date
953 , rec_g_org_csr.authoring_org_id ) ;
954 FETCH l_new_bus_csr INTO rec_l_new_bus_csr ;
955 IF l_new_bus_csr%FOUND THEN
956 l_prev_new_bus := rec_l_new_bus_csr.value ;
957 END IF ;
958 CLOSE l_new_bus_csr ;
959
960 l_loc := 'Inserting / updating ' || l_measure_type || '.' ;
961 -- Determine if the record is a new one or an existing one
962 OPEN oki_load_rbk_pvt.g_rbk_csr ( oki_utl_pub.g_summary_build_date
963 , rec_g_org_csr.authoring_org_id, oki_utl_pub.g_all_customer_id
964 , oki_utl_pub.g_all_k_category_code
965 , oki_load_rbk_pvt.g_new_bsn_code ) ;
966 FETCH oki_load_rbk_pvt.g_rbk_csr INTO rec_g_rbk_csr ;
967 IF oki_load_rbk_pvt.g_rbk_csr%NOTFOUND THEN
971 p_summary_build_date => oki_utl_pub.g_summary_build_date
968 l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;
969 -- Insert the new record
970 oki_load_rbk_pvt.ins_rnwl_bkng (
972 , p_authoring_org_id => rec_g_org_csr.authoring_org_id
973 , p_organization_name => rec_g_org_csr.organization_name
974 , p_customer_party_id => oki_utl_pub.g_all_customer_id
975 , p_customer_name => oki_utl_pub.g_all_customer_name
976 , p_scs_code => oki_utl_pub.g_all_k_category_code
977 , p_measure_code => oki_load_rbk_pvt.g_new_bsn_code
978 , p_measure_code_meaning => l_bin_code_meaning
979 , p_bin_code_seq => l_bin_code_seq
980 , p_curr_base_contract_amount => l_curr_new_bus
981 , p_prev_base_contract_amount => l_prev_new_bus
982 , x_retcode => l_retcode ) ;
983 IF l_retcode = '2' THEN
984 -- Load failed, exit immediately.
985 RAISE oki_utl_pub.g_excp_exit_immediate ;
986 END IF ;
987 ELSE
988 l_loc := 'Update the record -- ' || l_measure_type || '.' ;
989 -- Update the existing record
990 oki_load_rbk_pvt.upd_rnwl_bkng (
991 p_curr_base_contract_amount => l_curr_new_bus
992 , p_prev_base_contract_amount => l_prev_new_bus
993 , p_measure_code_meaning => l_bin_code_meaning
994 , p_bin_code_seq => l_bin_code_seq
995 , p_organization_name => rec_g_org_csr.organization_name
996 , p_customer_name => oki_utl_pub.g_all_customer_name
997 , p_rbk_rowid => rec_g_rbk_csr.rowid
998 , x_retcode => l_retcode ) ;
999
1000 IF l_retcode = '2' THEN
1001 -- Load failed, exit immediately.
1002 RAISE oki_utl_pub.g_excp_exit_immediate ;
1003 END IF ;
1004 END IF ;
1005 CLOSE oki_load_rbk_pvt.g_rbk_csr ;
1006
1007 --
1008 -- Process Terminated Contracts record
1009 --
1010
1011 l_measure_type := 'Terminated Contracts By Organization' ;
1012
1013 -- Get the bin display lookup values
1014 l_loc := 'Opening cursor to retrieve bin display lookup values for ' ||
1015 l_measure_type || '.' ;
1016 OPEN oki_utl_pvt.g_bin_disp_lkup_csr ( oki_load_rbk_pvt.g_bin_id
1017 , oki_load_rbk_pvt.g_trmn_k_code ) ;
1018 FETCH oki_utl_pvt.g_bin_disp_lkup_csr INTO rec_l_bin_disp_lkup_csr ;
1019 IF oki_utl_pvt.g_bin_disp_lkup_csr%FOUND THEN
1020 l_bin_code_meaning := rec_l_bin_disp_lkup_csr.bin_code_meaning ;
1021 l_bin_code_seq := rec_l_bin_disp_lkup_csr.bin_code_seq ;
1022 ELSE
1023 RAISE NO_DATA_FOUND ;
1024 END IF ;
1025 CLOSE oki_utl_pvt.g_bin_disp_lkup_csr ;
1026
1027 -- Get the current value
1028 l_loc := 'Opening cursor to determine current ' ||
1029 l_measure_type || '.' ;
1030 OPEN l_trmn_k_csr( oki_utl_pub.g_summary_build_date
1031 , oki_utl_pub.g_glpr_qtr_start_date
1032 , rec_g_org_csr.authoring_org_id ) ;
1033 FETCH l_trmn_k_csr INTO rec_l_trmn_k_csr ;
1034 IF l_trmn_k_csr%FOUND THEN
1035 l_curr_trmn_k := rec_l_trmn_k_csr.value ;
1036 END IF ;
1037 CLOSE l_trmn_k_csr ;
1038
1039 -- Get the previous value
1040 l_loc := 'Opening Cursor to determine previous ' ||
1041 l_measure_type || '.' ;
1042 OPEN l_trmn_k_csr( oki_utl_pub.g_py_summary_build_date
1043 , oki_utl_pub.g_py_glpr_qtr_start_date
1044 , rec_g_org_csr.authoring_org_id ) ;
1045 FETCH l_trmn_k_csr INTO rec_l_trmn_k_csr ;
1046 IF l_trmn_k_csr%FOUND THEN
1047 l_prev_trmn_k := rec_l_trmn_k_csr.value ;
1048 END IF ;
1049 CLOSE l_trmn_k_csr ;
1050
1051 l_loc := 'Inserting / updating ' || l_measure_type || '.' ;
1052 -- Determine if the record is a new one or an existing one
1053 OPEN oki_load_rbk_pvt.g_rbk_csr ( oki_utl_pub.g_summary_build_date
1054 , rec_g_org_csr.authoring_org_id, oki_utl_pub.g_all_customer_id
1055 , oki_utl_pub.g_all_k_category_code
1056 , oki_load_rbk_pvt.g_trmn_k_code ) ;
1057 FETCH oki_load_rbk_pvt.g_rbk_csr INTO rec_g_rbk_csr ;
1058 IF oki_load_rbk_pvt.g_rbk_csr%NOTFOUND THEN
1059 l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;
1060 -- Insert the new record
1061 oki_load_rbk_pvt.ins_rnwl_bkng (
1065 , p_customer_party_id => oki_utl_pub.g_all_customer_id
1062 p_summary_build_date => oki_utl_pub.g_summary_build_date
1063 , p_authoring_org_id => rec_g_org_csr.authoring_org_id
1064 , p_organization_name => rec_g_org_csr.organization_name
1066 , p_customer_name => oki_utl_pub.g_all_customer_name
1067 , p_scs_code => oki_utl_pub.g_all_k_category_code
1068 , p_measure_code => oki_load_rbk_pvt.g_trmn_k_code
1069 , p_measure_code_meaning => l_bin_code_meaning
1070 , p_bin_code_seq => l_bin_code_seq
1071 , p_curr_base_contract_amount => l_curr_trmn_k
1072 , p_prev_base_contract_amount => l_prev_trmn_k
1073 , x_retcode => l_retcode ) ;
1074 IF l_retcode = '2' THEN
1075 -- Load failed, exit immediately.
1076 RAISE oki_utl_pub.g_excp_exit_immediate ;
1077 END IF ;
1078 ELSE
1079 l_loc := 'Update the record -- ' || l_measure_type || '.' ;
1080 -- Update the existing record
1081 oki_load_rbk_pvt.upd_rnwl_bkng (
1082 p_curr_base_contract_amount => l_curr_trmn_k
1083 , p_prev_base_contract_amount => l_prev_trmn_k
1084 , p_measure_code_meaning => l_bin_code_meaning
1085 , p_bin_code_seq => l_bin_code_seq
1086 , p_organization_name => rec_g_org_csr.organization_name
1087 , p_customer_name => oki_utl_pub.g_all_customer_name
1088 , p_rbk_rowid => rec_g_rbk_csr.rowid
1089 , x_retcode => l_retcode ) ;
1090
1091 IF l_retcode = '2' THEN
1092 -- Load failed, exit immediately.
1093 RAISE oki_utl_pub.g_excp_exit_immediate ;
1094 END IF ;
1095 END IF ;
1096 CLOSE oki_load_rbk_pvt.g_rbk_csr ;
1097
1098 --
1099 -- Process Ending Active Contracts record
1100 --
1101
1102 l_measure_type := 'Ending Active Contracts By Organization' ;
1103
1104 -- Get the bin display lookup values
1105 l_loc := 'Opening cursor to retrieve bin display lookup values for ' ||
1106 l_measure_type || '.' ;
1107 OPEN oki_utl_pvt.g_bin_disp_lkup_csr ( oki_load_rbk_pvt.g_bin_id
1108 , oki_load_rbk_pvt.g_e_act_k_code ) ;
1109 FETCH oki_utl_pvt.g_bin_disp_lkup_csr INTO rec_l_bin_disp_lkup_csr ;
1110 IF oki_utl_pvt.g_bin_disp_lkup_csr%FOUND THEN
1111 l_bin_code_meaning := rec_l_bin_disp_lkup_csr.bin_code_meaning ;
1112 l_bin_code_seq := rec_l_bin_disp_lkup_csr.bin_code_seq ;
1113 ELSE
1114 RAISE NO_DATA_FOUND ;
1115 END IF ;
1116 CLOSE oki_utl_pvt.g_bin_disp_lkup_csr ;
1117
1118 -- Get the current value
1119 l_curr_e_act_k := ( l_curr_b_act_k + l_curr_bklg_k_rwned +
1120 l_curr_k_rnwed + l_curr_new_bus ) -
1121 ( l_curr_k_exp_in_qtr + l_curr_trmn_k ) ;
1122
1123 -- Get the previous value
1124 l_prev_e_act_k := ( l_prev_b_act_k + l_prev_bklg_k_rwned +
1125 l_prev_k_rnwed + l_prev_new_bus ) -
1126 ( l_prev_k_exp_in_qtr + l_prev_trmn_k ) ;
1127
1128 l_loc := 'Opening cursor to determine current ' || l_measure_type || '.' ;
1129
1130 l_loc := 'Inserting / updating ' || l_measure_type || '.' ;
1131 -- Determine if the record is a new one or an existing one
1132 OPEN oki_load_rbk_pvt.g_rbk_csr ( oki_utl_pub.g_summary_build_date
1133 , rec_g_org_csr.authoring_org_id, oki_utl_pub.g_all_customer_id
1134 , oki_utl_pub.g_all_k_category_code
1135 , oki_load_rbk_pvt.g_e_act_k_code ) ;
1136 FETCH oki_load_rbk_pvt.g_rbk_csr INTO rec_g_rbk_csr ;
1137 IF oki_load_rbk_pvt.g_rbk_csr%NOTFOUND THEN
1138 l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;
1139 -- Insert the new record
1140 oki_load_rbk_pvt.ins_rnwl_bkng (
1141 p_summary_build_date => oki_utl_pub.g_summary_build_date
1142 , p_authoring_org_id => rec_g_org_csr.authoring_org_id
1143 , p_organization_name => rec_g_org_csr.organization_name
1144 , p_customer_party_id => oki_utl_pub.g_all_customer_id
1145 , p_customer_name => oki_utl_pub.g_all_customer_name
1146 , p_scs_code => oki_utl_pub.g_all_k_category_code
1147 , p_measure_code => oki_load_rbk_pvt.g_e_act_k_code
1148 , p_measure_code_meaning => l_bin_code_meaning
1149 , p_bin_code_seq => l_bin_code_seq
1150 , p_curr_base_contract_amount => l_curr_e_act_k
1151 , p_prev_base_contract_amount => l_prev_e_act_k
1152 , x_retcode => l_retcode ) ;
1153 IF l_retcode = '2' THEN
1154 -- Load failed, exit immediately.
1155 RAISE oki_utl_pub.g_excp_exit_immediate ;
1156 END IF ;
1157 ELSE
1161 p_curr_base_contract_amount => l_curr_e_act_k
1158 l_loc := 'Update the record -- ' || l_measure_type || '.' ;
1159 -- Update the existing record
1160 oki_load_rbk_pvt.upd_rnwl_bkng (
1162 , p_prev_base_contract_amount => l_prev_e_act_k
1163 , p_measure_code_meaning => l_bin_code_meaning
1164 , p_bin_code_seq => l_bin_code_seq
1165 , p_organization_name => rec_g_org_csr.organization_name
1166 , p_customer_name => oki_utl_pub.g_all_customer_name
1167 , p_rbk_rowid => rec_g_rbk_csr.rowid
1168 , x_retcode => l_retcode ) ;
1169
1170 IF l_retcode = '2' THEN
1171 -- Load failed, exit immediately.
1172 RAISE oki_utl_pub.g_excp_exit_immediate ;
1173 END IF ;
1174 END IF ;
1175 CLOSE oki_load_rbk_pvt.g_rbk_csr ;
1176
1177 END LOOP g_org_csr_loop ;
1178
1179 EXCEPTION
1180 WHEN oki_utl_pub.g_excp_exit_immediate THEN
1181 -- Do not log an error ; It has already been logged.
1182 -- Set return code to error
1183 x_retcode := '2' ;
1184
1185 WHEN NO_DATA_FOUND THEN
1186 l_sqlcode := SQLCODE ;
1187 l_sqlerrm := SQLERRM ;
1188
1189 -- Set return code TO error
1190 x_retcode := '2' ;
1191
1192 -- Log the location within the procedure where the error occurred
1193 fnd_message.set_name( application => 'OKI'
1194 , name => 'OKI_LOC_IN_PROG_FAILURE');
1195
1196 fnd_message.set_token( token => 'LOCATION'
1197 , value => l_loc);
1198
1199 fnd_file.put_line( which => fnd_file.log
1200 , buff => fnd_message.get);
1201
1202 fnd_file.put_line( which => fnd_file.log
1203 , buff => l_sqlcode || ' ' || l_sqlerrm );
1204
1205 WHEN OTHERS THEN
1206
1207 l_sqlcode := SQLCODE ;
1208 l_sqlerrm := SQLERRM ;
1209 -- Set return code TO error
1210 x_retcode := '2' ;
1211
1212 fnd_message.set_name( application => 'OKI'
1213 , name => 'OKI_UNEXPECTED_FAILURE');
1214
1215 fnd_message.set_token( token => 'OBJECT_NAME'
1216 , value => 'OKI_LOAD_RBK_PVT.CALC_RBK_DLTL1');
1217
1218 fnd_file.put_line( which => fnd_file.log
1219 , buff => fnd_message.get);
1220
1221 -- Log the location within the procedure where the error occurred
1222 fnd_message.set_name( application => 'OKI'
1223 , name => 'OKI_LOC_IN_PROG_FAILURE');
1224
1225 fnd_message.set_token( token => 'LOCATION'
1226 , value => l_loc);
1227
1228 fnd_file.put_line( which => fnd_file.log
1229 , buff => fnd_message.get);
1230
1231 fnd_file.put_line( which => fnd_file.log
1232 , buff => l_sqlcode||' '|| l_sqlerrm );
1233 END calc_rbk_dtl1 ;
1234
1235 --------------------------------------------------------------------------------
1236 --
1237 -- Procedure to calculate the renewal bookings at the top most levet.
1238 --
1239 --------------------------------------------------------------------------------
1240 PROCEDURE calc_rbk_sum
1241 (
1242 x_retcode OUT NOCOPY VARCHAR2
1243 ) IS
1244
1245 --
1246 -- Local variable declaration
1247 --
1248
1249 -- For capturing the return code, 0 = success, 1 = warning, 2 = error
1250 l_retcode VARCHAR2(1) := NULL ;
1251
1252 -- For error handling
1253 l_sqlcode VARCHAR2(100) := NULL ;
1254 l_sqlerrm VARCHAR2(1000) := NULL ;
1255
1256 -- Location within the program before the error was encountered.
1257 l_loc VARCHAR2(200) ;
1258
1259 l_measure_type VARCHAR2(60) := NULL ;
1260
1261 l_bin_code_meaning VARCHAR2(240) := NULL ;
1262 l_bin_code_seq NUMBER := NULL ;
1263
1264 rec_l_tactk_all_csr oki_utl_pvt.g_tactk_all_csr_row ;
1265 rec_l_k_exp_in_qtr_all_csr oki_utl_pvt.g_k_exp_in_qtr_all_csr_row ;
1266 rec_l_bin_disp_lkup_csr oki_utl_pvt.g_bin_disp_lkup_csr_row ;
1267
1268 -- Holds the current measure values for the renewal bookings bin
1269 l_curr_b_act_k NUMBER := 0 ;
1270 l_curr_k_exp_in_qtr NUMBER := 0 ;
1271 l_curr_bklg_k_to_rnw NUMBER := 0 ;
1272 l_curr_b_rnwl_oppty NUMBER := 0 ;
1273 l_curr_bklg_k_rwned NUMBER := 0 ;
1274 l_curr_k_rnwed NUMBER := 0 ;
1275 l_curr_new_bus NUMBER := 0 ;
1276 l_curr_trmn_k NUMBER := 0 ;
1277 l_curr_e_act_k NUMBER := 0 ;
1278
1279 -- Holds the previous measure values for the renewal bookings bin
1280 l_prev_b_act_k NUMBER := 0 ;
1281 l_prev_k_exp_in_qtr NUMBER := 0 ;
1282 l_prev_bklg_k_to_rnw NUMBER := 0 ;
1283 l_prev_b_rnwl_oppty NUMBER := 0 ;
1284 l_prev_bklg_k_rwned NUMBER := 0 ;
1285 l_prev_k_rnwed NUMBER := 0 ;
1286 l_prev_new_bus NUMBER := 0 ;
1287 l_prev_trmn_k NUMBER := 0 ;
1288 l_prev_e_act_k NUMBER := 0 ;
1289
1290
1291 -- Contracts that have should have been renewed (booked) by the start of
1295 (
1292 -- the quarter but were not. These contracts are the considered
1293 -- the backlog contracts to be renewed this quarter
1294 CURSOR l_bklg_k_to_rnw_csr
1296 p_qtr_start_date IN DATE
1297 ) IS
1298 SELECT NVL(SUM(base_contract_amount), 0) value
1299 , COUNT(shd.chr_id) contract_count
1300 FROM oki_sales_k_hdrs shd
1301 WHERE shd.is_new_yn IS NULL
1302 AND ( shd.date_canceled IS NULL
1303 OR shd.date_canceled >= p_qtr_start_date )
1304 AND ( shd.date_signed IS NULL
1305 OR shd.date_signed >= p_qtr_start_date )
1306 AND shd.start_date < p_qtr_start_date
1307 AND shd.base_contract_amount BETWEEN 0
1308 AND oki_utl_pub.g_contract_limit
1309 ;
1310 rec_l_bklg_k_to_rnw_csr l_bklg_k_to_rnw_csr%ROWTYPE ;
1311
1312 -- Contracts that have been renewed (booked) in this quarter but should have
1313 -- been booked before this quarter
1314 CURSOR l_bklg_k_rwned_csr
1315 (
1316 p_summary_build_date IN DATE
1317 , p_qtr_start_date IN DATE
1318 ) IS
1319 SELECT NVL(SUM(base_contract_amount), 0) value
1320 , COUNT(shd.chr_id) contract_count
1321 FROM oki_sales_k_hdrs shd
1322 WHERE shd.is_new_yn IS NULL
1323 AND shd.date_signed IS NOT NULL
1324 AND shd.date_approved IS NOT NULL
1325 AND shd.start_date < p_qtr_start_date
1326 AND GREATEST(shd.date_signed, shd.date_approved )
1327 BETWEEN p_qtr_start_date AND p_summary_build_date
1328 AND shd.base_contract_amount BETWEEN 0
1329 AND oki_utl_pub.g_contract_limit
1330 ;
1331 rec_l_bklg_k_rwned_csr l_bklg_k_rwned_csr%ROWTYPE ;
1332
1333 -- Contracts that have been renewed in the quarter
1334 CURSOR l_k_rnwed_csr
1335 (
1336 p_qtr_start_date IN DATE
1337 , p_qtr_end_date IN DATE
1338 ) IS
1339 SELECT NVL(SUM(base_contract_amount), 0) value
1340 , COUNT(shd.chr_id) contract_count
1341 FROM oki_sales_k_hdrs shd
1342 WHERE shd.is_new_yn IS NULL
1343 AND shd.date_signed IS NOT NULL
1344 AND shd.start_date BETWEEN p_qtr_start_date AND p_qtr_end_date
1345 AND GREATEST(shd.date_signed, shd.date_approved) <= p_qtr_end_date
1346 AND shd.base_contract_amount BETWEEN 0
1347 AND oki_utl_pub.g_contract_limit
1348 ;
1349 rec_l_k_rnwed_csr l_k_rnwed_csr%ROWTYPE ;
1350
1351 -- Contracts that became active between the start of quarter
1352 -- and the summary build date and are is not a result of a renewal
1353 CURSOR l_new_bus_csr
1354 (
1355 p_summary_build_date IN DATE
1356 , p_qtr_start_date IN DATE
1357 ) IS
1358 SELECT NVL(SUM(base_contract_amount), 0) value
1359 , COUNT(shd.chr_id) contract_count
1360 FROM oki_sales_k_hdrs shd
1361 WHERE shd.date_signed <= p_summary_build_date
1362 AND shd.date_approved <= p_summary_build_date
1363 AND shd.is_new_yn = 'Y'
1364 AND shd.start_date BETWEEN p_qtr_start_date
1365 AND p_summary_build_date
1366 AND ( shd.date_terminated IS NULL
1367 OR shd.date_terminated > p_summary_build_date )
1368 AND shd.base_contract_amount BETWEEN 0
1369 AND oki_utl_pub.g_contract_limit
1370 ;
1371 rec_l_new_bus_csr l_new_bus_csr%ROWTYPE ;
1372
1373 -- Contracts that were terminated between the start of the quarter and the
1374 -- summary build date
1375 CURSOR l_trmn_k_csr
1376 (
1377 p_summary_build_date IN DATE
1378 , p_qtr_start_date IN DATE
1379 ) IS
1380 SELECT NVL(SUM(base_contract_amount), 0) value
1381 , COUNT(shd.chr_id) contract_count
1382 FROM oki_sales_k_hdrs shd
1383 WHERE date_terminated BETWEEN p_qtr_start_date
1384 AND p_summary_build_date
1385 AND shd.date_signed IS NOT NULL
1386 AND shd.date_approved IS NOT NULL
1387 AND shd.base_contract_amount BETWEEN 0
1388 AND oki_utl_pub.g_contract_limit
1389 ;
1390 rec_l_trmn_k_csr l_trmn_k_csr%ROWTYPE ;
1391
1392 BEGIN
1393
1394 -- initialize return code to success
1395 l_retcode := '0';
1396
1397 --
1398 -- Process Beginning Active Contracts record
1399 --
1400
1401 l_measure_type := 'Beginning Active Contracts' ;
1402
1403 -- Get the bin display lookup values
1404 l_loc := 'Opening cursor to retrieve bin display lookup values for ' ||
1405 l_measure_type || '.' ;
1406 OPEN oki_utl_pvt.g_bin_disp_lkup_csr ( oki_load_rbk_pvt.g_bin_id
1407 , oki_load_rbk_pvt.g_b_act_k_code ) ;
1408 FETCH oki_utl_pvt.g_bin_disp_lkup_csr INTO rec_l_bin_disp_lkup_csr ;
1409 IF oki_utl_pvt.g_bin_disp_lkup_csr%FOUND THEN
1410 l_bin_code_meaning := rec_l_bin_disp_lkup_csr.bin_code_meaning ;
1411 l_bin_code_seq := rec_l_bin_disp_lkup_csr.bin_code_seq ;
1412 ELSE
1413 RAISE NO_DATA_FOUND ;
1414 END IF ;
1415 CLOSE oki_utl_pvt.g_bin_disp_lkup_csr ;
1416
1417
1418 -- Get the current value
1419 l_loc := 'Opening cursor to determine current ' || l_measure_type || '.' ;
1420 OPEN oki_utl_pvt.g_tactk_all_csr ( oki_utl_pub.g_glpr_qtr_start_date ) ;
1424 END IF ;
1421 FETCH oki_utl_pvt.g_tactk_all_csr INTO rec_l_tactk_all_csr ;
1422 IF oki_utl_pvt.g_tactk_all_csr%FOUND THEN
1423 l_curr_b_act_k := rec_l_tactk_all_csr.value ;
1425 CLOSE oki_utl_pvt.g_tactk_all_csr ;
1426
1427 -- Get the previous value
1428 l_loc := 'Opening Cursor to determine previous ' || l_measure_type || '.' ;
1429 OPEN oki_utl_pvt.g_tactk_all_csr ( oki_utl_pub.g_py_glpr_qtr_start_date ) ;
1430 FETCH oki_utl_pvt.g_tactk_all_csr INTO rec_l_tactk_all_csr ;
1431 IF oki_utl_pvt.g_tactk_all_csr%FOUND THEN
1432 l_prev_b_act_k := rec_l_tactk_all_csr.value ;
1433 END IF ;
1434 CLOSE oki_utl_pvt.g_tactk_all_csr ;
1435
1436 l_loc := 'Inserting / updating ' || l_measure_type || '.' ;
1437 -- Determine if the record is a new one or an existing one
1438 OPEN oki_load_rbk_pvt.g_rbk_csr ( oki_utl_pub.g_summary_build_date
1439 , oki_utl_pub.g_all_organization_id, oki_utl_pub.g_all_customer_id
1440 , oki_utl_pub.g_all_k_category_code
1441 , oki_load_rbk_pvt.g_b_act_k_code ) ;
1442 FETCH oki_load_rbk_pvt.g_rbk_csr INTO rec_g_rbk_csr ;
1443 IF oki_load_rbk_pvt.g_rbk_csr%NOTFOUND THEN
1444 l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;
1445 -- Insert the new record
1446 oki_load_rbk_pvt.ins_rnwl_bkng (
1447 p_summary_build_date => oki_utl_pub.g_summary_build_date
1448 , p_authoring_org_id => oki_utl_pub.g_all_organization_id
1449 , p_organization_name => oki_utl_pub.g_all_organization_name
1450 , p_customer_party_id => oki_utl_pub.g_all_customer_id
1451 , p_customer_name => oki_utl_pub.g_all_customer_name
1452 , p_scs_code => oki_utl_pub.g_all_k_category_code
1453 , p_measure_code => oki_load_rbk_pvt.g_b_act_k_code
1454 , p_measure_code_meaning => l_bin_code_meaning
1455 , p_bin_code_seq => l_bin_code_seq
1456 , p_curr_base_contract_amount => l_curr_b_act_k
1457 , p_prev_base_contract_amount => l_prev_b_act_k
1458 , x_retcode => l_retcode ) ;
1459 IF l_retcode = '2' THEN
1460 -- Load failed, exit immediately.
1461 RAISE oki_utl_pub.g_excp_exit_immediate ;
1462 END IF ;
1463 ELSE
1464 l_loc := 'Update the record -- ' || l_measure_type || '.' ;
1465 -- Update the existing record
1466 oki_load_rbk_pvt.upd_rnwl_bkng (
1467 p_curr_base_contract_amount => l_curr_b_act_k
1468 , p_prev_base_contract_amount => l_prev_b_act_k
1469 , p_measure_code_meaning => l_bin_code_meaning
1470 , p_bin_code_seq => l_bin_code_seq
1471 , p_organization_name => oki_utl_pub.g_all_organization_name
1472 , p_customer_name => oki_utl_pub.g_all_customer_name
1473 , p_rbk_rowid => rec_g_rbk_csr.rowid
1474 , x_retcode => l_retcode ) ;
1475
1476 IF l_retcode = '2' THEN
1477 -- Load failed, exit immediately.
1478 RAISE oki_utl_pub.g_excp_exit_immediate ;
1479 END IF ;
1480 END IF ;
1481 CLOSE oki_load_rbk_pvt.g_rbk_csr ;
1482
1483 --
1484 -- Process Expiring During Quarter record
1485 --
1486
1487 l_measure_type := 'Expiring During Quarter' ;
1488
1489 -- Get the bin display lookup values
1490 l_loc := 'Opening cursor to retrieve bin display lookup values for ' ||
1491 l_measure_type || '.' ;
1492 OPEN oki_utl_pvt.g_bin_disp_lkup_csr ( oki_load_rbk_pvt.g_bin_id
1493 , oki_load_rbk_pvt.g_exp_in_qtr_code ) ;
1494 FETCH oki_utl_pvt.g_bin_disp_lkup_csr INTO rec_l_bin_disp_lkup_csr ;
1495 IF oki_utl_pvt.g_bin_disp_lkup_csr%FOUND THEN
1496 l_bin_code_meaning := rec_l_bin_disp_lkup_csr.bin_code_meaning ;
1497 l_bin_code_seq := rec_l_bin_disp_lkup_csr.bin_code_seq ;
1498 ELSE
1499 RAISE NO_DATA_FOUND ;
1500 END IF ;
1501 CLOSE oki_utl_pvt.g_bin_disp_lkup_csr ;
1502
1503 -- Get the current value
1504 l_loc := 'Opening cursor to determine current ' || l_measure_type || '.' ;
1505 OPEN oki_utl_pvt.g_k_exp_in_qtr_all_csr (
1506 oki_utl_pub.g_glpr_qtr_start_date,
1507 oki_utl_pub.g_glpr_qtr_end_date ) ;
1508 FETCH oki_utl_pvt.g_k_exp_in_qtr_all_csr INTO rec_l_k_exp_in_qtr_all_csr ;
1509 IF oki_utl_pvt.g_k_exp_in_qtr_all_csr%FOUND THEN
1510 l_curr_k_exp_in_qtr := rec_l_k_exp_in_qtr_all_csr.value ;
1511 END IF ;
1512 CLOSE oki_utl_pvt.g_k_exp_in_qtr_all_csr ;
1513
1514 -- Get the previous value
1515 l_loc := 'Opening cursor to determine previous ' || l_measure_type || '.' ;
1516 OPEN oki_utl_pvt.g_k_exp_in_qtr_all_csr (
1517 oki_utl_pub.g_py_glpr_qtr_start_date,
1518 oki_utl_pub.g_py_glpr_qtr_end_date ) ;
1519 FETCH oki_utl_pvt.g_k_exp_in_qtr_all_csr INTO rec_l_k_exp_in_qtr_all_csr ;
1520 IF oki_utl_pvt.g_k_exp_in_qtr_all_csr%FOUND THEN
1521 l_prev_k_exp_in_qtr := rec_l_k_exp_in_qtr_all_csr.value ;
1522 END IF ;
1523 CLOSE oki_utl_pvt.g_k_exp_in_qtr_all_csr ;
1524
1525 l_loc := 'Inserting / updating ' || l_measure_type || '.' ;
1526 -- Determine if the record is a new one or an existing one
1527 OPEN oki_load_rbk_pvt.g_rbk_csr ( oki_utl_pub.g_summary_build_date
1528 , oki_utl_pub.g_all_organization_id, oki_utl_pub.g_all_customer_id
1529 , oki_utl_pub.g_all_k_category_code
1530 , oki_load_rbk_pvt.g_exp_in_qtr_code ) ;
1534 -- Insert the new record
1531 FETCH oki_load_rbk_pvt.g_rbk_csr INTO rec_g_rbk_csr ;
1532 IF oki_load_rbk_pvt.g_rbk_csr%NOTFOUND THEN
1533 l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;
1535 oki_load_rbk_pvt.ins_rnwl_bkng (
1536 p_summary_build_date => oki_utl_pub.g_summary_build_date
1537 , p_authoring_org_id => oki_utl_pub.g_all_organization_id
1538 , p_organization_name => oki_utl_pub.g_all_organization_name
1539 , p_customer_party_id => oki_utl_pub.g_all_customer_id
1540 , p_customer_name => oki_utl_pub.g_all_customer_name
1541 , p_scs_code => oki_utl_pub.g_all_k_category_code
1542 , p_measure_code => oki_load_rbk_pvt.g_exp_in_qtr_code
1543 , p_measure_code_meaning => l_bin_code_meaning
1544 , p_bin_code_seq => l_bin_code_seq
1545 , p_curr_base_contract_amount => l_curr_k_exp_in_qtr
1546 , p_prev_base_contract_amount => l_prev_k_exp_in_qtr
1547 , x_retcode => l_retcode ) ;
1548 IF l_retcode = '2' THEN
1549 -- Load failed, exit immediately.
1550 RAISE oki_utl_pub.g_excp_exit_immediate ;
1551 END IF ;
1552 ELSE
1553 l_loc := 'Update the record -- ' || l_measure_type || '.' ;
1554 -- Update the existing record
1555 oki_load_rbk_pvt.upd_rnwl_bkng (
1556 p_curr_base_contract_amount => l_curr_k_exp_in_qtr
1557 , p_prev_base_contract_amount => l_prev_k_exp_in_qtr
1558 , p_measure_code_meaning => l_bin_code_meaning
1559 , p_bin_code_seq => l_bin_code_seq
1560 , p_organization_name => oki_utl_pub.g_all_organization_name
1561 , p_customer_name => oki_utl_pub.g_all_customer_name
1562 , p_rbk_rowid => rec_g_rbk_csr.rowid
1563 , x_retcode => l_retcode ) ;
1564
1565 IF l_retcode = '2' THEN
1566 -- Load failed, exit immediately.
1567 RAISE oki_utl_pub.g_excp_exit_immediate ;
1568 END IF ;
1569 END IF ;
1570 CLOSE oki_load_rbk_pvt.g_rbk_csr ;
1571
1572 --
1573 -- Process Backlog Renewals record
1574 --
1575
1576 l_measure_type := 'Backlog Renewals' ;
1577
1578 -- Get the bin display lookup values
1579 l_loc := 'Opening cursor to retrieve bin display lookup values for ' ||
1580 l_measure_type || '.' ;
1581 OPEN oki_utl_pvt.g_bin_disp_lkup_csr ( oki_load_rbk_pvt.g_bin_id
1582 , oki_load_rbk_pvt.g_bklg_k_to_rnw_code ) ;
1583 FETCH oki_utl_pvt.g_bin_disp_lkup_csr INTO rec_l_bin_disp_lkup_csr ;
1584 IF oki_utl_pvt.g_bin_disp_lkup_csr%FOUND THEN
1585 l_bin_code_meaning := rec_l_bin_disp_lkup_csr.bin_code_meaning ;
1586 l_bin_code_seq := rec_l_bin_disp_lkup_csr.bin_code_seq ;
1587 ELSE
1588 RAISE NO_DATA_FOUND ;
1589 END IF ;
1590 CLOSE oki_utl_pvt.g_bin_disp_lkup_csr ;
1591
1592 -- Get the current value
1593 l_loc := 'Opening cursor to determine current ' || l_measure_type || '.' ;
1594 OPEN l_bklg_k_to_rnw_csr ( oki_utl_pub.g_glpr_qtr_start_date ) ;
1595 FETCH l_bklg_k_to_rnw_csr INTO rec_l_bklg_k_to_rnw_csr ;
1596 IF l_bklg_k_to_rnw_csr%FOUND THEN
1597 l_curr_bklg_k_to_rnw := rec_l_bklg_k_to_rnw_csr.value ;
1598 END IF ;
1599 CLOSE l_bklg_k_to_rnw_csr ;
1600
1601 -- Get the previous value
1602 l_loc := 'Opening Cursor to determine previous ' || l_measure_type || '.' ;
1603 OPEN l_bklg_k_to_rnw_csr ( oki_utl_pub.g_py_glpr_qtr_start_date ) ;
1604 FETCH l_bklg_k_to_rnw_csr INTO rec_l_bklg_k_to_rnw_csr ;
1605 IF l_bklg_k_to_rnw_csr%FOUND THEN
1606 l_prev_bklg_k_to_rnw := rec_l_bklg_k_to_rnw_csr.value ;
1607 END IF ;
1608 CLOSE l_bklg_k_to_rnw_csr ;
1609
1610 l_loc := 'Inserting / updating ' || l_measure_type || '.' ;
1611 -- Determine if the record is a new one or an existing one
1612 OPEN oki_load_rbk_pvt.g_rbk_csr ( oki_utl_pub.g_summary_build_date
1613 , oki_utl_pub.g_all_organization_id, oki_utl_pub.g_all_customer_id
1614 , oki_utl_pub.g_all_k_category_code
1615 , oki_load_rbk_pvt.g_bklg_k_to_rnw_code ) ;
1616 FETCH oki_load_rbk_pvt.g_rbk_csr INTO rec_g_rbk_csr ;
1617 IF oki_load_rbk_pvt.g_rbk_csr%NOTFOUND THEN
1618 l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;
1619 -- Insert the new record
1620 oki_load_rbk_pvt.ins_rnwl_bkng (
1621 p_summary_build_date => oki_utl_pub.g_summary_build_date
1622 , p_authoring_org_id => oki_utl_pub.g_all_organization_id
1623 , p_organization_name => oki_utl_pub.g_all_organization_name
1624 , p_customer_party_id => oki_utl_pub.g_all_customer_id
1625 , p_customer_name => oki_utl_pub.g_all_customer_name
1626 , p_scs_code => oki_utl_pub.g_all_k_category_code
1627 , p_measure_code => oki_load_rbk_pvt.g_bklg_k_to_rnw_code
1628 , p_measure_code_meaning => l_bin_code_meaning
1629 , p_bin_code_seq => l_bin_code_seq
1630 , p_curr_base_contract_amount => l_curr_bklg_k_to_rnw
1631 , p_prev_base_contract_amount => l_prev_bklg_k_to_rnw
1632 , x_retcode => l_retcode ) ;
1633 IF l_retcode = '2' THEN
1634 -- Load failed, exit immediately.
1635 RAISE oki_utl_pub.g_excp_exit_immediate ;
1636 END IF ;
1637 ELSE
1641 p_curr_base_contract_amount => l_curr_bklg_k_to_rnw
1638 l_loc := 'Update the record -- ' || l_measure_type || '.' ;
1639 -- Update the existing record
1640 oki_load_rbk_pvt.upd_rnwl_bkng (
1642 , p_prev_base_contract_amount => l_prev_bklg_k_to_rnw
1643 , p_measure_code_meaning => l_bin_code_meaning
1644 , p_bin_code_seq => l_bin_code_seq
1645 , p_organization_name => oki_utl_pub.g_all_organization_name
1646 , p_customer_name => oki_utl_pub.g_all_customer_name
1647 , p_rbk_rowid => rec_g_rbk_csr.rowid
1648 , x_retcode => l_retcode ) ;
1649
1650 IF l_retcode = '2' THEN
1651 -- Load failed, exit immediately.
1652 RAISE oki_utl_pub.g_excp_exit_immediate ;
1653 END IF ;
1654 END IF ;
1655 CLOSE oki_load_rbk_pvt.g_rbk_csr ;
1656
1657 --
1658 -- Process Beginning Renewal Opportunity record
1659 --
1660
1661 l_measure_type := 'Beginning Renewal Opportunity' ;
1662
1663 -- Get the bin display lookup values
1664 l_loc := 'Opening cursor to retrieve bin display lookup values for ' ||
1665 l_measure_type || '.' ;
1666 OPEN oki_utl_pvt.g_bin_disp_lkup_csr ( oki_load_rbk_pvt.g_bin_id
1667 , oki_load_rbk_pvt.g_b_rnwl_oppty_code ) ;
1668 FETCH oki_utl_pvt.g_bin_disp_lkup_csr INTO rec_l_bin_disp_lkup_csr ;
1669 IF oki_utl_pvt.g_bin_disp_lkup_csr%FOUND THEN
1670 l_bin_code_meaning := rec_l_bin_disp_lkup_csr.bin_code_meaning ;
1671 l_bin_code_seq := rec_l_bin_disp_lkup_csr.bin_code_seq ;
1672 ELSE
1673 RAISE NO_DATA_FOUND ;
1674 END IF ;
1675 CLOSE oki_utl_pvt.g_bin_disp_lkup_csr ;
1676
1677 l_curr_b_rnwl_oppty := l_curr_k_exp_in_qtr + l_curr_bklg_k_to_rnw ;
1678 l_prev_b_rnwl_oppty := l_prev_k_exp_in_qtr + l_prev_bklg_k_to_rnw ;
1679
1680 l_loc := 'Inserting / updating ' || l_measure_type || '.' ;
1681 -- Determine if the record is a new one or an existing one
1682 OPEN oki_load_rbk_pvt.g_rbk_csr ( oki_utl_pub.g_summary_build_date
1683 , oki_utl_pub.g_all_organization_id, oki_utl_pub.g_all_customer_id
1684 , oki_utl_pub.g_all_k_category_code
1685 , oki_load_rbk_pvt.g_b_rnwl_oppty_code ) ;
1686 FETCH oki_load_rbk_pvt.g_rbk_csr INTO rec_g_rbk_csr ;
1687 IF oki_load_rbk_pvt.g_rbk_csr%NOTFOUND THEN
1688 l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;
1689 -- Insert the new record
1690 oki_load_rbk_pvt.ins_rnwl_bkng (
1691 p_summary_build_date => oki_utl_pub.g_summary_build_date
1692 , p_authoring_org_id => oki_utl_pub.g_all_organization_id
1693 , p_organization_name => oki_utl_pub.g_all_organization_name
1694 , p_customer_party_id => oki_utl_pub.g_all_customer_id
1695 , p_customer_name => oki_utl_pub.g_all_customer_name
1696 , p_scs_code => oki_utl_pub.g_all_k_category_code
1697 , p_measure_code => oki_load_rbk_pvt.g_b_rnwl_oppty_code
1698 , p_measure_code_meaning => l_bin_code_meaning
1699 , p_bin_code_seq => l_bin_code_seq
1700 , p_curr_base_contract_amount => l_curr_b_rnwl_oppty
1701 , p_prev_base_contract_amount => l_prev_b_rnwl_oppty
1702 , x_retcode => l_retcode ) ;
1703
1704 IF l_retcode = '2' THEN
1705 -- Load failed, exit immediately.
1706 RAISE oki_utl_pub.g_excp_exit_immediate ;
1707 END IF ;
1708 ELSE
1709 l_loc := 'Update the record -- ' || l_measure_type || '.' ;
1710 -- Update the existing record
1711 oki_load_rbk_pvt.upd_rnwl_bkng (
1712 p_curr_base_contract_amount => l_curr_b_rnwl_oppty
1713 , p_prev_base_contract_amount => l_prev_b_rnwl_oppty
1714 , p_measure_code_meaning => l_bin_code_meaning
1715 , p_bin_code_seq => l_bin_code_seq
1716 , p_organization_name => oki_utl_pub.g_all_organization_name
1717 , p_customer_name => oki_utl_pub.g_all_customer_name
1718 , p_rbk_rowid => rec_g_rbk_csr.rowid
1719 , x_retcode => l_retcode ) ;
1720
1721 IF l_retcode = '2' THEN
1722 -- Load failed, exit immediately.
1723 RAISE oki_utl_pub.g_excp_exit_immediate ;
1724 END IF ;
1725 END IF ;
1726 CLOSE oki_load_rbk_pvt.g_rbk_csr ;
1727
1728 --
1729 -- Process Quarter Activity record
1730 --
1731
1732 -- Get the bin display lookup values
1733 l_loc := 'Opening cursor to retrieve bin display lookup values for ' ||
1734 l_measure_type || '.' ;
1735 OPEN oki_utl_pvt.g_bin_disp_lkup_csr ( oki_load_rbk_pvt.g_bin_id
1736 , oki_load_rbk_pvt.g_qtr_actvty_code ) ;
1737 FETCH oki_utl_pvt.g_bin_disp_lkup_csr INTO rec_l_bin_disp_lkup_csr ;
1738 IF oki_utl_pvt.g_bin_disp_lkup_csr%FOUND THEN
1739 l_bin_code_meaning := rec_l_bin_disp_lkup_csr.bin_code_meaning ;
1740 l_bin_code_seq := rec_l_bin_disp_lkup_csr.bin_code_seq ;
1741 ELSE
1742 RAISE NO_DATA_FOUND ;
1743 END IF ;
1744 CLOSE oki_utl_pvt.g_bin_disp_lkup_csr ;
1745
1746 l_loc := 'Inserting / updating ' || l_measure_type || '.' ;
1747 -- Determine if the record is a new one or an existing one
1748 OPEN oki_load_rbk_pvt.g_rbk_csr ( oki_utl_pub.g_summary_build_date,
1752 IF oki_load_rbk_pvt.g_rbk_csr%NOTFOUND THEN
1749 oki_utl_pub.g_all_organization_id, oki_utl_pub.g_all_customer_id,
1750 oki_utl_pub.g_all_k_category_code, oki_load_rbk_pvt.g_qtr_actvty_code ) ;
1751 FETCH oki_load_rbk_pvt.g_rbk_csr INTO rec_g_rbk_csr ;
1753 l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;
1754 -- Insert the new record
1755 oki_load_rbk_pvt.ins_rnwl_bkng (
1756 p_summary_build_date => oki_utl_pub.g_summary_build_date
1757 , p_authoring_org_id => oki_utl_pub.g_all_organization_id
1758 , p_organization_name => oki_utl_pub.g_all_organization_name
1759 , p_customer_party_id => oki_utl_pub.g_all_customer_id
1760 , p_customer_name => oki_utl_pub.g_all_customer_name
1761 , p_scs_code => oki_utl_pub.g_all_k_category_code
1762 , p_measure_code => oki_load_rbk_pvt.g_qtr_actvty_code
1763 , p_measure_code_meaning => l_bin_code_meaning
1764 , p_bin_code_seq => l_bin_code_seq
1765 , p_curr_base_contract_amount => NULL
1766 , p_prev_base_contract_amount => NULL
1767 , x_retcode => l_retcode ) ;
1768 IF l_retcode = '2' THEN
1769 -- Load failed, exit immediately.
1770 RAISE oki_utl_pub.g_excp_exit_immediate ;
1771 END IF ;
1772 ELSE
1773 l_loc := 'Update the record -- ' || l_measure_type || '.' ;
1774 -- Update the existing record
1775 oki_load_rbk_pvt.upd_rnwl_bkng (
1776 p_curr_base_contract_amount => NULL
1777 , p_prev_base_contract_amount => NULL
1778 , p_measure_code_meaning => l_bin_code_meaning
1779 , p_bin_code_seq => l_bin_code_seq
1780 , p_organization_name => oki_utl_pub.g_all_organization_name
1781 , p_customer_name => oki_utl_pub.g_all_customer_name
1782 , p_rbk_rowid => rec_g_rbk_csr.rowid
1783 , x_retcode => l_retcode ) ;
1784
1785 IF l_retcode = '2' THEN
1786 -- Load failed, exit immediately.
1787 RAISE oki_utl_pub.g_excp_exit_immediate ;
1788 END IF ;
1789 END IF ;
1790 CLOSE oki_load_rbk_pvt.g_rbk_csr ;
1791
1792 --
1793 -- Process Backlog Contracts Renewed record
1794 --
1795
1796 l_measure_type := 'Backlog Contracts Renewed' ;
1797
1798 -- Get the bin display lookup values
1799 l_loc := 'Opening cursor to retrieve bin display lookup values for ' ||
1800 l_measure_type || '.' ;
1801 OPEN oki_utl_pvt.g_bin_disp_lkup_csr ( oki_load_rbk_pvt.g_bin_id
1802 , oki_load_rbk_pvt.g_bklg_k_rnwed_code ) ;
1803 FETCH oki_utl_pvt.g_bin_disp_lkup_csr INTO rec_l_bin_disp_lkup_csr ;
1804 IF oki_utl_pvt.g_bin_disp_lkup_csr%FOUND THEN
1805 l_bin_code_meaning := rec_l_bin_disp_lkup_csr.bin_code_meaning ;
1806 l_bin_code_seq := rec_l_bin_disp_lkup_csr.bin_code_seq ;
1807 ELSE
1808 RAISE NO_DATA_FOUND ;
1809 END IF ;
1810 CLOSE oki_utl_pvt.g_bin_disp_lkup_csr ;
1811
1812 -- Get the current value
1813 l_loc := 'Opening cursor to determine current ' || l_measure_type || '.' ;
1814 OPEN l_bklg_k_rwned_csr( oki_utl_pub.g_summary_build_date,
1815 oki_utl_pub.g_glpr_qtr_start_date ) ;
1816 FETCH l_bklg_k_rwned_csr INTO rec_l_bklg_k_rwned_csr ;
1817 IF l_bklg_k_rwned_csr%FOUND THEN
1818 l_curr_bklg_k_rwned := rec_l_bklg_k_rwned_csr.value ;
1819 END IF ;
1820 CLOSE l_bklg_k_rwned_csr;
1821
1822 -- Get the previous value
1823 OPEN l_bklg_k_rwned_csr( oki_utl_pub.g_py_summary_build_date,
1824 oki_utl_pub.g_py_glpr_qtr_start_date ) ;
1825 FETCH l_bklg_k_rwned_csr INTO rec_l_bklg_k_rwned_csr ;
1826 IF l_bklg_k_rwned_csr%FOUND THEN
1827 l_prev_bklg_k_rwned := rec_l_bklg_k_rwned_csr.value ;
1828 END IF ;
1829 CLOSE l_bklg_k_rwned_csr ;
1830
1831 l_loc := 'Inserting / updating ' || l_measure_type || '.' ;
1832 -- Determine if the record is a new one or an existing one
1833 OPEN oki_load_rbk_pvt.g_rbk_csr ( oki_utl_pub.g_summary_build_date
1834 , oki_utl_pub.g_all_organization_id
1835 , oki_utl_pub.g_all_customer_id, oki_utl_pub.g_all_k_category_code
1836 , oki_load_rbk_pvt.g_bklg_k_rnwed_code ) ;
1837 FETCH oki_load_rbk_pvt.g_rbk_csr INTO rec_g_rbk_csr ;
1838 IF oki_load_rbk_pvt.g_rbk_csr%NOTFOUND THEN
1839 l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;
1840 -- Insert the new record
1841 oki_load_rbk_pvt.ins_rnwl_bkng (
1842 p_summary_build_date => oki_utl_pub.g_summary_build_date
1843 , p_authoring_org_id => oki_utl_pub.g_all_organization_id
1844 , p_organization_name => oki_utl_pub.g_all_organization_name
1845 , p_customer_party_id => oki_utl_pub.g_all_customer_id
1846 , p_customer_name => oki_utl_pub.g_all_customer_name
1847 , p_scs_code => oki_utl_pub.g_all_k_category_code
1848 , p_measure_code => oki_load_rbk_pvt.g_bklg_k_rnwed_code
1849 , p_measure_code_meaning => l_bin_code_meaning
1850 , p_bin_code_seq => l_bin_code_seq
1851 , p_curr_base_contract_amount => l_curr_bklg_k_rwned
1852 , p_prev_base_contract_amount => l_prev_bklg_k_rwned
1853 , x_retcode => l_retcode ) ;
1854 IF l_retcode = '2' THEN
1858 ELSE
1855 -- Load failed, exit immediately.
1856 RAISE oki_utl_pub.g_excp_exit_immediate ;
1857 END IF ;
1859 l_loc := 'Update the record -- ' || l_measure_type || '.' ;
1860 -- Update the existing record
1861 oki_load_rbk_pvt.upd_rnwl_bkng (
1862 p_curr_base_contract_amount => l_curr_bklg_k_rwned
1863 , p_prev_base_contract_amount => l_prev_bklg_k_rwned
1864 , p_measure_code_meaning => l_bin_code_meaning
1865 , p_bin_code_seq => l_bin_code_seq
1866 , p_organization_name => oki_utl_pub.g_all_organization_name
1867 , p_customer_name => oki_utl_pub.g_all_customer_name
1868 , p_rbk_rowid => rec_g_rbk_csr.rowid
1869 , x_retcode => l_retcode ) ;
1870
1871 IF l_retcode = '2' THEN
1872 -- Load failed, exit immediately.
1873 RAISE oki_utl_pub.g_excp_exit_immediate ;
1874 END IF ;
1875 END IF ;
1876 CLOSE oki_load_rbk_pvt.g_rbk_csr ;
1877
1878 --
1879 -- Process Contracts Renewed record
1880 --
1881
1882 l_measure_type := 'Contracts Renewed' ;
1883
1884 -- Get the bin display lookup values
1885 l_loc := 'Opening cursor to retrieve bin display lookup values for ' ||
1886 l_measure_type || '.' ;
1887 OPEN oki_utl_pvt.g_bin_disp_lkup_csr ( oki_load_rbk_pvt.g_bin_id
1888 , oki_load_rbk_pvt.g_k_rnwed_code ) ;
1889 FETCH oki_utl_pvt.g_bin_disp_lkup_csr INTO rec_l_bin_disp_lkup_csr ;
1890 IF oki_utl_pvt.g_bin_disp_lkup_csr%FOUND THEN
1891 l_bin_code_meaning := rec_l_bin_disp_lkup_csr.bin_code_meaning ;
1892 l_bin_code_seq := rec_l_bin_disp_lkup_csr.bin_code_seq ;
1893 ELSE
1894 RAISE NO_DATA_FOUND ;
1895 END IF ;
1896 CLOSE oki_utl_pvt.g_bin_disp_lkup_csr ;
1897
1898 -- Get the current value
1899 l_loc := 'Opening cursor to determine current ' || l_measure_type || '.' ;
1900 OPEN l_k_rnwed_csr( oki_utl_pub.g_glpr_qtr_start_date,
1901 oki_utl_pub.g_glpr_qtr_end_date ) ;
1902 FETCH l_k_rnwed_csr INTO rec_l_k_rnwed_csr ;
1903 IF l_k_rnwed_csr%FOUND THEN
1904 l_curr_k_rnwed := rec_l_k_rnwed_csr.value ;
1905 END IF ;
1906 CLOSE l_k_rnwed_csr ;
1907
1908 -- Get the previous value
1909 l_loc := 'Opening Cursor to determine previous ' || l_measure_type || '.' ;
1910 OPEN l_k_rnwed_csr( oki_utl_pub.g_py_glpr_qtr_start_date,
1911 oki_utl_pub.g_py_glpr_qtr_end_date ) ;
1912 FETCH l_k_rnwed_csr INTO rec_l_k_rnwed_csr ;
1913 IF l_k_rnwed_csr%FOUND THEN
1914 l_prev_k_rnwed := rec_l_k_rnwed_csr.value ;
1915 END IF ;
1916 CLOSE l_k_rnwed_csr ;
1917
1918 l_loc := 'Inserting / updating ' || l_measure_type || '.' ;
1919 -- Determine if the record is a new one or an existing one
1920 OPEN oki_load_rbk_pvt.g_rbk_csr ( oki_utl_pub.g_summary_build_date,
1921 oki_utl_pub.g_all_organization_id, oki_utl_pub.g_all_customer_id,
1922 oki_utl_pub.g_all_k_category_code, oki_load_rbk_pvt.g_k_rnwed_code ) ;
1923 FETCH oki_load_rbk_pvt.g_rbk_csr INTO rec_g_rbk_csr ;
1924 IF oki_load_rbk_pvt.g_rbk_csr%NOTFOUND THEN
1925 l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;
1926 -- Insert the new record
1927 oki_load_rbk_pvt.ins_rnwl_bkng (
1928 p_summary_build_date => oki_utl_pub.g_summary_build_date
1929 , p_authoring_org_id => oki_utl_pub.g_all_organization_id
1930 , p_organization_name => oki_utl_pub.g_all_organization_name
1931 , p_customer_party_id => oki_utl_pub.g_all_customer_id
1932 , p_customer_name => oki_utl_pub.g_all_customer_name
1933 , p_scs_code => oki_utl_pub.g_all_k_category_code
1934 , p_measure_code => oki_load_rbk_pvt.g_k_rnwed_code
1935 , p_measure_code_meaning => l_bin_code_meaning
1936 , p_bin_code_seq => l_bin_code_seq
1937 , p_curr_base_contract_amount => l_curr_k_rnwed
1938 , p_prev_base_contract_amount => l_prev_k_rnwed
1939 , x_retcode => l_retcode ) ;
1940 IF l_retcode = '2' THEN
1941 -- Load failed, exit immediately.
1942 RAISE oki_utl_pub.g_excp_exit_immediate ;
1943 END IF ;
1944 ELSE
1945 l_loc := 'Update the record -- ' || l_measure_type || '.' ;
1946 -- Update the existing record
1947 oki_load_rbk_pvt.upd_rnwl_bkng (
1948 p_curr_base_contract_amount => l_curr_k_rnwed
1949 , p_prev_base_contract_amount => l_prev_k_rnwed
1950 , p_measure_code_meaning => l_bin_code_meaning
1951 , p_bin_code_seq => l_bin_code_seq
1952 , p_organization_name => oki_utl_pub.g_all_organization_name
1953 , p_customer_name => oki_utl_pub.g_all_customer_name
1954 , p_rbk_rowid => rec_g_rbk_csr.rowid
1955 , x_retcode => l_retcode ) ;
1956
1957 IF l_retcode = '2' THEN
1958 -- Load failed, exit immediately.
1959 RAISE oki_utl_pub.g_excp_exit_immediate ;
1960 END IF ;
1961 END IF ;
1962 CLOSE oki_load_rbk_pvt.g_rbk_csr ;
1963
1964 --
1965 -- Process New Business record
1966 --
1967
1968 l_measure_type := 'New Business' ;
1969
1970 -- Get the bin display lookup values
1974 , oki_load_rbk_pvt.g_new_bsn_code ) ;
1971 l_loc := 'Opening cursor to retrieve bin display lookup values for ' ||
1972 l_measure_type || '.' ;
1973 OPEN oki_utl_pvt.g_bin_disp_lkup_csr ( oki_load_rbk_pvt.g_bin_id
1975 FETCH oki_utl_pvt.g_bin_disp_lkup_csr INTO rec_l_bin_disp_lkup_csr ;
1976 IF oki_utl_pvt.g_bin_disp_lkup_csr%FOUND THEN
1977 l_bin_code_meaning := rec_l_bin_disp_lkup_csr.bin_code_meaning ;
1978 l_bin_code_seq := rec_l_bin_disp_lkup_csr.bin_code_seq ;
1979 ELSE
1980 RAISE NO_DATA_FOUND ;
1981 END IF ;
1982 CLOSE oki_utl_pvt.g_bin_disp_lkup_csr ;
1983
1984 -- Get the current value
1985 l_loc := 'Opening cursor to determine current ' || l_measure_type || '.' ;
1986 OPEN l_new_bus_csr( oki_utl_pub.g_summary_build_date,
1987 oki_utl_pub.g_glpr_qtr_start_date ) ;
1988 FETCH l_new_bus_csr INTO rec_l_new_bus_csr ;
1989 IF l_new_bus_csr%FOUND THEN
1990 l_curr_new_bus := rec_l_new_bus_csr.value ;
1991 END IF ;
1992 CLOSE l_new_bus_csr ;
1993
1994 -- Get the previous value
1995 l_loc := 'Opening Cursor to determine previous ' || l_measure_type || '.' ;
1996 OPEN l_new_bus_csr( oki_utl_pub.g_py_summary_build_date,
1997 oki_utl_pub.g_py_glpr_qtr_start_date ) ;
1998 FETCH l_new_bus_csr INTO rec_l_new_bus_csr ;
1999 IF l_new_bus_csr%FOUND THEN
2000 l_prev_new_bus := rec_l_new_bus_csr.value ;
2001 END IF ;
2002 CLOSE l_new_bus_csr ;
2003
2004 l_loc := 'Inserting / updating ' || l_measure_type || '.' ;
2005 -- Determine if the record is a new one or an existing one
2006 OPEN oki_load_rbk_pvt.g_rbk_csr ( oki_utl_pub.g_summary_build_date
2007 , oki_utl_pub.g_all_organization_id, oki_utl_pub.g_all_customer_id
2008 , oki_utl_pub.g_all_k_category_code
2009 , oki_load_rbk_pvt.g_new_bsn_code ) ;
2010 FETCH oki_load_rbk_pvt.g_rbk_csr INTO rec_g_rbk_csr ;
2011 IF oki_load_rbk_pvt.g_rbk_csr%NOTFOUND THEN
2012 l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;
2013 -- Insert the new record
2014 oki_load_rbk_pvt.ins_rnwl_bkng (
2015 p_summary_build_date => oki_utl_pub.g_summary_build_date
2016 , p_authoring_org_id => oki_utl_pub.g_all_organization_id
2017 , p_organization_name => oki_utl_pub.g_all_organization_name
2018 , p_customer_party_id => oki_utl_pub.g_all_customer_id
2019 , p_customer_name => oki_utl_pub.g_all_customer_name
2020 , p_scs_code => oki_utl_pub.g_all_k_category_code
2021 , p_measure_code => oki_load_rbk_pvt.g_new_bsn_code
2022 , p_measure_code_meaning => l_bin_code_meaning
2023 , p_bin_code_seq => l_bin_code_seq
2024 , p_curr_base_contract_amount => l_curr_new_bus
2025 , p_prev_base_contract_amount => l_prev_new_bus
2026 , x_retcode => l_retcode ) ;
2027 IF l_retcode = '2' THEN
2028 -- Load failed, exit immediately.
2029 RAISE oki_utl_pub.g_excp_exit_immediate ;
2030 END IF ;
2031 ELSE
2032 l_loc := 'Update the record -- ' || l_measure_type || '.' ;
2033 -- Update the existing record
2034 oki_load_rbk_pvt.upd_rnwl_bkng (
2035 p_curr_base_contract_amount => l_curr_new_bus
2036 , p_prev_base_contract_amount => l_prev_new_bus
2037 , p_measure_code_meaning => l_bin_code_meaning
2038 , p_bin_code_seq => l_bin_code_seq
2039 , p_organization_name => oki_utl_pub.g_all_organization_name
2040 , p_customer_name => oki_utl_pub.g_all_customer_name
2041 , p_rbk_rowid => rec_g_rbk_csr.rowid
2042 , x_retcode => l_retcode ) ;
2043
2044 IF l_retcode = '2' THEN
2045 -- Load failed, exit immediately.
2046 RAISE oki_utl_pub.g_excp_exit_immediate ;
2047 END IF ;
2048 END IF ;
2049 CLOSE oki_load_rbk_pvt.g_rbk_csr ;
2050
2051 --
2052 -- Process Terminated Contracts record
2053 --
2054
2055 l_measure_type := 'Terminated Contracts' ;
2056
2057 -- Get the bin display lookup values
2058 l_loc := 'Opening cursor to retrieve bin display lookup values for ' ||
2059 l_measure_type || '.' ;
2060 OPEN oki_utl_pvt.g_bin_disp_lkup_csr ( oki_load_rbk_pvt.g_bin_id
2061 , oki_load_rbk_pvt.g_trmn_k_code ) ;
2062 FETCH oki_utl_pvt.g_bin_disp_lkup_csr INTO rec_l_bin_disp_lkup_csr ;
2063 IF oki_utl_pvt.g_bin_disp_lkup_csr%FOUND THEN
2064 l_bin_code_meaning := rec_l_bin_disp_lkup_csr.bin_code_meaning ;
2065 l_bin_code_seq := rec_l_bin_disp_lkup_csr.bin_code_seq ;
2066 ELSE
2067 RAISE NO_DATA_FOUND ;
2068 END IF ;
2069 CLOSE oki_utl_pvt.g_bin_disp_lkup_csr ;
2070
2071 -- Get the current value
2072 l_loc := 'Opening cursor to determine current ' || l_measure_type || '.' ;
2073 OPEN l_trmn_k_csr( oki_utl_pub.g_summary_build_date,
2074 oki_utl_pub.g_glpr_qtr_start_date ) ;
2075 FETCH l_trmn_k_csr INTO rec_l_trmn_k_csr ;
2076 IF l_trmn_k_csr%FOUND THEN
2077 l_curr_trmn_k := rec_l_trmn_k_csr.value ;
2078 END IF ;
2079 CLOSE l_trmn_k_csr ;
2080
2081 -- Get the previous value
2082 l_loc := 'Opening Cursor to determine previous ' || l_measure_type || '.' ;
2086 IF l_trmn_k_csr%FOUND THEN
2083 OPEN l_trmn_k_csr( oki_utl_pub.g_py_summary_build_date,
2084 oki_utl_pub.g_py_glpr_qtr_start_date ) ;
2085 FETCH l_trmn_k_csr INTO rec_l_trmn_k_csr ;
2087 l_prev_trmn_k := rec_l_trmn_k_csr.value ;
2088 END IF ;
2089 CLOSE l_trmn_k_csr ;
2090
2091 l_loc := 'Inserting / updating ' || l_measure_type || '.' ;
2092 -- Determine if the record is a new one or an existing one
2093 OPEN oki_load_rbk_pvt.g_rbk_csr ( oki_utl_pub.g_summary_build_date
2094 , oki_utl_pub.g_all_organization_id, oki_utl_pub.g_all_customer_id
2095 , oki_utl_pub.g_all_k_category_code
2096 , oki_load_rbk_pvt.g_trmn_k_code ) ;
2097 FETCH oki_load_rbk_pvt.g_rbk_csr INTO rec_g_rbk_csr ;
2098 IF oki_load_rbk_pvt.g_rbk_csr%NOTFOUND THEN
2099 l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;
2100 -- Insert the new record
2101 oki_load_rbk_pvt.ins_rnwl_bkng (
2102 p_summary_build_date => oki_utl_pub.g_summary_build_date
2103 , p_authoring_org_id => oki_utl_pub.g_all_organization_id
2104 , p_organization_name => oki_utl_pub.g_all_organization_name
2105 , p_customer_party_id => oki_utl_pub.g_all_customer_id
2106 , p_customer_name => oki_utl_pub.g_all_customer_name
2107 , p_scs_code => oki_utl_pub.g_all_k_category_code
2108 , p_measure_code => oki_load_rbk_pvt.g_trmn_k_code
2109 , p_measure_code_meaning => l_bin_code_meaning
2110 , p_bin_code_seq => l_bin_code_seq
2111 , p_curr_base_contract_amount => l_curr_trmn_k
2112 , p_prev_base_contract_amount => l_prev_trmn_k
2113 , x_retcode => l_retcode ) ;
2114 IF l_retcode = '2' THEN
2115 -- Load failed, exit immediately.
2116 RAISE oki_utl_pub.g_excp_exit_immediate ;
2117 END IF ;
2118 ELSE
2119 l_loc := 'Update the record -- ' || l_measure_type || '.' ;
2120 -- Update the existing record
2121 oki_load_rbk_pvt.upd_rnwl_bkng (
2122 p_curr_base_contract_amount => l_curr_trmn_k
2123 , p_prev_base_contract_amount => l_prev_trmn_k
2124 , p_measure_code_meaning => l_bin_code_meaning
2125 , p_bin_code_seq => l_bin_code_seq
2126 , p_organization_name => oki_utl_pub.g_all_organization_name
2127 , p_customer_name => oki_utl_pub.g_all_customer_name
2128 , p_rbk_rowid => rec_g_rbk_csr.rowid
2129 , x_retcode => l_retcode ) ;
2130
2131 IF l_retcode = '2' THEN
2132 -- Load failed, exit immediately.
2133 RAISE oki_utl_pub.g_excp_exit_immediate ;
2134 END IF ;
2135 END IF ;
2136 CLOSE oki_load_rbk_pvt.g_rbk_csr ;
2137
2138 --
2139 -- Process Ending Active Contracts record
2140 --
2141
2142 -- Get the bin display lookup values
2143 l_loc := 'Opening cursor to retrieve bin display lookup values for ' ||
2144 l_measure_type || '.' ;
2145 OPEN oki_utl_pvt.g_bin_disp_lkup_csr ( oki_load_rbk_pvt.g_bin_id
2146 , oki_load_rbk_pvt.g_e_act_k_code ) ;
2147 FETCH oki_utl_pvt.g_bin_disp_lkup_csr INTO rec_l_bin_disp_lkup_csr ;
2148 IF oki_utl_pvt.g_bin_disp_lkup_csr%FOUND THEN
2149 l_bin_code_meaning := rec_l_bin_disp_lkup_csr.bin_code_meaning ;
2150 l_bin_code_seq := rec_l_bin_disp_lkup_csr.bin_code_seq ;
2151 ELSE
2152 RAISE NO_DATA_FOUND ;
2153 END IF ;
2154 CLOSE oki_utl_pvt.g_bin_disp_lkup_csr ;
2155
2156 l_measure_type := 'Ending Active Contracts' ;
2157
2158 l_curr_e_act_k := ( l_curr_b_act_k + l_curr_bklg_k_rwned +
2159 l_curr_k_rnwed + l_curr_new_bus ) -
2160 ( l_curr_k_exp_in_qtr + l_curr_trmn_k ) ;
2161
2162 l_prev_e_act_k := ( l_prev_b_act_k + l_prev_bklg_k_rwned +
2163 l_prev_k_rnwed + l_prev_new_bus ) -
2164 ( l_prev_k_exp_in_qtr + l_prev_trmn_k ) ;
2165
2166 l_loc := 'Opening cursor to determine current ' || l_measure_type || '.' ;
2167
2168 l_loc := 'Inserting / updating ' || l_measure_type || '.' ;
2169 -- Determine if the record is a new one or an existing one
2170 OPEN oki_load_rbk_pvt.g_rbk_csr ( oki_utl_pub.g_summary_build_date
2171 , oki_utl_pub.g_all_organization_id, oki_utl_pub.g_all_customer_id
2172 , oki_utl_pub.g_all_k_category_code
2173 , oki_load_rbk_pvt.g_e_act_k_code ) ;
2174 FETCH oki_load_rbk_pvt.g_rbk_csr INTO rec_g_rbk_csr ;
2175 IF oki_load_rbk_pvt.g_rbk_csr%NOTFOUND THEN
2176 l_loc := 'Insert the new record -- ' || l_measure_type || '.' ;
2177 -- Insert the new record
2178 oki_load_rbk_pvt.ins_rnwl_bkng (
2179 p_summary_build_date => oki_utl_pub.g_summary_build_date
2180 , p_authoring_org_id => oki_utl_pub.g_all_organization_id
2181 , p_organization_name => oki_utl_pub.g_all_organization_name
2182 , p_customer_party_id => oki_utl_pub.g_all_customer_id
2183 , p_customer_name => oki_utl_pub.g_all_customer_name
2184 , p_scs_code => oki_utl_pub.g_all_k_category_code
2185 , p_measure_code => oki_load_rbk_pvt.g_e_act_k_code
2186 , p_measure_code_meaning => l_bin_code_meaning
2187 , p_bin_code_seq => l_bin_code_seq
2188 , p_curr_base_contract_amount => l_curr_e_act_k
2189 , p_prev_base_contract_amount => l_prev_e_act_k
2190 , x_retcode => l_retcode ) ;
2191 IF l_retcode = '2' THEN
2192 -- Load failed, exit immediately.
2193 RAISE oki_utl_pub.g_excp_exit_immediate ;
2194 END IF ;
2195 ELSE
2196 l_loc := 'Update the record -- ' || l_measure_type || '.' ;
2197 -- Update the existing record
2198 oki_load_rbk_pvt.upd_rnwl_bkng (
2199 p_curr_base_contract_amount => l_curr_e_act_k
2200 , p_prev_base_contract_amount => l_prev_e_act_k
2201 , p_measure_code_meaning => l_bin_code_meaning
2202 , p_bin_code_seq => l_bin_code_seq
2203 , p_organization_name => oki_utl_pub.g_all_organization_name
2204 , p_customer_name => oki_utl_pub.g_all_customer_name
2205 , p_rbk_rowid => rec_g_rbk_csr.rowid
2206 , x_retcode => l_retcode ) ;
2207
2208 IF l_retcode = '2' THEN
2209 -- Load failed, exit immediately.
2210 RAISE oki_utl_pub.g_excp_exit_immediate ;
2211 END IF ;
2212 END IF ;
2213 CLOSE oki_load_rbk_pvt.g_rbk_csr ;
2214
2215 EXCEPTION
2216 WHEN oki_utl_pub.g_excp_exit_immediate THEN
2217 -- Do not log an error ; It has already been logged.
2218 -- Set return code to error
2219 x_retcode := '2' ;
2220
2221 WHEN NO_DATA_FOUND THEN
2222 l_sqlcode := SQLCODE ;
2223 l_sqlerrm := SQLERRM ;
2224
2225 -- Set return code TO error
2226 x_retcode := '2' ;
2227
2228 -- Log the location within the procedure where the error occurred
2229 fnd_message.set_name( application => 'OKI'
2230 , name => 'OKI_LOC_IN_PROG_FAILURE');
2231
2232 fnd_message.set_token( token => 'LOCATION'
2233 , value => l_loc);
2234
2235 fnd_file.put_line( which => fnd_file.log
2236 , buff => fnd_message.get);
2237
2241 WHEN OTHERS THEN
2238 fnd_file.put_line( which => fnd_file.log
2239 , buff => l_sqlcode || ' ' || l_sqlerrm );
2240
2242
2243 l_sqlcode := SQLCODE ;
2244 l_sqlerrm := SQLERRM ;
2245
2246 -- Set return code TO error
2247 x_retcode := '2' ;
2248
2249 fnd_message.set_name( application => 'OKI'
2250 , name => 'OKI_UNEXPECTED_FAILURE');
2251
2252 fnd_message.set_token( token => 'OBJECT_NAME'
2253 , value => 'OKI_LOAD_RBK_PVT.CALC_RBK_SUM');
2254
2255 fnd_file.put_line( which => fnd_file.log
2256 , buff => fnd_message.get);
2257
2258 -- Log the location within the procedure where the error occurred
2259 fnd_message.set_name( application => 'OKI'
2260 , name => 'OKI_LOC_IN_PROG_FAILURE');
2261
2262 fnd_message.set_token( token => 'LOCATION'
2263 , value => l_loc);
2264
2265 fnd_file.put_line( which => fnd_file.log
2266 , buff => fnd_message.get);
2267
2268 fnd_file.put_line( which => fnd_file.log
2269 , buff => l_sqlcode||' '|| l_sqlerrm );
2270 END calc_rbk_sum ;
2271 --------------------------------------------------------------------------------
2272 --
2273 -- Procedure which loops through the summary build date and calls procedures
2274 -- to load the renewal bookings table.
2275 --
2276 --------------------------------------------------------------------------------
2277
2278 PROCEDURE crt_rnwl_bkng
2279 ( p_start_summary_build_date IN DATE
2280 , p_end_summary_build_date IN DATE
2281 , x_errbuf OUT NOCOPY VARCHAR2
2282 , x_retcode OUT NOCOPY VARCHAR2
2283 ) IS
2284
2285 -- Local exception declaration
2286
2287 -- Exception to immediately exit the procedure
2288 l_excp_upd_refresh EXCEPTION ;
2289
2290
2291 -- Constant declaration
2292
2293 -- Name of the table for which data is being inserted
2294 l_table_name CONSTANT VARCHAR2(30) := 'OKI_RNWL_BOOKINGS' ;
2295
2296 -- Local variable declaration
2297
2298 -- For capturing the return code, 0 = success, 1 = warning, 2 = error
2299 l_retcode VARCHAR2(1) := NULL ;
2300
2301 -- For error handling
2302 l_sqlcode VARCHAR2(100) ;
2303 l_sqlerrm VARCHAR2(1000) ;
2304
2305 l_upper_bound NUMBER := 0 ;
2306 l_summary_build_date DATE := NULL ;
2307
2308 l_ending_period_type VARCHAR2(15) := NULL ;
2309
2310
2311 BEGIN
2312
2313 SAVEPOINT oki_etr_rnwl_bkng ;
2314
2315 -- initialize return code to success
2316 l_retcode := '0' ;
2317 x_retcode := '0' ;
2318
2319 l_upper_bound := TRUNC(p_end_summary_build_date) -
2320 TRUNC(p_start_summary_build_date) + 1 ;
2321
2322 l_summary_build_date := TRUNC(p_start_summary_build_date) ;
2323
2324 FOR i IN 1..l_upper_bound LOOP
2325
2326 oki_utl_pub.g_summary_build_date := l_summary_build_date ;
2327
2328 -- Get the GL periods start / end date
2332 IF l_retcode = '2' THEN
2329 oki_utl_pvt.get_gl_period_date (
2330 x_retcode => l_retcode ) ;
2331
2333 -- Load failed, exit immediately.
2334 RAISE oki_utl_pub.g_excp_exit_immediate ;
2335 END IF ;
2336
2337 -- Procedure to calculate the amounts for the all level
2338 oki_load_rbk_pvt.calc_rbk_sum (
2339 x_retcode => l_retcode ) ;
2340
2341 IF l_retcode = '2' THEN
2342 -- Load failed, exit immediately.
2343 RAISE oki_utl_pub.g_excp_exit_immediate ;
2344 END IF ;
2345
2346 -- Procedure to calculate the amounts for the organization level
2347 oki_load_rbk_pvt.calc_rbk_dtl1 (
2348 x_retcode => l_retcode ) ;
2349
2350 IF l_retcode = '2' THEN
2351 -- Load failed, exit immediately.
2352 RAISE oki_utl_pub.g_excp_exit_immediate ;
2353 END IF ;
2354
2355 l_summary_build_date := l_summary_build_date + 1 ;
2356
2357 END LOOP ;
2358
2359 COMMIT;
2360
2361 SAVEPOINT oki_rbk_upd_refresh ;
2362
2363
2364 -- Table loaded successfully. Log message IN concurrent manager
2365 -- log indicating successful load.
2366 fnd_message.set_name( application => 'OKI'
2367 , name => 'OKI_TABLE_LOAD_SUCCESS');
2368
2369 fnd_message.set_token( token => 'TABLE_NAME'
2370 , value => l_table_name );
2371
2372 fnd_file.put_line( which => fnd_file.log
2373 , buff => fnd_message.get);
2374
2375 oki_refresh_pvt.update_oki_refresh( l_table_name, l_retcode ) ;
2376
2377 IF l_retcode in ('1', '2') THEN
2378 -- Update to OKI_REFRESHS failed, exit immediately.
2379 RAISE l_excp_upd_refresh ;
2380 END IF ;
2381
2382 COMMIT ;
2383
2384 EXCEPTION
2385 WHEN l_excp_upd_refresh THEN
2386 -- Do not log error; It has already been logged by the refreshs
2387 -- program
2388 x_retcode := l_retcode ;
2389
2390 ROLLBACK TO oki_rbk_upd_refresh ;
2391
2392 WHEN oki_utl_pub.g_excp_exit_immediate THEN
2393 -- Do not log an error ; It has already been logged.
2394 -- Set return code to error
2395 x_retcode := '2' ;
2396
2397 ROLLBACK TO oki_etr_rnwl_bkng ;
2398
2399 WHEN OTHERS THEN
2400
2401 l_sqlcode := SQLCODE ;
2402 l_sqlerrm := SQLERRM ;
2403
2404 -- Set return code TO error
2405 x_retcode := '2' ;
2406
2407 -- ROLLBACK all transactions
2408 ROLLBACK TO oki_etr_rnwl_bkng ;
2409
2410
2411 fnd_message.set_name( application => 'OKI'
2412 , name => 'OKI_UNEXPECTED_FAILURE' ) ;
2413
2414 fnd_message.set_token( token => 'OBJECT_NAME'
2415 , value => 'OKI_LOAD_RBK_PVT.CRT_RNWL_BKNG' ) ;
2416
2417 fnd_file.put_line( which => fnd_file.log
2418 , buff => fnd_message.get ) ;
2419
2420 fnd_file.put_line( which => fnd_file.log
2421 , buff => l_sqlcode||' '||l_sqlerrm ) ;
2422 END crt_rnwl_bkng ;
2423
2424 BEGIN
2425 -- Initialize the global variables used TO log this job run
2426 -- FROM concurrent manager
2427 g_request_id := fnd_global.conc_request_id ;
2428 g_program_application_id := fnd_global.prog_appl_id ;
2429 g_program_id := fnd_global.conc_program_id ;
2430 g_program_update_date := SYSDATE ;
2431
2432 END oki_load_rbk_pvt ;