[Home] [Help]
PACKAGE BODY: APPS.AR_INTEREST_LINES_PKG
Source
1 PACKAGE BODY AR_INTEREST_LINES_PKG AS
2 /*$Header: ARIILINEB.pls 120.1 2006/03/09 22:54:30 hyu noship $*/
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,
30 P_LAST_UPDATED_BY IN NUMBER,
31 P_LAST_UPDATE_LOGIN IN NUMBER,
32 P_PROCESS_STATUS IN VARCHAR2,
33 P_PROCESS_MESSAGE 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
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' );
137 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'batch_status');
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 IS
170 CURSOR c IS
171 SELECT *
172 FROM ar_interest_lines
173 WHERE interest_line_id = P_INTEREST_line_ID
174 FOR UPDATE OF INTEREST_line_ID;
175 l_rec ar_interest_lines%ROWTYPE;
176 l_new_rec ar_interest_lines%ROWTYPE;
177 BEGIN
178 arp_util.debug('AR_INTEREST_BATCHES_PKG.update_line +');
179 arp_util.debug(' p_interest_line_id :' ||P_INTEREST_LINE_ID);
180 SAVEPOINT Update_line;
181 x_return_status := fnd_api.g_ret_sts_success;
182
183 IF fnd_api.to_boolean(p_init_msg_list) THEN
184 fnd_msg_pub.initialize;
185 END IF;
186
187 x_return_status := fnd_api.G_RET_STS_SUCCESS;
188
189 OPEN c;
190 FETCH c INTO l_rec;
191 CLOSE c;
192
193 IF l_rec.INTEREST_line_ID IS NULL THEN
194 fnd_message.set_name('AR', 'HZ_API_NO_RECORD');
195 fnd_message.set_token('RECORD', 'ar_interest_lines');
196 fnd_message.set_token('VALUE',
197 NVL(TO_CHAR(P_INTEREST_line_ID), 'null'));
198 fnd_msg_pub.add;
199 RAISE fnd_api.g_exc_error;
200 END IF;
201
202 IF NOT ((x_object_version_number IS NULL AND
203 l_rec.OBJECT_VERSION_NUMBER IS NULL) OR
204 (x_object_version_number IS NOT NULL AND
205 l_rec.OBJECT_VERSION_NUMBER IS NOT NULL AND
206 x_object_version_number = l_rec.OBJECT_VERSION_NUMBER))
207 THEN
208 fnd_message.set_name('AR', 'HZ_API_RECORD_CHANGED');
209 fnd_message.set_token('TABLE', 'ar_interest_lines');
210 fnd_msg_pub.add;
211 RAISE fnd_api.g_exc_error;
212 END IF;
213
214 l_new_rec.process_status := p_process_status;
215 l_new_rec.process_message := p_process_message;
216
217 validate_line
218 (p_action => 'UPDATE',
219 p_old_rec => l_rec,
220 p_new_rec => l_new_rec,
221 x_return_status => x_return_status);
222
223 IF x_return_status <> fnd_api.g_ret_sts_success THEN
224 RAISE fnd_api.g_exc_error;
225 END IF;
226
227 x_object_version_number := NVL(x_object_version_number,1) + 1;
228
229 arp_util.debug(' updating ar_interest_lines');
230 UPDATE AR_INTEREST_LINES
231 SET
232 LAST_UPDATE_DATE = SYSDATE,
233 LAST_UPDATED_BY = NVL(arp_global.last_updated_by,-1),
234 LAST_UPDATE_LOGIN = NVL(arp_global.LAST_UPDATE_LOGIN,-1),
235 PROCESS_STATUS = P_PROCESS_STATUS,
236 PROCESS_MESSAGE = P_PROCESS_MESSAGE,
237 object_version_number = x_object_version_number
238 WHERE interest_line_id = P_INTEREST_LINE_ID;
239
240 arp_util.debug('AR_INTEREST_BATCHES_PKG.update_line -');
241 EXCEPTION
242 WHEN fnd_api.g_exc_error THEN
243 ROLLBACK TO SAVEPOINT Update_line;
244 x_return_status := fnd_api.g_ret_sts_error;
245 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
246 p_count => x_msg_count,
247 p_data => x_msg_data);
248 arp_util.debug('EXCEPTION AR_INTEREST_BATCHES_PKG.update_line :'||x_msg_data);
249 WHEN OTHERS THEN
250 ROLLBACK TO SAVEPOINT Update_line;
251 x_return_status := fnd_api.g_ret_sts_unexp_error;
252 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
253 fnd_message.set_token('ERROR' ,SQLERRM);
254 fnd_msg_pub.add;
255 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
256 p_count => x_msg_count,
257 p_data => x_msg_data);
258 arp_util.debug('EXCEPTION OTHERS AR_INTEREST_BATCHES_PKG.update_line :'||SQLERRM);
259 END Update_Line;
260
261
262
263 PROCEDURE Delete_line
264 (p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
265 p_interest_line_id IN NUMBER,
266 x_object_version_number IN NUMBER,
267 x_return_status OUT NOCOPY VARCHAR2,
268 x_msg_count OUT NOCOPY NUMBER,
269 x_msg_data OUT NOCOPY VARCHAR2)
270 IS
271 CURSOR c IS
272 SELECT *
273 FROM ar_interest_lines
274 WHERE interest_line_id = P_INTEREST_line_ID
275 FOR UPDATE OF INTEREST_line_ID;
276 l_rec ar_interest_lines%ROWTYPE;
277 l_new_rec ar_interest_lines%ROWTYPE;
278 BEGIN
279 arp_util.debug('AR_INTEREST_BATCHES_PKG.delete_line +');
280 SAVEPOINT Delete_line;
281 x_return_status := fnd_api.g_ret_sts_success;
282
283 IF fnd_api.to_boolean(p_init_msg_list) THEN
284 fnd_msg_pub.initialize;
285 END IF;
286
287 x_return_status := fnd_api.G_RET_STS_SUCCESS;
288
289 OPEN c;
290 FETCH c INTO l_rec;
291 CLOSE c;
292
293 IF l_rec.INTEREST_line_ID IS NULL THEN
294 fnd_message.set_name('AR', 'HZ_API_NO_RECORD');
295 fnd_message.set_token('RECORD', 'ar_interest_lines');
296 fnd_message.set_token('VALUE',
297 NVL(TO_CHAR(P_INTEREST_line_ID), 'null'));
298 fnd_msg_pub.add;
299 RAISE fnd_api.g_exc_error;
300 END IF;
301
302 IF NOT ((x_object_version_number IS NULL AND
303 l_rec.OBJECT_VERSION_NUMBER IS NULL) OR
304 (x_object_version_number IS NOT NULL AND
305 l_rec.OBJECT_VERSION_NUMBER IS NOT NULL AND
306 x_object_version_number = l_rec.OBJECT_VERSION_NUMBER))
307 THEN
308 fnd_message.set_name('AR', 'HZ_API_RECORD_CHANGED');
309 fnd_message.set_token('TABLE', 'ar_interest_lines');
310 fnd_msg_pub.add;
311 RAISE fnd_api.g_exc_error;
312 END IF;
313
314 validate_line
315 (p_action => 'DELETE',
316 p_old_rec => l_rec,
317 p_new_rec => l_new_rec,
318 x_return_status => x_return_status);
319
320 IF x_return_status <> fnd_api.g_ret_sts_success THEN
321 RAISE fnd_api.g_exc_error;
322 END IF;
323
324 DELETE FROM AR_INTEREST_LINES
325 WHERE interest_line_id = p_interest_line_id;
326
327 arp_util.debug('AR_INTEREST_BATCHES_PKG.delete_line -');
328 EXCEPTION
329 WHEN fnd_api.g_exc_error THEN
330 ROLLBACK TO SAVEPOINT Delete_line;
331 x_return_status := fnd_api.g_ret_sts_error;
332 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
333 p_count => x_msg_count,
334 p_data => x_msg_data);
335 arp_util.debug('EXCEPTION AR_INTEREST_BATCHES_PKG.delete_line :'||x_msg_data);
336 WHEN OTHERS THEN
337 ROLLBACK TO SAVEPOINT Delete_line;
338 x_return_status := fnd_api.g_ret_sts_unexp_error;
339 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
340 fnd_message.set_token('ERROR' ,SQLERRM);
341 fnd_msg_pub.add;
342 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
343 p_count => x_msg_count,
344 p_data => x_msg_data);
345 arp_util.debug('EXCEPTION OTHERS AR_INTEREST_BATCHES_PKG.delete_line :'||SQLERRM);
346
347 END Delete_line;
348
349
350 END AR_INTEREST_LINES_PKG;