[Home] [Help]
PACKAGE BODY: APPS.FA_ASSET_HISTORY_PKG
Source
1 PACKAGE BODY FA_ASSET_HISTORY_PKG as
2 /* $Header: faxiahb.pls 120.5 2005/07/25 09:59:17 yyoon ship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
5 X_Asset_Id NUMBER,
6 X_Category_Id NUMBER,
7 X_Asset_Type VARCHAR2,
8 X_Units NUMBER,
9 X_Date_Effective DATE,
10 X_Date_Ineffective DATE DEFAULT NULL,
11 X_Transaction_Header_Id_In NUMBER,
12 X_Transaction_Header_Id_Out NUMBER DEFAULT NULL,
13 X_Last_Update_Date DATE,
14 X_Last_Updated_By NUMBER,
15 X_Last_Update_Login NUMBER DEFAULT NULL,
16 X_Return_Status OUT NOCOPY BOOLEAN,
17 X_Calling_Fn VARCHAR2,
18 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
19 CURSOR C IS SELECT rowid FROM fa_asset_history
20 WHERE asset_id = X_asset_id
21 AND transaction_header_id_in = X_Transaction_Header_Id_In;
22
23 BEGIN
24
25
26 INSERT INTO fa_asset_history
27 (asset_id,
28 category_id,
29 asset_type,
30 units,
31 date_effective,
32 date_ineffective,
33 transaction_header_id_in,
34 transaction_header_id_out,
35 last_update_date,
36 last_updated_by,
37 last_update_login
38 ) VALUES (
39 X_Asset_Id,
40 X_Category_Id,
41 X_Asset_Type,
42 X_Units,
43 X_Date_Effective,
44 X_Date_Ineffective,
45 X_Transaction_Header_Id_In,
46 X_Transaction_Header_Id_Out,
47 X_Last_Update_Date,
48 X_Last_Updated_By,
49 X_Last_Update_Login
50 );
51
52 /* Commenting out for Security by Book
53 OPEN C;
54 FETCH C INTO X_Rowid;
55 if (C%NOTFOUND) then
56 CLOSE C;
57 Raise NO_DATA_FOUND;
58 end if;
59 CLOSE C;
60 */
61
62 exception
63 when others then
64 fa_srvr_msg.add_sql_error(
65 calling_fn => 'fa_asset_history_pkg.insert_row'
66 ,p_log_level_rec => p_log_level_rec);
67 raise;
68
69 /* Commented out for better error handling after using trx engine
70 FA_STANDARD_PKG.RAISE_ERROR(
71 CALLED_FN => 'fa_distribution_history_pkg.insert_row',
72 CALLING_FN => X_Calling_Fn
73 ,p_log_level_rec => p_log_level_rec);
74 */
75
76 END Insert_Row;
77
78
79 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
80 X_Asset_Id NUMBER,
81 X_Category_Id NUMBER,
82 X_Asset_Type VARCHAR2,
83 X_Units NUMBER,
84 X_Date_Effective DATE,
85 X_Date_Ineffective DATE DEFAULT NULL,
86 X_Transaction_Header_Id_In NUMBER,
87 X_Transaction_Header_Id_Out NUMBER DEFAULT NULL,
88 X_Calling_Fn VARCHAR2,
89 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
90 CURSOR C IS
91 SELECT *
92 FROM fa_asset_history
93 WHERE rowid = X_Rowid
94 FOR UPDATE of Transaction_Header_Id_In NOWAIT;
95 Recinfo C%ROWTYPE;
96
97
98 BEGIN
99 OPEN C;
100 FETCH C INTO Recinfo;
101 if (C%NOTFOUND) then
102 CLOSE C;
103 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
104 APP_EXCEPTION.Raise_Exception;
105 end if;
106 CLOSE C;
107 if (
108
109 (Recinfo.asset_id = X_Asset_Id)
110 AND (Recinfo.category_id = X_Category_Id)
111 AND (Recinfo.asset_type = X_Asset_Type)
112 AND (Recinfo.units = X_Units)
113 AND (Recinfo.date_effective = X_Date_Effective)
114 AND ( (Recinfo.date_ineffective = X_Date_Ineffective)
115 OR ( (Recinfo.date_ineffective IS NULL)
116 AND (X_Date_Ineffective IS NULL)))
117 AND (Recinfo.transaction_header_id_in = X_Transaction_Header_Id_In)
118 AND ( (Recinfo.transaction_header_id_out =
119 X_Transaction_Header_Id_Out)
120 OR ( (Recinfo.transaction_header_id_out IS NULL)
121 AND (X_Transaction_Header_Id_Out IS NULL)))) then
122 return;
123 else
124 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
125 APP_EXCEPTION.Raise_Exception;
126 end if;
127 END Lock_Row;
128
129
130
131 PROCEDURE Update_Row(X_Rowid VARCHAR2 DEFAULT NULL,
132 X_Asset_Id NUMBER DEFAULT NULL,
133 X_Category_Id NUMBER DEFAULT NULL,
134 X_Asset_Type VARCHAR2 DEFAULT NULL,
135 X_Units NUMBER DEFAULT NULL,
136 X_Date_Effective DATE DEFAULT NULL,
137 X_Date_Ineffective DATE DEFAULT NULL,
138 X_Transaction_Header_Id_In NUMBER DEFAULT NULL,
139 X_Transaction_Header_Id_Out NUMBER DEFAULT NULL,
140 X_Last_Update_Date DATE DEFAULT NULL,
141 X_Last_Updated_By NUMBER DEFAULT NULL,
142 X_Last_Update_Login NUMBER DEFAULT NULL,
143 X_Return_Status OUT NOCOPY BOOLEAN,
144 X_Calling_Fn VARCHAR2,
145 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
146
147 BEGIN
148
149 If x_rowid is null then
150 UPDATE fa_asset_history
151 SET asset_id = decode(X_Asset_Id,
152 NULL, asset_id,
153 FND_API.G_MISS_NUM, null,
154 X_Asset_Id),
155 category_id = decode(X_Category_Id,
156 NULL, category_id,
157 FND_API.G_MISS_NUM, null,
158 X_Category_Id),
159 asset_type = decode(X_Asset_Type,
160 NULL, asset_type,
161 FND_API.G_MISS_CHAR, null,
162 X_Asset_Type),
163 units = decode(X_Units,
164 NULL, units,
165 FND_API.G_MISS_NUM, null,
166 X_Units),
167 date_effective = decode(X_Date_Effective,
168 NULL, date_effective,
169 X_Date_Effective),
170 date_ineffective = decode(X_Date_Ineffective,
171 NULL, date_ineffective,
172 X_Date_Ineffective),
173 transaction_header_id_in = decode(X_Transaction_Header_Id_In,
174 NULL, transaction_header_id_in,
175 FND_API.G_MISS_NUM, null,
176 X_Transaction_Header_Id_In),
177 transaction_header_id_out = decode(X_Transaction_Header_Id_Out,
178 NULL, transaction_header_id_out,
179 FND_API.G_MISS_NUM, null,
180 X_Transaction_Header_Id_Out),
181 last_update_date = decode(X_Last_Update_Date,
182 NULL, last_update_date,
183 X_Last_Update_Date),
184 last_updated_by = decode(X_Last_Updated_By,
185 NULL, last_updated_by,
186 FND_API.G_MISS_NUM, null,
187 X_Last_Updated_By),
188 last_update_login = decode(X_Last_Update_Login,
189 NULL, last_update_login,
190 FND_API.G_MISS_NUM, null,
191 X_Last_Update_Login)
192 WHERE asset_id = X_asset_id and
193 date_ineffective is null;
194
195 else
196 UPDATE fa_asset_history
197 SET asset_id = decode(X_Asset_Id,
198 NULL, asset_id,
199 FND_API.G_MISS_NUM, null,
200 X_Asset_Id),
201 category_id = decode(X_Category_Id,
202 NULL, category_id,
203 FND_API.G_MISS_NUM, null,
204 X_Category_Id),
205 asset_type = decode(X_Asset_Type,
206 NULL, asset_type,
207 FND_API.G_MISS_CHAR, null,
208 X_Asset_Type),
209 units = decode(X_Units,
210 NULL, units,
211 FND_API.G_MISS_NUM, null,
212 X_Units),
213 date_effective = decode(X_Date_Effective,
214 NULL, date_effective,
215 X_Date_Effective),
216 date_ineffective = decode(X_Date_Ineffective,
217 NULL, date_ineffective,
218 X_Date_Ineffective),
219 transaction_header_id_in = decode(X_Transaction_Header_Id_In,
220 NULL, transaction_header_id_in,
221 FND_API.G_MISS_NUM, null,
222 X_Transaction_Header_Id_In),
223 transaction_header_id_out = decode(X_Transaction_Header_Id_Out,
224 NULL, transaction_header_id_out,
225 FND_API.G_MISS_NUM, null,
226 X_Transaction_Header_Id_Out),
227 last_update_date = decode(X_Last_Update_Date,
228 NULL, last_update_date,
229 X_Last_Update_Date),
230 last_updated_by = decode(X_Last_Updated_By,
231 NULL, last_updated_by,
232 FND_API.G_MISS_NUM, null,
233 X_Last_Updated_By),
234 last_update_login = decode(X_Last_Update_Login,
235 NULL, last_update_login,
236 FND_API.G_MISS_NUM, null,
237 X_Last_Update_Login)
238 WHERE rowid = X_Rowid;
239
240 end if;
241
242 if (SQL%NOTFOUND) then
243 Raise NO_DATA_FOUND;
244 end if;
245
246 exception
247 when others then
248 fa_srvr_msg.add_sql_error(
249 calling_fn => 'fa_asset_history_pkg.update_row'
250 ,p_log_level_rec => p_log_level_rec);
251 raise;
252
253 /* Commented out for better error handling after using trx engine
254 when others then
255 FA_STANDARD_PKG.RAISE_ERROR(
256 CALLED_FN => 'fa_distribution_history_pkg.update_row',
257 CALLING_FN => X_Calling_Fn
258 ,p_log_level_rec => p_log_level_rec);
259 */
260
261 END Update_Row;
262
263 PROCEDURE Delete_Row(X_Rowid VARCHAR2 DEFAULT NULL,
264 X_Asset_Id NUMBER DEFAULT NULL,
265 X_Transaction_Header_Id_In NUMBER DEFAULT NULL,
266 X_Calling_Fn VARCHAR2,
267 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
268 BEGIN
269 if X_Rowid is not null then
270 DELETE FROM fa_asset_history
271 WHERE rowid = X_Rowid;
272 elsif X_Transaction_Header_Id_In is not null then
273 DELETE FROM fa_asset_history
274 WHERE asset_id = X_asset_id
275 AND transaction_header_id_in = X_Transaction_Header_Id_In;
276 elsif X_Asset_Id is not null then
277 DELETE FROM fa_asset_history
278 WHERE asset_id = X_Asset_Id;
279 else
280 -- print error message
281 null;
282 end if;
283
284 if (SQL%NOTFOUND) then
285 Raise NO_DATA_FOUND;
286 end if;
287
288 exception
289 when others then
290 fa_srvr_msg.add_sql_error(
291 calling_fn => 'fa_asset_history_pkg.delete_row'
292 ,p_log_level_rec => p_log_level_rec);
293 raise;
294
295 /* Commented out for better error handling after using trx engine
296 when others then
297 FA_STANDARD_PKG.RAISE_ERROR(
298 CALLED_FN => 'fa_distribution_history_pkg.delete_row',
299 CALLING_FN => X_Calling_Fn
300 ,p_log_level_rec => p_log_level_rec);
301 */
302 END Delete_Row;
303
304 PROCEDURE Reactivate_Row(X_Transaction_Header_Id_Out NUMBER,
305 X_asset_id NUMBER,
306 X_Calling_Fn VARCHAR2,
307 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
308 BEGIN
309
310 UPDATE fa_asset_history
311 set Transaction_Header_Id_Out = null,
312 date_ineffective = null
313 where asset_id = X_asset_id
314 and Transaction_Header_Id_Out = X_Transaction_Header_Id_Out;
315
316 if (SQL%NOTFOUND) then
317 Raise NO_DATA_FOUND;
318 end if;
319
320 exception
321 when others then
322 FA_STANDARD_PKG.RAISE_ERROR(
323 CALLED_FN => 'fa_distribution_history_pkg.reactivate_row',
324 CALLING_FN => X_Calling_Fn
325 ,p_log_level_rec => p_log_level_rec);
326 END Reactivate_Row;
327
328 END FA_ASSET_HISTORY_PKG;