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