1 PACKAGE BODY oki_load_fbo_pvt AS
2 /* $Header: OKIRFBOB.pls 115.21 2003/11/24 08:24:48 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.
11 -- 18-Sep-2001 mezra Moved fbo_csr from local cursor to global
12 -- cursor since it is used by all the calc
13 -- procedures.
14 -- 25-Sep-2001 mezra Change usd_ columns to base_.
15 -- 22-Oct-2001 mezra Changed All Categories value to -1.
16 -- 24-Oct-2001 mezra Removed trunc on date columns to increase
17 -- performance since index will be used.
18 -- 26-NOV-2002 rpotnuru NOCOPY Changes
19 --
20 -- 29-oct-2003 axraghav Modified l_org_id_csr in calc_fbo_Dtl and
21 -- calc_fbo_sum to null out organization_name
22 --
23 --------------------------------------------------------------------------------
24
25 -- Global exception declaration
26
27 -- Generic exception to immediately exit the procedure
28 g_excp_exit_immediate EXCEPTION ;
29
30
31 -- Global constant delcaration
32
33 -- Constants for the all subclass record
34 g_all_ctg_code CONSTANT VARCHAR2(30) := '-1' ;
35
36
37 -- Global cursor declaration
38
39 -- Cursor to retrieve the rowid for the selected record
40 -- If a rowid is retrieved, then the record will be updated,
41 -- else the record will be inserted.
42 CURSOR g_fbo_csr
43 ( p_period_set_name IN VARCHAR2
44 , p_period_name IN VARCHAR2
45 , p_authoring_org_id IN NUMBER
46 , p_scs_code IN VARCHAR2
47 ) IS
48 SELECT rowid
49 FROM oki_forecast_by_orgs fbo
50 WHERE fbo.period_set_name = p_period_set_name
51 AND fbo.period_name = p_period_name
52 AND fbo.authoring_org_id = p_authoring_org_id
53 AND fbo.scs_code = p_scs_code
54 ;
55 rec_g_fbo_csr g_fbo_csr%ROWTYPE ;
56
57 --------------------------------------------------------------------------------
58 -- Procedure to insert records into the oki_forecast_by_orgs table.
59
60 --------------------------------------------------------------------------------
61 PROCEDURE ins_fcst_by_org
62 ( p_period_name IN VARCHAR2
63 , p_period_set_name IN VARCHAR2
64 , p_period_type IN VARCHAR2
65 , p_authoring_org_id IN NUMBER
66 , p_authoring_org_name IN VARCHAR2
67 , p_scs_code IN VARCHAR2
68 , p_base_forecast_amount IN NUMBER
69 , p_base_booked_amount IN NUMBER
70 , x_retcode OUT NOCOPY VARCHAR2
71 ) IS
72
73 -- Local variable declaration
74
75 -- For error handling
76 l_sqlcode VARCHAR2(100) ;
77 l_sqlerrm VARCHAR2(1000) ;
78
79 l_sequence NUMBER := NULL ;
80
81 -- Cursor declaration
82 CURSOR l_seq_num IS
83 SELECT oki_forecast_by_orgs_s1.nextval seq
84 FROM dual
85 ;
86 rec_l_seq_num l_seq_num%ROWTYPE ;
87
88 BEGIN
89
90 OPEN l_seq_num ;
91 FETCH l_seq_num INTO rec_l_seq_num ;
92 -- unable to generate sequence number, exit immediately
93 IF l_seq_num%NOTFOUND THEN
94 RAISE g_excp_exit_immediate ;
95 END IF ;
96 l_sequence := rec_l_seq_num.seq ;
97 CLOSE l_seq_num ;
98
99 -- initialize return code to success
100 x_retcode := '0';
101
102 INSERT INTO oki_forecast_by_orgs
103 ( id
104 , period_name
105 , period_set_name
106 , period_type
107 , authoring_org_id
108 , authoring_org_name
109 , scs_code
110 , base_forecast_amount
111 , base_booked_amount
112 , request_id
113 , program_application_id
114 , program_id
115 , program_update_date )
116 VALUES ( l_sequence
117 , p_period_name
118 , p_period_set_name
119 , p_period_type
120 , p_authoring_org_id
121 , p_authoring_org_name
122 , p_scs_code
123 , p_base_forecast_amount
124 , p_base_booked_amount
125 , oki_load_fbo_pvt.g_request_id
126 , oki_load_fbo_pvt.g_program_application_id
127 , oki_load_fbo_pvt.g_program_id
128 , oki_load_fbo_pvt.g_program_update_date ) ;
129
130 EXCEPTION
131 WHEN OTHERS THEN
132 l_sqlcode := SQLCODE ;
133 l_sqlerrm := SQLERRM ;
134
135 -- Set return code TO error
136 x_retcode := '2';
137
138 fnd_message.set_name( application => 'OKI'
139 , name => 'OKI_TABLE_LOAD_FAILURE');
140
141 fnd_message.set_token( token => 'TABLE_NAME'
142 , value => 'OKI_FORECAST_BY_ORGS');
143
144 fnd_file.put_line( which => fnd_file.log
145 , buff => fnd_message.get);
146
147 fnd_file.put_line( which => fnd_file.log
148 , buff => l_sqlcode||' '||l_sqlerrm );
149 END ins_fcst_by_org ;
150
151 --------------------------------------------------------------------------------
152 -- Procedure to update records in the oki_forecast_by_orgs table.
153
154 --------------------------------------------------------------------------------
155 PROCEDURE upd_fcst_by_org
156 ( p_base_forecast_amount IN NUMBER
157 , p_base_booked_amount IN NUMBER
158 , p_fbo_rowid IN ROWID
159 , x_retcode OUT NOCOPY VARCHAR2
160 ) IS
161
162 -- Local variable declaration
163
164 -- For error handling
165 l_sqlcode VARCHAR2(100) ;
166 l_sqlerrm VARCHAR2(1000) ;
167
168
169 BEGIN
170
171 -- initialize return code to success
172 x_retcode := '0';
173
174 UPDATE oki_forecast_by_orgs SET
175 base_forecast_amount = p_base_forecast_amount
176 , base_booked_amount = p_base_booked_amount
177 , request_id = oki_load_fbo_pvt.g_request_id
178 , program_application_id = oki_load_fbo_pvt.g_program_application_id
179 , program_id = oki_load_fbo_pvt.g_program_id
180 , program_update_date = oki_load_fbo_pvt.g_program_update_date
181 WHERE ROWID = p_fbo_rowid ;
182
183 EXCEPTION
184 WHEN OTHERS THEN
185 l_sqlcode := SQLCODE ;
186 l_sqlerrm := SQLERRM ;
187
188 -- Set return code to error
189 x_retcode := '2';
190
191 fnd_message.set_name( application => 'OKI'
192 , name => 'OKI_UNEXPECTED_FAILURE');
193
194 fnd_message.set_token( token => 'OBJECT_NAME'
195 , value => 'OKI_UPD_FCST_BY_ORG');
196
197 fnd_file.put_line( which => fnd_file.log
198 , buff => fnd_message.get);
199
200 fnd_file.put_line( which => fnd_file.log
201 , buff => l_sqlcode||' '||l_sqlerrm );
202 END upd_fcst_by_org ;
203
204 --------------------------------------------------------------------------------
205 -- Procedure to calculate the forecast and booked amounts for the
206 -- organizations.
207 -- Calculates the amounts by each dimension:
208 -- period set name
209 -- period type
210 -- period name
211 -- subclass
212 -- organization
213 --
214 --------------------------------------------------------------------------------
215 PROCEDURE calc_fbo_dtl1
216 ( p_period_set_name IN VARCHAR2
217 , p_period_type IN VARCHAR2
218 , p_summary_build_date IN DATE
219 , x_retcode OUT NOCOPY VARCHAR2
220 ) IS
221
222 -- Local variable declaration
223
224 -- For capturing the return code, 0 = success, 1 = warning, 2 = error
225 l_retcode VARCHAR2(1) := NULL ;
226
227 -- For error handling
228 l_sqlcode VARCHAR2(100) := NULL ;
229 l_sqlerrm VARCHAR2(1000) := NULL ;
230
231 -- Holds the calculated forecast and booked amounts
232 l_base_forecast_amount NUMBER := 0 ;
233 l_base_booked_amount NUMBER := 0 ;
234
235 -- holds the rowid of the record in the oki_forecast_by_orgs table
236 l_fbo_rowid ROWID ;
237
238 -- Location within the program before the error was encountered.
239 l_loc VARCHAR2(100) ;
240
241 -- Holds the truncated start and end dates from gl_periods
242 l_glpr_start_date DATE ;
243 l_glpr_end_date DATE ;
244
245 -- Cursor declaration
246
247 -- Cursor to get all the organizations and subclasses
248 CURSOR l_org_id_csr IS
249 SELECT DISTINCT shd.authoring_org_id org_id
250 , /*11510 change */ NULL organization_name
251 , shd.scs_code
252 FROM oki_sales_k_hdrs shd
253 ;
254
255 -- Cursor that calculates the forecast amount for a
256 -- particular organization and subclass
257 CURSOR l_org_fcst_csr
258 ( p_glpr_start_date IN DATE
259 , p_glpr_end_date IN DATE
260 , p_authoring_org_id IN NUMBER
261 , p_scs_code IN VARCHAR2
262 ) IS
263 SELECT NVL(SUM(shd.base_forecast_amount), 0) base_forecast_amount
264 FROM oki_sales_k_hdrs shd
265 -- Contract is a renewal contract
266 WHERE shd.is_new_yn IS NULL
267 -- Contract must have undergone forecasting
268 AND shd.close_date IS NOT NULL
269 AND shd.win_percent IS NOT NULL
270 -- get forecast amount for a particular org
271 AND shd.authoring_org_id = p_authoring_org_id
272 -- Expected close date is in the period
273 AND shd.close_date BETWEEN p_glpr_start_date AND p_glpr_end_date
274 AND shd.scs_code = p_scs_code
275 ;
276 rec_l_org_fcst_csr l_org_fcst_csr%ROWTYPE ;
277
278 -- Cursor that calculates the booked amount for a
279 -- particular organization and subclass
280 CURSOR l_org_booked_csr
281 ( p_glpr_start_date IN DATE
282 , p_glpr_end_date IN DATE
283 , p_authoring_org_id IN NUMBER
284 , p_scs_code IN VARCHAR2
285 ) IS
286 SELECT NVL(SUM(shd.base_contract_amount), 0) base_contract_amount
287 FROM oki_sales_k_hdrs shd
288 -- Contract is a renewal contract
289 WHERE shd.is_new_yn IS NULL
290 -- Contract is signed or active
291 AND shd.ste_code IN ('SIGNED', 'ACTIVE')
292 -- get booked amount for a particular org
293 AND shd.authoring_org_id = p_authoring_org_id
294 -- Lesser of the signed DATE or the start date falls within
295 -- the period
296 AND least(NVL(shd.date_signed, shd.start_date), shd.start_date)
297 BETWEEN p_glpr_start_date AND p_glpr_end_date
298 AND shd.scs_code = p_scs_code
299 ;
300 rec_l_org_booked_csr l_org_booked_csr%ROWTYPE ;
301
302 BEGIN
303
304 -- initialize return code to success
305 l_retcode := '0';
306
307 l_loc := 'Looping through valid organizations.' ;
308 << l_org_id_csr_loop >>
309 -- Loop through all the organizations to calcuate the
310 -- appropriate amounts
311 FOR rec_l_org_id_csr IN l_org_id_csr LOOP
312
313 l_loc := 'Looping through valid periods.' ;
314 << g_glpr_csr_loop >>
315 -- Loop through all the periods
316 FOR rec_g_glpr_csr IN oki_utl_pvt.g_glpr_csr(
317 p_period_set_name, p_period_type, p_summary_build_date ) LOOP
318
319 -- Get the truncated gl_periods start and end dates
320 l_glpr_start_date := trunc(rec_g_glpr_csr.start_date );
321 l_glpr_end_date := trunc(rec_g_glpr_csr.end_date );
322
323 -- Re-initialize the amounts before calculating
324 l_base_forecast_amount := 0 ;
325 l_base_booked_amount := 0 ;
326
327 l_loc := 'Opening cursor to determine the forecast amount.' ;
328 -- Calculate the forecast amount for a given organization
329 << l_org_fcst_csr_loop >>
330 OPEN l_org_fcst_csr ( l_glpr_start_date, l_glpr_end_date,
331 rec_l_org_id_csr.org_id, rec_l_org_id_csr.scs_code ) ;
332 FETCH l_org_fcst_csr INTO rec_l_org_fcst_csr ;
333 IF l_org_fcst_csr%FOUND THEN
334 l_base_forecast_amount := rec_l_org_fcst_csr.base_forecast_amount ;
335 END IF ;
336 CLOSE l_org_fcst_csr ;
337
338 l_loc := 'Opening cursor to determine the booked amount.' ;
339 -- Calculate the booked amount for a given organization
340 OPEN l_org_booked_csr ( l_glpr_start_date, l_glpr_end_date,
341 rec_l_org_id_csr.org_id, rec_l_org_id_csr.scs_code ) ;
342 FETCH l_org_booked_csr INTO rec_l_org_booked_csr ;
343 IF l_org_booked_csr%FOUND THEN
344 l_base_booked_amount := rec_l_org_booked_csr.base_contract_amount ;
345 END IF ;
346 CLOSE l_org_booked_csr ;
347
348 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
349 -- Determine if the record is a new one or an existing one
350 OPEN oki_load_fbo_pvt.g_fbo_csr ( rec_g_glpr_csr.period_set_name,
351 rec_g_glpr_csr.period_name, rec_l_org_id_csr.org_id,
352 rec_l_org_id_csr.scs_code ) ;
353 FETCH oki_load_fbo_pvt.g_fbo_csr INTO rec_g_fbo_csr ;
354 IF oki_load_fbo_pvt.g_fbo_csr%NOTFOUND THEN
355 l_loc := 'Insert the new record.' ;
356 -- Insert the current period data for the period
357 oki_load_fbo_pvt.ins_fcst_by_org (
358 p_period_name => rec_g_glpr_csr.period_name
359 , p_period_set_name => rec_g_glpr_csr.period_set_name
360 , p_period_type => rec_g_glpr_csr.period_type
361 , p_authoring_org_id => rec_l_org_id_csr.org_id
362 , p_authoring_org_name => rec_l_org_id_csr.organization_name
363 , p_scs_code => rec_l_org_id_csr.scs_code
364 , p_base_forecast_amount => l_base_forecast_amount
365 , p_base_booked_amount => l_base_booked_amount
366 , x_retcode => l_retcode ) ;
367
368 IF l_retcode = '2' THEN
369 -- Load failed, exit immediately.
370 RAISE oki_load_fbo_pvt.g_excp_exit_immediate ;
371 END IF ;
372 ELSE
373 l_loc := 'Update the existing record.' ;
374 -- Record already exists, so perform an update
378 , p_fbo_rowid => rec_g_fbo_csr.rowid
375 oki_load_fbo_pvt.upd_fcst_by_org (
376 p_base_forecast_amount => l_base_forecast_amount
377 , p_base_booked_amount => l_base_booked_amount
379 , x_retcode => l_retcode ) ;
380
381 IF l_retcode = '2' THEN
382 -- Load failed, exit immediately.
383 RAISE oki_load_fbo_pvt.g_excp_exit_immediate ;
384 END IF ;
385 END IF ;
386 CLOSE oki_load_fbo_pvt.g_fbo_csr ;
387
388 END LOOP g_glpr_csr_loop ;
389 END LOOP l_org_id_csr_loop ;
390
391 EXCEPTION
392 WHEN oki_load_fbo_pvt.g_excp_exit_immediate THEN
393 -- Do not log an error ; It has already been logged.
394 -- Set return code to error
395 x_retcode := '2' ;
396
397
398 WHEN OTHERS THEN
399 l_sqlcode := SQLCODE ;
400 l_sqlerrm := SQLERRM ;
401
402 -- Set return code TO error
403 x_retcode := '2' ;
404
405 fnd_message.set_name( application => 'OKI'
406 , name => 'OKI_UNEXPECTED_FAILURE');
407
408 fnd_message.set_token( token => 'OBJECT_NAME'
409 , value => 'OKI_LOAD_FBO_PVT.CALC_FBO_DTL1');
410
411 fnd_file.put_line( which => fnd_file.log
412 , buff => fnd_message.get);
413
414 -- Log the location within the procedure where the error occurred
415 fnd_message.set_name( application => 'OKI'
416 , name => 'OKI_LOC_IN_PROG_FAILURE');
417
418 fnd_message.set_token( token => 'LOCATION'
419 , value => l_loc);
420
421 fnd_file.put_line( which => fnd_file.log
422 , buff => fnd_message.get);
423
424 fnd_file.put_line( which => fnd_file.log
425 , buff => l_sqlcode||' '||l_sqlerrm );
426 END calc_fbo_dtl1 ;
427
428 --------------------------------------------------------------------------------
429 -- Procedure to calculate the forecast and booked amounts for the
430 -- organizations.
431 -- Calculates the amounts across subclasses
432 -- each period set name
433 -- each period type
434 -- each period name
435 -- each status
436 -- all subclasses
437 -- each organization
438 --
439 --------------------------------------------------------------------------------
440 PROCEDURE calc_fbo_sum
441 ( p_period_set_name IN VARCHAR2
442 , p_period_type IN VARCHAR2
443 , p_summary_build_date IN DATE
444 , x_retcode OUT NOCOPY VARCHAR2
445 ) IS
446
447 -- Local variable declaration
448
449 -- For capturing the return code, 0 = success, 1 = warning, 2 = error
450 l_retcode VARCHAR2(1) := NULL ;
451
452 -- For error handling
453 l_sqlcode VARCHAR2(100) := NULL ;
454 l_sqlerrm VARCHAR2(1000) := NULL ;
455
456 -- Holds the calculated forecast and booked amounts
457 l_base_forecast_amount NUMBER := 0 ;
458 l_base_booked_amount NUMBER := 0 ;
459
460 -- holds the rowid of the record in the oki_forecast_by_orgs table
461 l_fbo_rowid ROWID ;
462
463 -- Location within the program before the error was encountered.
464 l_loc VARCHAR2(100) ;
465
466 -- Holds the truncated start and end dates from gl_periods
467 l_glpr_start_date DATE ;
468 l_glpr_end_date DATE ;
469
470 -- Cusor declaration
471
472 -- Cursor to get all the organizations
473 CURSOR l_org_id_csr IS
474 SELECT DISTINCT shd.authoring_org_id org_id
475 , /*11510 change*/ NULL organization_name
476 FROM oki_sales_k_hdrs shd
477 ;
478
479 -- Cursor that calculates the forecast amount for a
480 -- particular organization
481 CURSOR l_org_fcst_csr
482 ( p_glpr_start_date IN DATE
483 , p_glpr_end_date IN DATE
484 , p_authoring_org_id IN NUMBER
485 ) IS
486 SELECT NVL(SUM(shd.base_forecast_amount), 0) base_forecast_amount
487 FROM oki_sales_k_hdrs shd
488 -- Contract is a renewal contract
489 WHERE shd.is_new_yn IS NULL
490 -- Contract must have undergone forecasting
491 AND shd.close_date IS NOT NULL
492 AND shd.win_percent IS NOT NULL
493 -- get forecast amount for a particular org
494 AND shd.authoring_org_id = p_authoring_org_id
495 -- Expected close date is in the period
496 AND shd.close_date BETWEEN p_glpr_start_date AND p_glpr_end_date
497 ;
498 rec_l_org_fcst_csr l_org_fcst_csr%ROWTYPE ;
499
500 -- Cursor that calculates the booked amount for a
501 -- particular organization
502 CURSOR l_org_booked_csr
503 ( p_glpr_start_date IN DATE
504 , p_glpr_end_date IN DATE
505 , p_authoring_org_id IN NUMBER
506 ) IS
507 SELECT NVL(SUM(shd.base_contract_amount), 0) base_contract_amount
508 FROM oki_sales_k_hdrs shd
509 -- Contract is a renewal contract
510 WHERE shd.is_new_yn IS NULL
511 -- Contract is signed or active
512 AND shd.ste_code IN ('SIGNED', 'ACTIVE')
516 -- the period
513 -- get booked amount for a particular org
514 AND shd.authoring_org_id = p_authoring_org_id
515 -- Lesser of the signed DATE or the start date falls within
517 AND least(NVL(shd.date_signed, shd.start_date), shd.start_date)
518 BETWEEN p_glpr_start_date AND p_glpr_end_date
519 ;
520 rec_l_org_booked_csr l_org_booked_csr%ROWTYPE ;
521
522
523 BEGIN
524
525 -- initialize return code to success
526 l_retcode := '0';
527
528 l_loc := 'Looping through valid organizations.' ;
529 << l_org_id_csr_loop >>
530 -- Loop through all the organizations to calcuate the
531 -- appropriate amounts
532 FOR rec_l_org_id_csr IN l_org_id_csr LOOP
533
534 l_loc := 'Looping through valid periods.' ;
535 << g_glpr_csr_loop >>
536 -- Loop through all the periods
537 FOR rec_g_glpr_csr IN oki_utl_pvt.g_glpr_csr(
538 p_period_set_name, p_period_type, p_summary_build_date ) LOOP
539
540 -- Get the truncated gl_periods start and end dates
541 l_glpr_start_date := trunc(rec_g_glpr_csr.start_date );
542 l_glpr_end_date := trunc(rec_g_glpr_csr.end_date );
543
544 -- Re-initialize the amounts before calculating
545 l_base_forecast_amount := 0 ;
546 l_base_booked_amount := 0 ;
547
548 l_loc := 'Opening cursor to determine the forecast amount.' ;
549 -- Calculate the forecast amount for a given organization
550 OPEN l_org_fcst_csr ( l_glpr_start_date, l_glpr_end_date,
551 rec_l_org_id_csr.org_id ) ;
552 FETCH l_org_fcst_csr INTO rec_l_org_fcst_csr ;
553 IF l_org_fcst_csr%FOUND THEN
554 l_base_forecast_amount := rec_l_org_fcst_csr.base_forecast_amount ;
555 END IF ;
556 CLOSE l_org_fcst_csr ;
557
558 l_loc := 'Opening cursor to determine the booked amount.' ;
559 -- Calculate the booked amount for a given organization
560 OPEN l_org_booked_csr ( l_glpr_start_date, l_glpr_end_date,
561 rec_l_org_id_csr.org_id ) ;
562 FETCH l_org_booked_csr INTO rec_l_org_booked_csr ;
563 IF l_org_booked_csr%FOUND THEN
564 l_base_booked_amount := rec_l_org_booked_csr.base_contract_amount ;
565 END IF ;
566 CLOSE l_org_booked_csr ;
567
568 l_loc := 'Opening cursor to determine if insert or update should occur.' ;
569 -- Determine if the record is a new one or an existing one
570 OPEN oki_load_fbo_pvt.g_fbo_csr ( rec_g_glpr_csr.period_set_name,
571 rec_g_glpr_csr.period_name, rec_l_org_id_csr.org_id,
572 oki_load_fbo_pvt.g_all_ctg_code ) ;
573 FETCH oki_load_fbo_pvt.g_fbo_csr INTO rec_g_fbo_csr ;
574 IF oki_load_fbo_pvt.g_fbo_csr%NOTFOUND THEN
575 l_loc := 'Insert the new record.' ;
576 -- Insert the current period data for the period
577 oki_load_fbo_pvt.ins_fcst_by_org (
578 p_period_name => rec_g_glpr_csr.period_name
579 , p_period_set_name => rec_g_glpr_csr.period_set_name
580 , p_period_type => rec_g_glpr_csr.period_type
581 , p_authoring_org_id => rec_l_org_id_csr.org_id
582 , p_authoring_org_name => rec_l_org_id_csr.organization_name
583 , p_scs_code => oki_load_fbo_pvt.g_all_ctg_code
584 , p_base_forecast_amount => l_base_forecast_amount
585 , p_base_booked_amount => l_base_booked_amount
586 , x_retcode => l_retcode ) ;
587
588 IF l_retcode = '2' THEN
589 -- Load failed, exit immediately.
590 RAISE oki_load_fbo_pvt.g_excp_exit_immediate ;
591 END IF ;
592 ELSE
593 l_loc := 'Update the existing record.' ;
594 -- Record already exists, so perform an update
595 oki_load_fbo_pvt.upd_fcst_by_org (
596 p_base_forecast_amount => l_base_forecast_amount
597 , p_base_booked_amount => l_base_booked_amount
598 , p_fbo_rowid => rec_g_fbo_csr.rowid
599 , x_retcode => l_retcode ) ;
600
601 IF l_retcode = '2' THEN
602 -- Load failed, exit immediately.
603 RAISE oki_load_fbo_pvt.g_excp_exit_immediate ;
604 END IF ;
605 END IF ;
606 CLOSE oki_load_fbo_pvt.g_fbo_csr ;
607
608 END LOOP g_glpr_csr_loop ;
609 END LOOP l_org_id_csr_loop ;
610
611 EXCEPTION
612 WHEN oki_load_fbo_pvt.g_excp_exit_immediate THEN
613 -- Do not log an error ; It has already been logged.
614 -- Set return code to error
615 x_retcode := '2' ;
616
617
618 WHEN OTHERS THEN
619 l_sqlcode := SQLCODE ;
620 l_sqlerrm := SQLERRM ;
621
622 -- Set return code TO error
623 x_retcode := '2' ;
624
625 fnd_message.set_name( application => 'OKI'
626 , name => 'OKI_UNEXPECTED_FAILURE');
627
628 fnd_message.set_token( token => 'OBJECT_NAME'
629 , value => 'OKI_LOAD_FBO_PVT.CALC_FBO_SUM');
630
631 fnd_file.put_line( which => fnd_file.log
635 fnd_message.set_name( application => 'OKI'
632 , buff => fnd_message.get);
633
634 -- Log the location within the procedure where the error occurred
636 , name => 'OKI_LOC_IN_PROG_FAILURE');
637
638 fnd_message.set_token( token => 'LOCATION'
639 , value => l_loc);
640
641 fnd_file.put_line( which => fnd_file.log
642 , buff => fnd_message.get);
643
644 fnd_file.put_line( which => fnd_file.log
645 , buff => l_sqlcode||' '||l_sqlerrm );
646 END calc_fbo_sum ;
647
648 --------------------------------------------------------------------------------
649 -- Procedure to create all the forecast by organization records. If an
650 -- error is encountered in this procedure or subsequent procedures then
651 -- rollback all changes. Once the table is loaded and the data is committed
652 -- the load is considered successful even if update of the oki_refreshs
653 -- table failed.
654 --------------------------------------------------------------------------------
655 PROCEDURE crt_fcst_org
656 ( p_period_set_name IN VARCHAR2
657 , p_period_type IN VARCHAR2
658 , p_summary_build_date IN DATE
659 , x_errbuf OUT NOCOPY VARCHAR2
660 , x_retcode OUT NOCOPY VARCHAR2
661 ) IS
662
663
664 -- Local exception declaration
665
666 -- Exception to immediately exit the procedure
667 l_excp_upd_refresh EXCEPTION ;
668
669
670 -- Constant declaration
671
672 -- Name of the table for which data is being inserted
673 l_table_name CONSTANT VARCHAR2(30) := 'OKI_FORECAST_BY_ORGS' ;
674
675
676 -- Local variable declaration
677
678 -- For capturing the return code, 0 = success, 1 = warning, 2 = error
679 l_retcode VARCHAR2(1) := NULL ;
680
681 -- For error handling
682 l_sqlcode VARCHAR2(100) ;
683 l_sqlerrm VARCHAR2(1000) ;
684
685
686 BEGIN
687
688 SAVEPOINT oki_load_fbo_pvt_crt_fcst_org ;
689
690 -- initialize return code to success
691 l_retcode := '0' ;
692 x_retcode := '0' ;
693
694 -- Procedure to calculate the amounts for each dimension
695 -- and subclass
696 oki_load_fbo_pvt.calc_fbo_dtl1 (
697 p_period_set_name => p_period_set_name
698 , p_period_type => p_period_type
699 , p_summary_build_date => p_summary_build_date
700 , x_retcode => l_retcode ) ;
701
702 IF l_retcode = '2' THEN
703 -- Load failed, exit immediately.
704 RAISE oki_load_fbo_pvt.g_excp_exit_immediate ;
705 END IF ;
706
707 -- Procedure to calculate the amounts across subclasses
708 oki_load_fbo_pvt.calc_fbo_sum (
709 p_period_set_name => p_period_set_name
710 , p_period_type => p_period_type
711 , p_summary_build_date => p_summary_build_date
712 , x_retcode => l_retcode ) ;
713
714 IF l_retcode = '2' THEN
715 -- Load failed, exit immediately.
716 RAISE oki_load_fbo_pvt.g_excp_exit_immediate ;
717 END IF ;
718
719 COMMIT;
720
721 SAVEPOINT oki_load_fbo_pvt_upd_refresh ;
722
723
724 -- Table loaded successfully. Log message IN concurrent manager
725 -- log indicating successful load.
726 fnd_message.set_name( application => 'OKI'
727 , name => 'OKI_TABLE_LOAD_SUCCESS');
728
729 fnd_message.set_token( token => 'TABLE_NAME'
730 , value => l_table_name );
731
732 fnd_file.put_line( which => fnd_file.log
733 , buff => fnd_message.get);
734
735 oki_refresh_pvt.update_oki_refresh( l_table_name, l_retcode ) ;
736
737 IF l_retcode in ('1', '2') THEN
738 -- Update to OKI_REFRESHS failed, exit immediately.
739 RAISE l_excp_upd_refresh ;
740 END IF ;
741
742 COMMIT ;
743
744 EXCEPTION
745 WHEN l_excp_upd_refresh THEN
746 -- Do not log error; It has already been logged by the refreshs
747 -- program
748 x_retcode := l_retcode ;
749
750 ROLLBACK to oki_load_fbo_pvt_upd_refresh ;
751
752 WHEN oki_load_fbo_pvt.g_excp_exit_immediate THEN
753 -- Do not log an error ; It has already been logged.
754 -- Set return code to error
755 x_retcode := '2' ;
756
757 ROLLBACK TO oki_load_fbo_pvt_crt_fcst_org ;
758
759 WHEN OTHERS THEN
760
761 l_sqlcode := SQLCODE ;
762 l_sqlerrm := SQLERRM ;
763
764 -- Set return code TO error
765 x_retcode := '2' ;
766
767 -- ROLLBACK all transactions
768 ROLLBACK TO oki_load_fbo_pvt_crt_fcst_org ;
769
770
771 fnd_message.set_name( application => 'OKI'
772 , name => 'OKI_UNEXPECTED_FAILURE');
773
774 fnd_message.set_token( token => 'OBJECT_NAME'
775 , value => 'OKI_LOAD_FBO_PVT.CRT_FCST_ORG');
776
777 fnd_file.put_line( which => fnd_file.log
778 , buff => fnd_message.get);
779
780 fnd_file.put_line( which => fnd_file.log
781 , buff => l_sqlcode||' '||l_sqlerrm );
782
783 END crt_fcst_org ;
784
785
786 BEGIN
787 -- Initialize the global variables used to log this job run
788 -- FROM concurrent manager
789 g_request_id := fnd_global.conc_request_id ;
790 g_program_application_id := fnd_global.prog_appl_id ;
791 g_program_id := fnd_global.conc_program_id ;
792 g_program_update_date := SYSDATE ;
793
794 END oki_load_fbo_pvt ;
[Home] [Help]