[Home] [Help]
PACKAGE BODY: APPS.JE_GR_IE_RULES_PKG
Source
1 package body JE_GR_IE_RULES_PKG as
2 /* $Header: jegrerb.pls 120.5 2006/04/27 09:27:34 samalhot ship $ */
3
4 PROCEDURE INSERT_ROW (
5 p_application_id IN NUMBER,
6 p_rule_id IN OUT NOCOPY NUMBER,
7 p_rule_name IN VARCHAR2,
8 p_enabled_flag IN VARCHAR2,
9 p_description IN VARCHAR2,
10 p_attribute_category IN VARCHAR2,
11 p_attribute1 IN VARCHAR2,
12 p_attribute2 IN VARCHAR2,
13 p_attribute3 IN VARCHAR2,
14 p_attribute4 IN VARCHAR2,
15 p_attribute5 IN VARCHAR2,
16 p_attribute6 IN VARCHAR2,
17 p_attribute7 IN VARCHAR2,
18 p_attribute8 IN VARCHAR2,
19 p_attribute9 IN VARCHAR2,
20 p_attribute10 IN VARCHAR2,
21 p_attribute11 IN VARCHAR2,
22 p_attribute12 IN VARCHAR2,
23 p_attribute13 IN VARCHAR2,
24 p_attribute14 IN VARCHAR2,
25 p_attribute15 IN VARCHAR2,
26 p_creation_date IN DATE,
27 p_created_by IN NUMBER,
28 p_last_update_date IN DATE,
29 p_last_updated_by IN NUMBER,
30 p_last_update_login IN NUMBER,
31 p_legal_entity_id IN NUMBER) IS
32
33 l_rule_id je_gr_trnovr_rules.trnovr_rule_id%TYPE;
34
35 debug_info VARCHAR2(250);
36 current_calling_sequence VARCHAR2(250);
37
38
39 BEGIN
40
41 -- Update the calling sequence
42 --
43 current_calling_sequence :=
44 'JE_GR_IE_RULES_PKG.INSERT_ROW';
45
46 debug_info := 'Get Rule ID from the Sequence';
47
48 SELECT je_gr_trnovr_rules_s.nextval INTO l_rule_id FROM dual;
49
50 debug_info := 'Insert row in je_gr_trnovr_rules';
51
52 INSERT INTO JE_GR_TRNOVR_RULES (
53 application_id,
54 trnovr_rule_id,
55 trnovr_rule_name,
56 enabled_flag,
57 description,
58 attribute_category ,
59 attribute1 ,
60 attribute2 ,
61 attribute3 ,
62 attribute4 ,
63 attribute5 ,
64 attribute6 ,
65 attribute7 ,
66 attribute8 ,
67 attribute9 ,
68 attribute10 ,
69 attribute11 ,
70 attribute12 ,
71 attribute13 ,
72 attribute14 ,
73 attribute15 ,
74 creation_date,
75 created_by,
76 last_update_date,
77 last_updated_by,
78 last_update_login,
79 legal_entity_id
80 )
81 SELECT
82 p_application_id,
83 l_rule_id,
84 p_rule_name,
85 p_enabled_flag,
86 p_description,
87 p_attribute_category ,
88 p_attribute1 ,
89 p_attribute2 ,
90 p_attribute3 ,
91 p_attribute4 ,
92 p_attribute5 ,
93 p_attribute6 ,
94 p_attribute7 ,
95 p_attribute8 ,
96 p_attribute9 ,
97 p_attribute10 ,
98 p_attribute11 ,
99 p_attribute12 ,
100 p_attribute13 ,
101 p_attribute14 ,
102 p_attribute15 ,
103 p_creation_date,
104 p_created_by,
105 p_last_update_date,
106 p_last_updated_by,
107 p_last_update_login,
108 p_legal_entity_id
109 FROM DUAL
110 WHERE NOT EXISTS ( SELECT NULL
111 FROM je_gr_trnovr_rules
112 WHERE application_id = p_application_id
113 AND trnovr_rule_name = p_rule_name);
114
115 p_rule_id := l_rule_id;
116
117 EXCEPTION
118
119 WHEN OTHERS THEN
120 IF (SQLCODE <> -20001) THEN
121 fnd_message.set_name('JE','JE_DEBUG');
122 fnd_message.set_token('ERROR',SQLERRM);
123 fnd_message.set_token('CALLING_SEQUENCE', current_calling_sequence);
124 fnd_message.set_token('PARAMETERS',
125 'p_application_id = '||to_char(p_application_id)
126 ||'p_rule_id = '||to_char(p_rule_id)
127 ||'p_rule_name = '||p_rule_name
128 ||'p_enabled_flag = '||p_enabled_flag
129 ||'p_description = '||p_description
130 ||'p_creation_date = '||to_char(p_creation_date)
131 ||'p_created_by = '||to_char(p_created_by)
132 ||'p_last_update_date = '||to_char(p_last_update_date)
133 ||'p_last_updated_by = '||to_char(p_last_updated_by)
134 ||'p_last_update_login = '||to_char(p_last_update_login)
135 ||'p_legal_entity_id = ' || p_legal_entity_id );
136
137 fnd_message.set_token('DEBUG_INFO',debug_info);
138
139 END IF;
140 app_exception.raise_exception;
141
142
143 END INSERT_ROW;
144
145 PROCEDURE LOCK_ROW (
146 p_application_id IN NUMBER,
147 p_rule_id IN NUMBER,
148 p_rule_name IN VARCHAR2,
149 p_description IN VARCHAR2,
150 p_enabled_flag IN VARCHAR2,
151 p_attribute_category IN VARCHAR2,
152 p_attribute1 IN VARCHAR2,
153 p_attribute2 IN VARCHAR2,
154 p_attribute3 IN VARCHAR2,
155 p_attribute4 IN VARCHAR2,
156 p_attribute5 IN VARCHAR2,
157 p_attribute6 IN VARCHAR2,
158 p_attribute7 IN VARCHAR2,
159 p_attribute8 IN VARCHAR2,
160 p_attribute9 IN VARCHAR2,
161 p_attribute10 IN VARCHAR2,
162 p_attribute11 IN VARCHAR2,
163 p_attribute12 IN VARCHAR2,
164 p_attribute13 IN VARCHAR2,
165 p_attribute14 IN VARCHAR2,
166 p_attribute15 IN VARCHAR2
167 ) IS
168
169 CURSOR c IS
170 SELECT
171 trnovr_rule_name,
172 description,
173 enabled_flag
174 FROM je_gr_trnovr_rules
175 WHERE trnovr_rule_id = p_rule_id
176 AND application_id = p_application_id
177 FOR UPDATE OF trnovr_rule_id nowait;
178
179 recinfo c%ROWTYPE;
180
181 BEGIN
182 OPEN c;
183 FETCH c INTO recinfo;
184 IF (c%NOTFOUND) THEN
185 CLOSE c;
186 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
187 app_exception.raise_exception;
188 END IF;
189 CLOSE c;
190
191 IF ((recinfo.trnovr_rule_name = p_rule_name) AND
192 (recinfo.enabled_flag = p_enabled_flag)) THEN
193 null;
194 ELSE
195
196 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
197 app_exception.raise_exception;
198
199 END IF;
200
201 return;
202
203 END LOCK_ROW;
204
205 PROCEDURE UPDATE_ROW (
206 p_application_id IN NUMBER,
207 p_rule_id IN NUMBER,
208 p_rule_name IN VARCHAR2,
209 p_description IN VARCHAR2,
210 p_enabled_flag IN VARCHAR2,
211 p_attribute_category IN VARCHAR2,
212 p_attribute1 IN VARCHAR2,
213 p_attribute2 IN VARCHAR2,
214 p_attribute3 IN VARCHAR2,
215 p_attribute4 IN VARCHAR2,
216 p_attribute5 IN VARCHAR2,
217 p_attribute6 IN VARCHAR2,
218 p_attribute7 IN VARCHAR2,
219 p_attribute8 IN VARCHAR2,
220 p_attribute9 IN VARCHAR2,
221 p_attribute10 IN VARCHAR2,
222 p_attribute11 IN VARCHAR2,
223 p_attribute12 IN VARCHAR2,
224 p_attribute13 IN VARCHAR2,
225 p_attribute14 IN VARCHAR2,
226 p_attribute15 IN VARCHAR2,
227 p_last_update_date IN DATE,
228 p_last_updated_by IN NUMBER,
229 p_last_update_login IN NUMBER,
230 p_legal_entity_id IN NUMBER
231 ) IS
232
233 debug_info VARCHAR2(250);
234 current_calling_sequence VARCHAR2(250);
235
236 BEGIN
237
238 -- Update the calling sequence
239 --
240 current_calling_sequence :=
241 'JE_GR_IE_RULES_PKG.UPDATE_ROW';
242
243
244 debug_info := 'Update row in je_gr_trnovr_rules';
245
246 UPDATE je_gr_trnovr_rules
247 SET
248 trnovr_rule_name = p_rule_name,
249 description = p_description,
250 enabled_flag = p_enabled_flag,
251 attribute_category = p_attribute_category,
252 attribute1 = p_attribute1,
253 attribute2 = p_attribute2,
254 attribute3 = p_attribute3,
255 attribute4 = p_attribute4,
256 attribute5 = p_attribute5,
257 attribute6 = p_attribute6,
258 attribute7 = p_attribute7,
259 attribute8 = p_attribute8,
260 attribute9 = p_attribute9,
261 attribute10 = p_attribute10,
262 attribute11 = p_attribute11,
263 attribute12 = p_attribute12,
264 attribute13 = p_attribute13,
265 attribute14 = p_attribute14,
266 attribute15 = p_attribute15,
267 last_update_date = p_last_update_date,
268 last_updated_by = p_last_updated_by,
269 last_update_login = p_last_update_login,
270 legal_entity_id = p_legal_entity_id
271 WHERE trnovr_rule_id = p_rule_id
272 AND application_id = p_application_id;
273
274 EXCEPTION
275
276 WHEN OTHERS THEN
277 IF (SQLCODE <> -20001) THEN
278 fnd_message.set_name('JE','JE_DEBUG');
279 fnd_message.set_token('ERROR',SQLERRM);
280 fnd_message.set_token('CALLING_SEQUENCE', current_calling_sequence);
281 fnd_message.set_token('PARAMETERS',
282 'p_application_id = '||to_char(p_application_id)
283 ||'p_rule_id = '||to_char(p_rule_id)
284 ||'p_rule_name = '||p_rule_name
285 ||'p_enabled_flag = '||p_enabled_flag
286 ||'p_description = '||p_description
287 ||'p_last_update_date = '||to_char(p_last_update_date)
288 ||'p_last_updated_by = '||to_char(p_last_updated_by)
289 ||'p_last_update_login = '||to_char(p_last_update_login)
290 ||'p_legal_entity = '||p_legal_entity_id);
291 fnd_message.set_token('DEBUG_INFO',debug_info);
292 END IF;
293 app_exception.raise_exception;
294
295 END UPDATE_ROW;
296
297 PROCEDURE DELETE_ROW (
298 p_application_id IN NUMBER,
299 p_rule_id IN NUMBER
300 ) IS
301
302 debug_info VARCHAR2(250);
303 current_calling_sequence VARCHAR2(250);
304
305 BEGIN
306
307 -- Update the calling sequence
308 --
309 current_calling_sequence :=
310 'JE_GR_IE_RULES_PKG.DELETE_ROW';
311
312 debug_info := 'delete row in je_gr_trnovr_rule_lines';
313
314 DELETE FROM je_gr_trnovr_rule_lines
315 WHERE trnovr_rule_id = p_rule_id;
316
317 debug_info := 'delete row in je_gr_trnovr_rules';
318
319 DELETE FROM je_gr_trnovr_rules
320 WHERE application_id = p_application_id
321 AND trnovr_rule_id = p_rule_id;
322
323 EXCEPTION
324
325 WHEN OTHERS THEN
326 IF (SQLCODE <> -20001) THEN
327 fnd_message.set_name('JE','JE_DEBUG');
328 fnd_message.set_token('ERROR',SQLERRM);
329 fnd_message.set_token('CALLING_SEQUENCE', current_calling_sequence);
330 fnd_message.set_token('PARAMETERS',
331 'p_application_id = '||to_char(p_application_id)
332 ||'p_rule_id = '||to_char(p_rule_id));
333 fnd_message.set_token('DEBUG_INFO',debug_info);
334 END IF;
335 app_exception.raise_exception;
336
337 END DELETE_ROW;
338
339 END JE_GR_IE_RULES_PKG;