[Home] [Help]
PACKAGE BODY: APPS.WIP_REBUILD
Source
1 package body wip_rebuild AS
2 /* $Header: wiprbldb.pls 115.22 2004/06/02 18:07:20 kboonyap ship $ */
3
4 procedure insert_job_interface(p_tempId IN NUMBER,
5 x_groupId OUT NOCOPY NUMBER) IS BEGIN
6
7 select wip_job_schedule_interface_s.nextval
8 into x_groupID
9 from dual;
10
11 INSERT INTO WIP_JOB_SCHEDULE_INTERFACE(
12 creation_date,
13 created_by,
14 last_update_login,
15 last_update_date,
16 last_updated_by,
17 request_id,
18 program_application_id,
19 program_id,
20 program_update_date,
21 load_type,
22 group_id,
23 scheduling_method,
24 first_unit_start_date,
25 job_name,
26 description,
27 primary_item_id,
28 organization_id,
29 process_phase,
30 process_status,
31 rebuild_item_id,
32 rebuild_serial_number,
33 parent_wip_entity_id,
34 manual_rebuild_flag,
35 activity_type,
36 activity_cause,
37 activity_source,
38 priority,
39 owning_department,
40 tagout_required,
41 shutdown_type)
42 SELECT sysdate,
43 mmtt.created_by,
44 mmtt.last_update_login,
45 sysdate,
46 mmtt.last_updated_by,
47 mmtt.request_id,
48 mmtt.program_application_id,
49 mmtt.program_id,
50 mmtt.program_update_date,
51 wip_constants.CREATE_EAM_JOB,
52 x_groupID,
53 wip_constants.routing,
54 SYSDATE,
55 mmtt.rebuild_job_name,
56 msi.description,
57 mmtt.rebuild_activity_id,
58 mmtt.organization_id,
59 wip_constants.ML_VALIDATION,
60 wip_constants.pending,
61 mmtt.rebuild_item_id,
62 mmtt.rebuild_serial_number,
63 mmtt.transaction_source_id,
64 'N',
65 meaa.activity_type_code,
66 meaa.activity_cause_code,
67 meaa.activity_source_code,
68 to_number(meaa.priority_code),
69 meaa.owning_department_id,
70 meaa.tagging_required_flag,
71 to_number(meaa.shutdown_type_code)
72 FROM MTL_MATERIAL_TRANSACTIONS_TEMP mmtt,
73 mtl_system_items msi,
74 mtl_eam_asset_activities meaa
75 WHERE transaction_temp_id = p_tempId
76 and mmtt.rebuild_activity_id = msi.inventory_item_id (+)
77 and mmtt.organization_id = msi.organization_id (+)
78 and mmtt.rebuild_activity_id = meaa.asset_activity_id (+)
79 and mmtt.organization_id = meaa.organization_id (+)
80 and mmtt.rebuild_item_id = meaa.inventory_item_id (+)
81 and nvl(mmtt.rebuild_serial_number, '@@@') =
82 nvl(meaa.serial_number,'@@@'); /* Bug 3661984 */
83
84 end insert_job_interface;
85
86 procedure update_genealogy(p_tempId IN NUMBER,
87 x_retVal OUT NOCOPY VARCHAR2,
88 x_errMsg OUT NOCOPY VARCHAR2) IS
89
90 l_rebuildSerNum VARCHAR2(30);
91 l_rebuildItemID NUMBER;
92 l_orgID NUMBER;
93 l_txnDate DATE;
94 l_assetNum VARCHAR2(30);
95 l_assetGrpID NUMBER;
96 l_serial_status NUMBER; /* Bug 3655393 */
97
98 l_msgCount NUMBER;
99 BEGIN
100
101 x_retVal := fnd_api.G_RET_STS_SUCCESS; --assume success
102
103 select mmtt.rebuild_serial_number,
104 mmtt.rebuild_item_id,
105 mmtt.organization_id,
106 mmtt.transaction_date,
107 wdj.asset_number, --will need these eventually
108 wdj.asset_group_id
109 into l_rebuildSerNum,
110 l_rebuildItemID,
111 l_orgID,
112 l_txnDate,
113 l_assetNum,
114 l_assetGrpID
115 from mtl_material_transactions_temp mmtt,
116 wip_discrete_jobs wdj
117 where wdj.wip_entity_id = mmtt.transaction_source_id
118 and mmtt.transaction_temp_id = p_tempId;
119
120 --call genealogy API whether or not a serial exists. The eam procedure
121 --will figure out what to do (if anything).
122 /* Bug 3655393 - Should not call update_geneology if serial_status = 1
123 or defined but not used.Not handling NO_DATA_FOUND as we do not want
124 to call update_geneology, if there is no serial exists. Transaction
125 should error out if no MSN record for corresponding
126 rebuild_serial_number in mmtt*/
127
128 /*Bug 3655393 - reset l_serial_status to 0 so that in case no rebuid
129 serial number present also, update_geneology will be called*/
130 l_serial_status := 0;
131
132 if (l_rebuildSerNum is NOT NULL and
133 l_rebuildItemID is NOT NULL) then
134 select current_status
135 into l_serial_status
136 from mtl_serial_numbers
137 where serial_number = l_rebuildSerNum
138 and current_organization_id= l_orgID
139 and inventory_item_id = l_rebuildItemID;
140 end if;
141
142 if (l_serial_status <> WIP_CONSTANTS.DEF_NOT_USED) then /*Bug 3655393*/
143 wip_eam_genealogy_pvt.update_eam_genealogy(
144 p_api_version => 1.0,
145 p_object_type => 2, -- serial number
146 p_serial_number => l_rebuildSerNum,
147 p_inventory_item_id => l_rebuildItemID,
148 p_organization_id => l_orgID,
149 p_genealogy_type => 5, --asset/item releationship
150 p_end_date_active => l_txnDate,
151 x_return_status => x_retVal,
152 x_msg_count => l_msgCount,
153 x_msg_data => x_errMsg);
154 end if;
155
156 EXCEPTION when others then
157 x_retVal := fnd_api.G_RET_STS_ERROR;
158 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
159 fnd_message.set_token('ERROR_TEXT', 'wip_rebuild.update_genealogy');
160 x_errMsg := fnd_message.get;
161 end update_genealogy;
162
163 procedure create_rebuild_job(p_tempId IN NUMBER,
164 x_retVal OUT NOCOPY VARCHAR2,
165 x_errMsg OUT NOCOPY VARCHAR2) IS
166
167 l_groupId NUMBER;
168 l_requestId NUMBER;
169 l_phase VARCHAR2(240);
170 l_status VARCHAR2(240);
171 l_devPhase VARCHAR2(240);
172 l_devStatus VARCHAR2(240);
173 l_message VARCHAR2(240);
174 l_success BOOLEAN;
175
176 l_interface_record WIP_EAMWORKORDER_PVT.work_order_interface_rec_type;
177 --cursor to fetch details for the workorder to be created
178
179
180 CURSOR workorder IS
181 SELECT mmtt.created_by,
182 mmtt.last_update_login,
183 mmtt.last_updated_by,
184 mmtt.request_id,
185 mmtt.program_application_id,
186 mmtt.program_id,
187 mmtt.program_update_date,
188 mmtt.rebuild_job_name,
189 msi.description,
190 mmtt.rebuild_activity_id,
191 mmtt.organization_id,
192 mmtt.rebuild_item_id,
193 mmtt.rebuild_serial_number,
194 mmtt.transaction_source_id,
195 msn.wip_accounting_class_code,
196 meaa.activity_type_code,
197 meaa.activity_cause_code,
198 to_number(meaa.priority_code) as priority_code,
199 meaa.owning_department_id,
200 tagging_required_flag,
201 to_number(shutdown_type_code) as shutdown_type_code
202 FROM MTL_MATERIAL_TRANSACTIONS_TEMP mmtt,
203 mtl_serial_numbers msn,
204 mtl_system_items msi,
205 mtl_eam_asset_activities meaa
206 WHERE transaction_temp_id = p_tempId
207 and mmtt.rebuild_activity_id = msi.inventory_item_id (+)
208 and mmtt.organization_id = msi.organization_id (+)
209 and mmtt.rebuild_activity_id = meaa.asset_activity_id (+)
210 and mmtt.organization_id = meaa.organization_id (+)
211 and mmtt.rebuild_item_id = meaa.inventory_item_id (+)
212 and nvl(mmtt.rebuild_serial_number, '@@@') =
213 nvl(meaa.serial_number, '@@@'); /* Bug 3661984 */
214
215 l_workorder workorder%ROWTYPE;
216 l_return_status VARCHAR2(20);
217 l_msg_count NUMBER;
218 l_msg_data VARCHAR2(1000);
219 l_group_id NUMBER;
220 l_request_id NUMBER;
221
222 l_maintenance_object_id NUMBER;
223 l_maintenance_object_type NUMBER;
224 l_output_dir VARCHAR2(500);
225
226
227 mesg varchar2(2000);
228 i NUMBER;
229 msg_index number;
230 temp varchar2(500);
231
232
233 /* added for calling WO API */
234
235 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
236 l_eam_op_rec EAM_PROCESS_WO_PUB.eam_op_rec_type;
237 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
238 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
239 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
240 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
241 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
242 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
243 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
244 l_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
245
246 l_out_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
247 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
248 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
249 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
250 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
251 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
252 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
253 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
254 l_out_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
255
256 begin
257 SAVEPOINT REBUILD;
258
259 OPEN workorder;
260 FETCH workorder into l_workorder;
261
262 IF(workorder %FOUND) THEN
263
264 --find maintenance object type,id,source
265 if (l_workorder.rebuild_item_id is not null and l_workorder.rebuild_serial_number is not null) then
266 BEGIN
267 select gen_object_id into l_maintenance_object_id
268 from mtl_serial_numbers
269 where inventory_item_id = l_workorder.rebuild_item_id and serial_number = l_workorder.rebuild_serial_number and current_organization_id =l_workorder.organization_id;
270 l_maintenance_object_type := 1;
271 EXCEPTION
272 WHEN NO_DATA_FOUND THEN
273 NULL;
274 END;
275 else
276 BEGIN
277 SELECT inventory_item_id into l_maintenance_object_id
278 FROM mtl_system_items
279 WHERE inventory_item_id = l_workorder.rebuild_item_id and organization_id =l_workorder.organization_id;
280 l_maintenance_object_type := 2;
281 EXCEPTION
282 WHEN NO_DATA_FOUND THEN
283 NULL;
284 END;
285 end if;
286
287
288 begin
289 select trim(substr(value, 1, DECODE( instr( value, ','), 0, length( value), instr( value, ',') -1 ) ) ) into l_output_dir FROM v$parameter WHERE name = 'utl_file_dir';
290 exception
291 when NO_DATA_FOUND then
292 null;
293 end;
294
295
296 l_eam_wo_rec.user_id := fnd_global.user_id;
297 l_eam_wo_rec.responsibility_id :=fnd_global.resp_id;
298 l_eam_wo_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_CREATE;
299 l_eam_wo_rec.batch_id := 1;
300 l_eam_wo_rec.header_id := 1;
301 l_eam_wo_rec.wip_entity_name := l_workorder.rebuild_job_name;
302 l_eam_wo_rec.description := l_workorder.description;
303 l_eam_wo_rec.asset_activity_id := l_workorder.rebuild_activity_id;
304 l_eam_wo_rec.organization_id := l_workorder.organization_id;
305 l_eam_wo_rec.rebuild_item_id := l_workorder.rebuild_item_id;
306 l_eam_wo_rec.rebuild_serial_number := l_workorder.rebuild_serial_number;
307 l_eam_wo_rec.parent_wip_entity_id := l_workorder.transaction_source_id;
308 l_eam_wo_rec.manual_rebuild_flag := 'N';
309 l_eam_wo_rec.class_code := l_workorder.wip_accounting_class_code;
310 l_eam_wo_rec.activity_type := l_workorder.activity_type_code;
311 l_eam_wo_rec.activity_cause := l_workorder.activity_cause_code;
312 l_eam_wo_rec.priority := l_workorder.priority_code;
313 l_eam_wo_rec.owning_department := l_workorder.owning_department_id;
314 l_eam_wo_rec.tagout_required := l_workorder.tagging_required_flag;
315 l_eam_wo_rec.shutdown_type := l_workorder.shutdown_type_code;
316 l_eam_wo_rec.status_type := 1;
317 l_eam_wo_rec.maintenance_object_source := 1;
318 l_eam_wo_rec.maintenance_object_type := l_maintenance_object_type;
319 l_eam_wo_rec.maintenance_object_id := l_maintenance_object_id;
320 l_eam_wo_rec.requested_start_date := sysdate;
321 l_eam_wo_rec.scheduled_start_date := sysdate;
322 l_eam_wo_rec.scheduled_completion_date := sysdate;
323 l_eam_wo_rec.firm_planned_flag := 2;
324 l_eam_wo_rec.wip_supply_type := 7;
325 l_eam_wo_rec.po_creation_time := 2;
326
327
328 EAM_PROCESS_WO_PUB.Process_WO
329 ( p_bo_identifier => 'EAM'
330 , p_init_msg_list => TRUE
331 , p_api_version_number => 1.0
332 , p_commit => 'N'
333 , p_eam_wo_rec => l_eam_wo_rec
334 , p_eam_op_tbl => l_eam_op_tbl
335 , p_eam_op_network_tbl => l_eam_op_network_tbl
336 , p_eam_res_tbl => l_eam_res_tbl
337 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
338 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
339 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
340 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
341 , p_eam_direct_items_tbl => l_eam_di_tbl
342 , x_eam_wo_rec => l_out_eam_wo_rec
343 , x_eam_op_tbl => l_out_eam_op_tbl
344 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
345 , x_eam_res_tbl => l_out_eam_res_tbl
346 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
347 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
348 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
349 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
350 , x_eam_direct_items_tbl => l_out_eam_di_tbl
351 , x_return_status => l_return_status
352 , x_msg_count => l_msg_count
353 , p_debug =>NVL(fnd_profile.value('EAM_DEBUG'), 'N')
354 , p_debug_filename => 'wiprbldb.log'
355 , p_output_dir => l_output_dir
356 , p_debug_file_mode => 'W'
357 );
358 END IF;
359 CLOSE workorder;
360
361 IF(l_return_status<>'S') THEN
362 x_retVal := FND_API.G_RET_STS_ERROR;
363
364 --get the messages from the wo api
365
366 mesg := '';
367 IF(l_msg_count>0) THEN
368 msg_index := l_msg_count;
369 for i in 1..l_msg_count loop
370 fnd_msg_pub.get(p_msg_index => FND_MSG_PUB.G_NEXT,
371 p_encoded => 'F',
372 p_data => temp,
373 p_msg_index_out => msg_index);
374 msg_index := msg_index-1;
375 mesg := mesg || ' ' || to_char(i) || ' . '||temp ;
376 end loop;
377 END IF;
378
379 x_errMsg := mesg;
380 ROLLBACK TO REBUILD;
381 else
382
383 update_genealogy(p_tempId => p_tempId,
384 x_retVal => x_retVal,
385 x_errMsg => x_errMsg);
386 if(x_retVal <> FND_API.G_RET_STS_SUCCESS) then
387 ROLLBACK TO REBUILD; --gen update failure
388 end if;
389 end if;
390
391 end create_rebuild_job;
392 end wip_rebuild;