[Home] [Help]
PACKAGE BODY: APPS.PQH_BUDGET_DATA_MIGRATION
Source
1 package body PQH_BUDGET_DATA_MIGRATION as
2 /* $Header: pqbdgmig.pkb 120.2 2006/02/06 14:27:56 nsanghal noship $ */
3 -- ----------------------------------------------------------------------------
4 -- | Private Global Definitions |
5 -- ----------------------------------------------------------------------------
6 --
7 g_package varchar2(33) := ' pqh_budget_data_migration.'; -- Global package name
8 --
9 g_table_route_id_p_bgt number;
10 g_table_route_id_p_bvr number;
11 g_table_route_id_p_bdt number;
12 g_table_route_id_p_bpr number;
13 g_table_route_id_dst number;
14 g_table_route_id_del number;
15 g_table_route_id_dfs number;
16 g_error_exception exception;
17
18
19 --
20 /*--------------------------------------------------------------------------------------------------------------
21
22 Main Procedure
23 --------------------------------------------------------------------------------------------------------------*/
24
25 PROCEDURE extract_data
26 (
27 errbuf OUT NOCOPY VARCHAR2,
28 retcode OUT NOCOPY VARCHAR2,
29 p_budget_name IN per_budgets.name%TYPE DEFAULT NULL,
30 p_budget_set_name IN pqh_dflt_budget_sets.dflt_budget_set_name%TYPE,
31 p_business_group_id IN per_budgets.business_group_id%TYPE
32 )
33 IS
34 -- local variables and cursors
35
36 CURSOR per_budget_cur IS
37 SELECT *
38 FROM per_budgets
39 WHERE name = NVL(p_budget_name, name)
40 AND business_group_id = p_business_group_id
41 AND NVL(budget_type_code,'X') <> 'OTA_BUDGET' ;
42
43 CURSOR per_budget_ver_cur (p_budget_id IN per_budgets.budget_id%TYPE) IS
44 SELECT *
45 FROM per_budget_versions
46 WHERE budget_id = p_budget_id;
47
48 CURSOR per_budget_elmnt_cur (p_budget_version_id IN per_budget_versions.budget_version_id%TYPE) IS
49 SELECT *
50 FROM per_budget_elements
51 WHERE budget_version_id = p_budget_version_id;
52
53 CURSOR per_budget_val_cur (p_budget_element_id IN per_budget_elements.budget_element_id%TYPE) IS
54 SELECT *
55 FROM per_budget_values
56 WHERE budget_element_id = p_budget_element_id;
57
58
59 l_proc varchar2(72) := g_package||'extract_data';
60 l_per_budget_rec per_budgets%ROWTYPE;
61 l_budget_id pqh_budgets.budget_id%TYPE;
62 l_tot_budget_val per_budget_values.value%TYPE;
63 l_per_budget_ver_rec per_budget_versions%ROWTYPE;
64 l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
65 l_per_budget_elmnt_rec per_budget_elements%ROWTYPE;
66 l_budget_detail_id pqh_budget_details.budget_detail_id%TYPE;
67 l_budget_unit1_value pqh_budget_details.budget_unit1_value%TYPE;
68 l_per_budget_val_rec per_budget_values%ROWTYPE;
69 l_budget_period_id pqh_budget_periods.budget_period_id%TYPE;
70 l_log_context pqh_process_log.log_context%TYPE;
71 l_valid varchar2(10);
72
73
74 BEGIN
75
76 hr_utility.set_location('Entering: '||l_proc, 5);
77
78 -- check input params
79 check_params
80 (
81 p_budget_name => p_budget_name,
82 p_budget_set_name => p_budget_set_name,
83 p_business_group_id => p_business_group_id
84 );
85
86 -- populate the globals
87 populate_globals;
88
89 -- populate the per_shared_types table with user defined codes
90 populate_per_shared_types;
91
92
93 -- open the per_budget_cursor
94 OPEN per_budget_cur;
95 LOOP -- loop 1
96 FETCH per_budget_cur INTO l_per_budget_rec;
97 EXIT WHEN per_budget_cur%NOTFOUND;
98
99 -- Start the Log Process
100 pqh_process_batch_log.start_log
101 (
102 p_batch_id => l_per_budget_rec.budget_id,
103 p_module_cd => 'MIGRATE_BUDGETS',
104 p_log_context => l_per_budget_rec.name
105 );
106
107 -- get log_context
108 set_p_bgt_log_context
109 (
110 p_budget_id => l_per_budget_rec.budget_id,
111 p_log_context => l_log_context
112 );
113
114 -- set the context
115 pqh_process_batch_log.set_context_level
116 (
117 p_txn_id => l_per_budget_rec.budget_id,
118 p_txn_table_route_id => g_table_route_id_p_bgt,
119 p_level => 1,
120 p_log_context => l_log_context
121 );
122
123 -- check if budget is valid
124 check_valid_budget
125 (
126 p_per_budgets_rec => l_per_budget_rec,
127 p_valid => l_valid
128 );
129
130 -- create records in pqh_budgets
131 populate_budgets
132 (
133 p_per_budgets_rec => l_per_budget_rec,
134 p_valid => l_valid,
135 p_budget_id_o => l_budget_id,
136 p_tot_budget_val_o => l_tot_budget_val
137 );
138
139 -- open the per_budget_ver_cursor
140 OPEN per_budget_ver_cur (p_budget_id => l_per_budget_rec.budget_id );
141 LOOP -- loop 2
142 FETCH per_budget_ver_cur INTO l_per_budget_ver_rec;
143 EXIT WHEN per_budget_ver_cur%NOTFOUND;
144
145 -- get log_context
146 set_p_bvr_log_context
147 (
148 p_budget_version_id => l_per_budget_ver_rec.budget_version_id,
149 p_log_context => l_log_context
150 );
151
152 -- set the context
153 pqh_process_batch_log.set_context_level
154 (
155 p_txn_id => l_per_budget_ver_rec.budget_version_id,
156 p_txn_table_route_id => g_table_route_id_p_bvr,
157 p_level => 2,
158 p_log_context => l_log_context
159 );
160
161 -- create records in pqh_budget_versions
162 populate_budget_versions
163 (
164 p_per_budget_ver_rec => l_per_budget_ver_rec,
165 p_budget_id => l_budget_id,
166 p_budget_version_id_o => l_budget_version_id
167 );
168
169
170 -- open the per_budget_elmnt_cursor
171 OPEN per_budget_elmnt_cur (p_budget_version_id => l_per_budget_ver_rec.budget_version_id);
172 LOOP -- loop 3
173 FETCH per_budget_elmnt_cur INTO l_per_budget_elmnt_rec;
174 EXIT WHEN per_budget_elmnt_cur%NOTFOUND;
175
176 -- get log_context
177 set_p_bdt_log_context
178 (
179 p_budget_element_id => l_per_budget_elmnt_rec.budget_element_id,
180 p_log_context => l_log_context
181 );
182
183 -- set the context
184 pqh_process_batch_log.set_context_level
185 (
186 p_txn_id => l_per_budget_elmnt_rec.budget_element_id,
187 p_txn_table_route_id => g_table_route_id_p_bdt,
188 p_level => 3,
189 p_log_context => l_log_context
190 );
191
192
193 -- create records in pqh_budget_details
194 populate_budget_details
195 (
196 p_per_budget_elmnt_rec => l_per_budget_elmnt_rec,
197 p_budget_version_id => l_budget_version_id,
198 p_tot_budget_val => l_tot_budget_val,
199 p_budget_detail_id_o => l_budget_detail_id,
200 p_budget_unit1_value_o => l_budget_unit1_value
201 );
202
203 -- open the per_budget_val_cursor
204 OPEN per_budget_val_cur (p_budget_element_id => l_per_budget_elmnt_rec.budget_element_id);
205 LOOP -- loop 4
206 FETCH per_budget_val_cur INTO l_per_budget_val_rec;
207 EXIT WHEN per_budget_val_cur%NOTFOUND;
208
209 -- get log_context
210 set_p_bpr_log_context
211 (
212 p_budget_value_id => l_per_budget_val_rec.budget_value_id,
213 p_log_context => l_log_context
214 );
215
216 -- set the context
217 pqh_process_batch_log.set_context_level
218 (
219 p_txn_id => l_per_budget_val_rec.budget_value_id,
220 p_txn_table_route_id => g_table_route_id_p_bpr,
221 p_level => 4,
222 p_log_context => l_log_context
223 );
224
225
226 -- create records in pqh_budget_periods
227 populate_budget_periods
228 (
229 p_per_budget_val_rec => l_per_budget_val_rec,
230 p_budget_detail_id => l_budget_detail_id,
231 p_budget_unit1_value => l_budget_unit1_value,
232 p_budget_period_id_o => l_budget_period_id
233 );
234
235
236 -- create records into budget sets, elmnts and fund srcs
237 -- new rqmt as on 03/20/2000
238 populate_period_details
239 (
240 p_budget_period_id => l_budget_period_id,
241 p_budget_set_name => p_budget_set_name
242 );
243
244
245 END LOOP; -- loop 4
246 CLOSE per_budget_val_cur;
247
248
249 END LOOP; -- loop 3
250 CLOSE per_budget_elmnt_cur;
251
252
253 END LOOP; -- loop 2
254 CLOSE per_budget_ver_cur;
255
256
257
258 -- end the log for thr current budget id
259 pqh_process_batch_log.end_log;
260
261 END LOOP; -- loop 1
262 CLOSE per_budget_cur;
263
264
265 -- populate pqh_budget_versions with default row for those budgets that do not have child rows
266 -- in pqh_budget_versions
267
268 populate_empty_budget_versions;
269
270 -- commit the work;
271 commit;
272 hr_utility.set_location('Leaving:'||l_proc, 1000);
273
274 EXCEPTION
275 WHEN g_error_exception THEN
276 -- call the end log and stop
277 pqh_process_batch_log.end_log;
278 WHEN others THEN
279 raise;
280 END extract_data;
281
282
283 --------------------------------------------------------------------------------------------------------------
284
285 PROCEDURE populate_budgets
286 (
287 p_per_budgets_rec IN per_budgets%ROWTYPE,
288 p_valid IN varchar2,
289 p_budget_id_o OUT NOCOPY pqh_budgets.budget_id%TYPE,
290 p_tot_budget_val_o OUT NOCOPY per_budget_values.value%TYPE
291 )
292 IS
293 -- local variables and cursors
294
295 l_proc varchar2(72) := g_package||'populate_budgets';
296 l_budgeted_entity_cd pqh_budgets.budgeted_entity_cd%TYPE := 'OPEN';
297 l_budget_style_cd pqh_budgets.budget_style_cd%TYPE := 'BOTTOM';
298 l_budget_start_date pqh_budgets.budget_start_date%TYPE;
299 l_budget_end_date pqh_budgets.budget_end_date%TYPE;
300 l_object_version_number pqh_budgets.object_version_number%TYPE;
301 l_tot_budget_val per_budget_values.value%TYPE;
302 l_shared_type_id pqh_budgets.budget_unit1_id%TYPE;
303 l_status pqh_budgets.status%TYPE;
304 l_budget_unit1_aggregate pqh_budgets.budget_unit1_aggregate%TYPE := 'ACCUMULATE';
305
306
307 -- cursor to compute budget start and end dates
308
309 CURSOR budget_date_cur IS
310 SELECT MIN(start_date), MAX(end_date)
311 FROM per_time_periods
312 WHERE time_period_id IN
313 (
314 SELECT val.time_period_id
315 FROM per_budget_values val,
316 per_budget_elements ele,
317 per_budget_versions ver
318 WHERE val.budget_element_id = ele.budget_element_id
319 AND ele.budget_version_id = ver.budget_version_id
320 AND ver.budget_id = p_per_budgets_rec.budget_id
321 );
322
323
324 -- cursor to compute budget start and end dates for budget with no child records
325 -- in per_budget_values
326
327 CURSOR budget_cal_cur IS
328 SELECT start_date, start_date
329 FROM pay_calendars
330 WHERE period_set_name = p_per_budgets_rec.period_set_name;
331
332 -- cursor for unit1_value for ALL versions of the budget
333 -- this is used to compute the percentage at detail level
334
335 CURSOR tot_unit1_val_cur IS
336 SELECT SUM(value)
337 FROM per_budget_values
338 WHERE budget_element_id IN
339 ( SELECT ele.budget_element_id
340 FROM per_budget_elements ele,
341 per_budget_versions ver
342 WHERE ele.budget_version_id = ver.budget_version_id
343 AND ver.budget_id = p_per_budgets_rec.budget_id
344 );
345
346
347 BEGIN
348
349 hr_utility.set_location('Entering: '||l_proc, 5);
350
351 IF p_valid = 'Y' THEN
352
353 -- compute the budget start and end date
354 /*
355 Start date is the minimum date in per_budget_values for the current budget_id
356 End date is the maximum date in per_budget_values for the current budget_id
357 If the budget has no records in per_budget_values then we get the start and end date
358 for the calender
359 */
360
361 OPEN budget_date_cur;
362 FETCH budget_date_cur INTO l_budget_start_date, l_budget_end_date;
363 CLOSE budget_date_cur;
364
365 IF (l_budget_start_date IS NULL ) OR ( l_budget_end_date IS NULL ) THEN
366 OPEN budget_cal_cur;
367 FETCH budget_cal_cur INTO l_budget_start_date, l_budget_end_date;
368 CLOSE budget_cal_cur;
369 END IF;
370
371 hr_utility.set_location('Per Budget Id: '||p_per_budgets_rec.budget_id, 10);
372 hr_utility.set_location('Start Date: '||l_budget_start_date, 15);
373 hr_utility.set_location('End Date: '||l_budget_end_date, 20);
374
375 -- get the shared_type_id for UOM
376
377 l_shared_type_id := get_shared_type_id (p_unit => p_per_budgets_rec.unit );
378
379 -- compute budget status, if unit is null then status is null else status is FROZEN
380
381 IF p_per_budgets_rec.unit IS NULL THEN
382 l_status := '';
383 ELSE
384 l_status := 'FROZEN';
385 END IF;
386
387 hr_utility.set_location('Shared Id : '||l_shared_type_id, 25);
388
389 -- compute total budget value
390 OPEN tot_unit1_val_cur;
391 FETCH tot_unit1_val_cur INTO l_tot_budget_val;
392 p_tot_budget_val_o := l_tot_budget_val;
393 CLOSE tot_unit1_val_cur;
394
395 hr_utility.set_location('l_budget_unit1_aggregate : '||l_budget_unit1_aggregate,26);
396
397 -- call insert API
398 pqh_budgets_api.create_budget
399 (
400 p_validate => false
401 ,p_budget_id => p_budget_id_o
402 ,p_business_group_id => p_per_budgets_rec.business_group_id
403 ,p_start_organization_id => null
404 ,p_org_structure_version_id => null
405 ,p_budgeted_entity_cd => l_budgeted_entity_cd
406 ,p_budget_style_cd => l_budget_style_cd
407 ,p_budget_name => p_per_budgets_rec.name
408 ,p_period_set_name => p_per_budgets_rec.period_set_name
409 ,p_budget_start_date => l_budget_start_date
410 ,p_budget_end_date => l_budget_end_date
411 ,p_budget_unit1_id => l_shared_type_id
412 ,p_budget_unit2_id => null
413 ,p_budget_unit3_id => null
414 ,p_transfer_to_gl_flag => null
415 ,p_status => l_status
416 ,p_object_version_number => l_object_version_number
417 ,p_effective_date => sysdate
418 ,p_gl_set_of_books_id => null
419 ,p_budget_unit1_aggregate => l_budget_unit1_aggregate
420 ,p_budget_unit2_aggregate => null
421 ,p_budget_unit3_aggregate => null
422 );
423
424
425 END IF; -- p_valid = Y
426
427 hr_utility.set_location('PQH Budget ID OUT NOCOPY : '||p_budget_id_o, 100);
428
429 hr_utility.set_location('Leaving:'||l_proc, 1000);
430
431 EXCEPTION
432 WHEN others THEN
433
434 p_budget_id_o := null;
435 p_tot_budget_val_o := null;
436 -- insert error into log table
437 pqh_process_batch_log.insert_log
438 (
439 p_message_type_cd => 'ERROR',
440 p_message_text => SQLERRM
441 );
442 END populate_budgets;
443
444
445 --------------------------------------------------------------------------------------------------------------
446
447 PROCEDURE populate_budget_versions
448 (
449 p_per_budget_ver_rec IN per_budget_versions%ROWTYPE,
450 p_budget_id IN pqh_budgets.budget_id%TYPE,
451 p_budget_version_id_o OUT NOCOPY pqh_budget_versions.budget_version_id%TYPE
452 )
453 IS
454 -- local variables and cursors
455
456 l_proc varchar2(72) := g_package||'populate_budget_versions';
457 l_object_version_number pqh_budget_versions.object_version_number%TYPE;
458 l_budget_unit1_value pqh_budget_versions.budget_unit1_value%TYPE;
459 l_budget_unit1_available pqh_budget_versions.budget_unit1_available%TYPE := 0;
460
461
462 -- cursor for unit1_value
463 CURSOR unit1_val_cur IS
464 SELECT SUM(val.value)
465 FROM per_budget_values val
466 WHERE budget_element_id IN
467 ( SELECT DISTINCT budget_element_id
468 FROM per_budget_elements
469 WHERE budget_version_id = p_per_budget_ver_rec.budget_version_id);
470
471 BEGIN
472
473 hr_utility.set_location('Entering: '||l_proc, 5);
474
475 IF p_budget_id IS NOT NULL THEN
476
477 -- compute unit1
478 OPEN unit1_val_cur;
479 FETCH unit1_val_cur INTO l_budget_unit1_value;
480 CLOSE unit1_val_cur;
481
482 -- call insert API
483 pqh_budget_versions_api.create_budget_version
484 (
485 p_validate => false
486 ,p_budget_version_id => p_budget_version_id_o
487 ,p_budget_id => p_budget_id
488 ,p_version_number => p_per_budget_ver_rec.version_number
489 ,p_date_from => p_per_budget_ver_rec.date_from
490 ,p_date_to => p_per_budget_ver_rec.date_to
491 ,p_transfered_to_gl_flag => null
492 ,p_xfer_to_other_apps_cd => null
493 ,p_object_version_number => l_object_version_number
494 ,p_budget_unit1_value => l_budget_unit1_value
495 ,p_budget_unit1_available => l_budget_unit1_available
496 ,p_effective_date => sysdate
497 );
498
499
500 END IF; -- p_budget_id not null
501
502 hr_utility.set_location('PQH Budget Version out '||p_budget_version_id_o, 15);
503 hr_utility.set_location('Leaving:'||l_proc, 1000);
504
505 EXCEPTION
506 WHEN others THEN
507 p_budget_version_id_o := null;
508 -- insert error into log table
509 pqh_process_batch_log.insert_log
510 (
511 p_message_type_cd => 'ERROR',
512 p_message_text => SQLERRM
513 );
514 END populate_budget_versions;
515
516 --------------------------------------------------------------------------------------------------------------
517
518 PROCEDURE populate_budget_details
519 (
520 p_per_budget_elmnt_rec IN per_budget_elements%ROWTYPE,
521 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE,
522 p_tot_budget_val IN per_budget_values.value%TYPE,
523 p_budget_detail_id_o OUT NOCOPY pqh_budget_details.budget_detail_id%TYPE,
524 p_budget_unit1_value_o OUT NOCOPY pqh_budget_details.budget_unit1_value%TYPE
525 )
526 IS
527 -- local variables and cursors
528
529 l_proc varchar2(72) := g_package||'populate_budget_details';
530 l_object_version_number pqh_budget_details.object_version_number%TYPE;
531 l_budget_unit1_value_type_cd pqh_budget_details.budget_unit1_value_type_cd%TYPE := 'V';
532 l_budget_unit1_value pqh_budget_details.budget_unit1_value%TYPE;
533 l_budget_unit1_percent pqh_budget_details.budget_unit1_percent%TYPE;
534 l_budget_unit1_available pqh_budget_details.budget_unit1_available%TYPE := 0;
535
536 -- cursor for unit1_value
537 CURSOR unit1_val_cur IS
538 SELECT SUM(val.value)
539 FROM per_budget_values val
540 WHERE budget_element_id = p_per_budget_elmnt_rec.budget_element_id;
541
542
543 BEGIN
544
545 hr_utility.set_location('Entering: '||l_proc, 5);
546
547 IF p_budget_version_id IS NOT NULL THEN
548
549 -- compute l_budget_unit1_value
550 OPEN unit1_val_cur;
551 FETCH unit1_val_cur INTO l_budget_unit1_value;
552 p_budget_unit1_value_o := l_budget_unit1_value;
553 CLOSE unit1_val_cur;
554
555 -- compute l_budget_unit1_percent
556 l_budget_unit1_percent := (l_budget_unit1_value/p_tot_budget_val) * 100 ;
557
558 -- call insert API
559 pqh_budget_details_api.create_budget_detail
560 (
561 p_validate => false
562 ,p_budget_detail_id => p_budget_detail_id_o
563 ,p_organization_id => p_per_budget_elmnt_rec.organization_id
564 ,p_job_id => p_per_budget_elmnt_rec.job_id
565 ,p_position_id => p_per_budget_elmnt_rec.position_id
566 ,p_grade_id => p_per_budget_elmnt_rec.grade_id
567 ,p_budget_version_id => p_budget_version_id
568 ,p_budget_unit1_percent => l_budget_unit1_percent
569 ,p_budget_unit1_value_type_cd => l_budget_unit1_value_type_cd
570 ,p_budget_unit1_value => l_budget_unit1_value
571 ,p_budget_unit1_available => l_budget_unit1_available
572 ,p_budget_unit2_percent => null
573 ,p_budget_unit2_value_type_cd => null
574 ,p_budget_unit2_value => null
575 ,p_budget_unit2_available => null
576 ,p_budget_unit3_percent => null
577 ,p_budget_unit3_value_type_cd => null
578 ,p_budget_unit3_value => null
579 ,p_budget_unit3_available => null
580 ,p_object_version_number => l_object_version_number
581 );
582
583 END IF; -- p_budget_version_id is not null
584
585 hr_utility.set_location('PQH Budget Detail ID out '||p_budget_detail_id_o, 100);
586 hr_utility.set_location('Leaving:'||l_proc, 1000);
587
588 EXCEPTION
589 WHEN others THEN
590 p_budget_detail_id_o := null;
591 p_budget_unit1_value_o := null;
592 -- insert error into log table
593 pqh_process_batch_log.insert_log
594 (
595 p_message_type_cd => 'ERROR',
596 p_message_text => SQLERRM
597 );
598 END populate_budget_details;
599
600 --------------------------------------------------------------------------------------------------------------
601
602 PROCEDURE populate_budget_periods
603 (
604 p_per_budget_val_rec IN per_budget_values%ROWTYPE,
605 p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE,
606 p_budget_unit1_value IN pqh_budget_details.budget_unit1_value%TYPE,
607 p_budget_period_id_o OUT NOCOPY pqh_budget_periods.budget_period_id%TYPE
608 )
609 IS
610 -- local variables and cursors
611
612 l_proc varchar2(72) := g_package||'populate_budget_periods';
613 l_object_version_number pqh_budget_periods.object_version_number%TYPE;
614 l_budget_unit1_value_type_cd pqh_budget_periods.budget_unit1_value_type_cd%TYPE := 'V';
615 l_budget_unit1_percent pqh_budget_periods.budget_unit1_percent%TYPE;
616 l_budget_unit1_available pqh_budget_details.budget_unit1_available%TYPE := 0;
617 /*
618 changed as per Sumit's reqt that p_budget_unit1_available equals value
619 02/16/2000
620 changed on 3/20/1999 as now we also have records in budget sets for the budget_period
621 so in budget_periods we will have available = 0 as entire value passed to budget_sets
622 */
623
624
625 BEGIN
626
627 hr_utility.set_location('Entering: '||l_proc, 5);
628
629 IF p_budget_detail_id IS NOT NULL THEN
630
631 -- compute l_budget_unit1_percent
632 l_budget_unit1_percent := (p_per_budget_val_rec.value/p_budget_unit1_value)*100 ;
633
634 -- call insert API
635 pqh_budget_periods_api.create_budget_period
636 (
637 p_validate => false
638 ,p_budget_period_id => p_budget_period_id_o
639 ,p_budget_detail_id => p_budget_detail_id
640 ,p_start_time_period_id => p_per_budget_val_rec.time_period_id
641 ,p_end_time_period_id => p_per_budget_val_rec.time_period_id
642 ,p_budget_unit1_percent => l_budget_unit1_percent
643 ,p_budget_unit2_percent => null
644 ,p_budget_unit3_percent => null
645 ,p_budget_unit1_value => p_per_budget_val_rec.value
646 ,p_budget_unit2_value => null
647 ,p_budget_unit3_value => null
648 ,p_budget_unit1_value_type_cd => l_budget_unit1_value_type_cd
649 ,p_budget_unit2_value_type_cd => null
650 ,p_budget_unit3_value_type_cd => null
651 ,p_budget_unit1_available => l_budget_unit1_available
652 ,p_budget_unit2_available => null
653 ,p_budget_unit3_available => null
654 ,p_object_version_number => l_object_version_number
655 );
656
657
658 END IF; -- p_budget_detail_id is not null
659
660 hr_utility.set_location('Leaving:'||l_proc, 1000);
661
662 EXCEPTION
663 WHEN others THEN
664 p_budget_period_id_o := null;
665 -- insert error into log table
666 pqh_process_batch_log.insert_log
667 (
668 p_message_type_cd => 'ERROR',
669 p_message_text => SQLERRM
670 );
671 END populate_budget_periods;
672
673 --------------------------------------------------------------------------------------------------------------
674 FUNCTION get_shared_type_id (p_unit IN per_budgets.unit%TYPE ) RETURN number
675 IS
676
677 CURSOR uom_csr IS
678 SELECT pst.shared_type_id
679 FROM per_shared_types_vl pst , hr_standard_lookups lk
680 WHERE lk.lookup_type = pst.lookup_type
681 and lk.lookup_code = pst.system_type_cd
682 and lk.meaning = pst.shared_type_name
683 and lk.lookup_code = p_unit
684 and lk.lookup_type = 'BUDGET_MEASUREMENT_TYPE';
685
686 -- local variables and cursors
687
688 l_proc varchar2(72) := g_package||'get_shared_type_id';
689 l_shared_type_id number;
690
691 BEGIN
692
693 hr_utility.set_location('Entering: '||l_proc, 5);
694
695 hr_utility.set_location('Unit : '||p_unit,10);
696
697 IF p_unit IS NULL THEN
698
699 hr_utility.set_location('Unit is NULL ',10);
700 hr_utility.set_location('Leaving:'||l_proc, 1000);
701
702 RETURN NULL;
703
704 END IF;
705
706 OPEN uom_csr;
707 FETCH uom_csr INTO l_shared_type_id;
708 CLOSE uom_csr;
709
710 hr_utility.set_location('Shared Type ID :'||l_shared_type_id, 100);
711 hr_utility.set_location('Leaving:'||l_proc, 1000);
712
713 RETURN l_shared_type_id;
714
715 EXCEPTION
716 WHEN others THEN
717 raise;
718 END;
719 --------------------------------------------------------------------------------------------------------------
720 PROCEDURE populate_per_shared_types
721 IS
722 /*
723 This procedure will populate per_shared_types with lookup_code that the user may have
724 defined for lookup_type = BUDGET_MEASUREMENT_TYPE
725 */
726 -- local variables and cursors
727
728 l_proc varchar2(72) := g_package||'populate_per_shared_types';
729 l_object_version_number per_shared_types.object_version_number%TYPE;
730 l_shared_type_id per_shared_types.shared_type_id%TYPE;
731 l_lookup_code per_shared_types.system_type_cd%TYPE;
732 l_meaning per_shared_types.shared_type_name%TYPE;
733
734
735 CURSOR pop_uom_csr is
736 SELECT lookup_code, meaning
737 FROM hr_lookups
738 WHERE lookup_type = 'BUDGET_MEASUREMENT_TYPE'
739 AND enabled_flag = 'Y'
740 AND sysdate BETWEEN NVL(start_date_active,sysdate) AND NVL(end_date_active,sysdate)
741 AND lookup_code NOT IN ( SELECT system_type_cd
742 FROM per_shared_types
743 WHERE lookup_type = 'BUDGET_MEASUREMENT_TYPE' );
744
745 BEGIN
746
747 hr_utility.set_location('Entering: '||l_proc, 5);
748
749 OPEN pop_uom_csr;
750 LOOP
751
752 FETCH pop_uom_csr into l_lookup_code, l_meaning;
753 EXIT WHEN pop_uom_csr%NOTFOUND;
754
755 per_shared_types_api.create_shared_type
756 (
757 p_shared_type_id => l_shared_type_id
758 ,p_shared_type_name => l_meaning
759 ,p_system_type_cd => l_lookup_code
760 ,p_language_code => userenv('LANG')
761 ,p_object_version_number => l_object_version_number
762 ,p_lookup_type => 'BUDGET_MEASUREMENT_TYPE'
763 ,p_effective_date => sysdate
764 );
765
766 hr_utility.set_location('Ins Per Shared Types '||l_lookup_code,10);
767
768 END LOOP;
769
770 CLOSE pop_uom_csr;
771
772 hr_utility.set_location('Leaving:'||l_proc, 1000);
773
774
775 EXCEPTION
776 WHEN others THEN
777 raise;
778 END;
779
780
781 --------------------------------------------------------------------------------------------------------------
782 PROCEDURE populate_empty_budget_versions
783 IS
784 /*
785 This procedure will populate one row in pqh_budget_versions table for those budgets that have on row
786 here. This is a new rqmt that thete cannot be a row in pqh_budgets table without any child rows in
787 pqh_budget_versions
788 */
789 -- local variables and cursors
790
791 l_proc varchar2(72) := g_package||'populate_empty_budget_versions';
792 l_budget_rec pqh_budgets%ROWTYPE;
793 l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
794 l_object_version_number pqh_budget_versions.object_version_number%TYPE;
795
796 CURSOR budgets_csr is
797 SELECT * FROM pqh_budgets
798 WHERE budget_id NOT IN ( SELECT DISTINCT budget_id
799 FROM pqh_budget_versions );
800
801 BEGIN
802
803 hr_utility.set_location('Entering: '||l_proc, 5);
804
805 OPEN budgets_csr;
806 LOOP
807
808 FETCH budgets_csr INTO l_budget_rec;
809 EXIT WHEN budgets_csr%NOTFOUND;
810
811 -- call insert API
812 pqh_budget_versions_api.create_budget_version
813 (
814 p_validate => false
815 ,p_budget_version_id => l_budget_version_id
816 ,p_budget_id => l_budget_rec.budget_id
817 ,p_version_number => 1
818 ,p_date_from => l_budget_rec.budget_start_date
819 ,p_date_to => l_budget_rec.budget_end_date
820 ,p_transfered_to_gl_flag => 'N'
821 ,p_xfer_to_other_apps_cd => 'N'
822 ,p_object_version_number => l_object_version_number
823 ,p_effective_date => sysdate
824 );
825
826 END LOOP;
827
828 CLOSE budgets_csr;
829
830 hr_utility.set_location('Leaving:'||l_proc, 1000);
831
832
833 EXCEPTION
834 WHEN others THEN
835 raise;
836 END;
837
838
839
840 --------------------------------------------------------------------------------------------------------------
841
842 PROCEDURE populate_period_details
843 (
844 p_budget_period_id IN pqh_budget_periods.budget_period_id%TYPE,
845 p_budget_set_name IN pqh_dflt_budget_sets.dflt_budget_set_name%TYPE
846 )
847 IS
848 /*
849 This procedure will populate one rows in pqh_budget_sets, elements and funding srcs with rows from
850 pqh_dflt_budget_sets, elements and fund srcs
851 */
852 -- local variables and cursors
853
854 l_proc varchar2(72) := g_package||'populate_period_details';
855 l_budget_periods_rec pqh_budget_periods%ROWTYPE;
856 l_dflt_budget_sets_rec pqh_dflt_budget_sets%ROWTYPE;
857 l_dflt_budget_elements_rec pqh_dflt_budget_elements%ROWTYPE;
858 l_dflt_fund_srcs pqh_dflt_fund_srcs%ROWTYPE;
859 l_budget_set_id pqh_budget_sets.budget_set_id%TYPE;
860 l_budget_element_id pqh_budget_elements.budget_element_id%TYPE;
861 l_budget_fund_src_id pqh_budget_fund_srcs.budget_fund_src_id%TYPE;
862
863
864 CURSOR budget_periods_csr IS
865 SELECT *
866 FROM pqh_budget_periods
867 WHERE budget_period_id = p_budget_period_id;
868
869 CURSOR pqh_dflt_budget_sets_cur (p_budget_set_name IN pqh_dflt_budget_sets.dflt_budget_set_name%TYPE) IS
870 SELECT *
871 FROM pqh_dflt_budget_sets
872 WHERE dflt_budget_set_name = p_budget_set_name;
873
874 CURSOR pqh_dflt_budget_elements_cur (p_dflt_budget_set_id IN pqh_dflt_budget_elements.dflt_budget_set_id%TYPE) IS
875 SELECT *
876 FROM pqh_dflt_budget_elements
877 WHERE dflt_budget_set_id = p_dflt_budget_set_id;
878
879 CURSOR pqh_dflt_fund_srcs_cur (p_dflt_budget_element_id IN pqh_dflt_fund_srcs.dflt_budget_element_id%TYPE) IS
880 SELECT *
881 FROM pqh_dflt_fund_srcs
882 WHERE dflt_budget_element_id = p_dflt_budget_element_id;
883
884
885 BEGIN
886
887 hr_utility.set_location('Entering: '||l_proc, 5);
888
889 IF p_budget_period_id IS NOT NULL THEN
890
891 OPEN budget_periods_csr;
892 FETCH budget_periods_csr INTO l_budget_periods_rec;
893 CLOSE budget_periods_csr;
894
895
896 OPEN pqh_dflt_budget_sets_cur(p_budget_set_name => p_budget_set_name);
897 LOOP -- loop 1
898 FETCH pqh_dflt_budget_sets_cur INTO l_dflt_budget_sets_rec;
899 EXIT WHEN pqh_dflt_budget_sets_cur%NOTFOUND;
900 -- create records in pqh_budget_sets
901 populate_budget_sets
902 (
903 p_dflt_budget_sets_rec => l_dflt_budget_sets_rec,
904 p_budget_period_id => p_budget_period_id,
905 p_budget_set_id_o => l_budget_set_id
906 );
907
908
909 -- open budget elements cursor
910 OPEN pqh_dflt_budget_elements_cur(p_dflt_budget_set_id => l_dflt_budget_sets_rec.dflt_budget_set_id);
911 LOOP -- loop 2
912 FETCH pqh_dflt_budget_elements_cur INTO l_dflt_budget_elements_rec;
913 EXIT WHEN pqh_dflt_budget_elements_cur%NOTFOUND;
914 -- create records in pqh_budget_elements
915 populate_budget_elements
916 (
917 p_dflt_budget_elements_rec => l_dflt_budget_elements_rec,
918 p_budget_set_id => l_budget_set_id,
919 p_budget_element_id_o => l_budget_element_id
920 );
921
922
923 -- open budget fund srcs cursor
924 OPEN pqh_dflt_fund_srcs_cur(p_dflt_budget_element_id => l_dflt_budget_elements_rec.dflt_budget_element_id);
925 LOOP -- loop 3
926 FETCH pqh_dflt_fund_srcs_cur INTO l_dflt_fund_srcs;
927 EXIT WHEN pqh_dflt_fund_srcs_cur%NOTFOUND;
928 -- create records in pqh_budget_fund_srcs
929 populate_budget_fund_srcs
930 (
931 p_dflt_fund_srcs => l_dflt_fund_srcs,
932 p_budget_element_id => l_budget_element_id,
933 p_budget_fund_src_id_o => l_budget_fund_src_id
934 );
935
936
937 END LOOP; -- loop 3
938 CLOSE pqh_dflt_fund_srcs_cur;
939
940
941 END LOOP; -- loop 2
942 CLOSE pqh_dflt_budget_elements_cur;
943
944 END LOOP; -- loop 1
945 CLOSE pqh_dflt_budget_sets_cur;
946
947
948 END IF; -- p_budget_period_id is not null
949
950 hr_utility.set_location('Leaving:'||l_proc, 1000);
951
952
953 EXCEPTION
954 WHEN others THEN
955 raise;
956 END;
957
958 --------------------------------------------------------------------------------------------------------------
959
960 PROCEDURE populate_budget_sets
961 (
962 p_dflt_budget_sets_rec IN pqh_dflt_budget_sets%ROWTYPE,
963 p_budget_period_id IN pqh_budget_periods.budget_period_id%TYPE,
964 p_budget_set_id_o OUT NOCOPY pqh_budget_sets.budget_set_id%TYPE
965 )
966 IS
967 -- local variables and cursors
968
969 l_proc varchar2(72) := g_package||'populate_budget_sets';
970 l_object_version_number pqh_budget_periods.object_version_number%TYPE;
971 l_budget_periods_rec pqh_budget_periods%ROWTYPE;
972 l_percent_sum pqh_dflt_budget_elements.dflt_dist_percentage%TYPE;
973 l_budget_unit1_available pqh_budget_periods.budget_unit1_available%TYPE;
974
975 CURSOR budget_periods_csr IS
976 SELECT *
977 FROM pqh_budget_periods
978 WHERE budget_period_id = p_budget_period_id;
979
980 CURSOR budget_set_percent_csr IS
981 SELECT SUM(dflt_dist_percentage)
982 FROM pqh_dflt_budget_elements
983 WHERE dflt_budget_set_id = p_dflt_budget_sets_rec.dflt_budget_set_id;
984
985
986 BEGIN
987
988 hr_utility.set_location('Entering: '||l_proc, 5);
989
990 IF p_budget_period_id IS NOT NULL THEN
991
992 OPEN budget_periods_csr;
993 FETCH budget_periods_csr INTO l_budget_periods_rec;
994 CLOSE budget_periods_csr;
995
996 -- compute avaliable
997 OPEN budget_set_percent_csr;
998 FETCH budget_set_percent_csr INTO l_percent_sum;
999 CLOSE budget_set_percent_csr;
1000
1001 l_budget_unit1_available := l_budget_periods_rec.budget_unit1_value - (l_budget_periods_rec.budget_unit1_value*l_percent_sum/100);
1002
1003 -- unit2 and 3 are null in migrated data
1004
1005 -- call insert API
1006 pqh_budget_sets_api.create_budget_set
1007 (
1008 p_validate => false
1009 ,p_budget_set_id => p_budget_set_id_o
1010 ,p_dflt_budget_set_id => p_dflt_budget_sets_rec.dflt_budget_set_id
1011 ,p_budget_period_id => p_budget_period_id
1012 ,p_budget_unit1_percent => l_budget_periods_rec.budget_unit1_percent
1013 ,p_budget_unit2_percent => l_budget_periods_rec.budget_unit2_percent
1014 ,p_budget_unit3_percent => l_budget_periods_rec.budget_unit3_percent
1015 ,p_budget_unit1_value => l_budget_periods_rec.budget_unit1_value
1016 ,p_budget_unit2_value => l_budget_periods_rec.budget_unit2_value
1017 ,p_budget_unit3_value => l_budget_periods_rec.budget_unit3_value
1018 ,p_budget_unit1_available => l_budget_unit1_available
1019 ,p_budget_unit2_available => null
1020 ,p_budget_unit3_available => null
1021 ,p_object_version_number => l_object_version_number
1022 ,p_budget_unit1_value_type_cd => l_budget_periods_rec.budget_unit1_value_type_cd
1023 ,p_budget_unit2_value_type_cd => l_budget_periods_rec.budget_unit2_value_type_cd
1024 ,p_budget_unit3_value_type_cd => l_budget_periods_rec.budget_unit3_value_type_cd
1025 ,p_effective_date => sysdate
1026 );
1027
1028
1029 END IF; -- p_budget_period_id is not null
1030
1031 hr_utility.set_location('PQH Budget Set ID out '||p_budget_set_id_o, 100);
1032 hr_utility.set_location('Leaving:'||l_proc, 1000);
1033
1034 EXCEPTION
1035 WHEN others THEN
1036 p_budget_set_id_o := null;
1037 raise;
1038 END populate_budget_sets;
1039
1040 --------------------------------------------------------------------------------------------------------------
1041 PROCEDURE populate_budget_elements
1042 (
1043 p_dflt_budget_elements_rec IN pqh_dflt_budget_elements%ROWTYPE,
1044 p_budget_set_id IN pqh_budget_sets.budget_set_id%TYPE,
1045 p_budget_element_id_o OUT NOCOPY pqh_budget_elements.budget_element_id%TYPE
1046 )
1047 IS
1048
1049 -- local variables and cursors
1050
1051 l_proc varchar2(72) := g_package||'populate_budget_elements';
1052 l_object_version_number pqh_budget_periods.object_version_number%TYPE;
1053
1054 BEGIN
1055
1056 hr_utility.set_location('Entering: '||l_proc, 5);
1057
1058 IF p_budget_set_id IS NOT NULL THEN
1059
1060 -- call insert API
1061 pqh_budget_elements_api.create_budget_element
1062 (
1063 p_validate => false
1064 ,p_budget_element_id => p_budget_element_id_o
1065 ,p_budget_set_id => p_budget_set_id
1066 ,p_element_type_id => p_dflt_budget_elements_rec.element_type_id
1067 ,p_distribution_percentage => p_dflt_budget_elements_rec.dflt_dist_percentage
1068 ,p_object_version_number => l_object_version_number
1069 );
1070
1071 END IF; -- p_budget_set_id is not null
1072
1073 hr_utility.set_location('PQH Budget Element ID out '||p_budget_element_id_o, 100);
1074 hr_utility.set_location('Leaving:'||l_proc, 1000);
1075
1076 EXCEPTION
1077 WHEN others THEN
1078 p_budget_element_id_o := null;
1079 raise;
1080 END populate_budget_elements;
1081
1082 --------------------------------------------------------------------------------------------------------------
1083 PROCEDURE populate_budget_fund_srcs
1084 (
1085 p_dflt_fund_srcs IN pqh_dflt_fund_srcs%ROWTYPE,
1086 p_budget_element_id IN pqh_budget_elements.budget_element_id%TYPE,
1087 p_budget_fund_src_id_o OUT NOCOPY pqh_budget_fund_srcs.budget_fund_src_id%TYPE
1088 )
1089 IS
1090 -- local variables and cursors
1091
1092 l_proc varchar2(72) := g_package||'populate_budget_fund_srcs';
1093 l_object_version_number pqh_budget_periods.object_version_number%TYPE;
1094
1095 BEGIN
1096
1097 hr_utility.set_location('Entering: '||l_proc, 5);
1098
1099 IF p_budget_element_id IS NOT NULL THEN
1100
1101 -- call insert API
1102 pqh_budget_fund_srcs_api.create_budget_fund_src
1103 (
1104 p_validate => false
1105 ,p_budget_fund_src_id => p_budget_fund_src_id_o
1106 ,p_budget_element_id => p_budget_element_id
1107 ,p_cost_allocation_keyflex_id => p_dflt_fund_srcs.cost_allocation_keyflex_id
1108 ,p_distribution_percentage => p_dflt_fund_srcs.dflt_dist_percentage
1109 ,p_object_version_number => l_object_version_number
1110 );
1111
1112 END IF; -- p_budget_element_id is not null
1113
1114 hr_utility.set_location('PQH Budget Fund Src ID out '||p_budget_fund_src_id_o, 100);
1115 hr_utility.set_location('Leaving:'||l_proc, 1000);
1116
1117 EXCEPTION
1118 WHEN others THEN
1119 p_budget_fund_src_id_o := null;
1120 raise;
1121 END populate_budget_fund_srcs;
1122 --------------------------------------------------------------------------------------------------------------
1123
1124 PROCEDURE populate_globals
1125 IS
1126
1127 /*
1128 This procedure will populate all the global variables.
1129 */
1130
1131 l_proc varchar2(72) := g_package||'populate_globals';
1132
1133
1134 CURSOR csr_table_route (p_table_alias IN varchar2 )IS
1135 SELECT table_route_id
1136 FROM pqh_table_route
1137 WHERE table_alias = p_table_alias;
1138
1139 BEGIN
1140
1141 hr_utility.set_location('Entering:'||l_proc, 5);
1142
1143
1144 -- get table_route_id for all the tables
1145
1146 -- table_route_id for per_budgets
1147 OPEN csr_table_route (p_table_alias => 'P_BGT');
1148 FETCH csr_table_route INTO g_table_route_id_p_bgt;
1149 CLOSE csr_table_route;
1150
1151 -- table_route_id for per_budget_versions
1152 OPEN csr_table_route (p_table_alias => 'P_BVR');
1153 FETCH csr_table_route INTO g_table_route_id_p_bvr;
1154 CLOSE csr_table_route;
1155
1156 -- table_route_id for per_budget_elements
1157 OPEN csr_table_route (p_table_alias => 'P_BDT');
1158 FETCH csr_table_route INTO g_table_route_id_p_bdt;
1159 CLOSE csr_table_route;
1160
1161 -- table_route_id for per_budget_values
1162 OPEN csr_table_route (p_table_alias => 'P_BPR');
1163 FETCH csr_table_route INTO g_table_route_id_p_bpr;
1164 CLOSE csr_table_route;
1165
1166 -- table_route_id for pqh_dflt_budget_sets
1167 OPEN csr_table_route (p_table_alias => 'DST');
1168 FETCH csr_table_route INTO g_table_route_id_dst;
1169 CLOSE csr_table_route;
1170
1171 -- table_route_id for pqh_dflt_budget_elements
1172 OPEN csr_table_route (p_table_alias => 'DEL');
1173 FETCH csr_table_route INTO g_table_route_id_del;
1174 CLOSE csr_table_route;
1175
1176 -- table_route_id for pqh_dflt_budget_fund srcs
1177 OPEN csr_table_route (p_table_alias => 'DFS');
1178 FETCH csr_table_route INTO g_table_route_id_dfs;
1179 CLOSE csr_table_route;
1180
1181 hr_utility.set_location('g_table_route_id_p_bgt: '||g_table_route_id_p_bgt, 50);
1182 hr_utility.set_location('g_table_route_id_p_bvr: '||g_table_route_id_p_bvr, 60);
1183 hr_utility.set_location('g_table_route_id_p_bdt: '||g_table_route_id_p_bdt, 70);
1184 hr_utility.set_location('g_table_route_id_p_bpr: '||g_table_route_id_p_bpr, 80);
1185 hr_utility.set_location('g_table_route_id_dst: '||g_table_route_id_dst, 90);
1186 hr_utility.set_location('g_table_route_id_del: '||g_table_route_id_del, 95);
1187 hr_utility.set_location('g_table_route_id_dfs: '||g_table_route_id_dfs, 96);
1188
1189 hr_utility.set_location('Leaving:'||l_proc, 1000);
1190
1191 EXCEPTION
1192 WHEN OTHERS THEN
1193 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1194 hr_utility.set_message_token('ROUTINE', l_proc);
1195 hr_utility.set_message_token('REASON', SQLERRM);
1196 -- end log and halt the program here
1197 raise g_error_exception;
1198
1199 END populate_globals;
1200
1201
1202 --------------------------------------------------------------------------------------------------------------
1203 PROCEDURE set_p_bgt_log_context
1204 (
1205 p_budget_id IN per_budgets.budget_id%TYPE,
1206 p_log_context OUT NOCOPY pqh_process_log.log_context%TYPE
1207 ) IS
1208 /*
1209 This procedure will set the log_context at per budgets level
1210 */
1211
1212 l_proc varchar2(72) := g_package||'set_p_bgt_log_context';
1213 l_bdg_name per_budgets.name%TYPE;
1214
1215 CURSOR bdg_name_csr IS
1216 SELECT name
1217 FROM per_budgets
1218 WHERE budget_id = p_budget_id;
1219
1220 BEGIN
1221
1222 hr_utility.set_location('Entering:'||l_proc, 5);
1223
1224 OPEN bdg_name_csr;
1225 FETCH bdg_name_csr INTO l_bdg_name;
1226 CLOSE bdg_name_csr;
1227
1228 -- set log context
1229
1230 p_log_context := l_bdg_name;
1231
1232
1233
1234 hr_utility.set_location('Log Context : '||p_log_context, 101);
1235 hr_utility.set_location('Leaving:'||l_proc, 1000);
1236
1237 EXCEPTION
1238 WHEN OTHERS THEN
1239 p_log_context := null;
1240 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1241 hr_utility.set_message_token('ROUTINE', l_proc);
1242 hr_utility.set_message_token('REASON', SQLERRM);
1243 -- end log and halt the program here
1244 raise g_error_exception;
1245 END set_p_bgt_log_context;
1246
1247
1248
1249
1250
1251
1252
1253 --------------------------------------------------------------------------------------------------------------
1254 PROCEDURE set_p_bvr_log_context
1255 (
1256 p_budget_version_id IN per_budget_versions.budget_version_id%TYPE,
1257 p_log_context OUT NOCOPY pqh_process_log.log_context%TYPE
1258 ) IS
1259 /*
1260 This procedure will set the log_context at per budgets level
1261 */
1262
1263 l_proc varchar2(72) := g_package||'set_p_bvr_log_context';
1264 l_bdg_ver_number per_budget_versions.version_number%TYPE;
1265
1266 CURSOR bdg_ver_csr IS
1267 SELECT version_number
1268 FROM per_budget_versions
1269 WHERE budget_version_id = p_budget_version_id;
1270
1271 BEGIN
1272
1273 hr_utility.set_location('Entering:'||l_proc, 5);
1274
1275 OPEN bdg_ver_csr;
1276 FETCH bdg_ver_csr INTO l_bdg_ver_number;
1277 CLOSE bdg_ver_csr;
1278
1279 -- set log context
1280
1281 p_log_context := l_bdg_ver_number;
1282
1283
1284
1285 hr_utility.set_location('Log Context : '||p_log_context, 101);
1286 hr_utility.set_location('Leaving:'||l_proc, 1000);
1287
1288 EXCEPTION
1289 WHEN OTHERS THEN
1290 p_log_context := null;
1291 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1292 hr_utility.set_message_token('ROUTINE', l_proc);
1293 hr_utility.set_message_token('REASON', SQLERRM);
1294 -- end log and halt the program here
1295 raise g_error_exception;
1296 END set_p_bvr_log_context;
1297
1298
1299
1300 --------------------------------------------------------------------------------------------------------------
1301 PROCEDURE set_p_bdt_log_context
1302 (
1303 p_budget_element_id IN per_budget_elements.budget_element_id%TYPE,
1304 p_log_context OUT NOCOPY pqh_process_log.log_context%TYPE
1305 ) IS
1306 /*
1307 This procedure will set the log_context at per budgets level
1308 Display Order is P J O G ( which ever is not null
1309 */
1310 l_proc varchar2(72) := g_package||'set_p_bdt_log_context';
1311 l_budget_elements_rec per_budget_elements%ROWTYPE;
1312 l_position_name hr_all_positions.name%TYPE;
1313 l_job_name per_jobs.name%TYPE;
1314 l_organization_name hr_all_organization_units_tl.name%TYPE;
1315 l_grade_name per_grades.name%TYPE;
1316
1317 CURSOR csr_bdg_elmnt_rec IS
1318 SELECT *
1319 FROM per_budget_elements
1320 WHERE budget_element_id = p_budget_element_id ;
1321
1322 BEGIN
1323
1324 hr_utility.set_location('Entering:'||l_proc, 5);
1325
1326 OPEN csr_bdg_elmnt_rec;
1327 FETCH csr_bdg_elmnt_rec INTO l_budget_elements_rec;
1328 CLOSE csr_bdg_elmnt_rec;
1329
1330
1331 l_position_name := HR_GENERAL.DECODE_POSITION (p_position_id => l_budget_elements_rec.position_id);
1332 l_job_name := HR_GENERAL.DECODE_JOB (p_job_id => l_budget_elements_rec.job_id);
1333 l_organization_name := HR_GENERAL.DECODE_ORGANIZATION (p_organization_id => l_budget_elements_rec.organization_id);
1334 l_grade_name := HR_GENERAL.DECODE_GRADE (p_grade_id => l_budget_elements_rec.grade_id);
1335
1336
1337 IF l_position_name IS NOT NULL THEN
1338 p_log_context := SUBSTR(l_position_name,1,255);
1339 ELSIF l_job_name IS NOT NULL THEN
1340 p_log_context := SUBSTR(l_job_name,1,255);
1341 ELSIF l_organization_name IS NOT NULL THEN
1342 p_log_context := SUBSTR(l_organization_name,1,255);
1343 ELSIF l_grade_name IS NOT NULL THEN
1344 p_log_context := SUBSTR(l_grade_name,1,255);
1345 ELSE
1346 p_log_context := 'Budget Element';
1347 END IF;
1348
1349
1350 hr_utility.set_location('Log Context : '||p_log_context, 100);
1351
1352
1353 hr_utility.set_location('Leaving:'||l_proc, 1000);
1354
1355 EXCEPTION
1356 WHEN OTHERS THEN
1357 p_log_context := null;
1358 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1359 hr_utility.set_message_token('ROUTINE', l_proc);
1360 hr_utility.set_message_token('REASON', SQLERRM);
1361 -- end log and halt the program here
1362 raise g_error_exception;
1363 END set_p_bdt_log_context;
1364
1365 --------------------------------------------------------------------------------------------------------------
1366 PROCEDURE set_p_bpr_log_context
1367 (
1368 p_budget_value_id IN per_budget_values.budget_value_id%TYPE,
1369 p_log_context OUT NOCOPY pqh_process_log.log_context%TYPE
1370 ) IS
1371 /*
1372 This procedure will set the log_context at per budgets level
1373 */
1374
1375 l_proc varchar2(72) := g_package||'set_p_bpr_log_context';
1376 l_per_budget_values_rec per_budget_values%ROWTYPE;
1377 l_per_time_periods_rec per_time_periods%ROWTYPE;
1378
1379 CURSOR csr_bpr_periods_rec IS
1380 SELECT *
1381 FROM per_budget_values
1382 WHERE budget_value_id = p_budget_value_id ;
1383
1384 CURSOR csr_per_time_periods ( p_time_period_id IN number ) IS
1385 SELECT *
1386 FROM per_time_periods
1387 WHERE time_period_id = p_time_period_id ;
1388
1389
1390 BEGIN
1391
1392 hr_utility.set_location('Entering:'||l_proc, 5);
1393
1394 OPEN csr_bpr_periods_rec;
1395 FETCH csr_bpr_periods_rec INTO l_per_budget_values_rec;
1396 CLOSE csr_bpr_periods_rec;
1397
1398 OPEN csr_per_time_periods(p_time_period_id => l_per_budget_values_rec.time_period_id);
1399 FETCH csr_per_time_periods INTO l_per_time_periods_rec;
1400 CLOSE csr_per_time_periods;
1401
1402 -- set log context
1403
1404 p_log_context := l_per_time_periods_rec.period_name;
1405
1406
1407
1408 hr_utility.set_location('Log Context : '||p_log_context, 101);
1409 hr_utility.set_location('Leaving:'||l_proc, 1000);
1410
1411 EXCEPTION
1412 WHEN OTHERS THEN
1413 p_log_context := null;
1414 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1415 hr_utility.set_message_token('ROUTINE', l_proc);
1416 hr_utility.set_message_token('REASON', SQLERRM);
1417 -- end log and halt the program here
1418 raise g_error_exception;
1419 END set_p_bpr_log_context;
1420
1421
1422 --------------------------------------------------------------------------------------------------------------
1423 PROCEDURE check_params
1424 (
1425 p_budget_name IN per_budgets.name%TYPE,
1426 p_budget_set_name IN pqh_dflt_budget_sets.dflt_budget_set_name%TYPE,
1427 p_business_group_id IN per_budgets.business_group_id%TYPE
1428 ) IS
1429 /*
1430 This procedure will check at the input params are valid else it will log error and abort the program
1431 Valid Params :
1432 There should atleast be one record in per_budgets with name = p_budget_name and budget_type_code <> OTA_BUDGET
1433 as we are not migrating OTA_BUDGETS
1434 The budget_set_name must exist in pqh_dflt_budget_sets ( dflt_budget_set_name )
1435 */
1436
1437 l_proc varchar2(72) := g_package||'check_params';
1438 l_bdg_cnt number := 0;
1439 l_set_cnt number := 0;
1440 l_message_number_out fnd_new_messages.message_number%TYPE;
1441
1442
1443 CURSOR cnt_budgets_csr IS
1444 SELECT COUNT(*)
1445 FROM per_budgets
1446 WHERE name = NVL(p_budget_name, name)
1447 AND business_group_id = p_business_group_id
1448 AND NVL(budget_type_code,'X') <> 'OTA_BUDGET' ;
1449
1450 CURSOR bdg_sets_csr IS
1451 SELECT COUNT(*)
1452 FROM pqh_dflt_budget_sets
1453 WHERE dflt_budget_set_name = p_budget_set_name;
1454
1455 BEGIN
1456
1457 hr_utility.set_location('Entering:'||l_proc, 5);
1458
1459 -- count budgets
1460 OPEN cnt_budgets_csr;
1461 FETCH cnt_budgets_csr INTO l_bdg_cnt;
1462 CLOSE cnt_budgets_csr;
1463
1464 -- count sets
1465 OPEN bdg_sets_csr;
1466 FETCH bdg_sets_csr INTO l_set_cnt;
1467 CLOSE bdg_sets_csr;
1468
1469 -- if cnt is zero then stop here after logging the event
1470 IF l_bdg_cnt = 0 THEN
1471
1472 -- get the message text PQH_BDG_MIG_INV_NAME
1473 FND_MESSAGE.SET_NAME('PQH','PQH_BDG_MIG_INV_NAME');
1474 APP_EXCEPTION.RAISE_EXCEPTION;
1475 ELSIF l_set_cnt = 0 THEN
1476
1477 -- get the message text PQH_BDG_MIG_INV_SET
1478 FND_MESSAGE.SET_NAME('PQH','PQH_BDG_MIG_INV_SET');
1479 APP_EXCEPTION.RAISE_EXCEPTION;
1480
1481 END IF;
1482
1483 hr_utility.set_location('Leaving:'||l_proc, 1000);
1484
1485 EXCEPTION
1486 WHEN OTHERS THEN
1487 raise;
1488 END;
1489
1490 --------------------------------------------------------------------------------------------------------------
1491 PROCEDURE check_valid_budget
1492 (
1493 p_per_budgets_rec IN per_budgets%ROWTYPE,
1494 p_valid OUT NOCOPY varchar2
1495 ) IS
1496 /*
1497 This procedure validates if the budget is valid. For budget to be valid there must be no
1498 records in per_budget_elements which do not have child records in per_budget_values
1499 If it is not valid we set p_valid = N so that we can skip this budget migration and give the
1500 error message
1501 */
1502
1503 l_proc varchar2(72) := g_package||'check_valid_budget';
1504 l_cnt_elements number;
1505 l_cnt_values number;
1506 l_message_text_out fnd_new_messages.message_text%TYPE;
1507
1508
1509 CURSOR cnt_elements IS
1510 SELECT COUNT(budget_element_id)
1511 FROM per_budget_elements
1512 WHERE budget_version_id IN
1513 ( SELECT budget_version_id
1514 FROM per_budget_versions
1515 WHERE budget_id = p_per_budgets_rec.budget_id
1516 );
1517
1518 CURSOR cnt_values IS
1519 SELECT COUNT(distinct budget_element_id)
1520 FROM per_budget_values
1521 WHERE budget_element_id IN
1522 ( SELECT budget_element_id
1523 FROM per_budget_elements
1524 WHERE budget_version_id IN
1525 ( SELECT budget_version_id
1526 FROM per_budget_versions
1527 WHERE budget_id = p_per_budgets_rec.budget_id
1528 )
1529 );
1530
1531
1532 BEGIN
1533
1534 hr_utility.set_location('Entering:'||l_proc, 5);
1535
1536 OPEN cnt_elements;
1537 FETCH cnt_elements INTO l_cnt_elements;
1538 CLOSE cnt_elements;
1539
1540 OPEN cnt_values;
1541 FETCH cnt_values INTO l_cnt_values;
1542 CLOSE cnt_values;
1543
1544 IF NVL(l_cnt_elements,0) <> NVL(l_cnt_values,0) THEN
1545
1546 -- there are some elements under this budget without rows in budget values
1547 -- skip this budget
1548 p_valid := 'N';
1549
1550 -- get message text for PQH_WKS_INVALID_ID
1551 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_PER_BUDGET');
1552 l_message_text_out := FND_MESSAGE.GET;
1553
1554 -- insert error into log table
1555 pqh_process_batch_log.insert_log
1556 (
1557 p_message_type_cd => 'ERROR',
1558 p_message_text => l_message_text_out
1559 );
1560
1561
1562 ELSE
1563 p_valid := 'Y';
1564 END IF;
1565
1566
1567 hr_utility.set_location('Leaving:'||l_proc, 1000);
1568 exception
1569 when others then
1570 p_valid := null;
1571 raise;
1572
1573 END check_valid_budget;
1574
1575
1576 -----------------------------------------------------------------------------------------------
1577 PROCEDURE migrate_bdgt(p_budget_id in number,
1578 p_dflt_budget_set_id in number,
1579 p_request_number out nocopy number) is
1580 Cursor csr_budget is
1581 Select name,business_group_id
1582 From per_budgets
1583 Where budget_id = p_budget_id;
1584 --
1585 CURSOR bdg_sets_csr IS
1586 SELECT dflt_budget_set_name
1587 FROM pqh_dflt_budget_sets
1588 WHERE dflt_budget_set_id = p_dflt_budget_set_id;
1589 --
1590 l_name per_budgets.name%type;
1591 l_business_group_id per_budgets.business_group_id%type;
1592 l_bset_name pqh_dflt_budget_sets.dflt_budget_set_name%type;
1593 --
1594 begin
1595 --
1596 Open csr_budget;
1597 Fetch csr_budget into l_name,l_business_group_id;
1598 Close csr_budget;
1599 --
1600 Open bdg_sets_csr;
1601 Fetch bdg_sets_csr into l_bset_name;
1602 Close bdg_sets_csr;
1603
1604 --
1605 p_request_number := -1;
1606 --
1607 p_request_number := fnd_request.submit_request(application => 'PQH',
1608 program => 'PQHBDGMIG',
1609 argument1 => l_name,
1610 argument2 => l_bset_name,
1611 argument3 => l_business_group_id);
1612 exception
1613 when others then
1614 p_request_number := null;
1615 raise;
1616 end migrate_bdgt;
1617
1618 -----------------------------------------------------------------------------------------
1619
1620
1621
1622 END; -- Package Body PQH_BUDGET_DATA_MIGRATION