4 -- Procedure to validate Operation
1 PACKAGE AHL_RM_ROUTE_UTIL AUTHID CURRENT_USER AS
2 /* $Header: AHLVRUTS.pls 120.3.12020000.2 2012/12/07 14:28:12 sareepar ship $ */
3
5 PROCEDURE validate_operation
6 (
7 x_return_status OUT NOCOPY VARCHAR2,
8 x_msg_data OUT NOCOPY VARCHAR2,
9 p_concatenated_segments IN AHL_OPERATIONS_B_KFV.concatenated_segments%TYPE,
10 p_x_operation_id IN OUT NOCOPY AHL_OPERATIONS_B.operation_id%TYPE
11 );
12
13 -- Procedure to validate lookups
14 PROCEDURE validate_lookup
15 (
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_msg_data OUT NOCOPY VARCHAR2,
18 p_lookup_type IN FND_LOOKUPS.lookup_type%TYPE,
19 p_lookup_meaning IN FND_LOOKUPS.meaning%TYPE,
20 p_x_lookup_code IN OUT NOCOPY FND_LOOKUPS.lookup_code%TYPE
21 );
22
23 -- Procedure to validate Operator
24 PROCEDURE validate_operator
25 (
26 x_return_status OUT NOCOPY VARCHAR2,
27 x_msg_data OUT NOCOPY VARCHAR2,
28 p_operator_name IN HZ_PARTIES.party_name%TYPE,
29 p_x_operator_party_id IN OUT NOCOPY NUMBER
30 );
31
32 -- Procedure to validate Additional Disposition List Item
33 PROCEDURE validate_adt_item
34 (
38 p_x_inventory_item_id IN OUT NOCOPY MTL_SYSTEM_ITEMS.inventory_item_id%TYPE,
35 x_return_status OUT NOCOPY VARCHAR2,
36 x_msg_data OUT NOCOPY VARCHAR2,
37 p_item_number IN MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE,
39 p_x_inventory_org_id IN OUT NOCOPY MTL_SYSTEM_ITEMS.organization_id%TYPE
40 );
41
42 -- Procedure to validate Component Item
43 PROCEDURE validate_item
44 (
45 x_return_status OUT NOCOPY VARCHAR2,
46 x_msg_data OUT NOCOPY VARCHAR2,
47 p_item_number IN MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE,
48 p_x_inventory_item_id IN OUT NOCOPY MTL_SYSTEM_ITEMS.inventory_item_id%TYPE,
49 p_x_inventory_org_id IN OUT NOCOPY MTL_SYSTEM_ITEMS.organization_id%TYPE
50 );
51
52 -- Procedure to validate Service Item
53 PROCEDURE validate_service_item
54 (
55 x_return_status OUT NOCOPY VARCHAR2,
56 x_msg_data OUT NOCOPY VARCHAR2,
57 p_item_number IN MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE,
58 p_x_inventory_item_id IN OUT NOCOPY MTL_SYSTEM_ITEMS.inventory_item_id%TYPE,
59 p_x_inventory_org_id IN OUT NOCOPY MTL_SYSTEM_ITEMS.organization_id%TYPE
60 );
61
62 -- Procedure to validate effectivity Item
63 PROCEDURE validate_effectivity_item
64 (
65 x_return_status OUT NOCOPY VARCHAR2,
66 x_msg_data OUT NOCOPY VARCHAR2,
67 p_item_number IN MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE,
68 p_org_code IN MTL_PARAMETERS.ORGANIZATION_CODE%TYPE,
69 p_x_inventory_item_id IN OUT NOCOPY MTL_SYSTEM_ITEMS.inventory_item_id%TYPE,
70 p_x_inventory_org_id IN OUT NOCOPY MTL_SYSTEM_ITEMS.organization_id%TYPE
71 )
72 ;
73
74 -- Procedure to validate Accounting class
75 PROCEDURE validate_accounting_class
76 (
77 x_return_status OUT NOCOPY VARCHAR2,
78 x_msg_data OUT NOCOPY VARCHAR2,
79 p_accounting_class IN WIP_ACCOUNTING_CLASSES.description%TYPE,
80 p_x_accounting_class_code IN OUT NOCOPY WIP_ACCOUNTING_CLASSES.class_code%TYPE,
81 p_x_accounting_class_org_id IN OUT NOCOPY WIP_ACCOUNTING_CLASSES.organization_id%TYPE
82 );
83
84 -- Procedure to validate Task Template Group
85 PROCEDURE validate_task_template_group
86 (
87 x_return_status OUT NOCOPY VARCHAR2,
88 x_msg_data OUT NOCOPY VARCHAR2,
89 p_task_template_group IN JTF_TASK_TEMP_GROUPS_VL.template_group_name%TYPE,
90 p_x_task_template_group_id IN OUT NOCOPY JTF_TASK_TEMP_GROUPS_VL.task_template_group_id%TYPE
91 );
92
93 -- MANESING::Supplier Warranty, 25-Aug-2010
94 -- Procedure to validate Warranty Template Name
95 PROCEDURE validate_warr_template_name
96 (
97 x_return_status OUT NOCOPY VARCHAR2,
98 x_msg_data OUT NOCOPY VARCHAR2,
99 p_warranty_template_name IN AHL_WARRANTY_TEMPLATES_B.name%TYPE,
100 p_x_warranty_template_id IN OUT NOCOPY NUMBER
101 );
102
103 -- Procedure to validate QA Plan
104 PROCEDURE validate_qa_plan
105 (
106 x_return_status OUT NOCOPY VARCHAR2,
107 x_msg_data OUT NOCOPY VARCHAR2,
108 p_qa_plan IN QA_PLANS_VAL_V.name%TYPE,
109 p_x_qa_plan_id IN OUT NOCOPY QA_PLANS_VAL_V.plan_id%TYPE
110 );
111
112 -- Procedure to validate QA Inspection Type
113 PROCEDURE validate_qa_inspection_type
114 (
115 x_return_status OUT NOCOPY VARCHAR2,
116 x_msg_data OUT NOCOPY VARCHAR2,
117 p_qa_inspection_type_desc IN QA_CHAR_VALUE_LOOKUPS_V.description%TYPE,
118 p_x_qa_inspection_type IN OUT NOCOPY QA_CHAR_VALUE_LOOKUPS_V.short_code%TYPE
119 );
120
121 -- Procedure to valiadate the Item Group
122 PROCEDURE validate_item_group
123 (
124 x_return_status OUT NOCOPY VARCHAR2,
125 x_msg_data OUT NOCOPY VARCHAR2,
126 p_association_type IN VARCHAR2,
127 p_item_group_name IN AHL_ITEM_GROUPS_VL.name%TYPE,
128 p_x_item_group_id IN OUT NOCOPY AHL_ITEM_GROUPS_VL.item_group_id%TYPE
129 );
130
131
132 -- Procedure to valiadate the Position Path
133 PROCEDURE validate_item_comp
134 (
135 x_return_status OUT NOCOPY VARCHAR2,
136 x_msg_data OUT NOCOPY VARCHAR2,
137 p_x_item_comp_detail_id IN OUT NOCOPY NUMBER
138 );
139
140
141 -- Procedure to valiadate the Position Path
142 PROCEDURE validate_position_path
143 (
144 x_return_status OUT NOCOPY VARCHAR2,
145 x_msg_data OUT NOCOPY VARCHAR2,
146 p_position_path IN VARCHAR2,
147 p_x_position_path_id IN OUT NOCOPY NUMBER
148 );
149
150 PROCEDURE validate_master_configuration
151 (
152 x_return_status OUT NOCOPY VARCHAR2,
153 x_msg_data OUT NOCOPY VARCHAR2,
154 p_mc_name IN AHL_MC_HEADERS_V.NAME%TYPE,
155 p_x_mc_id IN OUT NOCOPY AHL_MC_HEADERS_V.MC_ID%TYPE,
156 p_mc_revision_number IN AHL_MC_HEADERS_V.REVISION%TYPE ,
157 p_x_mc_header_id IN OUT NOCOPY AHL_MC_HEADERS_V.MC_HEADER_ID%TYPE
158 )
159 ;
160
161 -- Procedure to validate UOM
162 PROCEDURE validate_uom
163 (
164 x_return_status OUT NOCOPY VARCHAR2,
165 x_msg_data OUT NOCOPY VARCHAR2,
166 p_uom IN MTL_UNITS_OF_MEASURE_VL.unit_of_measure%TYPE,
167 p_x_uom_code IN OUT NOCOPY MTL_UNITS_OF_MEASURE_VL.uom_code%TYPE
168 );
169
173 x_return_status OUT NOCOPY VARCHAR2,
170 -- Procedure to validate whether a UOM is valid for an Item / Item Group
171 PROCEDURE validate_item_uom
172 (
174 x_msg_data OUT NOCOPY VARCHAR2,
175 p_item_group_id IN AHL_ITEM_GROUPS_VL.item_group_id%TYPE,
176 p_inventory_item_id IN MTL_SYSTEM_ITEMS.inventory_item_id%TYPE,
177 p_inventory_org_id IN MTL_SYSTEM_ITEMS.organization_id%TYPE,
178 p_uom_code IN MTL_UNITS_OF_MEASURE_VL.uom_code%TYPE
179 );
180
181 -- Procedure to validate Product Type and Zone association
182 PROCEDURE validate_pt_zone
183 (
184 x_return_status OUT NOCOPY VARCHAR2,
185 x_msg_data OUT NOCOPY VARCHAR2,
186 p_product_type_code IN AHL_PRODTYPE_ZONES.product_type_code%TYPE,
187 p_zone_code IN AHL_PRODTYPE_ZONES.zone_code%TYPE
188 );
189
190 -- Procedure to validate Product Type, Zone and Sub Zone association
191 PROCEDURE validate_pt_zone_subzone
192 (
193 x_return_status OUT NOCOPY VARCHAR2,
194 x_msg_data OUT NOCOPY VARCHAR2,
195 p_product_type_code IN AHL_PRODTYPE_ZONES.product_type_code%TYPE,
196 p_zone_code IN AHL_PRODTYPE_ZONES.zone_code%TYPE,
197 p_sub_zone_code IN AHL_PRODTYPE_ZONES.sub_zone_code%TYPE
198 );
199
200 -- Procedure to validate MFG lookups
201 PROCEDURE validate_mfg_lookup
202 (
203 x_return_status OUT NOCOPY VARCHAR2,
204 x_msg_data OUT NOCOPY VARCHAR2,
205 p_lookup_type IN MFG_LOOKUPS.lookup_type%TYPE,
206 p_lookup_meaning IN MFG_LOOKUPS.meaning%TYPE,
207 p_x_lookup_code IN OUT NOCOPY MFG_LOOKUPS.lookup_code%TYPE
208 );
209
210 -- Procedure to validate ASO Resource
211 PROCEDURE validate_aso_resource
212 (
213 x_return_status OUT NOCOPY VARCHAR2,
214 x_msg_data OUT NOCOPY VARCHAR2,
215 p_aso_resource_name IN AHL_RESOURCES.name%TYPE,
216 p_x_aso_resource_id IN OUT NOCOPY AHL_RESOURCES.resource_id%TYPE
217 );
218
219 -- Procedure to validate ASO Resource
220 PROCEDURE validate_bom_resource
221 (
222 x_return_status OUT NOCOPY VARCHAR2,
223 x_msg_data OUT NOCOPY VARCHAR2,
224 p_bom_resource_code IN BOM_RESOURCES.resource_code%TYPE,
225 p_x_bom_resource_id IN OUT NOCOPY BOM_RESOURCES.resource_id%TYPE,
226 p_x_bom_org_id IN OUT NOCOPY BOM_RESOURCES.organization_id%TYPE
227 );
228
229 -- Procedure to validate BOM Resource department
230 --pdoki ER 7436910 Begin.
231 PROCEDURE validate_bom_res_dep
232 (
233 x_return_status OUT NOCOPY VARCHAR2,
234 x_msg_data OUT NOCOPY VARCHAR2,
235 p_bom_resource_id IN NUMBER,
236 p_bom_org_id IN BOM_DEPARTMENTS.organization_id%TYPE,
237 p_bom_department_name IN BOM_DEPARTMENTS.DESCRIPTION%TYPE,
238 p_x_bom_department_id IN OUT NOCOPY BOM_DEPARTMENTS.department_id%TYPE
239 );
240 --pdoki ER 7436910 End.
241
242 -- Procedure to validate Resource Costing - Activity
243 PROCEDURE validate_activity
244 (
245 x_return_status OUT NOCOPY VARCHAR2,
246 x_msg_data OUT NOCOPY VARCHAR2,
247 p_activity IN CST_ACTIVITIES.activity%TYPE,
248 p_x_activity_id IN OUT NOCOPY CST_ACTIVITIES.activity_id%TYPE
249 );
250
251 -- Procedure to validate Skill Type
252 PROCEDURE validate_skill_type
253 (
254 x_return_status OUT NOCOPY VARCHAR2,
255 x_msg_data OUT NOCOPY VARCHAR2,
256 p_business_group_id IN PER_COMPETENCES.business_group_id%TYPE,
257 p_skill_name IN PER_COMPETENCES.name%TYPE,
258 p_x_skill_competence_id IN OUT NOCOPY PER_COMPETENCES.competence_id%TYPE
259 );
260
261 -- Procedure to validate Skill Level
262 PROCEDURE validate_skill_level
263 (
264 x_return_status OUT NOCOPY VARCHAR2,
265 x_msg_data OUT NOCOPY VARCHAR2,
266 p_business_group_id IN PER_RATING_LEVELS.business_group_id%TYPE,
267 p_skill_competence_id IN PER_RATING_LEVELS.competence_id%TYPE,
268 p_skill_level_desc IN VARCHAR2,
269 p_x_rating_level_id IN OUT NOCOPY PER_RATING_LEVELS.rating_level_id%TYPE
270 );
271
272 -- Procedure to validate Qualification Type
273 PROCEDURE validate_qualification_type
274 (
275 x_return_status OUT NOCOPY VARCHAR2,
276 x_msg_data OUT NOCOPY VARCHAR2,
277 p_qualification_type IN PER_QUALIFICATION_TYPES.name%TYPE,
278 p_x_qualification_type_id IN OUT NOCOPY PER_QUALIFICATION_TYPES.qualification_type_id%TYPE
279 );
280
281 -- Procedure to validate whether the Route is in Updatable status
282 PROCEDURE validate_route_status
283 (
284 p_route_id IN NUMBER,
285 x_msg_data OUT NOCOPY VARCHAR2,
286 x_return_status OUT NOCOPY VARCHAR2
287 );
288
289 -- Procedure to validate whether the Operation is in Updatable status
290 PROCEDURE validate_operation_status
291 (
292 p_operation_id IN NUMBER,
293 x_msg_data OUT NOCOPY VARCHAR2,
294 x_return_status OUT NOCOPY VARCHAR2
295 );
296
297 -- Procedure to validate Effectivity of the Route
298 PROCEDURE validate_efct_status
299 (
300 p_efct_id IN NUMBER,
301 x_msg_data OUT NOCOPY VARCHAR2,
302 x_return_status OUT NOCOPY VARCHAR2
303 )
304 ;
305
306 -- Procedure to validate whether the Time Span of the Route is Greater than the Longest Resource Duration for the Same Route and all the Associated Operations
307 -- Bug # 8639648 - added route start date parameter for this bug fix.
308 PROCEDURE validate_route_time_span
309 (
310 p_route_id IN NUMBER,
311 p_time_span IN NUMBER,
312 p_rou_start_date IN DATE,
313 x_res_max_duration OUT NOCOPY NUMBER,
314 x_msg_data OUT NOCOPY VARCHAR2,
315 x_return_status OUT NOCOPY VARCHAR2
316 );
317
318 -- Procedure to validate whether the Duration specified for the Route / Operation Resource is longer than The Route Time Span.
319 PROCEDURE validate_resource_duration
320 (
321 p_object_id IN NUMBER,
322 p_association_type_code IN VARCHAR2,
323 p_duration IN NUMBER,
324 x_max_rt_time_span OUT NOCOPY NUMBER,
325 x_msg_data OUT NOCOPY VARCHAR2,
326 x_return_status OUT NOCOPY VARCHAR2
327 );
328
329 -- Procedure to validate whether the longest Duration specified for an operation Resource is longer than associated Route Time Span.
330 PROCEDURE validate_rt_op_res_duration
331 (
332 p_route_id IN NUMBER,
333 p_operation_id IN NUMBER,
334 x_rt_time_span OUT NOCOPY NUMBER,
335 x_op_max_res_duration OUT NOCOPY NUMBER,
336 x_msg_data OUT NOCOPY VARCHAR2,
337 x_return_status OUT NOCOPY VARCHAR2
338 );
339
340 -- Procedure to validate whether the route / operation Start date is valid.
341 PROCEDURE validate_rt_oper_start_date
342 (
343 p_object_id IN NUMBER,
344 p_association_type IN VARCHAR2,
345 p_start_date IN DATE,
346 x_start_date OUT NOCOPY DATE,
347 x_msg_data OUT NOCOPY VARCHAR2,
348 x_return_status OUT NOCOPY VARCHAR2
349 );
350
351 -- Procedure to validate whether the route has correct application usage code.
352 PROCEDURE validate_ApplnUsage
353 (
354 p_object_id IN NUMBER,
355 p_association_type IN VARCHAR2,
356 x_msg_data OUT NOCOPY VARCHAR2,
357 x_return_status OUT NOCOPY VARCHAR2
358 )
359 ;
360
361 FUNCTION get_position_meaning
362 (
363 p_position_path_id IN NUMBER,
364 p_item_comp_detail_id IN NUMBER
365 )
366 RETURN VARCHAR2
367 ;
368
369 FUNCTION get_source_composition
370 (
371 p_position_path_id IN NUMBER,
372 p_item_comp_detail_id IN NUMBER
373 )
374 RETURN VARCHAR2
375 ;
376
377 --Procedure to get Operation id out of Operation Number and Revision
378
379 -- Start of Comments
380 -- Procedure name : Operation_Number_To_Id
381 -- Type : Private
382 -- Pre-reqs :
383 -- Function :
384 -- Parameters :
385 --
386 -- Standard IN Parameters :
387 -- None
388 --
389 -- Standard OUT Parameters :
390 -- None
391 --
392 -- Operation_Number_To_Id IN parameters:
393 -- p_operation_number VARCHAR2 Required
394 -- p_operation_revision NUMBER Required
395 --
396 -- Operation_Number_To_Id IN OUT parameters:
397 -- None
398 --
399 -- Operation_Number_To_Id OUT parameters:
400 -- x_operation_id NUMBER Required
401 -- x_return_status VARCHAR2 Required
402 --
403 -- Version :
404 -- Current version 1.0
405 --
406 -- End of Comments
407
408 PROCEDURE Operation_Number_To_Id
409 (
410 p_operation_number IN VARCHAR2,
411 p_operation_revision IN NUMBER,
412 x_operation_id OUT NOCOPY NUMBER,
413 x_return_status OUT NOCOPY VARCHAR2
414 );
415
416 --Procedure to get Route id out of Route Number and Revision
417
418 -- Start of Comments
419 -- Procedure name : Route_Number_To_Id
420 -- Type : Private
421 -- Pre-reqs :
422 -- Function :
423 -- Parameters :
424 --
425 -- Standard IN Parameters :
426 -- None
427 --
428 -- Standard OUT Parameters :
429 -- None
430 --
431 -- Operation_Number_To_Id IN parameters:
432 -- p_route_number VARCHAR2 Required
433 -- p_route_revision NUMBER Required
434 --
435 -- Operation_Number_To_Id IN OUT parameters:
436 -- None
437 --
438 -- Operation_Number_To_Id OUT parameters:
439 -- x_route_id NUMBER Required
440 -- x_return_status VARCHAR2 Required
441 --
442 -- Version :
443 -- Current version 1.0
444 --
445 -- End of Comments
446
447 PROCEDURE Route_Number_To_Id
448 (
449 p_route_number IN VARCHAR2,
450 p_route_revision IN NUMBER,
451 x_route_id OUT NOCOPY NUMBER,
452 x_return_status OUT NOCOPY VARCHAR2
453 );
454 --sukhwsin: Proceddures for SB Effectivity Requirements starts
455 --Procedure to Validate Master Configuration
456 PROCEDURE Validate_Master_Config
457 (
458 x_return_status OUT NOCOPY VARCHAR2,
459 x_msg_data OUT NOCOPY VARCHAR2,
460 p_mc_name IN AHL_MC_HEADERS_VL.name%TYPE,
461 p_x_mc_id IN OUT NOCOPY AHL_MC_HEADERS_VL.mc_id%TYPE
462 );
463 --Procedure to Validate Master Conf Position
464 PROCEDURE Validate_Mc_Position
465 (
466 x_return_status OUT NOCOPY VARCHAR2,
467 x_msg_data OUT NOCOPY VARCHAR2,
468 p_mc_id IN AHL_MC_HEADERS_VL.mc_id%TYPE,
469 p_mc_position IN FND_LOOKUP_VALUES_VL.meaning%TYPE,
470 p_x_mc_position_key IN OUT NOCOPY AHL_MC_RELATIONSHIPS.position_key%TYPE
471 );
472 --sukhwsin: Proceddures for SB Effectivity Requirements ends
473
474 --Pekambar added new function for VEE- USAF
475 FUNCTION Split_token
476 (
477 p_input_string IN VARCHAR2, -- input string
478 p_token_number IN NUMBER, -- token number
479 p_delim IN VARCHAR2 DEFAULT ',' -- separator character
480 )
481 RETURN VARCHAR2;
482
483 END AHL_RM_ROUTE_UTIL;