[Home] [Help]
PACKAGE BODY: APPS.PAY_WC_STATE_SURCHARGES_PKG
Source
1 PACKAGE BODY PAY_WC_STATE_SURCHARGES_PKG as
2 /* $Header: pywss01t.pkb 115.1 99/07/17 06:50:51 porting ship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT VARCHAR2,
5 X_Surcharge_Id IN OUT NUMBER,
6 X_State_Code VARCHAR2,
7 X_Add_To_Rt VARCHAR2,
8 X_Name VARCHAR2,
9 X_Position VARCHAR2,
10 X_Rate NUMBER
11 ) IS
12 CURSOR C IS SELECT rowid FROM PAY_WC_STATE_SURCHARGES
13 WHERE surcharge_id = X_Surcharge_Id;
14 CURSOR C2 IS SELECT pay_wc_state_surcharges_s.nextval FROM sys.dual;
15 BEGIN
16 --
17 -- check if unique
18 --
19 check_unique( x_surcharge_id,
20 x_state_code,
21 x_name,
22 x_position );
23 --
24 check_position( p_state_code => x_state_code,
25 p_position => x_position,
26 p_event => 'INSERT');
27 --
28 if (X_Surcharge_Id is NULL) then
29 OPEN C2;
30 FETCH C2 INTO X_Surcharge_Id;
31 CLOSE C2;
32 end if;
33
34 INSERT INTO PAY_WC_STATE_SURCHARGES(
35 surcharge_id,
36 state_code,
37 add_to_rt,
38 name,
39 position,
40 rate
41 ) VALUES (
42 X_Surcharge_Id,
43 X_State_Code,
44 X_Add_To_Rt,
45 X_Name,
46 X_Position,
47 X_Rate
48 );
49
50 OPEN C;
51 FETCH C INTO X_Rowid;
52 if (C%NOTFOUND) then
53 CLOSE C;
54 Raise NO_DATA_FOUND;
55 end if;
56 CLOSE C;
57 END Insert_Row;
58
59
60 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
61 X_Surcharge_Id NUMBER,
62 X_State_Code VARCHAR2,
63 X_Add_To_Rt VARCHAR2,
64 X_Name VARCHAR2,
65 X_Position VARCHAR2,
66 X_Rate NUMBER
67 ) IS
68 CURSOR C IS
69 SELECT *
70 FROM PAY_WC_STATE_SURCHARGES
71 WHERE rowid = X_Rowid
72 FOR UPDATE of Surcharge_Id NOWAIT;
73 Recinfo C%ROWTYPE;
74
75
76 BEGIN
77 OPEN C;
78 FETCH C INTO Recinfo;
79 if (C%NOTFOUND) then
80 CLOSE C;
81 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
82 APP_EXCEPTION.Raise_Exception;
83 end if;
84 CLOSE C;
85 Recinfo.state_code := RTRIM(Recinfo.state_code);
86 Recinfo.add_to_rt := RTRIM(Recinfo.add_to_rt);
87 Recinfo.name := RTRIM(Recinfo.name);
88 Recinfo.position := RTRIM(Recinfo.position);
89 if (
90 (Recinfo.surcharge_id = X_Surcharge_Id)
91 AND (Recinfo.state_code = X_State_Code)
92 AND (Recinfo.add_to_rt = X_Add_To_Rt)
93 AND (Recinfo.name = X_Name)
94 AND (Recinfo.position = X_Position)
95 AND (Recinfo.rate = X_Rate)
96 ) then
97 return;
98 else
99 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
100 APP_EXCEPTION.Raise_Exception;
101 end if;
102 END Lock_Row;
103
104
105
106 PROCEDURE Update_Row(X_Rowid VARCHAR2,
107 X_Surcharge_Id NUMBER,
108 X_State_Code VARCHAR2,
109 X_Add_To_Rt VARCHAR2,
110 X_Name VARCHAR2,
111 X_Position VARCHAR2,
112 X_Rate NUMBER
113 ) IS
114 BEGIN
115 --
116 -- check if unique
117 --
118 check_unique( x_surcharge_id,
119 x_state_code,
120 x_name,
121 x_position );
122 --
123 check_position( p_state_code => x_state_code,
124 p_position => x_position,
125 p_event => 'UPDATE');
126 --
127 UPDATE PAY_WC_STATE_SURCHARGES
128 SET
129 surcharge_id = X_Surcharge_Id,
130 state_code = X_State_Code,
131 add_to_rt = X_Add_To_Rt,
132 name = X_Name,
133 position = X_Position,
134 rate = X_Rate
135 WHERE rowid = X_Rowid;
136
137 if (SQL%NOTFOUND) then
138 Raise NO_DATA_FOUND;
139 end if;
140 END Update_Row;
141 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
142 BEGIN
143 DELETE FROM PAY_WC_STATE_SURCHARGES
144 WHERE rowid = X_Rowid;
145
146 if (SQL%NOTFOUND) then
147 Raise NO_DATA_FOUND;
148 end if;
149 END Delete_Row;
150
151 PROCEDURE check_unique (p_surcharge_id NUMBER,
152 p_state_code VARCHAR2,
153 p_name VARCHAR2,
154 p_position VARCHAR2 ) IS
155 --
156 -- declare cursor
157 --
158 CURSOR chk_name IS
159 SELECT
160 'N'
161 FROM
162 pay_wc_state_surcharges wss
163 WHERE
164 wss.name = p_name AND
165 wss.state_code = p_state_code AND
166 (wss.surcharge_id <> p_surcharge_id
167 OR
168 p_surcharge_id IS NULL);
169 --
170 CURSOR chk_position IS
171 SELECT
172 'N'
173 FROM
174 pay_wc_state_surcharges wss
175 WHERE
176 wss.state_code = p_state_code AND
177 wss.position = p_position AND
178 (wss.surcharge_id <> p_surcharge_id
179 OR
180 p_surcharge_id IS NULL);
181 --
182 -- declare local variables
183 --
184 l_unique VARCHAR2(1) := 'Y';
185 --
186 BEGIN
187 --
188 OPEN chk_name;
189 FETCH chk_name INTO l_unique;
190 CLOSE chk_name;
191 --
192 IF (l_unique = 'N')
193 THEN
194 hr_utility.set_message(801, 'PAY_7362_WC_NAME_NOT_UNIQUE');
195 hr_utility.raise_error;
196 END IF;
197 --
198 OPEN chk_position;
199 FETCH chk_position INTO l_unique;
200 CLOSE chk_position;
201 --
202 IF (l_unique = 'N')
203 THEN
204 hr_utility.set_message(801, 'PAY_7363_WC_POS_NOT_UNIQUE');
205 hr_utility.raise_error;
206 END IF;
207 --
208 END check_unique;
209
210 PROCEDURE check_position ( p_state_code VARCHAR2,
211 p_position VARCHAR2,
212 p_event VARCHAR2 ) IS
213 --
214 -- declare local variables
215 --
216 l_position_exists VARCHAR2(1) := 'N';
217 l_position VARCHAR2(30);
218 --
219 -- declare cursor
220 --
221 CURSOR chk_position IS
222 SELECT
223 'Y'
224 FROM
225 pay_wc_state_surcharges wss
226 WHERE
227 wss.position = l_position AND
228 wss.state_code = p_state_code;
229 --
230 BEGIN
231 --
232 IF (p_event = 'DELETE')
233 THEN
234 IF(p_position = 'POST_EXP_MOD_1')
235 THEN
236 l_position := 'POST_EXP_MOD_2';
237 --
238 OPEN chk_position;
239 FETCH chk_position INTO l_position_exists;
240 CLOSE chk_position;
241 --
242 IF (l_position_exists = 'Y')
243 THEN
244 hr_utility.set_message(801, 'PAY_7365_WC_DEL_POS_2_FIRST');
245 hr_utility.raise_error;
246 END IF;
247 END IF;
248 --
249 ELSIF (p_event IN ('INSERT', 'UPDATE') )
250 THEN
251 IF(p_position = 'POST_EXP_MOD_2')
252 THEN
253 l_position := 'POST_EXP_MOD_1';
254 --
255 OPEN chk_position;
256 FETCH chk_position INTO l_position_exists;
257 CLOSE chk_position;
258 IF (l_position_exists = 'N')
259 THEN
260 hr_utility.set_message(801, 'PAY_7364_WC_WC_INS_POS_1_FIRST');
261 hr_utility.raise_error;
262 END IF;
263 END IF;
264 --
265 ELSE
266 hr_utility.raise_error;
267 END IF;
268 --
269 END check_position;
270
271
272 END PAY_WC_STATE_SURCHARGES_PKG;