[Home] [Help]
PACKAGE BODY: APPS.EAM_REBUILD
Source
1 package body eam_rebuild AS
2 /* $Header: EAMRBLDB.pls 120.4 2006/06/02 18:51:48 anjgupta noship $ */
3
4
5
6 /* List of Changes made for IB on update_genealogy procedure
7
8 1 Removed fetching Asset_Group_Id and Asset_Number columns from WDJ from
9 the initial query since the columns were not being used anywhere
10 */
11
12 procedure update_genealogy(p_tempId IN NUMBER,
13 x_retVal OUT NOCOPY VARCHAR2,
14 x_errMsg OUT NOCOPY VARCHAR2) IS
15
16 l_rebuildSerNum VARCHAR2(30);
17 l_rebuildItemID NUMBER;
18 l_orgID NUMBER;
19 l_txnDate DATE;
20 l_assetNum VARCHAR2(30);
21 l_assetGrpID NUMBER;
22
23 l_msgCount NUMBER;
24 l_serial_status NUMBER;
25 BEGIN
26
27 x_retVal := fnd_api.G_RET_STS_SUCCESS; --assume success
28
29 select mmtt.rebuild_serial_number,
30 mmtt.rebuild_item_id,
31 mmtt.organization_id,
32 mmtt.transaction_date
33 into l_rebuildSerNum,
34 l_rebuildItemID,
35 l_orgID,
36 l_txnDate
37 from mtl_material_transactions_temp mmtt
38 where mmtt.transaction_temp_id = p_tempId;
39
40 --call genealogy API whether or not a serial exists. The eam procedure
41 --will figure out what to do (if anything).
42 l_serial_status := 0; --fix for 3733049.initialise so that update_eam_genealogy will be called even if there is no serial number
43
44 if (l_rebuildSerNum is NOT NULL and
45 l_rebuildItemID is NOT NULL) then
46 select current_status
47 into l_serial_status
48 from mtl_serial_numbers
49 where serial_number = l_rebuildSerNum
50 and inventory_item_id = l_rebuildItemID;
51 end if;
52
53 if (l_serial_status <> WIP_CONSTANTS.DEF_NOT_USED) then --if status is not (defined but not used) then only call
54
55 wip_eam_genealogy_pvt.update_eam_genealogy(
56 p_api_version => 1.0,
57 p_object_type => 2, -- serial number
58 p_serial_number => l_rebuildSerNum,
59 p_inventory_item_id => l_rebuildItemID,
60 p_organization_id => l_orgID,
61 p_genealogy_type => 5, --asset/item releationship
62 p_end_date_active => l_txnDate,
63 x_return_status => x_retVal,
64 x_msg_count => l_msgCount,
65 x_msg_data => x_errMsg);
66 end if;
67
68
69 EXCEPTION when others then
70 x_retVal := fnd_api.G_RET_STS_ERROR;
71 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
72 fnd_message.set_token('ERROR_TEXT', 'eam_rebuild.update_genealogy');
73 x_errMsg := fnd_message.get;
74 end update_genealogy;
75
76 procedure create_rebuild_job(p_tempId IN NUMBER,
77 x_retVal OUT NOCOPY VARCHAR2,
78 x_errMsg OUT NOCOPY VARCHAR2) IS
79
80 l_groupId NUMBER;
81 l_requestId NUMBER;
82 l_phase VARCHAR2(240);
83 l_status VARCHAR2(240);
84 l_devPhase VARCHAR2(240);
85 l_devStatus VARCHAR2(240);
86 l_message VARCHAR2(240);
87 l_success BOOLEAN;
88 l_maintenance_object_id NUMBER;
89 l_maintenance_object_type NUMBER;
90
91
92 --cursor to fetch details for the workorder to be created
93
94 CURSOR workorder (l_org_id number) IS
95 SELECT mmtt.created_by,
96 mmtt.last_update_login,
97 mmtt.last_updated_by,
98 mmtt.request_id,
99 mmtt.program_application_id,
100 mmtt.program_id,
101 mmtt.program_update_date,
102 mmtt.rebuild_job_name,
103 msi.description,
104 mmtt.rebuild_activity_id,
105 mmtt.organization_id,
106 mmtt.rebuild_item_id,
107 mmtt.rebuild_serial_number,
108 mmtt.transaction_source_id,
109 eomd.activity_type_code,
110 eomd.activity_cause_code,
111 to_number(meaa.priority_code) as priority_code,
112 eomd.owning_department_id,
113 eomd.tagging_required_flag,
114 to_number(eomd.shutdown_type_code) as shutdown_type_code
115 FROM MTL_MATERIAL_TRANSACTIONS_TEMP mmtt,
116 mtl_system_items msi,
117 mtl_eam_asset_activities meaa,
118 (select * from eam_org_maint_defaults
119 where organization_id = l_org_id) eomd
120 --activity has to be assigned to the work order organization
121 -- hence no meed to join on MP.
122 WHERE transaction_temp_id = p_tempId
123 and mmtt.rebuild_activity_id = msi.inventory_item_id (+)
124 and mmtt.organization_id = msi.organization_id (+)
125 and mmtt.rebuild_activity_id = meaa.asset_activity_id (+)
126 and l_maintenance_object_id = meaa.maintenance_object_id (+)
127 and l_maintenance_object_type = meaa.maintenance_object_type(+)
128 and eomd.object_type (+) = 60
129 and eomd.object_id (+) = meaa.activity_association_id;
130
131 l_workorder workorder%ROWTYPE;
132 l_return_status VARCHAR2(20);
133 l_msg_count NUMBER;
134 l_msg_data VARCHAR2(1000);
135 l_group_id NUMBER;
136 l_request_id NUMBER;
137
138
139
140 l_rebuild_item_id NUMBER;
141 l_rebuild_serial_number VARCHAR2(30);
142 l_org_id NUMBER;
143 l_output_dir VARCHAR2(512);
144
145
146 mesg varchar2(2000);
147 i NUMBER;
148 msg_index number;
149 temp varchar2(500);
150
151
152 /* added for calling WO API */
153
154 l_eam_wo_tbl EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
155 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
156 l_eam_op_rec EAM_PROCESS_WO_PUB.eam_op_rec_type;
157 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
158 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
159 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
160 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
161 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
162 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
163 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
164 l_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
165 l_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
166 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
167 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
168 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
169 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
170 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
171 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
172 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
173
174 l_out_eam_wo_tbl EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
175 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
176 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
177 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
178 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
179 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
180 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
181 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
182 l_out_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
183 l_out_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
184 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
185 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
186 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
187 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
188 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
189 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
190 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
191
192 l_eam_wo_relations_tbl EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
193 l_out_eam_wo_relations_tbl EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
194 l_eam_wo_relations_rec EAM_PROCESS_WO_PUB.eam_wo_relations_rec_type;
195 l_serial number;
196
197 begin
198 SAVEPOINT REBUILD;
199
200 --fix for 3733049.Get the maintenance_object_id and type and then open the cursor
201 SELECT rebuild_item_id,rebuild_serial_number,organization_id
202 INTO l_rebuild_item_id,l_rebuild_serial_number,l_org_id
203 FROM MTL_MATERIAL_TRANSACTIONS_TEMP
204 where transaction_temp_id=p_tempId;
205
206 --find maintenance object type,id,source
207 if (l_rebuild_item_id is not null and l_rebuild_serial_number is not null) then
208 BEGIN
209 select cii.instance_id into l_maintenance_object_id
210 from csi_item_instances cii, mtl_parameters mp
211 where cii.inventory_item_id = l_rebuild_item_id and cii.serial_number = l_rebuild_serial_number
212 and mp.organization_id = cii.last_vld_organization_id
213 and mp.maint_organization_id =l_org_id;
214
215 l_maintenance_object_type := 3;
216 EXCEPTION
217 WHEN NO_DATA_FOUND THEN
218 NULL;
219 END;
220 else
221 BEGIN
222 SELECT msi.inventory_item_id into l_maintenance_object_id
223 FROM mtl_system_items msi, mtl_parameters mp
224 WHERE msi.inventory_item_id = l_rebuild_item_id
225 and mp.organization_id = msi.organization_id
226 and mp.maint_organization_id =l_org_id
227 and rownum = 1;
228
229 l_maintenance_object_type := 2;
230
231 EXCEPTION
232 WHEN NO_DATA_FOUND THEN
233 NULL;
234 END;
235 end if;
236
237
238 update_genealogy(p_tempId => p_tempId,
239 x_retVal => x_retVal,
240 x_errMsg => x_errMsg);
241 if(x_retVal <> FND_API.G_RET_STS_SUCCESS) then
242 ROLLBACK TO REBUILD; --gen update failure
243 else
244
245 OPEN workorder(l_org_id);
246 FETCH workorder into l_workorder;
247
248 IF(workorder %FOUND) THEN
249
250
251 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
252
253
254
255 l_eam_wo_rec.user_id := fnd_global.user_id;
256 l_eam_wo_rec.responsibility_id :=fnd_global.resp_id;
257 l_eam_wo_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_CREATE;
258 l_eam_wo_rec.batch_id := 1;
259 l_eam_wo_rec.header_id := 1;
260 l_eam_wo_rec.wip_entity_name := l_workorder.rebuild_job_name;
261 l_eam_wo_rec.description := l_workorder.description;
262 l_eam_wo_rec.asset_activity_id := l_workorder.rebuild_activity_id;
263 l_eam_wo_rec.organization_id := l_workorder.organization_id;
264 l_eam_wo_rec.rebuild_item_id := l_workorder.rebuild_item_id;
265 l_eam_wo_rec.rebuild_serial_number := l_workorder.rebuild_serial_number;
266 l_eam_wo_rec.parent_wip_entity_id := l_workorder.transaction_source_id;
267 l_eam_wo_rec.manual_rebuild_flag := 'N';
268 l_eam_wo_rec.activity_type := l_workorder.activity_type_code;
269 l_eam_wo_rec.activity_cause := l_workorder.activity_cause_code;
270 l_eam_wo_rec.priority := l_workorder.priority_code;
271 l_eam_wo_rec.owning_department := l_workorder.owning_department_id;
272 l_eam_wo_rec.tagout_required := l_workorder.tagging_required_flag;
273 l_eam_wo_rec.shutdown_type := l_workorder.shutdown_type_code;
274 l_eam_wo_rec.status_type := 1;
275 l_eam_wo_rec.maintenance_object_source := 1;
276 l_eam_wo_rec.maintenance_object_type := l_maintenance_object_type;
277 l_eam_wo_rec.maintenance_object_id := l_maintenance_object_id;
278 l_eam_wo_rec.requested_start_date := sysdate;
279 l_eam_wo_rec.scheduled_start_date := sysdate;
280 l_eam_wo_rec.scheduled_completion_date := sysdate;
281 l_eam_wo_rec.firm_planned_flag := 2;
282 l_eam_wo_rec.wip_supply_type := 7;
283
284 l_eam_wo_tbl(1) := l_eam_wo_rec;
285
286 --create follow up relation between workorder created and parent_wip_entity_id
287 IF(l_workorder.transaction_source_id IS NOT NULL) THEN
288 l_eam_wo_relations_rec.batch_id := 1;
289 l_eam_wo_relations_rec.parent_object_id := l_workorder.transaction_source_id;
290 l_eam_wo_relations_rec.parent_object_type_id := 1;
291 l_eam_wo_relations_rec.parent_header_id := l_workorder.transaction_source_id;
292 l_eam_wo_relations_rec.child_object_type_id := 1;
293 l_eam_wo_relations_rec.child_header_id :=1;
294 l_eam_wo_relations_rec.child_object_id := 1;
295 l_eam_wo_relations_rec.parent_relationship_type := 4;
296 l_eam_wo_relations_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_CREATE;
297 l_eam_wo_relations_tbl(1) := l_eam_wo_relations_rec;
298 END IF;
299
300
301 EAM_PROCESS_WO_PUB.Process_Master_Child_WO
302 ( p_bo_identifier => 'EAM'
303 , p_init_msg_list => TRUE
304 , p_api_version_number => 1.0
305 , p_commit => 'N'
306 , p_eam_wo_tbl => l_eam_wo_tbl
307 , p_eam_wo_relations_tbl => l_eam_wo_relations_tbl
308 , p_eam_op_tbl => l_eam_op_tbl
309 , p_eam_op_network_tbl => l_eam_op_network_tbl
310 , p_eam_res_tbl => l_eam_res_tbl
311 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
312 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
313 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
314 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
315 , p_eam_direct_items_tbl => l_eam_di_tbl
316 , p_eam_wo_comp_tbl => l_eam_wo_comp_tbl
317 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
318 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
319 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
320 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
321 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
322 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
323 , p_eam_request_tbl => l_eam_request_tbl
324 , x_eam_wo_tbl => l_out_eam_wo_tbl
325 , x_eam_wo_relations_tbl => l_out_eam_wo_relations_tbl
326 , x_eam_op_tbl => l_out_eam_op_tbl
327 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
328 , x_eam_res_tbl => l_out_eam_res_tbl
329 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
330 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
331 , x_eam_res_usage_tbl => l_eam_res_usage_tbl
332 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
333 , x_eam_direct_items_tbl => l_out_eam_di_tbl
334 , x_eam_wo_comp_tbl => l_out_eam_wo_comp_tbl
335 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
336 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
337 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
338 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
339 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
340 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
341 , x_eam_request_tbl => l_out_eam_request_tbl
342 , x_return_status => l_return_status
343 , x_msg_count => l_msg_count
344 , p_debug =>NVL(fnd_profile.value('EAM_DEBUG'), 'N')
345 , p_debug_filename => 'wiprbldb.log'
346 , p_output_dir => l_output_dir
347 , p_debug_file_mode => 'W'
348 );
349 END IF;
350 CLOSE workorder;
351
352 IF(l_return_status<>'S') THEN
353 x_retVal := FND_API.G_RET_STS_ERROR;
354
355 --get the messages from the wo api
356
357 mesg := '';
358 IF(l_msg_count>0) THEN
359 msg_index := l_msg_count;
360 for i in 1..l_msg_count loop
361 fnd_msg_pub.get(p_msg_index => FND_MSG_PUB.G_NEXT,
362 p_encoded => 'F',
363 p_data => temp,
364 p_msg_index_out => msg_index);
365 msg_index := msg_index-1;
366 mesg := mesg || ' ' || to_char(i) || ' . '||temp ;
367 end loop;
368 END IF;
369
370 x_errMsg := mesg;
371 ROLLBACK TO REBUILD;
372 end if;
373
374 end if;
375
376 end create_rebuild_job;
377 end eam_rebuild;