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