1 PACKAGE AHL_RM_ROUTE_UTIL AS
2 /* $Header: AHLVRUTS.pls 120.0.12010000.2 2008/10/24 07:25:31 pdoki ship $ */
3
4 -- Procedure to validate Operation
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 (
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,
38 p_x_inventory_item_id IN OUT NOCOPY MTL_SYSTEM_ITEMS.inventory_item_id%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 -- Procedure to validate QA Plan
94 PROCEDURE validate_qa_plan
95 (
96 x_return_status OUT NOCOPY VARCHAR2,
97 x_msg_data OUT NOCOPY VARCHAR2,
98 p_qa_plan IN QA_PLANS_VAL_V.name%TYPE,
99 p_x_qa_plan_id IN OUT NOCOPY QA_PLANS_VAL_V.plan_id%TYPE
100 );
101
102 -- Procedure to validate QA Inspection Type
103 PROCEDURE validate_qa_inspection_type
104 (
105 x_return_status OUT NOCOPY VARCHAR2,
106 x_msg_data OUT NOCOPY VARCHAR2,
107 p_qa_inspection_type_desc IN QA_CHAR_VALUE_LOOKUPS_V.description%TYPE,
108 p_x_qa_inspection_type IN OUT NOCOPY QA_CHAR_VALUE_LOOKUPS_V.short_code%TYPE
109 );
110
111 -- Procedure to valiadate the Item Group
112 PROCEDURE validate_item_group
113 (
114 x_return_status OUT NOCOPY VARCHAR2,
115 x_msg_data OUT NOCOPY VARCHAR2,
116 p_association_type IN VARCHAR2,
117 p_item_group_name IN AHL_ITEM_GROUPS_VL.name%TYPE,
118 p_x_item_group_id IN OUT NOCOPY AHL_ITEM_GROUPS_VL.item_group_id%TYPE
119 );
120
121
122 -- Procedure to valiadate the Position Path
123 PROCEDURE validate_item_comp
124 (
125 x_return_status OUT NOCOPY VARCHAR2,
126 x_msg_data OUT NOCOPY VARCHAR2,
127 p_x_item_comp_detail_id IN OUT NOCOPY NUMBER
128 );
129
130
131 -- Procedure to valiadate the Position Path
132 PROCEDURE validate_position_path
133 (
134 x_return_status OUT NOCOPY VARCHAR2,
135 x_msg_data OUT NOCOPY VARCHAR2,
136 p_position_path IN VARCHAR2,
137 p_x_position_path_id IN OUT NOCOPY NUMBER
138 );
139
140 PROCEDURE validate_master_configuration
141 (
142 x_return_status OUT NOCOPY VARCHAR2,
143 x_msg_data OUT NOCOPY VARCHAR2,
144 p_mc_name IN AHL_MC_HEADERS_V.NAME%TYPE,
145 p_x_mc_id IN OUT NOCOPY AHL_MC_HEADERS_V.MC_ID%TYPE,
146 p_mc_revision_number IN AHL_MC_HEADERS_V.REVISION%TYPE ,
147 p_x_mc_header_id IN OUT NOCOPY AHL_MC_HEADERS_V.MC_HEADER_ID%TYPE
148 )
149 ;
150
151 -- Procedure to validate UOM
152 PROCEDURE validate_uom
153 (
154 x_return_status OUT NOCOPY VARCHAR2,
155 x_msg_data OUT NOCOPY VARCHAR2,
156 p_uom IN MTL_UNITS_OF_MEASURE_VL.unit_of_measure%TYPE,
157 p_x_uom_code IN OUT NOCOPY MTL_UNITS_OF_MEASURE_VL.uom_code%TYPE
158 );
159
160 -- Procedure to validate whether a UOM is valid for an Item / Item Group
161 PROCEDURE validate_item_uom
162 (
163 x_return_status OUT NOCOPY VARCHAR2,
164 x_msg_data OUT NOCOPY VARCHAR2,
165 p_item_group_id IN AHL_ITEM_GROUPS_VL.item_group_id%TYPE,
166 p_inventory_item_id IN MTL_SYSTEM_ITEMS.inventory_item_id%TYPE,
167 p_inventory_org_id IN MTL_SYSTEM_ITEMS.organization_id%TYPE,
168 p_uom_code IN MTL_UNITS_OF_MEASURE_VL.uom_code%TYPE
169 );
170
171 -- Procedure to validate Product Type and Zone association
172 PROCEDURE validate_pt_zone
173 (
174 x_return_status OUT NOCOPY VARCHAR2,
175 x_msg_data OUT NOCOPY VARCHAR2,
176 p_product_type_code IN AHL_PRODTYPE_ZONES.product_type_code%TYPE,
177 p_zone_code IN AHL_PRODTYPE_ZONES.zone_code%TYPE
178 );
179
180 -- Procedure to validate Product Type, Zone and Sub Zone association
181 PROCEDURE validate_pt_zone_subzone
182 (
183 x_return_status OUT NOCOPY VARCHAR2,
184 x_msg_data OUT NOCOPY VARCHAR2,
185 p_product_type_code IN AHL_PRODTYPE_ZONES.product_type_code%TYPE,
186 p_zone_code IN AHL_PRODTYPE_ZONES.zone_code%TYPE,
187 p_sub_zone_code IN AHL_PRODTYPE_ZONES.sub_zone_code%TYPE
188 );
189
190 -- Procedure to validate MFG lookups
191 PROCEDURE validate_mfg_lookup
192 (
193 x_return_status OUT NOCOPY VARCHAR2,
194 x_msg_data OUT NOCOPY VARCHAR2,
195 p_lookup_type IN MFG_LOOKUPS.lookup_type%TYPE,
196 p_lookup_meaning IN MFG_LOOKUPS.meaning%TYPE,
197 p_x_lookup_code IN OUT NOCOPY MFG_LOOKUPS.lookup_code%TYPE
198 );
199
200 -- Procedure to validate ASO Resource
201 PROCEDURE validate_aso_resource
202 (
203 x_return_status OUT NOCOPY VARCHAR2,
204 x_msg_data OUT NOCOPY VARCHAR2,
205 p_aso_resource_name IN AHL_RESOURCES.name%TYPE,
206 p_x_aso_resource_id IN OUT NOCOPY AHL_RESOURCES.resource_id%TYPE
207 );
208
209 -- Procedure to validate ASO Resource
210 PROCEDURE validate_bom_resource
211 (
212 x_return_status OUT NOCOPY VARCHAR2,
213 x_msg_data OUT NOCOPY VARCHAR2,
214 p_bom_resource_code IN BOM_RESOURCES.resource_code%TYPE,
215 p_x_bom_resource_id IN OUT NOCOPY BOM_RESOURCES.resource_id%TYPE,
216 p_x_bom_org_id IN OUT NOCOPY BOM_RESOURCES.organization_id%TYPE
217 );
218
219 -- Procedure to validate BOM Resource department
220 --pdoki ER 7436910 Begin.
221 PROCEDURE validate_bom_res_dep
222 (
223 x_return_status OUT NOCOPY VARCHAR2,
224 x_msg_data OUT NOCOPY VARCHAR2,
225 p_bom_resource_id IN NUMBER,
226 p_bom_org_id IN BOM_DEPARTMENTS.organization_id%TYPE,
227 p_bom_department_name IN BOM_DEPARTMENTS.DESCRIPTION%TYPE,
228 p_x_bom_department_id IN OUT NOCOPY BOM_DEPARTMENTS.department_id%TYPE
229 );
230 --pdoki ER 7436910 End.
231
232 -- Procedure to validate Resource Costing - Activity
233 PROCEDURE validate_activity
234 (
235 x_return_status OUT NOCOPY VARCHAR2,
236 x_msg_data OUT NOCOPY VARCHAR2,
237 p_activity IN CST_ACTIVITIES.activity%TYPE,
238 p_x_activity_id IN OUT NOCOPY CST_ACTIVITIES.activity_id%TYPE
239 );
240
241 -- Procedure to validate Skill Type
242 PROCEDURE validate_skill_type
243 (
244 x_return_status OUT NOCOPY VARCHAR2,
245 x_msg_data OUT NOCOPY VARCHAR2,
246 p_business_group_id IN PER_COMPETENCES.business_group_id%TYPE,
247 p_skill_name IN PER_COMPETENCES.name%TYPE,
248 p_x_skill_competence_id IN OUT NOCOPY PER_COMPETENCES.competence_id%TYPE
249 );
250
251 -- Procedure to validate Skill Level
252 PROCEDURE validate_skill_level
253 (
254 x_return_status OUT NOCOPY VARCHAR2,
255 x_msg_data OUT NOCOPY VARCHAR2,
256 p_business_group_id IN PER_RATING_LEVELS.business_group_id%TYPE,
257 p_skill_competence_id IN PER_RATING_LEVELS.competence_id%TYPE,
258 p_skill_level_desc IN VARCHAR2,
259 p_x_rating_level_id IN OUT NOCOPY PER_RATING_LEVELS.rating_level_id%TYPE
260 );
261
262 -- Procedure to validate Qualification Type
263 PROCEDURE validate_qualification_type
264 (
265 x_return_status OUT NOCOPY VARCHAR2,
266 x_msg_data OUT NOCOPY VARCHAR2,
267 p_qualification_type IN PER_QUALIFICATION_TYPES.name%TYPE,
268 p_x_qualification_type_id IN OUT NOCOPY PER_QUALIFICATION_TYPES.qualification_type_id%TYPE
269 );
270
271 -- Procedure to validate whether the Route is in Updatable status
272 PROCEDURE validate_route_status
273 (
274 p_route_id IN NUMBER,
275 x_msg_data OUT NOCOPY VARCHAR2,
276 x_return_status OUT NOCOPY VARCHAR2
277 );
278
279 -- Procedure to validate whether the Operation is in Updatable status
280 PROCEDURE validate_operation_status
281 (
282 p_operation_id IN NUMBER,
283 x_msg_data OUT NOCOPY VARCHAR2,
284 x_return_status OUT NOCOPY VARCHAR2
285 );
286
287 -- Procedure to validate Effectivity of the Route
288 PROCEDURE validate_efct_status
289 (
290 p_efct_id IN NUMBER,
291 x_msg_data OUT NOCOPY VARCHAR2,
292 x_return_status OUT NOCOPY VARCHAR2
293 )
294 ;
295
296 -- 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
297 PROCEDURE validate_route_time_span
298 (
299 p_route_id IN NUMBER,
300 p_time_span IN NUMBER,
301 x_res_max_duration OUT NOCOPY NUMBER,
302 x_msg_data OUT NOCOPY VARCHAR2,
303 x_return_status OUT NOCOPY VARCHAR2
304 );
305
306 -- Procedure to validate whether the Duration specified for the Route / Operation Resource is longer than The Route Time Span.
307 PROCEDURE validate_resource_duration
308 (
309 p_object_id IN NUMBER,
310 p_association_type_code IN VARCHAR2,
311 p_duration IN NUMBER,
312 x_max_rt_time_span OUT NOCOPY NUMBER,
313 x_msg_data OUT NOCOPY VARCHAR2,
314 x_return_status OUT NOCOPY VARCHAR2
315 );
316
317 -- Procedure to validate whether the longest Duration specified for an operation Resource is longer than associated Route Time Span.
318 PROCEDURE validate_rt_op_res_duration
319 (
320 p_route_id IN NUMBER,
321 p_operation_id IN NUMBER,
322 x_rt_time_span OUT NOCOPY NUMBER,
323 x_op_max_res_duration OUT NOCOPY NUMBER,
324 x_msg_data OUT NOCOPY VARCHAR2,
325 x_return_status OUT NOCOPY VARCHAR2
326 );
327
328 -- Procedure to validate whether the route / operation Start date is valid.
329 PROCEDURE validate_rt_oper_start_date
330 (
331 p_object_id IN NUMBER,
332 p_association_type IN VARCHAR2,
333 p_start_date IN DATE,
334 x_start_date OUT NOCOPY DATE,
335 x_msg_data OUT NOCOPY VARCHAR2,
336 x_return_status OUT NOCOPY VARCHAR2
337 );
338
339 -- Procedure to validate whether the route has correct application usage code.
340 PROCEDURE validate_ApplnUsage
341 (
342 p_object_id IN NUMBER,
343 p_association_type IN VARCHAR2,
344 x_msg_data OUT NOCOPY VARCHAR2,
345 x_return_status OUT NOCOPY VARCHAR2
346 )
347 ;
348
349 FUNCTION get_position_meaning
350 (
351 p_position_path_id IN NUMBER,
352 p_item_comp_detail_id IN NUMBER
353 )
354 RETURN VARCHAR2
355 ;
356
357 FUNCTION get_source_composition
358 (
359 p_position_path_id IN NUMBER,
360 p_item_comp_detail_id IN NUMBER
361 )
362 RETURN VARCHAR2
363 ;
364
365 --Procedure to get Operation id out of Operation Number and Revision
366
367 -- Start of Comments
368 -- Procedure name : Operation_Number_To_Id
369 -- Type : Private
370 -- Pre-reqs :
371 -- Function :
372 -- Parameters :
373 --
374 -- Standard IN Parameters :
375 -- None
376 --
377 -- Standard OUT Parameters :
378 -- None
379 --
380 -- Operation_Number_To_Id IN parameters:
381 -- p_operation_number VARCHAR2 Required
382 -- p_operation_revision NUMBER Required
383 --
384 -- Operation_Number_To_Id IN OUT parameters:
385 -- None
386 --
387 -- Operation_Number_To_Id OUT parameters:
388 -- x_operation_id NUMBER Required
389 -- x_return_status VARCHAR2 Required
390 --
391 -- Version :
392 -- Current version 1.0
393 --
394 -- End of Comments
395
396 PROCEDURE Operation_Number_To_Id
397 (
398 p_operation_number IN VARCHAR2,
399 p_operation_revision IN NUMBER,
400 x_operation_id OUT NOCOPY NUMBER,
401 x_return_status OUT NOCOPY VARCHAR2
402 );
403
404 --Procedure to get Route id out of Route Number and Revision
405
406 -- Start of Comments
407 -- Procedure name : Route_Number_To_Id
408 -- Type : Private
409 -- Pre-reqs :
410 -- Function :
411 -- Parameters :
412 --
413 -- Standard IN Parameters :
414 -- None
415 --
416 -- Standard OUT Parameters :
417 -- None
418 --
419 -- Operation_Number_To_Id IN parameters:
420 -- p_route_number VARCHAR2 Required
421 -- p_route_revision NUMBER Required
422 --
423 -- Operation_Number_To_Id IN OUT parameters:
424 -- None
425 --
426 -- Operation_Number_To_Id OUT parameters:
427 -- x_route_id NUMBER Required
428 -- x_return_status VARCHAR2 Required
429 --
430 -- Version :
431 -- Current version 1.0
432 --
433 -- End of Comments
434
435 PROCEDURE Route_Number_To_Id
436 (
437 p_route_number IN VARCHAR2,
438 p_route_revision IN NUMBER,
439 x_route_id OUT NOCOPY NUMBER,
440 x_return_status OUT NOCOPY VARCHAR2
441 );
442
443 END AHL_RM_ROUTE_UTIL;