[Home] [Help]
PACKAGE BODY: APPS.EAM_ACTIVITYUTILITIES_PVT
Source
1 PACKAGE BODY EAM_ActivityUtilities_PVT AS
2 /* $Header: EAMVAAUB.pls 120.3 2005/09/01 01:43:51 kmurthy noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='EAM_ActivityUtilities_PVT';
5
6 -- ======================================================================
7 -- Utility Procedures
8 PROCEDURE Validate_Organization
9 ( p_organization_id IN NUMBER,
10 p_organization_code IN VARCHAR2,
11 x_return_status OUT NOCOPY VARCHAR2,
12 x_organization_id OUT NOCOPY NUMBER,
13 x_organization_code OUT NOCOPY VARCHAR2
14 )
15
16 IS
17
18 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Organization';
19 l_module varchar2(200);
20 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
21 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level ;
22 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
23 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
24
25 BEGIN
26 if(l_ulog) then
27 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
28 end if;
29
30 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
31 '----- Entering EAM_ActivityUtilities_PVT.Validate_Organization -----'
32 || 'p_organization_id=' || p_organization_id
33 || 'p_organization_code=' || p_organization_code);
34 end if;
35
36 -- transfer input data to output data
37 x_organization_id := p_organization_id;
38 x_organization_code := p_organization_code;
39
40 IF p_organization_id IS NOT NULL AND p_organization_id <> FND_API.G_MISS_NUM THEN
41 -- organization_id takes precedence
42 BEGIN
43 SELECT organization_code INTO x_organization_code
44 FROM mtl_parameters
45 WHERE organization_id = p_organization_id;
46 EXCEPTION
47 WHEN NO_DATA_FOUND THEN
48 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_INVALID_ORG_ID');
49 -- FND_MESSAGE.SET_ENCODED('Organization Id invalid.');
50 EAM_ActivityUtilities_PVT.Add_Message(FND_MSG_PUB.G_MSG_LVL_ERROR);
51 RAISE FND_API.G_EXC_ERROR;
52 END;
53
54 ELSIF p_organization_code IS NOT NULL AND p_organization_code <> FND_API.G_MISS_CHAR THEN
55
56 BEGIN
57 SELECT organization_id INTO x_organization_id
58 FROM mtl_parameters
59 WHERE organization_code = p_organization_code;
60 EXCEPTION
61 WHEN NO_DATA_FOUND THEN
62 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_INVALID_ORG_CODE');
63 -- FND_MESSAGE.SET_ENCODED('Organization Code invalid.');
64 EAM_ActivityUtilities_PVT.Add_Message(FND_MSG_PUB.G_MSG_LVL_ERROR);
65 RAISE FND_API.G_EXC_ERROR;
66 END;
67 ELSE
68 -- Error: both organization id and code are NULL
69 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_ORG_ID_CODE_NULL');
70 -- FND_MESSAGE.SET_ENCODED('Organization id and organization code cannot be both NULL.');
71 EAM_ActivityUtilities_PVT.Add_Message(FND_MSG_PUB.G_MSG_LVL_ERROR);
72 RAISE FND_API.G_EXC_ERROR;
73 END IF;
74
75 x_return_status := FND_API.G_RET_STS_SUCCESS;
76
77 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
78 '----- Exiting EAM_ActivityUtilities_PVT.Validate_Organization -----');
79 end if;
80
81 EXCEPTION
82 WHEN FND_API.G_EXC_ERROR THEN
83
84 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
85 'EAM_ActivityUtilities_PVT.Validate_Organization: error.' ||
86 'p_organization_id=' || p_organization_id ||
87 '; p_organization_code=' || p_organization_code);
88 end if;
89
90 x_return_status := FND_API.G_RET_STS_ERROR;
91
92 WHEN OTHERS THEN
93
94 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
95 'EAM_ActivityUtilities_PVT.Validate_Organization: unexpected error.' ||
96 'p_organization_id=' || p_organization_id ||
97 '; p_organization_code=' || p_organization_code);
98 end if;
99
100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
101 END;
102
103 -- ----------------------------------------------------------------------
104 PROCEDURE Validate_Work_Order
105 (
106 p_work_order_rec IN EAM_Activity_PUB.Work_Order_Rec_Type,
107 x_return_status OUT NOCOPY VARCHAR2,
108 x_work_order_rec OUT NOCOPY EAM_Activity_PUB.Work_Order_Rec_Type
109 )
110
111 IS
112
113 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Work_Order';
114 l_module varchar2(200);
115 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
116 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
117 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
118 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
119
120 l_validate_org_ret_sts VARCHAR2(1);
121 l_x_work_order_rec EAM_Activity_PUB.Work_Order_Rec_Type;
122 l_temp_org_id NUMBER;
123 l_temp_org_code VARCHAR2(3);
124
125 BEGIN
126 if(l_ulog) then
127 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
128 end if;
129
130 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
131 '----- Entering EAM_ActivityUtilities_PVT.Validate_Work_Order -----'
132 || 'p_work_order_rec.Organization_Id=' || p_work_order_rec.Organization_Id
133 || 'p_work_order_rec.Organization_Code=' || p_work_order_rec.Organization_Code
134 || 'p_work_order_rec.Wip_Entity_Id=' || p_work_order_rec.Wip_Entity_Id
135 || 'p_work_order_rec.Wip_Entity_Name=' || p_work_order_rec.Wip_Entity_Name);
136 end if;
137
138 -- transfer input data to output data
139 l_x_work_order_rec := p_work_order_rec;
140
141 IF p_work_order_rec.wip_entity_id IS NOT NULL THEN
142 BEGIN
143 -- wip_entity_id takes precedence
144 SELECT wip_entity_name,
145 organization_id
146 INTO l_x_work_order_rec.wip_entity_name,
147 l_x_work_order_rec.organization_id
148 FROM wip_entities
149 WHERE wip_entity_id = p_work_order_rec.wip_entity_id;
150 EXCEPTION
151 WHEN OTHERS THEN
152 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_INVALID_WIP_ENTITY_ID');
153 -- FND_MESSAGE.SET_ENCODED('Wip Entity Id invalid.');
154 EAM_ActivityUtilities_PVT.Add_Message(FND_MSG_PUB.G_MSG_LVL_ERROR);
155 RAISE FND_API.G_EXC_ERROR;
156 END;
157
158 -- also need to set org code
159 Validate_Organization(
160 p_organization_id => l_x_work_order_rec.organization_id,
161 p_organization_code => p_work_order_rec.organization_code,
162 x_return_status => l_validate_org_ret_sts,
163 x_organization_id => l_temp_org_id,
164 x_organization_code => l_temp_org_code
165 );
166 l_x_work_order_rec.organization_id := l_temp_org_id;
167 l_x_work_order_rec.organization_code := l_temp_org_code;
168
169 IF l_validate_org_ret_sts <> FND_API.G_RET_STS_SUCCESS THEN
170 EAM_ActivityUtilities_PVT.Add_Message(
171 'EAM_ActivityUtilities_PVT.Validate_Work_Order: wip_entity_id: organization validation failed.');
172 RAISE FND_API.G_EXC_ERROR;
173 END IF;
174
175 ELSIF p_work_order_rec.wip_entity_name IS NOT NULL THEN
176 -- For wip_entity_name, need to validate org first
177 Validate_Organization(
178 p_organization_id => p_work_order_rec.organization_id,
179 p_organization_code => p_work_order_rec.organization_code,
180 x_return_status => l_validate_org_ret_sts,
181 x_organization_id => l_temp_org_id,
182 x_organization_code => l_temp_org_code
183 );
184 l_x_work_order_rec.organization_id := l_temp_org_id;
185 l_x_work_order_rec.organization_code := l_temp_org_code;
186
187 IF l_validate_org_ret_sts <> FND_API.G_RET_STS_SUCCESS THEN
188 EAM_ActivityUtilities_PVT.Add_Message(
189 'EAM_ActivityUtilities_PVT.Validate_Work_Order: wip_entity_name: organization validation failed.');
190 RAISE FND_API.G_EXC_ERROR;
191 END IF;
192
193 BEGIN
194 SELECT wip_entity_id INTO l_x_work_order_rec.wip_entity_id
195 FROM wip_entities
196 WHERE organization_id = l_x_work_order_rec.organization_id
197 AND wip_entity_name = l_x_work_order_rec.wip_entity_name;
198 EXCEPTION
199 WHEN OTHERS THEN
200 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_INVALID_WIP_ENT_NAME');
201 -- FND_MESSAGE.SET_ENCODED('Wip Entity Name invalid.');
202 EAM_ActivityUtilities_PVT.Add_Message(FND_MSG_PUB.G_MSG_LVL_ERROR);
203 RAISE FND_API.G_EXC_ERROR;
204 END;
205
206 ELSE
207 -- Error: both wip entity id and code are NULL
208 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_WIP_ENT_ID_NAME_NULL');
209 -- FND_MESSAGE.SET_ENCODED('Wip Entity Id and Name cannot be both NULL.');
210 EAM_ActivityUtilities_PVT.Add_Message(FND_MSG_PUB.G_MSG_LVL_ERROR);
211 RAISE FND_API.G_EXC_ERROR;
212 END IF;
213
214 -- Assign output
215 x_work_order_rec := l_x_work_order_rec;
216
217 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
218 '----- Exiting EAM_ActivityUtilities_PVT.Validate_Work_Order -----');
219 end if;
220
221 EXCEPTION
222 WHEN FND_API.G_EXC_ERROR THEN
223
224 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
225 'EAM_ActivityUtilities_PVT.Validate_Work_Order: error.'||
226 'wip_entity_id=' || p_work_order_rec.wip_entity_id ||
227 '; wip_entity_name=' || p_work_order_rec.wip_entity_name ||
228 '; organization_id=' || p_work_order_rec.organization_id ||
229 '; organization_code=' || p_work_order_rec.organization_code);
230 end if;
231
232 x_return_status := FND_API.G_RET_STS_ERROR;
233
234 WHEN OTHERS THEN
235
236 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
237 'EAM_ActivityUtilities_PVT.Validate_Work_Order: unexpected error.'||
238 'wip_entity_id=' || p_work_order_rec.wip_entity_id ||
239 '; wip_entity_name=' || p_work_order_rec.wip_entity_name ||
240 '; organization_id=' || p_work_order_rec.organization_id ||
241 '; organization_code=' || p_work_order_rec.organization_code);
242 end if;
243
244 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
245 END;
246
247 -- ----------------------------------------------------------------------
248 FUNCTION Get_Item_Concatenated_Segments(
249 p_organization_id IN NUMBER,
250 p_inventory_item_id IN NUMBER
251 )
252 RETURN VARCHAR2
253 IS
254
255 l_api_name CONSTANT VARCHAR2(30) := 'Get_Item_Concatenated_Segments';
256 l_module varchar2(200);
257 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
258 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level ;
259 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
260 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
261
262 l_item_concatenated_segments VARCHAR2(40);
263 BEGIN
264 if(l_ulog) then
265 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
266 end if;
267
268 IF p_organization_id IS NULL OR p_inventory_item_id IS NULL THEN
269 RETURN NULL;
270 ELSE
271 SELECT concatenated_segments INTO l_item_concatenated_segments
272 FROM MTL_SYSTEM_ITEMS_B_KFV
273 WHERE organization_id = p_organization_id
274 AND inventory_item_id = p_inventory_item_id;
275
276 RETURN l_item_concatenated_segments;
277 END IF;
278
279 EXCEPTION
280
281 WHEN NO_DATA_FOUND THEN
282 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_NO_ITEM_NAME');
283 -- FND_MESSAGE.SET_ENCODED('Cannot find concatenated segment Item name.');
284 EAM_ActivityUtilities_PVT.Add_Message(FND_MSG_PUB.G_MSG_LVL_ERROR);
285
286 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
287 'EAM_ActivityUtilities_PVT.Get_Item_Concatenated_Segments: error.' ||
288 'p_organization_id=' || p_organization_id ||
289 '; p_inventory_item_id=' || p_inventory_item_id);
290 end if;
291
292 RAISE FND_API.G_EXC_ERROR;
293
294 WHEN OTHERS THEN
295
296 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
297 'EAM_ActivityUtilities_PVT.Get_Item_Concatenated_Segments: unexpected error.' ||
298 'p_organization_id=' || p_organization_id ||
299 '; p_inventory_item_id=' || p_inventory_item_id);
300 end if;
301
302 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
303 END;
304
305 -- ----------------------------------------------------------------------
306 FUNCTION Get_Act_Id_From_Work_Order(
307 p_wip_entity_id IN NUMBER
308 )
309 RETURN NUMBER
310 IS
311 l_api_name CONSTANT VARCHAR2(30) := 'Get_Act_Id_From_Work_Order';
312 l_module varchar2(200);
313 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
314 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level ;
315 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
316 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
317
318 l_activity_id NUMBER;
319 BEGIN
320 if(l_ulog) then
321 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
322 end if;
323
324 IF p_wip_entity_id IS NULL THEN
325 RETURN NULL;
326 ELSE
327 SELECT primary_item_id INTO l_activity_id
328 FROM wip_discrete_jobs
329 WHERE wip_entity_id = p_wip_entity_id;
330
331 RETURN l_activity_id;
332 END IF;
333
334 EXCEPTION
335 WHEN OTHERS THEN
336 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
337 'EAM_ActivityUtilities_PVT.Get_Act_Id_From_Work_Order: unexpected error.'||
338 'p_wip_entity_id=' || p_wip_entity_id);
339 end if;
340
341 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
342 END;
343
344 -- ----------------------------------------------------------------------
345 FUNCTION Get_Org_Id_From_Work_Order(
346 p_wip_entity_id IN NUMBER
347 )
348 RETURN NUMBER
349 IS
350 l_api_name CONSTANT VARCHAR2(30) := 'Get_Org_Id_From_Work_Order';
351 l_module varchar2(200);
352 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
353 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level ;
354 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
355 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
356
357 l_org_id NUMBER;
358 BEGIN
359 if(l_ulog) then
360 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
361 end if;
362
363 IF p_wip_entity_id IS NULL THEN
364 RETURN NULL;
365 ELSE
366 SELECT organization_id INTO l_org_id
367 FROM wip_discrete_jobs
368 WHERE wip_entity_id = p_wip_entity_id;
369
370 RETURN l_org_id;
371 END IF;
372
373 EXCEPTION
374 WHEN OTHERS THEN
375 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
376 'EAM_ActivityUtilities_PVT.Get_Org_Id_From_Work_Order: unexpected error.'||
377 'p_wip_entity_id=' || p_wip_entity_id);
378 end if;
379
380 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
381 END;
382
383 -- ----------------------------------------------------------------------
384 FUNCTION Get_Department_Code(
385 p_organization_id IN NUMBER,
386 p_department_id IN NUMBER
387 )
388 RETURN VARCHAR2
389 IS
390 l_api_name CONSTANT VARCHAR2(30) := 'Get_Department_Code';
391 l_module varchar2(200);
392 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
393 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level ;
394 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
395 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
396
397 l_department_code VARCHAR2(10);
398 BEGIN
399 if(l_ulog) then
400 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
401 end if;
402 IF p_organization_id IS NULL OR p_department_id IS NULL THEN
403 RETURN NULL;
404 ELSE
405 SELECT department_code INTO l_department_code
406 FROM bom_departments
407 WHERE organization_id = p_organization_id
408 and department_id = p_department_id
409 and (disable_date IS NULL
410 or disable_date > sysdate);
411
412 RETURN l_department_code;
413 END IF;
414
415 EXCEPTION
416 WHEN OTHERS THEN
417 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
418 'EAM_ActivityUtilities_PVT.Get_Department_Code: unexpected error.'||
419 'p_organization_id=' || p_organization_id ||
420 '; p_department_id=' || p_department_id);
421 end if;
422
423 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
424 END;
425
426 -- ----------------------------------------------------------------------
427 FUNCTION Get_Resource_Code(
428 p_organization_id IN NUMBER,
429 p_resource_id IN NUMBER
430 )
431 RETURN VARCHAR2
432 IS
433 l_api_name CONSTANT VARCHAR2(30) := 'Get_Resource_Code';
434 l_module varchar2(200);
435 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
436 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level ;
437 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
438 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
439
440 l_resource_code VARCHAR2(10);
441 BEGIN
442 if(l_ulog) then
443 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
444 end if;
445 IF p_organization_id IS NULL OR p_resource_id IS NULL THEN
446 RETURN NULL;
447 ELSE
448 SELECT resource_code INTO l_resource_code
449 FROM bom_resources
450 WHERE organization_id = p_organization_id
451 and resource_id = p_resource_id
452 and (disable_date IS NULL
453 or disable_date > sysdate);
454
455 RETURN l_resource_code;
456 END IF;
457
458 EXCEPTION
459 WHEN OTHERS THEN
460 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
461 'EAM_ActivityUtilities_PVT.Get_Resource_Code: unexpected error.'||
462 'p_organization_id=' || p_organization_id ||
463 '; p_resource_id=' || p_resource_id);
464 end if;
465
466 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
467 END;
468
469 -- ----------------------------------------------------------------------
470 FUNCTION Get_Expense_Account_Id(
471 p_organization_id IN NUMBER
472 )
473 RETURN NUMBER
474 IS
475 l_api_name CONSTANT VARCHAR2(30) := 'Get_Expense_Account_Id';
476 l_module varchar2(200) ;
477 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
478 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
479 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
480 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
481
482 l_expense_account_id NUMBER;
483 BEGIN
484 if(l_ulog) then
485 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
486 end if;
487 IF p_organization_id IS NULL THEN
488 RETURN NULL;
489 ELSE
490 SELECT expense_account INTO l_expense_account_id
491 FROM mtl_parameters
492 WHERE organization_id = p_organization_id;
493
494 RETURN l_expense_account_id;
495 END IF;
496
497 EXCEPTION
498 WHEN OTHERS THEN
499 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
500 'EAM_ActivityUtilities_PVT.Get_Expense_Account_Id: unexpected error.'||
501 'p_organization_id=' || p_organization_id);
502 end if;
503
504 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
505 END;
506
507 -- ----------------------------------------------------------------------
508 PROCEDURE Get_Asset_From_WO(
509 p_wip_entity_id IN NUMBER,
510 x_inventory_item_id OUT NOCOPY NUMBER,
511 x_serial_number OUT NOCOPY VARCHAR2
512 )
513 IS
514 l_api_name CONSTANT VARCHAR2(30) := 'Get_Asset_From_WO';
515 l_module varchar2(200);
516 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
517 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level ;
518 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
519 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
520 BEGIN
521 if(l_ulog) then
522 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
523 end if;
524 IF p_wip_entity_id IS NULL THEN
525 x_inventory_item_id := NULL;
526 x_serial_number := NULL;
527 RETURN;
528 ELSE
529 SELECT asset_group_id, asset_number
530 INTO x_inventory_item_id, x_serial_number
531 FROM wip_discrete_jobs
532 WHERE wip_entity_id = p_wip_entity_id;
533 END IF;
534
535 EXCEPTION
536 WHEN OTHERS THEN
537 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
538 'EAM_ActivityUtilities_PVT.Get_Asset_From_WO: unexpected error.'||
539 'p_wip_entity_id=' || p_wip_entity_id);
540 end if;
541
542 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
543 END;
544
545 -- ----------------------------------------------------------------------
546 FUNCTION Get_Asset_Owning_Dept_Id(
547 p_organization_id IN NUMBER,
548 p_inventory_item_id IN NUMBER,
549 p_serial_number IN VARCHAR2
550 )
551 RETURN NUMBER
552 IS
553 l_api_name CONSTANT VARCHAR2(30) := 'Get_Asset_Owning_Dept_Id';
554 l_module varchar2(200);
555 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
556 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level ;
557 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
558 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
559
560 l_owning_dept_id NUMBER;
561 BEGIN
562 if(l_ulog) then
563 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
564 end if;
565 IF p_organization_id IS NULL OR p_inventory_item_id IS NULL OR p_serial_number IS NULL THEN
566 RETURN NULL;
567 ELSE
568 SELECT owning_department_id INTO l_owning_dept_id
569 FROM mtl_serial_numbers
570 WHERE current_organization_id = p_organization_id
571 AND inventory_item_id = p_inventory_item_id
572 AND serial_number = p_serial_number;
573
574 RETURN l_owning_dept_id;
575 END IF;
576
577 EXCEPTION
578 WHEN OTHERS THEN
579 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
580 'EAM_ActivityUtilities_PVT.Get_Asset_Owning_Dept_Id: unexpected error.'||
581 'p_organization_id=' || p_organization_id ||
582 '; p_inventory_item_id=' || p_inventory_item_id ||
583 '; p_serial_number=' || p_serial_number);
584 end if;
585
586 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
587 END;
588
589 -- ----------------------------------------------------------------------
590 FUNCTION Get_WO_Res_Scheduled_Units(
591 p_organization_id IN NUMBER,
592 p_wip_entity_id IN NUMBER,
593 p_operation_seq_num IN NUMBER,
594 p_resource_seq_num IN NUMBER
595 )
596 RETURN NUMBER
597 IS
598 l_api_name CONSTANT VARCHAR2(30) := 'Get_WO_Res_Scheduled_Units';
599 l_module varchar2(200);
600 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
601 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level ;
602 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
603 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
604
605 l_scheduled_units NUMBER;
606 BEGIN
607 if(l_ulog) then
608 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
609 end if;
610 IF p_organization_id IS NULL OR p_wip_entity_id IS NULL OR
611 p_operation_seq_num IS NULL OR p_resource_seq_num IS NULL
612 THEN
613 RETURN NULL;
614 ELSE
615 SELECT scheduled_units INTO l_scheduled_units
616 FROM wip_operation_resources_v
617 WHERE organization_id = p_organization_id
618 AND wip_entity_id = p_wip_entity_id
619 AND operation_seq_num = p_operation_seq_num
620 AND resource_seq_num = p_resource_seq_num;
621
622 RETURN l_scheduled_units;
623 END IF;
624
625 EXCEPTION
626 WHEN OTHERS THEN
627 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
628 'EAM_ActivityUtilities_PVT.Get_WO_Res_Scheduled_Units: unexpected error.'||
629 'p_organization_id=' || p_organization_id ||
630 '; p_wip_entity_id=' || p_wip_entity_id ||
631 '; p_operation_seq_num=' || p_operation_seq_num ||
632 '; p_resource_seq_num=' || p_resource_seq_num);
633 end if;
634
635 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
636 END;
637
638 -- ----------------------------------------------------------------------
639 FUNCTION Get_Master_Org_Id(
640 p_organization_id IN NUMBER
641 )
642 RETURN NUMBER
643 IS
644 l_api_name CONSTANT VARCHAR2(30) := 'Get_Master_Org_Id';
645 l_module varchar2(200);
646 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
647 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level ;
648 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
649 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
650
651 l_master_org_id NUMBER;
652 BEGIN
653 if(l_ulog) then
654 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
655 end if;
656 IF p_organization_id IS NULL THEN
657 RETURN NULL;
658 ELSE
659 SELECT master_organization_id INTO l_master_org_id
660 FROM mtl_parameters
661 WHERE organization_id = p_organization_id;
662
663 RETURN l_master_org_id;
664 END IF;
665
666 EXCEPTION
667 WHEN OTHERS THEN
668 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
669 'EAM_ActivityUtilities_PVT.Get_Master_Org_Id: unexpected error.'||
670 'p_organization_id=' || p_organization_id);
671 end if;
672
673 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
674 END;
675
676 -- ----------------------------------------------------------------------
677 PROCEDURE Validate_Asset_Number(
678 p_instance_number IN VARCHAR2,
679 p_organization_id IN NUMBER,
680 p_inventory_item_id IN NUMBER,
681 p_serial_number IN VARCHAR2,
682
683 x_return_status OUT NOCOPY VARCHAR2,
684 x_error_mesg OUT NOCOPY VARCHAR2,
685
686 x_maintenance_object_id OUT NOCOPY NUMBER,
687 x_maintenance_object_type OUT NOCOPY NUMBER
688 )
689 IS
690 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Asset_Number';
691 l_module varchar2(200);
692 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
693 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
694 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
695 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
696
697 l_x_org_return_status VARCHAR2(1);
698 l_x_org_id NUMBER;
699 l_x_org_code VARCHAR2(3);
700
701 l_x_error_mesg VARCHAR2(20000);
702
703 BEGIN
704
705 if(l_ulog) then
706 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
707 end if;
708 -- Instance number takes precedence
709 IF p_instance_number IS NOT NULL THEN
710 BEGIN
711 SELECT instance_id, 3
712 INTO x_maintenance_object_id, x_maintenance_object_type
713 FROM csi_item_instances
714 WHERE instance_number = p_instance_number;
715 EXCEPTION
716 WHEN OTHERS THEN
717 x_return_status := FND_API.G_RET_STS_ERROR;
718 FND_MESSAGE.SET_NAME('EAM', 'EAM_INVALID_INSTANCE_NUMBER');
719 -- FND_MESSAGE.SET_ENCODED('Instance number is invalid.');
720 l_x_error_mesg := FND_MESSAGE.GET;
721
722 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
723 l_x_error_mesg ||
724 'p_instance_number=' || p_instance_number);
725 end if;
726
727 x_error_mesg := l_x_error_mesg;
728 RETURN;
729 END;
730 ELSE
731 -- 1) Validate Organization Id
732 Validate_Organization(
733 p_organization_id,
734 NULL,
735 l_x_org_return_status,
736 l_x_org_id,
737 l_x_org_code);
738 IF l_x_org_return_status <> FND_API.G_RET_STS_SUCCESS THEN
739 x_return_status := FND_API.G_RET_STS_ERROR;
740 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_INVALID_ORG_ID');
741 l_x_error_mesg := FND_MESSAGE.GET;
742
743 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
744 l_x_error_mesg ||
745 'p_instance_number=' || p_instance_number ||
746 'p_organization_id=' || p_organization_id ||
747 '; p_inventory_item_id=' || p_inventory_item_id ||
748 '; p_serial_number=' || p_serial_number);
749
750 end if;
751
752 x_error_mesg := l_x_error_mesg;
753 RETURN;
754 END IF;
755
756 if p_serial_number is not null then
757
758 -- 2) Validate Asset Group and Serial Number
759 BEGIN
760 SELECT instance_id, 3
761 INTO x_maintenance_object_id, x_maintenance_object_type
762 FROM csi_item_instances
763 WHERE inventory_item_id = p_inventory_item_id
764 AND serial_number = p_serial_number;
765 EXCEPTION
766 WHEN OTHERS THEN
767 x_return_status := FND_API.G_RET_STS_ERROR;
768 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_INVALID_ASSET_GRP_NUM');
769 -- FND_MESSAGE.SET_ENCODED('Asset Group, Number invalid.');
770 l_x_error_mesg := FND_MESSAGE.GET;
771
772 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
773 l_x_error_mesg ||
774 'p_instance_number=' || p_instance_number ||
775 'p_organization_id=' || p_organization_id ||
776 '; p_inventory_item_id=' || p_inventory_item_id ||
777 '; p_serial_number=' || p_serial_number);
778 end if;
779
780 x_error_mesg := l_x_error_mesg;
781 RETURN;
782 END;
783
784 else
785 -- Serial Number NULL, could be non-serialized rebuild or template
786 -- only validate inventory_item_id
787 declare
788 l_count number;
789 begin
790
791 select count(*) into l_count
792 from mtl_system_items
793 where inventory_item_id = p_inventory_item_id;
794
795 if l_count >= 1 then
796 x_maintenance_object_id := p_inventory_item_id;
797 x_maintenance_object_type := 2;
798 ELSE
799 x_return_status := FND_API.G_RET_STS_ERROR;
800 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_INVALID_INV_ITEM_ID');
801 -- FND_MESSAGE.SET_ENCODED('Invalid Inventory Item Id.');
802 l_x_error_mesg := FND_MESSAGE.GET;
803
804 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
805 l_x_error_mesg ||
806 'p_instance_number=' || p_instance_number ||
807 'p_organization_id=' || p_organization_id ||
808 '; p_inventory_item_id=' || p_inventory_item_id ||
809 '; p_serial_number=' || p_serial_number);
810 end if;
811
812 x_error_mesg := l_x_error_mesg;
813 RETURN;
814 end if;
815 end;
816 end if;
817 END IF;
818
819
820 -- If reach here, means data valid
821 x_return_status := FND_API.G_RET_STS_SUCCESS;
822 END;
823
824 -- ----------------------------------------------------------------------
825 FUNCTION Get_Cost_Activity(
826 p_activity_id IN NUMBER
827 )
828 RETURN VARCHAR2
829 IS
830 l_api_name CONSTANT VARCHAR2(30) := 'Get_Cost_Activity';
831 l_module varchar2(200);
832 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
833 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
834 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
835 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
836
837 l_activity cst_activities.activity%TYPE;
838 BEGIN
839 if(l_ulog) then
840 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
841 end if;
842 IF p_activity_id IS NULL THEN
843 RETURN NULL;
844 ELSE
845 SELECT activity INTO l_activity
846 FROM cst_activities
847 WHERE activity_id = p_activity_id;
848
849 RETURN l_activity;
850 END IF;
851 EXCEPTION
852 WHEN OTHERS THEN
853 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
854 'EAM_ActivityUtilities_PVT.Get_Cost_Activity: unexpected error.' ||
855 'p_activity_id=' || p_activity_id);
856 end if;
857
858 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
859 END;
860
861 -- ----------------------------------------------------------------------
862 FUNCTION Get_Locator(
863 p_organization_id IN NUMBER,
864 p_subinventory_code IN VARCHAR2,
865 p_locator_id IN NUMBER
866 )
867 RETURN VARCHAR2
868 IS
869 l_api_name CONSTANT VARCHAR2(30) := 'Get_Locator';
870 l_module varchar2(200);
871 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
872 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
873 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
874 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
875
876 l_locator mtl_item_locations_kfv.concatenated_segments%TYPE;
877 BEGIN
878 if(l_ulog) then
879 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
880 end if;
881 IF p_organization_id IS NULL OR p_subinventory_code IS NULL OR p_locator_id IS NULL THEN
882 RETURN NULL;
883 ELSE
884 SELECT concatenated_segments INTO l_locator
885 FROM mtl_item_locations_kfv
886 WHERE organization_id = p_organization_id
887 AND subinventory_code = p_subinventory_code
888 AND (disable_date > sysdate or disable_date is null)
889 AND inventory_location_id = p_locator_id;
890
891 RETURN l_locator;
892 END IF;
893 EXCEPTION
894 WHEN OTHERS THEN
895 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
896 'EAM_ActivityUtilities_PVT.Get_Locator: unexpected error.' ||
897 'p_organization_id=' || p_organization_id ||
898 '; p_subinventory_code=' || p_subinventory_code ||
899 '; p_locator_id=' || p_locator_id);
900
901 end if;
902
903 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
904 END;
905
906 -- ----------------------------------------------------------------------
907 PROCEDURE Get_Op_Coordinates(
908 p_organization_id IN NUMBER,
909 p_wip_entity_id IN NUMBER,
910 p_operation_seq_num IN NUMBER,
911 x_x_pos OUT NOCOPY NUMBER,
912 x_y_pos OUT NOCOPY NUMBER
913 )
914 IS
915 l_api_name CONSTANT VARCHAR2(30) := 'Get_Op_Coordinates';
916 l_module varchar2(200);
917 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
918 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level ;
919 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
920 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
921
922 BEGIN
923 if(l_ulog) then
924 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
925 end if;
926 IF p_organization_id IS NULL OR p_wip_entity_id IS NULL OR
927 p_operation_seq_num IS NULL
928 THEN
929 RETURN;
930 ELSE
931 SELECT x_pos, y_pos INTO x_x_pos, x_y_pos
932 FROM wip_operations
933 WHERE organization_id = p_organization_id
934 AND wip_entity_id = p_wip_entity_id
935 AND operation_seq_num = p_operation_seq_num;
936 END IF;
937
938 EXCEPTION
939 WHEN OTHERS THEN
940 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
941 'EAM_ActivityUtilities_PVT.Get_Op_Coordinates: unexpected error.' ||
942 'p_organization_id=' || p_organization_id ||
943 '; p_wip_entity_id=' || p_wip_entity_id ||
944 '; p_operation_seq_num=' || p_operation_seq_num);
945 end if;
946
947 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
948 END;
949
950 -- ----------------------------------------------------------------------
951 FUNCTION Get_Bom_Sequence_Id(
952 p_organization_id IN NUMBER,
953 p_assembly_item_id IN NUMBER,
954 p_alternate_bom_designator IN VARCHAR2
955 )
956 RETURN NUMBER
957 IS
958 l_api_name CONSTANT VARCHAR2(30) := 'Get_Bom_Sequence_Id';
959 l_module varchar2(200);
960 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
961 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level ;
962 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
963 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
964
965 l_bom_sequence_id NUMBER;
966 BEGIN
967 if(l_ulog) then
968 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
969 end if;
970 IF p_organization_id IS NULL OR p_assembly_item_id IS NULL
971 THEN
972 RETURN NULL;
973 ELSE
974 SELECT bill_sequence_id INTO l_bom_sequence_id
975 FROM bom_bill_of_materials
976 WHERE organization_id = p_organization_id
977 AND assembly_item_id = p_assembly_item_id
978 AND ( (alternate_bom_designator IS NULL AND p_alternate_bom_designator IS NULL)
979 OR alternate_bom_designator = p_alternate_bom_designator);
980 RETURN l_bom_sequence_id;
981 END IF;
982 EXCEPTION
983 WHEN NO_DATA_FOUND THEN
984 RETURN NULL;
985
986 WHEN OTHERS THEN
987 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
988 'EAM_ActivityUtilities_PVT.Get_Bom_Sequence_Id: unexpected error.' ||
989 'p_organization_id=' || p_organization_id ||
990 '; p_assembly_item_id=' || p_assembly_item_id ||
991 '; p_alternate_bom_designator=' || p_alternate_bom_designator);
992 end if;
993
994 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
995 END;
996
997 -- ----------------------------------------------------------------------
998 FUNCTION Get_Rtg_Sequence_Id(
999 p_organization_id IN NUMBER,
1000 p_assembly_item_id IN NUMBER,
1001 p_alternate_rtg_designator IN VARCHAR2
1002 )
1003 RETURN NUMBER
1004 IS
1005 l_api_name CONSTANT VARCHAR2(30) := 'Get_Rtg_Sequence_Id';
1006 l_module varchar2(200);
1007 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
1008 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
1009 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
1010 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
1011
1012 l_rtg_sequence_id NUMBER;
1013 BEGIN
1014 if(l_ulog) then
1015 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
1016 end if;
1017 IF p_organization_id IS NULL OR p_assembly_item_id IS NULL
1018 THEN
1019 RETURN NULL;
1020 ELSE
1021 SELECT routing_sequence_id INTO l_rtg_sequence_id
1022 FROM bom_operational_routings
1023 WHERE organization_id = p_organization_id
1024 AND assembly_item_id = p_assembly_item_id
1025 AND ( (alternate_routing_designator IS NULL AND p_alternate_rtg_designator IS NULL)
1026 OR alternate_routing_designator = p_alternate_rtg_designator);
1027 RETURN l_rtg_sequence_id;
1028 END IF;
1029 EXCEPTION
1030 WHEN NO_DATA_FOUND THEN
1031 RETURN NULL;
1032
1033 WHEN OTHERS THEN
1034 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1035 'EAM_ActivityUtilities_PVT.Get_Bom_Sequence_Id: unexpected error.' ||
1036 'p_organization_id=' || p_organization_id ||
1037 '; p_assembly_item_id=' || p_assembly_item_id ||
1038 '; p_alternate_rtg_designator=' || p_alternate_rtg_designator);
1039 end if;
1040
1041 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1042 END;
1043
1044 -- ----------------------------------------------------------------------
1045 FUNCTION Get_Gen_Object_Id(
1046 p_organization_id IN NUMBER,
1047 p_inventory_item_id IN NUMBER,
1048 p_serial_number IN VARCHAR2
1049 )
1050 RETURN NUMBER
1051 IS
1052 l_api_name CONSTANT VARCHAR2(30) := 'Get_Gen_Object_Id';
1053 l_module varchar2(200);
1054 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
1055 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
1056 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
1057 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
1058
1059 l_gen_object_id NUMBER;
1060 BEGIN
1061 if(l_ulog) then
1062 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
1063 end if;
1064 IF p_organization_id IS NULL OR p_inventory_item_id IS NULL OR p_serial_number IS NULL
1065 THEN
1066 RETURN NULL;
1067 ELSE
1068 SELECT gen_object_id INTO l_gen_object_id
1069 FROM mtl_serial_numbers
1070 WHERE current_organization_id = p_organization_id
1071 AND inventory_item_id = p_inventory_item_id
1072 AND serial_number = p_serial_number;
1073
1074 RETURN l_gen_object_id;
1075 END IF;
1076 EXCEPTION
1077 WHEN OTHERS THEN
1078 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1079 'EAM_ActivityUtilities_PVT.Get_Gen_Object_Id: unexpected error.' ||
1080 'p_organization_id=' || p_organization_id ||
1081 '; p_inventory_item_id=' || p_inventory_item_id ||
1082 '; p_serial_number=' || p_serial_number);
1083 end if;
1084
1085 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1086 END;
1087
1088 -- ----------------------------------------------------------------------
1089 PROCEDURE Get_Item_Info_From_WO(
1090 p_wip_entity_id IN NUMBER,
1091 x_source_org_id OUT NOCOPY NUMBER,
1092 x_source_activity_id OUT NOCOPY NUMBER,
1093 x_wo_maint_id OUT NOCOPY NUMBER,
1094 x_wo_maint_type OUT NOCOPY NUMBER
1095 )
1096 IS
1097 l_api_name CONSTANT VARCHAR2(30) := 'Get_Item_Info_From_WO';
1098 l_module varchar2(200);
1099 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
1100 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
1101 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
1102 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
1103
1104 l_organization_id NUMBER;
1105 l_primary_item_id NUMBER;
1106 l_wo_maint_id NUMBER;
1107 l_wo_maint_type NUMBER;
1108 BEGIN
1109 if(l_ulog) then
1110 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
1111 end if;
1112 IF p_wip_entity_id IS NULL THEN
1113 x_source_org_id := NULL;
1114 x_source_activity_id := NULL;
1115 x_wo_maint_id := NULL;
1116 x_wo_maint_type := NULL;
1117 ELSE
1118 SELECT organization_id, primary_item_id, maintenance_object_id, maintenance_object_type
1119 INTO x_source_org_id, x_source_activity_id, x_wo_maint_id, x_wo_maint_type
1120 FROM wip_discrete_jobs
1121 WHERE wip_entity_id = p_wip_entity_id;
1122
1123 END IF;
1124
1125 EXCEPTION
1126 WHEN OTHERS THEN
1127 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1128 'EAM_ActivityUtilities_PVT.Get_Item_Info_From_WO: unexpected error.'||
1129 'p_wip_entity_id=' || p_wip_entity_id);
1130 end if;
1131
1132 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1133 END;
1134 -- ----------------------------------------------------------------------
1135 FUNCTION Default_Owning_Department_Id(
1136 p_activity_association_id IN NUMBER,
1137 p_instance_id IN NUMBER,
1138 p_organization_id IN NUMBER
1139 )
1140 RETURN NUMBER
1141 IS
1142 l_api_name CONSTANT VARCHAR2(30) := 'Default_Owning_Department_Id';
1143 l_module varchar2(200) ;
1144 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
1145 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
1146 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
1147 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
1148
1149 l_owning_department_id NUMBER;
1150 BEGIN
1151 if(l_ulog) then
1152 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
1153 end if;
1154 -- Defaulting logic: first activity association, then from serial number, finally EAM org parameters.
1155 IF p_activity_association_id IS NOT NULL THEN
1156 BEGIN
1157 SELECT owning_department_id
1158 INTO l_owning_department_id
1159 FROM eam_org_maint_defaults
1160 WHERE object_id = p_activity_association_id AND object_type in (40, 60)
1161 AND organization_id = p_organization_id;
1162 EXCEPTION
1163 WHEN NO_DATA_FOUND THEN
1164 l_owning_department_id := NULL;
1165 END;
1166
1167 IF l_owning_department_id IS NOT NULL THEN
1168 RETURN l_owning_department_id;
1169 END IF;
1170 END IF;
1171
1172 IF p_instance_id IS NOT NULL THEN
1173 BEGIN
1174 SELECT owning_department_id
1175 INTO l_owning_department_id
1176 FROM eam_org_maint_defaults
1177 WHERE object_id = p_instance_id AND object_type = 50
1178 AND organization_id = p_organization_id;
1179 EXCEPTION
1180 WHEN NO_DATA_FOUND THEN
1181 l_owning_department_id := NULL;
1182 END;
1183
1184 IF l_owning_department_id IS NOT NULL THEN
1185 RETURN l_owning_department_id;
1186 END IF;
1187 END IF;
1188
1189 IF p_organization_id IS NOT NULL THEN
1190 SELECT default_department_id
1191 INTO l_owning_department_id
1192 FROM wip_eam_parameters
1193 WHERE organization_id = p_organization_id;
1194
1195 RETURN l_owning_department_id;
1196 END IF;
1197
1198 RETURN NULL;
1199
1200 EXCEPTION
1201 WHEN OTHERS THEN
1202 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1203 'EAM_ActivityUtilities_PVT.Default_Owning_Department_Id: unexpected error.'||
1204 'p_activity_association_id=' || p_activity_association_id ||
1205 'p_instance_id=' || p_instance_id ||
1206 'p_organization_id=' || p_organization_id);
1207 end if;
1208
1209 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1210 END;
1211
1212 -- ----------------------------------------------------------------------
1213 FUNCTION Is_Item_Serialized(
1214 p_organization_id IN NUMBER,
1215 p_maint_id IN NUMBER,
1216 p_maint_type IN NUMBER
1217 )
1218 RETURN BOOLEAN
1219 IS
1220 l_api_name CONSTANT VARCHAR2(30) := 'Is_Item_Serialized';
1221 l_module varchar2(200);
1222 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
1223 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
1224 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
1225 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
1226
1227 l_serial_number_control_code NUMBER;
1228 BEGIN
1229 if(l_ulog) then
1230 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
1231 end if;
1232 IF p_maint_type = 3 THEN
1233 RETURN TRUE;
1234 END IF;
1235 IF p_organization_id IS NULL OR p_maint_id IS NULL OR
1236 p_maint_type IS NULL OR p_maint_type <> 2 THEN
1237 RETURN NULL;
1238 ELSE
1239 SELECT msi.serial_number_control_code
1240 INTO l_serial_number_control_code
1241 FROM mtl_system_items msi, mtl_parameters mp
1242 WHERE mp.maint_organization_id = p_organization_id
1243 AND mp.organization_id = msi.organization_id
1244 AND msi.inventory_item_id = p_maint_id
1245 AND ROWNUM = 1;
1246
1247 IF l_serial_number_control_code = 1 THEN
1248 RETURN FALSE;
1249 ELSE
1250 RETURN TRUE;
1251 END IF;
1252 END IF;
1253 EXCEPTION
1254 WHEN OTHERS THEN
1255 if (l_plog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
1256 'EAM_ActivityUtilities_PVT.Is_Item_Serialized: unexpected error.' ||
1257 'p_organization_id=' || p_organization_id ||
1258 'p_maint_id=' || p_maint_id);
1259 end if;
1260
1261 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1262 END;
1263
1264 -- ----------------------------------------------------------------------
1265 -- For logging
1266 PROCEDURE Open_Debug_Session
1267 IS
1268 BEGIN
1269 IF Is_Debug = g_YES
1270 THEN
1271 Debug_File := utl_file.fopen( Debug_File_Dir,
1272 Debug_File_Name,
1273 'w');
1274 Log_Index := 1;
1275 utl_file.put_line(Debug_File, 'Created ' || TO_CHAR(sysdate, 'DD MON YYYY HH12:MI:SS AM') ||
1276 '; Debug_File_Dir=' || Debug_File_Dir ||
1277 '; Debug_File_Name=' || Debug_File_Name
1278 );
1279 utl_file.fflush(Debug_File);
1280 END IF;
1281
1282 EXCEPTION
1283 WHEN OTHERS THEN
1284
1285 Is_Debug := g_NO;
1286
1287 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1288 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_CANNOT_CREATE_LOG_FILE');
1289 -- FND_MESSAGE.SET_ENCODED('Cannot create log file.');
1290 FND_MSG_PUB.ADD;
1291 END IF;
1292 END;
1293
1294 -- ----------------------------------------------------------------------
1295 PROCEDURE Write_Debug(
1296 p_debug_message IN VARCHAR2
1297 )
1298 IS
1299 BEGIN
1300 IF Is_Debug = g_YES
1301 THEN
1302 IF utl_file.is_open(Debug_File)
1303 THEN
1304 utl_file.put_line(Debug_File, '[' || Log_Index || '] ' || p_debug_message);
1305 utl_file.fflush(Debug_File);
1306 Log_Index := Log_Index + 1;
1307 END IF;
1308 END IF;
1309 EXCEPTION
1310 WHEN OTHERS THEN
1311 Is_Debug := g_NO;
1312
1313 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1314 FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_ERROR_WRITING_TO_LOG');
1315 -- FND_MESSAGE.SET_ENCODED('Error writing to log file.');
1316 FND_MSG_PUB.ADD;
1317 END IF;
1318 END Write_Debug;
1319
1320 -- ----------------------------------------------------------------------
1321 PROCEDURE Close_Debug_Session
1322 IS
1323 BEGIN
1324 IF Is_Debug = g_YES
1325 THEN
1326 IF utl_file.is_open(Debug_File)
1327 THEN
1328 utl_file.fclose(Debug_File);
1329 END IF;
1330 END IF;
1331 END Close_Debug_Session;
1332
1333 -- ----------------------------------------------------------------------
1334 PROCEDURE Add_Message(
1335 p_message_level IN NUMBER
1336 )
1337 IS
1338 l_api_name CONSTANT VARCHAR2(30) := 'Add_Message';
1339 l_module varchar2(200) ;
1340 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
1341 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
1342 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
1343 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
1344
1345 l_message_text VARCHAR2(20000);
1346
1347 BEGIN
1348 if(l_ulog) then
1349 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
1350 end if;
1351 -- add message to fnd message stack
1352 IF FND_MSG_PUB.Check_Msg_Level(p_message_level)
1353 THEN
1354 -- Push message onto fnd message stack
1355 FND_MSG_PUB.ADD;
1356 -- Get translated message
1357 l_message_text := FND_MSG_PUB.Get(FND_MSG_PUB.G_LAST, FND_API.G_FALSE);
1358 ELSE
1359 -- Message not pushed onto fnd message stack
1360 l_message_text := FND_MESSAGE.GET;
1361 END IF;
1362
1363 -- Also output message to log file
1364 if (l_plog) then
1365 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, l_message_text);
1366 end if;
1367
1368 END;
1369
1370 -- ----------------------------------------------------------------------
1371 PROCEDURE Log_Item_Error_Tbl(
1372 p_item_error_tbl IN INV_Item_GRP.Error_Tbl_Type
1373 )
1374 IS
1375 l_index BINARY_INTEGER;
1376 BEGIN
1377 -- traverse whole table, add error messages to log file
1378 l_index := p_item_error_tbl.FIRST;
1379 WHILE l_index IS NOT NULL
1380 LOOP
1381 Write_Debug('Transaction_Id=' || p_item_error_tbl(l_index).transaction_id ||
1382 '; Unique_id=' || p_item_error_tbl(l_index).unique_id ||
1383 '; Organization_Id=' || p_item_error_tbl(l_index).organization_id);
1384 Write_Debug('Table_Name=' || p_item_error_tbl(l_index).table_name ||
1385 '; Column_Name=' || p_item_error_tbl(l_index).column_name ||
1386 '; Message_Name=' || p_item_error_tbl(l_index).message_name);
1387 Write_Debug('Message_Text=' || p_item_error_tbl(l_index).message_text);
1388
1389 l_index := p_item_error_tbl.NEXT(l_index);
1390 END LOOP; -- WHILE l_next_index IS NOT NULL
1391 END;
1392
1393 -- ----------------------------------------------------------------------
1394 PROCEDURE Log_Bom_Error_Tbl(
1395 p_bom_error_tbl IN Error_Handler.Error_Tbl_Type
1396 )
1397 IS
1398 l_index BINARY_INTEGER;
1399 BEGIN
1400
1401 Write_Debug('p_bom_error_tbl.COUNT=' || p_bom_error_tbl.COUNT);
1402 l_index := p_bom_error_tbl.FIRST;
1403 WHILE l_index IS NOT NULL
1404 LOOP
1405 Write_Debug('organization_id=' || p_bom_error_tbl(l_index).organization_id ||
1406 '; entity_id=' || p_bom_error_tbl(l_index).entity_id ||
1407 '; entity_index=' || p_bom_error_tbl(l_index).entity_index ||
1408 '; message_type=' || p_bom_error_tbl(l_index).message_type ||
1409 '; bo_identifier=' || p_bom_error_tbl(l_index).bo_identifier);
1410 Write_Debug('mesg_text=' || p_bom_error_tbl(l_index).message_text);
1411 l_index := p_bom_error_tbl.NEXT(l_index);
1412 END LOOP;
1413
1414 END;
1415
1416 -- ----------------------------------------------------------------------
1417 PROCEDURE Log_Process_Rtg_Parameters(
1418 p_rtg_header_rec IN BOM_RTG_PUB.Rtg_Header_Rec_Type,
1419 p_operation_tbl IN BOM_RTG_PUB.Operation_Tbl_Type,
1420 p_op_resource_tbl IN BOM_RTG_PUB.Op_Resource_Tbl_Type,
1421 p_op_network_tbl IN BOM_RTG_PUB.Op_Network_Tbl_Type
1422 )
1423 IS
1424 BEGIN
1425 Write_Debug('%%%%%%%%%% BOM_RTG_PUB.Process_Rtg Parameters %%%%%%%%%%');
1426 Log_Rtg_Header_Rec(p_rtg_header_rec);
1427 Log_Rtg_Operation_Tbl(p_operation_tbl);
1428 Log_Rtg_Op_Resource_Tbl(p_op_resource_tbl);
1429 Log_Rtg_Op_Network_Tbl(p_op_network_tbl);
1430 Write_Debug('%%%%%%%%%% End of BOM_RTG_PUB.Process_Rtg Parameters %%%%%%%%%%');
1431 END;
1432
1433 PROCEDURE Log_Rtg_Header_Rec(
1434 rtg_header_rec IN BOM_RTG_PUB.Rtg_Header_Rec_Type
1435 )
1436 IS
1437 BEGIN
1438 Write_Debug('rtg_header_rec.Assembly_Item_Name=' || rtg_header_rec.Assembly_Item_Name);
1439 Write_Debug('rtg_header_rec.Organization_Code=' || rtg_header_rec.Organization_Code);
1440 Write_Debug('rtg_header_rec.Alternate_Routing_Code=' || rtg_header_rec.Alternate_Routing_Code);
1441 Write_Debug('rtg_header_rec.Transaction_Type=' || rtg_header_rec.Transaction_Type);
1442 Write_Debug('rtg_header_rec.Return_Status=' || rtg_header_rec.Return_Status);
1443 END;
1444
1445 PROCEDURE Log_Rtg_Operation_Tbl(
1446 operation_tbl IN BOM_RTG_PUB.Operation_Tbl_Type
1447 )
1448 IS
1449 l_index BINARY_INTEGER;
1450 BEGIN
1451 Write_Debug('operation_tbl.COUNT=' || operation_tbl.COUNT);
1452 l_index := operation_tbl.FIRST;
1453 WHILE l_index IS NOT NULL
1454 LOOP
1455 Write_Debug('l_index=' || l_index);
1456 Write_Debug('operation_tbl(l_index).Assembly_Item_Name=' || operation_tbl(l_index).Assembly_Item_Name);
1457 Write_Debug('operation_tbl(l_index).Organization_Code=' || operation_tbl(l_index).Organization_Code);
1458 Write_Debug('operation_tbl(l_index).Alternate_Routing_Code=' || operation_tbl(l_index).Alternate_Routing_Code);
1459 Write_Debug('operation_tbl(l_index).Operation_Sequence_Number=' || operation_tbl(l_index).Operation_Sequence_Number);
1460 Write_Debug('operation_tbl(l_index).Start_Effective_Date=' || operation_tbl(l_index).Start_Effective_Date);
1461 Write_Debug('operation_tbl(l_index).Department_Code=' || operation_tbl(l_index).Department_Code);
1462 Write_Debug('operation_tbl(l_index).Operation_Description=' || operation_tbl(l_index).Operation_Description);
1463 Write_Debug('operation_tbl(l_index).Transaction_Type=' || operation_tbl(l_index).Transaction_Type);
1464 Write_Debug('operation_tbl(l_index).Return_Status=' || operation_tbl(l_index).Return_Status);
1465
1466 l_index := operation_tbl.NEXT(l_index);
1467 END LOOP;
1468 END;
1469
1470 PROCEDURE Log_Rtg_Op_Resource_Tbl(
1471 op_resource_tbl IN BOM_RTG_PUB.Op_Resource_Tbl_Type
1472 )
1473 IS
1474 l_index BINARY_INTEGER;
1475 BEGIN
1476 Write_Debug('op_resource_tbl.COUNT=' || op_resource_tbl.COUNT);
1477 l_index := op_resource_tbl.FIRST;
1478 WHILE l_index IS NOT NULL
1479 LOOP
1480 Write_Debug('l_index=' || l_index);
1481 Write_Debug('op_resource_tbl(l_index).Assembly_Item_Name=' || op_resource_tbl(l_index).Assembly_Item_Name);
1482 Write_Debug('op_resource_tbl(l_index).Organization_Code=' || op_resource_tbl(l_index).Organization_Code);
1483 Write_Debug('op_resource_tbl(l_index).Alternate_Routing_Code=' || op_resource_tbl(l_index).Alternate_Routing_Code);
1484 Write_Debug('op_resource_tbl(l_index).Operation_Sequence_Number='||op_resource_tbl(l_index).Operation_Sequence_Number);
1485 Write_Debug('op_resource_tbl(l_index).Op_Start_Effective_Date=' || op_resource_tbl(l_index).Op_Start_Effective_Date);
1486 Write_Debug('op_resource_tbl(l_index).Resource_Sequence_Number=' || op_resource_tbl(l_index).Resource_Sequence_Number);
1487 Write_Debug('op_resource_tbl(l_index).Resource_Code=' || op_resource_tbl(l_index).Resource_Code);
1488 Write_Debug('op_resource_tbl(l_index).Transaction_Type=' || op_resource_tbl(l_index).Transaction_Type);
1489 Write_Debug('op_resource_tbl(l_index).Return_Status=' || op_resource_tbl(l_index).Return_Status);
1490
1491 l_index := op_resource_tbl.NEXT(l_index);
1492 END LOOP;
1493 END;
1494
1495 PROCEDURE Log_Rtg_Op_Network_Tbl(
1496 op_network_tbl IN BOM_RTG_PUB.Op_Network_Tbl_Type
1497 )
1498 IS
1499 l_index BINARY_INTEGER;
1500 BEGIN
1501 Write_Debug('op_network_tbl.COUNT=' || op_network_tbl.COUNT);
1502 l_index := op_network_tbl.FIRST;
1503 WHILE l_index IS NOT NULL
1504 LOOP
1505 Write_Debug('l_index=' || l_index);
1506 Write_Debug('op_network_tbl(l_index).Assembly_Item_Name=' || op_network_tbl(l_index).Assembly_Item_Name);
1507 Write_Debug('op_network_tbl(l_index).Organization_Code=' || op_network_tbl(l_index).Organization_Code);
1508 Write_Debug('op_network_tbl(l_index).Alternate_Routing_Code=' || op_network_tbl(l_index).Alternate_Routing_Code);
1509 Write_Debug('op_network_tbl(l_index).From_Op_Seq_Number='||op_network_tbl(l_index).From_Op_Seq_Number);
1510 Write_Debug('op_network_tbl(l_index).From_Start_Effective_Date='||op_network_tbl(l_index).From_Start_Effective_Date);
1511 Write_Debug('op_network_tbl(l_index).To_Op_Seq_Number='||op_network_tbl(l_index).To_Op_Seq_Number);
1512 Write_Debug('op_network_tbl(l_index).To_Start_Effective_Date='||op_network_tbl(l_index).To_Start_Effective_Date);
1513 Write_Debug('op_network_tbl(l_index).Transaction_Type=' || op_network_tbl(l_index).Transaction_Type);
1514 Write_Debug('op_network_tbl(l_index).Return_Status=' || op_network_tbl(l_index).Return_Status);
1515
1516 l_index := op_network_tbl.NEXT(l_index);
1517 END LOOP;
1518 END;
1519
1520
1521 PROCEDURE Log_Process_BOM_Parameters(
1522 p_bom_header_rec IN BOM_BO_PUB.Bom_Head_Rec_Type,
1523 p_bom_component_tbl IN BOM_BO_PUB.Bom_Comps_Tbl_Type
1524 )
1525 IS
1526 BEGIN
1527 Write_Debug('%%%%%%%%%% BOM_BO_PUB.Process_BOM Parameters %%%%%%%%%%');
1528 Log_Bom_Header_Rec(p_bom_header_rec);
1529 Log_Bom_Component_Tbl(p_bom_component_tbl);
1530 Write_Debug('%%%%%%%%%% End of BOM_RTG_PUB.Process_Rtg Parameters %%%%%%%%%%');
1531 END;
1532
1533 PROCEDURE Log_Bom_Header_Rec(
1534 bom_header_rec IN BOM_BO_PUB.Bom_Head_Rec_Type
1535 )
1536 IS
1537 BEGIN
1538 Write_Debug('bom_header_rec.Assembly_Item_Name=' || bom_header_rec.Assembly_Item_Name);
1539 Write_Debug('bom_header_rec.Organization_Code=' || bom_header_rec.Organization_Code);
1540 Write_Debug('bom_header_rec.Alternate_Bom_Code=' || bom_header_rec.Alternate_Bom_Code);
1541 Write_Debug('bom_header_rec.Assembly_Type=' || bom_header_rec.Assembly_Type);
1542 Write_Debug('bom_header_rec.Transaction_Type=' || bom_header_rec.Transaction_Type);
1543 Write_Debug('bom_header_rec.Return_Status=' || bom_header_rec.Return_Status);
1544 END;
1545
1546 PROCEDURE Log_Bom_Component_Tbl(
1547 bom_component_tbl IN BOM_BO_PUB.Bom_Comps_Tbl_Type
1548 )
1549 IS
1550 l_index BINARY_INTEGER;
1551 BEGIN
1552 Write_Debug('bom_component_tbl.COUNT=' || bom_component_tbl.COUNT);
1553 l_index := bom_component_tbl.FIRST;
1554 WHILE l_index IS NOT NULL
1555 LOOP
1556 Write_Debug('l_index=' || l_index);
1557 Write_Debug('bom_component_tbl(l_index).Organization_Code=' || bom_component_tbl(l_index).Organization_Code);
1558 Write_Debug('bom_component_tbl(l_index).Assembly_Item_Name=' || bom_component_tbl(l_index).Assembly_Item_Name);
1559 Write_Debug('bom_component_tbl(l_index).Start_Effective_Date=' || bom_component_tbl(l_index).Start_Effective_Date);
1560 Write_Debug('bom_component_tbl(l_index).Operation_Sequence_Number='||bom_component_tbl(l_index).Operation_Sequence_Number);
1561 Write_Debug('bom_component_tbl(l_index).Component_Item_Name=' || bom_component_tbl(l_index).Component_Item_Name);
1562 Write_Debug('bom_component_tbl(l_index).Alternate_BOM_Code=' || bom_component_tbl(l_index).Alternate_BOM_Code);
1563 Write_Debug('bom_component_tbl(l_index).Item_Sequence_Number=' || bom_component_tbl(l_index).Item_Sequence_Number);
1564 Write_Debug('bom_component_tbl(l_index).Transaction_Type=' || bom_component_tbl(l_index).Transaction_Type);
1565 Write_Debug('bom_component_tbl(l_index).Return_Status=' || bom_component_tbl(l_index).Return_Status);
1566
1567 l_index := bom_component_tbl.NEXT(l_index);
1568 END LOOP;
1569 END;
1570
1571 PROCEDURE Log_Inv_Item_Rec(
1572 item_rec IN INV_Item_GRP.Item_rec_type
1573 )
1574 IS
1575
1576 BEGIN
1577 Write_Debug('item_rec.ORGANIZATION_ID=' || item_rec.ORGANIZATION_ID);
1578 Write_Debug('item_rec.ORGANIZATION_CODE=' || item_rec.ORGANIZATION_CODE);
1579 Write_Debug('item_rec.INVENTORY_ITEM_ID=' || item_rec.INVENTORY_ITEM_ID);
1580 Write_Debug('item_rec.ITEM_NUMBER=' || item_rec.ITEM_NUMBER);
1581 Write_Debug('item_rec.DESCRIPTION=' || item_rec.DESCRIPTION);
1582 Write_Debug('item_rec.EAM_ITEM_TYPE=' || item_rec.EAM_ITEM_TYPE);
1583
1584 Write_Debug('item_rec.EAM_ACTIVITY_TYPE_CODE=' || item_rec.EAM_ACTIVITY_TYPE_CODE);
1585 Write_Debug('item_rec.EAM_ACTIVITY_CAUSE_CODE=' || item_rec.EAM_ACTIVITY_CAUSE_CODE);
1586 Write_Debug('item_rec.EAM_ACT_NOTIFICATION_FLAG=' || item_rec.EAM_ACT_NOTIFICATION_FLAG);
1587 Write_Debug('item_rec.EAM_ACT_SHUTDOWN_STATUS=' || item_rec.EAM_ACT_SHUTDOWN_STATUS);
1588 Write_Debug('item_rec.EAM_ACTIVITY_SOURCE_CODE=' || item_rec.EAM_ACTIVITY_SOURCE_CODE);
1589
1590 Write_Debug('item_rec.INVENTORY_ITEM_FLAG=' || item_rec.INVENTORY_ITEM_FLAG);
1591 Write_Debug('item_rec.MTL_TRANSACTIONS_ENABLED_FLAG=' || item_rec.MTL_TRANSACTIONS_ENABLED_FLAG);
1592 Write_Debug('item_rec.BOM_ENABLED_FLAG=' || item_rec.BOM_ENABLED_FLAG);
1593 Write_Debug('item_rec.EXPENSE_ACCOUNT=' || item_rec.EXPENSE_ACCOUNT);
1594
1595 END;
1596
1597 -- ----------------------------------------------------------------------
1598 FUNCTION Get_First_N_Messages(
1599 p_n IN NUMBER
1600 )
1601 RETURN VARCHAR2
1602 IS
1603 l_first_n_messages VARCHAR2(20000) := '';
1604
1605 BEGIN
1606 FND_MSG_PUB.RESET;
1607 FOR i IN 1..p_n
1608 LOOP
1609 l_first_n_messages := l_first_n_messages || FND_MSG_PUB.GET || ' || ';
1610 END LOOP;
1611 RETURN l_first_n_messages;
1612 END;
1613
1614 -- ----------------------------------------------------------------------
1615
1616 -- From Saurabh
1617
1618 -- body in EAM_ACTIVITYUTILITIES_PVT
1619 FUNCTION BOM_Exists(
1620 p_org_id in number,
1621 p_inventory_item_id in number
1622 )
1623 return boolean is
1624 l_count number;
1625 begin
1626 select count(1) into l_count
1627 from bom_bill_of_materials
1628 where assembly_item_id = p_inventory_item_id and
1629 organization_id = p_org_id;
1630
1631 if l_count > 0 then
1632 return true;
1633 else
1634 return false;
1635 end if;
1636 end bom_exists;
1637
1638 FUNCTION Routing_Exists(
1639 p_org_id in number,
1640 p_inventory_item_id in number
1641 )
1642 return boolean is
1643 l_count number;
1644 begin
1645 select count(1) into l_count
1646 from bom_operational_routings
1647 where assembly_item_id = p_inventory_item_id and
1648 organization_id = p_org_id;
1649
1650 if l_count > 0 then
1651 return true;
1652 else
1653 return false;
1654 end if;
1655 end routing_exists;
1656
1657
1658 -- ----------------------------------------------------------------------
1659 FUNCTION IS_ACTIVITY_ASSIGNED(
1660 p_activity_id IN NUMBER,
1661 p_org_id IN NUMBER
1662 )
1663 RETURN BOOLEAN IS
1664 l_count number;
1665 BEGIN
1666 SELECT count(inventory_item_id) into l_count FROM mtl_system_items_b
1667 WHERE organization_id = p_org_id AND inventory_item_id = p_activity_id
1668 AND eam_item_type = 2;
1669
1670 IF l_count = 0 THEN
1671 return FALSE;
1672 ELSE
1673 return TRUE;
1674 END IF;
1675
1676 END IS_ACTIVITY_ASSIGNED;
1677
1678 -- ----------------------------------------------------------------------
1679 -- To be used in Activity WB view
1680 FUNCTION get_next_service_start_date
1681 (
1682 p_activity_association_id IN NUMBER,
1683 p_maintenance_object_id IN NUMBER,
1684 p_maintenance_object_type IN NUMBER
1685 )
1686 RETURN DATE IS
1687 l_date DATE;
1688 BEGIN
1689 SELECT epa.next_service_start_date into l_date
1690 FROM eam_pm_schedulings eps, eam_pm_activities epa
1691 WHERE eps.maintenance_object_id = p_maintenance_object_id
1692 AND eps.maintenance_object_type = p_maintenance_object_type
1693 AND eps.default_implement = 'Y'
1694 AND eps.pm_schedule_id = epa.pm_schedule_id
1695 AND epa.activity_association_id = p_activity_association_id;
1696 return l_date;
1697 EXCEPTION
1698 WHEN NO_DATA_FOUND THEN
1699 return null;
1700 END get_next_service_start_date;
1701
1702 -- ----------------------------------------------------------------------
1703 -- To be used in Activity WB view
1704 FUNCTION get_next_service_end_date
1705 (
1706 p_activity_association_id IN NUMBER,
1707 p_maintenance_object_id IN NUMBER,
1708 p_maintenance_object_type IN NUMBER
1709 )
1710 RETURN DATE IS
1711 l_date DATE;
1712 BEGIN
1713 SELECT epa.next_service_end_date into l_date
1714 FROM eam_pm_schedulings eps, eam_pm_activities epa
1715 WHERE eps.maintenance_object_id = p_maintenance_object_id
1716 AND eps.maintenance_object_type = p_maintenance_object_type
1717 AND eps.default_implement = 'Y'
1718 AND eps.pm_schedule_id = epa.pm_schedule_id
1719 AND epa.activity_association_id = p_activity_association_id;
1720 return l_date;
1721 EXCEPTION
1722 WHEN NO_DATA_FOUND THEN
1723 return null;
1724 END get_next_service_end_date;
1725
1726 -- ----------------------------------------------------------------------
1727
1728 -- End of Utility Procedures
1729 -- ======================================================================
1730
1731 END EAM_ActivityUtilities_PVT;