[Home] [Help]
PACKAGE BODY: APPS.PSA_BC_XLA_PVT
Source
1 PACKAGE BODY PSA_BC_XLA_PVT AS
2 --$Header: psavbcxb.pls 120.34.12010000.7 2008/11/28 19:46:35 gnrajago ship $
3 ---------------------------------------------------------------------------
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PSA_BC_XLA_PVT';
6
7 ---------------------------------------------------------------------------
8
9 --==========================================================================
10 --Logging Declarations
11 --==========================================================================
12 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
13 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
14 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
15 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
16 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
17 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
18 g_log_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
19 g_path_name CONSTANT VARCHAR2(200) := 'psa.plsql.psavbcxb.psa_bc_xla_pvt';
20 g_log_enabled BOOLEAN := FALSE;
21
22
23 --==========================================================================
24 -- declaring private constants
25 --==========================================================================
26
27 C_YES CONSTANT VARCHAR2(1) := 'Y'; -- yes flag
28 C_NO CONSTANT VARCHAR2(1) := 'N'; -- no flag
29 C_FUNDS_CHECK CONSTANT VARCHAR2(1) := 'C';
30 C_FUNDS_RESERVE CONSTANT VARCHAR2(1) := 'R';
31 C_FUNDS_PARTIAL CONSTANT VARCHAR2(1) := 'P';
32 C_FUNDS_FORCE_PASS CONSTANT VARCHAR2(1) := 'F';
33 C_FUNDS_ADVISORY CONSTANT VARCHAR2(1) := 'A';
34 C_FUNDS_CHK_FULL CONSTANT VARCHAR2(1) := 'M';
35
36 --==========================================================================
37 -- declaring private variables
38 --==========================================================================
39 TYPE psa_acctg_errors_table IS TABLE OF psa_bc_accounting_errors%ROWTYPE;
40 TYPE psa_events_table IS TABLE OF psa_bc_xla_events_gt.event_id%TYPE;
41
42 --==========================================================================
43 -- Forward Declaration of PA/GMS API's
44 --==========================================================================
45 PROCEDURE pa_gms_integration_api;
46 PROCEDURE pa_gms_tieback_api;
47
48 procedure psa_cleanup_gt;
49
50 -------------------------------------------------------------------------------
51 -- PROCEDURE psa_acctg_errors_insert
52 -- Autonomously insert xla accounting erros rows into psa_bc_accounting_errors
53 -- This will allow to see the error records in PSA view results report
54 -- in case a product team issues a rollback.
55 -------------------------------------------------------------------------------
56
57 PROCEDURE psa_acctg_errors_insert(psa_events IN psa_events_table, psa_acctg_errors IN psa_acctg_errors_table) IS
58 PRAGMA AUTONOMOUS_TRANSACTION;
59 l_path_name VARCHAR2(500);
60 BEGIN
61 l_path_name := g_path_name||'.psa_acctg_errors_insert';
62
63 psa_utils.debug_other_string(g_state_level,l_path_name, 'BEGIN of procedure psa_acctg_errors_insert');
64
65 FORALL i in 1..psa_events.COUNT
66 DELETE FROM psa_bc_accounting_errors
67 WHERE event_id = psa_events(i);
68 psa_utils.debug_other_string(g_state_level,l_path_name,'No of rows deleted from psa_bc_accounting_erros: '||SQL%ROWCOUNT);
69
70 FORALL j in 1..psa_acctg_errors.COUNT
71 INSERT INTO psa_bc_accounting_errors VALUES psa_acctg_errors(j);
72 psa_utils.debug_other_string(g_state_level,l_path_name,'No of rows inserted into psa_bc_accounting_erros: '||SQL%ROWCOUNT);
73
74 COMMIT;
75 psa_utils.debug_other_string(g_state_level,l_path_name, 'END of procedure psa_acctg_errors_insert');
76
77 END psa_acctg_errors_insert;
78
79
80 -- /*============================================================================
81 -- API name : Budgetary_Control
82 -- Type : private
83 -- Pre-reqs : Create events in psa_bc_xla_events_gt
84 -- Description : Call SLA engine for BCPSA
85 --
86 -- Parameters :
87 -- IN : p_api_version IN NUMBER Required
88 -- p_init_msg_list IN VARCHAR2 optional Default FND_API.G_FALSE
89 -- p_commit IN VARCHAR2 optional Default FND_API.G_FALSE
90 -- p_application_id IN NUMBER Required
91 -- p_bc_mode IN NUMBER optional Possible values:Check(C ) /Reserve(R )
92 -- p_bc_override_flag VARCHAR2 optional Possible values: Y/N
93 -- p_user_id IN NUMBER optional
94 -- p_user_resp_id IN NUMBER optional
95 --
96 -- OUT : x_return_status OUT VARCHAR2(1)
97 -- x_msg_count OUT NUMBER
98 -- x_msg_data OUT VARCHAR2(2000)
99 -- x_packet_id OUT NUMBER
100 --
101 -- Version : Current Version 1.0
102 -- Initial Version 1.0
103 --
104 --
105 --
106 -- Logic
107 -- - Validate the parameters
108 -- - Get the events to be processed
109 -- - Call the SLA online accounting engine with required parameters
110 -- - Return the Fund status/error
111 --
112 -- Notes:
113 -- Currently calling accounting engine in document mode
114 -- After SLA API for bcpsa is available need to make neccessary changes
115 --
116 -- Modification History
117 -- Date Author Description of Change
118 --
119 -- *===========================================================================*/
120
121 PROCEDURE Budgetary_Control
122 ( p_init_msg_list IN VARCHAR2
123 ,x_return_status OUT NOCOPY VARCHAR2
124 ,x_msg_count OUT NOCOPY NUMBER
125 ,x_msg_data OUT NOCOPY VARCHAR2
126 ,p_application_id IN INTEGER
127 ,p_bc_mode IN VARCHAR2
128 ,p_override_flag IN VARCHAR2
129 ,P_user_id IN NUMBER
130 ,P_user_resp_id IN NUMBER
131 ,x_status_code OUT NOCOPY VARCHAR2
132 ,x_Packet_ID OUT NOCOPY NUMBER
133 )
134
135 IS
136 --
137 -- To verify events exists in gt table
138 --- and identify eligible events to be processed by SLA
139 ---
140 CURSOR C_entity_event_info ( p_application_id NUMBER) IS
141 SELECT XE.entity_id entity_id
142 FROM XLA_ENTITY_EVENTS_V XE,PSA_BC_XLA_EVENTS_GT BCE
143 WHERE XE.event_id = BCE.event_id
144 AND XE.event_status_code <> 'P'
145 AND XE.application_id = p_application_id
146 GROUP BY xe.entity_id;
147
148 -- cursor to set the status code for API
149 -- Currently only GT table is used for getting the results status
150 -- after the SLA ebncahmcement for BCPSA and funds check code completion this
151 -- might change
152
153 CURSOR C_get_status_count IS
154 SELECT nvl(sum(decode(upper(result_code),'FATAL',1)),0) status_fatal_count,
155 sum(decode(upper(result_code),'XLA_ERROR',1)) status_xla_err_count,
156 sum(decode(upper(result_code),'FAIL',1)) status_fail_count,
157 sum(decode(upper(result_code),'PARTIAL',1)) status_partial_count,
158 sum(decode(upper(result_code),'ADVISORY',1)) status_advisory_count,
159 nvl(sum(decode(upper(result_code),'SUCCESS',1)),0) status_success_count,
160 nvl(sum(decode(upper(result_code),'XLA_NO_JOURNAL',1)),0) status_nojournal_count
161 FROM PSA_BC_XLA_EVENTS_GT;
162
163 CURSOR c_xla_errors IS
164 SELECT 'Y'
165 FROM PSA_BC_XLA_EVENTS_GT a
166 WHERE not exists (SELECT 'x'
167 FROM xla_ae_headers b
168 WHERE b.event_id = a.event_id);
169
170 -- Cursor c_get_bc_xla_events_gt is used to print data from psa_bc_xla_Events_gt
171 -- as entered by product teams. This is useful for debugging.
172 CURSOR c_get_bc_xla_events_gt IS
173 SELECT *
174 FROM psa_bc_xla_events_gt;
175
176 CURSOR c_get_psa_events IS
177 SELECT event_id
178 FROM psa_bc_xla_events_gt;
179
180 CURSOR c_get_xla_acctg_err IS
181 SELECT xla_evnt.EVENT_ID,
182 xla_evnt.ENTITY_ID,
183 xla_evnt.APPLICATION_ID,
184 xla_err.AE_HEADER_ID,
185 xla_err.AE_LINE_NUM,
186 xla_evnt.TRANSACTION_DATE,
187 fnd_mesg.MESSAGE_NUMBER,
188 fnd_mesg.MESSAGE_NAME,
189 xla_err.ENCODED_MSG,
190 xla_err.ERROR_SOURCE_CODE,
191 xla_evnt.LEDGER_ID,
192 xla_evnt.LEGAL_ENTITY_ID,
193 xla_evnt.transaction_number DOCUMENT_REFERENCE,
194 NULL BATCH_REFERENCE,
195 to_char(xla_evnt.event_id) LINE_REFERENCE,
196 SYSDATE CREATION_DATE,
197 'Y' XLA_ERROR_FLAG
198 FROM psa_bc_xla_events_gt psa_evnt,
199 xla_events_gt xla_evnt,
200 xla_accounting_errors xla_err,
201 fnd_new_messages fnd_mesg
202 WHERE psa_evnt.event_id = xla_evnt.event_id
203 AND xla_evnt.event_id = xla_err.event_id
204 AND fnd_mesg.application_id = 602
205 AND DECODE(xla_err.message_number, 0, -99, xla_err.message_number) = fnd_mesg.message_number (+)
206 AND userenv('LANG') = fnd_mesg.language_code (+);
207
208
209 l_entity_event_info c_entity_event_info%ROWTYPE;
210 l_status_count C_get_status_count%ROWTYPE;
211 l_event_source_info xla_events_pub_pkg.t_event_source_info;
212 l_entity_id NUMBER;
213 l_accounting_flag VARCHAR2(1);
214 l_accounting_mode VARCHAR2(20);
215 l_transfer_flag VARCHAR2(1);
216 l_gl_posting_flag VARCHAR2(1);
217 l_offline_flag VARCHAR2(1);
218 l_accounting_batch_id NUMBER;
219 l_errbuf VARCHAR2(2000);
220 l_retcode NUMBER;
221 l_request_id NUMBER;
222 l_application_id NUMBER;
223 l_bc_mode VARCHAR2(1);
224 l_partial_reserve_flag VARCHAR2(1);
225 l_override_flag VARCHAR2(1);
226 l_ledger_id NUMBER;
227 l_user_id NUMBER;
228 l_user_resp_id NUMBER;
229 l_path_name VARCHAR2(500);
230 e_event_id_null EXCEPTION;
231 l_accounting_events BOOLEAN;
232 l_run_id NUMBER;
233 l_trx_num NUMBER;
234 l_event_num NUMBER;
235 l_overall_success BOOLEAN;
236 l_count NUMBER;
237 l_index BINARY_INTEGER := 1;
238 l_status psa_bc_xla_events_gt%ROWTYPE;
239 l_xla_error VARCHAR2(1);
240 l_psa_acctg_errors psa_acctg_errors_table;
241 l_psa_events psa_events_table;
242 l_failed_evnt_array PSA_FUNDS_CHECKER_PKG.num_rec;
243
244 BEGIN
245
246 --
247 -- Start of the budgetary control
248 --
249 l_path_name := g_path_name || '.Budgetary_Control';
250 psa_utils.debug_other_string(g_state_level,l_path_name, 'BEGIN of procedure budgetary_control ' );
251
252 --
253 -- Get the parameters values
254 --
255 psa_utils.debug_other_string(g_state_level,l_path_name, 'Application Id = ' ||p_application_id);
256 psa_utils.debug_other_string(g_state_level,l_path_name, 'Budgetary Control Mode = ' ||p_bc_mode);
257 psa_utils.debug_other_string(g_state_level,l_path_name, 'Override Flag = ' ||p_override_flag);
258 psa_utils.debug_other_string(g_state_level,l_path_name, 'User Id = ' ||p_user_id);
259 psa_utils.debug_other_string(g_state_level,l_path_name, 'User Responsibility Id = ' ||p_user_resp_id);
260
261 IF (FND_API.to_boolean(p_init_msg_list)) THEN
262 FND_MSG_PUB.initialize;
263 END IF;
264
265
266 --
267 -- validate the parameters bc_mode and override flag
268 --
269 l_bc_mode := p_bc_mode;
270 l_override_flag := p_override_flag;
271 l_application_id:= p_application_id;
272 l_xla_error := 'N';
273 psa_utils.debug_other_string(g_state_level,l_path_name,'Start of Parameter Validation');
274
275 --
276 -- parameter validations
277 --
278 IF p_application_id IS NULL THEN
279 fnd_message.set_name('PSA','PSA_BC_PARAMETERS_ERROR');
280 fnd_message.set_token('PARAM_NAME','Application Id');
281 fnd_msg_pub.ADD;
282 RAISE Fnd_Api.G_Exc_Error;
283 END IF;
284
285 -- Currently the packetid is set to -1 , will change after SLA enhancement for PSA
286 x_packet_id := -1;
287
288
289 IF (l_bc_mode IS NULL) THEN
290 l_bc_mode :=C_FUNDS_CHECK;
291 ELSE
292 IF (l_bc_mode NOT IN (C_FUNDS_CHECK,C_FUNDS_CHK_FULL,C_FUNDS_RESERVE,C_FUNDS_PARTIAL,C_FUNDS_FORCE_PASS,C_FUNDS_ADVISORY)) THEN
293 Fnd_message.set_name('PSA','PSA_BC_PARAMETERS_ERROR');
294 Fnd_Message.Set_Token('PARAM_NAME','Funds Mode');
295 Fnd_Msg_Pub.ADD;
296 psa_utils.debug_other_msg(p_level => g_error_level,
297 p_full_path => l_path_name,
298 p_remove_from_stack => FALSE);
299 Fnd_file.put_line(fnd_file.log, fnd_message.get);
300 RAISE Fnd_Api.G_Exc_Error;
301 END IF;
302 END IF;
303
304 IF (l_override_flag IS NULL) THEN
305 l_override_flag := C_NO ;
306 ELSE
307 IF (l_override_flag NOT IN (C_YES,C_NO)) THEN
308 Fnd_message.set_name('PSA','PSA_BC_PARAMETERS_ERROR');
309 Fnd_Message.Set_Token('PARAM_NAME','Override Flag');
310 Fnd_Msg_Pub.ADD;
311 psa_utils.debug_other_msg(p_level => g_error_level,
312 p_full_path => l_path_name,
313 p_remove_from_stack => FALSE);
314 Fnd_file.put_line(fnd_file.log, fnd_message.get);
315
316 RAISE Fnd_Api.G_Exc_Error;
317 END IF;
318 END IF;
319
320 psa_utils.debug_other_string(g_state_level,l_path_name,'End of Parameter Validation');
321 --
322 -- Assign the wf parameters to global varibales.
323 -- These variables are used by accounitng engine while calling the funds checker
324 --
325 G_BC_MODE := l_bc_mode;
326 G_OVERRIDE_FLAG := l_override_flag;
327 G_USER_ID := p_user_id;
328 G_USER_RESP_ID := p_user_resp_id;
329 G_APPLICATION_ID := p_application_id;
330 G_PACKET_ID := Null;
331 --
332 -- Assign the parameters required for calling SLA Accounting engine
333 --
334 IF l_bc_mode IN (C_FUNDS_CHECK,C_FUNDS_CHK_FULL) THEN -- check funds draft mode
335 l_accounting_mode := 'FUNDS_CHECK';
336 psa_utils.debug_other_string(g_state_level,l_path_name,'Accounting Mode is FUNDS_CHECK');
337 ELSE
338 l_accounting_mode := 'FUNDS_RESERVE'; -- Reserve Funds in final mode
339 psa_utils.debug_other_string(g_state_level,l_path_name,'Accounting Mode is FUNDS_RESERVE');
340 END IF;
341
342 l_accounting_flag := 'Y'; -- Accounting required
343 l_transfer_flag := 'N'; -- No transfer to GL
344 l_gl_posting_flag := 'N'; -- GL Post not required
345 l_offline_flag := 'N'; -- Calling mode for SLA online engine document mode
346
347 --
348 -- Get the events information for which funds check required
349 -- Get the entity details ,
350 --- For each entity id SLA Accounting engine is invoked
351 --
352 l_accounting_events := FALSE;
353 x_return_status := FND_API.G_RET_STS_SUCCESS;
354 l_overall_success := TRUE;
355
356
357 SELECT count(*) INTO l_count
358 FROM psa_bc_xla_events_gt;
359
360 IF l_count = 0 THEN
361 -- Bug 5474201
362 -- There are no events to be processed, returns success as such
363 -- procedure should handle such situation gracefully
364 IF (l_bc_mode = C_FUNDS_CHECK) THEN
365 x_status_code := 'XLA_NO_JOURNAL';
366 ELSE
367 x_status_code := 'SUCCESS';
368 END IF;
369 return;
370 END IF;
371
372 psa_utils.debug_other_string(g_state_level,l_path_name, 'Number of rows in psa_bc_xla_events_gt table Prior to PA/GMS API ' || l_count );
373
374 ---------------------------------------------------------------
375 -- Calling PA/GMS INTEGRATION API PRIOR TO SLA ONLINE
376 ---------------------------------------------------------------
377
378 psa_utils.debug_other_string(g_state_level,l_path_name, 'Now invoking the PA_GMS_INTEGRATION_API' );
379 pa_gms_integration_api;
380
381 SELECT count(*) INTO l_count
382 FROM psa_bc_xla_events_gt;
383
384 psa_utils.debug_other_string(g_state_level,l_path_name, 'Number of rows in psa_bc_xla_events_gt table after invoking PA/GMS API ' || l_count );
385
386 IF l_count < 1 THEN
387
388 x_status_code := 'FATAL';
389 Fnd_message.set_name('PSA','PSA_BC_XLA_ERROR');
390 Fnd_Message.Set_Token('PARAM_NAME','No Events to be processed');
391 Fnd_Msg_Pub.ADD;
392 Raise FND_API.G_EXC_ERROR;
393
394 END IF;
395
396 --
397 -- Update the PSA_BC_XLA_EVENTS_GT event records to be in 'XLA_UNPROCESSED' status
398 --
399 UPDATE psa_bc_xla_events_gt
400 SET result_code = 'XLA_UNPROCESSED';
401 psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows updated of psa_bc_xla_events_gt: ' || SQL%ROWCOUNT);
402
403
404
405
406
407 ----------------------------------------------------------------------
408 -- clear the XLA_ACCT_PROG_EVENTS_GT table before inserting any rows
409 ----------------------------------------------------------------------
410
411 psa_cleanup_gt;
412
413 /* ---- 7460759 ---------------------------------------------------------------------------
414 DELETE from XLA_ACCT_PROG_EVENTS_GT;
415 psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows deleted from XLA_ACCT_PROG_EVENTS_GT table before insertion: ' || SQL%ROWCOUNT );
416 DELETE from xla_ae_headers_gt;
417 psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows deleted from xla_ae_headers_gt table before insertion: ' || SQL%ROWCOUNT);
418 DELETE from xla_ae_lines_gt;
419 psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows deleted from xla_ae_lines_gt table before insertion: ' || SQL%ROWCOUNT);
420 DELETE from xla_validation_lines_gt;
421 psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows deleted from xla_validation_lines_gt table before insertion: ' || SQL%ROWCOUNT);
422 DELETE from xla_evt_class_orders_gt;
423 psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows deleted from xla_evt_class_orders_gt; table before insertion: ' || SQL%ROWCOUNT);
424 */
425 DELETE from psa_option_details_gt;
426 psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows deleted from psa_option_details_gt table before insertion: ' || SQL%ROWCOUNT);
427 DELETE from psa_bc_alloc_gt;
428 psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows deleted from psa_bc_alloc_gt table before insertion: ' || SQL%ROWCOUNT);
429 ------------------------------------------------------------------------------------- *
430
434 INSERT into XLA_ACCT_PROG_EVENTS_GT (Event_Id)
431 -- Insert rows to XLA Events GT table and Call Accounting Engine
432 -----------------------------------------------------------------
433
435 SELECT event_id FROM psa_bc_xla_events_gt;
436 psa_utils.debug_other_string(g_state_level,l_path_name, 'Number of rows in inserted into XLA_ACCT_PROG_EVENTS_GT table: ' || SQL%ROWCOUNT );
437
438 l_accounting_events := TRUE;
439 l_accounting_batch_id := NULL;
440 l_errbuf := NULL;
441 l_retcode := NULL;
442 l_request_id := NULL;
443
444 psa_utils.debug_other_string(g_state_level,l_path_name, 'PSA_BC_XLA_EVENTS_GT');
445 psa_utils.debug_other_string(g_state_level,l_path_name, '=====================');
446
447 FOR x in c_get_bc_xla_events_gt
448 LOOP
449 psa_utils.debug_other_string(g_state_level,l_path_name, 'EVENT_ID = '||x.event_id);
450 psa_utils.debug_other_string(g_state_level,l_path_name, 'RESULT_CODE = '||x.result_code);
451 END LOOP;
452
453 ----------------------------------------------------------------
454 -- Calling SLA online accounting engine API
455 ----------------------------------------------------------------
456 psa_utils.debug_other_string(g_state_level,l_path_name, 'Calling API xla_accounting_pub_pkg.accounting_program_events');
457 xla_accounting_pub_pkg.accounting_program_events
458 ( p_application_id => P_application_id
459 ,p_accounting_mode => l_accounting_mode
460 ,p_gl_posting_flag => l_gl_posting_flag
461 ,p_accounting_batch_id => l_accounting_batch_id
462 ,p_errbuf => l_errbuf
463 ,p_retcode => l_retcode
464 );
465
466 psa_utils.debug_other_string(g_state_level,l_path_name, 'Return Code = ' || l_retcode);
467 psa_utils.debug_other_string(g_state_level,l_path_name, 'l_errbuf = ' ||l_errbuf );
468 psa_utils.debug_other_string(g_state_level,l_path_name, 'Accounting Batch id = ' ||l_accounting_batch_id );
469
470 -- Get psa_bc_xla_events_gt events
471 OPEN c_get_psa_events;
472 FETCH c_get_psa_events BULK COLLECT INTO l_psa_events;
473 CLOSE c_get_psa_events;
474
475 -- Fetch error records from XLA tables
476 OPEN c_get_xla_acctg_err;
477 FETCH c_get_xla_acctg_err BULK COLLECT INTO l_psa_acctg_errors;
478 CLOSE c_get_xla_acctg_err;
479
480 -- Delete/Save errors records from/into PSA BC accounting errors table
481 psa_acctg_errors_insert(l_psa_events,l_psa_acctg_errors);
482
483 IF l_retcode = 2 THEN
484 psa_utils.debug_other_string(g_state_level,l_path_name, 'ERROR returned in SLA Accounting Engine API');
485 l_overall_success := FALSE;
486 Fnd_message.set_name('PSA','PSA_BC_XLA_ERROR');
487 Fnd_Message.Set_Token('PARAM_NAME',l_errbuf);
488 Fnd_Msg_Pub.ADD;
489 -- update the psa_bc_xla_events_gt event records to
490 -- XLA_ERROR status
491 UPDATE psa_bc_xla_events_gt
492 SET result_code = 'XLA_ERROR';
493 psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows updated of psa_bc_xla_events_gt: ' || SQL%ROWCOUNT);
494 -- set the status code
495 x_status_code := 'XLA_ERROR';
496 ELSE
497 psa_utils.debug_other_string(g_state_level,l_path_name,'Events processed by SLA Accounting Engine');
498
499 --
500 -- Update the PSA_BC_XLA_EVENTS_GT event records to be in 'XLA_NO_JOURNAL' status
501 -- for events that remain in XLA_UNPROCESSED status
502 --
503
504 IF l_retcode = 0 THEN
505 UPDATE psa_bc_xla_events_gt
506 SET result_code = 'XLA_NO_JOURNAL'
507 WHERE result_code = 'XLA_UNPROCESSED';
508 psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows updated of psa_bc_xla_events_gt: ' || SQL%ROWCOUNT);
509 END IF;
510
511 IF l_retcode = 1 THEN
512 OPEN c_xla_errors;
513 FETCH c_xla_errors INTO l_xla_error;
514 CLOSE c_xla_errors;
515
516 IF (l_xla_error = 'Y') THEN
517 x_status_code := 'XLA_ERROR';
518
519 UPDATE psa_bc_xla_events_gt
520 SET result_code = 'XLA_ERROR';
521 psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows updated of psa_bc_xla_events_gt: ' || SQL%ROWCOUNT);
522 END IF;
523 END IF;
524
525 -- Intialize status code to Success
526 x_status_code := 'SUCCESS';
527
528 -- set the status code of the gt table
529 -- Currently only GT table is used for getting the results status
530 -- after the SLA enhancement for BCPSA and funds check code completion this
531 -- might change
532 open C_get_status_count;
533 Fetch C_get_status_count into l_status_count;
534 Close C_get_status_count;
535
536 IF (l_status_count.status_nojournal_count = l_count) THEN
540 ELSIF (l_status_count.status_partial_count > 0 ) THEN
537 x_status_code := 'XLA_NO_JOURNAL';
538 ELSIF (l_status_count.status_fatal_count > 0 ) THEN
539 x_status_code := 'FATAL';
541 x_status_code := 'PARTIAL';
542 ELSIF (l_status_count.status_xla_err_count > 0 ) THEN
543 x_status_code := 'XLA_ERROR';
544 ELSIF (l_status_count.status_fail_count > 0 ) THEN
545 IF (l_bc_mode IN (C_FUNDS_CHECK, C_FUNDS_PARTIAL)) AND (l_status_count.status_success_count > 0) THEN
546 x_status_code := 'PARTIAL';
547 ELSE
548 x_status_code := 'FAIL';
549 END IF;
550 ELSIF (l_status_count.status_advisory_count > 0 ) THEN
551 x_status_code := 'ADVISORY';
552 ELSE
553 x_status_code := 'SUCCESS';
554 END IF; -- advisory
555
556
557 END IF;
558 psa_utils.debug_other_string(g_state_level,l_path_name, 'Status Code= '||x_status_code);
559 ----------------------------------------------------
560 -- packet id will returned from gl_bc_packets
561 -- if more than one event per call of BC API
562 -- first packet id will be returned
563 ------------------------------------------------------
564 x_Packet_ID:= g_packet_id;
565
566 -----------------------------------------------------
567 -- Initialize the collection variables
568 -----------------------------------------------------
569 l_failed_evnt_array := PSA_FUNDS_CHECKER_PKG.num_rec();
570
571 -----------------------------------------------------
572 -- Store event ids and ledger id which are used later
573 -- for roll back if CBC funds check call fails
574 -----------------------------------------------------
575 OPEN c_get_psa_events;
576 FETCH c_get_psa_events BULK COLLECT INTO l_failed_evnt_array;
577 CLOSE c_get_psa_events;
578
579 -----------------------------------------------------
580 -- Check if CBC is enabled and
581 -- call CBC API with reserve mode or funds check mode
582 -- based on value of x_status_code. If call to CBC API
583 -- fails in reserve mode, SBC funds check changes
584 -- will also be rolled back.
585 -----------------------------------------------------
586 IF p_application_id = 201 AND IGI_GEN.is_req_installed('CBC') = TRUE THEN
587 psa_utils.debug_other_string(g_state_level,l_path_name, 'CBC Installed');
588
589 IF (x_status_code IN('SUCCESS','ADVISORY')) THEN
590 x_return_status := IGC_CBC_GL_FC_PKG.glzcbc(p_mode => p_bc_mode, p_conc_proc => FND_API.G_FALSE);
591
592 IF x_return_status <> 1 THEN
593 IF x_return_status = -1 THEN
594 x_status_code := 'XLA_ERROR';
595 ELSIF x_return_status = 0 THEN
596 x_status_code := 'FAIL';
597 END IF;
598 psa_funds_checker_pkg.sync_xla_errors(p_failed_ldgr_array => null, p_failed_evnt_array => l_failed_evnt_array);
599 END IF;
600
601 ELSIF x_status_code IN('FAIL','PARTIAL') THEN
602 x_return_status := IGC_CBC_GL_FC_PKG.glzcbc(p_mode => 'M', p_conc_proc => FND_API.G_FALSE);
603 END IF;
604
605 END IF;
606
607 ------------------------------------------------------
608 -- Calling PA/GMS Tieback API's in case of Errors
609 ------------------------------------------------------
610
611 IF (x_status_code IN('FATAL','XLA_ERROR', 'XLA_NO_JOURNAL')) THEN
612 psa_utils.debug_other_string(g_state_level,l_path_name, 'Calling pa_gms_tieback_api');
613 pa_gms_tieback_api;
614 END IF;
615 psa_utils.debug_other_string(g_state_level,l_path_name, 'END of procedure budgetary_control ' );
616
617 EXCEPTION
618
619 WHEN FND_API.G_EXC_ERROR THEN
620 x_return_status := FND_API.G_RET_STS_ERROR ;
621 FND_MSG_PUB.count_and_get(p_encoded => FND_API.G_FALSE
622 ,p_count => x_msg_count
623 ,p_data => x_msg_data);
624 psa_utils.debug_other_string(g_error_level,l_path_name,'EXCEPTION: '|| SQLERRM(sqlcode));
625 psa_utils.debug_other_string(g_error_level,l_path_name,'Error in budgetary_control Procedure' );
626 pa_gms_tieback_api;
627
628 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
629 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
630 psa_utils.debug_other_string(g_unexp_level,l_path_name, 'Unexpected Error'|| sqlerrm);
631 FND_MSG_PUB.count_and_get(p_encoded => FND_API.G_FALSE
632 ,p_count => x_msg_count
633 ,p_data => x_msg_data);
634
635 psa_utils.debug_other_string(g_unexp_level,l_path_name,'ERROR: Unexpected Error in budgetary_control Procedure' );
636 pa_gms_tieback_api;
637 WHEN OTHERS THEN
638 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
639 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
640 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, 'PSA_BC_XLA_PVT');
641 END IF;
642 psa_utils.debug_unexpected_msg(G_PKG_NAME);
643 FND_MSG_PUB.count_and_get(p_encoded => FND_API.G_FALSE
647 psa_utils.debug_other_string(g_excep_level,l_path_name,'Error in budgetary_control Procedure' );
644 ,p_count => x_msg_count
645 ,p_data => x_msg_data);
646 psa_utils.debug_other_string(g_excep_level,l_path_name,'EXCEPTION: '|| SQLERRM(sqlcode));
648 pa_gms_tieback_api;
649
650 END Budgetary_Control;
651
652
653 -- /*============================================================================
654 -- API name : get_sla_notupgraded_flag
655 -- Type : private
656 -- Pre-reqs : None
657 -- Description : Returns Y/N depending on whether the distribution passed is notupgraded
658 --
659 -- Parameters :
660 -- IN :
661 -- p_application_id IN NUMBER Applied to Application ID
662 -- p_entity_code IN VARCHAR2 Applied to Entity code
663 -- p_source_id_int_1 IN NUMBER Applied to Header ID
664 -- p_dist_link_type IN VARCHAR2 Applied to Dist Link Type
665 -- p_distribution_id IN NUMBER Applied to Distribution ID
666 --
667 -- Returns : VARCHAR2 i.e., Y/N
668 --
669 -- Logic
670 -- - If the transaction was created in transaction tables after R12 upgrade,
671 -- return N
672 -- - Else
673 -- If the distribution was accounted in xla
674 -- return N;
675 -- Else
676 -- return Y;
677 --
678 -- Notes:
679 -- This is called from transaction objects and the return value is
680 -- populated into a column that will be mapped to Upgrade option acct attrib
681 -- in SLA.
682 --
683 -- Modification History
684 -- Date Author Description of Change
685 -- 27-Oct-2005 Venkatesh N Created
686 -- *===========================================================================*/
687
688 FUNCTION get_sla_notupgraded_flag ( p_application_id IN NUMBER,
689 p_entity_code IN VARCHAR2,
690 p_source_id_int_1 IN NUMBER,
691 p_dist_link_type IN VARCHAR2,
692 p_distribution_id IN NUMBER) RETURN VARCHAR2 IS
693 l_dist_creation_date DATE;
694 l_r12_upgrade_date DATE;
695 l_check_variable VARCHAR2(1);
696 l_return_val VARCHAR2(1);
697 l_acctd_cr NUMBER;
698 l_entrd_cr NUMBER;
699
700 l_path_name VARCHAR2(500);
701
702 CURSOR c_check( cp_appl_id NUMBER,
703 cp_entity_code VARCHAR2,
704 cp_source_id_int_1 NUMBER,
705 cp_source_dist_type VARCHAR2,
706 cp_source_dist_id_num_1 NUMBER) IS
707 SELECT '1'
708 FROM xla_transaction_entities xte,
709 xla_ae_headers xah,
710 xla_distribution_links xdl,
711 xla_events xe
712 WHERE xte.application_id = cp_appl_id
713 AND xte.entity_code = cp_entity_code
714 AND xte.source_id_int_1 = cp_source_id_int_1
715 AND xte.entity_id = xah.entity_id
716 AND xah.event_id = xdl.event_id
717 AND xdl.source_distribution_type = cp_source_dist_type
718 AND xdl.source_distribution_id_num_1 = cp_source_dist_id_num_1
719 AND xah.event_id = xe.event_id
720 AND xe.budgetary_control_flag = 'Y';
721
722 --Cursor introduced for Bug 7598349
723 CURSOR c_po_upg_chk(cp_appl_id NUMBER,
724 cp_entity_code VARCHAR2,
725 cp_source_id_int_1 NUMBER,
726 cp_r12_live_date DATE) IS
727 SELECT xal.entered_cr, xal.accounted_cr
728 FROM xla_transaction_entities xte,
729 xla_ae_headers xah,
730 xla_ae_lines xal,
731 xla_events xe
732 WHERE xte.application_id = cp_appl_id
733 AND xte.entity_code = cp_entity_code
734 AND xte.source_id_int_1 = cp_source_id_int_1
735 AND xte.entity_id = xah.entity_id
736 AND xah.event_id = xe.event_id
737 AND xal.ae_header_id = xah.ae_header_id
738 AND xe.budgetary_control_flag = 'Y'
739 AND xal.accounting_class_code = 'PURCHASE_ORDER'
740 AND xal.accounting_date > cp_r12_live_date;
741 BEGIN
742
743 l_path_name := g_path_name || '.get_sla_notupgraded_flag';
744 psa_utils.debug_other_string(g_state_level,l_path_name, 'BEGIN of function get_sla_notupgraded_flag' );
745
746 psa_utils.debug_other_string(g_state_level,l_path_name,'Fetch PSA: R12 Upgrade Date profile value');
747
748 l_r12_upgrade_date :=to_date( Fnd_Profile.Value_Wnps('PSA_R12_UPGRADE_DATE'), 'MM/DD/YYYY HH24:MI:SS'); -- fetch the profile value
749
750 IF l_r12_upgrade_date IS NULL THEN
751 psa_utils.debug_other_string(g_state_level,l_path_name,'Profile value does not contain a value');
755
752 psa_utils.debug_other_string(g_state_level,l_path_name,'Please check whether psar12upg.sql script was run');
753 RAISE Fnd_Api.G_Exc_Error;
754 END IF;
756 psa_utils.debug_other_string(g_state_level,l_path_name,'Profile value = ' || to_char(l_r12_upgrade_date));
757
758 psa_utils.debug_other_string(g_state_level,l_path_name,'Fetch CREATION_DATE of the Distribution '|| p_distribution_id);
759
760 IF p_dist_link_type = 'PO_REQ_DISTRIBUTIONS_ALL' THEN
761 SELECT min(creation_date) INTO l_dist_creation_date
762 FROM po_req_distributions_all
763 WHERE distribution_id = p_distribution_id;
764 ELSIF p_dist_link_type = 'PO_DISTRIBUTIONS_ALL' THEN
765 SELECT min(creation_date) INTO l_dist_creation_date
766 FROM po_distributions_all
767 WHERE po_distribution_id = p_distribution_id;
768 ELSIF p_dist_link_type = 'AP_INV_DIST' THEN
769 SELECT min(creation_date) INTO l_dist_creation_date
770 FROM ap_invoice_distributions_all
771 WHERE invoice_distribution_id = p_distribution_id;
772 ELSE
773 psa_utils.debug_other_string(g_state_level,l_path_name,'Invalid Distribution Link Type'|| p_dist_link_type);
774 RAISE Fnd_Api.G_Exc_Error;
775 END IF;
776
777 psa_utils.debug_other_string(g_state_level,l_path_name,'Distribution CREATION_DATE = ' || to_char(l_dist_creation_date));
778
779 IF (l_dist_creation_date > l_r12_upgrade_date) OR (l_dist_creation_date IS NULL) THEN
780 l_return_val := 'N';
781 ELSIF l_dist_creation_date <= l_r12_upgrade_date THEN
782 OPEN c_check(p_application_id,
783 p_entity_code,
784 p_source_id_int_1,
785 p_dist_link_type,
786 p_distribution_id);
787 FETCH c_check INTO l_check_variable;
788 CLOSE c_check;
789
790 IF l_check_variable ='1' THEN
791 -- Following IF added for Bug 7598349
792 IF (p_dist_link_type = 'PO_DISTRIBUTIONS_ALL' ) THEN
793 OPEN c_po_upg_chk(p_application_id,
794 p_entity_code,
795 p_source_id_int_1,
796 l_r12_upgrade_date);
797 FETCH c_po_upg_chk into l_acctd_cr, l_entrd_cr;
798 CLOSE c_po_upg_chk;
799 IF (l_acctd_cr = 0) AND (l_entrd_cr = 0) then
800 l_return_val := 'Y';
801 ELSE
802 l_return_val := 'N';
803 END IF;
804 ELSE
805 l_return_val := 'N'; --this means data exists in sla and is a R12 entry
806 END IF; -- p_dist_link_type = 'PO_DISTRIBUTIONS_ALL'
807 ELSE
808 IF FV_INSTALL.ENABLED THEN
809 l_return_val := 'O'; --Make use of Upgrade tab in JLD form
810 ELSE
811 l_return_val := 'Y'; --this means data exists in 11i only
812 END IF;
813 END IF;
814 END IF;
815
816 psa_utils.debug_other_string(g_state_level,l_path_name,'Return Value = ' || l_return_val);
817 psa_utils.debug_other_string(g_state_level,l_path_name,'END of function get_sla_notupgraded_flag' || l_path_name);
818 RETURN l_return_val;
819 EXCEPTION
820 WHEN others THEN
821 psa_utils.debug_other_string(g_excep_level,l_path_name,'Error in function get_sla_notupgraded_flag' );
822 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
823 END get_sla_notupgraded_flag;
824
825
826 -- PA_GMS_INTEGRATION_API
827 -- Created By : Tushar Pradhan
828 -- Description: PA/GMS teams provided us with their APIs which we should invoke prior to calling the
829 -- XLA accounting package. These integration API's are invoked using this API.
830 -- Since the requirement demands that XLA accounting should not be invoked if this API fails,
831 -- the same is incorporated.
832 --
833
834 PROCEDURE pa_gms_integration_api IS
835
836 l_path_name VARCHAR2(500);
837 l_partial_resv_flag VARCHAR2(1);
838 l_pa_status VARCHAR2(1);
839 l_pa_enabled INTEGER;
840 l_gms_status VARCHAR2(1);
841 l_gms_enabled INTEGER;
842 l_prepare_stmt VARCHAR2(2000);
843 l_ret_code VARCHAR2(100);
844 l_bc_mode VARCHAR2(1);
845 l_industry fnd_profile_option_values.profile_option_value%TYPE;
846
847 BEGIN
848
849 l_path_name := g_path_name||'.pa_gms_integration_api';
850
851 psa_utils.debug_other_string(g_state_level,l_path_name, 'pa_gms_integration_api invoked');
852 psa_utils.debug_other_string(g_state_level,l_path_name, 'Invoke PA Integration API if PA is enabled');
853
854 IF g_bc_mode ='C' THEN
855 l_partial_resv_flag := 'Y';
856 l_bc_mode := 'C';
857 ELSIF g_bc_mode = 'P' THEN
858 l_partial_resv_flag := 'Y';
859 l_bc_mode := 'R';
860 ELSE
861 l_bc_mode := g_bc_mode;
862 l_partial_resv_flag := 'N';
863 END IF;
864
865 psa_utils.debug_other_string(g_state_level,l_path_name, ' l_partial_resv_flag -> '||l_partial_resv_flag);
866
867 BEGIN
868 l_industry := NULL;
869 l_prepare_stmt := NULL;
870 l_pa_status := 'N';
871
872 IF FND_INSTALLATION.GET(275, 275, l_pa_status, l_industry) THEN
873
874 IF l_pa_status ='I' THEN
875
876 l_pa_enabled := 0;
877 l_prepare_stmt := 'BEGIN IF PA_BUDGET_FUND_PKG.IS_PA_BC_ENABLED() THEN'||' :1 := 1; END IF; END;';
878
879 psa_utils.debug_other_string(g_state_level,l_path_name, ' Statement prepared -> '||l_prepare_stmt);
880
881 EXECUTE IMMEDIATE l_prepare_stmt USING OUT l_pa_enabled;
882
883 psa_utils.debug_other_string(g_state_level,l_path_name, ' l_pa_enabled -> '||l_pa_enabled);
884
885 IF l_pa_enabled = 1 THEN
886
887 l_prepare_stmt := ' BEGIN '||
888 ' PA_FUNDS_CONTROL_PKG1.CREATE_PROJ_ENCUMBRANCE_EVENTS ('||
889 ' :application_id, :partial_resv_flag, :bc_mode, :ret_code); '||
890 ' END; ';
891
892 EXECUTE IMMEDIATE l_prepare_stmt USING IN g_application_id,
893 IN l_partial_resv_flag,
894 IN l_bc_mode,
895 OUT l_ret_code;
896 IF (l_ret_code = 'F') THEN
897 psa_utils.debug_other_string(g_error_level,l_path_name, 'PA Integration API Failed');
898 FND_MESSAGE.SET_NAME('PA', 'PA_BC_FUND_CHK_FAIL');
899 FND_MSG_PUB.ADD;
900 RAISE FND_API.G_EXC_ERROR;
901 ELSE
902 psa_utils.debug_other_string(g_state_level,l_path_name, 'PA Integration API Successful');
903 END IF;
904
905 END IF;
906 END IF;
907 END IF;
908
909 END;
910
911 psa_utils.debug_other_string(g_state_level,l_path_name, 'Invoke GMS Integration API if GMS is enabled');
912 l_ret_code := NULL;
913
914 BEGIN
915 l_industry := NULL;
916 l_prepare_stmt := NULL;
917 l_gms_status := 'N';
918
919 IF FND_INSTALLATION.GET(8402, 8402, l_gms_status, l_industry) THEN
920
921 IF l_gms_status ='I' THEN
922
923 l_gms_enabled := 0;
924 l_prepare_stmt := 'BEGIN IF GMS_INSTALL.ENABLED() THEN'||' :1 := 1; END IF; END;';
925
926 psa_utils.debug_other_string(g_state_level,l_path_name, ' l_prepare_stmt -> '||l_prepare_stmt);
927
928 EXECUTE IMMEDIATE l_prepare_stmt USING OUT l_gms_enabled;
929
930 psa_utils.debug_other_string(g_state_level,l_path_name, ' l_gms_enabled -> '||l_gms_enabled);
931
932 IF l_gms_enabled = 1 THEN
933
934 l_prepare_stmt := ' BEGIN '||
935 ' GMS_FUNDS_CONTROL_PKG.COPY_GL_PKT_TO_GMS_PKT ( '||
936 ' :application_id, :mode, :partial_resv_flag, :ret_code); '||
937 ' END; ';
938 psa_utils.debug_other_string(g_state_level,l_path_name, ' l_prepare_stmt -> '||l_prepare_stmt);
939 EXECUTE IMMEDIATE l_prepare_stmt USING IN g_application_id,
940 IN l_bc_mode,
941 IN l_partial_resv_flag,
942 OUT l_ret_code;
943 IF (l_ret_code = 'F') THEN
944 psa_utils.debug_other_string(g_state_level,l_path_name, 'GMS Integration API Failed');
945 FND_MESSAGE.SET_NAME('GMS', 'GMS_FUNDS_CHECK_FAILED');
946 FND_MSG_PUB.ADD;
947 RAISE FND_API.G_EXC_ERROR;
948 ELSE
949 psa_utils.debug_other_string(g_state_level,l_path_name, 'GMS Integration API Successful');
950 END IF;
951
952 END IF;
953 END IF;
954 END IF;
955
956 END;
957 EXCEPTION
958 WHEN others THEN
959 psa_utils.debug_other_string(g_excep_level,l_path_name,'Error in pa_gms_integration_api' );
960 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
961
962 END pa_gms_integration_api;
963
964 PROCEDURE pa_gms_tieback_api IS
965
966 l_path_name VARCHAR2(500);
967 l_pa_status VARCHAR2(1);
968 l_pa_enabled INTEGER;
969 l_gms_status VARCHAR2(1);
970 l_gms_enabled INTEGER;
971 l_prepare_stmt VARCHAR2(2000);
972 l_industry fnd_profile_option_values.profile_option_value%TYPE;
973 l_bc_mode VARCHAR2(1);
974
975 BEGIN
976 l_path_name := g_path_name||'.pa_gms_tieback_api';
977 psa_utils.debug_other_string(g_state_level,l_path_name, 'PA_GMS_TIEBACK_API Invoked');
978
979 psa_utils.debug_other_string(g_state_level,l_path_name, 'Invoke PA Tieback API if PA is enabled');
980
981 IF g_bc_mode = 'C' THEN
982 l_bc_mode := 'C';
983 ELSIF g_bc_mode ='P' THEN
984 l_bc_mode := 'R';
985 ELSE
986 l_bc_mode := g_bc_mode;
987 END IF;
988
989 BEGIN
990 l_industry := NULL;
991 l_prepare_stmt := NULL;
992 l_pa_status := 'N';
993
994 IF FND_INSTALLATION.GET(275, 275, l_pa_status, l_industry) THEN
995
996 IF l_pa_status ='I' THEN
997
998 l_pa_enabled := 0;
999 l_prepare_stmt := 'BEGIN IF PA_BUDGET_FUND_PKG.IS_PA_BC_ENABLED() THEN'||' :1 := 1; END IF; END;';
1000
1004
1001 psa_utils.debug_other_string(g_state_level,l_path_name, ' l_prepare_stmt -> '||l_prepare_stmt);
1002
1003 EXECUTE IMMEDIATE l_prepare_stmt USING OUT l_pa_enabled;
1005 psa_utils.debug_other_string(g_state_level,l_path_name, ' l_pa_enabled -> '||l_pa_enabled);
1006
1007 IF l_pa_enabled = 1 THEN
1008
1009 l_prepare_stmt := ' BEGIN '||
1010 ' PA_FUNDS_CONTROL_PKG1.TIEBACK_FAILED_ACCT_STATUS( '||':bc_mode );'||
1011 ' END; ';
1012 psa_utils.debug_other_string(g_state_level,l_path_name, ' l_pa_enabled -> '||l_pa_enabled);
1013 EXECUTE IMMEDIATE l_prepare_stmt USING IN l_bc_mode;
1014
1015 psa_utils.debug_other_string(g_state_level,l_path_name, 'PA Tieback API Successful');
1016
1017 END IF;
1018 END IF;
1019 END IF;
1020
1021 END;
1022
1023 psa_utils.debug_other_string(g_state_level,l_path_name, 'Invoke GMS Tieback API if GMS is enabled');
1024
1025 BEGIN
1026 l_industry := NULL;
1027 l_prepare_stmt := NULL;
1028 l_gms_status := 'N';
1029
1030 IF FND_INSTALLATION.GET(8402, 8402, l_gms_status, l_industry) THEN
1031
1032 IF l_gms_status ='I' THEN
1033
1034 l_gms_enabled := 0;
1035 l_prepare_stmt := 'BEGIN IF GMS_INSTALL.ENABLED() THEN'||' :1 := 1; END IF; END;';
1036
1037 psa_utils.debug_other_string(g_state_level,l_path_name, ' l_prepare_stmt -> '||l_prepare_stmt);
1038
1039 EXECUTE IMMEDIATE l_prepare_stmt USING OUT l_gms_enabled;
1040
1041 psa_utils.debug_other_string(g_state_level,l_path_name, ' l_gms_enabled -> '||l_gms_enabled);
1042
1043 IF l_gms_enabled = 1 THEN
1044
1045 l_prepare_stmt := ' BEGIN '||
1046 ' GMS_FUNDS_CONTROL_PKG.TIEBACK_FAILED_ACCT_STATUS( '||':bc_mode );'||
1047 ' END; ';
1048 psa_utils.debug_other_string(g_state_level,l_path_name, ' l_prepare_stmt -> '||l_prepare_stmt);
1049 EXECUTE IMMEDIATE l_prepare_stmt USING IN l_bc_mode;
1050
1051 psa_utils.debug_other_string(g_state_level,l_path_name, 'GMS Tieback API Successful');
1052
1053 END IF;
1054 END IF;
1055 END IF;
1056
1057 END;
1058 EXCEPTION
1059 WHEN others THEN
1060 psa_utils.debug_other_string(g_excep_level,l_path_name,'Error in pa_gms_tieback_api' );
1061 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1062
1063 END pa_gms_tieback_api;
1064
1065 -----------------------------------------
1066
1067 procedure psa_cleanup_gt IS
1068 l_path_name varchar2(300) ;
1069 BEGIN
1070 l_path_name := g_path_name || '.psa_cleanup_gt';
1071
1072 psa_utils.debug_other_string(g_state_level,l_path_name,'Cleaning up xla GT Tables');
1073 DELETE FROM XLA_AE_HEADERS_GT;
1074 psa_utils.debug_other_string(g_state_level,l_path_name, '1 XLA_AE_HEADERS_GT : Deleted Row count :'||SQL%ROWCOUNT);
1075 DELETE FROM XLA_AE_LINES_GT;
1076 psa_utils.debug_other_string(g_state_level,l_path_name, '2 XLA_AE_LINES_GT : Deleted Row count :'||SQL%ROWCOUNT);
1077 DELETE FROM XLA_VALIDATION_HDRS_GT;
1078 psa_utils.debug_other_string(g_state_level,l_path_name, '3 XLA_VALIDATION_HDRS_GT : Deleted Row count :'||SQL%ROWCOUNT);
1079 DELETE FROM XLA_VALIDATION_LINES_GT;
1080 psa_utils.debug_other_string(g_state_level,l_path_name, '4 XLA_VALIDATION_LINES_GT : Deleted Row count :'||SQL%ROWCOUNT);
1081 DELETE FROM XLA_BAL_CTRL_CTRBS_GT;
1082 psa_utils.debug_other_string(g_state_level,l_path_name, '5 XLA_BAL_CTRL_CTRBS_GT : Deleted Row count :'||SQL%ROWCOUNT);
1083 DELETE FROM XLA_BAL_PERIOD_STATS_GT;
1084 psa_utils.debug_other_string(g_state_level,l_path_name, '6 XLA_BAL_PERIOD_STATS_GT : Deleted Row count :'||SQL%ROWCOUNT);
1085 DELETE FROM XLA_BAL_RECREATE_GT;
1086 psa_utils.debug_other_string(g_state_level,l_path_name, '7
1087 XLA_BAL_RECREATE_GT : Deleted Row count :'||SQL%ROWCOUNT);
1088 DELETE FROM XLA_BAL_ANACRI_LINES_GT;
1089 psa_utils.debug_other_string(g_state_level,l_path_name, '8
1090 XLA_BAL_ANACRI_LINES_GT : Deleted Row count :'||SQL%ROWCOUNT);
1091 DELETE FROM XLA_BAL_ANACRI_CTRBS_GT;
1092 psa_utils.debug_other_string(g_state_level,l_path_name, '9
1093 XLA_BAL_ANACRI_CTRBS_GT : Deleted Row count :'||SQL%ROWCOUNT);
1094 DELETE FROM XLA_BAL_SYNCHRONIZE_GT;
1095 psa_utils.debug_other_string(g_state_level,l_path_name, '10
1096 XLA_BAL_SYNCHRONIZE_GT : Deleted Row count :'||SQL%ROWCOUNT);
1097 DELETE FROM XLA_BAL_STATUSES_GT;
1098 psa_utils.debug_other_string(g_state_level,l_path_name, '11
1099 XLA_BAL_STATUSES_GT : Deleted Row count :'||SQL%ROWCOUNT);
1100 DELETE FROM XLA_BAL_CTRL_LINES_GT;
1101 psa_utils.debug_other_string(g_state_level,l_path_name, '12
1102 XLA_BAL_CTRL_LINES_GT : Deleted Row count :'||SQL%ROWCOUNT);
1103 DELETE FROM XLA_EVENTS_GT;
1104 psa_utils.debug_other_string(g_state_level,l_path_name, '13 XLA_EVENTS_GT
1105 : Deleted Row count :'||SQL%ROWCOUNT);
1106 DELETE FROM XLA_EVT_CLASS_SOURCES_GT;
1107 psa_utils.debug_other_string(g_state_level,l_path_name, '14
1108 XLA_EVT_CLASS_SOURCES_GT : Deleted Row count :'||SQL%ROWCOUNT);
1109 DELETE FROM XLA_EVT_CLASS_ORDERS_GT;
1110 psa_utils.debug_other_string(g_state_level,l_path_name, '15
1111 XLA_EVT_CLASS_ORDERS_GT : Deleted Row count :'||SQL%ROWCOUNT);
1112 DELETE FROM XLA_TAB_ERRORS_GT;
1113 psa_utils.debug_other_string(g_state_level,l_path_name, '16
1114 XLA_TAB_ERRORS_GT : Deleted Row count :'||SQL%ROWCOUNT);
1115 DELETE FROM XLA_SEQ_JE_HEADERS_GT;
1116 psa_utils.debug_other_string(g_state_level,l_path_name, '17
1117 XLA_SEQ_JE_HEADERS_GT : Deleted Row count :'||SQL%ROWCOUNT);
1118 DELETE FROM XLA_TAB_NEW_CCIDS_GT;
1119 psa_utils.debug_other_string(g_state_level,l_path_name, '18
1120 XLA_TAB_NEW_CCIDS_GT : Deleted Row count :'||SQL%ROWCOUNT);
1121 DELETE FROM XLA_EXTRACT_OBJECTS_GT;
1122 psa_utils.debug_other_string(g_state_level,l_path_name, '19
1123 XLA_EXTRACT_OBJECTS_GT : Deleted Row count :'||SQL%ROWCOUNT);
1124 DELETE FROM XLA_REFERENCE_OBJECTS_GT;
1125 psa_utils.debug_other_string(g_state_level,l_path_name, '20
1126 XLA_REFERENCE_OBJECTS_GT : Deleted Row count :'||SQL%ROWCOUNT);
1127 DELETE FROM XLA_TRANSACTION_ACCTS_GT;
1128 psa_utils.debug_other_string(g_state_level,l_path_name, '21
1129 XLA_TRANSACTION_ACCTS_GT : Deleted Row count :'||SQL%ROWCOUNT);
1130 DELETE FROM XLA_UPG_LINE_CRITERIA_GT;
1131 psa_utils.debug_other_string(g_state_level,l_path_name, '22
1132 XLA_UPG_LINE_CRITERIA_GT : Deleted Row count :'||SQL%ROWCOUNT);
1133 DELETE FROM XLA_TRIAL_BALANCES_GT;
1134 psa_utils.debug_other_string(g_state_level,l_path_name, '23
1135 XLA_TRIAL_BALANCES_GT : Deleted Row count :'||SQL%ROWCOUNT);
1136 DELETE FROM XLA_ACCT_PROG_EVENTS_GT;
1137 psa_utils.debug_other_string(g_state_level,l_path_name, '24
1138 XLA_ACCT_PROG_EVENTS_GT : Deleted Row count :'||SQL%ROWCOUNT);
1139 DELETE FROM XLA_ACCT_PROG_DOCS_GT;
1140 psa_utils.debug_other_string(g_state_level,l_path_name, '25
1141 XLA_ACCT_PROG_DOCS_GT : Deleted Row count :'||SQL%ROWCOUNT);
1142 DELETE FROM XLA_MERGE_SEG_MAPS_GT;
1143 psa_utils.debug_other_string(g_state_level,l_path_name, '26
1144 XLA_MERGE_SEG_MAPS_GT : Deleted Row count :'||SQL%ROWCOUNT);
1145 DELETE FROM XLA_EVENTS_INT_GT;
1146 psa_utils.debug_other_string(g_state_level,l_path_name, '27
1147 XLA_EVENTS_INT_GT : Deleted Row count :'||SQL%ROWCOUNT);
1148 DELETE FROM XLA_REPORT_BALANCES_GT;
1149 psa_utils.debug_other_string(g_state_level,l_path_name, '28
1150 XLA_REPORT_BALANCES_GT : Deleted Row count :'||SQL%ROWCOUNT);
1151 DELETE FROM XLA_TB_BALANCES_GT;
1152 psa_utils.debug_other_string(g_state_level,l_path_name, '29
1153 XLA_TB_BALANCES_GT : Deleted Row count :'||SQL%ROWCOUNT);
1154 DELETE FROM XLA_BAL_AC_CTRBS_GT;
1155 psa_utils.debug_other_string(g_state_level,l_path_name, '30
1156 XLA_BAL_AC_CTRBS_GT : Deleted Row count :'||SQL%ROWCOUNT);
1157 psa_utils.debug_other_string(g_state_level,l_path_name,'clean_xla_gt -');
1158 END psa_cleanup_gt;
1159
1160
1161 ----------------------------------------------------- --------------------------------------------------
1162 BEGIN
1163 g_log_enabled := fnd_log.test
1164 (log_level => FND_LOG.G_CURRENT_RUNTIME_LEVEL
1165 ,MODULE => g_path_name);
1166 END PSA_BC_XLA_PVT;