1 PACKAGE BODY pqh_gl_posting AS
2 /* $Header: pqglpost.pkb 120.8 2008/02/20 07:09:43 brsinha noship $ */
3
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_gl_posting'; -- Global package name
9 --
10 g_application_id NUMBER(15) := 101;
11 g_set_of_books_id gl_interface.set_of_books_id%TYPE;
12 g_budgetary_control_flag gl_sets_of_books.enable_budgetary_control_flag%TYPE;
13 g_budget_name pqh_budgets.budget_name%TYPE;
14 g_budgeted_entity_cd pqh_budgets.budgeted_entity_cd%TYPE;
15 g_budget_id pqh_budgets.budget_id%TYPE;
16 g_user_je_source_name gl_interface.user_je_source_name%TYPE;
17 g_user_je_category_name gl_interface.user_je_category_name%TYPE;
18 g_budget_version_id gl_interface.budget_version_id%TYPE;
19 g_gl_budget_version_id gl_interface.budget_version_id%TYPE;
20 g_version_number pqh_budget_versions.version_number%TYPE;
21 g_chart_of_accounts_id gl_interface.chart_of_accounts_id%TYPE;
22 g_currency_code gl_interface.currency_code%TYPE;
23 g_detail_error VARCHAR2(10);
24 g_currency_code1 gl_interface.currency_code%TYPE;
25 g_currency_code2 gl_interface.currency_code%TYPE;
26 g_currency_code3 gl_interface.currency_code%TYPE;
27 g_error_exception exception;
28 g_table_route_id_bvr number;
29 g_table_route_id_bdt number;
30 g_table_route_id_bpr number;
31 g_table_route_id_bfs number;
32 g_table_route_id_glf number;
33 g_status varchar2(10);
34 g_validate boolean;
35 g_last_posted_ver gl_interface.budget_version_id%TYPE;
36 g_psb_budget_flag pqh_budgets.psb_budget_flag%TYPE;
37 g_transfer_to_grants_flag pqh_budgets.transfer_to_grants_flag%TYPE;
38 g_bgt_currency_code pqh_budgets.currency_code%TYPE;
39
40
41
42 -- ----------------------------------------------------------------------------
43 --
44 -- Private procedures
45 --
46 PROCEDURE populate_globals_error
47 ( p_message_text IN pqh_process_log.message_text%TYPE) ;
48
49 --
50 PROCEDURE reverse_commitment_post(p_last_posted_ver IN NUMBER,
51 p_curr_bdgt_version IN NUMBER);
52 -- Procedures added for Transfer to Grants
53
54 PROCEDURE adjust_ptaeo_gms_amount
55 (
56 p_inx IN binary_integer,
57 p_unit_of_measure IN number,
58 p_period_encumbrance IN number,
59 p_period_tot_amount IN number
60 ) ;
61
62 PROCEDURE populate_pqh_gms_interface
63 (
64 p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE
65 );
66
67 PROCEDURE insert_pqh_gms_interface
68 (
69 p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
70 p_period_name IN varchar2,
71 p_project_id IN pqh_gl_interface.project_id%TYPE,
72 p_task_id IN pqh_gl_interface.task_id%TYPE,
73 p_award_id IN pqh_gl_interface.award_id%TYPE,
74 p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
75 p_organization_id IN pqh_gl_interface.organization_id%TYPE,
76 p_amount IN pqh_gl_interface.amount_dr%TYPE
77 );
78
79 PROCEDURE update_pqh_gms_interface
80 (
81 p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
82 p_period_name IN varchar2,
83 p_project_id IN pqh_gl_interface.project_id%TYPE,
84 p_task_id IN pqh_gl_interface.task_id%TYPE,
85 p_award_id IN pqh_gl_interface.award_id%TYPE,
86 p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
87 p_organization_id IN pqh_gl_interface.organization_id%TYPE,
88 p_amount IN pqh_gl_interface.amount_dr%TYPE
89 ) ;
90
91 PROCEDURE populate_gms_tables;
92
93 PROCEDURE ins_gl_bc_run_fund_check
94 ( p_packet_id IN gl_bc_packets.packet_id%TYPE
95 ,p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE
96 ,p_period_name IN pqh_gl_interface.period_name%TYPE
97 ,p_period_year IN gl_period_statuses.period_year%TYPE
98 ,p_period_num IN gl_period_statuses.period_num%TYPE
99 ,p_quarter_num IN gl_period_statuses.quarter_num%TYPE
100 ,p_currency_code IN pqh_gl_interface.currency_code%TYPE
101 ,p_entered_dr IN pqh_gl_interface.amount_dr%TYPE
102 ,p_entered_cr IN pqh_gl_interface.amount_cr%TYPE
103 ,p_accounted_dr IN pqh_gl_interface.amount_dr%TYPE
104 ,p_accounted_cr IN pqh_gl_interface.amount_cr%TYPE
105 ,p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE
106 ,p_fc_mode IN varchar2
107 ,p_fc_success OUT NOCOPY boolean
108 ,p_fc_return OUT NOCOPY varchar2
109 );
110 -- Procedure added to run funds checker in autonomous transaction
111
112 ------------------------------------------------------------------------------
113 PROCEDURE conc_post_budget
114 (
115 errbuf OUT NOCOPY VARCHAR2,
116 retcode OUT NOCOPY VARCHAR2,
117 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE,
118 p_validate IN varchar2 default 'N'
119 ) IS
120 /*
121 This procedure will call the post_budget procedure . This procedure is written as this
122 would be called from the concurrent program Budget GL Posting ( PQHGLPOST )
123 */
124 --
125 -- local variables
126 --
127 l_proc varchar2(72) := g_package||'.conc_post_budget';
128 l_status varchar2(50);
129 l_validate boolean;
130
131 BEGIN
132
133 hr_utility.set_location('Entering: '||l_proc, 5);
134
135 IF NVL(p_validate,'N') = 'Y' THEN
136 l_validate := true;
137 ELSE
138 l_validate := false;
139 END IF;
140
141 post_budget
142 ( p_budget_version_id => p_budget_version_id,
143 p_validate => l_validate,
144 p_status => l_status
145 );
146
147
148 hr_utility.set_location('Leaving:'||l_proc, 1000);
149
150 END;
151
152
153
154
155 -- ----------------------------------------------------------------------------
156
157 PROCEDURE post_budget
158 (
159 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE,
160 p_validate IN boolean default false,
161 p_status OUT NOCOPY varchar2
162 ) IS
163 /*
164 This is the MAIN procedure which would be called.
165 This would pick-up all the budget_detail_ids under the budget_version_id and
166 try to post them to gl interface tables and pa interface table
167 If the program is run in validate mode i.e p_validate is TRUE then we would just check for
168 errors in pqh budget tables i.e period and gl account errors ,LD Encumbrance and log the errors
169 */
170 --
171 -- local variables
172 --
173 l_proc varchar2(72) := g_package||'.post_budget';
174 l_budget_details_rec pqh_budget_details%ROWTYPE;
175 l_log_context pqh_process_log.log_context%TYPE;
176 l_budget_detail_result varchar2(1);
177 l_message_text fnd_new_messages.message_text%TYPE;
178 l_dummy varchar2(10);
179
180
181 CURSOR csr_budget_detail_recs IS
182 SELECT *
183 FROM pqh_budget_details
184 WHERE budget_version_id = p_budget_version_id
185 AND NVL(gl_status,'X') <> 'POST';
186
187
188 BEGIN
189
190 hr_utility.set_location('Entering: '||l_proc, 5);
191
192 -- establish a savepoint at the begining
193 -- added as part of fixing bug#3265978 by kgowripe
194 savepoint post_budget;
195 --
196 -- populate the out variable to ERROR and start processing
197 -- at the end we will populate the out variable depending on
198 -- the program status
199 p_status := 'ERROR';
200 g_validate := p_validate;
201
202 -- populate the globals and start the process log
203 populate_globals
204 (
205 p_budget_version_id => p_budget_version_id
206 );
207
208 -- If this Budget is marked as 'Transfer Commitments Only' then we cannot Transfer this budget
209 -- In that case we will end log and terminate the program
210 IF (nvl(g_psb_budget_flag,'N') ='Y') THEN
211 FND_MESSAGE.SET_NAME('PQH','PQH_CMMT_XFER_BUDGET');
212 l_message_text := FND_MESSAGE.GET;
213 populate_globals_error
214 (
215 p_message_text => l_message_text
216 );
217 -- abort the program
218 RAISE g_error_exception;
219 END IF;
220
221
222 -- process all the budget details
223 OPEN csr_budget_detail_recs;
224 LOOP
225 FETCH csr_budget_detail_recs INTO l_budget_details_rec;
226 EXIT WHEN csr_budget_detail_recs%NOTFOUND;
227
228 -- get log_context
229 set_bdt_log_context
230 (
231 p_budget_detail_id => l_budget_details_rec.budget_detail_id,
232 p_log_context => l_log_context
233 );
234
235 -- set the context
236 pqh_process_batch_log.set_context_level
237 (
238 p_txn_id => l_budget_details_rec.budget_detail_id,
239 p_txn_table_route_id => g_table_route_id_bdt,
240 p_level => 1,
241 p_log_context => l_log_context
242 );
243
244 -- for each budget detail
245 populate_period_amt_tab
246 (
247 p_budget_detail_id => l_budget_details_rec.budget_detail_id
248 );
249
250 -- get the period name ,gl account and LD Encumbrance adjustments
251 update_period_amt_tab
252 (
253 p_budget_detail_id => l_budget_details_rec.budget_detail_id
254 );
255
256 -- populate pqh_gl_interface table if there was no error and validate is false
257 IF NOT p_validate THEN
258 -- build the old_bdgt_dtls_tab
259 build_old_bdgt_dtls_tab
260 (
261 p_budget_detail_id => l_budget_details_rec.budget_detail_id
262 );
263
264 -- build the new bdgt_dtls tab and populate_pqh_gl_interface
265 -- we use same table pqh_gl_interface for Gl as well as GMS transfer
266 populate_pqh_gl_interface
267 (
268 p_budget_detail_id => l_budget_details_rec.budget_detail_id
269 );
270
271 populate_pqh_gms_interface
272 (
273 p_budget_detail_id => l_budget_details_rec.budget_detail_id
274 );
275
276 -- compare the old and new tables
277 compare_old_bdgt_dtls_tab;
278
279 -- reverse the old bdgt_dtls recs not in new
280 reverse_old_bdgt_dtls_tab
281 (
282 p_budget_detail_id => l_budget_details_rec.budget_detail_id
283 );
284
285 END IF; -- if not in validate mode
286
287 END LOOP;
288 CLOSE csr_budget_detail_recs;
289
290
291 /*
292 At any point of time , only ONE budget version can be posted. So if this version is different
293 from the previously posted version, we would reverse the previously posted version.
294 */
295
296 IF NOT p_validate THEN
297 reverse_prev_posted_version;
298 END IF;
299
300 -- if not in validate mode
301 -- insert into gl_interface or gl_bc_packets table all Records that need to be transfered to GL
302 -- For all Records that need to be transfered to Grants
303 -- insert into pa_interface_all table and call gms_pub api
304
305 IF NOT p_validate THEN
306 populate_gl_tables;
307 if g_transfer_to_grants_flag = 'Y' then
308 populate_gms_tables;
309 end if;
310 END IF;
311
312 /*
313 update gl_status of pqh_budget_versions and pqh_budget_details
314 update posting_date and status of pqh_gl_interface
315 update the global g_status with the program status
316 */
317
318 IF NOT p_validate THEN
319 update_gl_status;
320 END IF;
321
322
323 -- end the error log process and update the global g_status with the program status
324 end_log;
325
326 -- commit work if run in actual mode only i.e p_validate is false
327 IF NOT p_validate THEN
328 commit;
329 END IF;
330
331 -- update the OUT param
332 p_status := g_status;
333
334
335 hr_utility.set_location('Leaving:'||l_proc, 1000);
336
337 EXCEPTION
338 WHEN g_error_exception THEN
339 hr_utility.set_location('Aborting : '||l_proc, 1000);
340 -- ROLLBACK ;
341 rollback to post_budget;
342 end_log;
343 WHEN OTHERS THEN
344 -- ROLLBACK ;
345 -- rollback to the save point established at the start of the procedure
346 -- added as part fix for bug#3265978 by kgowripe
347 rollback to post_budget;
348 --
349 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
350 hr_utility.set_message_token('ROUTINE', l_proc);
351 hr_utility.set_message_token('REASON', SQLERRM);
352 hr_utility.raise_error;
353 END post_budget;
354
355 -- ----------------------------------------------------------------------------
356 PROCEDURE populate_globals
357 (
358 p_budget_version_id IN pqh_budget_versions.budget_version_id%TYPE
359 ) IS
360 /*
361 This procedure will populate all the global variables and start the log context
362 If there is any error in populate globals we will end log and terminate the program
363 */
364 --
365 -- local variables
366 --
367 l_proc varchar2(72) := g_package||'.populate_globals';
368 l_budgets_rec pqh_budgets%ROWTYPE;
369 l_budget_versions_rec pqh_budget_versions%ROWTYPE;
370 l_gl_sets_of_books_rec gl_sets_of_books%ROWTYPE;
371 l_shared_types_rec per_shared_types%ROWTYPE;
372 l_gl_budget_versions_rec gl_budget_versions%ROWTYPE;
373 l_gl_je_sources_rec gl_je_sources%ROWTYPE;
374 l_gl_je_categories_rec gl_je_categories%ROWTYPE;
375 l_transfer_to_gl_flag varchar2(10);
376 l_message_text pqh_process_log.message_text%TYPE;
377 l_message_text_out fnd_new_messages.message_text%TYPE;
378 l_error_flag varchar2(10) := 'N';
379 l_level number;
380 l_batch_id number;
381 l_batch_context varchar2(2000);
382 l_count number;
383 l_map_count_null number;
384 l_budget_detail_result varchar2(1);
385 l_gl_budget_name pqh_budgets.GL_BUDGET_NAME%TYPE;
386
387 CURSOR csr_budget_versions_rec IS
388 SELECT *
389 FROM pqh_budget_versions
390 WHERE budget_version_id = p_budget_version_id;
391
392 CURSOR csr_budgets_rec (c_budget_id IN NUMBER) IS
393 SELECT *
394 FROM pqh_budgets
395 WHERE budget_id = c_budget_id;
396 /* ns budget_id is already available, no need to fetch again
397 ( SELECT budget_id
398 FROM pqh_budget_versions
399 WHERE budget_version_id = p_budget_version_id ) ;
400 */
401
402 CURSOR csr_bus_grp (p_business_group_id IN NUMBER) IS
403 SELECT bg.ORG_INFORMATION10
404 FROM HR_ORGANIZATION_INFORMATION bg
405 WHERE bg.organization_id = p_business_group_id
406 AND bg.ORG_INFORMATION_CONTEXT = 'Business Group Information';
407
408 CURSOR csr_chart_of_acc_id(p_set_of_books_id IN NUMBER) IS
409 SELECT *
410 FROM gl_sets_of_books
411 WHERE set_of_books_id = p_set_of_books_id;
412
413 CURSOR csr_shared_types (p_shared_type_id IN number) IS
414 SELECT *
415 FROM per_shared_types
416 WHERE shared_type_id = p_shared_type_id;
417 -- Change by kmullapu. Changed p_budget_name to p_gl_budget_name as we can
418 --now select GL Budget Name from Budget Charectaristics form
419
420 CURSOR csr_gl_budget_version (p_gl_budget_name IN varchar2) IS
421 SELECT *
422 FROM gl_budget_versions
423 WHERE budget_name = p_gl_budget_name AND
424 status in ('O','C');
425
426 CURSOR csr_gl_je_sources IS
427 SELECT *
428 FROM gl_je_sources
429 WHERE je_source_name = 'Public Sector Budget';
430
431 CURSOR csr_gl_je_categories IS
432 SELECT *
433 FROM gl_je_categories
434 WHERE je_category_name = 'Public Sector Budget';
435
436 CURSOR csr_flex_maps_counts (p_budget_id IN number)IS
437 SELECT COUNT(*)
438 FROM pqh_budget_gl_flex_maps
439 WHERE budget_id = p_budget_id;
440
441 CURSOR csr_table_route IS
442 -- ns (p_table_alias IN varchar2 )IS
443 SELECT table_alias,table_route_id
444 FROM pqh_table_route
445 WHERE table_alias IN ('BVR','BDT','BPR','BFS','GLF');
446 -- = p_table_alias;
447
448 CURSOR csr_cost_map_null (p_budget_id IN number) IS
449 SELECT COUNT(*)
450 FROM pqh_budget_gl_flex_maps
451 WHERE budget_id = p_budget_id
452 AND payroll_cost_segment IS NULL;
453
454
455 BEGIN
456
457 hr_utility.set_location('Entering: '||l_proc, 5);
458
459 g_budget_version_id := p_budget_version_id;
460
461 hr_utility.set_location('p_budget_version_id '||p_budget_version_id,6);
462
463 -- check if the input budget version Id is valid and the budget_versions is
464 -- not already posted
465 OPEN csr_budget_versions_rec;
466 FETCH csr_budget_versions_rec INTO l_budget_versions_rec;
467 CLOSE csr_budget_versions_rec;
468
469 hr_utility.set_location('Step 1 Budget version id '||l_budget_versions_rec.budget_version_id,6);
470
471 IF l_budget_versions_rec.budget_version_id IS NULL THEN
472 -- no record fetched i.e invalid budget_version id
473 -- halt the program here
474 FND_MESSAGE.SET_NAME('PQH','PQH_INV_BDG_VERSION_ID');
475 APP_EXCEPTION.RAISE_EXCEPTION;
476 END IF;
477
478 hr_utility.set_location('Step 2 ',6);
479
480 -- check if budget version already posted
481 IF NVL(l_budget_versions_rec.gl_status,'X') = 'POST' THEN
482 -- this budget version is already posted
483 -- halt the program here
484 FND_MESSAGE.SET_NAME('PQH','PQH_BUDGET_ALREADY_POSTED');
485 APP_EXCEPTION.RAISE_EXCEPTION;
486 END IF;
487
488 -- set the version number
489 g_version_number := l_budget_versions_rec.version_number;
490
491 OPEN csr_budgets_rec(l_budget_versions_rec.budget_id);
492 FETCH csr_budgets_rec INTO l_budgets_rec;
493 CLOSE csr_budgets_rec;
494
495 g_budget_name := l_budgets_rec.budget_name;
496 g_budgeted_entity_cd := l_budgets_rec.budgeted_entity_cd;
497 g_budget_id := l_budgets_rec.budget_id;
498 g_set_of_books_id := l_budgets_rec.gl_set_of_books_id;
499 l_transfer_to_gl_flag := l_budgets_rec.transfer_to_gl_flag;
500 l_gl_budget_name := l_budgets_rec.gl_budget_name;
501 g_psb_budget_flag := l_budgets_rec.psb_budget_flag;
502 g_transfer_to_grants_flag := l_budgets_rec.transfer_to_grants_flag;
503 g_bgt_currency_code := l_budgets_rec.currency_code;
504
505 /*
506 Start the Process Log here
507 Batch ID = Budget Version Id
508 Batch Context = Budget Name + Version Number
509 */
510 l_batch_id := g_budget_version_id;
511
512 l_batch_context := g_budget_name||' - '||g_version_number;
513
514 hr_utility.set_location('Batch Context : '||l_batch_context,7);
515
516 -- Start the Log Process
517 pqh_process_batch_log.start_log
518 (
519 p_batch_id => l_batch_id,
520 p_module_cd => 'GL_POSTING',
521 p_log_context => l_batch_context
522 );
523
524 hr_utility.set_location('Step 3 ',7);
525
526 -- CHECK : if g_set_of_books_id IS NOT NULL
527 IF g_set_of_books_id IS NULL THEN
528 -- get message text for PQH_INVALID_GL_SET_BOOKS
529 -- message : Set of Books is not defined for the budget
530 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_GL_SET_BOOKS');
531 l_message_text_out := FND_MESSAGE.GET;
532
533 IF l_error_flag = 'Y' THEN
534 -- there is already an error so append the message
535
536 l_message_text := l_message_text||' **** '||l_message_text_out;
537 ELSE
538 -- new message
539 l_message_text := l_message_text_out;
540 END IF;
541
542 -- set l_error_flag to Y
543 l_error_flag := 'Y';
544
545 END IF; -- g_set_of_books_id IS NOT NULL
546
547 -- CHECK : if g_bgt_currency_code IS NOT NULL
548 IF g_bgt_currency_code IS NULL THEN
549 open csr_bus_grp(l_budgets_rec.business_group_id);
550 fetch csr_bus_grp into g_bgt_currency_code;
551 close csr_bus_grp;
552 /*
553 commenting the code which used to give error, if currency on budget was null
554 -- get message text for PQH_INVALID_BGT_CURR_CODE
555 -- message : Currency Code is not defined for the budget
556 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_BGT_CURR_CODE');
557 l_message_text_out := FND_MESSAGE.GET;
558
559 IF l_error_flag = 'Y' THEN
560 -- there is already an error so append the message
561
562 l_message_text := l_message_text||' **** '||l_message_text_out;
563 ELSE
564 -- new message
565 l_message_text := l_message_text_out;
566 END IF;
567
568 -- set l_error_flag to Y
569 l_error_flag := 'Y';
570 */
571 END IF; -- g_bgt_currency_code IS NOT NULL
572
573
574 -- CHECK : if transfer_to_gl_flag IS Y
575 IF NVL(l_transfer_to_gl_flag,'N') <> 'Y' THEN
576 -- get message text for PQH_BUDGET_TRANSFER_FLAG
577 -- message : This Budget cannot be transfered to GL. Please check the budget characteristics
578 IF NVL(g_transfer_to_grants_flag,'N') <> 'Y' THEN
579 FND_MESSAGE.SET_NAME('PQH','PQH_BUDGET_TRANSFER_FLAG');
580 l_message_text_out := FND_MESSAGE.GET;
581 IF l_error_flag = 'Y' THEN
582 -- there is already an error so append the message
583
584 l_message_text := l_message_text||' **** '||l_message_text_out;
585 ELSE
586 -- new message
587 l_message_text := l_message_text_out;
588 END IF;
589
590 -- set l_error_flag to Y
591 l_error_flag := 'Y';
592 END IF;
593 ELSE
594 -- check if rows in pqh_budget_gl_flex_maps with NULL cost segments
595 OPEN csr_cost_map_null(p_budget_id => g_budget_id);
596 FETCH csr_cost_map_null INTO l_map_count_null;
597 CLOSE csr_cost_map_null;
598
599 IF NVL(l_map_count_null,0) <> 0 THEN
600
601 -- get message text for PQH_BUDGET_GL_MAP
602 -- message : Some of the GL segments are not mapped with cost segments.
603 -- You must map all the GL segments with cost segments
604 FND_MESSAGE.SET_NAME('PQH','PQH_BUDGET_COST_SEGMENT_NULL');
605 l_message_text_out := FND_MESSAGE.GET;
606
607 IF l_error_flag = 'Y' THEN
608 -- there is already an error so append the message
609
610 l_message_text := l_message_text||' **** '||l_message_text_out;
611 ELSE
612 -- new message
613 l_message_text := l_message_text_out;
614 END IF;
615
616 -- set l_error_flag to Y
617 l_error_flag := 'Y';
618 END IF; -- l_map_count_null <> 0
619
620 END IF; -- if transfer_to_gl_flag IS Y
621
622 -- CHECK if the budget is mapped
623 OPEN csr_flex_maps_counts(p_budget_id => g_budget_id);
624 FETCH csr_flex_maps_counts INTO l_count;
625 CLOSE csr_flex_maps_counts;
626
627 -- CHECK : count <> 0 i.e mapping is defined
628 IF NVL(l_count,0) = 0 THEN
629 -- get message text for PQH_BUDGET_GL_MAP
630 -- message : Mapping with GL segments not defined for the budget
631 FND_MESSAGE.SET_NAME('PQH','PQH_BUDGET_GL_MAP');
632 l_message_text_out := FND_MESSAGE.GET;
633
634 IF l_error_flag = 'Y' THEN
635 -- there is already an error so append the message
636
637 l_message_text := l_message_text||' **** '||l_message_text_out;
638 ELSE
639 -- new message
640 l_message_text := l_message_text_out;
641 END IF;
642
643 -- set l_error_flag to Y
644 l_error_flag := 'Y';
645
646 END IF; -- count <> 0 i.e mapping is defined
647
648 -- get gl_budget_version_id
649 OPEN csr_gl_budget_version(p_gl_budget_name => l_gl_budget_name);
650 FETCH csr_gl_budget_version INTO l_gl_budget_versions_rec;
651 CLOSE csr_gl_budget_version;
652
653 g_gl_budget_version_id := l_gl_budget_versions_rec.budget_version_id;
654
655 -- CHECK : if gl_budget_version_id exists else error
656 IF g_gl_budget_version_id IS NULL THEN
657 -- get message text for PQH_GL_BUDGET_INVALID
658 -- message : Budget is not defined in GL
659 FND_MESSAGE.SET_NAME('PQH','PQH_GL_BUDGET_INVALID');
660 l_message_text_out := FND_MESSAGE.GET;
661
662 IF l_error_flag = 'Y' THEN
663 -- there is already an error so append the message
664
665 l_message_text := l_message_text||' **** '||l_message_text_out;
666 ELSE
667 -- new message
668 l_message_text := l_message_text_out;
669 END IF;
670
671 -- set l_error_flag to Y
672 l_error_flag := 'Y';
673
674 END IF; -- gl_budget_version_id is null
675
676
677 -- get the set of books , budgetary control flag and currency for money
678 OPEN csr_chart_of_acc_id(p_set_of_books_id => g_set_of_books_id );
679 FETCH csr_chart_of_acc_id INTO l_gl_sets_of_books_rec;
680 CLOSE csr_chart_of_acc_id;
681
682 g_chart_of_accounts_id := l_gl_sets_of_books_rec.chart_of_accounts_id;
683 g_budgetary_control_flag := l_gl_sets_of_books_rec.enable_budgetary_control_flag;
684 g_currency_code := l_gl_sets_of_books_rec.currency_code;
685
686 /*
687 call the get_default_currency to get business group currency code
688 this procedure will check if there is a default currency associated with the business_group
689 if yes it will override the gl_sets_of_books currency code
690 */
691 get_default_currency;
692
693 -- get the je_source
694 OPEN csr_gl_je_sources;
695 FETCH csr_gl_je_sources INTO l_gl_je_sources_rec;
696 CLOSE csr_gl_je_sources;
697
698 g_user_je_source_name := l_gl_je_sources_rec.user_je_source_name;
699
700 -- CHECK : if g_user_je_source_name IS NOT NULL
701 IF g_user_je_source_name IS NULL THEN
702 -- get message text for PQH_INVALID_JE_SOURCE_NAME
703 -- message : Journal Source Name not defined
704 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_JE_SOURCE_NAME');
705 l_message_text_out := FND_MESSAGE.GET;
706
707 IF l_error_flag = 'Y' THEN
708 -- there is already an error so append the message
709
710 l_message_text := l_message_text||' **** '||l_message_text_out;
711 ELSE
712 -- new message
713 l_message_text := l_message_text_out;
714 END IF;
715
716 -- set l_error_flag to Y
717 l_error_flag := 'Y';
718
719 END IF; -- g_user_je_source_name IS NOT NULL
720
721 -- get the je category
722 OPEN csr_gl_je_categories;
723 FETCH csr_gl_je_categories INTO l_gl_je_categories_rec;
724 CLOSE csr_gl_je_categories;
725
726 g_user_je_category_name := l_gl_je_categories_rec.user_je_category_name;
727
728 -- CHECK : if g_user_je_category_name IS NOT NULL
729 IF g_user_je_category_name IS NULL THEN
730 -- get message text for PQH_INVALID_JE_CATEGORY_NAME
731 -- message : Journal Category Name not defined
732 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_JE_CATEGORY_NAME');
733 l_message_text_out := FND_MESSAGE.GET;
734
735 IF l_error_flag = 'Y' THEN
736 -- there is already an error so append the message
737
738 l_message_text := l_message_text||' **** '||l_message_text_out;
739 ELSE
740 -- new message
741 l_message_text := l_message_text_out;
742 END IF;
743
744 -- set l_error_flag to Y
745 l_error_flag := 'Y';
746
747 END IF; -- g_user_je_category_name IS NOT NULL
748
749 -- populate the currency codes
750 OPEN csr_shared_types(p_shared_type_id => l_budgets_rec.budget_unit1_id );
751 FETCH csr_shared_types INTO l_shared_types_rec;
752 CLOSE csr_shared_types;
753
754 IF l_shared_types_rec.system_type_cd = 'MONEY' THEN
755 g_currency_code1 := g_currency_code;
756 ELSE
757 g_currency_code1 := 'STAT';
758 END IF;
759
760 IF l_budgets_rec.budget_unit2_id IS NOT NULL THEN
761
762 OPEN csr_shared_types(p_shared_type_id => l_budgets_rec.budget_unit2_id );
763 FETCH csr_shared_types INTO l_shared_types_rec;
764 CLOSE csr_shared_types;
765
766 IF l_shared_types_rec.system_type_cd = 'MONEY' THEN
767 g_currency_code2 := g_currency_code;
768 ELSE
769 g_currency_code2 := 'STAT';
770 END IF;
771
772 END IF; -- budget_unit2_id IS NOT NULL
773
774 IF l_budgets_rec.budget_unit3_id IS NOT NULL THEN
775
776 OPEN csr_shared_types(p_shared_type_id => l_budgets_rec.budget_unit3_id );
777 FETCH csr_shared_types INTO l_shared_types_rec;
778 CLOSE csr_shared_types;
779
780 IF l_shared_types_rec.system_type_cd = 'MONEY' THEN
781 g_currency_code3 := g_currency_code;
782 ELSE
783 g_currency_code3 := 'STAT';
784 END IF;
785
786 END IF; -- budget_unit3_id IS NOT NULL
787
788 -- mvankada
789
790 -- This function will determine whether the budget has details or not
791 -- If the budget has details return 'Y' else 'N'
792
793 l_budget_detail_result := pqh_gl_posting.chk_budget_details(p_budget_version_id => p_budget_version_id );
794 if l_budget_detail_result = 'N' then
795 FND_MESSAGE.SET_NAME('PQH','PQH_EMPTY_BUDGET');
796 l_message_text := FND_MESSAGE.GET;
797 populate_globals_error( p_message_text => l_message_text);
798 end if;
799
800
801 For csr_tab in csr_table_route Loop
802 if csr_tab.table_alias = 'BVR' then
803 g_table_route_id_bvr := csr_tab.table_route_id;
804 elsif csr_tab.table_alias = 'BDT' then
805 g_table_route_id_bdt := csr_tab.table_route_id;
806 elsif csr_tab.table_alias = 'BPR' then
807 g_table_route_id_bpr := csr_tab.table_route_id;
808 elsif csr_tab.table_alias = 'BFS' then
809 g_table_route_id_bfs := csr_tab.table_route_id;
810 elsif csr_tab.table_alias = 'GLF' then
811 g_table_route_id_glf := csr_tab.table_route_id;
812 end if;
813 End Loop;
814
815 /* ns fetched it in one cursor instead.
816 -- get the table route id for pqh_budget versions
817 OPEN csr_table_route(p_table_alias => 'BVR');
818 FETCH csr_table_route INTO g_table_route_id_bvr;
819 CLOSE csr_table_route;
820
821 -- get the table route id for pqh_budget details
822 OPEN csr_table_route(p_table_alias => 'BDT');
823 FETCH csr_table_route INTO g_table_route_id_bdt;
824 CLOSE csr_table_route;
825
826 -- get the table route id for pqh_budget details
827 OPEN csr_table_route(p_table_alias => 'BPR');
828 FETCH csr_table_route INTO g_table_route_id_bpr;
829 CLOSE csr_table_route;
830
831 -- get the table route id for pqh_budget fund srcs
832 OPEN csr_table_route(p_table_alias => 'BFS');
833 FETCH csr_table_route INTO g_table_route_id_bfs;
834 CLOSE csr_table_route;
835
836 -- get the table route id for gl_bc_packets
837 OPEN csr_table_route(p_table_alias => 'GLF');
838 FETCH csr_table_route INTO g_table_route_id_glf;
839 CLOSE csr_table_route;
840
841 */
842
843 hr_utility.set_location('Budget Name : '||g_budget_name,100);
844 hr_utility.set_location('Set Of Books Id : '||g_set_of_books_id,110);
845 hr_utility.set_location('g_gl_budget_version_id : '||g_gl_budget_version_id,111);
846 hr_utility.set_location('g_budget_version_id : '||g_budget_version_id,112);
847 hr_utility.set_location('g_budgetary_control_flag : '||g_budgetary_control_flag,120);
848 hr_utility.set_location('g_currency_code1 : '||g_currency_code1,150);
849 hr_utility.set_location('g_currency_code2 : '||g_currency_code2,160);
850 hr_utility.set_location('g_currency_code3 : '||g_currency_code3,170);
851 hr_utility.set_location('g_user_je_source_name : '||g_user_je_source_name,180);
852 hr_utility.set_location('g_user_je_category_name : '||g_user_je_category_name,190);
853
854
855
856 -- if any errors the end the process log and abort the program
857 IF l_error_flag = 'Y' THEN
858
859 -- end the process log as the batch itself has error
860 populate_globals_error
861 (
862 p_message_text => l_message_text
863 );
864
865 /*
866 -- we would rollback any inserts before we update
867 -- this is done to undo apply_budget if called from apply_transaction
868 rollback;
869
870 UPDATE pqh_process_log
871 SET message_type_cd = 'ERROR',
872 message_text = l_message_text,
873 txn_table_route_id = g_table_route_id_bvr
874 -- batch_status = 'ERROR',
875 -- batch_end_date = sysdate
876 WHERE process_log_id = pqh_process_batch_log.g_master_process_log_id;
877
878 -- commit the update work
879 commit;
880 */
881
882 -- abort the program
883 RAISE g_error_exception;
884
885 END IF; -- insert error message if l_error_flag is Y
886
887
888
889 hr_utility.set_location('Leaving:'||l_proc, 1000);
890
891 EXCEPTION
892 WHEN g_error_exception THEN
893 RAISE;
894 WHEN OTHERS THEN
895 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
896 hr_utility.set_message_token('ROUTINE', l_proc);
897 hr_utility.set_message_token('REASON', SQLERRM);
898 hr_utility.raise_error;
899 END populate_globals;
900
901
902
903 -- ----------------------------------------------------------------------------
904 PROCEDURE populate_period_amt_tab
905 (
906 p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE
907 ) IS
908 /*
909 this procedure will populate the global table g_period_amt_tab
910 Cursor csr_dflt_period_amts calculates the Funding Source wise distribution
911 from the Default Budget Set Distribution
912 */
913 --
914 -- local variables
915 --
916 l_proc varchar2(72) := g_package||'.populate_period_amt_tab';
917 l_budget_period_id pqh_budget_periods.budget_period_id%TYPE;
918 l_cost_allocation_keyflex_id pqh_budget_fund_srcs.cost_allocation_keyflex_id%TYPE;
919 l_project_id pqh_budget_fund_srcs.project_id%TYPE;
920 l_award_id pqh_budget_fund_srcs.award_id%TYPE;
921 l_task_id pqh_budget_fund_srcs.task_id%TYPE;
922 l_expenditure_type pqh_budget_fund_srcs.expenditure_type%TYPE;
923 l_organization_id pqh_budget_fund_srcs.organization_id%TYPE;
924 l_amount1 NUMBER;
925 l_amount2 NUMBER;
926 l_amount3 NUMBER;
927 i BINARY_INTEGER :=1;
928
929
930 CURSOR csr_period_amts IS
931 SELECT bpr.budget_period_id ,
932 bfs.cost_allocation_keyflex_id,
933 bfs.project_id,
934 bfs.award_id,
935 bfs.task_id,
936 bfs.expenditure_type,
937 bfs.organization_id,
938 SUM(pqh_gl_posting.get_amt1(bfs.budget_fund_src_id)) Amount1,
939 SUM(pqh_gl_posting.get_amt2(bfs.budget_fund_src_id)) Amount2,
940 SUM(pqh_gl_posting.get_amt3(bfs.budget_fund_src_id)) Amount3
941 FROM pqh_budget_fund_srcs bfs, pqh_budget_elements bel,
942 pqh_budget_sets bst, pqh_budget_periods bpr
943 WHERE bpr.budget_period_id = bst.budget_period_id
944 AND bst.budget_set_id = bel.budget_set_id
945 AND bel.budget_element_id = bfs.budget_element_id
946 AND bpr.budget_detail_id = p_budget_detail_id
947 GROUP BY bpr.budget_period_id ,bfs.cost_allocation_keyflex_id,
948 bfs.project_id, bfs.award_id,bfs.task_id,
949 bfs.expenditure_type,bfs.organization_id
950 ORDER BY bpr.budget_period_id , bfs.cost_allocation_keyflex_id,
951 bfs.project_id, bfs.award_id,bfs.task_id,
952 bfs.expenditure_type,bfs.organization_id;
953
954 CURSOR csr_dflt_period_amts IS
955 SELECT bpr.budget_period_id ,
956 bfs.cost_allocation_keyflex_id,
957 bfs.project_id,
958 bfs.award_id,
959 bfs.task_id,
960 bfs.expenditure_type,
961 bfs.organization_id,
962 SUM((NVL(bfs.dflt_dist_percentage,0)*0.01)*(NVL(bel.dflt_dist_percentage,0)*0.01)*NVL(bst.budget_unit1_value,0)) Amount1,
963 SUM((NVL(bfs.dflt_dist_percentage,0)*0.01)*(NVL(bel.dflt_dist_percentage,0)*0.01)*NVL(bst.budget_unit2_value,0)) Amount2,
964 SUM((NVL(bfs.dflt_dist_percentage,0)*0.01)*(NVL(bel.dflt_dist_percentage,0)*0.01)*NVL(bst.budget_unit3_value,0)) Amount3
965 FROM pqh_dflt_fund_srcs bfs,
966 pqh_dflt_budget_elements bel,
967 pqh_budget_sets bst,
968 pqh_budget_periods bpr
969 WHERE bpr.budget_period_id = bst.budget_period_id
970 AND bst.dflt_budget_set_id = bel.dflt_budget_set_id
971 AND bel.dflt_budget_element_id = bfs.dflt_budget_element_id
972 AND bpr.budget_detail_id = p_budget_detail_id
973 GROUP BY bpr.budget_period_id ,bfs.cost_allocation_keyflex_id,
974 bfs.project_id, bfs.award_id,bfs.task_id,
975 bfs.expenditure_type,bfs.organization_id
976 ORDER BY bpr.budget_period_id , bfs.cost_allocation_keyflex_id,
977 bfs.project_id, bfs.award_id,bfs.task_id,
978 bfs.expenditure_type,bfs.organization_id;
979
980 BEGIN
981
982 hr_utility.set_location('Entering: '||l_proc, 5);
983
984 -- delete the g_period_amt_tab;
985 g_period_amt_tab.DELETE;
986
987 OPEN csr_period_amts;
988 LOOP
989 FETCH csr_period_amts INTO l_budget_period_id, l_cost_allocation_keyflex_id,
990 l_project_id,l_award_id,l_task_id,
991 l_expenditure_type,l_organization_id,
992 l_amount1, l_amount2, l_amount3;
993 EXIT WHEN csr_period_amts%NOTFOUND;
994
995 g_period_amt_tab(i).period_id := l_budget_period_id;
996 g_period_amt_tab(i).cost_allocation_keyflex_id := l_cost_allocation_keyflex_id;
997 g_period_amt_tab(i).project_id := l_project_id;
998 g_period_amt_tab(i).award_id := l_award_id;
999 g_period_amt_tab(i).task_id := l_task_id;
1000 g_period_amt_tab(i).expenditure_type := l_expenditure_type;
1001 g_period_amt_tab(i).organization_id := l_organization_id;
1002 g_period_amt_tab(i).amount1 := l_amount1;
1003 g_period_amt_tab(i).amount2 := l_amount2;
1004 g_period_amt_tab(i).amount3 := l_amount3;
1005
1006 i := i + 1;
1007
1008 END LOOP;
1009 CLOSE csr_period_amts;
1010 -- Added By kgowripe. Populate Default Budget Set Distribution
1011 -- Should be using the Default Budget Set Distribution in case there is no
1012 -- elements and Funding sources defined for the Budget set in the Budget Period
1013 IF i = 1 THEN
1014 hr_utility.set_location('Populating Default Budget Set Distribution '||l_proc,10);
1015 OPEN csr_dflt_period_amts;
1016 LOOP
1017 FETCH csr_dflt_period_amts INTO l_budget_period_id, l_cost_allocation_keyflex_id,
1018 l_project_id,l_award_id,l_task_id,
1019 l_expenditure_type,l_organization_id,
1020 l_amount1, l_amount2, l_amount3;
1021 EXIT WHEN csr_dflt_period_amts%NOTFOUND;
1022 g_period_amt_tab(i).period_id := l_budget_period_id;
1023 g_period_amt_tab(i).cost_allocation_keyflex_id := l_cost_allocation_keyflex_id;
1024 g_period_amt_tab(i).project_id := l_project_id;
1025 g_period_amt_tab(i).award_id := l_award_id;
1026 g_period_amt_tab(i).task_id := l_task_id;
1027 g_period_amt_tab(i).expenditure_type := l_expenditure_type;
1028 g_period_amt_tab(i).organization_id := l_organization_id;
1029 g_period_amt_tab(i).amount1 := l_amount1;
1030 g_period_amt_tab(i).amount2 := l_amount2;
1031 g_period_amt_tab(i).amount3 := l_amount3;
1032
1033 i := i+1;
1034 END LOOP;
1035 CLOSE csr_dflt_period_amts;
1036 END IF;
1037 -- End code changes by kgowripe
1038 hr_utility.set_location('Leaving:'||l_proc, 1000);
1039
1040 EXCEPTION
1041 WHEN OTHERS THEN
1042 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1043 hr_utility.set_message_token('ROUTINE', l_proc);
1044 hr_utility.set_message_token('REASON', SQLERRM);
1045 hr_utility.raise_error;
1046 END populate_period_amt_tab;
1047
1048 -- ----------------------------------------------------------------------------
1049 PROCEDURE update_period_amt_tab
1050 (
1051 p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE
1052 )
1053 IS
1054 /*
1055 This procedure will read the above populated global table g_period_amt_tab and
1056
1057 1.Get the period_name and code_combination_id corresponding to the period_id and
1058 cost_allocation_keyflex_id. If it does not find a period_name or a code_combination_id then
1059 it will populate the global variable g_detail_error to Y.
1060
1061 2.Get LD Encumbrance/Liquidation amount for each Budget Period and make adjustments to
1062 all PTAEO's invlved in that Budget period.
1063
1064 If g_detail_error is Y then we will not populate the pqh_gl_interface table for the current
1065 budget_detail_id
1066 */
1067
1068 --
1069 -- local variables
1070 --
1071 l_proc varchar2(72) := g_package||'.update_period_amt_tab';
1072 l_period_name gl_period_statuses.period_name%TYPE;
1073 l_accounting_date gl_period_statuses.start_date%TYPE;
1074 l_code_combination_id gl_code_combinations.code_combination_id%TYPE;
1075 l_gl_period_statuses_rec gl_period_statuses%ROWTYPE;
1076 l_message_text pqh_process_log.message_text%TYPE;
1077 l_message_text_out fnd_new_messages.message_text%TYPE;
1078 l_count NUMBER;
1079 l_error_flag varchar2(10) := 'N';
1080 l_log_context pqh_process_log.log_context%TYPE;
1081
1082
1083
1084 BEGIN
1085
1086 hr_utility.set_location('Entering: '||l_proc, 5);
1087
1088 -- initialize g_detail_error
1089 g_detail_error := 'N';
1090
1091 IF NVL(g_period_amt_tab.COUNT,0) <> 0 THEN
1092 --
1093 --IF Budget is Transfered to GMS then we need to Get LD encumbrance amount and make PTAEO adjustments
1094 --
1095 IF (NVL(g_transfer_to_grants_flag,'N') ='Y' AND fnd_profile.value('PSP_ENC_ENABLE_PQH')='Y')
1096 THEN
1097 populate_period_enc_tab(p_budget_detail_id);
1098 END IF;
1099 -- loop thru the array and get the segment value
1100 FOR i IN NVL(g_period_amt_tab.FIRST,0)..NVL(g_period_amt_tab.LAST,-1)
1101 LOOP
1102 --GMS Changes by kmullapu
1103 --Check if records is Gl records (cost_allocation_flexfield is not null)
1104 --get the period_name and code_combination_id only for Gl reocrds
1105 --
1106 IF (g_period_amt_tab(i).cost_allocation_keyflex_id is not null) THEN
1107 -- period name
1108 get_gl_period
1109 (
1110 p_budget_period_id => g_period_amt_tab(i).period_id,
1111 p_gl_period_statuses_rec => l_gl_period_statuses_rec
1112 );
1113
1114 l_period_name := l_gl_period_statuses_rec.period_name;
1115 l_accounting_date := l_gl_period_statuses_rec.start_date;
1116
1117 IF l_period_name IS NULL THEN
1118 -- no period name found mark detail as error and proceed
1119 g_detail_error := 'Y';
1120 -- get log_context
1121 set_bpr_log_context
1122 (
1123 p_budget_period_id => g_period_amt_tab(i).period_id,
1124 p_log_context => l_log_context
1125 );
1126
1127 -- set the context
1128 pqh_process_batch_log.set_context_level
1129 (
1130 p_txn_id => g_period_amt_tab(i).period_id,
1131 p_txn_table_route_id => g_table_route_id_bpr,
1132 p_level => 2,
1133 p_log_context => l_log_context
1134 );
1135
1136 -- get message text for PQH_INVALID_GL_BUDGET_PERIOD
1137 -- message : There is no corresponding period in GL for the current Budget Period
1138 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_GL_BUDGET_PERIOD');
1139 l_message_text := FND_MESSAGE.GET;
1140
1141 -- insert error
1142 pqh_process_batch_log.insert_log
1143 (
1144 p_message_type_cd => 'ERROR',
1145 p_message_text => l_message_text
1146 );
1147
1148
1149 ELSE
1150 -- update the pl sql table with period name and accounting date
1151 g_period_amt_tab(i).period_name := l_period_name;
1152 g_period_amt_tab(i).accounting_date := l_accounting_date;
1153 END IF;
1154
1155 -- gl account
1156 get_gl_ccid
1157 (
1158 p_budget_detail_id => p_budget_detail_id,
1159 p_budget_period_id => g_period_amt_tab(i).period_id,
1160 p_cost_allocation_keyflex_id => g_period_amt_tab(i).cost_allocation_keyflex_id,
1161 p_code_combination_id => l_code_combination_id
1162 );
1163
1164 IF l_code_combination_id IS NULL THEN
1165 -- no gl account found, mark as error
1166 g_detail_error := 'Y';
1167
1168 -- get log_context
1169 set_bfs_log_context
1170 (
1171 p_cost_allocation_keyflex_id => g_period_amt_tab(i).cost_allocation_keyflex_id,
1172 p_log_context => l_log_context
1173 );
1174
1175 -- set the context
1176 pqh_process_batch_log.set_context_level
1177 (
1178 p_txn_id => g_period_amt_tab(i).cost_allocation_keyflex_id,
1179 p_txn_table_route_id => g_table_route_id_bfs,
1180 p_level => 2,
1181 p_log_context => l_log_context
1182 );
1183
1184 -- get message text for PQH_INVALID_GL_BUDGET_ACCOUNT
1185 -- message : There is no corresponding account in GL for the current funding source
1186 FND_MESSAGE.SET_NAME('PQH','PQH_INVALID_GL_BUDGET_ACCOUNT');
1187 l_message_text := FND_MESSAGE.GET;
1188
1189 -- insert error
1190 pqh_process_batch_log.insert_log
1191 (
1192 p_message_type_cd => 'ERROR',
1193 p_message_text => l_message_text
1194 );
1195
1196
1197 ELSE
1198 -- update the pl sql table with gl account
1199 g_period_amt_tab(i).code_combination_id := l_code_combination_id;
1200 END IF;
1201 ELSE
1202 --
1203 -- For Records that are being Xfered to Grants Peiod_name is nothing but Period_id
1204 --
1205 g_period_amt_tab(i).period_name :=to_char(g_period_amt_tab(i).period_id);
1206 END IF; /** g_period_amt_tab(i).cost_allocation_keyflex_id is not null **/
1207 END LOOP; -- end of pl sql table
1208 END IF;
1209
1210
1211
1212 hr_utility.set_location('Leaving:'||l_proc, 1000);
1213
1214 EXCEPTION
1215 WHEN OTHERS THEN
1216 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1217 hr_utility.set_message_token('ROUTINE', l_proc);
1218 hr_utility.set_message_token('REASON', SQLERRM);
1219 hr_utility.raise_error;
1220 END update_period_amt_tab;
1221
1222
1223 -- ----------------------------------------------------------------------------
1224
1225 PROCEDURE populate_pqh_gl_interface
1226 (
1227 p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE
1228 )
1229 IS
1230 /*
1231 This procedure will update or insert into pqh_gl_interface if there was no error for
1232 the current budget detail record i.e g_detail_error = N
1233 if g_detail_error = Y then update the pqh_budget_details record with gl_status = ERROR
1234 */
1235 --
1236 -- local variables
1237 --
1238 l_proc varchar2(72) := g_package||'.populate_pqh_gl_interface';
1239 l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
1240 l_uom1_count number;
1241 l_uom2_count number;
1242 l_uom3_count number;
1243
1244 CURSOR csr_pqh_interface (p_period_name IN varchar2,
1245 p_code_combination_id IN number,
1246 p_currency_code IN varchar2) IS
1247 SELECT COUNT(*)
1248 FROM pqh_gl_interface
1249 WHERE budget_version_id = g_budget_version_id
1250 AND budget_detail_id = p_budget_detail_id
1251 AND posting_type_cd = 'BUDGET'
1252 AND period_name = p_period_name
1253 AND code_combination_id = p_code_combination_id
1254 AND currency_code = p_currency_code
1255 AND NVL(adjustment_flag,'N') = 'N'
1256 AND status IS NOT NULL
1257 AND posting_date IS NOT NULL;
1258
1259
1260 BEGIN
1261
1262 hr_utility.set_location('Entering: '||l_proc, 5);
1263
1264 IF g_detail_error = 'N' THEN
1265
1266 -- loop thru the array and get populate the pqh_gl_interface table
1267
1268 FOR i IN NVL(g_period_amt_tab.FIRST,0)..NVL(g_period_amt_tab.LAST,-1)
1269 LOOP
1270
1271 -- Populate only GL records
1272 IF (g_period_amt_tab(i).cost_allocation_keyflex_id is not null)
1273 THEN
1274 -- for UOM1 i.e g_currency_code1
1275 OPEN csr_pqh_interface(p_period_name => g_period_amt_tab(i).period_name,
1276 p_code_combination_id => g_period_amt_tab(i).code_combination_id,
1277 p_currency_code => g_currency_code1 );
1278 FETCH csr_pqh_interface INTO l_uom1_count;
1279 CLOSE csr_pqh_interface;
1280
1281 IF l_uom1_count <> 0 THEN
1282 -- update pqh_gl_interface and create a adjustment txn
1283 update_pqh_gl_interface
1284 (
1285 p_budget_detail_id => p_budget_detail_id,
1286 p_period_name => g_period_amt_tab(i).period_name,
1287 p_accounting_date => g_period_amt_tab(i).accounting_date,
1288 p_code_combination_id => g_period_amt_tab(i).code_combination_id,
1289 p_cost_allocation_keyflex_id => g_period_amt_tab(i).cost_allocation_keyflex_id,
1290 p_amount => g_period_amt_tab(i).amount1,
1291 p_currency_code => g_currency_code1
1292 );
1293 ELSE
1294 -- insert into pqh_gl_interface
1295 insert_pqh_gl_interface
1296 (
1297 p_budget_detail_id => p_budget_detail_id,
1298 p_period_name => g_period_amt_tab(i).period_name,
1299 p_accounting_date => g_period_amt_tab(i).accounting_date,
1300 p_code_combination_id => g_period_amt_tab(i).code_combination_id,
1301 p_cost_allocation_keyflex_id => g_period_amt_tab(i).cost_allocation_keyflex_id,
1302 p_amount => g_period_amt_tab(i).amount1,
1303 p_currency_code => g_currency_code1
1304 );
1305 END IF; -- l_uom1_count <> 0
1306
1307
1308 -- for UOM2 i.e g_currency_code2
1309
1310 IF (g_currency_code2 IS NOT NULL) AND (g_period_amt_tab(i).amount2 <> 0) THEN
1311 OPEN csr_pqh_interface(p_period_name => g_period_amt_tab(i).period_name,
1312 p_code_combination_id => g_period_amt_tab(i).code_combination_id,
1313 p_currency_code => g_currency_code2 );
1314 FETCH csr_pqh_interface INTO l_uom2_count;
1315 CLOSE csr_pqh_interface;
1316
1317 IF l_uom2_count <> 0 THEN
1318 -- update pqh_gl_interface and create a adjustment txn
1319 update_pqh_gl_interface
1320 (
1321 p_budget_detail_id => p_budget_detail_id,
1322 p_period_name => g_period_amt_tab(i).period_name,
1323 p_accounting_date => g_period_amt_tab(i).accounting_date,
1324 p_code_combination_id => g_period_amt_tab(i).code_combination_id,
1325 p_cost_allocation_keyflex_id => g_period_amt_tab(i).cost_allocation_keyflex_id,
1326 p_amount => g_period_amt_tab(i).amount2,
1327 p_currency_code => g_currency_code2
1328 );
1329 ELSE
1330 -- insert into pqh_gl_interface
1331 insert_pqh_gl_interface
1332 (
1333 p_budget_detail_id => p_budget_detail_id,
1334 p_period_name => g_period_amt_tab(i).period_name,
1335 p_accounting_date => g_period_amt_tab(i).accounting_date,
1336 p_code_combination_id => g_period_amt_tab(i).code_combination_id,
1337 p_cost_allocation_keyflex_id => g_period_amt_tab(i).cost_allocation_keyflex_id,
1338 p_amount => g_period_amt_tab(i).amount2,
1339 p_currency_code => g_currency_code2
1340 );
1341 END IF; -- l_uom2_count <> 0
1342
1343 END IF; -- g_currency_code2 IS NOT NULL and amt2 <> 0
1344
1345
1346 -- for UOM3 i.e g_currency_code3
1347
1348 IF (g_currency_code3 IS NOT NULL) AND (g_period_amt_tab(i).amount3 <> 0) THEN
1349 OPEN csr_pqh_interface(p_period_name => g_period_amt_tab(i).period_name,
1350 p_code_combination_id => g_period_amt_tab(i).code_combination_id,
1351 p_currency_code => g_currency_code3 );
1352 FETCH csr_pqh_interface INTO l_uom3_count;
1353 CLOSE csr_pqh_interface;
1354
1355 IF l_uom3_count <> 0 THEN
1356 -- update pqh_gl_interface and create a adjustment txn
1357 update_pqh_gl_interface
1358 (
1359 p_budget_detail_id => p_budget_detail_id,
1360 p_period_name => g_period_amt_tab(i).period_name,
1361 p_accounting_date => g_period_amt_tab(i).accounting_date,
1362 p_code_combination_id => g_period_amt_tab(i).code_combination_id,
1363 p_cost_allocation_keyflex_id => g_period_amt_tab(i).cost_allocation_keyflex_id,
1364 p_amount => g_period_amt_tab(i).amount3,
1365 p_currency_code => g_currency_code3
1366 );
1367 ELSE
1368 -- insert into pqh_gl_interface
1369 insert_pqh_gl_interface
1370 (
1371 p_budget_detail_id => p_budget_detail_id,
1372 p_period_name => g_period_amt_tab(i).period_name,
1373 p_accounting_date => g_period_amt_tab(i).accounting_date,
1374 p_code_combination_id => g_period_amt_tab(i).code_combination_id,
1375 p_cost_allocation_keyflex_id => g_period_amt_tab(i).cost_allocation_keyflex_id,
1376 p_amount => g_period_amt_tab(i).amount3,
1377 p_currency_code => g_currency_code3
1378 );
1379 END IF; -- l_uom3_count <> 0
1380
1381 END IF; -- g_currency_code3 IS NOT NULL and amt3 <> 0
1382
1383
1384
1385
1386
1387 END IF; -- Insert only GL records
1388 END LOOP; -- end of pl sql table
1389
1390 -- update pqh_budget_details reset status if previous run was ERROR
1391 UPDATE pqh_budget_details
1392 SET gl_status = ''
1393 WHERE budget_detail_id = p_budget_detail_id;
1394
1395
1396
1397 ELSE -- g_detail_error = Y i.e errors in budget details children
1398
1399 -- update pqh_budget_details
1400 UPDATE pqh_budget_details
1401 SET gl_status = 'ERROR'
1402 WHERE budget_detail_id = p_budget_detail_id;
1403
1404 END IF; -- g_detail_error = 'N'
1405
1406 hr_utility.set_location('Leaving:'||l_proc, 1000);
1407
1408 EXCEPTION
1409 WHEN OTHERS THEN
1410 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1411 hr_utility.set_message_token('ROUTINE', l_proc);
1412 hr_utility.set_message_token('REASON', SQLERRM);
1413 hr_utility.raise_error;
1414 END populate_pqh_gl_interface;
1415
1416
1417
1418 -- ----------------------------------------------------------------------------
1419 PROCEDURE insert_pqh_gl_interface
1420 (
1421 p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
1422 p_period_name IN pqh_gl_interface.period_name%TYPE,
1423 p_accounting_date IN pqh_gl_interface.accounting_date%TYPE,
1424 p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE,
1425 p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE,
1426 p_amount IN pqh_gl_interface.amount_dr%TYPE,
1427 p_currency_code IN pqh_gl_interface.currency_code%TYPE
1428 ) IS
1429 /*
1430 This procedure will insert record into pqh_gl_interface
1431 If the same UOM is repeated more then once then we would update the unposted txn.
1432 */
1433 --
1434 -- local variables
1435 --
1436 l_proc varchar2(72) := g_package||'.insert_pqh_gl_interface';
1437 l_count number(9) := 0 ;
1438
1439 CURSOR csr_pqh_gl_interface IS
1440 SELECT COUNT(*)
1441 FROM pqh_gl_interface
1442 WHERE budget_version_id = g_budget_version_id
1443 AND budget_detail_id = p_budget_detail_id
1444 AND posting_type_cd = 'BUDGET'
1445 AND period_name = p_period_name
1446 AND code_combination_id = p_code_combination_id
1447 AND currency_code = p_currency_code
1448 AND NVL(adjustment_flag,'N') = 'N'
1449 AND status IS NULL
1450 AND posting_date IS NULL;
1451
1452 BEGIN
1453
1454 hr_utility.set_location('Entering: '||l_proc, 5);
1455 -- check if its a repeat of that same UOM
1456 OPEN csr_pqh_gl_interface;
1457 FETCH csr_pqh_gl_interface INTO l_count;
1458 CLOSE csr_pqh_gl_interface;
1459
1460 hr_utility.set_location('l_count in Insert pqh_gl_interface : '||l_count,10);
1461
1462 IF l_count <> 0 THEN
1463
1464 -- this is a repeat of UOM , so update the first one adding the new amount
1465 UPDATE pqh_gl_interface
1466 -- ns since the record is new, the current amount is actual amount
1467 -- no need to add to previous amount
1468 -- SET AMOUNT_DR = NVL(AMOUNT_DR,0) + NVL(p_amount,0)
1469 SET AMOUNT_DR = NVL(p_amount,0)
1470 WHERE budget_version_id = g_budget_version_id
1471 AND budget_detail_id = p_budget_detail_id
1472 AND posting_type_cd = 'BUDGET'
1473 AND period_name = p_period_name
1474 AND code_combination_id = p_code_combination_id
1475 AND currency_code = p_currency_code
1476 AND NVL(adjustment_flag,'N') = 'N'
1477 AND status IS NULL
1478 AND posting_date IS NULL;
1479
1480 ELSE
1481
1482 -- insert this record
1483 INSERT INTO pqh_gl_interface
1484 (
1485 gl_interface_id,
1486 budget_version_id,
1487 budget_detail_id,
1488 period_name,
1489 accounting_date,
1490 code_combination_id,
1491 cost_allocation_keyflex_id,
1492 amount_dr,
1493 amount_cr,
1494 currency_code,
1495 status,
1496 adjustment_flag,
1497 posting_date,
1498 posting_type_cd
1499 )
1500 VALUES
1501 (
1502 pqh_gl_interface_s.nextval,
1503 g_budget_version_id,
1504 p_budget_detail_id,
1505 p_period_name,
1506 p_accounting_date,
1507 p_code_combination_id,
1508 p_cost_allocation_keyflex_id,
1509 NVL(p_amount,0),
1510 0,
1511 p_currency_code,
1512 null,
1513 null,
1514 null,
1515 'BUDGET'
1516 );
1517
1518 END IF; -- l_count <> 0 UOM repeated
1519
1520
1521 hr_utility.set_location('Leaving:'||l_proc, 1000);
1522
1523 EXCEPTION
1524 WHEN OTHERS THEN
1525 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1526 hr_utility.set_message_token('ROUTINE', l_proc);
1527 hr_utility.set_message_token('REASON', SQLERRM);
1528 hr_utility.raise_error;
1529 END insert_pqh_gl_interface;
1530
1531 -- ----------------------------------------------------------------------------
1532 PROCEDURE update_pqh_gl_interface
1533 (
1534 p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
1535 p_period_name IN pqh_gl_interface.period_name%TYPE,
1536 p_accounting_date IN pqh_gl_interface.accounting_date%TYPE,
1537 p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE,
1538 p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE,
1539 p_amount IN pqh_gl_interface.amount_dr%TYPE,
1540 p_currency_code IN pqh_gl_interface.currency_code%TYPE
1541 ) IS
1542 /*
1543 This procedure will update pqh_gl_interface and create a adjustment record
1544 */
1545 --
1546 -- local variables
1547 --
1548 l_proc varchar2(72) := g_package||'.update_pqh_gl_interface';
1549 l_amount_diff pqh_gl_interface.amount_dr%TYPE :=0;
1550 l_amount_dr pqh_gl_interface.amount_dr%TYPE :=0;
1551 l_amount_cr pqh_gl_interface.amount_cr%TYPE :=0;
1552 l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
1553
1554
1555 CURSOR csr_pqh_gl_interface IS
1556 SELECT *
1557 FROM pqh_gl_interface
1558 WHERE budget_version_id = g_budget_version_id
1559 AND budget_detail_id = p_budget_detail_id
1560 AND posting_type_cd = 'BUDGET'
1561 AND period_name = p_period_name
1562 AND code_combination_id = p_code_combination_id
1563 AND currency_code = p_currency_code
1564 AND NVL(adjustment_flag,'N') = 'N'
1565 AND status IS NOT NULL
1566 AND posting_date IS NOT NULL
1567 FOR UPDATE of amount_dr;
1568
1569 BEGIN
1570
1571 hr_utility.set_location('Entering: '||l_proc, 5);
1572
1573 OPEN csr_pqh_gl_interface;
1574 FETCH csr_pqh_gl_interface INTO l_pqh_gl_interface_rec;
1575
1576 l_amount_diff := NVL(p_amount,0) - NVL(l_pqh_gl_interface_rec.amount_dr,0);
1577
1578 IF l_amount_diff > 0 THEN
1579 -- debit as new is more then old
1580 l_amount_dr := l_amount_diff;
1581 ELSE
1582 -- credit as new is less then old
1583 l_amount_cr := (-1)*l_amount_diff;
1584 END IF;
1585
1586
1587 -- update the pqh_gl_interface table
1588 UPDATE pqh_gl_interface
1589 SET amount_dr = NVL(p_amount,0)
1590 WHERE CURRENT OF csr_pqh_gl_interface;
1591
1592
1593 CLOSE csr_pqh_gl_interface;
1594
1595 -- create i.e insert a adjustment record ONLY if l_amount_diff <> 0
1596 IF NVL(l_amount_diff,0) <> 0 THEN
1597
1598 INSERT INTO pqh_gl_interface
1599 (
1600 gl_interface_id,
1601 budget_version_id,
1602 budget_detail_id,
1603 period_name,
1604 accounting_date,
1605 code_combination_id,
1606 cost_allocation_keyflex_id,
1607 amount_dr,
1608 amount_cr,
1609 currency_code,
1610 status,
1611 adjustment_flag,
1612 posting_date,
1613 posting_type_cd
1614 )
1615 VALUES
1616 (
1617 pqh_gl_interface_s.nextval,
1618 g_budget_version_id,
1619 p_budget_detail_id,
1620 p_period_name,
1621 p_accounting_date,
1622 p_code_combination_id,
1623 p_cost_allocation_keyflex_id,
1624 NVL(l_amount_dr,0),
1625 NVL(l_amount_cr,0),
1626 p_currency_code,
1627 null,
1628 'Y',
1629 null,
1630 'BUDGET'
1631 );
1632
1633 END IF; -- create i.e insert a adjustment record ONLY if l_amount_diff <> 0
1634
1635
1636 hr_utility.set_location('Leaving:'||l_proc, 1000);
1637
1638 EXCEPTION
1639 WHEN OTHERS THEN
1640 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1641 hr_utility.set_message_token('ROUTINE', l_proc);
1642 hr_utility.set_message_token('REASON', SQLERRM);
1643 hr_utility.raise_error;
1644 END update_pqh_gl_interface;
1645
1646 -- ----------------------------------------------------------------------------
1647 PROCEDURE populate_gl_tables
1648 IS
1649 /*
1650 This procedure will pick records from pqh_gl_interface table and insert them into
1651 gl tables depending on the g_budgetary_control_flag
1652 If we insert into gl_bc_packets do funds checking for each packet
1653 */
1654 --
1655 -- local variables
1656 --
1657 l_proc varchar2(72) := g_package||'.populate_gl_tables';
1658 l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
1659 l_period_name pqh_gl_interface.period_name%TYPE;
1660 l_accounting_date pqh_gl_interface.accounting_date%TYPE;
1661 l_code_combination_id pqh_gl_interface.code_combination_id%TYPE;
1662 l_cost_allocation_keyflex_id pqh_gl_interface.cost_allocation_keyflex_id%TYPE;
1663 l_currency_code pqh_gl_interface.currency_code%TYPE;
1664 l_amount_dr pqh_gl_interface.amount_dr%TYPE;
1665 l_amount_cr pqh_gl_interface.amount_cr%TYPE;
1666 l_packet_id gl_bc_packets.packet_id%TYPE;
1667 l_gl_period_statuses_rec gl_period_statuses%ROWTYPE;
1668 l_fc_success boolean;
1669 l_fc_return varchar2(100);
1670 l_fc_mode varchar2(100);
1671 l_fc_message varchar2(8000);
1672 l_log_context varchar2(255);
1673 l_packet_result_code varchar2(255);
1674 l_packet_status_code varchar2(255);
1675
1676 CURSOR csr_pqh_gl_interface IS
1677 SELECT period_name, accounting_date,
1678 code_combination_id, cost_allocation_keyflex_id, currency_code,
1679 SUM(NVL(amount_dr,0)) amount_dr,
1680 SUM(NVL(amount_cr,0)) amount_cr
1681 FROM pqh_gl_interface
1682 WHERE budget_version_id IN (g_budget_version_id, NVL(g_last_posted_ver,0) )
1683 AND status IS NULL
1684 AND posting_date IS NULL
1685 AND posting_type_cd = 'BUDGET'
1686 AND cost_allocation_keyflex_id is NOT NULL
1687 GROUP BY period_name, accounting_date,code_combination_id,
1688 cost_allocation_keyflex_id,currency_code;
1689
1690 CURSOR csr_packet_id IS
1691 SELECT gl_bc_packets_s.nextval
1692 FROM dual;
1693
1694 CURSOR csr_period_name( p_period_name IN varchar2 ) IS
1695 SELECT *
1696 FROM gl_period_statuses
1697 WHERE application_id = g_application_id
1698 AND set_of_books_id = g_set_of_books_id
1699 AND period_name = p_period_name;
1700
1701 CURSOR csr_gl_lookups(p_lookup_code IN varchar2 ) IS
1702 SELECT description
1703 FROM gl_lookups
1704 WHERE lookup_type = 'FUNDS_CHECK_RESULT_CODE'
1705 AND lookup_code = p_lookup_code
1706 AND NVL(enabled_flag,'N') = 'Y';
1707
1708 CURSOR csr_gl_packet_code(p_packet_id IN number ) IS
1709 SELECT result_code
1710 FROM gl_bc_packets
1711 WHERE packet_id = p_packet_id;
1712
1713 CURSOR csr_gl_status(p_lookup_code IN varchar2 ) IS
1714 SELECT description
1715 FROM gl_lookups
1716 WHERE lookup_type = 'FUNDS_CHECK_STATUS_CODE'
1717 AND lookup_code = p_lookup_code
1718 AND NVL(enabled_flag,'N') = 'Y';
1719
1720 BEGIN
1721
1722 hr_utility.set_location('Entering: '||l_proc, 5);
1723
1724 IF g_budgetary_control_flag = 'Y' THEN
1725 -- insert into gl_bc_packets and do funds checking for each packet
1726
1727 hr_utility.set_location('Inserting into GL_BC_PACKETS',10);
1728
1729 OPEN csr_pqh_gl_interface;
1730 LOOP
1731 FETCH csr_pqh_gl_interface INTO l_period_name, l_accounting_date,
1732 l_code_combination_id, l_cost_allocation_keyflex_id, l_currency_code,
1733 l_amount_dr, l_amount_cr;
1734 EXIT WHEN csr_pqh_gl_interface%NOTFOUND;
1735
1736 -- Get Packet ID
1737 OPEN csr_packet_id;
1738 FETCH csr_packet_id INTO l_packet_id;
1739 CLOSE csr_packet_id;
1740
1741 -- get period details
1742 OPEN csr_period_name(p_period_name => l_period_name);
1743 FETCH csr_period_name INTO l_gl_period_statuses_rec;
1744 CLOSE csr_period_name;
1745
1746 -- compute the GL funds checker Mode
1747 IF g_validate THEN
1748 -- this is validate ONLY mode
1749 l_fc_mode := 'C';
1750 ELSIF NVL(l_amount_dr,0) > 0 THEN
1751 -- this is debit so run fund checker in reserved mode
1752 l_fc_mode := 'P'; -- Changed from 'R' to 'P' for 4554281
1753 ELSE
1754 -- this is credit so run fund checker in unreserved mode
1755 l_fc_mode := 'U';
1756 END IF;
1757 /*
1758 ------------------------------------------------------------------------------------------------
1759 Insert in gl_bc_packets and Call the GL funds checker
1760 The GL funds checker program has COMMIT inside the program so we cannot rollback
1761 The GL funds checker is only called when the validate flag is false i.e no validation
1762 -- do funds checking for each packet
1763 -- Mode = R (reserved) if amount is dr
1764 -- Mode = U (unreserved) if amount is cr
1765 -- Mode = C (Checking) if program is run in validate mode i.e g_validate = TRUE
1766 -- Mode C is never called as there as explicit commits in GL funds checker program , so
1767 -- we call the GL funds checker program only when p_validate is FALSE in R or U mode
1768 ------------------------------------------------------------------------------------------------
1769 */
1770 -- Insert in gl_bc_packets and run funds checker
1771 hr_utility.set_location('Calling ins_gl_bc_run_fund_check with fund checker Mode : '||l_fc_mode,100);
1772
1773 ins_gl_bc_run_fund_check
1774 ( p_packet_id => l_packet_id
1775 ,p_code_combination_id => l_code_combination_id
1776 ,p_period_name => l_period_name
1777 ,p_period_year => l_gl_period_statuses_rec.period_year
1778 ,p_period_num => l_gl_period_statuses_rec.period_num
1779 ,p_quarter_num => l_gl_period_statuses_rec.quarter_num
1780 ,p_currency_code => l_currency_code
1781 ,p_entered_dr => NVL(l_amount_dr,0)
1782 ,p_entered_cr => NVL(l_amount_cr,0)
1783 ,p_accounted_dr => NVL(l_amount_dr,0)
1784 ,p_accounted_cr => NVL(l_amount_cr,0)
1785 ,p_cost_allocation_keyflex_id => l_cost_allocation_keyflex_id
1786 ,p_fc_mode => l_fc_mode
1787 ,p_fc_success => l_fc_success
1788 ,p_fc_return => l_fc_return
1789 );
1790
1791 hr_utility.set_location('GL Fund Checker return Code : '||l_fc_return,110);
1792
1793 -- get the return code desc from GL lookups
1794 OPEN csr_gl_status(p_lookup_code => l_fc_return);
1795 FETCH csr_gl_status INTO l_packet_status_code;
1796 CLOSE csr_gl_status;
1797
1798 hr_utility.set_location('GL Fund Checker return Code Desc : '||l_packet_status_code,111);
1799
1800 /*
1801
1802 -----------------------------------------------------------------------------------------------------
1803 If the fund checker program failed i.e l_fc_success = FALSE or l_fc_return in ('T', 'F','R') then we
1804 would do the following :
1805
1806 1. Put the error message in pqh_process_log ( context : Period Name + CCID + currency code )
1807
1808 2. update all the budget_detail records which have this Period Name + CCID + currency code to ERROR ( gl_status)
1809
1810 3. Reverse unposted adjustment txns in pqh_gl_interface
1811
1812 4. Delete all unposted non-adjustment txns from pqh_gl_interface
1813 -----------------------------------------------------------------------------------------------------
1814
1815 */
1816
1817 IF NOT ( l_fc_success ) OR
1818 ( NVL(l_fc_return,'T') in ('T', 'F','R') ) THEN
1819 -- fund checker failed
1820
1821 hr_utility.set_location('Fund Checker Failed ',120);
1822
1823 -----------------------------------------------------------------
1824 -- STEP 1: Log the Error Message
1825 -----------------------------------------------------------------
1826 -- get the error message which is populated in case of fatal error i.e l_fc_return = T
1827 l_fc_message := fnd_message.get;
1828
1829 -- if the above error message is null then get from result code
1830 IF l_fc_message IS NULL THEN
1831 OPEN csr_gl_packet_code(p_packet_id => l_packet_id);
1832 FETCH csr_gl_packet_code INTO l_packet_result_code;
1833 CLOSE csr_gl_packet_code;
1834
1835 OPEN csr_gl_lookups(p_lookup_code => l_packet_result_code);
1836 FETCH csr_gl_lookups INTO l_fc_message;
1837 CLOSE csr_gl_lookups;
1838 END IF;
1839
1840 hr_utility.set_location('Fund Chk Error : '||substr(l_fc_message,1,50),120);
1841
1842 -- set the log context and insert into log
1843 l_log_context := l_period_name||' - '||l_code_combination_id||' - '||l_currency_code;
1844
1845 hr_utility.set_location('Log Context : '||l_log_context,130);
1846
1847 -- set the context
1848 pqh_process_batch_log.set_context_level
1849 (
1850 p_txn_id => l_packet_id,
1851 p_txn_table_route_id => g_table_route_id_glf,
1852 p_level => 1,
1853 p_log_context => l_log_context
1854 );
1855
1856 -- insert error
1857 pqh_process_batch_log.insert_log
1858 (
1859 p_message_type_cd => 'ERROR',
1860 p_message_text => l_packet_status_code||' : '||l_fc_message
1861 );
1862
1863
1864 hr_utility.set_location('Inserted Error and calling reverse txn ',140);
1865
1866 -----------------------------------------------------------------
1867 -- STEP 2 ,3 , 4
1868 -----------------------------------------------------------------
1869 reverse_budget_details
1870 (
1871 p_period_name => l_period_name ,
1872 p_currency_code => l_currency_code ,
1873 p_code_combination_id => l_code_combination_id
1874 );
1875 -----------------------------------------------------------------
1876
1877
1878
1879 END IF; -- Fund checker Error
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889 END LOOP;
1890 CLOSE csr_pqh_gl_interface;
1891
1892 ELSE
1893 -- insert into gl_interface
1894 hr_utility.set_location('Inserting into GL_INTERFACE',200);
1895
1896 OPEN csr_pqh_gl_interface;
1897 LOOP
1898 FETCH csr_pqh_gl_interface INTO l_period_name, l_accounting_date,
1899 l_code_combination_id, l_cost_allocation_keyflex_id, l_currency_code,
1900 l_amount_dr, l_amount_cr;
1901 EXIT WHEN csr_pqh_gl_interface%NOTFOUND;
1902
1903 INSERT INTO gl_interface
1904 (status,
1905 set_of_books_id,
1906 user_je_source_name,
1907 user_je_category_name,
1908 currency_code,
1909 date_created,
1910 created_by,
1911 actual_flag,
1912 budget_version_id,
1913 accounting_date,
1914 period_name,
1915 code_combination_id,
1916 chart_of_accounts_id,
1917 entered_dr,
1918 entered_cr,
1919 reference1,
1920 reference2)
1921 VALUES
1922 ('NEW',
1923 g_set_of_books_id,
1924 g_user_je_source_name,
1925 g_user_je_category_name,
1926 l_currency_code,
1927 sysdate,
1928 8302,
1929 'B',
1930 g_gl_budget_version_id,
1931 l_accounting_date,
1932 l_period_name,
1933 l_code_combination_id,
1934 g_chart_of_accounts_id,
1935 NVL(l_amount_dr,0),
1936 NVL(l_amount_cr,0),
1937 g_budget_version_id,
1938 l_cost_allocation_keyflex_id);
1939
1940 END LOOP;
1941 CLOSE csr_pqh_gl_interface;
1942
1943 END IF;
1944
1945
1946
1947
1948 hr_utility.set_location('Leaving:'||l_proc, 1000);
1949
1950 EXCEPTION
1951 WHEN OTHERS THEN
1952 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
1953 hr_utility.set_message_token('ROUTINE', l_proc);
1954 hr_utility.set_message_token('REASON', SQLERRM);
1955 hr_utility.raise_error;
1956 END populate_gl_tables;
1957
1958 -- ----------------------------------------------------------------------------
1959 PROCEDURE update_gl_status
1960 IS
1961 /*
1962 This procedure will update the gl_status of pqh_budget_versions, pqh_budget_details
1963 and update the pqh_gl_interface table
1964
1965 We always update the TRANSFERED_TO_GL_FLAG = Y to indicate this is the latest budget_version that is posted to GL
1966 gl_status = POST or ERROR
1967 */
1968
1969 --
1970 -- local variables
1971 --
1972 l_proc varchar2(72) := g_package||'.update_gl_status';
1973 l_budget_details_rec pqh_budget_details%ROWTYPE;
1974 l_count NUMBER;
1975
1976 CURSOR csr_budget_details IS
1977 SELECT *
1978 FROM pqh_budget_details
1979 WHERE budget_version_id = g_budget_version_id
1980 AND NVL(gl_status,'X') <> 'ERROR'
1981 FOR UPDATE OF gl_status;
1982
1983 CURSOR csr_budget_details_cnt IS
1984 SELECT COUNT(*)
1985 FROM pqh_budget_details
1986 WHERE budget_version_id = g_budget_version_id
1987 AND NVL(gl_status,'ERROR') = 'ERROR';
1988
1989 BEGIN
1990
1991 hr_utility.set_location('Entering: '||l_proc, 5);
1992
1993 -- update pqh_budget_details
1994 OPEN csr_budget_details;
1995 LOOP
1996 FETCH csr_budget_details INTO l_budget_details_rec;
1997 EXIT WHEN csr_budget_details%NOTFOUND;
1998 UPDATE pqh_budget_details
1999 SET gl_status = 'POST'
2000 WHERE CURRENT OF csr_budget_details;
2001 END LOOP;
2002 CLOSE csr_budget_details;
2003
2004 -- update pqh_budget_versions and the program out variable
2005 OPEN csr_budget_details_cnt;
2006 FETCH csr_budget_details_cnt INTO l_count;
2007 CLOSE csr_budget_details_cnt;
2008
2009 IF NVL(l_count,0) = 0 THEN
2010 -- no errors
2011 -- mvankada
2012 IF pqh_gl_posting.chk_budget_details(p_budget_version_id => g_budget_version_id ) = 'Y' THEN
2013 UPDATE pqh_budget_versions
2014 SET gl_status = 'POST',
2015 transfered_to_gl_flag = 'Y'
2016 WHERE budget_version_id = g_budget_version_id;
2017
2018 -- set the OUT variable to SUCCESS
2019 g_status := 'SUCCESS';
2020 END IF;
2021 ELSE
2022 -- there were errors in details
2023 UPDATE pqh_budget_versions
2024 SET gl_status = 'ERROR',
2025 transfered_to_gl_flag = 'Y'
2026 WHERE budget_version_id = g_budget_version_id;
2027
2028 -- set the OUT variable to ERROR
2029 g_status := 'ERROR';
2030 END IF;
2031
2032 hr_utility.set_location('Budget Details Error Count : '||l_count, 100);
2033
2034 -- mvankada
2035 IF pqh_gl_posting.chk_budget_details(p_budget_version_id => g_budget_version_id ) = 'Y' THEN
2036 -- update the pqh_gl_interface table
2037 UPDATE pqh_gl_interface
2038 SET posting_date = sysdate,
2039 status = 'POST'
2040 WHERE budget_version_id = g_budget_version_id
2041 AND posting_type_cd = 'BUDGET'
2042 AND posting_date IS NULL
2043 AND status IS NULL;
2044
2045 -- update the pqh_gl_interface table for last posted version
2046 UPDATE pqh_gl_interface
2047 SET posting_date = sysdate,
2048 status = 'POST'
2049 WHERE budget_version_id = NVL(g_last_posted_ver,0)
2050 AND posting_type_cd = 'BUDGET'
2051 AND posting_date IS NULL
2052 AND status IS NULL;
2053 END IF;
2054
2055 hr_utility.set_location('Leaving:'||l_proc, 1000);
2056
2057 EXCEPTION
2058 WHEN OTHERS THEN
2059 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2060 hr_utility.set_message_token('ROUTINE', l_proc);
2061 hr_utility.set_message_token('REASON', SQLERRM);
2062 hr_utility.raise_error;
2063 END update_gl_status;
2064
2065 -- ----------------------------------------------------------------------------
2066
2067 PROCEDURE get_gl_ccid
2068 (
2069 p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE,
2070 p_budget_period_id IN pqh_budget_periods.budget_period_id%TYPE,
2071 p_cost_allocation_keyflex_id IN pay_cost_allocation_keyflex.cost_allocation_keyflex_id%TYPE,
2072 p_code_combination_id OUT NOCOPY gl_code_combinations.code_combination_id%TYPE
2073 ) IS
2074 /*
2075 This procedure will return the code_combination_id from gl_code_combinations corresponding to the
2076 cost_allocation_keyflex_id. The mapping between the gl segments and cost_allocation_keyflex segments is
2077 stored in pqh_gl_flex_maps table corresponding to the current budget.
2078
2079 First we will check to see if there are any defaults set for cost segments and if so populate the
2080 g_seg_val_tab with the defaults. Defaults are checks at the following level :
2081 payroll 1st level , we will first initialize the g_seg_val_tab and then get the defaults if any.
2082 element_link 2nd level, if for the position and element_type_id there are any defaults in the
2083 pay_element_links table , we only assign values for segments which are not null .
2084 organization 3rd level which will override the 1st level . At this level we only assign values
2085 which are not null
2086
2087 The budget funding src level will override the above default values.
2088 AT the budget funding src level we only assign values which are not null
2089
2090 */
2091 --
2092 -- local variables
2093 --
2094 l_proc varchar2(72) := g_package||'.get_gl_ccid';
2095 l_pay_cost_allocation_rec pay_cost_allocation_keyflex%ROWTYPE;
2096 l_pqh_budget_gl_flex_maps_rec pqh_budget_gl_flex_maps%ROWTYPE;
2097 i BINARY_INTEGER :=1;
2098 l_where_str varchar2(8000) ;
2099 sql_stmt varchar2(8000) := '';
2100
2101 TYPE SegCurTyp IS REF CURSOR;
2102 seg_cv SegCurTyp;
2103 l_gl_cc_rec gl_code_combinations%ROWTYPE;
2104 l_gl_cc_count number ;
2105
2106
2107 CURSOR csr_cost_segments IS
2108 SELECT *
2109 FROM pay_cost_allocation_keyflex
2110 WHERE cost_allocation_keyflex_id = p_cost_allocation_keyflex_id;
2111
2112
2113 CURSOR csr_map_segments IS
2114 SELECT *
2115 FROM pqh_budget_gl_flex_maps
2116 WHERE budget_id = g_budget_id ;
2117
2118 BEGIN
2119
2120 hr_utility.set_location('Entering: '||l_proc, 5);
2121
2122 -- get defaults at Payroll level
2123 get_payroll_defaults
2124 (
2125 p_budget_detail_id => p_budget_detail_id
2126 );
2127
2128 -- get defaults at element_link level
2129 get_element_link_defaults
2130 (
2131 p_budget_detail_id => p_budget_detail_id,
2132 p_budget_period_id => p_budget_period_id
2133 );
2134
2135 -- get defaults at Organization level
2136 get_organization_defaults
2137 (
2138 p_budget_detail_id => p_budget_detail_id
2139 );
2140
2141
2142 --
2143 OPEN csr_cost_segments;
2144 FETCH csr_cost_segments INTO l_pay_cost_allocation_rec;
2145 CLOSE csr_cost_segments;
2146
2147 -- populate the g_seg_val_tab with the segment values
2148 -- assign only the NOT NULL segments so that defaults at Payroll and Organization are not erased
2149 --
2150 -- g_seg_val_tab will have all the 30 segment names and their values
2151 --
2152 g_seg_val_tab(1).cost_segment_name := 'SEGMENT1';
2153 g_seg_val_tab(1).segment_value := NVL(l_pay_cost_allocation_rec.segment1,g_seg_val_tab(1).segment_value);
2154 g_seg_val_tab(2).cost_segment_name := 'SEGMENT2';
2155 g_seg_val_tab(2).segment_value := NVL(l_pay_cost_allocation_rec.segment2,g_seg_val_tab(2).segment_value);
2156 g_seg_val_tab(3).cost_segment_name := 'SEGMENT3';
2157 g_seg_val_tab(3).segment_value := NVL(l_pay_cost_allocation_rec.segment3,g_seg_val_tab(3).segment_value);
2158 g_seg_val_tab(4).cost_segment_name := 'SEGMENT4';
2159 g_seg_val_tab(4).segment_value := NVL(l_pay_cost_allocation_rec.segment4,g_seg_val_tab(4).segment_value);
2160 g_seg_val_tab(5).cost_segment_name := 'SEGMENT5';
2161 g_seg_val_tab(5).segment_value := NVL(l_pay_cost_allocation_rec.segment5,g_seg_val_tab(5).segment_value);
2162 g_seg_val_tab(6).cost_segment_name := 'SEGMENT6';
2163 g_seg_val_tab(6).segment_value := NVL(l_pay_cost_allocation_rec.segment6,g_seg_val_tab(6).segment_value);
2164 g_seg_val_tab(7).cost_segment_name := 'SEGMENT7';
2165 g_seg_val_tab(7).segment_value := NVL(l_pay_cost_allocation_rec.segment7,g_seg_val_tab(7).segment_value);
2166 g_seg_val_tab(8).cost_segment_name := 'SEGMENT8';
2167 g_seg_val_tab(8).segment_value := NVL(l_pay_cost_allocation_rec.segment8,g_seg_val_tab(8).segment_value);
2168 g_seg_val_tab(9).cost_segment_name := 'SEGMENT9';
2169 g_seg_val_tab(9).segment_value := NVL(l_pay_cost_allocation_rec.segment9,g_seg_val_tab(9).segment_value);
2170 g_seg_val_tab(10).cost_segment_name := 'SEGMENT10';
2171 g_seg_val_tab(10).segment_value := NVL(l_pay_cost_allocation_rec.segment10,g_seg_val_tab(10).segment_value);
2172 g_seg_val_tab(11).cost_segment_name := 'SEGMENT11';
2173 g_seg_val_tab(11).segment_value := NVL(l_pay_cost_allocation_rec.segment11,g_seg_val_tab(11).segment_value);
2174 g_seg_val_tab(12).cost_segment_name := 'SEGMENT12';
2175 g_seg_val_tab(12).segment_value := NVL(l_pay_cost_allocation_rec.segment12,g_seg_val_tab(12).segment_value);
2176 g_seg_val_tab(13).cost_segment_name := 'SEGMENT13';
2177 g_seg_val_tab(13).segment_value := NVL(l_pay_cost_allocation_rec.segment13,g_seg_val_tab(13).segment_value);
2178 g_seg_val_tab(14).cost_segment_name := 'SEGMENT14';
2179 g_seg_val_tab(14).segment_value := NVL(l_pay_cost_allocation_rec.segment14,g_seg_val_tab(14).segment_value);
2180 g_seg_val_tab(15).cost_segment_name := 'SEGMENT15';
2181 g_seg_val_tab(15).segment_value := NVL(l_pay_cost_allocation_rec.segment15,g_seg_val_tab(15).segment_value);
2182 g_seg_val_tab(16).cost_segment_name := 'SEGMENT16';
2183 g_seg_val_tab(16).segment_value := NVL(l_pay_cost_allocation_rec.segment16,g_seg_val_tab(16).segment_value);
2184 g_seg_val_tab(17).cost_segment_name := 'SEGMENT17';
2185 g_seg_val_tab(17).segment_value := NVL(l_pay_cost_allocation_rec.segment17,g_seg_val_tab(17).segment_value);
2186 g_seg_val_tab(18).cost_segment_name := 'SEGMENT18';
2187 g_seg_val_tab(18).segment_value := NVL(l_pay_cost_allocation_rec.segment18,g_seg_val_tab(18).segment_value);
2188 g_seg_val_tab(19).cost_segment_name := 'SEGMENT19';
2189 g_seg_val_tab(19).segment_value := NVL(l_pay_cost_allocation_rec.segment19,g_seg_val_tab(19).segment_value);
2190 g_seg_val_tab(20).cost_segment_name := 'SEGMENT20';
2191 g_seg_val_tab(20).segment_value := NVL(l_pay_cost_allocation_rec.segment20,g_seg_val_tab(20).segment_value);
2192 g_seg_val_tab(21).cost_segment_name := 'SEGMENT21';
2193 g_seg_val_tab(21).segment_value := NVL(l_pay_cost_allocation_rec.segment21,g_seg_val_tab(21).segment_value);
2194 g_seg_val_tab(22).cost_segment_name := 'SEGMENT22';
2195 g_seg_val_tab(22).segment_value := NVL(l_pay_cost_allocation_rec.segment22,g_seg_val_tab(22).segment_value);
2196 g_seg_val_tab(23).cost_segment_name := 'SEGMENT23';
2197 g_seg_val_tab(23).segment_value := NVL(l_pay_cost_allocation_rec.segment23,g_seg_val_tab(23).segment_value);
2198 g_seg_val_tab(24).cost_segment_name := 'SEGMENT24';
2199 g_seg_val_tab(24).segment_value := NVL(l_pay_cost_allocation_rec.segment24,g_seg_val_tab(24).segment_value);
2200 g_seg_val_tab(25).cost_segment_name := 'SEGMENT25';
2201 g_seg_val_tab(25).segment_value := NVL(l_pay_cost_allocation_rec.segment25,g_seg_val_tab(25).segment_value);
2202 g_seg_val_tab(26).cost_segment_name := 'SEGMENT26';
2203 g_seg_val_tab(26).segment_value := NVL(l_pay_cost_allocation_rec.segment26,g_seg_val_tab(26).segment_value);
2204 g_seg_val_tab(27).cost_segment_name := 'SEGMENT27';
2205 g_seg_val_tab(27).segment_value := NVL(l_pay_cost_allocation_rec.segment27,g_seg_val_tab(27).segment_value);
2206 g_seg_val_tab(28).cost_segment_name := 'SEGMENT28';
2207 g_seg_val_tab(28).segment_value := NVL(l_pay_cost_allocation_rec.segment28,g_seg_val_tab(28).segment_value);
2208 g_seg_val_tab(29).cost_segment_name := 'SEGMENT29';
2209 g_seg_val_tab(29).segment_value := NVL(l_pay_cost_allocation_rec.segment29,g_seg_val_tab(29).segment_value);
2210 g_seg_val_tab(30).cost_segment_name := 'SEGMENT30';
2211 g_seg_val_tab(30).segment_value := NVL(l_pay_cost_allocation_rec.segment30,g_seg_val_tab(30).segment_value);
2212
2213
2214 hr_utility.set_location('Populated g_seg_val_tab ', 10);
2215
2216
2217 -- populate the g_map_tab for the current budget id
2218
2219 OPEN csr_map_segments;
2220 LOOP
2221 FETCH csr_map_segments INTO l_pqh_budget_gl_flex_maps_rec;
2222 EXIT WHEN csr_map_segments%NOTFOUND;
2223 g_map_tab(i).gl_segment_name := l_pqh_budget_gl_flex_maps_rec.GL_ACCOUNT_SEGMENT;
2224 g_map_tab(i).cost_segment_name := l_pqh_budget_gl_flex_maps_rec.PAYROLL_COST_SEGMENT;
2225 g_map_tab(i).segment_value := get_value_from_array ( p_segment_name => l_pqh_budget_gl_flex_maps_rec.PAYROLL_COST_SEGMENT);
2226
2227 hr_utility.set_location('i : '||i,11);
2228 hr_utility.set_location('gl_segment_name: '||g_map_tab(i).gl_segment_name, 15);
2229 hr_utility.set_location('cost_segment_name: '||g_map_tab(i).cost_segment_name , 20);
2230 hr_utility.set_location('segment_value: '||g_map_tab(i).segment_value, 25);
2231
2232
2233 i := i + 1;
2234
2235
2236 END LOOP;
2237 CLOSE csr_map_segments;
2238
2239 --
2240
2241 -- build the dynamic select for fetching the gl cc id
2242
2243 IF NVL(g_map_tab.COUNT,0) <> 0 THEN
2244 -- loop thru the array and get the value in column 3 corresponding to col name
2245 FOR i IN NVL(g_map_tab.FIRST,0)..NVL(g_map_tab.LAST,-1)
2246 LOOP
2247 IF NVL(l_where_str,'X') = 'X' THEN
2248 l_where_str := l_where_str ||g_map_tab(i).gl_segment_name||' = '||''''||g_map_tab(i).segment_value||''''||' ';
2249 ELSE
2250 l_where_str := l_where_str||' and '||g_map_tab(i).gl_segment_name||' = '||''''||g_map_tab(i).segment_value||''''||' ';
2251 END IF;
2252 END LOOP;
2253 END IF;
2254
2255 hr_utility.set_location('Built dynamic select ',26);
2256 --
2257 sql_stmt := 'SELECT * FROM gl_code_combinations WHERE chart_of_accounts_id = :g_chart_of_accounts_id and '||l_where_str ;
2258
2259 hr_utility.set_location('sql stmt : '||substr(sql_stmt,1,50),27);
2260 hr_utility.set_location('sql stmt : '||substr(sql_stmt,51,50),27);
2261 hr_utility.set_location('sql stmt : '||substr(sql_stmt,101,50),27);
2262 hr_utility.set_location('sql stmt : '||substr(sql_stmt,151,50),27);
2263 hr_utility.set_location('sql stmt : '||substr(sql_stmt,200,50),27);
2264
2265
2266 OPEN seg_cv FOR sql_stmt using g_chart_of_accounts_id ;
2267 FETCH seg_cv INTO l_gl_cc_rec;
2268 l_gl_cc_count := seg_cv%rowcount ;
2269 CLOSE seg_cv;
2270
2271 hr_utility.set_location('Rows returned from gl_code_combination:'||l_gl_cc_count||':',31);
2272 hr_utility.set_location('Cursor closed ',31);
2273 --
2274
2275 -- populate the out variable
2276 p_code_combination_id := l_gl_cc_rec.code_combination_id;
2277
2278 hr_utility.set_location('CC ID :'||p_code_combination_id, 100);
2279
2280
2281 hr_utility.set_location('Leaving:'||l_proc, 1000);
2282
2283 EXCEPTION
2284 WHEN OTHERS THEN
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 END get_gl_ccid;
2290
2291
2292 -- ----------------------------------------------------------------------------
2293
2294
2295 /*----------------------------------------------------------------
2296 || FUNCTION : get_value_from_array
2297 ||
2298 ------------------------------------------------------------------*/
2299
2300
2301
2302 FUNCTION get_value_from_array ( p_segment_name IN varchar2 )
2303 RETURN VARCHAR2 IS
2304 /*
2305 This function would loop thru the g_seg_val_tab and would return the segment value for the
2306 segment name given as input
2307 */
2308 --
2309 -- local variables
2310 --
2311 l_proc varchar2(72) := g_package||'.get_value_from_array';
2312 l_col_val VARCHAR2(8000);
2313
2314
2315
2316
2317 BEGIN
2318 hr_utility.set_location('Entering:'||l_proc, 5);
2319
2320 hr_utility.set_location('Seg Name : '||p_segment_name, 6);
2321
2322 IF NVL(g_seg_val_tab.COUNT,0) <> 0 THEN
2323 -- loop thru the array and get the segment value
2324 FOR i IN NVL(g_seg_val_tab.FIRST,0)..NVL(g_seg_val_tab.LAST,-1)
2325 LOOP
2326 IF UPPER(g_seg_val_tab(i).cost_segment_name) = UPPER(p_segment_name) THEN
2327 l_col_val := g_seg_val_tab(i).segment_value;
2328 EXIT; -- exit the loop as the column is found
2329 END IF;
2330 END LOOP;
2331 END IF;
2332
2333
2334 l_col_val := l_col_val;
2335
2336 hr_utility.set_location('Col Val : '||l_col_val, 10);
2337 hr_utility.set_location('Leaving:'||l_proc, 1000);
2338
2339 return l_col_val;
2340
2341 EXCEPTION
2342 WHEN OTHERS THEN
2343 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2344 hr_utility.set_message_token('ROUTINE', l_proc);
2345 hr_utility.set_message_token('REASON', SQLERRM);
2346 hr_utility.raise_error;
2347 END get_value_from_array;
2348
2349
2350
2351 -- ----------------------------------------------------------------------------
2352
2353
2354 PROCEDURE get_gl_period
2355 (
2356 p_budget_period_id IN pqh_budget_periods.budget_period_id%TYPE,
2357 p_gl_period_statuses_rec OUT NOCOPY gl_period_statuses%ROWTYPE
2358 ) IS
2359 /*
2360 This procedure will return the period name corresponding to start_date between
2361 gl_period_statuses.start_date and gl_period_statuses.end_date
2362 */
2363 --
2364 -- local variables
2365 --
2366 l_proc varchar2(72) := g_package||'.get_gl_period';
2367 l_start_date DATE;
2368 l_period_name gl_period_statuses.period_name%TYPE;
2369 l_accounting_date DATE;
2370 l_gl_period_statuses_rec gl_period_statuses%ROWTYPE;
2371
2372
2373 CURSOR csr_time_period IS
2374 SELECT start_date
2375 FROM per_time_periods
2376 WHERE time_period_id = ( SELECT start_time_period_id
2377 FROM pqh_budget_periods
2378 WHERE budget_period_id = p_budget_period_id );
2379
2380 CURSOR csr_period_name( p_start_date IN DATE ) IS
2381 SELECT *
2382 FROM gl_period_statuses
2383 WHERE application_id = g_application_id
2384 AND set_of_books_id = g_set_of_books_id
2385 AND closing_status = 'O'
2386 AND p_start_date BETWEEN start_date AND end_date;
2387
2388 BEGIN
2389 hr_utility.set_location('Entering:'||l_proc, 5);
2390
2391 -- get the budget start date
2392 OPEN csr_time_period;
2393 FETCH csr_time_period INTO l_start_date;
2394 CLOSE csr_time_period;
2395
2396 hr_utility.set_location('Budget Start Date : '||l_start_date,10);
2397
2398 -- get the period name and accounting date
2399 OPEN csr_period_name( p_start_date => l_start_date);
2400 FETCH csr_period_name INTO l_gl_period_statuses_rec;
2401 CLOSE csr_period_name;
2402
2403
2404
2405 p_gl_period_statuses_rec := l_gl_period_statuses_rec;
2406
2407 hr_utility.set_location('Period Name : '||l_gl_period_statuses_rec.period_name,20);
2408
2409 hr_utility.set_location('Leaving:'||l_proc, 1000);
2410
2411 EXCEPTION
2412 WHEN OTHERS THEN
2413 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2414 hr_utility.set_message_token('ROUTINE', l_proc);
2415 hr_utility.set_message_token('REASON', SQLERRM);
2416 hr_utility.raise_error;
2417 END get_gl_period;
2418
2419 -- ----------------------------------------------------------------------------
2420 FUNCTION get_amt1 ( p_budget_fund_src_id IN pqh_budget_fund_srcs.budget_fund_src_id%TYPE )
2421 RETURN NUMBER IS
2422 /*
2423 This function will copmute the amout1 for UOM1
2424 */
2425
2426
2427 l_proc varchar2(72) := g_package||'.get_amt1';
2428 l_budget_fund_srcs_rec pqh_budget_fund_srcs%ROWTYPE;
2429 l_budget_elements_rec pqh_budget_elements%ROWTYPE;
2430 l_budget_sets_rec pqh_budget_sets%ROWTYPE;
2431 l_amount1 pqh_budget_sets.budget_unit1_value%TYPE;
2432
2433 CURSOR csr_bdgt_amt IS
2434 SELECT (NVL(srcs.distribution_percentage,0) * .01) *
2435 (NVL(elem.distribution_percentage,0) * .01) *
2436 NVL(sets.budget_unit1_value,0)
2437 FROM pqh_budget_fund_srcs srcs,
2438 pqh_budget_elements elem,
2439 pqh_budget_sets sets
2440 WHERE srcs.budget_fund_src_id = p_budget_fund_src_id
2441 AND elem.budget_element_id = srcs.budget_element_id
2442 AND sets.budget_set_id = elem.budget_set_id;
2443
2444 BEGIN
2445 hr_utility.set_location('Entering:'||l_proc, 5);
2446
2447 OPEN csr_bdgt_amt;
2448 FETCH csr_bdgt_amt INTO l_amount1;
2449 if csr_bdgt_amt%NotFound then
2450 l_amount1 := 0;
2451 end if;
2452 CLOSE csr_bdgt_amt;
2453
2454 hr_utility.set_location('Amount1 is : '||l_amount1, 10);
2455
2456 hr_utility.set_location('Leaving:'||l_proc, 1000);
2457
2458 RETURN l_amount1;
2459
2460 EXCEPTION
2461 WHEN OTHERS THEN
2462 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2463 hr_utility.set_message_token('ROUTINE', l_proc);
2464 hr_utility.set_message_token('REASON', SQLERRM);
2465 hr_utility.raise_error;
2466 END get_amt1;
2467
2468 -- ----------------------------------------------------------------------------
2469 FUNCTION get_amt2 ( p_budget_fund_src_id IN pqh_budget_fund_srcs.budget_fund_src_id%TYPE )
2470 RETURN NUMBER IS
2471 /*
2472 This function will copmute the amout1 for UOM2
2473 */
2474
2475
2476 l_proc varchar2(72) := g_package||'.get_amt2';
2477 l_budget_fund_srcs_rec pqh_budget_fund_srcs%ROWTYPE;
2478 l_budget_elements_rec pqh_budget_elements%ROWTYPE;
2479 l_budget_sets_rec pqh_budget_sets%ROWTYPE;
2480 l_amount2 pqh_budget_sets.budget_unit2_value%TYPE;
2481
2482 CURSOR csr_bdgt_amt IS
2483 SELECT (NVL(srcs.distribution_percentage,0) * .01) *
2484 (NVL(elem.distribution_percentage,0) * .01) *
2485 NVL(sets.budget_unit2_value,0)
2486 FROM pqh_budget_fund_srcs srcs,
2487 pqh_budget_elements elem,
2488 pqh_budget_sets sets
2489 WHERE srcs.budget_fund_src_id = p_budget_fund_src_id
2490 AND elem.budget_element_id = srcs.budget_element_id
2491 AND sets.budget_set_id = elem.budget_set_id;
2492
2493 BEGIN
2494 hr_utility.set_location('Entering:'||l_proc, 5);
2495
2496 OPEN csr_bdgt_amt;
2497 FETCH csr_bdgt_amt INTO l_amount2;
2498 if csr_bdgt_amt%NotFound then
2499 l_amount2 := 0;
2500 end if;
2501 CLOSE csr_bdgt_amt;
2502
2503 hr_utility.set_location('Amount2 is : '||l_amount2, 10);
2504
2505 hr_utility.set_location('Leaving:'||l_proc, 1000);
2506
2507 RETURN l_amount2;
2508
2509 EXCEPTION
2510 WHEN OTHERS THEN
2511 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2512 hr_utility.set_message_token('ROUTINE', l_proc);
2513 hr_utility.set_message_token('REASON', SQLERRM);
2514 hr_utility.raise_error;
2515 END get_amt2;
2516
2517
2518
2519 -- ----------------------------------------------------------------------------
2520 FUNCTION get_amt3 ( p_budget_fund_src_id IN pqh_budget_fund_srcs.budget_fund_src_id%TYPE )
2521 RETURN NUMBER IS
2522 /*
2523 This function will copmute the amout1 for UOM3
2524 */
2525
2526
2527 l_proc varchar2(72) := g_package||'.get_amt3';
2528 l_budget_fund_srcs_rec pqh_budget_fund_srcs%ROWTYPE;
2529 l_budget_elements_rec pqh_budget_elements%ROWTYPE;
2530 l_budget_sets_rec pqh_budget_sets%ROWTYPE;
2531 l_amount3 pqh_budget_sets.budget_unit3_value%TYPE;
2532
2533 CURSOR csr_bdgt_amt IS
2534 SELECT (NVL(srcs.distribution_percentage,0) * .01) *
2535 (NVL(elem.distribution_percentage,0) * .01) *
2536 NVL(sets.budget_unit3_value,0)
2537 FROM pqh_budget_fund_srcs srcs,
2538 pqh_budget_elements elem,
2539 pqh_budget_sets sets
2540 WHERE srcs.budget_fund_src_id = p_budget_fund_src_id
2541 AND elem.budget_element_id = srcs.budget_element_id
2542 AND sets.budget_set_id = elem.budget_set_id;
2543
2544 BEGIN
2545 hr_utility.set_location('Entering:'||l_proc, 5);
2546
2547 OPEN csr_bdgt_amt;
2548 FETCH csr_bdgt_amt INTO l_amount3;
2549 if csr_bdgt_amt%NotFound then
2550 l_amount3 := 0;
2551 end if;
2552 CLOSE csr_bdgt_amt;
2553
2554
2555 hr_utility.set_location('Amount3 is : '||l_amount3, 10);
2556
2557 hr_utility.set_location('Leaving:'||l_proc, 1000);
2558
2559 RETURN l_amount3;
2560
2561 EXCEPTION
2562 WHEN OTHERS THEN
2563 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2564 hr_utility.set_message_token('ROUTINE', l_proc);
2565 hr_utility.set_message_token('REASON', SQLERRM);
2566 hr_utility.raise_error;
2567 END get_amt3;
2568
2569 -- ----------------------------------------------------------------------------
2570 -- ----------------------------------------------------------------------------
2571 PROCEDURE set_bdt_log_context
2572 (
2573 p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE,
2574 p_log_context OUT NOCOPY pqh_process_log.log_context%TYPE
2575 ) IS
2576
2577 /*
2578 This procedure will set the log_context at Budget detail level
2579
2580 Budgeted Record -> Display name of Primary Budgeted Entity
2581 OPEN -> Display Order is P J O G ( which ever is not null
2582
2583 */
2584
2585 l_proc varchar2(72) := g_package||'set_bdt_log_context';
2586 l_budget_details_rec pqh_budget_details%ROWTYPE;
2587 l_position_name hr_positions.name%TYPE;
2588 l_job_name per_jobs.name%TYPE;
2589 l_organization_name hr_organization_units.name%TYPE;
2590 l_grade_name per_grades.name%TYPE;
2591
2592 CURSOR csr_bdt_detail_rec IS
2593 SELECT *
2594 FROM pqh_budget_details
2595 WHERE budget_detail_id = p_budget_detail_id ;
2596
2597 BEGIN
2598
2599 hr_utility.set_location('Entering:'||l_proc, 5);
2600
2601 OPEN csr_bdt_detail_rec;
2602 FETCH csr_bdt_detail_rec INTO l_budget_details_rec;
2603 CLOSE csr_bdt_detail_rec;
2604
2605
2606
2607 -- this is budgeted record , display Primary Budgeted Entity
2608 IF NVL(g_budgeted_entity_cd ,'OPEN') = 'POSITION' THEN
2609 l_position_name := HR_GENERAL.DECODE_POSITION (
2610 p_position_id => l_budget_details_rec.position_id);
2611 p_log_context := SUBSTR(l_position_name,1,255);
2612 ELSIF NVL(g_budgeted_entity_cd ,'OPEN') = 'JOB' THEN
2613 l_job_name := HR_GENERAL.DECODE_JOB (
2614 p_job_id => l_budget_details_rec.job_id);
2615 p_log_context := SUBSTR(l_job_name,1,255);
2616 ELSIF NVL(g_budgeted_entity_cd ,'OPEN') = 'ORGANIZATION' THEN
2617 l_organization_name := HR_GENERAL.DECODE_ORGANIZATION (
2618 p_organization_id => l_budget_details_rec.organization_id);
2619 p_log_context := SUBSTR(l_organization_name,1,255);
2620 ELSIF NVL(g_budgeted_entity_cd ,'OPEN') = 'GRADE' THEN
2621 l_grade_name := HR_GENERAL.DECODE_GRADE (
2622 p_grade_id => l_budget_details_rec.grade_id);
2623 p_log_context := SUBSTR(l_grade_name,1,255);
2624 ELSIF NVL(g_budgeted_entity_cd ,'OPEN') = 'OPEN' THEN
2625 l_position_name := HR_GENERAL.DECODE_POSITION (
2626 p_position_id => l_budget_details_rec.position_id);
2627 l_job_name := HR_GENERAL.DECODE_JOB (
2628 p_job_id => l_budget_details_rec.job_id);
2629 l_organization_name := HR_GENERAL.DECODE_ORGANIZATION (
2630 p_organization_id => l_budget_details_rec.organization_id);
2631 l_grade_name := HR_GENERAL.DECODE_GRADE (
2632 p_grade_id => l_budget_details_rec.grade_id);
2633
2634 IF l_position_name IS NOT NULL THEN
2635 p_log_context := SUBSTR(l_position_name,1,255);
2636 ELSIF l_job_name IS NOT NULL THEN
2637 p_log_context := SUBSTR(l_job_name,1,255);
2638 ELSIF l_organization_name IS NOT NULL THEN
2639 p_log_context := SUBSTR(l_organization_name,1,255);
2640 ELSIF l_grade_name IS NOT NULL THEN
2641 p_log_context := SUBSTR(l_grade_name,1,255);
2642 END IF;
2643
2644 END IF;
2645
2646
2647
2648
2649
2650
2651 hr_utility.set_location('Log Context : '||p_log_context, 100);
2652
2653
2654
2655 hr_utility.set_location('Leaving:'||l_proc, 1000);
2656
2657 EXCEPTION
2658 WHEN OTHERS THEN
2659 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2660 hr_utility.set_message_token('ROUTINE', l_proc);
2661 hr_utility.set_message_token('REASON', SQLERRM);
2662 -- end log and halt the program here
2663 raise g_error_exception;
2664 END set_bdt_log_context;
2665 --------------------------------------------------------------------------------------------------------------
2666 PROCEDURE set_bpr_log_context
2667 (
2668 p_budget_period_id IN pqh_budget_periods.budget_period_id%TYPE,
2669 p_log_context OUT NOCOPY pqh_process_log.log_context%TYPE
2670 ) IS
2671 /*
2672 This procedure will set the log_context at bpr periods level
2673
2674 Display the period start date for start_time_period_id and
2675 Display the period end date for end_time_period_id
2676 Table : per_time_periods
2677
2678 */
2679
2680 l_proc varchar2(72) := g_package||'set_bpr_log_context';
2681 l_budget_periods_rec pqh_budget_periods%ROWTYPE;
2682 l_per_time_periods per_time_periods%ROWTYPE;
2683 l_start_date per_time_periods.start_date%TYPE;
2684 l_end_date per_time_periods.end_date%TYPE;
2685
2686 CURSOR csr_bpr_periods_rec IS
2687 SELECT *
2688 FROM pqh_budget_periods
2689 WHERE budget_period_id = p_budget_period_id ;
2690
2691 CURSOR csr_per_time_periods ( p_time_period_id IN number ) IS
2692 SELECT *
2693 FROM per_time_periods
2694 WHERE time_period_id = p_time_period_id ;
2695
2696 BEGIN
2697
2698 hr_utility.set_location('Entering:'||l_proc, 5);
2699
2700 OPEN csr_bpr_periods_rec;
2701 FETCH csr_bpr_periods_rec INTO l_budget_periods_rec;
2702 CLOSE csr_bpr_periods_rec;
2703
2704 -- get the start date
2705 OPEN csr_per_time_periods ( p_time_period_id => l_budget_periods_rec.start_time_period_id);
2706 FETCH csr_per_time_periods INTO l_per_time_periods;
2707 CLOSE csr_per_time_periods;
2708
2709 l_start_date := l_per_time_periods.start_date;
2710
2711
2712 -- get the end date
2713
2714 OPEN csr_per_time_periods ( p_time_period_id => l_budget_periods_rec.end_time_period_id);
2715 FETCH csr_per_time_periods INTO l_per_time_periods;
2716 CLOSE csr_per_time_periods;
2717
2718 l_end_date := l_per_time_periods.end_date;
2719
2720 -- set log context
2721
2722 p_log_context := l_start_date||' - '||l_end_date;
2723
2724
2725
2726 hr_utility.set_location('Log Context : '||p_log_context, 101);
2727 hr_utility.set_location('Leaving:'||l_proc, 1000);
2728
2729 EXCEPTION
2730 WHEN OTHERS THEN
2731 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2732 hr_utility.set_message_token('ROUTINE', l_proc);
2733 hr_utility.set_message_token('REASON', SQLERRM);
2734 -- end log and halt the program here
2735 raise g_error_exception;
2736 END set_bpr_log_context;
2737 --------------------------------------------------------------------------------------------------------------
2738 PROCEDURE set_bfs_log_context
2739 (
2740 p_cost_allocation_keyflex_id IN pay_cost_allocation_keyflex.cost_allocation_keyflex_id%TYPE,
2741 p_log_context OUT NOCOPY pqh_process_log.log_context%TYPE
2742 ) IS
2743
2744 /*
2745 This procedure will set the log_context at wks budget fund srcs level
2746
2747 Display the CONCATENATED_SEGMENTS
2748 Table : pay_cost_allocation_keyflex
2749
2750 */
2751
2752 l_proc varchar2(72) := g_package||'set_bfs_log_context';
2753 l_pay_cost_allocation_kf_rec pay_cost_allocation_keyflex%ROWTYPE;
2754
2755
2756 CURSOR csr_pay_cost_allocation_kf_rec IS
2757 SELECT *
2758 FROM pay_cost_allocation_keyflex
2759 WHERE cost_allocation_keyflex_id = p_cost_allocation_keyflex_id;
2760
2761 BEGIN
2762
2763 hr_utility.set_location('Entering:'||l_proc, 5);
2764
2765 OPEN csr_pay_cost_allocation_kf_rec;
2766 FETCH csr_pay_cost_allocation_kf_rec INTO l_pay_cost_allocation_kf_rec;
2767 CLOSE csr_pay_cost_allocation_kf_rec;
2768
2769
2770 p_log_context := l_pay_cost_allocation_kf_rec.concatenated_segments;
2771
2772
2773 hr_utility.set_location('Log Context : '||p_log_context, 101);
2774 hr_utility.set_location('Leaving:'||l_proc, 1000);
2775
2776 EXCEPTION
2777 WHEN OTHERS THEN
2778 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2779 hr_utility.set_message_token('ROUTINE', l_proc);
2780 hr_utility.set_message_token('REASON', SQLERRM);
2781 -- end log and halt the program here
2782 raise g_error_exception;
2783 END set_bfs_log_context;
2784 --------------------------------------------------------------------------------------------------------------
2785
2786 PROCEDURE end_log
2787 IS
2788 /*
2789 This will update the g_status global with ERROR or SUCCESS
2790 */
2791 --
2792 -- local variables
2793 --
2794 l_proc varchar2(72) := g_package||'end_log';
2795 l_count_error NUMBER := 0;
2796 l_count_warning NUMBER := 0;
2797 l_status VARCHAR2(30);
2798 l_pqh_process_log_rec pqh_process_log%ROWTYPE;
2799 PRAGMA AUTONOMOUS_TRANSACTION;
2800
2801
2802 CURSOR csr_status (p_message_type_cd IN VARCHAR2 ) IS
2803 SELECT COUNT(*)
2804 FROM pqh_process_log
2805 WHERE message_type_cd = p_message_type_cd
2806 START WITH process_log_id = pqh_process_batch_log.g_master_process_log_id
2807 CONNECT BY PRIOR process_log_id = master_process_log_id;
2808
2809 CURSOR csr_batch_rec IS
2810 SELECT *
2811 FROM pqh_process_log
2812 WHERE process_log_id = pqh_process_batch_log.g_master_process_log_id;
2813
2814
2815 BEGIN
2816
2817 hr_utility.set_location('Entering: '||l_proc, 5);
2818
2819 /*
2820 Compute the status of the batch. If there exists any record in the batch with
2821 message_type_cd = 'ERROR' then the batch_status = 'ERROR'
2822 If there only exists records in the batch with message_type_cd = 'WARNING' then
2823 the batch_status = 'WARNING'
2824 If there are NO records in the batch with message_type_cd = 'WARNING' OR 'ERROR' then
2825 the batch_status = 'SUCCESS'
2826 */
2827
2828 OPEN csr_status(p_message_type_cd => 'ERROR');
2829 FETCH csr_status INTO l_count_error;
2830 CLOSE csr_status;
2831
2832 OPEN csr_status(p_message_type_cd => 'WARNING');
2833 FETCH csr_status INTO l_count_warning;
2834 CLOSE csr_status;
2835
2836
2837 IF l_count_error <> 0 THEN
2838 -- there are one or more errors
2839 l_status := 'ERROR';
2840 g_status := 'ERROR';
2841 ELSE
2842 -- errors are 0 , check for warnings
2843 IF l_count_warning <> 0 THEN
2844 -- there are one or more warnings
2845 l_status := 'WARNING';
2846 g_status := 'ERROR';
2847 ELSE
2848 -- no errors or warnings
2849 l_status := 'SUCCESS';
2850 g_status := 'SUCCESS';
2851 END IF;
2852
2853 END IF;
2854
2855 hr_utility.set_location('Batch Status : '||l_status,100);
2856
2857 /*
2858 update the 'start' record for this batch with message_type_cd = 'COMPLETE' and
2859 update the batch_end_date with current date time
2860 */
2861
2862 OPEN csr_batch_rec;
2863 FETCH csr_batch_rec INTO l_pqh_process_log_rec;
2864 CLOSE csr_batch_rec;
2865
2866 IF l_pqh_process_log_rec.message_type_cd <> 'ERROR'THEN
2867 -- no errors in the batch
2868 UPDATE pqh_process_log
2869 SET message_type_cd = 'COMPLETE',
2870 message_text = fnd_message.get_string('PQH','PQH_PROCESS_COMPLETED'),
2871 txn_table_route_id = g_table_route_id_bvr,
2872 batch_status = l_status,
2873 batch_end_date = sysdate
2874 WHERE process_log_id = pqh_process_batch_log.g_master_process_log_id;
2875 ELSE
2876 -- there were errors in the batch header record i.e the root node
2877 -- so only update the batch status and end date
2878 UPDATE pqh_process_log
2879 SET batch_status = l_status,
2880 batch_end_date = sysdate,
2881 txn_table_route_id = g_table_route_id_bvr
2882 WHERE process_log_id = pqh_process_batch_log.g_master_process_log_id;
2883 END IF;
2884
2885
2886
2887 /*
2888 commit the autonomous transaction
2889 */
2890
2891 commit;
2892
2893 hr_utility.set_location('Leaving:'||l_proc, 1000);
2894
2895 EXCEPTION
2896 WHEN OTHERS THEN
2897 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2898 hr_utility.set_message_token('ROUTINE', l_proc);
2899 hr_utility.set_message_token('REASON', SQLERRM);
2900 hr_utility.raise_error;
2901 END end_log;
2902
2903 --------------------------------------------------------------------------------------------------------------
2904 PROCEDURE populate_globals_error
2905 (
2906 p_message_text IN pqh_process_log.message_text%TYPE
2907 ) IS
2908 /*
2909 If there are errors in populate globals procedure we will call this procedure which will
2910 end the process log as the batch itself has error
2911 */
2912 --
2913 -- local variables
2914 --
2915 l_proc varchar2(72) := g_package||'populate_globals_error';
2916 PRAGMA AUTONOMOUS_TRANSACTION;
2917
2918 BEGIN
2919
2920 hr_utility.set_location('Entering: '||l_proc, 5);
2921
2922 UPDATE pqh_process_log
2923 SET message_type_cd = 'ERROR',
2924 message_text = p_message_text,
2925 txn_table_route_id = g_table_route_id_bvr
2926 -- batch_status = 'ERROR',
2927 -- batch_end_date = sysdate
2928 WHERE process_log_id = pqh_process_batch_log.g_master_process_log_id;
2929
2930 /*
2931 commit the autonomous transaction
2932 */
2933
2934 commit;
2935
2936 hr_utility.set_location('Leaving:'||l_proc, 1000);
2937
2938 EXCEPTION
2939 WHEN OTHERS THEN
2940 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
2941 hr_utility.set_message_token('ROUTINE', l_proc);
2942 hr_utility.set_message_token('REASON', SQLERRM);
2943 hr_utility.raise_error;
2944 END populate_globals_error;
2945
2946 --------------------------------------------------------------------------------------------------------------
2947 PROCEDURE populate_budget_gl_map
2948 (
2949 p_budget_id IN pqh_budgets.budget_id%TYPE
2950 ) IS
2951 /*
2952 Called from Budget Charactaristics FORM
2953 This procedure will populate the pqh_budget_gl_flex_maps
2954 This will be called from the Budget Characteristics Form when the user presses the Map Tab
2955 This will get all the segments for the current GL Chart of Account and populate the map table
2956 This procedure will only be called if transfer to gl flag is Y and gl_set_of_books_id is not null
2957 */
2958 --
2959 -- local variables
2960 --
2961 l_proc varchar2(72) := g_package||'populate_budget_gl_map';
2962 l_budgets_rec pqh_budgets%ROWTYPE;
2963 l_sets_of_books_rec gl_sets_of_books%ROWTYPE;
2964 l_fnd_id_flex_segments fnd_id_flex_segments%ROWTYPE;
2965 l_budget_gl_flex_map_id number(15);
2966 l_object_version_number number(9);
2967
2968 CURSOR csr_budget_rec IS
2969 SELECT *
2970 FROM pqh_budgets
2971 WHERE budget_id = p_budget_id;
2972
2973 CURSOR csr_gl_sets_of_books_rec(p_set_of_books_id IN number)IS
2974 SELECT *
2975 FROM gl_sets_of_books
2976 WHERE set_of_books_id = p_set_of_books_id;
2977
2978 CURSOR csr_flex_segments (p_id_flex_num IN number)IS
2979 SELECT *
2980 FROM fnd_id_flex_segments
2981 WHERE application_id = 101
2982 AND id_flex_code = 'GL#'
2983 AND id_flex_num = p_id_flex_num
2984 AND enabled_flag = 'Y'
2985 AND display_flag = 'Y'
2986 ORDER BY application_column_name;
2987
2988 BEGIN
2989
2990 hr_utility.set_location('Entering:'||l_proc, 5);
2991
2992 -- get the set of books ID
2993 OPEN csr_budget_rec;
2994 FETCH csr_budget_rec INTO l_budgets_rec;
2995 CLOSE csr_budget_rec;
2996
2997 hr_utility.set_location('Set Of Books ID : '||l_budgets_rec.gl_set_of_books_id,10);
2998
2999 -- get the structure number of the GL ACCOUNT
3000 OPEN csr_gl_sets_of_books_rec(p_set_of_books_id => l_budgets_rec.gl_set_of_books_id);
3001 FETCH csr_gl_sets_of_books_rec INTO l_sets_of_books_rec;
3002 CLOSE csr_gl_sets_of_books_rec;
3003
3004 hr_utility.set_location('Chart of Account ID : '||l_sets_of_books_rec.chart_of_accounts_id,100);
3005
3006 -- populate the pqh_budget_gl_flex_maps with the segments
3007 OPEN csr_flex_segments (p_id_flex_num => l_sets_of_books_rec.chart_of_accounts_id );
3008 LOOP
3009 FETCH csr_flex_segments INTO l_fnd_id_flex_segments;
3010 EXIT WHEN csr_flex_segments%NOTFOUND;
3011 -- call the insert API here
3012 pqh_budget_gl_flex_maps_api.create_budget_gl_flex_map
3013 (
3014 p_validate => false
3015 ,p_budget_gl_flex_map_id => l_budget_gl_flex_map_id
3016 ,p_budget_id => p_budget_id
3017 ,p_gl_account_segment => l_fnd_id_flex_segments.application_column_name
3018 ,p_payroll_cost_segment => null
3019 ,p_object_version_number => l_object_version_number
3020 );
3021
3022 hr_utility.set_location('Segment : '||l_fnd_id_flex_segments.application_column_name, 200);
3023 hr_utility.set_location('budget_gl_flex_map_id : '||l_budget_gl_flex_map_id,250);
3024
3025 END LOOP;
3026 CLOSE csr_flex_segments;
3027
3028
3029 hr_utility.set_location('Leaving:'||l_proc, 1000);
3030
3031 EXCEPTION
3032 WHEN OTHERS THEN
3033 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3034 hr_utility.set_message_token('ROUTINE', l_proc);
3035 hr_utility.set_message_token('REASON', SQLERRM);
3036 hr_utility.raise_error;
3037 END populate_budget_gl_map;
3038
3039 --------------------------------------------------------------------------------------------------------------
3040 PROCEDURE reverse_budget_details
3041 (
3042 p_period_name IN pqh_gl_interface.period_name%TYPE,
3043 p_currency_code IN pqh_gl_interface.currency_code%TYPE,
3044 p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE
3045 ) IS
3046 /*
3047 This procedure will be called if the GL fund checker failed. This procedure will do the following :
3048
3049 1. update all the budget_detail records which have this Period Name + CCID + currency code to ERROR ( gl_status)
3050
3051 2. Reverse unposted adjustment txns in pqh_gl_interface
3052
3053 3. Delete all unposted non-adjustment txns from pqh_gl_interface
3054
3055 Note : If a budget detail record has 4 periods and there was a error in 4th period , we have no control on the 1st three
3056 as they have already been Approved by funs checker program and would have already been posted to GL.
3057
3058 */
3059
3060 --
3061 -- local variables
3062 --
3063 l_proc varchar2(72) := g_package||'reverse_budget_details';
3064 l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
3065
3066
3067 CURSOR csr_adj IS
3068 SELECT *
3069 FROM pqh_gl_interface
3070 WHERE budget_version_id = g_budget_version_id
3071 AND posting_type_cd = 'BUDGET'
3072 AND period_name = p_period_name
3073 AND currency_code = p_currency_code
3074 AND code_combination_id = p_code_combination_id
3075 AND NVL(adjustment_flag,'N') = 'Y'
3076 AND status IS NULL
3077 AND posting_date IS NULL;
3078
3079
3080 BEGIN
3081
3082 hr_utility.set_location('Entering:'||l_proc, 5);
3083
3084 -- reverse the adjustment transactions
3085 OPEN csr_adj;
3086 LOOP
3087 FETCH csr_adj INTO l_pqh_gl_interface_rec;
3088 EXIT WHEN csr_adj%NOTFOUND;
3089
3090 -- update the amount_dr for the original record
3091 UPDATE pqh_gl_interface
3092 SET amount_dr = NVL(amount_dr,0) -
3093 NVL(l_pqh_gl_interface_rec.amount_dr,0) +
3094 NVL(l_pqh_gl_interface_rec.amount_cr,0)
3095 WHERE budget_version_id = l_pqh_gl_interface_rec.budget_version_id
3096 AND budget_detail_id = l_pqh_gl_interface_rec.budget_detail_id
3097 AND posting_type_cd = 'BUDGET'
3098 AND period_name = l_pqh_gl_interface_rec.period_name
3099 AND currency_code = l_pqh_gl_interface_rec.currency_code
3100 AND code_combination_id = l_pqh_gl_interface_rec.code_combination_id
3101 AND NVL(adjustment_flag,'N') = 'N'
3102 AND status IS NOT NULL;
3103
3104 END LOOP;
3105 CLOSE csr_adj;
3106
3107 -- update the pqh_budget_details table gl_status to ERROR
3108
3109 UPDATE pqh_budget_details
3110 SET gl_status = 'ERROR'
3111 WHERE budget_detail_id IN
3112 (
3113 SELECT distinct budget_detail_id
3114 FROM pqh_gl_interface
3115 WHERE budget_version_id = g_budget_version_id
3116 AND posting_type_cd = 'BUDGET'
3117 AND period_name = p_period_name
3118 AND currency_code = p_currency_code
3119 AND code_combination_id = p_code_combination_id
3120 AND status IS NULL
3121 AND posting_date IS NULL
3122 );
3123
3124 -- delete the unposted transactions from pqh_gl_interface
3125
3126 DELETE FROM pqh_gl_interface
3127 WHERE budget_version_id = g_budget_version_id
3128 AND period_name = p_period_name
3129 AND currency_code = p_currency_code
3130 AND code_combination_id = p_code_combination_id
3131 AND posting_type_cd = 'BUDGET'
3132 AND status IS NULL
3133 AND posting_date IS NULL;
3134
3135
3136 hr_utility.set_location('Leaving:'||l_proc, 1000);
3137
3138 EXCEPTION
3139 WHEN OTHERS THEN
3140 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3141 hr_utility.set_message_token('ROUTINE', l_proc);
3142 hr_utility.set_message_token('REASON', SQLERRM);
3143 hr_utility.raise_error;
3144 END reverse_budget_details;
3145 --------------------------------------------------------------------------------------------------------------
3146 PROCEDURE build_old_bdgt_dtls_tab
3147 (
3148 p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE
3149 ) IS
3150 /*
3151 This procedure will check if the current budget_detail_id was already posted (exists in pqh_gl_interface)
3152 If Yes, it would build a pl/sql table with all records which have this current budget_detail_id.
3153 This is done as the user might have changed the records with current budget_detail_id which were previously
3154 posted and not present in new records. For those records we need to unpost i.e reverse the transactions.
3155
3156 Consider the following example :
3157
3158 <----------- Old ------------------------> <------------- New ------>
3159 Budget_detail_id Period CCID Cur Amt Period CCID Cur Amt
3160 /PTAEO /PTAEO
3161 1 1 1 US 100 (reverse) 1 1 UK 100 ( new )
3162 2 2 US 100 (reverse) 6 2 US 100 ( new )
3163 3 3 US 100 (update) 3 3 US 200 ( update )
3164 4 4 US 100 (unchanged) 4 4 US 100 ( unchanged )
3165 4 7 UK 100 ( new )
3166 7 9 US 100 ( new )
3167
3168
3169 */
3170 --
3171 -- local variables
3172 --
3173 l_proc varchar2(72) := g_package||'build_old_bdgt_dtls_tab';
3174 l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
3175 i BINARY_INTEGER :=1;
3176
3177
3178
3179 CURSOR csr_old_bdgt_dtls_rec IS
3180 SELECT *
3181 FROM pqh_gl_interface
3182 WHERE budget_version_id = g_budget_version_id
3183 AND budget_detail_id = p_budget_detail_id
3184 AND posting_type_cd = 'BUDGET'
3185 AND NVL(adjustment_flag,'N') = 'N'
3186 AND status IS NOT NULL
3187 AND posting_date IS NOT NULL
3188 AND (NVL(amount_dr,0) > 0 OR NVL(amount_cr,0) > 0 ) ;
3189
3190
3191 BEGIN
3192
3193 hr_utility.set_location('Entering:'||l_proc, 5);
3194
3195 OPEN csr_old_bdgt_dtls_rec;
3196 LOOP
3197 FETCH csr_old_bdgt_dtls_rec INTO l_pqh_gl_interface_rec;
3198 EXIT WHEN csr_old_bdgt_dtls_rec%NOTFOUND;
3199
3200 g_old_bdgt_dtls_tab(i).budget_version_id := g_budget_version_id;
3201 g_old_bdgt_dtls_tab(i).budget_detail_id := p_budget_detail_id;
3202 g_old_bdgt_dtls_tab(i).period_name := l_pqh_gl_interface_rec.period_name;
3203 g_old_bdgt_dtls_tab(i).accounting_date := l_pqh_gl_interface_rec.accounting_date;
3204 g_old_bdgt_dtls_tab(i).cost_allocation_keyflex_id := l_pqh_gl_interface_rec.cost_allocation_keyflex_id;
3205 g_old_bdgt_dtls_tab(i).code_combination_id := l_pqh_gl_interface_rec.code_combination_id;
3206 g_old_bdgt_dtls_tab(i).project_id := l_pqh_gl_interface_rec.project_id;
3207 g_old_bdgt_dtls_tab(i).task_id := l_pqh_gl_interface_rec.task_id;
3208 g_old_bdgt_dtls_tab(i).award_id := l_pqh_gl_interface_rec.award_id;
3209 g_old_bdgt_dtls_tab(i).expenditure_type := l_pqh_gl_interface_rec.expenditure_type;
3210 g_old_bdgt_dtls_tab(i).organization_id := l_pqh_gl_interface_rec.organization_id;
3211 g_old_bdgt_dtls_tab(i).currency_code := l_pqh_gl_interface_rec.currency_code;
3212 g_old_bdgt_dtls_tab(i).amount_dr := l_pqh_gl_interface_rec.amount_dr;
3213 g_old_bdgt_dtls_tab(i).amount_cr := l_pqh_gl_interface_rec.amount_cr;
3214 g_old_bdgt_dtls_tab(i).reverse_flag := 'Y';
3215
3216 i := i + 1;
3217
3218
3219 END LOOP;
3220 CLOSE csr_old_bdgt_dtls_rec;
3221
3222
3223 hr_utility.set_location('Done building - records in old are :'||NVL(g_old_bdgt_dtls_tab.COUNT,0), 100);
3224
3225
3226 hr_utility.set_location('Leaving:'||l_proc, 1000);
3227
3228 EXCEPTION
3229 WHEN OTHERS THEN
3230 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3231 hr_utility.set_message_token('ROUTINE', l_proc);
3232 hr_utility.set_message_token('REASON', SQLERRM);
3233 hr_utility.raise_error;
3234 END build_old_bdgt_dtls_tab;
3235 --------------------------------------------------------------------------------------------------------------
3236 PROCEDURE compare_old_bdgt_dtls_tab IS
3237 /*
3238 This procedure will compare the g_old_bdgt_dtls_tab with g_period_amt_tab . It will check if there are records in
3239 g_old_bdgt_dtls_tab which are not in g_period_amt_tab and update the reverse flag for those records to 'Y' so that
3240 we can reverse those records
3241 */
3242
3243 --
3244 -- local variables
3245 --
3246 l_proc varchar2(72) := g_package||'compare_old_bdgt_dtls_tab';
3247
3248 BEGIN
3249
3250 hr_utility.set_location('Entering:'||l_proc, 5);
3251
3252 -- for each record in g_old_bdgt_dtls_tab, loop thru the g_period_amt_tab to check if the record exists in g_period_amt_tab
3253 -- if yes then reverse_flag is N else update the reverse_flag in g_old_bdgt_dtls_tab to 'Y'
3254
3255 IF NVL(g_old_bdgt_dtls_tab.COUNT,0) <> 0 AND
3256 NVL(g_period_amt_tab.COUNT,0) <> 0 AND
3257 g_detail_error = 'N' THEN
3258
3259 -- for each record in old
3260 FOR i IN NVL(g_old_bdgt_dtls_tab.FIRST,0)..NVL(g_old_bdgt_dtls_tab.LAST,-1)
3261 LOOP
3262 -- loop thru the new g_period_amt_tab to check if the record exists
3263 FOR j IN NVL(g_period_amt_tab.FIRST,0)..NVL(g_period_amt_tab.LAST,-1)
3264 LOOP
3265 IF g_period_amt_tab(j).cost_allocation_keyflex_id is NOT NULL AND
3266 g_old_bdgt_dtls_tab(i).code_combination_id is NOT NULL AND
3267 g_old_bdgt_dtls_tab(i).period_name = g_period_amt_tab(j).period_name AND
3268 g_old_bdgt_dtls_tab(i).code_combination_id = g_period_amt_tab(j).code_combination_id AND
3269 g_old_bdgt_dtls_tab(i).currency_code IN (g_currency_code1 , g_currency_code2, g_currency_code3 )THEN
3270 -- record found, go to next record
3271 g_old_bdgt_dtls_tab(i).reverse_flag := 'N';
3272 exit ; -- inner loop
3273 ELSIF
3274 g_period_amt_tab(j).cost_allocation_keyflex_id is NULL AND
3275 g_old_bdgt_dtls_tab(i).code_combination_id is NULL AND
3276 g_old_bdgt_dtls_tab(i).period_name = to_char(g_period_amt_tab(j).period_id) AND
3277 g_old_bdgt_dtls_tab(i).project_id = g_period_amt_tab(j).project_id AND
3278 g_old_bdgt_dtls_tab(i).task_id = g_period_amt_tab(j).task_id AND
3279 g_old_bdgt_dtls_tab(i).award_id = g_period_amt_tab(j).award_id AND
3280 g_old_bdgt_dtls_tab(i).expenditure_type = g_period_amt_tab(j).expenditure_type AND
3281 g_old_bdgt_dtls_tab(i).organization_id = g_period_amt_tab(j).organization_id THEN
3282 -- record found, go to next record
3283 g_old_bdgt_dtls_tab(i).reverse_flag := 'N';
3284 exit ; -- inner loop
3285
3286 END IF;
3287 END LOOP; -- for the g_period_amt_tab table
3288
3289 END LOOP; -- for the old g_old_bdgt_dtls_tab table
3290
3291
3292
3293 END IF; -- if both old and new tables have records and there was no error in new table
3294
3295
3296 hr_utility.set_location('Leaving:'||l_proc, 1000);
3297
3298 EXCEPTION
3299 WHEN OTHERS THEN
3300 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3301 hr_utility.set_message_token('ROUTINE', l_proc);
3302 hr_utility.set_message_token('REASON', SQLERRM);
3303 hr_utility.raise_error;
3304 END compare_old_bdgt_dtls_tab;
3305
3306
3307 --------------------------------------------------------------------------------------------------------------
3308 PROCEDURE reverse_old_bdgt_dtls_tab
3309 (
3310 p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE
3311 ) IS
3312 /*
3313 This procedure will loop thru the g_old_bdgt_dtls_tab and generate reverse transaction for all records
3314 where reverse_flag is Y and update the posted record amount to 0
3315 */
3316 --
3317 -- local variables
3318 --
3319 l_proc varchar2(72) := g_package||'reverse_old_bdgt_dtls_tab';
3320 l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
3321
3322 CURSOR csr_pqh_gl_interface(p_period_name IN varchar2,
3323 p_code_combination_id IN number,
3324 p_currency_code IN varchar2) IS
3325 SELECT *
3326 FROM pqh_gl_interface
3327 WHERE budget_version_id = g_budget_version_id
3328 AND budget_detail_id = p_budget_detail_id
3329 AND period_name = p_period_name
3330 AND code_combination_id = p_code_combination_id
3331 AND currency_code = p_currency_code
3332 AND posting_type_cd = 'BUDGET'
3333 AND NVL(adjustment_flag,'N') = 'N'
3334 AND status IS NOT NULL
3335 AND posting_date IS NOT NULL
3336 FOR UPDATE of amount_dr;
3337
3338
3339 Cursor csr_pqh_gms_interface ( p_period_name IN varchar2,
3340 p_project_id IN NUMBER,
3341 p_task_id IN NUMBER,
3342 p_award_id IN NUMBER,
3343 p_expenditure_type IN varchar2,
3344 p_organization_id IN NUMBER) IS
3345 SELECT *
3346 FROM pqh_gl_interface
3347 WHERE budget_version_id = g_budget_version_id
3348 AND budget_detail_id = p_budget_detail_id
3349 AND period_name = p_period_name
3350 AND project_id = p_project_id
3351 AND task_id = p_task_id
3352 AND award_id = p_award_id
3353 AND expenditure_type = p_expenditure_type
3354 AND organization_id = p_organization_id
3355 AND posting_type_cd = 'BUDGET'
3356 AND NVL(adjustment_flag,'N') = 'N'
3357 AND status IS NOT NULL
3358 AND posting_date IS NOT NULL
3359 FOR UPDATE of amount_dr;
3360
3361
3362 BEGIN
3363
3364 hr_utility.set_location('Entering:'||l_proc, 5);
3365
3366 hr_utility.set_location('Budget Detail Id : '||p_budget_detail_id,6);
3367
3368 IF NVL(g_old_bdgt_dtls_tab.COUNT,0) <> 0 AND
3369 NVL(g_period_amt_tab.COUNT,0) <> 0 AND
3370 g_detail_error = 'N' THEN
3371
3372 hr_utility.set_location('Inside the if ',7);
3373
3374 -- for each record in old
3375 FOR i IN NVL(g_old_bdgt_dtls_tab.FIRST,0)..NVL(g_old_bdgt_dtls_tab.LAST,-1)
3376 LOOP
3377 IF g_old_bdgt_dtls_tab(i).reverse_flag = 'Y' THEN
3378
3379 hr_utility.set_location('Reverse flag is Y ',8);
3380 hr_utility.set_location('code_combination_id '||g_old_bdgt_dtls_tab(i).code_combination_id,8);
3381
3382 -- update the record and reverse the txn
3383 IF (g_old_bdgt_dtls_tab(i).code_combination_id is not null) THEN
3384 OPEN csr_pqh_gl_interface(p_period_name => g_old_bdgt_dtls_tab(i).period_name,
3385 p_code_combination_id => g_old_bdgt_dtls_tab(i).code_combination_id,
3386 p_currency_code => g_old_bdgt_dtls_tab(i).currency_code) ;
3387 FETCH csr_pqh_gl_interface INTO l_pqh_gl_interface_rec;
3388
3389 hr_utility.set_location('Fetched record ',10);
3390
3391 -- update the pqh_gl_interface table
3392 UPDATE pqh_gl_interface
3393 SET amount_dr = 0
3394 WHERE CURRENT OF csr_pqh_gl_interface;
3395
3396 hr_utility.set_location('Updated pqh_gl_interface ',15);
3397 hr_utility.set_location('Creating reverse txn in pqh_gl_interface ',16);
3398
3399 -- create a reverse transaction for this amount_dr
3400
3401 INSERT INTO pqh_gl_interface
3402 (
3403 gl_interface_id,
3404 budget_version_id,
3405 budget_detail_id,
3406 period_name,
3407 accounting_date,
3408 code_combination_id,
3409 cost_allocation_keyflex_id,
3410 amount_dr,
3411 amount_cr,
3412 currency_code,
3413 status,
3414 adjustment_flag,
3415 posting_date,
3416 posting_type_cd
3417 )
3418 VALUES
3419 (
3420 pqh_gl_interface_s.nextval,
3421 g_budget_version_id,
3422 p_budget_detail_id,
3423 g_old_bdgt_dtls_tab(i).period_name,
3424 g_old_bdgt_dtls_tab(i).accounting_date,
3425 g_old_bdgt_dtls_tab(i).code_combination_id,
3426 g_old_bdgt_dtls_tab(i).cost_allocation_keyflex_id,
3427 0,
3428 NVL(l_pqh_gl_interface_rec.amount_dr,0),
3429 g_old_bdgt_dtls_tab(i).currency_code,
3430 null,
3431 'Y',
3432 null,
3433 'BUDGET'
3434 );
3435
3436 hr_utility.set_location('Created a reverse txn ',20);
3437
3438 CLOSE csr_pqh_gl_interface;
3439
3440 Else
3441 OPEN csr_pqh_gms_interface ( p_period_name => g_old_bdgt_dtls_tab(i).period_name,
3442 p_project_id => g_old_bdgt_dtls_tab(i).project_id,
3443 p_task_id => g_old_bdgt_dtls_tab(i).task_id,
3444 p_award_id => g_old_bdgt_dtls_tab(i).award_id,
3445 p_expenditure_type => g_old_bdgt_dtls_tab(i).expenditure_type,
3446 p_organization_id => g_old_bdgt_dtls_tab(i).organization_id);
3447 FETCH csr_pqh_gms_interface INTO l_pqh_gl_interface_rec;
3448
3449 hr_utility.set_location('Fetched record ',10);
3450
3451 -- update the pqh_gl_interface table
3452 UPDATE pqh_gl_interface
3453 SET amount_dr = 0
3454 WHERE CURRENT OF csr_pqh_gms_interface;
3455
3456 hr_utility.set_location('Updated pqh_gl_interface ',15);
3457 hr_utility.set_location('Creating reverse txn in pqh_gl_interface ',16);
3458
3459 -- create a reverse transaction for this amount_dr
3460
3461 INSERT INTO pqh_gl_interface
3462 (
3463 gl_interface_id,
3464 budget_version_id,
3465 budget_detail_id,
3466 period_name,
3467 project_id,
3468 task_id,
3469 award_id,
3470 expenditure_type,
3471 organization_id,
3472 amount_dr,
3473 amount_cr,
3474 status,
3475 adjustment_flag,
3476 posting_date,
3477 currency_code,
3478 posting_type_cd
3479 )
3480 VALUES
3481 (
3482 pqh_gl_interface_s.nextval,
3483 g_budget_version_id,
3484 p_budget_detail_id,
3485 g_old_bdgt_dtls_tab(i).period_name,
3486 g_old_bdgt_dtls_tab(i).project_id,
3487 g_old_bdgt_dtls_tab(i).task_id,
3488 g_old_bdgt_dtls_tab(i).award_id,
3489 g_old_bdgt_dtls_tab(i).expenditure_type,
3490 g_old_bdgt_dtls_tab(i).organization_id,
3491 0,
3492 NVL(l_pqh_gl_interface_rec.amount_dr,0),
3493 null,
3494 'Y',
3495 null,
3496 g_old_bdgt_dtls_tab(i).currency_code,
3497 'BUDGET'
3498 );
3499
3500 hr_utility.set_location('Created a reverse txn ',20);
3501
3502 CLOSE csr_pqh_gms_interface;
3503
3504 END IF; -- ccid is null
3505
3506
3507 END IF; -- if the transaction reverse_flag is Y
3508 END LOOP;
3509
3510 END IF; -- if both old and new tables have records and there was no error in new table
3511
3512 hr_utility.set_location('Leaving:'||l_proc, 1000);
3513
3514 EXCEPTION
3515 WHEN OTHERS THEN
3516 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3517 hr_utility.set_message_token('ROUTINE', l_proc);
3518 hr_utility.set_message_token('REASON', SQLERRM);
3519 hr_utility.raise_error;
3520 END reverse_old_bdgt_dtls_tab;
3521 --------------------------------------------------------------------------------------------------------------
3522 PROCEDURE get_default_currency
3523 IS
3524 /*
3525 This procedure will check if the business_group has a default currency, if yes it will override the
3526 gl_sets_of_book currency code
3527 If there is a currency associated with the budget , it will override all other currencies
3528 */
3529
3530 --
3531 -- local variables
3532 --
3533 l_proc varchar2(72) := g_package||'get_default_currency';
3534
3535 l_bg_curr_code varchar2(150) := '';
3536 l_budget_curr varchar2(150) := '';
3537
3538 CURSOR csr_curr_code IS
3539 SELECT bg.ORG_INFORMATION10
3540 FROM HR_ORGANIZATION_INFORMATION bg,
3541 pqh_budgets bgt,
3542 pqh_budget_versions bvr
3543 WHERE bgt.budget_id = bvr.budget_id
3544 AND bvr.budget_version_id = g_budget_version_id
3545 AND bgt.business_group_id = bg.organization_id
3546 AND bg.ORG_INFORMATION_CONTEXT = 'Business Group Information';
3547
3548 CURSOR csr_bgt_curr IS
3549 SELECT bgt.currency_code
3550 FROM pqh_budgets bgt,
3551 pqh_budget_versions bvr
3552 WHERE bgt.budget_id = bvr.budget_id
3553 AND bvr.budget_version_id = g_budget_version_id;
3554
3555
3556 BEGIN
3557
3558 hr_utility.set_location('Entering:'||l_proc, 5);
3559
3560 OPEN csr_curr_code;
3561 FETCH csr_curr_code INTO l_bg_curr_code;
3562 CLOSE csr_curr_code;
3563
3564 hr_utility.set_location('Business Group Curr Code : '||l_bg_curr_code,6);
3565
3566 IF l_bg_curr_code IS NOT NULL THEN
3567 -- assign this to g_currency_code
3568 g_currency_code := l_bg_curr_code;
3569 END IF;
3570
3571 OPEN csr_bgt_curr;
3572 FETCH csr_bgt_curr INTO l_budget_curr;
3573 CLOSE csr_bgt_curr;
3574
3575 hr_utility.set_location('Budget Currency Code : '||l_budget_curr,7);
3576
3577 IF l_budget_curr IS NOT NULL THEN
3578 -- assign this to g_currency_code
3579 g_currency_code := l_budget_curr;
3580 END IF;
3581
3582
3583
3584 hr_utility.set_location('Leaving:'||l_proc, 1000);
3585
3586 EXCEPTION
3587 WHEN OTHERS THEN
3588 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3589 hr_utility.set_message_token('ROUTINE', l_proc);
3590 hr_utility.set_message_token('REASON', SQLERRM);
3591 hr_utility.raise_error;
3592 END get_default_currency;
3593
3594 --------------------------------------------------------------------------------------------------------------
3595
3596 PROCEDURE get_payroll_defaults
3597 (
3598 p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE
3599 ) IS
3600 /*
3601 This procedure will initialize the g_seg_val_tab table.
3602 For the about budget_detail_id ,it will check if the Position ID is not null.
3603 If the Position_id is not null, we would check if there is pay_freq_payroll_id attached to this position_id
3604 If yes , we would get the defaults for this pay_freq_payroll_id from pay_all_payrolls table and assign to
3605 g_seg_val_tab table
3606
3607 */
3608 --
3609 -- local variables
3610 --
3611 l_proc varchar2(72) := g_package||' .get_payroll_defaults';
3612
3613 l_cost_allocation_keyflex_id pay_cost_allocation_keyflex.cost_allocation_keyflex_id%TYPE;
3614 l_pay_cost_allocation_rec pay_cost_allocation_keyflex%ROWTYPE;
3615
3616 CURSOR csr_cost_allocation_keyflex_id IS
3617 SELECT cost_allocation_keyflex_id
3618 FROM PAY_ALL_PAYROLLS_F pay,
3619 FND_SESSIONS SS
3620 WHERE SS.SESSION_ID = USERENV( 'sessionid')
3621 AND PAY.EFFECTIVE_START_DATE <= ss.effective_date
3622 AND PAY.EFFECTIVE_END_DATE >= ss.effective_date
3623 AND pay.payroll_id =
3624 (SELECT pos.pay_freq_payroll_id
3625 FROM pqh_budget_details bdt,
3626 hr_all_positions_f pos,
3627 FND_SESSIONS SS
3628 WHERE bdt.budget_detail_id = p_budget_detail_id
3629 AND bdt.position_id = pos.position_id
3630 AND SS.SESSION_ID = USERENV( 'sessionid')
3631 AND POS.EFFECTIVE_START_DATE <= ss.effective_date
3632 AND POS.EFFECTIVE_END_DATE >= ss.effective_date
3633 );
3634
3635 CURSOR csr_cost_segments (p_cost_allocation_keyflex_id IN number) IS
3636 SELECT *
3637 FROM pay_cost_allocation_keyflex
3638 WHERE cost_allocation_keyflex_id = p_cost_allocation_keyflex_id;
3639
3640
3641
3642
3643 BEGIN
3644
3645 hr_utility.set_location('Entering:'||l_proc, 5);
3646
3647 -- initialize the global table g_seg_val_tab
3648 g_seg_val_tab.DELETE;
3649 -- g_seg_val_tab will have all the 30 segment names and their values
3650 g_seg_val_tab(1).cost_segment_name := 'SEGMENT1';
3651 g_seg_val_tab(1).segment_value := '';
3652 g_seg_val_tab(2).cost_segment_name := 'SEGMENT2';
3653 g_seg_val_tab(2).segment_value := '';
3654 g_seg_val_tab(3).cost_segment_name := 'SEGMENT3';
3655 g_seg_val_tab(3).segment_value := '';
3656 g_seg_val_tab(4).cost_segment_name := 'SEGMENT4';
3657 g_seg_val_tab(4).segment_value := '';
3658 g_seg_val_tab(5).cost_segment_name := 'SEGMENT5';
3659 g_seg_val_tab(5).segment_value := '';
3660 g_seg_val_tab(6).cost_segment_name := 'SEGMENT6';
3661 g_seg_val_tab(6).segment_value := '';
3662 g_seg_val_tab(7).cost_segment_name := 'SEGMENT7';
3663 g_seg_val_tab(7).segment_value := '';
3664 g_seg_val_tab(8).cost_segment_name := 'SEGMENT8';
3665 g_seg_val_tab(8).segment_value := '';
3666 g_seg_val_tab(9).cost_segment_name := 'SEGMENT9';
3667 g_seg_val_tab(9).segment_value := '';
3668 g_seg_val_tab(10).cost_segment_name := 'SEGMENT10';
3669 g_seg_val_tab(10).segment_value := '';
3670 g_seg_val_tab(11).cost_segment_name := 'SEGMENT11';
3671 g_seg_val_tab(11).segment_value := '';
3672 g_seg_val_tab(12).cost_segment_name := 'SEGMENT12';
3673 g_seg_val_tab(12).segment_value := '';
3674 g_seg_val_tab(13).cost_segment_name := 'SEGMENT13';
3675 g_seg_val_tab(13).segment_value := '';
3676 g_seg_val_tab(14).cost_segment_name := 'SEGMENT14';
3677 g_seg_val_tab(14).segment_value := '';
3678 g_seg_val_tab(15).cost_segment_name := 'SEGMENT15';
3679 g_seg_val_tab(15).segment_value := '';
3680 g_seg_val_tab(16).cost_segment_name := 'SEGMENT16';
3681 g_seg_val_tab(16).segment_value := '';
3682 g_seg_val_tab(17).cost_segment_name := 'SEGMENT17';
3683 g_seg_val_tab(17).segment_value := '';
3684 g_seg_val_tab(18).cost_segment_name := 'SEGMENT18';
3685 g_seg_val_tab(18).segment_value := '';
3686 g_seg_val_tab(19).cost_segment_name := 'SEGMENT19';
3687 g_seg_val_tab(19).segment_value := '';
3688 g_seg_val_tab(20).cost_segment_name := 'SEGMENT20';
3689 g_seg_val_tab(20).segment_value := '';
3690 g_seg_val_tab(21).cost_segment_name := 'SEGMENT21';
3691 g_seg_val_tab(21).segment_value := '';
3692 g_seg_val_tab(22).cost_segment_name := 'SEGMENT22';
3693 g_seg_val_tab(22).segment_value := '';
3694 g_seg_val_tab(23).cost_segment_name := 'SEGMENT23';
3695 g_seg_val_tab(23).segment_value := '';
3696 g_seg_val_tab(24).cost_segment_name := 'SEGMENT24';
3697 g_seg_val_tab(24).segment_value := '';
3698 g_seg_val_tab(25).cost_segment_name := 'SEGMENT25';
3699 g_seg_val_tab(25).segment_value := '';
3700 g_seg_val_tab(26).cost_segment_name := 'SEGMENT26';
3701 g_seg_val_tab(26).segment_value := '';
3702 g_seg_val_tab(27).cost_segment_name := 'SEGMENT27';
3703 g_seg_val_tab(27).segment_value := '';
3704 g_seg_val_tab(28).cost_segment_name := 'SEGMENT28';
3705 g_seg_val_tab(28).segment_value := '';
3706 g_seg_val_tab(29).cost_segment_name := 'SEGMENT29';
3707 g_seg_val_tab(29).segment_value := '';
3708 g_seg_val_tab(30).cost_segment_name := 'SEGMENT30';
3709 g_seg_val_tab(30).segment_value := '';
3710
3711 -- check if position at budget detail level has payroll which has default cost allocation
3712 OPEN csr_cost_allocation_keyflex_id;
3713 FETCH csr_cost_allocation_keyflex_id INTO l_cost_allocation_keyflex_id;
3714 CLOSE csr_cost_allocation_keyflex_id;
3715
3716 hr_utility.set_location('cost_allocation_keyflex_id : '||l_cost_allocation_keyflex_id,10);
3717
3718 --
3719 IF NVL(l_cost_allocation_keyflex_id,0) <> 0 THEN
3720 --
3721 OPEN csr_cost_segments (p_cost_allocation_keyflex_id => l_cost_allocation_keyflex_id);
3722 FETCH csr_cost_segments INTO l_pay_cost_allocation_rec;
3723 CLOSE csr_cost_segments;
3724
3725 -- assign the defaults at payroll level
3726 -- populate the g_seg_val_tab with the segment values
3727 -- g_seg_val_tab will have all the 30 segment names and their values
3728 g_seg_val_tab(1).cost_segment_name := 'SEGMENT1';
3729 g_seg_val_tab(1).segment_value := l_pay_cost_allocation_rec.segment1;
3730 g_seg_val_tab(2).cost_segment_name := 'SEGMENT2';
3731 g_seg_val_tab(2).segment_value := l_pay_cost_allocation_rec.segment2;
3732 g_seg_val_tab(3).cost_segment_name := 'SEGMENT3';
3733 g_seg_val_tab(3).segment_value := l_pay_cost_allocation_rec.segment3;
3734 g_seg_val_tab(4).cost_segment_name := 'SEGMENT4';
3735 g_seg_val_tab(4).segment_value := l_pay_cost_allocation_rec.segment4;
3736 g_seg_val_tab(5).cost_segment_name := 'SEGMENT5';
3737 g_seg_val_tab(5).segment_value := l_pay_cost_allocation_rec.segment5;
3738 g_seg_val_tab(6).cost_segment_name := 'SEGMENT6';
3739 g_seg_val_tab(6).segment_value := l_pay_cost_allocation_rec.segment6;
3740 g_seg_val_tab(7).cost_segment_name := 'SEGMENT7';
3741 g_seg_val_tab(7).segment_value := l_pay_cost_allocation_rec.segment7;
3742 g_seg_val_tab(8).cost_segment_name := 'SEGMENT8';
3743 g_seg_val_tab(8).segment_value := l_pay_cost_allocation_rec.segment8;
3744 g_seg_val_tab(9).cost_segment_name := 'SEGMENT9';
3745 g_seg_val_tab(9).segment_value := l_pay_cost_allocation_rec.segment9;
3746 g_seg_val_tab(10).cost_segment_name := 'SEGMENT10';
3747 g_seg_val_tab(10).segment_value := l_pay_cost_allocation_rec.segment10;
3748 g_seg_val_tab(11).cost_segment_name := 'SEGMENT11';
3749 g_seg_val_tab(11).segment_value := l_pay_cost_allocation_rec.segment11;
3750 g_seg_val_tab(12).cost_segment_name := 'SEGMENT12';
3751 g_seg_val_tab(12).segment_value := l_pay_cost_allocation_rec.segment12;
3752 g_seg_val_tab(13).cost_segment_name := 'SEGMENT13';
3753 g_seg_val_tab(13).segment_value := l_pay_cost_allocation_rec.segment13;
3754 g_seg_val_tab(14).cost_segment_name := 'SEGMENT14';
3755 g_seg_val_tab(14).segment_value := l_pay_cost_allocation_rec.segment14;
3756 g_seg_val_tab(15).cost_segment_name := 'SEGMENT15';
3757 g_seg_val_tab(15).segment_value := l_pay_cost_allocation_rec.segment15;
3758 g_seg_val_tab(16).cost_segment_name := 'SEGMENT16';
3759 g_seg_val_tab(16).segment_value := l_pay_cost_allocation_rec.segment16;
3760 g_seg_val_tab(17).cost_segment_name := 'SEGMENT17';
3761 g_seg_val_tab(17).segment_value := l_pay_cost_allocation_rec.segment17;
3762 g_seg_val_tab(18).cost_segment_name := 'SEGMENT18';
3763 g_seg_val_tab(18).segment_value := l_pay_cost_allocation_rec.segment18;
3764 g_seg_val_tab(19).cost_segment_name := 'SEGMENT19';
3765 g_seg_val_tab(19).segment_value := l_pay_cost_allocation_rec.segment19;
3766 g_seg_val_tab(20).cost_segment_name := 'SEGMENT20';
3767 g_seg_val_tab(20).segment_value := l_pay_cost_allocation_rec.segment20;
3768 g_seg_val_tab(21).cost_segment_name := 'SEGMENT21';
3769 g_seg_val_tab(21).segment_value := l_pay_cost_allocation_rec.segment21;
3770 g_seg_val_tab(22).cost_segment_name := 'SEGMENT22';
3771 g_seg_val_tab(22).segment_value := l_pay_cost_allocation_rec.segment22;
3772 g_seg_val_tab(23).cost_segment_name := 'SEGMENT23';
3773 g_seg_val_tab(23).segment_value := l_pay_cost_allocation_rec.segment23;
3774 g_seg_val_tab(24).cost_segment_name := 'SEGMENT24';
3775 g_seg_val_tab(24).segment_value := l_pay_cost_allocation_rec.segment24;
3776 g_seg_val_tab(25).cost_segment_name := 'SEGMENT25';
3777 g_seg_val_tab(25).segment_value := l_pay_cost_allocation_rec.segment25;
3778 g_seg_val_tab(26).cost_segment_name := 'SEGMENT26';
3779 g_seg_val_tab(26).segment_value := l_pay_cost_allocation_rec.segment26;
3780 g_seg_val_tab(27).cost_segment_name := 'SEGMENT27';
3781 g_seg_val_tab(27).segment_value := l_pay_cost_allocation_rec.segment27;
3782 g_seg_val_tab(28).cost_segment_name := 'SEGMENT28';
3783 g_seg_val_tab(28).segment_value := l_pay_cost_allocation_rec.segment28;
3784 g_seg_val_tab(29).cost_segment_name := 'SEGMENT29';
3785 g_seg_val_tab(29).segment_value := l_pay_cost_allocation_rec.segment29;
3786 g_seg_val_tab(30).cost_segment_name := 'SEGMENT30';
3787 g_seg_val_tab(30).segment_value := l_pay_cost_allocation_rec.segment30;
3788
3789
3790 END IF; -- get defaults for the payroll
3791
3792
3793
3794 hr_utility.set_location('Leaving:'||l_proc, 1000);
3795
3796 EXCEPTION
3797 WHEN OTHERS THEN
3798 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3799 hr_utility.set_message_token('ROUTINE', l_proc);
3800 hr_utility.set_message_token('REASON', SQLERRM);
3801 hr_utility.raise_error;
3802 END get_payroll_defaults;
3803
3804 --------------------------------------------------------------------------------------------------------------
3805 PROCEDURE get_element_link_defaults
3806 (
3807 p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE,
3808 p_budget_period_id IN pqh_budget_periods.budget_period_id%TYPE
3809 ) IS
3810 /*
3811 This procedure wil check if there are any defaults for the position and element in
3812 pay_element_links if the position id is not null at budget detail level. We would get the
3813 defaults from pay_element_links table and assign to g_seg_val_tab table
3814 We would only assign those segments which are not null. That way we would only override the segments
3815 which were set by payroll if the segment value at this level is not null.
3816 */
3817
3818 --
3819 -- local variables
3820 --
3821 l_proc varchar2(72) := g_package||' .get_element_link_defaults';
3822
3823 l_cost_allocation_keyflex_id pay_cost_allocation_keyflex.cost_allocation_keyflex_id%TYPE;
3824 l_pay_cost_allocation_rec pay_cost_allocation_keyflex%ROWTYPE;
3825
3826 CURSOR csr_cost_allocation_keyflex_id IS
3827 SELECT cost_allocation_keyflex_id
3828 FROM pqh_budget_details bdt, pqh_budget_periods bpr,
3829 pqh_budget_sets bst, pqh_budget_elements bel,
3830 pay_element_links pel
3831 WHERE bdt.budget_detail_id = bpr.budget_detail_id
3832 AND bpr.budget_period_id = bst.budget_period_id
3833 AND bst.budget_set_id = bel.budget_set_id
3834 AND bdt.position_id = pel.position_id
3835 AND bel.element_type_id = pel.element_type_id
3836 AND bdt.budget_detail_id = p_budget_detail_id
3837 AND bpr.budget_period_id = p_budget_period_id ;
3838
3839
3840 CURSOR csr_cost_segments (p_cost_allocation_keyflex_id IN number) IS
3841 SELECT *
3842 FROM pay_cost_allocation_keyflex
3843 WHERE cost_allocation_keyflex_id = p_cost_allocation_keyflex_id;
3844
3845
3846
3847
3848 BEGIN
3849
3850 hr_utility.set_location('Entering:'||l_proc, 5);
3851
3852 -- check if position at budget detail level has default cost allocation
3853 OPEN csr_cost_allocation_keyflex_id;
3854 FETCH csr_cost_allocation_keyflex_id INTO l_cost_allocation_keyflex_id;
3855 CLOSE csr_cost_allocation_keyflex_id;
3856
3857 hr_utility.set_location('Element Link cost_allocation_keyflex_id : '||l_cost_allocation_keyflex_id,10);
3858
3859 --
3860 --
3861 IF NVL(l_cost_allocation_keyflex_id,0) <> 0 THEN
3862 --
3863 OPEN csr_cost_segments (p_cost_allocation_keyflex_id => l_cost_allocation_keyflex_id);
3864 FETCH csr_cost_segments INTO l_pay_cost_allocation_rec;
3865 CLOSE csr_cost_segments;
3866
3867 -- assign the defaults at position and element level for only not null values so that we don't erase information
3868 -- entered at payroll level
3869 g_seg_val_tab(1).segment_value := NVL(l_pay_cost_allocation_rec.segment1,g_seg_val_tab(1).segment_value);
3870 g_seg_val_tab(2).segment_value := NVL(l_pay_cost_allocation_rec.segment2,g_seg_val_tab(2).segment_value);
3871 g_seg_val_tab(3).segment_value := NVL(l_pay_cost_allocation_rec.segment3,g_seg_val_tab(3).segment_value);
3872 g_seg_val_tab(4).segment_value := NVL(l_pay_cost_allocation_rec.segment4,g_seg_val_tab(4).segment_value);
3873 g_seg_val_tab(5).segment_value := NVL(l_pay_cost_allocation_rec.segment5,g_seg_val_tab(5).segment_value);
3874 g_seg_val_tab(6).segment_value := NVL(l_pay_cost_allocation_rec.segment6,g_seg_val_tab(6).segment_value);
3875 g_seg_val_tab(7).segment_value := NVL(l_pay_cost_allocation_rec.segment7,g_seg_val_tab(7).segment_value);
3876 g_seg_val_tab(8).segment_value := NVL(l_pay_cost_allocation_rec.segment8,g_seg_val_tab(8).segment_value);
3877 g_seg_val_tab(9).segment_value := NVL(l_pay_cost_allocation_rec.segment9,g_seg_val_tab(9).segment_value);
3878 g_seg_val_tab(10).segment_value := NVL(l_pay_cost_allocation_rec.segment10,g_seg_val_tab(10).segment_value);
3879 g_seg_val_tab(11).segment_value := NVL(l_pay_cost_allocation_rec.segment11,g_seg_val_tab(11).segment_value);
3880 g_seg_val_tab(12).segment_value := NVL(l_pay_cost_allocation_rec.segment12,g_seg_val_tab(12).segment_value);
3881 g_seg_val_tab(13).segment_value := NVL(l_pay_cost_allocation_rec.segment13,g_seg_val_tab(13).segment_value);
3882 g_seg_val_tab(14).segment_value := NVL(l_pay_cost_allocation_rec.segment14,g_seg_val_tab(14).segment_value);
3883 g_seg_val_tab(15).segment_value := NVL(l_pay_cost_allocation_rec.segment15,g_seg_val_tab(15).segment_value);
3884 g_seg_val_tab(16).segment_value := NVL(l_pay_cost_allocation_rec.segment16,g_seg_val_tab(16).segment_value);
3885 g_seg_val_tab(17).segment_value := NVL(l_pay_cost_allocation_rec.segment17,g_seg_val_tab(17).segment_value);
3886 g_seg_val_tab(18).segment_value := NVL(l_pay_cost_allocation_rec.segment18,g_seg_val_tab(18).segment_value);
3887 g_seg_val_tab(19).segment_value := NVL(l_pay_cost_allocation_rec.segment19,g_seg_val_tab(19).segment_value);
3888 g_seg_val_tab(20).segment_value := NVL(l_pay_cost_allocation_rec.segment20,g_seg_val_tab(20).segment_value);
3889 g_seg_val_tab(21).segment_value := NVL(l_pay_cost_allocation_rec.segment21,g_seg_val_tab(21).segment_value);
3890 g_seg_val_tab(22).segment_value := NVL(l_pay_cost_allocation_rec.segment22,g_seg_val_tab(22).segment_value);
3891 g_seg_val_tab(23).segment_value := NVL(l_pay_cost_allocation_rec.segment23,g_seg_val_tab(23).segment_value);
3892 g_seg_val_tab(24).segment_value := NVL(l_pay_cost_allocation_rec.segment24,g_seg_val_tab(24).segment_value);
3893 g_seg_val_tab(25).segment_value := NVL(l_pay_cost_allocation_rec.segment25,g_seg_val_tab(25).segment_value);
3894 g_seg_val_tab(26).segment_value := NVL(l_pay_cost_allocation_rec.segment26,g_seg_val_tab(26).segment_value);
3895 g_seg_val_tab(27).segment_value := NVL(l_pay_cost_allocation_rec.segment27,g_seg_val_tab(27).segment_value);
3896 g_seg_val_tab(28).segment_value := NVL(l_pay_cost_allocation_rec.segment28,g_seg_val_tab(28).segment_value);
3897 g_seg_val_tab(29).segment_value := NVL(l_pay_cost_allocation_rec.segment29,g_seg_val_tab(29).segment_value);
3898 g_seg_val_tab(30).segment_value := NVL(l_pay_cost_allocation_rec.segment30,g_seg_val_tab(30).segment_value);
3899
3900 END IF; -- defaults at position and element level
3901
3902
3903
3904
3905 hr_utility.set_location('Leaving:'||l_proc, 1000);
3906
3907
3908 EXCEPTION
3909 WHEN OTHERS THEN
3910 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
3911 hr_utility.set_message_token('ROUTINE', l_proc);
3912 hr_utility.set_message_token('REASON', SQLERRM);
3913 hr_utility.raise_error;
3914 END get_element_link_defaults;
3915
3916 --------------------------------------------------------------------------------------------------------------
3917 PROCEDURE get_organization_defaults
3918 (
3919 p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE
3920 ) IS
3921 /*
3922 For the about budget_detail_id ,it will check if the Organization ID is not null.
3923 If the Organization is not null, we would get the defaults for this Organization ID
3924 from hr_organization_units table and assign to g_seg_val_tab table
3925 We would only assign those segments which are not null. That way we would only override the segments
3926 which were set by payroll if the segment value at this level is not null.
3927
3928 */
3929 --
3930 -- local variables
3931 --
3932 l_proc varchar2(72) := g_package||' .get_organization_defaults';
3933
3934 l_cost_allocation_keyflex_id pay_cost_allocation_keyflex.cost_allocation_keyflex_id%TYPE;
3935 l_pay_cost_allocation_rec pay_cost_allocation_keyflex%ROWTYPE;
3936
3937 CURSOR csr_cost_allocation_keyflex_id IS
3938 SELECT cost_allocation_keyflex_id
3939 FROM pqh_budget_details bdt,
3940 hr_all_organization_units org
3941 WHERE bdt.budget_detail_id = p_budget_detail_id
3942 AND bdt.organization_id = org.organization_id;
3943
3944
3945 CURSOR csr_cost_segments (p_cost_allocation_keyflex_id IN number) IS
3946 SELECT *
3947 FROM pay_cost_allocation_keyflex
3948 WHERE cost_allocation_keyflex_id = p_cost_allocation_keyflex_id;
3949
3950
3951
3952
3953 BEGIN
3954
3955 hr_utility.set_location('Entering:'||l_proc, 5);
3956
3957 -- check if organization at budget detail level has default cost allocation
3958 OPEN csr_cost_allocation_keyflex_id;
3959 FETCH csr_cost_allocation_keyflex_id INTO l_cost_allocation_keyflex_id;
3960 CLOSE csr_cost_allocation_keyflex_id;
3961
3962 hr_utility.set_location('cost_allocation_keyflex_id : '||l_cost_allocation_keyflex_id,10);
3963
3964 --
3965 IF NVL(l_cost_allocation_keyflex_id,0) <> 0 THEN
3966 --
3967 OPEN csr_cost_segments (p_cost_allocation_keyflex_id => l_cost_allocation_keyflex_id);
3968 FETCH csr_cost_segments INTO l_pay_cost_allocation_rec;
3969 CLOSE csr_cost_segments;
3970
3971 -- assign the defaults at organization level for only not null values so that we don't erase information
3972 -- entered at payroll level
3973 g_seg_val_tab(1).segment_value := NVL(l_pay_cost_allocation_rec.segment1,g_seg_val_tab(1).segment_value);
3974 g_seg_val_tab(2).segment_value := NVL(l_pay_cost_allocation_rec.segment2,g_seg_val_tab(2).segment_value);
3975 g_seg_val_tab(3).segment_value := NVL(l_pay_cost_allocation_rec.segment3,g_seg_val_tab(3).segment_value);
3976 g_seg_val_tab(4).segment_value := NVL(l_pay_cost_allocation_rec.segment4,g_seg_val_tab(4).segment_value);
3977 g_seg_val_tab(5).segment_value := NVL(l_pay_cost_allocation_rec.segment5,g_seg_val_tab(5).segment_value);
3978 g_seg_val_tab(6).segment_value := NVL(l_pay_cost_allocation_rec.segment6,g_seg_val_tab(6).segment_value);
3979 g_seg_val_tab(7).segment_value := NVL(l_pay_cost_allocation_rec.segment7,g_seg_val_tab(7).segment_value);
3980 g_seg_val_tab(8).segment_value := NVL(l_pay_cost_allocation_rec.segment8,g_seg_val_tab(8).segment_value);
3981 g_seg_val_tab(9).segment_value := NVL(l_pay_cost_allocation_rec.segment9,g_seg_val_tab(9).segment_value);
3982 g_seg_val_tab(10).segment_value := NVL(l_pay_cost_allocation_rec.segment10,g_seg_val_tab(10).segment_value);
3983 g_seg_val_tab(11).segment_value := NVL(l_pay_cost_allocation_rec.segment11,g_seg_val_tab(11).segment_value);
3984 g_seg_val_tab(12).segment_value := NVL(l_pay_cost_allocation_rec.segment12,g_seg_val_tab(12).segment_value);
3985 g_seg_val_tab(13).segment_value := NVL(l_pay_cost_allocation_rec.segment13,g_seg_val_tab(13).segment_value);
3986 g_seg_val_tab(14).segment_value := NVL(l_pay_cost_allocation_rec.segment14,g_seg_val_tab(14).segment_value);
3987 g_seg_val_tab(15).segment_value := NVL(l_pay_cost_allocation_rec.segment15,g_seg_val_tab(15).segment_value);
3988 g_seg_val_tab(16).segment_value := NVL(l_pay_cost_allocation_rec.segment16,g_seg_val_tab(16).segment_value);
3989 g_seg_val_tab(17).segment_value := NVL(l_pay_cost_allocation_rec.segment17,g_seg_val_tab(17).segment_value);
3990 g_seg_val_tab(18).segment_value := NVL(l_pay_cost_allocation_rec.segment18,g_seg_val_tab(18).segment_value);
3991 g_seg_val_tab(19).segment_value := NVL(l_pay_cost_allocation_rec.segment19,g_seg_val_tab(19).segment_value);
3992 g_seg_val_tab(20).segment_value := NVL(l_pay_cost_allocation_rec.segment20,g_seg_val_tab(20).segment_value);
3993 g_seg_val_tab(21).segment_value := NVL(l_pay_cost_allocation_rec.segment21,g_seg_val_tab(21).segment_value);
3994 g_seg_val_tab(22).segment_value := NVL(l_pay_cost_allocation_rec.segment22,g_seg_val_tab(22).segment_value);
3995 g_seg_val_tab(23).segment_value := NVL(l_pay_cost_allocation_rec.segment23,g_seg_val_tab(23).segment_value);
3996 g_seg_val_tab(24).segment_value := NVL(l_pay_cost_allocation_rec.segment24,g_seg_val_tab(24).segment_value);
3997 g_seg_val_tab(25).segment_value := NVL(l_pay_cost_allocation_rec.segment25,g_seg_val_tab(25).segment_value);
3998 g_seg_val_tab(26).segment_value := NVL(l_pay_cost_allocation_rec.segment26,g_seg_val_tab(26).segment_value);
3999 g_seg_val_tab(27).segment_value := NVL(l_pay_cost_allocation_rec.segment27,g_seg_val_tab(27).segment_value);
4000 g_seg_val_tab(28).segment_value := NVL(l_pay_cost_allocation_rec.segment28,g_seg_val_tab(28).segment_value);
4001 g_seg_val_tab(29).segment_value := NVL(l_pay_cost_allocation_rec.segment29,g_seg_val_tab(29).segment_value);
4002 g_seg_val_tab(30).segment_value := NVL(l_pay_cost_allocation_rec.segment30,g_seg_val_tab(30).segment_value);
4003
4004 END IF; -- defaults at organization level
4005
4006
4007
4008
4009 hr_utility.set_location('Leaving:'||l_proc, 1000);
4010
4011 EXCEPTION
4012 WHEN OTHERS THEN
4013 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4014 hr_utility.set_message_token('ROUTINE', l_proc);
4015 hr_utility.set_message_token('REASON', SQLERRM);
4016 hr_utility.raise_error;
4017 END get_organization_defaults;
4018 --------------------------------------------------------------------------------------------------------------
4019 PROCEDURE reverse_prev_posted_version
4020 IS
4021 /*
4022 Added on 10/10/2000 -- At any point of time , only ONE budget version can be posted to GL.
4023 The column TRANSFERED_TO_GL_FLAG in pqh_budget_versions will indicate the last posted version.
4024 For a given budget, only one budget_version will have TRANSFERED_TO_GL_FLAG = 'Y' , which is the last
4025 posted version.
4026 We check if the current budget_version_id has TRANSFERED_TO_GL_FLAG = 'Y' ,
4027 If Yes => this is the last posted version and the user is doing adjustments on the version.
4028 We don't do anything in this case as our current code takes care of adjustments
4029 If No => the last posted version is different then the current version. So we get the last posted
4030 budget_version_id and create reverse txns for this from the pqh_gl_interface table.
4031 We will also update the following :
4032 Budget Version table :
4033 For last Posted Version :
4034 TRANSFERED_TO_GL_FLAG for the last posted version to 'N '
4035 gl_status = 'UNPOST'
4036 For the current budget_version :
4037 After posting the current version we will update the TRANSFERED_TO_GL_FLAG to Y for the current version.
4038 gl_status = POST or ERROR
4039
4040 Budget Detail table :
4041 For last Posted Version :
4042 gl_status = NULL ( for all records )
4043 For the current budget_version :
4044 handled by the current posting logic, will be set to POST or ERROR
4045
4046
4047
4048 */
4049 --
4050 -- local variables
4051 --
4052 l_proc varchar2(72) := g_package||' .reverse_prev_posted_version';
4053
4054 l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
4055
4056 CURSOR csr_last_posted_ver IS
4057 SELECT budget_version_id
4058 FROM pqh_budget_versions
4059 WHERE budget_id = g_budget_id
4060 AND NVL(transfered_to_gl_flag,'N') = 'Y';
4061
4062 CURSOR csr_unpost_version (p_budget_version_id IN number )IS
4063 SELECT *
4064 FROM pqh_gl_interface
4065 WHERE budget_version_id = p_budget_version_id
4066 AND NVL(adjustment_flag,'N') = 'N'
4067 AND posting_type_cd = 'BUDGET'
4068 AND status IS NOT NULL
4069 AND posting_date IS NOT NULL
4070 AND (NVL(amount_dr,0) > 0 OR NVL(amount_cr,0) > 0 )
4071 FOR UPDATE of amount_dr;
4072
4073 BEGIN
4074
4075 hr_utility.set_location('Entering:'||l_proc, 5);
4076
4077 -- get the last posted budget_version
4078 OPEN csr_last_posted_ver;
4079 FETCH csr_last_posted_ver INTO g_last_posted_ver;
4080 CLOSE csr_last_posted_ver;
4081
4082 hr_utility.set_location('Last Posted Version is : '||g_last_posted_ver,6);
4083 hr_utility.set_location('Current Budget Version is : '||g_budget_version_id,6);
4084
4085 IF NVL(g_last_posted_ver,0) <> g_budget_version_id THEN
4086
4087 OPEN csr_unpost_version(p_budget_version_id => g_last_posted_ver);
4088 LOOP
4089 FETCH csr_unpost_version INTO l_pqh_gl_interface_rec;
4090 EXIT WHEN csr_unpost_version%NOTFOUND;
4091
4092 -- update the current record
4093 UPDATE pqh_gl_interface
4094 SET amount_dr = 0
4095 WHERE CURRENT OF csr_unpost_version;
4096
4097 -- create the reverse txn
4098 INSERT INTO pqh_gl_interface
4099 (
4100 gl_interface_id,
4101 budget_version_id,
4102 budget_detail_id,
4103 period_name,
4104 accounting_date,
4105 code_combination_id,
4106 cost_allocation_keyflex_id,
4107 project_id,
4108 task_id,
4109 award_id,
4110 expenditure_type,
4111 organization_id,
4112 amount_dr,
4113 amount_cr,
4114 currency_code,
4115 status,
4116 adjustment_flag,
4117 posting_date,
4118 posting_type_cd
4119 )
4120 VALUES
4121 (
4122 pqh_gl_interface_s.nextval,
4123 g_last_posted_ver,
4124 l_pqh_gl_interface_rec.budget_detail_id,
4125 l_pqh_gl_interface_rec.period_name,
4126 l_pqh_gl_interface_rec.accounting_date,
4127 l_pqh_gl_interface_rec.code_combination_id,
4128 l_pqh_gl_interface_rec.cost_allocation_keyflex_id,
4129 l_pqh_gl_interface_rec.project_id,
4130 l_pqh_gl_interface_rec.task_id,
4131 l_pqh_gl_interface_rec.award_id,
4132 l_pqh_gl_interface_rec.expenditure_type,
4133 l_pqh_gl_interface_rec.organization_id,
4134 0,
4135 NVL(l_pqh_gl_interface_rec.amount_dr,0),
4136 l_pqh_gl_interface_rec.currency_code,
4137 null,
4138 'Y',
4139 null,
4140 'BUDGET'
4141 );
4142
4143
4144
4145 END LOOP;
4146 CLOSE csr_unpost_version;
4147
4148 -- update the last posted version, gl_status to UNPOST and TRANSFERED_TO_GL_FLAG to N
4149 UPDATE pqh_budget_versions
4150 SET transfered_to_gl_flag = 'N' ,
4151 gl_status = 'UNPOST'
4152 WHERE budget_version_id = g_last_posted_ver;
4153
4154 -- update the budget_detail records corresponding to this version , set gl_status to null
4155 UPDATE pqh_budget_details
4156 SET gl_status = ''
4157 WHERE budget_version_id = g_last_posted_ver;
4158
4159 --
4160 -- Reverse commitment posting .
4161 --
4162 reverse_commitment_post(p_last_posted_ver => g_last_posted_ver,
4163 p_curr_bdgt_version => g_budget_version_id);
4164 --
4165
4166
4167 END IF; -- if the current version is not the last posted version
4168
4169
4170
4171 hr_utility.set_location('Leaving:'||l_proc, 1000);
4172
4173 EXCEPTION
4174 WHEN OTHERS THEN
4175 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4176 hr_utility.set_message_token('ROUTINE', l_proc);
4177 hr_utility.set_message_token('REASON', SQLERRM);
4178 hr_utility.raise_error;
4179 END reverse_prev_posted_version;
4180
4181 --------------------------------------------------------------------------------------------------------------
4182 --
4183 -- Added the following wrapper to get_gl_ccid function so that the code
4184 -- can be re-used in commitment gl posting
4185 --
4186 PROCEDURE get_ccid_for_commitment(
4187 p_budget_id IN pqh_budgets.budget_id%type,
4188 p_chart_of_accounts_id IN gl_interface.chart_of_accounts_id%TYPE,
4189 p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE,
4190 p_budget_period_id IN pqh_budget_periods.budget_period_id%TYPE,
4191 p_cost_allocation_keyflex_id IN pay_cost_allocation_keyflex.cost_allocation_keyflex_id%TYPE,
4192 p_code_combination_id OUT NOCOPY gl_code_combinations.code_combination_id%TYPE) IS
4193 --
4194 l_code_combination_id gl_code_combinations.code_combination_id%TYPE;
4195 --
4196 l_proc varchar2(72) := g_package||'.get_ccid_for_commitment';
4197 --
4198 BEGIN
4199 --
4200 hr_utility.set_location('Entering:'||l_proc, 5);
4201 --
4202 g_budget_id := p_budget_id;
4203 g_chart_of_accounts_id := p_chart_of_accounts_id;
4204 --
4205 --
4206 get_gl_ccid
4207 (
4208 p_budget_detail_id => p_budget_detail_id,
4209 p_budget_period_id => p_budget_period_id,
4210 p_cost_allocation_keyflex_id => p_cost_allocation_keyflex_id,
4211 p_code_combination_id => l_code_combination_id
4212 );
4213 --
4214 p_code_combination_id := l_code_combination_id;
4215 --
4216 hr_utility.set_location('Leaving:'||l_proc, 1000);
4217
4218 EXCEPTION
4219 WHEN OTHERS THEN
4220 p_code_combination_id := null;
4221 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4222 hr_utility.set_message_token('ROUTINE', l_proc);
4223 hr_utility.set_message_token('REASON', SQLERRM);
4224 hr_utility.raise_error;
4225 END;
4226
4227 --
4228 -- Added the following wrapper to end_commitment_log function so that the code
4229 -- can be re-used in commitment gl posting
4230 --
4231
4232 PROCEDURE end_commitment_log(p_status OUT NOCOPY varchar2) IS
4233 --
4234 l_proc varchar2(72) := g_package||'.end_commitment_log';
4235 --
4236 BEGIN
4237 --
4238 hr_utility.set_location('Entering:'||l_proc, 5);
4239 --
4240 end_log;
4241 --
4242 p_status := g_status;
4243 --
4244 hr_utility.set_location('Leaving:'||l_proc, 1000);
4245 --
4246 EXCEPTION
4247 WHEN OTHERS THEN
4248 p_status := null;
4249 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4250 hr_utility.set_message_token('ROUTINE', l_proc);
4251 hr_utility.set_message_token('REASON', SQLERRM);
4252 hr_utility.raise_error;
4253 END;
4254
4255
4256
4257 PROCEDURE reverse_commitment_post(p_last_posted_ver IN NUMBER,
4258 p_curr_bdgt_version IN NUMBER) IS
4259 --
4260 l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
4261 l_proc varchar2(72) := g_package||'.reverse_commitment_post';
4262 --
4263 CURSOR csr_unpost_version (p_budget_version_id IN number )IS
4264 SELECT *
4265 FROM pqh_gl_interface
4266 WHERE budget_version_id = p_budget_version_id
4267 AND NVL(adjustment_flag,'N') = 'N'
4268 AND posting_type_cd = 'COMMITMENT'
4269 AND status IS NOT NULL
4270 AND posting_date IS NOT NULL
4271 AND (NVL(amount_dr,0) > 0 OR NVL(amount_cr,0) > 0 )
4272 FOR UPDATE of amount_dr;
4273 --
4274 BEGIN
4275 --
4276 hr_utility.set_location('Entering:'||l_proc, 5);
4277 --
4278 hr_utility.set_location('Current Budget Version is:'||p_curr_bdgt_version,6);
4279
4280 IF NVL(p_last_posted_ver,0) <> p_curr_bdgt_version THEN
4281 --
4282 OPEN csr_unpost_version(p_budget_version_id => p_last_posted_ver);
4283 --
4284 LOOP
4285 FETCH csr_unpost_version INTO l_pqh_gl_interface_rec;
4286 EXIT WHEN csr_unpost_version%NOTFOUND;
4287
4288 -- update the current record
4289 UPDATE pqh_gl_interface
4290 SET amount_dr = 0
4291 WHERE CURRENT OF csr_unpost_version;
4292
4293 -- create the reverse txn
4294 INSERT INTO pqh_gl_interface
4295 (
4296 gl_interface_id,
4297 budget_version_id,
4298 budget_detail_id,
4299 period_name,
4300 accounting_date,
4301 code_combination_id,
4302 cost_allocation_keyflex_id,
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_last_posted_ver,
4320 l_pqh_gl_interface_rec.budget_detail_id,
4321 l_pqh_gl_interface_rec.period_name,
4322 l_pqh_gl_interface_rec.accounting_date,
4323 l_pqh_gl_interface_rec.code_combination_id,
4324 l_pqh_gl_interface_rec.cost_allocation_keyflex_id,
4325 l_pqh_gl_interface_rec.project_id,
4326 l_pqh_gl_interface_rec.task_id,
4327 l_pqh_gl_interface_rec.award_id,
4328 l_pqh_gl_interface_rec.expenditure_type,
4329 l_pqh_gl_interface_rec.organization_id,
4330 0,
4331 NVL(l_pqh_gl_interface_rec.amount_dr,0),
4332 l_pqh_gl_interface_rec.currency_code,
4333 null,
4334 'Y',
4335 null,
4336 'BUDGET'
4337 );
4338 --
4339 END LOOP;
4340 --
4341 CLOSE csr_unpost_version;
4342 --
4343 -- update the last posted version, commitment_gl_status to UNPOST
4344 --
4345 UPDATE pqh_budget_versions
4346 SET commitment_gl_status = 'UNPOST'
4347 WHERE budget_version_id = g_last_posted_ver;
4348 --
4349 -- update the budget_detail records corresponding to this version
4350 --
4351 UPDATE pqh_budget_details
4352 SET commitment_gl_status = 'UNPOST'
4353 WHERE budget_version_id = g_last_posted_ver;
4354 --
4355 END IF; -- if the current version is not the last posted version
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 reverse_commitment_post;
4366
4367 FUNCTION chk_budget_details(p_budget_version_id IN pqh_budget_details.budget_version_id%TYPE )
4368 RETURN varchar2 IS
4369 /*
4370 This function will determine whether the budget has details or not
4371 If the budget has details return 'Y' else 'N'
4372
4373 */
4374 l_budget_fund_src_id pqh_budget_fund_srcs.budget_fund_src_id%TYPE;
4375 l_result varchar2(30);
4376
4377 CURSOR csr_budget_details (p_budget_version_id IN number )IS
4378 SELECT bfs.budget_fund_src_id
4379 FROM pqh_budget_details bdt, pqh_budget_periods bpr, pqh_budget_sets bst, pqh_budget_elements bel, pqh_budget_fund_srcs bfs
4380 WHERE bdt.budget_version_id = p_budget_version_id
4381 and bdt.budget_detail_id = bpr.budget_detail_id
4382 and bpr.budget_period_id = bst.budget_period_id
4383 and bst.budget_set_id = bel.budget_set_id
4384 and bel.budget_element_id = bfs.budget_element_id;
4385
4386 BEGIN
4387
4388 OPEN csr_budget_details(p_budget_version_id);
4389 FETCH csr_budget_details INTO l_budget_fund_src_id;
4390 CLOSE csr_budget_details;
4391 If l_budget_fund_src_id is NOT NULL then
4392 l_result := 'Y';
4393 else
4394 l_result := 'N';
4395 end if;
4396 return l_result;
4397 END chk_budget_details;
4398
4399 -------------------------------------------------------------------------------------------------------------
4400 ------------------------------------------------------------------------------------------------------------
4401 PROCEDURE populate_period_enc_tab
4402 (
4403 p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE
4404 ) IS
4405
4406 /*
4407 This procedure will read the above populated global table g_period_amt_tab and get the
4408 LD encumbrance amount for the period_id .
4409 For each period of Budget Detail, all Assignments of the Budget Detail Position are found .
4410 For each such Assignment a call for LD function will be made to get LD Encumbrance amount.
4411 Sum of this encumbrance amount over all Assignments will give Period Encumbrance amount.
4412 This function will then call adjust_ptaeo_gms_amount to prorate across all PTAEO's for that period.
4413
4414 Any exception encountered in this procedure will terminate the program.
4415 */
4416
4417 --
4418 -------------------- Local Variables ----------------------------
4419 --
4420
4421 i binary_integer :=0;
4422 p_enc binary_integer :=0;
4423 inx binary_integer :=0;
4424 l_start_pid number;
4425 l_end_pid number;
4426 l_entity_id number;
4427 l_period_encumbrance number;
4428 l_assign_encumbrance number;
4429 l_assignment_id number;
4430 /*l_ptaeo_amt number;
4431 l_ptaeo_adjustment number;*/
4432 l_period_tot_amt number;
4433 l_budget_period_id number :=-1;
4434 l_unit_of_measure number :=-1;
4435 l_period_start_date date;
4436 l_period_end_date date;
4437 l_assg_start_date date;
4438 l_assg_end_date date;
4439 l_encumbrance_start_date date;
4440 l_encumbrance_end_date date;
4441 l_dummy date;
4442 l_asg_psp_encumbered boolean;
4443 l_return_status varchar2(10);
4444 l_uom1 varchar2(80);
4445 l_uom2 varchar2(80);
4446 l_uom3 varchar2(80);
4447 l_proc varchar2(72) := g_package||'.populate_period_enc_tab';
4448 l_encumbrance_table psp_pqh_integration.encumbrance_table_rec_col;
4449
4450 ------Local Types------
4451 TYPE t_period_enc_type IS RECORD
4452 (
4453 budget_period_id number,
4454 enc_amount number
4455 );
4456
4457 -- PL / SQL table based on the above structure
4458 TYPE t_period_enc_tab IS TABLE OF t_period_enc_type
4459 INDEX BY BINARY_INTEGER;
4460
4461 TYPE t_bin_array IS TABLE OF BINARY_INTEGER
4462 INDEX BY BINARY_INTEGER;
4463
4464 l_period_encumbrance_tab t_period_enc_tab;
4465 l_num t_bin_array;
4466
4467 ----------------------------------Cursors-----------------------------------------
4468
4469 -- Cursors to get Budget Detail Entity
4470
4471 Cursor budget_detail_position IS
4472 Select position_id
4473 From
4474 pqh_budget_details
4475 where budget_detail_id = p_budget_detail_id;
4476
4477 Cursor budget_detail_org IS
4478 Select organization_id
4479 From
4480 pqh_budget_details
4481 where budget_detail_id = p_budget_detail_id;
4482
4483 Cursor budget_detail_grd IS
4484 Select grade_id
4485 From
4486 pqh_budget_details
4487 where budget_detail_id = p_budget_detail_id;
4488
4489 Cursor budget_detail_job IS
4490 Select job_id
4491 From
4492 pqh_budget_details
4493 where budget_detail_id = p_budget_detail_id;
4494 --
4495 -- Cursors to get Budget Detail Entity Assignments
4496
4497 Cursor budget_period_pos_assignments(start_date date,end_date date) IS
4498 Select
4499 assignment_id,
4500 effective_start_date,
4501 effective_end_date
4502 From per_all_assignments_f
4503 Where position_id=l_entity_id and
4504 (
4505 (effective_start_date <=start_date AND effective_end_date >start_date)
4506 OR
4507 (effective_start_date <=end_date AND effective_end_date >end_date)
4508 );
4509
4510
4511 Cursor budget_period_org_assignments(start_date date,end_date date) IS
4512 Select
4513 assignment_id,
4514 effective_start_date,
4515 effective_end_date
4516 From per_all_assignments_f
4517 Where organization_id=l_entity_id and
4518 (
4519 (effective_start_date <=start_date AND effective_end_date >start_date)
4520 OR
4521 (effective_start_date <=end_date AND effective_end_date >end_date)
4522 );
4523
4524
4525 Cursor budget_period_grd_assignments(start_date date,end_date date) IS
4526 Select
4527 assignment_id,
4528 effective_start_date,
4529 effective_end_date
4530 From per_all_assignments_f
4531 Where grade_id=l_entity_id and
4532 (
4533 (effective_start_date <=start_date AND effective_end_date >start_date)
4534 OR
4535 (effective_start_date <=end_date AND effective_end_date >end_date)
4536 );
4537
4538
4539 Cursor budget_period_job_assignments(start_date date,end_date date) IS
4540 Select
4541 assignment_id,
4542 effective_start_date,
4543 effective_end_date
4544 From per_all_assignments_f
4545 Where job_id=l_entity_id and
4546 (
4547 (effective_start_date <=start_date AND effective_end_date >start_date)
4548 OR
4549 (effective_start_date <=end_date AND effective_end_date >end_date)
4550 );
4551 --
4552
4553 Cursor csr_budget_time_periods(p_budget_period_id NUMBER) IS
4554 Select
4555 start_time_period_id,
4556 end_time_period_id
4557 From
4558 pqh_budget_periods
4559 Where
4560 budget_period_id = p_budget_period_id;
4561
4562 Cursor csr_time_periods(p_time_period_id NUMBER)IS
4563 Select
4564 start_date,
4565 end_date
4566 From
4567 per_time_periods
4568 Where
4569 time_period_id = p_time_period_id;
4570
4571 Cursor csr_budget_units IS
4572 Select
4573 hr_general.decode_shared_type(budget_unit1_id) UOM1,
4574 hr_general.decode_shared_type(budget_unit2_id) UOM2,
4575 hr_general.decode_shared_type(budget_unit3_id) UOM3
4576 From
4577 pqh_budgets
4578 Where budget_id=g_budget_id;
4579
4580 Cursor csr_last_posted_ver IS
4581 Select budget_version_id
4582 From pqh_budget_versions
4583 Where budget_id = g_budget_id
4584 AND NVL(transfered_to_gl_flag,'N') = 'Y';
4585
4586
4587 Begin
4588
4589 hr_utility.set_location('Entering: '||l_proc, 5);
4590
4591 --
4592 -- Get ID of primary Entity attached with Budget Detail
4593 --
4594 IF (g_budgeted_entity_cd='POSITION') THEN
4595 OPEN budget_detail_position;
4596 FETCH budget_detail_position into l_entity_id;
4597 CLOSE budget_detail_position;
4598
4599 ELSIF(g_budgeted_entity_cd='ORGANIZATION') THEN
4600 OPEN budget_detail_org;
4601 FETCH budget_detail_org into l_entity_id;
4602 CLOSE budget_detail_org;
4603
4604 ELSIF(g_budgeted_entity_cd='GRADE') THEN
4605 OPEN budget_detail_grd;
4606 FETCH budget_detail_grd into l_entity_id;
4607 CLOSE budget_detail_grd;
4608
4609 ELSIF(g_budgeted_entity_cd='JOB') THEN
4610 OPEN budget_detail_job;
4611 FETCH budget_detail_job into l_entity_id;
4612 CLOSE budget_detail_job;
4613 END IF;
4614
4615
4616
4617 --
4618 --For each Period in Budget Details get LD encumbrance amount across Assignments
4619 --
4620
4621 FOR i in 1..g_period_amt_tab.COUNT
4622 LOOP
4623 --
4624 --Calculate Encumbrnace for a Period only if atleast one Funding source for that period is PTAEO
4625 --
4626 IF (g_period_amt_tab(i).cost_allocation_keyflex_id is null AND
4627 g_period_amt_tab(i).period_id<> l_budget_period_id)
4628 THEN
4629 --
4630 hr_utility.set_location('Processing Period:'||g_period_amt_tab(i).period_id, 10);
4631 --
4632 l_period_encumbrance :=0;
4633 l_budget_period_id := g_period_amt_tab(i).period_id;
4634 --
4635 --Get Budget Period start and end time period id's
4636 --
4637 OPEN csr_budget_time_periods(l_budget_period_id);
4638 FETCH csr_budget_time_periods into l_start_pid,l_end_pid;
4639 CLOSE csr_budget_time_periods;
4640 --
4641 -- Get Budget Periods start date
4642 --
4643 OPEN csr_time_periods(l_start_pid);
4644 FETCH csr_time_periods into l_period_start_date,l_dummy;
4645 CLOSE csr_time_periods;
4646 --
4647 --Get Budget Periods end date
4648 --
4649 OPEN csr_time_periods(l_end_pid);
4650 FETCH csr_time_periods into l_dummy, l_period_end_date;
4651 CLOSE csr_time_periods;
4652 --
4653 -- For each Position Assignment calculate LD encumbrance for that period
4654 --
4655 IF (g_budgeted_entity_cd='POSITION') THEN
4656
4657 OPEN budget_period_pos_assignments(l_period_start_date,l_period_end_date);
4658
4659 ELSIF(g_budgeted_entity_cd='ORGANIZATION') THEN
4660
4661 OPEN budget_period_org_assignments(l_period_start_date,l_period_end_date);
4662
4663 ELSIF(g_budgeted_entity_cd='GRADE') THEN
4664
4665 OPEN budget_period_grd_assignments(l_period_start_date,l_period_end_date);
4666
4667 ELSIF(g_budgeted_entity_cd='JOB') THEN
4668
4669 OPEN budget_period_job_assignments(l_period_start_date,l_period_end_date);
4670
4671 END IF;
4672 -- For each assignment
4673 LOOP
4674
4675 IF (g_budgeted_entity_cd='POSITION') THEN
4676
4677 FETCH budget_period_pos_assignments into l_assignment_id,l_assg_start_date ,l_assg_end_date;
4678 IF budget_period_pos_assignments%NOTFOUND THEN
4679 CLOSE budget_period_pos_assignments;
4680 EXIT;
4681 END IF;
4682
4683 ELSIF(g_budgeted_entity_cd='ORGANIZATION') THEN
4684
4685 FETCH budget_period_org_assignments into l_assignment_id,l_assg_start_date ,l_assg_end_date;
4686 IF budget_period_org_assignments%NOTFOUND THEN
4687 CLOSE budget_period_org_assignments;
4688 EXIT;
4689 END IF;
4690
4691 ELSIF(g_budgeted_entity_cd='GRADE') THEN
4692
4693 FETCH budget_period_grd_assignments into l_assignment_id,l_assg_start_date ,l_assg_end_date;
4694 IF budget_period_grd_assignments%NOTFOUND THEN
4695 CLOSE budget_period_grd_assignments;
4696 EXIT;
4697 END IF;
4698
4699 ELSIF(g_budgeted_entity_cd='JOB') THEN
4700
4701 FETCH budget_period_job_assignments into l_assignment_id,l_assg_start_date ,l_assg_end_date;
4702 IF budget_period_job_assignments%NOTFOUND THEN
4703 CLOSE budget_period_job_assignments;
4704 EXIT;
4705 END IF;
4706
4707 END IF;
4708 l_assign_encumbrance :=0;
4709 --
4710 --Get Encumbrance dates
4711 --
4712 IF (l_assg_start_date > l_period_start_date)
4713 THEN
4714 l_encumbrance_start_date := l_assg_start_date;
4715 ELSE
4716 l_encumbrance_start_date := l_period_start_date;
4717 END IF;
4718 IF (l_assg_end_date < l_period_end_date)
4719 THEN
4720 l_encumbrance_end_date := l_assg_end_date;
4721 ELSE
4722 l_encumbrance_end_date := l_period_end_date;
4723 END IF;
4724 --
4725 hr_utility.set_location('Calling LD Function with Followin params',20);
4726 hr_utility.set_location('Processing Assignment:'||l_assignment_id, 22);
4727 hr_utility.set_location('Encumbrance Start Date:'||l_encumbrance_start_date, 24);
4728 hr_utility.set_location('Encumbracne End Date :'||l_encumbrance_end_date, 26);
4729 --
4730 --Call LD functions to get Encumbered amount
4731 --
4732 psp_pqh_integration.get_asg_encumbrances(l_assignment_id,
4733 l_encumbrance_start_date ,
4734 l_encumbrance_end_date ,
4735 l_encumbrance_table,
4736 l_asg_psp_encumbered,
4737 l_return_status);
4738 IF(l_asg_psp_encumbered) THEN
4739 hr_utility.set_location('Assignment Encumbered by LD :'||l_assign_encumbrance, 30);
4740 FOR psp_inx IN 1..l_encumbrance_table.r_gms_enc_amount.COUNT LOOP
4741 hr_utility.set_location('GMS Amount :'||l_encumbrance_table.r_gms_enc_amount(psp_inx), 31);
4742 l_assign_encumbrance := l_assign_encumbrance +l_encumbrance_table.r_gms_enc_amount(psp_inx);
4743 END LOOP;
4744 l_period_encumbrance := l_period_encumbrance + l_assign_encumbrance;
4745 END IF;
4746 --
4747 hr_utility.set_location('Assignment Encumbrance:'||l_assign_encumbrance, 35);
4748 --
4749 END LOOP; -- Assignment Loop
4750 p_enc := p_enc + 1;
4751 hr_utility.set_location('Period Encumbrance:'||l_period_encumbrance, 40);
4752 l_period_encumbrance_tab(p_enc).budget_period_id :=l_budget_period_id;
4753 l_period_encumbrance_tab(p_enc).enc_amount :=l_period_encumbrance;
4754 END IF;
4755 END LOOP;
4756 /*
4757 Now for each period in l_period_encumbrance_tab find corresponding records in g_period_amt_tab
4758 Reduce Money units amount for those records based on enc_amount.
4759 */
4760 --
4761 --Find Which of three budget_units is Money Unit
4762 --
4763 OPEN csr_budget_units;
4764 FETCH csr_budget_units into l_uom1, l_uom2,l_uom3;
4765 CLOSE csr_budget_units;
4766
4767 IF NVL(l_uom1,'X')='Money' THEN
4768 l_unit_of_measure :=1;
4769 ELSIF NVL(l_uom2,'X')='Money' THEN
4770 l_unit_of_measure :=2;
4771 ELSIF NVL(l_uom3,'X')='Money' THEN
4772 l_unit_of_measure :=3;
4773 END IF;
4774 --
4775 -- If current Budget version id not Last posted Budget Version then entries in
4776 -- pqh_gms_excess would be meaningless. Hence delete them.
4777 --
4778 OPEN csr_last_posted_ver;
4779 FETCH csr_last_posted_ver INTO g_last_posted_ver;
4780 IF (csr_last_posted_ver%FOUND AND g_last_posted_ver <> g_budget_version_id)
4781 THEN
4782 DELETE from pqh_gms_excess
4783 WHERE budget_period_id in (Select budget_period_id
4784 From
4785 pqh_budget_periods bpr
4786 ,pqh_budget_details bdt
4787 Where bpr.budget_detail_id = bdt.budget_detail_id AND
4788 bdt.budget_version_id=g_budget_version_id);
4789 END IF;
4790 CLOSE csr_last_posted_ver;
4791 --
4792 hr_utility.set_location('Corrections for LD Encumbrance', 45);
4793 --
4794 FOR psp_inx IN 1..l_period_encumbrance_tab.COUNT
4795 LOOP
4796 --
4797 hr_utility.set_location('Updating Period :'||l_period_encumbrance_tab(psp_inx).budget_period_id, 50);
4798 --
4799 l_period_encumbrance :=l_period_encumbrance_tab(psp_inx).enc_amount;
4800 l_period_tot_amt :=0;
4801 inx :=0;
4802 --
4803 --Find all PTAEO's that need to be adjusted for that Budget Period and get sum total of Money posted
4804 -- to GMS by Budget for that period. We require this to pro-rate LD encumbrances across available PTAEO's
4805 --
4806 FOR i in 1..g_period_amt_tab.COUNT
4807 LOOP
4808 IF ( g_period_amt_tab(i).period_id = l_period_encumbrance_tab(psp_inx).budget_period_id
4809 AND
4810 g_period_amt_tab(i).cost_allocation_keyflex_id is null
4811 )
4812 THEN
4813 --
4814 --Set Currency Code
4815 --
4816 inx := inx +1;
4817 l_num(inx) := i;
4818 IF NVL(l_uom1,'X')='Money' THEN
4819 l_period_tot_amt :=l_period_tot_amt + g_period_amt_tab(i).amount1;
4820 ELSIF NVL(l_uom2,'X')='Money' THEN
4821 l_period_tot_amt :=l_period_tot_amt + g_period_amt_tab(i).amount2;
4822 ELSIF NVL(l_uom3,'X')='Money' THEN
4823 l_period_tot_amt :=l_period_tot_amt + g_period_amt_tab(i).amount3;
4824 END IF;
4825 END IF;
4826 END LOOP;
4827 --
4828 hr_utility.set_location('Updating relevant PTAEOs', 55);
4829 --
4830 --Adjust PTAEO amount by Prorating Period Encumbrance across PTAEO's
4831 --
4832 IF l_period_tot_amt <> 0 THEN
4833 FOR inx in 1..l_num.COUNT
4834 LOOP
4835 --
4836 --For Each PTAEO in period make adjustments according to LD encumbrance/Liquidation.
4837 --Also store Excess amounts if any in pqh_gms_excess table
4838 --
4839 adjust_ptaeo_gms_amount(l_num(inx),l_unit_of_measure,l_period_encumbrance,l_period_tot_amt);
4840 --
4841 END LOOP;
4842 END IF;
4843 --
4844 END LOOP;
4845 --
4846 hr_utility.set_location('Leaving:'||l_proc, 1000);
4847 --
4848 EXCEPTION
4849 WHEN OTHERS THEN
4850 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
4851 hr_utility.set_message_token('ROUTINE', l_proc);
4852 hr_utility.set_message_token('REASON', SQLERRM);
4853 hr_utility.raise_error;
4854 END populate_period_enc_tab;
4855
4856 ------------------------------------------------------------------------------------------------
4857 ------------------------------------------------------------------------------------------------
4858 PROCEDURE adjust_ptaeo_gms_amount
4859 (
4860 p_inx IN binary_integer,
4861 p_unit_of_measure IN number,
4862 p_period_encumbrance IN number,
4863 p_period_tot_amount IN number
4864 ) IS
4865 /*
4866 This procedure makes adjustments to PTAEO gms amount based on LD encumbrance/Liquidation for this period.
4867 prorates period_encumbrance across all PTAEO's for that period.
4868 Excess Encumbrance amount over PTAEO amount if any will be stored in pqh_gms_execess table.
4869 */
4870
4871 --
4872 -- Cursor to Fetch GMS excess amount for that PTAEO.
4873 -- IF LD encumbered amont 700 for a period but PTAEO in that period is only transfering 200 then excess
4874 -- 500 will be kept in pqh_gms_excess table for that budget_period and PTAEO
4875 --
4876 Cursor csr_ptaeo_excess(inx binary_integer) IS
4877 Select amount
4878 From pqh_gms_excess
4879 Where
4880 budget_period_id =g_period_amt_tab(inx).period_id
4881 AND project_id =g_period_amt_tab(inx).project_id
4882 AND task_id =g_period_amt_tab(inx).task_id
4883 AND award_id =g_period_amt_tab(inx).award_id
4884 AND expenditure_type =g_period_amt_tab(inx).expenditure_type
4885 AND organization_id =g_period_amt_tab(inx).organization_id;
4886
4887 ------------------------------Local Variables-------------------------------
4888 l_ptaeo_amt number;
4889 l_ptaeo_adjustment number;
4890 l_ptaeo_excess number;
4891 l_proc varchar2(72) := g_package||'.adjust_ptaeo_gms_amount';
4892
4893 Begin
4894 --
4895 hr_utility.set_location('Entering:'||l_proc, 10);
4896 --
4897 IF (p_unit_of_measure =1) THEN
4898 l_ptaeo_amt :=g_period_amt_tab(p_inx).amount1;
4899 ELSIF (p_unit_of_measure =2) THEN
4900 l_ptaeo_amt :=g_period_amt_tab(p_inx).amount2;
4901 ELSIF (p_unit_of_measure =3) THEN
4902 l_ptaeo_amt :=g_period_amt_tab(p_inx).amount3;
4903 END IF;
4904
4905 hr_utility.set_location('Original PTAEO Amount:'||l_ptaeo_amt, 20);
4906 --
4907 -- Prorate period_encumbrance across all PTAEO's in that budget period
4908 --
4909 l_ptaeo_adjustment :=p_period_encumbrance *(l_ptaeo_amt/p_period_tot_amount)*100;
4910 hr_utility.set_location('Prorated PTAEO Adjustment:'||l_ptaeo_adjustment, 20);
4911 --
4912 -- Check if there is any excess for that PTAEO/Period
4913 --
4914 OPEN csr_ptaeo_excess(p_inx);
4915 FETCH csr_ptaeo_excess into l_ptaeo_excess;
4916 --
4917 IF csr_ptaeo_excess%FOUND
4918 THEN
4919 hr_utility.set_location('Existing PTAEO Excess:'||l_ptaeo_excess, 30);
4920 --
4921 -- If excess is there and LD is liquidating take excess into consideration
4922 -- If excess is more than liquidation we dont need any adjustment for that PTAEO
4923 -- If excess is less than liquidation adjust for remaining amount
4924 --
4925 IF l_ptaeo_adjustment <0
4926 THEN
4927 --
4928 IF (ABS(l_ptaeo_adjustment) <l_ptaeo_excess)
4929 THEN
4930 l_ptaeo_excess := l_ptaeo_excess + l_ptaeo_adjustment;
4931 l_ptaeo_adjustment :=0;
4932 ELSE
4933 l_ptaeo_adjustment :=l_ptaeo_adjustment + l_ptaeo_excess;
4934 l_ptaeo_excess :=0;
4935 END IF;
4936 --
4937 l_ptaeo_amt := l_ptaeo_amt - l_ptaeo_adjustment;
4938 --
4939 -- If Ld is encumbering then deduct PTAEO adjustment from budget PTAEO amount
4940 -- If result is negative then we need not transfer anything for that PTAEO/Period and
4941 -- we will stor excess in pqh_gl_excess
4942 --
4943 ELSE
4944 l_ptaeo_amt := l_ptaeo_amt - l_ptaeo_adjustment;
4945 --
4946 IF l_ptaeo_amt <0 THEN
4947 l_ptaeo_excess := l_ptaeo_excess + ABS(l_ptaeo_amt);
4948 l_ptaeo_amt :=0;
4949
4950 END IF;
4951 --
4952 END IF;
4953 hr_utility.set_location('PTAEO Excess after Adjustment:'||l_ptaeo_excess, 40);
4954 --
4955 -- If net PTAEO excess after all adjustments is zero delete record, otherwise update
4956 --
4957 IF l_ptaeo_excess = 0 THEN
4958 --
4959 DELETE FROM PQH_GMS_EXCESS
4960 WHERE budget_period_id =g_period_amt_tab(p_inx).period_id
4961 AND project_id =g_period_amt_tab(p_inx).project_id
4962 AND task_id =g_period_amt_tab(p_inx).task_id
4963 AND award_id =g_period_amt_tab(p_inx).award_id
4964 AND expenditure_type =g_period_amt_tab(p_inx).expenditure_type
4965 AND organization_id =g_period_amt_tab(p_inx).organization_id;
4966 --
4967 ELSE
4968 --
4969 UPDATE PQH_GMS_EXCESS
4970 SET amount = l_ptaeo_excess
4971 WHERE budget_period_id =g_period_amt_tab(p_inx).period_id
4972 AND project_id =g_period_amt_tab(p_inx).project_id
4973 AND task_id =g_period_amt_tab(p_inx).task_id
4974 AND award_id =g_period_amt_tab(p_inx).award_id
4975 AND expenditure_type =g_period_amt_tab(p_inx).expenditure_type
4976 AND organization_id =g_period_amt_tab(p_inx).organization_id;
4977 --
4978 END IF;
4979 --
4980 -- If currently there is no excess for that PTAEO/Period and PTAEO amount after adjustment is negative
4981 -- post that amount to pqh_gms_excess and make PTAEO maount as zero
4982 --
4983 ELSE
4984 l_ptaeo_amt := l_ptaeo_amt - l_ptaeo_adjustment;
4985 hr_utility.set_location('PTAEO Excess after Adjustment:'||-l_ptaeo_amt, 50);
4986 --
4987 IF l_ptaeo_amt < 0 THEN
4988 INSERT into pqh_gms_excess
4989 ( GMS_EXCESS_ID
4990 ,BUDGET_PERIOD_ID
4991 ,PROJECT_ID
4992 ,TASK_ID
4993 ,AWARD_ID
4994 ,EXPENDITURE_TYPE
4995 ,ORGANIZATION_ID
4996 ,AMOUNT
4997 )
4998 VALUES
4999 (
5000 pqh_gms_excess_s.nextval
5001 ,g_period_amt_tab(p_inx).period_id
5002 ,g_period_amt_tab(p_inx).project_id
5003 ,g_period_amt_tab(p_inx).task_id
5004 ,g_period_amt_tab(p_inx).award_id
5005 ,g_period_amt_tab(p_inx).expenditure_type
5006 ,g_period_amt_tab(p_inx).organization_id
5007 ,-l_ptaeo_amt
5008 );
5009 l_ptaeo_amt :=0;
5010 END IF;
5011 --
5012 END IF;
5013 --
5014 -- Update global table with PTAEO amounts after adjustment
5015 --
5016 hr_utility.set_location('Adjusted PTAEO GMS amount:'||l_ptaeo_amt, 60);
5017 IF (p_unit_of_measure =1) THEN
5018 g_period_amt_tab(p_inx).amount1 :=l_ptaeo_amt;
5019 ELSIF (p_unit_of_measure =2) THEN
5020 g_period_amt_tab(p_inx).amount2 :=l_ptaeo_amt;
5021 ELSIF (p_unit_of_measure =3) THEN
5022 g_period_amt_tab(p_inx).amount3 :=l_ptaeo_amt;
5023 END IF;
5024 --
5025 hr_utility.set_location('Leaving:'||l_proc, 100);
5026 --
5027 END adjust_ptaeo_gms_amount;
5028
5029 --------------------------------------------------------------------------------------------
5030 --------------------------------------------------------------------------------------------
5031
5032 PROCEDURE populate_pqh_gms_interface
5033 (
5034 p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE
5035 )
5036 IS
5037 /*
5038 This procedure will update or insert GMS records into pqh_gl_interface if there was no error for
5039 the current budget detail record i.e g_detail_error = N
5040 if g_detail_error = Y then update the pqh_budget_details record with gl_status = ERROR
5041 */
5042 --
5043 -- local variables
5044 --
5045 l_proc varchar2(72) := g_package||'.populate_pqh_gms_interface';
5046 l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
5047 l_uom_count number;
5048 l_amount number;
5049 l_uom1 varchar2(80);
5050 l_uom2 varchar2(80);
5051 l_uom3 varchar2(80);
5052
5053
5054 Cursor csr_pqh_gms_interface ( p_period_name IN varchar2,
5055 p_project_id IN NUMBER,
5056 p_task_id IN NUMBER,
5057 p_award_id IN NUMBER,
5058 p_expenditure_type IN varchar2,
5059 p_organization_id IN NUMBER) IS
5060 Select COUNT(*)
5061 From pqh_gl_interface
5062 Where budget_version_id = g_budget_version_id
5063 AND budget_detail_id = p_budget_detail_id
5064 AND period_name = p_period_name
5065 AND posting_type_cd = 'BUDGET'
5066 AND project_id = p_project_id
5067 AND task_id = p_task_id
5068 AND award_id = p_award_id
5069 AND expenditure_type = p_expenditure_type
5070 AND organization_id = p_organization_id
5071 AND NVL(adjustment_flag,'N') = 'N'
5072 AND status IS NOT NULL
5073 AND posting_date IS NOT NULL;
5074
5075
5076 Cursor csr_budget_units IS
5077 Select
5078 hr_general.decode_shared_type(budget_unit1_id) UOM1,
5079 hr_general.decode_shared_type(budget_unit2_id) UOM2,
5080 hr_general.decode_shared_type(budget_unit3_id) UOM3
5081 From
5082 pqh_budgets
5083 Where budget_id=g_budget_id;
5084
5085
5086 BEGIN
5087
5088 hr_utility.set_location('Entering: '||l_proc, 5);
5089
5090 IF g_detail_error = 'N' THEN
5091 OPEN csr_budget_units;
5092 FETCH csr_budget_units into l_uom1, l_uom2,l_uom3;
5093 CLOSE csr_budget_units;
5094
5095 -- loop thru the array and get populate the pqh_gl_interface table
5096
5097 FOR i IN 1..g_period_amt_tab.COUNT
5098 LOOP
5099
5100 -- Populate only GMS records
5101 IF (g_period_amt_tab(i).cost_allocation_keyflex_id is null)
5102 THEN
5103 IF NVL(l_uom1,'X')='Money' THEN
5104 l_amount :=g_period_amt_tab(i).amount1;
5105 ELSIF NVL(l_uom2,'X')='Money' THEN
5106 l_amount :=g_period_amt_tab(i).amount2;
5107 ELSIF NVL(l_uom3,'X')='Money' THEN
5108 l_amount :=g_period_amt_tab(i).amount3;
5109 END IF;
5110 OPEN csr_pqh_gms_interface(p_period_name => g_period_amt_tab(i).period_name,
5111 p_project_id => g_period_amt_tab(i).project_id,
5112 p_task_id => g_period_amt_tab(i).task_id,
5113 p_award_id => g_period_amt_tab(i).award_id,
5114 p_expenditure_type => g_period_amt_tab(i).expenditure_type,
5115 p_organization_id => g_period_amt_tab(i).organization_id );
5116 FETCH csr_pqh_gms_interface INTO l_uom_count;
5117 CLOSE csr_pqh_gms_interface;
5118
5119
5120 IF l_uom_count <> 0 THEN
5121 -- update pqh_gl_interface and create a adjustment txn
5122 update_pqh_gms_interface
5123 (
5124 p_budget_detail_id => p_budget_detail_id,
5125 p_period_name => g_period_amt_tab(i).period_name,
5126 p_project_id => g_period_amt_tab(i).project_id,
5127 p_task_id => g_period_amt_tab(i).task_id,
5128 p_award_id => g_period_amt_tab(i).award_id,
5129 p_expenditure_type => g_period_amt_tab(i).expenditure_type,
5130 p_organization_id => g_period_amt_tab(i).organization_id,
5131 p_amount => l_amount
5132 );
5133 ELSE
5134 -- insert into pqh_gl_interface
5135 insert_pqh_gms_interface
5136 (
5137 p_budget_detail_id => p_budget_detail_id,
5138 p_period_name => g_period_amt_tab(i).period_name,
5139 p_project_id => g_period_amt_tab(i).project_id,
5140 p_task_id => g_period_amt_tab(i).task_id,
5141 p_award_id => g_period_amt_tab(i).award_id,
5142 p_expenditure_type => g_period_amt_tab(i).expenditure_type,
5143 p_organization_id => g_period_amt_tab(i).organization_id,
5144 p_amount => l_amount
5145 );
5146 END IF; -- l_uom1_count <> 0
5147
5148 END IF; -- Insert only GMS records
5149 END LOOP; -- end of pl sql table
5150
5151 -- update pqh_budget_details reset status if previous run was ERROR
5152 UPDATE pqh_budget_details
5153 SET gl_status = ''
5154 WHERE budget_detail_id = p_budget_detail_id;
5155
5156
5157
5158 ELSE -- g_detail_error = Y i.e errors in budget details children
5159
5160 -- update pqh_budget_details
5161 UPDATE pqh_budget_details
5162 SET gl_status = 'ERROR'
5163 WHERE budget_detail_id = p_budget_detail_id;
5164
5165 END IF; -- g_detail_error = 'N'
5166
5167 hr_utility.set_location('Leaving:'||l_proc, 1000);
5168
5169 EXCEPTION
5170 WHEN OTHERS THEN
5171 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5172 hr_utility.set_message_token('ROUTINE', l_proc);
5173 hr_utility.set_message_token('REASON', SQLERRM);
5174 hr_utility.raise_error;
5175 END populate_pqh_gms_interface;
5176
5177 ---------------------------------------------------------------------------------------------
5178 ---------------------------------------------------------------------------------------------
5179 PROCEDURE insert_pqh_gms_interface
5180 (
5181 p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
5182 p_period_name IN varchar2,
5183 p_project_id IN pqh_gl_interface.project_id%TYPE,
5184 p_task_id IN pqh_gl_interface.task_id%TYPE,
5185 p_award_id IN pqh_gl_interface.award_id%TYPE,
5186 p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
5187 p_organization_id IN pqh_gl_interface.organization_id%TYPE,
5188 p_amount IN pqh_gl_interface.amount_dr%TYPE
5189 ) IS
5190 /*
5191 This procedure will insert record into pqh_gl_interface
5192 If the same UOM is repeated more then once then we would update the unposted txn.
5193 */
5194 --
5195 -- local variables
5196 --
5197 l_proc varchar2(72) := g_package||'.insert_pqh_gms_interface';
5198 l_count number(9) := 0 ;
5199
5200 Cursor csr_pqh_gms_interface IS
5201 Select COUNT(*)
5202 From pqh_gl_interface
5203 Where budget_version_id = g_budget_version_id
5204 AND budget_detail_id = p_budget_detail_id
5205 AND p_period_name = p_period_name
5206 AND posting_type_cd = 'BUDGET'
5207 AND project_id = p_project_id
5208 AND task_id = p_task_id
5209 AND award_id = p_award_id
5210 AND expenditure_type = p_expenditure_type
5211 AND organization_id = p_organization_id
5212 AND NVL(adjustment_flag,'N') = 'N'
5213 AND status IS NULL
5214 AND posting_date IS NULL;
5215
5216 BEGIN
5217
5218 hr_utility.set_location('Entering: '||l_proc, 5);
5219
5220 -- check if its a repeat of that same UOM
5221 OPEN csr_pqh_gms_interface;
5222 FETCH csr_pqh_gms_interface INTO l_count;
5223 CLOSE csr_pqh_gms_interface;
5224
5225 hr_utility.set_location('l_count in Insert pqh_gl_interface : '||l_count,10);
5226
5227 IF l_count <> 0 THEN
5228
5229 -- this is a repeat of UOM , so update the first one adding the new amount
5230 UPDATE pqh_gl_interface
5231 SET AMOUNT_DR = NVL(AMOUNT_DR,0) + NVL(p_amount,0)
5232 WHERE budget_version_id = g_budget_version_id
5233 AND budget_detail_id = p_budget_detail_id
5234 AND p_period_name = p_period_name
5235 AND posting_type_cd = 'BUDGET'
5236 AND project_id = p_project_id
5237 AND task_id = p_task_id
5238 AND award_id = p_award_id
5239 AND expenditure_type = p_expenditure_type
5240 AND organization_id = p_organization_id
5241 AND NVL(adjustment_flag,'N') = 'N'
5242 AND status IS NULL
5243 AND posting_date IS NULL;
5244
5245 ELSE
5246
5247 -- insert this record
5248 INSERT INTO pqh_gl_interface
5249 (
5250 gl_interface_id,
5251 budget_version_id,
5252 budget_detail_id,
5253 period_name,
5254 project_id,
5255 task_id,
5256 award_id,
5257 expenditure_type,
5258 organization_id,
5259 amount_dr,
5260 amount_cr,
5261 currency_code,
5262 status,
5263 adjustment_flag,
5264 posting_date,
5265 posting_type_cd
5266 )
5267 VALUES
5268 (
5269 pqh_gl_interface_s.nextval,
5270 g_budget_version_id,
5271 p_budget_detail_id,
5272 p_period_name,
5273 p_project_id,
5274 p_task_id,
5275 p_award_id,
5276 p_expenditure_type,
5277 p_organization_id,
5278 NVL(p_amount,0),
5279 0,
5280 g_bgt_currency_code,
5281 null,
5282 null,
5283 null,
5284 'BUDGET'
5285 );
5286
5287 END IF; -- l_count <> 0 UOM repeated
5288
5289
5290 hr_utility.set_location('Leaving:'||l_proc, 1000);
5291
5292 EXCEPTION
5293 WHEN OTHERS THEN
5294 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5295 hr_utility.set_message_token('ROUTINE', l_proc);
5296 hr_utility.set_message_token('REASON', SQLERRM);
5297 hr_utility.raise_error;
5298 END insert_pqh_gms_interface;
5299 ---------------------------------------------------------------------------------------------
5300 ---------------------------------------------------------------------------------------------
5301 PROCEDURE update_pqh_gms_interface
5302 (
5303 p_budget_detail_id IN pqh_gl_interface.budget_detail_id%TYPE,
5304 p_period_name IN varchar2,
5305 p_project_id IN pqh_gl_interface.project_id%TYPE,
5306 p_task_id IN pqh_gl_interface.task_id%TYPE,
5307 p_award_id IN pqh_gl_interface.award_id%TYPE,
5308 p_expenditure_type IN pqh_gl_interface.expenditure_type%TYPE,
5309 p_organization_id IN pqh_gl_interface.organization_id%TYPE,
5310 p_amount IN pqh_gl_interface.amount_dr%TYPE
5311 ) IS
5312 /*
5313 This procedure will update pqh_gl_interface and create a adjustment record
5314 */
5315 --
5316 -- local variables
5317 --
5318 l_proc varchar2(72) := g_package||'.update_pqh_gms_interface';
5319 l_amount_diff pqh_gl_interface.amount_dr%TYPE :=0;
5320 l_amount_dr pqh_gl_interface.amount_dr%TYPE :=0;
5321 l_amount_cr pqh_gl_interface.amount_cr%TYPE :=0;
5322 l_pqh_gl_interface_rec pqh_gl_interface%ROWTYPE;
5323
5324
5325 CURSOR csr_pqh_gms_interface IS
5326 SELECT *
5327 FROM pqh_gl_interface
5328 WHERE budget_version_id = g_budget_version_id
5329 AND budget_detail_id = p_budget_detail_id
5330 AND period_name = p_period_name
5331 AND posting_type_cd = 'BUDGET'
5332 AND project_id = p_project_id
5333 AND task_id = p_task_id
5334 AND award_id = p_award_id
5335 AND expenditure_type = p_expenditure_type
5336 AND organization_id = p_organization_id
5337 AND NVL(adjustment_flag,'N') = 'N'
5338 AND status IS NOT NULL
5339 AND posting_date IS NOT NULL
5340 FOR UPDATE of amount_dr;
5341
5342
5343 BEGIN
5344
5345 hr_utility.set_location('Entering: '||l_proc, 5);
5346
5347 OPEN csr_pqh_gms_interface;
5348 FETCH csr_pqh_gms_interface INTO l_pqh_gl_interface_rec;
5349
5350 l_amount_diff := NVL(p_amount,0) - NVL(l_pqh_gl_interface_rec.amount_dr,0);
5351
5352 IF l_amount_diff > 0 THEN
5353 -- debit as new is more then old
5354 l_amount_dr := l_amount_diff;
5355 ELSE
5356 -- credit as new is less then old
5357 l_amount_cr := (-1)*l_amount_diff;
5358 END IF;
5359 -- update the pqh_gl_interface table
5360 UPDATE pqh_gl_interface
5361 SET amount_dr = NVL(p_amount,0)
5362 WHERE CURRENT OF csr_pqh_gms_interface;
5363
5364 CLOSE csr_pqh_gms_interface;
5365
5366 -- create i.e insert a adjustment record ONLY if l_amount_diff <> 0
5367 IF NVL(l_amount_diff,0) <> 0 THEN
5368
5369 INSERT INTO pqh_gl_interface
5370 (
5371 gl_interface_id,
5372 budget_version_id,
5373 budget_detail_id,
5374 period_name,
5375 project_id,
5376 task_id,
5377 award_id,
5378 expenditure_type,
5379 organization_id,
5380 amount_dr,
5381 amount_cr,
5382 currency_code,
5383 status,
5384 adjustment_flag,
5385 posting_date,
5386 posting_type_cd
5387 )
5388 VALUES
5389 (
5390 pqh_gl_interface_s.nextval,
5391 g_budget_version_id,
5392 p_budget_detail_id,
5393 p_period_name,
5394 p_project_id,
5395 p_task_id,
5396 p_award_id,
5397 p_expenditure_type,
5398 p_organization_id,
5399 NVL(l_amount_dr,0),
5400 NVL(l_amount_cr,0),
5401 g_bgt_currency_code,
5402 null,
5403 'Y',
5404 null,
5405 'BUDGET'
5406 );
5407
5408 END IF; -- create i.e insert a adjustment record ONLY if l_amount_diff <> 0
5409
5410
5411 hr_utility.set_location('Leaving:'||l_proc, 1000);
5412
5413 EXCEPTION
5414 WHEN OTHERS THEN
5415 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5416 hr_utility.set_message_token('ROUTINE', l_proc);
5417 hr_utility.set_message_token('REASON', SQLERRM);
5418 hr_utility.raise_error;
5419 END update_pqh_gms_interface;
5420 --------------------------------------------------------------------------------------------------------
5421 function get_gms_rejection_msg (p_rejection_code in varchar2) return varchar2 is
5422 cursor rej_msg is
5423 SELECT substr(l.description, 1, 240 ) meaning
5424 FROM pa_lookups l
5425 WHERE l.lookup_type in ('TRANSACTION REJECTION REASON','FC_RESULT_CODE',
5426 'COST DIST REJECTION CODE','INVOICE_CURRENCY',
5427 'TRANSACTION USER REJ REASON')
5428 AND l.lookup_code = p_rejection_code
5429 UNION all
5430 SELECT message_text
5431 FROM fnd_new_messages fnd
5432 WHERE language_code = userenv('lang')
5433 AND fnd.message_name = p_rejection_code
5434 AND application_id = 275; -- PA
5435
5436 l_message fnd_new_messages.message_text%Type;
5437 begin
5438 open rej_msg;
5439 fetch rej_msg into l_message;
5440 close rej_msg;
5441
5442 return l_message;
5443 end;
5444
5445 ---------------------------------------------------------------------------------------------------------
5446 PROCEDURE gms_pqh_tie_back
5447 (
5448 p_gms_batch_name IN VARCHAR2
5449 )
5450 IS
5451 /*
5452 This procedure ties back all the transactions posted into Oracle Grants Mgmt with records in pqh_gl_interface
5453 In case of failure the status in pqh_gl_interface is updated to error
5454 */
5455 --
5456 -- Cursor to get records rejected by import process
5457 --
5458 CURSOR gms_tie_back_reject_cur IS
5459 SELECT
5460 nvl(transaction_rejection_code,'P') rejection_code,
5461 orig_transaction_reference,
5462 transaction_status_code
5463 FROM pa_transaction_interface_all
5464 WHERE transaction_source = 'GMSEPQHBC'
5465 AND batch_name = p_gms_batch_name
5466 AND transaction_status_code in ('R', 'PI', 'PR', 'PO');
5467
5468
5469 l_proc varchar2(72) := g_package||'.gms_pqh_tie_back';
5470 l_int_id BINARY_INTEGER;
5471 l_cnt number;
5472
5473
5474 Begin
5475 hr_utility.set_location('Entering:'||l_proc, 5);
5476 --
5477 -- If transaction_status_code = 'P' then the transaction import process did not kick off
5478 -- for some reason.
5479 -- If transaction_status_code = 'I' then the transaction import process did not complete
5480 -- the Post Processing extension.
5481 -- In both cases import for all records failed
5482 --
5483 SELECT
5484 count(*) into l_cnt
5485 FROM pa_transaction_interface_all
5486 WHERE transaction_source = 'GMSEPQHBC'
5487 And batch_name = p_gms_batch_name
5488 And transaction_status_code in ('P', 'I');
5489
5490 --
5491 -- IF import for all records failed then update status in pqh_gl_interface to error
5492 --
5493 IF l_cnt > 0
5494 THEN
5495
5496 hr_utility.set_location('GMS Import is not Complete:'||to_char(l_cnt), 10);
5497 --
5498 hr_utility.set_message(8302,'PQH_TR_GMS_IMP_FAILED');
5499 populate_globals_error
5500 (
5501 p_message_text => FND_MESSAGE.get
5502 );
5503 RAISE g_error_exception;
5504 --
5505 ELSE
5506 hr_utility.set_location('GMS Import is complete', 15);
5507 --
5508 FOR reject_rec in gms_tie_back_reject_cur
5509 LOOP
5510 l_int_id := to_number(substr(reject_rec.orig_transaction_reference,
5511 instr(reject_rec.orig_transaction_reference,'-')+1));
5512 hr_utility.set_location('Import failed for:'||l_int_id, 20);
5513 hr_utility.set_location('Failure Code: '||reject_rec.rejection_code, 22);
5514
5515 populate_globals_error (
5516 p_message_text => get_gms_rejection_msg(reject_rec.rejection_code));
5517
5518 begin
5519
5520 UPDATE pqh_gl_interface
5521 SET status='ERROR',posting_date=sysdate
5522 WHERE period_name =g_gms_import_tab(l_int_id).period_name And
5523 project_id =g_gms_import_tab(l_int_id).project_id And
5524 task_id =g_gms_import_tab(l_int_id).task_id And
5525 award_id =g_gms_import_tab(l_int_id).award_id And
5526 expenditure_type =g_gms_import_tab(l_int_id).expenditure_type And
5527 organization_id =g_gms_import_tab(l_int_id).organization_id;
5528
5529 EXCEPTION
5530 when no_data_found then
5531 null;
5532 WHEN g_error_exception THEN
5533 RAISE;
5534 WHEN OTHERS THEN
5535 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5536 hr_utility.set_message_token('ROUTINE', l_proc||l_int_id);
5537 hr_utility.set_message_token('REASON', SQLERRM);
5538 hr_utility.raise_error;
5539 end;
5540 END LOOP;
5541 --
5542
5543 END IF;
5544 --
5545 --For each record that failed in import update budget_detail status
5546 --
5547 hr_utility.set_location('Set Budget Detail status to Error', 25);
5548 begin
5549 UPDATE pqh_budget_details
5550 SET gl_status = 'ERROR'
5551 Where budget_detail_id in (select budget_detail_id from pqh_gl_interface where
5552 budget_version_id=g_budget_version_id
5553 And cost_allocation_keyflex_id is null
5554 And status='ERROR'
5555 );
5556 exception
5557 when no_data_found then
5558 null;
5559 WHEN g_error_exception THEN
5560 RAISE;
5561 WHEN OTHERS THEN
5562 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5563 hr_utility.set_message_token('ROUTINE', l_proc||'2');
5564 hr_utility.set_message_token('REASON', SQLERRM);
5565 hr_utility.raise_error;
5566 end;
5567 hr_utility.set_location('Leaving:'||l_proc, 100);
5568
5569 EXCEPTION
5570 WHEN g_error_exception THEN
5571 RAISE;
5572 WHEN OTHERS THEN
5573 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5574 hr_utility.set_message_token('ROUTINE', l_proc);
5575 hr_utility.set_message_token('REASON', SQLERRM);
5576 hr_utility.raise_error;
5577 END gms_pqh_tie_back;
5578
5579
5580 -----------------------------------------------------------------------------------------------------------
5581 -----------------------------------------------------------------------------------------------------------
5582 PROCEDURE purge_pa_tables(
5583 p_gms_batch_name IN varchar2
5584 )
5585 IS
5586 /*
5587 Procedure to purge records from pa_transaction_interface_all and gms_transaction_interface_all
5588 once Import process is complete
5589 */
5590 l_proc varchar2(72) := g_package||'.purge_pa_tables';
5591 PRAGMA AUTONOMOUS_TRANSACTION;
5592
5593 BEGIN
5594 hr_utility.set_location('Entering:'||l_proc, 10);
5595 DELETE pa_transaction_interface_all
5596 WHERE batch_name = p_gms_batch_name
5597 And transaction_source = 'GMSEPQHBC';
5598
5599 hr_utility.set_location('Deleted pa_transaction_interface_all:',20);
5600
5601 DELETE gms_transaction_interface_all
5602 WHERE batch_name = p_gms_batch_name
5603 And transaction_source = 'GMSEPQHBC';
5604
5605 hr_utility.set_location('Deleted gms_transaction_interface_all:',30);
5606 COMMIT;
5607 hr_utility.set_location('Transaction commited:',40);
5608 hr_utility.set_location('Leaving:'||l_proc, 100);
5609 EXCEPTION
5610 WHEN OTHERS THEN
5611 ROLLBACK;
5612 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5613 hr_utility.set_message_token('ROUTINE', l_proc);
5614 hr_utility.set_message_token('REASON', SQLERRM);
5615 hr_utility.raise_error;
5616 END purge_pa_tables;
5617 ------------------------------------------------------------------------------------------------------------
5618 ------------------------------------------------------------------------------------------------------------
5619 PROCEDURE populate_pa_tables(
5620 p_gms_batch_name OUT NOCOPY varchar2,
5621 p_call_status OUT NOCOPY BOOLEAN
5622 )
5623 IS
5624 /*
5625 This procedure populates pa_transaction_interface_all and gms_transaction_interface_all tables
5626 and submits conc request to import records in to projects.
5627 It waits till conc request is complete
5628 */
5629 gms_rec gms_transaction_interface_all%ROWTYPE;
5630 l_proc varchar2(72) := g_package||'.populate_pa_tables';
5631 call_status BOOLEAN;
5632 rphase VARCHAR2(30);
5633 rstatus VARCHAR2(30);
5634 dphase VARCHAR2(30);
5635 dstatus VARCHAR2(30);
5636 message VARCHAR2(240);
5637 l_return_status VARCHAR2(30);
5638 l_txn_interface_id number(15);
5639 req_id NUMBER(15);
5640 PRAGMA AUTONOMOUS_TRANSACTION;
5641 begin
5642 hr_utility.set_location('Entering:'||l_proc, 10);
5643 --
5644 -- Select Batch Name for Transaction
5645 --
5646 Select
5647 'PQH'||to_char(pqh_gms_batch_name_s.nextval) INTO p_gms_batch_name
5648 From dual;
5649
5650 hr_utility.set_location('Batch Name: '||p_gms_batch_name, 15);
5651
5652 FOR cnt in 1..g_gms_import_tab.COUNT LOOP
5653
5654 hr_utility.set_location('Processing Record:'||g_gms_import_tab(cnt).ORIG_TRANSACTION_REFERENCE, 20);
5655 --
5656 -- Get the transaction_interface_id. We need this to populate the gms_interface table.
5657 --
5658 Select pa_txn_interface_s.nextval
5659 INTO l_txn_interface_id
5660 From dual;
5661 --
5662 -- Insert in to PA_TRANSACTIONS_ALL
5663 --
5664 INSERT INTO PA_TRANSACTION_INTERFACE_ALL
5665 (
5666 TXN_INTERFACE_ID
5667 ,TRANSACTION_SOURCE
5668 ,BATCH_NAME
5669 ,EXPENDITURE_ENDING_DATE
5670 ,ORGANIZATION_NAME
5671 ,EXPENDITURE_ITEM_DATE
5672 ,PROJECT_NUMBER
5673 ,TASK_NUMBER
5674 ,EXPENDITURE_TYPE
5675 ,QUANTITY
5676 ,TRANSACTION_STATUS_CODE
5677 ,ORIG_TRANSACTION_REFERENCE
5678 ,ORG_ID
5679 ,DENOM_CURRENCY_CODE
5680 ,DENOM_RAW_COST
5681 )
5682 VALUES
5683 (
5684 l_txn_interface_id
5685 ,g_gms_import_tab(cnt).TRANSACTION_SOURCE
5686 ,p_gms_batch_name
5687 ,g_gms_import_tab(cnt).EXPENDITURE_ENDING_DATE
5688 ,g_gms_import_tab(cnt).ORGANIZATION_NAME
5689 ,g_gms_import_tab(cnt).EXPENDITURE_ITEM_DATE
5690 ,g_gms_import_tab(cnt).PROJECT_NUMBER
5691 ,g_gms_import_tab(cnt).TASK_NUMBER
5692 ,g_gms_import_tab(cnt).EXPENDITURE_TYPE
5693 ,g_gms_import_tab(cnt).QUANTITY
5694 ,'P'
5695 ,g_gms_import_tab(cnt).ORIG_TRANSACTION_REFERENCE
5696 ,g_gms_import_tab(cnt).ORG_ID
5697 ,g_gms_import_tab(cnt).DENOM_CURRENCY_CODE
5698 ,g_gms_import_tab(cnt).amount
5699 );
5700
5701
5702 --
5703 -- insert into gms_interface table
5704 --
5705
5706 GMS_REC.TXN_INTERFACE_ID := l_txn_interface_id;
5707 GMS_REC.BATCH_NAME := p_gms_batch_name;
5708 GMS_REC.TRANSACTION_SOURCE := g_gms_import_tab(cnt).TRANSACTION_SOURCE;
5709 GMS_REC.EXPENDITURE_ENDING_DATE := g_gms_import_tab(cnt).EXPENDITURE_ENDING_DATE;
5710 GMS_REC.EXPENDITURE_ITEM_DATE := g_gms_import_tab(cnt).EXPENDITURE_ITEM_DATE ;
5711 GMS_REC.PROJECT_NUMBER := g_gms_import_tab(cnt).PROJECT_NUMBER;
5712 GMS_REC.TASK_NUMBER := g_gms_import_tab(cnt).TASK_NUMBER;
5713 GMS_REC.AWARD_ID := g_gms_import_tab(cnt).AWARD_ID ;
5714 GMS_REC.EXPENDITURE_TYPE := g_gms_import_tab(cnt).EXPENDITURE_TYPE;
5715 GMS_REC.TRANSACTION_STATUS_CODE := 'P';
5716 GMS_REC.ORIG_TRANSACTION_REFERENCE := g_gms_import_tab(cnt).ORIG_TRANSACTION_REFERENCE;
5717 GMS_REC.ORG_ID := g_gms_import_tab(cnt).ORG_ID;
5718 GMS_REC.SYSTEM_LINKAGE := NULL;
5719 GMS_REC.USER_TRANSACTION_SOURCE := NULL;
5720 GMS_REC.TRANSACTION_TYPE := NULL;
5721 GMS_REC.BURDENABLE_RAW_COST := g_gms_import_tab(cnt).AMOUNT;
5722 GMS_REC.FUNDING_PATTERN_ID := NULL;
5723
5724 gms_transactions_pub.LOAD_GMS_XFACE_API(gms_rec, l_return_status);
5725
5726 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5727 hr_utility.set_location('gms_transactions_pub failed', 25);
5728 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5729 END IF;
5730
5731 END LOOP; -- g_gms_import_tab
5732
5733
5734
5735 IF g_gms_import_tab.COUNT > 0
5736 THEN
5737 hr_utility.set_location('Submitting Request for batch: '||p_gms_batch_name, 30);
5738 req_id := fnd_request.submit_request(
5739 'PA',
5740 'PAXTRTRX',
5741 NULL,
5742 NULL,
5743 FALSE,
5744 'GMSEPQHBC',
5745 p_gms_batch_name
5746 );
5747
5748 IF req_id = 0
5749 THEN
5750 hr_utility.set_location('Conc Request not submitted properly', 35);
5751 ROLLBACK;
5752 p_call_status :=false;
5753 ELSE
5754 hr_utility.set_location('Transaction commited', 40);
5755 COMMIT;
5756 call_status := fnd_concurrent.wait_for_request(req_id, 20, 0,
5757 rphase, rstatus,
5758 dphase, dstatus,
5759 message
5760 );
5761 p_call_status := call_status;
5762 END IF;
5763 END IF;
5764 EXCEPTION
5765 WHEN OTHERS THEN
5766 ROLLBACK;
5767 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
5768 hr_utility.set_message_token('ROUTINE', l_proc);
5769 hr_utility.set_message_token('REASON', SQLERRM);
5770 hr_utility.raise_error;
5771 END populate_pa_tables;
5772 -----------------------------------------------------------------------------------------------------------
5773 ----------------------------------------------------------------------------------------------------------
5774
5775 PROCEDURE populate_gms_tables IS
5776 /*
5777 This procedure transfers records from pqh_gl_interface to pa_transaction_interface_all,
5778 kicks off the TRANSACTION IMPORT program in GMS
5779 */
5780
5781 ---------------Local Variables---------------------------------------------
5782 l_bg_id NUMBER(15) ;
5783 l_org_name hr_all_organization_units_tl.name%TYPE;
5784 l_seg1 VARCHAR2(25);
5785 l_task_number pa_tasks.task_number%TYPE;
5786 l_gms_batch_name VARCHAR2(10);
5787 l_exp_end_dt DATE;
5788 l_call_status BOOLEAN;
5789 l_value VARCHAR2(200);
5790 l_table VARCHAR2(100);
5791 l_org_id NUMBER(15);
5792 l_effective_date DATE := trunc(sysdate);
5793 l_gms_transaction_source varchar2(30);
5794 l_amount NUMBER;
5795 tran_setup_exception EXCEPTION;
5796 tran_source_exception EXCEPTION;
5797 l_pqh_interface_rec pqh_gl_interface%ROWTYPE;
5798 l_log_context pqh_process_log.log_context%TYPE;
5799 l_proc varchar2(72) := g_package||'.populate_gms_interface';
5800 l_log_message varchar2(8000);
5801 cnt BINARY_INTEGER := 1;
5802 ref_cnt BINARY_INTEGER := 1;
5803 l_period_name NUMBER;
5804 -----------------------------------------------------------------------
5805 Cursor csr_budget_bg IS
5806 Select business_group_id
5807 From pqh_budgets
5808 Where budget_id=g_budget_id;
5809
5810 Cursor csr_tran_srcs IS
5811 Select transaction_source
5812 From pa_transaction_sources
5813 Where transaction_source = 'GMSEPQHBC';
5814
5815 Cursor csr_pqh_gms_interface IS
5816 Select period_name,project_id,award_id,task_id,
5817 expenditure_type,organization_id,
5818 currency_code,
5819 SUM(NVL(amount_dr,0)) amount_dr,
5820 SUM(NVL(amount_cr,0)) amount_cr
5821 From pqh_gl_interface
5822 Where budget_version_id = g_budget_version_id
5823 AND posting_type_cd = 'BUDGET'
5824 AND status IS NULL
5825 AND posting_date IS NULL
5826 AND cost_allocation_keyflex_id IS NULL
5827 group by
5828 period_name,project_id,award_id,task_id,
5829 expenditure_type,organization_id,currency_code;
5830
5831
5832 Cursor csr_hr_org_name(p_organization_id NUMBER) is
5833 Select name
5834 From hr_organization_units
5835 Where organization_id = p_organization_id
5836 AND business_group_id = l_bg_id;
5837
5838 Cursor csr_pa_project_num (p_project_id NUMBER) IS
5839 Select segment1,org_id
5840 From pa_projects_all
5841 Where project_id = p_project_id;
5842
5843
5844 Cursor csr_pa_task_num(p_task_id NUMBER) IS
5845 Select task_number
5846 From pa_tasks
5847 Where task_id = p_task_id;
5848
5849
5850
5851 BEGIN
5852 hr_utility.set_location('Entering: '||l_proc, 5);
5853
5854 OPEN csr_budget_bg;
5855 FETCH csr_budget_bg INTO l_bg_id;
5856 IF (csr_budget_bg%NOTFOUND) THEN
5857 CLOSE csr_budget_bg;
5858 l_value := 'Business Group Id';
5859 l_table := 'pqh_budgets';
5860 RAISE tran_setup_exception;
5861 else
5862 CLOSE csr_budget_bg;
5863 END IF;
5864 --
5865 --Check if Transaction source is present .Other wise exit program
5866 --
5867 OPEN csr_tran_srcs;
5868 FETCH csr_tran_srcs INTO l_gms_transaction_source;
5869 IF (csr_tran_srcs%NOTFOUND) THEN
5870 CLOSE csr_tran_srcs;
5871 l_value := 'Transaction source ='||'GMSEPQHBC';
5872 l_table := 'pa_transaction_sources';
5873 RAISE tran_source_exception;
5874 else
5875 CLOSE csr_tran_srcs;
5876 END IF;
5877
5878 hr_utility.set_location('Transaction Source: '||l_gms_transaction_source, 10);
5879
5880 l_exp_end_dt := nvl(pa_utils.getweekending(sysdate),sysdate);
5881 --
5882 --Prepare a batch containing all records to be imported
5883 --
5884 For C1 in csr_pqh_gms_interface LOOP
5885 hr_utility.set_location('Processing Period: '||C1.period_name, 20);
5886 l_period_name := to_number(C1.period_name);
5887 --
5888 -- Fetch Hr Org Name
5889 --
5890 hr_utility.set_location('organization : '||C1.organization_id, 21);
5891 OPEN csr_hr_org_name (C1.organization_id);
5892 FETCH csr_hr_org_name INTO l_org_name;
5893 IF (csr_hr_org_name%NOTFOUND) THEN
5894 CLOSE csr_hr_org_name;
5895 l_value := 'Org id ='||to_char(C1.organization_id);
5896 l_table := 'HR_ORGANIZATION_UNITS';
5897 RAISE tran_setup_exception;
5898 else
5899 CLOSE csr_hr_org_name;
5900 hr_utility.set_location('org name : '||l_org_name, 22);
5901 END IF;
5902 --
5903 -- Fetch Project Number and Project Oraganization Id
5904 --
5905 hr_utility.set_location('project : '||C1.project_id, 23);
5906 OPEN csr_pa_project_num (C1.project_id);
5907 FETCH csr_pa_project_num INTO l_seg1,l_org_id;
5908 IF (csr_pa_project_num%NOTFOUND) THEN
5909 CLOSE csr_pa_project_num;
5910 l_value := 'Project id ='||to_char(C1.project_id);
5911 l_table := 'PA_PROJECTS_ALL';
5912 RAISE tran_setup_exception;
5913 else
5914 CLOSE csr_pa_project_num;
5915 hr_utility.set_location('project number : '||l_seg1, 22);
5916 END IF;
5917 --
5918 --Fetch Task Number
5919 --
5920 hr_utility.set_location('task : '||C1.task_id, 24);
5921 OPEN csr_pa_task_num (C1.task_id);
5922 FETCH csr_pa_task_num INTO l_task_number;
5923 IF (csr_pa_task_num%NOTFOUND) THEN
5924 CLOSE csr_pa_task_num;
5925 l_value := 'Task id ='||to_char(C1.task_id);
5926 l_table := 'PA_TASKS';
5927 RAISE tran_setup_exception;
5928 else
5929 CLOSE csr_pa_task_num;
5930 hr_utility.set_location('task num: '||l_task_number, 25);
5931 END IF;
5932 l_amount := C1.amount_dr + C1.amount_cr;
5933 hr_utility.set_location('setting tab row '||cnt, 26);
5934
5935 select pqh_gms_orig_txn_reference_s.nextval
5936 into ref_cnt
5937 from dual;
5938
5939 g_gms_import_tab(cnt).EXPENDITURE_ENDING_DATE :=l_exp_end_dt;
5940 g_gms_import_tab(cnt).ORGANIZATION_NAME :=l_org_name;
5941 g_gms_import_tab(cnt).EXPENDITURE_ITEM_DATE :=l_effective_date;
5942 g_gms_import_tab(cnt).PROJECT_NUMBER :=l_seg1;
5943 g_gms_import_tab(cnt).TASK_NUMBER :=l_task_number;
5944 g_gms_import_tab(cnt).QUANTITY :=1;
5945 g_gms_import_tab(cnt).ORIG_TRANSACTION_REFERENCE :='PQH'||ref_cnt||'-'||cnt;
5946 g_gms_import_tab(cnt).ORG_ID :=l_org_id;
5947 g_gms_import_tab(cnt).TRANSACTION_SOURCE :='GMSEPQHBC';
5948 g_gms_import_tab(cnt).Amount :=l_amount;
5949 g_gms_import_tab(cnt).DENOM_CURRENCY_CODE :=C1.currency_code;
5950 g_gms_import_tab(cnt).PERIOD_NAME :=C1.PERIOD_NAME;
5951 g_gms_import_tab(cnt).PROJECT_ID :=C1.PROJECT_ID;
5952 g_gms_import_tab(cnt).TASK_ID :=C1.TASK_ID;
5953 g_gms_import_tab(cnt).AWARD_ID :=C1.AWARD_ID;
5954 g_gms_import_tab(cnt).EXPENDITURE_TYPE :=C1.expenditure_type;
5955 g_gms_import_tab(cnt).ORGANIZATION_ID :=C1.ORGANIZATION_ID;
5956
5957 hr_utility.set_location('end setting tab row '||cnt, 27);
5958 cnt := cnt + 1;
5959
5960 END LOOP;
5961
5962 IF not g_validate THEN
5963 hr_utility.set_location('not validate mode : ', 30);
5964 hr_utility.set_location('calling populate_pa_tab : ', 31);
5965
5966 populate_pa_tables(l_gms_batch_name,l_call_status);
5967
5968 hr_utility.set_location('done calling populate_pa_tab : ', 32);
5969 IF l_call_status THEN
5970 hr_utility.set_location('for call back : ', 33);
5971 gms_pqh_tie_back(l_gms_batch_name);
5972 hr_utility.set_location('done call back : ', 34);
5973 END IF;
5974 purge_pa_tables(l_gms_batch_name);
5975 IF not l_call_status THEN
5976 hr_utility.set_message(8302,'PQH_TR_GMS_IMP_FAILED');
5977 populate_globals_error
5978 (
5979 p_message_text => FND_MESSAGE.get
5980 );
5981 RAISE g_error_exception;
5982 END IF;
5983 END IF;
5984
5985 hr_utility.set_location('Leaving: '||l_proc, 1000);
5986
5987 EXCEPTION
5988 WHEN tran_source_exception THEN
5989 hr_utility.set_message(8302,'PQH_TR_VALUE_NOT_FOUND');
5990 hr_utility.set_message_token('ROUTINE', l_proc);
5991 hr_utility.set_message_token('VALUE',l_value);
5992 hr_utility.set_message_token('TABLE',l_table);
5993 populate_globals_error
5994 (
5995 p_message_text => FND_MESSAGE.get
5996 );
5997 RAISE g_error_exception;
5998
5999 WHEN tran_setup_exception THEN
6000 hr_utility.set_message(8302,'PQH_TR_VALUE_NOT_FOUND');
6001 hr_utility.set_message_token('ROUTINE', l_proc);
6002 hr_utility.set_message_token('VALUE',l_value);
6003 hr_utility.set_message_token('TABLE',l_table);
6004 -- set the context
6005 set_bpr_log_context
6006 (
6007 p_budget_period_id =>l_period_name,
6008 p_log_context => l_log_context
6009 );
6010 pqh_process_batch_log.set_context_level
6011 (
6012 p_txn_id => l_period_name,
6013 p_txn_table_route_id => g_table_route_id_bpr,
6014 p_level => 1,
6015 p_log_context => l_log_context
6016 );
6017
6018 -- insert error
6019 pqh_process_batch_log.insert_log
6020 (
6021 p_message_type_cd => 'ERROR',
6022 p_message_text => fnd_message.get
6023 );
6024 RAISE g_error_exception;
6025 WHEN g_error_exception THEN
6026 RAISE ;
6027 WHEN OTHERS THEN
6028 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
6029 hr_utility.set_message_token('ROUTINE', l_proc);
6030 hr_utility.set_message_token('REASON', SQLERRM);
6031 hr_utility.raise_error;
6032 END populate_gms_tables;
6033
6034 /**************************************************************/
6035
6036 PROCEDURE ins_gl_bc_run_fund_check
6037 ( p_packet_id IN gl_bc_packets.packet_id%TYPE
6038 ,p_code_combination_id IN pqh_gl_interface.code_combination_id%TYPE
6039 ,p_period_name IN pqh_gl_interface.period_name%TYPE
6040 ,p_period_year IN gl_period_statuses.period_year%TYPE
6041 ,p_period_num IN gl_period_statuses.period_num%TYPE
6042 ,p_quarter_num IN gl_period_statuses.quarter_num%TYPE
6043 ,p_currency_code IN pqh_gl_interface.currency_code%TYPE
6044 ,p_entered_dr IN pqh_gl_interface.amount_dr%TYPE
6045 ,p_entered_cr IN pqh_gl_interface.amount_cr%TYPE
6046 ,p_accounted_dr IN pqh_gl_interface.amount_dr%TYPE
6047 ,p_accounted_cr IN pqh_gl_interface.amount_cr%TYPE
6048 ,p_cost_allocation_keyflex_id IN pqh_gl_interface.cost_allocation_keyflex_id%TYPE
6049 ,p_fc_mode IN varchar2
6050 ,p_fc_success OUT NOCOPY boolean
6051 ,p_fc_return OUT NOCOPY varchar2
6052 )
6053 IS
6054 /*
6055 This procedure Inserts in gl_bc_packets , commits so that the data is available
6056 for the autonomous funds checker and runs funds checker returns as argument funds
6057 checker return code and success flag
6058 */
6059 --
6060 -- local variables
6061 --
6062 l_proc varchar2(72) := g_package||'.ins_gl_bc_run_fund_check';
6063 l_fc_success boolean;
6064 l_fc_return varchar2(100);
6065 l_session_id gl_bc_packets.session_id%TYPE DEFAULT -1;
6066 l_serial_id gl_bc_packets.serial_id%TYPE DEFAULT -1;
6067 l_application_id gl_bc_packets.application_id%TYPE ;
6068 PRAGMA AUTONOMOUS_TRANSACTION;
6069
6070 BEGIN
6071 hr_utility.set_location('Entering: '||l_proc, 5);
6072
6073 -- get the session details and application_id to insert into gl_bc_packets (Bug Fix 6769905)
6074 -- session id and serial id is fetched from v$session, same as that in psa_funds_checker_pkg.get_session_details
6075 select s.audsid, s.serial#
6076 into l_session_id, l_serial_id
6077 from v$session s, v$process p
6078 where s.paddr = p.addr
6079 and s.audsid = USERENV('SESSIONID');
6080
6081 l_application_id := fnd_global.resp_appl_id;
6082
6083
6084 INSERT INTO gl_bc_packets
6085 (packet_id,
6086 ledger_id,
6087 je_source_name,
6088 je_category_name,
6089 code_combination_id,
6090 actual_flag,
6091 period_name,
6092 period_year,
6093 period_num,
6094 quarter_num,
6095 currency_code,
6096 status_code,
6097 last_update_date,
6098 last_updated_by,
6099 budget_version_id,
6100 entered_dr,
6101 entered_cr,
6102 accounted_dr,
6103 accounted_cr,
6104 reference1,
6105 reference2,
6106 -- Added these three columns for Bug 6769905 fix, only for R12
6107 SESSION_ID,
6108 SERIAL_ID,
6109 APPLICATION_ID
6110 )
6111 VALUES
6112 (p_packet_id,
6113 g_set_of_books_id,
6114 g_user_je_source_name,
6115 g_user_je_category_name,
6116 p_code_combination_id,
6117 'B',
6118 p_period_name,
6119 p_period_year,
6120 p_period_num,
6121 p_quarter_num,
6122 p_currency_code,
6123 'P',
6124 sysdate,
6125 8302,
6126 g_gl_budget_version_id,
6127 p_entered_dr,
6128 p_entered_cr,
6129 p_accounted_dr,
6130 p_accounted_cr,
6131 g_budget_version_id,
6132 p_cost_allocation_keyflex_id,
6133 -- Added these three columns for Bug 6769905 fix, only for R12
6134 l_session_id,
6135 l_serial_id,
6136 l_application_id);
6137
6138 -- Funds Checker is run in autonomous mode.
6139 -- Commit so that the gl_bc_packets records are visible to fundschecker
6140 commit;
6141
6142 hr_utility.set_location('Calling GL fund checker in Mode : '||p_fc_mode,100);
6143
6144 l_fc_success := PSA_FUNDS_CHECKER_PKG.GLXFCK
6145 (
6146 p_ledgerid => g_set_of_books_id,
6147 p_packetid => p_packet_id,
6148 p_mode => p_fc_mode,
6149 p_conc_flag => 'Y',
6150 p_return_code => l_fc_return,
6151 p_calling_prog_flag => 'H'
6152 );
6153
6154 hr_utility.set_location('GL Fund Checker return Code : '||l_fc_return,110);
6155
6156 p_fc_success := l_fc_success;
6157 p_fc_return := l_fc_return;
6158
6159 -- commit the autonomous transaction
6160 commit;
6161
6162 hr_utility.set_location('Leaving:'||l_proc, 1000);
6163
6164 end ins_gl_bc_run_fund_check;
6165
6166
6167 END pqh_gl_posting;
[Home] [Help]