[Home] [Help]
PACKAGE BODY: APPS.FA_LEASE_PUB
Source
1 PACKAGE BODY FA_LEASE_PUB AS
2 /* $Header: FAPLEAB.pls 120.5 2009/05/04 22:56:04 glchen ship $ */
3
4 --*********************** GLOBAL CONSTANTS *******************************--
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FA_LEASE_PUB';
6 G_API_NAME CONSTANT VARCHAR2(30) := 'LEASE 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 ----------------------------------------
13 -- CREATE LEASE PUBLIC API
14 ----------------------------------------
15 PROCEDURE CREATE_LEASE (
16 P_API_VERSION IN NUMBER,
17 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
18 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
19 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
20 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
21 X_MSG_COUNT OUT NOCOPY NUMBER,
22 X_MSG_DATA OUT NOCOPY VARCHAR2,
23 P_CALLING_FN IN VARCHAR2,
24 P_TRANS_REC IN FA_API_TYPES.TRANS_REC_TYPE,
25 PX_LEASE_DETAILS_REC IN OUT NOCOPY FA_API_TYPES.LEASE_DETAILS_REC_TYPE) IS
26
27 VALUE_ERROR_EXCEPTION EXCEPTION;
28 L_ROWID VARCHAR2(100):=NULL;
29 L_LEASE_ID NUMBER:=NULL;
30 L_PRESENT_VALUE NUMBER:=NULL;
31 L_ECONOMIC_TEST VARCHAR2(1):=NULL;
32 L_PRESENT_TEST VARCHAR2(1):=NULL;
33 L_FASB_LEASE_TYPE VARCHAR2(15):=NULL;
34 L_COST_CAPITALIZED NUMBER:=NULL;
35 L_API_NAME CONSTANT VARCHAR2(30) := 'CREATE_LEASE';
36 L_API_VERSION CONSTANT NUMBER := 1.0;
37 L_LEASE_DETAILS_REC FA_API_TYPES.LEASE_DETAILS_REC_TYPE;
38
39 CURSOR C_PRESENT_VALUE IS
40 SELECT PRESENT_VALUE
41 FROM FA_LEASE_SCHEDULES
42 WHERE PAYMENT_SCHEDULE_ID= L_LEASE_DETAILS_REC.PAYMENT_SCHEDULE_ID;
43
44 PRESENT_VALUE_NOT_FOUND EXCEPTION;
45
46 BEGIN
47
48 SAVEPOINT CREATE_LEASE;
49
50 if (not g_log_level_rec.initialized) then
51 if (NOT fa_util_pub.get_log_level_rec (
52 x_log_level_rec => g_log_level_rec
53 )) then
54 raise VALUE_ERROR_EXCEPTION;
55 end if;
56 end if;
57
58 ---------------------------------------------------
59 -- STANDARD CALL TO CHECK FOR CALL COMPATIBILITY.
60 ---------------------------------------------------
61
62 IF NOT FND_API.COMPATIBLE_API_CALL(L_API_VERSION, P_API_VERSION,
63 L_API_NAME, G_PKG_NAME)
64 THEN
65 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
66 END IF;
67
68 --------------------------------------------------------------
69 -- INITIALIZE MESSAGE LIST IF P_INIT_MSG_LIST IS SET TO TRUE.
70 --------------------------------------------------------------
71
72 IF FND_API.TO_BOOLEAN(P_INIT_MSG_LIST) THEN
73 FA_SRVR_MSG.INIT_SERVER_MESSAGE;
74 FA_DEBUG_PKG.INITIALIZE;
75 END IF;
76
77 L_LEASE_DETAILS_REC:= PX_LEASE_DETAILS_REC;
78
79 ------------------------------------------
80 -- VALIDATE VALUES WHICH ARE SUPPLIED
81 ------------------------------------------
82
83 IF NOT FA_LEASE_PVT.VALIDATION_CREATE_LEASE(L_LEASE_DETAILS_REC, p_log_level_rec => g_log_level_rec) THEN
84 RAISE VALUE_ERROR_EXCEPTION;
85 END IF;
86
87 ------------------------
88 -- GET LEASE ID
89 ------------------------
90
91 SELECT FA_LEASES_S.NEXTVAL
92 INTO L_LEASE_ID
93 FROM DUAL;
94
95 IF (g_log_level_rec.statement_level) THEN
96 FA_DEBUG_PKG.ADD('AFTER LEASE ID','LEASE_ID',
97 L_LEASE_ID, p_log_level_rec => g_log_level_rec);
98 END IF;
99
100 --------------------------
101 -- GET PRESENT VALUE
102 --------------------------
103
104 OPEN C_PRESENT_VALUE;
105 FETCH C_PRESENT_VALUE
106 INTO L_PRESENT_VALUE;
107 IF C_PRESENT_VALUE%NOTFOUND THEN
108 RAISE PRESENT_VALUE_NOT_FOUND;
109 END IF;
110 CLOSE C_PRESENT_VALUE;
111
112 IF (g_log_level_rec.statement_level) THEN
113 FA_DEBUG_PKG.ADD('AFTER PRESENT VALUE','PRESENT_VALUE',L_PRESENT_VALUE, p_log_level_rec => g_log_level_rec);
114 END IF;
115
116 ----------------------------------
117 -- DO ECONOMIC VALUE TEST
118 ----------------------------------
119
120 IF (L_LEASE_DETAILS_REC.LEASE_TERM IS NOT NULL) AND
121 (L_LEASE_DETAILS_REC.ASSET_LIFE IS NOT NULL) THEN
122 IF (L_LEASE_DETAILS_REC.LEASE_TERM >= L_LEASE_DETAILS_REC.ASSET_LIFE * 0.75) THEN
123 L_ECONOMIC_TEST := 'Y';
124 ELSE
125 L_ECONOMIC_TEST := 'N';
126 END IF;
127 ELSE
128 L_ECONOMIC_TEST := 'N';
129 END IF;
130
131 IF (g_log_level_rec.statement_level) THEN
132 FA_DEBUG_PKG.ADD('AFTER ECONOMIC TEST','ECONOMIC TEST',L_ECONOMIC_TEST, p_log_level_rec => g_log_level_rec);
133 END IF;
134
135 --------------------------------
136 -- DO PRESENT VALUE TEST
137 ---------------------------------
138
139 IF (L_LEASE_DETAILS_REC.FAIR_VALUE IS NOT NULL) AND
140 (L_PRESENT_VALUE IS NOT NULL) THEN
141 IF (L_PRESENT_VALUE >= L_LEASE_DETAILS_REC.FAIR_VALUE * 0.90) THEN
142 L_PRESENT_TEST := 'Y';
143 ELSE
144 L_PRESENT_TEST := 'N';
145 END IF;
146 ELSE
147 L_PRESENT_TEST := 'N';
148 END IF;
149
150 IF (g_log_level_rec.statement_level) THEN
151 FA_DEBUG_PKG.ADD('AFTER PRESENT VALUE TEST','PRESENT VALUE TEST',L_PRESENT_TEST , p_log_level_rec => g_log_level_rec);
152 END IF;
153
154 ----------------------------------------------------------------------
155 -- BASED ON ECONOMIC TEST, PRESENT VALUE TEST DETERMINE IF
156 --LEASE TYPE AND COST OF CAPITALIZATION FASB-13
157 ----------------------------------------------------------------------
158
159 IF (L_LEASE_DETAILS_REC.TRANSFER_OWNERSHIP = 'Y') OR
160 (L_LEASE_DETAILS_REC.BARGAIN_PURCHASE_OPTION = 'Y') OR
161 (L_PRESENT_TEST = 'Y') OR
162 (L_ECONOMIC_TEST = 'Y')
163 THEN
164 L_FASB_LEASE_TYPE := 'CAPITALIZED';
165 L_LEASE_DETAILS_REC.LEASE_TYPE := 'C';
166 IF (L_PRESENT_VALUE IS NOT NULL AND
167 L_LEASE_DETAILS_REC.FAIR_VALUE IS NOT NULL) THEN
168 IF (L_PRESENT_VALUE < L_LEASE_DETAILS_REC.FAIR_VALUE) THEN
169 L_COST_CAPITALIZED := L_PRESENT_VALUE;
170 ELSE
171 L_COST_CAPITALIZED := L_LEASE_DETAILS_REC.FAIR_VALUE;
172 END IF;
173 END IF;
174 ELSE
175 L_FASB_LEASE_TYPE := 'OPERATING';
176 L_LEASE_DETAILS_REC.LEASE_TYPE := 'O';
177 L_COST_CAPITALIZED := 0;
178 END IF;
179
180 ----------------------------------------------------------------
181 -- EVERYTING LOOKS GOOD, LET'S INSERT THIS ROW
182 ----------------------------------------------------------------
183
184 FA_LEASES2_PKG.INSERT_ROW( X_ROWID=>L_ROWID,
185 X_LEASE_ID=>L_LEASE_ID,
186 X_LEASE_NUMBER=>L_LEASE_DETAILS_REC.LEASE_NUMBER,
187 X_LESSOR_ID=>L_LEASE_DETAILS_REC.LESSOR_ID,
188 X_DESCRIPTION=>L_LEASE_DETAILS_REC.DESCRIPTION,
189 X_LAST_UPDATE_DATE=>NVL(P_TRANS_REC.WHO_INFO.LAST_UPDATE_DATE,SYSDATE),
190 X_LAST_UPDATED_BY=>NVL(P_TRANS_REC.WHO_INFO.LAST_UPDATED_BY,-1),
191 X_CREATED_BY=>NVL(P_TRANS_REC.WHO_INFO.CREATED_BY,-1),
192 X_CREATION_DATE=>NVL(P_TRANS_REC.WHO_INFO.CREATION_DATE,SYSDATE),
193 X_LAST_UPDATE_LOGIN=>NVL(P_TRANS_REC.WHO_INFO.LAST_UPDATE_LOGIN,-1),
194 X_ATTRIBUTE1=>L_LEASE_DETAILS_REC.DESC_FLEX.ATTRIBUTE1,
195 X_ATTRIBUTE2=>L_LEASE_DETAILS_REC.DESC_FLEX.ATTRIBUTE2,
196 X_ATTRIBUTE3=>L_LEASE_DETAILS_REC.DESC_FLEX.ATTRIBUTE3,
197 X_ATTRIBUTE4=>L_LEASE_DETAILS_REC.DESC_FLEX.ATTRIBUTE4,
198 X_ATTRIBUTE5=>L_LEASE_DETAILS_REC.DESC_FLEX.ATTRIBUTE5,
199 X_ATTRIBUTE6=>L_LEASE_DETAILS_REC.DESC_FLEX.ATTRIBUTE6,
200 X_ATTRIBUTE7=>L_LEASE_DETAILS_REC.DESC_FLEX.ATTRIBUTE7,
201 X_ATTRIBUTE8=>L_LEASE_DETAILS_REC.DESC_FLEX.ATTRIBUTE8,
202 X_ATTRIBUTE9=>L_LEASE_DETAILS_REC.DESC_FLEX.ATTRIBUTE9,
203 X_ATTRIBUTE10=>L_LEASE_DETAILS_REC.DESC_FLEX.ATTRIBUTE10,
204 X_ATTRIBUTE11=>L_LEASE_DETAILS_REC.DESC_FLEX.ATTRIBUTE11,
205 X_ATTRIBUTE12=>L_LEASE_DETAILS_REC.DESC_FLEX.ATTRIBUTE12,
206 X_ATTRIBUTE13=>L_LEASE_DETAILS_REC.DESC_FLEX.ATTRIBUTE13,
207 X_ATTRIBUTE14=>L_LEASE_DETAILS_REC.DESC_FLEX.ATTRIBUTE14,
208 X_ATTRIBUTE15=>L_LEASE_DETAILS_REC.DESC_FLEX.ATTRIBUTE15,
209 X_ATTRIBUTE_CATEGORY_CODE=>L_LEASE_DETAILS_REC.DESC_FLEX.ATTRIBUTE_CATEGORY_CODE,
210 X_FASB_LEASE_TYPE=>UPPER(L_FASB_LEASE_TYPE),
211 X_COST_CAPITALIZED=>L_COST_CAPITALIZED ,
212 X_TRANSFER_OWNERSHIP=>L_LEASE_DETAILS_REC.TRANSFER_OWNERSHIP,
213 X_BARGAIN_PURCHASE_OPTION=>L_LEASE_DETAILS_REC.BARGAIN_PURCHASE_OPTION,
214 X_PAYMENT_SCHEDULE_ID=>L_LEASE_DETAILS_REC.PAYMENT_SCHEDULE_ID,
215 X_FAIR_VALUE=>L_LEASE_DETAILS_REC.FAIR_VALUE,
216 X_PRESENT_VALUE=>L_PRESENT_VALUE,
217 X_LEASE_TYPE=>L_LEASE_DETAILS_REC.LEASE_TYPE,
218 X_LEASE_TERM=>L_LEASE_DETAILS_REC.LEASE_TERM,
219 X_ASSET_LIFE=>L_LEASE_DETAILS_REC.ASSET_LIFE,
220 X_CURRENCY_CODE=>L_LEASE_DETAILS_REC.CURRENCY_CODE,
221 X_LESSOR_SITE_ID=>L_LEASE_DETAILS_REC.LESSOR_SITE_ID,
222 X_DIST_CODE_COMBINATION_ID=>L_LEASE_DETAILS_REC.DIST_CODE_COMBINATION_ID,
223 X_TERMS_ID=>L_LEASE_DETAILS_REC.TERMS_ID,
224 X_CALLING_FN=>P_CALLING_FN,
225 p_log_level_rec => g_log_level_rec
226 ) ;
227
228 PX_LEASE_DETAILS_REC.LEASE_ID:=L_LEASE_ID;
229
230 ----------------------------------------------------------
231 -- CHECK FOR COMMIT
232 ----------------------------------------------------------
233 IF FND_API.TO_BOOLEAN( P_COMMIT ) THEN
234 COMMIT WORK;
235 END IF;
236
237 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
238
239 EXCEPTION
240 WHEN PRESENT_VALUE_NOT_FOUND THEN
241 ROLLBACK TO CREATE_LEASE;
242 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
243 FND_MESSAGE.SET_NAME ('OFA','FA_PRESENT_VALUE_NOT_FOUND');
244 FND_MESSAGE.SET_TOKEN ('CALLING_FN','CREATE_LEASE');
245 FND_MSG_PUB.ADD;
246 WHEN VALUE_ERROR_EXCEPTION THEN
247 ROLLBACK TO CREATE_LEASE;
248 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
249 WHEN OTHERS THEN
250 ROLLBACK TO CREATE_LEASE;
251 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
252 X_MSG_DATA:=SQLERRM;
253 END CREATE_LEASE;
254
255 --------------------------------------
256 -- UPDATE LEASE API
257 --------------------------------------
258 PROCEDURE UPDATE_LEASE (
259 P_API_VERSION IN NUMBER,
260 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
261 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
262 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
263 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
264 X_MSG_COUNT OUT NOCOPY NUMBER,
265 X_MSG_DATA OUT NOCOPY VARCHAR2,
266 P_CALLING_FN IN VARCHAR2,
267 P_TRANS_REC IN FA_API_TYPES.TRANS_REC_TYPE,
268 P_LEASE_DETAILS_REC_NEW IN FA_API_TYPES.LEASE_DETAILS_REC_TYPE) IS
269
270 VALUE_ERROR_EXCEPTION EXCEPTION;
271 L_UPDATE_FLAG VARCHAR2(1):= NULL;
272 L_OK_TO_UPDATE_LESSOR VARCHAR2(1):= NULL;
273 L_ATLEAST_ONE_UPDATE NUMBER := 0;
274 L_API_NAME CONSTANT VARCHAR2(30) := 'UPDATE_LEASE';
275 L_API_VERSION CONSTANT NUMBER := 1.0;
276 L_LEASE_DETAILS_REC_NEW FA_API_TYPES.LEASE_DETAILS_REC_TYPE;
277
278 BEGIN
279
280 SAVEPOINT UPDATE_LEASE;
281
282 if (not g_log_level_rec.initialized) then
283 if (NOT fa_util_pub.get_log_level_rec (
284 x_log_level_rec => g_log_level_rec
285 )) then
286 raise VALUE_ERROR_EXCEPTION;
287 end if;
288 end if;
289
290 ------------------------------------------------------
291 -- STANDARD CALL TO CHECK FOR CALL COMPATIBILITY.
292 ------------------------------------------------------
293
294 IF NOT FND_API.COMPATIBLE_API_CALL(L_API_VERSION, P_API_VERSION,
295 L_API_NAME, G_PKG_NAME)
296 THEN
297 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
298 END IF;
299
300 ----------------------------------------------------------------
301 -- INITIALIZE MESSAGE LIST IF P_INIT_MSG_LIST IS SET TO TRUE.
302 ----------------------------------------------------------------
303
304 IF FND_API.TO_BOOLEAN(P_INIT_MSG_LIST) THEN
305 FA_SRVR_MSG.INIT_SERVER_MESSAGE;
306 FA_DEBUG_PKG.INITIALIZE;
307 END IF;
308
309 L_LEASE_DETAILS_REC_NEW:=P_LEASE_DETAILS_REC_NEW;
310
311 ---------------------------------
312 -- VALIDATE SUPPLIED VALUES.
313 ---------------------------------
314 IF NOT FA_LEASE_PVT.VALIDATION_UPDATE_LEASE(L_LEASE_DETAILS_REC_NEW,L_OK_TO_UPDATE_LESSOR, p_log_level_rec => g_log_level_rec) THEN
315 RAISE VALUE_ERROR_EXCEPTION;
316 END IF;
317
318 ----------------------------------------------------
319 -- CHECK FOR G_MISS_CHAR
320 ----------------------------------------------------
321 IF L_LEASE_DETAILS_REC_NEW.DESCRIPTION = FND_API.G_MISS_CHAR THEN
322 FND_MESSAGE.SET_NAME ('OFA','FA_LEASE_DESCRIPTION_NULL');
323 FND_MESSAGE.SET_TOKEN ('CALLING_FN','UPDATE_LEASE');
324 FND_MSG_PUB.ADD;
325 RAISE VALUE_ERROR_EXCEPTION;
326 END IF;
327
328 IF L_OK_TO_UPDATE_LESSOR = 'Y'
329 AND L_LEASE_DETAILS_REC_NEW.LESSOR_ID IS NOT NULL
330 AND L_LEASE_DETAILS_REC_NEW.LESSOR_SITE_ID IS NOT NULL THEN
331 NULL;
332 ELSE
333 L_LEASE_DETAILS_REC_NEW.LESSOR_ID:=NULL;
334 L_LEASE_DETAILS_REC_NEW.LESSOR_SITE_ID:=NULL;
335 END IF;
336
337 -----------------------------------------
338 -- CALL UPDATE STATEMENT
339 -----------------------------------------
340 FA_LEASE_PVT.UPDATE_ROW
341 (X_ROWID =>NULL,
342 X_LEASE_ID =>L_LEASE_DETAILS_REC_NEW.LEASE_ID,
343 X_LESSOR_ID =>L_LEASE_DETAILS_REC_NEW.LESSOR_ID ,
344 X_LESSOR_SITE_ID =>L_LEASE_DETAILS_REC_NEW.LESSOR_SITE_ID ,
345 X_DESCRIPTION =>L_LEASE_DETAILS_REC_NEW.DESCRIPTION ,
346 X_LAST_UPDATE_DATE =>P_TRANS_REC.WHO_INFO.LAST_UPDATE_DATE ,
347 X_LAST_UPDATED_BY =>P_TRANS_REC.WHO_INFO.LAST_UPDATED_BY ,
348 X_ATTRIBUTE1 =>L_LEASE_DETAILS_REC_NEW.DESC_FLEX.ATTRIBUTE1 ,
349 X_ATTRIBUTE2 =>L_LEASE_DETAILS_REC_NEW.DESC_FLEX.ATTRIBUTE2 ,
350 X_ATTRIBUTE3 =>L_LEASE_DETAILS_REC_NEW.DESC_FLEX.ATTRIBUTE3 ,
351 X_ATTRIBUTE4 =>L_LEASE_DETAILS_REC_NEW.DESC_FLEX.ATTRIBUTE4 ,
352 X_ATTRIBUTE5 =>L_LEASE_DETAILS_REC_NEW.DESC_FLEX.ATTRIBUTE5 ,
353 X_ATTRIBUTE6 =>L_LEASE_DETAILS_REC_NEW.DESC_FLEX.ATTRIBUTE6 ,
354 X_ATTRIBUTE7 =>L_LEASE_DETAILS_REC_NEW.DESC_FLEX.ATTRIBUTE7 ,
355 X_ATTRIBUTE8 =>L_LEASE_DETAILS_REC_NEW.DESC_FLEX.ATTRIBUTE8 ,
356 X_ATTRIBUTE9 =>L_LEASE_DETAILS_REC_NEW.DESC_FLEX.ATTRIBUTE9 ,
357 X_ATTRIBUTE10 =>L_LEASE_DETAILS_REC_NEW.DESC_FLEX.ATTRIBUTE10 ,
358 X_ATTRIBUTE11 =>L_LEASE_DETAILS_REC_NEW.DESC_FLEX.ATTRIBUTE11 ,
359 X_ATTRIBUTE12 =>L_LEASE_DETAILS_REC_NEW.DESC_FLEX.ATTRIBUTE12 ,
360 X_ATTRIBUTE13 =>L_LEASE_DETAILS_REC_NEW.DESC_FLEX.ATTRIBUTE13 ,
361 X_ATTRIBUTE14 =>L_LEASE_DETAILS_REC_NEW.DESC_FLEX.ATTRIBUTE14 ,
362 X_ATTRIBUTE15 =>L_LEASE_DETAILS_REC_NEW.DESC_FLEX.ATTRIBUTE15 ,
363 X_ATTRIBUTE_CATEGORY_CODE=>L_LEASE_DETAILS_REC_NEW.DESC_FLEX.ATTRIBUTE_CATEGORY_CODE, p_log_level_rec => g_log_level_rec);
364
365 IF (g_log_level_rec.statement_level) THEN
366 FA_DEBUG_PKG.ADD('AFTER UPDATE','LEASE_ID',L_LEASE_DETAILS_REC_NEW.LEASE_ID, p_log_level_rec => g_log_level_rec);
367 END IF;
368
369 -------------------------------------------------
370 -- COMMIT
371 -------------------------------------------------
372
373 IF FND_API.TO_BOOLEAN( P_COMMIT ) THEN
374 COMMIT WORK;
375 END IF;
376
377 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
378
379 EXCEPTION
380 WHEN VALUE_ERROR_EXCEPTION THEN
381 ROLLBACK TO UPDATE_LEASE;
382 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
383 WHEN OTHERS THEN
384 ROLLBACK TO UPDATE_LEASE;
385 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
386 X_MSG_DATA:=SQLERRM;
387 END UPDATE_LEASE;
388
389 END FA_LEASE_PUB;