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