[Home] [Help]
PACKAGE BODY: APPS.AHL_RM_ROUTE_UTIL
Source
1 PACKAGE BODY AHL_RM_ROUTE_UTIL AS
2 /* $Header: AHLVRUTB.pls 120.7.12010000.3 2008/12/29 14:22:48 bachandr ship $ */
3
4 G_PKG_NAME VARCHAR2(30) := 'AHL_RM_ROUTE_UTIL';
5
6 -- Procedure to validate Operation
7 PROCEDURE validate_operation
8 (
9 x_return_status OUT NOCOPY VARCHAR2,
10 x_msg_data OUT NOCOPY VARCHAR2,
11 p_concatenated_segments IN AHL_OPERATIONS_B_KFV.concatenated_segments%TYPE,
12 p_x_operation_id IN OUT NOCOPY AHL_OPERATIONS_B.operation_id%TYPE
13 )
14 IS
15
16 l_operation_id AHL_OPERATIONS_B.operation_id%TYPE;
17
18 CURSOR get_rec_from_value ( c_concatenated_segments AHL_OPERATIONS_B_KFV.concatenated_segments%TYPE )
19 IS
20 SELECT DISTINCT operation_id
21 FROM AHL_OPERATIONS_B_KFV
22 WHERE concatenated_segments = c_concatenated_segments;
23
24 CURSOR get_rec_from_id ( c_operation_id AHL_OPERATIONS_B.operation_id%TYPE )
25 IS
26 SELECT DISTINCT operation_id
27 FROM AHL_OPERATIONS_B
28 WHERE operation_id = c_operation_id;
29
30 BEGIN
31 x_return_status := FND_API.G_RET_STS_SUCCESS;
32
33 IF ( ( p_concatenated_segments IS NULL OR
34 p_concatenated_segments = FND_API.G_MISS_CHAR ) AND
35 ( p_x_operation_id IS NULL OR
36 p_x_operation_id = FND_API.G_MISS_NUM ) ) THEN
37 x_return_status := FND_API.G_RET_STS_ERROR;
38 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
39 RETURN;
40 END IF;
41
42 IF ( ( p_concatenated_segments IS NULL OR
43 p_concatenated_segments = FND_API.G_MISS_CHAR ) AND
44 ( p_x_operation_id IS NOT NULL AND
45 p_x_operation_id <> FND_API.G_MISS_NUM ) ) THEN
46
47 OPEN get_rec_from_id( p_x_operation_id );
48
49 FETCH get_rec_from_id INTO
50 l_operation_id;
51
52 IF get_rec_from_id%NOTFOUND THEN
53 x_return_status := FND_API.G_RET_STS_ERROR;
54 x_msg_data := 'AHL_RM_INVALID_OPERATON';
55 END IF;
56
57 CLOSE get_rec_from_id;
58 RETURN;
59
60 END IF;
61
62 IF ( p_concatenated_segments IS NOT NULL AND
63 p_concatenated_segments <> FND_API.G_MISS_CHAR ) THEN
64
65 OPEN get_rec_from_value( p_concatenated_segments );
66
67 LOOP
68 FETCH get_rec_from_value INTO
69 l_operation_id;
70
71 EXIT WHEN get_rec_from_value%NOTFOUND;
72
73 IF ( l_operation_id = p_x_operation_id ) THEN
74 CLOSE get_rec_from_value;
75 RETURN;
76 END IF;
77
78 END LOOP;
79
80 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
81 x_return_status := FND_API.G_RET_STS_ERROR;
82 x_msg_data := 'AHL_RM_INVALID_OPERATION';
83 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
84 p_x_operation_id := l_operation_id;
85 ELSE
86 x_return_status := FND_API.G_RET_STS_ERROR;
87 x_msg_data := 'AHL_RM_TOO_MANY_OPERATIONS';
88 END IF;
89
90 CLOSE get_rec_from_value;
91 RETURN;
92
93 END IF;
94
95 END validate_operation;
96
97 -- Procedure to validate lookups
98 PROCEDURE validate_lookup
99 (
100 x_return_status OUT NOCOPY VARCHAR2,
101 x_msg_data OUT NOCOPY VARCHAR2,
102 p_lookup_type IN FND_LOOKUPS.lookup_type%TYPE,
103 p_lookup_meaning IN FND_LOOKUPS.meaning%TYPE,
104 p_x_lookup_code IN OUT NOCOPY FND_LOOKUPS.lookup_code%TYPE
105 )
106 IS
107
108 l_lookup_code FND_LOOKUPS.lookup_code%TYPE;
109
110 CURSOR get_rec_from_value ( c_lookup_type FND_LOOKUPS.lookup_type%TYPE,
111 c_lookup_meaning FND_LOOKUPS.meaning%TYPE )
112 IS
113 SELECT DISTINCT lookup_code
114 FROM FND_LOOKUP_VALUES_VL
115 WHERE lookup_type = c_lookup_type
116 AND meaning = c_lookup_meaning
117 AND SYSDATE BETWEEN NVL( start_date_active, SYSDATE ) AND
118 NVL( end_date_active, SYSDATE );
119
120 CURSOR get_rec_from_id ( c_lookup_type FND_LOOKUPS.lookup_type%TYPE,
121 c_lookup_code FND_LOOKUPS.lookup_code%TYPE )
122 IS
123 SELECT DISTINCT lookup_code
124 FROM FND_LOOKUP_VALUES_VL
125 WHERE lookup_type = c_lookup_type
126 AND lookup_code = c_lookup_code
127 AND SYSDATE BETWEEN NVL( start_date_active, SYSDATE ) AND
128 NVL( end_date_active, SYSDATE );
129
130 BEGIN
131 x_return_status := FND_API.G_RET_STS_SUCCESS;
132
133 IF ( p_lookup_type IS NULL OR
134 p_lookup_type = FND_API.G_MISS_CHAR ) THEN
135 x_return_status := FND_API.G_RET_STS_ERROR;
136 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
137 RETURN;
138 END IF;
139
140 IF ( ( p_lookup_meaning IS NULL OR
141 p_lookup_meaning = FND_API.G_MISS_CHAR ) AND
142 ( p_x_lookup_code IS NULL OR
143 p_x_lookup_code = FND_API.G_MISS_CHAR ) ) THEN
144 x_return_status := FND_API.G_RET_STS_ERROR;
145 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
146 RETURN;
147 END IF;
148
149 IF ( ( p_lookup_meaning IS NULL OR
150 p_lookup_meaning = FND_API.G_MISS_CHAR ) AND
151 ( p_x_lookup_code IS NOT NULL AND
152 p_x_lookup_code <> FND_API.G_MISS_CHAR ) ) THEN
153
154 OPEN get_rec_from_id( p_lookup_type, p_x_lookup_code );
155
156 FETCH get_rec_from_id INTO
157 l_lookup_code;
158
159 IF get_rec_from_id%NOTFOUND THEN
160 x_return_status := FND_API.G_RET_STS_ERROR;
161 x_msg_data := 'AHL_COM_INVALID_LOOKUP';
162 END IF;
163
164 CLOSE get_rec_from_id;
165 RETURN;
166
167 END IF;
168
169 IF ( p_lookup_meaning IS NOT NULL AND
170 p_lookup_meaning <> FND_API.G_MISS_CHAR ) THEN
171
172 OPEN get_rec_from_value( p_lookup_type, p_lookup_meaning );
173
174 LOOP
175 FETCH get_rec_from_value INTO
176 l_lookup_code;
177
178 EXIT WHEN get_rec_from_value%NOTFOUND;
179
180 IF ( l_lookup_code = p_x_lookup_code ) THEN
181 CLOSE get_rec_from_value;
182 RETURN;
183 END IF;
184
185 END LOOP;
186
187 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
188 x_return_status := FND_API.G_RET_STS_ERROR;
189 x_msg_data := 'AHL_COM_INVALID_LOOKUP';
190 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
191 p_x_lookup_code := l_lookup_code;
192 ELSE
193 x_return_status := FND_API.G_RET_STS_ERROR;
194 x_msg_data := 'AHL_COM_TOO_MANY_LOOKUPS';
195 END IF;
196
197 CLOSE get_rec_from_value;
198 RETURN;
199
200 END IF;
201
202 END validate_lookup;
203
204 -- Procedure to validate Operator
205 PROCEDURE validate_operator
206 (
207 x_return_status OUT NOCOPY VARCHAR2,
208 x_msg_data OUT NOCOPY VARCHAR2,
209 p_operator_name IN HZ_PARTIES.party_name%TYPE,
210 p_x_operator_party_id IN OUT NOCOPY NUMBER
211 )
212 IS
213
214 l_operator_party_id HZ_PARTIES.party_id%TYPE;
215
216 CURSOR get_rec_from_value ( c_operator_name HZ_PARTIES.party_name%TYPE )
217 IS
218 SELECT DISTINCT party_id
219 FROM HZ_PARTIES
220 WHERE party_name = c_operator_name;
221
222 CURSOR get_rec_from_id ( c_operator_party_id NUMBER )
223 IS
224 SELECT DISTINCT party_id
225 FROM HZ_PARTIES
226 WHERE party_id = c_operator_party_id;
227
228 BEGIN
229 x_return_status := FND_API.G_RET_STS_SUCCESS;
230
231 IF ( ( p_operator_name IS NULL OR
232 p_operator_name = FND_API.G_MISS_CHAR ) AND
233 ( p_x_operator_party_id IS NULL OR
234 p_x_operator_party_id = FND_API.G_MISS_NUM ) ) THEN
235 x_return_status := FND_API.G_RET_STS_ERROR;
236 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
237 RETURN;
238 END IF;
239
240 IF ( ( p_operator_name IS NULL OR
241 p_operator_name = FND_API.G_MISS_CHAR ) AND
242 ( p_x_operator_party_id IS NOT NULL AND
243 p_x_operator_party_id <> FND_API.G_MISS_NUM ) ) THEN
244
245 OPEN get_rec_from_id( p_x_operator_party_id );
246
247 FETCH get_rec_from_id INTO
248 l_operator_party_id;
249
250 IF get_rec_from_id%NOTFOUND THEN
251 x_return_status := FND_API.G_RET_STS_ERROR;
252 x_msg_data := 'AHL_RM_INVALID_OPERATOR';
253 END IF;
254
255 CLOSE get_rec_from_id;
256 RETURN;
257
258 END IF;
259
260 IF ( p_operator_name IS NOT NULL AND
261 p_operator_name <> FND_API.G_MISS_CHAR ) THEN
262
263 OPEN get_rec_from_value( p_operator_name );
264
265 LOOP
266 FETCH get_rec_from_value INTO
267 l_operator_party_id;
268
269 EXIT WHEN get_rec_from_value%NOTFOUND;
270
271 IF ( l_operator_party_id = p_x_operator_party_id ) THEN
272 CLOSE get_rec_from_value;
273 RETURN;
274 END IF;
275
276 END LOOP;
277
278 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
279 x_return_status := FND_API.G_RET_STS_ERROR;
280 x_msg_data := 'AHL_RM_INVALID_OPERATOR';
281 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
282 p_x_operator_party_id := l_operator_party_id;
283 ELSE
284 x_return_status := FND_API.G_RET_STS_ERROR;
285 x_msg_data := 'AHL_RM_TOO_MANY_OPERATORS';
286 END IF;
287
288 CLOSE get_rec_from_value;
289 RETURN;
290
291 END IF;
292
293 END validate_operator;
294
295 -- Procedure to validate Item
296 PROCEDURE validate_adt_item
297 (
298 x_return_status OUT NOCOPY VARCHAR2,
299 x_msg_data OUT NOCOPY VARCHAR2,
300 p_item_number IN MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE,
301 p_x_inventory_item_id IN OUT NOCOPY MTL_SYSTEM_ITEMS.inventory_item_id%TYPE,
302 p_x_inventory_org_id IN OUT NOCOPY MTL_SYSTEM_ITEMS.organization_id%TYPE
303 )
304 IS
305
306 l_inventory_item_flag MTL_SYSTEM_ITEMS.inventory_item_flag%TYPE;
307 l_inventory_item_id MTL_SYSTEM_ITEMS.inventory_item_id%TYPE;
308 l_inventory_org_id MTL_SYSTEM_ITEMS.organization_id%TYPE;
309
310 CURSOR get_rec_from_value ( c_item_number MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE ,
311 c_inventory_org_id MTL_SYSTEM_ITEMS.organization_id%TYPE)
312 IS
313 --AMSRINIV. Bug 4913429. Doing away with the use of 'upper' to tune below query.
314 SELECT
315 mtl.INVENTORY_ITEM_ID ,
316 mtl.organization_id ,
317 mtl.inventory_item_flag
318 from
319 AHL_MTL_ITEMS_EAM_V mtl
320 where
321 MTL.inventory_item_flag = 'Y'
322 AND mtl.enabled_flag = 'Y'
323 and mtl.concatenated_segments like c_item_number
324 and mtl.organization_id = c_inventory_org_id
325 order by 1;
326 /*
327 SELECT DISTINCT MI.inventory_item_id,
328 MI.organization_id,
329 NVL(MI.inventory_item_flag,'X')
330 FROM MTL_PARAMETERS MP, MTL_SYSTEM_ITEMS_KFV MI
331 WHERE MP.organization_id = MI.organization_id
332 AND MI.concatenated_segments = c_item_number
333 AND MI.organization_id = c_inventory_org_id
334 AND MI.enabled_flag = 'Y'
335 AND SYSDATE BETWEEN NVL( MI.start_date_active, SYSDATE )
336 AND NVL( MI.end_date_active, SYSDATE )
337 AND DECODE(AHL_UTIL_PKG.IS_PM_INSTALLED,'Y','Y',MP.eam_enabled_flag )='Y';
338 *//*
339 SELECT DISTINCT
340 mtl.INVENTORY_ITEM_ID ,
341 mtl.organization_id ,
342 NVL(mtl.inventory_item_flag,'X')
343 from
344 MTL_SYSTEM_ITEMS_KFV MTL
345 , fnd_lookup_values_vl IT
346 , MTL_PARAMETERS MP
347 where
348 DECODE(AHL_UTIL_PKG.IS_PM_INSTALLED,'Y','Y',mp.eam_enabled_flag )='Y'
349 AND MTL.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
350 and MTL.inventory_item_flag = 'Y'
351 AND IT.lookup_code (+) = MTL.item_type
352 AND IT.lookup_type (+) = 'ITEM_TYPE'
353 AND mtl.enabled_flag = 'Y'
354 AND SYSDATE BETWEEN NVL( mtl.start_date_active, SYSDATE )
355 AND NVL( mtl.end_date_active, SYSDATE )
356 and upper(mtl.concatenated_segments) like upper(c_item_number)
357 and mtl.organization_id = c_inventory_org_id
358 order by 1;
359 */
360 CURSOR get_rec_from_id ( c_inventory_item_id MTL_SYSTEM_ITEMS.inventory_item_id%TYPE,
361 c_inventory_org_id MTL_SYSTEM_ITEMS.organization_id%TYPE )
362 IS
363 SELECT
364 mtl.INVENTORY_ITEM_ID ,
365 mtl.organization_id ,
366 mtl.inventory_item_flag
367 from AHL_MTL_ITEMS_EAM_V mtl
368 where
369 MTL.inventory_item_flag = 'Y'
370 AND mtl.enabled_flag = 'Y'
371 and mtl.inventory_item_id = c_inventory_item_id
372 and mtl.organization_id = c_inventory_org_id
373 order by 1;
374 /*
375 SELECT DISTINCT MI.inventory_item_id,
376 MI.organization_id,
377 NVL(MI.inventory_item_flag,'X')
378 FROM MTL_PARAMETERS MP, MTL_SYSTEM_ITEMS_KFV MI
379 WHERE DECODE(AHL_UTIL_PKG.IS_PM_INSTALLED,'Y','Y',MP.eam_enabled_flag )='Y'
380 AND MP.organization_id = MI.organization_id
381 AND MI.inventory_item_id = c_inventory_item_id
382 AND MI.organization_id = c_inventory_org_id
383 AND MI.enabled_flag = 'Y'
384 AND SYSDATE BETWEEN NVL( MI.start_date_active, SYSDATE )
385 AND NVL( MI.end_date_active, SYSDATE );
386 *//*
387 SELECT DISTINCT
388 mtl.INVENTORY_ITEM_ID ,
389 mtl.organization_id ,
390 NVL(mtl.inventory_item_flag,'X')
391 from MTL_SYSTEM_ITEMS_KFV MTL
392 , MTL_PARAMETERS MP
393 , fnd_lookup_values_vl IT
394 where
395 DECODE(AHL_UTIL_PKG.IS_PM_INSTALLED,'Y','Y',mp.eam_enabled_flag )='Y'
396 AND MTL.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
397 and MTL.inventory_item_flag = 'Y'
398 AND IT.lookup_code (+) = MTL.item_type
399 AND IT.lookup_type (+) = 'ITEM_TYPE'
400 AND mtl.enabled_flag = 'Y'
401 AND SYSDATE BETWEEN NVL( mtl.start_date_active, SYSDATE )
402 AND NVL( mtl.end_date_active, SYSDATE )
403 and mtl.inventory_item_id = c_inventory_item_id
404 and mtl.organization_id = c_inventory_org_id
405 order by 1;
406 */
407
408
409 BEGIN
410 x_return_status := FND_API.G_RET_STS_SUCCESS;
411
412 IF ( ( p_item_number IS NULL OR
413 p_item_number = FND_API.G_MISS_CHAR ) AND
414 ( p_x_inventory_item_id IS NULL OR
415 p_x_inventory_item_id = FND_API.G_MISS_NUM ) AND
416 ( p_x_inventory_org_id IS NULL OR
417 p_x_inventory_org_id = FND_API.G_MISS_NUM ) ) THEN
418 x_return_status := FND_API.G_RET_STS_ERROR;
419 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
420 RETURN;
421 END IF;
422
423 IF ( ( p_item_number IS NULL OR
424 p_item_number = FND_API.G_MISS_CHAR ) AND
425 ( p_x_inventory_item_id IS NOT NULL AND
426 p_x_inventory_item_id <> FND_API.G_MISS_NUM AND
427 p_x_inventory_org_id IS NOT NULL AND
428 p_x_inventory_org_id <> FND_API.G_MISS_NUM ) ) THEN
429
430 OPEN get_rec_from_id( p_x_inventory_item_id , p_x_inventory_org_id );
431
432 FETCH get_rec_from_id INTO
433 l_inventory_item_id,
434 l_inventory_org_id,
435 l_inventory_item_flag;
436
437 IF ( get_rec_from_id%NOTFOUND ) THEN
438 x_return_status := FND_API.G_RET_STS_ERROR;
439 x_msg_data := 'AHL_COM_INVALID_ITEM';
440 ELSE
441 IF ( l_inventory_item_flag = 'N' ) THEN
442 x_return_status := FND_API.G_RET_STS_ERROR;
443 x_msg_data := 'AHL_COM_NOT_COMPONENT_ITEM';
444 END IF;
445 END IF;
446
447 CLOSE get_rec_from_id;
448 RETURN;
449
450 END IF;
451
452 IF ( p_item_number IS NOT NULL AND
453 p_item_number <> FND_API.G_MISS_CHAR ) THEN
454
455 OPEN get_rec_from_value( p_item_number , p_x_inventory_org_id);
456
457 LOOP
458 FETCH get_rec_from_value INTO
459 l_inventory_item_id,
460 l_inventory_org_id,
461 l_inventory_item_flag;
462
463 EXIT WHEN get_rec_from_value%NOTFOUND;
464
465 IF ( l_inventory_item_id = p_x_inventory_item_id AND
466 l_inventory_org_id = p_x_inventory_org_id AND
467 l_inventory_item_flag = 'Y' ) THEN
468 CLOSE get_rec_from_value;
469 RETURN;
470 END IF;
471
472 END LOOP;
473
474 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
475 x_return_status := FND_API.G_RET_STS_ERROR;
476 x_msg_data := 'AHL_COM_INVALID_ITEM';
477 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
478 IF ( l_inventory_item_flag = 'N' ) THEN
479 x_return_status := FND_API.G_RET_STS_ERROR;
480 x_msg_data := 'AHL_COM_NOT_COMPONENT_ITEM';
481 ELSE
482 p_x_inventory_item_id := l_inventory_item_id;
483 p_x_inventory_org_id := l_inventory_org_id;
484 END IF;
485 ELSE
486 x_return_status := FND_API.G_RET_STS_ERROR;
487 x_msg_data := 'AHL_COM_TOO_MANY_ITEMS';
488 END IF;
489
490 CLOSE get_rec_from_value;
491 RETURN;
492
493 END IF;
494
495 END validate_adt_item;
496
497
498 -- Procedure to validate Item
499 PROCEDURE validate_item
500 (
501 x_return_status OUT NOCOPY VARCHAR2,
502 x_msg_data OUT NOCOPY VARCHAR2,
503 p_item_number IN MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE,
504 p_x_inventory_item_id IN OUT NOCOPY MTL_SYSTEM_ITEMS.inventory_item_id%TYPE,
505 p_x_inventory_org_id IN OUT NOCOPY MTL_SYSTEM_ITEMS.organization_id%TYPE
506 )
507 IS
508
509 l_inventory_item_flag MTL_SYSTEM_ITEMS.inventory_item_flag%TYPE;
510 l_wip_supply_type MTL_SYSTEM_ITEMS.wip_supply_type%TYPE;
511 l_stock_enabled_flag MTL_SYSTEM_ITEMS.stock_enabled_flag%TYPE;
512 l_mtl_txns_enabled_flag MTL_SYSTEM_ITEMS.mtl_transactions_enabled_flag%TYPE;
513 l_inventory_item_id MTL_SYSTEM_ITEMS.inventory_item_id%TYPE;
514 l_inventory_org_id MTL_SYSTEM_ITEMS.organization_id%TYPE;
515
516 CURSOR get_rec_from_value ( c_item_number MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE ,
517 c_inventory_org_id MTL_SYSTEM_ITEMS.organization_id%TYPE)
518 IS
519 SELECT MTL.inventory_item_id,
520 MTL.organization_id,
521 MTL.inventory_item_flag,
522 MTL.mtl_transactions_enabled_flag,
523 MTL.stock_enabled_flag,
524 NVL(MTL.wip_supply_type,0)
525 FROM AHL_MTL_ITEMS_EAM_V MTL
526 WHERE
527 MTL.concatenated_segments = c_item_number
528 AND MTL.organization_id = c_inventory_org_id
529 AND MTL.enabled_flag = 'Y';
530 /*
531 SELECT DISTINCT MI.inventory_item_id,
532 -- MI.organization_id,
533 MP.master_organization_id,
534 NVL(MI.inventory_item_flag,'X'),
535 NVL(MI.mtl_transactions_enabled_flag,'X'),
536 NVL(MI.stock_enabled_flag,'X'),
537 NVL(MI.wip_supply_type,0)
538 FROM MTL_PARAMETERS MP, MTL_SYSTEM_ITEMS_KFV MI
539 WHERE DECODE(AHL_UTIL_PKG.IS_PM_INSTALLED,'Y','Y',MP.eam_enabled_flag )='Y'
540 AND MP.master_organization_id = MI.organization_id
541 AND MI.concatenated_segments = c_item_number
542 --AND MI.organization_id = c_inventory_org_id
543 AND MP.master_organization_id = c_inventory_org_id
544 AND MI.enabled_flag = 'Y'
545 AND SYSDATE BETWEEN NVL( MI.start_date_active, SYSDATE )
546 AND NVL( MI.end_date_active, SYSDATE );
547 */
548 /*
549 SELECT DISTINCT
550 mtl.inventory_item_id ,
551 mtl.inventory_org_id ,
552 NVL(mtl.inventory_item_flag,'X'),
553 NVL(mtl.mtl_transactions_enabled_flag,'X'),
554 NVL(mtl.stock_enabled_flag,'X'),
555 NVL(mtl.wip_supply_type,0)
556 from ahl_mtl_items_non_ou_v mtl
557 where
558 DECODE(AHL_UTIL_PKG.IS_PM_INSTALLED,'Y','Y',mtl.eam_enabled_flag )='Y'
559 AND mtl.enabled_flag = 'Y'
560 AND SYSDATE BETWEEN NVL( mtl.start_date_active, SYSDATE )
561 AND NVL( mtl.end_date_active, SYSDATE )
562 and upper(mtl.concatenated_segments) like upper(c_item_number)
563 and mtl.inventory_org_id = c_inventory_org_id
564 order by 1;
565 */
566 --
567 CURSOR get_rec_from_id ( c_inventory_item_id MTL_SYSTEM_ITEMS.inventory_item_id%TYPE,
568 c_inventory_org_id MTL_SYSTEM_ITEMS.organization_id%TYPE )
569 IS
570 SELECT MTL.inventory_item_id,
571 MTL.organization_id,
572 MTL.inventory_item_flag,
573 MTL.mtl_transactions_enabled_flag,
574 MTL.stock_enabled_flag,
575 NVL(MTL.wip_supply_type,0)
576 FROM AHL_MTL_ITEMS_EAM_V MTL
577 WHERE
578 MTL.inventory_item_id = c_inventory_item_id
579 AND MTL.organization_id = c_inventory_org_id
580 AND MTL.enabled_flag = 'Y';
581 /*
582 SELECT DISTINCT MI.inventory_item_id,
583 -- MI.organization_id,
584 MP.master_organization_id,
585 NVL(MI.inventory_item_flag,'X'),
586 NVL(MI.mtl_transactions_enabled_flag,'X'),
587 NVL(MI.stock_enabled_flag,'X'),
588 NVL(MI.wip_supply_type,0)
589 FROM MTL_PARAMETERS MP, MTL_SYSTEM_ITEMS_KFV MI
590 WHERE DECODE(AHL_UTIL_PKG.IS_PM_INSTALLED,'Y','Y',MP.eam_enabled_flag )='Y'
591 AND MP.master_organization_id = MI.organization_id
592 AND MI.inventory_item_id = c_inventory_item_id
593 --AND MI.organization_id = c_inventory_org_id
594 AND MP.master_organization_id = c_inventory_org_id
595 AND MI.enabled_flag = 'Y'
596 AND SYSDATE BETWEEN NVL( MI.start_date_active, SYSDATE )
597 AND NVL( MI.end_date_active, SYSDATE );
598 */
599 /*
600 SELECT DISTINCT
601 mtl.inventory_item_id ,
602 mtl.inventory_org_id ,
603 NVL(mtl.inventory_item_flag,'X'),
604 NVL(mtl.mtl_transactions_enabled_flag,'X'),
605 NVL(mtl.stock_enabled_flag,'X'),
606 NVL(mtl.wip_supply_type,0)
607 from ahl_mtl_items_non_ou_v mtl
608 where
609 DECODE(AHL_UTIL_PKG.IS_PM_INSTALLED,'Y','Y',mtl.eam_enabled_flag )='Y'
610 AND mtl.enabled_flag = 'Y'
611 AND SYSDATE BETWEEN NVL( mtl.start_date_active, SYSDATE )
612 AND NVL( mtl.end_date_active, SYSDATE )
613 and mtl.inventory_item_id = c_inventory_item_id
614 and mtl.inventory_org_id = c_inventory_org_id
615 order by 1;
616 */
617
618 BEGIN
619 x_return_status := FND_API.G_RET_STS_SUCCESS;
620
621 IF ( ( p_item_number IS NULL OR
622 p_item_number = FND_API.G_MISS_CHAR ) AND
623 ( p_x_inventory_item_id IS NULL OR
624 p_x_inventory_item_id = FND_API.G_MISS_NUM ) AND
625 ( p_x_inventory_org_id IS NULL OR
626 p_x_inventory_org_id = FND_API.G_MISS_NUM ) ) THEN
627 x_return_status := FND_API.G_RET_STS_ERROR;
628 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
629 RETURN;
630 END IF;
631
632 IF ( ( p_item_number IS NULL OR
633 p_item_number = FND_API.G_MISS_CHAR ) AND
634 ( p_x_inventory_item_id IS NOT NULL AND
635 p_x_inventory_item_id <> FND_API.G_MISS_NUM AND
636 p_x_inventory_org_id IS NOT NULL AND
637 p_x_inventory_org_id <> FND_API.G_MISS_NUM ) ) THEN
638
639 OPEN get_rec_from_id( p_x_inventory_item_id , p_x_inventory_org_id );
640
641 FETCH get_rec_from_id INTO
642 l_inventory_item_id,
643 l_inventory_org_id,
644 l_inventory_item_flag,
645 l_mtl_txns_enabled_flag,
646 l_stock_enabled_flag,
647 l_wip_supply_type;
648
649 IF ( get_rec_from_id%NOTFOUND ) THEN
650 x_return_status := FND_API.G_RET_STS_ERROR;
651 x_msg_data := 'AHL_COM_INVALID_ITEM';
652 ELSE
653 IF ( l_inventory_item_flag = 'N' OR
654 l_mtl_txns_enabled_flag = 'N' OR
655 l_stock_enabled_flag = 'N' OR
656 l_wip_supply_type <> 1 ) THEN
657 x_return_status := FND_API.G_RET_STS_ERROR;
658 x_msg_data := 'AHL_COM_NOT_COMPONENT_ITEM';
659 END IF;
660 END IF;
661
662 CLOSE get_rec_from_id;
663 RETURN;
664
665 END IF;
666
667 IF ( p_item_number IS NOT NULL AND
668 p_item_number <> FND_API.G_MISS_CHAR ) THEN
669
670 OPEN get_rec_from_value( p_item_number , p_x_inventory_org_id);
671
672 LOOP
673 FETCH get_rec_from_value INTO
674 l_inventory_item_id,
675 l_inventory_org_id,
676 l_inventory_item_flag,
677 l_mtl_txns_enabled_flag,
678 l_stock_enabled_flag,
679 l_wip_supply_type;
680
681 EXIT WHEN get_rec_from_value%NOTFOUND;
682
683 IF ( l_inventory_item_id = p_x_inventory_item_id AND
684 l_inventory_org_id = p_x_inventory_org_id AND
685 l_inventory_item_flag = 'Y' AND
686 l_mtl_txns_enabled_flag = 'Y' AND
687 l_stock_enabled_flag = 'Y' AND
688 l_wip_supply_type = 1 ) THEN
689 CLOSE get_rec_from_value;
690 RETURN;
691 END IF;
692
693 END LOOP;
694
695 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
696 x_return_status := FND_API.G_RET_STS_ERROR;
697 x_msg_data := 'AHL_COM_INVALID_ITEM';
698 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
699 IF ( l_inventory_item_flag = 'N' OR
700 l_stock_enabled_flag = 'N' OR
701 l_mtl_txns_enabled_flag = 'N' OR
702 l_wip_supply_type <> 1 ) THEN
703 x_return_status := FND_API.G_RET_STS_ERROR;
704 x_msg_data := 'AHL_COM_NOT_COMPONENT_ITEM';
705 ELSE
706 p_x_inventory_item_id := l_inventory_item_id;
707 p_x_inventory_org_id := l_inventory_org_id;
708 END IF;
709 ELSE
710 x_return_status := FND_API.G_RET_STS_ERROR;
711 x_msg_data := 'AHL_COM_TOO_MANY_ITEMS';
712 END IF;
713
714 CLOSE get_rec_from_value;
715 RETURN;
716
717 END IF;
718
719 END validate_item;
720
721 -- Procedure to validate Service Item
722 PROCEDURE validate_service_item
723 (
724 x_return_status OUT NOCOPY VARCHAR2,
725 x_msg_data OUT NOCOPY VARCHAR2,
726 p_item_number IN MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE,
727 p_x_inventory_item_id IN OUT NOCOPY MTL_SYSTEM_ITEMS.inventory_item_id%TYPE,
728 p_x_inventory_org_id IN OUT NOCOPY MTL_SYSTEM_ITEMS.organization_id%TYPE
729 )
730 IS
731
732 l_outside_operation_flag MTL_SYSTEM_ITEMS.outside_operation_flag%TYPE;
733 l_purchasing_enabled_flag MTL_SYSTEM_ITEMS.purchasing_enabled_flag%TYPE;
734 l_purchasing_item_flag MTL_SYSTEM_ITEMS.purchasing_item_flag%TYPE;
735 l_inventory_item_id MTL_SYSTEM_ITEMS.inventory_item_id%TYPE;
736 l_inventory_org_id MTL_SYSTEM_ITEMS.organization_id%TYPE;
737
738 CURSOR get_rec_from_value ( c_item_number MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE )
739 IS
740 SELECT DISTINCT MI.inventory_item_id,
741 MI.organization_id,
742 MI.outside_operation_flag,
743 MI.purchasing_item_flag,
744 MI.purchasing_enabled_flag
745 FROM MTL_PARAMETERS MP, MTL_SYSTEM_ITEMS_KFV MI
746 WHERE DECODE(AHL_UTIL_PKG.IS_PM_INSTALLED,'Y','Y',MP.eam_enabled_flag )='Y'
747 AND MP.organization_id = MI.organization_id
748 AND MI.concatenated_segments = c_item_number
749 AND MI.enabled_flag = 'Y'
750 AND SYSDATE BETWEEN NVL( MI.start_date_active, SYSDATE )
751 AND NVL( MI.end_date_active, SYSDATE );
752
753 CURSOR get_rec_from_id ( c_inventory_item_id MTL_SYSTEM_ITEMS.inventory_item_id%TYPE,
754 c_inventory_org_id MTL_SYSTEM_ITEMS.organization_id%TYPE )
755 IS
756 SELECT DISTINCT MI.inventory_item_id,
757 MI.organization_id,
758 MI.outside_operation_flag,
759 MI.purchasing_item_flag,
760 MI.purchasing_enabled_flag
761 FROM MTL_PARAMETERS MP, MTL_SYSTEM_ITEMS_KFV MI
762 WHERE DECODE(AHL_UTIL_PKG.IS_PM_INSTALLED,'Y','Y',MP.eam_enabled_flag )='Y'
763 AND MP.organization_id = MI.organization_id
764 AND MI.inventory_item_id = c_inventory_item_id
765 AND MI.organization_id = c_inventory_org_id
766 AND MI.enabled_flag = 'Y'
767 AND SYSDATE BETWEEN NVL( MI.start_date_active, SYSDATE )
768 AND NVL( MI.end_date_active, SYSDATE );
769
770 BEGIN
771 x_return_status := FND_API.G_RET_STS_SUCCESS;
772
773 IF ( ( p_item_number IS NULL OR
774 p_item_number = FND_API.G_MISS_CHAR ) AND
775 ( p_x_inventory_item_id IS NULL OR
776 p_x_inventory_item_id = FND_API.G_MISS_NUM ) AND
777 ( p_x_inventory_org_id IS NULL OR
778 p_x_inventory_org_id = FND_API.G_MISS_NUM ) ) THEN
779 x_return_status := FND_API.G_RET_STS_ERROR;
780 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
781 RETURN;
782 END IF;
783
784 IF ( ( p_item_number IS NULL OR
785 p_item_number = FND_API.G_MISS_CHAR ) AND
786 ( p_x_inventory_item_id IS NOT NULL AND
787 p_x_inventory_item_id <> FND_API.G_MISS_NUM AND
788 p_x_inventory_org_id IS NOT NULL AND
789 p_x_inventory_org_id <> FND_API.G_MISS_NUM ) ) THEN
790
791 OPEN get_rec_from_id( p_x_inventory_item_id , p_x_inventory_org_id );
792
793 FETCH get_rec_from_id INTO
794 l_inventory_item_id,
795 l_inventory_org_id,
796 l_outside_operation_flag,
797 l_purchasing_item_flag,
798 l_purchasing_enabled_flag;
799
800 IF ( get_rec_from_id%NOTFOUND ) THEN
801 x_return_status := FND_API.G_RET_STS_ERROR;
802 x_msg_data := 'AHL_COM_INVALID_ITEM';
803 ELSE
804 IF ( l_outside_operation_flag = 'Y' OR
805 l_purchasing_item_flag = 'N' OR
806 l_purchasing_enabled_flag = 'N' ) THEN
807 x_return_status := FND_API.G_RET_STS_ERROR;
808 x_msg_data := 'AHL_COM_NOT_SERVICE_ITEM';
809 END IF;
810 END IF;
811
812 CLOSE get_rec_from_id;
813 RETURN;
814
815 END IF;
816
817 IF ( p_item_number IS NOT NULL AND
818 p_item_number <> FND_API.G_MISS_CHAR ) THEN
819
820 OPEN get_rec_from_value( p_item_number );
821
822 LOOP
823 FETCH get_rec_from_value INTO
824 l_inventory_item_id,
825 l_inventory_org_id,
826 l_outside_operation_flag,
827 l_purchasing_item_flag,
828 l_purchasing_enabled_flag;
829
830 EXIT WHEN get_rec_from_value%NOTFOUND;
831
832 IF ( l_inventory_item_id = p_x_inventory_item_id AND
833 l_inventory_org_id = p_x_inventory_org_id AND
834 l_outside_operation_flag = 'N' AND
835 l_purchasing_item_flag = 'Y' AND
836 l_purchasing_enabled_flag = 'Y' ) THEN
837 CLOSE get_rec_from_value;
838 RETURN;
839 END IF;
840
841 END LOOP;
842
843 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
844 x_return_status := FND_API.G_RET_STS_ERROR;
845 x_msg_data := 'AHL_COM_INVALID_ITEM';
846 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
847 IF ( l_outside_operation_flag = 'Y' OR
848 l_purchasing_enabled_flag = 'N' OR
849 l_purchasing_item_flag = 'N' ) THEN
850 x_return_status := FND_API.G_RET_STS_ERROR;
851 x_msg_data := 'AHL_COM_NOT_SERVICE_ITEM';
852 ELSE
853 p_x_inventory_item_id := l_inventory_item_id;
854 p_x_inventory_org_id := l_inventory_org_id;
855 END IF;
856 ELSE
857 x_return_status := FND_API.G_RET_STS_ERROR;
858 x_msg_data := 'AHL_COM_TOO_MANY_ITEMS';
859 END IF;
860
861 CLOSE get_rec_from_value;
862 RETURN;
863
864 END IF;
865
866 END validate_service_item;
867
868 -- Procedure to validate Effectivity Item
869 PROCEDURE validate_effectivity_item
870 (
871 x_return_status OUT NOCOPY VARCHAR2,
872 x_msg_data OUT NOCOPY VARCHAR2,
873 p_item_number IN MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE,
874 p_org_code IN MTL_PARAMETERS.ORGANIZATION_CODE%TYPE,
875 p_x_inventory_item_id IN OUT NOCOPY MTL_SYSTEM_ITEMS.inventory_item_id%TYPE,
876 p_x_inventory_org_id IN OUT NOCOPY MTL_SYSTEM_ITEMS.organization_id%TYPE
877 )
878 IS
879
880 l_comms_nl_trackable_flag MTL_SYSTEM_ITEMS.COMMS_NL_TRACKABLE_FLAG%TYPE;
881 l_inventory_item_id MTL_SYSTEM_ITEMS.inventory_item_id%TYPE;
882 l_inventory_org_id MTL_SYSTEM_ITEMS.organization_id%TYPE;
883
884 CURSOR get_rec_from_value ( c_item_number MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE,
885 c_org_code MTL_PARAMETERS.ORGANIZATION_CODE%TYPE)
886 IS
887 SELECT
888 MTL.INVENTORY_ITEM_ID ,
889 MTL.organization_id ,
890 MTL.comms_nl_trackable_flag
891 from AHL_MTL_ITEMS_EAM_V MTL
892 where
893 upper(nvl(MTL.comms_nl_trackable_flag,'N')) = 'Y'
894 and MTL.enabled_flag = 'Y'
895 and upper(MTL.concatenated_segments) like upper(c_item_number)
896 and upper(MTL.organization_code) like upper(c_org_code)
897 order by 1;
898 /*
899 SELECT DISTINCT MI.inventory_item_id,
900 MP.master_organization_id,
901 MI.comms_nl_trackable_flag
902 FROM MTL_PARAMETERS MP, MTL_SYSTEM_ITEMS_KFV MI
903 WHERE MP.master_organization_id = MI.organization_id
904 AND upper(MI.concatenated_segments) = upper(c_item_number)
905 AND MI.enabled_flag = 'Y'
906 AND upper(MP.ORGANIZATION_CODE)=upper(c_org_code)
907 AND SYSDATE BETWEEN NVL( MI.start_date_active, SYSDATE )
908 AND NVL( MI.end_date_active, SYSDATE )
909 AND DECODE(AHL_UTIL_PKG.IS_PM_INSTALLED,'Y','Y',MP.eam_enabled_flag )='Y';
910 */
911 /*
912 SELECT DISTINCT
913 mtl.INVENTORY_ITEM_ID ,
914 mtl.organization_id ,
915 mtl.comms_nl_trackable_flag
916 from MTL_SYSTEM_ITEMS_KFV MTL
917 , MTL_PARAMETERS MP
918 , MTL_PARAMETERS MP1
919 , fnd_lookup_values_vl IT
920 where
921 DECODE(AHL_UTIL_PKG.IS_PM_INSTALLED,'Y','Y',mp.eam_enabled_flag )='Y'
922 AND MTL.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
923 and MP.MASTER_ORGANIZATION_ID = MP1.ORGANIZATION_ID
924 and sysdate between nvl( MTL.start_date_active, sysdate )
925 and nvl( MTL.end_date_active, sysdate )
926 and IT.lookup_code (+) = MTL.item_type
927 and IT.lookup_type (+) = 'ITEM_TYPE'
928 and MTL.enabled_flag = 'Y'
929 and upper(mtl.concatenated_segments) like upper(c_item_number)
930 and upper(mp1.organization_code) like upper(c_org_code)
931 order by 1;
932 */
933
934 CURSOR get_rec_from_id ( c_inventory_item_id MTL_SYSTEM_ITEMS.inventory_item_id%TYPE,
935 c_inventory_org_id MTL_SYSTEM_ITEMS.organization_id%TYPE )
936 IS
937 SELECT
938 mtl.INVENTORY_ITEM_ID ,
939 mtl.organization_id ,
940 mtl.comms_nl_trackable_flag
941 from AHL_MTL_ITEMS_EAM_V MTL
942 where
943 upper(nvl(MTL.comms_nl_trackable_flag,'N')) = 'Y'
944 and MTL.enabled_flag = 'Y'
945 and mtl.inventory_item_id = c_inventory_item_id
946 and mtl.organization_id = c_inventory_org_id
947 order by 1;
948 /*
949 SELECT DISTINCT MI.inventory_item_id,
950 MP.master_organization_id,
951 MI.comms_nl_trackable_flag
952 FROM MTL_PARAMETERS MP, MTL_SYSTEM_ITEMS_KFV MI
953 WHERE MP.master_organization_id = MI.organization_id
954 AND MI.inventory_item_id = c_inventory_item_id
955 AND MI.organization_id = c_inventory_org_id
956 AND MI.enabled_flag = 'Y'
957 AND SYSDATE BETWEEN NVL( MI.start_date_active, SYSDATE )
958 AND NVL( MI.end_date_active, SYSDATE )
959 AND DECODE(AHL_UTIL_PKG.IS_PM_INSTALLED,'Y','Y',MP.eam_enabled_flag )='Y';
960 */
961 /*SELECT DISTINCT
962 mtl.INVENTORY_ITEM_ID ,
963 mtl.organization_id ,
964 mtl.comms_nl_trackable_flag
965 from MTL_SYSTEM_ITEMS_KFV MTL
966 , MTL_PARAMETERS MP
967 , fnd_lookup_values_vl IT
968 where
969 DECODE(AHL_UTIL_PKG.IS_PM_INSTALLED,'Y','Y',mp.eam_enabled_flag )='Y'
970 AND MTL.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
971 and sysdate between nvl( MTL.start_date_active, sysdate )
972 and nvl( MTL.end_date_active, sysdate )
973 and IT.lookup_code (+) = MTL.item_type
974 and IT.lookup_type (+) = 'ITEM_TYPE'
975 and MTL.enabled_flag = 'Y'
976 and mtl.inventory_item_id = c_inventory_item_id
977 and mtl.organization_id = c_inventory_org_id
978 order by 1;
979 */
980
981
982 BEGIN
983 x_return_status := FND_API.G_RET_STS_SUCCESS;
984
985 IF ( ( p_item_number IS NULL OR
986 p_item_number = FND_API.G_MISS_CHAR ) AND
987 ( p_x_inventory_item_id IS NULL OR
988 p_x_inventory_item_id = FND_API.G_MISS_NUM ) AND
989 ( p_x_inventory_org_id IS NULL OR
990 p_x_inventory_org_id = FND_API.G_MISS_NUM ) ) THEN
991 x_return_status := FND_API.G_RET_STS_ERROR;
992 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
993 RETURN;
994 END IF;
995
996 IF ( (
997 ( p_item_number IS NULL OR p_item_number = FND_API.G_MISS_CHAR )
998 OR ( p_org_code IS NULL OR p_org_code = FND_API.G_MISS_CHAR )
999 )
1000 AND
1001 (
1002 ( p_x_inventory_item_id IS NOT NULL AND p_x_inventory_item_id <> FND_API.G_MISS_NUM )
1003 AND ( p_x_inventory_org_id IS NOT NULL AND p_x_inventory_org_id <> FND_API.G_MISS_NUM )
1004 )
1005 ) THEN
1006
1007 OPEN get_rec_from_id( p_x_inventory_item_id , p_x_inventory_org_id );
1008
1009 FETCH get_rec_from_id INTO
1010 l_inventory_item_id,
1011 l_inventory_org_id,
1012 l_comms_nl_trackable_flag;
1013
1014 IF ( get_rec_from_id%NOTFOUND ) THEN
1015 x_return_status := FND_API.G_RET_STS_ERROR;
1016 x_msg_data := 'AHL_COM_INVALID_ITEM';
1017 ELSE
1018 IF ( l_comms_nl_trackable_flag = 'N' ) THEN
1019 x_return_status := FND_API.G_RET_STS_ERROR;
1020 x_msg_data := 'AHL_COM_NOT_effectivity_ITEM';
1021 END IF;
1022 END IF;
1023
1024 CLOSE get_rec_from_id;
1025 RETURN;
1026
1027 END IF;
1028
1029 IF ( (p_item_number IS NOT NULL AND p_item_number <> FND_API.G_MISS_CHAR )
1030 AND(p_org_code IS NOT NULL AND p_item_number <> FND_API.G_MISS_CHAR)
1031 ) THEN
1032
1033 OPEN get_rec_from_value( p_item_number, p_org_code);
1034
1035 LOOP
1036 FETCH get_rec_from_value INTO
1037 l_inventory_item_id,
1038 l_inventory_org_id,
1039 l_comms_nl_trackable_flag;
1040
1041 EXIT WHEN get_rec_from_value%NOTFOUND;
1042
1043 IF ( l_inventory_item_id = p_x_inventory_item_id AND
1044 l_inventory_org_id = p_x_inventory_org_id AND
1045 l_comms_nl_trackable_flag = 'Y' ) THEN
1046 CLOSE get_rec_from_value;
1047 RETURN;
1048 END IF;
1049
1050 END LOOP;
1051
1052 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
1053 x_return_status := FND_API.G_RET_STS_ERROR;
1054 x_msg_data := 'AHL_COM_INVALID_ITEM';
1055 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
1056 IF ( l_comms_nl_trackable_flag = 'N' ) THEN
1057 x_return_status := FND_API.G_RET_STS_ERROR;
1058 x_msg_data := 'AHL_COM_NOT_effectivity_ITEM';
1059 ELSE
1060 p_x_inventory_item_id := l_inventory_item_id;
1061 p_x_inventory_org_id := l_inventory_org_id;
1062 END IF;
1063 ELSE
1064 x_return_status := FND_API.G_RET_STS_ERROR;
1065 x_msg_data := 'AHL_COM_TOO_MANY_ITEMS';
1066 END IF;
1067
1068 CLOSE get_rec_from_value;
1069 RETURN;
1070
1071 END IF;
1072
1073 END validate_effectivity_item;
1074
1075 -- Procedure to validate Accounting class
1076 PROCEDURE validate_accounting_class
1077 (
1078 x_return_status OUT NOCOPY VARCHAR2,
1079 x_msg_data OUT NOCOPY VARCHAR2,
1080 p_accounting_class IN WIP_ACCOUNTING_CLASSES.description%TYPE,
1081 p_x_accounting_class_code IN OUT NOCOPY WIP_ACCOUNTING_CLASSES.class_code%TYPE,
1082 p_x_accounting_class_org_id IN OUT NOCOPY WIP_ACCOUNTING_CLASSES.organization_id%TYPE
1083 )
1084 IS
1085
1086 l_accounting_class_code WIP_ACCOUNTING_CLASSES.class_code%TYPE;
1087 l_accounting_class_org_id WIP_ACCOUNTING_CLASSES.organization_id%TYPE;
1088
1089 CURSOR get_rec_from_value ( c_accounting_class WIP_ACCOUNTING_CLASSES.description%TYPE )
1090 IS
1091 SELECT DISTINCT class_code,
1092 organization_id
1093 FROM WIP_ACCOUNTING_CLASSES
1094 WHERE description = c_accounting_class
1095 AND class_type = 6;
1096
1097 CURSOR get_rec_from_id ( c_accounting_class_code WIP_ACCOUNTING_CLASSES.class_code%TYPE ,
1098 c_accounting_class_org_id WIP_ACCOUNTING_CLASSES.organization_id%TYPE )
1099 IS
1100 SELECT DISTINCT class_code,
1101 organization_id
1102 FROM WIP_ACCOUNTING_CLASSES
1103 WHERE class_code = c_accounting_class_code
1104 AND organization_id = c_accounting_class_org_id
1105 AND class_type = 6;
1106
1107 BEGIN
1108 x_return_status := FND_API.G_RET_STS_SUCCESS;
1109
1110 IF ( ( p_accounting_class IS NULL OR
1111 p_accounting_class = FND_API.G_MISS_CHAR ) AND
1112 ( p_x_accounting_class_code IS NULL OR
1113 p_x_accounting_class_code = FND_API.G_MISS_CHAR ) AND
1114 ( p_x_accounting_class_org_id IS NULL OR
1115 p_x_accounting_class_org_id = FND_API.G_MISS_NUM ) ) THEN
1116 x_return_status := FND_API.G_RET_STS_ERROR;
1117 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
1118 RETURN;
1119 END IF;
1120
1121 IF ( ( p_accounting_class IS NULL OR
1122 p_accounting_class = FND_API.G_MISS_CHAR ) AND
1123 ( p_x_accounting_class_code IS NOT NULL AND
1124 p_x_accounting_class_code <> FND_API.G_MISS_CHAR AND
1125 p_x_accounting_class_org_id IS NOT NULL AND
1126 p_x_accounting_class_org_id <> FND_API.G_MISS_NUM ) ) THEN
1127
1128 OPEN get_rec_from_id( p_x_accounting_class_code , p_x_accounting_class_org_id );
1129
1130 FETCH get_rec_from_id INTO
1131 l_accounting_class_code,
1132 l_accounting_class_org_id;
1133
1134 IF get_rec_from_id%NOTFOUND THEN
1135 x_return_status := FND_API.G_RET_STS_ERROR;
1136 x_msg_data := 'AHL_RM_INVALID_ACC_CLASS';
1137 END IF;
1138
1139 CLOSE get_rec_from_id;
1140 RETURN;
1141
1142 END IF;
1143
1144 IF ( p_accounting_class IS NOT NULL AND
1145 p_accounting_class <> FND_API.G_MISS_CHAR ) THEN
1146
1147 OPEN get_rec_from_value( p_accounting_class );
1148
1149 LOOP
1150 FETCH get_rec_from_value INTO
1151 l_accounting_class_code,
1152 l_accounting_class_org_id;
1153
1154 EXIT WHEN get_rec_from_value%NOTFOUND;
1155
1156 IF ( l_accounting_class_code = p_x_accounting_class_code AND
1157 l_accounting_class_org_id = p_x_accounting_class_org_id ) THEN
1158 CLOSE get_rec_from_value;
1159 RETURN;
1160 END IF;
1161
1162 END LOOP;
1163
1164 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
1165 x_return_status := FND_API.G_RET_STS_ERROR;
1166 x_msg_data := 'AHL_RM_INVALID_ACC_CLASS';
1167 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
1168 p_x_accounting_class_code := l_accounting_class_code;
1169 p_x_accounting_class_org_id := l_accounting_class_org_id;
1170 ELSE
1171 x_return_status := FND_API.G_RET_STS_ERROR;
1172 x_msg_data := 'AHL_RM_TOO_MANY_ACC_CLASSES';
1173 END IF;
1174
1175 CLOSE get_rec_from_value;
1176 RETURN;
1177
1178 END IF;
1179 END validate_accounting_class;
1180
1181 -- Procedure to validate Task Template Group
1182 PROCEDURE validate_task_template_group
1183 (
1184 x_return_status OUT NOCOPY VARCHAR2,
1185 x_msg_data OUT NOCOPY VARCHAR2,
1186 p_task_template_group IN JTF_TASK_TEMP_GROUPS_VL.template_group_name%TYPE,
1187 p_x_task_template_group_id IN OUT NOCOPY JTF_TASK_TEMP_GROUPS_VL.task_template_group_id%TYPE
1188 )
1189 IS
1190
1191 l_task_template_group_id JTF_TASK_TEMP_GROUPS_VL.task_template_group_id%TYPE;
1192
1193 CURSOR get_rec_from_value ( c_task_template_group JTF_TASK_TEMP_GROUPS_VL.template_group_name%TYPE )
1194 IS
1195 SELECT DISTINCT task_template_group_id
1196 FROM JTF_TASK_TEMP_GROUPS_VL
1197 WHERE trunc(sysdate) >= trunc(nvl(start_date_active, sysdate)) and
1198 trunc(sysdate) < trunc(nvl(end_date_active, sysdate+1)) and
1199 template_group_name = c_task_template_group;
1200
1201 CURSOR get_rec_from_id ( c_task_template_group_id JTF_TASK_TEMP_GROUPS_VL.task_template_group_id%TYPE )
1202 IS
1203 SELECT DISTINCT task_template_group_id
1204 FROM JTF_TASK_TEMP_GROUPS_VL
1205 WHERE trunc(sysdate) >= trunc(nvl(start_date_active, sysdate)) and
1206 trunc(sysdate) < trunc(nvl(end_date_active, sysdate+1)) and
1207 task_template_group_id = c_task_template_group_id;
1208
1209 BEGIN
1210 x_return_status := FND_API.G_RET_STS_SUCCESS;
1211
1212 IF ( ( p_task_template_group IS NULL OR
1213 p_task_template_group = FND_API.G_MISS_CHAR ) AND
1214 ( p_x_task_template_group_id IS NULL OR
1215 p_x_task_template_group_id = FND_API.G_MISS_NUM ) ) THEN
1216 x_return_status := FND_API.G_RET_STS_ERROR;
1217 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
1218 RETURN;
1219 END IF;
1220
1221 IF ( ( p_task_template_group IS NULL OR
1222 p_task_template_group = FND_API.G_MISS_CHAR ) AND
1223 ( p_x_task_template_group_id IS NOT NULL AND
1224 p_x_task_template_group_id <> FND_API.G_MISS_NUM ) ) THEN
1225
1226 OPEN get_rec_from_id( p_x_task_template_group_id );
1227
1228 FETCH get_rec_from_id INTO
1229 l_task_template_group_id;
1230
1231 IF get_rec_from_id%NOTFOUND THEN
1232 x_return_status := FND_API.G_RET_STS_ERROR;
1233 x_msg_data := 'AHL_RM_INVALID_TASK_TEMPLATE';
1234 END IF;
1235
1236 CLOSE get_rec_from_id;
1237 RETURN;
1238
1239 END IF;
1240
1241 IF ( p_task_template_group IS NOT NULL AND
1242 p_task_template_group <> FND_API.G_MISS_CHAR ) THEN
1243
1244 OPEN get_rec_from_value( p_task_template_group );
1245
1246 LOOP
1247 FETCH get_rec_from_value INTO
1248 l_task_template_group_id;
1249
1250 EXIT WHEN get_rec_from_value%NOTFOUND;
1251
1252 IF ( l_task_template_group_id = p_x_task_template_group_id ) THEN
1253 CLOSE get_rec_from_value;
1254 RETURN;
1255 END IF;
1256
1257 END LOOP;
1258
1259 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
1260 x_return_status := FND_API.G_RET_STS_ERROR;
1261 x_msg_data := 'AHL_RM_INVALID_TASK_TEMPLATE';
1262 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
1263 p_x_task_template_group_id := l_task_template_group_id;
1264 ELSE
1265 x_return_status := FND_API.G_RET_STS_ERROR;
1266 x_msg_data := 'AHL_RM_TOO_MANY_TASK_TEMPLATES';
1267 END IF;
1268
1269 CLOSE get_rec_from_value;
1270 RETURN;
1271
1272 END IF;
1273
1274 END validate_task_template_group;
1275
1276 -- Procedure to validate QA Inspection Type
1277 PROCEDURE validate_qa_inspection_type
1278 (
1279 x_return_status OUT NOCOPY VARCHAR2,
1280 x_msg_data OUT NOCOPY VARCHAR2,
1281 p_qa_inspection_type_desc IN QA_CHAR_VALUE_LOOKUPS_V.description%TYPE,
1282 p_x_qa_inspection_type IN OUT NOCOPY QA_CHAR_VALUE_LOOKUPS_V.short_code%TYPE
1283 )
1284 IS
1285
1286 l_qa_inspection_type QA_CHAR_VALUE_LOOKUPS_V.short_code%TYPE;
1287
1288 CURSOR get_rec_from_value ( c_qa_inspection_type_desc QA_CHAR_VALUE_LOOKUPS_V.description%TYPE )
1289 IS
1290 SELECT DISTINCT short_code
1291 FROM QA_CHAR_VALUE_LOOKUPS_V
1292 WHERE description = c_qa_inspection_type_desc;
1293
1294 CURSOR get_rec_from_id ( c_qa_inspection_type QA_CHAR_VALUE_LOOKUPS_V.short_code%TYPE )
1295 IS
1296 SELECT DISTINCT short_code
1297 FROM QA_CHAR_VALUE_LOOKUPS_V
1298 WHERE short_code = c_qa_inspection_type;
1299
1300 BEGIN
1301 x_return_status := FND_API.G_RET_STS_SUCCESS;
1302
1303 IF ( ( p_qa_inspection_type_desc IS NULL OR
1304 p_qa_inspection_type_desc = FND_API.G_MISS_CHAR ) AND
1305 ( p_x_qa_inspection_type IS NULL OR
1306 p_x_qa_inspection_type = FND_API.G_MISS_CHAR ) ) THEN
1307 x_return_status := FND_API.G_RET_STS_ERROR;
1308 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
1309 RETURN;
1310 END IF;
1311
1312 IF ( ( p_qa_inspection_type_desc IS NULL OR
1313 p_qa_inspection_type_desc = FND_API.G_MISS_CHAR ) AND
1314 ( p_x_qa_inspection_type IS NOT NULL AND
1315 p_x_qa_inspection_type <> FND_API.G_MISS_CHAR ) ) THEN
1316
1317 OPEN get_rec_from_id( p_x_qa_inspection_type );
1318
1319 FETCH get_rec_from_id INTO
1320 l_qa_inspection_type;
1321
1322 IF get_rec_from_id%NOTFOUND THEN
1323 x_return_status := FND_API.G_RET_STS_ERROR;
1324 x_msg_data := 'AHL_RM_INVALID_INSP_TYPE';
1325 END IF;
1326
1327 CLOSE get_rec_from_id;
1328 RETURN;
1329
1330 END IF;
1331
1332 IF ( p_qa_inspection_type_desc IS NOT NULL AND
1333 p_qa_inspection_type_desc <> FND_API.G_MISS_CHAR ) THEN
1334
1335 OPEN get_rec_from_value( p_qa_inspection_type_desc );
1336
1337 LOOP
1338 FETCH get_rec_from_value INTO
1339 l_qa_inspection_type;
1340
1341 EXIT WHEN get_rec_from_value%NOTFOUND;
1342
1343 IF ( l_qa_inspection_type = p_x_qa_inspection_type ) THEN
1344 CLOSE get_rec_from_value;
1345 RETURN;
1346 END IF;
1347
1348 END LOOP;
1349
1350 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
1351 x_return_status := FND_API.G_RET_STS_ERROR;
1352 x_msg_data := 'AHL_RM_INVALID_INSP_TYPE';
1353 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
1354 p_x_qa_inspection_type := l_qa_inspection_type;
1355 ELSE
1356 x_return_status := FND_API.G_RET_STS_ERROR;
1357 x_msg_data := 'AHL_RM_TOO_MANY_INSP_TYPES';
1358 END IF;
1359
1360 CLOSE get_rec_from_value;
1361 RETURN;
1362
1363 END IF;
1364
1365 END validate_qa_inspection_type;
1366
1367 -- Procedure to validate QA Plan
1368 PROCEDURE validate_qa_plan
1369 (
1370 x_return_status OUT NOCOPY VARCHAR2,
1371 x_msg_data OUT NOCOPY VARCHAR2,
1372 p_qa_plan IN QA_PLANS_VAL_V.name%TYPE,
1373 p_x_qa_plan_id IN OUT NOCOPY QA_PLANS_VAL_V.plan_id%TYPE
1374 )
1375 IS
1376
1377 l_qa_plan_id QA_PLANS_VAL_V.plan_id%TYPE;
1378
1379 CURSOR get_rec_from_value ( c_qa_plan QA_PLANS_VAL_V.name%TYPE )
1380 IS
1381 SELECT DISTINCT plan_id
1382 FROM QA_PLANS_VAL_V
1383 WHERE name = c_qa_plan;
1384
1385 CURSOR get_rec_from_id ( c_qa_plan_id QA_PLANS_VAL_V.plan_id%TYPE )
1386 IS
1387 SELECT DISTINCT plan_id
1388 FROM QA_PLANS_VAL_V
1389 WHERE plan_id = c_qa_plan_id;
1390
1391 BEGIN
1392 x_return_status := FND_API.G_RET_STS_SUCCESS;
1393
1394 IF ( ( p_qa_plan IS NULL OR
1395 p_qa_plan = FND_API.G_MISS_CHAR ) AND
1396 ( p_x_qa_plan_id IS NULL OR
1397 p_x_qa_plan_id = FND_API.G_MISS_NUM ) ) THEN
1398 x_return_status := FND_API.G_RET_STS_ERROR;
1399 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
1400 RETURN;
1401 END IF;
1402
1403 IF ( ( p_qa_plan IS NULL OR
1404 p_qa_plan = FND_API.G_MISS_CHAR ) AND
1405 ( p_x_qa_plan_id IS NOT NULL AND
1406 p_x_qa_plan_id <> FND_API.G_MISS_NUM ) ) THEN
1407
1408 OPEN get_rec_from_id( p_x_qa_plan_id );
1409
1410 FETCH get_rec_from_id INTO
1411 l_qa_plan_id;
1412
1413 IF get_rec_from_id%NOTFOUND THEN
1414 x_return_status := FND_API.G_RET_STS_ERROR;
1415 x_msg_data := 'AHL_RM_INVALID_QA_PLAN';
1416 END IF;
1417
1418 CLOSE get_rec_from_id;
1419 RETURN;
1420
1421 END IF;
1422
1423 IF ( p_qa_plan IS NOT NULL AND
1424 p_qa_plan <> FND_API.G_MISS_CHAR ) THEN
1425
1426 OPEN get_rec_from_value( p_qa_plan );
1427
1428 LOOP
1429 FETCH get_rec_from_value INTO
1430 l_qa_plan_id;
1431
1432 EXIT WHEN get_rec_from_value%NOTFOUND;
1433
1434 IF ( l_qa_plan_id = p_x_qa_plan_id ) THEN
1435 CLOSE get_rec_from_value;
1436 RETURN;
1437 END IF;
1438
1439 END LOOP;
1440
1441 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
1442 x_return_status := FND_API.G_RET_STS_ERROR;
1443 x_msg_data := 'AHL_RM_INVALID_QA_PLAN';
1444 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
1445 p_x_qa_plan_id := l_qa_plan_id;
1446 ELSE
1447 x_return_status := FND_API.G_RET_STS_ERROR;
1448 x_msg_data := 'AHL_RM_TOO_MANY_QA_PLANS';
1449 END IF;
1450
1451 CLOSE get_rec_from_value;
1452 RETURN;
1453
1454 END IF;
1455
1456 END validate_qa_plan;
1457
1458 -- Procedure to valiadate the Item Group
1459 PROCEDURE validate_item_group
1460 (
1461 x_return_status OUT NOCOPY VARCHAR2,
1462 x_msg_data OUT NOCOPY VARCHAR2,
1463 p_association_type IN VARCHAR2,
1464 p_item_group_name IN AHL_ITEM_GROUPS_VL.name%TYPE,
1465 p_x_item_group_id IN OUT NOCOPY AHL_ITEM_GROUPS_VL.item_group_id%TYPE
1466 )
1467 IS
1468
1469 l_item_group_id AHL_ITEM_GROUPS_VL.item_group_id%TYPE;
1470
1471 CURSOR get_rec_from_value ( c_item_group_name AHL_ITEM_GROUPS_VL.name%TYPE , c_association_type VARCHAR2)
1472 IS
1473 SELECT DISTINCT item_group_id
1474 FROM AHL_ITEM_GROUPS_VL
1475 WHERE name = c_item_group_name
1476 AND DECODE(c_association_type,'DISPOSITION',TYPE_CODE,'NON-TRACKED')='NON-TRACKED';
1477
1478 CURSOR get_rec_from_id ( c_item_group_id AHL_ITEM_GROUPS_VL.item_group_id%TYPE , c_association_type VARCHAR2)
1479 IS
1480 SELECT DISTINCT item_group_id
1481 FROM AHL_ITEM_GROUPS_VL
1482 WHERE item_group_id = c_item_group_id
1483 AND DECODE(c_association_type,'DISPOSITION',TYPE_CODE,'NON-TRACKED')='NON-TRACKED';
1484
1485 BEGIN
1486 x_return_status := FND_API.G_RET_STS_SUCCESS;
1487
1488 IF ( ( p_item_group_name IS NULL OR
1489 p_item_group_name = FND_API.G_MISS_CHAR ) AND
1490 ( p_x_item_group_id IS NULL OR
1491 p_x_item_group_id = FND_API.G_MISS_NUM ) ) THEN
1492 x_return_status := FND_API.G_RET_STS_ERROR;
1493 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
1494 RETURN;
1495 END IF;
1496
1497 IF ( ( p_item_group_name IS NULL OR
1498 p_item_group_name = FND_API.G_MISS_CHAR ) AND
1499 ( p_x_item_group_id IS NOT NULL AND
1500 p_x_item_group_id <> FND_API.G_MISS_NUM ) ) THEN
1501
1502 OPEN get_rec_from_id( p_x_item_group_id,p_association_type );
1503
1504 FETCH get_rec_from_id INTO
1505 l_item_group_id;
1506
1507 IF get_rec_from_id%NOTFOUND THEN
1508 x_return_status := FND_API.G_RET_STS_ERROR;
1509 x_msg_data := 'AHL_RM_INVALID_ITEM_GRP';
1510 END IF;
1511
1512 CLOSE get_rec_from_id;
1513 RETURN;
1514
1515 END IF;
1516
1517 IF ( p_item_group_name IS NOT NULL AND
1518 p_item_group_name <> FND_API.G_MISS_CHAR ) THEN
1519
1520 OPEN get_rec_from_value( p_item_group_name,p_association_type );
1521
1522 LOOP
1523 FETCH get_rec_from_value INTO
1524 l_item_group_id;
1525
1526 EXIT WHEN get_rec_from_value%NOTFOUND;
1527
1528 IF ( l_item_group_id = p_x_item_group_id ) THEN
1529 CLOSE get_rec_from_value;
1530 RETURN;
1531 END IF;
1532
1533 END LOOP;
1534
1535 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
1536 x_return_status := FND_API.G_RET_STS_ERROR;
1537 x_msg_data := 'AHL_RM_INVALID_ITEM_GRP';
1538 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
1539 p_x_item_group_id := l_item_group_id;
1540 ELSE
1541 x_return_status := FND_API.G_RET_STS_ERROR;
1542 x_msg_data := 'AHL_RM_TOO_MANY_ITEM_GRPS';
1543 END IF;
1544
1545 CLOSE get_rec_from_value;
1546 RETURN;
1547
1548 END IF;
1549
1550 END validate_item_group;
1551
1552 -- Procedure to valiadate the Item Composition
1553 PROCEDURE validate_item_comp
1554 (
1555 x_return_status OUT NOCOPY VARCHAR2,
1556 x_msg_data OUT NOCOPY VARCHAR2,
1557 p_x_item_comp_detail_id IN OUT NOCOPY NUMBER
1558 )
1559 IS
1560
1561 l_item_comp_id NUMBER;
1562
1563 CURSOR get_rec_from_id ( c_item_comp_detail_id NUMBER )
1564 IS
1565 SELECT DISTINCT ICD.item_comp_detail_id
1566 FROM AHL_ITEM_COMP_DETAILS ICD
1567 , AHL_ITEM_COMPOSITIONS CD
1568 WHERE ICD.item_comp_detail_id = c_item_comp_detail_id
1569 AND CD.APPROVAL_STATUS_CODE ='COMPLETE'
1570 AND CD.item_composition_id = ICD.item_composition_id
1571 AND nvl(trunc(CD.EFFECTIVE_END_DATE),trunc(sysdate+1)) > trunc(sysdate)
1572 AND nvl(trunc(ICD.EFFECTIVE_END_DATE),trunc(sysdate+1)) > trunc(sysdate);
1573
1574 BEGIN
1575 x_return_status := FND_API.G_RET_STS_SUCCESS;
1576
1577 IF ( p_x_item_comp_detail_id IS NULL OR
1578 p_x_item_comp_detail_id = FND_API.G_MISS_NUM ) THEN
1579 x_return_status := FND_API.G_RET_STS_ERROR;
1580 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
1581 RETURN;
1582 END IF;
1583
1584 OPEN get_rec_from_id( p_x_item_comp_detail_id );
1585
1586 FETCH get_rec_from_id INTO
1587 l_item_comp_id;
1588
1589 IF get_rec_from_id%NOTFOUND THEN
1590 x_return_status := FND_API.G_RET_STS_ERROR;
1591 x_msg_data := 'AHL_RM_INVALID_ITEM_COMP';
1592 ELSIF ( get_rec_from_id%ROWCOUNT = 1 ) THEN
1593 p_x_item_comp_detail_id := l_item_comp_id;
1594 ELSE
1595 x_return_status := FND_API.G_RET_STS_ERROR;
1596 x_msg_data := 'AHL_RM_TOO_MANY_ITEM_COMPS';
1597 END IF;
1598
1599 CLOSE get_rec_from_id;
1600 RETURN;
1601
1602
1603 END validate_item_comp;
1604
1605
1606 -- Procedure to valiadate the Positin Path
1607 PROCEDURE validate_position_path
1608 (
1609 x_return_status OUT NOCOPY VARCHAR2,
1610 x_msg_data OUT NOCOPY VARCHAR2,
1611 p_position_path IN VARCHAR2,
1612 p_x_position_path_id IN OUT NOCOPY NUMBER
1613 )
1614 IS
1615
1616 l_position_path_id NUMBER;
1617 /*
1618 CURSOR get_rec_from_value ( c_position_path VARCHAR2 )
1619 IS
1620 SELECT DISTINCT path_position_id
1621 FROM AHL_MC_PATH_POSITIONS
1622 WHERE position_ref_code = c_position_path;
1623 */
1624 CURSOR get_rec_from_id ( c_position_path_id NUMBER )
1625 IS
1626 --AMSRINIV. Bug 4913429. Replacing below commented query with a new query for better performance
1627 SELECT DISTINCT
1628 mcp.path_position_id
1629 FROM
1630 ahl_mc_relationships mcr,
1631 ahl_mc_headers_b mch,
1632 ahl_mc_path_position_nodes mcp,
1633 ahl_route_effectivities re
1634 WHERE
1635 re.mc_id IS NOT NULL AND
1636 re.mc_id = mch.mc_id AND
1637 mch.mc_header_id =mcr.mc_header_id AND
1638 mch.mc_id = mcp.mc_id AND
1639 mch.version_number = NVL(mcp.version_number, mch.version_number) AND
1640 mcr.position_key = mcp.position_key AND
1641 mcp.SEQUENCE = ( SELECT MAX(SEQUENCE) FROM ahl_mc_path_position_nodes WHERE path_position_id = NVL(c_position_path_id,-1) ) AND
1642 mcp.path_position_id = NVL(c_position_path_id,-1);
1643
1644 /*SELECT DISTINCT
1645 mcp.path_position_id
1646 FROM
1647 ahl_mc_relationships mcr
1648 , ahl_mc_headers_b mch
1649 , ahl_mc_path_position_nodes mcp
1650 , AHL_ROUTE_EFFECTIVITIES_V re
1651 WHERE
1652 mch.mc_header_id = mcr.mc_header_id
1653 and mch.mc_id = mcp.mc_id
1654 and re.MC_ID = mch.MC_ID
1655 and mch.version_number = nvl(mcp.version_number, mch.version_number)
1656 and mcr.position_key = mcp.position_key
1657 and mcp.sequence = (select max(sequence) from ahl_mc_path_position_nodes where path_position_id = nvl(c_position_path_id,-1))
1658 and mcp.path_position_id = nvl(c_position_path_id,-1);*/
1659
1660
1661 BEGIN
1662 x_return_status := FND_API.G_RET_STS_SUCCESS;
1663
1664 IF ( ( p_position_path IS NULL OR
1665 p_position_path = FND_API.G_MISS_CHAR ) AND
1666 ( p_x_position_path_id IS NULL OR
1667 p_x_position_path_id = FND_API.G_MISS_NUM ) ) THEN
1668 x_return_status := FND_API.G_RET_STS_ERROR;
1669 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
1670 RETURN;
1671 END IF;
1672
1673 IF ( ( p_position_path IS NULL OR
1674 p_position_path = FND_API.G_MISS_CHAR ) AND
1675 ( p_x_position_path_id IS NOT NULL AND
1676 p_x_position_path_id <> FND_API.G_MISS_NUM ) ) THEN
1677
1678 OPEN get_rec_from_id( p_x_position_path_id );
1679
1680 FETCH get_rec_from_id INTO
1681 l_position_path_id;
1682
1683 IF get_rec_from_id%NOTFOUND THEN
1684 x_return_status := FND_API.G_RET_STS_ERROR;
1685 x_msg_data := 'AHL_RM_INVALID_POS_PATH';
1686 END IF;
1687
1688 CLOSE get_rec_from_id;
1689 RETURN;
1690
1691 END IF;
1692 /*
1693 IF ( p_position_path IS NOT NULL AND
1694 p_position_path <> FND_API.G_MISS_CHAR ) THEN
1695
1696 OPEN get_rec_from_value( p_position_path );
1697
1698 LOOP
1699 FETCH get_rec_from_value INTO
1700 l_position_path_id;
1701
1702 EXIT WHEN get_rec_from_value%NOTFOUND;
1703
1704 IF ( l_position_path_id = p_x_position_path_id ) THEN
1705 CLOSE get_rec_from_value;
1706 RETURN;
1707 END IF;
1708
1709 END LOOP;
1710
1711 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
1712 x_return_status := FND_API.G_RET_STS_ERROR;
1713 x_msg_data := 'AHL_RM_INVALID_POS_PATH';
1714 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
1715 p_x_position_path_id := l_position_path_id;
1716 ELSE
1717 x_return_status := FND_API.G_RET_STS_ERROR;
1718 x_msg_data := 'AHL_RM_TOO_MANY_POS_PATHS';
1719 END IF;
1720
1721 CLOSE get_rec_from_value;
1722 RETURN;
1723
1724 END IF;
1725 */
1726 END validate_position_path;
1727
1728
1729 -- Procedure to valiadate the master_configuration
1730 PROCEDURE validate_master_configuration
1731 (
1732 x_return_status OUT NOCOPY VARCHAR2,
1733 x_msg_data OUT NOCOPY VARCHAR2,
1734 p_mc_name IN AHL_MC_HEADERS_V.NAME%TYPE,
1735 p_x_mc_id IN OUT NOCOPY AHL_MC_HEADERS_V.MC_ID%TYPE,
1736 p_mc_revision_number IN AHL_MC_HEADERS_V.REVISION%TYPE ,
1737 p_x_mc_header_id IN OUT NOCOPY AHL_MC_HEADERS_V.MC_HEADER_ID%TYPE
1738 )
1739 IS
1740
1741 l_mc_header_id AHL_MC_HEADERS_V.MC_HEADER_ID%TYPE;
1742 l_mc_id AHL_MC_HEADERS_V.MC_ID%TYPE;
1743
1744 CURSOR get_rec_from_value ( c_mc_name AHL_MC_HEADERS_V.NAME%TYPE )
1745 IS
1746 --AMSRINIV. Bug 4913429. Replacing below commented query with a new query for better performance
1747 SELECT DISTINCT mc_id
1748 FROM AHL_MC_HEADERS_B
1749 WHERE upper(name) = upper(c_mc_name)
1750 AND CONFIG_STATUS_CODE='COMPLETE';
1751
1752 /*SELECT DISTINCT mc_id
1753 FROM AHL_MC_HEADERS_V
1754 WHERE upper(name) = upper(c_mc_name)
1755 AND CONFIG_STATUS_CODE='COMPLETE';*/
1756
1757 CURSOR get_rec_from_value1 ( c_mc_name AHL_MC_HEADERS_V.NAME%TYPE , c_mc_revision_number AHL_MC_HEADERS_V.REVISION%TYPE)
1758 IS
1759 --AMSRINIV. Bug 4913429. Replacing below commented query with a new query for better performance
1760 SELECT DISTINCT mc_header_id, mc_id
1761 FROM AHL_MC_HEADERS_B
1762 WHERE upper(name) = upper(c_mc_name)
1763 AND upper(revision)=upper(c_mc_revision_number)
1764 AND CONFIG_STATUS_CODE='COMPLETE';
1765 /*
1766 SELECT DISTINCT mc_header_id,
1767 mc_id
1768 FROM AHL_MC_HEADERS_V
1769 WHERE upper(name) = upper(c_mc_name)
1770 AND upper(revision)=upper(c_mc_revision_number)
1771 AND CONFIG_STATUS_CODE='COMPLETE';*/
1772
1773 /*
1774 CURSOR get_rec_from_id1 ( c_mc_header_id AHL_MC_HEADERS_V.MC_HEADER_ID%TYPE )
1775 IS
1776 SELECT DISTINCT mc_header_id
1777 FROM AHL_MC_HEADERS_V
1778 WHERE mc_header_id = c_mc_header_id
1779 AND CONFIG_STATUS_CODE='COMPLETE'
1780 ;
1781
1782 CURSOR get_rec_from_id ( c_mc_id AHL_MC_HEADERS_V.MC_ID%TYPE )
1783 IS
1784 SELECT DISTINCT mc_id
1785 FROM AHL_MC_HEADERS_V
1786 WHERE mc_id = c_mc_id
1787 AND CONFIG_STATUS_CODE='COMPLETE'
1788 ;
1789 */
1790 /*
1791 CURSOR get_rec_status_id ( c_mc_id AHL_MC_HEADERS_V.MC_ID%TYPE )
1792 IS
1793 SELECT DISTINCT mc_id
1794 FROM AHL_MC_HEADERS_V
1795 WHERE mc_id = c_mc_id
1796 AND CONFIG_STATUS_CODE='COMPLETE'
1797 ;
1798
1799 CURSOR get_rec_status_id1 ( c_mc_header_id AHL_MC_HEADERS_V.MC_HEADER_ID%TYPE )
1800 IS
1801 SELECT DISTINCT mc_header_id
1802 FROM AHL_MC_HEADERS_V
1803 WHERE mc_header_id = c_mc_header_id
1804 AND CONFIG_STATUS_CODE='COMPLETE'
1805 ;
1806 */
1807 BEGIN
1808 x_return_status := FND_API.G_RET_STS_SUCCESS;
1809
1810 IF ( ( p_mc_name IS NULL OR
1811 p_mc_name = FND_API.G_MISS_CHAR ) AND
1812 ( p_x_mc_id IS NULL OR
1813 p_x_mc_id = FND_API.G_MISS_NUM ) ) THEN
1814 x_return_status := FND_API.G_RET_STS_ERROR;
1815 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
1816 RETURN;
1817 END IF;
1818 /*
1819 IF ( ( p_mc_name IS NULL OR
1820 p_mc_name = FND_API.G_MISS_CHAR ) AND
1821 ( p_x_mc_id IS NOT NULL AND
1822 p_x_mc_id <> FND_API.G_MISS_NUM ) ) THEN
1823
1824 OPEN get_rec_from_id( p_x_mc_id );
1825
1826 LOOP
1827 FETCH get_rec_from_id INTO
1828 l_mc_id;
1829 EXIT WHEN get_rec_from_id%NOTFOUND;
1830
1831 IF ( l_mc_id = p_x_mc_id ) THEN
1832 CLOSE get_rec_from_id;
1833 RETURN;
1834 END IF;
1835 END LOOP;
1836
1837 IF ( get_rec_from_id%ROWCOUNT = 0 ) THEN
1838 x_return_status := FND_API.G_RET_STS_ERROR;
1839 x_msg_data := 'AHL_RM_INVALID_MC';
1840 END IF;
1841
1842 CLOSE get_rec_from_id;
1843 RETURN;
1844
1845 END IF;
1846 */
1847
1848 IF ( (p_mc_name IS NOT NULL AND p_mc_name <> FND_API.G_MISS_CHAR)
1849 AND (p_mc_revision_number IS NOT NULL AND p_mc_revision_number <> FND_API.G_MISS_CHAR)
1850 )
1851 THEN
1852
1853 OPEN get_rec_from_value1( p_mc_name, p_mc_revision_number);
1854
1855 LOOP
1856 FETCH get_rec_from_value1 INTO
1857 l_mc_header_id,l_mc_id;
1858
1859 EXIT WHEN get_rec_from_value1%NOTFOUND;
1860
1861 IF ( l_mc_id = p_x_mc_id AND l_mc_header_id = p_x_mc_header_id ) THEN
1862 CLOSE get_rec_from_value1;
1863 RETURN;
1864 END IF;
1865
1866 END LOOP;
1867
1868 IF ( get_rec_from_value1%ROWCOUNT = 0 ) THEN
1869 x_return_status := FND_API.G_RET_STS_ERROR;
1870 x_msg_data := 'AHL_RM_INVALID_MC';
1871 ELSE
1872 p_x_mc_id := l_mc_id ;
1873 p_x_mc_header_id := l_mc_header_id;
1874 /*
1875 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
1876 p_x_mc_id := l_mc_id;
1877 ELSE
1878 x_return_status := FND_API.G_RET_STS_ERROR;
1879 x_msg_data := 'AHL_RM_TOO_MANY_MCS';
1880 */
1881 END IF;
1882
1883 CLOSE get_rec_from_value1;
1884 RETURN;
1885 END IF ;
1886 /*
1887 OPEN get_rec_status_id( p_x_mc_header_id );
1888
1889 FETCH get_rec_status_id INTO
1890 l_mc_header_id;
1891
1892 IF get_rec_status_id%NOTFOUND THEN
1893 x_return_status := FND_API.G_RET_STS_ERROR;
1894 x_msg_data := 'AHL_RM_INCOMPLETE_MC';
1895 END IF;
1896
1897 CLOSE get_rec_status_id;
1898 RETURN;
1899
1900 END IF;
1901 */
1902
1903 IF ( p_mc_name IS NOT NULL AND
1904 p_mc_name <> FND_API.G_MISS_CHAR ) THEN
1905
1906 OPEN get_rec_from_value( p_mc_name );
1907
1908 LOOP
1909 FETCH get_rec_from_value INTO
1910 l_mc_id;
1911
1912 EXIT WHEN get_rec_from_value%NOTFOUND;
1913
1914 IF ( l_mc_id = p_x_mc_id ) THEN
1915 CLOSE get_rec_from_value;
1916 RETURN;
1917 END IF;
1918
1919 END LOOP;
1920
1921 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
1922 x_return_status := FND_API.G_RET_STS_ERROR;
1923 x_msg_data := 'AHL_RM_INVALID_MC';
1924 ELSE
1925 p_x_mc_id := l_mc_id;
1926 /*
1927 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
1928 p_x_mc_id := l_mc_id;
1929 ELSE
1930 x_return_status := FND_API.G_RET_STS_ERROR;
1931 x_msg_data := 'AHL_RM_TOO_MANY_MCS';
1932 */
1933 END IF;
1934
1935 CLOSE get_rec_from_value;
1936 RETURN;
1937
1938 END IF ;
1939
1940 END validate_master_configuration;
1941
1942
1943 -- Procedure to validate UOM
1944 PROCEDURE validate_uom
1945 (
1946 x_return_status OUT NOCOPY VARCHAR2,
1947 x_msg_data OUT NOCOPY VARCHAR2,
1948 p_uom IN MTL_UNITS_OF_MEASURE_VL.unit_of_measure%TYPE,
1949 p_x_uom_code IN OUT NOCOPY MTL_UNITS_OF_MEASURE_VL.uom_code%TYPE
1950 )
1951 IS
1952
1953 l_uom_code MTL_UNITS_OF_MEASURE_VL.uom_code%TYPE;
1954
1955 CURSOR get_rec_from_value ( c_uom MTL_UNITS_OF_MEASURE_VL.unit_of_measure%TYPE )
1956 IS
1957 SELECT DISTINCT uom_code
1958 FROM MTL_UNITS_OF_MEASURE_VL
1959 WHERE upper(unit_of_measure) = upper(c_uom);
1960
1961 CURSOR get_rec_from_id ( c_uom_code MTL_UNITS_OF_MEASURE_VL.uom_code%TYPE )
1962 IS
1963 SELECT DISTINCT uom_code
1964 FROM MTL_UNITS_OF_MEASURE_VL
1965 WHERE upper(uom_code) = upper(c_uom_code);
1966
1967 BEGIN
1968 x_return_status := FND_API.G_RET_STS_SUCCESS;
1969
1970 IF ( ( p_uom IS NULL OR
1971 p_uom = FND_API.G_MISS_CHAR ) AND
1972 ( p_x_uom_code IS NULL OR
1973 p_x_uom_code = FND_API.G_MISS_CHAR ) ) THEN
1974 x_return_status := FND_API.G_RET_STS_ERROR;
1975 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
1976 RETURN;
1977 END IF;
1978
1979 IF ( ( p_uom IS NULL OR
1980 p_uom = FND_API.G_MISS_CHAR ) AND
1981 ( p_x_uom_code IS NOT NULL AND
1982 p_x_uom_code <> FND_API.G_MISS_CHAR ) ) THEN
1983
1984 OPEN get_rec_from_id( p_x_uom_code );
1985
1986 FETCH get_rec_from_id INTO
1987 l_uom_code;
1988
1989 IF get_rec_from_id%NOTFOUND THEN
1990 x_return_status := FND_API.G_RET_STS_ERROR;
1991 x_msg_data := 'AHL_RM_INVALID_UOM';
1992 END IF;
1993
1994 CLOSE get_rec_from_id;
1995 RETURN;
1996
1997 END IF;
1998
1999 IF ( p_uom IS NOT NULL AND
2000 p_uom <> FND_API.G_MISS_CHAR ) THEN
2001
2002 OPEN get_rec_from_value( p_uom );
2003
2004 LOOP
2005 FETCH get_rec_from_value INTO
2006 l_uom_code;
2007
2008 EXIT WHEN get_rec_from_value%NOTFOUND;
2009
2010 IF ( l_uom_code = p_x_uom_code ) THEN
2011 CLOSE get_rec_from_value;
2012 RETURN;
2013 END IF;
2014
2015 END LOOP;
2016
2017 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
2018 x_return_status := FND_API.G_RET_STS_ERROR;
2019 x_msg_data := 'AHL_RM_INVALID_UOM';
2020 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
2021 p_x_uom_code := l_uom_code;
2022 ELSE
2023 x_return_status := FND_API.G_RET_STS_ERROR;
2024 x_msg_data := 'AHL_RM_TOO_MANY_UOMS';
2025 END IF;
2026
2027 CLOSE get_rec_from_value;
2028 RETURN;
2029
2030 END IF;
2031
2032 END validate_uom;
2033
2034 -- Procedure to validate whether a UOM is valid for an Item / Item Group
2035 PROCEDURE validate_item_uom
2036 (
2037 x_return_status OUT NOCOPY VARCHAR2,
2038 x_msg_data OUT NOCOPY VARCHAR2,
2039 p_item_group_id IN AHL_ITEM_GROUPS_VL.item_group_id%TYPE,
2040 p_inventory_item_id IN MTL_SYSTEM_ITEMS.inventory_item_id%TYPE,
2041 p_inventory_org_id IN MTL_SYSTEM_ITEMS.organization_id%TYPE,
2042 p_uom_code IN MTL_UNITS_OF_MEASURE_VL.uom_code%TYPE
2043 )
2044 IS
2045
2046 l_dummy VARCHAR2(1);
2047
2048 CURSOR get_uom_for_item ( c_uom_code MTL_UNITS_OF_MEASURE_VL.uom_code%TYPE,
2049 c_inventory_item_id MTL_SYSTEM_ITEMS.inventory_item_id%TYPE,
2050 c_inventory_org_id MTL_SYSTEM_ITEMS.organization_id%TYPE )
2051 IS
2052 SELECT 'X'
2053 FROM AHL_ITEM_CLASS_UOM_V
2054 WHERE uom_code = c_uom_code
2055 AND inventory_item_id = c_inventory_item_id
2056 AND inventory_org_id = c_inventory_org_id;
2057
2058 CURSOR get_uom_for_item_group ( c_uom_code MTL_UNITS_OF_MEASURE_VL.uom_code%TYPE,
2059 c_item_group_id AHL_ITEM_GROUPS_VL.item_group_id%TYPE )
2060 IS
2061 SELECT 'X'
2062 FROM AHL_ITEM_CLASS_UOM_V UOM, AHL_ITEM_ASSOCIATIONS_B ASSOC
2063 WHERE UOM.uom_code = c_uom_code
2064 AND UOM.inventory_item_id = ASSOC.inventory_item_id
2065 AND UOM.inventory_org_id = ASSOC.inventory_org_id
2066 AND ASSOC.item_group_id = c_item_group_id;
2067 BEGIN
2068 x_return_status := FND_API.G_RET_STS_SUCCESS;
2069
2070 IF ( p_uom_code IS NULL OR
2071 p_uom_code = FND_API.G_MISS_CHAR ) THEN
2072 x_return_status := FND_API.G_RET_STS_ERROR;
2073 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
2074 RETURN;
2075 END IF;
2076
2077 IF ( ( p_inventory_item_id IS NULL OR
2078 p_inventory_item_id = FND_API.G_MISS_NUM ) AND
2079 ( p_inventory_org_id IS NULL OR
2080 p_inventory_org_id = FND_API.G_MISS_NUM ) AND
2081 ( p_item_group_id IS NULL OR
2082 p_item_group_id = FND_API.G_MISS_NUM ) ) THEN
2083 x_return_status := FND_API.G_RET_STS_ERROR;
2084 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
2085 RETURN;
2086 END IF;
2087
2088 IF ( p_item_group_id IS NOT NULL AND
2089 p_item_group_id <> FND_API.G_MISS_NUM ) THEN
2090
2091 OPEN get_uom_for_item_group( p_uom_code , p_item_group_id );
2092
2093 FETCH get_uom_for_item_group INTO
2094 l_dummy;
2095
2096 IF get_uom_for_item_group%NOTFOUND THEN
2097 x_return_status := FND_API.G_RET_STS_ERROR;
2098 x_msg_data := 'AHL_RM_INVALID_UOM_ITEM_GRP';
2099 END IF;
2100
2101 CLOSE get_uom_for_item_group;
2102 RETURN;
2103
2104 END IF;
2105
2106 IF ( p_inventory_item_id IS NOT NULL AND
2107 p_inventory_item_id <> FND_API.G_MISS_NUM AND
2108 p_inventory_org_id IS NOT NULL AND
2109 p_inventory_org_id <> FND_API.G_MISS_NUM ) THEN
2110
2111 OPEN get_uom_for_item( p_uom_code, p_inventory_item_id, p_inventory_org_id );
2112
2113 FETCH get_uom_for_item INTO
2114 l_dummy;
2115
2116 IF get_uom_for_item%NOTFOUND THEN
2117 x_return_status := FND_API.G_RET_STS_ERROR;
2118 x_msg_data := 'AHL_RM_INVALID_UOM_ITEM';
2119 END IF;
2120
2121 CLOSE get_uom_for_item;
2122 RETURN;
2123
2124 END IF;
2125
2126 END validate_item_uom;
2127
2128 -- Procedure to validate Product Type and Zone association
2129 PROCEDURE validate_pt_zone
2130 (
2131 x_return_status OUT NOCOPY VARCHAR2,
2132 x_msg_data OUT NOCOPY VARCHAR2,
2133 p_product_type_code IN AHL_PRODTYPE_ZONES.product_type_code%TYPE,
2134 p_zone_code IN AHL_PRODTYPE_ZONES.zone_code%TYPE
2135 )
2136 IS
2137
2138 CURSOR check_pt_zone ( c_product_type_code AHL_PRODTYPE_ZONES.product_type_code%TYPE,
2139 c_zone_code AHL_PRODTYPE_ZONES.zone_code%TYPE )
2140 IS
2141 SELECT 'X'
2142 FROM AHL_PRODTYPE_ZONES
2143 WHERE product_type_code = c_product_type_code
2144 AND zone_code = c_zone_code
2145 AND sub_zone_code IS NULL;
2146
2147 l_dummy VARCHAR2(1);
2148
2149 BEGIN
2150 x_return_status := FND_API.G_RET_STS_SUCCESS;
2151
2152 IF ( p_product_type_code IS NULL OR
2153 p_product_type_code = FND_API.G_MISS_CHAR OR
2154 p_zone_code IS NULL OR
2155 p_zone_code = FND_API.G_MISS_CHAR ) THEN
2156 x_return_status := FND_API.G_RET_STS_ERROR;
2157 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
2158 RETURN;
2159 END IF;
2160
2161 OPEN check_pt_zone( p_product_type_code , p_zone_code );
2162
2163 FETCH check_pt_zone INTO
2164 l_dummy;
2165
2166 IF check_pt_zone%NOTFOUND THEN
2167 x_return_status := FND_API.G_RET_STS_ERROR;
2168 x_msg_data := 'AHL_RM_INVALID_PT_ZONE';
2169 END IF;
2170
2171 CLOSE check_pt_zone;
2172 RETURN;
2173
2174 END validate_pt_zone;
2175
2176 -- Procedure to validate Product Type, Zone and Sub Zone association
2177 PROCEDURE validate_pt_zone_subzone
2178 (
2179 x_return_status OUT NOCOPY VARCHAR2,
2180 x_msg_data OUT NOCOPY VARCHAR2,
2181 p_product_type_code IN AHL_PRODTYPE_ZONES.product_type_code%TYPE ,
2182 p_zone_code IN AHL_PRODTYPE_ZONES.zone_code%TYPE,
2183 p_sub_zone_code IN AHL_PRODTYPE_ZONES.sub_zone_code%TYPE
2184 )
2185 IS
2186
2187 CURSOR check_pt_zone_subzone ( c_product_type_code AHL_PRODTYPE_ZONES.product_type_code%TYPE, c_zone_code AHL_PRODTYPE_ZONES.zone_code%TYPE, c_sub_zone_code AHL_PRODTYPE_ZONES.sub_zone_code%TYPE )
2188 IS
2189 SELECT 'X'
2190 FROM AHL_PRODTYPE_ZONES
2191 WHERE product_type_code = c_product_type_code
2192 AND zone_code = c_zone_code
2193 AND sub_zone_code = c_sub_zone_code;
2194
2195 l_dummy VARCHAR2(1);
2196
2197 BEGIN
2198 x_return_status := FND_API.G_RET_STS_SUCCESS;
2199
2200 IF ( p_product_type_code IS NULL OR
2201 p_product_type_code = FND_API.G_MISS_CHAR OR
2202 p_zone_code IS NULL OR
2203 p_zone_code = FND_API.G_MISS_CHAR OR
2204 p_sub_zone_code IS NULL OR
2205 p_sub_zone_code = FND_API.G_MISS_CHAR ) THEN
2206 x_return_status := FND_API.G_RET_STS_ERROR;
2207 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
2208 RETURN;
2209 END IF;
2210
2211 OPEN check_pt_zone_subzone( p_product_type_code, p_zone_code, p_sub_zone_code );
2212
2213 FETCH check_pt_zone_subzone INTO
2214 l_dummy;
2215
2216 IF check_pt_zone_subzone%NOTFOUND THEN
2217 x_return_status := FND_API.G_RET_STS_ERROR;
2218 x_msg_data := 'AHL_RM_INVALID_PT_ZONE_SUBZONE';
2219 END IF;
2220
2221 CLOSE check_pt_zone_subzone;
2222 RETURN;
2223
2224 END validate_pt_zone_subzone;
2225
2226 -- Procedure to validate MFG Lookups
2227 PROCEDURE validate_mfg_lookup
2228 (
2229 x_return_status OUT NOCOPY VARCHAR2,
2230 x_msg_data OUT NOCOPY VARCHAR2,
2231 p_lookup_type IN MFG_LOOKUPS.lookup_type%TYPE,
2232 p_lookup_meaning IN MFG_LOOKUPS.meaning%TYPE,
2233 p_x_lookup_code IN OUT NOCOPY MFG_LOOKUPS.lookup_code%TYPE
2234 )
2235 IS
2236
2237 l_lookup_code MFG_LOOKUPS.lookup_code%TYPE;
2238 l_sp_cursor_use VARCHAR2(5) := 'N';
2239
2240 CURSOR get_rec_from_value_sp ( c_lookup_type MFG_LOOKUPS.lookup_type%TYPE,
2241 c_lookup_meaning MFG_LOOKUPS.meaning%TYPE ,
2242 c_lookup_code_1 NUMBER ,
2243 c_lookup_code_2 NUMBER)
2244 IS
2245 SELECT DISTINCT lookup_code
2246 FROM MFG_LOOKUPS
2247 WHERE lookup_type = c_lookup_type
2248 AND UPPER(meaning) = UPPER(c_lookup_meaning)
2249 AND SYSDATE BETWEEN NVL( start_date_active, SYSDATE ) AND
2250 NVL( end_date_active, SYSDATE )
2251 AND LOOKUP_CODE IN (c_lookup_code_1, c_lookup_code_2);
2252
2253 CURSOR get_rec_from_id_sp ( c_lookup_type FND_LOOKUPS.lookup_type%TYPE,
2254 c_lookup_code FND_LOOKUPS.lookup_code%TYPE ,
2255 c_lookup_code_1 NUMBER ,
2256 c_lookup_code_2 NUMBER)
2257 IS
2258 SELECT DISTINCT lookup_code
2259 FROM MFG_LOOKUPS
2260 WHERE lookup_type = c_lookup_type
2261 AND lookup_code = c_lookup_code
2262 AND SYSDATE BETWEEN NVL( start_date_active, SYSDATE ) AND
2263 NVL( end_date_active, SYSDATE )
2264 AND LOOKUP_CODE IN (c_lookup_code_1, c_lookup_code_2);
2265
2266 CURSOR get_rec_from_value ( c_lookup_type MFG_LOOKUPS.lookup_type%TYPE,
2267 c_lookup_meaning MFG_LOOKUPS.meaning%TYPE )
2268 IS
2269 SELECT DISTINCT lookup_code
2270 FROM MFG_LOOKUPS
2271 WHERE lookup_type = c_lookup_type
2272 AND UPPER(meaning) = UPPER(c_lookup_meaning)
2273 AND SYSDATE BETWEEN NVL( start_date_active, SYSDATE ) AND
2274 NVL( end_date_active, SYSDATE );
2275
2276 CURSOR get_rec_from_id ( c_lookup_type FND_LOOKUPS.lookup_type%TYPE,
2277 c_lookup_code FND_LOOKUPS.lookup_code%TYPE )
2278 IS
2279 SELECT DISTINCT lookup_code
2280 FROM MFG_LOOKUPS
2281 WHERE lookup_type = c_lookup_type
2282 AND lookup_code = c_lookup_code
2283 AND SYSDATE BETWEEN NVL( start_date_active, SYSDATE ) AND
2284 NVL( end_date_active, SYSDATE );
2285
2286 BEGIN
2287 x_return_status := FND_API.G_RET_STS_SUCCESS;
2288
2289 IF ( p_lookup_type IS NULL OR
2290 p_lookup_type = FND_API.G_MISS_CHAR ) THEN
2291 x_return_status := FND_API.G_RET_STS_ERROR;
2292 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
2293 RETURN;
2294 END IF;
2295
2296 IF ( ( p_lookup_meaning IS NULL OR
2297 p_lookup_meaning = FND_API.G_MISS_CHAR ) AND
2298 ( p_x_lookup_code IS NULL OR
2299 p_x_lookup_code = FND_API.G_MISS_NUM ) ) THEN
2300 x_return_status := FND_API.G_RET_STS_ERROR;
2301 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
2302 RETURN;
2303 END IF;
2304
2305 IF ( ( p_lookup_meaning IS NULL OR
2306 p_lookup_meaning = FND_API.G_MISS_CHAR ) AND
2307 ( p_x_lookup_code IS NOT NULL AND
2308 p_x_lookup_code <> FND_API.G_MISS_NUM ) ) THEN
2309
2310 IF ( p_lookup_type = 'CST_BASIS' OR p_lookup_type = 'BOM_RESOURCE_SCHEDULE_TYPE' OR p_lookup_type = 'SYS_YES_NO')
2311 THEN
2312 OPEN get_rec_from_id_sp( p_lookup_type, p_x_lookup_code , 1 , 2 );
2313 l_sp_cursor_use := 'Y';
2314 ELSIF ( p_lookup_type = 'BOM_AUTOCHARGE_TYPE')
2315 THEN
2316 OPEN get_rec_from_id_sp( p_lookup_type, p_x_lookup_code , 2 , -1);
2317 l_sp_cursor_use := 'Y';
2318 ELSE
2319 OPEN get_rec_from_id( p_lookup_type, p_x_lookup_code );
2320 END IF;
2321
2322 IF(l_sp_cursor_use = 'Y')
2323 THEN
2324 FETCH get_rec_from_id_sp INTO
2325 l_lookup_code;
2326
2327 IF get_rec_from_id_sp%NOTFOUND THEN
2328 x_return_status := FND_API.G_RET_STS_ERROR;
2329 x_msg_data := 'AHL_COM_INVALID_MFG_LOOKUP';
2330 END IF;
2331
2332 CLOSE get_rec_from_id_sp;
2333 ELSE
2334 FETCH get_rec_from_id INTO
2335 l_lookup_code;
2336
2337 IF get_rec_from_id%NOTFOUND THEN
2338 x_return_status := FND_API.G_RET_STS_ERROR;
2339 x_msg_data := 'AHL_COM_INVALID_MFG_LOOKUP';
2340 END IF;
2341
2342 CLOSE get_rec_from_id;
2343 END IF;
2344
2345 RETURN;
2346
2347 END IF;
2348
2349 IF ( p_lookup_meaning IS NOT NULL AND
2350 p_lookup_meaning <> FND_API.G_MISS_CHAR ) THEN
2351
2352 IF ( p_lookup_type = 'CST_BASIS' OR p_lookup_type = 'BOM_RESOURCE_SCHEDULE_TYPE' OR p_lookup_type = 'SYS_YES_NO')
2353 THEN
2354 OPEN get_rec_from_value_sp( p_lookup_type, p_lookup_meaning , 1 , 2 );
2355 l_sp_cursor_use := 'Y';
2356 ELSIF ( p_lookup_type = 'BOM_AUTOCHARGE_TYPE')
2357 THEN
2358 OPEN get_rec_from_value_sp( p_lookup_type, p_lookup_meaning, 2 , -1 );
2359 l_sp_cursor_use := 'Y';
2360 ELSE
2361 OPEN get_rec_from_value( p_lookup_type, p_lookup_meaning );
2362 END IF;
2363
2364 IF( l_sp_cursor_use = 'Y')
2365 THEN
2366
2367 LOOP
2368 FETCH get_rec_from_value_sp INTO
2369 l_lookup_code;
2370
2371 EXIT WHEN get_rec_from_value_sp%NOTFOUND;
2372
2373 IF ( l_lookup_code = p_x_lookup_code ) THEN
2374 CLOSE get_rec_from_value_sp;
2375 RETURN;
2376 END IF;
2377
2378 END LOOP;
2379
2380 IF ( get_rec_from_value_sp%ROWCOUNT = 0 ) THEN
2381 x_return_status := FND_API.G_RET_STS_ERROR;
2382 x_msg_data := 'AHL_COM_INVALID_MFG_LOOKUP';
2383 ELSIF ( get_rec_from_value_sp%ROWCOUNT = 1 ) THEN
2384 p_x_lookup_code := l_lookup_code;
2385 ELSE
2386 x_return_status := FND_API.G_RET_STS_ERROR;
2387 x_msg_data := 'AHL_COM_TOO_MANY_MFG_LOOKUPS';
2388 END IF;
2389
2390 CLOSE get_rec_from_value_sp;
2391 RETURN;
2392 ELSE
2393 LOOP
2394 FETCH get_rec_from_value INTO
2395 l_lookup_code;
2396
2397 EXIT WHEN get_rec_from_value%NOTFOUND;
2398
2399 IF ( l_lookup_code = p_x_lookup_code ) THEN
2400 CLOSE get_rec_from_value;
2401 RETURN;
2402 END IF;
2403
2404 END LOOP;
2405
2406 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
2407 x_return_status := FND_API.G_RET_STS_ERROR;
2408 x_msg_data := 'AHL_COM_INVALID_MFG_LOOKUP';
2409 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
2410 p_x_lookup_code := l_lookup_code;
2411 ELSE
2412 x_return_status := FND_API.G_RET_STS_ERROR;
2413 x_msg_data := 'AHL_COM_TOO_MANY_MFG_LOOKUPS';
2414 END IF;
2415
2416 CLOSE get_rec_from_value;
2417 RETURN;
2418
2419 END IF;
2420 END IF;
2421
2422 END validate_mfg_lookup;
2423
2424 -- Procedure to validate ASO Resource
2425 PROCEDURE validate_aso_resource
2426 (
2427 x_return_status OUT NOCOPY VARCHAR2,
2428 x_msg_data OUT NOCOPY VARCHAR2,
2429 p_aso_resource_name IN AHL_RESOURCES.name%TYPE,
2430 p_x_aso_resource_id IN OUT NOCOPY AHL_RESOURCES.resource_id%TYPE
2431 )
2432 IS
2433
2434 l_aso_resource_id AHL_RESOURCES.resource_id%TYPE;
2435
2436 CURSOR get_rec_from_value ( c_aso_resource_name AHL_RESOURCES.name%TYPE )
2437 IS
2438 SELECT DISTINCT resource_id
2439 FROM AHL_RESOURCES
2440 WHERE UPPER(TRIM(name)) = UPPER(TRIM(c_aso_resource_name));
2441
2442 CURSOR get_rec_from_id ( c_aso_resource_id AHL_RESOURCES.resource_id%TYPE )
2443 IS
2444 SELECT DISTINCT resource_id
2445 FROM AHL_RESOURCES
2446 WHERE resource_id = c_aso_resource_id;
2447
2448 BEGIN
2449 x_return_status := FND_API.G_RET_STS_SUCCESS;
2450
2451 IF ( ( p_aso_resource_name IS NULL OR
2452 p_aso_resource_name = FND_API.G_MISS_CHAR ) AND
2453 ( p_x_aso_resource_id IS NULL OR
2454 p_x_aso_resource_id = FND_API.G_MISS_NUM ) ) THEN
2455 x_return_status := FND_API.G_RET_STS_ERROR;
2456 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
2457 RETURN;
2458 END IF;
2459
2460 IF ( ( p_aso_resource_name IS NULL OR
2461 p_aso_resource_name = FND_API.G_MISS_CHAR ) AND
2462 ( p_x_aso_resource_id IS NOT NULL AND
2463 p_x_aso_resource_id <> FND_API.G_MISS_NUM ) ) THEN
2464
2465 OPEN get_rec_from_id( p_x_aso_resource_id );
2466
2467 FETCH get_rec_from_id INTO
2468 l_aso_resource_id;
2469
2470 IF get_rec_from_id%NOTFOUND THEN
2471 x_return_status := FND_API.G_RET_STS_ERROR;
2472 x_msg_data := 'AHL_RM_INVALID_ASO_RESOURCE';
2473 END IF;
2474
2475 CLOSE get_rec_from_id;
2476 RETURN;
2477
2478 END IF;
2479
2480 IF ( p_aso_resource_name IS NOT NULL AND
2481 p_aso_resource_name <> FND_API.G_MISS_CHAR ) THEN
2482
2483 OPEN get_rec_from_value( p_aso_resource_name );
2484
2485 LOOP
2486 FETCH get_rec_from_value INTO
2487 l_aso_resource_id;
2488
2489 EXIT WHEN get_rec_from_value%NOTFOUND;
2490
2491 IF ( l_aso_resource_id = p_x_aso_resource_id ) THEN
2492 CLOSE get_rec_from_value;
2493 RETURN;
2494 END IF;
2495
2496 END LOOP;
2497
2498 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
2499 x_return_status := FND_API.G_RET_STS_ERROR;
2500 x_msg_data := 'AHL_RM_INVALID_ASO_RESOURCE';
2501 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
2502 p_x_aso_resource_id := l_aso_resource_id;
2503 ELSE
2504 x_return_status := FND_API.G_RET_STS_ERROR;
2505 x_msg_data := 'AHL_RM_TOO_MANY_ASO_RESOURCES';
2506 END IF;
2507
2508 CLOSE get_rec_from_value;
2509 RETURN;
2510
2511 END IF;
2512
2513 END validate_aso_resource;
2514
2515 -- Procedure to validate ASO Resource
2516 PROCEDURE validate_bom_resource
2517 (
2518 x_return_status OUT NOCOPY VARCHAR2,
2519 x_msg_data OUT NOCOPY VARCHAR2,
2520 p_bom_resource_code IN BOM_RESOURCES.resource_code%TYPE,
2521 p_x_bom_resource_id IN OUT NOCOPY BOM_RESOURCES.resource_id%TYPE,
2522 p_x_bom_org_id IN OUT NOCOPY BOM_RESOURCES.organization_id%TYPE
2523 )
2524 IS
2525
2526 l_bom_resource_id BOM_RESOURCES.resource_id%TYPE;
2527 l_bom_org_id BOM_RESOURCES.organization_id%TYPE;
2528
2529 CURSOR get_rec_from_value ( c_bom_resource_code BOM_RESOURCES.resource_code%TYPE )
2530 IS
2531 SELECT DISTINCT resource_id,
2532 organization_id
2533 FROM BOM_RESOURCES
2534 WHERE resource_code = c_bom_resource_code;
2535
2536 CURSOR get_rec_from_id ( c_bom_resource_id BOM_RESOURCES.resource_id%TYPE,
2537 c_bom_org_id BOM_RESOURCES.organization_id%TYPE )
2538 IS
2539 SELECT DISTINCT resource_id,
2540 organization_id
2541 FROM BOM_RESOURCES
2542 WHERE resource_id = c_bom_resource_id
2543 AND organization_id = c_bom_org_id;
2544
2545 BEGIN
2546 x_return_status := FND_API.G_RET_STS_SUCCESS;
2547
2548 IF ( ( p_bom_resource_code IS NULL OR
2549 p_bom_resource_code = FND_API.G_MISS_CHAR ) AND
2550 ( p_x_bom_resource_id IS NULL OR
2551 p_x_bom_resource_id = FND_API.G_MISS_NUM ) AND
2552 ( p_x_bom_org_id IS NULL OR
2553 p_x_bom_org_id = FND_API.G_MISS_NUM ) ) THEN
2554 x_return_status := FND_API.G_RET_STS_ERROR;
2555 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
2556 RETURN;
2557 END IF;
2558
2559 IF ( ( p_bom_resource_code IS NULL OR
2560 p_bom_resource_code = FND_API.G_MISS_CHAR ) AND
2561 ( p_x_bom_resource_id IS NOT NULL AND
2562 p_x_bom_resource_id <> FND_API.G_MISS_NUM AND
2563 p_x_bom_org_id IS NOT NULL AND
2564 p_x_bom_org_id <> FND_API.G_MISS_NUM ) ) THEN
2565
2566 OPEN get_rec_from_id( p_x_bom_resource_id , p_x_bom_org_id );
2567
2568 FETCH get_rec_from_id INTO
2569 l_bom_resource_id,
2570 l_bom_org_id;
2571
2572 IF ( get_rec_from_id%NOTFOUND ) THEN
2573 x_return_status := FND_API.G_RET_STS_ERROR;
2574 x_msg_data := 'AHL_RM_INVALID_BOM_RESOURCE';
2575 END IF;
2576
2577 CLOSE get_rec_from_id;
2578 RETURN;
2579
2580 END IF;
2581
2582 IF ( p_bom_resource_code IS NOT NULL AND
2583 p_bom_resource_code <> FND_API.G_MISS_CHAR ) THEN
2584
2585 OPEN get_rec_from_value( p_bom_resource_code );
2586
2587 LOOP
2588 FETCH get_rec_from_value INTO
2589 l_bom_resource_id,
2590 l_bom_org_id;
2591
2592 EXIT WHEN get_rec_from_value%NOTFOUND;
2593
2594 IF ( l_bom_resource_id = p_x_bom_resource_id AND
2595 l_bom_org_id = p_x_bom_org_id ) THEN
2596 CLOSE get_rec_from_value;
2597 RETURN;
2598 END IF;
2599
2600 END LOOP;
2601
2602 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
2603 x_return_status := FND_API.G_RET_STS_ERROR;
2604 x_msg_data := 'AHL_RM_INVALID_BOM_RESOURCE';
2605 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
2606 p_x_bom_resource_id := l_bom_resource_id;
2607 p_x_bom_org_id := l_bom_org_id;
2608 ELSE
2609 x_return_status := FND_API.G_RET_STS_ERROR;
2610 x_msg_data := 'AHL_RM_TOO_MANY_BOM_RESOURCES';
2611 END IF;
2612
2613 CLOSE get_rec_from_value;
2614 RETURN;
2615
2616 END IF;
2617
2618 END validate_bom_resource;
2619
2620 -- pdoki ER 7436910 Begin.
2621 -- Procedure to validate ASO Resource
2622 PROCEDURE validate_bom_res_dep
2623 (
2624 x_return_status OUT NOCOPY VARCHAR2,
2625 x_msg_data OUT NOCOPY VARCHAR2,
2626 p_bom_resource_id IN NUMBER,
2627 p_bom_org_id IN BOM_DEPARTMENTS.organization_id%TYPE,
2628 p_bom_department_name IN BOM_DEPARTMENTS.DESCRIPTION%TYPE,
2629 p_x_bom_department_id IN OUT NOCOPY BOM_DEPARTMENTS.department_id%TYPE
2630 )
2631 IS
2632
2633 l_bom_department_id number;
2634
2635 CURSOR get_rec_from_value ( c_bom_department_name BOM_DEPARTMENTS.DESCRIPTION%TYPE , c_bom_resource_id NUMBER, c_bom_org_id NUMBER)
2636 IS
2637 select distinct DEPT.department_id
2638 from bom_departments DEPT, BOM_DEPARTMENT_RESOURCES DEPT_RES, BOM_RESOURCES RES
2639 where DEPT_RES.department_id = DEPT.department_id
2640 and DEPT.description = c_bom_department_name
2641 and DEPT_RES.resource_id = c_bom_resource_id
2642 and DEPT.organization_id = c_bom_org_id;
2643
2644 BEGIN
2645 x_return_status := FND_API.G_RET_STS_SUCCESS;
2646
2647 IF ( p_bom_department_name IS NULL OR
2648 p_bom_department_name = FND_API.G_MISS_CHAR ) THEN
2649 x_return_status := FND_API.G_RET_STS_ERROR;
2650 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
2651 RETURN;
2652 END IF;
2653
2654 IF ( p_bom_department_name IS NOT NULL AND
2655 p_bom_department_name <> FND_API.G_MISS_CHAR ) THEN
2656
2657 OPEN get_rec_from_value( p_bom_department_name, p_bom_resource_id, p_bom_org_id );
2658
2659 LOOP
2660 FETCH get_rec_from_value INTO
2661 l_bom_department_id;
2662
2663 EXIT WHEN get_rec_from_value%NOTFOUND;
2664
2665 IF ( l_bom_department_id = p_x_bom_department_id ) THEN
2666 CLOSE get_rec_from_value;
2667 RETURN;
2668 END IF;
2669
2670 END LOOP;
2671
2672 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
2673 x_return_status := FND_API.G_RET_STS_ERROR;
2674 x_msg_data := 'AHL_RM_INVALID_BOM_RES_DEPT';
2675 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
2676 p_x_bom_department_id := l_bom_department_id;
2677 ELSE
2678 x_return_status := FND_API.G_RET_STS_ERROR;
2679 x_msg_data := 'AHL_RM_TOO_MANY_BOM_RES_DEPTS';
2680 END IF;
2681
2682 CLOSE get_rec_from_value;
2683 RETURN;
2684
2685 END IF;
2686
2687 END validate_bom_res_dep;
2688 -- pdoki ER 7436910 End.
2689
2690 -- Procedure to validate Resource Costing - Activity
2691 PROCEDURE validate_activity
2692 (
2693 x_return_status OUT NOCOPY VARCHAR2,
2694 x_msg_data OUT NOCOPY VARCHAR2,
2695 p_activity IN CST_ACTIVITIES.activity%TYPE,
2696 p_x_activity_id IN OUT NOCOPY CST_ACTIVITIES.activity_id%TYPE
2697 )
2698 IS
2699
2700 l_activity_id CST_ACTIVITIES.activity_id%TYPE;
2701
2702 CURSOR get_rec_from_value ( c_activity CST_ACTIVITIES.activity%TYPE )
2703 IS
2704 SELECT DISTINCT activity_id
2705 FROM CST_ACTIVITIES
2706 WHERE activity = c_activity;
2707
2708 CURSOR get_rec_from_id ( c_activity_id CST_ACTIVITIES.activity_id%TYPE )
2709 IS
2710 SELECT DISTINCT activity_id
2711 FROM CST_ACTIVITIES
2712 WHERE activity_id = c_activity_id;
2713
2714 BEGIN
2715 x_return_status := FND_API.G_RET_STS_SUCCESS;
2716
2717 IF ( ( p_activity IS NULL OR
2718 p_activity = FND_API.G_MISS_CHAR ) AND
2719 ( p_x_activity_id IS NULL OR
2720 p_x_activity_id = FND_API.G_MISS_NUM ) ) THEN
2721 x_return_status := FND_API.G_RET_STS_ERROR;
2722 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
2723 RETURN;
2724 END IF;
2725
2726 IF ( ( p_activity IS NULL OR
2727 p_activity = FND_API.G_MISS_CHAR ) AND
2728 ( p_x_activity_id IS NOT NULL AND
2729 p_x_activity_id <> FND_API.G_MISS_NUM ) ) THEN
2730
2731 OPEN get_rec_from_id( p_x_activity_id );
2732
2733 FETCH get_rec_from_id INTO
2734 l_activity_id;
2735
2736 IF get_rec_from_id%NOTFOUND THEN
2737 x_return_status := FND_API.G_RET_STS_ERROR;
2738 x_msg_data := 'AHL_RM_INVALID_ACTIVITY';
2739 END IF;
2740
2741 CLOSE get_rec_from_id;
2742 RETURN;
2743
2744 END IF;
2745
2746 IF ( p_activity IS NOT NULL AND
2747 p_activity <> FND_API.G_MISS_CHAR ) THEN
2748
2749 OPEN get_rec_from_value( p_activity );
2750
2751 LOOP
2752 FETCH get_rec_from_value INTO
2753 l_activity_id;
2754
2755 EXIT WHEN get_rec_from_value%NOTFOUND;
2756
2757 IF ( l_activity_id = p_x_activity_id ) THEN
2758 CLOSE get_rec_from_value;
2759 RETURN;
2760 END IF;
2761
2762 END LOOP;
2763
2764 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
2765 x_return_status := FND_API.G_RET_STS_ERROR;
2766 x_msg_data := 'AHL_RM_INVALID_ACTIVITY';
2767 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
2768 p_x_activity_id := l_activity_id;
2769 ELSE
2770 x_return_status := FND_API.G_RET_STS_ERROR;
2771 x_msg_data := 'AHL_RM_TOO_MANY_ACTIVITIES';
2772 END IF;
2773
2774 CLOSE get_rec_from_value;
2775 RETURN;
2776
2777 END IF;
2778
2779 END validate_activity;
2780
2781 -- Procedure to validate Skill Type
2782 PROCEDURE validate_skill_type
2783 (
2784 x_return_status OUT NOCOPY VARCHAR2,
2785 x_msg_data OUT NOCOPY VARCHAR2,
2786 p_business_group_id IN PER_COMPETENCES.business_group_id%TYPE,
2787 p_skill_name IN PER_COMPETENCES.name%TYPE,
2788 p_x_skill_competence_id IN OUT NOCOPY PER_COMPETENCES.competence_id%TYPE
2789 )
2790 IS
2791
2792 l_skill_competence_id PER_COMPETENCES.competence_id%TYPE;
2793
2794 CURSOR get_rec_from_value ( c_skill_name PER_COMPETENCES.name%TYPE,
2795 c_business_group_id PER_COMPETENCES.business_group_id%TYPE )
2796 IS
2797 SELECT DISTINCT competence_id
2798 FROM PER_COMPETENCES
2799 WHERE name = c_skill_name
2800 AND business_group_id = c_business_group_id;
2801
2802 CURSOR get_rec_from_id ( c_skill_competence_id PER_COMPETENCES.competence_id%TYPE,
2803 c_business_group_id PER_COMPETENCES.business_group_id%TYPE )
2804 IS
2805 SELECT DISTINCT competence_id
2806 FROM PER_COMPETENCES
2807 WHERE competence_id = c_skill_competence_id
2808 AND business_group_id = c_business_group_id;
2809
2810 BEGIN
2811 x_return_status := FND_API.G_RET_STS_SUCCESS;
2812
2813 IF ( p_business_group_id IS NULL OR
2814 p_business_group_id = FND_API.G_MISS_NUM ) THEN
2815 x_return_status := FND_API.G_RET_STS_ERROR;
2816 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
2817 RETURN;
2818 END IF;
2819
2820 IF ( ( p_skill_name IS NULL OR
2821 p_skill_name = FND_API.G_MISS_CHAR ) AND
2822 ( p_x_skill_competence_id IS NULL OR
2823 p_x_skill_competence_id = FND_API.G_MISS_NUM ) ) THEN
2824 x_return_status := FND_API.G_RET_STS_ERROR;
2825 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
2826 RETURN;
2827 END IF;
2828
2829 IF ( ( p_skill_name IS NULL OR
2830 p_skill_name = FND_API.G_MISS_CHAR ) AND
2831 ( p_x_skill_competence_id IS NOT NULL AND
2832 p_x_skill_competence_id <> FND_API.G_MISS_NUM ) ) THEN
2833
2834 OPEN get_rec_from_id( p_x_skill_competence_id, p_business_group_id );
2835
2836 FETCH get_rec_from_id INTO
2837 l_skill_competence_id;
2838
2839 IF get_rec_from_id%NOTFOUND THEN
2840 x_return_status := FND_API.G_RET_STS_ERROR;
2841 x_msg_data := 'AHL_RM_INVALID_SKILL_TYPE';
2842 END IF;
2843
2844 CLOSE get_rec_from_id;
2845 RETURN;
2846
2847 END IF;
2848
2849 IF ( p_skill_name IS NOT NULL AND
2850 p_skill_name <> FND_API.G_MISS_CHAR ) THEN
2851
2852 OPEN get_rec_from_value( p_skill_name, p_business_group_id );
2853
2854 LOOP
2855 FETCH get_rec_from_value INTO
2856 l_skill_competence_id;
2857
2858 EXIT WHEN get_rec_from_value%NOTFOUND;
2859
2860 IF ( l_skill_competence_id = p_x_skill_competence_id ) THEN
2861 CLOSE get_rec_from_value;
2862 RETURN;
2863 END IF;
2864
2865 END LOOP;
2866
2867 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
2868 x_return_status := FND_API.G_RET_STS_ERROR;
2869 x_msg_data := 'AHL_RM_INVALID_SKILL_TYPE';
2870 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
2871 p_x_skill_competence_id := l_skill_competence_id;
2872 ELSE
2873 x_return_status := FND_API.G_RET_STS_ERROR;
2874 x_msg_data := 'AHL_RM_TOO_MANY_SKILL_TYPES';
2875 END IF;
2876
2877 CLOSE get_rec_from_value;
2878 RETURN;
2879
2880 END IF;
2881
2882 END validate_skill_type;
2883
2884 -- Procedure to validate Skill Level
2885 PROCEDURE validate_skill_level
2886 (
2887 x_return_status OUT NOCOPY VARCHAR2,
2888 x_msg_data OUT NOCOPY VARCHAR2,
2889 p_business_group_id IN PER_RATING_LEVELS.business_group_id%TYPE,
2890 p_skill_competence_id IN PER_RATING_LEVELS.competence_id%TYPE,
2891 p_skill_level_desc IN VARCHAR2,
2892 p_x_rating_level_id IN OUT NOCOPY PER_RATING_LEVELS.rating_level_id%TYPE
2893 )
2894 IS
2895
2896 l_rating_level_id PER_RATING_LEVELS.rating_level_id%TYPE;
2897
2898 CURSOR get_rec_from_value ( c_skill_level_desc PER_RATING_LEVELS.name%TYPE,
2899 c_skill_competence_id PER_RATING_LEVELS.competence_id%TYPE,
2900 c_business_group_id PER_RATING_LEVELS.business_group_id%TYPE )
2901 IS
2902 SELECT DISTINCT rating_level_id
2903 FROM PER_RATING_LEVELS
2904 WHERE TO_CHAR( step_value ) || '-' || name = c_skill_level_desc
2905 AND competence_id = c_skill_competence_id
2906 AND business_group_id = c_business_group_id;
2907
2908 CURSOR get_rec_from_id ( c_rating_level_id PER_RATING_LEVELS.rating_level_id%TYPE,
2909 c_skill_competence_id PER_RATING_LEVELS.competence_id%TYPE,
2910 c_business_group_id PER_RATING_LEVELS.business_group_id%TYPE )
2911 IS
2912 SELECT DISTINCT rating_level_id
2913 FROM PER_RATING_LEVELS
2914 WHERE rating_level_id = c_rating_level_id
2915 AND competence_id = c_skill_competence_id
2916 AND business_group_id = c_business_group_id;
2917
2918 BEGIN
2919 x_return_status := FND_API.G_RET_STS_SUCCESS;
2920
2921 IF ( p_business_group_id IS NULL OR
2922 p_business_group_id = FND_API.G_MISS_NUM OR
2923 p_skill_competence_id IS NULL OR
2924 p_skill_competence_id = FND_API.G_MISS_NUM ) THEN
2925 x_return_status := FND_API.G_RET_STS_ERROR;
2926 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
2927 RETURN;
2928 END IF;
2929
2930 IF ( ( p_skill_level_desc IS NULL OR
2931 p_skill_level_desc = FND_API.G_MISS_CHAR ) AND
2932 ( p_x_rating_level_id IS NULL OR
2933 p_x_rating_level_id = FND_API.G_MISS_NUM ) ) THEN
2934 x_return_status := FND_API.G_RET_STS_ERROR;
2935 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
2936 RETURN;
2937 END IF;
2938
2939 IF ( ( p_skill_level_desc IS NULL OR
2940 p_skill_level_desc = FND_API.G_MISS_CHAR ) AND
2941 ( p_x_rating_level_id IS NOT NULL AND
2942 p_x_rating_level_id <> FND_API.G_MISS_NUM ) ) THEN
2943
2944 OPEN get_rec_from_id( p_x_rating_level_id, p_skill_competence_id, p_business_group_id );
2945
2946 FETCH get_rec_from_id INTO
2947 l_rating_level_id;
2948
2949 IF get_rec_from_id%NOTFOUND THEN
2950 x_return_status := FND_API.G_RET_STS_ERROR;
2951 x_msg_data := 'AHL_RM_INVALID_SKILL_LEVEL';
2952 END IF;
2953
2954 CLOSE get_rec_from_id;
2955 RETURN;
2956
2957 END IF;
2958
2959 IF ( p_skill_level_desc IS NOT NULL AND
2960 p_skill_level_desc <> FND_API.G_MISS_CHAR ) THEN
2961
2962 OPEN get_rec_from_value( p_skill_level_desc, p_skill_competence_id, p_business_group_id );
2963
2964 LOOP
2965 FETCH get_rec_from_value INTO
2966 l_rating_level_id;
2967
2968 EXIT WHEN get_rec_from_value%NOTFOUND;
2969
2970 IF ( l_rating_level_id = p_x_rating_level_id ) THEN
2971 CLOSE get_rec_from_value;
2972 RETURN;
2973 END IF;
2974
2975 END LOOP;
2976
2977 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
2978 x_return_status := FND_API.G_RET_STS_ERROR;
2979 x_msg_data := 'AHL_RM_INVALID_SKILL_LEVEL';
2980 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
2981 p_x_rating_level_id := l_rating_level_id;
2982 ELSE
2983 x_return_status := FND_API.G_RET_STS_ERROR;
2984 x_msg_data := 'AHL_RM_TOO_MANY_SKILL_LEVELS';
2985 END IF;
2986
2987 CLOSE get_rec_from_value;
2988 RETURN;
2989
2990 END IF;
2991
2992 END validate_skill_level;
2993
2994 -- Procedure to validate Qualification Type
2995 PROCEDURE validate_qualification_type
2996 (
2997 x_return_status OUT NOCOPY VARCHAR2,
2998 x_msg_data OUT NOCOPY VARCHAR2,
2999 p_qualification_type IN PER_QUALIFICATION_TYPES.name%TYPE,
3000 p_x_qualification_type_id IN OUT NOCOPY PER_QUALIFICATION_TYPES.qualification_type_id%TYPE
3001 )
3002 IS
3003
3004 l_qualification_type_id PER_QUALIFICATION_TYPES.qualification_type_id%TYPE;
3005
3006 CURSOR get_rec_from_value ( c_qualification_type PER_QUALIFICATION_TYPES.name%TYPE )
3007 IS
3008 SELECT DISTINCT qualification_type_id
3009 FROM PER_QUALIFICATION_TYPES
3010 WHERE name = c_qualification_type;
3011
3012 CURSOR get_rec_from_id ( c_qualification_type_id PER_QUALIFICATION_TYPES.qualification_type_id%TYPE )
3013 IS
3014 SELECT DISTINCT qualification_type_id
3015 FROM PER_QUALIFICATION_TYPES
3016 WHERE qualification_type_id = c_qualification_type_id;
3017
3018 BEGIN
3019 x_return_status := FND_API.G_RET_STS_SUCCESS;
3020
3021 IF ( ( p_qualification_type IS NULL OR
3022 p_qualification_type = FND_API.G_MISS_CHAR ) AND
3023 ( p_x_qualification_type_id IS NULL OR
3024 p_x_qualification_type_id = FND_API.G_MISS_NUM ) ) THEN
3025 x_return_status := FND_API.G_RET_STS_ERROR;
3026 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
3027 RETURN;
3028 END IF;
3029
3030 IF ( ( p_qualification_type IS NULL OR
3031 p_qualification_type = FND_API.G_MISS_CHAR ) AND
3032 ( p_x_qualification_type_id IS NOT NULL AND
3033 p_x_qualification_type_id <> FND_API.G_MISS_NUM ) ) THEN
3034
3035 OPEN get_rec_from_id( p_x_qualification_type_id );
3036
3037 FETCH get_rec_from_id INTO
3038 l_qualification_type_id;
3039
3040 IF get_rec_from_id%NOTFOUND THEN
3041 x_return_status := FND_API.G_RET_STS_ERROR;
3042 x_msg_data := 'AHL_RM_INVALID_QUAL_TYPE';
3043 END IF;
3044
3045 CLOSE get_rec_from_id;
3046 RETURN;
3047
3048 END IF;
3049
3050 IF ( p_qualification_type IS NOT NULL AND
3051 p_qualification_type <> FND_API.G_MISS_CHAR ) THEN
3052
3053 OPEN get_rec_from_value( p_qualification_type );
3054
3055 LOOP
3056 FETCH get_rec_from_value INTO
3057 l_qualification_type_id;
3058
3059 EXIT WHEN get_rec_from_value%NOTFOUND;
3060
3061 IF ( l_qualification_type_id = p_x_qualification_type_id ) THEN
3062 CLOSE get_rec_from_value;
3063 RETURN;
3064 END IF;
3065
3066 END LOOP;
3067
3068 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
3069 x_return_status := FND_API.G_RET_STS_ERROR;
3070 x_msg_data := 'AHL_RM_INVALID_QUAL_TYPE';
3071 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
3072 p_x_qualification_type_id := l_qualification_type_id;
3073 ELSE
3074 x_return_status := FND_API.G_RET_STS_ERROR;
3075 x_msg_data := 'AHL_RM_TOO_MANY_QUAL_TYPES';
3076 END IF;
3077
3078 CLOSE get_rec_from_value;
3079 RETURN;
3080
3081 END IF;
3082
3083 END validate_qualification_type;
3084
3085 -- Procedure to validate whether the Route is in Updatable status
3086 PROCEDURE validate_route_status
3087 (
3088 p_route_id IN NUMBER,
3089 x_msg_data OUT NOCOPY VARCHAR2,
3090 x_return_status OUT NOCOPY VARCHAR2
3091 )
3092 IS
3093
3094 CURSOR get_route_status(c_route_id number) IS
3095 SELECT revision_status_code
3096 FROM ahl_routes_app_v
3097 WHERE route_id = c_route_id;
3098 l_route_status_code VARCHAR2(30);
3099
3100 BEGIN
3101 x_return_status := FND_API.G_RET_STS_SUCCESS;
3102
3103 IF ( p_route_id IS NULL OR
3104 p_route_id= FND_API.G_MISS_NUM ) THEN
3105 x_return_status := FND_API.G_RET_STS_ERROR;
3106 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
3107 RETURN;
3108 END IF;
3109
3110 OPEN get_route_status( p_route_id );
3111
3112 FETCH get_route_status INTO l_route_status_code;
3113
3114 IF get_route_status%NOTFOUND THEN
3115 x_return_status := FND_API.G_RET_STS_ERROR;
3116 x_msg_data := 'AHL_RM_INVALID_ROUTE';
3117 CLOSE get_route_status;
3118 RETURN;
3119 END IF;
3120
3121 IF ( l_route_status_code <> 'DRAFT' AND
3122 l_route_status_code <> 'APPROVAL_REJECTED' ) THEN
3123 x_return_status := FND_API.G_RET_STS_ERROR;
3124 x_msg_data := 'AHL_RM_INVALID_ROUTE_STATUS';
3125 CLOSE get_route_status;
3126 RETURN;
3127 END IF;
3128
3129 CLOSE get_route_status;
3130
3131 END validate_route_status;
3132
3133
3134 -- Procedure to validate Effectivity of the Route
3135 PROCEDURE validate_efct_status
3136 (
3137 p_efct_id IN NUMBER,
3138 x_msg_data OUT NOCOPY VARCHAR2,
3139 x_return_status OUT NOCOPY VARCHAR2
3140 )
3141 IS
3142
3143 CURSOR get_efct_status(c_efct_id number) IS
3144 --AMSRINIV. Bug 4913429. Replacing below commented query with a new query for better performance
3145 SELECT ROUTE_ID
3146 FROM AHL_ROUTE_EFFECTIVITIES
3147 WHERE ROUTE_EFFECTIVITY_ID = c_efct_id;
3148 /*SELECT ROUTE_ID
3149 FROM AHL_ROUTE_EFFECTIVITIES_V
3150 WHERE ROUTE_EFFECTIVITY_ID = c_efct_id;*/
3151
3152 CURSOR get_route_status(c_route_id number) IS
3153 --AMSRINIV. Bug 4913429. Replacing below commented query with a new query for better performance
3154 SELECT ROUTE_NO
3155 FROM AHL_ROUTES_B
3156 WHERE ROUTE_ID = c_route_id AND
3157 APPLICATION_USG_CODE=rtrim(ltrim(fnd_profile.value('AHL_APPLN_USAGE')));
3158 /* SELECT ROUTE_NO
3159 FROM AHL_ROUTES_V
3160 WHERE ROUTE_ID = c_route_id;*/
3161
3162 l_route_id NUMBER;
3163 l_route_no VARCHAR2(30);
3164
3165 BEGIN
3166 x_return_status := FND_API.G_RET_STS_SUCCESS;
3167
3168 IF ( p_efct_id IS NULL OR
3169 p_efct_id= FND_API.G_MISS_NUM ) THEN
3170 x_return_status := FND_API.G_RET_STS_ERROR;
3171 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
3172 RETURN;
3173 END IF;
3174
3175 OPEN get_efct_status( p_efct_id );
3176
3177 FETCH get_efct_status INTO l_route_id;
3178
3179 IF get_efct_status%NOTFOUND THEN
3180 x_return_status := FND_API.G_RET_STS_ERROR;
3181 x_msg_data := 'AHL_RM_INVALID_efct';
3182 CLOSE get_efct_status;
3183 RETURN;
3184 END IF;
3185
3186 OPEN get_route_status( l_route_id );
3187
3188 FETCH get_route_status INTO l_route_no;
3189
3190 IF get_route_status%NOTFOUND THEN
3191 x_return_status := FND_API.G_RET_STS_ERROR;
3192 x_msg_data := 'AHL_RM_INVALID_efct_ROUTE';
3193 CLOSE get_route_status;
3194 RETURN;
3195 END IF;
3196
3197 CLOSE get_efct_status;
3198
3199 END validate_efct_status;
3200
3201
3202 -- Procedure to validate whether the Operation is in Updatable status
3203 PROCEDURE validate_operation_status
3204 (
3205 p_operation_id IN NUMBER,
3206 x_msg_data OUT NOCOPY VARCHAR2,
3207 x_return_status OUT NOCOPY VARCHAR2
3208 )
3209 IS
3210
3211 CURSOR get_operation_status(c_operation_id number) IS
3212 SELECT revision_status_code
3213 FROM ahl_operations_b
3214 WHERE operation_id = c_operation_id;
3215 l_operation_status_code VARCHAR2(30);
3216
3217 BEGIN
3218 x_return_status := FND_API.G_RET_STS_SUCCESS;
3219
3220 IF ( p_operation_id IS NULL OR
3221 p_operation_id = FND_API.G_MISS_NUM ) THEN
3222 x_return_status := FND_API.G_RET_STS_ERROR;
3223 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
3224 RETURN;
3225 END IF;
3226
3227 OPEN get_operation_status( p_operation_id );
3228
3229 FETCH get_operation_status INTO l_operation_status_code;
3230
3231 IF get_operation_status%NOTFOUND THEN
3232 x_return_status := FND_API.G_RET_STS_ERROR;
3233 x_msg_data := 'AHL_RM_INVALID_OPERATION';
3234 CLOSE get_operation_status;
3235 RETURN;
3236 END IF;
3237
3238 IF ( l_operation_status_code <> 'DRAFT' AND
3239 l_operation_status_code <> 'APPROVAL_REJECTED' ) THEN
3240 x_return_status := FND_API.G_RET_STS_ERROR;
3241 x_msg_data := 'AHL_RM_INVALID_OPER_STATUS';
3242 CLOSE get_operation_status;
3243 RETURN;
3244 END IF;
3245
3246 CLOSE get_operation_status;
3247 END validate_operation_status;
3248
3249 -- 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
3250 PROCEDURE validate_route_time_span
3251 (
3252 p_route_id IN NUMBER,
3253 p_time_span IN NUMBER,
3254 x_res_max_duration OUT NOCOPY NUMBER,
3255 x_msg_data OUT NOCOPY VARCHAR2,
3256 x_return_status OUT NOCOPY VARCHAR2
3257 )
3258 IS
3259
3260 -- Fix for bug# 6512803. Validate scheduled resources only.
3261 -- Bug # 7644260 (FP for ER # 6998882) -- start
3262 /*
3263 CURSOR get_max_rt_resource_duration( c_route_id NUMBER )
3264 IS
3265 SELECT MAX( duration )
3266 FROM AHL_RT_OPER_RESOURCES
3267 WHERE association_type_code = 'ROUTE'
3268 AND object_id = c_route_id
3269 AND scheduled_type_id = 1;
3270 */
3271 CURSOR get_max_rt_resource_duration( c_route_id NUMBER )
3272 IS
3273 SELECT SUM(MAX(duration))
3274 FROM AHL_RT_OPER_RESOURCES
3275 WHERE association_type_code = 'ROUTE'
3276 AND object_id = c_route_id
3277 GROUP BY schedule_seq ;
3278 -- Bug # 7644260 (FP for ER # 6998882) -- end
3279
3280 -- Fix for bug# 6512803. Validate scheduled resources only.
3281 -- Bug # 7644260 (FP for ER # 6998882) -- start
3282 /*
3283 CURSOR get_max_op_resource_duration( c_route_id NUMBER )
3284 IS
3285 SELECT MAX( RES.duration )
3286 FROM AHL_RT_OPER_RESOURCES RES, AHL_OPERATIONS_B OPER, AHL_ROUTE_OPERATIONS ASS
3287 WHERE RES.association_type_code = 'OPERATION'
3288 AND RES.object_id = ASS.operation_id
3289 AND NVL( OPER.end_date_active , TRUNC( SYSDATE ) + 1 ) > TRUNC( SYSDATE )
3290 AND OPER.operation_id = ASS.operation_id
3291 AND RES.scheduled_type_id = 1
3292 AND ASS.route_id = c_route_id;
3293 */
3294 CURSOR get_max_op_resource_duration( c_route_id NUMBER )
3295 IS
3296 SELECT SUM(OPR_DURATION)
3297 FROM
3298 (SELECT RES.object_id,RES.schedule_seq, MAX(duration) "OPR_DURATION"
3299 FROM AHL_RT_OPER_RESOURCES RES, AHL_OPERATIONS_B OPER, AHL_ROUTE_OPERATIONS ASS
3300 WHERE RES.association_type_code = 'OPERATION'
3301 AND RES.object_id = ASS.operation_id
3302 AND NVL( OPER.end_date_active , TRUNC( SYSDATE ) + 1 ) > TRUNC( SYSDATE )
3303 AND OPER.operation_id = ASS.operation_id
3304 AND ASS.route_id = c_route_id
3305 GROUP BY RES.object_id,RES.schedule_seq ) ;
3306 -- Bug # 7644260 (FP for ER # 6998882) -- end
3307
3308 BEGIN
3309 x_return_status := FND_API.G_RET_STS_SUCCESS;
3310
3311 IF ( p_route_id IS NULL OR
3312 p_route_id= FND_API.G_MISS_NUM OR
3313 p_time_span IS NULL OR
3314 p_time_span = FND_API.G_MISS_NUM ) THEN
3315 x_return_status := FND_API.G_RET_STS_ERROR;
3316 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
3317 RETURN;
3318 END IF;
3319
3320 OPEN get_max_rt_resource_duration( p_route_id );
3321
3322 FETCH get_max_rt_resource_duration INTO x_res_max_duration;
3323
3324 IF get_max_rt_resource_duration%FOUND THEN
3325 IF ( x_res_max_duration > p_time_span ) THEN
3326 x_return_status := FND_API.G_RET_STS_ERROR;
3327 x_msg_data := 'AHL_RM_RT_RES_TIME_LONGER';
3328 CLOSE get_max_rt_resource_duration;
3329 RETURN;
3330 END IF;
3331 END IF;
3332
3333 CLOSE get_max_rt_resource_duration;
3334
3335 OPEN get_max_op_resource_duration( p_route_id );
3336
3337 FETCH get_max_op_resource_duration INTO x_res_max_duration;
3338
3339 IF get_max_op_resource_duration%FOUND THEN
3340 IF ( x_res_max_duration > p_time_span ) THEN
3341 x_return_status := FND_API.G_RET_STS_ERROR;
3342 x_msg_data := 'AHL_RM_OP_RES_TIME_LONGER';
3343 END IF;
3344 END IF;
3345
3346 CLOSE get_max_op_resource_duration;
3347
3348 END validate_route_time_span;
3349
3350 -- Procedure to validate whether the Duration specified for the Route / Operation Resource is longer than The Route Time Span.
3351 PROCEDURE validate_resource_duration
3352 (
3353 p_object_id IN NUMBER,
3354 p_association_type_code IN VARCHAR2,
3355 p_duration IN NUMBER,
3356 x_max_rt_time_span OUT NOCOPY NUMBER,
3357 x_msg_data OUT NOCOPY VARCHAR2,
3358 x_return_status OUT NOCOPY VARCHAR2
3359 )
3360 IS
3361
3362 CURSOR get_route_time_span( c_route_id NUMBER )
3363 IS
3364 SELECT time_span
3365 FROM AHL_ROUTES_APP_V
3366 WHERE route_id = c_route_id;
3367
3368 CURSOR get_op_route_time_span( c_operation_id NUMBER )
3369 IS
3370 SELECT MIN( RT.time_span )
3371 FROM AHL_ROUTES_APP_V RT, AHL_ROUTE_OPERATIONS ASS
3372 WHERE TRUNC ( NVL ( RT.end_date_active , SYSDATE + 1 ) ) > TRUNC( SYSDATE )
3373 AND RT.route_id = ASS.route_id
3374 AND ASS.operation_id = c_operation_id;
3375
3376 BEGIN
3377 x_return_status := FND_API.G_RET_STS_SUCCESS;
3378
3379 IF ( p_object_id IS NULL OR
3380 p_object_id= FND_API.G_MISS_NUM OR
3381 p_association_type_code IS NULL OR
3382 p_association_type_code = FND_API.G_MISS_CHAR OR
3383 p_duration IS NULL OR
3384 p_duration = FND_API.G_MISS_NUM ) THEN
3385 x_return_status := FND_API.G_RET_STS_ERROR;
3386 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
3387 RETURN;
3388 END IF;
3389
3390 IF ( p_association_type_code = 'ROUTE' ) THEN
3391
3392 OPEN get_route_time_span( p_object_id );
3393
3394 FETCH get_route_time_span INTO x_max_rt_time_span;
3395
3396 IF get_route_time_span%FOUND THEN
3397 IF ( x_max_rt_time_span < p_duration ) THEN
3398 x_return_status := FND_API.G_RET_STS_ERROR;
3399 x_msg_data := 'AHL_RM_RT_RES_DURATION_LONGER';
3400 END IF;
3401 END IF;
3402
3403 CLOSE get_route_time_span;
3404
3405 ELSIF ( p_association_type_code = 'OPERATION' ) THEN
3406
3407 OPEN get_op_route_time_span( p_object_id );
3408
3409 FETCH get_op_route_time_span INTO x_max_rt_time_span;
3410
3411 IF get_op_route_time_span%FOUND THEN
3412 IF ( x_max_rt_time_span < p_duration ) THEN
3413 x_return_status := FND_API.G_RET_STS_ERROR;
3414 x_msg_data := 'AHL_RM_OP_RES_DURATION_LONGER';
3415 END IF;
3416 END IF;
3417
3418 CLOSE get_op_route_time_span;
3419 END IF;
3420
3421 END validate_resource_duration;
3422
3423 -- Procedure to validate whether the longest Duration specified for an operation Resource is longer than associated Route Time Span.
3424 PROCEDURE validate_rt_op_res_duration
3425 (
3426 p_route_id IN NUMBER,
3427 p_operation_id IN NUMBER,
3428 x_rt_time_span OUT NOCOPY NUMBER,
3429 x_op_max_res_duration OUT NOCOPY NUMBER,
3430 x_msg_data OUT NOCOPY VARCHAR2,
3431 x_return_status OUT NOCOPY VARCHAR2
3432 )
3433 IS
3434
3435 CURSOR get_route_time_span( c_route_id NUMBER )
3436 IS
3437 SELECT time_span
3438 FROM AHL_ROUTES_APP_V
3439 WHERE route_id = c_route_id;
3440
3441 -- Fix for bug# 6512803. Consider only scheduled resources.
3442 CURSOR get_op_max_duration( c_operation_id NUMBER )
3443 IS
3444 SELECT MAX( duration )
3445 FROM AHL_RT_OPER_RESOURCES
3446 WHERE association_type_code = 'OPERATION'
3447 AND scheduled_type_id = 1
3448 AND object_id = c_operation_id;
3449
3450 BEGIN
3451 x_return_status := FND_API.G_RET_STS_SUCCESS;
3452
3453 IF ( p_route_id IS NULL OR
3454 p_route_id = FND_API.G_MISS_NUM OR
3455 p_operation_id IS NULL OR
3456 p_operation_id = FND_API.G_MISS_NUM ) THEN
3457 x_return_status := FND_API.G_RET_STS_ERROR;
3458 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
3459 RETURN;
3460 END IF;
3461
3462 OPEN get_route_time_span( p_route_id );
3463 OPEN get_op_max_duration( p_operation_id );
3464
3465 FETCH get_route_time_span INTO x_rt_time_span;
3466 FETCH get_op_max_duration INTO x_op_max_res_duration;
3467
3468 IF ( get_route_time_span%FOUND AND
3469 get_op_max_duration%FOUND ) THEN
3470 IF ( x_rt_time_span < x_op_max_res_duration ) THEN
3471 x_return_status := FND_API.G_RET_STS_ERROR;
3472 x_msg_data := 'AHL_RM_RES_DURATION_LONGER';
3473 END IF;
3474 END IF;
3475
3476 CLOSE get_route_time_span;
3477 CLOSE get_op_max_duration;
3478
3479 END validate_rt_op_res_duration;
3480
3481 -- Procedure to validate whether the route / operation Start date is valid.
3482 PROCEDURE validate_rt_oper_start_date
3483 (
3484 p_object_id IN NUMBER,
3485 p_association_type IN VARCHAR2,
3486 p_start_date IN DATE,
3487 x_start_date OUT NOCOPY DATE,
3488 x_msg_data OUT NOCOPY VARCHAR2,
3489 x_return_status OUT NOCOPY VARCHAR2
3490 )
3491 IS
3492
3493 CURSOR get_rt_latest_start_date( c_route_id NUMBER )
3494 IS
3495 SELECT MAX( A.start_date_active )
3496 FROM AHL_ROUTES_APP_V A, AHL_ROUTES_APP_V B
3497 WHERE A.route_no = B.route_no
3498 AND A.route_id <> c_route_id
3499 AND B.route_id = c_route_id;
3500
3501 CURSOR get_op_latest_start_date( c_operation_id NUMBER )
3502 IS
3503 SELECT MAX( A.start_date_active )
3504 FROM AHL_OPERATIONS_B_KFV A, AHL_OPERATIONS_B_KFV B
3505 WHERE A.concatenated_segments = B.concatenated_segments
3506 AND A.operation_id <> c_operation_id
3507 AND B.operation_id = c_operation_id;
3508
3509 BEGIN
3510 x_return_status := FND_API.G_RET_STS_SUCCESS;
3511
3512 IF ( p_start_date IS NULL OR
3513 p_start_date = FND_API.G_MISS_DATE ) THEN
3514 x_return_status := FND_API.G_RET_STS_ERROR;
3515 x_msg_data := 'AHL_RM_ST_DATE_NULL';
3516 RETURN;
3517 END IF;
3518
3519 IF ( p_association_type IS NOT NULL AND
3520 p_association_type <> FND_API.G_MISS_CHAR AND
3521 p_association_type = 'ROUTE' ) THEN
3522
3523 OPEN get_rt_latest_start_date( p_object_id );
3524
3525 FETCH get_rt_latest_start_date INTO x_start_date;
3526
3527 IF ( get_rt_latest_start_date%FOUND ) THEN
3528 IF ( TRUNC( x_start_date ) > TRUNC( p_start_date ) ) THEN
3529 x_return_status := FND_API.G_RET_STS_ERROR;
3530 x_msg_data := 'AHL_RM_ST_DATE_LESSER';
3531 END IF;
3532 END IF;
3533
3534 CLOSE get_rt_latest_start_date;
3535 ELSIF ( p_association_type IS NOT NULL AND
3536 p_association_type <> FND_API.G_MISS_CHAR AND
3537 p_association_type = 'OPERATION' ) THEN
3538
3539 OPEN get_op_latest_start_date( p_object_id );
3540
3541 FETCH get_op_latest_start_date INTO x_start_date;
3542
3543 IF ( get_op_latest_start_date%FOUND ) THEN
3544 IF ( TRUNC( x_start_date ) > TRUNC( p_start_date ) ) THEN
3545 x_return_status := FND_API.G_RET_STS_ERROR;
3546 x_msg_data := 'AHL_RM_ST_DATE_LESSER';
3547 END IF;
3548 END IF;
3549
3550 CLOSE get_op_latest_start_date;
3551 END IF;
3552
3553 END validate_rt_oper_start_date;
3554
3555 PROCEDURE validate_ApplnUsage
3556 (
3557 p_object_id IN NUMBER,
3558 p_association_type IN VARCHAR2,
3559 x_msg_data OUT NOCOPY VARCHAR2,
3560 x_return_status OUT NOCOPY VARCHAR2
3561 )
3562 IS
3563
3564 l_appln_code VARCHAR2(30);
3565 l_object_appln_code VARCHAR2(30);
3566
3567 CURSOR get_rt_ApplnUsage( c_route_id NUMBER )
3568 IS
3569 SELECT r.Application_usg_code
3570 FROM AHL_ROUTES_B r
3571 WHERE r.route_id = c_route_id
3572 ;
3573
3574 BEGIN
3575 x_return_status := FND_API.G_RET_STS_SUCCESS;
3576
3577 IF ( (p_object_id IS NULL OR p_object_id = FND_API.G_MISS_NUM ) OR
3578 ( p_association_type IS NULL OR p_association_type = FND_API.G_MISS_CHAR ))
3579 THEN
3580 x_return_status := FND_API.G_RET_STS_ERROR;
3581 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
3582 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_PROCEDURE_CALL' );
3583 FND_MSG_PUB.add;
3584 RETURN;
3585 END IF;
3586
3587 AHL_UTIL_PKG.get_appln_usage
3588 (
3589 x_appln_code => l_appln_code,
3590 x_return_status => x_return_status
3591 );
3592
3593 -- Application code is mandatory .
3594 IF x_return_status = FND_API.G_RET_STS_ERROR
3595 THEN
3596 x_msg_data := 'AHL_COM_APPLN_CODE_NOTNULL';
3597 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_APPLN_CODE_NOTNULL' );
3598 FND_MSG_PUB.add;
3599 RETURN;
3600 END IF;
3601
3602 IF ( p_association_type = 'ROUTE' )
3603 THEN
3604
3605 OPEN get_rt_ApplnUsage( p_object_id );
3606
3607 FETCH get_rt_ApplnUsage INTO l_object_appln_code;
3608
3609 IF get_rt_ApplnUsage%NOTFOUND
3610 THEN
3611 x_return_status := FND_API.G_RET_STS_ERROR;
3612 x_msg_data := 'AHL_RM_INVALID_ROUTE';
3613 -- Balaji added code to push error data into fnd stack as a part of public API cleanup in 11510+
3614 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_ROUTE' );
3615 FND_MSG_PUB.add;
3616 CLOSE get_rt_ApplnUsage;
3617 RETURN;
3618 END IF;
3619
3620 CLOSE get_rt_ApplnUsage;
3621
3622 END IF;
3623
3624 IF (l_object_appln_code<> l_appln_code)
3625 THEN
3626 x_return_status := FND_API.G_RET_STS_ERROR;
3627 x_msg_data := 'AHL_COM_INVALID_APPLN';
3628 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_APPLN' );
3629 FND_MSG_PUB.add;
3630 END IF;
3631
3632 END validate_ApplnUsage;
3633
3634 FUNCTION get_position_meaning
3635 (
3636 p_position_path_id IN NUMBER,
3637 p_item_comp_detail_id IN NUMBER
3638 )
3639 RETURN VARCHAR2
3640 IS
3641 /*
3642 CURSOR get_rec_from_id ( c_position_path_id NUMBER )
3643 IS
3644 SELECT DISTINCT
3645 fnd.MEANING
3646 FROM
3647 ahl_mc_relationships mcr
3648 , ahl_mc_headers_b mch
3649 , ahl_mc_path_position_nodes mcp
3650 , AHL_ROUTE_EFFECTIVITIES_V re
3651 , fnd_lookup_values_vl fnd
3652 , AHL_RT_OPER_MATERIALS AOB
3653 WHERE
3654 mch.mc_header_id = mcr.mc_header_id
3655 and mch.mc_id = mcp.mc_id
3656 and re.MC_ID = mch.MC_ID
3657 and mch.version_number = nvl(mcp.version_number, mch.version_number)
3658 and mcr.position_key = mcp.position_key
3659 and mcp.sequence = (select max(sequence) from ahl_mc_path_position_nodes where path_position_id = nvl(c_position_path_id,-1))
3660 and mcp.path_position_id = nvl(c_position_path_id,-1)
3661 and fnd.LOOKUP_TYPE = 'AHL_POSITION_REFERENCE'
3662 and fnd.LOOKUP_CODE = mcr.POSITION_REF_CODE
3663 ;
3664 */
3665 l_position_path VARCHAR2(80) ; --amsriniv. Bug 6849831
3666
3667 BEGIN
3668
3669
3670 IF (
3671 ((p_position_path_id IS NOT NULL) AND (p_position_path_id <> FND_API.G_MISS_NUM ))
3672 AND
3673 ((p_item_comp_detail_id IS NULL ) OR (p_item_comp_detail_id = FND_API.G_MISS_NUM ))
3674 )
3675 THEN
3676 l_position_path := AHL_MC_PATH_POSITION_PVT.GET_POSREF_BY_ID(p_position_path_id);
3677 END IF ;
3678
3679 RETURN l_position_path;
3680
3681 END get_position_meaning ;
3682
3683 FUNCTION get_source_composition
3684 (
3685 p_position_path_id IN NUMBER,
3686 p_item_comp_detail_id IN NUMBER
3687 )
3688 RETURN VARCHAR2
3689 IS
3690
3691 CURSOR get_item_comp ( c_item_comp_detail_id NUMBER )
3692 IS
3693 SELECT DISTINCT MTL.CONCATENATED_SEGMENTS
3694 , MTL.ORGANIZATION_CODE
3695 FROM AHL_ITEM_COMP_DETAILS ICD
3696 , AHL_ITEM_COMPOSITIONS CD
3697 , AHL_MTL_ITEMS_NON_OU_V MTL
3698 WHERE ICD.item_comp_detail_id = c_item_comp_detail_id
3699 AND CD.APPROVAL_STATUS_CODE ='COMPLETE'
3700 AND CD.item_composition_id = ICD.item_composition_id
3701 AND CD.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID
3702 AND CD.INVENTORY_MASTER_ORG_ID = MTL.INVENTORY_ORG_ID
3703 AND nvl(trunc(CD.EFFECTIVE_END_DATE),trunc(sysdate+1)) > trunc(sysdate)
3704 AND nvl(trunc(ICD.EFFECTIVE_END_DATE),trunc(sysdate+1)) > trunc(sysdate)
3705 ;
3706
3707 CURSOR get_position_path ( c_position_path_id NUMBER , c_item_comp_detail_id NUMBER)
3708 IS
3709
3710 -- new query uses view ahl_position_alternates_v
3711 --AMSRINIV. Bug 5208104. Replacing below commented query with a new query for better performance
3712 select distinct mtl.concatenated_segments,
3713 mtl.organization_code
3714 from (select kfv.inventory_item_id,
3715 mp.master_organization_id inventory_org_id,
3716 mp.organization_code,
3717 kfv.concatenated_segments
3718 from mtl_system_items_kfv kfv,
3719 mtl_parameters mp
3720 where kfv.organization_id = mp.organization_id
3721 and exists (select 'X'
3722 from mtl_parameters mp1
3723 where mp1.master_organization_id = kfv.organization_id
3724 and mp1.eam_enabled_flag = 'Y')) mtl,
3725 (select inventory_item_id,
3726 inventory_org_id
3727 from ahl_position_alternates_v
3728 where relationship_id = c_position_path_id) pal,
3729 (select item_composition_id,
3730 item_comp_detail_id
3731 from ahl_item_comp_details
3732 where nvl(trunc(effective_end_date),trunc(sysdate + 1)) > trunc(sysdate)
3733 and item_comp_detail_id = c_item_comp_detail_id) icd,
3734 (select icb.item_composition_id,
3735 icb.inventory_item_id,
3736 icb.inventory_master_org_id,
3737 decode(sign(trunc(nvl(icb.effective_end_date,sysdate + 1)) - trunc(sysdate)),1,icb.approval_status_code,'EXPIRED') approval_status_code,
3738 icb.effective_end_date effective_end_date
3739 from ahl_item_compositions icb
3740 where approval_status_code = 'COMPLETE'
3741 and nvl(trunc(effective_end_date),trunc(sysdate + 1)) > trunc(sysdate)) cd
3742 where pal.inventory_item_id = mtl.inventory_item_id
3743 and pal.inventory_org_id = mtl.inventory_org_id
3744 and pal.inventory_item_id = cd.inventory_item_id
3745 and pal.inventory_org_id = cd.inventory_master_org_id
3746 and cd.item_composition_id = icd.item_composition_id;
3747
3748
3749 /*select distinct
3750 mtl.concatenated_segments ,
3751 mtl.organization_code
3752 from
3753 ahl_mtl_items_non_ou_v mtl,
3754 ahl_position_alternates_v pal,
3755 AHL_ITEM_COMP_V CD ,
3756 AHL_ITEM_COMP_DETAILS ICD
3757 where
3758 pal.relationship_id = nvl(c_position_path_id,'-1')
3759 and pal.inventory_item_id = mtl.INVENTORY_ITEM_ID
3760 and pal.inventory_org_id = mtl.INVENTORY_ORG_ID
3761 and pal.inventory_item_id = CD.inventory_item_id
3762 and pal.inventory_org_id = CD.inventory_master_org_id
3763 AND CD.APPROVAL_STATUS_CODE = 'COMPLETE'
3764 AND CD.item_composition_id = ICD.item_composition_id
3765 AND nvl(trunc(CD.EFFECTIVE_END_DATE),trunc(sysdate+1)) > trunc(sysdate)
3766 AND nvl(trunc(ICD.EFFECTIVE_END_DATE),trunc(sysdate+1)) > trunc(sysdate)
3767 AND ICD.ITEM_COMP_DETAIL_ID=c_item_comp_detail_id;*/
3768
3769
3770 /* the previous query .
3771 SELECT DISTINCT
3772 CD.concatenated_segments ,
3773 CD.organization_code
3774 FROM
3775 ahl_mc_relationships mcr,
3776 ahl_mc_headers_b mch,
3777 ahl_mc_path_position_nodes mcp,
3778 ahl_item_associations_v igass ,
3779 AHL_ROUTE_EFFECTIVITIES re ,
3780 -- ahl_mtl_items_non_ou_v mtl,
3781 AHL_ITEM_COMP_V CD ,
3782 AHL_ITEM_COMP_DETAILS ICD
3783 WHERE
3784 mch.mc_header_id = mcr.mc_header_id
3785 and mch.mc_id = mcp.mc_id
3786 and re.MC_ID = mch.MC_ID
3787 and mch.version_number = nvl(mcp.version_number, mch.version_number)
3788 and mcr.position_key = mcp.position_key
3789 and mcp.sequence = (select max(sequence) from ahl_mc_path_position_nodes where path_position_id = nvl(p_position_path_id,'-1'))
3790 and mcp.path_position_id = nvl(p_position_path_id,'-1')
3791 and mcr.item_group_id = igass.item_group_id
3792 and igass.INVENTORY_ITEM_ID = CD.inventory_item_id
3793 and igass.INVENTORY_ORG_ID = CD.inventory_master_org_id
3794 AND CD.APPROVAL_STATUS_CODE = 'COMPLETE'
3795 AND CD.item_composition_id = ICD.item_composition_id
3796 AND nvl(trunc(CD.EFFECTIVE_END_DATE),trunc(sysdate-1)) < trunc(sysdate)
3797 AND ICD.ITEM_COMP_DETAIL_ID=c_item_comp_detail_id
3798 order by 1
3799 ;
3800 */
3801 l_item_comp_detail_id NUMBER;
3802 l_position_path VARCHAR2(80);--amsriniv. Bug 6849831
3803 l_concatenated_segments VARCHAR2(40) ;
3804 l_organization_code VARCHAR2(3) ;
3805 l_source_composition VARCHAR2(73) ;
3806
3807
3808 BEGIN
3809 IF (
3810 ((p_position_path_id IS NOT NULL) AND (p_position_path_id <> FND_API.G_MISS_NUM ))
3811 AND
3812 ((p_item_comp_detail_id IS NULL ) OR (p_item_comp_detail_id = FND_API.G_MISS_NUM ))
3813 )
3814 THEN
3815 -- l_source_composition := AHL_MC_PATH_POSITION_PVT.GET_POSREF_BY_ID(p_position_path_id);
3816 l_source_composition := ' ';
3817 END IF;
3818
3819 IF (
3820 ((p_position_path_id IS NULL) OR (p_position_path_id = FND_API.G_MISS_NUM ))
3821 AND
3822 ((p_item_comp_detail_id IS NOT NULL ) AND (p_item_comp_detail_id <> FND_API.G_MISS_NUM ))
3823 )
3824 THEN
3825
3826 OPEN get_item_comp( p_item_comp_detail_id );
3827 FETCH get_item_comp INTO l_concatenated_segments,l_organization_code ;
3828
3829 IF get_item_comp%FOUND THEN
3830 l_source_composition := '('||l_concatenated_segments||','||l_organization_code||')';
3831 END IF;
3832 CLOSE get_item_comp;
3833 END IF ;
3834
3835 IF (
3836 ((p_position_path_id IS NOT NULL) AND (p_position_path_id <> FND_API.G_MISS_NUM ))
3837 AND
3838 ((p_item_comp_detail_id IS NOT NULL ) AND (p_item_comp_detail_id <> FND_API.G_MISS_NUM ))
3839 )
3840 THEN
3841
3842 OPEN get_item_comp( p_item_comp_detail_id );
3843 FETCH get_item_comp INTO l_concatenated_segments,l_organization_code ;
3844
3845
3846 IF get_item_comp%FOUND THEN
3847 IF ((l_concatenated_segments IS NOT NULL ) AND (l_organization_code IS NOT NULL)) THEN
3848 OPEN get_position_path( p_position_path_id , p_item_comp_detail_id );
3849 FETCH get_position_path INTO l_concatenated_segments,l_organization_code ;
3850
3851 IF get_position_path%FOUND THEN
3852 IF ((l_concatenated_segments IS NOT NULL ) AND (l_organization_code IS NOT NULL)) THEN
3853 l_position_path := AHL_MC_PATH_POSITION_PVT.GET_POSREF_BY_ID(p_position_path_id);
3854 IF l_position_path IS NOT NULL THEN
3855 l_source_composition := l_position_path||':('||l_concatenated_segments||','||l_organization_code||')';
3856 END IF;
3857 END IF ;
3858 END IF;
3859 CLOSE get_position_path;
3860 CLOSE get_item_comp;
3861 END IF;
3862 END IF;
3863
3864 END IF ;
3865
3866 RETURN l_source_composition;
3867
3868 END get_source_composition ;
3869
3870 --Procedure to get Operation id out of Operation Name and Revision
3871 PROCEDURE Operation_Number_To_Id
3872 (
3873 p_operation_number IN VARCHAR2,
3874 p_operation_revision IN NUMBER,
3875 x_operation_id OUT NOCOPY NUMBER,
3876 x_return_status OUT NOCOPY VARCHAR2
3877 )
3878 IS
3879
3880 -- Cursor for selecting operation id.
3881 CURSOR oper_name_rev_csr_type (p_operation_number IN VARCHAR2, p_operation_revision IN NUMBER)
3882 IS
3883 SELECT operation_id
3884 FROM ahl_operations_vl
3885 WHERE concatenated_segments = p_operation_number
3886 AND
3887 revision_number = p_operation_revision;
3888
3889 l_operation_id NUMBER;
3890 l_api_name CONSTANT VARCHAR2(30) := 'OPERATION_NUMBER_TO_ID';
3891
3892 BEGIN
3893
3894 x_return_status := FND_API.G_RET_STS_SUCCESS;
3895 OPEN oper_name_rev_csr_type(p_operation_number, p_operation_revision);
3896 FETCH oper_name_rev_csr_type INTO l_operation_id;
3897 CLOSE oper_name_rev_csr_type;
3898
3899 IF l_operation_id IS NULL THEN
3900 x_return_status := FND_API.G_RET_STS_ERROR;
3901 FND_MESSAGE.SET_NAME('AHL','AHL_RM_INV_OPER_NO_REV');
3902 FND_MESSAGE.SET_TOKEN('NUMBER', p_operation_number);
3903 FND_MESSAGE.SET_TOKEN('REVISION', p_operation_revision);
3904 FND_MSG_PUB.ADD;
3905 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
3906 fnd_log.string
3907 (
3908 fnd_log.level_error,
3909 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3910 'INVALID Operation Name AND Operation Revision'
3911 );
3912 END IF;
3913 ELSE
3914 x_operation_id := l_operation_id;
3915 END IF;
3916
3917 END Operation_Number_To_Id;
3918
3919
3920 --Procedure to get Route id out of Route Number and Revision
3921 PROCEDURE Route_Number_To_Id
3922 (
3923 p_route_number IN VARCHAR2,
3924 p_route_revision IN NUMBER,
3925 x_route_id OUT NOCOPY NUMBER,
3926 x_return_status OUT NOCOPY VARCHAR2
3927 )
3928 IS
3929
3930 -- Cursor for selecting route id.
3931 CURSOR route_name_rev_csr_type (p_route_number IN VARCHAR2, p_route_revision IN NUMBER)
3932 IS
3933 SELECT route_id
3934 FROM ahl_routes_app_v
3935 WHERE UPPER( TRIM(route_no)) = UPPER(TRIM(p_route_number))
3936 AND
3937 revision_number = p_route_revision;
3938
3939 l_route_id NUMBER;
3940 l_api_name CONSTANT VARCHAR2(30) := 'ROUTE_NUMBER_TO_ID';
3941
3942 BEGIN
3943 x_return_status := FND_API.G_RET_STS_SUCCESS;
3944
3945 OPEN route_name_rev_csr_type(p_route_number, p_route_revision);
3946 FETCH route_name_rev_csr_type INTO l_route_id;
3947 CLOSE route_name_rev_csr_type;
3948
3949 IF l_route_id IS NULL THEN
3950 x_return_status := FND_API.G_RET_STS_ERROR;
3951 FND_MESSAGE.SET_NAME('AHL','AHL_RM_INV_ROUTE_NO_REV');
3952 FND_MESSAGE.SET_TOKEN('NUMBER', p_route_number);
3953 FND_MESSAGE.SET_TOKEN('REVISION', p_route_revision);
3954 FND_MSG_PUB.ADD;
3955 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
3956 fnd_log.string
3957 (
3958 fnd_log.level_error,
3959 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
3960 'INVALID Route Name AND Route Revision'
3961 );
3962 END IF;
3963 ELSE
3964 x_route_id := l_route_id;
3965 END IF;
3966
3967 END Route_Number_To_Id;
3968
3969 END AHL_RM_ROUTE_UTIL;