[Home] [Help]
PACKAGE BODY: APPS.JL_ZZ_AR_TX_EXC_CUS_PKG
Source
1 PACKAGE BODY JL_ZZ_AR_TX_EXC_CUS_PKG as
2 /* $Header: jlzztesb.pls 120.2 2003/03/03 19:28:27 opedrega ship $ */
3
4 PROCEDURE Insert_Row
5 (X_rowid IN OUT NOCOPY VARCHAR2,
6 X_exc_cus_id NUMBER,
7 X_address_id NUMBER,
8 X_tax_category_id NUMBER,
9 --X_min_taxable_basis NUMBER,
10 X_end_date_active DATE,
11 X_last_update_date DATE,
12 X_last_updated_by NUMBER,
13 X_tax_code VARCHAR2,
14 X_base_rate NUMBER,
15 X_start_date_active DATE,
16 X_org_id NUMBER,
17 X_last_update_login NUMBER,
18 X_creation_date DATE,
19 X_created_by NUMBER,
20 X_attribute_category VARCHAR2,
21 X_attribute1 VARCHAR2,
22 X_attribute2 VARCHAR2,
23 X_attribute3 VARCHAR2,
24 X_attribute4 VARCHAR2,
25 X_attribute5 VARCHAR2,
26 X_attribute6 VARCHAR2,
27 X_attribute7 VARCHAR2,
28 X_attribute8 VARCHAR2,
29 X_attribute9 VARCHAR2,
30 X_attribute10 VARCHAR2,
31 X_attribute11 VARCHAR2,
32 X_attribute12 VARCHAR2,
33 X_attribute13 VARCHAR2,
34 X_attribute14 VARCHAR2,
35 X_attribute15 VARCHAR2,
36 X_calling_sequence IN VARCHAR2) IS
37
38 CURSOR C IS
39 SELECT rowid
40 FROM jl_zz_ar_tx_exc_cus
41 WHERE exc_cus_id = X_exc_cus_id;
42
43 current_calling_sequence VARCHAR2(2000);
44 debug_info VARCHAR2(100);
45
46 BEGIN
47 -- Update the calling sequence
48 --
49 current_calling_sequence := 'JL_ZZ_AR_TX_EXC_CUS_PKG.INSERT_ROW<-' ||
50 X_calling_sequence;
51
52 debug_info := 'Insert into JL_ZZ_AR_TX_EXC_CUS';
53
54 INSERT INTO JL_ZZ_AR_TX_EXC_CUS(exc_cus_id,
55 address_id,
56 tax_category_id,
57 --min_taxable_basis,
58 end_date_active,
59 last_update_date,
60 last_updated_by,
61 tax_code,
62 base_rate,
63 start_date_active,
64 org_id,
65 last_update_login,
66 creation_date,
67 created_by,
68 attribute_category,
69 attribute1,
70 attribute2,
71 attribute3,
72 attribute4,
73 attribute5,
74 attribute6,
75 attribute7,
76 attribute8,
77 attribute9,
78 attribute10,
79 attribute11,
80 attribute12,
81 attribute13,
82 attribute14,
83 attribute15 )
84 VALUES (X_exc_cus_id,
85 X_address_id,
86 X_tax_category_id,
87 --X_min_taxable_basis,
88 X_end_date_active,
89 X_last_update_date,
90 X_last_updated_by,
91 X_tax_code,
92 X_base_rate,
93 X_start_date_active,
94 X_org_id,
95 X_last_update_login,
96 X_creation_date,
97 X_created_by,
98 X_attribute_category,
99 X_attribute1,
100 X_attribute2,
101 X_attribute3,
102 X_attribute4,
103 X_attribute5,
104 X_attribute6,
105 X_attribute7,
106 X_attribute8,
107 X_attribute9,
108 X_attribute10,
109 X_attribute11,
110 X_attribute12,
111 X_attribute13,
112 X_attribute14,
113 X_attribute15);
114
115 debug_info := 'Open cursor C';
116 OPEN C;
117 debug_info := 'Fetch cursor C';
118 FETCH C INTO X_rowid;
119 IF (C%NOTFOUND) THEN
120 debug_info := 'Close cursor C - DATA NOTFOUND';
121 CLOSE C;
122 Raise NO_DATA_FOUND;
123 END IF;
124 debug_info := 'Close cursor C';
125 CLOSE C;
126
127 EXCEPTION
128 WHEN OTHERS THEN
129 IF (SQLCODE <> -20001) THEN
130 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
131 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
132 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
133 FND_MESSAGE.SET_TOKEN('PARAMETERS',
134 'exc_cus_id = ' || X_exc_cus_id);
135 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
136 END IF;
137 APP_EXCEPTION.RAISE_EXCEPTION;
138
139 END Insert_Row;
140
141 PROCEDURE Lock_Row
142 (X_rowid VARCHAR2,
143 X_exc_cus_id NUMBER,
144 X_address_id NUMBER,
145 X_tax_category_id NUMBER,
146 --X_min_taxable_basis NUMBER,
147 X_end_date_active DATE,
148 X_last_update_date DATE,
149 X_last_updated_by NUMBER,
150 X_tax_code VARCHAR2,
151 X_base_rate NUMBER,
152 X_start_date_active DATE,
153 X_org_id NUMBER,
154 X_last_update_login NUMBER,
155 X_creation_date DATE,
156 X_created_by NUMBER,
157 X_attribute_category VARCHAR2,
158 X_attribute1 VARCHAR2,
159 X_attribute2 VARCHAR2,
160 X_attribute3 VARCHAR2,
161 X_attribute4 VARCHAR2,
162 X_attribute5 VARCHAR2,
163 X_attribute6 VARCHAR2,
164 X_attribute7 VARCHAR2,
165 X_attribute8 VARCHAR2,
166 X_attribute9 VARCHAR2,
167 X_attribute10 VARCHAR2,
168 X_attribute11 VARCHAR2,
169 X_attribute12 VARCHAR2,
170 X_attribute13 VARCHAR2,
171 X_attribute14 VARCHAR2,
172 X_attribute15 VARCHAR2,
173 X_calling_sequence IN VARCHAR2) IS
174
175 CURSOR C IS
176 SELECT EXC_CUS_ID,
177 ADDRESS_ID,
178 TAX_CATEGORY_ID,
179 END_DATE_ACTIVE,
180 LAST_UPDATE_DATE,
181 LAST_UPDATED_BY,
182 TAX_CODE,
183 BASE_RATE,
184 START_DATE_ACTIVE,
185 ORG_ID,
186 LAST_UPDATE_LOGIN,
187 CREATION_DATE,
188 CREATED_BY,
189 ATTRIBUTE_CATEGORY,
190 ATTRIBUTE1,
191 ATTRIBUTE2,
192 ATTRIBUTE3,
193 ATTRIBUTE4,
194 ATTRIBUTE5,
195 ATTRIBUTE6,
196 ATTRIBUTE7,
197 ATTRIBUTE8,
198 ATTRIBUTE9,
199 ATTRIBUTE10,
200 ATTRIBUTE11,
201 ATTRIBUTE12,
202 ATTRIBUTE13,
203 ATTRIBUTE14,
204 ATTRIBUTE15
205 FROM JL_ZZ_AR_TX_EXC_CUS
206 WHERE exc_cus_id = X_exc_cus_id
207 FOR UPDATE of exc_cus_id
208 NOWAIT;
209
210 Recinfo C%ROWTYPE;
211
212 current_calling_sequence VARCHAR2(2000);
213 debug_info VARCHAR2(100);
214
215 BEGIN
216 -- Update the calling sequence
217 --
218 current_calling_sequence := 'JL_ZZ_AR_TX_EXC_CUS_PKG.LOCK_ROW<-' ||
219 X_calling_sequence;
220 debug_info := 'Open cursor C';
221 OPEN C;
222 debug_info := 'Fetch cursor C';
223 FETCH C INTO Recinfo;
224
225 IF (C%NOTFOUND) THEN
226 debug_info := 'Close cursor C - DATA NOTFOUND';
227 CLOSE C;
228 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
229 APP_EXCEPTION.Raise_Exception;
230 END IF;
231
232 debug_info := 'Close cursor C';
233 CLOSE C;
234
235 IF ((Recinfo.exc_cus_id = X_exc_cus_id) AND
236 (Recinfo.address_id = X_address_id) AND
237 (Recinfo.tax_category_id = X_tax_category_id) AND
238 --(Recinfo.min_taxable_basis = X_min_taxable_basis) AND
239 (Recinfo.end_date_active = X_end_date_active) AND
240 (Recinfo.last_updated_by = X_last_updated_by) AND
241 (Recinfo.last_update_date = X_last_update_date) AND
242 ((Recinfo.tax_code = X_tax_code) OR
243 ((Recinfo.tax_code IS NULL) AND
244 (X_tax_code IS NULL))) AND
245 ((Recinfo.base_rate = X_base_rate) OR
246 ((Recinfo.base_rate IS NULL) AND
247 (X_base_rate IS NULL))) AND
248 ((Recinfo.start_date_active = X_start_date_active) OR
252 ((Recinfo.org_id IS NULL) AND
249 ((Recinfo.start_date_active IS NULL) AND
250 (X_start_date_active IS NULL))) AND
251 ((Recinfo.org_id = X_org_id) OR
253 (X_org_id IS NULL))) AND
254 ((Recinfo.creation_date = X_creation_date) OR
255 ((Recinfo.creation_date IS NULL) AND
256 (X_creation_date IS NULL))) AND
257 ((Recinfo.created_by = X_created_by)OR
258 ((Recinfo.created_by IS NULL) AND
259 (X_created_by IS NULL))) AND
260 ((Recinfo.last_update_login = X_last_update_login) OR
261 ((Recinfo.last_update_login IS NULL) AND
262 (X_last_update_login IS NULL))) AND
263 ((Recinfo.attribute_category = X_attribute_category) OR
264 ((Recinfo.attribute_category IS NULL) AND
265 (X_attribute_category IS NULL))) AND
266 ((Recinfo.attribute1 = X_attribute1) OR
267 ((Recinfo.attribute1 IS NULL) AND
268 (X_attribute1 IS NULL))) AND
269 ((Recinfo.attribute2 = X_attribute2) OR
270 ((Recinfo.attribute2 IS NULL) AND
271 (X_attribute2 IS NULL))) AND
272 ((Recinfo.attribute3 = X_attribute3) OR
273 ((Recinfo.attribute3 IS NULL) AND
274 (X_attribute3 IS NULL))) AND
275 ((Recinfo.attribute4 = X_attribute4) OR
276 ((Recinfo.attribute4 IS NULL) AND
277 (X_attribute4 IS NULL))) AND
278 ((Recinfo.attribute5 = X_attribute5) OR
279 ((Recinfo.attribute5 IS NULL) AND
280 (X_attribute5 IS NULL))) AND
281 ((Recinfo.attribute6 = X_attribute6) OR
282 ((Recinfo.attribute6 IS NULL) AND
283 (X_attribute6 IS NULL))) AND
284 ((Recinfo.attribute7 = X_attribute7) OR
285 ((Recinfo.attribute7 IS NULL) AND
286 (X_attribute7 IS NULL))) AND
287 ((Recinfo.attribute8 = X_attribute8) OR
288 ((Recinfo.attribute8 IS NULL) AND
289 (X_attribute8 IS NULL))) AND
290 ((Recinfo.attribute9 = X_attribute9) OR
291 ((Recinfo.attribute9 IS NULL) AND
292 (X_attribute9 IS NULL))) AND
293 ((Recinfo.attribute10 = X_attribute10) OR
294 ((Recinfo.attribute10 IS NULL) AND
295 (X_attribute10 IS NULL))) AND
296 ((Recinfo.attribute11 = X_attribute11) OR
297 ((Recinfo.attribute11 IS NULL) AND
298 (X_attribute11 IS NULL))) AND
299 ((Recinfo.attribute12 = X_attribute12) OR
300 ((Recinfo.attribute12 IS NULL) AND
301 (X_attribute12 IS NULL))) AND
302 ((Recinfo.attribute13 = X_attribute13) OR
303 ((Recinfo.attribute13 IS NULL) AND
304 (X_attribute13 IS NULL))) AND
305 ((Recinfo.attribute14 = X_attribute14) OR
306 ((Recinfo.attribute14 IS NULL) AND
307 (X_attribute14 IS NULL))) AND
308 ((Recinfo.attribute15 = X_attribute15) OR
309 ((Recinfo.attribute15 IS NULL) AND
310 (X_attribute15 IS NULL)))) THEN
311 return;
312 ELSE
313 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
314 APP_EXCEPTION.Raise_Exception;
315 END IF;
316
317 EXCEPTION
318 WHEN OTHERS THEN
319 IF (SQLCODE <> -20001) THEN
320 IF (SQLCODE = -54) THEN
321 FND_MESSAGE.SET_NAME('AR','AR_RESOURCE_BUSY');
322 ELSE
323 FND_MESSAGE.SET_NAME('AR','AR_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 'exc_cus_id = ' || X_exc_cus_id);
328 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
329 END IF;
330 END IF;
331 APP_EXCEPTION.RAISE_EXCEPTION;
332 END Lock_Row;
333
334 PROCEDURE Update_Row
335 (X_rowid VARCHAR2,
336 X_exc_cus_id NUMBER,
337 X_address_id NUMBER,
338 X_tax_category_id NUMBER,
339 --X_min_taxable_basis NUMBER,
340 X_end_date_active DATE,
341 X_last_update_date DATE,
342 X_last_updated_by NUMBER,
343 X_tax_code VARCHAR2,
344 X_base_rate NUMBER,
345 X_start_date_active DATE,
346 X_last_update_login NUMBER,
347 X_creation_date DATE,
348 X_created_by NUMBER,
349 X_attribute_category VARCHAR2,
350 X_attribute1 VARCHAR2,
351 X_attribute2 VARCHAR2,
352 X_attribute3 VARCHAR2,
353 X_attribute4 VARCHAR2,
354 X_attribute5 VARCHAR2,
355 X_attribute6 VARCHAR2,
356 X_attribute7 VARCHAR2,
357 X_attribute8 VARCHAR2,
358 X_attribute9 VARCHAR2,
359 X_attribute10 VARCHAR2,
360 X_attribute11 VARCHAR2,
361 X_attribute12 VARCHAR2,
365 X_calling_sequence IN VARCHAR2) IS
362 X_attribute13 VARCHAR2,
363 X_attribute14 VARCHAR2,
364 X_attribute15 VARCHAR2,
366
367 BEGIN
368 UPDATE jl_zz_ar_tx_exc_cus
369 SET exc_cus_id = X_exc_cus_id,
370 address_id = X_address_id,
371 tax_category_id = X_tax_category_id,
372 --min_taxable_basis = X_min_taxable_basis,
373 end_date_active = X_end_date_active,
374 last_update_date = X_last_update_date,
375 last_updated_by = X_last_updated_by,
376 tax_code = X_tax_code,
377 base_rate = X_base_rate,
378 start_date_active = X_start_date_active,
379 last_update_login = X_last_update_login,
380 creation_date = X_creation_date,
381 created_by = X_created_by,
382 attribute_category = X_attribute_category,
383 attribute1 = X_attribute1,
384 attribute2 = X_attribute2,
385 attribute3 = X_attribute3,
386 attribute4 = X_attribute4,
387 attribute5 = X_attribute5,
388 attribute6 = X_attribute6,
389 attribute7 = X_attribute7,
390 attribute8 = X_attribute8,
391 attribute9 = X_attribute9,
392 attribute10 = X_attribute10,
393 attribute11 = X_attribute11,
394 attribute12 = X_attribute12,
395 attribute13 = X_attribute13,
396 attribute14 = X_attribute14,
397 attribute15 = X_attribute15
398 WHERE rowid = X_rowid;
399
400 IF (SQL%NOTFOUND) THEN
401 raise NO_DATA_FOUND;
402 END IF;
403 END Update_Row;
404
405 PROCEDURE Delete_Row
406 (X_rowid VARCHAR2) IS
407
408 BEGIN
409 DELETE
410 FROM JL_ZZ_AR_TX_EXC_CUS
411 WHERE rowid = X_rowid;
412
413 IF (SQL%NOTFOUND) THEN
414 raise NO_DATA_FOUND;
415 END IF;
416 END Delete_Row;
417
418 PROCEDURE Check_Unique
419 (X_rowid VARCHAR2,
420 X_address_id NUMBER,
421 X_tax_category_id NUMBER,
422 X_end_date_active DATE,
423 X_org_id NUMBER,
424 X_calling_sequence IN VARCHAR2) IS
425
426 l_dummy NUMBER;
427 current_calling_sequence VARCHAR2(2000);
428 debug_info VARCHAR2(100);
429
430 BEGIN
431 -- Update the calling sequence
432 --
433 current_calling_sequence :='JL_ZZ_AR_TX_EXC_CUS_PKG.CHECK_UNIQUE<-' ||
434 X_calling_sequence;
435 SELECT COUNT(1)
436 INTO l_dummy
437 FROM jl_zz_ar_tx_exc_cus
438 WHERE address_id = X_address_id
439 AND tax_category_id = X_tax_category_id
440 AND end_date_active = X_end_date_active
441 AND org_id = X_org_id
442 AND ((X_rowid IS NULL) OR (rowid <> X_rowid));
443
444 IF (l_dummy >=1) THEN
445 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
446 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
447 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
448 FND_MESSAGE.SET_TOKEN('PARAMETERS',
449 ' address_id = ' || X_address_id ||
450 ' tax_category_id = ' || X_tax_category_id ||
451 ' end_date_active = ' || X_end_date_active);
452 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
453 APP_EXCEPTION.RAISE_EXCEPTION;
454 END IF;
455 END Check_Unique;
456
457 PROCEDURE Check_Overlapped_Dates
458 (X_rowid VARCHAR2,
459 X_address_id NUMBER,
460 X_tax_category_id NUMBER,
461 X_end_date_active DATE,
462 X_start_date_active DATE,
463 X_org_id NUMBER,
464 X_calling_sequence IN VARCHAR2) IS
465
466 l_dummy NUMBER;
467 current_calling_sequence VARCHAR2(2000);
468 debug_info VARCHAR2(100);
469
470 BEGIN
471 -- Update the calling sequence
472 --
473 current_calling_sequence := 'JL_ZZ_AR_TX_EXC_CUS_PKG.<-CHECK_OVERLAPPED_DATES' ||
474 X_calling_sequence;
475
476 SELECT COUNT(1)
477 INTO l_dummy
478 FROM jl_zz_ar_tx_exc_cus a
479 WHERE a.address_id = X_address_id
480 AND a.tax_category_id = X_tax_category_id
481 AND ((a.end_date_active <= X_end_date_active AND
482 a.end_date_active >= X_start_date_active) OR
483 (a.start_date_active <= X_end_date_active AND
484 a.start_date_active >= X_start_date_active) OR
485 (a.start_date_active <= X_start_date_active AND
486 a.end_date_active >= X_end_date_active))
487 AND org_id = X_org_id
488 AND ((X_rowid IS NULL) OR (a.rowid <> X_rowid));
489
490 IF (l_dummy >=1) THEN
491 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
492 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
496 ' tax_category_id = ' || X_tax_category_id ||
493 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
494 FND_MESSAGE.SET_TOKEN('PARAMETERS',
495 ' address_id = ' || X_address_id ||
497 ' end_date_active = ' || X_end_date_active );
498 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
499 APP_EXCEPTION.RAISE_EXCEPTION;
500 END IF;
501 END Check_Overlapped_Dates;
502
503 PROCEDURE Check_Gaps
504 (X_rowid VARCHAR2,
505 X_address_id NUMBER,
506 X_tax_category_id NUMBER,
507 X_end_date_active DATE,
508 X_start_date_active DATE,
509 X_org_id NUMBER,
510 X_calling_sequence IN VARCHAR2) IS
511
512 l_dummy NUMBER;
513 l_dummy1 NUMBER;
514 l_dummy2 NUMBER;
515 current_calling_sequence VARCHAR2(2000);
516 debug_info VARCHAR2(100);
517
518 BEGIN
519 -- Update the calling sequence
520 --
521 current_calling_sequence := 'JL_ZZ_AR_TX_EXC_CUS_PKG.CHECK_GAPS<-' ||
522 X_calling_sequence;
523
524 -- Check if there is one row with it's end date exactly one day
525 -- less than the current row's start date
526 --
527 SELECT COUNT(1)
528 INTO l_dummy
529 FROM jl_zz_ar_tx_exc_cus a
530 WHERE a.address_id = X_address_id
531 AND a.tax_category_id = X_tax_category_id
532 AND trunc(a.end_date_active) = (trunc(X_start_date_active) -1)
533 AND org_id = X_org_id
534 AND ((X_rowid IS NULL) OR (a.rowid <> X_rowid));
535
536 IF (l_dummy = 0) THEN
537 BEGIN
538
539 -- Check if there is one row with the start date one day more than the
540 -- the current row's end-date
541
542 SELECT COUNT(1)
543 INTO l_dummy1
544 FROM jl_zz_ar_tx_exc_cus a
545 WHERE a.address_id = X_address_id
546 AND a.tax_category_id = X_tax_category_id
547 AND (trunc(a.start_date_active) = (trunc(X_end_date_active) + 1))
548 AND org_id = X_org_id
549 AND ((X_rowid IS NULL) OR (a.rowid <> X_rowid));
550
551 -- Check if there are no (other) rows at all for the primary key.
552 -- If there are no rows, then it is not an error. Otherwise, it is.
553
554 IF (l_dummy1 = 0) THEN
555 BEGIN
556 SELECT COUNT(1)
557 INTO l_dummy2
558 FROM jl_zz_ar_tx_exc_cus a
559 WHERE a.address_id = X_address_id
560 AND a.tax_category_id = X_tax_category_id
561 AND org_id = X_org_id
562 AND ((X_rowid IS NULL) OR (a.rowid <> X_rowid));
563
564 IF (l_dummy2 <> 0) THEN
565 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
566 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
567 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
568 FND_MESSAGE.SET_TOKEN('PARAMETERS',
569 ' address_id = ' || X_address_id ||
570 ' tax_category_id = ' || X_tax_category_id ||
571 ' end_date_active = ' || X_end_date_active );
572 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
573 APP_EXCEPTION.RAISE_EXCEPTION;
574 END IF;
575 END;
576 END IF;
577 END;
578 END IF;
579 END Check_Gaps;
580
581 END JL_ZZ_AR_TX_EXC_CUS_PKG;