1 PACKAGE BODY IGS_PS_VAL_UD AS
2 /* $Header: IGSPS61B.pls 115.5 2002/12/12 09:51:41 smvk ship $ */
3
4 --
5 -- Validate the IGS_PS_DSCP group code for IGS_PS_UNIT IGS_PS_DSCP.
6 FUNCTION crsp_val_ud_dg_cd(
7 p_discipline_group_cd IN VARCHAR2 ,
8 p_message_name OUT NOCOPY VARCHAR2 )
9 RETURN BOOLEAN AS
10 v_closed_ind IGS_PS_DSCP.closed_ind%TYPE;
11 CURSOR c_discipline IS
12 SELECT closed_ind
13 FROM IGS_PS_DSCP
14 WHERE discipline_group_cd = p_discipline_group_cd;
15 BEGIN
16 OPEN c_discipline;
17 FETCH c_discipline INTO v_closed_ind;
18 IF c_discipline%NOTFOUND THEN
19 p_message_name := NULL;
20 CLOSE c_discipline;
21 RETURN TRUE;
22 ELSIF (v_closed_ind = 'N') THEN
23 p_message_name := NULL;
24 CLOSE c_discipline;
25 RETURN TRUE;
26 ELSE
27 p_message_name := 'IGS_PS_DISCP_GRP_CLOSED';
28 CLOSE c_discipline;
29 RETURN FALSE;
30 END IF;
31 EXCEPTION
32 WHEN OTHERS THEN
33 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
34 FND_MESSAGE.SET_TOKEN('NAME','IGS_PS_VAL_UD.crsp_val_ud_dg_cd');
35 IGS_GE_MSG_STACK.ADD;
36 App_Exception.Raise_Exception;
37 END crsp_val_ud_dg_cd;
38 --
39 -- Validate IGS_PS_UNIT IGS_PS_DSCP percentage for the IGS_PS_UNIT version
40 FUNCTION crsp_val_ud_perc(
41 p_unit_cd IN VARCHAR2 ,
42 p_version_number IN NUMBER ,
43 p_message_name OUT NOCOPY VARCHAR2,
44 p_b_lgcy_validator IN BOOLEAN )
45 RETURN BOOLEAN AS
46
47 /***********************************************************************************************
48 Created By :
49 Date Created By:
50 Purpose :
51
52 Known limitations,enhancements,remarks:
53 Change History (in reverse chronological order)
54 Who When What
55 smvk 12-Dec-2002 Added a boolean parameter p_b_lgcy_validator to the function call crsp_val_ud_perc.
56 As a part of the Bug # 2696207
57 ********************************************************************************************** */
58
59 gv_percent NUMBER;
60 gv_unit_discip_exists CHAR;
61 gv_unit_status IGS_PS_UNIT_STAT.s_unit_status%TYPE;
62 CURSOR gc_unit_status IS
63 SELECT US.s_unit_status
64 FROM IGS_PS_UNIT_VER UV,
65 IGS_PS_UNIT_STAT US
66 WHERE UV.unit_cd = p_unit_cd AND
67 UV.version_number = p_version_number AND
68 UV.unit_status = US.unit_status;
69 CURSOR gc_unit_discip_exists IS
70 SELECT 'x'
71 FROM IGS_PS_UNIT_DSCP
72 WHERE unit_cd = p_unit_cd AND
73 version_number = p_version_number;
74
75 CURSOR cur_percent IS
76 SELECT SUM(percentage)
77 FROM IGS_PS_UNIT_DSCP
78 WHERE unit_cd = p_unit_cd AND
79 version_number= p_version_number;
80
81
82 BEGIN
83 -- finding the IGS_PS_UNIT_STAT
84 OPEN gc_unit_status;
85 FETCH gc_unit_status INTO gv_unit_status;
86 -- finding unit_responsibility records
87 OPEN gc_unit_discip_exists;
88 FETCH gc_unit_discip_exists INTO gv_unit_discip_exists;
89 -- Find the sum of all percentages
90
91 OPEN cur_percent;
92 FETCH cur_percent INTO gv_percent;
93
94 IF cur_percent%NOTFOUND THEN
95 RAISE no_data_found;
96 END IF;
97 CLOSE cur_percent;
98
99 -- when the percentage totals 100
100 IF gv_percent = 100.00 THEN
101 CLOSE gc_unit_status;
102 CLOSE gc_unit_discip_exists;
103 p_message_name := NULL;
104 RETURN TRUE;
105 ELSE
106 -- when the percentage doesn't total 100 and
107 -- when the IGS_PS_UNIT_STAT.s_unit_status is PLANNED
108 -- and no IGS_PS_UNIT_DSCP records exists
109 IF (gv_unit_status = 'PLANNED' AND gc_unit_discip_exists%NOTFOUND) AND (NOT p_b_lgcy_validator) THEN
110 CLOSE gc_unit_status;
111 CLOSE gc_unit_discip_exists;
112 p_message_name := NULL;
113 RETURN TRUE;
114 ELSE
115 -- when the percentage doesn't total 100 and
116 -- if the IGS_PS_UNIT_STAT.s_unit_status is not PLANNED
117 -- or no IGS_PS_UNIT responsibility records exists
118 CLOSE gc_unit_status;
119 CLOSE gc_unit_discip_exists;
120 p_message_name := 'IGS_PS_UNITDISCP_NOTTOTAL_100';
121 RETURN FALSE;
122 END IF;
123 END IF;
124 EXCEPTION
125 WHEN no_data_found THEN
126 IF cur_percent%ISOPEN THEN
127 CLOSE cur_percent;
128 END IF;
129 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
130 IGS_GE_MSG_STACK.ADD;
131 App_Exception.Raise_Exception;
132 WHEN OTHERS THEN
133 IF cur_percent%ISOPEN THEN
134 CLOSE cur_percent;
135 END IF;
136 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
137 FND_MESSAGE.SET_TOKEN('NAME','IGS_PS_VAL_UD.crsp_val_ud_perc');
138 IGS_GE_MSG_STACK.ADD;
139 App_Exception.Raise_Exception;
140
141 END crsp_val_ud_perc;
142 END IGS_PS_VAL_UD;
[Home] [Help]