1 package body PA_FUNDS_CONTROL_UTILS as
2 -- $Header: PAFCUTLB.pls 120.20.12000000.2 2007/04/20 08:51:05 rshaik ship $
3
4 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5
6 -- For R12
7 g_input_bvid pa_budget_versions.budget_version_id%type;
8 g_current_baseline_bvid pa_budget_versions.budget_version_id%type;
9 g_api_project_id pa_budget_versions.project_id%type;
10 g_api_task_id pa_tasks.task_id%type;
11 g_api_top_task_id pa_tasks.top_task_id%type;
12 g_api_rlmi pa_resource_list_members.resource_list_member_id%type;
13 g_api_parent_rlmi pa_resource_list_members.parent_member_id%type;
14 g_txn_exists_for_bvid Varchar2(1);
15 -- R12 complete
16
17
18 --This Api Initialize the pa_funds_control_utils package
19 -- global variables ( this is used as a one level cache)
20 PROCEDURE init_util_variables IS
21
22 BEGIN
23 g_project_id := null;
24 g_bdgt_version_id := null;
25 g_calling_mode := null;
26 g_calling_mode1 := null;
27 g_calling_mode2 := null;
28 g_calling_mode3 := null;
29 g_task_id := null;
30 g_exp_type := null;
31 g_exp_item_date := null;
32 g_exp_org_id := null;
33 g_budget_ccid := null;
34 g_start_date := null;
35 g_end_date := null;
36 g_rlmi := null;
37 g_period_name := null;
38 g_compiled_set_id := null;
39 g_multiplier := null;
40 g_fnd_reqd_flag := null;
41 g_encum_type_id := null;
42 g_ext_bdgt_link := null;
43 g_sch_rev_id := null;
44
45 END init_util_variables;
46
47 PROCEDURE print_message(p_msg in varchar2) IS
48 BEGIN
49 --dbms_output.put_line('Log: ' || p_msg);
50 --r_debug.r_msg('Log: ' || p_msg);
51 null;
52 END print_message;
53
54 -- -----------------------------------------------------------------------------+
55 -- This api provides the compiled set id for the given task, exp item date
56 -- and for the given organiation this is like a wraper api for the
57 -- pa_cost_plus.get_compiled_set_id
58 -- -----------------------------------------------------------------------------+
59
60 FUNCTION get_fc_compiled_set_id
61 ( p_task_id IN NUMBER
62 ,p_ei_date IN DATE
63 ,p_org_id IN NUMBER
64 ,p_sch_type IN VARCHAR2 DEFAULT 'C'
65 ,p_calling_mode IN VARCHAR2 DEFAULT 'COMPILE_SET_ID'
66 ,p_exp_type IN VARCHAR2 /** added for Burdening changes PAL */
67 ) return NUMBER is
68
69 l_compiled_set_id NUMBER ;
70 l_sch_id NUMBER ;
71 l_sch_date DATE;
72 l_stage NUMBER;
73 l_status NUMBER;
74 l_sch_fixed_date DATE;
75 l_rate_sch_rev_id NUMBER;
76 l_base VARCHAR2(100);
77 l_cp_structure VARCHAR2(100);
78
79 BEGIN
80
81 If (g_task_id is NULL or g_task_id <> p_task_id ) OR
82 (g_exp_item_date is NULL or p_ei_date <> g_exp_item_date ) OR
83 (g_exp_org_id is NULL or p_org_id <> g_exp_org_id )OR
84 (g_exp_type is NULL or g_exp_type <> p_exp_type ) THEN
85
86 print_message('sch type = '||p_sch_type);
87 If p_sch_type = 'C' then
88 BEGIN
89 /* Bug fix: The schedule override at the project or task is not taking
90 this issue is noticed during the DISTVIADJ process at PAL stage
91 this is fixed without logging any bugs.
92 SELECT t.cost_ind_rate_sch_id,
93 t.cost_ind_sch_fixed_date
94 INTO l_sch_id ,l_sch_date
95 FROM pa_tasks t,
96 pa_ind_rate_schedules irs
97 WHERE t.task_id = p_task_id
98 AND t.cost_ind_rate_sch_id = irs.ind_rate_sch_id
99 AND irs.cost_ovr_sch_flag = 'Y';
100 */
101 -- Select the Task level schedule override if not found
102 -- then select the Project level override
103 SELECT irs.ind_rate_sch_id,
104 t.cost_ind_sch_fixed_date
105 INTO l_sch_id,l_sch_date
106 FROM pa_tasks t,
107 pa_ind_rate_schedules irs
108 WHERE t.task_id = p_task_id
109 AND t.task_id = irs.task_id
110 AND irs.cost_ovr_sch_flag = 'Y';
111
112 EXCEPTION
113
114 WHEN NO_DATA_FOUND then
115 -- Select the project level sch override
116 BEGIN
117 SELECT irs.ind_rate_sch_id,
118 p.cost_ind_sch_fixed_date
119 INTO l_sch_id,l_sch_date
120 FROM pa_tasks t,
121 pa_projects_all p,
122 pa_ind_rate_schedules irs
123 WHERE t.task_id = p_task_id
124 AND t.project_id = p.project_id
125 AND t.project_id = irs.project_id
126 AND irs.cost_ovr_sch_flag = 'Y'
127 AND irs.task_id is null;
128 EXCEPTION
129
130 WHEN NO_DATA_FOUND THEN
131 -- select the schedule at the task
132 BEGIN
133 SELECT t.cost_ind_rate_sch_id,
134 t.cost_ind_sch_fixed_date
135 INTO l_sch_id ,l_sch_date
136 FROM pa_tasks t,
137 pa_ind_rate_schedules irs
138 WHERE t.task_id = p_task_id
139 AND t.cost_ind_rate_sch_id = irs.ind_rate_sch_id;
140 EXCEPTION
141
142 WHEN OTHERS THEN
143 raise;
144
145 END;
146 END;
147
148 END;
149 print_message('Schid['||l_sch_id||']date['||l_sch_date||']');
150
151 If l_sch_id is NOT NULL then
152 print_message('calling pa_cost_plus.get_revision_by_date');
153
154 pa_cost_plus.get_revision_by_date
155 (l_sch_id
156 ,l_sch_fixed_date
157 ,p_ei_date
158 ,l_rate_sch_rev_id
159 ,l_status
160 ,l_stage);
161 print_message('sch rev id = '||l_rate_sch_rev_id||' status ='||l_status);
162
163
164
165 If l_rate_sch_rev_id is NOT NULL then
166
167 /* Added these for PAL changes : Burdening enhancements */
168 pa_cost_plus.get_cost_plus_structure
169 (rate_sch_rev_id =>l_rate_sch_rev_id
170 ,cp_structure =>l_cp_structure
171 ,status =>l_status
172 ,stage =>l_stage);
173
174 pa_cost_plus.get_cost_base
175 (exp_type => p_exp_type
176 ,cp_structure => l_cp_structure
177 ,c_base => l_base
178 ,status => l_status
179 ,stage => l_stage);
180
181 pa_cost_plus.get_compiled_set_id
182 (l_rate_sch_rev_id,
183 p_org_id,
184 l_base,
185 l_compiled_set_id,
186 l_status,
187 l_stage);
188 print_message('compiled set id = '||l_compiled_set_id||' status ='||l_status);
189 Else
190 print_message('sch rev id is null so burden cost is zero'||' status ='||l_status);
191 null;
192
193 End if;
194
195 End if;
196
197 -- assign the values to global variables
198 g_task_id := p_task_id;
199 g_exp_item_date := p_ei_date;
200 g_exp_org_id := p_org_id;
201 g_exp_type := p_exp_type ;
202 g_compiled_set_id := l_compiled_set_id;
203 g_sch_rev_id := l_rate_sch_rev_id;
204
205 End if; -- sch type
206
207 Else
208 print_message('inside compiled set api same condition');
209 l_compiled_set_id := g_compiled_set_id;
210 l_rate_sch_rev_id := g_sch_rev_id;
211
212
213 End if;
214
215 If l_compiled_set_id is NULL then
216 l_compiled_set_id := 0;
217 End if;
218
219 /** Added this to make use of same api to return schedule revision id */
220 If p_calling_mode = 'SCH_REV_ID' then
221 return l_rate_sch_rev_id;
222 Else
223 return l_compiled_set_id;
224 End if;
225
226 END get_fc_compiled_set_id;
227
228 -- -----------------------------------------------------------------------------------------+
229 -- This api provides the compiled multipliers for the given task, exp item date,exp type
230 -- and for the given organiation this is like a wraper api for the
231 -- pa_cost_plus.get_compiled_multipliers
232 -- -----------------------------------------------------------------------------------------+
233 FUNCTION get_fc_compiled_multiplier
234 ( p_exp_org_id IN NUMBER,
235 p_task_id IN VARCHAR2,
236 P_exp_item_date IN date,
237 p_sch_type IN varchar2 default 'C',
238 p_exp_type IN varchar2
239 ) return NUMBER is
240
241 l_multiplier Number := 0;
242 l_sch_rev_id Number := 0;
243 l_compile_set_id Number := 0;
244 BEGIN
245
246
247 If (g_task_id is NULL or g_task_id <> p_task_id ) OR
248 (g_exp_item_date is NULL or P_exp_item_date <> g_exp_item_date ) OR
249 (g_exp_org_id is NULL or p_exp_org_id <> g_exp_org_id) OR
250 (g_exp_type is NULL or g_exp_type <> p_exp_type ) THEN
251
252 /* bug fix: 2795051 Performance Issues driving the table from
253 pa_compiled_multipliers to pa_ind_rate_sch_revisions is causing FTS
254 on pa_ind_rate_sch_revisions. Refer to the above bug for Explain plan and other details
255 */
256
257 l_sch_rev_id := get_fc_compiled_set_id
258 ( p_task_id => p_task_id
259 , p_ei_date => p_exp_item_date
260 , p_org_id => p_exp_org_id
261 , p_sch_type =>p_sch_type
262 , p_calling_mode => 'SCH_REV_ID'
263 , p_exp_type => p_exp_type );
264
265 l_compile_set_id := get_fc_compiled_set_id
266 ( p_task_id => p_task_id
267 , p_ei_date => p_exp_item_date
268 , p_org_id => p_exp_org_id
269 , p_sch_type =>p_sch_type
270 , p_calling_mode => 'COMPILE_SET_ID'
271 , p_exp_type => p_exp_type );
272
273 -- Bug 3687283 : Modified select statement in get_fc_compiled_multiplier procedure to
274 -- remove join with pa_expensiture_types table as the cost code may not be always mapped
275 -- to a expenditure type.Also removed unnecessary joins as the compiled_set_id and
276 -- ind_sch_rev_id are already derived.
277
278 If l_sch_rev_id is NOT NULL and l_compile_set_id is NOT NULL Then
279
280 SELECT SUM(NVL(cm.compiled_multiplier,0))
281 INTO l_multiplier
282 FROM pa_ind_rate_sch_revisions irsr,
283 pa_cost_base_exp_types cbet,
284 pa_compiled_multipliers cm
285 WHERE irsr.ind_rate_sch_revision_id = l_sch_rev_id
286 AND cbet.cost_plus_structure = irsr.cost_plus_structure
287 AND cbet.cost_base_type = 'INDIRECT COST'
288 AND cbet.expenditure_type = p_exp_type
289 AND cm.cost_base = cbet.cost_base
290 AND cm.ind_compiled_set_id = l_compile_set_id
291 AND cm.compiled_multiplier <> 0 ;
292
293 END IF;
294
295 -- SELECT SUM(cm.compiled_multiplier)
296 -- INTO l_multiplier
297 -- FROM
298 -- pa_ind_rate_sch_revisions irsr,
299 -- pa_cost_bases cb,
300 -- pa_expenditure_types et,
301 -- pa_ind_cost_codes icc,
302 -- pa_cost_base_exp_types cbet,
303 -- pa_ind_rate_schedules_all_bg irs,
304 -- pa_ind_compiled_sets ics,
305 -- pa_compiled_multipliers cm
306 -- WHERE irsr.cost_plus_structure = cbet.cost_plus_structure
307 -- AND cb.cost_base = cbet.cost_base
308 -- AND cb.cost_base_type = 'INDIRECT COST' /*cbet.cost_base_type changed the order */
309 -- AND et.expenditure_type = icc.expenditure_type
310 -- AND icc.ind_cost_code = cm.ind_cost_code
311 -- AND cbet.cost_base = cm.cost_base
312 -- AND cbet.cost_base_type = cb.cost_base_type /* 'INDIRECT COST' changed the order */
313 -- AND cbet.expenditure_type = p_exp_type
314 -- AND irs.ind_rate_sch_id = irsr.ind_rate_sch_id
315 -- AND ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id
316 -- AND irsr.ind_rate_sch_revision_id = l_sch_rev_id /* Bug fix :2795051 to make use of index */
317 -- AND ics.organization_id = p_exp_org_id
318 -- AND cm.ind_compiled_set_id = ics.ind_compiled_set_id
319 -- AND cm.compiled_multiplier <> 0
320 -- AND ics.ind_compiled_set_id = l_compile_set_id
321 -- AND ics.cost_base = cb.cost_base; -- added for burdening enhancements
322 /* Bug fix 2795051 commented out. assigned to variable
323 get_fc_compiled_set_id
324 ( p_task_id
325 , p_exp_item_date
326 , p_exp_org_id
327 , p_sch_type) ; */
328
329
330 g_task_id := p_task_id ;
331 g_exp_item_date := P_exp_item_date ;
332 g_exp_org_id := p_exp_org_id ;
333 g_exp_type := g_exp_type ;
334 g_multiplier := l_multiplier;
335
336 Else
337 --r_msg('inside compiled multiplier api same condition');
338 l_multiplier := g_multiplier;
339
340 End if;
341
342
343 If NVl(l_multiplier,0) = 0 then
344 l_multiplier := 0;
345 End if;
346
347 --r_msg('compiled multiplier is = '||l_multiplier);
348
349 Return l_multiplier;
350
351 EXCEPTION
352
353 when others then
354 --r_msg('error in getting compiled multiplier');
355 Raise;
356
357 END get_fc_compiled_multiplier;
358
359 -- -------------------------------------------------------------------------------+
360 -- This Api provides the Burdened cost for the given Expenditure item id and
361 -- and cdl line number
362 -- ------------------------------------------------------------------------------+
363 FUNCTION get_fc_proj_burdn_cost
364 (p_exp_item_id IN NUMBER
365 ,p_line_num IN NUMBER
366 )return NUMBER is
367
368 l_burdened_cost NUMBER := 0;
369 BEGIN
370
371 SELECT NVL(cdl.burdened_cost,cdl.amount)
372 + NVL(PROJFUNC_BURDENED_CHANGE,0) /* added for Burdening Enhanceents */
373 INTO l_burdened_cost
374 FROM pa_cost_distribution_lines_all cdl,
375 pa_expenditure_items_all ei,
376 pa_transaction_sources pts
377 WHERE
378 cdl.expenditure_item_id = p_exp_item_id
379 AND cdl.line_num = p_line_num
380 AND ei.expenditure_item_id = cdl.expenditure_item_id
381 AND ei.cost_dist_rejection_code is NULL
382 AND cdl.line_type ='R'
383 AND ei.system_linkage_function <> 'BTC'
384 AND NVL(ei.cost_distributed_flag,'N') = 'Y'
385 AND (ei.transaction_source = pts.transaction_source (+)
386 AND nvl(pts.cost_burdened_flag,'N') <> 'Y');
387
388 return l_burdened_cost;
389
390
391 EXCEPTION
392
393 WHEN NO_DATA_FOUND THEN
394 RETURN l_burdened_cost;
395
396 WHEN OTHERS THEN
397 Raise;
398 END get_fc_proj_burdn_cost;
399
400 -- --------------------------------------------------------------------------------------------+
401 -- This Api gets the open and closed periods start date, end date, period name and status
402 -- for the given start date ( Amount type) and end date ( boundary code) and sob
403 -- the out parameter will be in form of PLSQL table and also it returns the no of rows in
404 -- plsql table
405 -- ---------------------------------------------------------------------------------------------+
406 PROCEDURE get_gl_periods
407 (p_start_date IN date
408 ,p_end_date IN date
409 ,p_set_of_books_id IN gl_period_statuses.set_of_books_id%type
410 ,x_tab_count IN OUT NOCOPY Number
411 ,x_tab_pds IN OUT NOCOPY pa_funds_control_utils.tab_closed_period
412 ,x_return_status IN OUT NOCOPY varchar2
413 ) is
414
415
416 CURSOR cls_periods is
417 SELECT period_name
418 ,start_date
419 ,end_date
420 ,closing_status
421 FROM gl_period_statuses
422 WHERE application_id = 101
423 AND adjustment_period_flag = 'N'
424 AND set_of_books_id = p_set_of_books_id
425 AND ( (start_date between trunc(p_start_date) and trunc(p_end_date)
426 AND end_date between trunc(p_start_date) and trunc(p_end_date)
427 )
428 OR (
429 trunc(p_start_date) between start_date and end_date
430 or trunc(p_end_date) between start_date and end_date
431 )
432 )
433 AND closing_status in ('C','O','P');
434
435 l_count_rows NUMBER := 0;
436 l_tab_count NUMBER:=0;
437 l_tab_rec pa_funds_control_utils.tab_closed_period;
438
439
440 BEGIN
441 -- Initialize the error statck
442 PA_DEBUG.init_err_stack('PA_FUNDS_CHECK_UTILS.get_closed_periods');
443
444 -- set the return status to success
445 x_return_status := FND_API.G_RET_STS_SUCCESS;
446
447 --Initialize the plsql table to the null values
448 x_tab_pds.delete;
449
450 for i in cls_periods LOOP
451
452 l_tab_count := l_tab_count + 1;
453 x_tab_pds(l_tab_count).period_name := i.period_name;
454 x_tab_pds(l_tab_count).start_date := i.start_date;
455 x_tab_pds(l_tab_count).end_date := i.end_date;
456 x_tab_pds(l_tab_count).closing_status := i.closing_status;
457
458 End loop;
459
460
461 l_count_rows := x_tab_pds.count;
462 x_tab_count := l_count_rows;
463
464 -- the below condition is commented as it is not required
465 --If l_count_rows <= 0 then
466 --x_return_status := FND_API.G_RET_STS_ERROR;
467 --x_tab_count := 0;
468 --Else
469 --x_tab_count := l_count_rows;
470 --End if;
471 -- reset the error stack
472 PA_DEBUG.reset_err_stack;
473
474
475 EXCEPTION
476 when others then
477 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
478 x_tab_count := 0;
479 RAISE;
480 END get_gl_periods;
481
482 -- -----------------------------------------------------------------------------------+
483 --This API is a wrapper for the get_budgt_ctrl_options This api provides differenct
484 --options for the given project_id and calling mode
485 -- -----------------------------------------------------------------------------------+
486 FUNCTION get_fnd_reqd_flag
487 (p_project_id IN NUMBER
488 ,p_calling_mode IN VARCHAR2 -- STD / CBC
489 ) return varchar2 IS
490
491 l_fnd_chk_req_flag varchar2(20) := 'N';
492 l_bdgt_version_id PA_BUDGET_VERSIONS.budget_version_id%TYPE;
493 l_encum_type_id number(15) := null;
494 l_bdgt_link VARCHAR2(20) := 'N';
495 l_calling_mode VARCHAR2(20);
496 l_balance_type VARCHAR2(20);
497 l_return_status VARCHAR2(20);
498 l_msg_data VARCHAR2(2000);
499 l_msg_count number(15);
500 BEGIN
501
502 If (g_project_id is NULL or p_project_id <> g_project_id ) OR
503 (g_calling_mode is NULL or p_calling_mode <> g_calling_mode ) THEN
504
505 IF p_calling_mode = 'STD' THEN
506 l_calling_mode := 'STANDARD';
507 Elsif p_calling_mode = 'CBC' then
508 l_calling_mode := 'COMMITMENT';
509 End if;
510
511 PA_BUDGET_FUND_PKG.get_budget_ctrl_options
512 ( p_project_id =>p_project_id
513 ,p_budget_type_code => null
514 ,p_calling_mode =>l_calling_mode
515 ,X_BALANCE_TYPE =>l_balance_type
516 ,x_fck_req_flag =>l_fnd_chk_req_flag
517 ,x_bdgt_intg_flag =>l_bdgt_link
518 ,x_bdgt_ver_id =>l_bdgt_version_id
519 ,x_encum_type_id =>l_encum_type_id
520 ,X_Return_Status =>l_return_status
521 ,X_Msg_Data =>l_msg_data
522 ,X_Msg_Count =>l_msg_count
523 );
524
525 If l_bdgt_link in ('G','C') then
526 l_bdgt_link := 'Y';
527 Else
528 l_bdgt_link := 'N';
529 End if;
530
531 If l_encum_type_id is null then
532 l_encum_type_id := 0;
533 End if;
534
535 g_project_id := p_project_id;
536 g_calling_mode := p_calling_mode;
537 g_fnd_reqd_flag := l_fnd_chk_req_flag;
538 Else
539 --r_msg('inside fnd reqd flag api same condition');
540 l_fnd_chk_req_flag := g_fnd_reqd_flag;
541
542 End if;
543 return Nvl(l_fnd_chk_req_flag,'N');
544
545 EXCEPTION
546 WHEN OTHERS THEN
547 raise;
548 END get_fnd_reqd_flag;
549
550 -- -----------------------------------------------------------------------------------+
551 --This API is a wrapper for the get_budgt_ctrl_options This api provides differenct
552 --options for the given project_id and calling mode
553 -- -----------------------------------------------------------------------------------+
554 FUNCTION get_bdgt_version_id
555 (p_project_id IN NUMBER
556 ,p_calling_mode IN VARCHAR2 -- STD / CBC
557 ) return PA_BUDGET_VERSIONS.budget_version_id%TYPE IS
558
559 l_fnd_chk_req_flag varchar2(20) := 'N';
560 l_bdgt_version_id PA_BUDGET_VERSIONS.budget_version_id%TYPE;
561 l_encum_type_id number(15) := null;
562 l_bdgt_link VARCHAR2(20) := 'N';
563 l_calling_mode VARCHAR2(20);
564 l_balance_type VARCHAR2(20);
565 l_return_status VARCHAR2(20);
566 l_msg_data VARCHAR2(2000);
567 l_msg_count number(15);
568 BEGIN
569
570 IF (g_project_id is NULL or p_project_id <> g_project_id ) OR
571 (g_calling_mode1 is NULL or p_calling_mode <> g_calling_mode1) THEN
572 IF p_calling_mode = 'STD' THEN
573 l_calling_mode := 'STANDARD';
574 Elsif p_calling_mode = 'CBC' then
575 l_calling_mode := 'COMMITMENT';
576 End if;
577
578 PA_BUDGET_FUND_PKG.get_budget_ctrl_options
579 ( p_project_id =>p_project_id
580 ,p_budget_type_code => null
581 ,p_calling_mode =>l_calling_mode
582 ,X_BALANCE_TYPE =>l_balance_type
583 ,x_fck_req_flag =>l_fnd_chk_req_flag
584 ,x_bdgt_intg_flag =>l_bdgt_link
585 ,x_bdgt_ver_id =>l_bdgt_version_id
586 ,x_encum_type_id =>l_encum_type_id
587 ,X_Return_Status =>l_return_status
588 ,X_Msg_Data =>l_msg_data
589 ,X_Msg_Count =>l_msg_count
590 );
591 If l_bdgt_link in ('G','C') then
592 l_bdgt_link := 'Y';
593 Else
594 l_bdgt_link := 'N';
595 End if;
596
597 If l_encum_type_id is null then
598 l_encum_type_id := 0;
599 End if;
600
601 g_project_id := p_project_id;
602 g_calling_mode1 := p_calling_mode;
603 g_bdgt_version_id := l_bdgt_version_id;
604
605 Else
606 --r_msg('inside get bdgt version api same condition');
607 l_bdgt_version_id := g_bdgt_version_id;
608 End if;
609
610 return l_bdgt_version_id;
611
612 EXCEPTION
613 WHEN OTHERS THEN
614 raise;
615 END get_bdgt_version_id;
616
617 -- -------------------------------------------------------------------------------------------+
618 -- R12 Funds management changes --Rshaik
619 -- IN R12, project encumbrance type is no more user enterable in Project
620 -- budgetary control window .This is seeded into gl_encumbrance types
621 -- Hence existing API has been modified to return seeded encumbrance type id
622 -- for BC enabled projects if encumbrance type in pa_budgetary_control_options is NULL
623 -- Also for performance reasons instead of calling PA_BUDGET_FUND_PKG.get_budget_ctrl_options
624 -- have cursor C_BUDGET_CONTROL to fetch required details.
625 -- -------------------------------------------------------------------------------------------+
626
627 FUNCTION get_encum_type_id
628 (p_project_id IN NUMBER
629 ,p_calling_mode IN VARCHAR2 -- STD / CBC
630 ) return number IS
631
632 CURSOR get_seeded_enc_type_id IS
633 SELECT encumbrance_type_id
634 FROM gl_encumbrance_types
635 WHERE encumbrance_type_key = 'Projects';
636
637 l_encumbrance_type_id PA_BUDGETARY_CONTROL_OPTIONS.encumbrance_type_id%TYPE;
638
639 BEGIN
640
641 -- Note:
642 -- Bug 5618583 : With new upgrade strategy ,all transactions upgraded/unupgraded will use
643 -- only R12 seeded encumbrance types.Hence obsoleted logic associated with p_txn_sla_notupgraded_flag.
644
645 IF (g_project_id is NULL or p_project_id <> g_project_id ) OR
646 (g_calling_mode2 is NULL or p_calling_mode <> g_calling_mode2) THEN
647
648 OPEN get_seeded_enc_type_id;
649 FETCH get_seeded_enc_type_id INTO l_encumbrance_type_id;
650 CLOSE get_seeded_enc_type_id;
651
652 g_project_id := p_project_id;
653 g_calling_mode2 := p_calling_mode;
654 g_encum_type_id := l_encumbrance_type_id;
655
656 ELSE
657 l_encumbrance_type_id := g_encum_type_id;
658 END IF;
659 RETURN l_encumbrance_type_id;
660
661 EXCEPTION
662 WHEN OTHERS THEN
663 raise;
664 END get_encum_type_id;
665
666 -- -----------------------------------------------------------------------------------+
667 --This API is a wrapper for the get_budgt_ctrl_options This api provides differenct
668 --options for the given project_id and calling mode
669 -- -----------------------------------------------------------------------------------+
670 FUNCTION get_bdgt_link
671 (p_project_id IN NUMBER
672 ,p_calling_mode IN VARCHAR2 -- STD / CBC
673 ) return varchar2 IS
674
675 l_fnd_chk_req_flag varchar2(20) := 'N';
676 l_bdgt_version_id PA_BUDGET_VERSIONS.budget_version_id%TYPE;
677 l_encum_type_id number(15) := null;
678 l_bdgt_link VARCHAR2(20) := 'N';
679 l_calling_mode VARCHAR2(20);
680 l_balance_type VARCHAR2(20);
681 l_return_status VARCHAR2(20);
682 l_msg_data VARCHAR2(2000);
683 l_msg_count number(15);
684 BEGIN
685
686 If (g_calling_mode3 is NULL or p_calling_mode <> g_calling_mode3 ) OR
687 (g_project_id is NULL or g_project_id <> p_project_id ) THEN
688 IF p_calling_mode = 'STD' THEN
689 l_calling_mode := 'STANDARD';
690 Elsif p_calling_mode = 'CBC' then
691 l_calling_mode := 'COMMITMENT';
692 End if;
693 PA_BUDGET_FUND_PKG.get_budget_ctrl_options
694 ( p_project_id =>p_project_id
695 ,p_budget_type_code => null
696 ,p_calling_mode =>l_calling_mode
697 ,X_BALANCE_TYPE =>l_balance_type
698 ,x_fck_req_flag =>l_fnd_chk_req_flag
699 ,x_bdgt_intg_flag =>l_bdgt_link
700 ,x_bdgt_ver_id =>l_bdgt_version_id
701 ,x_encum_type_id =>l_encum_type_id
702 ,X_Return_Status =>l_return_status
703 ,X_Msg_Data =>l_msg_data
704 ,X_Msg_Count =>l_msg_count
705 );
706
707 If l_bdgt_link in ('G','C') then
708 l_bdgt_link := 'Y';
709 Else
710 l_bdgt_link := 'N';
711 End if;
712
713 If l_encum_type_id is null then
714 l_encum_type_id := 0;
715 End if;
716 g_project_id := p_project_id;
717 g_calling_mode3 := p_calling_mode;
718 g_ext_bdgt_link := l_bdgt_link;
719
720 Else
721
722 --r_msg('inside get bdgt link api same condition');
723 l_bdgt_link := g_ext_bdgt_link;
724
725 End if;
726
727
728 return Nvl(l_bdgt_link,'N');
729
730 EXCEPTION
731 WHEN OTHERS THEN
732 raise;
733 END get_bdgt_link;
734
735 -- ---------------------------------------------------------------------+
736 --The following API returns the Budget CCID for a given project, task,
737 --resource list member id, budget version id and start date.
738 -- ---------------------------------------------------------------------+
739 PROCEDURE Get_Budget_CCID (
740 p_project_id in number,
741 p_task_id in number,
742 p_top_task_id in number,
743 p_res_list_mem_id in number,
744 p_start_date in date,
745 p_budget_version_id in number,
746 p_entry_level_code in varchar2,
747 x_budget_ccid out NOCOPY number,
748 x_budget_line_id out NOCOPY number,
749 x_return_status out NOCOPY varchar2,
750 x_error_message_code out NOCOPY varchar2) is
751 BEGIN
752 x_return_status := fnd_api.g_ret_sts_success;
753
754 IF P_DEBUG_MODE = 'Y' THEN
755 pa_funds_control_utils.print_message('Get_Budget_CCID: ' || 'Inside Get Budget CCID');
756 pa_fck_util.debug_msg('Get_Budget_CCID: ' || 'PB:Inside Get Budget CCID');
757 pa_fck_util.debug_msg('Get_Budget_CCID: ' || 'PB:P:T:TT:R:S:B:E = ' || p_project_id || ':' || p_task_id || ':' || p_top_task_id|| ':' || p_res_list_mem_id ||':'|| p_start_date || ':' || p_budget_version_id ||':'|| p_entry_level_code);
758 END IF;
759
760 select pbl.code_combination_id, pbl.budget_line_id
761 into x_budget_ccid, x_budget_line_id
762 from pa_resource_assignments pra,
763 pa_budget_lines pbl
764 where ((p_entry_level_code = 'P' and
765 pra.task_id = 0)
766 or
767 (p_entry_level_code in ('L','M','T') and
768 pra.task_id in (p_task_id,p_top_task_id)))
769 and pra.budget_version_id = p_budget_version_id
770 and pra.project_id = p_project_id
771 and pra.resource_list_member_id = p_res_list_mem_id
772 and pra.resource_assignment_id = pbl.resource_assignment_id
773 and trunc(pbl.start_date) = trunc(p_start_date);
774
775 IF P_DEBUG_MODE = 'Y' THEN
776 pa_funds_control_utils.print_message('Get_Budget_CCID: ' || 'End of Get Budget CCID');
777 pa_fck_util.debug_msg('Get_Budget_CCID: ' || 'PB:End of Get Budget CCID = ' || x_budget_ccid||' Line id:'||x_budget_line_id);
778 END IF;
779
780 EXCEPTION
781 when no_data_found then
782 x_return_status := fnd_api.g_ret_sts_error;
783 x_error_message_code := 'PA_BC_BUDGET_CCID_NULL';
784 when others then
785 x_return_status := fnd_api.g_ret_sts_unexp_error;
786 raise;
787 END Get_Budget_CCID;
788
789 --The following API returns the Time Phased Type Code for a budget_version_id.
790 PROCEDURE Get_Time_Phased_Type_Code(
791 p_budget_version_id in number,
792 x_time_phased_type_code out NOCOPY varchar2,
793 x_return_status out NOCOPY varchar2,
794 x_error_message_code out NOCOPY varchar2) is
795 BEGIN
796 x_return_status := fnd_api.g_ret_sts_success;
797
798 IF P_DEBUG_MODE = 'Y' THEN
799 pa_funds_control_utils.print_message('Get_Time_Phased_Type_Code: ' || 'Inside Get Time Phased Type Code');
800 pa_fck_util.debug_msg('Get_Time_Phased_Type_Code: ' || 'PB:Inside Get Time Phased Type Code');
801 END IF;
802
803 select time_phased_type_code
804 into x_time_phased_type_code
805 from pa_budget_entry_methods a,
806 pa_budget_versions b
807 where a.budget_entry_method_code = b.budget_entry_method_code
808 and b.budget_version_id = p_budget_version_id;
809
810 IF P_DEBUG_MODE = 'Y' THEN
811 pa_funds_control_utils.print_message('Get_Time_Phased_Type_Code: ' || 'End of Get Time Phased Type Code');
812 pa_fck_util.debug_msg('Get_Time_Phased_Type_Code: ' || 'PB:End of Get Time Phased Type Code');
813 END IF;
814
815 EXCEPTION
816 when no_data_found then
817 x_return_status := fnd_api.g_ret_sts_error;
818 x_error_message_code := 'PA_BC_TIME_PHASE_CODE_NULL';
819 when others then
820 x_return_status := fnd_api.g_ret_sts_unexp_error;
821 raise;
822 END Get_Time_Phased_Type_Code;
823
824 -- -----------------------------------------------------------------------------------+
825 --The following API gets the current baselined budget version id for the project id.
826 -- -----------------------------------------------------------------------------------+
827 PROCEDURE Get_Baselined_Budget_Version(
828 p_calling_mode in varchar2, -- GL,CC
829 p_project_id in number,
830 x_base_version_id out NOCOPY number,
831 x_res_list_id out NOCOPY number,
832 x_entry_level_code out NOCOPY varchar2,
833 x_return_status out NOCOPY varchar2,
834 x_error_message_code out NOCOPY varchar2) is
835 BEGIN
836 x_return_status := fnd_api.g_ret_sts_success;
837
838 IF P_DEBUG_MODE = 'Y' THEN
839 pa_funds_control_utils.print_message('Inside Get_Baselined_Budget_Version');
840 pa_fck_util.debug_msg('PB:Inside Get_Baselined_Budget_Version');
841 END IF;
842
843 select pbv.budget_version_id, pbv.resource_list_id, pbm.entry_level_code
844 into x_base_version_id,x_res_list_id,x_entry_level_code
845 from pa_budget_versions pbv,
846 --pa_budget_types pbt,
847 pa_budget_entry_methods pbm,
848 pa_budgetary_control_options pbco
849 where pbv.project_id = p_project_id
850 and pbv.current_flag = 'Y'
851 and pbv.budget_status_code = 'B'
852 and pbv.budget_type_code = pbco.budget_type_code
853 and pbv.project_id = pbco.project_id
854 and pbco.bdgt_cntrl_flag = 'Y'
855 and ((p_calling_mode = 'GL' and pbco.external_budget_code = 'GL')
856 or
857 (p_calling_mode = 'CC' and pbco.external_budget_code = 'CC')
858 or
859 (p_calling_mode = 'GL' and pbco.external_budget_code is null))
860 --and pbv.budget_type_code = pbt.budget_type_code
861 --and pbt.budget_amount_code = 'C'
862 and pbv.budget_entry_method_code = pbm.budget_entry_method_code;
863
864 IF P_DEBUG_MODE = 'Y' THEN
865 pa_funds_control_utils.print_message('End of Get_Baselined_Budget_Version');
866 pa_fck_util.debug_msg('Get_Baselined_Budget_Version: ' || 'PB:Output = '|| x_base_version_id || ':' || x_res_list_id || ':'|| x_entry_level_code);
867 pa_fck_util.debug_msg('PB:End of Get_Baselined_Budget_Version');
868 END IF;
869
870 EXCEPTION
871 when no_data_found then
872 x_base_version_id := null;
873 x_res_list_id := null;
874 x_entry_level_code := null;
875 --x_return_status := fnd_api.g_ret_sts_error;
876 --x_error_message_code := 'PA_BC_BSLND_BDGT_VER_NULL';
877 when others then
878 x_return_status := fnd_api.g_ret_sts_unexp_error;
879 raise;
880 END Get_Baselined_Budget_Version;
881
882 -- --------------------------------------------------------------------------------------------------+
883 --The following API returns the available balance for the budget_version, budget_CCID and start_date
884 --from PA_BUDGET_ACCT_LINES
885 -- --------------------------------------------------------------------------------------------------+
886 FUNCTION Get_Acct_Line_Balance(
887 p_budget_version_id in number,
888 p_start_date in date,
889 p_end_date in date,
890 p_budget_ccid in number) RETURN NUMBER IS
891 x_avail_balance number;
892 BEGIN
893 IF P_DEBUG_MODE = 'Y' THEN
894 pa_funds_control_utils.print_message('Get_Acct_Line_Balance: ' || 'Inside Get Acct Line Balance');
895 pa_fck_util.debug_msg('Get_Acct_Line_Balance: ' || 'PB:Inside Get Acct Line Balance');
896 END IF;
897 /*
898 --select bal.Curr_Ver_Available_Amount
899 --into x_avail_balance
900 --from pa_budget_acct_lines bal,
901 -- gl_period_statuses gps
902 --where trunc(gps.start_date) = trunc(p_start_date)
903 --and trunc(gps.end_date) = trunc(p_end_date)
904 --and gps.period_name = bal.gl_period_name
905 --and gps.application_id = 101
906 --and bal.budget_version_id = p_budget_version_id
907 --and bal.code_combination_id = p_budget_ccid;
908 */
909
910 /**
911 * Bug fix : 1892535 nvl function is added in query
912 */
913
914 SELECT sum(nvl(bal.Curr_Ver_Available_Amount,0))
915 INTO x_avail_balance
916 FROM pa_budget_acct_lines bal
917 WHERE bal.budget_version_id = p_budget_version_id
918 AND bal.code_combination_id = p_budget_ccid
919 AND start_date between trunc(p_start_date) and trunc(p_end_date)
920 AND end_date between trunc(p_start_date) and trunc(p_end_date);
921
922 IF P_DEBUG_MODE = 'Y' THEN
923 pa_funds_control_utils.print_message('Get_Acct_Line_Balance: ' || 'End of Get Acct Line Balance');
924 pa_fck_util.debug_msg('Get_Acct_Line_Balance: ' || 'PB:End of Get Acct Line Balance');
925 END IF;
926 return nvl(x_avail_balance,0);
927 EXCEPTION
928 WHEN NO_DATA_FOUND THEN
929 return 0;
930 WHEN OTHERS THEN
931 return 0;
932 END Get_Acct_Line_Balance;
933
934 -- --------------------------------------------------------------------------------------------------+
935 FUNCTION Is_Budget_Baselined_Before(p_project_id in number) RETURN VARCHAR2 IS
936 x_baselined varchar2(1);
937 l_count NUMBER;
938 BEGIN
939
940 IF P_DEBUG_MODE = 'Y' THEN
941 pa_funds_control_utils.print_message('Is_Budget_Baselined_Before: ' || 'Inside Is budget baselined before');
942 pa_fck_util.debug_msg('Is_Budget_Baselined_Before: ' || 'PB:Inside Is budget baselined before');
943 END IF;
944
945 select 1 into l_count
946 from pa_budget_versions pbv,
947 pa_budget_types pbt
948 where pbv.project_id = p_project_id
949 and pbv.budget_status_code = 'B'
950 and pbv.budget_type_code = pbt.budget_type_code
951 and pbt.budget_amount_code = 'C'
952 and rownum = 1;
953
954 if l_count > 0 then
955 x_baselined := 'Y';
956 else
957 x_baselined := 'N';
958 end if;
959
960 IF P_DEBUG_MODE = 'Y' THEN
961 pa_funds_control_utils.print_message('Is_Budget_Baselined_Before: ' || 'After Is budget baselined before = ' || x_baselined);
962 pa_fck_util.debug_msg('Is_Budget_Baselined_Before: ' || 'PB:After Is budget baselined before = ' || x_baselined);
963 END IF;
964 return x_baselined;
965 EXCEPTION
966 WHEN NO_DATA_FOUND THEN
967 return 'N';
968 WHEN OTHERS THEN
969 return 'N';
970 END Is_Budget_Baselined_Before;
971
972 -- -----------------------------------------------+
973 -- Submit sweeper process as a concurrent request
974 -- -----------------------------------------------+
975 FUNCTION RunSweeper RETURN NUMBER is
976 l_reqid number;
977 BEGIN
978 l_reqid:= fnd_request.submit_request('PA','PAFCUPAE','','',FALSE);
979
980 return l_reqid;
981
982 END RunSweeper;
983
984 -- -----------------------------------------------------------------------------------------+
985 -- #This function has been created in base release 12 for SLA - FC integration project
986 -- #Function "Is_account_change_allowed" is the API that will be called from
987 -- #budgets form and from funds check tieback processing. This function will
988 -- #check if there exists any transaction, against any budget line, whose
989 -- #account has been modified. It will return 'N' if there exists transaction
990 -- #against a budget line, else it will return 'Y'.
991
992 -- # Code flow is as follows for each of the BEM: Lowest Task/Top Task/Project Level
993 -- # If API called through Check funds or form
994 -- Check if txn exists in pa_bc_packets or in pa_bc_commitments_all
995 -- Elsif API called during Baseline/YearEnd
996 -- Check if txn exists in pa_bc_balances or pa_bc_packets
997 -- # For baseline/yearend , we're using bc_balances to increase performance ..
998 -- -----------------------------------------------------------------------------------------+
999 FUNCTION Is_account_change_allowed (P_budget_version_id IN Number,
1000 P_resource_assignment_id IN Number,
1001 P_period_name IN Varchar2,
1002 P_budget_entry_level_code IN Varchar2 default null)
1003 return Varchar2
1004 IS
1005 l_return_status varchar2(1);
1006 l_budget_entry_level_code pa_budget_entry_methods.entry_level_code%type;
1007 l_period_name pa_budget_lines.period_name%type;
1008 l_start_date pa_budget_lines.start_date%type;
1009 l_rlmi pa_resource_assignments.resource_list_member_id%type;
1010 l_task_id pa_resource_assignments.task_id%type;
1011
1012 Begin
1013 l_return_status := 'N';
1014 l_budget_entry_level_code := P_budget_entry_level_code;
1015 l_period_name := p_period_name;
1016
1017 IF P_DEBUG_MODE = 'Y' THEN
1018 pa_fck_util.debug_msg('Is_account_change_allowed: l_budget_entry_level_code['||l_budget_entry_level_code||
1019 ']P_budget_version_id['||P_budget_version_id||']P_resource_assignment_id['||
1020 P_resource_assignment_id||']P_period_name['||l_period_name||
1021 ']pa_budget_fund_pkg.g_processing_mode['||pa_budget_fund_pkg.g_processing_mode||']'
1022 );
1023 END IF;
1024
1025 -- -----------------------------------------------------------+
1026 -- Derive the latest baseline version and also
1027 -- see to it that the query does not execute multiple times..
1028 -- If its the same, we dont even need to assign ..
1029 -- -----------------------------------------------------------+
1030 If nvl(g_input_bvid,-1) <> P_budget_version_id then -- I
1031
1032 g_txn_exists_for_bvid := 'N';
1033 g_current_baseline_bvid := null;
1034 g_api_project_id := null;
1035
1036 -- -------------------------------------+
1037 -- # get current baseline version ..
1038 -- -------------------------------------+
1039 Begin
1040 select pbv.budget_version_id,pbv.project_id
1041 into g_current_baseline_bvid,g_api_project_id
1042 from pa_budget_versions pbv
1043 where (pbv.project_id,pbv.budget_type_code) in
1044 (select project_id,budget_type_code
1045 from pa_budget_versions
1046 where budget_version_id = p_budget_version_id)
1047 and pbv.budget_status_code = 'B'
1048 and pbv.current_flag = 'Y';
1049 Exception
1050 When no_Data_found then
1051 IF P_DEBUG_MODE = 'Y' THEN
1052 pa_fck_util.debug_msg('Is_account_change_allowed: No baseline budget exists');
1053 END IF;
1054
1055 g_current_baseline_bvid := -2;
1056 g_api_project_id := -2;
1057
1058 End;
1059
1060 -- -----------------------------------------------------+
1061 -- # Check if any txn. exists for the baseline version ..
1062 -- -----------------------------------------------------+
1063 -- Following code can cause one issue .. If user is
1064 -- not closing the form and then creating a txn.. in this case
1065 -- following code will not fire and then user may be able to modify account
1066 -- If user tries the above workaround, baseline will anyways fail .. :)
1067
1068 If g_current_baseline_bvid <> -2 then
1069 Begin
1070 select 'Y'
1071 into g_txn_exists_for_bvid
1072 from dual
1073 where exists (select 1
1074 from pa_bc_balances pbb
1075 where pbb.budget_version_id = g_current_baseline_bvid
1076 and pbb.project_id = g_api_project_id
1077 and pbb.balance_type <> 'BGT');
1078 Exception
1079 When no_data_found then
1080
1081 Begin
1082 select 'Y'
1083 into g_txn_exists_for_bvid
1084 from dual
1085 where exists (select 1
1086 from pa_bc_packets pbb
1087 where pbb.project_id = g_api_project_id
1088 and pbb.status_code in ('A','P','I','Z') );
1089 Exception
1090 When no_data_found then
1091 null;
1092 End;
1093 End;
1094
1095 Else
1096 g_txn_exists_for_bvid := 'N';
1097 End If;
1098
1099 -- -------------------------------------+
1100 -- # Initialize global var. ...
1101 -- -------------------------------------+
1102 g_input_bvid := P_budget_version_id;
1103
1104 End If; -- I
1105
1106 IF P_DEBUG_MODE = 'Y' THEN
1107 pa_fck_util.debug_msg('Is_account_change_allowed: g_current_baseline_bvid['
1108 ||g_current_baseline_bvid||'] g_api_project_id ['
1109 ||g_api_project_id||'] g_txn_exists_for_bvid ['
1110 || g_txn_exists_for_bvid ||']');
1111 END IF;
1112
1113 -- ------------------------------------------------------------------------------------------+
1114 -- Return 'Y' if there exists no baseline version ..
1115 -- This can only happen if this API is called before first time baseline ..
1116 -- ------------------------------------------------------------------------------------------+
1117 If nvl(g_txn_exists_for_bvid,'N') = 'N' then
1118 IF P_DEBUG_MODE = 'Y' THEN
1119 pa_fck_util.debug_msg('Is_account_change_allowed: No txn. exists - return Y');
1120 END IF;
1121 RETURN 'Y';
1122 End If;
1123
1124 -- -------------------------------------+
1125 -- Derive BEM if top and lowest task ..
1126 -- -------------------------------------+
1127 If l_budget_entry_level_code is null then
1128
1129 select pbem.entry_level_code
1130 into l_budget_entry_level_code
1131 from pa_budget_entry_methods pbem,
1132 pa_budget_versions pbv
1133 where pbv.budget_version_id = P_budget_version_id
1134 and pbem.budget_entry_method_code = pbv.budget_entry_method_code;
1135
1136
1137 IF P_DEBUG_MODE = 'Y' THEN
1138 pa_fck_util.debug_msg('Is_account_change_allowed: l_budget_entry_level_code['||l_budget_entry_level_code);
1139 END IF;
1140
1141 End If;
1142
1143 If l_budget_entry_level_code = 'M' then
1144
1145 Begin
1146 Select 'T'
1147 into l_budget_entry_level_code
1148 from pa_resource_assignments pra,
1149 pa_tasks pt
1150 where pra.resource_assignment_id = P_resource_assignment_id
1151 and pra.budget_version_id = P_budget_version_id
1152 and pt.task_id = pra.task_id
1153 and pt.top_task_id = pra.task_id;
1154 Exception
1155 when no_data_found then
1156 l_budget_entry_level_code := 'L';
1157 End;
1158
1159 IF P_DEBUG_MODE = 'Y' THEN
1160 pa_fck_util.debug_msg('Is_account_change_allowed: l_budget_entry_level_code(L/T?)['||l_budget_entry_level_code);
1161 END IF;
1162
1163 End If; -- If l_budget_entry_level_code = 'M'
1164
1165 -- Derive values reqd. for "Is_Account_change_allowed2 API"..
1166 IF P_DEBUG_MODE = 'Y' THEN
1167 pa_fck_util.debug_msg('Is_account_change_allowed: get task/rlmi from resource assignments');
1168 END IF;
1169
1170 select task_id,resource_list_member_id
1171 into l_task_id,l_rlmi
1172 from pa_resource_assignments pra
1173 where pra.resource_assignment_id = P_resource_assignment_id;
1174
1175 IF P_DEBUG_MODE = 'Y' THEN
1176 pa_fck_util.debug_msg('Is_account_change_allowed: get parent rlmi');
1177 END IF;
1178
1179 If nvl(g_api_rlmi,-1) <> l_rlmi then
1180
1181 select nvl(parent_member_id,-99) into g_api_parent_rlmi
1182 from pa_resource_list_members prlm where resource_list_member_id = l_rlmi;
1183
1184 g_api_rlmi := l_rlmi;
1185 End If;
1186
1187 IF P_DEBUG_MODE = 'Y' THEN
1188 pa_fck_util.debug_msg('Is_account_change_allowed: get top task');
1189 END IF;
1190
1191 If nvl(g_api_task_id,-1) <> l_task_id then
1192 If l_budget_entry_level_code = 'P' then
1193 g_api_top_task_id := 0;
1194 Else
1195 select top_task_id into g_api_top_task_id
1196 from pa_tasks where task_id = l_task_id;
1197
1198 g_api_task_id := l_task_id;
1199 End if;
1200 End If;
1201
1202 IF P_DEBUG_MODE = 'Y' THEN
1203 pa_fck_util.debug_msg('Is_account_change_allowed: get start date');
1204 END IF;
1205
1206 select distinct start_date into l_start_date from pa_budget_lines
1207 where budget_version_id = p_budget_version_id
1208 and resource_assignment_id = P_resource_assignment_id
1209 and period_name = l_period_name;
1210
1211 IF P_DEBUG_MODE = 'Y' THEN
1212 pa_fck_util.debug_msg('Is_account_change_allowed: l_top_task_id['
1213 ||g_api_top_task_id||'] l_task_id ['
1214 ||l_task_id||'] l_rlmi ['||l_rlmi||']l_parent_rlmi['
1215 ||g_api_parent_rlmi||']l_start_date['||l_start_date
1216 ||'] l_period_name['||l_period_name ||']');
1217 pa_fck_util.debug_msg('Is_account_change_allowed: Calling Is_Account_change_allowed2');
1218 END IF;
1219
1220
1221 If Is_Account_change_allowed2
1222 (p_budget_version_id => g_current_baseline_bvid,
1223 p_project_id => g_api_project_id,
1224 p_top_task_id => g_api_top_task_id,
1225 p_task_id => l_task_id,
1226 p_parent_resource_id => g_api_parent_rlmi,
1227 p_resource_list_member_id => l_rlmi,
1228 p_start_date => l_start_date,
1229 p_period_name => l_period_name,
1230 p_entry_level_code => l_budget_entry_level_code,
1231 p_mode => 'FORM') = 'N' then
1232
1233 IF P_DEBUG_MODE = 'Y' THEN
1234 pa_fck_util.debug_msg('Is_account_change_allowed: Is_Account_change_allowed2 -> N');
1235 End If;
1236
1237 return 'N';
1238 Else
1239 IF P_DEBUG_MODE = 'Y' THEN
1240 pa_fck_util.debug_msg('Is_account_change_allowed: Is_Account_change_allowed2 -> Y');
1241 End If;
1242
1243 return'Y';
1244 End If;
1245
1246 /* =====================================================================================+
1247 -- ------------------------------- 'L' ---------------------------------------------------+
1248
1249 If l_budget_entry_level_code = 'L' then
1250
1251 If (nvl(pa_budget_fund_pkg.g_processing_mode,'CHECK_FUNDS') = 'CHECK_FUNDS') then
1252 -- CHECK_FUNDS/THROUGH FORM ..this if condition ..
1253 -- BASELINE/YEAR_END ..else part ..
1254
1255 -- If its Check funds, then we need to look at data from pa_bc_commitments_all
1256 Begin -- 'L' : pa_bc_packets
1257
1258 Select 'N'
1259 into l_return_status
1260 from pa_resource_assignments pra
1261 where pra.budget_version_id = p_budget_version_id
1262 and pra.resource_assignment_id = p_resource_assignment_id
1263 and exists(select 1
1264 from pa_bc_packets pbc
1265 where pbc.project_id = pra.project_id
1266 and pbc.resource_list_member_id = pra.resource_list_member_id
1267 and pbc.period_name = p_period_name
1268 and pbc.task_id = pra.task_id);
1269 Exception
1270 When no_data_found then
1271 Begin -- 'L' : pa_bc_commitments_all
1272 Select 'N'
1273 into l_return_status
1274 from pa_resource_assignments pra
1275 where pra.budget_version_id = p_budget_version_id
1276 and pra.resource_assignment_id = p_resource_assignment_id
1277 and exists(select 1
1278 from pa_bc_commitments_all pbc
1279 where pbc.project_id = pra.project_id
1280 and pbc.resource_list_member_id = pra.resource_list_member_id
1281 and pbc.period_name = p_period_name
1282 and pbc.task_id = pra.task_id);
1283 Exception
1284 When no_data_found then
1285 return 'Y';
1286 End; -- 'L' : pa_bc_commitments_all
1287 End; -- 'L' : pa_bc_packets
1288
1289 Else
1290
1291 Begin -- 'L' : pa_bc_balances
1292
1293 Select 'N'
1294 into l_return_status
1295 from pa_resource_assignments pra,
1296 pa_budget_lines pbl
1297 where pbl.budget_version_id = p_budget_version_id
1298 and pbl.resource_assignment_id = p_resource_assignment_id
1299 and pbl.period_name = P_period_name
1300 and pra.resource_assignment_id = pbl.resource_assignment_id
1301 and pra.budget_version_id = pbl.budget_version_id
1302 and exists(select 1
1303 from pa_bc_balances pbb
1304 where pbb.budget_version_id = pra.budget_version_id
1305 and pbb.project_id = pra.project_id
1306 and pbb.task_id = pra.task_id
1307 and pbb.resource_list_member_id = pra.resource_list_member_id
1308 and trunc(pbb.start_date) = trunc(pbl.start_date)
1309 and pbb.balance_type <> 'BGT');
1310
1311 Exception
1312 When no_data_found then
1313
1314 Begin -- 'L' : pa_bc_packets
1315
1316 Select 'N'
1317 into l_return_status
1318 from pa_resource_assignments pra
1319 where pra.budget_version_id = p_budget_version_id
1320 and pra.resource_assignment_id = p_resource_assignment_id
1321 and exists(select 1
1322 from pa_bc_packets pbc
1323 where pbc.budget_version_id = pra.budget_version_id
1324 and pbc.project_id = pra.project_id
1325 and pbc.task_id = pra.task_id
1326 and pbc.resource_list_member_id = pra.resource_list_member_id
1327 and pbc.period_name = p_period_name
1328 and pbc.status_code in ('A','P') );
1329 Exception
1330 When no_data_found then
1331 return 'Y';
1332 End; -- 'L' : pa_bc_packets
1333 End; -- 'L' : pa_bc_balances
1334
1335 End if; --Check funds or regular mode
1336
1337 -- ------------------------------- 'T' ---------------------------------------------------+
1338 ElsIf l_budget_entry_level_code = 'T' then
1339
1340 If (nvl(pa_budget_fund_pkg.g_processing_mode,'CHECK_FUNDS') = 'CHECK_FUNDS') then
1341
1342 -- If its Check funds, then we need to look at data from pa_bc_commitments_all
1343 Begin -- 'T' : pa_bc_packets
1344
1345 Select 'N'
1346 into l_return_status
1347 from pa_resource_assignments pra
1348 where pra.budget_version_id = p_budget_version_id
1349 and pra.resource_assignment_id = p_resource_assignment_id
1350 and exists(select 1
1351 from pa_bc_packets pbc
1352 where pbc.project_id = pra.project_id
1353 and pbc.resource_list_member_id = pra.resource_list_member_id
1354 and pbc.period_name = p_period_name
1355 and pbc.top_task_id = pra.task_id);
1356 Exception
1357 When no_data_found then
1358 Begin -- 'T' : pa_bc_commitments_all
1359 Select 'N'
1360 into l_return_status
1361 from pa_resource_assignments pra
1362 where pra.budget_version_id = p_budget_version_id
1363 and pra.resource_assignment_id = p_resource_assignment_id
1364 and exists(select 1
1365 from pa_bc_commitments_all pbc
1366 where pbc.project_id = pra.project_id
1367 and pbc.resource_list_member_id = pra.resource_list_member_id
1368 and pbc.period_name = p_period_name
1369 and pbc.top_task_id = pra.task_id);
1370 Exception
1371 When no_data_found then
1372 return 'Y';
1373 End; -- 'T' : pa_bc_commitments_all
1374
1375 End; -- 'T' : pa_bc_packets
1376
1377 Else
1378
1379 Begin -- 'T' : pa_bc_balances
1380
1381 Select 'N'
1382 into l_return_status
1383 from pa_resource_assignments pra,
1384 pa_budget_lines pbl
1385 where pbl.budget_version_id = p_budget_version_id
1386 and pbl.resource_assignment_id = p_resource_assignment_id
1387 and pbl.period_name = P_period_name
1388 and pra.resource_assignment_id = pbl.resource_assignment_id
1389 and pra.budget_version_id = pbl.budget_version_id
1390 and exists(select 1
1391 from pa_bc_balances pbb
1392 where pbb.budget_version_id = pra.budget_version_id
1393 and pbb.project_id = pra.project_id
1394 and pbb.top_task_id = pra.task_id
1395 and pbb.resource_list_member_id = pra.resource_list_member_id
1396 and trunc(pbb.start_date) = trunc(pbl.start_date)
1397 and pbb.balance_type <> 'BGT');
1398 Exception
1399 When no_data_found then
1400
1401 Begin -- 'T' : pa_bc_packets
1402
1403 Select 'N'
1404 into l_return_status
1405 from pa_resource_assignments pra
1406 where pra.budget_version_id = p_budget_version_id
1407 and pra.resource_assignment_id = p_resource_assignment_id
1408 and exists(select 1
1409 from pa_bc_packets pbc
1410 where pbc.budget_version_id = pra.budget_version_id
1411 and pbc.project_id = pra.project_id
1412 and pbc.top_task_id = pra.task_id
1413 and pbc.resource_list_member_id = pra.resource_list_member_id
1414 and pbc.period_name = p_period_name
1415 and pbc.status_code in ('A','P') );
1416 Exception
1417 When no_data_found then
1418 return 'Y';
1419 End; -- 'T' : pa_bc_packets
1420 End; -- 'T' : pa_bc_balances
1421
1422 End If; -- checkfunds or regular mode
1423
1424 -- ------------------------------- 'P' ---------------------------------------------------+
1425 ElsIf l_budget_entry_level_code = 'P' then
1426
1427 If (nvl(pa_budget_fund_pkg.g_processing_mode,'CHECK_FUNDS') = 'CHECK_FUNDS') then
1428
1429 -- If its Check funds, then we need to look at data from pa_bc_commitments_all
1430 Begin -- 'P' : pa_bc_packets
1431
1432 Select 'N'
1433 into l_return_status
1434 from pa_resource_assignments pra
1435 where pra.budget_version_id = p_budget_version_id
1436 and pra.resource_assignment_id = p_resource_assignment_id
1437 and exists(select 1
1438 from pa_bc_packets pbc
1439 where pbc.project_id = pra.project_id
1440 and pbc.resource_list_member_id = pra.resource_list_member_id
1441 and pbc.period_name = p_period_name);
1442 Exception
1443 When no_data_found then
1444 Begin -- 'P' : pa_bc_commitments_all
1445 Select 'N'
1446 into l_return_status
1447 from pa_resource_assignments pra
1448 where pra.budget_version_id = p_budget_version_id
1449 and pra.resource_assignment_id = p_resource_assignment_id
1450 and exists(select 1
1451 from pa_bc_commitments_all pbc
1452 where pbc.project_id = pra.project_id
1453 and pbc.resource_list_member_id = pra.resource_list_member_id
1454 and pbc.period_name = p_period_name);
1455 Exception
1456 When no_data_found then
1457 return 'Y';
1458 End; -- 'P' : pa_bc_commitments_all
1459
1460 End; -- 'P' : pa_bc_packets
1461
1462 Else
1463
1464 Begin -- 'P' : pa_bc_balances
1465
1466 Select 'N'
1467 into l_return_status
1468 from pa_resource_assignments pra,
1469 pa_budget_lines pbl
1470 where pbl.budget_version_id = p_budget_version_id
1471 and pbl.resource_assignment_id = p_resource_assignment_id
1472 and pbl.period_name = P_period_name
1473 and pra.resource_assignment_id = pbl.resource_assignment_id
1474 and pra.budget_version_id = pbl.budget_version_id
1475 and exists(select 1
1476 from pa_bc_balances pbb
1477 where pbb.budget_version_id = pra.budget_version_id
1478 and pbb.project_id = pra.project_id
1479 and pbb.resource_list_member_id = pra.resource_list_member_id
1480 and trunc(pbb.start_date) = trunc(pbl.start_date)
1481 and pbb.balance_type <> 'BGT');
1482
1483 Exception
1484 When no_data_found then
1485
1486 Begin -- 'P' : pa_bc_packets
1487
1488 Select 'N'
1489 into l_return_status
1490 from pa_resource_assignments pra
1491 where pra.budget_version_id = p_budget_version_id
1492 and pra.resource_assignment_id = p_resource_assignment_id
1493 and exists(select 1
1494 from pa_bc_packets pbc
1495 where pbc.budget_version_id = pra.budget_version_id
1496 and pbc.project_id = pra.project_id
1497 and pbc.resource_list_member_id = pra.resource_list_member_id
1498 and pbc.period_name = p_period_name
1499 and pbc.status_code in ('A','P') );
1500 Exception
1501 When no_data_found then
1502 return 'Y';
1503 End; -- 'P' : pa_bc_packets
1504 End; -- 'P' : pa_bc_balances
1505
1506 End if; -- checkfunds or normal mode
1507
1508 End If;
1509
1510 RETURN l_return_status; -- Value will be 'N'
1511 ======================================================================== */
1512 End Is_account_change_allowed;
1513
1514 -- --------------------------------------------------------------------------------------+
1515 -- ## Another variation of is_account_change_allowed
1516 -- ## This is called from pa_budget_account_pkg and pa_funds_control_pkg
1517 -- ## This API was written as the previous one was not performing as required
1518 -- ## Above API was being used from budgets form, but eventually we will remove that
1519 -- ## dependancy too
1520
1521 FUNCTION Is_Account_change_allowed2
1522 (p_budget_version_id IN Number,
1523 p_project_id IN Number,
1524 p_top_task_id IN Number,
1525 p_task_id IN Number,
1526 p_parent_resource_id IN Number,
1527 p_resource_list_member_id IN Number,
1528 p_start_date IN Date,
1529 p_period_name IN Varchar2,
1530 p_entry_level_code IN Varchar2,
1531 p_mode IN Varchar2)
1532 return Varchar2
1533 IS
1534 l_budget_entry_level_code pa_budget_entry_methods.entry_level_code%type;
1535 l_allowed_flag varchar2(1);
1536 Begin
1537 IF P_DEBUG_MODE = 'Y' THEN
1538 pa_fck_util.debug_msg('In Is_account_change_allowed2:');
1539 END IF;
1540
1541 l_allowed_flag := 'Y';
1542
1543 -- # Determine entry level code if its 'M'.
1544 If p_entry_level_code = 'M' then
1545 -- task_id being passed is that on pa_resource_assignments ...
1546 -- top task id is determined from pa_tasks
1547 If p_task_id = p_top_task_id then
1548 l_budget_entry_level_code := 'T';
1549 Else
1550 l_budget_entry_level_code := 'L';
1551 End If;
1552
1553 Else
1554 l_budget_entry_level_code := p_entry_level_code;
1555 End if;
1556
1557 IF P_DEBUG_MODE = 'Y' THEN
1558 pa_fck_util.debug_msg('Is_account_change_allowed2: l_budget_entry_level_code::'||l_budget_entry_level_code);
1559 END IF;
1560
1561 If p_mode = 'FORM' then -- ------------------------------+ FORM
1562 -- ------------------------------------------------------------------------+
1563 -- # Account check for Lowest Task level
1564 If l_budget_entry_level_code = 'L' then
1565 Begin
1566
1567 Select 'N'
1568 into l_allowed_flag
1569 from dual
1570 where exists(select 1
1571 from pa_bc_balances pbb
1572 where pbb.budget_version_id = p_budget_version_id
1573 and pbb.task_id = p_task_id
1574 and nvl(pbb.parent_member_id,-99) = nvl(p_parent_resource_id,-99)
1575 and pbb.resource_list_member_id = p_resource_list_member_id
1576 and pbb.start_date = p_start_date
1577 and pbb.balance_type <> 'BGT');
1578
1579 Exception
1580 When no_data_found then
1581 Select 'N'
1582 into l_allowed_flag
1583 from dual
1584 where exists(select 1
1585 from pa_bc_packets pbc
1586 where pbc.budget_version_id = p_budget_version_id
1587 and pbc.bud_task_id = p_task_id
1588 and pbc.bud_resource_list_member_id = p_resource_list_member_id
1589 and pbc.period_name = p_period_name
1590 and pbc.status_code in ('A','P','I','Z') );
1591 End;
1592
1593 End If; -- 'L'
1594 -- ------------------------------------------------------------------------+
1595 -- # Account check for Top Task level
1596 If l_budget_entry_level_code = 'T' then
1597 Begin
1598
1599 Select 'N'
1600 into l_allowed_flag
1601 from dual
1602 where exists(select 1
1603 from pa_bc_balances pbb
1604 where pbb.budget_version_id = p_budget_version_id
1605 and pbb.top_task_id = p_top_task_id
1606 and nvl(pbb.parent_member_id,-99) = nvl(p_parent_resource_id,-99)
1607 and pbb.resource_list_member_id = p_resource_list_member_id
1608 and pbb.start_date = p_start_date
1609 and pbb.balance_type <> 'BGT');
1610
1611 Exception
1612 When no_data_found then
1613 Select 'N'
1614 into l_allowed_flag
1615 from dual
1616 where exists(select 1
1617 from pa_bc_packets pbc
1618 where pbc.budget_version_id = p_budget_version_id
1619 and pbc.bud_task_id = p_top_task_id
1620 and pbc.bud_resource_list_member_id = p_resource_list_member_id
1621 and pbc.period_name = p_period_name
1622 and pbc.status_code in ('A','P','I','Z') );
1623 End;
1624
1625 End If; -- 'T'
1626 -- ------------------------------------------------------------------------+
1627 -- # Account check for Project level
1628 If l_budget_entry_level_code = 'P' then
1629 Begin
1630
1631 Select 'N'
1632 into l_allowed_flag
1633 from dual
1634 where exists(select 1
1635 from pa_bc_balances pbb
1636 where pbb.budget_version_id = p_budget_version_id
1637 and nvl(pbb.parent_member_id,-99) = nvl(p_parent_resource_id,-99)
1638 and pbb.resource_list_member_id = p_resource_list_member_id
1639 and pbb.start_date = p_start_date
1640 and pbb.balance_type <> 'BGT');
1641
1642 Exception
1643 When no_data_found then
1644 Select 'N'
1645 into l_allowed_flag
1646 from dual
1647 where exists(select 1
1648 from pa_bc_packets pbc
1649 where pbc.budget_version_id = p_budget_version_id
1650 and pbc.bud_resource_list_member_id = p_resource_list_member_id
1651 and pbc.period_name = p_period_name
1652 and pbc.status_code in ('A','P','I','Z') );
1653 End;
1654
1655 End If; -- 'P'
1656
1657
1658 -- ------------------------------------------------------------------------+
1659 End If; --If p_mode = 'FORM' then -- ------------------------------+ FORM
1660
1661
1662 If p_mode = 'BASELINE' then -- ------------------------------+ BASELINE
1663 -- ------------------------------------------------------------------------+
1664 -- # Account check for Lowest Task level
1665 If l_budget_entry_level_code = 'L' then
1666 Begin
1667
1668 Select 'N'
1669 into l_allowed_flag
1670 from dual
1671 where exists(select 1
1672 from pa_bc_packets pbc
1673 where pbc.budget_version_id = p_budget_version_id
1674 and pbc.bud_task_id = p_task_id
1675 and pbc.bud_resource_list_member_id = p_resource_list_member_id
1676 and pbc.period_name = p_period_name
1677 and pbc.status_code = 'A');
1678 Exception
1679 When no_data_found then
1680 Select 'N'
1681 into l_allowed_flag
1682 from dual
1683 where exists(select 1
1684 from pa_bc_balances pbb
1685 where pbb.budget_version_id = p_budget_version_id
1686 and pbb.task_id = p_task_id
1687 and nvl(pbb.parent_member_id,-99) = nvl(p_parent_resource_id,-99)
1688 and pbb.resource_list_member_id = p_resource_list_member_id
1689 and pbb.start_date = p_start_date
1690 and pbb.balance_type <> 'BGT');
1691
1692 End;
1693
1694 End If; -- 'L'
1695 -- ------------------------------------------------------------------------+
1696 -- # Account check for Top Task level
1697 If l_budget_entry_level_code = 'T' then
1698 Begin
1699
1700 Select 'N'
1701 into l_allowed_flag
1702 from dual
1703 where exists(select 1
1704 from pa_bc_packets pbc
1705 where pbc.budget_version_id = p_budget_version_id
1706 and pbc.bud_task_id = p_top_task_id
1707 and pbc.bud_resource_list_member_id = p_resource_list_member_id
1708 and pbc.period_name = p_period_name
1709 and pbc.status_code = 'A');
1710 Exception
1711 When no_data_found then
1712 Select 'N'
1713 into l_allowed_flag
1714 from dual
1715 where exists(select 1
1716 from pa_bc_balances pbb
1717 where pbb.budget_version_id = p_budget_version_id
1718 and pbb.top_task_id = p_top_task_id
1719 and nvl(pbb.parent_member_id,-99) = nvl(p_parent_resource_id,-99)
1720 and pbb.resource_list_member_id = p_resource_list_member_id
1721 and pbb.start_date = p_start_date
1722 and pbb.balance_type <> 'BGT');
1723
1724 End;
1725
1726 End If; -- 'T'
1727 -- ------------------------------------------------------------------------+
1728 -- # Account check for Project level
1729 If l_budget_entry_level_code = 'P' then
1730 Begin
1731
1732 Select 'N'
1733 into l_allowed_flag
1734 from dual
1735 where exists(select 1
1736 from pa_bc_packets pbc
1737 where pbc.budget_version_id = p_budget_version_id
1738 and pbc.bud_resource_list_member_id = p_resource_list_member_id
1739 and pbc.period_name = p_period_name
1740 and pbc.status_code = 'A');
1741 Exception
1742 When no_data_found then
1743 Select 'N'
1744 into l_allowed_flag
1745 from dual
1746 where exists(select 1
1747 from pa_bc_balances pbb
1748 where pbb.budget_version_id = p_budget_version_id
1749 and nvl(pbb.parent_member_id,-99) = nvl(p_parent_resource_id,-99)
1750 and pbb.resource_list_member_id = p_resource_list_member_id
1751 and pbb.start_date = p_start_date
1752 and pbb.balance_type <> 'BGT');
1753
1754 End;
1755
1756 End If; -- 'P'
1757
1758
1759 -- ------------------------------------------------------------------------+
1760 End If; --If p_mode = 'FORM' then -- ------------------------------+ BASELINE
1761
1762 IF P_DEBUG_MODE = 'Y' THEN
1763 pa_fck_util.debug_msg('In Is_account_change_allowed2:l_allowed_flag::'||l_allowed_flag);
1764 END IF;
1765
1766 RETURN l_allowed_flag;
1767
1768 Exception
1769 When no_data_found then
1770 IF P_DEBUG_MODE = 'Y' THEN
1771 pa_fck_util.debug_msg('In Is_account_change_allowed2:l_allowed_flag::'||'Y');
1772 END IF;
1773
1774 RETURN 'Y';
1775 End Is_account_change_allowed2;
1776 -- ----------------------------------------------------------------------------+
1777
1778 -- /*============================================================================+
1779 -- R12 Funds management enhancement --rshaik
1780 -- API name : get_sla_notupgraded_flag
1781 -- Type : private
1782 -- Description : Returns Y/N depending on whether the distribution and associated
1783 -- budget are non upgraded
1784 -- This procedure calls PSA_BC_XLA_PUB.Get_sla_notupgraded_flag
1785 -- for both REQ/PO transaction and associated budget .Even if one them
1786 -- is not upgraded then this function will consider it as nonupgraded
1787 -- transaction and returns 'Y'.
1788 -- /*============================================================================+
1789
1790 FUNCTION get_sla_notupgraded_flag ( p_application_id IN NUMBER,
1791 p_entity_code IN VARCHAR2,
1792 p_document_header_id IN NUMBER,
1793 p_document_distribution_id IN NUMBER,
1794 p_dist_link_type IN VARCHAR2,
1795 p_budget_version_id IN NUMBER,
1796 p_budget_line_id IN NUMBER )
1797 RETURN VARCHAR2 IS
1798
1799 -- Bug 5503577 : Cursor to check if the budget has been baselined in R12.
1800 CURSOR c_check_budget_upg_status IS
1801 SELECT 'UPGRADED'
1802 FROM pa_budget_lines
1803 WHERE budget_version_id = p_budget_version_id
1804 AND bc_event_id IS NOT NULL
1805 AND rownum = 1;
1806
1807 l_budget_upgraded_status VARCHAR2(10);
1808
1809 BEGIN
1810
1811 -- Check if transaction is not upgraded
1812 IF PSA_BC_XLA_PUB.Get_sla_notupgraded_flag
1813 (p_application_id => p_application_id ,
1814 p_entity_code => p_entity_code,
1815 p_source_id_int_1 => p_document_header_id,
1816 p_dist_link_type => p_dist_link_type,
1817 p_distribution_id => p_document_distribution_id) = 'Y' THEN
1818 RETURN 'Y';
1819
1820 END IF;
1821
1822 -- Bug 5503577 : Check if associated budget is not upgraded
1823 -- Note : p_budget_line_id will be NULL when called from sla extracts ,as fundscheck is not yet fired.
1824
1825 OPEN c_check_budget_upg_status;
1826 FETCH c_check_budget_upg_status INTO l_budget_upgraded_status;
1827 CLOSE c_check_budget_upg_status;
1828
1829 IF NVL(l_budget_upgraded_status,'NOTUPGRADED') = 'NOTUPGRADED' THEN
1830 RETURN 'Y';
1831 ELSE
1832 RETURN 'N';
1833 END IF;
1834
1835 EXCEPTION
1836 WHEN OTHERS THEN
1837 RETURN 'N';
1838 END get_sla_notupgraded_flag;
1839
1840 -- Bug 5206341 : Function to check if there exists any closed periods in current budget version
1841
1842 FUNCTION CLOSED_PERIODS_EXISTS_IN_BUDG (p_budget_version_id IN NUMBER)
1843 RETURN VARCHAR2 IS
1844
1845 CURSOR c_closed_periods_exists IS
1846 SELECT 'Y'
1847 FROM PA_BUDGET_ACCT_LINES PBA,
1848 GL_PERIOD_STATUSES GLS
1849 WHERE GLS.application_id = 101
1850 AND GLS.set_of_books_id in (SELECT set_of_books_id FROM pa_implementations)
1851 AND GLS.period_name = PBA.gl_period_name
1852 AND GLS.closing_status = 'C'
1853 AND PBA.budget_version_id = p_budget_version_id
1854 AND rownum = 1;
1855
1856 l_closed_periods_exists VARCHAR2(1) := NULL;
1857
1858 BEGIN
1859
1860 OPEN c_closed_periods_exists;
1861 FETCH c_closed_periods_exists INTO l_closed_periods_exists;
1862 CLOSE c_closed_periods_exists;
1863
1864 RETURN NVL(l_closed_periods_exists,'N');
1865
1866 END CLOSED_PERIODS_EXISTS_IN_BUDG;
1867
1868 --=======================================================================================+
1869 -- #R12 Funds management enhancement
1870 -- #API name : Update_bvid_blid_on_cdl_bccom
1871 -- #Type : private
1872 -- #Description : Stamps latest budget version id and budget_line_id on
1873 -- 1. CDL when called from baselining process
1874 -- 2. CDL and bc commitments when called from yearend rollover process
1875 --=======================================================================================+
1876
1877 PROCEDURE Update_bvid_blid_on_cdl_bccom ( p_bud_ver_id IN NUMBER,
1878 p_calling_mode IN VARCHAR2) IS
1879
1880 l_DocHdrTab PA_PLSQL_DATATYPES.IdTabTyp;
1881 l_DocDistTab PA_PLSQL_DATATYPES.IdTabTyp;
1882 l_bccomidTab PA_PLSQL_DATATYPES.IdTabTyp;
1883 l_bvidTab PA_PLSQL_DATATYPES.IdTabTyp;
1884 l_blidTab PA_PLSQL_DATATYPES.IdTabTyp;
1885 l_burcodeTab PA_PLSQL_DATATYPES.Char10TabTyp;
1886 l_projidTab PA_PLSQL_DATATYPES.IdTabTyp;
1887 l_taskidTab PA_PLSQL_DATATYPES.IdTabTyp;
1888 l_toptaskidTab PA_PLSQL_DATATYPES.IdTabTyp;
1889 l_rlmidTab PA_PLSQL_DATATYPES.IdTabTyp;
1890 l_startdateTab PA_PLSQL_DATATYPES.DateTabTyp;
1891 l_entrylevelcode PA_PLSQL_DATATYPES.Char10TabTyp;
1892 l_glprdstatustab PA_PLSQL_DATATYPES.Char10TabTyp;
1893 l_closed_prd_exists VARCHAR2(1);
1894
1895
1896 -- Driving cursor for updating CDL budget version and line details for baselining process
1897 -- Note: Sweeper process will be later run by baselining process which will handle bc_commitments update.
1898 CURSOR c_bc_packets IS
1899 SELECT bc.document_header_id,
1900 bc.document_distribution_id,
1901 bc.bc_commitment_id,
1902 bc.project_id,
1903 bc.task_id,
1904 bc.top_task_id,
1905 bc.resource_list_member_id,
1906 NULL start_date, -- Required only for closed period transactions
1907 bc.burden_method_code,
1908 NULL entry_level_code, -- Required only for closed period transactions
1909 bc.budget_version_id,
1910 bc.budget_line_id,
1911 NULL gl_period_status -- Required only for closed period transactions
1912 from pa_bc_packets bc
1913 WHERE bc.budget_version_id = p_bud_ver_id -- current baselined version id
1914 AND bc.status_code ='A'
1915 -- Parent bc packet id will be -99 for BTC and CWK lines --check logic in PA_BGT_BASELINE_PKG
1916 AND NVL(bc.parent_bc_packet_id,-99) = -99
1917 AND bc.actual_flag ='A'
1918 AND bc.document_type ='EXP'
1919 UNION ALL
1920 -- Bug 5206341 : Cursor to pick transactions associated with last baselined version and which were not picked in current
1921 -- run as the GL period has been closed.
1922 SELECT bc.exp_item_id,
1923 to_number(bc.reference3),
1924 bc.bc_commitment_id,
1925 bc.project_id,
1926 bc.task_id,
1927 bc.top_task_id,
1928 bc.resource_list_member_id,
1929 gl.start_date,
1930 bc.burden_method_code,
1931 BEM.entry_level_code,
1932 p_bud_ver_id budget_version_id,
1933 NULL budget_line_id,
1934 'C' gl_period_status
1935 FROM pa_bc_commitments bc,
1936 pa_budget_versions pbv,
1937 pa_budget_entry_methods bem,
1938 gl_period_statuses gl
1939 WHERE GL.application_id = 101
1940 AND GL.set_of_books_id = bc.set_of_books_id
1941 AND gl.period_name = bc.period_name
1942 AND GL.closing_status = 'C'
1943 AND bc.budget_version_id = pbv.budget_version_id
1944 AND BEM.Budget_Entry_Method_Code = PBV.Budget_Entry_Method_Code
1945 AND pbv.budget_version_id = pa_budget_fund_pkg.g_cost_prev_bvid
1946 AND l_closed_prd_exists = 'Y';
1947
1948 -- Driving cursor for updating CDL and bc_commitments budget version and budget line for year end rollover process
1949 -- Note : Sweeper process is run in start of year end rollover process, hence all the data exists in bc_commitments
1950 CURSOR c_bccom_packets IS
1951 SELECT bc.exp_item_id,
1952 to_number(bc.reference3),
1953 bc.bc_commitment_id,
1954 bc.project_id,
1955 bc.task_id,
1956 bc.top_task_id,
1957 bc.resource_list_member_id,
1958 gl.start_date,
1959 bc.burden_method_code,
1960 BEM.entry_level_code,
1961 p_bud_ver_id budget_version_id,
1962 NULL budget_line_id,
1963 DECODE(pbv.budget_version_id,pa_budget_fund_pkg.g_cost_prev_bvid,'C',NULL) gl_period_status
1964 from pa_bc_commitments bc,
1965 pa_budget_versions pbv,
1966 pa_budget_entry_methods bem,
1967 gl_period_statuses gl
1968 WHERE GL.application_id = 101
1969 AND GL.set_of_books_id = bc.set_of_books_id
1970 AND gl.period_name = bc.period_name
1971 AND GL.closing_status = DECODE(pbv.budget_version_id,pa_budget_fund_pkg.g_cost_prev_bvid,'C',GL.closing_status)
1972 AND bc.budget_version_id = pbv.budget_version_id
1973 AND BEM.Budget_Entry_Method_Code = PBV.Budget_Entry_Method_Code
1974 AND pbv.budget_version_id in (SELECT p_bud_ver_id
1975 FROM dual
1976 UNION ALL
1977 -- Bug 5206341 :Transactions in closed period are picked for latest budget details stamping
1978 SELECT pa_budget_fund_pkg.g_cost_prev_bvid
1979 FROM dual
1980 WHERE l_closed_prd_exists = 'Y');
1981
1982
1983 l_ccid pa_bc_packets.budget_ccid%type := null;
1984 l_error_message_code varchar2(200) := null;
1985 l_return_status varchar2(10) := 'S';
1986
1987 PROCEDURE Intialize_plsql_tables IS
1988 BEGIN
1989 l_DocHdrTab.delete;
1990 l_DocDistTab.delete;
1991 l_bccomidTab.delete;
1992 l_bvidTab.delete;
1993 l_blidTab.delete;
1994 l_burcodeTab.delete;
1995 l_projidTab.delete;
1996 l_taskidTab.delete;
1997 l_toptaskidTab.delete;
1998 l_rlmidTab.delete;
1999 l_startdateTab.delete;
2000 l_entrylevelcode.delete;
2001 l_glprdstatustab.delete;
2002
2003 END Intialize_plsql_tables;
2004
2005 BEGIN
2006
2007
2008 IF P_DEBUG_MODE = 'Y' THEN
2009 pa_fck_util.debug_msg('Update_bvid_blid_on_cdl_bccom: Start p_bud_ver_id ='||p_bud_ver_id );
2010 pa_fck_util.debug_msg('Update_bvid_blid_on_cdl_bccom: Start p_calling_mode ='||p_calling_mode );
2011 END IF;
2012
2013 -- Bug 5206341 : Check if closed GL periods exists for this baseline run.
2014 -- If exists we need to have additional logic to stamp latest budget version id and
2015 -- budget_line_id on CDL and bc commitments as baseline process donot pick these
2016 -- transactions for fundschecking
2017
2018 l_closed_prd_exists := PA_FUNDS_CONTROL_UTILS.CLOSED_PERIODS_EXISTS_IN_BUDG(p_bud_ver_id);
2019
2020 IF P_DEBUG_MODE = 'Y' THEN
2021 pa_fck_util.debug_msg('Update_bvid_blid_on_cdl_bccom: Start l_closed_prd_exists ='||l_closed_prd_exists );
2022 pa_fck_util.debug_msg('Update_bvid_blid_on_cdl_bccom: Start p_bud_ver_id ='||p_bud_ver_id );
2023 END IF;
2024
2025 IF p_calling_mode = 'RESERVE_BASELINE' THEN
2026
2027 OPEN c_bc_packets;
2028 LOOP
2029
2030 IF P_DEBUG_MODE = 'Y' THEN
2031 pa_fck_util.debug_msg('Clearing local plsql tabs');
2032 END IF;
2033
2034 Intialize_plsql_tables;
2035
2036 FETCH c_bc_packets BULK COLLECT INTO
2037 l_DocHdrTab,
2038 l_DocDistTab,
2039 l_bccomidTab,
2040 l_projidTab,
2041 l_taskidTab,
2042 l_toptaskidTab,
2043 l_rlmidTab,
2044 l_startdateTab,
2045 l_burcodeTab,
2046 l_entrylevelcode,
2047 l_bvidTab,
2048 l_blidTab,
2049 l_glprdstatustab
2050 LIMIT 500;
2051
2052 IF P_DEBUG_MODE = 'Y' THEN
2053 pa_fck_util.debug_msg('Number of records fetched from pa_bc_packets ='||l_DocHdrTab.count);
2054 END IF;
2055
2056 FOR i in 1..l_DocHdrTab.count LOOP
2057 pa_fck_util.debug_msg('Value of l_DocHdrTab ('||i||')='||l_DocHdrTab(i));
2058 pa_fck_util.debug_msg('Value of l_DocDistTab('||i||')='||l_DocDistTab(i));
2059 pa_fck_util.debug_msg('Value of l_bccomidTab ('||i||')='||l_bccomidTab(i));
2060 pa_fck_util.debug_msg('Value of l_projidTab ('||i||')='||l_projidTab(i));
2061 pa_fck_util.debug_msg('Value of l_taskidTab ('||i||')='||l_taskidTab(i));
2062 pa_fck_util.debug_msg('Value of l_toptaskidTab ('||i||')='||l_toptaskidTab(i));
2063 pa_fck_util.debug_msg('Value of l_rlmidTab ('||i||')='||l_rlmidTab(i));
2064 pa_fck_util.debug_msg('Value of l_startdateTab ('||i||')='||l_startdateTab(i));
2065 pa_fck_util.debug_msg('Value of l_entrylevelcode ('||i||')='||l_entrylevelcode(i));
2066 pa_fck_util.debug_msg('Value of l_burcodeTab ('||i||')='||l_burcodeTab(i));
2067 pa_fck_util.debug_msg('Value of l_bvidTab ('||i||')='||l_bvidTab(i));
2068 pa_fck_util.debug_msg('Value of l_glprdstatustab ('||i||')='||l_glprdstatustab(i));
2069 END LOOP;
2070
2071 IF l_DocHdrTab.count = 0 THEN
2072 IF P_DEBUG_MODE = 'Y' THEN
2073 pa_fck_util.debug_msg('No more records to process');
2074 END IF;
2075 EXIT;
2076 END IF;
2077
2078 -- Bug 5206341 : Logic to derive new budget_line_id for transactions which are falling with in
2079 -- closed periods.For transactions falling in open/future periods ,baselining process has already derived
2080 -- the latest budget details.
2081 IF l_closed_prd_exists = 'Y' THEN
2082
2083 FOR i in l_DocHdrTab.first..l_DocHdrTab.last LOOP
2084
2085 IF l_glprdstatustab(i) = 'C' THEN
2086
2087 IF P_DEBUG_MODE = 'Y' THEN
2088 pa_fck_util.debug_msg('Update_bvid_blid_on_cdl_bccom: Deriving budget details for closed period txns');
2089 END IF;
2090
2091 Get_Budget_CCID (
2092 p_project_id => l_projidTab(i),
2093 p_task_id => l_taskidTab(i),
2094 p_top_task_id => l_toptaskidTab(i),
2095 p_res_list_mem_id => l_rlmidTab(i),
2096 p_start_date => l_startdateTab(i),
2097 p_budget_version_id => l_bvidTab(i),
2098 p_entry_level_code => l_entrylevelcode(i),
2099 x_budget_ccid => l_ccid,
2100 x_budget_line_id => l_blidTab(i),
2101 x_return_status => l_return_status,
2102 x_error_message_code => l_error_message_code );
2103
2104 END IF;
2105
2106 IF P_DEBUG_MODE = 'Y' THEN
2107 pa_fck_util.debug_msg( 'Value of l_blidTab(i) '||l_blidTab(i));
2108 END IF;
2109
2110 END LOOP;
2111
2112 END IF; -- IF l_closed_prd_exists = 'Y' THEN
2113
2114 IF P_DEBUG_MODE = 'Y' THEN
2115 pa_fck_util.debug_msg( 'Updating budget_version_id and budget_line_id on pa_cost_distribution_lines_all ');
2116 END IF;
2117
2118 FORALL i in l_DocHdrTab.first..l_DocHdrTab.last
2119 UPDATE pa_cost_distribution_lines_all cdl
2120 SET cdl.budget_version_id = NVL(l_bvidTab(i),cdl.budget_version_id),
2121 cdl.budget_line_id = NVL(l_blidTab(i),cdl.budget_line_id)
2122 WHERE cdl.expenditure_item_id = l_DocHdrTab(i)
2123 AND ( cdl.line_num = l_DocDistTab(i) OR (l_burcodeTab(i) = 'S' AND cdl.line_type ='D'))
2124 AND cdl.budget_version_id IS NOT NULL
2125 AND (cdl.acct_event_id IS NULL OR -- events which are not processed by SLA
2126 EXISTS (SELECT 1
2127 FROM xla_events xev
2128 WHERE xev.event_id = cdl.acct_event_id
2129 AND xev.application_id = 275
2130 AND xev.process_status_code <> 'P' )
2131 );
2132
2133 IF P_DEBUG_MODE = 'Y' THEN
2134 pa_fck_util.debug_msg( 'Number of pa_cost_distribution_lines_all updated'||SQL%ROWCOUNT);
2135 pa_fck_util.debug_msg( 'Updating budget_version_id and budget_line_id on pa_bc_commitments_all ');
2136 END IF;
2137
2138 -- Bug 5206341 : Logic to stamp new budget_line_id for transactions which are falling with in
2139 -- closed periods.For transactions falling in open/future periods ,sweeper process will be updating
2140 -- the latest budget details.
2141
2142 IF l_closed_prd_exists = 'Y' THEN
2143
2144 FORALL i in l_DocHdrTab.first..l_DocHdrTab.last
2145 UPDATE pa_bc_commitments_all bccom
2146 SET bccom.budget_version_id = NVL(l_bvidTab(i), bccom.budget_version_id),
2147 bccom.budget_line_id = NVL(l_blidTab(i),bccom.budget_line_id)
2148 WHERE bccom.bc_commitment_id = l_bccomidTab(i)
2149 AND l_glprdstatustab(i) = 'C'
2150 AND bccom.budget_version_id = pa_budget_fund_pkg.g_cost_prev_bvid;
2151
2152 IF P_DEBUG_MODE = 'Y' THEN
2153 pa_fck_util.debug_msg( 'Number of pa_bc_commitments_all updated'||SQL%ROWCOUNT);
2154 END IF;
2155
2156 END IF; -- IF l_closed_prd_exists = 'Y' THEN
2157
2158 END LOOP;
2159 CLOSE c_bc_packets;
2160
2161 ELSE --IF p_calling_mode = 'YEAR END ROLLOVER' THEN
2162
2163 -- For year end rollover , below update will update latest budget version id and budget line id
2164 -- on expenditure records which got created while interfacing AP/PO to projects
2165
2166 OPEN c_bccom_packets;
2167 LOOP
2168
2169 IF P_DEBUG_MODE = 'Y' THEN
2170 pa_fck_util.debug_msg('Clearing local plsql tabs');
2171 END IF;
2172
2173 Intialize_plsql_tables;
2174
2175 FETCH c_bccom_packets BULK COLLECT INTO
2176 l_DocHdrTab,
2177 l_DocDistTab,
2178 l_bccomidTab,
2179 l_projidTab,
2180 l_taskidTab,
2181 l_toptaskidTab,
2182 l_rlmidTab,
2183 l_startdateTab,
2184 l_burcodeTab,
2185 l_entrylevelcode,
2186 l_bvidTab,
2187 l_blidTab,
2188 l_glprdstatustab
2189 LIMIT 500;
2190
2191 IF P_DEBUG_MODE = 'Y' THEN
2192 pa_fck_util.debug_msg('Number of records fetched from pa_bc_packets ='||l_DocHdrTab.count);
2193 END IF;
2194
2195 FOR i in 1..l_DocHdrTab.count LOOP
2196 pa_fck_util.debug_msg('Value of l_DocHdrTab ('||i||')='||l_DocHdrTab(i));
2197 pa_fck_util.debug_msg('Value of l_DocDistTab('||i||')='||l_DocDistTab(i));
2198 pa_fck_util.debug_msg('Value of l_bccomidTab ('||i||')='||l_bccomidTab(i));
2199 pa_fck_util.debug_msg('Value of l_projidTab ('||i||')='||l_projidTab(i));
2200 pa_fck_util.debug_msg('Value of l_taskidTab ('||i||')='||l_taskidTab(i));
2201 pa_fck_util.debug_msg('Value of l_toptaskidTab ('||i||')='||l_toptaskidTab(i));
2202 pa_fck_util.debug_msg('Value of l_rlmidTab ('||i||')='||l_rlmidTab(i));
2203 pa_fck_util.debug_msg('Value of l_startdateTab ('||i||')='||l_startdateTab(i));
2204 pa_fck_util.debug_msg('Value of l_entrylevelcode ('||i||')='||l_entrylevelcode(i));
2205 pa_fck_util.debug_msg('Value of l_burcodeTab ('||i||')='||l_burcodeTab(i));
2206 pa_fck_util.debug_msg('Value of l_bvidTab ('||i||')='||l_bvidTab(i));
2207 pa_fck_util.debug_msg('Value of l_glprdstatustab ('||i||')='||l_glprdstatustab(i));
2208 END LOOP;
2209
2210 IF l_DocHdrTab.count = 0 THEN
2211 IF P_DEBUG_MODE = 'Y' THEN
2212 pa_fck_util.debug_msg('No more records to process');
2213 END IF;
2214 EXIT;
2215 END IF;
2216
2217 IF P_DEBUG_MODE = 'Y' THEN
2218 pa_fck_util.debug_msg( 'deriving budget_version_id and budget_line_id ');
2219 END IF;
2220
2221 FOR i in l_DocHdrTab.first..l_DocHdrTab.last LOOP
2222
2223 Get_Budget_CCID (
2224 p_project_id => l_projidTab(i),
2225 p_task_id => l_taskidTab(i),
2226 p_top_task_id => l_toptaskidTab(i),
2227 p_res_list_mem_id => l_rlmidTab(i),
2228 p_start_date => l_startdateTab(i),
2229 p_budget_version_id => l_bvidTab(i),
2230 p_entry_level_code => l_entrylevelcode(i),
2231 x_budget_ccid => l_ccid,
2232 x_budget_line_id => l_blidTab(i),
2233 x_return_status => l_return_status,
2234 x_error_message_code => l_error_message_code );
2235
2236 IF P_DEBUG_MODE = 'Y' THEN
2237 pa_fck_util.debug_msg( 'Value of l_blidTab(i) '||l_blidTab(i));
2238 END IF;
2239
2240 END LOOP;
2241
2242 IF P_DEBUG_MODE = 'Y' THEN
2243 pa_fck_util.debug_msg( 'Updating budget_version_id and budget_line_id on pa_cost_distribution_lines_all for EXP');
2244 END IF;
2245
2246 FORALL i in l_DocHdrTab.first..l_DocHdrTab.last
2247 UPDATE pa_cost_distribution_lines_all cdl
2248 SET cdl.budget_version_id = NVL(l_bvidTab(i),cdl.budget_version_id),
2249 cdl.budget_line_id = NVL(l_blidTab(i),cdl.budget_line_id)
2250 WHERE cdl.expenditure_item_id = l_DocHdrTab(i)
2251 -- All the pending EXP lines associated with commitment should get updated ,hence no doc_dist_id join
2252 AND ( cdl.line_type = 'R' OR (l_burcodeTab(i) = 'S' AND cdl.line_type ='D'))
2253 AND cdl.budget_version_id IS NOT NULL
2254 AND l_DocHdrTab(i) is NOT NULL -- this record corresponds to EXP record in projects
2255 AND (cdl.acct_event_id IS NULL OR -- events which are not processed by SLA
2256 EXISTS (SELECT 1
2257 FROM xla_events xev
2258 WHERE xev.event_id = cdl.acct_event_id
2259 AND xev.application_id = 275
2260 AND xev.process_status_code <> 'P' )
2261 );
2262
2263 IF P_DEBUG_MODE = 'Y' THEN
2264 pa_fck_util.debug_msg( 'Number of pa_cost_distribution_lines_all updated'||SQL%ROWCOUNT);
2265 pa_fck_util.debug_msg( 'Updating budget_version_id and budget_line_id on pa_cost_distribution_lines_all for BTC');
2266 END IF;
2267
2268 FORALL i in l_DocHdrTab.first..l_DocHdrTab.last
2269 UPDATE pa_cost_distribution_lines_all cdl
2270 SET cdl.budget_version_id = NVL(l_bvidTab(i),cdl.budget_version_id),
2271 cdl.budget_line_id = NVL(l_blidTab(i),cdl.budget_line_id)
2272 WHERE cdl.expenditure_item_id IN (SELECT exp2.expenditure_item_id
2273 FROM pa_cost_distribution_lines_all cdl1,
2274 pa_expenditure_items_all exp2 -- BTC
2275 WHERE cdl1.expenditure_item_id = l_DocHdrTab(i)
2276 AND cdl1.burden_sum_source_run_id = exp2.burden_sum_dest_run_id
2277 AND exp2.system_linkage_function = 'BTC')
2278 AND l_burcodeTab(i) <> 'S'
2279 AND l_DocHdrTab(i) is NOT NULL -- this record corresponds to EXP record in projects
2280 AND cdl.budget_version_id IS NOT NULL
2281 AND (cdl.acct_event_id IS NULL OR -- events which are not processed by SLA
2282 EXISTS (SELECT 1
2283 FROM xla_events xev
2284 WHERE xev.event_id = cdl.acct_event_id
2285 AND xev.application_id = 275
2286 AND xev.process_status_code <> 'P' )
2287 );
2288
2289 IF P_DEBUG_MODE = 'Y' THEN
2290 pa_fck_util.debug_msg( 'Number of pa_cost_distribution_lines_all updated'||SQL%ROWCOUNT);
2291 pa_fck_util.debug_msg( 'Updating budget_version_id and budget_line_id on pa_bc_commitments_all ');
2292 END IF;
2293
2294 FORALL i in l_DocHdrTab.first..l_DocHdrTab.last
2295 UPDATE pa_bc_commitments_all bccom
2296 SET bccom.budget_version_id = NVL(l_bvidTab(i), bccom.budget_version_id),
2297 bccom.budget_line_id = NVL(l_blidTab(i),bccom.budget_line_id)
2298 WHERE bccom.bc_commitment_id = l_bccomidTab(i);
2299
2300 IF P_DEBUG_MODE = 'Y' THEN
2301 pa_fck_util.debug_msg( 'Number of pa_bc_commitments updated'||SQL%ROWCOUNT);
2302 END IF;
2303
2304 END LOOP;
2305 CLOSE c_bccom_packets;
2306
2307 END IF; --IF p_calling_mode = 'YEAR END ROLLOVER' THEN
2308
2309 IF P_DEBUG_MODE = 'Y' THEN
2310 pa_fck_util.debug_msg('Update_bvid_blid_on_cdl_bccom: End' );
2311 END IF;
2312
2313 Exception
2314 When OTHERS then
2315 IF P_DEBUG_MODE = 'Y' THEN
2316 pa_fck_util.debug_msg('EXCEPTION: '||SQLERRM);
2317 END IF;
2318 RAISE;
2319 END Update_bvid_blid_on_cdl_bccom;
2320
2321 --=======================================================================================+
2322 -- #Bug 5191768
2323 -- #API name : Get_cost_rejection_reason
2324 -- #Type : private
2325 -- #Description : Fundscheck rejection reasons are fetched from
2326 -- a. gms_lookups for Grants related transactions
2327 -- b. pa_lookups for Project related transactions
2328 -- Transaction rejection reason/cost distribution rejection reason are
2329 -- derived from pa_lookups
2330 --=======================================================================================+
2331
2332 FUNCTION Get_cost_rejection_reason ( p_Lookup_code IN VARCHAR2,
2333 p_sponsored_flag IN VARCHAR2)
2334 return VARCHAR2 IS
2335
2336 l_meaning pa_lookups.meaning%TYPE;
2337
2338 CURSOR c_pa_lookup_meaning IS
2339 SELECT LOOKUP.Meaning
2340 FROM PA_Lookups LOOKUP
2341 WHERE LOOKUP.Lookup_Type IN ('IND COST DIST REJECTION CODE','COST DIST REJECTION CODE', 'FC_RESULT_CODE', 'TRANSACTION REJECTION REASON')
2342 AND LOOKUP.Lookup_Code = p_Lookup_code;
2343
2344 CURSOR c_gms_lookup_meaning IS
2345 SELECT GMSLKUP.Meaning
2346 FROM GMS_Lookups GMSLKUP
2347 WHERE GMSLKUP.Lookup_Type = 'FC_RESULT_CODE'
2348 AND GMSLKUP.Lookup_Code = p_Lookup_code;
2349
2350 BEGIN
2351
2352 IF p_sponsored_flag = 'Y' THEN
2353
2354 OPEN c_gms_lookup_meaning ;
2355 FETCH c_gms_lookup_meaning INTO l_meaning;
2356 CLOSE c_gms_lookup_meaning ;
2357
2358 END IF;
2359
2360 IF l_meaning IS NULL THEN
2361
2362 OPEN c_pa_lookup_meaning ;
2363 FETCH c_pa_lookup_meaning INTO l_meaning;
2364 CLOSE c_pa_lookup_meaning ;
2365
2366 END IF;
2367
2368 RETURN l_meaning;
2369
2370 END Get_cost_rejection_reason;
2371
2372 -- #R12 Funds management enhancement
2373 -- #API name : get_ap_acct_reversal_attr
2374 -- #Type : private
2375 -- #Description : Returns parent distribution id if its a AP cancel scenario .
2376 -- SLA accounting reversal logic will be fired if this api returns NOT NULL
2377
2378 FUNCTION get_ap_acct_reversal_attr ( p_event_type_code IN VARCHAR2,
2379 p_document_distribution_id IN NUMBER ,
2380 p_document_distribution_type IN VARCHAR2 ) RETURN NUMBER IS
2381
2382 CURSOR C_get_inv_parent_id IS
2383 SELECT AID.Parent_Reversal_id
2384 FROM ap_invoice_distributions_all aid
2385 WHERE aid.invoice_distribution_id = p_document_distribution_id
2386 AND decode(nvl(AID.cancellation_flag,'N'), 'Y', decode(AID.Parent_Reversal_id, null,'N','Y'),'N') = 'Y';
2387
2388 CURSOR C_get_prepay_parent_id IS
2389 SELECT APAD.REVERSED_PREPAY_APP_DIST_ID
2390 FROM AP_PREPAY_APP_DISTS APAD,
2391 AP_PREPAY_HISTORY_ALL APPH,
2392 AP_INVOICES_ALL AI
2393 WHERE APAD.Prepay_App_Distribution_ID = p_document_distribution_id
2394 AND APPH.prepay_history_id = APAD.prepay_history_id
2395 AND AI.invoice_id = APPH.invoice_id
2396 AND decode(p_event_type_code, 'PREPAYMENT UNAPPLIED', decode(nvl(AI.historical_flag,'N'), 'Y','N',
2397 decode(APAD.REVERSED_PREPAY_APP_DIST_ID, null, 'N','Y') ),'N') = 'Y' ;
2398
2399 BEGIN
2400
2401 -- Check if cached
2402 IF NVL(g_event_type_code,-1) = NVL(p_event_type_code ,-1) AND
2403 NVL(g_document_distribution_id,-1) = NVL(p_document_distribution_id,-1) AND
2404 NVL(g_document_distribution_type,-1) = NVL(p_document_distribution_type,-1) THEN
2405
2406 RETURN g_parent_distribution_id;
2407
2408 ELSE
2409
2410 g_event_type_code := p_event_type_code;
2411 g_document_distribution_id := p_document_distribution_id;
2412 g_document_distribution_type := p_document_distribution_type;
2413
2414 -- SUBSTR is used to cover other types of prepayment application dists
2415 IF SUBSTR(p_document_distribution_type,1,11) ='PREPAY APPL' THEN
2416
2417 OPEN C_get_prepay_parent_id;
2418 FETCH C_get_prepay_parent_id INTO g_parent_distribution_id;
2419 CLOSE C_get_prepay_parent_id;
2420
2421 ELSE
2422
2423 OPEN C_get_inv_parent_id;
2424 FETCH C_get_inv_parent_id INTO g_parent_distribution_id;
2425 CLOSE C_get_inv_parent_id;
2426
2427 END IF;
2428
2429 RETURN g_parent_distribution_id;
2430
2431 END IF;
2432
2433 END get_ap_acct_reversal_attr;
2434
2435 -----------------------------------------------------------------------------------
2436 -- #R12 Funds management enhancement
2437 -- #API name : get_ap_sla_reversed_status
2438 -- #Type : private
2439 -- #Description : Returns 'Y' if AP is cancelled and the SLA lines associated with
2440 -- AP has been reversed .Business flow cannot be used in this scenario.
2441 -- This function uses same logic as that of ap extract which indentifies
2442 -- scenarios where line level reversals are used.
2443 -------------------------------------------------------------------------------------
2444 FUNCTION get_ap_sla_reversed_status (p_invoice_id IN NUMBER,
2445 p_invoice_distribution_id IN NUMBER ) RETURN VARCHAR2 IS
2446
2447 CURSOR C_check_reversing_dist IS
2448 SELECT nvl(AID.cancellation_flag,'N')
2449 FROM ap_invoice_distributions_all aid
2450 WHERE aid.invoice_distribution_id = p_invoice_distribution_id
2451 AND decode(nvl(AID.cancellation_flag,'N'), 'Y', decode(AID.Parent_Reversal_id, null,'N','Y'),'N') = 'Y';
2452
2453 CURSOR C_check_main_cancelled_dist IS
2454 SELECT 'Y'
2455 FROM dual
2456 WHERE exists ( select 1
2457 from ap_invoice_distributions_all aid
2458 where aid.invoice_id = p_invoice_id
2459 and aid.Parent_Reversal_id = p_invoice_distribution_id);
2460
2461 l_ap_sla_reversed_status VARCHAR2(1);
2462
2463 BEGIN
2464
2465 l_ap_sla_reversed_status := 'N';
2466
2467 OPEN C_check_main_cancelled_dist;
2468 FETCH C_check_main_cancelled_dist INTO l_ap_sla_reversed_status;
2469 CLOSE C_check_main_cancelled_dist;
2470
2471 IF NVL(l_ap_sla_reversed_status,'N') = 'N' THEN
2472
2473 OPEN C_check_reversing_dist;
2474 FETCH C_check_reversing_dist INTO l_ap_sla_reversed_status;
2475 CLOSE C_check_reversing_dist;
2476
2477 END IF;
2478
2479 l_ap_sla_reversed_status := NVL(l_ap_sla_reversed_status,'N');
2480
2481 RETURN l_ap_sla_reversed_status;
2482
2483 END get_ap_sla_reversed_status;
2484 -----------------------------------------------------------------------------------
2485 -- R12 Funds Management Uptake
2486 -- Procedure to derive credit/debit side of the amount for PO and REQ distributions
2487 -- This has logic in synch with R12 PO and REQ JLT's
2488 -- Note: PO is maintaining similar logic in PO_ENCUMBRANCE_POSTPROCESSING.get_sign_for_amount
2489 -- which needs to centralized by PSA .Will be logging bug against PSA and once fixed we can
2490 -- directly call psa package.
2491 -- For now since PO's logic is complicated ,PA will maintain below function .
2492 -------------------------------------------------------------------------------------
2493
2494 FUNCTION DERIVE_PO_REQ_AMT_SIDE (p_event_type_code IN VARCHAR2,
2495 p_main_or_backing_doc IN VARCHAR2,
2496 p_distribution_type IN VARCHAR2 ) RETURN NUMBER IS
2497
2498 l_cr_dr_side NUMBER := 0; -- If -1 then CR elsif +1 then DR
2499
2500 BEGIN
2501
2502 IF (p_event_type_code IN ( 'PO_PA_RESERVED' ,
2503 'PO_PA_CR_MEMO_CANCELLED',
2504 'RELEASE_REOPEN_FINAL_CLOSED',
2505 'RELEASE_CR_MEMO_CANCELLED',
2506 'RELEASE_RESERVED',
2507 'REQ_RESERVED',
2508 'PO_REOPEN_FINAL_MATCH',
2509 -- g_tab_entered_amount and g_tab_accted_amount will be negative for below events
2510 'PO_PA_CANCELLED',
2511 'RELEASE_CANCELLED',
2512 'REQ_CANCELLED'
2513 ) OR
2514 (p_event_type_code ='REQ_ADJUSTED' AND p_distribution_type = 'REQUISITION_ADJUSTED_NEW'))
2515 THEN
2516
2517 IF p_main_or_backing_doc = 'M' THEN
2518 l_cr_dr_side := 1;
2519 ELSE
2520 l_cr_dr_side := -1;
2521 END IF;
2522
2523 ELSIF (p_event_type_code IN ('PO_PA_UNRESERVED' ,
2524 'PO_PA_FINAL_CLOSED',
2525 'PO_PA_REJECTED',
2526 'PO_PA_INV_CANCELLED',
2527 'RELEASE_FINAL_CLOSED',
2528 'RELEASE_INV_CANCELLED',
2529 'RELEASE_REJECTED',
2530 'RELEASE_UNRESERVED',
2531 'REQ_FINAL_CLOSED',
2532 'REQ_REJECTED',
2533 'REQ_RETURNED',
2534 'REQ_UNRESERVED') OR
2535 (p_event_type_code ='REQ_ADJUSTED' AND p_distribution_type = 'REQUISITION_ADJUSTED_OLD'))
2536
2537 THEN
2538
2539 IF p_main_or_backing_doc = 'M' THEN
2540 l_cr_dr_side := -1;
2541 ELSE
2542 l_cr_dr_side := 1;
2543 END IF;
2544
2545 END IF;
2546
2547 RETURN l_cr_dr_side;
2548
2549 END DERIVE_PO_REQ_AMT_SIDE;
2550
2551
2552
2553 -- -----------------------------------------------------------------------------------------+
2554
2555 END PA_FUNDS_CONTROL_UTILS ;
[Home] [Help]