[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;