[Home] [Help]
PACKAGE BODY: APPS.JL_CO_FA_ASSET_APPRS_PKG
Source
1 PACKAGE BODY JL_CO_FA_ASSET_APPRS_PKG as
2 /* $Header: jlcoftab.pls 115.1 2002/11/13 23:30:45 vsidhart ship $ */
3
4 PROCEDURE Insert_Row(X_rowid IN OUT NOCOPY VARCHAR2,
5 X_appraisal_id NUMBER,
6 X_asset_number VARCHAR2,
7 X_appraisal_value NUMBER,
8 X_status VARCHAR2,
9 X_LAST_UPDATE_DATE DATE,
10 X_LAST_UPDATED_BY NUMBER,
11 X_CREATION_DATE DATE,
12 X_CREATED_BY NUMBER,
13 X_LAST_UPDATE_LOGIN NUMBER,
14 X_ATTRIBUTE_CATEGORY VARCHAR2,
15 X_ATTRIBUTE1 VARCHAR2,
16 X_ATTRIBUTE2 VARCHAR2,
17 X_ATTRIBUTE3 VARCHAR2,
18 X_ATTRIBUTE4 VARCHAR2,
19 X_ATTRIBUTE5 VARCHAR2,
20 X_ATTRIBUTE6 VARCHAR2,
21 X_ATTRIBUTE7 VARCHAR2,
22 X_ATTRIBUTE8 VARCHAR2,
23 X_ATTRIBUTE9 VARCHAR2,
24 X_ATTRIBUTE10 VARCHAR2,
25 X_ATTRIBUTE11 VARCHAR2,
26 X_ATTRIBUTE12 VARCHAR2,
27 X_ATTRIBUTE13 VARCHAR2,
28 X_ATTRIBUTE14 VARCHAR2,
29 X_ATTRIBUTE15 VARCHAR2,
30 X_calling_sequence IN VARCHAR2
31 ) IS
32 CURSOR C IS SELECT rowid
33 FROM jl_co_fa_asset_apprs
34 WHERE appraisal_id = X_appraisal_id;
35
36 current_calling_sequence VARCHAR2(2000);
37 debug_info VARCHAR2(100);
38
39 BEGIN
40 -- Update the calling sequence
41 --
42 current_calling_sequence := 'JL_CO_FA_ASSET_APPRS_PKG.INSERT_ROW<-' ||
43 X_calling_sequence;
44 --
45 debug_info := 'Insert into JL_CO_FA_ASSET_APPRS';
46 insert into jl_co_fa_asset_apprs(
47 appraisal_id,
48 asset_number,
49 appraisal_value,
50 status,
51 LAST_UPDATE_DATE,
52 LAST_UPDATED_BY,
53 CREATION_DATE,
54 CREATED_BY,
55 LAST_UPDATE_LOGIN,
56 ATTRIBUTE_CATEGORY,
57 ATTRIBUTE1,
58 ATTRIBUTE2,
59 ATTRIBUTE3,
60 ATTRIBUTE4,
61 ATTRIBUTE5,
62 ATTRIBUTE6,
63 ATTRIBUTE7,
64 ATTRIBUTE8,
65 ATTRIBUTE9,
66 ATTRIBUTE10,
67 ATTRIBUTE11,
68 ATTRIBUTE12,
69 ATTRIBUTE13,
70 ATTRIBUTE14,
71 ATTRIBUTE15)
72 VALUES (
73 X_appraisal_id,
74 X_asset_number,
75 X_appraisal_value,
76 X_status,
77 X_LAST_UPDATE_DATE,
78 X_LAST_UPDATED_BY,
79 X_CREATION_DATE,
80 X_CREATED_BY,
81 X_LAST_UPDATE_LOGIN,
82 X_ATTRIBUTE_CATEGORY,
83 X_ATTRIBUTE1,
84 X_ATTRIBUTE2,
85 X_ATTRIBUTE3,
86 X_ATTRIBUTE4,
87 X_ATTRIBUTE5,
88 X_ATTRIBUTE6,
89 X_ATTRIBUTE7,
90 X_ATTRIBUTE8,
91 X_ATTRIBUTE9,
92 X_ATTRIBUTE10,
93 X_ATTRIBUTE11,
94 X_ATTRIBUTE12,
95 X_ATTRIBUTE13,
96 X_ATTRIBUTE14,
97 X_ATTRIBUTE15);
98
99 debug_info := 'Open cursor C';
100 OPEN C;
101 debug_info := 'Fetch cursor C';
102 FETCH C INTO X_rowid;
103 if (C%NOTFOUND) then
104 debug_info := 'Close cursor C - DATA NOTFOUND';
105 CLOSE C;
106 Raise NO_DATA_FOUND;
107 end if;
108 debug_info := 'Close cursor C';
109 CLOSE C;
110
111 EXCEPTION
112 WHEN OTHERS THEN
113 IF (SQLCODE <> -20001) THEN
114 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
115 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
116 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
117 FND_MESSAGE.SET_TOKEN('PARAMETERS',
118 'appraisal_id = ' || X_appraisal_id);
119 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
120 END IF;
121 APP_EXCEPTION.RAISE_EXCEPTION;
122
123 END Insert_Row;
124
125 PROCEDURE Lock_Row( X_rowid VARCHAR2,
126
127 X_appraisal_id NUMBER,
128 X_asset_number VARCHAR2,
129 X_appraisal_value NUMBER,
130 X_status VARCHAR2,
131 X_LAST_UPDATE_DATE DATE,
132 X_LAST_UPDATED_BY NUMBER,
133 X_CREATION_DATE DATE,
134 X_CREATED_BY NUMBER,
135 X_LAST_UPDATE_LOGIN NUMBER,
136 X_ATTRIBUTE_CATEGORY VARCHAR2,
137 X_ATTRIBUTE1 VARCHAR2,
138 X_ATTRIBUTE2 VARCHAR2,
139 X_ATTRIBUTE3 VARCHAR2,
140 X_ATTRIBUTE4 VARCHAR2,
141 X_ATTRIBUTE5 VARCHAR2,
142 X_ATTRIBUTE6 VARCHAR2,
143 X_ATTRIBUTE7 VARCHAR2,
144 X_ATTRIBUTE8 VARCHAR2,
145 X_ATTRIBUTE9 VARCHAR2,
146 X_ATTRIBUTE10 VARCHAR2,
147 X_ATTRIBUTE11 VARCHAR2,
148 X_ATTRIBUTE12 VARCHAR2,
149 X_ATTRIBUTE13 VARCHAR2,
150 X_ATTRIBUTE14 VARCHAR2,
151 X_ATTRIBUTE15 VARCHAR2,
152 X_calling_sequence IN VARCHAR2
153 ) IS
154 CURSOR C IS SELECT *
155 FROM jl_co_fa_asset_apprs
156 WHERE appraisal_id = X_appraisal_id
157 AND asset_number = X_asset_number
158 FOR UPDATE of appraisal_id, asset_number
159 NOWAIT;
160
161 Recinfo C%ROWTYPE;
162
163 current_calling_sequence VARCHAR2(2000);
164 debug_info VARCHAR2(100);
165
166 BEGIN
167 -- Update the calling sequence
168 --
169 current_calling_sequence := 'JL_CO_FA_ASSET_APPR_PKG.LOCK_ROW<-' ||
170 X_calling_sequence;
171 debug_info := 'Open cursor C';
172 OPEN C;
173 debug_info := 'Fetch cursor C';
174 FETCH C INTO Recinfo;
175 IF (C%NOTFOUND)
176 THEN
177 debug_info := 'Close cursor C - DATA NOTFOUND';
178 CLOSE C;
179 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
180 APP_EXCEPTION.Raise_Exception;
181 END IF;
182 debug_info := 'Close cursor C';
183 CLOSE C;
184 IF (Recinfo.appraisal_id = X_appraisal_id AND
185 Recinfo.asset_number = X_asset_number AND
186 Recinfo.appraisal_value = X_appraisal_value AND
187 Recinfo.status = X_status AND
188 Recinfo.last_updated_by = X_last_updated_by AND
189 Recinfo.last_update_date = X_last_update_date AND
190 Recinfo.creation_date = X_creation_date AND
191 Recinfo.created_by = X_created_by AND
192 (Recinfo.last_update_login = X_last_update_login OR
193 X_last_update_login IS NULL) AND
194 (Recinfo.attribute1 = X_attribute1 OR
195 X_attribute1 IS NULL) AND
196 (Recinfo.attribute2 = X_attribute2 OR
197 X_attribute2 IS NULL) AND
198 (Recinfo.attribute3 = X_attribute3 OR
199 X_attribute3 IS NULL) AND
200 (Recinfo.attribute4 = X_attribute4 OR
201 X_attribute4 IS NULL) AND
202 (Recinfo.attribute5 = X_attribute5 OR
203 X_attribute5 IS NULL) AND
204 (Recinfo.attribute6 = X_attribute6 OR
205 X_attribute6 IS NULL) AND
206 (Recinfo.attribute7 = X_attribute7 OR
207 X_attribute7 IS NULL) AND
208 (Recinfo.attribute8 = X_attribute8 OR
209 X_attribute8 IS NULL) AND
210 (Recinfo.attribute9 = X_attribute9 OR
211 X_attribute9 IS NULL) AND
212 (Recinfo.attribute10 = X_attribute10 OR
213 X_attribute10 IS NULL) AND
214 (Recinfo.attribute11 = X_attribute11 OR
215 X_attribute11 IS NULL) AND
216 (Recinfo.attribute12 = X_attribute12 OR
217 X_attribute12 IS NULL) AND
218 (Recinfo.attribute13 = X_attribute13 OR
219 X_attribute13 IS NULL) AND
220 (Recinfo.attribute14 = X_attribute14 OR
221 X_attribute14 IS NULL) AND
222 (Recinfo.attribute15 = X_attribute15 OR
223 X_attribute15 IS NULL)
224 )
225 THEN
226 return;
227 ELSE
228 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
229 APP_EXCEPTION.Raise_Exception;
230 END IF;
231
232 EXCEPTION
233 WHEN OTHERS THEN
234 IF (SQLCODE <> -20001) THEN
235 IF (SQLCODE = -54) THEN
236 FND_MESSAGE.SET_NAME('SQLAP','AP_RESOURCE_BUSY');
237 ELSE
238 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
239 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
240 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
241 FND_MESSAGE.SET_TOKEN('PARAMETERS',
242 'appraisal_id = ' || X_appraisal_id);
243 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
244 END IF;
245 END IF;
246 APP_EXCEPTION.RAISE_EXCEPTION;
247 END Lock_Row;
248
249 PROCEDURE Update_Row(X_rowid VARCHAR2,
250
251 X_appraisal_id NUMBER,
252 X_asset_number VARCHAR2,
253 X_appraisal_value NUMBER,
257 X_CREATION_DATE DATE,
254 X_status VARCHAR2,
255 X_LAST_UPDATE_DATE DATE,
256 X_LAST_UPDATED_BY NUMBER,
258 X_CREATED_BY NUMBER,
259 X_LAST_UPDATE_LOGIN NUMBER,
260 X_ATTRIBUTE_CATEGORY VARCHAR2,
261 X_ATTRIBUTE1 VARCHAR2,
262 X_ATTRIBUTE2 VARCHAR2,
263 X_ATTRIBUTE3 VARCHAR2,
264 X_ATTRIBUTE4 VARCHAR2,
265 X_ATTRIBUTE5 VARCHAR2,
266 X_ATTRIBUTE6 VARCHAR2,
267 X_ATTRIBUTE7 VARCHAR2,
268 X_ATTRIBUTE8 VARCHAR2,
269 X_ATTRIBUTE9 VARCHAR2,
270 X_ATTRIBUTE10 VARCHAR2,
271 X_ATTRIBUTE11 VARCHAR2,
272 X_ATTRIBUTE12 VARCHAR2,
273 X_ATTRIBUTE13 VARCHAR2,
274 X_ATTRIBUTE14 VARCHAR2,
275 X_ATTRIBUTE15 VARCHAR2,
276
277 X_calling_sequence IN VARCHAR2
278 ) IS
279
280 BEGIN
281 UPDATE jl_co_fa_asset_apprs
282 SET appraisal_id = X_appraisal_id,
283 asset_number = X_asset_number,
284 appraisal_value = X_appraisal_value,
285 status = X_status,
286 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
287 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
288 CREATION_DATE = X_CREATION_DATE,
289 CREATED_BY = X_CREATED_BY,
290 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
291 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
292 ATTRIBUTE1 = X_ATTRIBUTE1,
293 ATTRIBUTE2 = X_ATTRIBUTE2,
294 ATTRIBUTE3 = X_ATTRIBUTE3,
295 ATTRIBUTE4 = X_ATTRIBUTE4,
296 ATTRIBUTE5 = X_ATTRIBUTE5,
297 ATTRIBUTE6 = X_ATTRIBUTE6,
298 ATTRIBUTE7 = X_ATTRIBUTE7,
299 ATTRIBUTE8 = X_ATTRIBUTE8,
300 ATTRIBUTE9 = X_ATTRIBUTE9,
301 ATTRIBUTE10 = X_ATTRIBUTE10,
302 ATTRIBUTE11 = X_ATTRIBUTE11,
303 ATTRIBUTE12 = X_ATTRIBUTE12,
304 ATTRIBUTE13 = X_ATTRIBUTE13,
305 ATTRIBUTE14 = X_ATTRIBUTE14,
306 ATTRIBUTE15 = X_ATTRIBUTE15
307 WHERE rowid = X_rowid;
308
309 IF (SQL%NOTFOUND)
310 THEN
311 raise NO_DATA_FOUND;
312 END IF;
313 END Update_Row;
314
315 PROCEDURE Delete_Row( X_rowid VARCHAR2
316 ) IS
317 BEGIN
318 DELETE
319 FROM jl_co_fa_asset_apprs
320 WHERE rowid = X_rowid;
321
322 IF (SQL%NOTFOUND)
323 THEN
324 raise NO_DATA_FOUND;
325 END IF;
326 END Delete_Row;
327
328 END JL_CO_FA_ASSET_APPRS_PKG;