eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PSA_BC_XLA_PVT

Source


1 PACKAGE BODY PSA_BC_XLA_PVT AS
2 --$Header: psavbcxb.pls 120.57 2011/06/13 11:13:02 yanasing 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 g_audsid      NUMBER;
22 
23 
24 --==========================================================================
25 -- declaring private constants
26 --==========================================================================
27 
28 C_YES                       CONSTANT VARCHAR2(1)   := 'Y'; -- yes flag
29 C_NO                        CONSTANT VARCHAR2(1)   := 'N'; -- no flag
30 C_FUNDS_CHECK               CONSTANT VARCHAR2(1)   := 'C';
31 C_FUNDS_RESERVE             CONSTANT VARCHAR2(1)   := 'R';
32 C_FUNDS_PARTIAL             CONSTANT VARCHAR2(1)   := 'P';
33 C_FUNDS_FORCE_PASS          CONSTANT VARCHAR2(1)   := 'F';
34 C_FUNDS_ADVISORY            CONSTANT VARCHAR2(1)   := 'A';
35 C_FUNDS_CHK_FULL            CONSTANT VARCHAR2(1)   := 'M';
36 
37 --==========================================================================
38 -- declaring private variables
39 --==========================================================================
40 TYPE psa_acctg_errors_table IS TABLE OF psa_bc_accounting_errors%ROWTYPE;
41 TYPE psa_events_table IS TABLE OF psa_bc_xla_events_gt.event_id%TYPE;
42 
43 --==========================================================================
44 -- Forward Declaration of PA/GMS API's
45 --==========================================================================
46 PROCEDURE pa_gms_integration_api;
47 PROCEDURE pa_gms_tieback_api;
48 
49 procedure psa_cleanup_gt;
50 
51   PROCEDURE init
52   IS
53     l_path_name       VARCHAR2(500);
54     l_file_info       VARCHAR2(2000);
55   BEGIN
56     l_path_name := g_path_name || '.init';
57     l_file_info :=
58        '$Header: psavbcxb.pls 120.57 2011/06/13 11:13:02 yanasing ship $';
59     psa_utils.debug_other_string(g_state_level,l_path_name,  'PSA_BC_XLA_PVT version = '||l_file_info);
60   END;
61 
62   PROCEDURE psa_xla_error_cleanup
63   (
64     p_xla_transaction_entities IN xla_transaction_entities%ROWTYPE
65   )
66   IS
67     PRAGMA AUTONOMOUS_TRANSACTION;
68     l_path_name VARCHAR2(500);
69   BEGIN
70     DELETE psa_xla_accounting_errors p
71      WHERE p.entity_code = p_xla_transaction_entities.entity_code
72        AND NVL(p.source_id_int_1, -1) = NVL(p_xla_transaction_entities.source_id_int_1, -1)
73        AND NVL(p.source_id_int_2, -1) = NVL(p_xla_transaction_entities.source_id_int_2, -1)
74        AND NVL(p.source_id_int_3, -1) = NVL(p_xla_transaction_entities.source_id_int_3, -1)
75        AND NVL(p.source_id_int_4, -1) = NVL(p_xla_transaction_entities.source_id_int_4, -1)
76        AND NVL(p.source_id_char_1, ' ') = NVL(p_xla_transaction_entities.source_id_char_1, ' ')
77        AND NVL(p.source_id_char_2, ' ') = NVL(p_xla_transaction_entities.source_id_char_2, ' ')
78        AND NVL(p.source_id_char_3, ' ') = NVL(p_xla_transaction_entities.source_id_char_3, ' ')
79        AND NVL(p.source_id_char_4, ' ') = NVL(p_xla_transaction_entities.source_id_char_4, ' ');
80     COMMIT;
81   END;
82 
83   PROCEDURE psa_xla_error_cleanup
84   IS
85     l_path_name VARCHAR2(500);
86     l_psa_xla_accounting_errors psa_xla_accounting_errors%ROWTYPE;
87   BEGIN
88     FOR entity_rec IN (SELECT t.*
89                           FROM xla_events e,
90                                xla_transaction_entities t,
91                                psa_bc_xla_events_gt p
92                          WHERE p.event_id = e.event_id
93                            AND e.entity_id = t.entity_id) LOOP
94       psa_xla_error_cleanup (entity_rec);
95     END LOOP;
96   END;
97 
98   /*
99     This is the final Autonomous Transaction Procedure
100     that inserts the PSA/XLA error into the table.
101   */
102 
103   PROCEDURE psa_xla_error
104   (
105     p_psa_xla_accounting_errors IN psa_xla_accounting_errors%ROWTYPE
106   )
107   IS
108     PRAGMA AUTONOMOUS_TRANSACTION;
109     l_path_name VARCHAR2(500);
110     l_psa_xla_accounting_errors psa_xla_accounting_errors%ROWTYPE;
111   BEGIN
112     l_psa_xla_accounting_errors := p_psa_xla_accounting_errors;
113     IF (p_psa_xla_accounting_errors.accounting_error_id IS NULL) THEN
114       SELECT psa_xla_accounting_errors_s.nextval
115         INTO l_psa_xla_accounting_errors.accounting_error_id
116         FROM DUAL;
117     END IF;
118 
119    /*INSERT INTO psa_xla_accounting_errors
120     VALUES l_psa_xla_accounting_errors;*/
121 
122     INSERT INTO psa_xla_accounting_errors
123     (	ACCOUNTING_ERROR_ID,
124 	APPLICATION_ID,
125 	LEDGER_ID,
126 	ENTITY_CODE,
127 	ENTITY_ID,
128 	EVENT_DATE ,
129 	EVENT_ID,
130 	TRANSACTION_NUMBER,
131 	AE_HEADER_ID,
132 	AE_LINE_NUM,
133 	SOURCE_ID_INT_1,
134 	SOURCE_ID_INT_2,
135 	SOURCE_ID_INT_3,
136 	SOURCE_ID_INT_4,
137 	SOURCE_ID_CHAR_1,
138 	SOURCE_ID_CHAR_2,
139 	SOURCE_ID_CHAR_3,
140 	SOURCE_ID_CHAR_4,
141 	MESSAGE_CODE,
142 	MESSAGE_NUM,
143 	ENCODED_MSG,
144 	AUDSID,
145 	CREATION_DATE,
146 	CREATED_BY
147 	)
148     VALUES (    l_psa_xla_accounting_errors.ACCOUNTING_ERROR_ID,
149 		l_psa_xla_accounting_errors.APPLICATION_ID ,
150 		l_psa_xla_accounting_errors.LEDGER_ID,
151 		l_psa_xla_accounting_errors.ENTITY_CODE,
152 		l_psa_xla_accounting_errors.ENTITY_ID ,
153 		l_psa_xla_accounting_errors.EVENT_DATE ,
154 		l_psa_xla_accounting_errors.EVENT_ID,
155 		l_psa_xla_accounting_errors.TRANSACTION_NUMBER,
156 		l_psa_xla_accounting_errors.AE_HEADER_ID,
157 		l_psa_xla_accounting_errors.AE_LINE_NUM ,
158 		l_psa_xla_accounting_errors.SOURCE_ID_INT_1,
159 		l_psa_xla_accounting_errors.SOURCE_ID_INT_2,
160 		l_psa_xla_accounting_errors.SOURCE_ID_INT_3,
161 		l_psa_xla_accounting_errors.SOURCE_ID_INT_4,
162 		l_psa_xla_accounting_errors.SOURCE_ID_CHAR_1,
163 		l_psa_xla_accounting_errors.SOURCE_ID_CHAR_2,
164 		l_psa_xla_accounting_errors.SOURCE_ID_CHAR_3,
165 		l_psa_xla_accounting_errors.SOURCE_ID_CHAR_4 ,
166 		l_psa_xla_accounting_errors.MESSAGE_CODE ,
167 		l_psa_xla_accounting_errors.MESSAGE_NUM,
168 		l_psa_xla_accounting_errors.ENCODED_MSG,
169 		l_psa_xla_accounting_errors.AUDSID,
170 		l_psa_xla_accounting_errors.CREATION_DATE ,
171 		l_psa_xla_accounting_errors.CREATED_BY);
172 
173     COMMIT;
174   END;
175 
176   PROCEDURE psa_xla_error
177   (
178     p_message_code IN VARCHAR2,
179     p_event_id IN NUMBER DEFAULT NULL
180   )
181   IS
182     l_path_name VARCHAR2(500);
183     l_message_text psa_xla_accounting_errors.encoded_msg%TYPE;
184     l_psa_xla_accounting_errors psa_xla_accounting_errors%ROWTYPE;
185     l_msg_index NUMBER;
186   BEGIN
187     FOR event_rec IN (SELECT e.event_id,
188                              t.entity_id,
189                              t.entity_code,
190                              t.source_id_int_1,
191                              t.source_id_int_2,
192                              t.source_id_int_3,
193                              t.source_id_int_4,
194                              t.source_id_char_1,
195                              t.source_id_char_2,
196                              t.source_id_char_3,
197                              t.source_id_char_4,
198                              t.application_id,
199                              t.transaction_number,
200                              e.event_date,
201                              t.ledger_id
202                         FROM xla_events e,
203                              xla_transaction_entities t,
204                              psa_bc_xla_events_gt p
205                        WHERE p.event_id = e.event_id
206                          AND e.entity_id = t.entity_id
207                          AND p.event_id = NVL(p_event_id, p.event_id)) LOOP
208 
209       l_psa_xla_accounting_errors.message_code := p_message_code;
210       l_psa_xla_accounting_errors.encoded_msg := fnd_message.get;
211       l_psa_xla_accounting_errors.audsid := g_audsid;
212       l_psa_xla_accounting_errors.creation_date := SYSDATE;
213       l_psa_xla_accounting_errors.created_by := g_user_id;
214       l_psa_xla_accounting_errors.entity_id := event_rec.entity_id;
215       l_psa_xla_accounting_errors.event_id := event_rec.event_id;
216       l_psa_xla_accounting_errors.application_id := event_rec.application_id;
217       l_psa_xla_accounting_errors.source_id_int_1 := event_rec.source_id_int_1;
218       l_psa_xla_accounting_errors.source_id_int_2 := event_rec.source_id_int_2;
219       l_psa_xla_accounting_errors.source_id_int_3 := event_rec.source_id_int_3;
220       l_psa_xla_accounting_errors.source_id_int_4 := event_rec.source_id_int_4;
221       l_psa_xla_accounting_errors.source_id_char_1 := event_rec.source_id_char_1;
222       l_psa_xla_accounting_errors.source_id_char_2 := event_rec.source_id_char_2;
223       l_psa_xla_accounting_errors.source_id_char_3 := event_rec.source_id_char_3;
224       l_psa_xla_accounting_errors.source_id_char_4 := event_rec.source_id_char_4;
225       l_psa_xla_accounting_errors.entity_code := event_rec.entity_code;
226       l_psa_xla_accounting_errors.transaction_number := event_rec.transaction_number;
227       l_psa_xla_accounting_errors.event_date := event_rec.event_date;
228       l_psa_xla_accounting_errors.ledger_id := event_rec.ledger_id;
229       psa_xla_error (l_psa_xla_accounting_errors);
230     END LOOP;
231   END;
232 
233   PROCEDURE copy_xla_error
234   (
235     p_error_found OUT NOCOPY VARCHAR2
236   )
237   IS
238     l_psa_xla_accounting_errors psa_xla_accounting_errors%ROWTYPE;
239     l_xla_error_found BOOLEAN := FALSE;
240     l_path_name       VARCHAR2(500);
241     l_count NUMBER;
242   BEGIN
243     l_path_name := g_path_name || '.copy_xla_error';
244     p_error_found := 'N';
245     psa_utils.debug_other_string(g_state_level,l_path_name, 'BEGIN of procedure copy_xla_error ' );
246     FOR error_rec IN (SELECT e.entity_id,
247                              e.event_id,
248                              e.application_id,
249                              er.message_number,
250                              er.encoded_msg,
251                              t.source_id_int_1,
252                              t.source_id_int_2,
253                              t.source_id_int_3,
254                              t.source_id_int_4,
255                              t.source_id_char_1,
256                              t.source_id_char_2,
257                              t.source_id_char_3,
258                              t.source_id_char_4,
259                              t.entity_code,
260                              t.transaction_number,
261                              t.ledger_id,
262                              e.event_date,
263                              er.ae_header_id,
264                              er.ae_line_num
265                         FROM xla_accounting_errors er,
266                              psa_bc_xla_events_gt p,
267                              xla_events e,
268                              xla_transaction_entities t
269                        WHERE er.event_id = p.event_id
270                          AND e.event_id = p.event_id
271                          AND t.entity_id = e.entity_id) LOOP
272       p_error_found := 'Y';
273       l_psa_xla_accounting_errors.entity_id := error_rec.entity_id;
274       l_psa_xla_accounting_errors.event_id := error_rec.event_id;
275       l_psa_xla_accounting_errors.message_num := error_rec.message_number;
276       l_psa_xla_accounting_errors.message_code := 'XLA_ERROR';
277       l_psa_xla_accounting_errors.encoded_msg := error_rec.encoded_msg;
278       l_psa_xla_accounting_errors.audsid := g_audsid;
279       l_psa_xla_accounting_errors.creation_date := SYSDATE;
280       l_psa_xla_accounting_errors.created_by := g_user_id;
281       l_psa_xla_accounting_errors.application_id := error_rec.application_id;
282       l_psa_xla_accounting_errors.source_id_int_1 := error_rec.source_id_int_1;
283       l_psa_xla_accounting_errors.source_id_int_2 := error_rec.source_id_int_2;
284       l_psa_xla_accounting_errors.source_id_int_3 := error_rec.source_id_int_3;
285       l_psa_xla_accounting_errors.source_id_int_4 := error_rec.source_id_int_4;
286       l_psa_xla_accounting_errors.source_id_char_1 := error_rec.source_id_char_1;
287       l_psa_xla_accounting_errors.source_id_char_2 := error_rec.source_id_char_2;
288       l_psa_xla_accounting_errors.source_id_char_3 := error_rec.source_id_char_3;
289       l_psa_xla_accounting_errors.source_id_char_4 := error_rec.source_id_char_4;
290       l_psa_xla_accounting_errors.entity_code := error_rec.entity_code;
291       l_psa_xla_accounting_errors.transaction_number := error_rec.transaction_number;
292       l_psa_xla_accounting_errors.ledger_id := error_rec.ledger_id;
293       l_psa_xla_accounting_errors.event_date := error_rec.event_date;
294       l_psa_xla_accounting_errors.ae_header_id := error_rec.ae_header_id;
295       l_psa_xla_accounting_errors.ae_line_num := error_rec.ae_line_num;
296       psa_xla_error (l_psa_xla_accounting_errors);
297     END LOOP;
298   END;
299 
300   PROCEDURE try_to_interpret_xla_error
301   IS
302     l_psa_xla_accounting_errors psa_xla_accounting_errors%ROWTYPE;
303     l_xla_error_found BOOLEAN := FALSE;
304     l_path_name       VARCHAR2(500);
305     l_count NUMBER;
306   BEGIN
307     l_path_name := g_path_name || '.try_to_interpret_xla_error';
308     psa_utils.debug_other_string(g_state_level,l_path_name, 'BEGIN of procedure try_to_interpret_xla_error ' );
309 
310     psa_utils.debug_other_string(g_state_level,l_path_name, 'Checking for Invalid AAD' );
311     FOR xla_rec IN (SELECT ru.compile_status_code,
312                            e.entity_id,
313                            e.event_id,
314                            ru.name product_rule_name,
315                            ru.product_rule_type_code product_rule_owner,
316                            t.ledger_id
317                       FROM xla_events e,
318                            xla_transaction_entities t,
319                            psa_bc_xla_events_gt p,
320                            gl_ledgers g,
321                            xla_acctg_methods_fvl m,
322                            xla_acctg_method_rules_fvl r,
323                            xla_product_rules_fvl ru
324                      WHERE p.event_id = e.event_id
325                        AND e.entity_id = t.entity_id
326                        AND t.ledger_id = g.ledger_id
327                        AND g.sla_accounting_method_code = m.accounting_method_code
328                        AND g.sla_accounting_method_type = m.accounting_method_type_code
329                        AND r.accounting_method_code = m.accounting_method_code
330                        AND r.accounting_method_type_code = m.accounting_method_type_code
331                        AND r.application_id = t.application_id
332                        AND r.product_rule_code = ru.product_rule_code
333                        AND r.product_rule_type_code = ru.product_rule_type_code
334                        AND e.event_date BETWEEN r.start_date_active AND NVL(r.end_date_active, e.event_date+1)) LOOP
335       psa_utils.debug_other_string(g_state_level,l_path_name, 'compile_status_code='||xla_rec.compile_status_code);
336       IF xla_rec.compile_status_code <> 'Y' THEN
337         fnd_message.set_name ('XLA','XLA_AP_PAD_INACTIVE');
338         fnd_message.set_token ('PAD_NAME', xla_rec.product_rule_name);
339         fnd_message.set_token ('OWNER', xla_lookups_pkg.get_meaning('XLA_OWNER_TYPE',xla_rec.product_rule_owner));
340         fnd_message.set_token ('SUBLEDGER_ACCTG_METHOD', xla_accounting_cache_pkg.GetSessionValueChar
341                                                           (p_source_code => 'XLA_ACCOUNTING_METHOD_NAME'
342                                                           ,p_target_ledger_id => xla_rec.ledger_id));
343 
344         psa_utils.debug_other_string(g_state_level,l_path_name, 'Calling psa_xla_error');
345         psa_xla_error
346         (
347           p_message_code => 'XLA_AP_PAD_INACTIVE',
348           p_event_id     => xla_rec.event_id
349         );
350       END IF;
351     END LOOP;
352   EXCEPTION
353     WHEN OTHERS THEN
354       psa_utils.debug_other_string(g_state_level,l_path_name, 'Error:'||SQLERRM);
355       RAISE;
356   END;
357 
358 
359 
360 
361 -------------------------------------------------------------------------------
362 -- PROCEDURE  psa_acctg_errors_insert
363 -- Autonomously insert xla accounting erros rows into psa_bc_accounting_errors
364 -- This will allow to see the error records in PSA view results report
365 -- in case a product team issues a rollback.
366 -------------------------------------------------------------------------------
367 
368 PROCEDURE psa_acctg_errors_insert(psa_events IN psa_events_table, psa_acctg_errors IN psa_acctg_errors_table) IS
369 PRAGMA AUTONOMOUS_TRANSACTION;
370 l_path_name            VARCHAR2(500);
371 BEGIN
372    l_path_name := g_path_name||'.psa_acctg_errors_insert';
373 
374    psa_utils.debug_other_string(g_state_level,l_path_name, 'BEGIN of procedure psa_acctg_errors_insert');
375 
376    FORALL i in 1..psa_events.COUNT
377        DELETE FROM psa_bc_accounting_errors
378        WHERE event_id = psa_events(i);
379    psa_utils.debug_other_string(g_state_level,l_path_name,'No of rows deleted from psa_bc_accounting_erros: '||SQL%ROWCOUNT);
380 
381    FORALL j in 1..psa_acctg_errors.COUNT
382        INSERT INTO psa_bc_accounting_errors VALUES psa_acctg_errors(j);
383    psa_utils.debug_other_string(g_state_level,l_path_name,'No of rows inserted into psa_bc_accounting_erros: '||SQL%ROWCOUNT);
384 
385    COMMIT;
386    psa_utils.debug_other_string(g_state_level,l_path_name, 'END of procedure psa_acctg_errors_insert');
387 
388 END psa_acctg_errors_insert;
389 
390 
391  -- /*============================================================================
392  -- API name     : Budgetary_Control
393  -- Type         : private
394  -- Pre-reqs     : Create events in psa_bc_xla_events_gt
395  -- Description  : Call SLA engine for BCPSA
396  --
397  --  Parameters  :
398  --  IN          :   p_api_version    IN NUMBER  Required
399  --                  p_init_msg_list  IN VARCHAR2 optional Default FND_API.G_FALSE
400  --                  p_commit         IN VARCHAR2 optional Default FND_API.G_FALSE
401  --                  p_application_id IN NUMBER  Required
402  --                  p_bc_mode        IN NUMBER optional Possible values:Check(C ) /Reserve(R )
403  --                  p_bc_override_flag VARCHAR2 optional Possible values: Y/N
404  --                  p_user_id        IN NUMBER optional
405  --                  p_user_resp_id   IN NUMBER optional
406  --
407  -- OUT          :   x_return_status  OUT VARCHAR2(1)
408  --                  x_msg_count      OUT NUMBER
409  --                  x_msg_data       OUT VARCHAR2(2000)
410 --                   x_packet_id      OUT  NUMBER
411  --
412  -- Version      :   Current Version 1.0
413  --                  Initial Version 1.0
414  --
415  --
416  --
417  --  Logic
418  --        - Validate the  parameters
419  --        - Get the events to be processed
420  --        - Call the SLA online accounting engine with required parameters
421  --        - Return the Fund status/error
422  --
423  --  Notes:
424  --         Currently calling accounting engine in document mode
425  --          After SLA API for bcpsa is available need to make neccessary changes
426  --
427  --  Modification History
428  --  Date         Author             Description of Change
429  --
430  -- *===========================================================================*/
431 
432 PROCEDURE Budgetary_Control
433    ( p_init_msg_list             IN  VARCHAR2
434     ,x_return_status             OUT NOCOPY VARCHAR2
435     ,x_msg_count                 OUT NOCOPY NUMBER
436     ,x_msg_data                  OUT NOCOPY VARCHAR2
437     ,p_application_id            IN INTEGER
438     ,p_bc_mode                   IN VARCHAR2
439     ,p_override_flag             IN VARCHAR2
440     ,P_user_id                   IN NUMBER
441     ,P_user_resp_id              IN NUMBER
442     ,x_status_code               OUT NOCOPY VARCHAR2
443     ,x_Packet_ID                 OUT NOCOPY NUMBER
444    )
445 
446 IS
447       --
448       -- To verify events exists in gt table
449       --- and identify eligible events to be processed by SLA
450       ---
451       CURSOR C_entity_event_info ( p_application_id NUMBER) IS
452       SELECT XE.entity_id entity_id
453       FROM XLA_ENTITY_EVENTS_V XE,PSA_BC_XLA_EVENTS_GT BCE
454       WHERE XE.event_id = BCE.event_id
455       AND   XE.event_status_code <> 'P'
456       AND   XE.application_id = p_application_id
457       GROUP BY xe.entity_id;
458 
459       -- cursor to set the status code for API
460       -- Currently only GT table is used for getting the results status
461       -- after the SLA ebncahmcement for BCPSA and funds check code completion this
462       -- might change
463 
464       CURSOR C_get_status_count IS
465       SELECT nvl(sum(decode(upper(result_code),'FATAL',1)),0)  status_fatal_count,
466       sum(decode(upper(result_code),'XLA_ERROR',1)) status_xla_err_count,
467       sum(decode(upper(result_code),'FAIL',1)) status_fail_count,
468       sum(decode(upper(result_code),'PARTIAL',1)) status_partial_count,
469       sum(decode(upper(result_code),'ADVISORY',1)) status_advisory_count,
470       nvl(sum(decode(upper(result_code),'SUCCESS',1)),0) status_success_count,
471       nvl(sum(decode(upper(result_code),'XLA_NO_JOURNAL',1)),0) status_nojournal_count
472       FROM PSA_BC_XLA_EVENTS_GT;
473 
474       CURSOR c_xla_errors IS
475       SELECT 'Y'
476       FROM  PSA_BC_XLA_EVENTS_GT a
477       WHERE not exists (SELECT 'x'
478                         FROM   xla_ae_headers b
479                         WHERE  b.event_id = a.event_id);
480 
481       -- Cursor c_get_bc_xla_events_gt is used to print data from psa_bc_xla_Events_gt
482       -- as entered by product teams. This is useful for debugging.
483       CURSOR c_get_bc_xla_events_gt IS
484       SELECT *
485       FROM   psa_bc_xla_events_gt;
486 
487       CURSOR c_get_psa_events IS
488       SELECT event_id
489       FROM   psa_bc_xla_events_gt;
490 
491       CURSOR c_get_xla_acctg_err IS
492       SELECT xla_evnt.EVENT_ID,
493              xla_evnt.ENTITY_ID,
494              xla_evnt.APPLICATION_ID,
495              xla_err.AE_HEADER_ID,
496              xla_err.AE_LINE_NUM,
497              xla_evnt.TRANSACTION_DATE,
498              fnd_mesg.MESSAGE_NUMBER,
499              fnd_mesg.MESSAGE_NAME,
500              xla_err.ENCODED_MSG,
501              xla_err.ERROR_SOURCE_CODE,
502              xla_evnt.LEDGER_ID,
503              xla_evnt.LEGAL_ENTITY_ID,
504              xla_evnt.transaction_number DOCUMENT_REFERENCE,
505              NULL BATCH_REFERENCE,
506              to_char(xla_evnt.event_id) LINE_REFERENCE,
507              SYSDATE CREATION_DATE,
508              'Y' XLA_ERROR_FLAG
509       FROM   psa_bc_xla_events_gt psa_evnt,
510              xla_events_gt xla_evnt,
511              xla_accounting_errors xla_err,
512              fnd_new_messages fnd_mesg
513       WHERE  psa_evnt.event_id = xla_evnt.event_id
514         AND  xla_evnt.event_id = xla_err.event_id
515         AND  fnd_mesg.application_id = 602
516         AND  DECODE(xla_err.message_number, 0, -99, xla_err.message_number) = fnd_mesg.message_number (+)
517         AND  userenv('LANG') =  fnd_mesg.language_code (+);
518 
519 
520       l_entity_event_info    c_entity_event_info%ROWTYPE;
521       l_status_count         C_get_status_count%ROWTYPE;
522       l_event_source_info    xla_events_pub_pkg.t_event_source_info;
523       l_entity_id            NUMBER;
524       l_accounting_flag      VARCHAR2(1);
525       l_accounting_mode      VARCHAR2(20);
526       l_transfer_flag        VARCHAR2(1);
527       l_gl_posting_flag      VARCHAR2(1);
528       l_offline_flag         VARCHAR2(1);
529       l_accounting_batch_id  NUMBER;
530       l_errbuf               VARCHAR2(2000);
531       l_retcode              NUMBER;
532       l_request_id           NUMBER;
533       l_application_id       NUMBER;
534       l_bc_mode              VARCHAR2(1);
535       l_partial_reserve_flag VARCHAR2(1);
536       l_override_flag        VARCHAR2(1);
537       l_ledger_id            NUMBER;
538       l_user_id              NUMBER;
539       l_user_resp_id         NUMBER;
540       l_path_name            VARCHAR2(500);
541       e_event_id_null        EXCEPTION;
542       l_accounting_events    BOOLEAN;
543       l_run_id               NUMBER;
544       l_trx_num              NUMBER;
545       l_event_num            NUMBER;
546       l_overall_success      BOOLEAN;
547       l_count                NUMBER;
548       l_index                BINARY_INTEGER := 1;
549       l_status               psa_bc_xla_events_gt%ROWTYPE;
550       l_xla_error            VARCHAR2(1);
551       l_psa_acctg_errors     psa_acctg_errors_table;
552       l_psa_events           psa_events_table;
553       l_failed_evnt_array    PSA_FUNDS_CHECKER_PKG.num_rec;
554       l_r12_upgrade_date     DATE;
555       l_error_found          VARCHAR2(1);
556       l_event_list           VARCHAR2(1024);
557       l_prepare_stmt         VARCHAR2(300); -- Bug 12352531
558 
559     BEGIN
560 
561        --
562        -- Start of the budgetary control
563        --
564        l_path_name := g_path_name || '.Budgetary_Control';
565        psa_utils.debug_other_string(g_state_level,l_path_name, 'BEGIN of procedure budgetary_control ' );
566 
567        --
568        -- Get the parameters values
569        --
570        psa_utils.debug_other_string(g_state_level,l_path_name, 'Application Id = ' ||p_application_id);
571        psa_utils.debug_other_string(g_state_level,l_path_name, 'Budgetary Control Mode = ' ||p_bc_mode);
572        psa_utils.debug_other_string(g_state_level,l_path_name, 'Override Flag = ' ||p_override_flag);
573        psa_utils.debug_other_string(g_state_level,l_path_name, 'User Id = ' ||p_user_id);
574        psa_utils.debug_other_string(g_state_level,l_path_name, 'User Responsibility Id = ' ||p_user_resp_id);
575 
576         IF (FND_API.to_boolean(p_init_msg_list)) THEN
577             FND_MSG_PUB.initialize;
578         END IF;
579 
580 
581         --
582         -- validate the parameters bc_mode and override flag
583         --
584         l_bc_mode := p_bc_mode;
585         l_override_flag := p_override_flag;
586         l_application_id:= p_application_id;
587         l_xla_error := 'N';
588         psa_utils.debug_other_string(g_state_level,l_path_name,'Start of Parameter Validation');
589 
590         --
591         -- parameter validations
592         --
593         IF p_application_id IS NULL THEN
594            fnd_message.set_name('PSA','PSA_BC_PARAMETERS_ERROR');
595            fnd_message.set_token('PARAM_NAME','Application Id');
596            fnd_msg_pub.ADD;
597            RAISE Fnd_Api.G_Exc_Error;
598         END IF;
599 
600         -- Currently the packetid is set to -1 , will change after SLA enhancement for PSA
601         x_packet_id := -1;
602 
603 
604         IF (l_bc_mode IS  NULL) THEN
605             l_bc_mode :=C_FUNDS_CHECK;
606         ELSE
607           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
608             Fnd_message.set_name('PSA','PSA_BC_PARAMETERS_ERROR');
609             Fnd_Message.Set_Token('PARAM_NAME','Funds Mode');
610             Fnd_Msg_Pub.ADD;
611             psa_utils.debug_other_msg(p_level => g_error_level,
612                                       p_full_path => l_path_name,
613                                       p_remove_from_stack => FALSE);
614             Fnd_file.put_line(fnd_file.log, fnd_message.get);
615             RAISE Fnd_Api.G_Exc_Error;
616           END IF;
617         END IF;
618 
619         IF (l_override_flag IS  NULL) THEN
620              l_override_flag := C_NO ;
621         ELSE
622              IF (l_override_flag NOT IN (C_YES,C_NO)) THEN
623                  Fnd_message.set_name('PSA','PSA_BC_PARAMETERS_ERROR');
624                  Fnd_Message.Set_Token('PARAM_NAME','Override Flag');
625                  Fnd_Msg_Pub.ADD;
626                  psa_utils.debug_other_msg(p_level => g_error_level,
627                                            p_full_path => l_path_name,
628                                            p_remove_from_stack => FALSE);
629                  Fnd_file.put_line(fnd_file.log, fnd_message.get);
630 
631                  RAISE Fnd_Api.G_Exc_Error;
632              END IF;
633         END IF;
634 
635         psa_utils.debug_other_string(g_state_level,l_path_name,'End of Parameter Validation');
636         --
637         -- Assign the wf parameters to global varibales.
638         -- These variables are used by accounitng engine while calling the funds checker
639         --
640         G_BC_MODE          := l_bc_mode;
641         G_OVERRIDE_FLAG    := l_override_flag;
642         G_USER_ID          := p_user_id;
643         G_USER_RESP_ID     := p_user_resp_id;
644         G_APPLICATION_ID   := p_application_id;
645         G_PACKET_ID        := Null;
646         --
647         -- Assign the parameters required for calling SLA Accounting engine
648         --
649         IF l_bc_mode IN (C_FUNDS_CHECK,C_FUNDS_CHK_FULL) THEN  -- check funds draft mode
650            l_accounting_mode := 'FUNDS_CHECK';
651            psa_utils.debug_other_string(g_state_level,l_path_name,'Accounting Mode is FUNDS_CHECK');
652         ELSE
653            l_accounting_mode := 'FUNDS_RESERVE';  -- Reserve Funds in final mode
654            psa_utils.debug_other_string(g_state_level,l_path_name,'Accounting Mode is FUNDS_RESERVE');
655         END IF;
656 
657         l_accounting_flag  := 'Y'; -- Accounting required
658         l_transfer_flag    := 'N'; -- No transfer to GL
659         l_gl_posting_flag  := 'N'; -- GL Post not required
660         l_offline_flag     := 'N'; -- Calling mode for SLA online engine document mode
661 
662         --
663         -- Get the events information for which funds check required
664         -- Get the entity details ,
665         --- For each entity id SLA Accounting engine is invoked
666         --
667         l_accounting_events := FALSE;
668         x_return_status := FND_API.G_RET_STS_SUCCESS;
669         l_overall_success := TRUE;
670 
671 
672         SELECT count(*) INTO l_count
673         FROM psa_bc_xla_events_gt;
674 
675         IF l_count = 0 THEN
676           -- Bug 5474201
677           -- There are no events to be processed, returns success as such
678           -- procedure should handle such situation gracefully
679           IF (l_bc_mode = C_FUNDS_CHECK) THEN
680              x_status_code := 'XLA_NO_JOURNAL';
681           ELSE
682              x_status_code := 'SUCCESS';
683           END IF;
684           return;
685         END IF;
686 
687         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 );
688         psa_xla_error_cleanup;
689 
690 
691         BEGIN
692           l_r12_upgrade_date :=to_date( Fnd_Profile.Value_Wnps('PSA_R12_UPGRADE_DATE'), 'MM/DD/YYYY HH24:MI:SS');  -- fetch the profile value
693 
694           IF l_r12_upgrade_date IS NULL THEN
695              x_status_code := 'FATAL';
696 
697              fnd_message.set_name('PSA','PSA_XLA_NO_R12_UPG_DATE');
698              psa_xla_error('PSA_XLA_NO_R12_UPG_DATE');
699 
700              Fnd_message.set_name('PSA','PSA_BC_XLA_ERROR');
701              Fnd_Message.Set_Token('PARAM_NAME','Profile PSA: R12 Upgrade Date does not have a value');
702              Fnd_Msg_Pub.ADD;
703 
704              Raise FND_API.G_EXC_ERROR;
705           END IF;
706         EXCEPTION
707           WHEN FND_API.G_EXC_ERROR THEN
708             RAISE FND_API.G_EXC_ERROR;
709           WHEN OTHERS THEN
710              x_status_code := 'FATAL';
711 
712              fnd_message.set_name('PSA','PSA_XLA_INVALID_R12_UPG_DATE');
713              psa_xla_error('PSA_XLA_INVALID_R12_UPG_DATE');
714 
715              Fnd_message.set_name('PSA','PSA_BC_XLA_ERROR');
716              Fnd_Message.Set_Token('PARAM_NAME','Format for Value in Profile PSA: R12 Upgrade Date should be MM/DD/YYYY HH24:MI:SS');
717              Fnd_Msg_Pub.ADD;
718 
719              Raise FND_API.G_EXC_ERROR;
720         END;
721 
722 
723         ---------------------------------------------------------------
724         -- Calling PA/GMS INTEGRATION API PRIOR TO SLA ONLINE
725         ---------------------------------------------------------------
726 
727         psa_utils.debug_other_string(g_state_level,l_path_name, 'Now invoking the PA_GMS_INTEGRATION_API' );
728         pa_gms_integration_api;
729 
730         SELECT count(*) INTO l_count
731         FROM psa_bc_xla_events_gt;
732 
733         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 );
734 
735         IF l_count < 1 THEN
736 
737              x_status_code := 'FATAL';
738              Fnd_message.set_name('PSA','PSA_BC_XLA_ERROR');
739              Fnd_Message.Set_Token('PARAM_NAME','No Events to be processed');
740              Fnd_Msg_Pub.ADD;
741              Raise FND_API.G_EXC_ERROR;
742 
743         END IF;
744 
745         --
746         -- Update the PSA_BC_XLA_EVENTS_GT event records to be in 'XLA_UNPROCESSED' status
747         --
748         UPDATE psa_bc_xla_events_gt
749         SET result_code = 'XLA_UNPROCESSED';
750         psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows updated of psa_bc_xla_events_gt: ' || SQL%ROWCOUNT);
751 
752 
753 
754 
755 
756         ----------------------------------------------------------------------
757         -- clear the XLA_ACCT_PROG_EVENTS_GT table before inserting any rows
758         ----------------------------------------------------------------------
759 
760         psa_cleanup_gt;
761 
762         /* ---- 7460759 ---------------------------------------------------------------------------
763         DELETE from XLA_ACCT_PROG_EVENTS_GT;
764         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 );
765         DELETE from xla_ae_headers_gt;
766         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);
767         DELETE from xla_ae_lines_gt;
768         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);
769         DELETE from xla_validation_lines_gt;
770         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);
771         DELETE from xla_evt_class_orders_gt;
772         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);
773 */
774         DELETE from psa_option_details_gt;
775         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);
776         DELETE from psa_bc_alloc_gt;
777         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);
778     ------------------------------------------------------------------------------------- *
779 
780      -- Insert rows to XLA Events GT table and Call Accounting Engine
781      -----------------------------------------------------------------
782 
783         INSERT into XLA_ACCT_PROG_EVENTS_GT (Event_Id)
784         SELECT event_id FROM psa_bc_xla_events_gt;
785         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 );
786 
787         l_accounting_events   := TRUE;
788         l_accounting_batch_id := NULL;
789         l_errbuf              := NULL;
790         l_retcode             := NULL;
791         l_request_id          := NULL;
792 
793         psa_utils.debug_other_string(g_state_level,l_path_name, 'PSA_BC_XLA_EVENTS_GT');
794         psa_utils.debug_other_string(g_state_level,l_path_name, '=====================');
795 
796         FOR x in c_get_bc_xla_events_gt
797         LOOP
798             psa_utils.debug_other_string(g_state_level,l_path_name, 'EVENT_ID = '||x.event_id);
799             psa_utils.debug_other_string(g_state_level,l_path_name, 'RESULT_CODE = '||x.result_code);
800         END LOOP;
801 
802         ----------------------------------------------------------------
803         -- Calling SLA online accounting engine API
804         ----------------------------------------------------------------
805         psa_utils.debug_other_string(g_state_level,l_path_name, 'Calling API xla_accounting_pub_pkg.accounting_program_events');
806         xla_accounting_pub_pkg.accounting_program_events
807                     ( p_application_id        => P_application_id
808                      ,p_accounting_mode      => l_accounting_mode
809                      ,p_gl_posting_flag      => l_gl_posting_flag
810                      ,p_accounting_batch_id  => l_accounting_batch_id
811                      ,p_errbuf               => l_errbuf
812                      ,p_retcode              => l_retcode
813                      );
814 
815          psa_utils.debug_other_string(g_state_level,l_path_name,  'Return Code = ' || l_retcode);
816          psa_utils.debug_other_string(g_state_level,l_path_name,  'l_errbuf = '  ||l_errbuf );
817          psa_utils.debug_other_string(g_state_level,l_path_name,  'Accounting Batch id = '  ||l_accounting_batch_id );
818 
819          -- Get psa_bc_xla_events_gt events
820          OPEN c_get_psa_events;
821          FETCH c_get_psa_events BULK COLLECT INTO l_psa_events;
822          CLOSE c_get_psa_events;
823 
824          -- Fetch error records from XLA tables
825          OPEN c_get_xla_acctg_err;
826          FETCH c_get_xla_acctg_err BULK COLLECT INTO l_psa_acctg_errors;
827          CLOSE c_get_xla_acctg_err;
828 
829          -- Delete/Save errors records from/into PSA BC accounting errors table
830          psa_acctg_errors_insert(l_psa_events,l_psa_acctg_errors);
831 
832          IF  l_retcode = 2 THEN
833                psa_utils.debug_other_string(g_state_level,l_path_name, 'ERROR returned in SLA Accounting Engine API');
834                l_overall_success := FALSE;
835                --Fnd_message.set_name('PSA','PSA_BC_XLA_ERROR');
836                --Fnd_Message.Set_Token('PARAM_NAME',l_errbuf);
837                --Fnd_Msg_Pub.ADD;
838                -- update the psa_bc_xla_events_gt event records to
839                -- XLA_ERROR status
840                UPDATE psa_bc_xla_events_gt
841                SET result_code = 'XLA_ERROR';
842                psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows updated of psa_bc_xla_events_gt: ' || SQL%ROWCOUNT);
843                -- set the status code
844                x_status_code := 'XLA_ERROR';
845                copy_xla_error (l_error_found);
846                IF (l_error_found = 'N') THEN
847                  try_to_interpret_xla_error;
848                END IF;
849          ELSE
850                psa_utils.debug_other_string(g_state_level,l_path_name,'Events processed by SLA Accounting Engine');
851 
852                --
853                -- Update the PSA_BC_XLA_EVENTS_GT event records to be in 'XLA_NO_JOURNAL' status
854                -- for events that remain in XLA_UNPROCESSED status
855                --
856 
857                IF l_retcode = 0 THEN
858                  UPDATE psa_bc_xla_events_gt
859                  SET result_code = 'XLA_NO_JOURNAL'
860                  WHERE result_code = 'XLA_UNPROCESSED';
861                  psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows updated of psa_bc_xla_events_gt: ' || SQL%ROWCOUNT);
862                END IF;
863 
864                IF l_retcode = 1 THEN
865                   copy_xla_error (l_error_found);
866                   OPEN c_xla_errors;
867                   FETCH c_xla_errors INTO l_xla_error;
868                   CLOSE c_xla_errors;
869 
870                   IF (l_xla_error = 'Y') THEN
871                       x_status_code := 'XLA_ERROR';
872 
873                       UPDATE psa_bc_xla_events_gt
874                       SET result_code = 'XLA_ERROR';
875                       psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows updated of psa_bc_xla_events_gt: ' || SQL%ROWCOUNT);
876                   END IF;
877                END IF;
878 
879                -- Intialize status code to Success
880                x_status_code := 'SUCCESS';
881 
882                --dumping the psa_bc_xla_events_gt eventid/result_code
883                psa_utils.debug_other_string(g_state_level,l_path_name,'Dump of psa_bc_xla_events_gt');
884                FOR x IN (SELECT * FROM psa_bc_xla_events_gt) LOOP
885                    psa_utils.debug_other_string(g_state_level,l_path_name,
886                      'PSA gt event_id'|| x.event_id || 'PSA gt result_code' || x.result_code);
887                END LOOP;
888 
889                -- set the status code of the gt table
890                -- Currently only GT table is used for getting the results status
891                -- after the SLA enhancement for BCPSA and funds check code completion this
892                -- might change
893                open C_get_status_count;
894                Fetch C_get_status_count into l_status_count;
895                Close C_get_status_count;
896 
897                IF (l_status_count.status_nojournal_count > 0) THEN
898                    x_status_code := 'XLA_NO_JOURNAL';
899                    IF (l_status_count.status_success_count > 0) THEN
900                       x_status_code := 'PARTIAL';
901                    END IF;
902 
903                    FOR event_list_rec IN (SELECT *
904                                               FROM PSA_BC_XLA_EVENTS_GT
905                                              WHERE upper(result_code) = 'XLA_NO_JOURNAL') LOOP
906                      fnd_message.set_name ('PSA','PSA_XLA_NO_JOURNAL');
907                      psa_utils.debug_other_string(g_state_level,l_path_name, 'Calling psa_xla_error');
908                      psa_xla_error
909                      (
910                        p_message_code => 'PSA_XLA_NO_JOURNAL',
911                        p_event_id     => event_list_rec.event_id
912                      );
913                    END LOOP;
914                ELSIF (l_status_count.status_fatal_count > 0 ) THEN
915                    x_status_code := 'FATAL';
916                ELSIF (l_status_count.status_partial_count > 0 ) THEN
917                    x_status_code := 'PARTIAL';
918                ELSIF (l_status_count.status_xla_err_count > 0 ) THEN
919                    x_status_code := 'XLA_ERROR';
920                    IF (l_status_count.status_success_count > 0) THEN
921                       x_status_code := 'PARTIAL';
922                    END IF;
923                ELSIF (l_status_count.status_fail_count > 0 ) THEN
924                       x_status_code := 'FAIL';
925                   IF (l_bc_mode IN (C_FUNDS_CHECK, C_FUNDS_PARTIAL)) AND (l_status_count.status_success_count > 0) THEN
926                       x_status_code := 'PARTIAL';
927                   END IF;
928                ELSIF (l_status_count.status_advisory_count > 0 ) THEN
929                       x_status_code := 'ADVISORY';
930                ELSE
931                       x_status_code := 'SUCCESS';
932                END IF;  -- advisory
933 
934 
935          END IF;
936               psa_utils.debug_other_string(g_state_level,l_path_name,  'Status Code= '||x_status_code);
937          ----------------------------------------------------
938          -- packet id will returned from gl_bc_packets
939          -- if more than one event per call of BC API
940          -- first packet id will be returned
941          ------------------------------------------------------
942          x_Packet_ID:= g_packet_id;
943 
944          -----------------------------------------------------
945          -- Initialize the collection variables
946          -----------------------------------------------------
947          l_failed_evnt_array := PSA_FUNDS_CHECKER_PKG.num_rec();
948 
949          -----------------------------------------------------
950          -- Store event ids and ledger id which are used later
951          -- for roll back if CBC funds check call fails
952          -----------------------------------------------------
953          OPEN c_get_psa_events;
954          FETCH c_get_psa_events BULK COLLECT INTO l_failed_evnt_array;
955          CLOSE c_get_psa_events;
956 
957          -----------------------------------------------------
958          -- Check if CBC is enabled and
959          -- call CBC API with reserve mode or funds check mode
960          -- based on value of x_status_code. If call to CBC API
961          -- fails in reserve mode, SBC funds check changes
962          -- will also be rolled back.
963          -----------------------------------------------------
964          IF p_application_id = 201 AND IGI_GEN.is_req_installed('CBC') = TRUE THEN
965             psa_utils.debug_other_string(g_state_level,l_path_name,  'CBC Installed');
966 
967             BEGIN
968               -- Bug 12352531
969               l_prepare_stmt := 'BEGIN :1 := IGC_CBC_GL_FC_PKG.glzcbc(p_mode => :2, p_conc_proc => :3); END;';
970               psa_utils.debug_other_string(g_state_level,l_path_name, ' l_prepare_stmt -> ' || l_prepare_stmt );
971               IF (x_status_code IN('SUCCESS','ADVISORY')) THEN
972                 EXECUTE IMMEDIATE l_prepare_stmt USING OUT x_return_status, IN p_bc_mode, IN FND_API.G_FALSE;
973                 psa_utils.debug_other_string(g_state_level,l_path_name, ' x_return_status -> ' || x_return_status );
974 
975                  IF x_return_status <> 1 THEN
976                     IF x_return_status = -1 THEN
977                        x_status_code := 'XLA_ERROR';
978                     ELSIF x_return_status = 0 THEN
979                        x_status_code := 'FAIL';
980                     END IF;
981                     psa_funds_checker_pkg.sync_xla_errors(p_failed_ldgr_array => null, p_failed_evnt_array => l_failed_evnt_array);
982                  END IF;
983 
984               ELSIF x_status_code IN('FAIL','PARTIAL') THEN
985                 EXECUTE IMMEDIATE l_prepare_stmt USING OUT x_return_status, IN 'M', IN FND_API.G_FALSE;
986                 psa_utils.debug_other_string(g_state_level,l_path_name, ' x_return_status -> ' || x_return_status );
987               END IF;
988            EXCEPTION
989              WHEN OTHERS THEN
990                 psa_utils.debug_other_string(g_excep_level,l_path_name,'CBC EXCEPTION: '|| SQLERRM(sqlcode));
991                 psa_utils.debug_other_string(g_excep_level,l_path_name,'Error in budgetary_control Procedure' );
992                 RAISE;
993            END;
994 
995          END IF;
996 
997          ------------------------------------------------------
998          -- Calling PA/GMS Tieback API's in case of Errors
999          ------------------------------------------------------
1000 
1001          IF (x_status_code IN('FATAL','XLA_ERROR', 'XLA_NO_JOURNAL')) THEN
1002            psa_utils.debug_other_string(g_state_level,l_path_name,  'Calling pa_gms_tieback_api');
1003            pa_gms_tieback_api;
1004          END IF;
1005          psa_utils.debug_other_string(g_state_level,l_path_name, 'END of procedure budgetary_control ' );
1006 
1007     EXCEPTION
1008 
1009     WHEN FND_API.G_EXC_ERROR THEN
1010         x_return_status := FND_API.G_RET_STS_ERROR ;
1011       FND_MSG_PUB.count_and_get(p_encoded => FND_API.G_FALSE
1012                                 ,p_count => x_msg_count
1013                                ,p_data  => x_msg_data);
1014       psa_utils.debug_other_string(g_error_level,l_path_name,'EXCEPTION: '|| SQLERRM(sqlcode));
1015       psa_utils.debug_other_string(g_error_level,l_path_name,'Error in budgetary_control Procedure' );
1016       pa_gms_tieback_api;
1017 
1018     WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1019       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1020       psa_utils.debug_other_string(g_unexp_level,l_path_name, 'Unexpected Error'|| sqlerrm);
1021       FND_MSG_PUB.count_and_get(p_encoded => FND_API.G_FALSE
1022                                 ,p_count => x_msg_count
1023                                ,p_data  => x_msg_data);
1024 
1025      psa_utils.debug_other_string(g_unexp_level,l_path_name,'ERROR: Unexpected Error in budgetary_control Procedure' );
1026      pa_gms_tieback_api;
1027     WHEN OTHERS THEN
1028      x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1029       IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1030         FND_MSG_PUB.add_exc_msg(G_PKG_NAME, 'PSA_BC_XLA_PVT');
1031       END IF;
1032       psa_utils.debug_unexpected_msg(G_PKG_NAME);
1033        FND_MSG_PUB.count_and_get(p_encoded => FND_API.G_FALSE
1034                                 ,p_count => x_msg_count
1035                                ,p_data  => x_msg_data);
1036       psa_utils.debug_other_string(g_excep_level,l_path_name,'EXCEPTION: '|| SQLERRM(sqlcode));
1037       psa_utils.debug_other_string(g_excep_level,l_path_name,'Error in budgetary_control Procedure' );
1038       pa_gms_tieback_api;
1039 
1040 END Budgetary_Control;
1041 
1042 
1043 -- /*============================================================================
1044  -- API name     : get_sla_notupgraded_flag
1045  -- Type         : private
1046  -- Pre-reqs     : None
1047  -- Description  : Returns Y/N depending on whether the distribution passed is notupgraded
1048  --
1049  --  Parameters  :
1050  --  IN          :
1051  --                  p_application_id   IN NUMBER        Applied to Application ID
1052  --                  p_entity_code              IN VARCHAR2      Applied to Entity code
1053  --                  p_source_id_int_1  IN NUMBER        Applied to Header ID
1054  --                  p_dist_link_type   IN VARCHAR2      Applied to Dist Link Type
1055  --                  p_distribution_id  IN NUMBER        Applied to Distribution ID
1056  --
1057  --  Returns     :   VARCHAR2 i.e., Y/N
1058  --
1059  --  Logic
1060  --        - If the transaction was created in transaction tables after R12 upgrade,
1061  --             return N
1062  --        - Else
1063  --             If the distribution was accounted in xla
1064  --                 return N;
1065  --             Else
1066  --                 return Y;
1067  --
1068  --  Notes:
1069  --         This is called from transaction objects and the return value is
1070  --         populated into a column that will be mapped to Upgrade option acct attrib
1071  --         in SLA.
1072  --
1073  --  Modification History
1074  --  Date               Author             Description of Change
1075  --  27-Oct-2005    Venkatesh N             Created
1076  -- *===========================================================================
1077 FUNCTION get_sla_notupgraded_flag (     p_application_id        IN NUMBER,
1078                                         p_entity_code           IN VARCHAR2,
1079                                         p_source_id_int_1       IN NUMBER,
1080                                         p_dist_link_type        IN VARCHAR2,
1081                                         p_distribution_id   IN NUMBER) RETURN VARCHAR2 IS
1082     l_dist_creation_date    DATE;
1083     l_r12_upgrade_date      DATE;
1084     l_check_variable        VARCHAR2(1) := '0';
1085     l_return_val            VARCHAR2(1) := 'N';  -- Bug 9849939: Setting the default value to N
1086     l_acctd_cr              NUMBER;
1087     l_acctd_dr              NUMBER;
1088     l_event_type_code       xla_events.event_type_code%TYPE;
1089 
1090     l_path_name            VARCHAR2(500);
1091 
1092     CURSOR c_check( cp_appl_id NUMBER,
1093                     cp_entity_code VARCHAR2,
1094                     cp_source_id_int_1  NUMBER,
1095                     cp_source_dist_type VARCHAR2,
1096                     cp_source_dist_id_num_1 NUMBER) IS
1097             SELECT '1'
1098             FROM    xla_transaction_entities xte,
1099                     xla_ae_headers           xah,
1100                     xla_distribution_links   xdl,
1101                     xla_events               xe
1102             WHERE   xte.application_id      = cp_appl_id
1103                 AND xte.entity_code         = cp_entity_code
1104                 AND xte.source_id_int_1     = cp_source_id_int_1
1105                 AND xte.entity_id           = xah.entity_id
1106                 AND xah.event_id            = xdl.event_id
1107                 AND xdl.source_distribution_type    = cp_source_dist_type
1108                 AND xdl.source_distribution_id_num_1 = cp_source_dist_id_num_1
1109                 AND xah.event_id            = xe.event_id
1110                 AND xe.budgetary_control_flag = 'Y';
1111 
1112     --Cursor introduced for Bug 7598349
1113     CURSOR c_po_upg_chk(cp_appl_id NUMBER,
1114                         cp_entity_code VARCHAR2,
1115                         cp_source_id_int_1 NUMBER,
1116                         cp_source_dist_type VARCHAR2,
1117                         cp_source_dist_id_num_1 NUMBER) IS
1118             SELECT NVL(xdl.unrounded_accounted_dr, 0), NVL(xdl.unrounded_accounted_cr, 0),
1119                    xe.event_type_code
1120             FROM    xla_transaction_entities xte,
1121                     xla_ae_headers           xah,
1122                     xla_events               xe,
1123                     xla_distribution_links   xdl
1124             WHERE   xte.application_id      = cp_appl_id
1125                 AND xte.entity_code         = cp_entity_code
1126                 AND xte.source_id_int_1     = cp_source_id_int_1
1127                 AND xte.entity_id           = xah.entity_id
1128                 AND xah.event_id            = xe.event_id
1129                 AND xdl.ae_header_id        = xah.ae_header_id
1130                 AND xah.application_id      = cp_appl_id
1131                 AND xe.budgetary_control_flag = 'Y'
1132                 AND xdl.source_distribution_type     = cp_source_dist_type
1133                 AND xdl.source_distribution_id_num_1 = cp_source_dist_id_num_1
1134            ORDER BY xe.event_id DESC;
1135 BEGIN
1136 
1137     l_path_name := g_path_name || '.get_sla_notupgraded_flag';
1138     psa_utils.debug_other_string(g_state_level,l_path_name, 'BEGIN of function get_sla_notupgraded_flag' );
1139 
1140     psa_utils.debug_other_string(g_state_level,l_path_name,'Fetch PSA: R12 Upgrade Date profile value');
1141 
1142     l_r12_upgrade_date :=to_date( Fnd_Profile.Value_Wnps('PSA_R12_UPGRADE_DATE'), 'MM/DD/YYYY HH24:MI:SS');  -- fetch the profile value
1143 
1144     IF l_r12_upgrade_date IS NULL THEN
1145         psa_utils.debug_other_string(g_state_level,l_path_name,'Profile value does not contain a value');
1146         psa_utils.debug_other_string(g_state_level,l_path_name,'Please check whether psar12upg.sql script was run');
1147         RAISE Fnd_Api.G_Exc_Error;
1148     END IF;
1149 
1150     psa_utils.debug_other_string(g_state_level,l_path_name,'Profile value = ' || to_char(l_r12_upgrade_date));
1151 
1152     psa_utils.debug_other_string(g_state_level,l_path_name,'Fetch CREATION_DATE of the Distribution '|| p_distribution_id);
1153 
1154     IF p_dist_link_type = 'PO_REQ_DISTRIBUTIONS_ALL' THEN
1155         SELECT min(creation_date) INTO l_dist_creation_date
1156         FROM po_req_distributions_all
1157         WHERE distribution_id = p_distribution_id;
1158     ELSIF p_dist_link_type = 'PO_DISTRIBUTIONS_ALL' THEN
1159         SELECT min(creation_date) INTO l_dist_creation_date
1160        FROM po_distributions_all
1161         WHERE po_distribution_id = p_distribution_id;
1162     ELSIF p_dist_link_type = 'AP_INV_DIST' THEN
1163         SELECT min(creation_date) INTO l_dist_creation_date
1164         FROM ap_invoice_distributions_all
1165         WHERE invoice_distribution_id = p_distribution_id;
1166     ELSE
1167         psa_utils.debug_other_string(g_state_level,l_path_name,'Invalid Distribution Link Type'|| p_dist_link_type);
1168         RAISE Fnd_Api.G_Exc_Error;
1169     END IF;
1170 
1171     psa_utils.debug_other_string(g_state_level,l_path_name,'Distribution CREATION_DATE = ' || to_char(l_dist_creation_date));
1172 
1173     IF (p_dist_link_type <>  'PO_DISTRIBUTIONS_ALL') then
1174 	    if (l_dist_creation_date > l_r12_upgrade_date) OR (l_dist_creation_date IS NULL) THEN
1175            l_return_val := 'N';
1176 		End if;
1177     END IF;
1178 	--IF (p_dist_link_type ='PO_DISTRIBUTIONS_ALL' OR l_dist_creation_date <= l_r12_upgrade_date) THEN
1179 	IF (l_dist_creation_date <= l_r12_upgrade_date) THEN
1180         OPEN c_check(p_application_id,
1181                     p_entity_code,
1182                     p_source_id_int_1,
1183                     p_dist_link_type,
1184                     p_distribution_id);
1185         FETCH c_check INTO l_check_variable;
1186         CLOSE c_check;
1187         psa_utils.debug_other_string(g_state_level,l_path_name,'l_check_variable = ' || l_check_variable);
1188         IF l_check_variable ='1' THEN
1189            -- Following IF added for Bug 7598349
1190            IF (p_dist_link_type = 'PO_DISTRIBUTIONS_ALL' ) THEN
1191               OPEN c_po_upg_chk(p_application_id,
1192                                  p_entity_code,
1193                                  p_source_id_int_1,
1194                                  p_dist_link_type,
1195                                  p_distribution_id);
1196                FETCH c_po_upg_chk into l_acctd_dr, l_acctd_cr, l_event_type_code;
1197                CLOSE c_po_upg_chk;
1198                psa_utils.debug_other_string(g_state_level,l_path_name,'l_acctd_dr = ' || l_acctd_dr
1199                                                                    || 'l_acctd_cr = ' || l_acctd_cr
1200                                                                    || 'l_event_type_code = ' || l_event_type_code
1201                                                                    );
1202                IF (l_acctd_dr = 0) AND (l_acctd_cr = 0) AND (l_event_type_code = 'PO_PA_FINAL_CLOSED')then
1203                   l_return_val := 'Y';
1204                ELSE
1205                   l_return_val := 'N';
1206                END IF;
1207            ELSE
1208                l_return_val := 'N';    --this means data exists in sla and is a R12 entry
1209            END IF; -- p_dist_link_type = 'PO_DISTRIBUTIONS_ALL' inner
1210         ELSE   -- if l_check_variable
1211             IF FV_INSTALL.ENABLED THEN
1212                 l_return_val := 'O';    --Make use of Upgrade tab in JLD form
1213             ELSE
1214                 l_return_val := 'Y';            --this means data exists in 11i only
1215             END IF;
1216         END IF; -- if l_check_variable
1217 	END IF;  --  if P_dist_link_type outer
1218 
1219     psa_utils.debug_other_string(g_state_level,l_path_name,'Return Value = ' || l_return_val);
1220     psa_utils.debug_other_string(g_state_level,l_path_name,'END of function get_sla_notupgraded_flag' || l_path_name);
1221     RETURN l_return_val;
1222 EXCEPTION
1223         WHEN others THEN
1224               psa_utils.debug_other_string(g_excep_level,l_path_name,'Error in function get_sla_notupgraded_flag' );
1225               RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1226 END get_sla_notupgraded_flag;
1227 
1228 -- PA_GMS_INTEGRATION_API
1229 -- Created By : Tushar Pradhan
1230 -- Description: PA/GMS teams provided us with their APIs which we should invoke prior to calling the
1231 --              XLA accounting package. These integration API's are invoked using this API.
1232 --              Since the requirement demands that XLA accounting should not be invoked if this API fails,
1233 --              the same is incorporated.
1234 --
1235 
1236 PROCEDURE pa_gms_integration_api IS
1237 
1238   l_path_name         VARCHAR2(500);
1239   l_partial_resv_flag VARCHAR2(1);
1240   l_pa_status         VARCHAR2(1);
1241   l_pa_enabled        INTEGER;
1242   l_gms_status        VARCHAR2(1);
1243   l_gms_enabled       INTEGER;
1244   l_prepare_stmt      VARCHAR2(2000);
1245   l_ret_code          VARCHAR2(100);
1246   l_bc_mode           VARCHAR2(1);
1247   l_industry          fnd_profile_option_values.profile_option_value%TYPE;
1248 
1249 BEGIN
1250 
1251   l_path_name := g_path_name||'.pa_gms_integration_api';
1252 
1253   psa_utils.debug_other_string(g_state_level,l_path_name, 'pa_gms_integration_api invoked');
1254   psa_utils.debug_other_string(g_state_level,l_path_name, 'Invoke PA Integration API if PA is enabled');
1255 
1256   IF g_bc_mode ='C' THEN
1257      l_partial_resv_flag := 'Y';
1258      l_bc_mode := 'C';
1259   ELSIF g_bc_mode = 'P' THEN
1260      l_partial_resv_flag := 'Y';
1261      l_bc_mode := 'R';
1262   ELSE
1263      l_bc_mode := g_bc_mode;
1264      l_partial_resv_flag := 'N';
1265   END IF;
1266 
1267   psa_utils.debug_other_string(g_state_level,l_path_name, ' l_partial_resv_flag -> '||l_partial_resv_flag);
1268 
1269   BEGIN
1270      l_industry     := NULL;
1271      l_prepare_stmt := NULL;
1272      l_pa_status    := 'N';
1273 
1274        IF FND_INSTALLATION.GET(275, 275, l_pa_status, l_industry) THEN
1275 
1276           IF l_pa_status ='I' THEN
1277 
1278             l_pa_enabled := 0;
1279             l_prepare_stmt := 'BEGIN IF PA_BUDGET_FUND_PKG.IS_PA_BC_ENABLED() THEN'||' :1 := 1; END IF; END;';
1280 
1281             psa_utils.debug_other_string(g_state_level,l_path_name, ' Statement prepared -> '||l_prepare_stmt);
1282 
1283             EXECUTE IMMEDIATE l_prepare_stmt USING OUT l_pa_enabled;
1284 
1285             psa_utils.debug_other_string(g_state_level,l_path_name, ' l_pa_enabled -> '||l_pa_enabled);
1286 
1287             IF l_pa_enabled = 1 THEN
1288 
1289                l_prepare_stmt := ' BEGIN '||
1290                                  ' PA_FUNDS_CONTROL_PKG1.CREATE_PROJ_ENCUMBRANCE_EVENTS ('||
1291                                  ' :application_id, :partial_resv_flag, :bc_mode, :ret_code); '||
1292                                  ' END; ';
1293 
1294                EXECUTE IMMEDIATE l_prepare_stmt USING IN g_application_id,
1295                                                       IN l_partial_resv_flag,
1296                                                       IN l_bc_mode,
1297                                                       OUT l_ret_code;
1298                IF (l_ret_code = 'F') THEN
1299                   psa_utils.debug_other_string(g_error_level,l_path_name, 'PA Integration API Failed');
1300                   FND_MESSAGE.SET_NAME('PA', 'PA_BC_FUND_CHK_FAIL');
1301                   FND_MSG_PUB.ADD;
1302                   RAISE FND_API.G_EXC_ERROR;
1303                ELSE
1304                   psa_utils.debug_other_string(g_state_level,l_path_name, 'PA Integration API Successful');
1305                END IF;
1306 
1307             END IF;
1308           END IF;
1309        END IF;
1310 
1311   END;
1312 
1313   psa_utils.debug_other_string(g_state_level,l_path_name, 'Invoke GMS Integration API if GMS is enabled');
1314   l_ret_code := NULL;
1315 
1316   BEGIN
1317      l_industry     := NULL;
1318      l_prepare_stmt := NULL;
1319      l_gms_status    := 'N';
1320 
1321        IF FND_INSTALLATION.GET(8402, 8402, l_gms_status, l_industry) THEN
1322 
1323           IF l_gms_status ='I' THEN
1324 
1325             l_gms_enabled := 0;
1326             l_prepare_stmt := 'BEGIN IF GMS_INSTALL.ENABLED() THEN'||' :1 := 1; END IF; END;';
1327 
1328             psa_utils.debug_other_string(g_state_level,l_path_name, ' l_prepare_stmt -> '||l_prepare_stmt);
1329 
1330             EXECUTE IMMEDIATE l_prepare_stmt USING OUT l_gms_enabled;
1331 
1332             psa_utils.debug_other_string(g_state_level,l_path_name, ' l_gms_enabled -> '||l_gms_enabled);
1333 
1334             IF l_gms_enabled = 1 THEN
1335 
1336             l_prepare_stmt :=    ' BEGIN '||
1337                                  ' GMS_FUNDS_CONTROL_PKG.COPY_GL_PKT_TO_GMS_PKT ( '||
1338                                  ' :application_id, :mode, :partial_resv_flag, :ret_code); '||
1339                                  ' END; ';
1340              psa_utils.debug_other_string(g_state_level,l_path_name, ' l_prepare_stmt -> '||l_prepare_stmt);
1341              EXECUTE IMMEDIATE l_prepare_stmt USING   IN g_application_id,
1342                                                       IN l_bc_mode,
1343                                                       IN l_partial_resv_flag,
1344                                                       OUT l_ret_code;
1345                IF (l_ret_code = 'F') THEN
1346                   psa_utils.debug_other_string(g_state_level,l_path_name, 'GMS Integration API Failed');
1347                   FND_MESSAGE.SET_NAME('GMS', 'GMS_FUNDS_CHECK_FAILED');
1348                   FND_MSG_PUB.ADD;
1349                   RAISE FND_API.G_EXC_ERROR;
1350                ELSE
1351                   psa_utils.debug_other_string(g_state_level,l_path_name, 'GMS Integration API Successful');
1352                END IF;
1353 
1354             END IF;
1355           END IF;
1356        END IF;
1357 
1358   END;
1359   EXCEPTION
1360             WHEN others THEN
1361                   psa_utils.debug_other_string(g_excep_level,l_path_name,'Error in pa_gms_integration_api' );
1362                   RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1363 
1364 END pa_gms_integration_api;
1365 
1366 PROCEDURE pa_gms_tieback_api IS
1367 
1368   l_path_name         VARCHAR2(500);
1369   l_pa_status         VARCHAR2(1);
1370   l_pa_enabled        INTEGER;
1371   l_gms_status        VARCHAR2(1);
1372   l_gms_enabled       INTEGER;
1373   l_prepare_stmt      VARCHAR2(2000);
1374   l_industry          fnd_profile_option_values.profile_option_value%TYPE;
1375   l_bc_mode           VARCHAR2(1);
1376 
1377 BEGIN
1378   l_path_name := g_path_name||'.pa_gms_tieback_api';
1379   psa_utils.debug_other_string(g_state_level,l_path_name, 'PA_GMS_TIEBACK_API Invoked');
1380 
1381   psa_utils.debug_other_string(g_state_level,l_path_name, 'Invoke PA Tieback API if PA is enabled');
1382 
1383   IF g_bc_mode = 'C' THEN
1384    l_bc_mode := 'C';
1385   ELSIF g_bc_mode ='P' THEN
1386    l_bc_mode := 'R';
1387   ELSE
1388    l_bc_mode := g_bc_mode;
1389    END IF;
1390 
1391   BEGIN
1392      l_industry     := NULL;
1393      l_prepare_stmt := NULL;
1394      l_pa_status    := 'N';
1395 
1396      IF FND_INSTALLATION.GET(275, 275, l_pa_status, l_industry) THEN
1397 
1398           IF l_pa_status ='I' THEN
1399 
1400             l_pa_enabled := 0;
1401             l_prepare_stmt := 'BEGIN IF PA_BUDGET_FUND_PKG.IS_PA_BC_ENABLED() THEN'||' :1 := 1; END IF; END;';
1402 
1403             psa_utils.debug_other_string(g_state_level,l_path_name, ' l_prepare_stmt -> '||l_prepare_stmt);
1404 
1405             EXECUTE IMMEDIATE l_prepare_stmt USING OUT l_pa_enabled;
1406 
1407             psa_utils.debug_other_string(g_state_level,l_path_name, ' l_pa_enabled -> '||l_pa_enabled);
1408 
1409             IF l_pa_enabled = 1 THEN
1410 
1411                l_prepare_stmt := ' BEGIN '||
1412                                  ' PA_FUNDS_CONTROL_PKG1.TIEBACK_FAILED_ACCT_STATUS( '||':bc_mode );'||
1413                                  ' END; ';
1414                psa_utils.debug_other_string(g_state_level,l_path_name, ' l_pa_enabled -> '||l_pa_enabled);
1415                EXECUTE IMMEDIATE l_prepare_stmt USING IN l_bc_mode;
1416 
1417                psa_utils.debug_other_string(g_state_level,l_path_name, 'PA Tieback API Successful');
1418 
1419             END IF;
1420           END IF;
1421        END IF;
1422 
1423   END;
1424 
1425   psa_utils.debug_other_string(g_state_level,l_path_name, 'Invoke GMS Tieback API if GMS is enabled');
1426 
1427   BEGIN
1428      l_industry     := NULL;
1429      l_prepare_stmt := NULL;
1430      l_gms_status    := 'N';
1431 
1432        IF FND_INSTALLATION.GET(8402, 8402, l_gms_status, l_industry) THEN
1433 
1434           IF l_gms_status ='I' THEN
1435 
1436             l_gms_enabled := 0;
1437             l_prepare_stmt := 'BEGIN IF GMS_INSTALL.ENABLED() THEN'||' :1 := 1; END IF; END;';
1438 
1439             psa_utils.debug_other_string(g_state_level,l_path_name, ' l_prepare_stmt -> '||l_prepare_stmt);
1440 
1441             EXECUTE IMMEDIATE l_prepare_stmt USING OUT l_gms_enabled;
1442 
1443             psa_utils.debug_other_string(g_state_level,l_path_name, ' l_gms_enabled -> '||l_gms_enabled);
1444 
1445             IF l_gms_enabled = 1 THEN
1446 
1447                l_prepare_stmt := ' BEGIN '||
1448                                  ' GMS_FUNDS_CONTROL_PKG.TIEBACK_FAILED_ACCT_STATUS( '||':bc_mode );'||
1449                                  ' END; ';
1450                psa_utils.debug_other_string(g_state_level,l_path_name, ' l_prepare_stmt -> '||l_prepare_stmt);
1451                EXECUTE IMMEDIATE l_prepare_stmt USING IN l_bc_mode;
1452 
1453                psa_utils.debug_other_string(g_state_level,l_path_name, 'GMS Tieback API Successful');
1454 
1455             END IF;
1456           END IF;
1457        END IF;
1458 
1459   END;
1460   EXCEPTION
1461      WHEN others THEN
1462      psa_utils.debug_other_string(g_excep_level,l_path_name,'Error in pa_gms_tieback_api' );
1463      RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1464 
1465 END pa_gms_tieback_api;
1466 
1467 -----------------------------------------
1468 
1469 procedure psa_cleanup_gt IS
1470 l_path_name varchar2(300) ;
1471 BEGIN
1472 l_path_name := g_path_name || '.psa_cleanup_gt';
1473 
1474   psa_utils.debug_other_string(g_state_level,l_path_name,'Cleaning up xla GT Tables');
1475   DELETE FROM XLA_AE_HEADERS_GT;
1476     psa_utils.debug_other_string(g_state_level,l_path_name, '1 XLA_AE_HEADERS_GT : Deleted Row count :'||SQL%ROWCOUNT);
1477   DELETE FROM XLA_AE_LINES_GT;
1478     psa_utils.debug_other_string(g_state_level,l_path_name, '2 XLA_AE_LINES_GT : Deleted Row count :'||SQL%ROWCOUNT);
1479   DELETE FROM XLA_VALIDATION_HDRS_GT;
1480 psa_utils.debug_other_string(g_state_level,l_path_name, '3 XLA_VALIDATION_HDRS_GT    : Deleted Row count :'||SQL%ROWCOUNT);
1481   DELETE FROM XLA_VALIDATION_LINES_GT;
1482     psa_utils.debug_other_string(g_state_level,l_path_name, '4 XLA_VALIDATION_LINES_GT    : Deleted Row count :'||SQL%ROWCOUNT);
1483   DELETE FROM XLA_BAL_CTRL_CTRBS_GT;
1484 psa_utils.debug_other_string(g_state_level,l_path_name, '5 XLA_BAL_CTRL_CTRBS_GT    : Deleted Row count :'||SQL%ROWCOUNT);
1485   DELETE FROM XLA_BAL_PERIOD_STATS_GT;
1486     psa_utils.debug_other_string(g_state_level,l_path_name, '6 XLA_BAL_PERIOD_STATS_GT    : Deleted Row count :'||SQL%ROWCOUNT);
1487   DELETE FROM XLA_BAL_RECREATE_GT;
1488     psa_utils.debug_other_string(g_state_level,l_path_name, '7 XLA_BAL_RECREATE_GT    : Deleted Row count :'||SQL%ROWCOUNT);
1489   DELETE FROM XLA_BAL_ANACRI_LINES_GT;
1490     psa_utils.debug_other_string(g_state_level,l_path_name, '8 XLA_BAL_ANACRI_LINES_GT    : Deleted Row count :'||SQL%ROWCOUNT);
1491   DELETE FROM XLA_BAL_ANACRI_CTRBS_GT;
1492     psa_utils.debug_other_string(g_state_level,l_path_name, '9 XLA_BAL_ANACRI_CTRBS_GT    : Deleted Row count :'||SQL%ROWCOUNT);
1493   DELETE FROM XLA_BAL_SYNCHRONIZE_GT;
1494     psa_utils.debug_other_string(g_state_level,l_path_name, '10 XLA_BAL_SYNCHRONIZE_GT    : Deleted Row count :'||SQL%ROWCOUNT);
1495   DELETE FROM XLA_BAL_STATUSES_GT;
1496     psa_utils.debug_other_string(g_state_level,l_path_name, '11 XLA_BAL_STATUSES_GT    : Deleted Row count :'||SQL%ROWCOUNT);
1497   DELETE FROM XLA_BAL_CTRL_LINES_GT;
1498     psa_utils.debug_other_string(g_state_level,l_path_name, '12 XLA_BAL_CTRL_LINES_GT    : Deleted Row count :'||SQL%ROWCOUNT);
1499   DELETE FROM XLA_EVENTS_GT;
1500     psa_utils.debug_other_string(g_state_level,l_path_name, '13 XLA_EVENTS_GT : Deleted Row count :'||SQL%ROWCOUNT);
1501   DELETE FROM XLA_EVT_CLASS_SOURCES_GT;
1502     psa_utils.debug_other_string(g_state_level,l_path_name, '14 XLA_EVT_CLASS_SOURCES_GT    : Deleted Row count :'||SQL%ROWCOUNT);
1503   DELETE FROM XLA_EVT_CLASS_ORDERS_GT;
1504     psa_utils.debug_other_string(g_state_level,l_path_name, '15 XLA_EVT_CLASS_ORDERS_GT    : Deleted Row count :'||SQL%ROWCOUNT);
1505   DELETE FROM XLA_TAB_ERRORS_GT;
1506     psa_utils.debug_other_string(g_state_level,l_path_name, '16 XLA_TAB_ERRORS_GT    : Deleted Row count :'||SQL%ROWCOUNT);
1507   DELETE FROM XLA_SEQ_JE_HEADERS_GT;
1508     psa_utils.debug_other_string(g_state_level,l_path_name, '17 XLA_SEQ_JE_HEADERS_GT    : Deleted Row count :'||SQL%ROWCOUNT);
1509   DELETE FROM XLA_TAB_NEW_CCIDS_GT;
1510     psa_utils.debug_other_string(g_state_level,l_path_name, '18 XLA_TAB_NEW_CCIDS_GT    : Deleted Row count :'||SQL%ROWCOUNT);
1511   DELETE FROM XLA_EXTRACT_OBJECTS_GT;
1512     psa_utils.debug_other_string(g_state_level,l_path_name, '19 XLA_EXTRACT_OBJECTS_GT    : Deleted Row count :'||SQL%ROWCOUNT);
1513   DELETE FROM XLA_REFERENCE_OBJECTS_GT;
1514     psa_utils.debug_other_string(g_state_level,l_path_name, '20 XLA_REFERENCE_OBJECTS_GT    : Deleted Row count :'||SQL%ROWCOUNT);
1515   DELETE FROM XLA_TRANSACTION_ACCTS_GT;
1516     psa_utils.debug_other_string(g_state_level,l_path_name, '21 XLA_TRANSACTION_ACCTS_GT    : Deleted Row count :'||SQL%ROWCOUNT);
1517   DELETE FROM XLA_UPG_LINE_CRITERIA_GT;
1518     psa_utils.debug_other_string(g_state_level,l_path_name, '22 XLA_UPG_LINE_CRITERIA_GT    : Deleted Row count :'||SQL%ROWCOUNT);
1519   DELETE FROM XLA_TRIAL_BALANCES_GT;
1520     psa_utils.debug_other_string(g_state_level,l_path_name, '23 XLA_TRIAL_BALANCES_GT    : Deleted Row count :'||SQL%ROWCOUNT);
1521   DELETE FROM XLA_ACCT_PROG_EVENTS_GT; psa_utils.debug_other_string(g_state_level,l_path_name, '24 XLA_ACCT_PROG_EVENTS_GT    : Deleted Row count :'||SQL%ROWCOUNT);
1522   DELETE FROM XLA_ACCT_PROG_DOCS_GT;
1523     psa_utils.debug_other_string(g_state_level,l_path_name, '25 XLA_ACCT_PROG_DOCS_GT    : Deleted Row count :'||SQL%ROWCOUNT);
1524   DELETE FROM XLA_MERGE_SEG_MAPS_GT;
1525     psa_utils.debug_other_string(g_state_level,l_path_name, '26 XLA_MERGE_SEG_MAPS_GT    : Deleted Row count :'||SQL%ROWCOUNT);
1526   DELETE FROM XLA_EVENTS_INT_GT;
1527     psa_utils.debug_other_string(g_state_level,l_path_name, '27 XLA_EVENTS_INT_GT    : Deleted Row count :'||SQL%ROWCOUNT);
1528   DELETE FROM XLA_REPORT_BALANCES_GT;
1529     psa_utils.debug_other_string(g_state_level,l_path_name, '28 XLA_REPORT_BALANCES_GT    : Deleted Row count :'||SQL%ROWCOUNT);
1530   DELETE FROM XLA_TB_BALANCES_GT;
1531     psa_utils.debug_other_string(g_state_level,l_path_name, '29 XLA_TB_BALANCES_GT    : Deleted Row count :'||SQL%ROWCOUNT);
1532   DELETE FROM XLA_BAL_AC_CTRBS_GT;
1533     psa_utils.debug_other_string(g_state_level,l_path_name, '30 XLA_BAL_AC_CTRBS_GT    : Deleted Row count :'||SQL%ROWCOUNT);
1534   psa_utils.debug_other_string(g_state_level,l_path_name,'clean_xla_gt -');
1535 END psa_cleanup_gt;
1536 
1537 
1538 ----------------------------------------------------- --------------------------------------------------
1539 BEGIN
1540          g_log_enabled    := fnd_log.test
1541                             (log_level  => FND_LOG.G_CURRENT_RUNTIME_LEVEL
1542                             ,MODULE     => g_path_name);
1543 init;
1544 END PSA_BC_XLA_PVT;