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