[Home] [Help]
PACKAGE BODY: APPS.XLA_TB_AP_REPORT_PVT
Source
1 PACKAGE BODY XLA_TB_AP_REPORT_PVT AS
2 /* $Header: xlatbapt.pkb 120.7 2010/09/08 08:27:42 vgopiset noship $ */
3
4
5 --+==========================================================================+
6 --| |
7 --| |
8 --| Global Constants |
9 --| |
10 --| |
11 --+==========================================================================+
12
13 C_TB_SOURCE_SQL CONSTANT VARCHAR2(32000) := '
14 SELECT
15 gcck.concatenated_segments ACCOUNT
16 ,$gl_balance_cols$ GL_BALANCE
17 ,tbg.code_combination_id CODE_COMBINATION_ID
18 ,tbg.balancing_segment_value BALANCING_SEGMENT_VALUE
19 ,tbg.natural_account_segment_value NATURAL_ACCOUNT_SEGMENT_VALUE
20 ,tbg.cost_center_segment_value COST_CENTER_SEGMENT_VALUE
21 ,tbg.management_segment_value MANAGEMENT_SEGMENT_VALUE
22 ,tbg.intercompany_segment_value INTERCOMPANY_SEGMENT_VALUE
23 ,tbg.ledger_id LEDGER_ID
24 ,tbg.ledger_name LEDGER_NAME
25 ,tbg.ledger_short_name LEDGER_SHORT_NAME
26 ,tbg.ledger_currency_code LEDGER_CURRENCY_CODE
27 ,tbg.third_party_name THIRD_PARTY_NAME
28 ,tbg.third_party_number THIRD_PARTY_NUMBER
29 ,tbg.third_party_type_code THIRD_PARTY_TYPE_CODE
30 ,tbg.third_party_type THIRD_PARTY_TYPE
31 ,tbg.third_party_site_name THIRD_PARTY_SITE_NAME
32 ,tbg.source_application_id SOURCE_TRX_APPLICATION_ID
33 ,tbg.source_entity_id SOURCE_ENTITY_ID
34 ,app.application_name SOURCE_TRX_APPLICATION_NAME
35 ,ett.name SOURCE_TRX_TYPE
36 ,tbg.transaction_number SOURCE_TRX_NUMBER
37 ,to_char(tbg.gl_date,''YYYY-MM-DD'') SOURCE_TRX_GL_DATE
38 ,tbg.trx_currency_code SOURCE_TRX_CURR
39 ,tbg.entered_unrounded_orig_amount SRC_ENTERED_UNROUNDED_ORIG_AMT
40 ,tbg.entered_unrounded_rem_amount SRC_ENTERED_UNROUNDED_REM_AMT
41 ,tbg.entered_rounded_orig_amount SRC_ENTERED_ROUNDED_ORIG_AMT
42 ,tbg.entered_rounded_rem_amount SRC_ENTERED_ROUNDED_REM_AMT
43 ,tbg.acctd_unrounded_orig_amount SRC_ACCTD_UNROUNDED_ORIG_AMT
44 ,tbg.acctd_unrounded_rem_amount SRC_ACCTD_UNROUNDED_REM_AMT
45 ,tbg.acctd_rounded_orig_amount SRC_ACCTD_ROUNDED_ORIG_AMT
46 ,tbg.acctd_rounded_rem_amount SRC_ACCTD_ROUNDED_REM_AMT
47 ,tbg.user_trx_identifier_name_1 USER_TRX_IDENTIFIER_NAME_1
48 ,tbg.user_trx_identifier_name_2 USER_TRX_IDENTIFIER_NAME_2
49 ,tbg.user_trx_identifier_name_3 USER_TRX_IDENTIFIER_NAME_3
50 ,tbg.user_trx_identifier_name_4 USER_TRX_IDENTIFIER_NAME_4
51 ,tbg.user_trx_identifier_name_5 USER_TRX_IDENTIFIER_NAME_5
52 ,tbg.user_trx_identifier_name_6 USER_TRX_IDENTIFIER_NAME_6
53 ,tbg.user_trx_identifier_name_7 USER_TRX_IDENTIFIER_NAME_7
54 ,tbg.user_trx_identifier_name_8 USER_TRX_IDENTIFIER_NAME_8
55 ,tbg.user_trx_identifier_name_9 USER_TRX_IDENTIFIER_NAME_9
56 ,tbg.user_trx_identifier_name_10 USER_TRX_IDENTIFIER_NAME_10
57 ,tbg.user_trx_identifier_value_1 USER_TRX_IDENTIFIER_VALUE_1
58 ,tbg.user_trx_identifier_value_2 USER_TRX_IDENTIFIER_VALUE_2
59 ,tbg.user_trx_identifier_value_3 USER_TRX_IDENTIFIER_VALUE_3
60 ,tbg.user_trx_identifier_value_4 USER_TRX_IDENTIFIER_VALUE_4
61 ,tbg.user_trx_identifier_value_5 USER_TRX_IDENTIFIER_VALUE_5
62 ,tbg.user_trx_identifier_value_6 USER_TRX_IDENTIFIER_VALUE_6
63 ,tbg.user_trx_identifier_value_7 USER_TRX_IDENTIFIER_VALUE_7
64 ,tbg.user_trx_identifier_value_8 USER_TRX_IDENTIFIER_VALUE_8
65 ,tbg.user_trx_identifier_value_9 USER_TRX_IDENTIFIER_VALUE_9
66 ,tbg.user_trx_identifier_value_10 USER_TRX_IDENTIFIER_VALUE_10
67 ,tbg.NON_AP_AMOUNT NON_AP_AMOUNT
68 ,tbg.MANUAL_SLA_AMOUNT MANUAL_SLA_AMOUNT
69 $seg_desc_cols$
70 FROM xla_trial_balances_gt tbg
71 ,fnd_application_vl app
72 ,xla_entity_types_vl ett
73 ,gl_code_combinations_kfv gcck
74 ,gl_balances gb
75 $seg_desc_from$
76 WHERE tbg.source_entity_code = ett.entity_code
77 AND tbg.source_application_id = ett.application_id
78 AND tbg.source_application_id = app.application_id
79 AND tbg.code_combination_id = gcck.code_combination_id
80 $gl_balance_join$
81 $seg_desc_join$
82 ';
83
84 --added TB phase 4 bug#7600550
85 C_SELECT_NONAP_AMOUNT CONSTANT VARCHAR2(32000) :=
86 '
87 WITH xtd AS
88 (
89 SELECT /*+ materialize */
90 DISTINCT p.ledger_id, d.code_combination_id,
91 p.period_name,
92 rpad(''x'',500) pad
93 FROM xla_tb_defn_details d,
94 xla_tb_definitions_vl vl,
95 gl_period_statuses p
96 WHERE d.definition_code = ''$p_definition_code$''
97 AND vl.definition_code = d.definition_code
98 AND p.application_id =200
99 AND p.ledger_id = vl.ledger_id
100 AND p.start_date >= NVL(:1, p.start_date + 1)
101 AND p.end_date <= NVL(:2, p.end_date + 1)
102 AND NVL(p.adjustment_period_flag,''N'')=''N''
103 )
104 SELECT /*+ leading(xtd,l,h,gcck) parallel(xtd) pq_distribute(l,broadcast,none)
105 use_nl(l,gcck,h) parallel(l) parallel(h) parallel(gcck)
106 */
107 l.ledger_id,
108 l.code_combination_id,
109 sum(nvl(l.accounted_cr, 0))- sum(nvl(l.accounted_dr,0)) NONAP_AMOUNT
110 FROM gl_je_headers h
111 ,gl_je_lines l
112 ,gl_code_combinations_kfv gcck
113 ,xtd
114 WHERE l.code_combination_id = gcck.code_combination_id
115 AND l.code_combination_id = xtd.code_combination_id
116 AND l.ledger_id = xtd.ledger_id
117 AND l.period_name = xtd.period_name
118 AND h.je_source <> ''Payables''
119 AND h.je_header_id = l.je_header_id
120 AND h.ledger_id = l.ledger_id
121 AND h.actual_flag = ''A''
122 AND h.status = ''P''
123 AND l.effective_date BETWEEN NVL(:3, l.effective_date )
124 AND NVL(:4, l.effective_date + 1 )
125 GROUP BY l.ledger_id,l.code_combination_id
126 ';
127
128 -- added xe INLINE VIEW for performance bug#9602525
129 C_SELECT_MANUAL_SLA_AMOUNT CONSTANT VARCHAR2(32000) :=
130 '
131 WITH xtd AS
132 (
133 SELECT /*+ materialize */ DISTINCT ledger_id, code_combination_id,
134 rpad(''x'',500) pad
135 FROM xla_tb_defn_details d,
136 xla_tb_definitions_vl vl
137 WHERE d.definition_code = ''$p_definition_code$''
138 AND vl.definition_code = d.definition_code
139 ) ,
140 xe AS
141 (
142 SELECT xe1.application_id , xe1.event_id
143 FROM xla_events xe1
144 WHERE xe1.application_id = 200
145 AND xe1.process_status_code = ''P''
146 AND xe1.event_type_code = ''MANUAL''
147 )
148 SELECT /*+ ordered use_nl(xe,h,l,xtd,gcck) no_index(l mis_xla_ae_lines_n1) */
149 l.ledger_id,
150 l.code_combination_id,
151 sum(nvl(accounted_cr,0))-sum(nvl(accounted_dr,0)) MANUAL_SLA_AMOUNT
152 FROM xe,
153 xla_ae_headers h,
154 xla_ae_lines l,
155 xtd ,
156 gl_code_combinations_kfv gcck
157 WHERE gcck.code_combination_id = l.code_combination_id
158 AND h.application_id = 200
159 AND gcck.code_combination_id = xtd.code_combination_id
160 AND l.application_id = h.application_id
161 AND l.ae_header_id = h.ae_header_id
162 AND h.ledger_id = l.ledger_id
163 AND h.gl_transfer_status_code=''Y''
164 AND h.accounting_entry_status_code=''F''
165 AND h.event_type_code=''MANUAL''
166 AND h.balance_type_code=''A''
167 AND h.ledger_id = xtd.ledger_id
168 AND h.event_id = xe.event_id
169 AND h.application_id = xe.application_id
170 AND h.ledger_id = :1
171 AND h.accounting_date BETWEEN :2 AND :3
172 GROUP BY l.ledger_id,l.code_combination_id
173 ';
174
175
176 -- added TB phase 4 bug#7600550 bug#8291101
177
178 C_SELECT_NONAP_SEGRANGES_AMT CONSTANT VARCHAR2(32000) :=
179 'WITH xtd AS
180 (
181 SELECT /*+ materialize */
182 DISTINCT gcck.code_combination_id, vl.ledger_id,
183 rpad(''x'',500) pad
184 FROM
185 xla_tb_definitions_vl vl,
186 xla_tb_def_seg_ranges xsr, -- added for bug#9926320
187 gl_code_combinations_kfv gcck
188 WHERE vl.definition_code = ''$p_definition_code$''
189 AND vl.definition_code = xsr.definition_code -- added for bug#9926320
190 $gcck_join$
191 AND EXISTS
192 ( SELECT /*+ no_unnest */ 1
193 FROM xla_trial_balances xtb
194 WHERE xtb.code_combination_id = gcck.code_combination_id
195 AND xtb.definition_code = vl.definition_code
196 )
197 )
198 SELECT /*+ leading(xtd,l,p,h) parallel(xtd) pq_distribute(l,broadcast,none)
199 use_nl(l,h,p) parallel(p) ,parallel(l) parallel(h)
200 */
201 l.ledger_id,
202 l.code_combination_id,
203 sum(nvl(l.accounted_cr, 0))- sum(nvl(l.accounted_dr,0)) NONAP_AMOUNT
204 FROM gl_je_headers h
205 ,gl_je_lines l
206 ,xtd
207 ,gl_period_statuses p
208 WHERE l.code_combination_id = xtd.code_combination_id
209 AND l.ledger_id = xtd.ledger_id
210 AND l.period_name = p.period_name
211 AND h.je_source <> ''Payables''
212 AND h.je_header_id = l.je_header_id
213 AND h.ledger_id = l.ledger_id
214 AND h.actual_flag = ''A''
215 AND h.status = ''P''
216 AND l.effective_date BETWEEN NVL(:1, l.effective_date )
217 AND NVL(:2, l.effective_date + 1 )
218 AND p.application_id =200
219 AND p.ledger_id = xtd.ledger_id
220 AND NVL(p.adjustment_period_flag,''N'')=''N''
221 GROUP BY l.ledger_id,l.code_combination_id
222 ';
223
224 --added TB phase 4 bug#7600550 bug#8291101
225 -- added xe INLINE VIEW for performance bug#9602525
226 C_SELECT_MANUAL_SEGRANGES_AMT CONSTANT VARCHAR2(32000) :=
227 '
228 WITH xtd AS
229 (
230 SELECT /*+ materialize */
231 DISTINCT gcck.code_combination_id, vl.ledger_id,
232 rpad(''x'',500) pad
233 FROM
234 xla_tb_definitions_vl vl,
235 xla_tb_def_seg_ranges xsr, -- added for bug#9926320
236 gl_code_combinations_kfv gcck
237 WHERE vl.definition_code = ''$p_definition_code$''
238 AND vl.definition_code = xsr.definition_code -- added for bug#9926320
239 $gcck_join$
240 AND EXISTS
241 ( SELECT /*+ no_unnest */ 1
242 FROM xla_trial_balances xtb
243 WHERE xtb.code_combination_id = gcck.code_combination_id
244 AND xtb.definition_code = vl.definition_code
245 )
246 ),
247 xe AS
248 (
249 SELECT xe1.application_id , xe1.event_id
250 FROM xla_events xe1
251 WHERE xe1.application_id = 200
252 AND xe1.process_status_code = ''P''
253 AND xe1.event_type_code = ''MANUAL''
254 )
255 SELECT /*+ ordered use_nl(xe,h,l,xtd) no_index(l mis_xla_ae_lines_n1) */
256 l.ledger_id,
257 l.code_combination_id,
258 sum(nvl(accounted_cr,0))-sum(nvl(accounted_dr,0)) MANUAL_SLA_AMOUNT
259 FROM xe,
260 xla_ae_headers h,
261 xla_ae_lines l,
262 xtd
263 WHERE h.application_id = 200
264 AND h.application_id = l.application_id
265 AND h.ae_header_id = l.ae_header_id
266 AND h.ledger_id = l.ledger_id
267 AND h.ledger_id = xtd.ledger_id
268 AND l.code_combination_id = xtd.code_combination_id
269 AND h.gl_transfer_status_code=''Y''
270 AND h.accounting_entry_status_code=''F''
271 AND h.event_type_code= ''MANUAL''
272 AND h.balance_type_code=''A''
273 AND h.ledger_id = xtd.ledger_id
274 AND h.event_id = xe.event_id
275 AND h.application_id = xe.application_id
276 AND h.ledger_id = :1
277 AND h.accounting_date BETWEEN :2 AND :3
278 GROUP BY l.ledger_id,l.code_combination_id
279 ';
280
281 --end bug#7600550 bug#8291101
282
283 C_TB_WRITE_OFF_SQL CONSTANT VARCHAR2(32000) := '
284 SELECT
285 gcck.concatenated_segments ACCOUNT
286 ,$gl_balance_cols$ GL_BALANCE
287 ,tbg.code_combination_id CODE_COMBINATION_ID
288 ,tbg.balancing_segment_value BALANCING_SEGMENT_VALUE
289 ,tbg.natural_account_segment_value NATURAL_ACCOUNT_SEGMENT_VALUE
290 ,tbg.cost_center_segment_value COST_CENTER_SEGMENT_VALUE
291 ,tbg.management_segment_value MANAGEMENT_SEGMENT_VALUE
292 ,tbg.intercompany_segment_value INTERCOMPANY_SEGMENT_VALUE
293 ,tbg.ledger_id LEDGER_ID
294 ,tbg.ledger_name LEDGER_NAME
295 ,tbg.ledger_short_name LEDGER_SHORT_NAME
296 ,tbg.ledger_currency_code LEDGER_CURRENCY_CODE
297 ,tbg.third_party_name THIRD_PARTY_NAME
298 ,tbg.third_party_number THIRD_PARTY_NUMBER
299 ,tbg.third_party_type_code THIRD_PARTY_TYPE_CODE
300 ,tbg.third_party_type THIRD_PARTY_TYPE
301 ,tbg.third_party_site_name THIRD_PARTY_SITE_NAME
302 ,tbg.source_application_id SOURCE_TRX_APPLICATION_ID
303 ,tbg.source_entity_id SOURCE_ENTITY_ID
304 ,app.application_name SOURCE_TRX_APPLICATION_NAME
305 ,''$write_off$'' SOURCE_TRX_TYPE
306 ,tbg.transaction_number SOURCE_TRX_NUMBER
307 ,to_char(tbg.gl_date,''YYYY-MM-DD'') SOURCE_TRX_GL_DATE
308 ,tbg.trx_currency_code SOURCE_TRX_CURR
309 ,tbg.entered_unrounded_orig_amount SRC_ENTERED_UNROUNDED_ORIG_AMT
310 ,tbg.entered_unrounded_rem_amount SRC_ENTERED_UNROUNDED_REM_AMT
311 ,tbg.entered_rounded_orig_amount SRC_ENTERED_ROUNDED_ORIG_AMT
312 ,tbg.entered_rounded_rem_amount SRC_ENTERED_ROUNDED_REM_AMT
313 ,tbg.acctd_unrounded_orig_amount SRC_ACCTD_UNROUNDED_ORIG_AMT
314 ,tbg.acctd_unrounded_rem_amount SRC_ACCTD_UNROUNDED_REM_AMT
315 ,tbg.acctd_rounded_orig_amount SRC_ACCTD_ROUNDED_ORIG_AMT
316 ,tbg.acctd_rounded_rem_amount SRC_ACCTD_ROUNDED_REM_AMT
317 ,tbg.user_trx_identifier_name_1 USER_TRX_IDENTIFIER_NAME_1
318 ,tbg.user_trx_identifier_name_2 USER_TRX_IDENTIFIER_NAME_2
319 ,tbg.user_trx_identifier_name_3 USER_TRX_IDENTIFIER_NAME_3
320 ,tbg.user_trx_identifier_name_4 USER_TRX_IDENTIFIER_NAME_4
321 ,tbg.user_trx_identifier_name_5 USER_TRX_IDENTIFIER_NAME_5
322 ,tbg.user_trx_identifier_name_6 USER_TRX_IDENTIFIER_NAME_6
323 ,tbg.user_trx_identifier_name_7 USER_TRX_IDENTIFIER_NAME_7
324 ,tbg.user_trx_identifier_name_8 USER_TRX_IDENTIFIER_NAME_8
325 ,tbg.user_trx_identifier_name_9 USER_TRX_IDENTIFIER_NAME_9
326 ,tbg.user_trx_identifier_name_10 USER_TRX_IDENTIFIER_NAME_10
327 ,tbg.user_trx_identifier_value_1 USER_TRX_IDENTIFIER_VALUE_1
328 ,tbg.user_trx_identifier_value_2 USER_TRX_IDENTIFIER_VALUE_2
329 ,tbg.user_trx_identifier_value_3 USER_TRX_IDENTIFIER_VALUE_3
330 ,tbg.user_trx_identifier_value_4 USER_TRX_IDENTIFIER_VALUE_4
331 ,tbg.user_trx_identifier_value_5 USER_TRX_IDENTIFIER_VALUE_5
332 ,tbg.user_trx_identifier_value_6 USER_TRX_IDENTIFIER_VALUE_6
333 ,tbg.user_trx_identifier_value_7 USER_TRX_IDENTIFIER_VALUE_7
334 ,tbg.user_trx_identifier_value_8 USER_TRX_IDENTIFIER_VALUE_8
335 ,tbg.user_trx_identifier_value_9 USER_TRX_IDENTIFIER_VALUE_9
336 ,tbg.user_trx_identifier_value_10 USER_TRX_IDENTIFIER_VALUE_10
337 ,tbg.NON_AP_AMOUNT NON_AP_AMOUNT
338 ,tbg.MANUAL_SLA_AMOUNT MANUAL_SLA_AMOUNT
339 $seg_desc_cols$
340 FROM xla_trial_balances_gt tbg
341 ,fnd_application_vl app
342 ,gl_code_combinations_kfv gcck
343 ,gl_balances gb
344 $seg_desc_from$
345 WHERE tbg.record_type_code = ''SOURCE''
346 AND tbg.source_application_id = app.application_id
347 AND tbg.code_combination_id = gcck.code_combination_id
348 AND tbg.acctd_rounded_rem_amount <> 0
349 AND tbg.acctd_unrounded_rem_amount = 0
350 $gl_balance_join$
351 $seg_desc_join$
352 ';
353
354 -- Perf changes for TB Report Summary Template bug:8773522 --
355 C_INSERT_GT_SUMMARY_STATEMENT CONSTANT VARCHAR2(32000) := '
356 INSERT INTO xla_trial_balances_gt
357 (definition_code
358 ,ledger_id
359 ,ledger_name
360 ,ledger_short_name
361 ,ledger_currency_code
362 ,record_type_code
363 ,source_application_id
364 ,code_combination_id
365 ,acctd_unrounded_orig_amount
366 ,acctd_rounded_orig_amount
367 ,entered_unrounded_rem_amount
368 ,entered_rounded_rem_amount
369 ,acctd_unrounded_rem_amount
370 ,acctd_rounded_rem_amount
371 ,third_party_name
372 ,third_party_number
373 ,balancing_segment_value
374 ,natural_account_segment_value
375 ,cost_center_segment_value
376 ,intercompany_segment_value
377 ,management_segment_value
378 ,trx_currency_code) ';
379
380 -- Perf changes for TB Report Summary Template bug:8773522 --
381 C_INSERT_GT_SUMMARY_SELECT CONSTANT VARCHAR2(32000) := '
382 SELECT
383 summary_dat.definition_code,
384 summary_dat.ledger_id,
385 gl.name,
386 gl.short_name,
387 gl.currency_code,
388 ''SUMMARY'',
389 summary_dat.source_application_id,
390 summary_dat.code_combination_id,
391 decode(gl.ledger_category_code,''PRIMARY'',summary_dat.SUM_acctd_unrounded_orig_amt ,0),
392 decode(gl.ledger_category_code,''PRIMARY'',summary_dat.SUM_acctd_rounded_orig_amt,0),
393 summary_dat.sum_entd_unrounded_rem_amount,
394 summary_dat.sum_entd_rounded_rem_amount,
395 summary_dat.sum_acctd_unrounded_rem_amount,
396 summary_dat.sum_acctd_rounded_rem_amount,
397 summary_dat.party_name,
398 summary_dat.party_id,
399 summary_dat.balancing_segment_value,
400 summary_dat.natural_account_segment_value,
401 summary_dat.cost_center_segment_value,
402 summary_dat.intercompany_segment_value,
403 summary_dat.management_segment_value,
404 gl.currency_code
405 FROM
406 (
407 SELECT
408 tb.definition_code,
409 tb.ledger_id,
410 tb.source_application_id,
411 tb.code_combination_id,
412 SUM(tb.entered_unrounded_rem_amount) SUM_ENTD_UNROUNDED_REM_AMOUNT,
413 SUM(tb.entered_rounded_rem_amount) SUM_entd_rounded_rem_amount ,
414 SUM(tb.acctd_unrounded_rem_amount) SUM_acctd_unrounded_rem_amount ,
415 SUM(tb.acctd_rounded_rem_amount) SUM_acctd_rounded_rem_amount ,
416 SUM(nvl(tiv.base_amount,tiv.invoice_amount)) SUM_acctd_unrounded_orig_amt,
417 SUM(nvl(tiv.base_amount,tiv.invoice_amount)) SUM_acctd_rounded_orig_amt,
418 tiv.party_name,
419 tb.party_id,
420 tb.balancing_segment_value,
421 tb.natural_account_segment_value,
422 tb.cost_center_segment_value,
423 tb.intercompany_segment_value,
424 tb.management_segment_value
425 FROM
426 AP_SLA_INVOICES_TRANSACTION_V tiv,
427 xla_transaction_entities xte,
428 -- inline view
429 ( SELECT /*+ parallel(xtb) leading(xtb) NO_MERGE */ --added hint bug#8409806 bug9133956
430 xtb.definition_code,
431 nvl(xtb.applied_to_entity_id,xtb.source_entity_id) entity_id,
432 xtb.code_combination_id ,
433 xtb.source_application_id,
434 SUM (Nvl(xtb.entered_unrounded_cr,0)) - SUM (Nvl(xtb.entered_unrounded_dr,0)) entered_unrounded_rem_amount,
435 SUM (Nvl(xtb.entered_rounded_cr,0)) - SUM (Nvl(xtb.entered_rounded_dr,0)) entered_rounded_rem_amount,
436 SUM (Nvl(xtb.acctd_unrounded_cr,0)) - SUM (Nvl(xtb.acctd_unrounded_dr,0)) acctd_unrounded_rem_amount,
437 SUM (Nvl(xtb.acctd_rounded_cr,0)) - SUM (Nvl(xtb.acctd_rounded_dr,0)) acctd_rounded_rem_amount,
438 xtb.ledger_id,
439 xtb.party_id,
440 xtb.balancing_segment_value,
441 xtb.natural_account_segment_value,
442 xtb.cost_center_segment_value,
443 xtb.intercompany_segment_value,
444 xtb.management_segment_value
445 FROM xla_trial_balances xtb
446 where xtb.definition_code = :1
447 and xtb.source_application_id=200
448 and xtb.gl_date between :2 and :3
449 AND NVL(xtb.party_id,-99) = NVL(:4,NVL(xtb.party_id,-99))
450 GROUP BY xtb.definition_code,
451 nvl(xtb.applied_to_entity_id,xtb.source_entity_id) ,
452 xtb.code_combination_id ,
453 xtb.source_application_id,
454 xtb.ledger_id,
455 xtb.party_id,
456 xtb.balancing_segment_value,
457 xtb.natural_account_segment_value,
458 xtb.cost_center_segment_value,
459 xtb.intercompany_segment_value,
460 xtb.management_segment_value
461 HAVING SUM (Nvl(xtb.acctd_rounded_cr,0)) <> SUM (Nvl(xtb.acctd_rounded_dr,0))
462 ) tb
463 --end of inline view
464 $account_tab$
465 WHERE tb.entity_id=xte.entity_id
466 AND tb.source_application_id=200
467 AND xte.entity_code=''AP_INVOICES''
468 AND xte.application_id=tb.source_application_id
469 AND nvl(xte.source_id_int_1,-99)=tiv.invoice_id
470 $security_valuation_join$
471 $account_range$
472 GROUP BY
473 tb.definition_code, tb.ledger_id, tb.source_application_id,
474 tb.code_combination_id, tiv.party_name,tb.party_id,
475 tb.balancing_segment_value, tb.natural_account_segment_value,
476 tb.cost_center_segment_value, tb.intercompany_segment_value,
477 tb.management_segment_value
478 ) summary_dat ,
479 gl_ledgers gl
480 WHERE summary_dat.ledger_id=gl.ledger_id
481 ';
482
483 -- Perf changes for TB Report Summary Template bug:8773522 --
484 C_TB_SUMMARY_SOURCE_SQL CONSTANT VARCHAR2(32000) := '
485 SELECT
486 gcck.concatenated_segments ACCOUNT
487 ,$gl_balance_cols$ GL_BALANCE
488 ,tbg.code_combination_id CODE_COMBINATION_ID
489 ,tbg.balancing_segment_value BALANCING_SEGMENT_VALUE
490 ,tbg.natural_account_segment_value NATURAL_ACCOUNT_SEGMENT_VALUE
491 ,tbg.cost_center_segment_value COST_CENTER_SEGMENT_VALUE
492 ,tbg.management_segment_value MANAGEMENT_SEGMENT_VALUE
493 ,tbg.intercompany_segment_value INTERCOMPANY_SEGMENT_VALUE
494 ,tbg.ledger_id LEDGER_ID
495 ,tbg.ledger_name LEDGER_NAME
496 ,tbg.ledger_short_name LEDGER_SHORT_NAME
497 ,tbg.ledger_currency_code LEDGER_CURRENCY_CODE
498 ,tbg.third_party_name THIRD_PARTY_NAME
499 ,tbg.third_party_number THIRD_PARTY_NUMBER
500 ,tbg.third_party_type_code THIRD_PARTY_TYPE_CODE
501 ,tbg.third_party_type THIRD_PARTY_TYPE
502 ,tbg.third_party_site_name THIRD_PARTY_SITE_NAME
503 ,tbg.source_application_id SOURCE_TRX_APPLICATION_ID
504 ,app.application_name SOURCE_TRX_APPLICATION_NAME
505 ,tbg.entered_unrounded_orig_amount SUM_SRC_ENTD_UNROUND_ORG_AMT
506 ,tbg.entered_unrounded_rem_amount SUM_SRC_ENTD_UNROUND_REM_AMT
507 ,tbg.entered_rounded_orig_amount SUM_SRC_ENTD_ROUNDED_ORG_AMT
508 ,tbg.entered_rounded_rem_amount SUM_SRC_ENTD_ROUNDED_REM_AMT
509 ,tbg.acctd_unrounded_orig_amount SUM_SRC_ACCTD_UNROUND_ORG_AMT
510 ,tbg.acctd_unrounded_rem_amount SUM_SRC_ACCTD_UNROUND_REM_AMT
511 ,tbg.acctd_rounded_orig_amount SUM_SRC_ACCTD_ROUNDED_ORG_AMT
512 ,tbg.acctd_rounded_rem_amount SUM_SRC_ACCTD_ROUNDED_REM_AMT
513 ,tbg.NON_AP_AMOUNT NON_AP_AMOUNT
514 ,tbg.MANUAL_SLA_AMOUNT MANUAL_SLA_AMOUNT
515 $seg_desc_cols$
516 FROM xla_trial_balances_gt tbg
517 ,fnd_application_vl app
518 ,gl_code_combinations_kfv gcck
519 ,gl_balances gb
520 $seg_desc_from$
521 WHERE tbg.source_application_id = app.application_id
522 AND tbg.code_combination_id = gcck.code_combination_id
523 $gl_balance_join$
524 $seg_desc_join$
525 ';
526
527 /*C_TB_APPLIED_SQL CONSTANT VARCHAR2(32000) := '
528 SELECT
529 tbg.third_party_name THIRD_PARTY_NAME
530 ,tbg.third_party_number THIRD_PARTY_NUMBER
531 ,tbg.third_party_type_code THIRD_PARTY_TYPE_CODE
532 ,tbg.third_party_type THIRD_PARTY_TYPE
533 ,tbg.third_party_site_name THIRD_PARTY_SITE_NAME
534 ,tbg.applied_to_entity_id APPLIED_TO_ENTITY_ID
535 ,tbg.source_application_id APPLIED_TRX_APPLICATION_ID
536 ,app.application_name APPLIED_TRX_APPLICATION_NAME
537 ,ett.name APPLIED_TRX_TYPE
538 ,tbg.transaction_number APPLIED_TRX_NUMBER
539 ,tbg.gl_date APPLIED_TRX_GL_DATE
540 ,tbg.trx_currency_code APPLIED_TRX_CURR
541 ,tbg.entered_unrounded_orig_amount APPLIED_ENTERED_UNROUNDED_AMT
542 ,tbg.entered_rounded_orig_amount APPLIED_ENTERED_ROUNDED_AMT
543 ,tbg.acctd_unrounded_orig_amount APPLIED_ACCTD_UNROUNDED_AMT
544 ,tbg.acctd_rounded_orig_amount APPLIED_ACCTD_ROUNDED_AMT
545 ,tbg.user_trx_identifier_name_1 APPLIED_TRX_IDENTIFIER_NAME_1
546 ,tbg.user_trx_identifier_name_2 APPLIED_TRX_IDENTIFIER_NAME_2
547 ,tbg.user_trx_identifier_name_3 APPLIED_TRX_IDENTIFIER_NAME_3
548 ,tbg.user_trx_identifier_name_4 APPLIED_TRX_IDENTIFIER_NAME_4
549 ,tbg.user_trx_identifier_name_5 APPLIED_TRX_IDENTIFIER_NAME_5
550 ,tbg.user_trx_identifier_name_6 APPLIED_TRX_IDENTIFIER_NAME_6
551 ,tbg.user_trx_identifier_name_7 APPLIED_TRX_IDENTIFIER_NAME_7
552 ,tbg.user_trx_identifier_name_8 APPLIED_TRX_IDENTIFIER_NAME_8
553 ,tbg.user_trx_identifier_name_9 APPLIED_TRX_IDENTIFIER_NAME_9
554 ,tbg.user_trx_identifier_name_10 APPLIED_TRX_IDENTIFIER_NAME_10
555 ,tbg.user_trx_identifier_value_1 APPLIED_TRX_IDENTIFIER_VAL_1
556 ,tbg.user_trx_identifier_value_2 APPLIED_TRX_IDENTIFIER_VAL_2
557 ,tbg.user_trx_identifier_value_3 APPLIED_TRX_IDENTIFIER_VAL_3
558 ,tbg.user_trx_identifier_value_4 APPLIED_TRX_IDENTIFIER_VAL_4
559 ,tbg.user_trx_identifier_value_5 APPLIED_TRX_IDENTIFIER_VAL_5
560 ,tbg.user_trx_identifier_value_6 APPLIED_TRX_IDENTIFIER_VAL_6
561 ,tbg.user_trx_identifier_value_7 APPLIED_TRX_IDENTIFIER_VAL_7
562 ,tbg.user_trx_identifier_value_8 APPLIED_TRX_IDENTIFIER_VAL_8
563 ,tbg.user_trx_identifier_value_9 APPLIED_TRX_IDENTIFIER_VAL_9
564 ,tbg.user_trx_identifier_value_10 APPLIED_TRX_IDENTIFIER_VAL_10
565 FROM xla_trial_balances_gt tbg
566 ,fnd_application_vl app
567 ,xla_entity_types_vl ett
568 WHERE tbg.source_entity_code = ett.entity_code
569 AND tbg.source_application_id = ett.application_id
570 AND tbg.source_application_id = app.application_id
571 AND tbg.record_type_code = ''APPLIED''
572 AND tbg.applied_to_entity_id = :SOURCE_ENTITY_ID
573 ';*/
574
575
576 C_TB_APPLIED_SQL CONSTANT VARCHAR2(32000) := '
577 SELECT/*+ index(tbg XLA_TRIAL_BALANCES_GT_N1)*/
578 tbg.third_party_name THIRD_PARTY_NAME
579 ,tbg.third_party_number THIRD_PARTY_NUMBER
580 ,tbg.third_party_type_code THIRD_PARTY_TYPE_CODE
581 ,tbg.third_party_type THIRD_PARTY_TYPE
582 ,tbg.third_party_site_name THIRD_PARTY_SITE_NAME
583 ,tbg.applied_to_entity_id APPLIED_TO_ENTITY_ID
584 ,tbg.source_application_id APPLIED_TRX_APPLICATION_ID
585 ,app.application_name APPLIED_TRX_APPLICATION_NAME
586 ,ett.name APPLIED_TRX_TYPE
587 ,tbg.transaction_number APPLIED_TRX_NUMBER
588 ,tbg.gl_date APPLIED_TRX_GL_DATE
589
590 ,tbg.trx_currency_code APPLIED_TRX_CURR
591 ,tbg.entered_unrounded_orig_amount APPLIED_ENTERED_UNROUNDED_AMT
592 ,tbg.entered_rounded_orig_amount APPLIED_ENTERED_ROUNDED_AMT
593 ,tbg.acctd_unrounded_orig_amount APPLIED_ACCTD_UNROUNDED_AMT
594 ,tbg.acctd_rounded_orig_amount APPLIED_ACCTD_ROUNDED_AMT
595 ,tbg.user_trx_identifier_name_1 APPLIED_TRX_IDENTIFIER_NAME_1
596 ,tbg.user_trx_identifier_name_2 APPLIED_TRX_IDENTIFIER_NAME_2
597 ,tbg.user_trx_identifier_name_3 APPLIED_TRX_IDENTIFIER_NAME_3
598 ,tbg.user_trx_identifier_name_4 APPLIED_TRX_IDENTIFIER_NAME_4
599 ,tbg.user_trx_identifier_name_5 APPLIED_TRX_IDENTIFIER_NAME_5
600 ,tbg.user_trx_identifier_name_6 APPLIED_TRX_IDENTIFIER_NAME_6
601 ,tbg.user_trx_identifier_name_7 APPLIED_TRX_IDENTIFIER_NAME_7
602 ,tbg.user_trx_identifier_name_8 APPLIED_TRX_IDENTIFIER_NAME_8
603 ,tbg.user_trx_identifier_name_9 APPLIED_TRX_IDENTIFIER_NAME_9
604 ,tbg.user_trx_identifier_name_10 APPLIED_TRX_IDENTIFIER_NAME_10
605 ,tbg.user_trx_identifier_value_1 APPLIED_TRX_IDENTIFIER_VAL_1
606 ,tbg.user_trx_identifier_value_2 APPLIED_TRX_IDENTIFIER_VAL_2
607 ,tbg.user_trx_identifier_value_3 APPLIED_TRX_IDENTIFIER_VAL_3
608 ,tbg.user_trx_identifier_value_4 APPLIED_TRX_IDENTIFIER_VAL_4
609 ,tbg.user_trx_identifier_value_5 APPLIED_TRX_IDENTIFIER_VAL_5
610 ,tbg.user_trx_identifier_value_6 APPLIED_TRX_IDENTIFIER_VAL_6
611 ,tbg.user_trx_identifier_value_7 APPLIED_TRX_IDENTIFIER_VAL_7
612 ,tbg.user_trx_identifier_value_8 APPLIED_TRX_IDENTIFIER_VAL_8
613 ,tbg.user_trx_identifier_value_9 APPLIED_TRX_IDENTIFIER_VAL_9
614 ,tbg.user_trx_identifier_value_10 APPLIED_TRX_IDENTIFIER_VAL_10
615 FROM xla_trial_balances_gt tbg
616 ,fnd_application_vl app
617 ,xla_entity_types_vl ett
618 WHERE tbg.source_entity_code = ett.entity_code
619 AND tbg.source_application_id = ett.application_id
620 AND tbg.source_application_id = app.application_id
621 AND tbg.record_type_code = ''APPLIED''
622 AND tbg.code_combination_id = :CODE_COMBINATION_ID
623 AND tbg.applied_to_entity_id = :SOURCE_ENTITY_ID
624 ';
625
626
627 C_TB_UPG_SQL CONSTANT VARCHAR2(32000) := '
628 SELECT
629 gcck.concatenated_segments ACCOUNT
630 ,$gl_balance_cols$ GL_BALANCE
631 ,tb.code_combination_id CODE_COMBINATION_ID
632 ,tb.balancing_segment_value BALANCING_SEGMENT_VALUE
633 ,tb.natural_account_segment_value NATURAL_ACCOUNT_SEGMENT_VALUE
634 ,tb.cost_center_segment_value COST_CENTER_SEGMENT_VALUE
635 ,tb.management_segment_value MANAGEMENT_SEGMENT_VALUE
636 ,tb.intercompany_segment_value INTERCOMPANY_SEGMENT_VALUE
637 ,$ledger_cols$
638 ,NULL THIRD_PARTY_NAME
639 ,NULL THIRD_PARTY_NUMBER
640 ,NULL THIRD_PARTY_TYPE_CODE
641 ,NULL THIRD_PARTY_TYPE
642 ,NULL THIRD_PARTY_SITE_NAME
643 ,tb.source_application_id SOURCE_TRX_APPLICATION_ID
644 ,tb.source_entity_id SOURCE_ENTITY_ID
645 ,app.application_name SOURCE_TRX_APPLICATION_NAME
646 ,''$initial_balance$'' SOURCE_TRX_TYPE
647 ,NULL SOURCE_TRX_NUMBER
648 ,to_char(tb.gl_date,''YYYY-MM-DD'') SOURCE_TRX_GL_DATE
649 ,tb.trx_currency_code SOURCE_TRX_CURR
650 ,$amount_cols$
651 ,NULL USER_TRX_IDENTIFIER_NAME_1
652 ,NULL USER_TRX_IDENTIFIER_NAME_2
653 ,NULL USER_TRX_IDENTIFIER_NAME_3
654 ,NULL USER_TRX_IDENTIFIER_NAME_4
655 ,NULL USER_TRX_IDENTIFIER_NAME_5
656 ,NULL USER_TRX_IDENTIFIER_NAME_6
657 ,NULL USER_TRX_IDENTIFIER_NAME_7
658 ,NULL USER_TRX_IDENTIFIER_NAME_8
659 ,NULL USER_TRX_IDENTIFIER_NAME_9
660 ,NULL USER_TRX_IDENTIFIER_NAME_10
661 ,NULL USER_TRX_IDENTIFIER_VALUE_1
662 ,NULL USER_TRX_IDENTIFIER_VALUE_2
663 ,NULL USER_TRX_IDENTIFIER_VALUE_3
664 ,NULL USER_TRX_IDENTIFIER_VALUE_4
665 ,NULL USER_TRX_IDENTIFIER_VALUE_5
666 ,NULL USER_TRX_IDENTIFIER_VALUE_6
667 ,NULL USER_TRX_IDENTIFIER_VALUE_7
668 ,NULL USER_TRX_IDENTIFIER_VALUE_8
669 ,NULL USER_TRX_IDENTIFIER_VALUE_9
670 ,NULL USER_TRX_IDENTIFIER_VALUE_10
671 ,NULL NON_AP_AMOUNT
672 ,NULL MANUAL_SLA_AMOUNT
673 $seg_desc_cols$
674 FROM xla_trial_balances tb
675 ,fnd_application_vl app
676 ,gl_code_combinations_kfv gcck
677 ,gl_balances gb
678 $seg_desc_from$
679 WHERE tb.definition_code = ''$definition_code$''
680 AND tb.record_type_code = ''SOURCE''
681 AND tb.source_entity_id = -1
682 AND tb.source_application_id = app.application_id
683 AND tb.code_combination_id = gcck.code_combination_id
684 AND tb.gl_date >= NVL(''$p_start_date$'',tb.gl_date)
685 AND tb.gl_date <= NVL(''$p_as_of_date$'',tb.gl_date + 1)
686 $gl_balance_join$
687 $seg_desc_join$
688 ';
689
690 C_NEW_LINE CONSTANT VARCHAR2(8) := fnd_global.newline;
691 C_OWNER_ORACLE CONSTANT VARCHAR2(1) := 'S';
692 -------------------------------------------------------------------------------
693 -- constant for getting flexfield segment value description
694 -------------------------------------------------------------------------------
695 C_SEG_DESC_JOIN CONSTANT VARCHAR2(32000) :=
696 ' AND $alias$.flex_value_set_id = $flex_value_set_id$ '
697 || C_NEW_LINE
698 || ' AND $alias$.flex_value = $segment_column$ '
699 || C_NEW_LINE
700 || ' AND $alias$.parent_flex_value_low ' -- added for bug:7641746 for Dependant/Table Validated Value Set
701 ;
702
703
704 -------------------------------------------------------------------------------
705 -- Define Types
706 -------------------------------------------------------------------------------
707 TYPE t_array_char IS TABLE OF VARCHAR2(32000) INDEX BY BINARY_INTEGER ;
708 TYPE r_security_info IS RECORD
709 (valuation_method xla_transaction_entities.valuation_method%TYPE
710 ,security_id_int_1 xla_transaction_entities.security_id_int_1%TYPE
711 ,security_id_char_1 xla_transaction_entities.security_id_char_1%TYPE);
712
713 -------------------------------------------------------------------------------
714 -- Global Constants
715 -------------------------------------------------------------------------------
716
717
718
719
720 --
721 -- Amount Columns: Balance Side = 'Credit'
722 -- When Balance Side 'Credit' of Source Transactions,
723 -- the "balance side" of Applied to transactions is 'Debit'.
724 --
725 -- Source Trx Dr Source Trx Dr
726 -- ----------------- ------------------
727 -- 100
728 -- Source Amount = 100 (Cr) - 0 (Dr)
729 --
730 -- Applied to Trx Dr Applied to Trx Cr
731 -- ----------------- ------------------
732 -- 30
733 --
734 -- Applied to Amount = 30 (Dr) - 0 (Cr)
735 --
736 -- Remaining Amount = 100 - 30 = 70
737 --
738 C_CR_APPLIED_AMT_COL CONSTANT VARCHAR2(32000)
739 := '
740 , SUM(NVL(xtb.entered_unrounded_dr,0)) -
741 SUM(NVL(xtb.entered_unrounded_cr,0)) entd_unrounded_appl_to_amount
742 , SUM(NVL(xtb.entered_rounded_dr,0)) -
743 SUM(NVL(xtb.entered_rounded_cr,0)) entd_rounded_appl_to_amount
744 , SUM(NVL(xtb.acctd_unrounded_dr,0)) -
745 SUM(NVL(xtb.acctd_unrounded_cr,0)) acctd_unrounded_appl_to_amount
746 , SUM(NVL(xtb.acctd_rounded_dr,0)) -
747 SUM(NVL(xtb.acctd_rounded_cr,0)) acctd_rounded_appl_to_amount
748 ';
749
750 --
751 -- Amount Columns: Balance Side = 'Debit'
752 --
753 C_DR_APPLIED_AMT_COL CONSTANT VARCHAR2(32000)
754 := '
755 , SUM(NVL(xtb.entered_unrounded_cr,0)) -
756 SUM(NVL(xtb.entered_unrounded_dr,0)) entd_unrounded_appl_to_amount
757 , SUM(NVL(xtb.entered_rounded_cr,0)) -
758 SUM(NVL(xtb.entered_rounded_dr,0)) entd_rounded_appl_to_amount
759 , SUM(NVL(xtb.acctd_unrounded_cr,0)) -
760 SUM(NVL(xtb.acctd_unrounded_dr,0)) acctd_unrounded_appl_to_amount
761 , SUM(NVL(xtb.acctd_rounded_cr,0)) -
762 SUM(NVL(xtb.acctd_rounded_dr,0)) acctd_rounded_appl_to_amount
763 ';
764
765 --
766 -- Amount Columns: Balance Side = 'Credit'
767 --
768
769 C_TB_CR_AMOUNT_COLUMN CONSTANT VARCHAR2(32000)
770 := ', sum(CASE WHEN xtb1.applied_to_entity_id IS NULL THEN
771 NVL(xtb1.entered_unrounded_cr,0) - NVL(xtb1.entered_unrounded_dr,0)
772 ELSE
773 NVL(xtb1.entered_unrounded_dr,0) - NVL(xtb1.entered_unrounded_cr,0)
774 END) entered_unrounded_orig_amount
775 , sum(CASE WHEN xtb1.applied_to_entity_id IS NULL THEN
776 NVL(xtb1.entered_rounded_cr,0) - NVL(xtb1.entered_rounded_dr,0)
777 ELSE
778 NVL(xtb1.entered_rounded_dr,0) - NVL(xtb1.entered_rounded_cr,0)
779 END) entered_rounded_orig_amount
780 , sum(CASE WHEN xtb1.applied_to_entity_id IS NULL THEN
781 NVL(xtb1.acctd_unrounded_cr,0) - NVL(xtb1.acctd_unrounded_dr,0)
782 ELSE
783 NVL(xtb1.acctd_unrounded_dr,0) - NVL(xtb1.acctd_unrounded_cr,0)
784 END) acctd_unrounded_orig_amount
785 , sum(CASE WHEN xtb1.applied_to_entity_id IS NULL THEN
786 NVL(xtb1.acctd_rounded_cr,0) - NVL(xtb1.acctd_rounded_dr,0)
787 ELSE
788 NVL(xtb1.acctd_rounded_dr,0) - NVL(xtb1.acctd_rounded_cr,0)
789 END) acctd_rounded_orig_amount
790 ';
791
792 --
793 -- Amount Columns: Balance Side = 'Debit'
794 --
795 C_TB_DR_AMOUNT_COLUMN CONSTANT VARCHAR2(32000)
796 := ', sum(CASE WHEN xtb1.applied_to_entity_id IS NULL THEN
797 NVL(xtb1.entered_unrounded_dr,0) - NVL(xtb1.entered_unrounded_cr,0)
798 ELSE
799 NVL(xtb1.entered_unrounded_cr,0) - NVL(xtb1.entered_unrounded_dr,0)
800 END) entered_unrounded_orig_amount
801 , sum(CASE WHEN xtb1.applied_to_entity_id IS NULL THEN
802 NVL(xtb1.entered_rounded_dr,0) - NVL(xtb1.entered_rounded_cr,0)
803 ELSE
804 NVL(xtb1.entered_rounded_cr,0) - NVL(xtb1.entered_rounded_dr,0)
805 END) entered_rounded_orig_amount
806 , sum(CASE WHEN xtb1.applied_to_entity_id IS NULL THEN
807 NVL(xtb1.acctd_unrounded_dr,0) - NVL(xtb1.acctd_unrounded_cr,0)
808 ELSE
809 NVL(xtb1.acctd_unrounded_cr,0) - NVL(xtb1.acctd_unrounded_dr,0)
810 END) acctd_unrounded_orig_amount
811 , sum(CASE WHEN xtb1.applied_to_entity_id IS NULL THEN
812 NVL(xtb1.acctd_rounded_dr,0) - NVL(xtb1.acctd_rounded_cr,0)
813 ELSE
814 NVL(xtb1.acctd_rounded_cr,0) - NVL(xtb1.acctd_rounded_dr,0)
815 END) acctd_rounded_orig_amount
816 ';
817
818 --
819 -- Amount Columns (Upgrade): Balance Side = 'Credit'
820 --
821 C_UPG_CR_AMOUNT_COLUMN CONSTANT VARCHAR2(32000)
822 := 'NULL SRC_ENTERED_UNROUNDED_ORIG_AMT
823 ,tb.entered_unrounded_cr SRC_ENTERED_UNROUNDED_REM_AMT
824 ,NULL SRC_ENTERED_ROUNDED_ORIG_AMT
825 ,tb.entered_rounded_cr SRC_ENTERED_ROUNDED_REM_AMT
826 ,NULL SRC_ACCTD_UNROUNDED_ORIG_AMT
827 ,tb.acctd_unrounded_cr SRC_ACCTD_UNROUNDED_REM_AMT
828 ,NULL SRC_ACCTD_ROUNDED_ORIG_AMT
829 ,tb.acctd_rounded_cr SRC_ACCTD_ROUNDED_REM_AMT
830 ';
831
832 --
833 -- Amount Columns (Upgrade): Balance Side = 'Dedit'
834 --
835 C_UPG_DR_AMOUNT_COLUMN CONSTANT VARCHAR2(32000)
836 := 'NULL SRC_ENTERED_UNROUNDED_ORIG_AMT
837 ,tb.entered_unrounded_dr SRC_ENTERED_UNROUNDED_REM_AMT
838 ,NULL SRC_ENTERED_ROUNDED_ORIG_AMT
839 ,tb.entered_rounded_dr SRC_ENTERED_ROUNDED_REM_AMT
840 ,NULL SRC_ACCTD_UNROUNDED_ORIG_AMT
841 ,tb.acctd_unrounded_dr SRC_ACCTD_UNROUNDED_REM_AMT
842 ,NULL SRC_ACCTD_ROUNDED_ORIG_AMT
843 ,tb.acctd_rounded_dr SRC_ACCTD_ROUNDED_REM_AMT
844 ';
845
846 --
847 -- Party Info: Party Type = Customer
848 --
849
850 --
851 -- Replace $party_col$ in C_INSERT_GT_SELECT
852 --
853 C_PARTY_CUST_COLUMN CONSTANT VARCHAR2(32000) := '
854 ,hzp.party_name third_party_name
855 ,hca.account_number third_party_number
856 ,tb.party_type_code third_party_type_code
857 ,$third_party_type_cust$ third_party_type
858 ,hps.party_site_name third_party_site_name
859 ,hca.account_number third_party_account_number
860 '
861 ;
862
863 --
864 -- Replace $party_tab$ in C_INSERT_GT_SELECT
865 --
866 C_PARTY_CUST_TABLE CONSTANT VARCHAR2(32000) := '
867 ,hz_parties hzp
868 ,hz_party_sites hps
869 ,hz_cust_accounts hca
870 ,hz_cust_acct_sites_all hcas
871 ,hz_cust_site_uses_all hcsu
872 ';
873
874 --
875 -- Replace $party_where$ in C_INSERT_GT_SELECT
876 --
877 C_PARTY_CUST_WHERE CONSTANT VARCHAR2(32000) := '
878 AND tb.party_id = hca.cust_account_id (+)
879 AND tb.party_type_code (+) = ''C''
880 AND hzp.party_id (+) = hca.party_id
881 AND tb.party_site_id = hcsu.site_use_id (+)
882 AND hcas.cust_acct_site_id (+) = hcsu.cust_acct_site_id
883 AND hcas.party_site_id = hps.party_site_id (+)
884 ';
885
886
887 --
888 -- Party Info: Party Type = Supplier
889 --
890 C_PARTY_SUPP_COLUMN CONSTANT VARCHAR2(32000) := '
891 ,hzp.party_name third_party_name
892 ,hzp.party_number third_party_number
893 ,tb.party_type_code third_party_type_code
894 ,$third_party_type_supp$ third_party_type
895 ,hps.party_site_name third_party_site_name
896 ,hzp.party_number third_party_account_number
897 ';
898
899 C_PARTY_SUPP_TABLE CONSTANT VARCHAR2(32000) := '
900 ,ap_suppliers aps
901 ,ap_supplier_sites_all apss
902 ,hz_parties hzp
903 ,hz_party_sites hps
904 ';
905
906 --
907 -- Include the cases that party information is null in ae lines
908 --
909 C_PARTY_SUPP_WHERE CONSTANT VARCHAR2(32000) := '
910 AND (tb.party_type_code is NULL OR tb.party_type_code = ''S'')
911 AND tb.party_id = aps.vendor_id (+)
912 AND tb.party_site_id = apss.vendor_site_id (+)
913 AND aps.party_id = hzp.party_id (+)
914 AND NVL(apss.party_site_id,0) = hps.party_site_id (+) --added nvl for bug 6601283,in case where supplier is employee,party_site_id should be null
915
916 ';
917
918 C_INSERT_GT_STATEMENT CONSTANT VARCHAR2(32000) := '
919 INSERT INTO xla_trial_balances_gt
920 (definition_code
921 ,ledger_id
922 ,ledger_name
923 ,ledger_short_name
924 ,ledger_currency_code
925 ,record_type_code
926 ,source_application_id
927 ,source_entity_id
928 ,source_entity_code
929 ,transaction_number
930 ,code_combination_id
931 ,gl_date
932 ,entered_unrounded_orig_amount
933 ,entered_rounded_orig_amount
934 ,acctd_unrounded_orig_amount
935 ,acctd_rounded_orig_amount
936 ,entered_unrounded_rem_amount
937 ,entered_rounded_rem_amount
938 ,acctd_unrounded_rem_amount
939 ,acctd_rounded_rem_amount
940 ,third_party_name
941 ,third_party_number
942 ,balancing_segment_value
943 ,natural_account_segment_value
944 ,cost_center_segment_value
945 ,intercompany_segment_value
946 ,management_segment_value
947 ,applied_to_entity_id
948 ,trx_currency_code
949 ,user_trx_identifier_name_1
950 ,user_trx_identifier_value_1
951 ,user_trx_identifier_name_2
952 ,user_trx_identifier_value_2
953 ,user_trx_identifier_name_3
954 ,user_trx_identifier_value_3
955 ,user_trx_identifier_name_4
956 ,user_trx_identifier_value_4
957 ,user_trx_identifier_name_5
958 ,user_trx_identifier_value_5
959 ,user_trx_identifier_name_6
960 ,user_trx_identifier_value_6
961 ,user_trx_identifier_name_7
962 ,user_trx_identifier_value_7
963 ,user_trx_identifier_name_8
964 ,user_trx_identifier_value_8
965 ,user_trx_identifier_name_9
966 ,user_trx_identifier_value_9
967 ,user_trx_identifier_name_10
968 ,user_trx_identifier_value_10) ';
969
970 -- added leading(xtb) hint for Perf Changes for bug:8773522
971 C_INSERT_GT_SELECT CONSTANT VARCHAR2(32000) := '
972 SELECT
973 tb.definition_code,
974 tb.ledger_id,
975 gl.name,
976 gl.short_name,
977 gl.currency_code,
978 ''X'',
979 tb.source_application_id,
980 tb.entity_id,
981 xte.entity_code,
982 xte.transaction_number,
983 tb.code_combination_id,
984 tiv.invoice_date,
985 --added bug 7359012 original amounts would be displayed only for primary ledger.
986 decode(gl.ledger_category_code,''PRIMARY'',tiv.invoice_amount,0),
987 decode(gl.ledger_category_code,''PRIMARY'',tiv.invoice_amount,0),
988 decode(gl.ledger_category_code,''PRIMARY'',nvl(tiv.base_amount,tiv.invoice_amount),0),
989 decode(gl.ledger_category_code,''PRIMARY'',nvl(tiv.base_amount,tiv.invoice_amount),0),
990 --end bug 7359012
991 tb.entered_unrounded_rem_amount,
992 tb.entered_rounded_rem_amount,
993 tb.acctd_unrounded_rem_amount,
994 tb.acctd_rounded_rem_amount,
995 tiv.party_name,
996 tb.party_id,
997 tb.balancing_segment_value,
998 tb.natural_account_segment_value,
999 tb.cost_center_segment_value,
1000 tb.intercompany_segment_value,
1001 tb.management_segment_value,
1002 tb.entity_id,
1003 tiv.invoice_currency_code, --added for bug 8321482 Removed hard-coded USD
1004 ''Party Name'' USER_TRX_IDENTIFIER_NAME_1,
1005 TIV.PARTY_NAME USER_TRX_IDENTIFIER_VALUE_1,
1006 ''Party Site Name'' USER_TRX_IDENTIFIER_NAME_2,
1007 TIV.PARTY_SITE_NAME USER_TRX_IDENTIFIER_VALUE_2,
1008 ''Invoice Number'' USER_TRX_IDENTIFIER_NAME_3,
1009 TIV.INVOICE_NUM USER_TRX_IDENTIFIER_VALUE_3,
1010 ''Invoice Amount'' USER_TRX_IDENTIFIER_NAME_4,
1011 to_char(TIV.INVOICE_AMOUNT) USER_TRX_IDENTIFIER_VALUE_4,
1012 --remod ''Invoice Currency'' USER_TRX_IDENTIFIER_NAME_5,
1013 --remod TIV.INVOICE_CURRENCY_CODE USER_TRX_IDENTIFIER_VALUE_5,
1014 ''Due Days'' USER_TRX_IDENTIFIER_NAME_5,
1015 TIV.DUE_DAYS USER_TRX_IDENTIFIER_VALUE_5,
1016 ''Invoice Ledger Amount'' USER_TRX_IDENTIFIER_NAME_6,
1017 to_char(TIV.BASE_AMOUNT) USER_TRX_IDENTIFIER_VALUE_6,
1018 ''Payment Status'' USER_TRX_IDENTIFIER_NAME_7,
1019 tiv.PAYMENT_STATUS USER_TRX_IDENTIFIER_VALUE_7,
1020 ''Invoice Date'' USER_TRX_IDENTIFIER_NAME_8,
1021 to_char(TIV.INVOICE_DATE,''YYYY-MM-DD"T"hh:mi:ss'') USER_TRX_IDENTIFIER_VALUE_8,
1022 ''Cancelled Date'' USER_TRX_IDENTIFIER_NAME_9,
1023 to_char(TIV.CANCELLED_DATE,''YYYY-MM-DD"T"hh:mi:ss'') USER_TRX_IDENTIFIER_VALUE_9,
1024 ''Invoice Description'' USER_TRX_IDENTIFIER_NAME_10,
1025 TIV.DESCRIPTION USER_TRX_IDENTIFIER_VALUE_10
1026 FROM
1027 AP_SLA_INVOICES_TRANSACTION_V tiv,
1028 xla_transaction_entities xte,
1029 gl_ledgers gl,
1030 -- inline view
1031 ( SELECT /*+ parallel(xtb) leading(xtb) NO_MERGE */ --added hint bug#8409806 -- leading hint for bug:9165098
1032 /* added NO_MERGE for bug:9473043 */
1033 xtb.definition_code,
1034 nvl(xtb.applied_to_entity_id,xtb.source_entity_id) entity_id,
1035 xtb.code_combination_id ,
1036 xtb.source_application_id,
1037 SUM (Nvl(xtb.entered_unrounded_cr,0)) - SUM (Nvl(xtb.entered_unrounded_dr,0)) entered_unrounded_rem_amount,
1038 SUM (Nvl(xtb.entered_rounded_cr,0)) - SUM (Nvl(xtb.entered_rounded_dr,0)) entered_rounded_rem_amount,
1039 SUM (Nvl(xtb.acctd_unrounded_cr,0)) - SUM (Nvl(xtb.acctd_unrounded_dr,0)) acctd_unrounded_rem_amount,
1040 SUM (Nvl(xtb.acctd_rounded_cr,0)) - SUM (Nvl(xtb.acctd_rounded_dr,0)) acctd_rounded_rem_amount,
1041 xtb.ledger_id,
1042 xtb.party_id,
1043 xtb.balancing_segment_value,
1044 xtb.natural_account_segment_value,
1045 xtb.cost_center_segment_value,
1046 xtb.intercompany_segment_value,
1047 xtb.management_segment_value
1048 FROM xla_trial_balances xtb
1049 where xtb.definition_code = :1
1050 and xtb.source_application_id=200
1051 and xtb.gl_date between :2 and :3
1052 AND NVL(xtb.party_id,-99) = NVL(:4,NVL(xtb.party_id,-99))
1053
1054 GROUP BY xtb.definition_code,
1055 nvl(xtb.applied_to_entity_id,xtb.source_entity_id) ,
1056 xtb.code_combination_id ,
1057 xtb.source_application_id,
1058 xtb.ledger_id,
1059 xtb.party_id,
1060 xtb.balancing_segment_value,
1061 xtb.natural_account_segment_value,
1062 xtb.cost_center_segment_value,
1063 xtb.intercompany_segment_value,
1064 xtb.management_segment_value
1065 HAVING SUM (Nvl(xtb.acctd_rounded_cr,0)) <> SUM (Nvl(xtb.acctd_rounded_dr,0))
1066 ) tb
1067 $account_tab$
1068 --end of inline view
1069 where tb.entity_id=xte.entity_id
1070 and tb.source_application_id=200
1071 and xte.entity_code=''AP_INVOICES''
1072 and xte.application_id=tb.source_application_id
1073 --and xte.ledger_id=tb.ledger_id removed join to make report work for reporting/secondary ledger,Bug 7331692
1074 and nvl(xte.source_id_int_1,-99)=tiv.invoice_id
1075 and tb.ledger_id=gl.ledger_id
1076 $account_range$
1077 ';
1078
1079 -- C_ORDER_BY CONSTANT VARCHAR2(2000) := '
1080 -- ORDER BY
1081 -- cck.concatenated_segs
1082 -- ,tbg.third_party_name
1083 --';
1084
1085 --=============================================================================
1086 -- *********** Global Variables **********
1087 --=============================================================================
1088 g_ledger_info xla_tb_data_manager_pvt.r_ledger_info;
1089 g_defn_info xla_tb_data_manager_pvt.r_definition_info;
1090
1091 --=============================================================================
1092 -- *********** Local Trace Routine **********
1093 --=============================================================================
1094 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
1095 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
1096 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
1097 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
1098 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
1099 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
1100
1101 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
1102 C_DEFAULT_MODULE CONSTANT VARCHAR2(240)
1103 := 'xla.plsql.xla_tb_report_pvt';
1104
1105 g_log_level NUMBER;
1106 g_log_enabled BOOLEAN;
1107
1108 PROCEDURE trace
1109 (p_msg IN VARCHAR2
1110 ,p_level IN NUMBER
1111 ,p_module IN VARCHAR2 DEFAULT C_DEFAULT_MODULE)
1112
1113 IS
1114 BEGIN
1115 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
1116 fnd_log.message(p_level, p_module);
1117 ELSIF p_level >= g_log_level THEN
1118 fnd_log.string(p_level, p_module, p_msg);
1119 END IF;
1120
1121 EXCEPTION
1122 WHEN xla_exceptions_pkg.application_exception THEN
1123 RAISE;
1124 WHEN OTHERS THEN
1125 xla_exceptions_pkg.raise_message
1126 (p_location => 'xla_tb_report_pvt.trace');
1127 END trace;
1128
1129 /*======================================================================+
1130 | |
1131 | Private Procedure |
1132 | |
1133 | Print_Logfile |
1134 | |
1135 | Print concurrent request logs. |
1136 | |
1137 +======================================================================*/
1138 PROCEDURE print_logfile(p_msg IN VARCHAR2) IS
1139 BEGIN
1140
1141 fnd_file.put_line(fnd_file.log,p_msg);
1142
1143 EXCEPTION
1144 WHEN xla_exceptions_pkg.application_exception THEN
1145 RAISE;
1146 WHEN OTHERS THEN
1147 xla_exceptions_pkg.raise_message
1148 (p_location => 'xla_tb_report_pvt.print_logfile');
1149
1150 END print_logfile;
1151
1152
1153 /*======================================================================+
1154 | |
1155 | Private Function |
1156 | |
1157 | Dump_Text |
1158 | |
1159 | Dump text into fnd_log_messages. |
1160 | |
1161 +======================================================================*/
1162 PROCEDURE dump_text
1163 (
1164 p_text IN VARCHAR2
1165 )
1166 IS
1167 l_cur_position INTEGER;
1168 l_next_cr_position INTEGER;
1169 l_text_length INTEGER;
1170 l_log_module VARCHAR2 (2000);
1171
1172 BEGIN
1173 IF g_log_enabled THEN
1174 l_log_module := C_DEFAULT_MODULE||'.dump_text';
1175 END IF;
1176
1177 --Dump the SQL command
1178 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1179 l_cur_position := 1;
1180 l_next_cr_position := 0;
1181 l_text_length := LENGTH(p_text);
1182
1183 WHILE l_next_cr_position < l_text_length
1184 LOOP
1185 l_next_cr_position := INSTR( p_text
1186 ,C_NEW_LINE
1187 ,l_cur_position
1188 );
1189
1190 IF l_next_cr_position = 0
1191 THEN
1192 l_next_cr_position := l_text_length;
1193 END IF;
1194
1195 trace
1196 (p_msg => SUBSTR( p_text
1197 ,l_cur_position
1198 ,l_next_cr_position
1199 - l_cur_position
1200 + 1
1201 )
1202 ,p_level => C_LEVEL_STATEMENT
1203 ,p_module => l_log_module);
1204
1205 IF l_cur_position < l_text_length
1206 THEN
1207 l_cur_position := l_next_cr_position + 1;
1208 END IF;
1209 END LOOP;
1210 END IF;
1211
1212 EXCEPTION
1213 WHEN xla_exceptions_pkg.application_exception THEN
1214 RAISE;
1215 WHEN OTHERS THEN
1216 xla_exceptions_pkg.raise_message
1217 (p_location => 'xla_tb_report_pvt.dump_text');
1218 END dump_text;
1219
1220 /*======================================================================+
1221 | |
1222 | Private Function |
1223 | |
1224 | Get_Period_Name |
1225 | |
1226 | |
1227 | Retrieve Current Period Name |
1228 | |
1229 +======================================================================*/
1230 FUNCTION get_period_name
1231 (p_ledger_id IN NUMBER) RETURN VARCHAR2
1232 IS
1233
1234 l_log_module VARCHAR2(240);
1235
1236 l_period_name VARCHAR2(30);
1237
1238 l_as_of_date DATE;
1239
1240
1241 -- add TB phase4 bug#7600550 bug#8278138
1242 CURSOR c_latest_open_period IS
1243 SELECT latest_opened_period_name , gp.start_date
1244 FROM gl_ledgers gl , gl_periods gp
1245 where ledger_id = p_ledger_id
1246 AND gp.period_set_name = gl.period_set_name
1247 AND gp.period_type = gl.accounted_period_type
1248 AND NVL(gp.adjustment_period_flag,'N')='N'
1249 AND gp.period_name = gl.latest_opened_period_name;
1250
1251
1252 CURSOR c_max_open_period IS
1253 SELECT period_name
1254 FROM gl_period_statuses
1255 WHERE application_id =101
1256 AND ledger_id = p_ledger_id
1257 AND NVL(adjustment_period_flag,'N')='N'
1258 AND closing_status = 'O'
1259 AND effective_period_num =
1260 (
1261 SELECT max(effective_period_num)
1262 FROM gl_period_statuses
1263 WHERE application_id = 101
1264 AND ledger_id = p_ledger_id
1265 AND NVL(adjustment_period_flag,'N')='N'
1266 AND closing_status = 'O'
1267 );
1268
1269 l_latest_opened_period_name gl_periods.period_name%TYPE;
1270 l_start_date gl_periods.start_date%TYPE;
1271
1272
1273
1274 BEGIN
1275
1276 IF g_log_enabled THEN
1277 l_log_module := C_DEFAULT_MODULE||'.get_period_name';
1278 END IF;
1279
1280 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1281
1282 trace
1283 (p_msg => 'BEGIN of replace_gl_bal_string'
1284 ,p_level => C_LEVEL_PROCEDURE
1285 ,p_module => l_log_module);
1286
1287 END IF;
1288
1289 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1290
1291 trace
1292 (p_msg => 'p_ledger_id: ' || p_ledger_id
1293 ,p_level => C_LEVEL_STATEMENT
1294 ,p_module => l_log_module);
1295
1296 END IF;
1297
1298 l_as_of_date := p_as_of_date;
1299
1300 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1301
1302 trace
1303 (p_msg => 'p_as_of_date: ' || p_as_of_date || ' l_as_of_date ' || l_as_of_date
1304 ,p_level => C_LEVEL_STATEMENT
1305 ,p_module => l_log_module);
1306
1307 END IF;
1308
1309 --
1310 -- Retrieve current period name
1311 --
1312
1313
1314 --added bug 6684579 ,pick as_of_date period instead of sysdate period if as of date is provided
1315 /*
1316 SELECT gp.period_name
1317 INTO l_period_name
1318 FROM gl_ledgers gl
1319 ,gl_periods gp
1320 WHERE gl.ledger_id = p_ledger_id
1321 AND gp.period_set_name = gl.period_set_name
1322 AND gp.period_type = gl.accounted_period_type
1323 AND NVL(TRUNC(l_as_of_date),TRUNC(sysdate)) BETWEEN gp.start_date AND gp.end_date
1324 AND NVL(gp.adjustment_period_flag,'N')='N';*/
1325
1326 /*
1327 Prior to TB phase4 bug#7600550 if as of date entered by user is of future period like
1328 1-JAN-2030 and the period_name does not exists in gl_balance the gl balance was
1329 showing 0 and the remaining amount is calculated based on the date range.
1330 To avoid gl_balance being shown 0 in such cases following logic is followed:
1331 a. Check whether the period exists in gl_balance for as_of_date entered
1332 b. If not in the Exceptions block get the latest open period and start date
1333 IF latest open period is NULL then its an adjustment period...
1334 Obtain the actual period_name for the latest open period in gl_period_statuses
1335 for the ledger and return.
1336 ELSIF as_of_date entered is < than start_date of latest open period
1337 Return Null ( example as_of_date entered is a prior period like '01-JAN-1930'
1338 ELSE
1339 Return the latest open period name
1340 END IF;
1341 */
1342
1343 SELECT gp.period_name
1344 INTO l_period_name
1345 FROM gl_ledgers gl
1346 ,gl_periods gp
1347 WHERE gl.ledger_id = p_ledger_id
1348 AND gp.period_set_name = gl.period_set_name
1349 AND gp.period_type = gl.accounted_period_type
1350 AND NVL(TRUNC(l_as_of_date),TRUNC(sysdate)) BETWEEN gp.start_date AND gp.end_date
1351 AND NVL(gp.adjustment_period_flag,'N')='N'
1352 AND EXISTS
1353 ( SELECT 1
1354 FROM gl_balances gb
1355 WHERE gb.ledger_id = gl.ledger_id
1356 AND gb.period_name = gp.period_name
1357 )
1358 ;
1359
1360 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1361
1362 trace
1363 (p_msg => 'l_period_name' || l_period_name
1364 ,p_level => C_LEVEL_PROCEDURE
1365 ,p_module => l_log_module);
1366
1367 END IF;
1368
1369 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1370
1371 trace
1372 (p_msg => 'END of get_period_name'
1373 ,p_level => C_LEVEL_PROCEDURE
1374 ,p_module => l_log_module);
1375
1376 END IF;
1377
1378 RETURN l_period_name;
1379
1380 EXCEPTION
1381 WHEN NO_DATA_FOUND THEN
1382 IF l_period_name IS NULL THEN
1383
1384 OPEN c_latest_open_period;
1385 FETCH c_latest_open_period INTO l_latest_opened_period_name,
1386 l_start_date;
1387 CLOSE c_latest_open_period;
1388
1389 IF l_start_date IS NULL THEN
1390 --This condition indicates that its a adjustment period
1391 -- pick up the max open period for a ledger
1392 OPEN c_max_open_period;
1393 FETCH c_max_open_period INTO l_period_name;
1394 CLOSE c_max_open_period;
1395 ELSIF p_as_of_date < l_start_date THEN
1396 l_period_name:= NULL;
1397 ELSE
1398 l_period_name:= l_latest_opened_period_name;
1399 END IF;
1400
1401 END IF;
1402
1403 RETURN l_period_name;
1404
1405 WHEN xla_exceptions_pkg.application_exception THEN
1406 RAISE;
1407 WHEN OTHERS THEN
1408 xla_exceptions_pkg.raise_message
1409 ('XLA' , 'XLA_COMMON_FAILURE'
1410 ,'LOCATION' , 'xla_tb_report_pvt.get_period_name'
1411 ,'ERROR' , sqlerrm);
1412
1413 END get_period_name;
1414
1415 /*======================================================================+
1416 | |
1417 | Private Function |
1418 | |
1419 | Replace_Gl_Bal_String |
1420 | |
1421 | |
1422 | Replace GL balance related string in C_TB_SOURCE_SQL |
1423 | |
1424 +======================================================================*/
1425 FUNCTION replace_gl_bal_string
1426 (p_select_sql IN VARCHAR2
1427 ,p_ledger_id IN NUMBER
1428 ,p_account_balance_code IN VARCHAR2
1429 ,p_balance_side_code IN VARCHAR2
1430 ,p_upg_flag IN VARCHAR2) RETURN VARCHAR2
1431 IS
1432
1433 l_log_module VARCHAR2(240);
1434
1435 l_period_name VARCHAR2(30);
1436 l_balance_cols VARCHAR2(32000);
1437 l_balance_join VARCHAR2(32000);
1438 l_select_sql VARCHAR2(32000);
1439
1440 C_YEAR_TO_DATE_CR_COL CONSTANT VARCHAR2(2000) :=
1441 '(NVL(gb.begin_balance_cr,0) -
1442 NVL(gb.begin_balance_dr,0)) +
1443 (NVL(gb.period_net_cr,0) -
1444 NVL(gb.period_net_dr,0))';
1445
1446 C_YEAR_TO_DATE_DR_COL CONSTANT VARCHAR2(2000) :=
1447 '(NVL(gb.begin_balance_dr,0) -
1448 NVL(gb.begin_balance_cr,0)) +
1449 (NVL(gb.period_net_dr,0) -
1450 NVL(gb.period_net_cr,0))';
1451
1452 C_CURRENT_PERIOD_CR_COL CONSTANT VARCHAR2(2000) :=
1453 '(NVL(gb.period_net_cr,0) -
1454 NVL(gb.period_net_dr,0))';
1455
1456 C_CURRENT_PERIOD_DR_COL CONSTANT VARCHAR2(2000) :=
1457 '(NVL(gb.period_net_dr,0) -
1458 NVL(gb.period_net_cr,0))';
1459
1460 BEGIN
1461
1462 IF g_log_enabled THEN
1463 l_log_module := C_DEFAULT_MODULE||'.replace_gl_bal_string';
1464 END IF;
1465
1466 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1467
1468 trace
1469 (p_msg => 'BEGIN of replace_gl_bal_string'
1470 ,p_level => C_LEVEL_PROCEDURE
1471 ,p_module => l_log_module);
1472
1473 END IF;
1474
1475 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1476
1477 trace
1478 (p_msg => 'p_ledger_id: ' || p_ledger_id
1479 ,p_level => C_LEVEL_STATEMENT
1480 ,p_module => l_log_module);
1481
1482 trace
1483 (p_msg => 'p_account_balance_code: ' || p_account_balance_code
1484 ,p_level => C_LEVEL_STATEMENT
1485 ,p_module => l_log_module);
1486
1487 trace
1488 (p_msg => 'p_balance_side_code: ' || p_balance_side_code
1489 ,p_level => C_LEVEL_STATEMENT
1490 ,p_module => l_log_module);
1491
1492 END IF;
1493
1494 --
1495 --
1496 -- Build columns to return GL Balances
1497 --
1498 --
1499 --IF p_account_balance_code = 'YEAR_TO_DATE' THEN
1500 -- commented from TB phase4 7600550 GL Balance will be calculated
1501 -- year_to_date
1502
1503 IF p_balance_side_code = 'C' THEN
1504
1505 --
1506 -- Balance Side = 'Credit'
1507 --
1508 l_balance_cols := C_YEAR_TO_DATE_CR_COL;
1509
1510 ELSIF p_balance_side_code = 'D' THEN
1511
1512 --
1513 -- Balance Side = 'Debit'
1514 --
1515 l_balance_cols := C_YEAR_TO_DATE_DR_COL;
1516
1517 END IF;
1518
1519 /* ELSIF p_account_balance_code = 'CURR_PERIOD' THEN
1520
1521 IF p_balance_side_code = 'C' THEN
1522
1523 --
1524 -- Balance Side = 'Credit'
1525 --
1526 l_balance_cols := C_CURRENT_PERIOD_CR_COL;
1527
1528 ELSE
1529
1530 --
1531 -- Balance Side = 'Debit'
1532 --
1533 l_balance_cols := C_CURRENT_PERIOD_DR_COL;
1534
1535 END IF;
1536 END IF; */
1537
1538 --
1539 --
1540 -- Build where clauses for GL Balances
1541 --
1542 --
1543
1544 --
1545 -- Retrieve current period name
1546 --
1547 l_period_name := get_period_name
1548 (p_ledger_id => p_ledger_id);
1549
1550 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1551
1552 trace
1553 (p_msg => 'Period Name: ' || l_period_name
1554 ,p_level => C_LEVEL_STATEMENT
1555 ,p_module => l_log_module);
1556
1557 END IF;
1558
1559 --
1560 -- Build Join Conditions
1561 --
1562 IF NVL(p_upg_flag,'Y') = 'N' THEN
1563 l_balance_join :=
1564 ' AND gb.code_combination_id (+) = tbg.code_combination_id ' || C_NEW_LINE ||
1565 ' AND gb.ledger_id (+) = tbg.ledger_id ' || C_NEW_LINE ||
1566 ' AND gb.actual_flag (+) = ''A'' ' || C_NEW_LINE ||
1567 ' AND gb.currency_code (+) = tbg.ledger_currency_code ' || C_NEW_LINE ||
1568 ' AND gb.period_name (+) = ' || '''' || l_period_name || '''';
1569 ELSE
1570 l_balance_join :=
1571 ' AND gb.code_combination_id (+) = tb.code_combination_id ' || C_NEW_LINE ||
1572 ' AND gb.ledger_id (+) = tb.ledger_id ' || C_NEW_LINE ||
1573 ' AND gb.actual_flag (+) = ''A'' ' || C_NEW_LINE ||
1574 ' AND gb.currency_code (+) = ' || ''''
1575 || g_ledger_info.currency_code
1576 || ''''
1577 || C_NEW_LINE ||
1578 ' AND gb.period_name (+) = ' || '''' || l_period_name || '''';
1579 END IF;
1580 --
1581 -- Replace strings in p_select_sql
1582 --
1583 l_select_sql := p_select_sql;
1584
1585 l_select_sql := REPLACE(l_select_sql,'$gl_balance_cols$',l_balance_cols);
1586 l_select_sql := REPLACE(l_select_sql,'$gl_balance_join$',l_balance_join);
1587
1588 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1589
1590 trace
1591 (p_msg => 'END of replace_gl_bal_string'
1592 ,p_level => C_LEVEL_PROCEDURE
1593 ,p_module => l_log_module);
1594
1595 END IF;
1596
1597 RETURN l_select_sql;
1598
1599 EXCEPTION
1600 WHEN xla_exceptions_pkg.application_exception THEN
1601 RAISE;
1602 WHEN OTHERS THEN
1603 xla_exceptions_pkg.raise_message
1604 ('XLA' , 'XLA_COMMON_FAILURE'
1605 ,'LOCATION' , 'xla_tb_report_pvt.replace_gl_bal_string'
1606 ,'ERROR' , sqlerrm);
1607
1608 END replace_gl_bal_string;
1609
1610 /*======================================================================+
1611 | |
1612 | Private Function |
1613 | |
1614 | Replace_String_for_Party |
1615 | |
1616 | |
1617 | Replace party related string in C_INSERT_GT_SELECT. |
1618 | |
1619 +======================================================================*/
1620 FUNCTION replace_party_string
1621 (p_party_id IN NUMBER
1622 ,p_party_type_code IN VARCHAR2 -- <C/S>
1623 ,p_insert_sql IN VARCHAR2) RETURN VARCHAR2
1624
1625 IS
1626
1627 l_log_module VARCHAR2(240);
1628
1629 l_cust_meaning VARCHAR2(80);
1630 l_supp_meaning VARCHAR2(80);
1631 l_party_type_code VARCHAR2(30);
1632 l_party_column_cust VARCHAR2(32000);
1633 l_party_column_supp VARCHAR2(32000);
1634
1635 l_insert_gt_sql VARCHAR2(32000);
1636
1637 BEGIN
1638
1639 IF g_log_enabled THEN
1640 l_log_module := C_DEFAULT_MODULE||'.replace_party_string';
1641 END IF;
1642
1643 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1644
1645 trace
1646 (p_msg => 'BEGIN of replace_party_string'
1647 ,p_level => C_LEVEL_PROCEDURE
1648 ,p_module => l_log_module);
1649
1650 END IF;
1651
1652 l_insert_gt_sql := p_insert_sql;
1653
1654 --
1655 -- Replace $party_col$, $party_Tab$, and $party_where$
1656 --
1657 SELECT xlc.meaning
1658 ,xls.meaning
1659 INTO l_cust_meaning
1660 ,l_supp_meaning
1661 FROM xla_lookups xlc, xla_lookups xls
1662 WHERE xlc.lookup_type = 'XLA_PARTY_TYPE'
1663 AND xlc.lookup_code = 'C'
1664 AND xls.lookup_type = 'XLA_PARTY_TYPE'
1665 AND xls.lookup_code = 'S';
1666
1667 --
1668 -- Retrieve Party Type Code
1669 --
1670
1671 IF p_party_type_code = 'C' THEN
1672
1673 l_party_column_cust := REPLACE(C_PARTY_CUST_COLUMN
1674 ,'$third_party_type_cust$'
1675 ,''''||l_cust_meaning||'''');
1676
1677 l_insert_gt_sql := REPLACE (l_insert_gt_sql
1678 ,'$party_col$'
1679 ,l_party_column_cust);
1680
1681 l_insert_gt_sql := REPLACE (l_insert_gt_sql
1682 ,'$party_tab$'
1683 ,C_PARTY_CUST_TABLE);
1684
1685 l_insert_gt_sql := REPLACE (l_insert_gt_sql
1686 ,'$party_where$'
1687 ,C_PARTY_CUST_WHERE);
1688
1689 ELSIF p_party_type_code = 'S' THEN
1690
1691 l_party_column_supp := REPLACE(C_PARTY_SUPP_COLUMN
1692 ,'$third_party_type_supp$'
1693 ,''''||l_supp_meaning||'''');
1694
1695 l_insert_gt_sql := REPLACE (l_insert_gt_sql
1696 ,'$party_col$'
1697 ,l_party_column_supp);
1698
1699 l_insert_gt_sql := REPLACE (l_insert_gt_sql
1700 ,'$party_tab$'
1701 ,C_PARTY_SUPP_TABLE);
1702
1703 l_insert_gt_sql := REPLACE (l_insert_gt_sql
1704 ,'$party_where$'
1705 ,C_PARTY_SUPP_WHERE);
1706 END IF;
1707
1708 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1709
1710 trace
1711 (p_msg => 'END of replace_party_string'
1712 ,p_level => C_LEVEL_PROCEDURE
1713 ,p_module => l_log_module);
1714
1715 END IF;
1716
1717 RETURN l_insert_gt_sql;
1718
1719 EXCEPTION
1720 WHEN xla_exceptions_pkg.application_exception THEN
1721 RAISE;
1722 WHEN OTHERS THEN
1723 xla_exceptions_pkg.raise_message
1724 ('XLA' , 'XLA_COMMON_FAILURE'
1725 ,'LOCATION' , 'xla_tb_report_pvt.replace_party_string'
1726 ,'ERROR' , sqlerrm);
1727
1728 END replace_party_string;
1729
1730 /*======================================================================+
1731 | |
1732 | Private Function |
1733 | |
1734 | get_flex_range_where |
1735 | |
1736 | |
1737 | Return where clauses for flexfield ranges |
1738 | |
1739 +======================================================================*/
1740 FUNCTION get_flex_range_where
1741 (p_coa_id IN NUMBER
1742 ,p_account_from IN VARCHAR2
1743 ,p_account_to IN VARCHAR2) RETURN VARCHAR
1744
1745 IS
1746
1747 l_log_module VARCHAR2(240);
1748
1749 l_where VARCHAR2(32000);
1750 l_bind_variables fnd_flex_xml_publisher_apis.bind_variables;
1751 l_numof_bind_variables NUMBER;
1752 l_segment_name VARCHAR2(30);
1753 l_segment_value VARCHAR2(1000);
1754 l_data_type VARCHAR2(30);
1755
1756 BEGIN
1757
1758 IF g_log_enabled THEN
1759 l_log_module := C_DEFAULT_MODULE||'.get_flex_range_where';
1760 END IF;
1761 --
1762 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1763
1764 trace
1765 (p_msg => 'BEGIN of get_flex_range_where'
1766 ,p_level => C_LEVEL_PROCEDURE
1767 ,p_module => l_log_module);
1768
1769 END IF;
1770
1771 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1772
1773 trace
1774 (p_msg => 'p_coa_id = '||to_char(p_coa_id)
1775 ,p_level => C_LEVEL_STATEMENT
1776 ,p_module=> l_log_module );
1777
1778 trace
1779 (p_msg => 'p_account_from = '||to_char(p_account_from)
1780 ,p_level => C_LEVEL_STATEMENT
1781 ,p_module=> l_log_module );
1782
1783 trace
1784 (p_msg => 'p_account_to = '||to_char(p_account_to)
1785 ,p_level => C_LEVEL_STATEMENT
1786 ,p_module=> l_log_module );
1787
1788 END IF;
1789
1790 --
1791 -- e.g. l_where stores the following:
1792 -- CC.SEGMENT1 BETWEEN :FLEX_PARM1 AND :FLEX_PARM2
1793 -- AND CC.SEGMENT2 BETWEEN :FLEX_PARM3 AND :FLEX_PARM4 ...
1794 --
1795 fnd_flex_xml_publisher_apis.kff_where
1796 (p_lexical_name => 'FLEX_PARM'
1797 ,p_application_short_name => 'SQLGL'
1798 ,p_id_flex_code => 'GL#'
1799 ,p_id_flex_num => p_coa_id
1800 ,p_code_combination_table_alias => 'CC'
1801 ,p_segments => 'ALL'
1802 ,p_operator => 'BETWEEN'
1803 ,p_operand1 => p_account_from
1804 ,p_operand2 => p_account_to
1805 ,x_where_expression => l_where
1806 ,x_numof_bind_variables => l_numof_bind_variables
1807 ,x_bind_variables => l_bind_variables);
1808
1809 FOR i IN l_bind_variables.FIRST .. l_bind_variables.LAST LOOP
1810
1811 l_segment_name := l_bind_variables(i).name;
1812 l_data_type := l_bind_variables(i).data_type;
1813
1814 IF (l_data_type='VARCHAR2') THEN
1815
1816 l_segment_value := '''' || l_bind_variables(i).varchar2_value || '''';
1817
1818 ELSIF (l_data_type='NUMBER') THEN
1819
1820 l_segment_value := l_bind_variables(i).canonical_value;
1821
1822 ELSIF (l_data_type='DATE') THEN
1823
1824 l_segment_value := '''' || TO_CHAR(l_bind_variables(i).date_value
1825 ,'yyyy-mm-dd HH24:MI:SS') || '''';
1826
1827 END IF;
1828
1829 --
1830 -- Use REGEXP_REPLACE instead of REPLACE not to replace
1831 -- string 'SEGMENT1' in 'SEGMENT10'.
1832 -- REGEXP_REPLACE replaces the first occurent of a segment name
1833 -- e.g.
1834 -- BETWEEN :FLEX_PARM9 AND :FLEX_PARM10
1835 -- =>
1836 -- BETWEEN '000' AND '100'
1837 --
1838 l_where := REGEXP_REPLACE
1839 (l_where
1840 ,':' || l_segment_name
1841 ,l_segment_value
1842 ,1 -- Position
1843 ,1 -- The first occurence
1844 , 'c' -- Case sensitive
1845 );
1846
1847 END LOOP ;
1848
1849 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1850
1851 trace
1852 (p_msg => 'END of get_flex_range_where'
1853 ,p_level => C_LEVEL_PROCEDURE
1854 ,p_module => l_log_module);
1855
1856 END IF;
1857
1858 RETURN l_where;
1859
1860 EXCEPTION
1861 WHEN xla_exceptions_pkg.application_exception THEN
1862 RAISE;
1863 WHEN OTHERS THEN
1864 xla_exceptions_pkg.raise_message
1865 (p_location => 'xla_tb_report_pvt.get_flex_range_where');
1866
1867 END get_flex_range_where;
1868 /*======================================================================+
1869 | |
1870 | Private Function |
1871 | |
1872 | get_report_parameters |
1873 | |
1874 | |
1875 | Get dipalyed values of report paramters |
1876 | |
1877 +======================================================================*/
1878 PROCEDURE get_report_parameters
1879 (p_journal_source IN VARCHAR2
1880 ,p_definition_code IN VARCHAR2
1881 ,p_third_party_id IN VARCHAR2
1882 ,p_show_trx_detail_flag IN VARCHAR2
1883 ,p_incl_write_off_flag IN VARCHAR2
1884 ,p_acct_balance IN VARCHAR2)
1885
1886 IS
1887
1888 l_log_module VARCHAR2(240);
1889
1890 l_party_type_code VARCHAR2(1);
1891 l_party_id NUMBER(15);
1892
1893 BEGIN
1894
1895 IF g_log_enabled THEN
1896 l_log_module := C_DEFAULT_MODULE||'.get_report_parameters';
1897 END IF;
1898
1899 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1900
1901 trace
1902 (p_msg => 'BEGIN of get_report_parameters'
1903 ,p_level => C_LEVEL_PROCEDURE
1904 ,p_module => l_log_module);
1905
1906 END IF;
1907
1908 IF p_journal_source IS NOT NULL THEN
1909
1910 SELECT user_je_source_name
1911 INTO p_journal_source_dsp
1912 FROM gl_je_sources
1913 WHERE je_source_name = p_journal_source;
1914
1915 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1916
1917 trace
1918 (p_msg => 'p_journal_source_dsp = '|| p_journal_source_dsp
1919 ,p_level => C_LEVEL_STATEMENT
1920 ,p_module=> l_log_module );
1921
1922 END IF;
1923
1924 END IF;
1925
1926 IF p_definition_code IS NOT NULL THEN
1927
1928 SELECT NAME
1929 INTO p_report_definition_dsp
1930 FROM xla_tb_definitions_vl
1931 WHERE definition_code = p_definition_code;
1932
1933 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1934
1935 trace
1936 (p_msg => 'p_report_definition_dsp = '|| p_report_definition_dsp
1937 ,p_level => C_LEVEL_STATEMENT
1938 ,p_module=> l_log_module );
1939
1940 END IF;
1941
1942 END IF;
1943
1944 IF p_show_trx_detail_flag IS NOT NULL THEN
1945
1946 SELECT meaning
1947 INTO p_show_trx_detail_dsp
1948 FROM xla_lookups
1949 WHERE lookup_code = p_show_trx_detail_flag
1950 AND lookup_type = 'XLA_YES_NO';
1951
1952 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1953
1954 trace
1955 (p_msg => 'p_show_trx_detail_dsp = '|| p_show_trx_detail_dsp
1956 ,p_level => C_LEVEL_STATEMENT
1957 ,p_module=> l_log_module );
1958
1959 END IF;
1960
1961 END IF;
1962
1963 IF p_incl_write_off_flag IS NOT NULL THEN
1964
1965 SELECT meaning
1966 INTO p_incl_write_off_dsp
1967 FROM xla_lookups
1968 WHERE lookup_code = p_incl_write_off_flag
1969 AND lookup_type = 'XLA_YES_NO';
1970
1971 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1972
1973 trace
1974 (p_msg => 'p_incl_write_off_dsp = '|| p_incl_write_off_dsp
1975 ,p_level => C_LEVEL_STATEMENT
1976 ,p_module=> l_log_module );
1977
1978 END IF;
1979
1980 END IF;
1981
1982 IF p_third_party_id IS NOT NULL THEN
1983
1984 --
1985 -- Retrieve party id and party type code
1986 -- e.g. p_third_party_id = 1000#$C
1987 -- =>
1988 -- l_party_id = 100, l_party_type_code = C
1989 --
1990 l_party_id := TO_NUMBER(SUBSTRB(p_third_party_id
1991 ,1
1992 ,INSTRB(p_third_party_id,'#$') - 1
1993 )
1994 );
1995
1996 l_party_type_code := SUBSTRB(p_third_party_id
1997 ,INSTRB(p_third_party_id,'#$') + 2
1998 ,LENGTHB(p_third_party_id));
1999
2000 IF l_party_type_code = 'C' THEN
2001
2002 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2003
2004 trace
2005 (p_msg => 'Retrieving customer name for party id: '||l_party_id
2006 ,p_level => C_LEVEL_STATEMENT
2007 ,p_module=> l_log_module );
2008
2009 END IF;
2010
2011 SELECT hzp.party_name
2012 INTO p_third_party_name
2013 FROM hz_parties hzp
2014 ,hz_cust_accounts hca
2015 WHERE hzp.party_id = hca.party_id
2016 AND hca.cust_account_id = l_party_id;
2017
2018 ELSIF l_party_type_code = 'S' THEN
2019
2020 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2021
2022 trace
2023 (p_msg => 'Retrieving supplier name for party id: '||l_party_id
2024 ,p_level => C_LEVEL_STATEMENT
2025 ,p_module=> l_log_module );
2026
2027 END IF;
2028
2029 SELECT vendor_name
2030 INTO p_third_party_name
2031 FROM ap_suppliers
2032 WHERE vendor_id = l_party_id;
2033
2034 END IF;
2035
2036 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2037
2038 trace
2039 (p_msg => 'p_third_party_name = '|| p_third_party_name
2040 ,p_level => C_LEVEL_STATEMENT
2041 ,p_module=> l_log_module );
2042
2043 END IF;
2044
2045 END IF;
2046
2047 IF p_acct_balance IS NOT NULL THEN
2048
2049 SELECT meaning
2050 INTO p_acct_balance_dsp
2051 FROM xla_lookups
2052 WHERE lookup_type = 'XLA_TB_ACCT_BALANCE'
2053 AND lookup_code = p_acct_balance;
2054
2055 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2056
2057 trace
2058 (p_msg => 'p_acct_balance_dsp = '|| p_acct_balance_dsp
2059 ,p_level => C_LEVEL_STATEMENT
2060 ,p_module=> l_log_module );
2061
2062 END IF;
2063
2064 END IF;
2065
2066 SELECT meaning
2067 INTO P_REPORT_MODE_DSP
2068 FROM xla_lookups
2069 WHERE lookup_type = 'XLA_REPORT_LEVEL'
2070 AND lookup_code = NVL(P_REPORT , 'D');
2071
2072 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2073
2074 trace
2075 (p_msg => 'P_REPORT_MODE_DSP = '|| P_REPORT_MODE_DSP
2076 ,p_level => C_LEVEL_STATEMENT
2077 ,p_module=> l_log_module );
2078
2079 END IF;
2080
2081 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2082
2083 trace
2084 (p_msg => 'END of get_report_parameters'
2085 ,p_level => C_LEVEL_PROCEDURE
2086 ,p_module => l_log_module);
2087
2088 END IF;
2089
2090 EXCEPTION
2091 WHEN xla_exceptions_pkg.application_exception THEN
2092 RAISE;
2093 WHEN OTHERS THEN
2094 xla_exceptions_pkg.raise_message
2095 ('XLA' , 'XLA_COMMON_FAILURE'
2096 ,'LOCATION' , 'xla_tb_report_pvt.get_report_parameters'
2097 ,'ERROR' , sqlerrm);
2098 END get_report_parameters;
2099
2100
2101 /*======================================================================+
2102 | |
2103 | Provate Procedure |
2104 | |
2105 | Populate_Trail_Balance_Gt |
2106 | |
2107 | |
2108 | Populate trial balance to xla_trial_balances_GT. |
2109 | |
2110 +======================================================================*/
2111 PROCEDURE populate_trial_balance_gt
2112 (p_defn_info IN xla_tb_data_manager_pvt.r_definition_info
2113 ,p_ledger_info IN xla_tb_data_manager_pvt.r_ledger_info
2114 ,p_journal_source IN VARCHAR2
2115 ,p_start_date IN DATE
2116 ,p_as_of_date IN DATE
2117 ,p_third_party_id IN VARCHAR2 -- <Party ID> || '#$' || <Party Type>
2118 ,p_show_trx_detail_flag IN VARCHAR2
2119 ,p_incl_write_off_flag IN VARCHAR2
2120 ,p_acct_balance IN VARCHAR2
2121 ,p_security_info IN r_security_info)
2122
2123 IS
2124
2125
2126 l_log_module VARCHAR2(240);
2127
2128 l_party_id NUMBER;
2129 l_party_type_code VARCHAR2(30);
2130 l_party_column_cust VARCHAR2(32000);
2131 l_party_column_supp VARCHAR2(32000);
2132 l_ledger_column VARCHAR2(32000);
2133 l_parameter_where VARCHAR2(32000);
2134
2135 l_insert_gt_sql VARCHAR2(32000);
2136 l_insert_gt_cust_sql VARCHAR2(32000);
2137 l_insert_gt_supp_sql VARCHAR2(32000);
2138
2139 l_application_id NUMBER(15);
2140 l_select_string VARCHAR2(4000);
2141 l_from_string VARCHAR2(4000);
2142 l_where_string VARCHAR2(4000);
2143 l_event_class_code VARCHAR2(30);
2144
2145 l_flex_range_where VARCHAR2(32000);
2146 l_months_between NUMBER(15); --Added for bug 8409806
2147 l_security_join VARCHAR2(1000) ; -- added for bug:8773522
2148
2149 CURSOR c_ec IS
2150 SELECT event_class_code
2151 ,select_string
2152 ,from_string
2153 ,where_string
2154 FROM xla_tb_user_trans_views
2155 WHERE definition_code = p_definition_code;
2156
2157 BEGIN
2158
2159 IF g_log_enabled THEN
2160 l_log_module := C_DEFAULT_MODULE||'.populate_trial_balance_gt';
2161 END IF;
2162 --
2163 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2164
2165 trace
2166 (p_msg => 'BEGIN of populate_trial_balance_gt'
2167 ,p_level => C_LEVEL_PROCEDURE
2168 ,p_module => l_log_module);
2169
2170 END IF;
2171
2172 --
2173 -- Debug information
2174 --
2175 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2176
2177 trace
2178 (p_msg => 'ledger_id = ' || p_ledger_info.ledger_id
2179 ,p_level => C_LEVEL_STATEMENT
2180 ,p_module=> l_log_module );
2181
2182 trace
2183 (p_msg => 'ledger_name = ' || p_ledger_info.ledger_name
2184 ,p_level => C_LEVEL_STATEMENT
2185 ,p_module=> l_log_module );
2186
2187 trace
2188 (p_msg => 'ledger_short_name = ' || p_ledger_info.ledger_short_name
2189 ,p_level => C_LEVEL_STATEMENT
2190 ,p_module=> l_log_module );
2191
2192 trace
2193 (p_msg => 'ledger_category_code = '
2194 || p_ledger_info.ledger_category_code
2195 ,p_level => C_LEVEL_STATEMENT
2196 ,p_module=> l_log_module );
2197
2198 trace
2199 (p_msg => 'currency_code = ' || p_ledger_info.currency_code
2200 ,p_level => C_LEVEL_STATEMENT
2201 ,p_module=> l_log_module );
2202
2203 trace
2204 (p_msg => 'coa_id = ' || p_ledger_info.coa_id
2205 ,p_level => C_LEVEL_STATEMENT
2206 ,p_module=> l_log_module );
2207
2208 trace
2209 (p_msg => 'definition_code = ' || p_defn_info.definition_code
2210 ,p_level => C_LEVEL_STATEMENT
2211 ,p_module=> l_log_module );
2212
2213 trace
2214 (p_msg => 'ledger_id (defn) = ' || p_defn_info.ledger_id
2215 ,p_level => C_LEVEL_STATEMENT
2216 ,p_module=> l_log_module );
2217
2218 trace
2219 (p_msg => 'je_source_name = ' || p_defn_info.je_source_name
2220 ,p_level => C_LEVEL_STATEMENT
2221 ,p_module=> l_log_module );
2222
2223 trace
2224 (p_msg => 'enabled_flag = ' || p_defn_info.enabled_flag
2225 ,p_level => C_LEVEL_STATEMENT
2226 ,p_module=> l_log_module );
2227
2228 trace
2229 (p_msg => 'balance_side_code = ' || p_defn_info.balance_side_code
2230 ,p_level => C_LEVEL_STATEMENT
2231 ,p_module=> l_log_module );
2232
2233 trace
2234 (p_msg => 'defined_by_code = ' || p_defn_info.defined_by_code
2235 ,p_level => C_LEVEL_STATEMENT
2236 ,p_module=> l_log_module );
2237
2238 trace
2239 (p_msg => 'definition_status_code = '
2240 || p_defn_info.definition_status_code
2241 ,p_level => C_LEVEL_STATEMENT
2242 ,p_module=> l_log_module );
2243
2244 END IF;
2245
2246 --
2247 -- Retrieve user transaction identifiers
2248 --
2249 /* OPEN c_ec;
2250 LOOP
2251 FETCH c_ec
2252 INTO l_event_class_code
2253 ,l_select_string
2254 ,l_from_string
2255 ,l_where_string;
2256 EXIT WHEN c_ec%NOTFOUND;*/
2257
2258 --
2259 -- Assign template sql C_INSERT_GT_SELECT for Detail C_INSERT_GT_SUMMARY_SELECT for Summary Report
2260 -- to a local variable to replace strings
2261
2262 -- Changes for bug#8773522
2263 IF XLA_TB_AP_REPORT_PVT.P_REPORT = 'S'
2264 THEN
2265 l_insert_gt_sql := C_INSERT_GT_SUMMARY_STATEMENT || C_INSERT_GT_SUMMARY_SELECT;
2266 ELSE
2267 l_insert_gt_sql := C_INSERT_GT_STATEMENT || C_INSERT_GT_SELECT;
2268 END IF ;
2269
2270 /*
2271 commented as we are considering the + parallel(xtb) hint and allowing
2272 the optimizer to consider the correct path.
2273
2274 --Added for bug 8409806
2275
2276 select trunc(months_between(p_as_of_date, p_start_date))
2277 INTO l_months_between
2278 from dual;
2279
2280 If l_months_between < 12 then
2281
2282 l_insert_gt_sql:= REPLACE(l_insert_gt_sql,'$hint$','+ index(xtb XLA_TRIAL_BALANCES_N1) ');
2283
2284 else
2285
2286 l_insert_gt_sql:= REPLACE(l_insert_gt_sql,'$hint$','+ parallel(xtb) full(xtb)');
2287
2288 END If;
2289 --Added for bug 8409806
2290
2291 */
2292
2293
2294 IF p_account_from IS NOT NULL THEN
2295
2296 l_flex_range_where := get_flex_range_where
2297 (p_coa_id => p_coa_id
2298 ,p_account_from => p_account_from
2299 ,p_account_to => p_account_to);
2300
2301 l_insert_gt_sql := REPLACE (
2302 l_insert_gt_sql
2303 ,'$account_range$'
2304 ,' AND cc.code_combination_id = tb.code_combination_id AND '||
2305 l_flex_range_where);
2306
2307 l_insert_gt_sql := REPLACE(l_insert_gt_sql
2308 ,'$account_tab$'
2309 ,' ,gl_code_combinations cc ');
2310
2311 ELSE
2312
2313 l_insert_gt_sql := REPLACE(l_insert_gt_sql, '$account_range$', '');
2314
2315 l_insert_gt_sql := REPLACE(l_insert_gt_sql
2316 ,'$account_tab$'
2317 ,'');
2318
2319 END IF;
2320
2321
2322 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2323
2324 trace
2325 (p_msg => 'After replace ledger col, l_insert_gl_sql ----'
2326 ,p_level => C_LEVEL_PROCEDURE
2327 ,p_module => l_log_module);
2328
2329 END IF;
2330
2331 --
2332 -- Replace $amount_col$ based on balance side code
2333 -- For the inline view in C_INSERT_GT_SELECT
2334 --
2335 -- Added for bug 8321482,8228354
2336 -- Security Filter
2337 --
2338 -- Security Conditions are copied to l_security_join and
2339 -- appended at the end for DETAIL mode
2340 -- Replaced at the end for SUMMARY mode
2341 IF p_security_info.valuation_method IS NOT NULL THEN
2342
2343 l_security_join := l_security_join ||' AND xte.valuation_method = '''
2344 || p_security_info.valuation_method ||'''';
2345
2346 END IF;
2347
2348 IF p_security_info.security_id_int_1 IS NOT NULL THEN
2349
2350 l_security_join := l_security_join ||' AND xte.security_id_int_1 = '
2351 || p_security_info.security_id_int_1;
2352
2353 END IF;
2354
2355 IF p_security_info.security_id_char_1 IS NOT NULL THEN
2356
2357 l_security_join := l_security_join ||' AND xte.security_id_char_1 = '''
2358 || p_security_info.security_id_char_1 ||'''';
2359
2360 END IF;
2361
2362 -- newly added for bug:8773522
2363 IF XLA_TB_AP_REPORT_PVT.P_REPORT = 'S'
2364 THEN
2365 IF l_security_join IS NOT NULL THEN
2366 l_insert_gt_sql := REPLACE(l_insert_gt_sql
2367 ,'$security_valuation_join$'
2368 , l_security_join ) ;
2369 ELSE
2370 l_insert_gt_sql := REPLACE(l_insert_gt_sql
2371 ,'$security_valuation_join$'
2372 ,'') ;
2373 END IF;
2374 ELSE
2375 IF l_security_join IS NOT NULL THEN
2376 l_insert_gt_sql := l_insert_gt_sql || l_security_join ;
2377 END IF;
2378 END IF;
2379
2380 -- Added for bug 8321482,8228354
2381 --
2382 -- Retrieve party id and party type code
2383 -- e.g. p_third_party_id = 1000#$C
2384 -- =>
2385 -- l_party_id = 100, l_party_type_code = C
2386 --
2387 l_party_id := TO_NUMBER(SUBSTRB(p_third_party_id
2388 ,1
2389 ,INSTRB(p_third_party_id,'#$') - 1
2390 )
2391 );
2392
2393 l_party_type_code := SUBSTRB(p_third_party_id
2394 ,INSTRB(p_third_party_id,'#$') + 2
2395 ,LENGTHB(p_third_party_id));
2396
2397 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2398 trace
2399 (p_msg => 'l_party_id: ' || l_party_id
2400 ,p_level => C_LEVEL_PROCEDURE
2401 ,p_module => l_log_module);
2402
2403 trace
2404 (p_msg => 'l_party_type_code: ' || l_party_type_code
2405 ,p_level => C_LEVEL_PROCEDURE
2406 ,p_module => l_log_module);
2407
2408 trace
2409 (p_msg => 'l_insert_gl_sql: ' || substr(l_insert_gt_sql,1,3500)
2410 ,p_level => C_LEVEL_PROCEDURE
2411 ,p_module => l_log_module);
2412 trace
2413 (p_msg => 'l_insert_gl_sql: ' || substr(l_insert_gt_sql,3501,3500)
2414 ,p_level => C_LEVEL_PROCEDURE
2415 ,p_module => l_log_module);
2416 trace
2417 (p_msg => 'l_insert_gl_sql: ' || substr(l_insert_gt_sql,7001,3500)
2418 ,p_level => C_LEVEL_PROCEDURE
2419 ,p_module => l_log_module);
2420 END IF;
2421
2422
2423 EXECUTE IMMEDIATE l_insert_gt_sql
2424 USING p_definition_code
2425 ,trunc(p_start_date)
2426 ,trunc(p_as_of_date)
2427 ,l_party_id;
2428
2429
2430 print_logfile('# of rows inserted into GT table '
2431 || ' - ' || l_event_class_code
2432 || ' : ' || SQL%ROWCOUNT);
2433
2434 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2435
2436 trace
2437 (p_msg => '# of rows inserted: ' || SQL%ROWCOUNT
2438 ,p_level => C_LEVEL_STATEMENT
2439 ,p_module => l_log_module);
2440
2441 END IF;
2442
2443
2444 -- END LOOP;
2445 -- CLOSE c_ec;
2446
2447 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2448
2449 trace
2450 (p_msg => 'END of populate_trial_balance_gt'
2451 ,p_level => C_LEVEL_PROCEDURE
2452 ,p_module => l_log_module);
2453
2454 END IF;
2455
2456 EXCEPTION
2457 WHEN xla_exceptions_pkg.application_exception THEN
2458 RAISE;
2459 WHEN OTHERS THEN
2460 xla_exceptions_pkg.raise_message
2461 (p_location => 'xla_tb_report_pvt.populate_trial_balance_gt');
2462
2463 END populate_trial_balance_gt;
2464
2465
2466
2467
2468 /*======================================================================+
2469 | |
2470 | Private Function |
2471 | |
2472 | get_select_clause |
2473 | |
2474 | Returns the sql for the event class |
2475 | |
2476 +======================================================================*/
2477 FUNCTION get_upg_select_clause
2478 RETURN VARCHAR2 IS
2479
2480 l_upg_sql VARCHAR2(32000);
2481 l_init_balance_dsp xla_lookups.meaning%TYPE;
2482 l_ledger_cols VARCHAR2(1000);
2483 l_balancing_segment VARCHAR2(80);
2484 l_account_segment VARCHAR2(80);
2485 l_costcenter_segment VARCHAR2(80);
2486 l_management_segment VARCHAR2(80);
2487 l_intercompany_segment VARCHAR2(80);
2488
2489 l_alias_balancing_segment VARCHAR2(80);
2490 l_alias_account_segment VARCHAR2(80);
2491 l_alias_costcenter_segment VARCHAR2(80);
2492 l_alias_management_segment VARCHAR2(80);
2493 l_alias_intercompany_segment VARCHAR2(80);
2494
2495 l_fnd_flex_hint VARCHAR2(400);
2496 l_coa_id NUMBER;
2497
2498 l_seg_desc_column VARCHAR2(32000);
2499 l_seg_desc_from VARCHAR2(32000);
2500 l_seg_desc_join VARCHAR2(32000);
2501
2502 BEGIN
2503
2504 l_upg_sql := C_TB_UPG_SQL;
2505
2506 l_upg_sql := REPLACE(l_upg_sql,'$definition_code$',p_definition_code);
2507 --
2508 -- Replace $gl_balance_cols$
2509 --
2510 -- Call replace_gl_bal_string
2511 l_upg_sql := replace_gl_bal_string
2512 (p_select_sql => l_upg_sql
2513 ,p_ledger_id => g_defn_info.ledger_id
2514 ,p_account_balance_code => p_acct_balance -- Global Variable
2515 ,p_balance_side_code => g_defn_info.balance_side_code
2516 ,p_upg_flag => 'Y');
2517
2518 l_upg_sql := REPLACE(l_upg_sql
2519 ,'$p_start_date$'
2520 ,trunc(p_start_date));
2521
2522 l_upg_sql := REPLACE(l_upg_sql
2523 ,'$p_as_of_date$'
2524 ,trunc(p_as_of_date));
2525
2526 --
2527 -- Replace $ledger_col$.
2528 --
2529 -- Returns following strings:
2530 -- <ledger_id> ledger_id
2531 -- ,<ledger_name> ledger_name
2532 -- ,<ledger_short_name> ledger_short_name
2533 -- ,<ledger_currency_code> ledger_currency_code
2534 --
2535 l_ledger_cols :=
2536 g_ledger_info.ledger_id
2537 || ' ledger_id '|| C_NEW_LINE ||
2538 ' , ' || '''' || g_ledger_info.ledger_name || ''''
2539 || ' ledger_name '|| C_NEW_LINE ||
2540 ' ,' || '''' || g_ledger_info.ledger_short_name || ''''
2541 || ' ledger_short_name ' || C_NEW_LINE ||
2542 ' ,' || '''' || g_ledger_info.currency_code || ''''
2543 || ' ledger_currency_code ';
2544
2545 l_upg_sql := REPLACE (l_upg_sql
2546 ,'$ledger_cols$'
2547 ,l_ledger_cols);
2548 --
2549 -- Replace $initial_balance$ with 'Initial Balance'
2550 --
2551 SELECT meaning
2552 INTO l_init_balance_dsp
2553 FROM xla_lookups
2554 WHERE lookup_type = 'XLA_TB_TRX_TYPE';
2555
2556 l_upg_sql := REPLACE (l_upg_sql,'$initial_balance$',l_init_balance_dsp);
2557
2558 --
2559 -- Replace Amount Columns
2560 --
2561 IF g_defn_info.balance_side_code = 'C' THEN
2562
2563 l_upg_sql := REPLACE (l_upg_sql,'$amount_cols$',C_UPG_CR_AMOUNT_COLUMN);
2564
2565 ELSIF g_defn_info.balance_side_code = 'D' THEN
2566
2567 l_upg_sql := REPLACE (l_upg_sql,'$amount_cols$',C_UPG_DR_AMOUNT_COLUMN);
2568
2569 END IF;
2570
2571
2572 --
2573 -- Replace segment related columns
2574 --
2575
2576 ----------------------------------------------------------------------------
2577 -- get qualifier segments for the COA
2578 ----------------------------------------------------------------------------
2579 xla_report_utility_pkg.get_acct_qualifier_segs
2580 (p_coa_id => p_coa_id
2581 ,p_balance_segment => l_balancing_segment
2582 ,p_account_segment => l_account_segment
2583 ,p_cost_center_segment => l_costcenter_segment
2584 ,p_management_segment => l_management_segment
2585 ,p_intercompany_segment => l_intercompany_segment);
2586
2587 --
2588 -- attach table alias to the column names
2589 --
2590 IF l_balancing_segment = 'NULL' THEN
2591 l_alias_balancing_segment := 'NULL';
2592 ELSE
2593 l_alias_balancing_segment := 'gcck.'||l_balancing_segment;
2594 END IF;
2595
2596 IF l_account_segment = 'NULL' THEN
2597 l_alias_account_segment := 'NULL';
2598 ELSE
2599 l_alias_account_segment := 'gcck.'||l_account_segment;
2600 END IF;
2601
2602 IF l_costcenter_segment = 'NULL' THEN
2603 l_alias_costcenter_segment := 'NULL';
2604 ELSE
2605 l_alias_costcenter_segment := 'gcck.'||l_costcenter_segment;
2606 END IF;
2607
2608 IF l_management_segment = 'NULL' THEN
2609 l_alias_management_segment := 'NULL';
2610 ELSE
2611 l_alias_management_segment := 'gcck.'||l_management_segment;
2612 END IF;
2613
2614 IF l_intercompany_segment = 'NULL' THEN
2615 l_alias_intercompany_segment := 'NULL';
2616 ELSE
2617 l_alias_intercompany_segment := 'gcck.'||l_intercompany_segment;
2618 END IF;
2619
2620 --
2621 -- Replace segment related columns
2622 --
2623 xla_report_utility_pkg.get_segment_info
2624 (p_coa_id => p_coa_id
2625 ,p_balancing_segment => l_balancing_segment
2626 ,p_account_segment => l_account_segment
2627 ,p_costcenter_segment => l_costcenter_segment
2628 ,p_management_segment => l_management_segment
2629 ,p_intercompany_segment => l_intercompany_segment
2630 ,p_alias_balancing_segment => l_alias_balancing_segment
2631 ,p_alias_account_segment => l_alias_account_segment
2632 ,p_alias_costcenter_segment => l_alias_costcenter_segment
2633 ,p_alias_management_segment => l_alias_management_segment
2634 ,p_alias_intercompany_segment=> l_alias_intercompany_segment
2635 ,p_seg_desc_column => l_seg_desc_column
2636 ,p_seg_desc_from => l_seg_desc_from
2637 ,p_seg_desc_join => l_seg_desc_join
2638 ,p_hint => l_fnd_flex_hint
2639 );
2640
2641
2642 -- replace placeholders for the qualified segemnts
2643 --
2644 l_upg_sql := REPLACE(l_upg_sql, '$seg_desc_cols$', l_seg_desc_column);
2645 l_upg_sql := REPLACE(l_upg_sql, '$seg_desc_from$', l_seg_desc_from);
2646 l_upg_sql := REPLACE(l_upg_sql, '$seg_desc_join$', l_seg_desc_join);
2647
2648
2649 RETURN l_upg_sql;
2650
2651 END get_upg_select_clause;
2652
2653 /*======================================================================+
2654 | |
2655 | Private Function |
2656 | |
2657 | get_gcck_join |
2658 | |
2659 | Returns the gcck join condition for definition code created with |
2660 | segment ranges. This join condition is used to derive SLA Manuals and |
2661 | NON AP Amounts for definition codes created by seg ranges |
2662 +======================================================================*/
2663
2664 FUNCTION get_gcck_join
2665 (p_ledger_id IN NUMBER
2666 ) RETURN VARCHAR2 IS
2667
2668 l_coa_id gl_ledgers.chart_of_accounts_id%TYPE;
2669 l_join_gcck VARCHAR2(32000) := ' ';
2670 --added for bug#9926320
2671 C_STRING CONSTANT VARCHAR2(240) :=
2672 ' AND NVL(gcck.$segment$,''0'') BETWEEN NVL(NVL(xsr.$segment$_from, gcck.$segment$),''0'')
2673 AND NVL(NVL(xsr.$segment$_to, gcck.$segment$),''0'') ';
2674
2675 CURSOR csr_segments(x_coa_id IN NUMBER) IS
2676 SELECT application_column_name
2677 FROM fnd_id_flex_segments
2678 WHERE application_id = 101
2679 AND id_flex_code = 'GL#'
2680 AND id_flex_num = x_coa_id
2681 AND enabled_flag = 'Y';
2682
2683 BEGIN
2684
2685 SELECT chart_of_accounts_id
2686 INTO l_coa_id
2687 FROM gl_ledgers
2688 WHERE ledger_id = p_ledger_id;
2689
2690
2691 l_join_gcck := l_join_gcck || ' AND gcck.chart_of_accounts_id = ' || l_coa_id;
2692
2693 -- added for bug#9926320
2694 FOR c1 IN csr_segments(l_coa_id)
2695 LOOP
2696 l_join_gcck := l_join_gcck ||
2697 REPLACE(C_STRING,'$segment$',c1.application_column_name);
2698 END LOOP;
2699
2700 /* commented for bug#9926320
2701 FOR i IN ( SELECT *
2702 FROM xla_tb_defn_details d
2703 WHERE d.definition_code = p_definition_code
2704 )
2705 LOOP
2706 l_join_gcck := l_join_gcck || ' AND gcck.'|| i.flexfield_segment_code || ' BETWEEN ' ||
2707 '''' || i.segment_value_from || '''' || ' AND ' || '''' || i.segment_value_to || '''';
2708
2709 END LOOP;
2710 end of changes for bug#9926320 */
2711
2712 RETURN(l_join_gcck);
2713
2714 EXCEPTION
2715 WHEN xla_exceptions_pkg.application_exception THEN
2716 RAISE;
2717 WHEN OTHERS THEN
2718 xla_exceptions_pkg.raise_message
2719 (p_location => 'xla_tb_ap_report_pvt.get_gcck_join');
2720 END get_gcck_join;
2721
2722 /*======================================================================+
2723 | |
2724 | Private Function |
2725 | |
2726 | get_select_clause |
2727 | |
2728 | Returns the sql for the event class |
2729 | |
2730 +======================================================================*/
2731
2732 FUNCTION get_select_clause
2733 (p_defn_info IN xla_tb_data_manager_pvt.r_definition_info
2734 ,p_show_trx_detail_flag IN VARCHAR2
2735 ,p_incl_write_off_flag IN VARCHAR2
2736 ,p_account_balance_code IN VARCHAR2)
2737
2738 RETURN BOOLEAN IS
2739
2740 l_log_module VARCHAR2(240);
2741
2742 l_source_sql VARCHAR2(32000);
2743 l_app_source_sql VARCHAR2(32000);
2744 l_upg_sql VARCHAR2(32000);
2745 l_write_off VARCHAR2(80);
2746 l_balance VARCHAR2(400);
2747
2748 l_balancing_segment VARCHAR2(80);
2749 l_account_segment VARCHAR2(80);
2750 l_costcenter_segment VARCHAR2(80);
2751 l_management_segment VARCHAR2(80);
2752 l_intercompany_segment VARCHAR2(80);
2753
2754 l_alias_balancing_segment VARCHAR2(80);
2755 l_alias_account_segment VARCHAR2(80);
2756 l_alias_costcenter_segment VARCHAR2(80);
2757 l_alias_management_segment VARCHAR2(80);
2758 l_alias_intercompany_segment VARCHAR2(80);
2759
2760 l_fnd_flex_hint VARCHAR2(400);
2761 l_coa_id NUMBER;
2762 l_seg_desc_column VARCHAR2(32000);
2763 l_seg_desc_from VARCHAR2(32000);
2764 l_seg_desc_join VARCHAR2(32000);
2765
2766 --added TB phase 4 bug#7600550
2767 l_select_nonap_amount VARCHAR2(32000);
2768 l_select_manual_sla_amount VARCHAR2(32000);
2769
2770 --added TB phase 4 bug#7600550 bug#8291101
2771 l_select_nonap_segranges_amt VARCHAR2(32000);
2772 l_select_manual_segranges_amt VARCHAR2(32000);
2773
2774 TYPE t_ccid IS TABLE OF NUMBER INDEX BY BINARY_INTEGER ;
2775 TYPE t_ledgerid IS TABLE OF NUMBER INDEX BY BINARY_INTEGER ;
2776 TYPE t_non_ap_amount IS TABLE OF NUMBER INDEX BY BINARY_INTEGER ;
2777 TYPE t_manual_sla_amount IS TABLE OF NUMBER INDEX BY BINARY_INTEGER ;
2778
2779
2780 arr_ledgerid t_ledgerid;
2781 arr_ccid t_ccid;
2782
2783 arr_non_ap_amount t_non_ap_amount;
2784 arr_manual_sla_amount t_manual_sla_amount;
2785
2786 CURSOR csr_seg_range_check IS
2787 SELECT code_combination_id
2788 FROM xla_tb_defn_details
2789 WHERE definition_code = p_definition_code;
2790
2791 l_code_combination_id gl_code_combinations.code_combination_id%TYPE;
2792
2793 BEGIN
2794
2795 IF g_log_enabled THEN
2796 l_log_module := C_DEFAULT_MODULE||'.get_select_clause';
2797 END IF;
2798
2799 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2800
2801 trace
2802 (p_msg => 'BEGIN of get_select_clause'
2803 ,p_level => C_LEVEL_PROCEDURE
2804 ,p_module => l_log_module);
2805
2806 END IF;
2807
2808 IF XLA_TB_AP_REPORT_PVT.P_REPORT = 'S'
2809 THEN
2810 l_source_sql := C_TB_SUMMARY_SOURCE_SQL ;
2811 ELSE
2812 IF p_incl_write_off_flag = 'Y' THEN
2813 l_source_sql := C_TB_SOURCE_SQL||' UNION ALL '||C_TB_WRITE_OFF_SQL ;
2814
2815 SELECT meaning
2816 INTO l_write_off
2817 FROM xla_lookups
2818 WHERE lookup_type = 'XLA_ACCOUNTING_CLASS'
2819 AND lookup_code = 'WRITE_OFF';
2820
2821 l_source_sql := REPLACE(l_source_sql, '$write_off$', l_write_off);
2822 ELSE
2823 l_source_sql := C_TB_SOURCE_SQL ;
2824 END IF;
2825
2826 IF p_show_trx_detail_flag = 'Y' THEN
2827 l_app_source_sql := C_TB_APPLIED_SQL;
2828 ELSE
2829 l_app_source_sql := 'Select 1 from dual where 1=2';
2830 END IF;
2831
2832 END IF;
2833 --
2834 ----------------------------------------------------------------------------
2835 -- get qualifier segments for the COA
2836 ----------------------------------------------------------------------------
2837 xla_report_utility_pkg.get_acct_qualifier_segs
2838 (p_coa_id => p_coa_id
2839 ,p_balance_segment => l_balancing_segment
2840 ,p_account_segment => l_account_segment
2841 ,p_cost_center_segment => l_costcenter_segment
2842 ,p_management_segment => l_management_segment
2843 ,p_intercompany_segment => l_intercompany_segment);
2844
2845
2846 -- attach table alias to the column names
2847 --
2848 IF l_balancing_segment = 'NULL' THEN
2849 l_alias_balancing_segment := 'NULL';
2850 ELSE
2851 l_alias_balancing_segment := 'gcck.'||l_balancing_segment;
2852 END IF;
2853
2854 IF l_account_segment = 'NULL' THEN
2855 l_alias_account_segment := 'NULL';
2856 ELSE
2857 l_alias_account_segment := 'gcck.'||l_account_segment;
2858 END IF;
2859
2860 IF l_costcenter_segment = 'NULL' THEN
2861 l_alias_costcenter_segment := 'NULL';
2862 ELSE
2863 l_alias_costcenter_segment := 'gcck.'||l_costcenter_segment;
2864 END IF;
2865
2866 IF l_management_segment = 'NULL' THEN
2867 l_alias_management_segment := 'NULL';
2868 ELSE
2869 l_alias_management_segment := 'gcck.'||l_management_segment;
2870 END IF;
2871
2872 IF l_intercompany_segment = 'NULL' THEN
2873 l_alias_intercompany_segment := 'NULL';
2874 ELSE
2875 l_alias_intercompany_segment := 'gcck.'||l_intercompany_segment;
2876 END IF;
2877
2878 --
2879 -- Replace segment related columns
2880 --
2881 xla_report_utility_pkg.get_segment_info
2882 (p_coa_id => p_coa_id
2883 ,p_balancing_segment => l_balancing_segment
2884 ,p_account_segment => l_account_segment
2885 ,p_costcenter_segment => l_costcenter_segment
2886 ,p_management_segment => l_management_segment
2887 ,p_intercompany_segment => l_intercompany_segment
2888 ,p_alias_balancing_segment => l_alias_balancing_segment
2889 ,p_alias_account_segment => l_alias_account_segment
2890 ,p_alias_costcenter_segment => l_alias_costcenter_segment
2891 ,p_alias_management_segment => l_alias_management_segment
2892 ,p_alias_intercompany_segment=> l_alias_intercompany_segment
2893 ,p_seg_desc_column => l_seg_desc_column
2894 ,p_seg_desc_from => l_seg_desc_from
2895 ,p_seg_desc_join => l_seg_desc_join
2896 ,p_hint => l_fnd_flex_hint
2897 );
2898
2899
2900
2901 --
2902 -- replace placeholders for the qualified segemnts
2903 --
2904 l_source_sql := REPLACE(l_source_sql, '$seg_desc_cols$', l_seg_desc_column);
2905 l_source_sql := REPLACE(l_source_sql, '$seg_desc_from$', l_seg_desc_from);
2906 l_source_sql := REPLACE(l_source_sql, '$seg_desc_join$', l_seg_desc_join);
2907
2908 l_source_sql := replace_gl_bal_string
2909 (p_select_sql => l_source_sql
2910 ,p_ledger_id => p_defn_info.ledger_id
2911 ,p_account_balance_code => p_account_balance_code
2912 ,p_balance_side_code => p_defn_info.balance_side_code
2913 ,p_upg_flag => 'N');
2914
2915
2916 /*
2917 -- commented out for bug: 9133956 , AS in DATA MANAGER code to INSERT source_entity_id with -1
2918 -- has been commented 5635401
2919 IF p_defn_info.owner_code = C_OWNER_ORACLE THEN
2920
2921 l_upg_sql := get_upg_select_clause;
2922 l_source_sql := l_source_sql || ' UNION ALL ' ||
2923 l_upg_sql;
2924
2925 END IF;
2926 */ -- bug 9133956
2927
2928 IF XLA_TB_AP_REPORT_PVT.P_REPORT = 'S'
2929 THEN
2930 P_SUMMARY_SQL_STATEMENT := l_source_sql;
2931 ELSE
2932 p_sql_statement := l_source_sql;
2933 p_app_sql_statement := l_app_source_sql;
2934 END IF;
2935
2936
2937 IF nvl(P_INCLUDE_SLA_MANUALS_UNPOSTED,'N') = 'Y' THEN
2938
2939
2940 OPEN csr_seg_range_check;
2941 FETCH csr_seg_range_check INTO l_code_combination_id;
2942 CLOSE csr_seg_range_check;
2943
2944 IF l_code_combination_id IS NOT NULL
2945 THEN
2946
2947
2948 --added TB phase 4 bug#7600550
2949 l_select_nonap_amount := C_SELECT_NONAP_AMOUNT;
2950 l_select_nonap_amount := REPLACE(l_select_nonap_amount,'$p_definition_code$',p_definition_code);
2951
2952
2953 EXECUTE IMMEDIATE l_select_nonap_amount
2954 BULK COLLECT INTO arr_ledgerid, arr_ccid, arr_non_ap_amount
2955 USING trunc(p_start_date),
2956 trunc(p_as_of_date),
2957 trunc(p_start_date),
2958 trunc(p_as_of_date);
2959
2960 FORALL i IN 1..arr_ccid.COUNT
2961 UPDATE xla_trial_balances_gt
2962 SET NON_AP_AMOUNT = arr_non_ap_amount(i)
2963 WHERE code_combination_id = arr_ccid(i)
2964 AND ledger_id = arr_ledgerid(i);
2965
2966
2967 l_select_manual_sla_amount := C_SELECT_MANUAL_SLA_AMOUNT;
2968 l_select_manual_sla_amount := REPLACE(l_select_manual_sla_amount,'$p_definition_code$',p_definition_code);
2969
2970 -- changes for performance bug#9602525
2971 EXECUTE IMMEDIATE l_select_manual_sla_amount
2972 BULK COLLECT INTO arr_ledgerid, arr_ccid, arr_manual_sla_amount
2973 USING p_defn_info.ledger_id,
2974 trunc(NVL(p_start_date,to_date('01-01-1950','DD-MM-YYYY'))),
2975 trunc(NVL(p_as_of_date,to_date('31-12-9999','DD-MM-YYYY')));
2976
2977 FORALL i IN 1..arr_ccid.COUNT
2978 UPDATE xla_trial_balances_gt
2979 SET MANUAL_SLA_AMOUNT = arr_manual_sla_amount(i)
2980 WHERE code_combination_id = arr_ccid(i)
2981 AND ledger_id = arr_ledgerid(i);
2982
2983 --End TB phase 4 bug#7600550
2984
2985 ELSE --definition code created by seg ranges
2986
2987 l_select_nonap_segranges_amt := C_SELECT_NONAP_SEGRANGES_AMT;
2988 l_select_nonap_segranges_amt := REPLACE(l_select_nonap_segranges_amt,'$p_definition_code$',p_definition_code);
2989 l_select_nonap_segranges_amt := REPLACE(l_select_nonap_segranges_amt,'$gcck_join$',get_gcck_join(p_defn_info.ledger_id));
2990
2991 -- added for bug#9926320
2992 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2993 dump_text(p_text => l_select_nonap_segranges_amt );
2994 END IF;
2995
2996 EXECUTE IMMEDIATE l_select_nonap_segranges_amt
2997 BULK COLLECT INTO arr_ledgerid, arr_ccid, arr_non_ap_amount
2998 USING trunc(p_start_date),
2999 trunc(p_as_of_date);
3000
3001 FORALL i IN 1..arr_ccid.COUNT
3002 UPDATE xla_trial_balances_gt
3003 SET NON_AP_AMOUNT = arr_non_ap_amount(i)
3004 WHERE code_combination_id = arr_ccid(i)
3005 AND ledger_id = arr_ledgerid(i);
3006
3007 l_select_manual_segranges_amt := C_SELECT_MANUAL_SEGRANGES_AMT;
3008 l_select_manual_segranges_amt := REPLACE(l_select_manual_segranges_amt,'$p_definition_code$',p_definition_code);
3009 l_select_manual_segranges_amt := REPLACE(l_select_manual_segranges_amt, '$gcck_join$' , get_gcck_join(p_defn_info.ledger_id));
3010
3011 -- added for bug#9926320
3012 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3013 dump_text(p_text => l_select_manual_segranges_amt );
3014 END IF;
3015
3016 -- changes for performance bug#9602525
3017 EXECUTE IMMEDIATE l_select_manual_segranges_amt
3018 BULK COLLECT INTO arr_ledgerid, arr_ccid, arr_manual_sla_amount
3019 USING p_defn_info.ledger_id,
3020 trunc(NVL(p_start_date,to_date('01-01-1950','DD-MM-YYYY'))),
3021 trunc(NVL(p_as_of_date,to_date('31-12-9999','DD-MM-YYYY')));
3022
3023 FORALL i IN 1..arr_ccid.COUNT
3024 UPDATE xla_trial_balances_gt
3025 SET MANUAL_SLA_AMOUNT = arr_manual_sla_amount(i)
3026 WHERE code_combination_id = arr_ccid(i)
3027 AND ledger_id = arr_ledgerid(i);
3028
3029 END IF;
3030
3031 END IF;
3032
3033 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3034
3035 IF XLA_TB_AP_REPORT_PVT.P_REPORT = 'S'
3036 THEN
3037 dump_text(p_text => p_summary_sql_statement);
3038 ELSE
3039 dump_text(p_text => p_sql_statement);
3040 END IF;
3041
3042 END IF;
3043
3044 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3045
3046 trace
3047 (p_msg => 'END of get_select_clause'
3048 ,p_level => C_LEVEL_PROCEDURE
3049 ,p_module => l_log_module);
3050
3051 END IF;
3052
3053 RETURN TRUE;
3054
3055
3056 EXCEPTION
3057 WHEN xla_exceptions_pkg.application_exception THEN
3058 RAISE;
3059 WHEN OTHERS THEN
3060 xla_exceptions_pkg.raise_message
3061 (p_location => 'xla_tb_report_pvt.get_select_clause');
3062
3063 END get_select_clause;
3064
3065 /*======================================================================+
3066 | |
3067 | Public Function |
3068 | |
3069 | Before_report |
3070 | |
3071 | Code for before_report trigger |
3072 | |
3073 +======================================================================*/
3074 FUNCTION before_report RETURN BOOLEAN
3075
3076 IS
3077
3078 l_log_module VARCHAR2(240);
3079 l_return BOOLEAN;
3080 l_ledger_id NUMBER(15);
3081
3082 l_definition_code VARCHAR2(30);
3083 l_journal_source VARCHAR2(50);
3084 l_third_party_id VARCHAR2(80);
3085 l_show_trx_detail_flag VARCHAR2(1);
3086 l_incl_write_off_flag VARCHAR2(1);
3087 l_acct_balance VARCHAR2(80);
3088 l_start_date DATE;
3089 l_as_of_date DATE;
3090 l_security_info r_security_info;
3091
3092 l_application_id xla_subledgers.application_id%TYPE;
3093
3094 BEGIN
3095
3096 IF g_log_enabled THEN
3097 l_log_module := C_DEFAULT_MODULE||'.before_report';
3098 END IF;
3099
3100 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3101
3102 trace
3103 (p_msg => 'BEGIN of before_report'
3104 ,p_level => C_LEVEL_PROCEDURE
3105 ,p_module => l_log_module);
3106
3107 END IF;
3108
3109 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3110
3111 trace
3112 (p_msg => 'p_definition_code = ' || p_definition_code
3113 ,p_level => C_LEVEL_STATEMENT
3114 ,p_module=> l_log_module );
3115
3116 trace
3117 (p_msg => 'p_journal_source = ' || p_journal_source
3118 ,p_level => C_LEVEL_STATEMENT
3119 ,p_module=> l_log_module );
3120
3121 trace
3122 (p_msg => 'p_third_party_id = ' || p_third_party_id
3123 ,p_level => C_LEVEL_STATEMENT
3124 ,p_module=> l_log_module );
3125
3126 trace
3127 (p_msg => 'p_show_trx_detail_flag = ' || p_show_trx_detail_flag
3128 ,p_level => C_LEVEL_STATEMENT
3129 ,p_module=> l_log_module );
3130
3131 trace
3132 (p_msg => 'p_incl_write_off_flag = ' || p_incl_write_off_flag
3133 ,p_level => C_LEVEL_STATEMENT
3134 ,p_module=> l_log_module );
3135
3136 trace
3137 (p_msg => 'p_acct_balance = ' || p_acct_balance
3138 ,p_level => C_LEVEL_STATEMENT
3139 ,p_module=> l_log_module );
3140
3141 trace
3142 (p_msg => 'p_start_date = ' || fnd_date.date_to_canonical
3143 (dateval => p_start_date)
3144 ,p_level => C_LEVEL_STATEMENT
3145 ,p_module=> l_log_module );
3146
3147 trace
3148 (p_msg => 'p_as_of_date = ' || fnd_date.date_to_canonical
3149 (dateval => p_as_of_date)
3150 ,p_level => C_LEVEL_STATEMENT
3151 ,p_module=> l_log_module );
3152
3153 trace
3154 (p_msg => 'p_security_flag = ' || p_security_flag
3155 ,p_level => C_LEVEL_STATEMENT
3156 ,p_module=> l_log_module );
3157
3158 trace
3159 (p_msg => 'p_custom_param_1 = ' || p_custom_param_1
3160 ,p_level => C_LEVEL_STATEMENT
3161 ,p_module=> l_log_module );
3162
3163 trace
3164 (p_msg => 'p_custom_param_2 = ' || p_custom_param_2
3165 ,p_level => C_LEVEL_STATEMENT
3166 ,p_module=> l_log_module );
3167
3168 trace
3169 (p_msg => 'p_custom_param_3 = ' || p_custom_param_3
3170 ,p_level => C_LEVEL_STATEMENT
3171 ,p_module=> l_log_module );
3172
3173 trace
3174 (p_msg => 'p_valuation_method = ' || p_valuation_method
3175 ,p_level => C_LEVEL_STATEMENT
3176 ,p_module=> l_log_module );
3177
3178 trace
3179 (p_msg => 'p_security_id_int_1 = ' || p_security_id_int_1
3180 ,p_level => C_LEVEL_STATEMENT
3181 ,p_module=> l_log_module );
3182
3183 trace
3184 (p_msg => 'p_security_id_char_1 = ' || p_security_id_char_1
3185 ,p_level => C_LEVEL_STATEMENT
3186 ,p_module=> l_log_module );
3187
3188 END IF;
3189
3190 --
3191 -- 1. Retrieve displayed values for Concurrent Program Parameters
3192 --
3193
3194 l_definition_code := p_definition_code;
3195 l_journal_source := p_journal_source;
3196 l_third_party_id := p_third_party_id;
3197 l_show_trx_detail_flag := p_show_trx_detail_flag;
3198 l_incl_write_off_flag := p_incl_write_off_flag;
3199 l_acct_balance := p_acct_balance;
3200 l_start_date := nvl(p_start_date,to_date('01-01-1950','DD-MM-YYYY'));
3201 l_as_of_date := p_as_of_date;
3202 l_security_info.valuation_method := p_valuation_method;
3203 l_security_info.security_id_int_1 := p_security_id_int_1;
3204 l_security_info.security_id_char_1 := p_security_id_char_1;
3205
3206 P_INCLUDE_SLA_MANUALS_UNPOSTED := NVL(P_INCLUDE_SLA_MANUALS_UNPOSTED,'N');
3207 p_start_date := nvl(p_start_date,to_date('01-01-1950','DD-MM-YYYY'));
3208
3209 print_logfile('>> get_report_parameters');
3210
3211 get_report_parameters
3212 (p_journal_source => l_journal_source
3213 ,p_definition_code => l_definition_code
3214 ,p_third_party_id => l_third_party_id
3215 ,p_show_trx_detail_flag => l_show_trx_detail_flag
3216 ,p_incl_write_off_flag => l_incl_write_off_flag
3217 ,p_acct_balance => l_acct_balance);
3218
3219 print_logfile('<< get_report_parameters');
3220
3221 --
3222 -- 2. Set security context
3223 --
3224 IF NVL(p_security_flag,'N') = 'Y' THEN
3225
3226 --
3227 -- The flag is 'Y' only when security function
3228 -- is defined for a given journal source
3229 -- That is when the flag is 'Y', the journal source is not null.
3230 --
3231 SELECT application_id
3232 INTO l_application_id
3233 FROM xla_subledgers
3234 WHERE je_source_name = l_journal_source;
3235
3236 print_logfile('>> set_security_context');
3237
3238 xla_security_pkg.set_security_context(l_application_id);
3239
3240 print_logfile('<< set_security_context');
3241
3242 print_logfile('# of operating units initialized: '
3243 || mo_global.get_ou_count);
3244
3245 END IF;
3246 --
3247 -- 3. Retrieve details of Ledger and Report Definition
3248 --
3249 print_logfile('>> get_report_definition');
3250
3251 g_defn_info := xla_tb_data_manager_pvt.get_report_definition
3252 (p_definition_code => p_definition_code);
3253
3254 print_logfile('<< get_report_definition');
3255
3256 print_logfile('>> get_ledger_info');
3257
3258 g_ledger_info := xla_tb_data_manager_pvt.get_ledger_info
3259 (p_ledger_id => g_defn_info.ledger_id);
3260
3261 print_logfile('<< get_ledger_info');
3262
3263 --
3264 -- 4. Populate trial balance data into the GT table
3265 --
3266 print_logfile('>> populate_trial_balance_gt');
3267
3268 populate_trial_balance_gt
3269 (p_defn_info => g_defn_info
3270 ,p_ledger_info => g_ledger_info
3271 ,p_journal_source => l_journal_source
3272 ,p_start_date => l_start_date
3273 ,p_as_of_date => l_as_of_date
3274 ,p_third_party_id => l_third_party_id
3275 ,p_show_trx_detail_flag => l_show_trx_detail_flag
3276 ,p_incl_write_off_flag => l_incl_write_off_flag
3277 ,p_acct_balance => l_acct_balance
3278 ,p_security_info => l_security_info);
3279
3280 print_logfile('<< populate_trial_balance_gt');
3281
3282 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3283 trace
3284 (p_msg => 'passed populate_trial_balance_gt'
3285 ,p_level => C_LEVEL_STATEMENT
3286 ,p_module => l_log_module);
3287
3288 END IF;
3289
3290 --
3291 -- 5. Build select statement to retrieve trial balance data
3292 --
3293 print_logfile('>> get_select_clause');
3294
3295 l_return := get_select_clause
3296 (p_defn_info => g_defn_info
3297 ,p_show_trx_detail_flag => p_show_trx_detail_flag
3298 ,p_incl_write_off_flag => p_incl_write_off_flag
3299 ,p_account_balance_code => p_acct_balance);
3300
3301 print_logfile('<< get_select_clause');
3302
3303 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3304 trace
3305 (p_msg => 'END of before_report '
3306 ,p_level => C_LEVEL_PROCEDURE
3307 ,p_module => l_log_module);
3308 END IF;
3309
3310 RETURN l_return;
3311
3312 EXCEPTION
3313 WHEN xla_exceptions_pkg.application_exception THEN
3314 RAISE;
3315 WHEN OTHERS THEN
3316 xla_exceptions_pkg.raise_message
3317 (p_location => 'xla_tb_report_pvt.before_report');
3318
3319 END before_report;
3320
3321 --=============================================================================
3322 -- *********** Initialization routine **********
3323 --=============================================================================
3324
3325 --=============================================================================
3326 --
3327 --
3328 --
3329 --
3330 --
3331 --
3332 --
3333 --
3334 --
3335 --
3336 -- Following code is executed when the package body is referenced for the first
3337 -- time
3338 --
3339 --
3340 --
3341 --
3342 --
3343 --
3344 --
3345 --
3346 --
3347 --
3348 --
3349 --
3350 --=============================================================================
3351
3352 BEGIN
3353
3354 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3355 g_log_enabled := fnd_log.test
3356 (log_level => g_log_level
3357 ,MODULE => C_DEFAULT_MODULE);
3358
3359 IF NOT g_log_enabled THEN
3360 g_log_level := C_LEVEL_LOG_DISABLED;
3361 END IF;
3362
3363 END xla_tb_ap_report_pvt;