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