1 package Body PA_CC_ENC_IMPORT_FCK as
2 -- $Header: PACCENCB.pls 120.2 2006/03/31 16:51:28 bkattupa noship $
3
4 -- Declare global variables for cache
5 G_debug_mode varchar2(100);
6 G_prev_flag_project_id Number;
7 G_prev_bdgt_project_id Number;
8 G_prev_flag_ext_budget_code varchar2(100);
9 G_prev_bdgt_ext_budget_code varchar2(100);
10 G_prev_budget_version_id Number;
11 G_Prev_budget_control_flag varchar2(100);
12
13 PROCEDURE print_msg(p_msg varchar2) IS
14
15 BEGIN
16 If g_debug_mode = 'Y' Then
17 --r_debug.r_msg('Log:'||p_msg);
18 --dbms_output.put_line('Log:'||p_msg);
19 pa_debug.g_err_stage := substr(p_msg,1,250);
20 pa_debug.write_file('LOG: '||pa_debug.g_err_stage);
21 End If;
22 null;
23
24 END print_msg;
25
26 /** This API checks whether the budgetary control is enabled or Not for the given project and budget type
27 * The return status of this API will be 'Y' or 'N' */
28 FUNCTION get_fc_reqd_flag(p_project_id number,p_ext_budget_code varchar2) RETURN varchar2 IS
29
30 l_budget_control_flag varchar2(100);
31
32 BEGIN
33
34 If (g_prev_flag_project_id is Null OR g_prev_flag_project_id <> p_project_id ) OR
35 (g_prev_flag_ext_budget_code is Null OR g_prev_flag_ext_budget_code <> p_ext_budget_code ) Then
36
37 print_msg('Inside get_fc_reqd_flag executing sql');
38
39 /* Modified the sql into exist clause to improve performance */
40 select 'Y'
41 into l_budget_control_flag
42 FROM DUAL
43 WHERE EXISTS ( select null
44 from pa_budgetary_control_options pbct
45 ,pa_budget_types bv
46 where pbct.project_id = p_project_id
47 AND pbct.BDGT_CNTRL_FLAG = 'Y'
48 AND pbct.BUDGET_TYPE_CODE = bv.budget_type_code
49 AND Nvl(pbct.EXTERNAL_BUDGET_CODE,'GL') = p_ext_budget_code
50 AND bv.budget_amount_code = 'C'
51 );
52
53 g_prev_flag_project_id := p_project_id;
54 g_prev_flag_ext_budget_code := p_ext_budget_code;
55 g_Prev_budget_control_flag := l_budget_control_flag;
56
57 Else
58 -- Retreive from the cache
59 print_msg('Inside get_fc_reqd_flag retreiveing from cache');
60 l_budget_control_flag := g_Prev_budget_control_flag;
61
62 End If;
63
64 RETURN l_budget_control_flag;
65
66
67 EXCEPTION
68 WHEN NO_DATA_FOUND THEN
69 l_budget_control_flag := 'N';
70 g_prev_flag_project_id := p_project_id;
71 g_prev_flag_ext_budget_code := p_ext_budget_code;
72 g_Prev_budget_control_flag := l_budget_control_flag;
73 RETURN l_budget_control_flag;
74 WHEN TOO_MANY_ROWS THEN
75 l_budget_control_flag := 'Y';
76 g_prev_flag_project_id := p_project_id;
77 g_prev_flag_ext_budget_code := p_ext_budget_code;
78 g_Prev_budget_control_flag := l_budget_control_flag;
82
79 RETURN l_budget_control_flag;
80 WHEN OTHERS THEN
81 Raise;
83 END get_fc_reqd_flag;
84
85 /** This API returns budget version id for the given project and external budget type */
86 FUNCTION get_bdgt_version_id(p_project_id number,p_ext_budget_code varchar2) RETURN NUMBER IS
87
88 l_budget_version_id Number;
89 BEGIN
90
91 If (g_prev_bdgt_project_id is Null OR g_prev_bdgt_project_id <> p_project_id ) OR
92 (g_prev_bdgt_ext_budget_code is Null OR g_prev_bdgt_ext_budget_code <> p_ext_budget_code ) Then
93 print_msg('Inside get_bdgt_version_id executing sql');
94
95 /* Added budget_amount_code = 'C' to select cost budget only */
96
97 SELECT max(pbv.budget_version_id)
98 INTO l_budget_version_id
99 FROM pa_budget_versions pbv
100 ,pa_budget_types bdgttype
101 ,pa_budgetary_control_options pbct
102 WHERE pbv.project_id = p_project_id
103 AND pbv.current_flag = 'Y'
104 AND pbv.budget_status_code = 'B'
105 AND bdgttype.budget_type_code = pbv.budget_type_code
106 AND bdgttype.budget_amount_code = 'C'
107 AND pbct.project_id = pbv.project_id
108 AND pbct.BDGT_CNTRL_FLAG = 'Y'
109 AND pbct.BUDGET_TYPE_CODE = pbv.budget_type_code
110 AND NVL(pbct.EXTERNAL_BUDGET_CODE,'GL') = p_ext_budget_code ;
111
112 g_prev_bdgt_project_id := p_project_id;
113 g_prev_bdgt_ext_budget_code := p_ext_budget_code;
114 g_prev_budget_version_id := l_budget_version_id;
115
116 Else
117 -- Retreive from the cache
118 print_msg('Inside get_bdgt_version_id retreiveing from cache');
119 l_budget_version_id := g_prev_budget_version_id;
120
121 End If;
122
123 RETURN l_budget_version_id;
124 EXCEPTION
125 WHEN NO_DATA_FOUND THEN
126 l_budget_version_id := Null;
127 g_prev_bdgt_project_id := p_project_id;
128 g_prev_bdgt_ext_budget_code := p_ext_budget_code;
129 g_prev_budget_version_id := l_budget_version_id;
130 RETURN l_budget_version_id;
131 WHEN OTHERS THEN
132 l_budget_version_id := Null;
133 g_prev_bdgt_project_id := p_project_id;
134 g_prev_bdgt_ext_budget_code := p_ext_budget_code;
135 g_prev_budget_version_id := l_budget_version_id;
136 RETURN l_budget_version_id;
137
138 END get_bdgt_version_id;
139 /** This API checks whether the PA is installed in the OU or not to avoid cross charage project
140 * transactions funds check The return status of this API will be 'Y' or 'N'
141 **/
142 FUNCTION IS_PA_INSTALL_IN_OU RETURN VARCHAR2 is
143
144 l_return_var varchar2(10) := 'Y';
145
146 BEGIN
147 SELECT 'Y'
148 INTO l_return_var
149 FROM pa_implementations;
150 Return l_return_var ;
151
152 EXCEPTION
153 when NO_data_found then
154 return 'N';
155 when Too_many_rows then
156 return 'Y';
157 When others then
158 Raise;
159
160 END IS_PA_INSTALL_IN_OU;
161
162 /** This is an autonmous Transaction API, which inserts records into
163 * pa_bc_packets. If the operation is success ,x_return_status will be set to 'S'
164 * else it will be set to 'T' - for fatal error and x_error_msg will return the sqlcode and sqlerrm
165 **/
166 PROCEDURE Load_pkts(
167 p_calling_module IN varchar2 default 'CCTRXIMPORT'
168 ,p_ext_budget_type IN varchar2 default 'GL'
169 , p_packet_id IN number
170 , p_fc_rec_tab IN PA_CC_ENC_IMPORT_FCK.FC_Rec_Table
171 , x_return_status OUT NOCOPY varchar2
172 , x_error_msg OUT NOCOPY varchar2
173 ) IS
174
175 PRAGMA AUTONOMOUS_TRANSACTION;
176 l_fc_rec_tab PA_CC_ENC_IMPORT_FCK.FC_Rec_Table := p_fc_rec_tab;
177 l_tab_count Number := 0;
178 l_ext_budget_code varchar2(100);
179 l_budget_version_id Number;
180 l_budget_control_flag varchar2(100);
181 l_ext_budget_type varchar2(100);
182
183
184 BEGIN
185 --Initialize the out variables
186 x_return_status := 'S';
187 x_error_msg := Null;
188
189 -- Initialize the error stack;
190 pa_debug.init_err_stack('PA_FUNDS_CONTROL_UTILS.Load_pkts');
191
192 --Intialize the debug flag
193 fnd_profile.get('PA_DEBUG_MODE',g_debug_mode);
194 g_debug_mode := NVL(g_debug_mode, 'N');
195 PA_DEBUG.SET_PROCESS( x_process => 'PLSQL'
196 ,x_write_file => 'LOG'
197 ,x_debug_mode => g_debug_mode
198 );
199
200 If IS_PA_INSTALL_IN_OU = 'N' Then
201 -- PA is not installed in this OU
202 print_msg('PA is not installed in this OU');
203 Return;
204
205 End If;
206
207 print_msg('Inside Load_pkts p_packet_id['||p_packet_id||']');
208
209 l_tab_count := l_fc_rec_tab.count();
210
211 print_msg('l_tab_count['||l_tab_count);
212 print_msg('p_tab_count['||p_fc_rec_tab.count);
213
214 IF l_tab_count > 0 Then --{
215
216 FOR i IN 1 .. l_tab_count LOOP
217
218 --derive the budget version id based on the project_id and budget_type
219 --If the project is budgetary control enables and if there is no baselined budget exists
220 --mark these transactions as error 'F166 : No Baseline budget exists for the this project';
221
222 l_ext_budget_type := l_fc_rec_tab(i).EXT_BUDGET_TYPE;
223
227 l_ext_budget_type := 'GL';
224 print_msg('l_ext_budget_type['||l_ext_budget_type);
225
226 If l_ext_budget_type is Null Then
228 End If;
229 l_budget_control_flag := get_fc_reqd_flag(l_fc_rec_tab(i).PROJECT_ID,l_ext_budget_type);
230 l_budget_version_id := get_bdgt_version_id(l_fc_rec_tab(i).PROJECT_ID,l_ext_budget_type);
231
232 print_msg('l_budget_control_flag['||l_budget_control_flag||']l_budget_version_id['||l_budget_version_id);
233
234
235 IF l_budget_control_flag = 'Y' and l_budget_version_id is NULL Then
236
237 l_fc_rec_tab(i).result_code := 'F166';
238 l_fc_rec_tab(i).budget_version_id := -1;
239 Elsif l_budget_control_flag = 'Y' and l_budget_version_id is NOT NULL Then
240
241 l_fc_rec_tab(i).budget_version_id := l_budget_version_id;
242
243 Else
244 l_fc_rec_tab(i).budget_version_id := -1;
245 l_fc_rec_tab(i).status_code := 'Z';
246 -- set status code to Z to avoid copying project related non-budgetary control records into pa_bc_packets
247
248 End If;
249
250 -- Note: We cannot use Bulk insert due to table of records
251 --PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
252 INSERT INTO PA_BC_PACKETS
253 (PACKET_ID
254 ,BC_PACKET_ID
255 ,PARENT_BC_PACKET_ID
256 ,BC_COMMITMENT_ID
257 ,PROJECT_ID
258 ,TASK_ID
259 ,EXPENDITURE_TYPE
260 ,EXPENDITURE_ITEM_DATE
261 ,SET_OF_BOOKS_ID
262 ,JE_CATEGORY_NAME
263 ,JE_SOURCE_NAME
264 ,STATUS_CODE
265 ,DOCUMENT_TYPE
266 ,FUNDS_PROCESS_MODE
267 ,EXPENDITURE_ORGANIZATION_ID
268 ,DOCUMENT_HEADER_ID
269 ,DOCUMENT_DISTRIBUTION_ID
270 ,BUDGET_VERSION_ID
271 ,BURDEN_COST_FLAG
272 ,BALANCE_POSTED_FLAG
273 ,ACTUAL_FLAG
274 ,GL_DATE
275 ,PERIOD_NAME
276 ,PERIOD_YEAR
277 ,PERIOD_NUM
278 ,ENCUMBRANCE_TYPE_ID
279 ,PROJ_ENCUMBRANCE_TYPE_ID
280 ,TOP_TASK_ID
281 ,PARENT_RESOURCE_ID
282 ,RESOURCE_LIST_MEMBER_ID
283 ,ENTERED_DR
284 ,ENTERED_CR
285 ,ACCOUNTED_DR
286 ,ACCOUNTED_CR
287 ,RESULT_CODE
288 ,OLD_BUDGET_CCID
289 ,TXN_CCID
290 ,ORG_ID
291 ,LAST_UPDATE_DATE
292 ,LAST_UPDATED_BY
293 ,CREATED_BY
294 ,CREATION_DATE
295 ,LAST_UPDATE_LOGIN
296 ) select
297 l_fc_rec_tab(i).PACKET_ID
298 ,pa_bc_packets_s.nextval --l_fc_rec_tab(i).BC_PACKET_ID
299 ,l_fc_rec_tab(i).PARENT_BC_PACKET_ID
300 ,l_fc_rec_tab(i).BC_COMMITMENT_ID
301 ,l_fc_rec_tab(i).PROJECT_ID
302 ,l_fc_rec_tab(i).TASK_ID
303 ,l_fc_rec_tab(i).EXPENDITURE_TYPE
304 ,l_fc_rec_tab(i).EXPENDITURE_ITEM_DATE
305 ,l_fc_rec_tab(i).SET_OF_BOOKS_ID
306 ,l_fc_rec_tab(i).JE_CATEGORY_NAME
307 ,l_fc_rec_tab(i).JE_SOURCE_NAME
308 ,l_fc_rec_tab(i).STATUS_CODE
309 ,l_fc_rec_tab(i).DOCUMENT_TYPE
310 ,l_fc_rec_tab(i).FUNDS_PROCESS_MODE
311 ,l_fc_rec_tab(i).EXPENDITURE_ORGANIZATION_ID
312 ,l_fc_rec_tab(i).DOCUMENT_HEADER_ID
313 ,l_fc_rec_tab(i).DOCUMENT_DISTRIBUTION_ID
314 ,l_fc_rec_tab(i).BUDGET_VERSION_ID
315 ,l_fc_rec_tab(i).BURDEN_COST_FLAG
316 ,l_fc_rec_tab(i).BALANCE_POSTED_FLAG
317 ,l_fc_rec_tab(i).ACTUAL_FLAG
318 ,l_fc_rec_tab(i).GL_DATE
319 ,l_fc_rec_tab(i).PERIOD_NAME
320 ,l_fc_rec_tab(i).PERIOD_YEAR
321 ,l_fc_rec_tab(i).PERIOD_NUM
322 ,l_fc_rec_tab(i).ENCUMBRANCE_TYPE_ID
323 ,l_fc_rec_tab(i).PROJ_ENCUMBRANCE_TYPE_ID
324 ,l_fc_rec_tab(i).TOP_TASK_ID
325 ,l_fc_rec_tab(i).PARENT_RESOURCE_ID
326 ,l_fc_rec_tab(i).RESOURCE_LIST_MEMBER_ID
327 ,l_fc_rec_tab(i).ENTERED_DR
328 ,l_fc_rec_tab(i).ENTERED_CR
329 ,l_fc_rec_tab(i).ACCOUNTED_DR
330 ,l_fc_rec_tab(i).ACCOUNTED_CR
331 ,l_fc_rec_tab(i).RESULT_CODE
332 ,l_fc_rec_tab(i).OLD_BUDGET_CCID
333 ,l_fc_rec_tab(i).TXN_CCID
334 ,l_fc_rec_tab(i).ORG_ID
335 ,l_fc_rec_tab(i).LAST_UPDATE_DATE
336 ,l_fc_rec_tab(i).LAST_UPDATED_BY
337 ,l_fc_rec_tab(i).CREATED_BY
338 ,l_fc_rec_tab(i).CREATION_DATE
339 ,l_fc_rec_tab(i).LAST_UPDATE_LOGIN
340 FROM DUAL
341 WHERE l_fc_rec_tab(i).status_code <> 'Z' ;
342
343 print_msg('No rec inserted ['||sql%rowcount);
344
345 END LOOP;
346
347 Commit;
348
349 End If; -- end of l_tab_count }
350
351 -- Reset the table count
352 l_tab_count := 0;
353
354 select count(*)
355 into l_tab_count
356 from pa_bc_packets
357 where packet_id = p_packet_id;
358
359 print_msg('Number of rec inserted ['||l_tab_count);
360
361 If l_tab_count > 0 Then
362
363 -- populate burden rows for the above inserted rows
364 -- calling the Populate_burden_cost API in TRXIMPORT api will not insert records into pa_bc_packets
365 -- for document type 'CC_C_CO','CC_P_CO','CC_C_PAY','CC_P_PAY','AP' so the api should be called
366 -- with calling mode manipulated with GL or CBC
367 If p_ext_budget_type = 'CC' Then
368 l_ext_budget_type := 'CBC';
369 Else
370 l_ext_budget_type := 'GL';
371 End If;
372
373 print_msg('calling Populate_burden_cost ');
374 PA_FUNDS_CONTROL_PKG1.Populate_burden_cost
375 (p_packet_id => p_packet_id
376 ,p_calling_module => l_ext_budget_type
377 ,x_return_status => x_return_status
378 ,x_err_msg_code => x_error_msg
379 );
380 print_msg('After calling Populate_burden_cost ');
381
382 End If;
383
387
384 pa_debug.reset_err_stack;
385 COMMIT;
386 Return;
388 EXCEPTION
389 WHEN OTHERS THEN
390 print_msg('When others of exception in Load pkts');
391 update pa_bc_packets
392 set result_code = decode(substr(nvl(result_code,'P'),1,1),'P','F142'
393 ,'F',result_code
394 ,'F142')
395 ,status_code = 'T'
396 where packet_id = p_packet_id;
397 x_return_status := 'T';
398 x_error_msg := SQLCODE||SQLERRM;
399 commit;
400 pa_debug.reset_err_stack;
401 RAISE;
402 END Load_pkts;
403
404 /** This is a wrapper API created on top of pa_funds_chedk for Contract commitments transactions
405 * During import of CC transactions, since the amounts are already encumbered in GL and CC
406 * the respective funds check process will not be called. Ref to bug:2877072 for further details
407 * so the PA encumbrnace entries were missing. In order to fix the above bug this API is created
408 * which calls pa funds check in TRXIMPORT mode so that, the liquidation entries need not be
409 * posted to GL and CBC.
410 * This API will be called twice for each batch of import.
411 * for documnet type - 'CC_C_CO','CC_P_CO' create a unique packet_id and p_ext_budget_type = 'CC'
412 * documnet type - 'CC_C_PAY','CC_P_PAY','AP' create a unique packet_id and p_ext_budget_type = 'GL'
413 * The return status of this API will be 'S' - success, 'F' - Failure, 'T' - Fatal error
414 * NOTE: For Transaction import process , the p_partial_flag is always 'N', if Pa_enc_import_fck is called
415 * in partial mode (Y), then calling program should have the logic to update the result and status code
416 * after the successfull completion of import process.
417 * Note: Since we don't have the origanal transaction reference, we cannot update the partial of
418 * the result code and status of the transactions in partial mode during TRXIMPORT process. so
419 * all the transactions will be marked as failed or passed.
420 **/
421 /** As discussed with Barbara, Dinakar, Sridhar, Prithi :- CC Transaction Import Strategy
422 * 1.If the project is burdened, the burdening setup in legacy system may differ from Projects burdening setup.
423 * So we always assume that, the GL and CC encumbrance import process will import the Burdened Amount.
424 * and going forward PA will derive the burden amounts based on PA burden setup
425 *
426 * 2.When you import CC transactions without calling normal funds check process,
427 * we assume that PA Encumbrance are populated in CC and GL budgets. so we will not post any
428 * liqudiation or burden entries into igc interface or gl_bc_packets
429 *
430 * 3.The CC calls Pa_enc_import_fck API, we assume that CC is putting raw amount into pa_bc_packets
431 * so this API will derive the burden amounts based on setup on the PA burden setup
432 **/
433
434 PROCEDURE Pa_enc_import_fck(
435 p_calling_module IN varchar2 default 'CCTRXIMPORT'
436 , p_ext_budget_type IN varchar2 default 'GL'
437 , p_conc_flag IN varchar2 default 'N'
438 , p_set_of_book_id IN number
439 , p_packet_id IN number
440 , p_mode IN varchar2 default 'R'
441 , p_partial_flag IN varchar2 default 'N'
442 , x_return_status OUT NOCOPY varchar2
443 , x_error_msg OUT NOCOPY varchar2
444 ) IS
445
446 l_fc_return_status varchar2(100);
447 l_fc_error_stage varchar2(100);
448 l_fc_error_msg varchar2(1000);
449
450 l_partial_flag varchar2(100);
451
452 BEGIN
453 --Initialize the out variables
454 x_return_status := 'S';
455 x_error_msg := Null;
456
457 -- Initialize the error stack;
458 pa_debug.init_err_stack('PA_FUNDS_CONTROL_UTILS.Pa_enc_import_fck');
459
460 --Intialize the debug flag
461 fnd_profile.get('PA_DEBUG_MODE',g_debug_mode);
462 g_debug_mode := NVL(g_debug_mode, 'N');
463 PA_DEBUG.SET_PROCESS( x_process => 'PLSQL'
464 ,x_write_file => 'LOG'
465 ,x_debug_mode => g_debug_mode
466 );
467
468 print_msg('Inside Pa_enc_import_fck API');
469
470 If IS_PA_INSTALL_IN_OU = 'N' Then
471 -- PA is not installed in this OU
472 Return;
473
474 End If;
475
476 -- -----------------------------------------------------------------------+
477 -- CC IS DISABLED FOR R12 ....
478 -- -----------------------------------------------------------------------+
479 If p_calling_module = 'CCTRXIMPORT' then
480 x_return_status := 'T';
481 return;
482 End If;
483
484 -- -----------------------------------------------------------------------+
485
486 -- Call the funds check in Reserve mode and calling module = 'TRXIMPORT'
487 -- so that FC process will not pass any entries into GL or CBC tables
488 -- Hard coding the l_partial_flag = 'N'
489
490 l_partial_flag := 'N';
491
492 print_msg('Calling pa_funds_check API');
493 IF Pa_Funds_Control_Pkg.pa_funds_check
494 (p_calling_module => 'TRXIMPORT'
495 ,p_conc_flag => 'N'
496 ,p_set_of_book_id => p_set_of_book_id
497 ,p_packet_id => p_packet_id
498 ,p_mode => p_mode
499 ,p_partial_flag => l_partial_flag
500 ,x_return_status => l_fc_return_status
501 ,x_error_stage => l_fc_error_stage
502 ,x_error_msg => l_fc_error_msg) THEN
503
507
504 print_msg('end of pa_funds_check API retur status '||l_fc_return_status);
505
506 If l_fc_return_status = 'S' Then
508 x_return_status := 'S';
509 x_error_msg := Null;
510 Else
511 x_return_status := 'F';
512 x_error_msg := sqlcode||sqlerrm;
513 End If;
514 End If;
515
516 pa_debug.reset_err_stack;
517 Return;
518
519 EXCEPTION
520 WHEN OTHERS THEN
521 x_return_status := 'T';
522 x_error_msg := SQLCODE||SQLERRM;
523 -- call the status code update api
524 pa_funds_control_pkg.status_code_update_autonomous
525 ( p_calling_module => 'TRXIMPORT'
526 ,p_packet_id => p_packet_id
527 ,p_mode => p_mode
528 ,p_partial => l_partial_flag
529 ,p_packet_status => 'T'
530 ,x_return_status => x_return_status
531 );
532 pa_debug.reset_err_stack;
533 RAISE;
534 END Pa_enc_import_fck;
535
536
537 /** This is tieback API for Contract commitment import process,Once the import process is completed
538 * this api will be called by passing the cbc result code. based on the cbc_result_code the
539 * status of the pa_bc_packets and pa_bdgt_acct_balances will be updated
540 * The return status of this API will be 'S' - success, 'F' - Failure, 'T' - Fatal error
541 **/
542 PROCEDURE Pa_enc_import_fck_tieback(
543 p_calling_module IN varchar2
544 ,p_ext_budget_type IN varchar2 default 'GL'
545 ,p_packet_id IN number
546 ,p_mode IN varchar2 default 'R'
547 ,p_partial_flag IN varchar2 default 'N'
548 ,p_cbc_return_code IN varchar2
549 ,x_return_status OUT NOCOPY varchar2
550 ) IS
551
552 l_calling_module varchar2(100);
553 l_partial_flag varchar2(100);
554
555 BEGIN
556 --Initialize the out variables
557 x_return_status := 'S';
558
559 -- Initialize the error stack;
560 pa_debug.init_err_stack('PA_FUNDS_CONTROL_UTILS.Pa_enc_import_fck_tieback');
561
562 --Intialize the debug flag
563 fnd_profile.get('PA_DEBUG_MODE',g_debug_mode);
564 g_debug_mode := NVL(g_debug_mode, 'N');
565 PA_DEBUG.SET_PROCESS( x_process => 'PLSQL'
566 ,x_write_file => 'LOG'
567 ,x_debug_mode => g_debug_mode
568 );
569
570 If IS_PA_INSTALL_IN_OU = 'N' Then
571 -- PA is not installed in this OU
572 Return;
573
574 End If;
575
576 -- -----------------------------------------------------------------------+
577 -- CC IS DISABLED FOR R12 ....
578 -- -----------------------------------------------------------------------+
579 If p_calling_module = 'CCTRXIMPORT' then
580 x_return_status := 'T';
581 return;
582 End If;
583 -- -----------------------------------------------------------------------+
584
585 If p_calling_module = 'CCTRXIMPORT' and p_ext_budget_type = 'GL' then
586 l_calling_module := 'GL';
587 Elsif p_calling_module = 'CCTRXIMPORT' and p_ext_budget_type = 'CC' Then
588 l_calling_module := 'CC';
589 Else
590 l_calling_module := 'TRXIMPORT';
591 End If;
592
593 -- Note: Since don't have the origanal transaction reference, we cannot update the partial of
594 -- the result code and status of the transactions in partial mode during TRXIMPORT process. so
595 -- all the transactions will be marked as failed or passed.
596 l_partial_flag := 'N';
597
598 print_msg('Calling tie_back_result_code API');
599 PA_CC_ENC_IMPORT_FCK.tie_back_result_code
600 (p_calling_module => l_calling_module
601 ,p_packet_id => p_packet_id
602 ,p_partial_flag => l_partial_flag
603 ,p_mode => p_mode
604 ,p_glcbc_return_code => p_cbc_return_code
605 ,x_return_status => x_return_status
606 );
607 print_msg(' After tie_back_result_code return status ['||x_return_status||']');
608
609 print_msg('Calling status_code_update');
610
611 -- call the status code update api
612 pa_funds_control_pkg.status_code_update
613 ( p_calling_module => 'TRXIMPORT'
614 ,p_packet_id => p_packet_id
615 ,p_mode => p_mode
616 ,p_partial => l_partial_flag
617 ,p_packet_status => p_cbc_return_code
618 ,x_return_status => x_return_status
619 );
620
621 print_msg(' After status_code_update return status ['||x_return_status||']');
622
623 If p_cbc_return_code = 'S' and x_return_status = 'S' Then
624 print_msg('calling upd_bdgt_encum_bal api');
625 -- call the api to update account balances
626 pa_funds_control_pkg.upd_bdgt_encum_bal(
627 p_packet_id => p_packet_id
628 ,p_calling_module => 'TRXIMPORT'
629 ,p_mode => p_mode
630 ,p_packet_status =>p_cbc_return_code
631 ,x_return_status => x_return_status
632 );
633 print_msg(' After upd_bdgt_encum_bal return status ['||x_return_status||']');
634
635 End If;
636
637
641 EXCEPTION
638 pa_debug.reset_err_stack;
639 Return;
640
642 WHEN OTHERS THEN
643 -- call the status code update api
644 pa_funds_control_pkg.status_code_update_autonomous
645 ( p_calling_module => 'TRXIMPORT'
646 ,p_packet_id => p_packet_id
647 ,p_mode => p_mode
648 ,p_partial => l_partial_flag
649 ,p_packet_status => 'T'
650 ,x_return_status => x_return_status
651 );
652 RAISE;
653
654 END Pa_enc_import_fck_tieback;
655
656 /** Update the result code of the transactions based on the partial flag, calling mode and p_mode
657 * in autonomous transaction. After updating the result code call the status_code update API
658 **/
659 PROCEDURE tie_back_result_code
660 (p_calling_module in varchar2,
661 p_packet_id in number,
662 p_partial_flag in varchar2,
663 p_mode in varchar2,
664 p_glcbc_return_code in varchar2,
665 x_return_status OUT NOCOPY varchar2) IS
666
667 PRAGMA AUTONOMOUS_TRANSACTION;
668
669 cursor cur_pkts IS
670 select pkt.rowid
671 ,pkt.bc_packet_id
672 ,pkt.status_code
673 ,pkt.result_code
674 from pa_bc_packets pkt
675 where pkt.packet_id = p_packet_id
676 and substr(nvl(result_code,'P'),1,1) = 'P';
677
678 l_num_rows Number := 500;
679 type rowidtabtyp is table of urowid index by binary_integer;
680 l_tab_rowid rowidtabtyp;
681 l_tab_bc_pkt_id pa_plsql_datatypes.IdTabTyp;
682 l_tab_status_code pa_plsql_datatypes.char50TabTyp;
683 l_tab_result_code pa_plsql_datatypes.char50TabTyp;
684
685 BEGIN
686 If p_calling_module in('GL','CC','TRXIMPORT') and p_mode in ('C','R','A','F') and p_glcbc_return_code <> 'S' then
687
688 OPEN cur_pkts;
689 LOOP
690 -- Intialize the tables
691 l_tab_rowid.delete;
692 l_tab_bc_pkt_id.delete;
693 l_tab_status_code.delete;
694 l_tab_result_code.delete;
695 FETCH cur_pkts BULK COLLECT INTO
696 l_tab_rowid
697 ,l_tab_bc_pkt_id
698 ,l_tab_status_code
699 ,l_tab_result_code
700 LIMIT l_num_rows;
701 IF NOT l_tab_rowid.EXISTS(1) then
702 EXIT;
703 END IF;
704 -- update the result code of the packets where it is passed
705 FORALL i IN l_tab_rowid.FIRST .. l_tab_rowid.LAST
706 UPDATE pa_bc_packets
707 SET result_code =
708 decode(p_calling_module,
709 'GL',
710 decode(p_partial_flag,
711 'Y',decode(p_mode,'C','F150','F156'),
712 'N',decode(p_mode,'C',decode(p_glcbc_return_code,'F','F150',
713 'R','F151',
714 'T','F151')
715 ,'R',decode(p_glcbc_return_code,'F','F155',
716 'R','F155',
717 'T','F155')
718 ,'A',decode(p_glcbc_return_code,'F','F155',
719 'R','F155',
720 'T','F155')
721 ,'F',decode(p_glcbc_return_code,'F','F155',
722 'R','F155',
723 'T','F155'))),
724 'CC',
725 decode(p_partial_flag,
726 'Y',decode(p_mode,'C','F152','F158'),
727 'N',decode(p_mode,'C',decode(p_glcbc_return_code,'F','F152',
728 'R','F153',
729 'T','F153')
730 ,'R',decode(p_glcbc_return_code,'F','F157',
731 'R','F157',
732 'T','F157')
733 ,'A',decode(p_glcbc_return_code,'F','F157',
734 'R','F157',
735 'T','F157')
736 ,'F',decode(p_glcbc_return_code,'F','F157',
737 'R','F157',
738 'T','F157'))),
739 'TRXIMPORT',
740 decode(p_partial_flag,
744 AND bc_packet_id = l_tab_bc_pkt_id(i)
741 'Y',decode(substr(nvl(result_code,'P'),1,1),'P',result_code,'F167'),
742 'N','F167' ))
743 WHERE packet_id = p_packet_id
745 AND substr(nvl(result_code,'P'),1,1) = 'P'
746 AND nvl(p_glcbc_return_code,'R') <> 'S';
747
748 EXIT WHEN cur_pkts%notfound;
749
750 END LOOP;
751 CLOSE cur_pkts;
752 End if;
753 commit; -- to end an active autonomous transaction
754 return;
755 EXCEPTION
756 WHEN OTHERS THEN
757 x_return_status := 'T';
758 print_msg('Failed in tie_back_status apiSQLERR:'||sqlcode||sqlerrm);
759 COMMIT;
760 RAISE;
761
762 END tie_back_result_code;
763
764 END PA_CC_ENC_IMPORT_FCK;
[Home] [Help]