[Home] [Help]
PACKAGE BODY: APPS.XLA_TB_DATA_MANAGER_PVT
Source
1 PACKAGE BODY XLA_TB_DATA_MANAGER_PVT AS
2 /* $Header: xlatbdmg.pkb 120.54 2012/01/13 10:29:43 nmikkili ship $ */
3 /*===========================================================================+
4 | Copyright (c) 2005-2006 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | ALL rights reserved. |
7 +============================================================================+
8 | PACKAGE NAME |
9 | xla_tb_data_manager_pvt |
10 | |
11 | DESCRIPTION |
12 | This IS a XLA PRIVATE PACKAGE, which contains ALL THE logic required |
13 | TO upload trial balance data INTO xla_trial_balances |
14 | |
15 | |
16 | HISTORY |
17 | 07-OCT-2005 M.Asada Created |
18 | 20-May-2008 schodava Bug 7109823 - TB Remodelling |
19 | 29-May-2008 rajose bug#7109823 Dynamic building of where clause |
20 | for accounting_class_code defined for |
21 | program code for an application. |
22 | 18-June-2008 rajose bug#7109823 Added index(xah XLA_AE_HEADERS_U1) |
23 | hint in the 3 sql's to improve performance. |
24 | Addition of the hint has improved performance |
25 | for US GAP customer Refer bug#6990540 |
26 | 4-Jul-2008 rajose bug#7225096 fix for ORA-00911: invalid character|
27 | error |
28 | 1-Sep-2008 rajose bug#7320079 To move all GSI changes performance |
29 | recovery to branchline code. Passed |
30 | p_je_source name to worker_process so that |
31 | insert_trial_balance_wu will insert data into |
32 | tb table if transfer to GL is spawned by an |
33 | application other than Payables and acctng class|
34 | code is registered in Post Programs. |
35 | 23-Sep-2008 rajose bug#7364921 Upgraded invoices not appearing in |
36 | the TB report for a given date range. |
37 | 19-Nov-2008 rajose bug#7552876 data manager upload_pvt procedure |
38 | errors out with ORA-01403: no data found |
39 | 27-Nov-2008 rajose bug#7600550 TB remodeling phase 4. Addresses the|
40 | issue where Open Account AP balances Listing |
41 | shows no data if new Journal source is added to |
42 | Definition part of QA bug 7431923 |
43 | 21-Jan-2008 rajose bug#7717479 data not appearing for definition |
44 | code rebuild of reporting ledger |
45 | 12-Mar-2009 VGOPISET Trial balance not rebuild for 11i Data for the |
46 | Definition Code defined by SEGMENT |
47 | 19-Mar-2009 rajose bug#8333978 Open Account Balances Data Manager |
48 | main program is taking time to complete. |
49 | 22-Jul-2009 nksurana 8348885:Added NO_INDEX on XAL in TBInsert script|
50 | to ignore usage of MIS_XLA_AE_LINES_N1 index. |
51 | 23-Feb-2011 VGOPISET 11770090:TB Data Manager errors when trying to |
52 | insert data for Third Party Merge in table |
53 | XLA_TB_USER_TRANS_VIEW in populate_user_trans_v |
54 | 05-Jan-2012 NMIKKILI 13464353: Data Manager Program ends in error |
55 | when it is cancelled and only when FND log is |
56 | enabled.Made a codefix to show an exception even|
57 | when fnd log is disabled |
58 | 13-Jan-2012 NMIKKILI Adding nvl on minimum_accountable_unit to avoid |
59 | null values for entered_rounded_cr |
60 +===========================================================================*/
61
62
63 --
64 -- Global Variables - WHO Column Information
65 --
66
67 g_request_id NUMBER(15);
68 g_user_id NUMBER(15);
69 g_login_id NUMBER(15);
70 g_prog_appl_id NUMBER(15);
71 g_program_id NUMBER(15);
72 g_ledger_id PLS_INTEGER;
73 g_group_id xla_ae_headers.group_id%TYPE;
74 g_definition_code xla_tb_definitions_b.definition_code%TYPE;
75 g_process_mode_code VARCHAR2(30);
76 g_je_source_name gl_je_sources.je_source_name%TYPE;
77 g_application_id PLS_INTEGER;
78 g_tb_insert_sql VARCHAR2(32000);
79 g_wu_count PLS_INTEGER; -- Work Unit Count
80 g_work_unit PLS_INTEGER;
81 g_num_of_workers PLS_INTEGER;
82 g_retrieve_wu_flag BOOLEAN;
83 g_gl_date_from VARCHAR2(30);
84 g_gl_date_to VARCHAR2(30);
85 /*------------------------------------------------------------+
86 | |
87 | Template SQL |
88 | |
89 +------------------------------------------------------------*/
90
91 -- ********************** Note bug#7213289********************************
92 -- The hint in the select of C_TB_INSERT_SQL ie
93 -- /*+ index(xah XLA_AE_HEADERS_U1) */ is replaced in the
94 -- procedure insert_trial_balance_wu. If any changes are made to the hint
95 -- in the SELECT, change the replace statement accordingly in
96 -- insert_trial_balance_wu.
97 -- **********************End Note*****************************************
98
99 C_TB_INSERT_SQL CONSTANT VARCHAR2(32000) := '
100 INSERT INTO xla_trial_balances (
101 record_type_code
102 ,source_entity_id
103 ,event_class_code
104 ,source_application_id
105 ,applied_to_entity_id
106 ,applied_to_application_id
107 ,gl_date
108 ,trx_currency_code
109 ,entered_rounded_dr
110 ,entered_rounded_cr
111 ,entered_unrounded_dr
112 ,entered_unrounded_cr
113 ,acctd_rounded_dr
114 ,acctd_rounded_cr
115 ,acctd_unrounded_dr
116 ,acctd_unrounded_cr
117 ,code_combination_id
118 ,balancing_segment_value
119 ,natural_account_segment_value
120 ,cost_center_segment_value
121 ,intercompany_segment_value
122 ,management_segment_value
123 ,ledger_id
124 ,definition_code
125 ,party_id
126 ,party_site_id
127 ,party_type_code
128 ,ae_header_id
129 ,generated_by_code
130 ,creation_date
131 ,created_by
132 ,last_update_date
133 ,last_updated_by
134 ,last_update_login
135 ,request_id
136 ,program_application_id
137 ,program_id
138 ,program_update_date)
139
140
141 SELECT /*+ index(xah XLA_AE_HEADERS_U1) no_index(xal MIS_XLA_AE_LINES_N1) */
142 DECODE(xdl.applied_to_entity_id
143 ,NULL
144 ,''SOURCE''
145 ,''APPLIED'') record_type_code
146 ,xah.entity_id source_entity_id
147 ,xet.event_class_code event_class_code
148 ,xah.application_id source_application_id
149 ,xdl.applied_to_entity_id applied_to_entity_id
150 ,xdl.applied_to_application_id applied_to_application_id
151 ,xah.accounting_date gl_date
152 ,xal.currency_code trx_currency_code
153
154 -- changes for incorrect trial balance amounts bug 6366295
155 -- entered_rounded_dr
156 ,decode(nvl(sum(xdl.unrounded_entered_cr), sum(xdl.unrounded_entered_dr)), null, null,
157 CASE xlo.acct_reversal_option_code
158 WHEN ''SIDE'' THEN
159 CASE SIGN(
160 NVL(SUM(xdl.unrounded_entered_dr),0) - NVL(SUM(xdl.unrounded_entered_cr),0)+
161 NVL(SUM(xdl.doc_rounding_entered_amt), 0)
162 )
163 WHEN -1 THEN null
164 WHEN 1 THEN
165 ROUND(
166 (NVL(SUM(xdl.unrounded_entered_dr),0) - NVL(SUM(xdl.unrounded_entered_cr),0)+
167 NVL(SUM(xdl.doc_rounding_entered_amt), 0))
168 /nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
169 + decode(xlo.rounding_rule_code,''NEAREST'', 0,''UP'',.5-power(10, -30),''DOWN'',-(.5-power(10, -30)),0))
170 *nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
171 ELSE
172 CASE SIGN(NVL(SUM(xdl.unrounded_accounted_dr),0) - NVL(SUM(xdl.unrounded_accounted_cr),0)
173 +NVL(SUM(xdl.doc_rounding_acctd_amt), 0))
174 WHEN -1 THEN null
175 WHEN 1 THEN 0
176 ELSE DECODE(sum(xdl.unrounded_accounted_cr), 0, to_number(null), 0)
177 END
178 END
179 ELSE DECODE(sum(xdl.unrounded_accounted_cr), null ,
180 ROUND(
181 (SUM(xdl.unrounded_entered_dr)-NVL(SUM(xdl.doc_rounding_entered_amt), 0))
182 /nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
183 +decode(xlo.rounding_rule_code,''NEAREST'', 0,''UP'',(.5-power(10, -30)),''DOWN'',-(.5-power(10, -30)),0))
184 *nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
185 ,ROUND(
186 SUM(xdl.unrounded_entered_dr) /nvl(minimum_accountable_unit, power(10, -1* precision))
187 +decode(rounding_rule_code,''NEAREST'', 0,''UP'',(.5-power(10, -30)),''DOWN'',-(.5-power(10, -30)),0))
188 *nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
189 )
190 END ) entered_rounded_dr
191 -- entered_rounded_cr
192 ,decode(nvl(sum(xdl.unrounded_entered_cr), sum(xdl.unrounded_entered_dr)), null, null,
193 CASE xlo.acct_reversal_option_code
194 WHEN ''SIDE'' THEN
195 CASE SIGN(
196 NVL(SUM(xdl.unrounded_entered_cr),0) - NVL(SUM(xdl.unrounded_entered_dr),0)+
197 NVL(SUM(xdl.doc_rounding_entered_amt), 0)
198 )
199 WHEN -1 THEN null
200 WHEN 1 THEN
201 ROUND(
202 (NVL(SUM(xdl.unrounded_entered_cr),0) - NVL(SUM(xdl.unrounded_entered_dr),0)+
203 NVL(SUM(xdl.doc_rounding_entered_amt), 0))
204 /nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
205 + decode(xlo.rounding_rule_code,''NEAREST'', 0,''UP'',.5-power(10, -30),''DOWN'',-(.5-power(10, -30)),0))
206 *nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
207 ELSE
208 CASE SIGN(NVL(SUM(xdl.unrounded_accounted_cr),0) - NVL(SUM(xdl.unrounded_accounted_dr),0)
209 +NVL(SUM(xdl.doc_rounding_acctd_amt), 0))
210 WHEN -1 THEN null
211 WHEN 1 THEN 0
212 ELSE DECODE(sum(xdl.unrounded_accounted_cr), 0, 0, null)
213 END
214 END
215 ELSE DECODE(SUM(xdl.unrounded_entered_cr), null, to_number(null) ,
216 ROUND(
217 (SUM(xdl.unrounded_entered_cr) +
218 NVL(SUM(xdl.doc_rounding_entered_amt), 0))
219 /nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision)) -- Bug 13464353
220 +decode(xlo.rounding_rule_code,''NEAREST'', 0,''UP'',.5-power(10, -30),''DOWN'',-(.5-power(10, -30)),0))
221 *nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))) -- Bug 13464353
222 END ) entered_rounded_cr
223
224 --entered_unrounded_dr
225 ,CASE SIGN(NVL(SUM(xdl.unrounded_entered_cr),0) - NVL(SUM(xdl.unrounded_entered_dr),0)
226 )
227 WHEN 1 THEN null
228 WHEN -1 THEN (NVL(SUM(xdl.unrounded_entered_dr),0) - NVL(SUM(xdl.unrounded_entered_cr),0))
229 ELSE 0
230 END entered_unrounded_dr
231
232 --entered_unrounded_cr
233 ,CASE SIGN(NVL(SUM(xdl.unrounded_entered_cr),0) - NVL(SUM(xdl.unrounded_entered_dr),0)
234 )
235 WHEN 1 THEN (NVL(SUM(xdl.unrounded_entered_cr),0) - NVL(SUM(xdl.unrounded_entered_dr),0))
236 WHEN -1 THEN NULL
237 ELSE 0
238 END entered_unrounded_cr
239
240 -- accounted_rounded_dr
241 , decode(nvl(sum(xdl.unrounded_accounted_cr), sum(xdl.unrounded_accounted_dr)), null, null,
242 CASE xlo.acct_reversal_option_code
243 WHEN ''SIDE'' THEN
244 CASE SIGN(
245 NVL(SUM(xdl.unrounded_accounted_dr),0) - NVL(SUM(xdl.unrounded_accounted_cr),0)-
246 NVL(SUM(xdl.doc_rounding_acctd_amt), 0)
247 )
248 WHEN -1 THEN null
249 WHEN 1 THEN
250 ROUND(
251 (NVL(SUM(xdl.unrounded_accounted_dr),0) - NVL(SUM(xdl.unrounded_accounted_cr),0)-
252 NVL(SUM(xdl.doc_rounding_acctd_amt), 0))
253 /nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
254 +decode(xlo.rounding_rule_code,''NEAREST'', 0,''UP'',.5-power(10, -30),''DOWN'',-(.5-power(10, -30)),0))
255 *nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
256 ELSE
257 CASE SIGN(NVL(SUM(xdl.unrounded_entered_dr),0) - NVL(SUM(xdl.unrounded_entered_cr),0)-
258 NVL(SUM(xdl.doc_rounding_entered_amt), 0))
259 WHEN -1 THEN null
260 WHEN 1 THEN 0
261 ELSE DECODE(sum(xdl.unrounded_accounted_cr), 0, to_number(null), 0)
262 END
263 END
264 ELSE
265 decode(SUM(xdl.unrounded_accounted_cr), null,
266 ROUND(
267 (SUM(xdl.unrounded_accounted_dr)-NVL(SUM(xdl.doc_rounding_acctd_amt), 0))
268 /nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
269 +decode(xlo.rounding_rule_code,''NEAREST'', 0,''UP'',.5-power(10, -30),''DOWN'',-(.5-power(10, -30)),0))
270 *nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
271 ,ROUND(
272 SUM(xdl.unrounded_accounted_dr) /nvl(minimum_accountable_unit, power(10, -1* precision))
273 +decode(xlo.rounding_rule_code,''NEAREST'', 0,''UP'',.5-power(10, -30),''DOWN'',-(.5-power(10, -30)),0))
274 *nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
275 )
276 END) accounted_rounded_dr
277
278 -- accounted_rounded_cr
279 , decode(nvl(sum(xdl.unrounded_accounted_cr), sum(xdl.unrounded_accounted_dr)), null, null,
280 CASE xlo.acct_reversal_option_code
281 WHEN ''SIDE'' THEN
282 CASE SIGN(
283 NVL(SUM(xdl.unrounded_accounted_cr),0) - NVL(SUM(xdl.unrounded_accounted_dr),0)+
284 NVL(SUM(xdl.doc_rounding_acctd_amt), 0)
285 )
286 WHEN -1 THEN null
287 WHEN 1 THEN
288 ROUND(
289 (NVL(SUM(xdl.unrounded_accounted_cr),0) - NVL(SUM(xdl.unrounded_accounted_dr),0)+
290 NVL(SUM(xdl.doc_rounding_acctd_amt), 0))
291 /nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
292 +decode(xlo.rounding_rule_code,''NEAREST'', 0,''UP'',.5-power(10, -30),''DOWN'',-(.5-power(10, -30)),0))
293 *nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
294 ELSE
295 CASE SIGN(NVL(SUM(xdl.unrounded_entered_cr),0) - NVL(SUM(xdl.unrounded_entered_dr),0)+
296 NVL(SUM(xdl.doc_rounding_entered_amt), 0))
297 WHEN -1 THEN null
298 WHEN 1 THEN 0
299 ELSE DECODE(sum(xdl.unrounded_accounted_cr), 0, 0, null)
300 END
301 END
302 ELSE DECODE(SUM(xdl.unrounded_accounted_cr), null, to_number(null) ,
303 ROUND(
304 (SUM(xdl.unrounded_accounted_cr) +
305 NVL(SUM(xdl.doc_rounding_acctd_amt), 0))
306 /nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
307 +decode(xlo.rounding_rule_code,''NEAREST'', 0,''UP'',.5-power(10, -30),''DOWN'',-(.5-power(10, -30)),0))
308 *nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
309 )
310 END) accounted_rounded_cr
311
312 -- acctd_unrounded_dr
313 ,CASE SIGN(NVL(SUM(xdl.unrounded_accounted_cr),0) - NVL(SUM(xdl.unrounded_accounted_dr),0)
314 )
315 WHEN 1 THEN NULL
316 WHEN -1 THEN (NVL(SUM(xdl.unrounded_accounted_dr),0) - NVL(SUM(xdl.unrounded_accounted_cr),0))
317 ELSE 0
318 END acctd_unrounded_dr
319
320 -- acctd_unrounded_cr
321 ,CASE SIGN(NVL(SUM(xdl.unrounded_accounted_cr),0) - NVL(SUM(xdl.unrounded_accounted_dr),0)
322 )
323 WHEN 1 THEN (NVL(SUM(xdl.unrounded_accounted_cr),0) - NVL(SUM(xdl.unrounded_accounted_dr),0))
324 WHEN -1 THEN NULL
325 ELSE 0
326 END acctd_unrounded_cr
327 --end changes bug 6366295
328 ,xal.code_combination_id code_combination_id
329 ,$bal_segment$ balancing_segment_value
330 ,$acct_segment$ natural_account_segment_value
331 ,$cc_segment$ cost_center_segment_value
332 ,$ic_segment$ intercompany_segment_value
333 ,$mgt_segment$ management_segment_value
334 ,xah.ledger_id ledger_id
335 ,xtd.definition_code DEFINITION_code
336 ,xal.party_id party_id
337 ,xal.party_site_id party_site_id
338 ,xal.party_type_code party_type_code
339 ,xah.ae_header_id ae_header_id
340 ,''SYSTEM'' generated_by_code
341 ,SYSDATE creation_date
342 ,:1 -- g_user_id
343 ,SYSDATE
344 ,:2 -- g_user_id
345 ,:3 -- g_login_id
346 ,:4 -- g_request_id
347 ,:5 -- g_prog_appl_id
348 ,:6 -- g_program_id
349 ,sysdate
350
351 FROM
352 xla_ae_headers xah
353 ,xla_ae_lines xal
354 ,xla_distribution_links xdl
355 ,xla_ledger_options xlo
356 ,fnd_currencies fdc
357 ,gl_ledgers gl
358 ,gl_code_combinations gcc
359 ,xla_event_types_b xet
360 ,xla_tb_definitions_b xtd
361 $l_from$
362 WHERE xah.ae_header_id BETWEEN :7 AND :8
363 AND xah.upg_batch_id IS NULL -- added bug 6704677
364 $l_ledger_where$
365 AND xah.gl_transfer_status_code IN (''Y'',''NT'')
366 AND xah.application_id = xal.application_id
367 AND xah.ae_header_id = xal.ae_header_id
368 AND xal.application_id = xdl.application_id (+)
369 AND xal.ae_header_id = xdl.ae_header_id (+)
370 AND xal.ae_line_num = xdl.ae_line_num (+)
371 AND xtd.enabled_flag = ''Y''
372 $l_where$
373 AND xal.code_combination_id = gcc.code_combination_id
374 AND gcc.chart_of_accounts_id = :coa_id
375 AND xah.application_id = xet.application_id
376 AND xah.event_type_code = xet.event_type_code
377 AND xlo.ledger_id(+) = xah.ledger_id
378 AND xlo.application_id(+) = xah.application_id
379 AND xah.ledger_id = gl.ledger_id
380 AND xah.ledger_id = xtd.ledger_id --added bug 7359012,one definition code showing data for multilple ledgers in TB report
381 AND fdc.currency_code = gl.currency_code
382 --- remodeling
383 $l_accounting_class_code_where$
384 AND xah.event_type_code <> ''MANUAL''
385 --- remodeling
386 GROUP BY
387 DECODE(xdl.applied_to_entity_id
388 ,NULL
389 ,''SOURCE''
390 ,''APPLIED'')
391 ,xtd.definition_code
392 ,xet.event_class_code
393 ,xah.application_id
394 ,xdl.applied_to_entity_id
395 ,xdl.applied_to_application_id
396 ,xal.party_id
397 ,xal.party_site_id
398 ,xal.party_type_code
399 ,xah.entity_id
400 ,xah.ledger_id
401 ,xah.accounting_date
402 ,xah.ae_header_id
403 ,xal.currency_code
404 ,xal.code_combination_id
405 ,$bal_segment$
406 ,$acct_segment$
407 ,$cc_segment$
408 ,$ic_segment$
409 ,$mgt_segment$
410 ,xlo.acct_reversal_option_code
411 ,xlo.rounding_rule_code
412 ,fdc.minimum_accountable_unit
413 ,fdc.precision
414 ';
415
416
417 --added bug 6704677
418 -- 26-Mar-2008 bug#6917849 added 2 insert scripts by splitting C_TB_INSERT_UPG_SQL into
419 -- C_TB_INSERT_UPG_SQL_AE and C_TB_INSERT_UPG_SQL_SLE. The SQL in the 2 scripts
420 -- are used to pick the appropriate index in ap_liability_balance table.
421
422 --for bug#7364921 did a trunc of xah.accounting_date in the query below
423 --Reason gl_date is populated with time component and the trial balance report
424 --query does not fetch data for a date including time stamp
425 -- example report query date range is '01-MAY-2008' to '31-MAY-2008' and if
426 -- for a invoice in trial balance table the gl_date is '31-MAY-2008 09:13:00 AM'
427 -- this invoice will not fall in the above date range. It will fall in the date
428 -- range for the next day ie '01-MAY-2008' to '01-JUN-2008'
429
430 --bug#7717479 commented the join --AND xteu.ledger_id = alb.set_of_books_id in both
431 -- the upgrade scripts _AE and _SLE as for reporting ledger this join fails and trial balance is not rebuild
432 -- cannot remove this join as performance will be affected since ledger_id is
433 -- leading part of index _N1 in xteu.
434
435 --bug#7619431 made the query dynamic so that definition Details/Definition Seg Ranges is used
436 -- for Flex/Segment Definition, when TB Definition Code is re-built
437 -- Also, all commented code has been removed from the String as Variable can hold only 32000
438
439 C_TB_INSERT_UPG_SQL_AE CONSTANT VARCHAR2(32000) := '
440 INSERT INTO xla_trial_balances xtb(
441 record_type_code
442 ,source_entity_id
443 ,event_class_code
444 ,source_application_id
445 ,applied_to_entity_id
446 ,applied_to_application_id
447 ,gl_date
448 ,trx_currency_code
449 ,entered_rounded_dr
450 ,entered_rounded_cr
451 ,entered_unrounded_dr
452 ,entered_unrounded_cr
453 ,acctd_rounded_dr
454 ,acctd_rounded_cr
455 ,acctd_unrounded_dr
456 ,acctd_unrounded_cr
457 ,code_combination_id
458 ,balancing_segment_value
459 ,natural_account_segment_value
460 ,cost_center_segment_value
461 ,intercompany_segment_value
462 ,management_segment_value
463 ,ledger_id
464 ,definition_code
465 ,party_id
466 ,party_site_id
467 ,party_type_code
468 ,ae_header_id
469 ,generated_by_code
470 ,creation_date
471 ,created_by
472 ,last_update_date
473 ,last_updated_by
474 ,last_update_login
475 ,request_id
476 ,program_application_id
477 ,program_id
478 ,program_update_date)
479 SELECT /*+ index(xah XLA_AE_HEADERS_U1) no_index(xal MIS_XLA_AE_LINES_N1) */
480 DECODE(xet.event_class_code,''PREPAYMENT APPLICATIONS'',''APPLIED'',DECODE(xteu.entity_id,xah.entity_id,''SOURCE'',''APPLIED'')) record_type_code
481 ,xah.entity_id source_entity_id
482 ,xet.event_class_code event_class_code
483 ,xah.application_id source_application_id
484 ,DECODE(xet.event_class_code,''PREPAYMENT APPLICATIONS'',xteu.entity_id,DECODE(xteu.entity_id, xah.entity_id,NULL,xteu.entity_id)) applied_to_entity_id
485 ,200 applied_to_application_id
486 ,trunc(xah.accounting_date) gl_date
487 ,xal.currency_code trx_currency_code
488 ,SUM(NVL(xal.entered_dr,0)) entered_rounded_dr
489 ,SUM(NVL(xal.entered_cr,0)) entered_rounded_cr
490 ,SUM(NVL(xal.entered_dr,0)) entered_unrounded_dr
491 ,SUM(NVL(xal.entered_cr,0)) entered_unrounded_cr
492 ,SUM(NVL(alb.accounted_dr, 0)) acctd_rounded_dr
493 ,SUM(NVL(alb.accounted_cr, 0)) acctd_rounded_cr
494 ,SUM(NVL(alb.accounted_dr,0)) acctd_unrounded_dr
495 ,SUM(NVL(alb.accounted_cr,0)) acctd_unrounded_cr
496 ,xal.code_combination_id code_combination_id
497 ,DECODE(fsav.balancing_segment,
498 ''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
499 ''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
500 ''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
501 ''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
502 ''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
503 ''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
504 ''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
505 ''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
506 ''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
507 ''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
508 null)
509 balancing_segment_value
510 ,DECODE(fsav.account_segment,
511 ''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
512 ''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
513 ''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
514 ''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
515 ''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
516 ''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
517 ''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
518 ''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
519 ''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
520 ''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
521 null)
522 natural_account_segment_value
523 ,DECODE(fsav.cost_crt_segment,
524 ''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
525 ''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
526 ''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
527 ''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
528 ''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
529 ''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
530 ''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
531 ''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
532 ''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
533 ''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
534 null)
535 cost_center_segment_value
536 ,DECODE(fsav.intercompany_segment,
537 ''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
538 ''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
539 ''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
540 ''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
541 ''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
542 ''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
543 ''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
544 ''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
545 ''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
546 ''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
547 null)
548 intercompany_segment_value
549 ,DECODE(fsav.management_segment,
550 ''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
551 ''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
552 ''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
553 ''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
554 ''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
555 ''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
556 ''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
557 ''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
558 ''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
559 ''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
560 null)
561 management_segment_value
562 ,xah.ledger_id ledger_id
563 ,xtd.definition_code DEFINITION_code
564 ,xal.party_id party_id
565 ,xal.party_site_id party_site_id
566 ,xal.party_type_code party_type_code
567 ,xah.ae_header_id ae_header_id
568 ,''SYSTEM'' generated_by_code
569 ,SYSDATE creation_date
570 ,-1 created_by
571 ,SYSDATE last_update_date
572 ,-1 last_updated_by
573 ,-1 last_update_login
574 ,-1 request_id
575 ,-1 program_application_id
576 ,-1 program_id
577 ,SYSDATE program_update_date
578 FROM
579 ap_liability_balance alb
580 ,xla_ae_headers PARTITION (AP) xah
581 ,xla_event_types_b xet
582 ,xla_tb_definitions_b xtd
583 $l_from$
584 ,xla_transaction_entities_upg PARTITION (AP) xteu
585 ,xla_ae_lines PARTITION (AP) xal
586 ,gl_code_combinations gcc
587 ,( SELECT /*+ NO_MERGE*/ id_flex_num
588 ,MAX(DECODE(SEGMENT_ATTRIBUTE_TYPE, ''GL_BALANCING'', application_column_name, NULL)) balancing_segment
589 ,MAX(DECODE(SEGMENT_ATTRIBUTE_TYPE, ''GL_ACCOUNT'', application_column_name, NULL)) account_segment
590 ,MAX(DECODE(SEGMENT_ATTRIBUTE_TYPE, ''FA_COST_CTR'', application_column_name, NULL)) cost_crt_segment
591 ,MAX(DECODE(SEGMENT_ATTRIBUTE_TYPE, ''GL_INTERCOMPANY'', application_column_name, NULL)) intercompany_segment
592 ,MAX(DECODE(SEGMENT_ATTRIBUTE_TYPE, ''GL_MANAGEMENT'', application_column_name, NULL)) management_segment
593 FROM fnd_segment_attribute_values fsav1 -- Need alias here also.
594 WHERE application_id = 101
595 AND id_flex_code = ''GL#''
596 AND attribute_value = ''Y''
597 GROUP BY id_flex_num) fsav
598 WHERE
599 xah.gl_transfer_status_code IN (''Y'',''NT'')
600 AND xah.application_id = xal.application_id
601 AND xah.ae_header_id BETWEEN :1 AND :2
602 AND xah.application_id = 200
603 AND xah.ledger_id = :3
604 AND xah.upg_batch_id IS NOT NULL
605 AND xah.ae_header_id = xal.ae_header_id
606 AND xal.code_combination_id = gcc.code_combination_id
607 AND xal.code_combination_id = alb.code_combination_id
608 AND xah.application_id = xet.application_id
609 AND xteu.application_id = 200
610 AND xteu.entity_code = ''AP_INVOICES''
611 AND NVL(xteu.source_id_int_1,-99) = alb.invoice_id
612 --AND xteu.ledger_id = alb.set_of_books_id
613 AND xteu.ledger_id = $l_derived_primary_ledger$
614 AND alb.ae_header_id IS NOT NULL
615 AND alb.ae_line_id IS NOT NULL
616 AND alb.ae_header_id = xah.completion_acct_seq_value
617 AND 200 = xah.completion_acct_seq_version_id
618 AND alb.ae_line_id = xal.ae_line_num
619 AND xah.upg_source_application_id = 200
620 AND xah.event_type_code = xet.event_type_code
621 AND gcc.chart_of_accounts_id = fsav.id_flex_num
622 $l_where$
623 AND xtd.ledger_id = alb.set_of_books_id
624 AND alb.code_combination_id = xal.code_combination_id
625 --- remodeling
626 AND xal.accounting_class_code = ''LIABILITY''
627 AND xah.event_type_code <> ''MANUAL''
628 --- remodeling
629
630 GROUP BY
631 DECODE(xet.event_class_code,''PREPAYMENT APPLICATIONS'',''APPLIED'',DECODE(xteu.entity_id,xah.entity_id,''SOURCE'',''APPLIED''))
632 ,xah.entity_id
633 ,xet.event_class_code
634 ,xah.application_id
635 ,DECODE(xet.event_class_code,''PREPAYMENT APPLICATIONS'',xteu.entity_id,DECODE(xteu.entity_id, xah.entity_id,NULL,xteu.entity_id))
636 ,xah.accounting_date
637 ,xal.currency_code
638 ,xal.code_combination_id
639 ,DECODE(fsav.balancing_segment,
640 ''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
641 ''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
642 ''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
643 ''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
644 ''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
645 ''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
646 ''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
647 ''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
648 ''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
649 ''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
650 null)
651 ,DECODE(fsav.account_segment,
652 ''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
653 ''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
654 ''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
655 ''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
656 ''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
657 ''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
658 ''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
659 ''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
660 ''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
661 ''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
662 null)
663 ,DECODE(fsav.cost_crt_segment,
664 ''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
665 ''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
666 ''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
667 ''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
668 ''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
669 ''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
670 ''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
671 ''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
672 ''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
673 ''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
674 null)
675 ,DECODE(fsav.intercompany_segment,
676 ''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
677 ''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
678 ''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
679 ''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
680 ''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
681 ''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
682 ''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
683 ''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
684 ''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
685 ''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
686 null)
687 ,DECODE(fsav.management_segment,
688 ''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
689 ''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
690 ''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
691 ''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
692 ''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
693 ''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
694 ''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
695 ''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
696 ''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
697 ''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
698 null)
699 ,xah.ledger_id
700 ,xtd.definition_code
701 ,xal.party_id
702 ,xal.party_site_id
703 ,xal.party_type_code
704 ,xah.ae_header_id
705 ';
706 -- added $l_from$,$l_where$ to dynamically build code for Flex/Segment TB Def Code. bug: 7619431
707 -- commented code from above SQL
708 /*
709 Tables removed from FROM clause:
710 -------------------------------
711 ,xla_tb_defn_details xdd
712 ,xla_tb_defn_je_sources xjs
713 ,xla_subledgers xsu
714 */
715 -- Conditions removed from WHERE clause:
716 -- -------------------------------------
717 /* AND NVL(alb.ae_header_id, alb.sle_header_id) = xah.completion_acct_seq_value
718 AND NVL2(alb.ae_header_id,200, alb.journal_sequence_id) = xah.completion_acct_seq_version_id
719 AND NVL2(alb.ae_header_id, alb.ae_line_id,alb.sle_line_num) = xal.ae_line_num
720 AND (
721 (alb.ae_header_id IS NOT NULL AND xah.upg_source_application_id = 200)
722 OR
723 (alb.ae_header_id IS NULL AND xah.upg_source_application_id = 600 AND xah.upg_batch_id = -5672)
724 )
725 */
726 /*
727 AND xtd.definition_code = xdd.definition_code
728 AND xtd.definition_code = :4
729 AND xtd.definition_code = xjs.definition_code
730 AND xtd.enabled_flag = ''Y''
731 AND xjs.je_source_name = xsu.je_source_name
732 AND xsu.application_id = 200
733 AND alb.code_combination_id = xdd.code_combination_id
734 */
735
736 --for bug#7364921 did a trunc of xah.accounting_date in the query below
737 --Reason gl_date is populated with time component and the trial balance report
738 --query does not fetch data for a date including time stamp.
739
740 --bug#7619431 made the query dynamic so that definition Details/Definition Seg Ranges is used
741 -- for Flex/Segment Definition, when TB Definition Code is re-built
742 -- Also, all commented code has been removed from the String as Variable can hold only 32000
743
744 C_TB_INSERT_UPG_SQL_SLE CONSTANT VARCHAR2(32000) := '
745 INSERT INTO xla_trial_balances xtb(
746 record_type_code
747 ,source_entity_id
748 ,event_class_code
749 ,source_application_id
750 ,applied_to_entity_id
751 ,applied_to_application_id
752 ,gl_date
753 ,trx_currency_code
754 ,entered_rounded_dr
755 ,entered_rounded_cr
756 ,entered_unrounded_dr
757 ,entered_unrounded_cr
758 ,acctd_rounded_dr
759 ,acctd_rounded_cr
760 ,acctd_unrounded_dr
761 ,acctd_unrounded_cr
762 ,code_combination_id
763 ,balancing_segment_value
764 ,natural_account_segment_value
765 ,cost_center_segment_value
766 ,intercompany_segment_value
767 ,management_segment_value
768 ,ledger_id
769 ,definition_code
770 ,party_id
771 ,party_site_id
772 ,party_type_code
773 ,ae_header_id
774 ,generated_by_code
775 ,creation_date
776 ,created_by
777 ,last_update_date
778 ,last_updated_by
779 ,last_update_login
780 ,request_id
781 ,program_application_id
782 ,program_id
783 ,program_update_date)
784 SELECT /*+ index(xah XLA_AE_HEADERS_U1) no_index(xal MIS_XLA_AE_LINES_N1) */
785 DECODE(xet.event_class_code,''PREPAYMENT APPLICATIONS'',''APPLIED'',DECODE(xteu.entity_id,xah.entity_id,''SOURCE'',''APPLIED'')) record_type_code
786 ,xah.entity_id source_entity_id
787 ,xet.event_class_code event_class_code
788 ,xah.application_id source_application_id
789 ,DECODE(xet.event_class_code,''PREPAYMENT APPLICATIONS'',xteu.entity_id,DECODE(xteu.entity_id, xah.entity_id,NULL,xteu.entity_id)) applied_to_entity_id
790 ,200 applied_to_application_id
791 ,trunc(xah.accounting_date) gl_date
792 ,xal.currency_code trx_currency_code
793 ,SUM(NVL(xal.entered_dr,0)) entered_rounded_dr
794 ,SUM(NVL(xal.entered_cr,0)) entered_rounded_cr
795 ,SUM(NVL(xal.entered_dr,0)) entered_unrounded_dr
796 ,SUM(NVL(xal.entered_cr,0)) entered_unrounded_cr
797 ,SUM(NVL(alb.accounted_dr, 0)) acctd_rounded_dr
798 ,SUM(NVL(alb.accounted_cr, 0)) acctd_rounded_cr
799 ,SUM(NVL(alb.accounted_dr,0)) acctd_unrounded_dr
800 ,SUM(NVL(alb.accounted_cr,0)) acctd_unrounded_cr
801 ,xal.code_combination_id code_combination_id
802 ,DECODE(fsav.balancing_segment,
803 ''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
804 ''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
805 ''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
806 ''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
807 ''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
808 ''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
809 ''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
810 ''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
811 ''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
812 ''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
813 null)
814 balancing_segment_value
815 ,DECODE(fsav.account_segment,
816 ''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
817 ''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
818 ''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
819 ''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
820 ''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
821 ''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
822 ''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
823 ''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
824 ''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
825 ''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
826 null)
827 natural_account_segment_value
828 ,DECODE(fsav.cost_crt_segment,
829 ''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
830 ''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
831 ''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
832 ''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
833 ''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
834 ''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
835 ''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
836 ''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
837 ''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
838 ''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
839 null)
840 cost_center_segment_value
841 ,DECODE(fsav.intercompany_segment,
842 ''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
843 ''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
844 ''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
845 ''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
846 ''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
847 ''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
848 ''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
849 ''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
850 ''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
851 ''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
852 null)
853 intercompany_segment_value
854 ,DECODE(fsav.management_segment,
855 ''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
856 ''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
857 ''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
858 ''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
859 ''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
860 ''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
861 ''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
862 ''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
863 ''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
864 ''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
865 null)
866 management_segment_value
867 ,xah.ledger_id ledger_id
868 ,xtd.definition_code DEFINITION_code
869 ,xal.party_id party_id
870 ,xal.party_site_id party_site_id
871 ,xal.party_type_code party_type_code
872 ,xah.ae_header_id ae_header_id
873 ,''SYSTEM'' generated_by_code
874 ,SYSDATE creation_date
875 ,-1 created_by
876 ,SYSDATE last_update_date
877 ,-1 last_updated_by
878 ,-1 last_update_login
879 ,-1 request_id
880 ,-1 program_application_id
881 ,-1 program_id
882 ,SYSDATE program_update_date
883 FROM
884 ap_liability_balance alb
885 ,xla_ae_headers PARTITION (AP) xah
886 ,xla_event_types_b xet
887 ,xla_tb_definitions_b xtd
888 $l_from$
889 ,xla_transaction_entities_upg PARTITION (AP) xteu
890 ,xla_ae_lines PARTITION (AP) xal
891 ,gl_code_combinations gcc
892 ,( SELECT /*+ NO_MERGE*/ id_flex_num
893 ,MAX(DECODE(SEGMENT_ATTRIBUTE_TYPE, ''GL_BALANCING'', application_column_name, NULL)) balancing_segment
894 ,MAX(DECODE(SEGMENT_ATTRIBUTE_TYPE, ''GL_ACCOUNT'', application_column_name, NULL)) account_segment
895 ,MAX(DECODE(SEGMENT_ATTRIBUTE_TYPE, ''FA_COST_CTR'', application_column_name, NULL)) cost_crt_segment
896 ,MAX(DECODE(SEGMENT_ATTRIBUTE_TYPE, ''GL_INTERCOMPANY'', application_column_name, NULL)) intercompany_segment
897 ,MAX(DECODE(SEGMENT_ATTRIBUTE_TYPE, ''GL_MANAGEMENT'', application_column_name, NULL)) management_segment
898 FROM fnd_segment_attribute_values fsav1 -- Need alias here also.
899 WHERE application_id = 101
900 AND id_flex_code = ''GL#''
901 AND attribute_value = ''Y''
902 GROUP BY id_flex_num) fsav
903 WHERE
904 xah.gl_transfer_status_code IN (''Y'',''NT'')
905 AND xah.application_id = xal.application_id
906 AND xah.ae_header_id BETWEEN :1 AND :2
907 AND xah.application_id = 200
908 AND xah.ledger_id = :3
909 AND xah.upg_batch_id IS NOT NULL
910 AND xah.ae_header_id = xal.ae_header_id
911 AND xal.code_combination_id = gcc.code_combination_id
912 AND xal.code_combination_id = alb.code_combination_id
913 AND xah.application_id = xet.application_id
914 AND xteu.application_id = 200
915 AND xteu.entity_code = ''AP_INVOICES''
916 AND NVL(xteu.source_id_int_1,-99) = alb.invoice_id
917 --AND xteu.ledger_id = alb.set_of_books_id
918 AND xteu.ledger_id = $l_derived_primary_ledger$
919 AND alb.sle_header_id IS NOT NULL
920 AND alb.sle_line_num IS NOT NULL
921 AND alb.sle_header_id = xah.completion_acct_seq_value
922 AND alb.journal_sequence_id = xah.completion_acct_seq_version_id
923 AND alb.sle_line_num = xal.ae_line_num
924 AND xah.upg_source_application_id = 600
925 AND xah.upg_batch_id = -5672
926
927 AND xah.event_type_code = xet.event_type_code
928 AND gcc.chart_of_accounts_id = fsav.id_flex_num
929 $l_where$
930 AND xtd.ledger_id = alb.set_of_books_id
931 AND alb.code_combination_id = xal.code_combination_id
932 --- remodeling
933 AND xal.accounting_class_code = ''LIABILITY''
934 AND xah.event_type_code <> ''MANUAL''
935 --- remodeling
936
937 GROUP BY
938 DECODE(xet.event_class_code,''PREPAYMENT APPLICATIONS'',''APPLIED'',DECODE(xteu.entity_id,xah.entity_id,''SOURCE'',''APPLIED''))
939 ,xah.entity_id
940 ,xet.event_class_code
941 ,xah.application_id
942 ,DECODE(xet.event_class_code,''PREPAYMENT APPLICATIONS'',xteu.entity_id,DECODE(xteu.entity_id, xah.entity_id,NULL,xteu.entity_id))
943 ,xah.accounting_date
944 ,xal.currency_code
945 ,xal.code_combination_id
946 ,DECODE(fsav.balancing_segment,
947 ''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
948 ''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
949 ''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
950 ''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
951 ''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
952 ''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
953 ''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
954 ''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
955 ''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
956 ''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
957 null)
958 ,DECODE(fsav.account_segment,
959 ''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
960 ''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
961 ''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
962 ''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
963 ''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
964 ''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
965 ''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
966 ''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
967 ''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
968 ''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
969 null)
970 ,DECODE(fsav.cost_crt_segment,
971 ''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
972 ''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
973 ''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
974 ''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
975 ''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
976 ''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
977 ''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
978 ''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
979 ''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
980 ''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
981 null)
982 ,DECODE(fsav.intercompany_segment,
983 ''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
984 ''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
985 ''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
986 ''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
987 ''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
988 ''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
989 ''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
990 ''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
991 ''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
992 ''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
993 null)
994 ,DECODE(fsav.management_segment,
995 ''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
996 ''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
997 ''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
998 ''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
999 ''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
1000 ''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
1001 ''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
1002 ''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
1003 ''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
1004 ''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
1005 null)
1006 ,xah.ledger_id
1007 ,xtd.definition_code
1008 ,xal.party_id
1009 ,xal.party_site_id
1010 ,xal.party_type_code
1011 ,xah.ae_header_id
1012 ';
1013
1014 -- added $l_from$,$l_where$ to dynamically build code for Flex/Segment TB Def Code. bug: 7619431
1015 -- commented code from the above SQL
1016 /*
1017 Tables removed from FROM clause:
1018 --------------------------------
1019 ,xla_tb_defn_details xdd
1020 ,xla_tb_defn_je_sources xjs
1021 ,xla_subledgers xsu
1022 */
1023 -- Conditions removed from WHERE clause:
1024 -------------------------------------
1025 /* AND NVL(alb.ae_header_id, alb.sle_header_id) = xah.completion_acct_seq_value
1026 AND NVL2(alb.ae_header_id,200, alb.journal_sequence_id) = xah.completion_acct_seq_version_id
1027 AND NVL2(alb.ae_header_id, alb.ae_line_id,alb.sle_line_num) = xal.ae_line_num
1028 AND (
1029 (alb.ae_header_id IS NOT NULL AND xah.upg_source_application_id = 200)
1030 OR
1031 (alb.ae_header_id IS NULL AND xah.upg_source_application_id = 600 AND xah.upg_batch_id = -5672)
1032 )
1033 */
1034 /*
1035 AND xtd.definition_code = xdd.definition_code
1036 AND xtd.definition_code = :4
1037 AND xtd.definition_code = xjs.definition_code
1038 AND xtd.enabled_flag = ''Y''
1039 AND xjs.je_source_name = xsu.je_source_name
1040 AND xsu.application_id = 200
1041 AND alb.code_combination_id = xdd.code_combination_id
1042 */
1043 --end bug 6704677
1044
1045
1046 --
1047 -- Template for upgraded transactions
1048 --
1049 C_TB_UPG_SQL CONSTANT VARCHAR2(32000) := '
1050 INSERT INTO xla_trial_balances (
1051 record_type_code
1052 ,source_entity_id
1053 ,event_class_code
1054 ,source_application_id
1055 ,applied_to_entity_id
1056 ,gl_date
1057 ,trx_currency_code
1058 ,entered_rounded_dr
1059 ,entered_rounded_cr
1060 ,entered_unrounded_dr
1061 ,entered_unrounded_cr
1062 ,acctd_rounded_dr
1063 ,acctd_rounded_cr
1064 ,acctd_unrounded_dr
1065 ,acctd_unrounded_cr
1066 ,code_combination_id
1067 ,balancing_segment_value
1068 ,natural_account_segment_value
1069 ,cost_center_segment_value
1070 ,intercompany_segment_value
1071 ,management_segment_value
1072 ,ledger_id
1073 ,definition_code
1074 ,party_id
1075 ,party_site_id
1076 ,party_type_code
1077 ,ae_header_id
1078 ,generated_by_code
1079 ,creation_date
1080 ,created_by
1081 ,last_update_date
1082 ,last_updated_by
1083 ,last_update_login
1084 ,request_id
1085 ,program_application_id
1086 ,program_id
1087 ,program_update_date)
1088 SELECT ''SOURCE'' record_type_code
1089 ,-1 source_entity_id
1090 ,''-1'' event_class_code
1091 ,xsu.application_id source_application_id
1092 ,NULL applied_to_entity_id
1093 ,xdd.balance_date gl_date
1094 ,:1 trx_currency_code
1095 ,$ent_rounded_amt_dr$ entered_rounded_dr
1096 ,$ent_rounded_amt_cr$ entered_rounded_cr
1097 ,$ent_unrounded_amt_dr$ entered_unrounded_dr
1098 ,$ent_unrounded_amt_cr$ entered_unrounded_cr
1099 ,$acct_rounded_amt_dr$ acctd_rounded_dr
1100 ,$acct_rounded_amt_cr$ acctd_rounded_cr
1101 ,$acct_unrounded_amt_dr$ acctd_unrounded_dr
1102 ,$acct_unrounded_amt_cr$ acctd_unrounded_cr
1103 ,xdd.code_combination_id code_combination_id
1104 ,$bal_segment$ balancing_segment_value
1105 ,$acct_segment$ natural_account_segment_value
1106 ,$cc_segment$ cost_center_segment_value
1107 ,$ic_segment$ intercompany_segment_value
1108 ,$mgt_segment$ management_segment_value
1109 ,:2 ledger_id
1110 ,:3 definition_code
1111 ,NULL party_id
1112 ,NULL party_site_id
1113 ,NULL party_type_code
1114 ,NULL ae_header_id
1115 ,''SYSTEM'' generated_by_code
1116 ,SYSDATE creation_date
1117 ,:4 -- g_user_id
1118 ,SYSDATE
1119 ,:5 -- g_user_id
1120 ,:6 -- g_login_id
1121 ,:7 -- g_request_id
1122 ,:8 -- g_prog_appl_id
1123 ,:9 -- g_program_id
1124 ,sysdate
1125 FROM
1126 gl_code_combinations gcc
1127 ,xla_subledgers xsu
1128 ,xla_tb_defn_je_sources xjs
1129 ,xla_tb_defn_details xdd
1130 WHERE xdd.definition_code = :10
1131 AND xdd.owner_code = ''S''
1132 and xdd.code_combination_id = gcc.code_combination_id
1133 AND xsu.je_source_name = xjs.je_source_name
1134 AND xjs.owner_code = ''S''
1135 AND xjs.definition_code = :11
1136 AND gcc.chart_of_accounts_id = :12
1137 ';
1138 -- end of C_TB_UPG_SQL
1139
1140 --
1141 -- Global Constants
1142 --
1143 C_NUM_OF_WORKERS CONSTANT NUMBER := 1;
1144 --NVL(fnd_profile.value ('XLA_TB_DM_NUM_OF_WORKERS'),0);
1145
1146 C_WORK_UNIT CONSTANT NUMBER := 1000;
1147 --NVL(fnd_profile.value ('XLA_TB_DM_WORK_UNIT'),0);
1148
1149 -- process status
1150
1151 C_PROCESSED CONSTANT VARCHAR2(30) := 'PROCESSED';
1152 C_DISABLED CONSTANT VARCHAR2(30) := 'DISABLED';
1153
1154 -- Work Unit
1155 C_WU_UNPROCESSED CONSTANT VARCHAR2(30) := 'UNPROCESSED';
1156 C_WU_PROCESSED CONSTANT VARCHAR2(30) := 'PROCESSED';
1157 C_WU_PROCESSING CONSTANT VARCHAR2(30) := 'PROCESSING';
1158
1159 -- definition status
1160 C_DEF_NEW CONSTANT VARCHAR2(30) := 'NEW';
1161 C_DEF_RELOAD CONSTANT VARCHAR2(30) := 'RELOAD';
1162 C_DEF_PROCESSED CONSTANT VARCHAR2(30) := 'PROCESSED';
1163
1164 C_NEW_LINE CONSTANT VARCHAR2(8) := fnd_global.newline;
1165 C_SPECIAL_STRING CONSTANT VARCHAR2(4) := '%#@*';
1166
1167 C_GL_APPS_ID CONSTANT NUMBER(15) := 101;
1168 C_ID_FLEX_CODE CONSTANT VARCHAR2(4) := 'GL#';
1169
1170 C_BALANCE_SEG CONSTANT VARCHAR2(30) := 'GL_BALANCING';
1171 C_ACCOUNT_SEG CONSTANT VARCHAR2(30) := 'GL_ACCOUNT';
1172 C_COST_CENTER_SEG CONSTANT VARCHAR2(30) := 'FA_COST_CTR';
1173 C_INTERCOMPANY_SEG CONSTANT VARCHAR2(30) := 'GL_INTERCOMPANY';
1174 C_MANAGEMENT_SEG CONSTANT VARCHAR2(30) := 'GL_MANAGEMENT';
1175
1176
1177 --
1178 -- Global Variables for Caching
1179 --
1180 TYPE t_array_num15 IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
1181 TYPE t_array_vc30 IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(100);
1182 TYPE t_array_vc30b IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
1183
1184 --
1185 g_array_segment_column t_array_vc30;
1186 g_array_wu_requests t_array_num15;
1187
1188
1189 --=============================================================================
1190 -- *********** Local Trace Routine **********
1191 --=============================================================================
1192 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
1193 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
1194 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
1195 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
1196 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
1197 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
1198
1199 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
1200 C_DEFAULT_MODULE CONSTANT VARCHAR2(240)
1201 := 'xla.plsql.xla_tb_data_manager_pkg';
1202
1203 g_log_level NUMBER;
1204 g_log_enabled BOOLEAN;
1205
1206 PROCEDURE trace
1207 (p_msg IN VARCHAR2
1208 ,p_level IN NUMBER
1209 ,p_module IN VARCHAR2 DEFAULT C_DEFAULT_MODULE) IS
1210 BEGIN
1211
1212 --fnd_file.put_line(FND_FILE.LOG, 'here2');
1213 --fnd_file.put_line(FND_FILE.LOG, 'p_level ' || p_level );
1214 --fnd_file.put_line(FND_FILE.LOG, 'g_log_level ' || g_log_level );
1215
1216 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
1217 fnd_log.message(p_level, p_module);
1218 ELSIF p_level >= g_log_level THEN
1219 fnd_log.string(p_level, p_module, p_msg);
1220 END IF;
1221
1222 EXCEPTION
1223 WHEN xla_exceptions_pkg.application_exception THEN
1224 RAISE;
1225 WHEN OTHERS THEN
1226 xla_exceptions_pkg.raise_message
1227 (p_location => 'xla_tb_data_manager_pkg.trace');
1228 END trace;
1229
1230 /*======================================================================+
1231 | |
1232 | Private Function |
1233 | |
1234 | Dump_Text |
1235 | |
1236 | Dump text into fnd_log_messages. |
1237 | |
1238 +======================================================================*/
1239 PROCEDURE dump_text
1240 (
1241 p_text IN VARCHAR2
1242 )
1243 IS
1244 l_cur_position INTEGER;
1245 l_next_cr_position INTEGER;
1246 l_text_length INTEGER;
1247 l_log_module VARCHAR2 (2000);
1248
1249 BEGIN
1250 IF g_log_enabled THEN
1251 l_log_module := C_DEFAULT_MODULE||'.dump_text';
1252 END IF;
1253
1254 --Dump the SQL command
1255 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1256 l_cur_position := 1;
1257 l_next_cr_position := 0;
1258 l_text_length := LENGTH(p_text);
1259
1260 WHILE l_next_cr_position < l_text_length
1261 LOOP
1262 l_next_cr_position := INSTR( p_text
1263 ,C_NEW_LINE
1264 ,l_cur_position
1265 );
1266
1267 IF l_next_cr_position = 0
1268 THEN
1269 l_next_cr_position := l_text_length;
1270 END IF;
1271
1272 trace
1273 (p_msg => SUBSTR( p_text
1274 ,l_cur_position
1275 ,l_next_cr_position
1276 - l_cur_position
1277 + 1
1278 )
1279 ,p_level => C_LEVEL_STATEMENT
1280 ,p_module => l_log_module);
1281
1282 IF l_cur_position < l_text_length
1283 THEN
1284 l_cur_position := l_next_cr_position + 1;
1285 END IF;
1286 END LOOP;
1287 END IF;
1288
1289 EXCEPTION
1290 WHEN xla_exceptions_pkg.application_exception THEN
1291 RAISE;
1292 WHEN OTHERS THEN
1293 xla_exceptions_pkg.raise_message
1294 (p_location => 'xla_tb_report_pvt.dump_text');
1295 END dump_text;
1296
1297
1298 PROCEDURE delete_tb_log
1299 IS
1300 l_log_module VARCHAR2(240);
1301 BEGIN
1302 IF g_log_enabled THEN
1303 l_log_module := C_DEFAULT_MODULE||'.delete_tb_log';
1304 END IF;
1305 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1306 trace('BEGIN delete_tb_log',C_LEVEL_PROCEDURE,l_Log_module);
1307 END IF;
1308
1309 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1310 trace('Deleting log entry',C_LEVEL_STATEMENT,l_Log_module);
1311 END IF;
1312
1313 DELETE xla_tb_logs
1314 WHERE request_id = g_request_id;
1315
1316
1317 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1318 trace('END delete_tb_log',C_LEVEL_PROCEDURE,l_Log_module);
1319 END IF;
1320 EXCEPTION
1321 WHEN xla_exceptions_pkg.application_exception THEN
1322 RAISE;
1323 WHEN OTHERS THEN
1324 xla_exceptions_pkg.raise_message
1325 (p_location => 'xla_tb_data_manager_pvt.delete_tb_log');
1326 END delete_tb_log;
1327
1328
1329 PROCEDURE define_segment_ranges
1330 (p_definition_code VARCHAR2 ) IS
1331
1332 l_define_by_code VARCHAR2(30);
1333 l_log_module VARCHAR2(240);
1334
1335 l_def_by_seg_sql VARCHAR2(32000);
1336 l_ins_columns VARCHAR2(32000);
1337 l_sel_columns VARCHAR2(32000);
1338 l_tables VARCHAR2(32000);
1339 l_joins VARCHAR2(32000);
1340 l_seg_num VARCHAR2(1);
1341 C_NEW_LINE CONSTANT VARCHAR2(8) := fnd_global.newline;
1342
1343 BEGIN
1344 IF g_log_enabled THEN
1345 l_log_module := C_DEFAULT_MODULE||'.define_segment_ranges';
1346 END IF;
1347
1348 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1349 trace
1350 (p_msg => 'BEGIN of procedure.define_segment_ranges'
1351 ,p_level => C_LEVEL_PROCEDURE
1352 ,p_module =>l_log_module);
1353 trace
1354 (p_msg => 'p_definition_code = '||p_definition_code
1355 ,p_level => C_LEVEL_PROCEDURE
1356 ,p_module =>l_log_module);
1357 END IF;
1358
1359 DELETE FROM xla_tb_def_seg_ranges
1360 WHERE definition_code = p_definition_code;
1361
1362 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1363 trace('# of rows deleted = ' || SQL%ROWCOUNT
1364 ,C_LEVEL_STATEMENT
1365 ,l_Log_module);
1366 END IF;
1367
1368 SELECT defined_by_code
1369 INTO l_define_by_code
1370 FROM xla_tb_definitions_b xtd
1371 WHERE xtd.definition_code = p_definition_code;
1372
1373 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1374 trace('Defined By Code = ' || l_define_by_code
1375 ,C_LEVEL_STATEMENT
1376 ,l_Log_module);
1377 END IF;
1378
1379 IF l_define_by_code = 'FLEXFIELD' THEN
1380
1381 INSERT INTO xla_tb_def_seg_ranges
1382 (definition_code
1383 ,line_num
1384 ,balance_date
1385 ,owner_code
1386 ,segment1_from
1387 ,segment1_to
1388 ,segment2_from
1389 ,segment2_to
1390 ,segment3_from
1391 ,segment3_to
1392 ,segment4_from
1393 ,segment4_to
1394 ,segment5_from
1395 ,segment5_to
1396 ,segment6_from
1397 ,segment6_to
1398 ,segment7_from
1399 ,segment7_to
1400 ,segment8_from
1401 ,segment8_to
1402 ,segment9_from
1403 ,segment9_to
1404 ,segment10_from
1405 ,segment10_to
1406 ,segment11_from
1407 ,segment11_to
1408 ,segment12_from
1409 ,segment12_to
1410 ,segment13_from
1411 ,segment13_to
1412 ,segment14_from
1413 ,segment14_to
1414 ,segment15_from
1415 ,segment15_to
1416 ,segment16_from
1417 ,segment16_to
1418 ,segment17_from
1419 ,segment17_to
1420 ,segment18_from
1421 ,segment18_to
1422 ,segment19_from
1423 ,segment19_to
1424 ,segment20_from
1425 ,segment20_to
1426 ,segment21_from
1427 ,segment21_to
1428 ,segment22_from
1429 ,segment22_to
1430 ,segment23_from
1431 ,segment23_to
1432 ,segment24_from
1433 ,segment24_to
1434 ,segment25_from
1435 ,segment25_to
1436 ,segment26_from
1437 ,segment26_to
1438 ,segment27_from
1439 ,segment27_to
1440 ,segment28_from
1441 ,segment28_to
1442 ,segment29_from
1443 ,segment29_to
1444 ,segment30_from
1445 ,segment30_to)
1446 SELECT tdd.definition_code definition_code
1447 ,ROWNUM
1448 ,tdd.balance_date balance_date
1449 ,tdd.owner_code owner_code
1450 ,gcc.segment1 segment1_from
1451 ,gcc.segment1 segment1_to
1452 ,gcc.segment2 segment2_from
1453 ,gcc.segment2 segment2_to
1454 ,gcc.segment3 segment3_from
1455 ,gcc.segment3 segment3_to
1456 ,gcc.segment4 segment4_from
1457 ,gcc.segment4 segment4_to
1458 ,gcc.segment5 segment5_from
1459 ,gcc.segment5 segment5_to
1460 ,gcc.segment6 segment6_from
1461 ,gcc.segment6 segment6_to
1462 ,gcc.segment7 segment7_from
1463 ,gcc.segment7 segment7_to
1464 ,gcc.segment8 segment8_from
1465 ,gcc.segment8 segment8_to
1466 ,gcc.segment9 segment9_from
1467 ,gcc.segment9 segment9_to
1468 ,gcc.segment10 segment10_from
1469 ,gcc.segment10 segment10_to
1470 ,gcc.segment11 segment11_from
1471 ,gcc.segment11 segment11_to
1472 ,gcc.segment12 segment12_from
1473 ,gcc.segment12 segment12_to
1474 ,gcc.segment13 segment13_from
1475 ,gcc.segment13 segment13_to
1476 ,gcc.segment14 segment14_from
1477 ,gcc.segment14 segment14_to
1478 ,gcc.segment15 segment15_from
1479 ,gcc.segment15 segment15_to
1480 ,gcc.segment16 segment16_from
1481 ,gcc.segment16 segment16_to
1482 ,gcc.segment17 segment17_from
1483 ,gcc.segment17 segment17_to
1484 ,gcc.segment18 segment18_from
1485 ,gcc.segment18 segment18_to
1486 ,gcc.segment19 segment19_from
1487 ,gcc.segment19 segment19_to
1488 ,gcc.segment20 segment20_from
1489 ,gcc.segment20 segment20_to
1490 ,gcc.segment21 segment21_from
1491 ,gcc.segment21 segment21_to
1492 ,gcc.segment22 segment22_from
1493 ,gcc.segment22 segment22_to
1494 ,gcc.segment23 segment23_from
1495 ,gcc.segment23 segment23_to
1496 ,gcc.segment24 segment24_from
1497 ,gcc.segment24 segment24_to
1498 ,gcc.segment25 segment25_from
1499 ,gcc.segment25 segment25_to
1500 ,gcc.segment26 segment26_from
1501 ,gcc.segment26 segment26_to
1502 ,gcc.segment27 segment27_from
1503 ,gcc.segment27 segment27_to
1504 ,gcc.segment28 segment28_from
1505 ,gcc.segment28 segment28_to
1506 ,gcc.segment29 segment29_from
1507 ,gcc.segment29 segment29_to
1508 ,gcc.segment30 segment30_from
1509 ,gcc.segment30 segment30_to
1510 FROM xla_tb_defn_details tdd
1511 ,gl_code_combinations gcc
1512 WHERE tdd.definition_code = p_definition_code
1513 AND gcc.code_combination_id = tdd.code_combination_id;
1514
1515 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1516 trace('# of rows inserted (Flexfield) = ' || SQL%ROWCOUNT
1517 ,C_LEVEL_STATEMENT
1518 ,l_Log_module);
1519 END IF;
1520
1521 ELSE
1522
1523 FOR c_segs IN (SELECT DISTINCT flexfield_segment_code
1524 FROM xla_tb_defn_details
1525 WHERE definition_code = p_definition_code)
1526 LOOP
1527 l_seg_num := SUBSTR(c_segs.flexfield_segment_code,8,2);
1528
1529 --
1530 -- Inserted Columns
1531 --
1532 -- ,segment<n>_from
1533 -- ,segment<n>_to
1534 --
1535 l_ins_columns := l_ins_columns --added for bug9926320
1536 || C_NEW_LINE
1537 || ',segment' || l_seg_num || '_from '
1538 || C_NEW_LINE
1539 || ',segment' || l_seg_num || '_to '
1540 || C_NEW_LINE;
1541
1542 --
1543 -- Selected Columns
1544 --
1545 -- ,tab<n>.segment_value_from segment<n>_from
1546 -- ,tab<n>.segment_value_to segment<n>_to
1547 --
1548 l_sel_columns := l_sel_columns --added for bug9926320
1549 || C_NEW_LINE
1550 || ',tab' || l_seg_num || '.segment_value_from '
1551 || ' segment' || l_seg_num || '_from '
1552 || C_NEW_LINE
1553 || ',tab' || l_seg_num || '.segment_value_to '
1554 || ' segment' || l_seg_num || '_to '
1555 || C_NEW_LINE;
1556 --
1557 -- Selected Tables
1558 --
1559 -- ,xla_tb_defn_details tab<n>
1560 --
1561 l_tables := l_tables --added for bug9926320
1562 || C_NEW_LINE
1563 || ',xla_tb_defn_details tab'|| l_seg_num
1564 || C_NEW_LINE;
1565
1566 --
1567 -- Join Conditions
1568 --
1569 -- AND tab<n>.flexfield_segment_code(+) = 'SEGMENT<n>'
1570 -- AND tab<n>.definition_code(+) = xtd.definition_code
1571 --
1572 l_joins := l_joins --added for bug9926320
1573 || C_NEW_LINE
1574 || ' AND tab' || l_seg_num
1575 || '.flexfield_segment_code(+) = ''SEGMENT'
1576 || l_seg_num ||''''
1577 || C_NEW_LINE
1578 || ' AND tab' || l_seg_num
1579 || '.definition_code(+) = xtd.definition_code '
1580 || C_NEW_LINE;
1581
1582
1583 END LOOP;
1584
1585 l_def_by_seg_sql :=
1586 'INSERT INTO xla_tb_def_seg_ranges
1587 (definition_code
1588 ,line_num '
1589 || l_ins_columns
1590 || ')
1591 SELECT xtd.definition_code
1592 ,ROWNUM '
1593 || l_sel_columns
1594 ||' FROM xla_tb_definitions_b xtd '
1595 || l_tables
1596 ||' WHERE xtd.definition_code = :1 '
1597 || l_joins;
1598
1599 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1600
1601 dump_text(p_text => l_def_by_seg_sql);
1602
1603 END IF;
1604
1605 EXECUTE IMMEDIATE l_def_by_seg_sql USING p_definition_code;
1606
1607 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1608 trace('# of rows inserted (Segment) = ' || SQL%ROWCOUNT
1609 ,C_LEVEL_STATEMENT
1610 ,l_Log_module);
1611 END IF;
1612
1613 END IF;
1614
1615 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1616 trace
1617 (p_msg => '# rows inserted = '||SQL%ROWCOUNT
1618 ,p_level => C_LEVEL_PROCEDURE
1619 ,p_module =>l_log_module);
1620
1621 trace
1622 (p_msg => 'End of procedure.define_segment_ranges'
1623 ,p_level => C_LEVEL_PROCEDURE
1624 ,p_module =>l_log_module);
1625 END IF;
1626
1627 EXCEPTION
1628 WHEN xla_exceptions_pkg.application_exception THEN
1629 RAISE;
1630 WHEN OTHERS THEN
1631 xla_exceptions_pkg.raise_message
1632 (p_location => 'xla_tb_data_manager_pkg.define_segment_ranges');
1633 END define_segment_ranges;
1634
1635 --=============================================================================
1636
1637 --=============================================================================
1638 --
1639 -- Name: get_schema
1640 -- Description: Retrieve the schema name for XLA
1641 --
1642 -- Return: If schema is found, the schema name is returned. Else, null is
1643 -- returned.
1644 --
1645 --=============================================================================
1646 FUNCTION get_schema
1647 RETURN VARCHAR2
1648 IS
1649 l_status VARCHAR2(30);
1650 l_industry VARCHAR2(30);
1651 l_schema VARCHAR2(30);
1652 l_retcode BOOLEAN;
1653
1654 l_log_module VARCHAR2(240);
1655 BEGIN
1656 IF g_log_enabled THEN
1657 l_log_module := C_DEFAULT_MODULE||'.get_schema';
1658 END IF;
1659
1660 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1661 trace(p_msg => 'BEGIN of function get_schema',
1662 p_module => l_log_module,
1663 p_level => C_LEVEL_PROCEDURE);
1664 END IF;
1665
1666 IF (NOT FND_INSTALLATION.get_app_info
1667 (application_short_name => 'XLA'
1668 ,status => l_status
1669 ,industry => l_industry
1670 ,oracle_schema => l_schema)) THEN
1671 l_schema := NULL;
1672 END IF;
1673
1674 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1675 trace(p_msg => 'END of function get_schema',
1676 p_module => l_log_module,
1677 p_level => C_LEVEL_PROCEDURE);
1678 END IF;
1679
1680 RETURN l_schema;
1681 EXCEPTION
1682 WHEN xla_exceptions_pkg.application_exception THEN
1683 RAISE;
1684
1685 WHEN OTHERS THEN
1686 xla_exceptions_pkg.raise_message
1687 (p_location => 'xla_subledgers_f_pkg.get_schema');
1688
1689 END get_schema;
1690
1691
1692 /*------------------------------------------------------------+
1693 | |
1694 | PRIVATE FUNCTION |
1695 | |
1696 | add_partition |
1697 | |
1698 | Add a new partition to the trial balance table. |
1699 | |
1700 +------------------------------------------------------------*/
1701 PROCEDURE add_partition ( p_definition_code VARCHAR2 ) IS
1702 l_schema VARCHAR2(30);
1703 l_log_module VARCHAR2(240);
1704 BEGIN
1705 IF g_log_enabled THEN
1706 l_log_module := C_DEFAULT_MODULE||'.add_partition';
1707 END IF;
1708
1709 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1710 trace('BEGIN add_partition',C_LEVEL_PROCEDURE,l_Log_module);
1711 END IF;
1712
1713 -- Get schema name
1714 l_schema := get_schema;
1715
1716 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1717 trace('p_definition_code = '||p_definition_code,C_LEVEL_STATEMENT,l_Log_module);
1718 trace('Adding a new partition' || p_definition_code ,C_LEVEL_STATEMENT,l_Log_module);
1719 trace('l_schema = ' || l_schema,C_LEVEL_STATEMENT,l_Log_module);
1720 END IF;
1721
1722 -- Add partition.
1723 EXECUTE IMMEDIATE
1724 'ALTER TABLE '||l_schema||'.xla_trial_balances'||' ADD PARTITION '||p_definition_code||
1725 ' VALUES ('''||p_definition_code||''' )';
1726 EXCEPTION
1727 WHEN OTHERS THEN
1728
1729 --
1730 -- Exit when partition p_definition_code already exists.
1731 --
1732 IF SQLCODE = -14312 THEN
1733
1734 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1735 trace('The following partition already exists: ' || p_definition_code
1736 ,C_LEVEL_STATEMENT
1737 ,l_Log_module);
1738 END IF;
1739
1740 NULL;
1741
1742 ELSE
1743 xla_exceptions_pkg.raise_message
1744 (p_location => 'xla_tb_data_manager_pvt.add_partition');
1745 END IF;
1746
1747 END add_partition;
1748
1749
1750 /*------------------------------------------------------------+
1751 | |
1752 | PUBLIC PROCEDURE |
1753 | |
1754 | delete_trial_balances |
1755 | |
1756 | Delete Trial Balances for given apps id and ae_header_id. |
1757 | Used for data fix. |
1758 +------------------------------------------------------------*/
1759 PROCEDURE delete_trial_balances
1760 (p_application_id IN NUMBER
1761 ,p_ae_header_id IN NUMBER)
1762 IS
1763 l_log_module VARCHAR2(240);
1764
1765 BEGIN
1766 IF g_log_enabled THEN
1767 l_log_module := C_DEFAULT_MODULE||'.delete_trial_balances';
1768 END IF;
1769
1770 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1771 trace('BEGIN delete_trial_balances'
1772 ,C_LEVEL_PROCEDURE
1773 ,l_Log_module);
1774 END IF;
1775
1776 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1777 trace('p_application_id = '||p_application_id
1778 ,C_LEVEL_STATEMENT
1779 ,l_Log_module);
1780 END IF;
1781
1782
1783 DELETE xla_trial_balances
1784 WHERE source_application_id = p_application_id
1785 AND ae_header_id = p_ae_header_id;
1786
1787 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1788 trace('# of records deleted '||SQL%ROWCOUNT
1789 ,C_LEVEL_STATEMENT
1790 ,l_Log_module);
1791 END IF;
1792
1793 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1794 trace('END delete_trial_balances'
1795 ,C_LEVEL_PROCEDURE
1796 ,l_Log_module);
1797 END IF;
1798
1799 EXCEPTION
1800 WHEN xla_exceptions_pkg.application_exception THEN
1801 RAISE;
1802 WHEN OTHERS THEN
1803 xla_exceptions_pkg.raise_message
1804 (p_location => 'xla_tb_data_manager_pvt.delete_trial_balances');
1805 END delete_trial_balances;
1806
1807 /*------------------------------------------------------------+
1808 | |
1809 | PUBLIC PROCEDURE |
1810 | |
1811 | delete_trial_balances |
1812 | |
1813 | DELETE Trial Balance Report Non-Setup Data |
1814 | |
1815 +------------------------------------------------------------*/
1816 PROCEDURE delete_trial_balances
1817 (p_definition_code IN VARCHAR2) IS
1818 l_log_module VARCHAR2(240);
1819 BEGIN
1820 IF g_log_enabled THEN
1821 l_log_module := C_DEFAULT_MODULE||'.delete_trial_balances';
1822 END IF;
1823 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1824 trace('BEGIN delete_trial_balances',C_LEVEL_PROCEDURE,l_Log_module);
1825 END IF;
1826
1827 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1828 trace('p_definition_code = '||p_definition_code,C_LEVEL_STATEMENT,l_Log_module);
1829 END IF;
1830
1831 IF g_je_source_name IS NULL THEN
1832
1833 DELETE xla_tb_user_trans_views
1834 WHERE definition_code = p_definition_code;
1835
1836 DELETE xla_tb_work_units
1837 WHERE definition_code = p_definition_code;
1838
1839 DELETE xla_tb_def_seg_ranges
1840 WHERE definition_code = p_definition_code;
1841
1842 DELETE xla_tb_logs
1843 WHERE definition_code = p_definition_code;
1844
1845 ELSE
1846
1847 DELETE xla_tb_logs
1848 WHERE definition_code = p_definition_code
1849 AND je_source_name = g_je_source_name;
1850
1851 DELETE xla_tb_user_trans_views
1852 WHERE definition_code = p_definition_code
1853 AND application_id = g_application_id;
1854
1855 DELETE xla_trial_balances
1856 WHERE definition_code = p_definition_code
1857 AND source_application_id = g_application_id;
1858 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1859 trace('rows deleted'||sql%rowcount,C_LEVEL_PROCEDURE,l_Log_module);
1860 END IF;
1861
1862 END IF;
1863
1864
1865 --DELETE xla_tb_processes
1866 --WHERE definition_code = p_definition_code;
1867
1868 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1869 trace('rows deleted'||sql%rowcount,C_LEVEL_PROCEDURE,l_Log_module);
1870 END IF;
1871
1872
1873
1874 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1875 trace('END delete_trial_balances',C_LEVEL_PROCEDURE,l_Log_module);
1876 END IF;
1877
1878 EXCEPTION
1879 WHEN xla_exceptions_pkg.application_exception THEN
1880 RAISE;
1881 WHEN OTHERS THEN
1882 xla_exceptions_pkg.raise_message
1883 (p_location => 'xla_tb_data_manager_pvt.delete_trial_balances');
1884 END delete_trial_balances;
1885
1886
1887
1888
1889 /*------------------------------------------------------------+
1890 | |
1891 | PRIVATE FUNCTION |
1892 | |
1893 | delete_definition |
1894 | |
1895 | DELETE Trial Balance Report DEFINITION |
1896 | |
1897 +------------------------------------------------------------*/
1898
1899 PROCEDURE delete_definition
1900 (p_definition_code IN VARCHAR2) IS
1901 l_log_module VARCHAR2(240);
1902 BEGIN
1903 IF g_log_enabled THEN
1904 l_log_module := C_DEFAULT_MODULE||'.delete_definition';
1905 END IF;
1906 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1907 trace('BEGIN delete_definition',C_LEVEL_PROCEDURE,l_Log_module);
1908 END IF;
1909
1910 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1911 trace('p_definition_code = '||p_definition_code,C_LEVEL_STATEMENT,l_Log_module);
1912 END IF;
1913
1914 IF g_je_source_name IS NULL THEN
1915
1916 DELETE xla_tb_definitions_b
1917 WHERE definition_code = p_definition_code;
1918
1919 DELETE xla_tb_definitions_tl
1920 WHERE definition_code = p_definition_code;
1921
1922 DELETE xla_tb_defn_details
1923 WHERE definition_code = p_definition_code;
1924
1925 DELETE xla_tb_defn_je_sources
1926 WHERE definition_code = p_definition_code;
1927
1928 DELETE xla_tb_user_trans_views
1929 WHERE definition_code = p_definition_code;
1930
1931 DELETE xla_tb_work_units
1932 WHERE definition_code = p_definition_code;
1933
1934 DELETE xla_tb_def_seg_ranges
1935 WHERE definition_code = p_definition_code;
1936
1937 --DELETE xla_tb_processes
1938 --WHERE definition_code = p_definition_code;
1939
1940 DELETE xla_tb_logs
1941 WHERE definition_code = p_definition_code;
1942
1943 ELSE
1944
1945 DELETE xla_tb_defn_je_sources
1946 WHERE definition_code = p_definition_code
1947 AND je_source_name = g_je_source_name;
1948
1949 DELETE xla_tb_logs
1950 WHERE definition_code = p_definition_code
1951 AND je_source_name = g_je_source_name;
1952
1953 DELETE xla_tb_user_trans_views
1954 WHERE definition_code = p_definition_code
1955 AND application_id = g_application_id;
1956
1957 DELETE xla_trial_balances
1958 WHERE definition_code = p_definition_code
1959 AND source_application_id = g_application_id;
1960
1961 END IF;
1962
1963
1964 --DELETE xla_tb_processes
1965 --WHERE definition_code = p_definition_code;
1966
1967 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1968 trace('END delete_definition',C_LEVEL_PROCEDURE,l_Log_module);
1969 END IF;
1970
1971 EXCEPTION
1972 WHEN xla_exceptions_pkg.application_exception THEN
1973 RAISE;
1974 WHEN OTHERS THEN
1975 xla_exceptions_pkg.raise_message
1976 (p_location => 'xla_tb_data_manager_pvt.delete_definition');
1977 END delete_definition;
1978
1979
1980 /*===========================================================================+
1981 PROCEDURE
1982 drop_partition (Private)
1983
1984 DESCRIPTION
1985 Drop partitions.
1986
1987 SCOPE - PRIVATE
1988
1989 ARGUMENTS
1990
1991
1992 NOTES
1993
1994 +===========================================================================*/
1995
1996 PROCEDURE drop_partition IS
1997 l_log_module VARCHAR2(240);
1998 l_schema VARCHAR2(30);
1999 BEGIN
2000 IF g_log_enabled THEN
2001 l_log_module := C_DEFAULT_MODULE||'.drop_partition';
2002 END IF;
2003 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2004 trace('drop_partition.Begin',C_LEVEL_PROCEDURE,l_Log_module);
2005 END IF;
2006 drop_partition (p_definition_code => g_definition_code);
2007
2008 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2009 trace('drop_partition.End',C_LEVEL_PROCEDURE,l_Log_module);
2010 END IF;
2011
2012 EXCEPTION
2013 WHEN xla_exceptions_pkg.application_exception THEN
2014 RAISE;
2015 WHEN OTHERS THEN
2016 xla_exceptions_pkg.raise_message
2017 (p_location => 'xla_tb_data_manager_pvt.drop_partition');
2018 END drop_partition;
2019
2020 /*===========================================================================+
2021 PROCEDURE
2022 drop_partition (Public)
2023
2024 DESCRIPTION
2025 Drop partitions.
2026
2027 SCOPE - Public
2028
2029 ARGUMENTS
2030 p_definition_code
2031
2032 NOTES
2033 Called from TbReportDefnsAMImpl.java.
2034 +===========================================================================*/
2035 PROCEDURE drop_partition
2036 (p_definition_code IN VARCHAR2)
2037 IS
2038 l_log_module VARCHAR2(240);
2039 l_schema VARCHAR2(30);
2040 BEGIN
2041 IF g_log_enabled THEN
2042 l_log_module := C_DEFAULT_MODULE||'.drop_partition';
2043 END IF;
2044 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2045 trace('Begin of drop_partition',C_LEVEL_PROCEDURE,l_Log_module);
2046 END IF;
2047
2048 l_schema := get_schema;
2049 EXECUTE IMMEDIATE 'ALTER TABLE ' ||l_schema ||'.XLA_TRIAL_BALANCES drop partition '||p_definition_code;
2050
2051 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2052 trace('End of drop_partition',C_LEVEL_PROCEDURE,l_Log_module);
2053 END IF;
2054
2055 EXCEPTION
2056 WHEN xla_exceptions_pkg.application_exception THEN
2057 RAISE;
2058 WHEN OTHERS THEN
2059 xla_exceptions_pkg.raise_message
2060 (p_location => 'xla_tb_data_manager_pvt.drop_partition');
2061 END drop_partition;
2062
2063 /*------------------------------------------------------------+
2064 | |
2065 | PRIVATE FUNCTION |
2066 | |
2067 | get_report_definition |
2068 | |
2069 | Get Trial Balance Report DEFINITION |
2070 | |
2071 +---------------------------eeg---------------------------------*/
2072
2073
2074 FUNCTION get_report_definition
2075 (p_definition_code IN VARCHAR2)
2076 RETURN r_definition_info IS
2077
2078 l_definition_info r_definition_info;
2079 l_log_module VARCHAR2(240);
2080
2081 BEGIN
2082
2083 IF g_log_enabled THEN
2084 l_log_module := C_DEFAULT_MODULE||'.get_report_definition';
2085 END IF;
2086 --
2087 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2088
2089 trace
2090 (p_msg => 'BEGIN of get_report_definition'
2091 ,p_level => C_LEVEL_PROCEDURE
2092 ,p_module => l_log_module);
2093
2094 END IF;
2095
2096 SELECT xtd.definition_code
2097 ,xtd.ledger_id
2098 --,xtd.je_source_name
2099 ,xtd.enabled_flag
2100 ,xtd.balance_side_code
2101 ,xtd.defined_by_code
2102 ,xtd.definition_status_code
2103 ,xtd.owner_code
2104 INTO l_definition_info.definition_code
2105 ,l_definition_info.ledger_id
2106 --,l_definition_info.je_source_name
2107 ,l_definition_info.enabled_flag
2108 ,l_definition_info.balance_side_code
2109 ,l_definition_info.defined_by_code
2110 ,l_definition_info.definition_status_code
2111 ,l_definition_info.owner_code
2112 FROM xla_tb_definitions_b xtd
2113 WHERE xtd.definition_code = p_definition_code;
2114
2115 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2116 trace
2117 (p_msg => 'END of get_report_definition'
2118 ,p_level => C_LEVEL_PROCEDURE
2119 ,p_module => l_log_module);
2120 END IF;
2121
2122 RETURN l_definition_info;
2123
2124 EXCEPTION
2125 WHEN xla_exceptions_pkg.application_exception THEN
2126 RAISE;
2127 WHEN OTHERS THEN
2128 xla_exceptions_pkg.raise_message
2129 (p_location => 'xla_tb_data_manager_pvt.get_report_definition');
2130 END get_report_definition;
2131
2132 PROCEDURE get_worker_info
2133 (p_ledger_id IN VARCHAR2)
2134 IS
2135 l_ledger_info r_ledger_info;
2136 l_log_module VARCHAR2(240);
2137
2138 BEGIN
2139
2140 IF g_log_enabled THEN
2141 l_log_module := C_DEFAULT_MODULE||'.get_worker_info';
2142 END IF;
2143 --
2144 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2145
2146 trace
2147 (p_msg => 'BEGIN of get_worker_info'
2148 ,p_level => C_LEVEL_PROCEDURE
2149 ,p_module => l_log_module);
2150
2151 END IF;
2152
2153 BEGIN
2154 SELECT work_unit
2155 ,num_of_workers
2156 INTO g_work_unit
2157 ,g_num_of_workers
2158 FROM xla_gl_ledgers
2159 WHERE ledger_id = p_ledger_id ;
2160 EXCEPTION
2161 WHEN no_data_found THEN
2162 l_ledger_info := get_ledger_info
2163 (p_ledger_id => p_ledger_id);
2164
2165 xla_exceptions_pkg.raise_message
2166 (p_appli_s_name => 'XLA'
2167 ,p_msg_name => 'XLA_TB_NO_DEF_FOR_LEDGER'
2168 ,p_token_1 => 'LEDGER_NAME'
2169 ,p_value_1 => l_ledger_info.ledger_name);
2170 END;
2171
2172 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2173 trace
2174 (p_msg => 'END of get_worker_info'
2175 ,p_level => C_LEVEL_PROCEDURE
2176 ,p_module => l_log_module);
2177 END IF;
2178
2179
2180 EXCEPTION
2181 WHEN xla_exceptions_pkg.application_exception THEN
2182 RAISE;
2183 WHEN OTHERS THEN
2184 xla_exceptions_pkg.raise_message
2185 (p_location => 'xla_tb_data_manager_pvt.get_worker_info');
2186 END get_worker_info;
2187
2188
2189 FUNCTION get_ledger_info
2190 (p_ledger_id IN NUMBER) RETURN r_ledger_info IS
2191
2192 l_ledger_info r_ledger_info;
2193 l_log_module VARCHAR2(240);
2194
2195 BEGIN
2196
2197 IF g_log_enabled THEN
2198 l_log_module := C_DEFAULT_MODULE||'.get_ledger_info';
2199 END IF;
2200 --
2201 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2202
2203 trace
2204 (p_msg => 'BEGIN of get_ledger_info'
2205 ,p_level => C_LEVEL_PROCEDURE
2206 ,p_module => l_log_module);
2207
2208 END IF;
2209
2210 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2211 trace('p_ledger_id = '|| p_ledger_id,C_LEVEL_STATEMENT,l_log_module);
2212 END IF;
2213
2214 SELECT gl.ledger_id
2215 ,gl.NAME
2216 ,gl.short_name
2217 ,gl.ledger_category_code
2218 ,gl.currency_code
2219 ,gl.chart_of_accounts_id
2220 ,gl.object_type_code
2221 INTO l_ledger_info.ledger_id
2222 ,l_ledger_info.ledger_name
2223 ,l_ledger_info.ledger_short_name
2224 ,l_ledger_info.ledger_category_code
2225 ,l_ledger_info.currency_code
2226 ,l_ledger_info.coa_id
2227 ,l_ledger_info.object_type_code
2228 FROM gl_ledgers gl
2229 WHERE gl.ledger_id = p_ledger_id;
2230
2231
2232 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2233 trace
2234 (p_msg => 'END of get_ledger_info'
2235 ,p_level => C_LEVEL_PROCEDURE
2236 ,p_module => l_log_module);
2237 END IF;
2238
2239 RETURN l_ledger_info;
2240
2241 EXCEPTION
2242 WHEN xla_exceptions_pkg.application_exception THEN
2243 RAISE;
2244 WHEN OTHERS THEN
2245 xla_exceptions_pkg.raise_message
2246 (p_location => 'xla_tb_data_manager_pvt.get_ledger_info');
2247 END get_ledger_info;
2248
2249 /*------------------------------------------------------------+
2250 |
2251 | PRIVATE FUNCTION
2252 |
2253 | get_ledger_where
2254 |
2255 | Return join conditions for ledgers and ledger sets.
2256 |
2257 +------------------------------------------------------------*/
2258 FUNCTION get_ledger_where
2259 (p_ledger_id IN NUMBER
2260 ,p_object_type_code IN VARCHAR2)
2261 RETURN VARCHAR2 IS
2262
2263 l_log_module VARCHAR2(240);
2264 l_ledger_where VARCHAR2(2000);
2265
2266 BEGIN
2267
2268 IF g_log_enabled THEN
2269 l_log_module := C_DEFAULT_MODULE||'.get_ledger_where';
2270 END IF;
2271 --
2272 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2273
2274 trace
2275 (p_msg => 'BEGIN of get_ledger_info'
2276 ,p_level => C_LEVEL_PROCEDURE
2277 ,p_module => l_log_module);
2278
2279 END IF;
2280
2281 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2282 trace('p_ledger_id = '|| p_ledger_id,C_LEVEL_STATEMENT,l_log_module);
2283 END IF;
2284
2285 IF p_object_type_code = 'S' THEN
2286 l_ledger_where := ' AND xah.ledger_id IN
2287 (SELECT gl.ledger_id
2288 FROM gl_ledgers gl
2289 ,gl_ledger_set_assignments sa
2290 WHERE gl.ledger_id = sa.ledger_id
2291 AND sa.ledger_set_id = :9) ';
2292 ELSE
2293 l_ledger_where := ' AND xah.ledger_id = :9 ';
2294 END IF;
2295
2296
2297
2298 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2299 trace
2300 (p_msg => 'END of get_ledger_where'
2301 ,p_level => C_LEVEL_PROCEDURE
2302 ,p_module => l_log_module);
2303 END IF;
2304
2305 RETURN l_ledger_where;
2306
2307 EXCEPTION
2308 WHEN xla_exceptions_pkg.application_exception THEN
2309 RAISE;
2310 WHEN OTHERS THEN
2311 xla_exceptions_pkg.raise_message
2312 (p_location => 'xla_tb_data_manager_pvt.get_ledger_where');
2313 END get_ledger_where;
2314
2315
2316 /*------------------------------------------------------------+
2317 |
2318 | PRIVATE FUNCTION
2319 |
2320 | get_je_source_info
2321 |
2322 | Derive information related TO THE JE SOURCE.
2323 |
2324 +------------------------------------------------------------*/
2325 FUNCTION get_je_source_info (p_je_source_name VARCHAR2)
2326 RETURN NUMBER IS
2327
2328 BEGIN
2329
2330 SELECT application_id
2331 INTO g_application_id
2332 FROM xla_subledgers
2333 WHERE je_source_name = g_je_source_name;
2334
2335 RETURN g_application_id;
2336
2337 EXCEPTION
2338 WHEN too_many_rows THEN
2339 xla_exceptions_pkg.raise_message
2340 (p_location => 'More than one applications is associated with the
2341 JE SOURCE ' || g_je_source_name);
2342 WHEN xla_exceptions_pkg.application_exception THEN
2343 RAISE;
2344 WHEN OTHERS THEN
2345 xla_exceptions_pkg.raise_message
2346 (p_location => 'xla_tb_data_manager_pvt.get_je_source_info');
2347
2348 END get_je_source_info;
2349
2350 /*------------------------------------------------------------+
2351 | |
2352 | PRIVATE FUNCTION |
2353 | |
2354 | get_segment_columns |
2355 | |
2356 | Returns a SEGMENT COLUMN NAME FOR SEGMENT NAMES |
2357 | |
2358 +------------------------------------------------------------*/
2359 PROCEDURE get_segment_columns
2360 (p_coa_id IN NUMBER
2361 ,p_bal_segment_column OUT NOCOPY VARCHAR2
2362 ,p_acct_segment_column OUT NOCOPY VARCHAR2
2363 ,p_cc_segment_column OUT NOCOPY VARCHAR2
2364 ,p_ic_segment_column OUT NOCOPY VARCHAR2
2365 ,p_mgt_segment_column OUT NOCOPY VARCHAR2)
2366 IS
2367
2368 l_bal_segment_column VARCHAR2(30);
2369 l_acct_segment_column VARCHAR2(30);
2370 l_cc_segment_column VARCHAR2(30);
2371 l_ic_segment_column VARCHAR2(30);
2372 l_mgt_segment_column VARCHAR2(30);
2373
2374 l_log_module VARCHAR2(240);
2375
2376 BEGIN
2377
2378 IF g_log_enabled THEN
2379 l_log_module := C_DEFAULT_MODULE||'.get_segment_columns';
2380 END IF;
2381 --
2382 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2383
2384 trace
2385 (p_msg => 'BEGIN of get_segment_columns'
2386 ,p_level => C_LEVEL_PROCEDURE
2387 ,p_module => l_log_module);
2388
2389 END IF;
2390
2391 l_bal_segment_column := 'gcc.' ||
2392 xla_flex_pkg.get_qualifier_segment
2393 (p_application_id => 101
2394 ,p_id_flex_code => 'GL#'
2395 ,p_id_flex_num => p_coa_id
2396 ,p_qualifier_segment => C_BALANCE_SEG);
2397
2398 l_acct_segment_column := 'gcc.' ||
2399 xla_flex_pkg.get_qualifier_segment
2400 (p_application_id => 101
2401 ,p_id_flex_code => 'GL#'
2402 ,p_id_flex_num => p_coa_id
2403 ,p_qualifier_segment => C_ACCOUNT_SEG);
2404
2405 l_cc_segment_column := 'gcc.' ||
2406 xla_flex_pkg.get_qualifier_segment
2407 (p_application_id => 101
2408 ,p_id_flex_code => 'GL#'
2409 ,p_id_flex_num => p_coa_id
2410 ,p_qualifier_segment => C_COST_CENTER_SEG);
2411
2412 l_ic_segment_column := 'gcc.' ||
2413 xla_flex_pkg.get_qualifier_segment
2414 (p_application_id => 101
2415 ,p_id_flex_code => 'GL#'
2416 ,p_id_flex_num => p_coa_id
2417 ,p_qualifier_segment => C_INTERCOMPANY_SEG);
2418
2419 l_mgt_segment_column := 'gcc.' ||
2420 xla_flex_pkg.get_qualifier_segment
2421 (p_application_id => 101
2422 ,p_id_flex_code => 'GL#'
2423 ,p_id_flex_num => p_coa_id
2424 ,p_qualifier_segment => C_MANAGEMENT_SEG);
2425
2426 IF l_bal_segment_column = 'gcc.' THEN
2427 l_bal_segment_column := 'NULL';
2428 END IF;
2429 IF l_acct_segment_column = 'gcc.' THEN
2430 l_acct_segment_column := 'NULL';
2431 END IF;
2432 IF l_cc_segment_column = 'gcc.' THEN
2433 l_cc_segment_column := 'NULL';
2434 END IF;
2435 IF l_ic_segment_column = 'gcc.' THEN
2436 l_ic_segment_column := 'NULL';
2437 END IF;
2438 IF l_mgt_segment_column = 'gcc.' THEN
2439 l_mgt_segment_column := 'NULL';
2440 END IF;
2441
2442 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2443
2444 trace('l_bal_segment_column = ' || l_bal_segment_column,C_LEVEL_STATEMENT,l_Log_module);
2445 trace('l_acct_segment_column = ' || l_acct_segment_column,C_LEVEL_STATEMENT,l_Log_module);
2446 trace('l_cc_segment_column = ' || l_cc_segment_column,C_LEVEL_STATEMENT,l_Log_module);
2447 trace('l_ic_segment_column = ' || l_ic_segment_column,C_LEVEL_STATEMENT,l_Log_module);
2448 trace('l_mgt_segment_column = ' || l_mgt_segment_column,C_LEVEL_STATEMENT,l_Log_module);
2449
2450 END IF;
2451
2452 p_bal_segment_column := l_bal_segment_column;
2453 p_acct_segment_column := l_acct_segment_column;
2454 p_cc_segment_column := l_cc_segment_column;
2455 p_ic_segment_column := l_ic_segment_column;
2456 p_mgt_segment_column := l_mgt_segment_column;
2457
2458 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2459 trace
2460 (p_msg => 'END of get_segment_columns'
2461 ,p_level => C_LEVEL_PROCEDURE
2462 ,p_module => l_log_module);
2463 END IF;
2464
2465 EXCEPTION
2466 WHEN xla_exceptions_pkg.application_exception THEN
2467 RAISE;
2468 WHEN OTHERS THEN
2469 xla_exceptions_pkg.raise_message
2470 (p_location => 'xla_tb_data_manager_pvt.get_segment_columns');
2471 END get_segment_columns;
2472
2473
2474 FUNCTION get_segment_clause
2475 (p_ledger_id IN NUMBER
2476 ) RETURN VARCHAR2 IS
2477
2478 C_STRING CONSTANT VARCHAR2(240) :=
2479 ' AND NVL(gcc.$segment$,''0'') BETWEEN NVL(NVL(xsr.$segment$_from, gcc.$segment$),''0'')
2480 AND NVL(NVL(xsr.$segment$_to, gcc.$segment$),''0'') '; --added nvl bug#9501376
2481
2482 CURSOR csr_segments(x_coa_id IN NUMBER) IS
2483
2484 SELECT application_column_name
2485 FROM fnd_id_flex_segments
2486 WHERE application_id = 101
2487 AND id_flex_code = 'GL#'
2488 AND id_flex_num = x_coa_id
2489 AND enabled_flag = 'Y';
2490
2491 l_return_string VARCHAR2(30000);
2492 l_coa_id NUMBER;
2493
2494 BEGIN
2495
2496 SELECT chart_of_accounts_id
2497 INTO l_coa_id
2498 FROM gl_ledgers
2499 WHERE ledger_id = p_ledger_id;
2500
2501 FOR c1 IN csr_segments(l_coa_id) LOOP
2502 l_return_string := l_return_string||
2503 REPLACE(C_STRING,'$segment$',c1.application_column_name);
2504 END LOOP;
2505
2506 RETURN(l_return_string);
2507
2508 EXCEPTION
2509 WHEN xla_exceptions_pkg.application_exception THEN
2510 RAISE;
2511 WHEN OTHERS THEN
2512 xla_exceptions_pkg.raise_message
2513 (p_location => 'xla_tb_data_manager_pvt.get_segment_clause');
2514 END get_segment_clause;
2515
2516
2517 PROCEDURE populate_user_trans_view
2518 (p_definition_code IN VARCHAR2
2519 ,p_ledger_id IN NUMBER
2520 ,p_group_id IN NUMBER
2521 )
2522 IS
2523
2524 CURSOR c_event_class (p_request_id NUMBER) IS
2525 SELECT DISTINCT
2526 xut.application_id
2527 ,xec.entity_code
2528 ,xut.event_class_code
2529 ,xut.reporting_view_name
2530 FROM xla_tb_user_trans_views xut
2531 ,xla_event_classes_b xec
2532 WHERE xut.application_id = xec.application_id
2533 AND xut.event_class_code = xec.event_class_code
2534 AND xut.select_string = '###'
2535 AND xut.request_id = p_request_id
2536 ;
2537
2538 l_application_id NUMBER(15);
2539 l_entity_code VARCHAR2(30);
2540 l_event_class_code VARCHAR2(30);
2541 l_reporting_view_name VARCHAR2(30);
2542 l_select_string VARCHAR2(4000);
2543 l_from_string VARCHAR2(4000);
2544 l_where_string VARCHAR2(4000);
2545
2546 l_log_module VARCHAR2(240);
2547
2548 BEGIN
2549
2550 IF g_log_enabled THEN
2551 l_log_module := C_DEFAULT_MODULE||'.populate_user_trans_view';
2552 END IF;
2553 --
2554 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2555
2556 trace
2557 (p_msg => 'BEGIN of populate_user_trans_view'
2558 ,p_level => C_LEVEL_PROCEDURE
2559 ,p_module => l_log_module);
2560
2561 END IF;
2562
2563 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2564
2565 trace('p_definition_code = ' || p_definition_code
2566 ,C_LEVEL_STATEMENT
2567 ,l_Log_module);
2568
2569 trace('Inserting user transaction views'
2570 ,C_LEVEL_STATEMENT
2571 ,l_Log_module);
2572
2573 END IF;
2574
2575 --
2576 -- Populate user transaction identifiers
2577 --
2578
2579 BEGIN
2580
2581
2582 IF p_definition_code IS NOT NULL THEN
2583
2584 --perf imp 13-may-2008
2585
2586 -- Bug#8333978 Changed the select clause to remove the reference of xla_trial_balances
2587 -- table in the from. To get the definition code and application id
2588 -- used the following tables in the join ie xla_tb_defn_je_sources and
2589 -- xla_subledgers.
2590
2591
2592 INSERT INTO xla_tb_user_trans_views
2593 (definition_code
2594 ,application_id
2595 ,event_class_code
2596 ,reporting_view_name
2597 ,select_string
2598 ,from_string
2599 ,where_string
2600 ,creation_date
2601 ,created_by
2602 ,last_update_date
2603 ,last_updated_by
2604 ,last_update_login
2605 ,request_id
2606 ,program_application_id
2607 ,program_id
2608 ,program_update_date
2609 )
2610 SELECT DISTINCT
2611 xjs.definition_code
2612 ,xsu.application_id
2613 ,xeca.event_class_code
2614 ,xeca.reporting_view_name
2615 ,'###'
2616 ,'###'
2617 ,'###'
2618 ,SYSDATE
2619 ,g_user_id
2620 ,SYSDATE
2621 ,g_user_id
2622 ,g_login_id
2623 ,g_request_id
2624 ,g_prog_appl_id
2625 ,g_program_id
2626 ,SYSDATE
2627 FROM
2628 xla_subledgers xsu,
2629 xla_tb_defn_je_sources xjs,
2630 xla_event_class_attrs xeca
2631 WHERE xeca.event_class_code <> 'MANUAL'
2632 AND xeca.application_id = xsu.application_id
2633 AND xsu.je_source_name = xjs.je_source_name
2634 AND xjs.definition_code = p_definition_code
2635 AND xeca.reporting_view_name IS NOT NULL
2636 AND NOT EXISTS
2637 (
2638 SELECT 'x'
2639 FROM xla_tb_user_trans_views xut
2640 WHERE xut.definition_code = xjs.definition_code
2641 AND xut.application_id = xsu.application_id
2642 AND xut.event_class_code = xeca.event_class_code
2643 );
2644
2645 --perf imp 13-may-2008
2646
2647
2648 ELSE
2649 --
2650 -- p_definition_code is null (from gl_transfer)
2651 --
2652 IF p_group_id IS NOT NULL AND p_ledger_id IS NOT NULL
2653 THEN
2654 INSERT INTO xla_tb_user_trans_views
2655 (definition_code
2656 ,application_id
2657 ,event_class_code
2658 ,reporting_view_name
2659 ,select_string
2660 ,from_string
2661 ,where_string
2662 ,creation_date
2663 ,created_by
2664 ,last_update_date
2665 ,last_updated_by
2666 ,last_update_login
2667 ,request_id
2668 ,program_application_id
2669 ,program_id
2670 ,program_update_date
2671 )
2672 SELECT DISTINCT
2673 xtd.definition_code
2674 ,xah.application_id
2675 ,xet.event_class_code
2676 ,xeca.reporting_view_name
2677 ,'###'
2678 ,'###'
2679 ,'###'
2680 ,SYSDATE
2681 ,g_user_id
2682 ,SYSDATE
2683 ,g_user_id
2684 ,g_login_id
2685 ,g_request_id
2686 ,g_prog_appl_id
2687 ,g_program_id
2688 ,SYSDATE
2689 from xla_ae_headers xah,
2690 xla_event_types_b xet,
2691 xla_event_class_attrs xeca,
2692 xla_tb_definitions_b xtd
2693 WHERE xet.event_class_code <> 'MANUAL'
2694 AND xet.event_type_code = xah.event_type_code
2695 AND xet.event_class_code = xeca.event_class_code
2696 AND xeca.application_id = xet.application_id
2697 AND xah.application_id = xet.application_id
2698 AND xah.ledger_id = xtd.ledger_id
2699 AND xah.ledger_id = p_ledger_id
2700 AND xah.group_id = p_group_id
2701 AND xeca.reporting_view_name IS NOT NULL -- added for bug11770090
2702 AND NOT EXISTS
2703 (
2704 SELECT 'x'
2705 FROM xla_tb_user_trans_views xut
2706 WHERE xut.definition_code = xtd.definition_code
2707 AND xut.application_id = xah.application_id
2708 AND xut.event_class_code = xet.event_class_code
2709 AND xut.event_class_code = xeca.event_class_code
2710 AND xut.application_id = xeca.application_id
2711 );
2712
2713
2714 ELSE
2715
2716 -- Bug#8333978 Changed the select clause to remove the reference of xla_trial_balances
2717 -- table in the from clause. To get the definition code and application id
2718 -- used the following tables in the join ie xla_tb_defn_je_sources and
2719 -- xla_subledgers.
2720
2721 INSERT INTO xla_tb_user_trans_views
2722 (definition_code
2723 ,application_id
2724 ,event_class_code
2725 ,reporting_view_name
2726 ,select_string
2727 ,from_string
2728 ,where_string
2729 ,creation_date
2730 ,created_by
2731 ,last_update_date
2732 ,last_updated_by
2733 ,last_update_login
2734 ,request_id
2735 ,program_application_id
2736 ,program_id
2737 ,program_update_date
2738 )
2739 SELECT DISTINCT
2740 xjs.definition_code
2741 ,xsu.application_id
2742 ,xeca.event_class_code
2743 ,xeca.reporting_view_name
2744 ,'###'
2745 ,'###'
2746 ,'###'
2747 ,SYSDATE
2748 ,g_user_id
2749 ,SYSDATE
2750 ,g_user_id
2751 ,g_login_id
2752 ,g_request_id
2753 ,g_prog_appl_id
2754 ,g_program_id
2755 ,SYSDATE
2756 FROM xla_subledgers xsu,
2757 xla_tb_defn_je_sources xjs,
2758 xla_event_class_attrs xeca
2759 WHERE xeca.event_class_code <> 'MANUAL'
2760 AND xsu.application_id = xeca.application_id
2761 AND xsu.je_source_name = xjs.je_source_name
2762 AND xeca.reporting_view_name IS NOT NULL
2763 AND NOT EXISTS
2764 (SELECT 'x'
2765 FROM xla_tb_user_trans_views xut
2766 WHERE xut.definition_code = xjs.definition_code
2767 AND xut.application_id = xsu.application_id
2768 AND xut.event_class_code = xeca.event_class_code
2769 );
2770
2771 END IF; --for p_group_id IS NOT NULL AND p_ledger_id IS NOT NULL
2772
2773 END IF; -- for p_definition_code IS NOT NULL
2774
2775 /*
2776 21-Aug-2008 Added this exception as part of bug#7304630
2777 Due to concurrency issues a unique constraint error is raised, as this is the last part
2778 of processing all the distinct event_class_codes would already be inserted even if this error is
2779 raised.
2780 */
2781
2782 EXCEPTION
2783 WHEN dup_val_on_index THEN
2784 NULL;
2785
2786 END; -- Exception handling for INSERT
2787
2788 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2789 trace('# of rows inserted = ' || SQL%ROWCOUNT
2790 ,C_LEVEL_STATEMENT
2791 ,l_Log_module);
2792 END IF;
2793
2794 OPEN c_event_class(g_request_id);
2795 LOOP
2796 FETCH c_event_class
2797 INTO l_application_id
2798 ,l_entity_code
2799 ,l_event_class_code
2800 ,l_reporting_view_name;
2801
2802 EXIT WHEN c_event_class%NOTFOUND;
2803
2804 IF l_event_class_code <> 'MANUAL' THEN
2805
2806 xla_report_utility_pkg.get_transaction_id
2807 (p_application_id => l_application_id
2808 ,p_entity_code => l_entity_code
2809 ,p_event_class_code => l_event_class_code
2810 ,p_reporting_view_name => l_reporting_view_name
2811 ,p_select_str => l_select_string
2812 ,p_from_str => l_from_string
2813 ,p_where_str => l_where_string);
2814
2815 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2816
2817 trace
2818 (p_msg => 'l_select_string = ' || l_select_string
2819 ,p_level => C_LEVEL_PROCEDURE
2820 ,p_module => l_log_module);
2821 trace
2822 (p_msg => 'l_from_string = ' || l_from_string
2823 ,p_level => C_LEVEL_PROCEDURE
2824 ,p_module => l_log_module);
2825 trace
2826 (p_msg => 'l_where_string = ' || l_where_string
2827 ,p_level => C_LEVEL_PROCEDURE
2828 ,p_module => l_log_module);
2829
2830 trace('Updating user transaction view...'
2831 ,C_LEVEL_STATEMENT
2832 ,l_Log_module);
2833
2834 END IF;
2835
2836 UPDATE xla_tb_user_trans_views
2837 SET select_string = l_select_string
2838 ,from_string = l_from_string
2839 ,where_string = l_where_string
2840 WHERE request_id = g_request_id
2841 AND application_id = l_application_id
2842 AND event_class_code = l_event_class_code
2843 ;
2844
2845 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2846 trace('# of rows updated = ' || SQL%ROWCOUNT
2847 ,C_LEVEL_STATEMENT
2848 ,l_Log_module);
2849 END IF;
2850
2851 END IF;
2852 END LOOP;
2853 CLOSE c_event_class;
2854
2855 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2856 trace
2857 (p_msg => 'END of populate_user_trans_view'
2858 ,p_level => C_LEVEL_PROCEDURE
2859 ,p_module => l_log_module);
2860 END IF;
2861
2862 EXCEPTION
2863 WHEN xla_exceptions_pkg.application_exception THEN
2864 RAISE;
2865 WHEN OTHERS THEN
2866 xla_exceptions_pkg.raise_message
2867 (p_location => 'xla_tb_data_manager_pvt.populate_user_trans_view');
2868 END populate_user_trans_view;
2869
2870 /*===========================================================================+
2871 PROCEDURE
2872 insert_trial_balance_upg
2873
2874 DESCRIPTION
2875 Insert Trial Balance for a system generated definition code
2876
2877 SCOPE - PRIVATE
2878
2879 ARGUMENTS
2880
2881
2882
2883 NOTES
2884
2885 +===========================================================================*/
2886
2887
2888 PROCEDURE insert_trial_balance_upg
2889 (p_definition_code IN VARCHAR2)
2890 IS
2891
2892 l_defined_by_code xla_tb_definitions_b.defined_by_code%TYPE;
2893 l_sql VARCHAR2(32000);
2894 l_log_module VARCHAR2(240);
2895 l_bal_segment_column VARCHAR2(30);
2896 l_acct_segment_column VARCHAR2(30);
2897 l_cc_segment_column VARCHAR2(30);
2898 l_ic_segment_column VARCHAR2(30);
2899 l_mgt_segment_column VARCHAR2(30);
2900 l_ledger_info r_ledger_info;
2901 l_defn_info r_definition_info;
2902 l_seg_clause VARCHAR2(32000);
2903 BEGIN
2904 IF g_log_enabled THEN
2905 l_log_module := C_DEFAULT_MODULE||'.insert_trial_balance_upg';
2906 END IF;
2907
2908 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2909 trace('insert_trial_balance_upg.Begin',C_LEVEL_PROCEDURE,l_log_module);
2910 END IF;
2911
2912 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2913 trace('p_definition_code = ' || p_definition_code ,C_LEVEL_STATEMENT,l_log_module);
2914 END IF;
2915 -- Initialize Variables
2916 l_sql := C_TB_UPG_SQL;
2917
2918 l_ledger_info := get_ledger_info
2919 (p_ledger_id => g_ledger_id);
2920
2921 get_segment_columns
2922 (p_coa_id => l_ledger_info.coa_id
2923 ,p_bal_segment_column => l_bal_segment_column
2924 ,p_acct_segment_column => l_acct_segment_column
2925 ,p_cc_segment_column => l_cc_segment_column
2926 ,p_ic_segment_column => l_ic_segment_column
2927 ,p_mgt_segment_column => l_mgt_segment_column);
2928
2929 l_sql :=
2930 REPLACE(l_sql,'$bal_segment$',l_bal_segment_column);
2931 l_sql :=
2932 REPLACE(l_sql,'$acct_segment$',l_acct_segment_column);
2933 l_sql :=
2934 REPLACE(l_sql,'$cc_segment$',l_cc_segment_column);
2935 l_sql :=
2936 REPLACE(l_sql,'$ic_segment$',l_ic_segment_column);
2937 l_sql :=
2938 REPLACE(l_sql,'$mgt_segment$',l_mgt_segment_column);
2939
2940 l_defn_info := get_report_definition
2941 (p_definition_code => p_definition_code);
2942
2943 IF l_defn_info.balance_side_code = 'C' THEN
2944
2945 l_sql := REPLACE(l_sql,'$ent_rounded_amt_dr$','NULL');
2946 l_sql := REPLACE(l_sql,'$ent_rounded_amt_cr$','xdd.balance_amount');
2947 l_sql := REPLACE(l_sql,'$ent_unrounded_amt_dr$','NULL');
2948 l_sql := REPLACE(l_sql,'$ent_unrounded_amt_cr$','xdd.balance_amount');
2949 l_sql := REPLACE(l_sql,'$acct_rounded_amt_dr$','NULL');
2950 l_sql := replace(l_sql,'$acct_rounded_amt_cr$','xdd.balance_amount');
2951 l_sql := REPLACE(l_sql,'$acct_unrounded_amt_dr$','NULL');
2952 l_sql := REPLACE(l_sql,'$acct_unrounded_amt_cr$','xdd.balance_amount');
2953
2954 ELSIF l_defn_info.balance_side_code = 'D' THEN
2955
2956 l_sql := REPLACE(l_sql,'$ent_rounded_amt_dr$','xdd.balance_amount');
2957 l_sql := REPLACE(l_sql,'$ent_rounded_amt_cr$','NULL');
2958 l_sql := REPLACE(l_sql,'$ent_unrounded_amt_dr$','xdd.balance_amount');
2959 l_sql := REPLACE(l_sql,'$ent_unrounded_amt_cr$','NULL');
2960 l_sql := REPLACE(l_sql,'$acct_rounded_amt_dr$','xdd.balance_amount');
2961 l_sql := replace(l_sql,'$acct_rounded_amt_cr$','NULL');
2962 l_sql := REPLACE(l_sql,'$acct_unrounded_amt_dr$','xdd.balance_amount');
2963 l_sql := REPLACE(l_sql,'$acct_unrounded_amt_cr$','NULL');
2964
2965 END IF;
2966
2967 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2968 trace('l_sql after replace = ' ||
2969 substr(l_sql,1,3000),C_LEVEL_STATEMENT,l_Log_module);
2970 trace('l_sql after replace = ' ||
2971 substr(l_sql,3001,6000),C_LEVEL_STATEMENT,l_Log_module);
2972 trace('l_sql after replace = ' ||
2973 substr(l_sql,6001,9000),C_LEVEL_STATEMENT,l_Log_module);
2974 END IF;
2975
2976 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2977 trace('Inserting trial balances - Upgrade ',C_LEVEL_STATEMENT,l_Log_module);
2978 END IF;
2979
2980 --
2981 -- Execute INSERT statement
2982 --
2983 EXECUTE IMMEDIATE l_sql
2984 USING l_ledger_info.currency_code
2985 ,l_ledger_info.ledger_id
2986 ,p_definition_code
2987 ,g_user_id
2988 ,g_user_id
2989 ,g_login_id
2990 ,g_request_id
2991 ,g_prog_appl_id
2992 ,g_program_id
2993 ,p_definition_code
2994 ,p_definition_code
2995 ,l_ledger_info.coa_id
2996 ;
2997
2998 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2999 trace('Number of rows inserted = ' || SQL%ROWCOUNT,C_LEVEL_STATEMENT,l_log_module);
3000 END IF;
3001
3002 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3003 trace('l_sql = ' || l_sql,C_LEVEL_STATEMENT,l_log_module);
3004 END IF;
3005
3006 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3007 trace('insert_trial_balance_upg.End',C_LEVEL_PROCEDURE,l_log_module);
3008 END IF;
3009
3010 EXCEPTION
3011 WHEN xla_exceptions_pkg.application_exception THEN
3012 IF (C_LEVEL_UNEXPECTED >= g_log_level) THEN
3013 trace('Unexpected error in insert_trial_balance_upg'
3014 ,C_LEVEL_UNEXPECTED
3015 ,l_log_module);
3016 END IF;
3017 RAISE;
3018 WHEN OTHERS THEN
3019 xla_exceptions_pkg.raise_message
3020 (p_location => 'xla_tb_data_manager_pvt.insert_trial_balance_upg');
3021 END insert_trial_balance_upg;
3022
3023 /*===========================================================================+
3024 PROCEDURE
3025 insert_trial_balance_def
3026
3027 DESCRIPTION
3028 Insert Trial Balance for a specific definition code
3029
3030 SCOPE - PRIVATE
3031
3032 ARGUMENTS
3033
3034
3035
3036 NOTES
3037
3038 +===========================================================================*/
3039
3040
3041 PROCEDURE insert_trial_balance_def
3042 (p_definition_code IN VARCHAR2
3043 ,p_application_id IN NUMBER DEFAULT NULL -- for Data Fix
3044 ,p_from_header_id IN NUMBER -- 8761772
3045 ,p_to_header_id IN NUMBER -- 8761772
3046 ) IS
3047
3048 l_defined_by_code xla_tb_definitions_b.defined_by_code%TYPE;
3049 l_owner_code xla_tb_definitions_b.owner_code%TYPE;
3050 l_sql VARCHAR2(32000);
3051 l_upg_sql VARCHAR2(32000);
3052 l_from VARCHAR2(4000);
3053 l_where VARCHAR2(4000);
3054 l_ledger_where VARCHAR2(4000);
3055 l_log_module VARCHAR2(240);
3056 l_bal_segment_column VARCHAR2(30);
3057 l_acct_segment_column VARCHAR2(30);
3058 l_cc_segment_column VARCHAR2(30);
3059 l_ic_segment_column VARCHAR2(30);
3060 l_mgt_segment_column VARCHAR2(30);
3061 l_ledger_info r_ledger_info;
3062 l_seg_clause VARCHAR2(32000);
3063 l_post_programs_where VARCHAR2(32000);
3064 l_application_id PLS_INTEGER;
3065 l_derived_primary_ledger PLS_INTEGER; --bug#7717479
3066 BEGIN
3067 IF g_log_enabled THEN
3068 l_log_module := C_DEFAULT_MODULE||'.insert_trial_balance_def';
3069 END IF;
3070
3071 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3072 trace('Begin of insert_trial_balance_def',C_LEVEL_PROCEDURE,l_log_module);
3073 END IF;
3074
3075 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3076 trace('p_definition_code = ' || p_definition_code ,C_LEVEL_STATEMENT,l_log_module);
3077 trace('p_from_header_id = ' || p_from_header_id ,C_LEVEL_STATEMENT,l_log_module);
3078 trace('p_to_header_id = ' || p_to_header_id ,C_LEVEL_STATEMENT,l_log_module);
3079 END IF;
3080
3081
3082 --29-may-2008 change remodeling bug#7109823 dynamic building of where clause
3083 -- for accounting_class_code defined for program code OPEN_ACCT_BAL_DATA_MGR_
3084 --for a given application
3085 -- Modified the code below to consider accounting class codes of
3086 -- all applications bug#7600550 remodeling phase 4
3087
3088 l_post_programs_where := ' AND xal.accounting_class_code IN (NULL';
3089
3090
3091 FOR i IN ( SELECT xsu.application_id
3092 FROM xla_subledgers xsu,
3093 xla_tb_defn_je_sources xjs
3094 WHERE xsu.je_source_name = xjs.je_source_name
3095 AND xjs.definition_code = p_definition_code)
3096 LOOP
3097 l_application_id := i.application_id;
3098
3099 FOR c1 in (
3100 select accounting_class_code
3101 from xla_acct_class_assgns xac, xla_post_acct_progs_b xpa
3102 where xac.program_owner_code = xpa.program_owner_code
3103 and xac.program_code = xpa.program_code
3104 and xac.program_code = 'OPEN_ACCT_BAL_DATA_MGR_'||l_application_id
3105 )
3106 LOOP
3107 l_post_programs_where := l_post_programs_where||
3108 ','''||c1.accounting_class_code||'''';
3109 END LOOP;
3110
3111 END LOOP;
3112
3113 l_post_programs_where := l_post_programs_where||')';
3114
3115 --end bug#7109823
3116
3117 -- Initialize Variables
3118 l_sql := g_tb_insert_sql;
3119
3120 -- bug#7109823
3121 l_sql :=
3122 REPLACE(l_sql,'$l_accounting_class_code_where$',l_post_programs_where);
3123 --bug#7109823
3124
3125 l_ledger_info := get_ledger_info(p_ledger_id => g_ledger_id);
3126 l_upg_sql := C_TB_INSERT_UPG_SQL_AE;
3127
3128 get_segment_columns
3129 (p_coa_id => l_ledger_info.coa_id
3130 ,p_bal_segment_column => l_bal_segment_column
3131 ,p_acct_segment_column => l_acct_segment_column
3132 ,p_cc_segment_column => l_cc_segment_column
3133 ,p_ic_segment_column => l_ic_segment_column
3134 ,p_mgt_segment_column => l_mgt_segment_column);
3135
3136
3137 l_sql :=
3138 REPLACE(l_sql,'$bal_segment$',l_bal_segment_column);
3139 l_sql :=
3140 REPLACE(l_sql,'$acct_segment$',l_acct_segment_column);
3141 l_sql :=
3142 REPLACE(l_sql,'$cc_segment$',l_cc_segment_column);
3143 l_sql :=
3144 REPLACE(l_sql,'$ic_segment$',l_ic_segment_column);
3145 l_sql :=
3146 REPLACE(l_sql,'$mgt_segment$',l_mgt_segment_column);
3147
3148 --
3149 -- If object type is 'S' (Ledger Set) then
3150 -- use joins between gl_ledgers and gl_ledger_set_assignments
3151 -- else
3152 -- use a simple join with gl_ledgers
3153 -- end if
3154 l_ledger_where := get_ledger_where
3155 (p_ledger_id => g_ledger_id
3156 ,p_object_type_code => l_ledger_info.object_type_code);
3157
3158 l_sql := REPLACE(l_sql,'$l_ledger_where$',l_ledger_where);
3159
3160
3161 -- Derive Definition Type
3162 SELECT defined_by_code
3163 ,owner_code
3164 INTO l_defined_by_code
3165 ,l_owner_code
3166 FROM xla_tb_definitions_b
3167 WHERE definition_code = p_definition_code;
3168
3169 IF l_defined_by_code = 'FLEXFIELD' THEN
3170 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3171 trace('Defined by Flexfield = ',C_LEVEL_STATEMENT,l_Log_module);
3172 END IF;
3173
3174 l_from := ',xla_subledgers xsu
3175 ,xla_tb_defn_je_sources xjs
3176 ,xla_tb_defn_details xdd ';
3177
3178 --
3179 -- Owner Cocde = 'C' (User)
3180 --
3181 IF NVL(l_owner_code,'C') = 'C' THEN
3182 l_where := ' AND xtd.definition_code = :10
3183 AND xtd.definition_code = xdd.definition_code
3184 AND xal.code_combination_id = xdd.code_combination_id
3185 AND NVL(xdd.owner_code,''C'')= ''C''
3186 AND xsu.application_id = xah.application_id
3187 AND xsu.je_source_name = xjs.je_source_name
3188 AND NVL(xjs.owner_code,''C'')= ''C''
3189 AND xtd.definition_code = xjs.definition_code ';
3190 ELSE
3191 --
3192 -- Owner Code = 'S' (Oracle) -- Upgraded
3193 -- accounting_date > balance_date (Bug 4931102)
3194 --
3195 l_where := ' AND xtd.definition_code = :10
3196 AND xtd.definition_code = xdd.definition_code
3197 AND xal.code_combination_id = xdd.code_combination_id
3198 AND xsu.application_id = xah.application_id
3199 AND xsu.je_source_name = xjs.je_source_name
3200 AND xtd.definition_code = xjs.definition_code ';
3201
3202 END IF;
3203
3204 l_sql := REPLACE(l_sql,'$l_from$', l_from);
3205 l_sql := REPLACE(l_sql,'$l_where$', l_where);
3206
3207 /* IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3208 trace('l_sql after replace = ' || substr(l_sql,1,2000),C_LEVEL_STATEMENT,l_Log_module);
3209 trace('l_sql after replace = ' || substr(l_sql,2001,4000),C_LEVEL_STATEMENT,l_Log_module);
3210 trace('l_sql after replace = ' || substr(l_sql,4001,6000),C_LEVEL_STATEMENT,l_Log_module);
3211
3212 END IF;
3213 */ -- commented for bug:7619431
3214
3215 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3216 trace('Inserting trial balances - by Flexfield ',C_LEVEL_STATEMENT,l_Log_module);
3217 dump_text(p_text => l_sql); -- added for bug:7619431
3218 END IF;
3219
3220 --
3221 -- Execute INSERT statement
3222 --
3223 EXECUTE IMMEDIATE l_sql
3224 USING g_user_id
3225 ,g_user_id
3226 ,g_login_id
3227 ,g_request_id
3228 ,g_prog_appl_id
3229 ,g_program_id
3230 ,p_from_header_id
3231 ,p_to_header_id
3232 ,g_ledger_id -- :9 in get_ledger_where
3233 ,p_definition_code -- :10 in this procedure
3234 ,l_ledger_info.coa_id -- :coa_id in C_TB_INSERT_SQL
3235 ;
3236
3237 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3238 trace('# of rows inserted for R12 data = ' || SQL%ROWCOUNT,C_LEVEL_STATEMENT,l_log_module);
3239 END IF;
3240
3241 /*Deriving primary ledger bug#7717479 */
3242 FOR i IN
3243 (
3244 SELECT ledger_id
3245 FROM gl_ledgers
3246 WHERE ledger_category_code = 'PRIMARY'
3247 AND configuration_id =
3248 (SELECT configuration_id
3249 FROM gl_ledgers WHERE ledger_id = g_ledger_id )
3250 )
3251 LOOP
3252 l_derived_primary_ledger := i.ledger_id;
3253 END LOOP;
3254
3255 l_derived_primary_ledger := nvl(l_derived_primary_ledger,g_ledger_id);
3256 /*End Deriving primary ledger bug#7717479 */
3257
3258
3259 l_where := ' AND xtd.definition_code = xdd.definition_code
3260 AND xtd.definition_code = :4
3261 AND xtd.definition_code = xjs.definition_code
3262 AND xtd.enabled_flag = ''Y''
3263 AND xjs.je_source_name = xsu.je_source_name
3264 AND xsu.application_id = 200
3265 AND alb.code_combination_id = xdd.code_combination_id
3266 '; -- added for bug:7619431
3267
3268 l_upg_sql := REPLACE(l_upg_sql, '$l_derived_primary_ledger$',l_derived_primary_ledger); -- bug#7717479
3269 l_upg_sql := REPLACE(l_upg_sql,'$l_from$', l_from); -- added for bug:7619431
3270 l_upg_sql := REPLACE(l_upg_sql,'$l_where$', l_where); -- added for bug:7619431
3271
3272 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3273 trace('Inserting trial balances for Upgraded Data - by Flexfield ',C_LEVEL_STATEMENT,l_Log_module);
3274 dump_text(p_text => l_upg_sql); -- added for bug:7619431
3275 END IF;
3276
3277 EXECUTE IMMEDIATE l_upg_sql
3278 USING p_from_header_id
3279 ,p_to_header_id
3280 ,g_ledger_id
3281 ,p_definition_code
3282 ;
3283 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3284 trace('# of rows inserted for Upgraded data = ' || SQL%ROWCOUNT,C_LEVEL_STATEMENT,l_log_module);
3285 END IF;
3286
3287 l_upg_sql := C_TB_INSERT_UPG_SQL_SLE;
3288
3289 l_upg_sql := REPLACE(l_upg_sql, '$l_derived_primary_ledger$',l_derived_primary_ledger); -- bug#7717479
3290 l_upg_sql := REPLACE(l_upg_sql,'$l_from$', l_from); -- added for bug:7619431
3291 l_upg_sql := REPLACE(l_upg_sql,'$l_where$', l_where); -- added for bug:7619431
3292
3293 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3294 trace('Inserting trial balances for AX Upgraded Data - by Flexfield ',C_LEVEL_STATEMENT,l_Log_module);
3295 dump_text(p_text => l_upg_sql); -- added for bug:7619431
3296 END IF;
3297
3298 EXECUTE IMMEDIATE l_upg_sql
3299 USING p_from_header_id
3300 ,p_to_header_id
3301 ,g_ledger_id
3302 ,p_definition_code
3303 ;
3304
3305 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3306 trace('# of rows inserted for AX Upgraded data = ' || SQL%ROWCOUNT,C_LEVEL_STATEMENT,l_log_module);
3307 END IF;
3308
3309 ELSIF l_defined_by_code = 'SEGMENT' THEN
3310 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3311 trace('Definition is defined by Segment ',C_LEVEL_STATEMENT,l_Log_module);
3312 END IF;
3313
3314 l_seg_clause := get_segment_clause(p_ledger_id => g_ledger_id);
3315
3316 l_from := ',xla_subledgers xsu
3317 ,xla_tb_defn_je_sources xjs
3318 ,xla_tb_def_seg_ranges xsr ';
3319
3320 l_where := ' AND xtd.definition_code = :p_definition_code
3321 AND xtd.definition_code = xsr.definition_code
3322 AND xsu.application_id = xah.application_id
3323 AND NVL(:11,xsu.application_id) = xsu.application_id
3324 AND xsu.je_source_name = xjs.je_source_name
3325 AND xtd.definition_code = xjs.definition_code ';
3326
3327
3328 l_sql :=
3329 REPLACE(l_sql,'$l_from$',l_from);
3330 l_sql :=
3331 REPLACE(l_sql,'$l_where$',l_where ||l_seg_clause);
3332
3333 /*IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3334 trace('l_sql after replace = ' || substr(l_sql,1,3000),C_LEVEL_STATEMENT,l_Log_module);
3335 trace('l_sql after replace = ' || substr(l_sql,3001,6000),C_LEVEL_STATEMENT,l_Log_module);
3336 trace('l_sql after replace = ' || substr(l_sql,6001,9000),C_LEVEL_STATEMENT,l_Log_module);
3337 END IF;
3338 */
3339
3340 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3341 trace('Inserting trial balances for R12 Data by Segment ',C_LEVEL_STATEMENT,l_Log_module);
3342 dump_text(p_text => l_sql);
3343 END IF;
3344
3345 EXECUTE IMMEDIATE l_sql
3346 USING g_user_id
3347 ,g_user_id
3348 ,g_login_id
3349 ,g_request_id
3350 ,g_prog_appl_id
3351 ,g_program_id
3352 ,p_from_header_id
3353 ,p_to_header_id
3354 ,g_ledger_id
3355 ,p_definition_code -- :10 in this procedure
3356 ,p_application_id -- :11 in this procedure
3357 ,l_ledger_info.coa_id
3358 ;
3359
3360 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3361 trace('# of rows inserted for R12 Data by Segment = ' || SQL%ROWCOUNT,C_LEVEL_STATEMENT,l_log_module);
3362 END IF;
3363
3364 -- added the following code for bug: 7619431
3365 /*Deriving primary ledger bug#7717479 */
3366 FOR i IN
3367 (
3368 SELECT ledger_id
3369 FROM gl_ledgers
3370 WHERE ledger_category_code = 'PRIMARY'
3371 AND configuration_id =
3372 (SELECT configuration_id
3373 FROM gl_ledgers WHERE ledger_id = g_ledger_id )
3374 )
3375 LOOP
3376 l_derived_primary_ledger := i.ledger_id;
3377 END LOOP;
3378
3379 l_derived_primary_ledger := nvl(l_derived_primary_ledger,g_ledger_id);
3380 /*End Deriving primary ledger bug#7717479 */
3381
3382 l_where := 'AND xtd.definition_code = xsr.definition_code
3383 AND xtd.definition_code = :4
3384 AND xtd.definition_code = xjs.definition_code
3385 AND xjs.je_source_name = xsu.je_source_name
3386 AND xsu.application_id = 200
3387 ';
3388
3389 l_upg_sql := REPLACE(l_upg_sql, '$l_derived_primary_ledger$',l_derived_primary_ledger); -- bug#7717479
3390 l_upg_sql := REPLACE(l_upg_sql,'$l_from$', l_from);
3391 l_upg_sql := REPLACE(l_upg_sql,'$l_where$', l_where || l_seg_clause );
3392
3393 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3394 trace('Inserting trial balances for Upgraded Data - by Segment',C_LEVEL_STATEMENT,l_Log_module);
3395 dump_text(p_text => l_upg_sql);
3396 END IF;
3397
3398 EXECUTE IMMEDIATE l_upg_sql
3399 USING p_from_header_id
3400 ,p_to_header_id
3401 ,g_ledger_id
3402 ,p_definition_code
3403 ;
3404 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3405 trace('# of rows inserted for Upgraded data = ' || SQL%ROWCOUNT,C_LEVEL_STATEMENT,l_log_module);
3406 END IF;
3407
3408 l_upg_sql := C_TB_INSERT_UPG_SQL_SLE;
3409
3410 l_upg_sql := REPLACE(l_upg_sql, '$l_derived_primary_ledger$',l_derived_primary_ledger);
3411 l_upg_sql := REPLACE(l_upg_sql,'$l_from$', l_from);
3412 l_upg_sql := REPLACE(l_upg_sql,'$l_where$', l_where || l_seg_clause);
3413
3414 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3415 trace('Inserting trial balances for AX Upgraded Data - by Segment',C_LEVEL_STATEMENT,l_Log_module);
3416 dump_text(p_text => l_upg_sql);
3417 END IF;
3418
3419 EXECUTE IMMEDIATE l_upg_sql
3420 USING p_from_header_id
3421 ,p_to_header_id
3422 ,g_ledger_id
3423 ,p_definition_code
3424 ;
3425
3426 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3427 trace('# of rows inserted for AX Upgraded data - by Segment = ' || SQL%ROWCOUNT,C_LEVEL_STATEMENT,l_log_module);
3428 END IF;
3429
3430 END IF;
3431
3432
3433
3434 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3435 trace('End of insert_trial_balance_def',C_LEVEL_PROCEDURE,l_log_module);
3436 END IF;
3437
3438 EXCEPTION
3439 WHEN xla_exceptions_pkg.application_exception THEN
3440 IF (C_LEVEL_UNEXPECTED >= g_log_level) THEN
3441 trace('Unexpected error in insert_trial_balance_def'
3442 ,C_LEVEL_UNEXPECTED
3443 ,l_log_module);
3444 END IF;
3445 RAISE;
3446 WHEN OTHERS THEN
3447 xla_exceptions_pkg.raise_message
3448 (p_location => 'xla_tb_data_manager_pvt.insert_trial_balance_def');
3449 END insert_trial_balance_def;
3450
3451 /*------------------------------------------------------------+
3452 | |
3453 | PUBLIC PROCEDURE |
3454 | |
3455 | recreate_trial_balances |
3456 | |
3457 | Delete Trial Balances for given apps id and ae_header_id. |
3458 | And re-extract journal entries to populate trila balances. |
3459 | Used for Data Fix. |
3460 +------------------------------------------------------------*/
3461 PROCEDURE recreate_trial_balances
3462 (p_application_id IN NUMBER
3463 ,p_ae_header_id IN NUMBER)
3464 IS
3465
3466 l_definition_info r_definition_info;
3467 l_array_defn_code t_array_vc30b;
3468 l_log_module VARCHAR2(240);
3469
3470 BEGIN
3471 IF g_log_enabled THEN
3472 l_log_module := C_DEFAULT_MODULE||'.recreate_trial_balances';
3473 END IF;
3474
3475 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3476 trace('BEGIN recreate_trial_balances'
3477 ,C_LEVEL_PROCEDURE
3478 ,l_Log_module);
3479 END IF;
3480
3481 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3482 trace('p_application_id = '||p_application_id
3483 ,C_LEVEL_STATEMENT
3484 ,l_Log_module);
3485 END IF;
3486
3487 --
3488 -- Set global variables for insert_trial_balance_def
3489 --
3490 g_request_id := xla_environment_pkg.g_req_id;
3491 g_user_id := xla_environment_pkg.g_usr_id;
3492 g_login_id := xla_environment_pkg.g_login_id;
3493 g_prog_appl_id := xla_environment_pkg.g_prog_appl_id;
3494 g_program_id := xla_environment_pkg.g_prog_id;
3495 g_tb_insert_sql := C_TB_INSERT_SQL;
3496
3497 DELETE xla_trial_balances
3498 WHERE source_application_id = p_application_id
3499 AND ae_header_id = p_ae_header_id
3500 RETURNING definition_code BULK COLLECT INTO l_array_defn_code;
3501
3502 FOR i IN l_array_defn_code.FIRST .. l_array_defn_code.LAST LOOP
3503
3504 l_definition_info := get_report_definition
3505 (p_definition_code => l_array_defn_code(i));
3506
3507 --
3508 -- Need g_ledger_id in insert_trial_balance_def
3509 --
3510 g_ledger_id := l_definition_info.ledger_id;
3511
3512 insert_trial_balance_def
3513 (p_definition_code => l_array_defn_code(i)
3514 ,p_application_id => p_application_id
3515 ,p_from_header_id => p_ae_header_id
3516 ,p_to_header_id => p_ae_header_id);
3517
3518 END LOOP;
3519
3520 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3521 trace('END recreate_trial_balances'
3522 ,C_LEVEL_PROCEDURE
3523 ,l_Log_module);
3524 END IF;
3525
3526 EXCEPTION
3527 WHEN xla_exceptions_pkg.application_exception THEN
3528 RAISE;
3529 WHEN OTHERS THEN
3530 xla_exceptions_pkg.raise_message
3531 (p_location => 'xla_tb_data_manager_pvt.recreate_trial_balances');
3532 END recreate_trial_balances;
3533
3534
3535
3536 /*===========================================================================+
3537 PROCEDURE
3538 insert_tb_logs
3539
3540 DESCRIPTION
3541
3542
3543 SCOPE - PRIVATE
3544
3545 ARGUMENTS
3546
3547
3548 NOTES
3549
3550 +===========================================================================*/
3551
3552
3553 PROCEDURE insert_tb_logs IS
3554
3555 l_log_module VARCHAR2(240);
3556 BEGIN
3557 IF g_log_enabled THEN
3558 l_log_module := C_DEFAULT_MODULE||'.insert_tb_logs';
3559 END IF;
3560 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3561 trace('insert_tb_logs.Begin',C_LEVEL_PROCEDURE,l_Log_module);
3562 END IF;
3563
3564 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3565 trace('Inserting into the xla_tb_logs table.',C_LEVEL_STATEMENT,l_Log_module);
3566 END IF;
3567
3568 INSERT INTO xla_tb_logs
3569 ( REQUEST_ID
3570 , LEDGER_ID
3571 , GROUP_ID
3572 , PROCESS_MODE_CODE
3573 , DEFINITION_CODE
3574 , DEFINITION_STATUS_CODE
3575 , REQUEST_STATUS_CODE
3576 )
3577 VALUES
3578 (g_request_id
3579 ,g_ledger_id
3580 ,g_group_id
3581 ,g_process_mode_code
3582 ,g_definition_code
3583 ,NULL
3584 ,C_WU_PROCESSING
3585 );
3586
3587 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3588 trace('END insert_tb_logs',C_LEVEL_PROCEDURE,l_Log_module);
3589 END IF;
3590
3591 EXCEPTION
3592 WHEN xla_exceptions_pkg.application_exception THEN
3593 RAISE;
3594 WHEN OTHERS THEN
3595 xla_exceptions_pkg.raise_message
3596 (p_location => 'xla_tb_data_manager_pvt.insert_tb_logs');
3597 END insert_tb_logs;
3598
3599 /*===========================================================================+
3600 PROCEDURE
3601 insert_trial_balance_wu
3602
3603 DESCRIPTION
3604
3605
3606 SCOPE - PRIVATE
3607
3608 ARGUMENTS
3609 p_ledger_id - PRIMARY/secondary ledger identifier.
3610
3611 NOTES
3612
3613 +===========================================================================*/
3614 PROCEDURE insert_trial_balance_wu
3615 (p_from_header_id IN NUMBER -- 8761772
3616 ,p_to_header_id IN NUMBER -- 8761772
3617 ,p_je_source_name IN VARCHAR2 -- pass the je source name
3618 ) IS
3619 l_log_module VARCHAR2(240);
3620 l_sql VARCHAR2(32000);
3621 l_ledger_info r_ledger_info;
3622 l_bal_segment_column VARCHAR2(30);
3623 l_acct_segment_column VARCHAR2(30);
3624 l_cc_segment_column VARCHAR2(30);
3625 l_ic_segment_column VARCHAR2(30);
3626 l_mgt_segment_column VARCHAR2(30);
3627 l_from VARCHAR2(2000);
3628 l_where VARCHAR2(32000);
3629 l_ledger_where VARCHAR2(4000);
3630 l_seg_clause VARCHAR2(32000);
3631 l_application_id PLS_INTEGER;
3632 l_post_programs_where VARCHAR2(32000);
3633 l_group_id NUMBER(15); --bug#7338524
3634
3635 BEGIN
3636 IF g_log_enabled THEN
3637 l_log_module := C_DEFAULT_MODULE||'.insert_trial_balance_wu';
3638 END IF;
3639
3640 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3641 trace('BEGIN insert_trial_balance_wu',C_LEVEL_PROCEDURE,l_Log_module);
3642 END IF;
3643 -- Initialize Variables
3644 l_sql := g_tb_insert_sql;
3645
3646 -- Replaced the hint to improve the performance in GSI instance bug#7213289
3647 --**********************Note****************************************
3648 --Any hint changes made in C_TB_INSERT_SQL replace the same correspondingly
3649 --in the replace statement below. Reason the SQL needs to run without any hints
3650 --to give better performance as observed in GSI instance bug#7213289 with respect
3651 --to the group id join.
3652 --**********************End Note************************************
3653
3654 l_sql := REPLACE(l_sql, '/*+ index(xah XLA_AE_HEADERS_U1) no_index(xal MIS_XLA_AE_LINES_N1) */', ' ');
3655
3656
3657 l_ledger_info := get_ledger_info(p_ledger_id => g_ledger_id);
3658 l_seg_clause := get_segment_clause(p_ledger_id => g_ledger_id);
3659 l_from := ',xla_tb_def_seg_ranges xsr ';
3660 l_where := ' AND xtd.definition_code = xsr.definition_code ';
3661
3662 IF g_group_id IS NOT NULL THEN
3663 l_where := l_where || ' AND xah.group_id = :group_id ';
3664 END IF;
3665
3666
3667 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3668 trace('p_from_header_id = ' || p_from_header_id,C_LEVEL_STATEMENT,l_Log_module);
3669 trace('p_to_header_id = ' || p_to_header_id,C_LEVEL_STATEMENT,l_Log_module);
3670 trace('l_ledger_info.coa_id = ' || l_ledger_info.coa_id,C_LEVEL_STATEMENT,l_Log_module);
3671 trace('g_ledger_id = ' || g_ledger_id,C_LEVEL_STATEMENT,l_Log_module);
3672 END IF;
3673
3674 get_segment_columns
3675 (p_coa_id => l_ledger_info.coa_id
3676 ,p_bal_segment_column => l_bal_segment_column
3677 ,p_acct_segment_column => l_acct_segment_column
3678 ,p_cc_segment_column => l_cc_segment_column
3679 ,p_ic_segment_column => l_ic_segment_column
3680 ,p_mgt_segment_column => l_mgt_segment_column);
3681
3682
3683
3684 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3685 trace('l_sql BEFORE replace = ' || substr(l_sql,1,3000),C_LEVEL_STATEMENT,l_Log_module);
3686 trace('l_sql BEFORE replace = ' || substr(l_sql,3001,6000),C_LEVEL_STATEMENT,l_Log_module);
3687 END IF;
3688
3689
3690 /*bug#7225096 4-Jul-2008 Added this replace as receiving ORA-00911: invalid character error
3691 when this program is spawned from Create Accounting */
3692 --29-may-2008 change remodeling bug#7109823 dynamic building of where clause
3693 -- for accounting_class_code defined for program code OPEN_ACCT_BAL_DATA_MGR_
3694 --for a given application
3695 -- Modified the code below to consider accounting class codes of
3696 -- all applications bug#7600550 remodeling phase 4
3697
3698 l_post_programs_where := ' AND xal.accounting_class_code IN (NULL';
3699
3700 FOR i IN ( SELECT xsu.application_id
3701 FROM xla_subledgers xsu,
3702 (SELECT distinct je_source_name FROM xla_tb_defn_je_sources) xjs
3703 WHERE xsu.je_source_name = xjs.je_source_name
3704 AND xjs.je_source_name = p_je_source_name)
3705 LOOP
3706 l_application_id := i.application_id;
3707
3708 FOR c1 in (
3709 select accounting_class_code
3710 from xla_acct_class_assgns xac, xla_post_acct_progs_b xpa
3711 where xac.program_owner_code = xpa.program_owner_code
3712 and xac.program_code = xpa.program_code
3713 and xac.program_code = 'OPEN_ACCT_BAL_DATA_MGR_'||l_application_id
3714 )
3715 LOOP
3716 l_post_programs_where := l_post_programs_where||
3717 ','''||c1.accounting_class_code||'''';
3718 END LOOP;
3719
3720 END LOOP;
3721
3722 l_post_programs_where := l_post_programs_where||')';
3723
3724 l_sql :=
3725 REPLACE(l_sql,'$l_accounting_class_code_where$',l_post_programs_where);
3726
3727 --end bug#7109823
3728 -- End bug#7225096 4-Jul-2008
3729
3730 l_sql :=
3731 REPLACE(l_sql,'$bal_segment$',l_bal_segment_column);
3732 l_sql :=
3733 REPLACE(l_sql,'$acct_segment$',l_acct_segment_column);
3734 l_sql :=
3735 REPLACE(l_sql,'$cc_segment$',l_cc_segment_column);
3736 l_sql :=
3737 REPLACE(l_sql,'$ic_segment$',l_ic_segment_column);
3738 l_sql :=
3739 REPLACE(l_sql,'$mgt_segment$',l_mgt_segment_column);
3740 l_sql :=
3741 REPLACE(l_sql,'$l_from$',' ,xla_tb_def_seg_ranges xsr ');
3742 l_sql :=
3743 REPLACE(l_sql,'$l_where$',l_where ||l_seg_clause);
3744
3745 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3746 trace('l_sql AFTER replace = ' || substr(l_sql,1,3000),C_LEVEL_STATEMENT,l_Log_module);
3747 trace('l_sql AFTER replace = ' || substr(l_sql,3001,6000),C_LEVEL_STATEMENT,l_Log_module);
3748 trace('l_sql AFTER replace = ' || substr(l_sql,6001,9000),C_LEVEL_STATEMENT,l_Log_module);
3749
3750 END IF;
3751
3752 --
3753 -- If object type is 'S' (Ledger Set) then
3754 -- use joins between gl_ledgers and gl_ledger_set_assignments
3755 -- else
3756 -- use a simple join with gl_ledgers
3757 -- end if
3758 l_ledger_where := get_ledger_where
3759 (p_ledger_id => g_ledger_id
3760 ,p_object_type_code => l_ledger_info.object_type_code);
3761
3762 l_sql := REPLACE(l_sql,'$l_ledger_where$',l_ledger_where);
3763
3764
3765 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3766 trace('Inserting trial balances ',C_LEVEL_STATEMENT,l_Log_module);
3767 END IF;
3768
3769 IF g_group_id IS NOT NULL THEN
3770 /*
3771 bug#7338524 This cursor has been introduced to find the correct group_id
3772 for the header_id passed and lying in xla_tb_work_units but not associated
3773 to the current group_id generated for transfer to GL.
3774 The failed worker units in xla_tb_work_units table is updated with the
3775 current transfer to GL's parent request id in recover_failed_requests procedure.
3776 The ae_header_id of that failed request would be picked up by the worker's and
3777 will be passed to this procedure for inserting into xla_trial_balances table.
3778 Part of recovery is handled here.
3779 */
3780
3781 FOR i in ( SELECT group_id FROM xla_tb_work_units
3782 WHERE FROM_HEADER_ID = p_from_header_id )
3783 LOOP
3784 l_group_id := i.group_id;
3785 END LOOP;
3786
3787 EXECUTE IMMEDIATE l_sql
3788 USING g_user_id
3789 ,g_user_id
3790 ,g_login_id
3791 ,g_request_id
3792 ,g_prog_appl_id
3793 ,g_program_id
3794 ,p_from_header_id
3795 ,p_to_header_id
3796 ,g_ledger_id
3797 , nvl(l_group_id,g_group_id) -- bug#7338524
3798 --,g_group_id
3799 ,l_ledger_info.coa_id
3800 ;
3801 ELSE
3802 EXECUTE IMMEDIATE l_sql
3803 USING g_user_id
3804 ,g_user_id
3805 ,g_login_id
3806 ,g_request_id
3807 ,g_prog_appl_id
3808 ,g_program_id
3809 ,p_from_header_id
3810 ,p_to_header_id
3811 ,g_ledger_id
3812 ,l_ledger_info.coa_id;
3813 END IF;
3814
3815
3816 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3817 trace('Number of rows inserted = ' || SQL%ROWCOUNT,C_LEVEL_STATEMENT,l_Log_module);
3818 END IF;
3819
3820 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3821 trace('END insert_trial_balance_wu',C_LEVEL_PROCEDURE,l_Log_module);
3822 END IF;
3823 EXCEPTION
3824 WHEN xla_exceptions_pkg.application_exception THEN
3825 RAISE;
3826 WHEN OTHERS THEN
3827 xla_exceptions_pkg.raise_message
3828 (p_location => 'xla_tb_data_manager_pvt.insert_trial_balance_wu');
3829 END insert_trial_balance_wu;
3830
3831
3832
3833
3834
3835 /*===========================================================================+
3836 PROCEDURE
3837 RECOVER_BATCH
3838
3839 DESCRIPTION
3840 Performs RECOVERY opration FOR THE previously failed batches.
3841
3842
3843 SCOPE - PRIVATE
3844
3845 EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3846
3847 ARGUMENTS
3848
3849
3850 NOTES
3851
3852 +===========================================================================*/
3853 PROCEDURE recover_failed_requests
3854 (p_ledger_id IN INTEGER
3855 ,p_definition_code IN VARCHAR2
3856 ) IS
3857
3858
3859 l_log_module VARCHAR2(240);
3860 l_process_mode_code VARCHAR2(30);
3861
3862 -- Find a failed request for a specific defintion
3863 CURSOR c_failed_req_def IS
3864 SELECT xtb.request_id, xtb.process_mode_code
3865 FROM xla_tb_logs xtb
3866 ,fnd_concurrent_requests fcr
3867 WHERE xtb.ledger_id = p_ledger_id
3868 AND xtb.definition_code = p_definition_code
3869 AND xtb.request_status_code = 'PROCESSING'
3870 AND xtb.request_id = fcr.request_id
3871 AND fcr.phase_code NOT IN ('R','P','I');
3872
3873 -- Find requests failed for a group_id or not specific to a definition code
3874 CURSOR c_failed_req ( p_ledger_id VARCHAR2 ) IS
3875 SELECT xtb.request_id
3876 FROM xla_tb_logs xtb
3877 ,fnd_concurrent_requests fcr
3878 WHERE xtb.ledger_id = p_ledger_id
3879 AND xtb.definition_code IS NULL
3880 AND xtb.request_status_code = 'PROCESSING'
3881 AND xtb.request_id = fcr.request_id
3882 AND fcr.phase_code NOT IN ('R','P','I');
3883 BEGIN
3884 IF g_log_enabled THEN
3885 l_log_module := C_DEFAULT_MODULE||'.recover_failed_requests';
3886 END IF;
3887 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3888 trace('recover_failed_requests.Begin',C_LEVEL_PROCEDURE,l_Log_module);
3889 END IF;
3890
3891
3892 -- for failed requests, delete old requets from the tb_log table, update
3893 -- the work unit table with new parent request_id, NEW status
3894 -- Ignore request that are either runnning, pending or inactive.
3895 -- Phase Code: R - Running, P - Pending, I - Inactive
3896 -- Check if there is an existing request for the specified criteria.
3897
3898
3899 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3900 trace('Resetting parent request in TB work units. ',C_LEVEL_STATEMENT,l_Log_module);
3901 END IF;
3902
3903 -- Recover request for a specific definition code
3904 IF g_definition_code IS NOT NULL THEN
3905 FOR failed_req_rec IN c_failed_req_def
3906 LOOP
3907
3908 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3909 trace('failed_req_rec - Updating TB work units',C_LEVEL_STATEMENT,l_Log_module);
3910 END IF;
3911
3912 -- Process
3913 UPDATE xla_tb_work_units
3914 SET status_code = C_WU_UNPROCESSED
3915 ,parent_request_id = g_request_id
3916 WHERE definition_code = g_definition_code;
3917
3918 g_wu_count := SQL%ROWCOUNT;
3919
3920 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3921 trace('Work units updated (g_wu_count) = ' || g_wu_count,C_LEVEL_STATEMENT,l_Log_module);
3922 trace('failed_req_rec - Deleting TB logs',C_LEVEL_STATEMENT,l_Log_module);
3923 END IF;
3924
3925 DELETE xla_tb_logs
3926 WHERE request_id = failed_req_rec.request_id;
3927
3928 END LOOP;
3929 ELSE
3930 FOR failed_req_rec IN c_failed_req(p_ledger_id => p_ledger_id)
3931 LOOP
3932 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3933 trace('Updating work units for request id = ' || failed_req_rec.request_id,C_LEVEL_STATEMENT,l_Log_module);
3934 END IF;
3935
3936 UPDATE xla_tb_work_units
3937 SET status_code = C_WU_UNPROCESSED
3938 ,parent_request_id = g_request_id
3939 WHERE parent_request_id = failed_req_rec.request_id;
3940
3941 g_wu_count := SQL%ROWCOUNT;
3942
3943 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3944 trace('Work units updated = ' || g_wu_count,C_LEVEL_STATEMENT,l_Log_module);
3945 trace('Work units updated rowcount = ' || SQL%ROWCOUNT,C_LEVEL_STATEMENT,l_Log_module);
3946 END IF;
3947
3948 DELETE xla_tb_logs
3949 WHERE request_id = failed_req_rec.request_id;
3950 END LOOP;
3951 END IF;
3952
3953 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3954 trace('Deleting entries from TB logs for failed requests.',C_LEVEL_STATEMENT,l_Log_module);
3955 END IF;
3956
3957 DELETE xla_tb_logs xtl
3958 WHERE request_id NOT IN
3959 ( SELECT xtb.request_id
3960 FROM xla_tb_logs xtb
3961 ,fnd_concurrent_requests fcr
3962 WHERE xtb.ledger_id = p_ledger_id
3963 AND nvl(xtb.definition_code,'###') = NVL(p_definition_code,'###')
3964 AND xtb.request_status_code = 'PROCESSING'
3965 AND xtb.request_id = fcr.request_id
3966 AND fcr.phase_code IN ('R','P','I'))
3967 /* bug#7338524 Added this and clause as records of only the current ledger
3968 needs to be deleted from the logs*/
3969 AND ledger_id = p_ledger_id;
3970
3971 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3972 trace('recover_failed_requests.End',C_LEVEL_PROCEDURE,l_Log_module);
3973 END IF;
3974
3975 EXCEPTION
3976 WHEN xla_exceptions_pkg.application_exception THEN
3977 RAISE;
3978 WHEN OTHERS THEN
3979 trace('Recovery failed',C_LEVEL_UNEXPECTED,l_Log_module);
3980 xla_exceptions_pkg.raise_message
3981 (p_location => 'xla_transfer_pkg.get_gllezl_status');
3982 END recover_failed_requests;
3983
3984
3985
3986 /*------------------------------------------------------------+
3987 | |
3988 | PRIVATE FUNCTION |
3989 | |
3990 | update_definition_status |
3991 | |
3992 +------------------------------------------------------------*/
3993 PROCEDURE update_definition_status
3994 (p_definition_code IN VARCHAR2
3995 ,p_status_code IN VARCHAR2) IS
3996
3997 l_log_module VARCHAR2(240);
3998
3999 BEGIN
4000
4001 IF g_log_enabled THEN
4002 l_log_module := C_DEFAULT_MODULE||'.update_definition_status';
4003 END IF;
4004 --
4005 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4006
4007 trace
4008 (p_msg => 'BEGIN of update_definition_status'
4009 ,p_level => C_LEVEL_PROCEDURE
4010 ,p_module => l_log_module);
4011
4012 END IF;
4013
4014 UPDATE xla_tb_definitions_b
4015 SET definition_status_code = p_status_code
4016 ,last_updated_by = g_user_id
4017 ,last_update_date = SYSDATE
4018 ,last_update_login = g_login_id
4019 ,request_id = g_request_id
4020 ,program_application_id = g_prog_appl_id
4021 ,program_id = g_program_id
4022 ,program_update_date = SYSDATE
4023 WHERE definition_code = p_definition_code;
4024
4025 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4026 trace
4027 (p_msg => 'END of update_definition_status'
4028 ,p_level => C_LEVEL_PROCEDURE
4029 ,p_module => l_log_module);
4030 END IF;
4031
4032 EXCEPTION
4033 WHEN xla_exceptions_pkg.application_exception THEN
4034 RAISE;
4035 WHEN OTHERS THEN
4036 xla_exceptions_pkg.raise_message
4037 (p_location => 'xla_tb_data_manager_pvt.update_definition_status');
4038 END update_definition_status;
4039
4040
4041 /*------------------------------------------------------------+
4042 | |
4043 | PRIVATE FUNCTION |
4044 | |
4045 | upload_pvt |
4046 | |
4047 +------------------------------------------------------------*/
4048 PROCEDURE generate_work_units
4049 (p_ledger_id IN NUMBER
4050 ,p_group_id IN NUMBER
4051 ,p_definition_code IN VARCHAR2
4052 ) IS
4053
4054 l_group_id NUMBER(15);
4055 l_upg_batch_id NUMBER(15);
4056 l_ledger_id NUMBER(15);
4057 l_ledger_info r_ledger_info;
4058
4059 l_log_module VARCHAR2(240);
4060 BEGIN
4061 IF g_log_enabled THEN
4062 l_log_module := C_DEFAULT_MODULE||'.generate_work_units';
4063 END IF;
4064
4065 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4066 trace('BEGIN generate_work_units',C_LEVEL_PROCEDURE,l_log_module);
4067 END IF;
4068
4069 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4070 trace('p_ledger_id = ' || p_ledger_id,C_LEVEL_STATEMENT,l_log_module);
4071 trace('p_group_id = ' || p_group_id,C_LEVEL_STATEMENT,l_log_module);
4072 trace('p_definition_code = ' || p_definition_code,C_LEVEL_STATEMENT,l_log_module);
4073 END IF;
4074
4075 l_ledger_info := get_ledger_info(p_ledger_id => p_ledger_id);
4076
4077 IF p_group_id IS NOT NULL THEN
4078 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4079 trace('Generating work units for a group_id = '
4080 || p_group_id,C_LEVEL_STATEMENT,l_log_module);
4081 trace('Ledger Object Type Code = '
4082 || l_ledger_info.object_type_code,C_LEVEL_STATEMENT,l_log_module);
4083 END IF;
4084
4085 IF l_ledger_info.object_type_code = 'S' THEN
4086 --
4087 -- Ledger Set
4088 --
4089 INSERT INTO xla_tb_work_units
4090 (group_id
4091 ,upg_batch_id
4092 ,from_header_id
4093 ,to_header_id
4094 ,status_code
4095 ,parent_request_id
4096 )
4097 SELECT
4098 p_group_id
4099 ,l_upg_batch_id
4100 ,min(ae_header_id)
4101 ,max(ae_header_id)
4102 ,C_WU_UNPROCESSED
4103 ,g_request_id
4104 FROM
4105 (SELECT ae_header_id,
4106 FLOOR
4107 (
4108 sum(count(*)) over
4109 (ORDER BY ae_header_id
4110 ROWS unbounded preceding
4111 )/g_work_unit
4112 ) wu
4113 FROM xla_ae_headers
4114 WHERE group_id = p_group_id
4115 AND ledger_id IN (
4116 SELECT lg.ledger_id
4117 FROM gl_ledgers lg
4118 ,gl_ledger_set_assignments sa
4119 WHERE lg.ledger_id = sa.ledger_id
4120 AND sa.ledger_set_id = p_ledger_id)
4121 GROUP BY ae_header_id
4122 )
4123 GROUP BY wu;
4124
4125
4126 ELSE
4127 --
4128 -- p_ledger_id is not a ledger set
4129 --
4130 INSERT INTO xla_tb_work_units
4131 (group_id
4132 ,upg_batch_id
4133 ,from_header_id
4134 ,to_header_id
4135 ,status_code
4136 ,parent_request_id
4137 )
4138 SELECT
4139 p_group_id
4140 ,l_upg_batch_id
4141 ,min(ae_header_id)
4142 ,max(ae_header_id)
4143 ,C_WU_UNPROCESSED
4144 ,g_request_id
4145 FROM
4146 (SELECT ae_header_id,
4147 FLOOR
4148 (
4149 sum(count(*)) over
4150 (ORDER BY ae_header_id
4151 ROWS unbounded preceding
4152 )/g_work_unit
4153 ) wu
4154 FROM xla_ae_headers
4155 WHERE group_id = p_group_id
4156 AND ledger_id = p_ledger_id
4157 GROUP BY ae_header_id
4158 )
4159 GROUP BY wu;
4160
4161 END IF;
4162
4163 ELSIF p_definition_code IS NOT NULL OR p_group_id IS NULL THEN
4164
4165 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4166 trace('Generating work units for definition code = '||p_definition_code
4167 ,C_LEVEL_STATEMENT
4168 ,l_log_module);
4169 trace('Ledger Object Type Code = '
4170 || l_ledger_info.object_type_code
4171 ,C_LEVEL_STATEMENT
4172 ,l_log_module);
4173 END IF;
4174
4175 --
4176 -- p_gl_date_from is not null only for upgrade on demand
4177 --
4178 IF g_gl_date_from IS NULL THEN
4179 -- Select all entries transferred to GL
4180
4181 IF l_ledger_info.object_type_code = 'S' THEN
4182 --
4183 -- Ledger Set
4184 --
4185 INSERT INTO xla_tb_work_units
4186 (group_id
4187 ,upg_batch_id
4188 ,from_header_id
4189 ,to_header_id
4190 ,status_code
4191 ,parent_request_id
4192 ,definition_code
4193 )
4194 SELECT
4195 NULL
4196 ,l_upg_batch_id
4197 ,min(ae_header_id)
4198 ,max(ae_header_id)
4199 ,C_WU_UNPROCESSED
4200 ,g_request_id
4201 ,p_definition_code
4202 FROM
4203 (SELECT ae_header_id,
4204 FLOOR
4205 (
4206 sum(count(*)) over
4207 (ORDER BY ae_header_id
4208 ROWS unbounded preceding
4209 )/C_WORK_UNIT
4210 ) wu
4211 FROM xla_ae_headers aeh
4212 ,xla_subledgers xsu
4213 ,xla_tb_definitions_b xtd
4214 ,xla_tb_defn_je_sources xjs
4215 WHERE gl_transfer_status_code IN ('Y','NT')
4216 AND aeh.ledger_id IN (
4217 SELECT lg.ledger_id
4218 FROM gl_ledgers lg
4219 ,gl_ledger_set_assignments sa
4220 WHERE lg.ledger_id = sa.ledger_id
4221 AND sa.ledger_set_id = p_ledger_id)
4222 AND xtd.definition_code = p_definition_code
4223 AND xtd.definition_code = xjs.definition_code
4224 AND xjs.je_source_name = xsu.je_source_name
4225 AND aeh.application_id = xsu.application_id
4226 GROUP BY ae_header_id
4227 )
4228 GROUP BY wu;
4229 ELSE
4230 --
4231 -- p_ledger_id is not a ledger set
4232 --
4233 INSERT INTO xla_tb_work_units
4234 (group_id
4235 ,upg_batch_id
4236 ,from_header_id
4237 ,to_header_id
4238 ,status_code
4239 ,parent_request_id
4240 ,definition_code
4241 )
4242 SELECT
4243 NULL
4244 ,l_upg_batch_id
4245 ,min(ae_header_id)
4246 ,max(ae_header_id)
4247 ,C_WU_UNPROCESSED
4248 ,g_request_id
4249 ,p_definition_code
4250 FROM
4251 (SELECT ae_header_id,
4252 FLOOR
4253 (
4254 sum(count(*)) over
4255 (ORDER BY ae_header_id
4256 ROWS unbounded preceding
4257 )/C_WORK_UNIT
4258 ) wu
4259 FROM xla_ae_headers aeh
4260 ,xla_subledgers xsu
4261 ,xla_tb_definitions_b xtd
4262 ,xla_tb_defn_je_sources xjs
4263 WHERE gl_transfer_status_code IN ('Y','NT')
4264 AND aeh.ledger_id = p_ledger_id
4265 AND xtd.definition_code = p_definition_code
4266 AND xtd.definition_code = xjs.definition_code
4267 AND xjs.je_source_name = xsu.je_source_name
4268 AND aeh.application_id = xsu.application_id
4269 GROUP BY ae_header_id
4270 )
4271 GROUP BY wu;
4272 END IF; -- Ledger Set
4273 --
4274 -- Upgrade On Demand
4275 --
4276 ELSE
4277 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4278 trace('Generating work units for definition code (Upgrade) = '||p_definition_code
4279 ,C_LEVEL_STATEMENT
4280 ,l_log_module);
4281 trace('Ledger Object Type Code = '
4282 || l_ledger_info.object_type_code
4283 ,C_LEVEL_STATEMENT
4284 ,l_log_module);
4285 END IF;
4286
4287 IF l_ledger_info.object_type_code = 'S' THEN
4288 --
4289 -- Ledger Set
4290 --
4291 INSERT INTO xla_tb_work_units
4292 (group_id
4293 ,upg_batch_id
4294 ,from_header_id
4295 ,to_header_id
4296 ,status_code
4297 ,parent_request_id
4298 ,definition_code
4299 )
4300 SELECT
4301 NULL
4302 ,l_upg_batch_id
4303 ,min(ae_header_id)
4304 ,max(ae_header_id)
4305 ,C_WU_UNPROCESSED
4306 ,g_request_id
4307 ,p_definition_code
4308 FROM
4309 (SELECT ae_header_id,
4310 FLOOR
4311 (
4312 sum(count(*)) over
4313 (ORDER BY ae_header_id
4314 ROWS unbounded preceding
4315 )/C_WORK_UNIT
4316 ) wu
4317 FROM xla_ae_headers aeh
4318 ,xla_subledgers xsu
4319 ,xla_tb_definitions_b xtd
4320 ,xla_tb_defn_je_sources xjs
4321 WHERE gl_transfer_status_code IN ('Y','NT')
4322 AND aeh.ledger_id IN (
4323 SELECT lg.ledger_id
4324 FROM gl_ledgers lg
4325 ,gl_ledger_set_assignments sa
4326 WHERE lg.ledger_id = sa.ledger_id
4327 AND sa.ledger_set_id = p_ledger_id)
4328 AND xtd.definition_code = p_definition_code
4329 AND xtd.definition_code = xjs.definition_code
4330 AND xjs.je_source_name = xsu.je_source_name
4331 AND xsu.je_source_name = g_je_source_name
4332 AND aeh.application_id = xsu.application_id
4333 AND aeh.accounting_date
4334 >= fnd_date.canonical_to_date(g_gl_date_from)
4335 AND aeh.accounting_date
4336 <= fnd_date.canonical_to_date(g_gl_date_to)
4337 GROUP BY ae_header_id
4338 )
4339 GROUP BY wu;
4340 ELSE
4341 --
4342 -- p_ledger_id is not a ledger set
4343 --
4344 INSERT INTO xla_tb_work_units
4345 (group_id
4346 ,upg_batch_id
4347 ,from_header_id
4348 ,to_header_id
4349 ,status_code
4350 ,parent_request_id
4351 ,definition_code
4352 )
4353 SELECT
4354 NULL
4355 ,l_upg_batch_id
4356 ,min(ae_header_id)
4357 ,max(ae_header_id)
4358 ,C_WU_UNPROCESSED
4359 ,g_request_id
4360 ,p_definition_code
4361 FROM
4362 (SELECT ae_header_id,
4363 FLOOR
4364 (
4365 sum(count(*)) over
4366 (ORDER BY ae_header_id
4367 ROWS unbounded preceding
4368 )/C_WORK_UNIT
4369 ) wu
4370 FROM xla_ae_headers aeh
4371 ,xla_subledgers xsu
4372 ,xla_tb_definitions_b xtd
4373 ,xla_tb_defn_je_sources xjs
4374 WHERE gl_transfer_status_code IN ('Y','NT')
4375 AND aeh.ledger_id = p_ledger_id
4376 AND xtd.definition_code = p_definition_code
4377 AND xtd.definition_code = xjs.definition_code
4378 AND xjs.je_source_name = xsu.je_source_name
4379 AND xsu.je_source_name = g_je_source_name
4380 AND aeh.application_id = xsu.application_id
4381 AND aeh.accounting_date
4382 >= fnd_date.canonical_to_date(g_gl_date_from)
4383 AND aeh.accounting_date
4384 <= fnd_date.canonical_to_date(g_gl_date_to)
4385 GROUP BY ae_header_id
4386 )
4387 GROUP BY wu;
4388 END IF;
4389 END IF; -- p_gl_date_from is null
4390
4391 END IF;
4392
4393 IF nvl(g_wu_count,0) <= 0 THEN
4394 g_wu_count := SQL%ROWCOUNT;
4395 END IF;
4396
4397 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4398 trace('Generated work units = ' || g_wu_count ,C_LEVEL_STATEMENT,l_Log_module);
4399 END IF;
4400
4401 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4402 trace(' END generate_work_units',C_LEVEL_PROCEDURE,l_Log_module);
4403 END IF;
4404 EXCEPTION
4405 WHEN xla_exceptions_pkg.application_exception THEN
4406 RAISE;
4407 WHEN OTHERS THEN
4408 xla_exceptions_pkg.raise_message
4409 (p_location => 'xla_tb_data_manager_pvt.generate_work_units');
4410 END generate_work_units;
4411
4412 /*------------------------------------------------------------+
4413 | |
4414 | PRIVATE FUNCTION |
4415 | |
4416 | Retrieve_Work_Unit |
4417 | |
4418 +------------------------------------------------------------*/
4419 PROCEDURE retrieve_work_unit
4420 (p_from_header_id OUT NOCOPY VARCHAR2
4421 ,p_to_header_id OUT NOCOPY VARCHAR2
4422 ,p_definition_code OUT NOCOPY VARCHAR2
4423 ,p_parent_request_id IN PLS_INTEGER
4424 ) IS
4425
4426 l_log_module VARCHAR2(240);
4427 BEGIN
4428 IF g_log_enabled THEN
4429 l_log_module := C_DEFAULT_MODULE||'.retrieve_work_unit';
4430 END IF;
4431
4432 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4433 trace('BEGIN retrieve_work_unit',C_LEVEL_PROCEDURE,l_log_module);
4434 END IF;
4435
4436 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4437 trace('p_parent_request_id = ' || p_parent_request_id,C_LEVEL_STATEMENT,l_log_module);
4438 trace('Updating tb_work_units.status_code to ' || C_WU_PROCESSING,C_LEVEL_STATEMENT,l_log_module);
4439 END IF;
4440
4441
4442 UPDATE xla_tb_work_units
4443 SET status_code = C_WU_PROCESSING
4444 WHERE parent_request_id = p_parent_request_id
4445 AND status_code = C_WU_UNPROCESSED
4446 AND ROWNUM = 1
4447 RETURNING from_header_id
4448 ,to_header_id
4449 ,definition_code
4450 INTO p_from_header_id, p_to_header_id, p_definition_code;
4451
4452 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4453 trace('Number of work units updated = ' || SQL%ROWCOUNT,C_LEVEL_STATEMENT,l_log_module);
4454 trace('p_from_header_id = ' || p_from_header_id,C_LEVEL_STATEMENT,l_log_module);
4455 trace('p_to_header_id = ' || p_to_header_id,C_LEVEL_STATEMENT,l_log_module);
4456 trace('p_definition_code = ' || p_definition_code,C_LEVEL_STATEMENT,l_log_module);
4457 END IF;
4458
4459 COMMIT;
4460
4461 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4462 trace('END retrieve_work_unit',C_LEVEL_PROCEDURE,l_log_module);
4463 END IF;
4464
4465 EXCEPTION
4466 WHEN xla_exceptions_pkg.application_exception THEN
4467 RAISE;
4468 WHEN OTHERS THEN
4469 xla_exceptions_pkg.raise_message
4470 (p_location => 'xla_tb_data_manager_pvt.retrieve_work_units');
4471 END retrieve_work_unit;
4472
4473
4474
4475 /*======================================================================+
4476 | |
4477 | PRIVATE FUNCTION |
4478 | |
4479 | Upload_Preprocessor |
4480 | |
4481 | |
4482 +======================================================================*/
4483 PROCEDURE upload_preprocessor
4484 (p_ledger_id IN NUMBER) IS
4485
4486 l_log_module VARCHAR2(240);
4487 BEGIN
4488 IF g_log_enabled THEN
4489 l_log_module := C_DEFAULT_MODULE||'.upload_preprocessor';
4490 END IF;
4491 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4492 trace('BEGIN upload_preprocessor',C_LEVEL_PROCEDURE,l_Log_module);
4493 END IF;
4494
4495 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4496 trace('Deleting log entry',C_LEVEL_STATEMENT,l_Log_module);
4497 END IF;
4498
4499 -- No need to generate if it's a recovery operation.
4500
4501 IF g_group_id IS NOT NULL OR g_process_mode_code IS NOT NULL THEN
4502 generate_work_units (p_ledger_id => p_ledger_id
4503 ,p_group_id => g_group_id
4504 ,p_definition_code => g_definition_code);
4505 END IF;
4506
4507 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4508 trace('END upload_preprocessor',C_LEVEL_PROCEDURE,l_Log_module);
4509 END IF;
4510 EXCEPTION
4511 WHEN xla_exceptions_pkg.application_exception THEN
4512 RAISE;
4513 WHEN OTHERS THEN
4514 xla_exceptions_pkg.raise_message
4515 (p_location => 'xla_tb_data_manager_pvt.upload_preprocessor');
4516 END upload_preprocessor;
4517
4518 /*======================================================================+
4519 | |
4520 | PRIVATE FUNCTION |
4521 | |
4522 | update_definition |
4523 | |
4524 | |
4525 +======================================================================*/
4526
4527 PROCEDURE truncate_partition
4528 (p_definition_code VARCHAR2
4529 ) IS
4530 l_log_module VARCHAR2(240);
4531 l_schema VARCHAR2(30);
4532 BEGIN
4533 IF g_log_enabled THEN
4534 l_log_module := C_DEFAULT_MODULE||'.truncate_partition';
4535 END IF;
4536 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4537 trace('BEGIN truncate_partition',C_LEVEL_PROCEDURE,l_Log_module);
4538 END IF;
4539
4540 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4541 trace('Truncating the partition',C_LEVEL_STATEMENT,l_Log_module);
4542 END IF;
4543 l_schema := get_schema;
4544
4545
4546 -- Truncate Partition
4547 EXECUTE IMMEDIATE 'ALTER TABLE ' || l_schema ||'.XLA_TRIAL_BALANCES TRUNCATE partition '||p_definition_code;
4548
4549
4550 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4551 trace('END truncate_partition',C_LEVEL_PROCEDURE,l_Log_module);
4552 END IF;
4553 EXCEPTION
4554 WHEN xla_exceptions_pkg.application_exception THEN
4555 RAISE;
4556 WHEN OTHERS THEN
4557 xla_exceptions_pkg.raise_message
4558 (p_location => 'xla_tb_data_manager_pvt.truncate_partition');
4559 END truncate_partition;
4560
4561 /*======================================================================+
4562 | |
4563 | PRIVATE FUNCTION |
4564 | |
4565 | Upload_Pvt |
4566 | |
4567 | |
4568 +======================================================================*/
4569 PROCEDURE upload_pvt
4570 (p_errbuf OUT NOCOPY VARCHAR2
4571 ,p_retcode OUT NOCOPY NUMBER
4572 ,p_ledger_id IN NUMBER
4573 ,p_group_id IN NUMBER
4574 ,p_definition_code IN VARCHAR2
4575 ) IS
4576
4577
4578 l_req_data VARCHAR2(10);
4579 l_request_id NUMBER(15);
4580 l_log_module VARCHAR2(240);
4581 l_array_wu_requests t_array_num15;
4582 l_callStatus BOOLEAN;
4583 l_phase VARCHAR2(30);
4584 l_status VARCHAR2(30);
4585 l_dev_phase VARCHAR2(30);
4586 l_dev_status VARCHAR2(30);
4587 l_message VARCHAR2(240);
4588
4589
4590 BEGIN
4591 IF g_log_enabled THEN
4592 l_log_module := C_DEFAULT_MODULE||'.upload_pvt';
4593 END IF;
4594
4595 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4596 trace
4597 (p_msg => 'p_ledger_id = '||p_ledger_id
4598 ,p_level => C_LEVEL_PROCEDURE
4599 ,p_module => l_log_module);
4600 trace
4601 (p_msg => 'p_group_id = '||p_group_id
4602 ,p_level => C_LEVEL_PROCEDURE
4603 ,p_module => l_log_module);
4604 trace
4605 (p_msg => 'p_definition_code = '||p_definition_code
4606 ,p_level => C_LEVEL_PROCEDURE
4607 ,p_module => l_log_module);
4608 END IF;
4609 --
4610 --
4611 -- Generate work units
4612 --
4613 upload_preprocessor(p_ledger_id => p_ledger_id);
4614
4615
4616 IF nvl(g_wu_count,0) = 0 THEN
4617 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4618 trace('No work units to process existing',C_LEVEL_STATEMENT,l_Log_module);
4619 END IF;
4620 RETURN;
4621 END IF;
4622 COMMIT;
4623
4624 -- Initialize array
4625 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4626 trace('Initializing array',C_LEVEL_STATEMENT,l_Log_module);
4627 END IF;
4628 g_array_wu_requests.DELETE;
4629
4630 --
4631 -- Submit child processes
4632 --
4633
4634 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4635 trace('Submitting child processes',C_LEVEL_STATEMENT,l_Log_module);
4636 END IF;
4637
4638 FOR j IN 1..g_NUM_OF_WORKERS LOOP
4639
4640 l_request_id := FND_REQUEST.SUBMIT_REQUEST
4641 (application => 'XLA'
4642 ,program => 'XLATBDMW'
4643 ,description => 'TB Worker '||j
4644 ,start_time => SYSDATE
4645 ,sub_request => NULL
4646 ,argument1 => p_ledger_id
4647 ,argument2 => p_group_id
4648 ,argument3 => p_definition_code
4649 ,argument4 => g_request_id
4650 ,argument5 => g_je_source_name -- to pass g_je_source_name
4651 );
4652
4653 COMMIT;
4654 IF l_request_id = 0 THEN
4655
4656 p_errbuf := fnd_message.get;
4657 p_retcode := 2;
4658
4659 RETURN;
4660 ELSE
4661 --l_array_wu_requests(l_request_id) := l_request_id;
4662 /* bug#7552876 request ids are not getting generated sequentially.
4663 so on looping l_array_wu_requests outside is giving ORA-01403: no data found
4664 error */
4665 l_array_wu_requests(j) := l_request_id;
4666 END IF;
4667 END LOOP;
4668
4669 -- Wait until child threads stop
4670
4671 FOR i IN REVERSE l_array_wu_requests.first..l_array_wu_requests.last
4672 LOOP
4673 IF (l_array_wu_requests(i) IS NOT NULL) THEN
4674 IF (C_LEVEL_EVENT >= g_log_level) THEN
4675 trace('Checking status for request id = ' || l_array_wu_requests(i),C_LEVEL_EVENT,l_log_module);
4676 END IF;
4677 l_callStatus := fnd_concurrent.wait_for_request
4678 (request_id => l_array_wu_requests(i)
4679 ,interval => 5
4680 ,phase => l_phase
4681 ,status => l_status
4682 ,dev_phase => l_dev_phase
4683 ,dev_status => l_dev_status
4684 ,message => l_message);
4685
4686 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4687 trace('l_dev_phase = ' || l_dev_phase,C_LEVEL_STATEMENT,l_log_module);
4688 trace('l_dev_status = ' || l_dev_status,C_LEVEL_STATEMENT,l_log_module);
4689 END IF;
4690
4691 IF ( l_dev_phase = 'COMPLETE' AND l_dev_status <> 'NORMAL') THEN
4692 IF (C_LEVEL_ERROR >= g_log_level) THEN
4693 trace('The child request failed. Request Id = ' || l_array_wu_requests(i),C_LEVEL_ERROR,l_log_module);
4694 --Bug 13464353
4695 END IF;
4696 xla_exceptions_pkg.raise_message
4697 (p_location => 'The child request failed. Request Id = ' || l_array_wu_requests(i));
4698 --Bug 13464353
4699 END IF;
4700 END IF;
4701 END LOOP;
4702
4703 -- Once all the child processes stop. If there are no work units to process
4704 -- then delete row from the TB logs table.
4705
4706 /*
4707 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
4708 request_data => fnd_global.conc_request_id);
4709 */
4710
4711 --perf imp 13-May-2008 moved here ie after all the workers have finished processing
4712 populate_user_trans_view
4713 (p_definition_code => p_definition_code
4714 ,p_ledger_id => p_ledger_id
4715 ,p_group_id => p_group_id
4716 );
4717 --perf imp 13-May-2008
4718
4719 p_errbuf := 'Worker processes submitted.';
4720 p_retcode := 0;
4721
4722 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4723 trace('upload_pvt.End',C_LEVEL_PROCEDURE,l_Log_module);
4724 END IF;
4725 RETURN;
4726
4727 EXCEPTION
4728 WHEN xla_exceptions_pkg.application_exception THEN
4729 RAISE;
4730 WHEN OTHERS THEN
4731 xla_exceptions_pkg.raise_message
4732 (p_location => 'xla_tb_data_manager_pvt.upload_pvt');
4733
4734 END upload_pvt;
4735
4736 PROCEDURE validate_parameters
4737 (p_ledger_id IN INTEGER
4738 ,p_definition_code IN VARCHAR2
4739 ,p_group_id IN INTEGER
4740 ,p_process_mode_code IN VARCHAR2
4741 ) IS
4742 BEGIN
4743
4744 IF p_ledger_id IS NOT NULL THEN
4745 -- ledger_id is required
4746 NULL;
4747 ELSIF p_definition_code IS NOT NULL
4748 AND p_group_id IS NOT NULL THEN
4749 -- invalid parameters
4750 NULL;
4751 ELSIF p_group_id IS NOT NULL AND p_process_mode_code IS NOT NULL THEN
4752 NULL;
4753 END IF;
4754
4755 EXCEPTION
4756 WHEN xla_exceptions_pkg.application_exception THEN
4757 RAISE;
4758 WHEN OTHERS THEN
4759 xla_exceptions_pkg.raise_message
4760 (p_location => 'xla_tb_data_manager_pvt.validate_parameters');
4761 END validate_parameters;
4762
4763
4764 /*======================================================================+
4765 | |
4766 | PUBLIC FUNCTION |
4767 | |
4768 | Upload |
4769 | |
4770 | Main PROCEDURE TO upload trial balance |
4771 | Called FROM Cocurrent Program Trial Balance Data Manager |
4772 | p_process_mode_code = DELETED, CHANGED, NEW |
4773 +======================================================================*/
4774 PROCEDURE upload
4775 (p_errbuf IN OUT NOCOPY VARCHAR2
4776 ,p_retcode IN OUT NOCOPY NUMBER
4777 ,p_application_id IN NUMBER DEFAULT NULL
4778 ,p_ledger_id IN NUMBER
4779 ,p_group_id IN NUMBER
4780 ,p_definition_code IN VARCHAR2 DEFAULT NULL
4781 ,p_process_mode_code IN VARCHAR2
4782 ,p_je_source_name IN VARCHAR2 DEFAULT NULL
4783 ,p_upg_batch_id IN NUMBER DEFAULT NULL
4784 ,p_gl_date_from IN VARCHAR2 DEFAULT NULL
4785 ,p_gl_date_to IN VARCHAR2 DEFAULT NULL
4786 ) IS
4787
4788 l_req_data VARCHAR2(10);
4789 l_request_id NUMBER(15);
4790 l_log_module VARCHAR2(240);
4791
4792 BEGIN
4793 --Initialize Variables
4794 g_request_id := xla_environment_pkg.g_req_id;
4795 g_user_id := xla_environment_pkg.g_usr_id;
4796 g_login_id := xla_environment_pkg.g_login_id;
4797 g_prog_appl_id := xla_environment_pkg.g_prog_appl_id;
4798 g_program_id := xla_environment_pkg.g_prog_id;
4799 g_ledger_id := p_ledger_id;
4800 g_definition_code := p_definition_code;
4801 g_process_mode_code := p_process_mode_code;
4802 g_je_source_name := p_je_source_name;
4803 g_group_id := p_group_id;
4804 g_gl_date_from := p_gl_date_from;
4805 g_gl_date_to := p_gl_date_to;
4806
4807
4808 IF g_log_enabled THEN
4809 l_log_module := C_DEFAULT_MODULE||'.upload';
4810 END IF;
4811
4812 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4813 trace
4814 (p_msg => 'BEGIN of procedure upload'
4815 ,p_level => C_LEVEL_PROCEDURE
4816 ,p_module => l_log_module);
4817 trace
4818 (p_msg => 'p_ledger_id = '||p_ledger_id
4819 ,p_level => C_LEVEL_PROCEDURE
4820 ,p_module => l_log_module);
4821 trace
4822 (p_msg => 'p_group_id = '||p_group_id
4823 ,p_level => C_LEVEL_PROCEDURE
4824 ,p_module => l_log_module);
4825 trace
4826 (p_msg => 'p_definition_code = '||p_definition_code
4827 ,p_level => C_LEVEL_PROCEDURE
4828 ,p_module => l_log_module);
4829 trace
4830 (p_msg => 'p_process_mode_code = '||p_process_mode_code
4831 ,p_level => C_LEVEL_PROCEDURE
4832 ,p_module => l_log_module);
4833 trace
4834 (p_msg => 'p_je_source_name = '||p_je_source_name
4835 ,p_level => C_LEVEL_PROCEDURE
4836 ,p_module => l_log_module);
4837 END IF;
4838
4839 -- Validate input parameters
4840 validate_parameters(
4841 p_ledger_id => p_ledger_id
4842 ,p_definition_code => p_definition_code
4843 ,p_group_id => p_group_id
4844 ,p_process_mode_code => p_process_mode_code
4845 );
4846
4847 -- Insert log entries
4848 insert_tb_logs;
4849
4850
4851 IF p_je_source_name IS NOT NULL THEN
4852 g_application_id := get_je_source_info
4853 (p_je_source_name => p_je_source_name);
4854 END IF;
4855
4856 IF p_process_mode_code = 'DELETED' THEN
4857 delete_definition
4858 ( p_definition_code => p_definition_code);
4859 drop_partition;
4860 ELSIF p_process_mode_code in ('CHANGED','NEW') THEN
4861 -- Delete non setup data
4862 delete_trial_balances
4863 ( p_definition_code => p_definition_code);
4864 truncate_partition
4865 ( p_definition_code => p_definition_code);
4866 -- Recreate segment ranges
4867 define_segment_ranges
4868 ( p_definition_code => p_definition_code);
4869 END IF;
4870
4871 -- Recover failed batches.
4872 -- IF p_group_id IS NULL
4873 /*Bug#7338524 Changed this condition to group_id IS NOT NULL as in case of transfer
4874 to GL group_id is passed and recovery needs to be done for failed batches lying in
4875 xla_tb_logs which belongs to different group_id */
4876
4877 IF p_group_id IS NOT NULL
4878 --OR (p_definition_code IS NOT NULL AND p_process_mode_code IS NULL)
4879 /* commented this condition as part of bug#7344564 which needs review and
4880 further analysis of the issues raised as part of recovery */
4881 THEN
4882 recover_failed_requests
4883 (p_ledger_id => p_ledger_id
4884 ,p_definition_code => p_definition_code
4885 );
4886 END IF;
4887
4888 IF nvl(p_process_mode_code,'N') NOT IN ('DELETED') THEN
4889 -- Derive processing unit;
4890 --
4891 -- Set work unit size and number of workers
4892 --
4893 get_worker_info(p_ledger_id => p_ledger_id);
4894
4895 --
4896 -- Submit Worker Processes
4897 --
4898 upload_pvt
4899 (p_errbuf => p_errbuf
4900 ,p_retcode => p_retcode
4901 ,p_ledger_id => p_ledger_id
4902 ,p_group_id => p_group_id
4903 ,p_definition_code => p_definition_code
4904 );
4905
4906 END IF;
4907
4908 -- Delete log entry
4909 delete_tb_log;
4910
4911 EXCEPTION
4912 WHEN xla_exceptions_pkg.application_exception THEN
4913 RAISE;
4914 WHEN OTHERS THEN
4915 xla_exceptions_pkg.raise_message
4916 (p_location => 'xla_tb_data_manager_pvt.upload');
4917 END upload;
4918
4919
4920 /*======================================================================+
4921 |
4922 | PRIVATE FUNCTION
4923 |
4924 | delete_wu
4925 |
4926 |
4927 +======================================================================*/
4928
4929 PROCEDURE delete_wu
4930 (p_from_header_id NUMBER )IS
4931
4932 l_log_module VARCHAR2(240);
4933 BEGIN
4934 IF g_log_enabled THEN
4935 l_log_module := C_DEFAULT_MODULE||'.delete_wu';
4936 END IF;
4937 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4938 trace('BEGIN of delete_wu',C_LEVEL_PROCEDURE,l_Log_module);
4939 END IF;
4940
4941 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4942 trace('p_from_header_id = '||p_from_header_id,C_LEVEL_STATEMENT,l_Log_module);
4943 END IF;
4944
4945 DELETE xla_tb_work_units
4946 WHERE from_header_id = p_from_header_id;
4947
4948 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4949 trace('END of delete_wu',C_LEVEL_PROCEDURE,l_Log_module);
4950 END IF;
4951
4952 EXCEPTION
4953 WHEN xla_exceptions_pkg.application_exception THEN
4954 RAISE;
4955 WHEN OTHERS THEN
4956 xla_exceptions_pkg.raise_message
4957 (p_location => 'xla_tb_data_manager_pvt.delete_wu');
4958
4959 END delete_wu;
4960
4961 /*======================================================================+
4962 | |
4963 | PRIVATE FUNCTION |
4964 | |
4965 | Worker_Proces_Pvt |
4966 | |
4967 | |
4968 +======================================================================*/
4969 PROCEDURE worker_process_pvt
4970 (p_errbuf OUT NOCOPY VARCHAR2
4971 ,p_retcode OUT NOCOPY VARCHAR2
4972 ,p_ledger_id IN NUMBER
4973 ,p_definition_code IN VARCHAR2
4974 ,p_parent_request_id IN PLS_INTEGER
4975 ,p_je_source_name IN VARCHAR2 -- to pass the je_source_name
4976 ) IS
4977
4978 l_from_header_id NUMBER(15);
4979 l_to_header_id NUMBER(15);
4980 l_definition_code xla_tb_definitions_b.definition_code%TYPE;
4981 l_log_module VARCHAR2(240);
4982 l_owner_code VARCHAR2(1);
4983 BEGIN
4984
4985 IF g_log_enabled THEN
4986 l_log_module := C_DEFAULT_MODULE||'.worker_process_pvt';
4987 END IF;
4988
4989 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4990 trace('worker_process_pvt.Begin',C_LEVEL_PROCEDURE,l_log_module);
4991 END IF;
4992
4993 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4994
4995 trace('p_ledger_id = ' || p_ledger_id
4996 ,C_LEVEL_STATEMENT
4997 ,l_log_module);
4998
4999 trace('p_definition_code = ' || p_definition_code
5000 ,C_LEVEL_STATEMENT
5001 ,l_log_module);
5002
5003 trace('p_parent_request_id = ' || p_parent_request_id
5004 ,C_LEVEL_STATEMENT
5005 ,l_log_module);
5006 END IF;
5007
5008 -- Initialize variables
5009
5010 g_tb_insert_sql := C_TB_INSERT_SQL;
5011
5012
5013 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
5014 trace('Calling retrieve_work_unit ',C_LEVEL_STATEMENT,l_log_module);
5015 END IF;
5016
5017 LOOP
5018 retrieve_work_unit
5019 (p_parent_request_id => p_parent_request_id
5020 ,p_from_header_id => l_from_header_id
5021 ,p_to_header_id => l_to_header_id
5022 ,p_definition_code => l_definition_code
5023 );
5024
5025 --
5026 -- Exit when there is no work unit
5027 --
5028 IF l_from_header_id IS NULL THEN
5029 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
5030 trace('No more records to process ',C_LEVEL_STATEMENT,l_log_module);
5031 END IF;
5032 EXIT;
5033 END IF;
5034
5035 IF p_definition_code IS NOT NULL THEN
5036 insert_trial_balance_def
5037 (p_definition_code => l_definition_code
5038 ,p_from_header_id => l_from_header_id
5039 ,p_to_header_id => l_to_header_id
5040 );
5041 ELSE
5042 insert_trial_balance_wu
5043 (p_from_header_id => l_from_header_id
5044 ,p_to_header_id => l_to_header_id
5045 ,p_je_source_name => p_je_source_name -- pass the je_source_name
5046 );
5047 END IF;
5048
5049 --
5050 -- Moved from worker_process to eliminate TX contention
5051 -- for the case # of unit processors is more than one.
5052 --
5053
5054 --
5055 -- Delete Work Unit
5056 --
5057
5058 delete_wu(p_from_header_id => l_from_header_id);
5059
5060
5061 END LOOP;
5062
5063 --
5064 -- Create upgraded rows
5065 --
5066 /* Bug 5635401
5067 Due to the change in bug 5394467, need not populate initial balances.
5068 For upgraded report definitions, insert_trial_balance_upg will fail
5069 with ORA-1400 as balance date is null.
5070
5071 FOR c_def IN (SELECT definition_code
5072 FROM xla_tb_definitions_b
5073 WHERE definition_code = NVL(p_definition_code,definition_code)
5074 AND ledger_id = p_ledger_id
5075 AND owner_code = 'S')
5076 LOOP
5077
5078 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
5079 trace('System Generated Report Definition ' || c_def.definition_code
5080 ,C_LEVEL_STATEMENT
5081 ,l_log_module);
5082 END IF;
5083
5084 --
5085 -- Delete existing records
5086 --
5087 DELETE FROM xla_trial_balances
5088 WHERE definition_code = c_def.definition_code
5089 AND source_entity_id = -1;
5090
5091 insert_trial_balance_upg
5092 (p_definition_code => c_def.definition_code);
5093
5094 END LOOP;
5095 */
5096
5097 COMMIT;
5098
5099 p_errbuf := 'Completed successfully.';
5100 p_retcode := 0;
5101
5102 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5103 trace('worker_process_pvt.End',C_LEVEL_PROCEDURE,l_log_module);
5104 END IF;
5105
5106 EXCEPTION
5107 WHEN xla_exceptions_pkg.application_exception THEN
5108 RAISE;
5109 WHEN OTHERS THEN
5110 IF l_from_header_id IS NOT NULL THEN
5111
5112 /*
5113 update_wu_status
5114 (p_from_header_id => l_from_header_id
5115 ,p_status_code => C_WU_ERROR);
5116 */ -- update processes status
5117
5118 p_errbuf := SQLERRM;
5119 p_retcode := 2;
5120
5121 COMMIT;
5122
5123 END IF;
5124 xla_exceptions_pkg.raise_message
5125 (p_location => 'xla_tb_data_manager_pvt.worker_process_pvt');
5126
5127 END worker_process_pvt;
5128
5129 /*======================================================================+
5130 | |
5131 | PUBLIC FUNCTION |
5132 | |
5133 | Worker_Proces |
5134 | |
5135 | |
5136 +======================================================================*/
5137 PROCEDURE worker_process
5138 (p_errbuf OUT NOCOPY VARCHAR2
5139 ,p_retcode OUT NOCOPY NUMBER
5140 ,p_ledger_id IN NUMBER
5141 ,p_group_id IN NUMBER
5142 ,p_definition_code IN VARCHAR2
5143 ,p_parent_request_id IN PLS_INTEGER
5144 ,p_je_source_name IN VARCHAR2 --to pass the je_source_name
5145 ) IS
5146
5147 l_log_module VARCHAR2(240);
5148 l_ledger_info r_ledger_info;
5149 BEGIN
5150 IF g_log_enabled THEN
5151 l_log_module := C_DEFAULT_MODULE||'.worker_process';
5152 END IF;
5153
5154 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5155 trace('BEGIN of worker_process',C_LEVEL_PROCEDURE,l_log_module);
5156 END IF;
5157
5158 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
5159 -- Print all input parameters
5160 trace('p_ledger_id = ' || p_ledger_id ,C_LEVEL_STATEMENT,l_log_module);
5161 trace('p_group_id = ' || p_group_id ,C_LEVEL_STATEMENT,l_log_module);
5162 trace('p_definition_code = ' || p_definition_code,C_LEVEL_STATEMENT,l_log_module);
5163 END IF;
5164
5165 -- Initialize variables
5166 g_request_id := xla_environment_pkg.g_req_id;
5167 g_user_id := xla_environment_pkg.g_usr_id;
5168 g_login_id := xla_environment_pkg.g_login_id;
5169 g_prog_appl_id := xla_environment_pkg.g_prog_appl_id;
5170 g_program_id := xla_environment_pkg.g_prog_id;
5171 g_group_id := p_group_id;
5172 g_ledger_id := p_ledger_id;
5173 l_ledger_info := get_ledger_info
5174 (p_ledger_id => g_ledger_id);
5175
5176
5177 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
5178 trace('Calling worker_process_pvt ',C_LEVEL_STATEMENT,l_log_module);
5179 END IF;
5180
5181 worker_process_pvt
5182 (p_errbuf => p_errbuf
5183 ,p_retcode => p_retcode
5184 ,p_ledger_id => p_ledger_id
5185 ,p_definition_code => p_definition_code
5186 ,p_parent_request_id => p_parent_request_id
5187 ,p_je_source_name => p_je_source_name -- to pass the je_source_name
5188 );
5189
5190 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
5191 trace('Calling populate_user_trans_view ',C_LEVEL_STATEMENT,l_log_module);
5192 END IF;
5193
5194 p_errbuf := 'Completed Successfully.';
5195 p_retcode := 0;
5196
5197 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5198 trace('End of worker_process',C_LEVEL_PROCEDURE,l_log_module);
5199 END IF;
5200 EXCEPTION
5201 WHEN xla_exceptions_pkg.application_exception THEN
5202 IF (C_LEVEL_UNEXPECTED >= g_log_level) THEN
5203 trace('Unexpected error in worker_process'
5204 ,C_LEVEL_UNEXPECTED
5205 ,l_log_module);
5206 END IF;
5207 RAISE;
5208 WHEN OTHERS THEN
5209 xla_exceptions_pkg.raise_message
5210 (p_location => 'xla_accounting_pub_pkg.accounting_program_batch');
5211 END worker_process;
5212
5213 /*======================================================================+
5214 | |
5215 | PUBLIC FUNCTION |
5216 | |
5217 | Delete_Non_UI_Rows |
5218 | |
5219 | Deletes rows from the following tables: |
5220 | - xla_tb_logs |
5221 | - xla_tb_def_seg_ranges |
5222 | - xla_tb_user_trans_views |
5223 | - xla_tb_work_units |
5224 | |
5225 | For xla_trial_balances, call drop_partition separately |
5226 | Called from TbReportDefnsAMImpl.java. |
5227 +======================================================================*/
5228 PROCEDURE delete_non_ui_rows
5229 (p_definition_code IN VARCHAR2)
5230 IS
5231 l_log_module VARCHAR2(240);
5232 BEGIN
5233 IF g_log_enabled THEN
5234 l_log_module := C_DEFAULT_MODULE||'.worker_process';
5235 END IF;
5236
5237 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5238 trace('BEGIN delete_non_ui_data'
5239 ,C_LEVEL_PROCEDURE
5240 ,l_log_module);
5241 END IF;
5242
5243 DELETE xla_tb_logs
5244 WHERE definition_code = p_definition_code;
5245
5246 DELETE xla_tb_def_seg_ranges
5247 WHERE definition_code = p_definition_code;
5248
5249 DELETE xla_tb_user_trans_views
5250 WHERE definition_code = p_definition_code;
5251
5252 DELETE xla_tb_work_units
5253 WHERE definition_code = p_definition_code;
5254
5255 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5256 trace('END delete_non_ui_rows'
5257 ,C_LEVEL_PROCEDURE
5258 ,l_log_module);
5259 END IF;
5260 EXCEPTION
5261 WHEN xla_exceptions_pkg.application_exception THEN
5262 IF (C_LEVEL_UNEXPECTED >= g_log_level) THEN
5263 trace('Unexpected error in delete_non_ui_rows'
5264 ,C_LEVEL_UNEXPECTED
5265 ,l_log_module);
5266 END IF;
5267 RAISE;
5268 WHEN OTHERS THEN
5269 xla_exceptions_pkg.raise_message
5270 (p_location => 'xla_tb_data_manager_pvt.delete_non_ui_rows');
5271 END delete_non_ui_rows;
5272
5273 --=============================================================================
5274 -- *********** Initialization routine **********
5275 --=============================================================================
5276
5277 --=============================================================================
5278 --
5279 --
5280 --
5281 -- The following code is executed when the package body is referenced for the
5282 -- first time
5283 --
5284 --
5285 --
5286 --=============================================================================
5287
5288 BEGIN
5289 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5290 g_log_enabled := fnd_log.test
5291 (log_level => g_log_level
5292 ,MODULE => C_DEFAULT_MODULE);
5293
5294 IF NOT g_log_enabled THEN
5295 g_log_level := C_LEVEL_LOG_DISABLED;
5296 END IF;
5297
5298
5299
5300 END XLA_TB_DATA_MANAGER_PVT;