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