[Home] [Help]
PACKAGE BODY: APPS.AR_INTEREST_HEADERS_PKG
Source
1 PACKAGE BODY AR_INTEREST_HEADERS_PKG AS
2 /*$Header: ARIIINVB.pls 120.3 2006/03/28 06:10:23 hyu noship $*/
3
4 g_current_version NUMBER;
5 g_not VARCHAR2(30):= ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('AR_PROCESS_STATUS','N');
6 g_error VARCHAR2(30):= ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('AR_PROCESS_STATUS','E');
7 g_success VARCHAR2(30):= ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('AR_PROCESS_STATUS','S');
8 g_draft VARCHAR2(30):= ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('AR_RUN_TYPE','D');
12 (p_interest_header_id IN NUMBER)
9 g_final VARCHAR2(30):= ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('AR_RUN_TYPE','F');
10
11 FUNCTION get_header_amount
13 RETURN NUMBER
14 IS
15 CURSOR c IS
16 SELECT SUM(NVL(iil.INTEREST_CHARGED,0))
17 FROM ar_interest_lines iil,
18 ar_interest_headers ii
19 WHERE ii.interest_header_id = iil.interest_header_id
20 AND ii.interest_header_id = p_interest_header_id;
21 l_header_amount number;
22 BEGIN
23 arp_util.debug('get_header_amount +');
24 arp_util.debug(' p_interest_header_id :'||p_interest_header_id);
25 OPEN c;
26 FETCH c INTO l_header_amount;
27 CLOSE c;
28 arp_util.debug(' result :'||l_header_amount);
29 arp_util.debug('get_header_amount -');
30 RETURN l_header_amount;
31 END;
32
33 PROCEDURE Lock_header
34 (P_INTEREST_HEADER_ID IN NUMBER,
35 P_INTEREST_BATCH_ID IN NUMBER,
36 P_CUSTOMER_ID IN NUMBER,
37 P_CUSTOMER_SITE_USE_ID IN NUMBER,
38 P_HEADER_TYPE IN VARCHAR2,
39 P_CURRENCY_CODE IN VARCHAR2,
40 P_LATE_CHARGE_CALCULATION_TRX IN VARCHAR2,
41 P_CREDIT_ITEMS_FLAG IN VARCHAR2,
42 P_DISPUTED_TRANSACTIONS_FLAG IN VARCHAR2,
43 P_PAYMENT_GRACE_DAYS IN NUMBER,
44 P_LATE_CHARGE_TERM_ID IN NUMBER,
45 P_INTEREST_PERIOD_DAYS IN NUMBER,
46 P_INTEREST_CALCULATION_PERIOD IN VARCHAR2,
47 P_CHARGE_ON_FINANCE_CHARGE_FLG IN VARCHAR2,
48 P_HOLD_CHARGED_INVOICES_FLAG IN VARCHAR2,
49 P_MESSAGE_TEXT_ID IN NUMBER,
50 P_MULTIPLE_INTEREST_RATES_FLAG IN VARCHAR2,
51 P_CHARGE_BEGIN_DATE IN DATE,
52 P_CUST_ACCT_PROFILE_AMT_ID IN NUMBER,
53 P_EXCHANGE_RATE IN NUMBER,
54 P_EXCHANGE_RATE_TYPE IN VARCHAR2,
55 P_MIN_FC_INVOICE_OVERDUE_TYPE IN VARCHAR2,
56 P_MIN_FC_INVOICE_AMOUNT IN NUMBER,
57 P_MIN_FC_INVOICE_PERCENT IN NUMBER,
58 P_MIN_FC_BALANCE_OVERDUE_TYPE IN VARCHAR2,
59 P_MIN_FC_BALANCE_AMOUNT IN NUMBER,
60 P_MIN_FC_BALANCE_PERCENT IN NUMBER,
61 P_MIN_INTEREST_CHARGE IN NUMBER,
62 P_MAX_INTEREST_CHARGE IN NUMBER,
63 P_INTEREST_TYPE IN VARCHAR2,
64 P_INTEREST_RATE IN NUMBER,
65 P_INTEREST_FIXED_AMOUNT IN NUMBER,
66 P_INTEREST_SCHEDULE_ID IN NUMBER,
67 P_PENALTY_TYPE IN VARCHAR2,
68 P_PENALTY_RATE IN NUMBER,
69 P_PENALTY_FIXED_AMOUNT IN NUMBER,
70 P_PENALTY_SCHEDULE_ID IN NUMBER,
71 P_LAST_ACCRUE_CHARGE_DATE IN DATE,
72 P_CUSTOMER_PROFILE_ID IN NUMBER,
73 P_COLLECTOR_ID IN NUMBER,
74 P_LEGAL_ENTITY_ID IN NUMBER,
75 P_LAST_UPDATE_DATE IN DATE,
76 P_LAST_UPDATED_BY IN NUMBER,
77 P_LAST_UPDATE_LOGIN IN NUMBER,
78 P_CREATED_BY IN NUMBER,
79 P_CREATION_DATE IN DATE,
80 P_ORG_ID IN NUMBER,
81 P_PROCESS_MESSAGE IN VARCHAR2,
82 P_PROCESS_STATUS IN VARCHAR2,
83 P_CUST_TRX_TYPE_ID IN NUMBER,
84 P_OBJECT_VERSION_NUMBER IN NUMBER,
85 x_return_status OUT NOCOPY VARCHAR2,
86 x_msg_count OUT NOCOPY NUMBER,
87 x_msg_data OUT NOCOPY VARCHAR2)
88 IS
89 CURSOR C IS
90 SELECT *
91 FROM AR_INTEREST_HEADERS
92 WHERE interest_header_id = p_interest_header_id
93 FOR UPDATE OF interest_header_Id NOWAIT;
94 Recinfo C%ROWTYPE;
95 l_continue VARCHAR2(1) := 'Y';
96 BEGIN
97 arp_util.debug('lock_header +');
98
99 OPEN C;
100 FETCH C INTO Recinfo;
101 IF (C%NOTFOUND) THEN
102 x_return_status := fnd_api.g_ret_sts_error;
103 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
104 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
105 p_count => x_msg_count,
106 p_data => x_msg_data);
107 l_continue := 'N';
108 END IF;
109 CLOSE C;
110
111 IF l_continue = 'Y' THEN
112 IF (P_INTEREST_HEADER_ID = Recinfo.INTEREST_HEADER_ID)
113 AND (P_INTEREST_BATCH_ID = Recinfo.INTEREST_BATCH_ID)
114 AND (NVL(P_CUSTOMER_ID,fnd_api.g_miss_num) = NVL(Recinfo.CUSTOMER_ID,fnd_api.g_miss_num))
115 AND (NVL(P_CUSTOMER_SITE_USE_ID,fnd_api.g_miss_num) = NVL(Recinfo.CUSTOMER_SITE_USE_ID,fnd_api.g_miss_num))
116 AND (NVL(P_HEADER_TYPE,fnd_api.g_miss_char) = NVL(Recinfo.HEADER_TYPE,fnd_api.g_miss_char))
117 AND (NVL(P_CURRENCY_CODE,fnd_api.g_miss_char) = NVL(Recinfo.CURRENCY_CODE,fnd_api.g_miss_char))
118 AND (NVL(P_LATE_CHARGE_CALCULATION_TRX,fnd_api.g_miss_char) = NVL(Recinfo.LATE_CHARGE_CALCULATION_TRX,fnd_api.g_miss_char))
119 AND (NVL(P_CREDIT_ITEMS_FLAG,fnd_api.g_miss_char) = NVL(Recinfo.CREDIT_ITEMS_FLAG,fnd_api.g_miss_char))
120 AND (NVL(P_DISPUTED_TRANSACTIONS_FLAG,fnd_api.g_miss_char)= NVL(Recinfo.DISPUTED_TRANSACTIONS_FLAG,fnd_api.g_miss_char))
121 AND (NVL(P_PAYMENT_GRACE_DAYS,fnd_api.g_miss_num) = NVL(Recinfo.PAYMENT_GRACE_DAYS,fnd_api.g_miss_num))
122 AND (NVL(P_LATE_CHARGE_TERM_ID,fnd_api.g_miss_num) = NVL(Recinfo.LATE_CHARGE_TERM_ID,fnd_api.g_miss_num))
123 AND (NVL(P_INTEREST_PERIOD_DAYS,fnd_api.g_miss_num) = NVL(Recinfo.INTEREST_PERIOD_DAYS,fnd_api.g_miss_num))
124 AND (NVL(P_INTEREST_CALCULATION_PERIOD,fnd_api.g_miss_char)=
125 NVL(Recinfo.INTEREST_CALCULATION_PERIOD,fnd_api.g_miss_char))
126 AND (NVL(P_CHARGE_ON_FINANCE_CHARGE_FLG,fnd_api.g_miss_char)=
127 NVL(Recinfo.CHARGE_ON_FINANCE_CHARGE_FLAG,fnd_api.g_miss_char))
128 AND (NVL(P_HOLD_CHARGED_INVOICES_FLAG,fnd_api.g_miss_char)= NVL(Recinfo.HOLD_CHARGED_INVOICES_FLAG,fnd_api.g_miss_char))
129 AND (NVL(P_MESSAGE_TEXT_ID,fnd_api.g_miss_num) = NVL(Recinfo.MESSAGE_TEXT_ID,fnd_api.g_miss_num))
130 AND (NVL(P_MULTIPLE_INTEREST_RATES_FLAG,fnd_api.g_miss_char)= NVL(Recinfo.MULTIPLE_INTEREST_RATES_FLAG,fnd_api.g_miss_char))
131 AND (NVL(P_CHARGE_BEGIN_DATE,fnd_api.g_miss_date) = NVL(Recinfo.CHARGE_BEGIN_DATE,fnd_api.g_miss_date))
132 AND (NVL(P_CUST_ACCT_PROFILE_AMT_ID,fnd_api.g_miss_num) = NVL(Recinfo.CUST_ACCT_PROFILE_AMT_ID,fnd_api.g_miss_num))
133 AND (NVL(P_EXCHANGE_RATE,fnd_api.g_miss_num) = NVL(Recinfo.EXCHANGE_RATE,fnd_api.g_miss_num))
134 AND (NVL(P_EXCHANGE_RATE_TYPE,fnd_api.g_miss_char) = NVL(Recinfo.EXCHANGE_RATE_TYPE,fnd_api.g_miss_char))
135 AND (NVL(P_MIN_FC_INVOICE_OVERDUE_TYPE,fnd_api.g_miss_char)= NVL(Recinfo.MIN_FC_INVOICE_OVERDUE_TYPE,fnd_api.g_miss_char))
136 AND (NVL(P_MIN_FC_INVOICE_AMOUNT,fnd_api.g_miss_num) = NVL(Recinfo.MIN_FC_INVOICE_AMOUNT,fnd_api.g_miss_num))
137 AND (NVL(P_MIN_FC_INVOICE_PERCENT,fnd_api.g_miss_num) = NVL(Recinfo.MIN_FC_INVOICE_PERCENT,fnd_api.g_miss_num))
138 AND (NVL(P_MIN_FC_BALANCE_OVERDUE_TYPE,fnd_api.g_miss_char)= NVL(Recinfo.MIN_FC_BALANCE_OVERDUE_TYPE,fnd_api.g_miss_char))
139 AND (NVL(P_MIN_FC_BALANCE_AMOUNT,fnd_api.g_miss_num) = NVL(Recinfo.MIN_FC_BALANCE_AMOUNT,fnd_api.g_miss_num))
140 AND (NVL(P_MIN_FC_BALANCE_PERCENT,fnd_api.g_miss_num) = NVL(Recinfo.MIN_FC_BALANCE_PERCENT,fnd_api.g_miss_num))
141 AND (NVL(P_MIN_INTEREST_CHARGE,fnd_api.g_miss_num) = NVL(Recinfo.MIN_INTEREST_CHARGE,fnd_api.g_miss_num))
142 AND (NVL(P_MAX_INTEREST_CHARGE,fnd_api.g_miss_num) = NVL(Recinfo.MAX_INTEREST_CHARGE,fnd_api.g_miss_num))
143 AND (NVL(P_INTEREST_TYPE,fnd_api.g_miss_char) = NVL(Recinfo.interest_type,fnd_api.g_miss_char))
144 AND (NVL(P_INTEREST_RATE,fnd_api.g_miss_num) = NVL(Recinfo.interest_rate,fnd_api.g_miss_num))
145 AND (NVL(P_INTEREST_FIXED_AMOUNT,fnd_api.g_miss_num) = NVL(Recinfo.INTEREST_FIXED_AMOUNT,fnd_api.g_miss_num))
146 AND (NVL(P_INTEREST_SCHEDULE_ID,fnd_api.g_miss_num) = NVL(Recinfo.INTEREST_SCHEDULE_ID,fnd_api.g_miss_num))
147 AND (NVL(P_PENALTY_TYPE,fnd_api.g_miss_char) = NVL(Recinfo.PENALTY_TYPE,fnd_api.g_miss_char))
148 AND (NVL(P_PENALTY_RATE,fnd_api.g_miss_num) = NVL(Recinfo.PENALTY_RATE,fnd_api.g_miss_num))
149 AND (NVL(P_PENALTY_FIXED_AMOUNT,fnd_api.g_miss_num) = NVL(Recinfo.PENALTY_FIXED_AMOUNT,fnd_api.g_miss_num))
150 AND (NVL(P_PENALTY_SCHEDULE_ID,fnd_api.g_miss_num) = NVL(Recinfo.PENALTY_SCHEDULE_ID,fnd_api.g_miss_num))
151 AND (NVL(P_LAST_ACCRUE_CHARGE_DATE,fnd_api.g_miss_date)= NVL(Recinfo.LAST_ACCRUE_CHARGE_DATE,fnd_api.g_miss_date))
152 AND (NVL(P_CUSTOMER_PROFILE_ID,fnd_api.g_miss_num) = NVL(Recinfo.CUSTOMER_PROFILE_ID,fnd_api.g_miss_num))
153 AND (NVL(P_COLLECTOR_ID,fnd_api.g_miss_num) = NVL(Recinfo.COLLECTOR_ID,fnd_api.g_miss_num))
154 AND (NVL(P_LEGAL_ENTITY_ID,fnd_api.g_miss_num) = NVL(Recinfo.LEGAL_ENTITY_ID,fnd_api.g_miss_num))
155 AND (NVL(P_LAST_UPDATE_DATE,fnd_api.g_miss_date) = NVL(Recinfo.LAST_UPDATE_DATE,fnd_api.g_miss_date))
156 AND (NVL(P_LAST_UPDATED_BY,fnd_api.g_miss_num) = NVL(Recinfo.LAST_UPDATED_BY,fnd_api.g_miss_num))
157 AND (NVL(P_LAST_UPDATE_LOGIN,fnd_api.g_miss_num) = NVL(Recinfo.LAST_UPDATE_LOGIN,fnd_api.g_miss_num))
158 AND (NVL(P_CREATED_BY,fnd_api.g_miss_num) = NVL(Recinfo.CREATED_BY,fnd_api.g_miss_num))
159 AND (NVL(P_CREATION_DATE,fnd_api.g_miss_date) = NVL(Recinfo.CREATION_DATE,fnd_api.g_miss_date))
160 AND (NVL(P_ORG_ID,fnd_api.g_miss_num) = NVL(Recinfo.ORG_ID,fnd_api.g_miss_num))
161 AND (NVL(P_PROCESS_MESSAGE,fnd_api.g_miss_char) = NVL(Recinfo.PROCESS_MESSAGE,fnd_api.g_miss_char))
162 AND (NVL(P_PROCESS_STATUS,fnd_api.g_miss_char) = NVL(Recinfo.PROCESS_STATUS,fnd_api.g_miss_char))
163 AND (NVL(P_CUST_TRX_TYPE_ID,fnd_api.g_miss_num) = NVL(Recinfo.CUST_TRX_TYPE_ID,fnd_api.g_miss_num))
164 AND (NVL(P_OBJECT_VERSION_NUMBER,1) = NVL(Recinfo.OBJECT_VERSION_NUMBER,1))
165 THEN
166 RETURN;
167 ELSE
168 arp_util.debug(' Header Record Changed');
169 x_return_status := fnd_api.g_ret_sts_error;
170 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
171 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
172 p_count => x_msg_count,
173 p_data => x_msg_data);
174 END IF;
175 END IF;
176 arp_util.debug('lock_header -');
177 END Lock_Header;
181 (p_action IN VARCHAR2,
178
179
180 PROCEDURE validate_header
182 p_old_rec IN ar_interest_headers%ROWTYPE,
183 p_new_rec IN ar_interest_headers%ROWTYPE,
184 p_updated_by_program IN VARCHAR2 DEFAULT 'ARIINR',
185 x_return_status IN OUT NOCOPY VARCHAR2)
186 IS
187 CURSOR c IS
188 SELECT transferred_status
189 FROM ar_interest_batches
190 WHERE interest_batch_id = p_old_rec.interest_batch_id;
191 l_flag VARCHAR2(1);
192 BEGIN
193 arp_util.debug('validate_header +');
194 arp_util.debug(' p_action :'||p_action);
195 IF p_action = 'UPDATE' THEN
196
197 IF p_new_rec.process_status NOT IN ('E','S','N') THEN
198 arp_util.debug('Column process_status can take values from E S or N only');
199 FND_MESSAGE.SET_NAME( 'AR', 'AR_ONLY_VALUE_ALLOWED' );
200 FND_MESSAGE.SET_NAME( 'COLUMN', 'PROCESS_STATUS' );
201 FND_MESSAGE.SET_TOKEN( 'VALUES', g_error||','||g_success||','||g_not);
202 FND_MSG_PUB.ADD;
203 x_return_status := FND_API.G_RET_STS_ERROR;
204 END IF;
205
206 IF p_old_rec.process_status = 'S' AND p_new_rec.process_status = 'S' THEN
207 arp_util.debug('Column process_status not updatable');
208 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_COLUMN' );
209 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'batch_status');
210 FND_MSG_PUB.ADD;
211 x_return_status := FND_API.G_RET_STS_ERROR;
212 END IF;
213
214 IF (p_old_rec.process_status <> 'S')
215 AND (p_new_rec.process_status = 'S' OR p_new_rec.process_status = 'E')
216 THEN
217 IF p_updated_by_program = 'ARIINR' THEN
218 arp_util.debug('Only creation of late charge document set the status to S');
219 fnd_message.set_name('AR', 'AR_STATUS_RESERVE_FOR_SRS');
220 fnd_msg_pub.add;
221 x_return_status := FND_API.G_RET_STS_ERROR;
222 END IF;
223 END IF;
224 END IF;
225
226 IF p_action = 'DELETE' THEN
227 IF p_old_rec.process_status = 'S' THEN
228 arp_util.debug('Column process_status not updatable');
229 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_COLUMN' );
230 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'batch_status');
231 FND_MSG_PUB.ADD;
232 x_return_status := FND_API.G_RET_STS_ERROR;
233 ELSE
234 OPEN c;
235 FETCH c INTO l_flag;
236 IF c%FOUND THEN
237 IF l_flag = 'S' THEN
238 arp_util.debug('Column process_status not updatable');
239 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_COLUMN' );
240 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'batch_status');
241 FND_MSG_PUB.ADD;
242 x_return_status := FND_API.G_RET_STS_ERROR;
243 END IF;
244 END IF;
245 CLOSE c;
246 END IF;
247 END IF;
248 arp_util.debug('validate_header -');
249 END;
250
251
252
253
254 PROCEDURE update_header
255 (p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
256 P_INTEREST_HEADER_ID IN NUMBER,
257 P_PROCESS_MESSAGE IN VARCHAR2,
258 P_PROCESS_STATUS IN VARCHAR2,
259 p_updated_by_program IN VARCHAR2 DEFAULT 'ARIINR',
260 x_object_version_number IN OUT NOCOPY NUMBER,
261 x_return_status OUT NOCOPY VARCHAR2,
262 x_msg_count OUT NOCOPY NUMBER,
263 x_msg_data OUT NOCOPY VARCHAR2)
264 IS
265 CURSOR c IS
266 SELECT *
267 FROM ar_interest_headers
268 WHERE interest_header_id = P_INTEREST_header_ID
269 FOR UPDATE OF INTEREST_header_ID;
270 l_rec ar_interest_headers%ROWTYPE;
271 l_new_rec ar_interest_headers%ROWTYPE;
272 BEGIN
273 arp_util.debug('update_header +');
274 SAVEPOINT update_header;
275
276 IF fnd_api.to_boolean(p_init_msg_list) THEN
277 fnd_msg_pub.initialize;
278 END IF;
279
280 x_return_status := fnd_api.G_RET_STS_SUCCESS;
281
282 OPEN c;
283 FETCH c INTO l_rec;
284 CLOSE c;
285
286 IF l_rec.INTEREST_header_ID IS NULL THEN
287 fnd_message.set_name('AR', 'HZ_API_NO_RECORD');
288 fnd_message.set_token('RECORD', 'ar_interest_headers');
289 fnd_message.set_token('VALUE',
290 NVL(TO_CHAR(P_INTEREST_header_ID), 'null'));
291 fnd_msg_pub.add;
292 RAISE fnd_api.g_exc_error;
293 END IF;
294
295 IF NOT ((x_object_version_number IS NULL AND
296 l_rec.OBJECT_VERSION_NUMBER IS NULL) OR
297 (x_object_version_number IS NOT NULL AND
298 l_rec.OBJECT_VERSION_NUMBER IS NOT NULL AND
299 x_object_version_number = l_rec.OBJECT_VERSION_NUMBER))
300 THEN
301 fnd_message.set_name('AR', 'HZ_API_RECORD_CHANGED');
302 fnd_message.set_token('TABLE', 'ar_interest_batches');
303 fnd_msg_pub.add;
304 RAISE fnd_api.g_exc_error;
305 END IF;
306
307 l_new_rec.process_status := P_PROCESS_STATUS;
308 l_new_rec.process_message := P_PROCESS_MESSAGE;
309
310 validate_header
311 (p_action => 'UPDATE',
312 p_old_rec => l_rec,
313 p_new_rec => l_new_rec,
314 p_updated_by_program => p_updated_by_program,
315 x_return_status => x_return_status);
316
317 IF x_return_status <> fnd_api.g_ret_sts_success THEN
318 RAISE fnd_api.g_exc_error;
319 END IF;
320
321 x_object_version_number := NVL(x_object_version_number,1) + 1;
322
323 UPDATE AR_INTEREST_HEADERS
324 SET
325 PROCESS_MESSAGE = P_PROCESS_MESSAGE ,
329 LAST_UPDATE_LOGIN = NVL(arp_global.LAST_UPDATE_LOGIN,-1),
326 PROCESS_STATUS = P_PROCESS_STATUS ,
327 LAST_UPDATE_DATE = SYSDATE,
328 LAST_UPDATED_BY = NVL(arp_global.last_updated_by,-1),
330 object_version_number = x_object_version_number
331 WHERE INTEREST_HEADER_ID = p_INTEREST_HEADER_ID;
332 arp_util.debug('lock_header -');
333 EXCEPTION
334 WHEN fnd_api.g_exc_error THEN
335 ROLLBACK TO SAVEPOINT Update_header;
336 x_return_status := fnd_api.g_ret_sts_error;
337 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
338 p_count => x_msg_count,
339 p_data => x_msg_data);
340 IF x_msg_count > 1 THEN
341 x_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_LAST,FND_API.G_FALSE );
342 END IF;
343 arp_util.debug('EXCEPTION AR_INTEREST_HEADERS_PKG.update_header :'||x_msg_data);
344 WHEN OTHERS THEN
345 ROLLBACK TO SAVEPOINT Update_header;
346 x_return_status := fnd_api.g_ret_sts_unexp_error;
347 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
348 fnd_message.set_token('ERROR' ,SQLERRM);
349 fnd_msg_pub.add;
350 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
351 p_count => x_msg_count,
352 p_data => x_msg_data);
353 arp_util.debug('EXCEPTION OTHERS AR_INTEREST_HEADERS_PKG.update_header :'||SQLERRM);
354 END Update_header;
355
356 PROCEDURE Delete_header
357 (p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
358 p_interest_header_id IN NUMBER,
359 x_object_version_number IN NUMBER,
360 x_return_status OUT NOCOPY VARCHAR2,
361 x_msg_count OUT NOCOPY NUMBER,
362 x_msg_data OUT NOCOPY VARCHAR2)
363 IS
364 CURSOR c IS
365 SELECT *
366 FROM ar_interest_headers
367 WHERE interest_header_id = P_INTEREST_header_ID
368 FOR UPDATE OF INTEREST_header_ID;
369 l_rec ar_interest_headers%ROWTYPE;
370 l_new_rec ar_interest_headers%ROWTYPE;
371 BEGIN
372 SAVEPOINT delete_header;
373
374 IF fnd_api.to_boolean(p_init_msg_list) THEN
375 fnd_msg_pub.initialize;
376 END IF;
377
378 x_return_status := fnd_api.G_RET_STS_SUCCESS;
379
380 OPEN c;
381 FETCH c INTO l_rec;
382 CLOSE c;
383
384 IF l_rec.INTEREST_header_ID IS NULL THEN
385 fnd_message.set_name('AR', 'HZ_API_NO_RECORD');
386 fnd_message.set_token('RECORD', 'ar_interest_headers');
387 fnd_message.set_token('VALUE',
388 NVL(TO_CHAR(P_INTEREST_header_ID), 'null'));
389 fnd_msg_pub.add;
390 RAISE fnd_api.g_exc_error;
391 END IF;
392
393 validate_header
394 (p_action => 'DELETE',
395 p_old_rec => l_rec,
396 p_new_rec => l_new_rec,
397 x_return_status => x_return_status);
398
399 IF x_return_status <> fnd_api.g_ret_sts_success THEN
400 RAISE fnd_api.g_exc_error;
401 END IF;
402
403 DELETE FROM ar_interest_lines
404 WHERE interest_header_id = p_interest_header_id;
405
406 DELETE FROM AR_INTEREST_HEADERS
407 WHERE interest_header_id = p_interest_header_id;
408
409 EXCEPTION
410 WHEN fnd_api.g_exc_error THEN
411 ROLLBACK TO SAVEPOINT delete_header;
412 x_return_status := fnd_api.g_ret_sts_error;
413 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
414 p_count => x_msg_count,
415 p_data => x_msg_data);
416 IF x_msg_count > 1 THEN
417 x_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_LAST,FND_API.G_FALSE );
418 END IF;
419 arp_util.debug('EXCEPTION AR_INTEREST_HEADERS_PKG.delete_header :'||x_msg_data);
420 WHEN OTHERS THEN
421 ROLLBACK TO SAVEPOINT Update_header;
422 x_return_status := fnd_api.g_ret_sts_unexp_error;
423 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
424 fnd_message.set_token('ERROR' ,SQLERRM);
425 fnd_msg_pub.add;
426 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
427 p_count => x_msg_count,
428 p_data => x_msg_data);
429 arp_util.debug('EXCEPTION OTHERS AR_INTEREST_HEADERS_PKG.delete_header :'||SQLERRM);
430 END Delete_header;
431
432
433 END AR_INTEREST_HEADERS_PKG;