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