[Home] [Help]
PACKAGE BODY: APPS.FA_LEASE_SCHEDULE_PUB
Source
1 PACKAGE BODY FA_LEASE_SCHEDULE_PUB AS
2 /* $Header: FAPLSCB.pls 120.5 2009/05/08 21:08:40 glchen ship $ */
3
4 --*********************** GLOBAL CONSTANTS *******************************
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FA_LEASE_SCHEDULE_PUB';
6 G_API_NAME CONSTANT VARCHAR2(30) := 'LEASE SCHEDULE API';
7 G_API_VERSION CONSTANT NUMBER := 1.0;
8
9 g_log_level_rec fa_api_types.log_level_rec_type;
10
11 --*********************** PUBLIC PROCEDURES *****************************
12 -- CREATE PAYMENTS PUBLIC API
13 PROCEDURE CREATE_PAYMENTS (
14 -- STANDARD PARAMTERS --
15 P_API_VERSION IN NUMBER,
16 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
17 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
18 P_VALIDATION_LEVEL IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
19 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
20 X_MSG_COUNT OUT NOCOPY NUMBER,
21 X_MSG_DATA OUT NOCOPY VARCHAR2,
22 P_CALLING_FN IN VARCHAR2,
23 P_TRANS_REC IN FA_API_TYPES.TRANS_REC_TYPE,
24 PX_LEASE_SCHEDULES_REC IN OUT NOCOPY FA_API_TYPES.LEASE_SCHEDULES_REC_TYPE,
25 P_LEASE_PAYMENTS_TBL IN FA_API_TYPES.LEASE_PAYMENTS_TBL_TYPE) IS
26
27 VALUE_ERROR_EXCEPTION EXCEPTION;
28 L_API_NAME CONSTANT VARCHAR2(30) := 'CREATE_PAYMENTS';
29 L_API_VERSION CONSTANT NUMBER := 1.0;
30 L_PAYMENT_LINE_NUMBER NUMBER;
31 L_PAYMENT_SCHEDULE_ID NUMBER;
32 L_END_DATE DATE;
33 L_MONTHS_PER_PERIOD NUMBER;
34
35 BEGIN
36
37 SAVEPOINT CREATE_PAYMENTS;
38
39 if (not g_log_level_rec.initialized) then
40 if (NOT fa_util_pub.get_log_level_rec (
41 x_log_level_rec => g_log_level_rec
42 )) then
43 raise VALUE_ERROR_EXCEPTION ;
44 end if;
45 end if;
46
47 ----------------------------------------------------
48 -- STANDARD CALL TO CHECK FOR CALL COMPATIBILITY.
49 ----------------------------------------------------
50 IF NOT FND_API.COMPATIBLE_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
51 THEN
52 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
53 END IF;
54
55 -------------------------------------------------------------
56 -- INITIALIZE MESSAGE LIST IF P_INIT_MSG_LIST IS SET TO TRUE.
57 -------------------------------------------------------------
58 IF FND_API.TO_BOOLEAN(P_INIT_MSG_LIST) THEN
59 FA_SRVR_MSG.INIT_SERVER_MESSAGE;
60 FA_DEBUG_PKG.INITIALIZE;
61 END IF;
62
63 ----------------------------------------------------
64 -- VALIDATE SUPPLIED INFORMATION
65 ----------------------------------------------------
66 IF NOT FA_LEASE_SCHEDULE_PVT.VALIDATE_PAYMENTS(PX_LEASE_SCHEDULES_REC,P_LEASE_PAYMENTS_TBL,L_MONTHS_PER_PERIOD, p_log_level_rec => g_log_level_rec)
67 THEN
68 RAISE VALUE_ERROR_EXCEPTION;
69 END IF;
70
71
72 ----------------------------------------------------
73 -- EVERYTHING LOOKS OK LET'S INSERT THIS ROW
74 ----------------------------------------------------
75 SELECT FA_LEASE_SCHEDULES_S.NEXTVAL
76 INTO
77 L_PAYMENT_SCHEDULE_ID
78 FROM DUAL;
79
80 IF (g_log_level_rec.statement_level)
81 THEN
82 FA_DEBUG_PKG.ADD('FA_LEASE_SCHEDULE_PUB', 'PAYMENT_SCHEDULE_ID', L_PAYMENT_SCHEDULE_ID, p_log_level_rec => g_log_level_rec);
83 END IF;
84
85 INSERT INTO FA_LEASE_SCHEDULES
86 (PAYMENT_SCHEDULE_ID
87 ,PAYMENT_SCHEDULE_NAME
88 ,PRESENT_VALUE
89 ,INTEREST_RATE
90 ,LEASE_INCEPTION_DATE
91 ,CREATED_BY
92 ,CREATION_DATE
93 ,LAST_UPDATE_LOGIN
94 ,LAST_UPDATE_DATE
95 ,LAST_UPDATED_BY
96 ,CURRENCY_CODE
97 ,FREQUENCY)
98 VALUES
99 (L_PAYMENT_SCHEDULE_ID
100 ,PX_LEASE_SCHEDULES_REC.PAYMENT_SCHEDULE_NAME
101 ,PX_LEASE_SCHEDULES_REC.PRESENT_VALUE
102 ,PX_LEASE_SCHEDULES_REC.INTEREST_RATE
103 ,PX_LEASE_SCHEDULES_REC.LEASE_INCEPTION_DATE
104 ,NVL(P_TRANS_REC.WHO_INFO.CREATED_BY,-1)
105 ,NVL(P_TRANS_REC.WHO_INFO.CREATION_DATE,SYSDATE)
106 ,NVL(P_TRANS_REC.WHO_INFO.LAST_UPDATE_LOGIN,-1)
107 ,NVL(P_TRANS_REC.WHO_INFO.LAST_UPDATE_DATE,SYSDATE)
108 ,NVL(P_TRANS_REC.WHO_INFO.LAST_UPDATED_BY,-1)
109 ,PX_LEASE_SCHEDULES_REC.CURRENCY_CODE
110 ,UPPER(PX_LEASE_SCHEDULES_REC.FREQUENCY));
111
112 L_PAYMENT_LINE_NUMBER:=1;
113 FOR TBL_INDEX IN 1..P_LEASE_PAYMENTS_TBL.COUNT LOOP
114 L_END_DATE:=NULL;
115 L_END_DATE:=FA_LEASE_SCHEDULE_PVT.CALC_END_DATE(P_LEASE_PAYMENTS_TBL(TBL_INDEX).NUMBER_OF_PAYMENTS,L_MONTHS_PER_PERIOD,P_LEASE_PAYMENTS_TBL(TBL_INDEX).START_DATE,g_log_level_rec);
116
117 INSERT INTO FA_LEASE_PAYMENTS (
118 PAYMENT_LINE_NUMBER
119 ,PAYMENT_SCHEDULE_ID
120 ,START_DATE
121 ,PAYMENT_AMOUNT
122 ,NUMBER_OF_PAYMENTS
123 ,PERIOD
124 ,END_DATE
125 ,ROW_PRESENT_VALUE
126 ,CREATED_BY
127 ,CREATION_DATE
128 ,LAST_UPDATE_LOGIN
129 ,LAST_UPDATE_DATE
130 ,LAST_UPDATED_BY )
131 VALUES
132 (L_PAYMENT_LINE_NUMBER
133 ,L_PAYMENT_SCHEDULE_ID
134 ,P_LEASE_PAYMENTS_TBL(TBL_INDEX).START_DATE
135 ,P_LEASE_PAYMENTS_TBL(TBL_INDEX).PAYMENT_AMOUNT
136 ,P_LEASE_PAYMENTS_TBL(TBL_INDEX).NUMBER_OF_PAYMENTS
137 ,P_LEASE_PAYMENTS_TBL(TBL_INDEX).PAYMENT_TYPE
138 ,L_END_DATE
139 ,NULL
140 ,NVL(P_TRANS_REC.WHO_INFO.CREATED_BY,-1)
141 ,NVL(P_TRANS_REC.WHO_INFO.CREATION_DATE,SYSDATE)
142 ,NVL(P_TRANS_REC.WHO_INFO.LAST_UPDATE_LOGIN,-1)
143 ,NVL(P_TRANS_REC.WHO_INFO.LAST_UPDATE_DATE,SYSDATE)
144 ,NVL(P_TRANS_REC.WHO_INFO.LAST_UPDATED_BY,-1)
145 );
146
147 L_PAYMENT_LINE_NUMBER:=L_PAYMENT_LINE_NUMBER+1;
148
149 END LOOP;
150
151 PX_LEASE_SCHEDULES_REC.PAYMENT_SCHEDULE_ID:=L_PAYMENT_SCHEDULE_ID;
152
153 ----------------------------------------------------
154 -- CHECK FOR COMMIT
155 ----------------------------------------------------
156
157 IF FND_API.TO_BOOLEAN( P_COMMIT ) THEN
158 COMMIT WORK;
159 END IF;
160
161 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
162
163 EXCEPTION
164 WHEN VALUE_ERROR_EXCEPTION THEN
165 ROLLBACK TO CREATE_PAYMENTS;
166 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
167 WHEN OTHERS THEN
168 ROLLBACK TO CREATE_PAYMENTS;
169 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
170 X_MSG_DATA:=SQLERRM;
171 END CREATE_PAYMENTS;
172
173 -- CREATE AMORTIZATION API
174 PROCEDURE CREATE_AMORTIZATION (
175 -- STANDARD PARAMTERS --
176 P_API_VERSION IN NUMBER,
177 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
178 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
179 P_VALIDATION_LEVEL IN NUMBER:=FND_API.G_VALID_LEVEL_FULL,
180 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
181 X_MSG_COUNT OUT NOCOPY NUMBER,
182 X_MSG_DATA OUT NOCOPY VARCHAR2,
183 P_CALLING_FN IN VARCHAR2,
184 P_TRANS_REC IN FA_API_TYPES.TRANS_REC_TYPE,
185 P_PAYMENT_SCHEDULE_ID IN NUMBER) IS
186
187 L_API_NAME CONSTANT VARCHAR2(30) := 'CREATE_AMORTIZATION';
188 L_API_VERSION CONSTANT NUMBER := 1.0;
189 VALUE_ERROR_EXCEPTION EXCEPTION;
190
191 BEGIN
192
193 SAVEPOINT CREATE_AMORTIZATION;
194
195 if (not g_log_level_rec.initialized) then
196 if (NOT fa_util_pub.get_log_level_rec (
197 x_log_level_rec => g_log_level_rec
198 )) then
199 raise VALUE_ERROR_EXCEPTION ;
200 end if;
201 end if;
202
203
204 ----------------------------------------------------
205 -- STANDARD CALL TO CHECK FOR CALL COMPATIBILITY.
206 ----------------------------------------------------
207
208 IF NOT FND_API.COMPATIBLE_API_CALL(L_API_VERSION, P_API_VERSION,L_API_NAME, G_PKG_NAME)
209 THEN
210 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
211 END IF;
212
213 -------------------------------------------------------------
214 -- INITIALIZE MESSAGE LIST IF P_INIT_MSG_LIST IS SET TO TRUE.
215 -------------------------------------------------------------
216
217 IF FND_API.TO_BOOLEAN(P_INIT_MSG_LIST) THEN
218 FA_SRVR_MSG.INIT_SERVER_MESSAGE;
219 FA_DEBUG_PKG.INITIALIZE;
220 END IF;
221
222 IF (g_log_level_rec.statement_level) THEN
223 FA_DEBUG_PKG.ADD('FA_LEASE_SCHEDULE_PUB', 'PAYMENT_SCHEDULE_ID', P_PAYMENT_SCHEDULE_ID, p_log_level_rec => g_log_level_rec);
224 END IF;
225
226
227 ----------------------------------------------------
228 -- AMORTIZE.
229 ----------------------------------------------------
230 IF NOT FA_LEASE_SCHEDULE_PVT.AMORTIZE(P_PAYMENT_SCHEDULE_ID,P_TRANS_REC, p_log_level_rec => g_log_level_rec)
231 THEN
232 RAISE VALUE_ERROR_EXCEPTION;
233 END IF;
234
235 DELETE FROM FA_AMORT_SCHEDULES WHERE PAYMENT_SCHEDULE_ID=P_PAYMENT_SCHEDULE_ID;
236
237 IF NOT FA_LEASE_SCHEDULE_PVT.AMORTIZE(P_PAYMENT_SCHEDULE_ID,P_TRANS_REC, p_log_level_rec => g_log_level_rec)
238 THEN
239 RAISE VALUE_ERROR_EXCEPTION;
240 END IF;
241
242 ----------------------------------------------------
243 -- CHECK FOR COMMIT
244 ----------------------------------------------------
245 IF FND_API.TO_BOOLEAN( P_COMMIT ) THEN
246 COMMIT WORK;
247 END IF;
248
249 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
250
251 EXCEPTION
252 WHEN VALUE_ERROR_EXCEPTION THEN
253 ROLLBACK TO CREATE_AMORTIZATION;
254 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
255 WHEN OTHERS THEN
256 ROLLBACK TO CREATE_AMORTIZATION;
257 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
258 X_MSG_DATA:=SQLERRM;
259 END CREATE_AMORTIZATION;
260
261 -- CREATE LEASE SCHEDULE API
262 PROCEDURE CREATE_LEASE_SCHEDULE (
263 -- STANDARD PARAMTERS --
264 P_API_VERSION IN NUMBER,
265 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
266 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
267 P_VALIDATION_LEVEL IN NUMBER:=FND_API.G_VALID_LEVEL_FULL,
268 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
269 X_MSG_COUNT OUT NOCOPY NUMBER,
270 X_MSG_DATA OUT NOCOPY VARCHAR2,
271 P_CALLING_FN IN VARCHAR2,
272 P_TRANS_REC IN FA_API_TYPES.TRANS_REC_TYPE,
273 PX_LEASE_SCHEDULES_REC IN OUT NOCOPY FA_API_TYPES.LEASE_SCHEDULES_REC_TYPE,
274 P_LEASE_PAYMENTS_TBL IN FA_API_TYPES.LEASE_PAYMENTS_TBL_TYPE) IS
275
276 VALUE_ERROR_EXCEPTION EXCEPTION;
277 L_API_NAME CONSTANT VARCHAR2(30) := 'CREATE_LEASE_SCHEDULE';
278 L_API_VERSION CONSTANT NUMBER := 1.0;
279 L_RETURN_STATUS VARCHAR2(1);
280 L_MESG_COUNT NUMBER := 0;
281 L_MESG_LEN NUMBER;
282 L_MESG VARCHAR2(4000);
283
284 BEGIN
285
286 SAVEPOINT CREATE_LEASE_SCHEDULE;
287
288 if (not g_log_level_rec.initialized) then
289 if (NOT fa_util_pub.get_log_level_rec (
290 x_log_level_rec => g_log_level_rec
291 )) then
292 raise VALUE_ERROR_EXCEPTION ;
293 end if;
294 end if;
295
296 ----------------------------------------------------
297 -- STANDARD CALL TO CHECK FOR CALL COMPATIBILITY.
298 ----------------------------------------------------
299 IF NOT FND_API.COMPATIBLE_API_CALL(L_API_VERSION, P_API_VERSION,
300 L_API_NAME, G_PKG_NAME)
301 THEN
302 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
303 END IF;
304 -------------------------------------------------------------
305 -- INITIALIZE MESSAGE LIST IF P_INIT_MSG_LIST IS SET TO TRUE.
306 -------------------------------------------------------------
307
308 IF FND_API.TO_BOOLEAN(P_INIT_MSG_LIST) THEN
309 FA_SRVR_MSG.INIT_SERVER_MESSAGE;
310 FA_DEBUG_PKG.INITIALIZE;
311 END IF;
312
313 ----------------------------------------------------
314 -- CREATE PAYMENTS.
315 ----------------------------------------------------
316
317 FA_LEASE_SCHEDULE_PUB.CREATE_PAYMENTS
318 (P_API_VERSION => 1.0,
319 P_INIT_MSG_LIST => FND_API.G_FALSE,
320 P_COMMIT => FND_API.G_FALSE,
321 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
322 X_RETURN_STATUS => L_RETURN_STATUS,
323 X_MSG_COUNT => L_MESG_COUNT,
324 X_MSG_DATA => L_MESG,
325 P_CALLING_FN =>'CREATE_LEASE_SCHEDULE',
326 P_TRANS_REC =>P_TRANS_REC,
327 PX_LEASE_SCHEDULES_REC =>PX_LEASE_SCHEDULES_REC,
328 P_LEASE_PAYMENTS_TBL =>P_LEASE_PAYMENTS_TBL
329 );
330
331 IF (L_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS) THEN
332 X_RETURN_STATUS:=L_RETURN_STATUS;
333 X_MSG_COUNT:=L_MESG_COUNT;
334 X_MSG_DATA:=L_MESG;
335 ELSE
336 ----------------------------------------------------
337 -- IF CREATE PAYMENTS, SUCCESS, AMORTIZE
338 ----------------------------------------------------
339 FA_LEASE_SCHEDULE_PUB.CREATE_AMORTIZATION
340 (P_API_VERSION => 1.0,
341 P_INIT_MSG_LIST => FND_API.G_FALSE,
342 P_COMMIT => FND_API.G_FALSE,
343 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
344 X_RETURN_STATUS => L_RETURN_STATUS,
345 X_MSG_COUNT => L_MESG_COUNT,
346 X_MSG_DATA => L_MESG,
347 P_CALLING_FN =>'CREATE_LEASE_SCHEDULE',
348 P_TRANS_REC =>P_TRANS_REC,
349 P_PAYMENT_SCHEDULE_ID =>PX_LEASE_SCHEDULES_REC.PAYMENT_SCHEDULE_ID
350 );
351 IF (L_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS) THEN
352 X_RETURN_STATUS:=L_RETURN_STATUS;
353 X_MSG_COUNT:=L_MESG_COUNT;
354 X_MSG_DATA:=L_MESG;
355 ELSE
356 L_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
357 END IF;
358 END IF;
359
360 ----------------------------------------------------
361 -- CHECK FOR COMMIT
362 ----------------------------------------------------
363
364 IF FND_API.TO_BOOLEAN( P_COMMIT ) THEN
365 COMMIT WORK;
366 END IF;
367
368 EXCEPTION
369 WHEN VALUE_ERROR_EXCEPTION THEN
370 ROLLBACK TO CREATE_LEASE_SCHEDULE;
371 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
372 WHEN OTHERS THEN
373 ROLLBACK TO CREATE_LEASE_SCHEDULE;
374 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
375 X_MSG_DATA:=SQLERRM;
376 END CREATE_LEASE_SCHEDULE;
377
378 END FA_LEASE_SCHEDULE_PUB;