[Home] [Help]
PACKAGE BODY: APPS.ARP_PROCESS_CUTIL
Source
1 PACKAGE BODY ARP_PROCESS_CUTIL AS
2 /* $Header: ARCEUTLB.pls 120.6 2006/06/30 08:46:10 arnkumar ship $ */
3
4 /* Declare WHO columns*/
5 l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
6 l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
7 l_last_update_date DATE := SYSDATE;
8
9 /*added for the bug 2641517 */
10 l_term_changed_flag VARCHAR2(1);
11 l_trx_sum_hist_rec AR_TRX_SUMMARY_HIST%rowtype;
12 l_history_id NUMBER;
13 l_trx_class varchar2(30);
14 l_old_dispute_date DATE;
15 l_new_dispute_date DATE;
16 l_new_dispute_amount NUMBER;
17 l_sysdate DATE := SYSDATE;
18 /* Added column dispute_date to the cursor, bug fix 5129946*/
19 CURSOR get_existing_ps (p_ps_id IN NUMBER) IS
20 SELECT payment_schedule_id,
21 invoice_currency_code,
22 due_date,
23 amount_in_dispute,
24 amount_due_original,
25 amount_due_remaining,
26 amount_adjusted,
27 customer_trx_id,
28 customer_id,
29 customer_site_use_id,
30 class,
31 trx_date,
32 dispute_date
33 FROM ar_payment_schedules
34 WHERE payment_schedule_id = p_ps_id;
35
36
37 procedure update_ps( p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
38 p_due_date IN ar_payment_schedules.due_date%TYPE,
39 p_amount_in_dispute IN ar_payment_schedules.amount_in_dispute%TYPE,
40 p_dispute_date IN ar_payment_schedules.dispute_date%TYPE,
41 p_update_dff IN VARCHAR2,
42 p_attribute_category IN ar_payment_schedules.attribute_category%TYPE,
43 p_attribute1 IN ar_payment_schedules.attribute1%TYPE,
44 p_attribute2 IN ar_payment_schedules.attribute2%TYPE,
45 p_attribute3 IN ar_payment_schedules.attribute3%TYPE,
46 p_attribute4 IN ar_payment_schedules.attribute4%TYPE,
47 p_attribute5 IN ar_payment_schedules.attribute5%TYPE,
48 p_attribute6 IN ar_payment_schedules.attribute6%TYPE,
49 p_attribute7 IN ar_payment_schedules.attribute7%TYPE,
50 p_attribute8 IN ar_payment_schedules.attribute8%TYPE,
51 p_attribute9 IN ar_payment_schedules.attribute9%TYPE,
52 p_attribute10 IN ar_payment_schedules.attribute10%TYPE,
53 p_attribute11 IN ar_payment_schedules.attribute11%TYPE,
54 p_attribute12 IN ar_payment_schedules.attribute12%TYPE,
55 p_attribute13 IN ar_payment_schedules.attribute13%TYPE,
56 p_attribute14 IN ar_payment_schedules.attribute14%TYPE,
57 p_attribute15 IN ar_payment_schedules.attribute15%TYPE,
58 p_staged_dunning_level IN ar_payment_schedules.staged_dunning_level%TYPE,
59 p_dunning_level_override_date IN ar_payment_schedules.dunning_level_override_date%TYPE,
60 p_global_attribute_category IN ar_payment_schedules.global_attribute_category%TYPE,
61 p_global_attribute1 IN ar_payment_schedules.global_attribute1%TYPE,
62 p_global_attribute2 IN ar_payment_schedules.global_attribute2%TYPE,
63 p_global_attribute3 IN ar_payment_schedules.global_attribute3%TYPE,
64 p_global_attribute4 IN ar_payment_schedules.global_attribute4%TYPE,
65 p_global_attribute5 IN ar_payment_schedules.global_attribute5%TYPE,
66 p_global_attribute6 IN ar_payment_schedules.global_attribute6%TYPE,
67 p_global_attribute7 IN ar_payment_schedules.global_attribute7%TYPE,
68 p_global_attribute8 IN ar_payment_schedules.global_attribute8%TYPE,
69 p_global_attribute9 IN ar_payment_schedules.global_attribute9%TYPE,
70 p_global_attribute10 IN ar_payment_schedules.global_attribute10%TYPE,
71 p_global_attribute11 IN ar_payment_schedules.global_attribute11%TYPE,
72 p_global_attribute12 IN ar_payment_schedules.global_attribute12%TYPE,
73 p_global_attribute13 IN ar_payment_schedules.global_attribute13%TYPE,
74 p_global_attribute14 IN ar_payment_schedules.global_attribute14%TYPE,
75 p_global_attribute15 IN ar_payment_schedules.global_attribute15%TYPE,
76 p_global_attribute16 IN ar_payment_schedules.global_attribute16%TYPE,
77 p_global_attribute17 IN ar_payment_schedules.global_attribute17%TYPE,
78 p_global_attribute18 IN ar_payment_schedules.global_attribute18%TYPE,
79 p_global_attribute19 IN ar_payment_schedules.global_attribute19%TYPE,
80 p_global_attribute20 IN ar_payment_schedules.global_attribute20%TYPE
81 ) IS
82 BEGIN
83
84 OPEN get_existing_ps(p_ps_id);
85 FETCH get_existing_ps INTO
86 l_trx_sum_hist_rec.payment_schedule_id,
87 l_trx_sum_hist_rec.currency_code,
88 l_trx_sum_hist_rec.due_date,
89 l_trx_sum_hist_rec.amount_in_dispute,
90 l_trx_sum_hist_rec.amount_due_original,
91 l_trx_sum_hist_rec.amount_due_remaining,
92 l_trx_sum_hist_rec.amount_adjusted,
93 l_trx_sum_hist_rec.customer_trx_id,
94 l_trx_sum_hist_rec.customer_id,
95 l_trx_sum_hist_rec.site_use_id,
96 l_trx_class,
97 l_trx_sum_hist_rec.trx_date,
98 l_old_dispute_date;
99
100 IF l_trx_sum_hist_rec.due_date <> p_due_date
101 OR nvl(l_trx_sum_hist_rec.amount_in_dispute,0)
102 <> nvl(p_amount_in_dispute,0)
103 THEN
104
105 AR_BUS_EVENT_COVER.p_insert_trx_sum_hist(l_trx_sum_hist_rec,
106 l_history_id,
107 l_trx_class,
108 'MODIFY_TRX');
109 END IF;
110 /*Bug 5129946: Calling arp_dispute_history.DisputeHistory*/
111 IF get_existing_ps%ROWCOUNT>0 THEN
112 l_new_dispute_amount := p_amount_in_dispute;
113 l_new_dispute_date := p_dispute_date;
114 if(l_new_dispute_amount <> l_trx_sum_hist_rec.amount_in_dispute)
115 OR (l_new_dispute_amount IS NULL and l_trx_sum_hist_rec.amount_in_dispute IS NOT NULL)
116 OR (l_new_dispute_amount IS NOT NULL and l_trx_sum_hist_rec.amount_in_dispute IS NULL)
117 THEN
118 arp_dispute_history.DisputeHistory(l_new_dispute_date,
119 l_old_dispute_date,
120 l_trx_sum_hist_rec.payment_schedule_id,
121 l_trx_sum_hist_rec.payment_schedule_id,
122 l_trx_sum_hist_rec.amount_due_remaining,
123 l_new_dispute_amount,
124 l_trx_sum_hist_rec.amount_in_dispute,
125 l_last_updated_by,
126 l_sysdate,
127 l_last_updated_by,
128 l_sysdate,
129 l_last_update_login);
130 END IF;--if(l_new_dispute_amount <> l_trx_sum_hist_rec.amount_in_dispute)
131 END IF;-- IF get_existing_ps%ROWCOUNT>0 THEN
132 CLOSE get_existing_ps;
133 /*4021729 set the last update date at time of saving record*/
134 l_last_update_date := SYSDATE;
135 IF p_update_dff = 'Y'
136 THEN UPDATE ar_payment_schedules
137 SET staged_dunning_level = p_staged_dunning_level,
138 dunning_level_override_date = p_dunning_level_override_date,
139 due_date = p_due_date,
140 amount_in_dispute = p_amount_in_dispute,
141 dispute_date = p_dispute_date,
142 attribute_category = p_attribute_category,
143 attribute1 = p_attribute1,
144 attribute2 = p_attribute2,
145 attribute3 = p_attribute3,
146 attribute4 = p_attribute4,
147 attribute5 = p_attribute5,
148 attribute6 = p_attribute6,
149 attribute7 = p_attribute7,
150 attribute8 = p_attribute8,
151 attribute9 = p_attribute9,
152 attribute10 = p_attribute10,
153 attribute11 = p_attribute11,
154 attribute12 = p_attribute12,
155 attribute13 = p_attribute13,
156 attribute14 = p_attribute14,
157 attribute15 = p_attribute15,
158 global_attribute_category = p_global_attribute_category,
159 global_attribute1 = p_global_attribute1,
160 global_attribute2 = p_global_attribute2,
161 global_attribute3 = p_global_attribute3,
162 global_attribute4 = p_global_attribute4,
163 global_attribute5 = p_global_attribute5,
164 global_attribute6 = p_global_attribute6,
165 global_attribute7 = p_global_attribute7,
166 global_attribute8 = p_global_attribute8,
167 global_attribute9 = p_global_attribute9,
168 global_attribute10 = p_global_attribute10,
169 global_attribute11 = p_global_attribute11,
170 global_attribute12 = p_global_attribute12,
171 global_attribute13 = p_global_attribute13,
172 global_attribute14 = p_global_attribute14,
173 global_attribute15 = p_global_attribute15,
174 global_attribute16 = p_global_attribute16,
175 global_attribute17 = p_global_attribute17,
176 global_attribute18 = p_global_attribute18,
177 global_attribute19 = p_global_attribute19,
178 global_attribute20 = p_global_attribute20,
179 last_updated_by = l_last_updated_by,
180 last_update_login = l_last_update_login,
181 last_update_date = l_last_update_date
182 WHERE payment_schedule_id = p_ps_id;
183 ELSE UPDATE ar_payment_schedules
184 SET staged_dunning_level = p_staged_dunning_level,
185 dunning_level_override_date = p_dunning_level_override_date,
186 due_date = p_due_date,
187 amount_in_dispute = p_amount_in_dispute,
188 dispute_date = p_dispute_date,
189 last_updated_by = l_last_updated_by,
190 last_update_login = l_last_update_login,
191 last_update_date = l_last_update_date
192 WHERE payment_schedule_id = p_ps_id;
193 END IF;
194 --apandit : bug 2641517 - raising business events.
195 IF l_trx_sum_hist_rec.due_date <> p_due_date
196 OR nvl(l_trx_sum_hist_rec.amount_in_dispute,0)
197 <> nvl(p_amount_in_dispute,0)
198 THEN
199
200 AR_BUS_EVENT_COVER.Raise_Trx_Modify_Event
201 (p_ps_id,
202 l_trx_class,
203 l_history_id);
204 END IF;
205
206 EXCEPTION
207 WHEN OTHERS
208 THEN arp_standard.debug('EXCEPTION: ARP_PROCESS_CUTIL.update_ps ARXCOQIT');
209 RAISE;
210
211 END;
212
213 /*
214 Overloaded the procedure so that it can be used by ARXCOECC (Call form) which
215 does not want dunning related fields to be updated. This is used when a transaction
216 is put on dispute from calls form
217 */
218 procedure update_ps( p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
219 p_due_date IN ar_payment_schedules.due_date%TYPE,
220 p_amount_in_dispute IN ar_payment_schedules.amount_in_dispute%TYPE,
221 p_dispute_date IN ar_payment_schedules.dispute_date%TYPE,
222 p_update_dff IN VARCHAR2,
223 p_attribute_category IN ar_payment_schedules.attribute_category%TYPE,
224 p_attribute1 IN ar_payment_schedules.attribute1%TYPE,
225 p_attribute2 IN ar_payment_schedules.attribute2%TYPE,
226 p_attribute3 IN ar_payment_schedules.attribute3%TYPE,
227 p_attribute4 IN ar_payment_schedules.attribute4%TYPE,
228 p_attribute5 IN ar_payment_schedules.attribute5%TYPE,
229 p_attribute6 IN ar_payment_schedules.attribute6%TYPE,
230 p_attribute7 IN ar_payment_schedules.attribute7%TYPE,
231 p_attribute8 IN ar_payment_schedules.attribute8%TYPE,
232 p_attribute9 IN ar_payment_schedules.attribute9%TYPE,
233 p_attribute10 IN ar_payment_schedules.attribute10%TYPE,
234 p_attribute11 IN ar_payment_schedules.attribute11%TYPE,
235 p_attribute12 IN ar_payment_schedules.attribute12%TYPE,
236 p_attribute13 IN ar_payment_schedules.attribute13%TYPE,
237 p_attribute14 IN ar_payment_schedules.attribute14%TYPE,
238 p_attribute15 IN ar_payment_schedules.attribute15%TYPE
239 )IS
240 BEGIN
241
242 OPEN get_existing_ps(p_ps_id);
243
244 FETCH get_existing_ps INTO
245 l_trx_sum_hist_rec.payment_schedule_id,
246 l_trx_sum_hist_rec.currency_code,
247 l_trx_sum_hist_rec.due_date,
248 l_trx_sum_hist_rec.amount_in_dispute,
249 l_trx_sum_hist_rec.amount_due_original,
250 l_trx_sum_hist_rec.amount_due_remaining,
251 l_trx_sum_hist_rec.amount_adjusted,
252 l_trx_sum_hist_rec.customer_trx_id,
253 l_trx_sum_hist_rec.customer_id,
254 l_trx_sum_hist_rec.site_use_id,
255 l_trx_class,
256 l_trx_sum_hist_rec.trx_date,
257 l_old_dispute_date;
258
259 IF l_trx_sum_hist_rec.due_date <> p_due_date
260 OR nvl(l_trx_sum_hist_rec.amount_in_dispute,0)
261 <> nvl(p_amount_in_dispute,0)
262 THEN
263
264 AR_BUS_EVENT_COVER.p_insert_trx_sum_hist(l_trx_sum_hist_rec,
265 l_history_id,
266 l_trx_class,
267 'MODIFY_TRX');
268 END IF;
269 /*Bug 5129946: Calling arp_dispute_history.DisputeHistory*/
270 IF get_existing_ps%ROWCOUNT>0 THEN
271 l_new_dispute_amount := p_amount_in_dispute;
272 l_new_dispute_date := p_dispute_date;
273 if(l_new_dispute_amount <> l_trx_sum_hist_rec.amount_in_dispute)
274 OR (l_new_dispute_amount IS NULL and l_trx_sum_hist_rec.amount_in_dispute IS NOT NULL)
275 OR (l_new_dispute_amount IS NOT NULL and l_trx_sum_hist_rec.amount_in_dispute IS NULL)
276 THEN
277 arp_dispute_history.DisputeHistory(l_new_dispute_date,
278 l_old_dispute_date,
279 l_trx_sum_hist_rec.payment_schedule_id,
280 l_trx_sum_hist_rec.payment_schedule_id,
281 l_trx_sum_hist_rec.amount_due_remaining,
282 l_new_dispute_amount,
283 l_trx_sum_hist_rec.amount_in_dispute,
284 l_last_updated_by,
285 l_sysdate,
286 l_last_updated_by,
287 l_sysdate,
288 l_last_update_login);
289 END IF;--if(l_new_dispute_amount <> l_trx_sum_hist_rec.amount_in_dispute)
290 END IF;--IF get_existing_ps%ROWCOUNT>0 THEN
291 CLOSE get_existing_ps;
292 /*4021729 set the last update date at time of saving record*/
293 l_last_update_date := SYSDATE;
294 IF p_update_dff = 'Y'
295 THEN UPDATE ar_payment_schedules
296 SET due_date = p_due_date,
297 amount_in_dispute = p_amount_in_dispute,
298 dispute_date = p_dispute_date,
299 attribute_category = p_attribute_category,
300 attribute1 = p_attribute1,
301 attribute2 = p_attribute2,
302 attribute3 = p_attribute3,
303 attribute4 = p_attribute4,
304 attribute5 = p_attribute5,
305 attribute6 = p_attribute6,
306 attribute7 = p_attribute7,
307 attribute8 = p_attribute8,
308 attribute9 = p_attribute9,
309 attribute10 = p_attribute10,
310 attribute11 = p_attribute11,
311 attribute12 = p_attribute12,
312 attribute13 = p_attribute13,
313 attribute14 = p_attribute14,
314 attribute15 = p_attribute15,
315 last_updated_by = l_last_updated_by,
316 last_update_login = l_last_update_login,
317 last_update_date = l_last_update_date
318 WHERE payment_schedule_id = p_ps_id;
319 ELSE UPDATE ar_payment_schedules
320 SET due_date = p_due_date,
321 amount_in_dispute = p_amount_in_dispute,
322 dispute_date = p_dispute_date,
323 last_updated_by = l_last_updated_by,
324 last_update_login = l_last_update_login,
325 last_update_date = l_last_update_date
326 WHERE payment_schedule_id = p_ps_id;
327 END IF;
328 --apandit : bug 2641517 - raising business events.
329 IF l_trx_sum_hist_rec.due_date <> p_due_date
330 OR nvl(l_trx_sum_hist_rec.amount_in_dispute,0)
331 <> nvl(p_amount_in_dispute,0)
332 THEN
333
334 AR_BUS_EVENT_COVER.Raise_Trx_Modify_Event
335 (p_ps_id,
336 l_trx_class,
337 l_history_id);
338 END IF;
339
340 EXCEPTION
341 WHEN OTHERS
342 THEN arp_standard.debug('EXCEPTION: ARP_PROCESS_CUTIL.update_ps ARXCOECC Dispute Action');
343 RAISE;
344
345 END;
346 procedure update_ps( p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
347 p_exclude_from_dunning IN ar_payment_schedules.exclude_from_dunning_flag%TYPE,
348 p_update_dff VARCHAR2,
349 p_attribute_category IN ar_payment_schedules.attribute_category%TYPE,
350 p_attribute1 IN ar_payment_schedules.attribute1%TYPE,
351 p_attribute2 IN ar_payment_schedules.attribute2%TYPE,
352 p_attribute3 IN ar_payment_schedules.attribute3%TYPE,
353 p_attribute4 IN ar_payment_schedules.attribute4%TYPE,
354 p_attribute5 IN ar_payment_schedules.attribute5%TYPE,
355 p_attribute6 IN ar_payment_schedules.attribute6%TYPE,
356 p_attribute7 IN ar_payment_schedules.attribute7%TYPE,
357 p_attribute8 IN ar_payment_schedules.attribute8%TYPE,
358 p_attribute9 IN ar_payment_schedules.attribute9%TYPE,
359 p_attribute10 IN ar_payment_schedules.attribute10%TYPE,
360 p_attribute11 IN ar_payment_schedules.attribute11%TYPE,
361 p_attribute12 IN ar_payment_schedules.attribute12%TYPE,
362 p_attribute13 IN ar_payment_schedules.attribute13%TYPE,
363 p_attribute14 IN ar_payment_schedules.attribute14%TYPE,
364 p_attribute15 IN ar_payment_schedules.attribute15%TYPE
365 )
366 IS
367 BEGIN
368 /*4021729 set the last update date at time of saving record*/
369 l_last_update_date := SYSDATE;
370 IF p_update_dff = 'Y'
371 THEN UPDATE ar_payment_schedules
372 SET exclude_from_dunning_flag = p_exclude_from_dunning,
373 attribute_category = p_attribute_category,
374 attribute1 = p_attribute1,
375 attribute2 = p_attribute2,
376 attribute3 = p_attribute3,
377 attribute4 = p_attribute4,
378 attribute5 = p_attribute5,
379 attribute6 = p_attribute6,
380 attribute7 = p_attribute7,
381 attribute8 = p_attribute8,
382 attribute9 = p_attribute9,
383 attribute10 = p_attribute10,
384 attribute11 = p_attribute11,
385 attribute12 = p_attribute12,
386 attribute13 = p_attribute13,
387 attribute14 = p_attribute14,
388 attribute15 = p_attribute15,
389 last_updated_by = l_last_updated_by,
390 last_update_login = l_last_update_login,
391 last_update_date = l_last_update_date
392 WHERE payment_schedule_id = p_ps_id;
393 ELSE UPDATE ar_payment_schedules
394 SET exclude_from_dunning_flag = p_exclude_from_dunning,
395 last_updated_by = l_last_updated_by,
396 last_update_login = l_last_update_login,
397 last_update_date = l_last_update_date
398 WHERE payment_schedule_id = p_ps_id;
399 END IF;
400
401 EXCEPTION
402 WHEN OTHERS
403 THEN arp_standard.debug('EXCEPTION: ARP_PROCESS_CUTIL.update_ps Dunning Action');
404 RAISE;
405
406 END;
407 procedure update_ps_fdate( p_ps_id IN ar_payment_schedules.payment_schedule_id%TYPE,
408 p_follow_up_date IN ar_payment_schedules.follow_up_date_last%TYPE)
409 IS
410 BEGIN
411 IF p_ps_id IS NOT NULL THEN
412 UPDATE ar_payment_schedules
413 SET follow_up_date_last = p_follow_up_date
414 WHERE payment_schedule_id = p_ps_id;
415 END IF;
416 EXCEPTION
417 WHEN OTHERS THEN
418 arp_standard.debug('EXCEPTION : ARP_PROCESS_CUTIL.update_ps_fdate Follow up Date ');
419 RAISE;
420 END;
421
422 END;