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