[Home] [Help]
PACKAGE BODY: APPS.JL_ZZ_AR_TX_ATT_VAL_PKG
Source
1 PACKAGE BODY JL_ZZ_AR_TX_ATT_VAL_PKG as
2 /* $Header: jlzztavb.pls 120.2 2003/03/03 19:30:55 opedrega ship $ */
3
4 PROCEDURE Insert_Row
5 (X_rowid IN OUT NOCOPY VARCHAR2,
6 X_tax_categ_attr_val_id NUMBER,
7 X_tax_category_id NUMBER,
8 X_tax_attribute_type VARCHAR2,
9 X_tax_attribute_name VARCHAR2,
10 X_tax_attribute_value VARCHAR2,
11 X_tax_attr_value_code VARCHAR2,
12 X_default_to_class VARCHAR2,
13 X_org_id NUMBER,
14 X_last_update_date DATE,
15 X_last_updated_by NUMBER,
16 X_creation_date DATE,
17 X_created_by NUMBER,
18 X_last_update_login NUMBER,
19 X_attribute_category VARCHAR2,
20 X_attribute1 VARCHAR2,
21 X_attribute2 VARCHAR2,
22 X_attribute3 VARCHAR2,
23 X_attribute4 VARCHAR2,
24 X_attribute5 VARCHAR2,
25 X_attribute6 VARCHAR2,
26 X_attribute7 VARCHAR2,
27 X_attribute8 VARCHAR2,
28 X_attribute9 VARCHAR2,
29 X_attribute10 VARCHAR2,
30 X_attribute11 VARCHAR2,
31 X_attribute12 VARCHAR2,
32 X_attribute13 VARCHAR2,
33 X_attribute14 VARCHAR2,
34 X_attribute15 VARCHAR2,
35 X_calling_sequence IN VARCHAR2) IS
36
37 CURSOR C IS
38 SELECT rowid
39 FROM jl_zz_ar_tx_att_val
40 WHERE tax_categ_attr_val_id = X_tax_categ_attr_val_id;
41
42 current_calling_sequence VARCHAR2(2000);
43 debug_info VARCHAR2(100);
44
45 BEGIN
46 -- Update the calling sequence
47 --
48 current_calling_sequence := 'JL_ZZ_AR_TX_ATT_VAL_PKG.INSERT_ROW<-' ||
49 X_calling_sequence;
50
51 debug_info := 'Insert into JL_ZZ_AR_TX_ATT_VAL';
52 insert into jl_zz_ar_tx_att_val(tax_categ_attr_val_id,
53 tax_category_id,
54 tax_attribute_type,
55 tax_attribute_name,
56 tax_attribute_value,
57 tax_attr_value_code,
58 default_to_class,
59 org_id,
60 last_update_date,
61 last_updated_by,
62 creation_date,
63 created_by,
64 last_update_login,
65 attribute_category,
66 attribute1,
67 attribute2,
68 attribute3,
69 attribute4,
70 attribute5,
71 attribute6,
72 attribute7,
73 attribute8,
74 attribute9,
75 attribute10,
76 attribute11,
77 attribute12,
78 attribute13,
79 attribute14,
80 attribute15)
81 VALUES (X_tax_categ_attr_val_id,
82 X_tax_category_id,
83 X_tax_attribute_type,
84 X_tax_attribute_name,
85 X_tax_attribute_value,
86 X_tax_attr_value_code,
87 X_default_to_class,
88 X_org_id,
89 X_last_update_date,
90 X_last_updated_by,
91 X_creation_date,
92 X_created_by,
93 X_last_update_login,
94 X_attribute_category,
95 X_attribute1,
96 X_attribute2,
97 X_attribute3,
98 X_attribute4,
99 X_attribute5,
100 X_attribute6,
101 X_attribute7,
102 X_attribute8,
103 X_attribute9,
104 X_attribute10,
105 X_attribute11,
106 X_attribute12,
107 X_attribute13,
108 X_attribute14,
109 X_attribute15);
110
111 debug_info := 'Open cursor C';
112 OPEN C;
113 debug_info := 'Fetch cursor C';
114 FETCH C INTO X_rowid;
115 if (C%NOTFOUND) then
116 debug_info := 'Close cursor C - DATA NOTFOUND';
117 CLOSE C;
118 Raise NO_DATA_FOUND;
119 end if;
120 debug_info := 'Close cursor C';
121 CLOSE C;
122 EXCEPTION
123 WHEN OTHERS THEN
124 IF (SQLCODE <> -20001) THEN
125 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
126 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
127 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
128 FND_MESSAGE.SET_TOKEN('PARAMETERS',
129 'tax_categ_attr_val_id = ' || X_tax_categ_attr_val_id);
130 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
131 END IF;
132 APP_EXCEPTION.RAISE_EXCEPTION;
133 END Insert_Row;
134
135 PROCEDURE Lock_Row
136 (X_rowid VARCHAR2,
137 X_tax_categ_attr_val_id NUMBER,
138 X_tax_category_id NUMBER,
139 X_tax_attribute_type VARCHAR2,
140 X_tax_attribute_name VARCHAR2,
141 X_tax_attribute_value VARCHAR2,
142 X_tax_attr_value_code VARCHAR2,
143 X_default_to_class VARCHAR2,
144 X_org_id NUMBER,
145 X_last_update_date DATE,
146 X_last_updated_by NUMBER,
147 X_creation_date DATE,
148 X_created_by NUMBER,
149 X_last_update_login NUMBER,
150 X_attribute_category VARCHAR2,
151 X_attribute1 VARCHAR2,
152 X_attribute2 VARCHAR2,
153 X_attribute3 VARCHAR2,
154 X_attribute4 VARCHAR2,
155 X_attribute5 VARCHAR2,
156 X_attribute6 VARCHAR2,
157 X_attribute7 VARCHAR2,
158 X_attribute8 VARCHAR2,
159 X_attribute9 VARCHAR2,
160 X_attribute10 VARCHAR2,
161 X_attribute11 VARCHAR2,
162 X_attribute12 VARCHAR2,
163 X_attribute13 VARCHAR2,
164 X_attribute14 VARCHAR2,
165 X_attribute15 VARCHAR2,
166 X_calling_sequence IN VARCHAR2) IS
167
168 CURSOR C IS
169 SELECT TAX_CATEG_ATTR_VAL_ID,
170 TAX_CATEGORY_ID,
171 TAX_ATTRIBUTE_TYPE,
172 TAX_ATTRIBUTE_NAME,
173 TAX_ATTRIBUTE_VALUE,
174 TAX_ATTR_VALUE_CODE,
175 DEFAULT_TO_CLASS,
176 ORG_ID,
177 LAST_UPDATE_DATE,
178 LAST_UPDATED_BY,
179 LAST_UPDATE_LOGIN,
180 CREATION_DATE,
181 CREATED_BY,
182 ATTRIBUTE_CATEGORY,
183 ATTRIBUTE1,
184 ATTRIBUTE2,
185 ATTRIBUTE3,
186 ATTRIBUTE4,
187 ATTRIBUTE5,
188 ATTRIBUTE6,
189 ATTRIBUTE7,
190 ATTRIBUTE8,
191 ATTRIBUTE9,
192 ATTRIBUTE10,
193 ATTRIBUTE11,
194 ATTRIBUTE12,
195 ATTRIBUTE13,
196 ATTRIBUTE14,
197 ATTRIBUTE15
198 FROM jl_zz_ar_tx_att_val
199 WHERE tax_categ_attr_val_id = X_tax_categ_attr_val_id
200 FOR UPDATE of tax_categ_attr_val_id
201 NOWAIT;
202
203 Recinfo C%ROWTYPE;
204
205 current_calling_sequence VARCHAR2(2000);
206 debug_info VARCHAR2(100);
207
208 BEGIN
209 -- Update the calling sequence
210 --
211 current_calling_sequence := 'JL_ZZ_AR_TX_ATT_VAL_PKG.LOCK_ROW<-' ||
212 X_calling_sequence;
213 debug_info := 'Open cursor C';
214 OPEN C;
215 debug_info := 'Fetch cursor C';
216 FETCH C INTO Recinfo;
217 IF (C%NOTFOUND) THEN
218 debug_info := 'Close cursor C - DATA NOTFOUND';
219 CLOSE C;
220 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
221 APP_EXCEPTION.Raise_Exception;
222 END IF;
223
224 debug_info := 'Close cursor C';
225 CLOSE C;
226 IF ((Recinfo.tax_categ_attr_val_id = X_tax_categ_attr_val_id) AND
227 (Recinfo.tax_category_id = X_tax_category_id) AND
228 (Recinfo.tax_attribute_type = X_tax_attribute_type) AND
229 (Recinfo.tax_attribute_name = X_tax_attribute_name) AND
230 (Recinfo.tax_attribute_value = X_tax_attribute_value) AND
231 ((Recinfo.tax_attr_value_code = X_tax_attr_value_code) OR
232 ((Recinfo.tax_attr_value_code IS NULL) AND
233 (X_tax_attr_value_code IS NULL))) AND
234 (Recinfo.default_to_class = X_default_to_class) AND
235 ((Recinfo.org_id = X_org_id) OR
236 ((Recinfo.org_id IS NULL) AND
237 (X_org_id IS NULL))) AND
238 (Recinfo.last_updated_by = X_last_updated_by) AND
239 (Recinfo.last_update_date = X_last_update_date) AND
240 ((Recinfo.created_by = X_created_by) OR
241 ((Recinfo.created_by IS NULL) AND
242 (X_created_by IS NULL))) AND
243 ((Recinfo.creation_date = X_creation_date) OR
244 ((Recinfo.creation_date IS NULL) AND
245 (X_creation_date IS NULL))) AND
246 ((Recinfo.last_update_login = X_last_update_login) OR
247 ((Recinfo.last_update_login IS NULL) AND
248 (X_last_update_login IS NULL))) AND
249 ((Recinfo.attribute_category = X_attribute_Category) OR
250 ((Recinfo.attribute_category IS NULL) AND
251 (X_attribute_category IS NULL))) AND
252 ((Recinfo.attribute1 = X_attribute1) OR
253 ((Recinfo.attribute1 IS NULL) AND
254 (X_attribute1 IS NULL))) AND
255 ((Recinfo.attribute2 = X_attribute2) OR
256 ((Recinfo.attribute2 IS NULL) AND
257 (X_attribute2 IS NULL))) AND
258 ((Recinfo.attribute3 = X_attribute3) OR
259 ((Recinfo.attribute3 IS NULL) AND
260 (X_attribute3 IS NULL))) AND
261 ((Recinfo.attribute4 = X_attribute4) OR
262 ((Recinfo.attribute4 IS NULL) AND
263 (X_attribute4 IS NULL))) AND
264 ((Recinfo.attribute5 = X_attribute5) OR
265 ((Recinfo.attribute5 IS NULL) AND
266 (X_attribute5 IS NULL))) AND
267 ((Recinfo.attribute6 = X_attribute6) OR
268 ((Recinfo.attribute6 IS NULL) AND
269 (X_attribute6 IS NULL))) AND
270 ((Recinfo.attribute7 = X_attribute7) OR
271 ((Recinfo.attribute7 IS NULL) AND
272 (X_attribute7 IS NULL))) AND
273 ((Recinfo.attribute8 = X_attribute8) OR
274 ((Recinfo.attribute8 IS NULL) AND
275 (X_attribute8 IS NULL))) AND
276 ((Recinfo.attribute9 = X_attribute9) OR
277 ((Recinfo.attribute9 IS NULL) AND
278 (X_attribute9 IS NULL))) AND
279 ((Recinfo.attribute10 = X_attribute10) OR
280 ((Recinfo.attribute10 IS NULL) AND
281 (X_attribute10 IS NULL))) AND
282 ((Recinfo.attribute11 = X_attribute11) OR
283 ((Recinfo.attribute11 IS NULL) AND
284 (X_attribute11 IS NULL))) AND
285 ((Recinfo.attribute12 = X_attribute12) OR
286 ((Recinfo.attribute12 IS NULL) AND
287 (X_attribute12 IS NULL))) AND
288 ((Recinfo.attribute13 = X_attribute13) OR
289 ((Recinfo.attribute13 IS NULL) AND
290 (X_attribute13 IS NULL))) AND
291 ((Recinfo.attribute14 = X_attribute14) OR
292 ((Recinfo.attribute14 IS NULL) AND
293 (X_attribute14 IS NULL))) AND
294 ((Recinfo.attribute15 = X_attribute15) OR
295 ((Recinfo.attribute15 IS NULL) AND
296 (X_attribute15 IS NULL)))) THEN
297 return;
298 ELSE
299 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
300 APP_EXCEPTION.Raise_Exception;
301 END IF;
302
303 EXCEPTION
304 WHEN OTHERS THEN
305 IF (SQLCODE <> -20001) THEN
306 IF (SQLCODE = -54) THEN
307 FND_MESSAGE.SET_NAME('AR','AR_RESOURCE_BUSY');
308 ELSE
309 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
310 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
311 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
312 FND_MESSAGE.SET_TOKEN('PARAMETERS',
313 'tax_categ_attr_val_id = ' ||X_tax_categ_attr_val_id);
314 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
315 END IF;
316 END IF;
317 APP_EXCEPTION.RAISE_EXCEPTION;
318 END Lock_Row;
319
320 PROCEDURE Update_Row
321 (X_rowid VARCHAR2,
322 X_tax_categ_attr_val_id NUMBER,
323 X_tax_category_id NUMBER,
324 X_tax_attribute_type VARCHAR2,
325 X_tax_attribute_name VARCHAR2,
326 X_tax_attribute_value VARCHAR2,
327 X_tax_attr_value_code VARCHAR2,
328 X_default_to_class VARCHAR2,
329 X_org_id NUMBER,
330 X_last_update_date DATE,
331 X_last_updated_by NUMBER,
332 X_creation_date DATE,
333 X_created_by NUMBER,
334 X_last_update_login NUMBER,
335 X_attribute_category VARCHAR2,
336 X_attribute1 VARCHAR2,
337 X_attribute2 VARCHAR2,
338 X_attribute3 VARCHAR2,
339 X_attribute4 VARCHAR2,
340 X_attribute5 VARCHAR2,
341 X_attribute6 VARCHAR2,
342 X_attribute7 VARCHAR2,
343 X_attribute8 VARCHAR2,
344 X_attribute9 VARCHAR2,
345 X_attribute10 VARCHAR2,
346 X_attribute11 VARCHAR2,
347 X_attribute12 VARCHAR2,
348 X_attribute13 VARCHAR2,
349 X_attribute14 VARCHAR2,
350 X_attribute15 VARCHAR2,
351 X_calling_sequence IN VARCHAR2) IS
352
353 BEGIN
354 UPDATE jl_zz_ar_tx_att_val
355 SET tax_categ_attr_val_id = X_tax_categ_attr_val_id,
356 tax_category_id = X_tax_category_id,
360 tax_attr_value_code = X_tax_attr_value_code,
357 tax_attribute_type = X_tax_attribute_type,
358 tax_attribute_name = X_tax_attribute_name,
359 tax_attribute_value = X_tax_attribute_value,
361 default_to_class = X_default_to_class,
362 org_id = X_org_id,
363 last_update_date = X_last_update_date,
364 last_updated_by = X_last_updated_by,
365 creation_date = X_creation_date,
366 created_by = X_created_by,
367 last_update_login = X_last_update_login,
368 attribute_category = X_attribute_category,
369 attribute1 = X_attribute1,
370 attribute2 = X_attribute2,
371 attribute3 = X_attribute3,
372 attribute4 = X_attribute4,
373 attribute5 = X_attribute5,
374 attribute6 = X_attribute6,
375 attribute7 = X_attribute7,
376 attribute8 = X_attribute8,
377 attribute9 = X_attribute9,
378 attribute10 = X_attribute10,
379 attribute11 = X_attribute11,
380 attribute12 = X_attribute12,
381 attribute13 = X_attribute13,
382 attribute14 = X_attribute14,
383 attribute15 = X_attribute15
384 WHERE rowid = X_rowid;
385
386 IF (SQL%NOTFOUND) THEN
387 raise NO_DATA_FOUND;
388 END IF;
389 END Update_Row;
390
391 PROCEDURE Delete_Row
392 (X_rowid VARCHAR2) IS
393 BEGIN
394 DELETE FROM jl_zz_ar_tx_att_val
395 WHERE rowid = X_rowid;
396
397 IF (SQL%NOTFOUND) THEN
398 raise NO_DATA_FOUND;
399 END IF;
400 END Delete_Row;
401
402 PROCEDURE Check_Unique
403 (X_rowid VARCHAR2,
404 X_tax_category_id NUMBER,
405 X_tax_attribute_type VARCHAR2,
406 X_tax_attribute_name VARCHAR2,
407 X_tax_attribute_value VARCHAR2,
408 X_org_id NUMBER,
409 X_calling_sequence IN VARCHAR2) IS
410
411 l_dummy NUMBER;
412 current_calling_sequence VARCHAR2(2000);
413 debug_info VARCHAR2(100);
414
415 BEGIN
416 -- Update the calling sequence
417 --
418 current_calling_sequence :='JL_ZZ_AR_TX_ATT_VAL_PKG.CHECK_UNIQUE<-' ||
419 X_calling_sequence;
420 SELECT COUNT(1)
421 INTO l_dummy
422 FROM jl_zz_ar_tx_att_val
423 WHERE tax_category_id = X_tax_category_id
424 AND tax_attribute_type = X_tax_attribute_type
425 AND tax_attribute_name = X_tax_attribute_name
426 AND tax_attribute_value = X_tax_attribute_value
427 AND ((X_rowid IS NULL) OR (rowid <> X_rowid))
428 AND org_id = X_org_id;
429
430 IF (l_dummy >=1) THEN
431 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
432 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
433 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
434 FND_MESSAGE.SET_TOKEN('PARAMETERS',
435 ' tax_category_id = ' || X_tax_category_id ||
436 ' tax_attribute_type = ' || X_tax_attribute_type ||
437 ' tax_attribute_name = ' || X_tax_attribute_name ||
438 ' tax_attribute_value = ' || X_tax_attribute_value||
439 ' org_id = ' || X_org_id);
440 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
441 APP_EXCEPTION.RAISE_EXCEPTION;
442 END IF;
443 END Check_Unique;
444
445 PROCEDURE Check_Default_To_Class
446 (X_rowid VARCHAR2,
447 X_tax_category_id NUMBER,
448 X_tax_attribute_type VARCHAR2,
449 X_tax_attribute_name VARCHAR2,
450 X_default_to_class VARCHAR2,
451 X_org_id NUMBER,
452 X_calling_sequence IN VARCHAR2) IS
453
454 l_dummy NUMBER;
455 current_calling_sequence VARCHAR2(2000);
456 debug_info VARCHAR2(100);
457
458 BEGIN
459 -- Update the calling sequence
460 --
461 current_calling_sequence := 'JL_ZZ_AR_TX_ATT_VAL_PKG.CHECK_DEFAULT_TO_CLASS<-' ||
462 X_calling_sequence;
463
464 SELECT COUNT(1)
465 INTO l_dummy
466 FROM jl_zz_ar_tx_att_val
467 WHERE tax_category_id = X_tax_category_id
468 AND tax_attribute_type = X_tax_attribute_type
469 AND tax_attribute_name = X_tax_attribute_name
470 AND default_to_class = X_default_to_class
471 AND ((X_rowid IS NULL) OR (rowid <> X_rowid))
472 AND org_id = X_org_id;
473
474 IF (l_dummy >=1) THEN
475 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
476 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
477 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
478 FND_MESSAGE.SET_TOKEN('PARAMETERS',
479 ' tax_category_id = ' || X_tax_category_id ||
483 ' org_id = ' || X_org_id);
480 ' tax_attribute_type = ' || X_tax_attribute_type ||
481 ' tax_attribute_name = ' || X_tax_attribute_name ||
482 ' defautl_to_class = ' || X_default_to_class||
484 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
485 APP_EXCEPTION.RAISE_EXCEPTION;
486 END IF;
487 END Check_Default_To_Class;
488
489 END JL_ZZ_AR_TX_ATT_VAL_PKG;