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