[Home] [Help]
PACKAGE BODY: APPS.CE_JE_MAPPINGS_PKG
Source
1 PACKAGE BODY CE_JE_MAPPINGS_PKG as
2 /* $Header: cejemcdb.pls 120.1 2006/04/07 06:27:20 svali noship $ */
3 --
4 -- Package
5 -- ce_je_mappings_pkg
6 -- Purpose
7 -- To contain validation and insertion routines for cb_transaction_codes
8 -- History
9 -- 08-Sept-2004 Sahik Vali Created
10
11 FUNCTION body_revision RETURN VARCHAR2 IS
12 BEGIN
13
14 RETURN '$Revision: 120.1 $';
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 --
27 -- Procedure
28 -- Insert_Row
29 -- Purpose
30 -- Inserts a row into ce_je_mappings
31 -- History
32 -- 08-Sept-2004 Shaik Vali Created
33 -- Arguments
34 -- all the columns of the table CE_JE_MAPPINGS
35 -- Example
36 -- CE_JE_MAPPINGS_PKG.Insert_Row(....;
37 -- Notes
38 --
39 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
40 X_JE_Mapping_Id IN OUT NOCOPY NUMBER,
41 X_Bank_Account_Id NUMBER,
42 X_Trx_Code_Id NUMBER,
43 X_Search_String_txt VARCHAR2,
44 X_GL_Account_CCID NUMBER,
45 X_Reference_txt VARCHAR2,
46 X_Last_Updated_By NUMBER,
47 X_Last_Update_Date DATE,
48 X_Last_Update_Login NUMBER,
49 X_Created_By NUMBER,
50 X_Creation_Date DATE,
51 X_trxn_subtype_code_id NUMBER
52 ) IS
53 CURSOR C IS SELECT rowid FROM CE_JE_MAPPINGS
54 WHERE je_mapping_id = X_JE_Mapping_Id;
55
56 CURSOR C2 IS SELECT ce_je_mappings_s.nextval FROM sys.dual;
57 --
58 BEGIN
59 -- cep_standard.debug('open c2 ');
60
61 OPEN C2;
62 FETCH C2 INTO X_JE_Mapping_id;
63 CLOSE C2;
64 --
65
66 INSERT INTO CE_JE_MAPPINGS(
67 je_mapping_id,
68 bank_account_id,
69 trx_code_id,
70 search_string_txt,
71 GL_account_ccid,
72 reference_txt,
73 Last_Updated_By,
74 Last_Update_Date,
75 Last_Update_Login,
76 Created_By,
77 Creation_Date,
78 trxn_subtype_code_id
79 ) VALUES (
80 X_JE_Mapping_Id,
81 X_Bank_Account_Id,
82 X_Trx_Code_Id,
83 X_Search_String_txt,
84 X_GL_Account_CCID,
85 X_Reference_txt,
86 X_Last_Updated_By,
87 X_Last_Update_Date,
88 X_Last_Update_Login,
89 X_Created_By,
90 X_Creation_Date,
91 X_trxn_subtype_code_id
92 );
93 --
94 OPEN C;
95 FETCH C INTO X_Rowid;
96 if (C%NOTFOUND) then
97 CLOSE C;
98 Raise NO_DATA_FOUND;
99 end if;
100 CLOSE C;
101 END Insert_Row;
102
103
104 --
105 -- Procedure
106 -- Lock_Row
107 -- Purpose
108 -- Locks a row into ce_je_mappings
109 -- History
110 -- 08-Sept-2004 Shaik Vali Created
111 -- Arguments
112 -- all the columns of the table CE_JE_MAPPINGS
113 -- Example
114 -- ce_je_mappings_pkg.Lock_Row(....;
115 -- Notes
116 --
117 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
118 X_JE_Mapping_Id NUMBER,
119 X_Bank_Account_Id NUMBER,
120 X_Trx_Code_Id NUMBER,
121 X_GL_Account_CCID NUMBER,
122 X_Search_String_txt VARCHAR2,
123 X_Reference_txt VARCHAR2,
124 X_trxn_subtype_code_id NUMBER
125 ) IS
126 CURSOR C IS
127 SELECT *
128 FROM CE_JE_MAPPINGS
129 WHERE rowid = X_Rowid
130 FOR UPDATE of JE_Mapping_Id NOWAIT;
131 Recinfo C%ROWTYPE;
132 BEGIN
133 OPEN C;
134 FETCH C INTO Recinfo;
135 if (C%NOTFOUND) then
136 CLOSE C;
137 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
138 APP_EXCEPTION.Raise_Exception;
139 end if;
140 CLOSE C;
141 if (
142 (Recinfo.je_mapping_id = X_JE_Mapping_Id)
143 AND (Recinfo.bank_account_id = X_Bank_Account_Id)
144 AND (Recinfo.trx_code_id = X_Trx_Code_Id)
145 AND (Recinfo.gl_account_ccid = X_GL_Account_CCID)
146 AND (Recinfo.search_string_txt is null
147 OR (Recinfo.search_string_txt = X_Search_String_txt))
148 AND (Recinfo.reference_txt is null
149 OR (Recinfo.reference_txt = X_Reference_txt))
150 ) then
151 return;
152 else
153 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
154 APP_EXCEPTION.Raise_Exception;
155 end if;
156 END Lock_Row;
157
158 --
159 -- Procedure
160 -- Update_Row
161 -- Purpose
162 -- Updates a row into ce_je_mappings
163 -- History
164 -- 08-Sept-2004 Shaik Vali Created
165 -- Arguments
166 -- all the columns of the table CE_JE_MAPPINGS
167 -- Example
168 -- ce_je_mappings_pkg.Update_Row(....;
169 -- Notes
170 --
171 PROCEDURE Update_Row(X_Rowid VARCHAR2,
172 X_JE_Mapping_Id NUMBER,
173 X_Bank_Account_Id NUMBER,
174 X_Trx_Code_Id NUMBER,
175 X_GL_Account_CCID NUMBER,
176 X_Search_String_txt VARCHAR2,
177 X_Reference_txt VARCHAR2,
178 X_Last_Updated_By NUMBER,
179 X_Last_Update_Date DATE,
180 X_Last_Update_Login NUMBER,
181 X_trxn_subtype_code_id NUMBER
182 ) IS
183 BEGIN
184 UPDATE CE_JE_MAPPINGS
185 SET
186 je_mapping_id = X_JE_Mapping_Id,
187 bank_account_id = X_Bank_Account_Id,
188 trx_code_id = X_Trx_Code_Id,
189 gl_account_ccid = X_GL_Account_CCID,
190 search_string_txt = X_Search_String_txt,
191 reference_txt = X_Reference_txt,
192 last_updated_by = X_Last_Updated_By,
193 last_update_date = X_Last_Update_Date,
194 last_update_login = X_Last_Update_Login,
195 trxn_subtype_code_id = X_trxn_subtype_code_id
196 WHERE rowid = X_Rowid;
197 --
198 if (SQL%NOTFOUND) then
199 Raise NO_DATA_FOUND;
200 end if;
201 END Update_Row;
202 --
203
204 -- Procedure
205 -- Delete_Row
206 -- Purpose
207 -- Deletes a row from ce_je_mappings
208 -- History
209 -- 08-Sept-2004 Shaik Vali Created
210 -- Arguments
211 -- x_rowid Rowid of a row
212 -- Example
213 -- ce_je_mappings_pkg.delete_row(...;
214 -- Notes
215 --
216 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
217 BEGIN
218 DELETE FROM CE_JE_MAPPINGS
219 WHERE rowid = X_Rowid;
220 --
221 if (SQL%NOTFOUND) then
222 Raise NO_DATA_FOUND;
223 end if;
224 END Delete_Row;
225 --
226
227 -- Procedure
228 -- check_unique_combination
229 -- Purpose
230 -- Checks for uniquness of combination of the Bank account,
231 -- Transaction code,Search string and GL Account before
232 -- insertion and updates for a given mapping
233 -- History
234 -- 08-Sept-2004 Shaik Vali Created
235 -- Arguments
236 -- x_row_id Rowid of a row
237 -- X_trx_code_id Transaction code of row to be inserted or updated
238 -- X_bank_account_id Bank Account Id
239 -- X_GL_accountccid GL Account ccid
240 -- X_Search_string_txt Search string
241 -- Example
242 -- ce_je_mappings_pkg.check_unique_combination(..;
243 -- Notes
244 --
245
246
247 PROCEDURE check_unique_combination( X_bank_account_id IN NUMBER,
248 X_trx_code_id IN NUMBER,
249 X_GL_account_ccid IN NUMBER,
250 X_Search_string_txt VARCHAR2,
251 X_Row_id IN VARCHAR2 ) IS
252 --
253 CURSOR chk_duplicates is
254 SELECT 'Duplicate'
255 FROM ce_je_mappings jem
256 WHERE bank_account_id = X_bank_account_id
257 AND trx_code_id = X_trx_code_id
258 AND ((search_string_txt is null and X_Search_string_txt is null) or
259 (search_string_txt = X_Search_string_txt))
260 AND ( X_Row_id is null
261 OR jem.rowid <> chartorowid( X_Row_id ) );
262 dummy VARCHAR2(100);
263 --
264 BEGIN
265 OPEN chk_duplicates;
266 FETCH chk_duplicates INTO dummy;
267 --
268 IF chk_duplicates%FOUND THEN
269 CLOSE chk_duplicates;
270 fnd_message.set_name( 'CE', 'CE_DUP_BANK_JE_MAPPING' );
271 app_exception.raise_exception;
272 END IF;
273 --
274 CLOSE chk_duplicates;
275 --
276 EXCEPTION
277 WHEN app_exceptions.application_exception THEN
278 IF ( chk_duplicates%ISOPEN ) THEN
279 CLOSE chk_duplicates;
280 END IF;
281 RAISE;
282 WHEN OTHERS THEN
283 fnd_message.set_name( 'SQLCE', 'CE_UNHANDLED_EXCEPTION');
284 fnd_message.set_token( 'PROCEDURE',
285 'CE_TRANSACTION_CODES_pkg.check_unique_txn_code');
286 IF ( chk_duplicates%ISOPEN ) THEN
287 CLOSE chk_duplicates;
288 END IF;
289 RAISE;
290 END check_unique_combination;
291
292 END CE_JE_MAPPINGS_PKG;