[Home] [Help]
PACKAGE BODY: APPS.CE_TRANSACTION_CODES_PKG
Source
1 PACKAGE BODY CE_TRANSACTION_CODES_PKG as
2 /* $Header: cetrxcdb.pls 120.4 2005/06/20 19:17:47 lkwan ship $ */
3 --
4 -- Package
5 -- cb_transaction_codes_pkg
6 -- Purpose
7 -- To contain validation and insertion routines for cb_transaction_codes
8 -- History
9 -- 00-00-94 Dean McCarthy Created
10
11 FUNCTION body_revision RETURN VARCHAR2 IS
12 BEGIN
13
14 RETURN '$Revision: 120.4 $';
15
16 END body_revision;
17
18 FUNCTION spec_revision RETURN VARCHAR2 IS
19 BEGIN
20
21 RETURN G_spec_revision;
22
23 END spec_revision;
24
25 --
26 -- Procedure
27 -- Insert_Row
28 -- Purpose
29 -- Inserts a row into cb_transaction_codes
30 -- History
31 -- 00-00-94 Dean McCarthy Created
32 -- Arguments
33 -- all the columns of the table CE_TRANSACTION_CODES
34 -- Example
35 -- CE_TRANSACTION_CODES.Insert_Row(....;
36 -- Notes
37 --
38
39 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
40 X_Transaction_Code_Id IN OUT NOCOPY NUMBER,
41 X_Bank_Account_Id NUMBER,
42 X_Trx_Code VARCHAR2,
43 X_Trx_Type VARCHAR2,
44 X_Description VARCHAR2,
45 X_Receivables_Trx_Id NUMBER,
46 X_Receipt_Method_Id NUMBER,
47 X_Create_Misc_Trx_Flag VARCHAR2,
48 X_Reconcile_Flag VARCHAR2,
49 X_Float_Days NUMBER,
50 X_Matching_Against VARCHAR2,
51 X_Correction_Method VARCHAR2,
52 X_Start_Date DATE,
53 X_End_Date DATE,
54 X_Attribute_Category VARCHAR2,
55 X_Attribute1 VARCHAR2,
56 X_Attribute2 VARCHAR2,
57 X_Attribute3 VARCHAR2,
58 X_Attribute4 VARCHAR2,
59 X_Attribute5 VARCHAR2,
60 X_Attribute6 VARCHAR2,
61 X_Attribute7 VARCHAR2,
62 X_Attribute8 VARCHAR2,
63 X_Attribute9 VARCHAR2,
64 X_Attribute10 VARCHAR2,
65 X_Attribute11 VARCHAR2,
66 X_Attribute12 VARCHAR2,
67 X_Attribute13 VARCHAR2,
68 X_Attribute14 VARCHAR2,
69 X_Attribute15 VARCHAR2,
70 X_Last_Updated_By NUMBER,
71 X_Last_Update_Date DATE,
72 X_Last_Update_Login NUMBER,
73 X_Created_By NUMBER,
74 X_Creation_Date DATE,
75 X_payroll_payment_format_Id NUMBER DEFAULT NULL,
76 X_reconciliation_sequence NUMBER
77 ) IS
78 CURSOR C IS SELECT rowid FROM CE_TRANSACTION_CODES
79 WHERE transaction_code_id = X_Transaction_Code_Id;
80 CURSOR C2 IS SELECT ce_transaction_codes_s.nextval FROM sys.dual;
81 --
82 BEGIN
83 cep_standard.debug('open c2 ');
84
85 OPEN C2;
86 FETCH C2 INTO X_transaction_code_id;
87 CLOSE C2;
88 --
89
90 cep_standard.debug('>>insert into CE_TRANSACTION_CODES ');
91
92
93 INSERT INTO CE_TRANSACTION_CODES(
94 transaction_code_id,
95 bank_account_id,
96 trx_code,
97 trx_type,
98 description,
99 receivables_trx_id,
100 receipt_method_id,
101 create_misc_trx_flag,
102 reconcile_flag,
103 float_days,
104 matching_against,
105 correction_method,
106 start_date,
107 end_date,
108 attribute_category,
109 attribute1,
110 attribute2,
111 attribute3,
112 attribute4,
113 attribute5,
114 attribute6,
115 attribute7,
116 attribute8,
117 attribute9,
118 attribute10,
119 attribute11,
120 attribute12,
121 attribute13,
122 attribute14,
123 attribute15,
124 last_updated_by,
125 last_update_date,
126 last_update_login,
127 created_by,
128 creation_date,
129 payroll_payment_format_id,
130 reconciliation_sequence
131 ) VALUES (
132 X_Transaction_Code_Id,
133 X_Bank_Account_Id,
134 X_Trx_Code,
135 X_Trx_Type,
136 X_Description,
137 X_Receivables_Trx_Id,
138 X_Receipt_Method_Id,
139 X_Create_Misc_Trx_Flag,
140 X_Reconcile_Flag,
141 X_Float_Days,
142 X_Matching_Against,
143 X_Correction_Method,
144 X_Start_Date,
145 X_End_Date,
146 X_Attribute_Category,
147 X_Attribute1,
148 X_Attribute2,
149 X_Attribute3,
150 X_Attribute4,
151 X_Attribute5,
152 X_Attribute6,
153 X_Attribute7,
154 X_Attribute8,
155 X_Attribute9,
156 X_Attribute10,
157 X_Attribute11,
158 X_Attribute12,
159 X_Attribute13,
160 X_Attribute14,
161 X_Attribute15,
162 X_Last_Updated_By,
163 X_Last_Update_Date,
164 X_Last_Update_Login,
165 X_Created_By,
166 X_Creation_Date,
167 X_payroll_payment_format_id,
168 X_reconciliation_sequence
169 );
170 cep_standard.debug('>>insert into CE_TRANSACTION_CODES end');
171 --
172 OPEN C;
173 FETCH C INTO X_Rowid;
174 if (C%NOTFOUND) then
175 CLOSE C;
176 Raise NO_DATA_FOUND;
177 end if;
178 CLOSE C;
179 END Insert_Row;
180 --
181 -- Procedure
182 -- Lock_Row
183 -- Purpose
184 -- Locks a row into gl_daily_conversion_rates
185 -- History
186 -- 00-00-94 Dean McCarthy Created
187 -- Arguments
188 -- all the columns of the table CE_TRANSACTION_CODES
189 -- Example
190 -- cb_transaction_codes.Lock_Row(....;
191 -- Notes
192 --
193 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
194 X_Transaction_Code_Id NUMBER,
195 X_Bank_Account_Id NUMBER,
196 X_Trx_Code VARCHAR2,
197 X_Trx_Type VARCHAR2,
198 X_Description VARCHAR2,
199 X_Receivables_Trx_Id NUMBER,
200 X_Receipt_Method_Id NUMBER,
201 X_Create_Misc_Trx_Flag VARCHAR2,
202 X_Reconcile_Flag VARCHAR2,
203 X_Float_Days NUMBER,
204 X_Matching_Against VARCHAR2,
205 X_Correction_Method VARCHAR2,
206 X_Start_Date DATE,
207 X_End_Date DATE,
208 X_Attribute_Category VARCHAR2,
209 X_Attribute1 VARCHAR2,
210 X_Attribute2 VARCHAR2,
211 X_Attribute3 VARCHAR2,
212 X_Attribute4 VARCHAR2,
213 X_Attribute5 VARCHAR2,
214 X_Attribute6 VARCHAR2,
215 X_Attribute7 VARCHAR2,
216 X_Attribute8 VARCHAR2,
217 X_Attribute9 VARCHAR2,
218 X_Attribute10 VARCHAR2,
219 X_Attribute11 VARCHAR2,
220 X_Attribute12 VARCHAR2,
221 X_Attribute13 VARCHAR2,
222 X_Attribute14 VARCHAR2,
223 X_Attribute15 VARCHAR2,
224 X_payroll_payment_format_id NUMBER DEFAULT NULL,
225 X_reconciliation_sequence NUMBER
226 ) IS
227 CURSOR C IS
228 SELECT *
229 FROM CE_TRANSACTION_CODES
230 WHERE rowid = X_Rowid
231 FOR UPDATE of Transaction_Code_Id NOWAIT;
232 Recinfo C%ROWTYPE;
233 BEGIN
234 OPEN C;
235 FETCH C INTO Recinfo;
236 if (C%NOTFOUND) then
237 CLOSE C;
238 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
239 APP_EXCEPTION.Raise_Exception;
240 end if;
241 CLOSE C;
242 if (
243
244 (Recinfo.transaction_code_id = X_Transaction_Code_Id)
245 AND (Recinfo.bank_account_id = X_Bank_Account_Id)
246 AND (Recinfo.trx_code = X_Trx_Code)
247 AND (Recinfo.trx_type = X_Trx_Type)
248 AND ( (Recinfo.description = X_Description)
249 OR ( (Recinfo.description IS NULL)
250 AND (X_Description IS NULL)))
251 AND ( (Recinfo.receivables_trx_id = X_Receivables_Trx_Id)
252 OR ( (Recinfo.receivables_trx_id IS NULL)
253 AND (X_Receivables_Trx_Id IS NULL)))
254 AND ( (Recinfo.receipt_method_id = X_Receipt_Method_Id)
255 OR ( (Recinfo.receipt_method_id IS NULL)
256 AND (X_Receipt_Method_Id IS NULL)))
257 AND ( (Recinfo.create_misc_trx_flag = X_Create_Misc_Trx_Flag)
258 OR ( (Recinfo.create_misc_trx_flag IS NULL)
259 AND (X_Create_Misc_Trx_Flag IS NULL)))
260 AND ( (Recinfo.reconcile_flag = X_reconcile_flag)
261 OR ( (Recinfo.reconcile_flag IS NULL)
262 AND (X_reconcile_flag IS NULL)))
263 AND ( (Recinfo.float_days = X_float_days)
264 OR ( (Recinfo.float_days IS NULL)
265 AND (X_float_days IS NULL)))
266 AND ( (Recinfo.matching_against = X_matching_against)
267 OR ( (Recinfo.matching_against IS NULL)
268 AND (X_matching_against IS NULL)))
269 AND ( (Recinfo.correction_method = X_correction_method)
270 OR ( (Recinfo.correction_method IS NULL)
271 AND (X_correction_method IS NULL)))
272 AND ( (Recinfo.start_date = X_Start_Date)
273 OR ( (Recinfo.start_date IS NULL)
274 AND (X_Start_Date IS NULL)))
275 AND ( (Recinfo.end_date = X_End_Date)
276 OR ( (Recinfo.end_date IS NULL)
277 AND (X_End_Date IS NULL)))
278 AND ( (Recinfo.attribute_category = X_Attribute_Category)
279 OR ( (Recinfo.attribute_category IS NULL)
280 AND (X_Attribute_Category IS NULL)))
281 AND ( (Recinfo.attribute1 = X_Attribute1)
282 OR ( (Recinfo.attribute1 IS NULL)
283 AND (X_Attribute1 IS NULL)))
284 AND ( (Recinfo.attribute2 = X_Attribute2)
285 OR ( (Recinfo.attribute2 IS NULL)
286 AND (X_Attribute2 IS NULL)))
287 AND ( (Recinfo.attribute3 = X_Attribute3)
288 OR ( (Recinfo.attribute3 IS NULL)
289 AND (X_Attribute3 IS NULL)))
290 AND ( (Recinfo.attribute4 = X_Attribute4)
291 OR ( (Recinfo.attribute4 IS NULL)
292 AND (X_Attribute4 IS NULL)))
293 AND ( (Recinfo.attribute5 = X_Attribute5)
294 OR ( (Recinfo.attribute5 IS NULL)
295 AND (X_Attribute5 IS NULL)))
296 AND ( (Recinfo.attribute6 = X_Attribute6)
297 OR ( (Recinfo.attribute6 IS NULL)
298 AND (X_Attribute6 IS NULL)))
299 AND ( (Recinfo.attribute7 = X_Attribute7)
300 OR ( (Recinfo.attribute7 IS NULL)
301 AND (X_Attribute7 IS NULL)))
302 AND ( (Recinfo.attribute8 = X_Attribute8)
303 OR ( (Recinfo.attribute8 IS NULL)
304 AND (X_Attribute8 IS NULL)))
305 AND ( (Recinfo.attribute9 = X_Attribute9)
306 OR ( (Recinfo.attribute9 IS NULL)
307 AND (X_Attribute9 IS NULL)))
308 AND ( (Recinfo.attribute10 = X_Attribute10)
309 OR ( (Recinfo.attribute10 IS NULL)
310 AND (X_Attribute10 IS NULL)))
311 AND ( (Recinfo.attribute11 = X_Attribute11)
312 OR ( (Recinfo.attribute11 IS NULL)
313 AND (X_Attribute11 IS NULL)))
314 AND ( (Recinfo.attribute12 = X_Attribute12)
315 OR ( (Recinfo.attribute12 IS NULL)
316 AND (X_Attribute12 IS NULL)))
317 AND ( (Recinfo.attribute13 = X_Attribute13)
318 OR ( (Recinfo.attribute13 IS NULL)
319 AND (X_Attribute13 IS NULL)))
320 AND ( (Recinfo.attribute14 = X_Attribute14)
321 OR ( (Recinfo.attribute14 IS NULL)
322 AND (X_Attribute14 IS NULL)))
323 AND ( (Recinfo.attribute15 = X_Attribute15)
324 OR ( (Recinfo.attribute15 IS NULL)
325 AND (X_Attribute15 IS NULL)))
326 AND ( (Recinfo.payroll_payment_format_id = X_payroll_payment_format_id)
327 OR ( (Recinfo.payroll_payment_format_id IS NULL)
328 AND (X_payroll_payment_format_id IS NULL)))
329 AND ( (Recinfo.reconciliation_sequence = X_reconciliation_sequence)
330 OR ( (Recinfo.reconciliation_sequence IS NULL)
331 AND (X_reconciliation_sequence IS NULL)))
332 ) then
333 return;
334 else
335 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
336 APP_EXCEPTION.Raise_Exception;
337 end if;
338 END Lock_Row;
339 --
340 -- Procedure
341 -- Update_Row
342 -- Purpose
343 -- Updates a row into cb_transaction_codes
344 -- History
345 -- 00-00-94 Dean McCarthy Created
346 -- Arguments
347 -- all the columns of the table CE_TRANSACTION_CODES
348 -- Example
349 -- cb_transaction_codes.Update_Row(....;
350 -- Notes
351 --
352 PROCEDURE Update_Row(X_Rowid VARCHAR2,
353 X_Transaction_Code_Id NUMBER,
354 X_Bank_Account_Id NUMBER,
355 X_Trx_Code VARCHAR2,
356 X_Trx_Type VARCHAR2,
357 X_Description VARCHAR2,
358 X_Receivables_Trx_Id NUMBER,
359 X_Receipt_Method_Id NUMBER,
360 X_Create_Misc_Trx_Flag VARCHAR2,
361 X_Reconcile_Flag VARCHAR2,
362 X_Float_Days NUMBER,
363 X_Matching_Against VARCHAR2,
364 X_Correction_Method VARCHAR2,
365 X_Start_Date DATE,
366 X_End_Date DATE,
367 X_Attribute_Category VARCHAR2,
368 X_Attribute1 VARCHAR2,
369 X_Attribute2 VARCHAR2,
373 X_Attribute6 VARCHAR2,
370 X_Attribute3 VARCHAR2,
371 X_Attribute4 VARCHAR2,
372 X_Attribute5 VARCHAR2,
374 X_Attribute7 VARCHAR2,
375 X_Attribute8 VARCHAR2,
376 X_Attribute9 VARCHAR2,
377 X_Attribute10 VARCHAR2,
378 X_Attribute11 VARCHAR2,
379 X_Attribute12 VARCHAR2,
380 X_Attribute13 VARCHAR2,
381 X_Attribute14 VARCHAR2,
382 X_Attribute15 VARCHAR2,
383 X_Last_Updated_By NUMBER,
384 X_Last_Update_Date DATE,
385 X_Last_Update_Login NUMBER,
386 X_payroll_payment_format_id NUMBER DEFAULT NULL,
387 X_reconciliation_sequence NUMBER
388 ) IS
389 BEGIN
390 UPDATE CE_TRANSACTION_CODES
391 SET
392 transaction_code_id = X_Transaction_Code_Id,
393 bank_account_id = X_Bank_Account_Id,
394 trx_code = X_Trx_Code,
395 trx_type = X_Trx_Type,
396 description = X_Description,
397 receivables_trx_id = X_Receivables_Trx_Id,
398 receipt_method_id = X_Receipt_Method_Id,
399 create_misc_trx_flag = X_Create_Misc_Trx_Flag,
400 reconcile_flag = X_Reconcile_Flag,
401 float_days = X_Float_Days,
402 matching_against = X_Matching_Against,
403 correction_method = X_Correction_Method,
404 start_date = X_Start_Date,
405 end_date = X_End_Date,
406 attribute_category = X_Attribute_Category,
407 attribute1 = X_Attribute1,
408 attribute2 = X_Attribute2,
409 attribute3 = X_Attribute3,
410 attribute4 = X_Attribute4,
411 attribute5 = X_Attribute5,
412 attribute6 = X_Attribute6,
413 attribute7 = X_Attribute7,
414 attribute8 = X_Attribute8,
415 attribute9 = X_Attribute9,
416 attribute10 = X_Attribute10,
417 attribute11 = X_Attribute11,
418 attribute12 = X_Attribute12,
419 attribute13 = X_Attribute13,
420 attribute14 = X_Attribute14,
421 attribute15 = X_Attribute15,
422 last_updated_by = X_Last_Updated_By,
423 last_update_date = X_Last_Update_Date,
424 last_update_login = X_Last_Update_Login,
425 payroll_payment_format_id = X_payroll_payment_format_id,
426 reconciliation_sequence = X_reconciliation_sequence
427 WHERE rowid = X_Rowid;
428 --
429 if (SQL%NOTFOUND) then
430 Raise NO_DATA_FOUND;
431 end if;
432 END Update_Row;
433 --
434 -- Procedure
435 -- Delete_Row
436 -- Purpose
437 -- Deletes a row from cb_transaction_codes
438 -- History
439 -- 00-00-94 Dean McCarthy Created
440 -- Arguments
441 -- x_rowid Rowid of a row
442 -- Example
443 -- cb_transaction_codes.delete_row('ajfdshj');
444 -- Notes
445 --
446 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
447 BEGIN
448 DELETE FROM CE_TRANSACTION_CODES
449 WHERE rowid = X_Rowid;
450 --
451 if (SQL%NOTFOUND) then
452 Raise NO_DATA_FOUND;
453 end if;
454 END Delete_Row;
455
456 --
457 -- Procedure
458 -- check_unique_txn_code
459 -- Purpose
460 -- Checks for uniquness of Transaction codes before
461 -- insertion and updates for a given bank code
462 -- History
463 -- 11-Jun-95 Ganesh Vaidee Created
464 -- 16-Jun-05 lkwan bug 4435028 - new unique validate rules
465
466 -- Users should be able to enter the following
467 -- Type Code Transaction Source Priority
468 -- Payment 100 AP Payments 1
469 -- Payment 100 Payroll 2
470 -- Payment 100 Cash Management 3
471 --
472 -- Arguments
473 -- x_row_id Rowid of a row
474 -- X_trx_code Transaction code of row to be inserted or updated
475 -- X_bank_account_id Bank Account Id
476 -- X_trx_type DEBIT, CREDIT, MISC_DEBIT, MISC_CREDIT, NSF, REJECTED, STOP
477 -- X_RECONCILE_FLAG null, AR, AP, JE, CE, OI
478 -- X_RECONCILIATION_SEQUENCE null, 1,2,3 ...
479 -- Example
480 -- cb_transaction_codes.check_unique_txn_code('ajfdshj', 11, '123.657.99', DEBIT, AP, 1 );
481 -- Notes
482 --
483 PROCEDURE check_unique_txn_code( X_trx_code IN VARCHAR2,
484 X_bank_account_id IN NUMBER,
488 X_RECONCILIATION_SEQUENCE IN NUMBER
485 X_Row_id IN VARCHAR2,
486 X_trx_type IN VARCHAR2,
487 X_RECONCILE_FLAG IN VARCHAR2,
489 ) IS
490 -- each trx_code cannot be used by more than one trx_type
491 -- check Type, Code
492 CURSOR chk_duplicates is
493 SELECT 'Duplicate'
494 FROM ce_transaction_codes tc
495 WHERE tc.trx_code = X_trx_code
496 AND tc. bank_account_id = X_bank_account_id
497 AND ( X_Row_id is null
498 OR tc.rowid <> chartorowid( X_Row_id ) )
499 AND exists (select 'x'
500 from ce_transaction_codes tc2
501 where tc2.trx_code = tc.trx_code
502 AND tc2.bank_account_id = tc.bank_account_id
503 AND tc2.TRX_TYPE <> X_trx_type
504 ) ;
505
506 -- check Type, Code, Priority combiniation
507 CURSOR chk_duplicates2 is
508 SELECT 'Duplicate'
509 FROM ce_transaction_codes tc
510 WHERE tc.trx_code = X_trx_code
511 AND tc.bank_account_id = X_bank_account_id
512 --AND tc.TRX_TYPE = X_trx_type
513 AND nvl(tc.RECONCILIATION_SEQUENCE,0) = nvl(X_RECONCILIATION_SEQUENCE,0)
514 AND ( X_Row_id is null
515 OR tc.rowid <> chartorowid( X_Row_id ) );
516 /* AND exists (select 'x'
517 from ce_transaction_codes tc2
518 where tc2.trx_code = tc.trx_code
519 AND tc2.bank_account_id = tc.bank_account_id
520 AND tc2.TRX_TYPE <> X_trx_type
521 ) ;*/
522
523 -- check Type, Code, Transaction Source combiniation
524 CURSOR chk_duplicates3 is
525 SELECT 'Duplicate'
526 FROM ce_transaction_codes tc
527 WHERE tc.trx_code = X_trx_code
528 AND tc. bank_account_id = X_bank_account_id
529 --AND tc.TRX_TYPE = X_trx_type
530 AND nvl(tc.RECONCILE_FLAG, 'X') = nvl(X_RECONCILE_FLAG, 'X')
531 AND ( X_Row_id is null
532 OR tc.rowid <> chartorowid( X_Row_id ) );
533
534
535 dummy VARCHAR2(100);
536 --
537 BEGIN
538 OPEN chk_duplicates;
539 FETCH chk_duplicates INTO dummy;
540 --
541 IF chk_duplicates%FOUND THEN
542 CLOSE chk_duplicates;
543 -- FND_MESSAGE.set_string( 'Duplicate Transaction code for this bank code' );
544 fnd_message.set_name( 'CE', 'CE_DUP_BANK_TRX_CODE' );
545 app_exception.raise_exception;
546 END IF;
547 --
548 CLOSE chk_duplicates;
549 --
550 OPEN chk_duplicates2;
551 FETCH chk_duplicates2 INTO dummy;
552 --
553 IF chk_duplicates2%FOUND THEN
554 CLOSE chk_duplicates2;
555 -- FND_MESSAGE.set_string( 'Duplicate Transaction code for this bank code' );
556 fnd_message.set_name( 'CE', 'CE_DUP_BANK_TRX_CODE_COMB2' );
557 app_exception.raise_exception;
558 END IF;
559 --
560 CLOSE chk_duplicates2;
561 --
562 --
563 OPEN chk_duplicates3;
564 FETCH chk_duplicates3 INTO dummy;
565 --
566 IF chk_duplicates3%FOUND THEN
567 CLOSE chk_duplicates3;
568 -- FND_MESSAGE.set_string( 'Duplicate Transaction code for this bank code' );
569 fnd_message.set_name( 'CE', 'CE_DUP_BANK_TRX_CODE_COMB1' );
570 app_exception.raise_exception;
571 END IF;
572 --
573 CLOSE chk_duplicates3;
574 EXCEPTION
575 WHEN app_exceptions.application_exception THEN
576 IF ( chk_duplicates%ISOPEN ) THEN
577 CLOSE chk_duplicates;
578 END IF;
579 IF ( chk_duplicates2%ISOPEN ) THEN
580 CLOSE chk_duplicates2;
581 END IF;
582 IF ( chk_duplicates3%ISOPEN ) THEN
583 CLOSE chk_duplicates3;
584 END IF;
585 RAISE;
586 WHEN OTHERS THEN
587 fnd_message.set_name( 'SQLCE', 'CE_UNHANDLED_EXCEPTION');
588 fnd_message.set_token( 'PROCEDURE',
589 'CE_TRANSACTION_CODES_pkg.check_unique_txn_code');
590 IF ( chk_duplicates%ISOPEN ) THEN
591 CLOSE chk_duplicates;
592 END IF;
593 IF ( chk_duplicates2%ISOPEN ) THEN
594 CLOSE chk_duplicates2;
595 END IF;
596 IF ( chk_duplicates3%ISOPEN ) THEN
597 CLOSE chk_duplicates3;
598 END IF;
599 RAISE;
600 END check_unique_txn_code;
601
602
603
604 FUNCTION is_in_use( X_ce_transaction_code_id NUMBER) return boolean IS
605
606 CURSOR chk_stmt_line is
607 SELECT 'Exists'
608 FROM ce_statement_lines sl
609 WHERE sl.trx_code_id = X_ce_transaction_code_id;
610
611 CURSOR chk_arch_stmt_line is
612 SELECT 'Exists'
613 FROM ce_arch_lines al
614 WHERE al.trx_code_id = X_ce_transaction_code_id;
615
616 dummy VARCHAR2(240);
617 --
618 BEGIN
619
620 /*
621 * Check for transaction code in the statement lines.
622 */
623 OPEN chk_stmt_line;
624
625 FETCH chk_stmt_line INTO dummy;
626
627 IF chk_stmt_line%FOUND THEN
628 CLOSE chk_stmt_line;
629 return TRUE;
630 END IF;
631
632 CLOSE chk_stmt_line;
633
634
635 /*
636 * Check for transaction code in the archived statement lines.
637 */
638 OPEN chk_arch_stmt_line;
639
640 FETCH chk_arch_stmt_line INTO dummy;
641
642 IF chk_arch_stmt_line%FOUND THEN
643 CLOSE chk_arch_stmt_line;
644 return TRUE;
645 END IF;
646
647 CLOSE chk_arch_stmt_line;
648
649
650 /*
651 * No reference. Return FALSE!!
652 */
653 return FALSE;
654
655 EXCEPTION
656 WHEN app_exceptions.application_exception THEN
657 IF ( chk_stmt_line%ISOPEN ) THEN
658 CLOSE chk_stmt_line;
659 END IF;
660 IF ( chk_arch_stmt_line%ISOPEN ) THEN
661 CLOSE chk_arch_stmt_line;
662 END IF;
663 RAISE;
664 WHEN OTHERS THEN
665 fnd_message.set_name( 'SQLCE', 'CE_UNHANDLED_EXCEPTION');
666 fnd_message.set_token( 'FUNCTION',
667 'CE_TRANSACTION_CODES_pkg.Is_In_Use');
668 IF ( chk_stmt_line%ISOPEN ) THEN
669 CLOSE chk_stmt_line;
670 END IF;
671 IF ( chk_arch_stmt_line%ISOPEN ) THEN
672 CLOSE chk_arch_stmt_line;
673 END IF;
674 RAISE;
675 END is_in_use;
676
677 END CE_TRANSACTION_CODES_PKG;