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