[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_ASSETS_PKG
Source
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
1 PACKAGE BODY PA_PROJECT_ASSETS_PKG as
2 /* $Header: PAXASSTB.pls 120.2 2005/08/10 14:16:47 dlanka noship $ */
3
5 X_Project_Asset_Id IN OUT NOCOPY NUMBER,
6 X_Project_Id NUMBER,
7 X_Asset_Number VARCHAR2,
8 X_Asset_Name VARCHAR2,
9 X_Asset_Description VARCHAR2,
10 X_Location_Id NUMBER,
11 X_Assigned_To_Person_Id NUMBER,
12 X_Date_Placed_In_Service DATE,
13 X_Asset_Category_Id NUMBER,
14 X_Asset_key_ccid NUMBER,
15 X_Book_Type_Code VARCHAR2,
16 X_Asset_Units NUMBER,
17 X_Depreciate_Flag VARCHAR2,
18 X_Amortize_Flag VARCHAR2,
19 X_Cost_Adjustment_Flag VARCHAR2,
20 X_Reverse_Flag VARCHAR2,
21 X_Depreciation_Expense_Ccid NUMBER,
22 X_Capitalized_Flag VARCHAR2,
23 X_Estimated_In_Service_Date DATE,
24 X_Capitalized_Cost NUMBER,
28 X_Creation_Date DATE,
25 X_Grouped_Cip_Cost NUMBER,
26 X_Last_Update_Date DATE,
27 X_Last_Updated_By NUMBER,
29 X_Created_By NUMBER,
30 X_Last_Update_Login NUMBER,
31 X_Attribute_Category VARCHAR2,
32 X_Attribute1 VARCHAR2,
33 X_Attribute2 VARCHAR2,
34 X_Attribute3 VARCHAR2,
35 X_Attribute4 VARCHAR2,
36 X_Attribute5 VARCHAR2,
37 X_Attribute6 VARCHAR2,
38 X_Attribute7 VARCHAR2,
39 X_Attribute8 VARCHAR2,
40 X_Attribute9 VARCHAR2,
41 X_Attribute10 VARCHAR2,
42 X_Attribute11 VARCHAR2,
43 X_Attribute12 VARCHAR2,
44 X_Attribute13 VARCHAR2,
45 X_Attribute14 VARCHAR2,
46 X_Attribute15 VARCHAR2,
47 --PA.L
48 X_Project_Asset_Type VARCHAR2,
49 X_Estimated_Units NUMBER,
50 X_Parent_Asset_Id NUMBER,
51 X_Estimated_Cost NUMBER,
52 X_Manufacturer_Name VARCHAR2,
53 X_Model_Number VARCHAR2,
54 X_Serial_Number VARCHAR2,
55 X_Tag_Number VARCHAR2,
56 X_Capital_Hold_Flag VARCHAR2,
57 X_Ret_Target_Asset_Id NUMBER,
58 X_Org_Id NUMBER
59 ) IS
60 CURSOR C IS SELECT rowid FROM pa_project_assets
61 WHERE project_asset_id = X_Project_Asset_Id;
62 CURSOR C2 IS SELECT pa_project_assets_s.nextval FROM sys.dual;
63 BEGIN
64 if (X_Project_Asset_Id is NULL) then
65 OPEN C2;
66 FETCH C2 INTO X_Project_Asset_Id;
67 CLOSE C2;
68 end if;
69
70 INSERT INTO pa_project_assets(
71 project_asset_id,
72 project_id,
73 asset_number,
74 asset_name,
75 asset_description,
76 location_id,
77 assigned_to_person_id,
78 date_placed_in_service,
79 asset_category_id,
80 asset_key_ccid,
81 book_type_code,
82 asset_units,
83 depreciate_flag,
84 amortize_flag,
85 cost_adjustment_flag,
86 reverse_flag,
87 depreciation_expense_ccid,
88 capitalized_flag,
89 estimated_in_service_date,
90 capitalized_cost,
91 grouped_cip_cost,
92 last_update_date,
93 last_updated_by,
94 creation_date,
95 created_by,
96 last_update_login,
97 attribute_category,
98 attribute1,
99 attribute2,
100 attribute3,
101 attribute4,
102 attribute5,
103 attribute6,
104 attribute7,
105 attribute8,
106 attribute9,
107 attribute10,
108 attribute11,
109 attribute12,
110 attribute13,
111 attribute14,
112 attribute15,
113 new_master_flag,
114 Project_Asset_Type,
115 Estimated_asset_Units ,
116 Parent_Asset_Id,
117 Estimated_Cost,
118 Manufacturer_Name,
119 Model_Number,
120 Serial_Number,
121 Tag_Number,
122 Capital_Hold_Flag,
123 Ret_Target_Asset_Id,
124 Org_Id
125 ) VALUES (
126
127 X_Project_Asset_Id,
128 X_Project_Id,
129 X_Asset_Number,
130 X_Asset_Name,
131 X_Asset_Description,
132 X_Location_Id,
133 X_Assigned_To_Person_Id,
134 X_Date_Placed_In_Service,
135 X_Asset_Category_Id,
136 X_Asset_key_ccid,
137 X_Book_Type_Code,
138 X_Asset_Units,
139 X_Depreciate_Flag,
140 X_Amortize_Flag,
141 X_Cost_Adjustment_Flag,
142 X_Reverse_Flag,
143 X_Depreciation_Expense_Ccid,
144 X_Capitalized_Flag,
145 X_Estimated_In_Service_Date,
146 X_Capitalized_Cost,
147 X_Grouped_Cip_Cost,
148 X_Last_Update_Date,
149 X_Last_Updated_By,
150 X_Creation_Date,
151 X_Created_By,
152 X_Last_Update_Login,
153 X_Attribute_Category,
154 X_Attribute1,
158 X_Attribute5,
155 X_Attribute2,
156 X_Attribute3,
157 X_Attribute4,
159 X_Attribute6,
160 X_Attribute7,
161 X_Attribute8,
162 X_Attribute9,
163 X_Attribute10,
164 X_Attribute11,
165 X_Attribute12,
166 X_Attribute13,
167 X_Attribute14,
168 X_Attribute15,
169 'N',
170 X_Project_Asset_Type,
171 X_Estimated_Units,
172 X_Parent_Asset_Id,
173 X_Estimated_Cost,
174 X_Manufacturer_Name,
175 X_Model_Number,
176 X_Serial_Number,
177 X_Tag_Number,
178 X_Capital_Hold_Flag,
179 X_Ret_Target_Asset_Id,
180 X_Org_Id
181 );
182
183 OPEN C;
184 FETCH C INTO X_Rowid;
185 if (C%NOTFOUND) then
186 CLOSE C;
187 Raise NO_DATA_FOUND;
188 end if;
189 CLOSE C;
190 END Insert_Row;
191
192
193 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
194 X_Project_Asset_Id NUMBER,
195 X_Project_Id NUMBER,
196 X_Asset_Number VARCHAR2,
197 X_Asset_Name VARCHAR2,
198 X_Asset_Description VARCHAR2,
199 X_Location_Id NUMBER,
200 X_Assigned_To_Person_Id NUMBER,
201 X_Date_Placed_In_Service DATE,
202 X_Asset_Category_Id NUMBER,
203 X_Asset_key_ccid NUMBER,
204 X_Book_Type_Code VARCHAR2,
205 X_Asset_Units NUMBER,
206 X_Depreciate_Flag VARCHAR2,
207 X_Amortize_Flag VARCHAR2,
208 X_Cost_Adjustment_Flag VARCHAR2,
209 X_Reverse_Flag VARCHAR2,
210 X_Reversal_Date DATE,
211 X_Depreciation_Expense_Ccid NUMBER,
212 X_Capitalized_Flag VARCHAR2,
213 X_Estimated_In_Service_Date DATE,
214 X_Capitalized_Cost NUMBER,
215 X_Grouped_Cip_Cost NUMBER,
216 X_Attribute_Category VARCHAR2,
217 X_Attribute1 VARCHAR2,
218 X_Attribute2 VARCHAR2,
219 X_Attribute3 VARCHAR2,
220 X_Attribute4 VARCHAR2,
221 X_Attribute5 VARCHAR2,
222 X_Attribute6 VARCHAR2,
223 X_Attribute7 VARCHAR2,
224 X_Attribute8 VARCHAR2,
225 X_Attribute9 VARCHAR2,
226 X_Attribute10 VARCHAR2,
227 X_Attribute11 VARCHAR2,
228 X_Attribute12 VARCHAR2,
229 X_Attribute13 VARCHAR2,
230 X_Attribute14 VARCHAR2,
231 X_Attribute15 VARCHAR2,
232 --PA.L
233 X_Project_Asset_Type VARCHAR2,
234 X_Estimated_Units NUMBER,
235 X_Parent_Asset_Id NUMBER,
236 X_Estimated_Cost NUMBER,
237 X_Manufacturer_Name VARCHAR2,
238 X_Model_Number VARCHAR2,
239 X_Serial_Number VARCHAR2,
240 X_Tag_Number VARCHAR2,
241 X_Capital_Event_ID NUMBER,
242 X_Capital_Hold_Flag VARCHAR2,
243 X_Ret_Target_Asset_Id NUMBER
244 ) IS
245 CURSOR C IS
246 SELECT *
247 FROM pa_project_assets
248 WHERE pa_project_assets.rowid = X_Rowid
249 FOR UPDATE of Project_Asset_Id NOWAIT;
250 Recinfo C%ROWTYPE;
251
252
253 BEGIN
254 OPEN C;
255 FETCH C INTO Recinfo;
256 if (C%NOTFOUND) then
257 CLOSE C;
258 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
259 APP_EXCEPTION.Raise_Exception;
260 end if;
261 CLOSE C;
262 if (
263 (Recinfo.project_asset_id = X_Project_Asset_Id)
264 AND (Recinfo.project_id = X_Project_Id)
265 AND ( (Recinfo.asset_number = X_Asset_Number)
266 OR ( (Recinfo.asset_number IS NULL)
267 AND (X_Asset_Number IS NULL)))
268 AND (Recinfo.asset_name = X_Asset_Name)
269 AND (Recinfo.asset_description = X_Asset_Description)
270 AND ( (Recinfo.location_id = X_Location_Id)
271 OR ( (Recinfo.location_id IS NULL)
272 AND (X_Location_Id IS NULL)))
273 AND ( (Recinfo.assigned_to_person_id = X_Assigned_To_Person_Id)
277 OR ( (Recinfo.date_placed_in_service IS NULL)
274 OR ( (Recinfo.assigned_to_person_id IS NULL)
275 AND (X_Assigned_To_Person_Id IS NULL)))
276 AND ( (Recinfo.date_placed_in_service = X_Date_Placed_In_Service)
278 AND (X_Date_Placed_In_Service IS NULL)))
279 AND ( (Recinfo.asset_category_id = X_Asset_Category_Id)
280 OR ( (Recinfo.asset_category_id IS NULL)
281 AND (X_Asset_Category_Id IS NULL)))
282 AND ( (Recinfo.asset_key_ccid = X_Asset_key_ccId)
283 OR ( (Recinfo.asset_key_ccid IS NULL)
284 AND (X_Asset_key_ccId IS NULL)))
285 AND ( (Recinfo.book_type_code = X_Book_Type_Code)
286 OR ( (Recinfo.book_type_code IS NULL)
287 AND (X_Book_Type_Code IS NULL)))
288 AND ( (Recinfo.asset_units = X_Asset_Units)
289 OR ( (Recinfo.asset_units IS NULL)
290 AND (X_Asset_Units IS NULL)))
291 AND ( (Recinfo.depreciate_flag = X_Depreciate_Flag)
292 OR ( (Recinfo.depreciate_flag IS NULL)
293 AND (X_Depreciate_Flag IS NULL)))
294 AND ( (Recinfo.amortize_flag = X_Amortize_Flag)
295 OR ( (Recinfo.amortize_flag IS NULL)
296 AND (X_Amortize_Flag IS NULL)))
297 AND ( (Recinfo.cost_adjustment_flag = X_Cost_Adjustment_Flag)
298 OR ( (Recinfo.cost_adjustment_flag IS NULL)
299 AND (X_Cost_Adjustment_Flag IS NULL)))
300 AND (Recinfo.reverse_flag = X_Reverse_Flag)
301 AND ( (Recinfo.reversal_date = X_Reversal_Date)
302 OR ( (Recinfo.reversal_date IS NULL)
303 AND (X_Reversal_Date IS NULL)))
304 AND ( (Recinfo.depreciation_expense_ccid =
305 X_Depreciation_Expense_Ccid)
306 OR ( (Recinfo.depreciation_expense_ccid IS NULL)
307 AND (X_Depreciation_Expense_Ccid IS NULL)))
308 AND (Recinfo.capitalized_flag = X_Capitalized_Flag)
309 AND ( (Recinfo.estimated_in_service_date =
310 X_Estimated_In_Service_Date)
311 OR ( (Recinfo.estimated_in_service_date IS NULL)
312 AND (X_Estimated_In_Service_Date IS NULL)))
313 AND ( (Recinfo.capitalized_cost = X_Capitalized_Cost)
314 OR ( (Recinfo.capitalized_cost IS NULL)
315 AND (X_Capitalized_Cost IS NULL)))
316 AND ( (Recinfo.grouped_cip_cost = X_Grouped_Cip_Cost)
317 OR ( (Recinfo.grouped_cip_cost IS NULL)
318 AND (X_Grouped_Cip_Cost IS NULL)))
319 AND ( (Recinfo.attribute_category = X_Attribute_Category)
320 OR ( (Recinfo.attribute_category IS NULL)
321 AND (X_Attribute_Category IS NULL)))
322 AND ( (Recinfo.attribute1 = X_Attribute1)
323 OR ( (Recinfo.attribute1 IS NULL)
324 AND (X_Attribute1 IS NULL)))
325 AND ( (Recinfo.attribute2 = X_Attribute2)
326 OR ( (Recinfo.attribute2 IS NULL)
327 AND (X_Attribute2 IS NULL)))
328 AND ( (Recinfo.attribute3 = X_Attribute3)
329 OR ( (Recinfo.attribute3 IS NULL)
330 AND (X_Attribute3 IS NULL)))
331 AND ( (Recinfo.attribute4 = X_Attribute4)
332 OR ( (Recinfo.attribute4 IS NULL)
333 AND (X_Attribute4 IS NULL)))
334 AND ( (Recinfo.attribute5 = X_Attribute5)
335 OR ( (Recinfo.attribute5 IS NULL)
336 AND (X_Attribute5 IS NULL)))
337 AND ( (Recinfo.attribute6 = X_Attribute6)
338 OR ( (Recinfo.attribute6 IS NULL)
339 AND (X_Attribute6 IS NULL)))
340 AND ( (Recinfo.attribute7 = X_Attribute7)
341 OR ( (Recinfo.attribute7 IS NULL)
342 AND (X_Attribute7 IS NULL)))
343 AND ( (Recinfo.attribute8 = X_Attribute8)
344 OR ( (Recinfo.attribute8 IS NULL)
345 AND (X_Attribute8 IS NULL)))
346 AND ( (Recinfo.attribute9 = X_Attribute9)
347 OR ( (Recinfo.attribute9 IS NULL)
348 AND (X_Attribute9 IS NULL)))
349 AND ( (Recinfo.attribute10 = X_Attribute10)
350 OR ( (Recinfo.attribute10 IS NULL)
351 AND (X_Attribute10 IS NULL)))
352 AND ( (Recinfo.attribute11 = X_Attribute11)
353 OR ( (Recinfo.attribute11 IS NULL)
354 AND (X_Attribute11 IS NULL)))
355 AND ( (Recinfo.attribute12 = X_Attribute12)
356 OR ( (Recinfo.attribute12 IS NULL)
357 AND (X_Attribute12 IS NULL)))
358 AND ( (Recinfo.attribute13 = X_Attribute13)
359 OR ( (Recinfo.attribute13 IS NULL)
360 AND (X_Attribute13 IS NULL)))
361 AND ( (Recinfo.attribute14 = X_Attribute14)
362 OR ( (Recinfo.attribute14 IS NULL)
363 AND (X_Attribute14 IS NULL)))
364 AND ( (Recinfo.attribute15 = X_Attribute15)
365 OR ( (Recinfo.attribute15 IS NULL)
366 AND (X_Attribute15 IS NULL)))
367 AND (Recinfo.project_asset_type = X_project_asset_type)
368 AND ( (Recinfo.estimated_asset_units = X_Estimated_Units)
369 OR ( (Recinfo.estimated_asset_units IS NULL)
370 AND (X_Estimated_Units IS NULL)))
371 AND ( (Recinfo.parent_asset_id = X_Parent_Asset_Id)
372 OR ( (Recinfo.parent_asset_id IS NULL)
373 AND (X_Parent_Asset_Id IS NULL)))
377 AND ( (Recinfo.manufacturer_name = X_Manufacturer_Name)
374 AND ( (Recinfo.estimated_cost = X_Estimated_Cost)
375 OR ( (Recinfo.estimated_cost IS NULL)
376 AND (X_Estimated_Cost IS NULL)))
378 OR ( (Recinfo.manufacturer_name IS NULL)
379 AND (X_Manufacturer_Name IS NULL)))
380 AND ( (Recinfo.model_number = X_Model_Number)
381 OR ( (Recinfo.model_number IS NULL)
382 AND (X_Model_Number IS NULL)))
383 AND ( (Recinfo.serial_number = X_serial_number)
384 OR ( (Recinfo.serial_number IS NULL)
385 AND (X_serial_number IS NULL)))
386 AND ( (Recinfo.tag_number = X_tag_number)
387 OR ( (Recinfo.tag_number IS NULL)
388 AND (X_tag_number IS NULL)))
389 AND ( (Recinfo.Capital_Event_ID = X_Capital_Event_ID)
390 OR ( (Recinfo.Capital_Event_ID IS NULL)
391 AND (X_Capital_Event_ID IS NULL)))
392 AND (Recinfo.capital_hold_flag = X_capital_hold_flag)
393 AND ( (Recinfo.ret_target_asset_id = X_ret_target_asset_id)
394 OR ( (Recinfo.ret_target_asset_id IS NULL)
395 AND (X_ret_target_asset_id IS NULL)))
396 ) then
397 return;
398 else
399 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
400 APP_EXCEPTION.Raise_Exception;
401 end if;
402 END Lock_Row;
403
404
405
406 PROCEDURE Update_Row(X_Rowid VARCHAR2,
407 X_Project_Asset_Id NUMBER,
408 X_Project_Id NUMBER,
409 X_Asset_Number VARCHAR2,
410 X_Asset_Name VARCHAR2,
411 X_Asset_Description VARCHAR2,
412 X_Location_Id NUMBER,
413 X_Assigned_To_Person_Id NUMBER,
414 X_Date_Placed_In_Service DATE,
415 X_Asset_Category_Id NUMBER,
416 X_Asset_key_ccid NUMBER,
417 X_Book_Type_Code VARCHAR2,
418 X_Asset_Units NUMBER,
419 X_Depreciate_Flag VARCHAR2,
420 X_Amortize_Flag VARCHAR2,
421 X_Cost_Adjustment_Flag VARCHAR2,
422 X_Reverse_Flag VARCHAR2,
423 X_Depreciation_Expense_Ccid NUMBER,
424 X_Capitalized_Flag VARCHAR2,
425 X_Estimated_In_Service_Date DATE,
426 X_Capitalized_Cost NUMBER,
427 X_Grouped_Cip_Cost NUMBER,
428 X_Last_Update_Date DATE,
429 X_Last_Updated_By NUMBER,
430 X_Last_Update_Login NUMBER,
431 X_Attribute_Category VARCHAR2,
432 X_Attribute1 VARCHAR2,
433 X_Attribute2 VARCHAR2,
434 X_Attribute3 VARCHAR2,
435 X_Attribute4 VARCHAR2,
436 X_Attribute5 VARCHAR2,
437 X_Attribute6 VARCHAR2,
438 X_Attribute7 VARCHAR2,
439 X_Attribute8 VARCHAR2,
440 X_Attribute9 VARCHAR2,
441 X_Attribute10 VARCHAR2,
442 X_Attribute11 VARCHAR2,
443 X_Attribute12 VARCHAR2,
444 X_Attribute13 VARCHAR2,
445 X_Attribute14 VARCHAR2,
446 X_Attribute15 VARCHAR2,
447 --PA.L
448 X_Project_Asset_Type VARCHAR2,
449 X_Estimated_Units NUMBER,
450 X_Parent_Asset_Id NUMBER,
451 X_Estimated_Cost NUMBER,
452 X_Manufacturer_Name VARCHAR2,
453 X_Model_Number VARCHAR2,
454 X_Serial_Number VARCHAR2,
455 X_Tag_Number VARCHAR2,
456 X_Capital_Event_Id NUMBER,
457 X_Capital_Hold_Flag VARCHAR2,
458 X_Ret_Target_Asset_Id NUMBER
459 ) IS
460 BEGIN
461 UPDATE pa_project_assets
462 SET
463 project_asset_id = X_Project_Asset_Id,
464 project_id = X_Project_Id,
465 asset_number = X_Asset_Number,
466 asset_name = X_Asset_Name,
467 asset_description = X_Asset_Description,
468 location_id = X_Location_Id,
469 assigned_to_person_id = X_Assigned_To_Person_Id,
470 date_placed_in_service = X_Date_Placed_In_Service,
471 asset_category_id = X_Asset_Category_Id,
472 asset_key_ccid = X_Asset_key_ccid,
473 book_type_code = X_Book_Type_Code,
474 asset_units = X_Asset_Units,
478 reverse_flag = X_Reverse_Flag,
475 depreciate_flag = X_Depreciate_Flag,
476 amortize_flag = X_Amortize_Flag,
477 cost_adjustment_flag = X_Cost_Adjustment_Flag,
479 depreciation_expense_ccid = X_Depreciation_Expense_Ccid,
480 capitalized_flag = X_Capitalized_Flag,
481 estimated_in_service_date = X_Estimated_In_Service_Date,
482 capitalized_cost = X_Capitalized_Cost,
483 grouped_cip_cost = X_Grouped_Cip_Cost,
484 last_update_date = X_Last_Update_Date,
485 last_updated_by = X_Last_Updated_By,
486 last_update_login = X_Last_Update_Login,
487 attribute_category = X_Attribute_Category,
488 attribute1 = X_Attribute1,
489 attribute2 = X_Attribute2,
490 attribute3 = X_Attribute3,
491 attribute4 = X_Attribute4,
492 attribute5 = X_Attribute5,
493 attribute6 = X_Attribute6,
494 attribute7 = X_Attribute7,
495 attribute8 = X_Attribute8,
496 attribute9 = X_Attribute9,
497 attribute10 = X_Attribute10,
498 attribute11 = X_Attribute11,
499 attribute12 = X_Attribute12,
500 attribute13 = X_Attribute13,
501 attribute14 = X_Attribute14,
502 attribute15 = X_Attribute15,
503 project_asset_type = X_Project_Asset_Type,
504 estimated_asset_units = X_Estimated_Units,
505 parent_asset_id = X_Parent_Asset_Id,
506 estimated_cost = X_Estimated_Cost,
507 manufacturer_name = X_Manufacturer_Name,
508 model_number = X_Model_Number,
509 serial_number = X_Serial_Number,
510 tag_number = X_Tag_Number ,
511 capital_event_id = X_Capital_Event_Id,
512 capital_hold_flag = X_Capital_Hold_Flag,
513 ret_target_asset_id = X_Ret_Target_Asset_Id
514 WHERE rowid = X_Rowid;
515
516 if (SQL%NOTFOUND) then
517 Raise NO_DATA_FOUND;
518 end if;
519 END Update_Row;
520
521 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
522 X_project_asset_id NUMBER) IS
523 BEGIN
524 DELETE FROM pa_project_asset_assignments
525 WHERE project_asset_id = X_project_asset_id;
526
527 DELETE FROM pa_project_assets
528 WHERE rowid = X_Rowid;
529
530 if (SQL%NOTFOUND) then
531 Raise NO_DATA_FOUND;
532 end if;
533 END Delete_Row;
534
535
536 END PA_PROJECT_ASSETS_PKG;