1 PACKAGE EAM_COMMON_UTILITIES_PVT AS
2 /* $Header: EAMPUTLS.pls 120.12.12010000.2 2008/11/06 23:51:26 mashah ship $*/
3 -- Start of comments
4 -- API name : APIname
5 -- Type : Public or Group or Private.
6 -- Function :
7 -- Pre-reqs : None.
8 -- Parameters :
9 -- IN p_api_version IN NUMBER Required
10 -- p_init_msg_list IN VARCHAR2 Optional
11 -- Default = FND_API.G_FALSE
12 -- p_commit IN VARCHAR2 Optional
13 -- Default = FND_API.G_FALSE
14 -- p_validation_level IN NUMBER Optional
15 -- Default = FND_API.G_VALID_LEVEL_FULL
16 -- parameter1
17 -- parameter2
18 -- .
19 -- .
20 -- OUT x_return_status OUT VARCHAR2(1)
21 -- x_msg_count OUT NUMBER
22 -- x_msg_data OUT VARCHAR2(2000)
23 -- parameter1
24 -- parameter2
25 -- .
26 -- .
27 -- Version Current version x.x
28 -- Changed....
29 -- previous version y.y
30 -- Changed....
31 -- .
32 -- .
33 -- previous version 2.0
34 -- Changed....
35 -- Initial version 1.0
36 --
37 -- Notes : Note text
38 --
39 -- End of comments
40
41 PROCEDURE get_org_code(
42 p_api_version IN NUMBER
43 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
44 ,p_commit IN VARCHAR2 := fnd_api.g_false
45 ,p_validation_level IN NUMBER
46 := fnd_api.g_valid_level_full
47 ,p_organization_id IN NUMBER
48 ,x_organization_code OUT NOCOPY VARCHAR2
49 ,x_return_status OUT NOCOPY VARCHAR2
50 ,x_msg_count OUT NOCOPY NUMBER
51 ,x_msg_data OUT NOCOPY VARCHAR2);
52
53 -- Start of comments
54 -- API name : APIname
55 -- Type : Public or Group or Private.
56 -- Function :
57 -- Pre-reqs : None.
58 -- Parameters :
59 -- IN p_api_version IN NUMBER Required
60 -- p_init_msg_list IN VARCHAR2 Optional
61 -- Default = FND_API.G_FALSE
62 -- p_commit IN VARCHAR2 Optional
63 -- Default = FND_API.G_FALSE
64 -- p_validation_level IN NUMBER Optional
65 -- Default = FND_API.G_VALID_LEVEL_FULL
66 -- parameter1
67 -- parameter2
68 -- .
69 -- .
70 -- OUT x_return_status OUT VARCHAR2(1)
71 -- x_msg_count OUT NUMBER
72 -- x_msg_data OUT VARCHAR2(2000)
73 -- parameter1
74 -- parameter2
75 -- .
76 -- .
77 -- Version Current version x.x
78 -- Changed....
79 -- previous version y.y
80 -- Changed....
81 -- .
82 -- .
83 -- previous version 2.0
84 -- Changed....
85 -- Initial version 1.0
86 --
87 -- Notes : Note text
88 --
89 -- End of comments
90
91 PROCEDURE get_item_id(
92 p_api_version IN NUMBER
93 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
94 ,p_commit IN VARCHAR2 := fnd_api.g_false
95 ,p_validation_level IN NUMBER
96 := fnd_api.g_valid_level_full
97 ,p_organization_id IN NUMBER
98 ,p_concatenated_segments IN VARCHAR2
99 ,x_inventory_item_id OUT NOCOPY NUMBER
100 ,x_return_status OUT NOCOPY VARCHAR2
101 ,x_msg_count OUT NOCOPY NUMBER
102 ,x_msg_data OUT NOCOPY VARCHAR2);
103
104
105
106
107 PROCEDURE get_current_period(
108 p_api_version IN NUMBER
109 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
110 ,p_commit IN VARCHAR2 := fnd_api.g_false
111 ,p_validation_level IN NUMBER
112 := fnd_api.g_valid_level_full
113 ,p_organization_id IN NUMBER
114 ,x_period_name OUT NOCOPY VARCHAR2
115 ,x_return_status OUT NOCOPY VARCHAR2
116 ,x_msg_count OUT NOCOPY NUMBER
117 ,x_msg_data OUT NOCOPY VARCHAR2);
118
119
120 PROCEDURE get_currency(
121 p_api_version IN NUMBER
122 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
123 ,p_commit IN VARCHAR2 := fnd_api.g_false
124 ,p_validation_level IN NUMBER
125 := fnd_api.g_valid_level_full
126 ,p_organization_id IN NUMBER
127 ,x_currency OUT NOCOPY VARCHAR2
128 ,x_return_status OUT NOCOPY VARCHAR2
129 ,x_msg_count OUT NOCOPY NUMBER
130 ,x_msg_data OUT NOCOPY VARCHAR2);
131
132
133
134 -- Start of comments
135 -- API name : APIname
136 -- Type : Public or Group or Private.
137 -- Function :
138 -- Pre-reqs : None.
139 -- Parameters :
140 -- IN p_api_version IN NUMBER Required
141 -- p_init_msg_list IN VARCHAR2 Optional
142 -- Default = FND_API.G_FALSE
143 -- p_commit IN VARCHAR2 Optional
144 -- Default = FND_API.G_FALSE
145 -- p_validation_level IN NUMBER Optional
146 -- Default = FND_API.G_VALID_LEVEL_FULL
147 -- parameter1
148 -- parameter2
149 -- .
150 -- .
151 -- OUT x_return_status OUT VARCHAR2(1)
152 -- x_msg_count OUT NUMBER
153 -- x_msg_data OUT VARCHAR2(2000)
154 -- parameter1
155 -- parameter2
156 -- .
157 -- .
158 -- Version Current version x.x
159 -- Changed....
160 -- previous version y.y
161 -- Changed....
162 -- .
163 -- .
164 -- previous version 2.0
165 -- Changed....
166 -- Initial version 1.0
167 --
168 -- Notes : Note text
169 --
170 -- End of comments
171
172 PROCEDURE get_next_asset_number (
173 p_api_version IN NUMBER
174 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
175 ,p_commit IN VARCHAR2 := fnd_api.g_false
176 ,p_validation_level IN NUMBER
177 := fnd_api.g_valid_level_full
178 ,p_organization_id IN NUMBER
179 ,p_inventory_item_id IN NUMBER
180 ,x_asset_number OUT NOCOPY VARCHAR2
181 ,x_return_status OUT NOCOPY VARCHAR2
182 ,x_msg_count OUT NOCOPY NUMBER
183 ,x_msg_data OUT NOCOPY VARCHAR2);
184
185 PROCEDURE verify_org(
186 p_resp_id number default FND_GLOBAL.RESP_ID,
187 p_resp_app_id number default FND_GLOBAL.RESP_APPL_ID,
188 p_org_id number,
189 p_init_msg_list in VARCHAR2 := FND_API.G_FALSE,
190 x_boolean out NOCOPY number,
191 x_return_status out NOCOPY VARCHAR2,
192 x_msg_count out NOCOPY NUMBER,
193 x_msg_data out NOCOPY VARCHAR2);
194
195 FUNCTION invalid_item_name (p_item_name in varchar2)
196 return boolean;
197
198 FUNCTION get_mfg_meaning(p_lookup_type in VARCHAR2 , p_lookup_code in number)
199 return VARCHAR2 ;
200
201 FUNCTION get_item_name(p_service_request_id in number,
202 p_org_id in number,
203 p_inv_organization_id in number
204 ) return varchar2;
205
206
207 -- Following new functions added by lllin for 11.5.10
208
209 -- This function validates an asset group, asset activity, or
210 -- rebuildable item. p_eam_item_type indicates the type of item being
211 -- validated. Asset group: 1; Asset activity: 2; Rebuildable item: 3.
212 FUNCTION validate_inventory_item_id
213 (
214 p_organization_id in number,
215 p_inventory_item_id in number,
216 p_eam_item_type in number
217 ) return boolean;
218
219 -- This function validates an asset number or serialized rebuildable.
220 -- p_eam_item_type indicates the type of serial number being validated.
221 -- Asset group: 1; Asset activity: 2; Rebuildable item: 3.
222 FUNCTION validate_serial_number
223 (
224 p_organization_id in number,
225 p_inventory_item_id in number,
226 p_serial_number in varchar2,
227 p_eam_item_type in number:=1
228 ) return boolean;
229
230 -- This function validates the boolean flags.
231 -- A boolean flag has to be either 'Y' or 'N'.
232 FUNCTION validate_boolean_flag
233 (
234 p_flag in varchar2
235 ) return boolean;
236
237 -- Following function validates department id in bom_departments table.
238 FUNCTION validate_department_id
239 (
240 p_department_id in number,
241 p_organization_id in number
242
243 ) return boolean;
244
245 -- Validates eam location id in mtl_eam_locations table.
246 FUNCTION validate_eam_location_id
247 (
248 p_location_id in number
249 ) return boolean;
250
251 -- The following function should NOT be called for rebuilds
252 -- This function validates the eam location for an asset.
253 -- The location has to exist, and its organization_id has to
254 -- the same as the current_organization_id of the serial number.
255 FUNCTION validate_eam_location_id_asset
256 (
257 p_organization_id in number, -- use organization id, not creation org id
258 p_location_id in number
259 ) return boolean;
260
261 FUNCTION validate_wip_acct_class_code
262 (
263 p_organization_id in number,
264 p_wip_accounting_class_code in varchar2
265 ) return boolean;
266
267
268 -- This function validates a meter_id.
269 -- If p_tmpl_flag is null, then p_meter_id can be either template or instance
270 -- to be valid.
271 -- If p_tmpl_flag is 'Y', p_meter_id has to be a template.
272 -- If p_tmpl_flag is 'N', p_meter_id has to be an instance.
273 -- If p_tmpl_flag is not null, not 'Y', and not 'N', false is returned.
274 FUNCTION validate_meter_id
275 (
276 p_meter_id in number,
277 p_tmpl_flag in varchar2:=null
278 ) return boolean;
279
280 function validate_desc_flex_field
281 (
282 p_app_short_name IN VARCHAR:='EAM',
283 p_desc_flex_name IN VARCHAR,
284 p_ATTRIBUTE_CATEGORY IN VARCHAR2 default null,
285 p_ATTRIBUTE1 IN VARCHAR2 default null,
286 p_ATTRIBUTE2 IN VARCHAR2 default null,
287 p_ATTRIBUTE3 IN VARCHAR2 default null,
288 p_ATTRIBUTE4 IN VARCHAR2 default null,
289 p_ATTRIBUTE5 IN VARCHAR2 default null,
290 p_ATTRIBUTE6 IN VARCHAR2 default null,
291 p_ATTRIBUTE7 IN VARCHAR2 default null,
292 p_ATTRIBUTE8 IN VARCHAR2 default null,
293 p_ATTRIBUTE9 IN VARCHAR2 default null,
294 p_ATTRIBUTE10 IN VARCHAR2 default null,
295 p_ATTRIBUTE11 IN VARCHAR2 default null,
296 p_ATTRIBUTE12 IN VARCHAR2 default null,
297 p_ATTRIBUTE13 IN VARCHAR2 default null,
298 p_ATTRIBUTE14 IN VARCHAR2 default null,
299 p_ATTRIBUTE15 IN VARCHAR2 default null,
300 x_error_segments OUT NOCOPY NUMBER,
301 x_error_message OUT NOCOPY VARCHAR2
302 )
303 return boolean;
304
305 FUNCTION validate_mfg_lookup_code
306 (p_lookup_type in VARCHAR2,
307 p_lookup_code in NUMBER)
308 return boolean;
309
310 -- Validates that the maintained object type and id represent a valid
311 -- maintained object.
312 -- x_eam_item_type represents the eam_item_type of the object.
313
314 FUNCTION validate_maintained_object_id
315 (p_maintenance_object_type in NUMBER,
316 p_maintenance_object_id in NUMBER,
317 p_organization_id in NUMBER default null,
321
318 p_eam_item_type in NUMBER
319 )
320 return boolean;
322 -- Validates that the combination (Organization_id, inventory_item_id, and
323 -- serial number) and the combination (maintenance_object_type and
324 -- maintenance_object_id) represent the same valid maintained object.
325 -- x_eam_item_type represents the eam_item_type of the object.
326
327 FUNCTION validate_maintained_object
328 (p_organization_id in NUMBER,
329 p_inventory_item_id in NUMBER,
330 p_serial_number in VARCHAR2 default null,
331 p_maintenance_object_type in NUMBER,
332 p_maintenance_object_id in NUMBER,
333 p_eam_item_type in NUMBER)
334 return boolean;
335
336 -- Translates a combination of {organization_id, inventory_item_id, and
337 -- serial_number} into a combination of {maintenance_object_type and
338 -- maintenance_object_id}.
339 -- If the object is found, x_object_found is true, and vice-versa.
340 -- Serial_number can be null.
341
342 procedure translate_asset_maint_obj
343 (p_organization_id in number,
344 p_inventory_item_id in number,
345 p_serial_number in varchar2 default null,
346 x_object_found out nocopy boolean,
347 x_maintenance_object_type out nocopy number,
348 x_maintenance_object_id out nocopy number);
349
350
351 -- Translates a combination of {maintenance_object_type and
352 -- maintenance_object_id} into a combination of {organization_id,
353 -- inventory_item_id, and serial_number}.
354 -- If maintenance_object_type is 2, p_organization_id must be supplied.
355
356 procedure translate_maint_obj_asset
357 (p_maintenance_object_type in number,
358 p_maintenance_object_id in number,
359 p_organization_id in number default null,
360 x_object_found out nocopy boolean,
361 x_organization_id out nocopy number,
362 x_inventory_item_id out nocopy number,
363 x_serial_number out nocopy varchar2
364 );
365
366 /* ----------------------------------------------------------------------------------------------
367 -- Procedure to get the sum of today's work, overdue work, open work and unassigned work in Maintenance
368 -- Engineer's Workbench
369 -- Author : amondal, Aug '03
370 ------------------------------------------------------------------------------------------------*/
371
372 PROCEDURE get_work_order_count (
373 p_api_version IN NUMBER
374 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
375 ,p_commit IN VARCHAR2 := fnd_api.g_false
376 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
377 ,p_organization_id IN VARCHAR2
378 ,p_employee_id IN VARCHAR2
379 ,p_instance_id IN NUMBER
380 ,p_asset_group_id IN NUMBER
381 ,p_department_id IN NUMBER
382 ,p_resource_id IN NUMBER
383 ,p_current_date IN VARCHAR2
384 ,x_todays_work OUT NOCOPY VARCHAR2
385 ,x_overdue_work OUT NOCOPY VARCHAR2
386 ,x_open_work OUT NOCOPY VARCHAR2
387 ,x_todays_work_duration OUT NOCOPY VARCHAR2
388 ,x_overdue_work_duration OUT NOCOPY VARCHAR2
389 ,x_open_work_duration OUT NOCOPY VARCHAR2
390 ,x_current_date OUT NOCOPY VARCHAR2
391 ,x_current_time OUT NOCOPY VARCHAR2
392 ,x_return_status OUT NOCOPY VARCHAR2
393 ,x_msg_count OUT NOCOPY NUMBER
394 ,x_msg_data OUT NOCOPY VARCHAR2);
395
396 /* ----------------------------------------------------------------------------------------------
397 -- Procedure for inserting into WIP_OP_RESOURCE_INSTANCES in Maintenance
398 -- Engineer's Workbench
399 -- Author : amondal, Aug '03
400 ------------------------------------------------------------------------------------------------*/
401
402 PROCEDURE insert_into_wori (
403 p_api_version IN NUMBER
404 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
405 ,p_commit IN VARCHAR2 := fnd_api.g_false
406 ,p_organization_id IN VARCHAR2
407 ,p_employee_id IN VARCHAR2
408 ,p_wip_entity_id IN VARCHAR2
409 ,p_operation_seq_num IN VARCHAR2
410 ,p_resource_seq_num IN VARCHAR2
411 ,p_resource_id IN VARCHAR2
412 ,x_return_status OUT NOCOPY VARCHAR2
413 ,x_msg_count OUT NOCOPY NUMBER
414 ,x_msg_data OUT NOCOPY VARCHAR2
415 ,x_wip_entity_name OUT NOCOPY VARCHAR2);
416
417 FUNCTION get_person_id RETURN VARCHAR2;
418
419 function get_dept_id(p_org_code in varchar2, p_org_id in number, p_dept_code in varchar2, p_dept_id in number)
420 return number;
421
422 PROCEDURE deactivate_assets(
423 P_API_VERSION IN NUMBER,
424 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
425 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
429 P_ORGANIZATION_ID IN NUMBER,
426 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
427 P_INVENTORY_ITEM_ID IN NUMBER DEFAULT NULL,
428 P_SERIAL_NUMBER IN VARCHAR2 DEFAULT NULL,
430 P_GEN_OBJECT_ID IN NUMBER DEFAULT NULL,
431 P_INSTANCE_ID IN NUMBER DEFAULT NULL,
432 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
433 X_MSG_COUNT OUT NOCOPY NUMBER,
434 X_MSG_DATA OUT NOCOPY VARCHAR2);
435
436
437 procedure log_api_return(
438 p_module in varchar2 default 'eam.plsql.eam_common_utilities_pvt.log_api_return',
439 p_api in varchar2 default 'Unknown API',
440 p_return_status in varchar2 default null,
441 p_msg_count in number default null,
442 p_msg_data in varchar2 default null
443 );
444
445
446 FUNCTION get_onhand_quant(p_org_id in number, p_inventory_item_id in number)
447 RETURN number ;
448
449
450 /* Bug # 3698307
451 validate_linear_id is added for Linear Asset Management project
452 Basically it verify's whether the passed linear_id exists in EAM_LINEAR_LOCATIONS
453 table or not.
454 */
455
456 FUNCTION validate_linear_id(p_eam_linear_id IN NUMBER)
457 RETURN BOOLEAN;
458
459
460 --------------------------------------------------------------------------
461 -- PROCEDURE --
462 -- Create_Asset --
463 -- --
464 -- DESCRIPTION --
465 -- This API is used to create an IB instance whenever a work order is --
466 -- saved on a rebuild in predefined status. It will call the wrapper --
467 -- API that in turn calls the IB create_asset API --
468 -- It a) Create the IB instance b) Updates current status in MSN --
469 -- c) Instantiates the rebuild d) Updates the WO Record --
470 -- --
471 -- This API is invoked from the WO API. --
472 -- --
473 -- PURPOSE: --
474 -- Oracle Applications Rel 12 --
475 -- --
476 -- HISTORY: --
477 -- 05/20/05 Anju Gupta Created --
478 ----------------------------------------------------------------------------
479
480
481 PROCEDURE CREATE_ASSET(
482 P_API_VERSION IN NUMBER
483 ,P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE
484 ,P_COMMIT IN VARCHAR2 := FND_API.G_FALSE
485 ,P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL
486 ,X_EAM_WO_REC IN OUT NOCOPY EAM_PROCESS_WO_PUB.eam_wo_rec_type
487 ,X_RETURN_STATUS OUT NOCOPY VARCHAR2
488 ,X_MSG_COUNT OUT NOCOPY NUMBER
489 ,X_MSG_DATA OUT NOCOPY VARCHAR2
490 );
491
492 FUNCTION check_deactivate(
493 p_maintenance_object_id IN NUMBER, -- for Maintenance Object Type of 3, this should be Instance_Id
494 p_maintenance_object_type IN NUMBER -- Type 3 (Instance Id)
495
496 )
497 return boolean;
498
499 FUNCTION get_parent_asset(p_parent_job_id in number, p_organization_id in number)
500 return VARCHAR2 ;
501
502
503 PROCEDURE write_WORU (
504 P_WIP_ENTITY_ID IN NUMBER
505 ,P_ORGANIZATION_ID IN NUMBER
506 ,P_OPERATION_SEQ_NUM IN NUMBER
507 ,P_RESOURCE_SEQ_NUM IN NUMBER
508 ,P_UPDATE_HIERARCHY IN VARCHAR2
509 ,P_START IN DATE
510 ,P_END IN DATE
511 ,P_DELTA IN NUMBER
512 ,X_RETURN_STATUS OUT NOCOPY VARCHAR2
513 ,X_MSG_COUNT OUT NOCOPY NUMBER
514 ,X_MSG_DATA OUT NOCOPY VARCHAR2
515 );
516
517 PROCEDURE Adjust_WORU (
518 P_API_VERSION IN NUMBER
519 ,P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE
520 ,P_COMMIT IN VARCHAR2 := FND_API.G_FALSE
521 ,P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL
522 ,P_WIP_ENTITY_ID IN NUMBER
523 ,P_ORGANIZATION_ID IN NUMBER
524 ,P_OPERATION_SEQ_NUM IN NUMBER
525 ,P_RESOURCE_SEQ_NUM IN NUMBER
526 ,P_DELTA IN NUMBER
527 ,P_UPDATE_HIERARCHY IN VARCHAR2
528 ,X_RETURN_STATUS OUT NOCOPY VARCHAR2
529 ,X_MSG_COUNT OUT NOCOPY NUMBER
530 ,X_MSG_DATA OUT NOCOPY VARCHAR2
531 );
532
533 -- Function to fetch Asset area code for corresponding maintenance organization.
534 FUNCTION get_asset_area( p_instance_id NUMBER, p_maint_org_id NUMBER) RETURN VARCHAR2;
535
536
537 PROCEDURE set_profile(
538 name in varchar2,
539 value in varchar2
540 ) ;
541 Function is_active(
542 p_instance_id number
543 ) return varchar2;
544
548
545 -- Function to check if completion subinventory, locator and lot are required to be shown in SSWA Work Order Completion Page.
546 FUNCTION showCompletionFields( p_wip_entity_id NUMBER ) RETURN VARCHAR2;
547
549 PROCEDURE update_logical_asset(
550 p_inventory_item_id number
551 ,p_serial_number varchar2
552 ,p_equipment_gen_object_id number
553 ,p_network_asset_flag varchar2
554 ,p_pn_location_id number
555 ,x_return_status out nocopy varchar2
556 );
557
558 FUNCTION get_scheduled_start_date( p_wip_entity_id NUMBER ) RETURN DATE;
559
560 FUNCTION get_scheduled_completion_date( p_wip_entity_id NUMBER ) RETURN DATE;
561
562 END EAM_COMMON_UTILITIES_PVT;
563
[Home] [Help]