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