[Home] [Help]
PACKAGE BODY: APPS.PQH_APPLY_BUDGET
Source
1 Package Body PQH_APPLY_BUDGET AS
2 /* $Header: pqappbdg.pkb 115.38 2004/02/05 12:40:56 rthiagar ship $ */
3 -- ----------------------------------------------------------------------------
4 -- | Private Global Definitions |
5 -- ----------------------------------------------------------------------------
6 --
7 g_package varchar2(33) := ' pqh_apply_budget.'; -- Global package name
8 g_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
9 g_worksheet_mode_cd pqh_worksheets.worksheet_mode_cd%TYPE;
10 g_budget_id pqh_worksheets.budget_id%TYPE;
11
12 g_budgeted_entity_cd pqh_budgets.budgeted_entity_cd%TYPE;
13 g_table_route_id_wks NUMBER;
14 g_table_route_id_wdt NUMBER;
15 g_table_route_id_wpr NUMBER;
16 g_table_route_id_wst NUMBER;
17 g_table_route_id_wel NUMBER;
18 g_table_route_id_wfs NUMBER;
19 g_transaction_category_id pqh_transaction_categories.transaction_category_id%TYPE;
20 g_worksheet_name pqh_worksheets.worksheet_name%TYPE;
21 g_worksheet_id pqh_worksheets.worksheet_id%TYPE;
22 g_error_exception exception;
23 g_curr_wks_dtl_level NUMBER;
24 g_root_wks_dtl_id NUMBER;
25
26 --
27 --
28 /*--------------------------------------------------------------------------------------------------------------
29
30 Main Procedure
31 --------------------------------------------------------------------------------------------------------------*/
32 PROCEDURE updt_wkd_status
33 (
34 p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE,
35 p_status IN pqh_worksheets.transaction_status%TYPE
36 ) IS
37 /*
38
39 */
40
41 l_proc varchar2(72) := g_package||'updt_wkd_status';
42
43
44 CURSOR csr_wkd IS
45 SELECT *
46 FROM pqh_worksheet_details
47 WHERE worksheet_id = p_worksheet_id
48 and parent_worksheet_detail_id is null;
49
50 l_wkd_rec pqh_worksheet_details%ROWTYPE;
51 l_wkd_ovn pqh_worksheet_details.object_version_number%TYPE;
52 BEGIN
53 hr_utility.set_location('Entering:'||l_proc, 5);
54 open csr_wkd;
55 fetch csr_wkd into l_wkd_rec;
56 CLOSE csr_wkd;
57 l_wkd_ovn := l_wkd_rec.object_version_number;
58 pqh_worksheet_details_api.update_worksheet_detail(
59 p_validate => false
60 ,p_worksheet_detail_id => l_wkd_rec.worksheet_detail_id
61 ,p_object_version_number => l_wkd_ovn
62 ,p_status => p_status
63 ,p_effective_date => sysdate
64 );
65 hr_utility.set_location('Leaving:'||l_proc, 1000);
66 EXCEPTION
67 WHEN OTHERS THEN
68 hr_utility.set_location('error:'||substr(sqlerrm,1,55), 1000);
69 hr_utility.set_location('error:'||substr(sqlerrm,56,55), 1000);
70 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
71 hr_utility.set_message_token('ROUTINE', l_proc);
72 hr_utility.set_message_token('REASON', SQLERRM);
73 hr_utility.raise_error;
74 END updt_wkd_status;
75
76 --------------------------------------------------------------------------------------------------------------
77 PROCEDURE apply_budget
78 (
79 p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE,
80 p_budget_version_id OUT NOCOPY pqh_budget_versions.budget_version_id%TYPE
81 )
82 IS
83 -- local variables and cursors
84
85 CURSOR pqh_worksheets_cur(p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE) IS
86 SELECT *
87 FROM pqh_worksheets
88 WHERE worksheet_id = p_worksheet_id;
89
90 l_proc varchar2(72) := g_package||'apply_budget';
91 l_pqh_worksheets_rec pqh_worksheets%ROWTYPE;
92 l_log_context pqh_process_log.log_context%TYPE;
93
94 BEGIN
95
96 hr_utility.set_location('Entering: '||l_proc, 5);
97
98 -- check the mode for the current worksheet is valid
99 -- and populate the global mode_cd variable
100 -- check that the worksheet has not been already applied
101 check_valid_mode ( p_worksheet_id => p_worksheet_id);
102
103 -- open pqh_worksheets_cur
104 OPEN pqh_worksheets_cur (p_worksheet_id => p_worksheet_id);
105 FETCH pqh_worksheets_cur INTO l_pqh_worksheets_rec;
106 CLOSE pqh_worksheets_cur;
107
108 -- populate the global variable
109 g_budget_id := l_pqh_worksheets_rec.budget_id;
110
111 -- populate other global variables
112 populate_globals
113 (
114 p_worksheet_id => p_worksheet_id
115 );
116
117
118 hr_utility.set_location('Worksheet Mode : '||g_worksheet_mode_cd, 6);
119 hr_utility.set_location('Budget ID : '||g_budget_id, 7);
120
121 -- Start the Log Process
122 pqh_process_batch_log.start_log
123 (
124 p_batch_id => g_worksheet_id,
125 p_module_cd => 'APPLY_BUDGET',
126 p_log_context => g_worksheet_name
127 );
128
129 -- set wks id as the top most context level
130 -- set the context before inserting error
131 pqh_process_batch_log.set_context_level
132 (
133 p_txn_id => g_worksheet_id,
134 p_txn_table_route_id => g_table_route_id_wks,
135 p_level => 1,
136 p_log_context => g_worksheet_name
137 );
138
139
140 /*
141 Depending on the worksheet_mode_cd call the corresponding procedure
142 */
143 IF l_pqh_worksheets_rec.worksheet_mode_cd = 'S' THEN
144 -- first version no carry forwardi.e NEW
145 -- OR new version with no carry forward i.e NEW_OVERRIDE
146 apply_new_budget
147 (
148 p_worksheet_id => p_worksheet_id,
149 p_mode => 'I'
150 );
151 ELSIF l_pqh_worksheets_rec.worksheet_mode_cd = 'W' THEN
152 -- new version with no carry forward i.e NEW_OVERRIDE
153 -- as of 02/16/2000 this mode is discontinued
154 apply_new_budget
155 (
156 p_worksheet_id => p_worksheet_id,
157 p_mode => 'I'
158 );
159 ELSIF l_pqh_worksheets_rec.worksheet_mode_cd = 'N' THEN
160 -- edit existing version and create a new version
161 -- with carry forward i.e EDIT_NEW no carry forward as of 06/09/2000
162 edit_create_new_budget
163 (
164 p_worksheet_id => p_worksheet_id
165 );
166 ELSIF l_pqh_worksheets_rec.worksheet_mode_cd = 'O' THEN
167 -- edit existing version and update the same version
168 -- with carry forward i.e EDIT_UPDATE
169 edit_update_budget
170 (
171 p_worksheet_id => p_worksheet_id
172 );
173
174 -- update the pqh_budget_version record with correct unit1, 2, 3 values
175 comp_bgt_ver_unit_val
176 (
177 p_budget_version_id => g_budget_version_id
178 );
179
180 ELSE
181 -- invalid mode code
182 hr_utility.set_location('Invalid Worksheet Mode : '||g_worksheet_mode_cd, 7);
183 hr_utility.set_message(8302,'PQH_INVALID_WORKSHEET_MODE_CD');
184 hr_utility.raise_error;
185 END IF;
186
187 -- update the worksheet status flag to 'APPLIED
188 -- and updt budget_version_id
189
190 updt_wks_status
191 (
192 p_worksheet_id => p_worksheet_id ,
193 p_status => 'APPLIED'
194 );
195 hr_utility.set_location('worksheet updated with Applied'||l_proc, 8);
196
197 updt_wkd_status
198 (
199 p_worksheet_id => p_worksheet_id ,
200 p_status => 'APPLIED'
201 );
202 hr_utility.set_location('wkd updated with Applied'||l_proc, 9);
203
204 -- update the status in pqh_budgets to FROZEN
205
206
207 updt_budget_status
208 (
209 p_budget_id => g_budget_id
210 );
211
212
213 -- Populate the OUT variable p_budget_version_id
214 p_budget_version_id := g_budget_version_id;
215
216 -- call the end log and stop
217 pqh_process_batch_log.end_log;
218
219 -- commit the work;
220 -- commit;
221
222 hr_utility.set_location('Leaving:'||l_proc, 1000);
223
224 EXCEPTION
225 WHEN g_error_exception THEN
226 -- call the end log and stop
227 pqh_process_batch_log.end_log;
228 -- call the wf error
229 hr_utility.set_location('txn_cat :'||g_transaction_category_id||l_proc, 10);
230 hr_utility.set_location('txn_id :'||g_root_wks_dtl_id||l_proc, 10);
231 pqh_wf.set_apply_error(p_transaction_category_id => g_transaction_category_id,
232 p_transaction_id => g_root_wks_dtl_id,
233 p_apply_error_mesg => SQLERRM,
234 p_apply_error_num => SQLCODE);
235 WHEN others THEN
236 p_budget_version_id := null;
237 raise;
238 END apply_budget;
239
240
241 --------------------------------------------------------------------------------------------------------------
242 PROCEDURE apply_new_budget
243 (
244 p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE,
245 p_mode IN varchar2
246 )
247 IS
248 -- local variables and cursors
249
250 CURSOR pqh_worksheets_cur(p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE) IS
251 SELECT *
252 FROM pqh_worksheets
253 WHERE worksheet_id = p_worksheet_id;
254
255 CURSOR pqh_worksheet_details_cur (p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE) IS
256 SELECT *
257 FROM pqh_worksheet_details
258 WHERE worksheet_id = p_worksheet_id
259 AND NVL(action_cd,'X') = 'B' ;
260
261 CURSOR pqh_worksheet_periods_cur (p_worksheet_detail_id IN pqh_worksheet_details.worksheet_detail_id%TYPE) IS
262 SELECT *
263 FROM pqh_worksheet_periods
264 WHERE worksheet_detail_id = p_worksheet_detail_id;
265
266 CURSOR pqh_worksheet_budget_sets_cur (p_worksheet_period_id IN pqh_worksheet_periods.worksheet_period_id%TYPE) IS
267 SELECT *
268 FROM pqh_worksheet_budget_sets
269 WHERE worksheet_period_id = p_worksheet_period_id;
270
271 CURSOR pqh_worksheet_bdgt_elmnts_cur (p_worksheet_budget_set_id IN pqh_worksheet_budget_sets.worksheet_budget_set_id%TYPE) IS
272 SELECT *
273 FROM pqh_worksheet_bdgt_elmnts
274 WHERE worksheet_budget_set_id = p_worksheet_budget_set_id;
275
276 CURSOR pqh_worksheet_fund_srcs_cur (p_worksheet_bdgt_elmnt_id IN pqh_worksheet_bdgt_elmnts.worksheet_bdgt_elmnt_id%TYPE) IS
277 SELECT *
278 FROM pqh_worksheet_fund_srcs
279 WHERE worksheet_bdgt_elmnt_id = p_worksheet_bdgt_elmnt_id;
280
281 CURSOR current_version_cur (p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE ) IS
282 SELECT bvr.budget_version_id
283 FROM pqh_budget_versions bvr, pqh_worksheets wks
284 WHERE bvr.budget_id = wks.budget_id
285 AND bvr.version_number = wks.version_number
286 AND wks.worksheet_id = p_worksheet_id;
287
288 l_proc varchar2(72) := g_package||'apply_new_budget';
289 l_pqh_worksheets_rec pqh_worksheets%ROWTYPE;
290 l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
291 l_pqh_worksheet_details_rec pqh_worksheet_details%ROWTYPE;
292 l_budget_detail_id pqh_budget_details.budget_detail_id%TYPE;
293 l_pqh_worksheet_periods_rec pqh_worksheet_periods%ROWTYPE;
294 l_budget_period_id pqh_budget_periods.budget_period_id%TYPE;
295 l_pqh_worksheet_budget_set_rec pqh_worksheet_budget_sets%ROWTYPE;
296 l_budget_set_id pqh_budget_sets.budget_set_id%TYPE;
297 l_pqh_worksheet_bdgt_elmnt_rec pqh_worksheet_bdgt_elmnts%ROWTYPE;
298 l_budget_element_id pqh_budget_elements.budget_element_id%TYPE;
299 l_pqh_worksheet_fund_srcs_rec pqh_worksheet_fund_srcs%ROWTYPE;
300 l_budget_fund_src_id pqh_budget_fund_srcs.budget_fund_src_id%TYPE;
301 l_curr_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
302 l_log_context pqh_process_log.log_context%TYPE;
303
304 BEGIN
305
306 hr_utility.set_location('Entering: '||l_proc, 5);
307 -- open the pqh_worksheets_cur
308 OPEN pqh_worksheets_cur(p_worksheet_id => p_worksheet_id);
309 LOOP -- loop 1
310 FETCH pqh_worksheets_cur INTO l_pqh_worksheets_rec;
311 EXIT WHEN pqh_worksheets_cur%NOTFOUND;
312 IF p_mode = 'I' THEN
313 -- create records in pqh_budget_versions
314 populate_budget_versions
315 (
316 p_worksheets_rec => l_pqh_worksheets_rec,
317 p_budget_id => l_pqh_worksheets_rec.budget_id,
318 p_worksheet_mode_cd => g_worksheet_mode_cd,
319 p_budget_version_id_o => l_budget_version_id
320 );
321
322 -- populate the global variable with the version_id
323 g_budget_version_id := l_budget_version_id;
324
325 ELSE
326 -- no new record in the pqh_budget_versions as this is update mode
327 -- get the current version_id
328 hr_utility.set_location('Called Apply Budget in Update Mode: '||p_mode, 6);
329 OPEN current_version_cur(p_worksheet_id => p_worksheet_id);
330 FETCH current_version_cur INTO l_curr_budget_version_id;
331 CLOSE current_version_cur;
332
333 -- populate the global variable with the version_id
334 g_budget_version_id := l_curr_budget_version_id;
335
336 END IF;
337
338 hr_utility.set_location('Budget Version: '||g_budget_version_id, 6);
339
340
341 -- open pqh_worksheet_details_cur
342 OPEN pqh_worksheet_details_cur(p_worksheet_id => l_pqh_worksheets_rec.worksheet_id );
343 LOOP -- loop 2
344 FETCH pqh_worksheet_details_cur INTO l_pqh_worksheet_details_rec;
345 EXIT WHEN pqh_worksheet_details_cur%NOTFOUND;
346
347 -- get log_context
348 set_wks_log_context
349 (
350 p_worksheet_detail_id => l_pqh_worksheet_details_rec.worksheet_detail_id,
351 p_log_context => l_log_context
352 );
353
354 -- set the context
355 pqh_process_batch_log.set_context_level
356 (
357 p_txn_id => l_pqh_worksheet_details_rec.worksheet_detail_id,
358 p_txn_table_route_id => g_table_route_id_wdt,
359 p_level => 2,
360 p_log_context => l_log_context
361 );
362
363 -- create records in pqh_budget_details
364 populate_budget_details
365 (
366 p_worksheet_details_rec => l_pqh_worksheet_details_rec,
367 p_budget_version_id => g_budget_version_id,
368 p_worksheet_id => l_pqh_worksheets_rec.worksheet_id,
369 p_worksheet_mode_cd => g_worksheet_mode_cd,
370 p_budget_detail_id_o => l_budget_detail_id
371 );
372
373 -- open pqh_worksheet_periods_cur
374 OPEN pqh_worksheet_periods_cur(p_worksheet_detail_id => l_pqh_worksheet_details_rec.worksheet_detail_id);
375 LOOP -- loop 3
376 FETCH pqh_worksheet_periods_cur INTO l_pqh_worksheet_periods_rec;
377 EXIT WHEN pqh_worksheet_periods_cur%NOTFOUND;
378
379 -- get log_context
380 set_wpr_log_context
381 (
382 p_worksheet_period_id => l_pqh_worksheet_periods_rec.worksheet_period_id,
383 p_log_context => l_log_context
384 );
385
386 -- set the context
387 pqh_process_batch_log.set_context_level
388 (
389 p_txn_id => l_pqh_worksheet_periods_rec.worksheet_period_id,
390 p_txn_table_route_id => g_table_route_id_wpr,
391 p_level => 3,
392 p_log_context => l_log_context
393 );
394
395
396 -- create records in pqh_budget_periods
397 populate_budget_periods
398 (
399 p_worksheet_periods_rec => l_pqh_worksheet_periods_rec,
400 p_budget_detail_id => l_budget_detail_id,
401 p_budget_period_id_o => l_budget_period_id
402 );
403
404 -- open pqh_worksheet_budget_sets_cur
405 OPEN pqh_worksheet_budget_sets_cur(p_worksheet_period_id => l_pqh_worksheet_periods_rec.worksheet_period_id);
406 LOOP -- loop 4
407 FETCH pqh_worksheet_budget_sets_cur INTO l_pqh_worksheet_budget_set_rec;
408 EXIT WHEN pqh_worksheet_budget_sets_cur%NOTFOUND;
409
410 -- get log_context
411 set_wst_log_context
412 (
413 p_worksheet_budget_set_id => l_pqh_worksheet_budget_set_rec.worksheet_budget_set_id,
414 p_log_context => l_log_context
415 );
416
417 -- set the context
418 pqh_process_batch_log.set_context_level
419 (
420 p_txn_id => l_pqh_worksheet_budget_set_rec.worksheet_budget_set_id,
421 p_txn_table_route_id => g_table_route_id_wst,
422 p_level => 4,
423 p_log_context => l_log_context
424 );
425
426
427 -- create records in pqh_budget_sets
428 populate_budget_sets
429 (
430 p_worksheet_budget_sets_rec => l_pqh_worksheet_budget_set_rec,
431 p_budget_period_id => l_budget_period_id,
432 p_budget_set_id_o => l_budget_set_id
433 );
434
435 -- open pqh_worksheet_bdgt_elmnts_cur
436 OPEN pqh_worksheet_bdgt_elmnts_cur(p_worksheet_budget_set_id => l_pqh_worksheet_budget_set_rec.worksheet_budget_set_id);
437 LOOP -- loop 5
438 FETCH pqh_worksheet_bdgt_elmnts_cur INTO l_pqh_worksheet_bdgt_elmnt_rec;
439 EXIT WHEN pqh_worksheet_bdgt_elmnts_cur%NOTFOUND;
440
441 -- get log_context
442 set_wel_log_context
443 (
444 p_worksheet_bdgt_elmnt_id => l_pqh_worksheet_bdgt_elmnt_rec.worksheet_bdgt_elmnt_id,
445 p_log_context => l_log_context
446 );
447
448 -- set the context
449 pqh_process_batch_log.set_context_level
450 (
451 p_txn_id => l_pqh_worksheet_bdgt_elmnt_rec.worksheet_bdgt_elmnt_id,
452 p_txn_table_route_id => g_table_route_id_wel,
453 p_level => 5,
454 p_log_context => l_log_context
455 );
456
457 -- create records in pqh_budget_elements
458 populate_budget_elements
459 (
460 p_worksheet_bdgt_elmnts_rec => l_pqh_worksheet_bdgt_elmnt_rec,
461 p_budget_set_id => l_budget_set_id,
462 p_budget_element_id_o => l_budget_element_id
463 );
464
465 -- open pqh_worksheet_fund_srcs_cur
466 OPEN pqh_worksheet_fund_srcs_cur(p_worksheet_bdgt_elmnt_id => l_pqh_worksheet_bdgt_elmnt_rec.worksheet_bdgt_elmnt_id);
467 LOOP -- loop 6
468 FETCH pqh_worksheet_fund_srcs_cur INTO l_pqh_worksheet_fund_srcs_rec;
469 EXIT WHEN pqh_worksheet_fund_srcs_cur%NOTFOUND;
470
471 -- get log_context
472 set_wfs_log_context
473 (
474 p_worksheet_fund_src_id => l_pqh_worksheet_fund_srcs_rec.worksheet_fund_src_id,
475 p_log_context => l_log_context
476 );
477
478 -- set the context
479 pqh_process_batch_log.set_context_level
480 (
481 p_txn_id => l_pqh_worksheet_fund_srcs_rec.worksheet_fund_src_id,
482 p_txn_table_route_id => g_table_route_id_wfs,
483 p_level => 6,
484 p_log_context => l_log_context
485 );
486
487 -- create records in pah_budget_fund_srcs
488 populate_budget_fund_srcs
489 (
490 p_worksheet_fund_srcs_rec => l_pqh_worksheet_fund_srcs_rec,
491 p_budget_element_id => l_budget_element_id,
492 p_budget_fund_src_id_o => l_budget_fund_src_id
493 );
494
495
496 END LOOP; -- loop 6
497 CLOSE pqh_worksheet_fund_srcs_cur;
498
499 END LOOP; -- loop 5
500 CLOSE pqh_worksheet_bdgt_elmnts_cur;
501
502 END LOOP; -- loop 4
503 CLOSE pqh_worksheet_budget_sets_cur;
504
505 END LOOP; -- loop 3
506 CLOSE pqh_worksheet_periods_cur;
507
508 END LOOP; -- loop 2
509 CLOSE pqh_worksheet_details_cur;
510
511 END LOOP; -- loop 1
512 CLOSE pqh_worksheets_cur;
513
514
515 hr_utility.set_location('Leaving:'||l_proc, 1000);
516
517 EXCEPTION
518 WHEN others THEN
519 raise;
520 END;
521
522
523 --------------------------------------------------------------------------------------------------------------
524
525 PROCEDURE edit_create_new_budget
526 (
527 p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE
528 )
529 IS
530 -- local variables and cursors
531
532 l_proc varchar2(72) := g_package||'edit_create_new_budget';
533
534 BEGIN
535
536 hr_utility.set_location('Entering: '||l_proc, 5);
537
538 -- apply the existing records from worksheet tables to budget tables
539 apply_new_budget
540 (
541 p_worksheet_id => p_worksheet_id,
542 p_mode => 'I'
543 );
544
545 /*
546 As of rqmt 06/09/2000 we will not carry forward any budget records for this mode
547
548 -- carry forward the remaining records from budget tables
549 carry_forward_budget
550 (
551 p_worksheet_id => p_worksheet_id,
552 p_budget_version_id => g_budget_version_id
553 );
554
555 */
556
557 hr_utility.set_location('Leaving:'||l_proc, 1000);
558
559 EXCEPTION
560 WHEN others THEN
561 raise;
562 END;
563
564
565
566 --------------------------------------------------------------------------------------------------------------
567
568
569 PROCEDURE edit_update_budget
570 (
571 p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE
572 )
573 IS
574 -- local variables and cursors
575
576 l_proc varchar2(72) := g_package||'edit_update_budget';
577
578 BEGIN
579
580 hr_utility.set_location('Entering: '||l_proc, 5);
581
582 -- delete the rows from budget tables
583 /*
584 delete_child_rows
585 we pick budget_detail_id from pqh_worksheet_details table for this worksheet_id
586 and delete only those rows from pqh_budget_periods table onwards for the above
587 fetched budget_detail_id from pqh_worksheet_details table.
588 */
589 delete_child_rows
590 (
591 p_worksheet_id => p_worksheet_id
592 );
593
594 -- now create new rows
595 apply_new_budget
596 (
597 p_worksheet_id => p_worksheet_id,
598 p_mode => 'U'
599 );
600
601 hr_utility.set_location('Leaving:'||l_proc, 1000);
602
603 EXCEPTION
604 WHEN others THEN
605 raise;
606 END;
607
608
609
610
611
612 --------------------------------------------------------------------------------------------------------------
613
614 PROCEDURE populate_budget_versions
615 (
616 p_worksheets_rec IN pqh_worksheets%ROWTYPE,
617 p_budget_id IN pqh_budgets.budget_id%TYPE,
618 p_worksheet_mode_cd IN pqh_worksheets.worksheet_mode_cd%TYPE,
619 p_budget_version_id_o OUT NOCOPY pqh_budget_versions.budget_version_id%TYPE
620 )
621 IS
622 -- local variables and cursors
623
624 CURSOR version_number_cur IS
625 SELECT NVL(max(version_number),0)
626 FROM pqh_budget_versions
627 WHERE budget_id = p_budget_id;
628
629 CURSOR budget_version_cur(p_curr_version_number IN number) IS
630 SELECT *
631 FROM pqh_budget_versions
632 WHERE budget_id = p_budget_id
633 AND version_number = p_curr_version_number;
634
635 -- cursor for unit1_value,2,3
636 CURSOR units_csr IS
637 SELECT sum(nvl(BUDGET_UNIT1_VALUE,0)) ,
638 sum(nvl(BUDGET_UNIT2_VALUE,0)) ,
639 sum(nvl(BUDGET_UNIT3_VALUE,0))
640 FROM pqh_worksheet_details
641 WHERE worksheet_id = p_worksheets_rec.worksheet_id
642 AND nvl(action_cd,'X') = 'B' ;
643
644
645 l_proc varchar2(72) := g_package||'populate_budget_versions';
646 l_object_version_number pqh_budget_versions.object_version_number%TYPE;
647 l_version_number pqh_budget_versions.version_number%TYPE;
648 l_curr_version_number pqh_budget_versions.version_number%TYPE;
649 l_max_version_number pqh_budget_versions.version_number%TYPE;
650 l_budget_versions_rec pqh_budget_versions%ROWTYPE;
651 l_budget_unit1_value pqh_budget_versions.budget_unit1_value%TYPE;
652 l_budget_unit1_available pqh_budget_versions.budget_unit1_available%TYPE ;
653 l_budget_unit2_value pqh_budget_versions.budget_unit2_value%TYPE;
654 l_budget_unit2_available pqh_budget_versions.budget_unit2_available%TYPE ;
655 l_budget_unit3_value pqh_budget_versions.budget_unit3_value%TYPE;
656 l_budget_unit3_available pqh_budget_versions.budget_unit3_available%TYPE ;
657
658
659 BEGIN
660
661 hr_utility.set_location('Entering: '||l_proc, 5);
662
663 -- compute max version number
664 OPEN version_number_cur;
665 FETCH version_number_cur INTO l_max_version_number;
666 CLOSE version_number_cur;
667
668 -- current version number of the current budget from the worksheet record
669 l_curr_version_number := p_worksheets_rec.version_number;
670
671 -- fetch the current budget_versions record
672 OPEN budget_version_cur(p_curr_version_number => l_curr_version_number);
673 FETCH budget_version_cur INTO l_budget_versions_rec;
674 CLOSE budget_version_cur;
675
676 l_object_version_number := l_budget_versions_rec.object_version_number;
677
678 -- compute the unit values
679 OPEN units_csr;
680 FETCH units_csr INTO l_budget_unit1_value, l_budget_unit2_value, l_budget_unit3_value;
681 CLOSE units_csr;
682
683 IF p_worksheet_mode_cd = 'O' THEN
684 -- update the same version
685 -- call update API here
686 l_version_number := l_curr_version_number;
687
688 pqh_budget_versions_api.update_budget_version
689 (
690 p_validate => false
691 ,p_budget_version_id => l_budget_versions_rec.budget_version_id
692 ,p_budget_id => l_budget_versions_rec.budget_id
693 ,p_version_number => l_budget_versions_rec.version_number
694 ,p_date_from => p_worksheets_rec.date_from
695 ,p_date_to => p_worksheets_rec.date_to
696 ,p_transfered_to_gl_flag => l_budget_versions_rec.transfered_to_gl_flag
697 ,p_xfer_to_other_apps_cd => l_budget_versions_rec.xfer_to_other_apps_cd
698 ,p_object_version_number => l_object_version_number
699 ,p_budget_unit1_value => l_budget_unit1_value
700 ,p_budget_unit2_value => l_budget_unit2_value
701 ,p_budget_unit3_value => l_budget_unit3_value
702 ,p_budget_unit1_available => l_budget_unit1_available
703 ,p_budget_unit2_available => l_budget_unit2_available
704 ,p_budget_unit3_available => l_budget_unit3_available
705 ,p_effective_date => sysdate
706 );
707
708 -- populate the out variable
709 p_budget_version_id_o := l_budget_versions_rec.budget_version_id;
710
711 ELSIF p_worksheet_mode_cd = 'S' THEN
712 -- this is a first version and a record for this has already been created by the
713 -- budget form.
714 -- call update API here
715 l_version_number := l_curr_version_number;
716
717 pqh_budget_versions_api.update_budget_version
718 (
719 p_validate => false
720 ,p_budget_version_id => l_budget_versions_rec.budget_version_id
721 ,p_budget_id => l_budget_versions_rec.budget_id
722 ,p_version_number => l_budget_versions_rec.version_number
723 ,p_date_from => p_worksheets_rec.date_from
724 ,p_date_to => p_worksheets_rec.date_to
725 ,p_transfered_to_gl_flag => l_budget_versions_rec.transfered_to_gl_flag
726 ,p_xfer_to_other_apps_cd => l_budget_versions_rec.xfer_to_other_apps_cd
727 ,p_object_version_number => l_object_version_number
728 ,p_budget_unit1_value => l_budget_unit1_value
729 ,p_budget_unit2_value => l_budget_unit2_value
730 ,p_budget_unit3_value => l_budget_unit3_value
731 ,p_budget_unit1_available => l_budget_unit1_available
732 ,p_budget_unit2_available => l_budget_unit2_available
733 ,p_budget_unit3_available => l_budget_unit3_available
734 ,p_effective_date => sysdate
735 );
736
737 -- populate the out variable
738 p_budget_version_id_o := l_budget_versions_rec.budget_version_id;
739
740
741 ELSE
742 -- modes new_override and carry_forward , create new version record
743 -- call insert API
744 l_version_number := l_max_version_number + 1;
745
746 pqh_budget_versions_api.create_budget_version
747 (
748 p_validate => false
749 ,p_budget_version_id => p_budget_version_id_o
750 ,p_budget_id => p_budget_id
751 ,p_version_number => l_version_number
752 ,p_date_from => p_worksheets_rec.date_from
753 ,p_date_to => p_worksheets_rec.date_to
754 ,p_transfered_to_gl_flag => 'N'
755 ,p_xfer_to_other_apps_cd => 'N'
756 ,p_object_version_number => l_object_version_number
757 ,p_budget_unit1_value => l_budget_unit1_value
758 ,p_budget_unit2_value => l_budget_unit2_value
759 ,p_budget_unit3_value => l_budget_unit3_value
760 ,p_budget_unit1_available => l_budget_unit1_available
761 ,p_budget_unit2_available => l_budget_unit2_available
762 ,p_budget_unit3_available => l_budget_unit3_available
763 ,p_effective_date => sysdate
764 );
765
766
767 END IF;
768
769 hr_utility.set_location('Current Version Number : '||l_version_number, 6);
770 hr_utility.set_location('Worksheet Id : '||p_worksheets_rec.worksheet_id, 7);
771 hr_utility.set_location('PQH Budget Version out nocopy '||p_budget_version_id_o, 15);
772
773
774 hr_utility.set_location('Leaving:'||l_proc, 1000);
775
776 EXCEPTION
777 WHEN others THEN
778 p_budget_version_id_o := null;
779 -- insert error into log table
780 pqh_process_batch_log.insert_log
781 (
782 p_message_type_cd => 'ERROR',
783 p_message_text => SQLERRM
784 );
785 END populate_budget_versions;
786
787 --------------------------------------------------------------------------------------------------------------
788
789 PROCEDURE populate_budget_details
790 (
791 p_worksheet_details_rec IN pqh_worksheet_details%ROWTYPE,
792 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE,
793 p_worksheet_id IN pqh_worksheets.worksheet_id%type,
794 p_worksheet_mode_cd IN pqh_worksheets.worksheet_mode_cd%TYPE,
795 p_budget_detail_id_o OUT NOCOPY pqh_budget_details.budget_detail_id%TYPE
796 )
797 IS
798 -- local variables and cursors
799
800 l_proc varchar2(72) := g_package||'populate_budget_details';
801 l_version_unit1_value number;
802 l_version_unit2_value number;
803 l_version_unit3_value number;
804 l_budget_unit1_percent number;
805 l_budget_unit2_percent number;
806 l_budget_unit3_percent number;
807 l_object_version_number pqh_budget_details.object_version_number%TYPE;
808
809 CURSOR l_object_version_number_cur IS
810 SELECT object_version_number
811 FROM pqh_budget_details
812 WHERE budget_detail_id = p_worksheet_details_rec.budget_detail_id;
813
814 CURSOR units_csr IS
815 SELECT sum(nvl(BUDGET_UNIT1_VALUE,0)) ,
816 sum(nvl(BUDGET_UNIT2_VALUE,0)) ,
817 sum(nvl(BUDGET_UNIT3_VALUE,0))
818 FROM pqh_worksheet_details
819 WHERE worksheet_id = p_worksheet_id
820 AND nvl(action_cd,'X') = 'B' ;
821
822 BEGIN
823
824 hr_utility.set_location('Entering: '||l_proc, 5);
825 hr_utility.set_location('Global Worksheet Mode : '||g_worksheet_mode_cd, 6);
826
827 -- compute the unit values
828 OPEN units_csr;
829 FETCH units_csr INTO l_version_unit1_value, l_version_unit2_value, l_version_unit3_value;
830 CLOSE units_csr;
831
832 if nvl(l_version_unit1_value,0) >0 then
833 l_budget_unit1_percent := (p_worksheet_details_rec.budget_unit1_value*100)/l_version_unit1_value ;
834 else
835 l_budget_unit1_percent := null;
836 end if;
837 if nvl(l_version_unit2_value,0) >0 then
838 l_budget_unit2_percent := (p_worksheet_details_rec.budget_unit2_value*100)/l_version_unit2_value ;
839 else
840 l_budget_unit2_percent := null;
841 end if;
842 if nvl(l_version_unit3_value,0) >0 then
843 l_budget_unit3_percent := (p_worksheet_details_rec.budget_unit3_value*100)/l_version_unit3_value ;
844 else
845 l_budget_unit3_percent := null;
846 end if;
847 IF p_budget_version_id IS NOT NULL THEN
848 IF g_worksheet_mode_cd = 'O' THEN
849 -- this is update to the same version
850 IF p_worksheet_details_rec.budget_detail_id IS NOT NULL THEN
851 -- update rows where p_worksheet_details_rec.budget_detail_id IS NOT NULL
852 hr_utility.set_location('Budget Detail Id : '||p_worksheet_details_rec.budget_detail_id, 7);
853
854 -- get the object_version_number for this budget_detail_id and pass to update API
855 OPEN l_object_version_number_cur;
856 FETCH l_object_version_number_cur INTO l_object_version_number;
857 CLOSE l_object_version_number_cur;
858
859 hr_utility.set_location('Update API OVN : '||l_object_version_number, 8);
860
861 pqh_budget_details_api.update_budget_detail
862 (
863 p_validate => false
864 ,p_budget_detail_id => p_worksheet_details_rec.budget_detail_id
865 ,p_organization_id => p_worksheet_details_rec.organization_id
866 ,p_job_id => p_worksheet_details_rec.job_id
867 ,p_position_id => p_worksheet_details_rec.position_id
868 ,p_grade_id => p_worksheet_details_rec.grade_id
869 ,p_budget_version_id => p_budget_version_id
870 ,p_budget_unit1_percent => l_budget_unit1_percent
871 ,p_budget_unit1_value_type_cd => p_worksheet_details_rec.budget_unit1_value_type_cd
872 ,p_budget_unit1_value => p_worksheet_details_rec.budget_unit1_value
873 ,p_budget_unit1_available => p_worksheet_details_rec.budget_unit1_available
874 ,p_budget_unit2_percent => l_budget_unit2_percent
875 ,p_budget_unit2_value_type_cd => p_worksheet_details_rec.budget_unit2_value_type_cd
876 ,p_budget_unit2_value => p_worksheet_details_rec.budget_unit2_value
877 ,p_budget_unit2_available => p_worksheet_details_rec.budget_unit2_available
878 ,p_budget_unit3_percent => l_budget_unit3_percent
879 ,p_budget_unit3_value_type_cd => p_worksheet_details_rec.budget_unit3_value_type_cd
880 ,p_budget_unit3_value => p_worksheet_details_rec.budget_unit3_value
881 ,p_budget_unit3_available => p_worksheet_details_rec.budget_unit3_available
882 ,p_object_version_number => l_object_version_number
883 );
884
885 p_budget_detail_id_o := p_worksheet_details_rec.budget_detail_id;
886
887 ELSE
888 -- for others i.e new rows call the insert API
889
890 hr_utility.set_location('Create API in update mode : ', 9);
891
892 pqh_budget_details_api.create_budget_detail
893 (
894 p_validate => false
895 ,p_budget_detail_id => p_budget_detail_id_o
896 ,p_organization_id => p_worksheet_details_rec.organization_id
897 ,p_job_id => p_worksheet_details_rec.job_id
898 ,p_position_id => p_worksheet_details_rec.position_id
899 ,p_grade_id => p_worksheet_details_rec.grade_id
900 ,p_budget_version_id => p_budget_version_id
901 ,p_budget_unit1_percent => l_budget_unit1_percent
902 ,p_budget_unit1_value_type_cd => p_worksheet_details_rec.budget_unit1_value_type_cd
903 ,p_budget_unit1_value => p_worksheet_details_rec.budget_unit1_value
904 ,p_budget_unit1_available => p_worksheet_details_rec.budget_unit1_available
905 ,p_budget_unit2_percent => l_budget_unit2_percent
906 ,p_budget_unit2_value_type_cd => p_worksheet_details_rec.budget_unit2_value_type_cd
907 ,p_budget_unit2_value => p_worksheet_details_rec.budget_unit2_value
908 ,p_budget_unit2_available => p_worksheet_details_rec.budget_unit2_available
909 ,p_budget_unit3_percent => l_budget_unit3_percent
910 ,p_budget_unit3_value_type_cd => p_worksheet_details_rec.budget_unit3_value_type_cd
911 ,p_budget_unit3_value => p_worksheet_details_rec.budget_unit3_value
912 ,p_budget_unit3_available => p_worksheet_details_rec.budget_unit3_available
913 ,p_object_version_number => l_object_version_number
914 );
915
916 END IF;
917 ELSE -- i.e not update mode
918 -- call insert API
919 hr_utility.set_location('Create API in INSERT Mode : ', 10);
920
921 pqh_budget_details_api.create_budget_detail
922 (
923 p_validate => false
924 ,p_budget_detail_id => p_budget_detail_id_o
925 ,p_organization_id => p_worksheet_details_rec.organization_id
926 ,p_job_id => p_worksheet_details_rec.job_id
927 ,p_position_id => p_worksheet_details_rec.position_id
928 ,p_grade_id => p_worksheet_details_rec.grade_id
929 ,p_budget_version_id => p_budget_version_id
930 ,p_budget_unit1_percent => l_budget_unit1_percent
931 ,p_budget_unit1_value_type_cd => p_worksheet_details_rec.budget_unit1_value_type_cd
932 ,p_budget_unit1_value => p_worksheet_details_rec.budget_unit1_value
933 ,p_budget_unit1_available => p_worksheet_details_rec.budget_unit1_available
934 ,p_budget_unit2_percent => l_budget_unit2_percent
935 ,p_budget_unit2_value_type_cd => p_worksheet_details_rec.budget_unit2_value_type_cd
936 ,p_budget_unit2_value => p_worksheet_details_rec.budget_unit2_value
937 ,p_budget_unit2_available => p_worksheet_details_rec.budget_unit2_available
938 ,p_budget_unit3_percent => l_budget_unit3_percent
939 ,p_budget_unit3_value_type_cd => p_worksheet_details_rec.budget_unit3_value_type_cd
940 ,p_budget_unit3_value => p_worksheet_details_rec.budget_unit3_value
941 ,p_budget_unit3_available => p_worksheet_details_rec.budget_unit3_available
942 ,p_object_version_number => l_object_version_number
943 );
944
945 END IF;
946
947 END IF; -- p_budget_version_id is not null
948
949 hr_utility.set_location('PQH Budget Detail ID out nocopy '||p_budget_detail_id_o, 100);
950 hr_utility.set_location('Leaving:'||l_proc, 1000);
951
952 EXCEPTION
953 WHEN others THEN
954
955 p_budget_detail_id_o := null;
956
957 -- insert error into log table
958 pqh_process_batch_log.insert_log
959 (
960 p_message_type_cd => 'ERROR',
961 p_message_text => SQLERRM
962 );
963 END populate_budget_details;
964
965 --------------------------------------------------------------------------------------------------------------
966 PROCEDURE populate_budget_periods
967 (
968 p_worksheet_periods_rec IN pqh_worksheet_periods%ROWTYPE,
969 p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE,
970 p_budget_period_id_o OUT NOCOPY pqh_budget_periods.budget_period_id%TYPE
971 )
972 IS
973 -- local variables and cursors
974
975 l_proc varchar2(72) := g_package||'populate_budget_periods';
976 l_object_version_number pqh_budget_periods.object_version_number%TYPE;
977
978 BEGIN
979
980 hr_utility.set_location('Entering: '||l_proc, 5);
981
982 IF p_budget_detail_id IS NOT NULL THEN
983
984 -- call insert API
985 pqh_budget_periods_api.create_budget_period
986 (
987 p_validate => false
988 ,p_budget_period_id => p_budget_period_id_o
989 ,p_budget_detail_id => p_budget_detail_id
990 ,p_start_time_period_id => p_worksheet_periods_rec.start_time_period_id
991 ,p_end_time_period_id => p_worksheet_periods_rec.end_time_period_id
992 ,p_budget_unit1_percent => p_worksheet_periods_rec.budget_unit1_percent
993 ,p_budget_unit2_percent => p_worksheet_periods_rec.budget_unit2_percent
994 ,p_budget_unit3_percent => p_worksheet_periods_rec.budget_unit3_percent
995 ,p_budget_unit1_value => p_worksheet_periods_rec.budget_unit1_value
996 ,p_budget_unit2_value => p_worksheet_periods_rec.budget_unit2_value
997 ,p_budget_unit3_value => p_worksheet_periods_rec.budget_unit3_value
998 ,p_budget_unit1_value_type_cd => p_worksheet_periods_rec.budget_unit1_value_type_cd
999 ,p_budget_unit2_value_type_cd => p_worksheet_periods_rec.budget_unit2_value_type_cd
1000 ,p_budget_unit3_value_type_cd => p_worksheet_periods_rec.budget_unit3_value_type_cd
1001 ,p_budget_unit1_available => p_worksheet_periods_rec.budget_unit1_available
1002 ,p_budget_unit2_available => p_worksheet_periods_rec.budget_unit2_available
1003 ,p_budget_unit3_available => p_worksheet_periods_rec.budget_unit3_available
1004 ,p_object_version_number => l_object_version_number
1005 );
1006
1007
1008 END IF; -- p_budget_detail_id is not null
1009
1010 hr_utility.set_location('PQH Budget Period ID out nocopy '||p_budget_period_id_o, 100);
1011 hr_utility.set_location('Leaving:'||l_proc, 1000);
1012
1013 EXCEPTION
1014 WHEN others THEN
1015
1016 p_budget_period_id_o := null;
1017
1018 -- insert error into log table
1019 pqh_process_batch_log.insert_log
1020 (
1021 p_message_type_cd => 'ERROR',
1022 p_message_text => SQLERRM
1023 );
1024 END populate_budget_periods;
1025
1026 --------------------------------------------------------------------------------------------------------------
1027
1028 PROCEDURE populate_budget_sets
1029 (
1030 p_worksheet_budget_sets_rec IN pqh_worksheet_budget_sets%ROWTYPE,
1031 p_budget_period_id IN pqh_budget_periods.budget_period_id%TYPE,
1032 p_budget_set_id_o OUT NOCOPY pqh_budget_sets.budget_set_id%TYPE
1033 )
1034 IS
1035 -- local variables and cursors
1036
1037 l_proc varchar2(72) := g_package||'populate_budget_sets';
1038 l_object_version_number pqh_budget_periods.object_version_number%TYPE;
1039
1040 BEGIN
1041
1042 hr_utility.set_location('Entering: '||l_proc, 5);
1043
1044 IF p_budget_period_id IS NOT NULL THEN
1045
1046 -- call insert API
1047 pqh_budget_sets_api.create_budget_set
1048 (
1049 p_validate => false
1050 ,p_budget_set_id => p_budget_set_id_o
1051 ,p_dflt_budget_set_id => p_worksheet_budget_sets_rec.dflt_budget_set_id
1052 ,p_budget_period_id => p_budget_period_id
1053 ,p_budget_unit1_percent => p_worksheet_budget_sets_rec.budget_unit1_percent
1054 ,p_budget_unit2_percent => p_worksheet_budget_sets_rec.budget_unit2_percent
1055 ,p_budget_unit3_percent => p_worksheet_budget_sets_rec.budget_unit3_percent
1056 ,p_budget_unit1_value => p_worksheet_budget_sets_rec.budget_unit1_value
1057 ,p_budget_unit2_value => p_worksheet_budget_sets_rec.budget_unit2_value
1058 ,p_budget_unit3_value => p_worksheet_budget_sets_rec.budget_unit3_value
1059 ,p_budget_unit1_available => p_worksheet_budget_sets_rec.budget_unit1_available
1060 ,p_budget_unit2_available => p_worksheet_budget_sets_rec.budget_unit2_available
1061 ,p_budget_unit3_available => p_worksheet_budget_sets_rec.budget_unit3_available
1062 ,p_object_version_number => l_object_version_number
1063 ,p_budget_unit1_value_type_cd => p_worksheet_budget_sets_rec.budget_unit1_value_type_cd
1064 ,p_budget_unit2_value_type_cd => p_worksheet_budget_sets_rec.budget_unit2_value_type_cd
1065 ,p_budget_unit3_value_type_cd => p_worksheet_budget_sets_rec.budget_unit3_value_type_cd
1066 ,p_effective_date => sysdate
1067 );
1068
1069
1070 END IF; -- p_budget_period_id is not null
1071
1072 hr_utility.set_location('PQH Budget Set ID out nocopy '||p_budget_set_id_o, 100);
1073 hr_utility.set_location('Leaving:'||l_proc, 1000);
1074
1075 EXCEPTION
1076 WHEN others THEN
1077
1078 p_budget_set_id_o := null;
1079
1080 -- insert error into log table
1081 pqh_process_batch_log.insert_log
1082 (
1083 p_message_type_cd => 'ERROR',
1084 p_message_text => SQLERRM
1085 );
1086 END populate_budget_sets;
1087
1088 --------------------------------------------------------------------------------------------------------------
1089 PROCEDURE populate_budget_elements
1090 (
1091 p_worksheet_bdgt_elmnts_rec IN pqh_worksheet_bdgt_elmnts%ROWTYPE,
1092 p_budget_set_id IN pqh_budget_sets.budget_set_id%TYPE,
1093 p_budget_element_id_o OUT NOCOPY pqh_budget_elements.budget_element_id%TYPE
1094 )
1095 IS
1096
1097 -- local variables and cursors
1098
1099 l_proc varchar2(72) := g_package||'populate_budget_elements';
1100 l_object_version_number pqh_budget_periods.object_version_number%TYPE;
1101
1102 BEGIN
1103
1104 hr_utility.set_location('Entering: '||l_proc, 5);
1105
1106 IF p_budget_set_id IS NOT NULL THEN
1107
1108 -- call insert API
1109 pqh_budget_elements_api.create_budget_element
1110 (
1111 p_validate => false
1112 ,p_budget_element_id => p_budget_element_id_o
1113 ,p_budget_set_id => p_budget_set_id
1114 ,p_element_type_id => p_worksheet_bdgt_elmnts_rec.element_type_id
1115 ,p_distribution_percentage => p_worksheet_bdgt_elmnts_rec.distribution_percentage
1116 ,p_object_version_number => l_object_version_number
1117 );
1118
1119 END IF; -- p_budget_set_id is not null
1120
1121 hr_utility.set_location('PQH Budget Element ID out nocopy '||p_budget_element_id_o, 100);
1122 hr_utility.set_location('Leaving:'||l_proc, 1000);
1123
1124 EXCEPTION
1125 WHEN others THEN
1126
1127 p_budget_element_id_o := null;
1128 -- insert error into log table
1129 pqh_process_batch_log.insert_log
1130 (
1131 p_message_type_cd => 'ERROR',
1132 p_message_text => SQLERRM
1133 );
1134 END populate_budget_elements;
1135
1136 --------------------------------------------------------------------------------------------------------------
1137 PROCEDURE populate_budget_fund_srcs
1138 (
1139 p_worksheet_fund_srcs_rec IN pqh_worksheet_fund_srcs%ROWTYPE,
1140 p_budget_element_id IN pqh_budget_elements.budget_element_id%TYPE,
1141 p_budget_fund_src_id_o OUT NOCOPY pqh_budget_fund_srcs.budget_fund_src_id%TYPE
1142 )
1143 IS
1144 -- local variables and cursors
1145
1146 l_proc varchar2(72) := g_package||'populate_budget_fund_srcs';
1147 l_object_version_number pqh_budget_periods.object_version_number%TYPE;
1148
1149 BEGIN
1150
1151 hr_utility.set_location('Entering: '||l_proc, 5);
1152
1153 IF p_budget_element_id IS NOT NULL THEN
1154
1155 -- call insert API
1156 pqh_budget_fund_srcs_api.create_budget_fund_src
1157 (
1158 p_validate => false
1159 ,p_budget_fund_src_id => p_budget_fund_src_id_o
1160 ,p_budget_element_id => p_budget_element_id
1161 ,p_cost_allocation_keyflex_id => p_worksheet_fund_srcs_rec.cost_allocation_keyflex_id
1162 ,p_project_id => p_worksheet_fund_srcs_rec.project_id
1163 ,p_award_id => p_worksheet_fund_srcs_rec.award_id
1164 ,p_task_id => p_worksheet_fund_srcs_rec.task_id
1165 ,p_expenditure_type => p_worksheet_fund_srcs_rec.expenditure_type
1166 ,p_organization_id => p_worksheet_fund_srcs_rec.organization_id
1167 ,p_distribution_percentage => p_worksheet_fund_srcs_rec.distribution_percentage
1168 ,p_object_version_number => l_object_version_number
1169 );
1170
1171 END IF; -- p_budget_element_id is not null
1172
1173 hr_utility.set_location('PQH Budget Fund Src ID out nocopy '||p_budget_fund_src_id_o, 100);
1174 hr_utility.set_location('Leaving:'||l_proc, 1000);
1175
1176 EXCEPTION
1177 WHEN others THEN
1178
1179 p_budget_fund_src_id_o := null;
1180 -- insert error into log table
1181 pqh_process_batch_log.insert_log
1182 (
1183 p_message_type_cd => 'ERROR',
1184 p_message_text => SQLERRM
1185 );
1186 END populate_budget_fund_srcs;
1187
1188 --------------------------------------------------------------------------------------------------------------
1189 PROCEDURE carry_forward_budget
1190 (
1191 p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE,
1192 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE
1193 )
1194 IS
1195 -- local variables and cursors
1196
1197 CURSOR pqh_worksheets_cur(p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE) IS
1198 SELECT *
1199 FROM pqh_worksheets
1200 WHERE worksheet_id = p_worksheet_id;
1201
1202 CURSOR pqh_budget_details_cur (p_curr_budget_version_id IN pqh_budget_details.budget_version_id%TYPE ,
1203 p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE ) IS
1204 SELECT *
1205 FROM pqh_budget_details
1206 WHERE budget_version_id = p_curr_budget_version_id
1207 AND budget_detail_id NOT IN ( SELECT budget_detail_id
1208 FROM pqh_worksheet_details
1209 WHERE worksheet_id = p_worksheet_id
1210 AND NVL(action_cd,'X') = 'B' ) ;
1211
1212 CURSOR pqh_budget_periods_cur (p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE) IS
1213 SELECT *
1214 FROM pqh_budget_periods
1215 WHERE budget_detail_id = p_budget_detail_id;
1216
1217 CURSOR pqh_budget_sets_cur (p_budget_period_id IN pqh_budget_periods.budget_period_id%TYPE) IS
1218 SELECT *
1219 FROM pqh_budget_sets
1220 WHERE budget_period_id = p_budget_period_id;
1221
1222 CURSOR pqh_budget_elements_cur (p_budget_set_id IN pqh_budget_sets.budget_set_id%TYPE) IS
1223 SELECT *
1224 FROM pqh_budget_elements
1225 WHERE budget_set_id = p_budget_set_id;
1226
1227 CURSOR pqh_budget_fund_srcs_cur (p_budget_element_id IN pqh_budget_elements.budget_element_id%TYPE) IS
1228 SELECT *
1229 FROM pqh_budget_fund_srcs
1230 WHERE budget_element_id = p_budget_element_id;
1231
1232 CURSOR current_version_cur (p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE ) IS
1233 SELECT bvr.budget_version_id
1234 FROM pqh_budget_versions bvr, pqh_worksheets wks
1235 WHERE bvr.budget_id = wks.budget_id
1236 AND bvr.version_number = wks.version_number
1237 AND wks.worksheet_id = p_worksheet_id;
1238
1239
1240 l_proc varchar2(72) := g_package||'carry_forward_budget';
1241 l_pqh_worksheets_rec pqh_worksheets%ROWTYPE;
1242 l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
1243 l_pqh_budget_details_rec pqh_budget_details%ROWTYPE;
1244 l_budget_detail_id pqh_budget_details.budget_detail_id%TYPE;
1245 l_pqh_budget_periods_rec pqh_budget_periods%ROWTYPE;
1246 l_budget_period_id pqh_budget_periods.budget_period_id%TYPE;
1247 l_pqh_budget_sets_rec pqh_budget_sets%ROWTYPE;
1248 l_budget_set_id pqh_budget_sets.budget_set_id%TYPE;
1249 l_pqh_budget_elements_rec pqh_budget_elements%ROWTYPE;
1250 l_budget_element_id pqh_budget_elements.budget_element_id%TYPE;
1251 l_pqh_budget_fund_srcs_rec pqh_budget_fund_srcs%ROWTYPE;
1252 l_budget_fund_src_id pqh_budget_fund_srcs.budget_fund_src_id%TYPE;
1253 l_curr_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
1254
1255 BEGIN
1256
1257 hr_utility.set_location('Entering: '||l_proc, 5);
1258
1259 -- get the current version number
1260 OPEN current_version_cur(p_worksheet_id => p_worksheet_id );
1261 FETCH current_version_cur INTO l_curr_budget_version_id;
1262 CLOSE current_version_cur;
1263
1264 hr_utility.set_location('Current Version : '||l_curr_budget_version_id, 6);
1265
1266 -- open the pqh_worksheets_cur
1267 OPEN pqh_worksheets_cur(p_worksheet_id => p_worksheet_id);
1268 LOOP -- loop 1
1269 FETCH pqh_worksheets_cur INTO l_pqh_worksheets_rec;
1270 EXIT WHEN pqh_worksheets_cur%NOTFOUND;
1271
1272 -- open pqh_budget_details_cur
1273 OPEN pqh_budget_details_cur(p_curr_budget_version_id => l_curr_budget_version_id,
1274 p_worksheet_id => l_pqh_worksheets_rec.worksheet_id );
1275 LOOP -- loop 2
1276 FETCH pqh_budget_details_cur INTO l_pqh_budget_details_rec;
1277 EXIT WHEN pqh_budget_details_cur%NOTFOUND;
1278 -- create records in pqh_budget_details
1279 carry_forward_budget_details
1280 (
1281 p_pqh_budget_details_rec => l_pqh_budget_details_rec,
1282 p_budget_version_id => p_budget_version_id,
1283 p_budget_detail_id_o => l_budget_detail_id
1284 );
1285
1286 -- open pqh_budget_periods_cur
1287 OPEN pqh_budget_periods_cur(p_budget_detail_id => l_pqh_budget_details_rec.budget_detail_id);
1288 LOOP -- loop 3
1289 FETCH pqh_budget_periods_cur INTO l_pqh_budget_periods_rec;
1290 EXIT WHEN pqh_budget_periods_cur%NOTFOUND;
1291 -- create records in pqh_budget_periods
1292 carry_forward_budget_periods
1293 (
1294 p_pqh_budget_periods_rec => l_pqh_budget_periods_rec,
1295 p_budget_detail_id => l_budget_detail_id,
1296 p_budget_period_id_o => l_budget_period_id
1297 );
1298
1299 -- open pqh_budget_sets_cur
1300 OPEN pqh_budget_sets_cur(p_budget_period_id => l_pqh_budget_periods_rec.budget_period_id);
1301 LOOP -- loop 4
1302 FETCH pqh_budget_sets_cur INTO l_pqh_budget_sets_rec;
1303 EXIT WHEN pqh_budget_sets_cur%NOTFOUND;
1304 -- create records in pqh_budget_sets
1305 carry_forward_budget_sets
1306 (
1307 p_pqh_budget_sets_rec => l_pqh_budget_sets_rec,
1308 p_budget_period_id => l_budget_period_id,
1309 p_budget_set_id_o => l_budget_set_id
1310 );
1311
1312 -- open pqh_budget_elements_cur
1313 OPEN pqh_budget_elements_cur(p_budget_set_id => l_pqh_budget_sets_rec.budget_set_id);
1314 LOOP -- loop 5
1315 FETCH pqh_budget_elements_cur INTO l_pqh_budget_elements_rec;
1316 EXIT WHEN pqh_budget_elements_cur%NOTFOUND;
1317 -- create records in pqh_budget_elements
1318 carry_forward_budget_elements
1319 (
1320 p_pqh_budget_elements_rec => l_pqh_budget_elements_rec,
1321 p_budget_set_id => l_budget_set_id,
1322 p_budget_element_id_o => l_budget_element_id
1323 );
1324
1325 -- open pqh_budget_fund_srcs_cur
1326 OPEN pqh_budget_fund_srcs_cur(p_budget_element_id => l_pqh_budget_elements_rec.budget_element_id);
1327 LOOP -- loop 6
1328 FETCH pqh_budget_fund_srcs_cur INTO l_pqh_budget_fund_srcs_rec;
1329 EXIT WHEN pqh_budget_fund_srcs_cur%NOTFOUND;
1330 -- create records in pqh_budget_fund_srcs
1331 carry_forward_budget_fund_srcs
1332 (
1333 p_pqh_budget_fund_srcs_rec => l_pqh_budget_fund_srcs_rec,
1334 p_budget_element_id => l_budget_element_id,
1335 p_budget_fund_src_id_o => l_budget_fund_src_id
1336 );
1337
1338
1339 END LOOP; -- loop 6
1340 CLOSE pqh_budget_fund_srcs_cur;
1341
1342 END LOOP; -- loop 5
1343 CLOSE pqh_budget_elements_cur;
1344
1345 END LOOP; -- loop 4
1346 CLOSE pqh_budget_sets_cur;
1347
1348 END LOOP; -- loop 3
1349 CLOSE pqh_budget_periods_cur;
1350
1351 END LOOP; -- loop 2
1352 CLOSE pqh_budget_details_cur;
1353
1354 END LOOP; -- loop 1
1355 CLOSE pqh_worksheets_cur;
1356
1357
1358 hr_utility.set_location('Leaving:'||l_proc, 1000);
1359
1360 EXCEPTION
1361 WHEN others THEN
1362 raise;
1363 END;
1364
1365 --------------------------------------------------------------------------------------------------------------
1366
1367 PROCEDURE carry_forward_budget_details
1368 (
1369 p_pqh_budget_details_rec IN pqh_budget_details%ROWTYPE,
1370 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE,
1371 p_budget_detail_id_o OUT NOCOPY pqh_budget_details.budget_detail_id%TYPE
1372 )
1373 IS
1374 -- local variables and cursors
1375
1376 l_proc varchar2(72) := g_package||'carry_forward_budget_details';
1377 l_object_version_number pqh_budget_details.object_version_number%TYPE;
1378
1379
1380 BEGIN
1381
1382 hr_utility.set_location('Entering: '||l_proc, 5);
1383
1384 IF p_budget_version_id IS NOT NULL THEN
1385
1386 -- call insert API
1387 pqh_budget_details_api.create_budget_detail
1388 (
1389 p_validate => false
1390 ,p_budget_detail_id => p_budget_detail_id_o
1391 ,p_organization_id => p_pqh_budget_details_rec.organization_id
1392 ,p_job_id => p_pqh_budget_details_rec.job_id
1393 ,p_position_id => p_pqh_budget_details_rec.position_id
1394 ,p_grade_id => p_pqh_budget_details_rec.grade_id
1395 ,p_budget_version_id => p_budget_version_id
1396 ,p_budget_unit1_percent => p_pqh_budget_details_rec.budget_unit1_percent
1397 ,p_budget_unit1_value_type_cd => p_pqh_budget_details_rec.budget_unit1_value_type_cd
1398 ,p_budget_unit1_value => p_pqh_budget_details_rec.budget_unit1_value
1399 ,p_budget_unit1_available => p_pqh_budget_details_rec.budget_unit1_available
1400 ,p_budget_unit2_percent => p_pqh_budget_details_rec.budget_unit2_percent
1401 ,p_budget_unit2_value_type_cd => p_pqh_budget_details_rec.budget_unit2_value_type_cd
1402 ,p_budget_unit2_value => p_pqh_budget_details_rec.budget_unit2_value
1403 ,p_budget_unit2_available => p_pqh_budget_details_rec.budget_unit2_available
1404 ,p_budget_unit3_percent => p_pqh_budget_details_rec.budget_unit3_percent
1405 ,p_budget_unit3_value_type_cd => p_pqh_budget_details_rec.budget_unit3_value_type_cd
1406 ,p_budget_unit3_value => p_pqh_budget_details_rec.budget_unit3_value
1407 ,p_budget_unit3_available => p_pqh_budget_details_rec.budget_unit3_available
1408 ,p_object_version_number => l_object_version_number
1409 );
1410
1411
1412 END IF; -- p_budget_version_id is not null
1413
1414 hr_utility.set_location('PQH Budget Detail ID out nocopy '||p_budget_detail_id_o, 100);
1415 hr_utility.set_location('Leaving:'||l_proc, 1000);
1416
1417 EXCEPTION
1418 WHEN others THEN
1419 p_budget_detail_id_o := null;
1420 raise;
1421 END carry_forward_budget_details;
1422
1423 --------------------------------------------------------------------------------------------------------------
1424 PROCEDURE carry_forward_budget_periods
1425 (
1426 p_pqh_budget_periods_rec IN pqh_budget_periods%ROWTYPE,
1427 p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE,
1428 p_budget_period_id_o OUT NOCOPY pqh_budget_periods.budget_period_id%TYPE
1429 )
1430 IS
1431 -- local variables and cursors
1432
1433 l_proc varchar2(72) := g_package||'carry_forward_budget_periods';
1434 l_object_version_number pqh_budget_periods.object_version_number%TYPE;
1435
1436 BEGIN
1437
1438 hr_utility.set_location('Entering: '||l_proc, 5);
1439
1440 IF p_budget_detail_id IS NOT NULL THEN
1441
1442 -- call insert API
1443 pqh_budget_periods_api.create_budget_period
1444 (
1445 p_validate => false
1446 ,p_budget_period_id => p_budget_period_id_o
1447 ,p_budget_detail_id => p_budget_detail_id
1448 ,p_start_time_period_id => p_pqh_budget_periods_rec.start_time_period_id
1449 ,p_end_time_period_id => p_pqh_budget_periods_rec.end_time_period_id
1450 ,p_budget_unit1_percent => p_pqh_budget_periods_rec.budget_unit1_percent
1451 ,p_budget_unit2_percent => p_pqh_budget_periods_rec.budget_unit2_percent
1452 ,p_budget_unit3_percent => p_pqh_budget_periods_rec.budget_unit3_percent
1453 ,p_budget_unit1_value => p_pqh_budget_periods_rec.budget_unit1_value
1454 ,p_budget_unit2_value => p_pqh_budget_periods_rec.budget_unit2_value
1455 ,p_budget_unit3_value => p_pqh_budget_periods_rec.budget_unit3_value
1456 ,p_budget_unit1_value_type_cd => p_pqh_budget_periods_rec.budget_unit1_value_type_cd
1457 ,p_budget_unit2_value_type_cd => p_pqh_budget_periods_rec.budget_unit2_value_type_cd
1458 ,p_budget_unit3_value_type_cd => p_pqh_budget_periods_rec.budget_unit3_value_type_cd
1459 ,p_budget_unit1_available => p_pqh_budget_periods_rec.budget_unit1_available
1460 ,p_budget_unit2_available => p_pqh_budget_periods_rec.budget_unit2_available
1461 ,p_budget_unit3_available => p_pqh_budget_periods_rec.budget_unit3_available
1462 ,p_object_version_number => l_object_version_number
1463 );
1464
1465
1466 END IF; -- p_budget_detail_id is not null
1467
1468 hr_utility.set_location('PQH Budget Period ID out nocopy '||p_budget_period_id_o, 100);
1469 hr_utility.set_location('Leaving:'||l_proc, 1000);
1470
1471 EXCEPTION
1472 WHEN others THEN
1473 p_budget_period_id_o := null;
1474 raise;
1475 END carry_forward_budget_periods;
1476
1477 --------------------------------------------------------------------------------------------------------------
1478
1479 PROCEDURE carry_forward_budget_sets
1480 (
1481 p_pqh_budget_sets_rec IN pqh_budget_sets%ROWTYPE,
1482 p_budget_period_id IN pqh_budget_periods.budget_period_id%TYPE,
1483 p_budget_set_id_o OUT NOCOPY pqh_budget_sets.budget_set_id%TYPE
1484 )
1485 IS
1486 -- local variables and cursors
1487
1488 l_proc varchar2(72) := g_package||'carry_forward_budget_sets';
1489 l_object_version_number pqh_budget_periods.object_version_number%TYPE;
1490
1491 BEGIN
1492
1493 hr_utility.set_location('Entering: '||l_proc, 5);
1494
1495 IF p_budget_period_id IS NOT NULL THEN
1496
1497 -- call insert API
1498 pqh_budget_sets_api.create_budget_set
1499 (
1500 p_validate => false
1501 ,p_budget_set_id => p_budget_set_id_o
1502 ,p_dflt_budget_set_id => p_pqh_budget_sets_rec.dflt_budget_set_id
1503 ,p_budget_period_id => p_budget_period_id
1504 ,p_budget_unit1_percent => p_pqh_budget_sets_rec.budget_unit1_percent
1505 ,p_budget_unit2_percent => p_pqh_budget_sets_rec.budget_unit2_percent
1506 ,p_budget_unit3_percent => p_pqh_budget_sets_rec.budget_unit3_percent
1507 ,p_budget_unit1_value => p_pqh_budget_sets_rec.budget_unit1_value
1508 ,p_budget_unit2_value => p_pqh_budget_sets_rec.budget_unit2_value
1509 ,p_budget_unit3_value => p_pqh_budget_sets_rec.budget_unit3_value
1510 ,p_budget_unit1_available => p_pqh_budget_sets_rec.budget_unit1_available
1511 ,p_budget_unit2_available => p_pqh_budget_sets_rec.budget_unit2_available
1512 ,p_budget_unit3_available => p_pqh_budget_sets_rec.budget_unit3_available
1513 ,p_object_version_number => l_object_version_number
1514 ,p_budget_unit1_value_type_cd => p_pqh_budget_sets_rec.budget_unit1_value_type_cd
1515 ,p_budget_unit2_value_type_cd => p_pqh_budget_sets_rec.budget_unit2_value_type_cd
1516 ,p_budget_unit3_value_type_cd => p_pqh_budget_sets_rec.budget_unit3_value_type_cd
1517 ,p_effective_date => sysdate
1518 );
1519
1520 END IF; -- p_budget_period_id is not null
1521
1522 hr_utility.set_location('PQH Budget Set ID out nocopy '||p_budget_set_id_o, 100);
1523 hr_utility.set_location('Leaving:'||l_proc, 1000);
1524
1525 EXCEPTION
1526 WHEN others THEN
1527 p_budget_set_id_o := null;
1528 raise;
1529 END carry_forward_budget_sets;
1530
1531 --------------------------------------------------------------------------------------------------------------
1532 PROCEDURE carry_forward_budget_elements
1533 (
1534 p_pqh_budget_elements_rec IN pqh_budget_elements%ROWTYPE,
1535 p_budget_set_id IN pqh_budget_sets.budget_set_id%TYPE,
1536 p_budget_element_id_o OUT NOCOPY pqh_budget_elements.budget_element_id%TYPE
1537 )
1538 IS
1539
1540 -- local variables and cursors
1541
1542 l_proc varchar2(72) := g_package||'carry_forward_budget_elements';
1543 l_object_version_number pqh_budget_periods.object_version_number%TYPE;
1544
1545 BEGIN
1546
1547 hr_utility.set_location('Entering: '||l_proc, 5);
1548
1549 IF p_budget_set_id IS NOT NULL THEN
1550
1551 -- call insert API
1552 pqh_budget_elements_api.create_budget_element
1553 (
1554 p_validate => false
1555 ,p_budget_element_id => p_budget_element_id_o
1556 ,p_budget_set_id => p_budget_set_id
1557 ,p_element_type_id => p_pqh_budget_elements_rec.element_type_id
1558 ,p_distribution_percentage => p_pqh_budget_elements_rec.distribution_percentage
1559 ,p_object_version_number => l_object_version_number
1560 );
1561
1562 END IF; -- p_budget_set_id is not null
1563
1564 hr_utility.set_location('PQH Budget Element ID out nocopy '||p_budget_element_id_o, 100);
1565 hr_utility.set_location('Leaving:'||l_proc, 1000);
1566
1567 EXCEPTION
1568 WHEN others THEN
1569 p_budget_element_id_o := null;
1570 raise;
1571 END carry_forward_budget_elements;
1572
1573 --------------------------------------------------------------------------------------------------------------
1574 PROCEDURE carry_forward_budget_fund_srcs
1575 (
1576 p_pqh_budget_fund_srcs_rec IN pqh_budget_fund_srcs%ROWTYPE,
1577 p_budget_element_id IN pqh_budget_elements.budget_element_id%TYPE,
1578 p_budget_fund_src_id_o OUT NOCOPY pqh_budget_fund_srcs.budget_fund_src_id%TYPE
1579 )
1580 IS
1581 -- local variables and cursors
1582
1583 l_proc varchar2(72) := g_package||'carry_forward_budget_fund_srcs';
1584 l_object_version_number pqh_budget_periods.object_version_number%TYPE;
1585
1586 BEGIN
1587
1588 hr_utility.set_location('Entering: '||l_proc, 5);
1589
1590 IF p_budget_element_id IS NOT NULL THEN
1591
1592 -- call insert API
1593 pqh_budget_fund_srcs_api.create_budget_fund_src
1594 (
1595 p_validate => false
1596 ,p_budget_fund_src_id => p_budget_fund_src_id_o
1597 ,p_budget_element_id => p_budget_element_id
1598 ,p_cost_allocation_keyflex_id => p_pqh_budget_fund_srcs_rec.cost_allocation_keyflex_id
1599 ,p_project_id => p_pqh_budget_fund_srcs_rec.project_id
1600 ,p_award_id => p_pqh_budget_fund_srcs_rec.award_id
1601 ,p_task_id => p_pqh_budget_fund_srcs_rec.task_id
1602 ,p_expenditure_type => p_pqh_budget_fund_srcs_rec.expenditure_type
1603 ,p_organization_id => p_pqh_budget_fund_srcs_rec.organization_id
1604 ,p_distribution_percentage => p_pqh_budget_fund_srcs_rec.distribution_percentage
1605 ,p_object_version_number => l_object_version_number
1606 );
1607
1608 END IF; -- p_budget_element_id is not null
1609
1610 hr_utility.set_location('PQH Budget Fund Src ID out nocopy '||p_budget_fund_src_id_o, 100);
1611 hr_utility.set_location('Leaving:'||l_proc, 1000);
1612
1613 EXCEPTION
1614 WHEN others THEN
1615 p_budget_fund_src_id_o := null;
1616 raise;
1617 END carry_forward_budget_fund_srcs;
1618
1619 --------------------------------------------------------------------------------------------------------------
1620
1621 PROCEDURE delete_child_rows
1622 (
1623 p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE
1624 )
1625 IS
1626
1627 -- local variables and cursors
1628
1629 CURSOR budget_period_id_cur IS
1630 SELECT bpr.budget_period_id
1631 FROM pqh_worksheets wks, pqh_worksheet_details wdt ,pqh_budget_periods bpr
1632 WHERE wks.worksheet_id = wdt.worksheet_id
1633 AND wdt.action_cd = 'B'
1634 AND wdt.budget_detail_id = bpr.budget_detail_id
1635 AND wks.worksheet_id = p_worksheet_id;
1636
1637 CURSOR budget_set_id_cur IS
1638 SELECT bst.budget_set_id
1639 FROM pqh_budget_sets bst, pqh_budget_periods bpr, pqh_budget_details bdt,
1640 pqh_worksheet_details wdt , pqh_worksheets wks
1641 WHERE bst.budget_period_id = bpr.budget_period_id
1642 AND bpr.budget_detail_id = wdt.budget_detail_id
1643 AND wks.worksheet_id = wdt.worksheet_id
1644 AND wdt.action_cd = 'B'
1645 AND wks.worksheet_id = p_worksheet_id;
1646
1647 CURSOR budget_element_id_cur IS
1648 SELECT bel.budget_element_id
1649 FROM pqh_budget_elements bel, pqh_budget_sets bst,
1650 pqh_budget_periods bpr,
1651 pqh_worksheet_details wdt , pqh_worksheets wks
1652 WHERE bel.budget_set_id = bst.budget_set_id
1653 AND bst.budget_period_id = bpr.budget_period_id
1654 AND bpr.budget_detail_id = wdt.budget_detail_id
1655 AND wks.worksheet_id = wdt.worksheet_id
1656 AND wdt.action_cd = 'B'
1657 AND wks.worksheet_id = p_worksheet_id;
1658
1659 CURSOR budget_fund_src_id_cur IS
1660 SELECT bfs.budget_fund_src_id
1661 FROM pqh_budget_fund_srcs bfs, pqh_budget_elements bel, pqh_budget_sets bst,
1662 pqh_budget_periods bpr,
1663 pqh_worksheet_details wdt , pqh_worksheets wks
1664 WHERE bfs.budget_element_id = bel.budget_element_id
1665 AND bel.budget_set_id = bst.budget_set_id
1666 AND bst.budget_period_id = bpr.budget_period_id
1667 AND bpr.budget_detail_id = wdt.budget_detail_id
1668 AND wks.worksheet_id = wdt.worksheet_id
1669 AND wdt.action_cd = 'B'
1670 AND wks.worksheet_id = p_worksheet_id;
1671
1672 l_proc varchar2(72) := g_package||'delete_child_rows';
1673 l_budget_period_id pqh_budget_periods.budget_period_id%TYPE;
1674 l_budget_set_id pqh_budget_sets.budget_set_id%TYPE;
1675 l_budget_element_id pqh_budget_elements.budget_element_id%TYPE;
1676 l_budget_fund_src_id pqh_budget_fund_srcs.budget_fund_src_id%TYPE;
1677
1678
1679 BEGIN
1680
1681 hr_utility.set_location('Entering: '||l_proc, 5);
1682
1683 -- delete from pqh_budget_fund_srcs
1684
1685 OPEN budget_fund_src_id_cur;
1686 LOOP
1687 FETCH budget_fund_src_id_cur INTO l_budget_fund_src_id;
1688 EXIT WHEN budget_fund_src_id_cur%NOTFOUND;
1689 DELETE from pqh_budget_fund_srcs
1690 WHERE budget_fund_src_id = l_budget_fund_src_id;
1691 END LOOP;
1692 CLOSE budget_fund_src_id_cur;
1693
1694 -- delete from pqh_budget_elements
1695
1696 OPEN budget_element_id_cur;
1697 LOOP
1698 FETCH budget_element_id_cur INTO l_budget_element_id;
1699 EXIT WHEN budget_element_id_cur%NOTFOUND;
1700 DELETE from pqh_budget_elements
1701 WHERE budget_element_id = l_budget_element_id;
1702 END LOOP;
1703 CLOSE budget_element_id_cur;
1704
1705 -- delete from pqh_budget_sets
1706
1707 OPEN budget_set_id_cur;
1708 LOOP
1709 FETCH budget_set_id_cur INTO l_budget_set_id;
1710 EXIT WHEN budget_set_id_cur%NOTFOUND;
1711 DELETE from pqh_budget_sets
1712 WHERE budget_set_id = l_budget_set_id;
1713 END LOOP;
1714 CLOSE budget_set_id_cur;
1715
1716 -- delete from pqh_budget_periods
1717 OPEN budget_period_id_cur;
1718 LOOP
1719 FETCH budget_period_id_cur INTO l_budget_period_id;
1720 EXIT WHEN budget_period_id_cur%NOTFOUND;
1721 DELETE from pqh_budget_periods
1722 WHERE budget_period_id = l_budget_period_id;
1723 END LOOP;
1724 CLOSE budget_period_id_cur;
1725
1726
1727 /*
1728 we update pqh_budget_details and so don't delete due to foreign key constraints
1729
1730 DELETE FROM pqh_budget_details
1731 WHERE budget_detail_id IN (
1732 SELECT wdt.budget_detail_id
1733 FROM pqh_worksheet_details wdt , pqh_worksheets wks
1734 WHERE wks.worksheet_id = wdt.worksheet_id
1735 AND wdt.budget_detail_id IS NOT NULL
1736 AND NVL(wdt.action_cd,'X') = 'B'
1737 AND wks.worksheet_id = p_worksheet_id
1738 );
1739 */
1740 hr_utility.set_location('Leaving:'||l_proc, 1000);
1741
1742 EXCEPTION
1743 WHEN others THEN
1744 raise;
1745 END;
1746
1747
1748 --------------------------------------------------------------------------------------------------------------
1749 PROCEDURE check_valid_mode
1750 (
1751 p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE
1752 )
1753 IS
1754 /*
1755 This procedure checks if the worksheet_mode is valid else it will give an
1756 error message. This will populate the global mode variable
1757 Now we only have 3 modes ( 02/16/2000 )
1758 So we will have to determine internally the 4th mode which is W
1759 check that the worksheet has not been already applied i.e worksheet_status <> APPLIED
1760 */
1761
1762 -- local variables and cursors
1763
1764 CURSOR pqh_worksheets_cur(p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE) IS
1765 SELECT *
1766 FROM pqh_worksheets
1767 WHERE worksheet_id = p_worksheet_id;
1768
1769 CURSOR pqh_budget_details_cur(p_budget_version_id IN pqh_worksheets.budget_version_id%TYPE) is
1770 SELECT count(*)
1771 FROM pqh_budget_details
1772 WHERE budget_version_id = p_budget_version_id;
1773
1774
1775 l_pqh_worksheets_rec pqh_worksheets%ROWTYPE;
1776 l_proc varchar2(72) := g_package||'check_valid_mode';
1777 l_budget_details_count number;
1778 l_worksheet_status pqh_worksheets.transaction_status%TYPE;
1779
1780
1781 BEGIN
1782 hr_utility.set_location('Entering: '||l_proc, 5);
1783
1784 hr_utility.set_location('Worksheet ID: '||p_worksheet_id, 5);
1785
1786 -- open pqh_worksheets_cur
1787 OPEN pqh_worksheets_cur (p_worksheet_id => p_worksheet_id);
1788 FETCH pqh_worksheets_cur INTO l_pqh_worksheets_rec;
1789 CLOSE pqh_worksheets_cur;
1790
1791 -- populate the global mode_cd variable
1792 g_worksheet_mode_cd := l_pqh_worksheets_rec.worksheet_mode_cd;
1793
1794 -- check if wks already applied then abort the program
1795 l_worksheet_status := l_pqh_worksheets_rec.transaction_status;
1796 IF nvl(l_worksheet_status,'X') = 'APPLIED' THEN
1797 hr_utility.set_message(8302,'PQH_WKS_APPLIED');
1798 hr_utility.raise_error;
1799 END IF;
1800
1801 hr_utility.set_location('Worksheet Mode : '||g_worksheet_mode_cd, 6);
1802
1803 IF l_pqh_worksheets_rec.worksheet_mode_cd = 'S' THEN
1804 -- first version no carry forward i.e NEW or
1805 -- existing version with no carry forward i.e W
1806
1807 /*
1808 since this is the first version, there should be no records in pqh_budget_details with the
1809 current budget_version_id.
1810 */
1811 OPEN pqh_budget_details_cur(p_budget_version_id => l_pqh_worksheets_rec.budget_version_id);
1812 FETCH pqh_budget_details_cur INTO l_budget_details_count;
1813 CLOSE pqh_budget_details_cur;
1814
1815 IF l_budget_details_count <> 0 THEN
1816
1817 /*
1818 This is the 'W' mode ie new worksheet
1819 */
1820
1821 g_worksheet_mode_cd := 'W';
1822
1823 ELSE
1824
1825 g_worksheet_mode_cd := 'S';
1826
1827 /*
1828 incorrect mode passed , give error
1829 New mode cannot have budget_detail records
1830
1831 hr_utility.set_message(8302,'PQH_INVALID_NEW_MODE');
1832 hr_utility.raise_error;
1833 */
1834
1835 END IF;
1836
1837 ELSIF l_pqh_worksheets_rec.worksheet_mode_cd = 'W' THEN
1838 -- new version with no carry forward i.e NEW_OVERRIDE
1839 -- this case is not used from 02/16/2000
1840
1841 /*
1842 since this is new override , there must exist atleast one record in budget_detail
1843 for the current version.
1844 */
1845 OPEN pqh_budget_details_cur(p_budget_version_id => l_pqh_worksheets_rec.budget_version_id);
1846 FETCH pqh_budget_details_cur INTO l_budget_details_count;
1847 CLOSE pqh_budget_details_cur;
1848
1849 IF l_budget_details_count = 0 THEN
1850
1851 /*
1852 incorrect mode passed , give error
1853 New override mode must have budget_detail records
1854 */
1855
1856 hr_utility.set_message(8302,'PQH_INVALID_OVERRIDE_MODE');
1857 hr_utility.raise_error;
1858
1859 ELSE
1860
1861 g_worksheet_mode_cd := 'W';
1862
1863 END IF;
1864
1865 ELSIF l_pqh_worksheets_rec.worksheet_mode_cd = 'N' THEN
1866 -- edit existing version and create a new version
1867 -- with carry forward i.e EDIT_NEW
1868
1869 /*
1870 for carry forward , there must be an existing version in budgets table
1871 */
1872 OPEN pqh_budget_details_cur(p_budget_version_id => l_pqh_worksheets_rec.budget_version_id);
1873 FETCH pqh_budget_details_cur INTO l_budget_details_count;
1874 CLOSE pqh_budget_details_cur;
1875
1876 IF l_budget_details_count = 0 THEN
1877
1878 /*
1879 incorrect mode passed , give error
1880 Carry forward mode must have budget_detail records
1881 */
1882
1883 hr_utility.set_message(8302,'PQH_INVALID_CARRY_MODE');
1884 hr_utility.raise_error;
1885
1886 ELSE
1887
1888 g_worksheet_mode_cd := 'N';
1889
1890 END IF;
1891
1892
1893 ELSIF l_pqh_worksheets_rec.worksheet_mode_cd = 'O' THEN
1894 -- edit existing version and update the same version
1895 -- with carry forward i.e EDIT_UPDATE
1896 /*
1897 Check if record exists in budget_details for this mode
1898 */
1899
1900 OPEN pqh_budget_details_cur(p_budget_version_id => l_pqh_worksheets_rec.budget_version_id);
1901 FETCH pqh_budget_details_cur INTO l_budget_details_count;
1902 CLOSE pqh_budget_details_cur;
1903
1904 IF l_budget_details_count = 0 THEN
1905
1906 /*
1907 incorrect mode passed , give error
1908 Carry forward mode must have budget_detail records
1909 */
1910
1911 hr_utility.set_message(8302,'PQH_INVALID_UPDATE_MODE');
1912 hr_utility.raise_error;
1913
1914 ELSE
1915
1916 g_worksheet_mode_cd := 'O';
1917
1918 END IF;
1919
1920 ELSE
1921 -- invalid mode code
1922 hr_utility.set_location('Invalid Worksheet Mode : '||g_worksheet_mode_cd, 7);
1923 hr_utility.set_message(8302,'PQH_INVALID_WORKSHEET_MODE_CD');
1924 hr_utility.raise_error;
1925 END IF;
1926
1927 hr_utility.set_location('Leaving:'||l_proc, 1000);
1928
1929 EXCEPTION
1930 WHEN others THEN
1931 raise;
1932 END;
1933 --------------------------------------------------------------------------------------------------------------
1934 PROCEDURE populate_globals
1935 (
1936 p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE
1937 ) IS
1938
1939 /*
1940 This procedure will populate all the global variables.
1941 */
1942
1943 l_proc varchar2(72) := g_package||'populate_globals';
1944 l_budgets_rec pqh_budgets%ROWTYPE;
1945 l_worksheets_rec pqh_worksheets%ROWTYPE;
1946
1947 CURSOR csr_budget_rec IS
1948 SELECT *
1949 FROM pqh_budgets
1950 WHERE budget_id =
1951 (
1952 SELECT b.budget_id
1953 FROM pqh_budgets b, pqh_worksheets wks
1954 WHERE wks.worksheet_id = p_worksheet_id
1955 AND wks.budget_id = b.budget_id
1956 );
1957
1958 CURSOR csr_worksheet_rec IS
1959 SELECT *
1960 FROM pqh_worksheets
1961 WHERE worksheet_id = p_worksheet_id ;
1962
1963 CURSOR csr_table_route (p_table_alias IN varchar2 )IS
1964 SELECT table_route_id
1965 FROM pqh_table_route
1966 WHERE table_alias = p_table_alias;
1967
1968 BEGIN
1969
1970 hr_utility.set_location('Entering:'||l_proc, 5);
1971
1972 -- get budget units
1973 OPEN csr_budget_rec;
1974 FETCH csr_budget_rec INTO l_budgets_rec;
1975 CLOSE csr_budget_rec;
1976
1977 g_budgeted_entity_cd := l_budgets_rec.budgeted_entity_cd;
1978
1979 hr_utility.set_location('budgeted_entity_cd: '||g_budgeted_entity_cd, 21);
1980
1981
1982 -- get worksheet mode
1983 OPEN csr_worksheet_rec;
1984 FETCH csr_worksheet_rec INTO l_worksheets_rec;
1985 CLOSE csr_worksheet_rec;
1986
1987 g_worksheet_name := l_worksheets_rec.worksheet_name;
1988 g_worksheet_id := p_worksheet_id;
1989 g_transaction_category_id := l_worksheets_rec.wf_transaction_category_id;
1990
1991 hr_utility.set_location('worksheet_name: '||g_worksheet_name, 30);
1992 hr_utility.set_location('worksheet_id: '||g_worksheet_id, 40);
1993 hr_utility.set_location('g_transaction_category_id: '||g_transaction_category_id, 45);
1994
1995 -- get table_route_id for all the 7 worksheet tables
1996
1997 -- table_route_id for pqh_worksheets
1998 OPEN csr_table_route (p_table_alias => 'WKS');
1999 FETCH csr_table_route INTO g_table_route_id_wks;
2000 CLOSE csr_table_route;
2001
2002 -- table_route_id for pqh_worksheet_details
2003 OPEN csr_table_route (p_table_alias => 'WDT');
2004 FETCH csr_table_route INTO g_table_route_id_wdt;
2005 CLOSE csr_table_route;
2006
2007 -- table_route_id for pqh_worksheet_periods
2008 OPEN csr_table_route (p_table_alias => 'WPR');
2009 FETCH csr_table_route INTO g_table_route_id_wpr;
2010 CLOSE csr_table_route;
2011
2012 -- table_route_id for pqh_worksheet_budget_sets
2013 OPEN csr_table_route (p_table_alias => 'WST');
2014 FETCH csr_table_route INTO g_table_route_id_wst;
2015 CLOSE csr_table_route;
2016
2017 -- table_route_id for pqh_worksheet_bdgt_elmnts
2018 OPEN csr_table_route (p_table_alias => 'WEL');
2019 FETCH csr_table_route INTO g_table_route_id_wel;
2020 CLOSE csr_table_route;
2021
2022 -- table_route_id for pqh_worksheet_fund_srcs
2023 OPEN csr_table_route (p_table_alias => 'WFS');
2024 FETCH csr_table_route INTO g_table_route_id_wfs;
2025 CLOSE csr_table_route;
2026
2027 hr_utility.set_location('g_table_route_id_wdt: '||g_table_route_id_wdt, 50);
2028 hr_utility.set_location('g_table_route_id_wpr: '||g_table_route_id_wpr, 60);
2029 hr_utility.set_location('g_table_route_id_wst: '||g_table_route_id_wst, 70);
2030 hr_utility.set_location('g_table_route_id_wel: '||g_table_route_id_wel, 80);
2031 hr_utility.set_location('g_table_route_id_wfs: '||g_table_route_id_wfs, 90);
2032
2033 hr_utility.set_location('Leaving:'||l_proc, 1000);
2034
2035 EXCEPTION
2036 WHEN OTHERS THEN
2037 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2038 hr_utility.set_message_token('ROUTINE', l_proc);
2039 hr_utility.set_message_token('REASON', SQLERRM);
2040 -- end log and halt the program here
2041 raise g_error_exception;
2042
2043 END populate_globals;
2044 --------------------------------------------------------------------------------------------------------------
2045 PROCEDURE set_wks_log_context
2046 (
2047 p_worksheet_detail_id IN pqh_worksheet_details.worksheet_detail_id%TYPE,
2048 p_log_context OUT NOCOPY pqh_process_log.log_context%TYPE
2049 ) IS
2050
2051 /*
2052 This procedure will set the log_context at wks detail level
2053
2054 Delegated Record -> Display Organization Name
2055 Budgeted Record -> Display name of Primary Budgeted Entity
2056 OPEN -> Display Order is P J O G ( which ever is not null
2057
2058 */
2059
2060 l_proc varchar2(72) := g_package||'set_wks_log_context';
2061 l_worksheet_details_rec pqh_worksheet_details%ROWTYPE;
2062 l_position_name hr_positions.name%TYPE;
2063 l_job_name per_jobs.name%TYPE;
2064 l_organization_name hr_all_organization_units_tl.name%TYPE;
2065 l_grade_name per_grades.name%TYPE;
2066
2067 CURSOR csr_wks_detail_rec IS
2068 SELECT *
2069 FROM pqh_worksheet_details
2070 WHERE worksheet_detail_id = p_worksheet_detail_id ;
2071
2072 BEGIN
2073
2074 hr_utility.set_location('Entering:'||l_proc, 5);
2075
2076 OPEN csr_wks_detail_rec;
2077 FETCH csr_wks_detail_rec INTO l_worksheet_details_rec;
2078 CLOSE csr_wks_detail_rec;
2079
2080
2081 l_position_name := HR_GENERAL.DECODE_POSITION (p_position_id => l_worksheet_details_rec.position_id);
2082 l_job_name := HR_GENERAL.DECODE_JOB (p_job_id => l_worksheet_details_rec.job_id);
2083 l_organization_name := HR_GENERAL.DECODE_ORGANIZATION (p_organization_id => l_worksheet_details_rec.organization_id);
2084 l_grade_name := HR_GENERAL.DECODE_GRADE (p_grade_id => l_worksheet_details_rec.grade_id);
2085
2086 IF NVL(l_worksheet_details_rec.action_cd , 'R') = 'R' THEN
2087 -- this is the main parent record , display Organization Name
2088 p_log_context := SUBSTR(l_organization_name,1,255);
2089 ELSIF NVL(l_worksheet_details_rec.action_cd , 'R') = 'D' THEN
2090 -- this is delegated record , display Organization Name
2091 p_log_context := SUBSTR(l_organization_name,1,255);
2092 ELSIF NVL(l_worksheet_details_rec.action_cd , 'R') = 'B' THEN
2093 -- this is budgeted record , display Primary Budgeted Entity
2094 IF NVL(g_budgeted_entity_cd ,'OPEN') = 'POSITION' THEN
2095 p_log_context := SUBSTR(l_position_name,1,255);
2096 ELSIF NVL(g_budgeted_entity_cd ,'OPEN') = 'JOB' THEN
2097 p_log_context := SUBSTR(l_job_name,1,255);
2098 ELSIF NVL(g_budgeted_entity_cd ,'OPEN') = 'ORGANIZATION' THEN
2099 p_log_context := SUBSTR(l_organization_name,1,255);
2100 ELSIF NVL(g_budgeted_entity_cd ,'OPEN') = 'GRADE' THEN
2101 p_log_context := SUBSTR(l_grade_name,1,255);
2102 ELSIF NVL(g_budgeted_entity_cd ,'OPEN') = 'OPEN' THEN
2103
2104 IF l_position_name IS NOT NULL THEN
2105 p_log_context := SUBSTR(l_position_name,1,255);
2106 ELSIF l_job_name IS NOT NULL THEN
2107 p_log_context := SUBSTR(l_job_name,1,255);
2108 ELSIF l_organization_name IS NOT NULL THEN
2109 p_log_context := SUBSTR(l_organization_name,1,255);
2110 ELSIF l_grade_name IS NOT NULL THEN
2111 p_log_context := SUBSTR(l_grade_name,1,255);
2112 END IF;
2113
2114 END IF;
2115 END IF;
2116
2117 hr_utility.set_location('Log Context : '||p_log_context, 100);
2118 hr_utility.set_location('Leaving:'||l_proc, 1000);
2119
2120 EXCEPTION
2121 WHEN OTHERS THEN
2122
2123 p_log_context := null;
2124 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2125 hr_utility.set_message_token('ROUTINE', l_proc);
2126 hr_utility.set_message_token('REASON', SQLERRM);
2127 -- end log and halt the program here
2128 raise g_error_exception;
2129 END set_wks_log_context;
2130 --------------------------------------------------------------------------------------------------------------
2131 PROCEDURE set_wpr_log_context
2132 (
2133 p_worksheet_period_id IN pqh_worksheet_periods.worksheet_period_id%TYPE,
2134 p_log_context OUT NOCOPY pqh_process_log.log_context%TYPE
2135 ) IS
2136 /*
2137 This procedure will set the log_context at wks periods level
2138
2139 Display the period start date for start_time_period_id and
2140 Display the period end date for end_time_period_id
2141 Table : per_time_periods
2142
2143 */
2144
2145 l_proc varchar2(72) := g_package||'set_wpr_log_context';
2146 l_worksheet_periods_rec pqh_worksheet_periods%ROWTYPE;
2147 l_per_time_periods per_time_periods%ROWTYPE;
2148 l_start_date per_time_periods.start_date%TYPE;
2149 l_end_date per_time_periods.end_date%TYPE;
2150
2151 CURSOR csr_wks_periods_rec IS
2152 SELECT *
2153 FROM pqh_worksheet_periods
2154 WHERE worksheet_period_id = p_worksheet_period_id ;
2155
2156 CURSOR csr_per_time_periods ( p_time_period_id IN number ) IS
2157 SELECT *
2158 FROM per_time_periods
2159 WHERE time_period_id = p_time_period_id ;
2160
2161 BEGIN
2162
2163 hr_utility.set_location('Entering:'||l_proc, 5);
2164
2165 OPEN csr_wks_periods_rec;
2166 FETCH csr_wks_periods_rec INTO l_worksheet_periods_rec;
2167 CLOSE csr_wks_periods_rec;
2168
2169 -- get the start date
2170 OPEN csr_per_time_periods ( p_time_period_id => l_worksheet_periods_rec.start_time_period_id);
2171 FETCH csr_per_time_periods INTO l_per_time_periods;
2172 CLOSE csr_per_time_periods;
2173
2174 l_start_date := l_per_time_periods.start_date;
2175
2176
2177 -- get the end date
2178
2179 OPEN csr_per_time_periods ( p_time_period_id => l_worksheet_periods_rec.end_time_period_id);
2180 FETCH csr_per_time_periods INTO l_per_time_periods;
2181 CLOSE csr_per_time_periods;
2182
2183 l_end_date := l_per_time_periods.end_date;
2184
2185 -- set log context
2186
2187 p_log_context := l_start_date||' - '||l_end_date;
2188
2189 hr_utility.set_location('Log Context : '||p_log_context, 101);
2190 hr_utility.set_location('Leaving:'||l_proc, 1000);
2191
2192 EXCEPTION
2193 WHEN OTHERS THEN
2194
2195 p_log_context := null;
2196 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2197 hr_utility.set_message_token('ROUTINE', l_proc);
2198 hr_utility.set_message_token('REASON', SQLERRM);
2199 -- end log and halt the program here
2200 raise g_error_exception;
2201 END set_wpr_log_context;
2202
2203 --------------------------------------------------------------------------------------------------------------
2204 PROCEDURE set_wst_log_context
2205 (
2206 p_worksheet_budget_set_id IN pqh_worksheet_budget_sets.worksheet_budget_set_id%TYPE,
2207 p_log_context OUT NOCOPY pqh_process_log.log_context%TYPE
2208 ) IS
2209
2210 /*
2211 This procedure will set the log_context at wks budget sets level
2212
2213 Display the DFLT_BUDGET_SET_NAME
2214 Table : pqh_dflt_budget_sets
2215
2216 */
2217
2218 l_proc varchar2(72) := g_package||'set_wst_log_context';
2219 l_worksheet_budget_sets_rec pqh_worksheet_budget_sets%ROWTYPE;
2220 l_dflt_budget_sets_rec pqh_dflt_budget_sets%ROWTYPE;
2221
2222
2223 CURSOR csr_wks_budget_sets_rec IS
2224 SELECT *
2225 FROM pqh_worksheet_budget_sets
2226 WHERE worksheet_budget_set_id = p_worksheet_budget_set_id;
2227
2228 CURSOR csr_dflt_budget_sets_rec ( p_dflt_budget_set_id IN number) IS
2229 SELECT *
2230 FROM pqh_dflt_budget_sets
2231 WHERE dflt_budget_set_id = p_dflt_budget_set_id;
2232
2233 BEGIN
2234
2235 hr_utility.set_location('Entering:'||l_proc, 5);
2236
2237 OPEN csr_wks_budget_sets_rec;
2238 FETCH csr_wks_budget_sets_rec INTO l_worksheet_budget_sets_rec;
2239 CLOSE csr_wks_budget_sets_rec;
2240
2241 OPEN csr_dflt_budget_sets_rec(p_dflt_budget_set_id => l_worksheet_budget_sets_rec.dflt_budget_set_id);
2242 FETCH csr_dflt_budget_sets_rec INTO l_dflt_budget_sets_rec;
2243 CLOSE csr_dflt_budget_sets_rec;
2244
2245 p_log_context := l_dflt_budget_sets_rec.dflt_budget_set_name;
2246
2247 hr_utility.set_location('Log Context : '||p_log_context, 101);
2248 hr_utility.set_location('Leaving:'||l_proc, 1000);
2249
2250 EXCEPTION
2251 WHEN OTHERS THEN
2252 p_log_context := null;
2253 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2254 hr_utility.set_message_token('ROUTINE', l_proc);
2255 hr_utility.set_message_token('REASON', SQLERRM);
2256 -- end log and halt the program here
2257 raise g_error_exception;
2258 END set_wst_log_context;
2259
2260 --------------------------------------------------------------------------------------------------------------
2261 PROCEDURE set_wel_log_context
2262 (
2263 p_worksheet_bdgt_elmnt_id IN pqh_worksheet_bdgt_elmnts.worksheet_bdgt_elmnt_id%TYPE,
2264 p_log_context OUT NOCOPY pqh_process_log.log_context%TYPE
2265 ) IS
2266
2267 /*
2268 This procedure will set the log_context at wks budget elements level
2269
2270 Display the ELEMENT_NAME
2271 Table : pay_element_types
2272
2273 */
2274
2275 l_proc varchar2(72) := g_package||'set_wel_log_context';
2276
2277 CURSOR csr_wks_bdgt_elmnts_rec IS
2278 SELECT *
2279 FROM pqh_worksheet_bdgt_elmnts
2280 WHERE worksheet_bdgt_elmnt_id = p_worksheet_bdgt_elmnt_id;
2281
2282 CURSOR csr_pay_element_types_rec ( p_element_type_id IN number) IS
2283 SELECT element_name
2284 FROM pay_element_types_f_tl
2285 WHERE element_type_id = p_element_type_id
2286 and language = userenv('LANG');
2287
2288 l_worksheet_bdgt_elmnts_rec pqh_worksheet_bdgt_elmnts%ROWTYPE;
2289 l_pay_element_types_rec csr_pay_element_types_rec%ROWTYPE;
2290
2291 BEGIN
2292
2293 hr_utility.set_location('Entering:'||l_proc, 5);
2294
2295 OPEN csr_wks_bdgt_elmnts_rec;
2296 FETCH csr_wks_bdgt_elmnts_rec INTO l_worksheet_bdgt_elmnts_rec;
2297 CLOSE csr_wks_bdgt_elmnts_rec;
2298
2299 OPEN csr_pay_element_types_rec(p_element_type_id => l_worksheet_bdgt_elmnts_rec.element_type_id);
2300 FETCH csr_pay_element_types_rec INTO l_pay_element_types_rec;
2301 CLOSE csr_pay_element_types_rec;
2302
2303 p_log_context := l_pay_element_types_rec.element_name;
2304
2305 hr_utility.set_location('Log Context : '||p_log_context, 101);
2306 hr_utility.set_location('Leaving:'||l_proc, 1000);
2307
2308
2309 EXCEPTION
2310 WHEN OTHERS THEN
2311 p_log_context := null;
2312 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2313 hr_utility.set_message_token('ROUTINE', l_proc);
2314 hr_utility.set_message_token('REASON', SQLERRM);
2315 -- end log and halt the program here
2316 raise g_error_exception;
2317 END set_wel_log_context;
2318
2319 --------------------------------------------------------------------------------------------------------------
2320 PROCEDURE set_wfs_log_context
2321 (
2322 p_worksheet_fund_src_id IN pqh_worksheet_fund_srcs.worksheet_fund_src_id%TYPE,
2323 p_log_context OUT NOCOPY pqh_process_log.log_context%TYPE
2324 ) IS
2325
2326 /*
2327 This procedure will set the log_context at wks budget fund srcs level
2328
2329 Display the CONCATENATED_SEGMENTS
2330 Table : pay_cost_allocation_keyflex
2331
2332 */
2333
2334 l_proc varchar2(72) := g_package||'set_wfs_log_context';
2335 l_worksheet_fund_srcs_rec pqh_worksheet_fund_srcs%ROWTYPE;
2336 l_pay_cost_allocation_kf_rec pay_cost_allocation_keyflex%ROWTYPE;
2337
2338
2339 CURSOR csr_wks_bdgt_fund_srcs_rec IS
2340 SELECT *
2341 FROM pqh_worksheet_fund_srcs
2342 WHERE worksheet_fund_src_id = p_worksheet_fund_src_id;
2343
2344 CURSOR csr_pay_cost_allocation_kf_rec ( p_cost_allocation_keyflex_id IN number) IS
2345 SELECT *
2346 FROM pay_cost_allocation_keyflex
2347 WHERE cost_allocation_keyflex_id = p_cost_allocation_keyflex_id;
2348
2349 BEGIN
2350
2351 hr_utility.set_location('Entering:'||l_proc, 5);
2352
2353 OPEN csr_wks_bdgt_fund_srcs_rec;
2354 FETCH csr_wks_bdgt_fund_srcs_rec INTO l_worksheet_fund_srcs_rec;
2355 CLOSE csr_wks_bdgt_fund_srcs_rec;
2356
2357 OPEN csr_pay_cost_allocation_kf_rec(p_cost_allocation_keyflex_id => l_worksheet_fund_srcs_rec.cost_allocation_keyflex_id);
2358 FETCH csr_pay_cost_allocation_kf_rec INTO l_pay_cost_allocation_kf_rec;
2359 CLOSE csr_pay_cost_allocation_kf_rec;
2360
2361
2362 p_log_context := l_pay_cost_allocation_kf_rec.concatenated_segments;
2363
2364
2365 hr_utility.set_location('Log Context : '||p_log_context, 101);
2366 hr_utility.set_location('Leaving:'||l_proc, 1000);
2367
2368 EXCEPTION
2369 WHEN OTHERS THEN
2370 p_log_context := null;
2371 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2372 hr_utility.set_message_token('ROUTINE', l_proc);
2373 hr_utility.set_message_token('REASON', SQLERRM);
2374 -- end log and halt the program here
2375 raise g_error_exception;
2376 END set_wfs_log_context;
2377
2378
2379 --------------------------------------------------------------------------------------------------------------
2380 FUNCTION apply_transaction
2381 (
2382 p_transaction_id IN number,
2383 p_validate_only IN varchar2
2384 ) RETURN varchar2 IS
2385
2386 /*
2387 This procedure is a wrapper which will be called by workflow. This procedure will call the
2388 check_wks_errors procedure and if there were no errors and if the p_validate_only is NO then
2389 call the apply_budget procedure
2390 If the chk_wks_errors has errors then we will update the wks status to 'APPROVED' from the 'SUBMITTED'
2391 status
2392
2393 If p_validate_only is YES then we would only call the check_wks_errors procedure
2394
2395 p_transaction_id is the WKS Detail ID
2396
2397 If the Apply transaction is successful and the budget can be transfered to GL , we would call the
2398 Apply to GL procedure
2399
2400 */
2401
2402 l_proc varchar2(72) := g_package||'apply_transaction';
2403 l_status varchar2(30);
2404 l_wks_detail_rec pqh_worksheet_details%ROWTYPE;
2405 l_return varchar2(30) := 'SUCCESS' ;
2406 l_worksheet_id pqh_worksheet_details.worksheet_id%TYPE;
2407 l_budget_id pqh_budgets.budget_id%TYPE;
2408 l_budget_rec pqh_budgets%ROWTYPE;
2409 l_budget_version_id pqh_budget_versions.budget_version_id%TYPE;
2410 l_gl_validation varchar2(30);
2411 l_req number(9);
2412 l_transaction_category_id number;
2413 l_transaction_categories_rec pqh_transaction_categories%ROWTYPE;
2414 l_pqh_worksheets_rec pqh_worksheets%ROWTYPE;
2415 l_txn_state varchar2(10);
2416
2417
2418 CURSOR csr_wks_dtl_rec IS
2419 SELECT *
2420 FROM pqh_worksheet_details
2421 WHERE worksheet_detail_id = p_transaction_id;
2422
2423 CURSOR csr_txn_cat_id(p_transaction_category_id in number) IS
2424 SELECT *
2425 FROM pqh_transaction_categories
2426 WHERE transaction_category_id = p_transaction_category_id;
2427
2428 CURSOR csr_wks(p_worksheet_id IN NUMBER) IS
2429 SELECT *
2430 FROM pqh_worksheets
2431 WHERE worksheet_id = p_worksheet_id;
2432
2433 CURSOR csr_budget_rec(p_budget_id IN NUMBER ) IS
2434 SELECT *
2435 FROM pqh_budgets
2436 WHERE budget_id = p_budget_id;
2437
2438 cursor c1(p_transaction_id in number) is
2439 select wf_transaction_category_id
2440 from pqh_worksheets wks, pqh_worksheet_details wkd
2441 where wks.worksheet_id = wkd.worksheet_id
2442 and wkd.worksheet_detail_id = p_transaction_id;
2443
2444 BEGIN
2445
2446 hr_utility.set_location('Entering:'||l_proc, 5);
2447
2448 -- populate global variable g_root_wks_dtl_id and g_transaction_category_id
2449 g_root_wks_dtl_id := p_transaction_id;
2450
2451 OPEN c1(p_transaction_id);
2452 FETCH c1 INTO l_transaction_category_id;
2453 CLOSE c1;
2454
2455 OPEN csr_txn_cat_id(l_transaction_category_id);
2456 FETCH csr_txn_cat_id INTO l_transaction_categories_rec;
2457 CLOSE csr_txn_cat_id;
2458
2459 g_transaction_category_id := l_transaction_categories_rec.transaction_category_id;
2460
2461 -- call the chk procedure
2462 pqh_wks_error_chk.check_wks_errors
2463 (
2464 p_worksheet_detail_id => p_transaction_id,
2465 p_status => l_status
2466 );
2467
2468 hr_utility.set_location('Chk Wks Status : '||l_status,10);
2469 hr_utility.set_location('Validate Only flag : '||p_validate_only,15);
2470
2471 -- if p_validate_only = 'NO' and the above chk was successful i.e
2472 -- l_status = SUCCESS then call apply budget
2473
2474 IF l_status <> 'SUCCESS' THEN
2475
2476 --
2477 -- if this is not a validate mode and there were errors in wks then
2478 -- mark the wks status as APPROVED from SUBMITTED
2479 --
2480 IF p_validate_only = 'NO' THEN
2481 -- get the worksheet ID
2482
2483 OPEN csr_wks_dtl_rec;
2484 FETCH csr_wks_dtl_rec INTO l_wks_detail_rec;
2485 CLOSE csr_wks_dtl_rec;
2486
2487 hr_utility.set_location('Changing WKS Status with WKS ID : '||l_wks_detail_rec.worksheet_id,20);
2488
2489 -- this is done by Sumit in PQHWSWKS form for txn state = 'I'
2490 -- if txn state = 'D' then update here
2491
2492 -- get the wks action date
2493 OPEN csr_wks(p_worksheet_id => l_wks_detail_rec.worksheet_id);
2494 FETCH csr_wks INTO l_pqh_worksheets_rec;
2495 CLOSE csr_wks;
2496
2497 l_txn_state := get_txn_state
2498 (
2499 p_transaction_category_id => g_transaction_category_id,
2500 p_action_date => l_pqh_worksheets_rec.action_date
2501 );
2502
2503 IF NVL(l_txn_state,'I') = 'D' THEN
2504
2505 -- update the worksheet status flag to 'APPROVED'
2506
2507 updt_wks_status
2508 (
2509 p_worksheet_id => l_wks_detail_rec.worksheet_id,
2510 p_status => 'APPROVED'
2511 );
2512
2513 hr_utility.set_location('wks changed with approved '||l_proc,23);
2514 updt_wkd_status
2515 (
2516 p_worksheet_id => l_wks_detail_rec.worksheet_id,
2517 p_status => 'APPROVED'
2518 );
2519 hr_utility.set_location('wkd changed with approved '||l_proc,26);
2520 END IF; -- for defered txn state
2521
2522
2523 END IF; -- not in validate mode and errors
2524
2525
2526 -- set the error message to see the process log
2527 pqh_wf.set_apply_error(p_transaction_category_id => g_transaction_category_id,
2528 p_transaction_id => g_root_wks_dtl_id,
2529 p_apply_error_mesg => 'PQH_WKS_CHK_ERRORS',
2530 p_apply_error_num => '1');
2531
2532 hr_utility.set_location('dberror returned ',18);
2533 RETURN 'FAILURE';
2534
2535 END IF;
2536
2537
2538 IF p_validate_only = 'NO' AND l_status = 'SUCCESS' THEN
2539
2540 -- get the worksheet ID
2541
2542 OPEN csr_wks_dtl_rec;
2543 FETCH csr_wks_dtl_rec INTO l_wks_detail_rec;
2544 CLOSE csr_wks_dtl_rec;
2545
2546 hr_utility.set_location('Calling Apply Budget with WKS ID : '||l_wks_detail_rec.worksheet_id,20);
2547
2548 -- create savepoint
2549
2550 savepoint s1;
2551
2552 pqh_apply_budget.apply_budget
2553 (
2554 p_worksheet_id => l_wks_detail_rec.worksheet_id,
2555 p_budget_version_id => l_budget_version_id
2556 );
2557
2558 hr_utility.set_location('Called Apply Budget, Budget Version ID is '||l_budget_version_id,20);
2559
2560 --
2561 -- check if the budget can be transfered to GL , if so POST the budget to GL
2562 --
2563 -- get the budget Id from pqh_worksheets
2564 OPEN csr_wks(p_worksheet_id => l_wks_detail_rec.worksheet_id);
2565 FETCH csr_wks INTO l_pqh_worksheets_rec;
2566 CLOSE csr_wks;
2567
2568 l_budget_id := l_pqh_worksheets_rec.budget_id;
2569
2570 hr_utility.set_location('Budget ID is '||l_budget_id, 21);
2571
2572 -- get the budget characteristics
2573 OPEN csr_budget_rec(p_budget_id => l_budget_id);
2574 FETCH csr_budget_rec INTO l_budget_rec;
2575 CLOSE csr_budget_rec;
2576
2577 -- if the budget can be transfered to GL then validate do the validations before posting
2578 -- budget should be marked for transfer to GL and it should not be marked as psb_budget
2579 IF NVL(l_budget_rec.transfer_to_gl_flag,'N') = 'Y' and l_budget_rec.psb_budget_flag<> 'Y' THEN
2580
2581 hr_utility.set_location('Calling GL Posting Validate Budget Version ID : '||l_budget_version_id,25);
2582 pqh_gl_posting.post_budget
2583 ( p_budget_version_id => l_budget_version_id,
2584 p_validate => true ,
2585 p_status => l_gl_validation
2586 );
2587
2588 -- if the validations are successful call the gl posting program
2589 IF NVL(l_gl_validation,'ERROR') = 'SUCCESS' THEN
2590
2591 hr_utility.set_location('Calling GL Posting Conc Program ',26);
2592 l_req := fnd_request.submit_request
2593 (application => 'PQH',
2594 program => 'PQHGLPOST' ,
2595 argument1 => l_budget_version_id
2596 );
2597 -- check if the program was submitted successfully
2598 IF NVL(l_req,0) = 0 THEN
2599 -- conc program could not be submitted
2600 -- ROLLBACK HERE up to Savepoint s1 AND THEN PASS CONTROL TO FORM
2601 rollback to s1;
2602 hr_utility.set_location('Conc Program could not be submittted '||l_req, 27);
2603 -- set the error message to see the process log
2604 pqh_wf.set_apply_error(p_transaction_category_id => g_transaction_category_id,
2605 p_transaction_id => g_root_wks_dtl_id,
2606 p_apply_error_mesg => 'PQH_CONC_GL_PGM',
2607 p_apply_error_num => '3');
2608
2609 hr_utility.set_location('dberror returned ',27);
2610 l_return := 'FAILURE';
2611 ELSE
2612 hr_utility.set_location('Submitted GL Post Conc Pgm Request '||l_req, 27);
2613 END IF; -- conc program submit failed
2614 ELSE
2615 -- there were errors in gl validation, return error for rollback
2616 -- ROLLBACK HERE up to Savepoint s1 AND THEN PASS CONTROL TO FORM
2617 rollback to s1;
2618 hr_utility.set_location('GL Validation Failed ',26);
2619 -- set the error message to see the process log
2620 pqh_wf.set_apply_error(p_transaction_category_id => g_transaction_category_id,
2621 p_transaction_id => g_root_wks_dtl_id,
2622 p_apply_error_mesg => 'PQH_GL_VAL_ERR',
2623 p_apply_error_num => '3');
2624
2625 hr_utility.set_location('dberror returned ',27);
2626 l_return := 'FAILURE';
2627 END IF;
2628 END IF; -- transfer_to_gl_flag is Y or psb_budget
2629
2630 END IF; -- p_validate_only = 'NO' AND l_status = 'SUCCESS'
2631 hr_utility.set_location('Leaving:'||l_proc, 1000);
2632 return l_return;
2633
2634 EXCEPTION
2635 WHEN OTHERS THEN
2636 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2637 hr_utility.set_message_token('ROUTINE', l_proc);
2638 hr_utility.set_message_token('REASON', SQLERRM);
2639 -- rollback to s1;
2640 return 'FAILURE';
2641 hr_utility.raise_error;
2642 END apply_transaction;
2643
2644 --------------------------------------------------------------------------------------------------------------
2645 PROCEDURE comp_bgt_ver_unit_val
2646 (
2647 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE
2648 ) IS
2649 /*
2650 This procedure will be called in the case of Correct the same version i.e worksheet_mode_cd = 'O'
2651 In this case will will compute the total of all unit values from pqh_budget_details instead of
2652 pqh_worksheet_details as the user may not have clicked the populate all button in the form in
2653 which case all budget records may not be there in pqh_worksheet_details table
2654 */
2655
2656 -- cursor for unit1_value,2,3
2657 CURSOR units_csr IS
2658 SELECT SUM(nvl(BUDGET_UNIT1_VALUE,0)) ,
2659 SUM(nvl(BUDGET_UNIT2_VALUE,0)) ,
2660 SUM(nvl(BUDGET_UNIT3_VALUE,0))
2661 FROM pqh_budget_details
2662 WHERE budget_version_id = p_budget_version_id;
2663
2664 -- cursor for OVN for the current budget version record
2665 CURSOR csr_budget_version IS
2666 SELECT *
2667 FROM pqh_budget_versions
2668 WHERE budget_version_id = p_budget_version_id;
2669
2670 -- worksheet cursor is
2671 CURSOR pqh_worksheets_cur(p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE) IS
2672 SELECT *
2673 FROM pqh_worksheets
2674 WHERE worksheet_id = p_worksheet_id;
2675
2676 l_proc varchar2(72) := g_package||'comp_bgt_ver_unit_val';
2677 l_budget_unit1_value pqh_budget_details.budget_unit1_value%TYPE;
2678 l_budget_unit2_value pqh_budget_details.budget_unit2_value%TYPE;
2679 l_budget_unit3_value pqh_budget_details.budget_unit3_value%TYPE;
2680 l_pqh_budget_version_rec pqh_budget_versions%ROWTYPE;
2681 l_object_version_number pqh_budget_versions.object_version_number%TYPE;
2682 l_pqh_worksheets_rec pqh_worksheets%ROWTYPE;
2683
2684
2685 BEGIN
2686 hr_utility.set_location('Entering:'||l_proc, 5);
2687
2688 -- compute the unit values
2689 OPEN units_csr;
2690 FETCH units_csr INTO l_budget_unit1_value, l_budget_unit2_value, l_budget_unit3_value;
2691 CLOSE units_csr;
2692
2693 -- get the current OVN of the budget_version record
2694 OPEN csr_budget_version;
2695 FETCH csr_budget_version INTO l_pqh_budget_version_rec;
2696 CLOSE csr_budget_version;
2697
2698 l_object_version_number := l_pqh_budget_version_rec.object_version_number;
2699
2700 -- get the worksheet start and end dates
2701 OPEN pqh_worksheets_cur(p_worksheet_id => g_worksheet_id);
2702 FETCH pqh_worksheets_cur INTO l_pqh_worksheets_rec;
2703 CLOSE pqh_worksheets_cur;
2704
2705 -- call the update API
2706
2707 pqh_budget_versions_api.update_budget_version
2708 (
2709 p_validate => false
2710 ,p_budget_version_id => l_pqh_budget_version_rec.budget_version_id
2711 ,p_budget_id => l_pqh_budget_version_rec.budget_id
2712 ,p_version_number => l_pqh_budget_version_rec.version_number
2713 ,p_date_from => l_pqh_worksheets_rec.date_from
2714 ,p_date_to => l_pqh_worksheets_rec.date_to
2715 ,p_transfered_to_gl_flag => l_pqh_budget_version_rec.transfered_to_gl_flag
2716 ,p_xfer_to_other_apps_cd => l_pqh_budget_version_rec.xfer_to_other_apps_cd
2717 ,p_object_version_number => l_object_version_number
2718 ,p_budget_unit1_value => l_budget_unit1_value
2719 ,p_budget_unit2_value => l_budget_unit2_value
2720 ,p_budget_unit3_value => l_budget_unit3_value
2721 ,p_budget_unit1_available => l_pqh_budget_version_rec.budget_unit1_available
2722 ,p_budget_unit2_available => l_pqh_budget_version_rec.budget_unit2_available
2723 ,p_budget_unit3_available => l_pqh_budget_version_rec.budget_unit3_available
2724 ,p_effective_date => sysdate
2725 );
2726
2727
2728 hr_utility.set_location('Leaving:'||l_proc, 1000);
2729
2730 EXCEPTION
2731 WHEN OTHERS THEN
2732 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2733 hr_utility.set_message_token('ROUTINE', l_proc);
2734 hr_utility.set_message_token('REASON', SQLERRM);
2735 hr_utility.raise_error;
2736 END comp_bgt_ver_unit_val;
2737 --------------------------------------------------------------------------------------------------------------
2738
2739 PROCEDURE updt_budget_status
2740 (
2741 p_budget_id IN pqh_budgets.budget_id%TYPE
2742 ) IS
2743 /*
2744 This procedure will update the budget status to FROZEN once the budgte is successfully
2745 applied
2746 */
2747
2748 l_proc varchar2(72) := g_package||'updt_budget_status';
2749
2750 CURSOR csr_budget IS
2751 SELECT *
2752 FROM pqh_budgets
2753 WHERE budget_id = p_budget_id
2754 AND NVL(status,'X') <> 'FROZEN';
2755
2756 l_budget_rec pqh_budgets%ROWTYPE;
2757 l_object_version_number pqh_budgets.object_version_number%TYPE;
2758
2759 BEGIN
2760 hr_utility.set_location('Entering:'||l_proc, 5);
2761
2762 OPEN csr_budget;
2763 LOOP
2764 FETCH csr_budget INTO l_budget_rec;
2765 EXIT WHEN csr_budget%NOTFOUND;
2766
2767 l_object_version_number := l_budget_rec.object_version_number;
2768
2769 -- call the update API here
2770 pqh_budgets_api.update_budget
2771 (
2772 p_validate => false
2773 ,p_budget_id => p_budget_id
2774 ,p_object_version_number => l_object_version_number
2775 ,p_status => 'FROZEN'
2776 ,p_effective_date => sysdate
2777 );
2778
2779
2780 END LOOP;
2781 CLOSE csr_budget;
2782
2783
2784 hr_utility.set_location('Leaving:'||l_proc, 1000);
2785
2786 EXCEPTION
2787 WHEN OTHERS THEN
2788 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2789 hr_utility.set_message_token('ROUTINE', l_proc);
2790 hr_utility.set_message_token('REASON', SQLERRM);
2791 hr_utility.raise_error;
2792 END updt_budget_status;
2793
2794
2795
2796 --------------------------------------------------------------------------------------------------------------
2797
2798 PROCEDURE updt_wks_status
2799 (
2800 p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE,
2801 p_status IN pqh_worksheets.transaction_status%TYPE
2802 ) IS
2803 /*
2804 This procedure will update the wks status to APPLIED after budget is applied successfully
2805 If the chk wks has error then the wks status will be changed to APPROVED from SUBMITTED
2806 If the wks has errors then the apply budget will not be called. In this case we will not have
2807 g_budget_version_id computed.
2808
2809 */
2810
2811 l_proc varchar2(72) := g_package||'updt_wks_status';
2812
2813
2814 CURSOR csr_wks IS
2815 SELECT *
2816 FROM pqh_worksheets
2817 WHERE worksheet_id = p_worksheet_id;
2818
2819 l_wks_rec pqh_worksheets%ROWTYPE;
2820 l_wks_ovn pqh_worksheets.object_version_number%TYPE;
2821
2822 BEGIN
2823 hr_utility.set_location('Entering:'||l_proc, 5);
2824
2825 OPEN csr_wks;
2826 LOOP
2827 FETCH csr_wks INTO l_wks_rec;
2828 EXIT WHEN csr_wks%NOTFOUND;
2829
2830 l_wks_ovn := l_wks_rec.object_version_number;
2831
2832 -- call the update API for APPLIED
2833
2834 IF p_status = 'APPLIED' THEN
2835 pqh_worksheets_api.update_worksheet
2836 (
2837 p_validate => false
2838 ,p_worksheet_id => p_worksheet_id
2839 ,p_object_version_number => l_wks_ovn
2840 ,p_transaction_status => p_status
2841 ,p_budget_version_id => g_budget_version_id
2842 ,p_effective_date => sysdate
2843 );
2844 ELSE
2845 -- p_status is APPROVED and we don't have g_budget_version_id
2846
2847 pqh_worksheets_api.update_worksheet
2848 (
2849 p_validate => false
2850 ,p_worksheet_id => p_worksheet_id
2851 ,p_object_version_number => l_wks_ovn
2852 ,p_transaction_status => p_status
2853 ,p_effective_date => sysdate
2854 );
2855 END IF;
2856
2857
2858 END LOOP;
2859 CLOSE csr_wks;
2860
2861
2862 hr_utility.set_location('Leaving:'||l_proc, 1000);
2863
2864 EXCEPTION
2865 WHEN OTHERS THEN
2866 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2867 hr_utility.set_message_token('ROUTINE', l_proc);
2868 hr_utility.set_message_token('REASON', SQLERRM);
2869 hr_utility.raise_error;
2870 END updt_wks_status;
2871
2872 --------------------------------------------------------------------------------------------------------------
2873 FUNCTION get_txn_state
2874 (
2875 p_transaction_category_id IN number,
2876 p_action_date IN date
2877 ) RETURN VARCHAR2 IS
2878 /*
2879 This function will determine whether the apply_transaction is called in Defered Mode or Immediate Mode
2880 and return D or I. This will be used by apply_transaction to determine whether to update the wks_status
2881 to APPROVED from SUBMIT if the wks had errors
2882
2883 In the following matrix , we have used the abbreviations as follows :
2884
2885 Immediate : I
2886 Deferred : D
2887 Future Dt : F
2888 Past or Present Dt : P-P
2889
2890 *---------------------------------------*
2891 | Future | Action | Post | Net |
2892 | Action CD | Date | Style | |
2893 ----------------------------------------
2894 | I | P-P | I | I |
2895 ----------------------------------------
2896 | I | P-P | D | D |
2897 ----------------------------------------
2898 | I | F | I | I |
2899 ----------------------------------------
2900 | I | F | D | D |
2901 ----------------------------------------
2902 | D | P-P | I | I |
2903 ----------------------------------------
2904 | D | P-P | D | D |
2905 ----------------------------------------
2906 | D | F | I | D |
2907 ----------------------------------------
2908 | D | F | D | D |
2909 ----------------------------------------
2910
2911 */
2912
2913 l_proc varchar2(72) := g_package||'get_txn_state';
2914 l_return_state varchar2(10);
2915
2916 l_transaction_categories_rec pqh_transaction_categories%ROWTYPE;
2917
2918 CURSOR csr_txn_cat_id IS
2919 SELECT *
2920 FROM pqh_transaction_categories
2921 WHERE transaction_category_id = p_transaction_category_id;
2922
2923
2924 BEGIN
2925 hr_utility.set_location('Entering:'||l_proc, 5);
2926
2927 OPEN csr_txn_cat_id;
2928 FETCH csr_txn_cat_id INTO l_transaction_categories_rec;
2929 CLOSE csr_txn_cat_id;
2930
2931 IF l_transaction_categories_rec.future_action_cd = 'I' AND
2932 l_transaction_categories_rec.post_style_cd = 'I' THEN
2933
2934 RETURN 'I';
2935
2936 END IF;
2937
2938 IF l_transaction_categories_rec.future_action_cd = 'D' AND
2939 l_transaction_categories_rec.post_style_cd = 'I' AND
2940 p_action_date <= sysdate THEN
2941
2942 RETURN 'I';
2943
2944 END IF;
2945
2946
2947
2948 hr_utility.set_location('Leaving:'||l_proc, 1000);
2949
2950 RETURN 'D';
2951
2952 EXCEPTION
2953 WHEN OTHERS THEN
2954 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2955 hr_utility.set_message_token('ROUTINE', l_proc);
2956 hr_utility.set_message_token('REASON', SQLERRM);
2957 hr_utility.raise_error;
2958 END get_txn_state;
2959 --------------------------------------------------------------------------------------------------------------
2960
2961
2962 --------------------------------------------------------------------------------------------------------------
2963 -- added as per Sir Hon' Lord Sumit Goyalji
2964 --------------------------------------------------------------------------------------------------------------
2965 PROCEDURE complete_all_del_workflow
2966 (
2967 p_worksheet_id in number,
2968 p_transaction_category_id in number
2969 ) IS
2970
2971 cursor c1 is
2972 select worksheet_detail_id
2973 from pqh_worksheet_details
2974 where worksheet_id = p_worksheet_id
2975 and nvl(action_cd,'B') ='D';
2976
2977 l_itemkey varchar2(30);
2978 l_workflow_name varchar2(30);
2979
2980 BEGIN
2981 l_workflow_name := pqh_wf.get_workflow_name(p_transaction_category_id => p_transaction_category_id);
2982 for i in c1 loop
2983 l_itemkey := to_char(p_transaction_category_id) || '-' || to_char(i.worksheet_detail_id) ;
2984 pqh_wf.complete_delegate_workflow(p_itemkey => l_itemkey,
2985 p_workflow_name => l_workflow_name);
2986 end loop;
2987
2988 EXCEPTION
2989 WHEN others THEN
2990 raise;
2991 END;
2992 --------------------------------------------------------------------------------------------------------------
2993 FUNCTION chk_root_node
2994 (
2995 p_transaction_id number
2996 ) RETURN VARCHAR2 IS
2997 cursor c1 is
2998 select parent_worksheet_detail_id
2999 from pqh_worksheet_details
3000 where worksheet_detail_id = p_transaction_id;
3001
3002 l_parent_id number;
3003 l_result varchar2(30);
3004
3005 BEGIN
3006 open c1;
3007 fetch c1 into l_parent_id;
3008 if c1%notfound then
3009 hr_utility.set_message(8302,'PQH_INVALID_WKS_TXN_ID');
3010 hr_utility.raise_error;
3011 end if;
3012 close c1;
3013 if l_parent_id is null then
3014 l_result := 'ROOT' ;
3015 else
3016 l_result := 'DELEGATE';
3017 end if;
3018 RETURN l_result;
3019
3020 EXCEPTION
3021 WHEN others THEN
3022 raise;
3023 END;
3024 --------------------------------------------------------------------------------------------------------------
3025 PROCEDURE delegate_approve
3026 (
3027 p_worksheet_detail_id in number
3028 ) IS
3029 cursor c1 is select status,parent_worksheet_detail_id,
3030 budget_unit1_available,budget_unit2_available,budget_unit3_available
3031 from pqh_worksheet_details
3032 where worksheet_detail_id = p_worksheet_detail_id
3033 and action_cd = 'D'
3034 for update of status;
3035 cursor c2(p_parent_worksheet_detail_id number) is
3036 select budget_unit1_available,budget_unit2_available, budget_unit3_available
3037 from pqh_worksheet_details
3038 where worksheet_detail_id = p_parent_worksheet_detail_id
3039 for update of budget_unit1_available,budget_unit2_available, budget_unit3_available;
3040 BEGIN
3041 for i in c1 loop
3042 if i.parent_worksheet_detail_id is not null then
3043 for j in c2(i.parent_worksheet_detail_id) loop
3044 update pqh_worksheet_details
3045 set budget_unit1_available = nvl(j.budget_unit1_available,0) + nvl(i.budget_unit1_available,0)
3046 , budget_unit2_available = nvl(j.budget_unit2_available,0) + nvl(i.budget_unit2_available,0)
3047 , budget_unit3_available = nvl(j.budget_unit3_available,0) + nvl(i.budget_unit3_available,0)
3048 where current of c2;
3049 end loop;
3050 update pqh_worksheet_details
3051 set status = 'APPROVED'
3052 where current of c1;
3053 else
3054 hr_utility.set_message(8302,'PQH_INVALID_WKS_TXN_ID');
3055 hr_utility.raise_error;
3056 end if;
3057 end loop;
3058 EXCEPTION
3059 WHEN others THEN
3060 hr_utility.set_message(8302,'PQH_INVALID_WKS_TXN_ID');
3061 hr_utility.raise_error;
3062 END delegate_approve;
3063 procedure build_wks_notice(p_transaction_id in number,
3064 p_worksheet_name out nocopy varchar2,
3065 p_budget_name out nocopy varchar2,
3066 p_tran_cat_name out nocopy varchar2,
3067 p_organization_name out nocopy varchar2,
3068 p_wks_start_date out nocopy date,
3069 p_wks_end_date out nocopy date,
3070 p_bgt_start_date out nocopy date,
3071 p_bgt_end_date out nocopy date,
3072 p_worksheet_mode out nocopy varchar2,
3073 p_budget_style out nocopy varchar2,
3074 p_budget_entity out nocopy varchar2,
3075 p_budget_version out nocopy number) is
3076 l_proc varchar2(61) := g_package||'build_wks_notice' ;
3077 l_worksheet_id number;
3078 l_organization_id number;
3079 l_budget_id number;
3080 l_worksheet_mode_cd varchar2(30);
3081 l_budget_entity_cd varchar2(30);
3082 l_budget_style_cd varchar2(30);
3083 l_tran_cat_id number;
3084 cursor c0 is select worksheet_id,organization_id
3085 from pqh_worksheet_details
3086 where worksheet_detail_id = p_transaction_id;
3087 cursor c1 is select budget_id,worksheet_name,version_number,worksheet_mode_cd,date_from,date_to,wf_transaction_category_id
3088 from pqh_worksheets
3089 where worksheet_id = l_worksheet_id;
3090 cursor c2 is select budget_name,budgeted_entity_cd,budget_style_cd,budget_start_date,budget_end_date
3091 from pqh_budgets
3092 where budget_id = l_budget_id;
3093 cursor c3 is select name from pqh_transaction_categories
3094 where transaction_category_id = l_tran_cat_id;
3095 cursor c4 is select name from hr_all_organization_units_tl
3096 where organization_id = l_organization_id
3097 and language = userenv('LANG');
3098 BEGIN
3099 hr_utility.set_location('inside build_wks_notice '||l_proc,10);
3100 open c0;
3101 fetch c0 into l_worksheet_id,l_organization_id;
3102 close c0;
3103 hr_utility.set_location('worksheet detail fetched '||l_proc,20);
3104 open c1;
3105 fetch c1 into l_budget_id,p_worksheet_name,p_budget_version,l_worksheet_mode_cd,p_wks_start_date,p_wks_end_date,l_tran_cat_id;
3106 close c1;
3107 hr_utility.set_location('worksheet fetched '||l_proc,30);
3108 open c2;
3109 fetch c2 into p_budget_name,l_budget_entity_cd,l_budget_style_cd,p_bgt_start_date,p_bgt_end_date;
3110 close c2;
3111 hr_utility.set_location('budget fetched'||l_proc,40);
3112 open c3;
3113 fetch c3 into p_tran_cat_name;
3114 close c3;
3115 hr_utility.set_location('tran_cat fetched'||l_proc,50);
3116 if l_organization_id is not null then
3117 open c4;
3118 fetch c4 into p_organization_name;
3119 close c4;
3120 hr_utility.set_location('organization fetched'||l_proc,60);
3121 end if;
3122 p_budget_style := hr_general.decode_lookup(p_lookup_type => 'PQH_BUDGET_STYLE',
3123 p_lookup_code => l_budget_style_cd);
3124 p_budget_entity := hr_general.decode_lookup(p_lookup_type => 'PQH_BUDGET_ENTITY',
3125 p_lookup_code => l_budget_entity_cd);
3126 p_worksheet_mode := hr_general.decode_lookup(p_lookup_type =>'PQH_WORKSHEET_MODE' ,
3127 p_lookup_code =>l_worksheet_mode_cd );
3128 exception
3129 when others then
3130 p_worksheet_name := null;
3131 p_budget_name := null;
3132 p_tran_cat_name := null;
3133 p_organization_name := null;
3134 p_wks_start_date := null;
3135 p_wks_end_date := null;
3136 p_bgt_start_date := null;
3137 p_bgt_end_date := null;
3138 p_worksheet_mode := null;
3139 p_budget_style := null;
3140 p_budget_entity := null;
3141 p_budget_version := null;
3142 end build_wks_notice;
3143 FUNCTION fyi_notification (p_transaction_id in number) RETURN varchar2
3144 is
3145 l_document varchar2(4000);
3146 l_proc varchar2(61) := g_package||'fyi_notification' ;
3147 l_budget_name varchar2(30);
3148 l_worksheet_name varchar2(30);
3149 l_budget_version number;
3150 l_worksheet_mode varchar2(60);
3151 l_budget_entity varchar2(30);
3152 l_budget_style varchar2(30);
3153 l_organization_name varchar2(60);
3154 l_tran_cat_name varchar2(60);
3155 l_tran_cat_id number;
3156 l_bgt_start_date date;
3157 l_bgt_end_date date;
3158 l_wks_start_date date;
3159 l_wks_end_date date;
3160 BEGIN
3161 hr_utility.set_location('inside fyi notification'||l_proc,10);
3162 build_wks_notice(p_transaction_id => p_transaction_id,
3163 p_worksheet_name => l_worksheet_name,
3164 p_budget_name => l_budget_name,
3165 p_tran_cat_name => l_tran_cat_name,
3166 p_organization_name => l_organization_name,
3167 p_wks_start_date => l_wks_start_date,
3168 p_wks_end_date => l_wks_end_date,
3169 p_bgt_start_date => l_bgt_start_date,
3170 p_bgt_end_date => l_bgt_end_date,
3171 p_worksheet_mode => l_worksheet_mode,
3172 p_budget_style => l_budget_style,
3173 p_budget_entity => l_budget_entity,
3174 p_budget_version => l_budget_version);
3175 hr_utility.set_message(8302,'PQH_WORKFLOW_FYI_NOTICE');
3176 hr_utility.set_message_token('WORKSHEET_NAME',l_worksheet_name);
3177 hr_utility.set_message_token('BUDGET_NAME',l_budget_name);
3178 hr_utility.set_message_token('TRANSACTION_CATEGORY',l_tran_cat_name);
3179 hr_utility.set_message_token('BUDGET_VERSION',l_budget_version);
3180 hr_utility.set_message_token('BUDGET_STYLE',l_budget_style);
3181 hr_utility.set_message_token('BUDGET_ENTITY',l_budget_entity);
3182 hr_utility.set_message_token('WORKSHEET_MODE',l_worksheet_mode);
3183 hr_utility.set_message_token('ORGANIZATION_NAME',l_organization_name);
3184 hr_utility.set_message_token('BUDGET_START_DATE',l_bgt_start_date);
3185 hr_utility.set_message_token('BUDGET_END_DATE',l_bgt_end_date);
3186 hr_utility.set_message_token('WORKSHEET_START_DATE',l_wks_start_date);
3187 hr_utility.set_message_token('WORKSHEET_END_DATE',l_wks_end_date);
3188 l_document := hr_utility.get_message;
3189 return l_document;
3190 exception
3191 when others then
3192 hr_utility.set_message(8302,'PQH_WF_FYI_NOTICE_FAIL');
3193 hr_utility.set_message_token('TRANSACTION_ID',p_transaction_id);
3194 l_document := hr_utility.get_message;
3195 return l_document;
3196 END fyi_notification;
3197
3198 FUNCTION back_notification (p_transaction_id in number) RETURN varchar2
3199 is
3200 l_document varchar2(4000);
3201 l_proc varchar2(61) := g_package||'back_notification' ;
3202 l_budget_name varchar2(30);
3203 l_worksheet_name varchar2(30);
3204 l_budget_version number;
3205 l_worksheet_mode varchar2(60);
3206 l_budget_entity varchar2(30);
3207 l_budget_style varchar2(30);
3208 l_organization_name varchar2(60);
3209 l_tran_cat_name varchar2(60);
3210 l_tran_cat_id number;
3211 l_bgt_start_date date;
3212 l_bgt_end_date date;
3213 l_wks_start_date date;
3214 l_wks_end_date date;
3215 BEGIN
3216 hr_utility.set_location('inside back notification'||l_proc,10);
3217 build_wks_notice(p_transaction_id => p_transaction_id,
3218 p_worksheet_name => l_worksheet_name,
3219 p_budget_name => l_budget_name,
3220 p_tran_cat_name => l_tran_cat_name,
3221 p_organization_name => l_organization_name,
3222 p_wks_start_date => l_wks_start_date,
3223 p_wks_end_date => l_wks_end_date,
3224 p_bgt_start_date => l_bgt_start_date,
3225 p_bgt_end_date => l_bgt_end_date,
3226 p_worksheet_mode => l_worksheet_mode,
3227 p_budget_style => l_budget_style,
3228 p_budget_entity => l_budget_entity,
3229 p_budget_version => l_budget_version);
3230 hr_utility.set_message(8302,'PQH_WORKFLOW_BACK_NOTICE');
3231 hr_utility.set_message_token('WORKSHEET_NAME',l_worksheet_name);
3232 hr_utility.set_message_token('BUDGET_NAME',l_budget_name);
3233 hr_utility.set_message_token('TRANSACTION_CATEGORY',l_tran_cat_name);
3234 hr_utility.set_message_token('BUDGET_VERSION',l_budget_version);
3235 hr_utility.set_message_token('BUDGET_STYLE',l_budget_style);
3236 hr_utility.set_message_token('BUDGET_ENTITY',l_budget_entity);
3237 hr_utility.set_message_token('WORKSHEET_MODE',l_worksheet_mode);
3238 hr_utility.set_message_token('ORGANIZATION_NAME',l_organization_name);
3239 hr_utility.set_message_token('BUDGET_START_DATE',l_bgt_start_date);
3240 hr_utility.set_message_token('BUDGET_END_DATE',l_bgt_end_date);
3241 hr_utility.set_message_token('WORKSHEET_START_DATE',l_wks_start_date);
3242 hr_utility.set_message_token('WORKSHEET_END_DATE',l_wks_end_date);
3243 l_document := hr_utility.get_message;
3244 return l_document;
3245 exception
3246 when others then
3247 hr_utility.set_message(8302,'PQH_WF_BACK_NOTICE_FAIL');
3248 hr_utility.set_message_token('TRANSACTION_ID',p_transaction_id);
3249 l_document := hr_utility.get_message;
3250 return l_document;
3251 END back_notification;
3252 FUNCTION override_notification (p_transaction_id in number) RETURN varchar2
3253 is
3254 l_document varchar2(4000);
3255 l_proc varchar2(61) := g_package||'override_notification' ;
3256 l_budget_name varchar2(30);
3257 l_worksheet_name varchar2(30);
3258 l_budget_version number;
3259 l_worksheet_mode varchar2(60);
3260 l_budget_entity varchar2(30);
3261 l_budget_style varchar2(30);
3262 l_organization_name varchar2(60);
3263 l_tran_cat_name varchar2(60);
3264 l_tran_cat_id number;
3265 l_bgt_start_date date;
3266 l_bgt_end_date date;
3267 l_wks_start_date date;
3268 l_wks_end_date date;
3269 BEGIN
3270 hr_utility.set_location('inside override notification'||l_proc,10);
3271 build_wks_notice(p_transaction_id => p_transaction_id,
3272 p_worksheet_name => l_worksheet_name,
3273 p_budget_name => l_budget_name,
3274 p_tran_cat_name => l_tran_cat_name,
3275 p_organization_name => l_organization_name,
3276 p_wks_start_date => l_wks_start_date,
3277 p_wks_end_date => l_wks_end_date,
3278 p_bgt_start_date => l_bgt_start_date,
3279 p_bgt_end_date => l_bgt_end_date,
3280 p_worksheet_mode => l_worksheet_mode,
3281 p_budget_style => l_budget_style,
3282 p_budget_entity => l_budget_entity,
3283 p_budget_version => l_budget_version);
3284 hr_utility.set_message(8302,'PQH_WORKFLOW_OVERRIDE_NOTICE');
3285 hr_utility.set_message_token('WORKSHEET_NAME',l_worksheet_name);
3286 hr_utility.set_message_token('BUDGET_NAME',l_budget_name);
3287 hr_utility.set_message_token('TRANSACTION_CATEGORY',l_tran_cat_name);
3288 hr_utility.set_message_token('BUDGET_VERSION',l_budget_version);
3289 hr_utility.set_message_token('BUDGET_STYLE',l_budget_style);
3290 hr_utility.set_message_token('BUDGET_ENTITY',l_budget_entity);
3291 hr_utility.set_message_token('WORKSHEET_MODE',l_worksheet_mode);
3292 hr_utility.set_message_token('ORGANIZATION_NAME',l_organization_name);
3293 hr_utility.set_message_token('BUDGET_START_DATE',l_bgt_start_date);
3294 hr_utility.set_message_token('BUDGET_END_DATE',l_bgt_end_date);
3295 hr_utility.set_message_token('WORKSHEET_START_DATE',l_wks_start_date);
3296 hr_utility.set_message_token('WORKSHEET_END_DATE',l_wks_end_date);
3297 l_document := hr_utility.get_message;
3298 return l_document;
3299 exception
3300 when others then
3301 hr_utility.set_message(8302,'PQH_WF_OVERRIDE_NOTICE_FAIL');
3302 hr_utility.set_message_token('TRANSACTION_ID',p_transaction_id);
3303 l_document := hr_utility.get_message;
3304 return l_document;
3305 END override_notification;
3306 FUNCTION apply_notification (p_transaction_id in number) RETURN varchar2
3307 is
3308 l_document varchar2(4000);
3309 l_proc varchar2(61) := g_package||'apply_notification' ;
3310 l_budget_name varchar2(30);
3311 l_worksheet_name varchar2(30);
3312 l_budget_version number;
3313 l_worksheet_mode varchar2(60);
3314 l_budget_entity varchar2(30);
3315 l_budget_style varchar2(30);
3316 l_organization_name varchar2(60);
3317 l_tran_cat_name varchar2(60);
3318 l_tran_cat_id number;
3319 l_bgt_start_date date;
3320 l_bgt_end_date date;
3321 l_wks_start_date date;
3322 l_wks_end_date date;
3323 BEGIN
3324 hr_utility.set_location('inside apply notification'||l_proc,10);
3325 build_wks_notice(p_transaction_id => p_transaction_id,
3326 p_worksheet_name => l_worksheet_name,
3327 p_budget_name => l_budget_name,
3328 p_tran_cat_name => l_tran_cat_name,
3329 p_organization_name => l_organization_name,
3330 p_wks_start_date => l_wks_start_date,
3331 p_wks_end_date => l_wks_end_date,
3332 p_bgt_start_date => l_bgt_start_date,
3333 p_bgt_end_date => l_bgt_end_date,
3334 p_worksheet_mode => l_worksheet_mode,
3335 p_budget_style => l_budget_style,
3336 p_budget_entity => l_budget_entity,
3337 p_budget_version => l_budget_version);
3338 hr_utility.set_message(8302,'PQH_WORKFLOW_APPLY_NOTICE');
3339 hr_utility.set_message_token('WORKSHEET_NAME',l_worksheet_name);
3340 hr_utility.set_message_token('BUDGET_NAME',l_budget_name);
3341 hr_utility.set_message_token('TRANSACTION_CATEGORY',l_tran_cat_name);
3342 hr_utility.set_message_token('BUDGET_VERSION',l_budget_version);
3343 hr_utility.set_message_token('BUDGET_STYLE',l_budget_style);
3344 hr_utility.set_message_token('BUDGET_ENTITY',l_budget_entity);
3345 hr_utility.set_message_token('WORKSHEET_MODE',l_worksheet_mode);
3346 hr_utility.set_message_token('ORGANIZATION_NAME',l_organization_name);
3347 hr_utility.set_message_token('BUDGET_START_DATE',l_bgt_start_date);
3348 hr_utility.set_message_token('BUDGET_END_DATE',l_bgt_end_date);
3349 hr_utility.set_message_token('WORKSHEET_START_DATE',l_wks_start_date);
3350 hr_utility.set_message_token('WORKSHEET_END_DATE',l_wks_end_date);
3351 l_document := hr_utility.get_message;
3352 return l_document;
3353 exception
3354 when others then
3355 hr_utility.set_message(8302,'PQH_WF_APPLY_NOTICE_FAIL');
3356 hr_utility.set_message_token('TRANSACTION_ID',p_transaction_id);
3357 l_document := hr_utility.get_message;
3358 return l_document;
3359 END apply_notification;
3360 FUNCTION reject_notification (p_transaction_id in number) RETURN varchar2
3361 is
3362 l_document varchar2(4000);
3363 l_proc varchar2(61) := g_package||'reject_notification' ;
3364 l_budget_name varchar2(30);
3365 l_worksheet_name varchar2(30);
3366 l_budget_version number;
3367 l_worksheet_mode varchar2(60);
3368 l_budget_entity varchar2(30);
3369 l_budget_style varchar2(30);
3370 l_organization_name varchar2(60);
3371 l_tran_cat_name varchar2(60);
3372 l_tran_cat_id number;
3373 l_bgt_start_date date;
3374 l_bgt_end_date date;
3375 l_wks_start_date date;
3376 l_wks_end_date date;
3377 BEGIN
3378 hr_utility.set_location('inside reject notification'||l_proc,10);
3379 build_wks_notice(p_transaction_id => p_transaction_id,
3380 p_worksheet_name => l_worksheet_name,
3381 p_budget_name => l_budget_name,
3382 p_tran_cat_name => l_tran_cat_name,
3383 p_organization_name => l_organization_name,
3384 p_wks_start_date => l_wks_start_date,
3385 p_wks_end_date => l_wks_end_date,
3386 p_bgt_start_date => l_bgt_start_date,
3387 p_bgt_end_date => l_bgt_end_date,
3388 p_worksheet_mode => l_worksheet_mode,
3389 p_budget_style => l_budget_style,
3390 p_budget_entity => l_budget_entity,
3391 p_budget_version => l_budget_version);
3392 hr_utility.set_message(8302,'PQH_WORKFLOW_REJECT_NOTICE');
3393 hr_utility.set_message_token('WORKSHEET_NAME',l_worksheet_name);
3394 hr_utility.set_message_token('BUDGET_NAME',l_budget_name);
3395 hr_utility.set_message_token('TRANSACTION_CATEGORY',l_tran_cat_name);
3396 hr_utility.set_message_token('BUDGET_VERSION',l_budget_version);
3397 hr_utility.set_message_token('BUDGET_STYLE',l_budget_style);
3398 hr_utility.set_message_token('BUDGET_ENTITY',l_budget_entity);
3399 hr_utility.set_message_token('WORKSHEET_MODE',l_worksheet_mode);
3400 hr_utility.set_message_token('ORGANIZATION_NAME',l_organization_name);
3401 hr_utility.set_message_token('BUDGET_START_DATE',l_bgt_start_date);
3402 hr_utility.set_message_token('BUDGET_END_DATE',l_bgt_end_date);
3403 hr_utility.set_message_token('WORKSHEET_START_DATE',l_wks_start_date);
3404 hr_utility.set_message_token('WORKSHEET_END_DATE',l_wks_end_date);
3405 l_document := hr_utility.get_message;
3406 return l_document;
3407 exception
3408 when others then
3409 hr_utility.set_message(8302,'PQH_WF_REJECT_NOTICE_FAIL');
3410 hr_utility.set_message_token('TRANSACTION_ID',p_transaction_id);
3411 l_document := hr_utility.get_message;
3412 return l_document;
3413 END reject_notification;
3414 FUNCTION warning_notification (p_transaction_id in number) RETURN varchar2
3415 is
3416 l_document varchar2(4000);
3417 l_proc varchar2(61) := g_package||'warning_notification' ;
3418 l_budget_name varchar2(30);
3419 l_worksheet_name varchar2(30);
3420 l_budget_version number;
3421 l_worksheet_mode varchar2(60);
3422 l_budget_entity varchar2(30);
3423 l_budget_style varchar2(30);
3424 l_organization_name varchar2(60);
3425 l_tran_cat_name varchar2(60);
3426 l_tran_cat_id number;
3427 l_bgt_start_date date;
3428 l_bgt_end_date date;
3429 l_wks_start_date date;
3430 l_wks_end_date date;
3431 BEGIN
3432 hr_utility.set_location('inside warning notification'||l_proc,10);
3433 build_wks_notice(p_transaction_id => p_transaction_id,
3434 p_worksheet_name => l_worksheet_name,
3435 p_budget_name => l_budget_name,
3436 p_tran_cat_name => l_tran_cat_name,
3437 p_organization_name => l_organization_name,
3438 p_wks_start_date => l_wks_start_date,
3439 p_wks_end_date => l_wks_end_date,
3440 p_bgt_start_date => l_bgt_start_date,
3441 p_bgt_end_date => l_bgt_end_date,
3442 p_worksheet_mode => l_worksheet_mode,
3443 p_budget_style => l_budget_style,
3444 p_budget_entity => l_budget_entity,
3445 p_budget_version => l_budget_version);
3446 hr_utility.set_message(8302,'PQH_WORKFLOW_WARNING_NOTICE');
3447 hr_utility.set_message_token('WORKSHEET_NAME',l_worksheet_name);
3448 hr_utility.set_message_token('BUDGET_NAME',l_budget_name);
3449 hr_utility.set_message_token('TRANSACTION_CATEGORY',l_tran_cat_name);
3450 hr_utility.set_message_token('BUDGET_VERSION',l_budget_version);
3451 hr_utility.set_message_token('BUDGET_STYLE',l_budget_style);
3452 hr_utility.set_message_token('BUDGET_ENTITY',l_budget_entity);
3453 hr_utility.set_message_token('WORKSHEET_MODE',l_worksheet_mode);
3454 hr_utility.set_message_token('ORGANIZATION_NAME',l_organization_name);
3455 hr_utility.set_message_token('BUDGET_START_DATE',l_bgt_start_date);
3456 hr_utility.set_message_token('BUDGET_END_DATE',l_bgt_end_date);
3457 hr_utility.set_message_token('WORKSHEET_START_DATE',l_wks_start_date);
3458 hr_utility.set_message_token('WORKSHEET_END_DATE',l_wks_end_date);
3459 l_document := hr_utility.get_message;
3460 return l_document;
3461 exception
3462 when others then
3463 hr_utility.set_message(8302,'PQH_WF_WARNING_NOTICE_FAIL');
3464 hr_utility.set_message_token('TRANSACTION_ID',p_transaction_id);
3465 l_document := hr_utility.get_message;
3466 return l_document;
3467 END warning_notification;
3468 FUNCTION respond_notification (p_transaction_id in number) RETURN varchar2
3469 is
3470 l_document varchar2(4000);
3471 l_proc varchar2(61) := g_package||'respond_notification' ;
3472 l_budget_name varchar2(30);
3473 l_worksheet_name varchar2(30);
3474 l_budget_version number;
3475 l_worksheet_mode varchar2(60);
3476 l_budget_entity varchar2(30);
3477 l_budget_style varchar2(30);
3478 l_organization_name varchar2(60);
3479 l_tran_cat_name varchar2(60);
3480 l_tran_cat_id number;
3481 l_bgt_start_date date;
3482 l_bgt_end_date date;
3483 l_wks_start_date date;
3484 l_wks_end_date date;
3485 BEGIN
3486 hr_utility.set_location('inside respond notification'||l_proc,10);
3487 build_wks_notice(p_transaction_id => p_transaction_id,
3488 p_worksheet_name => l_worksheet_name,
3489 p_budget_name => l_budget_name,
3490 p_tran_cat_name => l_tran_cat_name,
3491 p_organization_name => l_organization_name,
3492 p_wks_start_date => l_wks_start_date,
3493 p_wks_end_date => l_wks_end_date,
3494 p_bgt_start_date => l_bgt_start_date,
3495 p_bgt_end_date => l_bgt_end_date,
3496 p_worksheet_mode => l_worksheet_mode,
3497 p_budget_style => l_budget_style,
3498 p_budget_entity => l_budget_entity,
3499 p_budget_version => l_budget_version);
3500 hr_utility.set_message(8302,'PQH_WORKFLOW_RESPOND_NOTICE');
3501 hr_utility.set_message_token('WORKSHEET_NAME',l_worksheet_name);
3502 hr_utility.set_message_token('BUDGET_NAME',l_budget_name);
3503 hr_utility.set_message_token('TRANSACTION_CATEGORY',l_tran_cat_name);
3504 hr_utility.set_message_token('BUDGET_VERSION',l_budget_version);
3505 hr_utility.set_message_token('BUDGET_STYLE',l_budget_style);
3506 hr_utility.set_message_token('BUDGET_ENTITY',l_budget_entity);
3507 hr_utility.set_message_token('WORKSHEET_MODE',l_worksheet_mode);
3508 hr_utility.set_message_token('ORGANIZATION_NAME',l_organization_name);
3509 hr_utility.set_message_token('BUDGET_START_DATE',l_bgt_start_date);
3510 hr_utility.set_message_token('BUDGET_END_DATE',l_bgt_end_date);
3511 hr_utility.set_message_token('WORKSHEET_START_DATE',l_wks_start_date);
3512 hr_utility.set_message_token('WORKSHEET_END_DATE',l_wks_end_date);
3513 l_document := hr_utility.get_message;
3514 return l_document;
3515 exception
3516 when others then
3517 hr_utility.set_message(8302,'PQH_WF_RESPOND_NOTICE_FAIL');
3518 hr_utility.set_message_token('TRANSACTION_ID',p_transaction_id);
3519 l_document := hr_utility.get_message;
3520 return l_document;
3521 END respond_notification;
3522 --------------------------------------------------------------------------------------------------------------
3523 -- end added by Sumit Goyal
3524 --------------------------------------------------------------------------------------------------------------
3525
3526 -- added as per Dinesh's rqmt
3527 --------------------------------------------------------------------------------------------------------------
3528
3529 --------------------------------------------------------------------------------------------------------------
3530
3531 FUNCTION set_status
3532 (
3533 p_transaction_category_id IN pqh_transaction_categories.transaction_category_id%TYPE,
3534 p_transaction_id IN pqh_worksheets.worksheet_id%TYPE,
3535 p_status IN pqh_worksheets.transaction_status%TYPE
3536 ) RETURN varchar2 IS
3537 /*
3538 This procedure will update the wks status and wks detail status
3539 */
3540
3541 l_proc varchar2(72) := g_package||'set_status';
3542
3543 -- commented to remove dependency on pqh_transactions_v
3544 -- cursor c1 is select transaction_id,parent_transaction_id,transaction_status
3545 -- from pqh_transactions_v
3546 -- where parent_transaction_id = p_transaction_id
3547 -- and parent_transaction_id <> transaction_id
3548 -- and transaction_category_id = p_transaction_category_id
3549 -- and NVL(transaction_status,'X') <> p_status;
3550 --
3551
3552 cursor c1 is select wkd.worksheet_detail_id transaction_id,
3553 wkd.parent_worksheet_detail_id parent_transaction_id,
3554 wkd.status transaction_status
3555 from pqh_worksheet_details wkd, pqh_worksheets wks
3556 where parent_worksheet_detail_id = p_transaction_id
3557 and parent_worksheet_detail_id <> worksheet_detail_id
3558 and wkd.worksheet_id = wks.worksheet_id
3559 and wks.wf_transaction_category_id = p_transaction_category_id
3560 and NVL(wkd.status,'X') <> p_status;
3561
3562 CURSOR csr_wks IS
3563 SELECT wks.*
3564 FROM pqh_worksheets wks
3565 , pqh_worksheet_details wkd
3566 WHERE wkd.worksheet_detail_id = p_transaction_id
3567 and wks.worksheet_id = wkd.worksheet_id;
3568
3569 CURSOR csr_wdt IS
3570 SELECT wdt.*
3571 FROM pqh_worksheet_details wdt
3572 WHERE wdt.worksheet_detail_id = p_transaction_id;
3573
3574 l_wks_rec pqh_worksheets%ROWTYPE;
3575 l_wdt_rec pqh_worksheet_details%ROWTYPE;
3576 l_object_version_number pqh_worksheets.object_version_number%TYPE;
3577 l_object_version_number_wdt pqh_worksheet_details.object_version_number%TYPE;
3578 l_do_action boolean := FALSE;
3579 l_return_status varchar2(20);
3580
3581 BEGIN
3582 hr_utility.set_location('Entering:'||p_transaction_id||l_proc, 5);
3583 for i in c1 loop
3584 hr_utility.set_location('calling:'||i.transaction_id||l_proc, 10);
3585 l_return_status := set_status(p_transaction_category_id => p_transaction_category_id,
3586 p_transaction_id => i.transaction_id,
3587 p_status => p_status );
3588 end loop; -- for loop
3589 OPEN csr_wdt;
3590 FETCH csr_wdt INTO l_wdt_rec;
3591 CLOSE csr_wdt;
3592 if l_wdt_rec.parent_worksheet_detail_id is null and l_wdt_rec.status in ('PENDING','APPROVED','SUBMITTED') then
3593 l_do_action := TRUE;
3594 elsif l_wdt_rec.parent_worksheet_detail_id is not null and l_wdt_rec.status in ('DELEGATED','PENDING') then
3595 l_do_action := TRUE;
3596 else
3597 l_do_action := FALSE;
3598 end if;
3599 if l_do_action then
3600 if l_wdt_rec.parent_worksheet_detail_id is not null and l_wdt_rec.status ='PENDING' then
3601 hr_utility.set_location('changing just stat'||l_proc, 10);
3602 else
3603 BEGIN
3604 wf_engine.AbortProcess
3605 (itemtype => 'PQHGEN',
3606 itemkey => p_transaction_category_id || '-' || p_transaction_id,
3607 process => 'PQH_ROUTING',
3608 result => null
3609 );
3610 EXCEPTION
3611 WHEN OTHERS THEN
3612 null;
3613 END ; -- for abort process
3614 end if;
3615 l_object_version_number_wdt := l_wdt_rec.object_version_number;
3616 pqh_worksheet_details_api.update_worksheet_detail
3617 (
3618 p_validate => false
3619 ,p_worksheet_detail_id => p_transaction_id
3620 ,p_object_version_number => l_object_version_number_wdt
3621 ,p_status => p_status
3622 ,p_effective_date => sysdate
3623 );
3624 if l_wdt_rec.parent_worksheet_detail_id is null then
3625 OPEN csr_wks;
3626 FETCH csr_wks INTO l_wks_rec;
3627 CLOSE csr_wks;
3628 l_object_version_number := l_wks_rec.object_version_number;
3629 pqh_worksheets_api.update_worksheet
3630 (
3631 p_validate => false
3632 ,p_worksheet_id => l_wks_rec.worksheet_id
3633 ,p_object_version_number => l_object_version_number
3634 ,p_transaction_status => p_status
3635 ,p_effective_date => sysdate
3636 );
3637 end if;
3638 end if;
3639 hr_utility.set_location('Leaving:'||l_proc, 1000);
3640 RETURN 'SUCCESS';
3641 EXCEPTION
3642 WHEN OTHERS THEN
3643 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3644 hr_utility.set_message_token('ROUTINE', l_proc);
3645 hr_utility.set_message_token('REASON', SQLERRM);
3646 hr_utility.raise_error;
3647 hr_utility.set_location('Leaving: EXCEPTION '||l_proc, 1000);
3648 RETURN 'FAILURE';
3649 END set_status;
3650
3651
3652 --------------------------------------------------------------------------------------------------------------
3653
3654 END; -- Package Body PQH_APPLY_BUDGET