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