[Home] [Help]
PACKAGE BODY: APPS.XLA_JELINES_RPT_PKG
Source
1 PACKAGE BODY XLA_JELINES_RPT_PKG AS
2 -- $Header: xlarpjel.pkb 120.68.12020000.5 2013/02/18 17:36:33 sgullape ship $
3 /*===========================================================================+
4 | Copyright (c) 2003 Oracle Corporation BelmFont, California, USA |
5 | ALL rights reserved. |
6 +============================================================================+
7 | FILENAME |
8 | xlarpjel.pkb |
9 | |
10 | PACKAGE NAME |
11 | xla_jelines_rpt_pkg |
12 | |
13 | DESCRIPTION |
14 | Package body. This provides XML extract for Journal Entry Report |
15 | |
16 | HISTORY |
17 | 04/15/2005 V. Kumar Created |
18 | 04/27/2005 V. Kumar Bug:4309818 Added logic for entity_code = |
19 | MANUAL when include_user_trx_id_flag= 'Y'|
20 | 06/01/2005 V. Kumar Bug:4332077 Added constant C_TAX_DETAILS |
21 | to geting tax info. for JE Lines if |
22 | p_include_tax_details_flag = 'Y' |
23 | Bug:4391179 Added constant C_LEGAL_ENT_COL |
24 | ,C_LEGAL_ENT_FROM and C_LEGAL_ENT_JOIN to|
25 | get legal entity info if flag ='Y' |
26 | 06/30/2005 V. Kumar Bug:4311267 Modified get_parameter_sql to |
27 | get translated value of user parameters |
28 | 07/28/2005 V. Kumar Bug:4514905 Added join condition based on |
29 | application_id in C_JELINES_SLA_QUERY |
30 | 08/08/2005 V. Kumar Corrected filter condition for balance side|
31 | Bug:4529867 Changed the date format |
32 | 12/23/2005 V. Swapna Changed the package to use Data template |
33 | 12/30/2005 V. Kumar Modified code to select all event classes |
34 | for a transaction veiw. |
35 | 01/05/2005 V. Kumar Bug:4928256 Added missing column for Tax |
36 | info and Legal entity info |
37 | 01/19/2006 V. Swapna Bug 4755531. Modified the code to |
38 | calculate start and end dates for a period.|
39 | 01/20/2006 V. Swapna Bug 4725878. Added filter conditions to gl |
40 | and sla queries based on gl_batch_name. |
41 | 01/20/2006 S. Singhania Bug 4755531: Fixed SQLs |
42 | 01/30/2006 V. Swapna Bug 5000609: Add an outer join while |
43 | joining to party_type_code column in |
44 | joining to party_type_code column in |
45 | xla_ae_lines |
46 | 02/24/2006 V. Swapna Bug 5059634: Change a column name while |
47 | building parmaeter filter for gl |
48 | 03/31/2006 V. Swapna Bug 5097723: Correct a join condition of |
49 | gl_je_headers to fnd_sequences. Also,moved |
50 | statement populating p_party_details_col |
51 | from get_sla_query into beforeReport. |
52 | 04/03/2006 V. Swapna Bug 5122286: Correct the range paramters. |
53 | 04/23/2006 A. Wan 5072266 - replace po_vendors with |
54 | ap_suppliers |
55 | 04/26/2006 V. Kumar Bug 5127831: Modified constant C_TAX_QUERY |
56 | 06/06/2007 G.Praveen Bug 5895067: Added Code to fetch data from |
57 | reference_1 ,reference_4 from gl_je_lines |
58 | table and default_effective_date from |
59 | gl_je_batches table |
60 | 17-Apr-2008 rajose bug#6978940 changed the where clause for |
61 | p_include_zero_amount_flag from >0 to <> 0 |
62 | 16-Feb-2009 nksurana Instead of function calling new procedure |
63 | xla_report_utility_pkg.get_transaction_id |
64 | 12-Mar-2009 nksurana Added p_period_type to filter based on |
65 | whether period is Adjustment/Normal. |
66 | 11-Mar-2009 rajose bug#7834671 Journal Entries report showing |
67 | no data. |
68 | 20-Mar-2009 nksurana Added P_TRX_NUM_FROM,P_TRX_NUM_TO to filter|
69 | based on transaction number range.Also |
70 | modified filter on Post Acct. Program to |
71 | pick only the enabled Assignment. |
72 | 18-May-2009 VGOPISET 8250215: changed the value set of parameter|
73 | P_POSTING_STATUS_CODE to have values like |
74 | Posted, Not Posted, Transferred, Not Trans |
75 | ferred and show data accordingly |
76 | 10-Aug-2009 NKSURANA 8683445: Building new queries in the Report|
77 | based on the flag P_CUSTOM_QUERY_FLAG |
78 | 8638951: Added new column ORIG_LINE_NUMBER |
79 | in the extract to fetch the correct |
80 | AE_LINE_NUM for the TAX Query. |
81 | 08-Jun-2011 VGOPISET 12571692: Report to run when filtered by |
82 | Third Party Supplier Name. |
83 | 26-Jul-2011 NKSURANA 12663084: When report is filtered by Event |
84 | Class, also added filter on event_type_code|
85 | for better performance. |
86 +===========================================================================*/
87
88
89 --=============================================================================
90 -- **************** declarations ********************
91 --=============================================================================
92 -------------------------------------------------------------------------------
93 -- constant for getting flexfield segment value description
94 -------------------------------------------------------------------------------
95 C_SEG_DESC_JOIN CONSTANT VARCHAR2(1000) :=
96 ' AND $alias$.flex_value_set_id = $flex_value_set_id$ AND '||
97 ' $alias$.flex_value = $segment_column$ ';
98
99 -------------------------------------------------------------------------------
100 -- constant for getting leagal entity information
101 -------------------------------------------------------------------------------
102 C_LE_NULL_COL CONSTANT VARCHAR2(4000) :=
103 ' ,NULL LEGAL_ENTITY_ID
104 ,NULL LEGAL_ENTITY_NAME
105 ,NULL LEGAL_ENTITY_IDENTIFIER
106 ,NULL LE_ADDRESS_LINE_1
107 ,NULL LE_ADDRESS_LINE_2
108 ,NULL LE_ADDRESS_LINE_3
109 ,NULL LE_CITY
110 ,NULL LE_REGION_1
111 ,NULL LE_REGION_2
112 ,NULL LE_REGION_3
113 ,NULL LE_POSTAL_CODE
114 ,NULL LE_COUNTRY
115 ,NULL LE_REGISTRATION_NUMBER
116 ,NULL LE_REGISTRATION_EFFECTIVE_FROM
117 ,NULL LE_BR_DAILY_INSCRIPTION_NUMBER
118 ,NULL LE_BR_DAILY_INSCRIPTION_DATE
119 ,NULL LE_BR_DAILY_ENTITY
120 ,NULL LE_BR_DAILY_LOCATION
121 ,NULL LE_BR_DIRECTOR_NUMBER
122 ,NULL LE_BR_ACCOUNTANT_NUBMER
123 ,NULL LE_BR_ACCOUNTANT_NAME
124 ,NULL TRX_LEGAL_ENTITY_ID
125 ,NULL TRX_LEGAL_ENTITY_NAME
126 ,NULL TRX_LE_ADDRESS_LINE_1
127 ,NULL TRX_LE_ADDRESS_LINE_2
128 ,NULL TRX_LE_ADDRESS_LINE_3
129 ,NULL TRX_LE_CITY
130 ,NULL TRX_LE_REGION_1
131 ,NULL TRX_LE_REGION_2
132 ,NULL TRX_LE_REGION_3
133 ,NULL TRX_LE_POSTAL_CODE
134 ,NULL TRX_LE_COUNTRY
135 ,NULL TRX_LE_REGISTRATION_NUMBER
136 ,NULL TRX_LE_REGST_EFFECTIVE_FROM';
137
138 C_LEGAL_ENT_COL CONSTANT VARCHAR2(4000) :=
139 ' ,fiv.legal_entity_id LEGAL_ENTITY_ID
140 ,fiv.NAME LEGAL_ENTITY_NAME
141 ,fiv.LEGAL_ENTITY_IDENTIFIER LEGAL_ENTITY_IDENTIFIER
142 ,fiv.ADDRESS_LINE_1 LE_ADDRESS_LINE_1
143 ,fiv.ADDRESS_LINE_2 LE_ADDRESS_LINE_2
144 ,fiv.ADDRESS_LINE_3 LE_ADDRESS_LINE_3
145 ,fiv.TOWN_OR_CITY LE_CITY
146 ,fiv.REGION_1 LE_REGION_1
147 ,fiv.REGION_2 LE_REGION_2
148 ,fiv.REGION_3 LE_REGION_3
149 ,fiv.postal_code LE_POSTAL_CODE
150 ,fiv.country LE_COUNTRY
151 ,fiv.registration_number LE_REGISTRATION_NUMBER
152 ,fiv.effective_from LE_REGISTRATION_EFFECTIVE_FROM
153 ,xrv.registration_number LE_BR_DAILY_INSCRIPTION_NUMBER
154 ,to_char(xrv.effective_from
155 ,''YYYY-MM-DD'') LE_BR_DAILY_INSCRIPTION_DATE
156 ,xrv.legalauth_name LE_BR_DAILY_ENTITY
157 ,xlv.city LE_BR_DAILY_LOCATION
158 ,lc1.contact_number LE_BR_DIRECTOR_NUMBER
159 ,lc2.contact_number LE_BR_ACCOUNTANT_NUBMER
160 ,lc2.contact_name LE_BR_ACCOUNTANT_NAME
161 ,TABLE1.TRX_LEGAL_ENTITY_ID TRX_LEGAL_ENTITY_ID
162 ,TABLE1.TRX_LEGAL_ENTITY_NAME TRX_LEGAL_ENTITY_NAME
163 ,TABLE1.TRX_LE_ADDRESS_LINE_1 TRX_LE_ADDRESS_LINE_1
164 ,TABLE1.TRX_LE_ADDRESS_LINE_2 TRX_LE_ADDRESS_LINE_2
165 ,TABLE1.TRX_LE_ADDRESS_LINE_3 TRX_LE_ADDRESS_LINE_3
166 ,TABLE1.TRX_LE_CITY TRX_LE_CITY
167 ,TABLE1.TRX_LE_REGION_1 TRX_LE_REGION_1
168 ,TABLE1.TRX_LE_REGION_2 TRX_LE_REGION_2
169 ,TABLE1.TRX_LE_REGION_3 TRX_LE_REGION_3
170 ,TABLE1.TRX_LE_POSTAL_CODE TRX_LE_POSTAL_CODE
171 ,TABLE1.TRX_LE_COUNTRY TRX_LE_COUNTRY
172 ,TABLE1.TRX_LE_REGISTRATION_NUMBER TRX_LE_REGISTRATION_NUMBER
173 ,TABLE1.TRX_LE_REGST_EFFECTIVE_FROM TRX_LE_REGST_EFFECTIVE_FROM ';
174
175 C_LEGAL_ENT_FROM CONSTANT VARCHAR2(1000) :=
176 ' ,xle_firstparty_information_v fiv
177 ,xle_registrations_v xrv
178 ,xle_legalauth_v xlv
179 ,xle_legal_contacts_v lc1
180 ,xle_legal_contacts_v lc2
181 ,gl_ledger_le_bsv_specific_v gle';
182
183 C_LEGAL_ENT_JOIN CONSTANT VARCHAR2(2000) :=
184 ' AND gle.ledger_id(+) = TABLE1.ledger_id
185 AND gle.segment_value(+) = TABLE1.$leg_seg_val$
186 AND fiv.legal_entity_id(+) = gle.legal_entity_id
187 AND xrv.legal_entity_id(+) = fiv.legal_entity_id
188 AND xrv.legislative_category(+) = ''FEDERAL_TAX''
189 AND xlv.legalauth_id(+) = xrv.legalauth_id
190 AND lc1.entity_id(+) = fiv.legal_entity_id
191 AND lc1.ROLE(+) = ''DIRECTOR''
192 AND lc1.entity_type(+) = ''LEGAL_ENTITY''
193 AND lc2.entity_id(+) = fiv.legal_entity_id
194 AND lc2.ROLE(+) = ''ACCOUNTANT''
195 AND lc2.entity_type(+) = ''LEGAL_ENTITY'' ';
196
197 C_ESTBLISHMENT_COL CONSTANT VARCHAR2(4000) :=
198 ' ,xev.establishment_id LEGAL_ENTITY_ID
199 ,xev.establishment_name LEGAL_ENTITY_NAME
200 ,xev.address_line_1 LE_ADDRESS_LINE_1
201 ,xev.address_line_2 LE_ADDRESS_LINE_2
202 ,xev.address_line_3 LE_ADDRESS_LINE_3
203 ,xev.town_or_city LE_CITY
204 ,xev.region_1 LE_REGION_1
205 ,xev.region_2 LE_REGION_2
206 ,xev.region_3 LE_REGION_3
207 ,xev.postal_code LE_POSTAL_CODE
208 ,xev.country LE_COUNTRY
209 ,xev.registration_number LE_REGISTRATION_NUMBER
210 ,xev.effective_from LE_REGISTRATION_EFFECTIVE_FROM
211 ,xrv.registration_number LE_BR_DAILY_INSCRIPTION_NUMBER
212 ,to_char(xrv.effective_from
213 ,''YYYY-MM-DD'') LE_BR_DAILY_INSCRIPTION_DATE
214 ,xrv.legalauth_name LE_BR_DAILY_ENTITY
215 ,xlv.city LE_BR_DAILY_LOCATION
216 ,lc1.contact_number LE_BR_DIRECTOR_NUMBER
217 ,lc2.contact_number LE_BR_ACCOUNTANT_NUBMER
218 ,lc2.contact_name LE_BR_ACCOUNTANT_NAME
219 ,TABLE1.TRX_LEGAL_ENTITY_ID TRX_LEGAL_ENTITY_ID
220 ,TABLE1.TRX_LEGAL_ENTITY_NAME TRX_LEGAL_ENTITY_NAME
221 ,TABLE1.TRX_LE_ADDRESS_LINE_1 TRX_LE_ADDRESS_LINE_1
222 ,TABLE1.TRX_LE_ADDRESS_LINE_2 TRX_LE_ADDRESS_LINE_2
223 ,TABLE1.TRX_LE_ADDRESS_LINE_3 TRX_LE_ADDRESS_LINE_3
224 ,TABLE1.TRX_LE_CITY TRX_LE_CITY
225 ,TABLE1.TRX_LE_REGION_1 TRX_LE_REGION_1
226 ,TABLE1.TRX_LE_REGION_2 TRX_LE_REGION_2
227 ,TABLE1.TRX_LE_REGION_3 TRX_LE_REGION_3
228 ,TABLE1.TRX_LE_POSTAL_CODE TRX_LE_POSTAL_CODE
229 ,TABLE1.TRX_LE_COUNTRY TRX_LE_COUNTRY
230 ,TABLE1.TRX_LE_REGISTRATION_NUMBER TRX_LE_REGISTRATION_NUMBER
231 ,TABLE1.TRX_LE_REGST_EFFECTIVE_FROM TRX_LE_REGST_EFFECTIVE_FROM ';
232
233 C_ESTABLISHMENT_FROM CONSTANT VARCHAR2(2000) :=
234 ' ,gl_ledger_le_bsv_specific_v glv
235 ,xle_bsv_associations xba
236 ,xle_establishment_v xev
237 ,xle_registrations_v xrv
238 ,xle_legalauth_v xlv
239 ,xle_legal_contacts_v lc1
240 ,xle_legal_contacts_v lc2';
241
242 C_ESTABLISHMENT_JOIN CONSTANT VARCHAR2(2000) :=
243 ' AND glv.ledger_id(+) = TABLE1.ledger_id
244 AND glv.segment_value(+) = TABLE1.$leg_seg_val$
245 AND xba.legal_parent_id(+) = glv.legal_entity_id
246 AND xba.entity_name(+) = glv.segment_value
247 AND xba.context(+) = ''EST_BSV_MAPPING''
248 AND xev.establishment_id(+) = xba.legal_construct_id
249 AND xrv.establishment_id(+) = xev.establishment_id
250 AND xrv.legislative_category(+) = ''FEDERAL_TAX''
251 AND xlv.legalauth_id(+) = xrv.legalauth_id
252 AND lc1.entity_id(+) = xev.establishment_id
253 AND lc1.entity_type(+) = ''ESTABLISHMENT''
254 AND lc1.ROLE(+) = ''DIRECTOR''
255 AND lc2.entity_id(+) = xev.establishment_id
256 AND lc2.ROLE(+) = ''ACCOUNTANT''
257 AND lc2.entity_type(+) = ''ESTABLISHMENT'' ';
258
259 C_SLA_TRX_LEGAL_ENT_COL CONSTANT VARCHAR2(4000) :=
260 ' ,ftx.legal_entity_id TRX_LEGAL_ENTITY_ID
261 ,ftx.NAME TRX_LEGAL_ENTITY_NAME
262 ,ftx.ADDRESS_LINE_1 TRX_LE_ADDRESS_LINE_1
263 ,ftx.ADDRESS_LINE_2 TRX_LE_ADDRESS_LINE_2
264 ,ftx.ADDRESS_LINE_3 TRX_LE_ADDRESS_LINE_3
265 ,ftx.TOWN_OR_CITY TRX_LE_CITY
266 ,ftx.REGION_1 TRX_LE_REGION_1
267 ,ftx.REGION_2 TRX_LE_REGION_2
268 ,ftx.REGION_3 TRX_LE_REGION_3
269 ,ftx.postal_code TRX_LE_POSTAL_CODE
270 ,ftx.country TRX_LE_COUNTRY
271 ,ftx.registration_number TRX_LE_REGISTRATION_NUMBER
272 ,ftx.effective_from TRX_LE_REGST_EFFECTIVE_FROM';
273
274 C_SLA_TRX_LEGAL_ENT_FROM CONSTANT VARCHAR2(1000) :=
275 ' ,xle_firstparty_information_v ftx ';
276
277 C_SLA_TRX_LEGAL_ENT_JOIN CONSTANT VARCHAR2(2000) :=
278 ' AND ftx.legal_entity_id(+) = ent.legal_entity_id ';
279
280 C_GL_TRX_LEGAL_ENT_COL CONSTANT VARCHAR2(4000) :=
281 ' ,NULL TRX_LEGAL_ENTITY_ID
282 ,NULL TRX_LEGAL_ENTITY_NAME
283 ,NULL TRX_LE_ADDRESS_LINE_1
284 ,NULL TRX_LE_ADDRESS_LINE_2
285 ,NULL TRX_LE_ADDRESS_LINE_3
289 ,NULL TRX_LE_REGION_3
286 ,NULL TRX_LE_CITY
287 ,NULL TRX_LE_REGION_1
288 ,NULL TRX_LE_REGION_2
290 ,NULL TRX_LE_POSTAL_CODE
291 ,NULL TRX_LE_COUNTRY
292 ,NULL TRX_LE_REGISTRATION_NUMBER
293 ,NULL TRX_LE_REGST_EFFECTIVE_FROM ';
294
295 C_QUALIFIED_SEGMENT CONSTANT VARCHAR2(1000) :=
296 ',$alias_balancing_segment$ BALANCING_SEGMENT
297 ,$alias_account_segment$ NATURAL_ACCOUNT_SEGMENT
298 ,$alias_costcenter_segment$ COST_CENTER_SEGMENT
299 ,$alias_management_segment$ MANAGEMENT_SEGMENT
300 ,$alias_intercompany_segment$ INTERCOMPANY_SEGMENT
301 $seg_desc_column$ ';
302
303
304 ------------------------------------------------------------------------------
305 -- Based on P_YTD_CARRIEDFWD_FLAG building query to fetch Year-to-Date Carried
306 -- Forward Debit and Credit Amounts
307 ------------------------------------------------------------------------------
308
309 C_YTD_ACTUAL_CARRIEDFWD VARCHAR2(8000) :=
310 'SELECT
311 nvl(sum(nvl(gll.accounted_dr,0)),0) YTD_ACTIVITY_DR
312 ,nvl(sum(nvl(gll.accounted_cr,0)),0) YTD_ACTIVITY_CR
313 FROM
314 gl_je_headers glh
315 ,gl_je_lines gll
316 ,gl_ledgers glg
317 ,gl_periods glp
318 ,gl_code_combinations_kfv gcck
319 WHERE glg.ledger_id IN (:P_LEDGER_ID)
320 AND glh.period_name IN ( select distinct Period_name from gl_period_statuses where
321 ledger_id = :P_LEDGER_ID and period_num <
322 (select distinct Period_num from gl_period_statuses
323 where ledger_id = :P_LEDGER_ID and period_name = :P_PERIOD_FROM)
324 and period_year in
325 (select distinct Period_year from gl_period_statuses
326 where ledger_id = :P_LEDGER_ID and period_name = :P_PERIOD_FROM)
327 )
328 AND glh.ledger_id = glg.ledger_id
329 AND gll.je_header_id = glh.je_header_id
330 AND glp.period_name = glh.period_name
331 AND glp.period_set_name = glg.period_set_name
332 AND gcck.code_combination_id = gll.code_combination_id
333 AND glh.status = ''P''
334 AND glh.currency_code <> ''STAT'''; --added gcck for bug 12586551
335
336
337 C_YTD_ZERO_CARRIEDFWD VARCHAR2(500) :=
338 'SELECT
339 0 YTD_ACTIVITY_DR,
340 0 YTD_ACTIVITY_CR
341 FROM
342 DUAL';
343
344
345 --------------------------------------------------------------------------------
346 -- constant for tax details
350 zxl.tax_line_id TAX_LINE_ID
347 --------------------------------------------------------------------------------
348 C_TAX_QUERY VARCHAR2(8000) :=
349 'SELECT /*+ index(xdl, XLA_DISTRIBUTION_LINKS_N3) */
351 ,zxr.tax_regime_name TAX_REGIME
352 ,zxl.tax TAX
353 ,ztt.tax_full_name TAX_NAME
354 ,zst.tax_status_name TAX_STATUS_NAME
355 ,zrt.tax_rate_name TAX_RATE_NAME
356 ,zxl.tax_rate TAX_RATE
357 ,flk1.meaning TAX_RATE_TYPE_NAME
358 ,to_char(zxl.tax_determine_date
359 ,''YYYY-MM-DD'') TAX_DETERMINE_DATE
360 ,to_char(zxl.tax_point_date
361 ,''YYYY-MM-DD'') TAX_POINT_DATE
362 ,zxl.tax_type_code TAX_TYPE_CODE
363 ,flk2.meaning TAX_TYPE_NAME
364 ,zxl.tax_code TAX_CODE
365 ,zxl.tax_registration_number TAX_REGISTRATION_NUMBER
366 ,zxl.trx_currency_code TRX_CURRENCY_CODE
367 ,zxl.tax_currency_code TAX_CURRENCY_CODE
368 ,zxl.tax_amt TAX_AMOUNT
369 ,zxl.tax_amt_tax_curr TAX_AMOUNT_TAX_CURRENCY
370 ,zxl.tax_amt_funcl_curr TAX_AMOUNT_FUNCTIONAL_CURR
371 ,zxl.taxable_amt TAXABLE_AMOUNT
372 ,zxl.taxable_amt_tax_curr TAXABLE_AMOUNT_TAX_CURRENCY
373 ,zxl.taxable_amt_funcl_curr TAXABLE_AMT_FUNC_CURRENCY
374 ,zxl.unrounded_taxable_amt UNROUNDED_TAXABLE_AMOUNT
378 ,zxl.rec_tax_amt_funcl_curr RECOVERABLE_TAX_AMT_FUNC_CURR
375 ,zxl.unrounded_tax_amt UNROUNDED_TAX_AMOUNT
376 ,zxl.rec_tax_amt RECOVERABLE_TAX_AMOUNT
377 ,zxl.rec_tax_amt_tax_curr RECOVERABLE_TAX_AMT_TAX_CURR
379 ,zxl.nrec_tax_amt NON_RECOVERABLE_TAX_AMOUNT
380 ,zxl.nrec_tax_amt_tax_curr NON_REC_TAX_AMT_TAX_CURR
381 ,zxl.nrec_tax_amt_funcl_curr NON_REC_TAX_AMT_FUNC_CURR
382 ,zxl.tax_jurisdiction_code TAX_JURISDICTION_CODE
383 ,zxl.self_assessed_flag SELF_ASSESSED_FLAG
384 ,zxl.hq_estb_reg_number HQ_ESTB_REG_NUMBER
385 ,zrnd.rec_nrec_tax_dist_id REC_NREC_TAX_DIST_ID
386 ,zrnd.recovery_type_code RECOVERY_TYPE_CODE
387 ,zrnd.recovery_rate_code RECOVERY_RATE_CODE
388 ,zrnd.rec_nrec_rate REC_NREC_RATE
389 ,zrnd.recoverable_flag RECOVERABLE_FLAG
390 ,zrnd.rec_nrec_tax_amt REC_NREC_TAX_AMT
391 ,zrnd.rec_nrec_tax_amt_tax_curr REC_NREC_TAX_AMT_TAX_CURR
392 ,zrnd.rec_nrec_tax_amt_funcl_curr REC_NREC_TAX_AMT_FUNCL_CURR
393
394 FROM xla_distribution_links xdl
395 ,zx_lines zxl
396 ,zx_regimes_tl zxr
397 ,zx_taxes_tl ztt
398 ,zx_status_tl zst
399 ,zx_rates_tl zrt
400 ,fnd_lookups flk1
401 ,fnd_lookups flk2
402 ,zx_rec_nrec_dist zrnd
403 WHERE xdl.tax_line_ref_id = zxl.tax_line_id
404 AND zxr.tax_regime_id(+) = zxl.tax_regime_id
405 AND zxr.language(+) = USERENV(''LANG'')
406 AND ztt.tax_id(+) = zxl.tax_id
407 AND ztt.language(+) = USERENV(''LANG'')
408 AND zst.tax_status_id(+) = zxl.tax_status_id
409 AND zst.language(+) = USERENV(''LANG'')
410 AND zrt.tax_rate_id(+) = zxl.tax_rate_id
411 AND zrt.language(+) = USERENV(''LANG'')
412 AND flk1.lookup_type = ''ZX_RATE_TYPE''
413 AND flk1.lookup_code = zxl.tax_rate_type
414 AND flk2.lookup_type(+) = ''ZX_TAX_TYPE_CATEGORY''
415 AND flk2.lookup_code(+) = zxl.tax_type_code
416 AND xdl.application_id = :APPLICATION_ID
417 AND xdl.ae_header_id = :HEADER_ID
418 AND xdl.ae_line_num = :ORIG_LINE_NUMBER
419 AND xdl.tax_rec_nrec_dist_ref_id = zrnd.rec_nrec_tax_dist_id(+)';
420
421 C_TAX_NULL_QUERY VARCHAR2(8000) :=
422 'SELECT NULL TAX_LINE_ID
423 ,NULL TAX_REGIME
424 ,NULL TAX
425 ,NULL TAX_NAME
426 ,NULL TAX_STATUS_NAME
427 ,NULL TAX_RATE_NAME
428 ,NULL TAX_RATE
429 ,NULL TAX_RATE_TYPE_NAME
430 ,NULL TAX_DETERMINE_DATE
431 ,NULL TAX_POINT_DATE
432 ,NULL TAX_TYPE_CODE
433 ,NULL TAX_TYPE_NAME
434 ,NULL TAX_CODE
435 ,NULL TAX_REGISTRATION_NUMBER
436 ,NULL TRX_CURRENCY_CODE
437 ,NULL TAX_CURRENCY_CODE
438 ,NULL TAX_AMOUNT
439 ,NULL TAX_AMOUNT_TAX_CURRENCY
440 ,NULL TAX_AMOUNT_FUNCTIONAL_CURR
441 ,NULL TAXABLE_AMOUNT
442 ,NULL TAXABLE_AMOUNT_TAX_CURRENCY
443 ,NULL TAXABLE_AMT_FUNC_CURRENCY
444 ,NULL UNROUNDED_TAXABLE_AMOUNT
445 ,NULL UNROUNDED_TAX_AMOUNT
446 ,NULL RECOVERABLE_TAX_AMOUNT
447 ,NULL RECOVERABLE_TAX_AMT_TAX_CURR
448 ,NULL RECOVERABLE_TAX_AMT_FUNC_CURR
449 ,NULL NON_RECOVERABLE_TAX_AMOUNT
450 ,NULL NON_REC_TAX_AMT_TAX_CURR
451 ,NULL NON_REC_TAX_AMT_FUNC_CURR
452 ,NULL TAX_JURISDICTION_CODE
453 ,NULL SELF_ASSESSED_FLAG
454 ,NULL HQ_ESTB_REG_NUMBER
455 ,NULL REC_NREC_TAX_DIST_ID
456 ,NULL RECOVERY_TYPE_CODE
457 ,NULL RECOVERY_RATE_CODE
458 ,NULL REC_NREC_RATE
459 ,NULL RECOVERABLE_FLAG
460 ,NULL REC_NREC_TAX_AMT
461 ,NULL REC_NREC_TAX_AMT_TAX_CURR
462 ,NULL REC_NREC_TAX_AMT_FUNCL_CURR
463 FROM DUAL
464 WHERE 1>2';
465
466
467 --------------------------------------------------------------------------------
468 -- constant for created_by details
469 --------------------------------------------------------------------------------
470 C_CREATED_QUERY VARCHAR2(8000) :=
471 'select last_name||first_name LEGAL_CREATED_BY
472 from hr_employees
473 where employee_id =
474 (
475 select employee_id
476 from fnd_user
477 where user_id = :LEGAL_CREATED_ID
478 )';
479
480 C_CREATED_NULL_QUERY VARCHAR2(8000) :=
481 'select NULL LEGAL_CREATED_BY from dual where 1>2';
482
483 --------------------------------------------------------------------------------
484 -- constant for posted_by details
485 --------------------------------------------------------------------------------
486 C_POSTED_QUERY VARCHAR2(8000) :=
487 'select last_name||first_name LEGAL_POSTED_BY
488 from hr_employees
489 where employee_id =
490 (
491 select employee_id
492 from fnd_user
493 where user_id = :LEGAL_POSTED_ID
494 )';
495
499
496 C_POSTED_NULL_QUERY VARCHAR2(8000) :=
497 'select NULL LEGAL_POSTED_BY from dual where 1>2';
498
500
501 --------------------------------------------------------------------------------
502 -- constant for approval details
503 --------------------------------------------------------------------------------
504 C_APPROVED_QUERY VARCHAR2(8000) :=
505 'select last_name||first_name LEGAL_APPROVED_BY
506 from hr_employees
507 where employee_id =
508 (
509 select employee_id
510 from fnd_user
511 where user_name =
512 (
513 select d.TEXT_VALUE
514 from wf_items t
515 ,wf_item_attribute_values d
516 where d.item_key = t.item_key
517 and d.name = ''APPROVER_NAME''
518 and t.user_key = :GL_BATCH_NAME
519 AND d.item_type=''GLBATCH''
520 and t.begin_date in (select max(it.begin_date)
521 from wf_items it
522 ,wf_item_attribute_values t1
523 ,wf_item_attribute_values t
524 where it.user_key = :GL_BATCH_NAME
525 and it.item_key = t.item_key
526 and t1.item_type = ''GLBATCH''
527 and t1.item_key = t.item_key
528 and t.ITEM_TYPE = ''GLBATCH''
529 AND t.NAME = ''BATCH_NAME''
530 and t.text_value = :GL_BATCH_NAME
531 and t1.name = ''PERIOD_NAME''
532 and t1.text_value = :PERIOD_NAME)
533 )
534 )';
535
536 C_APPROVED_NULL_QUERY VARCHAR2(8000) :=
537 'select NULL LEGAL_APPROVED_BY from dual where 1>2';
538
539 --------------------------------------------------------------------------------
540 -- constant for COMMERCIAL_NUMBER details
541 --------------------------------------------------------------------------------
542 C_COMMERCIAL_QUERY VARCHAR2(8000) :=
543 'SELECT xler.registration_number LEGAL_COMMERCIAL_NUMBER
544 FROM XLE_REGISTRATIONS_V xler
545 WHERE legislative_category = ''COMMERCIAL_LAW''
546 AND legal_entity_id = :P_LEGAL_ENTITY_ID';
547
548 C_COMMERCIAL_NULL_QUERY VARCHAR2(8000) :=
549 'select NULL LEGAL_COMMERCIAL_NUMBER from dual where 1>2';
550
551
552 --------------------------------------------------------------------------------
553 -- constant for VAT_REGISTRATION details
554 --------------------------------------------------------------------------------
555 C_VAT_REGISTRATION_QUERY VARCHAR2(8000) :=
556 'SELECT zptp.REP_REGISTRATION_NUMBER LEGAL_VAT_REGISTRATION_NUMBER
557 FROM ZX_PARTY_TAX_PROFILE zptp ,XLE_ETB_PROFILES xetbp
558 WHERE zptp.PARTY_TYPE_CODE = ''LEGAL_ESTABLISHMENT''
559 AND xetbp.party_id=zptp.party_id
560 AND xetbp.MAIN_ESTABLISHMENT_FLAG = ''Y''
561 AND xetbp.LEGAL_ENTITY_ID = :P_LEGAL_ENTITY_ID' ;
562
563 C_VAT_REGISTRATION_NULL_QUERY VARCHAR2(8000) :=
564 'select NULL LEGAL_VAT_REGISTRATION_NUMBER from dual where 1>2';
565
566
567
568 g_period_year_start_date VARCHAR2(30);
569 g_period_year_end_date VARCHAR2(30);
570 g_je_source_application_id VARCHAR2(30);
571 g_flex_range_where VARCHAR2(4000); --bug 12586551
572 g_start_period_num NUMBER; --bug 12609107
573 g_end_period_num NUMBER; --bug 12609107
574
575
576 --=============================================================================
577 -- ************** forward declaraions ******************
578 --=============================================================================
579 FUNCTION get_flex_range_where(p_coa_id IN NUMBER
580 ,p_acct_flexfield_from IN VARCHAR2
581 ,p_acct_flexfield_to IN VARCHAR2) RETURN VARCHAR;
582
583 PROCEDURE get_sla_query;
584
585 PROCEDURE get_gl_query(p_accounts_range_specified in varchar2);
586
587 --=============================================================================
588 -- *********** Local Trace Routine **********
589 --=============================================================================
590 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
591 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
592 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
593 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
594 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
595 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
596
597 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
598 C_DEFAULT_MODULE CONSTANT VARCHAR2(240):= 'xla.plsql.xla_jelines_rpt_pkg';
599
600 g_log_level NUMBER;
601 g_log_enabled BOOLEAN;
602
603 PROCEDURE trace
604 (p_msg IN VARCHAR2
605 ,p_level IN NUMBER
606 ,p_module IN VARCHAR2) IS
607 BEGIN
608 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
609 fnd_log.message(p_level, NVL(p_module,C_DEFAULT_MODULE));
610 ELSIF p_level >= g_log_level THEN
611 fnd_log.string(p_level, NVL(p_module,C_DEFAULT_MODULE), p_msg);
612 END IF;
613
614 EXCEPTION
615 WHEN xla_exceptions_pkg.application_exception THEN
616 RAISE;
617 WHEN OTHERS THEN
618 xla_exceptions_pkg.raise_message
619 (p_location => 'xla_jelines_rpt_pkg.trace');
620 END trace;
621
622 /*======================================================================+
623 | |
624 | PRIVATE FUNCTION |
630 | get_gl_query |
625 | |
626 | get_flex_range_where :Return WHERE clauses FOR flexfield ranges |
627 | |
628 | PRIVATE Procedures |
629 | get_sla_query |
631 | |
632 | |
633 +======================================================================*/
634
635 FUNCTION get_flex_range_where
636 (p_coa_id IN NUMBER
637 ,p_acct_flexfield_from IN VARCHAR2
638 ,p_acct_flexfield_to IN VARCHAR2) RETURN VARCHAR
639
640 IS
641
642 l_log_module VARCHAR2(240);
643 l_where VARCHAR2(32000);
644 l_bind_variables fnd_flex_xml_publisher_apis.bind_variables;
645 l_numof_bind_variables NUMBER;
646 l_segment_name VARCHAR2(30);
647 l_segment_value VARCHAR2(1000);
648 l_data_type VARCHAR2(30);
649
650 BEGIN
651
652 IF g_log_enabled THEN
653 l_log_module := C_DEFAULT_MODULE||'.get_flex_range_where';
654 END IF;
655 --
656 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
657 trace
658 (p_msg => 'BEGIN of get_flex_range_where'
659 ,p_level => C_LEVEL_PROCEDURE
660 ,p_module => l_log_module);
661 END IF;
662
663 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
664 trace
665 (p_msg => 'p_coa_id = '||to_char(p_coa_id)
666 ,p_level => C_LEVEL_STATEMENT
667 ,p_module=> l_log_module );
668
669 trace
670 (p_msg => 'p_acct_flexfield_from = '||to_char(p_acct_flexfield_from )
671 ,p_level => C_LEVEL_STATEMENT
672 ,p_module=> l_log_module );
673
674 trace
675 (p_msg => 'p_acct_flexfield_to = '||to_char(p_acct_flexfield_to)
676 ,p_level => C_LEVEL_STATEMENT
677 ,p_module=> l_log_module );
678
679 END IF;
680
681 --
682 -- e.g. l_where stores the following:
683 -- gcck.SEGMENT1 BETWEEN :FLEX_PARM1 AND :FLEX_PARM2
684 -- AND gcck.SEGMENT2 BETWEEN :FLEX_PARM3 AND :FLEX_PARM4 ...
685 --
686 fnd_flex_xml_publisher_apis.kff_where
687 (p_lexical_name => 'FLEX_PARM'
688 ,p_application_short_name => 'SQLGL'
689 ,p_id_flex_code => 'GL#'
690 ,p_id_flex_num => p_coa_id
691 ,p_code_combination_table_alias => 'gcck'
692 ,p_segments => 'ALL'
693 ,p_operator => 'BETWEEN'
694 ,p_operand1 => p_acct_flexfield_from
695 ,p_operand2 => p_acct_flexfield_to
696 ,x_where_expression => l_where
697 ,x_numof_bind_variables => l_numof_bind_variables
698 ,x_bind_variables => l_bind_variables);
699
700 FOR i IN l_bind_variables.FIRST .. l_bind_variables.LAST LOOP
701 l_segment_name := l_bind_variables(i).NAME;
702 l_data_type := l_bind_variables(i).data_type;
703
704 IF(l_data_type='VARCHAR2') THEN
705
706 l_segment_value := '''' || l_bind_variables(i).varchar2_value || '''';
707
708 ELSIF (l_data_type='NUMBER') THEN
709
710 l_segment_value := l_bind_variables(i).canonical_value;
711
712 ELSIF (l_data_type='DATE') THEN
713
714 l_segment_value := '''' || TO_CHAR(l_bind_variables(i).date_value
715 ,'yyyy-mm-dd HH24:MI:SS') || '''';
716 END IF;
717
718 --
719 -- Use REGEXP_REPLACE instead of REPLACE not to replace
720 -- string 'SEGMENT1' in 'SEGMENT10'.
721 -- REGEXP_REPLACE replaces the first occurent of a segment name
722 -- e.g.
723 -- BETWEEN :FLEX_PARM9 AND :FLEX_PARM10
724 -- =>
725 -- BETWEEN '000' AND '100'
726 --
727 l_where := REGEXP_REPLACE
728 (l_where
729 ,':' || l_segment_name
730 ,l_segment_value
731 ,1 -- Position
732 ,1 -- The first occurence
733 , 'c' -- Case sensitive
734 );
735
736 END LOOP ;
737
738 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
739 trace
740 (p_msg => 'END of get_flex_range_where'
741 ,p_level => C_LEVEL_PROCEDURE
745 RETURN l_where;
742 ,p_module => l_log_module);
743 END IF;
744
746
747 EXCEPTION
748 WHEN xla_exceptions_pkg.application_exception THEN
749 RAISE;
750 WHEN OTHERS THEN
751 xla_exceptions_pkg.raise_message
752 (p_location => 'xla_jelines_rpt_pkg.get_flex_range_where');
753
754 END get_flex_range_where;
755
756
757 --==============================================================================
758 -- Private procedure get_sla_query to get value for sla query lexical parameters
759 --==============================================================================
760 PROCEDURE get_sla_query IS
761
762 l_log_module VARCHAR2(240) ;
763 l_other_param_filter VARCHAR2(8000) := ' ';
764 -- l_flex_range_where VARCHAR2(4000); bug 12586551
765 l_application_id NUMBER;
766 l_post_programs VARCHAR2(2000) := ' ';
767 l_event_classes VARCHAR2(2000) := ' ';
768 l_event_types VARCHAR2(8000) := ' '; --12663084
769
770 BEGIN
771
772 IF g_log_enabled THEN
773 l_log_module := C_DEFAULT_MODULE||'.get_sla_query';
774 END IF;
775 --
776 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
777 trace
778 (p_msg => 'BEGIN of get_sla_query'
779 ,p_level => C_LEVEL_PROCEDURE
780 ,p_module => l_log_module);
781 END IF;
782
783 p_sla_col_1 :=
784 'SELECT /*+ index(aeh XLA_AE_HEADERS_N5) no_index(ael MIS_XLA_AE_LINES_N1) */
785 to_char(aeh.accounting_date
786 ,''YYYY-MM-DD'') GL_DATE
787 ,fdu.user_name CREATED_BY
788 ,aeh.created_by LEGAL_CREATED_ID
789 ,gjb.posted_by LEGAL_POSTED_ID
790 ,to_char(aeh.creation_date
791 ,''YYYY-MM-DD"T"hh:mi:ss'') CREATION_DATE
792 ,to_char(aeh.last_update_date
793 ,''YYYY-MM-DD'') LAST_UPDATE_DATE
794 ,to_char(aeh.gl_transfer_date
795 ,''YYYY-MM-DD"T"hh:mi:ss'') GL_TRANSFER_DATE
796 ,to_char(aeh.reference_date
797 ,''YYYY-MM-DD'') REFERENCE_DATE
798 ,to_char(aeh.completed_date
799 ,''YYYY-MM-DD"T"hh:mi:ss'') COMPLETED_DATE
800 ,null EXTERNAL_REFERENCE
801 ,null REFERENCE_1
802 ,null REFERENCE_4
803 ,glp.period_year PERIOD_YEAR
804 ,'''||g_period_year_start_date||''' PERIOD_YEAR_START_DATE
805 ,'''||g_period_year_end_date||''' PERIOD_YEAR_END_DATE
806 ,glp.period_num PERIOD_NUMBER
807 ,aeh.period_name PERIOD_NAME
808 ,to_char(glp.start_date
809 ,''YYYY-MM-DD'') PERIOD_START_DATE
810 ,to_char(glp.end_date
811 ,''YYYY-MM-DD'') PERIOD_END_DATE
812 ,ent.transaction_number TRANSACTION_NUMBER
813 ,to_char(xle.transaction_date
814 ,''YYYY-MM-DD"T"hh:mi:ss'') TRANSACTION_DATE
815 ,fsv1.header_name ACCOUNTING_SEQUENCE_NAME
816 ,fsv1.version_name ACCOUNTING_SEQUENCE_VERSION
817 ,aeh.completion_acct_seq_value ACCOUNTING_SEQUENCE_NUMBER
818 ,fsv2.header_name REPORTING_SEQUENCE_NAME
819 ,fsv2.version_name REPORTING_SEQUENCE_VERSION
820 ,aeh.close_acct_seq_value REPORTING_SEQUENCE_NUMBER
821 ,NULL DOCUMENT_CATEGORY
825 ,aeh.application_id APPLICATION_ID
822 -- ,fns.sequence_name DOCUMENT_SEQUENCE_NAME -- Bug 7043803 - Fetching sequence from FND_DOCUMENT_SEQUENCES
823 ,fns.name DOCUMENT_SEQUENCE_NAME
824 ,aeh.doc_sequence_value DOCUMENT_SEQUENCE_NUMBER
826 ,fap.application_name APPLICATION_NAME
827 ,aeh.ledger_id LEDGER_ID
828 ,glg.short_name LEDGER_SHORT_NAME
829 ,glg.description LEDGER_DESCRIPTION
830 ,glg.NAME LEDGER_NAME
831 ,glg.currency_code LEDGER_CURRENCY
832 ,aeh.ae_header_id HEADER_ID
833 ,aeh.description HEADER_DESCRIPTION
834 ,xlk1.meaning JOURNAL_ENTRY_STATUS
835 ,xlk2.meaning TRANSFER_TO_GL_STATUS
836 ,aeh.balance_type_code BALANCE_TYPE_CODE
837 ,xlk3.meaning BALANCE_TYPE
838 ,glb.budget_name BUDGET_NAME
839 ,get.encumbrance_type ENCUMBRANCE_TYPE
840 ,xlk4.meaning FUND_STATUS
841 ,gjct.user_je_category_name JE_CATEGORY_NAME
842 ,gjst.user_je_source_name JE_SOURCE_NAME ';
843
844 p_sla_col_2 :=
845 ' ,xle.event_id EVENT_ID
846 ,to_char(xle.event_date
847 ,''YYYY-MM-DD'') EVENT_DATE
848 ,xle.event_number EVENT_NUMBER
849 ,xet.event_class_code EVENT_CLASS_CODE
850 ,xect.NAME EVENT_CLASS_NAME
851 ,aeh.event_type_code EVENT_TYPE_CODE
852 ,xet.NAME EVENT_TYPE_NAME
853 ,ael.displayed_line_number LINE_NUMBER
854 ,ael.ae_line_num ORIG_LINE_NUMBER
855 ,ael.accounting_class_code ACCOUNTING_CLASS_CODE
856 ,xlk5.meaning ACCOUNTING_CLASS_NAME
857 ,ael.description LINE_DESCRIPTION
858 ,ael.code_combination_id CODE_COMBINATION_ID
859 ,gcck.concatenated_segments ACCOUNTING_CODE_COMBINATION
860 ,xla_report_utility_pkg.get_ccid_desc(glg.chart_of_accounts_id
861 , ael.code_combination_id)
862 CODE_COMBINATION_DESCRIPTION
863 ,gcck.gl_control_account CONTROL_ACCOUNT_FLAG
864 ,ael.currency_code ENTERED_CURRENCY
865 ,ael.currency_conversion_rate CONVERSION_RATE
866 ,to_char(ael.currency_conversion_date
867 ,''YYYY-MM-DD'') CONVERSION_RATE_DATE
868 ,ael.currency_conversion_type CONVERSION_RATE_TYPE_CODE
869 ,gdct.user_conversion_type CONVERSION_RATE_TYPE
870 ,ael.entered_dr ENTERED_DR
871 ,ael.entered_cr ENTERED_CR
872 ,ael.unrounded_accounted_dr UNROUNDED_ACCOUNTED_DR
873 ,ael.unrounded_accounted_cr UNROUNDED_ACCOUNTED_CR
874 ,ael.accounted_dr ACCOUNTED_DR
875 ,ael.accounted_cr ACCOUNTED_CR
876 ,ael.statistical_amount STATISTICAL_AMOUNT
877 ,ael.jgzz_recon_ref RECONCILIATION_REFERENCE
878 ,ael.attribute_category ATTRIBUTE_CATEGORY
879 ,ael.attribute1 ATTRIBUTE1
880 ,ael.attribute2 ATTRIBUTE2
881 ,ael.attribute3 ATTRIBUTE3
882 ,ael.attribute4 ATTRIBUTE4
883 ,ael.attribute5 ATTRIBUTE5
884 ,ael.attribute6 ATTRIBUTE6
885 ,ael.attribute7 ATTRIBUTE7
886 ,ael.attribute8 ATTRIBUTE8
887 ,ael.attribute9 ATTRIBUTE9
888 ,ael.attribute10 ATTRIBUTE10
889 ,ael.party_type_code PARTY_TYPE_CODE
890 ,xlk6.meaning PARTY_TYPE';
891
892 p_sla_col_3 :=
893 ' ,gcck.segment1 SEGMENT1
894 ,gcck.segment2 SEGMENT2
895 ,gcck.segment3 SEGMENT3
896 ,gcck.segment4 SEGMENT4
897 ,gcck.segment5 SEGMENT5
898 ,gcck.segment6 SEGMENT6
899 ,gcck.segment7 SEGMENT7
900 ,gcck.segment8 SEGMENT8
901 ,gcck.segment9 SEGMENT9
902 ,gcck.segment10 SEGMENT10
903 ,gcck.segment11 SEGMENT11
904 ,gcck.segment12 SEGMENT12
905 ,gcck.segment13 SEGMENT13
906 ,gcck.segment14 SEGMENT14
907 ,gcck.segment15 SEGMENT15
908 ,gcck.segment16 SEGMENT16
909 ,gcck.segment17 SEGMENT17
910 ,gcck.segment18 SEGMENT18
911 ,gcck.segment19 SEGMENT19
912 ,gcck.segment20 SEGMENT20
913 ,gcck.segment21 SEGMENT21
914 ,gcck.segment22 SEGMENT22
915 ,gcck.segment23 SEGMENT23
916 ,gcck.segment24 SEGMENT24
917 ,gcck.segment25 SEGMENT25
918 ,gcck.segment26 SEGMENT26
919 ,gcck.segment27 SEGMENT27
920 ,gcck.segment28 SEGMENT28
921 ,gcck.segment29 SEGMENT29
922 ,gcck.segment30 SEGMENT30 ';
923
924 p_sla_from :=
925 'FROM
926 xla_ae_headers aeh
927 ,xla_ae_lines ael
928 ,xla_lookups xlk1
929 ,xla_lookups xlk2
930 ,xla_lookups xlk3
931 ,xla_lookups xlk4
932 ,xla_lookups xlk5
933 ,xla_lookups xlk6
934 ,xla_events xle
935 ,xla_event_classes_tl xect
936 ,xla_event_types_tl xet
937 ,fnd_user fdu
938 ,xla_transaction_entities ent
939 ,gl_ledgers glg
940 ,gl_periods glp
941 ,fnd_application_tl fap
942 ,gl_budget_versions glb
943 ,gl_encumbrance_types get
944 ,fun_seq_versions fsv1
945 ,fun_seq_versions fsv2
946 -- ,fnd_sequences fns -- Bug 7043803 - Fetching sequence from FND_DOCUMENT_SEQUENCES
947 ,fnd_document_sequences fns
948 ,xla_subledgers xls
949 ,gl_je_categories_tl gjct
950 ,gl_je_sources_tl gjst
951 ,gl_daily_conversion_types gdct
952 ,gl_code_combinations_kfv gcck';
953
954 p_sla_join :=
955 'WHERE glg.ledger_id IN $ledger_id$
956 AND aeh.ledger_id = glg.ledger_id
957 AND aeh.accounting_date BETWEEN :P_GL_DATE_FROM AND :P_GL_DATE_TO
958 AND ael.application_id = aeh.application_id
959 AND ael.ae_header_id = aeh.ae_header_id
960 AND xlk1.lookup_type = ''XLA_ACCOUNTING_ENTRY_STATUS''
961 AND xlk1.lookup_code = aeh.ACCOUNTING_ENTRY_STATUS_CODE
962 AND xlk2.lookup_type = ''GL_TRANSFER_FLAG''
963 AND xlk2.lookup_code = aeh.GL_TRANSFER_STATUS_CODE
964 AND xlk3.lookup_type = ''XLA_BALANCE_TYPE''
965 AND xlk3.lookup_code = aeh.BALANCE_TYPE_CODE
966 AND xlk4.lookup_type(+) = ''XLA_FUNDS_STATUS''
967 AND xlk4.lookup_code(+) = aeh.FUNDS_STATUS_CODE
968 AND xlk5.lookup_type = ''XLA_ACCOUNTING_CLASS''
969 AND xlk5.lookup_code = ael.ACCOUNTING_CLASS_CODE
970 AND xlk6.lookup_type(+) = ''XLA_PARTY_TYPE''
971 AND xlk6.lookup_code(+) = ael.party_type_code
972 AND xle.application_id = aeh.application_id
973 AND xle.event_id = aeh.event_id
974 AND xet.application_id = xle.application_id
975 AND xet.event_type_code = xle.event_type_code
976 AND xet.LANGUAGE = USERENV(''LANG'')
977 AND xect.application_id = xet.application_id
978 AND xect.entity_code = xet.entity_code
979 AND xect.event_class_code = xet.event_class_code
980 AND xect.LANGUAGE = USERENV(''LANG'')
981 AND ent.application_id = aeh.application_id
982 AND ent.entity_id = aeh.entity_id
983 AND fdu.user_id = ent.created_by
984 AND glp.period_name = aeh.period_name
985 AND glp.period_set_name = glg.period_set_name
986 AND fap.application_id = aeh.application_id
987 AND fap.LANGUAGE = USERENV(''LANG'')
988 AND glb.budget_version_id(+) = aeh.budget_version_id
989 AND get.encumbrance_type_id(+) = ael.encumbrance_type_id
990 AND fsv1.seq_version_id(+) = aeh.completion_acct_seq_version_id
991 AND fsv2.seq_version_id(+) = aeh.close_acct_seq_version_id
992 --AND fns.application_id(+) = aeh.application_id -- Bug 7655791
993 AND fns.doc_sequence_id(+) = aeh.doc_sequence_id -- Bug 7043803 - Fetching sequence from FND_DOCUMENT_SEQUENCES
994 AND xls.application_id = aeh.application_id
995 AND gjct.je_category_name = aeh.je_category_name
996 AND gjct.LANGUAGE = USERENV(''LANG'')
997 AND gjst.je_source_name = xls.je_source_name
998 AND gjst.LANGUAGE = USERENV(''LANG'')
999 AND gdct.conversion_type(+) = ael.currency_conversion_type
1000 AND gcck.code_combination_id = ael.code_combination_id ';
1001
1002 --
1003 -- User Transaction Identifiers
1004 --
1005 IF p_include_user_trx_id_flag = 'Y' AND
1006 g_je_source_application_id IS NOT NULL
1007 THEN
1008 /*
1009 p_trx_identifiers :=
1013 --Removed for bug 7580995
1010 xla_report_utility_pkg.get_transaction_id
1011 (g_je_source_application_id
1012 ,p_ledger_id)||' USERIDS ';*/
1014
1015 xla_report_utility_pkg.get_transaction_id
1016 (p_resp_application_id => g_je_source_application_id
1017 ,p_ledger_id => p_ledger_id
1018 ,p_trx_identifiers_1 => p_trx_identifiers_1
1019 ,p_trx_identifiers_2 => p_trx_identifiers_2
1020 ,p_trx_identifiers_3 => p_trx_identifiers_3
1021 ,p_trx_identifiers_4 => p_trx_identifiers_4
1022 ,p_trx_identifiers_5 => p_trx_identifiers_5); --Added for bug 7580995
1023
1024 ELSE
1025 -- p_trx_identifiers := ',NULL USERIDS '; --Removed for bug 7580995
1026 p_trx_identifiers_1 := ',NULL USERIDS '; --Added for bug 7580995
1027
1028 END IF;
1029
1030 --
1031 -- Third party information
1032 --
1033 IF p_party_type_code = 'S' THEN
1034
1035 -- 5072266 Modify po_vendors to use ap_suppliers
1036 -- po_vendors pov -> ap_suppliers ap
1037 p_party_details :=
1038 ',aps.segment1 PARTY_NUMBER
1039 ,aps.vendor_name PARTY_NAME
1040 ,hzp.jgzz_fiscal_code PARTY_TYPE_TAXPAYER_ID
1041 ,hzp.tax_reference PARTY_TAX_REGISTRATION_NUMBER
1042 ,hps.party_site_number PARTY_SITE_NUMBER
1043 ,hps.party_site_name PARTY_SITE_NAME
1044 ,NULL PARTY_SITE_TAX_RGSTN_NUMBER ';
1045 p_party_from :=
1046 ',ap_suppliers aps
1047 ,ap_supplier_sites_all apss
1048 ,hz_parties hzp
1049 ,hz_party_sites hps ';
1050 p_party_join :=
1051 ' AND aps.vendor_id = ael.party_id
1052 AND hzp.party_id = aps.party_id
1053 AND apss.vendor_site_id(+) = ael.party_site_id
1054 AND hps.party_site_id(+) = apss.party_site_id ';
1055
1056 ELSIF p_party_type_code = 'C' THEN
1057
1058 p_party_details :=
1059 ',hca.account_number PARTY_NUMBER
1060 ,hzp.party_name PARTY_NAME
1061 ,hzp.jgzz_fiscal_code PARTY_TYPE_TAXPAYER_ID
1062 ,hzp.tax_reference PARTY_TAX_REGISTRATION_NUMBER
1063 ,hps.party_site_number PARTY_SITE_NUMBER
1064 ,hps.party_site_name PARTY_SITE_NAME
1065 ,hzcu.tax_reference PARTY_SITE_TAX_RGSTN_NUMBER ';
1066 p_party_from :=
1067 ',hz_parties hzp
1068 ,hz_party_sites hps
1069 ,hz_cust_accounts hca
1070 ,hz_cust_acct_sites_all hcas
1071 ,hz_cust_site_uses_all hzcu ';
1072
1073 p_party_join :=
1074 ' AND hzp.party_id = hca.party_id
1075 AND hca.cust_account_id = ael.party_id
1076 AND hzcu.site_use_id(+) = ael.party_site_id
1077 AND hcas.cust_acct_site_id(+) = hzcu.cust_acct_site_id
1078 AND hps.party_site_id(+) = hcas.party_site_id ';
1079 ELSE
1080 -- Modify 5072266 Modify po_vendors to use ap_suppliers
1081 -- po_vendors pov -> ap_suppliers ap
1082
1083 /* Below the inner query is having join to xla_ae_lines ael2
1084 because it seems that CASE statment doesn't allow to have
1085 outer join from parent query column.So as a workaround we
1086 have joined to xla_ae_lines ale2 and then through ale2 we
1087 have outer joined to sites table for handling cases where
1088 party_site_id can be NULL for a valid party_id
1089 */
1090
1091 p_party_details :=
1092 ',CASE
1093 WHEN ael.party_type_code = ''S'' THEN
1094 (SELECT aps.segment1
1095 ||''|''||aps.vendor_name
1096 ||''|''||hzp.jgzz_fiscal_code
1097 ||''|''||hzp.tax_reference
1098 ||''|''||hps.party_site_number
1099 ||''|''||hps.party_site_name
1100 ||''|''||NULL
1101 FROM ap_suppliers aps
1102 ,ap_supplier_sites_all apss
1103 ,hz_parties hzp
1104 ,hz_party_sites hps
1105 ,xla_ae_lines ael2
1106 WHERE aps.vendor_id = ael2.party_id
1107 AND hzp.party_id = aps.party_id
1108 AND apss.vendor_site_id(+) = ael2.party_site_id
1109 AND hps.party_site_id(+) = apss.party_site_id
1110 AND ael2.application_id = ael.application_id
1111 AND ael2.ae_header_id = ael.ae_header_id
1112 AND ael2.ae_line_num = ael.ae_line_num )
1113 WHEN (ael.party_type_code = ''C'' and ael.party_id is not null) THEN
1114 (SELECT hca.account_number
1115 ||''|''||hzp.party_name
1116 ||''|''||hzp.jgzz_fiscal_code
1117 ||''|''||hzp.tax_reference
1118 ||''|''||hps.party_site_number
1119 ||''|''||hps.party_site_name
1120 ||''|''||hzcu.tax_reference
1121 FROM hz_cust_accounts hca
1122 ,hz_cust_acct_sites_all hcas
1123 ,hz_cust_site_uses_all hzcu
1124 ,hz_parties hzp
1125 ,hz_party_sites hps
1126 ,xla_ae_lines ael2
1127 WHERE hca.cust_account_id = ael2.party_id
1128 AND hzp.party_id = hca.party_id
1129 AND hzcu.site_use_id(+) = ael2.party_site_id
1133 AND ael2.ae_header_id = ael.ae_header_id
1130 AND hcas.cust_acct_site_id(+) = hzcu.cust_acct_site_id
1131 AND hps.party_site_id(+) = hcas.party_site_id
1132 AND ael2.application_id = ael.application_id
1134 AND ael2.ae_line_num = ael.ae_line_num )
1135 ELSE
1136 NULL
1137 END PARTY_INFO';
1138
1139 p_party_from := ' ';
1140 p_party_join := ' ';
1141
1142 END IF;
1143
1144
1145 --
1146 -- Building GL infomration in SLA query
1147 -- Modified for bug 5555715
1148 --
1149
1150
1151 p_gl_columns :=
1152 ',gjb.name GL_BATCH_NAME
1153 ,gjb.default_effective_date GL_DEFAULT_EFFECTIVE_DATE
1154 ,glk1.meaning GL_BATCH_STATUS
1155 ,to_char(gjb.posted_date
1156 ,''YYYY-MM-DD'') POSTED_DATE
1157 ,gjh.NAME GL_JE_NAME
1158 ,fns1.name GL_DOC_SEQUENCE_NAME --added bug 10385468
1159 ,gjh.doc_sequence_value GL_DOC_SEQUENCE_VALUE --added bug 10385468
1160 ,gjl.je_line_num GL_LINE_NUMBER ';
1161
1162 p_gl_view :=
1163 ',gl_import_references gir
1164 ,gl_je_lines gjl
1165 ,gl_je_headers gjh
1166 ,gl_je_batches gjb
1167 ,gl_lookups glk1
1168 ,fnd_document_sequences fns1 '; --added bug 10385468
1169
1170 -- bug8250215
1171 -- No Outer Join when GLTransfer/Posting Status is
1172 -- N(Not Posted in GL) and Y(Posted in GL) , else Outer Join
1173
1174 IF NVL(p_posting_status_code,'A') = 'N' THEN
1175
1176 p_gl_join :=
1177 ' AND gir.gl_sl_link_id = ael.gl_sl_link_id
1178 AND gir.gl_sl_link_table = ael.gl_sl_link_table
1179 AND fns1.application_id(+) = 101 -- added bug 10385468
1180 AND fns1.doc_sequence_id(+)= gjh.doc_sequence_id -- added bug 10385468
1181 AND gjl.je_header_id = gir.je_header_id
1182 AND gjl.je_line_num = gir.je_line_num
1183 AND gjh.je_header_id = gir.je_header_id
1184 AND gjb.je_batch_id = gir.je_batch_id
1185 AND decode(gjh.je_header_id,null,''Y'',gjh.je_from_sla_flag) in (''U'', ''Y'') -- bug 7163158
1186 AND glk1.lookup_type = ''BATCH_STATUS''
1187 AND glk1.lookup_code = gjb.status
1188 AND NVL(gjh.status,''U'') <> ''P''';
1189 -- removed outer join for bug:8250215
1190
1191 ELSIF NVL(p_posting_status_code,'A') = 'Y' THEN
1192
1193
1194 p_gl_join :=
1195 ' AND gir.gl_sl_link_id = ael.gl_sl_link_id
1196 AND gir.gl_sl_link_table = ael.gl_sl_link_table
1197 AND fns1.application_id(+) = 101 -- added bug 10385468
1198 AND fns1.doc_sequence_id(+)= gjh.doc_sequence_id -- added bug 10385468
1199 AND gjl.je_header_id = gir.je_header_id
1200 AND gjl.je_line_num = gir.je_line_num
1201 AND gjh.je_header_id = gir.je_header_id
1202 AND gjb.je_batch_id = gir.je_batch_id
1203 AND gjh.je_from_sla_flag in (''U'', ''Y'') -- bug 7163158
1204 AND glk1.lookup_type = ''BATCH_STATUS''
1205 AND glk1.lookup_code = gjb.status
1206 AND gjh.status = ''P'' ';
1207
1208 ELSIF NVL(p_posting_status_code,'A') = 'T' THEN
1209
1210 p_gl_join :=
1211 ' AND gir.gl_sl_link_id = ael.gl_sl_link_id
1212 AND gir.gl_sl_link_table = ael.gl_sl_link_table
1213 AND fns1.application_id(+) = 101 -- added bug 10385468
1214 AND fns1.doc_sequence_id(+)= gjh.doc_sequence_id -- added bug 10385468
1215 AND gjl.je_header_id = gir.je_header_id
1216 AND gjl.je_line_num = gir.je_line_num
1217 AND gjh.je_header_id = gir.je_header_id
1218 AND gjb.je_batch_id = gir.je_batch_id
1219 AND gjh.je_from_sla_flag in (''U'', ''Y'') -- bug 7163158
1220 AND glk1.lookup_type = ''BATCH_STATUS''
1221 AND glk1.lookup_code = gjb.status
1222 '; -- added for bug:8250215
1223
1224 ELSIF NVL(p_posting_status_code,'A') IN ( 'X' ,'A') THEN -- added status code: X for bug:8250215
1225
1226 p_gl_join :=
1227 ' AND gir.gl_sl_link_id(+) = ael.gl_sl_link_id
1228 AND gir.gl_sl_link_table(+) = ael.gl_sl_link_table
1229 AND fns1.application_id(+) = 101 -- added bug 10385468
1230 AND fns1.doc_sequence_id(+)= gjh.doc_sequence_id -- added bug 10385468
1231 AND gjl.je_header_id(+) = gir.je_header_id
1232 AND gjl.je_line_num(+) = gir.je_line_num
1233 AND gjh.je_header_id(+) = gir.je_header_id
1234 AND gjb.je_batch_id (+) = gir.je_batch_id
1235 AND decode(gjh.je_header_id,null,''Y'',gjh.je_from_sla_flag) in (''U'', ''Y'') -- bug 7163158
1236 AND glk1.lookup_type(+) = ''BATCH_STATUS''
1237 AND glk1.lookup_code(+) = gjb.status ';
1238
1239 END IF;
1240
1241 IF p_gl_batch_name IS NOT NULL THEN
1242 p_gl_join := p_gl_join||' AND gjb.name = '''||p_gl_batch_name||'''';
1243 END IF;
1244
1245 --
1246 -- Building Legal entity information: Bug 5659083
1247 --
1248
1249 IF p_include_le_info_flag = 'NONE' THEN
1250 p_sla_legal_ent_col := ' ';
1251 p_sla_legal_ent_from := ' ';
1252 p_sla_legal_ent_join := ' ';
1253 ELSE
1254 p_sla_legal_ent_col := C_SLA_TRX_LEGAL_ENT_COL;
1255 p_sla_legal_ent_from := C_SLA_TRX_LEGAL_ENT_FROM;
1256 p_sla_legal_ent_join := C_SLA_TRX_LEGAL_ENT_JOIN;
1257 END IF;
1258
1259 IF p_trx_legal_entity_id IS NOT NULL THEN
1260 p_sla_legal_ent_join := p_sla_legal_ent_join ||
1261 ' AND ent.legal_entity_id = :p_trx_legal_entity_id ';
1262 END IF;
1263
1264
1265 ----------------------------------------------------------------------------
1266 -- build filter condition based on parameters
1267 ----------------------------------------------------------------------------
1268 --
1269 -- <conditions based on p_post_acct_program_rowid >
1270 --
1271 IF p_post_acct_program_rowid IS NOT NULL THEN
1272 l_post_programs := l_post_programs||
1273 ' AND ael.accounting_class_code IN (NULL';
1274
1275 /*FOR c1 in (select accounting_class_code
1276 from xla_acct_class_assgns xac, xla_post_acct_progs_b xpa
1277 where xpa.rowid = p_post_acct_program_rowid
1278 and xac.program_owner_code = xpa.program_owner_code
1279 and xac.program_code = xpa.program_code
1280 )*/ -- Changed for bug 8337868
1281 FOR c1 in (select accounting_class_code
1282 from xla_acct_class_assgns xac, xla_post_acct_progs_b xpa,
1283 xla_assignment_defns_b xad
1284 where xpa.rowid = p_post_acct_program_rowid
1285 and xac.program_owner_code = xpa.program_owner_code
1286 and xac.program_code = xpa.program_code
1287 and xad.program_code = xac.program_code
1288 and xad.program_owner_code = xac.program_owner_code
1289 and xad.assignment_code = xac.assignment_code
1290 and xad.assignment_owner_code = xac.assignment_owner_code
1291 and xad.enabled_flag = 'Y'
1292 -- and nvl(xad.ledger_id, p_ledger_id) = p_ledger_id Removing this as not required
1293 )
1294 LOOP
1295 l_post_programs := l_post_programs||
1296 ','''||c1.accounting_class_code||'''';
1297 END LOOP;
1298 l_post_programs := l_post_programs||')';
1299
1300 l_other_param_filter := l_other_param_filter||l_post_programs;
1301 END IF;
1302
1303 --
1304 -- <conditions based on transaction view >
1305 --
1306 IF p_transaction_view IS NOT NULL THEN
1307 l_event_classes := l_event_classes||
1308 ' AND xet.event_class_code IN (NULL';
1309
1310 FOR c1 in (SELECT DISTINCT event_class_code
1311 FROM xla_event_class_attrs
1312 WHERE application_id = g_je_source_application_id
1313 AND reporting_view_name = p_transaction_view
1314 )
1315 LOOP
1316 l_event_classes := l_event_classes||
1317 ','''||c1.event_class_code||'''';
1318 END LOOP;
1319 l_event_classes := l_event_classes||')';
1320
1321 l_other_param_filter := l_other_param_filter||l_event_classes;
1322
1323 --added filter on event types based on event classes 12663084
1324 l_event_types := l_event_types||
1325 ' AND aeh.event_type_code IN (NULL';
1326
1327 FOR c1 in (SELECT DISTINCT xet.event_type_code
1328 FROM xla_event_class_attrs xeca, xla_event_types_b xet
1329 WHERE xeca.application_id = g_je_source_application_id
1330 AND xeca.reporting_view_name = p_transaction_view
1331 AND xeca.event_class_code = xet.event_class_code
1332 AND xeca.entity_code = xet.entity_code
1333 AND xeca.application_id = xet.application_id
1334 )
1335 LOOP
1336 l_event_types := l_event_types||
1337 ','''||c1.event_type_code||'''';
1338 END LOOP;
1339 l_event_types := l_event_types||')';
1340
1341 l_other_param_filter := l_other_param_filter||l_event_types;
1342 --added filter on event types based on event classes 12663084
1343 END IF;
1344
1345 --
1346 -- < conditions based on event class rowid >
1347 --
1348 IF p_event_class_rowid IS NOT NULL AND
1349 p_transaction_view IS NULL
1350 THEN
1351 SELECT ' AND xet.event_class_code = '''||event_class_code||''' '
1352 INTO l_event_classes
1353 FROM xla_event_classes_b
1354 WHERE rowid = p_event_class_rowid;
1355
1356 l_other_param_filter := l_other_param_filter||l_event_classes;
1357
1358 --added filter on event types based on event classes 12663084
1359 l_event_types := l_event_types||
1360 ' AND aeh.event_type_code IN (NULL';
1361
1362 FOR c1 in (SELECT DISTINCT xet.event_type_code
1363 FROM xla_event_classes_b xec, xla_event_types_b xet
1364 WHERE xec.rowid = p_event_class_rowid
1365 AND xec.event_class_code = xet.event_class_code
1366 AND xec.entity_code = xet.entity_code
1367 AND xec.application_id = xet.application_id
1368 )
1369 LOOP
1370 l_event_types := l_event_types||
1371 ','''||c1.event_type_code||'''';
1372 END LOOP;
1373 l_event_types := l_event_types||')';
1374
1375 l_other_param_filter := l_other_param_filter||l_event_types;
1376 --added filter on event types based on event classes 12663084
1377 END IF;
1378
1379 --
1380 -- < conditions based on process category rowid >
1381 --
1382 IF p_process_category_rowid IS NOT NULL AND
1383 p_transaction_view IS NULL AND
1384 p_event_class_rowid IS NULL
1385 THEN
1386 l_event_classes := l_event_classes||
1387 ' AND xet.event_class_code IN (NULL';
1388
1389 FOR c1 in (SELECT DISTINCT event_class_code
1390 FROM xla_event_class_grps_b a
1391 ,xla_event_class_attrs b
1392 WHERE a.application_id = b.application_id
1393 AND a.event_class_group_code = b.event_class_group_code
1394 AND a.rowid = p_process_category_rowid
1395 )
1396 LOOP
1397 l_event_classes := l_event_classes||
1398 ','''||c1.event_class_code||'''';
1399 END LOOP;
1400 l_event_classes := l_event_classes||')';
1401
1402 l_other_param_filter := l_other_param_filter||l_event_classes;
1403
1404 --added filter on event types based on event classes 12663084
1405 l_event_types := l_event_types||
1406 ' AND aeh.event_type_code IN (NULL';
1407
1408 FOR c1 in (SELECT DISTINCT xet.event_type_code
1409 FROM xla_event_class_grps_b xecg
1410 , xla_event_class_attrs xeca
1411 , xla_event_types_b xet
1412 WHERE xecg.application_id = xeca.application_id
1413 AND xecg.event_class_group_code = xeca.event_class_group_code
1414 AND xecg.rowid = p_process_category_rowid
1415 AND xeca.event_class_code = xet.event_class_code
1416 AND xeca.entity_code = xet.entity_code
1417 AND xeca.application_id = xet.application_id
1418 )
1419 LOOP
1420 l_event_types := l_event_types||
1421 ','''||c1.event_type_code||'''';
1422 END LOOP;
1423 l_event_types := l_event_types||')';
1424
1425 l_other_param_filter := l_other_param_filter||l_event_types;
1426 --added filter on event types based on event classes 12663084
1427 END IF;
1428
1429 --
1430 -- <conditions based on transaction number> bug 8337868
1431 --
1432 IF p_trx_num_from IS NOT NULL THEN
1433 l_other_param_filter := l_other_param_filter
1434 ||' AND ent.transaction_number >= '
1435 ||''''||p_trx_num_from||'''';
1436 END IF;
1437
1438 IF p_trx_num_to IS NOT NULL THEN
1439 l_other_param_filter := l_other_param_filter
1440 || ' AND ent.transaction_number <= '
1441 ||''''||p_trx_num_to||'''';
1442 END IF;
1443
1444
1445 --
1446 -- <conditions based on creation date>
1447 --
1448 IF p_creation_date_from IS NOT NULL THEN
1449 l_other_param_filter := l_other_param_filter
1450 ||' AND ent.creation_date >= '
1451 ||''''||p_creation_date_from||'''';
1452 END IF;
1453
1454 IF p_creation_date_to IS NOT NULL THEN
1455 l_other_param_filter := l_other_param_filter
1459
1456 || ' AND ent.creation_date <= '
1457 ||''''||p_creation_date_to||'''';
1458 END IF;
1460 --
1461 -- <conditions based on transaction date>
1462 --
1463 IF p_transaction_date_from IS NOT NULL THEN
1464 l_other_param_filter := l_other_param_filter
1465 ||' AND xle.transaction_date >= '
1466 ||''''||p_transaction_date_from||'''';
1467 END IF;
1468
1469 IF p_transaction_date_to IS NOT NULL THEN
1470 l_other_param_filter := l_other_param_filter
1471 ||'AND xle.transaction_date <= '
1472 ||'''' ||p_transaction_date_to||'''';
1473 END IF;
1474
1475 --
1476 -- <conditions based on je status>
1477 --
1478 IF NVL(p_je_status_code,'A')='F' THEN
1479 l_other_param_filter := l_other_param_filter
1480 ||' AND aeh.accounting_entry_status_code = ''F'' ';
1481
1482 ELSIF NVL(p_je_status_code,'A')='D' THEN
1483 l_other_param_filter := l_other_param_filter
1484 ||' AND aeh.accounting_entry_status_code = ''D'' ';
1485
1486 ELSIF NVL(p_je_status_code,'A')='I' THEN
1487 l_other_param_filter := l_other_param_filter
1488 ||' AND aeh.accounting_entry_status_code '
1489 ||' IN (''I'',''R'',''RELATED_EVENT_ERROR'') ';
1490 ELSE
1491 l_other_param_filter := l_other_param_filter
1492 ||' AND aeh.accounting_entry_status_code <> ''N'' ';
1493 END IF ;
1494
1495 --
1496 -- added for bug 8250215
1497 -- Look Up Transfer/Posting Status AEH.GL_TRANSFER_STATUS_CODE
1498 -- ====================================================================
1499 -- Y Posted In GL Y
1500 -- N Not Posted In GL Y
1501 -- T Transferred to GL No Condition
1502 -- X Not Transferred to GL N
1503 -- NULL Every thing No Condition
1504
1505 IF NVL(p_posting_status_code,'A') = 'X' THEN
1506 l_other_param_filter := l_other_param_filter
1507 ||' AND aeh.gl_transfer_status_code = ''N'' ';
1508
1509 ELSIF NVL(p_posting_status_code,'A') IN ( 'N','Y') THEN
1510 l_other_param_filter := l_other_param_filter
1511 ||' AND aeh.gl_transfer_status_code = ''Y'' ';
1512 END IF ;
1513
1514 --
1515 -- <conditions based on gl je source (g_je_source_application_id)>
1516 --
1517 IF g_je_source_application_id IS NOT NULL THEN
1518
1519 p_sla_col_1 := REPLACE(p_sla_col_1, 'index(aeh XLA_AE_HEADERS_N5) no_index(ael MIS_XLA_AE_LINES_N1)',
1520 'leading(xls glg aeh glp) use_nl(glp) index(aeh XLA_AE_HEADERS_N5) no_index(ael MIS_XLA_AE_LINES_N1)' );
1521
1522 l_other_param_filter :=
1523 l_other_param_filter||' AND aeh.application_id = '|| g_je_source_application_id;
1524 END IF;
1525
1526 --
1527 -- <conditions based on accounting sequence name and number range>
1528 --
1529 IF p_acct_sequence_version IS NOT NULL THEN
1530 l_other_param_filter := l_other_param_filter
1531 ||' AND aeh.completion_acct_seq_version_id = '
1532 || p_acct_sequence_version ;
1533 END IF;
1534
1535 IF p_acct_sequence_num_from IS NOT NULL THEN
1536 l_other_param_filter := l_other_param_filter
1537 || ' AND aeh.completion_acct_seq_value >= '
1538 || p_acct_sequence_num_from ;
1539 END IF;
1540
1541 IF p_acct_sequence_num_to IS NOT NULL THEN
1542 l_other_param_filter := l_other_param_filter
1543 ||' AND aeh.completion_acct_seq_value <= '
1544 || p_acct_sequence_num_to ;
1545 END IF;
1546
1547 --
1548 -- <conditions based on reporting sequence name and number range>
1549 --
1550 IF p_rpt_sequence_version IS NOT NULL THEN
1551 l_other_param_filter := l_other_param_filter
1552 || ' AND aeh.close_acct_seq_version_id = '
1553 || p_rpt_sequence_version ;
1554 END IF;
1555
1556 IF p_rpt_sequence_num_from IS NOT NULL THEN
1557 l_other_param_filter := l_other_param_filter
1558 || ' AND aeh.close_acct_seq_value >= '
1559 || p_rpt_sequence_num_from ;
1560 END IF;
1561
1562 IF p_rpt_sequence_num_to IS NOT NULL THEN
1563 l_other_param_filter := l_other_param_filter
1564 || ' AND aeh.close_acct_seq_value <= '
1565 || p_rpt_sequence_num_to ;
1566 END IF;
1567
1568
1569 -- <conditions based on document seq name>
1570 -- Bug #5741692 Added condition based on document sequence name
1571 IF p_doc_seq_name IS NOT NULL THEN
1572 l_other_param_filter := l_other_param_filter
1573 ||' AND fns.name = '''
1574 ||p_doc_seq_name||'''';
1575 END IF;
1576
1577
1578 --
1579 -- <conditions based on document sequence name and number range>
1580 --
1581 IF p_doc_sequence_num_from IS NOT NULL THEN
1582 l_other_param_filter := l_other_param_filter
1583 || ' AND aeh.doc_sequence_value >= '
1584 || p_doc_sequence_num_from;
1585 END IF;
1586
1587 IF p_doc_sequence_num_to IS NOT NULL THEN
1588 l_other_param_filter := l_other_param_filter
1589 || ' AND aeh.doc_sequence_value <= '
1590 || p_doc_sequence_num_to;
1591 END IF;
1592
1593 --
1594 -- <condition based on party type >
1595 --
1596 IF p_party_type_code IS NOT NULL THEN
1597 l_other_param_filter := l_other_param_filter
1598 ||' AND ael.party_type_code = '
1599 ||''''||p_party_type_code||'''';
1600 END IF;
1601
1602 --
1603 -- <condition based on party name >
1604 --
1605 IF p_party_name IS NOT NULL THEN
1606 /* Changed from hzp to XLA_AE_LINES for bug#12571692 */
1607 l_other_param_filter := l_other_param_filter
1608 ||' AND ael.party_id = '
1609 ||p_party_id;
1610 END IF;
1611
1612 --
1613 -- <condition based on party number range >
1614 --
1615 IF p_party_number_from IS NOT NULL THEN
1616 IF p_party_type_code = 'S' THEN
1617 l_other_param_filter := l_other_param_filter
1618 ||' AND aps.segment1 >= '
1619 ||''''||p_party_number_from||'''';
1620
1621 ELSIF p_party_type_code ='C' THEN
1622 l_other_param_filter := l_other_param_filter
1623 ||' AND hca.account_number >= '
1624 ||''''||p_party_number_from||'''';
1625 END IF;
1626 END IF;
1627
1628 IF p_party_number_to IS NOT NULL THEN
1629 IF p_party_type_code = 'S' THEN
1630 l_other_param_filter := l_other_param_filter
1631 ||' AND aps.segment1 <= '
1632 ||''''||p_party_number_to||'''';
1633
1634 ELSIF p_party_type_code ='C' THEN
1635 l_other_param_filter := l_other_param_filter
1636 ||' AND hca.account_number <= '
1637 ||''''||p_party_number_to||'''';
1638 END IF;
1639 END IF;
1640
1641 --
1642 -- <conditions based on period type> Added for bug 7645837
1643 --
1644 IF p_period_type IS NOT NULL THEN
1645 IF p_period_type = 'A' THEN
1646 l_other_param_filter := l_other_param_filter
1647 || ' AND glp.adjustment_period_flag = ''Y'' ' ;
1648 ELSIF p_period_type = 'N' THEN
1649 l_other_param_filter := l_other_param_filter
1650 || ' AND glp.adjustment_period_flag = ''N'' ' ;
1651 END IF;
1652
1653 END IF;
1654
1655
1656 --
1657 -- <conditions based on gl ge category>
1658 --
1659 IF p_je_category IS NOT NULL THEN
1660 l_other_param_filter := l_other_param_filter
1661 || ' AND aeh.je_category_name = '''
1662 ||p_je_category||'''';
1663 END IF;
1664
1665 --
1669 l_other_param_filter := l_other_param_filter
1666 -- <conditions based on balance type>
1667 --
1668 IF p_balance_type_code IS NOT NULL THEN
1670 || ' AND aeh.balance_type_code = '''
1671 || p_balance_type_code||'''';
1672 END IF;
1673
1674 --
1675 -- <conditions based on budget_version_id>
1676 --
1677 IF p_budget_version_id IS NOT NULL THEN
1678 l_other_param_filter := l_other_param_filter
1679 || ' AND aeh.budget_version_id = '
1680 ||p_budget_version_id;
1681 END IF;
1682
1683 --
1684 -- <conditions based on encumbrance type>
1685 --
1686 IF p_encumbrance_type_id IS NOT NULL THEN
1687 l_other_param_filter := l_other_param_filter
1688 || ' AND ael.encumbrance_type_id = '
1689 || p_encumbrance_type_id;
1690 END IF;
1691
1692 --
1693 -- <conditions based on include zero amount flag>
1694 --
1695 IF p_include_zero_amount_flag = 'N' THEN
1696 --bug#6978940 changed > 0 to <> 0
1697 l_other_param_filter := l_other_param_filter
1698 ||' AND ( NVL(ael.accounted_cr,0) <> 0
1699 OR NVL(ael.accounted_dr,0) <> 0 ) ';
1700 END IF;
1701
1702 --
1703 -- <conditions based on entered currency code>
1704 --
1705 IF p_entered_currency IS NOT NULL THEN
1706 l_other_param_filter := l_other_param_filter
1707 || ' AND ael.currency_code ='''
1708 || p_entered_currency||'''' ;
1709 END IF;
1710
1711 --
1712 -- <conditions based on accounted amount range>
1713 --
1714 IF p_accounted_amount_from IS NOT NULL THEN
1715 l_other_param_filter := l_other_param_filter
1716 ||' AND (NVL(ael.accounted_cr,0) >= '
1717 || p_accounted_amount_from
1718 ||' OR NVL(ael.accounted_dr,0) >= '
1719 || p_accounted_amount_from ||') ';
1720 END IF;
1721
1722 IF p_accounted_amount_to IS NOT NULL THEN
1723 l_other_param_filter := l_other_param_filter
1724 || ' AND (NVL(ael.accounted_cr,0) <= '
1725 ||p_accounted_amount_to
1726 ||' OR NVL(ael.accounted_dr,0) <= '
1727 ||p_accounted_amount_to ||') ';
1728 END IF;
1729
1730
1731 --
1732 -- <conditions based on side>
1733 --
1734 IF p_side_code = 'DEBIT' THEN
1735 l_other_param_filter := l_other_param_filter
1736 ||' AND ( NVL(ael.accounted_cr,0)
1737 - NVL(ael.accounted_dr,0) < 0) ';
1738 ELSIF p_side_code = 'CREDIT' THEN
1739 l_other_param_filter := l_other_param_filter
1740 ||' AND ( NVL(ael.accounted_cr,0)
1741 - NVL(ael.accounted_dr,0) > 0) ';
1742 END IF;
1743
1744 --
1745 -- <conditions based on valuation method>
1746 --
1747 IF p_valuation_method IS NOT NULL THEN
1748 l_other_param_filter := l_other_param_filter
1749 ||' AND ent.valuation_method = '''
1750 ||p_valuation_method||'''';
1751 END IF;
1752
1753 --
1754 -- <conditions based on security identifiers>
1755 --
1756 IF p_security_id_int_1 IS NOT NULL THEN
1757 l_other_param_filter := l_other_param_filter
1758 ||' AND ent.security_id_int_1 = '
1759 ||p_security_id_int_1;
1760 END IF;
1761
1762 IF p_security_id_int_2 IS NOT NULL THEN
1763 l_other_param_filter := l_other_param_filter
1764 ||' AND ent.security_id_int_2 = '
1765 ||p_security_id_int_2;
1766 END IF;
1767
1768 IF p_security_id_int_3 IS NOT NULL THEN
1769 l_other_param_filter := l_other_param_filter
1770 ||' AND ent.security_id_int_3 = '
1771 ||p_security_id_int_3;
1772 END IF;
1773
1774 IF p_security_id_char_1 IS NOT NULL THEN
1775 l_other_param_filter := l_other_param_filter
1776 ||' AND ent.security_id_char_1 = '''
1777 ||p_security_id_char_1||'''';
1778 END IF ;
1779
1780 IF p_security_id_char_2 IS NOT NULL THEN
1781 l_other_param_filter := l_other_param_filter
1782 ||' AND ent.security_id_char_2 = '''
1783 ||p_security_id_char_2||'''';
1784 END IF ;
1785
1786 IF p_security_id_char_3 IS NOT NULL THEN
1787 l_other_param_filter := l_other_param_filter
1788 ||' AND ent.security_id_char_3 ='''
1789 ||p_security_id_char_3||'''';
1790 END IF ;
1791
1792 IF p_account_flexfield_from IS NOT NULL AND
1793 p_account_flexfield_to IS NOT NULL
1794 THEN
1795 l_other_param_filter :=
1796 l_other_param_filter ||' AND '||g_flex_range_where; --bug 12586551
1797 END IF;
1798
1799 p_other_param_filter := l_other_param_filter;
1800
1801 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1802 trace
1803 (p_msg => 'END of get_sla_query'
1804 ,p_level => C_LEVEL_PROCEDURE
1805 ,p_module => l_log_module);
1806 END IF;
1807
1808 EXCEPTION
1809 WHEN xla_exceptions_pkg.application_exception THEN
1810 RAISE;
1814
1811 WHEN OTHERS THEN
1812 xla_exceptions_pkg.raise_message
1813 (p_location => 'xla_jelines_rpt_pkg.get_sla_query');
1815 END get_sla_query;
1816
1817 --==============================================================================
1818 -- Private procedure get_gl_query to get value for GL query lexical parameters
1819 --==============================================================================
1820 PROCEDURE get_gl_query(p_accounts_range_specified in varchar2) IS
1821
1822 l_log_module VARCHAR2(240);
1823 l_other_param_filter VARCHAR2(8000);
1824 -- l_flex_range_where VARCHAR2(4000); bug 12586551
1825 BEGIN
1826 IF g_log_enabled THEN
1827 l_log_module := C_DEFAULT_MODULE||'.get_gl_query';
1828 END IF;
1829 --
1830 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1831 trace
1832 (p_msg => 'BEGIN of get_gl_query'
1833 ,p_level => C_LEVEL_PROCEDURE
1834 ,p_module => l_log_module);
1835 END IF;
1836
1837 /*
1838 Added the ordered hint in gl query for bug#8815942
1839 In customers case the query was hitting gl_je_lines first and taking a path to gl_je_headers causing
1840 performance. Leading hint to start the query from gl_je_headers was not working at customers instance used ordered
1841 to start from glh filter the records and then hit gll. The ordered hint has improved the performance of Journal Entries Query
1842 by 50 percent. Removed the ordered hint as part of bug#12609107
1843 */
1844
1845
1846
1847 /* begin performance fix bug14050580 */
1848
1849 if p_accounts_range_specified='Y' then
1850 p_gl_col_1 :=
1851 'SELECT /*+ leading(glg glp gcck) */ ';
1852 else
1853 p_gl_col_1 :=
1854 'SELECT /*+ leading(glg glp) */ ';
1855 end if;
1856 /* End performance fix bug14050580 */
1857
1858 p_gl_col_1 :=p_gl_col_1||'
1859 to_char(glh.default_effective_date
1860 ,''YYYY-MM-DD'') GL_DATE
1861 ,fdu.user_name CREATED_BY
1862 ,glh.created_by LEGAL_CREATED_ID
1863 ,gjb.posted_by LEGAL_POSTED_ID
1864 ,to_char(glh.creation_date
1865 ,''YYYY-MM-DD"T"hh:mi:ss'') CREATION_DATE
1866 ,to_char(glh.last_update_date
1867 ,''YYYY-MM-DD'') LAST_UPDATE_DATE
1868 ,NULL GL_TRANSFER_DATE
1869 ,to_char(glh.reference_date
1870 ,''YYYY-MM-DD'') REFERENCE_DATE
1871 ,NULL COMPLETED_DATE
1872 ,glh.external_reference EXTERNAL_REFERENCE
1873 ,gll.reference_1 REFERENCE_1
1874 ,gll.reference_4 REFERENCE_4
1875 ,glp.period_year PERIOD_YEAR
1876 ,'''||g_period_year_start_date||''' PERIOD_YEAR_START_DATE
1877 ,'''||g_period_year_end_date||''' PERIOD_YEAR_END_DATE
1878 ,glp.period_num PERIOD_NUMBER
1879 ,glh.period_name PERIOD_NAME
1880 ,to_char(glp.start_date
1881 ,''YYYY-MM-DD'') PERIOD_START_DATE
1882 ,to_char(glp.end_date
1883 ,''YYYY-MM-DD'') PERIOD_END_DATE
1884 ,NULL TRANSACTION_NUMBER
1885 ,NULL TRANSACTION_DATE
1886 ,fsv1.header_name ACCOUNTING_SEQUENCE_NAME
1887 ,fsv1.version_name ACCOUNTING_SEQUENCE_VERSION
1888 ,glh.posting_acct_seq_value ACCOUNTING_SEQUENCE_NUMBER
1889 ,fsv2.header_name REPORTING_SEQUENCE_NAME
1890 ,fsv2.version_name REPORTING_SEQUENCE_VERSION
1891 ,glh.close_acct_seq_value REPORTING_SEQUENCE_NUMBER
1892 ,NULL DOCUMENT_CATEGORY
1893 ,NULL DOCUMENT_SEQUENCE_NAME
1894 ,NULL DOCUMENT_SEQUENCE_NUMBER
1895 ,NULL APPLICATION_ID
1896 ,NULL APPLICATION_NAME
1897 ,glh.ledger_id LEDGER_ID
1898 ,glg.short_name LEDGER_SHORT_NAME
1899 ,glg.description LEDGER_DESCRIPTION
1900 ,glg.NAME LEDGER_NAME
1901 ,glg.currency_code LEDGER_CURRENCY
1902 ,glh.je_header_id HEADER_ID
1903 ,glh.description HEADER_DESCRIPTION
1904 ,NULL JOURNAL_ENTRY_STATUS
1905 ,NULL TRANSFER_TO_GL_STATUS
1906 ,glh.actual_flag BALANCE_TYPE_CODE
1907 ,xlk.meaning BALANCE_TYPE
1908 ,gbv.budget_name BUDGET_NAME
1909 ,get.encumbrance_type ENCUMBRANCE_TYPE
1910 ,NULL FUND_STATUS
1911 ,gjct.user_je_category_name JE_CATEGORY_NAME
1912 ,gjst.user_je_source_name JE_SOURCE_NAME --bug12408239
1913 ,gjb.NAME GL_BATCH_NAME
1914 ,gjb.default_effective_date GL_DEFAULT_EFFECTIVE_DATE
1915 ,glk2.meaning GL_BATCH_STATUS
1916 ,to_char(glh.posted_date
1917 ,''YYYY-MM-DD'') POSTED_DATE
1918 ,glh.NAME GL_JE_NAME
1919 -- ,fsq.sequence_name GL_DOC_SEQUENCE_NAME -- krsankar - Commented as part of Bug 7153425
1920 ,fsq.name GL_DOC_SEQUENCE_NAME
1921 ,glh.doc_sequence_value GL_DOC_SEQUENCE_VALUE
1922 ,gll.je_line_num GL_LINE_NUMBER
1923 ,NULL EVENT_ID
1924 ,NULL EVENT_DATE
1925 ,NULL EVENT_NUMBER
1926 ,NULL EVENT_CLASS_CODE
1927 ,NULL EVENT_CLASS_NAME
1928 ,NULL EVENT_TYPE_CODE
1929 ,NULL EVENT_TYPE_NAME
1930 ,gll.je_line_num LINE_NUMBER
1931 ,gll.je_line_num ORIG_LINE_NUMBER
1932 ,NULL ACCOUNTING_CLASS_CODE
1933 ,NULL ACCOUNTING_CLASS_NAME
1934 ,gll.description LINE_DESCRIPTION
1935 ,gll.code_combination_id CODE_COMBINATION_ID
1936 ,gcck.concatenated_segments ACCOUNTING_CODE_COMBINATION
1937 ,xla_report_utility_pkg.get_ccid_desc(glg.chart_of_accounts_id
1938 , gll.code_combination_id)
1939 CODE_COMBINATION_DESCRIPTION
1940 ,gcck.gl_control_account CONTROL_ACCOUNT_FLAG
1941 ,glh.currency_code ENTERED_CURRENCY
1942 ,glh.currency_conversion_rate CONVERSION_RATE
1943 ,to_char(glh.currency_conversion_date
1944 ,''YYYY-MM-DD'') CONVERSION_RATE_DATE
1945 ,glh.currency_conversion_type CONVERSION_RATE_TYPE_CODE
1946 ,gdct.user_conversion_type CONVERSION_RATE_TYPE
1947 ,gll.entered_dr ENTERED_DR
1948 ,gll.entered_cr ENTERED_CR
1949 ,NULL UNROUNDED_ACCOUNTED_DR
1950 ,NULL UNROUNDED_ACCOUNTED_CR
1951 ,gll.accounted_dr ACCOUNTED_DR
1952 ,gll.accounted_cr ACCOUNTED_CR
1953 ,gll.stat_amount STATISTICAL_AMOUNT
1954 ,gll.jgzz_recon_ref_11i RECONCILIATION_REFERENCE
1955 ,gll.CONTEXT ATTRIBUTE_CATEGORY
1956 ,gll.attribute1 ATTRIBUTE1
1957 ,gll.attribute2 ATTRIBUTE2
1958 ,gll.attribute3 ATTRIBUTE3
1959 ,gll.attribute4 ATTRIBUTE4
1960 ,gll.attribute5 ATTRIBUTE5
1961 ,gll.attribute6 ATTRIBUTE6
1962 ,gll.attribute7 ATTRIBUTE7
1963 ,gll.attribute8 ATTRIBUTE8
1964 ,gll.attribute9 ATTRIBUTE9
1965 ,gll.attribute10 ATTRIBUTE10
1966 ,NULL PARTY_TYPE_CODE
1967 ,NULL PARTY_TYPE ';
1968
1969 p_gl_col_2 :=
1970 ' ,gcck.segment1 SEGMENT1
1971 ,gcck.segment2 SEGMENT2
1972 ,gcck.segment3 SEGMENT3
1973 ,gcck.segment4 SEGMENT4
1974 ,gcck.segment5 SEGMENT5
1975 ,gcck.segment6 SEGMENT6
1976 ,gcck.segment7 SEGMENT7
1977 ,gcck.segment8 SEGMENT8
1978 ,gcck.segment9 SEGMENT9
1979 ,gcck.segment10 SEGMENT10
1980 ,gcck.segment11 SEGMENT11
1981 ,gcck.segment12 SEGMENT12
1982 ,gcck.segment13 SEGMENT13
1983 ,gcck.segment14 SEGMENT14
1984 ,gcck.segment15 SEGMENT15
1985 ,gcck.segment16 SEGMENT16
1986 ,gcck.segment17 SEGMENT17
1987 ,gcck.segment18 SEGMENT18
1988 ,gcck.segment19 SEGMENT19
1989 ,gcck.segment20 SEGMENT20
1990 ,gcck.segment21 SEGMENT21
1991 ,gcck.segment22 SEGMENT22
1992 ,gcck.segment23 SEGMENT23
1993 ,gcck.segment24 SEGMENT24
1994 ,gcck.segment25 SEGMENT25
1995 ,gcck.segment26 SEGMENT26
1996 ,gcck.segment27 SEGMENT27
1997 ,gcck.segment28 SEGMENT28
1998 ,gcck.segment29 SEGMENT29
1999 ,gcck.segment30 SEGMENT30
2000 ,NULL USERIDS ';
2001
2002 p_gl_from :=
2003 'FROM
2004 gl_je_headers glh
2005 ,gl_je_lines gll
2006 ,gl_ledgers glg
2007 ,xla_lookups xlk
2008 ,gl_lookups glk2
2009 ,gl_budget_versions gbv
2010 ,fnd_user fdu
2011 ,gl_period_statuses glp --12609107
2012 ,fun_seq_versions fsv1
2013 ,fun_seq_versions fsv2
2014 -- ,fnd_sequences fsq -- krsankar - Commented as part of Bug 7153425
2015 ,fnd_document_sequences fsq
2016 ,gl_encumbrance_types get
2017 ,gl_je_categories_tl gjct
2018 ,gl_je_sources_tl gjst --bug12408239
2019 ,gl_je_batches gjb
2020 ,gl_code_combinations_kfv gcck
2021 ,gl_daily_conversion_types gdct ';
2022
2023 -- Bug 5097723. Put an outer join while joining gl_je_headers and fnd_sequences.
2024 p_gl_where :=
2028 and glp.period_name = gll.period_name -- Added perf 14050580
2025 'WHERE glg.ledger_id IN $ledger_id$
2026 --AND glh.period_name BETWEEN :P_PERIOD_FROM AND :P_PERIOD_TO
2027 and glp.period_name = glh.period_name -- Added perf 14050580
2029 AND glh.ledger_id = glg.ledger_id
2030 AND gll.je_header_id = glh.je_header_id
2031 AND gll.effective_date BETWEEN :P_GL_DATE_FROM AND :P_GL_DATE_TO
2032 AND xlk.lookup_type = ''XLA_BALANCE_TYPE''
2033 AND xlk.lookup_code = glh.actual_flag
2034 AND fdu.user_id = glh.created_by
2035 AND glp.period_name = glh.period_name
2036 -- Begin 12609107
2037 AND glp.effective_period_num BETWEEN '||g_start_period_num||' AND ' ||g_end_period_num
2038 || ' AND glp.application_id = 101
2039 AND glp.ledger_id = glh.ledger_id
2040 AND glp.ledger_id = glg.ledger_id
2041 -- End 12609107
2042 AND fsv1.seq_version_id(+) = glh.posting_acct_seq_version_id
2043 AND fsv2.seq_version_id(+) = glh.close_acct_seq_version_id
2044 AND fsq.application_id(+) = 101
2045 -- AND fsq.sequence_id(+) = glh.doc_sequence_id --krsankar - Commented as part of Bug 7153425
2046 AND fsq.doc_sequence_id(+) = glh.doc_sequence_id
2047 AND gbv.budget_version_id(+) = glh.budget_version_id
2048 AND get.encumbrance_type_id(+) = glh.encumbrance_type_id
2049 AND gjct.je_category_name = glh.je_category
2050 AND gjct.LANGUAGE = USERENV(''LANG'')
2051 AND gjst.je_source_name = glh.je_source --bug12408239
2052 AND gjst.LANGUAGE = USERENV(''LANG'') --bug12408239
2053 AND gjb.je_batch_id = glh.je_batch_id
2054 AND glk2.lookup_type = ''BATCH_STATUS''
2055 AND glk2.lookup_code = glh.status
2056 AND gcck.code_combination_id = gll.code_combination_id
2057 AND gdct.conversion_type(+) = glh.currency_conversion_type
2058 AND NVL(glh.je_from_sla_flag,''N'') = ''N'' ';
2059
2060 IF p_party_type_code IN('C','S') THEN
2061 p_gl_party_details :=
2062 ' ,NULL PARTY_NUMBER
2063 ,NULL PARTY_NAME
2064 ,NULL PARTY_TYPE_TAXPAYER_ID
2065 ,NULL PARTY_TAX_REGISTRATION_NUMBER
2066 ,NULL PARTY_SITE_NUMBER
2067 ,NULL PARTY_SITE_NAME
2068 ,NULL PARTY_SITE_TAX_RGSTN_NUMBER ';
2069
2070 ELSE
2071 p_gl_party_details := ',NULL PARTY_INFO ';
2072
2073 END IF;
2074
2075 --
2076 -- Building Legal entity information: Bug 5659083
2077 --
2078
2079 IF p_include_le_info_flag = 'NONE' THEN
2080 p_gl_legal_ent_col := ' ';
2081 p_gl_legal_ent_from := ' ';
2082 p_gl_legal_ent_join := ' ';
2083 ELSE
2084 p_gl_legal_ent_col := C_GL_TRX_LEGAL_ENT_COL;
2085 p_gl_legal_ent_from := ' ';
2086 p_gl_legal_ent_join := ' ';
2087
2088 END IF;
2089
2090 ----------------------------------------------------------------------------
2091 -- build filter condition based on parameters
2092 ----------------------------------------------------------------------------
2093 --
2094 -- <conditions based on creation date>
2095 --
2096 IF p_creation_date_from IS NOT NULL THEN
2097 l_other_param_filter := l_other_param_filter
2098 ||' AND glh.creation_date >= '
2099 ||''''||p_creation_date_from||'''';
2100 END IF;
2101
2102 IF p_creation_date_to IS NOT NULL THEN
2103 l_other_param_filter := l_other_param_filter
2104 || ' AND glh.creation_date <= '
2105 ||''''||p_creation_date_to||'''';
2106 END IF;
2107
2108 --
2109 -- <conditions based on posting status> Modified for bug 5555715
2110 --
2111 IF NVL(p_posting_status_code,'A') = 'Y' THEN
2112 l_other_param_filter := l_other_param_filter
2113 ||' AND glh.status = ''P''';
2114 ELSIF NVL(p_posting_status_code,'A') = 'N' THEN
2115 l_other_param_filter := l_other_param_filter
2116 ||' AND glh.status <> ''P''';
2117 ELSIF NVL(p_posting_status_code,'A') IN ('T','A') THEN -- added T for bug 8250215
2118 NULL; -- No filter
2119 END IF;
2120
2121 --
2122 -- <conditions based on gl je source>
2123 --
2124 IF p_je_source IS NOT NULL THEN
2125 l_other_param_filter := l_other_param_filter
2126 ||' AND glh.je_source = '''
2127 || p_je_source ||'''';
2128 END IF;
2129
2130 -- Bug 5653925
2131 -- <conditions based on accounting sequence name and number range>
2132 --
2133 IF p_acct_sequence_version IS NOT NULL THEN
2134 l_other_param_filter := l_other_param_filter
2135 || ' AND glh.posting_acct_seq_version_id = '
2136 || p_acct_sequence_version ;
2137 END IF;
2138
2139 IF p_acct_sequence_num_from IS NOT NULL THEN
2140 l_other_param_filter := l_other_param_filter
2141 || ' AND glh.posting_acct_seq_value >= '
2142 || p_acct_sequence_num_from ;
2143 END IF;
2144
2145 IF p_acct_sequence_num_to IS NOT NULL THEN
2146 l_other_param_filter := l_other_param_filter
2147 || ' AND glh.posting_acct_seq_value <= '
2148 || p_acct_sequence_num_to ;
2149 END IF;
2150
2151
2152 --
2153 -- <conditions based on reporting sequence name and number range>
2154 --
2155 IF p_rpt_sequence_version IS NOT NULL THEN
2156 l_other_param_filter := l_other_param_filter
2157 || ' AND glh.close_acct_seq_version_id = '
2158 || p_rpt_sequence_version ;
2159 END IF;
2160
2161 IF p_rpt_sequence_num_from IS NOT NULL THEN
2162 l_other_param_filter := l_other_param_filter
2163 || ' AND glh.close_acct_seq_value >= '
2164 || p_rpt_sequence_num_from ;
2165 END IF;
2166
2167 IF p_rpt_sequence_num_to IS NOT NULL THEN
2168 l_other_param_filter := l_other_param_filter
2169 || ' AND glh.close_acct_seq_value <= '
2170 || p_rpt_sequence_num_to ;
2171 END IF;
2172
2173 -- <conditions based on period type> Added for bug 7645837
2174 --
2175 IF p_period_type IS NOT NULL THEN
2176 IF p_period_type = 'A' THEN
2177 l_other_param_filter := l_other_param_filter
2178 || ' AND glp.adjustment_period_flag = ''Y'' ' ;
2179 ELSIF p_period_type = 'N' THEN
2180 l_other_param_filter := l_other_param_filter
2181 || ' AND glp.adjustment_period_flag = ''N'' ' ;
2182 END IF;
2183
2184 END IF;
2185
2186 --
2187
2188
2189 --
2190 -- <conditions based on gl je category> - Bug 5059634
2191 --
2192 IF p_je_category IS NOT NULL THEN
2193 l_other_param_filter := l_other_param_filter
2194 || ' AND glh.je_category = '''
2195 ||p_je_category||'''';
2196 END IF;
2197
2198 --
2199 -- <conditions based on balance type>
2200 --
2201 IF p_balance_type_code IS NOT NULL THEN
2202 l_other_param_filter := l_other_param_filter
2203 || ' AND glh.actual_flag = '''
2204 || p_balance_type_code||'''';
2205 END IF;
2206
2207 --
2208 -- <conditions based on budget_version_id>
2209 --
2210 IF p_budget_version_id IS NOT NULL THEN
2211 l_other_param_filter := l_other_param_filter
2212 ||' AND glh.budget_version_id = '
2213 ||p_budget_version_id;
2214 END IF;
2215
2216 --
2217 -- <conditions based on encumbrance type>
2218 --
2219 IF p_encumbrance_type_id IS NOT NULL THEN
2220 l_other_param_filter := l_other_param_filter
2221 || ' AND glh.encumbrance_type_id = '
2222 || p_encumbrance_type_id;
2223 END IF;
2224
2225 --
2226 -- <conditions based on include zero amount flag>
2227 --
2228 IF p_include_zero_amount_flag = 'N' THEN
2229 --bug#6978940 changed > 0 to <> 0
2230 l_other_param_filter := l_other_param_filter
2231 ||' AND ( NVL(gll.accounted_cr,0) <> 0
2232 OR NVL(gll.accounted_dr,0) <> 0 ) ';
2233 END IF;
2234
2235 --
2236 -- <conditions based on entered currency code> Modified for bug 5721755
2237 --
2238 IF p_entered_currency IS NOT NULL THEN
2239 l_other_param_filter := l_other_param_filter
2240 || ' AND glh.currency_code ='''
2241 || p_entered_currency||'''' ;
2242 ELSE
2243 l_other_param_filter := l_other_param_filter
2244 || ' AND glh.currency_code <> ''STAT''';
2245
2246 END IF;
2247
2248 --
2249 -- <conditions based on accounted amount range>
2250 --
2251 IF p_accounted_amount_from IS NOT NULL THEN
2252 l_other_param_filter := l_other_param_filter
2253 ||' AND (NVL(gll.accounted_cr,0) >= '
2254 || p_accounted_amount_from
2255 ||' OR NVL(gll.accounted_dr,0) >= '
2256 || p_accounted_amount_from ||') ';
2257 END IF;
2258
2259 IF p_accounted_amount_to IS NOT NULL THEN
2260 l_other_param_filter := l_other_param_filter
2261 || ' AND (NVL(gll.accounted_cr,0) <= '
2262 ||p_accounted_amount_to
2263 ||' OR NVL(gll.accounted_dr,0) <= '
2264 ||p_accounted_amount_to ||') ';
2265 END IF;
2266
2267
2268 --
2269 -- <conditions based on side>
2270 --
2271 IF p_side_code = 'DEBIT' THEN
2272 l_other_param_filter := l_other_param_filter
2273 ||' AND ( NVL(gll.accounted_cr,0)
2274 - NVL(gll.accounted_dr,0) < 0) ';
2275 ELSIF p_side_code = 'CREDIT' THEN
2276 l_other_param_filter := l_other_param_filter
2277 ||' AND ( NVL(gll.accounted_cr,0)
2278 - NVL(gll.accounted_dr,0) > 0) ';
2279 END IF;
2280
2281
2282 IF p_account_flexfield_from IS NOT NULL AND
2283 p_account_flexfield_to IS NOT NULL
2284 THEN
2285 l_other_param_filter :=
2286 l_other_param_filter ||' AND '||g_flex_range_where; --bug 12586551
2287 END IF;
2288
2289 --
2290 -- condition based on gl_batch_name. Bug 4725878.
2291 --
2292 IF p_gl_batch_name IS NOT NULL THEN
2293 l_other_param_filter := l_other_param_filter
2294 ||' AND gjb.name = '''
2295 ||p_gl_batch_name||'''';
2296 END IF;
2297
2298 p_gl_where := p_gl_where ||l_other_param_filter;
2299
2300 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2301 trace
2302 (p_msg => 'END of get_gl_sql'
2303 ,p_level => C_LEVEL_PROCEDURE
2304 ,p_module => l_log_module);
2305 END IF;
2306
2307 EXCEPTION
2308 WHEN xla_exceptions_pkg.application_exception THEN
2309 RAISE;
2310 WHEN OTHERS THEN
2311 xla_exceptions_pkg.raise_message
2312 (p_location => 'xla_jelines_rpt_pkg.get_gl_sql');
2313
2314 END get_gl_query;
2315
2316 --=============================================================================
2317 -- *********** public procedures and functions **********
2318 --=============================================================================
2319 --=============================================================================
2320 --
2321 --
2322 --
2323 --
2324 --
2325 --
2326 --
2327 --
2328 --
2329 --
2330 -- Following are public routines
2331 --
2332 -- 1. beforeReport
2333 --
2334 --
2335 --
2336 --
2337 --
2338 --
2339 --
2340 --
2341 --
2342 --
2343 --
2344 --=============================================================================
2345 --=============================================================================
2346 --
2347 --
2348 --
2349 --=============================================================================
2350 FUNCTION beforeReport RETURN BOOLEAN IS
2351
2352 l_object_type VARCHAR2(30);
2353 l_select_str VARCHAR2(4000);
2354 l_from_str VARCHAR2(240);
2355 l_where_str VARCHAR2(4000);
2356 l_count NUMBER;
2357 l_ledger_id NUMBER;
2358 l_coa_id NUMBER;
2359 l_balancing_segment VARCHAR2(80);
2360 l_account_segment VARCHAR2(80);
2361 l_costcenter_segment VARCHAR2(80);
2362 l_management_segment VARCHAR2(80);
2363 l_intercompany_segment VARCHAR2(80);
2364 l_alias_balancing_segment VARCHAR2(80);
2365 l_alias_account_segment VARCHAR2(80);
2366 l_alias_costcenter_segment VARCHAR2(80);
2367 l_alias_management_segment VARCHAR2(80);
2368 l_alias_intercompany_segment VARCHAR2(80);
2369 l_qualifier_segment VARCHAR2(4000) := ' ';
2370 l_seg_desc_column VARCHAR2(2000) := ' ';
2371 l_seg_desc_from VARCHAR2(1000) := ' ';
2372 l_seg_desc_join VARCHAR2(1000) := ' ';
2373 l_log_module VARCHAR2(240);
2374 --l_flex_range_where VARCHAR2(32000); bug 12586551
2375 l_gl_columns VARCHAR2(2000);
2376 l_gl_view VARCHAR2(4000);
2380 l_user_trx_value VARCHAR2(2000) := ' ';
2377 l_gl_join VARCHAR2(2000);
2378 l_fetch_from_sla_flag VARCHAR2(1);
2379 l_fetch_from_gl_flag VARCHAR2(1);
2381 l_period_year NUMBER;
2382 l_temp NUMBER;
2383 l_fnd_flex_hint VARCHAR2(200);
2384 i NUMBER;
2385
2386 l_ledgers VARCHAR2(1000);
2387
2388 --bug#7834671
2389 l_display_flag fnd_id_flex_segments.display_flag%TYPE;
2390
2391 CURSOR C_SEG_DISP_REQ_CHECK(
2392 p_application_id INTEGER,
2393 p_id_flex_code VARCHAR2,
2394 p_id_flex_num INTEGER,
2395 p_segment_code VARCHAR2
2396 )
2397 IS
2398 SELECT display_flag
2399 FROM fnd_id_flex_segments fid
2400 WHERE application_id = p_application_id
2401 AND id_flex_code = p_id_flex_code
2402 AND id_flex_num = p_id_flex_num
2403 AND application_column_name = p_segment_code;
2404 --bug#7834671
2405
2406
2407
2408 BEGIN
2409 --
2410 -- default values
2411 --
2412 p_include_zero_amount_flag := NVL(p_include_zero_amount_flag,'N');
2413 p_include_user_trx_id_flag := NVL(p_include_user_trx_id_flag,'N');
2417
2414 p_include_tax_details_flag := NVL(p_include_tax_details_flag,'N');
2415 p_include_le_info_flag := NVL(p_include_le_info_flag,'NONE');
2416 p_ytd_carriedfwd_flag := NVL(p_ytd_carriedfwd_flag,'N');
2418 --Added for bug 7159772
2419
2420 IF p_order_by IS NOT NULL THEN
2421 p_order_by_clause := ' order by '||p_order_by;
2422 END IF;
2423
2424 --Added for bug 7159772
2425
2426 IF p_je_source = '#ALL#' THEN
2427 p_je_source := NULL;
2428 END IF;
2429
2430 BEGIN
2431 SELECT application_id
2432 INTO g_je_source_application_id
2433 FROM xla_subledgers
2434 WHERE je_source_name = p_je_source;
2435 EXCEPTION
2436 WHEN NO_DATA_FOUND THEN
2437 g_je_source_application_id := NULL;
2438 END;
2439 --
2440 -- following will set the right transaction security
2441 --
2442 -- if g_je_source_application_id is NULL
2443 -- set security context for 602
2444 -- else g_je_source_application_id is an SLA subledger
2445 -- set security context for the g_je_source_application_id
2446 -- end if
2447
2448 IF g_je_source_application_id IS NULL THEN
2449 xla_security_pkg.set_security_context(602);
2450 ELSE
2451 xla_security_pkg.set_security_context(g_je_source_application_id);
2452 END IF;
2453
2454 --
2455 -- Logic to restrict SLA or GL query to get executed unnecessarly
2456 --
2457 -- if g_je_source_application_id is NULL
2458 -- if je source is NULL
2459 -- fetch from SLA and GL for all applciations and je_sources
2460 -- else je source is not NULL
2461 -- fetch from GL for that je_source
2462 -- end if
2463 -- else g_je_source_application_id is an SLA subledger
2464 -- fetch from SLA and GL (Non-upgraded entries) for that application
2465 -- end if
2466
2467 l_fetch_from_sla_flag := 'N';
2468 l_fetch_from_gl_flag := 'N';
2469
2470 IF g_je_source_application_id IS NULL THEN
2471 IF p_je_source IS NULL THEN
2472 l_fetch_from_sla_flag := 'Y';
2473 l_fetch_from_gl_flag := 'Y';
2474 ELSE
2475 l_fetch_from_sla_flag := 'N';
2476 l_fetch_from_gl_flag := 'Y';
2477 END IF;
2478 ELSE
2479 l_fetch_from_sla_flag := 'Y';
2480 l_fetch_from_gl_flag := 'Y';
2481 END IF;
2482
2483 IF ( (NVL(p_je_status_code,'F') <> 'F') OR (NVL(p_posting_status_code,'A') = 'X') ) -- added X for bug 8250215
2484 THEN
2485 l_fetch_from_gl_flag := 'N';
2486 END IF;
2487
2488 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2489 trace('l_fetch_from_sla_flag ='||l_fetch_from_sla_flag
2490 ,C_LEVEL_STATEMENT
2491 ,l_log_module);
2492 END IF;
2493
2494 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2495 trace('l_fetch_from_gl_flag ='|| l_fetch_from_gl_flag
2496 ,C_LEVEL_STATEMENT
2497 ,l_log_module);
2498 END IF;
2499
2500 --
2501 -- Identifying ledger as Ledger or Ledger Set
2502 --
2503 SELECT object_type_code
2504 INTO l_object_type
2505 FROM gl_ledgers
2506 WHERE ledger_id = p_ledger_id;
2507
2508 IF l_object_type = 'S' THEN
2509 l_ledgers := '(SELECT ledger_id '||
2510 'FROM gl_ledger_set_assignments '||
2514 INTO l_ledger_id
2511 'WHERE ledger_set_id = :P_LEDGER_ID)';
2512
2513 SELECT ledger_id
2515 FROM gl_ledger_set_assignments
2516 WHERE ledger_set_id = p_ledger_id
2517 AND ROWNUM = 1;
2518
2519 else
2520 l_ledgers := '(:P_LEDGER_ID)';
2521
2522 l_ledger_id := p_ledger_id;
2523
2524 end if;
2525
2526 -----------------------------------------------------------------------------------
2527 -- Calculating Period Year Start and End Date. Bug 4755531- Use application_id 101
2528 -- Getting effective period number for the from and to periods. Bug 12609107
2529 -----------------------------------------------------------------------------------
2530 SELECT period_year, effective_period_num
2531 INTO l_period_year, g_start_period_num
2532 FROM gl_period_statuses
2533 WHERE application_id = 101
2534 AND set_of_books_id = l_ledger_id
2535 AND period_name = p_period_from;
2536
2537 SELECT effective_period_num
2538 INTO g_end_period_num
2539 FROM gl_period_statuses
2540 WHERE application_id = 101
2541 AND ledger_id = l_ledger_id
2542 AND period_name = p_period_to;
2543
2544 SELECT TO_CHAR(MIN(start_date),'YYYY-MM-DD')
2545 ,TO_CHAR(MAX(end_date),'YYYY-MM-DD')
2546 INTO g_period_year_start_date
2547 ,g_period_year_end_date
2548 FROM gl_period_statuses
2549 WHERE application_id = 101
2550 AND set_of_books_id = l_ledger_id
2551 AND period_year = l_period_year
2552 AND adjustment_period_flag = 'N';
2553
2554 -----------------------------------------------------------------------------------
2555 -- Building the where clause based on the flex range. Bug 12586551.
2556 -----------------------------------------------------------------------------------
2557 IF p_account_flexfield_from IS NOT NULL AND
2558 p_account_flexfield_to IS NOT NULL
2559 THEN
2560 g_flex_range_where :=
2561 get_flex_range_where
2562 (p_coa_id => p_coa_id
2563 ,p_acct_flexfield_from => p_account_flexfield_from
2564 ,p_acct_flexfield_to => p_account_flexfield_to );
2565 END IF;
2566
2567 -----------------------------------------------------------------------------
2568 -- Based on P_INCLUDE_TAX_DETAIL building query to fetch tax info
2569 -----------------------------------------------------------------------------
2570 IF p_include_tax_details_flag = 'Y' THEN
2571 p_tax_query := C_TAX_QUERY;
2572 ELSE
2573 p_tax_query := C_TAX_NULL_QUERY;
2574 END IF;
2575
2576 --bug5702057
2577 IF p_legal_audit_flag = 'Y' THEN
2578 p_created_query := C_CREATED_QUERY;
2579 p_posted_query := C_POSTED_QUERY;
2580 p_approved_query := C_APPROVED_QUERY;
2581
2582 ELSE
2583 p_created_query := C_CREATED_NULL_QUERY;
2584 p_posted_query := C_POSTED_NULL_QUERY;
2585 p_approved_query := C_APPROVED_NULL_QUERY;
2586
2587 END IF;
2588 p_commercial_query := C_COMMERCIAL_QUERY;
2589 p_vat_registration_query := C_VAT_REGISTRATION_QUERY;
2590
2591
2592 ------------------------------------------------------------------------------
2593 -- Based on P_YTD_CARRIEDFWD_FLAG building query to fetch Year-to-Date Carried
2594 -- Forward Debit and Credit Amounts
2595 ------------------------------------------------------------------------------
2596 IF p_ytd_carriedfwd_flag = 'Y' THEN
2597 p_ytd_carriedfwd := C_YTD_ACTUAL_CARRIEDFWD;
2598 IF p_account_flexfield_from IS NOT NULL AND --bug 12586551
2599 p_account_flexfield_to IS NOT NULL THEN
2600 p_ytd_carriedfwd := p_ytd_carriedfwd ||' AND '||g_flex_range_where;
2601 END IF;
2602 ELSE
2606 ------------------------------------------------------------------------------
2603 p_ytd_carriedfwd := C_YTD_ZERO_CARRIEDFWD;
2604 END IF;
2605
2607 -- Bug 8683445 Based on p_custom_query_flag building Report/Application
2608 -- specific Custom Query
2609 ------------------------------------------------------------------------------
2610 IF p_custom_query_flag IS NOT NULL THEN
2611 xla_rpt_util_qry_pkg.get_custom_query
2612 (p_application_id => g_je_source_application_id,
2613 p_custom_query_flag => p_custom_query_flag,
2614 p_custom_header_query => p_custom_header_query,
2615 p_custom_line_query => p_custom_line_query);
2616 END IF;
2617
2618 IF p_custom_header_query IS NULL THEN
2619 p_custom_header_query := 'SELECT NULL FROM DUAL WHERE 1=2';
2620 END IF;
2621
2622 IF p_custom_line_query IS NULL THEN
2623 p_custom_line_query := 'SELECT NULL FROM DUAL WHERE 1=2';
2624 END IF;
2625
2626 --
2627 -- Qualified segments
2628 --
2629 l_qualifier_segment := C_QUALIFIED_SEGMENT;
2630 l_coa_id := p_coa_id;
2631
2632 ----------------------------------------------------------------------------
2633 -- get qualifier segments for the COA
2634 ----------------------------------------------------------------------------
2635 xla_report_utility_pkg.get_acct_qualifier_segs
2636 (p_coa_id => l_coa_id
2637 ,p_balance_segment => l_balancing_segment
2638 ,p_account_segment => l_account_segment
2639 ,p_cost_center_segment => l_costcenter_segment
2640 ,p_management_segment => l_management_segment
2641 ,p_intercompany_segment => l_intercompany_segment);
2642
2643 --
2644 -- attach table alias to the column names
2645 --
2646 IF l_balancing_segment = 'NULL' THEN
2647 l_alias_balancing_segment := 'NULL';
2648 ELSE
2649 l_alias_balancing_segment := 'gcck.'||l_balancing_segment;
2650 END IF;
2651
2652 IF l_account_segment = 'NULL' THEN
2653 l_alias_account_segment := 'NULL';
2654 ELSE
2655 l_alias_account_segment := 'gcck.'||l_account_segment;
2656 END IF;
2657
2658 IF l_costcenter_segment = 'NULL' THEN
2659 l_alias_costcenter_segment := 'NULL';
2660 ELSE
2661 l_alias_costcenter_segment := 'gcck.'||l_costcenter_segment;
2662 END IF;
2663
2664 IF l_management_segment = 'NULL' THEN
2665 l_alias_management_segment := 'NULL';
2666 ELSE
2667 l_alias_management_segment := 'gcck.'||l_management_segment;
2668 END IF;
2669
2670 IF l_intercompany_segment = 'NULL' THEN
2671 l_alias_intercompany_segment := 'NULL';
2672 ELSE
2673 l_alias_intercompany_segment := 'gcck.'||l_intercompany_segment;
2674 END IF;
2675
2676 --
2677 -- replace placeholders for the qualified segemnts
2678 --
2679 l_qualifier_segment := REPLACE(l_qualifier_segment
2680 ,'$alias_balancing_segment$'
2681 ,l_alias_balancing_segment);
2682
2683 l_qualifier_segment := REPLACE(l_qualifier_segment
2684 ,'$alias_account_segment$'
2685 ,l_alias_account_segment);
2686
2687 l_qualifier_segment := REPLACE(l_qualifier_segment
2688 ,'$alias_costcenter_segment$'
2689 ,l_alias_costcenter_segment);
2690
2691 l_qualifier_segment := REPLACE(l_qualifier_segment
2692 ,'$alias_management_segment$'
2693 ,l_alias_management_segment);
2694
2695 l_qualifier_segment := REPLACE(l_qualifier_segment
2696 ,'$alias_intercompany_segment$'
2697 ,l_alias_intercompany_segment);
2698
2699
2700 xla_report_utility_pkg.get_segment_info
2701 (p_coa_id => l_coa_id
2702 ,p_balancing_segment => l_balancing_segment
2703 ,p_account_segment => l_account_segment
2704 ,p_costcenter_segment => l_costcenter_segment
2705 ,p_management_segment => l_management_segment
2706 ,p_intercompany_segment => l_intercompany_segment
2707 ,p_alias_balancing_segment => l_alias_balancing_segment
2708 ,p_alias_account_segment => l_alias_account_segment
2709 ,p_alias_costcenter_segment => l_alias_costcenter_segment
2710 ,p_alias_management_segment => l_alias_management_segment
2711 ,p_alias_intercompany_segment=> l_alias_intercompany_segment
2712 ,p_seg_desc_column => l_seg_desc_column
2713 ,p_seg_desc_from => l_seg_desc_from
2714 ,p_seg_desc_join => l_seg_desc_join
2715 ,p_hint => l_fnd_flex_hint
2716 );
2717
2718
2719 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2720 trace
2721 (p_msg => 'seg_desc_column ='||l_seg_desc_column
2722 ,p_level => C_LEVEL_STATEMENT
2723 ,p_module=> l_log_module);
2724 trace
2725 (p_msg => 'seg_desc_from ='||l_seg_desc_from
2726 ,p_level => C_LEVEL_STATEMENT
2727 ,p_module=> l_log_module);
2728 trace
2729 (p_msg => 'seg_desc_join ='||l_seg_desc_join
2730 ,p_level => C_LEVEL_STATEMENT
2731 ,p_module=> l_log_module);
2732 END IF;
2733 --
2734 -- REPLACE placeholders for the qualified segemnts
2735 --
2736 l_qualifier_segment := REPLACE(l_qualifier_segment
2737 ,'$seg_desc_column$'
2738 ,l_seg_desc_column);
2739 --
2740 -- Legal Entity Information
2741 --
2745 --
2742
2743 --
2744 -- REPLACE placeholders for Legal entity information
2746 IF p_include_le_info_flag = 'LEGAL_ENTITY' THEN
2747 p_le_col := C_LEGAL_ENT_COL;
2748 p_le_from := C_LEGAL_ENT_FROM;
2749 p_le_join := C_LEGAL_ENT_JOIN;
2750
2751 p_le_join := REPLACE(p_le_join ,'$leg_seg_val$', l_balancing_segment);
2752
2753 IF p_legal_entity_id IS NOT NULL THEN
2754 p_le_join := p_le_join ||
2755 ' AND gle.legal_entity_id(+) = :p_legal_entity_id ';
2756 END IF;
2757 ELSIF p_include_le_info_flag = 'ESTABLISHMENT' THEN
2758 p_le_col := C_ESTBLISHMENT_COL;
2759 p_le_from := C_ESTABLISHMENT_FROM;
2760 p_le_join := C_ESTABLISHMENT_JOIN;
2761
2762 p_le_join := REPLACE(p_le_join ,'$leg_seg_val$', l_balancing_segment);
2763
2764 IF p_legal_entity_id IS NOT NULL THEN
2765 p_le_join := p_le_join ||
2766 ' AND glv.legal_entity_id = :p_legal_entity_id ';
2767 END IF;
2768 ELSE -- p_include_le_info_flag = 'NONE' THEN
2769 p_le_col := C_LE_NULL_COL;
2770 p_le_from := ' ';
2771 p_le_join := ' ';
2772
2773 IF p_legal_entity_id IS NOT NULL THEN
2774 p_le_from := ' ,gl_ledger_le_bsv_specific_v gle ';
2775 p_le_join := ' AND gle.ledger_id = TABLE1.LEDGER_ID '||
2776 ' AND gle.segment_value = TABLE1.$leg_seg_val$ '||
2777 ' AND gle.legal_entity_id(+) = :p_legal_entity_id ';
2778
2779 p_le_join := REPLACE(p_le_join,'$leg_seg_val$',l_balancing_segment);
2780 END IF;
2781 END IF;
2782
2783
2784 --===========================================================================
2785 -- Building filter for outer query based on user trx ids
2786 --===========================================================================
2787 IF p_transaction_view IS NOT NULL THEN
2788 FOR c1 IN (select user_sequence,column_name from xla_event_mappings_b
2789 where application_id = g_je_source_application_id
2790 and event_class_code in
2791 (select event_class_code
2792 from xla_event_class_attrs
2793 where application_id = g_je_source_application_id
2794 and reporting_view_name = p_transaction_view
2795 and rownum = 1
2796 )
2797 and column_name in (p_user_trx_id_column_1
2798 ,p_user_trx_id_column_2
2799 ,p_user_trx_id_column_3
2800 ,p_user_trx_id_column_4
2801 ,p_user_trx_id_column_5)
2802 order by user_sequence
2803 )
2804 LOOP
2805 CASE c1.column_name
2806 WHEN p_user_trx_id_column_1 THEN
2807 l_user_trx_value := p_user_trx_id_value_1;
2808 WHEN p_user_trx_id_column_2 THEN
2809 l_user_trx_value := p_user_trx_id_value_2;
2810 WHEN p_user_trx_id_column_3 THEN
2811 l_user_trx_value := p_user_trx_id_value_3;
2812 WHEN p_user_trx_id_column_4 THEN
2813 l_user_trx_value := p_user_trx_id_value_4;
2814 WHEN p_user_trx_id_column_5 THEN
2815 l_user_trx_value := p_user_trx_id_value_5;
2816 END CASE;
2817
2818 p_trx_id_filter :=
2819 p_trx_id_filter ||
2820 /* ' AND TABLE1.USER_TRX_IDENTIFIER_VALUE_'||c1.user_sequence||' = ' || ''' || l_user_trx_value || '''; */
2821 ' AND substr(USERIDS,instr(USERIDS,''|'',1,5)+1,(instr(USERIDS,''|'',1,6)-1-instr(USERIDS,''|'',1,5)))'||' = ' || ''''|| l_user_trx_value || '''';
2822 -- bug#6802751
2823
2824 END LOOP;
2825 END IF;
2826
2827 --===========================================================================
2828 -- Building party string for the outer query.
2829 --===========================================================================
2830 IF p_party_type_code IN('C','S') THEN
2831 NULL;
2832 ELSE
2833 p_party_details_col :=
2834 ',SUBSTR(PARTY_INFO,1,INSTR(PARTY_INFO,''|'',1,1)-1 ) PARTY_NUMBER
2835 ,SUBSTR(PARTY_INFO,INSTR(PARTY_INFO,''|'',1,1)+1
2836 ,(INSTR(PARTY_INFO,''|'',1,2)-1-INSTR(PARTY_INFO,''|'',1,1))) PARTY_NAME
2837 ,SUBSTR(PARTY_INFO,INSTR(PARTY_INFO,''|'',1,2)+1
2838 ,(INSTR(PARTY_INFO,''|'',1,3)-1-INSTR(PARTY_INFO,''|'',1,2))) PARTY_TYPE_TAXPAYER_ID
2839 ,SUBSTR(PARTY_INFO,INSTR(PARTY_INFO,''|'',1,3)+1
2840 ,(INSTR(PARTY_INFO,''|'',1,4)-1-INSTR(PARTY_INFO,''|'',1,3))) PARTY_TAX_REGISTRATION_NUMBER
2841 ,SUBSTR(PARTY_INFO,INSTR(PARTY_INFO,''|'',1,4)+1
2842 ,(INSTR(PARTY_INFO,''|'',1,5)-1-INSTR(PARTY_INFO,''|'',1,4))) PARTY_SITE_NUMBER
2843 ,SUBSTR(PARTY_INFO,INSTR(PARTY_INFO,''|'',1,5)+1
2844 ,(INSTR(PARTY_INFO,''|'',1,6)-1-INSTR(PARTY_INFO,''|'',1,5))) PARTY_SITE_NAME
2845 ,SUBSTR(PARTY_INFO,INSTR(PARTY_INFO,''|'',1,6)+1
2846 ,(LENGTH(PARTY_INFO)- INSTR(PARTY_INFO,''|'',1,6))) PARTY_SITE_TAX_RGSTN_NUMBER ';
2847 END IF;
2848
2849
2850 p_main_col_start :=
2851 ' /*+ opt_param(''_optimizer_cost_based_transformation'',''off'') */
2852 TABLE1.GL_DATE GL_DATE
2853 ,TABLE1.CREATED_BY CREATED_BY
2854 ,TABLE1.CREATION_DATE CREATION_DATE
2855 ,TABLE1.LAST_UPDATE_DATE LAST_UPDATE_DATE
2856 ,TABLE1.GL_TRANSFER_DATE GL_TRANSFER_DATE
2857 ,TABLE1.REFERENCE_DATE REFERENCE_DATE
2858 ,TABLE1.COMPLETED_DATE COMPLETED_DATE
2859 ,TABLE1.EXTERNAL_REFERENCE EXTERNAL_REFERENCE
2860 ,TABLE1.REFERENCE_1 REFERENCE_1
2861 ,TABLE1.REFERENCE_4 REFERENCE_4
2862 ,TABLE1.PERIOD_YEAR PERIOD_YEAR
2863 ,TABLE1.PERIOD_YEAR_START_DATE PERIOD_YEAR_START_DATE
2864 ,TABLE1.PERIOD_YEAR_END_DATE PERIOD_YEAR_END_DATE
2865 ,TABLE1.PERIOD_NUMBER PERIOD_NUMBER
2866 ,TABLE1.PERIOD_NAME PERIOD_NAME
2867 ,TABLE1.PERIOD_START_DATE PERIOD_START_DATE
2868 ,TABLE1.PERIOD_END_DATE PERIOD_END_DATE
2869 ,TABLE1.TRANSACTION_NUMBER TRANSACTION_NUMBER
2870 ,TABLE1.TRANSACTION_DATE TRANSACTION_DATE
2871 ,TABLE1.ACCOUNTING_SEQUENCE_NAME ACCOUNTING_SEQUENCE_NAME
2872 ,TABLE1.ACCOUNTING_SEQUENCE_VERSION ACCOUNTING_SEQUENCE_VERSION
2873 ,TABLE1.ACCOUNTING_SEQUENCE_NUMBER ACCOUNTING_SEQUENCE_NUMBER
2874 ,TABLE1.REPORTING_SEQUENCE_NAME REPORTING_SEQUENCE_NAME
2875 ,TABLE1.REPORTING_SEQUENCE_VERSION REPORTING_SEQUENCE_VERSION
2876 ,TABLE1.REPORTING_SEQUENCE_NUMBER REPORTING_SEQUENCE_NUMBER
2877 ,TABLE1.DOCUMENT_CATEGORY DOCUMENT_CATEGORY
2878 ,TABLE1.DOCUMENT_SEQUENCE_NAME DOCUMENT_SEQUENCE_NAME
2879 ,TABLE1.DOCUMENT_SEQUENCE_NUMBER DOCUMENT_SEQUENCE_NUMBER
2880 ,TABLE1.APPLICATION_ID APPLICATION_ID
2881 ,TABLE1.APPLICATION_NAME APPLICATION_NAME
2882 ,TABLE1.LEDGER_ID LEDGER_ID
2883 ,TABLE1.LEDGER_SHORT_NAME LEDGER_SHORT_NAME
2884 ,TABLE1.LEDGER_DESCRIPTION LEDGER_DESCRIPTION
2885 ,TABLE1.LEDGER_NAME LEDGER_NAME
2886 ,TABLE1.LEDGER_CURRENCY LEDGER_CURRENCY
2887 ,TABLE1.HEADER_ID HEADER_ID
2888 ,TABLE1.HEADER_DESCRIPTION HEADER_DESCRIPTION
2889 ,TABLE1.JOURNAL_ENTRY_STATUS JOURNAL_ENTRY_STATUS
2890 ,TABLE1.TRANSFER_TO_GL_STATUS TRANSFER_TO_GL_STATUS
2891 ,TABLE1.BALANCE_TYPE_CODE BALANCE_TYPE_CODE
2892 ,TABLE1.BALANCE_TYPE BALANCE_TYPE
2893 ,TABLE1.BUDGET_NAME BUDGET_NAME
2894 ,TABLE1.ENCUMBRANCE_TYPE ENCUMBRANCE_TYPE
2895 ,TABLE1.FUND_STATUS FUND_STATUS
2896 ,TABLE1.JE_CATEGORY_NAME JE_CATEGORY_NAME
2897 ,TABLE1.JE_SOURCE_NAME JE_SOURCE_NAME
2898 ,TABLE1.GL_BATCH_NAME GL_BATCH_NAME
2899 ,TABLE1.GL_DEFAULT_EFFECTIVE_DATE GL_DEFAULT_EFFECTIVE_DATE
2900 ,TABLE1.GL_BATCH_STATUS GL_BATCH_STATUS
2901 ,TABLE1.POSTED_DATE POSTED_DATE
2902 ,TABLE1.GL_JE_NAME GL_JE_NAME
2903 ,TABLE1.GL_DOC_SEQUENCE_NAME GL_DOC_SEQUENCE_NAME
2904 ,TABLE1.GL_DOC_SEQUENCE_VALUE GL_DOC_SEQUENCE_VALUE
2905 ,TABLE1.GL_LINE_NUMBER GL_LINE_NUMBER
2906 ,TABLE1.EVENT_ID EVENT_ID
2907 ,TABLE1.EVENT_DATE EVENT_DATE
2908 ,TABLE1.EVENT_NUMBER EVENT_NUMBER
2909 ,TABLE1.EVENT_CLASS_CODE EVENT_CLASS_CODE
2910 ,TABLE1.EVENT_CLASS_NAME EVENT_CLASS_NAME
2911 ,TABLE1.EVENT_TYPE_CODE EVENT_TYPE_CODE
2912 ,TABLE1.EVENT_TYPE_NAME EVENT_TYPE_NAME
2913 ,TABLE1.LINE_NUMBER LINE_NUMBER
2914 ,TABLE1.ACCOUNTING_CLASS_CODE ACCOUNTING_CLASS_CODE
2915 ,TABLE1.ACCOUNTING_CLASS_NAME ACCOUNTING_CLASS_NAME
2916 ,TABLE1.LINE_DESCRIPTION LINE_DESCRIPTION
2917 ,TABLE1.CODE_COMBINATION_ID CODE_COMBINATION_ID
2918 ,TABLE1.ACCOUNTING_CODE_COMBINATION ACCOUNTING_CODE_COMBINATION
2919 ,TABLE1.CODE_COMBINATION_DESCRIPTION CODE_COMBINATION_DESCRIPTION
2920 ,TABLE1.CONTROL_ACCOUNT_FLAG CONTROL_ACCOUNT_FLAG
2921 ,TABLE1.ENTERED_CURRENCY ENTERED_CURRENCY
2922 ,TABLE1.CONVERSION_RATE CONVERSION_RATE
2923 ,TABLE1.CONVERSION_RATE_DATE CONVERSION_RATE_DATE
2924 ,TABLE1.CONVERSION_RATE_TYPE_CODE CONVERSION_RATE_TYPE_CODE
2925 ,TABLE1.CONVERSION_RATE_TYPE CONVERSION_RATE_TYPE
2926 ,TABLE1.ENTERED_DR ENTERED_DR
2927 ,TABLE1.ENTERED_CR ENTERED_CR
2928 ,TABLE1.UNROUNDED_ACCOUNTED_DR UNROUNDED_ACCOUNTED_DR
2929 ,TABLE1.UNROUNDED_ACCOUNTED_CR UNROUNDED_ACCOUNTED_CR
2930 ,TABLE1.ACCOUNTED_DR ACCOUNTED_DR
2931 ,TABLE1.ACCOUNTED_CR ACCOUNTED_CR
2932 ,TABLE1.STATISTICAL_AMOUNT STATISTICAL_AMOUNT
2933 ,TABLE1.RECONCILIATION_REFERENCE RECONCILIATION_REFERENCE
2934 ,TABLE1.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
2935 ,TABLE1.ATTRIBUTE1 ATTRIBUTE1
2936 ,TABLE1.ATTRIBUTE2 ATTRIBUTE2
2937 ,TABLE1.ATTRIBUTE3 ATTRIBUTE3
2938 ,TABLE1.ATTRIBUTE4 ATTRIBUTE4
2939 ,TABLE1.ATTRIBUTE5 ATTRIBUTE5
2940 ,TABLE1.ATTRIBUTE6 ATTRIBUTE6
2941 ,TABLE1.ATTRIBUTE7 ATTRIBUTE7
2942 ,TABLE1.ATTRIBUTE8 ATTRIBUTE8
2943 ,TABLE1.ATTRIBUTE9 ATTRIBUTE9
2944 ,TABLE1.ATTRIBUTE10 ATTRIBUTE10
2945 ,TABLE1.PARTY_TYPE_CODE PARTY_TYPE_CODE
2946 ,TABLE1.PARTY_TYPE PARTY_TYPE';
2947
2948
2949 p_segment_col := ',TABLE1.BALANCING_SEGMENT BALANCING_SEGMENT
2950 ,TABLE1.NATURAL_ACCOUNT_SEGMENT NATURAL_ACCOUNT_SEGMENT
2951 ,TABLE1.COST_CENTER_SEGMENT COST_CENTER_SEGMENT
2952 ,TABLE1.MANAGEMENT_SEGMENT MANAGEMENT_SEGMENT
2953 ,TABLE1.INTERCOMPANY_SEGMENT INTERCOMPANY_SEGMENT
2954 ,TABLE1.BALANCING_SEGMENT_DESC BALANCING_SEGMENT_DESC
2955 ,TABLE1.NATURAL_ACCOUNT_DESC NATURAL_ACCOUNT_DESC
2956 ,TABLE1.COST_CENTER_DESC COST_CENTER_DESC
2957 ,TABLE1.MANAGEMENT_SEGMENT_DESC MANAGEMENT_SEGMENT_DESC
2958 ,TABLE1.INTERCOMPANY_SEGMENT_DESC INTERCOMPANY_SEGMENT_DESC
2959 ,TABLE1.SEGMENT1 SEGMENT1
2960 ,TABLE1.SEGMENT2 SEGMENT2
2961 ,TABLE1.SEGMENT3 SEGMENT3
2962 ,TABLE1.SEGMENT4 SEGMENT4
2966 ,TABLE1.SEGMENT8 SEGMENT8
2963 ,TABLE1.SEGMENT5 SEGMENT5
2964 ,TABLE1.SEGMENT6 SEGMENT6
2965 ,TABLE1.SEGMENT7 SEGMENT7
2967 ,TABLE1.SEGMENT9 SEGMENT9
2968 ,TABLE1.SEGMENT10 SEGMENT10
2969 ,TABLE1.SEGMENT11 SEGMENT11
2970 ,TABLE1.SEGMENT12 SEGMENT12
2971 ,TABLE1.SEGMENT13 SEGMENT13
2972 ,TABLE1.SEGMENT14 SEGMENT14
2973 ,TABLE1.SEGMENT15 SEGMENT15
2974 ,TABLE1.SEGMENT16 SEGMENT16
2975 ,TABLE1.SEGMENT17 SEGMENT17
2976 ,TABLE1.SEGMENT18 SEGMENT18
2977 ,TABLE1.SEGMENT19 SEGMENT19
2978 ,TABLE1.SEGMENT20 SEGMENT20
2979 ,TABLE1.SEGMENT21 SEGMENT21
2980 ,TABLE1.SEGMENT22 SEGMENT22
2981 ,TABLE1.SEGMENT23 SEGMENT23
2982 ,TABLE1.SEGMENT24 SEGMENT24
2983 ,TABLE1.SEGMENT25 SEGMENT25
2984 ,TABLE1.SEGMENT26 SEGMENT26
2985 ,TABLE1.SEGMENT27 SEGMENT27
2986 ,TABLE1.SEGMENT28 SEGMENT28
2987 ,TABLE1.SEGMENT29 SEGMENT29
2988 ,TABLE1.SEGMENT30 SEGMENT30';
2989
2990 p_uti_col := ',substr(USERIDS,1,instr(USERIDS,''|'',1,1)-1) USER_TRX_IDENTIFIER_NAME_1
2991 ,substr(USERIDS,instr(USERIDS,''|'',1,1)+1,(instr(USERIDS,''|'',1,2)-1-instr(USERIDS,''|'',1,1))) USER_TRX_IDENTIFIER_VALUE_1
2992 ,substr(USERIDS,instr(USERIDS,''|'',1,2)+1,(instr(USERIDS,''|'',1,3)-1-instr(USERIDS,''|'',1,2))) USER_TRX_IDENTIFIER_NAME_2
2993 ,substr(USERIDS,instr(USERIDS,''|'',1,3)+1,(instr(USERIDS,''|'',1,4)-1-instr(USERIDS,''|'',1,3))) USER_TRX_IDENTIFIER_VALUE_2
2994 ,substr(USERIDS,instr(USERIDS,''|'',1,4)+1,(instr(USERIDS,''|'',1,5)-1-instr(USERIDS,''|'',1,4))) USER_TRX_IDENTIFIER_NAME_3
2995 ,substr(USERIDS,instr(USERIDS,''|'',1,5)+1,(instr(USERIDS,''|'',1,6)-1-instr(USERIDS,''|'',1,5))) USER_TRX_IDENTIFIER_VALUE_3
2996 ,substr(USERIDS,instr(USERIDS,''|'',1,6)+1,(instr(USERIDS,''|'',1,7)-1-instr(USERIDS,''|'',1,6))) USER_TRX_IDENTIFIER_NAME_4
2997 ,substr(USERIDS,instr(USERIDS,''|'',1,7)+1,(instr(USERIDS,''|'',1,8)-1-instr(USERIDS,''|'',1,7))) USER_TRX_IDENTIFIER_VALUE_4
2998 ,substr(USERIDS,instr(USERIDS,''|'',1,8)+1,(instr(USERIDS,''|'',1,9)-1-instr(USERIDS,''|'',1,8))) USER_TRX_IDENTIFIER_NAME_5
2999 ,substr(USERIDS,instr(USERIDS,''|'',1,9)+1,(instr(USERIDS,''|'',1,10)-1-instr(USERIDS,''|'',1,9))) USER_TRX_IDENTIFIER_VALUE_5
3000 ,substr(USERIDS,instr(USERIDS,''|'',1,10)+1,(instr(USERIDS,''|'',1,11)-1-instr(USERIDS,''|'',1,10))) USER_TRX_IDENTIFIER_NAME_6
3001 ,substr(USERIDS,instr(USERIDS,''|'',1,11)+1,(instr(USERIDS,''|'',1,12)-1-instr(USERIDS,''|'',1,11))) USER_TRX_IDENTIFIER_VALUE_6
3002 ,substr(USERIDS,instr(USERIDS,''|'',1,12)+1,(instr(USERIDS,''|'',1,13)-1-instr(USERIDS,''|'',1,12))) USER_TRX_IDENTIFIER_NAME_7
3003 ,substr(USERIDS,instr(USERIDS,''|'',1,13)+1,(instr(USERIDS,''|'',1,14)-1-instr(USERIDS,''|'',1,13))) USER_TRX_IDENTIFIER_VALUE_7
3004 ,substr(USERIDS,instr(USERIDS,''|'',1,14)+1,(instr(USERIDS,''|'',1,15)-1-instr(USERIDS,''|'',1,14))) USER_TRX_IDENTIFIER_NAME_8
3005 ,substr(USERIDS,instr(USERIDS,''|'',1,15)+1,(instr(USERIDS,''|'',1,16)-1-instr(USERIDS,''|'',1,15))) USER_TRX_IDENTIFIER_VALUE_8
3006 ,substr(USERIDS,instr(USERIDS,''|'',1,16)+1,(instr(USERIDS,''|'',1,17)-1-instr(USERIDS,''|'',1,16))) USER_TRX_IDENTIFIER_NAME_9
3007 ,substr(USERIDS,instr(USERIDS,''|'',1,17)+1,(instr(USERIDS,''|'',1,18)-1-instr(USERIDS,''|'',1,17))) USER_TRX_IDENTIFIER_VALUE_9
3008 ,substr(USERIDS,instr(USERIDS,''|'',1,18)+1,(instr(USERIDS,''|'',1,19)-1-instr(USERIDS,''|'',1,18))) USER_TRX_IDENTIFIER_NAME_10
3009 ,substr(USERIDS,instr(USERIDS,''|'',1,19)+1,(length(USERIDS)-instr(USERIDS,''|'',1,19))) USER_TRX_IDENTIFIER_VALUE_10';
3010
3011
3012 -- Added LEGAL_CREATED_ID and LEGAL_POSTED_ID values for bug 16241762
3013 P_MAIN_COL_END := ',TABLE1.ORIG_LINE_NUMBER ORIG_LINE_NUMBER
3014 ,TABLE1.LEGAL_CREATED_ID LEGAL_CREATED_ID
3015 ,TABLE1.LEGAL_POSTED_ID LEGAL_POSTED_ID';
3016
3017
3018 --===========================================================================
3019 -- Building SLA query
3020 --===========================================================================
3021 IF l_fetch_from_sla_flag = 'Y' THEN
3022 get_sla_query;
3023
3024 p_sla_join := replace(p_sla_join,'$ledger_id$',l_ledgers);
3025
3026 p_sla_qualifier_segment := l_qualifier_segment;
3027 p_sla_seg_desc_from := l_seg_desc_from;
3028 p_sla_seg_desc_join := l_seg_desc_join;
3029
3030
3031 --
3032 -- <conditions based on balancing segment range>
3033 --
3034 IF(p_balancing_segment_from IS NOT NULL AND
3035 p_balancing_segment_to IS NOT NULL) THEN
3036
3037 p_other_param_filter := p_other_param_filter
3038 ||' AND '||l_balancing_segment
3039 ||' BETWEEN '|| p_balancing_segment_from
3040 ||' AND '|| p_balancing_segment_to ;
3041 END IF;
3042
3043 --
3044 -- <conditions based on accounting segment range>
3045 --
3046 IF(p_account_segment_from IS NOT NULL AND
3047 p_account_segment_to IS NOT NULL) THEN
3048
3049 p_other_param_filter := p_other_param_filter
3050 ||' AND '||l_account_segment
3051 ||' BETWEEN '||p_account_segment_from
3052 ||' AND '|| p_account_segment_to ;
3053 END IF;
3054
3055 END IF;
3056
3057 --===========================================================================
3058 -- Building GL query
3059 --===========================================================================
3060 IF l_fetch_from_gl_flag = 'Y' THEN
3061 IF NVL(p_fetch_from_gl,'Y') in ('Y','Yes') THEN -- Added for bug 7007065
3062
3063
3064 /* Begin Performance fix bug14050580 */
3065
3066
3067 IF ( p_balancing_segment_from is NULL
3068 AND p_balancing_segment_to is NULL
3069 AND p_account_segment_from is NULL
3070 AND p_account_segment_to is NULL
3071 AND p_account_flexfield_from is NULL
3072 AND p_account_flexfield_to is NULL
3073 )
3074 THEN
3075 get_gl_query('N');
3076 ELSE
3077 get_gl_query('Y');
3078 END IF;
3079
3080 /* End Performance fix bug14050580 */
3081
3082 p_gl_where := replace(p_gl_where,'$ledger_id$',l_ledgers);
3083
3084 p_gl_qualifier_segment := l_qualifier_segment;
3085 p_gl_seg_desc_from := l_seg_desc_from;
3086 p_gl_seg_desc_join := l_seg_desc_join;
3087
3088
3089 IF l_fetch_from_sla_flag = 'Y' THEN
3090 p_union_all := 'UNION ALL ';
3091 END IF;
3092
3093 END IF; -- Added for bug 7007065
3094
3095 END IF;
3096
3097 RETURN TRUE;
3098
3099 EXCEPTION
3100 WHEN OTHERS THEN
3101 xla_exceptions_pkg.raise_message
3102 (p_location => 'xla_jelines_rpt_pkg.beforeReport ');
3103 END beforeReport;
3104
3105
3106 --=============================================================================
3107 -- *********** Initialization routine **********
3108 --=============================================================================
3109
3110 --=============================================================================
3111 --
3112 --
3113 --
3114 --
3115 --
3116 --
3117 --
3118 --
3119 --
3120 --
3121 -- Following code is executed when the package body is referenced for the first
3122 -- time
3123 --
3124 --
3125 --
3126 --
3127 --
3128 --
3129 --
3130 --
3131 --
3132 --
3133 --
3134 --
3135 --=============================================================================
3136
3137 BEGIN
3138 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3139 g_log_enabled := fnd_log.test
3140 (log_level => g_log_level
3141 ,MODULE => C_DEFAULT_MODULE);
3142
3143 IF NOT g_log_enabled THEN
3144 g_log_level := C_LEVEL_LOG_DISABLED;
3145 END IF;
3146
3147 END xla_jelines_rpt_pkg;