DBA Data[Home] [Help]

PACKAGE BODY: APPS.XLA_UPDATE_INVAL_CCID

Source


1 PACKAGE BODY XLA_UPDATE_INVAL_CCID AS
2 /*$Header: xlaudccid.pkb 120.2 2010/11/23 07:14:08 vkasina noship $
3 ============================================================================+
4 |             COPYRIGHT (C) 2001-2002 ORACLE CORPORATION                     |
5 |                       REDWOOD SHORES, CA, USA                              |
6 |                         ALL RIGHTS RESERVED.                               |
7 +============================================================================+
8 | PACKAGE NAME                                                               |
9 |     xla_update_inval_ccid                                                  |
10 |                                                                            |
11 | DESCRIPTION                                                                |
12 |     PACKAGE BODY FOR Update Invalid CCIDS program                          |
13 |     This Api Will be called from the Java Layer once the BPEL              |
14 |     returns the invalid ccids java cp will call this API to Update         |
15 |     Accounting Entries with the invalid status                             |
16 |                                                                            |
17 | HISTORY                                                                    |
18 |     04/08/2008    Jagan Koduri         CREATED                             |
19 +===========================================================================*/
20 
21    -------------------------------------------------------------------------------
22 --               *********** LOCAL TRACE ROUTINE **********
23 -------------------------------------------------------------------------------
24    c_level_statement      CONSTANT NUMBER         := fnd_log.level_statement;
25    c_level_procedure      CONSTANT NUMBER         := fnd_log.level_procedure;
26    c_level_event          CONSTANT NUMBER         := fnd_log.level_event;
30    c_level_log_disabled   CONSTANT NUMBER         := 99;
27    c_level_exception      CONSTANT NUMBER         := fnd_log.level_exception;
28    c_level_error          CONSTANT NUMBER         := fnd_log.level_error;
29    c_level_unexpected     CONSTANT NUMBER         := fnd_log.level_unexpected;
31    c_default_module       CONSTANT VARCHAR2 (240)
32                                          := 'XLA.PLSQL.XLA_UPDATE_INVAL_CCID';
33    g_log_level                     NUMBER;
34    g_log_enabled                   BOOLEAN;
35    c_log_size             CONSTANT NUMBER         := 2000;
36 
37    PROCEDURE TRACE (
38       p_msg      IN   VARCHAR2,
39       p_level    IN   NUMBER,
40       p_module   IN   VARCHAR2 DEFAULT c_default_module
41    )
42    IS
43       l_max   NUMBER;
44       l_pos   NUMBER := 1;
45    BEGIN
46       l_pos := 1;
47 
48       IF (p_msg IS NULL AND p_level >= g_log_level)
49       THEN
50          fnd_log.MESSAGE (p_level, p_module);
51       ELSIF p_level >= g_log_level
52       THEN
53          l_max := LENGTH (p_msg);
54 
55          IF l_max <= c_log_size
56          THEN
57             fnd_log.STRING (p_level, p_module, p_msg);
58          ELSE
59             WHILE (l_pos - 1) * c_log_size <= l_max
60             LOOP
61                fnd_log.STRING (p_level,
62                                p_module,
63                                SUBSTR (p_msg,
64                                          (l_pos - 1) * c_log_size + 1,
65                                        c_log_size
66                                       )
67                               );
68                l_pos := l_pos + 1;
69             END LOOP;
70          END IF;
71       END IF;
72    EXCEPTION
73       WHEN xla_exceptions_pkg.application_exception
74       THEN
75          RAISE;
76       WHEN OTHERS
77       THEN
78          xla_exceptions_pkg.raise_message (p_location      => 'xla_update_inval_ccid.trace'
79                                           );
80    END TRACE;
81 
82 -------------------------------------------------------------------------------
83 --                   ******* Print Log File **********
84 -------------------------------------------------------------------------------
85    PROCEDURE print_logfile (p_msg IN VARCHAR2)
86    IS
87    BEGIN
88       fnd_file.put_line (fnd_file.LOG, p_msg);
89    EXCEPTION
90       WHEN xla_exceptions_pkg.application_exception
91       THEN
92          RAISE;
93       WHEN OTHERS
94       THEN
95          xla_exceptions_pkg.raise_message (p_location      => 'xla_update_inval_ccid.print_logfile'
96                                           );
97    END print_logfile;
98 
99 -------------------------------------------------------------------------------
100 --                   ******* Print Log File **********
101 -------------------------------------------------------------------------------
102    PROCEDURE xla_update_inval_ccid_api (
103       p_accounting_batch_id   IN   NUMBER,
104       p_ledger_id             IN   NUMBER,
105       p_application_id        IN   NUMBER,
106       p_ccid                  IN   t_ccid_table,
107       p_status                IN   NUMBER,
108       p_err_msg               IN   VARCHAR2
109    )
110    AS
111       TYPE t_ae_header_id IS TABLE OF xla_ae_headers.ae_header_id%TYPE;
112 
113       TYPE t_event_id IS TABLE OF xla_ae_headers.event_id%TYPE;
114 
115       l_ae_header_id            t_ae_header_id;
116       l_ccid                    t_ccid_table;
117       l_event_id                t_event_id;
118       l_log_module              VARCHAR2 (240);
119       l_entity_id               NUMBER;
120       l_e_header_id             NUMBER;
121       l_vent_id                 NUMBER;
122       l_err_msg                 VARCHAR2 (3000);
123       --for sus
124       l_suspense_allowed_flag   VARCHAR2 (3);
125       l_suspense_ccid           NUMBER;
126       l_bal_seg_column_name     VARCHAR2 (100);
127       l_ledger_id               NUMBER;
128       l_ledger_name             VARCHAR2 (100);
129       l_found                   BOOLEAN                   := FALSE;
130       l_found_sus               BOOLEAN                   := FALSE;
131       l_found_invalid           BOOLEAN                   := FALSE;
132       l_segments_sus            fnd_flex_ext.segmentarray;
133       l_segments_invalid        fnd_flex_ext.segmentarray;
134       l_numofsegments           NUMBER;
135       l_balancesegnum           NUMBER;
136       l_structnum               NUMBER;
137       l_invalid_ccid            NUMBER;
138       p_new_ccid                NUMBER;
139    --for sus
140    BEGIN
141       print_logfile (   TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
142                      || ' - Starting of the Update Invalid CCID Information'
143                     );
144 
145 print_logfile ('p_status '||p_status);
146 
147       IF g_log_enabled
148       THEN
149          l_log_module := c_default_module || 'xla_update_inval_ccid_api';
150       END IF;
151 
152       IF (c_level_procedure >= g_log_level)
153       THEN
154          TRACE (p_msg         => 'BEGIN of procedure XLA_UPDATE_INVAL_CCID',
155                 p_level       => c_level_procedure,
156                 p_module      => l_log_module
157                );
158          TRACE (p_msg         =>    'p_application_id = '
159                                  || TO_CHAR (p_application_id),
160                 p_level       => c_level_procedure,
161                 p_module      => l_log_module
162                );
166                );
163          TRACE (p_msg         => 'p_ledger_id = ' || p_ledger_id,
164                 p_level       => c_level_procedure,
165                 p_module      => l_log_module
167          TRACE (p_msg         =>    'p_accounting_batch_id = '
168                                  || p_accounting_batch_id,
169                 p_level       => c_level_procedure,
170                 p_module      => l_log_module
171                );
172       END IF;
173 
174       l_ccid := p_ccid;
175 
176       -- Finding the info weather the suspense accounting is allowed for this ledger or not
177       SELECT suspense_allowed_flag, suspense_ccid, bal_seg_column_name,
178              ledger_id, chart_of_accounts_id
179         INTO l_suspense_allowed_flag, l_suspense_ccid, l_bal_seg_column_name,
180              l_ledger_id, l_structnum
181         FROM gl_ledgers_v
182        WHERE ledger_id = p_ledger_id;
183 
184       l_invalid_ccid := l_ccid (1);
185 
186       print_logfile ('l_suspense_allowed_flag    ' || l_suspense_allowed_flag);
187       print_logfile ('l_suspense_ccid            ' || l_suspense_ccid);
188       print_logfile ('l_bal_seg_column_name      ' || l_bal_seg_column_name);
189       print_logfile ('l_ledger_name              ' || l_ledger_name);
190       print_logfile ('l_structnum                ' || l_structnum);
191       print_logfile ('l_numofsegments            ' || l_numofsegments);
192 
193       IF l_suspense_allowed_flag = 'Y' AND l_suspense_ccid IS NOT NULL
194       THEN
195          -- confirming that suspense account is exist
196          print_logfile ('Inside Suspense Loop ');
197          l_found_sus :=
198             fnd_flex_ext.get_segments ('SQLGL',
199                                        'GL#',
200                                        l_structnum,
201                                        l_suspense_ccid,
202                                        l_numofsegments,
203                                        l_segments_sus
204                                       );
205          print_logfile ('l_numofsegments l_found_sus ' || l_numofsegments);
206          print_logfile ('l_ccid.first ' || l_ccid.FIRST);
207          l_found_invalid :=
208             fnd_flex_ext.get_segments ('SQLGL',
209                                        'GL#',
210                                        l_structnum,
211                                        l_invalid_ccid,
212                                        l_numofsegments,
213                                        l_segments_invalid
214                                       );
215          print_logfile ('l_numofsegments l_found_invalid ' || l_numofsegments);
216 
217          --print_logfile (  'l_found_sus '||l_found_sus);
218          --print_logfile (  'l_found_invalid '||l_found_invalid);
219 
220 
221          IF l_found_sus AND l_found_invalid
222          THEN
223             print_logfile ('Getting the balancingsegment number');
224             -- getting the balancing segment number
225 
226 
227             l_found :=
228                fnd_flex_apis.get_qualifier_segnum (101,
229                                                    'GL#',
230                                                    l_structnum,
231                                                    'GL_BALANCING',
232                                                    l_balancesegnum
233                                                   );
234             print_logfile ('l_balancesegnum' || l_balancesegnum);
235 
236             IF l_segments_sus (l_balancesegnum) =
237                                           l_segments_invalid (l_balancesegnum)
238             THEN
239                print_logfile ('Balancing segments are equal ');
240                p_new_ccid := l_suspense_ccid;
241             ELSE
242                print_logfile ('Balancing segments are not equal generating new CCID with the combination'
243                              );
244                l_segments_sus (l_balancesegnum) :=
245                                           l_segments_invalid (l_balancesegnum);
246                print_logfile (   l_segments_sus (l_balancesegnum)
247                               || 'New balancing segment for getting the new ccid'
248                              );
249                l_found :=
250                   fnd_flex_ext.get_combination_id ('SQLGL',
251                                                    'GL#',
252                                                    l_structnum,
253                                                    SYSDATE,
254                                                    l_numofsegments,
255                                                    l_segments_sus,
256                                                    p_new_ccid
257                                                   );
258                print_logfile (p_new_ccid || ' p_new_ccid');
259             END IF;
260          END IF;
261 
262 -- Updateing with the new ccid
263          print_logfile (p_new_ccid || ' p_new_ccid');
264          FORALL i IN l_ccid.FIRST .. l_ccid.LAST
265             UPDATE xla_ae_lines xal
266                SET xal.code_combination_id = p_new_ccid
267              WHERE application_id = p_application_id
268                AND ae_header_id IN (
269                       SELECT ae_header_id
270                         FROM xla_ae_headers
271                        WHERE accounting_batch_id = p_accounting_batch_id
272                          AND application_id = p_application_id
273                          AND ledger_id = p_ledger_id)
274                AND code_combination_id = l_ccid (i);
275          print_logfile (SQL%ROWCOUNT ||'No. Records Updated');
276          COMMIT;
277       ELSE
278          print_logfile (   TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
282                         || p_ccid.COUNT
279                         || ' - Starting of Update Invalid CCID '
280                        );
281          print_logfile (   'No. of Distinct CCID required to update  '
283                        );
284 
285          IF p_ccid.COUNT > 0
286          THEN
287             print_logfile (':::CCIDs :::');
288 
289             FOR i IN l_ccid.FIRST .. l_ccid.LAST
290             LOOP
291                print_logfile (l_ccid (i));
292             END LOOP;
293 
294             FORALL i IN l_ccid.FIRST .. l_ccid.LAST
295                UPDATE    xla_ae_lines xal
296                      SET xal.code_combination_id = p_status -- -2
297                    WHERE application_id = p_application_id
298                      AND ae_header_id IN (
299                             SELECT ae_header_id
300                               FROM xla_ae_headers
301                              WHERE accounting_batch_id = p_accounting_batch_id
302                                AND application_id = p_application_id
303                                AND ledger_id = p_ledger_id)
304                      AND code_combination_id = l_ccid (i)
305                RETURNING         ae_header_id
306                BULK COLLECT INTO l_ae_header_id;
307             print_logfile ('No. of xla_ae_lines Records  ' || SQL%ROWCOUNT);
308             FORALL j IN l_ae_header_id.FIRST .. l_ae_header_id.LAST
309                UPDATE    xla_ae_headers
310                      SET accounting_entry_status_code = 'I'
311                    WHERE application_id = p_application_id
312                      AND accounting_batch_id = p_accounting_batch_id
313                      AND ae_header_id = l_ae_header_id (j)
314                RETURNING         event_id
315                BULK COLLECT INTO l_event_id;
316             print_logfile ('No. of xla_ae_headers Records  ' || SQL%ROWCOUNT);
317             FORALL k IN l_event_id.FIRST .. l_event_id.LAST
318                UPDATE xla_ae_headers
319                   SET accounting_entry_status_code = 'R'
320                 WHERE event_id = l_event_id (k)
321                   AND application_id = p_application_id
322                   AND accounting_batch_id = p_accounting_batch_id
323                   AND accounting_entry_status_code <> 'I';
324             print_logfile (   'No. of Related xla_ae_headers Records  '
325                            || SQL%ROWCOUNT
326                           );
327             FORALL l IN l_event_id.FIRST .. l_event_id.LAST
328                UPDATE xla_events
329                   SET event_status_code = 'U',
330                       process_status_code = 'I'
331                 WHERE application_id = p_application_id
332                   AND event_id = l_event_id (l);
333             print_logfile ('No. of xla_events Records  ' || SQL%ROWCOUNT);
334          END IF;
335 
336 ----------------------------------------------------------------------------
337 -- Building the error message of the ccid invalid with the psft validation
338 ----------------------------------------------------------------------------
339     l_err_msg := nvl(substr(p_err_msg,1,240),'failed validation with external system');
340 
341          FOR n IN l_ae_header_id.FIRST .. l_ae_header_id.LAST
342          LOOP
343             SELECT ledger_id, entity_id, event_id
344               INTO l_ledger_id, l_entity_id, l_vent_id
345               FROM xla_ae_headers
346              WHERE ae_header_id = l_ae_header_id (n);
347 
348 ----------------------------------------------------------------------------
349  -- As the BPEL Prcoess faild to process the ccids with external system
350  -- on the report).
351  ----------------------------------------------------------------------------
352             IF p_status = -3
353             THEN
354                xla_accounting_err_pkg.build_message (p_appli_s_name      => 'XLA',
355                                                      p_msg_name          => 'XLA_EXT_SYS_CCID_VAL_FAIL',
356                                                      p_token_1           => 'ERR_MEG',
357                                                      p_value_1           => l_err_msg,
358                                                      p_entity_id         => l_entity_id,
359                                                      p_event_id          => l_vent_id,
360                                                      p_ledger_id         => l_ledger_id,
361                                                      p_ae_header_id      => l_ae_header_id (n
362                                                                                            )
363                                                     );
364             ELSIF p_status = -2
365             THEN
366                xla_accounting_err_pkg.build_message (p_appli_s_name      => 'XLA',
367                                                      p_msg_name          => 'XLA_EXT_VAL_FAIL_CCID',
368                                                      p_entity_id         => l_entity_id,
369                                                      p_event_id          => l_vent_id,
370                                                      p_ledger_id         => l_ledger_id,
371                                                      p_ae_header_id      => l_ae_header_id (n
372                                                                                            )
373                                                     );
374             END IF;
375          END LOOP;
376 
377 ----------------------------------------------------------------------------
378  -- insert any errors that were build in this session (for them to appear
379  -- on the report).
380  ----------------------------------------------------------------------------
381          xla_accounting_err_pkg.insert_errors;
382          COMMIT;
383       END IF; -- for sus
384    EXCEPTION
385       WHEN OTHERS
386       THEN
387          RAISE;
388    END;
389 BEGIN
390    g_log_level := fnd_log.g_current_runtime_level;
391    g_log_enabled :=
392           fnd_log.test (log_level      => g_log_level,
393                         module         => c_default_module);
394 
395    IF NOT g_log_enabled
396    THEN
397       g_log_level := c_level_log_disabled;
398    END IF;
399 END xla_update_inval_ccid;