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