1 PACKAGE BODY IGS_PR_VAL_PRCT AS
2 /* $Header: IGSPR05B.pls 115.4 2002/11/29 02:44:46 nsidana ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 26-AUG-2001 Bug No. 1956374 .The function prgp_val_cfg_cat removed
7 -------------------------------------------------------------------------------------------
8 -- Validate the IGS_PR_RU_CA_TYPE start and end sequence_numbers.
9 FUNCTION prgp_val_prct_ci(
10 p_prg_cal_type IN VARCHAR2 ,
11 p_start_sequence_number IN NUMBER ,
12 p_end_sequence_number IN NUMBER ,
13 p_message_name OUT NOCOPY VARCHAR2 )
14 RETURN BOOLEAN AS
15 gv_other_detail VARCHAR2(255);
16 BEGIN -- prgp_val_prct_ci
17 -- Validate the IGS_PR_RU_CA_TYPE start_sequence_number and
18 -- end_sequence_number. Validate the IGS_CA_INST identified by prg_cal_type
19 -- and end_sequence_number does NOT have a start_dt before the start_dt of
20 -- the IGS_CA_INST identified by prg_cal_type and start_sequence_number.
21 DECLARE
22 v_dummy VARCHAR2(1);
23 CURSOR c_start_ci IS
24 SELECT 'X'
25 FROM IGS_CA_INST ci,
26 IGS_CA_STAT cs
27 WHERE ci.cal_type = p_prg_cal_type AND
28 ci.sequence_number = p_start_sequence_number AND
29 ci.cal_status = cs.cal_status AND
30 cs.s_cal_status = 'ACTIVE';
31 CURSOR c_end_ci IS
32 SELECT 'X'
33 FROM IGS_CA_INST ci,
34 IGS_CA_STAT cs
35 WHERE ci.cal_type = p_prg_cal_type AND
36 ci.sequence_number = p_end_sequence_number AND
37 ci.cal_status = cs.cal_status AND
38 cs.s_cal_status = 'ACTIVE';
39 CURSOR c_ci IS
40 SELECT 'X'
41 FROM IGS_CA_INST start_ci,
42 IGS_CA_INST end_ci
43 WHERE start_ci.cal_type = p_prg_cal_type AND
44 start_ci.sequence_number = p_start_sequence_number AND
45 end_ci.cal_type = p_prg_cal_type AND
46 end_ci.sequence_number = p_end_sequence_number AND
47 end_ci.end_dt < start_ci.start_dt;
48 BEGIN
49 -- Set the default message number
50 p_message_name := null;
51 -- Check the start cal instance is active
52 If p_start_sequence_number IS NOT NULL THEN
53 OPEN c_start_ci;
54 FETCH c_start_ci INTO v_dummy;
55 IF c_start_ci%NOTFOUND THEN
56 CLOSE c_start_ci;
57 p_message_name := 'IGS_PR_PER_CAL_MUST_BE_ACTIVE';
58 RETURN FALSE;
59 END IF;
60 CLOSE c_start_ci;
61 END IF;
62 -- Check the end cal instance is active
63 If p_end_sequence_number IS NOT NULL THEN
64 OPEN c_end_ci;
65 FETCH c_end_ci INTO v_dummy;
66 IF c_end_ci%NOTFOUND THEN
67 CLOSE c_end_ci;
68 p_message_name := 'IGS_PR_PER_CAL_MUST_BE_ACTIVE';
69 RETURN FALSE;
70 END IF;
71 CLOSE c_end_ci;
72 END IF;
73 -- Check the end cal instance is not before the start cal instance
74 If p_prg_cal_type IS NOT NULL AND
75 p_start_sequence_number IS NOT NULL AND
76 p_end_sequence_number IS NOT NULL THEN
77 OPEN c_ci;
78 FETCH c_ci INTO v_dummy;
79 IF c_ci%FOUND THEN
80 CLOSE c_ci;
81 p_message_name := 'IGS_PR_CHK_PRG_RUL_CAL_PER';
82 RETURN FALSE;
83 END IF;
84 CLOSE c_ci;
85 END IF;
86 RETURN TRUE;
87 EXCEPTION
88 WHEN OTHERS THEN
89 IF c_start_ci%ISOPEN THEN
90 CLOSE c_ci;
91 END IF;
92 IF c_end_ci%ISOPEN THEN
93 CLOSE c_ci;
94 END IF;
95 IF c_ci%ISOPEN THEN
96 CLOSE c_ci;
97 END IF;
98 RAISE;
99 END;
100 EXCEPTION
101 WHEN OTHERS THEN
102 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
103 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_VAL_PRCT.PRGP_VAL_PRCT_CI');
104 --IGS_GE_MSG_STACK.ADD;
105
106 END prgp_val_prct_ci;
107 --
108 END IGS_PR_VAL_PRCT;