[Home] [Help]
PACKAGE BODY: APPS.AP_OPEN_ITEMS_REVAL_PKG
Source
4 C_NEW_LINE CONSTANT VARCHAR2(8) := fnd_global.newline;
1 PACKAGE BODY AP_OPEN_ITEMS_REVAL_PKG AS
2 /* $Header: apopitrb.pls 120.10.12020000.3 2012/11/08 06:30:25 rseeta ship $ */
3
5
6 /* case when transfer to gl only = Yes
7 GL transfered invoices */
8 /*Bug 13438992 Modified hint*/
9 C_GL_TRANSFER_INVOICES_GT_SQL CONSTANT VARCHAR2(32000) := '
10 SELECT /*+ leading(xte) parallel(xte) no_parallel(asp) no_parallel(xah)
11 no_parallel(xal) no_parallel(gcck) no_parallel(ai)
12 no_parallel(alc) no_parallel(hp) no_parallel(supp)
13 no_parallel(site) use_nl(xte xah xal gcck) use_nl(xte ai)
14 use_nl(ai hp) use_nl(ai alc) use_nl(ai supp) use_nl(ai site)
15 index(xah) index(xal) index(gcck) index(ai) index(hp)
16 index(alc) index(supp) index(site) index(asp)*/
17 DISTINCT $segment_columns$
18 gcck.code_combination_id,
19 gcck.concatenated_segments account,
20 ai.party_id,
21 ai.party_site_id,
22 hp.party_name,
23 ai.vendor_id,
24 supp.segment1 vendor_number,
25 ai.vendor_site_id,
26 site.vendor_site_code,
27 ai.invoice_id txn_id,
28 ai.invoice_num txn_number,
29 alc.displayed_field txn_type_lookup_code,
30 ai.invoice_date txn_date,
31 ai.invoice_amount txn_amount,
32 nvl(ai.base_amount, ai.invoice_amount) txn_base_amount,
33 xal.currency_code txn_currency_code,
34 1 TXN_CURR_MIN_ACCT_UNIT,
35 2 TXN_CURR_PRECISION,
36 nvl(ai.exchange_rate, 1) txn_base_exchange_rate,
37 ai.payment_currency_code payment_currency_code,
38 ai.payment_cross_rate_type, /*Bug 14136626*/
39 nvl(ai.payment_cross_rate, 1) payment_cross_rate,
40 2 PAYMENT_CURR_PRECISION,
41 1 PMT_CURR_MIN_ACCT_UNIT,
42 ai.payment_status_flag,
43 sum(nvl(xal.entered_cr, 0) - nvl(xal.entered_dr, 0)) entered_amount,
44 sum(nvl(xal.accounted_cr, 0) - nvl(xal.accounted_dr, 0)) accounted_amount,
45 NULL DUE_DATE
46 from ap_system_parameters_all asp,
47 xla_transaction_entities xte,
48 xla_ae_headers xah,
49 xla_ae_lines xal,
50 gl_code_combinations_kfv gcck,
51 ap_invoices_all ai,
52 ap_lookup_codes alc,
53 hz_parties hp,
54 ap_suppliers supp,
55 ap_supplier_sites_all site
56 where xte.ledger_id = $ledger_id$
57 and xah.ledger_id = $ledger_id$
58 and xal.ledger_id = $ledger_id$
59 and asp.set_of_books_id = $ledger_id$
60 and ai.set_of_books_id = $ledger_id$
61 and asp.org_id = $org_id$
62 and ai.org_id = $org_id$
63 and nvl(xte.security_id_int_1, -99) = $org_id$
64 and xte.application_id = 200
65 and xah.application_id = 200
66 and xal.application_id = 200
67 and alc.lookup_type = ''INVOICE TYPE''
68 and ai.invoice_type_lookup_code = alc.lookup_code
69 and ai.invoice_id = nvl(xte.source_id_int_1, -99)
70 and xte.entity_code = ''AP_INVOICES''
71 and xah.entity_id = xte.entity_id
72 and xah.ae_header_id = xal.ae_header_id
73 and xah.gl_transfer_status_code = ''Y''
74 and xal.accounting_class_code = ''LIABILITY''
75 and gcck.code_combination_id = xal.code_combination_id
76 and hp.party_id = ai.party_id
77 and ai.vendor_id = supp.vendor_id(+)
81 $bal_segment_condition$
78 and ai.vendor_site_id = site.vendor_site_id(+)
79 and xah.accounting_date <= $accounting_date$
80 and :G_DAILY_RATE_ERROR = ''N''
82 group by $segment_group$
83 gcck.code_combination_id,
84 gcck.concatenated_segments ,
85 ai.party_id,
86 ai.party_site_id,
87 hp.party_name,
88 ai.vendor_id,
89 supp.segment1 ,
90 ai.vendor_site_id,
91 site.vendor_site_code,
92 ai.invoice_id ,
93 ai.invoice_num ,
94 alc.displayed_field ,
95 ai.invoice_date ,
96 ai.invoice_amount ,
97 nvl(ai.base_amount, ai.invoice_amount) ,
98 xal.currency_code ,
99 --1 TXN_CURR_MIN_ACCT_UNIT,
100 --2 TXN_CURR_PRECISION,
101 nvl(ai.exchange_rate, 1) ,
102 ai.payment_currency_code ,
103 ai.payment_cross_rate_type,
104 nvl(ai.payment_cross_rate, 1) ,
105 --2 PAYMENT_CURR_PRECISION,
106 --1 PMT_CURR_MIN_ACCT_UNIT,
107 ai.payment_status_flag';
108
109 /* case when transfer to gl only = Yes,
110 These are payments for which none of the invoices are falling inside the end-date.
111 GL transfered payments with out invoices */
112 C_GL_TRANSFER_PAYMENTS_GT_SQL CONSTANT VARCHAR2(32000) := '
113 select distinct balancing_segment,
114 account_segment,
115 code_combination_id,
116 account,
117 party_id,
118 party_site_id,
119 party_name,
120 vendor_id,
121 vendor_number,
122 vendor_site_id,
123 vendor_site_code,
124 check_id txn_id,
125 check_number txn_number,
126 alc.displayed_field txn_type_lookup_code,
127 check_date txn_date,
128 check_amount txn_amount,
129 nvl(check_base_amount, check_amount) txn_base_amount,
130 currency_code txn_currency_code,
131 1 TXN_CURR_MIN_ACCT_UNIT,
132 2 TXN_CURR_PRECISION,
133 currency_conversion_rate txn_base_exchange_rate,
134 currency_code payment_currency_code,
135 payment_cross_rate_type, /*Bug 14136626*/
136 1 payment_cross_rate,
137 2 PAYMENT_CURR_PRECISION,
138 1 PMT_CURR_MIN_ACCT_UNIT,
139 ''Y'' payment_status_flag,
140 sum(entered_amount) entered_amount,
141 sum(accounted_amount) accounted_amount,
142 null due_date
143 from (
144 /* Bug 9975987 removed the use of ap_ae_lines_all, placed xla_ae_lines instead
145 Bug 13438992 Modified hint*/
146 select /*+ leading(xte) parallel(xte)
147 no_parallel(asp) no_parallel(ai) no_parallel(hp) no_parallel(supp)
148 no_parallel(site) no_parallel(ac) no_parallel(xah) no_parallel(xal)
149 no_parallel(gcck) */ distinct
150 $segment_columns$
151 gcck.code_combination_id,
152 gcck.concatenated_segments account,
153 null ref_ae_header_id,
154 null temp_line_num,
155 xah.ae_header_id,
156 xal.ae_line_num,
157 ac.check_id,
158 ac.check_number,
159 ac.check_date,
160 ai.invoice_id,
161 ai.vendor_id,
162 hp.party_name,
163 supp.segment1 vendor_number,
164 ai.vendor_site_id,
165 site.vendor_site_code,
166 ai.party_id,
167 ai.party_site_id,
168 xal.currency_code,
169 xal.currency_conversion_rate,
170 ac.amount check_amount,
171 ac.base_amount check_base_amount,
172 nvl(xal.entered_cr, 0) - nvl(xal.entered_dr, 0) entered_amount,
173 nvl(xal.accounted_cr, 0) - nvl(xal.accounted_dr, 0) accounted_amount,
174 ai.payment_cross_rate_type /*Bug 14136626*/
175 from ap_system_parameters_all asp,
176 ap_invoices_all ai,
177 hz_parties hp,
178 ap_suppliers supp,
179 ap_supplier_sites_all site,
180 ap_checks_all ac,
181 xla_transaction_entities xte,
182 xla_ae_headers xah,
183 xla_ae_lines xal,
184 gl_code_combinations_kfv gcck
185 where asp.set_of_books_id = $ledger_id$
186 and ai.set_of_books_id = $ledger_id$
187 and xte.ledger_id = $ledger_id$
188 and xah.ledger_id = $ledger_id$
189 and xal.ledger_id = $ledger_id$
190 and asp.org_id = $org_id$
191 and ai.org_id = $org_id$
192 and ac.org_id = $org_id$
193 and nvl(xte.security_id_int_1, -99) = $org_id$
194 and xte.application_id = 200
198 and xah.upg_batch_id is not null
195 and xte.entity_code = ''AP_PAYMENTS''
196 and nvl(xte.source_id_int_1, -99) = ac.check_id
197 and xah.application_id = xte.application_id
199 and xah.entity_id = xte.entity_id
200 and xah.event_type_code <> ''MANUAL''
201
202 and xah.gl_transfer_status_code = ''Y''
203 and xah.accounting_date <= $accounting_date$
204 /* upgrade case */
205 and xal.application_id = xte.application_id
206 and xah.ae_header_id = xal.ae_header_id
207 and xal.accounting_class_code = ''LIABILITY''
208 and ((xal.source_table = ''AP_INVOICE_PAYMENTS''
209 and exists (select 1
210 from ap_invoice_payments_all aip
211 where aip.invoice_id = ai.invoice_id
212 and aip.invoice_payment_id = xal.source_id)
213 )
214 or
215 (xal.source_table = ''AP_INVOICES''
216 and xal.source_id = ai.invoice_id
217 )
218 or
219 (xal.source_table = ''AP_INVOICE_DISTRIBUTIONS''
220 and exists (select 1
221 from ap_invoice_distributions_all aid
222 where aid.invoice_id = ai.invoice_id
223 and aid.invoice_distribution_id = xal.source_id)
224 )
225 )
226 and gcck.code_combination_id = xal.code_combination_id
227 and hp.party_id = ai.party_id
228 and ai.vendor_id = supp.vendor_id(+)
229 and ai.vendor_site_id = site.vendor_site_id(+)
230 and not exists (select 1
231 from ap_open_items_reval_gt gt
232 where gt.txn_id = ai.invoice_id)
233 and ac.check_id IN (select aip.check_id
234 from ap_invoice_payments_all aip
235 where aip.invoice_id = ai.invoice_id)
236 and :G_DAILY_RATE_ERROR = ''N''
237 $bal_segment_condition$
238 union
239 /*Bug 13438992 Modified hint*/
240 select /*+ leading(xte) parallel(xte)
241 no_parallel(asp) no_parallel(ai) no_parallel(hp) no_parallel(supp)
242 no_parallel(site) no_parallel(ac) no_parallel(xah) no_parallel(xal)
243 no_parallel(xdl) no_parallel(gcck) */ distinct
244 $segment_columns$
245 gcck.code_combination_id,
246 gcck.concatenated_segments account,
247 xdl.ref_ae_header_id,
248 xdl.temp_line_num,
249 xdl.ae_header_id,
250 null ae_line_num,
251 ac.check_id,
252 ac.check_number,
253 ac.check_date,
254 ai.invoice_id,
255 ai.vendor_id,
256 hp.party_name,
257 supp.segment1 vendor_number,
258 ai.vendor_site_id,
259 site.vendor_site_code,
260 ai.party_id,
261 ai.party_site_id,
262 xal.currency_code,
263 xal.currency_conversion_rate,
264 ac.amount check_amount,
265 ac.base_amount check_base_amount,
266 nvl(xdl.unrounded_entered_cr, 0) - nvl(xdl.unrounded_entered_dr, 0) entered_amount,
267 nvl(xdl.unrounded_accounted_cr, 0) - nvl(xdl.unrounded_accounted_dr, 0) accounted_amount,
268 ai.payment_cross_rate_type /*Bug 14136626*/
269 from ap_system_parameters_all asp,
270 ap_invoices_all ai,
271 hz_parties hp,
272 ap_suppliers supp,
273 ap_supplier_sites_all site,
274 ap_checks_all ac,
275 xla_transaction_entities xte,
276 xla_ae_headers xah,
277 xla_ae_lines xal,
278 xla_distribution_links xdl,
279 gl_code_combinations_kfv gcck
280 where asp.set_of_books_id = $ledger_id$
281 and ai.set_of_books_id = $ledger_id$
282 and xte.ledger_id = $ledger_id$
283 and xah.ledger_id = $ledger_id$
284 and xal.ledger_id = $ledger_id$
285 and asp.org_id = $org_id$
286 and ai.org_id = $org_id$
287 and ac.org_id = $org_id$
288 and nvl(xte.security_id_int_1, -99) = $org_id$
289 and xte.application_id = 200
290 and xte.entity_code = ''AP_PAYMENTS''
291 and nvl(xte.source_id_int_1, -99) = ac.check_id
292 and xah.entity_id = xte.entity_id
293 and xah.application_id = xte.application_id
294 and xal.application_id = xte.application_id
295 and xah.gl_transfer_status_code = ''Y''
299 and xah.upg_batch_id is null
296 and xah.event_type_code <> ''MANUAL''
297 and xah.accounting_date <= $accounting_date$
298 /* non-upgrade case */
300 and xah.ae_header_id = xal.ae_header_id
301 and xdl.ae_header_id = xah.ae_header_id
302 and xdl.ae_line_num = xal.ae_line_num
303 and xal.code_combination_id = gcck.code_combination_id
304 and xdl.applied_to_source_id_num_1 = ai.invoice_id
305 and xal.accounting_class_code = ''LIABILITY''
306 and xdl.applied_to_entity_code = ''AP_INVOICES''
307 and xdl.application_id = xte.application_id
308
309 and hp.party_id = ai.party_id
310 and ai.vendor_id = supp.vendor_id(+)
311 and ai.vendor_site_id = site.vendor_site_id(+)
312 and not exists (select 1
313 from ap_open_items_reval_gt
314 where txn_id = ai.invoice_id)
315 and :G_DAILY_RATE_ERROR = ''N''
316 $bal_segment_condition$
317 )a,ap_lookup_codes alc
318 where alc.lookup_code = ''PAYMENT''
319 and alc.lookup_type = ''SYSTEM OPTIONS'' -- need modification
320 group by balancing_segment,
321 account_segment,
322 code_combination_id,
323 account,
324 party_id,
325 party_site_id,
326 party_name,
327 vendor_id,
328 vendor_number,
329 vendor_site_id,
330 vendor_site_code,
331 check_id ,
332 check_number ,
333 alc.displayed_field ,
334 check_date ,
335 check_amount ,
336 nvl(check_base_amount, check_amount) ,
337 currency_code ,
338 --1 TXN_CURR_MIN_ACCT_UNIT,
339 --2 TXN_CURR_PRECISION,
340 currency_conversion_rate,
341 payment_cross_rate_type
342 having sum(entered_amount) <> 0
343 or sum(accounted_amount) <> 0 --bug9975987
344 ';
345
346 /* case when gl transfer only = No.
347 invoices*/
348 C_INVOICES_GT_SQL CONSTANT VARCHAR2(32000) := '
349 select /*parallel (ai)*/ distinct
350 $segment_columns$
351 gcck.code_combination_id,
352 gcck.concatenated_segments account,
353 ai.party_id,
354 ai.party_site_id,
355 hp.party_name,
356 ai.vendor_id,
357 supp.segment1 vendor_number,
358 ai.vendor_site_id,
359 site.vendor_site_code,
360 ai.invoice_id txn_id,
361 ai.invoice_num txn_number,
362 alc.displayed_field txn_type_lookup_code,
363 ai.invoice_date txn_date,
364 ai.invoice_amount txn_amount,
365 nvl(ai.base_amount, ai.invoice_amount) txn_base_amount,
366 ai.invoice_currency_code txn_currency_code,
367 1 TXN_CURR_MIN_ACCT_UNIT ,
368 2 TXN_CURR_PRECISION ,
369 nvl(ai.exchange_rate,1) TXN_BASE_EXCHANGE_RATE,
370 ai.payment_currency_code payment_currency_code,
371 ai.payment_cross_rate_type, /*Bug 14136626*/
372 nvl(ai.payment_cross_rate, 1) payment_cross_rate,
373 2 PAYMENT_CURR_PRECISION,
374 1 PMT_CURR_MIN_ACCT_UNIT,
375 ai.payment_status_flag,
376 sum(nvl(aid.amount, 0)) entered_amount,
377 sum(nvl(aid.base_amount, nvl(aid.amount, 0))) accounted_amount,
378 NULL due_Date
379 from ap_system_parameters_all asp,
380 ap_invoice_distributions_all aid,
381 ap_invoice_lines_all ail,
382 gl_code_combinations_kfv gcck,
383 ap_invoices_all ai,
384 ap_lookup_codes alc,
385 hz_parties hp,
386 ap_suppliers supp,
387 ap_supplier_sites_all site
388 where asp.set_of_books_id = $ledger_id$
389 and ai.set_of_books_id = $ledger_id$
390 and aid.set_of_books_id = $ledger_id$
394 and ail.org_id = $org_id$
391 and ail.set_of_books_id = $ledger_id$
392 and asp.org_id = $org_id$
393 and ai.org_id = $org_id$
395 and aid.org_id = $org_id$
396 and gcck.code_combination_id = ai.accts_pay_code_combination_id
397 and ai.invoice_type_lookup_code = alc.lookup_code
398 and alc.lookup_type = ''INVOICE TYPE''
399 and hp.party_id = ai.party_id
400 and ai.vendor_id = supp.vendor_id(+)
401 and ai.vendor_site_id = site.vendor_site_id(+)
402 and aid.invoice_id = ai.invoice_id
403 and ail.invoice_id = ai.invoice_id
404 and ail.line_number = aid.invoice_line_number
405 and aid.match_status_flag in (''A'', ''T'')
406 and aid.accounting_date <= $accounting_date$
407 and :G_DAILY_RATE_ERROR = ''N''
408 $bal_segment_condition$
409 group by $segment_group$
410 gcck.code_combination_id,
411 gcck.concatenated_segments,
412 ai.party_id,
413 ai.party_site_id,
414 hp.party_name,
415 ai.vendor_id,
416 supp.segment1 ,
417 ai.vendor_site_id,
418 site.vendor_site_code,
419 ai.invoice_id ,
420 ai.invoice_num ,
421 alc.displayed_field ,
422 ai.invoice_date ,
423 ai.invoice_amount ,
424 nvl(ai.base_amount, ai.invoice_amount) ,
425 ai.invoice_currency_code ,
426 --1 TXN_CURR_MIN_ACCT_UNIT ,
427 --2 TXN_CURR_PRECISION ,
428 nvl(ai.exchange_rate,1) ,
429 ai.payment_currency_code ,
430 ai.payment_cross_rate_type,
431 nvl(ai.payment_cross_rate, 1) ,
432 --2 PAYMENT_CURR_PRECISION,
433 --1 PMT_CURR_MIN_ACCT_UNIT,
434 ai.payment_status_flag';
435
436 /* case when gl transfer only = No
437 these are the payments for which invoices fall outside of end date*/
438 C_PAYMENTS_GT_SQL CONSTANT VARCHAR2(32000) := '
439 select /*+ leading (aip) parallel(aip)*/ distinct
440 $segment_columns$
441 gcck.code_combination_id,
442 gcck.concatenated_segments account,
443 ai.party_id,
444 ai.party_site_id,
445 hp.party_name,
446 ai.vendor_id,
447 supp.segment1 vendor_number,
448 ai.vendor_site_id,
449 site.vendor_site_code,
450 ac.check_id txn_id,
451 ac.check_number txn_number,
452 alc.displayed_field txn_type_lookup_code,
453 ac.check_date txn_date,
454 ac.amount txn_amount,
455 nvl(ac.base_amount, ac.amount) txn_base_amount,
456 ac.currency_code txn_currency_code,
457 1 TXN_CURR_MIN_ACCT_UNIT ,
458 2 TXN_CURR_PRECISION,
459 nvl(ac.exchange_rate, 1) TXN_BASE_EXCHANGE_RATE,
460 ac.currency_code payment_currency_code,
461 ai.payment_cross_rate_type, /*Bug 14136626*/
462 1 payment_cross_rate,
463 2 PAYMENT_CURR_PRECISION,
464 1 PMT_CURR_MIN_ACCT_UNIT,
465 ''Y'' payment_status_flag,
466 -sum(aip.amount) entered_amount,
467 -sum(nvl(aip.payment_base_amount, aip.amount)) accounted_amount,
468 null due_Date
469 from ap_system_parameters_all asp,
470 ap_invoices_all ai,
471 ap_invoice_payments_all aip,
472 ap_checks_all ac,
473 ap_lookup_codes alc,
474 hz_parties hp,
475 ap_suppliers supp,
476 ap_supplier_sites_all site,
477 gl_code_combinations_kfv gcck
478 where asp.set_of_books_id = $ledger_id$
479 and ai.set_of_books_id = $ledger_id$
480 and aip.set_of_books_id = $ledger_id$
481 and asp.org_id = $org_id$
482 and ai.org_id = $org_id$
483 and ac.org_id = $org_id$
484 and aip.org_id = $org_id$
485 and alc.lookup_code = ''PAYMENT''
486 and alc.lookup_type = ''SYSTEM OPTIONS'' -- need modification
487 and hp.party_id = ai.party_id
488 and ai.vendor_id = supp.vendor_id(+)
489 and ai.vendor_site_id = site.vendor_site_id(+)
490 and gcck.code_combination_id = ai.accts_pay_code_combination_id
491 and aip.invoice_id = ai.invoice_id
492 and ac.check_id = aip.check_id
493 and aip.accounting_date <= $accounting_date$
494 and aip.invoice_id not in (select distinct a.txn_id
495 from ap_open_items_reval_gt a)
496 and :G_DAILY_RATE_ERROR = ''N''
497 and :G_DAILY_RATE_ERROR = ''N''
498 $bal_segment_condition$
499 group by $segment_group$
500 gcck.code_combination_id,
501 gcck.concatenated_segments ,
502 ai.party_id,
503 ai.party_site_id,
504 hp.party_name,
505 ai.vendor_id,
506 supp.segment1 ,
507 ai.vendor_site_id,
508 site.vendor_site_code,
509 ac.check_id ,
510 ac.check_number ,
511 alc.displayed_field ,
512 ac.check_date ,
513 ac.amount ,
514 nvl(ac.base_amount, ac.amount) ,
515 ac.currency_code ,
519 ac.currency_code,
516 --1 TXN_CURR_MIN_ACCT_UNIT ,
517 --2 TXN_CURR_PRECISION,
518 nvl(ac.exchange_rate, 1),
520 ai.payment_cross_rate_type
521 --1 payment_cross_rate
522 --2 PAYMENT_CURR_PRECISION,
523 --1 PMT_CURR_MIN_ACCT_UNIT
524 having sum(aip.amount) <> 0';
525
526 /* case when transfer to gl only = 'Y'
527 all open trnasactions */
528 C_GL_TRAN_OPEN_INVOICES_SQL CONSTANT VARCHAR2(32000) := '
529 select /*+ parallal b */distinct
530 b.balancing_segment,
531 b.account_segment,
532 b.code_combination_id,
533 b.account,
534 b.party_id,
535 b.party_site_id,
536 b.party_name,
537 b.vendor_id,
538 b.vendor_number,
539 b.vendor_site_id,
540 b.vendor_site_code,
541 b.txn_id,
542 b.txn_number,
543 b.txn_type_lookup_code,
544 b.txn_date,
545 b.txn_currency_code,
546 b.payment_currency_code,
547 b.TXN_BASE_EXCHANGE_RATE,
548 b.payment_cross_rate,
549 AP_OPEN_ITEMS_REVAL_PKG.get_revaluation_rate(b.txn_currency_code,
550 b.payment_cross_rate_type) revaluation_rate, /*Bug 14136626*/
551 b.payment_status_flag,
552 b.entered_amount,
553 b.accounted_amount,
554 --sum(nvl(a.entered_amount, 0)) pmt_entered_amount,
555 --sum(nvl(a.accounted_amount, 0)) pmt_accounted_amount,
556 b.entered_amount - sum(nvl(a.entered_amount, 0)) open_entered_amount,
557 b.accounted_amount - sum(nvl(a.accounted_amount, 0)) open_accounted_amount
558 from
559 (-- bug 9975987 removed the use of ap_ae_lines_all, placed xla_ae_lines instead
560 select /*+ parallel(aoi) leading(aoi)*/ distinct
561 200 application_id,
562 null ref_ae_header_id,
563 null temp_line_num,
564 xah.ae_header_id,
565 xal.ae_line_num,
566 aoi.code_combination_id,
567 aoi.account,
568 aoi.txn_id invoice_id,
569 --aoi.invoice_currency_code,
570 --aoi.payment_currency_code,
571 aoi.txn_base_exchange_rate,
572 aoi.txn_type_lookup_code, --bug13613111
573 txn_amount invoice_amount,
574 txn_base_amount invoice_base_amount,
575 nvl(xal.entered_dr, 0) - nvl(xal.entered_cr, 0) entered_amount,
576 nvl(xal.accounted_dr, 0) - nvl(xal.accounted_cr, 0) accounted_amount
577 from ap_open_items_reval_gt aoi,
578 ap_checks_all ac,
579 xla_transaction_entities xte,
580 xla_ae_headers xah,
581 xla_ae_lines xal
582 where ac.check_id IN (select bk.check_id
583 from ap_invoice_payments_all bk
584 where bk.invoice_id = aoi.txn_id)
585 and xte.ledger_id = $ledger_id$
586 and xah.ledger_id = $ledger_id$
587 and xal.ledger_id = $ledger_id$
588 and nvl(xte.security_id_int_1, -99) = $org_id$
589 and ac.org_id = $org_id$
590 and xte.application_id = 200
591 and nvl(xte.source_id_int_1, -99) = ac.check_id
592 and xte.entity_code = ''AP_PAYMENTS''
593 and xah.entity_id = xte.entity_id
594 and xah.application_id = 200
595 and xah.event_type_code <> ''MANUAL''
596 and xah.gl_transfer_status_code = ''Y''
597 and xah.accounting_date <= $accounting_date$
598 /* upgrade case */
599 and xah.upg_batch_id is not null
600 and xah.ae_header_id = xal.ae_header_id
601 and xal.application_id = 200
602 and xal.code_combination_id = aoi.code_combination_id
603 and xal.accounting_class_code= ''LIABILITY''
604 and ((xal.source_table = ''AP_INVOICE_PAYMENTS''
605 and exists (select 1
606 from ap_invoice_payments_all aip
607 where aip.invoice_id = aoi.txn_id
608 and aip.invoice_payment_id = xal.source_id)
609 )
610 or
611 (xal.source_table = ''AP_INVOICES''
612 and xal.source_id = aoi.txn_id
613 )
614 or
615 (xal.source_table = ''AP_INVOICE_DISTRIBUTIONS''
616 and exists (select 1
617 from ap_invoice_distributions_all aid
618 where aid.invoice_id = aoi.txn_id
619 and aid.invoice_distribution_id = xal.source_id)
620 )
621 )
622 and aoi.txn_type_lookup_code <> ''Payment''
623 $cleared_condition$
624 union
625 select /*+ leading (aoi aip xte xah xal xdl) parallel(aoi)*/ distinct
626 xdl.application_id,
627 xdl.ref_ae_header_id,
628 xdl.temp_line_num,
629 xdl.ae_header_id,
630 null ae_line_num,
631 aoi.code_combination_id,
632 aoi.account,
636 aoi.TXN_BASE_EXCHANGE_RATE,
633 aip.invoice_id,
634 --aoi.invoice_currency_code,
635 --aoi.payment_currency_code,
637 aoi.txn_type_lookup_code, --bug13613111
638 txn_amount invoice_amount,
639 txn_base_amount invoice_base_amount,
640 nvl(xdl.unrounded_entered_dr, 0) - nvl(xdl.unrounded_entered_cr, 0) entered_amount,
641 nvl(xdl.unrounded_accounted_dr, 0) - nvl(xdl.unrounded_accounted_cr, 0) accounted_amount
642 from ap_open_items_reval_gt aoi,
643 ap_invoice_payments_all aip,
644 ap_checks_all ac,
645 xla_transaction_entities xte,
646 xla_ae_headers xah,
647 xla_ae_lines xal,
648 xla_distribution_links xdl
649 where aip.invoice_id = aoi.txn_id
650 and nvl(xte.source_id_int_1, -99) = aip.check_id
651 and xte.ledger_id = $ledger_id$
652 and xah.ledger_id = $ledger_id$
653 and xal.ledger_id = $ledger_id$
654 and aip.set_of_books_id = $ledger_id$
655 and nvl(xte.security_id_int_1, -99) = $org_id$
656 and aip.org_id = $org_id$
657 and ac.check_id = aip.check_id
658
659 and xte.application_id = 200
660 and xte.entity_code = ''AP_PAYMENTS''
661 and xah.entity_id = xte.entity_id
662 and xah.application_id = 200
663 and xal.application_id = 200
664
665 and xah.gl_transfer_status_code = ''Y''
666 and xah.event_type_code <> ''MANUAL''
667 and xah.accounting_date <= $accounting_date$
668 /* non-upgrade case */
669 and xah.upg_batch_id is null
670 and xah.ae_header_id = xal.ae_header_id
671 and xdl.application_id = 200
672 and xdl.ae_header_id = xah.ae_header_id
673 and xdl.ae_line_num = xal.ae_line_num
674 and xal.code_combination_id = aoi.code_combination_id
675 and xdl.applied_to_source_id_num_1 = aip.invoice_id
676 and xal.accounting_class_code = ''LIABILITY''
677 and xdl.applied_to_entity_code = ''AP_INVOICES''
678 and aoi.txn_type_lookup_code <> ''Payment''
679 $cleared_condition$)a,
680 ap_open_items_reval_gt b
681 where b.txn_id = a.invoice_id(+)
682 and b.txn_type_lookup_code = a.txn_type_lookup_code(+) --bug13613111
683 and b.code_combination_id = a.code_combination_id(+)
684 group by b.balancing_segment,
685 b.account_segment,
686 b.code_combination_id,
687 b.account,
688 b.party_id,
689 b.party_site_id,
690 b.party_name,
691 b.vendor_id,
692 b.vendor_number,
693 b.vendor_site_id,
694 b.vendor_site_code,
695 b.txn_id,
696 b.txn_number,
697 b.txn_type_lookup_code,
698 b.txn_date,
699 b.txn_currency_code,
700 b.payment_currency_code,
701 b.TXN_BASE_EXCHANGE_RATE,
702 b.payment_cross_rate,
703 b.payment_cross_rate_type,
704 b.payment_status_flag,
705 b.entered_amount,
706 b.accounted_amount
707 having b.entered_amount <> sum(nvl(a.entered_amount, 0))
708 or b.accounted_amount <> sum(nvl(a.accounted_amount, 0)) --bug9975987
709 ';
710
711 /* case when transfer to gl only = 'N'
712 all open trnasactions
713 */
714 C_OPEN_INVOICES_SQL CONSTANT VARCHAR2(32000) := '
715 select b.balancing_segment,
716 b.account_segment,
717 b.code_combination_id,
718 b.account,
719 b.party_id,
720 b.party_site_id,
721 b.party_name,
722 b.vendor_id,
723 b.vendor_number,
724 b.vendor_site_id,
725 b.vendor_site_code,
726 b.txn_id,
727 b.txn_number,
728 b.txn_type_lookup_code,
729 b.txn_date,
730 b.txn_currency_code,
731 b.payment_currency_code,
732 b.TXN_BASE_EXCHANGE_RATE,
733 b.payment_cross_rate,
734 AP_OPEN_ITEMS_REVAL_PKG.get_revaluation_rate(b.txn_currency_code,
735 b.payment_cross_rate_type) revaluation_rate, /*Bug 14136626*/
736 b.payment_status_flag,
737 b.entered_amount,
741 from (
738 b.accounted_amount,
739 nvl(round((pay_cur_inv_entered_amt - payment_entered_amount)/b.payment_cross_rate, 2), b.entered_amount) open_entered_amount,
740 nvl(round(round((pay_cur_inv_entered_amt - payment_entered_amount)/b.payment_cross_rate, 2) * b.TXN_BASE_EXCHANGE_RATE, :g_base_precision), b.accounted_amount) open_accounted_amount
742 select /*+ leading (aoi aip) parallel(aoi)*/ distinct
743 aoi.code_combination_id,
744 aoi.party_id,
745 aoi.party_site_id,
746 aoi.vendor_id,
747 aoi.vendor_number,
748 aoi.vendor_site_id,
749 aoi.txn_id invoice_id,
750 aoi.txn_currency_code,
751 aoi.payment_currency_code,
752 aoi.TXN_BASE_EXCHANGE_RATE,
753 aoi.payment_cross_rate,
754 aoi.txn_type_lookup_code, --bug13613111
755 round(aoi.entered_amount * aoi.payment_cross_rate, 2) pay_cur_inv_entered_amt,
756 sum((nvl(aip.amount, 0) + nvl(aip.discount_taken, 0))) payment_entered_amount
757 from ap_open_items_reval_gt aoi,
758 ap_invoice_payments_all aip,
759 ap_checks_all ac
760 where aip.invoice_id = aoi.txn_id
761 and aip.set_of_books_id = $ledger_id$
762 and aip.org_id = $org_id$
763 and ac.org_id = $org_id$
764 and ac.check_id = aip.check_id
765 and aip.accounting_date <= $accounting_date$ /*Bug 14136626*/
766 and aoi.txn_type_lookup_code <> ''Payment''
767 $cleared_condition$
768 group by aoi.code_combination_id,
769 aoi.party_id,
770 aoi.party_site_id,
771 aoi.vendor_id,
772 aoi.vendor_number,
773 aoi.vendor_site_id,
774 aoi.txn_id ,
775 aoi.txn_currency_code,
776 aoi.payment_currency_code,
777 aoi.TXN_BASE_EXCHANGE_RATE,
778 aoi.payment_cross_rate,
779 aoi.txn_type_lookup_code,
780 aoi.entered_amount
781 )a, ap_open_items_reval_gt b
782 where b.txn_id = a.invoice_id(+)
783 and b.txn_type_lookup_code = a.txn_type_lookup_code(+) --bug13613111
784 and b.code_combination_id = a.code_combination_id(+)
785 and nvl((a.pay_cur_inv_entered_amt - a.payment_entered_amount), b.entered_amount) <> 0
786 and decode(nvl(sign(abs(a.pay_cur_inv_entered_amt - a.payment_entered_amount)-1), 1)
787 ,-1, decode(nvl(b.payment_status_flag, ''N'')
788 ,''Y'', 0
789 , 1
790 )
791 , 1
792 ) <> 0
793 ';
794
795 -- for getting the currency code
796 /*Bug 14136626*/
797 FUNCTION get_currency_code(l_currency_code IN gl_sets_of_books.currency_code%type)
798 RETURN VARCHAR2 IS
799 l_derive_type gl_sets_of_books.currency_code%type;
800 BEGIN
801 -- Get derive type for currency
802 SELECT nvl(derive_type,l_currency_code)
803 INTO l_derive_type
804 FROM FND_CURRENCIES
805 WHERE currency_code = l_currency_code;
806
807 return l_derive_type;
808
809 EXCEPTION
810 WHEN OTHERS THEN
811 return null;
812 END get_currency_code;
813
814 -- for getting the revaluation rate
815 /*Bug 14136626*/
816 function get_revaluation_rate(l_currency_code IN gl_sets_of_books.currency_code%type,
817 l_pay_cross_rate_type IN gl_sets_of_books.currency_code%type)
818 return number is
819
820 l_fixed_rate BOOLEAN;
821 l_multiply_factor NUMBER := 1;
822 l_revaluation_rate gl_daily_rates.conversion_rate%type;
823 l_relationship VARCHAR2(100);
824 trx_currency_code VARCHAR2(15);
825
826 begin
827
828 trx_currency_code := l_currency_code;
829
830 if trx_currency_code = g_base_currency_code then
831 return 1;
832 end if;
833
834 IF P_RATE_TYPE_LOOKUP_CODE = 'PERIOD' THEN
835 /*Bugs 14297602, 14136626*/
836 BEGIN
837 IF ((l_pay_cross_rate_type = 'EMU FIXED' AND trx_currency_code <> g_eur_code)
838 OR get_currency_code(trx_currency_code) = 'EMU') THEN
839 -- check if either the Base Currency or the Transaction Currency
840 -- is an EMU fixed rate currency, if so, we would have to
841 -- calculate an exchange rate by checking the rate in GL transaction
842 -- rates for EUR
843
844 gl_currency_api.get_relation
845 (
846 x_from_currency => trx_currency_code
847 ,x_to_currency => g_base_currency_code
848 ,x_effective_date => g_revaluation_date
849 ,x_fixed_rate => l_fixed_rate
850 ,x_relationship => l_relationship
851 );
852
853 IF l_relationship IN ('EMU-EMU', 'EMU-EURO', 'EURO-EMU') THEN
854
855 l_revaluation_rate:=
856 gl_currency_api.get_rate
857 (x_from_currency => trx_currency_code
858 ,x_to_currency => g_base_currency_code
859 ,x_conversion_date => g_revaluation_date
860 ,x_conversion_type => 'DUMMY');
861
865
862 return l_revaluation_rate;
863
864 ELSIF (l_relationship = 'EMU-OTHER') THEN
866 l_multiply_factor :=
867 gl_currency_api.get_rate
868 (x_from_currency => trx_currency_code
869 ,x_to_currency => g_eur_code
870 ,x_conversion_date => g_revaluation_date
871 ,x_conversion_type => 'DUMMY');
872
873 trx_currency_code := g_eur_code;
874 END IF;
875 ELSE
876 /* For relations 'EURO-OTHER', 'OTHER-EURO', 'OTHER-OTHER', 'OTHER-EMU' */
877 l_multiply_factor := 1;
878 END IF; -- if l_pay_cross_rate_type
879
880 select 1 / min(eop_rate)
881 into l_revaluation_rate
882 from gl_translation_rates gtr
883 where gtr.set_of_books_id = g_ledger_id
884 and gtr.to_currency_code = trx_currency_code
885 and upper(gtr.period_name) = upper(P_REVALUATION_PERIOD)
886 and gtr.actual_flag = 'A';
887
888
889 return (l_multiply_factor * l_revaluation_rate);
890 exception
891 when others then
892 return null;
893 end;
894
895 else
896 if P_RATE_TYPE_LOOKUP_CODE = 'DAILY' then
897 begin
898
899 l_revaluation_rate := ap_utilities_pkg.get_exchange_rate(trx_currency_code,
900 g_base_currency_code,
901 P_DAILY_RATE_TYPE,
902 P_DAILY_RATE_DATE,
903 'APOPITRN');
904
905 return l_revaluation_rate;
906 exception
907 when others then
908 return null;
909 end;
910 else
911 null; --invalid rate type
912 end if;
913 end if;
914
915 return null;
916 end get_revaluation_rate;
917
918 -- for due_date calculation
919 function get_due_date(p_invoice_id IN number, p_type in varchar2)
920 return date is
921 l_due_date DATE;
922 begin
923 select min(due_date)
924 into l_due_date
925 from ap_payment_schedules_all
926 where invoice_id = p_invoice_id
927 and p_type <> 'Payment';
928
929 return l_due_Date;
930 exception
931 when others then
932 return null;
933 end get_due_date;
934
935 PROCEDURE set_displayed_values IS
936 BEGIN
937
938 select name
939 into g_operating_unit_dsp
940 from hr_operating_units
941 where organization_id = P_ORG_ID;
942
943 select gsob.name,
944 gsob.set_of_books_id,
945 gsob.currency_code,
946 fc.precision,
947 nvl(fc.minimum_accountable_unit, 0),
948 fc.description,
949 gsob.chart_of_accounts_id
950 into g_gl_name_dsp,
951 g_ledger_id,
952 g_base_currency_code,
953 g_base_precision,
954 g_base_min_acct_unit,
955 g_base_currency_desc,
956 g_coa_id
957 from gl_sets_of_books gsob,
958 ap_system_parameters_all asp,
959 fnd_currencies_vl fc
960 where gsob.set_of_books_id = asp.set_of_books_id
961 and fc.currency_code = gsob.currency_code
962 and asp.org_id = P_ORG_ID;
963
964 select gps.end_date
965 into g_revaluation_date
966 from gl_period_statuses gps
967 where upper(gps.period_name) = upper(P_REVALUATION_PERIOD)
968 and gps.set_of_books_id = g_ledger_id
969 and gps.application_id = c_application_id;
970
971 select displayed_field
972 into g_rate_type_dsp
973 from ap_lookup_codes
974 where lookup_type = 'APXINREV_RATE_TYPE'
975 and lookup_code = P_RATE_TYPE_LOOKUP_CODE;
976
977 if P_RATE_TYPE_LOOKUP_CODE = 'PERIOD' then
978 if P_DAILY_RATE_TYPE is not null or P_DAILY_RATE_DATE is not null then
979 g_daily_rate_information := 'Y';
980 end if;
981 else
982
983 if P_RATE_TYPE_LOOKUP_CODE = 'DAILY' then
984 if P_DAILY_RATE_TYPE is null or P_DAILY_RATE_DATE is null then
985 g_daily_rate_error := 'Y';
986 else
987 select user_conversion_type
988 into g_daily_rate_type_dsp
989 from gl_daily_conversion_types
990 where conversion_type = P_DAILY_RATE_TYPE;
991 end if;
992 end if;
993 end if;
994
995 select meaning
996 into g_trans_to_gl_only_dsp
997 from fnd_lookups
998 where lookup_type = 'YES_NO'
999 and lookup_code = P_TRANSFER_TO_GL_ONLY;
1000
1001 select meaning
1002 into g_cleared_only_dsp
1003 from fnd_lookups
1004 where lookup_type = 'YES_NO'
1005 and lookup_code = P_CLEARED_ONLY;
1006
1007 /* Bugs 14297602, 14136626*/
1008 BEGIN
1009 g_eur_code := gl_currency_api.get_euro_code;
1010 EXCEPTION
1011 WHEN OTHERS THEN
1012 g_eur_code := 'DUMMY';
1013 END;
1014
1015 END set_displayed_values;
1016
1017 FUNCTION before_report RETURN BOOLEAN AS
1018 l_balancing_segment VARCHAR2(80);
1019 l_account_segment VARCHAR2(80);
1020 l_costcenter_segment VARCHAR2(80);
1021 l_management_segment VARCHAR2(80);
1022 l_intercompany_segment VARCHAR2(80);
1023 l_segments_column VARCHAR2(1000);
1024 l_segments_group VARCHAR2(1000);
1028 l_invoices_gt_sql VARCHAR2(32000) := '';
1025
1026 l_cleared_condition VARCHAR2(1000);
1027 l_bal_seg_condition VARCHAR2(1000) := '';
1029 l_payments_gt_sql VARCHAR2(32000) := '';
1030 l_open_sql VARCHAR2(32000) := '';
1031 BEGIN
1032
1033 /* report values to be display on report header */
1034 set_displayed_values;
1035
1036 /* get qualifier segments for the charts of accounts */
1037 xla_report_utility_pkg.get_acct_qualifier_segs(p_coa_id => g_coa_id,
1038 p_balance_segment => l_balancing_segment,
1039 p_account_segment => l_account_segment,
1040 p_cost_center_segment => l_costcenter_segment,
1041 p_management_segment => l_management_segment,
1042 p_intercompany_segment => l_intercompany_segment);
1043
1044 -- form the required conditions for the queries
1045 l_segments_column := 'gcck.' || l_balancing_segment ||
1046 ' balancing_segment,' || C_NEW_LINE || 'gcck.' ||
1047 l_account_segment || ' account_segment,';
1048 l_segments_group := 'gcck.' || l_balancing_segment || ',' ||
1049 C_NEW_LINE || 'gcck.' || l_account_segment || ' ,';
1050
1051 if P_FROM_BALANCING_SEGMENT is not null then
1052 l_bal_seg_condition := 'and gcck.' || l_balancing_segment || ' >= ' ||
1053 ''''||P_FROM_BALANCING_SEGMENT||''''; --8552975
1054 end if;
1055
1056 if P_TO_BALACING_SEGMENT is not null then
1057 l_bal_seg_condition := l_bal_seg_condition || ' and gcck.' ||
1058 l_balancing_segment || ' <= ' ||
1059 ''''||P_TO_BALACING_SEGMENT||''''; --8552975
1060 end if;
1061
1062 if P_CLEARED_ONLY = 'Y' then
1063 l_cleared_condition := ' and ac.status_lookup_code IN (''CLEARED'',
1064 ''RECONCILED'',
1065 ''CLEARED BUT UNACCOUNTED'',
1066 ''RECONCILED UNACCOUNTED'')'; --bug9483780
1067 end if;
1068
1069 -- build the gt queries
1070 if P_TRANSFER_TO_GL_ONLY = 'Y' then
1071 l_invoices_gt_sql := C_GL_TRANSFER_INVOICES_GT_SQL;
1072 l_payments_gt_sql := C_GL_TRANSFER_PAYMENTS_GT_SQL;
1073 l_open_sql := C_GL_TRAN_OPEN_INVOICES_SQL;
1074 else
1075 l_invoices_gt_sql := C_INVOICES_GT_SQL;
1076 l_payments_gt_sql := C_PAYMENTS_GT_SQL;
1077 l_open_sql := C_OPEN_INVOICES_SQL;
1078 end if;
1079
1080 l_invoices_gt_sql := replace(l_invoices_gt_sql,
1081 '$segment_columns$',
1082 l_segments_column);
1083 l_invoices_gt_sql := replace(l_invoices_gt_sql,
1084 '$segment_group$',
1085 l_segments_group);
1086 l_invoices_gt_sql := replace(l_invoices_gt_sql,
1087 '$bal_segment_condition$',
1088 l_bal_seg_condition);
1089 l_invoices_gt_sql := replace(l_invoices_gt_sql,
1090 '$ledger_id$',
1091 g_ledger_id);
1092 l_invoices_gt_sql := replace(l_invoices_gt_sql, '$org_id$', P_ORG_ID);
1093 l_invoices_gt_sql := replace(l_invoices_gt_sql,
1094 '$accounting_date$',
1095 '''' || g_revaluation_date || '''');
1096
1097 l_payments_gt_sql := replace(l_payments_gt_sql,
1098 '$segment_columns$',
1099 l_segments_column);
1100 l_payments_gt_sql := replace(l_payments_gt_sql,
1101 '$segment_group$',
1102 l_segments_group);
1103 l_payments_gt_sql := replace(l_payments_gt_sql,
1104 '$bal_segment_condition$',
1105 l_bal_seg_condition);
1106 l_payments_gt_sql := replace(l_payments_gt_sql,
1107 '$ledger_id$',
1108 g_ledger_id);
1109 l_payments_gt_sql := replace(l_payments_gt_sql, '$org_id$', P_ORG_ID);
1110 l_payments_gt_sql := replace(l_payments_gt_sql,
1111 '$accounting_date$',
1112 '''' || g_revaluation_date || '''');
1113
1114 -- execute the gt queries
1115 execute immediate 'insert into ap_open_items_reval_gt (
1116 BALANCING_SEGMENT,
1117 ACCOUNT_SEGMENT,
1118 CODE_COMBINATION_ID,
1119 ACCOUNT,
1120 PARTY_ID,
1121 PARTY_SITE_ID,
1122 PARTY_NAME,
1123 VENDOR_ID,
1124 VENDOR_NUMBER,
1125 VENDOR_SITE_ID,
1126 VENDOR_SITE_CODE,
1127 TXN_ID,
1128 TXN_NUMBER,
1129 TXN_TYPE_LOOKUP_CODE,
1130 TXN_DATE,
1131 TXN_AMOUNT,
1132 TXN_BASE_AMOUNT,
1133 TXN_CURRENCY_CODE,
1134 TXN_CURR_MIN_ACCT_UNIT,
1135 TXN_CURR_PRECISION,
1136 TXN_BASE_EXCHANGE_RATE,
1137 PAYMENT_CURRENCY_CODE,
1138 PAYMENT_CROSS_RATE_TYPE,
1139 PAYMENT_CROSS_RATE,
1140 PAYMENT_CURR_PRECISION,
1141 PMT_CURR_MIN_ACCT_UNIT,
1142 PAYMENT_STATUS_FLAG,
1143 ENTERED_AMOUNT,
1144 ACCOUNTED_AMOUNT,
1145 DUE_DATE) ' ||
1146 l_invoices_gt_sql
1147 using g_daily_rate_error;
1148
1149 execute immediate 'insert into ap_open_items_reval_gt (
1150 BALANCING_SEGMENT,
1151 ACCOUNT_SEGMENT,
1152 CODE_COMBINATION_ID,
1153 ACCOUNT,
1154 PARTY_ID,
1155 PARTY_SITE_ID,
1156 PARTY_NAME,
1157 VENDOR_ID,
1158 VENDOR_NUMBER,
1159 VENDOR_SITE_ID,
1160 VENDOR_SITE_CODE,
1161 TXN_ID,
1162 TXN_NUMBER,
1163 TXN_TYPE_LOOKUP_CODE,
1164 TXN_DATE,
1165 TXN_AMOUNT,
1166 TXN_BASE_AMOUNT,
1167 TXN_CURRENCY_CODE,
1168 TXN_CURR_MIN_ACCT_UNIT,
1169 TXN_CURR_PRECISION,
1170 TXN_BASE_EXCHANGE_RATE,
1171 PAYMENT_CURRENCY_CODE,
1172 PAYMENT_CROSS_RATE_TYPE,
1173 PAYMENT_CROSS_RATE,
1174 PAYMENT_CURR_PRECISION,
1175 PMT_CURR_MIN_ACCT_UNIT,
1176 PAYMENT_STATUS_FLAG,
1177 ENTERED_AMOUNT,
1178 ACCOUNTED_AMOUNT,
1179 DUE_DATE) ' ||
1180 l_payments_gt_sql
1181 using g_daily_rate_error, g_daily_rate_error;
1182
1183 -- build the xml query
1184 l_open_sql := replace(l_open_sql, '$ledger_id$', g_ledger_id);
1185 l_open_sql := replace(l_open_sql, '$org_id$', P_ORG_ID);
1186 l_open_sql := replace(l_open_sql,
1187 '$cleared_condition$',
1188 l_cleared_condition);
1189 l_open_sql := replace(l_open_sql,
1190 '$accounting_date$',
1191 '''' || g_revaluation_date || '''');
1192
1193 G_SQL_STATEMENT := replace(G_SQL_STATEMENT,
1194 '$open_items_query$',
1195 l_open_sql);
1196
1197 RETURN TRUE;
1198 END before_report;
1199
1200 END AP_OPEN_ITEMS_REVAL_PKG;