[Home] [Help]
PACKAGE BODY: APPS.AHL_FMP_COMMON_PVT
Source
1 PACKAGE BODY ahl_fmp_common_pvt AS
2 /* $Header: AHLVFCMB.pls 120.13.12020000.2 2012/12/14 08:52:36 shnatu ship $ */
3
4 G_PKG_NAME VARCHAR2(30):='AHL_FMP_COMMON_PVT';
5 G_APPLN_USAGE VARCHAR2(30):=LTRIM(RTRIM(FND_PROFILE.VALUE('AHL_APPLN_USAGE')));
6 G_DEBUG VARCHAR2(1):=AHL_DEBUG_PUB.is_log_enabled;
7
8 -- local procedures
9
10 -- Check to see whether a VARCHAR2 contains only numeric values
11 FUNCTION sn_num(
12 p_serial_number IN VARCHAR2
13 ) RETURN BOOLEAN;
14
15
16 -- Procedure to validate lookups
17 PROCEDURE validate_lookup
18 (
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_msg_data OUT NOCOPY VARCHAR2,
21 p_lookup_type IN FND_LOOKUPS.lookup_type%TYPE,
22 p_lookup_meaning IN FND_LOOKUPS.meaning%TYPE,
23 p_x_lookup_code IN OUT NOCOPY FND_LOOKUPS.lookup_code%TYPE
24 )
25 IS
26
27 l_lookup_code FND_LOOKUPS.lookup_code%TYPE;
28
29 CURSOR get_rec_from_value ( c_lookup_type FND_LOOKUPS.lookup_type%TYPE,
30 c_lookup_meaning FND_LOOKUPS.meaning%TYPE )
31 IS
32 SELECT lookup_code
33 FROM FND_LOOKUP_VALUES_VL
34 WHERE lookup_type = c_lookup_type
35 AND meaning = c_lookup_meaning
36 AND SYSDATE BETWEEN NVL( start_date_active, SYSDATE ) AND
37 NVL( end_date_active, SYSDATE );
38
39 CURSOR get_rec_from_id ( c_lookup_type FND_LOOKUPS.lookup_type%TYPE,
40 c_lookup_code FND_LOOKUPS.lookup_code%TYPE )
41 IS
42 SELECT lookup_code
43 FROM FND_LOOKUP_VALUES_VL
44 WHERE lookup_type = c_lookup_type
45 AND lookup_code = c_lookup_code
46 AND SYSDATE BETWEEN NVL( start_date_active, SYSDATE ) AND
47 NVL( end_date_active, SYSDATE );
48
49 BEGIN
50 x_return_status := FND_API.G_RET_STS_SUCCESS;
51
52 IF ( p_lookup_type IS NULL OR
53 p_lookup_type = FND_API.G_MISS_CHAR ) THEN
54 x_return_status := FND_API.G_RET_STS_ERROR;
55 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
56 RETURN;
57 END IF;
58
59 IF ( ( p_lookup_meaning IS NULL OR
60 p_lookup_meaning = FND_API.G_MISS_CHAR ) AND
61 ( p_x_lookup_code IS NULL OR
62 p_x_lookup_code = FND_API.G_MISS_CHAR ) ) THEN
63 x_return_status := FND_API.G_RET_STS_ERROR;
64 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
65 RETURN;
66 END IF;
67
68 IF ( ( p_lookup_meaning IS NULL OR
69 p_lookup_meaning = FND_API.G_MISS_CHAR ) AND
70 ( p_x_lookup_code IS NOT NULL AND
71 p_x_lookup_code <> FND_API.G_MISS_CHAR ) ) THEN
72
73 OPEN get_rec_from_id( p_lookup_type, p_x_lookup_code );
74
75 FETCH get_rec_from_id INTO
76 l_lookup_code;
77
78 IF get_rec_from_id%NOTFOUND THEN
79 x_return_status := FND_API.G_RET_STS_ERROR;
80 x_msg_data := 'AHL_COM_INVALID_LOOKUP';
81 END IF;
82
83 CLOSE get_rec_from_id;
84 RETURN;
85
86 END IF;
87
88 IF ( p_lookup_meaning IS NOT NULL AND
89 p_lookup_meaning <> FND_API.G_MISS_CHAR ) THEN
90
91 OPEN get_rec_from_value( p_lookup_type, p_lookup_meaning );
92
93 LOOP
94 FETCH get_rec_from_value INTO
95 l_lookup_code;
96
97 EXIT WHEN get_rec_from_value%NOTFOUND;
98
99 IF ( l_lookup_code = p_x_lookup_code ) THEN
100 CLOSE get_rec_from_value;
101 RETURN;
102 END IF;
103
104 END LOOP;
105
106 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
107 x_return_status := FND_API.G_RET_STS_ERROR;
108 x_msg_data := 'AHL_COM_INVALID_LOOKUP';
109 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
110 p_x_lookup_code := l_lookup_code;
111 ELSE
112 x_return_status := FND_API.G_RET_STS_ERROR;
113 x_msg_data := 'AHL_COM_TOO_MANY_LOOKUPS';
114 END IF;
115
116 CLOSE get_rec_from_value;
117 RETURN;
118
119 END IF;
120
121 END validate_lookup;
122
123 -- Procedure to validate Item
124 PROCEDURE validate_item
125 (
126 x_return_status OUT NOCOPY VARCHAR2,
127 x_msg_data OUT NOCOPY VARCHAR2,
128 p_item_number IN MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE,
129 p_x_inventory_item_id IN OUT NOCOPY MTL_SYSTEM_ITEMS.inventory_item_id%TYPE
130 )
131 IS
132
133 CURSOR get_rec_from_value ( c_item_number MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE )
134 IS
135 SELECT DISTINCT MI.inventory_item_id,
136 MI.inventory_item_flag,
137 MI.eng_item_flag,
138 MI.build_in_wip_flag,
139 MI.wip_supply_type,
140 MI.eam_item_type,
141 MI.comms_nl_trackable_flag,
142 MI.serv_req_enabled_code
143 FROM MTL_PARAMETERS MP, MTL_SYSTEM_ITEMS_KFV MI
144 WHERE DECODE(G_APPLN_USAGE,'PM','Y',MP.eam_enabled_flag )='Y'
145 AND MP.organization_id = MI.organization_id
146 AND MI.concatenated_segments = c_item_number
147 AND MI.enabled_flag = 'Y'
148 AND SYSDATE BETWEEN NVL( MI.start_date_active, SYSDATE )
149 AND NVL( MI.end_date_active, SYSDATE );
150
151 l_item_rec2 get_rec_from_value%rowtype;
152
153 CURSOR get_rec_from_id ( c_inventory_item_id MTL_SYSTEM_ITEMS.inventory_item_id%TYPE )
154 IS
155 SELECT DISTINCT MI.inventory_item_id,
156 MI.inventory_item_flag,
157 MI.eng_item_flag,
158 MI.build_in_wip_flag,
159 MI.wip_supply_type,
160 MI.eam_item_type,
161 MI.comms_nl_trackable_flag,
162 MI.serv_req_enabled_code
163 FROM MTL_PARAMETERS MP, MTL_SYSTEM_ITEMS_KFV MI
164 WHERE DECODE(G_APPLN_USAGE,'PM','Y',MP.eam_enabled_flag )='Y'
165 AND MP.organization_id = MI.organization_id
166 AND MI.inventory_item_id = c_inventory_item_id
167 AND MI.enabled_flag = 'Y'
168 AND SYSDATE BETWEEN NVL( MI.start_date_active, SYSDATE )
169 AND NVL( MI.end_date_active, SYSDATE );
170 l_item_rec1 get_rec_from_id%rowtype;
171 l_rec_found VARCHAR2(1):=FND_API.G_FALSE;
172 L_REC_COUNT NUMBER:=0;
173 BEGIN
174 x_return_status := FND_API.G_RET_STS_SUCCESS;
175
176 IF ( ( p_item_number IS NULL OR
177 p_item_number = FND_API.G_MISS_CHAR ) AND
178 ( p_x_inventory_item_id IS NULL OR
179 p_x_inventory_item_id = FND_API.G_MISS_NUM ) ) THEN
180 x_return_status := FND_API.G_RET_STS_ERROR;
181 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
182 RETURN;
183 END IF;
184
185 IF ( ( p_item_number IS NULL OR
186 p_item_number = FND_API.G_MISS_CHAR ) AND
187 ( p_x_inventory_item_id IS NOT NULL AND
188 p_x_inventory_item_id <> FND_API.G_MISS_NUM ) )
189 THEN
190 OPEN get_rec_from_id( p_x_inventory_item_id );
191 LOOP
192 FETCH get_rec_from_id INTO l_item_rec1;
193 EXIT WHEN get_rec_from_id%NOTFOUND;
194 L_REC_COUNT:=L_REC_COUNT+1;
195
196 IF G_APPLN_USAGE<>'PM'
197 THEN
198 IF l_item_rec1.inventory_item_flag='Y' and
199 l_item_rec1.eng_item_flag='N' and
200 l_item_rec1.build_in_wip_flag='Y' and
201 l_item_rec1.wip_supply_type=1 and
202 l_item_rec1.eam_item_type=3 and
203 l_item_rec1.comms_nl_trackable_flag='Y'
204 THEN
205 l_rec_found:=FND_API.G_TRUE;
206 END IF;
207 ELSIF G_APPLN_USAGE='PM'
208 THEN
209 IF l_item_rec1.comms_nl_trackable_flag='Y' and
210 l_item_rec1.serv_req_enabled_code='E'
211 THEN
212 l_rec_found:=FND_API.G_TRUE;
213 END IF;
214 END IF;
215 EXIT WHEN l_rec_found=FND_API.G_TRUE;
216 END LOOP;
217 CLOSE get_rec_from_id;
218
219 IF L_REC_COUNT=0
220 THEN
221 x_return_status := FND_API.G_RET_STS_ERROR;
222 x_msg_data := 'AHL_FMP_INVALID_ITEM';
223 RETURN;
224 END IF;
225
226 IF l_rec_found=FND_API.G_FALSE
227 THEN
228 x_return_status := FND_API.G_RET_STS_ERROR;
229 x_msg_data := 'AHL_FMP_NOT_JOB_ITEM';
230 END IF;
231 RETURN;
232
233 ELSIF p_item_number IS NOT NULL AND
234 p_item_number <> FND_API.G_MISS_CHAR
235 THEN
236 L_REC_COUNT:=0;
237 OPEN get_rec_from_value( p_item_number );
238 LOOP
239 FETCH get_rec_from_value INTO L_ITEM_REC2;
240
241 EXIT WHEN get_rec_from_value%NOTFOUND;
242 L_REC_COUNT:=L_REC_COUNT+1;
243 p_x_inventory_item_id := l_item_rec2.inventory_item_id;
244
245 IF G_APPLN_USAGE<>'PM' -- NOT PM MODE
246 THEN
247 IF l_item_rec2.inventory_item_flag='Y' and
248 l_item_rec2.eng_item_flag='N' and
249 l_item_rec2.build_in_wip_flag='Y' and
250 l_item_rec2.wip_supply_type=1 and
251 l_item_rec2.eam_item_type=3 and
252 l_item_rec2.comms_nl_trackable_flag='Y'
253 THEN
254 l_rec_found:=FND_API.G_TRUE;
255 END IF;
256 ELSIF G_APPLN_USAGE='PM' -- PM MODE
257 THEN
258 IF l_item_rec2.comms_nl_trackable_flag='Y' and
259 l_item_rec2.serv_req_enabled_code='E'
260 THEN
261 l_rec_found:=FND_API.G_TRUE;
262 END IF;
263 END IF;
264
265 END LOOP;
266
267 CLOSE get_rec_from_value;
268
269 IF L_REC_COUNT=0
270 THEN
271 x_return_status := FND_API.G_RET_STS_ERROR;
272 x_msg_data := 'AHL_FMP_INVALID_ITEM';
273 RETURN;
274 END IF;
275
276 IF l_rec_found=FND_API.G_FALSE
277 THEN
278 x_return_status := FND_API.G_RET_STS_ERROR;
279 x_msg_data := 'AHL_FMP_NOT_JOB_ITEM';
280 END IF;
281
282 RETURN;
283 END IF;
284 -- END OF ITEM NUMBER CHECK
285 END validate_item;
286
287
288
289
290
291 PROCEDURE validate_pc_node
292 (
293 x_return_status OUT NOCOPY VARCHAR2,
294 x_msg_data OUT NOCOPY VARCHAR2,
295 p_pc_node_name IN VARCHAR2 := NULL,
296 p_x_pc_node_id IN OUT NOCOPY NUMBER
297 )
298 IS
299
300 l_pc_node_id NUMBER;
301
302 CURSOR get_rec_from_value ( c_pc_node_name VARCHAR2 )
303 IS
304 SELECT pc_node_id
305 FROM AHL_PC_NODES_B
306 WHERE name = c_pc_node_name;
307
308 CURSOR get_rec_from_id ( c_pc_node_id NUMBER )
309 IS
310 SELECT pc_node_id
311 FROM AHL_PC_NODES_B
312 WHERE pc_node_id = c_pc_node_id;
313
314 BEGIN
315 x_return_status := FND_API.G_RET_STS_SUCCESS;
316
317 IF ( ( p_pc_node_name IS NULL OR
318 p_pc_node_name = FND_API.G_MISS_CHAR ) AND
319 ( p_x_pc_node_id IS NULL OR
320 p_x_pc_node_id = FND_API.G_MISS_NUM ) ) THEN
321 x_return_status := FND_API.G_RET_STS_ERROR;
322 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
323 RETURN;
324 END IF;
325
326 IF ( ( p_pc_node_name IS NULL OR
327 p_pc_node_name = FND_API.G_MISS_CHAR ) AND
328 ( p_x_pc_node_id IS NOT NULL AND
329 p_x_pc_node_id <> FND_API.G_MISS_NUM ) ) THEN
330
331 OPEN get_rec_from_id( p_x_pc_node_id );
332
333 FETCH get_rec_from_id INTO
334 l_pc_node_id;
335
336 IF get_rec_from_id%NOTFOUND THEN
337 x_return_status := FND_API.G_RET_STS_ERROR;
338 x_msg_data := 'AHL_FMP_INVALID_PC_NODE';
339 END IF;
340
341 CLOSE get_rec_from_id;
342 RETURN;
343
344 END IF;
345
346 IF ( p_pc_node_name IS NOT NULL AND
347 p_pc_node_name <> FND_API.G_MISS_CHAR ) THEN
348
349 OPEN get_rec_from_value( p_pc_node_name );
350
351 LOOP
352 FETCH get_rec_from_value INTO
353 l_pc_node_id;
354
355 EXIT WHEN get_rec_from_value%NOTFOUND;
356
357 IF ( l_pc_node_id = p_x_pc_node_id ) THEN
358 CLOSE get_rec_from_value;
359 RETURN;
360 END IF;
361
362 END LOOP;
363
364 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
365 x_return_status := FND_API.G_RET_STS_ERROR;
366 x_msg_data := 'AHL_FMP_INVALID_PC_NODE';
367 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
368 p_x_pc_node_id := l_pc_node_id;
369 ELSE
370 x_return_status := FND_API.G_RET_STS_ERROR;
371 x_msg_data := 'AHL_FMP_TOO_MANY_PC_NODES';
372 END IF;
373
374 CLOSE get_rec_from_value;
375 RETURN;
376
377 END IF;
378
379 END validate_pc_node;
380
381 /*pdoki commented for Bug 6032303 - Start
382 PROCEDURE validate_position
383 (
384 x_return_status OUT NOCOPY VARCHAR2,
385 x_msg_data OUT NOCOPY VARCHAR2,
386 p_position_ref_meaning IN VARCHAR2 := NULL,
387 p_x_relationship_id IN OUT NOCOPY NUMBER
388 )
389 IS
390
391 l_relationship_id NUMBER;
392
393 l_valid_rec_found BOOLEAN := FALSE;
394 l_posn_meaning FND_LOOKUPS.meaning%TYPE;
395 l_junk VARCHAR2(1);
396 */
397 /*CURSOR get_rec_from_value ( c_position_ref_meaning VARCHAR2 )
398 IS
399 SELECT DISTINCT relationship_id,
400 NVL( active_start_date, SYSDATE ),
401 NVL( active_end_date, SYSDATE + 1 )
402 FROM AHL_MASTER_CONFIG_DETAILS_V
403 WHERE position_ref_meaning = c_position_ref_meaning;
404
405 CURSOR get_rec_from_id ( c_relationship_id NUMBER )
406 IS
407 SELECT DISTINCT relationship_id,
408 NVL( active_start_date, SYSDATE ),
409 NVL( active_end_date, SYSDATE + 1 )
410 FROM AHL_RELATIONSHIPS_VL
411 WHERE relationship_id = c_relationship_id;
412 */
413
414 -- 11.5.10 changes for MC
415 /*
416 CURSOR get_rec_from_id ( c_relationship_id NUMBER )
417 IS
418 SELECT relationship_id
419 FROM ahl_mc_relationships mcr,
420 ahl_mc_headers_b mch, ahl_mc_path_position_nodes mcp
421 where mch.mc_header_id = mcr.mc_header_id and
422 mch.mc_id = mcp.mc_id and
423 mch.version_number = nvl(mcp.version_number, mch.version_number) and
424 mcr.position_key = mcp.position_key and
425 mcp.sequence = (select max(sequence)
426 from ahl_mc_path_position_nodes
427 where path_position_id = nvl(c_relationship_id,'-1'))
428 and mcp.path_position_id = nvl(c_relationship_id,'-1');
429
430 CURSOR check_reln_valid_csr (c_relationship_id IN NUMBER)
431 IS
432 SELECT 'x'
433 FROM ahl_mc_relationships
434 WHERE parent_relationship_id IS NULL
435 START WITH relationship_id = c_relationship_id
436 AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
437 CONNECT BY PRIOR parent_relationship_id = relationship_id
438 AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1));
439
440
441 BEGIN
442 x_return_status := FND_API.G_RET_STS_SUCCESS;
443
444 IF ( ( p_position_ref_meaning IS NULL OR
445 p_position_ref_meaning = FND_API.G_MISS_CHAR ) AND
446 ( p_x_relationship_id IS NULL OR
447 p_x_relationship_id = FND_API.G_MISS_NUM ) ) THEN
448 x_return_status := FND_API.G_RET_STS_ERROR;
449 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
450 RETURN;
451 END IF;
452
453 IF ( --( p_position_ref_meaning IS NULL OR
454 --p_position_ref_meaning = FND_API.G_MISS_CHAR ) AND
455 ( p_x_relationship_id IS NOT NULL AND
456 p_x_relationship_id <> FND_API.G_MISS_NUM ) ) THEN
457
458 FOR relationship_rec IN get_rec_from_id( p_x_relationship_id ) LOOP
459
460 -- Check if position is valid.
461 OPEN check_reln_valid_csr(relationship_rec.relationship_id);
462 FETCH check_reln_valid_csr INTO l_junk;
463 IF (check_reln_valid_csr%NOTFOUND) THEN
464 x_return_status := FND_API.G_RET_STS_ERROR;
465 x_msg_data := 'AHL_FMP_INVALID_MC_POS_STATUS';
466 CLOSE check_reln_valid_csr;
467
468 ELSE
469 IF (p_position_ref_meaning IS NOT NULL AND
470 p_position_ref_meaning <> FND_API.G_MISS_CHAR ) THEN
471
472 l_posn_meaning := AHL_MC_PATH_POSITION_PVT.GET_POSREF_BY_ID(relationship_rec.relationship_id);
473 IF (l_posn_meaning <> p_position_ref_meaning) THEN
474 x_return_status := FND_API.G_RET_STS_ERROR;
475 x_msg_data := 'AHL_FMP_INVALID_MC_POS_MEANING';
476 ELSE
477 -- exit when at least one valid relationship found.
478 l_valid_rec_found := TRUE;
479 EXIT;
480 END IF;
481 ELSE
482 -- exit when at least one valid relationship found.
483 l_valid_rec_found := TRUE;
484 EXIT;
485 END IF;
486 END IF;
487
488 END LOOP;
489
490 IF NOT(l_valid_rec_found) THEN
491 x_return_status := FND_API.G_RET_STS_ERROR;
492 x_msg_data := 'AHL_FMP_INVALID_MC_POSITION';
493
494 END IF;
495
496 RETURN;
497
498 END IF;
499
500 IF ( p_position_ref_meaning IS NOT NULL AND
501 p_position_ref_meaning <> FND_API.G_MISS_CHAR ) THEN
502
503 x_return_status := FND_API.G_RET_STS_ERROR;
504 x_msg_data := 'AHL_FMP_TOO_MANY_MC_POSITIONS';
505
506 RETURN;
507 END IF;
508 END validate_position;
509 --pdoki commented for Bug 6032303 - End */
510
511 --pdoki added for Bug 6032303 - Start
512 PROCEDURE validate_position
513 (
514 x_return_status OUT NOCOPY VARCHAR2,
515 x_msg_data OUT NOCOPY VARCHAR2,
516 p_position_ref_meaning IN VARCHAR2 := NULL,
517 p_x_relationship_id IN OUT NOCOPY NUMBER
518 )
519 IS
520 l_mc_header_id NUMBER;
521 l_posn_meaning FND_LOOKUPS.meaning%TYPE;
522 l_dummy_char VARCHAR2(1);
523
524 CURSOR get_mc_header_id ( c_relationship_id NUMBER )
525 IS
526 SELECT mch.mc_header_id
527 FROM ahl_mc_headers_b mch, ahl_mc_path_position_nodes mcp
528 WHERE
529 mch.mc_id = mcp.mc_id and
530 mch.version_number = nvl(mcp.version_number, mch.version_number) and
531 mcp.sequence = (select max(sequence)
532 from ahl_mc_path_position_nodes
533 where path_position_id = nvl(c_relationship_id,'-1'))
534 and mcp.path_position_id = nvl(c_relationship_id,'-1');
535
536 --pdoki commented for Bug 11723220
537 /*CURSOR check_mc_status ( c_mc_header_id NUMBER )
538 IS
539 SELECT 'X'
540 FROM ahl_mc_headers_b
541 WHERE mc_header_id = c_mc_header_id AND
542 config_status_code = 'COMPLETE';*/
543
544 BEGIN
545 x_return_status := FND_API.G_RET_STS_SUCCESS;
546
547 --If position meaning and path_position_id is null then throw 'invalid procedure call' error.
548
549 IF ( ( p_position_ref_meaning IS NULL OR
550 p_position_ref_meaning = FND_API.G_MISS_CHAR ) AND
551 ( p_x_relationship_id IS NULL OR
552 p_x_relationship_id = FND_API.G_MISS_NUM ) ) THEN
553 x_return_status := FND_API.G_RET_STS_ERROR;
554 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
555 RETURN;
556 END IF;
557
558 --If position meaning is not null but path_position_id is null, then throw error asking user to pick position from LOV.
559 IF ( ( p_position_ref_meaning IS NOT NULL AND
560 p_position_ref_meaning <> FND_API.G_MISS_CHAR ) AND
561 ( p_x_relationship_id IS NULL OR
562 p_x_relationship_id = FND_API.G_MISS_NUM ) ) THEN
563 x_return_status := FND_API.G_RET_STS_ERROR;
564 x_msg_data := 'AHL_FMP_TOO_MANY_MC_POSITIONS';
565 RETURN;
566 END IF;
567
568 --Following validations are done only when the path_postion_id is NOT null as we return to the caller API in the above cases.
569
570 --Retrieving the mc_header_id to check if the MC is complete.
571 OPEN get_mc_header_id(p_x_relationship_id);
572 FETCH get_mc_header_id INTO l_mc_header_id;
573 IF (get_mc_header_id%NOTFOUND) THEN
574 x_return_status := FND_API.G_RET_STS_ERROR;
575 x_msg_data := 'AHL_FMP_INVALID_MC_POSITION';
576 CLOSE get_mc_header_id;
577 RETURN;
578 END IF;
579 CLOSE get_mc_header_id;
580
581 --pdoki commented for Bug 11723220
582 --Throw error if MC is not complete.
583 /*OPEN check_mc_status(l_mc_header_id);
584 FETCH check_mc_status INTO l_dummy_char;
585 IF (check_mc_status%NOTFOUND) THEN
586 x_return_status := FND_API.G_RET_STS_ERROR;
587 x_msg_data := 'AHL_FMP_MC_NOT_COMPLETE';
588 CLOSE check_mc_status;
589 RETURN;
590 END IF;
591 CLOSE check_mc_status;*/
592
593 --Retrieve path_position_meaning and check with the path_position_id passed to the API.
594 --Throw invalid MC position error if no position_ref_meaning is returned.
595 --Throw error 'Invalid Master Configuration Position FIELD entered for Effectivity RECORD.'if they dont match
596 --Fix for Bug 6032303. Calling below function with the path_position_id
597 l_posn_meaning := AHL_MC_PATH_POSITION_PVT.GET_POSREF_BY_ID(p_x_relationship_id);
598 IF (l_posn_meaning IS NULL) THEN
599 x_return_status := FND_API.G_RET_STS_ERROR;
600 x_msg_data := 'AHL_FMP_INVALID_MC_POSITION';
601 ELSIF (p_position_ref_meaning IS NOT NULL AND l_posn_meaning <> p_position_ref_meaning) THEN
602 x_return_status := FND_API.G_RET_STS_ERROR;
603 x_msg_data := 'AHL_FMP_INVALID_MC_POS_MEANING';
604 END IF;
605
606 END validate_position;
607 --pdoki added for Bug 6032303 - End
608
609 PROCEDURE validate_position_item
610 (
611 x_return_status OUT NOCOPY VARCHAR2,
612 x_msg_data OUT NOCOPY VARCHAR2,
613 p_inventory_item_id IN NUMBER,
614 p_relationship_id IN NUMBER
615 )
616 IS
617
618 l_dummy VARCHAR2(1);
619
620 CURSOR check_alternate( c_relationship_id NUMBER, c_inventory_item_id NUMBER )
621 IS
622 SELECT 'X'
623 FROM ahl_mc_relationships mcr,
624 ahl_mc_headers_b mch,
625 ahl_mc_path_position_nodes mcp,
626 --ahl_item_associations_v igass ,
627 ahl_item_associations_vl igass, --priyan changes due to performance reasons , Refer Bug # 5078530
628 mtl_system_items_kfv MTL,
629 mtl_item_status STAT,
630 fnd_lookup_values_vl IT
631 WHERE mch.mc_header_id = mcr.mc_header_id
632 and mch.mc_id = mcp.mc_id
633 and mch.version_number = nvl(mcp.version_number, mch.version_number)
634 and mcr.position_key = mcp.position_key
635 and mcp.sequence = (select max(sequence)
636 from ahl_mc_path_position_nodes where
637 path_position_id = nvl(c_relationship_id,-1)
638 and path_position_id=mcp.path_position_id)
639 and mcp.path_position_id = nvl(c_relationship_id,-1)
640 and mcr.item_group_id = igass.item_group_id
641 and igass.INVENTORY_ITEM_ID = mtl.INVENTORY_ITEM_ID
642 and igass.INVENTORY_ORG_ID = mtl.ORGANIZATION_ID
643 and mtl.inventory_item_id=c_inventory_item_id
644 and MTL.service_item_flag = 'N'
645 and STAT.inventory_item_status_code = MTL.inventory_item_status_code
646 and IT.lookup_code (+) = MTL.item_type
647 and IT.lookup_type (+) = 'ITEM_TYPE'
648 and trunc(sysdate) between trunc(nvl(it.start_date_active,sysdate))
649 and trunc(nvl(IT.end_date_active,sysdate+1));
650
651 BEGIN
652 x_return_status := FND_API.G_RET_STS_SUCCESS;
653
654 IF ( p_inventory_item_id IS NULL OR
655 p_inventory_item_id = FND_API.G_MISS_NUM OR
656 p_relationship_id IS NULL OR
657 p_relationship_id = FND_API.G_MISS_NUM ) THEN
658 x_return_status := FND_API.G_RET_STS_ERROR;
659 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
660 RETURN;
661 END IF;
662
663 OPEN check_alternate( p_relationship_id , p_inventory_item_id );
664
665 FETCH check_alternate INTO
666 l_dummy;
667
668 IF check_alternate%NOTFOUND THEN
669 x_return_status := FND_API.G_RET_STS_ERROR;
670 x_msg_data := 'AHL_FMP_INVALID_POSITION_ITEM';
671 END IF;
672
673 CLOSE check_alternate;
674 RETURN;
675
676 END validate_position_item;
677
678 PROCEDURE validate_counter_template
679 (
680 x_return_status OUT NOCOPY VARCHAR2,
681 x_msg_data OUT NOCOPY VARCHAR2,
682 p_inventory_item_id IN NUMBER := NULL,
683 p_relationship_id IN NUMBER := NULL,
684 p_counter_name IN VARCHAR2 := NULL,
685 p_x_counter_id IN OUT NOCOPY NUMBER
686 )
687 IS
688
689 l_msg_data VARCHAR2(2000);
690 l_return_status VARCHAR2(1);
691 l_counter_id NUMBER;
692 l_counter_group_id NUMBER;
693 l_inventory_item_id NUMBER;
694 --pdoki added for bug 6719371
695 l_dummy VARCHAR2(1);
696 l_counter_valid_flag BOOLEAN := FALSE;
697
698 CURSOR check_alternate_ctr_id(c_counter_id NUMBER, c_relationship_id NUMBER)
699 IS
700 SELECT 'X'
701 FROM csi_counter_template_vl C,
702 --CS_COUNTER_GROUPS CG,
703 CS_CSI_COUNTER_GROUPS CG,
704 CS_CTR_ASSOCIATIONS CA,
705 AHL_POSITION_ALTERNATES_V PA
706 WHERE C.DEFAULTED_GROUP_ID = CG.COUNTER_GROUP_ID
707 AND CG.TEMPLATE_FLAG = 'Y'
708 AND C.COUNTER_ID = c_counter_id
709 AND C.DIRECTION IN ('A',
710 'B')
711 AND CG.COUNTER_GROUP_ID = CA.COUNTER_GROUP_ID
712 AND CA.SOURCE_OBJECT_ID = PA.INVENTORY_ITEM_ID
713 AND PA.RELATIONSHIP_ID = c_relationship_id;
714
715
716 CURSOR get_rec_from_value ( c_counter_name VARCHAR2 )
717 IS
718 /*
719 SELECT DISTINCT C.counter_id,
720 C.counter_group_id
721 FROM CS_COUNTERS C, CS_COUNTER_GROUPS CG
722 WHERE CG.template_flag = 'Y'
723 AND C.counter_group_id = CG.counter_group_id
724 AND C.name = c_counter_name
725 AND C.DIRECTION in ('A','B'); */
726
727 --Priyan
728 --Performance tuning changes
729 --Refer Bug # 4913671
730
731 SELECT DISTINCT
732 C.COUNTER_ID,
733 C.DEFAULTED_GROUP_ID COUNTER_GROUP_ID
734 FROM
735 CSI_COUNTER_TEMPLATE_VL C,
736 --CS_COUNTER_GROUPS CG
737 --Priyan
738 --Perf changes . Refer Bug # 4913671
739 CS_CSI_COUNTER_GROUPS CG
740 WHERE
741 CG.TEMPLATE_FLAG = 'Y'
742 AND C.DEFAULTED_GROUP_ID = CG.COUNTER_GROUP_ID
743 AND C.NAME = c_counter_name
744 AND C.DIRECTION in ('A','B');
745
746
747 -- NEED TO REQUEST CS TEAM TO ADD INDEX ON COUNTER_GROU_ID IN TABLE
748 CURSOR get_rec_from_id ( c_counter_id NUMBER )
749 IS
750 /*
751 SELECT DISTINCT C.counter_id,
752 C.counter_group_id
753 FROM CS_COUNTERS C, CS_COUNTER_GROUPS CG
754 WHERE CG.template_flag = 'Y'
755 AND C.counter_group_id = CG.counter_group_id
756 AND C.counter_id = c_counter_id;
757 */
758
759 --Priyan
760 --Performance tuning changes
761 --Refer Bug # 4913671
762
763 SELECT DISTINCT
764 C.COUNTER_ID,
765 C.DEFAULTED_GROUP_ID COUNTER_GROUP_ID
766 FROM
767 CSI_COUNTER_TEMPLATE_VL C,
768 --CS_COUNTER_GROUPS CG
769 --Priyan
770 --Perf changes . Refer Bug # 4913671
771 CS_CSI_COUNTER_GROUPS CG
772 WHERE
773 CG.TEMPLATE_FLAG = 'Y'
774 AND C.DEFAULTED_GROUP_ID = CG.COUNTER_GROUP_ID
775 AND C.COUNTER_ID = c_counter_id ;
776
777
778 CURSOR validate_item_ctr_id(c_counter_id NUMBER, c_inventory_item_id NUMBER)
779 IS
780 SELECT 'X'
781 FROM --cs_counters c,
782 csi_counter_template_vl C,
783 --cs_counter_groups CG,
784 CS_CSI_COUNTER_GROUPS CG,
785 cs_ctr_associations CA
786 where --C.counter_group_id = CG.counter_group_id
787 C.defaulted_group_id = CG.counter_group_id
788 and CG.template_flag = 'Y'
789 and C.COUNTER_ID = c_counter_id
790 and C.direction in ('A','B')
791 and CG.counter_group_id = CA.counter_group_id
792 and CA.source_object_id = c_inventory_item_id;
793
794
795 /*
796 SELECT DISTINCT source_object_id
797 FROM CS_CTR_ASSOCIATIONS
798 WHERE counter_group_id = c_counter_group_id;
799
800
801 CURSOR get_counter_item ( c_counter_group_id NUMBER )
802 IS
803 SELECT DISTINCT source_object_id
804 FROM CS_CTR_ASSOCIATIONS
805 WHERE counter_group_id = c_counter_group_id;
806 */
807
808
809 BEGIN
810 x_return_status := FND_API.G_RET_STS_SUCCESS;
811
812 IF ( p_counter_name IS NULL AND --amsriniv . changed OR to AND
813 ( p_x_counter_id IS NULL OR
814 p_x_counter_id = FND_API.G_MISS_NUM ) ) THEN
815 x_return_status := FND_API.G_RET_STS_ERROR;
816 x_msg_data := 'AHL_FMP_INVALID_COUNTER';
817 RETURN;
818 END IF;
819
820 IF ( ( p_inventory_item_id IS NULL OR
821 p_inventory_item_id = FND_API.G_MISS_NUM ) AND
822 ( p_relationship_id IS NULL OR
823 p_relationship_id = FND_API.G_MISS_NUM ) ) THEN
824 x_return_status := FND_API.G_RET_STS_ERROR;
825 x_msg_data := 'AHL_FMP_INVALID_CTR_ITEM';
826 RETURN;
827 END IF;
828
829 IF ( ( p_counter_name IS NULL OR
830 p_counter_name = FND_API.G_MISS_CHAR ) AND
831 ( p_x_counter_id IS NOT NULL AND
832 p_x_counter_id <> FND_API.G_MISS_NUM ) ) THEN
833
834 IF ( p_inventory_item_id IS NOT NULL AND
835 p_inventory_item_id <> FND_API.G_MISS_NUM ) THEN
836 OPEN validate_item_ctr_id(p_x_counter_id, p_inventory_item_id);
837 FETCH validate_item_ctr_id INTO l_dummy;
838 IF (validate_item_ctr_id%NOTFOUND) THEN
839 x_return_status := FND_API.G_RET_STS_ERROR;
840 x_msg_data := 'AHL_FMP_INVALID_CTR_ITEM';
841 END IF;
842 CLOSE validate_item_ctr_id;
843 ELSIF (p_relationship_id IS NOT NULL AND
844 p_relationship_id <> FND_API.G_MISS_NUM ) THEN
845 OPEN check_alternate_ctr_id(p_x_counter_id, p_relationship_id);
846 FETCH check_alternate_ctr_id INTO l_dummy;
847 IF (check_alternate_ctr_id%NOTFOUND) THEN
848 x_return_status := FND_API.G_RET_STS_ERROR;
849 x_msg_data := 'AHL_FMP_INVALID_CTR_POSITION';
850 END IF;
851 CLOSE check_alternate_ctr_id;
852 END IF;
853 END IF;
854
855 IF ( p_counter_name IS NOT NULL AND
856 p_counter_name <> FND_API.G_MISS_CHAR ) THEN
857
858 OPEN get_rec_from_value( p_counter_name );
859
860 LOOP
861
862 FETCH get_rec_from_value INTO
863 l_counter_id,
864 l_counter_group_id;
865
866 EXIT WHEN get_rec_from_value%NOTFOUND;
867
868 IF ( p_inventory_item_id IS NOT NULL AND
869 p_inventory_item_id <> FND_API.G_MISS_NUM ) THEN
870 OPEN validate_item_ctr_id(l_counter_id, p_inventory_item_id);
871 FETCH validate_item_ctr_id INTO l_dummy;
872 IF (validate_item_ctr_id%FOUND) THEN
873 l_counter_valid_flag := TRUE;
874 p_x_counter_id := l_counter_id;
875 CLOSE validate_item_ctr_id;
876 EXIT;
877 END IF;
878 CLOSE validate_item_ctr_id;
879 ELSIF (p_relationship_id IS NOT NULL AND
880 p_relationship_id <> FND_API.G_MISS_NUM ) THEN
881 OPEN check_alternate_ctr_id(l_counter_id, p_relationship_id);
882 FETCH check_alternate_ctr_id INTO l_dummy;
883 IF (check_alternate_ctr_id%FOUND) THEN
884 l_counter_valid_flag := TRUE;
885 p_x_counter_id := l_counter_id;
886 CLOSE check_alternate_ctr_id;
887 EXIT;
888 END IF;
889 CLOSE check_alternate_ctr_id;
890 END IF;
891
892 END LOOP;
893
894 IF (get_rec_from_value%ROWCOUNT = 0) THEN --added by amsriniv
895 x_return_status := FND_API.G_RET_STS_ERROR;
896 x_msg_data := 'AHL_FMP_INVALID_COUNTER';
897 ELSE
898 IF NOT(l_counter_valid_flag)THEN
899 IF ( p_inventory_item_id IS NOT NULL AND
900 p_inventory_item_id <> FND_API.G_MISS_NUM ) THEN
901 x_return_status := FND_API.G_RET_STS_ERROR;
902 x_msg_data := 'AHL_FMP_INVALID_CTR_ITEM';
903 ELSIF ( p_relationship_id IS NOT NULL AND
904 p_relationship_id <> FND_API.G_MISS_NUM ) THEN
905 x_return_status := FND_API.G_RET_STS_ERROR;
906 x_msg_data := 'AHL_FMP_INVALID_CTR_POSITION';
907 END IF;
908 END IF;
909 END IF;
910 END IF;
911
912 END validate_counter_template;
913
914 PROCEDURE validate_country
915 (
916 x_return_status OUT NOCOPY VARCHAR2,
917 x_msg_data OUT NOCOPY VARCHAR2,
918 p_country_name IN VARCHAR2 := NULL,
919 p_x_country_code IN OUT NOCOPY VARCHAR2
920 )
921 IS
922
923 l_country_code VARCHAR2(2);
924
925 CURSOR get_rec_from_value ( c_country_name VARCHAR2 )
926 IS
927 SELECT DISTINCT territory_code
928 FROM FND_TERRITORIES_VL
929 WHERE territory_short_name = c_country_name;
930
931 CURSOR get_rec_from_id ( c_country_code VARCHAR2 )
932 IS
933 SELECT DISTINCT territory_code
934 FROM FND_TERRITORIES_VL
935 WHERE territory_code = c_country_code;
936
937 BEGIN
938 x_return_status := FND_API.G_RET_STS_SUCCESS;
939
940 IF ( ( p_country_name IS NULL OR
941 p_country_name = FND_API.G_MISS_CHAR ) AND
942 ( p_x_country_code IS NULL OR
943 p_x_country_code = FND_API.G_MISS_CHAR ) ) THEN
944 x_return_status := FND_API.G_RET_STS_ERROR;
945 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
946 RETURN;
947 END IF;
948
949 IF ( ( p_country_name IS NULL OR
950 p_country_name = FND_API.G_MISS_CHAR ) AND
951 ( p_x_country_code IS NOT NULL AND
952 p_x_country_code <> FND_API.G_MISS_CHAR ) ) THEN
953
954 OPEN get_rec_from_id( p_x_country_code );
955
956 FETCH get_rec_from_id INTO
957 l_country_code;
958
959 IF get_rec_from_id%NOTFOUND THEN
960 x_return_status := FND_API.G_RET_STS_ERROR;
961 x_msg_data := 'AHL_FMP_INVALID_COUNTRY';
962 END IF;
963
964 CLOSE get_rec_from_id;
965 RETURN;
966
967 END IF;
968
969 IF ( p_country_name IS NOT NULL AND
970 p_country_name <> FND_API.G_MISS_CHAR ) THEN
971
972 OPEN get_rec_from_value( p_country_name );
973
974 LOOP
975 FETCH get_rec_from_value INTO
976 l_country_code;
977
978 EXIT WHEN get_rec_from_value%NOTFOUND;
979
980 IF ( l_country_code = p_x_country_code ) THEN
981 CLOSE get_rec_from_value;
982 RETURN;
983 END IF;
984
985 END LOOP;
986
987 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
988 x_return_status := FND_API.G_RET_STS_ERROR;
989 x_msg_data := 'AHL_FMP_INVALID_COUNTRY';
990 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
991 p_x_country_code := l_country_code;
992 ELSE
993 x_return_status := FND_API.G_RET_STS_ERROR;
994 x_msg_data := 'AHL_FMP_TOO_MANY_COUNTRIES';
995 END IF;
996
997 CLOSE get_rec_from_value;
998 RETURN;
999
1000 END IF;
1001
1002 END validate_country;
1003
1004 PROCEDURE validate_manufacturer
1005 (
1006 x_return_status OUT NOCOPY VARCHAR2,
1007 x_msg_data OUT NOCOPY VARCHAR2,
1008 p_inventory_item_id IN NUMBER := NULL,
1009 p_relationship_id IN NUMBER := NULL,
1010 p_manufacturer_name IN VARCHAR2 := NULL,
1011 p_x_manufacturer_id IN OUT NOCOPY NUMBER
1012 )
1013 IS
1014
1015 l_msg_data VARCHAR2(2000);
1016 l_return_status VARCHAR2(1);
1017 l_manufacturer_id NUMBER;
1018 l_inventory_item_id NUMBER;
1019
1020 CURSOR get_rec_from_value ( c_manufacturer_name VARCHAR2 )
1021 IS
1022 /*SELECT DISTINCT manufacturer_id,
1023 inventory_item_id
1024 FROM MTL_MFG_PART_NUMBERS_ALL_V
1025 WHERE manufacturer_name = c_manufacturer_name;*/
1026
1027 --priyan
1028 --Changing the query for performance reasons
1029 --Refer Bug # 5078530
1030
1031 select distinct b.manufacturer_id,
1032 a.inventory_item_id
1033 from mtl_manufacturers b,
1034 mtl_mfg_part_numbers a
1035 where a.manufacturer_id = b.manufacturer_id
1036 and b.manufacturer_name = c_manufacturer_name
1037 and a.organization_id in ( select distinct
1038 m.master_organization_id
1039 from inv_organization_info_v org,
1040 mtl_parameters m
1041 where org.organization_id = m.organization_id
1042 and nvl(org.operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id() );
1043
1044
1045 CURSOR get_rec_from_id ( c_manufacturer_id NUMBER )
1046 IS
1047 /*SELECT DISTINCT manufacturer_id,
1048 inventory_item_id
1049 FROM MTL_MFG_PART_NUMBERS_ALL_V
1050 WHERE manufacturer_id = c_manufacturer_id;*/
1051
1052 --priyan
1053 --Changing the query for performance reasons
1054 --Refer Bug # 5078530
1055
1056 select distinct manufacturer_id,
1057 inventory_item_id
1058 from mtl_mfg_part_numbers
1059 where manufacturer_id = c_manufacturer_id
1060 and organization_id in ( select distinct
1061 m.master_organization_id
1062 from inv_organization_info_v org,
1063 mtl_parameters m
1064 where org.organization_id = m.organization_id
1065 and nvl(org.operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id() );
1066
1067
1068 BEGIN
1069 x_return_status := FND_API.G_RET_STS_SUCCESS;
1070
1071 IF ( ( p_manufacturer_name IS NULL OR
1072 p_manufacturer_name = FND_API.G_MISS_CHAR ) AND
1073 ( p_x_manufacturer_id IS NULL OR
1074 p_x_manufacturer_id = FND_API.G_MISS_NUM ) ) THEN
1075 x_return_status := FND_API.G_RET_STS_ERROR;
1076 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
1077 RETURN;
1078 END IF;
1079
1080 IF ( ( p_inventory_item_id IS NULL OR
1081 p_inventory_item_id = FND_API.G_MISS_NUM ) AND
1082 ( p_relationship_id IS NULL OR
1083 p_relationship_id = FND_API.G_MISS_NUM ) ) THEN
1084 x_return_status := FND_API.G_RET_STS_ERROR;
1085 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
1086 RETURN;
1087 END IF;
1088
1089 IF ( ( p_manufacturer_name IS NULL OR
1090 p_manufacturer_name = FND_API.G_MISS_CHAR ) AND
1091 ( p_x_manufacturer_id IS NOT NULL AND
1092 p_x_manufacturer_id <> FND_API.G_MISS_NUM ) ) THEN
1093
1094 OPEN get_rec_from_id( p_x_manufacturer_id );
1095
1096 LOOP
1097 FETCH get_rec_from_id INTO
1098 l_manufacturer_id,
1099 l_inventory_item_id;
1100
1101 EXIT WHEN get_rec_from_id%NOTFOUND;
1102
1103 IF ( p_inventory_item_id IS NOT NULL AND
1104 p_inventory_item_id <> FND_API.G_MISS_NUM ) THEN
1105 IF ( p_inventory_item_id = l_inventory_item_id ) THEN
1106 CLOSE get_rec_from_id;
1107 RETURN;
1108 END IF;
1109 ELSIF ( p_relationship_id IS NOT NULL AND
1110 p_relationship_id <> FND_API.G_MISS_NUM ) THEN
1111
1112 validate_position_item
1113 (
1114 x_return_status => l_return_status,
1115 x_msg_data => l_msg_data,
1116 p_inventory_item_id => l_inventory_item_id,
1117 p_relationship_id => p_relationship_id
1118 );
1119
1120 IF ( NVL( l_return_status, 'X' ) = FND_API.G_RET_STS_SUCCESS ) THEN
1121 CLOSE get_rec_from_id;
1122 RETURN;
1123 END IF;
1124 END IF;
1125
1126 END LOOP;
1127
1128 IF ( get_rec_from_id%ROWCOUNT = 0 ) THEN
1129 x_return_status := FND_API.G_RET_STS_ERROR;
1130 x_msg_data := 'AHL_FMP_INVALID_MF';
1131 ELSE
1132 x_return_status := FND_API.G_RET_STS_ERROR;
1133 IF ( p_inventory_item_id IS NOT NULL AND
1134 p_inventory_item_id <> FND_API.G_MISS_NUM ) THEN
1135 x_msg_data := 'AHL_FMP_INVALID_MF_ITEM';
1136 ELSIF ( p_relationship_id IS NOT NULL AND
1137 p_relationship_id <> FND_API.G_MISS_NUM ) THEN
1138 x_msg_data := 'AHL_FMP_INVALID_MF_POSITION';
1139 END IF;
1140 END IF;
1141
1142 CLOSE get_rec_from_id;
1143 RETURN;
1144
1145 END IF;
1146
1147 IF ( p_manufacturer_name IS NOT NULL AND
1148 p_manufacturer_name <> FND_API.G_MISS_CHAR ) THEN
1149
1150 OPEN get_rec_from_value( p_manufacturer_name );
1151
1152 LOOP
1153
1154 FETCH get_rec_from_value INTO
1155 l_manufacturer_id,
1156 l_inventory_item_id;
1157
1158 EXIT WHEN get_rec_from_value%NOTFOUND;
1159
1160 IF ( p_inventory_item_id IS NOT NULL AND
1161 p_inventory_item_id <> FND_API.G_MISS_NUM ) THEN
1162 IF ( p_inventory_item_id = l_inventory_item_id ) THEN
1163 IF ( p_x_manufacturer_id IS NULL ) THEN
1164 p_x_manufacturer_id := l_manufacturer_id;
1165 CLOSE get_rec_from_value;
1166 RETURN;
1167 ELSIF ( l_manufacturer_id = p_x_manufacturer_id ) THEN
1168 CLOSE get_rec_from_value;
1169 RETURN;
1170 END IF;
1171 END IF;
1172 ELSIF ( p_relationship_id IS NOT NULL AND
1173 p_relationship_id <> FND_API.G_MISS_NUM ) THEN
1174
1175 validate_position_item
1176 (
1177 x_return_status => l_return_status,
1178 x_msg_data => l_msg_data,
1179 p_inventory_item_id => l_inventory_item_id,
1180 p_relationship_id => p_relationship_id
1181 );
1182
1183 IF ( NVL( l_return_status, 'X' ) = FND_API.G_RET_STS_SUCCESS ) THEN
1184 IF ( p_x_manufacturer_id IS NULL ) THEN
1185 p_x_manufacturer_id := l_manufacturer_id;
1186 CLOSE get_rec_from_value;
1187 RETURN;
1188 ELSIF ( l_manufacturer_id = p_x_manufacturer_id ) THEN
1189 CLOSE get_rec_from_value;
1190 RETURN;
1191 END IF;
1192 END IF;
1193
1194 END IF;
1195
1196 END LOOP;
1197
1198 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
1199 x_return_status := FND_API.G_RET_STS_ERROR;
1200 x_msg_data := 'AHL_FMP_INVALID_MF';
1201 ELSE
1202 IF ( p_inventory_item_id IS NOT NULL AND
1203 p_inventory_item_id <> FND_API.G_MISS_NUM ) THEN
1204 x_return_status := FND_API.G_RET_STS_ERROR;
1205 x_msg_data := 'AHL_FMP_INVALID_MF_ITEM';
1206 ELSIF ( p_relationship_id IS NOT NULL AND
1207 p_relationship_id <> FND_API.G_MISS_NUM ) THEN
1208 x_return_status := FND_API.G_RET_STS_ERROR;
1209 x_msg_data := 'AHL_FMP_INVALID_MF_POSITION';
1210 END IF;
1211 END IF;
1212
1213 CLOSE get_rec_from_value;
1214 RETURN;
1215
1216 END IF;
1217
1218 END validate_manufacturer;
1219
1220 PROCEDURE validate_serial_numbers_range
1221 (
1222 x_return_status OUT NOCOPY VARCHAR2,
1223 x_msg_data OUT NOCOPY VARCHAR2,
1224 p_serial_number_from IN VARCHAR2,
1225 p_serial_number_to IN VARCHAR2
1226 )
1227 IS
1228
1229 l_dummy VARCHAR2(1);
1230
1231 CURSOR compare_numbers ( c_serial_number_from VARCHAR2 , c_serial_number_to VARCHAR2 )
1232 IS
1233 SELECT 'X'
1234 FROM DUAL
1235 WHERE TO_NUMBER( c_serial_number_to ) >=
1236 TO_NUMBER( c_serial_number_from );
1237
1238 CURSOR compare_chars ( c_serial_number_from VARCHAR2 , c_serial_number_to VARCHAR2 )
1239 IS
1240 SELECT 'X'
1241 FROM DUAL
1242 WHERE c_serial_number_to >= c_serial_number_from;
1243
1244 BEGIN
1245 x_return_status := FND_API.G_RET_STS_SUCCESS;
1246
1247 IF ( p_serial_number_from IS NULL OR
1248 p_serial_number_from = FND_API.G_MISS_CHAR OR
1249 p_serial_number_to IS NULL OR
1250 p_serial_number_to = FND_API.G_MISS_CHAR ) THEN
1251 x_return_status := FND_API.G_RET_STS_ERROR;
1252 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
1253 RETURN;
1254 END IF;
1255
1256 -- added numeric check to fix bug#9311242
1257 IF (sn_num(p_serial_number_from) AND sn_num(p_serial_number_to)) THEN
1258 BEGIN
1259
1260 OPEN compare_numbers( p_serial_number_from, p_serial_number_to );
1261
1262 FETCH compare_numbers INTO
1263 l_dummy;
1264
1265 IF compare_numbers%NOTFOUND THEN
1266 x_return_status := FND_API.G_RET_STS_ERROR;
1267 x_msg_data := 'AHL_FMP_INVALID_SERIAL_RANGE';
1268 END IF;
1269
1270 CLOSE compare_numbers;
1271
1272 EXCEPTION WHEN INVALID_NUMBER THEN
1273 IF compare_numbers%ISOPEN THEN
1274 CLOSE compare_numbers;
1275 END IF;
1276
1277 -- added to fix bug#9311242
1278 WHEN OTHERS THEN
1279 IF compare_numbers%ISOPEN THEN
1280 CLOSE compare_numbers;
1281 END IF;
1282 END;
1283
1284 END IF;
1285
1286 OPEN compare_chars( p_serial_number_from, p_serial_number_to );
1287
1288 FETCH compare_chars INTO
1289 l_dummy;
1290
1291 IF compare_chars%NOTFOUND THEN
1292 x_return_status := FND_API.G_RET_STS_ERROR;
1293 x_msg_data := 'AHL_FMP_INVALID_SERIAL_RANGE';
1294 END IF;
1295
1296 CLOSE compare_chars;
1297 RETURN;
1298
1299 END validate_serial_numbers_range;
1300
1301 PROCEDURE validate_mr_status
1302 (
1303 x_return_status OUT NOCOPY VARCHAR2,
1304 x_msg_data OUT NOCOPY VARCHAR2,
1305 p_mr_header_id IN NUMBER
1306 )
1307 IS
1308 CURSOR check_mr_status( c_mr_header_id NUMBER )
1309 IS
1310 SELECT mr_status_code
1311 FROM AHL_MR_HEADERS_APP_V
1312 WHERE mr_header_id = c_mr_header_id;
1313
1314 l_mr_status_code varchar2(30);
1315 BEGIN
1316 x_return_status := FND_API.G_RET_STS_SUCCESS;
1317 IF ( G_DEBUG = 'Y' ) THEN
1318 AHL_DEBUG_PUB.enable_debug;
1319 AHL_DEBUG_PUB.debug('Step A' );
1320 END IF;
1321
1322 IF ( p_mr_header_id IS NULL OR
1323 p_mr_header_id = FND_API.G_MISS_NUM ) THEN
1324 x_return_status := FND_API.G_RET_STS_ERROR;
1325 x_msg_data := 'AHL_FMP_MR_HEADER_ID_INVALID';
1326 RETURN;
1327 END IF;
1328 AHL_DEBUG_PUB.debug('Step A3' );
1329 OPEN check_mr_status( p_mr_header_id );
1330
1331 FETCH check_mr_status INTO l_mr_status_code;
1332
1333 IF check_mr_status%NOTFOUND THEN
1334 x_return_status := FND_API.G_RET_STS_ERROR;
1335 x_msg_data := 'AHL_FMP_INVALID_MR';
1336 CLOSE check_mr_status;
1337 RETURN;
1338 END IF;
1339 AHL_DEBUG_PUB.debug('Step A6' );
1340 IF ( l_mr_status_code <> 'DRAFT' AND
1341 l_mr_status_code <> 'APPROVAL_REJECTED' ) THEN
1342 x_return_status := FND_API.G_RET_STS_ERROR;
1343 AHL_DEBUG_PUB.debug('Step 1' );
1344 x_msg_data := 'AHL_FMP_INVALID_MR_STATUS';
1345 AHL_DEBUG_PUB.debug('Step 2' );
1346 CLOSE check_mr_status;
1347 RETURN;
1348 END IF;
1349
1350 CLOSE check_mr_status;
1351 RETURN;
1352
1353 END validate_mr_status;
1354
1355 PROCEDURE validate_mr_effectivity
1356 (
1357 x_return_status OUT NOCOPY VARCHAR2,
1358 x_msg_data OUT NOCOPY VARCHAR2,
1359 p_mr_effectivity_id IN NUMBER,
1360 p_object_version_number IN NUMBER := NULL
1361 )
1362 IS
1363
1364 l_object_version_number NUMBER;
1365
1366 CURSOR check_mr_effectivity( c_mr_effectivity_id NUMBER )
1367 IS
1368 SELECT object_version_number
1369 FROM AHL_MR_EFFECTIVITIES_APP_V
1370 WHERE mr_effectivity_id = c_mr_effectivity_id;
1371
1372 BEGIN
1373 x_return_status := FND_API.G_RET_STS_SUCCESS;
1374
1375 IF ( p_mr_effectivity_id IS NULL OR
1376 p_mr_effectivity_id = FND_API.G_MISS_NUM ) THEN
1377 x_return_status := FND_API.G_RET_STS_ERROR;
1378 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
1379 RETURN;
1380 END IF;
1381
1382 OPEN check_mr_effectivity( p_mr_effectivity_id );
1383
1384 FETCH check_mr_effectivity INTO
1385 l_object_version_number;
1386
1387 IF check_mr_effectivity%NOTFOUND THEN
1388 x_return_status := FND_API.G_RET_STS_ERROR;
1389 x_msg_data := 'AHL_FMP_INVALID_MR_EFFECTIVITY';
1390 CLOSE check_mr_effectivity;
1391 RETURN;
1392 END IF;
1393
1394 IF ( p_object_version_number IS NOT NULL OR
1395 p_object_version_number <> FND_API.G_MISS_NUM ) THEN
1396 IF ( p_object_version_number <> l_object_version_number ) THEN
1397 x_return_status := FND_API.G_RET_STS_ERROR;
1398 x_msg_data := 'AHL_FMP_INVALID_MR_EFFECTIVITY';
1399 END IF;
1400 END IF;
1401
1402 CLOSE check_mr_effectivity;
1403 RETURN;
1404
1405 END validate_mr_effectivity;
1406
1407 PROCEDURE validate_mr_interval_threshold
1408 (
1409 x_return_status OUT NOCOPY VARCHAR2,
1410 x_msg_data OUT NOCOPY VARCHAR2,
1411 p_mr_header_id IN NUMBER,
1412 p_repetitive_flag IN VARCHAR2
1413 )
1414 IS
1415
1416 l_msg_data VARCHAR2(2000);
1417 l_return_status VARCHAR2(1);
1418 l_dummy VARCHAR2(1);
1419 l_mr_effectivity_id NUMBER;
1420 l_counter_id NUMBER;
1421
1422 CURSOR get_threshold( c_mr_header_id NUMBER )
1423 IS
1424 SELECT 'X'
1425 FROM AHL_MR_EFFECTIVITIES_APP_V
1426 WHERE mr_header_id = c_mr_header_id
1427 AND threshold_date IS NOT NULL;
1428
1429 CURSOR get_intervals( c_mr_header_id NUMBER )
1430 IS
1431 SELECT A.mr_effectivity_id,
1432 A.counter_id
1433 FROM AHL_MR_INTERVALS_APP_V A, AHL_MR_EFFECTIVITIES_APP_V B
1434 WHERE A.mr_effectivity_id = B.mr_effectivity_id
1435 AND B.mr_header_id = c_mr_header_id
1436 GROUP BY A.mr_effectivity_id,
1437 A.counter_id
1438 HAVING count(*) > 1;
1439
1440 CURSOR get_interval_range( c_mr_header_id NUMBER )
1441 IS
1442 SELECT 'X'
1443 FROM AHL_MR_INTERVALS_APP_V A, AHL_MR_EFFECTIVITIES_APP_V B
1444 WHERE ( A.start_date IS NOT NULL OR A.start_value IS NOT NULL )
1445 AND A.mr_effectivity_id = B.mr_effectivity_id
1446 AND B.mr_header_id = c_mr_header_id;
1447
1448 BEGIN
1449 x_return_status := FND_API.G_RET_STS_SUCCESS;
1450
1451 IF ( ( p_mr_header_id IS NULL OR
1452 p_mr_header_id = FND_API.G_MISS_NUM ) AND
1453 ( p_repetitive_flag IS NULL OR
1454 p_repetitive_flag = FND_API.G_MISS_CHAR ) ) THEN
1455 x_return_status := FND_API.G_RET_STS_ERROR;
1456 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
1457 RETURN;
1458 END IF;
1459
1460 -- Check if the Maintenance Requirement is in Updatable status
1461 AHL_FMP_COMMON_PVT.validate_mr_status
1462 (
1463 x_return_status => l_return_status,
1464 x_msg_data => l_msg_data,
1465 p_mr_header_id => p_mr_header_id
1466 );
1467
1468 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
1469 x_return_status := FND_API.G_RET_STS_ERROR;
1470 x_msg_data := l_msg_data;
1471 RETURN;
1472 END IF;
1473
1474 IF ( p_repetitive_flag = 'Y' ) THEN
1475 OPEN get_threshold( p_mr_header_id );
1476
1477 FETCH get_threshold INTO
1478 l_dummy;
1479
1480 IF get_threshold%FOUND THEN
1481 x_return_status := FND_API.G_RET_STS_ERROR;
1482 x_msg_data := 'AHL_FMP_INVALID_MR_THRESHOLD';
1483 END IF;
1484
1485 CLOSE get_threshold;
1486 RETURN;
1487 ELSE
1488 OPEN get_intervals( p_mr_header_id );
1489
1490 FETCH get_intervals INTO
1491 l_mr_effectivity_id,
1492 l_counter_id;
1493
1494 IF get_intervals%FOUND THEN
1495 x_return_status := FND_API.G_RET_STS_ERROR;
1496 x_msg_data := 'AHL_FMP_INVALID_MR_INTERVALS';
1497 CLOSE get_intervals;
1498 RETURN;
1499 END IF;
1500
1501 CLOSE get_intervals;
1502
1503 OPEN get_interval_range( p_mr_header_id );
1504
1505 FETCH get_interval_range INTO
1506 l_dummy;
1507
1508 IF get_interval_range%FOUND THEN
1509 x_return_status := FND_API.G_RET_STS_ERROR;
1510 x_msg_data := 'AHL_FMP_INVALID_MR_INT_RANGE';
1511 CLOSE get_interval_range;
1512 RETURN;
1513 END IF;
1514
1515 CLOSE get_interval_range;
1516
1517 END IF;
1518
1519 END validate_mr_interval_threshold;
1520
1521
1522
1523
1524 -----------------------------------------------------------------------
1525 -- Start of Comments --
1526 -- Procedure name : Populate_Appl_MRs
1527 -- Type : Private
1528 -- Function : Calls FMP and populates the AHL_APPLICABLE_MRS table.
1529 -- Pre-reqs :
1530 -- Parameters :
1531 --
1532 -- Populate_Appl_MRs Parameters:
1533 -- p_csi_ii_id IN csi item instance id Required
1534 --
1535 -- Version :
1536 -- Initial Version 1.0
1537 --
1538 -- End of Comments.
1539
1540
1541 PROCEDURE Populate_Appl_MRs (
1542 p_csi_ii_id IN NUMBER,
1543 p_include_doNotImplmt IN VARCHAR2 := 'Y',
1544 p_show_dependent_components IN VARCHAR2 := 'Y', -- BUG 9169406
1545 -- JKJain, NR Analysis and Forecasting
1546 p_consider_fleet_flag IN VARCHAR2 := 'N',
1547 p_flt_range_calc_date IN DATE := sysdate ,
1548 x_return_status OUT NOCOPY VARCHAR2,
1549 x_msg_count OUT NOCOPY NUMBER,
1550 x_msg_data OUT NOCOPY VARCHAR2)
1551 IS
1552 l_api_version CONSTANT NUMBER := 1.0;
1553 l_appl_mrs_tbl AHL_FMP_PVT.applicable_mr_tbl_type;
1554
1555 BEGIN
1556
1557 SAVEPOINT POPULATE_APPL_MRS_PVT;
1558
1559 -- Initialize temporary table.
1560 DELETE FROM AHL_APPLICABLE_MRS;
1561
1562 -- call api to fetch all applicable mrs for ASO installation.
1563 AHL_FMP_PVT.get_applicable_mrs(
1564 p_api_version => l_api_version,
1565 p_init_msg_list => FND_API.G_FALSE,
1566 p_commit => FND_API.G_FALSE,
1567 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1568 x_return_status => x_return_status,
1569 x_msg_count => x_msg_count,
1570 x_msg_data => x_msg_data,
1571 p_item_instance_id => p_csi_ii_id,
1572 p_components_flag => p_show_dependent_components, -- BUG 9169406
1573 p_include_doNotImplmt => p_include_doNotImplmt,
1574 -- JKJain, NR Analysis and Forecasting
1575 p_consider_fleet_flag => p_consider_fleet_flag,
1576 p_flt_range_calc_date => p_flt_range_calc_date,
1577 x_applicable_mr_tbl => l_appl_mrs_tbl);
1578
1579
1580 -- Raise errors if exceptions occur
1581 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1582 RAISE FND_API.G_EXC_ERROR;
1583 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1584 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1585 END IF;
1586
1587 -- Populate temporary table ahl_applicable_mrs.
1588 IF (l_appl_mrs_tbl.COUNT > 0) THEN
1589 FOR i IN l_appl_mrs_tbl.FIRST..l_appl_mrs_tbl.LAST LOOP
1590 -- dbms_output.put_line( l_appl_mrs_tbl(i).item_instance_id||' '||
1591 -- l_appl_mrs_tbl(i).mr_header_id);
1592 INSERT INTO AHL_APPLICABLE_MRS (
1593 CSI_ITEM_INSTANCE_ID,
1594 MR_HEADER_ID,
1595 MR_EFFECTIVITY_ID,
1596 REPETITIVE_FLAG ,
1597 SHOW_REPETITIVE_CODE,
1598 COPY_ACCOMPLISHMENT_CODE,
1599 PRECEDING_MR_HEADER_ID,
1600 IMPLEMENT_STATUS_CODE,
1601 DESCENDENT_COUNT
1602 ) values
1603 ( l_appl_mrs_tbl(i).item_instance_id,
1604 l_appl_mrs_tbl(i).mr_header_id,
1605 l_appl_mrs_tbl(i).mr_effectivity_id,
1606 l_appl_mrs_tbl(i).repetitive_flag,
1607 l_appl_mrs_tbl(i).show_repetitive_code,
1608 l_appl_mrs_tbl(i).copy_accomplishment_flag,
1609 l_appl_mrs_tbl(i).preceding_mr_header_id,
1610 l_appl_mrs_tbl(i).implement_status_code,
1611 l_appl_mrs_tbl(i).descendent_count
1612 );
1613 END LOOP;
1614 END IF;
1615
1616 -- pdoki added exception handlers for Bug 13788613
1617 EXCEPTION
1618 WHEN FND_API.G_EXC_ERROR THEN
1619 ROLLBACK TO POPULATE_APPL_MRS_PVT;
1620 x_return_status := FND_API.G_RET_STS_ERROR;
1621 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1622 p_count => x_msg_count,
1623 p_data => x_msg_data);
1624
1625
1626 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1627 ROLLBACK TO POPULATE_APPL_MRS_PVT;
1628 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1629 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1630 p_count => x_msg_count,
1631 p_data => x_msg_data);
1632
1633
1634 WHEN OTHERS THEN
1635 ROLLBACK TO POPULATE_APPL_MRS_PVT;
1636 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1637 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1638 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'AHL_FMP_COMMON_PVT',
1639 p_procedure_name => 'POPULATE_APPL_MRS',
1640 p_error_text => SUBSTRB(SQLERRM,1,240));
1641 END IF;
1642 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1643 p_count => x_msg_count,
1644 p_data => x_msg_data);
1645
1646 END Populate_Appl_MRs;
1647
1648 --------------------------------------------------------------------------------------------
1649 -- API Name : Mr_Title_Version_To_Id
1650 -- Purpose : To get mr_header_id out of mr_title and mr_version_number
1651 --------------------------------------------------------------------------------------------
1652 PROCEDURE Mr_Title_Version_To_Id
1653 (
1654 p_mr_title IN VARCHAR2,
1655 p_mr_version_number IN NUMBER,
1656 x_mr_header_id OUT NOCOPY NUMBER,
1657 x_return_status OUT NOCOPY VARCHAR2
1658 )
1659 AS
1660
1661 -- Cursor for getting mr_header_id out of mr_title and mr_version_number
1662 CURSOR header_id_csr_type(p_mr_title IN VARCHAR2, p_mr_version_number IN NUMBER) IS
1663 SELECT mr_header_id
1664 FROM ahl_mr_headers_app_v
1665 WHERE title = p_mr_title
1666 AND version_number = p_mr_version_number
1667 AND mr_status_code<>'TERMINATED'
1668 AND TRUNC(NVL(effective_to,SYSDATE+1))> TRUNC(SYSDATE);
1669
1670
1671 l_api_name CONSTANT VARCHAR2(30) := 'Mr_Title_Version_To_Id';
1672 l_api_version CONSTANT NUMBER := 1.0;
1673 l_header_id NUMBER;
1674
1675 BEGIN
1676 x_return_status:=FND_API.G_RET_STS_SUCCESS;
1677 OPEN header_id_csr_type(p_mr_title, p_mr_version_number);
1678 FETCH header_id_csr_type INTO l_header_id;
1679 CLOSE header_id_csr_type;
1680 IF l_header_id IS NULL THEN
1681 x_return_status:=FND_API.G_RET_STS_ERROR;
1682 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_NOT_EXISTS');
1683 FND_MESSAGE.SET_TOKEN('TITLE', p_mr_title);
1684 FND_MESSAGE.SET_TOKEN('VERSION', p_mr_version_number);
1685 FND_MSG_PUB.ADD;
1686 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1687 fnd_log.string
1688 (
1689 fnd_log.level_error,
1690 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1691 'MR Title and Version Number Combination is invalid'
1692 );
1693 END IF;
1694 ELSE
1695 x_mr_header_id := l_header_id;
1696 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1697 fnd_log.string
1698 (
1699 fnd_log.level_statement,
1700 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1701 'x_mr_header_id--->'||x_mr_header_id
1702 );
1703 END IF;
1704 END IF;
1705
1706 END Mr_Title_Version_To_Id;
1707
1708 --------------------------------------------------------------------------------------------
1709 -- API Name : Mr_Effectivity_Name_To_Id
1710 -- Purpose : To get mr_effectivity_id from mr_effectivity_name
1711 --------------------------------------------------------------------------------------------
1712
1713 PROCEDURE Mr_Effectivity_Name_To_Id
1714 (
1715 p_mr_header_id IN NUMBER,
1716 p_mr_effectivity_name IN VARCHAR2,
1717 x_mr_effectivity_id OUT NOCOPY NUMBER,
1718 x_return_status OUT NOCOPY VARCHAR2
1719 )
1720 AS
1721
1722 CURSOR effectivity_id_csr_type(p_mr_effectivity_name IN VARCHAR2,
1723 p_mr_header_id IN NUMBER)
1724 IS
1725 SELECT mr_effectivity_id
1726 FROM ahl_mr_effectivities_app_v
1727 WHERE name = p_mr_effectivity_name
1728 AND
1729 mr_header_id = p_mr_header_id;
1730
1731 l_api_name CONSTANT VARCHAR2(30) := 'Mr_Effectivity_Name_To_Id';
1732 l_api_version CONSTANT NUMBER := 1.0;
1733 l_mr_effectivity_id NUMBER;
1734
1735 BEGIN
1736 x_return_status:=FND_API.G_RET_STS_SUCCESS;
1737
1738 OPEN effectivity_id_csr_type(p_mr_effectivity_name,p_mr_header_id);
1739 FETCH effectivity_id_csr_type INTO l_mr_effectivity_id;
1740 CLOSE effectivity_id_csr_type;
1741 IF l_mr_effectivity_id IS NULL THEN
1742 x_return_status:=FND_API.G_RET_STS_ERROR;
1743 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_EFFEC_NOT_EXISTS');
1744 FND_MESSAGE.SET_TOKEN('RECORD', p_mr_effectivity_name);
1745 FND_MSG_PUB.ADD;
1746 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
1747 fnd_log.string
1748 (
1749 fnd_log.level_error,
1750 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1751 'MR Effectivity name specified is invalid'
1752 );
1753 END IF;
1754 ELSE
1755 x_mr_effectivity_id := l_mr_effectivity_id;
1756 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1757 fnd_log.string
1758 (
1759 fnd_log.level_statement,
1760 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1761 'x_mr_effectivity_id--->'||x_mr_effectivity_id
1762 );
1763 END IF;
1764
1765 END IF;
1766
1767 END Mr_Effectivity_Name_To_Id;
1768
1769
1770 FUNCTION check_mr_type
1771 (p_mr_header_id IN NUMBER) RETURN varchar2 IS
1772 l_activity varchar2(30);
1773 cursor l_check_mr_type_csr (c_mr_header_id IN NUMBER)
1774 is
1775 SELECT TYPE_CODE
1776 FROM AHL_MR_HEADERS_B
1777 WHERE mr_header_id = c_mr_header_id;
1778 begin
1779 open l_check_mr_type_csr(p_mr_header_id);
1780 FETCH l_check_mr_type_csr INTO l_activity;
1781
1782 if l_check_mr_type_csr%NOTFOUND THEN
1783 l_activity :=NULL;
1784 END IF;
1785 CLOSE l_check_mr_type_csr ;
1786 return l_activity;
1787 end check_mr_type;
1788
1789
1790
1791 FUNCTION check_mr_status
1792 (p_mr_header_id IN NUMBER) RETURN varchar2 IS
1793 l_status varchar2(30);
1794 cursor l_check_mr_status_csr (c_mr_header_id IN NUMBER)
1795 is
1796 SELECT mr_status_code
1797 FROM AHL_MR_HEADERS_B
1798 WHERE mr_header_id = c_mr_header_id
1799 AND trunc(nvl( effective_to, sysdate+1 ))>=trunc(sysdate);
1800 begin
1801 open l_check_mr_status_csr(p_mr_header_id);
1802 FETCH l_check_mr_status_csr INTO l_status;
1803
1804 if l_check_mr_status_csr%NOTFOUND THEN
1805 l_status :=NULL;
1806 END IF;
1807 CLOSE l_check_mr_status_csr ;
1808 return l_status;
1809 end check_mr_status;
1810
1811
1812 PROCEDURE validate_mr_pm_status
1813 (
1814 x_return_status OUT NOCOPY VARCHAR2,
1815 x_msg_data OUT NOCOPY VARCHAR2,
1816 p_mr_header_id IN NUMBER
1817 )
1818 IS
1819
1820 l_mr_status_code VARCHAR2(30);
1821
1822 CURSOR check_mr_status( c_mr_header_id NUMBER )
1823 IS
1824 SELECT mr_status_code
1825 FROM AHL_MR_HEADERS_B
1826 WHERE mr_header_id = c_mr_header_id;
1827
1828 BEGIN
1829 x_return_status := FND_API.G_RET_STS_SUCCESS;
1830
1831 IF ( p_mr_header_id IS NULL OR
1832 p_mr_header_id = FND_API.G_MISS_NUM ) THEN
1833 x_return_status := FND_API.G_RET_STS_ERROR;
1834 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
1835 RETURN;
1836 END IF;
1837
1838 OPEN check_mr_status( p_mr_header_id );
1839
1840 FETCH check_mr_status INTO
1841 l_mr_status_code;
1842
1843 IF check_mr_status%NOTFOUND THEN
1844 x_return_status := FND_API.G_RET_STS_ERROR;
1845 x_msg_data := 'AHL_FMP_INVALID_MR';
1846 CLOSE check_mr_status;
1847 RETURN;
1848 END IF;
1849
1850 IF ( l_mr_status_code <> 'DRAFT' AND
1851 l_mr_status_code <> 'APPROVAL_REJECTED' AND
1852 l_mr_status_code <> 'COMPLETE'
1853 ) THEN
1854 x_return_status := FND_API.G_RET_STS_ERROR;
1855 x_msg_data := 'AHL_FMP_INVALID_MR_STATUS';
1856 CLOSE check_mr_status;
1857 RETURN;
1858 END IF;
1859
1860 CLOSE check_mr_status;
1861 RETURN;
1862
1863 END validate_mr_pm_status;
1864
1865
1866 PROCEDURE validate_mr_type_program
1867 (
1868 x_return_status OUT NOCOPY VARCHAR2,
1869 x_msg_data OUT NOCOPY VARCHAR2,
1870 p_mr_header_id IN NUMBER,
1871 p_effectivity_id IN NUMBER,
1872 p_eff_obj_version IN NUMBER
1873 )
1874 IS
1875
1876 l_record_exists NUMBER;
1877
1878 CURSOR mr_type_program( c_effectivity_id NUMBER,c_eff_obj number,c_mr_header_id number)
1879 IS
1880 SELECT 1
1881 FROM DUAL
1882 WHERE
1883 EXISTS (
1884 SELECT 'x'
1885 FROM AHL_UNIT_EFFECTIVITIES_B unit,
1886 CSI_ITEM_INSTANCES csi,
1887 AHL_MR_EFFECTIVITIES eff
1888 where
1889 eff.inventory_item_id = csi.INVENTORY_ITEM_ID
1890 and unit.CSI_ITEM_INSTANCE_ID = csi.instance_id
1891 and unit.program_mr_header_id = c_mr_header_id
1892 and eff.mr_effectivity_id =c_effectivity_id
1893 and eff.object_version_number =c_eff_obj ) ;
1894
1895 BEGIN
1896 x_return_status := FND_API.G_RET_STS_SUCCESS;
1897
1898 IF ( p_mr_header_id IS NULL OR
1899 p_mr_header_id = FND_API.G_MISS_NUM ) THEN
1900 x_return_status := FND_API.G_RET_STS_ERROR;
1901 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
1902 RETURN;
1903 END IF;
1904
1905 OPEN mr_type_program( p_effectivity_id, p_eff_obj_version, p_mr_header_id );
1906 FETCH mr_type_program INTO l_record_exists;
1907
1908 IF mr_type_program%FOUND THEN
1909 x_return_status := FND_API.G_RET_STS_ERROR;
1910 x_msg_data := 'AHL_FMP_EFFECTIVITY_ITEM_PM';
1911 END IF;
1912
1913 CLOSE mr_type_program;
1914
1915 END validate_mr_type_program;
1916
1917
1918
1919 PROCEDURE validate_mr_type_activity
1920 (
1921 x_return_status OUT NOCOPY VARCHAR2,
1922 x_msg_data OUT NOCOPY VARCHAR2,
1923 p_effectivity_id IN NUMBER,
1924 p_eff_obj_version IN NUMBER
1925 )
1926 IS
1927
1928 l_record_exists NUMBER;
1929
1930 CURSOR mr_type_activity( c_effectivity_id NUMBER,c_eff_obj number)
1931 IS
1932 SELECT 1
1933 FROM DUAL
1934 WHERE EXISTS ( SELECT 'x'
1935 FROM AHL_UNIT_EFFECTIVITIES_B unit,
1936 CSI_ITEM_INSTANCES csi,
1937 AHL_MR_EFFECTIVITIES eff
1938 where
1939 eff.inventory_item_id = csi.INVENTORY_ITEM_ID
1940 and unit.CSI_ITEM_INSTANCE_ID = csi.instance_id
1941 and unit.mr_header_id =eff.mr_header_id
1942 and eff.mr_effectivity_id =c_effectivity_id
1943 and eff.object_version_number=c_eff_obj) ;
1944
1945 BEGIN
1946 x_return_status := FND_API.G_RET_STS_SUCCESS;
1947
1948
1949 OPEN mr_type_activity( p_effectivity_id, p_eff_obj_version);
1950 FETCH mr_type_activity INTO l_record_exists;
1951
1952 IF mr_type_activity%FOUND THEN
1953 x_return_status := FND_API.G_RET_STS_ERROR;
1954 x_msg_data := 'AHL_FMP_EFFECTIVITY_ITEM_PM';
1955 END IF;
1956
1957 CLOSE mr_type_activity;
1958
1959 END validate_mr_type_activity;
1960
1961 -- Start of Comments
1962 -- Procedure name : validate_owner
1963 -- Type : Private
1964 -- Pre-reqs :
1965 -- Function :
1966 -- Parameters :
1967 --
1968 -- Standard IN Parameters :
1969 -- None
1970 --
1971 -- Standard OUT Parameters :
1972 -- x_return_status VARCHAR2 Required
1973 -- x_msg_data VARCHAR2 Required
1974 --
1975 -- validate_owner IN parameters:
1976 -- p_inventory_item_id NUMBER Default NULL
1977 -- p_relationship_id NUMBER Default NULL
1978 -- p_owner VARCHAR2 Default NULL
1979 --
1980 -- validate_manufacturer IN OUT parameters:
1981 -- p_x_owner_id NUMBER
1982 --
1983 -- validate_manufacturer OUT parameters:
1984 -- None.
1985 --
1986 --
1987 -- Version :
1988 -- Current version 1.0
1989 --
1990 -- End of Comments
1991
1992 PROCEDURE validate_owner
1993 (
1994 x_return_status OUT NOCOPY VARCHAR2,
1995 x_msg_data OUT NOCOPY VARCHAR2,
1996 p_owner IN VARCHAR2 := NULL,
1997 p_x_owner_id IN OUT NOCOPY NUMBER
1998 ) IS
1999 l_msg_data VARCHAR2(2000);
2000 l_return_status VARCHAR2(1);
2001 l_owner_id NUMBER;
2002
2003
2004 CURSOR get_owner_rec_from_value ( c_owner VARCHAR2)
2005 IS
2006 select DISTINCT OWN.owner_id
2007 from ahl_owner_details_v OWN
2008 where upper(owner_name) like upper(c_owner);
2009
2010
2011 CURSOR get_owner_rec_from_id ( c_owner_id NUMBER)
2012 IS
2013 select DISTINCT OWN.owner_id
2014 from ahl_owner_details_v OWN
2015 where owner_id = c_owner_id;
2016
2017 CURSOR get_owner_rec_from_id_val ( c_owner_id NUMBER,c_owner VARCHAR2)
2018 IS
2019 select DISTINCT OWN.owner_id
2020 from ahl_owner_details_v OWN
2021 where owner_id = c_owner_id
2022 and upper(owner_name) like upper(c_owner);
2023
2024 BEGIN
2025 x_return_status := FND_API.G_RET_STS_SUCCESS;
2026
2027 IF ( ( p_owner IS NULL OR
2028 p_owner = FND_API.G_MISS_CHAR ) AND
2029 ( p_x_owner_id IS NULL OR
2030 p_x_owner_id = FND_API.G_MISS_NUM ) ) THEN
2031 x_return_status := FND_API.G_RET_STS_ERROR;
2032 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
2033 RETURN;
2034 END IF;
2035
2036
2037 IF ( ( p_owner IS NULL OR
2038 p_owner = FND_API.G_MISS_CHAR ) AND
2039 ( p_x_owner_id IS NOT NULL AND
2040 p_x_owner_id <> FND_API.G_MISS_NUM ) ) THEN
2041
2042 OPEN get_owner_rec_from_id( p_x_owner_id );
2043
2044 LOOP
2045 FETCH get_owner_rec_from_id INTO
2046 l_owner_id;
2047 EXIT WHEN get_owner_rec_from_id%NOTFOUND;
2048 END LOOP;
2049
2050 IF ( get_owner_rec_from_id%ROWCOUNT = 0 ) THEN
2051 x_return_status := FND_API.G_RET_STS_ERROR;
2052 x_msg_data := 'AHL_FMP_INVALID_OWNER';
2053 ELSIF ( get_owner_rec_from_id%ROWCOUNT = 1 ) THEN
2054 p_x_owner_id := l_owner_id;
2055 ELSE
2056 x_return_status := FND_API.G_RET_STS_ERROR;
2057 x_msg_data := 'AHL_FMP_INV_TOO_MANY_OWNERS';
2058 END IF;
2059 CLOSE get_owner_rec_from_id;
2060
2061 ELSIF ( p_owner IS NOT NULL AND
2062 p_owner <> FND_API.G_MISS_CHAR ) THEN
2063
2064 OPEN get_owner_rec_from_value( p_owner );
2065
2066 LOOP
2067
2068 FETCH get_owner_rec_from_value INTO
2069 l_owner_id;
2070 EXIT WHEN get_owner_rec_from_value%NOTFOUND;
2071
2072 END LOOP;
2073 IF ( get_owner_rec_from_value%ROWCOUNT = 0 ) THEN
2074 x_return_status := FND_API.G_RET_STS_ERROR;
2075 x_msg_data := 'AHL_FMP_INVALID_OWNER';
2076 ELSIF ( get_owner_rec_from_value%ROWCOUNT = 1 ) THEN
2077 p_x_owner_id := l_owner_id;
2078 ELSIF ( get_owner_rec_from_value%ROWCOUNT > 1 ) THEN
2079 IF( p_x_owner_id IS NOT NULL AND
2080 p_x_owner_id <> FND_API.G_MISS_NUM ) THEN
2081
2082 OPEN get_owner_rec_from_id_val( p_x_owner_id,p_owner );
2083
2084 LOOP
2085 FETCH get_owner_rec_from_id_val INTO
2086 l_owner_id;
2087 EXIT WHEN get_owner_rec_from_id_val%NOTFOUND;
2088 END LOOP;
2089
2090 IF ( get_owner_rec_from_id_val%ROWCOUNT = 0 ) THEN
2091 x_return_status := FND_API.G_RET_STS_ERROR;
2092 x_msg_data := 'AHL_FMP_INVALID_OWNER';
2093 ELSIF ( get_owner_rec_from_id_val%ROWCOUNT = 1 ) THEN
2094 p_x_owner_id := l_owner_id;
2095 ELSE
2096 x_return_status := FND_API.G_RET_STS_ERROR;
2097 x_msg_data := 'AHL_FMP_INV_TOO_MANY_OWNERS';
2098 END IF;
2099 CLOSE get_owner_rec_from_id_val;
2100
2101 ELSE
2102 x_return_status := FND_API.G_RET_STS_ERROR;
2103 x_msg_data := 'AHL_FMP_INV_TOO_MANY_OWNERS';
2104 END IF;
2105 END IF;
2106 CLOSE get_owner_rec_from_value;
2107
2108 END IF;
2109
2110 END validate_owner;
2111
2112 -- Start of Comments
2113 -- Procedure name : validate_location
2114 -- Type : Private
2115 -- Pre-reqs :
2116 -- Function :
2117 -- Parameters :
2118 --
2119 -- Standard IN Parameters :
2120 -- None
2121 --
2122 -- Standard OUT Parameters :
2123 -- x_return_status VARCHAR2 Required
2124 -- x_msg_data VARCHAR2 Required
2125 --
2126 -- validate_country IN parameters:
2127 -- p_location VARCHAR2 Default NULL
2128 --
2129 -- validate_location IN OUT parameters:
2130 -- p_x_location_type_code VARCHAR2
2131 --
2132 -- validate_country OUT parameters:
2133 -- None.
2134 --
2135 --
2136 -- Version :
2137 -- Current version 1.0
2138 --
2139 -- End of Comments
2140
2141 PROCEDURE validate_location
2142 (
2143 x_return_status OUT NOCOPY VARCHAR2,
2144 x_msg_data OUT NOCOPY VARCHAR2,
2145 p_location IN VARCHAR2 := NULL,
2146 p_x_location_type_code IN OUT NOCOPY VARCHAR2
2147 )IS
2148 l_location_type_code VARCHAR2(30);
2149
2150 CURSOR get_rec_from_value ( c_location VARCHAR2 )
2151 IS
2152 select lookup_code from csi_lookups
2153 where lookup_type='CSI_INST_LOCATION_SOURCE_CODE'
2154 and upper(meaning) like upper(c_location);
2155
2156 CURSOR get_rec_from_id ( c_location_type_code VARCHAR2 )
2157 IS
2158 select lookup_code from csi_lookups
2159 where lookup_type='CSI_INST_LOCATION_SOURCE_CODE'
2160 and lookup_code = c_location_type_code;
2161
2162 BEGIN
2163 x_return_status := FND_API.G_RET_STS_SUCCESS;
2164
2165 IF ( ( p_location IS NULL OR
2166 p_location = FND_API.G_MISS_CHAR ) AND
2167 ( p_x_location_type_code IS NULL OR
2168 p_x_location_type_code = FND_API.G_MISS_CHAR ) ) THEN
2169 x_return_status := FND_API.G_RET_STS_ERROR;
2170 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
2171 RETURN;
2172 END IF;
2173
2174 IF ( ( p_location IS NULL OR
2175 p_location = FND_API.G_MISS_CHAR ) AND
2176 ( p_x_location_type_code IS NOT NULL AND
2177 p_x_location_type_code <> FND_API.G_MISS_CHAR ) ) THEN
2178
2179 OPEN get_rec_from_id( p_x_location_type_code );
2180
2181 FETCH get_rec_from_id INTO
2182 l_location_type_code;
2183
2184 IF get_rec_from_id%NOTFOUND THEN
2185 x_return_status := FND_API.G_RET_STS_ERROR;
2186 x_msg_data := 'AHL_FMP_INVALID_LOCATION';
2187 END IF;
2188
2189 CLOSE get_rec_from_id;
2190 RETURN;
2191
2192 END IF;
2193
2194 IF ( p_location IS NOT NULL AND
2195 p_location <> FND_API.G_MISS_CHAR ) THEN
2196
2197 OPEN get_rec_from_value( p_location );
2198
2199 LOOP
2200 FETCH get_rec_from_value INTO
2201 l_location_type_code;
2202
2203 EXIT WHEN get_rec_from_value%NOTFOUND;
2204
2205 IF ( l_location_type_code = p_x_location_type_code ) THEN
2206 CLOSE get_rec_from_value;
2207 RETURN;
2208 END IF;
2209
2210 END LOOP;
2211
2212 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
2213 x_return_status := FND_API.G_RET_STS_ERROR;
2214 x_msg_data := 'AHL_FMP_INVALID_LOCATION';
2215 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
2216 p_x_location_type_code := l_location_type_code;
2217 ELSE
2218 x_return_status := FND_API.G_RET_STS_ERROR;
2219 x_msg_data := 'AHL_FMP_TOO_MANY_LOCATIONS';
2220 END IF;
2221
2222 CLOSE get_rec_from_value;
2223 RETURN;
2224
2225 END IF;
2226
2227 END validate_location;
2228
2229 -- Start of Comments
2230 -- Procedure name : validate_csi_ext_attribute
2231 -- Type : Private
2232 -- Pre-reqs :
2233 -- Function :
2234 -- Parameters :
2235 --
2236 -- Standard IN Parameters :
2237 -- None
2238 --
2239 -- Standard OUT Parameters :
2240 -- x_return_status VARCHAR2 Required
2241 -- x_msg_data VARCHAR2 Required
2242 --
2243 -- validate_country IN parameters:
2244 -- p_csi_attribute_name VARCHAR2 Default NULL
2245 --
2246 -- validate_country IN OUT parameters:
2247 -- p_x_csi_attribute_code VARCHAR2
2248 --
2249 -- validate_csi_ext_attribute OUT parameters:
2250 -- None.
2251 --
2252 --
2253 -- Version :
2254 -- Current version 1.0
2255 --
2256 -- End of Comments
2257
2258 PROCEDURE validate_csi_ext_attribute
2259 (
2260 x_return_status OUT NOCOPY VARCHAR2,
2261 x_msg_data OUT NOCOPY VARCHAR2,
2262 p_csi_attribute_name IN VARCHAR2 := NULL,
2263 p_x_csi_attribute_code IN OUT NOCOPY VARCHAR2
2264 )IS
2265 l_csi_attribute_code VARCHAR2(30);
2266
2267 CURSOR get_rec_from_value ( c_csi_attribute_name VARCHAR2 )
2268 IS
2269 select distinct CIEA.ATTRIBUTE_CODE from CSI_I_EXTENDED_ATTRIBS CIEA
2270 where upper(attribute_name) like upper(c_csi_attribute_name);
2271
2272 CURSOR get_rec_from_id ( c_csi_attribute_code VARCHAR2 )
2273 IS
2274 select distinct CIEA.ATTRIBUTE_CODE from CSI_I_EXTENDED_ATTRIBS CIEA
2275 where CIEA.ATTRIBUTE_CODE = c_csi_attribute_code;
2276
2277 BEGIN
2278 x_return_status := FND_API.G_RET_STS_SUCCESS;
2279
2280 IF ( ( p_csi_attribute_name IS NULL OR
2281 p_csi_attribute_name = FND_API.G_MISS_CHAR ) AND
2282 ( p_x_csi_attribute_code IS NULL OR
2283 p_x_csi_attribute_code = FND_API.G_MISS_CHAR ) ) THEN
2284 x_return_status := FND_API.G_RET_STS_ERROR;
2285 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
2286 RETURN;
2287 END IF;
2288
2289 IF ( ( p_csi_attribute_name IS NULL OR
2290 p_csi_attribute_name = FND_API.G_MISS_CHAR ) AND
2291 ( p_x_csi_attribute_code IS NOT NULL AND
2292 p_x_csi_attribute_code <> FND_API.G_MISS_CHAR ) ) THEN
2293
2294 OPEN get_rec_from_id( p_x_csi_attribute_code );
2295
2296 FETCH get_rec_from_id INTO
2297 l_csi_attribute_code;
2298
2299 IF get_rec_from_id%NOTFOUND THEN
2300 x_return_status := FND_API.G_RET_STS_ERROR;
2301 x_msg_data := 'AHL_FMP_INVALID_EXT_ATTR';
2302 END IF;
2303
2304 CLOSE get_rec_from_id;
2305 RETURN;
2306
2307 END IF;
2308
2309 IF ( p_csi_attribute_name IS NOT NULL AND
2310 p_csi_attribute_name <> FND_API.G_MISS_CHAR ) THEN
2311
2312 OPEN get_rec_from_value( p_csi_attribute_name );
2313
2314 LOOP
2315 FETCH get_rec_from_value INTO
2316 l_csi_attribute_code;
2317
2318 EXIT WHEN get_rec_from_value%NOTFOUND;
2319
2320 IF ( l_csi_attribute_code = p_x_csi_attribute_code ) THEN
2321 CLOSE get_rec_from_value;
2322 RETURN;
2323 END IF;
2324
2325 END LOOP;
2326
2327 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
2328 x_return_status := FND_API.G_RET_STS_ERROR;
2329 x_msg_data := 'AHL_FMP_INVALID_EXT_ATTR';
2330 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
2331 p_x_csi_attribute_code := l_csi_attribute_code;
2332 ELSE
2333 x_return_status := FND_API.G_RET_STS_ERROR;
2334 x_msg_data := 'AHL_FMP_TOO_MANY_EXT_ATTRS';
2335 END IF;
2336
2337 CLOSE get_rec_from_value;
2338 RETURN;
2339
2340 END IF;
2341
2342 END validate_csi_ext_attribute;
2343
2344 -- Check to see whether a VARCHAR2 contains only numeric values
2345 FUNCTION sn_num(
2346 p_serial_number IN VARCHAR2
2347 ) RETURN BOOLEAN IS
2348 i NUMBER := 0;
2349
2350 BEGIN
2351 LOOP
2352 i := i+1;
2353 EXIT WHEN SUBSTR(p_serial_number,-i,1) NOT IN ('0','1','2','3','4','5','6','7','8','9')
2354 OR i > LENGTH(p_serial_number);
2355 END LOOP;
2356 IF i = LENGTH(p_serial_number) + 1 THEN
2357 RETURN TRUE;
2358 ELSE
2359 RETURN FALSE;
2360 END IF;
2361 END sn_num;
2362
2363 -- sansatpa - to validate fleet values
2364 PROCEDURE validate_fleet_node
2365 (
2366 x_return_status OUT NOCOPY VARCHAR2,
2367 x_msg_data OUT NOCOPY VARCHAR2,
2368 p_fleet_name IN VARCHAR2 := NULL,
2369 p_x_fleet_header_id IN OUT NOCOPY NUMBER
2370 )
2371 IS
2372
2373 l_fleet_header_id NUMBER;
2374
2375 CURSOR get_fleet_rec_from_name ( c_fleet_node_name VARCHAR2 )
2376 IS
2377 SELECT FLEET_HEADER_ID
2378 FROM AHL_FLEET_HEADERS_B
2379 WHERE name = c_fleet_node_name;
2380
2381 CURSOR get_fleet_rec_from_id ( c_fleet_node_id NUMBER )
2382 IS
2383 SELECT FLEET_HEADER_ID
2384 FROM AHL_FLEET_HEADERS_B
2385 WHERE FLEET_HEADER_ID = c_fleet_node_id;
2386
2387 BEGIN
2388 x_return_status := FND_API.G_RET_STS_SUCCESS;
2389
2390 IF ( ( p_fleet_name IS NULL OR
2391 p_fleet_name = FND_API.G_MISS_CHAR ) AND
2392 ( p_x_fleet_header_id IS NULL OR
2393 p_x_fleet_header_id = FND_API.G_MISS_NUM ) ) THEN
2394 x_return_status := FND_API.G_RET_STS_ERROR;
2395 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
2396 RETURN;
2397 END IF;
2398
2399 IF ( ( p_fleet_name IS NULL OR
2400 p_fleet_name = FND_API.G_MISS_CHAR ) AND
2401 ( p_x_fleet_header_id IS NOT NULL AND
2402 p_x_fleet_header_id <> FND_API.G_MISS_NUM ) ) THEN
2403
2404 OPEN get_fleet_rec_from_id( p_x_fleet_header_id );
2405
2406 FETCH get_fleet_rec_from_id INTO
2407 l_fleet_header_id;
2408
2409 IF get_fleet_rec_from_id%NOTFOUND THEN
2410 x_return_status := FND_API.G_RET_STS_ERROR;
2411 x_msg_data := 'AHL_FMP_INVALID_FLEET_NODE';
2412 END IF;
2413
2414 CLOSE get_fleet_rec_from_id;
2415 RETURN;
2416
2417 END IF;
2418
2419 IF ( p_fleet_name IS NOT NULL AND
2420 p_fleet_name <> FND_API.G_MISS_CHAR ) THEN
2421
2422 OPEN get_fleet_rec_from_name( p_fleet_name );
2423
2424 LOOP
2425 FETCH get_fleet_rec_from_name INTO
2426 l_fleet_header_id;
2427
2428 EXIT WHEN get_fleet_rec_from_name%NOTFOUND;
2429
2430 IF ( l_fleet_header_id = p_x_fleet_header_id ) THEN
2431 CLOSE get_fleet_rec_from_name;
2432 RETURN;
2433 END IF;
2434
2435 END LOOP;
2436
2437 IF ( get_fleet_rec_from_name%ROWCOUNT = 0 ) THEN
2438 x_return_status := FND_API.G_RET_STS_ERROR;
2439 x_msg_data := 'AHL_FMP_INVALID_FLEET_NODE';
2440 ELSIF ( get_fleet_rec_from_name%ROWCOUNT = 1 ) THEN
2441 p_x_fleet_header_id := l_fleet_header_id;
2442 ELSE
2443 x_return_status := FND_API.G_RET_STS_ERROR;
2444 x_msg_data := 'AHL_FMP_TOO_MANY_FLEET_NODES';
2445 END IF;
2446
2447 CLOSE get_fleet_rec_from_name;
2448 RETURN;
2449
2450 END IF;
2451
2452 END validate_fleet_node;
2453
2454 END ahl_fmp_common_pvt;