[Home] [Help]
PACKAGE BODY: APPS.GL_SUSPENSE_ACCOUNTS_PKG
Source
1 PACKAGE BODY GL_SUSPENSE_ACCOUNTS_PKG as
2 /* $Header: gliacsab.pls 120.5 2005/05/05 00:58:41 kvora ship $ */
3
4
5 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
6 X_Ledger_Id NUMBER,
7 X_Je_Source_Name VARCHAR2,
8 X_Je_Category_Name VARCHAR2,
9 X_Code_Combination_Id NUMBER,
10 X_Last_Update_Date DATE,
11 X_Last_Updated_By NUMBER,
12 X_Creation_Date DATE,
13 X_Created_By NUMBER,
14 X_Last_Update_Login NUMBER,
15 X_Attribute1 VARCHAR2,
16 X_Attribute2 VARCHAR2,
17 X_Attribute3 VARCHAR2,
18 X_Attribute4 VARCHAR2,
19 X_Attribute5 VARCHAR2,
20 X_Context VARCHAR2
21 ) IS
22 CURSOR C IS SELECT rowid FROM GL_SUSPENSE_ACCOUNTS
23
24 WHERE ledger_id = X_Ledger_Id
25
26 AND je_source_name = X_Je_Source_Name
27
28 AND je_category_name = X_Je_Category_Name
29
30 AND code_combination_id = X_Code_Combination_Id;
31
32
33 BEGIN
34
35 INSERT INTO GL_SUSPENSE_ACCOUNTS(
36 ledger_id,
37 je_source_name,
38 je_category_name,
39 code_combination_id,
40 last_update_date,
41 last_updated_by,
42 creation_date,
43 created_by,
44 last_update_login,
45 attribute1,
46 attribute2,
47 attribute3,
48 attribute4,
49 attribute5,
50 context
51 ) VALUES (
52 X_Ledger_Id,
53 X_Je_Source_Name,
54 X_Je_Category_Name,
55 X_Code_Combination_Id,
56 X_Last_Update_Date,
57 X_Last_Updated_By,
58 X_Creation_Date,
59 X_Created_By,
60 X_Last_Update_Login,
61 X_Attribute1,
62 X_Attribute2,
63 X_Attribute3,
64 X_Attribute4,
65 X_Attribute5,
66 X_Context
67 );
68
69 OPEN C;
70 FETCH C INTO X_Rowid;
71 if (C%NOTFOUND) then
72 CLOSE C;
73 RAISE NO_DATA_FOUND;
74 end if;
75 CLOSE C;
76 END Insert_Row;
77
78 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
79
80 X_Ledger_Id NUMBER,
81 X_Je_Source_Name VARCHAR2,
82 X_Je_Category_Name VARCHAR2,
83 X_Code_Combination_Id NUMBER,
84 X_Attribute1 VARCHAR2,
85 X_Attribute2 VARCHAR2,
86 X_Attribute3 VARCHAR2,
87 X_Attribute4 VARCHAR2,
88 X_Attribute5 VARCHAR2,
89 X_Context VARCHAR2
90 ) IS
91 CURSOR C IS
92 SELECT *
93 FROM GL_SUSPENSE_ACCOUNTS
94 WHERE rowid = X_Rowid
95 FOR UPDATE of Ledger_Id NOWAIT;
96 Recinfo C%ROWTYPE;
97 BEGIN
98 OPEN C;
99 FETCH C INTO Recinfo;
100 if (C%NOTFOUND) then
101 CLOSE C;
102 RAISE NO_DATA_FOUND;
103 end if;
104 CLOSE C;
105 if (
106 ( (Recinfo.ledger_id = X_Ledger_Id)
107 OR ( (Recinfo.ledger_id IS NULL)
108 AND (X_Ledger_Id IS NULL)))
109 AND ( (Recinfo.je_source_name = X_Je_Source_Name)
110 OR ( (Recinfo.je_source_name IS NULL)
111 AND (X_Je_Source_Name IS NULL)))
112 AND ( (Recinfo.je_category_name = X_Je_Category_Name)
113 OR ( (Recinfo.je_category_name IS NULL)
114 AND (X_Je_Category_Name IS NULL)))
115 AND ( (Recinfo.code_combination_id = X_Code_Combination_Id)
116 OR ( (Recinfo.code_combination_id IS NULL)
117 AND (X_Code_Combination_Id IS NULL)))
118 AND ( (Recinfo.attribute1 = X_Attribute1)
119 OR ( (Recinfo.attribute1 IS NULL)
120 AND (X_Attribute1 IS NULL)))
121 AND ( (Recinfo.attribute2 = X_Attribute2)
122 OR ( (Recinfo.attribute2 IS NULL)
123 AND (X_Attribute2 IS NULL)))
124 AND ( (Recinfo.attribute3 = X_Attribute3)
125 OR ( (Recinfo.attribute3 IS NULL)
126 AND (X_Attribute3 IS NULL)))
127 AND ( (Recinfo.attribute4 = X_Attribute4)
128 OR ( (Recinfo.attribute4 IS NULL)
129 AND (X_Attribute4 IS NULL)))
130 AND ( (Recinfo.attribute5 = X_Attribute5)
131 OR ( (Recinfo.attribute5 IS NULL)
132 AND (X_Attribute5 IS NULL)))
133 AND ( (Recinfo.context = X_Context)
134 OR ( (Recinfo.context IS NULL)
135 AND (X_Context IS NULL)))
136 ) then
137 return;
138 else
139 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
140 APP_EXCEPTION.RAISE_EXCEPTION;
141 end if;
142 END Lock_Row;
143
144 PROCEDURE Update_Row(X_Rowid VARCHAR2,
145 X_Ledger_Id NUMBER,
146 X_Je_Source_Name VARCHAR2,
147 X_Je_Category_Name VARCHAR2,
148 X_Code_Combination_Id NUMBER,
149 X_Last_Update_Date DATE,
150 X_Last_Updated_By NUMBER,
151 X_Last_Update_Login NUMBER,
152 X_Attribute1 VARCHAR2,
153 X_Attribute2 VARCHAR2,
154 X_Attribute3 VARCHAR2,
155 X_Attribute4 VARCHAR2,
156 X_Attribute5 VARCHAR2,
157 X_Context VARCHAR2
158 ) IS
159 BEGIN
160 UPDATE GL_SUSPENSE_ACCOUNTS
161 SET
162
163 ledger_id = X_Ledger_Id,
164 je_source_name = X_Je_Source_Name,
165 je_category_name = X_Je_Category_Name,
166 code_combination_id = X_Code_Combination_Id,
167 last_update_date = X_Last_Update_Date,
168 last_updated_by = X_Last_Updated_By,
169 last_update_login = X_Last_Update_Login,
170 attribute1 = X_Attribute1,
171 attribute2 = X_Attribute2,
172 attribute3 = X_Attribute3,
173 attribute4 = X_Attribute4,
174 attribute5 = X_Attribute5,
175 context = X_Context
176 WHERE rowid = X_rowid;
177
178 if (SQL%NOTFOUND) then
179 RAISE NO_DATA_FOUND;
180 end if;
181
182 END Update_Row;
183
184 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
185 BEGIN
186 DELETE FROM GL_SUSPENSE_ACCOUNTS
187 WHERE rowid = X_Rowid;
188
189 if (SQL%NOTFOUND) then
190 RAISE NO_DATA_FOUND;
191 end if;
192 END Delete_Row;
193
194 PROCEDURE Check_Unique(X_Ledger_Id NUMBER,
195 X_Je_Source_Name VARCHAR2,
196 X_Je_Category_Name VARCHAR2,
197 X_Rowid VARCHAR2
198 ) IS
199 CURSOR check_dups IS
200 SELECT 1
201 FROM GL_SUSPENSE_ACCOUNTS sa
202 WHERE sa.je_source_name = X_Je_Source_Name
203 AND sa.je_category_name = X_Je_Category_Name
204 AND sa.ledger_id = X_Ledger_Id
205 AND ( X_Rowid is NULL
206 OR sa.rowid <> X_Rowid);
207
208 dummy NUMBER;
209
210 BEGIN
211 OPEN check_dups;
212 FETCH check_dups INTO dummy;
213
214 IF check_dups%FOUND THEN
215 CLOSE check_dups;
216 fnd_message.set_name('SQLGL', 'GL_SUS_ACCT_ALREADY_DEFINED');
217 app_exception.raise_exception;
218 END IF;
219
220 CLOSE check_dups;
221
222 EXCEPTION
223 WHEN app_exceptions.application_exception THEN
224 RAISE;
225 WHEN OTHERS THEN
226 fnd_message.set_name('SQLGL', 'Unhandled Exception');
227 fnd_message.set_token('PROCEDURE',
228 'GL_SUSPENSE_ACCOUNTS.Check_Unique');
229 RAISE;
230 END Check_Unique;
231
232 -- **********************************************************************
233
234 FUNCTION is_ledger_suspense_exist( x_ledger_id NUMBER ) RETURN BOOLEAN IS
235
236 CURSOR c_other IS
237 SELECT 'found'
238 FROM GL_SUSPENSE_ACCOUNTS sa
239 WHERE sa.LEDGER_ID = x_ledger_id
240 AND sa.JE_SOURCE_NAME = 'Other'
241 AND sa.JE_CATEGORY_NAME = 'Other';
242
243 dummy VARCHAR2( 100 );
244
245 BEGIN
246
247 OPEN c_other;
248 FETCH c_other INTO dummy;
249
250 IF c_other%FOUND THEN
251 CLOSE c_other;
252 RETURN( TRUE );
253 ELSE
254 CLOSE c_other;
255 RETURN( FALSE );
256 END IF;
257
258 EXCEPTION
259 WHEN app_exceptions.application_exception THEN
260 RAISE;
261 WHEN OTHERS THEN
262 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
263 fnd_message.set_token('PROCEDURE',
264 'GL_SUSPENSE_ACCOUNTS_PKG.is_ledger_suspense_exist');
265 RAISE;
266
267 END is_ledger_suspense_exist;
268
269 -- **********************************************************************
270
271 PROCEDURE insert_ledger_suspense( x_ledger_id NUMBER,
272 x_code_combination_id NUMBER,
273 x_last_update_date DATE,
274 x_last_updated_by NUMBER ) IS
275 BEGIN
276
277 LOCK TABLE GL_SUSPENSE_ACCOUNTS IN SHARE UPDATE MODE;
278
279 INSERT INTO gl_suspense_accounts
280 ( ledger_id,
281 je_source_name,
282 je_category_name,
283 code_combination_id,
284 last_update_date,
285 last_updated_by )
286 VALUES
287 ( x_ledger_id,
288 'Other',
289 'Other',
290 x_code_combination_id,
291 sysdate,
292 x_last_updated_by );
293
294 EXCEPTION
295 WHEN app_exceptions.application_exception THEN
296 RAISE;
297 WHEN OTHERS THEN
298 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
299 fnd_message.set_token('PROCEDURE',
300 'GL_SUSPENSE_ACCOUNTS_PKG.insert_ledger_suspense');
301 RAISE;
302
303 END insert_ledger_suspense;
304
305 -- **********************************************************************
306
307 PROCEDURE update_ledger_suspense( x_ledger_id NUMBER,
308 x_code_combination_id NUMBER,
309 x_last_update_date DATE,
310 x_last_updated_by NUMBER ) IS
311 BEGIN
312
313 LOCK TABLE GL_SUSPENSE_ACCOUNTS IN SHARE UPDATE MODE;
314
315 IF (x_code_combination_id IS NULL) THEN
316 DELETE FROM gl_suspense_accounts
317 WHERE ledger_id = x_ledger_id
318 AND je_source_name = 'Other'
319 AND je_category_name = 'Other';
320 ELSE
321 UPDATE gl_suspense_accounts
322 SET code_combination_id = x_code_combination_id,
323 last_update_date = x_last_update_date,
324 last_updated_by = x_last_updated_by
325 WHERE ledger_id = x_ledger_id
326 AND je_source_name = 'Other'
327 AND je_category_name = 'Other';
328 END IF;
329
330 EXCEPTION
331 WHEN NO_DATA_FOUND THEN
332 RETURN;
333 WHEN app_exceptions.application_exception THEN
334 RAISE;
335 WHEN OTHERS THEN
336 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
337 fnd_message.set_token('PROCEDURE',
338 'GL_SUSPENSE_ACCOUNTS_PKG.update_ledger_suspense');
339 RAISE;
340
341 END update_ledger_suspense;
342
343
344
345 END GL_SUSPENSE_ACCOUNTS_PKG;