[Home] [Help]
PACKAGE BODY: APPS.FUN_RECON_RPT_PKG
Source
1 PACKAGE BODY FUN_RECON_RPT_PKG AS
2 /* $Header: funrecrptb.pls 120.19.12010000.4 2009/02/19 12:51:48 srampure ship $ */
3
4 --=============================================================================
5 -- **************** declaraions ********************
6 --=============================================================================
7
8 TYPE t_rec IS RECORD
9 (f1 VARCHAR2(80)
10 ,f2 VARCHAR2(80));
11 TYPE t_array IS TABLE OF t_rec INDEX BY BINARY_INTEGER;
12
13
14 C_FUN_GET_ACCTS_QUERY CONSTANT VARCHAR2(3000) :=
15 'SELECT /*+ NO_MERGE Leading(gl2,fia2,fia1,gl1) */
16 distinct gl1.legal_entity_id TRANSACTING_LE_ID
17 ,gl1.LEGAL_ENTITY_NAME TRANSACTING_LE
18 ,gl1.ledger_id TRANSACTING_LEDGER_ID
19 ,gl1.ledger_name TRANSACTING_LEDGER
20 ,fia1.to_le_id TRADING_PARTNER_LE_ID
21 ,gl2.LEGAL_ENTITY_NAME TRADING_PARTNER_LE
22 ,gl2.ledger_id TRADING_PARTNER_LEDGER_ID
23 ,gl2.ledger_name TRADING_PARTNER_LEDGER
24 ,fia1.ccid CCID
25 ,fia1.type ACCT_TYPE
26 ,gl1.accounted_period_type ACCOUNTED_PERIOD_TYPE
27 ,gl1.period_set_name PERIOD_SET_NAME
28 FROM fun_inter_accounts_v fia1,
29 fun_inter_accounts_v fia2,
30 gl_ledger_le_v gl1,
31 gl_ledger_le_v gl2
32 WHERE fia1.ledger_id = gl1.ledger_id
33 AND fia1.from_le_id = gl1.legal_entity_id
34 AND fia1.to_le_id = fia2.from_le_id
35 AND fia1.from_le_id = fia2.to_le_id
36 AND fia2.ledger_id = gl2.ledger_id
37 AND fia2.from_le_id = gl2.legal_entity_id';
38
39 -- In the following query, fia1 relates to the receivables account side
40 -- of the transacting ledger and fia2 relates to the payables account side
41 -- of the trading ledger
42 -- The same query when passed with different parameters will be used such
43 -- that fia1 relates to the receivables account side of the trading ledger
44 -- fia2 relates to the payables account side of the transacting ledger
45 -- Note below is actually a subquery for gl balances balances. This is used
46 -- within C_FUN_GL_BALANCE_QUERY1 (which is the full query)
47 C_FUN_GL_BALANCE_QUERY CONSTANT VARCHAR2(30000) :=
48 '
49 SELECT distinct gl1.legal_entity_id TRANSACTING_LE_ID
50 ,gl1.LEGAL_ENTITY_NAME TRANSACTING_LE
51 ,gl1.ledger_id TRANSACTING_LEDGER_ID
52 ,gl1.ledger_name TRANSACTING_LEDGER
53 ,fia1.to_le_id TRADING_PARTNER_LE_ID
54 ,gl2.LEGAL_ENTITY_NAME TRADING_PARTNER_LE
55 ,gl2.ledger_id TRADING_PARTNER_LEDGER_ID
56 ,gl2.ledger_name TRADING_PARTNER_LEDGER
57 ,glb1.currency_code TRANSACTION_CURRENCY
58 ,glb1.period_name TRANSACTING_PERIOD_NAME
59 ,glp2.period_name TRADING_PERIOD_NAME
60 FROM fun_inter_accounts_v fia1,
61 gl_balances glb1,
62 gl_ledger_le_v gl1,
63 gl_periods glp1,
64 fun_inter_accounts_v fia2,
65 gl_ledger_le_v gl2,
66 gl_periods glp2
67 WHERE fia1.ledger_id = glb1.ledger_id
68 AND fia1.ccid = glb1.code_combination_id
69 AND fia1.type = ''R''
70 AND fia1.ledger_id = gl1.ledger_id
71 AND fia1.from_le_id = gl1.legal_entity_id
72 AND fia2.type = ''P''
73 AND fia1.to_le_id = fia2.from_le_id
74 AND fia1.from_le_id = fia2.to_le_id
75 AND glb1.actual_flag = ''A''
76 AND fia2.ledger_id = gl2.ledger_id
77 AND fia2.from_le_id = gl2.legal_entity_id
78 AND gl1.period_set_name = glp1.period_set_name
79 AND glp1.period_type = gl1.accounted_period_type
80 AND glp1.period_name = glb1.period_name
81 AND gl2.period_set_name = glp2.period_set_name
82 AND glp2.period_type = gl2.accounted_period_type
83 ';
84
85 C_ADD_CURRENCY_COLS CONSTANT VARCHAR2(30000) :=
86 ' ,gl_currency_api.convert_amount_sql (TRANSACTION_CURRENCY, :TO_CURR, fnd_date.canonical_to_date(:TO_DATE), :TYPE
87 ,fun_recon_rpt_pkg.get_balance(''R'', ''BEGIN_BALANCE_DR'', TRANSACTING_LEDGER_ID, TRANSACTING_LE_ID, TRANSACTING_PERIOD_NAME
88 ,TRADING_PARTNER_LEDGER_ID, TRADING_PARTNER_LE_ID, TRADING_PERIOD_NAME, TRANSACTION_CURRENCY)) AR_BEGIN_BALANCE_DR_ADD_CURR
89
90 ,gl_currency_api.convert_amount_sql (TRANSACTION_CURRENCY, :TO_CURR, fnd_date.canonical_to_date(:TO_DATE), :TYPE
91 ,fun_recon_rpt_pkg.get_balance(''R'', ''BEGIN_BALANCE_CR'', TRANSACTING_LEDGER_ID, TRANSACTING_LE_ID, TRANSACTING_PERIOD_NAME
92 ,TRADING_PARTNER_LEDGER_ID, TRADING_PARTNER_LE_ID, TRADING_PERIOD_NAME, TRANSACTION_CURRENCY)) AR_BEGIN_BALANCE_CR_ADD_CURR
93
94 ,gl_currency_api.convert_amount_sql (TRANSACTION_CURRENCY, :TO_CURR, fnd_date.canonical_to_date(:TO_DATE), :TYPE
95 ,fun_recon_rpt_pkg.get_balance(''P'', ''BEGIN_BALANCE_DR'', TRANSACTING_LEDGER_ID, TRANSACTING_LE_ID, TRANSACTING_PERIOD_NAME
96 ,TRADING_PARTNER_LEDGER_ID, TRADING_PARTNER_LE_ID, TRADING_PERIOD_NAME, TRANSACTION_CURRENCY)) AP_BEGIN_BALANCE_DR_ADD_CURR
97
98 ,gl_currency_api.convert_amount_sql (TRANSACTION_CURRENCY, :TO_CURR, fnd_date.canonical_to_date(:TO_DATE), :TYPE
99 ,fun_recon_rpt_pkg.get_balance(''P'', ''BEGIN_BALANCE_CR'', TRANSACTING_LEDGER_ID, TRANSACTING_LE_ID, TRANSACTING_PERIOD_NAME
100 ,TRADING_PARTNER_LEDGER_ID, TRADING_PARTNER_LE_ID, TRADING_PERIOD_NAME, TRANSACTION_CURRENCY)) AP_BEGIN_BALANCE_CR_ADD_CURR
101
102 ,gl_currency_api.convert_amount_sql (TRANSACTION_CURRENCY, :TO_CURR, fnd_date.canonical_to_date(:TO_DATE), :TYPE
103 ,fun_recon_rpt_pkg.get_balance(''R'', ''PERIOD_NET_DR'', TRANSACTING_LEDGER_ID, TRANSACTING_LE_ID, TRANSACTING_PERIOD_NAME
104 ,TRADING_PARTNER_LEDGER_ID, TRADING_PARTNER_LE_ID, TRADING_PERIOD_NAME, TRANSACTION_CURRENCY)) AR_PERIOD_NET_DR_ADD_CURR
105
106 ,gl_currency_api.convert_amount_sql (TRANSACTION_CURRENCY, :TO_CURR, fnd_date.canonical_to_date(:TO_DATE), :TYPE
107 ,fun_recon_rpt_pkg.get_balance(''R'', ''PERIOD_NET_CR'', TRANSACTING_LEDGER_ID, TRANSACTING_LE_ID, TRANSACTING_PERIOD_NAME
108 ,TRADING_PARTNER_LEDGER_ID, TRADING_PARTNER_LE_ID, TRADING_PERIOD_NAME, TRANSACTION_CURRENCY)) AR_PERIOD_NET_CR_ADD_CURR
109
110 ,gl_currency_api.convert_amount_sql (TRANSACTION_CURRENCY, :TO_CURR, fnd_date.canonical_to_date(:TO_DATE), :TYPE
111 ,fun_recon_rpt_pkg.get_balance(''P'', ''PERIOD_NET_DR'', TRANSACTING_LEDGER_ID, TRANSACTING_LE_ID, TRANSACTING_PERIOD_NAME
112 ,TRADING_PARTNER_LEDGER_ID, TRADING_PARTNER_LE_ID, TRADING_PERIOD_NAME, TRANSACTION_CURRENCY)) AP_PERIOD_NET_DR_ADD_CURR
113
114 ,gl_currency_api.convert_amount_sql (TRANSACTION_CURRENCY, :TO_CURR, fnd_date.canonical_to_date(:TO_DATE), :TYPE
115 ,fun_recon_rpt_pkg.get_balance(''P'', ''PERIOD_NET_CR'', TRANSACTING_LEDGER_ID, TRANSACTING_LE_ID, TRANSACTING_PERIOD_NAME
116 ,TRADING_PARTNER_LEDGER_ID, TRADING_PARTNER_LE_ID, TRADING_PERIOD_NAME, TRANSACTION_CURRENCY)) AP_PERIOD_NET_CR_ADD_CURR
117 ';
118
119
120 C_FUN_GL_BALANCE_QUERY1 CONSTANT VARCHAR2(30000) :=
121 '
122 SELECT TRANSACTING_LE_ID
123 ,TRANSACTING_LE
124 ,TRANSACTING_LEDGER_ID
125 ,TRANSACTING_LEDGER
126 ,TRADING_PARTNER_LE_ID
127 ,TRADING_PARTNER_LE
128 ,TRADING_PARTNER_LEDGER_ID
129 ,TRADING_PARTNER_LEDGER
130 ,TRANSACTION_CURRENCY
131 ,TRANSACTING_PERIOD_NAME
132 ,TRADING_PERIOD_NAME
133 ,fun_recon_rpt_pkg.get_balance(''R'', ''BEGIN_BALANCE_DR'', TRANSACTING_LEDGER_ID, TRANSACTING_LE_ID, TRANSACTING_PERIOD_NAME
134 ,TRADING_PARTNER_LEDGER_ID, TRADING_PARTNER_LE_ID, TRADING_PERIOD_NAME, TRANSACTION_CURRENCY) AR_BEGIN_BALANCE_DR
135
136 ,fun_recon_rpt_pkg.get_balance(''R'', ''BEGIN_BALANCE_CR'', TRANSACTING_LEDGER_ID, TRANSACTING_LE_ID, TRANSACTING_PERIOD_NAME
137 ,TRADING_PARTNER_LEDGER_ID, TRADING_PARTNER_LE_ID, TRADING_PERIOD_NAME, TRANSACTION_CURRENCY) AR_BEGIN_BALANCE_CR
138
139 ,fun_recon_rpt_pkg.get_balance(''P'', ''BEGIN_BALANCE_DR'', TRANSACTING_LEDGER_ID, TRANSACTING_LE_ID, TRANSACTING_PERIOD_NAME
140 ,TRADING_PARTNER_LEDGER_ID, TRADING_PARTNER_LE_ID, TRADING_PERIOD_NAME, TRANSACTION_CURRENCY) AP_BEGIN_BALANCE_DR
141
142 ,fun_recon_rpt_pkg.get_balance(''P'', ''BEGIN_BALANCE_CR'', TRANSACTING_LEDGER_ID, TRANSACTING_LE_ID, TRANSACTING_PERIOD_NAME
143 ,TRADING_PARTNER_LEDGER_ID, TRADING_PARTNER_LE_ID, TRADING_PERIOD_NAME, TRANSACTION_CURRENCY) AP_BEGIN_BALANCE_CR
144
145 ,fun_recon_rpt_pkg.get_balance(''R'', ''PERIOD_NET_DR'', TRANSACTING_LEDGER_ID, TRANSACTING_LE_ID, TRANSACTING_PERIOD_NAME
146 ,TRADING_PARTNER_LEDGER_ID, TRADING_PARTNER_LE_ID, TRADING_PERIOD_NAME, TRANSACTION_CURRENCY) AR_PERIOD_NET_DR
147
148 ,fun_recon_rpt_pkg.get_balance(''R'', ''PERIOD_NET_CR'', TRANSACTING_LEDGER_ID, TRANSACTING_LE_ID, TRANSACTING_PERIOD_NAME
149 ,TRADING_PARTNER_LEDGER_ID, TRADING_PARTNER_LE_ID, TRADING_PERIOD_NAME, TRANSACTION_CURRENCY) AR_PERIOD_NET_CR
150
151 ,fun_recon_rpt_pkg.get_balance(''P'', ''PERIOD_NET_DR'', TRANSACTING_LEDGER_ID, TRANSACTING_LE_ID, TRANSACTING_PERIOD_NAME
152 ,TRADING_PARTNER_LEDGER_ID, TRADING_PARTNER_LE_ID, TRADING_PERIOD_NAME, TRANSACTION_CURRENCY) AP_PERIOD_NET_DR
153
154 ,fun_recon_rpt_pkg.get_balance(''P'', ''PERIOD_NET_CR'', TRANSACTING_LEDGER_ID, TRANSACTING_LE_ID, TRANSACTING_PERIOD_NAME
155 ,TRADING_PARTNER_LEDGER_ID, TRADING_PARTNER_LE_ID, TRADING_PERIOD_NAME, TRANSACTION_CURRENCY) AP_PERIOD_NET_CR
156
157 $additional_currency_columns$
158
159 FROM
160 (
161 $sub_query$
162 ) ';
163
164 C_FUN_JELINES_SLA_QUERY CONSTANT VARCHAR2(30000) :=
165 'SELECT /*+ Leading(fun_act,aeh,xle) index (aeh XLA_AE_HEADERS_N5)*/
166 fun_act.TRANSACTING_LE_ID TRANS_LE_ID
167 ,fun_act.TRANSACTING_LE TRANS_LE
168 ,fun_act.TRADING_PARTNER_LE_ID TRAD_LE_ID
169 ,fun_act.TRADING_PARTNER_LE TRAD_LE
170 ,fun_act.TRANSACTING_LEDGER TRANSACTING_LEDGER
171 ,fun_act.TRANSACTING_LEDGER_ID LEDGER_ID
172 ,''SLA'' SOURCE
173 ,fun_act.ACCT_TYPE ACCOUNT_TYPE
174 ,aeh.accounting_date GL_DATE
175 ,aeh.creation_date CREATION_DATE
176 ,aeh.last_update_date LAST_UPDATE_DATE
177 ,aeh.gl_transfer_date GL_TRANSFER_DATE
178 ,aeh.reference_date REFERENCE_DATE
179 ,aeh.completed_date COMPLETED_DATE
180 ,ent.transaction_number TRANSACTION_NUMBER
181 ,xle.transaction_date TRANSACTION_DATE
182 ,aeh.doc_sequence_value DOCUMENT_SEQUENCE_NUMBER
183 ,aeh.application_id APPLICATION_ID
184 ,fap.application_name APPLICATION_NAME
185 ,aeh.ae_header_id HEADER_ID
186 ,aeh.description HEADER_DESCRIPTION
187 ,xlk1.meaning FUND_STATUS
188 ,gjct.user_je_category_name JE_CATEGORY_NAME
189 ,gjst.user_je_source_name JE_SOURCE_NAME
190 ,ae_line_num SLA_LINE_NUMBER
191 ,xle.event_id EVENT_ID
192 ,xle.event_date EVENT_DATE
193 ,xle.event_number EVENT_NUMBER
194 ,xet.event_class_code EVENT_CLASS_CODE
195 ,xect.NAME EVENT_CLASS_NAME
196 ,aeh.event_type_code EVENT_TYPE_CODE
197 ,xett.NAME EVENT_TYPE_NAME
198 ,gjb.NAME GL_BATCH_NAME
199 ,gjb.posted_date POSTED_DATE
200 ,gjh.NAME GL_JE_NAME
201 ,gjh.je_source JE_SOURCE_CODE
202 ,gjh.je_header_id JE_HEADER_ID
203 ,gjl.je_line_num GL_LINE_NUMBER
204 ,ael.displayed_line_number LINE_NUMBER
205 ,ael.accounting_class_code ACCOUNTING_CLASS_CODE
206 ,xlk2.meaning ACCOUNTING_CLASS_NAME
207 ,ael.description LINE_DESCRIPTION
208 ,ael.currency_code ENTERED_CURRENCY
209 ,ael.currency_conversion_rate CONVERSION_RATE
210 ,ael.currency_conversion_date CONVERSION_RATE_DATE
211 ,ael.currency_conversion_type CONVERSION_RATE_TYPE_CODE
212 ,gdct.user_conversion_type CONVERSION_RATE_TYPE
213 ,ael.entered_dr ENTERED_DR
214 ,ael.entered_cr ENTERED_CR
215 ,ael.unrounded_accounted_dr UNROUNDED_ACCOUNTED_DR
216 ,ael.unrounded_accounted_cr UNROUNDED_ACCOUNTED_CR
217 ,ael.accounted_dr ACCOUNTED_DR
218 ,ael.accounted_cr ACCOUNTED_CR
219 ,ael.statistical_amount STATISTICAL_AMOUNT
220 ,ael.jgzz_recon_ref RECONCILIATION_REFERENCE
221 ,ael.attribute_category ATTRIBUTE_CATEGORY
222 ,ael.attribute1 ATTRIBUTE1
223 ,ael.attribute2 ATTRIBUTE2
224 ,ael.attribute3 ATTRIBUTE3
225 ,ael.attribute4 ATTRIBUTE4
226 ,ael.attribute5 ATTRIBUTE5
227 ,ael.attribute6 ATTRIBUTE6
228 ,ael.attribute7 ATTRIBUTE7
229 ,ael.attribute8 ATTRIBUTE8
230 ,ael.attribute9 ATTRIBUTE9
231 ,ael.attribute10 ATTRIBUTE10
232 ,ael.attribute11 ATTRIBUTE11
233 ,ael.attribute12 ATTRIBUTE12
234 ,ael.attribute13 ATTRIBUTE13
235 ,ael.attribute14 ATTRIBUTE14
236 ,ael.attribute15 ATTRIBUTE15
237 ,ael.code_combination_id CODE_COMBINATION_ID
238 ,fun_trx_entry_util.get_concatenated_account(ael.code_combination_id ) ACCOUNT
239 ,ael.ae_header_id||''-''||ael.ae_line_num EXPAND_ID
240 ,nvl(xsrc.application_id, -1) DRILLDOWN_APP_ID
241 FROM xla_ae_headers aeh
242 ,xla_ae_lines ael
243 ,xla_lookups xlk1
244 ,xla_lookups xlk2
245 ,xla_events xle
246 ,xla_event_types_b xet
247 ,xla_event_classes_tl xect
248 ,xla_event_types_tl xett
249 ,xla_transaction_entities ent
250 ,fnd_application_tl fap
251 ,gl_je_categories_tl gjct
252 ,gl_je_sources_tl gjst
253 ,gl_daily_conversion_types gdct
254 ,gl_import_references gir
255 ,gl_je_lines gjl
256 ,gl_je_headers gjh
257 ,gl_je_batches gjb
258 ,gl_periods glp
259 ,xla_subledgers xsrc
260 , ($get_accounts_query$) fun_act
261 WHERE aeh.accounting_entry_status_code = ''F''
262 AND aeh.gl_transfer_status_code = ''Y''
263 AND ael.ae_header_id = aeh.ae_header_id
264 AND ael.application_id = aeh.application_id
265 AND xsrc.application_id = aeh.application_id
266 AND xlk1.lookup_type(+) = ''XLA_FUNDS_STATUS''
267 AND xlk1.lookup_code(+) = aeh.funds_status_code
268 AND xlk2.lookup_type = ''XLA_ACCOUNTING_CLASS''
269 AND xlk2.lookup_code = ael.accounting_class_code
270 AND xle.event_id = aeh.event_id
271 AND xet.application_id = aeh.application_id
272 AND xet.event_type_code = aeh.event_type_code
273 AND xect.application_id = xet.application_id
274 AND xect.entity_code = xet.entity_code
275 AND xect.event_class_code = xet.event_class_code
276 AND xect.LANGUAGE = USERENV(''LANG'')
277 AND xett.application_id = xet.application_id
278 AND xett.entity_code = xet.entity_code
279 AND xett.event_class_code = xet.event_class_code
280 AND xett.event_type_code = xet.event_type_code
281 AND xett.LANGUAGE = USERENV(''LANG'')
282 AND xle.application_id = aeh.application_id
283 AND ent.entity_id = xle.entity_id
284 AND ent.application_id = xle.application_id
285 AND fap.application_id = aeh.application_id
286 AND fap.LANGUAGE = USERENV(''LANG'')
287 AND gdct.conversion_type(+) = ael.currency_conversion_type
288 AND gir.gl_sl_link_id = ael.gl_sl_link_id
289 AND gir.gl_sl_link_table = ael.gl_sl_link_table
290 AND gjl.je_header_id = gir.je_header_id
291 AND gjl.je_line_num = gir.je_line_num
292 AND gjh.je_header_id = gir.je_header_id
293 AND gjb.je_batch_id = gir.je_batch_id
294 AND gjb.status = ''P''
295 AND gjct.je_category_name = gjh.je_category
296 AND gjct.LANGUAGE = USERENV(''LANG'')
297 AND gjst.je_source_name = gjh.je_source
298 AND gjst.LANGUAGE = USERENV(''LANG'')
299 AND aeh.balance_type_code = ''A''
300 AND ael.code_combination_id = fun_act.ccid
301 AND aeh.ledger_id = fun_act.TRANSACTING_LEDGER_ID
302 AND glp.period_set_name = fun_act.period_set_name
303 AND glp.period_type = fun_act.accounted_period_type
304 AND gjl.ledger_id = gjh.ledger_id
305 AND glp.period_name = aeh.period_name
306 AND glp.period_name = gjh.period_name
307 AND xsrc.je_source_name (+) = gjh.je_source ';
308
309 C_SLA_UNMATCHED_QUERY CONSTANT VARCHAR2(30000) :=
310 'SELECT glv.legal_entity_id TRANS_LE_ID
311 ,glv.LEGAL_ENTITY_NAME TRANS_LE
312 ,fia.to_le_id TRAD_LE_ID
313 ,fun_recon_rpt_pkg.get_legal_entity(fia.to_le_Id) TRAD_LE
314 ,glv.ledger_name TRANSACTING_LEDGER
315 ,glv.ledger_id LEDGER_ID
316 ,''SLA'' SOURCE
317 ,fia.type ACCOUNT_TYPE
318 ,aeh.accounting_date GL_DATE
319 ,aeh.creation_date CREATION_DATE
320 ,aeh.last_update_date LAST_UPDATE_DATE
321 ,aeh.gl_transfer_date GL_TRANSFER_DATE
322 ,aeh.reference_date REFERENCE_DATE
323 ,aeh.completed_date COMPLETED_DATE
324 ,ent.transaction_number TRANSACTION_NUMBER
325 ,xle.transaction_date TRANSACTION_DATE
326 ,aeh.application_id APPLICATION_ID
327 ,fap.application_name APPLICATION_NAME
328 ,aeh.ae_header_id HEADER_ID
329 ,aeh.description HEADER_DESCRIPTION
330 ,xlk1.meaning FUND_STATUS
331 ,gjct.user_je_category_name JE_CATEGORY_NAME
332 ,gjst.user_je_source_name JE_SOURCE_NAME
333 ,ae_line_num SLA_LINE_NUMBER
334 ,xle.event_id EVENT_ID
335 ,xle.event_date EVENT_DATE
336 ,xle.event_number EVENT_NUMBER
337 ,xet.event_class_code EVENT_CLASS_CODE
338 ,xect.NAME EVENT_CLASS_NAME
339 ,aeh.event_type_code EVENT_TYPE_CODE
340 ,xett.NAME EVENT_TYPE_NAME
341 ,gjb.NAME GL_BATCH_NAME
342 ,gjb.posted_date POSTED_DATE
343 ,gjh.NAME GL_JE_NAME
344 ,gjh.je_source JE_SOURCE_CODE
345 ,gjh.je_header_id JE_HEADER_ID
346 ,gjl.je_line_num GL_LINE_NUMBER
347 ,ael.displayed_line_number LINE_NUMBER
348 ,ael.accounting_class_code ACCOUNTING_CLASS_CODE
349 ,xlk2.meaning ACCOUNTING_CLASS_NAME
350 ,ael.description LINE_DESCRIPTION
351 ,ael.currency_code ENTERED_CURRENCY
352 ,ael.currency_conversion_rate CONVERSION_RATE
353 ,ael.currency_conversion_date CONVERSION_RATE_DATE
354 ,ael.currency_conversion_type CONVERSION_RATE_TYPE_CODE
355 ,gdct.user_conversion_type CONVERSION_RATE_TYPE
356 ,ael.entered_dr ENTERED_DR
357 ,ael.entered_cr ENTERED_CR
358 ,ael.unrounded_accounted_dr UNROUNDED_ACCOUNTED_DR
359 ,ael.unrounded_accounted_cr UNROUNDED_ACCOUNTED_CR
360 ,ael.accounted_dr ACCOUNTED_DR
361 ,ael.accounted_cr ACCOUNTED_CR
362 ,ael.statistical_amount STATISTICAL_AMOUNT
363 ,ael.jgzz_recon_ref RECONCILIATION_REFERENCE
364 ,ael.attribute_category ATTRIBUTE_CATEGORY
365 ,ael.attribute1 ATTRIBUTE1
366 ,ael.attribute2 ATTRIBUTE2
367 ,ael.attribute3 ATTRIBUTE3
368 ,ael.attribute4 ATTRIBUTE4
369 ,ael.attribute5 ATTRIBUTE5
370 ,ael.attribute6 ATTRIBUTE6
371 ,ael.attribute7 ATTRIBUTE7
372 ,ael.attribute8 ATTRIBUTE8
373 ,ael.attribute9 ATTRIBUTE9
374 ,ael.attribute10 ATTRIBUTE10
375 ,ael.attribute11 ATTRIBUTE11
376 ,ael.attribute12 ATTRIBUTE12
377 ,ael.attribute13 ATTRIBUTE13
378 ,ael.attribute14 ATTRIBUTE14
379 ,ael.attribute15 ATTRIBUTE15
380 ,ael.code_combination_id CODE_COMBINATION_ID
381 ,fun_trx_entry_util.get_concatenated_account(ael.code_combination_id ) ACCOUNT
382 ,ael.ae_header_id||''-''||ael.ae_line_num EXPAND_ID
383 ,nvl(xsrc.application_id, -1) DRILLDOWN_APP_ID
384 FROM xla_ae_headers aeh
385 ,xla_ae_lines ael
386 ,xla_lookups xlk1
387 ,xla_lookups xlk2
388 ,xla_events xle
389 ,xla_event_types_b xet
390 ,xla_event_classes_tl xect
391 ,xla_event_types_tl xett
392 ,xla_transaction_entities ent
393 ,fnd_application_tl fap
394 ,gl_je_categories_tl gjct
395 ,gl_je_sources_tl gjst
396 ,gl_daily_conversion_types gdct
397 ,gl_import_references gir
398 ,gl_je_lines gjl
399 ,gl_je_headers gjh
400 ,gl_je_batches gjb
401 ,gl_ledger_le_v glv
402 ,fun_inter_accounts_v fia
403 ,gl_periods glp
404 ,xla_subledgers xsrc
405 WHERE aeh.accounting_entry_status_code = ''F''
406 AND aeh.gl_transfer_status_code = ''Y''
407 AND ael.ae_header_id = aeh.ae_header_id
408 AND ael.application_id = aeh.application_id
409 AND xsrc.application_id = aeh.application_id
410 AND xlk1.lookup_type(+) = ''XLA_FUNDS_STATUS''
411 AND xlk1.lookup_code(+) = aeh.funds_status_code
412 AND xlk2.lookup_type = ''XLA_ACCOUNTING_CLASS''
413 AND xlk2.lookup_code = ael.accounting_class_code
414 AND xle.event_id = aeh.event_id
415 AND xet.application_id = aeh.application_id
416 AND xet.event_type_code = aeh.event_type_code
417 AND xect.application_id = xet.application_id
418 AND xect.entity_code = xet.entity_code
419 AND xect.event_class_code = xet.event_class_code
420 AND xect.LANGUAGE = USERENV(''LANG'')
421 AND xett.application_id = xet.application_id
422 AND xett.entity_code = xet.entity_code
423 AND xett.event_class_code = xet.event_class_code
424 AND xett.event_type_code = xet.event_type_code
425 AND xett.LANGUAGE = USERENV(''LANG'')
426 AND ent.entity_id = xle.entity_id
427 AND ent.application_id = xle.application_id
428 AND xle.application_id = aeh.application_id
429 AND fap.application_id = aeh.application_id
430 AND fap.LANGUAGE = USERENV(''LANG'')
431 AND gdct.conversion_type(+) = ael.currency_conversion_type
432 AND gir.gl_sl_link_id = ael.gl_sl_link_id
433 AND gir.gl_sl_link_table = ael.gl_sl_link_table
434 AND gjl.je_header_id = gir.je_header_id
435 AND gjl.je_line_num = gir.je_line_num
436 AND gjh.je_header_id = gir.je_header_id
437 AND gjb.je_batch_id = gir.je_batch_id
438 AND gjb.status = ''P''
439 AND gjct.je_category_name = gjh.je_category
440 AND gjct.LANGUAGE = USERENV(''LANG'')
441 AND gjst.je_source_name = gjh.je_source
442 AND gjst.LANGUAGE = USERENV(''LANG'')
443 AND aeh.ledger_id = fia.ledger_id
444 AND aeh.balance_type_code = ''A''
445 AND ael.code_combination_id = fia.ccid
446 AND fia.ledger_id = glv.ledger_id
447 AND fia.from_le_id = glv.legal_entity_id
448 AND glv.period_set_name = glp.period_set_name
449 AND glv.accounted_period_type = glp.period_type
450 AND glp.period_name = aeh.period_name
451 AND glp.period_name = gjh.period_name
452 AND xsrc.je_source_name (+) = gjh.je_source ';
453
454
455 C_FUN_JELINES_SUM_QUERY CONSTANT VARCHAR2(30000) :=
456 'SELECT fun_act.TRANSACTING_LE_ID SRC_TRANS_LE_ID
457 ,fun_act.TRANSACTING_LE SRC_TRANS_LE
458 ,fun_act.TRADING_PARTNER_LE_ID SRC_TRAD_LE_ID
459 ,fun_act.TRADING_PARTNER_LE SRC_TRAD_LE
460 ,fun_act.TRANSACTING_LEDGER SRC_TRANS_LEDGER
461 ,fun_act.TRANSACTING_LEDGER_ID SRC_TRANS_LEDGER_ID
462 ,gjct.user_je_category_name JOURNAL_CATEGORY
463 ,gjst.user_je_source_name JOURNAL_SOURCE
464 ,gjh.currency_code TRX_CURR
465 ,gjl.period_name PERIOD_NAME
466 ,NVL(sum(decode(fun_act.acct_type,''R'',(Nvl(gjl.entered_dr,0)), 0)),0) AR_ENTERED_DR
467 ,NVL(sum(decode(fun_act.acct_type,''R'',(Nvl(gjl.entered_cr,0)), 0)),0) AR_ENTERED_CR
468 ,NVL(sum(decode(fun_act.acct_type,''R'',(Nvl(gjl.accounted_dr,0)), 0)),0) AR_ACCOUNTED_DR
469 ,NVL(sum(decode(fun_act.acct_type,''R'',(Nvl(gjl.accounted_cr,0)), 0)),0) AR_ACCOUNTED_CR
470 ,NVL(sum(decode(fun_act.acct_type,''P'',(Nvl(gjl.entered_dr,0)), 0)),0) AP_ENTERED_DR
471 ,NVL(sum(decode(fun_act.acct_type,''P'',(Nvl(gjl.entered_cr,0)), 0)),0) AP_ENTERED_CR
472 ,NVL(sum(decode(fun_act.acct_type,''P'',(Nvl(gjl.accounted_dr,0)), 0)),0) AP_ACCOUNTED_DR
473 ,NVL(sum(decode(fun_act.acct_type,''P'',(Nvl(gjl.accounted_cr,0)), 0)),0) AP_ACCOUNTED_CR
474 FROM gl_je_categories_tl gjct
475 ,gl_je_sources_tl gjst
476 ,gl_je_lines gjl
477 ,gl_je_headers gjh
478 ,gl_je_batches gjb
479 ,gl_periods glp
480 , ($get_accounts_query$) fun_act
481 WHERE gjh.je_header_id = gjl.je_header_id
482 AND gjb.je_batch_id = gjh.je_batch_id
483 AND gjb.status = ''P''
484 AND gjct.je_category_name = gjh.je_category
485 AND gjct.LANGUAGE = USERENV(''LANG'')
486 AND gjst.je_source_name = gjh.je_source
487 AND gjst.language = USERENV(''LANG'')
488 AND gjh.actual_flag = ''A''
489 AND gjl.code_combination_id = fun_act.ccid
490 AND gjl.ledger_id = fun_act.TRANSACTING_LEDGER_ID
491 AND glp.period_set_name = fun_act.period_set_name
492 AND glp.period_type = fun_act.accounted_period_type
493 AND glp.period_name = gjl.period_name
494 AND gjl.ledger_id = gjh.ledger_id ';
495
496 C_SUM_UNMATCHED_QUERY CONSTANT VARCHAR2(30000) :=
497 ' SELECT SRC_TRANS_LE_ID
498 ,SRC_TRANS_LE
499 ,SRC_TRAD_LE_ID
500 ,SRC_TRAD_LE
501 ,SRC_TRANS_LEDGER
502 ,JOURNAL_CATEGORY
503 ,JOURNAL_SOURCE
504 ,TRX_CURR
505 ,PERIOD_NAME
506 ,NVL(sum(decode(TYPE,''R'',(Nvl(ENTERED_DR,0)), 0)),0) AR_ENTERED_DR
507 ,NVL(sum(decode(TYPE,''R'',(Nvl(ENTERED_CR,0)), 0)),0) AR_ENTERED_CR
508 ,NVL(sum(decode(TYPE,''R'',(Nvl(ACCOUNTED_DR,0)), 0)),0) AR_ACCOUNTED_DR
509 ,NVL(sum(decode(TYPE,''R'',(Nvl(ACCOUNTED_CR,0)), 0)),0) AR_ACCOUNTED_CR
510 ,NVL(sum(decode(TYPE,''P'',(Nvl(ENTERED_DR,0)), 0)),0) AP_ENTERED_DR
511 ,NVL(sum(decode(TYPE,''P'',(Nvl(ENTERED_CR,0)), 0)),0) AP_ENTERED_CR
512 ,NVL(sum(decode(TYPE,''P'',(Nvl(ACCOUNTED_DR,0)), 0)),0) AP_ACCOUNTED_DR
513 ,NVL(sum(decode(TYPE,''P'',(Nvl(ACCOUNTED_CR,0)), 0)),0) AP_ACCOUNTED_CR
514 FROM (
515
516 SELECT glv.legal_entity_id SRC_TRANS_LE_ID
517 ,glv.LEGAL_ENTITY_NAME SRC_TRANS_LE
518 ,fia.to_le_id SRC_TRAD_LE_ID
519 ,fun_recon_rpt_pkg.get_legal_entity(fia.to_le_Id) SRC_TRAD_LE
520 ,fia.ledger_id SRC_TRANS_LEDGER_ID
521 ,glv.ledger_name SRC_TRANS_LEDGER
522 ,gjct.user_je_category_name JOURNAL_CATEGORY
523 ,gjst.user_je_source_name JOURNAL_SOURCE
524 ,gjh.currency_code TRX_CURR
525 ,gjl.period_name PERIOD_NAME
526 ,gjl.entered_dr ENTERED_DR
527 ,gjl.entered_cr ENTERED_CR
528 ,gjl.accounted_dr ACCOUNTED_DR
529 ,gjl.accounted_cr ACCOUNTED_CR
530 ,fia.type TYPE
531 ,glp.start_date START_DATE
532 ,glp.end_date END_DATE
533 FROM gl_je_categories_tl gjct
534 ,gl_je_sources_tl gjst
535 ,gl_je_lines gjl
536 ,gl_je_headers gjh
537 ,gl_je_batches gjb
538 ,gl_ledger_le_v glv
539 ,fun_inter_accounts_v fia
540 ,gl_periods glp
541 WHERE gjh.je_header_id = gjl.je_header_id
542 AND gjb.je_batch_id = gjh.je_batch_id
543 AND gjb.status = ''P''
544 AND gjct.je_category_name = gjh.je_category
545 AND gjct.LANGUAGE = USERENV(''LANG'')
546 AND gjst.je_source_name = gjh.je_source
547 AND gjst.language = USERENV(''LANG'')
548 AND gjl.ledger_id = fia.ledger_id
549 AND gjh.actual_flag = ''A''
550 AND gjl.code_combination_id = fia.ccid
551 AND fia.ledger_id = glv.ledger_id
552 AND fia.from_le_id = glv.legal_entity_id
553 AND glv.period_set_name = glp.period_set_name
554 AND glv.accounted_period_type = glp.period_type
555 AND glp.period_name = gjl.period_name
556 AND (
557 gjh.parent_je_header_id IS NOT NULL
558 OR
559 gjh.je_source NOT IN (SELECT DISTINCT je_source_name
560 FROM xla_subledgers
561 WHERE je_source_name <> ''Global Intercompany'')
562
563 )
564
565 UNION
566
567 SELECT glv.legal_entity_id SRC_TRANS_LE_ID
568 ,glv.LEGAL_ENTITY_NAME SRC_TRANS_LE
569 ,fia.to_le_id SRC_TRAD_LE_ID
570 ,fun_recon_rpt_pkg.get_legal_entity(fia.to_le_Id) SRC_TRAD_LE
571 ,fia.ledger_id SRC_TRANS_LEDGER_ID
572 ,glv.ledger_name SRC_TRANS_LEDGER
573 ,gjct.user_je_category_name JOURNAL_CATEGORY
574 ,gjst.user_je_source_name JOURNAL_SOURCE
575 ,gjh.currency_code TRX_CURR
576 ,gjl.period_name PERIOD_NAME
577 ,gjl.entered_dr ENTERED_DR
578 ,gjl.entered_cr ENTERED_CR
579 ,gjl.accounted_dr ACCOUNTED_DR
580 ,gjl.accounted_cr ACCOUNTED_CR
581 ,fia.type TYPE
582 ,glp.start_date START_DATE
583 ,glp.end_date END_DATE
584 FROM gl_je_categories_tl gjct
585 ,gl_je_sources_tl gjst
586 ,gl_je_lines gjl
587 ,gl_je_headers gjh
588 ,gl_je_batches gjb
589 ,gl_ledger_le_v glv
590 ,fun_inter_accounts_v fia
591 ,gl_periods glp
592 ,xla_ae_headers aeh
593 ,xla_ae_lines ael
594 ,xla_events xle
595 ,xla_event_types_b xet
596 ,xla_transaction_entities ent
597 ,gl_import_references gir
598 WHERE gjh.je_header_id = gjl.je_header_id
599 AND gjb.je_batch_id = gjh.je_batch_id
600 AND gjb.status = ''P''
601 AND gjct.je_category_name = gjh.je_category
602 AND gjct.LANGUAGE = USERENV(''LANG'')
603 AND gjst.je_source_name = gjh.je_source
604 AND gjst.language = USERENV(''LANG'')
605 AND gjl.ledger_id = fia.ledger_id
606 AND gjh.actual_flag = ''A''
607 AND gjl.code_combination_id = fia.ccid
608 AND fia.ledger_id = glv.ledger_id
609 AND fia.from_le_id = glv.legal_entity_id
610 AND glv.period_set_name = glp.period_set_name
611 AND glv.accounted_period_type = glp.period_type
612 AND glp.period_name = gjl.period_name
613 AND aeh.accounting_entry_status_code = ''F''
614 AND aeh.gl_transfer_status_code = ''Y''
615 AND ael.ae_header_id = aeh.ae_header_id
616 AND ael.application_id = aeh.application_id
617 AND xle.event_id = aeh.event_id
618 AND xet.application_id = aeh.application_id
619 AND xet.event_type_code = aeh.event_type_code
620 AND ent.entity_id = xle.entity_id
621 AND ent.application_id = xle.application_id
622 AND gir.gl_sl_link_id = ael.gl_sl_link_id
623 AND gir.gl_sl_link_table = ael.gl_sl_link_table
624 AND gjl.je_header_id = gir.je_header_id
625 AND gjl.je_line_num = gir.je_line_num
626 AND gjh.je_header_id = gir.je_header_id
627 AND gjb.je_batch_id = gir.je_batch_id
628 $where_clause1$
629 )
630 WHERE 1 = 1 ';
631
632
633 C_FUN_JELINES_GL_QUERY CONSTANT VARCHAR2(30000) :=
634 'SELECT fun_act.TRANSACTING_LE_ID TRANS_LE_ID
635 ,fun_act.TRANSACTING_LE TRANS_LE
636 ,fun_act.TRADING_PARTNER_LE_ID TRAD_LE_ID
637 ,fun_act.TRADING_PARTNER_LE TRAD_LE
638 ,fun_act.TRANSACTING_LEDGER TRANSACTING_LEDGER
639 ,fun_act.TRANSACTING_LEDGER_ID LEDGER_ID
640 ,''GL'' SOURCE
641 ,fun_act.acct_type ACCOUNT_TYPE
642 ,gjh.default_effective_date GL_DATE
643 ,fdu.user_name CREATED_BY
644 ,gjh.creation_date CREATION_DATE
645 ,gjh.last_update_date LAST_UPDATE_DATE
646 ,gjh.reference_date REFERENCE_DATE
647 ,gjh.je_header_id HEADER_ID
648 ,gjh.description HEADER_DESCRIPTION
649 ,gjct.user_je_category_name JE_CATEGORY_NAME
650 ,gjst.user_je_source_name JE_SOURCE_NAME
651 ,gjb.NAME GL_BATCH_NAME
652 ,gjb.posted_date POSTED_DATE
653 ,gjh.NAME GL_JE_NAME
654 ,gjl.je_line_num GL_LINE_NUMBER
655 ,gjl.description LINE_DESCRIPTION
656 ,gjh.currency_code ENTERED_CURRENCY
657 ,gjh.currency_conversion_rate CONVERSION_RATE
658 ,gjh.currency_conversion_date CONVERSION_RATE_DATE
659 ,gjh.currency_conversion_type CONVERSION_RATE_TYPE_CODE
660 ,gdct.user_conversion_type CONVERSION_RATE_TYPE
661 ,gjl.entered_dr ENTERED_DR
662 ,gjl.entered_cr ENTERED_CR
663 ,gjl.accounted_dr ACCOUNTED_DR
664 ,gjl.accounted_cr ACCOUNTED_CR
665 ,gjl.code_combination_id CODE_COMBINATION_ID
666 ,gjl.period_name PERIOD_NAME
667 ,fun_trx_entry_util.get_concatenated_account(gjl.code_combination_id ) ACCOUNT
668 ,gjl.stat_amount STATISTICAL_AMOUNT
669 ,gjl.jgzz_recon_ref_11i RECONCILIATION_REFERENCE
670 ,gjl.context ATTRIBUTE_CATEGORY
671 ,gjl.attribute1 ATTRIBUTE1
672 ,gjl.attribute2 ATTRIBUTE2
673 ,gjl.attribute3 ATTRIBUTE3
674 ,gjl.attribute4 ATTRIBUTE4
675 ,gjl.attribute5 ATTRIBUTE5
676 ,gjl.attribute6 ATTRIBUTE6
677 ,gjl.attribute7 ATTRIBUTE7
678 ,gjl.attribute8 ATTRIBUTE8
679 ,gjl.attribute9 ATTRIBUTE9
680 ,gjl.attribute10 ATTRIBUTE10
681 ,gjl.attribute11 ATTRIBUTE11
682 ,gjl.attribute12 ATTRIBUTE12
683 ,gjl.attribute13 ATTRIBUTE13
684 ,gjl.attribute14 ATTRIBUTE14
685 ,gjl.attribute15 ATTRIBUTE15
686 ,gjl.attribute16 ATTRIBUTE16
687 ,gjl.attribute17 ATTRIBUTE17
688 ,gjl.attribute18 ATTRIBUTE18
689 ,gjl.attribute19 ATTRIBUTE19
690 ,gjl.attribute20 ATTRIBUTE20
691 ,gjl.je_header_id||''-''||gjl.je_line_num EXPAND_ID
692 FROM fnd_user fdu
693 ,gl_je_categories_tl gjct
694 ,gl_je_sources_tl gjst
695 ,gl_daily_conversion_types gdct
696 ,gl_je_lines gjl
697 ,gl_je_headers gjh
698 ,gl_je_batches gjb
699 ,gl_periods glp
700 , ($get_accounts_query$) fun_act
701 WHERE gjh.je_header_id = gjl.je_header_id
702 AND gjb.je_batch_id = gjh.je_batch_id
703 AND gjb.status = ''P''
704 AND fdu.user_id = gjb.created_by
705 AND gjct.je_category_name = gjh.je_category
706 AND gjct.LANGUAGE = USERENV(''LANG'')
707 AND gjst.je_source_name = gjh.je_source
708 AND gjst.language = USERENV(''LANG'')
709 AND gdct.conversion_type(+) = gjh.currency_conversion_type
710 AND (gjh.parent_je_header_id IS NOT NULL
711 OR
712 gjh.je_source NOT IN (SELECT DISTINCT je_source_name
713 FROM xla_subledgers
714 WHERE je_source_name <> ''Global Intercompany'')
715 )
716 AND gjl.ledger_id = gjh.ledger_id
717 AND gjh.actual_flag = ''A''
718 AND gjl.code_combination_id = fun_act.ccid
719 AND gjl.ledger_id = fun_act.TRANSACTING_LEDGER_ID
720 AND glp.period_set_name = fun_act.period_set_name
721 AND glp.period_type = fun_act.accounted_period_type
722 AND glp.period_name = gjl.period_name ';
723
724
725 C_GL_UNMATCHED_QUERY CONSTANT VARCHAR2(30000) :=
726 'SELECT glv.legal_entity_id TRANS_LE_ID
727 ,glv.LEGAL_ENTITY_NAME TRANS_LE
728 ,fia.to_le_id TRAD_LE_ID
729 ,fun_recon_rpt_pkg.get_legal_entity(fia.to_le_Id) TRAD_LE
730 ,glv.ledger_name TRANSACTING_LEDGER
731 ,glv.ledger_id LEDGER_ID
732 ,''GL'' SOURCE
733 ,fia.type ACCOUNT_TYPE
734 ,gjh.default_effective_date GL_DATE
735 ,fdu.user_name CREATED_BY
736 ,gjh.creation_date CREATION_DATE
737 ,gjh.last_update_date LAST_UPDATE_DATE
738 ,gjh.reference_date REFERENCE_DATE
739 ,gjh.je_header_id HEADER_ID
740 ,gjh.description HEADER_DESCRIPTION
741 ,gjct.user_je_category_name JE_CATEGORY_NAME
742 ,gjst.user_je_source_name JE_SOURCE_NAME
743 ,gjb.NAME GL_BATCH_NAME
744 ,gjb.posted_date POSTED_DATE
745 ,gjh.NAME GL_JE_NAME
746 ,gjl.je_line_num GL_LINE_NUMBER
747 ,gjl.description LINE_DESCRIPTION
748 ,gjh.currency_code ENTERED_CURRENCY
749 ,gjh.currency_conversion_rate CONVERSION_RATE
750 ,gjh.currency_conversion_date CONVERSION_RATE_DATE
751 ,gjh.currency_conversion_type CONVERSION_RATE_TYPE_CODE
752 ,gdct.user_conversion_type CONVERSION_RATE_TYPE
753 ,gjl.entered_dr ENTERED_DR
754 ,gjl.entered_cr ENTERED_CR
755 ,gjl.accounted_dr ACCOUNTED_DR
756 ,gjl.accounted_cr ACCOUNTED_CR
757 ,gjl.code_combination_id CODE_COMBINATION_ID
758 ,gjl.period_name PERIOD_NAME
759 ,fun_trx_entry_util.get_concatenated_account(gjl.code_combination_id ) ACCOUNT
760 ,gjl.stat_amount STATISTICAL_AMOUNT
761 ,gjl.jgzz_recon_ref_11i RECONCILIATION_REFERENCE
762 ,gjl.context ATTRIBUTE_CATEGORY
763 ,gjl.attribute1 ATTRIBUTE1
764 ,gjl.attribute2 ATTRIBUTE2
765 ,gjl.attribute3 ATTRIBUTE3
766 ,gjl.attribute4 ATTRIBUTE4
767 ,gjl.attribute5 ATTRIBUTE5
768 ,gjl.attribute6 ATTRIBUTE6
769 ,gjl.attribute7 ATTRIBUTE7
770 ,gjl.attribute8 ATTRIBUTE8
771 ,gjl.attribute9 ATTRIBUTE9
772 ,gjl.attribute10 ATTRIBUTE10
773 ,gjl.attribute11 ATTRIBUTE11
774 ,gjl.attribute12 ATTRIBUTE12
775 ,gjl.attribute13 ATTRIBUTE13
776 ,gjl.attribute14 ATTRIBUTE14
777 ,gjl.attribute15 ATTRIBUTE15
778 ,gjl.attribute16 ATTRIBUTE16
779 ,gjl.attribute17 ATTRIBUTE17
780 ,gjl.attribute18 ATTRIBUTE18
781 ,gjl.attribute19 ATTRIBUTE19
782 ,gjl.attribute20 ATTRIBUTE20
783 ,gjl.je_header_id||''-''||gjl.je_line_num EXPAND_ID
784 FROM fnd_user fdu
785 ,gl_je_categories_tl gjct
786 ,gl_je_sources_tl gjst
787 ,gl_daily_conversion_types gdct
788 ,gl_je_lines gjl
789 ,gl_je_headers gjh
790 ,gl_je_batches gjb
791 ,gl_ledger_le_v glv
792 ,fun_inter_accounts_v fia
793 ,gl_periods glp
794 WHERE gjh.je_header_id = gjl.je_header_id
795 AND gjb.je_batch_id = gjh.je_batch_id
796 AND gjb.status = ''P''
797 AND fdu.user_id = gjb.created_by
798 AND gjct.je_category_name = gjh.je_category
799 AND gjct.LANGUAGE = USERENV(''LANG'')
800 AND gjst.je_source_name = gjh.je_source
801 AND gjst.language = USERENV(''LANG'')
802 AND gdct.conversion_type(+) = gjh.currency_conversion_type
803 AND (gjh.parent_je_header_id IS NOT NULL
804 OR
805 gjh.je_source NOT IN (SELECT DISTINCT je_source_name
806 FROM xla_subledgers
807 WHERE je_source_name <> ''Global Intercompany'')
808 )
809 AND gjl.ledger_id = fia.ledger_id
810 AND gjh.actual_flag = ''A''
811 AND gjl.code_combination_id = fia.ccid
812 AND fia.ledger_id = glv.ledger_id
813 AND fia.from_le_id = glv.legal_entity_id
814 AND glv.period_set_name = glp.period_set_name
815 AND glv.accounted_period_type = glp.period_type
816 AND glp.period_name = gjl.period_name ';
817
818 -------------------------------------------------------------------------------
819 -- Define Types
820 -------------------------------------------------------------------------------
821 TYPE t_array_char IS TABLE OF VARCHAR2(32000) INDEX BY BINARY_INTEGER ;
822
823
824 --=============================================================================
825 -- ************** forward declaraions ******************
826 --=============================================================================
827
828 --------------------------------------------------------------------------------
829 -- procedure to create the main SQL
830 --------------------------------------------------------------------------------
831 PROCEDURE get_fun_main_sql
832 (p_trans_ledger_id IN NUMBER
833 ,p_trans_legal_entity_id IN NUMBER
834 ,p_trans_gl_period IN VARCHAR2
835 ,p_tp_ledger_id IN NUMBER
836 ,p_tp_legal_entity_id IN NUMBER
837 ,p_currency IN VARCHAR2
838 ,p_tp_gl_period IN VARCHAR2
839 ,p_rate_type IN VARCHAR2
840 ,p_rate_date IN DATE
841 ,p_array_sql IN OUT NOCOPY T_ARRAY_CHAR);
842
843
844 --------------------------------------------------------------------------------
845 -- procedure to create a dummy SQL to print paramteres to the XML file
846 --------------------------------------------------------------------------------
847
848 PROCEDURE get_fun_parameter_sql
849 (p_trans_ledger_id IN NUMBER
850 ,p_trans_legal_entity_id IN NUMBER
851 ,p_trans_gl_period IN VARCHAR2
852 ,p_tp_ledger_id IN NUMBER
853 ,p_tp_legal_entity_id IN NUMBER
854 ,p_currency IN VARCHAR2
855 ,p_tp_gl_period IN VARCHAR2
856 ,p_rate_type IN VARCHAR2
857 ,p_rate_date IN VARCHAR2
858 ,p_array_sql IN OUT NOCOPY T_ARRAY_CHAR);
859
860
861
862 --=============================================================================
863 -- *********** Local Trace Routine **********
864 --=============================================================================
865 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
866 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
867 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
868 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
869 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
870 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
871
872 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
873 C_DEFAULT_MODULE CONSTANT VARCHAR2(240):= 'fun.plsql.fun_recon_rpt_pkg';
874
875 g_log_level NUMBER;
876 g_log_enabled BOOLEAN;
877
878 PROCEDURE trace
879 (p_msg IN VARCHAR2
880 ,p_level IN NUMBER
881 ,p_module IN VARCHAR2) IS
882 BEGIN
883 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
884 fnd_log.message(p_level, NVL(p_module,C_DEFAULT_MODULE));
885 ELSIF p_level >= g_log_level THEN
886 fnd_log.string(p_level, NVL(p_module,C_DEFAULT_MODULE), p_msg);
887 END IF;
888
889 EXCEPTION
890 WHEN OTHERS THEN
891 FUN_UTIL.log_conc_unexp(C_DEFAULT_MODULE, 'trace');
892 RAISE;
893 END trace;
894
895
896 --=============================================================================
897 -- *********** public procedures and functions **********
898 --=============================================================================
899 --=============================================================================
900 -- Following are public routines
901 --
902 -- 1. run_fun_report
903 --=============================================================================
904 --=============================================================================
905 --
906 -- PROCEDURE RUN_FUN_REPORT
907 --
908 --=============================================================================
909 PROCEDURE run_fun_report
910 (p_errbuf OUT NOCOPY VARCHAR2
911 ,p_retcode OUT NOCOPY NUMBER
912 ,p_trans_ledger_id IN NUMBER
913 ,p_trans_legal_entity_id IN NUMBER
914 ,p_trans_gl_period IN VARCHAR2
915 ,p_tp_ledger_id IN NUMBER
916 ,p_tp_legal_entity_id IN NUMBER
917 ,p_tp_gl_period IN VARCHAR2
918 ,p_currency IN VARCHAR2
919 ,p_rate_type IN VARCHAR2
920 ,p_rate_date IN VARCHAR2) IS
921
922 l_array_sql T_ARRAY_CHAR;
923 l_source_application_id NUMBER;
924 l_fetch_from_sla_flag VARCHAR2(1);
925 l_fetch_from_gl_flag VARCHAR2(1);
926 l_xml_clob CLOB;
927 l_ctx NUMBER;
928 l_log_module VARCHAR2(240);
929 l_para_ctx dbms_xmlgen.ctxHandle;
930 l_encoding VARCHAR2(20);
931
932 l_start_period_num NUMBER;
933 l_end_period_num NUMBER;
934 l_start_date DATE;
935 l_end_date DATE;
936 l_rate_date DATE;
937 l_select_str VARCHAR2(4000);
938 l_from_str VARCHAR2(240);
939 l_where_str VARCHAR2(4000);
940 l_insert_query VARCHAR2(4000);
941 l_lang VARCHAR2(80);
942 l_count NUMBER;
943 l_message fnd_new_messages.message_text%TYPE;
944
945 BEGIN
946 IF g_log_enabled THEN
947 l_log_module := C_DEFAULT_MODULE||'.run_fun_report';
948 END IF;
949 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
950 trace('run_report.Begin',C_LEVEL_PROCEDURE,l_log_module);
951 END IF;
952
953 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
954 trace
955 (p_msg => 'p_trans_ledger_id = '|| to_char(p_trans_ledger_id)
956 ,p_level => C_LEVEL_STATEMENT
957 ,p_module=> l_log_module);
958 trace
959 (p_msg => 'p_trans_legal_entity_id = '|| to_char(p_trans_legal_entity_id)
960 ,p_level => C_LEVEL_STATEMENT
961 ,p_module=> l_log_module);
962 trace
963 (p_msg => 'p_trans_gl_period = '|| p_trans_gl_period
964 ,p_level => C_LEVEL_STATEMENT
965 ,p_module=> l_log_module);
966 trace
967 (p_msg => 'p_tp_ledger_id = '|| to_char(p_tp_ledger_id)
968 ,p_level => C_LEVEL_STATEMENT
969 ,p_module=> l_log_module);
970 trace
971 (p_msg => 'p_tp_legal_entity_id = '|| to_char(p_tp_legal_entity_id)
972 ,p_level => C_LEVEL_STATEMENT
973 ,p_module=> l_log_module);
974 trace
975 (p_msg => 'p_currency = '|| p_currency
976 ,p_level => C_LEVEL_STATEMENT
977 ,p_module=> l_log_module);
978 trace
979 (p_msg => 'p_tp_gl_period = '|| p_tp_gl_period
980 ,p_level => C_LEVEL_STATEMENT
981 ,p_module=> l_log_module);
982 END IF;
983
984 -- Check conversion rate type entered for additional currency
985 IF p_currency IS NOT NULL AND p_rate_type IS NULL
986 THEN
987 FND_MESSAGE.SET_NAME('FUN', 'FUN_RECON_RATE_TYPE_REQD');
988 l_message := fnd_message.get;
989 p_errbuf := l_message;
990 p_retcode := 2;
991 RETURN;
992 END IF;
993
994 l_rate_date := TRUNC(Nvl(fnd_date.canonical_to_date(p_rate_date), sysdate));
995 --
996 -- get value for language
997 --
998 SELECT USERENV('LANG')
999 INTO l_lang
1000 FROM dual;
1001
1002 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1003 trace
1004 (p_msg =>'value of LANG ='||l_lang
1005 ,p_level =>C_LEVEL_STATEMENT
1006 ,p_module=>l_log_module);
1007 END IF;
1008
1009 -- Set SLA security such that details across applications can be accessed.
1010 xla_security_pkg.set_security_context(602);
1011
1012 l_array_sql.DELETE;
1013
1014 get_fun_parameter_sql
1015 (p_trans_ledger_id => p_trans_ledger_id
1016 ,p_trans_legal_entity_id => p_trans_legal_entity_id
1017 ,p_trans_gl_period => p_trans_gl_period
1018 ,p_tp_ledger_id => p_tp_ledger_id
1019 ,p_tp_legal_entity_id => p_tp_legal_entity_id
1020 ,p_currency => p_currency
1021 ,p_tp_gl_period => p_tp_gl_period
1022 ,p_rate_type => p_rate_type
1023 ,p_rate_date => p_rate_date
1024 ,p_array_sql => l_array_sql);
1025
1026 get_fun_main_sql
1027 (p_trans_ledger_id => p_trans_ledger_id
1028 ,p_trans_legal_entity_id => p_trans_legal_entity_id
1029 ,p_trans_gl_period => p_trans_gl_period
1030 ,p_tp_ledger_id => p_tp_ledger_id
1031 ,p_tp_legal_entity_id => p_tp_legal_entity_id
1032 ,p_currency => p_currency
1033 ,p_tp_gl_period => p_tp_gl_period
1034 ,p_rate_type => p_rate_type
1035 ,p_rate_date => l_rate_date
1036 ,p_array_sql => l_array_sql);
1037
1038 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
1039 fnd_file.put_line(fnd_file.output, '<?xml version="1.0" encoding="'||l_encoding||'"?>');
1040 fnd_file.put_line(fnd_file.output, '<REPORT_ROOT>');
1041
1042 FOR i IN 1..l_array_sql.COUNT LOOP
1043 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1044 trace
1045 (p_msg =>'Query('||to_char(i)||') = '||l_array_sql(i)
1046 ,p_level =>C_LEVEL_STATEMENT
1047 ,p_module=>l_log_module);
1048 END IF;
1049
1050 IF i = 1 THEN
1051 IF (C_LEVEL_EVENT >= g_log_level) THEN
1052 trace
1053 (p_msg =>'Start Genrating XML for Parameter'
1054 ,p_level =>C_LEVEL_EVENT
1055 ,p_module=>l_log_module);
1056 END IF;
1057
1058 l_para_Ctx := dbms_xmlgen.newContext(l_array_sql(i));
1059 DBMS_XMLGEN.setRowSetTag(l_para_Ctx,NULL);
1060 DBMS_XMLGEN.setRowTag(l_para_Ctx, 'PARAMETER');
1061 l_xml_clob := DBMS_XMLGEN.GETXML(l_para_Ctx);
1062 l_xml_clob:= substr(l_xml_clob,instr(l_xml_clob,'>')+1);
1063 DBMS_XMLGEN.closeContext(l_para_Ctx);
1064
1065 IF (C_LEVEL_EVENT >= g_log_level) THEN
1066 trace
1067 (p_msg =>'End of Genrating XML for Parameter'
1068 ,p_level =>C_LEVEL_EVENT
1069 ,p_module=>l_log_module);
1070 END IF;
1071
1072 ELSE --i>1
1073 IF (C_LEVEL_EVENT >= g_log_level) THEN
1074 trace
1075 (p_msg =>'Start Genrating XML for JE lines'
1076 ,p_level =>C_LEVEL_EVENT
1077 ,p_module=>l_log_module);
1078 END IF;
1079
1080 fnd_file.put_line(fnd_file.log, l_array_sql(i));
1081 fnd_file.put_line(fnd_file.log, ' ');
1082 fnd_file.put_line(fnd_file.log, '+===========================================================================+');
1083 fnd_file.put_line(fnd_file.log, ' ');
1084
1085 IF i = 2 -- forward relation query
1086 OR i = 6 -- reverse relation query
1087 THEN
1088 fnd_file.put_line(fnd_file.output, '<G_SUMMARY_ROWSET>');
1089
1090 l_ctx := DBMS_XMLGEN.newContext(l_array_sql(i));
1091 DBMS_XMLGEN.setRowSetTag(l_ctx,'G_SUMMARY_ROWSET');
1092 DBMS_XMLGEN.setRowTag(l_ctx, 'G_SUMMARY_ROW');
1093
1094 IF p_currency IS NOT NULL
1095 THEN
1096 DBMS_XMLGEN.setBindValue(l_ctx,'TO_CURR',p_currency);
1097 DBMS_XMLGEN.setBindValue(l_ctx,'TO_DATE',p_rate_date);
1098 DBMS_XMLGEN.setBindValue(l_ctx,'TYPE',p_rate_type);
1099 END IF;
1100
1101 ELSIF i = 3 -- forward relation query
1102 OR i = 7 -- reverse relation query
1103 THEN
1104 fnd_file.put_line(fnd_file.output, '<G_JRNLSOURCE_ROWSET>');
1105
1106 l_ctx := DBMS_XMLGEN.newContext(l_array_sql(i));
1107 DBMS_XMLGEN.setRowSetTag(l_ctx,'G_JRNLSOURCE_ROWSET');
1108 DBMS_XMLGEN.setRowTag(l_ctx, 'G_JRNLSOURCE_ROW');
1109
1110 ELSIF i = 4 -- forward relation query
1111 OR i = 8 -- reverse relation query
1112 THEN
1113 fnd_file.put_line(fnd_file.output, '<G_JRNLDETAILS_ROWSET>');
1114
1115 l_ctx := DBMS_XMLGEN.newContext(l_array_sql(i));
1116 DBMS_XMLGEN.setRowSetTag(l_ctx,'G_JRNLDETAILS_ROWSET');
1117 DBMS_XMLGEN.setRowTag(l_ctx, 'G_JOURNAL_ROW');
1118
1119 ELSIF i = 5 -- forward relation query
1120 OR i = 9 -- reverse relation query
1121 THEN
1122 fnd_file.put_line(fnd_file.output, '<G_SLADETAILS_ROWSET>');
1123
1124 l_ctx := DBMS_XMLGEN.newContext(l_array_sql(i));
1125 DBMS_XMLGEN.setRowSetTag(l_ctx,'G_SLADETAILS_ROWSET');
1126 DBMS_XMLGEN.setRowTag(l_ctx, 'G_SLA_ROW');
1127
1128
1129 ELSIF i = 10 -- forward relation query
1130 OR i = 13 -- reverse relation query
1131 THEN
1132 fnd_file.put_line(fnd_file.output, '<G_JRNLSOURCE_UNMATCHED_ROWSET>');
1133
1134 l_ctx := DBMS_XMLGEN.newContext(l_array_sql(i));
1135 DBMS_XMLGEN.setRowSetTag(l_ctx,'G_JRNLSOURCE_UNMATCHED_ROWSET');
1136 DBMS_XMLGEN.setRowTag(l_ctx, 'G_JRNLSOURCE_UNMATCHED_ROW');
1137
1138 ELSIF i = 11 -- forward relation query
1139 OR i = 14 -- reverse relation query
1140 THEN
1141 fnd_file.put_line(fnd_file.output, '<G_JRNLDETAILS_UNMATCHED_ROWSET>');
1142
1143 l_ctx := DBMS_XMLGEN.newContext(l_array_sql(i));
1144 DBMS_XMLGEN.setRowSetTag(l_ctx,'G_JRNLDETAILS_UNMATCHED_ROWSET');
1145 DBMS_XMLGEN.setRowTag(l_ctx, 'G_JOURNAL_UNMATCHED_ROW');
1146
1147 ELSIF i = 12 -- forward relation query
1148 OR i = 15 -- reverse relation query
1149 THEN
1150 fnd_file.put_line(fnd_file.output, '<G_SLADETAILS_UNMATCHED_ROWSET>');
1151
1152 l_ctx := DBMS_XMLGEN.newContext(l_array_sql(i));
1153 DBMS_XMLGEN.setRowSetTag(l_ctx,'G_SLADETAILS_UNMATCHED_ROWSET');
1154 DBMS_XMLGEN.setRowTag(l_ctx, 'G_SLA_UNMATCHED_ROW');
1155
1156 END IF;
1157
1158 l_xml_clob := DBMS_XMLGEN.GETXML(l_ctx);
1159 l_xml_clob:= substr(l_xml_clob,instr(l_xml_clob,'>',1,2)+1);
1160 DBMS_XMLGEN.closeContext(l_ctx);
1161
1162 IF (C_LEVEL_EVENT >= g_log_level) THEN
1163 trace
1164 (p_msg =>'End of Genrating XML for JE lines'
1165 ,p_level =>C_LEVEL_EVENT
1166 ,p_module=>l_log_module);
1167 END IF;
1168 END IF;
1169
1170 IF l_xml_clob IS NULL THEN
1171 IF i = 2 OR i = 6 THEN
1172 fnd_file.put_line(fnd_file.output, '</G_SUMMARY_ROWSET>');
1173 ELSIF i = 3 OR i = 7 THEN
1174 fnd_file.put_line(fnd_file.output, '</G_JRNLSOURCE_ROWSET>');
1175 ELSIF i = 4 OR i = 8 THEN
1176 fnd_file.put_line(fnd_file.output, '</G_JRNLDETAILS_ROWSET>');
1177 ELSIF i = 5 OR i = 9 THEN
1178 fnd_file.put_line(fnd_file.output, '</G_SLADETAILS_ROWSET>');
1179 ELSIF i = 10 OR i = 13 THEN
1180 fnd_file.put_line(fnd_file.output, '</G_JRNLSOURCE_UNMATCHED_ROWSET>');
1181 ELSIF i = 11 OR i = 14 THEN
1182 fnd_file.put_line(fnd_file.output, '</G_JRNLDETAILS_UNMATCHED_ROWSET>');
1183 ELSIF i = 12 OR i = 15 THEN
1184 fnd_file.put_line(fnd_file.output, '</G_SLADETAILS_UNMATCHED_ROWSET>');
1185 END IF;
1186 END IF;
1187
1188 fun_recon_rpt_pkg.clob_to_file
1189 (p_xml_clob => l_xml_clob);
1190 END LOOP;
1191
1192 fnd_file.put_line(fnd_file.output, '</REPORT_ROOT>');
1193
1194 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1195 trace
1196 (p_msg =>'run_fun_report.End'
1197 ,p_level =>C_LEVEL_PROCEDURE
1198 ,p_module=>l_Log_module);
1199 END IF;
1200 EXCEPTION
1201 WHEN OTHERS THEN
1202 FUN_UTIL.log_conc_unexp(C_DEFAULT_MODULE, 'run_fun_report');
1203 RAISE;
1204 END run_fun_report;
1205
1206
1207 --=============================================================================
1208 --
1209 -- PROCEDURE GET_FUN_PARAMETER_SQL
1210 --
1211 --=============================================================================
1212 PROCEDURE get_fun_parameter_sql
1213 (p_trans_ledger_id IN NUMBER
1214 ,p_trans_legal_entity_id IN NUMBER
1215 ,p_trans_gl_period IN VARCHAR2
1216 ,p_tp_ledger_id IN NUMBER
1217 ,p_tp_legal_entity_id IN NUMBER
1218 ,p_currency IN VARCHAR2
1219 ,p_tp_gl_period IN VARCHAR2
1220 ,p_rate_type IN VARCHAR2
1221 ,p_rate_date IN VARCHAR2
1222 ,p_array_sql IN OUT NOCOPY T_ARRAY_CHAR) IS
1223
1224 l_param_query VARCHAR2(2000);
1225 l_index NUMBER;
1226 l_log_module VARCHAR2(240);
1227 BEGIN
1228 IF g_log_enabled THEN
1229 l_log_module := C_DEFAULT_MODULE||'.get_fun_parameter_sql';
1230 END IF;
1231 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1232 trace
1233 (p_msg => 'get_fun_parameter_sql.Begin'
1234 ,p_level => C_LEVEL_PROCEDURE
1235 ,p_module=> l_Log_module );
1236 END IF;
1237
1238 l_param_query := 'Select '
1239 ||''''||p_trans_ledger_id ||''''||' p_trans_ledger_id,'
1240 ||''''||p_trans_legal_entity_id ||''''||' p_TRANS_LEGAL_ENTITY_ID,'
1241 ||''''||p_trans_gl_period ||''''||' p_trans_gl_period,'
1242 ||''''||p_tp_ledger_id ||''''||' p_tp_ledger_id,'
1243 ||''''||p_tp_legal_entity_id ||''''||' p_TP_LEGAL_ENTITY_ID,'
1244 ||''''||p_currency ||''''||' p_CURRENCY,'
1245 ||''''||p_tp_gl_period ||''''||' p_tp_gl_period,'
1246 ||''''||p_rate_type ||''''||' p_rate_type,'
1247 ||''''||to_char(fnd_date.canonical_to_date(p_rate_date)) ||''''||' p_rate_date'
1248 ||' FROM DUAL ';
1249
1250 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1251 trace
1252 (p_msg => 'Query for parameter value ='||l_param_query
1253 ,p_level => C_LEVEL_STATEMENT
1254 ,p_module=>l_Log_module );
1255 END IF;
1256
1257 l_index := p_array_sql.count + 1;
1258 p_array_sql(l_index):= l_param_query;
1259
1260 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1261 trace
1262 (p_msg =>'get_fun_parameter_sql.End'
1263 ,p_level => C_LEVEL_PROCEDURE
1264 ,p_module=>l_Log_module);
1265 END IF;
1266 EXCEPTION
1267 WHEN OTHERS THEN
1268 FUN_UTIL.log_conc_unexp(C_DEFAULT_MODULE, 'get_fun_parameter_sql');
1269 RAISE;
1270 END get_fun_parameter_sql;
1271
1272
1273 --=============================================================================
1274 --
1275 -- PROCEDURE GET_FUN_MAIN_SQL
1276 --
1277 --=============================================================================
1278 PROCEDURE get_fun_main_sql
1279 (p_trans_ledger_id IN NUMBER
1280 ,p_trans_legal_entity_id IN NUMBER
1281 ,p_trans_gl_period IN VARCHAR2
1282 ,p_tp_ledger_id IN NUMBER
1283 ,p_tp_legal_entity_id IN NUMBER
1284 ,p_currency IN VARCHAR2
1285 ,p_tp_gl_period IN VARCHAR2
1286 ,p_rate_type IN VARCHAR2
1287 ,p_rate_date IN DATE
1288 ,p_array_sql IN OUT NOCOPY T_ARRAY_CHAR) IS
1289
1290 CURSOR c_get_prd_end_date (p_ledger_id NUMBER,
1291 p_period_name VARCHAR2)
1292 IS
1293 SELECT glp.start_date,
1294 glp.end_date
1295 FROM gl_periods glp,
1296 gl_ledgers gl
1297 WHERE glp.period_set_name = gl.period_set_name
1298 AND glp.period_type = gl.accounted_period_type
1299 AND glp.period_name = p_period_name
1300 AND gl.ledger_id = p_ledger_id;
1301
1302
1303 l_get_account_query VARCHAR2(32000);
1304 l_jelines_sla_query VARCHAR2(32000);
1305 l_jelines_gl_query VARCHAR2(32000);
1306 l_jelines_sum_query VARCHAR2(32000);
1307 l_gl_balances_query VARCHAR2(32000);
1308 l_gl_balances_query1 VARCHAR2(32000);
1309 l_sum_unmatched_query VARCHAR2(32000);
1310 l_gl_unmatched_query VARCHAR2(32000);
1311 l_sla_unmatched_query VARCHAR2(32000);
1312
1313 l_add_currency_cols VARCHAR2(32000);
1314
1315 l_coa_id NUMBER;
1316
1317 l_get_account_query_rev VARCHAR2(32000);
1318 l_jelines_sla_query_rev VARCHAR2(32000);
1319 l_jelines_gl_query_rev VARCHAR2(32000);
1320 l_jelines_sum_query_rev VARCHAR2(32000);
1321 l_gl_balances_query_rev VARCHAR2(32000);
1322 l_gl_balances_query_rev1 VARCHAR2(32000);
1323 l_sum_unmatched_query_rev VARCHAR2(32000);
1324 l_gl_unmatched_query_rev VARCHAR2(32000);
1325 l_sla_unmatched_query_rev VARCHAR2(32000);
1326
1327 l_period_end_date DATE;
1328 l_period_start_date DATE;
1329
1330 l_balancing_segment VARCHAR2(80);
1331 l_account_segment VARCHAR2(80);
1332 l_costcenter_segment VARCHAR2(80);
1333 l_management_segment VARCHAR2(80);
1334 l_intercompany_segment VARCHAR2(80);
1335
1336 l_alias_balancing_segment VARCHAR2(80);
1337 l_alias_account_segment VARCHAR2(80);
1338 l_alias_costcenter_segment VARCHAR2(80);
1339 l_alias_management_segment VARCHAR2(80);
1340 l_alias_intercompany_segment VARCHAR2(80);
1341 l_trx_source_view_columns VARCHAR2(4000);
1342 l_trx_source_view_name VARCHAR2(240);
1343 l_trx_source_view_join VARCHAR2(4000);
1344 l_other_param_filter VARCHAR2(4000);
1345 l_sla_other_filter VARCHAR2(1000);
1346 l_gl_other_filter VARCHAR2(1000);
1347 l_seg_desc_column VARCHAR2(4000);
1348 l_seg_desc_from VARCHAR2(4000);
1349 l_seg_desc_join VARCHAR2(4000);
1350
1351 l_anc_view_columns VARCHAR2(4000);
1352 l_anc_view_name VARCHAR2(240);
1353 l_anc_view_join VARCHAR2(4000);
1354
1355 l_le_columns VARCHAR2(4000);
1356 l_le_view VARCHAR2(4000);
1357 l_le_view_join VARCHAR2(4000);
1358
1359 l_index NUMBER;
1360 l_log_module VARCHAR2(240);
1361
1362 l_add_where_clause1 VARCHAR2(4000);
1363
1364 BEGIN
1365 IF g_log_enabled THEN
1366 l_log_module := C_DEFAULT_MODULE||'.get_fun_main_sql';
1367 END IF;
1368
1369 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1370 trace('get_fun_main_sql.Begin',C_LEVEL_PROCEDURE,l_Log_module);
1371 END IF;
1372
1373 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1374 trace
1375 (p_msg => 'p_trans_ledger_id = '|| to_char(p_trans_ledger_id)
1376 ,p_level => C_LEVEL_STATEMENT
1377 ,p_module=> l_log_module);
1378 trace
1379 (p_msg => 'p_trans_legal_entity_id = '|| to_char(p_trans_legal_entity_id)
1380 ,p_level => C_LEVEL_STATEMENT
1381 ,p_module=> l_log_module);
1382 trace
1383 (p_msg => 'p_trans_gl_period = '|| p_trans_gl_period
1384 ,p_level => C_LEVEL_STATEMENT
1385 ,p_module=> l_log_module);
1386 trace
1387 (p_msg => 'p_tp_ledger_id = '|| to_char(p_tp_ledger_id)
1388 ,p_level => C_LEVEL_STATEMENT
1389 ,p_module=> l_log_module);
1390 trace
1391 (p_msg => 'p_tp_legal_entity_id = '|| to_char(p_tp_legal_entity_id)
1392 ,p_level => C_LEVEL_STATEMENT
1393 ,p_module=> l_log_module);
1394 trace
1395 (p_msg => 'p_currency = '|| p_currency
1396 ,p_level => C_LEVEL_STATEMENT
1397 ,p_module=> l_log_module);
1398 trace
1399 (p_msg => 'p_tp_gl_period = '|| p_tp_gl_period
1400 ,p_level => C_LEVEL_STATEMENT
1401 ,p_module=> l_log_module);
1402 trace
1403 (p_msg => 'p_array_sql.count = '||p_array_sql.count
1404 ,p_level => C_LEVEL_STATEMENT
1405 ,p_module=> l_Log_module);
1406 END IF;
1407
1408 -- get the period end date of the transacting ledger
1409 -- as we might need to use it to find the corresponding
1410 -- period in the trading ledger
1411 OPEN c_get_prd_end_date (p_trans_ledger_id,
1412 p_trans_gl_period);
1413
1414 FETCH c_get_prd_end_date INTO l_period_start_date,
1415 l_period_end_date;
1416 CLOSE c_get_prd_end_date;
1417
1418 --==========================================================================
1419 -- building qeury to fetch source summary from GL,
1420 -- summary balances from GL,
1421 -- journal line details from SLA and GL
1422 --==========================================================================
1423
1424 l_get_account_query := C_FUN_GET_ACCTS_QUERY;
1425 l_jelines_sum_query := C_FUN_JELINES_SUM_QUERY;
1426 l_sum_unmatched_query := C_SUM_UNMATCHED_QUERY;
1427 l_gl_balances_query := C_FUN_GL_BALANCE_QUERY;
1428 l_gl_balances_query1 := C_FUN_GL_BALANCE_QUERY1;
1429 l_jelines_gl_query := C_FUN_JELINES_GL_QUERY;
1430 l_gl_unmatched_query := C_GL_UNMATCHED_QUERY;
1431 l_jelines_sla_query := C_FUN_JELINES_SLA_QUERY;
1432 l_sla_unmatched_query := C_SLA_UNMATCHED_QUERY;
1433 l_add_currency_cols := C_ADD_CURRENCY_COLS;
1434
1435
1436 IF p_trans_ledger_id IS NOT NULL -- User input parameter
1437 THEN
1438 l_gl_balances_query := l_gl_balances_query || ' AND fia1.ledger_id = '||p_trans_ledger_id;
1439 l_get_account_query := l_get_account_query || ' AND fia1.ledger_id = '||p_trans_ledger_id;
1440 l_jelines_sum_query := l_jelines_sum_query || ' AND gjl.ledger_id = '||p_trans_ledger_id;
1441 l_sum_unmatched_query := l_sum_unmatched_query || ' AND SRC_TRANS_LEDGER_ID = '||p_trans_ledger_id;
1442 l_jelines_gl_query := l_jelines_gl_query || ' AND gjl.ledger_id = '||p_trans_ledger_id;
1443 l_gl_unmatched_query := l_gl_unmatched_query || ' AND fia.ledger_id = '||p_trans_ledger_id;
1444 l_jelines_sla_query := l_jelines_sla_query || ' AND aeh.ledger_id = '||p_trans_ledger_id;
1445 l_sla_unmatched_query := l_sla_unmatched_query || ' AND fia.ledger_id = '||p_trans_ledger_id;
1446 END IF;
1447
1448 IF P_TRANS_LEGAL_ENTITY_ID IS NOT NULL -- User input parameter
1449 THEN
1450 l_gl_balances_query := l_gl_balances_query || ' AND fia1.from_le_id = '||P_TRANS_LEGAL_ENTITY_ID ;
1451 l_get_account_query := l_get_account_query || ' AND fia1.from_le_id = '||P_TRANS_LEGAL_ENTITY_ID ;
1452 l_sum_unmatched_query := l_sum_unmatched_query || ' AND SRC_TRANS_LE_ID = '||P_TRANS_LEGAL_ENTITY_ID ;
1453 l_gl_unmatched_query := l_gl_unmatched_query || ' AND fia.from_le_id = '||P_TRANS_LEGAL_ENTITY_ID ;
1454 l_sla_unmatched_query := l_sla_unmatched_query || ' AND fia.from_le_id = '||P_TRANS_LEGAL_ENTITY_ID ;
1455
1456 END IF;
1457
1458 IF p_trans_gl_period IS NOT NULL -- User input parameter
1459 THEN
1460 l_gl_balances_query := l_gl_balances_query|| ' AND glb1.period_name = '''||p_trans_gl_period ||'''' ;
1461 l_jelines_sum_query := l_jelines_sum_query || ' AND gjl.period_name = '''||p_trans_gl_period ||'''' ;
1462 l_sum_unmatched_query := l_sum_unmatched_query || ' AND PERIOD_NAME = '''||p_trans_gl_period ||'''' ;
1463 l_jelines_gl_query := l_jelines_gl_query || ' AND gjl.period_name = '''||p_trans_gl_period ||'''' ;
1464 l_gl_unmatched_query := l_gl_unmatched_query || ' AND gjl.period_name = '''||p_trans_gl_period ||'''' ;
1465 l_jelines_sla_query := l_jelines_sla_query || ' AND aeh.period_name = '''||p_trans_gl_period ||'''' ;
1466 l_jelines_sla_query := l_jelines_sla_query || ' AND aeh.accounting_date BETWEEN '''||l_period_start_date ||''' AND ''' || l_period_end_date || '''';
1467 l_sla_unmatched_query := l_sla_unmatched_query || ' AND aeh.period_name = '''||p_trans_gl_period ||'''' ;
1468 l_sla_unmatched_query := l_sla_unmatched_query || ' AND aeh.accounting_date BETWEEN '''||l_period_start_date ||''' AND ''' || l_period_end_date || '''';
1469 END IF;
1470
1471 IF p_tp_ledger_id IS NOT NULL -- User input parameter
1472 THEN
1473 l_gl_balances_query := l_gl_balances_query || ' AND fia2.ledger_id = '||p_tp_ledger_id ;
1474
1475 l_get_account_query := l_get_account_query || ' AND fia2.ledger_id = '||p_tp_ledger_id ;
1476
1477
1478 l_sum_unmatched_query := l_sum_unmatched_query || ' AND SRC_TRAD_LE_ID IN (SELECT tole.legal_entity_id
1479 FROM gl_ledger_le_v tole
1480 WHERE tole.ledger_id = '||p_tp_ledger_id ||')';
1481
1482
1483 l_gl_unmatched_query := l_gl_unmatched_query || ' AND fia.to_le_id IN (SELECT tole.legal_entity_id
1484 FROM gl_ledger_le_v tole
1485 WHERE tole.ledger_id = '||p_tp_ledger_id ||')';
1486
1487 l_sla_unmatched_query := l_sla_unmatched_query || ' AND fia.to_le_id IN (SELECT tole.legal_entity_id
1488 FROM gl_ledger_le_v tole
1489 WHERE tole.ledger_id = '||p_tp_ledger_id ||')';
1490 END IF;
1491
1492 IF P_TP_LEGAL_ENTITY_ID IS NOT NULL -- User input parameter
1493 THEN
1494 l_gl_balances_query := l_gl_balances_query || ' AND fia1.to_le_id = '||P_TP_LEGAL_ENTITY_ID ;
1495 l_get_account_query := l_get_account_query || ' AND fia1.to_le_id = '||P_TP_LEGAL_ENTITY_ID ;
1496 l_sum_unmatched_query := l_sum_unmatched_query || ' AND SRC_TRAD_LE_ID = '||P_TP_LEGAL_ENTITY_ID ;
1497 l_gl_unmatched_query := l_gl_unmatched_query || ' AND fia.to_le_id = '||P_TP_LEGAL_ENTITY_ID ;
1498 l_sla_unmatched_query := l_sla_unmatched_query || ' AND fia.to_le_id = '||P_TP_LEGAL_ENTITY_ID ;
1499 END IF;
1500
1501
1502 IF P_TP_GL_PERIOD IS NOT NULL -- User input parameter
1503 THEN
1504 l_gl_balances_query := l_gl_balances_query || ' AND glp2.period_name = '''||p_tp_gl_period ||'''' ;
1505 ELSE
1506 -- find corresponding GL period in the trading ledger side.
1507 l_gl_balances_query := l_gl_balances_query ||
1508 ' AND ''' ||l_period_end_date||''' BETWEEN glp2.start_date and glp2.end_date
1509 AND glp2.adjustment_period_flag = glp1.adjustment_period_flag';
1510 END IF;
1511
1512 IF P_CURRENCY IS NOT NULL
1513 THEN
1514 l_gl_balances_query1 := REPLACE(l_gl_balances_query1,
1515 '$additional_currency_columns$',
1516 l_add_currency_cols);
1517 ELSE
1518 l_gl_balances_query1 := REPLACE(l_gl_balances_query1,
1519 '$additional_currency_columns$',
1520 ',NULL');
1521 END IF;
1522
1523 l_gl_balances_query1 := REPLACE(l_gl_balances_query1,
1524 '$sub_query$',
1525 l_gl_balances_query);
1526
1527
1528 IF P_TP_GL_PERIOD IS NULL
1529 THEN
1530 l_add_where_clause1 := ' AND FUN_RECON_RPT_PKG.match_ap_ar_invoice(fia.from_le_id, fia.ledger_id, glp.period_name, fia.to_le_id, '
1531 || nvl(to_char(p_tp_ledger_id), 'null') || ', null , ' ||
1532 ' fia.type, ent.entity_code, ent.source_id_int_1) = ''UNMATCHED'' ';
1533 ELSE
1534 l_add_where_clause1 := ' AND FUN_RECON_RPT_PKG.match_ap_ar_invoice(fia.from_le_id, fia.ledger_id, glp.period_name, fia.to_le_id, ' ||
1535 nvl(to_char(p_tp_ledger_id), 'null') || ', '''||P_TP_GL_PERIOD ||''' , ' ||
1536 ' fia.type, ent.entity_code, ent.source_id_int_1) = ''UNMATCHED'' ';
1537 END IF;
1538
1539
1540 l_sum_unmatched_query := REPLACE(l_sum_unmatched_query, '$where_clause1$', l_add_where_clause1);
1541 l_sla_unmatched_query := l_sla_unmatched_query || l_add_where_clause1;
1542
1543 l_gl_balances_query1 := l_gl_balances_query1 ||
1544 ' ORDER BY TRANSACTING_LE, TRADING_PARTNER_LE, TRANSACTION_CURRENCY';
1545
1546
1547 l_jelines_sum_query := REPLACE (l_jelines_sum_query,
1548 '$get_accounts_query$',
1549 l_get_account_query);
1550
1551 l_jelines_sum_query := l_jelines_sum_query ||
1552 ' GROUP BY fun_act.transacting_le_id, '||
1553 ' fun_act.transacting_le, '||
1554 ' fun_act.trading_partner_le_id , '||
1555 ' fun_act.trading_partner_le, '||
1556 ' fun_act.transacting_ledger , '||
1557 ' fun_act.transacting_ledger_id, '||
1558 ' gjh.currency_code, '||
1559 ' gjl.period_name , '||
1560 ' gjst.user_je_source_name, '||
1561 ' gjct.user_je_category_name '||
1562 'ORDER BY fun_act.transacting_le_id, '||
1563 ' fun_act.transacting_le, '||
1564 ' fun_act.trading_partner_le_id , '||
1565 ' fun_act.trading_partner_le, '||
1566 ' fun_act.transacting_ledger , '||
1567 ' fun_act.transacting_ledger_id, '||
1568 ' gjh.currency_code, '||
1569 ' gjl.period_name , '||
1570 ' gjst.user_je_source_name, '||
1571 ' gjct.user_je_category_name ';
1572
1573
1574 l_sum_unmatched_query := l_sum_unmatched_query ||
1575 ' GROUP BY SRC_TRANS_LE_ID, SRC_TRANS_LE, SRC_TRAD_LE_ID, SRC_TRAD_LE,
1576 SRC_TRANS_LEDGER, TRX_CURR, PERIOD_NAME, JOURNAL_SOURCE, JOURNAL_CATEGORY
1577 ORDER BY SRC_TRANS_LE, SRC_TRAD_LE, TRX_CURR, JOURNAL_SOURCE, JOURNAL_CATEGORY ';
1578
1579 l_jelines_gl_query := REPLACE (l_jelines_gl_query,
1580 '$get_accounts_query$',
1581 l_get_account_query);
1582
1583 l_jelines_gl_query := l_jelines_gl_query || '
1584 ORDER BY TRANS_LE, TRAD_LE, ENTERED_CURRENCY, JE_SOURCE_NAME, JE_CATEGORY_NAME, HEADER_ID, GL_LINE_NUMBER';
1585
1586 l_gl_unmatched_query := l_gl_unmatched_query || '
1587 ORDER BY TRANS_LE, TRAD_LE, ENTERED_CURRENCY, JE_SOURCE_NAME, JE_CATEGORY_NAME, HEADER_ID, GL_LINE_NUMBER';
1588
1589 l_jelines_sla_query := REPLACE (l_jelines_sla_query,
1590 '$get_accounts_query$',
1591 l_get_account_query);
1592
1593 l_jelines_sla_query := l_jelines_sla_query || '
1594 ORDER BY TRANS_LE, TRAD_LE, ENTERED_CURRENCY, JE_SOURCE_NAME, JE_CATEGORY_NAME, HEADER_ID, SLA_LINE_NUMBER';
1595
1596 l_sla_unmatched_query := l_sla_unmatched_query || '
1597 ORDER BY TRANS_LE, TRAD_LE, ENTERED_CURRENCY, JE_SOURCE_NAME, JE_CATEGORY_NAME, HEADER_ID, SLA_LINE_NUMBER';
1598
1599
1600 -- Now do the queries to get the reverse side of the relationship
1601 -- so this is where the transacting_ledger becomes the trading_ledger
1602 -- and the trading ledger is now the transacting ledger.
1603
1604 l_gl_balances_query_rev := C_FUN_GL_BALANCE_QUERY;
1605 l_gl_balances_query_rev1 := C_FUN_GL_BALANCE_QUERY1;
1606 l_get_account_query_rev := C_FUN_GET_ACCTS_QUERY;
1607 l_jelines_sum_query_rev := C_FUN_JELINES_SUM_QUERY;
1608 l_sum_unmatched_query_rev:= C_SUM_UNMATCHED_QUERY;
1609 l_jelines_gl_query_rev := C_FUN_JELINES_GL_QUERY;
1610 l_gl_unmatched_query_rev := C_GL_UNMATCHED_QUERY;
1611 l_jelines_sla_query_rev := C_FUN_JELINES_SLA_QUERY;
1612 l_sla_unmatched_query_rev := C_SLA_UNMATCHED_QUERY;
1613 l_add_currency_cols := C_ADD_CURRENCY_COLS;
1614
1615
1616 IF p_trans_ledger_id IS NOT NULL -- User input parameter
1617 THEN
1618 l_gl_balances_query_rev := l_gl_balances_query_rev || ' AND fia2.ledger_id = '||p_trans_ledger_id;
1619
1620 l_get_account_query_rev := l_get_account_query_rev || ' AND fia2.ledger_id = '||p_trans_ledger_id;
1621
1622
1623 l_sum_unmatched_query_rev := l_sum_unmatched_query_rev || ' AND SRC_TRAD_LE_ID IN (SELECT tole.legal_entity_id
1624 FROM gl_ledger_le_v tole
1625 WHERE tole.ledger_id = '||p_trans_ledger_id ||')';
1626
1627 l_gl_unmatched_query_rev := l_gl_unmatched_query_rev || ' AND fia.to_le_id IN (SELECT tole.legal_entity_id
1628 FROM gl_ledger_le_v tole
1629 WHERE tole.ledger_id = '||p_trans_ledger_id ||')';
1630
1631 l_sla_unmatched_query_rev := l_sla_unmatched_query_rev || ' AND fia.to_le_id IN (SELECT tole.legal_entity_id
1632 FROM gl_ledger_le_v tole
1633 WHERE tole.ledger_id = '||p_trans_ledger_id ||')';
1634
1635 END IF;
1636
1637 IF p_trans_legal_entity_id IS NOT NULL -- User input parameter
1638 THEN
1639 l_gl_balances_query_rev := l_gl_balances_query_rev || ' AND fia2.from_le_id = '||P_TRANS_LEGAL_ENTITY_ID ;
1640 l_get_account_query_rev := l_get_account_query_rev || ' AND fia2.from_le_id = '||P_TRANS_LEGAL_ENTITY_ID ;
1641 l_sum_unmatched_query_rev := l_sum_unmatched_query_rev || ' AND SRC_TRAD_LE_ID = '||P_TRANS_LEGAL_ENTITY_ID ;
1642 l_gl_unmatched_query_rev := l_gl_unmatched_query_rev || ' AND fia.to_le_id = '||P_TRANS_LEGAL_ENTITY_ID ;
1643 l_sla_unmatched_query_rev := l_sla_unmatched_query_rev || ' AND fia.to_le_id = '||P_TRANS_LEGAL_ENTITY_ID ;
1644 END IF;
1645
1646 IF p_trans_gl_period IS NOT NULL -- User input parameter
1647 THEN
1648 l_gl_balances_query_rev := l_gl_balances_query_rev|| ' AND glp2.period_name = '''||p_trans_gl_period ||'''' ;
1649 l_jelines_sla_query_rev := l_jelines_sla_query_rev || ' AND aeh.accounting_date BETWEEN '''||l_period_start_date ||''' AND ''' || l_period_end_date || '''';
1650 l_sla_unmatched_query_rev := l_sla_unmatched_query_rev ||' AND aeh.accounting_date BETWEEN '''||l_period_start_date ||''' AND ''' || l_period_end_date || '''';
1651 END IF;
1652
1653 IF p_tp_ledger_id IS NOT NULL -- User input parameter
1654 THEN
1655 l_gl_balances_query_rev := l_gl_balances_query_rev || ' AND fia1.ledger_id = '||p_tp_ledger_id ;
1656 l_get_account_query_rev := l_get_account_query_rev || ' AND fia1.ledger_id = '||p_tp_ledger_id;
1657 l_jelines_sum_query_rev := l_jelines_sum_query_rev || ' AND gjl.ledger_id = '||p_tp_ledger_id;
1658 l_sum_unmatched_query_rev := l_sum_unmatched_query_rev || ' AND SRC_TRANS_LEDGER_ID = '||p_tp_ledger_id;
1659 l_jelines_gl_query_rev := l_jelines_gl_query_rev || ' AND gjl.ledger_id = '||p_tp_ledger_id;
1660 l_gl_unmatched_query_rev := l_gl_unmatched_query_rev || ' AND fia.ledger_id = '||p_tp_ledger_id;
1661 l_jelines_sla_query_rev := l_jelines_sla_query_rev || ' AND aeh.ledger_id = '||p_tp_ledger_id;
1662 l_sla_unmatched_query_rev := l_sla_unmatched_query_rev || ' AND fia.ledger_id = '||p_tp_ledger_id;
1663 END IF;
1664
1665
1666
1667 IF P_TP_LEGAL_ENTITY_ID IS NOT NULL -- User input parameter
1668 THEN
1669 l_gl_balances_query_rev := l_gl_balances_query_rev || ' AND fia1.from_le_id = '||P_TP_LEGAL_ENTITY_ID ;
1670 l_get_account_query_rev := l_get_account_query_rev || ' AND fia1.from_le_id = '||P_TP_LEGAL_ENTITY_ID ;
1671 l_gl_unmatched_query_rev := l_gl_unmatched_query_rev || ' AND fia.from_le_id = '||P_TP_LEGAL_ENTITY_ID ;
1672 l_sla_unmatched_query_rev := l_sla_unmatched_query_rev || ' AND fia.from_le_id = '||P_TP_LEGAL_ENTITY_ID ;
1673 END IF;
1674
1675
1676 IF p_tp_gl_period IS NOT NULL -- User input parameter
1677 THEN
1678 l_gl_balances_query_rev := l_gl_balances_query_rev || ' AND glb1.period_name = '''||p_tp_gl_period ||'''' ;
1679 l_jelines_sum_query_rev := l_jelines_sum_query_rev || ' AND gjl.period_name = '''||p_tp_gl_period ||'''' ;
1680 l_sum_unmatched_query_rev := l_sum_unmatched_query_rev || ' AND PERIOD_NAME = '''||p_tp_gl_period ||'''' ;
1681 l_jelines_gl_query_rev := l_jelines_gl_query_rev || ' AND gjl.period_name = '''||p_tp_gl_period ||'''' ;
1682 l_gl_unmatched_query_rev := l_gl_unmatched_query_rev || ' AND gjl.period_name = '''||p_tp_gl_period ||'''' ;
1683 l_jelines_sla_query_rev := l_jelines_sla_query_rev || ' AND aeh.period_name = '''||p_tp_gl_period ||'''' ;
1684 l_jelines_sla_query_rev := l_jelines_sla_query_rev || ' AND aeh.accounting_date BETWEEN '''||l_period_start_date ||''' AND ''' || l_period_end_date || '''';
1685 l_sla_unmatched_query_rev := l_sla_unmatched_query_rev || ' AND aeh.period_name = '''||p_tp_gl_period ||'''' ;
1686 l_sla_unmatched_query_rev := l_sla_unmatched_query_rev || ' AND aeh.accounting_date BETWEEN '''||l_period_start_date ||''' AND ''' || l_period_end_date || '''';
1687 ELSE
1688 -- find corresponding GL period in the relating ledger side.
1689 l_gl_balances_query_rev := l_gl_balances_query_rev ||
1690 ' AND ''' ||l_period_end_date||''' BETWEEN glp1.start_date and glp1.end_date
1691 AND glp1.adjustment_period_flag = glp2.adjustment_period_flag';
1692
1693 l_jelines_sum_query_rev := l_jelines_sum_query_rev ||
1694 ' AND ''' ||l_period_end_date||''' BETWEEN glp.start_date and glp.end_date';
1695
1696 l_sum_unmatched_query_rev := l_sum_unmatched_query_rev ||
1697 ' AND ''' ||l_period_end_date||''' BETWEEN START_DATE and END_DATE';
1698
1699 l_jelines_gl_query_rev := l_jelines_gl_query_rev ||
1700 ' AND ''' ||l_period_end_date||''' BETWEEN glp.start_date and glp.end_date ';
1701
1702 l_gl_unmatched_query_rev := l_gl_unmatched_query_rev ||
1703 ' AND ''' ||l_period_end_date||''' BETWEEN glp.start_date and glp.end_date ';
1704
1705 l_jelines_sla_query_rev := l_jelines_sla_query_rev ||
1706 ' AND ''' ||l_period_end_date||''' BETWEEN glp.start_date and glp.end_date ';
1707
1708 l_sla_unmatched_query_rev := l_sla_unmatched_query_rev ||
1709 ' AND ''' ||l_period_end_date||''' BETWEEN glp.start_date and glp.end_date ';
1710
1711
1712 END IF;
1713
1714 IF p_currency IS NOT NULL
1715 THEN
1716 l_gl_balances_query_rev1 := REPLACE(l_gl_balances_query_rev1,
1717 '$additional_currency_columns$',
1718 l_add_currency_cols);
1719 ELSE
1720 l_gl_balances_query_rev1 := REPLACE(l_gl_balances_query_rev1,
1721 '$additional_currency_columns$',
1722 ',NULL');
1723 END IF;
1724
1725 l_gl_balances_query_rev1 := REPLACE(l_gl_balances_query_rev1,
1726 '$sub_query$',
1727 l_gl_balances_query_rev);
1728
1729
1730 l_add_where_clause1 := ' AND FUN_RECON_RPT_PKG.match_ap_ar_invoice(fia.from_le_id, fia.ledger_id, glp.period_name, fia.to_le_id, ' ||
1731 nvl(to_char(p_trans_ledger_id), 'null') || ', ''' || p_trans_gl_period||''' , ' ||
1732 ' fia.type, ent.entity_code, ent.source_id_int_1) = ''UNMATCHED'' ';
1733
1734 l_sum_unmatched_query_rev := REPLACE(l_sum_unmatched_query_rev, '$where_clause1$', l_add_where_clause1);
1735 l_sla_unmatched_query_rev := l_sla_unmatched_query_rev || l_add_where_clause1;
1736
1737 l_gl_balances_query_rev1 := l_gl_balances_query_rev1 ||
1738 ' ORDER BY TRANSACTING_LE, TRADING_PARTNER_LE, TRANSACTION_CURRENCY';
1739
1740 l_jelines_sum_query_rev := REPLACE(l_jelines_sum_query_rev,
1741 '$get_accounts_query$',
1742 l_get_account_query_rev);
1743
1744 l_jelines_sum_query_rev := l_jelines_sum_query_rev ||
1745 ' GROUP BY fun_act.transacting_le_id, '||
1746 ' fun_act.transacting_le, '||
1747 ' fun_act.trading_partner_le_id , '||
1748 ' fun_act.trading_partner_le, '||
1749 ' fun_act.transacting_ledger , '||
1750 ' fun_act.transacting_ledger_id, '||
1751 ' gjh.currency_code, '||
1752 ' gjl.period_name , '||
1753 ' gjst.user_je_source_name, '||
1754 ' gjct.user_je_category_name '||
1755 'ORDER BY fun_act.transacting_le_id, '||
1756 ' fun_act.transacting_le, '||
1757 ' fun_act.trading_partner_le_id , '||
1758 ' fun_act.trading_partner_le, '||
1759 ' fun_act.transacting_ledger , '||
1760 ' fun_act.transacting_ledger_id, '||
1761 ' gjh.currency_code, '||
1762 ' gjl.period_name , '||
1763 ' gjst.user_je_source_name, '||
1764 ' gjct.user_je_category_name ';
1765
1766 l_sum_unmatched_query_rev := l_sum_unmatched_query_rev ||
1767 ' GROUP BY SRC_TRANS_LE_ID, SRC_TRANS_LE, SRC_TRAD_LE_ID, SRC_TRAD_LE, SRC_TRANS_LEDGER,
1768 TRX_CURR, PERIOD_NAME ,JOURNAL_SOURCE, JOURNAL_CATEGORY
1769 ORDER BY SRC_TRANS_LE, SRC_TRAD_LE, TRX_CURR, JOURNAL_SOURCE, JOURNAL_CATEGORY';
1770
1771 l_jelines_gl_query_rev := REPLACE (l_jelines_gl_query_rev,
1772 '$get_accounts_query$',
1773 l_get_account_query_rev);
1774
1775 l_jelines_gl_query_rev := l_jelines_gl_query_rev || '
1776 ORDER BY TRANS_LE, TRAD_LE, ENTERED_CURRENCY, JE_SOURCE_NAME, JE_CATEGORY_NAME, HEADER_ID, GL_LINE_NUMBER';
1777
1778 l_gl_unmatched_query_rev := l_gl_unmatched_query_rev || '
1779 ORDER BY TRANS_LE, TRAD_LE, ENTERED_CURRENCY, JE_SOURCE_NAME, JE_CATEGORY_NAME, HEADER_ID, GL_LINE_NUMBER';
1780
1781
1782 l_jelines_sla_query_rev := REPLACE (l_jelines_sla_query_rev,
1783 '$get_accounts_query$',
1784 l_get_account_query_rev);
1785
1786 l_jelines_sla_query_rev := l_jelines_sla_query_rev || '
1787 ORDER BY TRANS_LE, TRAD_LE, ENTERED_CURRENCY, JE_SOURCE_NAME, JE_CATEGORY_NAME, HEADER_ID, SLA_LINE_NUMBER';
1788
1789 l_sla_unmatched_query_rev := l_sla_unmatched_query_rev || '
1790 ORDER BY TRANS_LE, TRAD_LE, ENTERED_CURRENCY, JE_SOURCE_NAME, JE_CATEGORY_NAME, HEADER_ID, SLA_LINE_NUMBER';
1791
1792
1793
1794 p_array_sql(2) := l_gl_balances_query1;
1795 p_array_sql(3) := l_jelines_sum_query;
1796 p_array_sql(4) := l_jelines_gl_query;
1797 p_array_sql(5) := l_jelines_sla_query;
1798
1799 p_array_sql(6) := l_gl_balances_query_rev1;
1800 p_array_sql(7) := l_jelines_sum_query_rev;
1801 p_array_sql(8) := l_jelines_gl_query_rev;
1802 p_array_sql(9) := l_jelines_sla_query_rev;
1803
1804 p_array_sql(10) := l_sum_unmatched_query;
1805 p_array_sql(11) := l_gl_unmatched_query;
1806 p_array_sql(12) := l_sla_unmatched_query;
1807
1808 p_array_sql(13) := l_sum_unmatched_query_rev;
1809 p_array_sql(14) := l_gl_unmatched_query_rev;
1810 p_array_sql(15) := l_sla_unmatched_query_rev;
1811
1812
1813
1814 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1815 trace(p_msg => 'get_fun_main_sql.End'
1816 ,p_level =>C_LEVEL_PROCEDURE
1817 ,p_module=>l_Log_module);
1818 END IF;
1819
1820 EXCEPTION
1821 WHEN OTHERS THEN
1822 FUN_UTIL.log_conc_unexp(C_DEFAULT_MODULE, 'get_fun_main_sql');
1823 RAISE;
1824 END get_fun_main_sql ;
1825
1826
1827 --=============================================================================
1828 --
1829 -- FUNCTION get_legal_entity
1830 --
1831 --=============================================================================
1832 FUNCTION get_legal_entity(p_le_id NUMBER) RETURN VARCHAR2
1833 IS
1834 v_le_name varchar2(100);
1835 BEGIN
1836 select distinct legal_entity_name
1837 into v_le_name
1838 from gl_ledger_le_v
1839 where legal_entity_id = p_le_id
1840 and rownum=1;
1841
1842 return v_le_name;
1843 EXCEPTION
1844 WHEN OTHERS THEN
1845 return null;
1846 END get_legal_entity;
1847
1848 --=============================================================================
1849 --
1850 -- BODY FOR THE PROCEDURE CLOB_TO_FILE
1851 --
1852 --=============================================================================
1853 PROCEDURE clob_to_file
1854 (p_xml_clob IN CLOB) IS
1855
1856 l_clob_size NUMBER;
1857 l_offset NUMBER;
1858 l_chunk_size INTEGER;
1859 l_chunk VARCHAR2(32767);
1860 l_log_module VARCHAR2(240);
1861
1862 BEGIN
1863 IF g_log_enabled THEN
1864 l_log_module := C_DEFAULT_MODULE||'.clob_to_file';
1865 END IF;
1866 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1867 trace
1868 (p_msg => 'BEGIN of procedure CLOB_TO_FILE'
1869 ,p_level => C_LEVEL_PROCEDURE
1870 ,p_module => l_log_module);
1871 END IF;
1872
1873 l_clob_size := dbms_lob.getlength(p_xml_clob);
1874
1875 IF (l_clob_size = 0) THEN
1876 RETURN;
1877 END IF;
1878 l_offset := 1;
1879 l_chunk_size := 3000;
1880
1881 WHILE (l_clob_size > 0) LOOP
1882 l_chunk := dbms_lob.substr (p_xml_clob, l_chunk_size, l_offset);
1883 fnd_file.put
1884 (which => fnd_file.output
1885 ,buff => l_chunk);
1886
1887 l_clob_size := l_clob_size - l_chunk_size;
1888 l_offset := l_offset + l_chunk_size;
1889 END LOOP;
1890
1891 fnd_file.new_line(fnd_file.output,1);
1892
1893 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1894 trace
1895 (p_msg => 'END of procedure CLOB_TO_FILE'
1896 ,p_level => C_LEVEL_PROCEDURE
1897 ,p_module => l_log_module);
1898 END IF;
1899
1900 EXCEPTION
1901 WHEN OTHERS THEN
1902 FUN_UTIL.log_conc_unexp(C_DEFAULT_MODULE, 'clob_to_file');
1903 RAISE;
1904 END clob_to_file;
1905
1906 --=============================================================================
1907 --
1908 -- BODY FOR THE FUNCTION get_balance
1909 --
1910 --=============================================================================
1911
1912 /* ----------------------------------------------------------------------------
1913 This function gets Receivables and Payables balances from gl_balances
1914 ------------------------------------------------------------------------------------*/
1915
1916
1917 function get_balance(p_balance_type varchar2,
1918 p_column_name varchar2,
1919 p_trans_ledger_id number,
1920 p_trans_le_id number,
1921 p_trans_gl_period varchar2,
1922 p_trad_ledger_id number,
1923 p_trad_le_id number,
1924 p_trad_gl_period varchar2,
1925 p_currency varchar2) return number is
1926
1927 l_begin_balance_dr number;
1928 l_begin_balance_cr number;
1929 l_period_net_dr number;
1930 l_period_net_cr number;
1931
1932 begin
1933
1934 if (p_balance_type = 'R') then
1935
1936 SELECT sum(glb.begin_balance_dr), sum(glb.begin_balance_cr), sum(glb.period_net_dr), sum(glb.period_net_cr)
1937 INTO l_begin_balance_dr, l_begin_balance_cr, l_period_net_dr, l_period_net_cr
1938 FROM gl_balances glb
1939 WHERE glb.period_name = p_trans_gl_period
1940 AND glb.ledger_id = p_trans_ledger_id
1941 AND glb.actual_flag = 'A'
1942 AND glb.currency_code = p_currency
1943 AND glb.code_combination_id IN (SELECT DISTINCT fia.ccid
1944 FROM fun_inter_accounts_v fia
1945 WHERE fia.ledger_id = p_trans_ledger_id
1946 AND fia.from_le_id = p_trans_le_id
1947 AND fia.to_le_id = p_trad_le_id
1948 AND fia.type = 'R' );
1949
1950 elsif (p_balance_type = 'P') then
1951
1952 SELECT sum(glb.begin_balance_dr), sum(glb.begin_balance_cr), sum(glb.period_net_dr), sum(glb.period_net_cr)
1953 INTO l_begin_balance_dr, l_begin_balance_cr, l_period_net_dr, l_period_net_cr
1954 FROM gl_balances glb
1955 WHERE glb.period_name = p_trad_gl_period
1956 AND glb.ledger_id = p_trad_ledger_id
1957 AND glb.actual_flag = 'A'
1958 AND glb.currency_code = p_currency
1959 AND glb.code_combination_id IN (SELECT DISTINCT fia.ccid
1960 FROM fun_inter_accounts_v fia
1961 WHERE fia.ledger_id = p_trad_ledger_id
1962 AND fia.from_le_id = p_trad_le_id
1963 AND fia.to_le_id = p_trans_le_id
1964 AND fia.type = 'P' );
1965
1966 end if;
1967
1968 if (p_column_name = 'BEGIN_BALANCE_DR') then
1969 return Nvl(l_begin_balance_dr,0);
1970 elsif (p_column_name = 'BEGIN_BALANCE_CR') then
1971 return Nvl(l_begin_balance_cr,0);
1972 elsif (p_column_name = 'PERIOD_NET_DR') then
1973 return Nvl(l_period_net_dr,0);
1974 elsif (p_column_name = 'PERIOD_NET_CR') then
1975 return Nvl(l_period_net_cr,0);
1976 end if;
1977
1978 exception
1979 when others then
1980 return 0;
1981
1982 end get_balance;
1983
1984 --=============================================================================
1985 --
1986 -- BODY FOR THE FUNCTION match_ap_ar_invoice
1987 --
1988 --=============================================================================
1989
1990 /* ----------------------------------------------------------------------------
1991 Match based on assumption that AR invoice number (customer_trx_id) is stamped
1992 on both AR and AP tables. If the totals on AR and AP side for the same invoice
1993 number matches then it returns 'MATCHED' else it returns 'UNMATCHED'
1994 ------------------------------------------------------------------------------------*/
1995
1996 Function match_ap_ar_invoice(p_trans_le_id in number,
1997 p_trans_ledger_id in number,
1998 p_trans_gl_period in varchar2,
1999 p_trad_le_id in number,
2000 p_trad_ledger_id in number,
2001 p_trad_gl_period in varchar2,
2002 p_account_type in varchar2, -- this will be 'R', or 'P'
2003 p_entity_code in varchar2,
2004 p_ap_ar_invoice_id in number) return varchar2 is
2005
2006
2007 l_trans_le_id number;
2008 l_trans_ledger_id number;
2009 l_trans_gl_period varchar2(15);
2010 l_trad_le_id number;
2011 l_trad_ledger_id number;
2012 l_trad_gl_period varchar2(15);
2013
2014 l_ar_invoice_id number;
2015 l_ap_invoice_id number;
2016 l_invoice_num varchar2(50);
2017 l_payables_net_cr number;
2018 l_receivables_net_dr number;
2019 l_trans_gl_prd_end_date date;
2020 l_trans_adj_period_flag varchar2(1);
2021
2022 BEGIN
2023
2024 if ((p_ap_ar_invoice_id is null) OR
2025 ((p_entity_code <> 'AP_INVOICES') AND (p_entity_code <> 'BILLS_RECEIVABLE'))) then
2026 return 'UNMATCHED';
2027 end if;
2028
2029
2030 if (p_entity_code = 'AP_INVOICES') then
2031 l_ap_invoice_id := p_ap_ar_invoice_id;
2032
2033 select invoice_num
2034 into l_invoice_num
2035 from ap_invoices_all
2036 where invoice_id = l_ap_invoice_id;
2037
2038 -- this invoice_num is = ra_customer_trx_all.customer_trx_id (we are assuming)
2039 l_ar_invoice_id := to_number(l_invoice_num);
2040
2041 elsif (p_entity_code = 'BILLS_RECEIVABLE') then
2042 l_ar_invoice_id := p_ap_ar_invoice_id;
2043
2044 select invoice_id
2045 into l_ap_invoice_id
2046 from ap_invoices_all
2047 where invoice_num = l_ar_invoice_id;
2048
2049 else
2050 return 'UNMATCHED';
2051 end if;
2052
2053 l_trans_le_id := p_trans_le_id;
2054 l_trans_ledger_id := p_trans_ledger_id;
2055 l_trans_gl_period := p_trans_gl_period;
2056
2057 l_trad_le_id := p_trad_le_id;
2058
2059 if (p_trad_ledger_id is not null) then
2060 l_trad_ledger_id := p_trad_ledger_id;
2061 else
2062 null;
2063 ---- put some logic here if needed -------
2064 end if;
2065
2066 if (p_trad_gl_period is not null) then
2067 l_trad_gl_period := p_trad_gl_period;
2068 elsif (l_trans_ledger_id = l_trad_ledger_id) then
2069 l_trad_gl_period := l_trans_gl_period;
2070 else
2071 -- get the end date of l_trans_gl_period for l_trans_ledger_id
2072 SELECT glp.end_date, glp.adjustment_period_flag
2073 INTO l_trans_gl_prd_end_date, l_trans_adj_period_flag
2074 FROM gl_periods glp,
2075 gl_ledgers gl
2076 WHERE glp.period_set_name = gl.period_set_name
2077 AND glp.period_type = gl.accounted_period_type
2078 AND glp.period_name = l_trans_gl_period
2079 AND gl.ledger_id = l_trans_ledger_id;
2080
2081 SELECT glp.period_name
2082 INTO l_trad_gl_period
2083 FROM gl_periods glp,
2084 gl_ledgers gl
2085 WHERE glp.period_set_name = gl.period_set_name
2086 AND glp.period_type = gl.accounted_period_type
2087 AND gl.ledger_id = l_trad_ledger_id
2088 AND l_trans_gl_prd_end_date between glp.start_date and glp.end_date
2089 AND glp.adjustment_period_flag = l_trans_adj_period_flag;
2090
2091 end if;
2092
2093
2094 if (p_account_type = 'P') then
2095
2096 SELECT
2097 sum(nvl(ael.entered_cr, 0) - nvl(ael.entered_dr, 0))
2098 INTO l_payables_net_cr
2099 FROM xla_ae_headers aeh
2100 ,xla_ae_lines ael
2101 ,xla_events xle
2102 ,xla_event_types_b xet
2103 ,xla_transaction_entities ent
2104 ,gl_import_references gir
2105 ,gl_je_lines gjl
2106 ,gl_je_headers gjh
2107 ,gl_je_batches gjb
2108 ,gl_ledger_le_v glv
2109 ,fun_inter_accounts_v fia
2110 ,gl_periods glp
2111
2112 WHERE aeh.accounting_entry_status_code = 'F'
2113 AND aeh.gl_transfer_status_code = 'Y'
2114 AND ael.ae_header_id = aeh.ae_header_id
2115 AND ael.application_id = aeh.application_id
2116 AND xle.event_id = aeh.event_id
2117 AND xet.application_id = aeh.application_id
2118 AND xet.event_type_code = aeh.event_type_code
2119 AND ent.entity_id = xle.entity_id
2120 AND ent.application_id = xle.application_id
2121 AND gir.gl_sl_link_id = ael.gl_sl_link_id
2122 AND gir.gl_sl_link_table = ael.gl_sl_link_table
2123 AND gjl.je_header_id = gir.je_header_id
2124 AND gjl.je_line_num = gir.je_line_num
2125 AND gjh.je_header_id = gir.je_header_id
2126 AND gjb.je_batch_id = gir.je_batch_id
2127 AND gjb.status = 'P'
2128 AND aeh.ledger_id = fia.ledger_id
2129 AND aeh.balance_type_code = 'A'
2130 AND ael.code_combination_id = fia.ccid
2131 AND fia.ledger_id = glv.ledger_id
2132 AND fia.from_le_id = glv.legal_entity_id
2133 AND glv.period_set_name = glp.period_set_name
2134 AND glv.accounted_period_type = glp.period_type
2135 AND glp.period_name = aeh.period_name
2136 AND ent.application_id = 200
2137 AND ent.entity_code = 'AP_INVOICES'
2138 AND ent.source_id_int_1 = l_ap_invoice_id
2139 AND fia.type = 'P'
2140 AND fia.from_le_id = l_trans_le_id
2141 AND fia.ledger_id = l_trans_ledger_id
2142 AND glp.period_name = l_trans_gl_period
2143 AND fia.to_le_id = l_trad_le_id;
2144
2145 SELECT
2146 sum(nvl(ael.entered_dr, 0) - nvl(ael.entered_cr, 0))
2147 INTO l_receivables_net_dr
2148 FROM xla_ae_headers aeh
2149 ,xla_ae_lines ael
2150 ,xla_events xle
2151 ,xla_event_types_b xet
2152 ,xla_transaction_entities ent
2153 ,gl_import_references gir
2154 ,gl_je_lines gjl
2155 ,gl_je_headers gjh
2156 ,gl_je_batches gjb
2157 ,gl_ledger_le_v glv
2158 ,fun_inter_accounts_v fia
2159 ,gl_periods glp
2160
2161 WHERE aeh.accounting_entry_status_code = 'F'
2162 AND aeh.gl_transfer_status_code = 'Y'
2163 AND ael.ae_header_id = aeh.ae_header_id
2164 AND ael.application_id = aeh.application_id
2165 AND xle.event_id = aeh.event_id
2166 AND xet.application_id = aeh.application_id
2167 AND xet.event_type_code = aeh.event_type_code
2168 AND ent.entity_id = xle.entity_id
2169 AND ent.application_id = xle.application_id
2170 AND gir.gl_sl_link_id = ael.gl_sl_link_id
2171 AND gir.gl_sl_link_table = ael.gl_sl_link_table
2172 AND gjl.je_header_id = gir.je_header_id
2173 AND gjl.je_line_num = gir.je_line_num
2174 AND gjh.je_header_id = gir.je_header_id
2175 AND gjb.je_batch_id = gir.je_batch_id
2176 AND gjb.status = 'P'
2177 AND aeh.ledger_id = fia.ledger_id
2178 AND aeh.balance_type_code = 'A'
2179 AND ael.code_combination_id = fia.ccid
2180 AND fia.ledger_id = glv.ledger_id
2181 AND fia.from_le_id = glv.legal_entity_id
2182 AND glv.period_set_name = glp.period_set_name
2183 AND glv.accounted_period_type = glp.period_type
2184 AND glp.period_name = aeh.period_name
2185
2186 AND ent.application_id = 222
2187 AND ent.entity_code = 'BILLS_RECEIVABLE'
2188 AND ent.source_id_int_1 = l_ar_invoice_id
2189 AND fia.type = 'R'
2190 AND fia.from_le_id = l_trad_le_id
2191 AND fia.ledger_id = nvl(l_trad_ledger_id, fia.ledger_id)
2192 AND glp.period_name = l_trad_gl_period
2193 AND fia.to_le_id = l_trans_le_id;
2194
2195 elsif (p_account_type = 'R') then
2196
2197 SELECT
2198 sum(nvl(ael.entered_cr, 0) - nvl(ael.entered_dr, 0))
2199 INTO l_payables_net_cr
2200 FROM xla_ae_headers aeh
2201 ,xla_ae_lines ael
2202 ,xla_events xle
2203 ,xla_event_types_b xet
2204 ,xla_transaction_entities ent
2205 ,gl_import_references gir
2206 ,gl_je_lines gjl
2207 ,gl_je_headers gjh
2208 ,gl_je_batches gjb
2209 ,gl_ledger_le_v glv
2210 ,fun_inter_accounts_v fia
2211 ,gl_periods glp
2212
2213 WHERE aeh.accounting_entry_status_code = 'F'
2214 AND aeh.gl_transfer_status_code = 'Y'
2215 AND ael.ae_header_id = aeh.ae_header_id
2216 AND ael.application_id = aeh.application_id
2217 AND xle.event_id = aeh.event_id
2218 AND xet.application_id = aeh.application_id
2219 AND xet.event_type_code = aeh.event_type_code
2220 AND ent.entity_id = xle.entity_id
2221 AND ent.application_id = xle.application_id
2222 AND gir.gl_sl_link_id = ael.gl_sl_link_id
2223 AND gir.gl_sl_link_table = ael.gl_sl_link_table
2224 AND gjl.je_header_id = gir.je_header_id
2225 AND gjl.je_line_num = gir.je_line_num
2226 AND gjh.je_header_id = gir.je_header_id
2227 AND gjb.je_batch_id = gir.je_batch_id
2228 AND gjb.status = 'P'
2229 AND aeh.ledger_id = fia.ledger_id
2230 AND aeh.balance_type_code = 'A'
2231 AND ael.code_combination_id = fia.ccid
2232 AND fia.ledger_id = glv.ledger_id
2233 AND fia.from_le_id = glv.legal_entity_id
2234 AND glv.period_set_name = glp.period_set_name
2235 AND glv.accounted_period_type = glp.period_type
2236 AND glp.period_name = aeh.period_name
2237 AND ent.application_id = 200
2238 AND ent.entity_code = 'AP_INVOICES'
2239 AND ent.source_id_int_1 = l_ap_invoice_id
2240 AND fia.type = 'P'
2241 AND fia.from_le_id = l_trad_le_id
2242 AND fia.ledger_id = nvl(l_trad_ledger_id, fia.ledger_id)
2243 AND glp.period_name = l_trad_gl_period;
2244
2245 SELECT
2246 sum(nvl(ael.entered_dr, 0) - nvl(ael.entered_cr, 0))
2247 INTO l_receivables_net_dr
2248 FROM xla_ae_headers aeh
2249 ,xla_ae_lines ael
2250 ,xla_events xle
2251 ,xla_event_types_b xet
2252 ,xla_transaction_entities ent
2253 ,gl_import_references gir
2254 ,gl_je_lines gjl
2255 ,gl_je_headers gjh
2256 ,gl_je_batches gjb
2257 ,gl_ledger_le_v glv
2258 ,fun_inter_accounts_v fia
2259 ,gl_periods glp
2260
2261 WHERE aeh.accounting_entry_status_code = 'F'
2262 AND aeh.gl_transfer_status_code = 'Y'
2263 AND ael.ae_header_id = aeh.ae_header_id
2264 AND ael.application_id = aeh.application_id
2265 AND xle.event_id = aeh.event_id
2266 AND xet.application_id = aeh.application_id
2267 AND xet.event_type_code = aeh.event_type_code
2268 AND ent.entity_id = xle.entity_id
2269 AND ent.application_id = xle.application_id
2270 AND gir.gl_sl_link_id = ael.gl_sl_link_id
2271 AND gir.gl_sl_link_table = ael.gl_sl_link_table
2272 AND gjl.je_header_id = gir.je_header_id
2273 AND gjl.je_line_num = gir.je_line_num
2274 AND gjh.je_header_id = gir.je_header_id
2275 AND gjb.je_batch_id = gir.je_batch_id
2276 AND gjb.status = 'P'
2277 AND aeh.ledger_id = fia.ledger_id
2278 AND aeh.balance_type_code = 'A'
2279 AND ael.code_combination_id = fia.ccid
2280 AND fia.ledger_id = glv.ledger_id
2281 AND fia.from_le_id = glv.legal_entity_id
2282 AND glv.period_set_name = glp.period_set_name
2283 AND glv.accounted_period_type = glp.period_type
2284 AND glp.period_name = aeh.period_name
2285
2286 AND ent.application_id = 222
2287 AND ent.entity_code = 'BILLS_RECEIVABLE'
2288 AND ent.source_id_int_1 = l_ar_invoice_id
2289 AND fia.type = 'R'
2290 AND fia.from_le_id = l_trans_le_id
2291 AND fia.ledger_id = l_trans_ledger_id
2292 AND glp.period_name = l_trans_gl_period;
2293
2294 end if;
2295
2296 IF (l_payables_net_cr = l_receivables_net_dr) then
2297 return 'MATCHED';
2298 ELSE
2299 return 'UNMATCHED';
2300
2301 END IF;
2302
2303
2304 EXCEPTION
2305 WHEN OTHERS THEN
2306 return 'UNMATCHED';
2307 END match_ap_ar_invoice;
2308
2309
2310 --=============================================================================
2311 -- *********** Initialization routine **********
2312 --=============================================================================
2313
2314 --=============================================================================
2315 -- Following code is executed when the package body is referenced for the first
2316 -- time
2317 --=============================================================================
2318
2319 BEGIN
2320 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2321 g_log_enabled := fnd_log.test
2322 (log_level => g_log_level
2323 ,MODULE => C_DEFAULT_MODULE);
2324
2325 IF NOT g_log_enabled THEN
2326 g_log_level := C_LEVEL_LOG_DISABLED;
2327 END IF;
2328
2329 END FUN_RECON_RPT_PKG;