[Home] [Help]
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
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: --
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,
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
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:
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,
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,
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
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: --
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
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: --
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,
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;
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 ) )
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)
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')
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
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
673: END IF;
674: RAISE;
675: END is_in_use;
676:
677: END CE_TRANSACTION_CODES_PKG;