[Home] [Help]
PACKAGE BODY: APPS.AR_UPGRADE_CASH_ACCRUAL
Source
1 PACKAGE BODY ar_upgrade_cash_accrual AS
2 /* $Header: ARUPGLZB.pls 120.13.12010000.3 2008/11/14 06:05:05 dgaurab ship $ */
3
4 g_ae_sys_rec arp_acct_main.ae_sys_rec_type;
5 g_ind_current NUMBER := -9;
6 g_run_tot NUMBER := 0;
7 g_run_acctd_tot NUMBER := 0;
8
9 PROCEDURE create_cash_distributions;
10
11 --PROCEDURE create_mfar_distributions;
12
13
14
15 PROCEDURE local_log
16 (p_msg_text IN VARCHAR2,
17 p_msg_level IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT)
18 IS
19 BEGIN
20 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
21 arp_standard.debug(p_msg_text);
22 END IF;
23 END;
24
25 PROCEDURE log(
26 message IN VARCHAR2,
27 newline IN BOOLEAN DEFAULT TRUE) IS
28 BEGIN
29 IF NVL(fnd_global.CONC_REQUEST_ID,0) <> 0 THEN
30 IF message = 'NEWLINE' THEN
31 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
32 ELSIF (newline) THEN
33 FND_FILE.put_line(fnd_file.log,message);
34 ELSE
35 FND_FILE.put(fnd_file.log,message);
36 END IF;
37 ELSE
38 local_log(message);
39 END IF;
40 END log;
41
42
43
44 FUNCTION fct_acct_amt
45 (p_amt IN NUMBER,
46 p_base_amt IN NUMBER,
47 p_base_acctd_amt IN NUMBER,
48 p_currency_code IN VARCHAR2,
49 p_base_currency IN VARCHAR2,
50 p_ind_id IN NUMBER)
51 RETURN NUMBER
52 IS
53 l_acctd_amt NUMBER;
54 BEGIN
55 IF g_ind_current <> p_ind_id THEN
56 g_run_tot := 0;
57 g_run_acctd_tot := 0;
58 g_ind_current := p_ind_id;
59 END IF;
60 g_run_tot := g_run_tot + p_amt;
61 IF (p_base_amt <> p_base_acctd_amt) AND
62 (p_currency_code <> p_base_currency) AND
63 (p_base_acctd_amt <> 0)
64 THEN
65 l_acctd_amt := arpcurr.CurrRound(g_run_tot / p_base_amt * p_base_acctd_amt , p_base_currency) - g_run_acctd_tot;
66 ELSE
67 l_acctd_amt := p_amt;
68 END IF;
69 g_run_acctd_tot := g_run_acctd_tot + l_acctd_amt;
70 RETURN l_acctd_amt;
71 END;
72
73
74
75 PROCEDURE Init_Curr_Details
76 (p_sob_id IN NUMBER,
77 p_org_id IN NUMBER,
78 x_accounting_method IN OUT NOCOPY ar_system_parameters.accounting_method%TYPE)
79 IS
80 BEGIN
81 log('Init_Curr_Details +');
82 SELECT sob.set_of_books_id,
83 sob.chart_of_accounts_id,
84 sob.currency_code,
85 c.precision,
86 c.minimum_accountable_unit,
87 sysp.code_combination_id_gain,
88 sysp.code_combination_id_loss,
89 sysp.code_combination_id_round,
90 sysp.accounting_method
91 INTO g_ae_sys_rec.set_of_books_id,
92 g_ae_sys_rec.coa_id,
93 g_ae_sys_rec.base_currency,
94 g_ae_sys_rec.base_precision,
95 g_ae_sys_rec.base_min_acc_unit,
96 g_ae_sys_rec.gain_cc_id,
97 g_ae_sys_rec.loss_cc_id,
98 g_ae_sys_rec.round_cc_id,
99 x_accounting_method
100 FROM ar_system_parameters_all sysp,
101 gl_sets_of_books sob,
102 fnd_currencies c
103 WHERE sysp.org_id = p_org_id
104 AND sob.set_of_books_id = sysp.set_of_books_id --would be the row returned from multi org view
105 AND sob.currency_code = c.currency_code;
106 log('Init_Curr_Details -');
107 EXCEPTION
108 WHEN NO_DATA_FOUND THEN
109 log('Init_Curr_Details - NO_DATA_FOUND' );
110 RAISE;
111 WHEN OTHERS THEN
112 log('EXCEPTION OTHERS : '||SQLERRM);
113 RAISE;
114 END Init_Curr_Details;
115
116
117 PROCEDURE stamping_11i_app_post
118 IS
119 BEGIN
120 log(' stamping_11i_app_post +');
121 UPDATE ar_receivable_applications_all ra
122 SET ra.upgrade_method = 'R12_11ICASH_POST'
123 WHERE ra.receivable_application_id IN (
124 SELECT app.receivable_application_id
125 FROM xla_events_gt evt,
126 ar_receivable_applications_all app
127 WHERE evt.event_type_code IN ( 'RECP_CREATE' ,'RECP_UPDATE' ,
128 'RECP_RATE_ADJUST' ,'RECP_REVERSE' ,
129 'CM_CREATE' ,'CM_UPDATE')
130 AND evt.event_id = app.event_id
131 AND app.status = 'APP'
132 AND app.upgrade_method IS NULL
133 AND EXISTS (SELECT '1'
134 FROM ar_adjustments_all adj
135 WHERE adj.customer_trx_id = app.applied_customer_trx_id
136 AND adj.upgrade_method = '11I'
137 AND adj.status = 'A'
138 AND adj.postable = 'Y'));
139 log(' stamping_11i_app_post -');
140 EXCEPTION
141 WHEN OTHERS THEN
142 log('EXCEPTION OTHERS: stamping_11i_cash_app_post :'||SQLERRM);
143 END;
144
145
146
147
148
149 PROCEDURE conv_amt
150 (p_acctd_amt IN NUMBER,
151 p_trx_currency IN VARCHAR2,
152 p_base_currency IN VARCHAR2,
153 --
154 p_line_amt IN NUMBER,
155 p_tax_amt IN NUMBER,
156 p_frt_amt IN NUMBER,
157 p_chrg_amt IN NUMBER,
158 --
159 x_line_acctd_amt OUT NOCOPY NUMBER,
160 x_tax_acctd_amt OUT NOCOPY NUMBER,
161 x_frt_acctd_amt OUT NOCOPY NUMBER,
162 x_chrg_acctd_amt OUT NOCOPY NUMBER)
163 IS
164 l_same VARCHAR2(1) := 'N';
165 l_run_tot NUMBER := 0;
166 l_run_acctd_tot NUMBER := 0;
167 --
168 l_line NUMBER;
169 l_tax NUMBER;
170 l_frt NUMBER;
171 l_chrg NUMBER;
172 l_acctd_line NUMBER;
173 l_acctd_tax NUMBER;
174 l_acctd_frt NUMBER;
175 l_acctd_chrg NUMBER;
176 l_base NUMBER;
177 BEGIN
178 log('conv_amt +');
179 --
180 -- Note the p_xxx_amt should not be null at this point
181 -- The code is not checking the null value of the argument for perf
182 --
183 l_base := p_line_amt + p_tax_amt + p_frt_amt + p_chrg_amt;
184 IF l_base = 0 THEN
185 l_same := 'Y';
186 ELSE
187 IF (p_trx_currency = p_base_currency) OR
188 (l_base = p_acctd_amt)
189 THEN
190 l_same := 'Y';
191 END IF;
192 END IF;
193
194 IF l_same = 'N' THEN
195 -- line
196 l_line := p_line_amt;
197 l_run_tot := l_run_tot + l_line;
198 l_acctd_line := arpcurr.CurrRound( l_run_tot * p_acctd_amt /l_base , p_base_currency ) - l_run_acctd_tot;
199 l_run_acctd_tot := l_run_acctd_tot + l_acctd_line;
200 -- tax
201 l_tax := p_tax_amt;
202 l_run_tot := l_run_tot + l_tax;
203 l_acctd_tax := arpcurr.CurrRound( l_run_tot * p_acctd_amt /l_base , p_base_currency ) - l_run_acctd_tot;
204 l_run_acctd_tot := l_run_acctd_tot + l_acctd_tax;
205 -- freight
206 l_frt := p_frt_amt;
207 l_run_tot := l_run_tot + l_frt;
208 l_acctd_frt := arpcurr.CurrRound( l_run_tot * p_acctd_amt /l_base , p_base_currency ) - l_run_acctd_tot;
209 l_run_acctd_tot := l_run_acctd_tot + l_acctd_frt;
210 -- charges
211 l_chrg := p_chrg_amt;
212 l_run_tot := l_run_tot + l_chrg;
213 l_acctd_chrg := arpcurr.CurrRound( l_run_tot * p_acctd_amt /l_base , p_base_currency ) - l_run_acctd_tot;
214 l_run_acctd_tot := l_run_acctd_tot + l_acctd_chrg;
215 ELSE
216 -- Line
217 l_line := p_line_amt;
218 l_acctd_line := l_line;
219 -- tax
220 l_tax := p_tax_amt;
221 l_acctd_tax := l_tax;
222 -- freight
223 l_frt := p_frt_amt;
224 l_acctd_frt := l_frt;
225 -- charges
226 l_chrg := p_chrg_amt;
227 l_acctd_chrg := l_chrg;
228 END IF;
229
230 x_line_acctd_amt := l_acctd_line;
231 x_tax_acctd_amt := l_acctd_tax;
232 x_frt_acctd_amt := l_acctd_frt;
233 x_chrg_acctd_amt := l_acctd_chrg;
234
235 log(' x_line_acctd_amt :'|| x_line_acctd_amt);
236 log(' x_tax_acctd_amt :'|| x_tax_acctd_amt);
237 log(' x_frt_acctd_amt :'|| x_frt_acctd_amt);
238 log(' x_chrg_acctd_amt :'|| x_chrg_acctd_amt);
239 log('conv_amt -');
240 END;
241
242
243
244
245
246
247 PROCEDURE get_direct_inv_dist
248 (p_mode IN VARCHAR2,
249 p_trx_id IN NUMBER DEFAULT NULL,
250 p_gt_id IN NUMBER DEFAULT NULL)
251 IS
252 BEGIN
253 log('get_direct_inv_dist +');
254 log(' p_mode :'||p_mode);
255 log(' p_trx_id :'||p_trx_id);
256 log(' p_gt_id :'||p_gt_id);
257
258 IF p_mode = 'OLTP' THEN
259 INSERT INTO RA_AR_GT
260 ( GT_ID ,
261 AMT ,
262 ACCTD_AMT ,
263 ACCOUNT_CLASS ,
264 CCID_SECONDARY ,
265 REF_CUST_TRX_LINE_GL_DIST_ID,
266 REF_CUSTOMER_TRX_LINE_ID ,
267 REF_CUSTOMER_TRX_ID ,
268 TO_CURRENCY ,
269 BASE_CURRENCY ,
270 -- ADJ and APP Elmt
271 DIST_AMT ,
272 DIST_ACCTD_AMT ,
273 DIST_CHRG_AMT ,
274 DIST_CHRG_ACCTD_AMT ,
275 DIST_FRT_AMT ,
276 DIST_FRT_ACCTD_AMT ,
277 DIST_TAX_AMT ,
278 DIST_TAX_ACCTD_AMT ,
279 -- Buc
280 tl_alloc_amt ,
281 tl_alloc_acctd_amt ,
282 tl_chrg_alloc_amt ,
283 tl_chrg_alloc_acctd_amt,
284 tl_frt_alloc_amt ,
285 tl_frt_alloc_acctd_amt,
286 tl_tax_alloc_amt ,
287 tl_tax_alloc_acctd_amt,
288 -- ED Elmt
289 DIST_ed_AMT,
290 DIST_ed_ACCTD_AMT,
291 DIST_ed_chrg_AMT,
292 DIST_ed_chrg_ACCTD_AMT,
293 DIST_ed_frt_AMT ,
294 DIST_ed_frt_ACCTD_AMT,
295 DIST_ed_tax_AMT ,
296 DIST_ed_tax_ACCTD_AMT,
297 --
298 tl_ed_alloc_amt ,
299 tl_ed_alloc_acctd_amt ,
300 tl_ed_chrg_alloc_amt ,
301 tl_ed_chrg_alloc_acctd_amt,
302 tl_ed_frt_alloc_amt ,
303 tl_ed_frt_alloc_acctd_amt,
304 tl_ed_tax_alloc_amt ,
305 tl_ed_tax_alloc_acctd_amt,
306 -- UNED
307 DIST_uned_AMT ,
308 DIST_uned_ACCTD_AMT ,
309 DIST_uned_chrg_AMT ,
310 DIST_uned_chrg_ACCTD_AMT ,
311 DIST_uned_frt_AMT ,
312 DIST_uned_frt_ACCTD_AMT ,
313 DIST_uned_tax_AMT ,
314 DIST_uned_tax_ACCTD_AMT ,
315 --
316 tl_uned_alloc_amt ,
317 tl_uned_alloc_acctd_amt ,
318 tl_uned_chrg_alloc_amt ,
319 tl_uned_chrg_alloc_acctd_amt,
320 tl_uned_frt_alloc_amt ,
321 tl_uned_frt_alloc_acctd_amt,
322 tl_uned_tax_alloc_amt ,
323 tl_uned_tax_alloc_acctd_amt,
324 --
325 source_type ,
326 source_table ,
327 source_id ,
328 line_type,
329 --
330 group_id,
331 --{HYUBPAGP
332 source_data_key1 ,
333 source_data_key2 ,
334 source_data_key3 ,
335 source_data_key4 ,
336 source_data_key5 ,
337 --}
338 gp_level,
339 --
340 set_of_books_id,
341 sob_type,
342 se_gt_id,
343 --{Taxable Amount
344 tax_link_id,
345 tax_inc_flag
346 --}
347 )
348 SELECT
349 p_gt_id -- GT_ID
350 , ctlgd.amount -- AMT
351 , ctlgd.acctd_amount -- ACCTD_AMT
352 , DECODE(ctl.line_type,'LINE','REV',
353 'TAX','TAX',
354 'FREIGHT','FREIGHT',
355 'CHARGES','CHARGES',
356 'CB','REV') -- ACCOUNT_CLASS
357 , DECODE(ctlgd.collected_tax_ccid,
358 NULL, ctlgd.code_combination_id,
359 0 , ctlgd.code_combination_id,
360 ctlgd.collected_tax_ccid) -- CCID_SECONDARY
361 , ctlgd.cust_trx_line_gl_dist_id -- REF_CUST_TRX_LINE_GL_DIST_ID
362 , DECODE(ctl.line_type,'LINE',-6,
363 'TAX',-8,
364 'FREIGHT',-9,
365 'CHARGES',-7,
366 'CB',-6) -- REF_CUSTOMER_TRX_LINE_ID
367 , trx.customer_trx_id -- REF_CUSTOMER_TRX_ID
368 , trx.invoice_currency_code -- TO_CURRENCY
369 , NULL -- BASE_CURRENCY
370 -- ADJ and APP Elmt
371 , DECODE(ctl.line_type,'LINE',ctlgd.amount,0) -- DIST_AMT
372 , DECODE(ctl.line_type,'LINE',ctlgd.acctd_amount,0) -- DIST_ACCTD_AMT
373 , DECODE(ctl.line_type,'CHARGES',ctlgd.amount,0) -- DIST_CHRG_AMT
374 , DECODE(ctl.line_type,'CHARGES',ctlgd.acctd_amount,0) -- DIST_CHRG_ACCTD_AMT
375 , DECODE(ctl.line_type,'FREIGHT',ctlgd.amount,0) -- DIST_FRT_AMT
376 , DECODE(ctl.line_type,'FREIGHT',ctlgd.acctd_amount,0) -- DIST_FRT_ACCTD_AMT
377 , DECODE(ctl.line_type,'TAX',ctlgd.amount,0) -- DIST_TAX_AMT
378 , DECODE(ctl.line_type,'TAX',ctlgd.acctd_amount,0) -- DIST_TAX_ACCTD_AMT
379 -- Buc
380 , 0 -- tl_alloc_amt
381 , 0 -- tl_alloc_acctd_amt
382 , 0 -- tl_chrg_alloc_amt
383 , 0 -- tl_chrg_alloc_acctd_amt
384 , 0 -- tl_frt_alloc_amt
385 , 0 -- tl_frt_alloc_acctd_amt
386 , 0 -- tl_tax_alloc_amt
387 , 0 -- tl_tax_alloc_acctd_amt
388 -- ED Elmt
389 , DECODE(ctl.line_type,'LINE',ctlgd.amount,
390 'CB' ,ctlgd.amount, 0) -- DIST_ed_AMT
391 , DECODE(ctl.line_type,'LINE',ctlgd.acctd_amount,
392 'CB' ,ctlgd.acctd_amount, 0) -- DIST_ed_ACCTD_AMT
393 , DECODE(ctl.line_type,'CHARGES',ctlgd.amount,0) -- DIST_ed_chrg_AMT
394 , DECODE(ctl.line_type,'CHARGES',ctlgd.acctd_amount,0) -- DIST_ed_chrg_ACCTD_AMT
395 , DECODE(ctl.line_type,'FREIGHT',ctlgd.amount,0) -- DIST_ed_frt_AMT
396 , DECODE(ctl.line_type,'FREIGHT',ctlgd.acctd_amount,0) -- DIST_ed_frt_ACCTD_AMT
397 , DECODE(ctl.line_type,'TAX',ctlgd.amount,0) -- DIST_ed_tax_AMT
398 , DECODE(ctl.line_type,'TAX',ctlgd.acctd_amount,0) -- DIST_ed_tax_ACCTD_AMT
399 --
400 , 0 -- tl_ed_alloc_amt
401 , 0 -- tl_ed_alloc_acctd_amt
402 , 0 -- tl_ed_chrg_alloc_amt
403 , 0 -- tl_ed_chrg_alloc_acctd_amt
404 , 0 -- tl_ed_frt_alloc_amt
405 , 0 -- tl_ed_frt_alloc_acctd_amt
406 , 0 -- tl_ed_tax_alloc_amt
407 , 0 -- tl_ed_tax_alloc_acctd_amt
408 -- UNED
409 , DECODE(ctl.line_type,'LINE',ctlgd.amount,0) -- DIST_uned_AMT
410 , DECODE(ctl.line_type,'LINE',ctlgd.acctd_amount,0) -- DIST_uned_ACCTD_AMT
411 , DECODE(ctl.line_type,'CHARGES',ctlgd.amount,0) -- DIST_uned_chrg_AMT
412 , DECODE(ctl.line_type,'CHARGES',ctlgd.acctd_amount,0) -- DIST_uned_chrg_ACCTD_AMT
413 , DECODE(ctl.line_type,'FREIGHT',ctlgd.amount,0) -- DIST_uned_frt_AMT
414 , DECODE(ctl.line_type,'FREIGHT',ctlgd.acctd_amount,0) -- DIST_uned_frt_ACCTD_AMT
415 , DECODE(ctl.line_type,'TAX',ctlgd.amount,0) -- DIST_uned_tax_AMT
416 , DECODE(ctl.line_type,'TAX',ctlgd.acctd_amount,0) -- DIST_uned_tax_ACCTD_AMT
417 --
418 , 0 -- tl_uned_alloc_amt
419 , 0 -- tl_uned_alloc_acctd_amt
420 , 0 -- tl_uned_chrg_alloc_amt
421 , 0 -- tl_uned_chrg_alloc_acctd_amt
422 , 0 -- tl_uned_frt_alloc_amt
426 --
423 , 0 -- tl_uned_frt_alloc_acctd_amt
424 , 0 -- tl_uned_tax_alloc_amt
425 , 0 -- tl_uned_tax_alloc_acctd_amt
427 , NULL -- source_type
428 , 'CTLGD' -- source_table
429 , NULL -- source_id
430 , ctl.line_type -- line_type
431 --
432 , NULL -- group_id
433 , '00' -- source_data_key1
434 , '00' -- source_data_key2
435 , '00' -- source_data_key3
436 , '00' -- source_data_key4
437 , '00' -- source_data_key5
438 , 'D' -- gp_level
439 --
440 , trx.set_of_books_id -- set_of_books_id
441 , 'P' -- sob_type
442 , USERENV('SESSIONID') -- se_gt_id
443 --{Taxable Amount
444 , NULL -- tax_link_id
445 , NULL -- tax_inc_flag
446 --}
447 FROM ra_customer_trx trx,
448 ra_customer_trx_lines ctl,
449 ra_cust_trx_line_gl_dist ctlgd
450 WHERE trx.customer_trx_id = p_trx_id
451 AND ctl.customer_trx_id = trx.customer_trx_id
452 AND ctl.customer_trx_line_id = ctlgd.customer_trx_line_id
453 AND ctl.line_type IN ('LINE','TAX','FREIGHT','CHARGES','CB')
454 AND ctlgd.account_class IN ('REV','SUSPENSE','UNBILL','UNEARN','FREIGHT','TAX')
455 AND ctlgd.account_set_flag = 'N'
456 AND NOT EXISTS (SELECT '1' FROM RA_AR_GT
457 WHERE source_table = 'CTLGD'
458 AND REF_CUSTOMER_TRX_ID = p_trx_id );
459
460 ELSIF p_mode = 'BATCH' THEN
461
462 INSERT INTO RA_AR_GT
463 ( GT_ID ,
464 AMT ,
465 ACCTD_AMT ,
466 ACCOUNT_CLASS ,
467 CCID_SECONDARY ,
468 REF_CUST_TRX_LINE_GL_DIST_ID,
469 REF_CUSTOMER_TRX_LINE_ID ,
470 REF_CUSTOMER_TRX_ID ,
471 TO_CURRENCY ,
472 BASE_CURRENCY ,
473 -- ADJ and APP Elmt
474 DIST_AMT ,
475 DIST_ACCTD_AMT ,
476 DIST_CHRG_AMT ,
477 DIST_CHRG_ACCTD_AMT ,
478 DIST_FRT_AMT ,
479 DIST_FRT_ACCTD_AMT ,
480 DIST_TAX_AMT ,
481 DIST_TAX_ACCTD_AMT ,
482 -- Buc
483 tl_alloc_amt ,
484 tl_alloc_acctd_amt ,
485 tl_chrg_alloc_amt ,
486 tl_chrg_alloc_acctd_amt,
487 tl_frt_alloc_amt ,
488 tl_frt_alloc_acctd_amt,
489 tl_tax_alloc_amt ,
490 tl_tax_alloc_acctd_amt,
491 -- ED Elmt
492 DIST_ed_AMT,
493 DIST_ed_ACCTD_AMT,
494 DIST_ed_chrg_AMT,
495 DIST_ed_chrg_ACCTD_AMT,
496 DIST_ed_frt_AMT ,
497 DIST_ed_frt_ACCTD_AMT,
498 DIST_ed_tax_AMT ,
499 DIST_ed_tax_ACCTD_AMT,
500 --
501 tl_ed_alloc_amt ,
502 tl_ed_alloc_acctd_amt ,
503 tl_ed_chrg_alloc_amt ,
504 tl_ed_chrg_alloc_acctd_amt,
505 tl_ed_frt_alloc_amt ,
506 tl_ed_frt_alloc_acctd_amt,
507 tl_ed_tax_alloc_amt ,
508 tl_ed_tax_alloc_acctd_amt,
509 -- UNED
510 DIST_uned_AMT ,
511 DIST_uned_ACCTD_AMT ,
512 DIST_uned_chrg_AMT ,
513 DIST_uned_chrg_ACCTD_AMT ,
514 DIST_uned_frt_AMT ,
515 DIST_uned_frt_ACCTD_AMT ,
516 DIST_uned_tax_AMT ,
517 DIST_uned_tax_ACCTD_AMT ,
518 --
519 tl_uned_alloc_amt ,
520 tl_uned_alloc_acctd_amt ,
521 tl_uned_chrg_alloc_amt ,
522 tl_uned_chrg_alloc_acctd_amt,
523 tl_uned_frt_alloc_amt ,
524 tl_uned_frt_alloc_acctd_amt,
525 tl_uned_tax_alloc_amt ,
526 tl_uned_tax_alloc_acctd_amt,
527 --
528 source_type ,
529 source_table ,
530 source_id ,
531 line_type,
532 --
533 group_id,
534 source_data_key1 ,
535 source_data_key2 ,
536 source_data_key3 ,
537 source_data_key4 ,
538 source_data_key5 ,
539 gp_level,
540 --
541 set_of_books_id,
542 sob_type,
543 se_gt_id,
544 tax_link_id,
545 tax_inc_flag
546 )
547 SELECT
548 p_gt_id -- GT_ID
549 , ctlgd.amount -- AMT
550 , ctlgd.acctd_amount -- ACCTD_AMT
551 , DECODE(ctl.line_type,'LINE','REV',
552 'TAX','TAX',
553 'FREIGHT','FREIGHT',
554 'CHARGES','CHARGES',
555 'CB','REV') -- ACCOUNT_CLASS
556 , DECODE(ctlgd.collected_tax_ccid,
557 NULL, ctlgd.code_combination_id,
558 0 , ctlgd.code_combination_id,
559 ctlgd.collected_tax_ccid) -- CCID_SECONDARY
560 , ctlgd.cust_trx_line_gl_dist_id -- REF_CUST_TRX_LINE_GL_DIST_ID
561 , DECODE(ctl.line_type,'LINE',-6,
562 'TAX',-8,
563 'FREIGHT',-9,
564 'CHARGES',-7,
565 'CB',-6) --ctl.customer_trx_line_id -- REF_CUSTOMER_TRX_LINE_ID
569 -- ADJ and APP Elmt
566 , trx.customer_trx_id -- REF_CUSTOMER_TRX_ID
567 , trx.invoice_currency_code -- TO_CURRENCY
568 , NULL -- BASE_CURRENCY
570 , DECODE(ctl.line_type,'LINE',ctlgd.amount,0) -- DIST_AMT
571 , DECODE(ctl.line_type,'LINE',ctlgd.acctd_amount,0) -- DIST_ACCTD_AMT
572 , DECODE(ctl.line_type,'CHARGES',ctlgd.amount,0) -- DIST_CHRG_AMT
573 , DECODE(ctl.line_type,'CHARGES',ctlgd.acctd_amount,0) -- DIST_CHRG_ACCTD_AMT
574 , DECODE(ctl.line_type,'FREIGHT',ctlgd.amount,0) -- DIST_FRT_AMT
575 , DECODE(ctl.line_type,'FREIGHT',ctlgd.acctd_amount,0) -- DIST_FRT_ACCTD_AMT
576 , DECODE(ctl.line_type,'TAX',ctlgd.amount,0) -- DIST_TAX_AMT
577 , DECODE(ctl.line_type,'TAX',ctlgd.acctd_amount,0) -- DIST_TAX_ACCTD_AMT
578 -- Buc
579 , 0 -- tl_alloc_amt
580 , 0 -- tl_alloc_acctd_amt
581 , 0 -- tl_chrg_alloc_amt
582 , 0 -- tl_chrg_alloc_acctd_amt
583 , 0 -- tl_frt_alloc_amt
584 , 0 -- tl_frt_alloc_acctd_amt
585 , 0 -- tl_tax_alloc_amt
586 , 0 -- tl_tax_alloc_acctd_amt
587 -- ED Elmt
588 , DECODE(ctl.line_type,'LINE',ctlgd.amount,0) -- DIST_ed_AMT
589 , DECODE(ctl.line_type,'LINE',ctlgd.acctd_amount,0) -- DIST_ed_ACCTD_AMT
590 , DECODE(ctl.line_type,'CHARGES',ctlgd.amount,0) -- DIST_ed_chrg_AMT
591 , DECODE(ctl.line_type,'CHARGES',ctlgd.acctd_amount,0) -- DIST_ed_chrg_ACCTD_AMT
592 , DECODE(ctl.line_type,'FREIGHT',ctlgd.amount,0) -- DIST_ed_frt_AMT
593 , DECODE(ctl.line_type,'FREIGHT',ctlgd.acctd_amount,0) -- DIST_ed_frt_ACCTD_AMT
594 , DECODE(ctl.line_type,'TAX',ctlgd.amount,0) -- DIST_ed_tax_AMT
595 , DECODE(ctl.line_type,'TAX',ctlgd.acctd_amount,0) -- DIST_ed_tax_ACCTD_AMT
596 --
597 , 0 -- tl_ed_alloc_amt
598 , 0 -- tl_ed_alloc_acctd_amt
599 , 0 -- tl_ed_chrg_alloc_amt
600 , 0 -- tl_ed_chrg_alloc_acctd_amt
601 , 0 -- tl_ed_frt_alloc_amt
602 , 0 -- tl_ed_frt_alloc_acctd_amt
603 , 0 -- tl_ed_tax_alloc_amt
604 , 0 -- tl_ed_tax_alloc_acctd_amt
605 -- UNED
606 , DECODE(ctl.line_type,'LINE',ctlgd.amount,0) -- DIST_uned_AMT
607 , DECODE(ctl.line_type,'LINE',ctlgd.acctd_amount,0) -- DIST_uned_ACCTD_AMT
608 , DECODE(ctl.line_type,'CHARGES',ctlgd.amount,0) -- DIST_uned_chrg_AMT
609 , DECODE(ctl.line_type,'CHARGES',ctlgd.acctd_amount,0) -- DIST_uned_chrg_ACCTD_AMT
610 , DECODE(ctl.line_type,'FREIGHT',ctlgd.amount,0) -- DIST_uned_frt_AMT
611 , DECODE(ctl.line_type,'FREIGHT',ctlgd.acctd_amount,0) -- DIST_uned_frt_ACCTD_AMT
612 , DECODE(ctl.line_type,'TAX',ctlgd.amount,0) -- DIST_uned_tax_AMT
613 , DECODE(ctl.line_type,'TAX',ctlgd.acctd_amount,0) -- DIST_uned_tax_ACCTD_AMT
614 --
615 , 0 -- tl_uned_alloc_amt
616 , 0 -- tl_uned_alloc_acctd_amt
617 , 0 -- tl_uned_chrg_alloc_amt
618 , 0 -- tl_uned_chrg_alloc_acctd_amt
619 , 0 -- tl_uned_frt_alloc_amt
620 , 0 -- tl_uned_frt_alloc_acctd_amt
621 , 0 -- tl_uned_tax_alloc_amt
622 , 0 -- tl_uned_tax_alloc_acctd_amt
623 --
624 , NULL -- source_type
625 , 'CTLGD' -- source_table
626 , NULL -- source_id
627 , ctl.line_type -- line_type
628 --
629 , NULL -- group_id
630 , '00' -- source_data_key1
631 , '00' -- source_data_key2
632 , '00' -- source_data_key3
633 , '00' -- source_data_key4
634 , '00' -- source_data_key5
635 , 'D' -- gp_level
636 --
637 , trx.set_of_books_id -- set_of_books_id
638 , 'P' -- sob_type
639 , USERENV('SESSIONID') -- se_gt_id
640 , NULL -- tax_link_id
641 , NULL -- tax_inc_flag
642 FROM xla_events_gt evt,
643 ar_receivable_applications_all app,
644 ar_system_parameters_all ars,
645 ra_customer_trx_all trx,
646 ra_customer_trx_lines_all ctl,
647 ra_cust_trx_line_gl_dist_all ctlgd
648 WHERE evt.event_type_code IN ('RECP_CREATE' ,'RECP_UPDATE' ,
649 'RECP_RATE_ADJUST' ,'RECP_REVERSE' ,
650 'CM_CREATE' ,'CM_UPDATE' )
651 AND evt.application_id = 222
652 AND evt.event_id = app.event_id
653 AND app.status = 'APP'
654 AND app.upgrade_method IS NULL
655 AND app.org_id = ars.org_id
656 AND ars.accounting_method = 'CASH'
657 AND app.applied_customer_trx_id = trx.customer_trx_id
658 AND trx.customer_trx_id = ctl.customer_trx_id
659 AND ctl.customer_trx_line_id = ctlgd.customer_trx_line_id
660 AND ctl.line_type IN ('LINE','TAX','FREIGHT','CHARGES','CB')
661 AND ctlgd.account_class IN ('REV','SUSPENSE','UNBILL','UNEARN','FREIGHT','TAX')
662 AND ctlgd.account_set_flag = 'N'
663 AND EXISTS (SELECT '1' FROM ar_adjustments_all adj
664 WHERE adj.customer_trx_id = app.applied_customer_trx_id
665 AND adj.upgrade_method = '11I'
666 AND adj.status = 'A'
667 AND adj.postable = 'Y');
668
669 /*
673 inv.set_of_books_id
670 FROM ( -- Applied to transactions
671 SELECT DISTINCT inv.customer_trx_id,
672 inv.invoice_currency_code,
674 FROM xla_events_gt evt,
675 ar_receivable_applications_all app,
676 ra_customer_trx_all inv,
677 ar_system_parameters_all ars
678 WHERE evt.event_type_code
679 IN ( 'RECP_CREATE' ,'RECP_UPDATE' ,
680 'RECP_RATE_ADJUST' ,'RECP_REVERSE' ,
681 'CM_CREATE' ,'CM_UPDATE' )
682 AND evt.event_id = app.event_id
683 AND app.applied_customer_trx_id = inv.customer_trx_id
684 AND app.upgrade_method IS NULL
685 AND ars.org_id = app.org_id
686 AND ars.accounting_method = 'CASH'
687 AND NOT EXISTS ( SELECT '1'
688 FROM psa_trx_types_all psa
689 WHERE inv.cust_trx_type_id = psa.psa_trx_type_id)
690 UNION
691 -- From CM in the case of CM APP
692 SELECT DISTINCT inv.customer_trx_id,
693 inv.invoice_currency_code,
694 inv.set_of_books_id
695 FROM xla_events_gt evt,
696 ar_receivable_applications_all app,
697 ra_customer_trx_all inv,
698 ar_system_parameters_all ars
699 WHERE evt.event_type_code
700 IN ( 'CM_CREATE' ,'CM_UPDATE' )
701 AND evt.event_id = app.event_id
702 AND app.customer_trx_id = inv.customer_trx_id
703 AND app.upgrade_method IS NULL
704 AND ars.org_id = app.org_id
705 AND ars.accounting_method = 'CASH'
706 AND NOT EXISTS ( SELECT '1'
707 FROM psa_trx_types_all psa
708 WHERE inv.cust_trx_type_id = psa.psa_trx_type_id)) trx,
709 ra_customer_trx_lines_all ctl,
710 ra_cust_trx_line_gl_dist_all ctlgd
711 WHERE trx.customer_trx_id = ctl.customer_trx_id
712 AND ctl.customer_trx_line_id = ctlgd.customer_trx_line_id
713 AND ctl.line_type IN ('LINE','TAX','FREIGHT','CHARGES','CB')
714 AND ctlgd.account_class IN ('REV','SUSPENSE','UNBILL','UNEARN','FREIGHT','TAX')
715 AND ctlgd.account_set_flag = 'N';
716 */
717 END IF;
718 log('get_direct_inv_dist -');
719 EXCEPTION
720 WHEN OTHERS THEN
721 log('EXCEPTION OTHERS: get_direct_inv_dist :'||SQLERRM);
722 END get_direct_inv_dist;
723
724
725
726
727
728 PROCEDURE get_direct_mf_inv_dist
729 (p_mode IN VARCHAR2 DEFAULT 'BATCH',
730 p_gt_id IN NUMBER DEFAULT NULL)
731 IS
732 BEGIN
733 log('get_direct_mf_inv_dist +');
734 log(' p_gt_id :'||p_gt_id);
735
736 IF p_mode = 'BATCH' THEN
737
738 INSERT INTO RA_AR_GT
739 ( GT_ID ,
740 AMT ,
741 ACCTD_AMT ,
742 ACCOUNT_CLASS ,
743 CCID_SECONDARY ,
744 REF_CUST_TRX_LINE_GL_DIST_ID,
745 REF_CUSTOMER_TRX_LINE_ID ,
746 REF_CUSTOMER_TRX_ID ,
747 TO_CURRENCY ,
748 BASE_CURRENCY ,
749 -- ADJ and APP Elmt
750 DIST_AMT ,
751 DIST_ACCTD_AMT ,
752 DIST_CHRG_AMT ,
753 DIST_CHRG_ACCTD_AMT ,
754 DIST_FRT_AMT ,
755 DIST_FRT_ACCTD_AMT ,
756 DIST_TAX_AMT ,
757 DIST_TAX_ACCTD_AMT ,
758 -- Buc
759 tl_alloc_amt ,
760 tl_alloc_acctd_amt ,
761 tl_chrg_alloc_amt ,
762 tl_chrg_alloc_acctd_amt,
763 tl_frt_alloc_amt ,
764 tl_frt_alloc_acctd_amt,
765 tl_tax_alloc_amt ,
766 tl_tax_alloc_acctd_amt,
767 -- ED Elmt
768 DIST_ed_AMT,
769 DIST_ed_ACCTD_AMT,
770 DIST_ed_chrg_AMT,
771 DIST_ed_chrg_ACCTD_AMT,
772 DIST_ed_frt_AMT ,
773 DIST_ed_frt_ACCTD_AMT,
774 DIST_ed_tax_AMT ,
775 DIST_ed_tax_ACCTD_AMT,
776 --
777 tl_ed_alloc_amt ,
778 tl_ed_alloc_acctd_amt ,
779 tl_ed_chrg_alloc_amt ,
780 tl_ed_chrg_alloc_acctd_amt,
781 tl_ed_frt_alloc_amt ,
782 tl_ed_frt_alloc_acctd_amt,
783 tl_ed_tax_alloc_amt ,
784 tl_ed_tax_alloc_acctd_amt,
785 -- UNED
786 DIST_uned_AMT ,
787 DIST_uned_ACCTD_AMT ,
788 DIST_uned_chrg_AMT ,
789 DIST_uned_chrg_ACCTD_AMT ,
790 DIST_uned_frt_AMT ,
791 DIST_uned_frt_ACCTD_AMT ,
792 DIST_uned_tax_AMT ,
793 DIST_uned_tax_ACCTD_AMT ,
794 --
795 tl_uned_alloc_amt ,
796 tl_uned_alloc_acctd_amt ,
797 tl_uned_chrg_alloc_amt ,
798 tl_uned_chrg_alloc_acctd_amt,
799 tl_uned_frt_alloc_amt ,
800 tl_uned_frt_alloc_acctd_amt,
801 tl_uned_tax_alloc_amt ,
802 tl_uned_tax_alloc_acctd_amt,
806 source_id ,
803 --
804 source_type ,
805 source_table ,
807 line_type,
808 --
809 group_id,
810 source_data_key1 ,
811 source_data_key2 ,
812 source_data_key3 ,
813 source_data_key4 ,
814 source_data_key5 ,
815 gp_level,
816 --
817 set_of_books_id,
818 sob_type,
819 se_gt_id,
820 tax_link_id,
821 tax_inc_flag
822 )
823 SELECT
824 p_gt_id -- GT_ID
825 , ctlgd.amount -- AMT
826 , ctlgd.acctd_amount -- ACCTD_AMT
827 , DECODE(ctl.line_type,'LINE','REV',
828 'TAX','TAX',
829 'FREIGHT','FREIGHT',
830 'CHARGES','CHARGES',
831 'CB','REV') -- ACCOUNT_CLASS
832 , DECODE(ctlgd.collected_tax_ccid,
833 NULL, ctlgd.code_combination_id,
834 0 , ctlgd.code_combination_id,
835 ctlgd.collected_tax_ccid) -- CCID_SECONDARY
836 , ctlgd.cust_trx_line_gl_dist_id -- REF_CUST_TRX_LINE_GL_DIST_ID
837 , ctl.customer_trx_line_id -- REF_CUSTOMER_TRX_LINE_ID
838 , trx.customer_trx_id -- REF_CUSTOMER_TRX_ID
839 , trx.invoice_currency_code -- TO_CURRENCY
840 , NULL -- BASE_CURRENCY
841 -- ADJ and APP Elmt
842 , DECODE(ctl.line_type,'LINE',ctlgd.amount,0) -- DIST_AMT
843 , DECODE(ctl.line_type,'LINE',ctlgd.acctd_amount,0) -- DIST_ACCTD_AMT
844 , DECODE(ctl.line_type,'CHARGES',ctlgd.amount,0) -- DIST_CHRG_AMT
845 , DECODE(ctl.line_type,'CHARGES',ctlgd.acctd_amount,0) -- DIST_CHRG_ACCTD_AMT
846 , DECODE(ctl.line_type,'FREIGHT',ctlgd.amount,0) -- DIST_FRT_AMT
847 , DECODE(ctl.line_type,'FREIGHT',ctlgd.acctd_amount,0) -- DIST_FRT_ACCTD_AMT
848 , DECODE(ctl.line_type,'TAX',ctlgd.amount,0) -- DIST_TAX_AMT
849 , DECODE(ctl.line_type,'TAX',ctlgd.acctd_amount,0) -- DIST_TAX_ACCTD_AMT
850 -- Buc
851 , 0 -- tl_alloc_amt
852 , 0 -- tl_alloc_acctd_amt
853 , 0 -- tl_chrg_alloc_amt
854 , 0 -- tl_chrg_alloc_acctd_amt
855 , 0 -- tl_frt_alloc_amt
856 , 0 -- tl_frt_alloc_acctd_amt
857 , 0 -- tl_tax_alloc_amt
858 , 0 -- tl_tax_alloc_acctd_amt
859 -- ED Elmt
860 , DECODE(ctl.line_type,'LINE',ctlgd.amount,0) -- DIST_ed_AMT
861 , DECODE(ctl.line_type,'LINE',ctlgd.acctd_amount,0) -- DIST_ed_ACCTD_AMT
862 , DECODE(ctl.line_type,'CHARGES',ctlgd.amount,0) -- DIST_ed_chrg_AMT
863 , DECODE(ctl.line_type,'CHARGES',ctlgd.acctd_amount,0) -- DIST_ed_chrg_ACCTD_AMT
864 , DECODE(ctl.line_type,'FREIGHT',ctlgd.amount,0) -- DIST_ed_frt_AMT
865 , DECODE(ctl.line_type,'FREIGHT',ctlgd.acctd_amount,0) -- DIST_ed_frt_ACCTD_AMT
866 , DECODE(ctl.line_type,'TAX',ctlgd.amount,0) -- DIST_ed_tax_AMT
867 , DECODE(ctl.line_type,'TAX',ctlgd.acctd_amount,0) -- DIST_ed_tax_ACCTD_AMT
868 --
869 , 0 -- tl_ed_alloc_amt
870 , 0 -- tl_ed_alloc_acctd_amt
871 , 0 -- tl_ed_chrg_alloc_amt
872 , 0 -- tl_ed_chrg_alloc_acctd_amt
873 , 0 -- tl_ed_frt_alloc_amt
874 , 0 -- tl_ed_frt_alloc_acctd_amt
875 , 0 -- tl_ed_tax_alloc_amt
876 , 0 -- tl_ed_tax_alloc_acctd_amt
877 -- UNED
878 , DECODE(ctl.line_type,'LINE',ctlgd.amount,0) -- DIST_uned_AMT
879 , DECODE(ctl.line_type,'LINE',ctlgd.acctd_amount,0) -- DIST_uned_ACCTD_AMT
880 , DECODE(ctl.line_type,'CHARGES',ctlgd.amount,0) -- DIST_uned_chrg_AMT
881 , DECODE(ctl.line_type,'CHARGES',ctlgd.acctd_amount,0) -- DIST_uned_chrg_ACCTD_AMT
882 , DECODE(ctl.line_type,'FREIGHT',ctlgd.amount,0) -- DIST_uned_frt_AMT
883 , DECODE(ctl.line_type,'FREIGHT',ctlgd.acctd_amount,0) -- DIST_uned_frt_ACCTD_AMT
884 , DECODE(ctl.line_type,'TAX',ctlgd.amount,0) -- DIST_uned_tax_AMT
885 , DECODE(ctl.line_type,'TAX',ctlgd.acctd_amount,0) -- DIST_uned_tax_ACCTD_AMT
886 --
887 , 0 -- tl_uned_alloc_amt
888 , 0 -- tl_uned_alloc_acctd_amt
889 , 0 -- tl_uned_chrg_alloc_amt
890 , 0 -- tl_uned_chrg_alloc_acctd_amt
891 , 0 -- tl_uned_frt_alloc_amt
892 , 0 -- tl_uned_frt_alloc_acctd_amt
893 , 0 -- tl_uned_tax_alloc_amt
894 , 0 -- tl_uned_tax_alloc_acctd_amt
895 --
896 , NULL -- source_type
897 , 'CTLGD' -- source_table
898 , NULL -- source_id
899 , ctl.line_type -- line_type
900 --
901 , NULL -- group_id
902 , '00' -- source_data_key1
903 , '00' -- source_data_key2
904 , '00' -- source_data_key3
905 , '00' -- source_data_key4
906 , '00' -- source_data_key5
907 , 'D' -- gp_level
908 --
909 , trx.set_of_books_id -- set_of_books_id
910 , 'P' -- sob_type
911 , USERENV('SESSIONID') -- se_gt_id
912 , NULL -- tax_link_id
913 , NULL -- tax_inc_flag
914 FROM ( -- Applied to transactions
915 SELECT DISTINCT inv.customer_trx_id,
916 inv.invoice_currency_code,
917 inv.set_of_books_id
918 FROM xla_events_gt evt,
919 ar_receivable_applications_all app,
923 IN ( 'RECP_CREATE' ,'RECP_UPDATE' ,
920 ra_customer_trx_all inv,
921 psa_trx_types_all psa
922 WHERE evt.event_type_code
924 'RECP_RATE_ADJUST' ,'RECP_REVERSE' ,
925 'CM_CREATE' ,'CM_UPDATE' )
926 AND evt.event_id = app.event_id
927 AND app.applied_customer_trx_id = inv.customer_trx_id
928 AND inv.cust_trx_type_id = psa.psa_trx_type_id
929 AND app.upgrade_method IS NULL
930 UNION
931 -- From CM in the case of CM APP
932 SELECT DISTINCT inv.customer_trx_id,
933 inv.invoice_currency_code,
934 inv.set_of_books_id
935 FROM xla_events_gt evt,
936 ar_receivable_applications_all app,
937 ra_customer_trx_all inv,
938 psa_trx_types_all psa
939 WHERE evt.event_type_code
940 IN ( 'CM_CREATE' ,'CM_UPDATE' )
941 AND evt.event_id = app.event_id
942 AND app.customer_trx_id = inv.customer_trx_id
943 AND inv.cust_trx_type_id = psa.psa_trx_type_id
944 AND app.upgrade_method IS NULL) trx,
945 ra_customer_trx_lines_all ctl,
946 ra_cust_trx_line_gl_dist_all ctlgd
947 WHERE trx.customer_trx_id = ctl.customer_trx_id
948 AND ctl.customer_trx_line_id = ctlgd.customer_trx_line_id
949 AND ctl.line_type IN ('LINE','TAX','FREIGHT','CHARGES','CB')
950 AND ctlgd.account_class IN ('REV','SUSPENSE','UNBILL','UNEARN','FREIGHT','TAX')
951 AND ctlgd.account_set_flag = 'N';
952 END IF;
953 log('get_direct_mf_inv_dist -');
954 EXCEPTION
955 WHEN OTHERS THEN
956 log('EXCEPTION OTHERS: get_direct_mf_inv_dist :'||SQLERRM);
957 END get_direct_mf_inv_dist;
958
959
960
961
962
963
964 PROCEDURE get_direct_adj_dist
965 (p_mode IN VARCHAR2,
966 p_trx_id IN NUMBER DEFAULT NULL,
967 p_gt_id IN NUMBER DEFAULT NULL)
968 IS
969 BEGIN
970 log('get_direct_adj_dist +');
971 log(' p_mode : '|| p_mode);
972 log(' p_trx_id : '|| p_trx_id);
973 log(' p_gt_id : '|| p_gt_id);
974 IF p_mode = 'OLTP' THEN
975 INSERT INTO RA_AR_GT
976 ( GT_ID ,
977 AMT ,
978 ACCTD_AMT ,
979 ACCOUNT_CLASS ,
980 CCID_SECONDARY ,
981 REF_CUST_TRX_LINE_GL_DIST_ID,
982 REF_CUSTOMER_TRX_LINE_ID ,
983 REF_CUSTOMER_TRX_ID ,
984 TO_CURRENCY ,
985 BASE_CURRENCY ,
986 -- ADJ and APP Elmt
987 DIST_AMT ,
988 DIST_ACCTD_AMT ,
989 DIST_CHRG_AMT ,
990 DIST_CHRG_ACCTD_AMT ,
991 DIST_FRT_AMT ,
992 DIST_FRT_ACCTD_AMT ,
993 DIST_TAX_AMT ,
994 DIST_TAX_ACCTD_AMT ,
995 -- Buc
996 tl_alloc_amt ,
997 tl_alloc_acctd_amt ,
998 tl_chrg_alloc_amt ,
999 tl_chrg_alloc_acctd_amt,
1000 tl_frt_alloc_amt ,
1001 tl_frt_alloc_acctd_amt,
1002 tl_tax_alloc_amt ,
1003 tl_tax_alloc_acctd_amt,
1004 -- ED Elmt
1005 DIST_ed_AMT,
1006 DIST_ed_ACCTD_AMT,
1007 DIST_ed_chrg_AMT,
1008 DIST_ed_chrg_ACCTD_AMT,
1009 DIST_ed_frt_AMT ,
1010 DIST_ed_frt_ACCTD_AMT,
1011 DIST_ed_tax_AMT ,
1012 DIST_ed_tax_ACCTD_AMT,
1013 --
1014 tl_ed_alloc_amt ,
1015 tl_ed_alloc_acctd_amt ,
1016 tl_ed_chrg_alloc_amt ,
1017 tl_ed_chrg_alloc_acctd_amt,
1018 tl_ed_frt_alloc_amt ,
1019 tl_ed_frt_alloc_acctd_amt,
1020 tl_ed_tax_alloc_amt ,
1021 tl_ed_tax_alloc_acctd_amt,
1022 -- UNED
1023 DIST_uned_AMT ,
1024 DIST_uned_ACCTD_AMT ,
1025 DIST_uned_chrg_AMT ,
1026 DIST_uned_chrg_ACCTD_AMT ,
1027 DIST_uned_frt_AMT ,
1028 DIST_uned_frt_ACCTD_AMT ,
1029 DIST_uned_tax_AMT ,
1030 DIST_uned_tax_ACCTD_AMT ,
1031 --
1032 tl_uned_alloc_amt ,
1033 tl_uned_alloc_acctd_amt ,
1034 tl_uned_chrg_alloc_amt ,
1035 tl_uned_chrg_alloc_acctd_amt,
1036 tl_uned_frt_alloc_amt ,
1037 tl_uned_frt_alloc_acctd_amt,
1038 tl_uned_tax_alloc_amt ,
1039 tl_uned_tax_alloc_acctd_amt,
1040 --
1041 source_type ,
1042 source_table ,
1043 source_id ,
1044 line_type,
1045 --
1046 group_id,
1047 source_data_key1 ,
1048 source_data_key2 ,
1049 source_data_key3 ,
1050 source_data_key4 ,
1051 source_data_key5 ,
1052 gp_level,
1053 --
1054 set_of_books_id,
1055 sob_type,
1056 se_gt_id,
1057 --{Taxable Amount
1058 tax_link_id,
1062 )
1059 tax_inc_flag,
1060 --}
1061 ref_line_id
1063 SELECT
1064 p_gt_id -- GT_ID
1065 , NVL(ard.amount_cr,0)
1066 - NVL(ard.amount_dr,0) -- AMT
1067 , NVL(ard.acctd_amount_cr,0)
1068 - NVL(ard.acctd_amount_dr,0) -- ACCTD_AMT
1069 , DECODE(adj.type,
1070 'LINE',DECODE(ard.source_type,'ADJ','REV',
1071 'TAX','TAX',
1072 'DEFERRED_TAX','TAX',
1073 'ADJ_NON_REC_TAX','TAX','REV'),
1074 'TAX' ,DECODE(ard.source_type,'TAX','TAX',
1075 'ADJ','TAX',
1076 'DEFERRED_TAX','TAX',
1077 'ADJ_NON_REC_TAX','TAX','TAX'),
1078 'FREIGHT' ,DECODE(ard.source_type,'ADJ',
1079 'FREIGHT','FREIGHT'),
1080 'CHARGES',DECODE(ard.source_type,'FINCHRG',
1081 'CHARGES','CHARGES'),
1082 'REV') -- ACCOUNT_CLASS
1083 , ard.code_combination_id -- CCID_SECONDARY
1084 , DECODE(adj.type,
1085 'LINE',DECODE(ard.source_type,'ADJ',-6,
1086 'TAX',-8,
1087 'DEFERRED_TAX',-8,
1088 'ADJ_NON_REC_TAX',-8,-6),
1089 'TAX' ,DECODE(ard.source_type,'TAX',-8,
1090 'ADJ',-8,
1091 'DEFERRED_TAX',-8,
1092 'ADJ_NON_REC_TAX',-8,-8),
1093 'FREIGHT' ,DECODE(ard.source_type,'ADJ',
1094 -9,-9),
1095 'CHARGES',DECODE(ard.source_type,'FINCHRG',
1096 -7,-7),
1097 -6) -- REF_CUST_TRX_LINE_GL_DIST_ID
1098 , DECODE(adj.type,
1099 'LINE',DECODE(ard.source_type,'ADJ',-6,
1100 'TAX',-8,
1101 'DEFERRED_TAX',-8,
1102 'ADJ_NON_REC_TAX',-8,-6),
1103 'TAX' ,DECODE(ard.source_type,'TAX',-8,
1104 'ADJ',-8,
1105 'DEFERRED_TAX',-8,
1106 'ADJ_NON_REC_TAX',-8,-8),
1107 'FREIGHT' ,DECODE(ard.source_type,'ADJ',
1108 -9,-9),
1109 'CHARGES',DECODE(ard.source_type,'FINCHRG',
1110 -7,-7),
1111 -6) -- REF_CUSTOMER_TRX_LINE_ID
1112 , adj.customer_trx_id -- REF_CUSTOMER_TRX_ID
1113 , trx.invoice_currency_code -- TO_CURRENCY
1114 , NULL -- BASE_CURRENCY
1115 -- ADJ and APP Elmt
1116 , DECODE(adj.type,'LINE', DECODE(ard.source_type,
1117 'ADJ',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
1118 0) -- DIST_AMT
1119 , DECODE(adj.type,'LINE', DECODE(ard.source_type,
1120 'ADJ',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
1121 0) -- DIST_ACCTD_AMT
1122 --
1123 , DECODE(adj.type,'CHARGES',DECODE(ard.source_type,
1124 'FINCHRG',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
1125 0) -- DIST_CHRG_AMT
1126 , DECODE(adj.type,'CHARGES',DECODE(ard.source_type,
1127 'FINCHRG',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
1128 0) -- DIST_CHRG_ACCTD_AMT
1129 , DECODE(adj.type,'FREIGHT',DECODE(ard.source_type,
1130 'ADJ',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
1131 0) -- DIST_FRT_AMT
1132 , DECODE(adj.type,'FREIGHT',DECODE(ard.source_type,
1133 'ADJ',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
1134 0) -- DIST_FRT_ACCTD_AMT
1135 , DECODE(adj.type,'TAX', DECODE(ard.source_type,
1136 'TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
1137 'DEFERRED_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
1138 'ADJ',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
1139 'ADJ_NON_REC_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
1140 'LINE', DECODE(ard.source_type,
1141 'TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
1142 'ADJ_NON_REC_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
1143 'DEFERRED_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
1144 0) -- DIST_TAX_AMT
1145 , DECODE(adj.type,'TAX', DECODE(ard.source_type,
1146 'TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
1147 'DEFERRED_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
1148 'ADJ',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
1149 'ADJ_NON_REC_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
1150 'LINE', DECODE(ard.source_type,
1151 'TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
1155 -- Buc
1152 'ADJ_NON_REC_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
1153 'DEFERRED_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
1154 0) -- DIST_TAX_ACCTD_AMT
1156 , 0 -- tl_alloc_amt
1157 , 0 -- tl_alloc_acctd_amt
1158 , 0 -- tl_chrg_alloc_amt
1159 , 0 -- tl_chrg_alloc_acctd_amt
1160 , 0 -- tl_frt_alloc_amt
1161 , 0 -- tl_frt_alloc_acctd_amt
1162 , 0 -- tl_tax_alloc_amt
1163 , 0 -- tl_tax_alloc_acctd_amt
1164 -- ED Elmt
1165 , 0 -- DIST_ed_AMT
1166 , 0 -- DIST_ed_ACCTD_AMT
1167 , 0 -- DIST_ed_chrg_AMT
1168 , 0 -- DIST_ed_chrg_ACCTD_AMT
1169 , 0 -- DIST_ed_frt_AMT
1170 , 0 -- DIST_ed_frt_ACCTD_AMT
1171 , 0 -- DIST_ed_tax_AMT
1172 , 0 -- DIST_ed_tax_ACCTD_AMT
1173 --
1174 , 0 -- tl_ed_alloc_amt
1175 , 0 -- tl_ed_alloc_acctd_amt
1176 , 0 -- tl_ed_chrg_alloc_amt
1177 , 0 -- tl_ed_chrg_alloc_acctd_amt
1178 , 0 -- tl_ed_frt_alloc_amt
1179 , 0 -- tl_ed_frt_alloc_acctd_amt
1180 , 0 -- tl_ed_tax_alloc_amt
1181 , 0 -- tl_ed_tax_alloc_acctd_amt
1182 -- UNED
1183 , 0 -- DIST_uned_AMT
1184 , 0 -- DIST_uned_ACCTD_AMT
1185 , 0 -- DIST_uned_chrg_AMT
1186 , 0 -- DIST_uned_chrg_ACCTD_AMT
1187 , 0 -- DIST_uned_frt_AMT
1188 , 0 -- DIST_uned_frt_ACCTD_AMT
1189 , 0 -- DIST_uned_tax_AMT
1190 , 0 -- DIST_uned_tax_ACCTD_AMT
1191 --
1192 , 0 -- tl_uned_alloc_amt
1193 , 0 -- tl_uned_alloc_acctd_amt
1194 , 0 -- tl_uned_chrg_alloc_amt
1195 , 0 -- tl_uned_chrg_alloc_acctd_amt
1196 , 0 -- tl_uned_frt_alloc_amt
1197 , 0 -- tl_uned_frt_alloc_acctd_amt
1198 , 0 -- tl_uned_tax_alloc_amt
1199 , 0 -- tl_uned_tax_alloc_acctd_amt
1200 --
1201 , ard.source_type -- source_type
1202 , ard.source_table -- source_table
1203 , ard.source_id -- source_id
1204 , DECODE(adj.type,
1205 'LINE',DECODE(ard.source_type,'ADJ','LINE',
1206 'TAX','TAX',
1207 'DEFERRED_TAX','TAX','LINE'),
1208 'TAX','TAX',
1209 'CHARGES','CHARGES',
1210 'FREIGHT','FREIGHT', 'LINE') -- line_type
1211 --
1212 , NULL -- group_id
1213 , '00' -- source_data_key1
1214 , '00' -- source_data_key2
1215 , '00' -- source_data_key3
1216 , '00' -- source_data_key4
1217 , '00' -- source_data_key5
1218 , 'D' -- gp_level
1219 --
1220 , adj.set_of_books_id -- set_of_books_id
1221 , 'P' -- sob_type
1222 , USERENV('SESSIONID') -- se_gt_id
1223 , NULL -- tax_link_id
1224 , NULL -- tax_inc_flag
1225 , ard.line_id -- ref_line_id
1226 FROM ar_adjustments adj,
1227 ar_distributions ard,
1228 (SELECT MAX(ref_customer_trx_id) ref_customer_trx_id,
1229 MAX(to_currency) invoice_currency_code
1230 FROM ra_ar_gt
1231 WHERE gt_id = p_gt_id
1232 GROUP BY ref_customer_trx_id, to_currency) trx
1233 WHERE adj.customer_trx_id= p_trx_id
1234 AND adj.customer_trx_id= trx.ref_customer_trx_id
1235 AND adj.status = 'A'
1236 AND adj.postable = 'Y'
1237 AND ard.source_table = 'ADJ'
1238 AND ard.source_id = adj.adjustment_id
1239 AND adj.type IN ('LINE','CHARGES','TAX','FREIGHT')
1240 AND DECODE(
1241 adj.type, 'LINE',DECODE(ard.source_type,
1242 'ADJ','Y',
1243 'TAX','Y',
1244 'DEFERRED_TAX','Y',
1245 'ADJ_NON_REC_TAX','Y','N'),
1246 'CHARGES',DECODE(ard.source_type,
1247 'FINCHRG','Y','N'),
1248 'TAX',DECODE(ard.source_type,
1249 'TAX','Y',
1250 'DEFERRED_TAX','Y',
1251 'ADJ','Y',
1252 'ADJ_NON_REC_TAX','Y','N'),
1253 'FREIGHT',DECODE(ard.source_type,
1254 'ADJ','Y','N'),
1255 'N') = 'Y';
1256
1257 ELSIF p_mode = 'BATCH' THEN
1258
1259 INSERT INTO RA_AR_GT
1260 ( GT_ID ,
1261 AMT ,
1262 ACCTD_AMT ,
1263 ACCOUNT_CLASS ,
1264 CCID_SECONDARY ,
1265 REF_CUST_TRX_LINE_GL_DIST_ID,
1266 REF_CUSTOMER_TRX_LINE_ID ,
1267 REF_CUSTOMER_TRX_ID ,
1268 TO_CURRENCY ,
1269 BASE_CURRENCY ,
1270 -- ADJ and APP Elmt
1271 DIST_AMT ,
1272 DIST_ACCTD_AMT ,
1273 DIST_CHRG_AMT ,
1274 DIST_CHRG_ACCTD_AMT ,
1275 DIST_FRT_AMT ,
1276 DIST_FRT_ACCTD_AMT ,
1277 DIST_TAX_AMT ,
1278 DIST_TAX_ACCTD_AMT ,
1279 -- Buc
1280 tl_alloc_amt ,
1281 tl_alloc_acctd_amt ,
1285 tl_frt_alloc_acctd_amt,
1282 tl_chrg_alloc_amt ,
1283 tl_chrg_alloc_acctd_amt,
1284 tl_frt_alloc_amt ,
1286 tl_tax_alloc_amt ,
1287 tl_tax_alloc_acctd_amt,
1288 -- ED Elmt
1289 DIST_ed_AMT,
1290 DIST_ed_ACCTD_AMT,
1291 DIST_ed_chrg_AMT,
1292 DIST_ed_chrg_ACCTD_AMT,
1293 DIST_ed_frt_AMT ,
1294 DIST_ed_frt_ACCTD_AMT,
1295 DIST_ed_tax_AMT ,
1296 DIST_ed_tax_ACCTD_AMT,
1297 --
1298 tl_ed_alloc_amt ,
1299 tl_ed_alloc_acctd_amt ,
1300 tl_ed_chrg_alloc_amt ,
1301 tl_ed_chrg_alloc_acctd_amt,
1302 tl_ed_frt_alloc_amt ,
1303 tl_ed_frt_alloc_acctd_amt,
1304 tl_ed_tax_alloc_amt ,
1305 tl_ed_tax_alloc_acctd_amt,
1306 -- UNED
1307 DIST_uned_AMT ,
1308 DIST_uned_ACCTD_AMT ,
1309 DIST_uned_chrg_AMT ,
1310 DIST_uned_chrg_ACCTD_AMT ,
1311 DIST_uned_frt_AMT ,
1312 DIST_uned_frt_ACCTD_AMT ,
1313 DIST_uned_tax_AMT ,
1314 DIST_uned_tax_ACCTD_AMT ,
1315 --
1316 tl_uned_alloc_amt ,
1317 tl_uned_alloc_acctd_amt ,
1318 tl_uned_chrg_alloc_amt ,
1319 tl_uned_chrg_alloc_acctd_amt,
1320 tl_uned_frt_alloc_amt ,
1321 tl_uned_frt_alloc_acctd_amt,
1322 tl_uned_tax_alloc_amt ,
1323 tl_uned_tax_alloc_acctd_amt,
1324 --
1325 source_type ,
1326 source_table ,
1327 source_id ,
1328 line_type,
1329 --
1330 group_id,
1331 source_data_key1 ,
1332 source_data_key2 ,
1333 source_data_key3 ,
1334 source_data_key4 ,
1335 source_data_key5 ,
1336 gp_level,
1337 --
1338 set_of_books_id,
1339 sob_type,
1340 se_gt_id,
1341 --{Taxable Amount
1342 tax_link_id,
1343 tax_inc_flag,
1344 --}
1345 ref_line_id
1346 )
1347 SELECT
1348 p_gt_id -- GT_ID
1349 , NVL(ard.amount_cr,0)
1350 - NVL(ard.amount_dr,0) -- AMT
1351 , NVL(ard.acctd_amount_cr,0)
1352 - NVL(ard.acctd_amount_dr,0) -- ACCTD_AMT
1353 , DECODE(adj.type,
1354 'LINE',DECODE(ard.source_type,'ADJ','REV',
1355 'TAX','TAX',
1356 'DEFERRED_TAX','TAX',
1357 'ADJ_NON_REC_TAX','TAX','REV'),
1358 'TAX' ,DECODE(ard.source_type,'TAX','TAX',
1359 'ADJ','TAX',
1360 'DEFERRED_TAX','TAX',
1361 'ADJ_NON_REC_TAX','TAX','TAX'),
1362 'FREIGHT' ,DECODE(ard.source_type,'ADJ',
1363 'FREIGHT','FREIGHT'),
1364 'CHARGES',DECODE(ard.source_type,'FINCHRG',
1365 'CHARGES','CHARGES'),
1366 'REV') -- ACCOUNT_CLASS
1367 , ard.code_combination_id -- CCID_SECONDARY
1368 , DECODE(adj.type,
1369 'LINE',DECODE(ard.source_type,'ADJ',-6,
1370 'TAX',-8,
1371 'DEFERRED_TAX',-8,
1372 'ADJ_NON_REC_TAX',-8,-6),
1373 'TAX' ,DECODE(ard.source_type,'TAX',-8,
1374 'ADJ',-8,
1375 'DEFERRED_TAX',-8,
1376 'ADJ_NON_REC_TAX',-8,-8),
1377 'FREIGHT' ,DECODE(ard.source_type,'ADJ',
1378 -9,-9),
1379 'CHARGES',DECODE(ard.source_type,'FINCHRG',
1380 -7,-7),
1381 -6) -- REF_CUST_TRX_LINE_GL_DIST_ID
1382 , DECODE(adj.type,
1383 'LINE',DECODE(ard.source_type,'ADJ',-6,
1384 'TAX',-8,
1385 'DEFERRED_TAX',-8,
1386 'ADJ_NON_REC_TAX',-8,-6),
1387 'TAX' ,DECODE(ard.source_type,'TAX',-8,
1388 'ADJ',-8,
1389 'DEFERRED_TAX',-8,
1390 'ADJ_NON_REC_TAX',-8,-8),
1391 'FREIGHT' ,DECODE(ard.source_type,'ADJ',
1392 -9,-9),
1393 'CHARGES',DECODE(ard.source_type,'FINCHRG',
1394 -7,-7),
1395 -6) -- REF_CUSTOMER_TRX_LINE_ID
1396 , adj.customer_trx_id -- REF_CUSTOMER_TRX_ID
1397 , trx.invoice_currency_code -- TO_CURRENCY
1398 , NULL -- BASE_CURRENCY
1399 -- ADJ and APP Elmt
1400 , DECODE(adj.type,'LINE', DECODE(ard.source_type,
1401 'ADJ',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
1402 0) -- DIST_AMT
1403 , DECODE(adj.type,'LINE', DECODE(ard.source_type,
1404 'ADJ',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
1405 0) -- DIST_ACCTD_AMT
1406 --
1407 , DECODE(adj.type,'CHARGES',DECODE(ard.source_type,
1408 'FINCHRG',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
1409 'ADJ', (NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
1413 'ADJ',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
1410 0) -- DIST_CHRG_AMT
1411 , DECODE(adj.type,'CHARGES',DECODE(ard.source_type,
1412 'FINCHRG',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
1414 0) -- DIST_CHRG_ACCTD_AMT
1415 , DECODE(adj.type,'FREIGHT',DECODE(ard.source_type,
1416 'ADJ',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
1417 0) -- DIST_FRT_AMT
1418 , DECODE(adj.type,'FREIGHT',DECODE(ard.source_type,
1419 'ADJ',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
1420 0) -- DIST_FRT_ACCTD_AMT
1421 , DECODE(adj.type,'TAX', DECODE(ard.source_type,
1422 'TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
1423 'DEFERRED_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
1424 'ADJ',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
1425 'ADJ_NON_REC_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
1426 'LINE', DECODE(ard.source_type,
1427 'TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
1428 'ADJ_NON_REC_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
1429 'DEFERRED_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
1430 0) -- DIST_TAX_AMT
1431 , DECODE(adj.type,'TAX', DECODE(ard.source_type,
1432 'TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
1433 'DEFERRED_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
1434 'ADJ',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
1435 'ADJ_NON_REC_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
1436 'LINE', DECODE(ard.source_type,
1437 'TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
1438 'ADJ_NON_REC_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
1439 'DEFERRED_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
1440 0) -- DIST_TAX_ACCTD_AMT
1441 -- Buc
1442 , 0 -- tl_alloc_amt
1443 , 0 -- tl_alloc_acctd_amt
1444 , 0 -- tl_chrg_alloc_amt
1445 , 0 -- tl_chrg_alloc_acctd_amt
1446 , 0 -- tl_frt_alloc_amt
1447 , 0 -- tl_frt_alloc_acctd_amt
1448 , 0 -- tl_tax_alloc_amt
1449 , 0 -- tl_tax_alloc_acctd_amt
1450 -- ED Elmt
1451 , 0 -- DIST_ed_AMT
1452 , 0 -- DIST_ed_ACCTD_AMT
1453 , 0 -- DIST_ed_chrg_AMT
1454 , 0 -- DIST_ed_chrg_ACCTD_AMT
1455 , 0 -- DIST_ed_frt_AMT
1456 , 0 -- DIST_ed_frt_ACCTD_AMT
1457 , 0 -- DIST_ed_tax_AMT
1458 , 0 -- DIST_ed_tax_ACCTD_AMT
1459 --
1460 , 0 -- tl_ed_alloc_amt
1461 , 0 -- tl_ed_alloc_acctd_amt
1462 , 0 -- tl_ed_chrg_alloc_amt
1463 , 0 -- tl_ed_chrg_alloc_acctd_amt
1464 , 0 -- tl_ed_frt_alloc_amt
1465 , 0 -- tl_ed_frt_alloc_acctd_amt
1466 , 0 -- tl_ed_tax_alloc_amt
1467 , 0 -- tl_ed_tax_alloc_acctd_amt
1468 -- UNED
1469 , 0 -- DIST_uned_AMT
1470 , 0 -- DIST_uned_ACCTD_AMT
1471 , 0 -- DIST_uned_chrg_AMT
1472 , 0 -- DIST_uned_chrg_ACCTD_AMT
1473 , 0 -- DIST_uned_frt_AMT
1474 , 0 -- DIST_uned_frt_ACCTD_AMT
1475 , 0 -- DIST_uned_tax_AMT
1476 , 0 -- DIST_uned_tax_ACCTD_AMT
1477 --
1478 , 0 -- tl_uned_alloc_amt
1479 , 0 -- tl_uned_alloc_acctd_amt
1480 , 0 -- tl_uned_chrg_alloc_amt
1481 , 0 -- tl_uned_chrg_alloc_acctd_amt
1482 , 0 -- tl_uned_frt_alloc_amt
1483 , 0 -- tl_uned_frt_alloc_acctd_amt
1484 , 0 -- tl_uned_tax_alloc_amt
1485 , 0 -- tl_uned_tax_alloc_acctd_amt
1486 --
1487 , ard.source_type -- source_type
1488 , ard.source_table -- source_table
1489 , ard.source_id -- source_id
1490 , DECODE(adj.type,
1491 'LINE',DECODE(ard.source_type,'ADJ','LINE',
1492 'TAX','TAX',
1493 'DEFERRED_TAX','TAX','LINE'),
1494 'TAX','TAX',
1495 'CHARGES','CHARGES',
1496 'FREIGHT','FREIGHT', 'LINE') -- line_type
1497 --
1498 , NULL -- group_id
1499 , '00' -- source_data_key1
1500 , '00' -- source_data_key2
1501 , '00' -- source_data_key3
1502 , '00' -- source_data_key4
1503 , '00' -- source_data_key5
1504 , 'D' -- gp_level
1505 --
1506 , adj.set_of_books_id -- set_of_books_id
1507 , 'P' -- sob_type
1508 , USERENV('SESSIONID') -- se_gt_id
1509 , NULL -- tax_link_id
1510 , NULL -- tax_inc_flag
1511 , ard.line_id -- ref_line_id
1512 FROM ar_adjustments_all adj,
1516 MAX(to_currency) invoice_currency_code
1513 ar_distributions_all ard,
1514 ar_system_parameters_all ars,
1515 (SELECT MAX(ref_customer_trx_id) ref_customer_trx_id,
1517 FROM ra_ar_gt
1518 GROUP BY ref_customer_trx_id, to_currency) trx
1519 WHERE adj.customer_trx_id = trx.ref_customer_trx_id
1520 AND adj.status = 'A'
1521 AND adj.postable = 'Y'
1522 AND adj.upgrade_method = '11I'
1523 AND adj.adjustment_id = ard.source_id
1524 AND ard.source_table = 'ADJ'
1525 AND adj.type IN ('LINE','CHARGES','TAX','FREIGHT')
1526 AND adj.org_id = ars.org_id
1527 AND ars.accounting_method = 'CASH'
1528 AND DECODE(adj.type, 'LINE',DECODE(ard.source_type,
1529 'ADJ','Y',
1530 'TAX','Y',
1531 'DEFERRED_TAX','Y',
1532 'ADJ_NON_REC_TAX','Y','N'),
1533 'CHARGES',DECODE(ard.source_type,
1534 'FINCHRG','Y',
1535 'ADJ','Y','N'),
1536 'TAX',DECODE(ard.source_type,
1537 'TAX','Y',
1538 'DEFERRED_TAX','Y',
1539 'ADJ','Y',
1540 'ADJ_NON_REC_TAX','Y','N'),
1541 'FREIGHT',DECODE(ard.source_type,
1542 'ADJ','Y','N'),
1543 'N') = 'Y';
1544 END IF;
1545 log('get_direct_adj_dist -');
1546 EXCEPTION
1547 WHEN OTHERS THEN
1548 log('EXCEPTION OTHERS: get_direct_adj_dist :'||SQLERRM);
1549 END get_direct_adj_dist;
1550
1551
1552
1553
1554
1555 --HYU probably no longer usefull
1556 PROCEDURE get_direct_mf_adj_dist
1557 (p_mode IN VARCHAR2,
1558 p_gt_id IN NUMBER DEFAULT NULL)
1559 IS
1560 BEGIN
1561 log('get_direct_mf_adj_dist +');
1562 log(' p_mode : '||p_mode);
1563 log(' p_gt_id : '||p_gt_id);
1564
1565 IF p_mode = 'BATCH' THEN
1566 INSERT INTO RA_AR_GT
1567 ( GT_ID ,
1568 AMT ,
1569 ACCTD_AMT ,
1570 ACCOUNT_CLASS ,
1571 CCID_SECONDARY ,
1572 REF_CUST_TRX_LINE_GL_DIST_ID,
1573 REF_CUSTOMER_TRX_LINE_ID ,
1574 REF_CUSTOMER_TRX_ID ,
1575 TO_CURRENCY ,
1576 BASE_CURRENCY ,
1577 -- ADJ and APP Elmt
1578 DIST_AMT ,
1579 DIST_ACCTD_AMT ,
1580 DIST_CHRG_AMT ,
1581 DIST_CHRG_ACCTD_AMT ,
1582 DIST_FRT_AMT ,
1583 DIST_FRT_ACCTD_AMT ,
1584 DIST_TAX_AMT ,
1585 DIST_TAX_ACCTD_AMT ,
1586 -- Buc
1587 tl_alloc_amt ,
1588 tl_alloc_acctd_amt ,
1589 tl_chrg_alloc_amt ,
1590 tl_chrg_alloc_acctd_amt,
1591 tl_frt_alloc_amt ,
1592 tl_frt_alloc_acctd_amt,
1593 tl_tax_alloc_amt ,
1594 tl_tax_alloc_acctd_amt,
1595 -- ED Elmt
1596 DIST_ed_AMT,
1597 DIST_ed_ACCTD_AMT,
1598 DIST_ed_chrg_AMT,
1599 DIST_ed_chrg_ACCTD_AMT,
1600 DIST_ed_frt_AMT ,
1601 DIST_ed_frt_ACCTD_AMT,
1602 DIST_ed_tax_AMT ,
1603 DIST_ed_tax_ACCTD_AMT,
1604 --
1605 tl_ed_alloc_amt ,
1606 tl_ed_alloc_acctd_amt ,
1607 tl_ed_chrg_alloc_amt ,
1608 tl_ed_chrg_alloc_acctd_amt,
1609 tl_ed_frt_alloc_amt ,
1610 tl_ed_frt_alloc_acctd_amt,
1611 tl_ed_tax_alloc_amt ,
1612 tl_ed_tax_alloc_acctd_amt,
1613 -- UNED
1614 DIST_uned_AMT ,
1615 DIST_uned_ACCTD_AMT ,
1616 DIST_uned_chrg_AMT ,
1617 DIST_uned_chrg_ACCTD_AMT ,
1618 DIST_uned_frt_AMT ,
1619 DIST_uned_frt_ACCTD_AMT ,
1620 DIST_uned_tax_AMT ,
1621 DIST_uned_tax_ACCTD_AMT ,
1622 --
1623 tl_uned_alloc_amt ,
1624 tl_uned_alloc_acctd_amt ,
1625 tl_uned_chrg_alloc_amt ,
1626 tl_uned_chrg_alloc_acctd_amt,
1627 tl_uned_frt_alloc_amt ,
1628 tl_uned_frt_alloc_acctd_amt,
1629 tl_uned_tax_alloc_amt ,
1630 tl_uned_tax_alloc_acctd_amt,
1631 --
1632 source_type ,
1633 source_table ,
1634 source_id ,
1635 line_type,
1636 --
1637 group_id,
1638 source_data_key1 ,
1639 source_data_key2 ,
1640 source_data_key3 ,
1641 source_data_key4 ,
1642 source_data_key5 ,
1643 gp_level,
1644 --
1645 set_of_books_id,
1646 sob_type,
1647 se_gt_id,
1648 --{Taxable Amount
1649 tax_link_id,
1650 tax_inc_flag,
1651 --}
1652 ref_line_id,
1653 ref_mf_dist_flag
1654 )
1655 SELECT
1656 p_gt_id -- GT_ID
1657 , NVL(psad.amount,0) -- AMT
1658 , NVL(psad.amount,0) -- ACCTD_AMT
1659 , ctlgd.account_class -- ACCOUNT_CLASS
1660 , psad.mf_adjustment_ccid -- CCID_SECONDARY
1664 , trx.invoice_currency_code -- TO_CURRENCY
1661 , ctlgd.cust_trx_line_gl_dist_id -- REF_CUST_TRX_LINE_GL_DIST_ID
1662 , ctl.customer_trx_line_id -- REF_CUSTOMER_TRX_LINE_ID
1663 , ctlgd.customer_trx_id -- REF_CUSTOMER_TRX_ID
1665 , NULL -- BASE_CURRENCY
1666 -- ADJ and APP Elmt
1667 , DECODE(ctl.line_type,'LINE', NVL(psad.amount,0),0) -- DIST_AMT
1668 , DECODE(ctl.line_type,'LINE', NVL(psad.amount,0),0) -- DIST_ACCTD_AMT
1669 -- PSA 11i Charges adj are prorated over all distributions
1670 , NVL(psad.amount,0) -- DIST_CHRG_AMT
1671 , NVL(psad.amount,0) -- DIST_CHRG_ACCTD_AMT
1672 , DECODE(ctl.line_type,'FREIGHT',NVL(psad.amount,0),0) -- DIST_FRT_AMT
1673 , DECODE(ctl.line_type,'FREIGHT',NVL(psad.amount,0),0) -- DIST_FRT_ACCTD_AMT
1674 , DECODE(ctl.line_type,'TAX' ,NVL(psad.amount,0),0) -- DIST_TAX_AMT
1675 , DECODE(ctl.line_type,'TAX' ,NVL(psad.amount,0),0) -- DIST_TAX_ACCTD_AMT
1676 -- Buc
1677 , 0 -- tl_alloc_amt
1678 , 0 -- tl_alloc_acctd_amt
1679 , 0 -- tl_chrg_alloc_amt
1680 , 0 -- tl_chrg_alloc_acctd_amt
1681 , 0 -- tl_frt_alloc_amt
1682 , 0 -- tl_frt_alloc_acctd_amt
1683 , 0 -- tl_tax_alloc_amt
1684 , 0 -- tl_tax_alloc_acctd_amt
1685 -- ED Elmt
1686 , 0 -- DIST_ed_AMT
1687 , 0 -- DIST_ed_ACCTD_AMT
1688 , 0 -- DIST_ed_chrg_AMT
1689 , 0 -- DIST_ed_chrg_ACCTD_AMT
1690 , 0 -- DIST_ed_frt_AMT
1691 , 0 -- DIST_ed_frt_ACCTD_AMT
1692 , 0 -- DIST_ed_tax_AMT
1693 , 0 -- DIST_ed_tax_ACCTD_AMT
1694 --
1695 , 0 -- tl_ed_alloc_amt
1696 , 0 -- tl_ed_alloc_acctd_amt
1697 , 0 -- tl_ed_chrg_alloc_amt
1698 , 0 -- tl_ed_chrg_alloc_acctd_amt
1699 , 0 -- tl_ed_frt_alloc_amt
1700 , 0 -- tl_ed_frt_alloc_acctd_amt
1701 , 0 -- tl_ed_tax_alloc_amt
1702 , 0 -- tl_ed_tax_alloc_acctd_amt
1703 -- UNED
1704 , 0 -- DIST_uned_AMT
1705 , 0 -- DIST_uned_ACCTD_AMT
1706 , 0 -- DIST_uned_chrg_AMT
1707 , 0 -- DIST_uned_chrg_ACCTD_AMT
1708 , 0 -- DIST_uned_frt_AMT
1709 , 0 -- DIST_uned_frt_ACCTD_AMT
1710 , 0 -- DIST_uned_tax_AMT
1711 , 0 -- DIST_uned_tax_ACCTD_AMT
1712 --
1713 , 0 -- tl_uned_alloc_amt
1714 , 0 -- tl_uned_alloc_acctd_amt
1715 , 0 -- tl_uned_chrg_alloc_amt
1716 , 0 -- tl_uned_chrg_alloc_acctd_amt
1717 , 0 -- tl_uned_frt_alloc_amt
1718 , 0 -- tl_uned_frt_alloc_acctd_amt
1719 , 0 -- tl_uned_tax_alloc_amt
1720 , 0 -- tl_uned_tax_alloc_acctd_amt
1721 --
1722 , adj.type -- source_type
1723 , 'ADJ' -- source_table
1724 , adj.adjustment_id -- source_id
1725 , ctl.line_type -- line_type
1726 --
1727 , NULL -- group_id
1728 , '00' -- source_data_key1
1729 , '00' -- source_data_key2
1730 , '00' -- source_data_key3
1731 , '00' -- source_data_key4
1732 , '00' -- source_data_key5
1733 , 'D' -- gp_level
1734 --
1735 , adj.set_of_books_id -- set_of_books_id
1736 , 'P' -- sob_type
1737 , USERENV('SESSIONID') -- se_gt_id
1738 , NULL -- tax_link_id
1739 , NULL -- tax_inc_flag
1740 , NULL -- ref_line_id
1741 , 'Y' -- REF_MF_DIST_FLAG
1742 FROM ar_adjustments_all adj,
1743 psa_mf_adj_dist_all psad,
1744 (SELECT MAX(ref_customer_trx_id) ref_customer_trx_id,
1745 MAX(to_currency) invoice_currency_code
1746 FROM ra_ar_gt
1747 GROUP BY ref_customer_trx_id, to_currency) trx,
1748 ra_customer_trx_lines_all ctl,
1749 ra_cust_trx_line_gl_dist_all ctlgd
1750 WHERE adj.customer_trx_id = trx.ref_customer_trx_id
1751 AND adj.status = 'A'
1752 AND adj.postable = 'Y'
1753 AND adj.upgrade_method = '11IMFAR'
1754 AND adj.type IN ('LINE','CHARGES','TAX','FREIGHT','INVOICE')
1755 AND adj.adjustment_id = psad.adjustment_id
1756 AND psad.cust_trx_line_gl_dist_id = ctlgd.cust_trx_line_gl_dist_id
1757 AND ctlgd.customer_trx_line_id = ctl.customer_trx_line_id;
1758 END IF;
1759 log('get_direct_mf_adj_dist -');
1760 EXCEPTION
1761 WHEN OTHERS THEN
1762 log('EXCEPTION OTHERS: get_direct_mf_adj_dist :'||SQLERRM);
1763 END get_direct_mf_adj_dist;
1764
1765
1766
1767
1768
1769
1770
1771
1772 PROCEDURE get_direct_inv_adj_dist
1773 (p_mode IN VARCHAR2,
1774 p_trx_id IN NUMBER DEFAULT NULL,
1775 p_gt_id IN NUMBER DEFAULT NULL)
1776 IS
1777 BEGIN
1778 log('get_direct_inv_adj_dist +');
1779 log(' p_mode : '|| p_mode);
1780 log(' p_trx_id : '|| p_trx_id);
1781 log(' p_gt_id : '|| p_gt_id);
1782
1783 IF p_mode = 'BATCH' THEN
1784 INSERT INTO RA_AR_GT
1785 ( GT_ID ,
1786 AMT ,
1787 ACCTD_AMT ,
1791 REF_CUSTOMER_TRX_LINE_ID ,
1788 ACCOUNT_CLASS ,
1789 CCID_SECONDARY ,
1790 REF_CUST_TRX_LINE_GL_DIST_ID,
1792 REF_CUSTOMER_TRX_ID ,
1793 TO_CURRENCY ,
1794 BASE_CURRENCY ,
1795 -- ADJ and APP Elmt
1796 DIST_AMT ,
1797 DIST_ACCTD_AMT ,
1798 DIST_CHRG_AMT ,
1799 DIST_CHRG_ACCTD_AMT ,
1800 DIST_FRT_AMT ,
1801 DIST_FRT_ACCTD_AMT ,
1802 DIST_TAX_AMT ,
1803 DIST_TAX_ACCTD_AMT ,
1804 -- Buc
1805 tl_alloc_amt ,
1806 tl_alloc_acctd_amt ,
1807 tl_chrg_alloc_amt ,
1808 tl_chrg_alloc_acctd_amt,
1809 tl_frt_alloc_amt ,
1810 tl_frt_alloc_acctd_amt,
1811 tl_tax_alloc_amt ,
1812 tl_tax_alloc_acctd_amt,
1813 -- ED Elmt
1814 DIST_ed_AMT,
1815 DIST_ed_ACCTD_AMT,
1816 DIST_ed_chrg_AMT,
1817 DIST_ed_chrg_ACCTD_AMT,
1818 DIST_ed_frt_AMT ,
1819 DIST_ed_frt_ACCTD_AMT,
1820 DIST_ed_tax_AMT ,
1821 DIST_ed_tax_ACCTD_AMT,
1822 --
1823 tl_ed_alloc_amt ,
1824 tl_ed_alloc_acctd_amt ,
1825 tl_ed_chrg_alloc_amt ,
1826 tl_ed_chrg_alloc_acctd_amt,
1827 tl_ed_frt_alloc_amt ,
1828 tl_ed_frt_alloc_acctd_amt,
1829 tl_ed_tax_alloc_amt ,
1830 tl_ed_tax_alloc_acctd_amt,
1831 -- UNED
1832 DIST_uned_AMT ,
1833 DIST_uned_ACCTD_AMT ,
1834 DIST_uned_chrg_AMT ,
1835 DIST_uned_chrg_ACCTD_AMT ,
1836 DIST_uned_frt_AMT ,
1837 DIST_uned_frt_ACCTD_AMT ,
1838 DIST_uned_tax_AMT ,
1839 DIST_uned_tax_ACCTD_AMT ,
1840 --
1841 tl_uned_alloc_amt ,
1842 tl_uned_alloc_acctd_amt ,
1843 tl_uned_chrg_alloc_amt ,
1844 tl_uned_chrg_alloc_acctd_amt,
1845 tl_uned_frt_alloc_amt ,
1846 tl_uned_frt_alloc_acctd_amt,
1847 tl_uned_tax_alloc_amt ,
1848 tl_uned_tax_alloc_acctd_amt,
1849 --
1850 source_type ,
1851 source_table ,
1852 source_id ,
1853 line_type,
1854 --
1855 group_id,
1856 source_data_key1 ,
1857 source_data_key2 ,
1858 source_data_key3 ,
1859 source_data_key4 ,
1860 source_data_key5 ,
1861 gp_level,
1862 --
1863 set_of_books_id,
1864 sob_type,
1865 se_gt_id,
1866 --{Taxable Amount
1867 tax_link_id,
1868 tax_inc_flag,
1869 --}
1870 ref_line_id
1871 )
1872 SELECT
1873 p_gt_id -- GT_ID
1874 , NVL(adj.amount,0) -- AMT
1875 , NVL(adj.acctd_amount,0) -- ACCTD_AMT
1876 , 'INVOICE' -- ACCOUNT_CLASS
1877 , adj.code_combination_id -- CCID_SECONDARY
1878 , -10 -- REF_CUST_TRX_LINE_GL_DIST_ID
1879 , -10 -- REF_CUSTOMER_TRX_LINE_ID
1880 , adj.customer_trx_id -- REF_CUSTOMER_TRX_ID
1881 , trx.invoice_currency_code -- TO_CURRENCY
1882 , NULL -- BASE_CURRENCY
1883 -- ADJ and APP Elmt
1884 , NVL(adj.line_adjusted,0) -- DIST_AMT
1885 , fct_acct_amt(NVL(adj.line_adjusted,0),
1886 NVL(adj.amount,0),
1887 NVL(adj.acctd_amount,0),
1888 trx.invoice_currency_code,
1889 sob.currency_code,
1890 adj.adjustment_id) -- DIST_ACCTD_AMT
1891 --
1892 , NVL(adj.receivables_charges_adjusted,0) -- DIST_CHRG_AMT
1893 , fct_acct_amt(NVL(adj.receivables_charges_adjusted,0),
1894 NVL(adj.amount,0),
1895 NVL(adj.acctd_amount,0),
1896 trx.invoice_currency_code,
1897 sob.currency_code,
1898 adj.adjustment_id) -- DIST_CHRG_ACCTD_AMT
1899 , NVL(adj.freight_adjusted,0) -- DIST_FRT_AMT
1900 , fct_acct_amt(NVL(adj.freight_adjusted,0),
1901 NVL(adj.amount,0),
1902 NVL(adj.acctd_amount,0),
1903 trx.invoice_currency_code,
1904 sob.currency_code,
1905 adj.adjustment_id) -- DIST_FRT_ACCTD_AMT
1906 , NVL(adj.tax_adjusted,0) -- DIST_TAX_AMT
1907 , fct_acct_amt(NVL(adj.tax_adjusted,0),
1908 NVL(adj.amount,0),
1909 NVL(adj.acctd_amount,0),
1910 trx.invoice_currency_code,
1911 sob.currency_code,
1912 adj.adjustment_id) -- DIST_TAX_ACCTD_AMT
1913 -- Buc
1914 , 0 -- tl_alloc_amt
1915 , 0 -- tl_alloc_acctd_amt
1916 , 0 -- tl_chrg_alloc_amt
1917 , 0 -- tl_chrg_alloc_acctd_amt
1918 , 0 -- tl_frt_alloc_amt
1919 , 0 -- tl_frt_alloc_acctd_amt
1920 , 0 -- tl_tax_alloc_amt
1921 , 0 -- tl_tax_alloc_acctd_amt
1922 -- ED Elmt
1923 , 0 -- DIST_ed_AMT
1927 , 0 -- DIST_ed_frt_AMT
1924 , 0 -- DIST_ed_ACCTD_AMT
1925 , 0 -- DIST_ed_chrg_AMT
1926 , 0 -- DIST_ed_chrg_ACCTD_AMT
1928 , 0 -- DIST_ed_frt_ACCTD_AMT
1929 , 0 -- DIST_ed_tax_AMT
1930 , 0 -- DIST_ed_tax_ACCTD_AMT
1931 --
1932 , 0 -- tl_ed_alloc_amt
1933 , 0 -- tl_ed_alloc_acctd_amt
1934 , 0 -- tl_ed_chrg_alloc_amt
1935 , 0 -- tl_ed_chrg_alloc_acctd_amt
1936 , 0 -- tl_ed_frt_alloc_amt
1937 , 0 -- tl_ed_frt_alloc_acctd_amt
1938 , 0 -- tl_ed_tax_alloc_amt
1939 , 0 -- tl_ed_tax_alloc_acctd_amt
1940 -- UNED
1941 , 0 -- DIST_uned_AMT
1942 , 0 -- DIST_uned_ACCTD_AMT
1943 , 0 -- DIST_uned_chrg_AMT
1944 , 0 -- DIST_uned_chrg_ACCTD_AMT
1945 , 0 -- DIST_uned_frt_AMT
1946 , 0 -- DIST_uned_frt_ACCTD_AMT
1947 , 0 -- DIST_uned_tax_AMT
1948 , 0 -- DIST_uned_tax_ACCTD_AMT
1949 --
1950 , 0 -- tl_uned_alloc_amt
1951 , 0 -- tl_uned_alloc_acctd_amt
1952 , 0 -- tl_uned_chrg_alloc_amt
1953 , 0 -- tl_uned_chrg_alloc_acctd_amt
1954 , 0 -- tl_uned_frt_alloc_amt
1955 , 0 -- tl_uned_frt_alloc_acctd_amt
1956 , 0 -- tl_uned_tax_alloc_amt
1957 , 0 -- tl_uned_tax_alloc_acctd_amt
1958 --
1959 , 'INVOICE' -- source_type
1960 , 'ADJ' -- source_table
1961 , adj.adjustment_id -- source_id
1962 , 'INVOICE' -- line_type
1963 --
1964 , NULL -- group_id
1965 , '00' -- source_data_key1
1966 , '00' -- source_data_key2
1967 , '00' -- source_data_key3
1968 , '00' -- source_data_key4
1969 , '00' -- source_data_key5
1970 , 'D' -- gp_level
1971 --
1972 , adj.set_of_books_id -- set_of_books_id
1973 , 'P' -- sob_type
1974 , USERENV('SESSIONID') -- se_gt_id
1975 , NULL -- tax_link_id
1976 , NULL -- tax_inc_flag
1977 , NULL -- ref_line_id
1978 FROM ar_adjustments_all adj,
1979 ar_system_parameters_all ars,
1980 -- For performance this sql is not nec as for legacy data
1981 -- we are proposing no tied by to original line
1982 -- in new transaction the ref_line_id will be present
1983 -- (SELECT MAX(line_id) line_id,
1984 -- source_id source_id
1985 -- FROM ar_distributions_all
1986 -- WHERE source_table = 'ADJ'
1987 -- GROUP BY source_id) ard,
1988 (SELECT MAX(ref_customer_trx_id) ref_customer_trx_id,
1989 MAX(to_currency) invoice_currency_code
1990 FROM ra_ar_gt
1991 WHERE source_table = 'CTLGD'
1992 GROUP BY ref_customer_trx_id,
1993 to_currency) trx,
1994 gl_sets_of_books sob
1995 WHERE adj.customer_trx_id = trx.ref_customer_trx_id
1996 AND adj.type = 'INVOICE'
1997 AND adj.status = 'A'
1998 AND adj.postable = 'Y'
1999 AND adj.set_of_books_id = sob.set_of_books_id
2000 AND adj.org_id = ars.org_id
2001 AND ars.accounting_method = 'CASH';
2002 -- AND adj.adjustment_id = ard.source_id;
2003
2004
2005 ELSIF p_mode = 'OLTP' THEN
2006
2007
2008 INSERT INTO RA_AR_GT
2009 ( GT_ID ,
2010 AMT ,
2011 ACCTD_AMT ,
2012 ACCOUNT_CLASS ,
2013 CCID_SECONDARY ,
2014 REF_CUST_TRX_LINE_GL_DIST_ID,
2015 REF_CUSTOMER_TRX_LINE_ID ,
2016 REF_CUSTOMER_TRX_ID ,
2017 TO_CURRENCY ,
2018 BASE_CURRENCY ,
2019 -- ADJ and APP Elmt
2020 DIST_AMT ,
2021 DIST_ACCTD_AMT ,
2022 DIST_CHRG_AMT ,
2023 DIST_CHRG_ACCTD_AMT ,
2024 DIST_FRT_AMT ,
2025 DIST_FRT_ACCTD_AMT ,
2026 DIST_TAX_AMT ,
2027 DIST_TAX_ACCTD_AMT ,
2028 -- Buc
2029 tl_alloc_amt ,
2030 tl_alloc_acctd_amt ,
2031 tl_chrg_alloc_amt ,
2032 tl_chrg_alloc_acctd_amt,
2033 tl_frt_alloc_amt ,
2034 tl_frt_alloc_acctd_amt,
2035 tl_tax_alloc_amt ,
2036 tl_tax_alloc_acctd_amt,
2037 -- ED Elmt
2038 DIST_ed_AMT,
2039 DIST_ed_ACCTD_AMT,
2040 DIST_ed_chrg_AMT,
2041 DIST_ed_chrg_ACCTD_AMT,
2042 DIST_ed_frt_AMT ,
2043 DIST_ed_frt_ACCTD_AMT,
2044 DIST_ed_tax_AMT ,
2045 DIST_ed_tax_ACCTD_AMT,
2046 --
2047 tl_ed_alloc_amt ,
2048 tl_ed_alloc_acctd_amt ,
2049 tl_ed_chrg_alloc_amt ,
2050 tl_ed_chrg_alloc_acctd_amt,
2051 tl_ed_frt_alloc_amt ,
2052 tl_ed_frt_alloc_acctd_amt,
2053 tl_ed_tax_alloc_amt ,
2054 tl_ed_tax_alloc_acctd_amt,
2055 -- UNED
2056 DIST_uned_AMT ,
2057 DIST_uned_ACCTD_AMT ,
2058 DIST_uned_chrg_AMT ,
2059 DIST_uned_chrg_ACCTD_AMT ,
2063 DIST_uned_tax_ACCTD_AMT ,
2060 DIST_uned_frt_AMT ,
2061 DIST_uned_frt_ACCTD_AMT ,
2062 DIST_uned_tax_AMT ,
2064 --
2065 tl_uned_alloc_amt ,
2066 tl_uned_alloc_acctd_amt ,
2067 tl_uned_chrg_alloc_amt ,
2068 tl_uned_chrg_alloc_acctd_amt,
2069 tl_uned_frt_alloc_amt ,
2070 tl_uned_frt_alloc_acctd_amt,
2071 tl_uned_tax_alloc_amt ,
2072 tl_uned_tax_alloc_acctd_amt,
2073 --
2074 source_type ,
2075 source_table ,
2076 source_id ,
2077 line_type,
2078 --
2079 group_id,
2080 source_data_key1 ,
2081 source_data_key2 ,
2082 source_data_key3 ,
2083 source_data_key4 ,
2084 source_data_key5 ,
2085 gp_level,
2086 --
2087 set_of_books_id,
2088 sob_type,
2089 se_gt_id,
2090 --{Taxable Amount
2091 tax_link_id,
2092 tax_inc_flag,
2093 --}
2094 ref_line_id
2095 )
2096 SELECT
2097 p_gt_id -- GT_ID
2098 , NVL(adj.amount,0) -- AMT
2099 , NVL(adj.acctd_amount,0) -- ACCTD_AMT
2100 , 'INVOICE' -- ACCOUNT_CLASS
2101 , adj.code_combination_id -- CCID_SECONDARY
2102 , -10 -- REF_CUST_TRX_LINE_GL_DIST_ID
2103 , -10 -- REF_CUSTOMER_TRX_LINE_ID
2104 , adj.customer_trx_id -- REF_CUSTOMER_TRX_ID
2105 , trx.invoice_currency_code -- TO_CURRENCY
2106 , NULL -- BASE_CURRENCY
2107 -- ADJ and APP Elmt
2108 , NVL(adj.line_adjusted,0) -- DIST_AMT
2109 , fct_acct_amt(NVL(adj.line_adjusted,0),
2110 NVL(adj.amount,0),
2111 NVL(adj.acctd_amount,0),
2112 trx.invoice_currency_code,
2113 sob.currency_code,
2114 adj.adjustment_id) -- DIST_ACCTD_AMT
2115 --
2116 , NVL(adj.receivables_charges_adjusted,0) -- DIST_CHRG_AMT
2117 , fct_acct_amt(NVL(adj.receivables_charges_adjusted,0),
2118 NVL(adj.amount,0),
2119 NVL(adj.acctd_amount,0),
2120 trx.invoice_currency_code,
2121 sob.currency_code,
2122 adj.adjustment_id) -- DIST_CHRG_ACCTD_AMT
2123 , NVL(adj.freight_adjusted,0) -- DIST_FRT_AMT
2124 , fct_acct_amt(NVL(adj.freight_adjusted,0),
2125 NVL(adj.amount,0),
2126 NVL(adj.acctd_amount,0),
2127 trx.invoice_currency_code,
2128 sob.currency_code,
2129 adj.adjustment_id) -- DIST_FRT_ACCTD_AMT
2130 , NVL(adj.tax_adjusted,0) -- DIST_TAX_AMT
2131 , fct_acct_amt(NVL(adj.tax_adjusted,0),
2132 NVL(adj.amount,0),
2133 NVL(adj.acctd_amount,0),
2134 trx.invoice_currency_code,
2135 sob.currency_code,
2136 adj.adjustment_id) -- DIST_TAX_ACCTD_AMT
2137 -- Buc
2138 , 0 -- tl_alloc_amt
2139 , 0 -- tl_alloc_acctd_amt
2140 , 0 -- tl_chrg_alloc_amt
2141 , 0 -- tl_chrg_alloc_acctd_amt
2142 , 0 -- tl_frt_alloc_amt
2143 , 0 -- tl_frt_alloc_acctd_amt
2144 , 0 -- tl_tax_alloc_amt
2145 , 0 -- tl_tax_alloc_acctd_amt
2146 -- ED Elmt
2147 , 0 -- DIST_ed_AMT
2148 , 0 -- DIST_ed_ACCTD_AMT
2149 , 0 -- DIST_ed_chrg_AMT
2150 , 0 -- DIST_ed_chrg_ACCTD_AMT
2151 , 0 -- DIST_ed_frt_AMT
2152 , 0 -- DIST_ed_frt_ACCTD_AMT
2153 , 0 -- DIST_ed_tax_AMT
2154 , 0 -- DIST_ed_tax_ACCTD_AMT
2155 --
2156 , 0 -- tl_ed_alloc_amt
2157 , 0 -- tl_ed_alloc_acctd_amt
2158 , 0 -- tl_ed_chrg_alloc_amt
2159 , 0 -- tl_ed_chrg_alloc_acctd_amt
2160 , 0 -- tl_ed_frt_alloc_amt
2161 , 0 -- tl_ed_frt_alloc_acctd_amt
2162 , 0 -- tl_ed_tax_alloc_amt
2163 , 0 -- tl_ed_tax_alloc_acctd_amt
2164 -- UNED
2165 , 0 -- DIST_uned_AMT
2166 , 0 -- DIST_uned_ACCTD_AMT
2167 , 0 -- DIST_uned_chrg_AMT
2168 , 0 -- DIST_uned_chrg_ACCTD_AMT
2169 , 0 -- DIST_uned_frt_AMT
2170 , 0 -- DIST_uned_frt_ACCTD_AMT
2171 , 0 -- DIST_uned_tax_AMT
2172 , 0 -- DIST_uned_tax_ACCTD_AMT
2173 --
2174 , 0 -- tl_uned_alloc_amt
2175 , 0 -- tl_uned_alloc_acctd_amt
2176 , 0 -- tl_uned_chrg_alloc_amt
2177 , 0 -- tl_uned_chrg_alloc_acctd_amt
2178 , 0 -- tl_uned_frt_alloc_amt
2179 , 0 -- tl_uned_frt_alloc_acctd_amt
2180 , 0 -- tl_uned_tax_alloc_amt
2181 , 0 -- tl_uned_tax_alloc_acctd_amt
2182 --
2183 , 'INVOICE' -- source_type
2184 , 'ADJ' -- source_table
2185 , adj.adjustment_id -- source_id
2186 , 'INVOICE' -- line_type
2187 --
2188 , NULL -- group_id
2189 , '00' -- source_data_key1
2190 , '00' -- source_data_key2
2191 , '00' -- source_data_key3
2195 --
2192 , '00' -- source_data_key4
2193 , '00' -- source_data_key5
2194 , 'D' -- gp_level
2196 , adj.set_of_books_id -- set_of_books_id
2197 , 'P' -- sob_type
2198 , USERENV('SESSIONID') -- se_gt_id
2199 , NULL -- tax_link_id
2200 , NULL -- tax_inc_flag
2201 , NULL -- ref_line_id
2202 FROM ar_adjustments adj,
2203 (SELECT MAX(ref_customer_trx_id) ref_customer_trx_id,
2204 MAX(to_currency) invoice_currency_code
2205 FROM ra_ar_gt
2206 WHERE gt_id = p_gt_id
2207 GROUP BY ref_customer_trx_id, to_currency) trx,
2208 gl_sets_of_books sob
2209 WHERE adj.customer_trx_id = p_trx_id
2210 AND adj.customer_trx_id = trx.ref_customer_trx_id
2211 AND adj.type = 'INVOICE'
2212 AND adj.status = 'A'
2213 AND adj.postable = 'Y'
2214 AND adj.upgrade_method = '11I'
2215 AND adj.set_of_books_id = sob.set_of_books_id;
2216
2217
2218 END IF;
2219 log('get_direct_inv_adj_dist -');
2220 EXCEPTION
2221 WHEN OTHERS THEN
2222 log('EXCEPTION OTHERS: get_direct_inv_adj_dist :'||SQLERRM);
2223 END get_direct_inv_adj_dist;
2224
2225
2226
2227
2228
2229
2230
2231
2232 PROCEDURE update_base
2233 (p_gt_id IN NUMBER DEFAULT NULL)
2234 IS
2235 BEGIN
2236 log('update_base +');
2237 --populate the base amounts
2238 INSERT INTO ar_base_dist_amts_gt
2239 ( gt_id,
2240 gp_level,
2241 ref_customer_trx_id ,
2242 ref_customer_trx_line_id,
2243 base_dist_amt ,
2244 base_dist_acctd_amt ,
2245 base_dist_chrg_amt ,
2246 base_dist_chrg_acctd_amt ,
2247 base_dist_frt_amt ,
2248 base_dist_frt_acctd_amt ,
2249 base_dist_tax_amt ,
2250 base_dist_tax_acctd_amt ,
2251
2252 base_ed_dist_amt ,
2253 base_ed_dist_acctd_amt ,
2254 base_ed_dist_chrg_amt ,
2255 base_ed_dist_chrg_acctd_amt,
2256 base_ed_dist_frt_amt ,
2257 base_ed_dist_frt_acctd_amt ,
2258 base_ed_dist_tax_amt ,
2259 base_ed_dist_tax_acctd_amt ,
2260
2261 base_uned_dist_amt,
2262 base_uned_dist_acctd_amt,
2263 base_uned_dist_chrg_amt,
2264 base_uned_dist_chrg_acctd_amt,
2265 base_uned_dist_frt_amt,
2266 base_uned_dist_frt_acctd_amt,
2267 base_uned_dist_tax_amt,
2268 base_uned_dist_tax_acctd_amt,
2269 set_of_books_id,
2270 sob_type,
2271 source_table,
2272 source_type
2273 )
2274 SELECT DISTINCT
2275 a.gt_id,
2276 a.gp_level,
2277 a.ref_customer_trx_id ,
2278 a.ref_customer_trx_line_id,
2279
2280 s.sum_dist_amt,
2281 s.sum_dist_acctd_amt,
2282 s.sum_dist_chrg_amt,
2283 s.sum_dist_chrg_acctd_amt,
2284 s.sum_dist_frt_amt,
2285 s.sum_dist_frt_acctd_amt,
2286 s.sum_dist_tax_amt,
2287 s.sum_dist_tax_acctd_amt,
2288 --
2289 DECODE(a.source_table,'CTLGD',s.sum_dist_ed_amt,0),
2290 DECODE(a.source_table,'CTLGD',s.sum_dist_ed_acctd_amt,0),
2291 DECODE(a.source_table,'CTLGD',s.sum_dist_ed_chrg_amt,0),
2292 DECODE(a.source_table,'CTLGD',s.sum_dist_ed_chrg_acctd_amt,0),
2293 DECODE(a.source_table,'CTLGD',s.sum_dist_ed_frt_amt,0),
2294 DECODE(a.source_table,'CTLGD',s.sum_dist_ed_frt_acctd_amt,0),
2295 DECODE(a.source_table,'CTLGD',s.sum_dist_ed_tax_amt,0),
2296 DECODE(a.source_table,'CTLGD',s.sum_dist_ed_tax_acctd_amt,0),
2297 --
2298 DECODE(a.source_table,'CTLGD',s.sum_dist_uned_amt,0),
2299 DECODE(a.source_table,'CTLGD',s.sum_dist_uned_acctd_amt,0),
2300 DECODE(a.source_table,'CTLGD',s.sum_dist_uned_chrg_amt,0),
2301 DECODE(a.source_table,'CTLGD',s.sum_dist_uned_chrg_acctd_amt,0),
2302 DECODE(a.source_table,'CTLGD',s.sum_dist_uned_frt_amt,0),
2303 DECODE(a.source_table,'CTLGD',s.sum_dist_uned_frt_acctd_amt,0),
2304 DECODE(a.source_table,'CTLGD',s.sum_dist_uned_tax_amt,0),
2305 DECODE(a.source_table,'CTLGD',s.sum_dist_uned_tax_acctd_amt,0),
2306
2307 a.set_of_books_id,
2308 a.sob_type,
2309 a.source_table,
2310 a.source_type
2311 FROM (SELECT
2312 SUM(NVL(b.DIST_AMT,0)) sum_dist_amt ,
2313 SUM(NVL(b.DIST_ACCTD_AMT,0)) sum_dist_acctd_amt,
2314 SUM(NVL(b.DIST_CHRG_AMT,0)) sum_dist_chrg_amt,
2315 SUM(NVL(b.DIST_CHRG_ACCTD_AMT,0)) sum_dist_chrg_acctd_amt,
2316 SUM(NVL(b.DIST_FRT_AMT,0)) sum_dist_frt_amt,
2317 SUM(NVL(b.DIST_FRT_ACCTD_AMT,0)) sum_dist_frt_acctd_amt,
2318 SUM(NVL(b.DIST_TAX_AMT,0)) sum_dist_tax_amt,
2319 SUM(NVL(b.DIST_TAX_ACCTD_AMT,0)) sum_dist_tax_acctd_amt,
2320 --
2321 SUM(NVL(b.DIST_ed_AMT,0)) sum_dist_ed_amt,
2322 SUM(NVL(b.DIST_ed_ACCTD_AMT,0)) sum_dist_ed_acctd_amt,
2323 SUM(NVL(b.DIST_ed_chrg_AMT,0)) sum_dist_ed_chrg_amt,
2324 SUM(NVL(b.DIST_ed_chrg_ACCTD_AMT,0)) sum_dist_ed_chrg_acctd_amt,
2325 SUM(NVL(b.DIST_ed_frt_AMT,0)) sum_dist_ed_frt_amt,
2326 SUM(NVL(b.DIST_ed_frt_ACCTD_AMT,0)) sum_dist_ed_frt_acctd_amt,
2327 SUM(NVL(b.DIST_ed_tax_AMT,0)) sum_dist_ed_tax_amt,
2331 SUM(NVL(b.DIST_uned_ACCTD_AMT,0)) sum_dist_uned_acctd_amt,
2328 SUM(NVL(b.DIST_ed_tax_ACCTD_AMT,0)) sum_dist_ed_tax_acctd_amt,
2329 --
2330 SUM(NVL(b.DIST_uned_AMT,0)) sum_dist_uned_amt,
2332 SUM(NVL(b.DIST_uned_chrg_AMT,0)) sum_dist_uned_chrg_amt,
2333 SUM(NVL(b.DIST_uned_chrg_ACCTD_AMT,0)) sum_dist_uned_chrg_acctd_amt,
2334 SUM(NVL(b.DIST_uned_frt_AMT,0)) sum_dist_uned_frt_amt,
2335 SUM(NVL(b.DIST_uned_frt_ACCTD_AMT,0)) sum_dist_uned_frt_acctd_amt,
2336 SUM(NVL(b.DIST_uned_tax_AMT,0)) sum_dist_uned_tax_amt,
2337 SUM(NVL(b.DIST_uned_tax_ACCTD_AMT,0)) sum_dist_uned_tax_acctd_amt,
2338 b.ref_customer_trx_id ref_customer_trx_id,
2339 b.gt_id gt_id
2340 FROM ra_ar_gt b
2341 GROUP BY b.ref_customer_trx_id,
2342 b.gt_id ) s,
2343 ra_ar_gt a
2344 WHERE a.ref_customer_trx_id = s.ref_customer_trx_id
2345 AND a.gt_id = s.gt_id
2346 AND a.gt_id = NVL(p_gt_id,a.gt_id);
2347
2348 log('update_base -');
2349 EXCEPTION
2350 WHEN OTHERS THEN
2351 log('EXCEPTION OTHERS: update_base :'||SQLERRM);
2352 END update_base;
2353
2354
2355
2356
2357
2358
2359
2360 PROCEDURE create_distributions
2361 IS
2362 l_cash_post VARCHAR2(1) := 'N';
2363 l_mfar_post VARCHAR2(1) := 'N';
2364
2365 -- MFAR full upgraded to ARD this routine not used
2366 FUNCTION is_mfar_post
2367 RETURN VARCHAR2
2368 IS
2369 CURSOR c1 IS
2370 SELECT app.receivable_application_id
2371 FROM xla_events_gt evt,
2372 ar_receivable_applications_all app
2373 WHERE evt.event_type_code IN ('RECP_CREATE' ,'RECP_UPDATE' ,
2374 'RECP_RATE_ADJUST' ,'RECP_REVERSE' ,
2375 'CM_CREATE' ,'CM_UPDATE')
2376 AND evt.event_id = app.event_id
2377 AND app.status = 'APP'
2378 AND app.upgrade_method IS NULL
2379 AND EXISTS (SELECT '1'
2380 FROM ar_adjustments_all adj
2381 WHERE adj.customer_trx_id = app.applied_customer_trx_id
2382 AND adj.upgrade_method = '11IMFAR'
2383 AND adj.status = 'A'
2384 AND adj.postable = 'Y')
2385 MINUS -- This is to avoid corrupted data. In the case the same invoice has MF and none MF adjustment
2386 -- theorically impossible
2387 SELECT app.receivable_application_id
2388 FROM xla_events_gt evt,
2389 ar_receivable_applications_all app
2390 WHERE evt.event_type_code IN ('RECP_CREATE' ,'RECP_UPDATE' ,
2391 'RECP_RATE_ADJUST' ,'RECP_REVERSE' ,
2392 'CM_CREATE' ,'CM_UPDATE')
2393 AND evt.event_id = app.event_id
2394 AND app.status = 'APP'
2395 AND app.upgrade_method IS NULL
2396 AND EXISTS (SELECT '1'
2397 FROM ar_adjustments_all adj
2398 WHERE adj.customer_trx_id = app.applied_customer_trx_id
2399 AND adj.upgrade_method = '11I'
2400 AND adj.status = 'A'
2401 AND adj.postable = 'Y');
2402
2403
2404 l_res VARCHAR2(1);
2405 l_ra_id NUMBER;
2406 BEGIN
2407 OPEN c1;
2408 FETCH c1 INTO l_ra_id;
2409 IF c1%NOTFOUND THEN
2410 l_res := 'N';
2411 ELSE
2412 l_res := 'Y';
2413 END IF;
2414 CLOSE c1;
2415 RETURN l_res;
2416 END is_mfar_post;
2417
2418
2419 FUNCTION is_cash_post
2420 RETURN VARCHAR2
2421 IS
2422 CURSOR c1 IS
2423 SELECT app.receivable_application_id
2424 FROM xla_events_gt evt,
2425 ar_receivable_applications_all app,
2426 ar_system_parameters_all ars
2427 WHERE evt.event_type_code IN ('RECP_CREATE' ,'RECP_UPDATE' ,
2428 'RECP_RATE_ADJUST' ,'RECP_REVERSE' ,
2429 'CM_CREATE' ,'CM_UPDATE')
2430 AND evt.event_id = app.event_id
2431 AND app.status = 'APP'
2432 AND app.upgrade_method IS NULL
2433 AND app.org_id = ars.org_id
2434 AND ars.accounting_method = 'CASH'
2435 AND EXISTS (SELECT '1'
2436 FROM ar_adjustments_all adj
2437 WHERE adj.customer_trx_id = app.applied_customer_trx_id
2438 AND adj.upgrade_method = '11I'
2439 AND adj.status = 'A'
2440 AND adj.postable = 'Y');
2441 l_res VARCHAR2(1);
2442 l_ra_id NUMBER;
2443 BEGIN
2444 OPEN c1;
2445 FETCH c1 INTO l_ra_id;
2446 IF c1%NOTFOUND THEN
2447 l_res := 'N';
2448 ELSE
2449 l_res := 'Y';
2450 END IF;
2451 CLOSE c1;
2452 RETURN l_res;
2453 END is_cash_post;
2454
2455 BEGIN
2456 log('create_distributions +');
2457
2458 l_cash_post := is_cash_post;
2459 IF l_cash_post = 'Y' THEN
2460 create_cash_distributions;
2464 -- As all MFAR data into ar_distributions
2461 END IF;
2462
2463 -- Mfar post might be obsolete as AR will migrate all MFAR data into ar_distributions
2465 -- l_mfar_post := is_mfar_post;
2466 -- IF l_mfar_post = 'Y' THEN
2467 -- create_mfar_distributions;
2468 -- END IF;
2469
2470
2471 log('create_distributions -');
2472 EXCEPTION
2473 WHEN OTHERS THEN
2474 log( 'EXCEPTION OTHERS Create_distributions: '||SQLERRM);
2475 RAISE;
2476 END Create_distributions;
2477
2478
2479
2480
2481
2482
2483
2484
2485
2486 PROCEDURE create_cash_distributions
2487 IS
2488 l_sob_id NUMBER;
2489 l_accounting_method ar_system_parameters.accounting_method%TYPE;
2490 l_create_acct VARCHAR2(1) := 'Y';
2491 l_gt_id NUMBER := 0;
2492
2493 CURSOR c_app IS
2494 SELECT app.*
2495 FROM xla_events_gt evt,
2496 ar_receivable_applications_all app,
2497 ar_system_parameters_all ars
2498 WHERE evt.event_type_code IN ( 'RECP_CREATE' ,'RECP_UPDATE' ,
2499 'RECP_RATE_ADJUST' ,'RECP_REVERSE',
2500 'CM_CREATE' ,'CM_UPDATE' )
2501 AND evt.event_id = app.event_id
2502 AND app.status = 'APP'
2503 AND app.upgrade_method IS NULL
2504 AND app.org_id = ars.org_id
2505 AND ars.accounting_method = 'CASH'
2506 AND NOT EXISTS (SELECT '1'
2507 FROM psa_trx_types_all psa,
2508 ra_customer_trx_all inv
2509 WHERE inv.customer_trx_id = app.applied_customer_trx_id
2510 AND inv.cust_trx_type_id = psa.psa_trx_type_id);
2511 /*
2512 -- Only to reconcile for CMAPP the applied to transaction
2513 -- as the from cm can not have been adjusted
2514 -- and in cash basis we only need to post applications
2515 -- which is on the to document
2516 CURSOR c_cm_from_app IS
2517 SELECT app.*
2518 FROM xla_events_gt evt,
2519 ar_receivable_applications_all app,
2520 ar_system_parameters_all ars
2521 WHERE evt.event_type_code IN ('CM_CREATE','CM_UPDATE')
2522 AND evt.event_id = app.event_id
2523 AND app.status = 'APP'
2524 AND app.upgrade_method IS NULL
2525 AND app.org_id = ars.org_id
2526 AND ars.accounting_method = 'CASH'
2527 AND NOT EXISTS (SELECT '1'
2528 FROM psa_trx_types_all psa,
2529 ra_customer_trx_all inv
2530 WHERE inv.customer_trx_id = app.customer_trx_id
2531 AND inv.cust_trx_type_id = psa.psa_trx_type_id);
2532 */
2533 l_app_rec ar_receivable_applications%ROWTYPE;
2534 l_line_acctd_amt NUMBER;
2535 l_tax_acctd_amt NUMBER;
2536 l_frt_acctd_amt NUMBER;
2537 l_chrg_acctd_amt NUMBER;
2538 l_ed_line_acctd_amt NUMBER;
2539 l_ed_tax_acctd_amt NUMBER;
2540 l_ed_frt_acctd_amt NUMBER;
2541 l_ed_chrg_acctd_amt NUMBER;
2542 l_ued_line_acctd_amt NUMBER;
2543 l_ued_tax_acctd_amt NUMBER;
2544 l_ued_frt_acctd_amt NUMBER;
2545 l_ued_chrg_acctd_amt NUMBER;
2546 dummy VARCHAR2(1);
2547 l_ra_list DBMS_SQL.NUMBER_TABLE;
2548 erase_ra_list DBMS_SQL.NUMBER_TABLE;
2549 i NUMBER := 0;
2550 end_process_stop EXCEPTION;
2551
2552
2553
2554 BEGIN
2555 log('create_cash_distributions +');
2556
2557
2558 DELETE FROM ra_ar_gt;
2559
2560 -- Get the distributions ready
2561 get_direct_inv_dist(p_mode => 'BATCH');
2562
2563 get_direct_adj_dist(p_mode => 'BATCH');
2564
2565 get_direct_inv_adj_dist(p_mode => 'BATCH');
2566
2567 update_base;
2568
2569 -- Cash Basis
2570 OPEN c_app;
2571 LOOP
2572
2573 FETCH c_app INTO l_app_rec;
2574 EXIT WHEN c_app%NOTFOUND;
2575
2576 Init_Curr_Details(p_sob_id => l_app_rec.set_of_books_id,
2577 p_org_id => l_app_rec.org_id,
2578 x_accounting_method => l_accounting_method);
2579
2580 fnd_client_info.set_currency_context(g_ae_sys_rec.set_of_books_id);
2581
2582 l_gt_id := l_gt_id + 1;
2583
2584 -- proration
2585 arp_det_dist_pkg.prepare_for_ra
2586 ( p_gt_id => l_gt_id,
2587 p_app_rec => l_app_rec,
2588 p_ae_sys_rec => g_ae_sys_rec,
2589 p_inv_cm => 'I',
2590 p_cash_mfar => 'CASH');
2591
2592 arp_standard.debug( 'setting the currency context back to null');
2593 fnd_client_info.set_currency_context(NULL);
2594
2595 END LOOP;
2596 CLOSE c_app;
2597
2598
2599 -- For the from document CM
2600 -- OPEN c_cm_from_app;
2601 -- LOOP
2602 -- FETCH c_cm_from_app INTO l_app_rec;
2603 -- EXIT WHEN c_app%NOTFOUND;
2604 -- Init_Curr_Details(p_sob_id => l_app_rec.set_of_books_id,
2605 -- p_org_id => l_app_rec.org_id,
2606 -- x_accounting_method => l_accounting_method);
2607 -- fnd_client_info.set_currency_context(g_ae_sys_rec.set_of_books_id);
2611 -- ( p_gt_id => l_gt_id,
2608 -- l_gt_id := l_gt_id + 1;
2609 -- proration
2610 -- arp_det_dist_pkg.prepare_for_ra
2612 -- p_app_rec => l_app_rec,
2613 -- p_ae_sys_rec => g_ae_sys_rec,
2614 -- p_inv_cm => 'C',
2615 -- p_cash_mfar => 'CASH');
2616 -- arp_standard.debug( 'setting the currency context back to null');
2617 -- fnd_client_info.set_currency_context(NULL);
2618 -- END LOOP;
2619 -- CLOSE c_cm_from_app;
2620
2621 -- Stamping the CASH applications
2622 stamping_11i_app_post;
2623
2624
2625 log('create_cash_distributions -');
2626 EXCEPTION
2627 WHEN OTHERS THEN
2628 log( 'EXCEPTION OTHERS Create_cash_distributions: '||SQLERRM);
2629 RAISE;
2630 END Create_cash_distributions;
2631
2632
2633
2634
2635
2636
2637
2638
2639 /*
2640 PROCEDURE create_mfar_distributions
2641 IS
2642 l_sob_id NUMBER;
2643 l_accounting_method ar_system_parameters.accounting_method%TYPE;
2644 l_create_acct VARCHAR2(1) := 'Y';
2645 l_gt_id NUMBER := 0;
2646
2647 CURSOR c_app IS
2648 SELECT app.*
2649 FROM xla_events_gt evt,
2650 ar_receivable_applications_all app
2651 WHERE evt.event_type_code IN ( 'RECP_CREATE' ,'RECP_UPDATE' ,
2652 'RECP_RATE_ADJUST' ,'RECP_REVERSE',
2653 'CM_CREATE' ,'CM_UPDATE' )
2654 AND evt.event_id = app.event_id
2655 AND app.status = 'APP'
2656 AND app.upgrade_method IS NULL
2657 AND EXISTS (SELECT '1' FROM ar_adjustments_all adj
2658 WHERE adj.customer_trx_id = app.applied_customer_trx_id
2659 AND adj.upgrade_method = '11IMFAR'
2660 AND adj.status = 'A'
2661 AND adj.postable = 'Y')
2662 MINUS
2663 SELECT app.*
2664 FROM xla_events_gt evt,
2665 ar_receivable_applications_all app
2666 WHERE evt.event_type_code IN ( 'RECP_CREATE' ,'RECP_UPDATE' ,
2667 'RECP_RATE_ADJUST' ,'RECP_REVERSE',
2668 'CM_CREATE' ,'CM_UPDATE' )
2669 AND evt.event_id = app.event_id
2670 AND app.status = 'APP'
2671 AND app.upgrade_method IS NULL
2672 AND EXISTS (SELECT '1' FROM ar_adjustments_all adj
2673 WHERE adj.customer_trx_id = app.applied_customer_trx_id
2674 AND adj.upgrade_method = '11I'
2675 AND adj.status = 'A'
2676 AND adj.postable = 'Y');
2677
2678
2679 CURSOR c_cm_from_app IS
2680 SELECT app.*
2681 FROM xla_events_gt evt,
2682 ar_receivable_applications_all app
2683 WHERE evt.event_type_code IN ('CM_CREATE','CM_UPDATE')
2684 AND evt.event_id = app.event_id
2685 AND app.status = 'APP'
2686 AND app.upgrade_method IS NULL
2687 AND EXISTS (SELECT '1' FROM ar_adjustments_all adj
2688 WHERE adj.customer_trx_id = app.customer_trx_id
2689 AND adj.upgrade_method = '11IMFAR'
2690 AND adj.status = 'A'
2691 AND adj.postable = 'Y')
2692 MINUS
2693 SELECT app.*
2694 FROM xla_events_gt evt,
2695 ar_receivable_applications_all app
2696 WHERE evt.event_type_code IN ('CM_CREATE','CM_UPDATE')
2697 AND evt.event_id = app.event_id
2698 AND app.status = 'APP'
2699 AND app.upgrade_method IS NULL
2700 AND EXISTS (SELECT '1' FROM ar_adjustments_all adj
2701 WHERE adj.customer_trx_id = app.customer_trx_id
2702 AND adj.upgrade_method = '11I'
2703 AND adj.status = 'A'
2704 AND adj.postable = 'Y');
2705
2706
2707 l_app_rec ar_receivable_applications%ROWTYPE;
2708 l_line_acctd_amt NUMBER;
2709 l_tax_acctd_amt NUMBER;
2710 l_frt_acctd_amt NUMBER;
2711 l_chrg_acctd_amt NUMBER;
2712 l_ed_line_acctd_amt NUMBER;
2713 l_ed_tax_acctd_amt NUMBER;
2714 l_ed_frt_acctd_amt NUMBER;
2715 l_ed_chrg_acctd_amt NUMBER;
2716 l_ued_line_acctd_amt NUMBER;
2717 l_ued_tax_acctd_amt NUMBER;
2718 l_ued_frt_acctd_amt NUMBER;
2719 l_ued_chrg_acctd_amt NUMBER;
2720 dummy VARCHAR2(1);
2721 l_ra_list DBMS_SQL.NUMBER_TABLE;
2722 erase_ra_list DBMS_SQL.NUMBER_TABLE;
2723 i NUMBER := 0;
2724 end_process_stop EXCEPTION;
2725
2726 BEGIN
2727
2728 log('create_mfar_distributions +');
2729
2730
2731 DELETE FROM ra_ar_gt;
2732
2733 -- Get the distributions ready
2734 get_direct_mf_inv_dist(p_mode => 'BATCH');
2735
2736 get_direct_mf_adj_dist(p_mode => 'BATCH');
2737
2738 update_base;
2739
2740 -- MFAR basis
2741 OPEN c_app;
2742 LOOP
2743
2744 FETCH c_app INTO l_app_rec;
2745 EXIT WHEN c_app%NOTFOUND;
2746
2747 Init_Curr_Details(p_sob_id => l_app_rec.set_of_books_id,
2748 p_org_id => l_app_rec.org_id,
2752
2749 x_accounting_method => l_accounting_method);
2750
2751 fnd_client_info.set_currency_context(g_ae_sys_rec.set_of_books_id);
2753 l_gt_id := l_gt_id + 1;
2754
2755 -- proration
2756 arp_det_dist_pkg.prepare_for_ra
2757 ( p_gt_id => l_gt_id,
2758 p_app_rec => l_app_rec,
2759 p_ae_sys_rec => g_ae_sys_rec,
2760 p_inv_cm => 'I',
2761 p_cash_mfar => 'MFAR');
2762
2763 arp_standard.debug( 'setting the currency context back to null');
2764 fnd_client_info.set_currency_context(NULL);
2765
2766 END LOOP;
2767 CLOSE c_app;
2768
2769 -- For the from document CM
2770 OPEN c_cm_from_app;
2771 LOOP
2772
2773 FETCH c_cm_from_app INTO l_app_rec;
2774 EXIT WHEN c_app%NOTFOUND;
2775
2776 Init_Curr_Details(p_sob_id => l_app_rec.set_of_books_id,
2777 p_org_id => l_app_rec.org_id,
2778 x_accounting_method => l_accounting_method);
2779
2780 fnd_client_info.set_currency_context(g_ae_sys_rec.set_of_books_id);
2781
2782 l_gt_id := l_gt_id + 1;
2783
2784 -- proration
2785 arp_det_dist_pkg.prepare_for_ra
2786 ( p_gt_id => l_gt_id,
2787 p_app_rec => l_app_rec,
2788 p_ae_sys_rec => g_ae_sys_rec,
2789 p_inv_cm => 'C',
2790 p_cash_mfar => 'MFAR');
2791
2792
2793 arp_standard.debug( 'setting the currency context back to null');
2794 fnd_client_info.set_currency_context(NULL);
2795
2796 END LOOP;
2797 CLOSE c_cm_from_app;
2798
2799 -- Stamping the MFAR applications
2800 stamping_11i_mfar_app_post;
2801
2802 log('create_mfar_distributions -');
2803 EXCEPTION
2804 WHEN OTHERS THEN
2805 log( 'EXCEPTION OTHERS Create_mfar_distributions: '||SQLERRM);
2806 RAISE;
2807 END Create_mfar_distributions;
2808 */
2809
2810
2811
2812
2813
2814
2815
2816
2817
2818
2819
2820
2821
2822 ---------------------------------------
2823 -- PROCEDURE COMPARE_RA_REM_AMT
2824 ---------------------------------------
2825 -- Arguments Input
2826 -- p_app_rec IN ar_receivable_applications%ROWTYPE -- the application record initial
2827 -- p_app_level IN VARCHAR2 DEFAULT 'TRANSACTION' -- level of application
2828 -- p_group_id IN VARCHAR2 DEFAULT NULL -- if level = GROUP then which group
2829 -- p_ctl_id IN NUMBER DEFAULT NULL -- if level = LINE then which line
2830 -- p_currency IN VARCHAR2 -- transactional currency
2831 --------------
2832 -- Outputs
2833 -- x_app_rec OUT NOCOPY ar_receivable_applications%ROWTYPE -- after leasing the result app_rec
2834 -- x_return_status IN OUT NOCOPY VARCHAR2
2835 -- x_msg_data IN OUT NOCOPY VARCHAR2
2836 -- x_msg_count IN OUT NOCOPY NUMBER
2837 --------------
2838 -- Objective:
2839 -- When does a application on a 11i MFAR transaction, the amount allocated per bucket can in disconcordance
2840 -- with the remaining amounts stamped in AR on the transaction because
2841 -- AR tied the charges and freight adjusted to revenue line
2842 -- but PSA tied the freight to freight line
2843 -- prorate the charges on all lines
2844 -- Therefore remaining amount calculated by AR can not the same from PSA
2845 -- For legacy transaction originate by PSA, in the upgrade AR should ensure:
2846 -- * the overall amount remaining all buckets and application all buckets are not incompatible
2847 -- that is no overapplication
2848 -- * the ED UNED bucket are not mixed with the application buckets
2849 -- * but the disconcordance between the rem and the application amount per bucket will be
2850 -- handled by the amount applied bucket
2851 ----------------------------------------
2852 PROCEDURE COMPARE_RA_REM_AMT
2853 ( p_app_rec IN ar_receivable_applications%ROWTYPE,
2854 x_app_rec OUT NOCOPY ar_receivable_applications%ROWTYPE,
2855 p_app_level IN VARCHAR2 DEFAULT 'TRANSACTION',
2856 p_source_data_key1 IN VARCHAR2 DEFAULT NULL,
2857 p_source_data_key2 IN VARCHAR2 DEFAULT NULL,
2858 p_source_data_key3 IN VARCHAR2 DEFAULT NULL,
2859 p_source_data_key4 IN VARCHAR2 DEFAULT NULL,
2860 p_source_data_key5 IN VARCHAR2 DEFAULT NULL,
2861 p_ctl_id IN NUMBER DEFAULT NULL,
2862 p_currency IN VARCHAR2,
2863 x_return_status IN OUT NOCOPY VARCHAR2,
2864 x_msg_data IN OUT NOCOPY VARCHAR2,
2865 x_msg_count IN OUT NOCOPY NUMBER)
2866 IS
2867 l_line_rem NUMBER;
2868 l_tax_rem NUMBER;
2869 l_freight_rem NUMBER;
2870 l_chrg_rem NUMBER;
2871
2872 l_chrg_app NUMBER;
2873 l_chrg_ed NUMBER;
2874 l_chrg_uned NUMBER;
2875 l_chrg_entire NUMBER;
2876
2877 l_freight_app NUMBER;
2878 l_freight_ed NUMBER;
2879 l_freight_uned NUMBER;
2880 l_freight_entire NUMBER;
2881
2882 l_tax_app NUMBER;
2883 l_tax_ed NUMBER;
2884 l_tax_uned NUMBER;
2888 l_line_ed NUMBER;
2885 l_tax_entire NUMBER;
2886
2887 l_line_app NUMBER;
2889 l_line_uned NUMBER;
2890 l_line_entire NUMBER;
2891
2892 l_entire NUMBER;
2893 l_rem NUMBER;
2894
2895 l_new_line_entire NUMBER;
2896 l_new_tax_entire NUMBER;
2897 l_new_freight_entire NUMBER;
2898 l_new_chrg_entire NUMBER;
2899
2900 l_run_rem NUMBER := 0;
2901 l_line_apps NUMBER := 0;
2902 l_run_apps NUMBER := 0;
2903 l_tax_apps NUMBER := 0;
2904 l_freight_apps NUMBER := 0;
2905 l_chrg_apps NUMBER := 0;
2906 over_applications EXCEPTION;
2907
2908
2909 BEGIN
2910 arp_standard.debug('COMPARE_RA_REM_AMT +');
2911
2912 x_app_rec := p_app_rec;
2913
2914 ARP_DET_DIST_PKG.get_latest_amount_remaining
2915 (p_customer_trx_id => p_app_rec.applied_customer_trx_id,
2916 p_app_level => p_app_level,
2917 p_source_data_key1=> p_source_data_key1,
2918 p_source_data_key2=> p_source_data_key2,
2919 p_source_data_key3=> p_source_data_key3,
2920 p_source_data_key4=> p_source_data_key4,
2921 p_source_data_key5=> p_source_data_key5,
2922 p_ctl_id => p_ctl_id,
2923 x_line_rem => l_line_rem,
2924 x_tax_rem => l_tax_rem,
2925 x_freight_rem => l_freight_rem,
2926 x_charges_rem => l_chrg_rem,
2927 x_return_status => x_return_status,
2928 x_msg_data => x_msg_data,
2929 x_msg_count => x_msg_count);
2930
2931 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2932 RAISE fnd_api.g_exc_error;
2933 END IF;
2934
2935 l_chrg_app := NVL(p_app_rec.receivables_charges_applied,0);
2936 l_chrg_ed := NVL(p_app_rec.charges_ediscounted,0);
2937 l_chrg_uned := NVL(p_app_rec.charges_uediscounted,0);
2938 l_chrg_entire := l_chrg_app + l_chrg_ed + l_chrg_uned;
2939
2940 l_freight_app := NVL(p_app_rec.freight_applied,0);
2941 l_freight_ed := NVL(p_app_rec.freight_ediscounted,0);
2942 l_freight_uned := NVL(p_app_rec.freight_uediscounted,0);
2943 l_freight_entire := l_freight_app + l_freight_ed + l_freight_uned;
2944
2945 l_tax_app := NVL(p_app_rec.tax_applied,0);
2946 l_tax_ed := NVL(p_app_rec.tax_ediscounted,0);
2947 l_tax_uned := NVL(p_app_rec.tax_uediscounted,0);
2948 l_tax_entire := l_tax_app + l_tax_ed + l_tax_uned;
2949
2950 l_line_app := NVL(p_app_rec.line_applied,0);
2951 l_line_ed := NVL(p_app_rec.line_ediscounted,0);
2952 l_line_uned := NVL(p_app_rec.line_uediscounted,0);
2953 l_line_entire := l_line_app + l_line_ed + l_line_uned;
2954
2955 --
2956 l_entire := l_chrg_entire + l_freight_entire + l_tax_entire + l_line_entire;
2957 l_rem := l_chrg_rem + l_freight_rem + l_tax_rem + l_line_rem;
2958 --
2959
2960 arp_standard.debug(' l_chrg_app :'||l_chrg_app);
2961 arp_standard.debug(' l_chrg_ed :'||l_chrg_ed);
2962 arp_standard.debug(' l_chrg_uned:'||l_chrg_uned);
2963
2964 arp_standard.debug(' l_freight_app :'||l_freight_app);
2965 arp_standard.debug(' l_freight_ed :'||l_freight_ed);
2966 arp_standard.debug(' l_freight_uned:'||l_freight_uned);
2967
2968 arp_standard.debug(' l_tax_app :'||l_tax_app);
2969 arp_standard.debug(' l_tax_ed :'||l_tax_ed);
2970 arp_standard.debug(' l_tax_uned:'||l_tax_uned);
2971
2972 arp_standard.debug(' l_line_app :'||l_line_app);
2973 arp_standard.debug(' l_line_ed :'||l_line_ed);
2974 arp_standard.debug(' l_line_uned:'||l_line_uned);
2975
2976 arp_standard.debug(' Sum of all the bucket of the application :'||l_entire);
2977 arp_standard.debug(' Sum of all the remaining bucket on transaction :'||l_rem);
2978
2979
2980 --
2981 -- We should verify that all rem are less or equal to the all buckets of the application
2982 -- Otherwise this is a abnormal situation as it means overapplication
2983 --
2984 IF l_entire > l_rem THEN
2985 arp_standard.debug(' SUM_ALL_APP_BUCKET > SUM_ALL_REM_BUCKET - Overapplication');
2986 RAISE over_applications;
2987 END IF;
2988
2989 --
2990 --Prorate sum of apps over rem
2991 --
2992 arp_standard.debug(' l_run_rem :'||l_run_rem);
2993 arp_standard.debug(' l_line_rem :'||l_line_rem);
2994
2995 l_run_rem := l_run_rem + l_line_rem;
2996
2997 l_line_apps := arpcurr.CurrRound( l_run_rem
2998 / l_rem
2999 * l_entire,
3000 p_currency)
3001 - l_run_apps;
3002
3003 l_run_apps := l_run_apps + l_line_apps;
3004
3005 arp_standard.debug(' l_line_apps :'||l_line_apps);
3006 arp_standard.debug(' l_run_apps :'||l_run_apps);
3007
3008 --
3009
3010 arp_standard.debug(' l_run_rem :'||l_run_rem);
3011 arp_standard.debug(' l_tax_rem :'||l_tax_rem);
3012
3013 l_run_rem := l_run_rem + l_tax_rem;
3014
3015 l_tax_apps := arpcurr.CurrRound( l_run_rem
3016 / l_rem
3017 * l_entire,
3018 p_currency)
3019 - l_run_apps;
3020
3021
3022 l_run_apps := l_run_apps + l_tax_apps;
3023
3027 --
3024 arp_standard.debug(' l_tax_apps :'||l_tax_apps);
3025 arp_standard.debug(' l_run_apps :'||l_run_apps);
3026
3028 arp_standard.debug(' l_run_rem :'||l_run_rem);
3029 arp_standard.debug(' l_freight_rem :'||l_freight_rem);
3030
3031 l_run_rem := l_run_rem + l_freight_rem;
3032
3033 l_freight_apps := arpcurr.CurrRound( l_run_rem
3034 / l_rem
3035 * l_entire,
3036 p_currency)
3037 - l_run_apps;
3038
3039
3040 l_run_apps := l_run_apps + l_freight_apps;
3041
3042 arp_standard.debug(' l_freight_apps :'||l_freight_apps);
3043 arp_standard.debug(' l_run_apps :'||l_run_apps);
3044 --
3045 arp_standard.debug(' l_run_rem :'||l_run_rem);
3046 arp_standard.debug(' l_chrg_rem :'||l_chrg_rem);
3047
3048 l_run_rem := l_run_rem + l_chrg_rem;
3049
3050 l_chrg_apps := arpcurr.CurrRound( l_run_rem
3051 / l_rem
3052 * l_entire,
3053 p_currency)
3054 - l_run_apps;
3055
3056
3057 l_run_apps := l_run_apps + l_chrg_apps;
3058
3059 arp_standard.debug(' l_chrg_apps :'||l_chrg_apps);
3060 arp_standard.debug(' l_run_apps :'||l_run_apps);
3061
3062 IF l_line_apps <> l_line_entire THEN
3063 l_line_apps := l_line_apps - l_line_ed - l_line_uned;
3064 END IF;
3065
3066 IF l_tax_apps <> l_tax_entire THEN
3067 l_tax_apps := l_tax_apps - l_tax_ed - l_tax_uned;
3068 END IF;
3069
3070 IF l_freight_apps <> l_freight_entire THEN
3071 l_freight_apps := l_freight_apps - l_freight_ed - l_freight_uned;
3072 END IF;
3073
3074 IF l_chrg_apps <> l_chrg_entire THEN
3075 l_chrg_apps := l_chrg_apps - l_chrg_ed - l_chrg_uned;
3076 END IF;
3077
3078 x_app_rec.LINE_APPLIED := l_line_apps;
3079 x_app_rec.TAX_APPLIED := l_tax_apps;
3080 x_app_rec.FREIGHT_APPLIED := l_freight_apps;
3081 x_app_rec.RECEIVABLES_CHARGES_APPLIED:= l_chrg_apps;
3082 arp_standard.debug('COMPARE_RA_REM_AMT -');
3083
3084 EXCEPTION
3085 WHEN over_applications THEN
3086 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3087 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
3088 FND_MESSAGE.SET_TOKEN( 'TEXT', 'EXCEPTION over_applications in ar_upgrade_cash_accrual.COMPARE_RA_REM_AMT
3089 sum amount remaining from the invoice:'||l_rem ||'
3090 sum of application buckets :'||l_entire );
3091 FND_MSG_PUB.ADD;
3092 arp_standard.debug('EXCEPTION fnd_api.g_exc_error - over applications ar_upgrade_cash_accrual.COMPARE_RA_REM_AMT
3093 sum amount remaining from the invoice:'||l_rem ||'
3094 sum of application buckets :'||l_entire );
3095 WHEN fnd_api.g_exc_error THEN
3096 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
3097 FND_MESSAGE.SET_TOKEN( 'TEXT', 'EXCEPTION fnd_api.g_exc_error in ar_upgrade_cash_accrual.COMPARE_RA_REM_AMT' );
3098 FND_MSG_PUB.ADD;
3099 arp_standard.debug('EXCEPTION fnd_api.g_exc_error - fnd_api.g_exc_error ar_upgrade_cash_accrual.COMPARE_RA_REM_AMT');
3100 WHEN OTHERS THEN
3101 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3102 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
3103 FND_MESSAGE.SET_TOKEN( 'TEXT', 'EXCEPTION - OTHERS ar_upgrade_cash_accrual.COMPARE_RA_REM_AMT: '||SQLERRM );
3104 FND_MSG_PUB.ADD;
3105 arp_standard.debug('EXCEPTION - OTHERS ar_upgrade_cash_accrual.COMPARE_RA_REM_AMT: '||SQLERRM);
3106 END COMPARE_RA_REM_AMT;
3107
3108
3109
3110
3111
3112
3113 PROCEDURE stamping_11i_mfar_app_post
3114 IS
3115 BEGIN
3116 arp_standard.debug('stamping_11i_mfar_app_post +');
3117 UPDATE ar_receivable_applications_all ra
3118 SET ra.upgrade_method = 'R12_11IMFAR_POST'
3119 WHERE ra.receivable_application_id IN (
3120 SELECT app.receivable_application_id
3121 FROM xla_events_gt evt,
3122 ar_receivable_applications_all app
3123 WHERE evt.event_type_code IN ('RECP_CREATE' ,'RECP_UPDATE' ,
3124 'RECP_RATE_ADJUST' ,'RECP_REVERSE' ,
3125 'CM_CREATE' ,'CM_UPDATE')
3126 AND evt.event_id = app.event_id
3127 AND app.status = 'APP'
3128 AND app.upgrade_method IS NULL
3129 AND EXISTS (SELECT '1'
3130 FROM ar_adjustments_all adj
3131 WHERE adj.customer_trx_id = app.applied_customer_trx_id
3132 AND adj.upgrade_method = '11IMFAR'
3133 AND adj.status = 'A'
3134 AND adj.postable = 'Y')
3135 MINUS
3136 SELECT app.receivable_application_id
3137 FROM xla_events_gt evt,
3138 ar_receivable_applications_all app
3139 WHERE evt.event_type_code IN ('RECP_CREATE' ,'RECP_UPDATE' ,
3140 'RECP_RATE_ADJUST' ,'RECP_REVERSE' ,
3141 'CM_CREATE' ,'CM_UPDATE')
3142 AND evt.event_id = app.event_id
3146 FROM ar_adjustments_all adj
3143 AND app.status = 'APP'
3144 AND app.upgrade_method IS NULL
3145 AND EXISTS (SELECT '1'
3147 WHERE adj.customer_trx_id = app.applied_customer_trx_id
3148 AND adj.upgrade_method = '11I'
3149 AND adj.status = 'A'
3150 AND adj.postable = 'Y')
3151 );
3152 arp_standard.debug('stamping_11i_mfar_app_post -');
3153 EXCEPTION
3154 WHEN OTHERS THEN
3155 arp_standard.debug('EXCEPTION OTHERS : stamping_11i_mfar_app_post :' || SQLERRM);
3156 RAISE;
3157 END stamping_11i_mfar_app_post;
3158
3159
3160
3161 PROCEDURE stamping_11i_cash_app_post
3162 IS
3163 BEGIN
3164 arp_standard.debug('stamping_11i_cash_app_post +');
3165 UPDATE ar_receivable_applications_all ra
3166 SET ra.upgrade_method = 'R12_11ICASH_POST'
3167 WHERE ra.receivable_application_id IN (
3168 SELECT app.receivable_application_id
3169 FROM xla_events_gt evt,
3170 ar_receivable_applications_all app
3171 WHERE evt.event_type_code IN ( 'RECP_CREATE' ,'RECP_UPDATE' ,
3172 'RECP_RATE_ADJUST' ,'RECP_REVERSE' ,
3173 'CM_CREATE' ,'CM_UPDATE')
3174 AND evt.event_id = app.event_id
3175 AND app.status = 'APP'
3176 AND app.upgrade_method IS NULL
3177 -- AND app.cash_receipt_id = cr.cash_receipt_id(+)
3178 AND EXISTS (SELECT '1'
3179 FROM ar_adjustments_all adj
3180 WHERE adj.customer_trx_id = app.applied_customer_trx_id
3181 AND adj.upgrade_method = '11I'
3182 AND adj.status = 'A'
3183 AND adj.postable = 'Y'));
3184 arp_standard.debug('stamping_11i_cash_app_post -');
3185 EXCEPTION
3186 WHEN OTHERS THEN
3187 arp_standard.debug('EXCEPTION OTHERS : stamping_11i_mfar_app_post :' || SQLERRM);
3188 RAISE;
3189 END stamping_11i_cash_app_post;
3190
3191
3192
3193
3194
3195
3196 ---------------------------------------
3197 -- PROCEDURE portion_to_move
3198 ---------------------------------------
3199 -- Calculate the portion to move from the total to each bucket
3200 -- based on the ratio argument
3201 -- for example:
3202 -- total to move = 15
3203 -- freight ratio = 10
3204 -- tax ratio = 20
3205 -- line ratio = 40
3206 -- chrg ratio = 80
3207 -- ---
3208 -- freight_portion to move = 1
3209 -- tax_portion to move = 2
3210 -- line_portion to move = 4
3211 -- chrg_portion to move = 8
3212 ----------------------------------------
3213 PROCEDURE portion_to_move
3214 (p_total_to_move IN NUMBER,
3215 p_freight_ratio IN NUMBER DEFAULT 0,
3216 p_tax_ratio IN NUMBER DEFAULT 0,
3217 p_line_ratio IN NUMBER DEFAULT 0,
3218 p_chrg_ratio IN NUMBER DEFAULT 0,
3219 p_currency IN VARCHAR2,
3220 x_freight_portion OUT NOCOPY NUMBER,
3221 x_tax_portion OUT NOCOPY NUMBER,
3222 x_line_portion OUT NOCOPY NUMBER,
3223 x_chrg_portion OUT NOCOPY NUMBER)
3224 IS
3225 l_sum_base NUMBER := 0;
3226 l_run_ratio NUMBER := 0;
3227 l_run_portion NUMBER := 0;
3228 l_freight_ratio NUMBER := 0;
3229 l_tax_ratio NUMBER := 0;
3230 l_line_ratio NUMBER := 0;
3231 l_chrg_ratio NUMBER := 0;
3232 l_total_to_move NUMBER := 0;
3233 l_line_portion NUMBER;
3234 l_tax_portion NUMBER;
3235 l_freight_portion NUMBER;
3236 l_chrg_portion NUMBER;
3237 BEGIN
3238 arp_standard.debug(' portion_to_move +');
3239 arp_standard.debug(' p_total_to_move :'|| p_total_to_move);
3240 arp_standard.debug(' p_freight_ratio :'|| p_freight_ratio);
3241 arp_standard.debug(' p_tax_ratio :'|| p_tax_ratio);
3242 arp_standard.debug(' p_line_ratio :'|| p_line_ratio);
3243 arp_standard.debug(' p_chrg_ratio :'|| p_chrg_ratio);
3244
3245 IF (p_total_to_move IS NOT NULL) THEN l_total_to_move := p_total_to_move; END IF;
3246 IF (p_freight_ratio IS NOT NULL) THEN l_freight_ratio := p_freight_ratio; END IF;
3247 IF (p_tax_ratio IS NOT NULL) THEN l_tax_ratio := p_tax_ratio; END IF;
3248 IF (p_line_ratio IS NOT NULL) THEN l_line_ratio := p_line_ratio; END IF;
3249 IF (p_chrg_ratio IS NOT NULL) THEN l_chrg_ratio := p_chrg_ratio; END IF;
3250
3251 l_sum_base := l_freight_ratio + l_tax_ratio + l_line_ratio + l_chrg_ratio;
3252
3253 IF l_total_to_move = 0 THEN
3254 arp_standard.debug('The amount to move is 0, we return 0 for all portion');
3255 x_freight_portion := 0;
3256 x_tax_portion := 0;
3257 x_line_portion := 0;
3258 x_chrg_portion := 0;
3259 ELSIF l_sum_base = 0 THEN
3260 arp_standard.debug('The sum of all ratio is 0, we return 0 for all portion');
3261 x_freight_portion := 0;
3262 x_tax_portion := 0;
3263 x_line_portion := 0;
3264 x_chrg_portion := 0;
3265 ELSE
3266 --
3267 l_run_ratio := l_run_ratio + l_line_ratio;
3268 l_line_portion := arpcurr.CurrRound( l_run_ratio
3269 / l_sum_base
3273 l_run_portion := l_run_portion + l_line_portion;
3270 * l_total_to_move,
3271 p_currency)
3272 - l_run_portion;
3274 --
3275 l_run_ratio := l_run_ratio + l_tax_ratio;
3276 l_tax_portion := arpcurr.CurrRound( l_run_ratio
3277 / l_sum_base
3278 * l_total_to_move,
3279 p_currency)
3280 - l_run_portion;
3281 l_run_portion := l_run_portion + l_tax_portion;
3282 --
3283 l_run_ratio := l_run_ratio + l_freight_ratio;
3284 l_freight_portion := arpcurr.CurrRound( l_run_ratio
3285 / l_sum_base
3286 * l_total_to_move,
3287 p_currency)
3288 - l_run_portion;
3289 l_run_portion := l_run_portion + l_freight_portion;
3290 --
3291 l_run_ratio := l_run_ratio + l_chrg_ratio;
3292 l_chrg_portion := arpcurr.CurrRound( l_run_ratio
3293 / l_sum_base
3294 * l_total_to_move,
3295 p_currency)
3296 - l_run_portion;
3297 l_run_portion := l_run_portion + l_chrg_portion;
3298 --
3299 x_freight_portion := l_freight_portion;
3300 x_tax_portion := l_tax_portion;
3301 x_line_portion := l_line_portion;
3302 x_chrg_portion := l_chrg_portion;
3303 END IF;
3304 EXCEPTION
3305 WHEN OTHERS THEN
3306 arp_standard.debug('EXCEPTION OTHERS - portion_to_move '||SQLERRM);
3307 RAISE;
3308 END;
3309
3310 ---------------------------------------
3311 -- PROCEDURE move_bucket
3312 ---------------------------------------
3313 -- Determine the amount to move and
3314 -- Does the movement of the bucket for bucket originate the movement
3315 -- For example:
3316 -- p_chrg_entire meaning Chrg (ED UNED APP) of an application
3317 -- is greater then the Chrg remaining on the invoice to apply
3318 -- we need to reconcile the surplus amount from the chrg to move
3319 -- to other buckets
3320 --------------
3321 -- Consider we have a surplus of 15 usd of charge to move, so
3322 -- if which bucket = 'CHRG' then 15 usd will be moved to line, tax, freight buckets
3323 -- Consider we have a surplus of 10 usd of freight to move, so
3324 -- if which bucket = 'FREIGHT' then 10 usd will be moved to line, tax buckets
3325 -- Consider we have a surplus of 5 usd of tax to move, so
3326 -- if which bucket = 'TAX' then 5 usd will be moved to line
3327 -- No movement is allowed on LINE bucket the surplus stay in line buckets
3328 ---------------
3329 -- The new entire amount by bucket are returned in x_XXX_entire output argument
3330 ----------------------------------------
3331 PROCEDURE move_bucket
3332 (p_line_entire IN NUMBER,
3333 p_freight_entire IN NUMBER,
3334 p_tax_entire IN NUMBER,
3335 p_chrg_entire IN NUMBER,
3336 --
3337 p_line_rem IN NUMBER,
3338 p_freight_rem IN NUMBER,
3339 p_tax_rem IN NUMBER,
3340 p_chrg_rem IN NUMBER,
3341 --
3342 p_which_bucket IN VARCHAR2,
3343 p_currency IN VARCHAR2,
3344 --
3345 x_line_entire OUT NOCOPY NUMBER,
3346 x_freight_entire OUT NOCOPY NUMBER,
3347 x_tax_entire OUT NOCOPY NUMBER,
3348 x_chrg_entire OUT NOCOPY NUMBER)
3349 IS
3350 --
3351 l_entire NUMBER;
3352 l_rem NUMBER;
3353 --
3354 l_freight_ratio NUMBER;
3355 l_tax_ratio NUMBER;
3356 l_line_ratio NUMBER;
3357 l_chrg_ratio NUMBER;
3358 l_freight_portion NUMBER;
3359 l_tax_portion NUMBER;
3360 l_line_portion NUMBER;
3361 l_chrg_portion NUMBER;
3362 l_to_move NUMBER;
3363 --
3364 l_line_entire NUMBER;
3365 l_freight_entire NUMBER;
3366 l_tax_entire NUMBER;
3367 l_chrg_entire NUMBER;
3368 --
3369 chrg_rem_greater_chrg_app EXCEPTION;
3370 frt_rem_greater_frt_app EXCEPTION;
3371 tax_rem_greater_tax_app EXCEPTION;
3372 --
3373 BEGIN
3374 arp_standard.debug('move_bucket +');
3375
3376 l_line_entire := p_line_entire;
3377 l_freight_entire := p_freight_entire;
3378 l_tax_entire := p_tax_entire;
3379 l_chrg_entire := p_chrg_entire;
3380
3381 IF p_which_bucket = 'CHRG' THEN
3382
3383 l_entire := p_chrg_entire;
3384 l_rem := p_chrg_rem;
3385 --
3386 -- no portion to move for charges
3387 -- the dif between rem_chrg and (APP, ED, UNED Charges) is to be reallocated to line -tax -freight
3388 --
3389 l_freight_ratio := p_freight_entire;
3390 l_tax_ratio := p_tax_entire;
3391 l_line_ratio := p_line_entire;
3392 l_chrg_ratio := 0;
3393
3394 --
3395 -- The charges being applied is greater then the remaining on the transaction
3396 -- This is due to PSA legacy data because the charges adjusted are over all type of line
3400 arp_standard.debug('moving portion charges bucket to other line, tax, freight bucket');
3397 -- hence a portion of remaing charges are affected to other buckets such as freight or tax
3398 --
3399 IF l_entire > l_rem THEN
3401 l_to_move := l_entire - l_rem;
3402 l_chrg_entire := l_entire - l_to_move;
3403 ELSE
3404 RAISE chrg_rem_greater_chrg_app;
3405 END IF;
3406
3407 ELSIF p_which_bucket = 'FREIGHT' THEN
3408
3409 l_entire := p_freight_entire;
3410 l_rem := p_freight_rem;
3411 --
3412 -- no portion to move for freight and charges
3413 -- the dif between rem_chrg and (APP, ED, UNED Charges) is to be reallocated to line -tax
3414 --
3415 l_freight_ratio := 0;
3416 l_tax_ratio := p_tax_entire;
3417 l_line_ratio := p_line_entire;
3418 l_chrg_ratio := 0;
3419
3420 IF l_entire > l_rem THEN
3421 arp_standard.debug('moving portion freight bucket to line and tax');
3422 l_to_move := l_entire - l_rem;
3423 l_freight_entire := l_entire - l_to_move;
3424 ELSE
3425 RAISE frt_rem_greater_frt_app;
3426 END IF;
3427
3428 ELSIF p_which_bucket = 'TAX' THEN
3429
3430 l_entire := p_tax_entire;
3431 l_rem := p_tax_rem;
3432 --
3433 -- no portion to move for tax, freight and charges
3434 -- the dif between rem_chrg and (APP, ED, UNED tax) is to be reallocated to line
3435 --
3436 l_freight_ratio := 0;
3437 l_tax_ratio := 0;
3438 l_line_ratio := p_line_entire;
3439 l_chrg_ratio := 0;
3440
3441 IF l_entire > l_rem THEN
3442 arp_standard.debug('moving portion tax bucket to other line');
3443 l_to_move := l_entire - l_rem;
3444 l_tax_entire := l_entire - l_to_move;
3445 ELSE
3446 RAISE tax_rem_greater_tax_app;
3447 END IF;
3448
3449 ELSIF p_which_bucket = 'LINE' THEN
3450
3451 -- This code do not need to be executed for line
3452 NULL;
3453
3454 ELSE
3455
3456 x_line_entire := p_line_entire;
3457 x_freight_entire := p_freight_entire;
3458 x_tax_entire := p_tax_entire;
3459 x_chrg_entire := p_chrg_entire;
3460
3461 END IF;
3462
3463 IF l_entire > l_rem THEN
3464
3465 -- logic of charges movement.
3466 -- Move charge equivalently over line - tax - freight
3467 arp_standard.debug(' '||p_which_bucket||' to move : '|| l_to_move );
3468
3469 portion_to_move
3470 (p_total_to_move => l_to_move,
3471 p_freight_ratio => l_freight_ratio,
3472 p_tax_ratio => l_tax_ratio,
3473 p_line_ratio => l_line_ratio,
3474 p_chrg_ratio => l_chrg_ratio,
3475 p_currency => p_currency,
3476 x_freight_portion => l_freight_portion,
3477 x_tax_portion => l_tax_portion,
3478 x_line_portion => l_line_portion,
3479 x_chrg_portion => l_chrg_portion);
3480
3481 x_line_entire := l_line_entire + l_line_portion;
3482 x_freight_entire := l_freight_entire + l_freight_portion;
3483 x_tax_entire := l_tax_entire + l_tax_portion;
3484 x_chrg_entire := l_chrg_entire + l_chrg_portion;
3485
3486 END IF;
3487 arp_standard.debug(' x_line_entire :'|| x_line_entire);
3488 arp_standard.debug(' x_freight_entire :'|| x_freight_entire);
3489 arp_standard.debug(' x_tax_entire :'|| x_tax_entire);
3490 arp_standard.debug(' x_chrg_entire :'|| x_chrg_entire);
3491 arp_standard.debug('move_bucket -');
3492 EXCEPTION
3493 WHEN chrg_rem_greater_chrg_app THEN
3494 x_line_entire := p_line_entire;
3495 x_freight_entire := p_freight_entire;
3496 x_tax_entire := p_tax_entire;
3497 x_chrg_entire := p_chrg_entire;
3498 arp_standard.debug(' USER EXCEPTION chrg_rem_greater_chrg_app');
3499 arp_standard.debug(' x_line_entire :'|| x_line_entire);
3500 arp_standard.debug(' x_freight_entire :'|| x_freight_entire);
3501 arp_standard.debug(' x_tax_entire :'|| x_tax_entire);
3502 arp_standard.debug(' x_chrg_entire :'|| x_chrg_entire);
3503 arp_standard.debug('move_bucket -');
3504 WHEN frt_rem_greater_frt_app THEN
3505 x_line_entire := p_line_entire;
3506 x_freight_entire := p_freight_entire;
3507 x_tax_entire := p_tax_entire;
3508 x_chrg_entire := p_chrg_entire;
3509 arp_standard.debug(' USER EXCEPTION frt_rem_greater_frt_app');
3510 arp_standard.debug(' x_line_entire :'|| x_line_entire);
3511 arp_standard.debug(' x_freight_entire :'|| x_freight_entire);
3512 arp_standard.debug(' x_tax_entire :'|| x_tax_entire);
3513 arp_standard.debug(' x_chrg_entire :'|| x_chrg_entire);
3514 arp_standard.debug('move_bucket -');
3515 WHEN tax_rem_greater_tax_app THEN
3516 x_line_entire := p_line_entire;
3517 x_freight_entire := p_freight_entire;
3518 x_tax_entire := p_tax_entire;
3519 x_chrg_entire := p_chrg_entire;
3520 arp_standard.debug(' USER EXCEPTION tax_rem_greater_tax_app');
3521 arp_standard.debug(' x_line_entire :'|| x_line_entire);
3522 arp_standard.debug(' x_freight_entire :'|| x_freight_entire);
3526 WHEN OTHERS THEN
3523 arp_standard.debug(' x_tax_entire :'|| x_tax_entire);
3524 arp_standard.debug(' x_chrg_entire :'|| x_chrg_entire);
3525 arp_standard.debug('move_bucket -');
3527 RAISE;
3528 END;
3529
3530 ---------------------------------------
3531 -- PROCEDURE lease_app_bucket_amts
3532 ---------------------------------------
3533 -- This a wrapper which will lease the entire application amt buckets
3534 -- based on the remaining of the transaction
3535 --------------
3536 -- For example :
3537 -- The application has
3538 -- ED + UNED + APP for line - x_line_entire => 100
3539 -- ED + UNED + APP for freight - x_freight_entire=> 30
3540 -- ED + UNED + APP for tax - x_tax_entire => 16
3541 -- ED + UNED + APP for chrg - x_chrg_entire => 6
3542 --------------
3543 -- The transaction has remaining
3544 -- on line p_line_rem => 200
3545 -- on freight p_freight_rem => 30
3546 -- on tax p_tax_rem => 15
3547 -- on charges p_chrg_rem => 3
3548 ----------------
3549 -- sum all rem > sum all entire buckets ==> no over applications - OK
3550 -- The result will be
3551 -- x_line_entire => 104
3552 -- x_freight_entire => 30
3553 -- x_tax_entire => 15
3554 -- x_chrg_entire => 3
3555 -- Note in this example the surplus from tax and charges are absorbed by line buckets
3556 ----------------------------------------
3557 PROCEDURE lease_app_bucket_amts
3558 (p_line_rem IN NUMBER,
3559 p_tax_rem IN NUMBER,
3560 p_freight_rem IN NUMBER,
3561 p_chrg_rem IN NUMBER,
3562 --
3563 p_currency IN VARCHAR2,
3564 --
3565 x_line_entire IN OUT NOCOPY NUMBER,
3566 x_tax_entire IN OUT NOCOPY NUMBER,
3567 x_freight_entire IN OUT NOCOPY NUMBER,
3568 x_chrg_entire IN OUT NOCOPY NUMBER)
3569 IS
3570 l_app NUMBER;
3571 l_rem NUMBER;
3572 l_cur_line NUMBER;
3573 l_cur_tax NUMBER;
3574 l_cur_freight NUMBER;
3575 l_cur_chrg NUMBER;
3576 l_tmp_line NUMBER;
3577 l_tmp_tax NUMBER;
3578 l_tmp_freight NUMBER;
3579 l_tmp_chrg NUMBER;
3580 l_line_rem NUMBER;
3581 l_tax_rem NUMBER;
3582 l_freight_rem NUMBER;
3583 l_chrg_rem NUMBER;
3584 l_bucket VARCHAR2(30);
3585 i NUMBER := 0;
3586 over_applications EXCEPTION;
3587
3588 BEGIN
3589 arp_standard.debug('lease_app_bucket_amts +');
3590 arp_standard.debug(' p_line_rem :'|| p_line_rem);
3591 arp_standard.debug(' p_tax_rem :'|| p_tax_rem);
3592 arp_standard.debug(' p_freight_rem:'|| p_freight_rem);
3593 arp_standard.debug(' p_chrg_rem :'|| p_chrg_rem);
3594 arp_standard.debug(' -----------------------------');
3595
3596 l_cur_line := NVL(x_line_entire,0);
3597 l_cur_tax := NVL(x_tax_entire,0);
3598 l_cur_freight := NVL(x_freight_entire,0);
3599 l_cur_chrg := NVL(x_chrg_entire,0);
3600
3601 l_line_rem := NVL(p_line_rem,0);
3602 l_tax_rem := NVL(p_tax_rem,0);
3603 l_freight_rem := NVL(p_freight_rem,0);
3604 l_chrg_rem := NVL(p_chrg_rem,0);
3605
3606 l_app := l_cur_line + l_cur_tax + l_cur_freight + l_cur_chrg;
3607 l_rem := l_line_rem + l_tax_rem + l_freight_rem + l_chrg_rem;
3608
3609 IF l_app > l_rem THEN
3610 RAISE over_applications;
3611 END IF;
3612
3613 LOOP
3614
3615 i := i + 1;
3616
3617 IF i = 1 THEN l_bucket := 'CHRG';
3618 ELSIF i = 2 THEN l_bucket := 'FREIGHT';
3619 ELSIF i = 3 THEN l_bucket := 'TAX';
3620 ELSE l_bucket := 'LINE';
3621 END IF;
3622
3623 EXIT WHEN l_bucket = 'LINE';
3624
3625
3626 move_bucket
3627 (p_line_entire => l_cur_line,
3628 p_freight_entire => l_cur_freight,
3629 p_tax_entire => l_cur_tax,
3630 p_chrg_entire => l_cur_chrg,
3631 --
3632 p_line_rem => l_line_rem,
3633 p_freight_rem => l_freight_rem,
3634 p_tax_rem => l_tax_rem,
3635 p_chrg_rem => l_chrg_rem,
3636 --
3637 p_which_bucket => l_bucket,
3638 p_currency => p_currency,
3639 --
3640 x_line_entire => l_tmp_line,
3641 x_freight_entire => l_tmp_freight,
3642 x_tax_entire => l_tmp_tax,
3643 x_chrg_entire => l_tmp_chrg);
3644
3645 l_cur_line := l_tmp_line;
3646 l_cur_freight := l_tmp_freight;
3647 l_cur_tax := l_tmp_tax;
3648 l_cur_chrg := l_tmp_chrg;
3649
3650 END LOOP;
3651
3652 x_line_entire := l_cur_line;
3653 x_tax_entire := l_cur_tax;
3654 x_freight_entire := l_cur_freight;
3655 x_chrg_entire := l_cur_chrg;
3656 arp_standard.debug(' x_line_app :'|| l_cur_line);
3657 arp_standard.debug(' x_tax_app :'|| l_cur_tax);
3658 arp_standard.debug(' x_freight_app :'|| l_cur_freight);
3659 arp_standard.debug(' x_chrg_app :'|| l_cur_chrg);
3660 arp_standard.debug('lease_app_bucket_amts -');
3661
3662 EXCEPTION
3663 WHEN over_applications THEN
3664 arp_standard.debug('EXCEPTION fnd_api.g_exc_error - over applications ar_upgrade_cash_accrual.COMPARE_RA_REM_AMT
3665 sum amount remaining from the invoice:'||l_rem ||'
3666 sum of application buckets :'||l_app );
3667
3668 END;
3669
3670 END;