[Home] [Help]
PACKAGE BODY: APPS.JG_ZZ_TA_CC_RANGES_PKG
Source
1 PACKAGE BODY JG_ZZ_TA_CC_RANGES_PKG AS
2 /* $Header: jgzztacb.pls 115.1 2002/11/15 17:08:18 arimai ship $ */
3 --
4 -- PUBLIC FUNCTIONS
5 --
6 PROCEDURE Overlap(X_Rowid VARCHAR2,
7 X_rule_set_id NUMBER,
8 X_cc_range_low VARCHAR2,
9 X_cc_range_high VARCHAR2 ) IS
10 CURSOR C1 IS SELECT 'Overlaps'
11 FROM JG_ZZ_TA_CC_RANGES CCS
12 WHERE RULE_SET_ID = X_rule_set_id
13 AND ((CCS.cc_range_low between
14 X_cc_range_low and X_cc_range_high)
15 OR
16 (CCS.cc_range_high between
17 X_cc_range_low and X_cc_range_high)
18 OR
19 (X_cc_range_low between
20 CCS.cc_range_low and CCS.cc_range_high)
21 OR
22 (X_cc_range_high between
23 CCS.cc_range_low and CCS.cc_range_high))
24 AND ROWIDTOCHAR(CCS.rowid) <> nvl(X_rowid, 'x');
25
26 V1 VARCHAR2(21);
27 BEGIN
28 OPEN C1;
29 FETCH C1 INTO V1;
30 IF (C1%FOUND) THEN
31 CLOSE C1;
32 fnd_message.set_name('JG', 'JG_ZZ_TA_CCS_RANGE_OVERLAP');
33 app_exception.raise_exception;
34 END IF;
35 CLOSE C1;
36 END Overlap;
37
38 PROCEDURE Insert_Row( X_rowid IN OUT NOCOPY VARCHAR2
39 ,X_cc_range_id IN OUT NOCOPY NUMBER
40 ,X_rule_set_id IN OUT NOCOPY NUMBER
41 ,X_cc_range_low VARCHAR2
42 ,X_cc_range_high VARCHAR2
43 ,X_creation_date DATE
44 ,X_created_by NUMBER
45 ,X_last_updated_by NUMBER
46 ,X_last_update_date DATE
47 ,X_last_update_login NUMBER
48 ,X_description VARCHAR2
49 ,X_Context VARCHAR2
50 ,X_attribute1 VARCHAR2
51 ,X_attribute2 VARCHAR2
52 ,X_attribute3 VARCHAR2
53 ,X_attribute4 VARCHAR2
54 ,X_attribute5 VARCHAR2
55 ,X_attribute6 VARCHAR2
56 ,X_attribute7 VARCHAR2
57 ,X_attribute8 VARCHAR2
58 ,X_attribute9 VARCHAR2
59 ,X_attribute10 VARCHAR2
60 ,X_attribute11 VARCHAR2
61 ,X_attribute12 VARCHAR2
62 ,X_attribute13 VARCHAR2
63 ,X_attribute14 VARCHAR2
64 ,X_attribute15 VARCHAR2 ) IS
65 CURSOR C IS SELECT rowid
66 FROM JG_ZZ_TA_CC_RANGES
67 WHERE CC_RANGE_ID = X_cc_range_id;
68 CURSOR C2 IS SELECT JG_ZZ_TA_CC_RANGES_S.nextval FROM sys.dual;
69 CURSOR C4 IS SELECT JG_ZZ_TA_RULE_SETS_S.nextval FROM sys.dual;
70 CURSOR C3 IS SELECT 'FOUND'
71 FROM JG_ZZ_TA_CC_RANGES
72 WHERE RULE_SET_ID = X_rule_set_id
73 AND CC_RANGE_LOW = X_cc_range_low
74 AND CC_RANGE_HIGH = X_cc_range_high;
75 l_found varchar2(5);
76 BEGIN
77 IF (X_cc_range_id IS NULL) THEN
78 OPEN C2;
79 FETCH C2 INTO X_cc_range_id;
80 CLOSE C2;
81 END IF;
82 IF (X_rule_set_id IS NULL) THEN
83 OPEN C4;
84 FETCH C4 INTO X_rule_set_id;
85 CLOSE C4;
86 END IF;
87
88 --
89 -- Insert Row
90 --
91 INSERT INTO JG_ZZ_TA_CC_RANGES
92 (
93 rule_set_id
94 ,cc_range_id
95 ,cc_range_low
96 ,cc_range_high
97 ,creation_date
98 ,created_by
99 ,last_updated_by
100 ,last_update_date
101 ,last_update_login
102 ,description
103 ,context
104 ,attribute1
105 ,attribute2
106 ,attribute3
107 ,attribute4
108 ,attribute5
109 ,attribute6
110 ,attribute7
111 ,attribute8
112 ,attribute9
113 ,attribute10
114 ,attribute11
115 ,attribute12
116 ,attribute13
117 ,attribute14
118 ,attribute15
119 )
120 VALUES
121 (
122 X_rule_set_id
123 ,X_cc_range_id
124 ,X_cc_range_low
125 ,X_cc_range_high
126 ,X_creation_date
127 ,X_created_by
128 ,X_last_updated_by
129 ,X_last_update_date
130 ,X_last_update_login
131 ,X_description
132 ,X_context
133 ,X_attribute1
134 ,X_attribute2
135 ,X_attribute3
136 ,X_attribute4
137 ,X_attribute5
138 ,X_attribute6
139 ,X_attribute7
140 ,X_attribute8
141 ,X_attribute9
142 ,X_attribute10
143 ,X_attribute11
144 ,X_attribute12
145 ,X_attribute13
146 ,X_attribute14
147 ,X_attribute15
148 );
149 OPEN C;
150 FETCH C INTO X_rowid;
151 IF (C%NOTFOUND) THEN
152 CLOSE C;
153 raise NO_DATA_FOUND;
154 END IF;
155 CLOSE C;
156 END insert_row;
157
158
159 PROCEDURE Update_Row( X_rowid VARCHAR2
160 ,X_cc_range_id NUMBER
161 ,X_rule_set_id NUMBER
162 ,X_cc_range_low VARCHAR2
163 ,X_cc_range_high VARCHAR2
164 ,X_creation_date DATE
165 ,X_created_by NUMBER
166 ,X_last_updated_by NUMBER
167 ,X_last_update_date DATE
168 ,X_last_update_login NUMBER
169 ,X_description VARCHAR2
170 ,X_Context VARCHAR2
171 ,X_attribute1 VARCHAR2
172 ,X_attribute2 VARCHAR2
173 ,X_attribute3 VARCHAR2
174 ,X_attribute4 VARCHAR2
175 ,X_attribute5 VARCHAR2
176 ,X_attribute6 VARCHAR2
177 ,X_attribute7 VARCHAR2
178 ,X_attribute8 VARCHAR2
179 ,X_attribute9 VARCHAR2
180 ,X_attribute10 VARCHAR2
181 ,X_attribute11 VARCHAR2
182 ,X_attribute12 VARCHAR2
183 ,X_attribute13 VARCHAR2
184 ,X_attribute14 VARCHAR2
185 ,X_attribute15 VARCHAR2 ) IS
186 BEGIN
187 --
188 -- Standard ON-UPDATE routine
189 --
190 UPDATE JG_ZZ_TA_CC_RANGES
191 SET
192 rule_set_id = X_rule_set_id
193 ,cc_range_id = X_cc_range_id /* ??????? */
194 ,cc_range_low = X_cc_range_low
195 ,cc_range_high = X_cc_range_high
196 ,creation_date = X_creation_date
197 ,created_by = X_created_by
198 ,last_updated_by = X_last_updated_by
199 ,last_update_date = X_last_update_date
200 ,last_update_login = X_last_update_login
201 ,description = X_description
202 ,context = X_context
203 ,attribute1 = X_attribute1
204 ,attribute2 = X_attribute2
205 ,attribute3 = X_attribute3
206 ,attribute4 = X_attribute4
207 ,attribute5 = X_attribute5
208 ,attribute6 = X_attribute6
209 ,attribute7 = X_attribute7
210 ,attribute8 = X_attribute8
211 ,attribute9 = X_attribute9
212 ,attribute10 = X_attribute10
213 ,attribute11 = X_attribute11
214 ,attribute12 = X_attribute12
215 ,attribute13 = X_attribute13
216 ,attribute14 = X_attribute14
217 ,attribute15 = X_attribute15
218 WHERE
219 rowid = X_rowid;
220 IF (SQL%NOTFOUND) THEN
221 RAISE NO_DATA_FOUND;
222 END IF;
223 END Update_Row;
224
225 PROCEDURE Delete_Row( X_rowid VARCHAR2 ) IS
226
227 -- ITHEODOR CHANGE START
228 CURSOR Get_Children(P_rowid VARCHAR2) IS
229 SELECT Row_Id
230 FROM JG_ZZ_TA_ACCOUNT_RANGES_V C
231 WHERE C.CC_RANGE_ID = (SELECT P.CC_RANGE_ID
232 FROM JG_ZZ_TA_CC_RANGES P
233 WHERE P.ROWID = P_rowid);
234 -- ITHEODOR CHANGE END
235
236 BEGIN
237 -- ITHEODOR CHANGE START
238 -- Cascade Delete Implementation
239 FOR Rec IN Get_Children(X_rowid) LOOP
240 JG_ZZ_TA_ACCOUNT_RANGES_PKG.Delete_Row( Rec.Row_id );
241 END LOOP;
242 -- ITHEODOR CHANGE END
243
244 DELETE FROM JG_ZZ_TA_CC_RANGES
245 WHERE
246 ROWID = X_rowid;
247 IF (SQL%NOTFOUND) THEN
248 RAISE NO_DATA_FOUND;
249 END IF;
250 END Delete_Row;
251
252 PROCEDURE Lock_Row( X_rowid VARCHAR2
253 ,X_cc_range_id NUMBER
254 ,X_rule_set_id NUMBER
255 ,X_cc_range_low VARCHAR2
256 ,X_cc_range_high VARCHAR2
257 ,X_creation_date DATE
258 ,X_created_by NUMBER
259 ,X_last_updated_by NUMBER
260 ,X_last_update_date DATE
261 ,X_last_update_login NUMBER
262 ,X_description VARCHAR2
263 ,X_Context VARCHAR2
264 ,X_attribute1 VARCHAR2
265 ,X_attribute2 VARCHAR2
266 ,X_attribute3 VARCHAR2
267 ,X_attribute4 VARCHAR2
268 ,X_attribute5 VARCHAR2
269 ,X_attribute6 VARCHAR2
270 ,X_attribute7 VARCHAR2
271 ,X_attribute8 VARCHAR2
272 ,X_attribute9 VARCHAR2
273 ,X_attribute10 VARCHAR2
274 ,X_attribute11 VARCHAR2
275 ,X_attribute12 VARCHAR2
276 ,X_attribute13 VARCHAR2
277 ,X_attribute14 VARCHAR2
278 ,X_attribute15 VARCHAR2 ) IS
279 CURSOR C IS
280 SELECT *
281 FROM JG_ZZ_TA_CC_RANGES
282 WHERE rowid = X_Rowid
283 FOR UPDATE of rule_set_id NOWAIT;
284 Recinfo C%ROWTYPE;
285 BEGIN
286 OPEN C;
287 FETCH C INTO Recinfo;
288 IF (C%NOTFOUND) THEN
289 CLOSE C;
290 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
291 APP_EXCEPTION.raise_exception;
292 END IF;
293 CLOSE C;
294 IF (
295 ( ( (Recinfo.rule_set_id = X_rule_set_id )
296 OR ( (Recinfo.rule_set_id IS NULL)
297 AND (X_rule_set_id IS NULL))))
298 AND ( ( (Recinfo.cc_range_id = X_cc_range_id )
299 OR ( (Recinfo.cc_range_id IS NULL)
300 AND (X_cc_range_id IS NULL))))
301 AND ( ( (Recinfo.cc_range_low = X_cc_range_low )
302 OR ( (Recinfo.cc_range_low IS NULL)
303 AND (X_cc_range_high IS NULL))))
304 AND ( ( (Recinfo.cc_range_high = X_cc_range_high )
305 OR ( (Recinfo.cc_range_high IS NULL)
306 AND (X_cc_range_high IS NULL))))
307 AND ( ( (Recinfo.context = X_context )
308 OR ( (Recinfo.context IS NULL)
309 AND (X_context IS NULL))))
310 AND ( ( (Recinfo.description = X_description )
311 OR ( (Recinfo.description IS NULL)
312 AND (X_description IS NULL))))
313 AND ( ( (Recinfo.attribute1 = X_attribute1 )
314 OR ( (Recinfo.attribute1 IS NULL)
315 AND (X_attribute1 IS NULL))))
316 AND ( ( (Recinfo.attribute2 = X_attribute2 )
317 OR ( (Recinfo.attribute2 IS NULL)
318 AND (X_attribute2 IS NULL))))
319 AND ( ( (Recinfo.attribute3 = X_attribute3 )
320 OR ( (Recinfo.attribute3 IS NULL)
321 AND (X_attribute3 IS NULL))))
322 AND ( ( (Recinfo.attribute4 = X_attribute4 )
323 OR ( (Recinfo.attribute4 IS NULL)
324 AND (X_attribute4 IS NULL))))
325 AND ( ( (Recinfo.attribute5 = X_attribute5 )
326 OR ( (Recinfo.attribute5 IS NULL)
327 AND (X_attribute5 IS NULL))))
328 AND ( ( (Recinfo.attribute6 = X_attribute6 )
329 OR ( (Recinfo.attribute6 IS NULL)
330 AND (X_attribute6 IS NULL))))
331 AND ( ( (Recinfo.attribute7 = X_attribute7 )
332 OR ( (Recinfo.attribute7 IS NULL)
333 AND (X_attribute7 IS NULL))))
334 AND ( ( (Recinfo.attribute8 = X_attribute8 )
335 OR ( (Recinfo.attribute8 IS NULL)
336 AND (X_attribute8 IS NULL))))
337 AND ( ( (Recinfo.attribute9 = X_attribute9 )
338 OR ( (Recinfo.attribute9 IS NULL)
339 AND (X_attribute9 IS NULL))))
340 AND ( ( (Recinfo.attribute10 = X_attribute10 )
341 OR ( (Recinfo.attribute10 IS NULL)
342 AND (X_attribute10 IS NULL))))
343 AND ( ( (Recinfo.attribute11 = X_attribute11 )
344 OR ( (Recinfo.attribute11 IS NULL)
345 AND (X_attribute11 IS NULL))))
346 AND ( ( (Recinfo.attribute12 = X_attribute12 )
347 OR ( (Recinfo.attribute12 IS NULL)
348 AND (X_attribute12 IS NULL))))
349 AND ( ( (Recinfo.attribute13 = X_attribute13 )
350 OR ( (Recinfo.attribute13 IS NULL)
351 AND (X_attribute13 IS NULL))))
352 AND ( ( (Recinfo.attribute14 = X_attribute14 )
353 OR ( (Recinfo.attribute14 IS NULL)
354 AND (X_attribute14 IS NULL))))
355 AND ( ( (Recinfo.attribute15 = X_attribute15 )
356 OR ( (Recinfo.attribute15 IS NULL)
357 AND (X_attribute15 IS NULL))))
358 ) THEN
359 return;
360 ELSE
361 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
362 APP_EXCEPTION.RAISE_EXCEPTION;
363 END IF;
364 END Lock_Row;
365 END JG_ZZ_TA_CC_RANGES_PKG;