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