[Home] [Help]
PACKAGE BODY: APPS.AHL_FMP_COMMON_PVT
Source
1 PACKAGE BODY ahl_fmp_common_pvt AS
2 /* $Header: AHLVFCMB.pls 120.7 2008/03/24 08:07:25 pdoki 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 -- Procedure to validate lookups
8 PROCEDURE validate_lookup
9 (
10 x_return_status OUT NOCOPY VARCHAR2,
11 x_msg_data OUT NOCOPY VARCHAR2,
12 p_lookup_type IN FND_LOOKUPS.lookup_type%TYPE,
13 p_lookup_meaning IN FND_LOOKUPS.meaning%TYPE,
14 p_x_lookup_code IN OUT NOCOPY FND_LOOKUPS.lookup_code%TYPE
15 )
16 IS
17
18 l_lookup_code FND_LOOKUPS.lookup_code%TYPE;
19
20 CURSOR get_rec_from_value ( c_lookup_type FND_LOOKUPS.lookup_type%TYPE,
21 c_lookup_meaning FND_LOOKUPS.meaning%TYPE )
22 IS
23 SELECT lookup_code
24 FROM FND_LOOKUP_VALUES_VL
25 WHERE lookup_type = c_lookup_type
26 AND meaning = c_lookup_meaning
27 AND SYSDATE BETWEEN NVL( start_date_active, SYSDATE ) AND
28 NVL( end_date_active, SYSDATE );
29
30 CURSOR get_rec_from_id ( c_lookup_type FND_LOOKUPS.lookup_type%TYPE,
31 c_lookup_code FND_LOOKUPS.lookup_code%TYPE )
32 IS
33 SELECT lookup_code
34 FROM FND_LOOKUP_VALUES_VL
35 WHERE lookup_type = c_lookup_type
36 AND lookup_code = c_lookup_code
37 AND SYSDATE BETWEEN NVL( start_date_active, SYSDATE ) AND
38 NVL( end_date_active, SYSDATE );
39
40 BEGIN
41 x_return_status := FND_API.G_RET_STS_SUCCESS;
42
43 IF ( p_lookup_type IS NULL OR
44 p_lookup_type = FND_API.G_MISS_CHAR ) THEN
45 x_return_status := FND_API.G_RET_STS_ERROR;
46 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
47 RETURN;
48 END IF;
49
50 IF ( ( p_lookup_meaning IS NULL OR
51 p_lookup_meaning = FND_API.G_MISS_CHAR ) AND
52 ( p_x_lookup_code IS NULL OR
53 p_x_lookup_code = 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 NOT NULL AND
62 p_x_lookup_code <> FND_API.G_MISS_CHAR ) ) THEN
63
64 OPEN get_rec_from_id( p_lookup_type, p_x_lookup_code );
65
66 FETCH get_rec_from_id INTO
67 l_lookup_code;
68
69 IF get_rec_from_id%NOTFOUND THEN
70 x_return_status := FND_API.G_RET_STS_ERROR;
71 x_msg_data := 'AHL_COM_INVALID_LOOKUP';
72 END IF;
73
74 CLOSE get_rec_from_id;
75 RETURN;
76
77 END IF;
78
79 IF ( p_lookup_meaning IS NOT NULL AND
80 p_lookup_meaning <> FND_API.G_MISS_CHAR ) THEN
81
82 OPEN get_rec_from_value( p_lookup_type, p_lookup_meaning );
83
84 LOOP
85 FETCH get_rec_from_value INTO
86 l_lookup_code;
87
88 EXIT WHEN get_rec_from_value%NOTFOUND;
89
90 IF ( l_lookup_code = p_x_lookup_code ) THEN
91 CLOSE get_rec_from_value;
92 RETURN;
93 END IF;
94
95 END LOOP;
96
97 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
98 x_return_status := FND_API.G_RET_STS_ERROR;
99 x_msg_data := 'AHL_COM_INVALID_LOOKUP';
100 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
101 p_x_lookup_code := l_lookup_code;
102 ELSE
103 x_return_status := FND_API.G_RET_STS_ERROR;
104 x_msg_data := 'AHL_COM_TOO_MANY_LOOKUPS';
105 END IF;
106
107 CLOSE get_rec_from_value;
108 RETURN;
109
110 END IF;
111
112 END validate_lookup;
113
114 -- Procedure to validate Item
115 PROCEDURE validate_item
116 (
117 x_return_status OUT NOCOPY VARCHAR2,
118 x_msg_data OUT NOCOPY VARCHAR2,
119 p_item_number IN MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE,
120 p_x_inventory_item_id IN OUT NOCOPY MTL_SYSTEM_ITEMS.inventory_item_id%TYPE
121 )
122 IS
123
124 CURSOR get_rec_from_value ( c_item_number MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE )
125 IS
126 SELECT DISTINCT MI.inventory_item_id,
127 MI.inventory_item_flag,
128 MI.eng_item_flag,
129 MI.build_in_wip_flag,
130 MI.wip_supply_type,
131 MI.eam_item_type,
132 MI.comms_nl_trackable_flag,
133 MI.serv_req_enabled_code
134 FROM MTL_PARAMETERS MP, MTL_SYSTEM_ITEMS_KFV MI
135 WHERE DECODE(G_APPLN_USAGE,'PM','Y',MP.eam_enabled_flag )='Y'
136 AND MP.organization_id = MI.organization_id
137 AND MI.concatenated_segments = c_item_number
138 AND MI.enabled_flag = 'Y'
139 AND SYSDATE BETWEEN NVL( MI.start_date_active, SYSDATE )
140 AND NVL( MI.end_date_active, SYSDATE );
141
142 l_item_rec2 get_rec_from_value%rowtype;
143
144 CURSOR get_rec_from_id ( c_inventory_item_id MTL_SYSTEM_ITEMS.inventory_item_id%TYPE )
145 IS
146 SELECT DISTINCT MI.inventory_item_id,
147 MI.inventory_item_flag,
148 MI.eng_item_flag,
149 MI.build_in_wip_flag,
150 MI.wip_supply_type,
151 MI.eam_item_type,
152 MI.comms_nl_trackable_flag,
153 MI.serv_req_enabled_code
154 FROM MTL_PARAMETERS MP, MTL_SYSTEM_ITEMS_KFV MI
155 WHERE DECODE(G_APPLN_USAGE,'PM','Y',MP.eam_enabled_flag )='Y'
156 AND MP.organization_id = MI.organization_id
157 AND MI.inventory_item_id = c_inventory_item_id
158 AND MI.enabled_flag = 'Y'
159 AND SYSDATE BETWEEN NVL( MI.start_date_active, SYSDATE )
160 AND NVL( MI.end_date_active, SYSDATE );
161 l_item_rec1 get_rec_from_id%rowtype;
162 l_rec_found VARCHAR2(1):=FND_API.G_FALSE;
163 L_REC_COUNT NUMBER:=0;
164 BEGIN
165 x_return_status := FND_API.G_RET_STS_SUCCESS;
166
167 IF ( ( p_item_number IS NULL OR
168 p_item_number = FND_API.G_MISS_CHAR ) AND
169 ( p_x_inventory_item_id IS NULL OR
170 p_x_inventory_item_id = FND_API.G_MISS_NUM ) ) THEN
171 x_return_status := FND_API.G_RET_STS_ERROR;
172 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
173 RETURN;
174 END IF;
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 NOT NULL AND
179 p_x_inventory_item_id <> FND_API.G_MISS_NUM ) )
180 THEN
181 OPEN get_rec_from_id( p_x_inventory_item_id );
182 LOOP
183 FETCH get_rec_from_id INTO l_item_rec1;
184 EXIT WHEN get_rec_from_id%NOTFOUND;
185 L_REC_COUNT:=L_REC_COUNT+1;
186
187 IF G_APPLN_USAGE<>'PM'
188 THEN
189 IF l_item_rec1.inventory_item_flag='Y' and
190 l_item_rec1.eng_item_flag='N' and
191 l_item_rec1.build_in_wip_flag='Y' and
192 l_item_rec1.wip_supply_type=1 and
193 l_item_rec1.eam_item_type=3 and
194 l_item_rec1.comms_nl_trackable_flag='Y'
195 THEN
196 l_rec_found:=FND_API.G_TRUE;
197 END IF;
198 ELSIF G_APPLN_USAGE='PM'
199 THEN
200 IF l_item_rec1.comms_nl_trackable_flag='Y' and
201 l_item_rec1.serv_req_enabled_code='E'
202 THEN
203 l_rec_found:=FND_API.G_TRUE;
204 END IF;
205 END IF;
206 EXIT WHEN l_rec_found=FND_API.G_TRUE;
207 END LOOP;
208 CLOSE get_rec_from_id;
209
210 IF L_REC_COUNT=0
211 THEN
212 x_return_status := FND_API.G_RET_STS_ERROR;
213 x_msg_data := 'AHL_FMP_INVALID_ITEM';
214 RETURN;
215 END IF;
216
217 IF l_rec_found=FND_API.G_FALSE
218 THEN
219 x_return_status := FND_API.G_RET_STS_ERROR;
220 x_msg_data := 'AHL_FMP_NOT_JOB_ITEM';
221 END IF;
222 RETURN;
223
224 ELSIF p_item_number IS NOT NULL AND
225 p_item_number <> FND_API.G_MISS_CHAR
226 THEN
227 L_REC_COUNT:=0;
228 OPEN get_rec_from_value( p_item_number );
229 LOOP
230 FETCH get_rec_from_value INTO L_ITEM_REC2;
231
232 EXIT WHEN get_rec_from_value%NOTFOUND;
233 L_REC_COUNT:=L_REC_COUNT+1;
234 p_x_inventory_item_id := l_item_rec2.inventory_item_id;
235
236 IF G_APPLN_USAGE<>'PM' -- NOT PM MODE
237 THEN
238 IF l_item_rec2.inventory_item_flag='Y' and
239 l_item_rec2.eng_item_flag='N' and
240 l_item_rec2.build_in_wip_flag='Y' and
241 l_item_rec2.wip_supply_type=1 and
242 l_item_rec2.eam_item_type=3 and
243 l_item_rec2.comms_nl_trackable_flag='Y'
244 THEN
245 l_rec_found:=FND_API.G_TRUE;
246 END IF;
247 ELSIF G_APPLN_USAGE='PM' -- PM MODE
248 THEN
249 IF l_item_rec2.comms_nl_trackable_flag='Y' and
250 l_item_rec2.serv_req_enabled_code='E'
251 THEN
252 l_rec_found:=FND_API.G_TRUE;
253 END IF;
254 END IF;
255
256 END LOOP;
257
258 CLOSE get_rec_from_value;
259
260 IF L_REC_COUNT=0
261 THEN
262 x_return_status := FND_API.G_RET_STS_ERROR;
263 x_msg_data := 'AHL_FMP_INVALID_ITEM';
264 RETURN;
265 END IF;
266
267 IF l_rec_found=FND_API.G_FALSE
268 THEN
269 x_return_status := FND_API.G_RET_STS_ERROR;
270 x_msg_data := 'AHL_FMP_NOT_JOB_ITEM';
271 END IF;
272
273 RETURN;
274 END IF;
275 -- END OF ITEM NUMBER CHECK
276 END validate_item;
277
278
279
280
281
282 PROCEDURE validate_pc_node
283 (
284 x_return_status OUT NOCOPY VARCHAR2,
285 x_msg_data OUT NOCOPY VARCHAR2,
286 p_pc_node_name IN VARCHAR2 := NULL,
287 p_x_pc_node_id IN OUT NOCOPY NUMBER
288 )
289 IS
290
291 l_pc_node_id NUMBER;
292
293 CURSOR get_rec_from_value ( c_pc_node_name VARCHAR2 )
294 IS
295 SELECT pc_node_id
296 FROM AHL_PC_NODES_B
297 WHERE name = c_pc_node_name;
298
299 CURSOR get_rec_from_id ( c_pc_node_id NUMBER )
300 IS
301 SELECT pc_node_id
302 FROM AHL_PC_NODES_B
303 WHERE pc_node_id = c_pc_node_id;
304
305 BEGIN
306 x_return_status := FND_API.G_RET_STS_SUCCESS;
307
308 IF ( ( p_pc_node_name IS NULL OR
309 p_pc_node_name = FND_API.G_MISS_CHAR ) AND
310 ( p_x_pc_node_id IS NULL OR
311 p_x_pc_node_id = FND_API.G_MISS_NUM ) ) THEN
312 x_return_status := FND_API.G_RET_STS_ERROR;
313 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
314 RETURN;
315 END IF;
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 NOT NULL AND
320 p_x_pc_node_id <> FND_API.G_MISS_NUM ) ) THEN
321
322 OPEN get_rec_from_id( p_x_pc_node_id );
323
324 FETCH get_rec_from_id INTO
325 l_pc_node_id;
326
327 IF get_rec_from_id%NOTFOUND THEN
328 x_return_status := FND_API.G_RET_STS_ERROR;
329 x_msg_data := 'AHL_FMP_INVALID_PC_NODE';
330 END IF;
331
332 CLOSE get_rec_from_id;
333 RETURN;
334
335 END IF;
336
337 IF ( p_pc_node_name IS NOT NULL AND
338 p_pc_node_name <> FND_API.G_MISS_CHAR ) THEN
339
340 OPEN get_rec_from_value( p_pc_node_name );
341
342 LOOP
343 FETCH get_rec_from_value INTO
344 l_pc_node_id;
345
346 EXIT WHEN get_rec_from_value%NOTFOUND;
347
348 IF ( l_pc_node_id = p_x_pc_node_id ) THEN
349 CLOSE get_rec_from_value;
350 RETURN;
351 END IF;
352
353 END LOOP;
354
355 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
356 x_return_status := FND_API.G_RET_STS_ERROR;
357 x_msg_data := 'AHL_FMP_INVALID_PC_NODE';
358 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
359 p_x_pc_node_id := l_pc_node_id;
360 ELSE
361 x_return_status := FND_API.G_RET_STS_ERROR;
362 x_msg_data := 'AHL_FMP_TOO_MANY_PC_NODES';
363 END IF;
364
365 CLOSE get_rec_from_value;
366 RETURN;
367
368 END IF;
369
370 END validate_pc_node;
371
372 /*pdoki commented for Bug 6032303 - Start
373 PROCEDURE validate_position
374 (
375 x_return_status OUT NOCOPY VARCHAR2,
376 x_msg_data OUT NOCOPY VARCHAR2,
377 p_position_ref_meaning IN VARCHAR2 := NULL,
378 p_x_relationship_id IN OUT NOCOPY NUMBER
379 )
380 IS
381
382 l_relationship_id NUMBER;
383
384 l_valid_rec_found BOOLEAN := FALSE;
385 l_posn_meaning FND_LOOKUPS.meaning%TYPE;
386 l_junk VARCHAR2(1);
387 */
388 /*CURSOR get_rec_from_value ( c_position_ref_meaning VARCHAR2 )
389 IS
390 SELECT DISTINCT relationship_id,
391 NVL( active_start_date, SYSDATE ),
392 NVL( active_end_date, SYSDATE + 1 )
393 FROM AHL_MASTER_CONFIG_DETAILS_V
394 WHERE position_ref_meaning = c_position_ref_meaning;
395
396 CURSOR get_rec_from_id ( c_relationship_id NUMBER )
397 IS
398 SELECT DISTINCT relationship_id,
399 NVL( active_start_date, SYSDATE ),
400 NVL( active_end_date, SYSDATE + 1 )
401 FROM AHL_RELATIONSHIPS_VL
402 WHERE relationship_id = c_relationship_id;
403 */
404
405 -- 11.5.10 changes for MC
406 /*
407 CURSOR get_rec_from_id ( c_relationship_id NUMBER )
408 IS
409 SELECT relationship_id
410 FROM ahl_mc_relationships mcr,
411 ahl_mc_headers_b mch, ahl_mc_path_position_nodes mcp
412 where mch.mc_header_id = mcr.mc_header_id and
413 mch.mc_id = mcp.mc_id and
414 mch.version_number = nvl(mcp.version_number, mch.version_number) and
415 mcr.position_key = mcp.position_key and
416 mcp.sequence = (select max(sequence)
417 from ahl_mc_path_position_nodes
418 where path_position_id = nvl(c_relationship_id,'-1'))
419 and mcp.path_position_id = nvl(c_relationship_id,'-1');
420
421 CURSOR check_reln_valid_csr (c_relationship_id IN NUMBER)
422 IS
423 SELECT 'x'
424 FROM ahl_mc_relationships
425 WHERE parent_relationship_id IS NULL
426 START WITH relationship_id = c_relationship_id
427 AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
428 CONNECT BY PRIOR parent_relationship_id = relationship_id
429 AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1));
430
431
432 BEGIN
433 x_return_status := FND_API.G_RET_STS_SUCCESS;
434
435 IF ( ( p_position_ref_meaning IS NULL OR
436 p_position_ref_meaning = FND_API.G_MISS_CHAR ) AND
437 ( p_x_relationship_id IS NULL OR
438 p_x_relationship_id = FND_API.G_MISS_NUM ) ) THEN
439 x_return_status := FND_API.G_RET_STS_ERROR;
440 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
441 RETURN;
442 END IF;
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 NOT NULL AND
447 p_x_relationship_id <> FND_API.G_MISS_NUM ) ) THEN
448
449 FOR relationship_rec IN get_rec_from_id( p_x_relationship_id ) LOOP
450
451 -- Check if position is valid.
452 OPEN check_reln_valid_csr(relationship_rec.relationship_id);
453 FETCH check_reln_valid_csr INTO l_junk;
454 IF (check_reln_valid_csr%NOTFOUND) THEN
455 x_return_status := FND_API.G_RET_STS_ERROR;
456 x_msg_data := 'AHL_FMP_INVALID_MC_POS_STATUS';
457 CLOSE check_reln_valid_csr;
458
459 ELSE
460 IF (p_position_ref_meaning IS NOT NULL AND
461 p_position_ref_meaning <> FND_API.G_MISS_CHAR ) THEN
462
463 l_posn_meaning := AHL_MC_PATH_POSITION_PVT.GET_POSREF_BY_ID(relationship_rec.relationship_id);
464 IF (l_posn_meaning <> p_position_ref_meaning) THEN
465 x_return_status := FND_API.G_RET_STS_ERROR;
466 x_msg_data := 'AHL_FMP_INVALID_MC_POS_MEANING';
467 ELSE
468 -- exit when at least one valid relationship found.
469 l_valid_rec_found := TRUE;
470 EXIT;
471 END IF;
472 ELSE
473 -- exit when at least one valid relationship found.
474 l_valid_rec_found := TRUE;
475 EXIT;
476 END IF;
477 END IF;
478
479 END LOOP;
480
481 IF NOT(l_valid_rec_found) THEN
482 x_return_status := FND_API.G_RET_STS_ERROR;
483 x_msg_data := 'AHL_FMP_INVALID_MC_POSITION';
484
485 END IF;
486
487 RETURN;
488
489 END IF;
490
491 IF ( p_position_ref_meaning IS NOT NULL AND
492 p_position_ref_meaning <> FND_API.G_MISS_CHAR ) THEN
493
494 x_return_status := FND_API.G_RET_STS_ERROR;
495 x_msg_data := 'AHL_FMP_TOO_MANY_MC_POSITIONS';
496
497 RETURN;
498 END IF;
499 END validate_position;
500 --pdoki commented for Bug 6032303 - End */
501
502 --pdoki added for Bug 6032303 - Start
503 PROCEDURE validate_position
504 (
505 x_return_status OUT NOCOPY VARCHAR2,
506 x_msg_data OUT NOCOPY VARCHAR2,
507 p_position_ref_meaning IN VARCHAR2 := NULL,
508 p_x_relationship_id IN OUT NOCOPY NUMBER
509 )
510 IS
511 l_mc_header_id NUMBER;
512 l_posn_meaning FND_LOOKUPS.meaning%TYPE;
513 l_dummy_char VARCHAR2(1);
514
515 CURSOR get_mc_header_id ( c_relationship_id NUMBER )
516 IS
517 SELECT mch.mc_header_id
518 FROM ahl_mc_headers_b mch, ahl_mc_path_position_nodes mcp
519 WHERE
520 mch.mc_id = mcp.mc_id and
521 mch.version_number = nvl(mcp.version_number, mch.version_number) and
522 mcp.sequence = (select max(sequence)
523 from ahl_mc_path_position_nodes
524 where path_position_id = nvl(c_relationship_id,'-1'))
525 and mcp.path_position_id = nvl(c_relationship_id,'-1');
526
527 CURSOR check_mc_status ( c_mc_header_id NUMBER )
528 IS
529 SELECT 'X'
530 FROM ahl_mc_headers_b
531 WHERE mc_header_id = c_mc_header_id AND
532 config_status_code = 'COMPLETE';
533
534 BEGIN
535 x_return_status := FND_API.G_RET_STS_SUCCESS;
536
537 --If position meaning and path_position_id is null then throw 'invalid procedure call' error.
538
539 IF ( ( p_position_ref_meaning IS NULL OR
540 p_position_ref_meaning = FND_API.G_MISS_CHAR ) AND
541 ( p_x_relationship_id IS NULL OR
542 p_x_relationship_id = FND_API.G_MISS_NUM ) ) THEN
543 x_return_status := FND_API.G_RET_STS_ERROR;
544 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
545 RETURN;
546 END IF;
547
548 --If position meaning is not null but path_position_id is null, then throw error asking user to pick position from LOV.
549 IF ( ( p_position_ref_meaning IS NOT NULL AND
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_FMP_TOO_MANY_MC_POSITIONS';
555 RETURN;
556 END IF;
557
558 --Following validations are done only when the path_postion_id is NOT null as we return to the caller API in the above cases.
559
560 --Retrieving the mc_header_id to check if the MC is complete.
561 OPEN get_mc_header_id(p_x_relationship_id);
562 FETCH get_mc_header_id INTO l_mc_header_id;
563 IF (get_mc_header_id%NOTFOUND) THEN
564 x_return_status := FND_API.G_RET_STS_ERROR;
565 x_msg_data := 'AHL_FMP_INVALID_MC_POSITION';
566 CLOSE get_mc_header_id;
567 RETURN;
568 END IF;
569 CLOSE get_mc_header_id;
570
571 --Throw error if MC is not complete.
572 OPEN check_mc_status(l_mc_header_id);
573 FETCH check_mc_status INTO l_dummy_char;
574 IF (check_mc_status%NOTFOUND) THEN
575 x_return_status := FND_API.G_RET_STS_ERROR;
576 x_msg_data := 'AHL_FMP_MC_NOT_COMPLETE';
577 CLOSE check_mc_status;
578 RETURN;
579 END IF;
580 CLOSE check_mc_status;
581
582 --Retrieve path_position_meaning and check with the path_position_id passed to the API.
583 --Throw invalid MC position error if no position_ref_meaning is returned.
584 --Throw error 'Invalid Master Configuration Position FIELD entered for Effectivity RECORD.'if they dont match
585 --Fix for Bug 6032303. Calling below function with the path_position_id
586 l_posn_meaning := AHL_MC_PATH_POSITION_PVT.GET_POSREF_BY_ID(p_x_relationship_id);
587 IF (l_posn_meaning IS NULL) THEN
588 x_return_status := FND_API.G_RET_STS_ERROR;
589 x_msg_data := 'AHL_FMP_INVALID_MC_POSITION';
590 ELSIF (p_position_ref_meaning IS NOT NULL AND l_posn_meaning <> p_position_ref_meaning) THEN
591 x_return_status := FND_API.G_RET_STS_ERROR;
592 x_msg_data := 'AHL_FMP_INVALID_MC_POS_MEANING';
593 END IF;
594
595 END validate_position;
596 --pdoki added for Bug 6032303 - End
597
598 PROCEDURE validate_position_item
599 (
600 x_return_status OUT NOCOPY VARCHAR2,
601 x_msg_data OUT NOCOPY VARCHAR2,
602 p_inventory_item_id IN NUMBER,
603 p_relationship_id IN NUMBER
604 )
605 IS
606
607 l_dummy VARCHAR2(1);
608
609 CURSOR check_alternate( c_relationship_id NUMBER, c_inventory_item_id NUMBER )
610 IS
611 SELECT 'X'
612 FROM ahl_mc_relationships mcr,
613 ahl_mc_headers_b mch,
614 ahl_mc_path_position_nodes mcp,
615 --ahl_item_associations_v igass ,
616 ahl_item_associations_vl igass, --priyan changes due to performance reasons , Refer Bug # 5078530
617 mtl_system_items_kfv MTL,
618 mtl_item_status STAT,
619 fnd_lookup_values_vl IT
620 WHERE mch.mc_header_id = mcr.mc_header_id
621 and mch.mc_id = mcp.mc_id
622 and mch.version_number = nvl(mcp.version_number, mch.version_number)
623 and mcr.position_key = mcp.position_key
624 and mcp.sequence = (select max(sequence)
625 from ahl_mc_path_position_nodes where
626 path_position_id = nvl(c_relationship_id,-1)
627 and path_position_id=mcp.path_position_id)
628 and mcp.path_position_id = nvl(c_relationship_id,-1)
629 and mcr.item_group_id = igass.item_group_id
630 and igass.INVENTORY_ITEM_ID = mtl.INVENTORY_ITEM_ID
631 and igass.INVENTORY_ORG_ID = mtl.ORGANIZATION_ID
632 and mtl.inventory_item_id=c_inventory_item_id
633 and MTL.service_item_flag = 'N'
634 and STAT.inventory_item_status_code = MTL.inventory_item_status_code
635 and IT.lookup_code (+) = MTL.item_type
636 and IT.lookup_type (+) = 'ITEM_TYPE'
637 and trunc(sysdate) between trunc(nvl(it.start_date_active,sysdate))
638 and trunc(nvl(IT.end_date_active,sysdate+1));
639
640 BEGIN
641 x_return_status := FND_API.G_RET_STS_SUCCESS;
642
643 IF ( p_inventory_item_id IS NULL OR
644 p_inventory_item_id = FND_API.G_MISS_NUM OR
645 p_relationship_id IS NULL OR
646 p_relationship_id = FND_API.G_MISS_NUM ) THEN
647 x_return_status := FND_API.G_RET_STS_ERROR;
648 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
649 RETURN;
650 END IF;
651
652 OPEN check_alternate( p_relationship_id , p_inventory_item_id );
653
654 FETCH check_alternate INTO
655 l_dummy;
656
657 IF check_alternate%NOTFOUND THEN
658 x_return_status := FND_API.G_RET_STS_ERROR;
659 x_msg_data := 'AHL_FMP_INVALID_POSITION_ITEM';
660 END IF;
661
662 CLOSE check_alternate;
663 RETURN;
664
665 END validate_position_item;
666
667 PROCEDURE validate_counter_template
668 (
669 x_return_status OUT NOCOPY VARCHAR2,
670 x_msg_data OUT NOCOPY VARCHAR2,
671 p_inventory_item_id IN NUMBER := NULL,
672 p_relationship_id IN NUMBER := NULL,
673 p_counter_name IN VARCHAR2 := NULL,
674 p_x_counter_id IN OUT NOCOPY NUMBER
675 )
676 IS
677
678 l_msg_data VARCHAR2(2000);
679 l_return_status VARCHAR2(1);
680 l_counter_id NUMBER;
681 l_counter_group_id NUMBER;
682 l_inventory_item_id NUMBER;
683 --pdoki added for bug 6719371
684 l_dummy VARCHAR2(1);
685 l_counter_valid_flag BOOLEAN := FALSE;
686
687 CURSOR check_alternate_ctr_id(c_counter_id NUMBER, c_relationship_id NUMBER)
688 IS
689 SELECT 'X'
690 FROM csi_counter_template_vl C,
691 --CS_COUNTER_GROUPS CG,
692 CS_CSI_COUNTER_GROUPS CG,
693 CS_CTR_ASSOCIATIONS CA,
694 AHL_POSITION_ALTERNATES_V PA
695 WHERE C.DEFAULTED_GROUP_ID = CG.COUNTER_GROUP_ID
696 AND CG.TEMPLATE_FLAG = 'Y'
697 AND C.COUNTER_ID = c_counter_id
698 AND C.DIRECTION IN ('A',
699 'B')
700 AND CG.COUNTER_GROUP_ID = CA.COUNTER_GROUP_ID
701 AND CA.SOURCE_OBJECT_ID = PA.INVENTORY_ITEM_ID
702 AND PA.RELATIONSHIP_ID = c_relationship_id;
703
704
705 CURSOR get_rec_from_value ( c_counter_name VARCHAR2 )
706 IS
707 /*
708 SELECT DISTINCT C.counter_id,
709 C.counter_group_id
710 FROM CS_COUNTERS C, CS_COUNTER_GROUPS CG
711 WHERE CG.template_flag = 'Y'
712 AND C.counter_group_id = CG.counter_group_id
713 AND C.name = c_counter_name
714 AND C.DIRECTION in ('A','B'); */
715
716 --Priyan
717 --Performance tuning changes
718 --Refer Bug # 4913671
719
720 SELECT DISTINCT
721 C.COUNTER_ID,
722 C.DEFAULTED_GROUP_ID COUNTER_GROUP_ID
723 FROM
724 CSI_COUNTER_TEMPLATE_VL C,
725 --CS_COUNTER_GROUPS CG
726 --Priyan
727 --Perf changes . Refer Bug # 4913671
728 CS_CSI_COUNTER_GROUPS CG
729 WHERE
730 CG.TEMPLATE_FLAG = 'Y'
731 AND C.DEFAULTED_GROUP_ID = CG.COUNTER_GROUP_ID
732 AND C.NAME = c_counter_name
733 AND C.DIRECTION in ('A','B');
734
735
736 -- NEED TO REQUEST CS TEAM TO ADD INDEX ON COUNTER_GROU_ID IN TABLE
737 CURSOR get_rec_from_id ( c_counter_id NUMBER )
738 IS
739 /*
740 SELECT DISTINCT C.counter_id,
741 C.counter_group_id
742 FROM CS_COUNTERS C, CS_COUNTER_GROUPS CG
743 WHERE CG.template_flag = 'Y'
744 AND C.counter_group_id = CG.counter_group_id
745 AND C.counter_id = c_counter_id;
746 */
747
748 --Priyan
749 --Performance tuning changes
750 --Refer Bug # 4913671
751
752 SELECT DISTINCT
753 C.COUNTER_ID,
754 C.DEFAULTED_GROUP_ID COUNTER_GROUP_ID
755 FROM
756 CSI_COUNTER_TEMPLATE_VL C,
757 --CS_COUNTER_GROUPS CG
758 --Priyan
759 --Perf changes . Refer Bug # 4913671
760 CS_CSI_COUNTER_GROUPS CG
761 WHERE
762 CG.TEMPLATE_FLAG = 'Y'
763 AND C.DEFAULTED_GROUP_ID = CG.COUNTER_GROUP_ID
764 AND C.COUNTER_ID = c_counter_id ;
765
766
767 CURSOR validate_item_ctr_id(c_counter_id NUMBER, c_inventory_item_id NUMBER)
768 IS
769 SELECT 'X'
770 FROM --cs_counters c,
771 csi_counter_template_vl C,
772 --cs_counter_groups CG,
773 CS_CSI_COUNTER_GROUPS CG,
774 cs_ctr_associations CA
775 where --C.counter_group_id = CG.counter_group_id
776 C.defaulted_group_id = CG.counter_group_id
777 and CG.template_flag = 'Y'
778 and C.COUNTER_ID = c_counter_id
779 and C.direction in ('A','B')
780 and CG.counter_group_id = CA.counter_group_id
781 and CA.source_object_id = c_inventory_item_id;
782
783
784 /*
785 SELECT DISTINCT source_object_id
786 FROM CS_CTR_ASSOCIATIONS
787 WHERE counter_group_id = c_counter_group_id;
788
789
790 CURSOR get_counter_item ( c_counter_group_id NUMBER )
791 IS
792 SELECT DISTINCT source_object_id
793 FROM CS_CTR_ASSOCIATIONS
794 WHERE counter_group_id = c_counter_group_id;
795 */
796
797
798 BEGIN
799 x_return_status := FND_API.G_RET_STS_SUCCESS;
800
801 IF ( p_counter_name IS NULL AND --amsriniv . changed OR to AND
802 ( p_x_counter_id IS NULL OR
803 p_x_counter_id = FND_API.G_MISS_NUM ) ) THEN
804 x_return_status := FND_API.G_RET_STS_ERROR;
805 x_msg_data := 'AHL_FMP_INVALID_COUNTER';
806 RETURN;
807 END IF;
808
809 IF ( ( p_inventory_item_id IS NULL OR
810 p_inventory_item_id = FND_API.G_MISS_NUM ) AND
811 ( p_relationship_id IS NULL OR
812 p_relationship_id = FND_API.G_MISS_NUM ) ) THEN
813 x_return_status := FND_API.G_RET_STS_ERROR;
814 x_msg_data := 'AHL_FMP_INVALID_CTR_ITEM';
815 RETURN;
816 END IF;
817
818 IF ( ( p_counter_name IS NULL OR
819 p_counter_name = FND_API.G_MISS_CHAR ) AND
820 ( p_x_counter_id IS NOT NULL AND
821 p_x_counter_id <> FND_API.G_MISS_NUM ) ) THEN
822
823 IF ( p_inventory_item_id IS NOT NULL AND
824 p_inventory_item_id <> FND_API.G_MISS_NUM ) THEN
825 OPEN validate_item_ctr_id(p_x_counter_id, p_inventory_item_id);
826 FETCH validate_item_ctr_id INTO l_dummy;
827 IF (validate_item_ctr_id%NOTFOUND) THEN
828 x_return_status := FND_API.G_RET_STS_ERROR;
829 x_msg_data := 'AHL_FMP_INVALID_CTR_ITEM';
830 END IF;
831 CLOSE validate_item_ctr_id;
832 ELSIF (p_relationship_id IS NOT NULL AND
833 p_relationship_id <> FND_API.G_MISS_NUM ) THEN
834 OPEN check_alternate_ctr_id(p_x_counter_id, p_relationship_id);
835 FETCH check_alternate_ctr_id INTO l_dummy;
836 IF (check_alternate_ctr_id%NOTFOUND) THEN
837 x_return_status := FND_API.G_RET_STS_ERROR;
838 x_msg_data := 'AHL_FMP_INVALID_CTR_POSITION';
839 END IF;
840 CLOSE check_alternate_ctr_id;
841 END IF;
842 END IF;
843
844 IF ( p_counter_name IS NOT NULL AND
845 p_counter_name <> FND_API.G_MISS_CHAR ) THEN
846
847 OPEN get_rec_from_value( p_counter_name );
848
849 LOOP
850
851 FETCH get_rec_from_value INTO
852 l_counter_id,
853 l_counter_group_id;
854
855 EXIT WHEN get_rec_from_value%NOTFOUND;
856
857 IF ( p_inventory_item_id IS NOT NULL AND
858 p_inventory_item_id <> FND_API.G_MISS_NUM ) THEN
859 OPEN validate_item_ctr_id(l_counter_id, p_inventory_item_id);
860 FETCH validate_item_ctr_id INTO l_dummy;
861 IF (validate_item_ctr_id%FOUND) THEN
862 l_counter_valid_flag := TRUE;
863 p_x_counter_id := l_counter_id;
864 CLOSE validate_item_ctr_id;
865 EXIT;
866 END IF;
867 CLOSE validate_item_ctr_id;
868 ELSIF (p_relationship_id IS NOT NULL AND
869 p_relationship_id <> FND_API.G_MISS_NUM ) THEN
870 OPEN check_alternate_ctr_id(l_counter_id, p_relationship_id);
871 FETCH check_alternate_ctr_id INTO l_dummy;
872 IF (check_alternate_ctr_id%FOUND) THEN
873 l_counter_valid_flag := TRUE;
874 p_x_counter_id := l_counter_id;
875 CLOSE check_alternate_ctr_id;
876 EXIT;
877 END IF;
878 CLOSE check_alternate_ctr_id;
879 END IF;
880
881 END LOOP;
882
883 IF (get_rec_from_value%ROWCOUNT = 0) THEN --added by amsriniv
884 x_return_status := FND_API.G_RET_STS_ERROR;
885 x_msg_data := 'AHL_FMP_INVALID_COUNTER';
886 ELSE
887 IF NOT(l_counter_valid_flag)THEN
888 IF ( p_inventory_item_id IS NOT NULL AND
889 p_inventory_item_id <> FND_API.G_MISS_NUM ) THEN
890 x_return_status := FND_API.G_RET_STS_ERROR;
891 x_msg_data := 'AHL_FMP_INVALID_CTR_ITEM';
892 ELSIF ( p_relationship_id IS NOT NULL AND
893 p_relationship_id <> FND_API.G_MISS_NUM ) THEN
894 x_return_status := FND_API.G_RET_STS_ERROR;
895 x_msg_data := 'AHL_FMP_INVALID_CTR_POSITION';
896 END IF;
897 END IF;
898 END IF;
899 END IF;
900
901 END validate_counter_template;
902
903 PROCEDURE validate_country
904 (
905 x_return_status OUT NOCOPY VARCHAR2,
906 x_msg_data OUT NOCOPY VARCHAR2,
907 p_country_name IN VARCHAR2 := NULL,
908 p_x_country_code IN OUT NOCOPY VARCHAR2
909 )
910 IS
911
912 l_country_code VARCHAR2(2);
913
914 CURSOR get_rec_from_value ( c_country_name VARCHAR2 )
915 IS
916 SELECT DISTINCT territory_code
917 FROM FND_TERRITORIES_VL
918 WHERE territory_short_name = c_country_name;
919
920 CURSOR get_rec_from_id ( c_country_code VARCHAR2 )
921 IS
922 SELECT DISTINCT territory_code
923 FROM FND_TERRITORIES_VL
924 WHERE territory_code = c_country_code;
925
926 BEGIN
927 x_return_status := FND_API.G_RET_STS_SUCCESS;
928
929 IF ( ( p_country_name IS NULL OR
930 p_country_name = FND_API.G_MISS_CHAR ) AND
931 ( p_x_country_code IS NULL OR
932 p_x_country_code = FND_API.G_MISS_CHAR ) ) THEN
933 x_return_status := FND_API.G_RET_STS_ERROR;
934 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
935 RETURN;
936 END IF;
937
938 IF ( ( p_country_name IS NULL OR
939 p_country_name = FND_API.G_MISS_CHAR ) AND
940 ( p_x_country_code IS NOT NULL AND
941 p_x_country_code <> FND_API.G_MISS_CHAR ) ) THEN
942
943 OPEN get_rec_from_id( p_x_country_code );
944
945 FETCH get_rec_from_id INTO
946 l_country_code;
947
948 IF get_rec_from_id%NOTFOUND THEN
949 x_return_status := FND_API.G_RET_STS_ERROR;
950 x_msg_data := 'AHL_FMP_INVALID_COUNTRY';
951 END IF;
952
953 CLOSE get_rec_from_id;
954 RETURN;
955
956 END IF;
957
958 IF ( p_country_name IS NOT NULL AND
959 p_country_name <> FND_API.G_MISS_CHAR ) THEN
960
961 OPEN get_rec_from_value( p_country_name );
962
963 LOOP
964 FETCH get_rec_from_value INTO
965 l_country_code;
966
967 EXIT WHEN get_rec_from_value%NOTFOUND;
968
969 IF ( l_country_code = p_x_country_code ) THEN
970 CLOSE get_rec_from_value;
971 RETURN;
972 END IF;
973
974 END LOOP;
975
976 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
977 x_return_status := FND_API.G_RET_STS_ERROR;
978 x_msg_data := 'AHL_FMP_INVALID_COUNTRY';
979 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
980 p_x_country_code := l_country_code;
981 ELSE
982 x_return_status := FND_API.G_RET_STS_ERROR;
983 x_msg_data := 'AHL_FMP_TOO_MANY_COUNTRIES';
984 END IF;
985
986 CLOSE get_rec_from_value;
987 RETURN;
988
989 END IF;
990
991 END validate_country;
992
993 PROCEDURE validate_manufacturer
994 (
995 x_return_status OUT NOCOPY VARCHAR2,
996 x_msg_data OUT NOCOPY VARCHAR2,
997 p_inventory_item_id IN NUMBER := NULL,
998 p_relationship_id IN NUMBER := NULL,
999 p_manufacturer_name IN VARCHAR2 := NULL,
1000 p_x_manufacturer_id IN OUT NOCOPY NUMBER
1001 )
1002 IS
1003
1004 l_msg_data VARCHAR2(2000);
1005 l_return_status VARCHAR2(1);
1006 l_manufacturer_id NUMBER;
1007 l_inventory_item_id NUMBER;
1008
1009 CURSOR get_rec_from_value ( c_manufacturer_name VARCHAR2 )
1010 IS
1011 /*SELECT DISTINCT manufacturer_id,
1012 inventory_item_id
1013 FROM MTL_MFG_PART_NUMBERS_ALL_V
1014 WHERE manufacturer_name = c_manufacturer_name;*/
1015
1016 --priyan
1017 --Changing the query for performance reasons
1018 --Refer Bug # 5078530
1019
1020 select distinct b.manufacturer_id,
1021 a.inventory_item_id
1022 from mtl_manufacturers b,
1023 mtl_mfg_part_numbers a
1024 where a.manufacturer_id = b.manufacturer_id
1025 and b.manufacturer_name = c_manufacturer_name
1026 and a.organization_id in ( select distinct
1027 m.master_organization_id
1028 from inv_organization_info_v org,
1029 mtl_parameters m
1030 where org.organization_id = m.organization_id
1031 and nvl(org.operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id() );
1032
1033
1034 CURSOR get_rec_from_id ( c_manufacturer_id NUMBER )
1035 IS
1036 /*SELECT DISTINCT manufacturer_id,
1037 inventory_item_id
1038 FROM MTL_MFG_PART_NUMBERS_ALL_V
1039 WHERE manufacturer_id = c_manufacturer_id;*/
1040
1041 --priyan
1042 --Changing the query for performance reasons
1043 --Refer Bug # 5078530
1044
1045 select distinct manufacturer_id,
1046 inventory_item_id
1047 from mtl_mfg_part_numbers
1048 where manufacturer_id = c_manufacturer_id
1049 and organization_id in ( select distinct
1050 m.master_organization_id
1051 from inv_organization_info_v org,
1052 mtl_parameters m
1053 where org.organization_id = m.organization_id
1054 and nvl(org.operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id() );
1055
1056
1057 BEGIN
1058 x_return_status := FND_API.G_RET_STS_SUCCESS;
1059
1060 IF ( ( p_manufacturer_name IS NULL OR
1061 p_manufacturer_name = FND_API.G_MISS_CHAR ) AND
1062 ( p_x_manufacturer_id IS NULL OR
1063 p_x_manufacturer_id = FND_API.G_MISS_NUM ) ) THEN
1064 x_return_status := FND_API.G_RET_STS_ERROR;
1065 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
1066 RETURN;
1067 END IF;
1068
1069 IF ( ( p_inventory_item_id IS NULL OR
1070 p_inventory_item_id = FND_API.G_MISS_NUM ) AND
1071 ( p_relationship_id IS NULL OR
1072 p_relationship_id = FND_API.G_MISS_NUM ) ) THEN
1073 x_return_status := FND_API.G_RET_STS_ERROR;
1074 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
1075 RETURN;
1076 END IF;
1077
1078 IF ( ( p_manufacturer_name IS NULL OR
1079 p_manufacturer_name = FND_API.G_MISS_CHAR ) AND
1080 ( p_x_manufacturer_id IS NOT NULL AND
1081 p_x_manufacturer_id <> FND_API.G_MISS_NUM ) ) THEN
1082
1083 OPEN get_rec_from_id( p_x_manufacturer_id );
1084
1085 LOOP
1086 FETCH get_rec_from_id INTO
1087 l_manufacturer_id,
1088 l_inventory_item_id;
1089
1090 EXIT WHEN get_rec_from_id%NOTFOUND;
1091
1092 IF ( p_inventory_item_id IS NOT NULL AND
1093 p_inventory_item_id <> FND_API.G_MISS_NUM ) THEN
1094 IF ( p_inventory_item_id = l_inventory_item_id ) THEN
1095 CLOSE get_rec_from_id;
1096 RETURN;
1097 END IF;
1098 ELSIF ( p_relationship_id IS NOT NULL AND
1099 p_relationship_id <> FND_API.G_MISS_NUM ) THEN
1100
1101 validate_position_item
1102 (
1103 x_return_status => l_return_status,
1104 x_msg_data => l_msg_data,
1105 p_inventory_item_id => l_inventory_item_id,
1106 p_relationship_id => p_relationship_id
1107 );
1108
1109 IF ( NVL( l_return_status, 'X' ) = FND_API.G_RET_STS_SUCCESS ) THEN
1110 CLOSE get_rec_from_id;
1111 RETURN;
1112 END IF;
1113 END IF;
1114
1115 END LOOP;
1116
1117 IF ( get_rec_from_id%ROWCOUNT = 0 ) THEN
1118 x_return_status := FND_API.G_RET_STS_ERROR;
1119 x_msg_data := 'AHL_FMP_INVALID_MF';
1120 ELSE
1121 x_return_status := FND_API.G_RET_STS_ERROR;
1122 IF ( p_inventory_item_id IS NOT NULL AND
1123 p_inventory_item_id <> FND_API.G_MISS_NUM ) THEN
1124 x_msg_data := 'AHL_FMP_INVALID_MF_ITEM';
1125 ELSIF ( p_relationship_id IS NOT NULL AND
1126 p_relationship_id <> FND_API.G_MISS_NUM ) THEN
1127 x_msg_data := 'AHL_FMP_INVALID_MF_POSITION';
1128 END IF;
1129 END IF;
1130
1131 CLOSE get_rec_from_id;
1132 RETURN;
1133
1134 END IF;
1135
1136 IF ( p_manufacturer_name IS NOT NULL AND
1137 p_manufacturer_name <> FND_API.G_MISS_CHAR ) THEN
1138
1139 OPEN get_rec_from_value( p_manufacturer_name );
1140
1141 LOOP
1142
1143 FETCH get_rec_from_value INTO
1144 l_manufacturer_id,
1145 l_inventory_item_id;
1146
1147 EXIT WHEN get_rec_from_value%NOTFOUND;
1148
1149 IF ( p_inventory_item_id IS NOT NULL AND
1150 p_inventory_item_id <> FND_API.G_MISS_NUM ) THEN
1151 IF ( p_inventory_item_id = l_inventory_item_id ) THEN
1152 IF ( p_x_manufacturer_id IS NULL ) THEN
1153 p_x_manufacturer_id := l_manufacturer_id;
1154 CLOSE get_rec_from_value;
1155 RETURN;
1156 ELSIF ( l_manufacturer_id = p_x_manufacturer_id ) THEN
1157 CLOSE get_rec_from_value;
1158 RETURN;
1159 END IF;
1160 END IF;
1161 ELSIF ( p_relationship_id IS NOT NULL AND
1162 p_relationship_id <> FND_API.G_MISS_NUM ) THEN
1163
1164 validate_position_item
1165 (
1166 x_return_status => l_return_status,
1167 x_msg_data => l_msg_data,
1168 p_inventory_item_id => l_inventory_item_id,
1169 p_relationship_id => p_relationship_id
1170 );
1171
1172 IF ( NVL( l_return_status, 'X' ) = FND_API.G_RET_STS_SUCCESS ) THEN
1173 IF ( p_x_manufacturer_id IS NULL ) THEN
1174 p_x_manufacturer_id := l_manufacturer_id;
1175 CLOSE get_rec_from_value;
1176 RETURN;
1177 ELSIF ( l_manufacturer_id = p_x_manufacturer_id ) THEN
1178 CLOSE get_rec_from_value;
1179 RETURN;
1180 END IF;
1181 END IF;
1182
1183 END IF;
1184
1185 END LOOP;
1186
1187 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
1188 x_return_status := FND_API.G_RET_STS_ERROR;
1189 x_msg_data := 'AHL_FMP_INVALID_MF';
1190 ELSE
1191 IF ( p_inventory_item_id IS NOT NULL AND
1192 p_inventory_item_id <> FND_API.G_MISS_NUM ) THEN
1193 x_return_status := FND_API.G_RET_STS_ERROR;
1194 x_msg_data := 'AHL_FMP_INVALID_MF_ITEM';
1195 ELSIF ( p_relationship_id IS NOT NULL AND
1196 p_relationship_id <> FND_API.G_MISS_NUM ) THEN
1197 x_return_status := FND_API.G_RET_STS_ERROR;
1198 x_msg_data := 'AHL_FMP_INVALID_MF_POSITION';
1199 END IF;
1200 END IF;
1201
1202 CLOSE get_rec_from_value;
1203 RETURN;
1204
1205 END IF;
1206
1207 END validate_manufacturer;
1208
1209 PROCEDURE validate_serial_numbers_range
1210 (
1211 x_return_status OUT NOCOPY VARCHAR2,
1212 x_msg_data OUT NOCOPY VARCHAR2,
1213 p_serial_number_from IN VARCHAR2,
1214 p_serial_number_to IN VARCHAR2
1215 )
1216 IS
1217
1218 l_dummy VARCHAR2(1);
1219
1220 CURSOR compare_numbers ( c_serial_number_from VARCHAR2 , c_serial_number_to VARCHAR2 )
1221 IS
1222 SELECT 'X'
1223 FROM DUAL
1224 WHERE TO_NUMBER( c_serial_number_to ) >=
1225 TO_NUMBER( c_serial_number_from );
1226
1227 CURSOR compare_chars ( c_serial_number_from VARCHAR2 , c_serial_number_to VARCHAR2 )
1228 IS
1229 SELECT 'X'
1230 FROM DUAL
1231 WHERE c_serial_number_to >= c_serial_number_from;
1232
1233 BEGIN
1234 x_return_status := FND_API.G_RET_STS_SUCCESS;
1235
1236 IF ( p_serial_number_from IS NULL OR
1237 p_serial_number_from = FND_API.G_MISS_CHAR OR
1238 p_serial_number_to IS NULL OR
1239 p_serial_number_to = FND_API.G_MISS_CHAR ) THEN
1240 x_return_status := FND_API.G_RET_STS_ERROR;
1241 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
1242 RETURN;
1243 END IF;
1244
1245 BEGIN
1246
1247 OPEN compare_numbers( p_serial_number_from, p_serial_number_to );
1248
1249 FETCH compare_numbers INTO
1250 l_dummy;
1251
1252 IF compare_numbers%NOTFOUND THEN
1253 x_return_status := FND_API.G_RET_STS_ERROR;
1254 x_msg_data := 'AHL_FMP_INVALID_SERIAL_RANGE';
1255 END IF;
1256
1257 CLOSE compare_numbers;
1258 RETURN;
1259
1260 EXCEPTION WHEN INVALID_NUMBER THEN
1261 IF compare_numbers%ISOPEN THEN
1262 CLOSE compare_numbers;
1263 END IF;
1264 END;
1265
1266 OPEN compare_chars( p_serial_number_from, p_serial_number_to );
1267
1268 FETCH compare_chars INTO
1269 l_dummy;
1270
1271 IF compare_chars%NOTFOUND THEN
1272 x_return_status := FND_API.G_RET_STS_ERROR;
1273 x_msg_data := 'AHL_FMP_INVALID_SERIAL_RANGE';
1274 END IF;
1275
1276 CLOSE compare_chars;
1277 RETURN;
1278
1279 END validate_serial_numbers_range;
1280
1281 PROCEDURE validate_mr_status
1282 (
1283 x_return_status OUT NOCOPY VARCHAR2,
1284 x_msg_data OUT NOCOPY VARCHAR2,
1285 p_mr_header_id IN NUMBER
1286 )
1287 IS
1288 CURSOR check_mr_status( c_mr_header_id NUMBER )
1289 IS
1290 SELECT mr_status_code
1291 FROM AHL_MR_HEADERS_APP_V
1292 WHERE mr_header_id = c_mr_header_id;
1293
1294 l_mr_status_code varchar2(30);
1295 BEGIN
1296 x_return_status := FND_API.G_RET_STS_SUCCESS;
1297 IF ( G_DEBUG = 'Y' ) THEN
1298 AHL_DEBUG_PUB.enable_debug;
1299 AHL_DEBUG_PUB.debug('Step A' );
1300 END IF;
1301
1302 IF ( p_mr_header_id IS NULL OR
1303 p_mr_header_id = FND_API.G_MISS_NUM ) THEN
1304 x_return_status := FND_API.G_RET_STS_ERROR;
1305 x_msg_data := 'AHL_FMP_MR_HEADER_ID_INVALID';
1306 RETURN;
1307 END IF;
1308 AHL_DEBUG_PUB.debug('Step A3' );
1309 OPEN check_mr_status( p_mr_header_id );
1310
1311 FETCH check_mr_status INTO l_mr_status_code;
1312
1313 IF check_mr_status%NOTFOUND THEN
1314 x_return_status := FND_API.G_RET_STS_ERROR;
1315 x_msg_data := 'AHL_FMP_INVALID_MR';
1316 CLOSE check_mr_status;
1317 RETURN;
1318 END IF;
1319 AHL_DEBUG_PUB.debug('Step A6' );
1320 IF ( l_mr_status_code <> 'DRAFT' AND
1321 l_mr_status_code <> 'APPROVAL_REJECTED' ) THEN
1322 x_return_status := FND_API.G_RET_STS_ERROR;
1323 AHL_DEBUG_PUB.debug('Step 1' );
1324 x_msg_data := 'AHL_FMP_INVALID_MR_STATUS';
1325 AHL_DEBUG_PUB.debug('Step 2' );
1326 CLOSE check_mr_status;
1327 RETURN;
1328 END IF;
1329
1330 CLOSE check_mr_status;
1331 RETURN;
1332
1333 END validate_mr_status;
1334
1335 PROCEDURE validate_mr_effectivity
1336 (
1337 x_return_status OUT NOCOPY VARCHAR2,
1338 x_msg_data OUT NOCOPY VARCHAR2,
1339 p_mr_effectivity_id IN NUMBER,
1340 p_object_version_number IN NUMBER := NULL
1341 )
1342 IS
1343
1344 l_object_version_number NUMBER;
1345
1346 CURSOR check_mr_effectivity( c_mr_effectivity_id NUMBER )
1347 IS
1348 SELECT object_version_number
1349 FROM AHL_MR_EFFECTIVITIES_APP_V
1350 WHERE mr_effectivity_id = c_mr_effectivity_id;
1351
1352 BEGIN
1353 x_return_status := FND_API.G_RET_STS_SUCCESS;
1354
1355 IF ( p_mr_effectivity_id IS NULL OR
1356 p_mr_effectivity_id = FND_API.G_MISS_NUM ) THEN
1357 x_return_status := FND_API.G_RET_STS_ERROR;
1358 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
1359 RETURN;
1360 END IF;
1361
1362 OPEN check_mr_effectivity( p_mr_effectivity_id );
1363
1364 FETCH check_mr_effectivity INTO
1365 l_object_version_number;
1366
1367 IF check_mr_effectivity%NOTFOUND THEN
1368 x_return_status := FND_API.G_RET_STS_ERROR;
1369 x_msg_data := 'AHL_FMP_INVALID_MR_EFFECTIVITY';
1370 CLOSE check_mr_effectivity;
1371 RETURN;
1372 END IF;
1373
1374 IF ( p_object_version_number IS NOT NULL OR
1375 p_object_version_number <> FND_API.G_MISS_NUM ) THEN
1376 IF ( p_object_version_number <> l_object_version_number ) THEN
1377 x_return_status := FND_API.G_RET_STS_ERROR;
1378 x_msg_data := 'AHL_FMP_INVALID_MR_EFFECTIVITY';
1379 END IF;
1380 END IF;
1381
1382 CLOSE check_mr_effectivity;
1383 RETURN;
1384
1385 END validate_mr_effectivity;
1386
1387 PROCEDURE validate_mr_interval_threshold
1388 (
1389 x_return_status OUT NOCOPY VARCHAR2,
1390 x_msg_data OUT NOCOPY VARCHAR2,
1391 p_mr_header_id IN NUMBER,
1392 p_repetitive_flag IN VARCHAR2
1393 )
1394 IS
1395
1396 l_msg_data VARCHAR2(2000);
1397 l_return_status VARCHAR2(1);
1398 l_dummy VARCHAR2(1);
1399 l_mr_effectivity_id NUMBER;
1400 l_counter_id NUMBER;
1401
1402 CURSOR get_threshold( c_mr_header_id NUMBER )
1403 IS
1404 SELECT 'X'
1405 FROM AHL_MR_EFFECTIVITIES_APP_V
1406 WHERE mr_header_id = c_mr_header_id
1407 AND threshold_date IS NOT NULL;
1408
1409 CURSOR get_intervals( c_mr_header_id NUMBER )
1410 IS
1411 SELECT A.mr_effectivity_id,
1412 A.counter_id
1413 FROM AHL_MR_INTERVALS_APP_V A, AHL_MR_EFFECTIVITIES_APP_V B
1414 WHERE A.mr_effectivity_id = B.mr_effectivity_id
1415 AND B.mr_header_id = c_mr_header_id
1416 GROUP BY A.mr_effectivity_id,
1417 A.counter_id
1418 HAVING count(*) > 1;
1419
1420 CURSOR get_interval_range( c_mr_header_id NUMBER )
1421 IS
1422 SELECT 'X'
1423 FROM AHL_MR_INTERVALS_APP_V A, AHL_MR_EFFECTIVITIES_APP_V B
1424 WHERE ( A.start_date IS NOT NULL OR A.start_value IS NOT NULL )
1425 AND A.mr_effectivity_id = B.mr_effectivity_id
1426 AND B.mr_header_id = c_mr_header_id;
1427
1428 BEGIN
1429 x_return_status := FND_API.G_RET_STS_SUCCESS;
1430
1431 IF ( ( p_mr_header_id IS NULL OR
1432 p_mr_header_id = FND_API.G_MISS_NUM ) AND
1433 ( p_repetitive_flag IS NULL OR
1434 p_repetitive_flag = FND_API.G_MISS_CHAR ) ) THEN
1435 x_return_status := FND_API.G_RET_STS_ERROR;
1436 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
1437 RETURN;
1438 END IF;
1439
1440 -- Check if the Maintenance Requirement is in Updatable status
1441 AHL_FMP_COMMON_PVT.validate_mr_status
1442 (
1443 x_return_status => l_return_status,
1444 x_msg_data => l_msg_data,
1445 p_mr_header_id => p_mr_header_id
1446 );
1447
1448 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
1449 x_return_status := FND_API.G_RET_STS_ERROR;
1450 x_msg_data := l_msg_data;
1451 RETURN;
1452 END IF;
1453
1454 IF ( p_repetitive_flag = 'Y' ) THEN
1455 OPEN get_threshold( p_mr_header_id );
1456
1457 FETCH get_threshold INTO
1458 l_dummy;
1459
1460 IF get_threshold%FOUND THEN
1461 x_return_status := FND_API.G_RET_STS_ERROR;
1462 x_msg_data := 'AHL_FMP_INVALID_MR_THRESHOLD';
1463 END IF;
1464
1465 CLOSE get_threshold;
1466 RETURN;
1467 ELSE
1468 OPEN get_intervals( p_mr_header_id );
1469
1470 FETCH get_intervals INTO
1471 l_mr_effectivity_id,
1472 l_counter_id;
1473
1474 IF get_intervals%FOUND THEN
1475 x_return_status := FND_API.G_RET_STS_ERROR;
1476 x_msg_data := 'AHL_FMP_INVALID_MR_INTERVALS';
1477 CLOSE get_intervals;
1478 RETURN;
1479 END IF;
1480
1481 CLOSE get_intervals;
1482
1483 OPEN get_interval_range( p_mr_header_id );
1484
1485 FETCH get_interval_range INTO
1486 l_dummy;
1487
1488 IF get_interval_range%FOUND THEN
1489 x_return_status := FND_API.G_RET_STS_ERROR;
1490 x_msg_data := 'AHL_FMP_INVALID_MR_INT_RANGE';
1491 CLOSE get_interval_range;
1492 RETURN;
1493 END IF;
1494
1495 CLOSE get_interval_range;
1496
1497 END IF;
1498
1499 END validate_mr_interval_threshold;
1500
1501
1502
1503
1504 -----------------------------------------------------------------------
1505 -- Start of Comments --
1506 -- Procedure name : Populate_Appl_MRs
1507 -- Type : Private
1508 -- Function : Calls FMP and populates the AHL_APPLICABLE_MRS table.
1509 -- Pre-reqs :
1510 -- Parameters :
1511 --
1512 -- Populate_Appl_MRs Parameters:
1513 -- p_csi_ii_id IN csi item instance id Required
1514 --
1515 -- Version :
1516 -- Initial Version 1.0
1517 --
1518 -- End of Comments.
1519
1520
1521 PROCEDURE Populate_Appl_MRs (
1522 p_csi_ii_id IN NUMBER,
1523 p_include_doNotImplmt IN VARCHAR2 := 'Y',
1524 x_return_status OUT NOCOPY VARCHAR2,
1525 x_msg_count OUT NOCOPY NUMBER,
1526 x_msg_data OUT NOCOPY VARCHAR2)
1527 IS
1528 l_api_version CONSTANT NUMBER := 1.0;
1529 l_appl_mrs_tbl AHL_FMP_PVT.applicable_mr_tbl_type;
1530
1531 BEGIN
1532
1533 -- Initialize temporary table.
1534 DELETE FROM AHL_APPLICABLE_MRS;
1535
1536 -- call api to fetch all applicable mrs for ASO installation.
1537 AHL_FMP_PVT.get_applicable_mrs(
1538 p_api_version => l_api_version,
1539 p_init_msg_list => FND_API.G_FALSE,
1540 p_commit => FND_API.G_FALSE,
1541 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1542 x_return_status => x_return_status,
1543 x_msg_count => x_msg_count,
1544 x_msg_data => x_msg_data,
1545 p_item_instance_id => p_csi_ii_id,
1546 p_components_flag => 'Y',
1547 p_include_doNotImplmt => p_include_doNotImplmt,
1548 x_applicable_mr_tbl => l_appl_mrs_tbl);
1549
1550
1551 -- Raise errors if exceptions occur
1552 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1553 RAISE FND_API.G_EXC_ERROR;
1554 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1555 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1556 END IF;
1557
1558 -- Populate temporary table ahl_applicable_mrs.
1559 IF (l_appl_mrs_tbl.COUNT > 0) THEN
1560 FOR i IN l_appl_mrs_tbl.FIRST..l_appl_mrs_tbl.LAST LOOP
1561 -- dbms_output.put_line( l_appl_mrs_tbl(i).item_instance_id||' '||
1562 -- l_appl_mrs_tbl(i).mr_header_id);
1563 INSERT INTO AHL_APPLICABLE_MRS (
1564 CSI_ITEM_INSTANCE_ID,
1565 MR_HEADER_ID,
1566 MR_EFFECTIVITY_ID,
1567 REPETITIVE_FLAG ,
1568 SHOW_REPETITIVE_CODE,
1569 COPY_ACCOMPLISHMENT_CODE,
1570 PRECEDING_MR_HEADER_ID,
1571 IMPLEMENT_STATUS_CODE,
1572 DESCENDENT_COUNT
1573 ) values
1574 ( l_appl_mrs_tbl(i).item_instance_id,
1575 l_appl_mrs_tbl(i).mr_header_id,
1576 l_appl_mrs_tbl(i).mr_effectivity_id,
1577 l_appl_mrs_tbl(i).repetitive_flag,
1578 l_appl_mrs_tbl(i).show_repetitive_code,
1579 l_appl_mrs_tbl(i).copy_accomplishment_flag,
1580 l_appl_mrs_tbl(i).preceding_mr_header_id,
1581 l_appl_mrs_tbl(i).implement_status_code,
1582 l_appl_mrs_tbl(i).descendent_count
1583 );
1584 END LOOP;
1585 END IF;
1586
1587 END Populate_Appl_MRs;
1588
1589 --------------------------------------------------------------------------------------------
1590 -- API Name : Mr_Title_Version_To_Id
1591 -- Purpose : To get mr_header_id out of mr_title and mr_version_number
1592 --------------------------------------------------------------------------------------------
1593 PROCEDURE Mr_Title_Version_To_Id
1594 (
1595 p_mr_title IN VARCHAR2,
1596 p_mr_version_number IN NUMBER,
1597 x_mr_header_id OUT NOCOPY NUMBER,
1598 x_return_status OUT NOCOPY VARCHAR2
1599 )
1600 AS
1601
1602 -- Cursor for getting mr_header_id out of mr_title and mr_version_number
1603 CURSOR header_id_csr_type(p_mr_title IN VARCHAR2, p_mr_version_number IN NUMBER) IS
1604 SELECT mr_header_id
1605 FROM ahl_mr_headers_app_v
1606 WHERE title = p_mr_title
1607 AND version_number = p_mr_version_number
1608 AND mr_status_code<>'TERMINATED'
1609 AND TRUNC(NVL(effective_to,SYSDATE+1))> TRUNC(SYSDATE);
1610
1611
1612 l_api_name CONSTANT VARCHAR2(30) := 'Mr_Title_Version_To_Id';
1613 l_api_version CONSTANT NUMBER := 1.0;
1614 l_header_id NUMBER;
1615
1616 BEGIN
1617 x_return_status:=FND_API.G_RET_STS_SUCCESS;
1618 OPEN header_id_csr_type(p_mr_title, p_mr_version_number);
1619 FETCH header_id_csr_type INTO l_header_id;
1620 CLOSE header_id_csr_type;
1621 IF l_header_id IS NULL THEN
1622 x_return_status:=FND_API.G_RET_STS_ERROR;
1623 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_NOT_EXISTS');
1624 FND_MESSAGE.SET_TOKEN('TITLE', p_mr_title);
1625 FND_MESSAGE.SET_TOKEN('VERSION', p_mr_version_number);
1626 FND_MSG_PUB.ADD;
1627 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1628 fnd_log.string
1629 (
1630 fnd_log.level_error,
1631 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1632 'MR Title and Version Number Combination is invalid'
1633 );
1634 END IF;
1635 ELSE
1636 x_mr_header_id := l_header_id;
1637 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1638 fnd_log.string
1639 (
1640 fnd_log.level_statement,
1641 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1642 'x_mr_header_id--->'||x_mr_header_id
1643 );
1644 END IF;
1645 END IF;
1646
1647 END Mr_Title_Version_To_Id;
1648
1649 --------------------------------------------------------------------------------------------
1650 -- API Name : Mr_Effectivity_Name_To_Id
1651 -- Purpose : To get mr_effectivity_id from mr_effectivity_name
1652 --------------------------------------------------------------------------------------------
1653
1654 PROCEDURE Mr_Effectivity_Name_To_Id
1655 (
1656 p_mr_header_id IN NUMBER,
1657 p_mr_effectivity_name IN VARCHAR2,
1658 x_mr_effectivity_id OUT NOCOPY NUMBER,
1659 x_return_status OUT NOCOPY VARCHAR2
1660 )
1661 AS
1662
1663 CURSOR effectivity_id_csr_type(p_mr_effectivity_name IN VARCHAR2,
1664 p_mr_header_id IN NUMBER)
1665 IS
1666 SELECT mr_effectivity_id
1667 FROM ahl_mr_effectivities_app_v
1668 WHERE name = p_mr_effectivity_name
1669 AND
1670 mr_header_id = p_mr_header_id;
1671
1672 l_api_name CONSTANT VARCHAR2(30) := 'Mr_Effectivity_Name_To_Id';
1673 l_api_version CONSTANT NUMBER := 1.0;
1674 l_mr_effectivity_id NUMBER;
1675
1676 BEGIN
1677 x_return_status:=FND_API.G_RET_STS_SUCCESS;
1678
1679 OPEN effectivity_id_csr_type(p_mr_effectivity_name,p_mr_header_id);
1680 FETCH effectivity_id_csr_type INTO l_mr_effectivity_id;
1681 CLOSE effectivity_id_csr_type;
1682 IF l_mr_effectivity_id IS NULL THEN
1683 x_return_status:=FND_API.G_RET_STS_ERROR;
1684 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_EFFEC_NOT_EXISTS');
1685 FND_MESSAGE.SET_TOKEN('RECORD', p_mr_effectivity_name);
1686 FND_MSG_PUB.ADD;
1687 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
1688 fnd_log.string
1689 (
1690 fnd_log.level_error,
1691 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1692 'MR Effectivity name specified is invalid'
1693 );
1694 END IF;
1695 ELSE
1696 x_mr_effectivity_id := l_mr_effectivity_id;
1697 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1698 fnd_log.string
1699 (
1700 fnd_log.level_statement,
1701 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1702 'x_mr_effectivity_id--->'||x_mr_effectivity_id
1703 );
1704 END IF;
1705
1706 END IF;
1707
1708 END Mr_Effectivity_Name_To_Id;
1709
1710
1711 FUNCTION check_mr_type
1712 (p_mr_header_id IN NUMBER) RETURN varchar2 IS
1713 l_activity varchar2(30);
1714 cursor l_check_mr_type_csr (c_mr_header_id IN NUMBER)
1715 is
1716 SELECT TYPE_CODE
1717 FROM AHL_MR_HEADERS_B
1718 WHERE mr_header_id = c_mr_header_id;
1719 begin
1720 open l_check_mr_type_csr(p_mr_header_id);
1721 FETCH l_check_mr_type_csr INTO l_activity;
1722
1723 if l_check_mr_type_csr%NOTFOUND THEN
1724 l_activity :=NULL;
1725 END IF;
1726 CLOSE l_check_mr_type_csr ;
1727 return l_activity;
1728 end check_mr_type;
1729
1730
1731
1732 FUNCTION check_mr_status
1733 (p_mr_header_id IN NUMBER) RETURN varchar2 IS
1734 l_status varchar2(30);
1735 cursor l_check_mr_status_csr (c_mr_header_id IN NUMBER)
1736 is
1737 SELECT mr_status_code
1738 FROM AHL_MR_HEADERS_B
1739 WHERE mr_header_id = c_mr_header_id
1740 AND trunc(nvl( effective_to, sysdate+1 ))>=trunc(sysdate);
1741 begin
1742 open l_check_mr_status_csr(p_mr_header_id);
1743 FETCH l_check_mr_status_csr INTO l_status;
1744
1745 if l_check_mr_status_csr%NOTFOUND THEN
1746 l_status :=NULL;
1747 END IF;
1748 CLOSE l_check_mr_status_csr ;
1749 return l_status;
1750 end check_mr_status;
1751
1752
1753 PROCEDURE validate_mr_pm_status
1754 (
1755 x_return_status OUT NOCOPY VARCHAR2,
1756 x_msg_data OUT NOCOPY VARCHAR2,
1757 p_mr_header_id IN NUMBER
1758 )
1759 IS
1760
1761 l_mr_status_code VARCHAR2(30);
1762
1763 CURSOR check_mr_status( c_mr_header_id NUMBER )
1764 IS
1765 SELECT mr_status_code
1766 FROM AHL_MR_HEADERS_B
1767 WHERE mr_header_id = c_mr_header_id;
1768
1769 BEGIN
1770 x_return_status := FND_API.G_RET_STS_SUCCESS;
1771
1772 IF ( p_mr_header_id IS NULL OR
1773 p_mr_header_id = FND_API.G_MISS_NUM ) THEN
1774 x_return_status := FND_API.G_RET_STS_ERROR;
1775 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
1776 RETURN;
1777 END IF;
1778
1779 OPEN check_mr_status( p_mr_header_id );
1780
1781 FETCH check_mr_status INTO
1782 l_mr_status_code;
1783
1784 IF check_mr_status%NOTFOUND THEN
1785 x_return_status := FND_API.G_RET_STS_ERROR;
1786 x_msg_data := 'AHL_FMP_INVALID_MR';
1787 CLOSE check_mr_status;
1788 RETURN;
1789 END IF;
1790
1791 IF ( l_mr_status_code <> 'DRAFT' AND
1792 l_mr_status_code <> 'APPROVAL_REJECTED' AND
1793 l_mr_status_code <> 'COMPLETE'
1794 ) THEN
1795 x_return_status := FND_API.G_RET_STS_ERROR;
1796 x_msg_data := 'AHL_FMP_INVALID_MR_STATUS';
1797 CLOSE check_mr_status;
1798 RETURN;
1799 END IF;
1800
1801 CLOSE check_mr_status;
1802 RETURN;
1803
1804 END validate_mr_pm_status;
1805
1806
1807 PROCEDURE validate_mr_type_program
1808 (
1809 x_return_status OUT NOCOPY VARCHAR2,
1810 x_msg_data OUT NOCOPY VARCHAR2,
1811 p_mr_header_id IN NUMBER,
1812 p_effectivity_id IN NUMBER,
1813 p_eff_obj_version IN NUMBER
1814 )
1815 IS
1816
1817 l_record_exists NUMBER;
1818
1819 CURSOR mr_type_program( c_effectivity_id NUMBER,c_eff_obj number,c_mr_header_id number)
1820 IS
1821 SELECT 1
1822 FROM DUAL
1823 WHERE
1824 EXISTS (
1825 SELECT 'x'
1826 FROM AHL_UNIT_EFFECTIVITIES_B unit,
1827 CSI_ITEM_INSTANCES csi,
1828 AHL_MR_EFFECTIVITIES eff
1829 where
1830 eff.inventory_item_id = csi.INVENTORY_ITEM_ID
1831 and unit.CSI_ITEM_INSTANCE_ID = csi.instance_id
1832 and unit.program_mr_header_id = c_mr_header_id
1833 and eff.mr_effectivity_id =c_effectivity_id
1834 and eff.object_version_number =c_eff_obj ) ;
1835
1836 BEGIN
1837 x_return_status := FND_API.G_RET_STS_SUCCESS;
1838
1839 IF ( p_mr_header_id IS NULL OR
1840 p_mr_header_id = FND_API.G_MISS_NUM ) THEN
1841 x_return_status := FND_API.G_RET_STS_ERROR;
1842 x_msg_data := 'AHL_COM_INVALID_PROCEDURE_CALL';
1843 RETURN;
1844 END IF;
1845
1846 OPEN mr_type_program( p_effectivity_id, p_eff_obj_version, p_mr_header_id );
1847 FETCH mr_type_program INTO l_record_exists;
1848
1849 IF mr_type_program%FOUND THEN
1850 x_return_status := FND_API.G_RET_STS_ERROR;
1851 x_msg_data := 'AHL_FMP_EFFECTIVITY_ITEM_PM';
1852 END IF;
1853
1854 CLOSE mr_type_program;
1855
1856 END validate_mr_type_program;
1857
1858
1859
1860 PROCEDURE validate_mr_type_activity
1861 (
1862 x_return_status OUT NOCOPY VARCHAR2,
1863 x_msg_data OUT NOCOPY VARCHAR2,
1864 p_effectivity_id IN NUMBER,
1865 p_eff_obj_version IN NUMBER
1866 )
1867 IS
1868
1869 l_record_exists NUMBER;
1870
1871 CURSOR mr_type_activity( c_effectivity_id NUMBER,c_eff_obj number)
1872 IS
1873 SELECT 1
1874 FROM DUAL
1875 WHERE EXISTS ( SELECT 'x'
1876 FROM AHL_UNIT_EFFECTIVITIES_B unit,
1877 CSI_ITEM_INSTANCES csi,
1878 AHL_MR_EFFECTIVITIES eff
1879 where
1880 eff.inventory_item_id = csi.INVENTORY_ITEM_ID
1881 and unit.CSI_ITEM_INSTANCE_ID = csi.instance_id
1882 and unit.mr_header_id =eff.mr_header_id
1883 and eff.mr_effectivity_id =c_effectivity_id
1884 and eff.object_version_number=c_eff_obj) ;
1885
1886 BEGIN
1887 x_return_status := FND_API.G_RET_STS_SUCCESS;
1888
1889
1890 OPEN mr_type_activity( p_effectivity_id, p_eff_obj_version);
1891 FETCH mr_type_activity INTO l_record_exists;
1892
1893 IF mr_type_activity%FOUND THEN
1894 x_return_status := FND_API.G_RET_STS_ERROR;
1895 x_msg_data := 'AHL_FMP_EFFECTIVITY_ITEM_PM';
1896 END IF;
1897
1898 CLOSE mr_type_activity;
1899
1900 END validate_mr_type_activity;
1901
1902 END AHL_FMP_COMMON_PVT;