1 PACKAGE BODY ARP_MISC_CASH_DIST AS
2 /* $Header: ARREMCDB.pls 120.18 2006/11/24 08:39:16 susivara ship $ */
3
4 /* declare subtype */
5
6 SUBTYPE l_ae_doc_rec_type IS arp_acct_main.ae_doc_rec_type ;
7 --
8 -- new type defined for 1543658
9 TYPE ard_tbl_type IS TABLE of ar_distributions%ROWTYPE
10 INDEX BY BINARY_INTEGER;
11 ard_tbl_tbl ard_tbl_type; --for 1543658
12
13 TYPE mcd_tbl_type IS TABLE of ar_misc_cash_distributions%ROWTYPE
14 INDEX BY BINARY_INTEGER;
15 mcd_tbl_tbl mcd_tbl_type; --for 1543658
16
17
18 /* ---------------------- Public functions -------------------------------- */
19
20
21 /*===========================================================================+
22 | PROCEDURE |
23 | delete_mcd_rec |
24 | |
25 | DESCRIPTION |
26 | Deletes a miscellaneous distribution record from |
27 | ar_misc_cash_distributions. |
28 | |
29 | SCOPE - PRIVATE |
30 | |
31 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
32 | |
33 | ARGUMENTS |
34 | IN: |
35 | OUT: |
36 | |
37 | RETURNS |
38 | |
39 | NOTES |
40 | |
41 | MODIFICATION HISTORY |
42 | |
43 | 28-SEP-1995 OSTEINME created |
44 | 18-JAN-2001 ANUJ Modified |
45 | |
46 +===========================================================================*/
47
48
49
50 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
51
52 PROCEDURE delete_mcd_rec(
53 p_mcd_id IN
54 ar_misc_cash_distributions.misc_cash_distribution_id%TYPE,
55 p_form_name IN varchar2,
56 p_form_version IN varchar2
57 ) IS
58 l_ard_line_id ar_distributions.line_id%TYPE;-- for 1543658
59 l_ard_rec ar_distributions%ROWTYPE;-- for 1543658
60 l_ard_tax_source_id ar_distributions.source_id%TYPE;-- for 1543658
61 BEGIN
62
63 -- arp_standard.enable_debug;
64 IF PG_DEBUG in ('Y', 'C') THEN
65 arp_standard.debug('arp_process_receipts.delete_mcd_rec()+');
66 END IF;
67
68 -- check if calling form is compatible with entity handler
69
70 -- Call table handler for ar_misc_cash_distributions to delete record.
71 -- for 1543658 new block is written,
72 -- when a MCD record is deleted, the line for AR DISTRIBUTION is also
73 -- deleted, but the TAX line in AR DISTRIBUTION is untouched
74 -- we insure that line is not a TAX line by source_type ='MISCCASH'
75 -- bec'z TAX line has same source_id as first AR DISTRIBUTION line
76 -- begin 1543658
77 --bug5655154, commented the accounting_method='ACCRUAL' check
78 --begin 1813186
79 -- if arp_global.sysparam.accounting_method = 'ACCRUAL' then
80 --end 1813186
81 BEGIN
82 SELECT line_id into l_ard_line_id
83 FROM ar_distributions
84 WHERE source_id = p_mcd_id AND
85 source_table = 'MCD' AND
86 source_type ='MISCCASH' ;
87 ARP_DISTRIBUTIONS_PKG.delete_p(l_ard_line_id);
88
89 BEGIN
90 SELECT * into l_ard_rec
91 FROM ar_distributions
92 WHERE source_id = p_mcd_id AND
93 source_table = 'MCD' AND
94 source_type ='TAX' AND
95 source_id in (select misc_cash_distribution_id
96 from ar_misc_cash_distributions
97 where cash_receipt_id = ar_distributions.source_id_secondary
98 and reversal_gl_date is null);
99
100 select nvl(min(source_id),0) into l_ard_tax_source_id
101 FROM ar_distributions
102 WHERE source_table = 'MCD' AND
103 source_type ='MISCCASH' AND
104 source_id in (select misc_cash_distribution_id
105 from ar_misc_cash_distributions
106 where cash_receipt_id = l_ard_rec.source_id_secondary
107 and reversal_gl_date is null);
108 l_ard_rec.source_id := l_ard_tax_source_id;
109 ARP_DISTRIBUTIONS_PKG.update_p(l_ard_rec);
110
111
112 EXCEPTION
113 WHEN no_data_found then
114 IF PG_DEBUG in ('Y', 'C') THEN
115 arp_standard.debug('delete_mcd_rec: ' || 'Distribution TAX line does not exists');
116 END IF;
117 END;
118 -- Table handler for AR DISTRIBUTION record
119 EXCEPTION
120 WHEN no_data_found then
121 IF PG_DEBUG in ('Y', 'C') THEN
122 arp_standard.debug('delete_mcd_rec: ' || 'Distribution line does not exists');
123 END IF;
124
125 END;
126 -- end 1543658
127 --begin 1813186
128 -- end if; -- bug5655154
129 --end 1813186
130
131 arp_misc_cash_dist_pkg.delete_p(p_mcd_id);
132 --Table handler for MCD record
133
134
135 IF PG_DEBUG in ('Y', 'C') THEN
136 arp_standard.debug('arp_process_receipts.delete_mcd_rec()-');
137 END IF;
138
139 END delete_mcd_rec;
140
141
142 /*===========================================================================+
143 | PROCEDURE |
144 | insert_mcd_rec |
145 | |
146 | DESCRIPTION |
147 | Inserts a misc cash distribution record into ar_misc_cash_distributions|
148 | |
149 | SCOPE - PRIVATE |
150 | |
151 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
152 | |
153 | ARGUMENTS |
154 | IN: |
155 | OUT: |
156 | |
157 | RETURNS |
158 | |
159 | NOTES |
160 | |
161 | MODIFICATION HISTORY |
162 | |
163 | 28-SEP-1995 OSTEINME created |
164 | 18-JAN-2001 ANUJ Modified |
165 | |
166 +===========================================================================*/
167
168 PROCEDURE insert_mcd_rec(
169 p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
170 p_gl_date IN ar_misc_cash_distributions.gl_date%TYPE,
171 p_percent IN ar_misc_cash_distributions.percent%TYPE,
172 p_amount IN ar_misc_cash_distributions.amount%TYPE,
173 p_comments IN ar_misc_cash_distributions.comments%TYPE,
174 p_apply_date IN ar_misc_cash_distributions.apply_date%TYPE,
175 p_code_combination_id IN ar_misc_cash_distributions.code_combination_id%TYPE,
176 p_attribute_category IN ar_misc_cash_distributions.attribute_category%TYPE,
177 p_attribute1 IN ar_misc_cash_distributions.attribute1%TYPE,
178 p_attribute2 IN ar_misc_cash_distributions.attribute2%TYPE,
179 p_attribute3 IN ar_misc_cash_distributions.attribute3%TYPE,
180 p_attribute4 IN ar_misc_cash_distributions.attribute4%TYPE,
181 p_attribute5 IN ar_misc_cash_distributions.attribute5%TYPE,
182 p_attribute6 IN ar_misc_cash_distributions.attribute6%TYPE,
183 p_attribute7 IN ar_misc_cash_distributions.attribute7%TYPE,
184 p_attribute8 IN ar_misc_cash_distributions.attribute8%TYPE,
185 p_attribute9 IN ar_misc_cash_distributions.attribute9%TYPE,
186 p_attribute10 IN ar_misc_cash_distributions.attribute10%TYPE,
187 p_attribute11 IN ar_misc_cash_distributions.attribute11%TYPE,
188 p_attribute12 IN ar_misc_cash_distributions.attribute12%TYPE,
189 p_attribute13 IN ar_misc_cash_distributions.attribute13%TYPE,
190 p_attribute14 IN ar_misc_cash_distributions.attribute14%TYPE,
191 p_attribute15 IN ar_misc_cash_distributions.attribute15%TYPE,
192 p_acctd_amount IN ar_misc_cash_distributions.acctd_amount%TYPE,
193 p_ussgl_tc IN ar_misc_cash_distributions.ussgl_transaction_code%TYPE,
194 p_mcd_id OUT NOCOPY ar_misc_cash_distributions.misc_cash_distribution_id%TYPE,
195 p_form_name IN varchar2,
196 p_form_version IN varchar2,
197 p_amount_ard IN ar_distributions.amount_dr%TYPE,--for 1543658
198 p_acctd_amount_ard IN ar_distributions.acctd_amount_dr%TYPE --for 1543658
199 ) IS
200 l_mcd_rec ar_misc_cash_distributions%ROWTYPE;
201 l_mcd_id ar_misc_cash_distributions.misc_cash_distribution_id%TYPE;
202 l_ae_doc_rec l_ae_doc_rec_type;
203 l_ard_line_id ar_distributions.line_id%TYPE;
204 l_ard_rec ar_distributions%ROWTYPE;
205 l_ard_tax_rec ar_distributions%ROWTYPE;
206 l_ard_tax_rec_flag char(1);
207 l_ard_chk_first_rec char(1);
208 l_cr_rec ar_cash_receipts%ROWTYPE;
209 l_tax_account_id ar_vat_tax.tax_account_id%TYPE; -- code_combination_id for tax
210 l_vat_tax_id ar_vat_tax.vat_tax_id%TYPE; -- tax_code_id
211 ard_tbl_ctr number := 0; --counter to store AR Distribution record
212 -- in plsql Table
213 -- for 1543658
214 -- this cursor stores all AR Distributions lines except the one
215 -- which is processed presently
216 -- and which is not a Tax line for the present receipt
217 CURSOR cr_ard_cur
218 (p_mcd_id in ar_misc_cash_distributions.misc_cash_distribution_id%TYPE,
219 p_cash_receipt_id in ar_cash_receipts.cash_receipt_id%TYPE)
220 IS
221 SELECT *
222 FROM ar_distributions
223 WHERE source_table = 'MCD' AND
224 source_type ='MISCCASH' and
225 source_id in (select misc_cash_distribution_id
226 from ar_misc_cash_distributions
227 where cash_receipt_id = p_cash_receipt_id and
228 misc_cash_distribution_id <> p_mcd_id
229 and reversal_gl_date is null );
230
231 l_min_unit NUMBER;
232 l_precision NUMBER;
233 l_ard_cnt NUMBER;
234 update_flag CHAR(1);
235
236 l_ar_dist_key_value_list gl_ca_utility_pkg.r_key_value_arr; /* MRC */
237 /* Bug fix 2827019 */
238 l_percent_total NUMBER;
239 l_amount_total NUMBER;
240
241
242 --Bug#2750340
243 l_xla_ev_rec arp_xla_events.xla_events_type;
244 l_xla_doc_table VARCHAR2(20);
245
246 BEGIN
247
248 -- arp_standard.enable_debug;
249 IF PG_DEBUG in ('Y', 'C') THEN
250 arp_standard.debug('arp_process_receipts.insert_mcd_rec()+');
251 END IF;
252 --bug5655154, commented accounting_method ='ACCRUAL'
253 --begin 1813186
254 -- if arp_global.sysparam.accounting_method = 'ACCRUAL' then
255 --end 1813186
256
257
258 -- check if calling form is compatible with entity handler
259
260 -- ??????
261
262 --begin 1543658
263 -- this select will fetch some important information
264 -- which we need to pass in the table handler
265 -- of AR Distributions
266 select cr.cash_receipt_id ,
267 cr.amount ,
268 cr.vat_tax_id ,
269 cr.tax_rate ,
270 cr.currency_code ,
271 cr.exchange_rate ,
272 cr.exchange_rate_type ,
273 cr.exchange_date ,
274 cr.pay_from_customer , --third_party_id
275 cr.customer_site_use_id , --third_party_sub_id
276 avt.tax_account_id ,
277 avt.vat_tax_id ,
278 fc.precision ,
279 fc.minimum_accountable_unit
280 into l_cr_rec.cash_receipt_id ,
281 l_cr_rec.amount ,
282 l_cr_rec.vat_tax_id ,
283 l_cr_rec.tax_rate ,
284 l_cr_rec.currency_code ,
285 l_cr_rec.exchange_rate ,
286 l_cr_rec.exchange_rate_type ,
287 l_cr_rec.exchange_date ,
288 l_cr_rec.pay_from_customer , --third_party_id
289 l_cr_rec.customer_site_use_id , --third_party_sub_id
290 l_tax_account_id , --code_combination_id for tax
291 l_vat_tax_id , --tax_code_id
292 l_precision ,
293 l_min_unit
294 from ar_cash_receipts cr,
295 ar_vat_tax avt,
296 fnd_currencies fc
297 where cr.cash_receipt_id = p_cash_receipt_id
298 and cr.currency_code = fc.currency_code
299 and cr.vat_tax_id = avt.vat_tax_id(+);
300
301 IF (l_vat_tax_id is not null and l_cr_rec.amount <> 0 ) then /* Bug fix 2874047 : and condition added*/
302 if (l_min_unit is null ) then
303 l_mcd_rec.amount := round(l_cr_rec.amount* p_percent/100,l_precision);
304
305 else
306 l_mcd_rec.amount := round(l_cr_rec.amount* (p_percent/100)/l_min_unit)*l_min_unit;
307 end if;
308
309 if (arp_global.base_min_acc_unit is null) then
310 l_mcd_rec.acctd_amount := round((l_cr_rec.amount * p_percent/100) * nvl(l_cr_rec.exchange_rate ,1),
311 arp_global.base_precision);
312 else
313 l_mcd_rec.acctd_amount := round(l_cr_rec.amount* (p_percent/100) * nvl(l_cr_rec.exchange_rate ,1)
314 / arp_global.base_precision) * arp_global.base_precision;
315 end if;
316 IF PG_DEBUG in ('Y', 'C') THEN
317 arp_standard.debug( 'l_mcd_rec.amount :='||to_char(l_mcd_rec.amount));
318 arp_standard.debug( 'l_mcd_rec.acctd_amount:='||
319 to_char(l_mcd_rec.acctd_amount));
320 END IF;
321 else
322 l_mcd_rec.amount := p_amount;
323 l_mcd_rec.acctd_amount := p_acctd_amount;
324 end if;
325
326 --end 1543658
327
328
329 /* Bug fix 2827019 */
330 arp_standard.debug('p_percent = '||to_char(p_percent));
331 SELECT sum(amount), sum(percent)
332 INTO l_amount_total, l_percent_total
333 FROM ar_misc_cash_distributions
334 WHERE cash_receipt_id = p_cash_receipt_id
335 AND reversal_gl_date IS NULL;
336 arp_standard.debug('l_amount_total = '||to_char(l_amount_total));
337 arp_standard.debug('l_percent_total = '||to_char(l_percent_total));
338
339 IF l_amount_total = l_cr_rec.amount - l_mcd_rec.amount
340 AND l_cr_rec.amount <> 0 THEN
341 arp_standard.debug('Calculate percent = 100-total percent');
342 l_mcd_rec.percent := 100 - l_percent_total;
343 ELSE
344 arp_standard.debug('Calculate percent from p_percent');
345 l_mcd_rec.percent := round(p_percent,3);
346 END IF;
347 arp_standard.debug('After rounding, p_percent = '||to_char(l_mcd_rec.percent));
348 /* end bug fix 2827019*/
349
350 -- create mcd record
351
352 l_mcd_rec.cash_receipt_id := p_cash_receipt_id;
353 l_mcd_rec.gl_date := p_gl_date;
354 -- l_mcd_rec.percent := p_percent; /* Bug fix 2827019 */
355 -- l_mcd_rec.amount := p_amount;
356 l_mcd_rec.comments := p_comments;
357 l_mcd_rec.apply_date := p_apply_date;
358 l_mcd_rec.attribute_category := p_attribute_category;
359 l_mcd_rec.attribute1 := p_attribute1;
360 l_mcd_rec.attribute2 := p_attribute2;
361 l_mcd_rec.attribute3 := p_attribute3;
362 l_mcd_rec.attribute4 := p_attribute4;
363 l_mcd_rec.attribute5 := p_attribute5;
364 l_mcd_rec.attribute6 := p_attribute6;
365 l_mcd_rec.attribute7 := p_attribute7;
366 l_mcd_rec.attribute8 := p_attribute8;
367 l_mcd_rec.attribute9 := p_attribute9;
368 l_mcd_rec.attribute10 := p_attribute10;
369 l_mcd_rec.attribute11 := p_attribute11;
370 l_mcd_rec.attribute12 := p_attribute12;
371 l_mcd_rec.attribute13 := p_attribute13;
372 l_mcd_rec.attribute14 := p_attribute14;
373 l_mcd_rec.attribute15 := p_attribute15;
374 -- l_mcd_rec.acctd_amount := p_acctd_amount;
375 l_mcd_rec.ussgl_transaction_code := p_ussgl_tc;
376 l_mcd_rec.posting_control_id := -3; -- not posted;
377 l_mcd_rec.set_of_books_id := arp_global.set_of_books_id;
378 l_mcd_rec.code_combination_id := p_code_combination_id;
379 l_mcd_rec.created_from := 'ARRERCT';
380
381
382 -- Call table handler for ar_misc_cash_distributions to insert record.
383
384 arp_misc_cash_dist_pkg.insert_p(l_mcd_rec, l_mcd_id);
385
386
387 --begin for 1543654
388
389 l_ard_rec.source_id := l_mcd_id;
390 l_ard_rec.source_table := 'MCD';
391 l_ard_rec.source_type := 'MISCCASH';
392 -- It is 'MISCCASH' for line and
393 --'TAX' for Tax line
394 l_ard_rec.source_type_secondary := '';
395 l_ard_rec.code_combination_id := p_code_combination_id;
396 l_ard_rec.source_id_secondary := l_cr_rec.cash_receipt_id;
397 l_ard_rec.source_table_secondary := 'CR';
398 l_ard_rec.currency_code := l_cr_rec.currency_code;
399 l_ard_rec.currency_conversion_rate := l_cr_rec.exchange_rate;
400 l_ard_rec.currency_conversion_type := l_cr_rec.exchange_rate_type;
401 l_ard_rec.currency_conversion_date := l_cr_rec.exchange_date;
402 l_ard_rec.third_party_id := l_cr_rec.pay_from_customer;
403 l_ard_rec.third_party_sub_id := l_cr_rec.customer_site_use_id;
404
405 IF sign(p_amount_ard) = -1 THEN -- Debits for Ar distribution
406
407 l_ard_rec.amount_dr := abs(p_amount_ard);
408 if (arp_global.base_min_acc_unit is null) then
409 l_ard_rec.acctd_amount_dr := round(abs(p_amount_ard) *nvl(l_cr_rec.exchange_rate,1),
410 arp_global.base_precision);
411
412 else
413 l_ard_rec.acctd_amount_dr := round(abs(p_amount_ard)*nvl(l_cr_rec.exchange_rate,1)
414 /arp_global.base_precision)*arp_global.base_precision;
415 end if;
416
417
418 l_ard_rec.amount_cr := NULL;
419 l_ard_rec.acctd_amount_cr := NULL;
420
421 ELSE -- Credits for Ar distribution
422
423 l_ard_rec.amount_cr := p_amount_ard;
424 if (arp_global.base_min_acc_unit is null) then
425 l_ard_rec.acctd_amount_cr := round(abs(p_amount_ard) *nvl(l_cr_rec.exchange_rate,1),
426 arp_global.base_precision);
427
428 else
429 l_ard_rec.acctd_amount_cr := round(abs(p_amount_ard)*nvl(l_cr_rec.exchange_rate,1)
430 /arp_global.base_precision)*arp_global.base_precision;
431 end if;
432 l_ard_rec.amount_dr := NULL;
433 l_ard_rec.acctd_amount_dr := NULL;
434
435 END IF;
436
437 -- end for 1543654
438
439
440
441 -- Call accounting entry library begins
442
443 IF PG_DEBUG in ('Y', 'C') THEN
444 arp_standard.debug( 'Insert Misc Cash Receipt Distribution start');
445 END IF;
446
447 l_ae_doc_rec.document_type := 'RECEIPT';
448 l_ae_doc_rec.document_id := l_mcd_rec.cash_receipt_id;
449 l_ae_doc_rec.accounting_entity_level := 'ONE';
450 l_ae_doc_rec.source_table := 'MCD';
451 l_ae_doc_rec.source_id := '';
452
453
454 -- begin for 1543658
455 -- Instead of calling accounting engine to insert lines in
456 -- AR Distribution table, we are simultaneously inserting line using
457 -- table handler, side by side of MCD record.
458 -- We presevre all other lines in AR Distribution table except the one
459 -- on which we perform any modification or insertion
460 -- In the case of delete, we delete line from AR Distribution table
461 -- simultaneously when we delete from MCD.
462 -- During Insert and update of Distribution, AR Distribution lines for the
463 -- the partcular misc receipt are conserverd in plsql table
464 -- then record are deleted. Later new lines are entered in AR Distribution
465 -- appending the new changes, using Plsql table record , tax record
466 -- and current record.
467
468
469 BEGIN
470 -- to select the tax record of AR distribution in local variable
471 -- which will be deleted by arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
472 -- In the next call storing it back by
473 -- "procedure ARP_DISTRIBUTIONS_PKG.insert_p(l_ard_tax_rec,l_ard_line_id);"
474 select * into l_ard_tax_rec
475 from ar_distributions
476 where source_type ='TAX' and
477 source_table ='MCD' and
478 source_type_secondary = 'MISCCASH' and
479 source_id in (select misc_cash_distribution_id
480 from ar_misc_cash_distributions
481 where cash_receipt_id = l_cr_rec.cash_receipt_id
482 and reversal_gl_date is null
483 UNION
484 select 0 from dual);
485
486
487 if (arp_global.base_min_acc_unit is null) then
488 l_ard_tax_rec.acctd_amount_dr := round(l_ard_tax_rec.amount_dr
489 *nvl(l_cr_rec.exchange_rate,1),
490 arp_global.base_precision);
491
492 l_ard_tax_rec.acctd_amount_cr:= round(l_ard_tax_rec.amount_cr
493 *nvl(l_cr_rec.exchange_rate,1),
494 arp_global.base_precision);
495 else
496 l_ard_tax_rec.acctd_amount_dr := round(l_ard_tax_rec.amount_dr
497 *nvl(l_cr_rec.exchange_rate,1)
498 /arp_global.base_precision)
499 *arp_global.base_precision;
500
501 l_ard_tax_rec.acctd_amount_cr := round(l_ard_tax_rec.amount_cr
502 *nvl(l_cr_rec.exchange_rate,1)
503 /arp_global.base_precision)
504 *arp_global.base_precision;
505
506 end if;
507 DELETE FROM ar_distributions
508 WHERE source_table = 'MCD' AND
509 source_type ='TAX' and
510 source_id = 0
511 RETURNING line_id
512 BULK COLLECT into l_ar_dist_key_value_list;
513
514
515 l_ard_tax_rec_flag:='Y'; -- flag to check if we have tax record
516 EXCEPTION
517 WHEN NO_DATA_FOUND THEN
518 IF PG_DEBUG in ('Y', 'C') THEN
519 arp_standard.debug( 'NO INSERT, TAX IS STORED WITH FIRST misc_cash_distribution_id');
520 END IF;
521 l_ard_tax_rec_flag:='N';
522
523 END;
524
525
526 -- this loop stores all AR Distributions lines except the one
527 -- which is processed presently and which is not a Tax line for
528 -- the present receipt in a plsql table
529 update_flag:='N';
530 FOR cr_ard_rec IN cr_ard_cur(l_mcd_id,l_cr_rec.cash_receipt_id) LOOP
531 ard_tbl_ctr := ard_tbl_ctr + 1;
532 if (arp_global.base_min_acc_unit is null) then
533 cr_ard_rec.acctd_amount_dr := round(cr_ard_rec.amount_dr*
534 nvl(l_cr_rec.exchange_rate,1),
535 arp_global.base_precision);
536
537 cr_ard_rec.acctd_amount_cr := round(cr_ard_rec.amount_cr
538 *nvl(l_cr_rec.exchange_rate,1),
539 arp_global.base_precision);
540 else
541 cr_ard_rec.acctd_amount_dr := round(cr_ard_rec.amount_dr
542 *nvl(l_cr_rec.exchange_rate,1)
543 /arp_global.base_precision)
544 *arp_global.base_precision;
545 cr_ard_rec.acctd_amount_cr := round(cr_ard_rec.amount_cr*
546 nvl(l_cr_rec.exchange_rate,1)
547 /arp_global.base_precision)
548 *arp_global.base_precision;
549
550 end if;
551 ard_tbl_tbl(ard_tbl_ctr) := cr_ard_rec;
552 update_flag:='Y';
553 END LOOP;
554
555 SELECT count(*) cnt into l_ard_cnt
556 FROM ar_distributions
557 WHERE source_table = 'MCD' AND
558 source_type ='MISCCASH' and
559 source_id in (select misc_cash_distribution_id
560 from ar_misc_cash_distributions
561 where cash_receipt_id = l_cr_rec.cash_receipt_id
562 and reversal_gl_date is null );
563
564 -- delete all AR Distribution Entery
565 if l_ard_cnt > 0 then
566 arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
567 end if;
568
569 -- strore back all records from above plsql table
570 IF update_flag='Y' THEN
571 FOR l_ctr IN ard_tbl_tbl.FIRST .. ard_tbl_tbl.LAST LOOP
572 ARP_DISTRIBUTIONS_PKG.insert_p(ard_tbl_tbl(l_ctr),l_ard_line_id);
573
574 IF PG_DEBUG in ('Y', 'C') THEN
575 arp_standard.debug( 'line id for line:= '||to_char(l_ard_line_id));
576 END IF;
577 END LOOP;
578 END IF;
579 -- delete record from plsql table
580 ard_tbl_tbl.delete;
581
582 /* Bug 2233284
583 tax_link_id must be assigned before inserting the new record */
584
585 if l_ard_tax_rec_flag ='Y' then
586 l_ard_rec.tax_link_id := 1;
587 end if;
588
589 -- to insert new line record
590 ARP_DISTRIBUTIONS_PKG.insert_p(l_ard_rec,l_ard_line_id);
591
592 IF PG_DEBUG in ('Y', 'C') THEN
593 arp_standard.debug( 'line id for line:= '||to_char(l_ard_line_id));
594 END IF;
595
596 -- to update the tax record
597 IF l_ard_tax_rec_flag = 'Y' THEN
598 if l_ard_tax_rec.source_id = 0 then
599 l_ard_tax_rec.source_id :=l_mcd_id;
600 end if;
601 ARP_DISTRIBUTIONS_PKG.insert_p(l_ard_tax_rec,l_ard_line_id);
602
603 END IF;
604
605 --end for 1543658
606
607 -- Call to accounting engine is commented for 1543658
608 --arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
609
610 IF PG_DEBUG in ('Y', 'C') THEN
611 arp_standard.debug( 'Insert Misc Cash Receipt Distribution start');
612 END IF;
613
614 -- populate return variable:
615
616 p_mcd_id := l_mcd_id;
617
618 /********** bug5655154
619 --begin 1813186
620 else
621
622 -- create mcd record for cash besis
623
624 l_mcd_rec.cash_receipt_id := p_cash_receipt_id;
625 l_mcd_rec.gl_date := p_gl_date;
626 l_mcd_rec.percent := p_percent;
627 l_mcd_rec.amount := p_amount;
628 l_mcd_rec.comments := p_comments;
629 l_mcd_rec.apply_date := p_apply_date;
630 l_mcd_rec.attribute_category := p_attribute_category;
631 l_mcd_rec.attribute1 := p_attribute1;
632 l_mcd_rec.attribute2 := p_attribute2;
633 l_mcd_rec.attribute3 := p_attribute3;
634 l_mcd_rec.attribute4 := p_attribute4;
635 l_mcd_rec.attribute5 := p_attribute5;
636 l_mcd_rec.attribute6 := p_attribute6;
637 l_mcd_rec.attribute7 := p_attribute7;
638 l_mcd_rec.attribute8 := p_attribute8;
639 l_mcd_rec.attribute9 := p_attribute9;
640 l_mcd_rec.attribute10 := p_attribute10;
641 l_mcd_rec.attribute11 := p_attribute11;
642 l_mcd_rec.attribute12 := p_attribute12;
643 l_mcd_rec.attribute13 := p_attribute13;
644 l_mcd_rec.attribute14 := p_attribute14;
645 l_mcd_rec.attribute15 := p_attribute15;
646 l_mcd_rec.acctd_amount := p_acctd_amount;
647 l_mcd_rec.ussgl_transaction_code := p_ussgl_tc;
648 l_mcd_rec.posting_control_id := -3; -- not posted;
649 l_mcd_rec.set_of_books_id := arp_global.set_of_books_id;
650 l_mcd_rec.code_combination_id := p_code_combination_id;
651 l_mcd_rec.created_from := 'ARRERCT';
652
653
654 -- Call table handler for ar_misc_cash_distributions to insert record.
655
656 arp_misc_cash_dist_pkg.insert_p(l_mcd_rec, l_mcd_id);
657
658 -- populate return variable:
659
660 p_mcd_id := l_mcd_id;
661
662
663 end if;
664 --end 1813186
665 ************ bug5655154 ****/
666
667 l_xla_ev_rec.xla_from_doc_id := p_cash_receipt_id;
668 l_xla_ev_rec.xla_to_doc_id := p_cash_receipt_id;
669 l_xla_ev_rec.xla_mode := 'O';
670 l_xla_ev_rec.xla_call := 'B';
671
672 l_xla_ev_rec.xla_doc_table := 'MCD';
673 l_xla_ev_rec.xla_call := 'D';
674 ARP_XLA_EVENTS.create_events(p_xla_ev_rec => l_xla_ev_rec);
675
676
677 IF PG_DEBUG in ('Y', 'C') THEN
678 arp_standard.debug('arp_process_receipts.insert_mcd_rec()-');
679 END IF;
680
681 END insert_mcd_rec;
682
683 /*===========================================================================+
684 | PROCEDURE |
685 | update_mcd_rec |
686 | |
687 | DESCRIPTION |
688 | updates a record in ar_misc_cash_distributions |
689 | |
690 | SCOPE - PRIVATE |
691 | |
692 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
693 | |
694 | ARGUMENTS |
695 | IN: |
696 | OUT: |
697 | |
698 | RETURNS |
699 | |
700 | NOTES |
701 | |
702 | MODIFICATION HISTORY |
703 | |
704 | 02-OCT-1995 OSTEINME created |
705 | 29-SEP-1998 GJWANG Bug fix: 737949 Remove code setting posting|
706 | control id to -3 when update |
707 | 18-JAN-2001 ANUJ Modified for 1543658 |
708 +===========================================================================*/
709
710 PROCEDURE update_mcd_rec(
711 p_misc_cash_distribution_id
712 IN ar_misc_cash_distributions.misc_cash_distribution_id%TYPE,
713 p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
714 p_gl_date IN ar_misc_cash_distributions.gl_date%TYPE,
715 p_percent IN ar_misc_cash_distributions.percent%TYPE,
716 p_amount IN ar_misc_cash_distributions.amount%TYPE,
717 p_comments IN ar_misc_cash_distributions.comments%TYPE,
718 p_apply_date IN ar_misc_cash_distributions.apply_date%TYPE,
719 p_code_combination_id IN ar_misc_cash_distributions.code_combination_id%TYPE,
720 p_attribute_category IN ar_misc_cash_distributions.attribute_category%TYPE,
721 p_attribute1 IN ar_misc_cash_distributions.attribute1%TYPE,
722 p_attribute2 IN ar_misc_cash_distributions.attribute2%TYPE,
723 p_attribute3 IN ar_misc_cash_distributions.attribute3%TYPE,
724 p_attribute4 IN ar_misc_cash_distributions.attribute4%TYPE,
725 p_attribute5 IN ar_misc_cash_distributions.attribute5%TYPE,
726 p_attribute6 IN ar_misc_cash_distributions.attribute6%TYPE,
727 p_attribute7 IN ar_misc_cash_distributions.attribute7%TYPE,
728 p_attribute8 IN ar_misc_cash_distributions.attribute8%TYPE,
729 p_attribute9 IN ar_misc_cash_distributions.attribute9%TYPE,
730 p_attribute10 IN ar_misc_cash_distributions.attribute10%TYPE,
731 p_attribute11 IN ar_misc_cash_distributions.attribute11%TYPE,
732 p_attribute12 IN ar_misc_cash_distributions.attribute12%TYPE,
733 p_attribute13 IN ar_misc_cash_distributions.attribute13%TYPE,
734 p_attribute14 IN ar_misc_cash_distributions.attribute14%TYPE,
735 p_attribute15 IN ar_misc_cash_distributions.attribute15%TYPE,
736 p_acctd_amount IN ar_misc_cash_distributions.acctd_amount%TYPE,
737 p_ussgl_tc IN ar_misc_cash_distributions.ussgl_transaction_code%TYPE,
738 p_form_name IN varchar2,
739 p_form_version IN varchar2,
740 p_amount_ard IN ar_distributions.amount_dr%TYPE,--for 1543658
741 p_acctd_amount_ard IN ar_distributions.acctd_amount_dr%TYPE --for 1543658
742 ) IS
743
744 l_mcd_rec ar_misc_cash_distributions%ROWTYPE;
745 l_ard_line_id ar_distributions.line_id%TYPE;
746 l_ard_rec ar_distributions%ROWTYPE;
747 l_ard_tax_rec ar_distributions%ROWTYPE;
748 l_ard_tax_rec_flag char(1);
749 l_ard_chk_first_rec char(1);
750 l_cr_rec ar_cash_receipts%ROWTYPE;
751 l_tax_account_id ar_vat_tax.tax_account_id%TYPE; -- code_combination_id for tax
752 l_vat_tax_id ar_vat_tax.vat_tax_id%TYPE; -- tax_code_id
753 ard_tbl_ctr number := 0; --counter to store AR Distribution record
754 -- in plsql Table
755 -- for 1543658
756 -- this cursor stores all AR Distributions lines except the one
757 -- which is processed presently
758 -- and which is not a Tax line for the present receipt
759
760 CURSOR cr_ard_cur
761 (p_mcd_id in ar_misc_cash_distributions.misc_cash_distribution_id%TYPE,
762 p_cash_receipt_id in ar_cash_receipts.cash_receipt_id%TYPE)
763 IS
764 SELECT *
765 FROM ar_distributions
766 WHERE source_table = 'MCD' AND
767 source_type ='MISCCASH' and
768 source_id in (select misc_cash_distribution_id
769 from ar_misc_cash_distributions
770 where cash_receipt_id = p_cash_receipt_id and
771 misc_cash_distribution_id <> p_mcd_id
772 and reversal_gl_date is null);
773
774 --VAT variable
775 l_ae_doc_rec l_ae_doc_rec_type;
776 l_min_unit NUMBER;
777 l_precision NUMBER;
778 update_flag CHAR(1);
779 /* Bug fix 2827019 */
780 l_amount_total NUMBER;
781 l_percent_total NUMBER;
782 l_amount_current NUMBER;
783 l_percent_current NUMBER;
784
785 --Bug#2750340
786 l_xla_ev_rec arp_xla_events.xla_events_type;
787 l_xla_doc_table VARCHAR2(20);
788 l_ard_cnt NUMBER := 0 ; -- bug5655154
789 BEGIN
790
791 -- arp_standard.enable_debug;
792 IF PG_DEBUG in ('Y', 'C') THEN
793 arp_standard.debug('arp_process_receipts.update_mcd_rec()+');
794 END IF;
795 --bug5655154, commented accounting_method = 'ACCRUAL'
796 --begin 1813186
797 -- if arp_global.sysparam.accounting_method = 'ACCRUAL' then
798 --end 1813186
799
800
801 -- check if calling form is compatible with entity handler
802
803 -- ??????
804
805
806 -- fetch existing record from database:
807 arp_misc_cash_dist_pkg.fetch_p(p_misc_cash_distribution_id, l_mcd_rec);
808 --begin 1543658
809 -- this select will fetch some important information
810 -- which we need to pass in the table handler
811 -- of AR Distributions
812 select cr.cash_receipt_id ,
813 cr.amount ,
814 cr.vat_tax_id ,
815 cr.tax_rate ,
816 cr.currency_code ,
817 cr.exchange_rate ,
818 cr.exchange_rate_type ,
819 cr.exchange_date ,
820 cr.pay_from_customer , --third_party_id
821 cr.customer_site_use_id , --third_party_sub_id
822 avt.tax_account_id ,
823 avt.vat_tax_id ,
824 fc.precision ,
825 fc.minimum_accountable_unit
826 into l_cr_rec.cash_receipt_id ,
827 l_cr_rec.amount ,
828 l_cr_rec.vat_tax_id ,
829 l_cr_rec.tax_rate ,
830 l_cr_rec.currency_code ,
831 l_cr_rec.exchange_rate ,
832 l_cr_rec.exchange_rate_type ,
833 l_cr_rec.exchange_date ,
834 l_cr_rec.pay_from_customer , --third_party_id
835 l_cr_rec.customer_site_use_id , --third_party_sub_id
836 l_tax_account_id , --code_combination_id for tax
837 l_vat_tax_id , --tax_code_id
838 l_precision ,
839 l_min_unit
840 from ar_cash_receipts cr,
841 ar_vat_tax avt,
842 fnd_currencies fc
843 where cr.cash_receipt_id = p_cash_receipt_id
844 and cr.currency_code = fc.currency_code
845 and cr.vat_tax_id = avt.vat_tax_id(+);
846
847 IF (l_vat_tax_id is not null and l_cr_rec.amount <> 0) then /* Bug fix 2874047 : Added the and condition */
848 if (l_min_unit is null ) then
849 l_mcd_rec.amount := round(l_cr_rec.amount* p_percent/100,l_precision);
850
851 else
852 l_mcd_rec.amount := round(l_cr_rec.amount* (p_percent/100)/l_min_unit)*l_min_unit;
853 end if;
854
855 if (arp_global.base_min_acc_unit is null) then
856 l_mcd_rec.acctd_amount := round((l_cr_rec.amount * p_percent/100) * nvl(l_cr_rec.exchange_rate ,1),
857 arp_global.base_precision);
858 else
859 l_mcd_rec.acctd_amount := round(l_cr_rec.amount* (p_percent/100) * nvl(l_cr_rec.exchange_rate ,1)
860 / arp_global.base_precision) * arp_global.base_precision;
861 end if;
862 IF PG_DEBUG in ('Y', 'C') THEN
863 arp_standard.debug('l_mcd_rec.amount :='||to_char(l_mcd_rec.amount));
864 arp_standard.debug('l_mcd_rec.acctd_amount :='||to_char(l_mcd_rec.acctd_amount));
865 END IF;
866 else
867 l_mcd_rec.amount := p_amount;
868 l_mcd_rec.acctd_amount := p_acctd_amount;
869 end if;
870
871 --end 1543658
872
873 /* Bug fix 2827019 */
874 SELECT sum(amount), sum(percent)
875 INTO l_amount_total, l_percent_total
876 FROM ar_misc_cash_distributions
877 WHERE cash_receipt_id = p_cash_receipt_id
878 AND reversal_gl_date IS NULL;
879
880 SELECT amount,percent
881 INTO l_amount_current, l_percent_current
882 FROM ar_misc_cash_distributions
883 WHERE misc_cash_distribution_id = p_misc_cash_distribution_id;
884
885 arp_standard.debug('l_amount_total = '||to_char(l_amount_total));
886 arp_standard.debug('l_percent_total = '||to_char(l_percent_total));
887 arp_standard.debug('l_amount_current = '||to_char(l_amount_current));
888 arp_standard.debug('l_percent_current = '||to_char(l_percent_current));
889
890 IF l_amount_total - l_amount_current = l_cr_rec.amount - l_mcd_rec.amount
891 AND l_cr_rec.amount <> 0 THEN
892 arp_standard.debug('Calculate percent = 100-total percent');
893 l_mcd_rec.percent := 100 - l_percent_total + l_percent_current;
894 ELSE
895 arp_standard.debug('Calculate percent from p_percent');
896 l_mcd_rec.percent := round(p_percent,3);
897 END IF;
898 arp_standard.debug('After rounding, p_percent = '||to_char(l_mcd_rec.percent));
899 /* end bug fix 2827019*/
900
901
902 -- check if record has already been posted. If yes, raise exception
903 -- (updates are not allowed in that case).
904
905 IF (l_mcd_rec.posting_control_id <> -3 OR
906 (l_mcd_rec.gl_posted_date IS NOT NULL)) THEN
907
908 -- raise exception!
909 NULL;
910 END IF;
911
912 -- update mcd record
913
914 l_mcd_rec.cash_receipt_id := p_cash_receipt_id;
915 l_mcd_rec.gl_date := p_gl_date;
916 -- l_mcd_rec.percent := p_percent; /* Bug fix 2827019*/
917 -- l_mcd_rec.amount := p_amount;
918 l_mcd_rec.comments := p_comments;
919 l_mcd_rec.apply_date := p_apply_date;
920 l_mcd_rec.attribute_category := p_attribute_category;
921 l_mcd_rec.attribute1 := p_attribute1;
922 l_mcd_rec.attribute2 := p_attribute2;
923 l_mcd_rec.attribute3 := p_attribute3;
924 l_mcd_rec.attribute4 := p_attribute4;
925 l_mcd_rec.attribute5 := p_attribute5;
926 l_mcd_rec.attribute6 := p_attribute6;
927 l_mcd_rec.attribute7 := p_attribute7;
928 l_mcd_rec.attribute8 := p_attribute8;
929 l_mcd_rec.attribute9 := p_attribute9;
930 l_mcd_rec.attribute10 := p_attribute10;
931 l_mcd_rec.attribute11 := p_attribute11;
932 l_mcd_rec.attribute12 := p_attribute12;
933 l_mcd_rec.attribute13 := p_attribute13;
934 l_mcd_rec.attribute14 := p_attribute14;
935 l_mcd_rec.attribute15 := p_attribute15;
936 -- l_mcd_rec.acctd_amount := p_acctd_amount;
937 l_mcd_rec.ussgl_transaction_code := p_ussgl_tc;
938 -- l_mcd_rec.posting_control_id := -3; -- not posted;
939 l_mcd_rec.set_of_books_id := arp_global.set_of_books_id;
940 l_mcd_rec.code_combination_id := p_code_combination_id;
941 l_mcd_rec.created_from := 'ARRERCT';
942
943 IF PG_DEBUG in ('Y', 'C') THEN
944 arp_util.debug('MCD amount ' || TO_CHAR(l_mcd_rec.amount));
945 END IF;
946
947 -- Call table handler for ar_misc_cash_distributions to update record.
948
949 arp_misc_cash_dist_pkg.update_p(l_mcd_rec);
950
951 /*-------------------------------------------------------------------------+
952 | Bug 2399871 : Call the Accounting Engine for the unposted records only .|
953 +-------------------------------------------------------------------------*/
954 IF ( l_mcd_rec.posting_control_id = -3 ) THEN
955 --begin for 1543654
956 l_ard_rec.source_id := p_misc_cash_distribution_id;
957 l_ard_rec.source_table := 'MCD';
958 l_ard_rec.source_type := 'MISCCASH';
959 l_ard_rec.source_type_secondary := '';
960 l_ard_rec.code_combination_id := p_code_combination_id;
961 l_ard_rec.source_id_secondary := l_cr_rec.cash_receipt_id;
962 l_ard_rec.source_table_secondary := 'CR';
963 l_ard_rec.currency_code := l_cr_rec.currency_code;
964 l_ard_rec.currency_conversion_rate := l_cr_rec.exchange_rate;
965 l_ard_rec.currency_conversion_type := l_cr_rec.exchange_rate_type;
966 l_ard_rec.currency_conversion_date := l_cr_rec.exchange_date;
967 l_ard_rec.third_party_id := l_cr_rec.pay_from_customer;
968 l_ard_rec.third_party_sub_id := l_cr_rec.customer_site_use_id;
969
970
971 IF sign(p_amount_ard) = -1 THEN -- Debits for Ar distribution
972
973 l_ard_rec.amount_dr := abs(p_amount_ard);
974 if (arp_global.base_min_acc_unit is null) then
975 l_ard_rec.acctd_amount_dr := round(abs(p_amount_ard)
976 *nvl(l_cr_rec.exchange_rate,1),
977 arp_global.base_precision);
978
979 else
980 l_ard_rec.acctd_amount_dr := round(abs(p_amount_ard)
981 *nvl(l_cr_rec.exchange_rate,1)
982 /arp_global.base_precision)
983 *arp_global.base_precision;
984 end if;
985
986
987 l_ard_rec.amount_cr := NULL;
988 l_ard_rec.acctd_amount_cr := NULL;
989
990 ELSE -- Credits for Ar distribution
991
992 l_ard_rec.amount_cr := p_amount_ard;
993 if (arp_global.base_min_acc_unit is null) then
994 l_ard_rec.acctd_amount_cr := round(abs(p_amount_ard)
995 *nvl(l_cr_rec.exchange_rate,1),
996 arp_global.base_precision);
997
998 else
999 l_ard_rec.acctd_amount_cr := round(abs(p_amount_ard)
1000 *nvl(l_cr_rec.exchange_rate,1)
1001 /arp_global.base_precision)
1002 *arp_global.base_precision;
1003 end if;
1004 l_ard_rec.amount_dr := NULL;
1005 l_ard_rec.acctd_amount_dr := NULL;
1006
1007 END IF;
1008 -- end for 1543654
1009
1010
1011 -- Call accounting entry library begins for updating Distribution
1012
1013 IF PG_DEBUG in ('Y', 'C') THEN
1014 arp_standard.debug('Update Misc Cash Receipt Distribution start');
1015 END IF;
1016
1017 l_ae_doc_rec.document_type := 'RECEIPT';
1018 l_ae_doc_rec.document_id := l_mcd_rec.cash_receipt_id;
1019 l_ae_doc_rec.accounting_entity_level := 'ONE';
1020 l_ae_doc_rec.source_table := 'MCD';
1021 l_ae_doc_rec.source_id := '';
1022
1023 -- begin for 1543658
1024 -- Instead of calling accounting engine to update lines in
1025 -- AR Distribution table, we are simultaneously inserting line using
1026 -- table handler, side by side of MCD record.
1027 -- We presevre all other lines in AR Distribution table except the one
1028 -- on which we perform any modification or insertion
1029 -- In the case of delete, we delete line from AR Distribution table
1030 -- simultaneously when we delete from MCD.
1031 -- During Insert and update of Distribution, AR Distribution lines for the
1032 -- the partcular misc receipt are conserverd in plsql table
1033 -- then record are deleted. Later new lines are entered in AR Distribution
1034 -- appending the new changes, using Plsql table record , tax record
1035 -- and current record.
1036
1037 BEGIN
1038 -- to select the tax record of AR distribution in local variable
1039 -- which will be deleted by arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
1040 -- In the next call storing it back by
1041 -- "procedure ARP_DISTRIBUTIONS_PKG.insert_p(l_ard_tax_rec,l_ard_line_id);"
1042 select * into l_ard_tax_rec
1043 from ar_distributions
1044 where source_type ='TAX' and
1045 source_table ='MCD' and
1046 source_type_secondary = 'MISCCASH' and
1047 source_id in (select misc_cash_distribution_id
1048 from ar_misc_cash_distributions
1049 where cash_receipt_id = l_mcd_rec.cash_receipt_id
1050 and reversal_gl_date is null
1051 UNION
1052 select 0 from dual);
1053
1054 if (arp_global.base_min_acc_unit is null) then
1055 l_ard_tax_rec.acctd_amount_dr := round(l_ard_tax_rec.amount_dr
1056 *nvl(l_cr_rec.exchange_rate,1),
1057 arp_global.base_precision);
1058
1059 l_ard_tax_rec.acctd_amount_cr:= round(l_ard_tax_rec.amount_cr
1060 *nvl(l_cr_rec.exchange_rate,1),
1061 arp_global.base_precision);
1062 else
1063 l_ard_tax_rec.acctd_amount_dr := round(l_ard_tax_rec.amount_dr
1064 *nvl(l_cr_rec.exchange_rate,1)
1065 /arp_global.base_precision)
1066 *arp_global.base_precision;
1067
1068 l_ard_tax_rec.acctd_amount_cr := round(l_ard_tax_rec.amount_cr
1069 *nvl(l_cr_rec.exchange_rate,1)
1070 /arp_global.base_precision)
1071 *arp_global.base_precision;
1072
1073 end if;
1074 l_ard_tax_rec_flag:='Y'; -- flag to check if we have tax record
1075 EXCEPTION
1076 WHEN NO_DATA_FOUND THEN
1077 IF PG_DEBUG in ('Y', 'C') THEN
1078 arp_standard.debug('NO INSERT, TAX IS STORED WITH FIRST misc_cash_distribution_id');
1079 END IF;
1080 l_ard_tax_rec_flag:='N';
1081 END;
1082
1083 -- this loop stores all AR Distributions lines except the one
1084 -- which is processed presently and which is not a Tax line for
1085 -- the present receipt in a plsql table
1086
1087 update_flag:='N';
1088 FOR cr_ard_rec IN cr_ard_cur(p_misc_cash_distribution_id,l_cr_rec.cash_receipt_id) LOOP
1089 ard_tbl_ctr := ard_tbl_ctr + 1;
1090
1091 if (arp_global.base_min_acc_unit is null) then
1092 cr_ard_rec.acctd_amount_dr := round(cr_ard_rec.amount_dr *nvl(l_cr_rec.exchange_rate,1),
1093 arp_global.base_precision);
1094
1095 cr_ard_rec.acctd_amount_cr := round(cr_ard_rec.amount_cr*nvl(l_cr_rec.exchange_rate,1),
1096 arp_global.base_precision);
1097 else
1098 cr_ard_rec.acctd_amount_dr := round(cr_ard_rec.amount_dr*nvl(l_cr_rec.exchange_rate,1)
1099 /arp_global.base_precision)*arp_global.base_precision;
1100
1101 cr_ard_rec.acctd_amount_cr := round(cr_ard_rec.amount_cr*nvl(l_cr_rec.exchange_rate,1)
1102 /arp_global.base_precision)*arp_global.base_precision;
1103
1104 end if;
1105 ard_tbl_tbl(ard_tbl_ctr) := cr_ard_rec;
1106 update_flag:='Y';
1107
1108 END LOOP;
1109
1110 -- begin bug5655154, added to check if distributions exists or not
1111 SELECT count(*) cnt into l_ard_cnt
1112 FROM ar_distributions
1113 WHERE source_table = 'MCD' AND
1114 source_type ='MISCCASH' and
1115 source_id in ( select misc_cash_distribution_id
1116 from ar_misc_cash_distributions
1117 where cash_receipt_id = l_cr_rec.cash_receipt_id
1118 and reversal_gl_date is null );
1119
1120 IF l_ard_cnt >0 THEN
1121 -- delete all AR Distribution Entery
1122 arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
1123 END IF ;
1124 -- end bug5655154
1125 -- strore back all records from above plsql table
1126 IF update_flag='Y' THEN
1127 FOR l_ctr IN ard_tbl_tbl.FIRST .. ard_tbl_tbl.LAST LOOP
1128 ARP_DISTRIBUTIONS_PKG.insert_p(ard_tbl_tbl(l_ctr),l_ard_line_id);
1129
1130 END LOOP;
1131 END IF;
1132 -- cleaning up of plsql table
1133 ard_tbl_tbl.delete;
1134
1135 /* Bug 2233284
1136 tax_link_id must be assigned before inserting the new record */
1137
1138 if l_ard_tax_rec_flag = 'Y' then
1139 l_ard_rec.tax_link_id := 1;
1140 end if;
1141
1142 -- to insert new line record
1143 ARP_DISTRIBUTIONS_PKG.insert_p(l_ard_rec,l_ard_line_id);
1144
1145 IF PG_DEBUG in ('Y', 'C') THEN
1146 arp_standard.debug('line id for line:= '||to_char(l_ard_line_id));
1147 END IF;
1148
1149 -- to update the tax record
1150 if l_ard_tax_rec_flag = 'Y' then
1151 if l_ard_tax_rec.source_id = 0 then
1152 l_ard_tax_rec.source_id :=p_misc_cash_distribution_id;
1153 end if;
1154 ARP_DISTRIBUTIONS_PKG.insert_p(l_ard_tax_rec,l_ard_line_id);
1155
1156 end if;
1157
1158 --end for 1543658
1159
1160 -- Call to accounting engine is commented for 1543658
1161 -- arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
1162 IF PG_DEBUG in ('Y', 'C') THEN
1163 arp_standard.debug('Update Misc Cash Receipt Distribution end');
1164 END IF;
1165
1166 END IF; /* IF ( l_mcd_rec.posting_control_id = -3 ) */
1167
1168 /****** bug5655154
1169 --begin 1813186
1170 else --for CASH basis
1171
1172 -- check if calling form is compatible with entity handler
1173
1174 -- ??????
1175
1176 -- fetch existing record from database:
1177
1178 arp_misc_cash_dist_pkg.fetch_p(p_misc_cash_distribution_id, l_mcd_rec);
1179
1180 -- check if record has already been posted. If yes, raise exception
1181 -- (updates are not allowed in that case).
1182
1183 IF (l_mcd_rec.posting_control_id <> -3 OR
1184 (l_mcd_rec.gl_posted_date IS NOT NULL)) THEN
1185
1186 -- raise exception!
1187 NULL;
1188 END IF;
1189
1190 -- update mcd record
1191
1192 l_mcd_rec.cash_receipt_id := p_cash_receipt_id;
1193 l_mcd_rec.gl_date := p_gl_date;
1194 l_mcd_rec.percent := p_percent;
1195 l_mcd_rec.amount := p_amount;
1196 l_mcd_rec.comments := p_comments;
1197 l_mcd_rec.apply_date := p_apply_date;
1198 l_mcd_rec.attribute_category := p_attribute_category;
1199 l_mcd_rec.attribute1 := p_attribute1;
1200 l_mcd_rec.attribute2 := p_attribute2;
1201 l_mcd_rec.attribute3 := p_attribute3;
1202 l_mcd_rec.attribute4 := p_attribute4;
1203 l_mcd_rec.attribute5 := p_attribute5;
1204 l_mcd_rec.attribute6 := p_attribute6;
1205 l_mcd_rec.attribute7 := p_attribute7;
1206 l_mcd_rec.attribute8 := p_attribute8;
1207 l_mcd_rec.attribute9 := p_attribute9;
1208 l_mcd_rec.attribute10 := p_attribute10;
1209 l_mcd_rec.attribute11 := p_attribute11;
1210 l_mcd_rec.attribute12 := p_attribute12;
1211 l_mcd_rec.attribute13 := p_attribute13;
1212 l_mcd_rec.attribute14 := p_attribute14;
1213 l_mcd_rec.attribute15 := p_attribute15;
1214 l_mcd_rec.acctd_amount := p_acctd_amount;
1215 l_mcd_rec.ussgl_transaction_code := p_ussgl_tc;
1216 -- l_mcd_rec.posting_control_id := -3; -- not posted;
1217 l_mcd_rec.set_of_books_id := arp_global.set_of_books_id;
1218 l_mcd_rec.code_combination_id := p_code_combination_id;
1219 l_mcd_rec.created_from := 'ARRERCT';
1220
1221 -- Call table handler for ar_misc_cash_distributions to update record.
1222
1223 arp_misc_cash_dist_pkg.update_p(l_mcd_rec);
1224
1225
1226
1227
1228 end if;
1229 ******** bug5655154 ***/
1230 l_xla_ev_rec.xla_from_doc_id := p_cash_receipt_id;
1231 l_xla_ev_rec.xla_to_doc_id := p_cash_receipt_id;
1232 l_xla_ev_rec.xla_mode := 'O';
1233 l_xla_ev_rec.xla_call := 'B';
1234
1235 l_xla_ev_rec.xla_doc_table := 'MCD';
1236 l_xla_ev_rec.xla_call := 'D';
1237 ARP_XLA_EVENTS.create_events(p_xla_ev_rec => l_xla_ev_rec);
1238
1239
1240
1241 --end 1813186
1242 /* Bug fix 3032059
1243 Update the receipt version number */
1244 arp_cash_receipts_pkg.update_version_number(p_cash_receipt_id);
1245
1246 IF PG_DEBUG in ('Y', 'C') THEN
1247 arp_standard.debug('arp_process_receipts.update_mcd_rec()-');
1248 END IF;
1249
1250 END update_mcd_rec;
1251
1252 /*===========================================================================+
1253 | PROCEDURE |
1254 | lock_mcd_rec |
1255 | |
1256 | DESCRIPTION |
1257 | locks a record in ar_misc_cash_distributions and the corresponding |
1258 | cash receipt record. |
1259 | |
1260 | SCOPE - PRIVATE |
1261 | |
1262 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1263 | |
1264 | ARGUMENTS |
1265 | IN: |
1266 | OUT: |
1267 | |
1268 | RETURNS |
1269 | |
1270 | NOTES |
1271 | |
1272 | MODIFICATION HISTORY |
1273 | |
1274 | 05-OCT-95 OSTEINME created |
1275 | 09-SEP-99 GJWANG 941243: when distribution amount change, lock|
1276 | amount from ar_distributions table isntead of|
1277 | ar_misc_cash_distributions table |
1278 | 30-DEC-02 MRAMANAT Bugfix 2626083. Added code to compare |
1279 | gl_posted_date. |
1280 | |
1281 +===========================================================================*/
1282
1283 PROCEDURE lock_mcd_rec(
1284 p_misc_cash_distribution_id
1285 IN ar_misc_cash_distributions.misc_cash_distribution_id%TYPE,
1286 p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
1287 p_percent IN ar_misc_cash_distributions.percent%TYPE,
1288 p_amount IN ar_misc_cash_distributions.amount%TYPE,
1289 p_comments IN ar_misc_cash_distributions.comments%TYPE,
1290 p_code_combination_id IN ar_misc_cash_distributions.code_combination_id%TYPE,
1291 p_attribute_category IN ar_misc_cash_distributions.attribute_category%TYPE,
1292 p_attribute1 IN ar_misc_cash_distributions.attribute1%TYPE,
1293 p_attribute2 IN ar_misc_cash_distributions.attribute2%TYPE,
1294 p_attribute3 IN ar_misc_cash_distributions.attribute3%TYPE,
1295 p_attribute4 IN ar_misc_cash_distributions.attribute4%TYPE,
1296 p_attribute5 IN ar_misc_cash_distributions.attribute5%TYPE,
1297 p_attribute6 IN ar_misc_cash_distributions.attribute6%TYPE,
1298 p_attribute7 IN ar_misc_cash_distributions.attribute7%TYPE,
1299 p_attribute8 IN ar_misc_cash_distributions.attribute8%TYPE,
1300 p_attribute9 IN ar_misc_cash_distributions.attribute9%TYPE,
1301 p_attribute10 IN ar_misc_cash_distributions.attribute10%TYPE,
1302 p_attribute11 IN ar_misc_cash_distributions.attribute11%TYPE,
1303 p_attribute12 IN ar_misc_cash_distributions.attribute12%TYPE,
1304 p_attribute13 IN ar_misc_cash_distributions.attribute13%TYPE,
1305 p_attribute14 IN ar_misc_cash_distributions.attribute14%TYPE,
1306 p_attribute15 IN ar_misc_cash_distributions.attribute15%TYPE,
1307 p_ussgl_tc IN ar_misc_cash_distributions.ussgl_transaction_code%TYPE,
1308 p_form_name IN varchar2,
1309 p_form_version IN varchar2,
1310 p_gl_posted_date IN ar_misc_cash_distributions.gl_posted_date%TYPE,
1311 p_rec_version_number IN ar_cash_receipts.rec_version_number%TYPE /* Bug fix 3032059 */
1312 ) IS
1313
1314 l_mcd_rec ar_misc_cash_distributions%ROWTYPE;
1315 l_dist_rec_amt NUMBER;
1316 l_dist_rec ar_distributions%ROWTYPE;
1317 l_ard_cnt NUMBER := 0 ;
1318 BEGIN
1319
1320 -- arp_standard.enable_debug;
1321 IF PG_DEBUG in ('Y', 'C') THEN
1322 arp_standard.debug('arp_process_receipts.lock_mcd_rec()+');
1323 END IF;
1324
1325
1326 -- check if calling form is compatible with entity handler
1327
1328 -- ??????
1329
1330
1331 arp_misc_cash_dist_pkg.nowaitlock_fetch_p(p_misc_cash_distribution_id, l_mcd_rec);
1332 --bug5655154, commented accounting_method = 'ACCRUAL' check
1333 --begin 1813186
1334 -- if arp_global.sysparam.accounting_method = 'ACCRUAL' then
1335 --end 1813186
1336
1337 IF PG_DEBUG in ('Y', 'C') THEN
1338 arp_standard.debug('Begin lock Dist amount ');
1339 arp_util.debug('lock_mcd_rec: ' || ' MCD_ID ' || TO_CHAR(p_misc_cash_distribution_id));
1340 END IF;
1341
1342 -- begin, bug5655154, to check if distributions already exist or not
1343 SELECT count(*) cnt into l_ard_cnt
1344 FROM ar_distributions
1345 WHERE source_table = 'MCD' AND
1346 source_type = 'MISCCASH' and
1347 source_id = p_misc_cash_distribution_id ;
1348
1349 IF l_ard_cnt > 0 THEN -- bug5655154
1350 SELECT decode(sign(p_amount), 1, amount_cr,
1351 0, amount_cr,
1352 -1, amount_dr )
1353 INTO l_dist_rec_amt
1354 FROM ar_distributions
1355 WHERE source_id = p_misc_cash_distribution_id
1356 AND source_table = 'MCD'
1357 AND source_type = 'MISCCASH'
1358 FOR UPDATE OF source_id NOWAIT;
1359 ELSE -- bug5655154
1360 l_dist_rec_amt := ABS(p_amount) ;
1361 END IF ; -- bug5655154
1362 -- end bug5655154
1363
1364 IF (((l_mcd_rec.percent = p_percent) OR
1365 (l_mcd_rec.percent IS NULL AND p_percent IS NULL))
1366 AND
1367 -- ((l_mcd_rec.amount = p_amount) OR
1368 -- (l_mcd_rec.amount IS NULL AND p_amount IS NULL))
1369 -- 11/3/2000 mramanat Bugfix 1424234. While Checking
1370 -- l_dist_rec_amt to p_amount, Then the Absolute Value of
1371 -- p_amount is used.
1372 ((l_dist_rec_amt = ABS(p_amount)) OR --Bug Fix 1424234
1373 (l_dist_rec_amt IS NULL AND p_amount IS NULL))
1374 AND
1375 ((l_mcd_rec.comments = p_comments) OR
1376 (l_mcd_rec.comments IS NULL AND p_comments IS NULL))
1377 AND
1378 ((l_mcd_rec.code_combination_id = p_code_combination_id) OR
1379 (l_mcd_rec.code_combination_id IS NULL AND p_code_combination_id IS NULL))
1380 AND
1381 ((l_mcd_rec.attribute_category = p_attribute_category) OR
1382 (l_mcd_rec.attribute_category IS NULL AND p_attribute_category IS NULL))
1383 AND
1384 ((l_mcd_rec.ussgl_transaction_code = p_ussgl_tc) OR
1385 (l_mcd_rec.ussgl_transaction_code IS NULL AND p_ussgl_tc IS NULL))
1386 AND
1387 ((l_mcd_rec.attribute1 = p_attribute1) OR
1388 (l_mcd_rec.attribute1 IS NULL AND p_attribute1 IS NULL))
1389 AND
1390 ((l_mcd_rec.attribute2 = p_attribute2) OR
1391 (l_mcd_rec.attribute2 IS NULL AND p_attribute2 IS NULL))
1392 AND
1393 ((l_mcd_rec.attribute3 = p_attribute3) OR
1394 (l_mcd_rec.attribute3 IS NULL AND p_attribute3 IS NULL))
1395 AND
1396 ((l_mcd_rec.attribute4 = p_attribute4) OR
1397 (l_mcd_rec.attribute4 IS NULL AND p_attribute4 IS NULL))
1398 AND
1399 ((l_mcd_rec.attribute5 = p_attribute5) OR
1400 (l_mcd_rec.attribute5 IS NULL AND p_attribute5 IS NULL))
1401 AND
1402 ((l_mcd_rec.attribute6 = p_attribute6) OR
1403 (l_mcd_rec.attribute6 IS NULL AND p_attribute6 IS NULL))
1404 AND
1405 ((l_mcd_rec.attribute7 = p_attribute7) OR
1406 (l_mcd_rec.attribute7 IS NULL AND p_attribute7 IS NULL))
1407 AND
1408 ((l_mcd_rec.attribute8 = p_attribute8) OR
1409 (l_mcd_rec.attribute8 IS NULL AND p_attribute8 IS NULL))
1410 AND
1411 ((l_mcd_rec.attribute9 = p_attribute9) OR
1412 (l_mcd_rec.attribute9 IS NULL AND p_attribute9 IS NULL))
1413 AND
1414 ((l_mcd_rec.attribute10 = p_attribute10) OR
1415 (l_mcd_rec.attribute10 IS NULL AND p_attribute10 IS NULL))
1416 AND
1417 ((l_mcd_rec.attribute11 = p_attribute11) OR
1418 (l_mcd_rec.attribute11 IS NULL AND p_attribute11 IS NULL))
1419 AND
1420 ((l_mcd_rec.attribute12 = p_attribute12) OR
1421 (l_mcd_rec.attribute12 IS NULL AND p_attribute12 IS NULL))
1422 AND
1423 ((l_mcd_rec.attribute13 = p_attribute13) OR
1424 (l_mcd_rec.attribute13 IS NULL AND p_attribute13 IS NULL))
1425 AND
1426 ((l_mcd_rec.attribute14 = p_attribute14) OR
1427 (l_mcd_rec.attribute14 IS NULL AND p_attribute14 IS NULL))
1428 AND
1429 ((l_mcd_rec.attribute15 = p_attribute15) OR
1430 (l_mcd_rec.attribute15 IS NULL AND p_attribute15 IS NULL))
1431 AND
1432 ((l_mcd_rec.gl_posted_date = p_gl_posted_date) OR
1433 (l_mcd_rec.gl_posted_date IS NULL AND p_gl_posted_date IS NULL)) )
1434 THEN
1435 NULL;
1436 IF PG_DEBUG in ('Y', 'C') THEN
1437 arp_standard.debug(' after IF');
1438 END IF;
1439 ELSE
1440 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1441 APP_EXCEPTION.Raise_Exception;
1442 END IF;
1443 IF PG_DEBUG in ('Y', 'C') THEN
1444 arp_util.debug(' distribution amount ' || TO_CHAR(l_dist_rec_amt));
1445 arp_standard.debug(' End lock AR_DIST amount');
1446 END IF;
1447
1448 --begin 1813186
1449 -- end if;
1450 --end 1813186
1451 -- Call table handler for ar_cash_receipts to lock record.
1452 /* Bug fix 3032059 */
1453 /* Receipt Version Number also to be used for locking the cash_receipt record */
1454 arp_cash_receipts_pkg.nowaitlock_version_p(p_cash_receipt_id,p_rec_version_number);
1455 /*arp_cash_receipts_pkg.nowaitlock_p(p_cash_receipt_id);*/
1456 IF PG_DEBUG in ('Y', 'C') THEN
1457 arp_standard.debug('arp_process_receipts.lock_mcd_rec()-');
1458 END IF;
1459
1460 -- EXCEPTION
1461 -- WHEN NO_DATA_FOUND THEN
1462 -- FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1463 -- APP_EXCEPTION.Raise_Exception;
1464 -- WHEN OTHERS THEN
1465 -- IF (SQLCODE = -54) THEN
1466 -- FND_MESSAGE.Set_Name('FND', 'FORM-CANNOT LOCK');
1467 -- FND_MESSAGE.set_token( 'TABLE', 'AR_MISC_CASH_DISTRIBUTIONS');
1468 -- APP_EXCEPTION.Raise_Exception;
1469 -- ELSE
1470 -- arp_util.debug( SQLERRM );
1471 -- RAISE;
1472 -- END IF;
1473
1474 -- APP_EXCEPTION.Raise_Exception;
1475
1476 END lock_mcd_rec;
1477
1478
1479 /*===========================================================================+
1480 | PROCEDURE |
1481 | round_correction_mcd_rec |
1482 | |
1483 | DESCRIPTION |
1484 | Correct the rounding correctiopn in a miscellaneous distribution |
1485 | record from ar_misc_cash_distributions lines to 1st line. |
1486 | we don't take care of rounding while insert or updation new MCD |
1487 | in Distribution window |
1488 | |
1489 | SCOPE - PRIVATE |
1490 | |
1491 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1492 | |
1493 | ARGUMENTS |
1494 | IN: |
1495 | OUT: |
1496 | |
1497 | RETURNS |
1498 | |
1499 | NOTES |
1500 | |
1501 | MODIFICATION HISTORY |
1502 | |
1503 | 18-JAN-2001 ANUJ Created for 1543658 |
1504 | 19-Sep-2001 Debbie Jancis Added hook for mrc engine for |
1505 | to process update information |
1506 +===========================================================================*/
1507
1508
1509
1510 PROCEDURE round_correction_mcd_rec(
1511 p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
1512 p_flag OUT NOCOPY NUMBER
1513 ) IS
1514
1515 l_min_unit NUMBER;
1516 l_precision NUMBER;
1517 l_acctd_rounding_diff NUMBER;
1518 l_rounding_diff NUMBER;
1519 l_acctd_amount NUMBER;
1520 l_dummy NUMBER;
1521 l_cr_rec ar_cash_receipts%ROWTYPE;
1522 l_ard_acctd_cr_rounding_diff NUMBER;
1523 l_ard_acctd_dr_rounding_diff NUMBER;
1524 l_ar_rounding_diff NUMBER;
1525 l_ard_acctd_cr NUMBER;
1526 l_ard_acctd_dr NUMBER;
1527
1528 l_misc_cash_key_value_list gl_ca_utility_pkg.r_key_value_arr; /* MRC */
1529
1530 CURSOR mcd_cur
1531 ( l_cash_receipt_id in ar_cash_receipts.cash_receipt_id%TYPE)
1532 IS
1533 SELECT *
1534 from ar_misc_cash_distributions
1535 where cash_receipt_id = l_cash_receipt_id
1536 and reversal_gl_date is null;
1537 tbl_ctr number;
1538 update_flag Char(1);
1539
1540 BEGIN
1541
1542 -- arp_standard.enable_debug;
1543 IF PG_DEBUG in ('Y', 'C') THEN
1544 arp_standard.debug('arp_process_receipts.rounding_correction_mcd_rec()+');
1545 END IF;
1546 p_flag := 0;
1547
1548 -- this select will fetch some important information
1549 -- which we need to pass in the table handler
1550 -- of AR Distributions
1551 select cr.cash_receipt_id ,
1552 cr.amount ,
1553 cr.vat_tax_id ,
1554 cr.tax_rate ,
1555 cr.type ,
1556 cr.currency_code ,
1557 cr.exchange_rate ,
1558 cr.exchange_rate_type ,
1559 cr.exchange_date ,
1560 cr.pay_from_customer , --third_party_id
1561 cr.customer_site_use_id , --third_party_sub_id
1562 fc.precision ,
1563 fc.minimum_accountable_unit
1564 into l_cr_rec.cash_receipt_id ,
1565 l_cr_rec.amount ,
1566 l_cr_rec.vat_tax_id ,
1567 l_cr_rec.tax_rate ,
1568 l_cr_rec.type ,
1569 l_cr_rec.currency_code ,
1570 l_cr_rec.exchange_rate ,
1571 l_cr_rec.exchange_rate_type ,
1572 l_cr_rec.exchange_date ,
1573 l_cr_rec.pay_from_customer , --third_party_id
1574 l_cr_rec.customer_site_use_id , --third_party_sub_id
1575 l_precision ,
1576 l_min_unit
1577 from ar_cash_receipts cr,
1578 fnd_currencies fc
1579 where cr.cash_receipt_id = p_cash_receipt_id
1580 and cr.currency_code = fc.currency_code;
1581 -- calculate accounted amount
1582 -- Changes for triangulation: If exchange rate type is not user, call
1583 -- GL API to calculate accounted amount
1584 IF (l_cr_rec.type = 'MISC' ) THEN
1585 IF (l_cr_rec.exchange_rate_type = 'User') THEN
1586 arp_util.calc_acctd_amount( NULL,
1587 NULL,
1588 NULL,
1589 l_cr_rec.exchange_rate,
1590 '+',
1591 l_cr_rec.amount ,
1592 l_acctd_amount,
1593 0,
1594 l_dummy,
1595 l_dummy,
1596 l_dummy);
1597 ELSE
1598 l_acctd_amount := gl_currency_api.convert_amount(
1599 arp_global.set_of_books_id,
1600 l_cr_rec.currency_code,
1601 l_cr_rec.exchange_date,
1602 l_cr_rec.exchange_rate_type,
1603 l_cr_rec.amount);
1604 END IF;
1605 tbl_ctr :=0;
1606 FOR cr_mcd_rec IN mcd_cur(p_cash_receipt_id) LOOP
1607 --tbl_ctr := tbl_ctr + 1; -- Commented for bug 2113787.
1608 if l_cr_rec.exchange_rate is not null then
1609 tbl_ctr := tbl_ctr + 1; -- Added for bug 2113787.
1610 if (arp_global.base_min_acc_unit is null) then
1611
1612 cr_mcd_rec.acctd_amount := round(cr_mcd_rec.amount*nvl(l_cr_rec.exchange_rate ,1),
1613 arp_global.base_precision);
1614 else
1615 cr_mcd_rec.acctd_amount := round(cr_mcd_rec.amount*nvl(l_cr_rec.exchange_rate ,1)
1616 / arp_global.base_precision) * arp_global.base_precision;
1617 end if;
1618
1619
1620 --end if; Commented for bug 2113787.
1621 mcd_tbl_tbl(tbl_ctr) := cr_mcd_rec;
1622 update_flag:='Y';
1623 end if; -- Added for bug2113787.
1624 END LOOP;
1625 IF update_flag='Y' THEN
1626 FOR l_ctr IN mcd_tbl_tbl.FIRST .. mcd_tbl_tbl.LAST LOOP
1627 arp_misc_cash_dist_pkg.update_p(mcd_tbl_tbl(l_ctr));
1628 END LOOP;
1629 p_flag :=p_flag +1;
1630 END IF;
1631 mcd_tbl_tbl.delete;
1632
1633
1634
1635 SELECT NVL(l_cr_rec.amount, 0) -
1636 NVL(SUM(amount),0)
1637 ,
1638 NVL(l_acctd_amount,0) -
1639 NVL(SUM(acctd_amount),0)
1640
1641 INTO l_rounding_diff,
1642 l_acctd_rounding_diff
1643 FROM ar_misc_cash_distributions
1644 WHERE cash_receipt_id = p_cash_receipt_id
1645 and reversal_gl_date is null;
1646
1647 IF PG_DEBUG in ('Y', 'C') THEN
1648 arp_util.debug('round_correction_mcd_rec: ' || 'Rounding error = ' ||to_char(l_rounding_diff));
1649 arp_util.debug('round_correction_mcd_rec: ' || 'Rounding error (acctd) = ' ||to_char(l_acctd_rounding_diff));
1650 END IF;
1651
1652 IF (l_acctd_rounding_diff <> 0 OR l_rounding_diff <>0) THEN
1653
1654 UPDATE ar_misc_cash_distributions
1655 SET amount = amount + l_rounding_diff,
1656 acctd_amount = acctd_amount + l_acctd_rounding_diff
1657
1658 WHERE cash_receipt_id = p_cash_receipt_id
1659 and reversal_gl_date is null
1660 AND ROWNUM = 1
1661 RETURNING misc_cash_distribution_id
1662 BULK COLLECT INTO l_misc_cash_key_value_list;
1663
1664 p_flag :=p_flag +1;
1665 END IF;
1666
1667
1668 --Now rounding correction of accounting amount for AR Distribution
1669
1670 SELECT NVL(SUM(acctd_amount_cr),0),
1671 NVL(SUM(acctd_amount_dr),0)
1672 INTO l_ard_acctd_cr,
1673 l_ard_acctd_dr
1674 FROM ar_distributions
1675 where source_id in (select misc_cash_distribution_id
1676 from ar_misc_cash_distributions
1677 where cash_receipt_id = p_cash_receipt_id
1678 and reversal_gl_date is null ) and
1679 source_table = 'MCD' ;
1680
1681 l_ard_acctd_dr_rounding_diff:= 0;
1682 l_ard_acctd_cr_rounding_diff:= 0;
1683 IF sign(l_cr_rec.amount) = -1 THEN
1684 l_ard_acctd_dr_rounding_diff:=
1685 ABS(NVL(l_acctd_amount,0)) +l_ard_acctd_cr -l_ard_acctd_dr;
1686 ELSE
1687
1688 l_ard_acctd_cr_rounding_diff:=
1689 ABS(NVL(l_acctd_amount,0)) -l_ard_acctd_cr+l_ard_acctd_dr;
1690 END IF;
1691
1692
1693 IF (l_ard_acctd_dr_rounding_diff <> 0) THEN
1694
1695 UPDATE ar_distributions
1696 SET acctd_amount_dr = acctd_amount_dr + l_ard_acctd_dr_rounding_diff
1697
1698 WHERE source_id in (select misc_cash_distribution_id
1699 from ar_misc_cash_distributions
1700 where cash_receipt_id = p_cash_receipt_id
1701 and reversal_gl_date is null )
1702 AND ROWNUM = 1
1703 AND source_table = 'MCD'
1704 AND source_type ='MISCCASH'
1705 AND acctd_amount_dr is not null ; /* Added for bug 2278738 */
1706 p_flag :=p_flag +1;
1707 END IF;
1708 IF (l_ard_acctd_cr_rounding_diff <>0) THEN
1709
1710 UPDATE ar_distributions
1711 SET acctd_amount_cr = acctd_amount_cr + l_ard_acctd_cr_rounding_diff
1712 WHERE source_id in (select misc_cash_distribution_id
1713 from ar_misc_cash_distributions
1714 where cash_receipt_id = p_cash_receipt_id
1715 and reversal_gl_date is null)
1716 AND ROWNUM = 1
1717 AND source_table = 'MCD'
1718 AND source_type ='MISCCASH'
1719 AND acctd_amount_cr is not null ; /* Added for bug 2278738 */
1720 p_flag :=p_flag +1;
1721 END IF;
1722 /* Added for bug 2278738 */
1723 /* Bug fix 2929316 : Commented out the call to COMMIT
1724 The rounding correction is called in the ON-COMMIT trigger and
1725 need not be committed again */
1726 /* IF (p_flag > 0) THEN
1727 COMMIT;
1728 END IF; */
1729
1730 IF PG_DEBUG in ('Y', 'C') THEN
1731 arp_standard.debug('round_correction_mcd_rec: ' || 'arp_process_receipts.rounding_correction_mcd_rec()-');
1732 END IF;
1733 END IF;
1734 END round_correction_mcd_rec;
1735
1736 /* Bug fix 2300268 */
1737 /* Function which returns the code combination id associated with the tax line of a MISC receipt */
1738 /* Bugfix 2753644 . Code modified since reversal or rate adjustment results
1739 in ora 1422. Used MIN to select source_id. */
1740 FUNCTION misc_cash_tax_line_ccid_in_ard(
1741 p_cash_receipt_id IN number) return NUMBER IS
1742 return_value number;
1743
1744 BEGIN
1745 BEGIN
1746 select code_combination_id into return_value
1747 from ar_distributions
1748 where source_id in (select MIN(misc_cash_distribution_id)
1749 from ar_misc_cash_distributions
1750 where cash_receipt_id=p_cash_receipt_id)
1751 and SOURCE_TABLE ='MCD'
1752 and SOURCE_TYPE = 'TAX';
1753 EXCEPTION
1754 when no_data_found then null;
1755 END;
1756 RETURN return_value ;
1757 EXCEPTION
1758 WHEN others THEN
1759 raise ;
1760 END misc_cash_tax_line_ccid_in_ard;
1761 /* End bug fix 2300268 */
1762
1763 END ARP_MISC_CASH_DIST;
[Home] [Help]