[Home] [Help]
PACKAGE BODY: APPS.PA_COPY_ASSET_PVT
Source
1 PACKAGE BODY PA_COPY_ASSET_PVT AS
2 /* $Header: PACCPYAB.pls 115.3 2003/08/18 14:30:00 ajdas noship $ */
3
4
5 PROCEDURE COPY_ASSET
6 (p_cur_project_asset_id IN NUMBER,
7 p_asset_name IN VARCHAR2,
8 p_asset_description IN VARCHAR2,
9 p_project_asset_type IN VARCHAR2,
10 p_asset_units IN NUMBER DEFAULT NULL,
11 p_est_asset_units IN NUMBER DEFAULT NULL,
12 p_asset_dpis IN DATE DEFAULT NULL,
13 p_est_asset_dpis IN DATE DEFAULT NULL,
14 p_asset_number IN VARCHAR2 DEFAULT NULL,
15 p_copy_assignments IN VARCHAR2,
16 x_new_project_asset_id OUT NOCOPY NUMBER,
17 x_return_status OUT NOCOPY VARCHAR2,
18 x_msg_data OUT NOCOPY VARCHAR2) IS
19
20
21 CURSOR assignments_cur IS
22 SELECT *
23 FROM pa_project_asset_assignments
24 WHERE project_asset_id = p_cur_project_asset_id;
25
26 assignments_rec assignments_cur%ROWTYPE;
27
28 current_asset_rec pa_project_assets_all%ROWTYPE;
29
30 v_user NUMBER := FND_GLOBAL.user_id;
31 v_login NUMBER := FND_GLOBAL.login_id;
32
33
34
35 BEGIN
36 x_return_status := 'S';
37
38
39
40 --Get the current asset information
41 SELECT *
42 INTO current_asset_rec
43 FROM pa_project_assets_all
44 WHERE project_asset_id = p_cur_project_asset_id;
45
46
47 --Get next project_asset_id sequence value
48 SELECT pa_project_assets_s.NEXTVAL
49 INTO x_new_project_asset_id
50 FROM SYS.DUAL;
51
52 --Insert new project asset, since all validations have passed
53 INSERT INTO pa_project_assets_all(
54 project_asset_id,
55 project_id,
56 asset_number,
57 asset_name,
58 asset_description,
59 location_id,
60 assigned_to_person_id,
61 date_placed_in_service,
62 asset_category_id,
63 book_type_code,
64 asset_units,
65 depreciate_flag,
66 depreciation_expense_ccid,
67 amortize_flag,
68 capitalized_flag,
69 reverse_flag,
70 capital_hold_flag,
71 estimated_in_service_date,
72 last_update_date,
73 last_updated_by,
74 created_by,
75 creation_date,
76 last_update_login,
77 attribute_category,
78 attribute1,
79 attribute2,
80 attribute3,
81 attribute4,
82 attribute5,
83 attribute6,
84 attribute7,
85 attribute8,
86 attribute9,
87 attribute10,
88 attribute11,
89 attribute12,
90 attribute13,
91 attribute14,
92 attribute15,
93 org_id,
94 asset_key_ccid,
95 project_asset_type,
96 estimated_cost,
97 estimated_asset_units,
98 parent_asset_id,
99 manufacturer_name,
100 model_number,
101 tag_number, --Don't copy Tag Number, since it must be unique
102 serial_number,
103 ret_target_asset_id
104 )
105 VALUES (
106 x_new_project_asset_id,
107 current_asset_rec.project_id,
108 p_asset_number,
109 p_asset_name,
110 p_asset_description,
111 current_asset_rec.location_id,
112 current_asset_rec.assigned_to_person_id,
113 p_asset_dpis,
114 current_asset_rec.asset_category_id,
115 current_asset_rec.book_type_code,
116 p_asset_units,
117 current_asset_rec.depreciate_flag,
118 current_asset_rec.depreciation_expense_ccid,
119 current_asset_rec.amortize_flag,
120 'N', --Capitalized Flag
121 'N', --Reverse Flag
122 'N', --Capital Hold Flag
123 p_est_asset_dpis,
124 SYSDATE, --last_update_date
125 v_user, --last_updated_by
126 v_user, --created_by
127 SYSDATE, --creation_date
128 v_login, --last_update_login
129 current_asset_rec.attribute_category,
130 current_asset_rec.attribute1,
131 current_asset_rec.attribute2,
132 current_asset_rec.attribute3,
133 current_asset_rec.attribute4,
134 current_asset_rec.attribute5,
135 current_asset_rec.attribute6,
136 current_asset_rec.attribute7,
137 current_asset_rec.attribute8,
138 current_asset_rec.attribute9,
139 current_asset_rec.attribute10,
140 current_asset_rec.attribute11,
141 current_asset_rec.attribute12,
142 current_asset_rec.attribute13,
143 current_asset_rec.attribute14,
144 current_asset_rec.attribute15,
145 current_asset_rec.org_id,
146 current_asset_rec.asset_key_ccid,
147 p_project_asset_type,
148 current_asset_rec.estimated_cost,
149 p_est_asset_units,
150 current_asset_rec.parent_asset_id,
151 current_asset_rec.manufacturer_name,
152 current_asset_rec.model_number,
153 NULL, --current_asset_rec.tag_number, --Don't copy Tag Number, since it must be unique
154 current_asset_rec.serial_number,
155 current_asset_rec.ret_target_asset_id
156 );
157
158
159 --Copy Asset Assignments if indicated
160 IF SUBSTR(p_copy_assignments,1,1) = 'Y' THEN
161
162 FOR assignments_rec IN assignments_cur LOOP
163
164 --Insert new asset assignments
165 INSERT INTO pa_project_asset_assignments(
166 project_asset_id,
167 task_id,
168 project_id,
169 last_update_date,
170 last_updated_by,
171 created_by,
172 creation_date,
173 last_update_login,
174 attribute_category,
175 attribute1,
176 attribute2,
177 attribute3,
178 attribute4,
179 attribute5,
180 attribute6,
181 attribute7,
182 attribute8,
183 attribute9,
184 attribute10,
185 attribute11,
186 attribute12,
187 attribute13,
188 attribute14,
189 attribute15
190 )
191 VALUES (
192 x_new_project_asset_id,
193 assignments_rec.task_id,
194 assignments_rec.project_id,
195 SYSDATE, --last_update_date
196 v_user, --last_updated_by
197 v_user, --created_by
198 SYSDATE, --creation_date
199 v_login, --last_update_login
200 current_asset_rec.attribute_category,
201 current_asset_rec.attribute1,
202 current_asset_rec.attribute2,
203 current_asset_rec.attribute3,
204 current_asset_rec.attribute4,
205 current_asset_rec.attribute5,
206 current_asset_rec.attribute6,
207 current_asset_rec.attribute7,
208 current_asset_rec.attribute8,
209 current_asset_rec.attribute9,
210 current_asset_rec.attribute10,
211 current_asset_rec.attribute11,
212 current_asset_rec.attribute12,
213 current_asset_rec.attribute13,
214 current_asset_rec.attribute14,
215 current_asset_rec.attribute15
216 );
217 END LOOP; --Asset Assignments
218 END IF; --Copy Assignments = 'Y'
219
220
221 EXCEPTION
222
223 WHEN NO_DATA_FOUND THEN
224 x_return_status := 'U';
225 x_msg_data := 'Project asset id '||p_cur_project_asset_id||' not found. '||SQLCODE||' '||SQLERRM;
226 RAISE;
227
228
229 WHEN OTHERS THEN
230 x_return_status := 'U';
231 x_msg_data := 'Unexpected error for project asset id '||p_cur_project_asset_id||': '||SQLCODE||' '||SQLERRM;
232 RAISE;
233
234
235 END COPY_ASSET;
236
237
238 END PA_COPY_ASSET_PVT;