1 PACKAGE BODY PQH_PSP_INTEGRATION AS
2 /* $Header: pqhpspkg.pkb 115.4 2003/09/24 23:35:50 kgowripe noship $ */
3 --
4 -----------------------------------------------------------------------------
5 -- | Private Global Definitions
6 -----------------------------------------------------------------------------
7 --
8 g_asg_bgt_tab t_assignment_budget_tab;
9 g_encumbered_entity_tab t_enc_entity_tab;
10 g_package varchar2(25) := 'pqh_psp_integration';
11 --
12
13 -----------------------------------------------------------------------------
14 -- Private procedure to get Encumbered/Liquidated Assignments and Periods
15 -----------------------------------------------------------------------------
16 /*
17 LD call returns a Pl/Sql record with Assignment, Element,Period and Amount
18 details for Encumbered/Liquidated Assignments.
19 But we are interested only in Assignment and Period details and we dont want
20 Element Details.
21 This procedure finds Assignment and Min/Max Encumbered Periods irrespective
22 of Elements Involved.
23 */
24
25 Procedure get_distinct_assignments
26 (
27 p_assg_ele_tab IN psp_pqh_integration.assignment_enc_ld_col
28 ) IS
29 --
30 Cursor csr_period_date(p_time_period_id in number) is
31 Select start_date,end_date
32 From per_time_periods
33 Where time_period_id = p_time_period_id;
34
35 --------Local Variables-----------------------------------------------
36
37 i NUMBER :=1;
38 l_start_date DATE;
39 l_end_date DATE;
40 l_start_period NUMBER;
41 l_end_period NUMBER;
42 l_proc VARCHAR2(72) := g_package||'get_distinct_assignments';
43
44
45 Begin
46 --
47 hr_utility.set_location('Entering: '||l_proc, 5);
48 --
49 g_asg_bgt_tab(1).assignment_id :=p_assg_ele_tab.r_assignment_id(1);
50 l_start_period :=p_assg_ele_tab.r_begin_time_period_id(1);
51 l_end_period := p_assg_ele_tab.r_end_time_period_id(1);
52 --
53 Open csr_period_date(l_start_period);
54 Fetch csr_period_date into l_start_date, l_end_date;
55 Close csr_period_date;
56 g_asg_bgt_tab(1).start_period :=l_start_date;
57 --
58 Open csr_period_date(l_end_period);
59 Fetch csr_period_date into l_start_date, l_end_date;
60 Close csr_period_date;
61 g_asg_bgt_tab(1).end_period :=l_end_date;
62 --
63 For cnt in 2..p_assg_ele_tab.r_assignment_id.COUNT
64 LOOP
65 --
66 IF(g_asg_bgt_tab(i).assignment_id = p_assg_ele_tab.r_assignment_id(cnt))
67
68 THEN
69 --
70 IF (l_start_period <> p_assg_ele_tab.r_begin_time_period_id(cnt) Or
71 l_end_period <> p_assg_ele_tab.r_end_time_period_id(cnt))
72 THEN
73 Open csr_period_date(p_assg_ele_tab.r_begin_time_period_id(cnt));
74 Fetch csr_period_date into l_start_date, l_end_date;
75 Close csr_period_date;
76 IF ( l_start_date < g_asg_bgt_tab(i).start_period)
77 THEN
78 l_start_period :=p_assg_ele_tab.r_begin_time_period_id(cnt);
79 g_asg_bgt_tab(i).start_period :=l_start_date;
80 END IF;
81 Open csr_period_date(p_assg_ele_tab.r_end_time_period_id(cnt));
82 Fetch csr_period_date into l_start_date, l_end_date;
83 Close csr_period_date;
84 IF ( l_end_date > g_asg_bgt_tab(i).end_period)
85 THEN
86 l_end_period :=p_assg_ele_tab.r_end_time_period_id(cnt);
87 g_asg_bgt_tab(i).end_period :=l_end_date;
88 END IF;
89 END IF;
90 --
91 Else
92 --
93 i :=i+1;
94 g_asg_bgt_tab(i).assignment_id :=p_assg_ele_tab.r_assignment_id(cnt);
95 l_start_period :=p_assg_ele_tab.r_begin_time_period_id(cnt);
96 l_end_period := p_assg_ele_tab.r_end_time_period_id(cnt);
97 Open csr_period_date(l_start_period);
98 Fetch csr_period_date into l_start_date, l_end_date;
99 Close csr_period_date;
100 g_asg_bgt_tab(i).start_period :=l_start_date;
101 Open csr_period_date(l_end_period);
102 Fetch csr_period_date into l_start_date, l_end_date;
103 Close csr_period_date;
104 g_asg_bgt_tab(i).end_period :=l_end_date;
105 --
106 END IF;
107
108 --
109 END LOOP;
110 hr_utility.set_location('Leaving:'||l_proc, 1000);
111 --
112 EXCEPTION
113 WHEN OTHERS THEN
114 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
115 hr_utility.set_message_token('ROUTINE', l_proc);
116 hr_utility.set_message_token('REASON', SQLERRM);
117 hr_utility.raise_error;
118 END;
119
120 -----------------------------------------------------------------------------
121 -- Private procedure to get Entities attached to Enc/Liquidated Assignment
122 -----------------------------------------------------------------------------
123 /*
124 Given a set of Assignments and Encumbered Period this procedure will find
125 all Entites attatched to those Assignments in that period.
126 Eg:
127 ---LD Encumbrance Details--- ----Assignment Details-----------------
128 Assignment Start Period End Period Assignment Start Date End date Position
129 A1 01-jan-2000 30-Jun-2000 A1 01-jan-2000 15-jan-2000 P1
130 A1 15-jan-2000 31-mar-2000 P2
131 A1 01-apr-2000 30-jun-2000 P3
132 For this assignment we will pick positions P1,P2,P3.
133
134 Note that Positions may repeate. But when fnding Budget Versions we will take
135 care that Budget Version in not repeated even though position is repeated.
136 */
137 Procedure get_asg_entities IS
138 --
139 Cursor csr_asg_entities(p_assignment_id NUMBER,p_start_date DATE, p_end_date DATE) IS
140 Select position_id ,organization_id,
141 grade_id,job_id,
142 effective_start_date,effective_end_date
143 From per_all_assignments_f
144 Where assignment_id=p_assignment_id And
145 position_id is not null And
146 effective_start_date >= p_start_date And
147 effective_end_date <= p_end_date;
148
149 i NUMBER :=0;
150 l_proc VARCHAR2(72) := g_package||'get_asg_entities';
151 --
152 Begin
153 --
154 hr_utility.set_location('Entering: '||l_proc, 5);
155 For cnt in 1..g_asg_bgt_tab.COUNT
156 LOOP
157 --
158 FOR C1 in csr_asg_entities(g_asg_bgt_tab(cnt).assignment_id,
159 g_asg_bgt_tab(cnt).start_period,
160 g_asg_bgt_tab(cnt).end_period)
161 LOOP
162 --
163 i :=i+1;
164 g_encumbered_entity_tab(i).position_id :=C1.position_id;
165 g_encumbered_entity_tab(i).organization_id :=C1.organization_id;
166 g_encumbered_entity_tab(i).grade_id :=C1.grade_id;
167 g_encumbered_entity_tab(i).job_id :=C1.job_id;
168 g_encumbered_entity_tab(i).start_period :=C1.effective_start_date;
169 g_encumbered_entity_tab(i).end_period :=C1.effective_end_date;
170 --
171 END LOOP;
172 --
173 END LOOP;
174 hr_utility.set_location('Leaving:'||l_proc, 1000);
175 --
176 EXCEPTION
177 WHEN OTHERS THEN
178 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
179 hr_utility.set_message_token('ROUTINE', l_proc);
180 hr_utility.set_message_token('REASON', SQLERRM);
181 hr_utility.raise_error;
182 END;
183
184 -----------------------------------------------------------------------------
185 -- Private procedure to get Budget Versions from Assignment Positions
186 -----------------------------------------------------------------------------
187 /*
188 Given a set of Positions this procedure will find Budget Versions coresponding
189 to those Positions.
190 However Budget Version will be considered only if
191 1.Budget is marked for Transfer to GMS
192 2.Corresponding Budget detail is already posted to Target system
193 3.Budget Version is already posted to Target system
194
195 Also care is taken that Budget version is not repeated even though 2 different
196 positions may belong to same budget version.
197 */
198 Procedure get_budget_versions
199 (
200 l_versions_tab OUT NOCOPY PSP_PQH_INTEGRATION.t_num_15_type
201 ) IS
202 --
203 -----------------------Cursors----------------------------------------------------
204 Cursor csr_position_budget(p_position_id NUMBER,
205 p_start_date date,
206 p_end_date date) IS
207 Select bdt.budget_detail_id ,bdt.budget_version_id
208 From pqh_budget_details bdt,
209 pqh_budget_versions bvr,
210 pqh_budgets bgt
211 Where
212 bdt.position_id=p_position_id And
213 bdt.gl_status='POST' And
214 bvr.budget_version_id=bdt.budget_version_id And
215 bvr.gl_status='POST' And
216 bgt.budget_id=bvr.budget_id And
217 bgt.TRANSFER_TO_GRANTS_FLAG='Y' And
218 bgt.budgeted_entity_cd='POSITION'AND
219 ( (bgt.budget_start_date>=p_start_date and bgt.budget_start_date < p_end_date) Or
220 (bgt.budget_end_date > P_start_date and bgt.budget_end_date <=p_end_date )Or
221 (p_start_date >=bgt.budget_end_date and p_start_date < bgt.budget_end_date )
222 );
223
224
225 Cursor csr_org_budget(p_organization_id NUMBER,
226 p_start_date date,
227 p_end_date date) IS
228 Select bdt.budget_detail_id ,bdt.budget_version_id
229 From pqh_budget_details bdt,
230 pqh_budget_versions bvr,
231 pqh_budgets bgt
232 Where
233 bdt.organization_id=p_organization_id And
234 bdt.gl_status='POST' And
235 bvr.budget_version_id=bdt.budget_version_id And
236 bvr.gl_status='POST' And
237 bgt.budget_id=bvr.budget_id And
238 bgt.TRANSFER_TO_GRANTS_FLAG='Y' And
239 bgt.budgeted_entity_cd='ORGANIZATION'AND
240 ( (bgt.budget_start_date>=p_start_date and bgt.budget_start_date < p_end_date) Or
241 (bgt.budget_end_date > P_start_date and bgt.budget_end_date <=p_end_date )Or
242 (p_start_date >=bgt.budget_end_date and p_start_date < bgt.budget_end_date )
243 );
244
245
246
247 Cursor csr_grade_budget(p_grade_id NUMBER,
248 p_start_date date,
249 p_end_date date) IS
250 Select bdt.budget_detail_id ,bdt.budget_version_id
251 From pqh_budget_details bdt,
252 pqh_budget_versions bvr,
253 pqh_budgets bgt
254 Where
255 bdt.grade_id=p_grade_id And
256 bdt.gl_status='POST' And
257 bvr.budget_version_id=bdt.budget_version_id And
258 bvr.gl_status='POST' And
259 bgt.budget_id=bvr.budget_id And
260 bgt.TRANSFER_TO_GRANTS_FLAG='Y' And
261 bgt.budgeted_entity_cd='GRADE'AND
262 ( (bgt.budget_start_date>=p_start_date and bgt.budget_start_date < p_end_date) Or
263 (bgt.budget_end_date > P_start_date and bgt.budget_end_date <=p_end_date )Or
264 (p_start_date >=bgt.budget_end_date and p_start_date < bgt.budget_end_date )
265 );
266
267
268 Cursor csr_job_budget(p_job_id NUMBER,
269 p_start_date date,
270 p_end_date date) IS
271 Select bdt.budget_detail_id ,bdt.budget_version_id
272 From pqh_budget_details bdt,
273 pqh_budget_versions bvr,
274 pqh_budgets bgt
275 Where
276 bdt.job_id=p_job_id And
277 bdt.gl_status='POST' And
278 bvr.budget_version_id=bdt.budget_version_id And
279 bvr.gl_status='POST' And
280 bgt.budget_id=bvr.budget_id And
281 bgt.TRANSFER_TO_GRANTS_FLAG='Y' And
282 bgt.budgeted_entity_cd='JOB'AND
283 ( (bgt.budget_start_date>=p_start_date and bgt.budget_start_date < p_end_date) Or
284 (bgt.budget_end_date > P_start_date and bgt.budget_end_date <=p_end_date )Or
285 (p_start_date >=bgt.budget_end_date and p_start_date < bgt.budget_end_date )
286 );
287
288 ----------------------------Local Variables-----------------------------------------
289
290 i NUMBER :=0;
291 version_repeated BOOLEAN;
292 l_proc VARCHAR2(72) := g_package||'get_budget_versions';
293
294
295 Begin
296 --
297 hr_utility.set_location('Entering: '||l_proc, 5);
298 --
299 -- For each Encumbered Entity
300 --
301 For cnt in 1..g_encumbered_entity_tab.COUNT
302 LOOP
303 --
304 -- If Assignment has a Position attached
305 --
306 IF g_encumbered_entity_tab(cnt).position_id is NOT NULL
307 THEN
308 FOR C1 in csr_position_budget(g_encumbered_entity_tab(cnt).position_id,
309 g_encumbered_entity_tab(cnt).start_period,
310 g_encumbered_entity_tab(cnt).end_period)
311 LOOP
312 --
313 version_repeated :=false;
314 For inx in 1..l_versions_tab.COUNT
315 LOOP
316 --
317 IF (C1.budget_version_id = l_versions_tab(inx))
318 THEN
319 version_repeated :=true;
320 EXIT;
321 END IF;
322 --
323 END LOOP;
324 IF ( not version_repeated )
325 THEN
326 i :=i+1;
327 l_versions_tab(i) :=C1.budget_version_id;
328 Update pqh_budget_details
329 SET commitment_gl_status='ENC',gl_status='ENC'
330 Where budget_detail_id = C1.budget_detail_id;
331 END IF;
332 --
333 END LOOP;
334 END IF;
335 --
336 --If Assignment has Organization attached
337 --
338 IF g_encumbered_entity_tab(cnt).organization_id is NOT NULL
339 THEN
340 FOR C1 in csr_org_budget(g_encumbered_entity_tab(cnt).organization_id,
341 g_encumbered_entity_tab(cnt).start_period,
342 g_encumbered_entity_tab(cnt).end_period)
343 LOOP
344 --
345 version_repeated :=false;
346 For inx in 1..l_versions_tab.COUNT
347 LOOP
348 --
349 IF (C1.budget_version_id = l_versions_tab(inx))
350 THEN
351 version_repeated :=true;
352 EXIT;
353 END IF;
354 --
355 END LOOP;
356 IF ( not version_repeated )
357 THEN
358 i :=i+1;
359 l_versions_tab(i) :=C1.budget_version_id;
360 Update pqh_budget_details
361 SET commitment_gl_status='ENC',gl_status='ENC'
362 Where budget_detail_id = C1.budget_detail_id;
363 END IF;
364 --
365 END LOOP;
366 END IF;
367 --
368 --If Assignment has Grade attached
369 --
370 IF g_encumbered_entity_tab(cnt).grade_id is NOT NULL
371 THEN
372 FOR C1 in csr_grade_budget(g_encumbered_entity_tab(cnt).grade_id,
373 g_encumbered_entity_tab(cnt).start_period,
374 g_encumbered_entity_tab(cnt).end_period)
375 LOOP
376 --
377 version_repeated :=false;
378 For inx in 1..l_versions_tab.COUNT
379 LOOP
380 --
381 IF (C1.budget_version_id = l_versions_tab(inx))
382 THEN
383 version_repeated :=true;
384 EXIT;
385 END IF;
386 --
387 END LOOP;
388 IF ( not version_repeated )
389 THEN
390 i :=i+1;
391 l_versions_tab(i) :=C1.budget_version_id;
392 Update pqh_budget_details
393 SET commitment_gl_status='ENC',gl_status='ENC'
394 Where budget_detail_id = C1.budget_detail_id;
395 END IF;
396 --
397 END LOOP;
398 END IF;
399 --
400 --If Assignment has Job attached
401 --
402 IF g_encumbered_entity_tab(cnt).job_id is NOT NULL
403 THEN
404 FOR C1 in csr_job_budget(g_encumbered_entity_tab(cnt).job_id,
405 g_encumbered_entity_tab(cnt).start_period,
406 g_encumbered_entity_tab(cnt).end_period)
407 LOOP
408 --
409 version_repeated :=false;
410 For inx in 1..l_versions_tab.COUNT
411 LOOP
412 --
413 IF (C1.budget_version_id = l_versions_tab(inx))
414 THEN
415 version_repeated :=true;
416 EXIT;
417 END IF;
418 --
419 END LOOP;
420 IF ( not version_repeated )
421 THEN
422 i :=i+1;
423 l_versions_tab(i) :=C1.budget_version_id;
424 Update pqh_budget_details
425 SET commitment_gl_status='ENC',gl_status='ENC'
426 Where budget_detail_id = C1.budget_detail_id;
427 END IF;
428 --
429 END LOOP;
430 END IF;
431 --
432 END LOOP;
433 hr_utility.set_location('Leaving:'||l_proc, 1000);
434 --
435 EXCEPTION
436 WHEN OTHERS THEN
437 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
438 hr_utility.set_message_token('ROUTINE', l_proc);
439 hr_utility.set_message_token('REASON', SQLERRM);
440 hr_utility.raise_error;
441 END;
442
443 -----------------------------------------------------------------------------
444 -- Main procedure called by LD Encumbrane and Summarize/Liquidation process
445 -----------------------------------------------------------------------------
446 /*
447 This procedure is called by LD whenever assignments are encumbered or lquidated.
448 This procedure picks up all assignments encumbered by LD and identifies Budget
449 Version involved based on Assignment positions.
450 For each such budget version we will call
451 Budget Transfer process
452 Commitment Calculation process
453 Commitment Transfer Process
454 in that order.
455 These individual processes will take care of relieving PQH commitments.
456 */
457
458 PROCEDURE relieve_budget_commitments( p_calling_process IN VARCHAR2,
459 p_return_status OUT NOCOPY VARCHAR2) IS
460 --
461 l_assignment_enc_ld_table psp_pqh_integration.assignment_enc_ld_col;
462 l_distinct_assignment_table t_assignment_budget_tab;
463 l_encumbered_positions_table PSP_PQH_INTEGRATION.t_num_15_type;
464 l_budget_versions_table PSP_PQH_INTEGRATION.t_num_15_type;
465 l_psp_encumbered BOOLEAN;
466 l_return_status varchar2(2);
467 l_proc varchar2(72) := g_package||'relieve_budget_commitments';
468 l_err varchar2(100);
469 l_ret varchar2(100);
470 l_budget_version number(15);
471 --
472 BEGIN
473
474 hr_utility.set_location('Entering: '||l_proc, 5);
475 PSP_PQH_INTEGRATION.GET_ENCUMBRANCE_DETAILS(
476 P_CALLING_PROCESS =>p_calling_process,
477 P_ASSIGNMENT_ENC_LD_TABLE =>l_assignment_enc_ld_table,
478 P_PSP_ENCUMBERED =>l_psp_encumbered,
479 P_RETURN_STATUS =>l_return_status);
480
481 IF( l_return_status <> FND_API.G_RET_STS_SUCCESS)
482 THEN
483 --
484 p_return_status:=FND_API.G_RET_STS_ERROR;
485 RETURN;
486 --
487 --added for fixing bug#3153433
488 -- check whether the assignments are encumbered or not and proceed.
489 ELSIF NOT (l_psp_encumbered) THEN
490
491 p_return_status:=FND_API.G_RET_STS_SUCCESS;
492 RETURN;
493 --end changes for bug#3153433
494 END IF;
495
496 --Get Assignmetns and Period os Encumbrance
497 get_distinct_assignments(l_assignment_enc_ld_table);
498
499 -- Get Postions attached to Encumbered/Liquidated assignmnts
500 get_asg_entities;
501
502 --Get Budget Versions of Assignment Positions
503 get_budget_versions(l_budget_versions_table);
504
505 --For each Budget Version call Budget Xfer, Commitment Calculation and Commiment Xfer
506 For cnt in 1..l_budget_versions_table.COUNT
507 LOOP
508 --
509 l_budget_version :=l_budget_versions_table(cnt);
510 pqh_commitment_pkg.calculate_commitment(
511 errbuf =>l_err,
512 retcode => l_ret,
513 p_budgeted_entity_cd =>'POSITION',
514 p_budget_version_id =>l_budget_version
515 );
516 pqh_gl_posting.post_budget(
517 p_budget_version_id=>l_budget_version,
518 p_status =>l_ret
519 );
520
521 pqh_commitment_posting.post_budget_commitment(
522 errbuf=>l_err,
523 retcode => l_ret,
524 p_effective_date =>sysdate,
525 p_budget_version_id=>l_budget_version
526 );
527 --
528 END LOOP;
529
530 p_return_status:=FND_API.G_RET_STS_SUCCESS;
531 hr_utility.set_location('Leaving:'||l_proc, 1000);
532 EXCEPTION
533 WHEN OTHERS THEN
534 ROLLBACK ;
535 p_return_status:=FND_API.G_RET_STS_ERROR;
536 hr_utility.set_message(8302, 'PQH_CATASTROPHIC_ERROR');
537 hr_utility.set_message_token('ROUTINE', l_proc);
538 hr_utility.set_message_token('REASON', SQLERRM);
539 hr_utility.raise_error;
540 END relieve_budget_commitments;
541 --
542 --
543 END PQH_PSP_INTEGRATION;
[Home] [Help]