[Home] [Help]
PACKAGE BODY: APPS.GL_INTERCOMPANY_ACCTS_PKG
Source
1 PACKAGE BODY GL_INTERCOMPANY_ACCTS_PKG as
2 /* $Header: gliacicb.pls 120.6 2005/05/05 00:58:26 kvora ship $ */
3
4
5 --
6 -- PUBLIC FUNCTIONS
7 --
8
9 PROCEDURE check_acc_set_unique( x_rowid VARCHAR2,
10 x_ledger_id NUMBER,
11 x_je_source_name VARCHAR2,
12 x_je_category_name VARCHAR2 ) IS
13 CURSOR c_dup IS
14 SELECT 'Duplicate row found'
15 FROM gl_intercompany_acc_sets ia
16 WHERE ia.LEDGER_ID = x_ledger_id
17 AND ia.JE_SOURCE_NAME = x_je_source_name
18 AND ia.JE_CATEGORY_NAME = x_je_category_name
19 AND ( x_rowid IS NULL
20 OR
21 ia.rowid <> x_rowid );
22 dummy VARCHAR2( 100 );
23
24 BEGIN
25 OPEN c_dup;
26 FETCH c_dup INTO dummy;
27
28 IF c_dup%FOUND THEN
29 CLOSE c_dup;
30 fnd_message.set_name('SQLGL',
31 gl_public_sector.get_message_name('GL_DUPLICATE_INTERCO_ACC_SET',
32 'SQLGL', null));
33 app_exception.raise_exception;
34 END IF;
35
36 CLOSE c_dup;
37
38 EXCEPTION
39 WHEN app_exceptions.application_exception THEN
40 RAISE;
41 WHEN OTHERS THEN
42 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
43 fnd_message.set_token('PROCEDURE',
44 'GL_INTERCOMPANY_ACCTS_PKG.check_acc_set_unique');
45 RAISE;
46
47 END check_acc_set_unique;
48
49
50 -- **********************************************************************
51 PROCEDURE check_acct_unique( x_rowid VARCHAR2,
52 x_ledger_id NUMBER,
53 x_je_source_name VARCHAR2,
54 x_je_category_name VARCHAR2,
55 x_bal_seg_value VARCHAR2 ) IS
56 CURSOR c_dup IS
57 SELECT 'Duplicate row found'
58 FROM gl_intercompany_accounts ia
59 WHERE ia.LEDGER_ID = x_ledger_id
60 AND ia.JE_SOURCE_NAME = x_je_source_name
61 AND ia.JE_CATEGORY_NAME = x_je_category_name
62 AND ia.bal_seg_value = x_bal_seg_value
63 AND ( x_rowid IS NULL
64 OR
65 ia.rowid <> x_rowid );
66
67 dummy VARCHAR2( 100 );
68
69 BEGIN
70 OPEN c_dup;
71 FETCH c_dup INTO dummy;
72
73 IF c_dup%FOUND THEN
74 CLOSE c_dup;
75 fnd_message.set_name('SQLGL',
76 gl_public_sector.get_message_name('GL_DUPLICATE_INTERCO_ACCT',
77 'SQLGL', null));
78 app_exception.raise_exception;
79 END IF;
80
81 CLOSE c_dup;
82
83 EXCEPTION
84 WHEN app_exceptions.application_exception THEN
85 RAISE;
86 WHEN OTHERS THEN
87 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
88 fnd_message.set_token('PROCEDURE',
89 'GL_INTERCOMPANY_ACCTS_PKG.check_acct_unique');
90 RAISE;
91
92 END check_acct_unique;
93
94
95 -- **********************************************************************
96
97 FUNCTION is_other_exist( x_ledger_id NUMBER ) RETURN BOOLEAN IS
98
99 CURSOR c_other IS
100 SELECT 'found'
101 FROM gl_intercompany_accounts ia
102 WHERE ia.LEDGER_ID = x_ledger_id
103 AND ia.JE_SOURCE_NAME = 'Other'
104 AND ia.JE_CATEGORY_NAME = 'Other'
105 AND ia.BAL_SEG_VALUE = 'OTHER1234567890123456789012345';
106
107 dummy VARCHAR2( 100 );
108
109 BEGIN
110
111 OPEN c_other;
112 FETCH c_other INTO dummy;
113
114 IF c_other%FOUND THEN
115 CLOSE c_other;
116 RETURN( TRUE );
117 ELSE
118 CLOSE c_other;
119 RETURN( FALSE );
120 END IF;
121
122 EXCEPTION
123 WHEN app_exceptions.application_exception THEN
124 RAISE;
125 WHEN OTHERS THEN
126 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
127 fnd_message.set_token('PROCEDURE',
128 'GL_INTERCOMPANY_ACCTS_PKG.is_other_exist');
129 RAISE;
130
131 END is_other_exist;
132
133 -- **********************************************************************
134
135 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
136 X_Je_Source_Name VARCHAR2,
137 X_Je_Category_Name VARCHAR2,
138 X_Ledger_Id NUMBER,
139 X_Balance_By_Code VARCHAR2,
140 X_Bal_Seg_Rule_Code VARCHAR2,
141 X_Always_Balance_Flag VARCHAR2,
142 X_Last_Update_Date DATE,
143 X_Last_Updated_By NUMBER,
144 X_Creation_Date DATE,
145 X_Created_By NUMBER,
146 X_Last_Update_Login NUMBER,
147 X_Default_Bal_Seg_Value VARCHAR2,
148 X_Attribute1 VARCHAR2,
149 X_Attribute2 VARCHAR2,
150 X_Attribute3 VARCHAR2,
151 X_Attribute4 VARCHAR2,
152 X_Attribute5 VARCHAR2,
153 X_Attribute6 VARCHAR2,
154 X_Attribute7 VARCHAR2,
155 X_Attribute8 VARCHAR2,
156 X_Attribute9 VARCHAR2,
157 X_Attribute10 VARCHAR2,
158 X_Attribute11 VARCHAR2,
159 X_Attribute12 VARCHAR2,
160 X_Attribute13 VARCHAR2,
161 X_Attribute14 VARCHAR2,
162 X_Attribute15 VARCHAR2,
163 X_Context VARCHAR2
164 ) IS
165 CURSOR C IS SELECT rowid FROM gl_intercompany_acc_sets
166 WHERE je_source_name = X_Je_Source_Name
167 AND je_category_name = X_Je_Category_Name
168 AND ledger_id = X_Ledger_Id;
169
170 BEGIN
171
172 INSERT INTO gl_intercompany_acc_sets(
173 je_source_name,
174 je_category_name,
175 ledger_id,
176 balance_by_code,
177 bal_seg_rule_code,
178 always_balance_flag,
179 last_update_date,
180 last_updated_by,
181 creation_date,
182 created_by,
183 last_update_login,
184 default_bal_seg_value,
185 attribute1,
186 attribute2,
187 attribute3,
188 attribute4,
189 attribute5,
190 attribute6,
191 attribute7,
192 attribute8,
193 attribute9,
194 attribute10,
195 attribute11,
196 attribute12,
197 attribute13,
198 attribute14,
199 attribute15,
200 context
201 ) VALUES (
202 X_Je_Source_Name,
203 X_Je_Category_Name,
204 X_Ledger_Id,
205 X_Balance_By_Code,
206 X_Bal_Seg_Rule_Code,
207 X_Always_Balance_Flag,
208 X_Last_Update_Date,
209 X_Last_Updated_By,
210 X_Creation_Date,
211 X_Created_By,
212 X_Last_Update_Login,
213 X_Default_Bal_Seg_Value,
214 X_Attribute1,
215 X_Attribute2,
216 X_Attribute3,
217 X_Attribute4,
218 X_Attribute5,
219 X_Attribute6,
220 X_Attribute7,
221 X_Attribute8,
222 X_Attribute9,
223 X_Attribute10,
224 X_Attribute11,
225 X_Attribute12,
226 X_Attribute13,
227 X_Attribute14,
228 X_Attribute15,
229 X_Context
230 );
231
232 OPEN C;
233 FETCH C INTO X_Rowid;
234
235 if (C%NOTFOUND) then
236 CLOSE C;
237 RAISE NO_DATA_FOUND;
238 end if;
239 CLOSE C;
240
241 EXCEPTION
242 WHEN app_exceptions.application_exception THEN
243 RAISE;
244 WHEN OTHERS THEN
245 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
246 fnd_message.set_token('PROCEDURE',
247 'GL_INTERCOMPANY_ACCTS_PKG.Insert_Row');
248 RAISE;
249
250 END Insert_Row;
251
252 -- **********************************************************************
253
254 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
255 X_Je_Source_Name VARCHAR2,
256 X_Je_Category_Name VARCHAR2,
257 X_Ledger_Id NUMBER,
258 X_Balance_By_Code VARCHAR2,
259 X_Bal_Seg_Rule_Code VARCHAR2,
260 X_Always_Balance_Flag VARCHAR2,
261 X_Default_Bal_Seg_Value VARCHAR2,
262 X_Attribute1 VARCHAR2,
263 X_Attribute2 VARCHAR2,
264 X_Attribute3 VARCHAR2,
265 X_Attribute4 VARCHAR2,
266 X_Attribute5 VARCHAR2,
267 X_Attribute6 VARCHAR2,
268 X_Attribute7 VARCHAR2,
269 X_Attribute8 VARCHAR2,
270 X_Attribute9 VARCHAR2,
271 X_Attribute10 VARCHAR2,
272 X_Attribute11 VARCHAR2,
273 X_Attribute12 VARCHAR2,
274 X_Attribute13 VARCHAR2,
275 X_Attribute14 VARCHAR2,
276 X_Attribute15 VARCHAR2,
277 X_Context VARCHAR2
278 ) IS
279 CURSOR C IS
280 SELECT *
281 FROM gl_intercompany_acc_sets
282 WHERE rowid = X_Rowid
283 FOR UPDATE of Je_Source_Name NOWAIT;
284 Recinfo C%ROWTYPE;
285 BEGIN
286 OPEN C;
287 FETCH C INTO Recinfo;
288 if (C%NOTFOUND) then
289 CLOSE C;
290 RAISE NO_DATA_FOUND;
291 end if;
292 CLOSE C;
293 if (
294 ( (Recinfo.je_source_name = X_Je_Source_Name)
295 OR ( (Recinfo.je_source_name IS NULL)
296 AND (X_Je_Source_Name IS NULL)))
297 AND ( (Recinfo.je_category_name = X_Je_Category_Name)
298 OR ( (Recinfo.je_category_name IS NULL)
299 AND (X_Je_Category_Name IS NULL)))
300 AND ( (Recinfo.ledger_id = X_Ledger_Id)
301 OR ( (Recinfo.ledger_id IS NULL)
302 AND (X_Ledger_Id IS NULL)))
303 AND ( (Recinfo.balance_by_code = X_Balance_By_Code)
304 OR ( (Recinfo.Balance_By_Code IS NULL)
305 AND (X_Balance_By_Code IS NULL)))
306 AND ( (Recinfo.bal_seg_rule_code = X_Bal_Seg_Rule_Code)
307 OR ( (Recinfo.bal_seg_rule_code IS NULL)
308 AND (X_Bal_Seg_Rule_Code IS NULL)))
309 AND ( (Recinfo.always_balance_flag = X_Always_Balance_Flag)
310 OR ( (Recinfo.always_balance_flag IS NULL)
311 AND (X_Always_Balance_Flag IS NULL)))
312 AND ( (Recinfo.default_bal_seg_value = X_Default_Bal_Seg_Value)
313 OR ( (Recinfo.default_bal_seg_value IS NULL)
314 AND (X_Default_Bal_Seg_Value IS NULL)))
315 AND ( (Recinfo.attribute1 = X_Attribute1)
316 OR ( (Recinfo.attribute1 IS NULL)
317 AND (X_Attribute1 IS NULL)))
318 AND ( (Recinfo.attribute2 = X_Attribute2)
319 OR ( (Recinfo.attribute2 IS NULL)
320 AND (X_Attribute2 IS NULL)))
321 AND ( (Recinfo.attribute3 = X_Attribute3)
322 OR ( (Recinfo.attribute3 IS NULL)
323 AND (X_Attribute3 IS NULL)))
324 AND ( (Recinfo.attribute4 = X_Attribute4)
325 OR ( (Recinfo.attribute4 IS NULL)
326 AND (X_Attribute4 IS NULL)))
327 AND ( (Recinfo.attribute5 = X_Attribute5)
328 OR ( (Recinfo.attribute5 IS NULL)
329 AND (X_Attribute5 IS NULL)))
330 AND ( (Recinfo.attribute6 = X_Attribute6)
331 OR ( (Recinfo.attribute6 IS NULL)
332 AND (X_Attribute6 IS NULL)))
333 AND ( (Recinfo.attribute7 = X_Attribute7)
334 OR ( (Recinfo.attribute7 IS NULL)
335 AND (X_Attribute7 IS NULL)))
336 AND ( (Recinfo.attribute8 = X_Attribute8)
337 OR ( (Recinfo.attribute8 IS NULL)
338 AND (X_Attribute8 IS NULL)))
339 AND ( (Recinfo.attribute9 = X_Attribute9)
340 OR ( (Recinfo.attribute9 IS NULL)
341 AND (X_Attribute9 IS NULL)))
342 AND ( (Recinfo.attribute10 = X_Attribute10)
343 OR ( (Recinfo.attribute10 IS NULL)
344 AND (X_Attribute10 IS NULL)))
345 AND ( (Recinfo.attribute11 = X_Attribute11)
346 OR ( (Recinfo.attribute11 IS NULL)
347 AND (X_Attribute11 IS NULL)))
348 AND ( (Recinfo.attribute12 = X_Attribute12)
349 OR ( (Recinfo.attribute12 IS NULL)
350 AND (X_Attribute12 IS NULL)))
351 AND ( (Recinfo.attribute13 = X_Attribute13)
352 OR ( (Recinfo.attribute13 IS NULL)
353 AND (X_Attribute13 IS NULL)))
354 AND ( (Recinfo.attribute14 = X_Attribute14)
355 OR ( (Recinfo.attribute14 IS NULL)
356 AND (X_Attribute14 IS NULL)))
357 AND ( (Recinfo.attribute15 = X_Attribute15)
358 OR ( (Recinfo.attribute15 IS NULL)
359 AND (X_Attribute15 IS NULL)))
360 AND ( (Recinfo.context = X_Context)
361 OR ( (Recinfo.context IS NULL)
362 AND (X_Context IS NULL)))
363 ) then
364 return;
365 else
366 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
367 APP_EXCEPTION.RAISE_EXCEPTION;
368 end if;
369
370 EXCEPTION
371 WHEN app_exceptions.application_exception THEN
372 RAISE;
373 WHEN OTHERS THEN
374 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
375 fnd_message.set_token('PROCEDURE',
376 'GL_INTERCOPANY_ACCTS_PKG.Lock_Row');
377 RAISE;
378
379 END Lock_Row;
380
381 -- **********************************************************************
382
383 PROCEDURE Update_Row(X_Rowid VARCHAR2,
384 X_Je_Source_Name VARCHAR2,
385 X_Je_Category_Name VARCHAR2,
386 X_Ledger_Id NUMBER,
387 X_Balance_By_Code VARCHAR2,
388 X_Bal_Seg_Rule_Code VARCHAR2,
389 X_Always_Balance_Flag VARCHAR2,
390 X_Last_Update_Date DATE,
391 X_Last_Updated_By NUMBER,
392 X_Last_Update_Login NUMBER,
393 X_Default_Bal_Seg_Value VARCHAR2,
394 X_Attribute1 VARCHAR2,
395 X_Attribute2 VARCHAR2,
396 X_Attribute3 VARCHAR2,
397 X_Attribute4 VARCHAR2,
398 X_Attribute5 VARCHAR2,
402 X_Attribute9 VARCHAR2,
399 X_Attribute6 VARCHAR2,
400 X_Attribute7 VARCHAR2,
401 X_Attribute8 VARCHAR2,
403 X_Attribute10 VARCHAR2,
404 X_Attribute11 VARCHAR2,
405 X_Attribute12 VARCHAR2,
406 X_Attribute13 VARCHAR2,
407 X_Attribute14 VARCHAR2,
408 X_Attribute15 VARCHAR2,
409 X_Context VARCHAR2
410 ) IS
411 BEGIN
412 UPDATE gl_intercompany_acc_sets
413 SET
414 je_source_name = X_Je_Source_Name,
415 je_category_name = X_Je_Category_Name,
416 ledger_id = X_Ledger_Id,
417 balance_by_code = X_Balance_By_Code,
418 bal_seg_rule_code = X_Bal_Seg_Rule_Code,
419 always_balance_flag = X_Always_Balance_Flag,
420 default_bal_seg_value = X_Default_Bal_Seg_Value,
421 last_update_date = X_Last_Update_Date,
422 last_updated_by = X_Last_Updated_By,
423 last_update_login = X_Last_Update_Login,
424 attribute1 = X_Attribute1,
425 attribute2 = X_Attribute2,
426 attribute3 = X_Attribute3,
427 attribute4 = X_Attribute4,
428 attribute5 = X_Attribute5,
429 attribute6 = X_Attribute6,
430 attribute7 = X_Attribute7,
431 attribute8 = X_Attribute8,
432 attribute9 = X_Attribute9,
433 attribute10 = X_Attribute10,
434 attribute11 = X_Attribute11,
435 attribute12 = X_Attribute12,
436 attribute13 = X_Attribute13,
437 attribute14 = X_Attribute14,
438 attribute15 = X_Attribute15,
439 context = X_Context
440 WHERE rowid = X_Rowid;
441
442 if (SQL%NOTFOUND) then
443 RAISE NO_DATA_FOUND;
444 end if;
445
446 EXCEPTION
447 WHEN app_exceptions.application_exception THEN
448 RAISE;
449 WHEN OTHERS THEN
450 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
451 fnd_message.set_token('PROCEDURE',
452 'GL_INTERCOMPANY_ACCTS_PKG.Update_Row');
453 RAISE;
454
455 END Update_Row;
456
457 -- **********************************************************************
458
459 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
460 BEGIN
461 DELETE FROM gl_intercompany_acc_sets
462 WHERE rowid = X_Rowid;
463
464 if (SQL%NOTFOUND) then
465 RAISE NO_DATA_FOUND;
466 end if;
467
468 EXCEPTION
469 WHEN app_exceptions.application_exception THEN
470 RAISE;
471 WHEN OTHERS THEN
472 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
473 fnd_message.set_token('PROCEDURE',
474 'GL_INTERCOMPANY_ACCTS_PKG.Delete_Row');
475 RAISE;
476
477 END Delete_Row;
478
479 -- **********************************************************************
480
481 END GL_INTERCOMPANY_ACCTS_PKG;