1 PACKAGE BODY ARP_DISCOUNTS_API AS
2 /* $Header: ARRUDIAB.pls 120.7 2006/06/01 09:39:40 naneja noship $ */
3
4 /*===========================================================================+
5 | PROCEDURE |
6 | get_discount |
7 | |
8 | DESCRIPTION |
9 | Calculate the discount allowed on a payment and the remaining |
10 | on the invoice. |
11 | |
12 | ARGUMENTS : IN: |
13 | p_ps_id - payment schedule id of the invoice |
14 | p_apply_date - application date of the payment |
15 | p_in_applied_amount - amount being applied |
16 | p_grace_flag - Flag that decides grace days to be applied |
17 | in discount calculation or not. |
18 | |
19 | IN OUT: |
20 | |
21 | OUT: |
22 | p_out_discount - discount available on the payment amount |
23 | p_out_remaining_amount - remaining amount on the invoice |
24 | after application amount and discounts are taken |
25 | |
26 | MODIFICATION HISTORY |
27 | 01/25/01 R Yeluri Created |
28 | 09/10/04 J Beckett Bug 3866488 - corrected the sql to fetch |
29 | grace days from cust acct profile if none |
30 | exists for site use. |
31 +===========================================================================*/
32
33 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
34 /*Bug 5223829 introduced new parameter for iReceivables*/
35 PROCEDURE get_discount (
36 p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
37 p_apply_date IN DATE,
38 p_in_applied_amount IN NUMBER,
39 p_grace_days_flag IN VARCHAR2,
40 p_out_discount OUT NOCOPY NUMBER,
41 p_out_rem_amt_rcpt OUT NOCOPY NUMBER,
42 p_out_rem_amt_inv OUT NOCOPY NUMBER,
43 P_called_from IN VARCHAR2) IS
44
45 p_ps_rec ar_payment_schedules%ROWTYPE;
46 p_disc_rec arp_calculate_discount.discount_record_type;
47
48 l_grace_days NUMBER;
49 l_customer_id NUMBER;
50 l_site_use_id NUMBER;
51
52 l_use_max_cash_flag VARCHAR2(2);
53 l_earned_both_flag VARCHAR2(2);
54 l_default_amt_app VARCHAR2(241);
55 l_error_code NUMBER;
56 l_select_flag VARCHAR2(1);
57 l_close_invoice_flag VARCHAR2(2);
58 l_mode NUMBER;
59 l_earned_disc_pct NUMBER;
60 l_best_disc_pct NUMBER;
61 l_out_earned_disc NUMBER;
62 l_out_unearned_disc NUMBER;
63 l_out_discount_date DATE;
64 l_out_amt_to_apply NUMBER;
65 l_cash_receipt_id NUMBER;
66 l_site_level_profile BOOLEAN; -- Bug 3866488
67 l_amt_due_rem NUMBER; /*Bug 5223829*/
68 l_amt_in_dispute ar_payment_schedules.amount_in_dispute%TYPE; /*Bug 5223829*/
69
70 BEGIN
71 IF PG_DEBUG in ('Y', 'C') THEN
72 arp_standard.debug( 'arp_discounts_api.get_discount() +');
73 END IF;
74
75 -- Get payment schedule info and populate the payment schedule record
76 p_ps_rec.payment_schedule_id := p_ps_id;
77 arp_calculate_discount.get_payment_schedule_info(p_disc_rec, p_ps_rec);
78
79 IF PG_DEBUG in ('Y', 'C') THEN
80 arp_standard.debug('get_discount: ' || 'initialized payment schedule record');
81 END IF;
82
83 -- Get Customer Id and Site Use Id to obtain any grace days
84 -- available for the Customer.
85 BEGIN
86 SELECT customer_id, customer_site_use_id
87 INTO l_customer_id, l_site_use_id
88 FROM ar_payment_schedules
89 WHERE payment_schedule_id = p_ps_id;
90
91 EXCEPTION
92 WHEN NO_DATA_FOUND THEN
93 IF PG_DEBUG in ('Y', 'C') THEN
94 arp_standard.debug('get_discount: ' || 'No data found for Customer id');
95 END IF;
96 END;
97
98 -- If grace days is allowed from iReceivables, calculate the
99 -- grace days available for the Customer, otherwise grace days is 0
100 /* Bug 3866488 - even though a site use id exists, a customer profile
101 may not be assigned to it, so the cust account profile must be used */
102 if (p_grace_days_flag = 'Y') then
103 l_site_level_profile := TRUE;
104 if l_site_use_id is NOT NULL then
105 BEGIN
106 SELECT NVL(discount_grace_days, 0)
107 INTO l_grace_days
108 FROM hz_customer_profiles
109 WHERE cust_account_id = l_customer_id
110 AND site_use_id = l_site_use_id;
111 EXCEPTION
112 WHEN NO_DATA_FOUND THEN
113 l_site_level_profile := FALSE;
114
115 WHEN OTHERS THEN
116 IF PG_DEBUG in ('Y', 'C') THEN
117 arp_util.debug('get_discount: ' || 'EXCEPTION: Error selecting discount_grace_days from site level customer profile ');
118 arp_util.debug('Validate_amount: ' || 'site_use_id = ' ||TO_CHAR(l_site_use_id));
119 END IF;
120 RAISE;
121 END;
122 END IF;
123
124 IF ( l_site_use_id IS NULL OR NOT l_site_level_profile ) THEN
125 SELECT NVL(discount_grace_days, 0)
126 INTO l_grace_days
127 FROM hz_customer_profiles
128 WHERE cust_account_id = l_customer_id
129 AND site_use_id IS NULL;
130 end if;
131
132 else
133 l_grace_days := 0;
134 end if;
135
136 IF PG_DEBUG in ('Y', 'C') THEN
137 arp_standard.debug('get_discount: ' || 'grace days:'||l_grace_days );
138 END IF;
139
140
141 -- Initilaize various variables necessary to be passed into
142 -- calculate discounts routine.
143 l_use_max_cash_flag := 'Y' ;
144 /*Bug 4288015 Discount should be earned discount */
145 l_earned_both_flag := 'E' ; /*does B imply both earned and Unearned */
146 l_default_amt_app := 'PMT' ;
147 l_error_code := '0' ;
148 l_select_flag := 'N' ; -- set to 'N' if select_flag is FALSE
149 l_close_invoice_flag := 'N' ; --is Y if mode=default and l_default_amt_app<>PMT
150 --Need to check on this, this needs to be a N
151 l_mode := 1 ; -- default mode
152
153 l_earned_disc_pct := 0 ;
154 l_best_disc_pct := 0 ;
155 l_out_earned_disc := 0 ;
156 l_out_unearned_disc := 0 ;
157 l_out_discount_date := p_apply_date ;
158 l_out_amt_to_apply := 0 ;
159 l_cash_receipt_id := 0 ;
160 /*Bug 5223829 put the logic to consider for amount in dispute when called from iReceivables*/
161 IF p_called_from ='OIR' THEN
162 l_amt_due_rem := p_ps_rec.amount_due_remaining - nvl(p_ps_rec.amount_in_dispute,0);
163 l_amt_in_dispute := p_ps_rec.amount_in_dispute;
164 ELSE
165 l_amt_due_rem := p_ps_rec.amount_due_remaining;
166 l_amt_in_dispute := NULL;
167 END IF;
168
169 -- Call the discounts package.
170 arp_calculate_discount.calculate_discounts(
171 p_in_applied_amount, --input
172 l_grace_days,
173 p_apply_date, --input
174 p_disc_rec.disc_partial_pmt_flag,
175 p_disc_rec.calc_disc_on_lines,
176 l_earned_both_flag,
177 l_use_max_cash_flag,
178 l_default_amt_app,
179 l_earned_disc_pct,
180 l_best_disc_pct,
181 l_out_earned_disc,
182 l_out_unearned_disc,
183 l_out_discount_date,
184 l_out_amt_to_apply,
185 l_close_invoice_flag,
186 p_ps_id, --input
187 p_ps_rec.term_id,
188 p_ps_rec.terms_sequence_number,
189 p_ps_rec.trx_date,
190 p_ps_rec.amount_due_original,
191 l_amt_due_rem,/*Bug 5223829 callled in place of p_ps_rec.amount_due_remaining*/
192 NVL(p_ps_rec.discount_taken_earned,0),
193 NVL(p_ps_rec.discount_taken_unearned,0),
194 NVL(p_ps_rec.amount_line_items_original,0),
195 p_ps_rec.invoice_currency_code,
196 l_select_flag,
197 l_mode,
198 l_error_code,
199 l_cash_receipt_id,
200 p_called_from, /*Bug 5223829*/
201 l_amt_in_dispute /*Bug 5223829*/
202 );
203
204 IF PG_DEBUG in ('Y', 'C') THEN
205 arp_standard.debug('get_discount: ' || 'earned discount :'|| l_out_earned_disc );
206 arp_standard.debug('get_discount: ' || 'unearned discount :'|| l_out_unearned_disc );
207 END IF;
208
209 -- This is the total discount available on the payment schedule
210 -- This program is intended only to give out NOCOPY earned disocunts.
211 -- There will be no unearned discounts, hence even if there are
212 -- unearned discounts, the total disocunt is zero.
213
214 /* Bug 4460264 - allows for negative discount */
215 p_out_discount := l_out_earned_disc;
216
217 IF PG_DEBUG in ('Y', 'C') THEN
218 arp_standard.debug('get_discount: ' || 'Input Amount applied :'|| p_in_applied_amount);
219 arp_standard.debug('get_discount: ' || 'ADR on the Invoice :'||p_ps_rec.amount_due_remaining);
220 END IF;
221
222
223 -- This calculates if the receipt application plus the discount can
224 -- close out NOCOPY the invoice. If is does, is there any receipt amount being
225 -- left as unapplied. If the application plus discount does not close out NOCOPY
226 -- the invoice, what is the amount due remaining on the invoice.
227
228 if ((p_out_discount+p_in_applied_amount)
229 >= p_ps_rec.amount_due_remaining) then
230
231 p_out_rem_amt_rcpt := (p_out_discount+p_in_applied_amount)
232 - p_ps_rec.amount_due_remaining;
233 p_out_rem_amt_inv := 0;
234 else
235 p_out_rem_amt_rcpt := 0;
236 p_out_rem_amt_inv := p_ps_rec.amount_due_remaining
237 - (p_out_discount+p_in_applied_amount);
238 end if;
239
240 IF PG_DEBUG in ('Y', 'C') THEN
241 arp_standard.debug('get_discount: ' || 'Total discount :'|| p_out_discount );
242 arp_standard.debug('get_discount: ' || 'ADR on the Invoice after discount :'||p_out_rem_amt_inv);
243 arp_standard.debug('get_discount: ' || 'ADR on the Receipt after discount :'||p_out_rem_amt_rcpt);
244 arp_standard.debug( 'arp_discounts_api.get_discount() -');
245 END IF;
246
247
248 END get_discount;
249
250
251 /*===========================================================================+
252 | PROCEDURE |
253 | get_max_discount |
254 | |
255 | DESCRIPTION |
256 | Calculate the maximum allowable discount for a given payment schedule |
257 | and the amount needed to close out NOCOPY the invoice. |
258 | on the invoice. |
259 | |
260 | ARGUMENTS : IN: |
261 | p_ps_id - payment schedule id of the invoice |
262 | p_apply_date - application date of the payment |
263 | p_grace_days_flag - Allow grace days Yes/No |
264 | |
265 | IN OUT: |
266 | |
267 | OUT: |
268 | p_out_discount - Max discount available on the payment |
269 | schedule |
270 | p_out_amount_applied - amount needed minus the discount |
271 | available needed to close the invoice |
272 | |
273 | MODIFICATION HISTORY |
274 | 01/25/01 R Yeluri Created |
275 | |
276 +===========================================================================*/
277
278 PROCEDURE get_max_discount (
279 p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
280 p_apply_date IN DATE,
281 p_grace_days_flag IN VARCHAR2,
282 p_out_discount OUT NOCOPY NUMBER,
283 p_out_applied_amt OUT NOCOPY NUMBER) IS
284
285 p_ps_rec ar_payment_schedules%ROWTYPE;
286 p_disc_rec arp_calculate_discount.discount_record_type;
287
288 p_mode NUMBER;
289 l_customer_id NUMBER;
290 l_site_use_id NUMBER;
291 l_grace_days NUMBER;
292 l_discount_date DATE;
293
294 BEGIN
295
296 IF PG_DEBUG in ('Y', 'C') THEN
297 arp_standard.debug( 'arp_discounts_api.get_max_discount() +');
298 arp_standard.debug('get_max_discount: ' || 'Payment schedule Id :'|| p_ps_id);
299 arp_standard.debug('get_max_discount: ' || 'Apply Date :'|| p_apply_date );
300 arp_standard.debug('get_max_discount: ' || 'Allow Grace Days :'|| p_grace_days_flag );
301 END IF;
302
303 p_mode := 1; --default mode
304
305 -- Get Customer Id and Site Use Id to obtain any grace days
306 -- available for the Customer.
307 BEGIN
308 SELECT customer_id, customer_site_use_id
309 INTO l_customer_id, l_site_use_id
310 FROM ar_payment_schedules
311 WHERE payment_schedule_id = p_ps_id;
312
313 EXCEPTION
314 WHEN NO_DATA_FOUND THEN
315 IF PG_DEBUG in ('Y', 'C') THEN
316 arp_standard.debug('get_max_discount: ' || 'No Customer Data Found');
317 END IF;
318 END;
319
320
321 -- Grace days
322 -- If grace days is allowed from iReceivables, calculate the
323 -- grace days available for the Customer, otherwise grace days is 0
324
325 if (p_grace_days_flag = 'Y') then
326 if l_site_use_id is NOT NULL then
327 SELECT NVL(discount_grace_days, 0)
328 INTO l_grace_days
329 FROM hz_customer_profiles
330 WHERE cust_account_id = l_customer_id
331 AND site_use_id = l_site_use_id;
332 else
333 SELECT NVL(discount_grace_days, 0)
334 INTO l_grace_days
335 FROM hz_customer_profiles
336 WHERE cust_account_id = l_customer_id;
337 end if;
338
339 else
340 l_grace_days := 0;
341 end if;
342
343 IF PG_DEBUG in ('Y', 'C') THEN
344 arp_standard.debug('get_max_discount: ' || 'Grace Days :'|| l_grace_days );
345 END IF;
346
347 -- We need this following set of statements, because the
348 -- determine_max_discount always calculates max discount
349 -- while taking into consideration grace days
350 -- The requirement of iReceivables is that if the grace_days_flag
351 -- is FALSE, then the max discount should eliminate any
352 -- grace days available during discount calculation
353 BEGIN
354 SELECT discount_date
355 INTO l_discount_date
356 FROM ar_trx_discounts_v
357 WHERE payment_schedule_id = p_ps_id;
358
359 EXCEPTION
360 WHEN NO_DATA_FOUND THEN
361 IF PG_DEBUG in ('Y', 'C') THEN
362 arp_standard.debug('get_max_discount: ' || 'error getting discount date');
363 END IF;
364 END;
365
366 IF PG_DEBUG in ('Y', 'C') THEN
367 arp_standard.debug('get_max_discount: ' || 'Discount Date :'|| l_discount_date );
368 END IF;
369
370 -- Populate the payment schedule record.
371 p_ps_rec.payment_schedule_id := p_ps_id;
372 arp_calculate_discount.get_payment_schedule_info(p_disc_rec, p_ps_rec);
373 IF PG_DEBUG in ('Y', 'C') THEN
374 arp_standard.debug('get_max_discount: ' || 'Initialized the payment schedule record');
375 END IF;
376
377 if ((p_apply_date - l_grace_days) > l_discount_date) then
378 --implies the application date is past the discount date
379 --including the grace days, hence no disocunt is available
380
381 p_out_discount := 0;
382 else
383
384 --Get Discount Percentages
385 arp_calculate_discount.get_discount_percentages (p_disc_rec, p_ps_rec);
386 IF PG_DEBUG in ('Y', 'C') THEN
387 arp_standard.debug('get_max_discount: ' || 'Calculated Discount percentages');
388 END IF;
389
390 -- Correct percentages for lines-only(ardline) discount if necessary.
391 IF p_disc_rec.calc_disc_on_lines <> 'I' AND
392 p_disc_rec.calc_disc_on_lines <> 'N' THEN
393 arp_calculate_discount.correct_lines_only_discounts ( p_disc_rec, p_ps_rec );
394 END IF;
395 --
396
397 -- If no discount percentages, set discounts to zero.
398 IF ( p_disc_rec.best_disc_pct = 0 ) THEN
399 p_out_discount := 0 ;
400 p_disc_rec.earned_disc_pct := 0;
401 END IF;
402
403 --Calculate max discount available
404 IF PG_DEBUG in ('Y', 'C') THEN
405 arp_standard.debug('get_max_discount: ' || 'Calling MAX discount routine() +');
406 END IF;
407 arp_calculate_discount.determine_max_allowed_disc
408 ( p_mode, p_disc_rec, p_ps_rec );
409
410
411 p_out_discount := nvl(p_disc_rec.max_disc,0);
412
413
414 end if;
415
416 IF PG_DEBUG in ('Y', 'C') THEN
417 arp_standard.debug('get_max_discount: ' || 'Maximum Available Discount :'|| p_out_discount);
418 END IF;
419
420 if (p_out_discount > 0) then
421 p_out_applied_amt := p_ps_rec.amount_due_remaining - p_out_discount;
422 else
423 p_out_applied_amt := p_ps_rec.amount_due_remaining;
424 end if;
425
426 IF PG_DEBUG in ('Y', 'C') THEN
427 arp_standard.debug('get_max_discount: ' || 'Amount Needed to Close the Invoice :'|| p_out_applied_amt);
428 arp_standard.debug( 'arp_discounts_api.get_max_discount() -');
429 END IF;
430
431 END get_max_discount;
432
433 END ARP_DISCOUNTS_API;
[Home] [Help]