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