1 PACKAGE BODY WIP_EAMWORKORDER_PVT AS
2 /* $Header: WIPVEWOB.pls 120.8.12020000.2 2013/01/30 13:38:32 srkotika ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='WIP_EAMWORKORDER_PVT';
5
6 PROCEDURE Create_EAM_Work_Order
7 ( p_api_version IN NUMBER,
8 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
9 p_commit IN VARCHAR2 := FND_API.G_FALSE,
10 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
11 x_return_status OUT NOCOPY VARCHAR2,
12 x_msg_count OUT NOCOPY NUMBER,
13 x_msg_data OUT NOCOPY VARCHAR2,
14 p_work_order_rec IN work_order_interface_rec_type,
15 x_group_id OUT NOCOPY NUMBER,
16 x_request_id OUT NOCOPY NUMBER
17 )
18 IS
19 l_api_name CONSTANT VARCHAR2(30) := 'Create_EAM_Work_Order';
20 l_api_version CONSTANT NUMBER := 1.0;
21 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
22 l_module CONSTANT VARCHAR2(60) := 'wip.plsql.'||l_full_name;
23 l_log boolean := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
24 l_request_id NUMBER := 0;
25 l_debug VARCHAR2(1);
26 l_scheduled_start_date DATE;
27 l_scheduled_completion_date DATE;
28 l_gen_object_id NUMBER ;
29 l_return_status VARCHAR2(1);
30 l_msg_count NUMBER;
31 l_message_text VARCHAR2(2000);
32 l_maintenance_object_type NUMBER;
33 l_maint_obj_fnd VARCHAR2(1);
34 l_dept_id NUMBER;
35 l_def_return_status VARCHAR2(1);
36 l_def_msg_count NUMBER;
37 l_def_msg_data VARCHAR2(1000);
38 l_maintenance_object_id NUMBER;
39 l_output_dir VARCHAR2(512);
40 l_asset_group_id NUMBER := NULL;
41 l_asset_number VARCHAR2(255) := NULL;
42 l_rebuild_item_id NUMBER := NULL;
43 l_rebuild_serial_number VARCHAR2(255) := NULL;
44 l_eam_item_type NUMBER;
45
46 /* added for calling WO API */
47
48 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
49 l_eam_op_rec EAM_PROCESS_WO_PUB.eam_op_rec_type;
50 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
51 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
52 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
53 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
54 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
55 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
56 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
57 l_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
58
59 l_out_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
60 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
61 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
62 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
63 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
64 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
65 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
66 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
67 l_out_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
68
69 BEGIN
70 if (l_log and (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
71 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Start of ' || l_module);
72 end if;
73 l_maint_obj_fnd :='N';
74 l_eam_wo_rec.asset_activity_id := p_work_order_rec.primary_item_id;
75
76 -- Standard Start of API savepoint
77 SAVEPOINT Create_EAM_Work_Order_PVT;
78 -- Standard call to check for call compatibility.
79 IF NOT FND_API.Compatible_API_Call (l_api_version,
80 p_api_version,
81 l_api_name,
82 G_PKG_NAME )
83 THEN
84 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
85 END IF;
86 -- Initialize message list if p_init_msg_list is set to TRUE.
87 IF FND_API.to_Boolean( p_init_msg_list ) THEN
88 FND_MSG_PUB.initialize;
89 END IF;
90 -- Initialize API return status to success
91 x_return_status := FND_API.G_RET_STS_SUCCESS;
92 -- API body
93
94 -- EAM Specific Validation
95 IF (NVL(p_work_order_rec.load_type,7) <> 7) THEN
96 FND_MESSAGE.SET_NAME('EAM','Incorrect Value of Load Type: '||p_work_order_rec.load_type);
97 FND_MSG_PUB.ADD();
98 RAISE FND_API.G_EXC_ERROR;
99 END IF;
100
101 /*
102 For maintenance_object_type, use the following criteria
103 1. For asset work orders and serialized rebuild work order, pass value of 3
104 2. For non-serialized rebuild work orders, pass value of 2
105 */
106
107 l_asset_group_id := p_work_order_rec.asset_group_id;
108 l_asset_number := p_work_order_rec.asset_number;
109 l_rebuild_item_id := p_work_order_rec.rebuild_item_id;
110 l_rebuild_serial_number := p_work_order_rec.rebuild_serial_number;
111
112 IF(p_work_order_rec.maintenance_object_id IS NOT NULL) THEN
113 BEGIN
114
115 SELECT msib.eam_item_type into l_eam_item_type
116 FROM
117 csi_item_instances cii, mtl_system_items_b msib, mtl_parameters mp
118 WHERE
119 msib.organization_id = mp.organization_id and
120 msib.organization_id = cii.last_vld_organization_id and
121 msib.inventory_item_id = cii.inventory_item_id and
122 msib.eam_item_type in (1,3) and
123 msib.serial_number_control_code <> 1 and
124 sysdate between nvl(cii.active_start_date, sysdate-1)
125 and nvl(cii.active_end_date, sysdate+1) and
126 mp.maint_organization_id = p_work_order_rec.organization_id and
127 cii.instance_id = p_work_order_rec.maintenance_object_id;
128
129 EXCEPTION
130 WHEN NO_DATA_FOUND THEN
131 NULL;
132 END;
133
134 END IF;
135
136 IF (p_work_order_rec.maintenance_object_id IS NOT NULL AND
137 p_work_order_rec.maintenance_object_type = 3) THEN
138 BEGIN
139 IF(l_eam_item_type = 1) THEN
140 SELECT cii.inventory_item_id,
141 cii.serial_number
142 INTO
143 l_asset_group_id,
144 l_asset_number
145 FROM
146 csi_item_instances cii
147 WHERE
148 cii.instance_id = p_work_order_rec.maintenance_object_id;
149 l_maintenance_object_type := 3;
150 l_gen_object_id := p_work_order_rec.maintenance_object_id;
151 ELSIF (l_eam_item_type = 3) THEN
152 SELECT cii.inventory_item_id,
153 cii.serial_number
154 INTO
155 l_rebuild_item_id,
156 l_rebuild_serial_number
157 FROM
158 csi_item_instances cii
159 WHERE
160 cii.instance_id = p_work_order_rec.maintenance_object_id;
161 l_maintenance_object_type := 3;
162 l_gen_object_id := p_work_order_rec.maintenance_object_id;
163 END IF;
164 l_maint_obj_fnd :='Y';
165
166 EXCEPTION
167 WHEN NO_DATA_FOUND THEN
168 NULL;
169 END;
170 END IF;
171
172 IF (l_maint_obj_fnd<>'Y' AND p_work_order_rec.asset_group_id IS NOT NULL AND p_work_order_rec.asset_number IS NOT NULL ) THEN
173 BEGIN
174
175 SELECT instance_id into l_gen_object_id
176 FROM csi_item_instances
177 WHERE inventory_item_id = p_work_order_rec.asset_group_id and serial_number = p_work_order_rec.asset_number and last_vld_organization_id =p_work_order_rec.organization_id ;
178 l_maintenance_object_type := 3;
179 l_maintenance_object_id := l_gen_object_id;
180 l_maint_obj_fnd :='Y';
181 EXCEPTION
182 WHEN NO_DATA_FOUND THEN
183 NULL;
184 END;
185 END IF;
186
187 IF (l_maint_obj_fnd<>'Y' AND p_work_order_rec.rebuild_item_id IS NOT NULL
188 AND p_work_order_rec.rebuild_serial_number IS NOT NULL ) THEN
189 BEGIN
190 select instance_id into l_gen_object_id
191 from csi_item_instances
192 where inventory_item_id = p_work_order_rec.rebuild_item_id and serial_number = p_work_order_rec.rebuild_serial_number and last_vld_organization_id =p_work_order_rec.organization_id;
193 l_maintenance_object_type := 3;
194 l_maintenance_object_id := l_gen_object_id;
195 l_maint_obj_fnd :='Y';
196 EXCEPTION
197 WHEN NO_DATA_FOUND THEN
198 NULL;
199 END;
200 END IF ;
201
202 IF (l_maint_obj_fnd<>'Y' AND p_work_order_rec.rebuild_item_id IS NOT NULL) THEN
203 BEGIN
204 SELECT inventory_item_id into l_gen_object_id
205 FROM mtl_system_items
206 WHERE inventory_item_id = p_work_order_rec.rebuild_item_id and organization_id =p_work_order_rec.organization_id;
207 l_maintenance_object_type := 2;
208 l_maintenance_object_id := l_gen_object_id;
209 EXCEPTION
210 WHEN NO_DATA_FOUND THEN
211 NULL;
212 END;
213 END IF;
214
215 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
216 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
217 'l_gen_object_id: ' || l_gen_object_id);
218 end if;
219 l_eam_wo_rec.status_type := p_work_order_rec.status_type;
220
221 /* get output directory path from database */
222 log_path(l_output_dir);
223
224 l_eam_wo_rec.header_id := null;
225 l_eam_wo_rec.batch_id := null;
226 l_eam_wo_rec.row_id := null;
227 l_eam_wo_rec.wip_entity_name := null ;
228 l_eam_wo_rec.wip_entity_id := null;
229 l_eam_wo_rec.organization_id := p_work_order_rec.organization_id;
230 l_eam_wo_rec.description := null;
231 l_eam_wo_rec.asset_number := l_asset_number;
232 l_eam_wo_rec.asset_group_id := l_asset_group_id;
233 l_eam_wo_rec.rebuild_item_id := l_rebuild_item_id;
234 l_eam_wo_rec.rebuild_serial_number := l_rebuild_serial_number;
235 l_eam_wo_rec.maintenance_object_id := l_gen_object_id;
236 l_eam_wo_rec.maintenance_object_type := l_maintenance_object_type;
237 l_eam_wo_rec.maintenance_object_source := 1;
238 l_eam_wo_rec.class_code := p_work_order_rec.class_code;
239 l_eam_wo_rec.activity_type := p_work_order_rec.activity_type;
240 l_eam_wo_rec.activity_cause :=p_work_order_rec.activity_cause;
241 l_eam_wo_rec.activity_source := p_work_order_rec.activity_source;
242 l_eam_wo_rec.work_order_type := p_work_order_rec.work_order_type;
243
244 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
245 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module, 'Fetching default owning dept');
246 end if;
247 IF p_work_order_rec.owning_department is not null THEN
248 l_dept_id := p_work_order_rec.owning_department;
249 ELSE if p_work_order_rec.owning_department_code is not null THEN
250 SELECT department_id into l_dept_id
251 FROM bom_departments
252 WHERE department_code = p_work_order_rec.owning_department_code and organization_id= p_work_order_rec.organization_id;
253 ELSE
254 WIP_EAMWORKORDER_PVT.Get_EAM_Owning_Dept_Default
255 ( p_api_version => 1.0,
256 p_init_msg_list => FND_API.G_FALSE,
257 p_commit => FND_API.G_FALSE,
258 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
259 x_return_status => l_def_return_status,
260 x_msg_count => l_def_msg_count,
261 x_msg_data => l_def_msg_data,
262 p_primary_item_id => l_eam_wo_rec.asset_activity_id,
263 p_organization_id => l_eam_wo_rec.organization_id,
264 p_maintenance_object_type => l_maintenance_object_type,
265 p_maintenance_object_id => l_maintenance_object_id,
266 p_rebuild_item_id => l_eam_wo_rec.rebuild_item_id,
267 x_owning_department_id => l_dept_id
268 );
269 END IF ;
270 END IF ;
271
272 /* If the Department of Work Order cannot be set then change the status of Work Order to Unrelease */
273 IF l_dept_id is null and p_work_order_rec.status_type not in (1,6,17) THEN
274 l_eam_wo_rec.status_type:=1;
275 END IF ;
276 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
277 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module, 'default owning dept id:'
278 || l_dept_id);
279 end if;
280
281 l_eam_wo_rec.job_quantity := null;
282 l_eam_wo_rec.date_released := p_work_order_rec.date_released;
283 l_eam_wo_rec.owning_department := l_dept_id;
284 l_eam_wo_rec.priority := p_work_order_rec.priority;
285 l_eam_wo_rec.requested_start_date := p_work_order_rec.requested_start_date;
286 l_eam_wo_rec.due_date := p_work_order_rec.due_date;
287 l_eam_wo_rec.shutdown_type := p_work_order_rec.shutdown_type;
288 l_eam_wo_rec.firm_planned_flag := p_work_order_rec.firm_planned_flag;
289 l_eam_wo_rec.notification_required := p_work_order_rec.notification_required;
290 l_eam_wo_rec.tagout_required := p_work_order_rec.tagout_required;
291 l_eam_wo_rec.plan_maintenance := p_work_order_rec.plan_maintenance;
292 l_eam_wo_rec.project_id := p_work_order_rec.project_id;
293 l_eam_wo_rec.task_id := p_work_order_rec.task_id;
294 --project_costed
295 l_eam_wo_rec.end_item_unit_number := p_work_order_rec.end_item_unit_number;
296 l_eam_wo_rec.schedule_group_id := p_work_order_rec.schedule_group_id;
297 l_eam_wo_rec.bom_revision_date := p_work_order_rec.bom_revision_date;
298 l_eam_wo_rec.routing_revision_date := p_work_order_rec.routing_revision_date;
299 l_eam_wo_rec.alternate_routing_designator := p_work_order_rec.alternate_routing_designator;
300 l_eam_wo_rec.alternate_bom_designator := p_work_order_rec.alternate_bom_designator;
301 l_eam_wo_rec.routing_revision := p_work_order_rec.routing_revision;
302 l_eam_wo_rec.bom_revision := p_work_order_rec.bom_revision;
303 l_eam_wo_rec.parent_wip_entity_id := p_work_order_rec.parent_wip_entity_id;
304 l_eam_wo_rec.manual_rebuild_flag := p_work_order_rec.manual_rebuild_flag;
305 l_eam_wo_rec.pm_schedule_id := p_work_order_rec.pm_schedule_id;
306 l_eam_wo_rec.wip_supply_type := p_work_order_rec.wip_supply_type;
307 l_eam_wo_rec.material_account := null;
308 l_eam_wo_rec.material_overhead_account := null ;
309 l_eam_wo_rec.resource_account := null;
310 l_eam_wo_rec.outside_processing_account := null;
311 l_eam_wo_rec.material_variance_account := null;
312 l_eam_wo_rec.resource_variance_account := null;
313 l_eam_wo_rec.outside_proc_variance_account := null;
314 l_eam_wo_rec.std_cost_adjustment_account := null;
315 l_eam_wo_rec.overhead_account := null;
316 l_eam_wo_rec.overhead_variance_account := null;
317 l_eam_wo_rec.scheduled_start_date := sysdate;
318 l_eam_wo_rec.scheduled_completion_date := sysdate;
319 l_eam_wo_rec.common_bom_sequence_id := null;
320 l_eam_wo_rec.common_routing_sequence_id := null;
321 l_eam_wo_rec.po_creation_time := null;
322 l_eam_wo_rec.gen_object_id := l_gen_object_id;
323 l_eam_wo_rec.attribute_category := p_work_order_rec.attribute_category;
324 l_eam_wo_rec.attribute1 := p_work_order_rec.attribute1;
325 l_eam_wo_rec.attribute2 := p_work_order_rec.attribute2;
326 l_eam_wo_rec.attribute3 := p_work_order_rec.attribute3;
327 l_eam_wo_rec.attribute4 := p_work_order_rec.attribute4;
328 l_eam_wo_rec.attribute5 := p_work_order_rec.attribute5;
329 l_eam_wo_rec.attribute6 := p_work_order_rec.attribute6;
330 l_eam_wo_rec.attribute7 := p_work_order_rec.attribute7;
331 l_eam_wo_rec.attribute8 := p_work_order_rec.attribute8;
332 l_eam_wo_rec.attribute9 := p_work_order_rec.attribute9;
333 l_eam_wo_rec.attribute10 := p_work_order_rec.attribute10;
334 l_eam_wo_rec.attribute11 := p_work_order_rec.attribute11;
335 l_eam_wo_rec.attribute12 := p_work_order_rec.attribute12;
336 l_eam_wo_rec.attribute13 := p_work_order_rec.attribute13;
337 l_eam_wo_rec.attribute14 := p_work_order_rec.attribute14;
338 l_eam_wo_rec.attribute15 := p_work_order_rec.attribute15;
339 l_eam_wo_rec.material_issue_by_mo := null;
340 l_eam_wo_rec.issue_zero_cost_flag := null;
341 l_eam_wo_rec.user_id := fnd_global.user_id;
342 l_eam_wo_rec.responsibility_id := fnd_global.resp_id;
343 l_eam_wo_rec.request_id := p_work_order_rec.request_id;
344 l_eam_wo_rec.program_id := p_work_order_rec.program_id;
345 l_eam_wo_rec.program_application_id := p_work_order_rec.program_application_id;
346 l_eam_wo_rec.source_line_id := p_work_order_rec.source_line_id;
347 l_eam_wo_rec.source_code := p_work_order_rec.source_code;
348 -- l_eam_wo_rec.return_status :=
349 l_eam_wo_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_CREATE;
350
351 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
352 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module, 'Calling work order API');
353 end if;
354 EAM_PROCESS_WO_PUB.Process_WO
355 ( p_bo_identifier => 'EAM'
356 , p_init_msg_list => TRUE
357 , p_api_version_number => 1.0
358 , p_commit => 'N'
359 , p_eam_wo_rec => l_eam_wo_rec
360 , p_eam_op_tbl => l_eam_op_tbl
361 , p_eam_op_network_tbl => l_eam_op_network_tbl
362 , p_eam_res_tbl => l_eam_res_tbl
363 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
364 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
365 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
366 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
367 , p_eam_direct_items_tbl => l_eam_di_tbl
368 , x_eam_wo_rec => l_out_eam_wo_rec
369 , x_eam_op_tbl => l_out_eam_op_tbl
370 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
371 , x_eam_res_tbl => l_out_eam_res_tbl
372 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
373 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
374 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
375 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
376 , x_eam_direct_items_tbl => l_out_eam_di_tbl
377 , x_return_status => l_return_status
378 , x_msg_count => l_msg_count
379 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
380 , p_debug_filename => 'wipvewob.log'
381 , p_output_dir => l_output_dir
382 , p_debug_file_mode => 'W'
383 );
384 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
385 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
386 'work order API returned status: '|| l_return_status);
387 end if;
388
389 if(l_return_status <> FND_API.G_RET_STS_SUCCESS) then
390 fnd_message.set_name('EAM','EAM_CANNOT_CREAT_WRK');
391 fnd_msg_pub.add();
392 RAISE FND_API.G_EXC_ERROR;
393 end if ;
394
395 IF FND_API.To_Boolean( p_commit ) THEN
396 COMMIT WORK;
397 END IF;
398
399 -- End of API body.
400 -- Standard call to get message count and if count is 1, get message info.
401 FND_MSG_PUB.Count_And_Get
402 (p_count => x_msg_count,
403 p_data => x_msg_data
404 );
405 EXCEPTION
406 WHEN FND_API.G_EXC_ERROR THEN
407 ROLLBACK TO Create_EAM_Work_Order_PVT;
408 x_return_status := FND_API.G_RET_STS_ERROR ;
409 x_request_id := l_request_id;
410 FND_MSG_PUB.Count_And_Get
411 (p_count => x_msg_count,
412 p_data => x_msg_data
413 );
414 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
415 ROLLBACK TO Create_EAM_Work_Order_PVT;
416 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
417 x_request_id := l_request_id;
418 FND_MSG_PUB.Count_And_Get
419 (p_count => x_msg_count,
420 p_data => x_msg_data
421 );
422 WHEN OTHERS THEN
423 ROLLBACK TO Create_EAM_Work_Order_PVT;
424 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
425 x_request_id := l_request_id;
426 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
427 THEN
428 FND_MSG_PUB.Add_Exc_Msg
429 (G_PKG_NAME,
430 l_api_name
431 );
432 END IF;
433 FND_MSG_PUB.Count_And_Get
434 (p_count => x_msg_count,
435 p_data => x_msg_data
436 );
437 END Create_EAM_Work_Order;
438
439
440 /********************************************************************/
441 -- API to obtain eAM Mass Load Defaults
442 /********************************************************************/
443
444
445 PROCEDURE Get_EAM_Act_Cause_Default
446 ( p_api_version IN NUMBER,
447 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
448 p_commit IN VARCHAR2 := FND_API.G_FALSE,
449 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
450 x_return_status OUT NOCOPY VARCHAR2,
451 x_msg_count OUT NOCOPY NUMBER,
452 x_msg_data OUT NOCOPY VARCHAR2,
453 p_primary_item_id IN NUMBER,
454 p_organization_id IN NUMBER,
455 p_maintenance_object_type IN NUMBER,
456 p_maintenance_object_id IN NUMBER,
457 p_rebuild_item_id IN NUMBER,
458 x_activity_cause_code OUT NOCOPY NUMBER
459 )
460 IS
461 l_api_name CONSTANT VARCHAR2(30) := 'Get_EAM_Act_Cause_Default';
462 l_api_version CONSTANT NUMBER := 1.0;
463
464
465 BEGIN
466 -- Standard Start of API savepoint
467 SAVEPOINT Get_EAM_Act_Cause_Default_PVT;
468 -- Standard call to check for call compatibility.
469 IF NOT FND_API.Compatible_API_Call (l_api_version,
470 p_api_version,
471 l_api_name,
472 G_PKG_NAME )
473 THEN
474 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
475 END IF;
476 -- Initialize message list if p_init_msg_list is set to TRUE.
477 IF FND_API.to_Boolean( p_init_msg_list ) THEN
478 FND_MSG_PUB.initialize;
479 END IF;
480 -- Initialize API return status to success
481 x_return_status := FND_API.G_RET_STS_SUCCESS;
482 -- API body
483
484 -- Find the Activity Cause from the Association Table
485 IF (p_maintenance_object_type IN (1,2) ) THEN -- 'MSN', 'MSI'
486 BEGIN
487 SELECT MEAA.activity_cause_code
488 INTO x_activity_cause_code
489 FROM MTL_EAM_ASSET_ACTIVITIES MEAA
490 WHERE MEAA.organization_id = p_organization_id
491 AND MEAA.asset_activity_id = p_primary_item_id
492 AND MEAA.maintenance_object_id = p_maintenance_object_id
493 AND MEAA.maintenance_object_type = p_maintenance_object_type
494 AND NVL(MEAA.tmpl_flag,'N') = 'N';
495 EXCEPTION
496 WHEN OTHERS THEN
497 x_activity_cause_code := NULL;
498 END;
499 END IF;
500
501 IF (p_maintenance_object_type = 3) THEN -- 'CII'
502 BEGIN
503 --Begin bug fix 7343758
504 /*SELECT MEAA.activity_cause_code
505 INTO x_activity_cause_code
506 FROM MTL_EAM_ASSET_ACTIVITIES MEAA
507 WHERE MEAA.organization_id = p_organization_id
508 AND MEAA.asset_activity_id = p_primary_item_id
509 AND MEAA.maintenance_object_id = p_rebuild_item_id
510 AND MEAA.maintenance_object_type = 2 ;-- 'MSI'*/
511 SELECT eomd.activity_cause_code
512 INTO x_activity_cause_code
513 FROM eam_org_maint_defaults eomd, mtl_eam_asset_activities MEAA
514 WHERE eomd.organization_id=p_organization_id
515 AND eomd.object_type = 60
516 AND eomd.object_id = meaa.activity_association_id
517 AND MEAA.asset_activity_id = p_primary_item_id
518 AND MEAA.maintenance_object_id = p_maintenance_object_id
519 AND MEAA.maintenance_object_type = 3 ;
520 --End bug fix 7343758
521 EXCEPTION
522 WHEN OTHERS THEN
523 x_activity_cause_code := NULL;
524 END;
525
526 END IF;
527
528 -- Find the Activity Cause from the MSI or MSN Tables based on maintenance_object_type
529
530 IF (x_activity_cause_code IS NULL) THEN
531 BEGIN
532 SELECT MSI.eam_activity_cause_code
533 INTO x_activity_cause_code
534 FROM MTL_SYSTEM_ITEMS MSI
535 WHERE MSI.inventory_item_id = p_primary_item_id
536 AND MSI.organization_id = p_organization_id;
537 EXCEPTION
538 WHEN OTHERS THEN
539 x_activity_cause_code := NULL;
540 END;
541
542 END IF;
543
544
545 -- End of API body.
546 -- Standard check of p_commit.
547 IF FND_API.To_Boolean( p_commit ) THEN
548 COMMIT WORK;
549 END IF;
550 -- Standard call to get message count and if count is 1, get message info.
551 FND_MSG_PUB.Count_And_Get
552 (p_count => x_msg_count,
553 p_data => x_msg_data
554 );
555 EXCEPTION
556 WHEN FND_API.G_EXC_ERROR THEN
557 ROLLBACK TO Get_EAM_Act_Cause_Default_PVT;
558 x_return_status := FND_API.G_RET_STS_ERROR ;
559
560 FND_MSG_PUB.Count_And_Get
561 (p_count => x_msg_count,
562 p_data => x_msg_data
563 );
564 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
565 ROLLBACK TO Get_EAM_Act_Cause_Default_PVT;
566 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
567
568 FND_MSG_PUB.Count_And_Get
569 (p_count => x_msg_count,
570 p_data => x_msg_data
571 );
572 WHEN OTHERS THEN
573 ROLLBACK TO Get_EAM_Act_Cause_Default_PVT;
574 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
575
576 IF FND_MSG_PUB.Check_Msg_Level
577 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
578 THEN
579 FND_MSG_PUB.Add_Exc_Msg
580 (G_PKG_NAME,
581 l_api_name
582 );
583 END IF;
584 FND_MSG_PUB.Count_And_Get
585 (p_count => x_msg_count,
586 p_data => x_msg_data
587 );
588 END Get_EAM_Act_Cause_Default;
589
590 /*Procedure to find the log directory path to write debug messages for EAM workorder API*/
591
592 PROCEDURE log_path(
593 x_output_dir OUT NOCOPY VARCHAR2
594 )
595 IS
596 l_full_path VARCHAR2(512);
597 l_new_full_path VARCHAR2(512);
598 l_file_dir VARCHAR2(512);
599
600 fileHandler UTL_FILE.FILE_TYPE;
601 fileName VARCHAR2(50);
602
603 l_flag NUMBER;
604 BEGIN
605 fileName:='test.log';--this is only a dummy filename to check if directory is valid or not
606
607 /* get output directory path from database */
608 SELECT value
609 INTO l_full_path
610 FROM v$parameter
611 WHERE name = 'utl_file_dir';
612
613 l_flag := 0;
614 --l_full_path contains a list of comma-separated directories
615 WHILE(TRUE)
616 LOOP
617 --get the first dir in the list
618 SELECT trim(substr(l_full_path, 1, decode(instr(l_full_path,',')-1,
619 -1, length(l_full_path),
620 instr(l_full_path, ',')-1
621 )
622 )
623 )
624 INTO l_file_dir
625 FROM dual;
626
627 -- check if the dir is valid
628 BEGIN
629 fileHandler := UTL_FILE.FOPEN(l_file_dir , filename, 'w');
630 l_flag := 1;
631 EXCEPTION
632 WHEN utl_file.invalid_path THEN
633 l_flag := 0;
634 WHEN utl_file.invalid_operation THEN
635 l_flag := 0;
636 END;
637
638 IF l_flag = 1 THEN --got a valid directory
639 utl_file.fclose(fileHandler);
640 EXIT;
641 END IF;
642
643 --earlier found dir was not a valid dir,
644 --so remove that from the list, and get the new list
645 l_new_full_path := trim(substr(l_full_path, instr(l_full_path, ',')+1, length(l_full_path)));
646
647 --if the new list has not changed, there are no more valid dirs left
648 IF l_full_path = l_new_full_path THEN
649 l_flag:=0;
650 EXIT;
651 END IF;
652 l_full_path := l_new_full_path;
653 END LOOP;
654
655 IF(l_flag=1) THEN --found a valid directory
656 x_output_dir := l_file_dir;
657 ELSE
658 x_output_dir:= null;
659
660 END IF;
661 EXCEPTION
662 WHEN OTHERS THEN
663 x_output_dir := null;
664
665 END log_path;
666
667 /********************************************************************/
668 -- API to obtain eAM Mass Load Defaults
669 /********************************************************************/
670
671
672 PROCEDURE Get_EAM_Act_Type_Default
673 ( p_api_version IN NUMBER,
674 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
675 p_commit IN VARCHAR2 := FND_API.G_FALSE,
676 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
677 x_return_status OUT NOCOPY VARCHAR2,
678 x_msg_count OUT NOCOPY NUMBER,
679 x_msg_data OUT NOCOPY VARCHAR2,
680 p_primary_item_id IN NUMBER,
681 p_organization_id IN NUMBER,
682 p_maintenance_object_type IN NUMBER,
683 p_maintenance_object_id IN NUMBER,
684 p_rebuild_item_id IN NUMBER,
685 x_activity_type_code OUT NOCOPY NUMBER
686 )
687 IS
688 l_api_name CONSTANT VARCHAR2(30) := 'Get_EAM_Act_Type_Default';
689 l_api_version CONSTANT NUMBER := 1.0;
690
691
692 BEGIN
693 -- Standard Start of API savepoint
694 SAVEPOINT Get_EAM_Act_Type_Default_PVT;
695 -- Standard call to check for call compatibility.
696 IF NOT FND_API.Compatible_API_Call (l_api_version,
697 p_api_version,
698 l_api_name,
699 G_PKG_NAME)
700 THEN
701 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
702 END IF;
703 -- Initialize message list if p_init_msg_list is set to TRUE.
704 IF FND_API.to_Boolean( p_init_msg_list ) THEN
705 FND_MSG_PUB.initialize;
706 END IF;
707 -- Initialize API return status to success
708 x_return_status := FND_API.G_RET_STS_SUCCESS;
709 -- API body
710
711 -- Find the Activity type from the Association Table
712 IF (p_maintenance_object_type IN (1,2) ) THEN -- 'MSN', 'MSI'
713 BEGIN
714 SELECT MEAA.activity_type_code
715 INTO x_activity_type_code
716 FROM MTL_EAM_ASSET_ACTIVITIES MEAA
717 WHERE MEAA.organization_id = p_organization_id
718 AND MEAA.asset_activity_id = p_primary_item_id
719 AND MEAA.maintenance_object_id = p_maintenance_object_id
720 AND MEAA.maintenance_object_type = p_maintenance_object_type
721 AND NVL(MEAA.tmpl_flag,'N') = 'N';
722 EXCEPTION
723 WHEN OTHERS THEN
724 x_activity_type_code := NULL;
725 END;
726 END IF;
727
728 IF (p_maintenance_object_type = 3) THEN -- 'CII'
729 BEGIN
730 --Begin bug fix 7343758
731 /*SELECT MEAA.activity_type_code
732 INTO x_activity_type_code
733 FROM MTL_EAM_ASSET_ACTIVITIES MEAA
734 WHERE MEAA.organization_id = p_organization_id
735 AND MEAA.asset_activity_id = p_primary_item_id
736 AND MEAA.maintenance_object_id = p_rebuild_item_id
737 AND MEAA.maintenance_object_type = 2 ;-- 'MSI'*/
738 SELECT eomd.activity_type_code
739 INTO x_activity_type_code
740 FROM eam_org_maint_defaults eomd, mtl_eam_asset_activities MEAA
741 WHERE eomd.organization_id=p_organization_id
742 AND eomd.object_type = 60
743 AND eomd.object_id = meaa.activity_association_id
744 AND MEAA.asset_activity_id = p_primary_item_id
745 AND MEAA.maintenance_object_id = p_maintenance_object_id
746 AND MEAA.maintenance_object_type = 3 ;
747 --End bug fix 7343758
748 EXCEPTION
749 WHEN OTHERS THEN
750 x_activity_type_code := NULL;
751 END;
752
753 END IF;
754
755 -- Find the Activity type from the MSI or MSN Tables based on maintenance_object_type
756
757 IF (x_activity_type_code IS NULL) THEN
758 BEGIN
759 SELECT MSI.eam_activity_type_code
760 INTO x_activity_type_code
761 FROM MTL_SYSTEM_ITEMS MSI
762 WHERE MSI.inventory_item_id = p_primary_item_id
763 AND MSI.organization_id = p_organization_id;
764 EXCEPTION
765 WHEN OTHERS THEN
766 x_activity_type_code := NULL;
767 END;
768
769 END IF;
770
771 -- End of API body.
772 -- Standard check of p_commit.
773 IF FND_API.To_Boolean( p_commit ) THEN
774 COMMIT WORK;
775 END IF;
776 -- Standard call to get message count and if count is 1, get message info.
777 FND_MSG_PUB.Count_And_Get
778 (p_count => x_msg_count,
779 p_data => x_msg_data
780 );
781 EXCEPTION
782 WHEN FND_API.G_EXC_ERROR THEN
783 ROLLBACK TO Get_EAM_Act_Type_Default_PVT;
784 x_return_status := FND_API.G_RET_STS_ERROR ;
785
786 FND_MSG_PUB.Count_And_Get
787 (p_count => x_msg_count,
788 p_data => x_msg_data
789 );
790 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
791 ROLLBACK TO Get_EAM_Act_Type_Default_PVT;
792 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
793
794 FND_MSG_PUB.Count_And_Get
795 (p_count => x_msg_count,
796 p_data => x_msg_data
797 );
798 WHEN OTHERS THEN
799 ROLLBACK TO Get_EAM_Act_Type_Default_PVT;
800 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
801
802 IF FND_MSG_PUB.Check_Msg_Level
803 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
804 THEN
805 FND_MSG_PUB.Add_Exc_Msg
806 (G_PKG_NAME,
807 l_api_name
808 );
809 END IF;
810 FND_MSG_PUB.Count_And_Get
811 (p_count => x_msg_count,
812 p_data => x_msg_data
813 );
814 END Get_EAM_Act_type_Default;
815
816
817
818 /********************************************************************/
819 -- API to obtain eAM Mass Load Defaults
820 /********************************************************************/
821
822
823 PROCEDURE Get_EAM_Act_Source_Default
824 ( p_api_version IN NUMBER,
825 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
826 p_commit IN VARCHAR2 := FND_API.G_FALSE,
827 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
828 x_return_status OUT NOCOPY VARCHAR2,
829 x_msg_count OUT NOCOPY NUMBER,
830 x_msg_data OUT NOCOPY VARCHAR2,
831 p_primary_item_id IN NUMBER,
832 p_organization_id IN NUMBER,
833 p_maintenance_object_type IN NUMBER,
834 p_maintenance_object_id IN NUMBER,
835 p_rebuild_item_id IN NUMBER,
836 x_activity_Source_code OUT NOCOPY NUMBER
837 )
838 IS
839 l_api_name CONSTANT VARCHAR2(30) := 'Get_EAM_Act_Source_Default';
840 l_api_version CONSTANT NUMBER := 1.0;
841
842
843 BEGIN
844 -- Standard Start of API savepoint
845 SAVEPOINT Get_EAM_Act_Source_Default_PVT;
846 -- Standard call to check for call compatibility.
847 IF NOT FND_API.Compatible_API_Call (l_api_version,
848 p_api_version,
849 l_api_name,
850 G_PKG_NAME)
851 THEN
852 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
853 END IF;
854 -- Initialize message list if p_init_msg_list is set to TRUE.
855 IF FND_API.to_Boolean( p_init_msg_list ) THEN
856 FND_MSG_PUB.initialize;
857 END IF;
858 -- Initialize API return status to success
859 x_return_status := FND_API.G_RET_STS_SUCCESS;
860 -- API body
861
862 -- Find the Activity Source from the Association Table
863 IF (p_maintenance_object_type IN (1, 2) ) THEN -- 'MSN', 'MSI'
864 BEGIN
865 SELECT MEAA.activity_source_code
866 INTO x_activity_source_code
867 FROM MTL_EAM_ASSET_ACTIVITIES MEAA
868 WHERE MEAA.organization_id = p_organization_id
869 AND MEAA.asset_activity_id = p_primary_item_id
870 AND MEAA.maintenance_object_id = p_maintenance_object_id
871 AND MEAA.maintenance_object_type = p_maintenance_object_type
872 AND NVL(MEAA.tmpl_flag,'N') = 'N';
873 EXCEPTION
874 WHEN OTHERS THEN
875 x_activity_source_code := NULL;
876 END;
877 END IF;
878
879 IF (p_maintenance_object_type = 3) THEN -- 'CII'
880 BEGIN
881 --Begin bug fix 7343758
882 /* SELECT MEAA.activity_source_code
883 INTO x_activity_source_code
884 FROM MTL_EAM_ASSET_ACTIVITIES MEAA
885 WHERE MEAA.organization_id = p_organization_id
886 AND MEAA.asset_activity_id = p_primary_item_id
887 AND MEAA.maintenance_object_id = p_rebuild_item_id
888 AND MEAA.maintenance_object_type = 2 ;-- 'MSI'*/
889 SELECT eomd.activity_source_code
890 INTO x_activity_source_code
891 FROM eam_org_maint_defaults eomd, mtl_eam_asset_activities MEAA
892 WHERE eomd.organization_id=p_organization_id
893 AND eomd.object_type = 60
894 AND eomd.object_id = meaa.activity_association_id
895 AND MEAA.asset_activity_id = p_primary_item_id
896 AND MEAA.maintenance_object_id = p_maintenance_object_id
897 AND MEAA.maintenance_object_type = 3 ;
898 --End bug fix 7343758
899 EXCEPTION
900 WHEN OTHERS THEN
901 x_activity_source_code := NULL;
902 END;
903
904 END IF;
905
906 -- Find the Activity Source from the MSI or MSN Tables based on maintenance_object_type
907
908 IF (x_activity_source_code IS NULL) THEN
909 BEGIN
910 SELECT MSI.eam_activity_source_code
911 INTO x_activity_source_code
912 FROM MTL_SYSTEM_ITEMS MSI
913 WHERE MSI.inventory_item_id = p_primary_item_id
914 AND MSI.organization_id = p_organization_id;
915 EXCEPTION
916 WHEN OTHERS THEN
917 x_activity_source_code := NULL;
918 END;
919
920 END IF;
921
922 -- End of API body.
923 -- Standard check of p_commit.
924 IF FND_API.To_Boolean( p_commit ) THEN
925 COMMIT WORK;
926 END IF;
927 -- Standard call to get message count and if count is 1, get message info.
928 FND_MSG_PUB.Count_And_Get
929 (p_count => x_msg_count,
930 p_data => x_msg_data
931 );
932 EXCEPTION
933 WHEN FND_API.G_EXC_ERROR THEN
934 ROLLBACK TO Get_EAM_Act_Source_Default_PVT;
935 x_return_status := FND_API.G_RET_STS_ERROR ;
936
937 FND_MSG_PUB.Count_And_Get
938 (p_count => x_msg_count,
939 p_data => x_msg_data
940 );
941 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
942 ROLLBACK TO Get_EAM_Act_Source_Default_PVT;
943 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
944
945 FND_MSG_PUB.Count_And_Get
946 (p_count => x_msg_count,
947 p_data => x_msg_data
948 );
949 WHEN OTHERS THEN
950 ROLLBACK TO Get_EAM_Act_Source_Default_PVT;
951 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
952
953 IF FND_MSG_PUB.Check_Msg_Level
954 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
955 THEN
956 FND_MSG_PUB.Add_Exc_Msg
957 (G_PKG_NAME,
958 l_api_name
959 );
960 END IF;
961 FND_MSG_PUB.Count_And_Get
962 (p_count => x_msg_count,
963 p_data => x_msg_data
964 );
965 END Get_EAM_Act_Source_Default;
966
967
968
969 /********************************************************************/
970 -- API to obtain eAM Mass Load Defaults
971 /********************************************************************/
972
973
974 PROCEDURE Get_EAM_Shutdown_Default
975 ( p_api_version IN NUMBER,
976 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
977 p_commit IN VARCHAR2 := FND_API.G_FALSE,
978 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
979 x_return_status OUT NOCOPY VARCHAR2,
980 x_msg_count OUT NOCOPY NUMBER,
981 x_msg_data OUT NOCOPY VARCHAR2,
982 p_primary_item_id IN NUMBER,
983 p_organization_id IN NUMBER,
984 p_maintenance_object_type IN NUMBER,
985 p_maintenance_object_id IN NUMBER,
986 p_rebuild_item_id IN NUMBER,
987 x_shutdown_type_code OUT NOCOPY NUMBER
988 )
989 IS
990 l_api_name CONSTANT VARCHAR2(30) := 'Get_EAM_Shutdown_Default';
991 l_api_version CONSTANT NUMBER := 1.0;
992
993 BEGIN
994 -- Standard Start of API savepoint
995 SAVEPOINT Get_EAM_Shutdown_Default_PVT;
996 -- Standard call to check for call compatibility.
997 IF NOT FND_API.Compatible_API_Call (l_api_version,
998 p_api_version,
999 l_api_name,
1000 G_PKG_NAME)
1001 THEN
1002 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1003 END IF;
1004 -- Initialize message list if p_init_msg_list is set to TRUE.
1005 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1006 FND_MSG_PUB.initialize;
1007 END IF;
1008 -- Initialize API return status to success
1009 x_return_status := FND_API.G_RET_STS_SUCCESS;
1010 -- API body
1011
1012 -- Find the Shutdown Type from the Association Table
1013 IF (p_maintenance_object_type IN (1,2) ) THEN -- 'MSN', 'MSI'
1014 BEGIN
1015 SELECT MEAA.shutdown_type_code
1016 INTO x_shutdown_type_code
1017 FROM MTL_EAM_ASSET_ACTIVITIES MEAA
1018 WHERE MEAA.organization_id = p_organization_id
1019 AND MEAA.asset_activity_id = p_primary_item_id
1020 AND MEAA.maintenance_object_id = p_maintenance_object_id
1021 AND MEAA.maintenance_object_type = p_maintenance_object_type
1022 AND NVL(MEAA.tmpl_flag,'N') = 'N';
1023 EXCEPTION
1024 WHEN OTHERS THEN
1025 x_shutdown_type_code := NULL;
1026 END;
1027 END IF;
1028
1029 IF (p_maintenance_object_type = 3) THEN -- 'CII'
1030 BEGIN
1031 --Begin bug fix 7343758
1032 /* SELECT MEAA.shutdown_type_code
1033 INTO x_shutdown_type_code
1034 FROM MTL_EAM_ASSET_ACTIVITIES MEAA
1035 WHERE MEAA.organization_id = p_organization_id
1036 AND MEAA.asset_activity_id = p_primary_item_id
1037 AND MEAA.maintenance_object_id = p_rebuild_item_id
1038 AND MEAA.maintenance_object_type = 2 ;-- 'MSI'*/
1039
1040 SELECT eomd.shutdown_type_code
1041 INTO x_shutdown_type_code
1042 FROM eam_org_maint_defaults eomd, mtl_eam_asset_activities MEAA
1043 WHERE eomd.organization_id=p_organization_id
1044 AND eomd.object_type = 60
1045 AND eomd.object_id = meaa.activity_association_id
1046 AND MEAA.asset_activity_id = p_primary_item_id
1047 AND MEAA.maintenance_object_id = p_maintenance_object_id
1048 AND MEAA.maintenance_object_type = 3 ;
1049
1050 --End bug fix 7343758
1051 EXCEPTION
1052 WHEN OTHERS THEN
1053 x_shutdown_type_code := NULL;
1054 END;
1055
1056 END IF;
1057
1058 -- Find the Activity source from the MSI or MSN Tables based on maintenance_object_source
1059
1060 IF (x_shutdown_type_code IS NULL) THEN
1061 BEGIN
1062 SELECT MSI.eam_act_shutdown_status
1063 INTO x_shutdown_type_code
1064 FROM MTL_SYSTEM_ITEMS MSI
1065 WHERE MSI.inventory_item_id = p_primary_item_id
1066 AND MSI.organization_id = p_organization_id;
1067 EXCEPTION
1068 WHEN OTHERS THEN
1069 x_shutdown_type_code := NULL;
1070 END;
1071
1072 END IF;
1073
1074 -- End of API body.
1075 -- Standard check of p_commit.
1076 IF FND_API.To_Boolean( p_commit ) THEN
1077 COMMIT WORK;
1078 END IF;
1079 -- Standard call to get message count and if count is 1, get message info.
1080 FND_MSG_PUB.Count_And_Get
1081 (p_count => x_msg_count,
1082 p_data => x_msg_data
1083 );
1084 EXCEPTION
1085 WHEN FND_API.G_EXC_ERROR THEN
1086 ROLLBACK TO Get_EAM_Shutdown_Default_PVT;
1087 x_return_status := FND_API.G_RET_STS_ERROR ;
1088
1089 FND_MSG_PUB.Count_And_Get
1090 (p_count => x_msg_count,
1091 p_data => x_msg_data
1092 );
1093 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1094 ROLLBACK TO Get_EAM_Shutdown_Default_PVT;
1095 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1096
1097 FND_MSG_PUB.Count_And_Get
1098 (p_count => x_msg_count,
1099 p_data => x_msg_data
1100 );
1101 WHEN OTHERS THEN
1102 ROLLBACK TO Get_EAM_Shutdown_Default_PVT;
1103 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1104
1105 IF FND_MSG_PUB.Check_Msg_Level
1106 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1107 THEN
1108 FND_MSG_PUB.Add_Exc_Msg
1109 (G_PKG_NAME,
1110 l_api_name
1111 );
1112 END IF;
1113 FND_MSG_PUB.Count_And_Get
1114 (p_count => x_msg_count,
1115 p_data => x_msg_data
1116 );
1117 END Get_EAM_Shutdown_Default;
1118
1119
1120
1121 /********************************************************************/
1122 -- API to obtain eAM Mass Load Defaults
1123 /********************************************************************/
1124
1125
1126 PROCEDURE Get_EAM_Notification_Default
1127 ( p_api_version IN NUMBER,
1128 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1129 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1130 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1131 x_return_status OUT NOCOPY VARCHAR2,
1132 x_msg_count OUT NOCOPY NUMBER,
1133 x_msg_data OUT NOCOPY VARCHAR2,
1134 p_primary_item_id IN NUMBER,
1135 p_organization_id IN NUMBER,
1136 p_maintenance_object_type IN NUMBER,
1137 p_maintenance_object_id IN NUMBER,
1138 p_rebuild_item_id IN NUMBER,
1139 x_notification_flag OUT NOCOPY VARCHAr2
1140 )
1141 IS
1142 l_api_name CONSTANT VARCHAR2(30) := 'Get_EAM_Notification_Default';
1143 l_api_version CONSTANT NUMBER := 1.0;
1144
1145
1146 BEGIN
1147 -- Standard Start of API savepoint
1148 SAVEPOINT Get_EAM_Notification_Def_PVT;
1149 -- Standard call to check for call compatibility.
1150 IF NOT FND_API.Compatible_API_Call (l_api_version,
1151 p_api_version,
1152 l_api_name,
1153 G_PKG_NAME)
1154 THEN
1155 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1156 END IF;
1157 -- Initialize message list if p_init_msg_list is set to TRUE.
1158 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1159 FND_MSG_PUB.initialize;
1160 END IF;
1161 -- Initialize API return status to success
1162 x_return_status := FND_API.G_RET_STS_SUCCESS;
1163 -- API body
1164 /*
1165 -- Find the Shutdown Type from the Association Table
1166 IF (p_maintenance_object_type IN (1,2) ) THEN -- 'MSN', 'MSI'
1167 BEGIN
1168 SELECT MEAA.notification_required
1169 INTO x_notification_flag
1170 FROM MTL_EAM_ASSET_ACTIVITIES MEAA
1171 WHERE MEAA.organization_id = p_organization_id
1172 AND MEAA.asset_activity_id = p_primary_item_id
1173 AND MEAA.maintenance_object_id = p_maintenance_object_id
1174 AND MEAA.maintenance_object_type = p_maintenance_object_type
1175 AND NVL(MEAA.tmpl_flag,'N') = 'N';
1176 EXCEPTION
1177 WHEN OTHERS THEN
1178 x_notification_flag := NULL;
1179 END;
1180 END IF;
1181
1182 IF (p_maintenance_object_type = 3) THEN -- 'CII'
1183 BEGIN
1184 SELECT MEAA.notification_required
1185 INTO x_notification_flag
1186 FROM MTL_EAM_ASSET_ACTIVITIES MEAA
1187 WHERE MEAA.organization_id = p_organization_id
1188 AND MEAA.asset_activity_id = p_primary_item_id
1189 AND MEAA.maintenance_object_id = p_rebuild_item_id
1190 AND MEAA.maintenance_object_type = 2 ;-- 'MSI'
1191
1192 EXCEPTION
1193 WHEN OTHERS THEN
1194 x_notification_flag := NULL;
1195 END;
1196
1197 END IF;
1198 */
1199
1200 /*WO Defaults ER*/
1201 BEGIN
1202 SELECT eomd.notification_required
1203 INTO x_notification_flag
1204 FROM eam_org_maint_defaults eomd, mtl_eam_asset_activities MEAA
1205 WHERE eomd.organization_id=p_organization_id
1206 AND eomd.object_type = 60
1207 AND eomd.object_id = meaa.activity_association_id
1208 AND MEAA.asset_activity_id = p_primary_item_id
1209 AND MEAA.maintenance_object_id = p_maintenance_object_id
1210 AND MEAA.maintenance_object_type = 3 ;
1211 EXCEPTION
1212 WHEN OTHERS Then
1213 x_notification_flag := null;
1214 END;
1215
1216 -- Find the Activity source from the MSI or MSN Tables based on maintenance_object_source
1217
1218 IF (x_notification_flag IS NULL) THEN
1219 BEGIN
1220 SELECT MSI.eam_act_notification_flag
1221 INTO x_notification_flag
1222 FROM MTL_SYSTEM_ITEMS MSI
1223 WHERE MSI.inventory_item_id = p_primary_item_id
1224 AND MSI.organization_id = p_organization_id;
1225 EXCEPTION
1226 WHEN OTHERS THEN
1227 x_notification_flag := NULL;
1228 END;
1229
1230 END IF;
1231
1232 -- End of API body.
1233 -- Standard check of p_commit.
1234 IF FND_API.To_Boolean( p_commit ) THEN
1235 COMMIT WORK;
1236 END IF;
1237 -- Standard call to get message count and if count is 1, get message info.
1238 FND_MSG_PUB.Count_And_Get
1239 (p_count => x_msg_count,
1240 p_data => x_msg_data
1241 );
1242 EXCEPTION
1243 WHEN FND_API.G_EXC_ERROR THEN
1244 ROLLBACK TO Get_EAM_Notification_Def_PVT;
1245 x_return_status := FND_API.G_RET_STS_ERROR ;
1246
1247 FND_MSG_PUB.Count_And_Get
1248 (p_count => x_msg_count,
1249 p_data => x_msg_data
1250 );
1251 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1252 ROLLBACK TO Get_EAM_Notification_Def_PVT;
1253 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1254
1255 FND_MSG_PUB.Count_And_Get
1256 (p_count => x_msg_count,
1257 p_data => x_msg_data
1258 );
1259 WHEN OTHERS THEN
1260 ROLLBACK TO Get_EAM_Notification_Def_PVT;
1261 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1262
1263 IF FND_MSG_PUB.Check_Msg_Level
1264 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1265 THEN
1266 FND_MSG_PUB.Add_Exc_Msg
1267 (G_PKG_NAME,
1268 l_api_name
1269 );
1270 END IF;
1271 FND_MSG_PUB.Count_And_Get
1272 (p_count => x_msg_count,
1273 p_data => x_msg_data
1274 );
1275 END Get_EAM_Notification_Default;
1276
1277
1278
1279 /********************************************************************/
1280 -- API to obtain eAM Mass Load Defaults
1281 /********************************************************************/
1282
1283
1284 PROCEDURE Get_EAM_Tagout_Default
1285 ( p_api_version IN NUMBER,
1286 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1287 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1288 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1289 x_return_status OUT NOCOPY VARCHAR2,
1290 x_msg_count OUT NOCOPY NUMBER,
1291 x_msg_data OUT NOCOPY VARCHAR2,
1292 p_primary_item_id IN NUMBER,
1293 p_organization_id IN NUMBER,
1294 p_maintenance_object_type IN NUMBER,
1295 p_maintenance_object_id IN NUMBER,
1296 p_rebuild_item_id IN NUMBER,
1297 x_tagout_required OUT NOCOPY VARCHAR2
1298 )
1299 IS
1300 l_api_name CONSTANT VARCHAR2(30) := 'Get_EAM_Tagout_Default';
1301 l_api_version CONSTANT NUMBER := 1.0;
1302
1303
1304 BEGIN
1305 -- Standard Start of API savepoint
1306 SAVEPOINT Get_EAM_Tagout_Default_PVT;
1307 -- Standard call to check for call compatibility.
1308 IF NOT FND_API.Compatible_API_Call (l_api_version,
1309 p_api_version,
1310 l_api_name,
1311 G_PKG_NAME)
1312 THEN
1313 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1314 END IF;
1315 -- Initialize message list if p_init_msg_list is set to TRUE.
1316 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1317 FND_MSG_PUB.initialize;
1318 END IF;
1319 -- Initialize API return status to success
1320 x_return_status := FND_API.G_RET_STS_SUCCESS;
1321 -- API body
1322
1323 -- Find the Shutdown Type from the Association Table
1324 IF (p_maintenance_object_type IN (1,2) ) THEN -- 'MSN', 'MSI'
1325 BEGIN
1326 SELECT MEAA.tagging_required_flag
1327 INTO x_tagout_required
1328 FROM MTL_EAM_ASSET_ACTIVITIES MEAA
1329 WHERE MEAA.organization_id = p_organization_id
1330 AND MEAA.asset_activity_id = p_primary_item_id
1331 AND MEAA.maintenance_object_id = p_maintenance_object_id
1332 AND MEAA.maintenance_object_type = p_maintenance_object_type
1333 AND NVL(MEAA.tmpl_flag,'N') = 'N';
1334 EXCEPTION
1335 WHEN OTHERS THEN
1336 x_tagout_required := NULL;
1337 END;
1338 END IF;
1339
1340 IF (p_maintenance_object_type = 3) THEN -- 'CII'
1341 BEGIN
1342 /*Begin fix for 8287895*
1343 SELECT MEAA.tagging_required_flag
1344 INTO x_tagout_required
1345 FROM MTL_EAM_ASSET_ACTIVITIES MEAA
1346 WHERE MEAA.organization_id = p_organization_id
1347 AND MEAA.asset_activity_id = p_primary_item_id
1348 AND MEAA.maintenance_object_id = p_rebuild_item_id
1349 AND MEAA.maintenance_object_type = 2 ;-- 'MSI'*/
1350
1351 SELECT eomd.tagging_required_flag
1352 INTO x_tagout_required
1353 FROM eam_org_maint_defaults eomd, mtl_eam_asset_activities MEAA
1354 WHERE eomd.organization_id=p_organization_id
1355 AND eomd.object_type = 60
1356 AND eomd.object_id = meaa.activity_association_id
1357 AND MEAA.asset_activity_id = p_primary_item_id
1358 AND MEAA.maintenance_object_id = p_maintenance_object_id
1359 AND MEAA.maintenance_object_type = 3 ;
1360
1361 /*End fix for 8287895*/
1362 EXCEPTION
1363 WHEN OTHERS THEN
1364 x_tagout_required := NULL;
1365 END;
1366
1367 END IF;
1368
1369 -- End of API body.
1370 -- Standard check of p_commit.
1371 IF FND_API.To_Boolean( p_commit ) THEN
1372 COMMIT WORK;
1373 END IF;
1374 -- Standard call to get message count and if count is 1, get message info.
1375 FND_MSG_PUB.Count_And_Get
1376 (p_count => x_msg_count,
1377 p_data => x_msg_data
1378 );
1379 EXCEPTION
1380 WHEN FND_API.G_EXC_ERROR THEN
1381 ROLLBACK TO Get_EAM_Tagout_Default_PVT;
1382 x_return_status := FND_API.G_RET_STS_ERROR ;
1383
1384 FND_MSG_PUB.Count_And_Get
1385 (p_count => x_msg_count,
1386 p_data => x_msg_data
1387 );
1388 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1389 ROLLBACK TO Get_EAM_Tagout_Default_PVT;
1390 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1391
1392 FND_MSG_PUB.Count_And_Get
1393 (p_count => x_msg_count,
1394 p_data => x_msg_data
1395 );
1396 WHEN OTHERS THEN
1397 ROLLBACK TO Get_EAM_Tagout_Default_PVT;
1398 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1399
1400 IF FND_MSG_PUB.Check_Msg_Level
1401 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1402 THEN
1403 FND_MSG_PUB.Add_Exc_Msg
1404 (G_PKG_NAME,
1405 l_api_name
1406 );
1407 END IF;
1408 FND_MSG_PUB.Count_And_Get
1409 (p_count => x_msg_count,
1410 p_data => x_msg_data
1411 );
1412 END Get_EAM_Tagout_Default;
1413
1414
1415
1416 /********************************************************************/
1417 -- API to obtain eAM Mass Load Defaults
1418 /********************************************************************/
1419
1420
1421
1422 PROCEDURE Get_EAM_Owning_Dept_Default
1423 ( p_api_version IN NUMBER,
1424 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1425 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1426 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1427 x_return_status OUT NOCOPY VARCHAR2,
1428 x_msg_count OUT NOCOPY NUMBER,
1429 x_msg_data OUT NOCOPY VARCHAR2,
1430 p_primary_item_id IN NUMBER,
1431 p_organization_id IN NUMBER,
1432 p_maintenance_object_type IN NUMBER,
1433 p_maintenance_object_id IN NUMBER,
1434 p_rebuild_item_id IN NUMBER,
1435 x_owning_department_id OUT NOCOPY NUMBER
1436 )
1437 IS
1438 l_api_name CONSTANT VARCHAR2(30) := 'Get_EAM_Owning_Dept_Default';
1439 l_api_version CONSTANT NUMBER := 1.0;
1440 l_act_assoc_id NUMBER;
1441
1442 BEGIN
1443 -- Standard Start of API savepoint
1444 SAVEPOINT Get_EAM_Owning_Dept_Def_PVT;
1445 -- Standard call to check for call compatibility.
1446 IF NOT FND_API.Compatible_API_Call (l_api_version,
1447 p_api_version,
1448 l_api_name,
1449 G_PKG_NAME)
1450 THEN
1451 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1452 END IF;
1453 -- Initialize message list if p_init_msg_list is set to TRUE.
1454 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1455 FND_MSG_PUB.initialize;
1456 END IF;
1457 -- Initialize API return status to success
1458 x_return_status := FND_API.G_RET_STS_SUCCESS;
1459 -- API body
1460
1461 -- Find the Shutdown Type from the Association Table
1462 IF (p_maintenance_object_type = 1 ) THEN -- 'MSN', 'MSI'
1463 BEGIN
1464 SELECT MEAA.owning_department_id
1465 INTO x_owning_department_id
1466 FROM MTL_EAM_ASSET_ACTIVITIES MEAA
1467 WHERE MEAA.asset_activity_id = p_primary_item_id
1468 AND MEAA.maintenance_object_id = p_maintenance_object_id
1469 AND MEAA.maintenance_object_type = p_maintenance_object_type
1470 AND NVL(MEAA.tmpl_flag,'N') = 'N';
1471 EXCEPTION
1472 WHEN OTHERS THEN
1473 x_owning_department_id := NULL;
1474 END;
1475 END IF;
1476
1477 IF (p_maintenance_object_type IN( 2,3) ) THEN -- 'CII'
1478 BEGIN
1479 SELECT MEAA.activity_association_id
1480 INTO l_act_assoc_id
1481 FROM MTL_EAM_ASSET_ACTIVITIES MEAA
1482 WHERE MEAA.asset_activity_id = p_primary_item_id
1483 AND MEAA.maintenance_object_id = p_maintenance_object_id
1484 AND MEAA.maintenance_object_type = p_maintenance_object_type
1485 AND NVL(MEAA.tmpl_flag,'N') = 'N';
1486
1487 SELECT EOMD.OWNING_DEPARTMENT_ID
1488 INTO x_owning_department_id
1489 FROM EAM_ORG_MAINT_DEFAULTS EOMD
1490 WHERE EOMD.organization_id = p_organization_id
1491 AND EOMD.OBJECT_ID = l_act_assoc_id
1492 AND EOMD.OBJECT_TYPE IN (40,60) ;
1493 EXCEPTION
1494 WHEN OTHERS THEN
1495 x_owning_department_id := NULL;
1496 END;
1497
1498 END IF;
1499
1500 -- Find the Activity source from the MSI or MSN Tables based on maintenance_object_source
1501
1502 IF (x_owning_department_id IS NULL AND p_maintenance_object_type = 3) THEN
1503 BEGIN
1504 SELECT EOMD.OWNING_DEPARTMENT_ID
1505 INTO x_owning_department_id
1506 FROM EAM_ORG_MAINT_DEFAULTS EOMD
1507 WHERE EOMD.organization_id = p_organization_id
1508 AND EOMD.OBJECT_ID = p_maintenance_object_id
1509 AND EOMD.OBJECT_TYPE = 50 ;
1510 EXCEPTION
1511 WHEN OTHERS THEN
1512 x_owning_department_id := NULL;
1513 END;
1514
1515 END IF;
1516
1517 -- Find the Activity source from the EAM Parameters Tables based on maintenance_object_source
1518
1519 IF (x_owning_department_id IS NULL AND p_maintenance_object_type IN (2,3) ) THEN
1520 BEGIN
1521 SELECT WEP.default_department_id
1522 INTO x_owning_department_id
1523 FROM WIP_EAM_PARAMETERS WEP
1524 WHERE WEP.organization_id = p_organization_id;
1525 EXCEPTION
1526 WHEN OTHERS THEN
1527 x_owning_department_id := NULL;
1528 END;
1529
1530 END IF;
1531
1532 -- End of API body.
1533 -- Standard check of p_commit.
1534 IF FND_API.To_Boolean( p_commit ) THEN
1535 COMMIT WORK;
1536 END IF;
1537 -- Standard call to get message count and if count is 1, get message info.
1538 FND_MSG_PUB.Count_And_Get
1539 (p_count => x_msg_count,
1540 p_data => x_msg_data
1541 );
1542 EXCEPTION
1543 WHEN FND_API.G_EXC_ERROR THEN
1544 ROLLBACK TO Get_EAM_Owning_Dept_Def_PVT;
1545 x_return_status := FND_API.G_RET_STS_ERROR ;
1546
1547 FND_MSG_PUB.Count_And_Get
1548 (p_count => x_msg_count,
1549 p_data => x_msg_data
1550 );
1551 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1552 ROLLBACK TO Get_EAM_Owning_Dept_Def_PVT;
1553 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1554
1555 FND_MSG_PUB.Count_And_Get
1556 (p_count => x_msg_count,
1557 p_data => x_msg_data
1558 );
1559 WHEN OTHERS THEN
1560 ROLLBACK TO Get_EAM_Owning_Dept_Def_PVT;
1561 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1562
1563 IF FND_MSG_PUB.Check_Msg_Level
1564 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1565 THEN
1566 FND_MSG_PUB.Add_Exc_Msg
1567 (G_PKG_NAME,
1568 l_api_name
1569 );
1570 END IF;
1571 FND_MSG_PUB.Count_And_Get
1572 (p_count => x_msg_count,
1573 p_data => x_msg_data
1574 );
1575 END Get_EAM_Owning_Dept_Default;
1576
1577 /********************************************************************/
1578 -- WO Defaults ER 16094794
1579 /********************************************************************/
1580
1581
1582 PROCEDURE Get_EAM_ACT_WO_Type_Default
1583 ( p_api_version IN NUMBER,
1584 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1585 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1586 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1587 x_return_status OUT NOCOPY VARCHAR2,
1588 x_msg_count OUT NOCOPY NUMBER,
1589 x_msg_data OUT NOCOPY VARCHAR2,
1590 p_primary_item_id IN NUMBER,
1591 p_organization_id IN NUMBER,
1592 p_maintenance_object_type IN NUMBER,
1593 p_maintenance_object_id IN NUMBER,
1594 p_rebuild_item_id IN NUMBER,
1595 x_ACT_WO_Type OUT NOCOPY NUMBER
1596 )
1597 IS
1598 l_api_name CONSTANT VARCHAR2(30) := 'Get_EAM_Owning_Dept_Default';
1599 l_api_version CONSTANT NUMBER := 1.0;
1600 l_act_assoc_id NUMBER;
1601
1602 BEGIN
1603 -- Standard Start of API savepoint
1604 SAVEPOINT Get_EAM_ACT_WO_Type_Def_PVT;
1605 -- Standard call to check for call compatibility.
1606 IF NOT FND_API.Compatible_API_Call (l_api_version,
1607 p_api_version,
1608 l_api_name,
1609 G_PKG_NAME)
1610 THEN
1611 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1612 END IF;
1613 -- Initialize message list if p_init_msg_list is set to TRUE.
1614 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1615 FND_MSG_PUB.initialize;
1616 END IF;
1617 -- Initialize API return status to success
1618 x_return_status := FND_API.G_RET_STS_SUCCESS;
1619 -- API body
1620
1621 -- IF (p_maintenance_object_type IN( 2,3) ) THEN -- 'CII'
1622 BEGIN
1623 SELECT eomd.work_order_type
1624 INTO x_ACT_WO_Type
1625 FROM eam_org_maint_defaults eomd, mtl_eam_asset_activities MEAA
1626 WHERE eomd.organization_id=p_organization_id
1627 AND eomd.object_type = 60
1628 AND eomd.object_id = meaa.activity_association_id
1629 AND MEAA.asset_activity_id = p_primary_item_id
1630 AND MEAA.maintenance_object_id = p_maintenance_object_id
1631 AND MEAA.maintenance_object_type = 3 ;
1632
1633 EXCEPTION
1634 WHEN OTHERS THEN
1635 x_ACT_WO_Type := NULL;
1636 END;
1637
1638 -- END IF;
1639 -- End of API body.
1640 -- Standard check of p_commit.
1641 IF FND_API.To_Boolean( p_commit ) THEN
1642 COMMIT WORK;
1643 END IF;
1644 -- Standard call to get message count and if count is 1, get message info.
1645 FND_MSG_PUB.Count_And_Get
1646 (p_count => x_msg_count,
1647 p_data => x_msg_data
1648 );
1649 EXCEPTION
1650 WHEN FND_API.G_EXC_ERROR THEN
1651 ROLLBACK TO Get_EAM_ACT_WO_Type_Def_PVT;
1652 x_return_status := FND_API.G_RET_STS_ERROR ;
1653
1654 FND_MSG_PUB.Count_And_Get
1655 (p_count => x_msg_count,
1656 p_data => x_msg_data
1657 );
1658 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1659 ROLLBACK TO Get_EAM_ACT_WO_Type_Def_PVT;
1660 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1661
1662 FND_MSG_PUB.Count_And_Get
1663 (p_count => x_msg_count,
1664 p_data => x_msg_data
1665 );
1666 WHEN OTHERS THEN
1667 ROLLBACK TO Get_EAM_ACT_WO_Type_Def_PVT;
1668 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1669
1670 IF FND_MSG_PUB.Check_Msg_Level
1671 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1672 THEN
1673 FND_MSG_PUB.Add_Exc_Msg
1674 (G_PKG_NAME,
1675 l_api_name
1676 );
1677 END IF;
1678 FND_MSG_PUB.Count_And_Get
1679 (p_count => x_msg_count,
1680 p_data => x_msg_data
1681 );
1682
1683 END Get_EAM_ACT_WO_Type_Default;
1684
1685 PROCEDURE Get_EAM_ACT_Planner_Default
1686 ( p_api_version IN NUMBER,
1687 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1688 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1689 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1690 x_return_status OUT NOCOPY VARCHAR2,
1691 x_msg_count OUT NOCOPY NUMBER,
1692 x_msg_data OUT NOCOPY VARCHAR2,
1693 p_primary_item_id IN NUMBER,
1694 p_organization_id IN NUMBER,
1695 p_maintenance_object_type IN NUMBER,
1696 p_maintenance_object_id IN NUMBER,
1697 p_rebuild_item_id IN NUMBER,
1698 x_ACT_Planner OUT NOCOPY NUMBER
1699 )
1700 IS
1701 l_api_name CONSTANT VARCHAR2(30) := 'Get_EAM_Owning_Dept_Default';
1702 l_api_version CONSTANT NUMBER := 1.0;
1703 l_act_assoc_id NUMBER;
1704
1705 BEGIN
1706 -- Standard Start of API savepoint
1707 SAVEPOINT Get_EAM_ACT_Planner_Def_PVT;
1708 -- Standard call to check for call compatibility.
1709 IF NOT FND_API.Compatible_API_Call (l_api_version,
1710 p_api_version,
1711 l_api_name,
1712 G_PKG_NAME)
1713 THEN
1714 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1715 END IF;
1716 -- Initialize message list if p_init_msg_list is set to TRUE.
1717 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1718 FND_MSG_PUB.initialize;
1719 END IF;
1720 -- Initialize API return status to success
1721 x_return_status := FND_API.G_RET_STS_SUCCESS;
1722 -- API body
1723
1724 BEGIN
1725 SELECT eomd.planner_maintenance
1726 INTO x_ACT_Planner
1727 FROM eam_org_maint_defaults eomd, mtl_eam_asset_activities MEAA
1728 WHERE eomd.organization_id=p_organization_id
1729 AND eomd.object_type = 60
1730 AND eomd.object_id = meaa.activity_association_id
1731 AND MEAA.asset_activity_id = p_primary_item_id
1732 AND MEAA.maintenance_object_id = p_maintenance_object_id
1733 AND MEAA.maintenance_object_type = 3 ;
1734
1735 EXCEPTION
1736 WHEN OTHERS THEN
1737 x_ACT_Planner := NULL;
1738 END;
1739
1740 -- END IF;
1741 -- End of API body.
1742 -- Standard check of p_commit.
1743 IF FND_API.To_Boolean( p_commit ) THEN
1744 COMMIT WORK;
1745 END IF;
1746 -- Standard call to get message count and if count is 1, get message info.
1747 FND_MSG_PUB.Count_And_Get
1748 (p_count => x_msg_count,
1749 p_data => x_msg_data
1750 );
1751 EXCEPTION
1752 WHEN FND_API.G_EXC_ERROR THEN
1753 ROLLBACK TO Get_EAM_ACT_Planner_Def_PVT;
1754 x_return_status := FND_API.G_RET_STS_ERROR ;
1755
1756 FND_MSG_PUB.Count_And_Get
1757 (p_count => x_msg_count,
1758 p_data => x_msg_data
1759 );
1760 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1761 ROLLBACK TO Get_EAM_ACT_Planner_Def_PVT;
1762 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1763
1764 FND_MSG_PUB.Count_And_Get
1765 (p_count => x_msg_count,
1766 p_data => x_msg_data
1767 );
1768 WHEN OTHERS THEN
1769 ROLLBACK TO Get_EAM_ACT_Planner_Def_PVT;
1770 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1771
1772 IF FND_MSG_PUB.Check_Msg_Level
1773 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1774 THEN
1775 FND_MSG_PUB.Add_Exc_Msg
1776 (G_PKG_NAME,
1777 l_api_name
1778 );
1779 END IF;
1780 FND_MSG_PUB.Count_And_Get
1781 (p_count => x_msg_count,
1782 p_data => x_msg_data
1783 );
1784 END Get_EAM_ACT_Planner_Default;
1785
1786 PROCEDURE Get_EAM_ACT_Firm_Default
1787 ( p_api_version IN NUMBER,
1788 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1789 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1790 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1791 x_return_status OUT NOCOPY VARCHAR2,
1792 x_msg_count OUT NOCOPY NUMBER,
1793 x_msg_data OUT NOCOPY VARCHAR2,
1794 p_primary_item_id IN NUMBER,
1795 p_organization_id IN NUMBER,
1796 p_maintenance_object_type IN NUMBER,
1797 p_maintenance_object_id IN NUMBER,
1798 p_rebuild_item_id IN NUMBER,
1799 x_ACT_Firm OUT NOCOPY NUMBER
1800 )
1801 IS
1802 l_api_name CONSTANT VARCHAR2(30) := 'Get_EAM_ACT_Firm_Default';
1803 l_api_version CONSTANT NUMBER := 1.0;
1804 l_act_assoc_id NUMBER;
1805
1806 BEGIN
1807 -- Standard Start of API savepoint
1808 SAVEPOINT Get_EAM_ACT_Firm_Def_PVT;
1809 -- Standard call to check for call compatibility.
1810 IF NOT FND_API.Compatible_API_Call (l_api_version,
1811 p_api_version,
1812 l_api_name,
1813 G_PKG_NAME)
1814 THEN
1815 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1816 END IF;
1817 -- Initialize message list if p_init_msg_list is set to TRUE.
1818 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1819 FND_MSG_PUB.initialize;
1820 END IF;
1821 -- Initialize API return status to success
1822 x_return_status := FND_API.G_RET_STS_SUCCESS;
1823 -- API body
1824
1825 BEGIN
1826 SELECT eomd.firm_planned_flag
1827 INTO x_ACT_Firm
1828 FROM eam_org_maint_defaults eomd, mtl_eam_asset_activities MEAA
1829 WHERE eomd.organization_id=p_organization_id
1830 AND eomd.object_type = 60
1831 AND eomd.object_id = meaa.activity_association_id
1832 AND MEAA.asset_activity_id = p_primary_item_id
1833 AND MEAA.maintenance_object_id = p_maintenance_object_id
1834 AND MEAA.maintenance_object_type = 3 ;
1835
1836 EXCEPTION
1837 WHEN OTHERS THEN
1838 x_ACT_Firm := NULL;
1839 END;
1840
1841 -- END IF;
1842 -- End of API body.
1843 -- Standard check of p_commit.
1844 IF FND_API.To_Boolean( p_commit ) THEN
1845 COMMIT WORK;
1846 END IF;
1847 -- Standard call to get message count and if count is 1, get message info.
1848 FND_MSG_PUB.Count_And_Get
1849 (p_count => x_msg_count,
1850 p_data => x_msg_data
1851 );
1852 EXCEPTION
1853 WHEN FND_API.G_EXC_ERROR THEN
1854 ROLLBACK TO Get_EAM_ACT_Firm_Def_PVT;
1855 x_return_status := FND_API.G_RET_STS_ERROR ;
1856
1857 FND_MSG_PUB.Count_And_Get
1858 (p_count => x_msg_count,
1859 p_data => x_msg_data
1860 );
1861 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1862 ROLLBACK TO Get_EAM_ACT_Firm_Def_PVT;
1863 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1864
1865 FND_MSG_PUB.Count_And_Get
1866 (p_count => x_msg_count,
1867 p_data => x_msg_data
1868 );
1869 WHEN OTHERS THEN
1870 ROLLBACK TO Get_EAM_ACT_Firm_Def_PVT;
1871 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1872
1873 IF FND_MSG_PUB.Check_Msg_Level
1874 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1875 THEN
1876 FND_MSG_PUB.Add_Exc_Msg
1877 (G_PKG_NAME,
1878 l_api_name
1879 );
1880 END IF;
1881 FND_MSG_PUB.Count_And_Get
1882 (p_count => x_msg_count,
1883 p_data => x_msg_data
1884 );
1885 END Get_EAM_ACT_Firm_Default;
1886
1887 PROCEDURE Get_EAM_ACT_Planned_Default
1888 ( p_api_version IN NUMBER,
1889 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1890 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1891 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1892 x_return_status OUT NOCOPY VARCHAR2,
1893 x_msg_count OUT NOCOPY NUMBER,
1894 x_msg_data OUT NOCOPY VARCHAR2,
1895 p_primary_item_id IN NUMBER,
1896 p_organization_id IN NUMBER,
1897 p_maintenance_object_type IN NUMBER,
1898 p_maintenance_object_id IN NUMBER,
1899 p_rebuild_item_id IN NUMBER,
1900 x_ACT_Planned OUT NOCOPY VARCHAR2
1901 )
1902 IS
1903 l_api_name CONSTANT VARCHAR2(30) := 'Get_EAM_Owning_Dept_Default';
1904 l_api_version CONSTANT NUMBER := 1.0;
1905 l_act_assoc_id NUMBER;
1906
1907 BEGIN
1908 -- Standard Start of API savepoint
1909 SAVEPOINT Get_EAM_ACT_Planned_Def_PVT;
1910 -- Standard call to check for call compatibility.
1911 IF NOT FND_API.Compatible_API_Call (l_api_version,
1912 p_api_version,
1913 l_api_name,
1914 G_PKG_NAME)
1915 THEN
1916 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1917 END IF;
1918 -- Initialize message list if p_init_msg_list is set to TRUE.
1919 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1920 FND_MSG_PUB.initialize;
1921 END IF;
1922 -- Initialize API return status to success
1923 x_return_status := FND_API.G_RET_STS_SUCCESS;
1924 -- API body
1925
1926 BEGIN
1927 SELECT eomd.plan_maintenance
1928 INTO x_ACT_Planned
1929 FROM eam_org_maint_defaults eomd, mtl_eam_asset_activities MEAA
1930 WHERE eomd.organization_id=p_organization_id
1931 AND eomd.object_type = 60
1932 AND eomd.object_id = meaa.activity_association_id
1933 AND MEAA.asset_activity_id = p_primary_item_id
1934 AND MEAA.maintenance_object_id = p_maintenance_object_id
1935 AND MEAA.maintenance_object_type = 3 ;
1936
1937 EXCEPTION
1938 WHEN OTHERS THEN
1939 x_ACT_Planned := NULL;
1940 END;
1941
1942 -- END IF;
1943 -- End of API body.
1944 -- Standard check of p_commit.
1945 IF FND_API.To_Boolean( p_commit ) THEN
1946 COMMIT WORK;
1947 END IF;
1948 -- Standard call to get message count and if count is 1, get message info.
1949 FND_MSG_PUB.Count_And_Get
1950 (p_count => x_msg_count,
1951 p_data => x_msg_data
1952 );
1953 EXCEPTION
1954 WHEN FND_API.G_EXC_ERROR THEN
1955 ROLLBACK TO Get_EAM_ACT_Planned_Def_PVT;
1956 x_return_status := FND_API.G_RET_STS_ERROR ;
1957
1958 FND_MSG_PUB.Count_And_Get
1959 (p_count => x_msg_count,
1960 p_data => x_msg_data
1961 );
1962 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1963 ROLLBACK TO Get_EAM_ACT_Planned_Def_PVT;
1964 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1965
1966 FND_MSG_PUB.Count_And_Get
1967 (p_count => x_msg_count,
1968 p_data => x_msg_data
1969 );
1970 WHEN OTHERS THEN
1971 ROLLBACK TO Get_EAM_ACT_Planned_Def_PVT;
1972 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1973
1974 IF FND_MSG_PUB.Check_Msg_Level
1975 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1976 THEN
1977 FND_MSG_PUB.Add_Exc_Msg
1978 (G_PKG_NAME,
1979 l_api_name
1980 );
1981 END IF;
1982 FND_MSG_PUB.Count_And_Get
1983 (p_count => x_msg_count,
1984 p_data => x_msg_data
1985 );
1986 END Get_EAM_ACT_Planned_Default;
1987
1988
1989 END WIP_EAMWORKORDER_PVT;
[Home] [Help]