1 PACKAGE BODY arp_dual_currency AS
2 /* $Header: ARPLDUCB.pls 120.2 2005/09/01 13:10:54 mantani ship $ */
3 --
4 /*
5 ArpducError EXCEPTION;
6 PRAGMA EXCEPTION_INIT( ArpducError, -20000 );
7 */
8 --
9 --
10 PROCEDURE DualCurrency(
11 p_PostingControlId NUMBER,
12 p_DualCurr VARCHAR2,
13 p_GlDateFrom DATE,
14 p_GlDateTo DATE,
15 p_SetOfBooksId NUMBER,
16 p_UserSource VARCHAR2 ) IS
17
18 --PeriodLastDate DATE;
19
20 BEGIN
21 /*
22 -- This is to be used for Bill In Arrears Invoices. If gl_date
23 -- is different from its transaction date, then use this last date of
24 -- the period to be the transaction date for looking for rate.
25
26 select end_date
27 into PeriodLastDate
28 from gl_period_statuses
29 where p_GLDateFrom between start_date and end_date
30 and application_id = 101
31 and set_of_books_id = p_SetOfBooksId
32 and adjustment_period_flag = 'N'
33 and rownum = 1
34 order by period_num;
35
36 -- #1
37 -- This is for ar_cash_receipt_history,
38 -- ar_misc_cash_distributions,
39 -- CASH in ar_receivable_applications
40 --
41
42 update gl_interface int
43 set
44 (int.transaction_date,
45 int.reference3) = (
46 select
47 cr.receipt_date,
48 tre.translation_rate
49 from
50 ar_cash_receipts cr,
51 gl_transaction_rate_exceptions tre
52 where int.reference22 = cr.cash_receipt_id
53 and tre.transaction_type(+) = 'ARRA_TRADE'
54 and tre.identifier1(+) = cr.cash_receipt_id
55 and tre.identifier2(+) = -1
56 and tre.to_currency_code(+) = p_DualCurr
57 and tre.set_of_books_id(+) = cr.set_of_books_id )
58 where int.transaction_date is null
59 and int.user_je_source_name = p_UserSource
60 and int.set_of_books_id = p_SetOfBooksId
61 and int.group_id = p_PostingControlId
62 and int.accounting_date between
63 p_GlDateFrom
64 and
65 p_GlDateTo
66 and (int.reference30 in ( 'AR_CASH_RECEIPT_HISTORY',
67 'AR_MISC_CASH_DISTRIBUTIONS')
68 or
69 int.reference29 in ( 'TRADE_APP','TRADE_GL')
70 );
71
72 -- #2
73 -- This is for non CB adjustment,
74 --
75
76 update gl_interface int
77 set
78 (int.transaction_date,
79 int.reference3) = (
80 select
81 ct.trx_date,
82 tre.translation_rate
83 from
84 ar_adjustments adj,
85 ra_customer_trx ct,
86 gl_transaction_rate_exceptions tre
87 where int.reference23 = adj.adjustment_id
88 and adj.customer_trx_id = ct.customer_trx_id
89 and tre.source_table(+) = 'RA_CUSTOMER_TRX'
90 and tre.identifier1(+) = ct.customer_trx_id
91 and tre.identifier2(+) = -1
92 and tre.to_currency_code(+) = p_DualCurr
93 and tre.set_of_books_id(+) = ct.set_of_books_id )
94 where int.transaction_date is null
95 and int.user_je_source_name = p_UserSource
96 and int.set_of_books_id = p_SetOfBooksId
97 and int.group_id = p_PostingControlId
98 and int.accounting_date between
99 p_GlDateFrom
100 and
101 p_GlDateTo
102 and int.reference30 = 'AR_ADJUSTMENTS'
103 and int.reference26 <> 'CB';
104
105 -- #3
106 -- This is for CB adjustment,
107 --
108
109 update gl_interface int
110 set
111 (int.transaction_date,
112 int.reference3) = (
113 select
114 ctinv.trx_date,
115 tre.translation_rate
116 from
117 ar_adjustments adjcb,
118 ar_adjustments adjinv,
119 ra_customer_trx ctinv,
120 gl_transaction_rate_exceptions tre
121 where int.reference23 = adjcb.adjustment_id
122 and adjinv.receivables_trx_id = -11
123 and adjinv.chargeback_customer_trx_id = adjcb.customer_trx_id
124 and adjinv.customer_trx_id = ctinv.customer_trx_id
125 and tre.source_table(+) = 'RA_CUSTOMER_TRX'
126 and tre.identifier1(+) = ctinv.customer_trx_id
127 and tre.identifier2(+) = -1
128 and tre.to_currency_code(+) = p_DualCurr
129 and tre.set_of_books_id(+) = ctinv.set_of_books_id )
130 where int.transaction_date is null
131 and int.user_je_source_name = p_UserSource
132 and int.set_of_books_id = p_SetOfBooksId
133 and int.group_id = p_PostingControlId
134 and int.accounting_date between
135 p_GlDateFrom
136 and
137 p_GlDateTo
138 and int.reference30 = 'AR_ADJUSTMENTS'
139 and int.reference26 = 'CB';
140
141 -- #4
142 -- This is for non CB and non regular CM transactions in
143 -- ra_cust_trx_line_gl_dist
144 --
145
146 update gl_interface int
147 set
148 (int.transaction_date,
149 int.reference3) = (
150 select
151 decode( ct.invoicing_rule_id,
152 -3,
153 decode( ct.trx_date,
154 int.accounting_date,
155 ct.trx_date,
156 PeriodLastDate ),
157 ct.trx_date ),
158 tre.translation_rate
159 from
160 ra_customer_trx ct,
161 gl_transaction_rate_exceptions tre
162 where int.reference22 = ct.customer_trx_id
163 and tre.source_table(+) = 'RA_CUSTOMER_TRX'
164 and tre.identifier1(+) = ct.customer_trx_id
165 and tre.identifier2(+) = -1
166 and tre.to_currency_code(+) = p_DualCurr
167 and tre.set_of_books_id(+) = ct.set_of_books_id )
168 where int.transaction_date is null
169 and int.user_je_source_name = p_UserSource
170 and int.set_of_books_id = p_SetOfBooksId
171 and int.group_id = p_PostingControlId
172 and int.accounting_date between
173 p_GlDateFrom
174 and
175 p_GlDateTo
176 and int.reference30 = 'RA_CUST_TRX_LINE_GL_DIST'
177 and int.reference28 <> 'CB'
178 and int.reference22 in
179 (select ct2.customer_trx_id
180 from ra_customer_trx ct2
181 where ct2.customer_trx_id = int.reference22
182 and ct2.previous_customer_trx_id is null );
183
184 -- #5
185 -- This is for Regular CM in
186 -- ra_cust_trx_line_gl_dist
187 --
188
189 update gl_interface int
190 set
191 (int.transaction_date,
192 int.reference3) = (
193 select
194 ctinv.trx_date,
195 tre.translation_rate
196 from
197 ra_customer_trx ctinv,
198 ra_customer_trx ctcm,
199 gl_transaction_rate_exceptions tre
200 where int.reference22 = ctcm.customer_trx_id
201 and ctcm.previous_customer_trx_id = ctinv.customer_trx_id
202 and tre.source_table(+) = 'RA_CUSTOMER_TRX'
203 and tre.identifier1(+) = ctinv.customer_trx_id
204 and tre.identifier2(+) = -1
205 and tre.to_currency_code(+) = p_DualCurr
206 and tre.set_of_books_id(+) = ctinv.set_of_books_id )
207 where int.transaction_date is null
208 and int.user_je_source_name = p_UserSource
209 and int.set_of_books_id = p_SetOfBooksId
210 and int.group_id = p_PostingControlId
211 and int.accounting_date between
212 p_GlDateFrom
213 and
214 p_GlDateTo
215 and int.reference30 = 'RA_CUST_TRX_LINE_GL_DIST'
216 and int.reference22 in
217 (select ct2.customer_trx_id
218 from ra_customer_trx ct2
219 where ct2.customer_trx_id = int.reference22
220 and ct2.previous_customer_trx_id is not null);
221
222 -- #6
223 -- This is for CB in
224 -- ra_cust_trx_line_gl_dist
225 --
226
227 update gl_interface int
228 set
229 (int.transaction_date,
230 int.reference3) = (
231 select
232 ctinv.trx_date,
233 tre.translation_rate
234 from
235 ar_adjustments adj,
236 ra_customer_trx ctinv,
237 gl_transaction_rate_exceptions tre
238 where int.reference22 = adj.chargeback_customer_trx_id
239 and adj.receivables_trx_id = -11
240 and adj.customer_trx_id = ctinv.customer_trx_id
241 and tre.source_table(+) = 'RA_CUSTOMER_TRX'
242 and tre.identifier1(+) = ctinv.customer_trx_id
243 and tre.identifier2(+) = -1
244 and tre.to_currency_code(+) = p_DualCurr
245 and tre.set_of_books_id(+) = ctinv.set_of_books_id )
246 where int.transaction_date is null
247 and int.user_je_source_name = p_UserSource
248 and int.set_of_books_id = p_SetOfBooksId
249 and int.group_id = p_PostingControlId
250 and int.accounting_date between
251 p_GlDateFrom
252 and
253 p_GlDateTo
254 and int.reference30 = 'RA_CUST_TRX_LINE_GL_DIST'
255 and int.reference28 = 'CB';
256
257
258 -- #7
259 -- This is for non CB Discount
260 -- in ar_receivable_applications
261 --
262
263 update gl_interface int
264 set
265 (int.transaction_date,
266 int.reference3) = (
267 select
268 ct.trx_date,
269 tre.translation_rate
270 from
271 ar_receivable_applications ra,
272 ra_customer_trx ct,
273 gl_transaction_rate_exceptions tre
274 where int.reference23 = ra.receivable_application_id
275 and ra.applied_customer_trx_id = ct.customer_trx_id
276 and tre.source_table(+) = 'RA_CUSTOMER_TRX'
277 and tre.identifier1(+) = ct.customer_trx_id
278 and tre.identifier2(+) = -1
279 and tre.to_currency_code(+) = p_DualCurr
280 and tre.set_of_books_id(+) = ct.set_of_books_id )
281 where int.transaction_date is null
282 and int.user_je_source_name = p_UserSource
283 and int.set_of_books_id = p_SetOfBooksId
284 and int.group_id = p_PostingControlId
285 and int.accounting_date between
286 p_GlDateFrom
287 and
288 p_GlDateTo
289 and int.reference30 = 'AR_RECEIVABLE_APPLICATIONS'
290 and int.reference26 <> 'CB'
291 and int.reference29 like '%_DISC%';
292
293 -- #8
294 -- This is for CB Discount
295 -- in ar_receivable_applications
296 --
297
298 update gl_interface int
299 set
300 (int.transaction_date,
301 int.reference3) = (
302 select
303 ctinv.trx_date,
304 tre.translation_rate
305 from
306 ar_receivable_applications ra,
307 ar_adjustments adj,
308 ra_customer_trx ctinv,
309 gl_transaction_rate_exceptions tre
310 where int.reference23 = ra.receivable_application_id
311 and ra.applied_customer_trx_id = adj.chargeback_customer_trx_id
312 and adj.receivables_trx_id = -11
313 and adj.customer_trx_id = ctinv.customer_trx_id
314 and tre.source_table(+) = 'RA_CUSTOMER_TRX'
315 and tre.identifier1(+) = ctinv.customer_trx_id
316 and tre.identifier2(+) = -1
317 and tre.to_currency_code(+) = p_DualCurr
318 and tre.set_of_books_id(+) = ctinv.set_of_books_id )
319 where int.transaction_date is null
320 and int.user_je_source_name = p_UserSource
321 and int.set_of_books_id = p_SetOfBooksId
322 and int.group_id = p_PostingControlId
323 and int.accounting_date between
324 p_GlDateFrom
325 and
326 p_GlDateTo
327 and int.reference30 = 'AR_RECEIVABLE_APPLICATIONS'
328 and int.reference26 = 'CB'
329 and int.reference29 like '%_DISC%';
330
331 -- #9
332 -- This is for OnAccnt CM
333 -- in ar_receivable_applications
334 --
335
336 update gl_interface int
337 set
338 (int.transaction_date,
339 int.reference3) = (
340 select
341 ct.trx_date,
342 tre.translation_rate
343 from
344 ar_receivable_applications ra,
345 ra_customer_trx ct,
346 gl_transaction_rate_exceptions tre
347 where int.reference23 = ra.receivable_application_id
348 and ra.customer_trx_id = ct.customer_trx_id
349 and tre.source_table(+) = 'RA_CUSTOMER_TRX'
350 and tre.identifier1(+) = ct.customer_trx_id
351 and tre.identifier2(+) = -1
352 and tre.to_currency_code(+) = p_DualCurr
353 and tre.set_of_books_id(+) = ct.set_of_books_id )
354 where int.transaction_date is null
355 and int.user_je_source_name = p_UserSource
356 and int.set_of_books_id = p_SetOfBooksId
357 and int.group_id = p_PostingControlId
358 and int.accounting_date between
359 p_GlDateFrom
360 and
361 p_GlDateTo
362 and int.reference30 = 'AR_RECEIVABLE_APPLICATIONS'
363 and int.reference28 = 'CMAPP'
364 and int.reference23 in
365 (select ra2.receivable_application_id
366 from ar_receivable_applications ra2,
367 ra_customer_trx ct2
368 where ra2.receivable_application_id = int.reference23
369 and ra2.customer_trx_id = ct2.customer_trx_id
370 and ct2.previous_customer_trx_id is null);
371
372 -- #10
373 -- This is for Regular CM applies to non CB
374 -- in ar_receivable_applications
375 --
376
377 update gl_interface int
378 set
379 (int.transaction_date,
380 int.reference3) = (
381 select
382 ctinv.trx_date,
383 tre.translation_rate
384 from
385 ar_receivable_applications ra,
386 ra_customer_trx ctinv,
387 gl_transaction_rate_exceptions tre
388 where int.reference23 = ra.receivable_application_id
389 and ra.applied_customer_trx_id = ctinv.customer_trx_id
390 and tre.source_table(+) = 'RA_CUSTOMER_TRX'
391 and tre.identifier1(+) = ctinv.customer_trx_id
392 and tre.identifier2(+) = -1
393 and tre.to_currency_code(+) = p_DualCurr
394 and tre.set_of_books_id(+) = ctinv.set_of_books_id )
395 where int.transaction_date is null
399 and int.accounting_date between
396 and int.user_je_source_name = p_UserSource
397 and int.set_of_books_id = p_SetOfBooksId
398 and int.group_id = p_PostingControlId
400 p_GlDateFrom
401 and
402 p_GlDateTo
403 and int.reference30 = 'AR_RECEIVABLE_APPLICATIONS'
404 and int.reference28 = 'CMAPP'
405 and int.reference26 <> 'CB'
406 and int.reference23 in
407 (select ra2.receivable_application_id
408 from ar_receivable_applications ra2,
409 ra_customer_trx ct2
410 where ra2.receivable_application_id = int.reference23
411 and ra2.customer_trx_id = ct2.customer_trx_id
412 and ct2.previous_customer_trx_id is not null);
413
414
415 -- #11
416 -- This is for Regular CM applies to CB
417 -- in ar_receivable_applications
418 --
419
420 update gl_interface int
421 set
422 (int.transaction_date,
423 int.reference3) = (
424 select
425 ctinv.trx_date,
426 tre.translation_rate
427 from
428 ar_receivable_applications ra,
429 ar_adjustments adj,
430 ra_customer_trx ctinv,
431 gl_transaction_rate_exceptions tre
432 where int.reference23 = ra.receivable_application_id
433 and ra.applied_customer_trx_id = adj.chargeback_customer_trx_id
434 and adj.receivables_trx_id = -11
435 and adj.customer_trx_id = ctinv.customer_trx_id
436 and tre.source_table(+) = 'RA_CUSTOMER_TRX'
437 and tre.identifier1(+) = ctinv.customer_trx_id
438 and tre.identifier2(+) = -1
439 and tre.to_currency_code(+) = p_DualCurr
440 and tre.set_of_books_id(+) = ctinv.set_of_books_id )
441 where int.transaction_date is null
442 and int.user_je_source_name = p_UserSource
443 and int.set_of_books_id = p_SetOfBooksId
444 and int.group_id = p_PostingControlId
445 and int.accounting_date between
446 p_GlDateFrom
447 and
448 p_GlDateTo
449 and int.reference30 = 'AR_RECEIVABLE_APPLICATIONS'
450 and int.reference28 = 'CMAPP'
451 and int.reference26 = 'CB'
452 and int.reference23 in
453 (select ra2.receivable_application_id
454 from ar_receivable_applications ra2,
455 ra_customer_trx ct2
456 where ra2.receivable_application_id = int.reference23
457 and ra2.customer_trx_id = ct2.customer_trx_id
458 and ct2.previous_customer_trx_id is not null);
459 --
460 EXCEPTION
461 WHEN OTHERS THEN
462 arp_standard.debug( 'Exception:arp_dual_currency.DualCurrency( ... ):'||sqlerrm );
463 RAISE_APPLICATION_ERROR( -20000, sqlerrm||'DualCurrency( ... ):' );
464 */
465 NULL;
466 END;
467 --
468 END arp_dual_currency;
[Home] [Help]