[Home] [Help]
PACKAGE BODY: APPS.AR_CHARGE_SCHEDULE_PKG
Source
4 ----------------------------------
1 PACKAGE BODY ar_charge_schedule_pkg AS
2 /* $Header: ARSCAMTB.pls 120.4 2011/07/19 00:22:50 dgaurab ship $ */
3
5 -- Table Handler for tier set row
6 ----------------------------------
7 PROCEDURE Insert_schedule_Row
8 (P_SCHEDULE_ID IN NUMBER,
9 P_SCHEDULE_NAME IN VARCHAR2,
10 P_SCHEDULE_DESCRIPTION IN VARCHAR2,
11 P_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
12 P_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
13 P_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
14 P_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
15 P_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
16 P_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
17 P_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
18 P_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
19 P_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
20 P_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
21 P_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
22 P_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
23 P_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
24 P_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
25 P_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
26 P_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
27 P_OBJECT_VERSION_NUMBER IN NUMBER,
28 x_return_status IN OUT NOCOPY VARCHAR2)
29 IS
30 BEGIN
31 arp_standard.debug('Insert_schedule_Row +');
32 INSERT INTO AR_CHARGE_SCHEDULES
33 (SCHEDULE_ID ,
34 SCHEDULE_NAME ,
35 SCHEDULE_DESCRIPTION,
36 ATTRIBUTE_CATEGORY,
37 ATTRIBUTE1 ,
38 ATTRIBUTE2 ,
39 ATTRIBUTE3 ,
40 ATTRIBUTE4 ,
41 ATTRIBUTE5 ,
42 ATTRIBUTE6 ,
43 ATTRIBUTE7 ,
44 ATTRIBUTE8 ,
45 ATTRIBUTE9 ,
46 ATTRIBUTE10 ,
47 ATTRIBUTE11 ,
48 ATTRIBUTE12 ,
49 ATTRIBUTE13 ,
50 ATTRIBUTE14 ,
51 ATTRIBUTE15 ,
52 OBJECT_VERSION_NUMBER,
53 CREATED_BY ,
54 CREATION_DATE ,
55 LAST_UPDATED_BY ,
56 LAST_UPDATE_DATE ,
57 LAST_UPDATE_LOGIN)
58 VALUES
59 (P_SCHEDULE_ID ,
60 P_SCHEDULE_NAME ,
61 DECODE(P_SCHEDULE_DESCRIPTION,FND_API.G_MISS_CHAR,NULL,P_SCHEDULE_DESCRIPTION),
62 DECODE(P_ATTRIBUTE_CATEGORY,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE_CATEGORY),
63 DECODE(P_ATTRIBUTE1 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE1),
67 DECODE(P_ATTRIBUTE5 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE5),
64 DECODE(P_ATTRIBUTE2 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE2),
65 DECODE(P_ATTRIBUTE3 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE3),
66 DECODE(P_ATTRIBUTE4 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE4),
68 DECODE(P_ATTRIBUTE6 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE6),
69 DECODE(P_ATTRIBUTE7 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE7),
70 DECODE(P_ATTRIBUTE8 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE8),
71 DECODE(P_ATTRIBUTE9 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE9),
72 DECODE(P_ATTRIBUTE10 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE10),
73 DECODE(P_ATTRIBUTE11 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE11),
74 DECODE(P_ATTRIBUTE12 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE12),
75 DECODE(P_ATTRIBUTE13 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE13),
76 DECODE(P_ATTRIBUTE14 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE14),
77 DECODE(P_ATTRIBUTE15 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE15),
78 p_object_version_number,
79 NVL(FND_GLOBAL.user_id,-1),
80 TRUNC(SYSDATE),
81 NVL(FND_GLOBAL.user_id,-1),
82 TRUNC(SYSDATE),
83 NVL(FND_GLOBAL.login_id,-1));
84
85 arp_standard.debug('Insert_schedule_Row -');
86 EXCEPTION
87 WHEN OTHERS THEN
88 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
89 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
90 FND_MESSAGE.SET_TOKEN('ERROR' ,'Insert_schedule_Row:'||SQLERRM);
91 FND_MSG_PUB.ADD;
92 END Insert_schedule_Row;
93
94
95
96 PROCEDURE Update_schedule_Row
97 (P_SCHEDULE_ID IN NUMBER,
98 P_SCHEDULE_DESCRIPTION IN VARCHAR2,
99 P_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
100 P_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
101 P_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
102 P_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
103 P_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
104 P_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
105 P_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
106 P_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
107 P_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
108 P_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
109 P_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
110 P_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
111 P_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
112 P_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
113 P_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
114 P_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
115 X_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
116 x_return_status IN OUT NOCOPY VARCHAR2)
117 IS
118 BEGIN
119 arp_standard.debug('Insert_schedule_Row +');
120 UPDATE AR_CHARGE_SCHEDULES SET
121 SCHEDULE_DESCRIPTION = DECODE(P_SCHEDULE_DESCRIPTION,
122 FND_API.G_MISS_CHAR, NULL,
123 NULL , SCHEDULE_DESCRIPTION, P_SCHEDULE_DESCRIPTION ),
124 ATTRIBUTE_CATEGORY = DECODE(P_ATTRIBUTE_CATEGORY,
125 FND_API.G_MISS_CHAR, NULL,
126 NULL , ATTRIBUTE_CATEGORY, P_ATTRIBUTE_CATEGORY),
127 ATTRIBUTE1 = DECODE(P_ATTRIBUTE1,
128 FND_API.G_MISS_CHAR, NULL,
129 NULL , ATTRIBUTE1, P_ATTRIBUTE1),
130 ATTRIBUTE2 = DECODE(P_ATTRIBUTE2,
131 FND_API.G_MISS_CHAR, NULL,
132 NULL , ATTRIBUTE2, P_ATTRIBUTE2),
133 ATTRIBUTE3 = DECODE(P_ATTRIBUTE3,
134 FND_API.G_MISS_CHAR, NULL,
135 NULL , ATTRIBUTE3, P_ATTRIBUTE3),
136 ATTRIBUTE4 = DECODE(P_ATTRIBUTE4,
137 FND_API.G_MISS_CHAR, NULL,
138 NULL , ATTRIBUTE4, P_ATTRIBUTE4),
139 ATTRIBUTE5 = DECODE(P_ATTRIBUTE5,
140 FND_API.G_MISS_CHAR, NULL,
141 NULL , ATTRIBUTE5, P_ATTRIBUTE5),
142 ATTRIBUTE6 = DECODE(P_ATTRIBUTE6,
143 FND_API.G_MISS_CHAR, NULL,
144 NULL , ATTRIBUTE6, P_ATTRIBUTE6),
145 ATTRIBUTE7 = DECODE(P_ATTRIBUTE7,
146 FND_API.G_MISS_CHAR, NULL,
147 NULL , ATTRIBUTE7, P_ATTRIBUTE7),
148 ATTRIBUTE8 = DECODE(P_ATTRIBUTE8,
149 FND_API.G_MISS_CHAR, NULL,
150 NULL , ATTRIBUTE8, P_ATTRIBUTE8),
151 ATTRIBUTE9 = DECODE(P_ATTRIBUTE9,
152 FND_API.G_MISS_CHAR, NULL,
153 NULL , ATTRIBUTE9, P_ATTRIBUTE9),
154 ATTRIBUTE10 = DECODE(P_ATTRIBUTE10,
155 FND_API.G_MISS_CHAR, NULL,
156 NULL , ATTRIBUTE10, P_ATTRIBUTE10),
157 ATTRIBUTE11 = DECODE(P_ATTRIBUTE11,
158 FND_API.G_MISS_CHAR, NULL,
159 NULL , ATTRIBUTE11, P_ATTRIBUTE11),
160 ATTRIBUTE12 = DECODE(P_ATTRIBUTE12,
161 FND_API.G_MISS_CHAR, NULL,
162 NULL , ATTRIBUTE12, P_ATTRIBUTE12),
163 ATTRIBUTE13 = DECODE(P_ATTRIBUTE13,
164 FND_API.G_MISS_CHAR, NULL,
165 NULL , ATTRIBUTE13, P_ATTRIBUTE13),
166 ATTRIBUTE14 = DECODE(P_ATTRIBUTE14,
170 FND_API.G_MISS_CHAR, NULL,
167 FND_API.G_MISS_CHAR, NULL,
168 NULL , ATTRIBUTE14, P_ATTRIBUTE14),
169 ATTRIBUTE15 = DECODE(P_ATTRIBUTE15,
171 NULL , ATTRIBUTE15, P_ATTRIBUTE15),
172 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
173 LAST_UPDATE_DATE = TRUNC(SYSDATE),
174 LAST_UPDATED_BY = NVL(FND_GLOBAL.user_id,-1),
175 LAST_UPDATE_LOGIN = NVL(FND_GLOBAL.login_id,-1)
176 WHERE SCHEDULE_ID = P_SCHEDULE_ID
177 RETURNING OBJECT_VERSION_NUMBER INTO x_OBJECT_VERSION_NUMBER;
178 arp_standard.debug('Insert_schedule_Row -');
179 EXCEPTION
180 WHEN OTHERS THEN
181 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
182 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
183 FND_MESSAGE.SET_TOKEN('ERROR' ,'Insert_schedule_Row:'||SQLERRM);
184 FND_MSG_PUB.ADD;
185 END Update_schedule_Row;
186
187
188
189 -------------------------------------------
190 -- Table Handler insert schedule header row
191 -------------------------------------------
192 PROCEDURE Insert_Head_Row
193 (P_SCHEDULE_HEADER_ID IN NUMBER,
194 P_SCHEDULE_ID IN NUMBER,
195 P_SCHEDULE_HEADER_TYPE IN VARCHAR2,
196 P_AGING_BUCKET_ID IN NUMBER,
197 P_START_DATE IN DATE,
198 P_END_DATE IN DATE,
199 P_STATUS IN VARCHAR2,
200 P_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
201 P_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
202 P_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
203 P_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
204 P_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
205 P_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
206 P_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
207 P_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
208 P_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
209 P_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
210 P_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
211 P_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
212 P_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
213 P_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
214 P_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
215 P_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
216 P_OBJECT_VERSION_NUMBER IN NUMBER,
217 x_return_status IN OUT NOCOPY VARCHAR2)
218 IS
219 BEGIN
220 arp_standard.debug('Insert_Head_Row +');
221 INSERT INTO AR_charge_SCHEDULE_HDRS
222 (SCHEDULE_HEADER_ID,
223 SCHEDULE_ID ,
224 SCHEDULE_HEADER_TYPE,
225 AGING_BUCKET_ID ,
226 START_DATE ,
227 END_DATE ,
228 STATUS ,
229 ATTRIBUTE_CATEGORY,
230 ATTRIBUTE1 ,
231 ATTRIBUTE2 ,
232 ATTRIBUTE3 ,
233 ATTRIBUTE4 ,
234 ATTRIBUTE5 ,
235 ATTRIBUTE6 ,
236 ATTRIBUTE7 ,
237 ATTRIBUTE8 ,
238 ATTRIBUTE9 ,
239 ATTRIBUTE10 ,
240 ATTRIBUTE11 ,
241 ATTRIBUTE12 ,
242 ATTRIBUTE13 ,
243 ATTRIBUTE14 ,
244 ATTRIBUTE15 ,
245 OBJECT_VERSION_NUMBER,
246 CREATED_BY ,
247 CREATION_DATE ,
248 LAST_UPDATED_BY ,
249 LAST_UPDATE_DATE ,
250 LAST_UPDATE_LOGIN)
251 VALUES
252 (P_SCHEDULE_HEADER_ID ,
253 P_SCHEDULE_ID ,
254 P_SCHEDULE_HEADER_TYPE,
255 P_AGING_BUCKET_ID ,
256 P_START_DATE ,
257 DECODE(P_END_DATE ,FND_API.G_MISS_DATE,NULL,P_END_DATE),
258 P_STATUS ,
259 DECODE(P_ATTRIBUTE_CATEGORY,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE_CATEGORY),
260 DECODE(P_ATTRIBUTE1 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE1),
261 DECODE(P_ATTRIBUTE2 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE2),
262 DECODE(P_ATTRIBUTE3 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE3),
263 DECODE(P_ATTRIBUTE4 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE4),
264 DECODE(P_ATTRIBUTE5 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE5),
265 DECODE(P_ATTRIBUTE6 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE6),
266 DECODE(P_ATTRIBUTE7 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE7),
267 DECODE(P_ATTRIBUTE8 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE8),
268 DECODE(P_ATTRIBUTE9 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE9),
269 DECODE(P_ATTRIBUTE10 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE10),
270 DECODE(P_ATTRIBUTE11 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE11),
271 DECODE(P_ATTRIBUTE12 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE12),
275 P_OBJECT_VERSION_NUMBER,
272 DECODE(P_ATTRIBUTE13 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE13),
273 DECODE(P_ATTRIBUTE14 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE14),
274 DECODE(P_ATTRIBUTE15 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE15),
276 NVL(FND_GLOBAL.user_id,-1),
277 TRUNC(SYSDATE),
278 NVL(FND_GLOBAL.user_id,-1),
279 TRUNC(SYSDATE),
280 NVL(FND_GLOBAL.login_id,-1));
281
282 arp_standard.debug('Insert_Head_Row -');
283 EXCEPTION
284 WHEN OTHERS THEN
285 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
286 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
287 FND_MESSAGE.SET_TOKEN('ERROR' ,'Insert_Head_Row:'||SQLERRM);
288 FND_MSG_PUB.ADD;
289 END Insert_Head_Row;
290
291
292
293
294
295
296
297 ----------------------------------
298 -- Table Handler Update header row
299 ----------------------------------
300 PROCEDURE Update_Head_Row
301 (P_SCHEDULE_HEADER_ID IN NUMBER,
302 P_END_DATE IN DATE,
303 P_STATUS IN VARCHAR2,
304 P_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
305 P_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
306 P_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
307 P_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
308 P_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
309 P_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
310 P_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
311 P_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
312 P_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
313 P_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
314 P_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
315 P_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
316 P_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
317 P_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
318 P_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
319 P_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
320 x_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
321 x_return_status IN OUT NOCOPY VARCHAR2)
322 IS
323 BEGIN
324 arp_standard.debug('Update_Head_Row +');
325
326 UPDATE ar_charge_schedule_hdrs SET
327 END_DATE = DECODE(P_END_DATE,
328 FND_API.G_MISS_DATE, NULL,
329 NULL , END_DATE, P_END_DATE ),
330 STATUS = DECODE(P_STATUS,
331 FND_API.G_MISS_CHAR, NULL,
332 NULL , STATUS, P_STATUS ),
333 ATTRIBUTE_CATEGORY = DECODE(P_ATTRIBUTE_CATEGORY,
334 FND_API.G_MISS_CHAR, NULL,
335 NULL , ATTRIBUTE_CATEGORY, P_ATTRIBUTE_CATEGORY),
336 ATTRIBUTE1 = DECODE(P_ATTRIBUTE1,
337 FND_API.G_MISS_CHAR, NULL,
338 NULL , ATTRIBUTE1, P_ATTRIBUTE1),
339 ATTRIBUTE2 = DECODE(P_ATTRIBUTE2,
340 FND_API.G_MISS_CHAR, NULL,
341 NULL , ATTRIBUTE2, P_ATTRIBUTE2),
342 ATTRIBUTE3 = DECODE(P_ATTRIBUTE3,
343 FND_API.G_MISS_CHAR, NULL,
344 NULL , ATTRIBUTE3, P_ATTRIBUTE3),
345 ATTRIBUTE4 = DECODE(P_ATTRIBUTE4,
346 FND_API.G_MISS_CHAR, NULL,
347 NULL , ATTRIBUTE4, P_ATTRIBUTE4),
348 ATTRIBUTE5 = DECODE(P_ATTRIBUTE5,
349 FND_API.G_MISS_CHAR, NULL,
350 NULL , ATTRIBUTE5, P_ATTRIBUTE5),
351 ATTRIBUTE6 = DECODE(P_ATTRIBUTE6,
352 FND_API.G_MISS_CHAR, NULL,
353 NULL , ATTRIBUTE6, P_ATTRIBUTE6),
354 ATTRIBUTE7 = DECODE(P_ATTRIBUTE7,
355 FND_API.G_MISS_CHAR, NULL,
356 NULL , ATTRIBUTE7, P_ATTRIBUTE7),
357 ATTRIBUTE8 = DECODE(P_ATTRIBUTE8,
358 FND_API.G_MISS_CHAR, NULL,
359 NULL , ATTRIBUTE8, P_ATTRIBUTE8),
360 ATTRIBUTE9 = DECODE(P_ATTRIBUTE9,
361 FND_API.G_MISS_CHAR, NULL,
362 NULL , ATTRIBUTE9, P_ATTRIBUTE9),
363 ATTRIBUTE10 = DECODE(P_ATTRIBUTE10,
364 FND_API.G_MISS_CHAR, NULL,
365 NULL , ATTRIBUTE10, P_ATTRIBUTE10),
366 ATTRIBUTE11 = DECODE(P_ATTRIBUTE11,
367 FND_API.G_MISS_CHAR, NULL,
368 NULL , ATTRIBUTE11, P_ATTRIBUTE11),
369 ATTRIBUTE12 = DECODE(P_ATTRIBUTE12,
370 FND_API.G_MISS_CHAR, NULL,
371 NULL , ATTRIBUTE12, P_ATTRIBUTE12),
372 ATTRIBUTE13 = DECODE(P_ATTRIBUTE13,
373 FND_API.G_MISS_CHAR, NULL,
374 NULL , ATTRIBUTE13, P_ATTRIBUTE13),
375 ATTRIBUTE14 = DECODE(P_ATTRIBUTE14,
376 FND_API.G_MISS_CHAR, NULL,
377 NULL , ATTRIBUTE14, P_ATTRIBUTE14),
378 ATTRIBUTE15 = DECODE(P_ATTRIBUTE15,
379 FND_API.G_MISS_CHAR, NULL,
380 NULL , ATTRIBUTE15, P_ATTRIBUTE15),
381 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
385 WHERE SCHEDULE_HEADER_ID = P_SCHEDULE_HEADER_ID
382 LAST_UPDATE_DATE = TRUNC(SYSDATE),
383 LAST_UPDATED_BY = NVL(FND_GLOBAL.user_id,-1),
384 LAST_UPDATE_LOGIN = NVL(FND_GLOBAL.login_id,-1)
386 RETURNING OBJECT_VERSION_NUMBER INTO x_OBJECT_VERSION_NUMBER;
387
388 arp_standard.debug('Update_Head_Row -');
389 EXCEPTION
390 WHEN OTHERS THEN
391 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
392 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
393 FND_MESSAGE.SET_TOKEN('ERROR' ,'Update_Head_Row:'||SQLERRM);
394 FND_MSG_PUB.ADD;
395 END Update_Head_Row;
396
397
398
399 ----------------------------------
400 -- Table Handler insert line row
401 ----------------------------------
402 PROCEDURE Insert_Line_Row
403 (P_SCHEDULE_LINE_ID IN NUMBER,
404 P_SCHEDULE_HEADER_ID IN NUMBER,
405 P_SCHEDULE_ID IN NUMBER,
406 P_AGING_BUCKET_ID IN NUMBER,
407 P_AGING_BUCKET_LINE_ID IN NUMBER,
408 P_AMOUNT IN NUMBER,
409 P_RATE IN NUMBER,
410 P_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
411 P_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
412 P_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
413 P_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
414 P_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
415 P_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
416 P_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
417 P_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
418 P_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
419 P_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
420 P_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
421 P_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
422 P_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
423 P_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
424 P_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
425 P_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
426 P_OBJECT_VERSION_NUMBER IN NUMBER,
427 x_return_status IN OUT NOCOPY VARCHAR2)
428 IS
429 BEGIN
430 arp_standard.debug('Insert_Line_Row +');
431 INSERT INTO AR_charge_SCHEDULE_LINES
432 (SCHEDULE_LINE_ID ,
433 SCHEDULE_HEADER_ID ,
434 SCHEDULE_ID ,
435 AGING_BUCKET_ID ,
436 AGING_BUCKET_LINE_ID,
437 AMOUNT ,
438 RATE ,
439 ATTRIBUTE_CATEGORY ,
440 ATTRIBUTE1 ,
441 ATTRIBUTE2 ,
442 ATTRIBUTE3 ,
443 ATTRIBUTE4 ,
444 ATTRIBUTE5 ,
445 ATTRIBUTE6 ,
446 ATTRIBUTE7 ,
447 ATTRIBUTE8 ,
448 ATTRIBUTE9 ,
449 ATTRIBUTE10 ,
450 ATTRIBUTE11 ,
451 ATTRIBUTE12 ,
452 ATTRIBUTE13 ,
453 ATTRIBUTE14 ,
454 ATTRIBUTE15 ,
455 OBJECT_VERSION_NUMBER,
456 CREATED_BY ,
457 CREATION_DATE ,
458 LAST_UPDATED_BY ,
459 LAST_UPDATE_DATE ,
460 LAST_UPDATE_LOGIN)
461 VALUES
462 (P_SCHEDULE_LINE_ID ,
463 P_SCHEDULE_HEADER_ID ,
464 P_SCHEDULE_ID ,
465 P_AGING_BUCKET_ID ,
466 P_AGING_BUCKET_LINE_ID,
467 DECODE(P_AMOUNT, FND_API.G_MISS_NUM, NULL, P_AMOUNT),
468 DECODE(P_RATE , FND_API.G_MISS_NUM, NULL, P_RATE ),
469 DECODE(P_ATTRIBUTE_CATEGORY,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE_CATEGORY),
470 DECODE(P_ATTRIBUTE1 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE1),
471 DECODE(P_ATTRIBUTE2 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE2),
472 DECODE(P_ATTRIBUTE3 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE3),
473 DECODE(P_ATTRIBUTE4 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE4),
474 DECODE(P_ATTRIBUTE5 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE5),
475 DECODE(P_ATTRIBUTE6 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE6),
476 DECODE(P_ATTRIBUTE7 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE7),
477 DECODE(P_ATTRIBUTE8 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE8),
478 DECODE(P_ATTRIBUTE9 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE9),
479 DECODE(P_ATTRIBUTE10 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE10),
480 DECODE(P_ATTRIBUTE11 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE11),
481 DECODE(P_ATTRIBUTE12 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE12),
482 DECODE(P_ATTRIBUTE13 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE13),
483 DECODE(P_ATTRIBUTE14 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE14),
484 DECODE(P_ATTRIBUTE15 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE15),
485 P_OBJECT_VERSION_NUMBER,
486 NVL(FND_GLOBAL.user_id,-1),
487 TRUNC(SYSDATE),
488 NVL(FND_GLOBAL.user_id,-1),
489 TRUNC(SYSDATE),
490 NVL(FND_GLOBAL.login_id,-1));
491
492 arp_standard.debug('Insert_Line_Row -');
493 EXCEPTION
494 WHEN OTHERS THEN
495 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
496 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
497 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
498 FND_MSG_PUB.ADD;
499 END Insert_Line_Row;
500
501
502 PROCEDURE update_Line_Row
503 (P_SCHEDULE_LINE_ID IN NUMBER,
504 P_AMOUNT IN NUMBER,
505 P_RATE IN NUMBER,
506 P_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
507 P_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
508 P_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
509 P_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
510 P_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
511 P_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
512 P_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
513 P_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
514 P_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
515 P_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
516 P_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
520 P_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
517 P_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
518 P_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
519 P_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
521 P_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
522 X_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
523 x_return_status IN OUT NOCOPY VARCHAR2)
524 IS
525 BEGIN
526 arp_standard.debug('Update_Line_Row +');
527 X_OBJECT_VERSION_NUMBER := X_OBJECT_VERSION_NUMBER + 1;
528 UPDATE AR_charge_SCHEDULE_LINES SET
529 AMOUNT = DECODE(p_amount,
530 fnd_api.g_miss_num, NULL,
531 NULL ,AMOUNT,p_amount),
532 RATE = DECODE(p_rate,
533 fnd_api.g_miss_num, NULL,
534 NULL ,RATE,p_rate),
535 ATTRIBUTE_CATEGORY = DECODE(P_ATTRIBUTE_CATEGORY,
536 FND_API.G_MISS_CHAR, NULL,
537 NULL , ATTRIBUTE_CATEGORY, P_ATTRIBUTE_CATEGORY),
538 ATTRIBUTE1 = DECODE(P_ATTRIBUTE1,
539 FND_API.G_MISS_CHAR, NULL,
540 NULL , ATTRIBUTE1, P_ATTRIBUTE1),
541 ATTRIBUTE2 = DECODE(P_ATTRIBUTE2,
542 FND_API.G_MISS_CHAR, NULL,
543 NULL , ATTRIBUTE2, P_ATTRIBUTE2),
544 ATTRIBUTE3 = DECODE(P_ATTRIBUTE3,
545 FND_API.G_MISS_CHAR, NULL,
546 NULL , ATTRIBUTE3, P_ATTRIBUTE3),
547 ATTRIBUTE4 = DECODE(P_ATTRIBUTE4,
548 FND_API.G_MISS_CHAR, NULL,
549 NULL , ATTRIBUTE4, P_ATTRIBUTE4),
550 ATTRIBUTE5 = DECODE(P_ATTRIBUTE5,
551 FND_API.G_MISS_CHAR, NULL,
552 NULL , ATTRIBUTE5, P_ATTRIBUTE5),
553 ATTRIBUTE6 = DECODE(P_ATTRIBUTE6,
554 FND_API.G_MISS_CHAR, NULL,
555 NULL , ATTRIBUTE6, P_ATTRIBUTE6),
556 ATTRIBUTE7 = DECODE(P_ATTRIBUTE7,
557 FND_API.G_MISS_CHAR, NULL,
558 NULL , ATTRIBUTE7, P_ATTRIBUTE7),
559 ATTRIBUTE8 = DECODE(P_ATTRIBUTE8,
560 FND_API.G_MISS_CHAR, NULL,
561 NULL , ATTRIBUTE8, P_ATTRIBUTE8),
562 ATTRIBUTE9 = DECODE(P_ATTRIBUTE9,
563 FND_API.G_MISS_CHAR, NULL,
564 NULL , ATTRIBUTE9, P_ATTRIBUTE9),
565 ATTRIBUTE10 = DECODE(P_ATTRIBUTE10,
566 FND_API.G_MISS_CHAR, NULL,
567 NULL , ATTRIBUTE10, P_ATTRIBUTE10),
568 ATTRIBUTE11 = DECODE(P_ATTRIBUTE11,
569 FND_API.G_MISS_CHAR, NULL,
570 NULL , ATTRIBUTE11, P_ATTRIBUTE11),
571 ATTRIBUTE12 = DECODE(P_ATTRIBUTE12,
572 FND_API.G_MISS_CHAR, NULL,
573 NULL , ATTRIBUTE12, P_ATTRIBUTE12),
574 ATTRIBUTE13 = DECODE(P_ATTRIBUTE13,
575 FND_API.G_MISS_CHAR, NULL,
576 NULL , ATTRIBUTE13, P_ATTRIBUTE13),
577 ATTRIBUTE14 = DECODE(P_ATTRIBUTE14,
578 FND_API.G_MISS_CHAR, NULL,
579 NULL , ATTRIBUTE14, P_ATTRIBUTE14),
580 ATTRIBUTE15 = DECODE(P_ATTRIBUTE15,
581 FND_API.G_MISS_CHAR, NULL,
582 NULL , ATTRIBUTE15, P_ATTRIBUTE15),
583 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
584 LAST_UPDATE_DATE = TRUNC(SYSDATE),
585 LAST_UPDATED_BY = NVL(FND_GLOBAL.user_id,-1),
586 LAST_UPDATE_LOGIN = NVL(FND_GLOBAL.login_id,-1)
587 WHERE SCHEDULE_LINE_ID = P_SCHEDULE_LINE_ID;
588
589
590 arp_standard.debug('Update_Line_Row -');
591 EXCEPTION
592 WHEN OTHERS THEN
593 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
594 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
595 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
596 FND_MSG_PUB.ADD;
597 END Update_Line_Row;
598
599
600
601
602 FUNCTION validate_lookup
603 (p_type IN VARCHAR2,
604 p_code IN VARCHAR2)
605 RETURN BOOLEAN
606 IS
607 CURSOR c_lookup(p_type IN VARCHAR, p_code IN VARCHAR2)
608 IS
609 SELECT NULL
610 FROM ar_lookups
611 WHERE lookup_type = p_type
612 AND lookup_code = p_code
613 AND ( ENABLED_FLAG = 'Y' AND
614 TRUNC( SYSDATE ) BETWEEN
615 TRUNC(NVL( START_DATE_ACTIVE,SYSDATE ) ) AND
616 TRUNC(NVL( END_DATE_ACTIVE,SYSDATE ) ) );
617 l_c VARCHAR2(30);
618 l_result BOOLEAN;
619 BEGIN
620 OPEN c_lookup(p_type,p_code);
621 FETCH c_lookup INTO l_c;
622 IF c_lookup%NOTFOUND THEN
623 l_result := FALSE;
624 ELSE
625 l_result := TRUE;
626 END IF;
627 CLOSE c_lookup;
628 RETURN l_result;
629 END;
630
631
632
633 FUNCTION compare
634 (date1 DATE,
635 date2 DATE)
636 RETURN NUMBER
637 IS
638 ldate1 date;
639 ldate2 date;
640 BEGIN
641 ldate1 := date1;
645 ELSIF (ldate2 IS NULL OR ldate2 = FND_API.G_MISS_DATE) THEN
642 ldate2 := date2;
643 IF ((ldate1 IS NULL OR ldate1 = FND_API.G_MISS_DATE) AND (ldate2 IS NULL OR ldate2 = FND_API.G_MISS_DATE)) THEN
644 RETURN 0;
646 RETURN -1;
647 ELSIF (ldate1 IS NULL OR ldate1 = FND_API.G_MISS_DATE) THEN
648 RETURN 1;
649 ELSIF ( ldate1 = ldate2 ) THEN
650 RETURN 0;
651 ELSIF ( ldate1 > ldate2 ) THEN
652 RETURN 1;
653 ELSE
654 RETURN -1;
655 END IF;
656 END compare;
657
658
659
660 PROCEDURE validate_schedule
661 (P_SCHEDULE_NAME IN VARCHAR2,
662 P_SCHEDULE_DESCRIPTION IN VARCHAR2,
663 P_MODE IN VARCHAR2,
664 x_return_status IN OUT NOCOPY VARCHAR2)
665 IS
666 BEGIN
667 -------------------------
668 -- Validate schedule_name
669 -------------------------
670 arp_standard.debug(' Validate schedule_name +');
671 IF p_mode = 'INSERT' THEN
672 IF p_schedule_name IS NULL OR p_schedule_name = FND_API.G_MISS_CHAR THEN
673 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
674 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'schedule_name' );
675 FND_MSG_PUB.ADD;
676 x_return_status := FND_API.G_RET_STS_ERROR;
677 END IF;
678 END IF;
679 arp_standard.debug(' Validate schedule_name -');
680 END;
681
682
683
684 PROCEDURE validate_schedule_header
685 (P_SCHEDULE_HEADER_ID IN NUMBER,
686 P_SCHEDULE_ID IN NUMBER,
687 P_SCHEDULE_HEADER_TYPE IN VARCHAR2,
688 P_AGING_BUCKET_ID IN NUMBER,
689 P_START_DATE IN DATE,
690 P_END_DATE IN DATE,
691 P_STATUS IN VARCHAR2,
692 P_OLD_STATUS IN VARCHAR2,
693 p_mode IN VARCHAR2,
694 x_return_status IN OUT NOCOPY VARCHAR2)
695 IS
696 CURSOR c_schedule_id IS
697 SELECT NULL
698 FROM ar_charge_schedules
699 WHERE schedule_id = p_schedule_id;
700
701 CURSOR c_aging_bucket IS
702 SELECT NULL
703 FROM ar_aging_buckets
704 WHERE aging_bucket_id = P_AGING_BUCKET_ID;
705
706 CURSOR c_overlapp IS
707 SELECT start_date,
708 end_date
709 FROM ar_charge_schedule_hdrs
710 WHERE schedule_id = p_schedule_id
711 AND status = 'A';
712
713 CURSOR c_overlapp_upd IS
714 SELECT start_date,
715 end_date
716 FROM ar_charge_schedule_hdrs
717 WHERE schedule_id = p_schedule_id
718 AND schedule_header_id <> P_SCHEDULE_HEADER_ID
719 AND status = 'A';
720
721 l_start_date DATE;
722 l_end_date DATE;
723 l_val NUMBER;
724 l_cpt NUMBER := 0;
725 l_c VARCHAR2(1);
726 BEGIN
727
728 arp_standard.debug('validate_schedule_header +');
729
730
731 IF p_mode IN ('INSERT') THEN
732 -------------------------------------
733 -- Validate schedule_id
734 -------------------------------------
735 arp_standard.debug(' Validate schedule_id +');
736 IF P_SCHEDULE_ID IS NULL OR P_SCHEDULE_ID = fnd_api.g_miss_num THEN
737 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
738 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'SCHEDULE_ID' );
739 FND_MSG_PUB.ADD;
740 x_return_status := FND_API.G_RET_STS_ERROR;
741 END IF;
742 OPEN c_schedule_id;
743 FETCH c_schedule_id INTO l_c;
744 IF c_schedule_id%NOTFOUND THEN
745 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_FK');
746 FND_MESSAGE.SET_TOKEN('FK', 'ar charge schedules');
747 FND_MESSAGE.SET_TOKEN('COLUMN', 'schedule_id');
748 FND_MESSAGE.SET_TOKEN('TABLE', 'ar_charge_schedules');
749 FND_MSG_PUB.ADD;
750 x_return_status := FND_API.G_RET_STS_ERROR;
751 END IF;
752 CLOSE c_schedule_id;
753 arp_standard.debug(' Validate schedule_id -');
754
755 --------------------------------------------------------------------------
756 -- Validate SCHEDULE_HEADER_TYPE lookup code in lookup type SCHEDULE_HEADER_TYPE
757 --------------------------------------------------------------------------
758 arp_standard.debug(' Validate schedule_header_type +');
759 IF p_schedule_header_type IS NULL OR p_schedule_header_type = fnd_api.g_miss_char THEN
760 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
761 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'schedule_header_type' );
762 FND_MSG_PUB.ADD;
763 x_return_status := FND_API.G_RET_STS_ERROR;
764 END IF;
765
766 IF validate_lookup('SCHEDULE_HEADER_TYPE',p_schedule_header_type) = FALSE THEN
767 arp_standard.debug(' schedule type should be lookup code for the lookup type SCHEDULE_HEADER_TYPE');
768 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_LOOKUP' );
769 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'schedule header type' );
770 FND_MESSAGE.SET_TOKEN( 'LOOKUP_TYPE', 'SCHEDULE_HEADER_TYPE' );
771 FND_MSG_PUB.ADD;
772 x_return_status := FND_API.G_RET_STS_ERROR;
773 END IF;
774 arp_standard.debug(' Validate schedule_header_type -');
775
776
777 -------------------------------------------------------------
778 -- Validate STATUS lookup code in lookup type REGISTRY_STATUS
779 -------------------------------------------------------------
780 arp_standard.debug(' Validate status +');
781 IF p_status IS NULL OR p_status = fnd_api.g_miss_char THEN
782 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
783 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'status' );
784 FND_MSG_PUB.ADD;
785 x_return_status := FND_API.G_RET_STS_ERROR;
786 END IF;
787
788 IF validate_lookup('REGISTRY_STATUS',p_status) = FALSE THEN
789 arp_standard.debug(' status should be lookup code for the lookup type REGISTRY_STATUS');
790 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_LOOKUP' );
791 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'status' );
792 FND_MESSAGE.SET_TOKEN( 'LOOKUP_TYPE', 'REGISTRY_STATUS' );
793 FND_MSG_PUB.ADD;
794 x_return_status := FND_API.G_RET_STS_ERROR;
795 END IF;
796 arp_standard.debug(' Validate status -');
797
798
799 -----------------------------
800 -- Validate P_AGING_BUCKET_ID
801 -----------------------------
802 arp_standard.debug(' validate aging_bucket +');
803 IF P_AGING_BUCKET_ID IS NULL OR P_AGING_BUCKET_ID = FND_API.G_MISS_NUM THEN
804 NULL;
805 ELSE
806 OPEN c_aging_bucket;
807 FETCH c_aging_bucket INTO l_c;
808 IF c_aging_bucket%NOTFOUND THEN
809 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_FK');
810 FND_MESSAGE.SET_TOKEN('FK', 'ar aging bucket id');
811 FND_MESSAGE.SET_TOKEN('COLUMN', 'aging_bucket_id');
812 FND_MESSAGE.SET_TOKEN('TABLE', 'ar_aging_bucket');
813 FND_MSG_PUB.ADD;
814 x_return_status := FND_API.G_RET_STS_ERROR;
815 END IF;
816 CLOSE c_aging_bucket;
817 END IF;
818 arp_standard.debug(' validate aging_bucket -');
819
820
821 ----------------------------------------------------
822 -- Validate start_date and end_date
823 ----------------------------------------------------
824 arp_standard.debug(' validate start and end dates +');
825
826 IF p_start_date IS NULL OR p_start_date = fnd_api.g_miss_date THEN
827 arp_standard.debug(' validate start date is mandatory');
828 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
829 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'p_start_date' );
830 FND_MSG_PUB.ADD;
831 x_return_status := FND_API.G_RET_STS_ERROR;
832 END IF;
833
834 l_val := compare(date1 => p_start_date,
835 date2 => p_end_date );
836
837 IF l_val = 1 THEN
838 arp_standard.debug(' end date should be greater than the start date');
839 fnd_message.set_name('AR', 'HZ_API_DATE_GREATER');
840 fnd_message.set_token('DATE2', 'end_date');
841 fnd_message.set_token('DATE1', 'start_date');
842 fnd_msg_pub.add;
843 x_return_status := fnd_api.g_ret_sts_error;
844 END IF;
845
846
847 IF x_return_status = fnd_api.g_ret_sts_success THEN
848 arp_standard.debug(' Check overlappings of periods');
849 OPEN c_overlapp;
850 LOOP
851 FETCH c_overlapp INTO l_start_date, l_end_date;
852 EXIT WHEN c_overlapp%NOTFOUND;
853 l_cpt := l_cpt + 1;
854 l_val := compare(p_start_date,l_end_date);
855 IF l_val = 1 THEN
856 arp_standard.debug('Entered start date :'|| p_start_date ||' greater than existing end date :'||l_end_date);
857 ELSE
858 l_val := compare(l_start_date,p_end_date);
859 IF l_val = 1 THEN
860 arp_standard.debug('Existing start date :'|| l_start_date ||' greater than existing entered end date :'||p_end_date);
861 ELSE
862 arp_standard.debug(' overlapping issue :');
863 arp_standard.debug(' existing start :'||l_start_date|| ' end :'||l_end_date);
864 arp_standard.debug(' entered start :'||p_start_date|| ' end :'||p_end_date);
865 fnd_message.set_name('AR', 'AR_DATE_OVERLAPP');
866 --Int'l Calendar Project
867 fnd_message.set_token('START_DATE_PER_1', fnd_date.date_to_chardate(l_start_date, calendar_aware=> FND_DATE.calendar_aware_alt));
868 fnd_message.set_token('END_DATE_PER_1' , fnd_date.date_to_chardate(l_end_date, calendar_aware=> FND_DATE.calendar_aware_alt));
869 fnd_message.set_token('START_DATE_PER_2', fnd_date.date_to_chardate(p_start_date, calendar_aware=> FND_DATE.calendar_aware_alt));
870 fnd_message.set_token('END_DATE_PER_2' , fnd_date.date_to_chardate(p_end_date, calendar_aware=> FND_DATE.calendar_aware_alt));
871 fnd_msg_pub.add;
872 x_return_status := fnd_api.g_ret_sts_error;
873 END IF;
874 END IF;
875 IF x_return_status <> fnd_api.g_ret_sts_success THEN
876 EXIT;
877 END IF;
878 END LOOP;
879 CLOSE c_overlapp;
880 END IF;
881 arp_standard.debug(' validate start and end dates -');
882
883 END IF;
884
885
886 IF p_mode IN ('UPDATE') THEN
887 -------------------------------------------------------------
888 -- Validate STATUS lookup code in lookup type REGISTRY_STATUS
889 -------------------------------------------------------------
890 arp_standard.debug(' Validate status +');
894 FND_MSG_PUB.ADD;
891 IF p_status = fnd_api.g_miss_char THEN
892 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
893 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'status' );
895 x_return_status := FND_API.G_RET_STS_ERROR;
896 END IF;
897
898 IF p_status IS NOT NULL THEN
899 IF validate_lookup('REGISTRY_STATUS',p_status) = FALSE THEN
900 arp_standard.debug(' status should be lookup code for the lookup type REGISTRY_STATUS');
901 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_LOOKUP' );
902 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'status' );
903 FND_MESSAGE.SET_TOKEN( 'LOOKUP_TYPE', 'REGISTRY_STATUS' );
904 FND_MSG_PUB.ADD;
905 x_return_status := FND_API.G_RET_STS_ERROR;
906 END IF;
907 END IF;
908
909 IF p_old_status = 'I' AND p_status = 'A' THEN
910 arp_standard.debug(' Schedule Header can not be reactivated');
911 FND_MESSAGE.SET_NAME( 'AR', 'AR_NO_REACTIVATE_ALLOW' );
912 FND_MSG_PUB.ADD;
913 x_return_status := FND_API.G_RET_STS_ERROR;
914 END IF;
915
916 arp_standard.debug(' Validate status -');
917
918 -----------------------------------
919 -- Validate start_date and end_date
920 -----------------------------------
921 arp_standard.debug(' validate start and end dates +');
922 IF p_end_date IS NOT NULL AND p_end_date <> FND_API.G_MISS_DATE THEN
923 l_val := compare(date1 => p_start_date,
924 date2 => p_end_date );
925 IF l_val = 1 THEN
926 arp_standard.debug(' end date should be greater than the start date');
927 fnd_message.set_name('AR', 'HZ_API_DATE_GREATER');
928 fnd_message.set_token('DATE2', 'end_date');
929 fnd_message.set_token('DATE1', 'start_date');
930 fnd_msg_pub.add;
931 x_return_status := fnd_api.g_ret_sts_error;
932 END IF;
933 IF x_return_status = fnd_api.g_ret_sts_success THEN
934 arp_standard.debug(' Check overlappings of periods');
935 OPEN c_overlapp_upd;
936 LOOP
937 FETCH c_overlapp_upd INTO l_start_date, l_end_date;
938 EXIT WHEN c_overlapp_upd%NOTFOUND;
939 l_cpt := l_cpt + 1;
940 l_val := compare(p_start_date,l_end_date);
941 IF l_val = 1 THEN
942 NULL; -- OK the existing period start after the entered period
943 ELSE
944 l_val := compare(l_start_date,p_end_date);
945 IF l_val = 1 THEN
946 NULL; -- Ok the existing period ends before the entered period start
947 ELSE
948 arp_standard.debug(' overlapping issue :');
949 arp_standard.debug(' existing start :'||l_start_date|| ' end :'||l_end_date);
950 arp_standard.debug(' entered start :'||p_start_date|| ' end :'||p_end_date);
951 fnd_message.set_name('AR', 'AR_DATE_OVERLAPP');
952 --Int'l Calendar Project
953 fnd_message.set_token('START_DATE_PER_1', fnd_date.date_to_chardate(l_start_date, calendar_aware=> FND_DATE.calendar_aware_alt));
954 fnd_message.set_token('END_DATE_PER_1' , fnd_date.date_to_chardate(l_end_date, calendar_aware=> FND_DATE.calendar_aware_alt));
955 fnd_message.set_token('START_DATE_PER_2', fnd_date.date_to_chardate(p_start_date, calendar_aware=> FND_DATE.calendar_aware_alt));
956 fnd_message.set_token('END_DATE_PER_2' , fnd_date.date_to_chardate(p_end_date, calendar_aware=> FND_DATE.calendar_aware_alt));
957 fnd_msg_pub.add;
958 x_return_status := fnd_api.g_ret_sts_error;
959 END IF;
960 END IF;
961 IF x_return_status <> fnd_api.g_ret_sts_success THEN
962 EXIT;
963 END IF;
964 END LOOP;
965 CLOSE c_overlapp_upd;
966 END IF;
967 arp_standard.debug(' validate start and end dates -');
968 END IF;
969 END IF;
970 arp_standard.debug('validate_schedule_header -');
971 END;
972
973
974 PROCEDURE validate_schedule_line
975 (P_SCHEDULE_LINE_ID IN NUMBER,
976 P_SCHEDULE_HEADER_ID IN NUMBER,
977 P_SCHEDULE_ID IN NUMBER,
978 P_AGING_BUCKET_ID IN NUMBER,
979 P_AGING_BUCKET_LINE_ID IN NUMBER,
980 P_AMOUNT IN NUMBER,
981 P_RATE IN NUMBER,
982 P_MODE IN VARCHAR2,
983 x_return_status IN OUT NOCOPY VARCHAR2)
984 IS
985 CURSOR c_header IS
986 SELECT aging_bucket_id,
987 schedule_id,
988 schedule_header_id
989 FROM ar_charge_schedule_hdrs
990 WHERE schedule_header_id = p_schedule_header_id;
991
992 l_rec c_header%ROWTYPE;
993
994 CURSOR c_aging_bucket_id(p_aging_bucket_id IN NUMBER,
995 p_aging_bucket_line_id IN NUMBER) IS
996 SELECT NULL
997 FROM ar_aging_buckets a,
998 ar_aging_bucket_lines_b b
999 WHERE a.aging_bucket_id = p_aging_bucket_id
1000 AND a.status = 'A'
1001 AND a.aging_bucket_id = b.aging_bucket_id
1002 AND b.aging_bucket_line_id = p_aging_bucket_line_id;
1003
1004 CURSOR ar_schedule_line_u2(
1005 p_SCHEDULE_HEADER_ID IN NUMBER,
1006 p_aging_bucket_id IN NUMBER,
1007 p_aging_bucket_line_id IN NUMBER)
1008 IS
1009 SELECT NULL
1010 FROM ar_charge_schedule_lines
1011 WHERE schedule_header_id = p_SCHEDULE_HEADER_ID
1012 AND aging_bucket_id = p_aging_bucket_id
1013 AND aging_bucket_line_id = p_aging_bucket_line_id;
1014
1015 CURSOR cl IS
1016 SELECT lookup_code
1017 FROM ar_charge_schedule_hdrs a,
1018 ar_lookups b
1019 WHERE a.schedule_header_id = P_SCHEDULE_HEADER_ID
1020 AND b.lookup_type = 'SCHEDULE_HEADER_TYPE'
1021 AND b.lookup_code = a.schedule_header_type;
1022
1023 l_c VARCHAR2(30);
1024 BEGIN
1025 -------------------------------------
1026 -- Validate schedule_header_id
1027 -------------------------------------
1028 arp_standard.debug(' Validate schedule_header_id +');
1029
1030 IF p_schedule_header_id IS NULL OR p_schedule_header_id = fnd_api.g_miss_num THEN
1031 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
1032 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'schedule_header_id' );
1033 FND_MSG_PUB.ADD;
1034 x_return_status := FND_API.G_RET_STS_ERROR;
1035 END IF;
1036
1037 OPEN c_header;
1038 FETCH c_header INTO l_rec;
1039 IF c_header%NOTFOUND THEN
1040 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_FK');
1041 FND_MESSAGE.SET_TOKEN('FK', 'schedule header');
1042 FND_MESSAGE.SET_TOKEN('COLUMN', 'schedule_header_id');
1043 FND_MESSAGE.SET_TOKEN('TABLE', 'ar_charge_schedule_hdrs');
1044 FND_MSG_PUB.ADD;
1045 x_return_status := FND_API.G_RET_STS_ERROR;
1046 END IF;
1047 CLOSE c_header;
1048 arp_standard.debug(' Validate schedule_header_id -');
1049
1050
1051 ------------------------
1052 -- Validate schedule_id
1053 ------------------------
1054 arp_standard.debug(' Validate schedule_id +');
1055 IF p_schedule_id IS NULL OR p_schedule_id = fnd_api.g_miss_num THEN
1056 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
1057 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'schedule_id' );
1058 FND_MSG_PUB.ADD;
1059 x_return_status := FND_API.G_RET_STS_ERROR;
1060 ELSE
1061 IF p_schedule_id <> l_rec.schedule_id THEN
1062 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_FK');
1063 FND_MESSAGE.SET_TOKEN('FK', 'schedule header');
1064 FND_MESSAGE.SET_TOKEN('COLUMN', 'schedule_id');
1065 FND_MESSAGE.SET_TOKEN('TABLE', 'ar_charge_schedule_hdrs');
1066 FND_MSG_PUB.ADD;
1067 x_return_status := FND_API.G_RET_STS_ERROR;
1068 END IF;
1069 END IF;
1070 arp_standard.debug(' Validate schedule_id -');
1071
1072
1073 ---------------------------
1074 -- Validate aging_bucket_id
1075 ---------------------------
1076 arp_standard.debug(' Validate aging_bucket_id +');
1077 IF P_AGING_BUCKET_ID IS NULL OR P_AGING_BUCKET_ID = fnd_api.g_miss_num THEN
1078 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
1079 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'P_AGING_BUCKET_ID' );
1080 FND_MSG_PUB.ADD;
1081 x_return_status := FND_API.G_RET_STS_ERROR;
1082 ELSE
1083 IF P_AGING_BUCKET_ID <> l_rec.aging_bucket_id THEN
1084 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_FK');
1085 FND_MESSAGE.SET_TOKEN('FK', 'schedule header');
1086 FND_MESSAGE.SET_TOKEN('COLUMN', 'aging_bucket_id');
1087 FND_MESSAGE.SET_TOKEN('TABLE', 'ar_charge_schedule_hdrs');
1088 FND_MSG_PUB.ADD;
1089 x_return_status := FND_API.G_RET_STS_ERROR;
1090 END IF;
1091 END IF;
1092 arp_standard.debug(' Validate aging_bucket_id -');
1093
1094
1095 ----------------------------------------------------
1096 -- Unidicity of the combination schedule_header_id, aging_bucket, aging_bucket_line
1097 ----------------------------------------------------
1098 arp_standard.debug(' unidicity of ar_schedule_line_u2 +');
1099 OPEN ar_schedule_line_u2(
1100 p_SCHEDULE_HEADER_ID ,
1101 p_aging_bucket_id ,
1102 p_aging_bucket_line_id);
1103 FETCH ar_schedule_line_u2 INTO l_c;
1104 IF ar_schedule_line_u2%FOUND THEN
1105 arp_standard.debug(' A Record in ar_charge_schedule_lines exists with '||
1106 ' schedule_header_id - aging_bucket_id - aging_bucket_line_id');
1107 fnd_message.set_name('AR', 'AR_API_REC_COMB_EXISTS');
1108 fnd_message.set_token('COLUMN1', 'schedule_header_id');
1109 fnd_message.set_token('COLUMN2', 'aging_bucket_id');
1110 fnd_message.set_token('COLUMN3', 'aging_bucket_line_id');
1111 fnd_msg_pub.add;
1112 x_return_status := fnd_api.g_ret_sts_error;
1113 END IF;
1114 CLOSE ar_schedule_line_u2;
1115 arp_standard.debug(' unidicity of ar_schedule_line_u2 -');
1116
1117
1118 ---------------------------------
1119 -- Validate aging_bucket_line_id
1120 ---------------------------------
1121 arp_standard.debug(' Validate aging_bucket_id and aging_bucket_line_id +');
1122 IF p_aging_bucket_line_id IS NULL OR p_aging_bucket_line_id = fnd_api.g_miss_num
1123 THEN
1124 IF p_aging_bucket_line_id IS NULL or p_aging_bucket_line_id = fnd_api.g_miss_num THEN
1125 arp_standard.debug(' AGING_BUCKET_LINE_ID missing');
1126 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
1127 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'AGING_BUCKET_LINE_ID' );
1128 FND_MSG_PUB.ADD;
1129 x_return_status := FND_API.G_RET_STS_ERROR;
1130 END IF;
1131 ELSE
1132 IF p_aging_bucket_id IS NOT NULL AND p_aging_bucket_id <> FND_API.G_MISS_NUM THEN
1133 OPEN c_aging_bucket_id(p_aging_bucket_id, p_aging_bucket_line_id);
1134 FETCH c_aging_bucket_id INTO l_c;
1135 IF c_aging_bucket_id%NOTFOUND THEN
1136 arp_standard.debug(' AGING_BUCKET_ID/AGING_BUCKET_LINE_ID are not valid');
1137 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_FK' );
1138 FND_MESSAGE.SET_TOKEN( 'FK', 'aging_bucket_id' );
1139 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'aging_bucket_id' );
1140 FND_MESSAGE.SET_TOKEN( 'TABLE', 'ar_aging_buckets');
1141 FND_MSG_PUB.ADD;
1142 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_FK' );
1143 FND_MESSAGE.SET_TOKEN( 'FK', 'aging_bucket_line_id' );
1144 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'aging_bucket_line_id' );
1145 FND_MESSAGE.SET_TOKEN( 'TABLE', 'ar_aging_bucket_lines_b');
1146 FND_MSG_PUB.ADD;
1147 x_return_status := FND_API.G_RET_STS_ERROR;
1148 END IF;
1149 CLOSE c_aging_bucket_id;
1150 END IF;
1151 END IF;
1152 arp_standard.debug(' Validate aging_bucket_line_id -');
1153
1154
1155
1156 --------------------------
1157 -- Validate amount or rate
1158 --------------------------
1159 arp_standard.debug(' validate amount or rate +');
1160
1161 OPEN cl;
1162 FETCH cl INTO l_c;
1163 CLOSE cl;
1164
1165 IF (p_amount IS NULL OR p_amount = FND_API.G_MISS_NUM) AND
1166 (l_c = 'AMOUNT')
1167 THEN
1168 arp_standard.debug(' The amount column is mandatory for SCHEDULE_HEADER_TYPE :'||l_c);
1169 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
1170 fnd_message.set_token('COLUMN', 'AMOUNT');
1171 FND_MSG_PUB.ADD;
1172 x_return_status := FND_API.G_RET_STS_ERROR;
1173 END IF;
1174
1175 IF (p_rate IS NULL OR p_rate = FND_API.G_MISS_NUM) AND
1176 (l_c = 'PERCENTAGE')
1177 THEN
1178 arp_standard.debug(' The rate column is mandatory for SCHEDULE_HEADER_TYPE:'||l_c);
1179 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
1180 fnd_message.set_token('COLUMN', 'RATE');
1181 fnd_msg_pub.add;
1182 FND_MSG_PUB.ADD;
1183 x_return_status := FND_API.G_RET_STS_ERROR;
1184 END IF;
1185 arp_standard.debug(' validate amount or rate -');
1186 END;
1187
1188
1189
1190
1191
1192 PROCEDURE create_schedule
1193 (p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1194 P_SCHEDULE_NAME IN VARCHAR2,
1195 P_SCHEDULE_DESCRIPTION IN VARCHAR2,
1196 P_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
1197 P_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
1198 P_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
1199 P_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
1200 P_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
1201 P_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
1202 P_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
1203 P_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
1204 P_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
1205 P_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
1206 P_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
1207 P_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
1208 P_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
1209 P_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
1213 x_schedule_id OUT NOCOPY NUMBER,
1210 P_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
1211 P_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
1212 P_OBJECT_VERSION_NUMBER IN NUMBER DEFAULT 1,
1214 x_return_status OUT NOCOPY VARCHAR2,
1215 x_msg_count OUT NOCOPY NUMBER,
1216 x_msg_data OUT NOCOPY VARCHAR2)
1217 IS
1218 CURSOR cu_schedule_id IS
1219 SELECT ar_charge_schedules_s.nextval
1220 FROM DUAL;
1221 l_schedule_id NUMBER;
1222 l_n VARCHAR2(10);
1223 BEGIN
1224 l_n := 0;
1225 arp_standard.debug('create_schedule +');
1226 arp_standard.debug(' P_SCHEDULE_NAME :'||P_SCHEDULE_NAME);
1227 arp_standard.debug(' P_SCHEDULE_DESCRIPTION :'||P_SCHEDULE_DESCRIPTION);
1228
1229 SAVEPOINT create_schedule;
1230
1231 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1232
1233 IF fnd_api.to_boolean(p_init_msg_list) THEN
1234 fnd_msg_pub.initialize;
1235 END IF;
1236
1237 l_n := 1;
1238
1239 validate_schedule
1240 (P_SCHEDULE_NAME => p_schedule_name,
1241 p_schedule_description => P_SCHEDULE_DESCRIPTION,
1242 p_mode => 'INSERT',
1243 x_return_status => x_return_status);
1244
1245
1246 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1247 RAISE fnd_api.G_EXC_ERROR;
1248 END IF;
1249
1250 l_n := 2;
1251 OPEN cu_schedule_id;
1252 FETCH cu_schedule_id INTO l_schedule_id;
1253 CLOSE cu_schedule_id;
1254
1255 l_n := 3;
1256 Insert_schedule_Row
1257 (P_SCHEDULE_ID => l_schedule_id,
1258 P_SCHEDULE_NAME => P_SCHEDULE_NAME,
1259 P_SCHEDULE_DESCRIPTION=> P_SCHEDULE_DESCRIPTION,
1260 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
1261 P_ATTRIBUTE1 => P_ATTRIBUTE1,
1262 P_ATTRIBUTE2 => P_ATTRIBUTE2,
1263 P_ATTRIBUTE3 => P_ATTRIBUTE3,
1264 P_ATTRIBUTE4 => P_ATTRIBUTE4,
1265 P_ATTRIBUTE5 => P_ATTRIBUTE5,
1266 P_ATTRIBUTE6 => P_ATTRIBUTE6,
1267 P_ATTRIBUTE7 => P_ATTRIBUTE7,
1268 P_ATTRIBUTE8 => P_ATTRIBUTE8,
1269 P_ATTRIBUTE9 => P_ATTRIBUTE9,
1270 P_ATTRIBUTE10 => P_ATTRIBUTE10,
1271 P_ATTRIBUTE11 => P_ATTRIBUTE11,
1272 P_ATTRIBUTE12 => P_ATTRIBUTE12,
1273 P_ATTRIBUTE13 => P_ATTRIBUTE13,
1274 P_ATTRIBUTE14 => P_ATTRIBUTE14,
1275 P_ATTRIBUTE15 => P_ATTRIBUTE15,
1276 P_OBJECT_VERSION_NUMBER => P_OBJECT_VERSION_NUMBER,
1277 x_return_status => x_return_status);
1278
1279 l_n := 4;
1280 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1281 RAISE fnd_api.G_EXC_ERROR;
1282 END IF;
1283
1284 x_schedule_id := l_schedule_id;
1285
1286 arp_standard.debug('create_schedule -');
1287
1288 EXCEPTION
1289 WHEN FND_API.G_EXC_ERROR THEN
1290 ROLLBACK TO create_schedule;
1291 FND_MSG_PUB.Count_And_Get(
1292 p_encoded => FND_API.G_FALSE,
1293 p_count => x_msg_count,
1294 p_data => x_msg_data);
1295 arp_standard.debug('EXCEPTION create_schedule:'||x_msg_data);
1296
1297 WHEN OTHERS THEN
1298 ROLLBACK TO create_schedule;
1299 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1300 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1301 FND_MESSAGE.SET_TOKEN('ERROR' ,l_n||' '||SQLERRM);
1302 FND_MSG_PUB.ADD;
1303 FND_MSG_PUB.Count_And_Get(
1304 p_encoded => FND_API.G_FALSE,
1305 p_count => x_msg_count,
1306 p_data => x_msg_data);
1307 arp_standard.debug('EXCEPTION create_schedule:'||x_msg_data);
1308 END;
1309
1310
1311 PROCEDURE update_schedule
1312 (p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1313 P_SCHEDULE_ID IN NUMBER,
1314 P_SCHEDULE_DESCRIPTION IN VARCHAR2,
1315 P_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
1316 P_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
1317 P_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
1318 P_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
1319 P_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
1320 P_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
1321 P_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
1322 P_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
1323 P_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
1324 P_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
1325 P_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
1326 P_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
1327 P_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
1328 P_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
1329 P_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
1330 P_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
1331 X_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
1332 x_return_status OUT NOCOPY VARCHAR2,
1333 x_msg_count OUT NOCOPY NUMBER,
1334 x_msg_data OUT NOCOPY VARCHAR2)
1335 IS
1336 CURSOR c IS
1337 SELECT SCHEDULE_ID ,
1338 SCHEDULE_NAME ,
1339 SCHEDULE_DESCRIPTION,
1340 ATTRIBUTE_CATEGORY ,
1341 ATTRIBUTE1 ,
1342 ATTRIBUTE2 ,
1343 ATTRIBUTE3 ,
1344 ATTRIBUTE4 ,
1345 ATTRIBUTE5 ,
1346 ATTRIBUTE6 ,
1347 ATTRIBUTE7 ,
1348 ATTRIBUTE8 ,
1349 ATTRIBUTE9 ,
1350 ATTRIBUTE10 ,
1351 ATTRIBUTE11 ,
1352 ATTRIBUTE12 ,
1353 ATTRIBUTE13 ,
1354 ATTRIBUTE14 ,
1355 ATTRIBUTE15 ,
1356 OBJECT_VERSION_NUMBER
1357 FROM ar_charge_schedules
1358 WHERE schedule_id = P_SCHEDULE_ID
1359 FOR UPDATE NOWAIT;
1360 l_rec c%ROWTYPE;
1361 BEGIN
1362 arp_standard.debug('update_schedule +');
1363 arp_standard.debug(' P_SCHEDULE_ID :'||P_SCHEDULE_ID);
1364 arp_standard.debug(' P_SCHEDULE_DESCRIPTION :'||P_SCHEDULE_DESCRIPTION);
1365
1366 SAVEPOINT update_schedule;
1367
1368 IF fnd_api.to_boolean(p_init_msg_list) THEN
1369 fnd_msg_pub.initialize;
1370 END IF;
1371
1372 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1373
1374 OPEN c;
1375 FETCH c INTO
1376 l_rec.SCHEDULE_ID ,
1377 l_rec.SCHEDULE_NAME ,
1378 l_rec.SCHEDULE_DESCRIPTION,
1379 l_rec.ATTRIBUTE_CATEGORY ,
1380 l_rec.ATTRIBUTE1 ,
1381 l_rec.ATTRIBUTE2 ,
1382 l_rec.ATTRIBUTE3 ,
1383 l_rec.ATTRIBUTE4 ,
1384 l_rec.ATTRIBUTE5 ,
1385 l_rec.ATTRIBUTE6 ,
1386 l_rec.ATTRIBUTE7 ,
1387 l_rec.ATTRIBUTE8 ,
1388 l_rec.ATTRIBUTE9 ,
1389 l_rec.ATTRIBUTE10 ,
1390 l_rec.ATTRIBUTE11 ,
1391 l_rec.ATTRIBUTE12 ,
1392 l_rec.ATTRIBUTE13 ,
1393 l_rec.ATTRIBUTE14 ,
1394 l_rec.ATTRIBUTE15 ,
1395 l_rec.OBJECT_VERSION_NUMBER;
1396 CLOSE c;
1397
1398 IF l_rec.SCHEDULE_ID IS NULL THEN
1399 fnd_message.set_name('AR', 'HZ_API_NO_RECORD');
1400 fnd_message.set_token('RECORD', 'ar_charge_schedules');
1401 fnd_message.set_token('VALUE',
1402 NVL(TO_CHAR(P_SCHEDULE_ID), 'null'));
1403 fnd_msg_pub.add;
1404 RAISE fnd_api.g_exc_error;
1405 END IF;
1406
1407
1408 IF NOT ((x_object_version_number IS NULL AND
1409 l_rec.OBJECT_VERSION_NUMBER IS NULL) OR
1410 (x_object_version_number IS NOT NULL AND
1411 l_rec.OBJECT_VERSION_NUMBER IS NOT NULL AND
1412 x_object_version_number = l_rec.OBJECT_VERSION_NUMBER))
1413 THEN
1414 fnd_message.set_name('AR', 'HZ_API_RECORD_CHANGED');
1415 fnd_message.set_token('TABLE', 'ar_charge_schedules');
1416 fnd_msg_pub.add;
1417 RAISE fnd_api.g_exc_error;
1418 END IF;
1419
1420
1421 validate_schedule
1422 (P_SCHEDULE_NAME => l_rec.SCHEDULE_NAME,
1423 p_schedule_description => P_SCHEDULE_DESCRIPTION,
1424 p_mode => 'UPDATE',
1425 x_return_status => x_return_status);
1426
1427 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1428 RAISE fnd_api.G_EXC_ERROR;
1429 END IF;
1430
1431 Update_schedule_row
1432 (P_SCHEDULE_ID => P_SCHEDULE_ID,
1433 P_SCHEDULE_DESCRIPTION => P_SCHEDULE_DESCRIPTION,
1434 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
1435 P_ATTRIBUTE1 => P_ATTRIBUTE1,
1436 P_ATTRIBUTE2 => P_ATTRIBUTE2,
1437 P_ATTRIBUTE3 => p_attribute3,
1438 P_ATTRIBUTE4 => p_attribute4,
1439 P_ATTRIBUTE5 => p_attribute5,
1440 P_ATTRIBUTE6 => p_attribute6,
1441 P_ATTRIBUTE7 => p_attribute7,
1442 P_ATTRIBUTE8 => p_attribute8,
1443 P_ATTRIBUTE9 => p_attribute9,
1444 P_ATTRIBUTE10 => p_attribute10,
1445 P_ATTRIBUTE11 => p_attribute11,
1446 P_ATTRIBUTE12 => p_attribute12,
1447 P_ATTRIBUTE13 => p_attribute13,
1448 P_ATTRIBUTE14 => p_attribute14,
1449 P_ATTRIBUTE15 => p_attribute15,
1450 X_OBJECT_VERSION_NUMBER=> X_OBJECT_VERSION_NUMBER,
1451 x_return_status => x_return_status);
1452
1453 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1454 RAISE fnd_api.g_exc_error;
1455 END IF;
1456
1457 arp_standard.debug('Update_schedule -');
1458
1459 EXCEPTION
1460 WHEN FND_API.G_EXC_ERROR THEN
1461 ROLLBACK TO Update_schedule;
1462 FND_MSG_PUB.Count_And_Get(
1463 p_encoded => FND_API.G_FALSE,
1464 p_count => x_msg_count,
1465 p_data => x_msg_data);
1466 arp_standard.debug('EXCEPTION Update_schedule:'||x_msg_data);
1467
1468 WHEN OTHERS THEN
1469 ROLLBACK TO Update_schedule;
1470 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1471 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1472 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1473 FND_MSG_PUB.ADD;
1474 FND_MSG_PUB.Count_And_Get(
1475 p_encoded => FND_API.G_FALSE,
1476 p_count => x_msg_count,
1477 p_data => x_msg_data);
1478 arp_standard.debug('EXCEPTION Update_schedule:'||x_msg_data);
1479 END;
1480
1481
1485 PROCEDURE create_schedule_header
1482
1483
1484
1486 (p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1487 P_SCHEDULE_ID IN NUMBER,
1488 P_SCHEDULE_HEADER_TYPE IN VARCHAR2,
1489 P_AGING_BUCKET_ID IN NUMBER,
1490 P_START_DATE IN DATE,
1491 P_END_DATE IN DATE,
1492 P_STATUS IN VARCHAR2,
1493 P_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
1494 P_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
1495 P_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
1496 P_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
1497 P_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
1498 P_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
1499 P_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
1500 P_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
1501 P_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
1502 P_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
1503 P_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
1504 P_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
1505 P_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
1506 P_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
1507 P_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
1508 P_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
1509 P_OBJECT_VERSION_NUMBER IN NUMBER DEFAULT 1,
1510 x_schedule_header_id OUT NOCOPY NUMBER,
1511 x_return_status OUT NOCOPY VARCHAR2,
1512 x_msg_count OUT NOCOPY NUMBER,
1513 x_msg_data OUT NOCOPY VARCHAR2)
1514 IS
1515 CURSOR cu_header_id IS
1516 SELECT ar_charge_schedule_hdrs_s.nextval
1517 FROM DUAL;
1518 l_header_id NUMBER;
1519 BEGIN
1520 arp_standard.debug('create_schedule_header +');
1521 arp_standard.debug(' P_SCHEDULE_HEADER_TYPE :'||P_SCHEDULE_HEADER_TYPE);
1522 arp_standard.debug(' P_AGING_BUCKET_ID :'||P_AGING_BUCKET_ID);
1523 arp_standard.debug(' p_start_date :'||p_start_date);
1524 arp_standard.debug(' p_end_date :'||p_end_date);
1525
1526 savepoint create_schedule_header;
1527
1528 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1529
1530 IF fnd_api.to_boolean(p_init_msg_list) THEN
1531 fnd_msg_pub.initialize;
1532 END IF;
1533
1534
1535 validate_schedule_header
1536 (P_SCHEDULE_HEADER_ID => l_header_id,
1537 P_SCHEDULE_ID => p_schedule_id,
1538 P_SCHEDULE_HEADER_TYPE => P_SCHEDULE_HEADER_TYPE,
1539 P_AGING_BUCKET_ID => P_AGING_BUCKET_ID,
1540 P_START_DATE => p_start_date,
1541 P_END_DATE => p_end_date,
1542 p_status => p_status,
1543 p_old_status => NULL,
1544 p_mode => 'INSERT',
1545 x_return_status => x_return_status);
1546
1547
1548 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1549 RAISE fnd_api.G_EXC_ERROR;
1550 END IF;
1551
1552 OPEN cu_header_id;
1553 FETCH cu_header_id INTO l_header_id;
1554 CLOSE cu_header_id;
1555
1556 Insert_Head_Row
1557 (P_SCHEDULE_HEADER_ID => l_header_id,
1558 P_SCHEDULE_ID => p_schedule_id,
1559 P_SCHEDULE_HEADER_TYPE => P_SCHEDULE_HEADER_TYPE,
1560 P_AGING_BUCKET_ID => P_AGING_BUCKET_ID,
1561 P_START_DATE => p_start_date,
1562 P_END_DATE => p_end_date,
1563 P_STATUS => P_STATUS,
1564 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
1565 P_ATTRIBUTE1 => P_ATTRIBUTE1,
1566 P_ATTRIBUTE2 => P_ATTRIBUTE2,
1567 P_ATTRIBUTE3 => P_ATTRIBUTE3,
1568 P_ATTRIBUTE4 => P_ATTRIBUTE4,
1569 P_ATTRIBUTE5 => P_ATTRIBUTE5,
1570 P_ATTRIBUTE6 => P_ATTRIBUTE6,
1571 P_ATTRIBUTE7 => P_ATTRIBUTE7,
1572 P_ATTRIBUTE8 => P_ATTRIBUTE8,
1573 P_ATTRIBUTE9 => P_ATTRIBUTE9,
1574 P_ATTRIBUTE10 => P_ATTRIBUTE10,
1575 P_ATTRIBUTE11 => P_ATTRIBUTE11,
1576 P_ATTRIBUTE12 => P_ATTRIBUTE12,
1577 P_ATTRIBUTE13 => P_ATTRIBUTE13,
1578 P_ATTRIBUTE14 => P_ATTRIBUTE14,
1579 P_ATTRIBUTE15 => P_ATTRIBUTE15,
1580 P_OBJECT_VERSION_NUMBER => P_OBJECT_VERSION_NUMBER,
1581 x_return_status => x_return_status);
1582
1583
1584 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1585 RAISE fnd_api.G_EXC_ERROR;
1586 END IF;
1587
1588 x_SCHEDULE_HEADER_ID := l_header_id;
1589
1590 arp_standard.debug('create_schedule_header -');
1591
1592 EXCEPTION
1593 WHEN FND_API.G_EXC_ERROR THEN
1594 ROLLBACK TO create_schedule_header;
1595 FND_MSG_PUB.Count_And_Get(
1596 p_encoded => FND_API.G_FALSE,
1597 p_count => x_msg_count,
1598 p_data => x_msg_data);
1599 arp_standard.debug('EXCEPTION create_schedule_header:'||x_msg_data);
1600
1601 WHEN OTHERS THEN
1602 ROLLBACK TO create_schedule_header;
1603 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1604 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1605 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1606 FND_MSG_PUB.ADD;
1607 FND_MSG_PUB.Count_And_Get(
1608 p_encoded => FND_API.G_FALSE,
1609 p_count => x_msg_count,
1610 p_data => x_msg_data);
1611 arp_standard.debug('EXCEPTION create_schedule_header:'||x_msg_data);
1612 END;
1613
1614
1615 PROCEDURE Update_schedule_header
1616 (p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1617 P_SCHEDULE_HEADER_ID IN NUMBER,
1618 P_END_DATE IN DATE,
1619 P_STATUS IN VARCHAR2,
1620 P_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
1621 P_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
1622 P_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
1623 P_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
1624 P_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
1625 P_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
1626 P_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
1627 P_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
1628 P_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
1629 P_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
1630 P_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
1631 P_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
1632 P_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
1633 P_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
1634 P_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
1635 P_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
1636 X_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
1637 x_return_status OUT NOCOPY VARCHAR2,
1638 x_msg_count OUT NOCOPY NUMBER,
1639 x_msg_data OUT NOCOPY VARCHAR2)
1640 IS
1641 CURSOR c IS
1642 SELECT SCHEDULE_ID ,
1643 SCHEDULE_HEADER_ID ,
1644 SCHEDULE_HEADER_TYPE,
1645 AGING_BUCKET_ID ,
1646 START_DATE ,
1647 END_DATE ,
1648 ATTRIBUTE_CATEGORY ,
1649 ATTRIBUTE1 ,
1650 ATTRIBUTE2 ,
1651 ATTRIBUTE3 ,
1652 ATTRIBUTE4 ,
1653 ATTRIBUTE5 ,
1654 ATTRIBUTE6 ,
1655 ATTRIBUTE7 ,
1656 ATTRIBUTE8 ,
1657 ATTRIBUTE9 ,
1658 ATTRIBUTE10 ,
1659 ATTRIBUTE11 ,
1660 ATTRIBUTE12 ,
1661 ATTRIBUTE13 ,
1662 ATTRIBUTE14 ,
1663 ATTRIBUTE15 ,
1664 OBJECT_VERSION_NUMBER,
1665 STATUS
1666 FROM ar_charge_schedule_hdrs
1667 WHERE schedule_header_id = P_SCHEDULE_HEADER_ID
1668 FOR UPDATE NOWAIT;
1669
1670 l_rec c%ROWTYPE;
1671 BEGIN
1672 arp_standard.debug('Update_schedule_header +');
1673 arp_standard.debug(' P_SCHEDULE_HEADER_ID :'||P_SCHEDULE_HEADER_ID);
1674 arp_standard.debug(' p_end_date :'||p_end_date);
1675 arp_standard.debug(' X_OBJECT_VERSION_NUMBER :'||X_OBJECT_VERSION_NUMBER);
1676
1677 savepoint Update_schedule_header;
1678
1679 IF fnd_api.to_boolean(p_init_msg_list) THEN
1680 fnd_msg_pub.initialize;
1681 END IF;
1682
1683 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1684
1685 OPEN c;
1686 FETCH c INTO
1687 l_rec.SCHEDULE_ID ,
1688 l_rec.SCHEDULE_HEADER_ID ,
1689 l_rec.SCHEDULE_HEADER_TYPE,
1690 l_rec.AGING_BUCKET_ID ,
1691 l_rec.START_DATE ,
1692 l_rec.END_DATE ,
1693 l_rec.ATTRIBUTE_CATEGORY ,
1694 l_rec.ATTRIBUTE1 ,
1695 l_rec.ATTRIBUTE2 ,
1696 l_rec.ATTRIBUTE3 ,
1697 l_rec.ATTRIBUTE4 ,
1698 l_rec.ATTRIBUTE5 ,
1699 l_rec.ATTRIBUTE6 ,
1700 l_rec.ATTRIBUTE7 ,
1701 l_rec.ATTRIBUTE8 ,
1702 l_rec.ATTRIBUTE9 ,
1703 l_rec.ATTRIBUTE10 ,
1707 l_rec.ATTRIBUTE14 ,
1704 l_rec.ATTRIBUTE11 ,
1705 l_rec.ATTRIBUTE12 ,
1706 l_rec.ATTRIBUTE13 ,
1708 l_rec.ATTRIBUTE15 ,
1709 l_rec.OBJECT_VERSION_NUMBER,
1710 l_rec.status;
1711 CLOSE c;
1712
1713 IF l_rec.SCHEDULE_HEADER_ID IS NULL THEN
1714 fnd_message.set_name('AR', 'HZ_API_NO_RECORD');
1715 fnd_message.set_token('RECORD', 'ar_charge_schedule_hdrs');
1716 fnd_message.set_token('VALUE',
1717 NVL(TO_CHAR(P_SCHEDULE_HEADER_ID), 'null'));
1718 fnd_msg_pub.add;
1719 RAISE fnd_api.g_exc_error;
1720 END IF;
1721
1722
1723 IF NOT ((x_object_version_number IS NULL AND
1724 l_rec.OBJECT_VERSION_NUMBER IS NULL) OR
1725 (x_object_version_number IS NOT NULL AND
1726 l_rec.OBJECT_VERSION_NUMBER IS NOT NULL AND
1727 x_object_version_number = l_rec.OBJECT_VERSION_NUMBER))
1728 THEN
1729 fnd_message.set_name('AR', 'HZ_API_RECORD_CHANGED');
1730 fnd_message.set_token('TABLE', 'ar_charge_schedule_hdrs');
1731 fnd_msg_pub.add;
1732 RAISE fnd_api.g_exc_error;
1733 END IF;
1734
1735
1736 validate_schedule_header
1737 (P_SCHEDULE_HEADER_ID => l_rec.SCHEDULE_HEADER_ID,
1738 P_SCHEDULE_ID => l_rec.SCHEDULE_ID,
1739 P_SCHEDULE_HEADER_TYPE => l_rec.SCHEDULE_HEADER_TYPE,
1740 P_AGING_BUCKET_ID => l_rec.AGING_BUCKET_ID,
1741 P_START_DATE => l_rec.START_DATE,
1742 P_END_DATE => P_END_DATE,
1743 P_STATUS => p_status,
1744 P_OLD_STATUS => l_rec.status,
1745 p_mode => 'UPDATE',
1746 x_return_status => x_return_status);
1747
1748
1749 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1750 RAISE fnd_api.g_exc_error;
1751 END IF;
1752
1753 Update_Head_Row
1754 (P_SCHEDULE_HEADER_ID => l_rec.SCHEDULE_HEADER_ID,
1755 P_END_DATE => p_end_date,
1756 P_STATUS => P_STATUS,
1757 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
1758 P_ATTRIBUTE1 => P_ATTRIBUTE1,
1759 P_ATTRIBUTE2 => P_ATTRIBUTE2,
1760 P_ATTRIBUTE3 => p_attribute3,
1761 P_ATTRIBUTE4 => p_attribute4,
1762 P_ATTRIBUTE5 => p_attribute5,
1763 P_ATTRIBUTE6 => p_attribute6,
1764 P_ATTRIBUTE7 => p_attribute7,
1765 P_ATTRIBUTE8 => p_attribute8,
1766 P_ATTRIBUTE9 => p_attribute9,
1767 P_ATTRIBUTE10 => p_attribute10,
1768 P_ATTRIBUTE11 => p_attribute11,
1769 P_ATTRIBUTE12 => p_attribute12,
1770 P_ATTRIBUTE13 => p_attribute13,
1771 P_ATTRIBUTE14 => p_attribute14,
1772 P_ATTRIBUTE15 => p_attribute15,
1773 X_OBJECT_VERSION_NUMBER=> X_OBJECT_VERSION_NUMBER,
1774 x_return_status => x_return_status);
1775
1776 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1777 RAISE fnd_api.g_exc_error;
1778 END IF;
1779
1780 arp_standard.debug('Update_schedule_header -');
1781
1782 EXCEPTION
1783 WHEN FND_API.G_EXC_ERROR THEN
1784 ROLLBACK TO Update_schedule_header;
1785 FND_MSG_PUB.Count_And_Get(
1786 p_encoded => FND_API.G_FALSE,
1787 p_count => x_msg_count,
1788 p_data => x_msg_data);
1789 arp_standard.debug('EXCEPTION Update_schedule_header:'||x_msg_data);
1790
1791 WHEN OTHERS THEN
1792 ROLLBACK TO Update_schedule_header;
1793 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1794 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1795 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1796 FND_MSG_PUB.ADD;
1797 FND_MSG_PUB.Count_And_Get(
1798 p_encoded => FND_API.G_FALSE,
1799 p_count => x_msg_count,
1800 p_data => x_msg_data);
1801 arp_standard.debug('EXCEPTION Update_schedule_header:'||x_msg_data);
1802 END;
1803
1804
1805
1806 PROCEDURE create_schedule_line
1807 (p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1808 P_SCHEDULE_HEADER_ID IN NUMBER,
1809 P_SCHEDULE_ID IN NUMBER,
1810 P_AGING_BUCKET_ID IN NUMBER,
1811 P_AGING_BUCKET_LINE_ID IN NUMBER,
1812 P_AMOUNT IN NUMBER,
1813 P_RATE IN NUMBER,
1814 P_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
1815 P_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
1816 P_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
1817 P_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
1818 P_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
1819 P_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
1820 P_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
1821 P_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
1822 P_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
1823 P_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
1824 P_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
1825 P_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
1826 P_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
1827 P_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
1828 P_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
1829 P_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
1830 P_OBJECT_VERSION_NUMBER IN NUMBER DEFAULT 1,
1831 X_SCHEDULE_LINE_ID OUT NOCOPY NUMBER,
1832 x_return_status OUT NOCOPY VARCHAR2,
1833 x_msg_count OUT NOCOPY NUMBER,
1834 x_msg_data OUT NOCOPY VARCHAR2)
1835 IS
1836 CURSOR cu_line_id IS
1837 SELECT ar_charge_schedule_lines_s.NEXTVAL
1838 FROM dual;
1839 l_line_id NUMBER;
1840 BEGIN
1841 arp_standard.debug('create_schedule_line +');
1842 arp_standard.debug(' P_SCHEDULE_HEADER_ID :'||P_SCHEDULE_HEADER_ID);
1843 arp_standard.debug(' P_SCHEDULE_ID :'||P_SCHEDULE_ID);
1844 arp_standard.debug(' P_AGING_BUCKET_ID :'||P_AGING_BUCKET_ID);
1845 arp_standard.debug(' P_AGING_BUCKET_LINE_ID :'||P_AGING_BUCKET_LINE_ID);
1846 arp_standard.debug(' P_AMOUNT :'||P_amount);
1847 arp_standard.debug(' P_RATE :'||P_RATE);
1848
1849 SAVEPOINT create_schedule_line;
1850
1851 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1852
1853 IF fnd_api.to_boolean(p_init_msg_list) THEN
1854 fnd_msg_pub.initialize;
1855 END IF;
1856
1857 validate_schedule_line
1858 (P_SCHEDULE_LINE_ID => l_line_id,
1859 P_SCHEDULE_HEADER_ID => P_SCHEDULE_HEADER_ID,
1860 P_SCHEDULE_ID => P_SCHEDULE_ID,
1861 P_AGING_BUCKET_ID => P_AGING_BUCKET_ID,
1862 P_AGING_BUCKET_LINE_ID => P_AGING_BUCKET_LINE_ID,
1863 P_AMOUNT => p_amount,
1864 P_RATE => p_rate,
1865 P_MODE => 'INSERT',
1866 x_return_status => x_return_status);
1867
1868
1869 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1870 RAISE fnd_api.G_EXC_ERROR;
1871 END IF;
1872
1873 OPEN cu_line_id;
1874 FETCH cu_line_id INTO l_line_id;
1875 CLOSE cu_line_id;
1876
1877
1878 Insert_Line_Row
1879 (P_SCHEDULE_LINE_ID => l_line_id,
1880 P_SCHEDULE_HEADER_ID => P_SCHEDULE_HEADER_ID,
1881 P_SCHEDULE_ID => P_SCHEDULE_ID,
1882 P_AGING_BUCKET_ID => P_AGING_BUCKET_ID,
1883 P_AGING_BUCKET_LINE_ID => P_AGING_BUCKET_LINE_ID,
1884 P_AMOUNT => P_AMOUNT,
1885 P_RATE => P_RATE,
1886 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
1887 P_ATTRIBUTE1 => P_ATTRIBUTE1,
1888 P_ATTRIBUTE2 => P_ATTRIBUTE2,
1889 P_ATTRIBUTE3 => P_ATTRIBUTE3,
1890 P_ATTRIBUTE4 => P_ATTRIBUTE4,
1891 P_ATTRIBUTE5 => P_ATTRIBUTE5,
1892 P_ATTRIBUTE6 => P_ATTRIBUTE6,
1893 P_ATTRIBUTE7 => P_ATTRIBUTE7,
1894 P_ATTRIBUTE8 => P_ATTRIBUTE8,
1895 P_ATTRIBUTE9 => P_ATTRIBUTE9,
1896 P_ATTRIBUTE10 => P_ATTRIBUTE10,
1897 P_ATTRIBUTE11 => P_ATTRIBUTE11,
1898 P_ATTRIBUTE12 => P_ATTRIBUTE12,
1899 P_ATTRIBUTE13 => P_ATTRIBUTE13,
1900 P_ATTRIBUTE14 => P_ATTRIBUTE14,
1901 P_ATTRIBUTE15 => P_ATTRIBUTE15,
1902 P_OBJECT_VERSION_NUMBER => P_OBJECT_VERSION_NUMBER,
1903 x_return_status => x_return_status);
1904
1905 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1906 RAISE fnd_api.g_exc_error;
1907 END IF;
1908
1909 X_SCHEDULE_LINE_ID := l_line_id;
1910
1911 arp_standard.debug('create_schedule_line -');
1912
1913 EXCEPTION
1914 WHEN FND_API.G_EXC_ERROR THEN
1915 ROLLBACK TO create_schedule_line;
1916 FND_MSG_PUB.Count_And_Get(
1917 p_encoded => FND_API.G_FALSE,
1918 p_count => x_msg_count,
1919 p_data => x_msg_data);
1920 arp_standard.debug('EXCEPTION create_schedule_line :'||x_msg_data);
1921
1922 WHEN OTHERS THEN
1923 ROLLBACK TO create_schedule_line;
1924 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1925 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1926 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1927 FND_MSG_PUB.ADD;
1928 FND_MSG_PUB.Count_And_Get(
1929 p_encoded => FND_API.G_FALSE,
1930 p_count => x_msg_count,
1931 p_data => x_msg_data);
1932 arp_standard.debug('EXCEPTION create_schedule_line :'||x_msg_data);
1933 END;
1934
1935 PROCEDURE Update_schedule_line
1936 (p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1937 P_SCHEDULE_line_ID IN NUMBER,
1938 P_amount IN NUMBER,
1939 P_rate IN NUMBER,
1940 P_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
1941 P_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
1942 P_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
1943 P_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
1944 P_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
1945 P_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
1946 P_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
1947 P_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
1948 P_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
1949 P_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
1950 P_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
1951 P_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
1952 P_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
1953 P_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
1954 P_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
1955 P_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
1956 X_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
1957 x_return_status OUT NOCOPY VARCHAR2,
1958 x_msg_count OUT NOCOPY NUMBER,
1959 x_msg_data OUT NOCOPY VARCHAR2)
1960 IS
1961 CURSOR c IS
1962 SELECT SCHEDULE_ID ,
1963 SCHEDULE_HEADER_ID ,
1964 schedule_line_id ,
1965 amount ,
1966 rate ,
1967 ATTRIBUTE_CATEGORY ,
1968 ATTRIBUTE1 ,
1969 ATTRIBUTE2 ,
1970 ATTRIBUTE3 ,
1971 ATTRIBUTE4 ,
1972 ATTRIBUTE5 ,
1973 ATTRIBUTE6 ,
1974 ATTRIBUTE7 ,
1975 ATTRIBUTE8 ,
1976 ATTRIBUTE9 ,
1977 ATTRIBUTE10 ,
1978 ATTRIBUTE11 ,
1979 ATTRIBUTE12 ,
1980 ATTRIBUTE13 ,
1981 ATTRIBUTE14 ,
1982 ATTRIBUTE15 ,
1983 OBJECT_VERSION_NUMBER
1984 FROM ar_charge_schedule_lines
1985 WHERE schedule_line_id = P_SCHEDULE_LINE_ID
1986 FOR UPDATE NOWAIT;
1987
1988 l_rec c%ROWTYPE;
1989 BEGIN
1990 arp_standard.debug('Update_schedule_line +');
1991 arp_standard.debug(' P_SCHEDULE_line_ID :'||P_SCHEDULE_line_ID);
1992 arp_standard.debug(' p_amount :'||p_amount);
1993 arp_standard.debug(' p_rate :'||p_rate);
1994 arp_standard.debug(' X_OBJECT_VERSION_NUMBER :'||X_OBJECT_VERSION_NUMBER);
1995
1996 savepoint Update_schedule_line;
1997
1998 IF fnd_api.to_boolean(p_init_msg_list) THEN
1999 fnd_msg_pub.initialize;
2000 END IF;
2001
2002 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2003
2004 OPEN c;
2005 FETCH c INTO
2006 l_rec.SCHEDULE_ID ,
2007 l_rec.SCHEDULE_HEADER_ID ,
2008 l_rec.schedule_line_id ,
2009 l_rec.amount ,
2010 l_rec.rate ,
2011 l_rec.ATTRIBUTE_CATEGORY ,
2012 l_rec.ATTRIBUTE1 ,
2013 l_rec.ATTRIBUTE2 ,
2014 l_rec.ATTRIBUTE3 ,
2015 l_rec.ATTRIBUTE4 ,
2016 l_rec.ATTRIBUTE5 ,
2017 l_rec.ATTRIBUTE6 ,
2018 l_rec.ATTRIBUTE7 ,
2019 l_rec.ATTRIBUTE8 ,
2020 l_rec.ATTRIBUTE9 ,
2021 l_rec.ATTRIBUTE10 ,
2022 l_rec.ATTRIBUTE11 ,
2023 l_rec.ATTRIBUTE12 ,
2024 l_rec.ATTRIBUTE13 ,
2025 l_rec.ATTRIBUTE14 ,
2026 l_rec.ATTRIBUTE15 ,
2027 l_rec.OBJECT_VERSION_NUMBER;
2028 CLOSE c;
2029
2030 IF l_rec.SCHEDULE_line_ID IS NULL THEN
2031 fnd_message.set_name('AR', 'HZ_API_NO_RECORD');
2032 fnd_message.set_token('RECORD', 'ar_charge_schedule_lines');
2033 fnd_message.set_token('VALUE',
2034 NVL(TO_CHAR(P_SCHEDULE_line_ID), 'null'));
2035 fnd_msg_pub.add;
2036 RAISE fnd_api.g_exc_error;
2037 END IF;
2038
2039
2040 IF NOT ((x_object_version_number IS NULL AND
2041 l_rec.OBJECT_VERSION_NUMBER IS NULL) OR
2042 (x_object_version_number IS NOT NULL AND
2043 l_rec.OBJECT_VERSION_NUMBER IS NOT NULL AND
2044 x_object_version_number = l_rec.OBJECT_VERSION_NUMBER))
2045 THEN
2046 fnd_message.set_name('AR', 'HZ_API_RECORD_CHANGED');
2047 fnd_message.set_token('TABLE', 'ar_charge_schedule_lines');
2048 fnd_msg_pub.add;
2049 RAISE fnd_api.g_exc_error;
2050 END IF;
2051
2052 /* This would be the place to call validate_line
2053 validate_schedule_line
2054 (P_SCHEDULE_LINE_ID => l_line_id,
2055 P_SCHEDULE_HEADER_ID => P_SCHEDULE_HEADER_ID,
2056 P_SCHEDULE_ID => P_SCHEDULE_ID,
2057 P_AGING_BUCKET_ID => P_AGING_BUCKET_ID,
2058 P_AGING_BUCKET_LINE_ID => P_AGING_BUCKET_LINE_ID,
2059 P_AMOUNT => p_amount,
2060 P_RATE => p_rate,
2061 P_MODE => 'UPDATE',
2062 x_return_status => x_return_status);
2063
2064 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
2065 RAISE fnd_api.g_exc_error;
2066 END IF;
2067 */
2068
2069 Update_line_Row
2070 (P_SCHEDULE_line_ID => l_rec.SCHEDULE_line_ID,
2071 P_amount => p_amount,
2072 P_rate => P_rate,
2073 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
2074 P_ATTRIBUTE1 => P_ATTRIBUTE1,
2075 P_ATTRIBUTE2 => P_ATTRIBUTE2,
2076 P_ATTRIBUTE3 => p_attribute3,
2077 P_ATTRIBUTE4 => p_attribute4,
2078 P_ATTRIBUTE5 => p_attribute5,
2079 P_ATTRIBUTE6 => p_attribute6,
2080 P_ATTRIBUTE7 => p_attribute7,
2081 P_ATTRIBUTE8 => p_attribute8,
2082 P_ATTRIBUTE9 => p_attribute9,
2083 P_ATTRIBUTE10 => p_attribute10,
2084 P_ATTRIBUTE11 => p_attribute11,
2085 P_ATTRIBUTE12 => p_attribute12,
2086 P_ATTRIBUTE13 => p_attribute13,
2087 P_ATTRIBUTE14 => p_attribute14,
2088 P_ATTRIBUTE15 => p_attribute15,
2089 X_OBJECT_VERSION_NUMBER=> X_OBJECT_VERSION_NUMBER,
2090 x_return_status => x_return_status);
2091
2092 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
2093 RAISE fnd_api.g_exc_error;
2094 END IF;
2095
2096 arp_standard.debug('Update_schedule_line -');
2097
2098 EXCEPTION
2099 WHEN FND_API.G_EXC_ERROR THEN
2100 ROLLBACK TO Update_schedule_line;
2101 FND_MSG_PUB.Count_And_Get(
2102 p_encoded => FND_API.G_FALSE,
2103 p_count => x_msg_count,
2104 p_data => x_msg_data);
2105 arp_standard.debug('EXCEPTION Update_schedule_line:'||x_msg_data);
2106
2107 WHEN OTHERS THEN
2108 ROLLBACK TO Update_schedule_header;
2109 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2110 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2111 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2112 FND_MSG_PUB.ADD;
2113 FND_MSG_PUB.Count_And_Get(
2114 p_encoded => FND_API.G_FALSE,
2115 p_count => x_msg_count,
2116 p_data => x_msg_data);
2117 arp_standard.debug('EXCEPTION Update_schedule_line:'||x_msg_data);
2118 END;
2119
2120
2121 END;