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