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