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