[Home] [Help]
PACKAGE BODY: APPS.EAM_TEXT_UTIL
Source
1 PACKAGE BODY eam_text_util AS
2 /* $Header: EAMVTIUB.pls 120.9 2006/09/20 15:00:19 sdandapa noship $*/
3
4
5 -- -----------------------------------------------------------------------------
6 -- Private Globals
7 -- -----------------------------------------------------------------------------
8 g_pkg_name CONSTANT VARCHAR2(30):= 'eam_text_util';
9
10 g_Prod_Short_Name CONSTANT VARCHAR2(30) := 'EAM';
11 g_Prod_Schema VARCHAR2(30);
12 g_Index_Owner VARCHAR2(30);
13
14 g_Indexing_Context VARCHAR2(30) := 'SYNC_INDEX';
15
16 g_installed BOOLEAN;
17 g_inst_status VARCHAR2(1);
18 g_industry VARCHAR2(1);
19
20 g_DB_Version_Num NUMBER := NULL;
21 g_DB_Version_Str VARCHAR2(30) := NULL;
22 g_compatibility VARCHAR2(30) := NULL;
23 l_DB_Version_Str VARCHAR2(30) := NULL;
24 l_DB_Numeric_Character VARCHAR2(30) := NULL;
25
26 -- Global debug flag
27 G_DEBUG VARCHAR2(1) := NVL(fnd_profile.value('EAM_DEBUG'), 'N');
28
29 -- Variable used to buffer text strings before writing into LOB.
30 g_Buffer VARCHAR2(32767);
31 g_Buffer_Length INTEGER;
32
33 -- Document section tags
34 Tag_asset_number CONSTANT VARCHAR2(30) := 'tagassetnumber';
35 Tag_begin_asset_number CONSTANT VARCHAR2(30) := '<' || Tag_asset_number || '>';
36 Tag_end_asset_number CONSTANT VARCHAR2(30) := '</' || Tag_asset_number || '>';
37 Tag_work_order CONSTANT VARCHAR2(30) := 'tagworkorder';
38 Tag_begin_work_order CONSTANT VARCHAR2(30) := '<' || Tag_work_order || '>';
39 Tag_end_work_order CONSTANT VARCHAR2(30) := '</' || Tag_work_order || '>';
40
41 -- -----------------------------------------------------------------------------
42 -- Set_Context
43 -- -----------------------------------------------------------------------------
44
45 PROCEDURE Set_Context ( p_context IN VARCHAR2 )
46 IS
47 BEGIN
48 g_Indexing_Context := p_context;
49 END Set_Context;
50
51
52 -- -----------------------------------------------------------------------------
53 -- Append_VARCHAR_to_LOB
54 -- -----------------------------------------------------------------------------
55
56 PROCEDURE Append_VARCHAR_to_LOB
57 (
58 x_tlob IN OUT NOCOPY CLOB
59 , p_string IN VARCHAR2
60 , p_action IN VARCHAR2 DEFAULT 'APPEND'
61 )
62 IS
63 start_writing BOOLEAN := TRUE;
64 l_offset INTEGER := 1;
65 l_Max_Length INTEGER := 32767;
66 l_String_Length INTEGER;
67 BEGIN
68
69 IF ( p_action = 'BEGIN' ) THEN
70
71 -- Empty the LOB, if this is the first chunk of text to append
72 DBMS_LOB.Trim ( lob_loc => x_tlob, newlen => 0 );
73
74 g_Buffer := p_string;
75 g_Buffer_Length := -1;
76
77 ELSIF ( p_action IN ('APPEND', 'END') ) THEN
78
79 start_writing := ( g_Buffer_Length = -1 );
80 IF ( start_writing ) THEN
81 g_Buffer_Length := Length (g_Buffer);
82 END IF;
83
84 l_String_Length := Length (p_string);
85
86 -- Write buffer to LOB if required
87
88 IF ( g_Buffer_Length + l_String_Length >= l_Max_Length ) THEN
89 IF ( start_writing ) THEN
90 DBMS_LOB.Write ( lob_loc => x_tlob
91 , amount => Length (g_Buffer)
92 , offset => l_offset
93 , buffer => g_Buffer
94 );
95 ELSE
96 DBMS_LOB.WriteAppend ( lob_loc => x_tlob
97 , amount => Length (g_Buffer)
98 , buffer => g_Buffer
99 );
100 END IF;
101
102 -- Reset buffer
103 g_Buffer := p_string;
104 g_Buffer_Length := Length (g_Buffer);
105 ELSE
106 g_Buffer := g_Buffer || p_string;
107 g_Buffer_Length := g_Buffer_Length + l_String_Length;
108 END IF; -- Max_Length reached
109
110 IF ( p_action = 'END' ) THEN
111 start_writing := ( g_Buffer_Length = -1 );
112 IF ( start_writing ) THEN
113 DBMS_LOB.Write ( lob_loc => x_tlob
114 , amount => Length (g_Buffer)
115 , offset => l_offset
116 , buffer => g_Buffer
117 );
118 ELSE
119 DBMS_LOB.WriteAppend ( lob_loc => x_tlob
120 , amount => Length (g_Buffer)
121 , buffer => g_Buffer
122 );
123 END IF;
124 -- Reset buffer
125 g_Buffer := '';
126 g_Buffer_Length := -1;
127 END IF;
128
129 END IF; -- p_action
130
131 END Append_VARCHAR_to_LOB;
132
133
134
135 -- -----------------------------------------------------------------------------
136 -- Get_Asset_Text
137 --- Procedure called from the Intermedia index for asset to find the text on which index has to be created
138 -- -----------------------------------------------------------------------------
139
140 PROCEDURE Get_Asset_Text
141 (
142 p_rowid IN ROWID
143 , p_output_type IN VARCHAR2
144 , x_tlob IN OUT NOCOPY CLOB
145 , x_tchar IN OUT NOCOPY VARCHAR2
146 )
147 IS
148 l_api_name CONSTANT VARCHAR2(30) := 'Get_Asset_Text';
149 l_return_status VARCHAR2(1);
150 l_instance_id NUMBER;
151 l_org_id NUMBER;
152 l_eam_item_type NUMBER;
153 l_criticality_code NUMBER;
154 l_buffer VARCHAR2(32767);
155
156 CURSOR Attribute(p_instance_id NUMBER) IS
157 SELECT
158 attribute_category ||' '|| c_attribute1 ||' '|| c_attribute2 ||' '|| c_attribute3
159 ||' '|| c_attribute4 ||' '|| c_attribute5 ||' '|| c_attribute6 ||' '|| c_attribute7
160 ||' '|| c_attribute8 ||' '|| c_attribute9 ||' '|| c_attribute10 ||' '|| c_attribute11
161 ||' '|| c_attribute12 ||' '|| c_attribute13 ||' '|| c_attribute14 ||' '|| c_attribute15
162 ||' '|| c_attribute16 ||' '|| c_attribute17 ||' '|| c_attribute18 ||' '|| c_attribute19
163 ||' '|| c_attribute20 ||' '|| d_attribute1 ||' '|| d_attribute2 ||' '|| d_attribute3
164 ||' '|| d_attribute4 ||' '|| d_attribute5 ||' '|| d_attribute6 ||' '|| d_attribute7
165 ||' '|| d_attribute8 ||' '|| d_attribute9 ||' '|| d_attribute10 ||' '|| n_attribute1
166 ||' '|| n_attribute2 ||' '|| n_attribute3 ||' '|| n_attribute4 ||' '|| n_attribute5
167 ||' '|| n_attribute6 ||' '|| n_attribute7 ||' '|| n_attribute8 ||' '|| n_attribute9
168 ||' '|| n_attribute10 as value
169 FROM mtl_eam_asset_attr_values meaav
170 WHERE meaav.maintenance_object_id = p_instance_id;
171
172 CURSOR lookup_meaning(p_lookup_type VARCHAR2, p_lookup_code NUMBER) IS
173 SELECT meaning
174 FROM fnd_lookup_values
175 WHERE lookup_type = p_lookup_type
176 AND lookup_code = p_lookup_code;
177
178 /* Search based on Activity and meter will be enabled in future
179
180 CURSOR Activity(p_instance_id NUMBER) IS
181 SELECT
182 msi.concatenated_segments as value
183 FROM
184 mtl_system_items_b_kfv msi
185 , mtl_eam_asset_activities meaa
186 WHERE
187 meaa.maintenance_object_id = p_instance_id
188 AND meaa.maintenance_object_type = 3
189 AND meaa.asset_activity_id = msi.inventory_item_id
190 AND rownum = 1;
191
192 -- Meter are now migrated to counter's schema...
193 -- following query needs to be changed
194 CURSOR Meter(p_instance_id NUMBER) IS
195 SELECT
196 em.meter_name as value
197 FROM
198 eam_asset_meters eam
199 , eam_meters em
200 WHERE
201 eam.maintenance_object_id = p_instance_id
202 AND eam.maintenance_object_type = 3
203 AND eam.meter_id = em.meter_id;
204 */
205
206 BEGIN
207
208 -----------------------------------------------------------
209 -- Get CII Data
210 -----------------------------------------------------------
211 l_buffer := NULL;
212
213 BEGIN
214
215 SELECT
216 eat.instance_id, cii.last_vld_organization_id, msi.eam_item_type , cii.asset_criticality_code,
217 Tag_begin_asset_number ||' '|| cii.instance_number ||' '|| cii.instance_description ||' '||
218 Tag_end_asset_number ||' '|| cii.serial_number ||' '|| msi.concatenated_segments ||' '||
219 msi.description ||' '|| mck.concatenated_segments ||' '|| msi.description ||' '||
220 cii.context ||' '|| cii.attribute1 ||' '|| cii.attribute2 ||' '|| cii.attribute3 ||' '||
221 cii.attribute4 ||' '|| cii.attribute5 ||' '|| cii.attribute6 ||' '|| cii.attribute7 ||' '||
222 cii.attribute8 ||' '|| cii.attribute9 ||' '|| cii.attribute10 ||' '|| cii.attribute11
223 ||' '|| cii.attribute12 ||' '|| cii.attribute13 ||' '|| cii.attribute14 ||' '||
224 cii.attribute15 ||' '|| cii.attribute16 ||' '|| cii.attribute17 ||' '|| cii.attribute18
225 ||' '|| cii.attribute19 ||' '|| cii.attribute20 ||' '|| cii.attribute21 ||' '||
226 cii.attribute22 ||' '|| cii.attribute23 ||' '|| cii.attribute24 ||' '|| cii.attribute25
227 ||' '|| cii.attribute26 ||' '|| cii.attribute27 ||' '|| cii.attribute28 ||' '||
228 cii.attribute29 ||' '|| cii.attribute30 ||' '|| msi.attribute_category ||' '||
229 msi.attribute1 ||' '|| msi.attribute2 ||' '|| msi.attribute3 ||' '|| msi.attribute4
230 ||' '|| msi.attribute5 ||' '|| msi.attribute6 ||' '|| msi.attribute7 ||' '||
231 msi.attribute8 ||' '|| msi.attribute9 ||' '|| msi.attribute10 ||' '|| msi.attribute11
232 ||' '|| msi.attribute12 ||' '|| msi.attribute13 ||' '|| msi.attribute14 ||' '||
233 msi.attribute15
234 INTO
235 l_instance_id, l_org_id, l_eam_item_type, l_criticality_code, l_buffer
236 FROM
237 eam_asset_text eat
238 , csi_item_instances cii
239 , mtl_system_items_b_kfv msi
240 , mtl_categories_kfv mck
241 WHERE
242 eat.rowid = p_rowid
243 AND eat.instance_id = cii.instance_id
244 AND nvl(cii.active_start_date, sysdate-1) <= sysdate
245 AND nvl(cii.active_end_date, sysdate+1) >= sysdate
246 AND cii.inventory_item_id = msi.inventory_item_id
247 AND cii.last_vld_organization_id = msi.organization_id
248 AND msi.serial_number_control_code <> 1
249 AND cii.category_id = mck.category_id(+);
250
251 EXCEPTION
252 WHEN no_data_found THEN
253 /*IF (g_Debug) THEN
254 Debug(p_rowid, null, '** 0: ' || SQLERRM);
255 END IF;*/
256 Raise;
257 END;
258
259 IF ( p_output_type = 'VARCHAR2' ) THEN
260 x_tchar := l_buffer;
261 ELSE
262 Append_VARCHAR_to_LOB (x_tlob, ' ', 'BEGIN');
263 Append_VARCHAR_to_LOB (x_tlob, l_buffer);
264 END IF;
265
266 -- Get the Maintenance Attributes of the Asset -------------------
267 l_buffer := NULL;
268 BEGIN
269 SELECT bd.department_code ||' '|| mel.location_codes ||' '|| eomd.accounting_class_code
270 INTO l_buffer
271 FROM eam_org_maint_defaults eomd, bom_departments bd, mtl_eam_locations mel,
272 mtl_parameters mp
273 WHERE mp.organization_id = l_org_id AND mp.maint_organization_id = eomd.organization_id
274 AND eomd.object_id = l_instance_id AND eomd.object_type = 50
275 AND eomd.owning_department_id = bd.department_id (+) AND eomd.area_id = mel.location_id(+);
276
277 IF ( p_output_type = 'VARCHAR2' ) THEN
278 x_tchar := x_tchar ||' '||l_buffer;
279 ELSE
280 Append_VARCHAR_to_LOB (x_tlob, l_buffer);
281 END IF;
282
283 EXCEPTION
284 WHEN NO_DATA_FOUND THEN
285 null;
286 END;
287
288 -- Get the Asset Type -------------------------------------------
289 l_buffer := NULL;
290 FOR asset_type in lookup_meaning('MTL_EAM_ASSET_TYPE', l_eam_item_type) LOOP
291 l_buffer := l_buffer ||' '|| asset_type.meaning;
292 END LOOP;
293
294 IF ( p_output_type = 'VARCHAR2' ) THEN
295 x_tchar := x_tchar ||' '||l_buffer;
296 ELSE
297 Append_VARCHAR_to_LOB (x_tlob, l_buffer);
298 END IF;
299
300 -- Get the Asset criticality code ---------------------------------
301 l_buffer := NULL;
302 FOR asset_criticality in lookup_meaning('MTL_EAM_ASSET_CRITICALITY', l_criticality_code) LOOP
303 l_buffer := l_buffer ||' '|| asset_criticality.meaning;
304 END LOOP;
305
306 IF ( p_output_type = 'VARCHAR2' ) THEN
307 x_tchar := x_tchar ||' '||l_buffer;
308 ELSE
309 Append_VARCHAR_to_LOB (x_tlob, l_buffer);
310 END IF;
311
312 -- Get the Asset Attributes Details ------------------------------
313 l_buffer := NULL;
314 FOR attribute_value in Attribute(l_instance_id) LOOP
315 l_buffer := l_buffer ||' '|| attribute_value.value;
316 END LOOP;
317
318 IF ( p_output_type = 'VARCHAR2' ) THEN
319 x_tchar := x_tchar ||' '||l_buffer;
320 ELSE
321 Append_VARCHAR_to_LOB (x_tlob, l_buffer);
322 END IF;
323
324 /* As per the TDD review, we will add this feature in the future
325 -- Get the Activities Associated Details ------------------------------
326 l_buffer := NULL;
327 FOR activity_name in Activity(l_gen_object_id) LOOP
328 l_buffer := l_buffer ||' '|| activity_name.value;
329 END LOOP;
330
331 IF ( p_output_type = 'VARCHAR2' ) THEN
332 x_tchar := x_tchar ||' '||l_buffer;
333 ELSE
334 Append_VARCHAR_to_LOB (x_tlob, l_buffer);
335 END IF;
336
337
338 -- Get the Meters Associated Details ------------------------------------
339 l_buffer := NULL;
340 FOR meter_name in Meter(l_gen_object_id) LOOP
341 l_buffer := l_buffer ||' '|| meter_name.value;
342 END LOOP;
343
344 IF ( p_output_type = 'VARCHAR2' ) THEN
345 x_tchar := x_tchar ||' '||l_buffer;
346 ELSE
347 Append_VARCHAR_to_LOB (x_tlob, l_buffer);
348 END IF;
349 */
350
351 Append_VARCHAR_to_LOB (x_tlob, ' ', 'END');
352
353
354 EXCEPTION
355
356 WHEN others THEN
357 --eam_text_util.Log_Error ('SQL_ERROR', SQLERRM);
358 --IF (g_Debug) THEN Debug(l_item_id, l_org_id, l_item_code, '** 9: ' || SQLERRM); END IF;
359 RAISE;
360
361 END Get_Asset_Text;
362
363
364 -- -----------------------------------------------------------------------------
365 -- Get_Wo_Text
366 -- Procedure called from the Intermedia index for work ordersto find the text on which index has to be created
367 -- -----------------------------------------------------------------------------
368
369 PROCEDURE Get_Wo_Text
370 (
371 p_rowid IN ROWID
372 , p_output_type IN VARCHAR2
373 , x_tlob IN OUT NOCOPY CLOB
374 , x_tchar IN OUT NOCOPY VARCHAR2
375 )
376 IS
377 l_api_name CONSTANT VARCHAR2(30) := 'Get_Wo_Text';
378 l_return_status VARCHAR2(1);
379 l_wip_entity_id NUMBER;
380 l_org_id NUMBER;
381 l_priority NUMBER;
382 l_work_order_type NUMBER;
383 l_activity_type VARCHAR2(30);
384 l_activity_cause VARCHAR2(30);
385 l_activity_source VARCHAR2(30);
386 l_maint_obj_type NUMBER;
387
388 l_buffer VARCHAR2(32767);
389
390 CURSOR Operation(p_wip_entity_id NUMBER) IS
391 SELECT
392 wo.operation_seq_num||' '||bd.department_code as value
393 FROM WIP_OPERATIONS wo,BOM_DEPARTMENTS bd
394 WHERE wo.wip_entity_id=p_wip_entity_id
395 AND wo.department_id=bd.department_id;
396
397 CURSOR Resource_Details(p_wip_entity_id NUMBER) IS
398 SELECT
399 br.resource_code as value
400 FROM WIP_OPERATION_RESOURCES wor,BOM_RESOURCES br
401 WHERE wor.wip_entity_id= p_wip_entity_id
402 AND wor.resource_id = br.resource_id;
403
404 CURSOR Employee(p_wip_entity_id NUMBER) IS
405 SELECT
406 ppf.full_name as value
407 FROM WIP_OP_RESOURCE_INSTANCES wori,
408 BOM_RESOURCE_EMPLOYEES bre,PER_ALL_PEOPLE_F ppf
409 WHERE wori.wip_entity_id = p_wip_entity_id
410 AND wori.instance_id = bre.instance_id
411 AND bre.person_id = ppf.person_id;
412
413
414 CURSOR Work_Order(p_wip_entity_id NUMBER,p_maint_obj_type NUMBER,p_org_id NUMBER) IS
415 SELECT (Tag_begin_work_order ||' '||we.wip_entity_name||
416 ' '||wdj.description||' '||cii.instance_number||' '||msik.concatenated_segments||' '||
417 cii.serial_number||' '||msik1.concatenated_segments||' '||Tag_end_work_order||
418 ' '||bd.department_code||' '||PJM_PROJECT.ALL_PROJ_IDTONUM(wdj.project_id)||' '||
419 PJM_PROJECT.ALL_TASK_IDTONUM(wdj.task_id)) as value
420 FROM WIP_ENTITIES we,WIP_DISCRETE_JOBS wdj,CSI_ITEM_INSTANCES cii,
421 EAM_WORK_ORDER_DETAILS ewod,
422 BOM_DEPARTMENTS bd, MTL_SYSTEM_ITEMS_B_KFV msik, MTL_SYSTEM_ITEMS_B_KFV msik1,
423 MTL_PARAMETERS mp
424 WHERE we.wip_entity_id = p_wip_entity_id
425 AND we.wip_entity_id = wdj.wip_entity_id
426 AND wdj.wip_entity_id = ewod.wip_entity_id
427 AND wdj.owning_department = bd.department_id(+)
428 AND msik1.organization_id(+)=wdj.organization_id
429 AND msik1.inventory_item_id(+)=wdj.primary_item_id
430 AND msik.inventory_item_id=NVL(wdj.rebuild_item_id,wdj.asset_group_id)
431 AND msik.organization_id = mp.organization_id
432 AND cii.instance_id(+)=DECODE(wdj.maintenance_object_type,p_maint_obj_type,wdj.maintenance_object_id,NULL)
433 AND mp.maint_organization_id = p_org_id;
434
435 CURSOR lookup_meaning(p_lookup_type VARCHAR2, p_lookup_code VARCHAR2) IS
436 SELECT meaning
437 FROM fnd_lookup_values
438 WHERE lookup_type = p_lookup_type
439 AND lookup_code = p_lookup_code;
440
441 CURSOR status(p_wip_entity_id NUMBER) IS
442 SELECT NVL(ewst.user_defined_status,flv.meaning) as value
443 FROM EAM_WORK_ORDER_DETAILS ewod, EAM_WO_STATUSES_B ewsb,
444 EAM_WO_STATUSES_TL ewst,FND_LOOKUP_VALUES flv
445 WHERE ewod.wip_entity_id = p_wip_entity_id
446 AND ewod.user_defined_status_id = ewsb.status_id
447 AND ewsb.status_id = ewst.status_id(+)
448 AND flv.lookup_type(+) = 'WIP_JOB_STATUS'
449 AND flv.lookup_code(+) = ewsb.status_id;
450
451
452 BEGIN
453
454 -----------------------------------------------------------
455 -- Get WO Data
456 -----------------------------------------------------------
457 l_buffer := NULL;
458
459 BEGIN
460
461 SELECT
462 ewot.wip_entity_id,ewot.organization_id,wdj.priority,wdj.work_order_type,
463 wdj.activity_type,wdj.activity_cause,wdj.activity_source
464 INTO
465 l_wip_entity_id,l_org_id,l_priority,l_work_order_type,l_activity_type,l_activity_cause,l_activity_source
466 FROM
467 eam_work_order_text ewot,wip_discrete_jobs wdj
468 WHERE
469 ewot.rowid = p_rowid
470 AND ewot.wip_entity_id = wdj.wip_entity_id;
471
472 EXCEPTION
473 WHEN no_data_found THEN
474 Raise;
475 END;
476
477
478 -- Get the Work Order details -------------------
479 l_buffer := NULL;
480
481 l_maint_obj_type := 3;
482 FOR work_details in Work_Order(l_wip_entity_id,l_maint_obj_type,l_org_id)
483 LOOP
484 l_buffer := l_buffer ||' '|| work_details.value;
485 EXIT; --return after first row
486 END LOOP;
487
488 IF ( p_output_type = 'VARCHAR2' ) THEN
489 x_tchar := l_buffer;
490 ELSE
491 Append_VARCHAR_to_LOB (x_tlob, ' ', 'BEGIN');
492 Append_VARCHAR_to_LOB (x_tlob, l_buffer);
493 END IF;
494
495 -- Get the work order status details in all the langauges so that text search fetches the correct work order
496 -- irrespective of the current language of the user. To fetch status for all languages we are fetching from
497 -- base tables instaed of view
498 l_buffer := NULL;
499 FOR status_details in Status(l_wip_entity_id) LOOP
500 l_buffer := l_buffer ||' '|| status_details.value;
501 END LOOP;
502
503 IF ( p_output_type = 'VARCHAR2' ) THEN
504 x_tchar := x_tchar ||' '||l_buffer;
505 ELSE
506 Append_VARCHAR_to_LOB (x_tlob, l_buffer);
507 END IF;
508
509 -- Get the Operation details -------------------------------------------
510 l_buffer := NULL;
511 FOR op_details in Operation(l_wip_entity_id) LOOP
512 l_buffer := l_buffer ||' '|| op_details.value;
513 END LOOP;
514
515 IF ( p_output_type = 'VARCHAR2' ) THEN
516 x_tchar := x_tchar ||' '||l_buffer;
517 ELSE
518 Append_VARCHAR_to_LOB (x_tlob, l_buffer);
519 END IF;
520
521 -- Get the Resource details ---------------------------------
522 l_buffer := NULL;
523 FOR res_details in Resource_Details(l_wip_entity_id) LOOP
524 l_buffer := l_buffer ||' '|| res_details.value;
525 END LOOP;
526
527 IF ( p_output_type = 'VARCHAR2' ) THEN
528 x_tchar := x_tchar ||' '||l_buffer;
529 ELSE
530 Append_VARCHAR_to_LOB (x_tlob, l_buffer);
531 END IF;
532
533 -- Get the Employee Details ------------------------------
534 l_buffer := NULL;
535 FOR emp_details in Employee(l_wip_entity_id) LOOP
536 l_buffer := l_buffer ||' '|| emp_details.value;
537 END LOOP;
538
539 IF ( p_output_type = 'VARCHAR2' ) THEN
540 x_tchar := x_tchar ||' '||l_buffer;
541 ELSE
542 Append_VARCHAR_to_LOB (x_tlob, l_buffer);
543 END IF;
544
545 --Get the Work Order Priority details--------------
546 l_buffer := NULL;
547 FOR lookup_details in lookup_meaning('WIP_EAM_ACTIVITY_PRIORITY',TO_CHAR(l_priority)) LOOP
548 l_buffer := l_buffer ||' '|| lookup_details.meaning;
549 END LOOP;
550
551 IF ( p_output_type = 'VARCHAR2' ) THEN
552 x_tchar := x_tchar ||' '||l_buffer;
553 ELSE
554 Append_VARCHAR_to_LOB (x_tlob, l_buffer);
555 END IF;
556
557 --Get the Work Order Type details--------------
558 l_buffer := NULL;
559 FOR lookup_details in lookup_meaning('WIP_EAM_WORK_ORDER_TYPE',TO_CHAR(l_work_order_type)) LOOP
560 l_buffer := l_buffer ||' '|| lookup_details.meaning;
561 END LOOP;
562
563 IF ( p_output_type = 'VARCHAR2' ) THEN
564 x_tchar := x_tchar ||' '||l_buffer;
565 ELSE
566 Append_VARCHAR_to_LOB (x_tlob, l_buffer);
567 END IF;
568
569 --Get the Activity Type details--------------
570 l_buffer := NULL;
571 FOR lookup_details in lookup_meaning('MTL_EAM_ACTIVITY_TYPE',l_activity_type) LOOP
572 l_buffer := l_buffer ||' '|| lookup_details.meaning;
573 END LOOP;
574
575 IF ( p_output_type = 'VARCHAR2' ) THEN
576 x_tchar := x_tchar ||' '||l_buffer;
577 ELSE
578 Append_VARCHAR_to_LOB (x_tlob, l_buffer);
579 END IF;
580
581 --Get the Activity Cause details--------------
582 l_buffer := NULL;
583 FOR lookup_details in lookup_meaning('MTL_EAM_ACTIVITY_CAUSE',l_activity_cause) LOOP
584 l_buffer := l_buffer ||' '|| lookup_details.meaning;
585 END LOOP;
586
587 IF ( p_output_type = 'VARCHAR2' ) THEN
588 x_tchar := x_tchar ||' '||l_buffer;
589 ELSE
590 Append_VARCHAR_to_LOB (x_tlob, l_buffer);
591 END IF;
592
593 --Get the Activity Source details--------------
594 l_buffer := NULL;
595 FOR lookup_details in lookup_meaning('MTL_EAM_ACTIVITY_SOURCE',l_activity_source) LOOP
596 l_buffer := l_buffer ||' '|| lookup_details.meaning;
597 END LOOP;
598
599 IF ( p_output_type = 'VARCHAR2' ) THEN
600 x_tchar := x_tchar ||' '||l_buffer;
601 ELSE
602 Append_VARCHAR_to_LOB (x_tlob, l_buffer);
603 END IF;
604
605
606 --End the LOB
607 Append_VARCHAR_to_LOB (x_tlob, ' ', 'END');
608
609
610 EXCEPTION
611 WHEN others THEN
612 RAISE;
613 END Get_Wo_Text;
614
615
616 -- -----------------------------------------------------------------------------
617 -- Process_Asset_DML_Opn
618 --- Procedure to insert / update / delete records in eam_asset_text
619 -- -----------------------------------------------------------------------------
620 PROCEDURE Process_Asset_DML_Opn
621 (
622 p_event IN VARCHAR2
623 , p_instance_id IN NUMBER
624 , p_last_update_date IN VARCHAR2 DEFAULT FND_API.G_MISS_DATE
625 , p_last_updated_by IN VARCHAR2 DEFAULT FND_API.G_MISS_NUM
626 , p_last_update_login IN VARCHAR2 DEFAULT FND_API.G_MISS_NUM
627 )
628 IS
629 l_text_ins VARCHAR2(1);
630 l_text_upd VARCHAR2(1);
631 l_count NUMBER;
632 BEGIN
633 l_text_ins := '1';
634 l_text_upd := '2';
635
636 SELECT count(instance_id) INTO l_count
637 FROM eam_asset_text WHERE instance_id = p_instance_id AND rownum = 1;
638
639 IF ( p_event = 'UPDATE' OR p_event = 'INSERT' ) THEN
640 IF (l_count = 1) THEN
641 UPDATE eam_asset_text
642 SET text = l_text_upd
643 , last_update_date = SYSDATE
644 , last_updated_by = DECODE(p_last_updated_by, FND_API.G_MISS_NUM, last_updated_by, p_last_updated_by)
645 , last_update_login = DECODE(p_last_update_login, FND_API.G_MISS_NUM, last_update_login, p_last_update_login)
646 WHERE instance_id = p_instance_id;
647
648 ELSIF (l_count = 0) THEN
649
650 INSERT INTO eam_asset_text
651 (
652 instance_id
653 , text
654 , creation_date
655 , created_by
656 , last_update_date
657 , last_updated_by
658 , last_update_login
659 )
660 values (
661 p_instance_id
662 , l_text_ins
663 , SYSDATE
664 , fnd_global.user_id
665 , SYSDATE
666 , DECODE(p_last_updated_by, FND_API.G_MISS_NUM, fnd_global.user_id, p_last_updated_by)
667 , DECODE(p_last_update_login, FND_API.G_MISS_NUM, fnd_global.login_id, p_last_update_login));
668 END IF;
669 ELSIF ( p_event = 'DELETE' ) THEN
670 DELETE FROM eam_asset_text
671 WHERE p_instance_id = p_instance_id;
672
673 END IF; -- p_event
674 EXCEPTION
675 WHEN DUP_VAL_ON_INDEX THEN
676 null;
677 END Process_Asset_DML_Opn;
678
679
680
681
682 -- -----------------------------------------------------------------------------
683 -- Process_Asset_Update_Event
684 --- Procedure called when an asset is create/updated
685 -- -----------------------------------------------------------------------------
686 PROCEDURE Process_Asset_Update_Event
687 (
688 p_event IN VARCHAR2 DEFAULT NULL
689 , p_instance_id IN NUMBER
690 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
691 , p_last_update_date IN VARCHAR2 DEFAULT FND_API.G_MISS_DATE
692 , p_last_updated_by IN VARCHAR2 DEFAULT FND_API.G_MISS_NUM
693 , p_last_update_login IN VARCHAR2 DEFAULT FND_API.G_MISS_NUM
694 )
695 IS
696 l_eam VARCHAR2(5);
697 l_ctx VARCHAR2(8);
698 l_table VARCHAR2(25);
699 l_index VARCHAR2(25);
700 l_status VARCHAR2(15);
701 l_count NUMBER;
702 BEGIN
703
704 IF (p_instance_id <> -1 AND p_event IS NOT NULL) THEN
705 /* Perform DML operation */
706 Process_Asset_DML_Opn( p_event
707 , p_instance_id
708 , p_last_update_date
709 , p_last_updated_by
710 , p_last_update_login );
711 IF (p_commit = FND_API.G_TRUE) THEN
712 COMMIT;
713 END IF;
714 END IF;
715
716 /* If Text index exists execute sync up */
717 IF (p_commit = FND_API.G_TRUE OR p_instance_id = -1) THEN
718
719 l_eam := 'EAM';
720 l_ctx := 'CTXSYS';
721 l_table := 'EAM_ASSET_TEXT';
722 l_index := 'EAM_ASSET_TEXT_CTX1';
723 l_status := 'VALID';
724 SELECT count(*) into l_count
725 FROM all_indexes
726 WHERE (owner = l_eam OR owner = USER OR owner = l_ctx)
727 AND table_name = l_table AND index_name = l_index
728 AND status = l_status AND domidx_status = l_status AND domidx_opstatus = l_status;
729 IF (l_count > 0) THEN
730 EXECUTE IMMEDIATE
731 ' BEGIN '||
732 ' eam_text_util.Sync_Index(''EAM_ASSET_TEXT_CTX1''); '||
733 ' END;';
734 /* Calling sync_index would cause database commit. Should be called after commit */
735 END IF;
736 END IF;
737
738 EXCEPTION
739
740 WHEN others THEN
741 --Raise_Application_Error (-20001, 'Process_Asset_Update_Event: ' || SQLERRM);
742 RAISE;
743
744 END Process_Asset_Update_Event;
745
746 -- -----------------------------------------------------------------------------
747 -- Process_Wo_DML_Opn
748 --- Procedure to insert / update / delete records in eam_work_order_text
749 -- -----------------------------------------------------------------------------
750 PROCEDURE Process_Wo_Dml_Opn
751 (
752 p_event IN VARCHAR2,
753 p_wip_entity_id IN NUMBER,
754 p_organization_id IN NUMBER
755 , p_last_update_date IN DATE DEFAULT FND_API.G_MISS_DATE
756 , p_last_updated_by IN NUMBER DEFAULT FND_API.G_MISS_NUM
757 , p_last_update_login IN NUMBER DEFAULT FND_API.G_MISS_NUM
758 )
759 IS
760 l_text_ins VARCHAR2(1) := '1';
761 l_text_upd VARCHAR2(1) := '2';
762 BEGIN
763
764 IF ( p_event = 'UPDATE' ) THEN
765 UPDATE eam_work_order_text
766 SET text = l_text_upd
767 , last_update_date = SYSDATE
768 , last_updated_by = FND_GLOBAL.user_id
769 , last_update_login = FND_GLOBAL.login_id
770 WHERE wip_entity_id = p_wip_entity_id;
771
772 ELSIF ( p_event = 'INSERT' ) THEN
773 INSERT INTO eam_work_order_text
774 (
775 organization_id
776 , wip_entity_id
777 , text
778 , creation_date
779 , created_by
780 , last_update_date
781 , last_updated_by
782 , last_update_login
783 )
784 values
785 (
786 p_organization_id,
787 p_wip_entity_id,
788 l_text_ins,
789 SYSDATE,
790 FND_GLOBAL.user_id,
791 SYSDATE,
792 FND_GLOBAL.user_id,
793 FND_GLOBAL.login_id
794 );
795 END IF; -- p_event
796
797
798 EXCEPTION
799 WHEN DUP_VAL_ON_INDEX THEN
800 NULL;
801 END Process_Wo_Dml_Opn;
802
803 -- -----------------------------------------------------------------------------
804 -- Process_Wo_Event
805 --- Proedure called when a workorder is created/updated
806 -- -----------------------------------------------------------------------------
807
808 PROCEDURE Process_Wo_Event
809 (
810 p_event IN VARCHAR2 DEFAULT NULL,
811 p_wip_entity_id IN NUMBER,
812 p_organization_id IN NUMBER DEFAULT NULL
813 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
814 , p_last_update_date IN DATE DEFAULT FND_API.G_MISS_DATE
815 , p_last_updated_by IN NUMBER DEFAULT FND_API.G_MISS_NUM
816 , p_last_update_login IN NUMBER DEFAULT FND_API.G_MISS_NUM
817 )
818 IS
819 l_eam VARCHAR2(5);
820 l_ctx VARCHAR2(8);
821 l_table VARCHAR2(25);
822 l_index VARCHAR2(30);
823 l_status VARCHAR2(15);
824 l_count NUMBER;
825
826 BEGIN
827
828 /* Perform DML Operation */
829 IF (p_wip_entity_id <> -1 AND p_event IS NOT NULL) THEN
830 Process_Wo_Dml_Opn
831 (p_event,
832 p_wip_entity_id,
833 p_organization_id,
834 p_last_update_date,
835 p_last_updated_by,
836 p_last_update_login
837 );
838
839 END IF;
840
841 /* If Text index exists execute sync up */
842 IF (p_commit = FND_API.G_TRUE OR p_wip_entity_id = -1) THEN
843 l_eam := 'EAM';
844 l_ctx := 'CTXSYS';
845 l_table := 'EAM_WORK_ORDER_TEXT';
846 l_index := 'EAM_WORK_ORDER_TEXT_CTX1';
847 l_status := 'VALID';
848 SELECT count(*) into l_count
849 FROM all_indexes
850 WHERE (owner = l_eam OR owner = USER OR owner = l_ctx)
851 AND table_name = l_table AND index_name = l_index
852 AND status = l_status AND domidx_status = l_status AND domidx_opstatus = l_status;
853 IF (l_count > 0) THEN
854 EXECUTE IMMEDIATE
855 ' BEGIN '||
856 ' eam_text_util.Sync_Index(''EAM_WORK_ORDER_TEXT_CTX1''); '||
857 ' END;';
858 END IF;
859 END IF;
860
861 EXCEPTION
862 WHEN others THEN
863 RAISE;
864 END Process_Wo_Event;
865
866 /***
867 ***** Procedure called when a status code is updated from User Defined Statuses form
868 ***/
869 PROCEDURE Process_Status_Update_Event
870 (
871 p_event IN VARCHAR2 DEFAULT NULL,
872 p_status_id IN NUMBER
873 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
874 , p_last_update_date IN DATE DEFAULT FND_API.G_MISS_DATE
875 , p_last_updated_by IN NUMBER DEFAULT FND_API.G_MISS_NUM
876 , p_last_update_login IN NUMBER DEFAULT FND_API.G_MISS_NUM
877 , x_return_status IN OUT NOCOPY VARCHAR2
878 )
879 IS
880 l_eam VARCHAR2(5);
881 l_ctx VARCHAR2(8);
882 l_table VARCHAR2(25);
883 l_index VARCHAR2(30);
884 l_status VARCHAR2(15);
885 l_count NUMBER;
886
887 CURSOR workorders(l_status_id NUMBER) IS
888 SELECT ewod.wip_entity_id,ewod.organization_id
889 FROM EAM_WORK_ORDER_DETAILS ewod
890 WHERE ewod.user_defined_status_id = l_status_id;
891
892 BEGIN
893
894 /* Perform DML Operation */
895 IF (p_status_id <> -1) THEN
896 FOR wo in workorders(p_status_id) LOOP
897 Process_Wo_Dml_Opn
898 ('UPDATE',
899 wo.wip_entity_id,
900 wo.organization_id,
901 p_last_update_date,
902 p_last_updated_by,
903 p_last_update_login
904 );
905 END LOOP;
906 END IF;
907
908 /* If Text index exists execute sync up */
909 IF (p_commit = FND_API.G_TRUE OR p_status_id = -1) THEN
910
911 l_eam := 'EAM';
912 l_ctx := 'CTXSYS';
913 l_table := 'EAM_WORK_ORDER_TEXT';
914 l_index := 'EAM_WORK_ORDER_TEXT_CTX1';
915 l_status := 'VALID';
916
917 SELECT count(*) into l_count
918 FROM all_indexes
919 WHERE (owner = l_eam OR owner = USER OR owner = l_ctx)
920 AND table_name = l_table AND index_name = l_index
921 AND status = l_status AND domidx_status = l_status AND domidx_opstatus = l_status;
922
923 IF (l_count > 0) THEN
924 EXECUTE IMMEDIATE
925 ' BEGIN '||
926 ' eam_text_util.Sync_Index(''EAM_WORK_ORDER_TEXT_CTX1''); '||
927 ' END;';
928 END IF;
929
930 END IF;
931
932 EXCEPTION
933 WHEN others THEN
934 RAISE;
935 END Process_Status_Update_Event;
936
937 -- -----------------------------------------------------------------------------
938 -- Sync_Index
939 --- Procedure called when the intermedia index has to be updated
940 -- -----------------------------------------------------------------------------
941 PROCEDURE Sync_Index ( p_idx_name IN VARCHAR2)
942 IS
943 BEGIN
944 AD_CTX_DDL.Sync_Index ( idx_name => g_Index_Owner ||'.'|| p_idx_name);
945 END Sync_Index;
946
947
948 -- -----------------------------------------------------------------------------
949 -- get_Prod_Schema
950 -- -----------------------------------------------------------------------------
951
952 FUNCTION get_Prod_Schema
953 RETURN VARCHAR2
954 IS
955 BEGIN
956 RETURN (g_Prod_Schema);
957 END get_Prod_Schema;
958
959 -- -----------------------------------------------------------------------------
960 -- get_DB_Version_Num
961 -- -----------------------------------------------------------------------------
962
963 FUNCTION get_DB_Version_Num
964 RETURN NUMBER
965 IS
966 BEGIN
967 RETURN (g_DB_Version_Num);
968 END get_DB_Version_Num;
969
970 FUNCTION get_DB_Version_Str
971 RETURN VARCHAR2
972 IS
973 BEGIN
974 RETURN (g_DB_Version_Str);
975 END get_DB_Version_Str;
976
977
978 -- *****************************************************************************
979 -- ** Package initialization block **
980 -- *****************************************************************************
981
982 BEGIN
983
984 ------------------------------------------------------------------
985 -- Determine index schema and store in a private global variable
986 ------------------------------------------------------------------
987
988 g_installed := FND_INSTALLATION.Get_App_Info ('EAM', g_inst_status, g_industry, g_Prod_Schema);
989 g_Index_Owner := g_Prod_Schema;
990
991 -------------------------
992 -- Determine DB version
993 -------------------------
994
995 DBMS_UTILITY.db_Version (g_DB_Version_Str, g_compatibility);
996 l_DB_Version_Str := SUBSTR(g_DB_Version_Str, 1, INSTR(g_DB_Version_Str, '.', 1, 2) - 1);
997 SELECT SUBSTR(VALUE,0,1) into l_DB_Numeric_Character
998 FROM V$NLS_PARAMETERS
999 WHERE PARAMETER = 'NLS_NUMERIC_CHARACTERS';
1000 g_DB_Version_Num := TO_NUMBER( REPLACE(l_DB_Version_Str, '.', l_DB_Numeric_Character) );
1001
1002
1003 END eam_text_util;
1004
1005