[Home] [Help]
PACKAGE BODY: APPS.AR_CHARGE_SCHEDULE_PKG
Source
1 PACKAGE BODY ar_charge_schedule_pkg AS
2 /* $Header: ARSCAMTB.pls 120.3 2006/03/31 02:32:00 hyu noship $ */
3
4 ----------------------------------
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),
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),
67 DECODE(P_ATTRIBUTE5 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE5),
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,
167 FND_API.G_MISS_CHAR, NULL,
168 NULL , ATTRIBUTE14, P_ATTRIBUTE14),
169 ATTRIBUTE15 = DECODE(P_ATTRIBUTE15,
170 FND_API.G_MISS_CHAR, NULL,
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),
265 DECODE(P_ATTRIBUTE6 ,FND_API.G_MISS_CHAR,NULL,P_ATTRIBUTE6),
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),
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),
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),
275 P_OBJECT_VERSION_NUMBER,
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),
375 ATTRIBUTE14 = DECODE(P_ATTRIBUTE14,
372 ATTRIBUTE13 = DECODE(P_ATTRIBUTE13,
373 FND_API.G_MISS_CHAR, NULL,
374 NULL , ATTRIBUTE13, P_ATTRIBUTE13),
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,
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)
385 WHERE SCHEDULE_HEADER_ID = P_SCHEDULE_HEADER_ID
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,
508 P_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
505 P_RATE IN NUMBER,
506 P_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
507 P_ATTRIBUTE1 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,
517 P_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
518 P_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
519 P_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
520 P_ATTRIBUTE14 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
619 BEGIN
616 TRUNC(NVL( END_DATE_ACTIVE,SYSDATE ) ) );
617 l_c VARCHAR2(30);
618 l_result BOOLEAN;
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;
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;
645 ELSIF (ldate2 IS NULL OR ldate2 = FND_API.G_MISS_DATE) THEN
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;
777 -------------------------------------------------------------
774 arp_standard.debug(' Validate schedule_header_type -');
775
776
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 fnd_message.set_token('START_DATE_PER_1', l_start_date);
867 fnd_message.set_token('END_DATE_PER_1' , l_end_date);
868 fnd_message.set_token('START_DATE_PER_2', p_start_date);
869 fnd_message.set_token('END_DATE_PER_2' , p_end_date);
870 fnd_msg_pub.add;
871 x_return_status := fnd_api.g_ret_sts_error;
872 END IF;
873 END IF;
874 IF x_return_status <> fnd_api.g_ret_sts_success THEN
875 EXIT;
876 END IF;
877 END LOOP;
878 CLOSE c_overlapp;
879 END IF;
880 arp_standard.debug(' validate start and end dates -');
881
882 END IF;
883
884
885 IF p_mode IN ('UPDATE') THEN
886 -------------------------------------------------------------
887 -- Validate STATUS lookup code in lookup type REGISTRY_STATUS
888 -------------------------------------------------------------
889 arp_standard.debug(' Validate status +');
890 IF p_status = fnd_api.g_miss_char THEN
891 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
892 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'status' );
893 FND_MSG_PUB.ADD;
894 x_return_status := FND_API.G_RET_STS_ERROR;
895 END IF;
896
897 IF p_status IS NOT NULL THEN
898 IF validate_lookup('REGISTRY_STATUS',p_status) = FALSE THEN
899 arp_standard.debug(' status should be lookup code for the lookup type REGISTRY_STATUS');
900 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_LOOKUP' );
901 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'status' );
905 END IF;
902 FND_MESSAGE.SET_TOKEN( 'LOOKUP_TYPE', 'REGISTRY_STATUS' );
903 FND_MSG_PUB.ADD;
904 x_return_status := FND_API.G_RET_STS_ERROR;
906 END IF;
907
908 IF p_old_status = 'I' AND p_status = 'A' THEN
909 arp_standard.debug(' Schedule Header can not be reactivated');
910 FND_MESSAGE.SET_NAME( 'AR', 'AR_NO_REACTIVATE_ALLOW' );
911 FND_MSG_PUB.ADD;
912 x_return_status := FND_API.G_RET_STS_ERROR;
913 END IF;
914
915 arp_standard.debug(' Validate status -');
916
917 -----------------------------------
918 -- Validate start_date and end_date
919 -----------------------------------
920 arp_standard.debug(' validate start and end dates +');
921 IF p_end_date IS NOT NULL AND p_end_date <> FND_API.G_MISS_DATE THEN
922 l_val := compare(date1 => p_start_date,
923 date2 => p_end_date );
924 IF l_val = 1 THEN
925 arp_standard.debug(' end date should be greater than the start date');
926 fnd_message.set_name('AR', 'HZ_API_DATE_GREATER');
927 fnd_message.set_token('DATE2', 'end_date');
928 fnd_message.set_token('DATE1', 'start_date');
929 fnd_msg_pub.add;
930 x_return_status := fnd_api.g_ret_sts_error;
931 END IF;
932 IF x_return_status = fnd_api.g_ret_sts_success THEN
933 arp_standard.debug(' Check overlappings of periods');
934 OPEN c_overlapp_upd;
935 LOOP
936 FETCH c_overlapp_upd INTO l_start_date, l_end_date;
937 EXIT WHEN c_overlapp_upd%NOTFOUND;
938 l_cpt := l_cpt + 1;
939 l_val := compare(p_start_date,l_end_date);
940 IF l_val = 1 THEN
941 NULL; -- OK the existing period start after the entered period
942 ELSE
943 l_val := compare(l_start_date,p_end_date);
944 IF l_val = 1 THEN
945 NULL; -- Ok the existing period ends before the entered period start
946 ELSE
947 arp_standard.debug(' overlapping issue :');
948 arp_standard.debug(' existing start :'||l_start_date|| ' end :'||l_end_date);
949 arp_standard.debug(' entered start :'||p_start_date|| ' end :'||p_end_date);
950 fnd_message.set_name('AR', 'AR_DATE_OVERLAPP');
951 fnd_message.set_token('START_DATE_PER_1', l_start_date);
952 fnd_message.set_token('END_DATE_PER_1' , l_end_date);
953 fnd_message.set_token('START_DATE_PER_2', p_start_date);
954 fnd_message.set_token('END_DATE_PER_2' , p_end_date);
955 fnd_msg_pub.add;
956 x_return_status := fnd_api.g_ret_sts_error;
957 END IF;
958 END IF;
959 IF x_return_status <> fnd_api.g_ret_sts_success THEN
960 EXIT;
961 END IF;
962 END LOOP;
963 CLOSE c_overlapp_upd;
964 END IF;
965 arp_standard.debug(' validate start and end dates -');
966 END IF;
967 END IF;
968 arp_standard.debug('validate_schedule_header -');
969 END;
970
971
972 PROCEDURE validate_schedule_line
973 (P_SCHEDULE_LINE_ID IN NUMBER,
974 P_SCHEDULE_HEADER_ID IN NUMBER,
975 P_SCHEDULE_ID IN NUMBER,
976 P_AGING_BUCKET_ID IN NUMBER,
977 P_AGING_BUCKET_LINE_ID IN NUMBER,
978 P_AMOUNT IN NUMBER,
979 P_RATE IN NUMBER,
980 P_MODE IN VARCHAR2,
981 x_return_status IN OUT NOCOPY VARCHAR2)
982 IS
983 CURSOR c_header IS
984 SELECT aging_bucket_id,
985 schedule_id,
986 schedule_header_id
987 FROM ar_charge_schedule_hdrs
988 WHERE schedule_header_id = p_schedule_header_id;
989
990 l_rec c_header%ROWTYPE;
991
992 CURSOR c_aging_bucket_id(p_aging_bucket_id IN NUMBER,
993 p_aging_bucket_line_id IN NUMBER) IS
994 SELECT NULL
995 FROM ar_aging_buckets a,
996 ar_aging_bucket_lines_b b
997 WHERE a.aging_bucket_id = p_aging_bucket_id
998 AND a.status = 'A'
999 AND a.aging_bucket_id = b.aging_bucket_id
1000 AND b.aging_bucket_line_id = p_aging_bucket_line_id;
1001
1002 CURSOR ar_schedule_line_u2(
1003 p_SCHEDULE_HEADER_ID IN NUMBER,
1004 p_aging_bucket_id IN NUMBER,
1005 p_aging_bucket_line_id IN NUMBER)
1006 IS
1007 SELECT NULL
1008 FROM ar_charge_schedule_lines
1009 WHERE schedule_header_id = p_SCHEDULE_HEADER_ID
1010 AND aging_bucket_id = p_aging_bucket_id
1011 AND aging_bucket_line_id = p_aging_bucket_line_id;
1012
1013 CURSOR cl IS
1014 SELECT lookup_code
1015 FROM ar_charge_schedule_hdrs a,
1016 ar_lookups b
1017 WHERE a.schedule_header_id = P_SCHEDULE_HEADER_ID
1018 AND b.lookup_type = 'SCHEDULE_HEADER_TYPE'
1019 AND b.lookup_code = a.schedule_header_type;
1020
1021 l_c VARCHAR2(30);
1022 BEGIN
1023 -------------------------------------
1024 -- Validate schedule_header_id
1025 -------------------------------------
1026 arp_standard.debug(' Validate schedule_header_id +');
1027
1028 IF p_schedule_header_id IS NULL OR p_schedule_header_id = fnd_api.g_miss_num THEN
1029 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
1030 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'schedule_header_id' );
1031 FND_MSG_PUB.ADD;
1032 x_return_status := FND_API.G_RET_STS_ERROR;
1033 END IF;
1034
1035 OPEN c_header;
1036 FETCH c_header INTO l_rec;
1037 IF c_header%NOTFOUND THEN
1038 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_FK');
1039 FND_MESSAGE.SET_TOKEN('FK', 'schedule header');
1043 x_return_status := FND_API.G_RET_STS_ERROR;
1040 FND_MESSAGE.SET_TOKEN('COLUMN', 'schedule_header_id');
1041 FND_MESSAGE.SET_TOKEN('TABLE', 'ar_charge_schedule_hdrs');
1042 FND_MSG_PUB.ADD;
1044 END IF;
1045 CLOSE c_header;
1046 arp_standard.debug(' Validate schedule_header_id -');
1047
1048
1049 ------------------------
1050 -- Validate schedule_id
1051 ------------------------
1052 arp_standard.debug(' Validate schedule_id +');
1053 IF p_schedule_id IS NULL OR p_schedule_id = fnd_api.g_miss_num THEN
1054 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
1055 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'schedule_id' );
1056 FND_MSG_PUB.ADD;
1057 x_return_status := FND_API.G_RET_STS_ERROR;
1058 ELSE
1059 IF p_schedule_id <> l_rec.schedule_id THEN
1060 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_FK');
1061 FND_MESSAGE.SET_TOKEN('FK', 'schedule header');
1062 FND_MESSAGE.SET_TOKEN('COLUMN', 'schedule_id');
1063 FND_MESSAGE.SET_TOKEN('TABLE', 'ar_charge_schedule_hdrs');
1064 FND_MSG_PUB.ADD;
1065 x_return_status := FND_API.G_RET_STS_ERROR;
1066 END IF;
1067 END IF;
1068 arp_standard.debug(' Validate schedule_id -');
1069
1070
1071 ---------------------------
1072 -- Validate aging_bucket_id
1073 ---------------------------
1074 arp_standard.debug(' Validate aging_bucket_id +');
1075 IF P_AGING_BUCKET_ID IS NULL OR P_AGING_BUCKET_ID = fnd_api.g_miss_num THEN
1076 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
1077 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'P_AGING_BUCKET_ID' );
1078 FND_MSG_PUB.ADD;
1079 x_return_status := FND_API.G_RET_STS_ERROR;
1080 ELSE
1081 IF P_AGING_BUCKET_ID <> l_rec.aging_bucket_id THEN
1082 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_FK');
1083 FND_MESSAGE.SET_TOKEN('FK', 'schedule header');
1084 FND_MESSAGE.SET_TOKEN('COLUMN', 'aging_bucket_id');
1085 FND_MESSAGE.SET_TOKEN('TABLE', 'ar_charge_schedule_hdrs');
1086 FND_MSG_PUB.ADD;
1087 x_return_status := FND_API.G_RET_STS_ERROR;
1088 END IF;
1089 END IF;
1090 arp_standard.debug(' Validate aging_bucket_id -');
1091
1092
1093 ----------------------------------------------------
1094 -- Unidicity of the combination schedule_header_id, aging_bucket, aging_bucket_line
1095 ----------------------------------------------------
1096 arp_standard.debug(' unidicity of ar_schedule_line_u2 +');
1097 OPEN ar_schedule_line_u2(
1098 p_SCHEDULE_HEADER_ID ,
1099 p_aging_bucket_id ,
1100 p_aging_bucket_line_id);
1101 FETCH ar_schedule_line_u2 INTO l_c;
1102 IF ar_schedule_line_u2%FOUND THEN
1103 arp_standard.debug(' A Record in ar_charge_schedule_lines exists with '||
1104 ' schedule_header_id - aging_bucket_id - aging_bucket_line_id');
1105 fnd_message.set_name('AR', 'AR_API_REC_COMB_EXISTS');
1106 fnd_message.set_token('COLUMN1', 'schedule_header_id');
1107 fnd_message.set_token('COLUMN2', 'aging_bucket_id');
1108 fnd_message.set_token('COLUMN3', 'aging_bucket_line_id');
1109 fnd_msg_pub.add;
1110 x_return_status := fnd_api.g_ret_sts_error;
1111 END IF;
1112 CLOSE ar_schedule_line_u2;
1113 arp_standard.debug(' unidicity of ar_schedule_line_u2 -');
1114
1115
1116 ---------------------------------
1117 -- Validate aging_bucket_line_id
1118 ---------------------------------
1119 arp_standard.debug(' Validate aging_bucket_id and aging_bucket_line_id +');
1120 IF p_aging_bucket_line_id IS NULL OR p_aging_bucket_line_id = fnd_api.g_miss_num
1121 THEN
1122 IF p_aging_bucket_line_id IS NULL or p_aging_bucket_line_id = fnd_api.g_miss_num THEN
1123 arp_standard.debug(' AGING_BUCKET_LINE_ID missing');
1124 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
1125 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'AGING_BUCKET_LINE_ID' );
1126 FND_MSG_PUB.ADD;
1127 x_return_status := FND_API.G_RET_STS_ERROR;
1128 END IF;
1129 ELSE
1130 IF p_aging_bucket_id IS NOT NULL AND p_aging_bucket_id <> FND_API.G_MISS_NUM THEN
1131 OPEN c_aging_bucket_id(p_aging_bucket_id, p_aging_bucket_line_id);
1132 FETCH c_aging_bucket_id INTO l_c;
1133 IF c_aging_bucket_id%NOTFOUND THEN
1134 arp_standard.debug(' AGING_BUCKET_ID/AGING_BUCKET_LINE_ID are not valid');
1135 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_FK' );
1136 FND_MESSAGE.SET_TOKEN( 'FK', 'aging_bucket_id' );
1137 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'aging_bucket_id' );
1138 FND_MESSAGE.SET_TOKEN( 'TABLE', 'ar_aging_buckets');
1139 FND_MSG_PUB.ADD;
1140 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_FK' );
1141 FND_MESSAGE.SET_TOKEN( 'FK', 'aging_bucket_line_id' );
1142 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'aging_bucket_line_id' );
1143 FND_MESSAGE.SET_TOKEN( 'TABLE', 'ar_aging_bucket_lines_b');
1144 FND_MSG_PUB.ADD;
1145 x_return_status := FND_API.G_RET_STS_ERROR;
1146 END IF;
1147 CLOSE c_aging_bucket_id;
1148 END IF;
1149 END IF;
1150 arp_standard.debug(' Validate aging_bucket_line_id -');
1151
1152
1153
1154 --------------------------
1155 -- Validate amount or rate
1156 --------------------------
1157 arp_standard.debug(' validate amount or rate +');
1158
1159 OPEN cl;
1160 FETCH cl INTO l_c;
1161 CLOSE cl;
1162
1163 IF (p_amount IS NULL OR p_amount = FND_API.G_MISS_NUM) AND
1164 (l_c = 'AMOUNT')
1165 THEN
1166 arp_standard.debug(' The amount column is mandatory for SCHEDULE_HEADER_TYPE :'||l_c);
1167 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
1168 fnd_message.set_token('COLUMN', 'AMOUNT');
1169 FND_MSG_PUB.ADD;
1170 x_return_status := FND_API.G_RET_STS_ERROR;
1171 END IF;
1172
1176 arp_standard.debug(' The rate column is mandatory for SCHEDULE_HEADER_TYPE:'||l_c);
1173 IF (p_rate IS NULL OR p_rate = FND_API.G_MISS_NUM) AND
1174 (l_c = 'PERCENTAGE')
1175 THEN
1177 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
1178 fnd_message.set_token('COLUMN', 'RATE');
1179 fnd_msg_pub.add;
1180 FND_MSG_PUB.ADD;
1181 x_return_status := FND_API.G_RET_STS_ERROR;
1182 END IF;
1183 arp_standard.debug(' validate amount or rate -');
1184 END;
1185
1186
1187
1188
1189
1190 PROCEDURE create_schedule
1191 (p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1192 P_SCHEDULE_NAME IN VARCHAR2,
1193 P_SCHEDULE_DESCRIPTION IN VARCHAR2,
1194 P_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
1195 P_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
1196 P_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
1197 P_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
1198 P_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
1199 P_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
1200 P_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
1201 P_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
1202 P_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
1203 P_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
1204 P_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
1205 P_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
1206 P_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
1207 P_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
1208 P_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
1209 P_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
1210 P_OBJECT_VERSION_NUMBER IN NUMBER DEFAULT 1,
1211 x_schedule_id OUT NOCOPY NUMBER,
1212 x_return_status OUT NOCOPY VARCHAR2,
1213 x_msg_count OUT NOCOPY NUMBER,
1214 x_msg_data OUT NOCOPY VARCHAR2)
1215 IS
1216 CURSOR cu_schedule_id IS
1217 SELECT ar_charge_schedules_s.nextval
1218 FROM DUAL;
1219 l_schedule_id NUMBER;
1220 l_n VARCHAR2(10);
1221 BEGIN
1222 l_n := 0;
1223 arp_standard.debug('create_schedule +');
1224 arp_standard.debug(' P_SCHEDULE_NAME :'||P_SCHEDULE_NAME);
1225 arp_standard.debug(' P_SCHEDULE_DESCRIPTION :'||P_SCHEDULE_DESCRIPTION);
1226
1227 SAVEPOINT create_schedule;
1228
1229 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1230
1231 IF fnd_api.to_boolean(p_init_msg_list) THEN
1232 fnd_msg_pub.initialize;
1233 END IF;
1234
1235 l_n := 1;
1236
1237 validate_schedule
1238 (P_SCHEDULE_NAME => p_schedule_name,
1239 p_schedule_description => P_SCHEDULE_DESCRIPTION,
1240 p_mode => 'INSERT',
1241 x_return_status => x_return_status);
1242
1243
1244 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1245 RAISE fnd_api.G_EXC_ERROR;
1246 END IF;
1247
1248 l_n := 2;
1249 OPEN cu_schedule_id;
1250 FETCH cu_schedule_id INTO l_schedule_id;
1251 CLOSE cu_schedule_id;
1252
1253 l_n := 3;
1254 Insert_schedule_Row
1255 (P_SCHEDULE_ID => l_schedule_id,
1256 P_SCHEDULE_NAME => P_SCHEDULE_NAME,
1257 P_SCHEDULE_DESCRIPTION=> P_SCHEDULE_DESCRIPTION,
1258 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
1259 P_ATTRIBUTE1 => P_ATTRIBUTE1,
1260 P_ATTRIBUTE2 => P_ATTRIBUTE2,
1261 P_ATTRIBUTE3 => P_ATTRIBUTE3,
1262 P_ATTRIBUTE4 => P_ATTRIBUTE4,
1263 P_ATTRIBUTE5 => P_ATTRIBUTE5,
1264 P_ATTRIBUTE6 => P_ATTRIBUTE6,
1265 P_ATTRIBUTE7 => P_ATTRIBUTE7,
1266 P_ATTRIBUTE8 => P_ATTRIBUTE8,
1267 P_ATTRIBUTE9 => P_ATTRIBUTE9,
1268 P_ATTRIBUTE10 => P_ATTRIBUTE10,
1269 P_ATTRIBUTE11 => P_ATTRIBUTE11,
1270 P_ATTRIBUTE12 => P_ATTRIBUTE12,
1271 P_ATTRIBUTE13 => P_ATTRIBUTE13,
1272 P_ATTRIBUTE14 => P_ATTRIBUTE14,
1273 P_ATTRIBUTE15 => P_ATTRIBUTE15,
1274 P_OBJECT_VERSION_NUMBER => P_OBJECT_VERSION_NUMBER,
1275 x_return_status => x_return_status);
1276
1277 l_n := 4;
1278 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1279 RAISE fnd_api.G_EXC_ERROR;
1280 END IF;
1281
1282 x_schedule_id := l_schedule_id;
1283
1284 arp_standard.debug('create_schedule -');
1285
1286 EXCEPTION
1287 WHEN FND_API.G_EXC_ERROR THEN
1288 ROLLBACK TO create_schedule;
1289 FND_MSG_PUB.Count_And_Get(
1290 p_encoded => FND_API.G_FALSE,
1291 p_count => x_msg_count,
1292 p_data => x_msg_data);
1293 arp_standard.debug('EXCEPTION create_schedule:'||x_msg_data);
1294
1295 WHEN OTHERS THEN
1296 ROLLBACK TO create_schedule;
1297 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1298 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1299 FND_MESSAGE.SET_TOKEN('ERROR' ,l_n||' '||SQLERRM);
1300 FND_MSG_PUB.ADD;
1301 FND_MSG_PUB.Count_And_Get(
1302 p_encoded => FND_API.G_FALSE,
1303 p_count => x_msg_count,
1304 p_data => x_msg_data);
1305 arp_standard.debug('EXCEPTION create_schedule:'||x_msg_data);
1306 END;
1307
1308
1309 PROCEDURE update_schedule
1310 (p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1311 P_SCHEDULE_ID IN NUMBER,
1312 P_SCHEDULE_DESCRIPTION IN VARCHAR2,
1313 P_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
1314 P_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
1315 P_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
1316 P_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
1317 P_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
1318 P_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
1319 P_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
1323 P_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
1320 P_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
1321 P_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
1322 P_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
1324 P_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
1325 P_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
1326 P_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
1327 P_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
1328 P_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
1329 X_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
1330 x_return_status OUT NOCOPY VARCHAR2,
1331 x_msg_count OUT NOCOPY NUMBER,
1332 x_msg_data OUT NOCOPY VARCHAR2)
1333 IS
1334 CURSOR c IS
1335 SELECT SCHEDULE_ID ,
1336 SCHEDULE_NAME ,
1337 SCHEDULE_DESCRIPTION,
1338 ATTRIBUTE_CATEGORY ,
1339 ATTRIBUTE1 ,
1340 ATTRIBUTE2 ,
1341 ATTRIBUTE3 ,
1342 ATTRIBUTE4 ,
1343 ATTRIBUTE5 ,
1344 ATTRIBUTE6 ,
1345 ATTRIBUTE7 ,
1346 ATTRIBUTE8 ,
1347 ATTRIBUTE9 ,
1348 ATTRIBUTE10 ,
1349 ATTRIBUTE11 ,
1350 ATTRIBUTE12 ,
1351 ATTRIBUTE13 ,
1352 ATTRIBUTE14 ,
1353 ATTRIBUTE15 ,
1354 OBJECT_VERSION_NUMBER
1355 FROM ar_charge_schedules
1356 WHERE schedule_id = P_SCHEDULE_ID
1357 FOR UPDATE NOWAIT;
1358 l_rec c%ROWTYPE;
1359 BEGIN
1360 arp_standard.debug('update_schedule +');
1361 arp_standard.debug(' P_SCHEDULE_ID :'||P_SCHEDULE_ID);
1362 arp_standard.debug(' P_SCHEDULE_DESCRIPTION :'||P_SCHEDULE_DESCRIPTION);
1363
1364 SAVEPOINT update_schedule;
1365
1366 IF fnd_api.to_boolean(p_init_msg_list) THEN
1367 fnd_msg_pub.initialize;
1368 END IF;
1369
1370 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1371
1372 OPEN c;
1373 FETCH c INTO
1374 l_rec.SCHEDULE_ID ,
1375 l_rec.SCHEDULE_NAME ,
1376 l_rec.SCHEDULE_DESCRIPTION,
1377 l_rec.ATTRIBUTE_CATEGORY ,
1378 l_rec.ATTRIBUTE1 ,
1379 l_rec.ATTRIBUTE2 ,
1380 l_rec.ATTRIBUTE3 ,
1381 l_rec.ATTRIBUTE4 ,
1382 l_rec.ATTRIBUTE5 ,
1383 l_rec.ATTRIBUTE6 ,
1384 l_rec.ATTRIBUTE7 ,
1385 l_rec.ATTRIBUTE8 ,
1386 l_rec.ATTRIBUTE9 ,
1387 l_rec.ATTRIBUTE10 ,
1388 l_rec.ATTRIBUTE11 ,
1389 l_rec.ATTRIBUTE12 ,
1390 l_rec.ATTRIBUTE13 ,
1391 l_rec.ATTRIBUTE14 ,
1392 l_rec.ATTRIBUTE15 ,
1393 l_rec.OBJECT_VERSION_NUMBER;
1394 CLOSE c;
1395
1396 IF l_rec.SCHEDULE_ID IS NULL THEN
1397 fnd_message.set_name('AR', 'HZ_API_NO_RECORD');
1398 fnd_message.set_token('RECORD', 'ar_charge_schedules');
1399 fnd_message.set_token('VALUE',
1400 NVL(TO_CHAR(P_SCHEDULE_ID), 'null'));
1401 fnd_msg_pub.add;
1402 RAISE fnd_api.g_exc_error;
1403 END IF;
1404
1405
1406 IF NOT ((x_object_version_number IS NULL AND
1407 l_rec.OBJECT_VERSION_NUMBER IS NULL) OR
1408 (x_object_version_number IS NOT NULL AND
1409 l_rec.OBJECT_VERSION_NUMBER IS NOT NULL AND
1410 x_object_version_number = l_rec.OBJECT_VERSION_NUMBER))
1411 THEN
1412 fnd_message.set_name('AR', 'HZ_API_RECORD_CHANGED');
1413 fnd_message.set_token('TABLE', 'ar_charge_schedules');
1414 fnd_msg_pub.add;
1415 RAISE fnd_api.g_exc_error;
1416 END IF;
1417
1418
1419 validate_schedule
1420 (P_SCHEDULE_NAME => l_rec.SCHEDULE_NAME,
1421 p_schedule_description => P_SCHEDULE_DESCRIPTION,
1422 p_mode => 'UPDATE',
1423 x_return_status => x_return_status);
1424
1425 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1426 RAISE fnd_api.G_EXC_ERROR;
1427 END IF;
1428
1429 Update_schedule_row
1430 (P_SCHEDULE_ID => P_SCHEDULE_ID,
1431 P_SCHEDULE_DESCRIPTION => P_SCHEDULE_DESCRIPTION,
1432 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
1433 P_ATTRIBUTE1 => P_ATTRIBUTE1,
1434 P_ATTRIBUTE2 => P_ATTRIBUTE2,
1435 P_ATTRIBUTE3 => p_attribute3,
1436 P_ATTRIBUTE4 => p_attribute4,
1437 P_ATTRIBUTE5 => p_attribute5,
1438 P_ATTRIBUTE6 => p_attribute6,
1439 P_ATTRIBUTE7 => p_attribute7,
1440 P_ATTRIBUTE8 => p_attribute8,
1441 P_ATTRIBUTE9 => p_attribute9,
1442 P_ATTRIBUTE10 => p_attribute10,
1443 P_ATTRIBUTE11 => p_attribute11,
1444 P_ATTRIBUTE12 => p_attribute12,
1445 P_ATTRIBUTE13 => p_attribute13,
1446 P_ATTRIBUTE14 => p_attribute14,
1447 P_ATTRIBUTE15 => p_attribute15,
1448 X_OBJECT_VERSION_NUMBER=> X_OBJECT_VERSION_NUMBER,
1449 x_return_status => x_return_status);
1450
1451 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1452 RAISE fnd_api.g_exc_error;
1453 END IF;
1454
1455 arp_standard.debug('Update_schedule -');
1456
1457 EXCEPTION
1458 WHEN FND_API.G_EXC_ERROR THEN
1459 ROLLBACK TO Update_schedule;
1460 FND_MSG_PUB.Count_And_Get(
1461 p_encoded => FND_API.G_FALSE,
1462 p_count => x_msg_count,
1463 p_data => x_msg_data);
1464 arp_standard.debug('EXCEPTION Update_schedule:'||x_msg_data);
1465
1466 WHEN OTHERS THEN
1470 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1467 ROLLBACK TO Update_schedule;
1468 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1469 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1471 FND_MSG_PUB.ADD;
1472 FND_MSG_PUB.Count_And_Get(
1473 p_encoded => FND_API.G_FALSE,
1474 p_count => x_msg_count,
1475 p_data => x_msg_data);
1476 arp_standard.debug('EXCEPTION Update_schedule:'||x_msg_data);
1477 END;
1478
1479
1480
1481
1482
1483 PROCEDURE create_schedule_header
1484 (p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1485 P_SCHEDULE_ID IN NUMBER,
1486 P_SCHEDULE_HEADER_TYPE IN VARCHAR2,
1487 P_AGING_BUCKET_ID IN NUMBER,
1488 P_START_DATE IN DATE,
1489 P_END_DATE IN DATE,
1490 P_STATUS IN VARCHAR2,
1491 P_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
1492 P_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
1493 P_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
1494 P_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
1495 P_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
1496 P_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
1497 P_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
1498 P_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
1499 P_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
1500 P_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
1501 P_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
1502 P_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
1503 P_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
1504 P_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
1505 P_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
1506 P_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
1507 P_OBJECT_VERSION_NUMBER IN NUMBER DEFAULT 1,
1508 x_schedule_header_id OUT NOCOPY NUMBER,
1509 x_return_status OUT NOCOPY VARCHAR2,
1510 x_msg_count OUT NOCOPY NUMBER,
1511 x_msg_data OUT NOCOPY VARCHAR2)
1512 IS
1513 CURSOR cu_header_id IS
1514 SELECT ar_charge_schedule_hdrs_s.nextval
1515 FROM DUAL;
1516 l_header_id NUMBER;
1517 BEGIN
1518 arp_standard.debug('create_schedule_header +');
1519 arp_standard.debug(' P_SCHEDULE_HEADER_TYPE :'||P_SCHEDULE_HEADER_TYPE);
1520 arp_standard.debug(' P_AGING_BUCKET_ID :'||P_AGING_BUCKET_ID);
1521 arp_standard.debug(' p_start_date :'||p_start_date);
1522 arp_standard.debug(' p_end_date :'||p_end_date);
1523
1524 savepoint create_schedule_header;
1525
1526 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1527
1528 IF fnd_api.to_boolean(p_init_msg_list) THEN
1529 fnd_msg_pub.initialize;
1530 END IF;
1531
1532
1533 validate_schedule_header
1534 (P_SCHEDULE_HEADER_ID => l_header_id,
1535 P_SCHEDULE_ID => p_schedule_id,
1536 P_SCHEDULE_HEADER_TYPE => P_SCHEDULE_HEADER_TYPE,
1537 P_AGING_BUCKET_ID => P_AGING_BUCKET_ID,
1538 P_START_DATE => p_start_date,
1539 P_END_DATE => p_end_date,
1540 p_status => p_status,
1541 p_old_status => NULL,
1542 p_mode => 'INSERT',
1543 x_return_status => x_return_status);
1544
1545
1546 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1547 RAISE fnd_api.G_EXC_ERROR;
1548 END IF;
1549
1550 OPEN cu_header_id;
1551 FETCH cu_header_id INTO l_header_id;
1552 CLOSE cu_header_id;
1553
1554 Insert_Head_Row
1555 (P_SCHEDULE_HEADER_ID => l_header_id,
1556 P_SCHEDULE_ID => p_schedule_id,
1557 P_SCHEDULE_HEADER_TYPE => P_SCHEDULE_HEADER_TYPE,
1558 P_AGING_BUCKET_ID => P_AGING_BUCKET_ID,
1559 P_START_DATE => p_start_date,
1560 P_END_DATE => p_end_date,
1561 P_STATUS => P_STATUS,
1562 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
1563 P_ATTRIBUTE1 => P_ATTRIBUTE1,
1564 P_ATTRIBUTE2 => P_ATTRIBUTE2,
1565 P_ATTRIBUTE3 => P_ATTRIBUTE3,
1566 P_ATTRIBUTE4 => P_ATTRIBUTE4,
1567 P_ATTRIBUTE5 => P_ATTRIBUTE5,
1568 P_ATTRIBUTE6 => P_ATTRIBUTE6,
1569 P_ATTRIBUTE7 => P_ATTRIBUTE7,
1570 P_ATTRIBUTE8 => P_ATTRIBUTE8,
1571 P_ATTRIBUTE9 => P_ATTRIBUTE9,
1572 P_ATTRIBUTE10 => P_ATTRIBUTE10,
1573 P_ATTRIBUTE11 => P_ATTRIBUTE11,
1574 P_ATTRIBUTE12 => P_ATTRIBUTE12,
1575 P_ATTRIBUTE13 => P_ATTRIBUTE13,
1576 P_ATTRIBUTE14 => P_ATTRIBUTE14,
1577 P_ATTRIBUTE15 => P_ATTRIBUTE15,
1578 P_OBJECT_VERSION_NUMBER => P_OBJECT_VERSION_NUMBER,
1579 x_return_status => x_return_status);
1580
1581
1582 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1583 RAISE fnd_api.G_EXC_ERROR;
1584 END IF;
1585
1586 x_SCHEDULE_HEADER_ID := l_header_id;
1587
1588 arp_standard.debug('create_schedule_header -');
1589
1590 EXCEPTION
1591 WHEN FND_API.G_EXC_ERROR THEN
1592 ROLLBACK TO create_schedule_header;
1593 FND_MSG_PUB.Count_And_Get(
1594 p_encoded => FND_API.G_FALSE,
1595 p_count => x_msg_count,
1596 p_data => x_msg_data);
1597 arp_standard.debug('EXCEPTION create_schedule_header:'||x_msg_data);
1598
1599 WHEN OTHERS THEN
1600 ROLLBACK TO create_schedule_header;
1601 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1602 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1603 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1604 FND_MSG_PUB.ADD;
1605 FND_MSG_PUB.Count_And_Get(
1606 p_encoded => FND_API.G_FALSE,
1607 p_count => x_msg_count,
1608 p_data => x_msg_data);
1609 arp_standard.debug('EXCEPTION create_schedule_header:'||x_msg_data);
1613 PROCEDURE Update_schedule_header
1610 END;
1611
1612
1614 (p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1615 P_SCHEDULE_HEADER_ID IN NUMBER,
1616 P_END_DATE IN DATE,
1617 P_STATUS IN VARCHAR2,
1618 P_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
1619 P_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
1620 P_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
1621 P_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
1622 P_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
1623 P_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
1624 P_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
1625 P_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
1626 P_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
1627 P_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
1628 P_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
1629 P_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
1630 P_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
1631 P_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
1632 P_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
1633 P_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
1634 X_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
1635 x_return_status OUT NOCOPY VARCHAR2,
1636 x_msg_count OUT NOCOPY NUMBER,
1637 x_msg_data OUT NOCOPY VARCHAR2)
1638 IS
1639 CURSOR c IS
1640 SELECT SCHEDULE_ID ,
1641 SCHEDULE_HEADER_ID ,
1642 SCHEDULE_HEADER_TYPE,
1643 AGING_BUCKET_ID ,
1644 START_DATE ,
1645 END_DATE ,
1646 ATTRIBUTE_CATEGORY ,
1647 ATTRIBUTE1 ,
1648 ATTRIBUTE2 ,
1649 ATTRIBUTE3 ,
1650 ATTRIBUTE4 ,
1651 ATTRIBUTE5 ,
1652 ATTRIBUTE6 ,
1653 ATTRIBUTE7 ,
1654 ATTRIBUTE8 ,
1655 ATTRIBUTE9 ,
1656 ATTRIBUTE10 ,
1657 ATTRIBUTE11 ,
1658 ATTRIBUTE12 ,
1659 ATTRIBUTE13 ,
1660 ATTRIBUTE14 ,
1661 ATTRIBUTE15 ,
1662 OBJECT_VERSION_NUMBER,
1663 STATUS
1664 FROM ar_charge_schedule_hdrs
1665 WHERE schedule_header_id = P_SCHEDULE_HEADER_ID
1666 FOR UPDATE NOWAIT;
1667
1668 l_rec c%ROWTYPE;
1669 BEGIN
1670 arp_standard.debug('Update_schedule_header +');
1671 arp_standard.debug(' P_SCHEDULE_HEADER_ID :'||P_SCHEDULE_HEADER_ID);
1672 arp_standard.debug(' p_end_date :'||p_end_date);
1673 arp_standard.debug(' X_OBJECT_VERSION_NUMBER :'||X_OBJECT_VERSION_NUMBER);
1674
1675 savepoint Update_schedule_header;
1676
1677 IF fnd_api.to_boolean(p_init_msg_list) THEN
1678 fnd_msg_pub.initialize;
1679 END IF;
1680
1681 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1682
1683 OPEN c;
1684 FETCH c INTO
1685 l_rec.SCHEDULE_ID ,
1686 l_rec.SCHEDULE_HEADER_ID ,
1687 l_rec.SCHEDULE_HEADER_TYPE,
1688 l_rec.AGING_BUCKET_ID ,
1689 l_rec.START_DATE ,
1690 l_rec.END_DATE ,
1691 l_rec.ATTRIBUTE_CATEGORY ,
1692 l_rec.ATTRIBUTE1 ,
1693 l_rec.ATTRIBUTE2 ,
1694 l_rec.ATTRIBUTE3 ,
1695 l_rec.ATTRIBUTE4 ,
1696 l_rec.ATTRIBUTE5 ,
1697 l_rec.ATTRIBUTE6 ,
1698 l_rec.ATTRIBUTE7 ,
1699 l_rec.ATTRIBUTE8 ,
1700 l_rec.ATTRIBUTE9 ,
1701 l_rec.ATTRIBUTE10 ,
1702 l_rec.ATTRIBUTE11 ,
1703 l_rec.ATTRIBUTE12 ,
1704 l_rec.ATTRIBUTE13 ,
1705 l_rec.ATTRIBUTE14 ,
1706 l_rec.ATTRIBUTE15 ,
1707 l_rec.OBJECT_VERSION_NUMBER,
1708 l_rec.status;
1709 CLOSE c;
1710
1711 IF l_rec.SCHEDULE_HEADER_ID IS NULL THEN
1712 fnd_message.set_name('AR', 'HZ_API_NO_RECORD');
1713 fnd_message.set_token('RECORD', 'ar_charge_schedule_hdrs');
1714 fnd_message.set_token('VALUE',
1715 NVL(TO_CHAR(P_SCHEDULE_HEADER_ID), 'null'));
1716 fnd_msg_pub.add;
1717 RAISE fnd_api.g_exc_error;
1718 END IF;
1719
1720
1721 IF NOT ((x_object_version_number IS NULL AND
1722 l_rec.OBJECT_VERSION_NUMBER IS NULL) OR
1723 (x_object_version_number IS NOT NULL AND
1724 l_rec.OBJECT_VERSION_NUMBER IS NOT NULL AND
1725 x_object_version_number = l_rec.OBJECT_VERSION_NUMBER))
1726 THEN
1727 fnd_message.set_name('AR', 'HZ_API_RECORD_CHANGED');
1728 fnd_message.set_token('TABLE', 'ar_charge_schedule_hdrs');
1729 fnd_msg_pub.add;
1730 RAISE fnd_api.g_exc_error;
1731 END IF;
1732
1733
1734 validate_schedule_header
1735 (P_SCHEDULE_HEADER_ID => l_rec.SCHEDULE_HEADER_ID,
1736 P_SCHEDULE_ID => l_rec.SCHEDULE_ID,
1737 P_SCHEDULE_HEADER_TYPE => l_rec.SCHEDULE_HEADER_TYPE,
1738 P_AGING_BUCKET_ID => l_rec.AGING_BUCKET_ID,
1739 P_START_DATE => l_rec.START_DATE,
1740 P_END_DATE => P_END_DATE,
1741 P_STATUS => p_status,
1742 P_OLD_STATUS => l_rec.status,
1743 p_mode => 'UPDATE',
1744 x_return_status => x_return_status);
1745
1746
1747 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1748 RAISE fnd_api.g_exc_error;
1749 END IF;
1750
1751 Update_Head_Row
1752 (P_SCHEDULE_HEADER_ID => l_rec.SCHEDULE_HEADER_ID,
1753 P_END_DATE => p_end_date,
1754 P_STATUS => P_STATUS,
1758 P_ATTRIBUTE3 => p_attribute3,
1755 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
1756 P_ATTRIBUTE1 => P_ATTRIBUTE1,
1757 P_ATTRIBUTE2 => P_ATTRIBUTE2,
1759 P_ATTRIBUTE4 => p_attribute4,
1760 P_ATTRIBUTE5 => p_attribute5,
1761 P_ATTRIBUTE6 => p_attribute6,
1762 P_ATTRIBUTE7 => p_attribute7,
1763 P_ATTRIBUTE8 => p_attribute8,
1764 P_ATTRIBUTE9 => p_attribute9,
1765 P_ATTRIBUTE10 => p_attribute10,
1766 P_ATTRIBUTE11 => p_attribute11,
1767 P_ATTRIBUTE12 => p_attribute12,
1768 P_ATTRIBUTE13 => p_attribute13,
1769 P_ATTRIBUTE14 => p_attribute14,
1770 P_ATTRIBUTE15 => p_attribute15,
1771 X_OBJECT_VERSION_NUMBER=> X_OBJECT_VERSION_NUMBER,
1772 x_return_status => x_return_status);
1773
1774 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1775 RAISE fnd_api.g_exc_error;
1776 END IF;
1777
1778 arp_standard.debug('Update_schedule_header -');
1779
1780 EXCEPTION
1781 WHEN FND_API.G_EXC_ERROR THEN
1782 ROLLBACK TO Update_schedule_header;
1783 FND_MSG_PUB.Count_And_Get(
1784 p_encoded => FND_API.G_FALSE,
1785 p_count => x_msg_count,
1786 p_data => x_msg_data);
1787 arp_standard.debug('EXCEPTION Update_schedule_header:'||x_msg_data);
1788
1789 WHEN OTHERS THEN
1790 ROLLBACK TO Update_schedule_header;
1791 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1792 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1793 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1794 FND_MSG_PUB.ADD;
1795 FND_MSG_PUB.Count_And_Get(
1796 p_encoded => FND_API.G_FALSE,
1797 p_count => x_msg_count,
1798 p_data => x_msg_data);
1799 arp_standard.debug('EXCEPTION Update_schedule_header:'||x_msg_data);
1800 END;
1801
1802
1803
1804 PROCEDURE create_schedule_line
1805 (p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1806 P_SCHEDULE_HEADER_ID IN NUMBER,
1807 P_SCHEDULE_ID IN NUMBER,
1808 P_AGING_BUCKET_ID IN NUMBER,
1809 P_AGING_BUCKET_LINE_ID IN NUMBER,
1810 P_AMOUNT IN NUMBER,
1811 P_RATE IN NUMBER,
1812 P_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
1813 P_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
1814 P_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
1815 P_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
1816 P_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
1817 P_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
1818 P_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
1819 P_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
1820 P_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
1821 P_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
1822 P_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
1823 P_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
1824 P_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
1825 P_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
1826 P_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
1827 P_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
1828 P_OBJECT_VERSION_NUMBER IN NUMBER DEFAULT 1,
1829 X_SCHEDULE_LINE_ID OUT NOCOPY NUMBER,
1830 x_return_status OUT NOCOPY VARCHAR2,
1831 x_msg_count OUT NOCOPY NUMBER,
1832 x_msg_data OUT NOCOPY VARCHAR2)
1833 IS
1834 CURSOR cu_line_id IS
1835 SELECT ar_charge_schedule_lines_s.NEXTVAL
1836 FROM dual;
1837 l_line_id NUMBER;
1838 BEGIN
1839 arp_standard.debug('create_schedule_line +');
1840 arp_standard.debug(' P_SCHEDULE_HEADER_ID :'||P_SCHEDULE_HEADER_ID);
1841 arp_standard.debug(' P_SCHEDULE_ID :'||P_SCHEDULE_ID);
1842 arp_standard.debug(' P_AGING_BUCKET_ID :'||P_AGING_BUCKET_ID);
1843 arp_standard.debug(' P_AGING_BUCKET_LINE_ID :'||P_AGING_BUCKET_LINE_ID);
1844 arp_standard.debug(' P_AMOUNT :'||P_amount);
1845 arp_standard.debug(' P_RATE :'||P_RATE);
1846
1847 SAVEPOINT create_schedule_line;
1848
1849 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1850
1851 IF fnd_api.to_boolean(p_init_msg_list) THEN
1852 fnd_msg_pub.initialize;
1853 END IF;
1854
1855 validate_schedule_line
1856 (P_SCHEDULE_LINE_ID => l_line_id,
1857 P_SCHEDULE_HEADER_ID => P_SCHEDULE_HEADER_ID,
1858 P_SCHEDULE_ID => P_SCHEDULE_ID,
1859 P_AGING_BUCKET_ID => P_AGING_BUCKET_ID,
1860 P_AGING_BUCKET_LINE_ID => P_AGING_BUCKET_LINE_ID,
1861 P_AMOUNT => p_amount,
1862 P_RATE => p_rate,
1863 P_MODE => 'INSERT',
1864 x_return_status => x_return_status);
1865
1866
1867 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1868 RAISE fnd_api.G_EXC_ERROR;
1869 END IF;
1870
1871 OPEN cu_line_id;
1872 FETCH cu_line_id INTO l_line_id;
1873 CLOSE cu_line_id;
1874
1875
1876 Insert_Line_Row
1877 (P_SCHEDULE_LINE_ID => l_line_id,
1878 P_SCHEDULE_HEADER_ID => P_SCHEDULE_HEADER_ID,
1879 P_SCHEDULE_ID => P_SCHEDULE_ID,
1880 P_AGING_BUCKET_ID => P_AGING_BUCKET_ID,
1881 P_AGING_BUCKET_LINE_ID => P_AGING_BUCKET_LINE_ID,
1882 P_AMOUNT => P_AMOUNT,
1883 P_RATE => P_RATE,
1884 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
1885 P_ATTRIBUTE1 => P_ATTRIBUTE1,
1886 P_ATTRIBUTE2 => P_ATTRIBUTE2,
1887 P_ATTRIBUTE3 => P_ATTRIBUTE3,
1888 P_ATTRIBUTE4 => P_ATTRIBUTE4,
1889 P_ATTRIBUTE5 => P_ATTRIBUTE5,
1890 P_ATTRIBUTE6 => P_ATTRIBUTE6,
1891 P_ATTRIBUTE7 => P_ATTRIBUTE7,
1895 P_ATTRIBUTE11 => P_ATTRIBUTE11,
1892 P_ATTRIBUTE8 => P_ATTRIBUTE8,
1893 P_ATTRIBUTE9 => P_ATTRIBUTE9,
1894 P_ATTRIBUTE10 => P_ATTRIBUTE10,
1896 P_ATTRIBUTE12 => P_ATTRIBUTE12,
1897 P_ATTRIBUTE13 => P_ATTRIBUTE13,
1898 P_ATTRIBUTE14 => P_ATTRIBUTE14,
1899 P_ATTRIBUTE15 => P_ATTRIBUTE15,
1900 P_OBJECT_VERSION_NUMBER => P_OBJECT_VERSION_NUMBER,
1901 x_return_status => x_return_status);
1902
1903 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1904 RAISE fnd_api.g_exc_error;
1905 END IF;
1906
1907 X_SCHEDULE_LINE_ID := l_line_id;
1908
1909 arp_standard.debug('create_schedule_line -');
1910
1911 EXCEPTION
1912 WHEN FND_API.G_EXC_ERROR THEN
1913 ROLLBACK TO create_schedule_line;
1914 FND_MSG_PUB.Count_And_Get(
1915 p_encoded => FND_API.G_FALSE,
1916 p_count => x_msg_count,
1917 p_data => x_msg_data);
1918 arp_standard.debug('EXCEPTION create_schedule_line :'||x_msg_data);
1919
1920 WHEN OTHERS THEN
1921 ROLLBACK TO create_schedule_line;
1922 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1923 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1924 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1925 FND_MSG_PUB.ADD;
1926 FND_MSG_PUB.Count_And_Get(
1927 p_encoded => FND_API.G_FALSE,
1928 p_count => x_msg_count,
1929 p_data => x_msg_data);
1930 arp_standard.debug('EXCEPTION create_schedule_line :'||x_msg_data);
1931 END;
1932
1933 PROCEDURE Update_schedule_line
1934 (p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1935 P_SCHEDULE_line_ID IN NUMBER,
1936 P_amount IN NUMBER,
1937 P_rate IN NUMBER,
1938 P_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
1939 P_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
1940 P_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
1941 P_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
1942 P_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
1943 P_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
1944 P_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
1945 P_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
1946 P_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
1947 P_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
1948 P_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
1949 P_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
1950 P_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
1951 P_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
1952 P_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
1953 P_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
1954 X_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
1955 x_return_status OUT NOCOPY VARCHAR2,
1956 x_msg_count OUT NOCOPY NUMBER,
1957 x_msg_data OUT NOCOPY VARCHAR2)
1958 IS
1959 CURSOR c IS
1960 SELECT SCHEDULE_ID ,
1961 SCHEDULE_HEADER_ID ,
1962 schedule_line_id ,
1963 amount ,
1964 rate ,
1965 ATTRIBUTE_CATEGORY ,
1966 ATTRIBUTE1 ,
1967 ATTRIBUTE2 ,
1968 ATTRIBUTE3 ,
1969 ATTRIBUTE4 ,
1970 ATTRIBUTE5 ,
1971 ATTRIBUTE6 ,
1972 ATTRIBUTE7 ,
1973 ATTRIBUTE8 ,
1974 ATTRIBUTE9 ,
1975 ATTRIBUTE10 ,
1976 ATTRIBUTE11 ,
1977 ATTRIBUTE12 ,
1978 ATTRIBUTE13 ,
1979 ATTRIBUTE14 ,
1980 ATTRIBUTE15 ,
1981 OBJECT_VERSION_NUMBER
1982 FROM ar_charge_schedule_lines
1983 WHERE schedule_line_id = P_SCHEDULE_LINE_ID
1984 FOR UPDATE NOWAIT;
1985
1986 l_rec c%ROWTYPE;
1987 BEGIN
1988 arp_standard.debug('Update_schedule_line +');
1989 arp_standard.debug(' P_SCHEDULE_line_ID :'||P_SCHEDULE_line_ID);
1990 arp_standard.debug(' p_amount :'||p_amount);
1991 arp_standard.debug(' p_rate :'||p_rate);
1992 arp_standard.debug(' X_OBJECT_VERSION_NUMBER :'||X_OBJECT_VERSION_NUMBER);
1993
1994 savepoint Update_schedule_line;
1995
1996 IF fnd_api.to_boolean(p_init_msg_list) THEN
1997 fnd_msg_pub.initialize;
1998 END IF;
1999
2000 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2001
2002 OPEN c;
2003 FETCH c INTO
2004 l_rec.SCHEDULE_ID ,
2005 l_rec.SCHEDULE_HEADER_ID ,
2006 l_rec.schedule_line_id ,
2007 l_rec.amount ,
2008 l_rec.rate ,
2009 l_rec.ATTRIBUTE_CATEGORY ,
2010 l_rec.ATTRIBUTE1 ,
2011 l_rec.ATTRIBUTE2 ,
2012 l_rec.ATTRIBUTE3 ,
2013 l_rec.ATTRIBUTE4 ,
2014 l_rec.ATTRIBUTE5 ,
2015 l_rec.ATTRIBUTE6 ,
2016 l_rec.ATTRIBUTE7 ,
2017 l_rec.ATTRIBUTE8 ,
2018 l_rec.ATTRIBUTE9 ,
2019 l_rec.ATTRIBUTE10 ,
2020 l_rec.ATTRIBUTE11 ,
2021 l_rec.ATTRIBUTE12 ,
2022 l_rec.ATTRIBUTE13 ,
2023 l_rec.ATTRIBUTE14 ,
2024 l_rec.ATTRIBUTE15 ,
2025 l_rec.OBJECT_VERSION_NUMBER;
2026 CLOSE c;
2027
2028 IF l_rec.SCHEDULE_line_ID IS NULL THEN
2029 fnd_message.set_name('AR', 'HZ_API_NO_RECORD');
2030 fnd_message.set_token('RECORD', 'ar_charge_schedule_lines');
2031 fnd_message.set_token('VALUE',
2032 NVL(TO_CHAR(P_SCHEDULE_line_ID), 'null'));
2033 fnd_msg_pub.add;
2034 RAISE fnd_api.g_exc_error;
2035 END IF;
2036
2037
2041 l_rec.OBJECT_VERSION_NUMBER IS NOT NULL AND
2038 IF NOT ((x_object_version_number IS NULL AND
2039 l_rec.OBJECT_VERSION_NUMBER IS NULL) OR
2040 (x_object_version_number IS NOT NULL AND
2042 x_object_version_number = l_rec.OBJECT_VERSION_NUMBER))
2043 THEN
2044 fnd_message.set_name('AR', 'HZ_API_RECORD_CHANGED');
2045 fnd_message.set_token('TABLE', 'ar_charge_schedule_lines');
2046 fnd_msg_pub.add;
2047 RAISE fnd_api.g_exc_error;
2048 END IF;
2049
2050 /* This would be the place to call validate_line
2051 validate_schedule_line
2052 (P_SCHEDULE_LINE_ID => l_line_id,
2053 P_SCHEDULE_HEADER_ID => P_SCHEDULE_HEADER_ID,
2054 P_SCHEDULE_ID => P_SCHEDULE_ID,
2055 P_AGING_BUCKET_ID => P_AGING_BUCKET_ID,
2056 P_AGING_BUCKET_LINE_ID => P_AGING_BUCKET_LINE_ID,
2057 P_AMOUNT => p_amount,
2058 P_RATE => p_rate,
2059 P_MODE => 'UPDATE',
2060 x_return_status => x_return_status);
2061
2062 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
2063 RAISE fnd_api.g_exc_error;
2064 END IF;
2065 */
2066
2067 Update_line_Row
2068 (P_SCHEDULE_line_ID => l_rec.SCHEDULE_line_ID,
2069 P_amount => p_amount,
2070 P_rate => P_rate,
2071 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
2072 P_ATTRIBUTE1 => P_ATTRIBUTE1,
2073 P_ATTRIBUTE2 => P_ATTRIBUTE2,
2074 P_ATTRIBUTE3 => p_attribute3,
2075 P_ATTRIBUTE4 => p_attribute4,
2076 P_ATTRIBUTE5 => p_attribute5,
2077 P_ATTRIBUTE6 => p_attribute6,
2078 P_ATTRIBUTE7 => p_attribute7,
2079 P_ATTRIBUTE8 => p_attribute8,
2080 P_ATTRIBUTE9 => p_attribute9,
2081 P_ATTRIBUTE10 => p_attribute10,
2082 P_ATTRIBUTE11 => p_attribute11,
2083 P_ATTRIBUTE12 => p_attribute12,
2084 P_ATTRIBUTE13 => p_attribute13,
2085 P_ATTRIBUTE14 => p_attribute14,
2086 P_ATTRIBUTE15 => p_attribute15,
2087 X_OBJECT_VERSION_NUMBER=> X_OBJECT_VERSION_NUMBER,
2088 x_return_status => x_return_status);
2089
2090 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
2091 RAISE fnd_api.g_exc_error;
2092 END IF;
2093
2094 arp_standard.debug('Update_schedule_line -');
2095
2096 EXCEPTION
2097 WHEN FND_API.G_EXC_ERROR THEN
2098 ROLLBACK TO Update_schedule_line;
2099 FND_MSG_PUB.Count_And_Get(
2100 p_encoded => FND_API.G_FALSE,
2101 p_count => x_msg_count,
2102 p_data => x_msg_data);
2103 arp_standard.debug('EXCEPTION Update_schedule_line:'||x_msg_data);
2104
2105 WHEN OTHERS THEN
2106 ROLLBACK TO Update_schedule_header;
2107 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2108 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2109 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2110 FND_MSG_PUB.ADD;
2111 FND_MSG_PUB.Count_And_Get(
2112 p_encoded => FND_API.G_FALSE,
2113 p_count => x_msg_count,
2114 p_data => x_msg_data);
2115 arp_standard.debug('EXCEPTION Update_schedule_line:'||x_msg_data);
2116 END;
2117
2118
2119 END;