1 package body PA_FUNDS_CONTROL_UTILS2 as
2 -- $Header: PAFUTL2B.pls 120.2 2006/07/14 06:14:44 anuagraw noship $
3
4 PROCEDURE PRINT_MSG (p_debug_flag varchar2 default 'N'
5 ,p_msg varchar2) IS
6
7 BEGIN
8 If p_debug_flag = 'Y' Then
9 --r_debug.r_msg('Log:'||p_msg);
10 pa_debug.write_file('LOG',p_msg);
11 End If;
12
13 END PRINT_MSG;
14
15 /* This API returs the Y if the given PO line is contingent worker PO */
16 FUNCTION is_CWK_PO(p_po_header_id Number
17 ,p_po_line_id Number
18 ,p_po_dist_id Number
19 ,p_proj_org_id Number ) Return varchar2 IS
20
21 l_return_CWK_flag varchar2(1) := 'N';
22 l_check_cwk_implemented Varchar2(1) := 'N';
23
24 Cursor check_cwk_implemented IS
25 Select nvl(imp.XFACE_CWK_TIMECARDS_FLAG ,'N')
26 from pa_implementations_all imp
27 where imp.org_id = nvl(p_proj_org_id,-99) ; /*5368274*/
28
29 BEGIN
30 If pa_funds_control_utils2.g_cwk_implemented_flag is NULL Then
31 Open check_cwk_implemented;
32 Fetch check_cwk_implemented INTO l_check_cwk_implemented;
33 Close check_cwk_implemented;
34 pa_funds_control_utils2.g_cwk_implemented_flag := l_check_cwk_implemented;
35 Else
36 l_check_cwk_implemented := pa_funds_control_utils2.g_cwk_implemented_flag ;
37 End If;
38
39 If l_check_cwk_implemented = 'Y' Then
40
41 -- Call PO api to check the particular po line is CWK line
42 l_return_CWK_flag := PA_PJC_CWK_UTILS.is_rate_based_line
43 (P_Po_Line_Id => p_po_line_id
44 ,P_Po_Distribution_Id => p_po_dist_id
45 );
46 Else
47 l_return_CWK_flag := 'N';
48
49 End If;
50 return l_return_CWK_flag;
51 EXCEPTION
52 when no_data_found then
53 return 'N';
54
55 when others then
56 Raise;
57 END is_CWK_PO ;
58
59
60
61 /* This api will return the resource list member id from the summary record
62 * for the given document header and line for contingent worker record
63 */
64 FUNCTION get_CWK_RLMI(p_project_id IN Number
65 ,p_task_id IN Number
66 ,p_budget_version_id IN Number
67 ,p_document_header_id IN Number
68 ,p_document_dist_id IN Number
69 ,p_document_line_id IN Number
70 ,p_document_type IN VARCHAR2
71 ,p_expenditure_type IN VARCHAR2
72 ,p_line_type IN VARCHAR2
73 ,p_calling_module IN VARCHAR2 ) Return Number
74 IS
75 l_cwk_rlmi Number := NULL;
76
77 CURSOR cur_pkt_cwk_rlmi IS
78 SELECT resource_list_member_id
79 FROM pa_bc_packets pkt
80 WHERE pkt.project_id = p_project_id
81 AND pkt.task_id = p_task_id
82 AND pkt.budget_version_id = p_budget_version_id
83 AND ((p_document_type = 'PO'
84 AND pkt.document_header_id = p_document_header_id)
85 OR
86 p_document_type = 'EXP'
87 )
88 AND pkt.document_line_id = p_document_line_id
89 AND NVL(pkt.summary_record_flag,'N') = 'Y'
90 AND pkt.balance_posted_flag = 'N'
91 AND pkt.status_code in ('P','L','A','B','C')
92 AND substr(nvl(pkt.result_code,'P'),1,1) = 'P'
93 AND pkt.document_type = 'PO'
94 AND (( pkt.expenditure_type = p_expenditure_type
95 and p_line_type = 'BURDEN'
96 and EXISTS (select null
97 from pa_projects_all pp
98 ,pa_project_types_all pt
99 where pt.project_type = pp.project_type
100 and pt.org_id = pp.org_id /*5368274*/
101 and pp.project_id = pkt.project_id
102 and NVL(pt.burden_amt_display_method,'N') = 'D'
103 ))
104 OR
105 (p_line_type = 'RAW'
106 and pkt.parent_bc_packet_id is NULL
107 and EXISTS (select null
108 from pa_projects_all pp
109 ,pa_project_types_all pt
110 where pt.project_type = pp.project_type
111 and pt.org_id = pp.org_id /*5368274*/
112 and pp.project_id = pkt.project_id
113 and NVL(pt.burden_amt_display_method,'N') = 'D'
114 ))
115 OR
116 (EXISTS (select null
117 from pa_projects_all pp
118 ,pa_project_types_all pt
119 where pt.project_type = pp.project_type
120 and pt.org_id = pp.org_id /*5368274*/
121 and pp.project_id = pkt.project_id
122 and NVL(pt.burden_amt_display_method,'N') <> 'D'
123 )
124 )
125 ) ;
126
127 CURSOR cur_com_cwk_rlmi IS
128 SELECT resource_list_member_id
129 FROM pa_bc_commitments_all com
130 WHERE com.project_id = p_project_id
131 AND com.task_id = p_task_id
132 AND com.budget_version_id = p_budget_version_id
133 AND ((p_document_type = 'PO'
134 AND com.document_header_id = p_document_header_id)
135 OR
136 p_document_type = 'EXP'
137 )
138 AND com.document_line_id = p_document_line_id
139 AND com.document_type = 'PO'
140 AND NVL(com.summary_record_flag,'N') = 'Y'
141 AND (( com.expenditure_type = p_expenditure_type
142 and p_line_type = 'BURDEN'
143 and EXISTS (select null
144 from pa_projects_all pp
145 ,pa_project_types_all pt
146 where pt.project_type = pp.project_type
147 and pt.org_id = pp.org_id /*5368274*/
148 and pp.project_id = com.project_id
149 and NVL(pt.burden_amt_display_method,'N') = 'D'
150 ))
151 OR
152 (p_line_type = 'RAW'
153 and com.parent_bc_packet_id is NULL
154 and EXISTS (select null
155 from pa_projects_all pp
156 ,pa_project_types_all pt
157 where pt.project_type = pp.project_type
158 and pt.org_id = pp.org_id /*5368274*/
159 and pp.project_id = com.project_id
160 and NVL(pt.burden_amt_display_method,'N') = 'D'
161 ))
162 OR
163 (EXISTS (select null
164 from pa_projects_all pp
165 ,pa_project_types_all pt
166 where pt.project_type = pp.project_type
167 and pt.org_id = pp.org_id /*5368274*/
168 and pp.project_id = com.project_id
169 and NVL(pt.burden_amt_display_method,'N') <> 'D'
170 )
171 )
172 ) ;
173
174 l_debug_stage varchar2(1000);
175 l_debug_mode varchar2(100) := 'N';
176
177 BEGIN
178 --Initialize the error stack
179 PA_DEBUG.set_curr_function('PA_FUNDS_CONTROL_UTILS2.get_CWK_RLMI');
180
181 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
182 l_debug_mode := NVL(l_debug_mode, 'N');
183
184 PA_DEBUG.SET_PROCESS( x_process => 'PLSQL'
185 ,x_write_file => 'LOG'
186 ,x_debug_mode => l_debug_mode
187 );
188
189 l_cwk_rlmi := NULL;
190
191 IF p_calling_module = 'FUNDS_CHECK' THEN
192 OPEN cur_com_cwk_rlmi ;
193 FETCH cur_com_cwk_rlmi INTO l_cwk_rlmi;
194 IF cur_com_cwk_rlmi%NOTFOUND THEN
195 l_debug_stage := 'Summary Record Not Found in Commitments';
196 print_msg(l_debug_mode,l_debug_stage);
197 OPEN cur_pkt_cwk_rlmi;
198 FETCH cur_pkt_cwk_rlmi INTO l_cwk_rlmi;
199 If cur_pkt_cwk_rlmi%NOTFOUND THEN
200 l_debug_stage := 'Summary Record Not Found in packets';
201 print_msg(l_debug_mode,l_debug_stage);
202 End If;
203 CLOSE cur_pkt_cwk_rlmi;
204 END IF;
205 CLOSE cur_com_cwk_rlmi;
206
207 END IF;
208
209 pa_debug.reset_curr_function; /* 4129612 */
210 Return l_cwk_rlmi;
211
212 EXCEPTION
213 WHEN OTHERS THEN
214 print_msg(l_debug_mode,l_debug_stage);
215 pa_debug.reset_err_stack;
216 RAISE;
217 END get_CWK_RLMI;
218
219 /* This Function returns the Baseline Budget version OR Draft budget version
220 * based on the calling mode 'DRAFT'/ 'BASELINE'
221 */
222 FUNCTION get_draftORbaseLine_bdgtver(p_project_id IN Number
223 ,p_ext_bdgt_type IN varchar2
224 ,p_mode IN varchar2 ) RETURN Number
225 IS
226
227 l_bdgt_version_id Number;
228 BEGIN
229 IF p_mode = 'DRAFT' Then
230 select max(pbv.budget_version_id)
231 INTO l_bdgt_version_id
232 FROM pa_budget_versions pbv
233 ,pa_budget_types bdgttype
234 ,pa_budgetary_control_options pbct
235 WHERE pbv.project_id = p_project_id
236 AND pbv.current_flag = 'N'
237 AND pbv.budget_status_code = 'W'
238 AND bdgttype.budget_type_code = pbv.budget_type_code
239 AND bdgttype.budget_amount_code = 'C'
240 AND pbct.project_id = pbv.project_id
241 AND pbct.BDGT_CNTRL_FLAG = 'Y'
242 AND pbct.BUDGET_TYPE_CODE = pbv.budget_type_code
243 AND NVL(pbct.EXTERNAL_BUDGET_CODE,'GL') = p_ext_bdgt_type ;
244
245 ELSE
246 select max(pbv.budget_version_id)
247 INTO l_bdgt_version_id
248 FROM pa_budget_versions pbv
249 ,pa_budget_types bdgttype
250 ,pa_budgetary_control_options pbct
251 WHERE pbv.project_id = p_project_id
252 AND pbv.current_flag = 'Y'
253 AND pbv.budget_status_code = 'B'
254 AND bdgttype.budget_type_code = pbv.budget_type_code
255 AND bdgttype.budget_amount_code = 'C'
256 AND pbct.project_id = pbv.project_id
257 AND pbct.BDGT_CNTRL_FLAG = 'Y'
258 AND pbct.BUDGET_TYPE_CODE = pbv.budget_type_code
259 AND NVL(pbct.EXTERNAL_BUDGET_CODE,'GL') = p_ext_bdgt_type ;
260
261 END IF;
262
263 RETURN l_bdgt_version_id;
264
265 EXCEPTION
266 when others then
267 raise;
268
269 END get_draftORbaseLine_bdgtver;
270
271
272 /* This API will checks the burden components for CWK transactions has been changed, if so return error
273 * if the burden componenets are not same as the summary record , later we cannot
274 * derive the budget ccid and resource list member id.
275 * so mark the trasnaction as Error.
276 * This check should be carried only for the Burden display method is different
277 * Example: PO is entered for exp 'Airfare' which maps to the following cost codes
278 * Cost Base ind_cost_codes
279 * -----------------------------------
280 * Expenses GA
281 * FEE
282 *
283 * When timecard is entered for expenditure type which maps
284 * Labor GA
285 * FEE
286 * Fringe
287 * Overhead
288 * Since we cannot map RLMI for the Fringe and Overhead, later the transactions get rejected
289 * so mark the bc_packet and parent_bc_packet records as rejected with reason
290 * "Burden cost codes not mapping to summary record expenditure types" - F100
291 *
292 */
293 PROCEDURE checkCWKbdCostCodes( p_calling_module varchar2
294 ,p_project_id Number
295 ,p_task_id Number
296 ,p_doc_line_id Number
297 ,p_exp_type varchar2
298 ,p_exp_item_date date
299 ,x_return_status OUT NOCOPY varchar2
300 ,x_result_code OUT NOCOPY varchar2
301 ,x_status_code OUT NOCOPY varchar2
302 ) IS
303
304 l_error_flag varchar2(1) := 'N';
305 l_exp_string varchar2(1000) := Null;
306 l_cost_base varchar2(100);
307 l_cost_plus_structure varchar2(100);
308 l_error_msg_code varchar2(1000);
309 l_debug_mode varchar2(100) := 'N';
310 l_status_code varchar2(100);
311 l_result_code varchar2(100);
312 l_return_status varchar2(100) := 'S';
313 l_debug_stage varchar2(1000);
314 l_cwkbdCount Number := 0;
315 l_expCostCodeCount Number :=0;
316 l_no_costplus_structure Exception;
317
318 CURSOR cur_comsumRecInfo (p_project_id Number,p_task_id Number,p_doc_line_id Number ) IS
319 SELECT sm.expenditure_type
320 FROM pa_bc_commitments_all sm
321 ,pa_projects_all pp
322 ,pa_project_types_all pt
323 WHERE sm.summary_record_flag = 'Y'
324 AND sm.project_id = p_project_id
325 AND sm.task_id = p_task_id
326 AND sm.document_line_id = p_doc_line_id
327 AND sm.parent_bc_packet_id is not Null
328 AND nvl(sm.comm_tot_bd_amt,0) <> 0
329 AND pp.project_id = sm.project_id
330 AND pt.project_type = pp.project_type
331 AND pt.org_id = pp.org_id /*5368274*/
332 AND NVL(pt.burden_amt_display_method,'N') = 'D';
333
334 CURSOR cur_pktsumRecInfo(p_project_id Number,p_task_id Number,p_doc_line_id Number ) IS
335 SELECT sm.expenditure_type
336 FROM pa_bc_packets sm
337 ,pa_projects_all pp
338 ,pa_project_types_all pt
339 WHERE sm.summary_record_flag = 'Y'
340 AND sm.project_id = p_project_id
341 AND sm.task_id = p_task_id
342 AND sm.document_line_id = p_doc_line_id
343 AND sm.parent_bc_packet_id is not Null
344 AND sm.balance_posted_flag <> 'Y'
345 AND sm.status_code in ('A','C','B','P')
346 AND substr(sm.result_code,1,1) = 'P'
347 AND nvl(sm.comm_tot_bd_amt,0) <> 0
348 AND pp.project_id = sm.project_id
349 AND pt.project_type = pp.project_type
350 AND pt.org_id = pp.org_id /*5368274*/
351 AND NVL(pt.burden_amt_display_method,'N') = 'D';
352
353 BEGIN
354
355 PA_DEBUG.set_curr_function('PA_FUNDS_CONTROL_UTILS2.checkCWKbdCostCodes');
356
357 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
358 l_debug_mode := NVL(l_debug_mode, 'N');
359
360 PA_DEBUG.SET_PROCESS( x_process => 'PLSQL'
361 ,x_write_file => 'LOG'
362 ,x_debug_mode => l_debug_mode
363 );
364
365 -- Initialize the out variables;
366 x_return_status := 'S';
367 x_status_code := 'P';
368 x_result_code := Null;
369
370 l_debug_stage := 'Inside checkCWKbdCostCodes api';
371 print_msg(l_debug_mode,l_debug_stage);
372
373 -- Check the summary record burden components count
374 IF (p_project_id <> NVL(pa_funds_control_utils2.g_bd_cache_proj_id,0) OR
375 p_task_id <> nvl(pa_funds_control_utils2.g_bd_cache_task_id,0) OR
376 p_doc_line_id <> nvl(pa_funds_control_utils2.g_bd_cache_doc_line_id,0) OR
377 p_exp_type <> nvl(pa_funds_control_utils2.g_bd_cache_exp_type,'') OR
378 p_exp_item_date <> nvl(pa_funds_control_utils2.g_bd_cache_Ei_date,'') ) THEN
379 l_debug_stage := 'Excuting cursor to fetch the values';
380 print_msg(l_debug_mode,l_debug_stage);
381 l_exp_string:= null;
382 l_expCostCodeCount := 0;
383 FOR j in cur_comsumRecInfo(p_project_id,p_task_id,p_doc_line_id) LOOP
384 l_exp_string:= l_exp_string||j.expenditure_type||',';
385 l_expCostCodeCount := l_expCostCodeCount + 1;
386 END LOOP;
387 If l_exp_string is NULL Then
388 l_expCostCodeCount := 0;
389 FOR j in cur_pktsumRecInfo(p_project_id,p_task_id,p_doc_line_id) LOOP
390 l_exp_string:= l_exp_string||j.expenditure_type||',';
391 l_expCostCodeCount := l_expCostCodeCount + 1;
392 END LOOP;
393 End If;
394 l_exp_string:= substr(l_exp_string,1,length(l_exp_string)-1);
395 l_debug_stage := 'Summary Record CostCodes['||l_exp_string||']CostCodeCount['||l_expCostCodeCount||']' ;
396 print_msg(l_debug_mode,l_debug_stage);
397 pa_funds_control_utils2.g_bd_cache_exp_string := l_exp_string;
398 pa_funds_control_utils2.g_bd_cache_proj_id := p_project_id;
399 pa_funds_control_utils2.g_bd_cache_task_id := p_task_id;
400 pa_funds_control_utils2.g_bd_cache_doc_line_id := p_doc_line_id;
401 pa_funds_control_utils2.g_bd_cache_exp_type := p_exp_type;
402 pa_funds_control_utils2.g_bd_cache_Ei_date := p_exp_item_date;
403 If l_exp_string is NULL Then
404 l_error_flag := 'N';
405
406 Elsif l_exp_string is NOT NULL Then
407 -- sumary record exists check the burden expenditure types
408 --check the burden cost codes count for the new pkt lines
409 l_debug_stage := 'Calling check_exp_of_cost_base api';
410 print_msg(l_debug_mode,l_debug_stage);
411 pa_funds_control_pkg1.check_exp_of_cost_base
412 (p_task_id => p_task_id
413 ,p_exp_type => p_exp_type
414 ,p_ei_date => p_exp_item_date
415 ,p_sch_type => 'C'
416 ,x_base => l_cost_base
417 ,x_cp_structure => l_cost_plus_structure
418 ,x_return_status => l_return_status
419 ,x_error_msg_code => l_error_msg_code
420 );
421 If l_return_status <> 'S' Then
422 Raise l_no_costplus_structure;
423 End If;
424 BEGIN
425 l_debug_stage := 'Excuting sql to check costcodes has been changed';
426 print_msg(l_debug_mode,l_debug_stage);
427 /*
428 Select 'Y'
429 INTO l_error_flag
430 From Dual
431 Where Exists (Select null
432 from pa_cost_base_cost_codes icc
433 ,pa_cost_base_exp_types exp
434 ,pa_cost_bases base
435 where icc.cost_base = exp.cost_base
436 and icc.cost_plus_structure = exp.cost_plus_structure
437 and base.cost_base = icc.cost_base
438 and icc.ind_cost_code NOT IN (l_exp_string)
439 and exp.expenditure_type = p_exp_type
440 and base.cost_base = l_cost_base
441 and icc.cost_plus_structure = l_cost_plus_structure
442 and icc.cost_base_type = 'INDIRECT COST') ;
443 */
444 Select count(*)
445 INTO l_cwkbdCount
446 from pa_cost_base_cost_codes icc
447 ,pa_cost_base_exp_types exp
448 ,pa_cost_bases base
449 where icc.cost_base = exp.cost_base
450 and icc.cost_plus_structure = exp.cost_plus_structure
451 and base.cost_base = icc.cost_base
452 and icc.ind_cost_code IN (l_exp_string)
453 and exp.expenditure_type = p_exp_type
454 and base.cost_base = l_cost_base
455 and icc.cost_plus_structure = l_cost_plus_structure
456 and icc.cost_base_type = 'INDIRECT COST' ;
457
458 If nvl(l_expCostCodeCount,0) <> 0 Then
459 IF nvl(l_cwkbdCount,0) < nvl(l_expCostCodeCount,0) Then
460 l_error_flag := 'Y';
461 Else
462 l_error_flag := 'N';
463 End If;
464 End If;
465
466 EXCEPTION
467 WHEN NO_DATA_FOUND then
468 l_error_flag := 'N';
469 l_debug_stage := 'End of sql execution';
470 print_msg(l_debug_mode,l_debug_stage);
471 WHEN OTHERS THEN
472 Raise;
473 END;
474
475 End If;
476
477 If l_error_flag = 'Y' Then
478 l_result_code := 'F100';
479 l_status_code := 'R';
480 l_return_status := 'E';
481 Else
482 l_result_code := NULL;
483 l_status_code := 'P';
484 l_return_status := 'S';
485 End If;
486 pa_funds_control_utils2.g_bd_cache_result_code := l_result_code;
487 pa_funds_control_utils2.g_bd_cache_status_code := l_status_code;
488
489 ELSE
490 l_exp_string := pa_funds_control_utils2.g_bd_cache_exp_string;
491 l_status_code := pa_funds_control_utils2.g_bd_cache_status_code;
492 l_result_code := pa_funds_control_utils2.g_bd_cache_result_code;
493
494 END If;
495 l_debug_stage := 'End Of checkCWKbdCostCodes Statuscode['||l_status_code||']Rescode['||l_result_code||']';
496 print_msg(l_debug_mode,l_debug_stage);
497
498 -- set the out variables values
499 x_return_status := l_return_status;
500 x_status_code := l_status_code;
501 x_result_code := l_result_code;
502
503
504 --Reset Error stack;
505 pa_debug.reset_err_stack;
506
507
508 EXCEPTION
509
510 when l_no_costplus_structure Then
511 x_return_status := 'E';
512 x_status_code := 'T';
513 x_result_code := 'F100';
514 pa_debug.reset_err_stack;
515 RAISE;
516
517 when others then
518 x_return_status := 'U';
519 x_status_code := 'T';
520 x_result_code := 'F100';
521 pa_funds_control_utils2.g_bd_cache_status_code := NULL;
522 pa_funds_control_utils2.g_bd_cache_result_code := NULL;
523 pa_funds_control_utils2.g_bd_cache_exp_string := NULL;
524 pa_debug.reset_err_stack;
525 RAISE;
526
527 END checkCWKbdCostCodes;
528
529 end;
[Home] [Help]