1 PACKAGE BODY pqh_wks_error_chk AS
2 /* $Header: pqwkserr.pkb 115.27 2004/06/15 13:58:59 rthiagar ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_wks_error_chk.'; -- Global package name
9 --
10 g_budget_unit1_id pqh_budgets.budget_unit1_id%TYPE;
11 g_budget_unit2_id pqh_budgets.budget_unit2_id%TYPE;
12 g_budget_unit3_id pqh_budgets.budget_unit3_id%TYPE;
13 g_budgeted_entity_cd pqh_budgets.budgeted_entity_cd%TYPE;
14 g_budget_name pqh_budgets.budget_name%TYPE;
15 g_worksheet_mode_cd pqh_worksheets.worksheet_mode_cd%TYPE;
16 g_table_route_id_wdt NUMBER;
17 g_table_route_id_wpr NUMBER;
18 g_table_route_id_wst NUMBER;
19 g_table_route_id_wel NUMBER;
20 g_table_route_id_wfs NUMBER;
21 g_worksheet_name pqh_worksheets.worksheet_name%TYPE;
22 g_worksheet_id pqh_worksheets.worksheet_id%TYPE;
23 g_error_exception exception;
24 g_curr_wks_dtl_level NUMBER;
25 g_batch_status VARCHAR2(30);
26 g_position_control pqh_budgets.position_control_flag%TYPE;
27 g_budget_id pqh_budgets.budget_id%TYPE;
28 g_budget_start_dt pqh_budgets.budget_start_date%TYPE;
29 g_budget_end_dt pqh_budgets.budget_end_date%TYPE;
30 g_budget_unit1_aggregate pqh_budgets.budget_unit1_aggregate%TYPE;
31 g_budget_unit2_aggregate pqh_budgets.budget_unit2_aggregate%TYPE;
32 g_budget_unit3_aggregate pqh_budgets.budget_unit3_aggregate%TYPE;
33 g_budget_style_cd pqh_budgets.budget_style_cd%TYPE;
34 g_context_organization_id number;
35
36 function get_organization_id (p_worksheet_detail_id in number) return number is
37 l_position_id number;
38 l_job_id number;
39 l_grade_id number;
40 l_organization_id number;
41 l_position_transaction_id number;
42 l_res_organization_id number;
43 cursor c1 is select position_id,organization_id,job_id,grade_id,position_transaction_id
44 from pqh_worksheet_details
45 where worksheet_detail_id = p_worksheet_detail_id;
46 cursor c2 is select business_group_id from hr_positions
47 where position_id = l_position_id;
48 cursor c3 is select business_group_id from per_jobs
49 where job_id = l_job_id;
50 cursor c4 is select business_group_id from per_grades
51 where grade_id = l_grade_id;
52 cursor c5 is select business_group_id from pqh_position_transactions
53 where position_transaction_id = l_position_transaction_id;
54 begin
55 open c1;
56 fetch c1 into l_position_id,l_organization_id,l_job_id,l_grade_id,l_position_transaction_id;
57 close c1;
58 if l_organization_id is null then
59 if l_position_id is not null then
60 open c2;
61 fetch c2 into l_res_organization_id;
62 close c2;
63 elsif l_job_id is not null then
64 open c3;
65 fetch c3 into l_res_organization_id;
66 close c3;
67 elsif l_grade_id is not null then
68 open c4;
69 fetch c4 into l_res_organization_id;
70 close c4;
71 elsif l_position_transaction_id is not null then
72 open c5;
73 fetch c5 into l_res_organization_id;
74 close c5;
75 end if;
76 else
77 l_res_organization_id := l_organization_id;
78 end if;
79 return l_res_organization_id;
80 end get_organization_id;
81
82
83
84
85
86 --
87 /*----------------------------------------------------------------
88 || PROCEDURE : check_wks_errors -- This is the MAIN procedure
89 ||
90 ------------------------------------------------------------------*/
91
92 PROCEDURE check_wks_errors
93 (
94 p_worksheet_detail_id IN pqh_worksheet_details.worksheet_detail_id%TYPE,
95 p_status OUT NOCOPY varchar2
96 ) IS
97 /*
98
99 p_status = SUCCESS if no errors for the batch else it is ERROR or WARNING
100
101 */
102 -- local variables and cursors
103
104 CURSOR pqh_worksheet_details_cur IS
105 SELECT level, wdt.*
106 FROM pqh_worksheet_details wdt
107 START WITH worksheet_detail_id = p_worksheet_detail_id
108 CONNECT BY prior worksheet_detail_id = parent_worksheet_detail_id ;
109
110 CURSOR pqh_worksheet_periods_cur (p_worksheet_detail_id IN pqh_worksheet_details.worksheet_detail_id%TYPE) IS
111 SELECT *
112 FROM pqh_worksheet_periods
113 WHERE worksheet_detail_id = p_worksheet_detail_id;
114
115 CURSOR pqh_worksheet_budget_sets_cur (p_worksheet_period_id IN pqh_worksheet_periods.worksheet_period_id%TYPE) IS
116 SELECT *
117 FROM pqh_worksheet_budget_sets
118 WHERE worksheet_period_id = p_worksheet_period_id;
119
120 CURSOR pqh_worksheet_bdgt_elmnts_cur (p_worksheet_budget_set_id IN pqh_worksheet_budget_sets.worksheet_budget_set_id%TYPE) IS
121 SELECT *
122 FROM pqh_worksheet_bdgt_elmnts
123 WHERE worksheet_budget_set_id = p_worksheet_budget_set_id;
124
125 CURSOR pqh_worksheet_fund_srcs_cur (p_worksheet_bdgt_elmnt_id IN pqh_worksheet_bdgt_elmnts.worksheet_bdgt_elmnt_id%TYPE) IS
126 SELECT *
127 FROM pqh_worksheet_fund_srcs
128 WHERE worksheet_bdgt_elmnt_id = p_worksheet_bdgt_elmnt_id;
129
130 CURSOR wks_detail_id_input_cur IS
131 SELECT *
132 FROM pqh_worksheet_details
133 WHERE worksheet_detail_id = p_worksheet_detail_id;
134
135
136 l_proc varchar2(72) := g_package||'check_wks_errors';
137 l_pqh_worksheets_rec pqh_worksheets%ROWTYPE;
138 l_pqh_worksheet_details_rec pqh_worksheet_details%ROWTYPE;
139 l_pqh_worksheet_periods_rec pqh_worksheet_periods%ROWTYPE;
140 l_pqh_worksheet_budget_set_rec pqh_worksheet_budget_sets%ROWTYPE;
141 l_pqh_worksheet_bdgt_elmnt_rec pqh_worksheet_bdgt_elmnts%ROWTYPE;
142 l_pqh_worksheet_fund_srcs_rec pqh_worksheet_fund_srcs%ROWTYPE;
143 l_message_text pqh_process_log.message_text%TYPE;
144 l_message_text_out fnd_new_messages.message_text%TYPE;
145 l_message_number_out fnd_new_messages.message_number%TYPE;
146 l_log_context pqh_process_log.log_context%TYPE;
147 l_pqh_worksheet_details_c_rec pqh_worksheet_details_cur%ROWTYPE;
148 l_level number;
149 l_batch_id number;
150 l_batch_context varchar2(2000);
151 l_date_status varchar2(10);
152
153
154 BEGIN
155
156 hr_utility.set_location('Entering: '||l_proc, 5);
157
158 -- populate the globals
159 populate_globals
160 (
161 p_worksheet_detail_id => p_worksheet_detail_id
162 );
163
164 -- compute batch id and batch context
165 -- Batch Id will be the WKS Detail ID passed to the pgm
166 -- Batch Context is worksheet dtl id context 03/28/2000
167
168 l_batch_id := p_worksheet_detail_id;
169
170 -- get log_context for the batch
171 set_wks_log_context
172 (
173 p_worksheet_detail_id => p_worksheet_detail_id,
174 p_log_context => l_log_context
175 );
176
177 l_batch_context := l_log_context;
178
179 -- Start the Log Process
180 pqh_process_batch_log.start_log
181 (
182 p_batch_id => l_batch_id,
183 p_module_cd => 'APPROVE_WORKSHEET',
184 p_log_context => l_batch_context
185 );
186
187 -- check that the worksheet_detail_id is root or delegated record else error and exit
188 OPEN wks_detail_id_input_cur;
189 FETCH wks_detail_id_input_cur INTO l_pqh_worksheet_details_rec;
190 CLOSE wks_detail_id_input_cur;
191
192 /*
193 -- get log_context
194 set_wks_log_context
195 (
196 p_worksheet_detail_id => p_worksheet_detail_id,
197 p_log_context => l_log_context
198 );
199
200 -- set the context before inserting error
201 pqh_process_batch_log.set_context_level
202 (
203 p_txn_id => p_worksheet_detail_id,
204 p_txn_table_route_id => g_table_route_id_wdt,
205 p_level => 1,
206 p_log_context => l_log_context
207 );
208
209 */
210 -- main check for input action cd in D or R
211 IF NVL(l_pqh_worksheet_details_rec.action_cd,'R') NOT IN ('D', 'R') THEN
212
213 -- get message text for PQH_WKS_INVALID_ID
214 FND_MESSAGE.SET_NAME('PQH','PQH_WKS_INVALID_ID');
215 l_message_text_out := FND_MESSAGE.GET;
216
217 l_message_text := l_message_text_out;
218
219 /*
220 -- insert error message
221 pqh_process_batch_log.insert_log
222 (
223 p_message_type_cd => 'ERROR',
224 p_message_text => l_message_text
225 );
226 */
227
228 -- end log and HALT the program here
229 raise g_error_exception;
230
231 END IF; -- main check for input action cd in D or R
232
233 -- check the worksheet dates
234 check_wks_dates
235 (
236 p_worksheet_detail_id => p_worksheet_detail_id,
237 p_status => l_date_status,
238 p_message => l_message_text
239 );
240
241 IF NVL(l_date_status,'ERROR') = 'ERROR' THEN
242 -- end log and HALT the program here
243 raise g_error_exception;
244 END IF; -- valid wks dates
245
246 -- open pqh_worksheet_details_cur
247 OPEN pqh_worksheet_details_cur ;
248 LOOP -- loop 1
249 FETCH pqh_worksheet_details_cur INTO l_pqh_worksheet_details_c_rec;
250 EXIT WHEN pqh_worksheet_details_cur%NOTFOUND;
251
252 -- current level
253 l_level := l_pqh_worksheet_details_c_rec.level ;
254
255 -- populate the global for current level
256 -- as wks detail is now the batch , actual level will be one less
257 g_curr_wks_dtl_level := l_pqh_worksheet_details_c_rec.level - 1;
258
259 IF g_curr_wks_dtl_level <> 0 THEN
260 -- this is NOT THE ROOT NODE
261
262 -- get log_context
263 set_wks_log_context
264 (
265 p_worksheet_detail_id => l_pqh_worksheet_details_c_rec.worksheet_detail_id,
266 p_log_context => l_log_context
267 );
268
269 -- set the context
270 pqh_process_batch_log.set_context_level
271 (
272 p_txn_id => l_pqh_worksheet_details_c_rec.worksheet_detail_id,
273 p_txn_table_route_id => g_table_route_id_wdt,
274 p_level => g_curr_wks_dtl_level,
275 p_log_context => l_log_context
276 );
277
278 -- check level1 rows of the worksheet_detail_id for Delegated OR Root record ONLY
279 IF NVL(l_pqh_worksheet_details_c_rec.action_cd,'R') IN ('D','R') THEN
280 check_level1_rows
281 (
282 p_worksheet_detail_id => l_pqh_worksheet_details_c_rec.worksheet_detail_id
283 );
284 END IF;
285
286 check_wks_details
287 (
288 p_worksheet_detail_id => l_pqh_worksheet_details_c_rec.worksheet_detail_id
289 );
290
291 ELSE
292 -- this is the ROOT INPUT NODE
293
294 -- check level1 rows of the worksheet_detail_id for Delegated OR Root record ONLY
295 IF NVL(l_pqh_worksheet_details_c_rec.action_cd,'R') IN ('D','R') THEN
296 check_level1_rows
297 (
298 p_worksheet_detail_id => l_pqh_worksheet_details_c_rec.worksheet_detail_id
299 );
300 END IF;
301
302 check_input_wks_details
303 (
304 p_worksheet_detail_id => l_pqh_worksheet_details_c_rec.worksheet_detail_id
305 );
306
307 END IF; -- for root node
308
309 g_context_organization_id := get_organization_id(p_worksheet_detail_id => l_pqh_worksheet_details_c_rec.worksheet_detail_id);
310 -- For app periods level = g_curr_wks_dtl_level + 1
311
312
313 -- open pqh_worksheet_periods_cur
314 OPEN pqh_worksheet_periods_cur(p_worksheet_detail_id => l_pqh_worksheet_details_c_rec.worksheet_detail_id);
315 LOOP -- loop 2
316 FETCH pqh_worksheet_periods_cur INTO l_pqh_worksheet_periods_rec;
317 EXIT WHEN pqh_worksheet_periods_cur%NOTFOUND;
318
319 -- get log_context
320 set_wpr_log_context
321 (
322 p_worksheet_period_id => l_pqh_worksheet_periods_rec.worksheet_period_id,
323 p_log_context => l_log_context
324 );
325
326 -- set the context
327 pqh_process_batch_log.set_context_level
328 (
329 p_txn_id => l_pqh_worksheet_periods_rec.worksheet_period_id,
330 p_txn_table_route_id => g_table_route_id_wpr,
331 p_level => g_curr_wks_dtl_level + 1,
332 p_log_context => l_log_context
333 );
334
335 check_wks_periods
336 (
337 p_worksheet_detail_id => l_pqh_worksheet_periods_rec.worksheet_detail_id,
338 p_worksheet_period_id => l_pqh_worksheet_periods_rec.worksheet_period_id
339 );
340
341 -- For all budget sets , current level = g_curr_wks_dtl_level + 2
342
343 -- open pqh_worksheet_budget_sets_cur
344 OPEN pqh_worksheet_budget_sets_cur(p_worksheet_period_id => l_pqh_worksheet_periods_rec.worksheet_period_id);
345 LOOP -- loop 3
346 FETCH pqh_worksheet_budget_sets_cur INTO l_pqh_worksheet_budget_set_rec;
347 EXIT WHEN pqh_worksheet_budget_sets_cur%NOTFOUND;
348
349 -- get log_context
350 set_wst_log_context
351 (
352 p_worksheet_budget_set_id => l_pqh_worksheet_budget_set_rec.worksheet_budget_set_id,
353 p_log_context => l_log_context
354 );
355
356 -- set the context
357 pqh_process_batch_log.set_context_level
358 (
359 p_txn_id => l_pqh_worksheet_budget_set_rec.worksheet_budget_set_id,
360 p_txn_table_route_id => g_table_route_id_wst,
361 p_level => g_curr_wks_dtl_level + 2,
362 p_log_context => l_log_context
363 );
364
365 check_wks_budget_sets
366 (
367 p_worksheet_period_id => l_pqh_worksheet_budget_set_rec.worksheet_period_id,
368 p_worksheet_budget_set_id => l_pqh_worksheet_budget_set_rec.worksheet_budget_set_id
369 );
370
371 -- For all elements current level = g_curr_wks_dtl_level + 3
372
373 -- open pqh_worksheet_bdgt_elmnts_cur
374 OPEN pqh_worksheet_bdgt_elmnts_cur(p_worksheet_budget_set_id => l_pqh_worksheet_budget_set_rec.worksheet_budget_set_id);
375 LOOP -- loop 4
376 FETCH pqh_worksheet_bdgt_elmnts_cur INTO l_pqh_worksheet_bdgt_elmnt_rec;
377 EXIT WHEN pqh_worksheet_bdgt_elmnts_cur%NOTFOUND;
378
379 -- get log_context
380 set_wel_log_context
381 (
382 p_worksheet_bdgt_elmnt_id => l_pqh_worksheet_bdgt_elmnt_rec.worksheet_bdgt_elmnt_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_bdgt_elmnt_rec.worksheet_bdgt_elmnt_id,
390 p_txn_table_route_id => g_table_route_id_wel,
391 p_level => g_curr_wks_dtl_level + 3,
392 p_log_context => l_log_context
393 );
394
395 check_wks_budget_elements
396 (
397 p_worksheet_budget_set_id => l_pqh_worksheet_bdgt_elmnt_rec.worksheet_budget_set_id,
398 p_worksheet_bdgt_elmnt_id => l_pqh_worksheet_bdgt_elmnt_rec.worksheet_bdgt_elmnt_id
399 );
400
401 -- For all funding srcs current level = g_curr_wks_dtl_level + 4
402
403 -- open pqh_worksheet_fund_srcs_cur
404 OPEN pqh_worksheet_fund_srcs_cur(p_worksheet_bdgt_elmnt_id => l_pqh_worksheet_bdgt_elmnt_rec.worksheet_bdgt_elmnt_id);
405 LOOP -- loop 5
406 FETCH pqh_worksheet_fund_srcs_cur INTO l_pqh_worksheet_fund_srcs_rec;
407 EXIT WHEN pqh_worksheet_fund_srcs_cur%NOTFOUND;
408
409 -- get log_context
410 set_wfs_log_context
411 (
412 p_worksheet_fund_src_id => l_pqh_worksheet_fund_srcs_rec.worksheet_fund_src_id,
413 p_log_context => l_log_context
414 );
415
416
417 -- set the context
418 pqh_process_batch_log.set_context_level
419 (
420 p_txn_id => l_pqh_worksheet_fund_srcs_rec.worksheet_fund_src_id,
421 p_txn_table_route_id => g_table_route_id_wfs,
422 p_level => g_curr_wks_dtl_level + 4,
423 p_log_context => l_log_context
424 );
425
426 check_wks_fund_srcs
427 (
428 p_worksheet_bdgt_elmnt_id => l_pqh_worksheet_fund_srcs_rec.worksheet_bdgt_elmnt_id,
429 p_worksheet_fund_src_id => l_pqh_worksheet_fund_srcs_rec.worksheet_fund_src_id
430 );
431
432
433 END LOOP; -- loop 5
434 CLOSE pqh_worksheet_fund_srcs_cur;
435
436 END LOOP; -- loop 4
437 CLOSE pqh_worksheet_bdgt_elmnts_cur;
438
439 END LOOP; -- loop 3
440 CLOSE pqh_worksheet_budget_sets_cur;
441
442 END LOOP; -- loop 2
443 CLOSE pqh_worksheet_periods_cur;
444
445 END LOOP; -- loop 1
446 CLOSE pqh_worksheet_details_cur;
447
448
449 -- commit propogation
450 -- commit;
451
452 -- end the error log process
453 end_log;
454
455 -- populate the out param
456 p_status := g_batch_status;
457
458
459 hr_utility.set_location('Leaving:'||l_proc, 1000);
460
461 EXCEPTION
462 WHEN g_error_exception THEN
463 -- update the out varchar
464 p_status := 'ERROR';
465 -- call the end log and stop
466 -- pqh_process_batch_log.end_log;
467 -- now we don't call the end log as there is no batch here
468 updt_batch
469 (
470 p_message_text => l_message_text
471 );
472 -- UPDATE pqh_process_log
473 -- SET message_type_cd = 'ERROR',
474 -- message_text = l_message_text,
475 -- txn_table_route_id = g_table_route_id_wdt,
476 -- batch_status = 'ERROR',
477 -- batch_end_date = sysdate
478 -- WHERE process_log_id = pqh_process_batch_log.g_master_process_log_id;
479 -- commit;
480 WHEN others THEN
481 p_status := 'ERROR';
482 raise;
483 END check_wks_errors;
484
485
486
487 /*----------------------------------------------------------------
488 || PROCEDURE : populate_globals
489 ||
490 ------------------------------------------------------------------*/
491
492
493 PROCEDURE populate_globals
494 (
495 p_worksheet_detail_id IN pqh_worksheet_details.worksheet_detail_id%TYPE
496 ) IS
497
498 /*
499 This procedure will populate all the global variables.
500 If the p_worksheet_detail_id is invalid we abort the program
501 New Rqmt : 03/28/2000
502 Worksheet detail Id passed to the program will be the TOP MOST NODE and NOT THE
503 worksheet Id as done previously
504
505 */
506
507 l_proc varchar2(72) := g_package||'populate_globals';
508 l_budgets_rec pqh_budgets%ROWTYPE;
509 l_worksheets_rec pqh_worksheets%ROWTYPE;
510
511 CURSOR csr_budget_rec IS
512 SELECT *
513 FROM pqh_budgets
514 WHERE budget_id =
515 (
516 SELECT b.budget_id
517 FROM pqh_budgets b, pqh_worksheets wks, pqh_worksheet_details wdt
518 WHERE wdt.worksheet_id = wks.worksheet_id
519 AND wks.budget_id = b.budget_id
520 AND wdt.worksheet_detail_id = p_worksheet_detail_id
521 );
522
523 CURSOR csr_worksheet_rec IS
524 SELECT *
525 FROM pqh_worksheets
526 WHERE worksheet_id =
527 (
528 SELECT wks.worksheet_id
529 FROM pqh_worksheets wks, pqh_worksheet_details wdt
530 WHERE wdt.worksheet_id = wks.worksheet_id
531 AND wdt.worksheet_detail_id = p_worksheet_detail_id
532 );
533
534 CURSOR csr_table_route (p_table_alias IN varchar2 )IS
535 SELECT table_route_id
536 FROM pqh_table_route
537 WHERE table_alias = p_table_alias;
538
539 BEGIN
540
541 hr_utility.set_location('Entering:'||l_proc, 5);
542
543 -- get budget units
544 OPEN csr_budget_rec;
545 FETCH csr_budget_rec INTO l_budgets_rec;
546 CLOSE csr_budget_rec;
547
548 g_budget_unit1_id := l_budgets_rec.budget_unit1_id;
549 g_budget_unit2_id := l_budgets_rec.budget_unit2_id;
550 g_budget_unit3_id := l_budgets_rec.budget_unit3_id;
551 g_budgeted_entity_cd := l_budgets_rec.budgeted_entity_cd;
552 g_budget_name := l_budgets_rec.budget_name;
553 g_position_control := l_budgets_rec.position_control_flag;
554 g_budget_id := l_budgets_rec.budget_id;
555 g_budget_start_dt := l_budgets_rec.budget_start_date;
556 g_budget_end_dt := l_budgets_rec.budget_end_date;
557 g_budget_unit1_aggregate := l_budgets_rec.budget_unit1_aggregate;
558 g_budget_unit2_aggregate := l_budgets_rec.budget_unit2_aggregate;
559 g_budget_unit3_aggregate := l_budgets_rec.budget_unit3_aggregate;
560 g_budget_style_cd := l_budgets_rec.budget_style_cd;
561
562
563 hr_utility.set_location('budget_unit1: '||g_budget_unit1_id, 11);
564 hr_utility.set_location('budget_unit2: '||g_budget_unit2_id, 15);
565 hr_utility.set_location('budget_unit3: '||g_budget_unit3_id, 20);
566 hr_utility.set_location('budgeted_entity_cd: '||g_budgeted_entity_cd, 21);
567
568
569 -- get worksheet mode
570 OPEN csr_worksheet_rec;
571 FETCH csr_worksheet_rec INTO l_worksheets_rec;
572 CLOSE csr_worksheet_rec;
573
574 g_worksheet_mode_cd := l_worksheets_rec.worksheet_mode_cd;
575 g_worksheet_name := l_worksheets_rec.worksheet_name;
576 g_worksheet_id := l_worksheets_rec.worksheet_id;
577
578 hr_utility.set_location('worksheet_mode_cd: '||g_worksheet_mode_cd, 25);
579 hr_utility.set_location('worksheet_name: '||g_worksheet_name, 30);
580 hr_utility.set_location('worksheet_id: '||g_worksheet_id, 40);
581
582 -- check if p_worksheet_detail_id is valid. If p_worksheet_detail_id is INVALID then
583 -- no worksheet record would be fetched . So abort
584
585 IF g_worksheet_id IS NULL THEN
586 -- get the message text PQH_INV_WKS_DTL_ID
587 FND_MESSAGE.SET_NAME('PQH','PQH_INV_WKS_DTL_ID');
588 APP_EXCEPTION.RAISE_EXCEPTION;
589 END IF;
590
591 -- get table_route_id for all the six worksheet tables
592
593 -- table_route_id for pqh_worksheet_details
594 OPEN csr_table_route (p_table_alias => 'WDT');
595 FETCH csr_table_route INTO g_table_route_id_wdt;
596 CLOSE csr_table_route;
597
598 -- table_route_id for pqh_worksheet_periods
599 OPEN csr_table_route (p_table_alias => 'WPR');
600 FETCH csr_table_route INTO g_table_route_id_wpr;
601 CLOSE csr_table_route;
602
603 -- table_route_id for pqh_worksheet_budget_sets
604 OPEN csr_table_route (p_table_alias => 'WST');
605 FETCH csr_table_route INTO g_table_route_id_wst;
606 CLOSE csr_table_route;
607
608 -- table_route_id for pqh_worksheet_bdgt_elmnts
609 OPEN csr_table_route (p_table_alias => 'WEL');
610 FETCH csr_table_route INTO g_table_route_id_wel;
611 CLOSE csr_table_route;
612
613 -- table_route_id for pqh_worksheet_fund_srcs
614 OPEN csr_table_route (p_table_alias => 'WFS');
615 FETCH csr_table_route INTO g_table_route_id_wfs;
616 CLOSE csr_table_route;
617
618 hr_utility.set_location('g_table_route_id_wdt: '||g_table_route_id_wdt, 50);
619 hr_utility.set_location('g_table_route_id_wpr: '||g_table_route_id_wpr, 60);
620 hr_utility.set_location('g_table_route_id_wst: '||g_table_route_id_wst, 70);
621 hr_utility.set_location('g_table_route_id_wel: '||g_table_route_id_wel, 80);
622 hr_utility.set_location('g_table_route_id_wfs: '||g_table_route_id_wfs, 90);
623
624 hr_utility.set_location('Leaving:'||l_proc, 1000);
625
626 EXCEPTION
627 WHEN OTHERS THEN
628 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
629 hr_utility.set_message_token('ROUTINE', l_proc);
630 hr_utility.set_message_token('REASON', SQLERRM);
631 hr_utility.raise_error;
632 END populate_globals;
633
634
635
636
637 /*----------------------------------------------------------------
638 || PROCEDURE : check_level1_rows
639 ||
640 ------------------------------------------------------------------*/
641
642
643 PROCEDURE check_level1_rows
644 (
645 p_worksheet_detail_id IN pqh_worksheet_details.worksheet_detail_id%TYPE
646 ) IS
647
648 /*
649 This procedure will check if all the level one rows below root or input node
650 which are delegated have null in old values and have available amounts > = 0
651 Here context_level = 2 as they are one below main input node
652 -- new change as of 03/23/2000
653 We will call this check_level1_rows at every node which is Delegated
654 If we find values in old values then we will call Sumit's procedure which will
655 propogate the changes and give the available values for the child row.
656 We will then update the child row with the new values
657
658 */
659
660 l_proc varchar2(72) := g_package||'check_level1_rows';
661 l_worksheet_details_rec pqh_worksheet_details%ROWTYPE;
662 l_message_text pqh_process_log.message_text%TYPE;
663 l_message_text_out fnd_new_messages.message_text%TYPE;
664 l_message_number_out fnd_new_messages.message_number%TYPE;
665 l_log_context pqh_process_log.log_context%TYPE;
666 l_error_flag varchar2(10) := 'N';
667
668 l_unit1_available pqh_worksheet_details.budget_unit1_available%TYPE;
669 l_unit2_available pqh_worksheet_details.budget_unit2_available%TYPE;
670 l_unit3_available pqh_worksheet_details.budget_unit3_available%TYPE;
671 l_unit1_precision number;
672 l_unit2_precision number;
673 l_unit3_precision number;
674 l_object_version_number pqh_worksheet_details.object_version_number%TYPE;
675 l_unit1_value pqh_worksheet_details.budget_unit1_value%TYPE;
676 l_unit2_value pqh_worksheet_details.budget_unit2_value%TYPE;
677 l_unit3_value pqh_worksheet_details.budget_unit3_value%TYPE;
678 l_propogate_status varchar2(30);
679
680
681 CURSOR csr_level1_rows IS
682 SELECT *
683 FROM pqh_worksheet_details
684 WHERE worksheet_detail_id IN
685 (
686 SELECT worksheet_detail_id
687 FROM pqh_worksheet_details
688 WHERE level = 2
689 AND action_cd = 'D'
690 START WITH worksheet_detail_id = p_worksheet_detail_id
691 CONNECT BY prior worksheet_detail_id = parent_worksheet_detail_id
692 )
693 FOR UPDATE OF budget_unit1_available,budget_unit2_available,budget_unit3_available;
694
695 BEGIN
696
697 hr_utility.set_location('Entering:'||l_proc, 5);
698
699 OPEN csr_level1_rows;
700 LOOP
701 FETCH csr_level1_rows INTO l_worksheet_details_rec;
702 EXIT WHEN csr_level1_rows%NOTFOUND;
703 /*
704 We don't report this as error but instead call Sumit's procedure
705 which would correct the error
706 */
707
708 /*
709 -- get log_context
710 set_wks_log_context
711 (
712 p_worksheet_detail_id => l_worksheet_details_rec.worksheet_detail_id,
713 p_log_context => l_log_context
714 );
715
716 -- set the context before inserting error
717 pqh_process_batch_log.set_context_level
718 (
719 p_txn_id => l_worksheet_details_rec.worksheet_detail_id,
720 p_txn_table_route_id => g_table_route_id_wdt,
721 p_level => 2,
722 p_log_context => l_log_context
723 );
724
725 */
726 -- CHECK # 1 check if old unit values are null
727
728 IF g_budget_style_cd = 'TOP_DOWN' THEN
729 --
730 IF ( l_worksheet_details_rec.old_unit1_value IS NOT NULL ) OR
731 ( l_worksheet_details_rec.old_unit2_value IS NOT NULL ) OR
732 ( l_worksheet_details_rec.old_unit3_value IS NOT NULL ) THEN
733
734
735 -- call to Sumit's procedure for TOP_DOWN
736 -- populate the in out variables
737 l_unit1_available := l_worksheet_details_rec.budget_unit1_available;
738 l_unit2_available := l_worksheet_details_rec.budget_unit2_available;
739 l_unit3_available := l_worksheet_details_rec.budget_unit3_available;
740 pqh_wks_budget.get_wkd_unit_precision(p_worksheet_detail_id => l_worksheet_details_rec.worksheet_detail_id,
741 p_unit1_precision => l_unit1_precision,
742 p_unit2_precision => l_unit2_precision,
743 p_unit3_precision => l_unit3_precision);
744 pqh_budget.propagate_worksheet_changes
745 (p_change_mode => l_worksheet_details_rec.propagation_method,
746 p_worksheet_detail_id => l_worksheet_details_rec.worksheet_detail_id,
747 p_budget_style_cd => g_budget_style_cd,
748 p_new_wks_unit1_value => l_worksheet_details_rec.budget_unit1_value,
749 p_new_wks_unit2_value => l_worksheet_details_rec.budget_unit2_value,
750 p_new_wks_unit3_value => l_worksheet_details_rec.budget_unit3_value,
751 p_unit1_aggregate => g_budget_unit1_aggregate,
752 p_unit2_aggregate => g_budget_unit2_aggregate,
753 p_unit3_aggregate => g_budget_unit3_aggregate,
754 p_unit1_precision => l_unit1_precision,
755 p_unit2_precision => l_unit2_precision,
756 p_unit3_precision => l_unit3_precision,
757 p_wks_unit1_available => l_unit1_available,
758 p_wks_unit2_available => l_unit2_available,
759 p_wks_unit3_available => l_unit3_available,
760 p_object_version_number => l_object_version_number
761 );
762
763 -- update the current record with the new available values
764 UPDATE pqh_worksheet_details
765 SET budget_unit1_available = l_unit1_available,
766 budget_unit2_available = l_unit2_available,
767 budget_unit3_available = l_unit3_available
768 WHERE CURRENT OF csr_level1_rows;
769
770
771 /*
772 -- get message text for PQH_WKS_DEL_CHANGES
773 -- Changes in the delegated organization ORG_NAME have not been applied
774
775 -- get message text for PQH_WKS_DEL_CHANGES
776 FND_MESSAGE.SET_NAME('PQH','PQH_WKS_DEL_CHANGES');
777 FND_MESSAGE.SET_TOKEN('ORG_NAME',l_log_context);
778 l_message_text_out := FND_MESSAGE.GET;
779
780 l_message_text := l_message_text_out;
781
782 -- set l_error_flag to Y
783 l_error_flag := 'Y';
784 */
785
786 END IF; -- check if old unit values are null for TOP_DOWN
787
788 ELSE
789 -- budget_style_cd is BOTTOM_UP
790 IF ( NVL(l_worksheet_details_rec.old_unit1_value,0) <> NVL(l_worksheet_details_rec.budget_unit1_value,0) ) OR
791 ( NVL(l_worksheet_details_rec.old_unit2_value,0) <> NVL(l_worksheet_details_rec.budget_unit2_value,0) ) OR
792 ( NVL(l_worksheet_details_rec.old_unit3_value,0) <> NVL(l_worksheet_details_rec.budget_unit3_value,0) ) THEN
793 -- call sumit's pkg for BOTTOM_UP
794 l_unit1_value := l_worksheet_details_rec.budget_unit1_value;
795 l_unit2_value := l_worksheet_details_rec.budget_unit2_value;
796 l_unit3_value := l_worksheet_details_rec.budget_unit3_value;
797
798 pqh_wks_budget.propagate_bottom_up
799 (p_worksheet_detail_id => l_worksheet_details_rec.worksheet_detail_id,
800 p_budget_unit1_value => l_unit1_value,
801 p_budget_unit2_value => l_unit2_value,
802 p_budget_unit3_value => l_unit3_value,
803 p_status => l_propogate_status
804 ) ;
805
806 -- update the current record with the new available values
807 UPDATE pqh_worksheet_details
808 SET budget_unit1_value = l_unit1_value,
809 budget_unit2_value = l_unit2_value,
810 budget_unit3_value = l_unit3_value
811 WHERE CURRENT OF csr_level1_rows;
812
813 END IF; -- for BOTTOM_UP
814
815
816
817
818 END IF; -- budget_style_cd
819
820
821
822 hr_utility.set_location('Error in Txn : '||l_worksheet_details_rec.worksheet_detail_id, 10);
823
824
825 /*
826 -- insert error message if l_error_flag is Y
827 IF l_error_flag = 'Y' THEN
828 pqh_process_batch_log.insert_log
829 (
830 p_message_type_cd => 'ERROR',
831 p_message_text => l_message_text
832 );
833 END IF; -- insert error message if l_error_flag is Y
834 */
835
836
837
838
839
840 END LOOP;
841 CLOSE csr_level1_rows;
842
843
844 hr_utility.set_location('Leaving:'||l_proc, 1000);
845
846 EXCEPTION
847 WHEN OTHERS THEN
848 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
849 hr_utility.set_message_token('ROUTINE', l_proc);
850 hr_utility.set_message_token('REASON', SQLERRM);
851 -- end log and halt the program here
852 raise g_error_exception;
853 END check_level1_rows;
854
855
856
857 /*----------------------------------------------------------------
858 || PROCEDURE : check_wks_details
859 ||
860 ------------------------------------------------------------------*/
861
862
863
864 PROCEDURE check_wks_details
865 (
866 p_worksheet_detail_id IN pqh_worksheet_details.worksheet_detail_id%TYPE
867 ) IS
868
869 /*
870 This procedure will check if all the wks detail records under the main node EXCLUDING
871 level 1 as they are already checked in check_level1_rows procedure and report all the errors
872 For budgeted records we check if there are rows in periods
873 */
874
875 l_proc varchar2(72) := g_package||'check_wks_details';
876 l_worksheet_details_rec pqh_worksheet_details%ROWTYPE;
877 l_message_text pqh_process_log.message_text%TYPE;
878 l_message_text_out fnd_new_messages.message_text%TYPE;
879 l_count number;
880 l_error_flag varchar2(10) := 'N';
881 l_pc_posn_status varchar2(30);
882 l_availability_status_id hr_positions.availability_status_id%type;
883
884
885 CURSOR csr_wks_details IS
886 SELECT *
887 FROM pqh_worksheet_details
888 WHERE worksheet_detail_id = p_worksheet_detail_id;
889
890 CURSOR csr_wks_periods_count IS
891 SELECT COUNT(*)
892 FROM pqh_worksheet_periods
893 WHERE worksheet_detail_id = p_worksheet_detail_id;
894
895 CURSOR csr_pos_availability(p_position_id l_worksheet_details_rec.position_id%type,
896 p_worksheet_id l_worksheet_details_rec.worksheet_id%type) IS
897 SELECT pos.availability_status_id
898 FROM hr_all_positions_f pos, pqh_worksheets wks, pqh_budgets bud
899 WHERE pos.position_id = p_position_id
900 AND wks.worksheet_id = p_worksheet_id
901 AND wks.budget_id = bud.budget_id
902 AND pos.effective_start_date < bud.budget_end_date
903 AND pos.effective_end_date > bud.budget_start_date;
904
905
906 BEGIN
907
908 hr_utility.set_location('Entering:'||l_proc, 5);
909
910 OPEN csr_wks_details;
911 FETCH csr_wks_details INTO l_worksheet_details_rec;
912 CLOSE csr_wks_details;
913
914 -- CHECK # 1,2, 3 ARE MUTUALLY EXCLUSIVE
915
916 /*
917 THIS IS DONE IN WORKSHEET DETAILS API
918 removed on 03/23/2000
919
920 -- CHECK # 1 check valid action code which is null for Root , B for budgeted and D for delegated
921
922 IF NVL(l_worksheet_details_rec.action_cd , 'R') NOT IN ( 'R','D','B' ) THEN
923
924 -- get message text for PQH_WKS_INVALID_ACTION_CD
925 -- message : Worksheet has invalid Action Code ACTION_CD
926 FND_MESSAGE.SET_NAME('PQH','PQH_WKS_INVALID_ACTION_CD');
927 FND_MESSAGE.SET_TOKEN('ACTION_CD',l_worksheet_details_rec.action_cd);
928 l_message_text_out := FND_MESSAGE.GET;
929
930
931 l_message_text := l_message_text_out;
932
933 -- set l_error_flag to Y
934 l_error_flag := 'Y';
935
936
937 hr_utility.set_location('Error in Txn : '||l_worksheet_details_rec.worksheet_detail_id, 10);
938
939
940 END IF; -- CHECK # 1
941
942 */
943
944 /*
945 THIS IS DONE IN WORKSHEET DETAILS API
946 removed on 03/23/2000
947
948 -- CHECK # 2 for delegated or Root record the unit values must be greater then zero
949
950 IF NVL(l_worksheet_details_rec.action_cd , 'R') IN ( 'R','D' ) THEN
951 -- this is a root or delegated record
952
953 IF ( g_budget_unit1_id IS NOT NULL ) AND
954 ( NVL(l_worksheet_details_rec.budget_unit1_value,0) < 0 ) THEN
955
956 IF l_error_flag = 'Y' THEN
957 -- there is already an error so append the message
958
959 -- get message text for PQH_WKS_INVALID_BDG_AMT
960 -- message : Worksheet Budgeted Amount Must be more then zero
961 FND_MESSAGE.SET_NAME('PQH','PQH_WKS_INVALID_BDG_AMT');
962 l_message_text_out := FND_MESSAGE.GET;
963
964 l_message_text := l_message_text||' **** '||l_message_text_out;
965 ELSE
966 -- new message
967 l_message_text := l_message_text_out;
968 END IF;
969
970 -- set l_error_flag to Y
971 l_error_flag := 'Y';
972
973 hr_utility.set_location('Error in Txn : '||l_worksheet_details_rec.worksheet_detail_id, 10);
974
975
976
977 ELSIF ( g_budget_unit2_id IS NOT NULL ) AND
978 ( NVL(l_worksheet_details_rec.budget_unit2_value,0) < 0 ) THEN
979
980 IF l_error_flag = 'Y' THEN
981 -- there is already an error so append the message
982
983 -- get message text for PQH_WKS_INVALID_BDG_AMT
984 -- message : Worksheet Budgeted Amount Must be more then zero
985 FND_MESSAGE.SET_NAME('PQH','PQH_WKS_INVALID_BDG_AMT');
986 l_message_text_out := FND_MESSAGE.GET;
987
988 l_message_text := l_message_text||' **** '||l_message_text_out;
989 ELSE
990 -- new message
991 l_message_text := l_message_text_out;
992 END IF;
993
994 -- set l_error_flag to Y
995 l_error_flag := 'Y';
996
997 hr_utility.set_location('Error in Txn : '||l_worksheet_details_rec.worksheet_detail_id, 10);
998
999
1000 ELSIF ( g_budget_unit3_id IS NOT NULL ) AND
1001 ( NVL(l_worksheet_details_rec.budget_unit3_value,0) < 0 ) THEN
1002
1003 IF l_error_flag = 'Y' THEN
1004 -- there is already an error so append the message
1005
1006 -- get message text for PQH_WKS_INVALID_BDG_AMT
1007 -- message : Worksheet Budgeted Amount Must be more then zero
1008 FND_MESSAGE.SET_NAME('PQH','PQH_WKS_INVALID_BDG_AMT');
1009 l_message_text_out := FND_MESSAGE.GET;
1010
1011 l_message_text := l_message_text||' **** '||l_message_text_out;
1012 ELSE
1013 -- new message
1014 l_message_text := l_message_text_out;
1015 END IF;
1016
1017 -- set l_error_flag to Y
1018 l_error_flag := 'Y';
1019
1020 hr_utility.set_location('Error in Txn : '||l_worksheet_details_rec.worksheet_detail_id, 10);
1021
1022
1023
1024 END IF;
1025
1026 END IF; -- CHECK # 2 for delegated or Root record the unit values must be greater then zero
1027
1028 */
1029
1030
1031 -- CHECK # 3 for budgeted records check rows in periods
1032 -- if position control then check if position is not budgeted in any other budget
1033 IF NVL(l_worksheet_details_rec.action_cd , 'R') ='B' THEN
1034 -- this is budgeted record
1035 OPEN csr_wks_periods_count;
1036 FETCH csr_wks_periods_count INTO l_count;
1037 CLOSE csr_wks_periods_count;
1038
1039 IF NVL(l_count,0) = 0 THEN
1040
1041 hr_utility.set_location('WKS Detail Error 3 PQH_WKS_NO_PERIODS '||l_worksheet_details_rec.worksheet_detail_id,10);
1042
1043 -- get message text for PQH_WKS_NO_PERIODS
1044 -- message : No Periods Defined for the budgeted entity
1045 FND_MESSAGE.SET_NAME('PQH','PQH_WKS_NO_PERIODS');
1046 l_message_text_out := FND_MESSAGE.GET;
1047
1048 IF l_error_flag = 'Y' THEN
1049 -- there is already an error so append the message
1050
1051 l_message_text := l_message_text||' **** '||l_message_text_out;
1052 ELSE
1053 -- new message
1054 l_message_text := l_message_text_out;
1055 END IF;
1056
1057 -- set l_error_flag to Y
1058 l_error_flag := 'Y';
1059
1060
1061 END IF; -- l_count = 0
1062
1063 OPEN csr_pos_availability(l_worksheet_details_rec.position_id,
1064 l_worksheet_details_rec.worksheet_id);
1065 FETCH csr_pos_availability into l_availability_status_id;
1066 CLOSE csr_pos_availability;
1067
1068 IF pqh_wks_budget.get_position_budget_flag(l_availability_status_id) = 'N' THEN
1069 FND_MESSAGE.SET_NAME('PQH','PQH_WKS_WRONG_POSITION');
1070 FND_MESSAGE.SET_TOKEN('STATUS',HR_GENERAL.DECODE_AVAILABILITY_STATUS(l_availability_status_id));
1071
1072 l_message_text_out := FND_MESSAGE.GET;
1073
1074 IF l_error_flag = 'Y' THEN
1075 -- there is already an error so append the message
1076
1077 l_message_text := l_message_text||' **** '||l_message_text_out;
1078 ELSE
1079 -- new message
1080 l_message_text := l_message_text_out;
1081 END IF;
1082
1083 -- set l_error_flag to Y
1084 l_error_flag := 'Y';
1085
1086 END IF;
1087
1088 /*
1089 The Posn Control Check is now done at Budget Version Level as of 03/22/2001
1090
1091 IF NVL(g_position_control,'N') = 'Y' AND l_worksheet_details_rec.position_id IS NOT NULL THEN
1092
1093 -- for a pc budget we check if the position budgeted is budgeted in any other budget , if yes we
1094 -- check if the Fiscal Period for the current budget and the other budget overlap, if yes we report this
1095 -- as error
1096
1097 check_pc_posn
1098 (
1099 p_position_id => l_worksheet_details_rec.position_id,
1100 p_status => l_pc_posn_status
1101 );
1102
1103 IF l_pc_posn_status = 'ERROR' THEN
1104 hr_utility.set_location('WKS Detail Error 3.1 PQH_PC_POSN_INVALID '||l_worksheet_details_rec.position_id,11);
1105 -- get message text for PQH_PC_POSN_INVALID
1106 -- message : Position is already budgeted in another budget for the same Fiscal period
1107 FND_MESSAGE.SET_NAME('PQH','PQH_PC_POSN_INVALID');
1108 l_message_text_out := FND_MESSAGE.GET;
1109
1110 IF l_error_flag = 'Y' THEN
1111 -- there is already an error so append the message
1112
1113 l_message_text := l_message_text||' **** '||l_message_text_out;
1114 ELSE
1115 -- new message
1116 l_message_text := l_message_text_out;
1117 END IF;
1118
1119 -- set l_error_flag to Y
1120 l_error_flag := 'Y';
1121 END IF; -- for pc_posn_status = ERROR
1122 END IF; -- pc budget for posn check
1123
1124 */
1125
1126 END IF; -- CHECK # 3 for budgeted records check rows in periods
1127
1128
1129 -- CHECK # 4 check if available amount is >= 0 for all records
1130
1131 IF ( NVL(l_worksheet_details_rec.budget_unit1_available,0) < 0 ) OR
1132 ( NVL(l_worksheet_details_rec.budget_unit2_available,0) < 0 ) OR
1133 ( NVL(l_worksheet_details_rec.budget_unit3_available,0) < 0 ) THEN
1134
1135 hr_utility.set_location('WKS Detail Error 4 PQH_WKS_DEL_ALL_AMT '||l_worksheet_details_rec.worksheet_detail_id,10);
1136
1137 -- get message text for PQH_WKS_DEL_ALL_AMT
1138 -- message : Budget Amount in the delegated organization exceeds the allocated amount
1139 FND_MESSAGE.SET_NAME('PQH','PQH_WKS_DEL_ALL_AMT');
1140 l_message_text_out := FND_MESSAGE.GET;
1141
1142 IF l_error_flag = 'Y' THEN
1143 -- there is already an error so append the message
1144
1145 l_message_text := l_message_text||' **** '||l_message_text_out;
1146 ELSE
1147 -- new message
1148 l_message_text := l_message_text_out;
1149 END IF;
1150
1151 -- set l_error_flag to Y
1152 l_error_flag := 'Y';
1153
1154
1155 END IF; -- CHECK # 4 check if available amount is >= 0
1156
1157 -- CHECK # 5 for position records check if position_id exists for the position_transaction_id
1158 IF NVL(l_worksheet_details_rec.action_cd , 'R') ='B' THEN
1159 -- this is budgeted record
1160
1161 IF l_worksheet_details_rec.position_transaction_id IS NOT NULL THEN
1162 -- posn txn id exists, check if position id exists
1163 IF l_worksheet_details_rec.position_id IS NULL THEN
1164 hr_utility.set_location('WKS Detail Error 5 PQH_WKS_NO_POSITION '||l_worksheet_details_rec.worksheet_detail_id,10);
1165
1166 -- get message text for PQH_WKS_NO_POSITION
1167 -- message : Position is not created for this position transaction
1168 FND_MESSAGE.SET_NAME('PQH','PQH_WKS_NO_POSITION');
1169 l_message_text_out := FND_MESSAGE.GET;
1170
1171 IF l_error_flag = 'Y' THEN
1172 -- there is already an error so append the message
1173
1174 l_message_text := l_message_text||' **** '||l_message_text_out;
1175 ELSE
1176 -- new message
1177 l_message_text := l_message_text_out;
1178 END IF;
1179
1180 -- set l_error_flag to Y
1181 l_error_flag := 'Y';
1182
1183 END IF; -- position id is null
1184
1185 END IF; -- for position_transaction_id
1186
1187 END IF; -- CHECK # 5 budgeted record
1188
1189
1190 hr_utility.set_location('Error Flag : '||l_error_flag,10);
1191
1192 -- insert error message if l_error_flag is Y
1193 IF l_error_flag = 'Y' THEN
1194 pqh_process_batch_log.insert_log
1195 (
1196 p_message_type_cd => 'ERROR',
1197 p_message_text => l_message_text
1198 );
1199 END IF; -- insert error message if l_error_flag is Y
1200
1201
1202
1203 hr_utility.set_location('Leaving:'||l_proc, 10);
1204
1205 EXCEPTION
1206 WHEN OTHERS THEN
1207 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1208 hr_utility.set_message_token('ROUTINE', l_proc);
1209 hr_utility.set_message_token('REASON', SQLERRM);
1210 -- end log and halt the program here
1211 raise g_error_exception;
1212 END check_wks_details;
1213
1214
1215 /*----------------------------------------------------------------
1216 || PROCEDURE : check_wks_periods
1217 ||
1218 ------------------------------------------------------------------*/
1219
1220
1221
1222 PROCEDURE check_wks_periods
1223 (
1224 p_worksheet_detail_id IN pqh_worksheet_details.worksheet_detail_id%TYPE,
1225 p_worksheet_period_id IN pqh_worksheet_periods.worksheet_period_id%TYPE
1226 ) IS
1227
1228 /*
1229 This procedure will be called ONLY for Budgeted Worksheet records
1230 This procedure will check if all the wks period records under the wks detail
1231 and report all the errors
1232 */
1233
1234 l_proc varchar2(72) := g_package||'check_wks_periods';
1235 l_worksheet_periods_rec pqh_worksheet_periods%ROWTYPE;
1236 l_message_text pqh_process_log.message_text%TYPE;
1237 l_message_text_out fnd_new_messages.message_text%TYPE;
1238 l_count NUMBER;
1239 l_error_flag varchar2(10) := 'N';
1240 l_message_type varchar2(10) := 'E';
1241 l_warnings_rec pqh_utility.warnings_rec;
1242
1243
1244 CURSOR csr_wks_periods IS
1245 SELECT *
1246 FROM pqh_worksheet_periods
1247 WHERE worksheet_period_id = p_worksheet_period_id;
1248
1249 CURSOR csr_wks_periods_count IS
1250 SELECT COUNT(*)
1251 FROM pqh_worksheet_periods
1252 WHERE worksheet_detail_id = p_worksheet_detail_id;
1253
1254 CURSOR csr_wks_bset_count IS
1255 SELECT COUNT(*)
1256 FROM pqh_worksheet_budget_sets
1257 WHERE worksheet_period_id = p_worksheet_period_id;
1258
1259
1260 BEGIN
1261
1262 hr_utility.set_location('Entering:'||l_proc, 5);
1263
1264
1265 OPEN csr_wks_periods;
1266 FETCH csr_wks_periods INTO l_worksheet_periods_rec;
1267 CLOSE csr_wks_periods;
1268
1269 -- CHECK # 1 if budget sets defined for the period else error
1270 OPEN csr_wks_bset_count;
1271 FETCH csr_wks_bset_count INTO l_count;
1272 CLOSE csr_wks_bset_count;
1273
1274 IF NVL(l_count,0) = 0 THEN
1275 -- get message text for PQH_WKS_NO_BSETS
1276 -- message : No budget Sets have been defined for the budgeted period.
1277 FND_MESSAGE.SET_NAME('PQH','PQH_WKS_NO_BSETS');
1278 l_message_text_out := FND_MESSAGE.GET;
1279
1280 l_message_text := l_message_text_out;
1281
1282 -- set l_error_flag to Y
1283 l_error_flag := 'Y';
1284
1285 END IF; -- for budget sets defined
1286
1287 /*
1288 THIS IS DONE IN WORKSHEET DETAILS API
1289 removed on 03/23/2000
1290
1291 -- CHECK # 1 check if unit values are >= 0
1292
1293 IF ( NVL(l_worksheet_periods_rec.budget_unit1_value,0) < 0 ) OR
1294 ( NVL(l_worksheet_periods_rec.budget_unit2_value,0) < 0 ) OR
1295 ( NVL(l_worksheet_periods_rec.budget_unit3_value,0) < 0 ) THEN
1296
1297 -- get message text for PQH_WKS_INVALID_PERIOD_VAL
1298 -- message : Budget Period Values must be more then zero
1299 FND_MESSAGE.SET_NAME('PQH','PQH_WKS_INVALID_PERIOD_VAL');
1300 l_message_text_out := FND_MESSAGE.GET;
1301
1302 l_message_text := l_message_text_out;
1303
1304 -- set l_error_flag to Y
1305 l_error_flag := 'Y';
1306
1307 END IF; -- CHECK # 1 check if unit values are >= 0
1308
1309 */
1310
1311 -- CHECK # 2 check if available amount is >= 0
1312
1313 IF ( NVL(l_worksheet_periods_rec.budget_unit1_available,0) < 0 ) OR
1314 ( NVL(l_worksheet_periods_rec.budget_unit2_available,0) < 0 ) OR
1315 ( NVL(l_worksheet_periods_rec.budget_unit3_available,0) < 0 ) THEN
1316
1317 -- get message text for PQH_WKS_INVALID_PERIOD_AMT
1318 -- message : Budget Period Amount exceeds the allocated amount
1319 pqh_utility.set_message(8302,'PQH_WKS_INVALID_PERIOD_AMT',g_context_organization_id);
1320 l_message_text_out := pqh_utility.get_message;
1321 l_message_type := pqh_utility.get_message_type_cd;
1322
1323 IF nvl(l_message_type,'E') = 'E' THEN
1324 -- this is a error
1325 IF l_error_flag = 'Y' THEN
1326 -- there is already an error so append the message
1327
1328 l_message_text := l_message_text||' **** '||l_message_text_out;
1329 ELSE
1330 -- new message
1331 l_message_text := l_message_text_out;
1332 END IF;
1333 -- set l_error_flag to Y
1334 l_error_flag := 'Y';
1335
1336 ELSIF nvl(l_message_type,'E') = 'W' THEN
1337 -- this is a warning
1338 l_warnings_rec.message_text := l_message_text_out;
1339 -- pqh_utility.init_warnings_table;
1340 pqh_utility.insert_warning
1341 (
1342 p_warnings_rec => l_warnings_rec
1343 );
1344 -- assign warning message
1345 l_message_text := l_message_text_out;
1346 -- insert warning into process log
1347 pqh_process_batch_log.insert_log
1348 (
1349 p_message_type_cd => 'WARNING',
1350 p_message_text => l_message_text
1351 );
1352 ELSE
1353 -- this is ignore
1354 hr_utility.set_location('Message Type in wks Periods is : '||l_message_type,6);
1355 END IF;
1356
1357
1358 /* -- added configurable rule on 10/13/2000
1359
1360 -- get message text for PQH_WKS_INVALID_PERIOD_AMT
1361 -- message : Budget Period Amount exceeds the allocated amount
1362 FND_MESSAGE.SET_NAME('PQH','PQH_WKS_INVALID_PERIOD_AMT');
1363 l_message_text_out := FND_MESSAGE.GET;
1364
1365 IF l_error_flag = 'Y' THEN
1366 -- there is already an error so append the message
1367
1368 l_message_text := l_message_text||' **** '||l_message_text_out;
1369 ELSE
1370 -- new message
1371 l_message_text := l_message_text_out;
1372 END IF;
1373
1374 -- set l_error_flag to Y
1375 l_error_flag := 'Y';
1376
1377 */
1378
1379 END IF; -- CHECK # 2 check if available amount is >= 0
1380
1381
1382 -- insert error message if l_error_flag is Y
1383 IF l_error_flag = 'Y' THEN
1384 pqh_process_batch_log.insert_log
1385 (
1386 p_message_type_cd => 'ERROR',
1387 p_message_text => l_message_text
1388 );
1389 END IF; -- insert error message if l_error_flag is Y
1390
1391
1392
1393
1394
1395
1396
1397 hr_utility.set_location('Leaving:'||l_proc, 10);
1398
1399 EXCEPTION
1400 WHEN OTHERS THEN
1401 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1402 hr_utility.set_message_token('ROUTINE', l_proc);
1403 hr_utility.set_message_token('REASON', SQLERRM);
1404 -- end log and halt the program here
1405 raise g_error_exception;
1406 END check_wks_periods;
1407
1408
1409
1410 /*----------------------------------------------------------------
1411 || PROCEDURE : check_wks_budget_sets
1412 ||
1413 ------------------------------------------------------------------*/
1414
1415
1416
1417 PROCEDURE check_wks_budget_sets
1418 (
1419 p_worksheet_period_id IN pqh_worksheet_periods.worksheet_period_id%TYPE,
1420 p_worksheet_budget_set_id IN pqh_worksheet_budget_sets.worksheet_budget_set_id%TYPE
1421 ) IS
1422
1423 /*
1424 This procedure will be called ONLY for Budgeted Worksheet records
1425 This procedure will check if all the wks budget sets records under the wks detail
1426 and report all the errors
1427 we check that if for the period the budget_unit1_value is more then zero, then
1428 its sum in the budget sets must be more then zero
1429 */
1430
1431 l_proc varchar2(72) := g_package||'check_wks_budget_sets';
1432 l_worksheet_budget_sets_rec pqh_worksheet_budget_sets%ROWTYPE;
1433 l_message_text pqh_process_log.message_text%TYPE;
1434 l_message_text_out fnd_new_messages.message_text%TYPE;
1435 l_unit1_sum NUMBER;
1436 l_unit2_sum NUMBER;
1437 l_unit3_sum NUMBER;
1438 l_worksheet_periods_rec pqh_worksheet_periods%ROWTYPE;
1439 l_message_type varchar2(10) := 'E';
1440 l_warnings_rec pqh_utility.warnings_rec;
1441
1442
1443
1444
1445
1446 CURSOR csr_wks_budget_sets_value IS
1447 SELECT SUM(budget_unit1_value),
1448 SUM(budget_unit2_value),
1449 SUM(budget_unit3_value)
1450 FROM pqh_worksheet_budget_sets
1451 WHERE worksheet_period_id = p_worksheet_period_id;
1452
1453 CURSOR csr_wks_worksheet_periods IS
1454 SELECT *
1455 FROM pqh_worksheet_periods
1456 WHERE worksheet_period_id = p_worksheet_period_id;
1457
1458
1459 BEGIN
1460
1461 hr_utility.set_location('Entering:'||l_proc, 5);
1462
1463 -- get the details for the current period
1464 OPEN csr_wks_worksheet_periods;
1465 FETCH csr_wks_worksheet_periods INTO l_worksheet_periods_rec;
1466 CLOSE csr_wks_worksheet_periods;
1467
1468 -- CHECK # 1 check if sum of unit values under the current period are > 0
1469
1470 OPEN csr_wks_budget_sets_value;
1471 FETCH csr_wks_budget_sets_value INTO l_unit1_sum, l_unit2_sum, l_unit3_sum;
1472 CLOSE csr_wks_budget_sets_value;
1473
1474
1475 IF ( g_budget_unit1_id IS NOT NULL ) AND
1476 ( NVL(l_worksheet_periods_rec.budget_unit1_value,0) > 0 ) AND
1477 ( NVL(l_unit1_sum,0) <= 0 ) THEN
1478
1479 -- get message text for PQH_WKS_INVALID_BSET_VAL
1480 -- message : Sum of Budget Sets value for a period must be more then zero
1481 pqh_utility.set_message(8302,'PQH_WKS_INVALID_BSET_VAL',g_context_organization_id);
1482 l_message_text_out := pqh_utility.get_message;
1483 l_message_type := pqh_utility.get_message_type_cd;
1484
1485 l_message_text := l_message_text_out;
1486
1487 IF nvl(l_message_type,'E') = 'E' THEN
1488 -- this is a error
1489 -- insert error message
1490 pqh_process_batch_log.insert_log
1491 (
1492 p_message_type_cd => 'ERROR',
1493 p_message_text => l_message_text
1494 );
1495 ELSIF nvl(l_message_type,'E') = 'W' THEN
1496 -- this is a warning
1497 l_warnings_rec.message_text := l_message_text_out;
1498 -- pqh_utility.init_warnings_table;
1499 pqh_utility.insert_warning
1500 (
1501 p_warnings_rec => l_warnings_rec
1502 );
1503 -- insert warning into process log
1504 pqh_process_batch_log.insert_log
1505 (
1506 p_message_type_cd => 'WARNING',
1507 p_message_text => l_message_text
1508 );
1509 ELSE
1510 -- this is ignore
1511 hr_utility.set_location('Message Type in wks Periods is : '||l_message_type,6);
1512 END IF; -- message_type
1513
1514
1515
1516
1517 /*
1518 -- get message text for PQH_WKS_INVALID_BSET_VAL
1519 -- message : Sum of Budget Sets value for a period must be more then zero
1520 FND_MESSAGE.SET_NAME('PQH','PQH_WKS_INVALID_BSET_VAL');
1521 l_message_text_out := FND_MESSAGE.GET;
1522
1523 l_message_text := l_message_text_out;
1524
1525 -- insert error message
1526 pqh_process_batch_log.insert_log
1527 (
1528 p_message_type_cd => 'ERROR',
1529 p_message_text => l_message_text
1530 );
1531 */
1532
1533
1534 ELSIF ( g_budget_unit2_id IS NOT NULL ) AND
1535 ( NVL(l_worksheet_periods_rec.budget_unit2_value,0) > 0 ) AND
1536 ( NVL(l_unit2_sum,0) <= 0 ) THEN
1537
1538
1539 -- get message text for PQH_WKS_INVALID_BSET_VAL
1540 -- message : Sum of Budget Sets value for a period must be more then zero
1541 pqh_utility.set_message(8302,'PQH_WKS_INVALID_BSET_VAL',g_context_organization_id);
1542 l_message_text_out := pqh_utility.get_message;
1543 l_message_type := pqh_utility.get_message_type_cd;
1544
1545 l_message_text := l_message_text_out;
1546
1547 IF nvl(l_message_type,'E') = 'E' THEN
1548 -- this is a error
1549 -- insert error message
1550 pqh_process_batch_log.insert_log
1551 (
1552 p_message_type_cd => 'ERROR',
1553 p_message_text => l_message_text
1554 );
1555 ELSIF nvl(l_message_type,'E') = 'W' THEN
1556 -- this is a warning
1557 l_warnings_rec.message_text := l_message_text_out;
1558 -- pqh_utility.init_warnings_table;
1559 pqh_utility.insert_warning
1560 (
1561 p_warnings_rec => l_warnings_rec
1562 );
1563 -- insert warning into process log
1564 pqh_process_batch_log.insert_log
1565 (
1566 p_message_type_cd => 'WARNING',
1567 p_message_text => l_message_text
1568 );
1569 ELSE
1570 -- this is ignore
1571 hr_utility.set_location('Message Type in wks Periods is : '||l_message_type,6);
1572 END IF; -- message_type
1573
1574
1575
1576
1577
1578 /*
1579 -- get message text for PQH_WKS_INVALID_BSET_VAL
1580 -- message : Sum of Budget Sets value for a period must be more then zero
1581 FND_MESSAGE.SET_NAME('PQH','PQH_WKS_INVALID_BSET_VAL');
1582 l_message_text_out := FND_MESSAGE.GET;
1583
1584 l_message_text := l_message_text_out;
1585
1586 -- insert error message
1587 pqh_process_batch_log.insert_log
1588 (
1589 p_message_type_cd => 'ERROR',
1590 p_message_text => l_message_text
1591 );
1592 */
1593
1594
1595
1596 ELSIF ( g_budget_unit3_id IS NOT NULL ) AND
1597 ( NVL(l_worksheet_periods_rec.budget_unit3_value,0) > 0 ) AND
1598 ( NVL(l_unit3_sum,0) <= 0 ) THEN
1599
1600
1601 -- get message text for PQH_WKS_INVALID_BSET_VAL
1602 -- message : Sum of Budget Sets value for a period must be more then zero
1603 pqh_utility.set_message(8302,'PQH_WKS_INVALID_BSET_VAL',g_context_organization_id);
1604 l_message_text_out := pqh_utility.get_message;
1605 l_message_type := pqh_utility.get_message_type_cd;
1606
1607 l_message_text := l_message_text_out;
1608
1609 IF nvl(l_message_type,'E') = 'E' THEN
1610 -- this is a error
1611 -- insert error message
1612 pqh_process_batch_log.insert_log
1613 (
1614 p_message_type_cd => 'ERROR',
1615 p_message_text => l_message_text
1616 );
1617 ELSIF nvl(l_message_type,'E') = 'W' THEN
1618 -- this is a warning
1619 l_warnings_rec.message_text := l_message_text_out;
1620 -- pqh_utility.init_warnings_table;
1621 pqh_utility.insert_warning
1622 (
1623 p_warnings_rec => l_warnings_rec
1624 );
1625 -- insert warning into process log
1626 pqh_process_batch_log.insert_log
1627 (
1628 p_message_type_cd => 'WARNING',
1629 p_message_text => l_message_text
1630 );
1631 ELSE
1632 -- this is ignore
1633 hr_utility.set_location('Message Type in wks Periods is : '||l_message_type,6);
1634 END IF; -- message_type
1635
1636
1637
1638
1639 /*
1640 -- get message text for PQH_WKS_INVALID_BSET_VAL
1641 -- message : Sum of Budget Sets value for a period must be more then zero
1642 FND_MESSAGE.SET_NAME('PQH','PQH_WKS_INVALID_BSET_VAL');
1643 l_message_text_out := FND_MESSAGE.GET;
1644
1645 l_message_text := l_message_text_out;
1646
1647 -- insert error message
1648 pqh_process_batch_log.insert_log
1649 (
1650 p_message_type_cd => 'ERROR',
1651 p_message_text => l_message_text
1652 );
1653 */
1654
1655
1656 END IF;
1657
1658
1659
1660
1661 hr_utility.set_location('Leaving:'||l_proc, 10);
1662
1663 EXCEPTION
1664 WHEN OTHERS THEN
1665 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1666 hr_utility.set_message_token('ROUTINE', l_proc);
1667 hr_utility.set_message_token('REASON', SQLERRM);
1668 -- end log and halt the program here
1669 raise g_error_exception;
1670 END check_wks_budget_sets;
1671
1672
1673 /*----------------------------------------------------------------
1674 || PROCEDURE : check_wks_budget_elements
1675 ||
1676 ------------------------------------------------------------------*/
1677
1678
1679
1680 PROCEDURE check_wks_budget_elements
1681 (
1682 p_worksheet_budget_set_id IN pqh_worksheet_budget_sets.worksheet_budget_set_id%TYPE,
1683 p_worksheet_bdgt_elmnt_id IN pqh_worksheet_fund_srcs.worksheet_bdgt_elmnt_id%TYPE
1684 ) IS
1685
1686 /*
1687 This procedure will be called ONLY for Budgeted Worksheet records
1688 This procedure will check if all the wks budget element records under the wks budget set
1689 and report all the errors
1690 */
1691
1692 l_proc varchar2(72) := g_package||'check_wks_budget_elements';
1693 l_worksheet_bdgt_elmnts_rec pqh_worksheet_bdgt_elmnts%ROWTYPE;
1694 l_message_text pqh_process_log.message_text%TYPE;
1695 l_message_text_out fnd_new_messages.message_text%TYPE;
1696 l_percentage_sum NUMBER;
1697 l_error_flag varchar2(10) := 'N';
1698 l_message_type varchar2(10) := 'E';
1699 l_warnings_rec pqh_utility.warnings_rec;
1700
1701
1702
1703 CURSOR csr_wks_budget_elements IS
1704 SELECT *
1705 FROM pqh_worksheet_bdgt_elmnts
1706 WHERE worksheet_bdgt_elmnt_id = p_worksheet_bdgt_elmnt_id;
1707
1708 CURSOR csr_wks_budget_elements_sum IS
1709 SELECT SUM(distribution_percentage)
1710 FROM pqh_worksheet_bdgt_elmnts
1711 WHERE worksheet_budget_set_id = p_worksheet_budget_set_id;
1712
1713
1714 BEGIN
1715
1716 hr_utility.set_location('Entering:'||l_proc, 5);
1717
1718
1719 -- CHECK # 1 check if SUM of percentage = 100
1720
1721 OPEN csr_wks_budget_elements_sum;
1722 FETCH csr_wks_budget_elements_sum INTO l_percentage_sum;
1723 CLOSE csr_wks_budget_elements_sum;
1724
1725 IF NVL(l_percentage_sum,0) > 100 THEN
1726
1727 -- get message text for PQH_WKS_INVALID_ELMNT_SUM
1728 -- message : For Budget elements total of percentage under a budget set cannot exceed 100
1729 pqh_utility.set_message(8302,'PQH_WKS_INVALID_ELMNT_SUM',g_context_organization_id);
1730
1731 hr_utility.set_location('Message Set ',6);
1732
1733 l_message_text_out := pqh_utility.get_message;
1734
1735 hr_utility.set_location('Get Message Text : '||l_message_text_out,7);
1736
1737 l_message_type := pqh_utility.get_message_type_cd;
1738
1739 hr_utility.set_location('Get Message Type : '||l_message_type,8);
1740
1741 -- assign warning message
1742 l_message_text := l_message_text_out;
1743
1744 IF nvl(l_message_type,'E') = 'E' THEN
1745 -- set l_error_flag to Y
1746 l_error_flag := 'Y';
1747 ELSIF nvl(l_message_type,'E') = 'W' THEN
1748 -- this is a warning
1749 l_warnings_rec.message_text := l_message_text_out;
1750 -- pqh_utility.init_warnings_table;
1751 pqh_utility.insert_warning
1752 (
1753 p_warnings_rec => l_warnings_rec
1754 );
1755 -- insert warning into process log
1756 pqh_process_batch_log.insert_log
1757 (
1758 p_message_type_cd => 'WARNING',
1759 p_message_text => l_message_text
1760 );
1761 ELSE
1762 -- this is ignore
1763 hr_utility.set_location('Message Type in wks Periods is : '||l_message_type,6);
1764 END IF;
1765
1766 /*
1767
1768 -- get message text for PQH_WKS_INVALID_ELMNT_SUM
1769 -- message : For Budget elements total of percentage under a budget set cannot exceed 100
1770 FND_MESSAGE.SET_NAME('PQH','PQH_WKS_INVALID_ELMNT_SUM');
1771 l_message_text_out := FND_MESSAGE.GET;
1772
1773 l_message_text := l_message_text_out;
1774
1775 -- set l_error_flag to Y
1776 l_error_flag := 'Y';
1777 */
1778
1779 ELSIF NVL(l_percentage_sum,0) < 100 THEN
1780
1781
1782 -- get message text for PQH_WKS_LESS_ELMNT_SUM
1783 -- message : Warning : For Budget elements total of percentage under budget set is less then 100
1784 pqh_utility.set_message(8302,'PQH_WKS_LESS_ELMNT_SUM',g_context_organization_id);
1785 l_message_text_out := pqh_utility.get_message;
1786 l_message_type := pqh_utility.get_message_type_cd;
1787
1788 -- assign warning message
1789 l_message_text := l_message_text_out;
1790
1791 IF nvl(l_message_type,'E') = 'E' THEN
1792 -- set l_error_flag to Y
1793 l_error_flag := 'Y';
1794 ELSIF nvl(l_message_type,'E') = 'W' THEN
1795 -- this is a warning
1796 l_warnings_rec.message_text := l_message_text_out;
1797 -- pqh_utility.init_warnings_table;
1798 pqh_utility.insert_warning
1799 (
1800 p_warnings_rec => l_warnings_rec
1801 );
1802 -- insert warning into process log
1803 pqh_process_batch_log.insert_log
1804 (
1805 p_message_type_cd => 'WARNING',
1806 p_message_text => l_message_text
1807 );
1808 ELSE
1809 -- this is ignore
1810 hr_utility.set_location('Message Type in wks Periods is : '||l_message_type,6);
1811 END IF;
1812
1813
1814
1815 /*
1816 -- get message text for PQH_WKS_LESS_ELMNT_SUM
1817 -- message : Warning : For Budget elements total of percentage under budget set is less then 100
1818 FND_MESSAGE.SET_NAME('PQH','PQH_WKS_LESS_ELMNT_SUM');
1819 l_message_text_out := FND_MESSAGE.GET;
1820
1821 l_message_text := l_message_text_out;
1822
1823 -- set l_error_flag to Y
1824 l_error_flag := 'Y';
1825 */
1826
1827
1828 END IF; -- CHECK # 1 check if SUM of percentage = 100
1829
1830
1831 hr_utility.set_location('After check 1 ',10);
1832
1833
1834 /*
1835 THIS IS DONE IN WORKSHEET DETAILS API
1836 removed on 03/23/2000
1837
1838 -- CHECK # 2 check if percent is negative
1839
1840 OPEN csr_wks_budget_elements;
1841 FETCH csr_wks_budget_elements INTO l_worksheet_bdgt_elmnts_rec;
1842 CLOSE csr_wks_budget_elements;
1843
1844
1845 IF NVL(l_worksheet_bdgt_elmnts_rec.distribution_percentage,0) < 0 THEN
1846
1847 IF l_error_flag = 'Y' THEN
1848 -- there is already an error so append the message
1849
1850 -- get message text for PQH_WKS_INVALID_ELMNT_PERCENT
1851 -- message : For Budget elements percentage cannot be less then 0
1852 FND_MESSAGE.SET_NAME('PQH','PQH_WKS_INVALID_ELMNT_PERCENT');
1853 l_message_text_out := FND_MESSAGE.GET;
1854
1855 l_message_text := l_message_text||' **** '||l_message_text_out;
1856 ELSE
1857 -- new message
1858 l_message_text := l_message_text_out;
1859 END IF;
1860
1861 -- set l_error_flag to Y
1862 l_error_flag := 'Y';
1863
1864 END IF; -- CHECK # 2 check if percent is negative
1865
1866 */
1867
1868
1869
1870 -- insert error message if l_error_flag is Y
1871 IF l_error_flag = 'Y' THEN
1872 pqh_process_batch_log.insert_log
1873 (
1874 p_message_type_cd => 'ERROR',
1875 p_message_text => l_message_text
1876 );
1877 END IF; -- insert error message if l_error_flag is Y
1878
1879
1880
1881
1882 hr_utility.set_location('Leaving:'||l_proc, 10);
1883
1884 EXCEPTION
1885 WHEN OTHERS THEN
1886 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1887 hr_utility.set_message_token('ROUTINE', l_proc);
1888 hr_utility.set_message_token('REASON', SQLERRM);
1889 -- end log and halt the program here
1890 raise g_error_exception;
1891 END check_wks_budget_elements;
1892
1893
1894 /*----------------------------------------------------------------
1895 || PROCEDURE : check_wks_fund_srcs
1896 ||
1897 ------------------------------------------------------------------*/
1898
1899
1900
1901 PROCEDURE check_wks_fund_srcs
1902 (
1903 p_worksheet_bdgt_elmnt_id IN pqh_worksheet_fund_srcs.worksheet_bdgt_elmnt_id%TYPE,
1904 p_worksheet_fund_src_id IN pqh_worksheet_fund_srcs.worksheet_fund_src_id%TYPE
1905 ) IS
1906
1907 /*
1908 This procedure will be called ONLY for Budgeted Worksheet records
1909 This procedure will check if all the wks budget fund srcs records under the wks budget set
1910 and report all the errors
1911 */
1912
1913 l_proc varchar2(72) := g_package||'check_wks_fund_srcs';
1914 l_worksheet_fund_srcs_rec pqh_worksheet_fund_srcs%ROWTYPE;
1915 l_message_text pqh_process_log.message_text%TYPE;
1916 l_message_text_out fnd_new_messages.message_text%TYPE;
1917 l_percentage_sum NUMBER;
1918 l_error_flag varchar2(10) := 'N';
1919 l_message_type varchar2(10) := 'E';
1920 l_warnings_rec pqh_utility.warnings_rec;
1921
1922
1923 CURSOR csr_wks_fund_srcs IS
1924 SELECT *
1925 FROM pqh_worksheet_fund_srcs
1926 WHERE worksheet_fund_src_id = p_worksheet_fund_src_id;
1927
1928 CURSOR csr_wks_fund_srcs_sum IS
1929 SELECT SUM(distribution_percentage)
1930 FROM pqh_worksheet_fund_srcs
1931 WHERE worksheet_bdgt_elmnt_id = p_worksheet_bdgt_elmnt_id;
1932
1933
1934 BEGIN
1935
1936 hr_utility.set_location('Entering:'||l_proc, 5);
1937
1938
1939 -- CHECK # 1 check if SUM of percentage = 100
1940
1941 OPEN csr_wks_fund_srcs_sum;
1942 FETCH csr_wks_fund_srcs_sum INTO l_percentage_sum;
1943 CLOSE csr_wks_fund_srcs_sum;
1944
1945 IF NVL(l_percentage_sum,0) > 100 THEN
1946
1947 -- get message text for PQH_WKS_INVALID_SRCS_SUM
1948 -- message : For Budget funding source total of percentage under a budget element cannot exceed 100
1949 pqh_utility.set_message(8302,'PQH_WKS_INVALID_SRCS_SUM',g_context_organization_id);
1950 l_message_text_out := pqh_utility.get_message;
1951 l_message_type := pqh_utility.get_message_type_cd;
1952
1953 -- assign warning message
1954 l_message_text := l_message_text_out;
1955
1956 IF nvl(l_message_type,'E') = 'E' THEN
1957 -- set l_error_flag to Y
1958 l_error_flag := 'Y';
1959 ELSIF nvl(l_message_type,'E') = 'W' THEN
1960 -- this is a warning
1961 l_warnings_rec.message_text := l_message_text_out;
1962 -- pqh_utility.init_warnings_table;
1963 pqh_utility.insert_warning
1964 (
1965 p_warnings_rec => l_warnings_rec
1966 );
1967 -- insert warning into process log
1968 pqh_process_batch_log.insert_log
1969 (
1970 p_message_type_cd => 'WARNING',
1971 p_message_text => l_message_text
1972 );
1973 ELSE
1974 -- this is ignore
1975 hr_utility.set_location('Message Type in wks Periods is : '||l_message_type,6);
1976 END IF;
1977
1978 ELSIF NVL(l_percentage_sum,0) < 100 THEN
1979
1980 -- get message text for PQH_WKS_LESS_SRC_SUM
1981 -- message : Warning : For Budget funding source total of percentage under budget element is less then 100
1982 pqh_utility.set_message(8302,'PQH_WKS_LESS_SRC_SUM',g_context_organization_id);
1983 l_message_text_out := pqh_utility.get_message;
1984 l_message_type := pqh_utility.get_message_type_cd;
1985
1986 -- assign warning message
1987 l_message_text := l_message_text_out;
1988
1989 IF nvl(l_message_type,'E') = 'E' THEN
1990 -- set l_error_flag to Y
1991 l_error_flag := 'Y';
1992 ELSIF nvl(l_message_type,'E') = 'W' THEN
1993 -- this is a warning
1994 l_warnings_rec.message_text := l_message_text_out;
1995 -- pqh_utility.init_warnings_table;
1996 pqh_utility.insert_warning
1997 (
1998 p_warnings_rec => l_warnings_rec
1999 );
2000 -- insert warning into process log
2001 pqh_process_batch_log.insert_log
2002 (
2003 p_message_type_cd => 'WARNING',
2004 p_message_text => l_message_text
2005 );
2006 ELSE
2007 -- this is ignore
2008 hr_utility.set_location('Message Type in wks Periods is : '||l_message_type,6);
2009 END IF;
2010
2011 END IF; -- CHECK # 1 check if SUM of percentage = 100
2012
2013
2014 OPEN csr_wks_fund_srcs;
2015 FETCH csr_wks_fund_srcs INTO l_worksheet_fund_srcs_rec;
2016 CLOSE csr_wks_fund_srcs;
2017
2018 if l_worksheet_fund_srcs_rec.cost_allocation_keyflex_id is null then
2019 -- ALL PATEO columns should be there as cost allocation is null
2020 if l_worksheet_fund_srcs_rec.project_id is null or
2021 l_worksheet_fund_srcs_rec.task_id is null or
2022 l_worksheet_fund_srcs_rec.award_id is null or
2023 l_worksheet_fund_srcs_rec.expenditure_type is null or
2024 l_worksheet_fund_srcs_rec.organization_id is null then
2025 -- one of the PATEO column is missing, raise an error
2026 l_error_flag := 'Y';
2027 FND_MESSAGE.SET_NAME('PQH','PQH_BUDGET_SRC_MANDATORY');
2028 l_message_text_out := FND_MESSAGE.GET;
2029 l_message_text := l_message_text||' **** '||l_message_text_out;
2030 end if;
2031 else
2032 -- ALL PATEO columns should be null as cost allocation is present
2033 if l_worksheet_fund_srcs_rec.project_id is not null and
2034 l_worksheet_fund_srcs_rec.task_id is not null and
2035 l_worksheet_fund_srcs_rec.award_id is not null and
2036 l_worksheet_fund_srcs_rec.expenditure_type is not null and
2037 l_worksheet_fund_srcs_rec.organization_id is not null then
2038 -- some of the PATEO column is present alongwith cost allocation, raise an error
2039 l_error_flag := 'Y';
2040 FND_MESSAGE.SET_NAME('PQH','PQH_BUDGET_SRC_GL_GMS');
2041 l_message_text_out := FND_MESSAGE.GET;
2042 l_message_text := l_message_text||' **** '||l_message_text_out;
2043 end if;
2044 end if;
2045 /*
2046 THIS IS DONE IN WORKSHEET DETAILS API
2047 removed on 03/23/2000
2048 -- CHECK # 2 check if percent is negative
2049 IF NVL(l_worksheet_fund_srcs_rec.distribution_percentage,0) < 0 THEN
2050
2051 IF l_error_flag = 'Y' THEN
2052 -- there is already an error so append the message
2053
2054 -- get message text for PQH_WKS_INVALID_SRC_PERCENT
2055 -- message : For Budget funding source percentage cannot be less then 0
2056 FND_MESSAGE.SET_NAME('PQH','PQH_WKS_INVALID_SRC_PERCENT');
2057 l_message_text_out := FND_MESSAGE.GET;
2058
2059 l_message_text := l_message_text||' **** '||l_message_text_out;
2060 ELSE
2061 -- new message
2062 l_message_text := l_message_text_out;
2063 END IF;
2064
2065 -- set l_error_flag to Y
2066 l_error_flag := 'Y';
2067
2068 END IF; -- CHECK # 2 check if percent is negative
2069 */
2070
2071 -- insert error message if l_error_flag is Y
2072 IF l_error_flag = 'Y' THEN
2073 pqh_process_batch_log.insert_log
2074 (
2075 p_message_type_cd => 'ERROR',
2076 p_message_text => l_message_text
2077 );
2078 END IF; -- insert error message if l_error_flag is Y
2079
2080
2081
2082
2083 hr_utility.set_location('Leaving:'||l_proc, 10);
2084
2085 EXCEPTION
2086 WHEN OTHERS THEN
2087 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2088 hr_utility.set_message_token('ROUTINE', l_proc);
2089 hr_utility.set_message_token('REASON', SQLERRM);
2090 -- end log and halt the program here
2091 raise g_error_exception;
2092 END check_wks_fund_srcs;
2093
2094
2095
2096
2097 /*----------------------------------------------------------------
2098 || PROCEDURE : set_wks_log_context
2099 ||
2100 ------------------------------------------------------------------*/
2101
2102
2103
2104 PROCEDURE set_wks_log_context
2105 (
2106 p_worksheet_detail_id IN pqh_worksheet_details.worksheet_detail_id%TYPE,
2107 p_log_context OUT NOCOPY pqh_process_log.log_context%TYPE
2108 ) IS
2109
2110 /*
2111 This procedure will set the log_context at wks detail level
2112
2113 Delegated Record -> Display Organization Name
2114 Budgeted Record -> Display name of Primary Budgeted Entity
2115 OPEN -> Display Order is P J O G ( which ever is not null
2116 Root Record -> Display Worksheet Name
2117
2118 */
2119
2120 l_proc varchar2(72) := g_package||'set_wks_log_context';
2121 l_worksheet_details_rec pqh_worksheet_details%ROWTYPE;
2122 l_position_name hr_positions.name%TYPE;
2123 l_job_name per_jobs.name%TYPE;
2124 l_organization_name hr_organization_units.name%TYPE;
2125 l_grade_name per_grades.name%TYPE;
2126
2127 CURSOR csr_wks_detail_rec IS
2128 SELECT *
2129 FROM pqh_worksheet_details
2130 WHERE worksheet_detail_id = p_worksheet_detail_id ;
2131
2132 CURSOR csr_ptx_name(p_position_transaction_id IN number) IS
2133 SELECT name
2134 FROM pqh_position_transactions
2135 WHERE position_transaction_id = p_position_transaction_id;
2136
2137 BEGIN
2138
2139 hr_utility.set_location('Entering:'||l_proc, 5);
2140
2141 OPEN csr_wks_detail_rec;
2142 FETCH csr_wks_detail_rec INTO l_worksheet_details_rec;
2143 CLOSE csr_wks_detail_rec;
2144
2145
2146 l_position_name := HR_GENERAL.DECODE_POSITION (p_position_id => l_worksheet_details_rec.position_id);
2147 l_job_name := HR_GENERAL.DECODE_JOB (p_job_id => l_worksheet_details_rec.job_id);
2148 l_organization_name := HR_GENERAL.DECODE_ORGANIZATION (p_organization_id => l_worksheet_details_rec.organization_id);
2149 l_grade_name := HR_GENERAL.DECODE_GRADE (p_grade_id => l_worksheet_details_rec.grade_id);
2150
2151 IF NVL(l_worksheet_details_rec.action_cd , 'R') = 'R' THEN
2152 -- this is the main parent record , display worksheet Name
2153 p_log_context := g_worksheet_name;
2154 ELSIF NVL(l_worksheet_details_rec.action_cd , 'R') = 'D' THEN
2155 -- this is delegated record , display Organization Name
2156 p_log_context := SUBSTR(l_organization_name,1,255);
2157 ELSIF NVL(l_worksheet_details_rec.action_cd , 'R') = 'B' THEN
2158 -- this is budgeted record , display Primary Budgeted Entity
2159 IF NVL(g_budgeted_entity_cd ,'OPEN') = 'POSITION' THEN
2160 p_log_context := SUBSTR(l_position_name,1,255);
2161 -- if there is no position then get name from PTX table
2162 IF (l_worksheet_details_rec.position_transaction_id IS NOT NULL ) AND
2163 (l_worksheet_details_rec.position_id IS NULL ) THEN
2164 OPEN csr_ptx_name(p_position_transaction_id => l_worksheet_details_rec.position_transaction_id);
2165 FETCH csr_ptx_name INTO p_log_context;
2166 CLOSE csr_ptx_name;
2167 END IF; -- ptx record
2168 ELSIF NVL(g_budgeted_entity_cd ,'OPEN') = 'JOB' THEN
2169 p_log_context := SUBSTR(l_job_name,1,255);
2170 ELSIF NVL(g_budgeted_entity_cd ,'OPEN') = 'ORGANIZATION' THEN
2171 p_log_context := SUBSTR(l_organization_name,1,255);
2172 ELSIF NVL(g_budgeted_entity_cd ,'OPEN') = 'GRADE' THEN
2173 p_log_context := SUBSTR(l_grade_name,1,255);
2174 ELSIF NVL(g_budgeted_entity_cd ,'OPEN') = 'OPEN' THEN
2175
2176 IF l_position_name IS NOT NULL THEN
2177 p_log_context := SUBSTR(l_position_name,1,255);
2178 ELSIF l_job_name IS NOT NULL THEN
2179 p_log_context := SUBSTR(l_job_name,1,255);
2180 ELSIF l_organization_name IS NOT NULL THEN
2181 p_log_context := SUBSTR(l_organization_name,1,255);
2182 ELSIF l_grade_name IS NOT NULL THEN
2183 p_log_context := SUBSTR(l_grade_name,1,255);
2184 END IF;
2185
2186 END IF;
2187 END IF;
2188
2189
2190
2191
2192
2193 hr_utility.set_location('Log Context : '||p_log_context, 100);
2194
2195
2196
2197 hr_utility.set_location('Leaving:'||l_proc, 1000);
2198
2199 EXCEPTION
2200 WHEN OTHERS THEN
2201 p_log_context := 'When others exception in pqwkserr.pkb.set_wks_log_context';
2202 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2203 hr_utility.set_message_token('ROUTINE', l_proc);
2204 hr_utility.set_message_token('REASON', SQLERRM);
2205 -- end log and halt the program here
2206 raise g_error_exception;
2207 END set_wks_log_context;
2208
2209
2210
2211
2212 /*----------------------------------------------------------------
2213 || PROCEDURE : set_wpr_log_context
2214 ||
2215 ------------------------------------------------------------------*/
2216
2217
2218
2219
2220 PROCEDURE set_wpr_log_context
2221 (
2222 p_worksheet_period_id IN pqh_worksheet_periods.worksheet_period_id%TYPE,
2223 p_log_context OUT NOCOPY pqh_process_log.log_context%TYPE
2224 ) IS
2225 /*
2226 This procedure will set the log_context at wks periods level
2227
2228 Display the period start date for start_time_period_id and
2229 Display the period end date for end_time_period_id
2230 Table : per_time_periods
2231
2232 */
2233
2234 l_proc varchar2(72) := g_package||'set_wpr_log_context';
2235 l_worksheet_periods_rec pqh_worksheet_periods%ROWTYPE;
2236 l_per_time_periods per_time_periods%ROWTYPE;
2237 l_start_date per_time_periods.start_date%TYPE;
2238 l_end_date per_time_periods.end_date%TYPE;
2239
2240 CURSOR csr_wks_periods_rec IS
2241 SELECT *
2242 FROM pqh_worksheet_periods
2243 WHERE worksheet_period_id = p_worksheet_period_id ;
2244
2245 CURSOR csr_per_time_periods ( p_time_period_id IN number ) IS
2246 SELECT *
2247 FROM per_time_periods
2248 WHERE time_period_id = p_time_period_id ;
2249
2250 BEGIN
2251
2252 hr_utility.set_location('Entering:'||l_proc, 5);
2253
2254 OPEN csr_wks_periods_rec;
2255 FETCH csr_wks_periods_rec INTO l_worksheet_periods_rec;
2256 CLOSE csr_wks_periods_rec;
2257
2258 -- get the start date
2259 OPEN csr_per_time_periods ( p_time_period_id => l_worksheet_periods_rec.start_time_period_id);
2260 FETCH csr_per_time_periods INTO l_per_time_periods;
2261 CLOSE csr_per_time_periods;
2262
2263 l_start_date := l_per_time_periods.start_date;
2264
2265
2266 -- get the end date
2267
2268 OPEN csr_per_time_periods ( p_time_period_id => l_worksheet_periods_rec.end_time_period_id);
2269 FETCH csr_per_time_periods INTO l_per_time_periods;
2270 CLOSE csr_per_time_periods;
2271
2272 l_end_date := l_per_time_periods.end_date;
2273
2274 -- set log context
2275
2276 p_log_context := l_start_date||' - '||l_end_date;
2277
2278
2279
2280 hr_utility.set_location('Log Context : '||p_log_context, 101);
2281 hr_utility.set_location('Leaving:'||l_proc, 1000);
2282
2283 EXCEPTION
2284 WHEN OTHERS THEN
2285 p_log_context := 'pqwkserr.pkb.set_wpr_log_context failed in when others';
2286 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2287 hr_utility.set_message_token('ROUTINE', l_proc);
2288 hr_utility.set_message_token('REASON', SQLERRM);
2289 -- end log and halt the program here
2290 raise g_error_exception;
2291 END set_wpr_log_context;
2292
2293
2294
2295 /*----------------------------------------------------------------
2296 || PROCEDURE : set_wst_log_context
2297 ||
2298 ------------------------------------------------------------------*/
2299
2300
2301 PROCEDURE set_wst_log_context
2302 (
2303 p_worksheet_budget_set_id IN pqh_worksheet_budget_sets.worksheet_budget_set_id%TYPE,
2304 p_log_context OUT NOCOPY pqh_process_log.log_context%TYPE
2305 ) IS
2306
2307 /*
2308 This procedure will set the log_context at wks budget sets level
2309
2310 Display the DFLT_BUDGET_SET_NAME
2311 Table : pqh_dflt_budget_sets
2312
2313 */
2314
2315 l_proc varchar2(72) := g_package||'set_wst_log_context';
2316 l_worksheet_budget_sets_rec pqh_worksheet_budget_sets%ROWTYPE;
2317 l_dflt_budget_sets_rec pqh_dflt_budget_sets%ROWTYPE;
2318
2319
2320 CURSOR csr_wks_budget_sets_rec IS
2321 SELECT *
2322 FROM pqh_worksheet_budget_sets
2323 WHERE worksheet_budget_set_id = p_worksheet_budget_set_id;
2324
2325 CURSOR csr_dflt_budget_sets_rec ( p_dflt_budget_set_id IN number) IS
2326 SELECT *
2327 FROM pqh_dflt_budget_sets
2328 WHERE dflt_budget_set_id = p_dflt_budget_set_id;
2329
2330 BEGIN
2331
2332 hr_utility.set_location('Entering:'||l_proc, 5);
2333
2334 OPEN csr_wks_budget_sets_rec;
2335 FETCH csr_wks_budget_sets_rec INTO l_worksheet_budget_sets_rec;
2336 CLOSE csr_wks_budget_sets_rec;
2337
2338 OPEN csr_dflt_budget_sets_rec(p_dflt_budget_set_id => l_worksheet_budget_sets_rec.dflt_budget_set_id);
2339 FETCH csr_dflt_budget_sets_rec INTO l_dflt_budget_sets_rec;
2340 CLOSE csr_dflt_budget_sets_rec;
2341
2342
2343 p_log_context := l_dflt_budget_sets_rec.dflt_budget_set_name;
2344
2345 hr_utility.set_location('Log Context : '||p_log_context, 101);
2346 hr_utility.set_location('Leaving:'||l_proc, 1000);
2347
2348 EXCEPTION
2349 WHEN OTHERS THEN
2350 p_log_context := 'pqwkserr.pkb.set_wst_log_context failed in when others';
2351 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2352 hr_utility.set_message_token('ROUTINE', l_proc);
2353 hr_utility.set_message_token('REASON', SQLERRM);
2354 -- end log and halt the program here
2355 raise g_error_exception;
2356 END set_wst_log_context;
2357
2358
2359
2360 /*----------------------------------------------------------------
2361 || PROCEDURE : set_wel_log_context
2362 ||
2363 ------------------------------------------------------------------*/
2364
2365
2366 PROCEDURE set_wel_log_context
2367 (
2368 p_worksheet_bdgt_elmnt_id IN pqh_worksheet_bdgt_elmnts.worksheet_bdgt_elmnt_id%TYPE,
2369 p_log_context OUT NOCOPY pqh_process_log.log_context%TYPE
2370 ) IS
2371
2372 /*
2373 This procedure will set the log_context at wks budget elements level
2374
2375 Display the ELEMENT_NAME
2376 Table : pay_element_types
2377
2378 Added on 11/01/2000
2379 At worksheet element level, we only check for the sum of percentage . The context in this case
2380 will be the above budget set .
2381
2382 */
2383
2384 l_proc varchar2(72) := g_package||'set_wel_log_context';
2385 l_worksheet_bdgt_elmnts_rec pqh_worksheet_bdgt_elmnts%ROWTYPE;
2386 l_pay_element_types_rec pay_element_types%ROWTYPE;
2387
2388 l_worksheet_budget_sets_rec pqh_worksheet_budget_sets%ROWTYPE;
2389 l_dflt_budget_sets_rec pqh_dflt_budget_sets%ROWTYPE;
2390
2391
2392
2393 CURSOR csr_wks_bdgt_elmnts_rec IS
2394 SELECT *
2395 FROM pqh_worksheet_bdgt_elmnts
2396 WHERE worksheet_bdgt_elmnt_id = p_worksheet_bdgt_elmnt_id;
2397
2398 CURSOR csr_pay_element_types_rec ( p_element_type_id IN number) IS
2399 SELECT *
2400 FROM pay_element_types
2401 WHERE element_type_id = p_element_type_id;
2402
2403 CURSOR csr_wks_budget_sets_rec(p_worksheet_budget_set_id IN number) IS
2404 SELECT *
2405 FROM pqh_worksheet_budget_sets
2406 WHERE worksheet_budget_set_id = p_worksheet_budget_set_id;
2407
2408 CURSOR csr_dflt_budget_sets_rec ( p_dflt_budget_set_id IN number) IS
2409 SELECT *
2410 FROM pqh_dflt_budget_sets
2411 WHERE dflt_budget_set_id = p_dflt_budget_set_id;
2412
2413
2414 BEGIN
2415
2416 hr_utility.set_location('Entering:'||l_proc, 5);
2417
2418 OPEN csr_wks_bdgt_elmnts_rec;
2419 FETCH csr_wks_bdgt_elmnts_rec INTO l_worksheet_bdgt_elmnts_rec;
2420 CLOSE csr_wks_bdgt_elmnts_rec;
2421
2422 /*
2423 OPEN csr_pay_element_types_rec(p_element_type_id => l_worksheet_bdgt_elmnts_rec.element_type_id);
2424 FETCH csr_pay_element_types_rec INTO l_pay_element_types_rec;
2425 CLOSE csr_pay_element_types_rec;
2426 */
2427
2428 OPEN csr_wks_budget_sets_rec(p_worksheet_budget_set_id => l_worksheet_bdgt_elmnts_rec.worksheet_budget_set_id);
2429 FETCH csr_wks_budget_sets_rec INTO l_worksheet_budget_sets_rec;
2430 CLOSE csr_wks_budget_sets_rec;
2431
2432 OPEN csr_dflt_budget_sets_rec(p_dflt_budget_set_id => l_worksheet_budget_sets_rec.dflt_budget_set_id);
2433 FETCH csr_dflt_budget_sets_rec INTO l_dflt_budget_sets_rec;
2434 CLOSE csr_dflt_budget_sets_rec;
2435
2436
2437 p_log_context := l_dflt_budget_sets_rec.dflt_budget_set_name;
2438
2439 -- p_log_context := l_pay_element_types_rec.element_name;
2440
2441 hr_utility.set_location('Log Context : '||p_log_context, 101);
2442 hr_utility.set_location('Leaving:'||l_proc, 1000);
2443
2444 EXCEPTION
2445 WHEN OTHERS THEN
2446 p_log_context := 'pqwkserr.pkb.set_wel_log_context failed in when others';
2447 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2448 hr_utility.set_message_token('ROUTINE', l_proc);
2449 hr_utility.set_message_token('REASON', SQLERRM);
2450 -- end log and halt the program here
2451 raise g_error_exception;
2452 END set_wel_log_context;
2453
2454
2455
2456
2457 /*----------------------------------------------------------------
2458 || PROCEDURE : set_wfs_log_context
2459 ||
2460 ------------------------------------------------------------------*/
2461
2462
2463 PROCEDURE set_wfs_log_context
2464 (
2465 p_worksheet_fund_src_id IN pqh_worksheet_fund_srcs.worksheet_fund_src_id%TYPE,
2466 p_log_context OUT NOCOPY pqh_process_log.log_context%TYPE
2467 ) IS
2468
2469 /*
2470 This procedure will set the log_context at wks budget fund srcs level
2471
2472 Display the CONCATENATED_SEGMENTS
2473 Table : pay_cost_allocation_keyflex
2474
2475 Added on 11/01/2000. The check done at fund src level is for sum of the above budget element.
2476 So context will be the element name
2477
2478 */
2479
2480 l_proc varchar2(72) := g_package||'set_wfs_log_context';
2481 l_worksheet_fund_srcs_rec pqh_worksheet_fund_srcs%ROWTYPE;
2482 l_pay_cost_allocation_kf_rec pay_cost_allocation_keyflex%ROWTYPE;
2483
2484 l_worksheet_bdgt_elmnts_rec pqh_worksheet_bdgt_elmnts%ROWTYPE;
2485 l_pay_element_types_rec pay_element_types%ROWTYPE;
2486
2487
2488
2489 CURSOR csr_wks_bdgt_fund_srcs_rec IS
2490 SELECT *
2491 FROM pqh_worksheet_fund_srcs
2492 WHERE worksheet_fund_src_id = p_worksheet_fund_src_id;
2493
2494 CURSOR csr_pay_cost_allocation_kf_rec ( p_cost_allocation_keyflex_id IN number) IS
2495 SELECT *
2496 FROM pay_cost_allocation_keyflex
2497 WHERE cost_allocation_keyflex_id = p_cost_allocation_keyflex_id;
2498
2499 CURSOR csr_wks_bdgt_elmnts_rec(p_worksheet_bdgt_elmnt_id IN number) IS
2500 SELECT *
2501 FROM pqh_worksheet_bdgt_elmnts
2502 WHERE worksheet_bdgt_elmnt_id = p_worksheet_bdgt_elmnt_id;
2503
2504 CURSOR csr_pay_element_types_rec ( p_element_type_id IN number) IS
2505 SELECT *
2506 FROM pay_element_types
2507 WHERE element_type_id = p_element_type_id;
2508
2509
2510
2511 BEGIN
2512
2513 hr_utility.set_location('Entering:'||l_proc, 5);
2514
2515 OPEN csr_wks_bdgt_fund_srcs_rec;
2516 FETCH csr_wks_bdgt_fund_srcs_rec INTO l_worksheet_fund_srcs_rec;
2517 CLOSE csr_wks_bdgt_fund_srcs_rec;
2518
2519 OPEN csr_wks_bdgt_elmnts_rec(p_worksheet_bdgt_elmnt_id => l_worksheet_fund_srcs_rec.worksheet_bdgt_elmnt_id);
2520 FETCH csr_wks_bdgt_elmnts_rec INTO l_worksheet_bdgt_elmnts_rec;
2521 CLOSE csr_wks_bdgt_elmnts_rec;
2522
2523 OPEN csr_pay_element_types_rec(p_element_type_id => l_worksheet_bdgt_elmnts_rec.element_type_id);
2524 FETCH csr_pay_element_types_rec INTO l_pay_element_types_rec;
2525 CLOSE csr_pay_element_types_rec;
2526
2527 p_log_context := l_pay_element_types_rec.element_name;
2528
2529
2530 /*
2531 OPEN csr_pay_cost_allocation_kf_rec(p_cost_allocation_keyflex_id => l_worksheet_fund_srcs_rec.cost_allocation_keyflex_id);
2532 FETCH csr_pay_cost_allocation_kf_rec INTO l_pay_cost_allocation_kf_rec;
2533 CLOSE csr_pay_cost_allocation_kf_rec;
2534 */
2535
2536
2537 -- p_log_context := l_pay_cost_allocation_kf_rec.concatenated_segments;
2538
2539
2540 hr_utility.set_location('Log Context : '||p_log_context, 101);
2541 hr_utility.set_location('Leaving:'||l_proc, 1000);
2542
2543 EXCEPTION
2544 WHEN OTHERS THEN
2545 p_log_context := 'pqwkserr.pkb.set_wfs_log_context failed in when others';
2546 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2547 hr_utility.set_message_token('ROUTINE', l_proc);
2548 hr_utility.set_message_token('REASON', SQLERRM);
2549 -- end log and halt the program here
2550 raise g_error_exception;
2551 END set_wfs_log_context;
2552
2553
2554 -- added on 03/28/2000 for the new rqmt that the batch header is wks detail record
2555 -- and not the wks id as was previously designed
2556
2557 /*----------------------------------------------------------------
2558 || PROCEDURE : check_input_wks_details
2559 ||
2560 ------------------------------------------------------------------*/
2561
2562
2563
2564 PROCEDURE check_input_wks_details
2565 (
2566 p_worksheet_detail_id IN pqh_worksheet_details.worksheet_detail_id%TYPE
2567 ) IS
2568
2569 /*
2570 This procedure will check if all the input wks detail records i.e the main node is valid
2571 and report all the errors
2572 For budgeted records we check if there are rows in periods
2573 For the input wks dtl ID there must be atleast one budgeted record under this node
2574 */
2575
2576 l_proc varchar2(72) := g_package||'check_input_wks_details';
2577 l_worksheet_details_rec pqh_worksheet_details%ROWTYPE;
2578 l_message_text pqh_process_log.message_text%TYPE;
2579 l_message_text_out fnd_new_messages.message_text%TYPE;
2580 l_count number;
2581 l_budget_count number;
2582 l_error_flag varchar2(10) := 'N';
2583
2584
2585 CURSOR csr_wks_details IS
2586 SELECT *
2587 FROM pqh_worksheet_details
2588 WHERE worksheet_detail_id = p_worksheet_detail_id;
2589
2590 CURSOR csr_wks_periods_count IS
2591 SELECT COUNT(*)
2592 FROM pqh_worksheet_periods
2593 WHERE worksheet_detail_id = p_worksheet_detail_id;
2594
2595 CURSOR csr_budget_count IS
2596 SELECT COUNT(*)
2597 FROM pqh_worksheet_details wdt
2598 WHERE action_cd = 'B'
2599 START WITH worksheet_detail_id = p_worksheet_detail_id
2600 CONNECT BY prior worksheet_detail_id = parent_worksheet_detail_id ;
2601
2602
2603 BEGIN
2604
2605 hr_utility.set_location('Entering:'||l_proc, 5);
2606
2607 OPEN csr_wks_details;
2608 FETCH csr_wks_details INTO l_worksheet_details_rec;
2609 CLOSE csr_wks_details;
2610
2611
2612 -- CHECK # 1 for budgeted records check rows in periods
2613 IF NVL(l_worksheet_details_rec.action_cd , 'R') ='B' THEN
2614 -- this is budgeted record
2615 OPEN csr_wks_periods_count;
2616 FETCH csr_wks_periods_count INTO l_count;
2617 CLOSE csr_wks_periods_count;
2618
2619 IF NVL(l_count,0) = 0 THEN
2620
2621 hr_utility.set_location('WKS Detail Error 3 PQH_WKS_NO_PERIODS '||l_worksheet_details_rec.worksheet_detail_id,10);
2622
2623 -- get message text for PQH_WKS_NO_PERIODS
2624 -- message : No Periods Defined for the budgeted entity
2625 FND_MESSAGE.SET_NAME('PQH','PQH_WKS_NO_PERIODS');
2626 l_message_text_out := FND_MESSAGE.GET;
2627
2628 IF l_error_flag = 'Y' THEN
2629 -- there is already an error so append the message
2630
2631 l_message_text := l_message_text||' **** '||l_message_text_out;
2632 ELSE
2633 -- new message
2634 l_message_text := l_message_text_out;
2635 END IF;
2636
2637 -- set l_error_flag to Y
2638 l_error_flag := 'Y';
2639
2640 END IF;
2641 END IF; -- CHECK # 1 for budgeted records check rows in periods
2642
2643
2644 -- CHECK # 2 check if available amount is >= 0 for all records
2645
2646 IF ( NVL(l_worksheet_details_rec.budget_unit1_available,0) < 0 ) OR
2647 ( NVL(l_worksheet_details_rec.budget_unit2_available,0) < 0 ) OR
2648 ( NVL(l_worksheet_details_rec.budget_unit3_available,0) < 0 ) THEN
2649
2650 hr_utility.set_location('WKS Detail Error 4 PQH_WKS_DEL_ALL_AMT '||l_worksheet_details_rec.worksheet_detail_id,10);
2651
2652 -- get message text for PQH_WKS_DEL_ALL_AMT
2653 -- message : Budget Amount in the delegated organization exceeds the allocated amount
2654 FND_MESSAGE.SET_NAME('PQH','PQH_WKS_DEL_ALL_AMT');
2655 l_message_text_out := FND_MESSAGE.GET;
2656
2657 IF l_error_flag = 'Y' THEN
2658 -- there is already an error so append the message
2659
2660 l_message_text := l_message_text||' **** '||l_message_text_out;
2661 ELSE
2662 -- new message
2663 l_message_text := l_message_text_out;
2664 END IF;
2665
2666 -- set l_error_flag to Y
2667 l_error_flag := 'Y';
2668
2669
2670 END IF; -- CHECK # 2 check if available amount is >= 0
2671
2672 -- CHECK # 3 check if atleast one record under the input node is Budgeted
2673
2674 OPEN csr_budget_count;
2675 FETCH csr_budget_count INTO l_budget_count;
2676 CLOSE csr_budget_count;
2677
2678 IF NVL(l_budget_count,0) = 0 THEN
2679
2680 -- get message text for PQH_WKS_NO_BDT_RECS
2681 -- message : There must be atleast one Budgeted entity
2682 FND_MESSAGE.SET_NAME('PQH','PQH_WKS_NO_BDT_RECS');
2683 l_message_text_out := FND_MESSAGE.GET;
2684
2685 IF l_error_flag = 'Y' THEN
2686 -- there is already an error so append the message
2687
2688 l_message_text := l_message_text||' **** '||l_message_text_out;
2689 ELSE
2690 -- new message
2691 l_message_text := l_message_text_out;
2692 END IF;
2693
2694 -- set l_error_flag to Y
2695 l_error_flag := 'Y';
2696
2697 END IF; -- End CHECK # 3 check if atleast one record under the input node is Budgeted
2698
2699 hr_utility.set_location('Error Flag : '||l_error_flag,10);
2700
2701 -- insert error message if l_error_flag is Y
2702 IF l_error_flag = 'Y' THEN
2703
2704 -- end the process log as the batch itself has error
2705
2706 updt_batch
2707 (
2708 p_message_text => l_message_text
2709 );
2710
2711 -- UPDATE pqh_process_log
2712 -- SET message_type_cd = 'ERROR',
2713 -- message_text = l_message_text,
2714 -- txn_table_route_id = g_table_route_id_wdt,
2715 -- batch_status = 'ERROR',
2716 -- batch_end_date = sysdate
2717 -- WHERE process_log_id = pqh_process_batch_log.g_master_process_log_id;
2718
2719 -- set the batch status to error
2720 g_batch_status := 'ERROR';
2721
2722 END IF; -- insert error message if l_error_flag is Y
2723
2724
2725 hr_utility.set_location('Leaving:'||l_proc, 10);
2726
2727 EXCEPTION
2728 WHEN OTHERS THEN
2729 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2730 hr_utility.set_message_token('ROUTINE', l_proc);
2731 hr_utility.set_message_token('REASON', SQLERRM);
2732 hr_utility.raise_error;
2733 END check_input_wks_details;
2734
2735
2736
2737 /*----------------------------------------------------------------
2738 || PROCEDURE : end_log
2739 ||
2740 ------------------------------------------------------------------*/
2741
2742 PROCEDURE end_log
2743 IS
2744
2745 --
2746 -- local variables
2747 --
2748 l_proc varchar2(72) := g_package||'end_log';
2749 l_count_error NUMBER := 0;
2750 l_count_warning NUMBER := 0;
2751 l_status VARCHAR2(30);
2752 l_pqh_process_log_rec pqh_process_log%ROWTYPE;
2753
2754
2755 CURSOR csr_status (p_message_type_cd IN VARCHAR2 ) IS
2756 SELECT COUNT(*)
2757 FROM pqh_process_log
2758 WHERE message_type_cd = p_message_type_cd
2759 START WITH process_log_id = pqh_process_batch_log.g_master_process_log_id
2760 CONNECT BY PRIOR process_log_id = master_process_log_id;
2761
2762 CURSOR csr_batch_rec IS
2763 SELECT *
2764 FROM pqh_process_log
2765 WHERE process_log_id = pqh_process_batch_log.g_master_process_log_id;
2766
2767 PRAGMA AUTONOMOUS_TRANSACTION;
2768
2769
2770 BEGIN
2771
2772 hr_utility.set_location('Entering: '||l_proc, 5);
2773
2774 /*
2775 Compute the status of the batch. If there exists any record in the batch with
2776 message_type_cd = 'ERROR' then the batch_status = 'ERROR'
2777 If there only exists records in the batch with message_type_cd = 'WARNING' then
2778 the batch_status = 'WARNING'
2779 If there are NO records in the batch with message_type_cd = 'WARNING' OR 'ERROR' then
2780 the batch_status = 'SUCCESS'
2781 */
2782
2783 OPEN csr_status(p_message_type_cd => 'ERROR');
2784 FETCH csr_status INTO l_count_error;
2785 CLOSE csr_status;
2786
2787 OPEN csr_status(p_message_type_cd => 'WARNING');
2788 FETCH csr_status INTO l_count_warning;
2789 CLOSE csr_status;
2790
2791
2792 IF l_count_error <> 0 THEN
2793 -- there are one or more errors
2794 l_status := 'ERROR';
2795 g_batch_status := 'ERROR';
2796 ELSE
2797 -- errors are 0 , check for warnings
2798 IF l_count_warning <> 0 THEN
2799 -- there are one or more warnings
2800 l_status := 'WARNING';
2801 g_batch_status := 'WARNING';
2802 ELSE
2803 -- no errors or warnings
2804 l_status := 'SUCCESS';
2805 g_batch_status := 'SUCCESS';
2806 END IF;
2807
2808 END IF;
2809
2810 hr_utility.set_location('Batch Status : '||l_status,100);
2811
2812 /*
2813 update the 'start' record for this batch with message_type_cd = 'COMPLETE' and
2814 update the batch_end_date with current date time
2815 */
2816
2817 OPEN csr_batch_rec;
2818 FETCH csr_batch_rec INTO l_pqh_process_log_rec;
2819 CLOSE csr_batch_rec;
2820
2821 IF l_pqh_process_log_rec.message_type_cd <> 'ERROR'THEN
2822 -- no errors in the batch
2823 UPDATE pqh_process_log
2824 SET message_type_cd = 'COMPLETE',
2825 message_text = fnd_message.get_string('PQH','PQH_PROCESS_COMPLETED'),
2826 txn_table_route_id = g_table_route_id_wdt,
2827 batch_status = l_status,
2828 batch_end_date = sysdate
2829 WHERE process_log_id = pqh_process_batch_log.g_master_process_log_id;
2830 ELSE
2831 -- there were errors in the batch header record i.w the root node
2832 -- so only update the batch status and end date
2833 UPDATE pqh_process_log
2834 SET batch_status = l_status,
2835 batch_end_date = sysdate,
2836 txn_table_route_id = g_table_route_id_wdt
2837 WHERE process_log_id = pqh_process_batch_log.g_master_process_log_id;
2838 END IF;
2839
2840
2841 hr_utility.set_location('Leaving:'||l_proc, 1000);
2842
2843 /*
2844 commit the transaction
2845 */
2846
2847 commit;
2848
2849
2850 EXCEPTION
2851 WHEN OTHERS THEN
2852 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2853 hr_utility.set_message_token('ROUTINE', l_proc);
2854 hr_utility.set_message_token('REASON', SQLERRM);
2855 hr_utility.raise_error;
2856 END end_log;
2857
2858
2859
2860 /*----------------------------------------------------------------
2861 || PROCEDURE : updt_batch
2862 ||
2863 ------------------------------------------------------------------*/
2864
2865 PROCEDURE updt_batch
2866 (
2867 p_message_text IN pqh_process_log.message_text%TYPE
2868 )
2869 IS
2870 --
2871 -- local variables
2872 --
2873 l_proc varchar2(72) := g_package||'updt_batch';
2874 l_message_text pqh_process_log.message_text%TYPE;
2875
2876 PRAGMA AUTONOMOUS_TRANSACTION;
2877
2878 BEGIN
2879
2880 hr_utility.set_location('Entering: '||l_proc, 5);
2881
2882
2883 UPDATE pqh_process_log
2884 SET message_type_cd = 'ERROR',
2885 message_text = p_message_text,
2886 txn_table_route_id = g_table_route_id_wdt,
2887 batch_status = 'ERROR',
2888 batch_end_date = sysdate
2889 WHERE process_log_id = pqh_process_batch_log.g_master_process_log_id;
2890
2891 /*
2892 Commit the autonomous txn
2893 */
2894
2895 commit;
2896
2897 hr_utility.set_location('Leaving: '||l_proc, 100);
2898
2899 EXCEPTION
2900 WHEN OTHERS THEN
2901 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2902 hr_utility.set_message_token('ROUTINE', l_proc);
2903 hr_utility.set_message_token('REASON', SQLERRM);
2904 hr_utility.raise_error;
2905 END updt_batch;
2906
2907
2908
2909 /*----------------------------------------------------------------
2910 || PROCEDURE : check_wks_dates
2911 ||
2912 ------------------------------------------------------------------*/
2913
2914 PROCEDURE check_wks_dates
2915 (
2916 p_worksheet_detail_id IN pqh_worksheet_details.worksheet_detail_id%TYPE,
2917 p_status OUT NOCOPY varchar2,
2918 p_message OUT NOCOPY varchar2
2919 ) IS
2920 /*
2921 This procedure will call Sumit's procedure to check if the wks dates are valid
2922 If not we will populate the log and abort the program
2923 */
2924 --
2925 -- local variables
2926 --
2927 l_proc varchar2(72) := g_package||'check_wks_dates';
2928 l_worksheets_rec pqh_worksheets%ROWTYPE;
2929 l_wks_ll_date date;
2930 l_wks_ul_date date;
2931 l_status varchar2(10);
2932 l_message_text_out fnd_new_messages.message_text%TYPE;
2933 l_pc_start_date date;
2934 l_pc_end_date date;
2935 l_pc_bdgt_name varchar2(80);
2936 l_pc_version_no number(9);
2937 l_budgets_rec pqh_budgets%ROWTYPE;
2938
2939 CURSOR csr_worksheet_rec IS
2940 SELECT *
2941 FROM pqh_worksheets
2942 WHERE worksheet_id =
2943 (
2944 SELECT wks.worksheet_id
2945 FROM pqh_worksheets wks, pqh_worksheet_details wdt
2946 WHERE wdt.worksheet_id = wks.worksheet_id
2947 AND wdt.worksheet_detail_id = p_worksheet_detail_id
2948 );
2949
2950 CURSOR csr_budgets_rec(p_budget_id IN number) IS
2951 SELECT *
2952 FROM pqh_budgets
2953 WHERE budget_id = p_budget_id ;
2954
2955 BEGIN
2956
2957 hr_utility.set_location('Entering: '||l_proc, 5);
2958
2959 OPEN csr_worksheet_rec;
2960 FETCH csr_worksheet_rec INTO l_worksheets_rec;
2961 CLOSE csr_worksheet_rec;
2962
2963 pqh_wks_budget.wks_date_validation
2964 (
2965 p_worksheet_mode => l_worksheets_rec.worksheet_mode_cd,
2966 p_budget_id => l_worksheets_rec.budget_id,
2967 p_budget_version_id => l_worksheets_rec.budget_version_id,
2968 p_wks_start_date => l_worksheets_rec.date_from,
2969 p_wks_end_date => l_worksheets_rec.date_to,
2970 p_wks_ll_date => l_wks_ll_date,
2971 p_wks_ul_date => l_wks_ul_date,
2972 p_status => l_status
2973 );
2974
2975 IF NVL(l_status,'ERROR') = 'ERROR' THEN
2976 -- get message text for PQH_WKS_VALID_DATES
2977 FND_MESSAGE.SET_NAME('PQH','PQH_WKS_VALID_DATES');
2978 FND_MESSAGE.SET_TOKEN('LL',l_wks_ll_date);
2979 FND_MESSAGE.SET_TOKEN('UL',l_wks_ul_date);
2980 l_message_text_out := FND_MESSAGE.GET;
2981 p_message := l_message_text_out;
2982
2983 p_status := 'ERROR';
2984
2985 ELSE
2986
2987 p_message := '';
2988 p_status := 'SUCCESS';
2989
2990 END IF;
2991
2992 hr_utility.set_location('Leaving:'||l_proc, 1000);
2993
2994 EXCEPTION
2995 WHEN OTHERS THEN
2996 p_status := 'ERROR';
2997 p_message := 'Erroring out in pqwkserr.pkb.check_wks_dates';
2998 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2999 hr_utility.set_message_token('ROUTINE', l_proc);
3000 hr_utility.set_message_token('REASON', SQLERRM);
3001 hr_utility.raise_error;
3002 END check_wks_dates;
3003
3004
3005 /*----------------------------------------------------------------
3006 || PROCEDURE : check_pc_posn
3007 ||
3008 ------------------------------------------------------------------*/
3009 PROCEDURE check_pc_posn
3010 (
3011 p_position_id IN pqh_worksheet_details.position_id%TYPE,
3012 p_status OUT NOCOPY varchar2
3013 ) IS
3014 /*
3015 This procedure will check if the position is already budgeted in any other budget
3016 */
3017 --
3018 -- local variables
3019 --
3020 l_proc varchar2(72) := g_package||'check_pc_posn';
3021
3022 CURSOR csr_budget_id IS
3023 SELECT DISTINCT bgt.budget_id
3024 FROM pqh_budgets bgt, pqh_budget_versions bvr, pqh_budget_details bdt
3025 WHERE bgt.budget_id = bvr.budget_id
3026 AND bvr.budget_version_id = bdt.budget_version_id
3027 AND bdt.position_id = p_position_id
3028 AND NVL(bgt.position_control_flag,'X') = 'Y'
3029 AND bgt.budget_id <> g_budget_id;
3030
3031 CURSOR csr_budget_rec(p_budget_id IN number) IS
3032 SELECT *
3033 FROM pqh_budgets
3034 WHERE budget_id = p_budget_id;
3035
3036 CURSOR csr_lookup(p_shared_type_id IN number) IS
3037 SELECT system_type_cd
3038 FROM per_shared_types
3039 WHERE shared_type_id = NVL(p_shared_type_id,-1);
3040
3041 l_budget_id pqh_budgets.budget_id%TYPE;
3042 l_budget_rec pqh_budgets%ROWTYPE;
3043
3044 l_curr_lookup1 varchar2(50) := 'A';
3045 l_curr_lookup2 varchar2(50) := 'B';
3046 l_curr_lookup3 varchar2(50) := 'C';
3047
3048 l_pc_lookup1 varchar2(50);
3049 l_pc_lookup2 varchar2(50);
3050 l_pc_lookup3 varchar2(50);
3051
3052
3053 BEGIN
3054
3055 hr_utility.set_location('Entering: '||l_proc, 5);
3056
3057 -- start with success and mark error if found
3058 p_status := 'SUCCESS';
3059
3060 -- get the lookup codes for current budget
3061 OPEN csr_lookup(p_shared_type_id => g_budget_unit1_id);
3062 FETCH csr_lookup INTO l_curr_lookup1;
3063 CLOSE csr_lookup;
3064
3065 OPEN csr_lookup(p_shared_type_id => g_budget_unit2_id);
3066 FETCH csr_lookup INTO l_curr_lookup2;
3067 CLOSE csr_lookup;
3068
3069 OPEN csr_lookup(p_shared_type_id => g_budget_unit3_id);
3070 FETCH csr_lookup INTO l_curr_lookup3;
3071 CLOSE csr_lookup;
3072
3073 OPEN csr_budget_id;
3074 LOOP
3075 FETCH csr_budget_id INTO l_budget_id;
3076 EXIT WHEN csr_budget_id%NOTFOUND;
3077 -- get details for this budget
3078 OPEN csr_budget_rec(p_budget_id => l_budget_id);
3079 FETCH csr_budget_rec INTO l_budget_rec;
3080 CLOSE csr_budget_rec;
3081
3082 -- compare fiscal periods
3083 IF (l_budget_rec.budget_start_date BETWEEN g_budget_start_dt AND g_budget_end_dt ) OR
3084 (l_budget_rec.budget_end_date BETWEEN g_budget_start_dt AND g_budget_end_dt ) OR
3085 (g_budget_start_dt BETWEEN l_budget_rec.budget_start_date AND l_budget_rec.budget_end_date) OR
3086 (g_budget_end_dt BETWEEN l_budget_rec.budget_start_date AND l_budget_rec.budget_end_date)THEN
3087 -- there is a FISCAL PERIOD OVERLAP, compare UOM lookup codes
3088 -- initialize and get the loopup codes for this budget
3089 l_pc_lookup1 := 'X';
3090 l_pc_lookup1 := 'Y';
3091 l_pc_lookup1 := 'Z';
3092 OPEN csr_lookup(p_shared_type_id => l_budget_rec.budget_unit1_id);
3093 FETCH csr_lookup INTO l_pc_lookup1;
3094 CLOSE csr_lookup;
3095
3096 OPEN csr_lookup(p_shared_type_id => l_budget_rec.budget_unit2_id);
3097 FETCH csr_lookup INTO l_pc_lookup2;
3098 CLOSE csr_lookup;
3099
3100 OPEN csr_lookup(p_shared_type_id => l_budget_rec.budget_unit3_id);
3101 FETCH csr_lookup INTO l_pc_lookup3;
3102 CLOSE csr_lookup;
3103
3104 -- compare if UOM lookup codes overlap
3105 IF l_pc_lookup1 IN ( l_curr_lookup1, l_curr_lookup2, l_curr_lookup3 ) OR
3106 l_pc_lookup2 IN ( l_curr_lookup1, l_curr_lookup2, l_curr_lookup3 ) OR
3107 l_pc_lookup3 IN ( l_curr_lookup1, l_curr_lookup2, l_curr_lookup3 ) THEN
3108 -- lookup codes match too , sor this is ERROR
3109 p_status := 'ERROR';
3110 exit; -- exit the loop
3111 END IF;
3112
3113 END IF ; -- FISCAL PERIODS OVERLAP
3114 END LOOP;
3115 CLOSE csr_budget_id;
3116
3117
3118 hr_utility.set_location('Leaving:'||l_proc, 1000);
3119
3120 EXCEPTION
3121 WHEN OTHERS THEN
3122 p_status := 'ERROR';
3123 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3124 hr_utility.set_message_token('ROUTINE', l_proc);
3125 hr_utility.set_message_token('REASON', SQLERRM);
3126 hr_utility.raise_error;
3127 END check_pc_posn;
3128
3129
3130
3131
3132
3133
3134 END ; -- end of body for package pqh_wks_error_chk
[Home] [Help]