[Home] [Help]
PACKAGE BODY: APPS.GL_BC_RULES_PKG
Source
1 PACKAGE BODY GL_BC_RULES_PKG as
2 /* $Header: glibcrlb.pls 120.3 2005/05/05 01:00:03 kvora ship $ */
3 --
4 -- Package
5 -- gl_bc_rules_pkg
6 -- Purpose
7 -- To contain validation, insertion, and update routines for gl_bc_rules
8 -- History
9 -- 09-12-94 Sharif Rahman Created
10
11 PROCEDURE check_unique_bc_rules( X_rowid VARCHAR2,
12 X_bc_option_id NUMBER,
13 X_je_source_name VARCHAR2,
14 X_je_category_name VARCHAR2 ) IS
15 dummy NUMBER;
16 BEGIN
17 SELECT 1 INTO dummy FROM dual
18 WHERE NOT EXISTS
19 (SELECT 1 FROM gl_bc_option_details
20 WHERE bc_option_id = X_bc_option_id
21 AND je_source_name = X_je_source_name
22 AND je_category_name = X_je_category_name
23 AND ((X_rowid IS NULL) OR (rowid <> X_rowid)));
24 EXCEPTION
25 WHEN NO_DATA_FOUND THEN
26 FND_MESSAGE.set_name('SQLGL', 'GL_DUPLICATE_BC_RULE');
27 APP_EXCEPTION.raise_exception;
28 END check_unique_bc_rules;
29
30 PROCEDURE insert_row(X_rowid IN OUT NOCOPY VARCHAR2 ,
31 X_bc_option_id NUMBER ,
32 X_last_update_date DATE ,
33 X_last_updated_by NUMBER ,
34 X_last_update_login NUMBER ,
35 X_je_source_name VARCHAR2 ,
36 X_je_category_name VARCHAR2 ,
37 X_funds_check_level_code VARCHAR2 ,
38 X_creation_date DATE ,
39 X_created_by NUMBER ,
40 X_override_amount NUMBER ,
41 X_tolerance_percentage NUMBER ,
42 X_tolerance_amount NUMBER ,
43 X_context VARCHAR2 ,
44 X_attribute1 VARCHAR2 ,
45 X_attribute2 VARCHAR2 ,
46 X_attribute3 VARCHAR2 ,
47 X_attribute4 VARCHAR2 ,
48 X_attribute5 VARCHAR2 ,
49 X_attribute6 VARCHAR2 ,
50 X_attribute7 VARCHAR2 ,
51 X_attribute8 VARCHAR2 ,
52 X_attribute9 VARCHAR2 ,
53 X_attribute10 VARCHAR2 ,
54 X_attribute11 VARCHAR2 ,
55 X_attribute12 VARCHAR2 ,
56 X_attribute13 VARCHAR2 ,
57 X_attribute14 VARCHAR2 ,
58 X_attribute15 VARCHAR2 ) IS
59 CURSOR C IS SELECT rowid FROM gl_bc_option_details
60 WHERE bc_option_id = X_bc_option_id
61 AND je_source_name = X_je_source_name
62 AND je_category_name = X_je_category_name;
63 BEGIN
64 INSERT INTO gl_bc_option_details(
65 bc_option_id ,
66 last_update_date ,
67 last_updated_by ,
68 last_update_login ,
69 je_source_name ,
70 je_category_name ,
71 funds_check_level_code ,
72 creation_date ,
73 created_by ,
74 override_amount ,
75 tolerance_percentage ,
76 tolerance_amount ,
77 context ,
78 attribute1 ,
79 attribute2 ,
80 attribute3 ,
81 attribute4 ,
82 attribute5 ,
83 attribute6 ,
84 attribute7 ,
85 attribute8 ,
86 attribute9 ,
87 attribute10 ,
88 attribute11 ,
89 attribute12 ,
90 attribute13 ,
91 attribute14 ,
92 attribute15 )
93 VALUES
94 (X_bc_option_id ,
95 X_last_update_date ,
96 X_last_updated_by ,
97 X_last_update_login ,
98 X_je_source_name ,
99 X_je_category_name ,
100 X_funds_check_level_code ,
101 X_creation_date ,
102 X_created_by ,
103 X_override_amount ,
104 X_tolerance_percentage ,
105 X_tolerance_amount ,
106 X_context ,
107 X_attribute1 ,
108 X_attribute2 ,
109 X_attribute3 ,
110 X_attribute4 ,
111 X_attribute5 ,
112 X_attribute6 ,
113 X_attribute7 ,
114 X_attribute8 ,
115 X_attribute9 ,
116 X_attribute10 ,
117 X_attribute11 ,
118 X_attribute12 ,
119 X_attribute13 ,
120 X_attribute14 ,
121 X_attribute15 );
122 OPEN C;
123 FETCH C INTO X_rowid;
124 IF (C%NOTFOUND) THEN
125 CLOSE C;
126 RAISE NO_DATA_FOUND;
127 END IF;
128 CLOSE C;
129 END insert_row;
130
131
132 PROCEDURE update_row(X_rowid IN OUT NOCOPY VARCHAR2 ,
133 X_bc_option_id NUMBER ,
134 X_last_update_date DATE ,
135 X_last_updated_by NUMBER ,
136 X_last_update_login NUMBER ,
137 X_je_source_name VARCHAR2 ,
138 X_je_category_name VARCHAR2 ,
139 X_funds_check_level_code VARCHAR2 ,
140 X_override_amount NUMBER ,
141 X_tolerance_percentage NUMBER ,
142 X_tolerance_amount NUMBER ,
143 X_context VARCHAR2 ,
144 X_attribute1 VARCHAR2 ,
145 X_attribute2 VARCHAR2 ,
146 X_attribute3 VARCHAR2 ,
147 X_attribute4 VARCHAR2 ,
148 X_attribute5 VARCHAR2 ,
149 X_attribute6 VARCHAR2 ,
150 X_attribute7 VARCHAR2 ,
151 X_attribute8 VARCHAR2 ,
152 X_attribute9 VARCHAR2 ,
153 X_attribute10 VARCHAR2 ,
154 X_attribute11 VARCHAR2 ,
155 X_attribute12 VARCHAR2 ,
156 X_attribute13 VARCHAR2 ,
157 X_attribute14 VARCHAR2 ,
158 X_attribute15 VARCHAR2 ) IS
159 BEGIN
160 UPDATE gl_bc_option_details
161 SET
162 bc_option_id = X_bc_option_id ,
163 last_update_date = X_last_update_date ,
164 last_updated_by = X_last_updated_by ,
165 last_update_login = X_last_update_login ,
166 je_source_name = X_je_source_name ,
167 je_category_name = X_je_category_name ,
168 funds_check_level_code = X_funds_check_level_code ,
169 override_amount = X_override_amount ,
170 tolerance_percentage = X_tolerance_percentage ,
171 tolerance_amount = X_tolerance_amount ,
172 context = X_context ,
173 attribute1 = X_attribute1 ,
174 attribute2 = X_attribute2 ,
175 attribute3 = X_attribute3 ,
176 attribute4 = X_attribute4 ,
177 attribute5 = X_attribute5 ,
178 attribute6 = X_attribute6 ,
179 attribute7 = X_attribute7 ,
180 attribute8 = X_attribute8 ,
181 attribute9 = X_attribute9 ,
182 attribute10 = X_attribute10 ,
183 attribute11 = X_attribute11 ,
184 attribute12 = X_attribute12 ,
185 attribute13 = X_attribute13 ,
186 attribute14 = X_attribute14 ,
187 attribute15 = X_attribute15
188 WHERE rowid = X_rowid;
189
190 IF (SQL%NOTFOUND) THEN
191 RAISE NO_DATA_FOUND;
192 END IF;
193 END update_row;
194
195
196 PROCEDURE lock_row ( X_rowid IN OUT NOCOPY VARCHAR2 ,
197 X_bc_option_id NUMBER ,
198 X_je_source_name VARCHAR2 ,
199 X_je_category_name VARCHAR2 ,
200 X_funds_check_level_code VARCHAR2 ,
201 X_override_amount NUMBER ,
202 X_tolerance_percentage NUMBER ,
203 X_tolerance_amount NUMBER ,
204 X_context VARCHAR2 ,
205 X_attribute1 VARCHAR2 ,
206 X_attribute2 VARCHAR2 ,
207 X_attribute3 VARCHAR2 ,
208 X_attribute4 VARCHAR2 ,
209 X_attribute5 VARCHAR2 ,
210 X_attribute6 VARCHAR2 ,
211 X_attribute7 VARCHAR2 ,
212 X_attribute8 VARCHAR2 ,
213 X_attribute9 VARCHAR2 ,
214 X_attribute10 VARCHAR2 ,
215 X_attribute11 VARCHAR2 ,
216 X_attribute12 VARCHAR2 ,
217 X_attribute13 VARCHAR2 ,
218 X_attribute14 VARCHAR2 ,
219 X_attribute15 VARCHAR2 ) IS
220 CURSOR C IS
221 SELECT *
222 FROM gl_bc_option_details
223 WHERE rowid = X_rowid
224 FOR UPDATE OF bc_option_id NOWAIT;
225 RecInfo C%ROWTYPE;
226 BEGIN
227 OPEN C;
228 FETCH C INTO RecInfo;
229 IF (C%NOTFOUND) THEN
230 CLOSE C;
231 FND_MESSAGE.set_name('FND', 'FORM_RECORD_DELETED');
232 APP_EXCEPTION.RAISE_EXCEPTION;
233 END IF;
234 CLOSE C;
235
236 IF (
237 ( (Recinfo.bc_option_id = X_bc_option_id)
238 OR ( (Recinfo.bc_option_id IS NULL)
239 AND (X_bc_option_id IS NULL)))
240 AND ( (Recinfo.je_source_name = X_je_source_name)
241 OR ( (Recinfo.je_source_name IS NULL)
242 AND (X_je_source_name IS NULL)))
243 AND ( (Recinfo.je_category_name = X_je_category_name)
244 OR ( (Recinfo.je_category_name IS NULL)
245 AND (X_je_category_name IS NULL)))
246 AND ( (Recinfo.funds_check_level_code = X_funds_check_level_code)
247 OR ( (Recinfo.funds_check_level_code IS NULL)
248 AND (X_funds_check_level_code IS NULL)))
249 AND ( (Recinfo.override_amount = X_override_amount)
250 OR ( (Recinfo.override_amount IS NULL)
251 AND (X_override_amount IS NULL)))
252 AND ( (Recinfo.tolerance_percentage = X_tolerance_percentage)
253 OR ( (Recinfo.tolerance_percentage IS NULL)
254 AND (X_tolerance_percentage IS NULL)))
255 AND ( (Recinfo.tolerance_amount = X_tolerance_amount)
256 OR ( (Recinfo.tolerance_amount IS NULL)
257 AND (X_tolerance_amount IS NULL)))
258 AND ( (Recinfo.context = X_context)
259 OR ( (Recinfo.context IS NULL)
260 AND (X_context IS NULL)))
261 AND ( (Recinfo.attribute1 = X_attribute1)
262 OR ( (Recinfo.attribute1 IS NULL)
263 AND (X_attribute1 IS NULL)))
264 AND ( (Recinfo.attribute2 = X_attribute2)
265 OR ( (Recinfo.attribute2 IS NULL)
266 AND (X_attribute2 IS NULL)))
267 AND ( (Recinfo.attribute3 = X_attribute3)
268 OR ( (Recinfo.attribute3 IS NULL)
269 AND (X_attribute3 IS NULL)))
270 AND ( (Recinfo.attribute4 = X_attribute4)
271 OR ( (Recinfo.attribute4 IS NULL)
272 AND (X_attribute4 IS NULL)))
273 AND ( (Recinfo.attribute5 = X_attribute5)
274 OR ( (Recinfo.attribute5 IS NULL)
275 AND (X_attribute5 IS NULL)))
276 AND ( (Recinfo.attribute6 = X_attribute6)
277 OR ( (Recinfo.attribute6 IS NULL)
278 AND (X_attribute6 IS NULL)))
279 AND ( (Recinfo.attribute7 = X_attribute7)
280 OR ( (Recinfo.attribute7 IS NULL)
281 AND (X_attribute7 IS NULL)))
282 AND ( (Recinfo.attribute8 = X_attribute8)
283 OR ( (Recinfo.attribute8 IS NULL)
284 AND (X_attribute8 IS NULL)))
285 AND ( (Recinfo.attribute9 = X_attribute9)
286 OR ( (Recinfo.attribute9 IS NULL)
287 AND (X_attribute9 IS NULL)))
288 AND ( (Recinfo.attribute10 = X_attribute10)
289 OR ( (Recinfo.attribute10 IS NULL)
290 AND (X_attribute10 IS NULL)))
291 AND ( (Recinfo.attribute11 = X_attribute11)
292 OR ( (Recinfo.attribute11 IS NULL)
293 AND (X_attribute11 IS NULL)))
294 AND ( (Recinfo.attribute12 = X_attribute12)
295 OR ( (Recinfo.attribute12 IS NULL)
296 AND (X_attribute12 IS NULL)))
297 AND ( (Recinfo.attribute13 = X_attribute13)
298 OR ( (Recinfo.attribute13 IS NULL)
299 AND (X_attribute13 IS NULL)))
300 AND ( (Recinfo.attribute14 = X_attribute14)
301 OR ( (Recinfo.attribute4 IS NULL)
302 AND (X_attribute14 IS NULL)))
303 AND ( (Recinfo.attribute15 = X_attribute15)
304 OR ( (Recinfo.attribute15 IS NULL)
305 AND (X_attribute15 IS NULL)))
306 ) THEN
307 RETURN;
308 ELSE
309 FND_MESSAGE.set_name('FND', 'FORM_RECORD_CHANGED');
310 APP_EXCEPTION.RAISE_EXCEPTION;
311 END IF;
312 END lock_row;
313
314
315 PROCEDURE delete_row(X_rowid VARCHAR2) IS
316 BEGIN
317 DELETE FROM gl_bc_option_details
318 WHERE rowid = X_rowid;
319
320 IF (SQL%NOTFOUND) THEN
321 RAISE NO_DATA_FOUND;
322 END IF;
323 END delete_row;
324
325
326 FUNCTION default_source_name RETURN VARCHAR2 IS
327 name gl_je_sources.user_je_source_name%TYPE;
328 BEGIN
329 SELECT user_je_source_name
330 INTO name
331 FROM gl_je_sources
332 WHERE je_source_name = 'Other';
333 RETURN (name);
334 END default_source_name;
335
336
337 FUNCTION default_category_name RETURN VARCHAR2 IS
338 name gl_je_categories.user_je_category_name%TYPE;
339 BEGIN
340 SELECT user_je_category_name
341 INTO name
342 FROM gl_je_categories
343 WHERE je_category_name = 'Other';
344 RETURN (name);
345 END default_category_name;
346
347
348 END GL_BC_RULES_PKG;