[Home] [Help]
PACKAGE BODY: APPS.EAM_WB_UTILS
Source
1 PACKAGE BODY EAM_WB_UTILS AS
2 /* $Header: EAMWBUTB.pls 120.26.12020000.5 2013/01/30 13:04:32 srkotika ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30):= 'EAM_WB_UTILS';
5
6 procedure add_forecast(p_pm_forecast_id number) IS
7 begin
8 if current_forecasts.COUNT = 0 then
9 current_forecasts_index := system.eam_wipid_tab_type();
10 end if;
11 current_forecasts(p_pm_forecast_id) := p_pm_forecast_id;
12 current_forecasts_index.extend;
13 current_forecasts_index2(p_pm_forecast_id) := current_forecasts_index.last;
14 current_forecasts_index(current_forecasts_index.last) := p_pm_forecast_id;
15
16 end add_forecast;
17
18 procedure remove_forecast(p_pm_forecast_id number) IS
19 begin
20 current_forecasts.DELETE(p_pm_forecast_id);
21 current_forecasts_index.delete(current_forecasts_index2(p_pm_forecast_id));
22 current_forecasts_index2.DELETE(p_pm_forecast_id);
23 end remove_forecast;
24
25 procedure clear_forecasts IS
26 begin
27 current_forecasts := empty_id_list;
28 current_forecasts_index2 := empty_id_list;
29 if current_forecasts.COUNT = 0 then
30 current_forecasts_index := system.eam_wipid_tab_type();
31 end if;
32 current_forecasts_index.delete;
33 end clear_forecasts;
34
35 function get_forecast_total return number IS
36 begin
37 return current_forecasts.COUNT;
38 end get_forecast_total;
39
40 procedure convert_work_orders2(p_pm_group_id number,
41 p_project_id IN NUMBER DEFAULT NULL,
42 p_task_id IN NUMBER DEFAULT NULL,
43 p_parent_wo_id IN NUMBER DEFAULT NULL,
44 p_planner_code IN NUMBER DEFAULT NULL, -- WO default ER 16094794
45 p_return_status OUT NOCOPY VARCHAR2,
46 p_msg OUT NOCOPY VARCHAR2) IS
47 l_group_id NUMBER;
48 l_forecast_id NUMBER;
49 l_old_flag VARCHAR2(1);
50 l_req_id NUMBER;
51
52 -- parameters needed for the WO wrapper API call
53 l_eam_wo_tbl eam_process_wo_pub.eam_wo_tbl_type;
54 l_eam_wo_relations_tbl eam_process_wo_pub.eam_wo_relations_tbl_type;
55 l_eam_op_tbl eam_process_wo_pub.eam_op_tbl_type;
56 l_eam_op_network_tbl eam_process_wo_pub.eam_op_network_tbl_type;
57 l_eam_res_tbl eam_process_wo_pub.eam_res_tbl_type;
58 l_eam_res_inst_tbl eam_process_wo_pub.eam_res_inst_tbl_type;
59 l_eam_sub_res_tbl eam_process_wo_pub.eam_sub_res_tbl_type;
60 l_eam_res_usage_tbl eam_process_wo_pub.eam_res_usage_tbl_type;
61 l_eam_mat_req_tbl eam_process_wo_pub.eam_mat_req_tbl_type;
62 l_eam_direct_item_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
63 l_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
64 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
65 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
66 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
67 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
68 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
69 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
70 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
71
72 l_out_eam_wo_tbl eam_process_wo_pub.eam_wo_tbl_type;
73 l_out_eam_wo_relations_tbl eam_process_wo_pub.eam_wo_relations_tbl_type;
74 l_out_eam_op_tbl eam_process_wo_pub.eam_op_tbl_type;
75 l_out_eam_op_network_tbl eam_process_wo_pub.eam_op_network_tbl_type;
76 l_out_eam_res_tbl eam_process_wo_pub.eam_res_tbl_type;
77 l_out_eam_res_inst_tbl eam_process_wo_pub.eam_res_inst_tbl_type;
78 l_out_eam_sub_res_tbl eam_process_wo_pub.eam_sub_res_tbl_type;
79 l_out_eam_res_usage_tbl eam_process_wo_pub.eam_res_usage_tbl_type;
80 l_out_eam_mat_req_tbl eam_process_wo_pub.eam_mat_req_tbl_type;
81 l_out_eam_direct_item_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
82 l_out_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
83 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
84 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
85 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
86 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
87 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
88 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
89 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
90
91 l_return_status VARCHAR2(1);
92 l_msl_count NUMBER;
93 l_message_text VARCHAR2(256);
94 l_msl_text VARCHAR2(256);
95 l_entity_index NUMBER;
96 l_entity_id VARCHAR2(100);
97 l_message_type VARCHAR2(100);
98 l_base_meter_id NUMBER;
99 l_api_name CONSTANT VARCHAR2(30) := 'convert_work_orders2';
100
101 l_module varchar2(200) ;
102 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
103 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level ;
104 l_sLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_statement >= l_log_level;
105
106
107 -- This cursor returns all necessary fields to call the WO API.modified for ib
108 --modified for performance issues
109 CURSOR c1 IS
110 SELECT fw.asset_activity_id, fw.pm_schedule_id, fw.action_type,
111 fw.wip_entity_id, fw.wo_status, fw.system_status, fw.cycle_id, fw.seq_id,
112 fw.maintenance_object_type, fw.maintenance_object_id,
113 fw.inventory_item_id, fw.eam_item_type, fw.scheduled_start_date,
114 fw.scheduled_completion_date, fw.organization_id organization_id,
115 fw.pm_base_meter_reading
116 FROM
117 (SELECT meaa.asset_activity_id, fw.pm_schedule_id, fw.action_type,
118 fw.wip_entity_id, fw.wo_status, ewsv.system_status, fw.cycle_id, fw.seq_id,
119 meaa.maintenance_object_type, meaa.maintenance_object_id,
120 msi.inventory_item_id, msi.eam_item_type, fw.scheduled_start_date,
121 fw.scheduled_completion_date, fw.organization_id organization_id,
122 fw.pm_base_meter_reading
123 from eam_forecasted_work_orders fw, mtl_eam_asset_activities meaa,
124 eam_wo_statuses_v ewsv, csi_item_instances cii, mtl_system_items_b msi
125 where PM_FORECAST_ID = l_forecast_id and
126 fw.activity_association_id = meaa.activity_association_id and
127 ewsv.status_id=fw.wo_status and meaa.maintenance_object_type = 3 and
128 meaa.maintenance_object_id = cii.instance_id and cii.inventory_item_id =
129 msi.inventory_item_id and cii.last_vld_organization_id = msi.organization_id
130 union all
131 SELECT meaa.asset_activity_id, fw.pm_schedule_id, fw.action_type,
132 fw.wip_entity_id, fw.wo_status, ewsv.system_status, fw.cycle_id, fw.seq_id,
133 meaa.maintenance_object_type, meaa.maintenance_object_id,
134 meaa.maintenance_object_id as inventory_item_id, 3 as eam_item_type, fw.scheduled_start_date,
135 fw.scheduled_completion_date, fw.organization_id organization_id,
136 fw.pm_base_meter_reading
137 from eam_forecasted_work_orders fw, mtl_eam_asset_activities meaa,
138 eam_wo_statuses_v ewsv
139 where PM_FORECAST_ID = l_forecast_id and fw.activity_association_id =
140 meaa.activity_association_id and ewsv.status_id=fw.wo_status and
141 meaa.maintenance_object_type = 2) fw
142 order by fw.cycle_id,fw.seq_id ; --added order by for bug13903329
143
144 sugg_rec c1%ROWTYPE;
145
146 -- batch id is the forecast group id (p_pm_group_id),
147 -- header id is i
148 -- These two are used in the WO wrapper API.
149 i number;
150
151 -- counter for relationship table
152 j number;
153 l_output_dir VARCHAR2(512);
154
155 l_planner_code NUMBER; -- WO Defaults ER 16094794
156
157 BEGIN
158 if (l_ulog) then
159 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
160 end if;
161 l_group_id := p_pm_group_id;
162 l_eam_wo_tbl.delete;
163 l_eam_wo_relations_tbl.delete;
164
165 l_forecast_id := current_forecasts.FIRST;
166 i := 1;
167 LOOP
168 open c1;
169 fetch c1 into sugg_rec;
170 if (c1%NOTFOUND) then
171 close c1;
172 fnd_message.set_name('EAM', 'EAM_FORECAST_DELETED');
173 raise NO_DATA_FOUND;
174 end if;
175
176 select decode(eps.RESCHEDULING_POINT,6,epr.meter_id ,null) into l_base_meter_id
177 from eam_pm_schedulings eps,eam_pm_scheduling_rules epr where
178 eps.pm_schedule_id = epr.pm_schedule_id and rownum = 1
179 and eps.pm_schedule_id = sugg_rec.pm_schedule_id;
180
181 -- Now process the suggestion and fill out the parameters for the
182 -- wrapper API call
183 if(sugg_rec.action_type = 4) then
184 -- NO ACTION
185 null;
186 else
187 -- Bug 3610484
188 l_eam_wo_tbl(i).plan_maintenance := 'Y';
189 l_eam_wo_tbl(i).user_id := fnd_global.user_id;
190 l_eam_wo_tbl(i).responsibility_id := fnd_global.resp_id;
191
192 begin
193 select description into l_eam_wo_tbl(i).description
194 from mtl_system_items_vl
195 where inventory_item_id = sugg_rec.asset_activity_id
196 and organization_id = sugg_rec.organization_id;
197
198 exception
199 WHEN no_data_found THEN
200 l_eam_wo_tbl(i).description := null;
201 WHEN OTHERS THEN
202 l_eam_wo_tbl(i).description := null;
203 END ;
204
205 if(sugg_rec.action_type IN (2,6,7)) then
206 -- Reschedule
207 l_eam_wo_tbl(i).transaction_type := eam_process_wo_pub.G_OPR_UPDATE;
208
209 l_eam_wo_tbl(i).organization_id := sugg_rec.organization_id;
210 l_eam_wo_tbl(i).wip_entity_id := sugg_rec.wip_entity_id;
211
212 if(sugg_rec.scheduled_start_date is not null) then
213 -- forward scheduling
214 l_eam_wo_tbl(i).scheduled_start_date := sugg_rec.scheduled_start_date;
215 -- dummy value here, it will be over-written by the scheduler
216 l_eam_wo_tbl(i).scheduled_completion_date := sugg_rec.scheduled_start_date;
217 l_eam_wo_tbl(i).requested_start_date := sugg_rec.scheduled_start_date;
218 l_eam_wo_tbl(i).pm_suggested_start_date := sugg_rec.scheduled_start_date;
219 else
220 -- backward scheduling
221 l_eam_wo_tbl(i).scheduled_start_date := sugg_rec.scheduled_completion_date;
222 -- dummy value here, it will be over-written by the scheduler
223 l_eam_wo_tbl(i).scheduled_completion_date := sugg_rec.scheduled_completion_date;
224 l_eam_wo_tbl(i).due_date := sugg_rec.scheduled_completion_date;
225 l_eam_wo_tbl(i).pm_suggested_end_date := sugg_rec.scheduled_completion_date;
226 end if;
227 l_eam_wo_tbl(i).status_type := sugg_rec.system_status;
228 l_eam_wo_tbl(i).user_defined_status_id := sugg_rec.wo_status;
229 l_eam_wo_tbl(i).cycle_id := sugg_rec.cycle_id;
230 l_eam_wo_tbl(i).seq_id := sugg_rec.seq_id;
231
232 elsif(sugg_rec.action_type = 3) then
233 -- Cancel
234 l_eam_wo_tbl(i).transaction_type := eam_process_wo_pub.G_OPR_UPDATE;
235
236 l_eam_wo_tbl(i).organization_id := sugg_rec.organization_id;
237 l_eam_wo_tbl(i).wip_entity_id := sugg_rec.wip_entity_id;
238 l_eam_wo_tbl(i).status_type := 7; -- cancelled
239 l_eam_wo_tbl(i).user_defined_status_id := 98; --cancelled by pm added for 12i
240 elsif(sugg_rec.action_type = 1) then
241 -- Create
242 l_eam_wo_tbl(i).transaction_type := eam_process_wo_pub.G_OPR_CREATE;
243 l_eam_wo_tbl(i).batch_id := p_pm_group_id;
244 l_eam_wo_tbl(i).header_id := i;
245 l_eam_wo_tbl(i).maintenance_object_source := 1; -- EAM
246 l_eam_wo_tbl(i).maintenance_object_type := sugg_rec.maintenance_object_type;
247 l_eam_wo_tbl(i).maintenance_object_id := sugg_rec.maintenance_object_id;
248 --l_eam_wo_tbl(i).class_code := null; -- WO API will default WAC
249 --Commented assigning Null to class_code to make the code to invoke Attribute_Defaulting instead of Populate_Null_Columns. bug# 13366445
250
251 --added by akalaval for cyclic pm
252 l_eam_wo_tbl(i).status_type := sugg_rec.system_status;
253 l_eam_wo_tbl(i).user_defined_status_id := sugg_rec.wo_status;
254 l_eam_wo_tbl(i).cycle_id := sugg_rec.cycle_id;
255 l_eam_wo_tbl(i).seq_id := sugg_rec.seq_id;
256
257 l_eam_wo_tbl(i).pm_schedule_id := sugg_rec.pm_schedule_id;
258 l_eam_wo_tbl(i).asset_activity_id := sugg_rec.asset_activity_id;
259
260
261 if(sugg_rec.scheduled_start_date is not null) then
262 -- forward scheduling
263 l_eam_wo_tbl(i).scheduled_start_date := sugg_rec.scheduled_start_date;
264 -- dummy value here, it will be over-written by the scheduler
265 l_eam_wo_tbl(i).scheduled_completion_date := sugg_rec.scheduled_start_date;
266 l_eam_wo_tbl(i).requested_start_date := sugg_rec.scheduled_start_date;
267 l_eam_wo_tbl(i).pm_suggested_start_date := sugg_rec.scheduled_start_date;
268 else
269 -- backward scheduling
270 l_eam_wo_tbl(i).scheduled_start_date := sugg_rec.scheduled_completion_date;
271 -- dummy value here, it will be over-written by the scheduler
272 l_eam_wo_tbl(i).scheduled_completion_date := sugg_rec.scheduled_completion_date;
273 l_eam_wo_tbl(i).due_date := sugg_rec.scheduled_completion_date;
274 l_eam_wo_tbl(i).pm_suggested_end_date := sugg_rec.scheduled_completion_date;
275 end if;
276 l_eam_wo_tbl(i).organization_id := sugg_rec.organization_id;
277
278 if(sugg_rec.eam_item_type = 1) then
279 -- asset
280 l_eam_wo_tbl(i).asset_group_id := sugg_rec.inventory_item_id;
281 else
282 -- rebuildable
283 l_eam_wo_tbl(i).rebuild_item_id := sugg_rec.inventory_item_id;
284 end if;
285 -- Bug 14522442
286 BEGIN
287 /*bug 13693291 ... start*/
288 SELECT FAILURE_CODE_REQUIREd INTO l_eam_wo_tbl(i).FAILURE_CODE_REQUIRED
289 FROM EAM_FAILURE_SET_ASSOCIATIONS
290 WHERE inventory_item_id = sugg_rec.inventory_item_id;
291 /*bug 13693291 ...end*/
292 EXCEPTION
293 WHEN OTHERS THEN
294 l_eam_wo_tbl(i).FAILURE_CODE_REQUIRED := NULL;
295 END;
296
297 else
298 fnd_message.set_name('EAM', 'EAM_FORECAST_DELETED');
299 raise NO_DATA_FOUND;
300 end if;
301
302 -- common fields for all operations
303 l_eam_wo_tbl(i).batch_id := p_pm_group_id;
304 l_eam_wo_tbl(i).header_id := i;
305 l_eam_wo_tbl(i).pm_base_meter_reading := sugg_rec.pm_base_meter_reading;
306 l_eam_wo_tbl(i).pm_base_meter := l_base_meter_id;
307
308
309 -- project and task
310 if(p_project_id is not null) then
311 l_eam_wo_tbl(i).project_id := p_project_id;
312 end if;
313 if(p_task_id is not null) then
314 l_eam_wo_tbl(i).task_id := p_task_id;
315 end if;
316
317 -- parent work order
318 if(p_parent_wo_id is not null) then
319 l_eam_wo_relations_tbl(i).batch_id := p_pm_group_id;
320 l_eam_wo_relations_tbl(i).PARENT_OBJECT_ID := p_parent_wo_id;
321 l_eam_wo_relations_tbl(i).PARENT_OBJECT_TYPE_ID := 1;
322 l_eam_wo_relations_tbl(i).PARENT_HEADER_ID := p_parent_wo_id;
323 l_eam_wo_relations_tbl(i).CHILD_OBJECT_ID := null;
324 l_eam_wo_relations_tbl(i).CHILD_OBJECT_TYPE_ID := 1;
325 l_eam_wo_relations_tbl(i).CHILD_HEADER_ID := i;
326
327 -- constraint child
328 l_eam_wo_relations_tbl(i).PARENT_RELATIONSHIP_TYPE :=1;
329
330 l_eam_wo_relations_tbl(i).TOP_LEVEL_OBJECT_ID := p_parent_wo_id;
331 l_eam_wo_relations_tbl(i).TOP_LEVEL_OBJECT_TYPE_ID :=1;
332 l_eam_wo_relations_tbl(i).TOP_LEVEL_HEADER_ID :=p_parent_wo_id;
333
334 l_eam_wo_relations_tbl(i).RETURN_STATUS :=null;
335 l_eam_wo_relations_tbl(i).TRANSACTION_TYPE :=EAM_PROCESS_WO_PUB.G_OPR_CREATE;
336 end if;
337
338 if(p_planner_code is not null) then -- WO Defaults ER 16094794
339 l_eam_wo_tbl(i).planner_type := p_planner_code;
340 else
341 BEGIN
342 select planner_maintenance into l_planner_code from eam_pm_schedulings
343 where pm_schedule_id = sugg_rec.pm_schedule_id;
344 l_eam_wo_tbl(i).planner_type := l_planner_code;
345 -- test_mesg('in planner code');
346 Exception
347 When NO_DATA_FOUND then
348 l_eam_wo_tbl(i).planner_type := null; -- get planner from act
349 END;
350 end if;
351
352 end if;
353 close c1;
354
355 EXIT when l_forecast_id = current_forecasts.LAST;
356 l_forecast_id := current_forecasts.NEXT(l_forecast_id);
357
358 if(sugg_rec.action_type <> 4) then
359 i := i + 1;
360 j := j + 1;
361 end if;
362
363
364 end loop;
365
366 --checking whether the previous sequences have been implemented or selected
367 if not check_previous_implements(p_pm_group_id) then
368 p_return_status := 'N' ;
369 p_msg := 'EAM_PM_PREV_NOTIMPL';
370 return;
371 end if;
372
373 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
374
375
376 eam_process_wo_pub.PROCESS_MASTER_CHILD_WO
377 ( p_bo_identifier => 'EAM'
378 , p_init_msg_list => TRUE
379 , p_api_version_number => 1.0
380 , p_eam_wo_relations_tbl => l_eam_wo_relations_tbl
381 , p_eam_wo_tbl => l_eam_wo_tbl
382
383 -- dummy parameters as these are not used in PM
384 , p_eam_op_tbl => l_eam_op_tbl
385 , p_eam_op_network_tbl => l_eam_op_network_tbl
386 , p_eam_res_tbl => l_eam_res_tbl
387 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
388 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
389 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
390 , p_eam_direct_items_tbl => l_eam_direct_item_tbl
391 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
392 , p_eam_wo_comp_tbl => l_eam_wo_comp_tbl
393 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
394 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
395 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
396 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
397 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
398 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
399 , p_eam_request_tbl => l_eam_request_tbl
400 , x_eam_direct_items_tbl => l_out_eam_direct_item_tbl
401 , x_eam_res_usage_tbl => l_eam_res_usage_tbl
402 , x_eam_wo_tbl => l_out_eam_wo_tbl
403 , x_eam_wo_relations_tbl => l_out_eam_wo_relations_tbl
404 , x_eam_op_tbl => l_out_eam_op_tbl
405 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
406 , x_eam_res_tbl => l_out_eam_res_tbl
407 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
408 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
409 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
410 , x_eam_wo_comp_tbl => l_out_eam_wo_comp_tbl
411 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
412 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
413 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
414 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
415 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
416 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
417 , x_eam_request_tbl => l_out_eam_request_tbl
418
419 -- error handling parameters
420 , p_commit => 'Y'
421 -- , x_error_msl_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.error_tbl_type
422 , x_return_status => p_return_status
423 , x_msg_count => l_msl_count
424 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
425 , p_debug_filename => 'convertwo2.log'
426 , p_output_dir => l_output_dir
427
428 );
429
430
431
432 -- This commit is for work orders and deletion of suggestions
433 commit;
434
435
436 EAM_ERROR_MESSAGE_PVT.Get_Message(l_message_text, l_entity_index, l_entity_id, l_message_type);
437
438 IF( l_slog ) THEN
439 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'Return status:' || p_return_status);
440 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'Error message:' || SUBSTRB(l_message_text,1,200));
441 END IF;
442 END convert_work_orders2;
443 procedure convert_work_orders3(p_pm_group_id number,
444 p_project_id IN NUMBER DEFAULT NULL,
445 p_task_id IN NUMBER DEFAULT NULL,
446 p_parent_wo_id IN NUMBER DEFAULT NULL,
447 p_return_status OUT NOCOPY VARCHAR2,
448 p_msg OUT NOCOPY VARCHAR2) IS
449 l_group_id NUMBER;
450 l_forecast_id NUMBER;
451 l_old_flag VARCHAR2(1);
452 l_req_id NUMBER;
453
454 -- parameters needed for the WO wrapper API call
455 l_eam_wo_tbl eam_process_wo_pub.eam_wo_tbl_type;
456 l_eam_wo_relations_tbl eam_process_wo_pub.eam_wo_relations_tbl_type;
457 l_eam_op_tbl eam_process_wo_pub.eam_op_tbl_type;
458 l_eam_op_network_tbl eam_process_wo_pub.eam_op_network_tbl_type;
459 l_eam_res_tbl eam_process_wo_pub.eam_res_tbl_type;
460 l_eam_res_inst_tbl eam_process_wo_pub.eam_res_inst_tbl_type;
461 l_eam_sub_res_tbl eam_process_wo_pub.eam_sub_res_tbl_type;
462 l_eam_res_usage_tbl eam_process_wo_pub.eam_res_usage_tbl_type;
463 l_eam_mat_req_tbl eam_process_wo_pub.eam_mat_req_tbl_type;
464 l_eam_direct_item_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
465 l_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
466 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
467 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
468 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
469 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
470 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
471 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
472 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
473
474 l_out_eam_wo_tbl eam_process_wo_pub.eam_wo_tbl_type;
475 l_out_eam_wo_relations_tbl eam_process_wo_pub.eam_wo_relations_tbl_type;
476 l_out_eam_op_tbl eam_process_wo_pub.eam_op_tbl_type;
477 l_out_eam_op_network_tbl eam_process_wo_pub.eam_op_network_tbl_type;
478 l_out_eam_res_tbl eam_process_wo_pub.eam_res_tbl_type;
479 l_out_eam_res_inst_tbl eam_process_wo_pub.eam_res_inst_tbl_type;
480 l_out_eam_sub_res_tbl eam_process_wo_pub.eam_sub_res_tbl_type;
481 l_out_eam_res_usage_tbl eam_process_wo_pub.eam_res_usage_tbl_type;
482 l_out_eam_mat_req_tbl eam_process_wo_pub.eam_mat_req_tbl_type;
483 l_out_eam_direct_item_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
484 l_out_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
485 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
486 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
487 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
488 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
489 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
490 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
491 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
492
493 l_return_status VARCHAR2(1);
494 l_msl_count NUMBER;
495 l_message_text VARCHAR2(256);
496 l_msl_text VARCHAR2(256);
497 l_entity_index NUMBER;
498 l_entity_id VARCHAR2(100);
499 l_message_type VARCHAR2(100);
500 l_org_id number;
501 l_api_name CONSTANT VARCHAR2(30) := 'convert_work_orders2';
502
503 l_module varchar2(200) ;
504 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
505 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level ;
506 l_sLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_statement >= l_log_level;
507
508
509 -- This cursor returns all necessary fields to call the WO API.modified for ib
510 --modified for performance issues
511 CURSOR c1 IS
512 SELECT meaa.asset_activity_id, fw.pm_schedule_id, fw.action_type,
513 fw.wip_entity_id, fw.wo_status, ewsv.system_status, fw.cycle_id, fw.seq_id,
514 meaa.maintenance_object_type, meaa.maintenance_object_id,
515 msi.inventory_item_id, msi.eam_item_type, fw.scheduled_start_date,
516 fw.scheduled_completion_date, fw.organization_id organization_id,
517 fw.pm_base_meter_reading
518 from eam_forecasted_work_orders fw, mtl_eam_asset_activities meaa,
519 eam_wo_statuses_v ewsv, csi_item_instances cii, mtl_system_items_b msi
520 where group_id = l_group_id and
521 fw.activity_association_id = meaa.activity_association_id and
522 ewsv.status_id=fw.wo_status and meaa.maintenance_object_type = 3 and
523 meaa.maintenance_object_id = cii.instance_id and cii.inventory_item_id =
524 msi.inventory_item_id and cii.last_vld_organization_id = msi.organization_id
525 union all
526 SELECT meaa.asset_activity_id, fw.pm_schedule_id, fw.action_type,
527 fw.wip_entity_id, fw.wo_status, ewsv.system_status, fw.cycle_id, fw.seq_id,
528 meaa.maintenance_object_type, meaa.maintenance_object_id,
529 meaa.maintenance_object_id, 3, fw.scheduled_start_date,
530 fw.scheduled_completion_date, fw.organization_id organization_id,
531 fw.pm_base_meter_reading
532 from eam_forecasted_work_orders fw, mtl_eam_asset_activities meaa,
533 eam_wo_statuses_v ewsv
534 where group_id = l_group_id and fw.activity_association_id =
535 meaa.activity_association_id and ewsv.status_id=fw.wo_status and
536 meaa.maintenance_object_type = 2 ;
537
538 sugg_rec c1%ROWTYPE;
539
540 -- batch id is the forecast group id (p_pm_group_id),
541 -- header id is i
542 -- These two are used in the WO wrapper API.
543 i number;
544
545 -- counter for relationship table
546 j number;
547 l_output_dir VARCHAR2(512);
548 BEGIN
549
550
551 if (l_ulog) then
552 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
553 end if;
554 l_group_id := p_pm_group_id;
555 l_eam_wo_tbl.delete;
556 l_eam_wo_relations_tbl.delete;
557
558 i := 1;
559
560 FOR sugg_rec in c1
561
562 LOOP
563
564 if(sugg_rec.action_type IN (2,6,7,1)) then
565
566 l_eam_wo_tbl(i).plan_maintenance := 'Y';
567 --fnd_file.put_line(FND_FILE.LOG, 'Anton: Create');
568 -- Create
569 l_eam_wo_tbl(i).transaction_type := eam_process_wo_pub.G_OPR_CREATE;
570 l_eam_wo_tbl(i).batch_id := p_pm_group_id;
571 l_eam_wo_tbl(i).header_id := i;
572 l_eam_wo_tbl(i).maintenance_object_source := 1; -- EAM
573 l_eam_wo_tbl(i).maintenance_object_type := sugg_rec.maintenance_object_type;
574 l_eam_wo_tbl(i).maintenance_object_id := sugg_rec.maintenance_object_id;
575 --l_eam_wo_tbl(i).class_code := null; -- WO API will default WAC
576 --Commented assigning Null to class_code to make the code to invoke Attribute_Defaulting instead of Populate_Null_Columns. bug# 13366445
577 -- l_eam_wo_tbl(i).status_type := 1; -- unreleased
578
579 --added by akalaval for cyclic pm
580 l_eam_wo_tbl(i).status_type := sugg_rec.system_status;
581 l_eam_wo_tbl(i).user_defined_status_id := sugg_rec.wo_status;
582 l_eam_wo_tbl(i).cycle_id := sugg_rec.cycle_id;
583 l_eam_wo_tbl(i).seq_id := sugg_rec.seq_id;
584
585 l_eam_wo_tbl(i).pm_schedule_id := sugg_rec.pm_schedule_id;
586 l_eam_wo_tbl(i).asset_activity_id := sugg_rec.asset_activity_id;
587
588
589 if(sugg_rec.scheduled_start_date is not null) then
590 -- forward scheduling
591 l_eam_wo_tbl(i).scheduled_start_date := sugg_rec.scheduled_start_date;
592 -- dummy value here, it will be over-written by the scheduler
593 l_eam_wo_tbl(i).scheduled_completion_date := sugg_rec.scheduled_start_date;
594 l_eam_wo_tbl(i).requested_start_date := sugg_rec.scheduled_start_date;
595 else
596 -- forward scheduling
597 l_eam_wo_tbl(i).scheduled_start_date := sugg_rec.scheduled_completion_date;
598 -- dummy value here, it will be over-written by the scheduler
599 l_eam_wo_tbl(i).scheduled_completion_date := sugg_rec.scheduled_completion_date;
600 l_eam_wo_tbl(i).due_date := sugg_rec.scheduled_completion_date;
601 end if;
602 l_eam_wo_tbl(i).organization_id := sugg_rec.organization_id;
603
604 if(sugg_rec.eam_item_type = 1) then
605 -- asset
606 l_eam_wo_tbl(i).asset_group_id := sugg_rec.inventory_item_id;
607 else
608 -- rebuildable
609 l_eam_wo_tbl(i).rebuild_item_id := sugg_rec.inventory_item_id;
610 end if;
611
612 -- common fields for all operations
613 l_eam_wo_tbl(i).batch_id := p_pm_group_id;
614 l_eam_wo_tbl(i).header_id := i;
615
616 -- project and task
617 if(p_project_id is not null) then
618 l_eam_wo_tbl(i).project_id := p_project_id;
619 end if;
620 if(p_task_id is not null) then
621 l_eam_wo_tbl(i).task_id := p_task_id;
622 end if;
623
624 -- parent work order
625 if(p_parent_wo_id is not null) then
626 l_eam_wo_relations_tbl(i).batch_id := p_pm_group_id;
627 l_eam_wo_relations_tbl(i).PARENT_OBJECT_ID := p_parent_wo_id;
628 l_eam_wo_relations_tbl(i).PARENT_OBJECT_TYPE_ID := 1;
629 l_eam_wo_relations_tbl(i).PARENT_HEADER_ID := p_parent_wo_id;
630 l_eam_wo_relations_tbl(i).CHILD_OBJECT_ID := null;
631 l_eam_wo_relations_tbl(i).CHILD_OBJECT_TYPE_ID := 1;
632 l_eam_wo_relations_tbl(i).CHILD_HEADER_ID := i;
633
634 -- constraint child
635 l_eam_wo_relations_tbl(i).PARENT_RELATIONSHIP_TYPE :=1;
636
637 l_eam_wo_relations_tbl(i).TOP_LEVEL_OBJECT_ID := p_parent_wo_id;
638 l_eam_wo_relations_tbl(i).TOP_LEVEL_OBJECT_TYPE_ID :=1;
639 l_eam_wo_relations_tbl(i).TOP_LEVEL_HEADER_ID :=p_parent_wo_id;
640
641 l_eam_wo_relations_tbl(i).RETURN_STATUS :=null;
642 l_eam_wo_relations_tbl(i).TRANSACTION_TYPE :=EAM_PROCESS_WO_PUB.G_OPR_CREATE;
643 end if;
644
645 i := i + 1;
646 j := j + 1;
647 end if;
648
649
650 end loop;
651
652
653 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
654
655
656
657 eam_process_wo_pub.PROCESS_MASTER_CHILD_WO
658 ( p_bo_identifier => 'EAM'
659 , p_init_msg_list => TRUE
660 , p_api_version_number => 1.0
661 , p_eam_wo_relations_tbl => l_eam_wo_relations_tbl
662 , p_eam_wo_tbl => l_eam_wo_tbl
663
664 -- dummy parameters as these are not used in PM
665 , p_eam_op_tbl => l_eam_op_tbl
666 , p_eam_op_network_tbl => l_eam_op_network_tbl
667 , p_eam_res_tbl => l_eam_res_tbl
668 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
669 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
670 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
671 , p_eam_direct_items_tbl => l_eam_direct_item_tbl
672 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
673 , p_eam_wo_comp_tbl => l_eam_wo_comp_tbl
674 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
675 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
676 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
677 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
678 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
679 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
680 , p_eam_request_tbl => l_eam_request_tbl
681 , x_eam_direct_items_tbl => l_out_eam_direct_item_tbl
682 , x_eam_res_usage_tbl => l_eam_res_usage_tbl
683 , x_eam_wo_tbl => l_out_eam_wo_tbl
684 , x_eam_wo_relations_tbl => l_out_eam_wo_relations_tbl
685 , x_eam_op_tbl => l_out_eam_op_tbl
686 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
687 , x_eam_res_tbl => l_out_eam_res_tbl
688 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
689 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
690 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
691 , x_eam_wo_comp_tbl => l_out_eam_wo_comp_tbl
692 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
693 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
694 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
695 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
696 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
697 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
698 , x_eam_request_tbl => l_out_eam_request_tbl
699
700 -- error handling parameters
701 , p_commit => 'N'
702 -- , x_error_msl_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.error_tbl_type
703 , x_return_status => p_return_status
704 , x_msg_count => l_msl_count
705 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
706 , p_debug_filename => 'convertwo2.log'
707 , p_output_dir => l_output_dir
708
709 );
710
711 EAM_ERROR_MESSAGE_PVT.Get_Message(l_message_text, l_entity_index, l_entity_id, l_message_type);
712
713 IF( l_slog ) THEN
714 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'Return status:' || p_return_status);
715 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'Error message:' || SUBSTRB(l_message_text,1,200));
716 END IF;
717 END convert_work_orders3;
718
719
720 procedure convert_work_orders(p_pm_group_id number, x_request_id OUT NOCOPY number) IS
721
722
723 l_group_id NUMBER;
724 l_forecast_id NUMBER;
725 l_old_flag VARCHAR2(1);
726 l_req_id NUMBER;
727
728 CURSOR c1 IS
729 SELECT PROCESS_FLAG
730 from eam_forecasted_work_orders
731 where PM_FORECAST_ID = l_forecast_id
732 for update of process_flag nowait;
733
734 begin
735 l_group_id := p_pm_group_id;
736 if current_forecasts.COUNT <= 0 then
737 x_request_id := -1;
738 return;
739 end if;
740
741 l_forecast_id := current_forecasts.FIRST;
742 LOOP
743 open c1;
744 fetch c1 into l_old_flag;
745 if (c1%NOTFOUND) then
746 close c1;
747 fnd_message.set_name('EAM', 'EAM_FORECAST_DELETED');
748 raise NO_DATA_FOUND;
749 end if;
750 close c1;
751
752 if l_old_flag <> 'Y' then
753 update eam_forecasted_work_orders
754 set process_flag = 'Y'
755 where pm_forecast_id = l_forecast_id;
756 else
757 raise_application_error(
758 -22222,
759 'EAM FORECAST IS ALREADY CONVERTED TO WORK ORDER');
760 end if;
761 EXIT when l_forecast_id = current_forecasts.LAST;
762 l_forecast_id := current_forecasts.NEXT(l_forecast_id);
763 END LOOP;
764
765 l_req_id := FND_REQUEST.SUBMIT_REQUEST('EAM', 'EAMCVTWO',
766 null, null, FALSE, to_char(l_group_id),
767 chr(0), null, null, null, null, null, null, null, null, null,
768 null, null, null, null, null, null, null, null, null, null,
769 null, null, null, null, null, null, null, null, null, null,
770 null, null, null, null, null, null, null, null, null, null,
771 null, null, null, null, null, null, null, null, null, null,
772 null, null, null, null, null, null, null, null, null, null,
773 null, null, null, null, null, null, null, null, null, null,
774 null, null, null, null, null, null, null, null, null, null,
775 null, null, null, null, null, null, null, null, null, null,
776 null, null, null, null, null, null, null, null, null);
777
778
779 if ( l_req_id = 0 ) then
780 raise_application_error(
781 -22223,
782 'EAM WORK ORDER CONVERTING REQUEST FAILED');
783 else
784 commit;
785 end if;
786
787 x_request_id := l_req_id;
788
789 end convert_work_orders;
790
791 -- wrapper for autonomous commit in pm scheduler
792 procedure run_pm_scheduler2(
793 p_view_non_scheduled IN varchar2,
794 p_start_date IN date,
795 p_end_date IN date,
796 p_org_id IN number,
797 p_user_id IN number,
798 p_stmt IN varchar2,
799 p_setname_id IN number,
800 p_combine_default IN varchar2,
801 p_forecast_set_id IN number,
802 p_source IN varchar2) IS
803 begin
804 eam_pm_engine.do_forecast2(
805 p_view_non_scheduled,
806 p_start_date,
807 p_end_date,
808 p_org_id,
809 p_user_id,
810 p_stmt,
811 p_setname_id,
812 p_combine_default,
813 p_forecast_set_id,
814 p_source
815 );
816 --Uncomment for deubg purpose
817 --commit;
818
819 END run_pm_scheduler2;
820
821
822 function run_pm_scheduler(
823 p_view_non_scheduled IN varchar2,
824 p_start_date IN date,
825 p_end_date IN date,
826 p_org_id IN number,
827 p_user_id IN number,
828 p_stmt IN varchar2,
829 p_setname_id IN number,
830 p_combine_default IN varchar2) return number IS
831
832 l_group_id number;
833 BEGIN
834
835 l_group_id := eam_pm_engine.do_forecast(
836 p_view_non_scheduled,
837 p_start_date,
838 p_end_date,
839 p_org_id,
840 p_user_id,
841 p_stmt,
842 p_setname_id,
843 p_combine_default
844 );
845 --Uncomment for deubg purpose
846 --commit;
847
848 return l_group_id;
849 END run_pm_scheduler;
850
851 procedure clear_forecasted_work_orders(p_group_id number) IS
852
853
854
855 BEGIN
856 delete from eam_forecasted_work_orders
857 where group_id = p_group_id and
858 process_flag = 'N';
859 commit;
860 END clear_forecasted_work_orders;
861
862 --
863 -- fore MASS RELEASE
864 --
865
866 procedure add_work_order(p_wip_entity_id number, wo_type number) IS
867 begin
868 if wo_type = 1 then
869 work_orders_not_ready(p_wip_entity_id) := p_wip_entity_id;
870 elsif wo_type = 3 then
871 work_orders_released(p_wip_entity_id) := p_wip_entity_id;
872 else
873 work_orders_unreleased(p_wip_entity_id) := p_wip_entity_id;
874 end if;
875 end add_work_order;
876
877 procedure remove_work_order(p_wip_entity_id number, wo_type number) IS
878 begin
879 if wo_type = 1 then
880 work_orders_not_ready.DELETE(p_wip_entity_id);
881 elsif wo_type = 3 then
882 work_orders_released.DELETE(p_wip_entity_id);
883 else
884 work_orders_unreleased.DELETE(p_wip_entity_id);
885 end if;
886 end remove_work_order;
887
888 procedure clear_work_orders IS
889 begin
890 work_orders_not_ready := empty_id_list;
891 work_orders_unreleased := empty_id_list;
892 end clear_work_orders;
893
894 procedure clear_released_work_orders IS
895 begin
896 work_orders_released := empty_id_list;
897 end clear_released_work_orders;
898
899
900 function get_work_order_total return number IS
901 begin
902 return work_orders_not_ready.COUNT + work_orders_unreleased.COUNT;
903 end get_work_order_total;
904
905 function get_work_order_release_total return number IS
906 begin
907 return work_orders_released.COUNT;
908 end get_work_order_release_total;
909
910 procedure complete_work_orders(p_org_id number) IS
911 PRAGMA AUTONOMOUS_TRANSACTION;
912
913 l_org_id NUMBER;
914 l_wip_entity_id NUMBER;
915
916 l_return_status VARCHAR2(1);
917 l_msg_count NUMBER;
918 l_message_text VARCHAR2(4000);
919 l_err_occured VARCHAR2(1);
920 l_scheduled_start_date date;
921 l_scheduled_completion_date date;
922 l_shutdown_type VARCHAR2(30); --Added for bug 7133506
923
924 /* added for calling WO API */
925
926 l_eam_wo_tbl EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
927 l_eam_wo_tbl_1 EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
928 l_eam_wo_relations_tbl EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
929 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
930 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
931 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
932 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
933 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
934 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
935 l_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
936 l_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
937 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
938 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
939 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
940 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
941 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
942 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
943 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
944 l_eam_res_usage_tbl eam_process_wo_pub.eam_res_usage_tbl_type;
945
946 l_eam_wo_tbl1 EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
947 l_eam_wo_relations_tbl1 EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
948 l_eam_op_tbl1 EAM_PROCESS_WO_PUB.eam_op_tbl_type;
949 l_eam_op_network_tbl1 EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
950 l_eam_res_tbl1 EAM_PROCESS_WO_PUB.eam_res_tbl_type;
951 l_eam_res_inst_tbl1 EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
952 l_eam_sub_res_tbl1 EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
953 l_eam_mat_req_tbl1 EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
954 l_out_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
955 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
956 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
957 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
958 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
959 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
960 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
961 l_eam_direct_items_tbl_1 EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
962 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
963
964 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
965
966 l_output_dir VARCHAR2(512);
967 i number;
968 j number;
969
970 l_actual_comp_date_preference varchar2(1); -- ER 14016180
971 x_start_date date;
972 x_end_date date;
973
974 begin
975
976 l_org_id := p_org_id;
977 l_return_status := FND_API.G_RET_STS_SUCCESS;
978 l_msg_count := 0;
979 l_err_occured := 'N';
980
981 i:= 1;
982 j:= 1;
983 /* get output directory path from database */
984 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
985
986 if work_orders_released.COUNT > 0 then
987 l_wip_entity_id := work_orders_released.FIRST;
988
989 l_actual_comp_date_preference := nvl(fnd_profile.value('EAM_MASS_COMPLETE_WO_AT_SYSDATE'),'1'); -- ER 14016180
990
991 LOOP
992 l_eam_wo_comp_rec.header_id := l_wip_entity_id;
993 l_eam_wo_comp_rec.batch_id := 1;
994 l_eam_wo_comp_rec.batch_id := 1;
995 l_eam_wo_comp_rec.wip_entity_id := l_wip_entity_id;
996 l_eam_wo_comp_rec.organization_id := l_org_id;
997 l_eam_wo_comp_rec.user_status_id := 4;
998
999 select scheduled_start_date,scheduled_completion_date,shutdown_type
1000 into l_scheduled_start_date,l_scheduled_completion_date,l_shutdown_type
1001 from wip_discrete_jobs where wip_entity_id = l_wip_entity_id;
1002
1003 /*start of ER 14016180*/
1004 x_start_date := l_scheduled_start_date;
1005 x_end_date := l_scheduled_completion_date;
1006
1007 if(l_actual_comp_date_preference = '2') then
1008 if l_scheduled_start_date > sysdate or l_scheduled_completion_date > sysdate then
1009 x_start_date := sysdate - (l_scheduled_completion_date - l_scheduled_start_date);
1010 x_end_date := sysdate;
1011 end if;
1012 elsif l_actual_comp_date_preference = '3' then
1013 if l_scheduled_start_date < sysdate or l_scheduled_completion_date < sysdate then
1014 x_start_date := sysdate - (l_scheduled_completion_date - l_scheduled_start_date);
1015 x_end_date := sysdate;
1016 end if;
1017 elsif l_actual_comp_date_preference = '4' then
1018 x_start_date := sysdate - (l_scheduled_completion_date - l_scheduled_start_date);
1019 x_end_date := sysdate;
1020 end if;
1021 /*end of ER 14016180*/
1022
1023 l_eam_wo_comp_rec.actual_start_date := x_start_date;
1024 l_eam_wo_comp_rec.actual_end_date := x_end_date;
1025
1026 l_eam_wo_comp_rec.transaction_type := 4;
1027
1028 --Start to add code for bug 7133506
1029 if l_shutdown_type = 2 then --if shutdown is required, need to set shutdown start and end date
1030 l_eam_wo_comp_rec.shutdown_start_date := l_eam_wo_comp_rec.actual_start_date;
1031 l_eam_wo_comp_rec.shutdown_end_date := l_eam_wo_comp_rec.actual_end_date;
1032 end if;
1033 --End add code for bug 7133506
1034
1035 l_eam_wo_comp_tbl(i) := l_eam_wo_comp_rec;
1036 i := i + 1;
1037 exit when l_wip_entity_id = work_orders_released.LAST;
1038 l_wip_entity_id := work_orders_released.NEXT(l_wip_entity_id);
1039 END LOOP;
1040
1041 EAM_PROCESS_WO_PUB.Process_Master_Child_WO
1042 ( p_bo_identifier => 'EAM'
1043 , p_init_msg_list => TRUE
1044 , p_api_version_number => 1.0
1045 , p_eam_wo_tbl => l_eam_wo_tbl
1046 , p_eam_wo_relations_tbl => l_eam_wo_relations_tbl
1047 , p_eam_op_tbl => l_eam_op_tbl
1048 , p_eam_op_network_tbl => l_eam_op_network_tbl
1049 , p_eam_res_tbl => l_eam_res_tbl
1050 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
1051 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
1052 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
1053 , p_eam_direct_items_tbl => l_eam_direct_items_tbl
1054 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
1055 , p_eam_wo_comp_tbl => l_eam_wo_comp_tbl
1056 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
1057 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
1058 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
1059 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
1060 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
1061 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
1062 , p_eam_request_tbl => l_eam_request_tbl
1063 , x_eam_wo_tbl => l_eam_wo_tbl1
1064 , x_eam_wo_relations_tbl => l_eam_wo_relations_tbl1
1065 , x_eam_op_tbl => l_eam_op_tbl1
1066 , x_eam_op_network_tbl => l_eam_op_network_tbl1
1067 , x_eam_res_tbl => l_eam_res_tbl1
1068 , x_eam_res_inst_tbl => l_eam_res_inst_tbl1
1069 , x_eam_sub_res_tbl => l_eam_sub_res_tbl1
1070 , x_eam_mat_req_tbl => l_eam_mat_req_tbl1
1071 , x_eam_direct_items_tbl => l_eam_direct_items_tbl_1
1072 , x_eam_res_usage_tbl => l_eam_res_usage_tbl
1073 , x_eam_wo_comp_tbl => l_out_eam_wo_comp_tbl
1074 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
1075 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
1076 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
1077 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
1078 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
1079 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
1080 , x_eam_request_tbl => l_out_eam_request_tbl
1081 , x_return_status => l_return_status
1082 , x_msg_count => l_msg_count
1083 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
1084 , p_debug_filename => 'completewo.log'
1085 , p_output_dir => l_output_dir
1086 , p_commit => 'Y'
1087 , p_debug_file_mode => 'W'
1088 );
1089 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1090 l_err_occured :='Y';
1091 END IF ;
1092
1093 end if;
1094
1095 IF (l_err_occured='Y' OR l_msg_count>0) THEN
1096 IF (l_msg_count>0) THEN
1097 eam_workorder_pkg.get_all_mesg(mesg=>l_message_text);
1098 END IF;
1099 fnd_message.set_name('EAM','EAM_WO_NOT_COMPLETED');
1100 fnd_message.set_token(token => 'MESG',
1101 value => l_message_text,
1102 translate => FALSE);
1103 APP_EXCEPTION.RAISE_EXCEPTION;
1104 END IF ;
1105
1106 commit;
1107
1108 end complete_work_orders;
1109
1110 procedure release_work_orders(p_group_id number,
1111 p_org_id number,
1112 p_auto_firm_flag varchar2) IS
1113 PRAGMA AUTONOMOUS_TRANSACTION;
1114
1115 l_group_id NUMBER;
1116 l_org_id NUMBER;
1117 l_wip_entity_id NUMBER;
1118 l_old_status NUMBER;
1119 l_req_id NUMBER;
1120 l_dummy NUMBER;
1121
1122 l_req_start_date DATE;
1123 l_due_date DATE;
1124 l_asset_number VARCHAR2(30);
1125 l_class_code VARCHAR2(40);
1126
1127 l_return_status VARCHAR2(1);
1128 l_msg_count NUMBER;
1129 l_message_text VARCHAR2(4000);
1130 l_err_occured VARCHAR2(1);
1131
1132 /* added for calling WO API */
1133
1134 l_eam_wo_tbl EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
1135 l_eam_wo_tbl_1 EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
1136 l_eam_wo_relations_tbl EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
1137 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
1138 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
1139 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
1140 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
1141 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
1142 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
1143 l_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
1144 l_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
1145 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
1146 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
1147 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
1148 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
1149 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
1150 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
1151 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
1152 l_eam_res_usage_tbl eam_process_wo_pub.eam_res_usage_tbl_type;
1153
1154 l_eam_wo_tbl1 EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
1155 l_eam_wo_relations_tbl1 EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
1156 l_eam_op_tbl1 EAM_PROCESS_WO_PUB.eam_op_tbl_type;
1157 l_eam_op_network_tbl1 EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
1158 l_eam_res_tbl1 EAM_PROCESS_WO_PUB.eam_res_tbl_type;
1159 l_eam_res_inst_tbl1 EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
1160 l_eam_sub_res_tbl1 EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
1161 l_eam_mat_req_tbl1 EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
1162 l_out_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
1163 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
1164 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
1165 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
1166 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
1167 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
1168 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
1169 l_eam_direct_items_tbl_1 EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
1170 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
1171
1172 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
1173 l_eam_wo_rec_1 EAM_PROCESS_WO_PUB.eam_wo_rec_type;
1174 l_output_dir VARCHAR2(512);
1175 i number;
1176 j number;
1177 begin
1178
1179 l_group_id := p_group_id;
1180 l_org_id := p_org_id;
1181 l_return_status := FND_API.G_RET_STS_SUCCESS;
1182 l_msg_count := 0;
1183 l_err_occured := 'N';
1184
1185 i:= 1;
1186 j:= 1;
1187 /* get output directory path from database */
1188 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
1189
1190 --
1191 -- UNRELEASED
1192 --
1193 if work_orders_unreleased.COUNT > 0 then
1194 l_wip_entity_id := work_orders_unreleased.FIRST;
1195 LOOP
1196 l_eam_wo_rec.header_id := l_wip_entity_id;
1197 l_eam_wo_rec.batch_id := 1;
1198 l_eam_wo_rec.wip_entity_id := l_wip_entity_id;
1199 l_eam_wo_rec.organization_id := l_org_id;
1200 l_eam_wo_rec.status_type := 3;
1201 l_eam_wo_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
1202 l_eam_wo_rec.user_id := fnd_global.user_id; /*added for bug 8408518*/
1203 l_eam_wo_rec.responsibility_id := fnd_global.resp_id; /*added for bug 8408518*/
1204 l_eam_wo_tbl(i) :=l_eam_wo_rec;
1205 i := i + 1;
1206 exit when l_wip_entity_id = work_orders_unreleased.LAST;
1207 l_wip_entity_id := work_orders_unreleased.NEXT(l_wip_entity_id);
1208 END LOOP;
1209
1210 EAM_PROCESS_WO_PUB.Process_Master_Child_WO
1211 ( p_bo_identifier => 'EAM'
1212 , p_init_msg_list => TRUE
1213 , p_api_version_number => 1.0
1214 , p_eam_wo_tbl => l_eam_wo_tbl
1215 , p_eam_wo_relations_tbl => l_eam_wo_relations_tbl
1216 , p_eam_op_tbl => l_eam_op_tbl
1217 , p_eam_op_network_tbl => l_eam_op_network_tbl
1218 , p_eam_res_tbl => l_eam_res_tbl
1219 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
1220 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
1221 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
1222 , p_eam_direct_items_tbl => l_eam_direct_items_tbl
1223 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
1224 , p_eam_wo_comp_tbl => l_eam_wo_comp_tbl
1225 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
1226 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
1227 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
1228 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
1229 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
1230 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
1231 , p_eam_request_tbl => l_eam_request_tbl
1232 , x_eam_wo_tbl => l_eam_wo_tbl1
1233 , x_eam_wo_relations_tbl => l_eam_wo_relations_tbl1
1234 , x_eam_op_tbl => l_eam_op_tbl1
1235 , x_eam_op_network_tbl => l_eam_op_network_tbl1
1236 , x_eam_res_tbl => l_eam_res_tbl1
1237 , x_eam_res_inst_tbl => l_eam_res_inst_tbl1
1238 , x_eam_sub_res_tbl => l_eam_sub_res_tbl1
1239 , x_eam_mat_req_tbl => l_eam_mat_req_tbl1
1240 , x_eam_direct_items_tbl => l_eam_direct_items_tbl_1
1241 , x_eam_res_usage_tbl => l_eam_res_usage_tbl
1242 , x_eam_wo_comp_tbl => l_out_eam_wo_comp_tbl
1243 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
1244 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
1245 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
1246 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
1247 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
1248 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
1249 , x_eam_request_tbl => l_out_eam_request_tbl
1250 , x_return_status => l_return_status
1251 , x_msg_count => l_msg_count
1252 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
1253 , p_debug_filename => 'releasewo.log'
1254 , p_output_dir => l_output_dir
1255 , p_commit => 'Y'
1256 , p_debug_file_mode => 'W'
1257 );
1258 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1259 l_err_occured :='Y';
1260 END IF ;
1261
1262 end if;
1263
1264 --
1265 -- DRAFT
1266 --
1267
1268 if work_orders_not_ready.COUNT > 0 then
1269 l_wip_entity_id := work_orders_not_ready.FIRST;
1270 LOOP
1271 l_eam_wo_rec_1.header_id := l_wip_entity_id;
1272 l_eam_wo_rec_1.batch_id := 1;
1273 l_eam_wo_rec_1.wip_entity_id := l_wip_entity_id;
1274 l_eam_wo_rec_1.organization_id := l_org_id;
1275 l_eam_wo_rec_1.status_type := 3;
1276 l_eam_wo_rec_1.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
1277 l_eam_wo_tbl_1(j) :=l_eam_wo_rec_1;
1278 j := j + 1;
1279 exit when l_wip_entity_id = work_orders_not_ready.LAST ;
1280 l_wip_entity_id := work_orders_not_ready.NEXT(l_wip_entity_id);
1281 END LOOP;
1282
1283 EAM_PROCESS_WO_PUB.Process_Master_Child_WO
1284 ( p_bo_identifier => 'EAM'
1285 , p_init_msg_list => TRUE
1286 , p_api_version_number => 1.0
1287 , p_eam_wo_tbl => l_eam_wo_tbl_1
1288 , p_eam_wo_relations_tbl => l_eam_wo_relations_tbl
1289 , p_eam_op_tbl => l_eam_op_tbl
1290 , p_eam_op_network_tbl => l_eam_op_network_tbl
1291 , p_eam_res_tbl => l_eam_res_tbl
1292 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
1293 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
1294 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
1295 , p_eam_direct_items_tbl => l_eam_direct_items_tbl
1296 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
1297 , p_eam_wo_comp_tbl => l_eam_wo_comp_tbl
1298 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
1299 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
1300 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
1301 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
1302 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
1303 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
1304 , p_eam_request_tbl => l_eam_request_tbl
1305 , x_eam_wo_tbl => l_eam_wo_tbl1
1306 , x_eam_wo_relations_tbl => l_eam_wo_relations_tbl1
1307 , x_eam_op_tbl => l_eam_op_tbl1
1308 , x_eam_op_network_tbl => l_eam_op_network_tbl1
1309 , x_eam_res_tbl => l_eam_res_tbl1
1310 , x_eam_res_inst_tbl => l_eam_res_inst_tbl1
1311 , x_eam_sub_res_tbl => l_eam_sub_res_tbl1
1312 , x_eam_mat_req_tbl => l_eam_mat_req_tbl1
1313 , x_eam_direct_items_tbl => l_eam_direct_items_tbl_1
1314 , x_eam_res_usage_tbl => l_eam_res_usage_tbl
1315 , x_eam_wo_comp_tbl => l_out_eam_wo_comp_tbl
1316 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
1317 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
1318 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
1319 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
1320 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
1321 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
1322 , x_eam_request_tbl => l_out_eam_request_tbl
1323 , x_return_status => l_return_status
1324 , x_msg_count => l_msg_count
1325 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
1326 , p_debug_filename => 'releasedrwo.log'
1327 , p_output_dir => l_output_dir
1328 , p_commit => 'Y'
1329 , p_debug_file_mode => 'A'
1330 );
1331 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1332 l_err_occured :='Y';
1333 END IF ;
1334 end if;
1335
1336 IF (l_err_occured='Y' OR l_msg_count>0) THEN
1337 IF (l_msg_count>0) THEN
1338 eam_workorder_pkg.get_all_mesg(mesg=>l_message_text);
1339 END IF;
1340 fnd_message.set_name('EAM','EAM_WO_NOT_RELEASED');
1341 fnd_message.set_token(token => 'MESG',
1342 value => l_message_text,
1343 translate => FALSE);
1344 APP_EXCEPTION.RAISE_EXCEPTION;
1345 END IF ;
1346
1347 commit;
1348
1349 end release_work_orders;
1350
1351 --function added by akalaval
1352 --function checks whether the previous sequence have been either implemented or selected
1353 --called from convert_work_orders2 before calling process master child wo
1354 FUNCTION check_previous_implements(p_pm_group_id number)
1355 return boolean is
1356
1357
1358 TYPE c_efwo_type IS REF CURSOR;
1359 c_efwo c_efwo_type;
1360
1361 i number;
1362 l_forecast_total number;
1363
1364 l_pm_schedule_id eam_forecasted_work_orders.pm_schedule_id%type;
1365 l_cycle_id eam_forecasted_work_orders.cycle_id%type;
1366 l_seq_id eam_forecasted_work_orders.seq_id%type;
1367 l_wip_entity_id eam_forecasted_work_orders.wip_entity_id%type;
1368 l_activity_association_id eam_forecasted_work_orders.activity_association_id%type;
1369
1370 l_cnt number;
1371 l_forecast_id NUMBER;
1372 l_total NUMBER;
1373 l_maint_id NUMBER;
1374 l_maint_type NUMBER;
1375 l_prev_maint_type NUMBER;
1376 l_prev_maint_id NUMBER;
1377 l_prev_act_ass_id NUMBER;
1378 l_act_ass_id NUMBER;
1379
1380 begin
1381
1382 l_total := 0;
1383 l_cnt := 0;
1384 l_maint_id := -1;
1385 l_maint_type := -1;
1386 l_act_ass_id := -1;
1387 l_prev_maint_id := -2;
1388 l_prev_maint_type := -2;
1389 l_prev_act_ass_id := -2;
1390 i:=1;
1391 l_forecast_total := eam_wb_utils.get_forecast_total;
1392
1393 for c1 in (
1394 select fwo.cycle_id,fwo.seq_id,eps.maintenance_object_id,eps.maintenance_object_type,fwo.activity_association_id
1395 from eam_forecasted_work_orders fwo,eam_pm_schedulings eps
1396 where fwo.pm_schedule_id=eps.pm_schedule_id and fwo.pm_forecast_id in (
1397 ( select * from table( cast ( current_forecasts_index as system.eam_wipid_tab_type
1398 )
1399 )
1400 ) )
1401 and action_type <>3 and action_type <> 4 order by maintenance_object_id,maintenance_object_type,fwo.activity_association_id,cycle_id desc,seq_id desc
1402 )
1403 loop
1404 l_cycle_id := c1.cycle_id;
1405 l_seq_id := c1.seq_id;
1406 l_maint_id := c1.maintenance_object_id;
1407 l_maint_type := c1.maintenance_object_type;
1408 l_act_ass_id := c1.activity_association_id ;
1409
1410 if (l_maint_id <> l_prev_maint_id or l_maint_type <> l_prev_maint_type or l_act_ass_id <> l_prev_act_ass_id ) then
1411 if l_cnt <> l_total then
1412 return false;
1413 end if;
1414 l_total := 0;
1415 l_prev_maint_id := l_maint_id;
1416 l_prev_maint_type := l_maint_type;
1417 l_prev_act_ass_id := l_act_ass_id;
1418
1419 end if;
1420
1421 if l_total = 0 then
1422 select count(1) into l_cnt from
1423 eam_forecasted_work_orders fwo,eam_pm_schedulings eps
1424 where fwo.pm_schedule_id = eps.pm_schedule_id and group_id = p_pm_group_id
1425 and (cycle_id >0 and seq_id >0 and (( cycle_id=l_cycle_id and seq_id <= l_seq_id) or cycle_id < l_cycle_id ))
1426 and action_type NOT IN(3,4)
1427 and maintenance_object_id =l_maint_id
1428 and maintenance_object_type = l_maint_type
1429 and l_act_ass_id = fwo.activity_association_id;
1430 end if;
1431 l_total := l_total+1;
1432 end loop;
1433
1434 if l_total = l_cnt then
1435 i:=1;
1436 l_forecast_id :=eam_wb_utils.current_forecasts.FIRST;
1437 while ( i <= l_forecast_total ) loop
1438
1439 delete from eam_forecasted_work_orders
1440 where PM_FORECAST_ID = l_forecast_id;
1441
1442 if i < l_forecast_total then
1443 l_forecast_id := eam_wb_utils.current_forecasts.NEXT(l_forecast_id);
1444 end if;
1445 i:=i+1;
1446 end loop;
1447 return true;
1448 else
1449 return false;
1450 end if;
1451 end;
1452
1453 FUNCTION get_owning_dept_default(
1454 p_organization_id IN number,
1455 p_maintenance_object_type IN number,
1456 p_maintenance_object_id IN number,
1457 p_rebuild_item_id IN number,
1458 p_primary_item_id IN number
1459 ) return number IS
1460
1461 l_return_status varchar2(10);
1462 l_msg_count number;
1463 l_msg_data varchar2(5000);
1464 l_owning_department_id number;
1465 BEGIN
1466 l_owning_department_id := null;
1467 Wip_eamworkorder_pvt.get_eam_owning_dept_default(
1468 p_api_version => 1.0,
1469 p_init_msg_list => FND_API.G_FALSE,
1470 p_commit => FND_API.G_FALSE,
1471 p_validation_level =>FND_API.G_VALID_LEVEL_FULL,
1472 x_return_status => l_return_status,
1473 x_msg_count => l_msg_count ,
1474 x_msg_data => l_msg_data ,
1475 p_primary_item_id => p_primary_item_id ,
1476 p_organization_id => p_organization_id,
1477 p_maintenance_object_type => p_maintenance_object_type,
1478 p_maintenance_object_id => p_maintenance_object_id,
1479 p_rebuild_item_id => p_rebuild_item_id ,
1480 x_owning_department_id => l_owning_department_id
1481 );
1482 return l_owning_department_id;
1483 END get_owning_dept_default;
1484
1485 END eam_wb_utils;
1486