1 PACKAGE BODY WIP_EAMWORKORDER_PVT AS
2 /* $Header: WIPVEWOB.pls 120.5 2006/03/31 02:58:26 kmurthy noship $ */
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
43 /* added for calling WO API */
44
45 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
46 l_eam_op_rec EAM_PROCESS_WO_PUB.eam_op_rec_type;
47 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
48 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
49 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
50 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
51 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
52 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
53 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
54 l_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
55
56 l_out_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
57 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
58 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
59 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
60 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
61 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
62 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
63 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
64 l_out_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
65
66 BEGIN
67 if (l_log and (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
68 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Start of ' || l_module);
69 end if;
70 l_maint_obj_fnd :='N';
71 l_eam_wo_rec.asset_activity_id := p_work_order_rec.primary_item_id;
72
73 -- Standard Start of API savepoint
74 SAVEPOINT Create_EAM_Work_Order_PVT;
75 -- Standard call to check for call compatibility.
76 IF NOT FND_API.Compatible_API_Call (l_api_version,
77 p_api_version,
78 l_api_name,
79 G_PKG_NAME )
80 THEN
81 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
82 END IF;
83 -- Initialize message list if p_init_msg_list is set to TRUE.
84 IF FND_API.to_Boolean( p_init_msg_list ) THEN
85 FND_MSG_PUB.initialize;
86 END IF;
87 -- Initialize API return status to success
88 x_return_status := FND_API.G_RET_STS_SUCCESS;
89 -- API body
90
91 -- EAM Specific Validation
92 IF (NVL(p_work_order_rec.load_type,7) <> 7) THEN
93 FND_MESSAGE.SET_NAME('EAM','Incorrect Value of Load Type: '||p_work_order_rec.load_type);
94 FND_MSG_PUB.ADD();
95 RAISE FND_API.G_EXC_ERROR;
96 END IF;
97
98 /*
99 For maintenance_object_type, use the following criteria
100 1. For asset work orders and serialized rebuild work order, pass value of 3
101 2. For non-serialized rebuild work orders, pass value of 2
102 */
103
104 l_asset_group_id := p_work_order_rec.asset_group_id;
105 l_asset_number := p_work_order_rec.asset_number;
106
107 IF (p_work_order_rec.maintenance_object_id IS NOT NULL AND
108 p_work_order_rec.maintenance_object_type = 3 ) THEN
109
110 BEGIN
111 SELECT cii.inventory_item_id,
112 cii.serial_number
113 INTO
114 l_asset_group_id,
115 l_asset_number
116 FROM
117 csi_item_instances cii
118 WHERE
119 cii.instance_id = p_work_order_rec.maintenance_object_id;
120 l_maintenance_object_type := 3;
121 l_gen_object_id := p_work_order_rec.maintenance_object_id;
122 l_maint_obj_fnd :='Y';
123 EXCEPTION
124 WHEN NO_DATA_FOUND THEN
125 NULL;
126 END;
127 END IF;
128
129 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
130 BEGIN
131
132 SELECT instance_id into l_gen_object_id
133 FROM csi_item_instances
134 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 ;
135 l_maintenance_object_type := 3;
136 l_maintenance_object_id := l_gen_object_id;
137 l_maint_obj_fnd :='Y';
138 EXCEPTION
139 WHEN NO_DATA_FOUND THEN
140 NULL;
141 END;
142 END IF;
143
144 IF (l_maint_obj_fnd<>'Y' AND p_work_order_rec.rebuild_item_id IS NOT NULL
145 AND p_work_order_rec.rebuild_serial_number IS NOT NULL ) THEN
146 BEGIN
147 select instance_id into l_gen_object_id
148 from csi_item_instances
149 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;
150 l_maintenance_object_type := 3;
151 l_maintenance_object_id := l_gen_object_id;
152 l_maint_obj_fnd :='Y';
153 EXCEPTION
154 WHEN NO_DATA_FOUND THEN
155 NULL;
156 END;
157 END IF ;
158
159 IF (l_maint_obj_fnd<>'Y' AND p_work_order_rec.rebuild_item_id IS NOT NULL) THEN
160 BEGIN
161 SELECT inventory_item_id into l_gen_object_id
162 FROM mtl_system_items
163 WHERE inventory_item_id = p_work_order_rec.rebuild_item_id and organization_id =p_work_order_rec.organization_id;
164 l_maintenance_object_type := 2;
165 l_maintenance_object_id := l_gen_object_id;
166 EXCEPTION
167 WHEN NO_DATA_FOUND THEN
168 NULL;
169 END;
170 END IF;
171
172 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
173 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
174 'l_gen_object_id: ' || l_gen_object_id);
175 end if;
176 l_eam_wo_rec.status_type := p_work_order_rec.status_type;
177
178 /* get output directory path from database */
179 log_path(l_output_dir);
180
181 l_eam_wo_rec.header_id := null;
182 l_eam_wo_rec.batch_id := null;
183 l_eam_wo_rec.row_id := null;
184 l_eam_wo_rec.wip_entity_name := null ;
185 l_eam_wo_rec.wip_entity_id := null;
186 l_eam_wo_rec.organization_id := p_work_order_rec.organization_id;
187 l_eam_wo_rec.description := null;
188 l_eam_wo_rec.asset_number := l_asset_number;
189 l_eam_wo_rec.asset_group_id := l_asset_group_id;
190 l_eam_wo_rec.rebuild_item_id := p_work_order_rec.rebuild_item_id;
191 l_eam_wo_rec.rebuild_serial_number := p_work_order_rec.rebuild_serial_number;
192 l_eam_wo_rec.maintenance_object_id := l_gen_object_id;
193 l_eam_wo_rec.maintenance_object_type := l_maintenance_object_type;
194 l_eam_wo_rec.maintenance_object_source := 1;
195 l_eam_wo_rec.class_code := p_work_order_rec.class_code;
196 l_eam_wo_rec.activity_type := p_work_order_rec.activity_type;
197 l_eam_wo_rec.activity_cause :=p_work_order_rec.activity_cause;
198 l_eam_wo_rec.activity_source := p_work_order_rec.activity_source;
199 l_eam_wo_rec.work_order_type := p_work_order_rec.work_order_type;
200
201 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
202 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module, 'Fetching default owning dept');
203 end if;
204 IF p_work_order_rec.owning_department is not null THEN
205 l_dept_id := p_work_order_rec.owning_department;
206 ELSE if p_work_order_rec.owning_department_code is not null THEN
207 SELECT department_id into l_dept_id
208 FROM bom_departments
209 WHERE department_code = p_work_order_rec.owning_department_code and organization_id= p_work_order_rec.organization_id;
210 ELSE
211 WIP_EAMWORKORDER_PVT.Get_EAM_Owning_Dept_Default
212 ( p_api_version => 1.0,
213 p_init_msg_list => FND_API.G_FALSE,
214 p_commit => FND_API.G_FALSE,
215 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
216 x_return_status => l_def_return_status,
217 x_msg_count => l_def_msg_count,
218 x_msg_data => l_def_msg_data,
219 p_primary_item_id => l_eam_wo_rec.asset_activity_id,
220 p_organization_id => l_eam_wo_rec.organization_id,
221 p_maintenance_object_type => l_maintenance_object_type,
222 p_maintenance_object_id => l_maintenance_object_id,
223 p_rebuild_item_id => l_eam_wo_rec.rebuild_item_id,
224 x_owning_department_id => l_dept_id
225 );
226 END IF ;
227 END IF ;
228
229 /* If the Department of Work Order cannot be set then change the status of Work Order to Unrelease */
230 IF l_dept_id is null and p_work_order_rec.status_type not in (1,6,17) THEN
231 l_eam_wo_rec.status_type:=1;
232 END IF ;
233 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
234 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module, 'default owning dept id:'
235 || l_dept_id);
236 end if;
237
238 l_eam_wo_rec.job_quantity := null;
239 l_eam_wo_rec.date_released := p_work_order_rec.date_released;
240 l_eam_wo_rec.owning_department := l_dept_id;
241 l_eam_wo_rec.priority := p_work_order_rec.priority;
242 l_eam_wo_rec.requested_start_date := p_work_order_rec.requested_start_date;
243 l_eam_wo_rec.due_date := p_work_order_rec.due_date;
244 l_eam_wo_rec.shutdown_type := p_work_order_rec.shutdown_type;
245 l_eam_wo_rec.firm_planned_flag := p_work_order_rec.firm_planned_flag;
246 l_eam_wo_rec.notification_required := p_work_order_rec.notification_required;
247 l_eam_wo_rec.tagout_required := p_work_order_rec.tagout_required;
248 l_eam_wo_rec.plan_maintenance := p_work_order_rec.plan_maintenance;
249 l_eam_wo_rec.project_id := p_work_order_rec.project_id;
250 l_eam_wo_rec.task_id := p_work_order_rec.task_id;
251 --project_costed
252 l_eam_wo_rec.end_item_unit_number := p_work_order_rec.end_item_unit_number;
253 l_eam_wo_rec.schedule_group_id := p_work_order_rec.schedule_group_id;
254 l_eam_wo_rec.bom_revision_date := p_work_order_rec.bom_revision_date;
255 l_eam_wo_rec.routing_revision_date := p_work_order_rec.routing_revision_date;
256 l_eam_wo_rec.alternate_routing_designator := p_work_order_rec.alternate_routing_designator;
257 l_eam_wo_rec.alternate_bom_designator := p_work_order_rec.alternate_bom_designator;
258 l_eam_wo_rec.routing_revision := p_work_order_rec.routing_revision;
259 l_eam_wo_rec.bom_revision := p_work_order_rec.bom_revision;
260 l_eam_wo_rec.parent_wip_entity_id := p_work_order_rec.parent_wip_entity_id;
261 l_eam_wo_rec.manual_rebuild_flag := p_work_order_rec.manual_rebuild_flag;
262 l_eam_wo_rec.pm_schedule_id := p_work_order_rec.pm_schedule_id;
266 l_eam_wo_rec.resource_account := null;
263 l_eam_wo_rec.wip_supply_type := p_work_order_rec.wip_supply_type;
264 l_eam_wo_rec.material_account := null;
265 l_eam_wo_rec.material_overhead_account := null ;
267 l_eam_wo_rec.outside_processing_account := null;
268 l_eam_wo_rec.material_variance_account := null;
269 l_eam_wo_rec.resource_variance_account := null;
270 l_eam_wo_rec.outside_proc_variance_account := null;
271 l_eam_wo_rec.std_cost_adjustment_account := null;
272 l_eam_wo_rec.overhead_account := null;
273 l_eam_wo_rec.overhead_variance_account := null;
274 l_eam_wo_rec.scheduled_start_date := sysdate;
275 l_eam_wo_rec.scheduled_completion_date := sysdate;
276 l_eam_wo_rec.common_bom_sequence_id := null;
277 l_eam_wo_rec.common_routing_sequence_id := null;
278 l_eam_wo_rec.po_creation_time := null;
279 l_eam_wo_rec.gen_object_id := l_gen_object_id;
280 l_eam_wo_rec.attribute_category := p_work_order_rec.attribute_category;
281 l_eam_wo_rec.attribute1 := p_work_order_rec.attribute1;
282 l_eam_wo_rec.attribute2 := p_work_order_rec.attribute2;
283 l_eam_wo_rec.attribute3 := p_work_order_rec.attribute3;
284 l_eam_wo_rec.attribute4 := p_work_order_rec.attribute4;
285 l_eam_wo_rec.attribute5 := p_work_order_rec.attribute5;
286 l_eam_wo_rec.attribute6 := p_work_order_rec.attribute6;
287 l_eam_wo_rec.attribute7 := p_work_order_rec.attribute7;
288 l_eam_wo_rec.attribute8 := p_work_order_rec.attribute8;
289 l_eam_wo_rec.attribute9 := p_work_order_rec.attribute9;
290 l_eam_wo_rec.attribute10 := p_work_order_rec.attribute10;
291 l_eam_wo_rec.attribute11 := p_work_order_rec.attribute11;
292 l_eam_wo_rec.attribute12 := p_work_order_rec.attribute12;
293 l_eam_wo_rec.attribute13 := p_work_order_rec.attribute13;
294 l_eam_wo_rec.attribute14 := p_work_order_rec.attribute14;
295 l_eam_wo_rec.attribute15 := p_work_order_rec.attribute15;
296 l_eam_wo_rec.material_issue_by_mo := null;
297 l_eam_wo_rec.issue_zero_cost_flag := null;
298 l_eam_wo_rec.user_id := fnd_global.user_id;
299 l_eam_wo_rec.responsibility_id := fnd_global.resp_id;
300 l_eam_wo_rec.request_id := p_work_order_rec.request_id;
301 l_eam_wo_rec.program_id := p_work_order_rec.program_id;
302 l_eam_wo_rec.program_application_id := p_work_order_rec.program_application_id;
303 l_eam_wo_rec.source_line_id := p_work_order_rec.source_line_id;
304 l_eam_wo_rec.source_code := p_work_order_rec.source_code;
305 -- l_eam_wo_rec.return_status :=
306 l_eam_wo_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_CREATE;
307
308 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
309 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module, 'Calling work order API');
310 end if;
311 EAM_PROCESS_WO_PUB.Process_WO
312 ( p_bo_identifier => 'EAM'
313 , p_init_msg_list => TRUE
314 , p_api_version_number => 1.0
315 , p_commit => 'N'
316 , p_eam_wo_rec => l_eam_wo_rec
317 , p_eam_op_tbl => l_eam_op_tbl
318 , p_eam_op_network_tbl => l_eam_op_network_tbl
319 , p_eam_res_tbl => l_eam_res_tbl
320 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
321 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
322 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
323 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
324 , p_eam_direct_items_tbl => l_eam_di_tbl
325 , x_eam_wo_rec => l_out_eam_wo_rec
326 , x_eam_op_tbl => l_out_eam_op_tbl
327 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
328 , x_eam_res_tbl => l_out_eam_res_tbl
329 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
330 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
331 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
332 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
333 , x_eam_direct_items_tbl => l_out_eam_di_tbl
334 , x_return_status => l_return_status
335 , x_msg_count => l_msg_count
336 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
337 , p_debug_filename => 'wipvewob.log'
338 , p_output_dir => l_output_dir
339 , p_debug_file_mode => 'W'
340 );
341 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
342 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
343 'work order API returned status: '|| l_return_status);
344 end if;
345
346 if(l_return_status <> FND_API.G_RET_STS_SUCCESS) then
347 fnd_message.set_name('EAM','EAM_CANNOT_CREAT_WRK');
348 fnd_msg_pub.add();
349 RAISE FND_API.G_EXC_ERROR;
350 end if ;
351
352 IF FND_API.To_Boolean( p_commit ) THEN
353 COMMIT WORK;
354 END IF;
355
356 -- End of API body.
357 -- Standard call to get message count and if count is 1, get message info.
358 FND_MSG_PUB.Count_And_Get
359 (p_count => x_msg_count,
360 p_data => x_msg_data
361 );
362 EXCEPTION
363 WHEN FND_API.G_EXC_ERROR THEN
364 ROLLBACK TO Create_EAM_Work_Order_PVT;
368 (p_count => x_msg_count,
365 x_return_status := FND_API.G_RET_STS_ERROR ;
366 x_request_id := l_request_id;
367 FND_MSG_PUB.Count_And_Get
369 p_data => x_msg_data
370 );
371 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
372 ROLLBACK TO Create_EAM_Work_Order_PVT;
373 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
374 x_request_id := l_request_id;
375 FND_MSG_PUB.Count_And_Get
376 (p_count => x_msg_count,
377 p_data => x_msg_data
378 );
379 WHEN OTHERS THEN
380 ROLLBACK TO Create_EAM_Work_Order_PVT;
381 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
382 x_request_id := l_request_id;
383 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
384 THEN
385 FND_MSG_PUB.Add_Exc_Msg
386 (G_PKG_NAME,
387 l_api_name
388 );
389 END IF;
390 FND_MSG_PUB.Count_And_Get
391 (p_count => x_msg_count,
392 p_data => x_msg_data
393 );
394 END Create_EAM_Work_Order;
395
396
397 /********************************************************************/
398 -- API to obtain eAM Mass Load Defaults
399 /********************************************************************/
400
401
402 PROCEDURE Get_EAM_Act_Cause_Default
403 ( p_api_version IN NUMBER,
404 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
405 p_commit IN VARCHAR2 := FND_API.G_FALSE,
406 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
407 x_return_status OUT NOCOPY VARCHAR2,
408 x_msg_count OUT NOCOPY NUMBER,
409 x_msg_data OUT NOCOPY VARCHAR2,
410 p_primary_item_id IN NUMBER,
411 p_organization_id IN NUMBER,
412 p_maintenance_object_type IN NUMBER,
413 p_maintenance_object_id IN NUMBER,
414 p_rebuild_item_id IN NUMBER,
415 x_activity_cause_code OUT NOCOPY NUMBER
416 )
417 IS
418 l_api_name CONSTANT VARCHAR2(30) := 'Get_EAM_Act_Cause_Default';
419 l_api_version CONSTANT NUMBER := 1.0;
420
421
422 BEGIN
423 -- Standard Start of API savepoint
424 SAVEPOINT Get_EAM_Act_Cause_Default_PVT;
425 -- Standard call to check for call compatibility.
426 IF NOT FND_API.Compatible_API_Call (l_api_version,
427 p_api_version,
428 l_api_name,
429 G_PKG_NAME )
430 THEN
431 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
432 END IF;
433 -- Initialize message list if p_init_msg_list is set to TRUE.
434 IF FND_API.to_Boolean( p_init_msg_list ) THEN
435 FND_MSG_PUB.initialize;
436 END IF;
437 -- Initialize API return status to success
438 x_return_status := FND_API.G_RET_STS_SUCCESS;
439 -- API body
440
441 -- Find the Activity Cause from the Association Table
442 IF (p_maintenance_object_type IN (1,2) ) THEN -- 'MSN', 'MSI'
443 BEGIN
444 SELECT MEAA.activity_cause_code
445 INTO x_activity_cause_code
446 FROM MTL_EAM_ASSET_ACTIVITIES MEAA
447 WHERE MEAA.organization_id = p_organization_id
448 AND MEAA.asset_activity_id = p_primary_item_id
449 AND MEAA.maintenance_object_id = p_maintenance_object_id
450 AND MEAA.maintenance_object_type = p_maintenance_object_type
451 AND NVL(MEAA.tmpl_flag,'N') = 'N';
452 EXCEPTION
453 WHEN OTHERS THEN
454 x_activity_cause_code := NULL;
455 END;
456 END IF;
457
458 IF (p_maintenance_object_type = 3) THEN -- 'CII'
459 BEGIN
460 SELECT MEAA.activity_cause_code
461 INTO x_activity_cause_code
462 FROM MTL_EAM_ASSET_ACTIVITIES MEAA
463 WHERE MEAA.organization_id = p_organization_id
464 AND MEAA.asset_activity_id = p_primary_item_id
465 AND MEAA.maintenance_object_id = p_rebuild_item_id
466 AND MEAA.maintenance_object_type = 2 ;-- 'MSI'
467 EXCEPTION
468 WHEN OTHERS THEN
469 x_activity_cause_code := NULL;
470 END;
471
472 END IF;
473
474 -- Find the Activity Cause from the MSI or MSN Tables based on maintenance_object_type
475
476 IF (x_activity_cause_code IS NULL) THEN
477 BEGIN
478 SELECT MSI.eam_activity_cause_code
479 INTO x_activity_cause_code
480 FROM MTL_SYSTEM_ITEMS MSI
481 WHERE MSI.inventory_item_id = p_primary_item_id
482 AND MSI.organization_id = p_organization_id;
483 EXCEPTION
484 WHEN OTHERS THEN
485 x_activity_cause_code := NULL;
486 END;
487
488 END IF;
489
490
491 -- End of API body.
492 -- Standard check of p_commit.
493 IF FND_API.To_Boolean( p_commit ) THEN
497 FND_MSG_PUB.Count_And_Get
494 COMMIT WORK;
495 END IF;
496 -- Standard call to get message count and if count is 1, get message info.
498 (p_count => x_msg_count,
499 p_data => x_msg_data
500 );
501 EXCEPTION
502 WHEN FND_API.G_EXC_ERROR THEN
503 ROLLBACK TO Get_EAM_Act_Cause_Default_PVT;
504 x_return_status := FND_API.G_RET_STS_ERROR ;
505
506 FND_MSG_PUB.Count_And_Get
507 (p_count => x_msg_count,
508 p_data => x_msg_data
509 );
510 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
511 ROLLBACK TO Get_EAM_Act_Cause_Default_PVT;
512 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
513
514 FND_MSG_PUB.Count_And_Get
515 (p_count => x_msg_count,
516 p_data => x_msg_data
517 );
518 WHEN OTHERS THEN
519 ROLLBACK TO Get_EAM_Act_Cause_Default_PVT;
520 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
521
522 IF FND_MSG_PUB.Check_Msg_Level
523 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
524 THEN
525 FND_MSG_PUB.Add_Exc_Msg
526 (G_PKG_NAME,
527 l_api_name
528 );
529 END IF;
530 FND_MSG_PUB.Count_And_Get
531 (p_count => x_msg_count,
532 p_data => x_msg_data
533 );
534 END Get_EAM_Act_Cause_Default;
535
536 /*Procedure to find the log directory path to write debug messages for EAM workorder API*/
537
538 PROCEDURE log_path(
539 x_output_dir OUT NOCOPY VARCHAR2
540 )
541 IS
542 l_full_path VARCHAR2(512);
543 l_new_full_path VARCHAR2(512);
544 l_file_dir VARCHAR2(512);
545
546 fileHandler UTL_FILE.FILE_TYPE;
547 fileName VARCHAR2(50);
548
549 l_flag NUMBER;
550 BEGIN
551 fileName:='test.log';--this is only a dummy filename to check if directory is valid or not
552
553 /* get output directory path from database */
554 SELECT value
555 INTO l_full_path
556 FROM v$parameter
557 WHERE name = 'utl_file_dir';
558
559 l_flag := 0;
560 --l_full_path contains a list of comma-separated directories
561 WHILE(TRUE)
562 LOOP
563 --get the first dir in the list
564 SELECT trim(substr(l_full_path, 1, decode(instr(l_full_path,',')-1,
565 -1, length(l_full_path),
566 instr(l_full_path, ',')-1
567 )
568 )
569 )
570 INTO l_file_dir
571 FROM dual;
572
573 -- check if the dir is valid
574 BEGIN
575 fileHandler := UTL_FILE.FOPEN(l_file_dir , filename, 'w');
576 l_flag := 1;
577 EXCEPTION
578 WHEN utl_file.invalid_path THEN
579 l_flag := 0;
580 WHEN utl_file.invalid_operation THEN
581 l_flag := 0;
582 END;
583
584 IF l_flag = 1 THEN --got a valid directory
585 utl_file.fclose(fileHandler);
586 EXIT;
587 END IF;
588
589 --earlier found dir was not a valid dir,
590 --so remove that from the list, and get the new list
591 l_new_full_path := trim(substr(l_full_path, instr(l_full_path, ',')+1, length(l_full_path)));
592
593 --if the new list has not changed, there are no more valid dirs left
594 IF l_full_path = l_new_full_path THEN
595 l_flag:=0;
596 EXIT;
597 END IF;
598 l_full_path := l_new_full_path;
599 END LOOP;
600
601 IF(l_flag=1) THEN --found a valid directory
602 x_output_dir := l_file_dir;
603 ELSE
604 x_output_dir:= null;
605
606 END IF;
607 EXCEPTION
608 WHEN OTHERS THEN
609 x_output_dir := null;
610
611 END log_path;
612
613 /********************************************************************/
614 -- API to obtain eAM Mass Load Defaults
615 /********************************************************************/
616
617
618 PROCEDURE Get_EAM_Act_Type_Default
619 ( p_api_version IN NUMBER,
620 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
621 p_commit IN VARCHAR2 := FND_API.G_FALSE,
622 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
623 x_return_status OUT NOCOPY VARCHAR2,
624 x_msg_count OUT NOCOPY NUMBER,
625 x_msg_data OUT NOCOPY VARCHAR2,
626 p_primary_item_id IN NUMBER,
627 p_organization_id IN NUMBER,
628 p_maintenance_object_type IN NUMBER,
629 p_maintenance_object_id IN NUMBER,
630 p_rebuild_item_id IN NUMBER,
631 x_activity_type_code OUT NOCOPY NUMBER
632 )
633 IS
634 l_api_name CONSTANT VARCHAR2(30) := 'Get_EAM_Act_Type_Default';
635 l_api_version CONSTANT NUMBER := 1.0;
636
637
638 BEGIN
642 IF NOT FND_API.Compatible_API_Call (l_api_version,
639 -- Standard Start of API savepoint
640 SAVEPOINT Get_EAM_Act_Type_Default_PVT;
641 -- Standard call to check for call compatibility.
643 p_api_version,
644 l_api_name,
645 G_PKG_NAME)
646 THEN
647 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
648 END IF;
649 -- Initialize message list if p_init_msg_list is set to TRUE.
650 IF FND_API.to_Boolean( p_init_msg_list ) THEN
651 FND_MSG_PUB.initialize;
652 END IF;
653 -- Initialize API return status to success
654 x_return_status := FND_API.G_RET_STS_SUCCESS;
655 -- API body
656
657 -- Find the Activity type from the Association Table
658 IF (p_maintenance_object_type IN (1,2) ) THEN -- 'MSN', 'MSI'
659 BEGIN
660 SELECT MEAA.activity_type_code
661 INTO x_activity_type_code
662 FROM MTL_EAM_ASSET_ACTIVITIES MEAA
663 WHERE MEAA.organization_id = p_organization_id
664 AND MEAA.asset_activity_id = p_primary_item_id
665 AND MEAA.maintenance_object_id = p_maintenance_object_id
666 AND MEAA.maintenance_object_type = p_maintenance_object_type
667 AND NVL(MEAA.tmpl_flag,'N') = 'N';
668 EXCEPTION
669 WHEN OTHERS THEN
670 x_activity_type_code := NULL;
671 END;
672 END IF;
673
674 IF (p_maintenance_object_type = 3) THEN -- 'CII'
675 BEGIN
676 SELECT MEAA.activity_type_code
677 INTO x_activity_type_code
678 FROM MTL_EAM_ASSET_ACTIVITIES MEAA
679 WHERE MEAA.organization_id = p_organization_id
680 AND MEAA.asset_activity_id = p_primary_item_id
681 AND MEAA.maintenance_object_id = p_rebuild_item_id
682 AND MEAA.maintenance_object_type = 2 ;-- 'MSI'
683 EXCEPTION
684 WHEN OTHERS THEN
685 x_activity_type_code := NULL;
686 END;
687
688 END IF;
689
690 -- Find the Activity type from the MSI or MSN Tables based on maintenance_object_type
691
692 IF (x_activity_type_code IS NULL) THEN
693 BEGIN
694 SELECT MSI.eam_activity_type_code
695 INTO x_activity_type_code
696 FROM MTL_SYSTEM_ITEMS MSI
697 WHERE MSI.inventory_item_id = p_primary_item_id
698 AND MSI.organization_id = p_organization_id;
699 EXCEPTION
700 WHEN OTHERS THEN
701 x_activity_type_code := NULL;
702 END;
703
704 END IF;
705
706 -- End of API body.
707 -- Standard check of p_commit.
708 IF FND_API.To_Boolean( p_commit ) THEN
709 COMMIT WORK;
710 END IF;
711 -- Standard call to get message count and if count is 1, get message info.
712 FND_MSG_PUB.Count_And_Get
713 (p_count => x_msg_count,
714 p_data => x_msg_data
715 );
716 EXCEPTION
717 WHEN FND_API.G_EXC_ERROR THEN
718 ROLLBACK TO Get_EAM_Act_Type_Default_PVT;
719 x_return_status := FND_API.G_RET_STS_ERROR ;
720
721 FND_MSG_PUB.Count_And_Get
722 (p_count => x_msg_count,
723 p_data => x_msg_data
724 );
725 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
726 ROLLBACK TO Get_EAM_Act_Type_Default_PVT;
727 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
728
729 FND_MSG_PUB.Count_And_Get
730 (p_count => x_msg_count,
731 p_data => x_msg_data
732 );
733 WHEN OTHERS THEN
734 ROLLBACK TO Get_EAM_Act_Type_Default_PVT;
735 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
736
737 IF FND_MSG_PUB.Check_Msg_Level
738 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
739 THEN
740 FND_MSG_PUB.Add_Exc_Msg
741 (G_PKG_NAME,
742 l_api_name
743 );
744 END IF;
745 FND_MSG_PUB.Count_And_Get
746 (p_count => x_msg_count,
747 p_data => x_msg_data
748 );
749 END Get_EAM_Act_type_Default;
750
751
752
753 /********************************************************************/
754 -- API to obtain eAM Mass Load Defaults
758 PROCEDURE Get_EAM_Act_Source_Default
755 /********************************************************************/
756
757
759 ( p_api_version IN NUMBER,
760 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
761 p_commit IN VARCHAR2 := FND_API.G_FALSE,
762 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
763 x_return_status OUT NOCOPY VARCHAR2,
764 x_msg_count OUT NOCOPY NUMBER,
765 x_msg_data OUT NOCOPY VARCHAR2,
766 p_primary_item_id IN NUMBER,
767 p_organization_id IN NUMBER,
768 p_maintenance_object_type IN NUMBER,
769 p_maintenance_object_id IN NUMBER,
770 p_rebuild_item_id IN NUMBER,
771 x_activity_Source_code OUT NOCOPY NUMBER
772 )
773 IS
774 l_api_name CONSTANT VARCHAR2(30) := 'Get_EAM_Act_Source_Default';
775 l_api_version CONSTANT NUMBER := 1.0;
776
777
778 BEGIN
779 -- Standard Start of API savepoint
780 SAVEPOINT Get_EAM_Act_Source_Default_PVT;
781 -- Standard call to check for call compatibility.
782 IF NOT FND_API.Compatible_API_Call (l_api_version,
783 p_api_version,
784 l_api_name,
785 G_PKG_NAME)
786 THEN
787 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
788 END IF;
789 -- Initialize message list if p_init_msg_list is set to TRUE.
790 IF FND_API.to_Boolean( p_init_msg_list ) THEN
791 FND_MSG_PUB.initialize;
792 END IF;
793 -- Initialize API return status to success
794 x_return_status := FND_API.G_RET_STS_SUCCESS;
795 -- API body
796
797 -- Find the Activity Source from the Association Table
798 IF (p_maintenance_object_type IN (1, 2) ) THEN -- 'MSN', 'MSI'
799 BEGIN
800 SELECT MEAA.activity_source_code
801 INTO x_activity_source_code
802 FROM MTL_EAM_ASSET_ACTIVITIES MEAA
803 WHERE MEAA.organization_id = p_organization_id
804 AND MEAA.asset_activity_id = p_primary_item_id
805 AND MEAA.maintenance_object_id = p_maintenance_object_id
806 AND MEAA.maintenance_object_type = p_maintenance_object_type
807 AND NVL(MEAA.tmpl_flag,'N') = 'N';
808 EXCEPTION
809 WHEN OTHERS THEN
810 x_activity_source_code := NULL;
811 END;
812 END IF;
813
814 IF (p_maintenance_object_type = 3) THEN -- 'CII'
815 BEGIN
816 SELECT MEAA.activity_source_code
817 INTO x_activity_source_code
818 FROM MTL_EAM_ASSET_ACTIVITIES MEAA
819 WHERE MEAA.organization_id = p_organization_id
820 AND MEAA.asset_activity_id = p_primary_item_id
821 AND MEAA.maintenance_object_id = p_rebuild_item_id
822 AND MEAA.maintenance_object_type = 2 ;-- 'MSI'
823 EXCEPTION
824 WHEN OTHERS THEN
825 x_activity_source_code := NULL;
826 END;
827
831
828 END IF;
829
830 -- Find the Activity Source from the MSI or MSN Tables based on maintenance_object_type
832 IF (x_activity_source_code IS NULL) THEN
833 BEGIN
834 SELECT MSI.eam_activity_source_code
835 INTO x_activity_source_code
836 FROM MTL_SYSTEM_ITEMS MSI
837 WHERE MSI.inventory_item_id = p_primary_item_id
838 AND MSI.organization_id = p_organization_id;
839 EXCEPTION
840 WHEN OTHERS THEN
841 x_activity_source_code := NULL;
842 END;
843
844 END IF;
845
846 -- End of API body.
847 -- Standard check of p_commit.
848 IF FND_API.To_Boolean( p_commit ) THEN
849 COMMIT WORK;
850 END IF;
851 -- Standard call to get message count and if count is 1, get message info.
852 FND_MSG_PUB.Count_And_Get
853 (p_count => x_msg_count,
854 p_data => x_msg_data
855 );
856 EXCEPTION
857 WHEN FND_API.G_EXC_ERROR THEN
858 ROLLBACK TO Get_EAM_Act_Source_Default_PVT;
859 x_return_status := FND_API.G_RET_STS_ERROR ;
860
861 FND_MSG_PUB.Count_And_Get
862 (p_count => x_msg_count,
863 p_data => x_msg_data
864 );
865 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
866 ROLLBACK TO Get_EAM_Act_Source_Default_PVT;
867 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
868
869 FND_MSG_PUB.Count_And_Get
870 (p_count => x_msg_count,
871 p_data => x_msg_data
872 );
873 WHEN OTHERS THEN
874 ROLLBACK TO Get_EAM_Act_Source_Default_PVT;
875 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
876
877 IF FND_MSG_PUB.Check_Msg_Level
878 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
879 THEN
880 FND_MSG_PUB.Add_Exc_Msg
881 (G_PKG_NAME,
882 l_api_name
883 );
884 END IF;
885 FND_MSG_PUB.Count_And_Get
886 (p_count => x_msg_count,
887 p_data => x_msg_data
888 );
889 END Get_EAM_Act_Source_Default;
890
891
892
893 /********************************************************************/
894 -- API to obtain eAM Mass Load Defaults
895 /********************************************************************/
896
897
898 PROCEDURE Get_EAM_Shutdown_Default
899 ( p_api_version IN NUMBER,
900 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
901 p_commit IN VARCHAR2 := FND_API.G_FALSE,
905 x_msg_data OUT NOCOPY VARCHAR2,
902 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
903 x_return_status OUT NOCOPY VARCHAR2,
904 x_msg_count OUT NOCOPY NUMBER,
906 p_primary_item_id IN NUMBER,
907 p_organization_id IN NUMBER,
908 p_maintenance_object_type IN NUMBER,
909 p_maintenance_object_id IN NUMBER,
910 p_rebuild_item_id IN NUMBER,
911 x_shutdown_type_code OUT NOCOPY NUMBER
912 )
913 IS
914 l_api_name CONSTANT VARCHAR2(30) := 'Get_EAM_Shutdown_Default';
915 l_api_version CONSTANT NUMBER := 1.0;
916
917 BEGIN
918 -- Standard Start of API savepoint
919 SAVEPOINT Get_EAM_Shutdown_Default_PVT;
920 -- Standard call to check for call compatibility.
921 IF NOT FND_API.Compatible_API_Call (l_api_version,
922 p_api_version,
923 l_api_name,
924 G_PKG_NAME)
925 THEN
926 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
927 END IF;
928 -- Initialize message list if p_init_msg_list is set to TRUE.
929 IF FND_API.to_Boolean( p_init_msg_list ) THEN
930 FND_MSG_PUB.initialize;
931 END IF;
932 -- Initialize API return status to success
933 x_return_status := FND_API.G_RET_STS_SUCCESS;
934 -- API body
935
936 -- Find the Shutdown Type from the Association Table
937 IF (p_maintenance_object_type IN (1,2) ) THEN -- 'MSN', 'MSI'
938 BEGIN
939 SELECT MEAA.shutdown_type_code
940 INTO x_shutdown_type_code
941 FROM MTL_EAM_ASSET_ACTIVITIES MEAA
942 WHERE MEAA.organization_id = p_organization_id
943 AND MEAA.asset_activity_id = p_primary_item_id
944 AND MEAA.maintenance_object_id = p_maintenance_object_id
945 AND MEAA.maintenance_object_type = p_maintenance_object_type
946 AND NVL(MEAA.tmpl_flag,'N') = 'N';
947 EXCEPTION
948 WHEN OTHERS THEN
949 x_shutdown_type_code := NULL;
950 END;
951 END IF;
952
953 IF (p_maintenance_object_type = 3) THEN -- 'CII'
954 BEGIN
955 SELECT MEAA.shutdown_type_code
956 INTO x_shutdown_type_code
957 FROM MTL_EAM_ASSET_ACTIVITIES MEAA
958 WHERE MEAA.organization_id = p_organization_id
959 AND MEAA.asset_activity_id = p_primary_item_id
960 AND MEAA.maintenance_object_id = p_rebuild_item_id
961 AND MEAA.maintenance_object_type = 2 ;-- 'MSI'
962 EXCEPTION
963 WHEN OTHERS THEN
964 x_shutdown_type_code := NULL;
965 END;
966
967 END IF;
968
969 -- Find the Activity source from the MSI or MSN Tables based on maintenance_object_source
970
971 IF (x_shutdown_type_code IS NULL) THEN
972 BEGIN
973 SELECT MSI.eam_act_shutdown_status
974 INTO x_shutdown_type_code
975 FROM MTL_SYSTEM_ITEMS MSI
976 WHERE MSI.inventory_item_id = p_primary_item_id
977 AND MSI.organization_id = p_organization_id;
978 EXCEPTION
979 WHEN OTHERS THEN
980 x_shutdown_type_code := NULL;
981 END;
982
983 END IF;
984
985 -- End of API body.
986 -- Standard check of p_commit.
987 IF FND_API.To_Boolean( p_commit ) THEN
988 COMMIT WORK;
989 END IF;
990 -- Standard call to get message count and if count is 1, get message info.
991 FND_MSG_PUB.Count_And_Get
992 (p_count => x_msg_count,
993 p_data => x_msg_data
994 );
995 EXCEPTION
996 WHEN FND_API.G_EXC_ERROR THEN
997 ROLLBACK TO Get_EAM_Shutdown_Default_PVT;
998 x_return_status := FND_API.G_RET_STS_ERROR ;
999
1000 FND_MSG_PUB.Count_And_Get
1001 (p_count => x_msg_count,
1002 p_data => x_msg_data
1003 );
1004 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1005 ROLLBACK TO Get_EAM_Shutdown_Default_PVT;
1006 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1007
1008 FND_MSG_PUB.Count_And_Get
1009 (p_count => x_msg_count,
1010 p_data => x_msg_data
1011 );
1012 WHEN OTHERS THEN
1013 ROLLBACK TO Get_EAM_Shutdown_Default_PVT;
1014 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1015
1016 IF FND_MSG_PUB.Check_Msg_Level
1017 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1018 THEN
1019 FND_MSG_PUB.Add_Exc_Msg
1020 (G_PKG_NAME,
1021 l_api_name
1022 );
1023 END IF;
1024 FND_MSG_PUB.Count_And_Get
1025 (p_count => x_msg_count,
1026 p_data => x_msg_data
1027 );
1028 END Get_EAM_Shutdown_Default;
1029
1030
1031
1032 /********************************************************************/
1033 -- API to obtain eAM Mass Load Defaults
1037 PROCEDURE Get_EAM_Notification_Default
1034 /********************************************************************/
1035
1036
1038 ( p_api_version IN NUMBER,
1039 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1040 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1041 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1042 x_return_status OUT NOCOPY VARCHAR2,
1043 x_msg_count OUT NOCOPY NUMBER,
1044 x_msg_data OUT NOCOPY VARCHAR2,
1045 p_primary_item_id IN NUMBER,
1046 p_organization_id IN NUMBER,
1047 p_maintenance_object_type IN NUMBER,
1048 p_maintenance_object_id IN NUMBER,
1049 p_rebuild_item_id IN NUMBER,
1050 x_notification_flag OUT NOCOPY VARCHAr2
1051 )
1052 IS
1053 l_api_name CONSTANT VARCHAR2(30) := 'Get_EAM_Notification_Default';
1054 l_api_version CONSTANT NUMBER := 1.0;
1055
1056
1057 BEGIN
1058 -- Standard Start of API savepoint
1059 SAVEPOINT Get_EAM_Notification_Def_PVT;
1060 -- Standard call to check for call compatibility.
1061 IF NOT FND_API.Compatible_API_Call (l_api_version,
1062 p_api_version,
1063 l_api_name,
1064 G_PKG_NAME)
1065 THEN
1066 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1067 END IF;
1068 -- Initialize message list if p_init_msg_list is set to TRUE.
1069 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1070 FND_MSG_PUB.initialize;
1071 END IF;
1072 -- Initialize API return status to success
1073 x_return_status := FND_API.G_RET_STS_SUCCESS;
1074 -- API body
1075 /*
1076 -- Find the Shutdown Type from the Association Table
1077 IF (p_maintenance_object_type IN (1,2) ) THEN -- 'MSN', 'MSI'
1078 BEGIN
1079 SELECT MEAA.notification_required
1080 INTO x_notification_flag
1081 FROM MTL_EAM_ASSET_ACTIVITIES MEAA
1082 WHERE MEAA.organization_id = p_organization_id
1083 AND MEAA.asset_activity_id = p_primary_item_id
1084 AND MEAA.maintenance_object_id = p_maintenance_object_id
1085 AND MEAA.maintenance_object_type = p_maintenance_object_type
1086 AND NVL(MEAA.tmpl_flag,'N') = 'N';
1087 EXCEPTION
1088 WHEN OTHERS THEN
1089 x_notification_flag := NULL;
1090 END;
1091 END IF;
1092
1093 IF (p_maintenance_object_type = 3) THEN -- 'CII'
1094 BEGIN
1095 SELECT MEAA.notification_required
1096 INTO x_notification_flag
1097 FROM MTL_EAM_ASSET_ACTIVITIES MEAA
1098 WHERE MEAA.organization_id = p_organization_id
1099 AND MEAA.asset_activity_id = p_primary_item_id
1100 AND MEAA.maintenance_object_id = p_rebuild_item_id
1101 AND MEAA.maintenance_object_type = 2 ;-- 'MSI'
1102 EXCEPTION
1103 WHEN OTHERS THEN
1104 x_notification_flag := NULL;
1105 END;
1106
1107 END IF;
1108 */
1109 -- Find the Activity source from the MSI or MSN Tables based on maintenance_object_source
1110
1111 IF (x_notification_flag IS NULL) THEN
1112 BEGIN
1113 SELECT MSI.eam_act_notification_flag
1114 INTO x_notification_flag
1115 FROM MTL_SYSTEM_ITEMS MSI
1116 WHERE MSI.inventory_item_id = p_primary_item_id
1117 AND MSI.organization_id = p_organization_id;
1118 EXCEPTION
1119 WHEN OTHERS THEN
1120 x_notification_flag := NULL;
1121 END;
1122
1123 END IF;
1124
1125 -- End of API body.
1126 -- Standard check of p_commit.
1127 IF FND_API.To_Boolean( p_commit ) THEN
1128 COMMIT WORK;
1129 END IF;
1130 -- Standard call to get message count and if count is 1, get message info.
1131 FND_MSG_PUB.Count_And_Get
1132 (p_count => x_msg_count,
1133 p_data => x_msg_data
1134 );
1135 EXCEPTION
1136 WHEN FND_API.G_EXC_ERROR THEN
1137 ROLLBACK TO Get_EAM_Notification_Def_PVT;
1138 x_return_status := FND_API.G_RET_STS_ERROR ;
1139
1140 FND_MSG_PUB.Count_And_Get
1141 (p_count => x_msg_count,
1142 p_data => x_msg_data
1143 );
1144 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1145 ROLLBACK TO Get_EAM_Notification_Def_PVT;
1146 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1147
1148 FND_MSG_PUB.Count_And_Get
1149 (p_count => x_msg_count,
1150 p_data => x_msg_data
1151 );
1152 WHEN OTHERS THEN
1153 ROLLBACK TO Get_EAM_Notification_Def_PVT;
1154 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1155
1156 IF FND_MSG_PUB.Check_Msg_Level
1157 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1158 THEN
1159 FND_MSG_PUB.Add_Exc_Msg
1160 (G_PKG_NAME,
1161 l_api_name
1162 );
1163 END IF;
1164 FND_MSG_PUB.Count_And_Get
1165 (p_count => x_msg_count,
1169
1166 p_data => x_msg_data
1167 );
1168 END Get_EAM_Notification_Default;
1170
1171
1172 /********************************************************************/
1173 -- API to obtain eAM Mass Load Defaults
1174 /********************************************************************/
1175
1176
1177 PROCEDURE Get_EAM_Tagout_Default
1178 ( p_api_version IN NUMBER,
1179 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1180 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1181 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1182 x_return_status OUT NOCOPY VARCHAR2,
1183 x_msg_count OUT NOCOPY NUMBER,
1184 x_msg_data OUT NOCOPY VARCHAR2,
1185 p_primary_item_id IN NUMBER,
1186 p_organization_id IN NUMBER,
1187 p_maintenance_object_type IN NUMBER,
1188 p_maintenance_object_id IN NUMBER,
1189 p_rebuild_item_id IN NUMBER,
1190 x_tagout_required OUT NOCOPY VARCHAR2
1191 )
1192 IS
1193 l_api_name CONSTANT VARCHAR2(30) := 'Get_EAM_Tagout_Default';
1194 l_api_version CONSTANT NUMBER := 1.0;
1195
1196
1197 BEGIN
1198 -- Standard Start of API savepoint
1199 SAVEPOINT Get_EAM_Tagout_Default_PVT;
1200 -- Standard call to check for call compatibility.
1201 IF NOT FND_API.Compatible_API_Call (l_api_version,
1202 p_api_version,
1203 l_api_name,
1204 G_PKG_NAME)
1205 THEN
1206 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1207 END IF;
1208 -- Initialize message list if p_init_msg_list is set to TRUE.
1209 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1210 FND_MSG_PUB.initialize;
1211 END IF;
1212 -- Initialize API return status to success
1213 x_return_status := FND_API.G_RET_STS_SUCCESS;
1214 -- API body
1215
1216 -- Find the Shutdown Type from the Association Table
1217 IF (p_maintenance_object_type IN (1,2) ) THEN -- 'MSN', 'MSI'
1218 BEGIN
1219 SELECT MEAA.tagging_required_flag
1220 INTO x_tagout_required
1221 FROM MTL_EAM_ASSET_ACTIVITIES MEAA
1222 WHERE MEAA.organization_id = p_organization_id
1223 AND MEAA.asset_activity_id = p_primary_item_id
1224 AND MEAA.maintenance_object_id = p_maintenance_object_id
1225 AND MEAA.maintenance_object_type = p_maintenance_object_type
1226 AND NVL(MEAA.tmpl_flag,'N') = 'N';
1227 EXCEPTION
1228 WHEN OTHERS THEN
1229 x_tagout_required := NULL;
1230 END;
1231 END IF;
1232
1233 IF (p_maintenance_object_type = 3) THEN -- 'CII'
1234 BEGIN
1235 SELECT MEAA.tagging_required_flag
1236 INTO x_tagout_required
1237 FROM MTL_EAM_ASSET_ACTIVITIES MEAA
1238 WHERE MEAA.organization_id = p_organization_id
1239 AND MEAA.asset_activity_id = p_primary_item_id
1240 AND MEAA.maintenance_object_id = p_rebuild_item_id
1241 AND MEAA.maintenance_object_type = 2 ;-- 'MSI'
1242 EXCEPTION
1243 WHEN OTHERS THEN
1244 x_tagout_required := NULL;
1245 END;
1246
1247 END IF;
1248
1249 -- End of API body.
1250 -- Standard check of p_commit.
1251 IF FND_API.To_Boolean( p_commit ) THEN
1252 COMMIT WORK;
1253 END IF;
1254 -- Standard call to get message count and if count is 1, get message info.
1255 FND_MSG_PUB.Count_And_Get
1256 (p_count => x_msg_count,
1257 p_data => x_msg_data
1258 );
1259 EXCEPTION
1260 WHEN FND_API.G_EXC_ERROR THEN
1261 ROLLBACK TO Get_EAM_Tagout_Default_PVT;
1262 x_return_status := FND_API.G_RET_STS_ERROR ;
1263
1264 FND_MSG_PUB.Count_And_Get
1265 (p_count => x_msg_count,
1266 p_data => x_msg_data
1267 );
1268 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1269 ROLLBACK TO Get_EAM_Tagout_Default_PVT;
1270 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1271
1272 FND_MSG_PUB.Count_And_Get
1273 (p_count => x_msg_count,
1274 p_data => x_msg_data
1275 );
1276 WHEN OTHERS THEN
1277 ROLLBACK TO Get_EAM_Tagout_Default_PVT;
1278 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1279
1280 IF FND_MSG_PUB.Check_Msg_Level
1281 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1282 THEN
1283 FND_MSG_PUB.Add_Exc_Msg
1284 (G_PKG_NAME,
1285 l_api_name
1286 );
1287 END IF;
1288 FND_MSG_PUB.Count_And_Get
1289 (p_count => x_msg_count,
1290 p_data => x_msg_data
1291 );
1292 END Get_EAM_Tagout_Default;
1293
1294
1295
1296 /********************************************************************/
1297 -- API to obtain eAM Mass Load Defaults
1298 /********************************************************************/
1302 PROCEDURE Get_EAM_Owning_Dept_Default
1299
1300
1301
1303 ( p_api_version IN NUMBER,
1304 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1305 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1306 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1307 x_return_status OUT NOCOPY VARCHAR2,
1308 x_msg_count OUT NOCOPY NUMBER,
1309 x_msg_data OUT NOCOPY VARCHAR2,
1310 p_primary_item_id IN NUMBER,
1311 p_organization_id IN NUMBER,
1312 p_maintenance_object_type IN NUMBER,
1313 p_maintenance_object_id IN NUMBER,
1314 p_rebuild_item_id IN NUMBER,
1315 x_owning_department_id OUT NOCOPY NUMBER
1316 )
1317 IS
1318 l_api_name CONSTANT VARCHAR2(30) := 'Get_EAM_Owning_Dept_Default';
1319 l_api_version CONSTANT NUMBER := 1.0;
1320 l_act_assoc_id NUMBER;
1321
1322 BEGIN
1323 -- Standard Start of API savepoint
1324 SAVEPOINT Get_EAM_Owning_Dept_Def_PVT;
1325 -- Standard call to check for call compatibility.
1326 IF NOT FND_API.Compatible_API_Call (l_api_version,
1327 p_api_version,
1328 l_api_name,
1329 G_PKG_NAME)
1330 THEN
1331 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1332 END IF;
1333 -- Initialize message list if p_init_msg_list is set to TRUE.
1334 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1335 FND_MSG_PUB.initialize;
1336 END IF;
1337 -- Initialize API return status to success
1338 x_return_status := FND_API.G_RET_STS_SUCCESS;
1339 -- API body
1340
1341 -- Find the Shutdown Type from the Association Table
1342 IF (p_maintenance_object_type = 1 ) THEN -- 'MSN', 'MSI'
1343 BEGIN
1344 SELECT MEAA.owning_department_id
1345 INTO x_owning_department_id
1346 FROM MTL_EAM_ASSET_ACTIVITIES MEAA
1347 WHERE MEAA.asset_activity_id = p_primary_item_id
1348 AND MEAA.maintenance_object_id = p_maintenance_object_id
1349 AND MEAA.maintenance_object_type = p_maintenance_object_type
1350 AND NVL(MEAA.tmpl_flag,'N') = 'N';
1351 EXCEPTION
1352 WHEN OTHERS THEN
1353 x_owning_department_id := NULL;
1354 END;
1355 END IF;
1356
1357 IF (p_maintenance_object_type IN( 2,3) ) THEN -- 'CII'
1358 BEGIN
1359 SELECT MEAA.activity_association_id
1360 INTO l_act_assoc_id
1361 FROM MTL_EAM_ASSET_ACTIVITIES MEAA
1362 WHERE MEAA.asset_activity_id = p_primary_item_id
1363 AND MEAA.maintenance_object_id = p_maintenance_object_id
1364 AND MEAA.maintenance_object_type = p_maintenance_object_type
1365 AND NVL(MEAA.tmpl_flag,'N') = 'N';
1366
1367 SELECT EOMD.OWNING_DEPARTMENT_ID
1368 INTO x_owning_department_id
1369 FROM EAM_ORG_MAINT_DEFAULTS EOMD
1370 WHERE EOMD.organization_id = p_organization_id
1371 AND EOMD.OBJECT_ID = l_act_assoc_id
1372 AND EOMD.OBJECT_TYPE IN (40,60) ;
1373 EXCEPTION
1374 WHEN OTHERS THEN
1375 x_owning_department_id := NULL;
1376 END;
1377
1378 END IF;
1379
1380 -- Find the Activity source from the MSI or MSN Tables based on maintenance_object_source
1381
1382 IF (x_owning_department_id IS NULL AND p_maintenance_object_type = 3) THEN
1383 BEGIN
1384 SELECT EOMD.OWNING_DEPARTMENT_ID
1385 INTO x_owning_department_id
1386 FROM EAM_ORG_MAINT_DEFAULTS EOMD
1387 WHERE EOMD.organization_id = p_organization_id
1388 AND EOMD.OBJECT_ID = p_maintenance_object_id
1389 AND EOMD.OBJECT_TYPE = 50 ;
1390 EXCEPTION
1391 WHEN OTHERS THEN
1392 x_owning_department_id := NULL;
1393 END;
1394
1395 END IF;
1396
1397 -- Find the Activity source from the EAM Parameters Tables based on maintenance_object_source
1398
1399 IF (x_owning_department_id IS NULL AND p_maintenance_object_type IN (2,3) ) THEN
1400 BEGIN
1401 SELECT WEP.default_department_id
1402 INTO x_owning_department_id
1403 FROM WIP_EAM_PARAMETERS WEP
1404 WHERE WEP.organization_id = p_organization_id;
1405 EXCEPTION
1406 WHEN OTHERS THEN
1407 x_owning_department_id := NULL;
1408 END;
1409
1410 END IF;
1411
1412 -- End of API body.
1413 -- Standard check of p_commit.
1414 IF FND_API.To_Boolean( p_commit ) THEN
1415 COMMIT WORK;
1416 END IF;
1417 -- Standard call to get message count and if count is 1, get message info.
1418 FND_MSG_PUB.Count_And_Get
1419 (p_count => x_msg_count,
1420 p_data => x_msg_data
1421 );
1422 EXCEPTION
1423 WHEN FND_API.G_EXC_ERROR THEN
1424 ROLLBACK TO Get_EAM_Owning_Dept_Def_PVT;
1425 x_return_status := FND_API.G_RET_STS_ERROR ;
1426
1427 FND_MSG_PUB.Count_And_Get
1428 (p_count => x_msg_count,
1429 p_data => x_msg_data
1430 );
1431 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1432 ROLLBACK TO Get_EAM_Owning_Dept_Def_PVT;
1433 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1434
1435 FND_MSG_PUB.Count_And_Get
1436 (p_count => x_msg_count,
1437 p_data => x_msg_data
1438 );
1439 WHEN OTHERS THEN
1440 ROLLBACK TO Get_EAM_Owning_Dept_Def_PVT;
1441 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1442
1443 IF FND_MSG_PUB.Check_Msg_Level
1444 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1445 THEN
1446 FND_MSG_PUB.Add_Exc_Msg
1447 (G_PKG_NAME,
1448 l_api_name
1449 );
1450 END IF;
1451 FND_MSG_PUB.Count_And_Get
1452 (p_count => x_msg_count,
1453 p_data => x_msg_data
1454 );
1455 END Get_EAM_Owning_Dept_Default;
1456
1457
1458 END WIP_EAMWORKORDER_PVT;
[Home] [Help]