[Home] [Help]
PACKAGE BODY: APPS.PA_COPY_ASSET_PVT
Source
1 PACKAGE BODY PA_COPY_ASSET_PVT AS
2 /* $Header: PACCPYAB.pls 120.1 2009/08/20 06:52:08 sgottimu ship $ */
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 new_master_flag /* Bug# 8781769 */
105 )
106 VALUES (
107 x_new_project_asset_id,
108 current_asset_rec.project_id,
109 p_asset_number,
110 p_asset_name,
111 p_asset_description,
112 current_asset_rec.location_id,
113 current_asset_rec.assigned_to_person_id,
114 p_asset_dpis,
115 current_asset_rec.asset_category_id,
116 current_asset_rec.book_type_code,
117 p_asset_units,
118 current_asset_rec.depreciate_flag,
119 current_asset_rec.depreciation_expense_ccid,
120 current_asset_rec.amortize_flag,
121 'N', --Capitalized Flag
122 'N', --Reverse Flag
123 'N', --Capital Hold Flag
124 p_est_asset_dpis,
125 SYSDATE, --last_update_date
126 v_user, --last_updated_by
127 v_user, --created_by
128 SYSDATE, --creation_date
129 v_login, --last_update_login
130 current_asset_rec.attribute_category,
131 current_asset_rec.attribute1,
132 current_asset_rec.attribute2,
133 current_asset_rec.attribute3,
134 current_asset_rec.attribute4,
135 current_asset_rec.attribute5,
136 current_asset_rec.attribute6,
137 current_asset_rec.attribute7,
138 current_asset_rec.attribute8,
139 current_asset_rec.attribute9,
140 current_asset_rec.attribute10,
141 current_asset_rec.attribute11,
142 current_asset_rec.attribute12,
143 current_asset_rec.attribute13,
144 current_asset_rec.attribute14,
145 current_asset_rec.attribute15,
146 current_asset_rec.org_id,
147 current_asset_rec.asset_key_ccid,
148 p_project_asset_type,
149 current_asset_rec.estimated_cost,
150 p_est_asset_units,
151 current_asset_rec.parent_asset_id,
152 current_asset_rec.manufacturer_name,
153 current_asset_rec.model_number,
154 NULL, --current_asset_rec.tag_number, --Don't copy Tag Number, since it must be unique
155 current_asset_rec.serial_number,
156 current_asset_rec.ret_target_asset_id,
157 nvl(current_asset_rec.new_master_flag,'N')
158 );
159
160
161 --Copy Asset Assignments if indicated
162 IF SUBSTR(p_copy_assignments,1,1) = 'Y' THEN
163
164 FOR assignments_rec IN assignments_cur LOOP
165
166 --Insert new asset assignments
167 INSERT INTO pa_project_asset_assignments(
168 project_asset_id,
169 task_id,
170 project_id,
171 last_update_date,
172 last_updated_by,
173 created_by,
174 creation_date,
175 last_update_login,
176 attribute_category,
177 attribute1,
178 attribute2,
179 attribute3,
180 attribute4,
181 attribute5,
182 attribute6,
183 attribute7,
184 attribute8,
185 attribute9,
186 attribute10,
187 attribute11,
188 attribute12,
189 attribute13,
190 attribute14,
191 attribute15
192 )
193 VALUES (
194 x_new_project_asset_id,
195 assignments_rec.task_id,
196 assignments_rec.project_id,
197 SYSDATE, --last_update_date
198 v_user, --last_updated_by
199 v_user, --created_by
200 SYSDATE, --creation_date
201 v_login, --last_update_login
202 current_asset_rec.attribute_category,
203 current_asset_rec.attribute1,
204 current_asset_rec.attribute2,
205 current_asset_rec.attribute3,
206 current_asset_rec.attribute4,
207 current_asset_rec.attribute5,
208 current_asset_rec.attribute6,
209 current_asset_rec.attribute7,
210 current_asset_rec.attribute8,
211 current_asset_rec.attribute9,
212 current_asset_rec.attribute10,
213 current_asset_rec.attribute11,
214 current_asset_rec.attribute12,
215 current_asset_rec.attribute13,
216 current_asset_rec.attribute14,
217 current_asset_rec.attribute15
218 );
219 END LOOP; --Asset Assignments
220 END IF; --Copy Assignments = 'Y'
221
222
223 EXCEPTION
224
225 WHEN NO_DATA_FOUND THEN
226 x_return_status := 'U';
227 x_msg_data := 'Project asset id '||p_cur_project_asset_id||' not found. '||SQLCODE||' '||SQLERRM;
228 RAISE;
229
230
231 WHEN OTHERS THEN
232 x_return_status := 'U';
233 x_msg_data := 'Unexpected error for project asset id '||p_cur_project_asset_id||': '||SQLCODE||' '||SQLERRM;
234 RAISE;
235
236
237 END COPY_ASSET;
238
239
240 END PA_COPY_ASSET_PVT;