[Home] [Help]
PACKAGE BODY: APPS.OKI_LOAD_SGR_PVT
Source
1 PACKAGE BODY oki_load_sgr_pvt AS
2 /* $Header: OKIRSGRB.pls 115.8 2002/06/06 11:35:21 pkm ship $ */
3
4 --------------------------------------------------------------------------------
5 -- Modification History
6 -- 11-Nov-2001 mezra Corrected logic for ending active contracts.
7 -- Expired in quarter, cancelled renewals and
8 -- terminated contracts are subtracted from the
9 -- running total for ending active contracts.
10 -- 11-Nov-2001 mezra Added restriction to retrieve contracts that are
11 -- within a particular threshold. Fixed contracts
12 -- terminated cursor to sum the contracts.
13 -- 17-Oct-2001 mezra Removed the trunc function from all date columns
14 -- since the truncated date is placed into
15 -- oki_sales_k_hdrs. This program no longer needs
16 -- to truncate the dates.
17 -- 10-Oct-2001 mezra Initial version
18 --
19 --------------------------------------------------------------------------------
20
21 -- Global exception declaration
22
23 -- Generic exception to immediately exit the procedure
24 g_excp_exit_immediate EXCEPTION ;
25
26
27 -- Global constant delcaration
28
29 -- Constants for the all organization and caetgory record
30 g_all_org_id CONSTANT NUMBER := -1 ;
31 g_all_org_name CONSTANT VARCHAR2(60) := 'All Organizations' ;
32 g_all_cst_id CONSTANT NUMBER := -1 ;
33 g_all_cst_name CONSTANT VARCHAR2(30) := 'All Customers' ;
34 g_all_scs_code CONSTANT VARCHAR2(30) := '-1' ;
35 g_all_pct_code CONSTANT VARCHAR2(30) := '-1' ;
36
37 g_active_k_code CONSTANT VARCHAR2(30) := 'BACTK' ;
38 g_exp_in_qtr_code CONSTANT VARCHAR2(30) := 'EXPINQTR' ;
39 g_qtr_k_rnw_code CONSTANT VARCHAR2(30) := 'QTRKRNW' ;
40 g_bklg_k_rnw_code CONSTANT VARCHAR2(30) := 'BKLGKRNW' ;
41 g_new_bsn_code CONSTANT VARCHAR2(30) := 'NEWBUS' ;
42 g_cncl_rnwl_code CONSTANT VARCHAR2(30) := 'CNCLRNWL' ;
43 g_end_active_k_code CONSTANT VARCHAR2(30) := 'ENDACTK' ;
44 g_seq_grw_rate_code CONSTANT VARCHAR2(30) := 'SEQGWRT' ;
45 g_seq_trmn_k_code CONSTANT VARCHAR2(30) := 'TRMNK' ;
46 g_problem_k_threshold CONSTANT NUMBER :=
47 fnd_profile.value('OKI_PROBLEM_K_THRESHOLD') ;
48
49 -- Global cursor declaration
50
51 -- Cusror to retrieve the rowid for the selected record
52 CURSOR g_sgr_csr
53 ( p_period_set_name IN VARCHAR2
54 , p_period_name IN VARCHAR2
55 , p_authoring_org_id IN NUMBER
56 , p_seq_grw_rate_code IN VARCHAR2
57 , p_scs_code IN VARCHAR2
58 , p_customer_party_id IN NUMBER
59 , p_product_category_code IN VARCHAR2
60 , p_summary_build_date IN DATE
61 , p_period_type IN VARCHAR2
62 ) IS
63 SELECT rowid
64 FROM oki_seq_growth_rate sgr
65 WHERE sgr.period_set_name = p_period_set_name
66 AND sgr.period_name = p_period_name
67 AND sgr.authoring_org_id = p_authoring_org_id
68 AND sgr.seq_grw_rate_code = p_seq_grw_rate_code
69 AND sgr.scs_code = p_scs_code
70 AND sgr.customer_party_id = p_customer_party_id
71 AND sgr.product_category_code = p_product_category_code
72 AND sgr.summary_build_date = p_summary_build_date
73 AND sgr.period_type = p_period_type
74 ;
75 rec_g_sgr_csr g_sgr_csr%ROWTYPE ;
76
77
78
79 -- Cusor declaration
80
81 -- Cursor that calculates the contract amount for all
82 -- the active contracts
83 CURSOR g_active_k_csr
84 ( p_summary_build_date IN DATE
85 ) IS
86 SELECT NVL(SUM(base_contract_amount), 0) base_contract_amount
87 , NVL(SUM(sob_contract_amount), 0) sob_contract_amount
88 FROM oki_sales_k_hdrs shd
89 WHERE shd.date_signed <= p_summary_build_date
90 AND shd.date_approved <= p_summary_build_date
91 AND shd.start_date <= p_summary_build_date
92 AND shd.end_date > p_summary_build_date
93 AND ( shd.date_terminated IS NULL
94 OR shd.date_terminated > p_summary_build_date)
95 AND shd.base_contract_amount
96 BETWEEN 0 AND oki_load_sgr_pvt.g_problem_k_threshold
97 ;
98 rec_g_active_k_csr g_active_k_csr%ROWTYPE ;
99
100 -- Cursor that calculates contract amounts for all contracts
101 -- expiring this quarter
102 CURSOR g_expire_in_qtr_csr
103 ( p_glpr_qtr_start_date IN DATE
104 , p_glpr_qtr_end_date IN DATE
105 , p_summary_build_date IN DATE
106 )
107 IS
108 SELECT NVL(SUM(base_contract_amount), 0) base_contract_amount
109 , NVL(SUM(sob_contract_amount), 0) sob_contract_amount
110 FROM oki_sales_k_hdrs shd
111 WHERE shd.date_signed <= p_glpr_qtr_end_date
112 AND shd.date_approved <= p_glpr_qtr_end_date
113 AND shd.end_date BETWEEN p_glpr_qtr_start_date
114 AND p_glpr_qtr_end_date
115 AND ( shd.date_terminated IS NULL
116 OR shd.date_terminated > p_summary_build_date)
117 AND shd.base_contract_amount
118 BETWEEN 0 AND oki_load_sgr_pvt.g_problem_k_threshold
119 ;
120 rec_g_expire_in_qtr_csr g_expire_in_qtr_csr%ROWTYPE ;
121
122 -- Cursor that calculates contract amounts for contracts that
123 -- have been renewed in this quarter
124 CURSOR g_qtr_k_rnw_csr
125 ( p_glpr_qtr_start_date IN DATE
126 , p_summary_build_date IN DATE
127 )
128 IS
129 SELECT NVL(SUM(base_contract_amount), 0) base_contract_amount
130 , NVL(SUM(sob_contract_amount), 0) sob_contract_amount
131 FROM oki_sales_k_hdrs shd
132 WHERE shd.is_new_yn IS NULL
133 AND shd.date_signed IS NOT NULL
134 AND shd.date_approved IS NOT NULL
135 AND shd.start_date BETWEEN p_glpr_qtr_start_date
136 AND p_summary_build_date
137 AND GREATEST(shd.date_signed, shd.date_approved)
138 BETWEEN p_glpr_qtr_start_date
139 AND p_summary_build_date
140 AND shd.base_contract_amount
141 BETWEEN 0 AND oki_load_sgr_pvt.g_problem_k_threshold
142 ;
143 rec_g_qtr_k_rnw_csr g_qtr_k_rnw_csr%ROWTYPE ;
144
145 -- Contracts that were renewed in this quarter but should
146 -- have been renewed before this quarter
147 CURSOR g_bklg_k_rnw_csr
148 ( p_glpr_qtr_start_date IN DATE
149 , p_summary_build_date IN DATE
150 )
151 IS
152 SELECT NVL(SUM(base_contract_amount), 0) base_contract_amount
153 , NVL(SUM(sob_contract_amount), 0) sob_contract_amount
154 FROM oki_sales_k_hdrs shd
155 WHERE shd.is_new_yn IS NULL
156 AND shd.date_signed IS NOT NULL
157 AND shd.date_approved IS NOT NULL
158 AND shd.start_date < p_glpr_qtr_start_date
159 AND GREATEST(shd.date_signed, shd.date_approved)
160 BETWEEN p_glpr_qtr_start_date
161 AND p_summary_build_date
162 AND shd.base_contract_amount
163 BETWEEN 0 AND oki_load_sgr_pvt.g_problem_k_threshold
164 ;
165 rec_g_bklg_k_rnw_csr g_bklg_k_rnw_csr%ROWTYPE ;
166
167 -- Contracts that are active in the current quarter that are not the
168 -- result of renewal or renewal consolidation
169 CURSOR g_new_bsn_csr
170 ( p_glpr_qtr_start_date IN DATE
171 , p_summary_build_date IN DATE
172 )
173 IS
174 SELECT NVL(SUM(base_contract_amount), 0) base_contract_amount
175 , NVL(SUM(sob_contract_amount), 0) sob_contract_amount
176 FROM oki_sales_k_hdrs shd
177 WHERE shd.date_signed <= p_summary_build_date
178 AND shd.date_approved <= p_summary_build_date
179 AND shd.is_new_yn = 'Y'
180 AND shd.start_date BETWEEN p_glpr_qtr_start_date
181 AND p_summary_build_date
182 AND ( shd.date_terminated IS NULL
183 OR shd.date_terminated > p_summary_build_date)
184 AND shd.base_contract_amount
185 BETWEEN 0 AND oki_load_sgr_pvt.g_problem_k_threshold
186 ;
187 rec_g_new_bsn_csr g_new_bsn_csr%ROWTYPE ;
188
189 -- Renewal or renewal consolidate contracts that have been cancelled
190 CURSOR g_cncl_rnwl_csr
191 ( p_glpr_qtr_start_date IN DATE
192 , p_summary_build_date IN DATE
193 )
194 IS
195 SELECT NVL(SUM(base_contract_amount), 0) base_contract_amount
196 , NVL(SUM(sob_contract_amount), 0) sob_contract_amount
197 FROM oki_sales_k_hdrs shd
198 WHERE shd.ste_code = 'CANCELLED'
199 AND shd.is_new_yn IS NULL
200 AND shd.is_latest_yn IS NULL
201 AND shd.start_date BETWEEN p_glpr_qtr_start_date
202 AND p_summary_build_date
203 AND shd.base_contract_amount
204 BETWEEN 0 AND oki_load_sgr_pvt.g_problem_k_threshold
205 ;
206 rec_g_cncl_rnwl_csr g_cncl_rnwl_csr%ROWTYPE ;
207
208 -- Contracts that have been termined in this quarter
209 CURSOR g_trmn_rnwl_csr
210 ( p_glpr_qtr_start_date IN DATE
211 , p_summary_build_date IN DATE
212 )
213 IS
214 SELECT NVL(SUM((((shd.end_date - shd.date_terminated) /
215 (shd.end_date - shd.start_date)) *
216 base_contract_amount)), 0) base_contract_amount
217 , NVL(SUM((((shd.end_date - shd.date_terminated) /
218 (shd.end_date - shd.start_date)) *
219 sob_contract_amount)), 0) sob_contract_amount
220 FROM oki_sales_k_hdrs shd
221 WHERE date_terminated BETWEEN p_glpr_qtr_start_date
222 AND p_summary_build_date
223 AND shd.base_contract_amount
224 BETWEEN 0 AND oki_load_sgr_pvt.g_problem_k_threshold
225 ;
226 rec_g_trmn_rnwl_csr g_trmn_rnwl_csr%ROWTYPE ;
227
228 --------------------------------------------------------------------------------
229 -- Procedure to insert records into the oki_seq_growth_rate table.
230
231 --------------------------------------------------------------------------------
232 PROCEDURE ins_seq_grw_rate
233 ( p_period_set_name IN VARCHAR2
234 , p_period_name IN VARCHAR2
235 , p_period_type IN VARCHAR2
236 , p_summary_build_date IN DATE
237 , p_authoring_org_id IN NUMBER
238 , p_authoring_org_name IN VARCHAR2
239 , p_customer_party_id IN NUMBER
240 , p_customer_name IN VARCHAR2
241 , p_seq_grw_rate_code IN VARCHAR2
242 , p_scs_code IN VARCHAR2
243 , p_product_category_code IN VARCHAR2
244 , p_curr_base_contract_amount IN NUMBER
245 , p_prev_base_contract_amount IN NUMBER
246 , p_curr_sob_contract_amount IN NUMBER
247 , p_prev_sob_contract_amount IN NUMBER
248 , x_retcode OUT VARCHAR2
249 ) IS
250
251 -- Local variable declaration
252
253 -- For error handling
254 l_sqlcode VARCHAR2(100) ;
255 l_sqlerrm VARCHAR2(1000) ;
256
257 BEGIN
258
259 -- initialize return code to success
260 x_retcode := '0';
261 INSERT INTO oki_seq_growth_rate
262 ( period_set_name
263 , period_name
264 , period_type
265 , summary_build_date
266 , authoring_org_id
267 , authoring_org_name
268 , customer_party_id
269 , customer_name
270 , seq_grw_rate_code
271 , scs_code
272 , product_category_code
273 , curr_base_contract_amount
274 , prev_base_contract_amount
275 , curr_sob_contract_amount
276 , prev_sob_contract_amount
277 , request_id
278 , program_application_id
279 , program_id
280 , program_update_date )
281 VALUES ( p_period_set_name
282 , p_period_name
283 , p_period_type
284 , p_summary_build_date
285 , p_authoring_org_id
286 , p_authoring_org_name
287 , p_customer_party_id
288 , p_customer_name
289 , p_seq_grw_rate_code
290 , p_scs_code
291 , p_product_category_code
292 , p_curr_base_contract_amount
293 , p_prev_base_contract_amount
294 , p_curr_sob_contract_amount
295 , p_prev_sob_contract_amount
296 , oki_load_sgr_pvt.g_request_id
297 , oki_load_sgr_pvt.g_program_application_id
298 , oki_load_sgr_pvt.g_program_id
299 , oki_load_sgr_pvt.g_program_update_date ) ;
300
301 EXCEPTION
302 WHEN OTHERS THEN
303 l_sqlcode := SQLCODE ;
304 l_sqlerrm := SQLERRM ;
305
306 -- Set return code TO error
307 x_retcode := '2';
308
309 fnd_message.set_name( application => 'OKI'
310 , name => 'OKI_TABLE_LOAD_FAILURE' );
311
312 fnd_message.set_token( token => 'TABLE_NAME'
313 , value => 'OKI_SEQ_GROWTH_RATE' );
314
315 fnd_file.put_line( which => fnd_file.log
316 , buff => fnd_message.get );
317
318 fnd_file.put_line( which => fnd_file.log
319 , buff => l_sqlcode||' '|| l_sqlerrm );
320
321 END ins_seq_grw_rate ;
322
323 --------------------------------------------------------------------------------
324 -- Procedure to update records in the oki_seq_growth_rate table.
325
326 --------------------------------------------------------------------------------
327 PROCEDURE upd_seq_grw_rate
328 ( p_curr_base_contract_amount IN NUMBER
329 , p_prev_base_contract_amount IN NUMBER
330 , p_curr_sob_contract_amount IN NUMBER
331 , p_prev_sob_contract_amount IN NUMBER
332 , p_sgr_rowid IN ROWID
333 , x_retcode OUT VARCHAR2
334 ) IS
335
336 -- Local variable declaration
337
338 -- For error handling
339 l_sqlcode VARCHAR2(100) ;
340 l_sqlerrm VARCHAR2(1000) ;
341
342
343 BEGIN
344
345 -- initialize return code to success
346 x_retcode := '0';
347
348 UPDATE oki_seq_growth_rate SET
349 curr_base_contract_amount = p_curr_base_contract_amount
350 , prev_base_contract_amount = p_prev_base_contract_amount
351 , curr_sob_contract_amount = p_curr_sob_contract_amount
352 , prev_sob_contract_amount = p_prev_sob_contract_amount
353 , request_id = oki_load_sgr_pvt.g_request_id
354 , program_application_id = oki_load_sgr_pvt.g_program_application_id
355 , program_id = oki_load_sgr_pvt.g_program_id
356 , program_update_date = oki_load_sgr_pvt.g_program_update_date
357 WHERE ROWID = p_sgr_rowid ;
358
359 EXCEPTION
360 WHEN OTHERS THEN
361 l_sqlcode := SQLCODE ;
362 l_sqlerrm := SQLERRM ;
363
364 -- Set return code to error
365 x_retcode := '2';
366
367 fnd_message.set_name( application => 'OKI'
368 , name => 'OKI_UNEXPECTED_FAILURE' );
369
370 fnd_message.set_token( token => 'OBJECT_NAME'
371 , value => 'OKI_LOAD_SGR_PVT.UPD_SEQ_GRW_RATE' );
372
373 fnd_file.put_line( which => fnd_file.log
374 , buff => fnd_message.get );
375
376 fnd_file.put_line( which => fnd_file.log
380 --------------------------------------------------------------------------------
377 , buff => l_sqlcode||' '|| l_sqlerrm );
378 END upd_seq_grw_rate ;
379
381 -- Procedure to calcuate the contract amount for the current and previous
382 -- year.
383
384 --------------------------------------------------------------------------------
385
386 PROCEDURE calc_sgr_dtl1
387 ( p_period_set_name IN VARCHAR2
388 , p_period_type IN VARCHAR2
389 , p_summary_build_date IN DATE
390 , x_retcode OUT VARCHAR2
391 ) IS
392
393 -- Local variable declaration
394
395 -- For capturing the return code, 0 = success, 1 = warning, 2 = error
396 l_retcode VARCHAR2(1) := NULL ;
397
398 -- For error handling
399 l_sqlcode VARCHAR2(100) := NULL ;
400 l_sqlerrm VARCHAR2(1000) := NULL ;
401
402 -- Holds the contract amount for the current and previous
403 -- beginning active contracts
404 l_curr_active_k NUMBER := 0 ;
405 l_prev_active_k NUMBER := 0 ;
406 l_curr_sob_active_k NUMBER := 0 ;
407 l_prev_sob_active_k NUMBER := 0 ;
408 -- Holds the contract amount for the current and previous
409 -- ending active contracts
410 l_curr_end_active_k NUMBER := 0 ;
411 l_prev_end_active_k NUMBER := 0 ;
412 l_curr_sob_end_active_k NUMBER := 0 ;
413 l_prev_sob_end_active_k NUMBER := 0 ;
414 -- Holds the sequetial growth rate %
415 l_curr_seq_grw_rate NUMBER := 0 ;
416 l_prev_seq_grw_rate NUMBER := 0 ;
417 l_curr_sob_seq_grw_rate NUMBER := 0 ;
418 l_prev_sob_seq_grw_rate NUMBER := 0 ;
419 -- Holds the contract amount current and previous
420 -- sequential growth rate records
421 l_curr_k_amount NUMBER := 0 ;
422 l_prev_k_amount NUMBER := 0 ;
423 l_curr_sob_k_amount NUMBER := 0 ;
424 l_prev_sob_k_amount NUMBER := 0 ;
425
426 -- Location within the program before the error was encountered.
427 l_loc VARCHAR2(200) ;
428
429 -- Holds the truncated start and end dates from gl_periods
430 -- Holds the quarter start and end dates
431 l_glpr_qtr_start_date DATE ;
432 l_glpr_qtr_end_date DATE ;
433 -- Holds the prior year summary build date
434 l_py_summary_build_date DATE ;
435 -- Holds the start and end dates for the same quarter in the previous year
436 l_sqpy_glpr_qtr_start_date DATE ;
437 l_sqpy_glpr_qtr_end_date DATE ;
438
439 -- Cusor declaration
440
441 -- Cursor that calculates the contract amount for all
442 -- the active contracts
443 CURSOR l_active_k_csr
444 ( p_summary_build_date IN DATE
445 , p_customer_party_id IN NUMBER
446 ) IS
447 SELECT NVL(SUM(base_contract_amount), 0) base_contract_amount
448 , NVL(SUM(sob_contract_amount), 0) sob_contract_amount
449 FROM oki_sales_k_hdrs shd
450 WHERE shd.date_signed <= p_summary_build_date
451 AND shd.date_approved <= p_summary_build_date
452 AND shd.start_date <= p_summary_build_date
453 AND shd.end_date > p_summary_build_date
454 AND ( shd.date_terminated IS NULL
455 OR shd.date_terminated > p_summary_build_date)
456 AND shd.customer_party_id = p_customer_party_id
457 AND shd.base_contract_amount
458 BETWEEN 0 AND oki_load_sgr_pvt.g_problem_k_threshold
459 ;
460 rec_l_active_k_csr l_active_k_csr%ROWTYPE ;
461
462 -- Cursor that calculates contract amounts for all contracts
463 -- expiring this quarter
464 CURSOR l_expire_in_qtr_csr
465 ( p_glpr_qtr_start_date IN DATE
466 , p_glpr_qtr_end_date IN DATE
467 , p_summary_build_date IN DATE
468 , p_customer_party_id IN NUMBER
469 )
470 IS
471 SELECT NVL(SUM(base_contract_amount), 0) base_contract_amount
472 , NVL(SUM(sob_contract_amount), 0) sob_contract_amount
473 FROM oki_sales_k_hdrs shd
474 WHERE shd.date_signed <= p_glpr_qtr_end_date
475 AND shd.date_approved <= p_glpr_qtr_end_date
476 AND shd.end_date BETWEEN p_glpr_qtr_start_date
477 AND p_glpr_qtr_end_date
478 AND ( shd.date_terminated IS NULL
479 OR shd.date_terminated > p_summary_build_date)
480 AND shd.customer_party_id = p_customer_party_id
481 AND shd.base_contract_amount
482 BETWEEN 0 AND oki_load_sgr_pvt.g_problem_k_threshold
483 ;
484 rec_l_expire_in_qtr_csr l_expire_in_qtr_csr%ROWTYPE ;
485
486 -- Cursor that calculates contract amounts for contracts that
487 -- have been renewed in this quarter
488 CURSOR l_qtr_k_rnw_csr
489 ( p_glpr_qtr_start_date IN DATE
490 , p_summary_build_date IN DATE
491 , p_customer_party_id IN NUMBER
492 )
493 IS
494 SELECT NVL(SUM(base_contract_amount), 0) base_contract_amount
495 , NVL(SUM(sob_contract_amount), 0) sob_contract_amount
496 FROM oki_sales_k_hdrs shd
497 WHERE shd.is_new_yn IS NULL
498 AND shd.date_signed IS NOT NULL
499 AND shd.date_approved IS NOT NULL
500 AND shd.start_date BETWEEN p_glpr_qtr_start_date
501 AND p_summary_build_date
502 AND GREATEST(shd.date_signed, shd.date_approved)
503 BETWEEN p_glpr_qtr_start_date
504 AND p_summary_build_date
508 ;
505 AND shd.customer_party_id = p_customer_party_id
506 AND shd.base_contract_amount
507 BETWEEN 0 AND oki_load_sgr_pvt.g_problem_k_threshold
509 rec_l_qtr_k_rnw_csr l_qtr_k_rnw_csr%ROWTYPE ;
510
511 -- Contracts that were renewed in this quarter but should
512 -- have been renewed before this quarter
513 CURSOR l_bklg_k_rnw_csr
514 ( p_glpr_qtr_start_date IN DATE
515 , p_summary_build_date IN DATE
516 , p_customer_party_id IN NUMBER
517 )
518 IS
519 SELECT NVL(SUM(base_contract_amount), 0) base_contract_amount
520 , NVL(SUM(sob_contract_amount), 0) sob_contract_amount
521 FROM oki_sales_k_hdrs shd
522 WHERE shd.is_new_yn IS NULL
523 AND shd.date_signed IS NOT NULL
524 AND shd.date_approved IS NOT NULL
525 AND shd.start_date < p_glpr_qtr_start_date
526 AND GREATEST(shd.date_signed, shd.date_approved)
527 BETWEEN p_glpr_qtr_start_date
528 AND p_summary_build_date
529 AND shd.customer_party_id = p_customer_party_id
530 AND shd.base_contract_amount
531 BETWEEN 0 AND oki_load_sgr_pvt.g_problem_k_threshold
532 ;
533 rec_l_bklg_k_rnw_csr l_bklg_k_rnw_csr%ROWTYPE ;
534
535 -- Contracts that are active in the current quarter that are not the
536 -- result of renewal or renewal consolidation
537 CURSOR l_new_bsn_csr
538 ( p_glpr_qtr_start_date IN DATE
539 , p_summary_build_date IN DATE
540 , p_customer_party_id IN NUMBER
541 )
542 IS
543 SELECT NVL(SUM(base_contract_amount), 0) base_contract_amount
544 , NVL(SUM(sob_contract_amount), 0) sob_contract_amount
545 FROM oki_sales_k_hdrs shd
546 WHERE shd.date_signed <= p_summary_build_date
547 AND shd.date_approved <= p_summary_build_date
548 AND shd.is_new_yn = 'Y'
549 AND shd.start_date BETWEEN p_glpr_qtr_start_date
550 AND p_summary_build_date
551 AND ( shd.date_terminated IS NULL
552 OR shd.date_terminated > p_summary_build_date)
553 AND shd.customer_party_id = p_customer_party_id
554 AND shd.base_contract_amount
555 BETWEEN 0 AND oki_load_sgr_pvt.g_problem_k_threshold
556 ;
557 rec_l_new_bsn_csr l_new_bsn_csr%ROWTYPE ;
558
559 -- Renewal or renewal consolidate contracts that have been cancelled
560 CURSOR l_cncl_rnwl_csr
561 ( p_glpr_qtr_start_date IN DATE
562 , p_summary_build_date IN DATE
563 , p_customer_party_id IN NUMBER
564 )
565 IS
566 SELECT NVL(SUM(base_contract_amount), 0) base_contract_amount
567 , NVL(SUM(sob_contract_amount), 0) sob_contract_amount
568 FROM oki_sales_k_hdrs shd
569 WHERE shd.ste_code = 'CANCELLED'
570 AND shd.is_new_yn IS NULL
571 AND shd.is_latest_yn IS NULL
572 AND shd.start_date BETWEEN p_glpr_qtr_start_date
573 AND p_summary_build_date
574 AND shd.customer_party_id = p_customer_party_id
575 AND shd.base_contract_amount
576 BETWEEN 0 AND oki_load_sgr_pvt.g_problem_k_threshold
577 ;
578 rec_l_cncl_rnwl_csr l_cncl_rnwl_csr%ROWTYPE ;
579
580 -- Contracts that have been termined in this quarter
581 CURSOR l_trmn_rnwl_csr
582 ( p_glpr_qtr_start_date IN DATE
583 , p_summary_build_date IN DATE
584 , p_customer_party_id IN NUMBER
585 )
586 IS
587 SELECT NVL(SUM((((shd.end_date - shd.date_terminated) /
588 (shd.end_date - shd.start_date)) *
589 base_contract_amount)), 0) base_contract_amount
590 , NVL(SUM((((shd.end_date - shd.date_terminated) /
591 (shd.end_date - shd.start_date)) *
592 sob_contract_amount)), 0) sob_contract_amount
593 FROM oki_sales_k_hdrs shd
594 WHERE date_terminated BETWEEN p_glpr_qtr_start_date
595 AND p_summary_build_date
596 AND shd.customer_party_id = p_customer_party_id
597 AND shd.base_contract_amount
598 BETWEEN 0 AND oki_load_sgr_pvt.g_problem_k_threshold
599 ;
600 rec_l_trmn_rnwl_csr l_trmn_rnwl_csr%ROWTYPE ;
601
602 -- Cursor to retrieve the distinct organizations
603 CURSOR l_cst_csr IS
604 SELECT DISTINCT shd.customer_party_id customer_party_id
605 , shd.customer_name customer_name
606 FROM oki_sales_k_hdrs shd
607 ;
608
609
610 BEGIN
611
612 -- initialize return code to success
613 l_retcode := '0';
614
615 l_loc := 'Looping through valid organizations.' ;
616 << l_cst_csr_loop >>
617 -- Loop through all the organizations to calcuate the
618 -- appropriate amounts
619 FOR rec_l_cst_csr IN l_cst_csr LOOP
620
621 l_loc := 'Looping through valid periods.' ;
622 << g_glpr_csr_loop >>
623 -- Loop through all the periods
624 FOR rec_g_glpr_csr IN oki_utl_pvt.g_glpr_csr(
625 p_period_set_name, p_period_type, p_summary_build_date ) LOOP
626
627 -- Get the truncated gl_periods start and end dates
628 l_glpr_qtr_start_date := trunc(rec_g_glpr_csr.quarter_start_date) ;
629 l_glpr_qtr_end_date := ADD_MONTHS(l_glpr_qtr_start_date, 3) - 1 ;
630
631 -- Set the prior year summary build date
632 l_py_summary_build_date := ADD_MONTHS(p_summary_build_date, - 12) ;
636 l_sqpy_glpr_qtr_end_date := ADD_MONTHS(l_glpr_qtr_end_date, -12) ;
633 -- Set the quarter start and end dates for the same quarter
634 -- in the previous year
635 l_sqpy_glpr_qtr_start_date := ADD_MONTHS(l_glpr_qtr_start_date, -12) ;
637
638 -- Re-initialize the amounts before calculating
639 l_curr_active_k := 0 ;
640 l_prev_active_k := 0 ;
641 l_curr_sob_active_k := 0 ;
642 l_prev_sob_active_k := 0 ;
643 l_curr_end_active_k := 0 ;
644 l_prev_end_active_k := 0 ;
645 l_curr_sob_end_active_k := 0 ;
646 l_prev_sob_end_active_k := 0 ;
647 l_curr_seq_grw_rate := 0 ;
648 l_prev_seq_grw_rate := 0 ;
649 l_curr_sob_seq_grw_rate := 0 ;
650 l_prev_sob_seq_grw_rate := 0 ;
651 l_curr_k_amount := 0 ;
652 l_prev_k_amount := 0 ;
653 l_curr_sob_k_amount := 0 ;
654 l_prev_sob_k_amount := 0 ;
655
656 l_loc := 'Opening cursor to determine the current beginning ' ;
657 l_loc := l_loc || 'active contracts.' ;
658 OPEN l_active_k_csr ( p_summary_build_date,
659 rec_l_cst_csr.customer_party_id ) ;
660 FETCH l_active_k_csr INTO rec_l_active_k_csr ;
661 IF l_active_k_csr%FOUND THEN
662 l_curr_k_amount := rec_l_active_k_csr.base_contract_amount ;
663 l_curr_sob_k_amount := rec_l_active_k_csr.sob_contract_amount ;
664 -- keep the beginning active amount to determine the sequential
665 -- growth rate later
666 l_curr_active_k := l_curr_k_amount ;
667 l_curr_sob_active_k := l_curr_sob_k_amount ;
668 END IF;
669 CLOSE l_active_k_csr ;
670
671 l_loc := 'Opening cursor to determine the previous beginning ' ;
672 l_loc := l_loc || 'active contracts.' ;
673 OPEN l_active_k_csr ( l_py_summary_build_date,
674 rec_l_cst_csr.customer_party_id ) ;
675 FETCH l_active_k_csr INTO rec_l_active_k_csr ;
676 IF l_active_k_csr%FOUND THEN
677 l_prev_k_amount := rec_l_active_k_csr.base_contract_amount ;
678 l_prev_sob_k_amount := rec_l_active_k_csr.sob_contract_amount ;
679 -- keep the beginning active amount to determine the sequential
680 -- growth rate later
681 l_prev_active_k := l_prev_k_amount ;
682 l_prev_sob_active_k := l_prev_sob_k_amount ;
683 END IF ;
684 CLOSE l_active_k_csr ;
685
686 -- Determine running total for ending active contracts
687 -- Add beginning active contract amount
688 l_curr_end_active_k := l_curr_k_amount ;
689 l_prev_end_active_k := l_prev_k_amount ;
690 l_curr_sob_end_active_k := l_curr_sob_k_amount ;
691 l_prev_sob_end_active_k := l_prev_sob_k_amount ;
692
693 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
694 l_loc := l_loc || ' -- current / previous beginning active contracts' ;
695 -- Determine if the record is a new one or an existing one
696 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
697 rec_g_glpr_csr.period_name, oki_load_sgr_pvt.g_all_org_id,
698 oki_load_sgr_pvt.g_active_k_code, oki_load_sgr_pvt.g_all_scs_code,
699 rec_l_cst_csr.customer_party_id, oki_load_sgr_pvt.g_all_pct_code,
700 p_summary_build_date, rec_g_glpr_csr.period_type ) ;
701 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
702 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
703 l_loc := 'Insert the new record.' ;
704 l_loc := l_loc || ' -- current / previous beginning active contracts' ;
705 -- Insert the current period data for the period
706 oki_load_sgr_pvt.ins_seq_grw_rate (
707 p_period_set_name => rec_g_glpr_csr.period_set_name
708 , p_period_name => rec_g_glpr_csr.period_name
709 , p_period_type => rec_g_glpr_csr.period_type
710 , p_summary_build_date => p_summary_build_date
711 , p_authoring_org_id => oki_load_sgr_pvt.g_all_org_id
712 , p_authoring_org_name => oki_load_sgr_pvt.g_all_org_name
713 , p_customer_party_id => rec_l_cst_csr.customer_party_id
714 , p_customer_name => rec_l_cst_csr.customer_name
715 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_active_k_code
716 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
717 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
718 , p_curr_base_contract_amount => l_curr_k_amount
719 , p_prev_base_contract_amount => l_prev_k_amount
720 , p_curr_sob_contract_amount => l_curr_sob_k_amount
721 , p_prev_sob_contract_amount => l_prev_sob_k_amount
722 , x_retcode => l_retcode ) ;
723 IF l_retcode = '2' THEN
724 -- Load failed, exit immediately.
725 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
726 END IF ;
727 ELSE
728 l_loc := 'Update the existing record.' ;
729 l_loc := l_loc || ' -- current / previous beginning active contracts' ;
730 -- Record already exists, so perform an update
731 oki_load_sgr_pvt.upd_seq_grw_rate (
732 p_curr_base_contract_amount => l_curr_active_k
736 , p_sgr_rowid => rec_g_sgr_csr.rowid
733 , p_prev_base_contract_amount => l_prev_active_k
734 , p_curr_sob_contract_amount => l_curr_sob_active_k
735 , p_prev_sob_contract_amount => l_prev_sob_active_k
737 , x_retcode => l_retcode ) ;
738
739 IF l_retcode = '2' THEN
740 -- Load failed, exit immediately.
741 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
742 END IF ;
743 END IF ;
744 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
745
746 -- Re-initialize the amounts before calculating
747 l_curr_k_amount := 0 ;
748 l_prev_k_amount := 0 ;
749 l_curr_sob_k_amount := 0 ;
750 l_prev_sob_k_amount := 0 ;
751
752 l_loc := 'Opening cursor to determine the current expiring ' ;
753 l_loc := l_loc || 'during this quarter.' ;
754 OPEN l_expire_in_qtr_csr ( l_glpr_qtr_start_date,
755 l_glpr_qtr_end_date, p_summary_build_date,
756 rec_l_cst_csr.customer_party_id ) ;
757 FETCH l_expire_in_qtr_csr INTO rec_l_expire_in_qtr_csr ;
758 IF l_expire_in_qtr_csr%FOUND THEN
759 l_curr_k_amount := rec_l_expire_in_qtr_csr.base_contract_amount ;
760 l_curr_sob_k_amount := rec_l_expire_in_qtr_csr.sob_contract_amount ;
761 END IF;
762 CLOSE l_expire_in_qtr_csr ;
763
764 l_loc := 'Opening cursor to determine the previous expiring ' ;
765 l_loc := l_loc || 'during this quarter.' ;
766 OPEN l_expire_in_qtr_csr ( l_sqpy_glpr_qtr_start_date,
767 l_sqpy_glpr_qtr_end_date, l_py_summary_build_date,
768 rec_l_cst_csr.customer_party_id ) ;
769 FETCH l_expire_in_qtr_csr INTO rec_l_expire_in_qtr_csr ;
770 IF l_expire_in_qtr_csr%FOUND THEN
771 l_prev_k_amount := rec_l_expire_in_qtr_csr.base_contract_amount ;
772 l_prev_sob_k_amount := rec_l_expire_in_qtr_csr.sob_contract_amount ;
773 END IF ;
774 CLOSE l_expire_in_qtr_csr ;
775
776 -- Determine running total for ending active contracts
777 -- Subtract expiring during contract amount
778 l_curr_end_active_k := l_curr_end_active_k + (l_curr_k_amount * -1) ;
779 l_prev_end_active_k := l_prev_end_active_k + (l_prev_k_amount * -1) ;
780 l_curr_sob_end_active_k := l_curr_sob_end_active_k + (l_curr_sob_k_amount * -1) ;
781 l_prev_sob_end_active_k := l_prev_sob_end_active_k + (l_prev_sob_k_amount * -1) ;
782
783 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
784 l_loc := l_loc || ' -- current / previous expiring during quarter' ;
785 -- Determine if the record is a new one or an existing one
786 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
787 rec_g_glpr_csr.period_name, oki_load_sgr_pvt.g_all_org_id,
788 oki_load_sgr_pvt.g_exp_in_qtr_code, oki_load_sgr_pvt.g_all_scs_code,
789 rec_l_cst_csr.customer_party_id, oki_load_sgr_pvt.g_all_pct_code,
790 p_summary_build_date, rec_g_glpr_csr.period_type ) ;
791 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
792 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
793
794 l_loc := 'Insert the new record.' ;
795 l_loc := l_loc || ' -- current / previous expiring during quarter' ;
796 -- Insert the current period data for the period
797 oki_load_sgr_pvt.ins_seq_grw_rate (
798 p_period_set_name => rec_g_glpr_csr.period_set_name
799 , p_period_name => rec_g_glpr_csr.period_name
800 , p_period_type => rec_g_glpr_csr.period_type
801 , p_summary_build_date => p_summary_build_date
802 , p_authoring_org_id => oki_load_sgr_pvt.g_all_org_id
803 , p_authoring_org_name => oki_load_sgr_pvt.g_all_org_name
804 , p_customer_party_id => rec_l_cst_csr.customer_party_id
805 , p_customer_name => rec_l_cst_csr.customer_name
806 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_exp_in_qtr_code
807 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
808 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
809 , p_curr_base_contract_amount => l_curr_k_amount
810 , p_prev_base_contract_amount => l_prev_k_amount
811 , p_curr_sob_contract_amount => l_curr_sob_k_amount
812 , p_prev_sob_contract_amount => l_prev_sob_k_amount
813 , x_retcode => l_retcode ) ;
814 IF l_retcode = '2' THEN
815 -- Load failed, exit immediately.
816 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
817 END IF ;
818 ELSE
819 l_loc := 'Update the existing record.' ;
820 l_loc := l_loc || ' -- current / previous expiring during quarter' ;
821 -- Record already exists, so perform an update
822 oki_load_sgr_pvt.upd_seq_grw_rate (
823 p_curr_base_contract_amount => l_curr_k_amount
824 , p_prev_base_contract_amount => l_prev_k_amount
825 , p_curr_sob_contract_amount => l_curr_sob_k_amount
826 , p_prev_sob_contract_amount => l_prev_sob_k_amount
827 , p_sgr_rowid => rec_g_sgr_csr.rowid
828 , x_retcode => l_retcode ) ;
829
830 IF l_retcode = '2' THEN
834 END IF ;
831 -- Load failed, exit immediately.
832 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
833 END IF ;
835 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
836
837 -- Re-initialize the amounts before calculating
838 l_curr_k_amount := 0 ;
839 l_prev_k_amount := 0 ;
840 l_curr_sob_k_amount := 0 ;
841 l_prev_sob_k_amount := 0 ;
842
843 l_loc := 'Opening cursor to determine the current quarter ' ;
844 l_loc := l_loc || 'contracts renewed.' ;
845 OPEN l_qtr_k_rnw_csr ( l_glpr_qtr_start_date,
846 p_summary_build_date, rec_l_cst_csr.customer_party_id ) ;
847 FETCH l_qtr_k_rnw_csr INTO rec_l_qtr_k_rnw_csr ;
848 IF l_qtr_k_rnw_csr%FOUND THEN
849 l_curr_k_amount := rec_l_qtr_k_rnw_csr.base_contract_amount ;
850 l_curr_sob_k_amount := rec_l_qtr_k_rnw_csr.sob_contract_amount ;
851 END IF;
852 CLOSE l_qtr_k_rnw_csr ;
853
854 l_loc := 'Opening cursor to determine the previous quarter ' ;
855 l_loc := l_loc || 'contracts renewed.' ;
856 OPEN l_qtr_k_rnw_csr ( l_sqpy_glpr_qtr_start_date,
857 l_py_summary_build_date, rec_l_cst_csr.customer_party_id ) ;
858 FETCH l_qtr_k_rnw_csr INTO rec_l_qtr_k_rnw_csr ;
859 IF l_qtr_k_rnw_csr%FOUND THEN
860 l_prev_k_amount := rec_l_qtr_k_rnw_csr.base_contract_amount ;
861 l_prev_sob_k_amount := rec_l_qtr_k_rnw_csr.sob_contract_amount ;
862 END IF ;
863 CLOSE l_qtr_k_rnw_csr ;
864
865 -- Determine running total for ending active contracts
866 -- Add quarter contracts renewed amount
867 l_curr_end_active_k := l_curr_end_active_k + l_curr_k_amount ;
868 l_prev_end_active_k := l_prev_end_active_k + l_prev_k_amount ;
869 l_curr_sob_end_active_k := l_curr_sob_end_active_k + l_curr_sob_k_amount ;
870 l_prev_sob_end_active_k := l_prev_sob_end_active_k + l_prev_sob_k_amount ;
871
872 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
873 l_loc := l_loc || ' -- current / previous quarter contracts renewed' ;
874 -- Determine if the record is a new one or an existing one
875 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
876 rec_g_glpr_csr.period_name, oki_load_sgr_pvt.g_all_org_id,
877 oki_load_sgr_pvt.g_qtr_k_rnw_code, oki_load_sgr_pvt.g_all_scs_code,
878 rec_l_cst_csr.customer_party_id, oki_load_sgr_pvt.g_all_pct_code,
879 p_summary_build_date, rec_g_glpr_csr.period_type ) ;
880 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
881 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
882 l_loc := 'Insert the new record.' ;
883 l_loc := l_loc || ' -- current / previous quarter contracts renewed' ;
884 -- Insert the current period data for the period
885 oki_load_sgr_pvt.ins_seq_grw_rate (
886 p_period_set_name => rec_g_glpr_csr.period_set_name
887 , p_period_name => rec_g_glpr_csr.period_name
888 , p_period_type => rec_g_glpr_csr.period_type
889 , p_summary_build_date => p_summary_build_date
890 , p_authoring_org_id => oki_load_sgr_pvt.g_all_org_id
891 , p_authoring_org_name => oki_load_sgr_pvt.g_all_org_name
892 , p_customer_party_id => rec_l_cst_csr.customer_party_id
893 , p_customer_name => rec_l_cst_csr.customer_name
894 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_qtr_k_rnw_code
895 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
896 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
897 , p_curr_base_contract_amount => l_curr_k_amount
898 , p_prev_base_contract_amount => l_prev_k_amount
899 , p_curr_sob_contract_amount => l_curr_sob_k_amount
900 , p_prev_sob_contract_amount => l_prev_sob_k_amount
901 , x_retcode => l_retcode ) ;
902 IF l_retcode = '2' THEN
903 -- Load failed, exit immediately.
904 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
905 END IF ;
906 ELSE
907 l_loc := 'Update the existing record.' ;
908 l_loc := l_loc || ' -- current / previous quarter contracts renewed' ;
909 -- Record already exists, so perform an update
910 oki_load_sgr_pvt.upd_seq_grw_rate (
911 p_curr_base_contract_amount => l_curr_k_amount
912 , p_prev_base_contract_amount => l_prev_k_amount
913 , p_curr_sob_contract_amount => l_curr_sob_k_amount
914 , p_prev_sob_contract_amount => l_prev_sob_k_amount
915 , p_sgr_rowid => rec_g_sgr_csr.rowid
916 , x_retcode => l_retcode ) ;
917
918 IF l_retcode = '2' THEN
919 -- Load failed, exit immediately.
920 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
921 END IF ;
922 END IF ;
923 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
924
925 -- Re-initialize the amounts before calculating
926 l_curr_k_amount := 0 ;
927 l_prev_k_amount := 0 ;
928 l_curr_sob_k_amount := 0 ;
929 l_prev_sob_k_amount := 0 ;
930
931 l_loc := 'Opening cursor to determine the current backlog' ;
935 FETCH l_bklg_k_rnw_csr INTO rec_l_bklg_k_rnw_csr ;
932 l_loc := l_loc || 'contracts renewed.' ;
933 OPEN l_bklg_k_rnw_csr ( l_glpr_qtr_start_date,
934 p_summary_build_date, rec_l_cst_csr.customer_party_id ) ;
936 IF l_bklg_k_rnw_csr%FOUND THEN
937 l_curr_k_amount := rec_l_bklg_k_rnw_csr.base_contract_amount ;
938 l_curr_sob_k_amount := rec_l_bklg_k_rnw_csr.sob_contract_amount ;
939 END IF;
940 CLOSE l_bklg_k_rnw_csr ;
941
942 l_loc := 'Opening cursor to determine the previous backlog' ;
943 l_loc := l_loc || 'contracts renewed.' ;
944 OPEN l_bklg_k_rnw_csr ( l_sqpy_glpr_qtr_start_date,
945 l_py_summary_build_date, rec_l_cst_csr.customer_party_id ) ;
946 FETCH l_bklg_k_rnw_csr INTO rec_l_bklg_k_rnw_csr ;
947 IF l_bklg_k_rnw_csr%FOUND THEN
948 l_prev_k_amount := rec_l_bklg_k_rnw_csr.base_contract_amount ;
949 l_prev_sob_k_amount := rec_l_bklg_k_rnw_csr.sob_contract_amount ;
950 END IF;
951 CLOSE l_bklg_k_rnw_csr ;
952
953 -- Determine running total for ending active contracts
954 -- Add backlog contracts renewed amount
955 l_curr_end_active_k := l_curr_end_active_k + l_curr_k_amount ;
956 l_prev_end_active_k := l_prev_end_active_k + l_prev_k_amount ;
957 l_curr_sob_end_active_k := l_curr_sob_end_active_k + l_curr_sob_k_amount ;
958 l_prev_sob_end_active_k := l_prev_sob_end_active_k + l_prev_sob_k_amount ;
959
960 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
961 l_loc := l_loc || ' -- current / previous backlog contracts renewed' ;
962 -- Determine if the record is a new one or an existing one
963 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
964 rec_g_glpr_csr.period_name, oki_load_sgr_pvt.g_all_org_id,
965 oki_load_sgr_pvt.g_bklg_k_rnw_code, oki_load_sgr_pvt.g_all_scs_code,
966 rec_l_cst_csr.customer_party_id, oki_load_sgr_pvt.g_all_pct_code,
967 p_summary_build_date, rec_g_glpr_csr.period_type ) ;
968 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
969 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
970 l_loc := 'Insert the new record.' ;
971 l_loc := l_loc || ' -- current / previous backlog contracts renewed' ;
972 -- Insert the current period data for the period
973 oki_load_sgr_pvt.ins_seq_grw_rate (
974 p_period_set_name => rec_g_glpr_csr.period_set_name
975 , p_period_name => rec_g_glpr_csr.period_name
976 , p_period_type => rec_g_glpr_csr.period_type
977 , p_summary_build_date => p_summary_build_date
978 , p_authoring_org_id => oki_load_sgr_pvt.g_all_org_id
979 , p_authoring_org_name => oki_load_sgr_pvt.g_all_org_name
980 , p_customer_party_id => rec_l_cst_csr.customer_party_id
981 , p_customer_name => rec_l_cst_csr.customer_name
982 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_bklg_k_rnw_code
983 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
984 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
985 , p_curr_base_contract_amount => l_curr_k_amount
986 , p_prev_base_contract_amount => l_prev_k_amount
987 , p_curr_sob_contract_amount => l_curr_sob_k_amount
988 , p_prev_sob_contract_amount => l_prev_sob_k_amount
989 , x_retcode => l_retcode ) ;
990 IF l_retcode = '2' THEN
991 -- Load failed, exit immediately.
992 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
993 END IF ;
994 ELSE
995 l_loc := 'Update the existing record.' ;
996 l_loc := l_loc || ' -- current / previous backlog contracts renewed' ;
997 -- Record already exists, so perform an update
998 oki_load_sgr_pvt.upd_seq_grw_rate (
999 p_curr_base_contract_amount => l_curr_k_amount
1000 , p_prev_base_contract_amount => l_prev_k_amount
1001 , p_curr_sob_contract_amount => l_curr_sob_k_amount
1002 , p_prev_sob_contract_amount => l_prev_sob_k_amount
1003 , p_sgr_rowid => rec_g_sgr_csr.rowid
1004 , x_retcode => l_retcode ) ;
1005
1006 IF l_retcode = '2' THEN
1007 -- Load failed, exit immediately.
1008 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
1009 END IF ;
1010 END IF ;
1011 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
1012
1013 -- Re-initialize the amounts before calculating
1014 l_curr_k_amount := 0 ;
1015 l_prev_k_amount := 0 ;
1016 l_curr_sob_k_amount := 0 ;
1017 l_prev_sob_k_amount := 0 ;
1018
1019 l_loc := 'Opening cursor to determine the current new business.' ;
1020 OPEN l_new_bsn_csr ( l_glpr_qtr_start_date,
1021 p_summary_build_date, rec_l_cst_csr.customer_party_id ) ;
1022 FETCH l_new_bsn_csr INTO rec_l_new_bsn_csr ;
1023 IF l_new_bsn_csr%FOUND THEN
1024 l_curr_k_amount := rec_l_new_bsn_csr.base_contract_amount ;
1025 l_curr_sob_k_amount := rec_l_new_bsn_csr.sob_contract_amount ;
1026 END IF;
1027 CLOSE l_new_bsn_csr ;
1028
1029 l_loc := 'Opening cursor to determine the previous new business.' ;
1033 IF l_new_bsn_csr%FOUND THEN
1030 OPEN l_new_bsn_csr ( l_sqpy_glpr_qtr_start_date,
1031 l_py_summary_build_date, rec_l_cst_csr.customer_party_id ) ;
1032 FETCH l_new_bsn_csr INTO rec_l_new_bsn_csr ;
1034 l_prev_k_amount := rec_l_new_bsn_csr.base_contract_amount ;
1035 l_prev_sob_k_amount := rec_l_new_bsn_csr.sob_contract_amount ;
1036 END IF ;
1037 CLOSE l_new_bsn_csr ;
1038
1039 -- Determine running total for ending active contracts
1040 -- Add new business amount
1041 l_curr_end_active_k := l_curr_end_active_k + l_curr_k_amount ;
1042 l_prev_end_active_k := l_prev_end_active_k + l_prev_k_amount ;
1043 l_curr_sob_end_active_k := l_curr_sob_end_active_k + l_curr_sob_k_amount ;
1044 l_prev_sob_end_active_k := l_prev_sob_end_active_k + l_prev_sob_k_amount ;
1045
1046 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
1047 l_loc := l_loc || ' -- current / previous new business' ;
1048 -- Determine if the record is a new one or an existing one
1049 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
1050 rec_g_glpr_csr.period_name, oki_load_sgr_pvt.g_all_org_id,
1051 oki_load_sgr_pvt.g_new_bsn_code, oki_load_sgr_pvt.g_all_scs_code,
1052 rec_l_cst_csr.customer_party_id, oki_load_sgr_pvt.g_all_pct_code,
1053 p_summary_build_date, rec_g_glpr_csr.period_type ) ;
1054 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
1055 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
1056 l_loc := 'Insert the new record.' ;
1057 l_loc := l_loc || ' -- current / previous new business' ;
1058 -- Insert the current period data for the period
1059 oki_load_sgr_pvt.ins_seq_grw_rate (
1060 p_period_set_name => rec_g_glpr_csr.period_set_name
1061 , p_period_name => rec_g_glpr_csr.period_name
1062 , p_period_type => rec_g_glpr_csr.period_type
1063 , p_summary_build_date => p_summary_build_date
1064 , p_authoring_org_id => oki_load_sgr_pvt.g_all_org_id
1065 , p_authoring_org_name => oki_load_sgr_pvt.g_all_org_name
1066 , p_customer_party_id => rec_l_cst_csr.customer_party_id
1067 , p_customer_name => rec_l_cst_csr.customer_name
1068 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_new_bsn_code
1069 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
1070 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
1071 , p_curr_base_contract_amount => l_curr_k_amount
1072 , p_prev_base_contract_amount => l_prev_k_amount
1073 , p_curr_sob_contract_amount => l_curr_sob_k_amount
1074 , p_prev_sob_contract_amount => l_prev_sob_k_amount
1075 , x_retcode => l_retcode ) ;
1076 IF l_retcode = '2' THEN
1077 -- Load failed, exit immediately.
1078 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
1079 END IF ;
1080 ELSE
1081 l_loc := 'Update the existing record.' ;
1082 l_loc := l_loc || ' -- current / previous new business' ;
1083 -- Record already exists, so perform an update
1084 oki_load_sgr_pvt.upd_seq_grw_rate (
1085 p_curr_base_contract_amount => l_curr_k_amount
1086 , p_prev_base_contract_amount => l_prev_k_amount
1087 , p_curr_sob_contract_amount => l_curr_sob_k_amount
1088 , p_prev_sob_contract_amount => l_prev_sob_k_amount
1089 , p_sgr_rowid => rec_g_sgr_csr.rowid
1090 , x_retcode => l_retcode ) ;
1091
1092 IF l_retcode = '2' THEN
1093 -- Load failed, exit immediately.
1094 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
1095 END IF ;
1096 END IF ;
1097 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
1098
1099 -- Re-initialize the amounts before calculating
1100 l_curr_k_amount := 0 ;
1101 l_prev_k_amount := 0 ;
1102 l_curr_sob_k_amount := 0 ;
1103 l_prev_sob_k_amount := 0 ;
1104
1105 l_loc := 'Opening cursor to determine the current cancelled renewals.' ;
1106 OPEN l_cncl_rnwl_csr( l_glpr_qtr_start_date,
1107 p_summary_build_date, rec_l_cst_csr.customer_party_id ) ;
1108 FETCH l_cncl_rnwl_csr INTO rec_l_cncl_rnwl_csr ;
1109 IF l_cncl_rnwl_csr%FOUND THEN
1110 l_curr_k_amount := rec_l_cncl_rnwl_csr.base_contract_amount ;
1111 l_curr_sob_k_amount := rec_l_cncl_rnwl_csr.sob_contract_amount ;
1112 END IF;
1113 CLOSE l_cncl_rnwl_csr ;
1114
1115 l_loc := 'Opening cursor to determine the previous cancelled renewals.' ;
1116 OPEN l_cncl_rnwl_csr( l_sqpy_glpr_qtr_start_date,
1117 l_py_summary_build_date, rec_l_cst_csr.customer_party_id ) ;
1118 FETCH l_cncl_rnwl_csr INTO rec_l_cncl_rnwl_csr ;
1119 IF l_cncl_rnwl_csr%FOUND THEN
1120 l_prev_k_amount := rec_l_cncl_rnwl_csr.base_contract_amount ;
1121 l_prev_sob_k_amount := rec_l_cncl_rnwl_csr.sob_contract_amount ;
1122 END IF ;
1123 CLOSE l_cncl_rnwl_csr ;
1124
1125 -- Determine running total for ending active contracts
1126 -- Subtract cancelled contract amount
1130 l_prev_sob_end_active_k := l_prev_sob_end_active_k + (l_prev_sob_k_amount * -1) ;
1127 l_curr_end_active_k := l_curr_end_active_k + (l_curr_k_amount * -1) ;
1128 l_prev_end_active_k := l_prev_end_active_k + (l_prev_k_amount * -1) ;
1129 l_curr_sob_end_active_k := l_curr_sob_end_active_k + (l_curr_sob_k_amount * -1) ;
1131
1132 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
1133 l_loc := l_loc || ' -- current / previous cancelled contract' ;
1134 -- Determine if the record is a new one or an existing one
1135 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
1136 rec_g_glpr_csr.period_name, oki_load_sgr_pvt.g_all_org_id,
1137 oki_load_sgr_pvt.g_cncl_rnwl_code, oki_load_sgr_pvt.g_all_scs_code,
1138 rec_l_cst_csr.customer_party_id, oki_load_sgr_pvt.g_all_pct_code,
1139 p_summary_build_date, rec_g_glpr_csr.period_type ) ;
1140 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
1141 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
1142 l_loc := 'Insert the new record.' ;
1143 l_loc := l_loc || ' -- current / previous cancelled renewals' ;
1144 -- Insert the current period data for the period
1145 oki_load_sgr_pvt.ins_seq_grw_rate (
1146 p_period_set_name => rec_g_glpr_csr.period_set_name
1147 , p_period_name => rec_g_glpr_csr.period_name
1148 , p_period_type => rec_g_glpr_csr.period_type
1149 , p_summary_build_date => p_summary_build_date
1150 , p_authoring_org_id => oki_load_sgr_pvt.g_all_org_id
1151 , p_authoring_org_name => oki_load_sgr_pvt.g_all_org_name
1152 , p_customer_party_id => rec_l_cst_csr.customer_party_id
1153 , p_customer_name => rec_l_cst_csr.customer_name
1154 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_cncl_rnwl_code
1155 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
1156 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
1157 , p_curr_base_contract_amount => l_curr_k_amount
1158 , p_prev_base_contract_amount => l_prev_k_amount
1159 , p_curr_sob_contract_amount => l_curr_sob_k_amount
1160 , p_prev_sob_contract_amount => l_prev_sob_k_amount
1161 , x_retcode => l_retcode ) ;
1162 IF l_retcode = '2' THEN
1163 -- Load failed, exit immediately.
1164 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
1165 END IF ;
1166 ELSE
1167 l_loc := 'Update the existing record.' ;
1168 l_loc := l_loc || ' -- current / previous cancelled renewals' ;
1169 -- Record already exists, so perform an update
1170 oki_load_sgr_pvt.upd_seq_grw_rate (
1171 p_curr_base_contract_amount => l_curr_k_amount
1172 , p_prev_base_contract_amount => l_prev_k_amount
1173 , p_curr_sob_contract_amount => l_curr_sob_k_amount
1174 , p_prev_sob_contract_amount => l_prev_sob_k_amount
1175 , p_sgr_rowid => rec_g_sgr_csr.rowid
1176 , x_retcode => l_retcode ) ;
1177
1178 IF l_retcode = '2' THEN
1179 -- Load failed, exit immediately.
1180 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
1181 END IF ;
1182 END IF ;
1183 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
1184
1185 -- Re-initialize the amounts before calculating
1186 l_curr_k_amount := 0 ;
1187 l_prev_k_amount := 0 ;
1188 l_curr_sob_k_amount := 0 ;
1189 l_prev_sob_k_amount := 0 ;
1190
1191 l_loc := 'Looping through all the current contracts terminated in the period.';
1192 << l_trmn_rnwl_csr_loop >>
1193 -- Loop through all the contracts terminated in the period
1194 FOR rec_l_trmn_rnwl_csr IN l_trmn_rnwl_csr ( l_glpr_qtr_start_date,
1195 p_summary_build_date, rec_l_cst_csr.customer_party_id ) LOOP
1196 l_curr_k_amount := l_curr_k_amount +
1197 rec_l_trmn_rnwl_csr.base_contract_amount ;
1198 l_curr_sob_k_amount := l_curr_sob_k_amount +
1199 rec_l_trmn_rnwl_csr.sob_contract_amount ;
1200 END LOOP l_trmn_rnwl_csr_loop ;
1201 l_curr_k_amount := ROUND(l_curr_k_amount, 2) ;
1202 l_curr_sob_k_amount := ROUND(l_curr_sob_k_amount, 2) ;
1203
1204 l_loc := 'Looping through all the previous contracts terminated in the period.';
1205 << l_trmn_rnwl_csr_loop >>
1206 -- Loop through all the contracts terminated in the period
1207 FOR rec_l_trmn_rnwl_csr IN l_trmn_rnwl_csr ( l_sqpy_glpr_qtr_start_date,
1208 l_py_summary_build_date, rec_l_cst_csr.customer_party_id ) LOOP
1209 l_prev_k_amount := l_prev_k_amount +
1210 rec_l_trmn_rnwl_csr.base_contract_amount ;
1211 l_prev_sob_k_amount := l_prev_sob_k_amount +
1212 rec_l_trmn_rnwl_csr.sob_contract_amount ;
1213 END LOOP l_trmn_rnwl_csr_loop ;
1214 l_prev_k_amount := ROUND(l_prev_k_amount, 2) ;
1215 l_prev_sob_k_amount := ROUND(l_prev_sob_k_amount, 2) ;
1216
1217 -- Determine running total for ending active contracts
1218 -- Subtract terminated contract amount
1219 l_curr_end_active_k := l_curr_end_active_k + (l_curr_k_amount * -1) ;
1220 l_prev_end_active_k := l_prev_end_active_k + (l_prev_k_amount * -1) ;
1221 l_curr_sob_end_active_k := l_curr_sob_end_active_k + (l_curr_sob_k_amount * -1) ;
1222 l_prev_sob_end_active_k := l_prev_sob_end_active_k + (l_prev_sob_k_amount * -1) ;
1223
1224 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
1225 l_loc := l_loc || ' -- current / previous terminated renewals' ;
1226 -- Determine if the record is a new one or an existing one
1227 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
1228 rec_g_glpr_csr.period_name, oki_load_sgr_pvt.g_all_org_id,
1229 oki_load_sgr_pvt.g_seq_trmn_k_code, oki_load_sgr_pvt.g_all_scs_code,
1230 rec_l_cst_csr.customer_party_id, oki_load_sgr_pvt.g_all_pct_code,
1231 p_summary_build_date, rec_g_glpr_csr.period_type ) ;
1232 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
1233 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
1234 l_loc := 'Insert the new record.' ;
1238 p_period_set_name => rec_g_glpr_csr.period_set_name
1235 l_loc := l_loc || ' -- current / previous terminated renewals' ;
1236 -- Insert the current period data for the period
1237 oki_load_sgr_pvt.ins_seq_grw_rate (
1239 , p_period_name => rec_g_glpr_csr.period_name
1240 , p_period_type => rec_g_glpr_csr.period_type
1241 , p_summary_build_date => p_summary_build_date
1242 , p_authoring_org_id => oki_load_sgr_pvt.g_all_org_id
1243 , p_authoring_org_name => oki_load_sgr_pvt.g_all_org_name
1244 , p_customer_party_id => rec_l_cst_csr.customer_party_id
1245 , p_customer_name => rec_l_cst_csr.customer_name
1246 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_seq_trmn_k_code
1247 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
1248 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
1249 , p_curr_base_contract_amount => l_curr_k_amount
1250 , p_prev_base_contract_amount => l_prev_k_amount
1251 , p_curr_sob_contract_amount => l_curr_sob_k_amount
1252 , p_prev_sob_contract_amount => l_prev_sob_k_amount
1253 , x_retcode => l_retcode ) ;
1254 IF l_retcode = '2' THEN
1255 -- Load failed, exit immediately.
1256 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
1257 END IF ;
1258 ELSE
1259 l_loc := 'Update the existing record.' ;
1260 l_loc := l_loc || ' -- current / previous terminated renewals' ;
1261 -- Record already exists, so perform an update
1262 oki_load_sgr_pvt.upd_seq_grw_rate (
1263 p_curr_base_contract_amount => l_curr_k_amount
1264 , p_prev_base_contract_amount => l_prev_k_amount
1265 , p_curr_sob_contract_amount => l_curr_sob_k_amount
1266 , p_prev_sob_contract_amount => l_prev_sob_k_amount
1267 , p_sgr_rowid => rec_g_sgr_csr.rowid
1268 , x_retcode => l_retcode ) ;
1269
1270 IF l_retcode = '2' THEN
1271 -- Load failed, exit immediately.
1272 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
1273 END IF ;
1274 END IF ;
1275 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
1276
1277 -- Re-initialize the amounts before calculating
1278 l_curr_k_amount := 0 ;
1279 l_prev_k_amount := 0 ;
1280 l_curr_sob_k_amount := 0 ;
1281 l_prev_sob_k_amount := 0 ;
1282
1283 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
1284 l_loc := l_loc || ' -- current / previous ending active contracts' ;
1285 -- Determine if the record is a new one or an existing one
1286 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
1287 rec_g_glpr_csr.period_name, oki_load_sgr_pvt.g_all_org_id,
1288 oki_load_sgr_pvt.g_end_active_k_code, oki_load_sgr_pvt.g_all_scs_code,
1289 rec_l_cst_csr.customer_party_id, oki_load_sgr_pvt.g_all_pct_code,
1290 p_summary_build_date, rec_g_glpr_csr.period_type ) ;
1291 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
1292 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
1293 l_loc := 'Insert the new record.' ;
1294 l_loc := l_loc || ' -- current / previous ending active contracts' ;
1295 -- Insert the current period data for the period
1296 oki_load_sgr_pvt.ins_seq_grw_rate (
1297 p_period_set_name => rec_g_glpr_csr.period_set_name
1298 , p_period_name => rec_g_glpr_csr.period_name
1299 , p_period_type => rec_g_glpr_csr.period_type
1300 , p_summary_build_date => p_summary_build_date
1301 , p_authoring_org_id => oki_load_sgr_pvt.g_all_org_id
1302 , p_authoring_org_name => oki_load_sgr_pvt.g_all_org_name
1303 , p_customer_party_id => rec_l_cst_csr.customer_party_id
1304 , p_customer_name => rec_l_cst_csr.customer_name
1305 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_end_active_k_code
1306 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
1307 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
1308 , p_curr_base_contract_amount => l_curr_end_active_k
1309 , p_prev_base_contract_amount => l_prev_end_active_k
1310 , p_curr_sob_contract_amount => l_curr_sob_end_active_k
1311 , p_prev_sob_contract_amount => l_prev_sob_end_active_k
1312 , x_retcode => l_retcode ) ;
1313 IF l_retcode = '2' THEN
1314 -- Load failed, exit immediately.
1315 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
1316 END IF ;
1317 ELSE
1318 l_loc := 'Update the existing record.' ;
1319 l_loc := l_loc || ' -- current / previous ending active contracts' ;
1320 -- Record already exists, so perform an update
1321 oki_load_sgr_pvt.upd_seq_grw_rate (
1322 p_curr_base_contract_amount => l_curr_end_active_k
1323 , p_prev_base_contract_amount => l_prev_end_active_k
1324 , p_curr_sob_contract_amount => l_curr_sob_end_active_k
1325 , p_prev_sob_contract_amount => l_prev_sob_end_active_k
1326 , p_sgr_rowid => rec_g_sgr_csr.rowid
1330 -- Load failed, exit immediately.
1327 , x_retcode => l_retcode ) ;
1328
1329 IF l_retcode = '2' THEN
1331 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
1332 END IF ;
1333 END IF ;
1334 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
1335
1336 -- Re-initialize the amounts before calculating
1337 l_curr_k_amount := 0 ;
1338 l_prev_k_amount := 0 ;
1339 l_curr_sob_k_amount := 0 ;
1340 l_prev_sob_k_amount := 0 ;
1341
1342 IF l_curr_active_k = 0 THEN
1343 l_curr_seq_grw_rate := 0 ;
1344 ELSE
1345 l_curr_seq_grw_rate := ROUND(((l_curr_end_active_k -
1346 l_curr_active_k ) / l_curr_active_k ) * 100, 2) ;
1347 END IF ;
1348
1349 IF l_curr_sob_active_k = 0 THEN
1350 l_curr_sob_seq_grw_rate := 0 ;
1351 ELSE
1352 l_curr_sob_seq_grw_rate := ROUND(((l_curr_sob_end_active_k -
1353 l_curr_sob_active_k ) / l_curr_sob_active_k ) * 100, 2) ;
1354 END IF ;
1355
1356 IF l_prev_active_k = 0 THEN
1357 l_prev_seq_grw_rate := 0 ;
1358 ELSE
1359 l_prev_seq_grw_rate := ROUND(((l_prev_end_active_k -
1360 l_prev_active_k ) / l_prev_active_k ) * 100, 2) ;
1361 END IF ;
1362
1363 IF l_prev_sob_active_k = 0 THEN
1364 l_prev_sob_seq_grw_rate := 0 ;
1365 ELSE
1366 l_prev_sob_seq_grw_rate := ROUND(((l_prev_sob_end_active_k -
1367 l_prev_sob_active_k ) / l_prev_sob_active_k ) * 100, 2) ;
1368 END IF ;
1369
1370 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
1371 l_loc := l_loc || ' -- current / previous sequential growth rate' ;
1372 -- Determine if the record is a new one or an existing one
1373 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
1374 rec_g_glpr_csr.period_name, oki_load_sgr_pvt.g_all_org_id,
1375 oki_load_sgr_pvt.g_seq_grw_rate_code, oki_load_sgr_pvt.g_all_scs_code,
1376 rec_l_cst_csr.customer_party_id, oki_load_sgr_pvt.g_all_pct_code,
1377 p_summary_build_date, rec_g_glpr_csr.period_type ) ;
1378 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
1379 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
1380 l_loc := 'Insert the new record.' ;
1381 l_loc := l_loc || ' -- current / previous sequential growth rate' ;
1382 -- Insert the current period data for the period
1383 oki_load_sgr_pvt.ins_seq_grw_rate (
1384 p_period_set_name => rec_g_glpr_csr.period_set_name
1385 , p_period_name => rec_g_glpr_csr.period_name
1386 , p_period_type => rec_g_glpr_csr.period_type
1387 , p_summary_build_date => p_summary_build_date
1388 , p_authoring_org_id => oki_load_sgr_pvt.g_all_org_id
1389 , p_authoring_org_name => oki_load_sgr_pvt.g_all_org_name
1390 , p_customer_party_id => rec_l_cst_csr.customer_party_id
1391 , p_customer_name => rec_l_cst_csr.customer_name
1392 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_seq_grw_rate_code
1393 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
1394 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
1395 , p_curr_base_contract_amount => l_curr_seq_grw_rate
1396 , p_prev_base_contract_amount => l_prev_seq_grw_rate
1397 , p_curr_sob_contract_amount => l_curr_sob_seq_grw_rate
1398 , p_prev_sob_contract_amount => l_prev_sob_seq_grw_rate
1399 , x_retcode => l_retcode ) ;
1400 IF l_retcode = '2' THEN
1401 -- Load failed, exit immediately.
1402 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
1403 END IF ;
1404 ELSE
1405 l_loc := 'Update the existing record.' ;
1406 l_loc := l_loc || ' -- current / previous sequential growth rate' ;
1407 -- Record already exists, so perform an update
1408 oki_load_sgr_pvt.upd_seq_grw_rate (
1409 p_curr_base_contract_amount => l_curr_seq_grw_rate
1410 , p_prev_base_contract_amount => l_prev_seq_grw_rate
1411 , p_curr_sob_contract_amount => l_curr_sob_seq_grw_rate
1412 , p_prev_sob_contract_amount => l_prev_sob_seq_grw_rate
1413 , p_sgr_rowid => rec_g_sgr_csr.rowid
1414 , x_retcode => l_retcode ) ;
1415
1416 IF l_retcode = '2' THEN
1417 -- Load failed, exit immediately.
1418 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
1419 END IF ;
1420 END IF ;
1421 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
1422
1423 END LOOP g_glpr_csr_loop ;
1424 END LOOP l_cst_csr_loop ;
1425
1426 EXCEPTION
1427 WHEN oki_load_sgr_pvt.g_excp_exit_immediate THEN
1428 -- Do not log an error ; It has already been logged.
1429 -- Set return code to error
1430 x_retcode := '2' ;
1431
1432
1433 WHEN OTHERS THEN
1434 l_sqlcode := SQLCODE ;
1435 l_sqlerrm := SQLERRM ;
1436
1437 -- Set return code TO error
1438 x_retcode := '2' ;
1439
1440 fnd_message.set_name( application => 'OKI'
1441 , name => 'OKI_UNEXPECTED_FAILURE');
1442
1443 fnd_message.set_token( token => 'OBJECT_NAME'
1447 , buff => fnd_message.get);
1444 , value => 'OKI_LOAD_SGR_PVT.CALC_SGR_DTL1');
1445
1446 fnd_file.put_line( which => fnd_file.log
1448
1449 -- Log the location within the procedure where the error occurred
1450 fnd_message.set_name( application => 'OKI'
1451 , name => 'OKI_LOC_IN_PROG_FAILURE');
1452
1453 fnd_message.set_token( token => 'LOCATION'
1454 , value => l_loc);
1455
1456 fnd_file.put_line( which => fnd_file.log
1457 , buff => fnd_message.get);
1458
1459 fnd_file.put_line( which => fnd_file.log
1460 , buff => l_sqlcode||' '||l_sqlerrm );
1461 END calc_sgr_dtl1 ;
1462
1463 --------------------------------------------------------------------------------
1464 -- Procedure to calcuate the contract amount for the current and previous
1465 -- year.
1466
1467 --------------------------------------------------------------------------------
1468 PROCEDURE calc_sgr_dtl2
1469 ( p_period_set_name IN VARCHAR2
1470 , p_period_type IN VARCHAR2
1471 , p_summary_build_date IN DATE
1472 , x_retcode OUT VARCHAR2
1473 ) IS
1474
1475 -- Local variable declaration
1476
1477 -- For capturing the return code, 0 = success, 1 = warning, 2 = error
1478 l_retcode VARCHAR2(1) := NULL ;
1479
1480 -- For error handling
1481 l_sqlcode VARCHAR2(100) := NULL ;
1482 l_sqlerrm VARCHAR2(1000) := NULL ;
1483
1484 -- Holds the contract amount for the current and previous
1485 -- beginning active contracts
1486 l_curr_active_k NUMBER := 0 ;
1487 l_prev_active_k NUMBER := 0 ;
1488 l_curr_sob_active_k NUMBER := 0 ;
1489 l_prev_sob_active_k NUMBER := 0 ;
1490 -- Holds the contract amount for the current and previous
1491 -- ending active contracts
1492 l_curr_end_active_k NUMBER := 0 ;
1493 l_prev_end_active_k NUMBER := 0 ;
1494 l_curr_sob_end_active_k NUMBER := 0 ;
1495 l_prev_sob_end_active_k NUMBER := 0 ;
1496 -- Holds the sequetial growth rate %
1497 l_curr_seq_grw_rate NUMBER := 0 ;
1498 l_prev_seq_grw_rate NUMBER := 0 ;
1499 l_curr_sob_seq_grw_rate NUMBER := 0 ;
1500 l_prev_sob_seq_grw_rate NUMBER := 0 ;
1501 -- Holds the contract amount current and previous
1502 -- sequential growth rate records
1503 l_curr_k_amount NUMBER := 0 ;
1504 l_prev_k_amount NUMBER := 0 ;
1505 l_curr_sob_k_amount NUMBER := 0 ;
1506 l_prev_sob_k_amount NUMBER := 0 ;
1507
1508 -- Location within the program before the error was encountered.
1509 l_loc VARCHAR2(200) ;
1510
1511 -- Holds the truncated start and end dates from gl_periods
1512 -- Holds the quarter start and end dates
1513 l_glpr_qtr_start_date DATE ;
1514 l_glpr_qtr_end_date DATE ;
1515 -- Holds the prior year summary build date
1516 l_py_summary_build_date DATE ;
1517 -- Holds the start and end dates for the same quarter in the previous year
1518 l_sqpy_glpr_qtr_start_date DATE ;
1519 l_sqpy_glpr_qtr_end_date DATE ;
1520
1521 -- Cusor declaration
1522
1523 -- Cursor that calculates the contract amount for all
1524 -- the active contracts
1525 CURSOR l_active_k_csr
1526 ( p_summary_build_date IN DATE
1527 , p_authoring_org_id IN NUMBER
1528 ) IS
1529 SELECT NVL(SUM(base_contract_amount), 0) base_contract_amount
1530 , NVL(SUM(sob_contract_amount), 0) sob_contract_amount
1531 FROM oki_sales_k_hdrs shd
1532 WHERE shd.date_signed <= p_summary_build_date
1533 AND shd.date_approved <= p_summary_build_date
1534 AND shd.start_date <= p_summary_build_date
1535 AND shd.end_date > p_summary_build_date
1536 AND ( shd.date_terminated IS NULL
1537 OR shd.date_terminated > p_summary_build_date)
1538 AND shd.authoring_org_id = p_authoring_org_id
1539 AND shd.base_contract_amount
1540 BETWEEN 0 AND oki_load_sgr_pvt.g_problem_k_threshold
1541 ;
1542 rec_l_active_k_csr l_active_k_csr%ROWTYPE ;
1543
1544 -- Cursor that calculates contract amounts for all contracts
1545 -- expiring this quarter
1546 CURSOR l_expire_in_qtr_csr
1547 ( p_glpr_qtr_start_date IN DATE
1548 , p_glpr_qtr_end_date IN DATE
1549 , p_summary_build_date IN DATE
1550 , p_authoring_org_id IN NUMBER
1551 )
1552 IS
1553 SELECT NVL(SUM(base_contract_amount), 0) base_contract_amount
1554 , NVL(SUM(sob_contract_amount), 0) sob_contract_amount
1555 FROM oki_sales_k_hdrs shd
1556 WHERE shd.date_signed <= p_glpr_qtr_end_date
1557 AND shd.date_approved <= p_glpr_qtr_end_date
1558 AND shd.end_date BETWEEN p_glpr_qtr_start_date
1559 AND p_glpr_qtr_end_date
1560 AND ( shd.date_terminated IS NULL
1561 OR shd.date_terminated > p_summary_build_date)
1562 AND shd.authoring_org_id = p_authoring_org_id
1563 AND shd.base_contract_amount
1564 BETWEEN 0 AND oki_load_sgr_pvt.g_problem_k_threshold
1565 ;
1566 rec_l_expire_in_qtr_csr l_expire_in_qtr_csr%ROWTYPE ;
1567
1568 -- Cursor that calculates contract amounts for contracts that
1569 -- have been renewed in this quarter
1570 CURSOR l_qtr_k_rnw_csr
1571 ( p_glpr_qtr_start_date IN DATE
1572 , p_summary_build_date IN DATE
1573 , p_authoring_org_id IN NUMBER
1577 , NVL(SUM(sob_contract_amount), 0) sob_contract_amount
1574 )
1575 IS
1576 SELECT NVL(SUM(base_contract_amount), 0) base_contract_amount
1578 FROM oki_sales_k_hdrs shd
1579 WHERE shd.is_new_yn IS NULL
1580 AND shd.date_signed IS NOT NULL
1581 AND shd.date_approved IS NOT NULL
1582 AND shd.start_date BETWEEN p_glpr_qtr_start_date
1583 AND p_summary_build_date
1584 AND GREATEST(shd.date_signed, shd.date_approved)
1585 BETWEEN p_glpr_qtr_start_date
1586 AND p_summary_build_date
1587 AND shd.authoring_org_id = p_authoring_org_id
1588 AND shd.base_contract_amount
1589 BETWEEN 0 AND oki_load_sgr_pvt.g_problem_k_threshold
1590 ;
1591 rec_l_qtr_k_rnw_csr l_qtr_k_rnw_csr%ROWTYPE ;
1592
1593 -- Contracts that were renewed in this quarter but should
1594 -- have been renewed before this quarter
1595 CURSOR l_bklg_k_rnw_csr
1596 ( p_glpr_qtr_start_date IN DATE
1597 , p_summary_build_date IN DATE
1598 , p_authoring_org_id IN NUMBER
1599 )
1600 IS
1601 SELECT NVL(SUM(base_contract_amount), 0) base_contract_amount
1602 , NVL(SUM(sob_contract_amount), 0) sob_contract_amount
1603 FROM oki_sales_k_hdrs shd
1604 WHERE shd.is_new_yn IS NULL
1605 AND shd.date_signed IS NOT NULL
1606 AND shd.date_approved IS NOT NULL
1607 AND shd.start_date < p_glpr_qtr_start_date
1608 AND GREATEST(shd.date_signed, shd.date_approved)
1609 BETWEEN p_glpr_qtr_start_date
1610 AND p_summary_build_date
1611 AND shd.authoring_org_id = p_authoring_org_id
1612 AND shd.base_contract_amount
1613 BETWEEN 0 AND oki_load_sgr_pvt.g_problem_k_threshold
1614 ;
1615 rec_l_bklg_k_rnw_csr l_bklg_k_rnw_csr%ROWTYPE ;
1616
1617 -- Contracts that are active in the current quarter that are not the
1618 -- result of renewal or renewal consolidation
1619 CURSOR l_new_bsn_csr
1620 ( p_glpr_qtr_start_date IN DATE
1621 , p_summary_build_date IN DATE
1622 , p_authoring_org_id IN NUMBER
1623 )
1624 IS
1625 SELECT NVL(SUM(base_contract_amount), 0) base_contract_amount
1626 , NVL(SUM(sob_contract_amount), 0) sob_contract_amount
1627 FROM oki_sales_k_hdrs shd
1628 WHERE shd.date_signed <= p_summary_build_date
1629 AND shd.date_approved <= p_summary_build_date
1630 AND shd.is_new_yn = 'Y'
1631 AND shd.start_date BETWEEN p_glpr_qtr_start_date
1632 AND p_summary_build_date
1633 AND ( shd.date_terminated IS NULL
1634 OR shd.date_terminated > p_summary_build_date)
1635 AND shd.authoring_org_id = p_authoring_org_id
1636 AND shd.base_contract_amount
1637 BETWEEN 0 AND oki_load_sgr_pvt.g_problem_k_threshold
1638 ;
1639 rec_l_new_bsn_csr l_new_bsn_csr%ROWTYPE ;
1640
1641 -- Renewal or renewal consolidate contracts that have been cancelled
1642 CURSOR l_cncl_rnwl_csr
1643 ( p_glpr_qtr_start_date IN DATE
1644 , p_summary_build_date IN DATE
1645 , p_authoring_org_id IN NUMBER
1646 )
1647 IS
1648 SELECT NVL(SUM(base_contract_amount), 0) base_contract_amount
1649 , NVL(SUM(sob_contract_amount), 0) sob_contract_amount
1650 FROM oki_sales_k_hdrs shd
1651 WHERE shd.ste_code = 'CANCELLED'
1652 AND shd.is_new_yn IS NULL
1653 AND shd.is_latest_yn IS NULL
1654 AND shd.start_date BETWEEN p_glpr_qtr_start_date
1655 AND p_summary_build_date
1656 AND shd.authoring_org_id = p_authoring_org_id
1657 AND shd.base_contract_amount
1658 BETWEEN 0 AND oki_load_sgr_pvt.g_problem_k_threshold
1659 ;
1660 rec_l_cncl_rnwl_csr l_cncl_rnwl_csr%ROWTYPE ;
1661
1662 -- Contracts that have been termined in this quarter
1663 CURSOR l_trmn_rnwl_csr
1664 ( p_glpr_qtr_start_date IN DATE
1665 , p_summary_build_date IN DATE
1666 , p_authoring_org_id IN NUMBER
1667 )
1668 IS
1669 SELECT NVL(SUM((((shd.end_date - shd.date_terminated) /
1670 (shd.end_date - shd.start_date)) *
1671 base_contract_amount)), 0) base_contract_amount
1672 , NVL(SUM((((shd.end_date - shd.date_terminated) /
1673 (shd.end_date - shd.start_date)) *
1674 sob_contract_amount)), 0) sob_contract_amount
1675 FROM oki_sales_k_hdrs shd
1676 WHERE date_terminated BETWEEN p_glpr_qtr_start_date
1677 AND p_summary_build_date
1678 AND shd.authoring_org_id = p_authoring_org_id
1679 AND shd.base_contract_amount
1680 BETWEEN 0 AND oki_load_sgr_pvt.g_problem_k_threshold
1681 ;
1682 rec_l_trmn_rnwl_csr l_trmn_rnwl_csr%ROWTYPE ;
1683
1684 -- Cursor to retrieve the distinct organizations
1685 CURSOR l_org_csr IS
1686 SELECT DISTINCT shd.authoring_org_id authoring_org_id
1687 , shd.organization_name authoring_org_name
1688 FROM oki_sales_k_hdrs shd
1689 ;
1690
1691
1692 BEGIN
1693
1694 -- initialize return code to success
1695 l_retcode := '0';
1696
1697 l_loc := 'Looping through valid organizations.' ;
1698 << l_org_csr_loop >>
1699 -- Loop through all the organizations to calcuate the
1700 -- appropriate amounts
1701 FOR rec_l_org_csr IN l_org_csr LOOP
1702
1703 l_loc := 'Looping through valid periods.' ;
1704 << g_glpr_csr_loop >>
1705 -- Loop through all the periods
1709 -- set the quarter and year gl_periods start and end dates
1706 FOR rec_g_glpr_csr IN oki_utl_pvt.g_glpr_csr(
1707 p_period_set_name, p_period_type, p_summary_build_date ) LOOP
1708
1710 l_glpr_qtr_start_date := trunc(rec_g_glpr_csr.quarter_start_date) ;
1711 l_glpr_qtr_end_date := ADD_MONTHS(l_glpr_qtr_start_date, 3) - 1 ;
1712
1713 -- Set the prior year summary build date
1714 l_py_summary_build_date := ADD_MONTHS(p_summary_build_date, - 12) ;
1715 -- Set the year start and end dates for the previous year
1716 l_sqpy_glpr_qtr_start_date := ADD_MONTHS(l_glpr_qtr_start_date, -12) ;
1717 l_sqpy_glpr_qtr_end_date := ADD_MONTHS(l_glpr_qtr_end_date, -12) ;
1718
1719 -- Re-initialize the amounts before calculating
1720 l_curr_active_k := 0 ;
1721 l_prev_active_k := 0 ;
1722 l_curr_sob_active_k := 0 ;
1723 l_prev_sob_active_k := 0 ;
1724 l_curr_end_active_k := 0 ;
1725 l_prev_end_active_k := 0 ;
1726 l_curr_sob_end_active_k := 0 ;
1727 l_prev_sob_end_active_k := 0 ;
1728 l_curr_seq_grw_rate := 0 ;
1729 l_prev_seq_grw_rate := 0 ;
1730 l_curr_sob_seq_grw_rate := 0 ;
1731 l_prev_sob_seq_grw_rate := 0 ;
1732 l_curr_k_amount := 0 ;
1733 l_prev_k_amount := 0 ;
1734 l_curr_sob_k_amount := 0 ;
1735 l_prev_sob_k_amount := 0 ;
1736
1737 l_loc := 'Opening cursor to determine the current beginning ' ;
1738 l_loc := l_loc || 'active contracts.' ;
1739 OPEN l_active_k_csr ( p_summary_build_date,
1740 rec_l_org_csr.authoring_org_id ) ;
1741 FETCH l_active_k_csr INTO rec_l_active_k_csr ;
1742 IF l_active_k_csr%FOUND THEN
1743 l_curr_k_amount := rec_l_active_k_csr.base_contract_amount ;
1744 l_curr_sob_k_amount := rec_l_active_k_csr.sob_contract_amount ;
1745 -- keep the beginning active amount to determine the sequential
1746 -- growth rate later
1747 l_curr_active_k := l_curr_k_amount ;
1748 l_curr_sob_active_k := l_curr_sob_k_amount ;
1749 END IF;
1750 CLOSE l_active_k_csr ;
1751
1752 l_loc := 'Opening cursor to determine the previous beginning ' ;
1753 l_loc := l_loc || 'active contracts.' ;
1754 OPEN l_active_k_csr ( l_py_summary_build_date,
1755 rec_l_org_csr.authoring_org_id ) ;
1756 FETCH l_active_k_csr INTO rec_l_active_k_csr ;
1757 IF l_active_k_csr%FOUND THEN
1758 l_prev_k_amount := rec_l_active_k_csr.base_contract_amount ;
1759 l_prev_sob_k_amount := rec_l_active_k_csr.sob_contract_amount ;
1760 -- keep the beginning active amount to determine the sequential
1761 -- growth rate later
1762 l_prev_active_k := l_prev_k_amount ;
1763 l_prev_sob_active_k := l_prev_sob_k_amount ;
1764 END IF ;
1765 CLOSE l_active_k_csr ;
1766
1767 -- Determine running total for ending active contracts
1768 -- Add beginning active contract amount
1769 l_curr_end_active_k := l_curr_k_amount ;
1770 l_prev_end_active_k := l_prev_k_amount ;
1771 l_curr_sob_end_active_k := l_curr_sob_k_amount ;
1772 l_prev_sob_end_active_k := l_prev_sob_k_amount ;
1773
1774 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
1775 l_loc := l_loc || ' -- current / previous beginning active contracts' ;
1776 -- Determine if the record is a new one or an existing one
1777 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
1778 rec_g_glpr_csr.period_name, rec_l_org_csr.authoring_org_id,
1779 oki_load_sgr_pvt.g_active_k_code, oki_load_sgr_pvt.g_all_scs_code,
1780 oki_load_sgr_pvt.g_all_cst_id, oki_load_sgr_pvt.g_all_pct_code,
1781 p_summary_build_date, rec_g_glpr_csr.period_type ) ;
1782 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
1783 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
1784 l_loc := 'Insert the new record.' ;
1785 l_loc := l_loc || ' -- current / previous beginning active contracts' ;
1786 -- Insert the current period data for the period
1787 oki_load_sgr_pvt.ins_seq_grw_rate (
1788 p_period_set_name => rec_g_glpr_csr.period_set_name
1789 , p_period_name => rec_g_glpr_csr.period_name
1790 , p_period_type => rec_g_glpr_csr.period_type
1791 , p_summary_build_date => p_summary_build_date
1792 , p_authoring_org_id => rec_l_org_csr.authoring_org_id
1793 , p_authoring_org_name => rec_l_org_csr.authoring_org_name
1794 , p_customer_party_id => oki_load_sgr_pvt.g_all_cst_id
1795 , p_customer_name => oki_load_sgr_pvt.g_all_cst_name
1796 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_active_k_code
1797 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
1798 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
1799 , p_curr_base_contract_amount => l_curr_k_amount
1800 , p_prev_base_contract_amount => l_prev_k_amount
1801 , p_curr_sob_contract_amount => l_curr_sob_k_amount
1802 , p_prev_sob_contract_amount => l_prev_sob_k_amount
1803 , x_retcode => l_retcode ) ;
1804 IF l_retcode = '2' THEN
1805 -- Load failed, exit immediately.
1806 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
1807 END IF ;
1808 ELSE
1812 oki_load_sgr_pvt.upd_seq_grw_rate (
1809 l_loc := 'Update the existing record.' ;
1810 l_loc := l_loc || ' -- current / previous beginning active contracts' ;
1811 -- Record already exists, so perform an update
1813 p_curr_base_contract_amount => l_curr_active_k
1814 , p_prev_base_contract_amount => l_prev_active_k
1815 , p_curr_sob_contract_amount => l_curr_sob_active_k
1816 , p_prev_sob_contract_amount => l_prev_sob_active_k
1817 , p_sgr_rowid => rec_g_sgr_csr.rowid
1818 , x_retcode => l_retcode ) ;
1819
1820 IF l_retcode = '2' THEN
1821 -- Load failed, exit immediately.
1822 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
1823 END IF ;
1824 END IF ;
1825 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
1826
1827 -- Re-initialize the amounts before calculating
1828 l_curr_k_amount := 0 ;
1829 l_prev_k_amount := 0 ;
1830 l_curr_sob_k_amount := 0 ;
1831 l_prev_sob_k_amount := 0 ;
1832
1833 l_loc := 'Opening cursor to determine the current expiring ' ;
1834 l_loc := l_loc || 'during this quarter.' ;
1835 OPEN l_expire_in_qtr_csr ( l_glpr_qtr_start_date,
1836 l_glpr_qtr_end_date, p_summary_build_date,
1837 rec_l_org_csr.authoring_org_id ) ;
1838 FETCH l_expire_in_qtr_csr INTO rec_l_expire_in_qtr_csr ;
1839 IF l_expire_in_qtr_csr%FOUND THEN
1840 l_curr_k_amount := rec_l_expire_in_qtr_csr.base_contract_amount ;
1841 l_curr_sob_k_amount := rec_l_expire_in_qtr_csr.sob_contract_amount ;
1842 END IF;
1843 CLOSE l_expire_in_qtr_csr ;
1844
1845 l_loc := 'Opening cursor to determine the previous expiring ' ;
1846 l_loc := l_loc || 'during this quarter.' ;
1847 OPEN l_expire_in_qtr_csr ( l_sqpy_glpr_qtr_start_date,
1848 l_sqpy_glpr_qtr_end_date, l_py_summary_build_date,
1849 rec_l_org_csr.authoring_org_id ) ;
1850 FETCH l_expire_in_qtr_csr INTO rec_l_expire_in_qtr_csr ;
1851 IF l_expire_in_qtr_csr%FOUND THEN
1852 l_prev_k_amount := rec_l_expire_in_qtr_csr.base_contract_amount ;
1853 l_prev_sob_k_amount := rec_l_expire_in_qtr_csr.sob_contract_amount ;
1854 END IF ;
1855 CLOSE l_expire_in_qtr_csr ;
1856
1857 -- Determine running total for ending active contracts
1858 -- Subtract expiring during contract amount
1859 l_curr_end_active_k := l_curr_end_active_k + (l_curr_k_amount * -1) ;
1860 l_prev_end_active_k := l_prev_end_active_k + (l_prev_k_amount * -1) ;
1861 l_curr_sob_end_active_k := l_curr_sob_end_active_k + (l_curr_sob_k_amount * -1) ;
1862 l_prev_sob_end_active_k := l_prev_sob_end_active_k + (l_prev_sob_k_amount * -1) ;
1863
1864 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
1865 l_loc := l_loc || ' -- current / previous expiring during quarter' ;
1866 -- Determine if the record is a new one or an existing one
1867 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
1868 rec_g_glpr_csr.period_name, rec_l_org_csr.authoring_org_id,
1869 oki_load_sgr_pvt.g_exp_in_qtr_code, oki_load_sgr_pvt.g_all_scs_code,
1870 oki_load_sgr_pvt.g_all_cst_id, oki_load_sgr_pvt.g_all_pct_code,
1871 p_summary_build_date, rec_g_glpr_csr.period_type ) ;
1872 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
1873 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
1874 l_loc := 'Insert the new record.' ;
1875 l_loc := l_loc || ' -- current / previous expiring during quarter' ;
1876 -- Insert the current period data for the period
1877 oki_load_sgr_pvt.ins_seq_grw_rate (
1878 p_period_set_name => rec_g_glpr_csr.period_set_name
1879 , p_period_name => rec_g_glpr_csr.period_name
1880 , p_period_type => rec_g_glpr_csr.period_type
1881 , p_summary_build_date => p_summary_build_date
1882 , p_authoring_org_id => rec_l_org_csr.authoring_org_id
1883 , p_authoring_org_name => rec_l_org_csr.authoring_org_name
1884 , p_customer_party_id => oki_load_sgr_pvt.g_all_cst_id
1885 , p_customer_name => oki_load_sgr_pvt.g_all_cst_name
1886 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_exp_in_qtr_code
1887 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
1888 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
1889 , p_curr_base_contract_amount => l_curr_k_amount
1890 , p_prev_base_contract_amount => l_prev_k_amount
1891 , p_curr_sob_contract_amount => l_curr_sob_k_amount
1892 , p_prev_sob_contract_amount => l_prev_sob_k_amount
1893 , x_retcode => l_retcode ) ;
1894 IF l_retcode = '2' THEN
1895 -- Load failed, exit immediately.
1896 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
1897 END IF ;
1898 ELSE
1899 l_loc := 'Update the existing record.' ;
1900 l_loc := l_loc || ' -- current / previous expiring during quarter' ;
1901 -- Record already exists, so perform an update
1902 oki_load_sgr_pvt.upd_seq_grw_rate (
1903 p_curr_base_contract_amount => l_curr_k_amount
1904 , p_prev_base_contract_amount => l_prev_k_amount
1905 , p_curr_sob_contract_amount => l_curr_sob_k_amount
1906 , p_prev_sob_contract_amount => l_prev_sob_k_amount
1910 -- Load failed, exit immediately.
1907 , p_sgr_rowid => rec_g_sgr_csr.rowid
1908 , x_retcode => l_retcode ) ;
1909 IF l_retcode = '2' THEN
1911 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
1912 END IF ;
1913 END IF ;
1914 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
1915
1916 -- Re-initialize the amounts before calculating
1917 l_curr_k_amount := 0 ;
1918 l_prev_k_amount := 0 ;
1919 l_curr_sob_k_amount := 0 ;
1920 l_prev_sob_k_amount := 0 ;
1921
1922 l_loc := 'Opening cursor to determine the current quarter ' ;
1923 l_loc := l_loc || 'contracts renewed.' ;
1924 OPEN l_qtr_k_rnw_csr ( l_glpr_qtr_start_date,
1925 p_summary_build_date, rec_l_org_csr.authoring_org_id ) ;
1926 FETCH l_qtr_k_rnw_csr INTO rec_l_qtr_k_rnw_csr ;
1927 IF l_qtr_k_rnw_csr%FOUND THEN
1928 l_curr_k_amount := rec_l_qtr_k_rnw_csr.base_contract_amount ;
1929 l_curr_sob_k_amount := rec_l_qtr_k_rnw_csr.sob_contract_amount ;
1930 END IF;
1931 CLOSE l_qtr_k_rnw_csr ;
1932
1933 l_loc := 'Opening cursor to determine the previous quarter ' ;
1934 l_loc := l_loc || 'contracts renewed.' ;
1935 OPEN l_qtr_k_rnw_csr ( l_sqpy_glpr_qtr_start_date,
1936 l_py_summary_build_date, rec_l_org_csr.authoring_org_id ) ;
1937 FETCH l_qtr_k_rnw_csr INTO rec_l_qtr_k_rnw_csr ;
1938 IF l_qtr_k_rnw_csr%FOUND THEN
1939 l_prev_k_amount := rec_l_qtr_k_rnw_csr.base_contract_amount ;
1940 l_prev_sob_k_amount := rec_l_qtr_k_rnw_csr.sob_contract_amount ;
1941 END IF ;
1942 CLOSE l_qtr_k_rnw_csr ;
1943
1944 -- Determine running total for ending active contracts
1945 -- Add quarter contracts renewed amount
1946 l_curr_end_active_k := l_curr_end_active_k + l_curr_k_amount ;
1947 l_prev_end_active_k := l_prev_end_active_k + l_prev_k_amount ;
1948 l_curr_sob_end_active_k := l_curr_sob_end_active_k + l_curr_sob_k_amount ;
1949 l_prev_sob_end_active_k := l_prev_sob_end_active_k + l_prev_sob_k_amount ;
1950
1951 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
1952 l_loc := l_loc || ' -- current / previous quarter contracts renewed' ;
1953 -- Determine if the record is a new one or an existing one
1954 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
1955 rec_g_glpr_csr.period_name, rec_l_org_csr.authoring_org_id,
1956 oki_load_sgr_pvt.g_qtr_k_rnw_code, oki_load_sgr_pvt.g_all_scs_code,
1957 oki_load_sgr_pvt.g_all_cst_id, oki_load_sgr_pvt.g_all_pct_code,
1958 p_summary_build_date, rec_g_glpr_csr.period_type ) ;
1959 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
1960 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
1961 l_loc := 'Insert the new record.' ;
1962 l_loc := l_loc || ' -- current / previous quarter contracts renewed' ;
1963 -- Insert the current period data for the period
1964 oki_load_sgr_pvt.ins_seq_grw_rate (
1965 p_period_set_name => rec_g_glpr_csr.period_set_name
1966 , p_period_name => rec_g_glpr_csr.period_name
1967 , p_period_type => rec_g_glpr_csr.period_type
1968 , p_summary_build_date => p_summary_build_date
1969 , p_authoring_org_id => rec_l_org_csr.authoring_org_id
1970 , p_authoring_org_name => rec_l_org_csr.authoring_org_name
1971 , p_customer_party_id => oki_load_sgr_pvt.g_all_cst_id
1972 , p_customer_name => oki_load_sgr_pvt.g_all_cst_name
1973 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_qtr_k_rnw_code
1974 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
1975 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
1976 , p_curr_base_contract_amount => l_curr_k_amount
1977 , p_prev_base_contract_amount => l_prev_k_amount
1978 , p_curr_sob_contract_amount => l_curr_sob_k_amount
1979 , p_prev_sob_contract_amount => l_prev_sob_k_amount
1980 , x_retcode => l_retcode ) ;
1981 IF l_retcode = '2' THEN
1982 -- Load failed, exit immediately.
1983 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
1984 END IF ;
1985 ELSE
1986 l_loc := 'Update the existing record.' ;
1987 l_loc := l_loc || ' -- current / previous quarter contracts renewed' ;
1988 -- Record already exists, so perform an update
1989 oki_load_sgr_pvt.upd_seq_grw_rate (
1990 p_curr_base_contract_amount => l_curr_k_amount
1991 , p_prev_base_contract_amount => l_prev_k_amount
1992 , p_curr_sob_contract_amount => l_curr_sob_k_amount
1993 , p_prev_sob_contract_amount => l_prev_sob_k_amount
1994 , p_sgr_rowid => rec_g_sgr_csr.rowid
1995 , x_retcode => l_retcode ) ;
1996 IF l_retcode = '2' THEN
1997 -- Load failed, exit immediately.
1998 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
1999 END IF ;
2000 END IF ;
2001 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
2002
2003 -- Re-initialize the amounts before calculating
2004 l_curr_k_amount := 0 ;
2005 l_prev_k_amount := 0 ;
2006 l_curr_sob_k_amount := 0 ;
2007 l_prev_sob_k_amount := 0 ;
2008
2009 l_loc := 'Opening cursor to determine the current backlog' ;
2013 FETCH l_bklg_k_rnw_csr INTO rec_l_bklg_k_rnw_csr ;
2010 l_loc := l_loc || 'contracts renewed.' ;
2011 OPEN l_bklg_k_rnw_csr ( l_glpr_qtr_start_date,
2012 p_summary_build_date, rec_l_org_csr.authoring_org_id ) ;
2014 IF l_bklg_k_rnw_csr%FOUND THEN
2015 l_curr_k_amount := rec_l_bklg_k_rnw_csr.base_contract_amount ;
2016 l_curr_sob_k_amount := rec_l_bklg_k_rnw_csr.sob_contract_amount ;
2017 END IF;
2018 CLOSE l_bklg_k_rnw_csr ;
2019
2020 l_loc := 'Opening cursor to determine the previous backlog' ;
2021 l_loc := l_loc || 'contracts renewed.' ;
2022 OPEN l_bklg_k_rnw_csr ( l_sqpy_glpr_qtr_start_date,
2023 l_py_summary_build_date, rec_l_org_csr.authoring_org_id ) ;
2024 FETCH l_bklg_k_rnw_csr INTO rec_l_bklg_k_rnw_csr ;
2025 IF l_bklg_k_rnw_csr%FOUND THEN
2026 l_prev_k_amount := rec_l_bklg_k_rnw_csr.base_contract_amount ;
2027 l_prev_sob_k_amount := rec_l_bklg_k_rnw_csr.sob_contract_amount ;
2028 END IF;
2029 CLOSE l_bklg_k_rnw_csr ;
2030
2031 -- Determine running total for ending active contracts
2032 -- Add backlog contracts renewed amount
2033 l_curr_end_active_k := l_curr_end_active_k + l_curr_k_amount ;
2034 l_prev_end_active_k := l_prev_end_active_k + l_prev_k_amount ;
2035 l_curr_sob_end_active_k := l_curr_sob_end_active_k + l_curr_sob_k_amount ;
2036 l_prev_sob_end_active_k := l_prev_sob_end_active_k + l_prev_sob_k_amount ;
2037
2038 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
2039 l_loc := l_loc || ' -- current / previous backlog contracts renewed' ;
2040 -- Determine if the record is a new one or an existing one
2041 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
2042 rec_g_glpr_csr.period_name, rec_l_org_csr.authoring_org_id,
2043 oki_load_sgr_pvt.g_bklg_k_rnw_code, oki_load_sgr_pvt.g_all_scs_code,
2044 oki_load_sgr_pvt.g_all_cst_id, oki_load_sgr_pvt.g_all_pct_code,
2045 p_summary_build_date, rec_g_glpr_csr.period_type ) ;
2046 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
2047 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
2048 l_loc := 'Insert the new record.' ;
2049 l_loc := l_loc || ' -- current / previous backlog contracts renewed' ;
2050 -- Insert the current period data for the period
2051 oki_load_sgr_pvt.ins_seq_grw_rate (
2052 p_period_set_name => rec_g_glpr_csr.period_set_name
2053 , p_period_name => rec_g_glpr_csr.period_name
2054 , p_period_type => rec_g_glpr_csr.period_type
2055 , p_summary_build_date => p_summary_build_date
2056 , p_authoring_org_id => rec_l_org_csr.authoring_org_id
2057 , p_authoring_org_name => rec_l_org_csr.authoring_org_name
2058 , p_customer_party_id => oki_load_sgr_pvt.g_all_cst_id
2059 , p_customer_name => oki_load_sgr_pvt.g_all_cst_name
2060 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_bklg_k_rnw_code
2061 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
2062 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
2063 , p_curr_base_contract_amount => l_curr_k_amount
2064 , p_prev_base_contract_amount => l_prev_k_amount
2065 , p_curr_sob_contract_amount => l_curr_sob_k_amount
2066 , p_prev_sob_contract_amount => l_prev_sob_k_amount
2067 , x_retcode => l_retcode ) ;
2068 IF l_retcode = '2' THEN
2069 -- Load failed, exit immediately.
2070 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
2071 END IF ;
2072 ELSE
2073 l_loc := 'Update the existing record.' ;
2074 l_loc := l_loc || ' -- current / previous backlog contracts renewed' ;
2075 -- Record already exists, so perform an update
2076 oki_load_sgr_pvt.upd_seq_grw_rate (
2077 p_curr_base_contract_amount => l_curr_k_amount
2078 , p_prev_base_contract_amount => l_prev_k_amount
2079 , p_curr_sob_contract_amount => l_curr_sob_k_amount
2080 , p_prev_sob_contract_amount => l_prev_sob_k_amount
2081 , p_sgr_rowid => rec_g_sgr_csr.rowid
2082 , x_retcode => l_retcode ) ;
2083 IF l_retcode = '2' THEN
2084 -- Load failed, exit immediately.
2085 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
2086 END IF ;
2087 END IF ;
2088 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
2089
2090 -- Re-initialize the amounts before calculating
2091 l_curr_k_amount := 0 ;
2092 l_prev_k_amount := 0 ;
2093 l_curr_sob_k_amount := 0 ;
2094 l_prev_sob_k_amount := 0 ;
2095
2096 l_loc := 'Opening cursor to determine the current new business.' ;
2097 OPEN l_new_bsn_csr ( l_glpr_qtr_start_date,
2098 p_summary_build_date, rec_l_org_csr.authoring_org_id ) ;
2099 FETCH l_new_bsn_csr INTO rec_l_new_bsn_csr ;
2100 IF l_new_bsn_csr%FOUND THEN
2101 l_curr_k_amount := rec_l_new_bsn_csr.base_contract_amount ;
2102 l_curr_sob_k_amount := rec_l_new_bsn_csr.sob_contract_amount ;
2103 END IF;
2104 CLOSE l_new_bsn_csr ;
2105
2106 l_loc := 'Opening cursor to determine the previous new business.' ;
2107 OPEN l_new_bsn_csr ( l_sqpy_glpr_qtr_start_date,
2108 l_py_summary_build_date, rec_l_org_csr.authoring_org_id ) ;
2112 l_prev_sob_k_amount := rec_l_new_bsn_csr.sob_contract_amount ;
2109 FETCH l_new_bsn_csr INTO rec_l_new_bsn_csr ;
2110 IF l_new_bsn_csr%FOUND THEN
2111 l_prev_k_amount := rec_l_new_bsn_csr.base_contract_amount ;
2113 END IF ;
2114 CLOSE l_new_bsn_csr ;
2115
2116 -- Determine running total for ending active contracts
2117 -- Add new business amount
2118 l_curr_end_active_k := l_curr_end_active_k + l_curr_k_amount ;
2119 l_prev_end_active_k := l_prev_end_active_k + l_prev_k_amount ;
2120 l_curr_sob_end_active_k := l_curr_sob_end_active_k + l_curr_sob_k_amount ;
2121 l_prev_sob_end_active_k := l_prev_sob_end_active_k + l_prev_sob_k_amount ;
2122
2123 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
2124 l_loc := l_loc || ' -- current / previous new business' ;
2125 -- Determine if the record is a new one or an existing one
2126 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
2127 rec_g_glpr_csr.period_name, rec_l_org_csr.authoring_org_id,
2128 oki_load_sgr_pvt.g_new_bsn_code, oki_load_sgr_pvt.g_all_scs_code,
2129 oki_load_sgr_pvt.g_all_cst_id, oki_load_sgr_pvt.g_all_pct_code,
2130 p_summary_build_date, rec_g_glpr_csr.period_type ) ;
2131 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
2132 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
2133 l_loc := 'Insert the new record.' ;
2134 l_loc := l_loc || ' -- current / previous new business' ;
2135 -- Insert the current period data for the period
2136 oki_load_sgr_pvt.ins_seq_grw_rate (
2137 p_period_set_name => rec_g_glpr_csr.period_set_name
2138 , p_period_name => rec_g_glpr_csr.period_name
2139 , p_period_type => rec_g_glpr_csr.period_type
2140 , p_summary_build_date => p_summary_build_date
2141 , p_authoring_org_id => rec_l_org_csr.authoring_org_id
2142 , p_authoring_org_name => rec_l_org_csr.authoring_org_name
2143 , p_customer_party_id => oki_load_sgr_pvt.g_all_cst_id
2144 , p_customer_name => oki_load_sgr_pvt.g_all_cst_name
2145 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_new_bsn_code
2146 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
2147 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
2148 , p_curr_base_contract_amount => l_curr_k_amount
2149 , p_prev_base_contract_amount => l_prev_k_amount
2150 , p_curr_sob_contract_amount => l_curr_sob_k_amount
2151 , p_prev_sob_contract_amount => l_prev_sob_k_amount
2152 , x_retcode => l_retcode ) ;
2153 IF l_retcode = '2' THEN
2154 -- Load failed, exit immediately.
2155 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
2156 END IF ;
2157 ELSE
2158 l_loc := 'Update the existing record.' ;
2159 l_loc := l_loc || ' -- current / previous new business' ;
2160 -- Record already exists, so perform an update
2161 oki_load_sgr_pvt.upd_seq_grw_rate (
2162 p_curr_base_contract_amount => l_curr_k_amount
2163 , p_prev_base_contract_amount => l_prev_k_amount
2164 , p_curr_sob_contract_amount => l_curr_sob_k_amount
2165 , p_prev_sob_contract_amount => l_prev_sob_k_amount
2166 , p_sgr_rowid => rec_g_sgr_csr.rowid
2167 , x_retcode => l_retcode ) ;
2168 IF l_retcode = '2' THEN
2169 -- Load failed, exit immediately.
2170 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
2171 END IF ;
2172 END IF ;
2173 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
2174
2175 -- Re-initialize the amounts before calculating
2176 l_curr_k_amount := 0 ;
2177 l_prev_k_amount := 0 ;
2178 l_curr_sob_k_amount := 0 ;
2179 l_prev_sob_k_amount := 0 ;
2180
2181 l_loc := 'Opening cursor to determine the current cancelled renewals.' ;
2182 OPEN l_cncl_rnwl_csr( l_glpr_qtr_start_date,
2183 p_summary_build_date, rec_l_org_csr.authoring_org_id ) ;
2184 FETCH l_cncl_rnwl_csr INTO rec_l_cncl_rnwl_csr ;
2185 IF l_cncl_rnwl_csr%FOUND THEN
2186 l_curr_k_amount := rec_l_cncl_rnwl_csr.base_contract_amount ;
2187 l_curr_sob_k_amount := rec_l_cncl_rnwl_csr.sob_contract_amount ;
2188 END IF;
2189 CLOSE l_cncl_rnwl_csr ;
2190
2191 l_loc := 'Opening cursor to determine the previous cancelled renewals.' ;
2192 OPEN l_cncl_rnwl_csr( l_sqpy_glpr_qtr_start_date,
2193 l_py_summary_build_date, rec_l_org_csr.authoring_org_id ) ;
2194 FETCH l_cncl_rnwl_csr INTO rec_l_cncl_rnwl_csr ;
2195 IF l_cncl_rnwl_csr%FOUND THEN
2196 l_prev_k_amount := rec_l_cncl_rnwl_csr.base_contract_amount ;
2197 l_prev_sob_k_amount := rec_l_cncl_rnwl_csr.sob_contract_amount ;
2198 END IF ;
2199 CLOSE l_cncl_rnwl_csr ;
2200
2201 -- Determine running total for ending active contracts
2202 -- Subtract cancelled contract amount
2203 l_curr_end_active_k := l_curr_end_active_k + (l_curr_k_amount * -1) ;
2204 l_prev_end_active_k := l_prev_end_active_k + (l_prev_k_amount * -1) ;
2205 l_curr_sob_end_active_k := l_curr_sob_end_active_k + (l_curr_sob_k_amount * -1) ;
2206 l_prev_sob_end_active_k := l_prev_sob_end_active_k + (l_prev_sob_k_amount * -1) ;
2207
2211 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
2208 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
2209 l_loc := l_loc || ' -- current / previous cancelled contract' ;
2210 -- Determine if the record is a new one or an existing one
2212 rec_g_glpr_csr.period_name, rec_l_org_csr.authoring_org_id,
2213 oki_load_sgr_pvt.g_cncl_rnwl_code, oki_load_sgr_pvt.g_all_scs_code,
2214 oki_load_sgr_pvt.g_all_cst_id, oki_load_sgr_pvt.g_all_pct_code,
2215 p_summary_build_date, rec_g_glpr_csr.period_type ) ;
2216 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
2217 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
2218 l_loc := 'Insert the new record.' ;
2219 l_loc := l_loc || ' -- current / previous cancelled renewals' ;
2220 -- Insert the current period data for the period
2221 oki_load_sgr_pvt.ins_seq_grw_rate (
2222 p_period_set_name => rec_g_glpr_csr.period_set_name
2223 , p_period_name => rec_g_glpr_csr.period_name
2224 , p_period_type => rec_g_glpr_csr.period_type
2225 , p_summary_build_date => p_summary_build_date
2226 , p_authoring_org_id => rec_l_org_csr.authoring_org_id
2227 , p_authoring_org_name => rec_l_org_csr.authoring_org_name
2228 , p_customer_party_id => oki_load_sgr_pvt.g_all_cst_id
2229 , p_customer_name => oki_load_sgr_pvt.g_all_cst_name
2230 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_cncl_rnwl_code
2231 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
2232 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
2233 , p_curr_base_contract_amount => l_curr_k_amount
2234 , p_prev_base_contract_amount => l_prev_k_amount
2235 , p_curr_sob_contract_amount => l_curr_sob_k_amount
2236 , p_prev_sob_contract_amount => l_prev_sob_k_amount
2237 , x_retcode => l_retcode ) ;
2238 IF l_retcode = '2' THEN
2239 -- Load failed, exit immediately.
2240 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
2241 END IF ;
2242 ELSE
2243 l_loc := 'Update the existing record.' ;
2244 l_loc := l_loc || ' -- current / previous cancelled renewals' ;
2245 -- Record already exists, so perform an update
2246 oki_load_sgr_pvt.upd_seq_grw_rate (
2247 p_curr_base_contract_amount => l_curr_k_amount
2248 , p_prev_base_contract_amount => l_prev_k_amount
2249 , p_curr_sob_contract_amount => l_curr_sob_k_amount
2250 , p_prev_sob_contract_amount => l_prev_sob_k_amount
2251 , p_sgr_rowid => rec_g_sgr_csr.rowid
2252 , x_retcode => l_retcode ) ;
2253 IF l_retcode = '2' THEN
2254 -- Load failed, exit immediately.
2255 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
2256 END IF ;
2257 END IF ;
2258 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
2259
2260 -- Re-initialize the amounts before calculating
2261 l_curr_k_amount := 0 ;
2262 l_prev_k_amount := 0 ;
2263 l_curr_sob_k_amount := 0 ;
2264 l_prev_sob_k_amount := 0 ;
2265
2266 l_loc := 'Looping through all the current contracts terminated in the period.';
2267 << l_trmn_rnwl_csr_loop >>
2268 -- Loop through all the contracts terminated in the period
2269 FOR rec_l_trmn_rnwl_csr IN l_trmn_rnwl_csr ( l_glpr_qtr_start_date,
2270 p_summary_build_date, rec_l_org_csr.authoring_org_id ) LOOP
2271 l_curr_k_amount := l_curr_k_amount +
2272 rec_l_trmn_rnwl_csr.base_contract_amount ;
2273 l_curr_sob_k_amount := l_curr_sob_k_amount +
2274 rec_l_trmn_rnwl_csr.sob_contract_amount ;
2275 END LOOP l_trmn_rnwl_csr_loop ;
2276 l_curr_k_amount := ROUND(l_curr_k_amount, 2) ;
2277 l_curr_sob_k_amount := ROUND(l_curr_sob_k_amount, 2) ;
2278
2279 l_loc := 'Looping through all the previous contracts terminated in the period.';
2280 << l_trmn_rnwl_csr_loop >>
2281 -- Loop through all the contracts terminated in the period
2282 FOR rec_l_trmn_rnwl_csr IN l_trmn_rnwl_csr ( l_sqpy_glpr_qtr_start_date,
2283 l_py_summary_build_date, rec_l_org_csr.authoring_org_id ) LOOP
2284 l_prev_k_amount := l_prev_k_amount +
2285 rec_l_trmn_rnwl_csr.base_contract_amount ;
2286 l_prev_sob_k_amount := l_prev_sob_k_amount +
2287 rec_l_trmn_rnwl_csr.sob_contract_amount ;
2288 END LOOP l_trmn_rnwl_csr_loop ;
2289 l_prev_k_amount := ROUND(l_prev_k_amount, 2) ;
2290 l_prev_sob_k_amount := ROUND(l_prev_sob_k_amount, 2) ;
2291
2292 -- Determine running total for ending active contracts
2293 -- Subtract terminated contract amount
2294 l_curr_end_active_k := l_curr_end_active_k + (l_curr_k_amount * -1) ;
2295 l_prev_end_active_k := l_prev_end_active_k + (l_prev_k_amount * -1) ;
2296 l_curr_sob_end_active_k := l_curr_sob_end_active_k + (l_curr_sob_k_amount * -1) ;
2297 l_prev_sob_end_active_k := l_prev_sob_end_active_k + (l_prev_sob_k_amount * -1) ;
2298
2299 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
2300 l_loc := l_loc || ' -- current / previous terminated renewals' ;
2301 -- Determine if the record is a new one or an existing one
2302 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
2303 rec_g_glpr_csr.period_name, rec_l_org_csr.authoring_org_id,
2307 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
2304 oki_load_sgr_pvt.g_seq_trmn_k_code, oki_load_sgr_pvt.g_all_scs_code,
2305 oki_load_sgr_pvt.g_all_cst_id, oki_load_sgr_pvt.g_all_pct_code,
2306 p_summary_build_date, rec_g_glpr_csr.period_type ) ;
2308 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
2309 l_loc := 'Insert the new record.' ;
2310 l_loc := l_loc || ' -- current / previous terminated renewals' ;
2311 -- Insert the current period data for the period
2312 oki_load_sgr_pvt.ins_seq_grw_rate (
2313 p_period_set_name => rec_g_glpr_csr.period_set_name
2314 , p_period_name => rec_g_glpr_csr.period_name
2315 , p_period_type => rec_g_glpr_csr.period_type
2316 , p_summary_build_date => p_summary_build_date
2317 , p_authoring_org_id => rec_l_org_csr.authoring_org_id
2318 , p_authoring_org_name => rec_l_org_csr.authoring_org_name
2319 , p_customer_party_id => oki_load_sgr_pvt.g_all_cst_id
2320 , p_customer_name => oki_load_sgr_pvt.g_all_cst_name
2321 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_seq_trmn_k_code
2322 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
2323 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
2324 , p_curr_base_contract_amount => l_curr_k_amount
2325 , p_prev_base_contract_amount => l_prev_k_amount
2326 , p_curr_sob_contract_amount => l_curr_sob_k_amount
2327 , p_prev_sob_contract_amount => l_prev_sob_k_amount
2328 , x_retcode => l_retcode ) ;
2329 IF l_retcode = '2' THEN
2330 -- Load failed, exit immediately.
2331 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
2332 END IF ;
2333 ELSE
2334 l_loc := 'Update the existing record.' ;
2335 l_loc := l_loc || ' -- current / previous terminated renewals' ;
2336 -- Record already exists, so perform an update
2337 oki_load_sgr_pvt.upd_seq_grw_rate (
2338 p_curr_base_contract_amount => l_curr_k_amount
2339 , p_prev_base_contract_amount => l_prev_k_amount
2340 , p_curr_sob_contract_amount => l_curr_sob_k_amount
2341 , p_prev_sob_contract_amount => l_prev_sob_k_amount
2342 , p_sgr_rowid => rec_g_sgr_csr.rowid
2343 , x_retcode => l_retcode ) ;
2344 IF l_retcode = '2' THEN
2345 -- Load failed, exit immediately.
2346 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
2347 END IF ;
2348 END IF ;
2349 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
2350
2351 -- Re-initialize the amounts before calculating
2352 l_curr_k_amount := 0 ;
2353 l_prev_k_amount := 0 ;
2354 l_curr_sob_k_amount := 0 ;
2355 l_prev_sob_k_amount := 0 ;
2356
2357 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
2358 l_loc := l_loc || ' -- current / previous ending active contracts' ;
2359 -- Determine if the record is a new one or an existing one
2360 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
2361 rec_g_glpr_csr.period_name, rec_l_org_csr.authoring_org_id,
2362 oki_load_sgr_pvt.g_end_active_k_code, oki_load_sgr_pvt.g_all_scs_code,
2363 oki_load_sgr_pvt.g_all_cst_id, oki_load_sgr_pvt.g_all_pct_code,
2364 p_summary_build_date, rec_g_glpr_csr.period_type ) ;
2365 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
2366 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
2367 l_loc := 'Insert the new record.' ;
2368 l_loc := l_loc || ' -- current / previous ending active contracts' ;
2369 -- Insert the current period data for the period
2370 oki_load_sgr_pvt.ins_seq_grw_rate (
2371 p_period_set_name => rec_g_glpr_csr.period_set_name
2372 , p_period_name => rec_g_glpr_csr.period_name
2373 , p_period_type => rec_g_glpr_csr.period_type
2374 , p_summary_build_date => p_summary_build_date
2375 , p_authoring_org_id => rec_l_org_csr.authoring_org_id
2376 , p_authoring_org_name => rec_l_org_csr.authoring_org_name
2377 , p_customer_party_id => oki_load_sgr_pvt.g_all_cst_id
2378 , p_customer_name => oki_load_sgr_pvt.g_all_cst_name
2379 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_end_active_k_code
2380 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
2381 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
2382 , p_curr_base_contract_amount => l_curr_end_active_k
2383 , p_prev_base_contract_amount => l_prev_end_active_k
2384 , p_curr_sob_contract_amount => l_curr_sob_end_active_k
2385 , p_prev_sob_contract_amount => l_prev_sob_end_active_k
2386 , x_retcode => l_retcode ) ;
2387 IF l_retcode = '2' THEN
2388 -- Load failed, exit immediately.
2389 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
2390 END IF ;
2391 ELSE
2392 l_loc := 'Update the existing record.' ;
2393 l_loc := l_loc || ' -- current / previous ending active contracts' ;
2394 -- Record already exists, so perform an update
2395 oki_load_sgr_pvt.upd_seq_grw_rate (
2396 p_curr_base_contract_amount => l_curr_end_active_k
2400 , p_sgr_rowid => rec_g_sgr_csr.rowid
2397 , p_prev_base_contract_amount => l_prev_end_active_k
2398 , p_curr_sob_contract_amount => l_curr_sob_end_active_k
2399 , p_prev_sob_contract_amount => l_prev_sob_end_active_k
2401 , x_retcode => l_retcode ) ;
2402
2403 IF l_retcode = '2' THEN
2404 -- Load failed, exit immediately.
2405 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
2406 END IF ;
2407 END IF ;
2408 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
2409
2410 -- Re-initialize the amounts before calculating
2411 l_curr_k_amount := 0 ;
2412 l_prev_k_amount := 0 ;
2413 l_curr_sob_k_amount := 0 ;
2414 l_prev_sob_k_amount := 0 ;
2415
2416 -- If the denomiator is zero, then set the sequential growth rate to zero
2417 l_loc := 'Setting the sequential growth rate value.' ;
2418 IF l_curr_active_k = 0 THEN
2419 l_curr_seq_grw_rate := 0 ;
2420 ELSE
2421 l_curr_seq_grw_rate := ROUND(((l_curr_end_active_k -
2422 l_curr_active_k ) / l_curr_active_k ) * 100, 2) ;
2423 END IF ;
2424
2425 IF l_curr_sob_active_k = 0 THEN
2426 l_curr_sob_seq_grw_rate := 0 ;
2427 ELSE
2428 l_curr_sob_seq_grw_rate := ROUND(((l_curr_sob_end_active_k -
2429 l_curr_sob_active_k ) / l_curr_sob_active_k ) * 100, 2) ;
2430 END IF ;
2431
2432 IF l_prev_active_k = 0 THEN
2433 l_prev_seq_grw_rate := 0 ;
2434 ELSE
2435 l_prev_seq_grw_rate := ROUND(((l_prev_end_active_k -
2436 l_prev_active_k ) / l_prev_active_k ) * 100, 2) ;
2437 END IF ;
2438
2439 IF l_prev_sob_active_k = 0 THEN
2440 l_prev_sob_seq_grw_rate := 0 ;
2441 ELSE
2442 l_prev_sob_seq_grw_rate := ROUND(((l_prev_sob_end_active_k -
2443 l_prev_sob_active_k ) / l_prev_sob_active_k ) * 100, 2) ;
2444 END IF ;
2445
2446 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
2447 l_loc := l_loc || ' -- current / previous sequential growth rate' ;
2448 -- Determine if the record is a new one or an existing one
2449 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
2450 rec_g_glpr_csr.period_name, rec_l_org_csr.authoring_org_id,
2451 oki_load_sgr_pvt.g_seq_grw_rate_code, oki_load_sgr_pvt.g_all_scs_code,
2452 oki_load_sgr_pvt.g_all_cst_id, oki_load_sgr_pvt.g_all_pct_code,
2453 p_summary_build_date, rec_g_glpr_csr.period_type ) ;
2454 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
2455 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
2456 l_loc := 'Insert the new record.' ;
2457 l_loc := l_loc || ' -- current / previous sequential growth rate' ;
2458 -- Insert the current period data for the period
2459 oki_load_sgr_pvt.ins_seq_grw_rate (
2460 p_period_set_name => rec_g_glpr_csr.period_set_name
2461 , p_period_name => rec_g_glpr_csr.period_name
2462 , p_period_type => rec_g_glpr_csr.period_type
2463 , p_summary_build_date => p_summary_build_date
2464 , p_authoring_org_id => rec_l_org_csr.authoring_org_id
2465 , p_authoring_org_name => rec_l_org_csr.authoring_org_name
2466 , p_customer_party_id => oki_load_sgr_pvt.g_all_cst_id
2467 , p_customer_name => oki_load_sgr_pvt.g_all_cst_name
2468 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_seq_grw_rate_code
2469 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
2470 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
2471 , p_curr_base_contract_amount => l_curr_seq_grw_rate
2472 , p_prev_base_contract_amount => l_prev_seq_grw_rate
2473 , p_curr_sob_contract_amount => l_curr_sob_seq_grw_rate
2474 , p_prev_sob_contract_amount => l_prev_sob_seq_grw_rate
2475 , x_retcode => l_retcode ) ;
2476 IF l_retcode = '2' THEN
2477 -- Load failed, exit immediately.
2478 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
2479 END IF ;
2480 ELSE
2481 l_loc := 'Update the existing record.' ;
2482 l_loc := l_loc || ' -- current / previous sequential growth rate' ;
2483 -- Record already exists, so perform an update
2484 oki_load_sgr_pvt.upd_seq_grw_rate (
2485 p_curr_base_contract_amount => l_curr_seq_grw_rate
2486 , p_prev_base_contract_amount => l_prev_seq_grw_rate
2487 , p_curr_sob_contract_amount => l_curr_sob_seq_grw_rate
2488 , p_prev_sob_contract_amount => l_prev_sob_seq_grw_rate
2489 , p_sgr_rowid => rec_g_sgr_csr.rowid
2490 , x_retcode => l_retcode ) ;
2491
2492 IF l_retcode = '2' THEN
2493 -- Load failed, exit immediately.
2494 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
2495 END IF ;
2496 END IF ;
2497 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
2498
2499 END LOOP g_glpr_csr_loop ;
2500 END LOOP l_org_csr_loop ;
2501
2502 EXCEPTION
2503 WHEN oki_load_sgr_pvt.g_excp_exit_immediate THEN
2504 -- Do not log an error ; It has already been logged.
2505 -- Set return code to error
2506 x_retcode := '2' ;
2507
2508
2512
2509 WHEN OTHERS THEN
2510 l_sqlcode := SQLCODE ;
2511 l_sqlerrm := SQLERRM ;
2513 -- Set return code TO error
2514 x_retcode := '2' ;
2515
2516 fnd_message.set_name( application => 'OKI'
2517 , name => 'OKI_UNEXPECTED_FAILURE');
2518
2519 fnd_message.set_token( token => 'OBJECT_NAME'
2520 , value => 'OKI_LOAD_SGR_PVT.CALC_SGR_DTL2');
2521
2522 fnd_file.put_line( which => fnd_file.log
2523 , buff => fnd_message.get);
2524
2525 -- Log the location within the procedure where the error occurred
2526 fnd_message.set_name( application => 'OKI'
2527 , name => 'OKI_LOC_IN_PROG_FAILURE');
2528
2529 fnd_message.set_token( token => 'LOCATION'
2530 , value => l_loc);
2531
2532 fnd_file.put_line( which => fnd_file.log
2533 , buff => fnd_message.get);
2534
2535 fnd_file.put_line( which => fnd_file.log
2536 , buff => l_sqlcode||' '|| l_sqlerrm );
2537
2538 END calc_sgr_dtl2 ;
2539
2540 --------------------------------------------------------------------------------
2541 -- Procedure to calcuate the contract amount for the current and previous
2542 -- quarter / year.
2543
2544 --------------------------------------------------------------------------------
2545 PROCEDURE calc_sgr_dtl3
2546 ( p_period_set_name IN VARCHAR2
2547 , p_period_type IN VARCHAR2
2548 , p_summary_build_date IN DATE
2549 , p_ending_period_type IN vARCHAR2
2550 , x_retcode OUT VARCHAR2
2551 ) IS
2552
2553 -- Local variable declaration
2554
2555 -- For capturing the return code, 0 = success, 1 = warning, 2 = error
2556 l_retcode VARCHAR2(1) := NULL ;
2557
2558 -- For error handling
2559 l_sqlcode VARCHAR2(100) := NULL ;
2560 l_sqlerrm VARCHAR2(1000) := NULL ;
2561
2562 -- Holds the contract amount for the current and previous
2563 -- beginning active contracts
2564 l_curr_active_k NUMBER := 0 ;
2565 l_prev_active_k NUMBER := 0 ;
2566 l_curr_sob_active_k NUMBER := 0 ;
2567 l_prev_sob_active_k NUMBER := 0 ;
2568 -- Holds the contract amount for the current and previous
2569 -- ending active contracts
2570 l_curr_end_active_k NUMBER := 0 ;
2571 l_prev_end_active_k NUMBER := 0 ;
2572 l_curr_sob_end_active_k NUMBER := 0 ;
2573 l_prev_sob_end_active_k NUMBER := 0 ;
2574 -- Holds the sequetial growth rate %
2575 l_curr_seq_grw_rate NUMBER := 0 ;
2576 l_prev_seq_grw_rate NUMBER := 0 ;
2577 l_curr_sob_seq_grw_rate NUMBER := 0 ;
2578 l_prev_sob_seq_grw_rate NUMBER := 0 ;
2579 -- Holds the contract amount current and previous
2580 -- sequential growth rate records
2581 l_curr_k_amount NUMBER := 0 ;
2582 l_prev_k_amount NUMBER := 0 ;
2583 l_curr_sob_k_amount NUMBER := 0 ;
2584 l_prev_sob_k_amount NUMBER := 0 ;
2585
2586 -- Location within the program before the error was encountered.
2587 l_loc VARCHAR2(200) ;
2588
2589 -- Holds the truncated start and end dates from gl_periods
2590 -- Holds the quarter start and end dates
2591 l_glpr_qtr_start_date DATE ;
2592 l_glpr_qtr_end_date DATE ;
2593 -- Holds the year start and end dates
2594 l_glpr_year_start_date DATE ;
2595 l_glpr_year_end_date DATE ;
2596 l_period_start_date DATE ;
2597 l_period_end_date DATE ;
2598 -- Holds the prior year summary build date
2599 l_py_summary_build_date DATE ;
2600 -- Holds the start and end dates for the same quarter in the previous year
2601 l_sqpy_glpr_qtr_start_date DATE ;
2602 l_sqpy_glpr_qtr_end_date DATE ;
2603 -- Holds the start and end dates for the previous year
2604 l_py_glpr_period_start_date DATE ;
2605 l_py_glpr_period_end_date DATE ;
2606 l_py_period_start_date DATE ;
2607 l_py_period_end_date DATE ;
2608
2609 -- If the period is the build summary date, then calculate
2610 -- the period amounts
2611 l_period_end VARCHAR2(30) := 'NOT_PERIOD_END' ;
2612
2613 BEGIN
2614 -- initialize return code to success
2615 l_retcode := '0';
2616
2617 << g_glpr_csr_loop >>
2618 -- Loop through all the periods
2619 FOR rec_g_glpr_csr IN oki_utl_pvt.g_glpr_csr(
2620 p_period_set_name, p_period_type, p_summary_build_date ) LOOP
2621
2622 -- set the quarter and year gl_periods start and end dates
2623 l_glpr_qtr_start_date := trunc(rec_g_glpr_csr.quarter_start_date) ;
2624 l_glpr_qtr_end_date := ADD_MONTHS(l_glpr_qtr_start_date, 3) - 1 ;
2625 l_glpr_year_start_date := TRUNC(rec_g_glpr_csr.year_start_date) ;
2626 l_glpr_year_end_date := ADD_MONTHS(TRUNC(rec_g_glpr_csr.year_start_date), 12) - 1 ;
2627
2628 -- Set the prior year summary build date
2629 l_py_summary_build_date := ADD_MONTHS(p_summary_build_date, - 12) ;
2630 -- Set the quarter start and end dates for the same quarter
2631 -- in the previous year
2632 l_sqpy_glpr_qtr_start_date := ADD_MONTHS(l_glpr_qtr_start_date, -12) ;
2633 l_sqpy_glpr_qtr_end_date := ADD_MONTHS(l_glpr_qtr_end_date, -12) ;
2634
2635 -- Set the year start and end dates for the previous year
2636 l_py_glpr_period_start_date := ADD_MONTHS(l_glpr_year_start_date, -12) ;
2637 l_py_glpr_period_end_date := ADD_MONTHS(l_glpr_year_end_date, -12) ;
2638
2642 -- Set up the current and previous start and end dates
2639 IF p_ending_period_type = 'Quarter' THEN
2640 IF p_summary_build_date = l_glpr_qtr_end_date THEN
2641 -- The summary build date is the quarter end date
2643 -- for the quarter
2644 l_period_start_date := l_glpr_qtr_start_date ;
2645 l_period_end_date := l_glpr_qtr_end_date ;
2646 l_py_period_start_date := l_sqpy_glpr_qtr_start_date ;
2647 l_py_period_end_date := l_sqpy_glpr_qtr_end_date ;
2648 l_period_end := 'PERIOD_END' ;
2649 END IF ;
2650 ELSIF p_ending_period_type = 'Year' THEN
2651 IF p_summary_build_date = l_glpr_year_end_date THEN
2652 -- The summary build date is the year end date
2653 -- Set up the current and previous start and end dates
2654 -- for the year
2655 l_period_start_date := l_glpr_year_start_date ;
2656 l_period_end_date := l_glpr_year_end_date ;
2657 l_period_end := 'PERIOD_END' ;
2658 l_py_period_start_date := l_py_glpr_period_start_date ;
2659 l_py_period_end_date := l_py_glpr_period_end_date ;
2660 END IF ;
2661 END IF ;
2662
2663 IF l_period_end = 'PERIOD_END' THEN
2664 l_period_end := 'NOT_PERIOD_END' ;
2665
2666 -- Re-initialize the amounts before calculating
2667 l_curr_active_k := 0 ;
2668 l_prev_active_k := 0 ;
2669 l_curr_sob_active_k := 0 ;
2670 l_prev_sob_active_k := 0 ;
2671 l_curr_end_active_k := 0 ;
2672 l_prev_end_active_k := 0 ;
2673 l_curr_sob_end_active_k := 0 ;
2674 l_prev_sob_end_active_k := 0 ;
2675 l_curr_seq_grw_rate := 0 ;
2676 l_prev_seq_grw_rate := 0 ;
2677 l_curr_sob_seq_grw_rate := 0 ;
2678 l_prev_sob_seq_grw_rate := 0 ;
2679 l_curr_k_amount := 0 ;
2680 l_prev_k_amount := 0 ;
2681 l_curr_sob_k_amount := 0 ;
2682 l_prev_sob_k_amount := 0 ;
2683
2684
2685 l_loc := 'Opening cursor to determine the current beginning ' ;
2686 l_loc := l_loc || 'active contracts.' ;
2687 OPEN oki_load_sgr_pvt.g_active_k_csr ( p_summary_build_date ) ;
2688 FETCH oki_load_sgr_pvt.g_active_k_csr INTO rec_g_active_k_csr ;
2689 IF oki_load_sgr_pvt.g_active_k_csr%FOUND THEN
2690 l_curr_k_amount := rec_g_active_k_csr.base_contract_amount ;
2691 l_curr_sob_k_amount := rec_g_active_k_csr.sob_contract_amount ;
2692 -- keep the beginning active amount to determine the sequential
2693 -- growth rate later
2694 l_curr_active_k := l_curr_k_amount ;
2695 l_curr_sob_active_k := l_curr_sob_k_amount ;
2696 END IF;
2697 CLOSE oki_load_sgr_pvt.g_active_k_csr ;
2698
2699 l_loc := 'Opening cursor to determine the previous beginning ' ;
2700 l_loc := l_loc || 'active contracts.' ;
2701 OPEN oki_load_sgr_pvt.g_active_k_csr ( l_py_summary_build_date ) ;
2702 FETCH oki_load_sgr_pvt.g_active_k_csr INTO rec_g_active_k_csr ;
2703 IF oki_load_sgr_pvt.g_active_k_csr%FOUND THEN
2704 l_prev_k_amount := rec_g_active_k_csr.base_contract_amount ;
2705 l_prev_sob_k_amount := rec_g_active_k_csr.sob_contract_amount ;
2706 -- keep the beginning active amount to determine the sequential
2707 -- growth rate later
2708 l_prev_active_k := l_prev_k_amount ;
2709 l_prev_sob_active_k := l_prev_sob_k_amount ;
2710 END IF ;
2711 CLOSE oki_load_sgr_pvt.g_active_k_csr ;
2712
2713 -- Determine running total for ending active contracts
2714 -- Add beginning active contract amount
2715 l_curr_end_active_k := l_curr_k_amount ;
2716 l_prev_end_active_k := l_prev_k_amount ;
2717 l_curr_sob_end_active_k := l_curr_sob_k_amount ;
2718 l_prev_sob_end_active_k := l_prev_sob_k_amount ;
2719
2720 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
2721 l_loc := l_loc || ' -- current / previous beginning active contracts' ;
2722 -- Determine if the record is a new one or an existing one
2723 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
2724 rec_g_glpr_csr.period_name, oki_load_sgr_pvt.g_all_org_id,
2725 oki_load_sgr_pvt.g_active_k_code, oki_load_sgr_pvt.g_all_scs_code,
2726 oki_load_sgr_pvt.g_all_cst_id, oki_load_sgr_pvt.g_all_pct_code,
2727 p_summary_build_date, p_ending_period_type ) ;
2728 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
2729 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
2730 l_loc := 'Insert the new record.' ;
2731 l_loc := l_loc || ' -- current / previous beginning active contracts' ;
2732 -- Insert the current period data for the period
2733 oki_load_sgr_pvt.ins_seq_grw_rate (
2734 p_period_set_name => rec_g_glpr_csr.period_set_name
2735 , p_period_name => rec_g_glpr_csr.period_name
2736 , p_period_type => p_ending_period_type
2737 , p_summary_build_date => p_summary_build_date
2738 , p_authoring_org_id => oki_load_sgr_pvt.g_all_org_id
2739 , p_authoring_org_name => oki_load_sgr_pvt.g_all_org_name
2740 , p_customer_party_id => oki_load_sgr_pvt.g_all_cst_id
2741 , p_customer_name => oki_load_sgr_pvt.g_all_cst_name
2742 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_active_k_code
2746 , p_prev_base_contract_amount => l_prev_k_amount
2743 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
2744 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
2745 , p_curr_base_contract_amount => l_curr_k_amount
2747 , p_curr_sob_contract_amount => l_curr_sob_k_amount
2748 , p_prev_sob_contract_amount => l_prev_sob_k_amount
2749 , x_retcode => l_retcode ) ;
2750 IF l_retcode = '2' THEN
2751 -- Load failed, exit immediately.
2752 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
2753 END IF ;
2754 ELSE
2755 l_loc := 'Update the existing record.' ;
2756 l_loc := l_loc || ' -- current / previous beginning active contracts' ;
2757 -- Record already exists, so perform an update
2758 oki_load_sgr_pvt.upd_seq_grw_rate (
2759 p_curr_base_contract_amount => l_curr_active_k
2760 , p_prev_base_contract_amount => l_prev_active_k
2761 , p_curr_sob_contract_amount => l_curr_sob_active_k
2762 , p_prev_sob_contract_amount => l_prev_sob_active_k
2763 , p_sgr_rowid => rec_g_sgr_csr.rowid
2764 , x_retcode => l_retcode ) ;
2765 IF l_retcode = '2' THEN
2766 -- Load failed, exit immediately.
2767 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
2768 END IF ;
2769 END IF ;
2770 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
2771
2772 -- Re-initialize the amounts before calculating
2773 l_curr_k_amount := 0 ;
2774 l_prev_k_amount := 0 ;
2775 l_curr_sob_k_amount := 0 ;
2776 l_prev_sob_k_amount := 0 ;
2777
2778 l_loc := 'Opening cursor to determine the current expiring ' ;
2779 l_loc := l_loc || 'during this quarter.' ;
2780
2781 OPEN oki_load_sgr_pvt.g_expire_in_qtr_csr ( l_period_start_date,
2782 l_period_end_date, p_summary_build_date ) ;
2783 FETCH oki_load_sgr_pvt.g_expire_in_qtr_csr INTO rec_g_expire_in_qtr_csr ;
2784 IF oki_load_sgr_pvt.g_expire_in_qtr_csr%FOUND THEN
2785 l_curr_k_amount := rec_g_expire_in_qtr_csr.base_contract_amount ;
2786 l_curr_sob_k_amount := rec_g_expire_in_qtr_csr.sob_contract_amount ;
2787 END IF;
2788 CLOSE oki_load_sgr_pvt.g_expire_in_qtr_csr ;
2789
2790 l_loc := 'Opening cursor to determine the previous expiring ' ;
2791 l_loc := l_loc || 'during this quarter.' ;
2792 OPEN oki_load_sgr_pvt.g_expire_in_qtr_csr ( l_py_period_start_date,
2793 l_py_period_end_date, l_py_summary_build_date ) ;
2794 FETCH oki_load_sgr_pvt.g_expire_in_qtr_csr INTO rec_g_expire_in_qtr_csr ;
2795 IF oki_load_sgr_pvt.g_expire_in_qtr_csr%FOUND THEN
2796 l_prev_k_amount := rec_g_expire_in_qtr_csr.base_contract_amount ;
2797 l_prev_sob_k_amount := rec_g_expire_in_qtr_csr.sob_contract_amount ;
2798 END IF ;
2799 CLOSE oki_load_sgr_pvt.g_expire_in_qtr_csr ;
2800
2801 -- Determine running total for ending active contracts
2802 -- Subtract expiring during contract amount
2803 l_curr_end_active_k := l_curr_end_active_k + (l_curr_k_amount * -1);
2804 l_prev_end_active_k := l_prev_end_active_k + (l_prev_k_amount * -1);
2805 l_curr_sob_end_active_k := l_curr_sob_end_active_k + (l_curr_sob_k_amount * -1) ;
2806 l_prev_sob_end_active_k := l_prev_sob_end_active_k + (l_prev_sob_k_amount * -1) ;
2807
2808 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
2809 l_loc := l_loc || ' -- current / previous expiring during quarter' ;
2810 -- Determine if the record is a new one or an existing one
2811 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
2812 rec_g_glpr_csr.period_name, oki_load_sgr_pvt.g_all_org_id,
2813 oki_load_sgr_pvt.g_exp_in_qtr_code, oki_load_sgr_pvt.g_all_scs_code,
2814 oki_load_sgr_pvt.g_all_cst_id, oki_load_sgr_pvt.g_all_pct_code,
2815 p_summary_build_date, p_ending_period_type ) ;
2816 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
2817 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
2818 l_loc := 'Insert the new record.' ;
2819 l_loc := l_loc || ' -- current / previous expiring during quarter' ;
2820 -- Insert the current period data for the period
2821 oki_load_sgr_pvt.ins_seq_grw_rate (
2822 p_period_set_name => rec_g_glpr_csr.period_set_name
2823 , p_period_name => rec_g_glpr_csr.period_name
2824 , p_period_type => p_ending_period_type
2825 , p_summary_build_date => p_summary_build_date
2826 , p_authoring_org_id => oki_load_sgr_pvt.g_all_org_id
2827 , p_authoring_org_name => oki_load_sgr_pvt.g_all_org_name
2828 , p_customer_party_id => oki_load_sgr_pvt.g_all_cst_id
2829 , p_customer_name => oki_load_sgr_pvt.g_all_cst_name
2830 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_exp_in_qtr_code
2831 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
2832 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
2833 , p_curr_base_contract_amount => l_curr_k_amount
2834 , p_prev_base_contract_amount => l_prev_k_amount
2835 , p_curr_sob_contract_amount => l_curr_sob_k_amount
2836 , p_prev_sob_contract_amount => l_prev_sob_k_amount
2837 , x_retcode => l_retcode ) ;
2841 END IF ;
2838 IF l_retcode = '2' THEN
2839 -- Load failed, exit immediately.
2840 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
2842 ELSE
2843 l_loc := 'Update the existing record.' ;
2844 l_loc := l_loc || ' -- current / previous expiring during quarter' ;
2845 -- Record already exists, so perform an update
2846 oki_load_sgr_pvt.upd_seq_grw_rate (
2847 p_curr_base_contract_amount => l_curr_k_amount
2848 , p_prev_base_contract_amount => l_prev_k_amount
2849 , p_curr_sob_contract_amount => l_curr_sob_k_amount
2850 , p_prev_sob_contract_amount => l_prev_sob_k_amount
2851 , p_sgr_rowid => rec_g_sgr_csr.rowid
2852 , x_retcode => l_retcode ) ;
2853
2854 IF l_retcode = '2' THEN
2855 -- Load failed, exit immediately.
2856 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
2857 END IF ;
2858 END IF ;
2859 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
2860
2861 -- Re-initialize the amounts before calculating
2862 l_curr_k_amount := 0 ;
2863 l_prev_k_amount := 0 ;
2864 l_curr_sob_k_amount := 0 ;
2865 l_prev_sob_k_amount := 0 ;
2866
2867 l_loc := 'Opening cursor to determine the current quarter ' ;
2868 l_loc := l_loc || 'contracts renewed.' ;
2869 OPEN oki_load_sgr_pvt.g_qtr_k_rnw_csr ( l_period_start_date,
2870 p_summary_build_date ) ;
2871 FETCH oki_load_sgr_pvt.g_qtr_k_rnw_csr INTO rec_g_qtr_k_rnw_csr ;
2872 IF oki_load_sgr_pvt.g_qtr_k_rnw_csr%FOUND THEN
2873 l_curr_k_amount := rec_g_qtr_k_rnw_csr.base_contract_amount ;
2874 l_curr_sob_k_amount := rec_g_qtr_k_rnw_csr.sob_contract_amount ;
2875 END IF;
2876 CLOSE oki_load_sgr_pvt.g_qtr_k_rnw_csr ;
2877
2878 l_loc := 'Opening cursor to determine the previous quarter ' ;
2879 l_loc := l_loc || 'contracts renewed.' ;
2880 OPEN oki_load_sgr_pvt.g_qtr_k_rnw_csr ( l_py_period_start_date,
2881 l_py_summary_build_date ) ;
2882 FETCH oki_load_sgr_pvt.g_qtr_k_rnw_csr INTO rec_g_qtr_k_rnw_csr ;
2883 IF oki_load_sgr_pvt.g_qtr_k_rnw_csr%FOUND THEN
2884 l_prev_k_amount := rec_g_qtr_k_rnw_csr.base_contract_amount ;
2885 l_prev_sob_k_amount := rec_g_qtr_k_rnw_csr.sob_contract_amount ;
2886 END IF ;
2887 CLOSE oki_load_sgr_pvt.g_qtr_k_rnw_csr ;
2888
2889 -- Determine running total for ending active contracts
2890 -- Add quarter contracts renewed amount
2891 l_curr_end_active_k := l_curr_end_active_k + l_curr_k_amount ;
2892 l_prev_end_active_k := l_prev_end_active_k + l_prev_k_amount ;
2893 l_curr_sob_end_active_k := l_curr_sob_end_active_k + l_curr_sob_k_amount ;
2894 l_prev_sob_end_active_k := l_prev_sob_end_active_k + l_prev_sob_k_amount ;
2895
2896 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
2897 l_loc := l_loc || ' -- current / previous quarter contracts renewed' ;
2898 -- Determine if the record is a new one or an existing one
2899 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
2900 rec_g_glpr_csr.period_name, oki_load_sgr_pvt.g_all_org_id,
2901 oki_load_sgr_pvt.g_qtr_k_rnw_code, oki_load_sgr_pvt.g_all_scs_code,
2902 oki_load_sgr_pvt.g_all_cst_id, oki_load_sgr_pvt.g_all_pct_code,
2903 p_summary_build_date, p_ending_period_type ) ;
2904 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
2905 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
2906 l_loc := 'Insert the new record.' ;
2907 l_loc := l_loc || ' -- current / previous quarter contracts renewed' ;
2908 -- Insert the current period data for the period
2909 oki_load_sgr_pvt.ins_seq_grw_rate (
2910 p_period_set_name => rec_g_glpr_csr.period_set_name
2911 , p_period_name => rec_g_glpr_csr.period_name
2912 , p_period_type => p_ending_period_type
2913 , p_summary_build_date => p_summary_build_date
2914 , p_authoring_org_id => oki_load_sgr_pvt.g_all_org_id
2915 , p_authoring_org_name => oki_load_sgr_pvt.g_all_org_name
2916 , p_customer_party_id => oki_load_sgr_pvt.g_all_cst_id
2917 , p_customer_name => oki_load_sgr_pvt.g_all_cst_name
2918 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_qtr_k_rnw_code
2919 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
2920 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
2921 , p_curr_base_contract_amount => l_curr_k_amount
2922 , p_prev_base_contract_amount => l_prev_k_amount
2923 , p_curr_sob_contract_amount => l_curr_sob_k_amount
2924 , p_prev_sob_contract_amount => l_prev_sob_k_amount
2925 , x_retcode => l_retcode ) ;
2926 IF l_retcode = '2' THEN
2927 -- Load failed, exit immediately.
2928 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
2929 END IF ;
2930 ELSE
2931 l_loc := 'Update the existing record.' ;
2932 l_loc := l_loc || ' -- current / previous quarter contracts renewed' ;
2933 -- Record already exists, so perform an update
2934 oki_load_sgr_pvt.upd_seq_grw_rate (
2935 p_curr_base_contract_amount => l_curr_k_amount
2936 , p_prev_base_contract_amount => l_prev_k_amount
2940 , x_retcode => l_retcode ) ;
2937 , p_curr_sob_contract_amount => l_curr_sob_k_amount
2938 , p_prev_sob_contract_amount => l_prev_sob_k_amount
2939 , p_sgr_rowid => rec_g_sgr_csr.rowid
2941
2942 IF l_retcode = '2' THEN
2943 -- Load failed, exit immediately.
2944 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
2945 END IF ;
2946 END IF ;
2947 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
2948
2949 -- Re-initialize the amounts before calculating
2950 l_curr_k_amount := 0 ;
2951 l_prev_k_amount := 0 ;
2952 l_curr_sob_k_amount := 0 ;
2953 l_prev_sob_k_amount := 0 ;
2954
2955 l_loc := 'Opening cursor to determine the current backlog' ;
2956 l_loc := l_loc || 'contracts renewed.' ;
2957 OPEN oki_load_sgr_pvt.g_bklg_k_rnw_csr ( l_period_start_date,
2958 p_summary_build_date ) ;
2959 FETCH oki_load_sgr_pvt.g_bklg_k_rnw_csr INTO rec_g_bklg_k_rnw_csr ;
2960 IF oki_load_sgr_pvt.g_bklg_k_rnw_csr%FOUND THEN
2961 l_curr_k_amount := rec_g_bklg_k_rnw_csr.base_contract_amount ;
2962 l_curr_sob_k_amount := rec_g_bklg_k_rnw_csr.sob_contract_amount ;
2963 END IF;
2964 CLOSE oki_load_sgr_pvt.g_bklg_k_rnw_csr ;
2965
2966 l_loc := 'Opening cursor to determine the previous backlog' ;
2967 l_loc := l_loc || 'contracts renewed.' ;
2968 OPEN oki_load_sgr_pvt.g_bklg_k_rnw_csr ( l_py_period_start_date,
2969 l_py_summary_build_date ) ;
2970 FETCH oki_load_sgr_pvt.g_bklg_k_rnw_csr INTO rec_g_bklg_k_rnw_csr ;
2971 IF oki_load_sgr_pvt.g_bklg_k_rnw_csr%FOUND THEN
2972 l_prev_k_amount := rec_g_bklg_k_rnw_csr.base_contract_amount ;
2973 l_prev_sob_k_amount := rec_g_bklg_k_rnw_csr.sob_contract_amount ;
2974 END IF;
2975 CLOSE oki_load_sgr_pvt.g_bklg_k_rnw_csr ;
2976
2977 -- Determine running total for ending active contracts
2978 -- Add backlog contracts renewed amount
2979 l_curr_end_active_k := l_curr_end_active_k + l_curr_k_amount ;
2980 l_prev_end_active_k := l_prev_end_active_k + l_prev_k_amount ;
2981 l_curr_sob_end_active_k := l_curr_sob_end_active_k + l_curr_sob_k_amount ;
2982 l_prev_sob_end_active_k := l_prev_sob_end_active_k + l_prev_sob_k_amount ;
2983
2984 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
2985 l_loc := l_loc || ' -- current / previous backlog contracts renewed' ;
2986 -- Determine if the record is a new one or an existing one
2987 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
2988 rec_g_glpr_csr.period_name, oki_load_sgr_pvt.g_all_org_id,
2989 oki_load_sgr_pvt.g_bklg_k_rnw_code, oki_load_sgr_pvt.g_all_scs_code,
2990 oki_load_sgr_pvt.g_all_cst_id, oki_load_sgr_pvt.g_all_pct_code,
2991 p_summary_build_date, p_ending_period_type ) ;
2992 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
2993 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
2994 l_loc := 'Insert the new record.' ;
2995 l_loc := l_loc || ' -- current / previous backlog contracts renewed' ;
2996 -- Insert the current period data for the period
2997 oki_load_sgr_pvt.ins_seq_grw_rate (
2998 p_period_set_name => rec_g_glpr_csr.period_set_name
2999 , p_period_name => rec_g_glpr_csr.period_name
3000 , p_period_type => p_ending_period_type
3001 , p_summary_build_date => p_summary_build_date
3002 , p_authoring_org_id => oki_load_sgr_pvt.g_all_org_id
3003 , p_authoring_org_name => oki_load_sgr_pvt.g_all_org_name
3004 , p_customer_party_id => oki_load_sgr_pvt.g_all_cst_id
3005 , p_customer_name => oki_load_sgr_pvt.g_all_cst_name
3006 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_bklg_k_rnw_code
3007 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
3008 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
3009 , p_curr_base_contract_amount => l_curr_k_amount
3010 , p_prev_base_contract_amount => l_prev_k_amount
3011 , p_curr_sob_contract_amount => l_curr_sob_k_amount
3012 , p_prev_sob_contract_amount => l_prev_sob_k_amount
3013 , x_retcode => l_retcode ) ;
3014 IF l_retcode = '2' THEN
3015 -- Load failed, exit immediately.
3016 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
3017 END IF ;
3018 ELSE
3019 l_loc := 'Update the existing record.' ;
3020 l_loc := l_loc || ' -- current / previous backlog contracts renewed' ;
3021 -- Record already exists, so perform an update
3022 oki_load_sgr_pvt.upd_seq_grw_rate (
3023 p_curr_base_contract_amount => l_curr_k_amount
3024 , p_prev_base_contract_amount => l_prev_k_amount
3025 , p_curr_sob_contract_amount => l_curr_sob_k_amount
3026 , p_prev_sob_contract_amount => l_prev_sob_k_amount
3027 , p_sgr_rowid => rec_g_sgr_csr.rowid
3028 , x_retcode => l_retcode ) ;
3029
3030 IF l_retcode = '2' THEN
3031 -- Load failed, exit immediately.
3032 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
3033 END IF ;
3034 END IF ;
3035 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
3036
3037 -- Re-initialize the amounts before calculating
3041 l_prev_sob_k_amount := 0 ;
3038 l_curr_k_amount := 0 ;
3039 l_prev_k_amount := 0 ;
3040 l_curr_sob_k_amount := 0 ;
3042
3043 l_loc := 'Opening cursor to determine the current new business.' ;
3044 OPEN oki_load_sgr_pvt.g_new_bsn_csr ( l_period_start_date,
3045 p_summary_build_date ) ;
3046 FETCH oki_load_sgr_pvt.g_new_bsn_csr INTO rec_g_new_bsn_csr ;
3047 IF oki_load_sgr_pvt.g_new_bsn_csr%FOUND THEN
3048 l_curr_k_amount := rec_g_new_bsn_csr.base_contract_amount ;
3049 l_curr_sob_k_amount := rec_g_new_bsn_csr.sob_contract_amount ;
3050 END IF;
3051 CLOSE oki_load_sgr_pvt.g_new_bsn_csr ;
3052
3053 l_loc := 'Opening cursor to determine the previous new business.' ;
3054 OPEN oki_load_sgr_pvt.g_new_bsn_csr ( l_py_period_start_date,
3055 l_py_summary_build_date ) ;
3056 FETCH oki_load_sgr_pvt.g_new_bsn_csr INTO rec_g_new_bsn_csr ;
3057 IF oki_load_sgr_pvt.g_new_bsn_csr%FOUND THEN
3058 l_prev_k_amount := rec_g_new_bsn_csr.base_contract_amount ;
3059 l_prev_sob_k_amount := rec_g_new_bsn_csr.sob_contract_amount ;
3060 END IF ;
3061 CLOSE oki_load_sgr_pvt.g_new_bsn_csr ;
3062
3063 -- Determine running total for ending active contracts
3064 -- Add new business amount
3065 l_curr_end_active_k := l_curr_end_active_k + l_curr_k_amount ;
3066 l_prev_end_active_k := l_prev_end_active_k + l_prev_k_amount ;
3067 l_curr_sob_end_active_k := l_curr_sob_end_active_k + l_curr_sob_k_amount ;
3068 l_prev_sob_end_active_k := l_prev_sob_end_active_k + l_prev_sob_k_amount ;
3069
3070 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
3071 l_loc := l_loc || ' -- current / previous new business' ;
3072 -- Determine if the record is a new one or an existing one
3073 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
3074 rec_g_glpr_csr.period_name, oki_load_sgr_pvt.g_all_org_id,
3075 oki_load_sgr_pvt.g_new_bsn_code, oki_load_sgr_pvt.g_all_scs_code,
3076 oki_load_sgr_pvt.g_all_cst_id, oki_load_sgr_pvt.g_all_pct_code,
3077 p_summary_build_date, p_ending_period_type ) ;
3078 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
3079 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
3080 l_loc := 'Insert the new record.' ;
3081 l_loc := l_loc || ' -- current / previous new business' ;
3082 -- Insert the current period data for the period
3083 oki_load_sgr_pvt.ins_seq_grw_rate (
3084 p_period_set_name => rec_g_glpr_csr.period_set_name
3085 , p_period_name => rec_g_glpr_csr.period_name
3086 , p_period_type => p_ending_period_type
3087 , p_summary_build_date => p_summary_build_date
3088 , p_authoring_org_id => oki_load_sgr_pvt.g_all_org_id
3089 , p_authoring_org_name => oki_load_sgr_pvt.g_all_org_name
3090 , p_customer_party_id => oki_load_sgr_pvt.g_all_cst_id
3091 , p_customer_name => oki_load_sgr_pvt.g_all_cst_name
3092 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_new_bsn_code
3093 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
3094 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
3095 , p_curr_base_contract_amount => l_curr_k_amount
3096 , p_prev_base_contract_amount => l_prev_k_amount
3097 , p_curr_sob_contract_amount => l_curr_sob_k_amount
3098 , p_prev_sob_contract_amount => l_prev_sob_k_amount
3099 , x_retcode => l_retcode ) ;
3100 IF l_retcode = '2' THEN
3101 -- Load failed, exit immediately.
3102 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
3103 END IF ;
3104 ELSE
3105 l_loc := 'Update the existing record.' ;
3106 l_loc := l_loc || ' -- current / previous new business' ;
3107 -- Record already exists, so perform an update
3108 oki_load_sgr_pvt.upd_seq_grw_rate (
3109 p_curr_base_contract_amount => l_curr_k_amount
3110 , p_prev_base_contract_amount => l_prev_k_amount
3111 , p_curr_sob_contract_amount => l_curr_sob_k_amount
3112 , p_prev_sob_contract_amount => l_prev_sob_k_amount
3113 , p_sgr_rowid => rec_g_sgr_csr.rowid
3114 , x_retcode => l_retcode ) ;
3115
3116 IF l_retcode = '2' THEN
3117 -- Load failed, exit immediately.
3118 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
3119 END IF ;
3120 END IF ;
3121 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
3122
3123 -- Re-initialize the amounts before calculating
3124 l_curr_k_amount := 0 ;
3125 l_prev_k_amount := 0 ;
3126 l_curr_sob_k_amount := 0 ;
3127 l_prev_sob_k_amount := 0 ;
3128
3129 l_loc := 'Opening cursor to determine the current cancelled renewals.' ;
3130 OPEN oki_load_sgr_pvt.g_cncl_rnwl_csr( l_period_start_date,
3131 p_summary_build_date ) ;
3132 FETCH oki_load_sgr_pvt.g_cncl_rnwl_csr INTO rec_g_cncl_rnwl_csr ;
3133 IF oki_load_sgr_pvt.g_cncl_rnwl_csr%FOUND THEN
3134 l_curr_k_amount := rec_g_cncl_rnwl_csr.base_contract_amount ;
3135 l_curr_sob_k_amount := rec_g_cncl_rnwl_csr.sob_contract_amount ;
3136 END IF;
3137 CLOSE oki_load_sgr_pvt.g_cncl_rnwl_csr ;
3138
3142 FETCH oki_load_sgr_pvt.g_cncl_rnwl_csr INTO rec_g_cncl_rnwl_csr ;
3139 l_loc := 'Opening cursor to determine the previous cancelled renewals.' ;
3140 OPEN oki_load_sgr_pvt.g_cncl_rnwl_csr( l_py_period_start_date,
3141 l_py_summary_build_date ) ;
3143 IF oki_load_sgr_pvt.g_cncl_rnwl_csr%FOUND THEN
3144 l_prev_k_amount := rec_g_cncl_rnwl_csr.base_contract_amount ;
3145 l_prev_sob_k_amount := rec_g_cncl_rnwl_csr.sob_contract_amount ;
3146 END IF ;
3147 CLOSE oki_load_sgr_pvt.g_cncl_rnwl_csr ;
3148
3149 -- Determine running total for ending active contracts
3150 -- Subtract cancelled contract amount
3151 l_curr_end_active_k := l_curr_end_active_k + (l_curr_k_amount * -1) ;
3152 l_prev_end_active_k := l_prev_end_active_k + (l_prev_k_amount * -1) ;
3153 l_curr_sob_end_active_k := l_curr_sob_end_active_k + (l_curr_sob_k_amount * -1) ;
3154 l_prev_sob_end_active_k := l_prev_sob_end_active_k + (l_prev_sob_k_amount * -1) ;
3155
3156 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
3157 l_loc := l_loc || ' -- current / previous cancelled contract' ;
3158 -- Determine if the record is a new one or an existing one
3159 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
3160 rec_g_glpr_csr.period_name, oki_load_sgr_pvt.g_all_org_id,
3161 oki_load_sgr_pvt.g_cncl_rnwl_code, oki_load_sgr_pvt.g_all_scs_code,
3162 oki_load_sgr_pvt.g_all_cst_id, oki_load_sgr_pvt.g_all_pct_code,
3163 p_summary_build_date, p_ending_period_type ) ;
3164 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
3165 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
3166 l_loc := 'Insert the new record.' ;
3167 l_loc := l_loc || ' -- current / previous cancelled renewals' ;
3168 -- Insert the current period data for the period
3169 oki_load_sgr_pvt.ins_seq_grw_rate (
3170 p_period_set_name => rec_g_glpr_csr.period_set_name
3171 , p_period_name => rec_g_glpr_csr.period_name
3172 , p_period_type => p_ending_period_type
3173 , p_summary_build_date => p_summary_build_date
3174 , p_authoring_org_id => oki_load_sgr_pvt.g_all_org_id
3175 , p_authoring_org_name => oki_load_sgr_pvt.g_all_org_name
3176 , p_customer_party_id => oki_load_sgr_pvt.g_all_cst_id
3177 , p_customer_name => oki_load_sgr_pvt.g_all_cst_name
3178 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_cncl_rnwl_code
3179 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
3180 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
3181 , p_curr_base_contract_amount => l_curr_k_amount
3182 , p_prev_base_contract_amount => l_prev_k_amount
3183 , p_curr_sob_contract_amount => l_curr_sob_k_amount
3184 , p_prev_sob_contract_amount => l_prev_sob_k_amount
3185 , x_retcode => l_retcode ) ;
3186 IF l_retcode = '2' THEN
3187 -- Load failed, exit immediately.
3188 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
3189 END IF ;
3190 ELSE
3191 l_loc := 'Update the existing record.' ;
3192 l_loc := l_loc || ' -- current / previous cancelled renewals' ;
3193 -- Record already exists, so perform an update
3194 oki_load_sgr_pvt.upd_seq_grw_rate (
3195 p_curr_base_contract_amount => l_curr_k_amount
3196 , p_prev_base_contract_amount => l_prev_k_amount
3197 , p_curr_sob_contract_amount => l_curr_sob_k_amount
3198 , p_prev_sob_contract_amount => l_prev_sob_k_amount
3199 , p_sgr_rowid => rec_g_sgr_csr.rowid
3200 , x_retcode => l_retcode ) ;
3201
3202 IF l_retcode = '2' THEN
3203 -- Load failed, exit immediately.
3204 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
3205 END IF ;
3206 END IF ;
3207 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
3208
3209 -- Re-initialize the amounts before calculating
3210 l_curr_k_amount := 0 ;
3211 l_prev_k_amount := 0 ;
3212 l_curr_sob_k_amount := 0 ;
3213 l_prev_sob_k_amount := 0 ;
3214
3215 l_loc := 'Looping through all the current contracts terminated in the period.';
3216 << g_trmn_rnwl_csr_loop >>
3217 -- Loop through all the contracts terminated in the period
3218 FOR rec_g_trmn_rnwl_csr IN oki_load_sgr_pvt.g_trmn_rnwl_csr(
3219 l_period_start_date, p_summary_build_date ) LOOP
3220 l_curr_k_amount := l_curr_k_amount +
3221 rec_g_trmn_rnwl_csr.base_contract_amount ;
3222 l_curr_sob_k_amount := l_curr_sob_k_amount +
3223 rec_g_trmn_rnwl_csr.sob_contract_amount ;
3224 END LOOP g_trmn_rnwl_csr_loop ;
3225 l_curr_k_amount := ROUND(l_curr_k_amount, 2) ;
3226 l_curr_sob_k_amount := ROUND(l_curr_sob_k_amount, 2) ;
3227
3228 l_loc := 'Looping through all the previous contracts terminated in the period.';
3229 << g_trmn_rnwl_csr_loop >>
3230 -- Loop through all the contracts terminated in the period
3231 FOR rec_g_trmn_rnwl_csr IN oki_load_sgr_pvt.g_trmn_rnwl_csr(
3232 l_py_period_start_date, l_py_summary_build_date ) LOOP
3233 l_prev_k_amount := l_prev_k_amount +
3234 rec_g_trmn_rnwl_csr.base_contract_amount ;
3235 l_prev_sob_k_amount := l_prev_sob_k_amount +
3239 l_prev_sob_k_amount := ROUND(l_prev_sob_k_amount, 2) ;
3236 rec_g_trmn_rnwl_csr.sob_contract_amount ;
3237 END LOOP g_trmn_rnwl_csr_loop ;
3238 l_prev_k_amount := ROUND(l_prev_k_amount, 2) ;
3240
3241 -- Determine running total for ending active contracts
3242 -- Subtract terminated contract amount
3243 l_curr_end_active_k := l_curr_end_active_k + (l_curr_k_amount * -1) ;
3244 l_prev_end_active_k := l_prev_end_active_k + (l_prev_k_amount * -1) ;
3245 l_curr_sob_end_active_k := l_curr_sob_end_active_k + (l_curr_sob_k_amount * -1) ;
3246 l_prev_sob_end_active_k := l_prev_sob_end_active_k + (l_prev_sob_k_amount * -1) ;
3247
3248 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
3249 l_loc := l_loc || ' -- current / previous terminated renewals' ;
3250 -- Determine if the record is a new one or an existing one
3251 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
3252 rec_g_glpr_csr.period_name, oki_load_sgr_pvt.g_all_org_id,
3253 oki_load_sgr_pvt.g_seq_trmn_k_code, oki_load_sgr_pvt.g_all_scs_code,
3254 oki_load_sgr_pvt.g_all_cst_id, oki_load_sgr_pvt.g_all_pct_code,
3255 p_summary_build_date, p_ending_period_type ) ;
3256 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
3257 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
3258 l_loc := 'Insert the new record.' ;
3259 l_loc := l_loc || ' -- current / previous terminated renewals' ;
3260 -- Insert the current period data for the period
3261 oki_load_sgr_pvt.ins_seq_grw_rate (
3262 p_period_set_name => rec_g_glpr_csr.period_set_name
3263 , p_period_name => rec_g_glpr_csr.period_name
3264 , p_period_type => p_ending_period_type
3265 , p_summary_build_date => p_summary_build_date
3266 , p_authoring_org_id => oki_load_sgr_pvt.g_all_org_id
3267 , p_authoring_org_name => oki_load_sgr_pvt.g_all_org_name
3268 , p_customer_party_id => oki_load_sgr_pvt.g_all_cst_id
3269 , p_customer_name => oki_load_sgr_pvt.g_all_cst_name
3270 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_seq_trmn_k_code
3271 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
3272 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
3273 , p_curr_base_contract_amount => l_curr_k_amount
3274 , p_prev_base_contract_amount => l_prev_k_amount
3275 , p_curr_sob_contract_amount => l_curr_sob_k_amount
3276 , p_prev_sob_contract_amount => l_prev_sob_k_amount
3277 , x_retcode => l_retcode ) ;
3278 IF l_retcode = '2' THEN
3279 -- Load failed, exit immediately.
3280 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
3281 END IF ;
3282 ELSE
3283 l_loc := 'Update the existing record.' ;
3284 l_loc := l_loc || ' -- current / previous terminated renewals' ;
3285 -- Record already exists, so perform an update
3286 oki_load_sgr_pvt.upd_seq_grw_rate (
3287 p_curr_base_contract_amount => l_curr_k_amount
3288 , p_prev_base_contract_amount => l_prev_k_amount
3289 , p_curr_sob_contract_amount => l_curr_sob_k_amount
3290 , p_prev_sob_contract_amount => l_prev_sob_k_amount
3291 , p_sgr_rowid => rec_g_sgr_csr.rowid
3292 , x_retcode => l_retcode ) ;
3293
3294 IF l_retcode = '2' THEN
3295 -- Load failed, exit immediately.
3296 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
3297 END IF ;
3298 END IF ;
3299 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
3300
3301 -- Re-initialize the amounts before calculating
3302 l_curr_k_amount := 0 ;
3303 l_prev_k_amount := 0 ;
3304 l_curr_sob_k_amount := 0 ;
3305 l_prev_sob_k_amount := 0 ;
3306
3307 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
3308 l_loc := l_loc || ' -- current / previous ending active contracts' ;
3309 -- Determine if the record is a new one or an existing one
3310 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
3311 rec_g_glpr_csr.period_name, oki_load_sgr_pvt.g_all_org_id,
3312 oki_load_sgr_pvt.g_end_active_k_code, oki_load_sgr_pvt.g_all_scs_code,
3313 oki_load_sgr_pvt.g_all_cst_id, oki_load_sgr_pvt.g_all_pct_code,
3314 p_summary_build_date, p_ending_period_type ) ;
3315 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
3316 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
3317 l_loc := 'Insert the new record.' ;
3318 l_loc := l_loc || ' -- current / previous ending active contracts' ;
3319 -- Insert the current period data for the period
3320 oki_load_sgr_pvt.ins_seq_grw_rate (
3321 p_period_set_name => rec_g_glpr_csr.period_set_name
3322 , p_period_name => rec_g_glpr_csr.period_name
3323 , p_period_type => p_ending_period_type
3324 , p_summary_build_date => p_summary_build_date
3325 , p_authoring_org_id => oki_load_sgr_pvt.g_all_org_id
3326 , p_authoring_org_name => oki_load_sgr_pvt.g_all_org_name
3327 , p_customer_party_id => oki_load_sgr_pvt.g_all_cst_id
3328 , p_customer_name => oki_load_sgr_pvt.g_all_cst_name
3329 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_end_active_k_code
3333 , p_prev_base_contract_amount => l_prev_end_active_k
3330 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
3331 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
3332 , p_curr_base_contract_amount => l_curr_end_active_k
3334 , p_curr_sob_contract_amount => l_curr_sob_end_active_k
3335 , p_prev_sob_contract_amount => l_prev_sob_end_active_k
3336 , x_retcode => l_retcode ) ;
3337 IF l_retcode = '2' THEN
3338 -- Load failed, exit immediately.
3339 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
3340 END IF ;
3341 ELSE
3342 l_loc := 'Update the existing record.' ;
3343 l_loc := l_loc || ' -- current / previous ending active contracts' ;
3344 -- Record already exists, so perform an update
3345 oki_load_sgr_pvt.upd_seq_grw_rate (
3346 p_curr_base_contract_amount => l_curr_end_active_k
3347 , p_prev_base_contract_amount => l_prev_end_active_k
3348 , p_curr_sob_contract_amount => l_curr_sob_end_active_k
3349 , p_prev_sob_contract_amount => l_prev_sob_end_active_k
3350 , p_sgr_rowid => rec_g_sgr_csr.rowid
3351 , x_retcode => l_retcode ) ;
3352
3353 IF l_retcode = '2' THEN
3354 -- Load failed, exit immediately.
3355 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
3356 END IF ;
3357 END IF ;
3358 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
3359
3360 -- Re-initialize the amounts before calculating
3361 l_curr_k_amount := 0 ;
3362 l_prev_k_amount := 0 ;
3363 l_curr_sob_k_amount := 0 ;
3364 l_prev_sob_k_amount := 0 ;
3365
3366 -- If the denominator is zero, then set the sequential growth rate to zero
3367 l_loc := 'Setting the sequential growth rate value.' ;
3368 IF l_curr_active_k = 0 THEN
3369 l_curr_seq_grw_rate := 0 ;
3370 ELSE
3371 l_curr_seq_grw_rate := ROUND(((l_curr_end_active_k -
3372 l_curr_active_k ) / l_curr_active_k ) * 100, 2) ;
3373 END IF ;
3374
3375 IF l_curr_sob_active_k = 0 THEN
3376 l_curr_sob_seq_grw_rate := 0 ;
3377 ELSE
3378 l_curr_sob_seq_grw_rate := ROUND(((l_curr_sob_end_active_k -
3379 l_curr_sob_active_k ) / l_curr_sob_active_k ) * 100, 2) ;
3380 END IF ;
3381
3382 IF l_prev_active_k = 0 THEN
3383 l_prev_seq_grw_rate := 0 ;
3384 ELSE
3385 l_prev_seq_grw_rate := ROUND(((l_prev_end_active_k -
3386 l_prev_active_k ) / l_prev_active_k ) * 100, 2) ;
3387 END IF ;
3388
3389 IF l_prev_sob_active_k = 0 THEN
3390 l_prev_sob_seq_grw_rate := 0 ;
3391 ELSE
3392 l_prev_sob_seq_grw_rate := ROUND(((l_prev_sob_end_active_k -
3393 l_prev_sob_active_k ) / l_prev_sob_active_k ) * 100, 2) ;
3394 END IF ;
3395
3396 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
3397 l_loc := l_loc || ' -- current / previous sequentail growth rate' ;
3398 -- Determine if the record is a new one or an existing one
3399 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
3400 rec_g_glpr_csr.period_name, oki_load_sgr_pvt.g_all_org_id,
3401 oki_load_sgr_pvt.g_seq_grw_rate_code, oki_load_sgr_pvt.g_all_scs_code,
3402 oki_load_sgr_pvt.g_all_cst_id, oki_load_sgr_pvt.g_all_pct_code,
3403 p_summary_build_date, p_ending_period_type ) ;
3404 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
3405 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
3406 l_loc := 'Insert the new record.' ;
3407 l_loc := l_loc || ' -- current / previous sequentail growth rate' ;
3408 -- Insert the current period data for the period
3409 oki_load_sgr_pvt.ins_seq_grw_rate (
3410 p_period_set_name => rec_g_glpr_csr.period_set_name
3411 , p_period_name => rec_g_glpr_csr.period_name
3412 , p_period_type => p_ending_period_type
3413 , p_summary_build_date => p_summary_build_date
3414 , p_authoring_org_id => oki_load_sgr_pvt.g_all_org_id
3415 , p_authoring_org_name => oki_load_sgr_pvt.g_all_org_name
3416 , p_customer_party_id => oki_load_sgr_pvt.g_all_cst_id
3417 , p_customer_name => oki_load_sgr_pvt.g_all_cst_name
3418 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_seq_grw_rate_code
3419 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
3420 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
3421 , p_curr_base_contract_amount => l_curr_seq_grw_rate
3422 , p_prev_base_contract_amount => l_prev_seq_grw_rate
3423 , p_curr_sob_contract_amount => l_curr_sob_seq_grw_rate
3424 , p_prev_sob_contract_amount => l_prev_sob_seq_grw_rate
3425 , x_retcode => l_retcode ) ;
3426 IF l_retcode = '2' THEN
3427 -- Load failed, exit immediately.
3428 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
3429 END IF ;
3430 ELSE
3431 l_loc := 'Update the existing record.' ;
3432 l_loc := l_loc || ' -- current / previous sequentail growth rate' ;
3433 -- Record already exists, so perform an update
3437 , p_curr_sob_contract_amount => l_curr_sob_seq_grw_rate
3434 oki_load_sgr_pvt.upd_seq_grw_rate (
3435 p_curr_base_contract_amount => l_curr_seq_grw_rate
3436 , p_prev_base_contract_amount => l_prev_seq_grw_rate
3438 , p_prev_sob_contract_amount => l_prev_sob_seq_grw_rate
3439 , p_sgr_rowid => rec_g_sgr_csr.rowid
3440 , x_retcode => l_retcode ) ;
3441
3442 IF l_retcode = '2' THEN
3443 -- Load failed, exit immediately.
3444 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
3445 END IF ;
3446 END IF ;
3447 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
3448 END IF ;
3449
3450 END LOOP g_glpr_csr_loop ;
3451
3452 EXCEPTION
3453 WHEN oki_load_sgr_pvt.g_excp_exit_immediate THEN
3454 -- Do not log an error ; It has already been logged.
3455 -- Set return code to error
3456 x_retcode := '2' ;
3457
3458
3459 WHEN OTHERS THEN
3460 l_sqlcode := SQLCODE ;
3461 l_sqlerrm := SQLERRM ;
3462
3463 -- Set return code TO error
3464 x_retcode := '2' ;
3465
3466 fnd_message.set_name( application => 'OKI'
3467 , name => 'OKI_UNEXPECTED_FAILURE');
3468
3469 fnd_message.set_token( token => 'OBJECT_NAME'
3470 , value => 'OKI_LOAD_SGR_PVT.CALC_SGR_DTL3');
3471
3472 fnd_file.put_line( which => fnd_file.log
3473 , buff => fnd_message.get);
3474
3475 -- Log the location within the procedure where the error occurred
3476 fnd_message.set_name( application => 'OKI'
3477 , name => 'OKI_LOC_IN_PROG_FAILURE');
3478
3479 fnd_message.set_token( token => 'LOCATION'
3480 , value => l_loc);
3481
3482 fnd_file.put_line( which => fnd_file.log
3483 , buff => fnd_message.get);
3484
3485 fnd_file.put_line( which => fnd_file.log
3486 , buff => l_sqlcode||' '|| l_sqlerrm );
3487 END calc_sgr_dtl3 ;
3488 --------------------------------------------------------------------------------
3489 -- Procedure to calcuate the contract amount for the current and previous
3490 -- year.
3491
3492 --------------------------------------------------------------------------------
3493 PROCEDURE calc_sgr_sum
3494 ( p_period_set_name IN VARCHAR2
3495 , p_period_type IN VARCHAR2
3496 , p_summary_build_date IN DATE
3497 , x_retcode OUT VARCHAR2
3498 ) IS
3499
3500 -- Local variable declaration
3501
3502 -- For capturing the return code, 0 = success, 1 = warning, 2 = error
3503 l_retcode VARCHAR2(1) := NULL ;
3504
3505 -- For error handling
3506 l_sqlcode VARCHAR2(100) := NULL ;
3507 l_sqlerrm VARCHAR2(1000) := NULL ;
3508
3509 -- Holds the contract amount for the current and previous
3510 -- beginning active contracts
3511 l_curr_active_k NUMBER := 0 ;
3512 l_prev_active_k NUMBER := 0 ;
3513 l_curr_sob_active_k NUMBER := 0 ;
3514 l_prev_sob_active_k NUMBER := 0 ;
3515 -- Holds the contract amount for the current and previous
3516 -- ending active contracts
3517 l_curr_end_active_k NUMBER := 0 ;
3518 l_prev_end_active_k NUMBER := 0 ;
3519 l_curr_sob_end_active_k NUMBER := 0 ;
3520 l_prev_sob_end_active_k NUMBER := 0 ;
3521 -- Holds the sequetial growth rate %
3522 l_curr_seq_grw_rate NUMBER := 0 ;
3523 l_prev_seq_grw_rate NUMBER := 0 ;
3524 l_curr_sob_seq_grw_rate NUMBER := 0 ;
3525 l_prev_sob_seq_grw_rate NUMBER := 0 ;
3526 -- Holds the contract amount current and previous
3527 -- sequential growth rate records
3528 l_curr_k_amount NUMBER := 0 ;
3529 l_prev_k_amount NUMBER := 0 ;
3530 l_curr_sob_k_amount NUMBER := 0 ;
3531 l_prev_sob_k_amount NUMBER := 0 ;
3532
3533 -- Location within the program before the error was encountered.
3534 l_loc VARCHAR2(200) ;
3535
3536 -- Holds the truncated start and end dates from gl_periods
3537 -- Holds the quarter start and end dates
3538 l_glpr_qtr_start_date DATE ;
3539 l_glpr_qtr_end_date DATE ;
3540 -- Holds the prior year summary build date
3541 l_py_summary_build_date DATE ;
3542 -- Holds the start and end dates for the same quarter in the previous year
3543 l_sqpy_glpr_qtr_start_date DATE ;
3544 l_sqpy_glpr_qtr_end_date DATE ;
3545 /*
3546 -- Cusor declaration
3547
3548 -- Cursor that calculates the contract amount for all
3549 -- the active contracts
3550 CURSOR l_active_k_csr
3551 ( p_summary_build_date IN DATE
3552 ) IS
3553 SELECT NVL(SUM(base_contract_amount), 0) base_contract_amount
3554 , NVL(SUM(sob_contract_amount), 0) sob_contract_amount
3555 FROM oki_sales_k_hdrs shd
3556 WHERE shd.date_signed <= p_summary_build_date
3557 AND shd.date_approved <= p_summary_build_date
3558 AND shd.start_date <= p_summary_build_date
3559 AND shd.end_date > p_summary_build_date
3560 AND ( shd.date_terminated IS NULL
3561 OR shd.date_terminated > p_summary_build_date)
3562 ;
3563 rec_l_active_k_csr l_active_k_csr%ROWTYPE ;
3564
3568 ( p_glpr_qtr_start_date IN DATE
3565 -- Cursor that calculates contract amounts for all contracts
3566 -- expiring this quarter
3567 CURSOR l_expire_in_qtr_csr
3569 , p_glpr_qtr_end_date IN DATE
3570 , p_summary_build_date IN DATE
3571 )
3572 IS
3573 SELECT NVL(SUM(base_contract_amount), 0) base_contract_amount
3574 , NVL(SUM(sob_contract_amount), 0) sob_contract_amount
3575 FROM oki_sales_k_hdrs shd
3576 WHERE shd.date_signed <= p_glpr_qtr_end_date
3577 AND shd.date_approved <= p_glpr_qtr_end_date
3578 AND shd.end_date BETWEEN p_glpr_qtr_start_date
3579 AND p_glpr_qtr_end_date
3580 AND ( shd.date_terminated IS NULL
3581 OR shd.date_terminated > p_summary_build_date)
3582 ;
3583 rec_l_expire_in_qtr_csr l_expire_in_qtr_csr%ROWTYPE ;
3584
3585 -- Cursor that calculates contract amounts for contracts that
3586 -- have been renewed in this quarter
3587 CURSOR l_qtr_k_rnw_csr
3588 ( p_glpr_qtr_start_date IN DATE
3589 , p_summary_build_date IN DATE
3590 )
3591 IS
3592 SELECT NVL(SUM(base_contract_amount), 0) base_contract_amount
3593 , NVL(SUM(sob_contract_amount), 0) sob_contract_amount
3594 FROM oki_sales_k_hdrs shd
3595 WHERE shd.is_new_yn IS NULL
3596 AND shd.date_signed IS NOT NULL
3597 AND shd.date_approved IS NOT NULL
3598 AND shd.start_date BETWEEN p_glpr_qtr_start_date
3599 AND p_summary_build_date
3600 AND GREATEST(shd.date_signed, shd.date_approved)
3601 BETWEEN p_glpr_qtr_start_date
3602 AND p_summary_build_date
3603 ;
3604 rec_l_qtr_k_rnw_csr l_qtr_k_rnw_csr%ROWTYPE ;
3605
3606 -- Contracts that were renewed in this quarter but should
3607 -- have been renewed before this quarter
3608 CURSOR l_bklg_k_rnw_csr
3609 ( p_glpr_qtr_start_date IN DATE
3610 , p_summary_build_date IN DATE
3611 )
3612 IS
3613 SELECT NVL(SUM(base_contract_amount), 0) base_contract_amount
3614 , NVL(SUM(sob_contract_amount), 0) sob_contract_amount
3615 FROM oki_sales_k_hdrs shd
3616 WHERE shd.is_new_yn IS NULL
3617 AND shd.date_signed IS NOT NULL
3618 AND shd.date_approved IS NOT NULL
3619 AND shd.start_date < p_glpr_qtr_start_date
3620 AND GREATEST(shd.date_signed, shd.date_approved)
3621 BETWEEN p_glpr_qtr_start_date
3622 AND p_summary_build_date
3623 ;
3624 rec_l_bklg_k_rnw_csr l_bklg_k_rnw_csr%ROWTYPE ;
3625
3626 -- Contracts that are active in the current quarter that are not the
3627 -- result of renewal or renewal consolidation
3628 CURSOR l_new_bsn_csr
3629 ( p_glpr_qtr_start_date IN DATE
3630 , p_summary_build_date IN DATE
3631 )
3632 IS
3633 SELECT NVL(SUM(base_contract_amount), 0) base_contract_amount
3634 , NVL(SUM(sob_contract_amount), 0) sob_contract_amount
3635 FROM oki_sales_k_hdrs shd
3636 WHERE shd.date_signed <= p_summary_build_date
3637 AND shd.date_approved <= p_summary_build_date
3638 AND shd.is_new_yn = 'Y'
3639 AND shd.start_date BETWEEN p_glpr_qtr_start_date
3640 AND p_summary_build_date
3641 AND ( shd.date_terminated IS NULL
3642 OR shd.date_terminated > p_summary_build_date)
3643 ;
3644 rec_l_new_bsn_csr l_new_bsn_csr%ROWTYPE ;
3645
3646 -- Renewal or renewal consolidate contracts that have been cancelled
3647 CURSOR l_cncl_rnwl_csr
3648 ( p_glpr_qtr_start_date IN DATE
3649 , p_summary_build_date IN DATE
3650 )
3651 IS
3652 SELECT NVL(SUM(base_contract_amount), 0) base_contract_amount
3653 , NVL(SUM(sob_contract_amount), 0) sob_contract_amount
3654 FROM oki_sales_k_hdrs shd
3655 WHERE shd.ste_code = 'CANCELLED'
3656 AND shd.is_new_yn IS NULL
3657 AND shd.is_latest_yn IS NULL
3658 AND shd.start_date BETWEEN p_glpr_qtr_start_date
3659 AND p_summary_build_date
3660 ;
3661 rec_l_cncl_rnwl_csr l_cncl_rnwl_csr%ROWTYPE ;
3662
3663 -- Contracts that have been termined in this quarter
3664 CURSOR l_trmn_rnwl_csr
3665 ( p_glpr_qtr_start_date IN DATE
3666 , p_summary_build_date IN DATE
3667 )
3668 IS
3669 SELECT (((shd.end_date - shd.date_terminated) /
3670 (shd.end_date - shd.start_date)) *
3671 base_contract_amount) base_contract_amount
3672 , (((shd.end_date - shd.date_terminated) /
3673 (shd.end_date - shd.start_date)) *
3674 sob_contract_amount) sob_contract_amount
3675 FROM oki_sales_k_hdrs shd
3676 WHERE date_terminated BETWEEN p_glpr_qtr_start_date
3677 AND p_summary_build_date
3678 ;
3679 rec_l_trmn_rnwl_csr l_trmn_rnwl_csr%ROWTYPE ;
3680 */
3681
3682 BEGIN
3683
3684 -- initialize return code to success
3685 l_retcode := '0';
3686
3687 l_loc := 'Looping through valid organizations.' ;
3688 -- Loop through all the organizations to calcuate the
3689 -- appropriate amounts
3690
3691 l_loc := 'Looping through valid periods.' ;
3692 << g_glpr_csr_loop >>
3693 -- Loop through all the periods
3694 FOR rec_g_glpr_csr IN oki_utl_pvt.g_glpr_csr(
3695 p_period_set_name, p_period_type, p_summary_build_date ) LOOP
3696
3700
3697 -- set the quarter and year gl_periods start and end dates
3698 l_glpr_qtr_start_date := trunc(rec_g_glpr_csr.quarter_start_date) ;
3699 l_glpr_qtr_end_date := ADD_MONTHS(l_glpr_qtr_start_date, 3) - 1 ;
3701 -- Set the prior year summary build date
3702 l_py_summary_build_date := ADD_MONTHS(p_summary_build_date, - 12) ;
3703 -- Set the quarter start and end dates for the same quarter
3704 -- in the previous year
3705 l_sqpy_glpr_qtr_start_date := ADD_MONTHS(l_glpr_qtr_start_date, -12) ;
3706 l_sqpy_glpr_qtr_end_date := ADD_MONTHS(l_glpr_qtr_end_date, -12) ;
3707
3708 -- Re-initialize the amounts before calculating
3709 l_curr_active_k := 0 ;
3710 l_prev_active_k := 0 ;
3711 l_curr_sob_active_k := 0 ;
3712 l_prev_sob_active_k := 0 ;
3713 l_curr_end_active_k := 0 ;
3714 l_prev_end_active_k := 0 ;
3715 l_curr_sob_end_active_k := 0 ;
3716 l_prev_sob_end_active_k := 0 ;
3717 l_curr_seq_grw_rate := 0 ;
3718 l_prev_seq_grw_rate := 0 ;
3719 l_curr_sob_seq_grw_rate := 0 ;
3720 l_prev_sob_seq_grw_rate := 0 ;
3721 l_curr_k_amount := 0 ;
3722 l_prev_k_amount := 0 ;
3723 l_curr_sob_k_amount := 0 ;
3724 l_prev_sob_k_amount := 0 ;
3725
3726 l_loc := 'Opening cursor to determine the current beginning ' ;
3727 l_loc := l_loc || 'active contracts.' ;
3728 OPEN oki_load_sgr_pvt.g_active_k_csr ( p_summary_build_date ) ;
3729 FETCH oki_load_sgr_pvt.g_active_k_csr INTO rec_g_active_k_csr ;
3730 IF oki_load_sgr_pvt.g_active_k_csr%FOUND THEN
3731 l_curr_k_amount := rec_g_active_k_csr.base_contract_amount ;
3732 l_curr_sob_k_amount := rec_g_active_k_csr.sob_contract_amount ;
3733 -- keep the beginning active amount to determine the sequential
3734 -- growth rate later
3735 l_curr_active_k := l_curr_k_amount ;
3736 l_curr_sob_active_k := l_curr_sob_k_amount ;
3737 END IF;
3738 CLOSE oki_load_sgr_pvt.g_active_k_csr ;
3739
3740
3741 l_loc := 'Opening cursor to determine the previous beginning ' ;
3742 l_loc := l_loc || 'active contracts.' ;
3743 OPEN oki_load_sgr_pvt.g_active_k_csr ( l_py_summary_build_date ) ;
3744 FETCH oki_load_sgr_pvt.g_active_k_csr INTO rec_g_active_k_csr ;
3745 IF oki_load_sgr_pvt.g_active_k_csr%FOUND THEN
3746 l_prev_k_amount := rec_g_active_k_csr.base_contract_amount ;
3747 l_prev_sob_k_amount := rec_g_active_k_csr.sob_contract_amount ;
3748 -- keep the beginning active amount to determine the sequential
3749 -- growth rate later
3750 l_prev_active_k := l_prev_k_amount ;
3751 l_prev_sob_active_k := l_prev_sob_k_amount ;
3752 END IF ;
3753 CLOSE oki_load_sgr_pvt.g_active_k_csr ;
3754
3755 -- Determine running total for ending active contracts
3756 -- Add beginning active contract amount
3757 l_curr_end_active_k := l_curr_k_amount ;
3758 l_prev_end_active_k := l_prev_k_amount ;
3759 l_curr_sob_end_active_k := l_curr_sob_k_amount ;
3760 l_prev_sob_end_active_k := l_prev_sob_k_amount ;
3761
3762 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
3763 l_loc := l_loc || ' -- current / previous beginning active contracts' ;
3764 -- Determine if the record is a new one or an existing one
3765 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
3766 rec_g_glpr_csr.period_name, oki_load_sgr_pvt.g_all_org_id,
3767 oki_load_sgr_pvt.g_active_k_code, oki_load_sgr_pvt.g_all_scs_code,
3768 oki_load_sgr_pvt.g_all_cst_id, oki_load_sgr_pvt.g_all_pct_code,
3769 p_summary_build_date, rec_g_glpr_csr.period_type ) ;
3770 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
3771 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
3772 l_loc := 'Insert the new record.' ;
3773 l_loc := l_loc || ' -- current / previous beginning active contracts' ;
3774 -- Insert the current period data for the period
3775 oki_load_sgr_pvt.ins_seq_grw_rate (
3776 p_period_set_name => rec_g_glpr_csr.period_set_name
3777 , p_period_name => rec_g_glpr_csr.period_name
3778 , p_period_type => rec_g_glpr_csr.period_type
3779 , p_summary_build_date => p_summary_build_date
3780 , p_authoring_org_id => oki_load_sgr_pvt.g_all_org_id
3781 , p_authoring_org_name => oki_load_sgr_pvt.g_all_org_name
3782 , p_customer_party_id => oki_load_sgr_pvt.g_all_cst_id
3783 , p_customer_name => oki_load_sgr_pvt.g_all_cst_name
3784 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_active_k_code
3785 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
3786 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
3787 , p_curr_base_contract_amount => l_curr_k_amount
3788 , p_prev_base_contract_amount => l_prev_k_amount
3789 , p_curr_sob_contract_amount => l_curr_sob_k_amount
3790 , p_prev_sob_contract_amount => l_prev_sob_k_amount
3791 , x_retcode => l_retcode ) ;
3792 IF l_retcode = '2' THEN
3793 -- Load failed, exit immediately.
3794 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
3795 END IF ;
3796 ELSE
3797 l_loc := 'Update the existing record.' ;
3801 p_curr_base_contract_amount => l_curr_active_k
3798 l_loc := l_loc || ' -- current / previous beginning active contracts' ;
3799 -- Record already exists, so perform an update
3800 oki_load_sgr_pvt.upd_seq_grw_rate (
3802 , p_prev_base_contract_amount => l_prev_active_k
3803 , p_curr_sob_contract_amount => l_curr_sob_active_k
3804 , p_prev_sob_contract_amount => l_prev_sob_active_k
3805 , p_sgr_rowid => rec_g_sgr_csr.rowid
3806 , x_retcode => l_retcode ) ;
3807
3808 IF l_retcode = '2' THEN
3809 -- Load failed, exit immediately.
3810 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
3811 END IF ;
3812 END IF ;
3813 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
3814
3815 -- Re-initialize the amounts before calculating
3816 l_curr_k_amount := 0 ;
3817 l_prev_k_amount := 0 ;
3818 l_curr_sob_k_amount := 0 ;
3819 l_prev_sob_k_amount := 0 ;
3820
3821 l_loc := 'Opening cursor to determine the current expiring ' ;
3822 l_loc := l_loc || 'during this quarter.' ;
3823 OPEN oki_load_sgr_pvt.g_expire_in_qtr_csr ( l_glpr_qtr_start_date,
3824 l_glpr_qtr_end_date, p_summary_build_date ) ;
3825 FETCH oki_load_sgr_pvt.g_expire_in_qtr_csr INTO rec_g_expire_in_qtr_csr ;
3826 IF oki_load_sgr_pvt.g_expire_in_qtr_csr%FOUND THEN
3827 l_curr_k_amount := rec_g_expire_in_qtr_csr.base_contract_amount ;
3828 l_curr_sob_k_amount := rec_g_expire_in_qtr_csr.sob_contract_amount ;
3829 END IF;
3830 CLOSE oki_load_sgr_pvt.g_expire_in_qtr_csr ;
3831
3832 l_loc := 'Opening cursor to determine the previous expiring ' ;
3833 l_loc := l_loc || 'during this quarter.' ;
3834 OPEN oki_load_sgr_pvt.g_expire_in_qtr_csr ( l_sqpy_glpr_qtr_start_date,
3835 l_sqpy_glpr_qtr_end_date, l_py_summary_build_date ) ;
3836 FETCH oki_load_sgr_pvt.g_expire_in_qtr_csr INTO rec_g_expire_in_qtr_csr ;
3837 IF oki_load_sgr_pvt.g_expire_in_qtr_csr%FOUND THEN
3838 l_prev_k_amount := rec_g_expire_in_qtr_csr.base_contract_amount ;
3839 l_prev_sob_k_amount := rec_g_expire_in_qtr_csr.sob_contract_amount ;
3840 END IF ;
3841 CLOSE oki_load_sgr_pvt.g_expire_in_qtr_csr ;
3842
3843 -- Determine running total for ending active contracts
3844 -- Subtract expiring during contract amount
3845 l_curr_end_active_k := l_curr_end_active_k + (l_curr_k_amount * -1) ;
3846 l_prev_end_active_k := l_prev_end_active_k + (l_prev_k_amount * -1) ;
3847 l_curr_sob_end_active_k := l_curr_sob_end_active_k + (l_curr_sob_k_amount * -1) ;
3848 l_prev_sob_end_active_k := l_prev_sob_end_active_k + (l_prev_sob_k_amount * -1) ;
3849
3850 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
3851 l_loc := l_loc || ' -- current / previous expiring during quarter' ;
3852 -- Determine if the record is a new one or an existing one
3853 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
3854 rec_g_glpr_csr.period_name, oki_load_sgr_pvt.g_all_org_id,
3855 oki_load_sgr_pvt.g_exp_in_qtr_code, oki_load_sgr_pvt.g_all_scs_code,
3856 oki_load_sgr_pvt.g_all_cst_id, oki_load_sgr_pvt.g_all_pct_code,
3857 p_summary_build_date, rec_g_glpr_csr.period_type ) ;
3858 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
3859 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
3860
3861 l_loc := 'Insert the new record.' ;
3862 l_loc := l_loc || ' -- current / previous expiring during quarter' ;
3863 -- Insert the current period data for the period
3864 oki_load_sgr_pvt.ins_seq_grw_rate (
3865 p_period_set_name => rec_g_glpr_csr.period_set_name
3866 , p_period_name => rec_g_glpr_csr.period_name
3867 , p_period_type => rec_g_glpr_csr.period_type
3868 , p_summary_build_date => p_summary_build_date
3869 , p_authoring_org_id => oki_load_sgr_pvt.g_all_org_id
3870 , p_authoring_org_name => oki_load_sgr_pvt.g_all_org_name
3871 , p_customer_party_id => oki_load_sgr_pvt.g_all_cst_id
3872 , p_customer_name => oki_load_sgr_pvt.g_all_cst_name
3873 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_exp_in_qtr_code
3874 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
3875 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
3876 , p_curr_base_contract_amount => l_curr_k_amount
3877 , p_prev_base_contract_amount => l_prev_k_amount
3878 , p_curr_sob_contract_amount => l_curr_sob_k_amount
3879 , p_prev_sob_contract_amount => l_prev_sob_k_amount
3880 , x_retcode => l_retcode ) ;
3881 IF l_retcode = '2' THEN
3882 -- Load failed, exit immediately.
3883 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
3884 END IF ;
3885 ELSE
3886 l_loc := 'Update the existing record.' ;
3887 l_loc := l_loc || ' -- current / previous expiring during quarter' ;
3888 -- Record already exists, so perform an update
3889 oki_load_sgr_pvt.upd_seq_grw_rate (
3890 p_curr_base_contract_amount => l_curr_k_amount
3891 , p_prev_base_contract_amount => l_prev_k_amount
3895 , x_retcode => l_retcode ) ;
3892 , p_curr_sob_contract_amount => l_curr_sob_k_amount
3893 , p_prev_sob_contract_amount => l_prev_sob_k_amount
3894 , p_sgr_rowid => rec_g_sgr_csr.rowid
3896
3897 IF l_retcode = '2' THEN
3898 -- Load failed, exit immediately.
3899 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
3900 END IF ;
3901 END IF ;
3902 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
3903
3904 -- Re-initialize the amounts before calculating
3905 l_curr_k_amount := 0 ;
3906 l_prev_k_amount := 0 ;
3907 l_curr_sob_k_amount := 0 ;
3908 l_prev_sob_k_amount := 0 ;
3909
3910 l_loc := 'Opening cursor to determine the current quarter ' ;
3911 l_loc := l_loc || 'contracts renewed.' ;
3912 OPEN oki_load_sgr_pvt.g_qtr_k_rnw_csr ( l_glpr_qtr_start_date,
3913 p_summary_build_date ) ;
3914 FETCH oki_load_sgr_pvt.g_qtr_k_rnw_csr INTO rec_g_qtr_k_rnw_csr ;
3915 IF oki_load_sgr_pvt.g_qtr_k_rnw_csr%FOUND THEN
3916 l_curr_k_amount := rec_g_qtr_k_rnw_csr.base_contract_amount ;
3917 l_curr_sob_k_amount := rec_g_qtr_k_rnw_csr.sob_contract_amount ;
3918 END IF;
3919 CLOSE oki_load_sgr_pvt.g_qtr_k_rnw_csr ;
3920
3921 l_loc := 'Opening cursor to determine the previous quarter ' ;
3922 l_loc := l_loc || 'contracts renewed.' ;
3923 OPEN oki_load_sgr_pvt.g_qtr_k_rnw_csr ( l_sqpy_glpr_qtr_start_date,
3924 l_py_summary_build_date ) ;
3925 FETCH oki_load_sgr_pvt.g_qtr_k_rnw_csr INTO rec_g_qtr_k_rnw_csr ;
3926 IF oki_load_sgr_pvt.g_qtr_k_rnw_csr%FOUND THEN
3927 l_prev_k_amount := rec_g_qtr_k_rnw_csr.base_contract_amount ;
3928 l_prev_sob_k_amount := rec_g_qtr_k_rnw_csr.sob_contract_amount ;
3929 END IF ;
3930 CLOSE oki_load_sgr_pvt.g_qtr_k_rnw_csr ;
3931
3932 -- Determine running total for ending active contracts
3933 -- Add quarter contracts renewed amount
3934 l_curr_end_active_k := l_curr_end_active_k + l_curr_k_amount ;
3935 l_prev_end_active_k := l_prev_end_active_k + l_prev_k_amount ;
3936 l_curr_sob_end_active_k := l_curr_sob_end_active_k + l_curr_sob_k_amount ;
3937 l_prev_sob_end_active_k := l_prev_sob_end_active_k + l_prev_sob_k_amount ;
3938
3939 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
3940 l_loc := l_loc || ' -- current / previous quarter contracts renewed' ;
3941 -- Determine if the record is a new one or an existing one
3942 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
3943 rec_g_glpr_csr.period_name, oki_load_sgr_pvt.g_all_org_id,
3944 oki_load_sgr_pvt.g_qtr_k_rnw_code, oki_load_sgr_pvt.g_all_scs_code,
3945 oki_load_sgr_pvt.g_all_cst_id, oki_load_sgr_pvt.g_all_pct_code,
3946 p_summary_build_date, rec_g_glpr_csr.period_type ) ;
3947 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
3948 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
3949 l_loc := 'Insert the new record.' ;
3950 l_loc := l_loc || ' -- current / previous quarter contracts renewed' ;
3951 -- Insert the current period data for the period
3952 oki_load_sgr_pvt.ins_seq_grw_rate (
3953 p_period_set_name => rec_g_glpr_csr.period_set_name
3954 , p_period_name => rec_g_glpr_csr.period_name
3955 , p_period_type => rec_g_glpr_csr.period_type
3956 , p_summary_build_date => p_summary_build_date
3957 , p_authoring_org_id => oki_load_sgr_pvt.g_all_org_id
3958 , p_authoring_org_name => oki_load_sgr_pvt.g_all_org_name
3959 , p_customer_party_id => oki_load_sgr_pvt.g_all_cst_id
3960 , p_customer_name => oki_load_sgr_pvt.g_all_cst_name
3961 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_qtr_k_rnw_code
3962 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
3963 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
3964 , p_curr_base_contract_amount => l_curr_k_amount
3965 , p_prev_base_contract_amount => l_prev_k_amount
3966 , p_curr_sob_contract_amount => l_curr_sob_k_amount
3967 , p_prev_sob_contract_amount => l_prev_sob_k_amount
3968 , x_retcode => l_retcode ) ;
3969 IF l_retcode = '2' THEN
3970 -- Load failed, exit immediately.
3971 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
3972 END IF ;
3973 ELSE
3974 l_loc := 'Update the existing record.' ;
3975 l_loc := l_loc || ' -- current / previous quarter contracts renewed' ;
3976 -- Record already exists, so perform an update
3977 oki_load_sgr_pvt.upd_seq_grw_rate (
3978 p_curr_base_contract_amount => l_curr_k_amount
3979 , p_prev_base_contract_amount => l_prev_k_amount
3980 , p_curr_sob_contract_amount => l_curr_sob_k_amount
3981 , p_prev_sob_contract_amount => l_prev_sob_k_amount
3982 , p_sgr_rowid => rec_g_sgr_csr.rowid
3983 , x_retcode => l_retcode ) ;
3984 IF l_retcode = '2' THEN
3985 -- Load failed, exit immediately.
3986 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
3990
3987 END IF ;
3988 END IF ;
3989 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
3991 -- Re-initialize the amounts before calculating
3992 l_curr_k_amount := 0 ;
3993 l_prev_k_amount := 0 ;
3994 l_curr_sob_k_amount := 0 ;
3995 l_prev_sob_k_amount := 0 ;
3996
3997 l_loc := 'Opening cursor to determine the current backlog' ;
3998 l_loc := l_loc || 'contracts renewed.' ;
3999 OPEN oki_load_sgr_pvt.g_bklg_k_rnw_csr ( l_glpr_qtr_start_date,
4000 p_summary_build_date ) ;
4001 FETCH oki_load_sgr_pvt.g_bklg_k_rnw_csr INTO rec_g_bklg_k_rnw_csr ;
4002 IF oki_load_sgr_pvt.g_bklg_k_rnw_csr%FOUND THEN
4003 l_curr_k_amount := rec_g_bklg_k_rnw_csr.base_contract_amount ;
4004 l_curr_sob_k_amount := rec_g_bklg_k_rnw_csr.sob_contract_amount ;
4005 END IF;
4006 CLOSE oki_load_sgr_pvt.g_bklg_k_rnw_csr ;
4007
4008 l_loc := 'Opening cursor to determine the previous backlog' ;
4009 l_loc := l_loc || 'contracts renewed.' ;
4010 OPEN oki_load_sgr_pvt.g_bklg_k_rnw_csr ( l_sqpy_glpr_qtr_start_date,
4011 l_py_summary_build_date ) ;
4012 FETCH oki_load_sgr_pvt.g_bklg_k_rnw_csr INTO rec_g_bklg_k_rnw_csr ;
4013 IF oki_load_sgr_pvt.g_bklg_k_rnw_csr%FOUND THEN
4014 l_prev_k_amount := rec_g_bklg_k_rnw_csr.base_contract_amount ;
4015 l_prev_sob_k_amount := rec_g_bklg_k_rnw_csr.sob_contract_amount ;
4016 END IF;
4017 CLOSE oki_load_sgr_pvt.g_bklg_k_rnw_csr ;
4018
4019 -- Determine running total for ending active contracts
4020 -- Add backlog contracts renewed amount
4021 l_curr_end_active_k := l_curr_end_active_k + l_curr_k_amount ;
4022 l_prev_end_active_k := l_prev_end_active_k + l_prev_k_amount ;
4023 l_curr_sob_end_active_k := l_curr_sob_end_active_k + l_curr_sob_k_amount ;
4024 l_prev_sob_end_active_k := l_prev_sob_end_active_k + l_prev_sob_k_amount ;
4025
4026 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
4027 l_loc := l_loc || ' -- current / previous backlog contracts renewed' ;
4028 -- Determine if the record is a new one or an existing one
4029 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
4030 rec_g_glpr_csr.period_name, oki_load_sgr_pvt.g_all_org_id,
4031 oki_load_sgr_pvt.g_bklg_k_rnw_code, oki_load_sgr_pvt.g_all_scs_code,
4032 oki_load_sgr_pvt.g_all_cst_id, oki_load_sgr_pvt.g_all_pct_code,
4033 p_summary_build_date, rec_g_glpr_csr.period_type ) ;
4034 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
4035 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
4036 l_loc := 'Insert the new record.' ;
4037 l_loc := l_loc || ' -- current / previous backlog contracts renewed' ;
4038 -- Insert the current period data for the period
4039 oki_load_sgr_pvt.ins_seq_grw_rate (
4040 p_period_set_name => rec_g_glpr_csr.period_set_name
4041 , p_period_name => rec_g_glpr_csr.period_name
4042 , p_period_type => rec_g_glpr_csr.period_type
4043 , p_summary_build_date => p_summary_build_date
4044 , p_authoring_org_id => oki_load_sgr_pvt.g_all_org_id
4045 , p_authoring_org_name => oki_load_sgr_pvt.g_all_org_name
4046 , p_customer_party_id => oki_load_sgr_pvt.g_all_cst_id
4047 , p_customer_name => oki_load_sgr_pvt.g_all_cst_name
4048 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_bklg_k_rnw_code
4049 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
4050 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
4051 , p_curr_base_contract_amount => l_curr_k_amount
4052 , p_prev_base_contract_amount => l_prev_k_amount
4053 , p_curr_sob_contract_amount => l_curr_sob_k_amount
4054 , p_prev_sob_contract_amount => l_prev_sob_k_amount
4055 , x_retcode => l_retcode ) ;
4056 IF l_retcode = '2' THEN
4057 -- Load failed, exit immediately.
4058 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
4059 END IF ;
4060 ELSE
4061 l_loc := 'Update the existing record.' ;
4062 l_loc := l_loc || ' -- current / previous backlog contracts renewed' ;
4063 -- Record already exists, so perform an update
4064 oki_load_sgr_pvt.upd_seq_grw_rate (
4065 p_curr_base_contract_amount => l_curr_k_amount
4066 , p_prev_base_contract_amount => l_prev_k_amount
4067 , p_curr_sob_contract_amount => l_curr_sob_k_amount
4068 , p_prev_sob_contract_amount => l_prev_sob_k_amount
4069 , p_sgr_rowid => rec_g_sgr_csr.rowid
4070 , x_retcode => l_retcode ) ;
4071 IF l_retcode = '2' THEN
4072 -- Load failed, exit immediately.
4073 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
4074 END IF ;
4075 END IF ;
4076 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
4077
4078 -- Re-initialize the amounts before calculating
4079 l_curr_k_amount := 0 ;
4080 l_prev_k_amount := 0 ;
4081 l_curr_sob_k_amount := 0 ;
4082 l_prev_sob_k_amount := 0 ;
4083
4087 FETCH oki_load_sgr_pvt.g_new_bsn_csr INTO rec_g_new_bsn_csr ;
4084 l_loc := 'Opening cursor to determine the current new business.' ;
4085 OPEN oki_load_sgr_pvt.g_new_bsn_csr ( l_glpr_qtr_start_date,
4086 p_summary_build_date ) ;
4088 IF oki_load_sgr_pvt.g_new_bsn_csr%FOUND THEN
4089 l_curr_k_amount := rec_g_new_bsn_csr.base_contract_amount ;
4090 l_curr_sob_k_amount := rec_g_new_bsn_csr.sob_contract_amount ;
4091 END IF;
4092 CLOSE oki_load_sgr_pvt.g_new_bsn_csr ;
4093
4094 l_loc := 'Opening cursor to determine the previous new business.' ;
4095 OPEN oki_load_sgr_pvt.g_new_bsn_csr ( l_sqpy_glpr_qtr_start_date,
4096 l_py_summary_build_date ) ;
4097 FETCH oki_load_sgr_pvt.g_new_bsn_csr INTO rec_g_new_bsn_csr ;
4098 IF oki_load_sgr_pvt.g_new_bsn_csr%FOUND THEN
4099 l_prev_k_amount := rec_g_new_bsn_csr.base_contract_amount ;
4100 l_prev_sob_k_amount := rec_g_new_bsn_csr.sob_contract_amount ;
4101 END IF ;
4102 CLOSE oki_load_sgr_pvt.g_new_bsn_csr ;
4103
4104 -- Determine running total for ending active contracts
4105 -- Add new business amount
4106 l_curr_end_active_k := l_curr_end_active_k + l_curr_k_amount ;
4107 l_prev_end_active_k := l_prev_end_active_k + l_prev_k_amount ;
4108 l_curr_sob_end_active_k := l_curr_sob_end_active_k + l_curr_sob_k_amount ;
4109 l_prev_sob_end_active_k := l_prev_sob_end_active_k + l_prev_sob_k_amount ;
4110
4111 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
4112 l_loc := l_loc || ' -- current / previous new business' ;
4113 -- Determine if the record is a new one or an existing one
4114 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
4115 rec_g_glpr_csr.period_name, oki_load_sgr_pvt.g_all_org_id,
4116 oki_load_sgr_pvt.g_new_bsn_code, oki_load_sgr_pvt.g_all_scs_code,
4117 oki_load_sgr_pvt.g_all_cst_id, oki_load_sgr_pvt.g_all_pct_code,
4118 p_summary_build_date, rec_g_glpr_csr.period_type ) ;
4119 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
4120 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
4121 l_loc := 'Insert the new record.' ;
4122 l_loc := l_loc || ' -- current / previous new business' ;
4123 -- Insert the current period data for the period
4124 oki_load_sgr_pvt.ins_seq_grw_rate (
4125 p_period_set_name => rec_g_glpr_csr.period_set_name
4126 , p_period_name => rec_g_glpr_csr.period_name
4127 , p_period_type => rec_g_glpr_csr.period_type
4128 , p_summary_build_date => p_summary_build_date
4129 , p_authoring_org_id => oki_load_sgr_pvt.g_all_org_id
4130 , p_authoring_org_name => oki_load_sgr_pvt.g_all_org_name
4131 , p_customer_party_id => oki_load_sgr_pvt.g_all_cst_id
4132 , p_customer_name => oki_load_sgr_pvt.g_all_cst_name
4133 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_new_bsn_code
4134 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
4135 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
4136 , p_curr_base_contract_amount => l_curr_k_amount
4137 , p_prev_base_contract_amount => l_prev_k_amount
4138 , p_curr_sob_contract_amount => l_curr_sob_k_amount
4139 , p_prev_sob_contract_amount => l_prev_sob_k_amount
4140 , x_retcode => l_retcode ) ;
4141 IF l_retcode = '2' THEN
4142 -- Load failed, exit immediately.
4143 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
4144 END IF ;
4145 ELSE
4146 l_loc := 'Update the existing record.' ;
4147 l_loc := l_loc || ' -- current / previous new business' ;
4148 -- Record already exists, so perform an update
4149 oki_load_sgr_pvt.upd_seq_grw_rate (
4150 p_curr_base_contract_amount => l_curr_k_amount
4151 , p_prev_base_contract_amount => l_prev_k_amount
4152 , p_curr_sob_contract_amount => l_curr_sob_k_amount
4153 , p_prev_sob_contract_amount => l_prev_sob_k_amount
4154 , p_sgr_rowid => rec_g_sgr_csr.rowid
4155 , x_retcode => l_retcode ) ;
4156
4157 IF l_retcode = '2' THEN
4158 -- Load failed, exit immediately.
4159 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
4160 END IF ;
4161 END IF ;
4162 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
4163
4164 -- Re-initialize the amounts before calculating
4165 l_curr_k_amount := 0 ;
4166 l_prev_k_amount := 0 ;
4167 l_curr_sob_k_amount := 0 ;
4168 l_prev_sob_k_amount := 0 ;
4169
4170 l_loc := 'Opening cursor to determine the current cancelled renewals.' ;
4171 OPEN oki_load_sgr_pvt.g_cncl_rnwl_csr( l_glpr_qtr_start_date,
4172 p_summary_build_date ) ;
4173 FETCH oki_load_sgr_pvt.g_cncl_rnwl_csr INTO rec_g_cncl_rnwl_csr ;
4174 IF oki_load_sgr_pvt.g_cncl_rnwl_csr%FOUND THEN
4175 l_curr_k_amount := rec_g_cncl_rnwl_csr.base_contract_amount ;
4176 l_curr_sob_k_amount := rec_g_cncl_rnwl_csr.sob_contract_amount ;
4177 END IF;
4178 CLOSE oki_load_sgr_pvt.g_cncl_rnwl_csr ;
4179
4180 l_loc := 'Opening cursor to determine the previous cancelled renewals.' ;
4184 IF oki_load_sgr_pvt.g_cncl_rnwl_csr%FOUND THEN
4181 OPEN oki_load_sgr_pvt.g_cncl_rnwl_csr( l_sqpy_glpr_qtr_start_date,
4182 l_py_summary_build_date ) ;
4183 FETCH oki_load_sgr_pvt.g_cncl_rnwl_csr INTO rec_g_cncl_rnwl_csr ;
4185 l_prev_k_amount := rec_g_cncl_rnwl_csr.base_contract_amount ;
4186 l_prev_sob_k_amount := rec_g_cncl_rnwl_csr.sob_contract_amount ;
4187 END IF ;
4188 CLOSE oki_load_sgr_pvt.g_cncl_rnwl_csr ;
4189
4190 -- Determine running total for ending active contracts
4191 -- Subtract cancelled contract amount
4192 l_curr_end_active_k := l_curr_end_active_k + (l_curr_k_amount * -1) ;
4193 l_prev_end_active_k := l_prev_end_active_k + (l_prev_k_amount * -1) ;
4194 l_curr_sob_end_active_k := l_curr_sob_end_active_k + (l_curr_sob_k_amount * -1) ;
4195 l_prev_sob_end_active_k := l_prev_sob_end_active_k + (l_prev_sob_k_amount * -1) ;
4196
4197 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
4198 l_loc := l_loc || ' -- current / previous cancelled contract' ;
4199 -- Determine if the record is a new one or an existing one
4200 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
4201 rec_g_glpr_csr.period_name, oki_load_sgr_pvt.g_all_org_id,
4202 oki_load_sgr_pvt.g_cncl_rnwl_code, oki_load_sgr_pvt.g_all_scs_code,
4203 oki_load_sgr_pvt.g_all_cst_id, oki_load_sgr_pvt.g_all_pct_code,
4204 p_summary_build_date, rec_g_glpr_csr.period_type ) ;
4205 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
4206 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
4207 l_loc := 'Insert the new record.' ;
4208 l_loc := l_loc || ' -- current / previous cancelled renewals' ;
4209 -- Insert the current period data for the period
4210 oki_load_sgr_pvt.ins_seq_grw_rate (
4211 p_period_set_name => rec_g_glpr_csr.period_set_name
4212 , p_period_name => rec_g_glpr_csr.period_name
4213 , p_period_type => rec_g_glpr_csr.period_type
4214 , p_summary_build_date => p_summary_build_date
4215 , p_authoring_org_id => oki_load_sgr_pvt.g_all_org_id
4216 , p_authoring_org_name => oki_load_sgr_pvt.g_all_org_name
4217 , p_customer_party_id => oki_load_sgr_pvt.g_all_cst_id
4218 , p_customer_name => oki_load_sgr_pvt.g_all_cst_name
4219 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_cncl_rnwl_code
4220 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
4221 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
4222 , p_curr_base_contract_amount => l_curr_k_amount
4223 , p_prev_base_contract_amount => l_prev_k_amount
4224 , p_curr_sob_contract_amount => l_curr_sob_k_amount
4225 , p_prev_sob_contract_amount => l_prev_sob_k_amount
4226 , x_retcode => l_retcode ) ;
4227 IF l_retcode = '2' THEN
4228 -- Load failed, exit immediately.
4229 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
4230 END IF ;
4231 ELSE
4232 l_loc := 'Update the existing record.' ;
4233 l_loc := l_loc || ' -- current / previous cancelled renewals' ;
4234 -- Record already exists, so perform an update
4235 oki_load_sgr_pvt.upd_seq_grw_rate (
4236 p_curr_base_contract_amount => l_curr_k_amount
4237 , p_prev_base_contract_amount => l_prev_k_amount
4238 , p_curr_sob_contract_amount => l_curr_sob_k_amount
4239 , p_prev_sob_contract_amount => l_prev_sob_k_amount
4240 , p_sgr_rowid => rec_g_sgr_csr.rowid
4241 , x_retcode => l_retcode ) ;
4242
4243 IF l_retcode = '2' THEN
4244 -- Load failed, exit immediately.
4245 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
4246 END IF ;
4247 END IF ;
4248 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
4249
4250 -- Re-initialize the amounts before calculating
4251 l_curr_k_amount := 0 ;
4252 l_prev_k_amount := 0 ;
4253 l_curr_sob_k_amount := 0 ;
4254 l_prev_sob_k_amount := 0 ;
4255
4256 l_loc := 'Looping through all the current contracts terminated in the period.';
4257 << l_trmn_rnwl_csr_loop >>
4258 -- Loop through all the contracts terminated in the period
4259 FOR rec_g_trmn_rnwl_csr IN oki_load_sgr_pvt.g_trmn_rnwl_csr(
4260 l_glpr_qtr_start_date, p_summary_build_date ) LOOP
4261 l_curr_k_amount := l_curr_k_amount +
4262 rec_g_trmn_rnwl_csr.base_contract_amount ;
4263 l_curr_sob_k_amount := l_curr_sob_k_amount +
4264 rec_g_trmn_rnwl_csr.sob_contract_amount ;
4265 END LOOP l_trmn_rnwl_csr_loop ;
4266 l_curr_k_amount := ROUND(l_curr_k_amount, 2) ;
4267 l_curr_sob_k_amount := ROUND(l_curr_sob_k_amount, 2) ;
4268
4269 l_loc := 'Looping through all the previous contracts terminated in the period.';
4270 << l_trmn_rnwl_csr_loop >>
4271 -- Loop through all the contracts terminated in the period
4272 FOR rec_g_trmn_rnwl_csr IN oki_load_sgr_pvt.g_trmn_rnwl_csr(
4273 l_sqpy_glpr_qtr_start_date, l_py_summary_build_date ) LOOP
4274 l_prev_k_amount := l_prev_k_amount +
4275 rec_g_trmn_rnwl_csr.base_contract_amount ;
4276 l_prev_sob_k_amount := l_prev_sob_k_amount +
4280 l_prev_sob_k_amount := ROUND(l_prev_sob_k_amount, 2) ;
4277 rec_g_trmn_rnwl_csr.sob_contract_amount ;
4278 END LOOP l_trmn_rnwl_csr_loop ;
4279 l_prev_k_amount := ROUND(l_prev_k_amount, 2) ;
4281
4282 -- Determine running total for ending active contracts
4283 -- Subtract terminated contract amount
4284 l_curr_end_active_k := l_curr_end_active_k + (l_curr_k_amount * -1);
4285 l_prev_end_active_k := l_prev_end_active_k + (l_prev_k_amount * -1) ;
4286 l_curr_sob_end_active_k := l_curr_sob_end_active_k + (l_curr_sob_k_amount * -1) ;
4287 l_prev_sob_end_active_k := l_prev_sob_end_active_k + (l_prev_sob_k_amount * -1) ;
4288
4289 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
4290 l_loc := l_loc || ' -- current / previous terminated renewals' ;
4291 -- Determine if the record is a new one or an existing one
4292 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
4293 rec_g_glpr_csr.period_name, oki_load_sgr_pvt.g_all_org_id,
4294 oki_load_sgr_pvt.g_seq_trmn_k_code, oki_load_sgr_pvt.g_all_scs_code,
4295 oki_load_sgr_pvt.g_all_cst_id, oki_load_sgr_pvt.g_all_pct_code,
4296 p_summary_build_date, rec_g_glpr_csr.period_type ) ;
4297 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
4298 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
4299 l_loc := 'Insert the new record.' ;
4300 l_loc := l_loc || ' -- current / previous terminated renewals' ;
4301 -- Insert the current period data for the period
4302 oki_load_sgr_pvt.ins_seq_grw_rate (
4303 p_period_set_name => rec_g_glpr_csr.period_set_name
4304 , p_period_name => rec_g_glpr_csr.period_name
4305 , p_period_type => rec_g_glpr_csr.period_type
4306 , p_summary_build_date => p_summary_build_date
4307 , p_authoring_org_id => oki_load_sgr_pvt.g_all_org_id
4308 , p_authoring_org_name => oki_load_sgr_pvt.g_all_org_name
4309 , p_customer_party_id => oki_load_sgr_pvt.g_all_cst_id
4310 , p_customer_name => oki_load_sgr_pvt.g_all_cst_name
4311 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_seq_trmn_k_code
4312 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
4313 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
4314 , p_curr_base_contract_amount => l_curr_k_amount
4315 , p_prev_base_contract_amount => l_prev_k_amount
4316 , p_curr_sob_contract_amount => l_curr_sob_k_amount
4317 , p_prev_sob_contract_amount => l_prev_sob_k_amount
4318 , x_retcode => l_retcode ) ;
4319 IF l_retcode = '2' THEN
4320 -- Load failed, exit immediately.
4321 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
4322 END IF ;
4323 ELSE
4324 l_loc := 'Update the existing record.' ;
4325 l_loc := l_loc || ' -- current / previous terminated renewals' ;
4326 -- Record already exists, so perform an update
4327 oki_load_sgr_pvt.upd_seq_grw_rate (
4328 p_curr_base_contract_amount => l_curr_k_amount
4329 , p_prev_base_contract_amount => l_prev_k_amount
4330 , p_curr_sob_contract_amount => l_curr_sob_k_amount
4331 , p_prev_sob_contract_amount => l_prev_sob_k_amount
4332 , p_sgr_rowid => rec_g_sgr_csr.rowid
4333 , x_retcode => l_retcode ) ;
4334
4335 IF l_retcode = '2' THEN
4336 -- Load failed, exit immediately.
4337 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
4338 END IF ;
4339 END IF ;
4340 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
4341
4342 -- Re-initialize the amounts before calculating
4343 l_curr_k_amount := 0 ;
4344 l_prev_k_amount := 0 ;
4345 l_curr_sob_k_amount := 0 ;
4346 l_prev_sob_k_amount := 0 ;
4347
4348 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
4349 l_loc := l_loc || ' -- current / previous ending active contracts' ;
4350 -- Determine if the record is a new one or an existing one
4351 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
4352 rec_g_glpr_csr.period_name, oki_load_sgr_pvt.g_all_org_id,
4353 oki_load_sgr_pvt.g_end_active_k_code, oki_load_sgr_pvt.g_all_scs_code,
4354 oki_load_sgr_pvt.g_all_cst_id, oki_load_sgr_pvt.g_all_pct_code,
4355 p_summary_build_date, rec_g_glpr_csr.period_type ) ;
4356 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
4357 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
4358 l_loc := 'Insert the new record.' ;
4359 l_loc := l_loc || ' -- current / previous ending active contracts' ;
4360 -- Insert the current period data for the period
4361 oki_load_sgr_pvt.ins_seq_grw_rate (
4362 p_period_set_name => rec_g_glpr_csr.period_set_name
4363 , p_period_name => rec_g_glpr_csr.period_name
4364 , p_period_type => rec_g_glpr_csr.period_type
4365 , p_summary_build_date => p_summary_build_date
4366 , p_authoring_org_id => oki_load_sgr_pvt.g_all_org_id
4367 , p_authoring_org_name => oki_load_sgr_pvt.g_all_org_name
4368 , p_customer_party_id => oki_load_sgr_pvt.g_all_cst_id
4372 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
4369 , p_customer_name => oki_load_sgr_pvt.g_all_cst_name
4370 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_end_active_k_code
4371 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
4373 , p_curr_base_contract_amount => l_curr_end_active_k
4374 , p_prev_base_contract_amount => l_prev_end_active_k
4375 , p_curr_sob_contract_amount => l_curr_sob_end_active_k
4376 , p_prev_sob_contract_amount => l_prev_sob_end_active_k
4377 , x_retcode => l_retcode ) ;
4378 IF l_retcode = '2' THEN
4379 -- Load failed, exit immediately.
4380 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
4381 END IF ;
4382 ELSE
4383 l_loc := 'Update the existing record.' ;
4384 l_loc := l_loc || ' -- current / previous ending active contracts' ;
4385 -- Record already exists, so perform an update
4386 oki_load_sgr_pvt.upd_seq_grw_rate (
4387 p_curr_base_contract_amount => l_curr_end_active_k
4388 , p_prev_base_contract_amount => l_prev_end_active_k
4389 , p_curr_sob_contract_amount => l_curr_sob_end_active_k
4390 , p_prev_sob_contract_amount => l_prev_sob_end_active_k
4391 , p_sgr_rowid => rec_g_sgr_csr.rowid
4392 , x_retcode => l_retcode ) ;
4393
4394 IF l_retcode = '2' THEN
4395 -- Load failed, exit immediately.
4396 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
4397 END IF ;
4398 END IF ;
4399 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
4400
4401
4402 -- Re-initialize the amounts before calculating
4403 l_curr_k_amount := 0 ;
4404 l_prev_k_amount := 0 ;
4405 l_curr_sob_k_amount := 0 ;
4406 l_prev_sob_k_amount := 0 ;
4407
4408 -- If the denominator is zero, then set the sequential growth rate to zero
4409 l_loc := 'Setting the sequential growth rate value.' ;
4410 IF l_curr_active_k = 0 THEN
4411 l_curr_seq_grw_rate := 0 ;
4412 ELSE
4413 l_curr_seq_grw_rate := ROUND(((l_curr_end_active_k -
4414 l_curr_active_k ) / l_curr_active_k ) * 100, 2) ;
4415 END IF ;
4416
4417 IF l_curr_sob_active_k = 0 THEN
4418 l_curr_sob_seq_grw_rate := 0 ;
4419 ELSE
4420 l_curr_sob_seq_grw_rate := ROUND(((l_curr_sob_end_active_k -
4421 l_curr_sob_active_k ) / l_curr_sob_active_k ) * 100, 2) ;
4422 END IF ;
4423
4424 IF l_prev_active_k = 0 THEN
4425 l_prev_seq_grw_rate := 0 ;
4426 ELSE
4427 l_prev_seq_grw_rate := ROUND(((l_prev_end_active_k -
4428 l_prev_active_k ) / l_prev_active_k ) * 100, 2) ;
4429 END IF ;
4430
4431 IF l_prev_sob_active_k = 0 THEN
4432 l_prev_sob_seq_grw_rate := 0 ;
4433 ELSE
4434 l_prev_sob_seq_grw_rate := ROUND(((l_prev_sob_end_active_k -
4435 l_prev_sob_active_k ) / l_prev_sob_active_k ) * 100, 2) ;
4436 END IF ;
4437
4438 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
4439 l_loc := l_loc || ' -- current / previous sequentail growth rate' ;
4440 -- Determine if the record is a new one or an existing one
4441 OPEN oki_load_sgr_pvt.g_sgr_csr ( rec_g_glpr_csr.period_set_name,
4442 rec_g_glpr_csr.period_name, oki_load_sgr_pvt.g_all_org_id,
4443 oki_load_sgr_pvt.g_seq_grw_rate_code, oki_load_sgr_pvt.g_all_scs_code,
4444 oki_load_sgr_pvt.g_all_cst_id, oki_load_sgr_pvt.g_all_pct_code,
4445 p_summary_build_date, rec_g_glpr_csr.period_type ) ;
4446 FETCH oki_load_sgr_pvt.g_sgr_csr INTO rec_g_sgr_csr ;
4447 IF oki_load_sgr_pvt.g_sgr_csr%NOTFOUND THEN
4448 l_loc := 'Insert the new record.' ;
4449 l_loc := l_loc || ' -- current / previous sequentail growth rate' ;
4450 -- Insert the current period data for the period
4451 oki_load_sgr_pvt.ins_seq_grw_rate (
4452 p_period_set_name => rec_g_glpr_csr.period_set_name
4453 , p_period_name => rec_g_glpr_csr.period_name
4454 , p_period_type => rec_g_glpr_csr.period_type
4455 , p_summary_build_date => p_summary_build_date
4456 , p_authoring_org_id => oki_load_sgr_pvt.g_all_org_id
4457 , p_authoring_org_name => oki_load_sgr_pvt.g_all_org_name
4458 , p_customer_party_id => oki_load_sgr_pvt.g_all_cst_id
4459 , p_customer_name => oki_load_sgr_pvt.g_all_cst_name
4460 , p_seq_grw_rate_code => oki_load_sgr_pvt.g_seq_grw_rate_code
4461 , p_scs_code => oki_load_sgr_pvt.g_all_scs_code
4462 , p_product_category_code => oki_load_sgr_pvt.g_all_pct_code
4463 , p_curr_base_contract_amount => l_curr_seq_grw_rate
4464 , p_prev_base_contract_amount => l_prev_seq_grw_rate
4465 , p_curr_sob_contract_amount => l_curr_sob_seq_grw_rate
4466 , p_prev_sob_contract_amount => l_prev_sob_seq_grw_rate
4467 , x_retcode => l_retcode ) ;
4468 IF l_retcode = '2' THEN
4469 -- Load failed, exit immediately.
4470 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
4471 END IF ;
4475 -- Record already exists, so perform an update
4472 ELSE
4473 l_loc := 'Update the existing record.' ;
4474 l_loc := l_loc || ' -- current / previous sequentail growth rate' ;
4476 oki_load_sgr_pvt.upd_seq_grw_rate (
4477 p_curr_base_contract_amount => l_curr_seq_grw_rate
4478 , p_prev_base_contract_amount => l_prev_seq_grw_rate
4479 , p_curr_sob_contract_amount => l_curr_sob_seq_grw_rate
4480 , p_prev_sob_contract_amount => l_prev_sob_seq_grw_rate
4481 , p_sgr_rowid => rec_g_sgr_csr.rowid
4482 , x_retcode => l_retcode ) ;
4483
4484 IF l_retcode = '2' THEN
4485 -- Load failed, exit immediately.
4486 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
4487 END IF ;
4488 END IF ;
4489 CLOSE oki_load_sgr_pvt.g_sgr_csr ;
4490
4491 END LOOP g_glpr_csr_loop ;
4492
4493 EXCEPTION
4494 WHEN oki_load_sgr_pvt.g_excp_exit_immediate THEN
4495 -- Do not log an error ; It has already been logged.
4496 -- Set return code to error
4497 x_retcode := '2' ;
4498
4499
4500 WHEN OTHERS THEN
4501 l_sqlcode := SQLCODE ;
4502 l_sqlerrm := SQLERRM ;
4503
4504 -- Set return code TO error
4505 x_retcode := '2' ;
4506
4507 fnd_message.set_name( application => 'OKI'
4508 , name => 'OKI_UNEXPECTED_FAILURE');
4509
4510 fnd_message.set_token( token => 'OBJECT_NAME'
4511 , value => 'OKI_LOAD_SGR_PVT.CALC_SGR_SUM');
4512
4513 fnd_file.put_line( which => fnd_file.log
4514 , buff => fnd_message.get);
4515
4516 -- Log the location within the procedure where the error occurred
4517 fnd_message.set_name( application => 'OKI'
4518 , name => 'OKI_LOC_IN_PROG_FAILURE');
4519
4520 fnd_message.set_token( token => 'LOCATION'
4521 , value => l_loc);
4522
4523 fnd_file.put_line( which => fnd_file.log
4524 , buff => fnd_message.get);
4525
4526 fnd_file.put_line( which => fnd_file.log
4527 , buff => l_sqlcode||' '|| l_sqlerrm );
4528 END calc_sgr_sum ;
4529
4530 --------------------------------------------------------------------------------
4531 -- Procedure to create all the seqeuantial growth rate records. If an
4532 -- error is encountered in this procedure or subsequent procedures then
4533 -- rollback all changes. Once the table is loaded and the data is committed
4534 -- the load is considered successful even if update of the oki_refreshs
4535 -- table failed.
4536 --------------------------------------------------------------------------------
4537 PROCEDURE crt_seq_grw
4538 ( p_period_set_name IN VARCHAR2
4539 , p_period_type IN VARCHAR2
4540 , p_start_summary_build_date IN DATE
4541 , p_end_summary_build_date IN DATE
4542 , x_errbuf OUT VARCHAR2
4543 , x_retcode OUT VARCHAR2
4544 ) IS
4545
4546
4547 -- Local exception declaration
4548
4549 -- Exception to immediately exit the procedure
4550 l_excp_upd_refresh EXCEPTION ;
4551
4552
4553 -- Constant declaration
4554
4555 -- Name of the table for which data is being inserted
4556 l_table_name CONSTANT VARCHAR2(30) := 'OKI_SEQ_GROWTH_RATE' ;
4557
4558
4559 -- Local variable declaration
4560
4561 -- For capturing the return code, 0 = success, 1 = warning, 2 = error
4562 l_retcode VARCHAR2(1) := NULL ;
4563
4564 -- For error handling
4565 l_sqlcode VARCHAR2(100) ;
4566 l_sqlerrm VARCHAR2(1000) ;
4567
4568 l_upper_bound NUMBER := 0 ;
4569 l_summary_build_date DATE := NULL ;
4570
4571 l_ending_period_type VARCHAR2(15) := NULL ;
4572
4573
4574 BEGIN
4575
4576 SAVEPOINT oki_load_sgr_pvt_crt_seq_grw ;
4577
4578 -- initialize return code to success
4579 l_retcode := '0' ;
4580 x_retcode := '0' ;
4581
4582 l_upper_bound := TRUNC(p_end_summary_build_date) -
4583 TRUNC(p_start_summary_build_date) + 1 ;
4584
4585 l_summary_build_date := TRUNC(p_start_summary_build_date) ;
4586
4587 FOR i IN 1..l_upper_bound LOOP
4588
4589 -- Procedure to calculate the amounts for each customer
4590 oki_load_sgr_pvt.calc_sgr_dtl1 (
4591 p_period_set_name => p_period_set_name
4592 , p_period_type => p_period_type
4593 , p_summary_build_date => l_summary_build_date
4594 , x_retcode => l_retcode ) ;
4595
4596 IF l_retcode = '2' THEN
4597 -- Load failed, exit immediately.
4598 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
4599 END IF ;
4600
4601 -- Procedure to calculate the amounts for each organization
4602 oki_load_sgr_pvt.calc_sgr_dtl2 (
4603 p_period_set_name => p_period_set_name
4604 , p_period_type => p_period_type
4605 , p_summary_build_date => l_summary_build_date
4606 , x_retcode => l_retcode ) ;
4607
4608 IF l_retcode = '2' THEN
4609 -- Load failed, exit immediately.
4610 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
4614 l_ending_period_type := 'Quarter' ;
4611 END IF ;
4612
4613 /*
4615 -- Procedure to calculate the amounts across organizations
4616 oki_load_sgr_pvt.calc_sgr_dtl3 (
4617 p_period_set_name => p_period_set_name
4618 , p_period_type => p_period_type
4619 , p_summary_build_date => l_summary_build_date
4620 , p_ending_period_type => l_ending_period_type
4621 , x_retcode => l_retcode ) ;
4622
4623 IF l_retcode = '2' THEN
4624 -- Load failed, exit immediately.
4625 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
4626 END IF ;
4627
4628 l_ending_period_type := 'Year' ;
4629 -- Procedure to calculate the amounts across organizations
4630 oki_load_sgr_pvt.calc_sgr_dtl3 (
4631 p_period_set_name => p_period_set_name
4632 , p_period_type => p_period_type
4633 , p_summary_build_date => l_summary_build_date
4634 , p_ending_period_type => l_ending_period_type
4635 , x_retcode => l_retcode ) ;
4636
4637 IF l_retcode = '2' THEN
4638 -- Load failed, exit immediately.
4639 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
4640 END IF ;
4641 */
4642 -- Procedure to calculate the amounts amounts across organizations,
4643 -- subclasses
4644 oki_load_sgr_pvt.calc_sgr_sum (
4645 p_period_set_name => p_period_set_name
4646 , p_period_type => p_period_type
4647 , p_summary_build_date => l_summary_build_date
4648 , x_retcode => l_retcode ) ;
4649
4650 IF l_retcode = '2' THEN
4651 -- Load failed, exit immediately.
4652 RAISE oki_load_sgr_pvt.g_excp_exit_immediate ;
4653 END IF ;
4654
4655 l_summary_build_date := l_summary_build_date + 1 ;
4656
4657 END LOOP ;
4658
4659 COMMIT;
4660
4661 SAVEPOINT oki_load_sgr_pvt_upd_refresh ;
4662
4663
4664 -- Table loaded successfully. Log message IN concurrent manager
4665 -- log indicating successful load.
4666 fnd_message.set_name( application => 'OKI'
4667 , name => 'OKI_TABLE_LOAD_SUCCESS');
4668
4669 fnd_message.set_token( token => 'TABLE_NAME'
4670 , value => l_table_name );
4671
4672 fnd_file.put_line( which => fnd_file.log
4673 , buff => fnd_message.get);
4674
4675 oki_refresh_pvt.update_oki_refresh( l_table_name, l_retcode ) ;
4676
4677 IF l_retcode in ('1', '2') THEN
4678 -- Update to OKI_REFRESHS failed, exit immediately.
4679 RAISE l_excp_upd_refresh ;
4680 END IF ;
4681
4682 COMMIT ;
4683
4684 EXCEPTION
4685 WHEN l_excp_upd_refresh THEN
4686 -- Do not log error; It has already been logged by the refreshs
4687 -- program
4688 x_retcode := l_retcode ;
4689
4690 ROLLBACK to oki_load_sgr_pvt_upd_refresh ;
4691
4692 WHEN oki_load_sgr_pvt.g_excp_exit_immediate THEN
4693 -- Do not log an error ; It has already been logged.
4694 -- Set return code to error
4695 x_retcode := '2' ;
4696
4697 ROLLBACK TO oki_load_sgr_pvt_crt_seq_grw ;
4698
4699 WHEN OTHERS THEN
4700
4701 l_sqlcode := SQLCODE ;
4702 l_sqlerrm := SQLERRM ;
4703
4704 -- Set return code TO error
4705 x_retcode := '2' ;
4706
4707 -- ROLLBACK all transactions
4708 ROLLBACK TO oki_load_sgr_pvt_crt_seq_grw ;
4709
4710
4711 fnd_message.set_name( application => 'OKI'
4712 , name => 'OKI_UNEXPECTED_FAILURE');
4713
4714 fnd_message.set_token( token => 'OBJECT_NAME'
4715 , value => 'OKI_LOAD_SGR_PVT.CRT_SEQ_GRW');
4716
4717 fnd_file.put_line( which => fnd_file.log
4718 , buff => fnd_message.get);
4719
4720 fnd_file.put_line( which => fnd_file.log
4721 , buff => l_sqlcode||' '||l_sqlerrm );
4722
4723 END crt_seq_grw ;
4724
4725
4726 BEGIN
4727 -- Initialize the global variables used TO log this job run
4728 -- FROM concurrent manager
4729 g_request_id := fnd_global.conc_request_id ;
4730 g_program_application_id := fnd_global.prog_appl_id ;
4731 g_program_id := fnd_global.conc_program_id ;
4732 g_program_update_date := SYSDATE ;
4733
4734 END oki_load_sgr_pvt ;