[Home] [Help]
PACKAGE BODY: APPS.AR_INTEREST_BATCHES_PKG
Source
1 PACKAGE BODY AR_INTEREST_BATCHES_PKG AS
2 /*$Header: ARIIBATB.pls 120.5 2006/04/07 21:47:35 hyu noship $*/
3
4 g_not VARCHAR2(30):= ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('AR_PROCESS_STATUS','N');
5 g_error VARCHAR2(30):= ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('AR_PROCESS_STATUS','E');
6 g_success VARCHAR2(30):= ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('AR_PROCESS_STATUS','S');
7 g_draft VARCHAR2(30):= ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('AR_RUN_TYPE','D');
8 g_final VARCHAR2(30):= ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('AR_RUN_TYPE','F');
9
10 FUNCTION get_batch_amount
11 (p_interest_batch_id IN NUMBER)
12 RETURN NUMBER
13 IS
14 CURSOR c IS
15 SELECT SUM(NVL(iil.INTEREST_CHARGED,0))
16 FROM ar_interest_lines iil,
17 ar_interest_headers ii,
18 ar_interest_batches ib
19 WHERE iil.interest_header_id = ii.interest_header_id
20 AND ib.interest_batch_id = ii.interest_batch_id
21 AND ib.interest_batch_id = p_interest_batch_id;
22 l_batch_amount NUMBER := 0;
23 BEGIN
24 arp_util.debug('AR_INTEREST_BATCHES_PKG.get_batch_amount+');
25 arp_util.debug(' p_interest_batch_id :'||p_interest_batch_id);
26 OPEN c;
27 FETCH c INTO l_batch_amount;
28 IF c%NOTFOUND THEN
29 l_batch_amount := 0;
30 END IF;
31 CLOSE c;
32 arp_util.debug(' resultat :'||l_batch_amount);
33 arp_util.debug('AR_INTEREST_BATCHES_PKG.get_batch_amount-');
34 RETURN l_batch_amount;
35 END;
36
37 PROCEDURE Lock_batch
38 ( p_Interest_Batch_Id IN NUMBER,
39 p_Batch_Name IN VARCHAR2,
40 p_Calculate_Interest_To_Date IN DATE,
41 p_Gl_Date IN DATE,
42 p_Transferred_status IN VARCHAR2,
43 p_batch_status IN VARCHAR2,
44 p_Org_Id IN NUMBER,
45 p_object_version_number IN NUMBER,
46 x_return_status OUT NOCOPY VARCHAR2,
47 x_msg_count OUT NOCOPY NUMBER,
48 x_msg_data OUT NOCOPY VARCHAR2)
49 IS
50 CURSOR C IS
51 SELECT *
52 FROM AR_INTEREST_BATCHES
53 WHERE Interest_Batch_Id = p_Interest_Batch_Id
54 FOR UPDATE OF Interest_Batch_Id NOWAIT;
55 Recinfo C%ROWTYPE;
56 l_continue VARCHAR2(1) := 'Y';
57 BEGIN
58 arp_util.debug('AR_INTEREST_BATCHES_PKG.Lock_batch+');
59 arp_util.debug(' p_Interest_Batch_Id :'||p_Interest_Batch_Id);
60 x_return_status := fnd_api.g_ret_sts_success;
61 OPEN C;
62 FETCH C INTO Recinfo;
63 IF (C%NOTFOUND) THEN
64 CLOSE C;
65 l_continue := 'N';
66 x_return_status := fnd_api.g_ret_sts_error;
67 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
68 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
69 p_count => x_msg_count,
70 p_data => x_msg_data);
71 END IF;
72 CLOSE C;
73
74 IF l_continue = 'Y' THEN
75 IF (Recinfo.interest_batch_id = p_Interest_Batch_Id )
76 AND (Recinfo.batch_name = p_Batch_Name)
77 AND (Recinfo.calculate_interest_to_date = p_Calculate_Interest_To_Date)
78 AND (Recinfo.gl_date = p_Gl_Date)
79 AND (NVL(Recinfo.transferred_status,'X') = NVL(p_Transferred_status,'X'))
80 AND (NVL(Recinfo.batch_status,'X') = NVL(p_batch_status,'X'))
81 AND (NVL(Recinfo.org_id,-99) = NVL(p_Org_Id,-99))
82 THEN
83 RETURN;
84 ELSE
85 x_return_status := fnd_api.g_ret_sts_error;
86 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
87 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
88 p_count => x_msg_count,
89 p_data => x_msg_data);
90 END IF;
91 END IF;
95
92 arp_util.debug('AR_INTEREST_BATCHES_PKG.Lock_batch-');
93 END Lock_batch;
94
96
97 PROCEDURE Validate_batch
98 ( p_action IN VARCHAR2,
99 p_updated_by_program IN VARCHAR2 DEFAULT 'ARIINR',
100 p_old_batch_rec IN ar_interest_batches%ROWTYPE,
101 p_new_batch_rec IN ar_interest_batches%ROWTYPE,
102 x_cascade_update OUT NOCOPY VARCHAR2,
103 x_return_status IN OUT NOCOPY VARCHAR2)
104 IS
105 --Batch should exists
106 BEGIN
107
108 arp_util.debug(' Validate_batch +');
109
110 x_cascade_update := 'N';
111
112 IF p_action = 'UPDATE' THEN
113
114 IF p_old_batch_rec.batch_status IS NULL OR p_new_batch_rec.batch_status IS NULL
115 THEN
116 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
117 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'BATCH_STATUS' );
118 FND_MSG_PUB.ADD;
119 x_return_status := FND_API.G_RET_STS_ERROR;
120 END IF;
121
122 IF p_old_batch_rec.transferred_status IS NULL OR p_new_batch_rec.transferred_status IS NULL
123 THEN
124 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
125 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'TRANSFERRED_STATUS' );
126 FND_MSG_PUB.ADD;
127 x_return_status := FND_API.G_RET_STS_ERROR;
128 END IF;
129
130 IF p_new_batch_rec.batch_status NOT IN ('D','F') OR
131 p_old_batch_rec.batch_status NOT IN ('D','F')
132 THEN
133 arp_util.debug('Value value possible for batch_status is F or D');
134 FND_MESSAGE.SET_NAME( 'AR', 'AR_ONLY_VALUE_ALLOWED' );
135 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'BATCH_STATUS' );
136 FND_MESSAGE.SET_TOKEN( 'VALUES', g_draft||','||g_final);
137 FND_MSG_PUB.ADD;
138 x_return_status := FND_API.G_RET_STS_ERROR;
139 END IF;
140
141 IF p_new_batch_rec.transferred_status NOT IN ('N','E','S','P') OR
142 p_old_batch_rec.transferred_status NOT IN ('N','E','S','P')
143 THEN
144 arp_util.debug('Value value possible for transferred_status are N, E , S , P');
145 FND_MESSAGE.SET_NAME( 'AR', 'AR_ONLY_VALUE_ALLOWED' );
146 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'TRANSFERRED_STATUS' );
147 FND_MESSAGE.SET_TOKEN( 'VALUES', g_not||','||g_error||','||g_success);
148 FND_MSG_PUB.ADD;
149 x_return_status := FND_API.G_RET_STS_ERROR;
150 END IF;
151
152
153 IF p_old_batch_rec.batch_status = 'F' AND p_new_batch_rec.batch_status <> 'F'
154 THEN
155 arp_util.debug('Can not update the batch status as it is F');
156 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_COLUMN' );
157 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'batch_status');
158 FND_MSG_PUB.ADD;
159 x_return_status := FND_API.G_RET_STS_ERROR;
160 END IF;
161
162
163 IF p_updated_by_program = 'ARIINR' THEN
164 IF p_new_batch_rec.transferred_status NOT IN ('N')
165 AND p_new_batch_rec.transferred_status <> p_old_batch_rec.transferred_status
166 THEN
167 x_return_status := fnd_api.g_ret_sts_error;
168 fnd_message.set_name('AR', 'AR_STATUS_RESERVE_FOR_SRS');
169 fnd_msg_pub.add;
170 END IF;
171 END IF;
172
173
174 IF p_new_batch_rec.batch_status = 'D' AND p_new_batch_rec.transferred_status <> 'N' THEN
175 arp_util.debug('Draft batch only accepts transferred status N');
176 FND_MESSAGE.SET_NAME( 'AR', 'AR_ONLY_VALUE_ALLOWED' );
177 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'TRANSFERRED_STATUS' );
178 FND_MESSAGE.SET_TOKEN( 'VALUES', g_not);
179 FND_MSG_PUB.ADD;
180 x_return_status := FND_API.G_RET_STS_ERROR;
181 END IF;
182
183
184 IF p_old_batch_rec.transferred_status = 'S' AND p_new_batch_rec.transferred_status <> 'S' THEN
185 arp_util.debug('Can not update a successfull batch transferred status');
186 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_COLUMN' );
187 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'transferred_status' );
188 FND_MSG_PUB.ADD;
189 x_return_status := FND_API.G_RET_STS_ERROR;
190 END IF;
191
192 IF p_old_batch_rec.transferred_status <> 'N' AND p_new_batch_rec.transferred_status ='N' THEN
193 x_cascade_update := 'Y';
194 END IF;
195
196 IF p_new_batch_rec.gl_date = fnd_api.g_miss_date THEN
197 arp_util.debug('Gl Date can not be null');
198 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
199 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'gl_date' );
200 FND_MSG_PUB.ADD;
201 x_return_status := FND_API.G_RET_STS_ERROR;
202 END IF;
203
204 IF (p_new_batch_rec.gl_date <> fnd_api.g_miss_date) AND
205 (p_old_batch_rec.gl_date <> p_new_batch_rec.gl_date)
206 THEN
207 IF p_new_batch_rec.transferred_status <> 'N' THEN
208 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_COLUMN' );
209 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'GL_DATE' );
210 FND_MSG_PUB.ADD;
211 x_return_status := FND_API.G_RET_STS_ERROR;
212 END IF;
213 END IF;
214
215
216 END IF;
217
218 IF p_action = 'DELETE' THEN
219 IF p_old_batch_rec.batch_status <> 'D' THEN
220 arp_util.debug('Only Draft batches are delateable');
221 x_return_status := FND_API.G_RET_STS_ERROR;
222 FND_MESSAGE.SET_NAME( 'AR', 'AR_DRAFT_BATCH_DELETABLE' );
223 FND_MSG_PUB.ADD;
224 END IF;
225 END IF;
226
227 arp_util.debug(' Validate_batch -');
228 END;
229
230 PROCEDURE Delete_batch
231 ( p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
232 p_interest_batch_id IN NUMBER,
233 x_object_version_number IN NUMBER,
237 IS
234 x_return_status OUT NOCOPY VARCHAR2,
235 x_msg_count OUT NOCOPY NUMBER,
236 x_msg_data OUT NOCOPY VARCHAR2)
238 CURSOR c IS
239 SELECT
240 INTEREST_BATCH_ID ,
241 BATCH_NAME ,
242 CALCULATE_INTEREST_TO_DATE ,
243 BATCH_STATUS ,
244 GL_DATE ,
245 CREATED_BY ,
246 CREATION_DATE ,
247 TRANSFERRED_status ,
248 ORG_ID ,
249 OBJECT_VERSION_NUMBER
250 FROM ar_interest_batches
251 WHERE interest_batch_id = P_INTEREST_BATCH_ID
252 FOR UPDATE OF INTEREST_BATCH_ID;
253 l_rec ar_interest_batches%ROWTYPE;
254 l_new_rec ar_interest_batches%ROWTYPE;
255 x_cascade_update VARCHAR2(1);
256 BEGIN
257 arp_util.debug('Delete_Batch +');
258 arp_util.debug(' p_interest_batch_id : '||p_interest_batch_id);
259
260 IF fnd_api.to_boolean(p_init_msg_list) THEN
261 fnd_msg_pub.initialize;
262 END IF;
263
264 x_return_status := fnd_api.G_RET_STS_SUCCESS;
265
266 OPEN c;
267 FETCH c INTO
268 l_rec.INTEREST_BATCH_ID ,
269 l_rec.BATCH_NAME ,
270 l_rec.CALCULATE_INTEREST_TO_DATE ,
271 l_rec.BATCH_STATUS ,
272 l_rec.GL_DATE ,
273 l_rec.CREATED_BY ,
274 l_rec.CREATION_DATE ,
275 l_rec.TRANSFERRED_status ,
276 l_rec.ORG_ID ,
277 l_rec.OBJECT_VERSION_NUMBER;
278 CLOSE c;
279
280 IF l_rec.INTEREST_BATCH_ID IS NULL THEN
281 fnd_message.set_name('AR', 'HZ_API_NO_RECORD');
282 fnd_message.set_token('RECORD', 'ar_interest_batches');
283 fnd_message.set_token('VALUE',
284 NVL(TO_CHAR(P_INTEREST_BATCH_ID), 'null'));
285 fnd_msg_pub.add;
286 RAISE fnd_api.g_exc_error;
287 END IF;
288
289 IF NOT ((x_object_version_number IS NULL AND
290 l_rec.OBJECT_VERSION_NUMBER IS NULL) OR
291 (x_object_version_number IS NOT NULL AND
292 l_rec.OBJECT_VERSION_NUMBER IS NOT NULL AND
293 x_object_version_number = l_rec.OBJECT_VERSION_NUMBER))
294 THEN
295 fnd_message.set_name('AR', 'HZ_API_RECORD_CHANGED');
296 fnd_message.set_token('TABLE', 'ar_interest_batches');
297 fnd_msg_pub.add;
298 RAISE fnd_api.g_exc_error;
299 END IF;
300
301 Validate_batch
302 ( p_action => 'DELETE',
303 p_old_batch_rec => l_rec,
304 p_new_batch_rec => l_new_rec,
305 x_cascade_update => x_cascade_update,
306 x_return_status => x_return_status);
307
308 IF x_return_status <> fnd_api.g_ret_sts_success THEN
309 RAISE fnd_api.g_exc_error;
310 END IF;
311
312 DELETE FROM ar_interest_lines iil
313 WHERE EXISTS
314 (SELECT interest_header_id
315 FROM ar_interest_headers ii
316 WHERE iil.interest_header_id = ii.interest_header_id
317 AND ii.interest_batch_id = p_interest_batch_id);
318
319 DELETE FROM ar_interest_headers
320 WHERE interest_batch_id = p_interest_batch_id;
321
322 DELETE FROM AR_INTEREST_BATCHES
323 WHERE interest_batch_id = p_interest_batch_id ;
324
325 arp_util.debug('Delete_Batch -');
326 EXCEPTION
327 WHEN fnd_api.g_exc_error THEN
328 x_return_status := fnd_api.g_ret_sts_error;
329 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
330 p_count => x_msg_count,
331 p_data => x_msg_data);
332
333 IF x_msg_count > 1 THEN
334 x_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_LAST,FND_API.G_FALSE );
335 END IF;
336 WHEN OTHERS THEN
337 x_return_status := fnd_api.g_ret_sts_unexp_error;
338 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
339 fnd_message.set_token('ERROR' ,SQLERRM);
340 fnd_msg_pub.add;
341 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
342 p_count => x_msg_count,
343 p_data => x_msg_data);
344
345 END Delete_batch;
346
347
348 PROCEDURE update_batch
349 (p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
350 P_INTEREST_BATCH_ID IN NUMBER,
351 P_BATCH_STATUS IN VARCHAR2,
352 P_TRANSFERRED_status IN VARCHAR2,
353 p_gl_date IN DATE DEFAULT NULL,
354 p_updated_by_program IN VARCHAR2 DEFAULT 'ARIINR',
355 x_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
356 x_return_status OUT NOCOPY VARCHAR2,
357 x_msg_count OUT NOCOPY NUMBER,
358 x_msg_data OUT NOCOPY VARCHAR2)
359 IS
360 CURSOR c IS
361 SELECT
362 INTEREST_BATCH_ID ,
363 BATCH_NAME ,
364 CALCULATE_INTEREST_TO_DATE ,
365 BATCH_STATUS ,
366 GL_DATE ,
367 CREATED_BY ,
368 CREATION_DATE ,
369 TRANSFERRED_status ,
370 ORG_ID ,
371 OBJECT_VERSION_NUMBER
372 FROM ar_interest_batches
373 WHERE interest_batch_id = P_INTEREST_BATCH_ID
374 FOR UPDATE OF INTEREST_BATCH_ID;
378
375 l_rec ar_interest_batches%ROWTYPE;
376 l_new_rec ar_interest_batches%ROWTYPE;
377 x_cascade_update VARCHAR2(1);
379 BEGIN
380 arp_util.debug('update_batch +');
381 arp_util.debug(' p_interest_batch_id : '||p_interest_batch_id);
382
383 savepoint update_batch;
384
385 IF fnd_api.to_boolean(p_init_msg_list) THEN
386 fnd_msg_pub.initialize;
387 END IF;
388
389 x_return_status := fnd_api.G_RET_STS_SUCCESS;
390
391 OPEN c;
392 FETCH c INTO
393 l_rec.INTEREST_BATCH_ID ,
394 l_rec.BATCH_NAME ,
395 l_rec.CALCULATE_INTEREST_TO_DATE ,
396 l_rec.BATCH_STATUS ,
397 l_rec.GL_DATE ,
398 l_rec.CREATED_BY ,
399 l_rec.CREATION_DATE ,
400 l_rec.TRANSFERRED_status ,
401 l_rec.ORG_ID ,
402 l_rec.OBJECT_VERSION_NUMBER;
403 CLOSE c;
404
405 IF l_rec.INTEREST_BATCH_ID IS NULL THEN
406 fnd_message.set_name('AR', 'HZ_API_NO_RECORD');
407 fnd_message.set_token('RECORD', 'ar_interest_batches');
408 fnd_message.set_token('VALUE',
409 NVL(TO_CHAR(P_INTEREST_BATCH_ID), 'null'));
410 fnd_msg_pub.add;
411 RAISE fnd_api.g_exc_error;
412 END IF;
413
414 IF NOT ((x_object_version_number IS NULL AND
415 l_rec.OBJECT_VERSION_NUMBER IS NULL) OR
416 (x_object_version_number IS NOT NULL AND
417 l_rec.OBJECT_VERSION_NUMBER IS NOT NULL AND
418 x_object_version_number = l_rec.OBJECT_VERSION_NUMBER))
419 THEN
420 fnd_message.set_name('AR', 'HZ_API_RECORD_CHANGED');
421 fnd_message.set_token('TABLE', 'ar_interest_batches');
422 fnd_msg_pub.add;
423 RAISE fnd_api.g_exc_error;
424 END IF;
425
426 l_new_rec.INTEREST_BATCH_ID := P_INTEREST_BATCH_ID;
427 l_new_rec.BATCH_STATUS := P_BATCH_STATUS;
428 l_new_rec.TRANSFERRED_status := P_TRANSFERRED_status;
429 l_new_rec.gl_date := p_gl_date;
430
431 Validate_batch
432 ( p_action => 'UPDATE',
433 p_updated_by_program => p_updated_by_program,
434 p_old_batch_rec => l_rec,
435 p_new_batch_rec => l_new_rec,
436 x_cascade_update => x_cascade_update,
437 x_return_status => x_return_status);
438
439 IF x_return_status <> fnd_api.g_ret_sts_success THEN
440 RAISE fnd_api.g_exc_error;
441 END IF;
442
443 x_OBJECT_VERSION_NUMBER := NVL(x_OBJECT_VERSION_NUMBER,1) + 1;
444
445 IF x_cascade_update = 'Y' THEN
446
447 UPDATE ar_interest_lines iil
448 SET process_status = 'N',
449 object_version_number = NVL(object_version_number,1) + 1
450 WHERE EXISTS
451 (SELECT interest_header_id
452 FROM ar_interest_headers ii
453 WHERE iil.interest_header_id = ii.interest_header_id
454 AND ii.interest_batch_id = p_interest_batch_id)
455 AND iil.process_status <> 'S';
456
457 UPDATE ar_interest_headers
458 SET process_status = 'N',
459 object_version_number = NVL(object_version_number,1) + 1
460 WHERE interest_batch_id = p_interest_batch_id
461 AND process_status <> 'S';
462
463 END IF;
464
465
466 UPDATE ar_interest_batches SET
467 BATCH_STATUS = P_BATCH_STATUS,
468 transferred_status = p_transferred_status,
469 gl_date = DECODE(p_gl_date,NULL,gl_date,p_gl_date),
470 LAST_UPDATE_DATE = SYSDATE,
471 LAST_UPDATED_BY = NVL(arp_global.last_updated_by,-1),
472 LAST_UPDATE_LOGIN = NVL(arp_global.LAST_UPDATE_LOGIN,-1),
473 object_version_number = x_OBJECT_VERSION_NUMBER
474 WHERE interest_batch_id = P_INTEREST_BATCH_ID;
475
476 arp_util.debug('update_batch -');
477 EXCEPTION
478 WHEN fnd_api.g_exc_error THEN
479 rollback to update_batch;
480 x_return_status := fnd_api.g_ret_sts_error;
481
482 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
483 p_count => x_msg_count,
484 p_data => x_msg_data);
485 IF x_msg_count > 1 THEN
486 x_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_LAST,FND_API.G_FALSE );
487 END IF;
488
489 WHEN OTHERS THEN
490 rollback to update_batch;
491 x_return_status := fnd_api.g_ret_sts_unexp_error;
492 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
493 fnd_message.set_token('ERROR' ,SQLERRM);
494 fnd_msg_pub.add;
495 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
496 p_count => x_msg_count,
497 p_data => x_msg_data);
498 END;
499
500 END AR_INTEREST_BATCHES_PKG;