[Home] [Help]
PACKAGE BODY: APPS.PQH_COMMITMENT_POSTING
Source
1 PACKAGE BODY pqh_commitment_posting AS
2 /* $Header: pqglcmmt.pkb 120.10 2006/12/28 10:37:28 krajarat noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(100) := 'pqh_commitment_posting.'; -- Global package name
9 --
10 g_application_id NUMBER(15) := 101;
11 --
12 g_budget_id pqh_budgets.budget_id%TYPE;
13 g_set_of_books_id gl_interface.set_of_books_id%TYPE;
14 g_budgetary_control_flag gl_sets_of_books.enable_budgetary_control_flag%TYPE;
15 g_budget_name pqh_budgets.budget_name%TYPE;
16 g_budgeted_entity_cd pqh_budgets.budgeted_entity_cd%TYPE;
17 g_transfer_to_grants_flag pqh_budgets.transfer_to_grants_flag%TYPE;
18 g_bgt_currency_code pqh_budgets.currency_code%TYPE;
19 --
20 g_user_je_source_name gl_interface.user_je_source_name%TYPE;
21 g_user_je_category_name gl_interface.user_je_category_name%TYPE;
22 --
23 g_budget_version_id gl_interface.budget_version_id%TYPE;
24 g_gl_budget_version_id gl_interface.budget_version_id%TYPE;
25 g_version_number pqh_budget_versions.version_number%TYPE;
26 g_last_posted_ver gl_interface.budget_version_id%TYPE;
27 --
28 g_chart_of_accounts_id gl_interface.chart_of_accounts_id%TYPE;
29 g_default_currency_code gl_interface.currency_code%TYPE;
30 g_currency_code1 gl_interface.currency_code%TYPE;
31 g_currency_code2 gl_interface.currency_code%TYPE;
32 g_currency_code3 gl_interface.currency_code%TYPE;
33 g_budget_uom1 pqh_budgets.budget_unit1_id%TYPE;
34 g_budget_uom2 pqh_budgets.budget_unit2_id%TYPE;
35 g_budget_uom3 pqh_budgets.budget_unit3_id%TYPE;
36 --
37 g_table_route_id_bvr number;
38 g_table_route_id_bdt number;
39 g_table_route_id_bpr number;
40 g_table_route_id_bfs number;
41 g_table_route_id_glf number;
42 --
43 g_detail_error VARCHAR2(10);
44 g_error_exception exception;
45 g_status varchar2(10);
46 g_validate boolean;
47 --
48 g_distribution_table t_distribution_table;
49 g_period_amt_tab t_period_amt_tab;
50 g_old_bdgt_dtls_tab pqh_gl_posting.t_old_bdgt_dtls_tab;
51 g_gms_import_tab pqh_gl_posting.t_gms_import_tab;
52 --
53 ---------------------------------------------------------------------------------------
54 -- Private Procedures added for Transfer to Grants
55 ---------------------------------------------------------------------------------------
56 PROCEDURE populate_pqh_gms_interface
57 (
58 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE,
59 p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE,
60 p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
61 );
62
63 PROCEDURE insert_pqh_gms_interface
64 (
65 p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
66 p_period_name IN varchar2,
67 p_project_id IN pqh_gl_interface.project_id%TYPE,
68 p_task_id IN pqh_gl_interface.task_id%TYPE,
69 p_award_id IN pqh_gl_interface.award_id%TYPE,
70 p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
71 p_organization_id IN pqh_gl_interface.organization_id%TYPE,
72 p_amount IN pqh_gl_interface.amount_dr%TYPE,
73 p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
74 );
75
76 PROCEDURE update_pqh_gms_interface
77 (
78 p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
79 p_period_name IN varchar2,
80 p_project_id IN pqh_gl_interface.project_id%TYPE,
81 p_task_id IN pqh_gl_interface.task_id%TYPE,
82 p_award_id IN pqh_gl_interface.award_id%TYPE,
83 p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
84 p_organization_id IN pqh_gl_interface.organization_id%TYPE,
85 p_amount IN pqh_gl_interface.amount_dr%TYPE,
86 p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
87 ) ;
88
89 -- Procedure added to run funds checker in autonomous transaction
90 PROCEDURE ins_gl_bc_run_fund_check
91 ( p_packet_id IN gl_bc_packets.packet_id%TYPE
92 ,p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE
93 ,p_period_name IN pqh_gl_interface.period_name%TYPE
94 ,p_period_year IN gl_period_statuses.period_year%TYPE
95 ,p_period_num IN gl_period_statuses.period_num%TYPE
96 ,p_quarter_num IN gl_period_statuses.quarter_num%TYPE
97 ,p_currency_code IN pqh_gl_interface.currency_code%TYPE
98 ,p_entered_dr IN pqh_gl_interface.amount_dr%TYPE
99 ,p_entered_cr IN pqh_gl_interface.amount_cr%TYPE
100 ,p_accounted_dr IN pqh_gl_interface.amount_dr%TYPE
101 ,p_accounted_cr IN pqh_gl_interface.amount_cr%TYPE
102 ,p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE
103 ,p_fc_mode IN varchar2
104 ,p_fc_success OUT NOCOPY boolean
105 ,p_fc_return OUT NOCOPY varchar2
106 );
107
108 PROCEDURE populate_gms_tables;
109
110 ---------------------------------------------------------------------------------------
111
112 ------------------------------------------------------------------------------------------
113 -- Private procedure added for Consolidating the commitments -- Bug :5645538 --krajarat
114 ------------------------------------------------------------------------------------------
115 Procedure consolidate_commitment
116 IS
117 BEGIN
118 --loop thro the g_period_amt_tab and consolidate all teh commitments into a one record.
119
120 For cnt in g_period_amt_tab.FIRST .. g_period_amt_tab.LAST loop
121 --
122 IF cnt > g_period_amt_tab.FIRST THEN --Skip the first time and process next time onwards
123 g_period_amt_tab(1).commitment1 := g_period_amt_tab(1).commitment1 + g_period_amt_tab(cnt).commitment1 ;
124 g_period_amt_tab(1).commitment2 := g_period_amt_tab(1).commitment2 + g_period_amt_tab(cnt).commitment2 ;
125 g_period_amt_tab(1).commitment3 := g_period_amt_tab(1).commitment3 + g_period_amt_tab(cnt).commitment3 ;
126 g_period_amt_tab.delete(cnt);
127 END IF;
128
129 END LOOP; --end of for loop.
130 hr_utility.set_location('Consolidation-> The size is :'||g_period_amt_tab.LAST , 5);
131 --
132 --
133 END;
134
135 Procedure get_period_dates
136 (p_budget_period_id IN pqh_budget_periods.budget_period_id%TYPE,
137 p_period_start_date OUT NOCOPY date,
138 p_period_end_date OUT NOCOPY date) IS
139 --
140 Cursor csr_period is
141 Select start_time_period_id,end_time_period_id
142 From pqh_budget_periods
143 Where budget_period_id = p_budget_period_id;
144 --
145 Cursor csr_period_date(p_time_period_id in number) is
146 Select start_date,end_date
147 From per_time_periods
148 Where time_period_id = p_time_period_id;
149 --
150 l_start_time_period_id pqh_budget_periods.start_time_period_id%TYPE;
151 l_end_time_period_id pqh_budget_periods.end_time_period_id%TYPE;
152 --
153 l_start_date date;
154 l_end_date date;
155 --
156 l_proc varchar2(72) := g_package||'get_period_dates';
157 --
158 BEGIN
159 --
160 hr_utility.set_location('Entering:'||l_proc, 5);
161 --
162 Open csr_period;
163 Fetch csr_period into l_start_time_period_id,l_end_time_period_id;
164 Close csr_period;
165 --
166 Open csr_period_date(l_start_time_period_id);
167 Fetch csr_period_date into l_start_date,l_end_date;
168 Close csr_period_date;
169 --
170 p_period_start_date := l_start_date;
171 --
172 Open csr_period_date(l_end_time_period_id);
173 Fetch csr_period_date into l_start_date,l_end_date;
174 Close csr_period_date;
175 --
176 p_period_end_date := l_end_date;
177 --
178 hr_utility.set_location('Leaving:'||l_proc,10);
179 --
180 exception when others then
181 p_period_start_date := null;
182 p_period_end_date := null;
183 raise;
184 End;
185 --
186 ----------------------------------------------------------------------------------------
187 --
188 -- This procedure will check if the business_group has a default currency,if yes it will
189 -- override the gl_sets_of_book currency code.
190 -- If there is a currency associated with the budget,it will override all other currencies
191 --
192 --
193 PROCEDURE get_default_currency
194 (p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE,
195 p_default_currency_code OUT NOCOPY gl_interface.currency_code%TYPE) IS
196 --
197 -- local variables
198 --
199 l_bg_curr_code varchar2(150) := '';
200 l_budget_curr varchar2(150) := '';
201 --
202 CURSOR csr_curr_code IS
203 SELECT bg.currency_code
204 FROM per_business_groups bg,
205 pqh_budgets bgt,
206 pqh_budget_versions bvr
207 WHERE bgt.budget_id = bvr.budget_id
208 AND bvr.budget_version_id = p_budget_version_id
209 AND bgt.business_group_id = bg.business_group_id ;
210 --
211 CURSOR csr_bgt_curr IS
212 SELECT bgt.currency_code
213 FROM pqh_budgets bgt,
214 pqh_budget_versions bvr
215 WHERE bgt.budget_id = bvr.budget_id
216 AND bvr.budget_version_id = g_budget_version_id;
217 --
218 l_proc varchar2(72) := g_package||'get_default_currency';
219 --
220 BEGIN
221 --
222 hr_utility.set_location('Entering:'||l_proc, 5);
223 --
224 --
225 OPEN csr_bgt_curr;
226 FETCH csr_bgt_curr INTO l_budget_curr;
227 CLOSE csr_bgt_curr;
228 --
229 hr_utility.set_location('Budget Currency Code : '||l_budget_curr,7);
230 --
231 IF l_budget_curr IS NOT NULL THEN
232 --
233 -- assign this to g_currency_code
234 --
235 p_default_currency_code := l_budget_curr;
236 --
237 else
238 OPEN csr_curr_code;
239 FETCH csr_curr_code INTO l_bg_curr_code;
240 CLOSE csr_curr_code;
241 --
242 hr_utility.set_location('Business Group Curr Code : '||l_bg_curr_code,6);
243 --
244 IF l_bg_curr_code IS NOT NULL THEN
245 --
246 -- assign this to g_currency_code
247 --
248 p_default_currency_code := l_bg_curr_code;
249 --
250 END IF;
251 END IF;
252 --
253 hr_utility.set_location('Leaving:'||l_proc, 1000);
254 --
255 EXCEPTION
256 WHEN OTHERS THEN
257 p_default_currency_code := null;
258 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
259 hr_utility.set_message_token('ROUTINE', l_proc);
260 hr_utility.set_message_token('REASON', SQLERRM);
261 hr_utility.raise_error;
262 END get_default_currency;
263 --
264 ------------------------------------------------------------------------------------
265 --
266 -- If there are errors in fetch globals procedure we will call this procedure which will
267 -- end the process log as the batch itself has error
268 --
269 PROCEDURE populate_globals_error
270 (
271 p_message_text IN pqh_process_log.message_text%TYPE
272 ) IS
273 --
274 -- local variables
275 --
276 l_proc varchar2(72) := g_package||'populate_globals_error';
277 PRAGMA AUTONOMOUS_TRANSACTION;
278 --
279 BEGIN
280 --
281 hr_utility.set_location('Entering: '||l_proc, 5);
282 --
283 UPDATE pqh_process_log
284 SET message_type_cd = 'ERROR',
285 message_text = p_message_text,
286 txn_table_route_id = g_table_route_id_bvr
287 WHERE process_log_id = pqh_process_batch_log.g_master_process_log_id;
288 --
289 -- commit the autonomous transaction
290 --
291 commit;
292 --
293 hr_utility.set_location('Leaving:'||l_proc, 1000);
294 --
295 EXCEPTION
296 WHEN OTHERS THEN
297 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
298 hr_utility.set_message_token('ROUTINE', l_proc);
299 hr_utility.set_message_token('REASON', SQLERRM);
300 hr_utility.raise_error;
301 END populate_globals_error;
302
303 ----------------------------------------------------------------------------------------------------
304 --
305 PROCEDURE fetch_global_values
306 (p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE) IS
307 --
308 l_proc varchar2(72) := g_package||'fetch_global_values';
309 --
310 l_budgets_rec pqh_budgets%ROWTYPE;
311 l_budget_versions_rec pqh_budget_versions%ROWTYPE;
312 --
313 l_gl_sets_of_books_rec gl_sets_of_books%ROWTYPE;
314 l_shared_types_rec per_shared_types%ROWTYPE;
315 l_gl_budget_versions_rec gl_budget_versions%ROWTYPE;
316 l_gl_je_sources_rec gl_je_sources%ROWTYPE;
317 l_gl_je_categories_rec gl_je_categories%ROWTYPE;
318 --
319 --
320 l_version_gl_status pqh_budget_versions.gl_status%TYPE;
321 --
322 l_gl_encumbrance_type_id gl_encumbrance_types.encumbrance_type_id%TYPE;
323 --
324 l_transfer_to_gl_flag pqh_budgets.transfer_to_gl_flag%TYPE;
325 l_psb_budget_flag pqh_budgets.psb_budget_flag%TYPE;
326 l_default_currency_code gl_interface.currency_code%TYPE;
327 --
328 l_message_text pqh_process_log.message_text%TYPE;
329 l_message_text_out fnd_new_messages.message_text%TYPE;
330 l_error_flag varchar2(10) := 'N';
331 l_level number;
332 l_batch_id number;
333 l_batch_context varchar2(2000);
334 l_count number;
335 l_map_count_null number;
336 l_gl_budget_name pqh_budgets.gl_budget_name%TYPE;
337 --
338 --
339 CURSOR csr_budget_versions_rec IS
340 SELECT *
341 FROM pqh_budget_versions
342 WHERE budget_version_id = p_budget_version_id;
343
344 CURSOR csr_budgets_rec IS
345 SELECT *
346 FROM pqh_budgets
347 WHERE budget_id = ( SELECT budget_id
348 FROM pqh_budget_versions
349 WHERE budget_version_id = p_budget_version_id ) ;
350 --
351 CURSOR csr_chart_of_acc_id(p_set_of_books_id IN NUMBER) IS
352 SELECT *
353 FROM gl_sets_of_books
354 WHERE set_of_books_id = p_set_of_books_id;
355
356 CURSOR csr_shared_types (p_shared_type_id IN number) IS
357 SELECT *
358 FROM per_shared_types
359 WHERE shared_type_id = p_shared_type_id;
360 -- Change by kmullapu. Changed p_budget_name to p_gl_budget_name as we can
361 --now select GL Budget Name from Budget Charectaristics form
362
363 CURSOR csr_gl_budget_version (p_gl_budget_name IN varchar2) IS
364 SELECT *
365 FROM gl_budget_versions
366 WHERE budget_name =p_gl_budget_name AND
367 status in ('O','C');
368
369 CURSOR csr_gl_je_sources IS
370 SELECT *
371 FROM gl_je_sources
372 WHERE je_source_name = 'Public Sector Budget';
373
374 CURSOR csr_gl_je_categories IS
375 SELECT *
376 FROM gl_je_categories
377 WHERE je_category_name = 'Public Sector Budget';
378
379 CURSOR csr_table_route (p_table_alias IN varchar2 )IS
380 SELECT table_route_id
381 FROM pqh_table_route
382 WHERE table_alias = p_table_alias;
383
384 CURSOR csr_flex_maps_counts (p_budget_id IN number)IS
385 SELECT COUNT(*)
386 FROM pqh_budget_gl_flex_maps
387 WHERE budget_id = p_budget_id;
388
389 CURSOR csr_cost_map_null (p_budget_id IN number) IS
390 SELECT COUNT(*)
391 FROM pqh_budget_gl_flex_maps
392 WHERE budget_id = p_budget_id
393 AND payroll_cost_segment IS NULL;
394
395 cursor csr_gl_encumbrance_types is
396 select encumbrance_type_id
397 from gl_encumbrance_types
398 where encumbrance_type_id = 1000;
399 --
400 BEGIN
401 --
402 hr_utility.set_location('Entering: '||l_proc, 5);
403 --
404 -- check if the input budget version Id is valid
405 --
406 OPEN csr_budget_versions_rec;
407 FETCH csr_budget_versions_rec INTO l_budget_versions_rec;
408 CLOSE csr_budget_versions_rec;
409 --
410 g_version_number := l_budget_versions_rec.version_number;
411 --
412 OPEN csr_budgets_rec;
413 FETCH csr_budgets_rec INTO l_budgets_rec;
414 CLOSE csr_budgets_rec;
415 --
416 g_budget_id := l_budgets_rec.budget_id;
417 g_budget_name := l_budgets_rec.budget_name;
418 g_budgeted_entity_cd := l_budgets_rec.budgeted_entity_cd;
419 g_set_of_books_id := l_budgets_rec.gl_set_of_books_id;
420 l_transfer_to_gl_flag := l_budgets_rec.transfer_to_gl_flag;
421 g_transfer_to_grants_flag := l_budgets_rec.transfer_to_grants_flag;
422 l_psb_budget_flag := l_budgets_rec.psb_budget_flag;
423 g_budget_uom1 := l_budgets_rec.budget_unit1_id;
424 g_budget_uom2 := l_budgets_rec.budget_unit2_id;
425 g_budget_uom3 := l_budgets_rec.budget_unit3_id;
426 l_gl_budget_name := l_budgets_rec.gl_budget_name;
427 get_default_currency(p_budget_version_id => p_budget_version_id,
428 p_default_currency_code => g_bgt_currency_code);
429 g_default_currency_code := g_bgt_currency_code;
430 --
431 l_batch_id := g_budget_version_id;
432 l_batch_context := g_budget_name||' - '||g_version_number;
433 --
434 hr_utility.set_location('Batch Context : '||l_batch_context,7);
435 --
436 -- Start the Log Process
437 --
438 pqh_process_batch_log.start_log
439 (
440 p_batch_id => l_batch_id,
441 p_module_cd => 'COMMITMENT_GL_POSTING',
442 p_log_context => l_batch_context
443 );
444 --
445 --
446 --
447 l_version_gl_status := l_budget_versions_rec.gl_status;
448 IF l_budget_versions_rec.budget_version_id IS NULL THEN
449 --
450 -- invalid budget_version id
451 --
452 FND_MESSAGE.SET_NAME('PQH','PQH_INV_BDG_VERSION_ID');
453 -- APP_EXCEPTION.RAISE_EXCEPTION; /* Fix for bug 2714555 */
454 l_message_text_out := FND_MESSAGE.GET;
455 --
456 IF l_error_flag = 'Y' THEN
457 --
458 -- there is already an error so append the message
459 --
460 l_message_text := l_message_text||' **** '||l_message_text_out;
461
462 --
463 ELSE
464 --
465 -- new message
466 --
467 l_error_flag := 'Y';
468 l_message_text := l_message_text_out;
469 --
470 END IF;
471 --
472 END IF;
473 --
474 -- Raise error if budget version is not posted
475 -- Note : If the budget version was posted , then it means that
476 -- flexfield has been mapped for the budget version . So we
477 -- dont have to validate for that .
478 --
479 /**
480 IF NVL(l_budget_versions_rec.gl_status,'X') <>'POST' THEN
481 --
482 FND_MESSAGE.SET_NAME('PQH','PQH_BUDGET_VERSION_NOT_POSTED');
483 APP_EXCEPTION.RAISE_EXCEPTION;
484 --
485 END IF;
486 **/
487 --
488 -- Raise error if the commitment for this budget version is already
489 -- posted.
490 --
491 IF NVL(l_budget_versions_rec.commitment_gl_status,'X') = 'POST' THEN
492 --
493 FND_MESSAGE.SET_NAME('PQH','PQH_BUDGET_VER_CMMTMNT_POSTED');
494 -- APP_EXCEPTION.RAISE_EXCEPTION; /* Fix for bug 2714555 */
495 l_message_text_out := FND_MESSAGE.GET;
496 --
497 IF l_error_flag = 'Y' THEN
498 --
499 -- there is already an error so append the message
500 --
501 l_message_text := l_message_text||' **** '||l_message_text_out;
502
503 --
504 ELSE
505 --
506 -- new message
507 --
508 l_error_flag := 'Y';
509 l_message_text := l_message_text_out;
510 --
511 END IF;
512 --
513 ELSIF NVL(l_budget_versions_rec.commitment_gl_status,'X') = 'CALCULATION_ERROR' THEN
514 --
515 -- Raise error if the commitment for this budget version is already
516 -- posted.
517 --
518 FND_MESSAGE.SET_NAME('PQH','PQH_BDGT_VER_CMMTMNT_CALC_ERR');
519 -- APP_EXCEPTION.RAISE_EXCEPTION; /* Fix for bug 2714555 */
520 l_message_text_out := FND_MESSAGE.GET;
521 --
522 IF l_error_flag = 'Y' THEN
523 --
524 -- there is already an error so append the message
525 --
526 l_message_text := l_message_text||' **** '||l_message_text_out;
527
528 --
529 ELSE
530 --
531 -- new message
532 --
533 l_error_flag := 'Y';
534 l_message_text := l_message_text_out;
535 --
536 END IF;
537 --
538 -- ELSIF l_budget_versions_rec.commitment_gl_status IS NULL THEN
539 --
540 --
541 --
542 END IF;
543 --
544 /* g_version_number := l_budget_versions_rec.version_number;
545 --
546 OPEN csr_budgets_rec;
547 FETCH csr_budgets_rec INTO l_budgets_rec;
548 CLOSE csr_budgets_rec;
549 --
550 g_budget_id := l_budgets_rec.budget_id;
551 g_budget_name := l_budgets_rec.budget_name;
552 g_budgeted_entity_cd := l_budgets_rec.budgeted_entity_cd;
553 g_set_of_books_id := l_budgets_rec.gl_set_of_books_id;
554 l_transfer_to_gl_flag := l_budgets_rec.transfer_to_gl_flag;
555 g_transfer_to_grants_flag := l_budgets_rec.transfer_to_grants_flag;
556 l_psb_budget_flag := l_budgets_rec.psb_budget_flag;
557 g_budget_uom1 := l_budgets_rec.budget_unit1_id;
558 g_budget_uom2 := l_budgets_rec.budget_unit2_id;
559 g_budget_uom3 := l_budgets_rec.budget_unit3_id;
560 l_gl_budget_name := l_budgets_rec.gl_budget_name;
561 get_default_currency(p_budget_version_id => p_budget_version_id,
562 p_default_currency_code => g_bgt_currency_code);
563 g_default_currency_code := g_bgt_currency_code;
564 --
565 l_batch_id := g_budget_version_id;
566 l_batch_context := g_budget_name||' - '||g_version_number;
567 --
568 hr_utility.set_location('Batch Context : '||l_batch_context,7);
569 --
570 -- Start the Log Process
571 --
572 pqh_process_batch_log.start_log
573 (
574 p_batch_id => l_batch_id,
575 p_module_cd => 'COMMITMENT_GL_POSTING',
576 p_log_context => l_batch_context
577 ); */
578 --
579 -- Raise error if set_of_books_id IS NULL
580 --
581 IF g_set_of_books_id IS NULL THEN
582 --
583 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_GL_SET_BOOKS');
584 l_message_text_out := FND_MESSAGE.GET;
585 --
586 IF l_error_flag = 'Y' THEN
587 --
588 -- there is already an error so append the message
589 --
590 l_message_text := l_message_text||' **** '||l_message_text_out;
591 --
592 ELSE
593 --
594 -- new message
595 --
596 l_error_flag := 'Y';
597 l_message_text := l_message_text_out;
598 --
599 END IF;
600 --
601 END IF; -- set_of_books_id IS NOT NULL
602
603 -- CHECK : if g_bgt_currency_code IS NOT NULL
604 IF g_bgt_currency_code IS NULL THEN
605 -- get message text for PQH_INVALID_BGT_CURR_CODE
606 -- message : Currency Code is not defined for the budget
607 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_BGT_CURR_CODE');
608 l_message_text_out := FND_MESSAGE.GET;
609
610 IF l_error_flag = 'Y' THEN
611 -- there is already an error so append the message
612
613 l_message_text := l_message_text||' **** '||l_message_text_out;
614 ELSE
615 -- new message
616 l_message_text := l_message_text_out;
617 END IF;
618
619 -- set l_error_flag to Y
620 l_error_flag := 'Y';
621
622 END IF; -- g_bgt_currency_code IS NOT NULL
623 --
624 --
625 --
626 --If Budget is Transfered to Grants then Budget should be posted prior to Commitment Xfer
627 --
628 IF NVL(g_transfer_to_grants_flag,'N') = 'Y' AND
629 NVL(l_version_gl_status,'X') <> 'POST' THEN
630 hr_utility.set_message(8302, 'PQH_BUDGET_VERSION_NOT_POSTED');
631 hr_utility.raise_error;
632 END IF;
633
634
635 --
636 -- Raise error if transfer_to_gl_flag <> Y and transfer_to_grants_flag <> Y
637 --
638 IF NVL(l_transfer_to_gl_flag,'N') <> 'Y' THEN
639 --
640 IF NVL(g_transfer_to_grants_flag,'N') <> 'Y' THEN
641 --
642 FND_MESSAGE.SET_NAME('PQH','PQH_BUDGET_TRANSFER_FLAG');
643 l_message_text_out := FND_MESSAGE.GET;
644 --
645 IF l_error_flag = 'Y' THEN
646 --
647 -- there is already an error so append the message
648 --
649 l_message_text := l_message_text||' **** '||l_message_text_out;
650 --
651 ELSE
652 --
653 -- new message
654 --
655 l_error_flag := 'Y';
656 l_message_text := l_message_text_out;
657 --
658 END IF;
659
660 END IF;
661 Else
662 -- check if rows in pqh_budget_gl_flex_maps with NULL cost segments
663 OPEN csr_cost_map_null(p_budget_id => g_budget_id);
664 FETCH csr_cost_map_null INTO l_map_count_null;
665 CLOSE csr_cost_map_null;
666
667 IF NVL(l_map_count_null,0) <> 0 THEN
668
669 -- get message text for PQH_BUDGET_GL_MAP
670 -- message: Some of the GL segments are not mapped with cost segments.
671 -- You must map all the GL segments with cost segments
672 FND_MESSAGE.SET_NAME('PQH','PQH_BUDGET_COST_SEGMENT_NULL');
673 l_message_text_out := FND_MESSAGE.GET;
674
675 IF l_error_flag = 'Y' THEN
676 -- there is already an error so append the message
677
678 l_message_text := l_message_text||' **** '||l_message_text_out;
679 ELSE
680 -- new message
681 l_message_text := l_message_text_out;
682 END IF;
683
684 -- set l_error_flag to Y
685 l_error_flag := 'Y';
686 END IF; -- l_map_count_null <> 0
687
688 END IF; -- if transfer_to_gl_flag IS Y
689 --
690 -- CHECK if the budget is mapped
691 --
692 OPEN csr_flex_maps_counts(p_budget_id => g_budget_id);
693 FETCH csr_flex_maps_counts INTO l_count;
694 CLOSE csr_flex_maps_counts;
695 --
696 -- CHECK : count <> 0 i.e mapping is defined
697 --
698 IF NVL(l_count,0) = 0 THEN
699 -- get message text for PQH_BUDGET_GL_MAP
700 -- message : Mapping with GL segments not defined for the budget
701 --
702 FND_MESSAGE.SET_NAME('PQH','PQH_BUDGET_GL_MAP');
703 l_message_text_out := FND_MESSAGE.GET;
704
705 IF l_error_flag = 'Y' THEN
706 --
707 -- there is already an error so append the message
708 --
709
710 l_message_text := l_message_text||' **** '||l_message_text_out;
711 --
712 ELSE
713 --
714 -- new message
715 --
716 l_message_text := l_message_text_out;
717 --
718 END IF;
719 --
720 -- set l_error_flag to Y
721 --
722 l_error_flag := 'Y';
723 --
724 END IF; -- count <> 0 i.e mapping is defined
725 --
726 -- get gl_budget_version_id
727 --
728 OPEN csr_gl_budget_version(p_gl_budget_name => l_gl_budget_name);
729 FETCH csr_gl_budget_version INTO l_gl_budget_versions_rec;
730 CLOSE csr_gl_budget_version;
731 --
732 g_gl_budget_version_id := l_gl_budget_versions_rec.budget_version_id;
733 --
734 -- CHECK : if gl_budget_version_id exists else error
735 --
736 IF g_gl_budget_version_id IS NULL THEN
737 --
738 FND_MESSAGE.SET_NAME('PQH','PQH_GL_BUDGET_INVALID');
739 l_message_text_out := FND_MESSAGE.GET;
740 --
741 IF l_error_flag = 'Y' THEN
742 --
743 -- there is already an error so append the message
744 --
745 l_message_text := l_message_text||' **** '||l_message_text_out;
746 --
747 ELSE
748 --
749 -- new message
750 --
751 l_error_flag := 'Y';
752 l_message_text := l_message_text_out;
753 --
754 END IF;
755 --
756 END IF; -- gl_budget_version_id is null
757 --
758 -- get encumbrance_type_id
759 --
760 open csr_gl_encumbrance_types;
761 fetch csr_gl_encumbrance_types into l_gl_encumbrance_type_id;
762 close csr_gl_encumbrance_types;
763 if l_gl_encumbrance_type_id is null then
764 --
765 FND_MESSAGE.SET_NAME('PQH','PQH_GL_ENC_TYP_INVALID');
766 l_message_text_out := FND_MESSAGE.GET;
767 --
768 IF l_error_flag = 'Y' THEN
769 --
770 -- there is already an error so append the message
771 --
772 l_message_text := l_message_text||' **** '||l_message_text_out;
773 --
774 ELSE
775 --
776 -- new message
777 --
778 l_error_flag := 'Y';
779 l_message_text := l_message_text_out;
780 --
781 END IF;
782 --
783 END IF;
784 --
785 -- get the set of books , budgetary control flag and currency for money
786 --
787 OPEN csr_chart_of_acc_id(p_set_of_books_id => l_budgets_rec.gl_set_of_books_id );
788 FETCH csr_chart_of_acc_id INTO l_gl_sets_of_books_rec;
789 CLOSE csr_chart_of_acc_id;
790 --
791 g_chart_of_accounts_id := l_gl_sets_of_books_rec.chart_of_accounts_id;
792 g_budgetary_control_flag := l_gl_sets_of_books_rec.enable_budgetary_control_flag;
793 --
794 if g_default_currency_code <> l_gl_sets_of_books_rec.currency_code then
795 --
796 -- currency used in Budget or Business group is different that in Set of books.
797 -- it is an error condition.
798 null;
799 --
800 End if;
801 --
802 --
803 -- get the je_source
804 --
805 OPEN csr_gl_je_sources;
806 FETCH csr_gl_je_sources INTO l_gl_je_sources_rec;
807 CLOSE csr_gl_je_sources;
808 --
809 g_user_je_source_name := l_gl_je_sources_rec.user_je_source_name;
810 --
811 -- CHECK : if g_user_je_source_name IS NOT NULL
812 --
813 IF g_user_je_source_name IS NULL THEN
814 --
815 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_JE_SOURCE_NAME');
816 l_message_text_out := FND_MESSAGE.GET;
817 --
818 IF l_error_flag = 'Y' THEN
819 --
820 -- there is already an error so append the message
821 --
822 l_message_text := l_message_text||' **** '||l_message_text_out;
823 --
824 ELSE
825 --
826 -- new message
827 --
828 l_error_flag := 'Y';
829 l_message_text := l_message_text_out;
830 --
831 END IF;
832 --
833 END IF; -- gl_user_je_source_name IS NOT NULL
834 --
835 -- get the je category
836 --
837 OPEN csr_gl_je_categories;
838 FETCH csr_gl_je_categories INTO l_gl_je_categories_rec;
839 CLOSE csr_gl_je_categories;
840
841 g_user_je_category_name := l_gl_je_categories_rec.user_je_category_name;
842 --
843 -- CHECK : if g_user_je_category_name IS NOT NULL
844 --
845 IF g_user_je_category_name IS NULL THEN
846 --
847 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_JE_CATEGORY_NAME');
848 l_message_text_out := FND_MESSAGE.GET;
849 --
850 IF l_error_flag = 'Y' THEN
851 --
852 -- there is already an error so append the message
853 --
854 l_message_text := l_message_text||' **** '||l_message_text_out;
855 --
856 ELSE
857 --
858 -- new message
859 --
860 l_error_flag := 'Y';
861 l_message_text := l_message_text_out;
862 --
863 END IF;
864 --
865 END IF; -- gl_user_je_category_name IS NOT NULL
866 --
867 -- populate the currency codes
868 --
869 OPEN csr_shared_types(p_shared_type_id => g_budget_uom1 );
870 FETCH csr_shared_types INTO l_shared_types_rec;
871 CLOSE csr_shared_types;
872
873 IF l_shared_types_rec.system_type_cd = 'MONEY' THEN
874 g_currency_code1 := g_default_currency_code;
875 ELSE
876 g_currency_code1 := 'STAT';
877 END IF;
878
879 IF g_budget_uom2 IS NOT NULL THEN
880
881 OPEN csr_shared_types(p_shared_type_id => g_budget_uom2 );
882 FETCH csr_shared_types INTO l_shared_types_rec;
883 CLOSE csr_shared_types;
884
885 IF l_shared_types_rec.system_type_cd = 'MONEY' THEN
886 g_currency_code2 := g_default_currency_code;
887 ELSE
888 g_currency_code2 := 'STAT';
889 END IF;
890
891 END IF; -- budget_unit2_id IS NOT NULL
892
893 IF g_budget_uom3 IS NOT NULL THEN
894
895 OPEN csr_shared_types(p_shared_type_id => g_budget_uom3 );
896 FETCH csr_shared_types INTO l_shared_types_rec;
897 CLOSE csr_shared_types;
898
899 IF l_shared_types_rec.system_type_cd = 'MONEY' THEN
900 g_currency_code3 := g_default_currency_code;
901 ELSE
902 g_currency_code3 := 'STAT';
903 END IF;
904
905 END IF; -- budget_unit3_id IS NOT NULL
906 --
907 -- get the table route id for pqh_budget versions
908 --
909 OPEN csr_table_route(p_table_alias => 'BVR');
910 FETCH csr_table_route INTO g_table_route_id_bvr;
911 CLOSE csr_table_route;
912
913 --
914 -- get the table route id for pqh_budget details
915 --
916 OPEN csr_table_route(p_table_alias => 'BDT');
917 FETCH csr_table_route INTO g_table_route_id_bdt;
918 CLOSE csr_table_route;
919
920 --
921 -- get the table route id for pqh_budget details
922 --
923 OPEN csr_table_route(p_table_alias => 'BPR');
924 FETCH csr_table_route INTO g_table_route_id_bpr;
925 CLOSE csr_table_route;
926
927 --
928 -- get the table route id for pqh_budget fund srcs
929 --
930 OPEN csr_table_route(p_table_alias => 'BFS');
931 FETCH csr_table_route INTO g_table_route_id_bfs;
932 CLOSE csr_table_route;
933
934 --
935 -- get the table route id for gl_bc_packets
936 --
937 OPEN csr_table_route(p_table_alias => 'GLF');
938 FETCH csr_table_route INTO g_table_route_id_glf;
939 CLOSE csr_table_route;
940
941 hr_utility.set_location('Budget Name : '||g_budget_name,100);
942 hr_utility.set_location('Set Of Books Id : '||g_set_of_books_id,110);
943 hr_utility.set_location('g_gl_budget_version_id : '||g_gl_budget_version_id,111);
944 hr_utility.set_location('g_budget_version_id : '||g_budget_version_id,112);
945 hr_utility.set_location('g_budgetary_control_flag : '||g_budgetary_control_flag,120);
946 hr_utility.set_location('g_budget_uom1 : '||to_char(g_budget_uom1),150);
947 hr_utility.set_location('g_budget_uom2 : '||to_char(g_budget_uom2),160);
948 hr_utility.set_location('g_budget_uom3 : '||to_char(g_budget_uom3),170);
949 hr_utility.set_location('g_currency_code1 : '||g_currency_code1,150);
950 hr_utility.set_location('g_currency_code2 : '||g_currency_code2,160);
951 hr_utility.set_location('g_currency_code3 : '||g_currency_code3,170);
952 hr_utility.set_location('g_user_je_source_name : '||g_user_je_source_name,180);
953 hr_utility.set_location('g_user_je_category_name : '||g_user_je_category_name,190);
954 --
955 -- if any errors the end the process log and abort the program
956 --
957 IF l_error_flag = 'Y' THEN
958 --
959 -- end the process log as the batch itself has error
960 --
961 populate_globals_error
962 (
963 p_message_text => l_message_text
964 );
965 --
966 -- abort the program
967 --
968 RAISE g_error_exception;
969 --
970 --
971 END IF; -- insert error message if l_error_flag is Y
972 --
973 hr_utility.set_location('Leaving:'||l_proc, 1000);
974 --
975 EXCEPTION
976 WHEN g_error_exception THEN
977 RAISE;
978 WHEN OTHERS THEN
979 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
980 hr_utility.set_message_token('ROUTINE', l_proc);
981 hr_utility.set_message_token('REASON', SQLERRM);
982 hr_utility.raise_error;
983 END fetch_global_values;
984 --
985 ---------------------------------------------------------------------------------------------------
986 --
987 Procedure build_budget_set_ratio_table(
988 p_budget_period_id IN pqh_budget_periods.budget_period_id%TYPE,
989 p_element_type_id IN pqh_budget_elements.element_type_id%TYPE,
990 p_budget_unit_seq IN number,
991 p_budget_ratio_table IN OUT NOCOPY t_ratio_table) IS
992 --
993 --
994 l_budget_ratio_table t_ratio_table := p_budget_ratio_table;
995 Cursor csr_budget_sets is
996 Select bst.budget_set_id,decode(p_budget_unit_seq,1,bst.budget_unit1_value,
997 2,bst.budget_unit2_value,
998 bst.budget_unit3_value)
999 From pqh_budget_sets bst
1000 Where bst.budget_period_id = p_budget_period_id;
1001 --
1002 Cursor csr_dist_money is
1003 Select distinct bst.budget_set_id,
1004 decode(p_budget_unit_seq,1,bst.budget_unit1_value,
1005 2,bst.budget_unit2_value,
1006 bst.budget_unit3_value)
1007 from pqh_budget_sets bst,pqh_budget_elements bel
1008 Where bst.budget_period_id = p_budget_period_id
1009 and bst.budget_set_id = bel.budget_set_id
1010 and bel.element_type_id = p_element_type_id ;
1011 --
1012 l_budget_set_id pqh_budget_sets.budget_set_id%type;
1013 l_budgeted_amt pqh_budget_sets.budget_unit1_value%type;
1014 l_total_budgeted_amt pqh_budget_sets.budget_unit1_value%type;
1015 cnt number(10) := 0;
1016 --
1017 --
1018 l_proc varchar2(72) := g_package||'build_budget_set_ratio_table';
1019 --
1020 Begin
1021 --
1022 hr_utility.set_location('Entering: '||l_proc, 5);
1023 --
1024 l_total_budgeted_amt := 0;
1025 --
1026 If p_element_type_id IS NULL then
1027 --
1028 Open csr_budget_sets;
1029 --
1030 loop
1031 --
1032 Fetch csr_budget_sets into l_budget_set_id,
1033 l_budgeted_amt;
1034 --
1035 exit when csr_budget_sets%notfound;
1036 --
1037 cnt := cnt + 1;
1038 --
1039 p_budget_ratio_table(cnt).budget_set_id := l_budget_set_id;
1040 p_budget_ratio_table(cnt).budgeted_amt := l_budgeted_amt;
1041 --
1042 l_total_budgeted_amt := l_total_budgeted_amt + l_budgeted_amt;
1043 --
1044 End loop;
1045 --
1046 Close csr_budget_sets;
1047 --
1048 Else
1049 --
1050 Open csr_dist_money;
1051 --
1052 loop
1053 --
1054 Fetch csr_dist_money into l_budget_set_id,
1055 l_budgeted_amt;
1056 --
1057 exit when csr_dist_money%notfound;
1058 --
1059 cnt := cnt + 1;
1060 --
1061 p_budget_ratio_table(cnt).budget_set_id := l_budget_set_id;
1062 p_budget_ratio_table(cnt).budgeted_amt := l_budgeted_amt;
1063 --
1064 l_total_budgeted_amt := l_total_budgeted_amt + l_budgeted_amt;
1065 --
1066 End loop;
1067 --
1068 Close csr_dist_money;
1069 --
1070 End if;
1071 --
1072 --
1073 If p_budget_ratio_table.COUNT > 0 then
1074 --
1075 hr_utility.set_location('-----No of budget Sets for Element '||to_char(p_element_type_id)||' = '||to_char(p_budget_ratio_table.COUNT),101);
1076 --
1077 --
1078 For cnt in 1..p_budget_ratio_table.COUNT loop
1079 --
1080 p_budget_ratio_table(cnt).budget_set_percent
1081 := p_budget_ratio_table(cnt).budgeted_amt / l_total_budgeted_amt;
1082 --
1083 -- Print Computed Values
1084 --
1085 hr_utility.set_location('-----TOTAL BUDGET :'||to_char(l_total_budgeted_amt),100);
1086 hr_utility.set_location('-----B SET BUDGET :'||to_char(p_budget_ratio_table(cnt).budgeted_amt),100);
1087 hr_utility.set_location('-----B SET PERCENT :'||to_char(p_budget_ratio_table(cnt).budget_set_percent),100);
1088 --
1089 End loop;
1090 --
1091 End if;
1092 --
1093 hr_utility.set_location('Leaving: '||l_proc, 10);
1094 --
1095 EXCEPTION
1096 WHEN OTHERS THEN
1097 p_budget_ratio_table := l_budget_ratio_table;
1098 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1099 hr_utility.set_message_token('ROUTINE', l_proc);
1100 hr_utility.set_message_token('REASON', SQLERRM);
1101 hr_utility.raise_error;
1102 End;
1103 --
1104 ----------------------------------------------------------------------------------------------------
1105 --
1106 Procedure update_money_dist_table(p_budget_ratio_table IN t_ratio_table,
1107 p_element_type_id IN number,
1108 p_commitment IN NUMBER,
1109 p_distribution_table IN OUT NOCOPY t_distribution_table) IS
1110 --
1111 cnt number(10);
1112 rec_no number(10);
1113 --
1114 l_distribution_table t_distribution_table := p_distribution_table;
1115
1116 --
1117 --
1118 l_proc varchar2(72) := g_package||'update_money_dist_table';
1119 --
1120 Begin
1121 --
1122 hr_utility.set_location('Entering: '||l_proc, 5);
1123 --
1124 -- Copy the appropriate budget set ratios.
1125 --
1126 If p_budget_ratio_table.COUNT > 0 AND p_distribution_table.COUNT > 0 then
1127 --
1128 For cnt in p_budget_ratio_table.FIRST .. p_budget_ratio_table.LAST loop
1129 --
1130 For rec_no in p_distribution_table.FIRST .. p_distribution_table.LAST loop
1131 --
1132 If p_distribution_table(rec_no).element_type_id = p_element_type_id
1133 AND p_budget_ratio_table(cnt).budget_set_id = p_distribution_table(rec_no).budget_set_id then
1134 --
1135 p_distribution_table(rec_no).budget_set_dist_percent :=
1136 p_budget_ratio_table(cnt).budget_set_percent;
1137 --
1138 End if;
1139 --
1140 End loop;
1141 --
1142 End loop;
1143 --
1144 End if;
1145 --
1146 -- Distribute the budget set among its elements and funding sources
1147 -- using their distribution percentages.
1148 --
1149 hr_utility.set_location('-------Recs in Dist table when breaking commitment :'||to_char(p_distribution_table.COUNT),100);
1150 --
1151 If p_distribution_table.COUNT > 0 then
1152 --
1153 For rec_no in p_distribution_table.FIRST .. p_distribution_table.LAST loop
1154 --
1155 -- Process only those budget sets that contain the current element type
1156 --
1157 If p_distribution_table(rec_no).element_type_id = p_element_type_id then
1158 --
1159 p_distribution_table(rec_no).budget_set_commitment := nvl(p_distribution_table(rec_no).budget_set_dist_percent,0) * p_commitment;
1160 --
1161 p_distribution_table(rec_no).element_commitment := nvl(p_distribution_table(rec_no).budget_set_commitment,0) ;
1162 --
1163 p_distribution_table(rec_no).fs_commitment := nvl(p_distribution_table(rec_no).element_commitment,0) * p_distribution_table(rec_no).fs_distribution_percentage * .01;
1164 --
1165 -- Print computed Values
1166 --
1167 hr_utility.set_location('-------B SETID : '||to_char(p_distribution_table(rec_no).budget_set_id),6);
1168 hr_utility.set_location('-------B SET COMMITMENT : '||to_char(p_distribution_table(rec_no).budget_set_commitment),6);
1169 hr_utility.set_location('-------ELMNT COMMITMENT : '||to_char(p_distribution_table(rec_no).element_commitment),7);
1170 hr_utility.set_location('-------FS PERCENT:'||to_char(p_distribution_table(rec_no).fs_distribution_percentage),8);
1171 hr_utility.set_location('-------FS COMMITMENT:'||to_char(p_distribution_table(rec_no).fs_commitment),9);
1172 --
1173 End if;
1174 --
1175 End loop;
1176 --
1177 End if;
1178 --
1179 hr_utility.set_location('Leaving: '||l_proc, 10);
1180 --
1181 EXCEPTION
1182 WHEN OTHERS THEN
1183 p_distribution_table := l_distribution_table;
1184 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1185 hr_utility.set_message_token('ROUTINE', l_proc);
1186 hr_utility.set_message_token('REASON', SQLERRM);
1187 hr_utility.raise_error;
1188 End update_money_dist_table;
1189 --
1190 -------------------------------------------------------------------------------------------------------
1191 --
1192 Procedure update_distribution_table(p_budget_ratio_table IN t_ratio_table,
1193 p_commitment IN NUMBER,
1194 p_distribution_table IN OUT NOCOPY t_distribution_table) IS
1195 --
1196 cnt number(10);
1197 rec_no number(10);
1198 l_distribution_table t_distribution_table := p_distribution_table;
1199 --
1200 --
1201 l_proc varchar2(72) := g_package||'update_distribution_table';
1202 --
1203 Begin
1204 --
1205 hr_utility.set_location('Entering: '||l_proc, 5);
1206 --
1207 -- Copy the appropriate budget set ratios.
1208 --
1209 hr_utility.set_location('DISTRIBUTING NON MONEY COMMITMENTS !', 6);
1210 --
1211 If p_budget_ratio_table.COUNT > 0 then
1212 --
1213 For cnt in p_budget_ratio_table.FIRST .. p_budget_ratio_table.LAST loop
1214 --
1215 If p_distribution_table.COUNT > 0 then
1216 --
1217 For rec_no in p_distribution_table.FIRST .. p_distribution_table.LAST loop
1218 --
1219 If p_budget_ratio_table(cnt).budget_set_id = p_distribution_table(rec_no).budget_set_id then
1220 --
1221 --
1222 p_distribution_table(rec_no).budget_set_dist_percent := nvl(p_budget_ratio_table(cnt).budget_set_percent,0);
1223 --
1224 End if;
1225 --
1226 End loop;
1227 --
1228 End if;
1229 --
1230 End loop;
1231 --
1232 End if;
1233 --
1234 -- Distribute the budget set among its elements and funding sources using their
1235 -- distribution percentages.
1236 --
1237 If p_distribution_table.COUNT > 0 then
1238 --
1239 For rec_no in p_distribution_table.FIRST .. p_distribution_table.LAST loop
1240 --
1241 p_distribution_table(rec_no).budget_set_commitment := nvl(p_distribution_table(rec_no).budget_set_dist_percent,0) * p_commitment;
1242 --
1243 p_distribution_table(rec_no).element_commitment := nvl(p_distribution_table(rec_no).budget_set_commitment,0) * p_distribution_table(rec_no).el_distribution_percentage * .01;
1244 --
1245 p_distribution_table(rec_no).fs_commitment := nvl(p_distribution_table(rec_no).element_commitment,0) * p_distribution_table(rec_no).fs_distribution_percentage * .01;
1246 --
1247 -- Print Computed Values
1248 --
1249 hr_utility.set_location('----B SET commitment :'||to_char(p_distribution_table(rec_no).budget_set_commitment),7);
1250 hr_utility.set_location('----EL commitment :'||to_char(p_distribution_table(rec_no).element_commitment),8);
1251 hr_utility.set_location('----FS commitment :'||to_char(p_distribution_table(rec_no).fs_commitment),9);
1252 --
1253 --
1254 End loop;
1255 --
1256 End if;
1257 --
1258 hr_utility.set_location('Leaving: '||l_proc, 10);
1259 --
1260 EXCEPTION
1261 WHEN OTHERS THEN
1262 p_distribution_table := l_distribution_table;
1263 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1264 hr_utility.set_message_token('ROUTINE', l_proc);
1265 hr_utility.set_message_token('REASON', SQLERRM);
1266 hr_utility.raise_error;
1267 End update_distribution_table;
1268 --
1269 --------------------------------------------------------------------------------------------------------------
1270 --
1271 PROCEDURE build_period_commitment (p_budget_period_id IN pqh_budget_periods.budget_period_id%TYPE,
1272 p_distribution_table IN t_distribution_table,
1273 p_budget_unit_seq IN number) IS
1274 --
1275 cnt NUMBER(10);
1276 rec_no NUMBER(10);
1277 --
1278 --
1279 l_proc varchar2(72) := g_package||'build_period_commitment';
1280 --
1281 Begin
1282 --
1283 hr_utility.set_location('Entering: '||l_proc, 5);
1284 --
1285 --
1286 If g_period_amt_tab.COUNT > 0 AND p_distribution_table.COUNT > 0 then
1287 --
1288 For cnt in g_period_amt_tab.FIRST .. g_period_amt_tab.LAST loop
1289 --
1290 For rec_no in p_distribution_table.FIRST .. p_distribution_table.LAST loop
1291 --
1292 --
1293 if(g_period_amt_tab(cnt).cost_allocation_keyflex_id is not null AND
1294 p_distribution_table(rec_no).cost_allocation_keyflex_id is not null) THEN
1295 --
1296 --
1297 if p_distribution_table(rec_no).budget_period_id = g_period_amt_tab(cnt).period_id AND
1298 p_distribution_table(rec_no).cost_allocation_keyflex_id = g_period_amt_tab(cnt).cost_allocation_keyflex_id then
1299 --
1300 hr_utility.set_location('Period is :'||to_char(g_period_amt_tab(cnt).period_id),100);
1301
1302 If p_budget_unit_seq = 1 then
1303 --
1304 hr_utility.set_location('CF Commitment1 :'||to_char(g_period_amt_tab(cnt).commitment1),100);
1305 g_period_amt_tab(cnt).commitment1 :=
1306 nvl(g_period_amt_tab(cnt).commitment1,0)+
1307 nvl(p_distribution_table(rec_no).fs_commitment,0);
1308 --
1309 Elsif p_budget_unit_seq = 2 then
1310 --
1311 hr_utility.set_location('CF Commitment2 : '||to_char(g_period_amt_tab(cnt).commitment2),100);
1312 g_period_amt_tab(cnt).commitment2 :=
1313 nvl(g_period_amt_tab(cnt).commitment2 ,0)+
1314 nvl(p_distribution_table(rec_no).fs_commitment,0);
1315 --
1316 Elsif p_budget_unit_seq = 3 then
1317 --
1318 hr_utility.set_location('CF Commitment3 : '||to_char(g_period_amt_tab(cnt).commitment3),100);
1319 g_period_amt_tab(cnt).commitment3 :=
1320 nvl(g_period_amt_tab(cnt).commitment3,0)+
1321 nvl(p_distribution_table(rec_no).fs_commitment,0);
1322 --
1323 End if;
1324 --
1325 End if;
1326 --
1327 --
1328 elsif (g_period_amt_tab(cnt).cost_allocation_keyflex_id is null AND
1329 p_distribution_table(rec_no).cost_allocation_keyflex_id is null) THEN
1330 --
1331 --
1332 if p_distribution_table(rec_no).budget_period_id = g_period_amt_tab(cnt).period_id AND
1333 p_distribution_table(rec_no).project_id = g_period_amt_tab(cnt).project_id AND
1334 p_distribution_table(rec_no).task_id = g_period_amt_tab(cnt).task_id AND
1335 p_distribution_table(rec_no).award_id = g_period_amt_tab(cnt).award_id AND
1336 p_distribution_table(rec_no).expenditure_type = g_period_amt_tab(cnt).expenditure_type AND
1337 p_distribution_table(rec_no).organization_id = g_period_amt_tab(cnt).organization_id
1338 then
1339 --
1340 hr_utility.set_location('Period is :'||to_char(g_period_amt_tab(cnt).period_id),100);
1341
1342 If p_budget_unit_seq = 1 then
1343 --
1344 hr_utility.set_location('CF Commitment1 :'||to_char(g_period_amt_tab(cnt).commitment1),100);
1345 g_period_amt_tab(cnt).commitment1 :=
1346 nvl(g_period_amt_tab(cnt).commitment1,0)+
1347 nvl(p_distribution_table(rec_no).fs_commitment,0);
1348 --
1349 Elsif p_budget_unit_seq = 2 then
1350 --
1351 hr_utility.set_location('CF Commitment2 : '||to_char(g_period_amt_tab(cnt).commitment2),100);
1352 g_period_amt_tab(cnt).commitment2 :=
1353 nvl(g_period_amt_tab(cnt).commitment2 ,0)+
1354 nvl(p_distribution_table(rec_no).fs_commitment,0);
1355 --
1356 Elsif p_budget_unit_seq = 3 then
1357 --
1358 hr_utility.set_location('CF Commitment3 : '||to_char(g_period_amt_tab(cnt).commitment3),100);
1359 g_period_amt_tab(cnt).commitment3 :=
1360 nvl(g_period_amt_tab(cnt).commitment3,0)+
1361 nvl(p_distribution_table(rec_no).fs_commitment,0);
1362 --
1363 End if;
1364 --
1365 End if;
1366 --
1367 --
1368 End IF;
1369 --
1370 --
1371 End loop;
1372 --
1373 End loop;
1374 End if;
1375 --
1376 hr_utility.set_location('Leaving: '||l_proc, 10);
1377 --
1378 EXCEPTION
1379 WHEN OTHERS THEN
1380 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1381 hr_utility.set_message_token('ROUTINE', l_proc);
1382 hr_utility.set_message_token('REASON', SQLERRM);
1383 hr_utility.raise_error;
1384 --
1385 End;
1386 --
1387 -------------------------------------------------------------------------------------
1388 --
1389 Procedure distribute_other_commitments
1390 (p_budget_version_id IN pqh_budget_versions.budget_version_id%type,
1391 p_position_id IN pqh_budget_details.position_id%type,
1392 p_organization_id IN pqh_budget_details.organization_id%type,
1393 p_job_id IN pqh_budget_details.job_id%type,
1394 p_grade_id IN pqh_budget_details.grade_id%type,
1395 p_budget_period_id IN pqh_budget_periods.budget_period_id%type,
1396 p_budget_unit_seq IN number,
1397 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%type,
1398 p_effective_date IN varchar2) IS
1399 --
1400 l_period_start_date per_time_periods.start_date%type := NULL;
1401 l_period_end_date per_time_periods.end_date%type := NULL;
1402 l_commitment number := NULL;
1403 l_distribution_table t_distribution_table;
1404 l_budget_ratio_table t_ratio_table;
1405 l_budget_entity pqh_budgets.budgeted_entity_cd%type := NULL;
1406 l_business_group_id pqh_budgets.business_group_id%type :=NULL;
1407 l_commt_value number;
1408 l_effective_dt date;
1409 --
1410 Cursor csr_get_budget_entity_cd is
1411 Select BGT.BUDGETED_ENTITY_CD , BGT.BUSINESS_GROUP_ID
1412 From PQH_BUDGETS BGT,
1413 PQH_BUDGET_VERSIONS BVR
1414 Where BGT.BUDGET_ID = BVR.BUDGET_ID
1415 And BGT.POSITION_CONTROL_FLAG ='Y'
1416 And l_effective_dt BETWEEN BGT.BUDGET_START_DATE AND BGT.BUDGET_END_DATE
1417 And BVR.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID;
1418 l_proc varchar2(72) := g_package||'distribute_other_commitments';
1419 --
1420 Begin
1421 --
1422 hr_utility.set_location('Entering: '||l_proc, 5);
1423 --
1424 l_effective_dt := fnd_date.canonical_to_date(p_effective_date);
1425 --
1426 -- Obtain the start and end date of the period from per_time_periods
1427 --
1428 get_period_dates(p_budget_period_id => p_budget_period_id,
1429 p_period_start_date => l_period_start_date,
1430 p_period_end_date => l_period_end_date);
1431 --
1432 -- For the period and for the specified position , find the commitment
1433 --
1434 hr_utility.set_location('UOM : '||to_char(p_unit_of_measure_id),7);
1435 --
1436 Open csr_get_budget_entity_cd;
1437
1438 Fetch csr_get_budget_entity_cd into l_budget_entity,l_business_group_id;
1439
1440 close csr_get_budget_entity_cd;
1441 --
1442 pqh_bdgt_actual_cmmtmnt_pkg.get_actual_and_cmmtmnt
1443 (p_budget_entity => l_budget_entity,
1444 p_position_id => p_position_id,
1445 p_organization_id => p_organization_id,
1446 p_job_id => p_job_id,
1447 p_grade_id => p_grade_id,
1448 p_element_type_id => NULL,
1449 p_start_date => l_period_start_date,
1450 p_end_date => l_period_end_date,
1451 p_unit_of_measure => hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(p_unit_of_measure_id),
1452 p_commt_value => l_commt_value,
1453 p_actual_value => l_commitment,
1454 p_business_group_id => l_business_group_id,
1455 p_effective_date => l_effective_dt );
1456
1457 --p_value_type => 'C', REMOVED FROM PARAMETER LIST
1458 --
1459 -- We now have to start distributing the commitment .
1460 -- Step 1: Determine the distribution_percent for the budget sets.
1461 --
1462 build_budget_set_ratio_table(p_budget_period_id => p_budget_period_id,
1463 p_element_type_id => NULL,
1464 p_budget_unit_seq => p_budget_unit_seq,
1465 p_budget_ratio_table => l_budget_ratio_table);
1466
1467 --
1468 -- Step 2: Distribute the commitment among the budget set,elements and
1469 -- funding sources.
1470 --
1471 -- copy values from global table to distribution table.
1472 --
1473 l_distribution_table := g_distribution_table;
1474 --
1475 update_distribution_table(p_budget_ratio_table => l_budget_ratio_table,
1476 p_commitment => l_commitment,
1477 p_distribution_table => l_distribution_table);
1478 --
1479 -- Step 3: Obtain commitment for each cost alloc flexfield under the period
1480 --
1481 build_period_commitment(p_budget_period_id => p_budget_period_id,
1482 p_budget_unit_seq => p_budget_unit_seq,
1483 p_distribution_table => l_distribution_table);
1484 --
1485 hr_utility.set_location('Leaving: '||l_proc, 10);
1486 --
1487 EXCEPTION
1488 WHEN OTHERS THEN
1489 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1490 hr_utility.set_message_token('ROUTINE', l_proc);
1491 hr_utility.set_message_token('REASON', SQLERRM);
1492 hr_utility.raise_error;
1493 End;
1494 --
1495 -------------------------------------------------------------------------------------
1496 --
1497 Procedure distribute_money_commitments
1498 (p_budget_version_id IN pqh_budget_versions.budget_version_id%type,
1499 p_position_id IN pqh_budget_details.position_id%type,
1500 p_organization_id IN pqh_budget_details.organization_id%type,
1501 p_job_id IN pqh_budget_details.job_id%type,
1502 p_grade_id IN pqh_budget_details.grade_id%type,
1503 p_budget_period_id IN pqh_budget_periods.budget_period_id%type,
1504 p_budget_unit_seq IN number,
1505 p_unit_of_measure_id IN pqh_budgets.budget_unit1_id%type,
1506 p_effective_date IN varchar2) IS
1507 --
1508 l_period_start_date per_time_periods.start_date%type := NULL;
1509 l_period_end_date per_time_periods.end_date%type := NULL;
1510 --
1511 l_element_type_id pay_element_types_f.element_type_id%TYPE := NULL;
1512 l_budget_entity pqh_budgets.budgeted_entity_cd%type := NULL;
1513 l_business_group_id pqh_budgets.business_group_id%type := NULL;
1514 l_effective_dt date;
1515 --
1516 Cursor csr_get_budget_entity_cd is
1517 Select BGT.BUDGETED_ENTITY_CD, BGT.BUSINESS_GROUP_ID
1518 From PQH_BUDGETS BGT,
1519 PQH_BUDGET_VERSIONS BVR
1520 Where BGT.BUDGET_ID = BVR.BUDGET_ID
1521 And BGT.POSITION_CONTROL_FLAG ='Y'
1522 And l_effective_dt BETWEEN BGT.BUDGET_START_DATE AND BGT.BUDGET_END_DATE
1523 And BVR.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID;
1524
1525
1526 --
1527 Cursor csr_bdgt_elmnts is
1528 Select distinct bel.element_type_id
1529 from pqh_budget_sets bst,pqh_budget_elements bel
1530 Where bst.budget_period_id = p_budget_period_id
1531 and bst.budget_set_id = bel.budget_set_id;
1532 --
1533 l_commitment number := NULL;
1534 l_distribution_table t_distribution_table;
1535 l_commt_value number;
1536 --
1537 l_budget_ratio_table t_ratio_table;
1538
1539 --
1540 --
1541 l_proc varchar2(72) := g_package||'distribute_money_commitments';
1542 --
1543 Begin
1544 --
1545 hr_utility.set_location('Entering: '||l_proc, 5);
1546 --
1547 l_effective_dt := fnd_date.canonical_to_date(p_effective_date);
1548 --
1549 hr_utility.set_location('UOM : '||to_char(p_unit_of_measure_id),7);
1550 -- Obtain the start and end date of the period from per_time_periods
1551 --
1552 get_period_dates(p_budget_period_id => p_budget_period_id,
1553 p_period_start_date => l_period_start_date,
1554 p_period_end_date => l_period_end_date);
1555 --
1556 -- copy values from global table to distribution table.
1557 --
1558 l_distribution_table := g_distribution_table;
1559 --
1560 -- For the period and for the specified position , find the commitment
1561 --
1562 Open csr_get_budget_entity_cd;
1563
1564 Fetch csr_get_budget_entity_cd into l_budget_entity,l_business_group_id;
1565
1566 close csr_get_budget_entity_cd;
1567 --
1568 hr_utility.set_location('-----Period :'||to_char(l_period_start_date,'DD/MM/RRRR')||' -'||to_char(l_period_end_date,'DD/MM/RRRR'),100);
1569 Open csr_bdgt_elmnts;
1570 loop
1571
1572 Fetch csr_bdgt_elmnts into l_element_type_id;
1573 --
1574 exit when csr_bdgt_elmnts%notfound;
1575 --
1576 hr_utility.set_location('-----Processing Element:'||to_char(l_element_type_id),100);
1577 pqh_bdgt_actual_cmmtmnt_pkg.get_actual_and_cmmtmnt
1578 (p_budget_entity => l_budget_entity,
1579 p_position_id => p_position_id,
1580 p_organization_id => p_organization_id,
1581 p_job_id => p_job_id,
1582 p_grade_id => p_grade_id,
1583 p_element_type_id => l_element_type_id,
1584 p_start_date => l_period_start_date,
1585 p_end_date => l_period_end_date,
1586 p_unit_of_measure => hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(p_unit_of_measure_id),
1587 p_commt_value => l_commt_value,
1588 p_actual_value => l_commitment,
1589 p_business_group_id => l_business_group_id,
1590 p_effective_date => l_effective_dt);
1591
1592 --p_value_type => 'C', commented
1593
1594 hr_utility.set_location('-----ELEMENT COMMITMENT:'||to_char(l_commitment),100);
1595 --
1596 -- We now have to start distributing the commitment .
1597 -- Step 1: Determine the distribution_percent for the budget sets.
1598 --
1599 build_budget_set_ratio_table(p_budget_period_id => p_budget_period_id,
1600 p_element_type_id => l_element_type_id,
1601 p_budget_unit_seq => p_budget_unit_seq,
1602 p_budget_ratio_table => l_budget_ratio_table);
1603
1604 --
1605 -- Step 2: Distribute the commitment among the budget set,elements and
1606 -- funding sources.
1607 --
1608 update_money_dist_table (p_budget_ratio_table => l_budget_ratio_table,
1609 p_commitment => l_commt_value, /* l_commitment,*/
1610 p_element_type_id => l_element_type_id,
1611 p_distribution_table => l_distribution_table);
1612 --
1613 End loop;
1614 --
1615 Close csr_bdgt_elmnts;
1616 --
1617 -- Step 3: Obtain commitment for each cost alloc flexfield under the period
1618 --
1619 build_period_commitment(p_budget_period_id => p_budget_period_id,
1620 p_budget_unit_seq => p_budget_unit_seq,
1621 p_distribution_table => l_distribution_table);
1622 --
1623 hr_utility.set_location('Leaving: '||l_proc, 10);
1624 --
1625 EXCEPTION
1626 WHEN OTHERS THEN
1627 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1628 hr_utility.set_message_token('ROUTINE', l_proc);
1629 hr_utility.set_message_token('REASON', SQLERRM);
1630 hr_utility.raise_error;
1631 End;
1632 --
1633 --
1634 ---------------------------------------------------------------------------------
1635 --
1636 -- This procedure will fetch the commitment for each unit of measure for a
1637 -- budget detail and distribute it to the funding sources.
1638 --
1639 --
1640 PROCEDURE populate_period_commitment_tab (
1641 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE,
1642 p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE,
1643 p_position_id IN pqh_budget_details.position_id%TYPE,
1644 p_organization_id IN pqh_budget_details.organization_id%TYPE,
1645 p_job_id IN pqh_budget_details.job_id%TYPE,
1646 p_grade_id IN pqh_budget_details.grade_id%TYPE,
1647 p_effective_date IN varchar2) IS
1648 --
1649 -- local variables
1650 --
1651 l_budget_period_id pqh_budget_periods.budget_period_id%TYPE;
1652 l_budget_set_id pqh_budget_sets.budget_set_id%TYPE;
1653 l_budget_element_id pqh_budget_elements.budget_element_id%TYPE;
1654 l_element_type_id pqh_budget_elements.element_type_id%TYPE;
1655 l_el_distribution_percentage pqh_budget_fund_srcs.distribution_percentage%TYPE;
1656 l_budget_fund_src_id pqh_budget_fund_srcs.budget_fund_src_id%TYPE;
1657 l_cost_allocation_keyflex_id pqh_budget_fund_srcs.cost_allocation_keyflex_id%TYPE;
1658 l_project_id pqh_budget_fund_srcs.project_id%TYPE;
1659 l_task_id pqh_budget_fund_srcs.task_id%TYPE;
1660 l_award_id pqh_budget_fund_srcs.award_id%TYPE;
1661 l_expenditure_type pqh_budget_fund_srcs.expenditure_type%TYPE;
1662 l_organization_id pqh_budget_fund_srcs.organization_id%TYPE;
1663 l_fs_distribution_percentage pqh_budget_fund_srcs.distribution_percentage%TYPE;
1664 --
1665 cnt NUMBER(10) := 0;
1666 --
1667 --
1668 CURSOR csr_period_break(p_budget_period_id IN NUMBER) IS
1669 SELECT bst.budget_set_id,
1670 bel.budget_element_id,bel.element_type_id,bel.distribution_percentage,
1671 bfs.budget_fund_src_id,bfs.cost_allocation_keyflex_id,
1672 bfs.project_id,bfs.task_id,bfs.award_id,
1673 bfs.expenditure_type,bfs.organization_id,
1674 bfs.distribution_percentage
1675 FROM pqh_budget_fund_srcs bfs, pqh_budget_elements bel,
1676 pqh_budget_sets bst
1677 WHERE bst.budget_period_id = p_budget_period_id
1678 AND bst.budget_set_id = bel.budget_set_id
1679 AND bel.budget_element_id = bfs.budget_element_id;
1680 --
1681 Cursor csr_fund_srcs(p_budget_detail_id IN NUMBER) is
1682 Select bpr.budget_period_id ,
1683 bfs.cost_allocation_keyflex_id,
1684 bfs.project_id,
1685 bfs.task_id,
1686 bfs.award_id,
1687 bfs.expenditure_type,
1688 bfs.organization_id
1689 FROM pqh_budget_fund_srcs bfs, pqh_budget_elements bel,
1690 pqh_budget_sets bst, pqh_budget_periods bpr
1691 WHERE bpr.budget_detail_id = p_budget_detail_id
1692 AND bpr.budget_period_id = bst.budget_period_id
1693 AND bst.budget_set_id = bel.budget_set_id
1694 AND bel.budget_element_id = bfs.budget_element_id
1695 GROUP BY bpr.budget_period_id ,bfs.cost_allocation_keyflex_id,bfs.project_id,
1696 bfs.task_id,bfs.award_id,bfs.expenditure_type, bfs.organization_id;
1697 --
1698 Cursor csr_bdgt_periods is
1699 Select bpr.budget_period_id
1700 From pqh_budget_periods bpr
1701 Where bpr.budget_detail_id = p_budget_detail_id;
1702 --
1703 l_proc varchar2(72) := g_package||'populate_period_commitment_tab';
1704 --
1705 Begin
1706 --
1707 hr_utility.set_location('Entering: '||l_proc, 5);
1708 --
1709 -- Clear the g_distribution_table
1710 --
1711 g_period_amt_tab.DELETE;
1712 --
1713 -- Build a global table with all period,cost allocation flexfield under the budget_detail
1714 --
1715 Open csr_fund_srcs(p_budget_detail_id => p_budget_detail_id);
1716 --
1717 loop
1718 --
1719 fetch csr_fund_srcs into l_budget_period_id, l_cost_allocation_keyflex_id,l_project_id,
1720 l_task_id,l_award_id,l_expenditure_type, l_organization_id;
1721 --
1722 exit when csr_fund_srcs%notfound;
1723 --
1724 cnt := cnt + 1;
1725 --
1726 g_period_amt_tab(cnt).period_id := l_budget_period_id;
1727 g_period_amt_tab(cnt).cost_allocation_keyflex_id := l_cost_allocation_keyflex_id;
1728 g_period_amt_tab(cnt).project_id := l_project_id;
1729 g_period_amt_tab(cnt).task_id := l_task_id;
1730 g_period_amt_tab(cnt).award_id := l_award_id;
1731 g_period_amt_tab(cnt).expenditure_type := l_expenditure_type;
1732 g_period_amt_tab(cnt).organization_id := l_organization_id;
1733 --
1734 End loop;
1735 Close csr_fund_srcs;
1736 --
1737 -- Process each period , one by one
1738 --
1739 OPEN csr_bdgt_periods;
1740 --
1741 LOOP
1742 --
1743 FETCH csr_bdgt_periods into l_budget_period_id;
1744 --
1745 EXIT WHEN csr_bdgt_periods%NOTFOUND;
1746 --
1747 -- Select all the budget sets , elements and funding sources under that period
1748 -- and store it in a global table . We will use this table to distribute
1749 -- commitments.
1750 --
1751 hr_utility.set_location('---------------------------------------',101);
1752 hr_utility.set_location('Processing period :'||to_char(l_budget_period_id),101);
1753 --
1754 cnt := 0;
1755 g_distribution_table.DELETE;
1756 --
1757 OPEN csr_period_break(p_budget_period_id => l_budget_period_id);
1758 LOOP
1759 --
1760 FETCH csr_period_break INTO l_budget_set_id,
1761 l_budget_element_id,l_element_type_id,
1762 l_el_distribution_percentage,
1763 l_budget_fund_src_id,l_cost_allocation_keyflex_id,
1764 l_project_id,l_task_id,l_award_id,l_expenditure_type,
1765 l_organization_id,l_fs_distribution_percentage;
1766 --
1767 EXIT WHEN csr_period_break%NOTFOUND;
1768 --
1769 cnt := cnt + 1;
1770 --
1771 g_distribution_table(cnt).budget_period_id := l_budget_period_id;
1772 g_distribution_table(cnt).budget_set_id := l_budget_set_id;
1773 g_distribution_table(cnt).budget_element_id := l_budget_element_id;
1774 g_distribution_table(cnt).element_type_id := l_element_type_id;
1775 g_distribution_table(cnt).el_distribution_percentage := l_el_distribution_percentage;
1776 g_distribution_table(cnt).budget_fund_src_id := l_budget_fund_src_id;
1777 g_distribution_table(cnt).cost_allocation_keyflex_id := l_cost_allocation_keyflex_id;
1778 g_distribution_table(cnt).project_id := l_project_id;
1779 g_distribution_table(cnt).task_id := l_task_id;
1780 g_distribution_table(cnt).award_id := l_award_id;
1781 g_distribution_table(cnt).expenditure_type := l_expenditure_type;
1782 g_distribution_table(cnt).organization_id := l_organization_id;
1783 g_distribution_table(cnt).fs_distribution_percentage := l_fs_distribution_percentage;
1784 --
1785 hr_utility.set_location('--Period :'||to_char(l_budget_period_id),100);
1786 hr_utility.set_location('--Budget Set :'||to_char(l_budget_set_id),100);
1787 hr_utility.set_location('--Element :'||to_char(l_element_type_id),100);
1788 hr_utility.set_location('--FS :'||to_char(l_cost_allocation_keyflex_id),100);
1789 END LOOP;
1790 --
1791 hr_utility.set_location('Out of loop ',120);
1792 CLOSE csr_period_break;
1793 --
1794 -- For the selected period , we will determine commitments for the 3 units of measure.
1795 -- For each unit of measure, we will distribute the commitment to its various
1796 -- funding sources and get the commitment amounts by period, flexfield.
1797 --
1798 hr_utility.set_location('Currency Processed :'||g_currency_code1,100);
1799 If g_currency_code1 = 'STAT' then
1800 --
1801 null;
1802 --
1803 /* Commented Call to the following procedure as GL does not understand
1804 'STAT' as an input and gives a EEO3 error in GL import report. The error
1805 message is EEO3 : Encumbrances can't be in STAT */
1806 --
1807 /* distribute_other_commitments
1808 (p_budget_version_id => p_budget_version_id,
1809 p_position_id => p_position_id,
1810 p_organization_id => p_organization_id,
1811 p_job_id => p_job_id,
1812 p_grade_id => p_grade_id,
1813 p_budget_period_id => l_budget_period_id,
1814 p_budget_unit_seq => 1,
1815 p_unit_of_measure_id => g_budget_uom1,
1816 p_effective_date => p_effective_date); */
1817 --
1818 Else
1819 --
1820 distribute_money_commitments
1821 (p_budget_version_id => p_budget_version_id,
1822 p_position_id => p_position_id,
1823 p_organization_id => p_organization_id,
1824 p_job_id => p_job_id,
1825 p_grade_id => p_grade_id,
1826 p_budget_period_id => l_budget_period_id,
1827 p_budget_unit_seq => 1,
1828 p_unit_of_measure_id => g_budget_uom1,
1829 p_effective_date => p_effective_date);
1830 --
1831 End if;
1832 --
1833 If g_budget_uom2 IS NOT NULL then
1834 --
1835 hr_utility.set_location('Currency Processed :'||g_currency_code2,100);
1836 --
1837 If g_currency_code2 = 'STAT' then
1838 --
1839 null;
1840 --
1841 /* Commented Call to the following procedure as GL does not understand
1842 'STAT' as an input and gives a EEO3 error in GL import report. The error
1843 message is EEO3 : Encumbrances can't be in STAT */
1844 --
1845 /* distribute_other_commitments
1846 (p_budget_version_id => p_budget_version_id,
1847 p_position_id => p_position_id,
1848 p_organization_id => p_organization_id,
1849 p_job_id => p_job_id,
1850 p_grade_id => p_grade_id,
1851 p_budget_period_id => l_budget_period_id,
1852 p_budget_unit_seq => 2,
1853 p_unit_of_measure_id => g_budget_uom2,
1854 p_effective_date => p_effective_date); */
1855 --
1856 Else
1857 --
1858 distribute_money_commitments
1859 (p_budget_version_id => p_budget_version_id,
1860 p_position_id => p_position_id,
1861 p_organization_id => p_organization_id,
1862 p_job_id => p_job_id,
1863 p_grade_id => p_grade_id,
1864 p_budget_period_id => l_budget_period_id,
1865 p_budget_unit_seq => 2,
1866 p_unit_of_measure_id => g_budget_uom2,
1867 p_effective_date => p_effective_date);
1868 --
1869 End if;
1870 --
1871 End if;
1872 --
1873 If g_budget_uom3 IS NOT NULL then
1874 --
1875 hr_utility.set_location('Currency Processed :'||g_currency_code3,100);
1876 If g_currency_code3 = 'STAT' then
1877 --
1878 null;
1879 --
1880 /* Commented Call to the following procedure as GL does not understand
1881 'STAT' as an input and gives a EEO3 error in GL import report. The error
1882 message is EEO3 : Encumbrances can't be in STAT */
1883 --
1884 /* distribute_other_commitments
1885 (p_budget_version_id => p_budget_version_id,
1886 p_position_id => p_position_id,
1887 p_organization_id => p_organization_id,
1888 p_job_id => p_job_id,
1889 p_grade_id => p_grade_id,
1890 p_budget_period_id => l_budget_period_id,
1891 p_budget_unit_seq => 3,
1892 p_unit_of_measure_id => g_budget_uom3,
1893 p_effective_date => p_effective_date); */
1894 --
1895 Else
1896 --
1897 distribute_money_commitments
1898 (p_budget_version_id => p_budget_version_id,
1899 p_position_id => p_position_id,
1900 p_organization_id => p_organization_id,
1901 p_job_id => p_job_id,
1902 p_grade_id => p_grade_id,
1903 p_budget_period_id => l_budget_period_id,
1904 p_budget_unit_seq => 3,
1905 p_unit_of_measure_id => g_budget_uom3,
1906 p_effective_date => p_effective_date);
1907 --
1908 End if;
1909 --
1910 End if;
1911 --
1912 END LOOP;
1913 --
1914 CLOSE csr_bdgt_periods;
1915 --
1916 --
1917 hr_utility.set_location('Leaving:'||l_proc, 1000);
1918 --
1919 EXCEPTION
1920 WHEN OTHERS THEN
1921 hr_utility.set_message(8302,'PQH_CATASTROPHIC_ERROR');
1922 hr_utility.set_message_token('ROUTINE', l_proc);
1923 hr_utility.set_message_token('REASON', SQLERRM);
1924 hr_utility.raise_error;
1925 --
1926 END populate_period_commitment_tab;
1927 --
1928 -------------------------------------------------------------------------------------------
1929 --
1930 PROCEDURE get_gl_period
1931 (
1932 p_budget_period_id IN pqh_budget_periods.budget_period_id%TYPE,
1933 p_set_of_books_id IN pqh_budgets.gl_set_of_books_id%type,
1934 p_gl_period_statuses_rec OUT NOCOPY gl_period_statuses%ROWTYPE
1935 ) IS
1936 --
1937 -- This procedure will return the period name corresponding to start_date between
1938 -- gl_period_statuses.start_date and gl_period_statuses.end_date
1939 --
1940 -- local variables
1941 --
1942 l_start_date DATE;
1943 l_gl_period_statuses_rec gl_period_statuses%ROWTYPE;
1944 --
1945 CURSOR csr_time_period IS
1946 SELECT start_date
1947 FROM per_time_periods
1948 WHERE time_period_id = ( SELECT start_time_period_id
1949 FROM pqh_budget_periods
1950 WHERE budget_period_id = p_budget_period_id );
1951 --
1952 CURSOR csr_period_name( p_start_date IN DATE ) IS
1953 SELECT *
1954 FROM gl_period_statuses
1955 WHERE application_id = g_application_id
1956 AND set_of_books_id = g_set_of_books_id
1957 AND closing_status = 'O'
1958 AND p_start_date BETWEEN start_date AND end_date;
1959
1960 --
1961 l_proc varchar2(72) := g_package||'get_gl_period';
1962 --
1963 BEGIN
1964 --
1965 hr_utility.set_location('Entering:'||l_proc, 5);
1966 --
1967 -- get the budget start date
1968 --
1969 OPEN csr_time_period;
1970 FETCH csr_time_period INTO l_start_date;
1971 CLOSE csr_time_period;
1972 --
1973 hr_utility.set_location('Budget Start Date : '||l_start_date,10);
1974 --
1975 -- get the period name and accounting date
1976 --
1977 OPEN csr_period_name( p_start_date => l_start_date);
1978 FETCH csr_period_name INTO l_gl_period_statuses_rec;
1979 CLOSE csr_period_name;
1980 --
1981 p_gl_period_statuses_rec := l_gl_period_statuses_rec;
1982 --
1983 hr_utility.set_location('Period Name : '||l_gl_period_statuses_rec.period_name,20);
1984 --
1985 hr_utility.set_location('Leaving:'||l_proc, 1000);
1986 --
1987 EXCEPTION
1988 WHEN OTHERS THEN
1989 p_gl_period_statuses_rec := l_gl_period_statuses_rec;
1990 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1991 hr_utility.set_message_token('ROUTINE', l_proc);
1992 hr_utility.set_message_token('REASON', SQLERRM);
1993 hr_utility.raise_error;
1994 END get_gl_period;
1995 --
1996 -----------------------------------------------------------------------------------------
1997 --
1998 PROCEDURE update_period_commitment_tab
1999 (
2000 p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE,
2001 p_post_to_period_name IN gl_period_statuses.period_name%TYPE
2002 )
2003 IS
2004 --
2005 -- The foll procedure reads the global table g_period_amt_tab and
2006 -- fetches the period_name and code_combination_id corresponding to the
2007 -- period_id and cost_allocation_keyflex_id.If it does not find a period_name
2008 -- or a code_combination_id then it will populate the global variable
2009 -- g_detail_error to Y and we will not populate the pqh_gl_interface
2010 -- table for the current budget_detail_id.
2011 --
2012 l_gl_period_statuses_rec gl_period_statuses%ROWTYPE;
2013 l_period_name gl_period_statuses.period_name%TYPE;
2014 l_accounting_date gl_period_statuses.start_date%TYPE;
2015 --
2016 l_code_combination_id gl_code_combinations.code_combination_id%TYPE;
2017 --
2018 l_message_text pqh_process_log.message_text%TYPE;
2019 l_log_context pqh_process_log.log_context%TYPE;
2020 --
2021 l_proc varchar2(72) := 'update_period_commitment_tab';
2022 --
2023 BEGIN
2024
2025 hr_utility.set_location('Entering: '||l_proc, 5);
2026
2027 -- initialize g_detail_error
2028 g_detail_error := 'N';
2029
2030 IF NVL(g_period_amt_tab.COUNT,0) <> 0 THEN
2031
2032 FOR i IN NVL(g_period_amt_tab.FIRST,0)..NVL(g_period_amt_tab.LAST,-1) LOOP
2033 --
2034 -- Check if record is GL Record or Grant Record
2035 --
2036 IF g_period_amt_tab(i).cost_allocation_keyflex_id is NOT NULL THEN
2037 --
2038 -- For the period , check if there is a corresponding gl period.
2039 --
2040 IF p_post_to_period_name is not null then
2041 hr_utility.set_location('Entering: '||l_proc, 10000000);
2042 hr_utility.set_location('p_post_to_period_name: '||p_post_to_period_name, 10000000);
2043 hr_utility.set_location('g_application_id: '||g_application_id, 10000000);
2044 hr_utility.set_location('g_set_of_books_id: '||g_set_of_books_id, 10000000);
2045 hr_utility.set_location('g_budget_id: '||g_budget_id, 10000000);
2046 l_period_name := p_post_to_period_name;
2047 SELECT gl.start_date into l_accounting_date
2048 FROM gl_period_statuses gl, pqh_budgets bdgt
2049 WHERE gl.application_id = g_application_id
2050 AND gl.closing_status = 'O'
2051 AND gl.set_of_books_id = g_set_of_books_id
2052 AND gl.period_name = p_post_to_period_name
2053 AND bdgt.budget_id = g_budget_id
2054 AND gl.start_date <= bdgt.budget_end_date
2055 AND gl.end_date >= bdgt.budget_start_date ;
2056 ELSE
2057 get_gl_period(p_budget_period_id => g_period_amt_tab(i).period_id,
2058 p_set_of_books_id => g_set_of_books_id,
2059 p_gl_period_statuses_rec => l_gl_period_statuses_rec );
2060 l_period_name := l_gl_period_statuses_rec.period_name;
2061 l_accounting_date := l_gl_period_statuses_rec.start_date;
2062 END IF;
2063
2064 --
2065 IF l_period_name IS NULL THEN
2066 --
2067 -- no period name found mark detail as error and proceed
2068 --
2069 g_detail_error := 'Y';
2070 hr_utility.set_location('#######No Period#####',101);
2071 --
2072 -- get log_context
2073 --
2074 pqh_gl_posting.set_bpr_log_context
2075 (
2076 p_budget_period_id => g_period_amt_tab(i).period_id,
2077 p_log_context => l_log_context
2078 );
2079 --
2080 -- set the context
2081 --
2082 pqh_process_batch_log.set_context_level
2083 (
2084 p_txn_id => g_period_amt_tab(i).period_id,
2085 p_txn_table_route_id => g_table_route_id_bpr,
2086 p_level => 2,
2087 p_log_context => l_log_context
2088 );
2089 --
2090 -- Get the error message.
2091 --
2092 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_GL_BUDGET_PERIOD');
2093 l_message_text := FND_MESSAGE.GET;
2094 --
2095 -- insert error into process log
2096 --
2097 pqh_process_batch_log.insert_log
2098 (
2099 p_message_type_cd => 'ERROR',
2100 p_message_text => l_message_text
2101 );
2102 --
2103 ELSE
2104 --
2105 -- update the pl sql table with period name and accounting date
2106 --
2107 g_period_amt_tab(i).period_name := l_period_name;
2108 g_period_amt_tab(i).accounting_date := l_accounting_date;
2109 --
2110 END IF;
2111 --
2112 -- gl account ,
2113 -- Note : Change gl_posting to take the extra parameter.
2114 --
2115 pqh_gl_posting.get_ccid_for_commitment
2116 (
2117 p_budget_id => g_budget_id,
2118 p_chart_of_accounts_id => g_chart_of_accounts_id,
2119 p_budget_detail_id => p_budget_detail_id,
2120 p_budget_period_id => g_period_amt_tab(i).period_id,
2121 p_cost_allocation_keyflex_id => g_period_amt_tab(i).cost_allocation_keyflex_id,
2122 p_code_combination_id => l_code_combination_id
2123 );
2124
2125 IF l_code_combination_id IS NULL THEN
2126 --
2127 -- no gl account found, mark as error
2128 --
2129 hr_utility.set_location('#######No l_code_combination_id#####',101);
2130 g_detail_error := 'Y';
2131 --
2132 -- get log_context
2133 --
2134 pqh_gl_posting.set_bfs_log_context
2135 (
2136 p_cost_allocation_keyflex_id => g_period_amt_tab(i).cost_allocation_keyflex_id,
2137 p_log_context => l_log_context
2138 );
2139 --
2140 -- set the context
2141 --
2142 pqh_process_batch_log.set_context_level
2143 (
2144 p_txn_id => g_period_amt_tab(i).cost_allocation_keyflex_id,
2145 p_txn_table_route_id => g_table_route_id_bfs,
2146 p_level => 2,
2147 p_log_context => l_log_context
2148 );
2149 --
2150 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_GL_BUDGET_ACCOUNT');
2151 l_message_text := FND_MESSAGE.GET;
2152 --
2153 -- insert error
2154 --
2155 pqh_process_batch_log.insert_log
2156 (
2157 p_message_type_cd => 'ERROR',
2158 p_message_text => l_message_text
2159 );
2160 --
2161 --
2162 ELSE
2163 --
2164 -- update the pl sql table with gl account
2165 --
2166 g_period_amt_tab(i).code_combination_id := l_code_combination_id;
2167 --
2168 --
2169 END IF;
2170 --
2171 ELSE
2172 --
2173 -- This is a GMS record
2174 --
2175 g_period_amt_tab(i).period_name := to_char(g_period_amt_tab(i).period_id);
2176 END IF;
2177 --
2178 --
2179 END LOOP; -- end of all periods,cost_flexfield under the budget detail
2180 --
2181 END IF;
2182 --
2183 hr_utility.set_location('Leaving:'||l_proc, 1000);
2184 --
2185 EXCEPTION
2186 WHEN OTHERS THEN
2187 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2188 hr_utility.set_message_token('ROUTINE', l_proc);
2189 hr_utility.set_message_token('REASON', SQLERRM);
2190 hr_utility.raise_error;
2191 END update_period_commitment_tab;
2192 --
2193 --
2194 /************************************************************************************************************
2195 This procedure will check if the current budget_detail_id was already posted (exists in pqh_gl_interface)
2196 If Yes, it would build a pl/sql table with all records which have this current budget_detail_id.
2197 This is done as the user might have changed the records with current budget_detail_id which were previously
2198 posted and not present in new records. For those records we need to unpost i.e reverse the transactions.
2199
2200 Consider the following example :
2201
2202 <----------- Old ------------------------> <------------- New ------>
2203 Budget_detail_id Period CCID Cur Amt Period CCID Cur Amt
2204 1 1 1 US 100 (reverse) 1 1 UK 100 ( new )
2205 2 2 US 100 (reverse) 6 2 US 100 ( new )
2206 3 3 US 100 (update) 3 3 US 200 ( update )
2207 4 4 US 100 (unchanged) 4 4 US 100 ( unchanged )
2208 4 7 UK 100 ( new )
2209 7 9 US 100 ( new )
2210
2211
2212 ***************************************************************************************************************/
2213 --
2214 PROCEDURE build_old_bdgt_dtls_tab
2215 (
2216 p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE,
2217 p_posting_type_cd IN varchar2
2218 ) IS
2219 --
2220 -- local variables
2221 --
2222 l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
2223 i BINARY_INTEGER :=1;
2224 --
2225 --
2226 CURSOR csr_old_bdgt_dtls_rec IS
2227 SELECT *
2228 FROM pqh_gl_interface
2229 WHERE budget_version_id = g_budget_version_id
2230 AND budget_detail_id = p_budget_detail_id
2231 AND posting_type_cd = p_posting_type_cd
2232 AND NVL(adjustment_flag,'N') = 'N'
2233 AND status IS NOT NULL
2234 AND posting_date IS NOT NULL
2235 AND (NVL(amount_dr,0) > 0 OR NVL(amount_cr,0) > 0 ) ;
2236 --
2237 l_proc varchar2(72) := g_package||'build_old_bdgt_dtls_tab';
2238 --
2239 BEGIN
2240 --
2241 hr_utility.set_location('Entering:'||l_proc, 5);
2242 --
2243 -- Print Passed values
2244 --
2245 hr_utility.set_location('Budget Version:'||to_char(g_budget_version_id), 6);
2246 hr_utility.set_location('Budget Detail:'||to_char(p_budget_detail_id), 7);
2247 hr_utility.set_location('Posting Type:'||p_posting_type_cd, 8);
2248 --
2249 OPEN csr_old_bdgt_dtls_rec;
2250 --
2251 LOOP
2252 --
2253 FETCH csr_old_bdgt_dtls_rec INTO l_pqh_gl_interface_rec;
2254 EXIT WHEN csr_old_bdgt_dtls_rec%NOTFOUND;
2255 --
2256 g_old_bdgt_dtls_tab(i).budget_version_id := g_budget_version_id;
2257 g_old_bdgt_dtls_tab(i).budget_detail_id := p_budget_detail_id;
2258 g_old_bdgt_dtls_tab(i).period_name := l_pqh_gl_interface_rec.period_name;
2259 g_old_bdgt_dtls_tab(i).accounting_date := l_pqh_gl_interface_rec.accounting_date;
2260 g_old_bdgt_dtls_tab(i).cost_allocation_keyflex_id := l_pqh_gl_interface_rec.cost_allocation_keyflex_id;
2261 g_old_bdgt_dtls_tab(i).code_combination_id := l_pqh_gl_interface_rec.code_combination_id;
2262 g_old_bdgt_dtls_tab(i).project_id := l_pqh_gl_interface_rec.project_id;
2263 g_old_bdgt_dtls_tab(i).task_id := l_pqh_gl_interface_rec.task_id;
2264 g_old_bdgt_dtls_tab(i).award_id := l_pqh_gl_interface_rec.award_id ;
2265 g_old_bdgt_dtls_tab(i).expenditure_type := l_pqh_gl_interface_rec.expenditure_type;
2266 g_old_bdgt_dtls_tab(i).organization_id := l_pqh_gl_interface_rec.organization_id;
2267 g_old_bdgt_dtls_tab(i).currency_code := l_pqh_gl_interface_rec.currency_code;
2268 g_old_bdgt_dtls_tab(i).amount_dr := l_pqh_gl_interface_rec.amount_dr;
2269 g_old_bdgt_dtls_tab(i).amount_cr := l_pqh_gl_interface_rec.amount_cr;
2270 g_old_bdgt_dtls_tab(i).reverse_flag := 'Y';
2271 --
2272 i := i + 1;
2273 --
2274 END LOOP;
2275 --
2276 CLOSE csr_old_bdgt_dtls_rec;
2277 --
2278 hr_utility.set_location('No of old records :'||NVL(g_old_bdgt_dtls_tab.COUNT,0), 9);
2279 --
2280 hr_utility.set_location('Leaving:'||l_proc, 10);
2281 --
2282 EXCEPTION
2283 WHEN OTHERS THEN
2284 --
2285 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2286 hr_utility.set_message_token('ROUTINE', l_proc);
2287 hr_utility.set_message_token('REASON', SQLERRM);
2288 hr_utility.raise_error;
2289 --
2290 END build_old_bdgt_dtls_tab;
2291 --
2292 ----------------------------------------------------------------------------------
2293 --
2294 -- This procedure will compare the g_old_bdgt_dtls_tab with g_period_amt_tab .
2295 -- It will check if there are records in g_old_bdgt_dtls_tab which are not in
2296 -- g_period_amt_tab and update the reverse flag for those records to 'Y' so
2297 -- that we can reverse those records
2298 --
2299 PROCEDURE compare_old_bdgt_dtls_tab IS
2300 --
2301 -- local variables
2302 --
2303 l_proc varchar2(72) := g_package||'compare_old_bdgt_dtls_tab';
2304 --
2305 BEGIN
2306 --
2307 hr_utility.set_location('Entering:'||l_proc, 5);
2308 --
2309 -- for each record in g_old_bdgt_dtls_tab,loop thru the g_period_amt_tab
2310 -- to check if the record exists in g_period_amt_tab,if yes then set
2311 -- reverse_flag is N,else update the reverse_flag in g_old_bdgt_dtls_tab
2312 -- to 'Y'
2313 --
2314 IF NVL(g_old_bdgt_dtls_tab.COUNT,0) <> 0 AND
2315 NVL(g_period_amt_tab.COUNT,0) <> 0 AND
2316 g_detail_error = 'N' THEN
2317 --
2318 -- for each record in old
2319 --
2320 FOR i IN NVL(g_old_bdgt_dtls_tab.FIRST,0)..NVL(g_old_bdgt_dtls_tab.LAST,-1)
2321 --
2322 LOOP
2323 --
2324 -- loop thru the new g_period_amt_tab to check if the record exists
2325 --
2326 FOR j IN NVL(g_period_amt_tab.FIRST,0)..NVL(g_period_amt_tab.LAST,-1)
2327 LOOP
2328 --
2329 IF (g_old_bdgt_dtls_tab(i).cost_allocation_keyflex_id is not null and g_period_amt_tab(j).cost_allocation_keyflex_id is not null )
2330 THEN
2331 --
2332 IF g_old_bdgt_dtls_tab(i).period_name = g_period_amt_tab(j).period_name AND
2333 g_old_bdgt_dtls_tab(i).code_combination_id = g_period_amt_tab(j).code_combination_id AND
2334 g_old_bdgt_dtls_tab(i).currency_code IN(g_currency_code1,g_currency_code2,g_currency_code3)THEN
2335 --
2336 -- record found, go to next record
2337 --
2338 hr_utility.set_location('Do NOT Reverse old Record',7);
2339 --
2340 g_old_bdgt_dtls_tab(i).reverse_flag := 'N';
2341 --
2342 exit ; -- inner loop
2343 --
2344 END IF;
2345 ELSIF (g_old_bdgt_dtls_tab(i).cost_allocation_keyflex_id is null and g_period_amt_tab(j).cost_allocation_keyflex_id is null )
2346 THEN
2347 --
2348 IF g_old_bdgt_dtls_tab(i).period_name = g_period_amt_tab(j).period_name AND
2349 g_old_bdgt_dtls_tab(i).project_id = g_period_amt_tab(j).project_id AND
2350 g_old_bdgt_dtls_tab(i).task_id = g_period_amt_tab(j).task_id AND
2351 g_old_bdgt_dtls_tab(i).award_id = g_period_amt_tab(j).award_id AND
2352 g_old_bdgt_dtls_tab(i).expenditure_type = g_period_amt_tab(j).expenditure_type AND
2353 g_old_bdgt_dtls_tab(i).organization_id = g_period_amt_tab(j).organization_id
2354 THEN
2355 --
2356 -- record found, go to next record
2357 --
2358 hr_utility.set_location('Do NOT Reverse old Record',7);
2359 --
2360 g_old_bdgt_dtls_tab(i).reverse_flag := 'N';
2361 --
2362 exit ; -- inner loop
2363 --
2364 END IF;
2365 --
2366 END IF;
2367 --
2368 END LOOP; -- for the g_period_amt_tab table
2369 --
2370 END LOOP; -- for the old g_old_bdgt_dtls_tab table
2371 --
2372 END IF; -- if both old and new tables have records and there was no error in new table
2373 --
2374 hr_utility.set_location('Leaving:'||l_proc, 10);
2375 --
2376 EXCEPTION
2377 WHEN OTHERS THEN
2378 --
2379 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2380 hr_utility.set_message_token('ROUTINE', l_proc);
2381 hr_utility.set_message_token('REASON', SQLERRM);
2382 hr_utility.raise_error;
2383 --
2384 END compare_old_bdgt_dtls_tab;
2385 --
2386 ---------------------------------------------------------------------------------
2387 --
2388 -- This procedure will loop thru the g_old_bdgt_dtls_tab and generate reverse
2389 -- transaction for all records where reverse_flag is Y and update the posted
2390 -- record amount to 0
2391 --
2392 PROCEDURE reverse_old_bdgt_dtls_tab
2393 ( p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE,
2394 p_posting_type_cd IN varchar2) IS
2395 --
2396 -- local variables
2397 --
2398 l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
2399 --
2400 CURSOR csr_pqh_gl_interface(p_period_name IN varchar2,
2401 p_code_combination_id IN number,
2402 p_currency_code IN varchar2) IS
2403 SELECT *
2404 FROM pqh_gl_interface
2405 WHERE budget_version_id = g_budget_version_id
2406 AND budget_detail_id = p_budget_detail_id
2407 AND posting_type_cd = p_posting_type_cd
2408 AND period_name = p_period_name
2409 AND code_combination_id = p_code_combination_id
2410 AND currency_code = p_currency_code
2411 AND NVL(adjustment_flag,'N') = 'N'
2412 AND status IS NOT NULL
2413 AND posting_date IS NOT NULL
2414 AND cost_allocation_keyflex_id is not null
2415 FOR UPDATE of amount_dr;
2416
2417
2418 CURSOR csr_pqh_gms_interface(p_period_name IN varchar2,
2419 p_project_id IN number,
2420 p_task_id IN number,
2421 p_award_id IN number,
2422 p_expenditure_type IN varchar2,
2423 p_organization_id IN number,
2424 p_currency_code IN varchar2) IS
2425 SELECT *
2426 FROM pqh_gl_interface
2427 WHERE budget_version_id = g_budget_version_id
2428 AND budget_detail_id = p_budget_detail_id
2429 AND posting_type_cd = p_posting_type_cd
2430 AND period_name = p_period_name
2431 AND project_id = p_project_id
2432 AND task_id = p_task_id
2433 AND award_id = p_award_id
2434 AND expenditure_type = p_expenditure_type
2435 AND organization_id = p_organization_id
2436 AND currency_code = p_currency_code
2437 AND NVL(adjustment_flag,'N') = 'N'
2438 AND status IS NOT NULL
2439 AND posting_date IS NOT NULL
2440 AND cost_allocation_keyflex_id is null
2441 FOR UPDATE of amount_dr;
2442 --
2443 l_proc varchar2(72) := g_package||'reverse_old_bdgt_dtls_tab';
2444 --
2445 BEGIN
2446 --
2447 hr_utility.set_location('Entering:'||l_proc, 5);
2448 --
2449 hr_utility.set_location('Budget Detail Id : '||p_budget_detail_id,6);
2450 --
2451 IF NVL(g_old_bdgt_dtls_tab.COUNT,0) <> 0 AND
2452 NVL(g_period_amt_tab.COUNT,0) <> 0 AND
2453 --
2454 g_detail_error = 'N' THEN
2455 --
2456 -- for each record in old
2457 --
2458 FOR i IN NVL(g_old_bdgt_dtls_tab.FIRST,0)..NVL(g_old_bdgt_dtls_tab.LAST,-1)
2459 LOOP
2460 IF g_old_bdgt_dtls_tab(i).reverse_flag = 'Y' THEN
2461 hr_utility.set_location('Reversing .... ',8);
2462 hr_utility.set_location('Period Name is '||g_old_bdgt_dtls_tab(i).period_name,8);
2463 hr_utility.set_location('code_combination_id '||g_old_bdgt_dtls_tab(i).code_combination_id,8);
2464 hr_utility.set_location('currency_code '|| g_old_bdgt_dtls_tab(i).currency_code,8);
2465 --
2466 -- update the record and reverse the txn
2467 --
2468 IF g_old_bdgt_dtls_tab(i).cost_allocation_keyflex_id is NOT NULL
2469 THEN
2470 OPEN csr_pqh_gl_interface(p_period_name => g_old_bdgt_dtls_tab(i).period_name,
2471 p_code_combination_id => g_old_bdgt_dtls_tab(i).code_combination_id,
2472 p_currency_code => g_old_bdgt_dtls_tab(i).currency_code) ;
2473 FETCH csr_pqh_gl_interface INTO l_pqh_gl_interface_rec;
2474
2475 hr_utility.set_location('Fetched record ',10);
2476
2477 --
2478 -- Reverse the old record.
2479 --
2480 UPDATE pqh_gl_interface
2481 SET amount_dr = 0
2482 WHERE CURRENT OF csr_pqh_gl_interface;
2483
2484 CLOSE csr_pqh_gl_interface;
2485 ELSE
2486 OPEN csr_pqh_gms_interface(p_period_name => g_old_bdgt_dtls_tab(i).period_name,
2487 p_project_id => g_old_bdgt_dtls_tab(i).project_id,
2488 p_task_id => g_old_bdgt_dtls_tab(i).task_id,
2489 p_award_id => g_old_bdgt_dtls_tab(i).award_id,
2490 p_expenditure_type => g_old_bdgt_dtls_tab(i).expenditure_type,
2491 p_organization_id => g_old_bdgt_dtls_tab(i).organization_id,
2492 p_currency_code => g_old_bdgt_dtls_tab(i).currency_code) ;
2493 FETCH csr_pqh_gms_interface INTO l_pqh_gl_interface_rec;
2494
2495 hr_utility.set_location('Fetched record ',10);
2496
2497 --
2498 -- Reverse the old record.
2499 --
2500 UPDATE pqh_gl_interface
2501 SET amount_dr = 0
2502 WHERE CURRENT OF csr_pqh_gms_interface;
2503
2504 CLOSE csr_pqh_gms_interface;
2505 END IF;
2506 --
2507 -- create a reverse transaction for this amount_dr
2508 --
2509 INSERT INTO pqh_gl_interface
2510 (
2511 gl_interface_id,
2512 budget_version_id,
2513 budget_detail_id,
2514 period_name,
2515 accounting_date,
2516 code_combination_id,
2517 cost_allocation_keyflex_id,
2518 project_id,
2519 task_id,
2520 award_id,
2521 expenditure_type,
2522 organization_id,
2523 amount_dr,
2524 amount_cr,
2525 currency_code,
2526 status,
2527 adjustment_flag,
2528 posting_type_cd,
2529 posting_date
2530 )
2531 VALUES
2532 (
2533 pqh_gl_interface_s.nextval,
2534 g_budget_version_id,
2535 p_budget_detail_id,
2536 g_old_bdgt_dtls_tab(i).period_name,
2537 g_old_bdgt_dtls_tab(i).accounting_date,
2538 g_old_bdgt_dtls_tab(i).code_combination_id,
2539 g_old_bdgt_dtls_tab(i).cost_allocation_keyflex_id,
2540 g_old_bdgt_dtls_tab(i).project_id,
2541 g_old_bdgt_dtls_tab(i).task_id,
2542 g_old_bdgt_dtls_tab(i).award_id,
2543 g_old_bdgt_dtls_tab(i).expenditure_type,
2544 g_old_bdgt_dtls_tab(i).organization_id,
2545 0,
2546 NVL(l_pqh_gl_interface_rec.amount_dr,0),
2547 g_old_bdgt_dtls_tab(i).currency_code,
2548 null,
2549 'Y',
2550 'COMMITMENT',
2551 null
2552 );
2553 --
2554
2555 hr_utility.set_location('Created a reverse txn ',20);
2556
2557
2558 END IF; -- if the transaction reverse_flag is Y
2559 --
2560 END LOOP;
2561 --
2562 END IF; -- if both old and new tables have records and there was no error in new table
2563 --
2564 hr_utility.set_location('Leaving:'||l_proc, 1000);
2565 --
2566 EXCEPTION
2567 WHEN OTHERS THEN
2568 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2569 hr_utility.set_message_token('ROUTINE', l_proc);
2570 hr_utility.set_message_token('REASON', SQLERRM);
2571 hr_utility.raise_error;
2572 END reverse_old_bdgt_dtls_tab;
2573 --
2574 ---------------------------------------------------------------------------------------
2575 --
2576 PROCEDURE reverse_budget_details
2577 (
2578 p_period_name IN pqh_gl_interface.period_name%TYPE,
2579 p_currency_code IN pqh_gl_interface.currency_code%TYPE,
2580 p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE,
2581 p_posting_type_cd IN varchar2
2582 ) IS
2583 --
2584 -- This procedure will be called if the GL fund checker failed.
2585 -- This procedure will does the following : 1. update all the budget_detail
2586 -- records which have this Period Name + CCID + currency code to ERROR ( gl_status)
2587 -- 2. Reverse unposted adjustment txns in pqh_gl_interface
2588 -- 3. Delete all unposted non-adjustment txns from pqh_gl_interface
2589 -- Note : If a budget detail record has 4 periods and there was a error in 4th period ,
2590 -- we have no control on the 1st three as they have already been Approved by funds
2591 -- checker program and would have already been posted to GL.
2592 --
2593 -- local variables
2594 --
2595 l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
2596 --
2597 CURSOR csr_adj IS
2598 SELECT *
2599 FROM pqh_gl_interface
2600 WHERE budget_version_id = g_budget_version_id
2601 AND period_name = p_period_name
2602 AND currency_code = p_currency_code
2603 AND code_combination_id = p_code_combination_id
2604 AND posting_type_cd = p_posting_type_cd
2605 AND NVL(adjustment_flag,'N') = 'Y'
2606 AND status IS NULL
2607 AND posting_date IS NULL;
2608 --
2609 l_proc varchar2(72) := g_package||'reverse_budget_details';
2610 --
2611 BEGIN
2612 --
2613 hr_utility.set_location('Entering:'||l_proc, 5);
2614 --
2615 -- reverse the adjustment transactions
2616 --
2617 OPEN csr_adj;
2618 LOOP
2619 FETCH csr_adj INTO l_pqh_gl_interface_rec;
2620 EXIT WHEN csr_adj%NOTFOUND;
2621 --
2622 -- update the amount_dr for the original record
2623 --
2624 UPDATE pqh_gl_interface
2625 SET amount_dr = NVL(amount_dr,0) -
2626 NVL(l_pqh_gl_interface_rec.amount_dr,0) +
2627 NVL(l_pqh_gl_interface_rec.amount_cr,0)
2628 WHERE budget_version_id = l_pqh_gl_interface_rec.budget_version_id
2629 AND budget_detail_id = l_pqh_gl_interface_rec.budget_detail_id
2630 AND period_name = l_pqh_gl_interface_rec.period_name
2631 AND currency_code = l_pqh_gl_interface_rec.currency_code
2632 AND code_combination_id = l_pqh_gl_interface_rec.code_combination_id
2633 AND posting_type_cd = p_posting_type_cd
2634 AND NVL(adjustment_flag,'N') = 'N'
2635 AND status IS NOT NULL;
2636
2637 END LOOP;
2638 CLOSE csr_adj;
2639 --
2640 -- update the pqh_budget_details table gl_status to ERROR
2641 --
2642 UPDATE pqh_budget_details
2643 SET commitment_gl_status = 'ERROR'
2644 WHERE budget_version_id = g_budget_version_id
2645 AND budget_detail_id IN
2646 ( SELECT distinct budget_detail_id
2647 FROM pqh_gl_interface
2648 WHERE budget_version_id = g_budget_version_id
2649 AND period_name = p_period_name
2650 AND currency_code = p_currency_code
2651 AND code_combination_id = p_code_combination_id
2652 AND posting_type_cd = p_posting_type_cd
2653 AND status IS NULL
2654 AND posting_date IS NULL
2655 );
2656 --
2657 -- delete the unposted transactions from pqh_gl_interface
2658 --
2659 DELETE FROM pqh_gl_interface
2660 WHERE budget_version_id = g_budget_version_id
2661 AND period_name = p_period_name
2662 AND currency_code = p_currency_code
2663 AND code_combination_id = p_code_combination_id
2664 AND posting_type_cd = p_posting_type_cd
2665 AND status IS NULL
2666 AND posting_date IS NULL;
2667 --
2668 hr_utility.set_location('Leaving:'||l_proc, 1000);
2669 --
2670 EXCEPTION
2671 WHEN OTHERS THEN
2672 --
2673 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2674 hr_utility.set_message_token('ROUTINE', l_proc);
2675 hr_utility.set_message_token('REASON', SQLERRM);
2676 hr_utility.raise_error;
2677 --
2678 END reverse_budget_details;
2679 --
2680 -----------------------------------------------------------------------------------------
2681 --
2682 -- This procedure will pick records from pqh_gl_interface table and insert them into
2683 -- gl tables depending on the g_budgetary_control_flag If we insert into gl_bc_packets
2684 -- do funds checking for each packet
2685 --
2686 PROCEDURE populate_gl_tables
2687 IS
2688 --
2689 -- local variables
2690 --
2691 l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
2692 l_period_name pqh_gl_interface.period_name%TYPE;
2693 l_accounting_date pqh_gl_interface.accounting_date%TYPE;
2694 l_code_combination_id pqh_gl_interface.code_combination_id%TYPE;
2695 l_cost_allocation_keyflex_id pqh_gl_interface.cost_allocation_keyflex_id%TYPE;
2696 l_currency_code pqh_gl_interface.currency_code%TYPE;
2697 l_amount_dr pqh_gl_interface.amount_dr%TYPE;
2698 l_amount_cr pqh_gl_interface.amount_cr%TYPE;
2699 l_packet_id gl_bc_packets.packet_id%TYPE;
2700 l_gl_period_statuses_rec gl_period_statuses%ROWTYPE;
2701 l_fc_success boolean;
2702 l_fc_return varchar2(100);
2703 l_fc_mode varchar2(100);
2704 l_fc_message varchar2(8000);
2705 l_log_context varchar2(255);
2706 l_packet_result_code varchar2(255);
2707 l_packet_status_code varchar2(255);
2708 --
2709 CURSOR csr_pqh_gl_interface IS
2710 SELECT period_name, accounting_date,
2711 code_combination_id, cost_allocation_keyflex_id, currency_code,
2712 SUM(NVL(amount_dr,0)) amount_dr,
2713 SUM(NVL(amount_cr,0)) amount_cr
2714 FROM pqh_gl_interface
2715 WHERE budget_version_id IN (g_budget_version_id, NVL(g_last_posted_ver,0) )
2716 AND status IS NULL
2717 AND posting_date IS NULL
2718 AND posting_type_cd = 'COMMITMENT'
2719 AND cost_allocation_keyflex_id is NOT NULL
2720 GROUP BY period_name, accounting_date,code_combination_id,
2721 cost_allocation_keyflex_id,currency_code;
2722 --
2723 CURSOR csr_packet_id IS
2724 SELECT gl_bc_packets_s.nextval
2725 FROM dual;
2726 --
2727 CURSOR csr_period_name( p_period_name IN varchar2 ) IS
2728 SELECT *
2729 FROM gl_period_statuses
2730 WHERE application_id = g_application_id
2731 AND set_of_books_id = g_set_of_books_id
2732 AND period_name = p_period_name;
2733 --
2734 CURSOR csr_gl_lookups(p_lookup_code IN varchar2 ) IS
2735 SELECT description
2736 FROM gl_lookups
2737 WHERE lookup_type = 'FUNDS_CHECK_RESULT_CODE'
2738 AND lookup_code = p_lookup_code
2739 AND NVL(enabled_flag,'N') = 'Y';
2740 --
2741 CURSOR csr_gl_packet_code(p_packet_id IN number ) IS
2742 SELECT result_code
2743 FROM gl_bc_packets
2744 WHERE packet_id = p_packet_id;
2745 --
2746 CURSOR csr_gl_status(p_lookup_code IN varchar2 ) IS
2747 SELECT description
2748 FROM gl_lookups
2749 WHERE lookup_type = 'FUNDS_CHECK_STATUS_CODE'
2750 AND lookup_code = p_lookup_code
2751 AND NVL(enabled_flag,'N') = 'Y';
2752 --
2753 l_proc varchar2(72) := g_package||'populate_gl_tables';
2754 --
2755 BEGIN
2756 --
2757 hr_utility.set_location('Entering: '||l_proc, 5);
2758 --
2759 IF g_budgetary_control_flag = 'Y' THEN
2760 --
2761 -- insert into gl_bc_packets and do funds checking for each packet
2762 --
2763 hr_utility.set_location('Inserting into GL_BC_PACKETS',10);
2764 --
2765 OPEN csr_pqh_gl_interface;
2766 LOOP
2767 --
2768 FETCH csr_pqh_gl_interface INTO l_period_name, l_accounting_date,
2769 l_code_combination_id, l_cost_allocation_keyflex_id, l_currency_code,
2770 l_amount_dr, l_amount_cr;
2771 EXIT WHEN csr_pqh_gl_interface%NOTFOUND;
2772 --
2773 -- Get Packet ID
2774 --
2775 OPEN csr_packet_id;
2776 FETCH csr_packet_id INTO l_packet_id;
2777 CLOSE csr_packet_id;
2778 --
2779 -- get period details
2780 --
2781 OPEN csr_period_name(p_period_name => l_period_name);
2782 FETCH csr_period_name INTO l_gl_period_statuses_rec;
2783 CLOSE csr_period_name;
2784 --
2785 -- compute the GL funds checker Mode
2786 --
2787 IF g_validate THEN
2788 --
2789 -- this is validate ONLY mode
2790 --
2791 l_fc_mode := 'C';
2792 --
2793 ELSIF NVL(l_amount_dr,0) > 0 THEN
2794 --
2795 -- this is debit so run fund checker in reserved mode
2796 --
2797 l_fc_mode := 'R';
2798 --
2799 ELSE
2800 --
2801 -- this is credit so run fund checker in unreserved mode
2802 --
2803 l_fc_mode := 'U';
2804 --
2805 END IF;
2806 --
2807 --
2808 -- Call the GL funds checker. The GL funds checker program has COMMIT inside
2809 -- the program.so we cannot rollback. The GL funds checker is only called
2810 -- when the validate flag is false i.e no validation
2811 -- do funds checking for each packet
2812 -- Mode = R (reserved) if amount is dr
2813 -- Mode = U (unreserved) if amount is cr
2814 -- Mode = C (Checking) if program is run in validate mode i.e g_validate = TRUE
2815 -- Mode C is never called as there as explicit commits in GL funds checker program , so
2816 -- we call the GL funds checker program only when g_validate is FALSE in R or U mode
2817
2818 -- Insert in gl_bc_packets and run funds checker
2819 hr_utility.set_location('Calling ins_gl_bc_run_fund_check with fund checker Mode : '||l_fc_mode,100);
2820
2821 ins_gl_bc_run_fund_check
2822 ( p_packet_id => l_packet_id
2823 ,p_code_combination_id => l_code_combination_id
2824 ,p_period_name => l_period_name
2825 ,p_period_year => l_gl_period_statuses_rec.period_year
2826 ,p_period_num => l_gl_period_statuses_rec.period_num
2827 ,p_quarter_num => l_gl_period_statuses_rec.quarter_num
2828 ,p_currency_code => l_currency_code
2829 ,p_entered_dr => NVL(l_amount_dr,0)
2830 ,p_entered_cr => NVL(l_amount_cr,0)
2831 ,p_accounted_dr => NVL(l_amount_dr,0)
2832 ,p_accounted_cr => NVL(l_amount_cr,0)
2833 ,p_cost_allocation_keyflex_id => l_cost_allocation_keyflex_id
2834 ,p_fc_mode => l_fc_mode
2835 ,p_fc_success => l_fc_success
2836 ,p_fc_return => l_fc_return
2837 );
2838
2839 hr_utility.set_location('GL Fund Checker return Code : '||l_fc_return,110);
2840 --
2841 -- get the return code desc from GL lookups
2842 --
2843 OPEN csr_gl_status(p_lookup_code => l_fc_return);
2844 FETCH csr_gl_status INTO l_packet_status_code;
2845 CLOSE csr_gl_status;
2846 --
2847 hr_utility.set_location('GL Fund Checker return Code Desc : '||l_packet_status_code,111);
2848 --
2849 -- If the fund checker program failed i.e l_fc_success = FALSE or
2850 -- l_fc_return in ('T', 'F','R') then we would do the following :
2851 -- 1. Put the error message in pqh_process_log ( context : Period Name + CCID + currency code )
2852 -- 2.update gl_status of budget_detail records which have this Period Name+CCID+currency code to ERROR
2853 -- 3. Reverse unposted adjustment txns in pqh_gl_interface
2854 -- 4. Delete all unposted non-adjustment txns from pqh_gl_interface
2855 --
2856 IF NOT ( l_fc_success ) OR ( NVL(l_fc_return,'T') in ('T', 'F','R') ) THEN
2857 --
2858 -- fund checker failed
2859 --
2860 hr_utility.set_location('Fund Checker Failed ',120);
2861 --
2862 -- STEP 1: Log the Error Message
2863 -- get the error message which is populated in case of fatal error i.e l_fc_return = T
2864 --
2865 l_fc_message := fnd_message.get;
2866 --
2867 -- if the above error message is null then get from result code
2868 --
2869 IF l_fc_message IS NULL THEN
2870 OPEN csr_gl_packet_code(p_packet_id => l_packet_id);
2871 FETCH csr_gl_packet_code INTO l_packet_result_code;
2872 CLOSE csr_gl_packet_code;
2873
2874 OPEN csr_gl_lookups(p_lookup_code => l_packet_result_code);
2875 FETCH csr_gl_lookups INTO l_fc_message;
2876 CLOSE csr_gl_lookups;
2877 END IF;
2878 --
2879 hr_utility.set_location('Fund Chk Error : '||substr(l_fc_message,1,50),120);
2880 --
2881 -- set the log context and insert into log
2882 --
2883 l_log_context := l_period_name||' - '||l_code_combination_id||' - '||l_currency_code;
2884 --
2885 hr_utility.set_location('Log Context : '||l_log_context,130);
2886 --
2887 -- set the context
2888 --
2889 pqh_process_batch_log.set_context_level
2890 (
2891 p_txn_id => l_packet_id,
2892 p_txn_table_route_id => g_table_route_id_glf,
2893 p_level => 1,
2894 p_log_context => l_log_context
2895 );
2896 --
2897 -- insert error
2898 --
2899 pqh_process_batch_log.insert_log
2900 (
2901 p_message_type_cd => 'ERROR',
2902 p_message_text => l_packet_status_code||' : '||l_fc_message
2903 );
2904 --
2905 hr_utility.set_location('Inserted Error and calling reverse txn ',140);
2906 --
2907 -- Reverse budget details
2908 --
2909 reverse_budget_details
2910 (
2911 p_period_name => l_period_name ,
2912 p_currency_code => l_currency_code ,
2913 p_code_combination_id => l_code_combination_id ,
2914 p_posting_type_cd => 'COMMITMENT'
2915 );
2916 --
2917 END IF; -- Fund checker Error
2918 --
2919 END LOOP;
2920 --
2921 CLOSE csr_pqh_gl_interface;
2922 --
2923 ELSE
2924 --
2925 -- insert into gl_interface
2926 --
2927 hr_utility.set_location('Inserting into GL_INTERFACE',200);
2928 --
2929 OPEN csr_pqh_gl_interface;
2930 LOOP
2931 --
2932 FETCH csr_pqh_gl_interface INTO l_period_name, l_accounting_date,
2933 l_code_combination_id, l_cost_allocation_keyflex_id, l_currency_code,
2934 l_amount_dr, l_amount_cr;
2935 EXIT WHEN csr_pqh_gl_interface%NOTFOUND;
2936 --
2937 INSERT INTO gl_interface
2938 (status,
2939 set_of_books_id,
2940 user_je_source_name,
2941 user_je_category_name,
2942 currency_code,
2943 date_created,
2944 created_by,
2945 actual_flag,
2946 accounting_date,
2947 period_name,
2948 code_combination_id,
2949 chart_of_accounts_id,
2950 entered_dr,
2951 entered_cr,
2952 encumbrance_type_id,
2953 reference1,
2954 reference2)
2955 VALUES
2956 ('NEW',
2957 g_set_of_books_id,
2958 g_user_je_source_name,
2959 g_user_je_category_name,
2960 l_currency_code,
2961 sysdate,
2962 8302,
2963 'E',
2964 l_accounting_date,
2965 l_period_name,
2966 l_code_combination_id,
2967 g_chart_of_accounts_id,
2968 NVL(l_amount_dr,0),
2969 NVL(l_amount_cr,0),
2970 1000, -- encumbrance_type_id
2971 g_budget_version_id,
2972 l_cost_allocation_keyflex_id);
2973 --
2974 END LOOP;
2975 --
2976 CLOSE csr_pqh_gl_interface;
2977 --
2978 END IF;
2979 --
2980 hr_utility.set_location('Leaving:'||l_proc, 1000);
2981 --
2982 EXCEPTION
2983 WHEN OTHERS THEN
2984 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2985 hr_utility.set_message_token('ROUTINE', l_proc);
2986 hr_utility.set_message_token('REASON', SQLERRM);
2987 hr_utility.raise_error;
2988 END populate_gl_tables;
2989 --
2990 ----------------------------------------------------------------------------------------------
2991 PROCEDURE update_commitment_gl_status
2992 IS
2993 --
2994 -- This procedure will update the gl_status of pqh_budget_versions,
2995 -- pqh_budget_details and update the pqh_gl_interface table
2996 -- We always update the TRANSFERED_TO_GL_FLAG = Y to indicate this is the
2997 -- latest budget_version that is posted to GL
2998 -- gl_status = POST or ERROR
2999 --
3000 -- local variables
3001 --
3002 l_budget_details_rec pqh_budget_details%ROWTYPE;
3003 l_count NUMBER;
3004 --
3005 CURSOR csr_budget_details IS
3006 SELECT *
3007 FROM pqh_budget_details
3008 WHERE budget_version_id = g_budget_version_id
3009 AND NVL(commitment_gl_status,'X') <> 'ERROR'
3010 FOR UPDATE OF commitment_gl_status;
3011 --
3012 CURSOR csr_budget_details_cnt IS
3013 SELECT COUNT(*)
3014 FROM pqh_budget_details
3015 WHERE budget_version_id = g_budget_version_id
3016 AND NVL(commitment_gl_status,'ERROR') = 'ERROR';
3017 --
3018 l_proc varchar2(72) := g_package||'update_commitment_gl_status';
3019 --
3020 BEGIN
3021 --
3022 hr_utility.set_location('Entering: '||l_proc, 5);
3023 --
3024 -- update pqh_budget_details
3025 --
3026 OPEN csr_budget_details;
3027 LOOP
3028 FETCH csr_budget_details INTO l_budget_details_rec;
3029 EXIT WHEN csr_budget_details%NOTFOUND;
3030 --
3031 UPDATE pqh_budget_details
3032 SET commitment_gl_status = 'POST'
3033 WHERE CURRENT OF csr_budget_details;
3034 --
3035 END LOOP;
3036 CLOSE csr_budget_details;
3037 --
3038 -- update pqh_budget_versions and the program out variable
3039 --
3040 OPEN csr_budget_details_cnt;
3041 FETCH csr_budget_details_cnt INTO l_count;
3042 CLOSE csr_budget_details_cnt;
3043 --
3044 IF NVL(l_count,0) = 0 THEN
3045 --
3046 -- no errors
3047 --
3048 UPDATE pqh_budget_versions
3049 SET commitment_gl_status = 'POST'
3050 WHERE budget_version_id = g_budget_version_id;
3051 --
3052 -- set the OUT variable to SUCCESS
3053 --
3054 g_status := 'SUCCESS';
3055 --
3056 ELSE
3057 --
3058 -- there were errors in details
3059 --
3060 UPDATE pqh_budget_versions
3061 SET commitment_gl_status = 'ERROR'
3062 WHERE budget_version_id = g_budget_version_id;
3063 --
3064 -- set the OUT variable to ERROR
3065 --
3066 g_status := 'ERROR';
3067 --
3068 END IF;
3069 --
3070 hr_utility.set_location('Budget Details Error Count : '||l_count, 100);
3071 --
3072 -- update the pqh_gl_interface table
3073 --
3074 UPDATE pqh_gl_interface
3075 SET posting_date = sysdate,
3076 status = 'POST'
3077 WHERE budget_version_id = g_budget_version_id
3078 AND posting_type_cd = 'COMMITMENT'
3079 AND posting_date IS NULL
3080 AND status IS NULL;
3081 --
3082 -- update the pqh_gl_interface table for last posted version
3083 --
3084 UPDATE pqh_gl_interface
3085 SET posting_date = sysdate,
3086 status = 'POST'
3087 WHERE budget_version_id = NVL(g_last_posted_ver,0)
3088 AND posting_type_cd = 'COMMITMENT'
3089 AND posting_date IS NULL
3090 AND status IS NULL;
3091 --
3092 hr_utility.set_location('Leaving:'||l_proc, 1000);
3093 --
3094 EXCEPTION
3095 WHEN OTHERS THEN
3096 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3097 hr_utility.set_message_token('ROUTINE', l_proc);
3098 hr_utility.set_message_token('REASON', SQLERRM);
3099 hr_utility.raise_error;
3100 END update_commitment_gl_status;
3101 --
3102 --------------------------------------------------------------------------------------------------
3103 --
3104 PROCEDURE set_bdt_log_context
3105 (
3106 p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE,
3107 p_log_context OUT NOCOPY pqh_process_log.log_context%TYPE
3108 ) IS
3109 --
3110 -- This procedure will set the log_context at Budget detail level
3111 -- We are processing only positions . So we need to set store the
3112 -- position_name.
3113 --
3114 l_budget_details_rec pqh_budget_details%ROWTYPE;
3115 l_position_name hr_all_positions.name%TYPE;
3116 l_log_context pqh_process_log.log_context%TYPE;
3117 --
3118 CURSOR csr_bdt_detail_rec IS
3119 SELECT *
3120 FROM pqh_budget_details
3121 WHERE budget_detail_id = p_budget_detail_id ;
3122 --
3123 l_proc varchar2(72) := g_package||'set_bdt_log_context';
3124 --
3125 BEGIN
3126 --
3127 hr_utility.set_location('Entering:'||l_proc, 5);
3128 --
3129 OPEN csr_bdt_detail_rec;
3130 FETCH csr_bdt_detail_rec INTO l_budget_details_rec;
3131 CLOSE csr_bdt_detail_rec;
3132 --
3133 l_position_name := HR_GENERAL.DECODE_POSITION (p_position_id => l_budget_details_rec.position_id);
3134 --
3135 hr_utility.set_location('Position :'||l_position_name, 8);
3136 --
3137 p_log_context := SUBSTR(l_position_name,1,255);
3138 --
3139 hr_utility.set_location('Leaving:'||l_proc, 10);
3140 --
3141 EXCEPTION
3142 WHEN OTHERS THEN
3143 p_log_context := l_log_context;
3144 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3145 hr_utility.set_message_token('ROUTINE', l_proc);
3146 hr_utility.set_message_token('REASON', SQLERRM);
3147 -- end log and halt the program here
3148 raise g_error_exception;
3149 END set_bdt_log_context;
3150 --
3151 ------------------------------------------------------------------------------------
3152 PROCEDURE insert_pqh_gl_interface
3153 (
3154 p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
3155 p_period_name IN pqh_gl_interface.period_name%TYPE,
3156 p_accounting_date IN pqh_gl_interface.accounting_date%TYPE,
3157 p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE,
3158 p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE,
3159 p_amount IN pqh_gl_interface.amount_dr%TYPE,
3160 p_currency_code IN pqh_gl_interface.currency_code%TYPE,
3161 p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
3162 ) IS
3163 --
3164 -- This procedure will insert record into pqh_gl_interface
3165 -- If the same UOM is repeated more then once then we would update the unposted txn.
3166 --
3167 CURSOR csr_pqh_gl_interface IS
3168 SELECT COUNT(*)
3169 FROM pqh_gl_interface
3170 WHERE budget_version_id = g_budget_version_id
3171 AND budget_detail_id = p_budget_detail_id
3172 AND period_name = p_period_name
3173 AND code_combination_id = p_code_combination_id
3174 AND currency_code = p_currency_code
3175 AND posting_type_cd = p_posting_type_cd
3176 AND NVL(adjustment_flag,'N') = 'N'
3177 AND status IS NULL
3178 AND posting_date IS NULL
3179 AND cost_allocation_keyflex_id is not null;
3180 --
3181 -- local variables
3182 --
3183 l_proc varchar2(72) := g_package||'insert_pqh_gl_interface';
3184 l_count number(9) := 0 ;
3185 --
3186 BEGIN
3187
3188 hr_utility.set_location('Entering: '||l_proc, 5);
3189 --
3190 -- check if its a repeat of that same UOM
3191 --
3192 OPEN csr_pqh_gl_interface;
3193 FETCH csr_pqh_gl_interface INTO l_count;
3194 CLOSE csr_pqh_gl_interface;
3195 --
3196 hr_utility.set_location('l_count in Insert pqh_gl_interface : '||l_count,10);
3197 --
3198 IF l_count <> 0 THEN
3199 --
3200 -- this is a repeat of UOM , so update the first one adding the new amount
3201 --
3202 UPDATE pqh_gl_interface
3203 SET AMOUNT_DR = NVL(AMOUNT_DR,0) + NVL(p_amount,0)
3204 WHERE budget_version_id = g_budget_version_id
3205 AND budget_detail_id = p_budget_detail_id
3206 AND period_name = p_period_name
3207 AND code_combination_id = p_code_combination_id
3208 AND currency_code = p_currency_code
3209 AND posting_type_cd = p_posting_type_cd
3210 AND NVL(adjustment_flag,'N') = 'N'
3211 AND status IS NULL
3212 AND posting_date IS NULL;
3213
3214 ELSE
3215 --
3216 hr_utility.set_location('Currency code: '||p_currency_code, 5);
3217 -- insert this record
3218 --
3219 INSERT INTO pqh_gl_interface
3220 (
3221 gl_interface_id,
3222 budget_version_id,
3223 budget_detail_id,
3224 period_name,
3225 accounting_date,
3226 code_combination_id,
3227 cost_allocation_keyflex_id,
3228 amount_dr,
3229 amount_cr,
3230 currency_code,
3231 status,
3232 adjustment_flag,
3233 posting_date,
3234 posting_type_cd
3235 )
3236 VALUES
3237 (
3238 pqh_gl_interface_s.nextval,
3239 g_budget_version_id,
3240 p_budget_detail_id,
3241 p_period_name,
3242 p_accounting_date,
3243 p_code_combination_id,
3244 p_cost_allocation_keyflex_id,
3245 NVL(p_amount,0),
3246 0,
3247 p_currency_code,
3248 null,
3249 null,
3250 null,
3251 p_posting_type_cd
3252 );
3253 --
3254 END IF; -- l_count <> 0 UOM repeated
3255 --
3256 hr_utility.set_location('Leaving:'||l_proc, 1000);
3257 --
3258 EXCEPTION
3259 WHEN OTHERS THEN
3260 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3261 hr_utility.set_message_token('ROUTINE', l_proc);
3262 hr_utility.set_message_token('REASON', SQLERRM);
3263 hr_utility.raise_error;
3264 END insert_pqh_gl_interface;
3265 --
3266 -- ----------------------------------------------------------------------------
3267 PROCEDURE update_pqh_gl_interface
3268 (
3269 p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
3270 p_period_name IN pqh_gl_interface.period_name%TYPE,
3271 p_accounting_date IN pqh_gl_interface.accounting_date%TYPE,
3272 p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE,
3273 p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE,
3274 p_amount IN pqh_gl_interface.amount_dr%TYPE,
3275 p_currency_code IN pqh_gl_interface.currency_code%TYPE,
3276 p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
3277 ) IS
3278 --
3279 -- This procedure will update pqh_gl_interface and create a adjustment record
3280 --
3281 --
3282 -- local variables
3283 --
3284 l_proc varchar2(72) := g_package||'update_pqh_gl_interface';
3285 --
3286 l_amount_diff pqh_gl_interface.amount_dr%TYPE :=0;
3287 l_amount_dr pqh_gl_interface.amount_dr%TYPE :=0;
3288 l_amount_cr pqh_gl_interface.amount_cr%TYPE :=0;
3289 l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
3290 --
3291 CURSOR csr_pqh_gl_interface IS
3292 SELECT *
3293 FROM pqh_gl_interface
3294 WHERE budget_version_id = g_budget_version_id
3295 AND budget_detail_id = p_budget_detail_id
3296 AND period_name = p_period_name
3297 AND code_combination_id = p_code_combination_id
3298 AND currency_code = p_currency_code
3299 AND posting_type_cd = p_posting_type_cd
3300 AND NVL(adjustment_flag,'N') = 'N'
3301 AND status IS NOT NULL
3302 AND posting_date IS NOT NULL
3303 AND cost_allocation_keyflex_id is not null
3304 FOR UPDATE of amount_dr;
3305 --
3306 BEGIN
3307 --
3308 hr_utility.set_location('Entering: '||l_proc, 5);
3309 --
3310 OPEN csr_pqh_gl_interface;
3311 FETCH csr_pqh_gl_interface INTO l_pqh_gl_interface_rec;
3312 --
3313 l_amount_diff := NVL(p_amount,0) - NVL(l_pqh_gl_interface_rec.amount_dr,0);
3314 --
3315 IF l_amount_diff > 0 THEN
3316 --
3317 -- debit as new is more then old
3318 --
3319 l_amount_dr := l_amount_diff;
3320 --
3321 ELSE
3322 --
3323 -- credit as new is less then old
3324 --
3325 l_amount_cr := (-1)*l_amount_diff;
3326 --
3327 END IF;
3328 --
3329 -- update the pqh_gl_interface table
3330 --
3331 UPDATE pqh_gl_interface
3332 SET amount_dr = NVL(p_amount,0)
3333 WHERE CURRENT OF csr_pqh_gl_interface;
3334 --
3335 CLOSE csr_pqh_gl_interface;
3336 --
3337 -- create i.e insert a adjustment record ONLY if l_amount_diff <> 0
3338 --
3339 IF NVL(l_amount_diff,0) <> 0 THEN
3340 --
3341 INSERT INTO pqh_gl_interface
3342 (
3343 gl_interface_id,
3344 budget_version_id,
3345 budget_detail_id,
3346 period_name,
3347 accounting_date,
3348 code_combination_id,
3349 cost_allocation_keyflex_id,
3350 amount_dr,
3351 amount_cr,
3352 currency_code,
3353 status,
3354 adjustment_flag,
3355 posting_date,
3356 posting_type_cd
3357 )
3358 VALUES
3359 (
3360 pqh_gl_interface_s.nextval,
3361 g_budget_version_id,
3362 p_budget_detail_id,
3363 p_period_name,
3364 p_accounting_date,
3365 p_code_combination_id,
3366 p_cost_allocation_keyflex_id,
3367 NVL(l_amount_dr,0),
3368 NVL(l_amount_cr,0),
3369 p_currency_code,
3370 null,
3371 'Y',
3372 null,
3373 p_posting_type_cd
3374 );
3375 --
3376 END IF; -- create i.e insert a adjustment record ONLY if l_amount_diff <> 0
3377 --
3378 hr_utility.set_location('Leaving:'||l_proc, 1000);
3379 --
3380 EXCEPTION
3381 WHEN OTHERS THEN
3382 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3383 hr_utility.set_message_token('ROUTINE', l_proc);
3384 hr_utility.set_message_token('REASON', SQLERRM);
3385 hr_utility.raise_error;
3386 END update_pqh_gl_interface;
3387
3388 -- ----------------------------------------------------------------------------
3389 --
3390 PROCEDURE populate_pqh_gl_interface
3391 (
3392 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE,
3393 p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE,
3394 p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
3395 )
3396 IS
3397 --
3398 -- This procedure will update or insert into pqh_gl_interface if there was
3399 -- no error -- for the current budget detail record i.e g_detail_error = N.
3400 -- If g_detail_error = Y
3401 -- then update the pqh_budget_details record with gl_status = ERROR.
3402 --
3403 l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
3404 l_uom1_count number;
3405 l_uom2_count number;
3406 l_uom3_count number;
3407 --
3408 CURSOR csr_pqh_interface (p_period_name IN varchar2,
3409 p_code_combination_id IN number,
3410 p_currency_code IN varchar2) IS
3411 SELECT COUNT(*)
3412 FROM pqh_gl_interface
3413 WHERE budget_version_id = p_budget_version_id
3414 AND budget_detail_id = p_budget_detail_id
3415 AND period_name = p_period_name
3416 AND code_combination_id = p_code_combination_id
3417 AND currency_code = p_currency_code
3418 AND posting_type_cd = p_posting_type_cd
3419 AND NVL(adjustment_flag,'N') = 'N'
3420 AND status IS NOT NULL
3421 AND posting_date IS NOT NULL
3422 AND cost_allocation_keyflex_id is not null;
3423 --
3424 -- local variables
3425 --
3426 l_proc varchar2(72) := g_package||'populate_pqh_gl_interface';
3427 --
3428 BEGIN
3429 --
3430 hr_utility.set_location('Entering: '||l_proc, 5);
3431 --
3432 If g_detail_error = 'N' THEN
3433 --
3434 -- loop thru the array and get populate the pqh_gl_interface table
3435 --
3436 FOR i IN NVL(g_period_amt_tab.FIRST,0)..NVL(g_period_amt_tab.LAST,-1)
3437 LOOP
3438 --
3439 hr_utility.set_location('PERIOD '||g_period_amt_tab(i).period_name,6);
3440 IF g_period_amt_tab(i).code_combination_id is not NULL THEN
3441 -- for UOM1 i.e g_currency_code1
3442 --
3443 OPEN csr_pqh_interface
3444 (p_period_name => g_period_amt_tab(i).period_name,
3445 p_code_combination_id => g_period_amt_tab(i).code_combination_id,
3446 p_currency_code => g_currency_code1 );
3447 --
3448 FETCH csr_pqh_interface INTO l_uom1_count;
3449 --
3450 CLOSE csr_pqh_interface;
3451
3452 IF l_uom1_count <> 0 THEN
3453 --
3454 hr_utility.set_location('CURRENCY '||g_currency_code1,7);
3455 -- update pqh_gl_interface and create a adjustment txn
3456 --
3457 update_pqh_gl_interface
3458 (
3459 p_budget_detail_id => p_budget_detail_id,
3460 p_period_name => g_period_amt_tab(i).period_name,
3461 p_accounting_date => g_period_amt_tab(i).accounting_date,
3462 p_code_combination_id => g_period_amt_tab(i).code_combination_id,
3463 p_cost_allocation_keyflex_id=> g_period_amt_tab(i).cost_allocation_keyflex_id,
3464 p_amount => g_period_amt_tab(i).commitment1,
3465 p_posting_type_cd => p_posting_type_cd,
3466 p_currency_code => g_currency_code1
3467 );
3468 ELSE
3469 --
3470 hr_utility.set_location('CURRENCY '||g_currency_code1,7);
3471 -- insert into pqh_gl_interface
3472 --
3473 insert_pqh_gl_interface
3474 (
3475 p_budget_detail_id => p_budget_detail_id,
3476 p_period_name => g_period_amt_tab(i).period_name,
3477 p_accounting_date => g_period_amt_tab(i).accounting_date,
3478 p_code_combination_id => g_period_amt_tab(i).code_combination_id,
3479 p_cost_allocation_keyflex_id => g_period_amt_tab(i).cost_allocation_keyflex_id,
3480 p_amount => g_period_amt_tab(i).commitment1,
3481 p_posting_type_cd => p_posting_type_cd,
3482 p_currency_code => g_currency_code1
3483 );
3484 END IF; -- l_uom1_count <> 0
3485 --
3486 If g_budget_uom2 IS NOT NULL then
3487 --
3488 -- for UOM2 i.e g_currency_code2
3489 --
3490 OPEN csr_pqh_interface
3491 (p_period_name => g_period_amt_tab(i).period_name,
3492 p_code_combination_id => g_period_amt_tab(i).code_combination_id,
3493 p_currency_code => g_currency_code2 );
3494 --
3495 FETCH csr_pqh_interface INTO l_uom2_count;
3496 --
3497 CLOSE csr_pqh_interface;
3498
3499 IF l_uom2_count <> 0 THEN
3500 --
3501 -- update pqh_gl_interface and create a adjustment txn
3502 --
3503 update_pqh_gl_interface
3504 (
3505 p_budget_detail_id => p_budget_detail_id,
3506 p_period_name => g_period_amt_tab(i).period_name,
3507 p_accounting_date => g_period_amt_tab(i).accounting_date,
3508 p_code_combination_id => g_period_amt_tab(i).code_combination_id,
3509 p_cost_allocation_keyflex_id=> g_period_amt_tab(i).cost_allocation_keyflex_id,
3510 p_amount => g_period_amt_tab(i).commitment2,
3511 p_posting_type_cd => p_posting_type_cd,
3512 p_currency_code => g_currency_code2
3513 );
3514 ELSE
3515 --
3516 -- insert into pqh_gl_interface
3517 --
3518 insert_pqh_gl_interface
3519 (
3520 p_budget_detail_id => p_budget_detail_id,
3521 p_period_name => g_period_amt_tab(i).period_name,
3522 p_accounting_date => g_period_amt_tab(i).accounting_date,
3523 p_code_combination_id => g_period_amt_tab(i).code_combination_id,
3524 p_cost_allocation_keyflex_id => g_period_amt_tab(i).cost_allocation_keyflex_id,
3525 p_amount => g_period_amt_tab(i).commitment2,
3526 p_posting_type_cd => p_posting_type_cd,
3527 p_currency_code => g_currency_code2
3528 );
3529 END IF; -- l_uom2_count <> 0
3530 --
3531 End if;
3532 --
3533 If g_budget_uom3 IS NOT NULL then
3534 --
3535 -- for UOM3 i.e g_currency_code3
3536 --
3537 OPEN csr_pqh_interface
3538 (p_period_name => g_period_amt_tab(i).period_name,
3539 p_code_combination_id => g_period_amt_tab(i).code_combination_id,
3540 p_currency_code => g_currency_code3 );
3541 --
3542 FETCH csr_pqh_interface INTO l_uom3_count;
3543 --
3544 CLOSE csr_pqh_interface;
3545
3546 IF l_uom3_count <> 0 THEN
3547 --
3548 -- update pqh_gl_interface and create a adjustment txn
3549 --
3550 update_pqh_gl_interface
3551 (
3552 p_budget_detail_id => p_budget_detail_id,
3553 p_period_name => g_period_amt_tab(i).period_name,
3554 p_accounting_date => g_period_amt_tab(i).accounting_date,
3555 p_code_combination_id => g_period_amt_tab(i).code_combination_id,
3556 p_cost_allocation_keyflex_id=> g_period_amt_tab(i).cost_allocation_keyflex_id,
3557 p_amount => g_period_amt_tab(i).commitment3,
3558 p_posting_type_cd => p_posting_type_cd,
3559 p_currency_code => g_currency_code3
3560 );
3561 ELSE
3562 --
3563 -- insert into pqh_gl_interface
3564 --
3565 insert_pqh_gl_interface
3566 (
3567 p_budget_detail_id => p_budget_detail_id,
3568 p_period_name => g_period_amt_tab(i).period_name,
3569 p_accounting_date => g_period_amt_tab(i).accounting_date,
3570 p_code_combination_id => g_period_amt_tab(i).code_combination_id,
3571 p_cost_allocation_keyflex_id => g_period_amt_tab(i).cost_allocation_keyflex_id,
3572 p_amount => g_period_amt_tab(i).commitment3,
3573 p_posting_type_cd => p_posting_type_cd,
3574 p_currency_code => g_currency_code3
3575 );
3576 END IF; -- l_uom3_count <> 0
3577
3578 End if;
3579 --
3580 END IF;
3581 END LOOP; -- end of pl sql table
3582 --
3583 -- update pqh_budget_details reset status if previous run was ERROR
3584 --
3585 UPDATE pqh_budget_details
3586 SET commitment_gl_status = ''
3587 WHERE budget_detail_id = p_budget_detail_id;
3588 --
3589 ELSE -- g_detail_error = Y i.e errors in budget details children
3590 hr_utility.set_location('******############',101);
3591 --
3592 -- update pqh_budget_details
3593 --
3594 UPDATE pqh_budget_details
3595 SET commitment_gl_status = 'ERROR'
3596 WHERE budget_detail_id = p_budget_detail_id;
3597 --
3598 END IF; -- g_detail_error = 'N'
3599 --
3600 hr_utility.set_location('Leaving:'||l_proc, 1000);
3601 --
3602 EXCEPTION
3603 WHEN OTHERS THEN
3604 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3605 hr_utility.set_message_token('ROUTINE', l_proc);
3606 hr_utility.set_message_token('REASON', SQLERRM);
3607 hr_utility.raise_error;
3608 END populate_pqh_gl_interface;
3609
3610
3611
3612 -- ----------------------------------------------------------------------------
3613 ----------------------------------------------------------------------------------------------
3614 -- This is the MAIN procedure which is called to post budget commitment
3615 -- This would pick-up all the budget_detail under the budget_version_id
3616 -- and try to post them to gl interface tables
3617 -- If the program is run in validate mode i.e g_validate is TRUE then we
3618 -- would just check for errors and log the errors
3619 --
3620 -- Additional parameter is added p_effecitve_date for the bug 2288274
3621 --
3622 PROCEDURE post_budget_commitment
3623 (
3624 errbuf OUT NOCOPY VARCHAR2,
3625 retcode OUT NOCOPY VARCHAR2,
3626 p_effective_date IN VARCHAR2 ,
3627 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE,
3628 p_post_to_period_name IN gl_period_statuses.period_name%TYPE DEFAULT NULL,
3629 p_validate IN VARCHAR2 default 'N'
3630 ) IS
3631 --
3632 -- Declaring local variables
3633 --
3634 l_budget_details_rec pqh_budget_details%ROWTYPE;
3635 l_log_context pqh_process_log.log_context%TYPE;
3636 l_effective_dt date;
3637 --
3638 CURSOR csr_budget_detail_recs IS
3639 SELECT *
3640 FROM pqh_budget_details
3641 WHERE budget_version_id = p_budget_version_id
3642 AND NVL(commitment_gl_status,'X') <> 'POST';
3643 --
3644 -- Cursor added to check the passed budget version is control budget.(Bug 2288274)
3645 --
3646
3647 Cursor csr_check_budget_is_ctrlbgt IS
3648 Select 1
3649 From PQH_BUDGETS BGT,
3650 PQH_BUDGET_VERSIONS BVR
3651 Where BGT.BUDGET_ID = BVR.BUDGET_ID
3652 And BGT.POSITION_CONTROL_FLAG ='Y'
3653 And l_effective_dt BETWEEN BGT.BUDGET_START_DATE AND BGT.BUDGET_END_DATE
3654 And BVR.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID;
3655 --
3656 l_proc varchar2(72) := g_package||'post_budget_commitment';
3657 l_dummy varchar2(3) := null;
3658 --
3659
3660 BEGIN
3661 --
3662 hr_utility.set_location('Entering: '||l_proc, 5);
3663 --
3664 l_effective_dt := fnd_date.canonical_to_date(p_effective_date);
3665 --
3666 IF NVL(p_validate,'N') = 'Y' THEN
3667 g_validate := true;
3668 ELSE
3669 g_validate := false;
3670 END IF;
3671
3672 /* kmullapu : in procedure fetch_globals we throwing error if both transfer_to_grants ans transfer_to_gl
3673 are not set. If atlest one of them is set then it implies that Budget is controlled.
3674 So we dont require a control budget check.
3675 --
3676 -- CHECK THE BUDGET VERSION ID IS CONTROL BUDGET OR NOT.
3677 --
3678 OPEN csr_check_budget_is_ctrlbgt;
3679 FETCH csr_check_budget_is_ctrlbgt into l_dummy;
3680
3681 If csr_check_budget_is_ctrlbgt%notfound then
3682 --
3683 -- Raise Error , budget_version is not a CONTROL BUDGET_VERSION.
3684 --
3685 Close csr_check_budget_is_ctrlbgt;
3686 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_CTRL_BDGT_VERSION');
3687 APP_EXCEPTION.RAISE_EXCEPTION;
3688 End if;
3689
3690 Close csr_check_budget_is_ctrlbgt;
3691
3692 */
3693 g_budget_version_id := p_budget_version_id;
3694 --
3695 -- populate the globals and start the process log
3696 --
3697 fetch_global_values(p_budget_version_id => p_budget_version_id);
3698 --
3699 -- process all the budget details records whose commitment have not been
3700 -- posted. Also we currently generate commitment only for positions . So
3701 -- we can post only those budget detail records.
3702 --
3703 OPEN csr_budget_detail_recs;
3704 --
3705 LOOP
3706 --
3707 FETCH csr_budget_detail_recs INTO l_budget_details_rec;
3708 --
3709 EXIT WHEN csr_budget_detail_recs%NOTFOUND;
3710 --
3711 -- get log_context
3712 --
3713 set_bdt_log_context
3714 (
3715 p_budget_detail_id => l_budget_details_rec.budget_detail_id,
3716 p_log_context => l_log_context
3717 );
3718 --
3719 -- set the context
3720 --
3721 pqh_process_batch_log.set_context_level
3722 (
3723 p_txn_id => l_budget_details_rec.budget_detail_id,
3724 p_txn_table_route_id => g_table_route_id_bdt,
3725 p_level => 1,
3726 p_log_context => l_log_context
3727 );
3728 --
3729 hr_utility.set_location('--------------------------------------------', 5);
3730 hr_utility.set_location('POSITION : '||to_char(l_budget_details_rec.position_id), 5);
3731 hr_utility.set_location('--------------------------------------------', 5);
3732 -- for each budget detail
3733 --
3734 populate_period_commitment_tab
3735 (
3736 p_budget_version_id => p_budget_version_id,
3737 p_budget_detail_id => l_budget_details_rec.budget_detail_id,
3738 p_position_id => l_budget_details_rec.position_id,
3739 p_organization_id => l_budget_details_rec.organization_id,
3740 p_job_id => l_budget_details_rec.job_id,
3741 p_grade_id => l_budget_details_rec.grade_id,
3742 p_effective_date => p_effective_date
3743 );
3744 --
3745 -- get the period name and gl account
3746 --
3747 update_period_commitment_tab
3748 (
3749 p_budget_detail_id => l_budget_details_rec.budget_detail_id,
3750 p_post_to_period_name => p_post_to_period_name
3751 );
3752 --
3753 -- If the parameter is passed, consolidate the commitments into the passed period.
3754 --
3755 IF p_post_to_period_name IS NOT NULL THEN
3756 hr_utility.set_location('Consolidating into one period', 10);
3757 consolidate_commitment;
3758 END IF;
3759
3760 --
3761 -- populate pqh_gl_interface table if there was no error and
3762 -- validate is false
3763 --
3764 IF NOT g_validate THEN
3765 --
3766 -- build the old_bdgt_dtls_tab
3767 --
3768 build_old_bdgt_dtls_tab
3769 (
3770 p_budget_detail_id => l_budget_details_rec.budget_detail_id,
3771 p_posting_type_cd => 'COMMITMENT'
3772 );
3773 --
3774 -- build the new bdgt_dtls tab and populate_pqh_gl_interface
3775 --
3776 populate_pqh_gl_interface
3777 (
3778 p_budget_version_id => l_budget_details_rec.budget_version_id,
3779 p_budget_detail_id => l_budget_details_rec.budget_detail_id,
3780 p_posting_type_cd => 'COMMITMENT'
3781 );
3782
3783 populate_pqh_gms_interface
3784 (
3785 p_budget_version_id => l_budget_details_rec.budget_version_id,
3786 p_budget_detail_id => l_budget_details_rec.budget_detail_id,
3787 p_posting_type_cd => 'COMMITMENT'
3788 );
3789 --
3790 -- compare the old and new tables
3791 --
3792 compare_old_bdgt_dtls_tab;
3793 --
3794 -- reverse the old bdgt_dtls recs not in new
3795 --
3796 reverse_old_bdgt_dtls_tab
3797 (
3798 p_budget_detail_id => l_budget_details_rec.budget_detail_id,
3799 p_posting_type_cd => 'COMMITMENT'
3800 );
3801 --
3802 --
3803 END IF; -- if not in validate mode
3804 --
3805 END LOOP;
3806 --
3807 CLOSE csr_budget_detail_recs;
3808 --
3809 /** Check this out.
3810 --
3811 -- At any point of time , only ONE budget version can be posted to GL.
3812 -- So if this version is different from the previously posted version,
3813 -- we would reverse the previously posted version.
3814 --
3815 IF NOT p_validate THEN
3816 --
3817 reverse_prev_posted_version;
3818 --
3819 END IF;
3820 --
3821 **/
3822 --
3823 -- insert into gl_interface or gl_bc_packets table if not in validate mode
3824 --
3825 IF NOT g_validate THEN
3826 --
3827 populate_gl_tables;
3828 if g_transfer_to_grants_flag = 'Y' then
3829 populate_gms_tables;
3830 end if;
3831 --
3832 END IF;
3833 --
3834 -- update gl_status of pqh_budget_versions and pqh_budget_details
3835 -- update posting_date and status of pqh_gl_interface
3836 -- update the global g_status with the program status
3837 --
3838 IF NOT g_validate THEN
3839 --
3840 update_commitment_gl_status;
3841 --
3842 END IF;
3843 --
3844 -- end the error log process and update the global g_status with the program status
3845 --
3846 pqh_gl_posting.end_commitment_log(p_status => g_status);
3847 --
3848 -- commit work if run in actual mode only i.e g_validate is false
3849 --
3850 IF NOT g_validate THEN
3851 --
3852 commit;
3853 --
3854 END IF;
3855 --
3856 --
3857 hr_utility.set_location('Leaving:'||l_proc, 1000);
3858 --
3859 EXCEPTION
3860 WHEN g_error_exception THEN
3861 hr_utility.set_location('Aborting : '||l_proc, 1000);
3862 -- ROLLBACK ;
3863 pqh_gl_posting.end_commitment_log(p_status => g_status);
3864 --
3865 WHEN OTHERS THEN
3866 ROLLBACK ;
3867 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3868 hr_utility.set_message_token('ROUTINE', l_proc);
3869 hr_utility.set_message_token('REASON', SQLERRM);
3870 hr_utility.raise_error;
3871 END post_budget_commitment;
3872 -------------------------------------------------------------------------------------------------------
3873 -------------------------------------------------------------------------------------------------------
3874 PROCEDURE insert_pqh_gms_interface
3875 (
3876 p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
3877 p_period_name IN varchar2,
3878 p_project_id IN pqh_gl_interface.project_id%TYPE,
3879 p_task_id IN pqh_gl_interface.task_id%TYPE,
3880 p_award_id IN pqh_gl_interface.award_id%TYPE,
3881 p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
3882 p_organization_id IN pqh_gl_interface.organization_id%TYPE,
3883 p_amount IN pqh_gl_interface.amount_dr%TYPE,
3884 p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
3885 ) IS
3886 /*
3887 This procedure will insert record into pqh_gl_interface
3888 If the same UOM is repeated more then once then we would update the unposted txn.
3889 */
3890 --
3891 -- local variables
3892 --
3893 l_proc varchar2(72) := g_package||'.insert_pqh_gms_interface';
3894 l_count number(9) := 0 ;
3895
3896 Cursor csr_pqh_gms_interface IS
3897 Select COUNT(*)
3898 From pqh_gl_interface
3899 Where budget_version_id = g_budget_version_id
3900 AND budget_detail_id = p_budget_detail_id
3901 AND p_period_name = p_period_name
3902 AND posting_type_cd = p_posting_type_cd
3903 AND project_id = p_project_id
3904 AND task_id = p_task_id
3905 AND award_id = p_award_id
3906 AND expenditure_type = p_expenditure_type
3907 AND organization_id = p_organization_id
3908 AND NVL(adjustment_flag,'N') = 'N'
3909 AND status IS NULL
3910 AND posting_date IS NULL
3911 AND cost_allocation_keyflex_id is null;
3912
3913 BEGIN
3914
3915 hr_utility.set_location('Entering: '||l_proc, 5);
3916
3917 -- check if its a repeat of that same UOM
3918 OPEN csr_pqh_gms_interface;
3919 FETCH csr_pqh_gms_interface INTO l_count;
3920 CLOSE csr_pqh_gms_interface;
3921
3922 hr_utility.set_location('l_count in Insert pqh_gl_interface : '||l_count,10);
3923
3924 IF l_count <> 0 THEN
3925
3926 -- this is a repeat of UOM , so update the first one adding the new amount
3927 UPDATE pqh_gl_interface
3928 SET AMOUNT_DR = NVL(AMOUNT_DR,0) + NVL(p_amount,0)
3929 WHERE budget_version_id = g_budget_version_id
3930 AND budget_detail_id = p_budget_detail_id
3931 AND p_period_name = p_period_name
3932 AND posting_type_cd = p_posting_type_cd
3933 AND project_id = p_project_id
3934 AND task_id = p_task_id
3935 AND award_id = p_award_id
3936 AND expenditure_type = p_expenditure_type
3937 AND organization_id = p_organization_id
3938 AND NVL(adjustment_flag,'N') = 'N'
3939 AND status IS NULL
3940 AND posting_date IS NULL;
3941
3942 ELSE
3943
3944 -- insert this record
3945 INSERT INTO pqh_gl_interface
3946 (
3947 gl_interface_id,
3948 budget_version_id,
3949 budget_detail_id,
3950 period_name,
3951 project_id,
3952 task_id,
3953 award_id,
3954 expenditure_type,
3955 organization_id,
3956 amount_dr,
3957 amount_cr,
3958 currency_code,
3959 status,
3960 adjustment_flag,
3961 posting_date,
3962 posting_type_cd
3963 )
3964 VALUES
3965 (
3966 pqh_gl_interface_s.nextval,
3967 g_budget_version_id,
3968 p_budget_detail_id,
3969 p_period_name,
3970 p_project_id,
3971 p_task_id,
3972 p_award_id,
3973 p_expenditure_type,
3974 p_organization_id,
3975 NVL(p_amount,0),
3976 0,
3977 g_bgt_currency_code,
3978 null,
3979 null,
3980 null,
3981 p_posting_type_cd
3982 );
3983
3984 END IF; -- l_count <> 0 UOM repeated
3985
3986
3987 hr_utility.set_location('Leaving:'||l_proc, 1000);
3988
3989 EXCEPTION
3990 WHEN OTHERS THEN
3991 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3992 hr_utility.set_message_token('ROUTINE', l_proc);
3993 hr_utility.set_message_token('REASON', SQLERRM);
3994 hr_utility.raise_error;
3995 END insert_pqh_gms_interface;
3996 ---------------------------------------------------------------------------------------------
3997 ---------------------------------------------------------------------------------------------
3998 PROCEDURE update_pqh_gms_interface
3999 (
4000 p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
4001 p_period_name IN varchar2,
4002 p_project_id IN pqh_gl_interface.project_id%TYPE,
4003 p_task_id IN pqh_gl_interface.task_id%TYPE,
4004 p_award_id IN pqh_gl_interface.award_id%TYPE,
4005 p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
4006 p_organization_id IN pqh_gl_interface.organization_id%TYPE,
4007 p_amount IN pqh_gl_interface.amount_dr%TYPE,
4008 p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
4009 ) IS
4010 /*
4011 This procedure will update pqh_gl_interface and create a adjustment record
4012 */
4013 --
4014 -- local variables
4015 --
4016 l_proc varchar2(72) := g_package||'.update_pqh_gms_interface';
4017 l_amount_diff pqh_gl_interface.amount_dr%TYPE :=0;
4018 l_amount_dr pqh_gl_interface.amount_dr%TYPE :=0;
4019 l_amount_cr pqh_gl_interface.amount_cr%TYPE :=0;
4020 l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
4021
4022
4023 CURSOR csr_pqh_gms_interface IS
4024 SELECT *
4025 FROM pqh_gl_interface
4026 WHERE budget_version_id = g_budget_version_id
4027 AND budget_detail_id = p_budget_detail_id
4028 AND period_name = p_period_name
4029 AND posting_type_cd = p_posting_type_cd
4030 AND project_id = p_project_id
4031 AND task_id = p_task_id
4032 AND award_id = p_award_id
4033 AND expenditure_type = p_expenditure_type
4034 AND organization_id = p_organization_id
4035 AND NVL(adjustment_flag,'N') = 'N'
4036 AND status IS NOT NULL
4037 AND posting_date IS NOT NULL
4038 AND cost_allocation_keyflex_id is null
4039 FOR UPDATE of amount_dr;
4040
4041
4042 BEGIN
4043
4044 hr_utility.set_location('Entering: '||l_proc, 5);
4045
4046 OPEN csr_pqh_gms_interface;
4047 FETCH csr_pqh_gms_interface INTO l_pqh_gl_interface_rec;
4048
4049 l_amount_diff := NVL(p_amount,0) - NVL(l_pqh_gl_interface_rec.amount_dr,0);
4050
4051 IF l_amount_diff > 0 THEN
4052 -- debit as new is more then old
4053 l_amount_dr := l_amount_diff;
4054 ELSE
4055 -- credit as new is less then old
4056 l_amount_cr := (-1)*l_amount_diff;
4057 END IF;
4058 -- update the pqh_gl_interface table
4059 UPDATE pqh_gl_interface
4060 SET amount_dr = NVL(p_amount,0)
4061 WHERE CURRENT OF csr_pqh_gms_interface;
4062
4063 CLOSE csr_pqh_gms_interface;
4064
4065 -- create i.e insert a adjustment record ONLY if l_amount_diff <> 0
4066 IF NVL(l_amount_diff,0) <> 0 THEN
4067
4068 INSERT INTO pqh_gl_interface
4069 (
4070 gl_interface_id,
4071 budget_version_id,
4072 budget_detail_id,
4073 period_name,
4074 project_id,
4075 task_id,
4076 award_id,
4077 expenditure_type,
4078 organization_id,
4079 amount_dr,
4080 amount_cr,
4081 currency_code,
4082 status,
4083 adjustment_flag,
4084 posting_date,
4085 posting_type_cd
4086 )
4087 VALUES
4088 (
4089 pqh_gl_interface_s.nextval,
4090 g_budget_version_id,
4091 p_budget_detail_id,
4092 p_period_name,
4093 p_project_id,
4094 p_task_id,
4095 p_award_id,
4096 p_expenditure_type,
4097 p_organization_id,
4098 NVL(l_amount_dr,0),
4099 NVL(l_amount_cr,0),
4100 g_bgt_currency_code,
4101 null,
4102 'Y',
4103 null,
4104 p_posting_type_cd
4105 );
4106
4107 END IF; -- create i.e insert a adjustment record ONLY if l_amount_diff <> 0
4108
4109
4110 hr_utility.set_location('Leaving:'||l_proc, 1000);
4111
4112 EXCEPTION
4113 WHEN OTHERS THEN
4114 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4115 hr_utility.set_message_token('ROUTINE', l_proc);
4116 hr_utility.set_message_token('REASON', SQLERRM);
4117 hr_utility.raise_error;
4118 END update_pqh_gms_interface;
4119
4120 --------------------------------------------------------------------------------------------
4121 --------------------------------------------------------------------------------------------
4122
4123 PROCEDURE populate_pqh_gms_interface
4124 (
4125 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE,
4126 p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE,
4127 p_posting_type_cd IN pqh_gl_interface.posting_type_cd%TYPE
4128 )
4129 IS
4130 /*
4131 This procedure will update or insert GMS records into pqh_gl_interface if there was no error for
4132 the current budget detail record i.e g_detail_error = N
4133 if g_detail_error = Y then update the pqh_budget_details record with gl_status = ERROR
4134
4135 Also it will Deduct a similar amount from Budget Commitments.
4136 If a Bduget Commitment for that Detail/Period is not available error is thrown and program is
4137 aborted
4138 */
4139 --
4140 -- local variables
4141 --
4142 l_proc varchar2(72) := g_package||'.populate_pqh_gms_interface';
4143 l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
4144 l_uom_count number;
4145 l_amount number;
4146 l_amount_dr number;
4147 l_amount_cr number;
4148 l_uom1 varchar2(80);
4149 l_uom2 varchar2(80);
4150 l_uom3 varchar2(80);
4151
4152
4153 Cursor csr_pqh_gms_interface ( p_period_name IN varchar2,
4154 p_project_id IN NUMBER,
4155 p_task_id IN NUMBER,
4156 p_award_id IN NUMBER,
4157 p_expenditure_type IN varchar2,
4158 p_organization_id IN NUMBER,
4159 p_posting_type_cd IN VARCHAR2) IS
4160 Select *
4161 From pqh_gl_interface
4162 Where budget_version_id = p_budget_version_id
4163 AND budget_detail_id = p_budget_detail_id
4164 AND period_name = p_period_name
4165 AND posting_type_cd = p_posting_type_cd
4166 AND project_id = p_project_id
4167 AND task_id = p_task_id
4168 AND award_id = p_award_id
4169 AND expenditure_type = p_expenditure_type
4170 AND organization_id = p_organization_id
4171 AND NVL(adjustment_flag,'N') = 'N'
4172 AND cost_allocation_keyflex_id is null
4173 AND nvl(status,'X')='POST'
4174 AND posting_date IS NOT NULL
4175 AND cost_allocation_keyflex_id is null
4176 FOR UPDATE of amount_dr;
4177
4178
4179 Cursor csr_budget_units IS
4180 Select
4181 hr_general.decode_shared_type(budget_unit1_id) UOM1,
4182 hr_general.decode_shared_type(budget_unit2_id) UOM2,
4183 hr_general.decode_shared_type(budget_unit3_id) UOM3
4184 From
4185 pqh_budgets
4186 Where budget_id=g_budget_id;
4187
4188
4189 BEGIN
4190
4191 hr_utility.set_location('Entering: '||l_proc, 5);
4192
4193 IF g_detail_error = 'N' THEN
4194 OPEN csr_budget_units;
4195 FETCH csr_budget_units into l_uom1, l_uom2,l_uom3;
4196 CLOSE csr_budget_units;
4197
4198 -- loop thru the array and get populate the pqh_gl_interface table
4199
4200 FOR i IN 1..g_period_amt_tab.COUNT
4201 LOOP
4202
4203 -- Populate only GMS records
4204 IF (g_period_amt_tab(i).cost_allocation_keyflex_id is null)
4205 THEN
4206 IF NVL(l_uom1,'X')='Money' THEN
4207 l_amount :=g_period_amt_tab(i).commitment1;
4208 ELSIF NVL(l_uom2,'X')='Money' THEN
4209 l_amount :=g_period_amt_tab(i).commitment2;
4210 ELSIF NVL(l_uom3,'X')='Money' THEN
4211 l_amount :=g_period_amt_tab(i).commitment3;
4212 END IF;
4213 OPEN csr_pqh_gms_interface(p_period_name => g_period_amt_tab(i).period_name,
4214 p_project_id => g_period_amt_tab(i).project_id,
4215 p_task_id => g_period_amt_tab(i).task_id,
4216 p_award_id => g_period_amt_tab(i).award_id,
4217 p_expenditure_type => g_period_amt_tab(i).expenditure_type,
4218 p_organization_id => g_period_amt_tab(i).organization_id ,
4219 p_posting_type_cd =>'COMMITMENT');
4220 FETCH csr_pqh_gms_interface INTO l_pqh_gl_interface_rec;
4221 IF csr_pqh_gms_interface%FOUND THEN
4222 l_uom_count :=1;
4223 ELSE l_uom_count :=0;
4224 END IF;
4225 CLOSE csr_pqh_gms_interface;
4226
4227 OPEN csr_pqh_gms_interface(p_period_name => g_period_amt_tab(i).period_name,
4228 p_project_id => g_period_amt_tab(i).project_id,
4229 p_task_id => g_period_amt_tab(i).task_id,
4230 p_award_id => g_period_amt_tab(i).award_id,
4231 p_expenditure_type => g_period_amt_tab(i).expenditure_type,
4232 p_organization_id => g_period_amt_tab(i).organization_id ,
4233 p_posting_type_cd => 'BUDGET');
4234 FETCH csr_pqh_gms_interface INTO l_pqh_gl_interface_rec;
4235 IF csr_pqh_gms_interface%FOUND THEN
4236 CLOSE csr_pqh_gms_interface;
4237 hr_utility.set_message(8302, 'PQH_BUDGET_VERSION_NOT_POSTED');
4238 hr_utility.raise_error;
4239 END IF;
4240 --
4241 --We cannot Xfer a commitment greater than Budget amount posted for that period/Detail
4242 --
4243 IF(nvl(l_amount,-1) > 0 and l_amount < l_pqh_gl_interface_rec.amount_dr)
4244 THEN
4245 l_amount := l_pqh_gl_interface_rec.amount_dr;
4246 END IF;
4247
4248 CLOSE csr_pqh_gms_interface;
4249
4250
4251 IF l_uom_count <> 0 THEN
4252 -- update pqh_gl_interface and create a adjustment txn
4253 update_pqh_gms_interface
4254 (
4255 p_budget_detail_id => p_budget_detail_id,
4256 p_period_name => g_period_amt_tab(i).period_name,
4257 p_project_id => g_period_amt_tab(i).project_id,
4258 p_task_id => g_period_amt_tab(i).task_id,
4259 p_award_id => g_period_amt_tab(i).award_id,
4260 p_expenditure_type => g_period_amt_tab(i).expenditure_type,
4261 p_organization_id => g_period_amt_tab(i).organization_id,
4262 p_amount => l_amount,
4263 p_posting_type_cd => p_posting_type_cd
4264 );
4265 ELSE
4266 -- insert into pqh_gl_interface
4267 insert_pqh_gms_interface
4268 (
4269 p_budget_detail_id => p_budget_detail_id,
4270 p_period_name => g_period_amt_tab(i).period_name,
4271 p_project_id => g_period_amt_tab(i).project_id,
4272 p_task_id => g_period_amt_tab(i).task_id,
4273 p_award_id => g_period_amt_tab(i).award_id,
4274 p_expenditure_type => g_period_amt_tab(i).expenditure_type,
4275 p_organization_id => g_period_amt_tab(i).organization_id,
4276 p_amount => l_amount,
4277 p_posting_type_cd => p_posting_type_cd
4278 );
4279 END IF; -- l_uom1_count <> 0
4280
4281 --
4282 -- Deduct Commitment Amount posted, from Budget Commitment for that Detail/Period and create
4283 -- adjustment transaction for BUDGET
4284 --
4285 IF NVL(l_amount,0) <>0 THEN
4286 UPDATE pqh_gl_interface
4287 SET amount_dr = amount_dr - l_amount
4288 WHERE CURRENT OF csr_pqh_gms_interface;
4289 l_amount_dr :=0;
4290 l_amount_cr :=0;
4291 IF ( l_amount > 0) THEN
4292 l_amount_dr := l_amount;
4293 ELSE l_amount_cr := -1 * l_amount;
4294 END IF;
4295
4296
4297 INSERT INTO pqh_gl_interface
4298 (
4299 gl_interface_id,
4300 budget_version_id,
4301 budget_detail_id,
4302 period_name,
4303 project_id,
4304 task_id,
4305 award_id,
4306 expenditure_type,
4307 organization_id,
4308 amount_dr,
4309 amount_cr,
4310 currency_code,
4311 status,
4312 adjustment_flag,
4313 posting_date,
4314 posting_type_cd
4315 )
4316 VALUES
4317 (
4318 pqh_gl_interface_s.nextval,
4319 g_budget_version_id,
4320 p_budget_detail_id,
4321 g_period_amt_tab(i).period_name,
4322 g_period_amt_tab(i).project_id,
4323 g_period_amt_tab(i).task_id,
4324 g_period_amt_tab(i).award_id,
4325 g_period_amt_tab(i).expenditure_type,
4326 g_period_amt_tab(i).organization_id,
4327 l_amount_dr,
4328 l_amount_cr,
4329 g_bgt_currency_code,
4330 null,
4331 'Y',
4332 null,
4333 'BUDGET'
4334 );
4335
4336 END IF; -- create i.e insert a adjustment record ONLY if l_amount_diff <> 0
4337
4338 END IF; -- Insert only GMS records
4339 END LOOP; -- end of pl sql table
4340
4341 -- update pqh_budget_details reset status if previous run was ERROR
4342 UPDATE pqh_budget_details
4343 SET commitment_gl_status = ''
4344 WHERE budget_detail_id = p_budget_detail_id;
4345
4346
4347
4348 ELSE -- g_detail_error = Y i.e errors in budget details children
4349
4350 -- update pqh_budget_details
4351 UPDATE pqh_budget_details
4352 SET commitment_gl_status = 'ERROR'
4353 WHERE budget_detail_id = p_budget_detail_id;
4354
4355 END IF; -- g_detail_error = 'N'
4356
4357 hr_utility.set_location('Leaving:'||l_proc, 1000);
4358
4359 EXCEPTION
4360 WHEN OTHERS THEN
4361 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4362 hr_utility.set_message_token('ROUTINE', l_proc);
4363 hr_utility.set_message_token('REASON', SQLERRM);
4364 hr_utility.raise_error;
4365 END populate_pqh_gms_interface;
4366
4367 /**************************************************************/
4368
4369 PROCEDURE ins_gl_bc_run_fund_check
4370 ( p_packet_id IN gl_bc_packets.packet_id%TYPE
4371 ,p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE
4372 ,p_period_name IN pqh_gl_interface.period_name%TYPE
4373 ,p_period_year IN gl_period_statuses.period_year%TYPE
4374 ,p_period_num IN gl_period_statuses.period_num%TYPE
4375 ,p_quarter_num IN gl_period_statuses.quarter_num%TYPE
4376 ,p_currency_code IN pqh_gl_interface.currency_code%TYPE
4377 ,p_entered_dr IN pqh_gl_interface.amount_dr%TYPE
4378 ,p_entered_cr IN pqh_gl_interface.amount_cr%TYPE
4379 ,p_accounted_dr IN pqh_gl_interface.amount_dr%TYPE
4380 ,p_accounted_cr IN pqh_gl_interface.amount_cr%TYPE
4381 ,p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE
4382 ,p_fc_mode IN varchar2
4383 ,p_fc_success OUT NOCOPY boolean
4384 ,p_fc_return OUT NOCOPY varchar2
4385 )
4386 IS
4387 /*
4388 This procedure Inserts in gl_bc_packets , commits so that the data is available
4389 for the autonomous funds checker and runs funds checker returns as argument funds
4390 checker return code and success flag
4391 */
4392 --
4393 -- local variables
4394 --
4395 l_proc varchar2(72) := g_package||'.ins_gl_bc_run_fund_check';
4396 l_fc_success boolean;
4397 l_fc_return varchar2(100);
4398
4399 PRAGMA AUTONOMOUS_TRANSACTION;
4400
4401 BEGIN
4402 hr_utility.set_location('Entering: '||l_proc, 5);
4403
4404 INSERT INTO gl_bc_packets
4405 (packet_id,
4406 ledger_id,
4407 je_source_name,
4408 je_category_name,
4409 code_combination_id,
4410 actual_flag,
4411 period_name,
4412 period_year,
4413 period_num,
4414 quarter_num,
4415 currency_code,
4416 status_code,
4417 last_update_date,
4418 last_updated_by,
4419 entered_dr,
4420 entered_cr,
4421 accounted_dr,
4422 accounted_cr,
4423 encumbrance_type_id,
4424 reference1,
4425 reference2 )
4426 VALUES
4427 (p_packet_id,
4428 g_set_of_books_id,
4429 g_user_je_source_name,
4430 g_user_je_category_name,
4431 p_code_combination_id,
4432 'E',
4433 p_period_name,
4434 p_period_year,
4435 p_period_num,
4436 p_quarter_num,
4437 p_currency_code,
4438 'P',
4439 sysdate,
4440 8302,
4441 p_entered_dr,
4442 p_entered_cr,
4443 p_accounted_dr,
4444 p_accounted_cr,
4445 1000, -- encumbrance_type_id
4446 g_budget_version_id,
4447 p_cost_allocation_keyflex_id );
4448
4449 -- Funds Checker is run in autonomous mode.
4450 -- Commit so that the gl_bc_packets records are visible to fundschecker
4451 commit;
4452
4453 hr_utility.set_location('Calling GL fund checker in Mode : '||p_fc_mode,100);
4454
4455 l_fc_success := PSA_FUNDS_CHECKER_PKG.GLXFCK
4456 (
4457 p_ledgerid => g_set_of_books_id,
4458 p_packetid => p_packet_id,
4459 p_mode => p_fc_mode,
4460 p_conc_flag => 'Y',
4461 p_return_code => l_fc_return,
4462 p_calling_prog_flag => 'H'
4463 );
4464
4465 hr_utility.set_location('GL Fund Checker return Code : '||l_fc_return,110);
4466
4467 p_fc_success := l_fc_success;
4468 p_fc_return := l_fc_return;
4469
4470 -- commit the autonomous transaction
4471 commit;
4472
4473 hr_utility.set_location('Leaving:'||l_proc, 1000);
4474
4475 end ins_gl_bc_run_fund_check;
4476
4477 /**************************************************************/
4478
4479 PROCEDURE populate_pa_tables(
4480 p_gms_batch_name OUT NOCOPY varchar2,
4481 p_call_status OUT NOCOPY BOOLEAN
4482 )
4483 IS
4484 /*
4485 This procedure populates pa_transaction_interface_all and gms_transaction_interface_all tables
4486 and submits conc request to import records in to projects.
4487 It waits till conc request is complete
4488 */
4489 gms_rec gms_transaction_interface_all%ROWTYPE;
4490 l_proc varchar2(72) := g_package||'.populate_pa_tables';
4491 call_status BOOLEAN;
4492 rphase VARCHAR2(30);
4493 rstatus VARCHAR2(30);
4494 dphase VARCHAR2(30);
4495 dstatus VARCHAR2(30);
4496 message VARCHAR2(240);
4497 l_return_status VARCHAR2(30);
4498 l_txn_interface_id number(15);
4499 req_id NUMBER(15);
4500 PRAGMA AUTONOMOUS_TRANSACTION;
4501 begin
4502 hr_utility.set_location('Entering:'||l_proc, 10);
4503 --
4504 -- Select Batch Name for Transaction
4505 --
4506 Select
4507 'PQH'||to_char(pqh_gms_batch_name_s.nextval) INTO p_gms_batch_name
4508 From dual;
4509
4510 hr_utility.set_location('Batch Name: '||p_gms_batch_name, 15);
4511
4512 FOR cnt in 1..g_gms_import_tab.COUNT LOOP
4513
4514 hr_utility.set_location('Processing Record:'||g_gms_import_tab(cnt).ORIG_TRANSACTION_REFERENCE, 20);
4515 --
4516 -- Get the transaction_interface_id. We need this to populate the gms_interface table.
4517 --
4518 Select pa_txn_interface_s.nextval
4519 INTO l_txn_interface_id
4520 From dual;
4521 --
4522 -- Insert in to PA_TRANSACTIONS_ALL
4523 --
4524 INSERT INTO PA_TRANSACTION_INTERFACE_ALL
4525 (
4526 TXN_INTERFACE_ID
4527 ,TRANSACTION_SOURCE
4528 ,BATCH_NAME
4529 ,EXPENDITURE_ENDING_DATE
4530 ,ORGANIZATION_NAME
4531 ,EXPENDITURE_ITEM_DATE
4532 ,PROJECT_NUMBER
4533 ,TASK_NUMBER
4534 ,EXPENDITURE_TYPE
4535 ,QUANTITY
4536 ,TRANSACTION_STATUS_CODE
4537 ,ORIG_TRANSACTION_REFERENCE
4538 ,ORG_ID
4539 ,DENOM_CURRENCY_CODE
4540 ,DENOM_RAW_COST
4541 )
4542 VALUES
4543 (
4544 l_txn_interface_id
4545 ,g_gms_import_tab(cnt).TRANSACTION_SOURCE
4546 ,p_gms_batch_name
4547 ,g_gms_import_tab(cnt).EXPENDITURE_ENDING_DATE
4548 ,g_gms_import_tab(cnt).ORGANIZATION_NAME
4549 ,g_gms_import_tab(cnt).EXPENDITURE_ITEM_DATE
4550 ,g_gms_import_tab(cnt).PROJECT_NUMBER
4551 ,g_gms_import_tab(cnt).TASK_NUMBER
4552 ,g_gms_import_tab(cnt).EXPENDITURE_TYPE
4553 ,g_gms_import_tab(cnt).QUANTITY
4554 ,'P'
4555 ,g_gms_import_tab(cnt).ORIG_TRANSACTION_REFERENCE
4556 ,g_gms_import_tab(cnt).ORG_ID
4557 ,g_gms_import_tab(cnt).DENOM_CURRENCY_CODE
4558 ,g_gms_import_tab(cnt).amount
4559 );
4560
4561
4562 --
4563 -- insert into gms_interface table
4564 --
4565
4566 GMS_REC.TXN_INTERFACE_ID := l_txn_interface_id;
4567 GMS_REC.BATCH_NAME := p_gms_batch_name;
4568 GMS_REC.TRANSACTION_SOURCE := g_gms_import_tab(cnt).TRANSACTION_SOURCE;
4569 GMS_REC.EXPENDITURE_ENDING_DATE := g_gms_import_tab(cnt).EXPENDITURE_ENDING_DATE;
4570 GMS_REC.EXPENDITURE_ITEM_DATE := g_gms_import_tab(cnt).EXPENDITURE_ITEM_DATE ;
4571 GMS_REC.PROJECT_NUMBER := g_gms_import_tab(cnt).PROJECT_NUMBER;
4572 GMS_REC.TASK_NUMBER := g_gms_import_tab(cnt).TASK_NUMBER;
4573 GMS_REC.AWARD_ID := g_gms_import_tab(cnt).AWARD_ID ;
4574 GMS_REC.EXPENDITURE_TYPE := g_gms_import_tab(cnt).EXPENDITURE_TYPE;
4575 GMS_REC.TRANSACTION_STATUS_CODE := 'P';
4576 GMS_REC.ORIG_TRANSACTION_REFERENCE := g_gms_import_tab(cnt).ORIG_TRANSACTION_REFERENCE;
4577 GMS_REC.ORG_ID := g_gms_import_tab(cnt).ORG_ID;
4578 GMS_REC.SYSTEM_LINKAGE := NULL;
4579 GMS_REC.USER_TRANSACTION_SOURCE := NULL;
4580 GMS_REC.TRANSACTION_TYPE := NULL;
4581 GMS_REC.BURDENABLE_RAW_COST := g_gms_import_tab(cnt).AMOUNT;
4582 GMS_REC.FUNDING_PATTERN_ID := NULL;
4583
4584 gms_transactions_pub.LOAD_GMS_XFACE_API(gms_rec, l_return_status);
4585
4586 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4587 hr_utility.set_location('gms_transactions_pub failed', 25);
4588 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4589 END IF;
4590
4591 END LOOP; -- g_gms_import_tab
4592
4593
4594
4595 IF g_gms_import_tab.COUNT > 0
4596 THEN
4597 hr_utility.set_location('Submitting Request for batch: '||p_gms_batch_name, 30);
4598 req_id := fnd_request.submit_request(
4599 'PA',
4600 'PAXTRTRX',
4601 NULL,
4602 NULL,
4603 FALSE,
4604 'GMSEPQHC ',
4605 p_gms_batch_name
4606 );
4607
4608 IF req_id = 0
4609 THEN
4610 hr_utility.set_location('Conc Request not submitted properly', 35);
4611 ROLLBACK;
4612 p_call_status :=false;
4613 ELSE
4614 hr_utility.set_location('Transaction commited', 40);
4615 COMMIT;
4616 call_status := fnd_concurrent.wait_for_request(req_id, 20, 0,
4617 rphase, rstatus,
4618 dphase, dstatus,
4619 message
4620 );
4621 p_call_status := call_status;
4622 END IF;
4623 END IF;
4624 EXCEPTION
4625 WHEN OTHERS THEN
4626 ROLLBACK;
4627 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4628 hr_utility.set_message_token('ROUTINE', l_proc);
4629 hr_utility.set_message_token('REASON', SQLERRM);
4630 hr_utility.raise_error;
4631 END populate_pa_tables;
4632
4633 /**************************************************************/
4634
4635 PROCEDURE gms_pqh_tie_back
4636 (
4637 p_gms_batch_name IN VARCHAR2
4638 )
4639 IS
4640 /*
4641 This procedure ties back all the transactions posted into Oracle Grants Mgmt with records in pqh_gl_interface
4642 In case of failure the status in pqh_gl_interface is updated to error
4643 */
4644 --
4645 -- Cursor to get records rejected by import process
4646 --
4647 CURSOR gms_tie_back_reject_cur IS
4648 SELECT
4649 nvl(transaction_rejection_code,'P') rejection_code,
4650 orig_transaction_reference,
4651 transaction_status_code
4652 FROM pa_transaction_interface_all
4653 WHERE transaction_source = 'GMSEPQHC '
4654 AND batch_name = p_gms_batch_name
4655 AND transaction_status_code in ('R', 'PI', 'PR', 'PO');
4656
4657
4658 l_proc varchar2(72) := g_package||'.gms_pqh_tie_back';
4659 l_int_id BINARY_INTEGER;
4660 l_cnt number;
4661
4662
4663 Begin
4664 hr_utility.set_location('Entering:'||l_proc, 5);
4665 --
4666 -- If transaction_status_code = 'P' then the transaction import process did not kick off
4667 -- for some reason.
4668 -- If transaction_status_code = 'I' then the transaction import process did not complete
4669 -- the Post Processing extension.
4670 -- In both cases import for all records failed
4671 --
4672 SELECT
4673 count(*) into l_cnt
4674 FROM pa_transaction_interface_all
4675 WHERE transaction_source = 'GMSEPQHC '
4676 And batch_name = p_gms_batch_name
4677 And transaction_status_code in ('P', 'I');
4678
4679 --
4680 -- IF import for all records failed then update status in pqh_gl_interface to error
4681 --
4682 IF l_cnt > 0
4683 THEN
4684
4685 hr_utility.set_location('GMS Import is not Complete:'||to_char(l_cnt), 10);
4686 --
4687 hr_utility.set_message(8302,'PQH_TR_GMS_IMP_FAILED');
4688 populate_globals_error
4689 (
4690 p_message_text => FND_MESSAGE.get
4691 );
4692 RAISE g_error_exception;
4693 --
4694 ELSE
4695 hr_utility.set_location('GMS Import is complete', 15);
4696 --
4697 FOR reject_rec in gms_tie_back_reject_cur
4698 LOOP
4699 l_int_id := to_number(substr(reject_rec.orig_transaction_reference,
4700 instr(reject_rec.orig_transaction_reference,'-')+1));
4701 hr_utility.set_location('Import failed for:'||l_int_id, 20);
4702 hr_utility.set_location('Failure Code: '||reject_rec.rejection_code, 22);
4703
4704 populate_globals_error (
4705 p_message_text => pqh_gl_posting.get_gms_rejection_msg(reject_rec.rejection_code));
4706
4707 begin
4708
4709 UPDATE pqh_gl_interface
4710 SET status='ERROR',posting_date=sysdate
4711 WHERE period_name =g_gms_import_tab(l_int_id).period_name And
4712 project_id =g_gms_import_tab(l_int_id).project_id And
4713 task_id =g_gms_import_tab(l_int_id).task_id And
4714 award_id =g_gms_import_tab(l_int_id).award_id And
4715 expenditure_type =g_gms_import_tab(l_int_id).expenditure_type And
4716 organization_id =g_gms_import_tab(l_int_id).organization_id;
4717
4718 EXCEPTION
4719 when no_data_found then
4720 null;
4721 WHEN g_error_exception THEN
4722 RAISE;
4723 WHEN OTHERS THEN
4724 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4725 hr_utility.set_message_token('ROUTINE', l_proc||l_int_id);
4726 hr_utility.set_message_token('REASON', SQLERRM);
4727 hr_utility.raise_error;
4728 end;
4729 END LOOP;
4730 --
4731
4732 END IF;
4733 --
4734 --For each record that failed in import update budget_detail status
4735 --
4736 hr_utility.set_location('Set Budget Detail status to Error', 25);
4737 begin
4738 UPDATE pqh_budget_details
4739 SET gl_status = 'ERROR'
4740 Where budget_detail_id in (select budget_detail_id from pqh_gl_interface where
4741 budget_version_id=g_budget_version_id
4742 And cost_allocation_keyflex_id is null
4743 And status='ERROR'
4744 );
4745 exception
4746 when no_data_found then
4747 null;
4748 WHEN g_error_exception THEN
4749 RAISE;
4750 WHEN OTHERS THEN
4751 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4752 hr_utility.set_message_token('ROUTINE', l_proc||'2');
4753 hr_utility.set_message_token('REASON', SQLERRM);
4754 hr_utility.raise_error;
4755 end;
4756 hr_utility.set_location('Leaving:'||l_proc, 100);
4757
4758 EXCEPTION
4759 WHEN g_error_exception THEN
4760 RAISE;
4761 WHEN OTHERS THEN
4762 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4763 hr_utility.set_message_token('ROUTINE', l_proc);
4764 hr_utility.set_message_token('REASON', SQLERRM);
4765 hr_utility.raise_error;
4766 END gms_pqh_tie_back;
4767
4768 /**************************************************************/
4769
4770 PROCEDURE purge_pa_tables(
4771 p_gms_batch_name IN varchar2
4772 )
4773 IS
4774 /*
4775 Procedure to purge records from pa_transaction_interface_all and gms_transaction_interface_all
4776 once Import process is complete
4777 */
4778 l_proc varchar2(72) := g_package||'.purge_pa_tables';
4779 PRAGMA AUTONOMOUS_TRANSACTION;
4780
4781 BEGIN
4782 hr_utility.set_location('Entering:'||l_proc, 10);
4783 DELETE pa_transaction_interface_all
4784 WHERE batch_name = p_gms_batch_name
4785 And transaction_source = 'GMSEPQHC ';
4786
4787 hr_utility.set_location('Deleted pa_transaction_interface_all:',20);
4788
4789 DELETE gms_transaction_interface_all
4790 WHERE batch_name = p_gms_batch_name
4791 And transaction_source = 'GMSEPQHC ';
4792
4793 hr_utility.set_location('Deleted gms_transaction_interface_all:',30);
4794 COMMIT;
4795 hr_utility.set_location('Transaction commited:',40);
4796 hr_utility.set_location('Leaving:'||l_proc, 100);
4797 EXCEPTION
4798 WHEN OTHERS THEN
4799 ROLLBACK;
4800 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4801 hr_utility.set_message_token('ROUTINE', l_proc);
4802 hr_utility.set_message_token('REASON', SQLERRM);
4803 hr_utility.raise_error;
4804 END purge_pa_tables;
4805
4806 /**************************************************************/
4807
4808 PROCEDURE populate_gms_tables IS
4809 /*
4810 This procedure transfers records from pqh_gl_interface to pa_transaction_interface_all,
4811 kicks off the TRANSACTION IMPORT program in GMS
4812 */
4813
4814 ---------------Local Variables---------------------------------------------
4815 l_bg_id NUMBER(15) ;
4816 l_org_name hr_all_organization_units_tl.name%TYPE;
4817 l_seg1 VARCHAR2(25);
4818 l_task_number pa_tasks.task_number%TYPE;
4819 l_gms_batch_name VARCHAR2(10);
4820 l_exp_end_dt DATE;
4821 l_call_status BOOLEAN;
4822 l_value VARCHAR2(200);
4823 l_table VARCHAR2(100);
4824 l_org_id NUMBER(15);
4825 l_effective_date DATE := trunc(sysdate);
4826 l_gms_transaction_source varchar2(30);
4827 l_amount NUMBER;
4828 tran_setup_exception EXCEPTION;
4829 tran_source_exception EXCEPTION;
4830 l_pqh_interface_rec pqh_gl_interface%ROWTYPE;
4831 l_log_context pqh_process_log.log_context%TYPE;
4832 l_proc varchar2(72) := g_package||'.populate_gms_interface';
4833 l_log_message varchar2(8000);
4834 cnt BINARY_INTEGER := 1;
4835 ref_cnt BINARY_INTEGER := 1;
4836 l_period_name NUMBER;
4837 -----------------------------------------------------------------------
4838 Cursor csr_budget_bg IS
4839 Select business_group_id
4840 From pqh_budgets
4841 Where budget_id=g_budget_id;
4842
4843 Cursor csr_tran_srcs IS
4844 Select transaction_source
4845 From pa_transaction_sources
4846 Where transaction_source = 'GMSEPQHC ';
4847
4848 Cursor csr_pqh_gms_interface IS
4849 Select period_name,project_id,award_id,task_id,
4850 expenditure_type,organization_id,
4851 currency_code,
4852 SUM(NVL(amount_dr,0)) amount_dr,
4853 SUM(NVL(amount_cr,0)) amount_cr
4854 From pqh_gl_interface
4855 Where budget_version_id = g_budget_version_id
4856 AND posting_type_cd = 'COMMITMENT'
4857 AND status IS NULL
4858 AND posting_date IS NULL
4859 AND cost_allocation_keyflex_id IS NULL
4860 group by
4861 period_name,project_id,award_id,task_id,
4862 expenditure_type,organization_id,currency_code;
4863
4864
4865 Cursor csr_hr_org_name(p_organization_id NUMBER) is
4866 Select name
4867 From hr_organization_units
4868 Where organization_id = p_organization_id
4869 AND business_group_id = l_bg_id;
4870
4871 Cursor csr_pa_project_num (p_project_id NUMBER) IS
4872 Select segment1,org_id
4873 From pa_projects_all
4874 Where project_id = p_project_id;
4875
4876
4877 Cursor csr_pa_task_num(p_task_id NUMBER) IS
4878 Select task_number
4879 From pa_tasks
4880 Where task_id = p_task_id;
4881
4882
4883
4884 BEGIN
4885 hr_utility.set_location('Entering: '||l_proc, 5);
4886
4887 OPEN csr_budget_bg;
4888 FETCH csr_budget_bg INTO l_bg_id;
4889 IF (csr_budget_bg%NOTFOUND) THEN
4890 CLOSE csr_budget_bg;
4891 l_value := 'Business Group Id';
4892 l_table := 'pqh_budgets';
4893 RAISE tran_setup_exception;
4894 else
4895 CLOSE csr_budget_bg;
4896 END IF;
4897 --
4898 --Check if Transaction source is present .Other wise exit program
4899 --
4900 OPEN csr_tran_srcs;
4901 FETCH csr_tran_srcs INTO l_gms_transaction_source;
4902 IF (csr_tran_srcs%NOTFOUND) THEN
4903 CLOSE csr_tran_srcs;
4904 l_value := 'Transaction source ='||'GMSEPQHC ';
4905 l_table := 'pa_transaction_sources';
4906 RAISE tran_source_exception;
4907 else
4908 CLOSE csr_tran_srcs;
4909 END IF;
4910
4911 hr_utility.set_location('Transaction Source: '||l_gms_transaction_source, 10);
4912
4913 l_exp_end_dt := nvl(pa_utils.getweekending(sysdate),sysdate);
4914 --
4915 --Prepare a batch containing all records to be imported
4916 --
4917 For C1 in csr_pqh_gms_interface LOOP
4918 hr_utility.set_location('Processing Period: '||C1.period_name, 20);
4919 l_period_name := to_number(C1.period_name);
4920 --
4921 -- Fetch Hr Org Name
4922 --
4923 hr_utility.set_location('organization : '||C1.organization_id, 21);
4924 OPEN csr_hr_org_name (C1.organization_id);
4925 FETCH csr_hr_org_name INTO l_org_name;
4926 IF (csr_hr_org_name%NOTFOUND) THEN
4927 CLOSE csr_hr_org_name;
4928 l_value := 'Org id ='||to_char(C1.organization_id);
4929 l_table := 'HR_ORGANIZATION_UNITS';
4930 RAISE tran_setup_exception;
4931 else
4932 CLOSE csr_hr_org_name;
4933 hr_utility.set_location('org name : '||l_org_name, 22);
4934 END IF;
4935 --
4936 -- Fetch Project Number and Project Oraganization Id
4937 --
4938 hr_utility.set_location('project : '||C1.project_id, 23);
4939 OPEN csr_pa_project_num (C1.project_id);
4940 FETCH csr_pa_project_num INTO l_seg1,l_org_id;
4941 IF (csr_pa_project_num%NOTFOUND) THEN
4942 CLOSE csr_pa_project_num;
4943 l_value := 'Project id ='||to_char(C1.project_id);
4944 l_table := 'PA_PROJECTS_ALL';
4945 RAISE tran_setup_exception;
4946 else
4947 CLOSE csr_pa_project_num;
4948 hr_utility.set_location('project number : '||l_seg1, 22);
4949 END IF;
4950 --
4951 --Fetch Task Number
4952 --
4953 hr_utility.set_location('task : '||C1.task_id, 24);
4954 OPEN csr_pa_task_num (C1.task_id);
4955 FETCH csr_pa_task_num INTO l_task_number;
4956 IF (csr_pa_task_num%NOTFOUND) THEN
4957 CLOSE csr_pa_task_num;
4958 l_value := 'Task id ='||to_char(C1.task_id);
4959 l_table := 'PA_TASKS';
4960 RAISE tran_setup_exception;
4961 else
4962 CLOSE csr_pa_task_num;
4963 hr_utility.set_location('task num: '||l_task_number, 25);
4964 END IF;
4965 l_amount := C1.amount_dr + C1.amount_cr;
4966 hr_utility.set_location('setting tab row '||cnt, 26);
4967
4968 select pqh_gms_orig_txn_reference_s.nextval
4969 into ref_cnt
4970 from dual;
4971
4972 g_gms_import_tab(cnt).EXPENDITURE_ENDING_DATE :=l_exp_end_dt;
4973 g_gms_import_tab(cnt).ORGANIZATION_NAME :=l_org_name;
4974 g_gms_import_tab(cnt).EXPENDITURE_ITEM_DATE :=l_effective_date;
4975 g_gms_import_tab(cnt).PROJECT_NUMBER :=l_seg1;
4976 g_gms_import_tab(cnt).TASK_NUMBER :=l_task_number;
4977 g_gms_import_tab(cnt).QUANTITY :=1;
4978 g_gms_import_tab(cnt).ORIG_TRANSACTION_REFERENCE :='PQH'||ref_cnt||'-'||cnt;
4979 g_gms_import_tab(cnt).ORG_ID :=l_org_id;
4980 g_gms_import_tab(cnt).TRANSACTION_SOURCE :='GMSEPQHC ';
4981 g_gms_import_tab(cnt).Amount :=l_amount;
4982 g_gms_import_tab(cnt).DENOM_CURRENCY_CODE :=C1.currency_code;
4983 g_gms_import_tab(cnt).PERIOD_NAME :=C1.PERIOD_NAME;
4984 g_gms_import_tab(cnt).PROJECT_ID :=C1.PROJECT_ID;
4985 g_gms_import_tab(cnt).TASK_ID :=C1.TASK_ID;
4986 g_gms_import_tab(cnt).AWARD_ID :=C1.AWARD_ID;
4987 g_gms_import_tab(cnt).EXPENDITURE_TYPE :=C1.expenditure_type;
4988 g_gms_import_tab(cnt).ORGANIZATION_ID :=C1.ORGANIZATION_ID;
4989
4990 hr_utility.set_location('end setting tab row '||cnt, 27);
4991 cnt := cnt + 1;
4992
4993 END LOOP;
4994
4995 IF not g_validate THEN
4996 hr_utility.set_location('not validate mode : ', 30);
4997 hr_utility.set_location('calling populate_pa_tab : ', 31);
4998
4999 populate_pa_tables(l_gms_batch_name,l_call_status);
5000
5001 hr_utility.set_location('done calling populate_pa_tab : ', 32);
5002 IF l_call_status THEN
5003 hr_utility.set_location('for call back : ', 33);
5004 gms_pqh_tie_back(l_gms_batch_name);
5005 hr_utility.set_location('done call back : ', 34);
5006 END IF;
5007 purge_pa_tables(l_gms_batch_name);
5008 IF not l_call_status THEN
5009 hr_utility.set_message(8302,'PQH_TR_GMS_IMP_FAILED');
5010 populate_globals_error
5011 (
5012 p_message_text => FND_MESSAGE.get
5013 );
5014 RAISE g_error_exception;
5015 END IF;
5016 END IF;
5017
5018 hr_utility.set_location('Leaving: '||l_proc, 1000);
5019
5020 EXCEPTION
5021 WHEN tran_source_exception THEN
5022 hr_utility.set_message(8302,'PQH_TR_VALUE_NOT_FOUND');
5023 hr_utility.set_message_token('ROUTINE', l_proc);
5024 hr_utility.set_message_token('VALUE',l_value);
5025 hr_utility.set_message_token('TABLE',l_table);
5026 populate_globals_error
5027 (
5028 p_message_text => FND_MESSAGE.get
5029 );
5030 RAISE g_error_exception;
5031
5032 WHEN tran_setup_exception THEN
5033 hr_utility.set_message(8302,'PQH_TR_VALUE_NOT_FOUND');
5034 hr_utility.set_message_token('ROUTINE', l_proc);
5035 hr_utility.set_message_token('VALUE',l_value);
5036 hr_utility.set_message_token('TABLE',l_table);
5037 -- set the context
5038 pqh_gl_posting.set_bpr_log_context
5039 (
5040 p_budget_period_id =>l_period_name,
5041 p_log_context => l_log_context
5042 );
5043 pqh_process_batch_log.set_context_level
5044 (
5045 p_txn_id => l_period_name,
5046 p_txn_table_route_id => g_table_route_id_bpr,
5047 p_level => 1,
5048 p_log_context => l_log_context
5049 );
5050
5051 -- insert error
5052 pqh_process_batch_log.insert_log
5053 (
5054 p_message_type_cd => 'ERROR',
5055 p_message_text => fnd_message.get
5056 );
5057 RAISE g_error_exception;
5058 WHEN g_error_exception THEN
5059 RAISE ;
5060 WHEN OTHERS THEN
5061 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5062 hr_utility.set_message_token('ROUTINE', l_proc);
5063 hr_utility.set_message_token('REASON', SQLERRM);
5064 hr_utility.raise_error;
5065 END populate_gms_tables;
5066
5067 /**************************************************************/
5068
5069 End;