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