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