[Home] [Help]
PACKAGE BODY: APPS.AR_INTEREST_LINES_PKG
Source
1 PACKAGE BODY AR_INTEREST_LINES_PKG AS
2 /*$Header: ARIILINEB.pls 120.1.12010000.3 2009/04/08 11:02:34 pbapna ship $*/
3
4 PROCEDURE Lock_line
5 (P_INTEREST_LINE_ID IN NUMBER,
6 P_INTEREST_HEADER_ID IN NUMBER,
7 P_PAYMENT_SCHEDULE_ID IN NUMBER,
8 P_TYPE IN VARCHAR2,
9 P_ORIGINAL_TRX_CLASS IN VARCHAR2,
10 P_DAILY_INTEREST_CHARGE IN NUMBER,
11 P_OUTSTANDING_AMOUNT IN NUMBER,
12 P_DAYS_OVERDUE_LATE IN NUMBER,
13 P_DAYS_OF_INTEREST IN NUMBER,
14 P_INTEREST_CHARGED IN NUMBER,
15 P_PAYMENT_DATE IN DATE,
16 P_FINANCE_CHARGE_CHARGED IN NUMBER,
17 P_AMOUNT_DUE_ORIGINAL IN NUMBER,
18 P_AMOUNT_DUE_REMAINING IN NUMBER,
19 P_ORIGINAL_TRX_ID IN NUMBER,
20 P_RECEIVABLES_TRX_ID IN NUMBER,
21 P_LAST_CHARGE_DATE IN DATE,
22 P_DUE_DATE IN DATE,
23 P_ACTUAL_DATE_CLOSED IN DATE,
24 P_INTEREST_RATE IN NUMBER,
25 P_RATE_START_DATE IN DATE,
26 P_RATE_END_DATE IN DATE,
27 P_SCHEDULE_DAYS_FROM IN NUMBER,
28 P_SCHEDULE_DAYS_TO IN NUMBER,
29 P_LAST_UPDATE_DATE IN DATE,
33 P_PROCESS_MESSAGE IN VARCHAR2,
30 P_LAST_UPDATED_BY IN NUMBER,
31 P_LAST_UPDATE_LOGIN IN NUMBER,
32 P_PROCESS_STATUS IN VARCHAR2,
34 P_ORG_ID IN NUMBER,
35 P_OBJECT_VERSION_NUMBER IN NUMBER,
36 x_return_status OUT NOCOPY VARCHAR2,
37 x_msg_count OUT NOCOPY NUMBER,
38 x_msg_data OUT NOCOPY VARCHAR2)
39 IS
40 CURSOR C IS
41 SELECT *
42 FROM AR_INTEREST_LINES
43 WHERE interest_line_id = p_interest_line_id
44 FOR UPDATE of Interest_Line_Id NOWAIT;
45 Recinfo C%ROWTYPE;
46 BEGIN
47 arp_util.debug('AR_INTEREST_BATCHES_PKG.Lock_line+');
48 x_return_status := fnd_api.g_ret_sts_success;
49
50 OPEN C;
51 FETCH C INTO Recinfo;
52 IF (C%NOTFOUND) THEN
53 CLOSE C;
54 x_return_status := fnd_api.g_ret_sts_error;
55 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
56 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
57 p_count => x_msg_count,
58 p_data => x_msg_data);
59 END IF;
60 CLOSE C;
61
62 IF (Recinfo.INTEREST_LINE_ID = p_interest_line_id)
63 AND (Recinfo.INTEREST_HEADER_ID = p_interest_header_id)
64 AND (Recinfo.PAYMENT_SCHEDULE_ID = P_PAYMENT_SCHEDULE_ID)
65 AND (NVL(Recinfo.TYPE,'X') = NVL(P_TYPE,'X'))
66 AND (NVL(Recinfo.original_trx_class,'X') = NVL(P_ORIGINAL_TRX_CLASS,'X'))
67 AND (NVL(Recinfo.daily_interest_charge,fnd_api.g_miss_num) = NVL(P_DAILY_INTEREST_CHARGE,fnd_api.g_miss_num))
68 AND (NVL(Recinfo.outstanding_amount,fnd_api.g_miss_num) = NVL(P_OUTSTANDING_AMOUNT,fnd_api.g_miss_num))
69 AND (NVL(Recinfo.days_overdue_late,fnd_api.g_miss_num) = NVL(P_DAYS_OVERDUE_LATE,fnd_api.g_miss_num))
70 AND (NVL(Recinfo.days_of_interest,fnd_api.g_miss_num) = NVL(P_DAYS_OF_INTEREST,fnd_api.g_miss_num))
71 AND (NVL(Recinfo.interest_charged,fnd_api.g_miss_num) = NVL(P_INTEREST_CHARGED,fnd_api.g_miss_num))
72 AND (NVL(Recinfo.payment_date,fnd_api.g_miss_date) = NVL(P_PAYMENT_DATE,fnd_api.g_miss_Date))
73 AND (NVL(Recinfo.finance_charge_charged,fnd_api.g_miss_num) = NVL(P_FINANCE_CHARGE_CHARGED,fnd_api.g_miss_num))
74 AND (NVL(Recinfo.amount_due_original,fnd_api.g_miss_num) = NVL(P_AMOUNT_DUE_ORIGINAL,fnd_api.g_miss_num))
75 AND (NVL(Recinfo.amount_due_remaining,fnd_api.g_miss_num) = NVL(P_AMOUNT_DUE_REMAINING,fnd_api.g_miss_num))
76 AND (NVL(Recinfo.original_trx_id,fnd_api.g_miss_num) = NVL(P_ORIGINAL_TRX_ID,fnd_api.g_miss_num))
77 AND (NVL(Recinfo.receivables_trx_id,fnd_api.g_miss_num) = NVL(P_RECEIVABLES_TRX_ID,fnd_api.g_miss_num))
78 AND (NVL(Recinfo.last_charge_date,fnd_api.g_miss_date) = NVL(P_LAST_CHARGE_DATE,fnd_api.g_miss_date))
79 AND (NVL(Recinfo.due_date,fnd_api.g_miss_date) = NVL(P_DUE_DATE,fnd_api.g_miss_date))
80 AND (NVL(Recinfo.actual_date_closed,fnd_api.g_miss_date) = NVL(P_ACTUAL_DATE_CLOSED,fnd_api.g_miss_date))
81 AND (NVL(Recinfo.interest_rate,fnd_api.g_miss_num) = NVL(P_INTEREST_RATE,fnd_api.g_miss_num))
82 AND (NVL(Recinfo.rate_start_date,fnd_api.g_miss_date) = NVL(P_RATE_START_DATE,fnd_api.g_miss_date))
83 AND (NVL(Recinfo.rate_end_date,fnd_api.g_miss_date) = NVL(P_RATE_END_DATE,fnd_api.g_miss_date))
84 AND (NVL(Recinfo.schedule_days_from,fnd_api.g_miss_num) = NVL(P_SCHEDULE_DAYS_FROM,fnd_api.g_miss_num))
85 AND (NVL(Recinfo.schedule_days_to,fnd_api.g_miss_num) = NVL(P_SCHEDULE_DAYS_TO,fnd_api.g_miss_num))
86 AND (NVL(Recinfo.last_update_date,fnd_api.g_miss_date) = NVL(P_LAST_UPDATE_DATE,fnd_api.g_miss_date))
87 AND (NVL(Recinfo.last_updated_by,fnd_api.g_miss_num) = NVL(P_LAST_UPDATED_BY,fnd_api.g_miss_num))
88 AND (NVL(Recinfo.last_update_login,fnd_api.g_miss_num) = NVL(P_LAST_UPDATE_LOGIN,fnd_api.g_miss_num))
89 AND (NVL(Recinfo.process_status,'X') = NVL(P_PROCESS_STATUS,'X'))
90 AND (NVL(Recinfo.process_message,'X') = NVL(P_PROCESS_MESSAGE,'X'))
91 AND (NVL(Recinfo.org_id,fnd_api.g_miss_num) = NVL(P_ORG_ID,fnd_api.g_miss_num))
92 AND (NVL(Recinfo.object_version_number,1) = NVL(P_object_version_number,1))
93 THEN
94 RETURN;
95 ELSE
96 arp_util.debug(' Line Record Changed');
97 x_return_status := fnd_api.g_ret_sts_error;
98 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
99 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
100 p_count => x_msg_count,
101 p_data => x_msg_data);
102 END IF;
103 arp_util.debug('AR_INTEREST_BATCHES_PKG.Lock_line-');
104 END Lock_line;
105
106 PROCEDURE validate_line
107 (p_action IN VARCHAR2,
108 p_old_rec IN ar_interest_lines%ROWTYPE,
109 p_new_rec IN ar_interest_lines%ROWTYPE,
110 x_return_status IN OUT NOCOPY VARCHAR2)
111 IS
112 CURSOR c IS
113 SELECT process_status
114 FROM ar_interest_headers
115 WHERE interest_header_id = p_old_rec.interest_header_id;
116 l_header_status VARCHAR2(1);
117 BEGIN
118 arp_util.debug('validate_line +');
119 arp_util.debug(' p_action :'||p_action);
120 OPEN c;
121 FETCH c INTO l_header_status;
122 IF c%NOTFOUND THEN
123 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_FK' );
124 FND_MESSAGE.SET_TOKEN( 'FK', 'interest_header_id' );
125 FND_MESSAGE.SET_TOKEN( 'COLUMN', p_old_rec.interest_header_id);
126 FND_MESSAGE.SET_TOKEN( 'TABLE', 'ar_interest_headers' );
127 FND_MSG_PUB.ADD;
128 x_return_status := FND_API.G_RET_STS_ERROR;
129 END IF;
130 CLOSE c;
131
132 IF p_action = 'UPDATE' THEN
133 IF l_header_status = 'S' THEN
137 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'batch_status');
134 IF p_old_rec.process_status <> p_new_rec.process_status THEN
135 arp_util.debug('Column process_status not updatable');
136 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_COLUMN' );
138 FND_MSG_PUB.ADD;
139 x_return_status := FND_API.G_RET_STS_ERROR;
140 END IF;
141 END IF;
142 END IF;
143
144
145 IF p_action = 'DELETE' THEN
146 IF l_header_status = 'S' THEN
147 IF p_old_rec.process_status <> p_new_rec.process_status THEN
148 arp_util.debug('Column process_status not updatable');
149 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_COLUMN' );
150 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'batch_status');
151 FND_MSG_PUB.ADD;
152 x_return_status := FND_API.G_RET_STS_ERROR;
153 END IF;
154 END IF;
155 END IF;
156 arp_util.debug('validate_line -');
157 END;
158
159
160 PROCEDURE Update_line
161 (p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
162 P_INTEREST_LINE_ID IN NUMBER,
163 P_PROCESS_STATUS IN VARCHAR2,
164 P_PROCESS_MESSAGE IN VARCHAR2,
165 x_object_version_number IN OUT NOCOPY NUMBER,
166 x_return_status OUT NOCOPY VARCHAR2,
167 x_msg_count OUT NOCOPY NUMBER,
168 x_msg_data OUT NOCOPY VARCHAR2,
169 P_DAYS_INTEREST IN NUMBER DEFAULT NULL,
170 P_INTEREST_CHARGED IN NUMBER DEFAULT NULL )
171 IS
172 CURSOR c IS
173 SELECT *
174 FROM ar_interest_lines
175 WHERE interest_line_id = P_INTEREST_line_ID
176 FOR UPDATE OF INTEREST_line_ID;
177 l_rec ar_interest_lines%ROWTYPE;
178 l_new_rec ar_interest_lines%ROWTYPE;
179 BEGIN
180 arp_util.debug('AR_INTEREST_BATCHES_PKG.update_line +');
181 arp_util.debug(' p_interest_line_id :' ||P_INTEREST_LINE_ID);
182 SAVEPOINT Update_line;
183 x_return_status := fnd_api.g_ret_sts_success;
184
185 IF fnd_api.to_boolean(p_init_msg_list) THEN
186 fnd_msg_pub.initialize;
187 END IF;
188
189 x_return_status := fnd_api.G_RET_STS_SUCCESS;
190
191 OPEN c;
192 FETCH c INTO l_rec;
193 CLOSE c;
194
195 IF l_rec.INTEREST_line_ID IS NULL THEN
196 fnd_message.set_name('AR', 'HZ_API_NO_RECORD');
197 fnd_message.set_token('RECORD', 'ar_interest_lines');
198 fnd_message.set_token('VALUE',
199 NVL(TO_CHAR(P_INTEREST_line_ID), 'null'));
200 fnd_msg_pub.add;
201 RAISE fnd_api.g_exc_error;
202 END IF;
203
204 IF NOT ((x_object_version_number IS NULL AND
205 l_rec.OBJECT_VERSION_NUMBER IS NULL) OR
206 (x_object_version_number IS NOT NULL AND
207 l_rec.OBJECT_VERSION_NUMBER IS NOT NULL AND
208 x_object_version_number = l_rec.OBJECT_VERSION_NUMBER))
209 THEN
210 fnd_message.set_name('AR', 'HZ_API_RECORD_CHANGED');
211 fnd_message.set_token('TABLE', 'ar_interest_lines');
212 fnd_msg_pub.add;
213 RAISE fnd_api.g_exc_error;
214 END IF;
215
216 l_new_rec.process_status := p_process_status;
217 l_new_rec.process_message := p_process_message;
218
219 validate_line
220 (p_action => 'UPDATE',
221 p_old_rec => l_rec,
222 p_new_rec => l_new_rec,
223 x_return_status => x_return_status);
224
225 IF x_return_status <> fnd_api.g_ret_sts_success THEN
226 RAISE fnd_api.g_exc_error;
227 END IF;
228
229 x_object_version_number := NVL(x_object_version_number,1) + 1;
230
231 arp_util.debug(' updating ar_interest_lines');
232 UPDATE AR_INTEREST_LINES
233 SET
234 LAST_UPDATE_DATE = SYSDATE,
235 LAST_UPDATED_BY = NVL(arp_global.last_updated_by,-1),
236 LAST_UPDATE_LOGIN = NVL(arp_global.LAST_UPDATE_LOGIN,-1),
237 PROCESS_STATUS = P_PROCESS_STATUS,
238 PROCESS_MESSAGE = P_PROCESS_MESSAGE,
239 object_version_number = x_object_version_number,
240 DAYS_OF_INTEREST = P_DAYS_INTEREST,
241 INTEREST_CHARGED = P_INTEREST_CHARGED
242 WHERE interest_line_id = P_INTEREST_LINE_ID;
243
244 arp_util.debug('AR_INTEREST_BATCHES_PKG.update_line -');
245 EXCEPTION
246 WHEN fnd_api.g_exc_error THEN
247 ROLLBACK TO SAVEPOINT Update_line;
248 x_return_status := fnd_api.g_ret_sts_error;
249 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
250 p_count => x_msg_count,
251 p_data => x_msg_data);
252 arp_util.debug('EXCEPTION AR_INTEREST_BATCHES_PKG.update_line :'||x_msg_data);
253 WHEN OTHERS THEN
254 ROLLBACK TO SAVEPOINT Update_line;
255 x_return_status := fnd_api.g_ret_sts_unexp_error;
256 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
257 fnd_message.set_token('ERROR' ,SQLERRM);
258 fnd_msg_pub.add;
259 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
260 p_count => x_msg_count,
261 p_data => x_msg_data);
262 arp_util.debug('EXCEPTION OTHERS AR_INTEREST_BATCHES_PKG.update_line :'||SQLERRM);
263 END Update_Line;
264
265
266
267 PROCEDURE Delete_line
268 (p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
269 p_interest_line_id IN NUMBER,
270 x_object_version_number IN NUMBER,
274 IS
271 x_return_status OUT NOCOPY VARCHAR2,
272 x_msg_count OUT NOCOPY NUMBER,
273 x_msg_data OUT NOCOPY VARCHAR2)
275 CURSOR c IS
276 SELECT *
277 FROM ar_interest_lines
278 WHERE interest_line_id = P_INTEREST_line_ID
279 FOR UPDATE OF INTEREST_line_ID;
280 l_rec ar_interest_lines%ROWTYPE;
281 l_new_rec ar_interest_lines%ROWTYPE;
282 BEGIN
283 arp_util.debug('AR_INTEREST_BATCHES_PKG.delete_line +');
284 SAVEPOINT Delete_line;
285 x_return_status := fnd_api.g_ret_sts_success;
286
287 IF fnd_api.to_boolean(p_init_msg_list) THEN
288 fnd_msg_pub.initialize;
289 END IF;
290
291 x_return_status := fnd_api.G_RET_STS_SUCCESS;
292
293 OPEN c;
294 FETCH c INTO l_rec;
295 CLOSE c;
296
297 IF l_rec.INTEREST_line_ID IS NULL THEN
298 fnd_message.set_name('AR', 'HZ_API_NO_RECORD');
299 fnd_message.set_token('RECORD', 'ar_interest_lines');
300 fnd_message.set_token('VALUE',
301 NVL(TO_CHAR(P_INTEREST_line_ID), 'null'));
302 fnd_msg_pub.add;
303 RAISE fnd_api.g_exc_error;
304 END IF;
305
306 IF NOT ((x_object_version_number IS NULL AND
307 l_rec.OBJECT_VERSION_NUMBER IS NULL) OR
308 (x_object_version_number IS NOT NULL AND
309 l_rec.OBJECT_VERSION_NUMBER IS NOT NULL AND
310 x_object_version_number = l_rec.OBJECT_VERSION_NUMBER))
311 THEN
312 fnd_message.set_name('AR', 'HZ_API_RECORD_CHANGED');
313 fnd_message.set_token('TABLE', 'ar_interest_lines');
314 fnd_msg_pub.add;
315 RAISE fnd_api.g_exc_error;
316 END IF;
317
318 validate_line
319 (p_action => 'DELETE',
320 p_old_rec => l_rec,
321 p_new_rec => l_new_rec,
322 x_return_status => x_return_status);
323
324 IF x_return_status <> fnd_api.g_ret_sts_success THEN
325 RAISE fnd_api.g_exc_error;
326 END IF;
327
328 DELETE FROM AR_INTEREST_LINES
329 WHERE interest_line_id = p_interest_line_id;
330
331 arp_util.debug('AR_INTEREST_BATCHES_PKG.delete_line -');
332 EXCEPTION
333 WHEN fnd_api.g_exc_error THEN
334 ROLLBACK TO SAVEPOINT Delete_line;
335 x_return_status := fnd_api.g_ret_sts_error;
336 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
337 p_count => x_msg_count,
338 p_data => x_msg_data);
339 arp_util.debug('EXCEPTION AR_INTEREST_BATCHES_PKG.delete_line :'||x_msg_data);
340 WHEN OTHERS THEN
341 ROLLBACK TO SAVEPOINT Delete_line;
342 x_return_status := fnd_api.g_ret_sts_unexp_error;
343 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
344 fnd_message.set_token('ERROR' ,SQLERRM);
345 fnd_msg_pub.add;
346 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
347 p_count => x_msg_count,
348 p_data => x_msg_data);
349 arp_util.debug('EXCEPTION OTHERS AR_INTEREST_BATCHES_PKG.delete_line :'||SQLERRM);
350
351 END Delete_line;
352
353
354 END AR_INTEREST_LINES_PKG;