1 PACKAGE BODY gl_elimination_sets_pkg As
2 /* $Header: gliesetb.pls 120.6 2005/05/05 01:07:26 kvora ship $ */
3
4 ---
5 --- PRIVATE VARIABLES
6 ---
7
8 --- Position of the balancing segment
9 company_seg_num NUMBER := null;
10
11
12 -- Function
13 -- get_unique_id
14 -- Purpose
15 -- Returns nextval from gl_elimination_sets_s
16 -- Parameters
17 -- None
18 -- History
19 -- 11-06-1998 W Wong Created
20 -- Notes
21 -- Raises GL_ERROR_GETTING_UNIQUE_ID on failure
22 --
23 FUNCTION get_unique_id RETURN NUMBER IS
24
25 CURSOR get_new_id IS
26 SELECT gl_elimination_sets_s.NEXTVAL
27 FROM dual;
28 new_id number;
29
30 BEGIN
31 OPEN get_new_id;
32 FETCH get_new_id INTO new_id;
33
34 IF get_new_id%FOUND THEN
35 CLOSE get_new_id;
36 return(new_id);
37 ELSE
38 CLOSE get_new_id;
39 fnd_message.set_name('SQLGL', 'GL_ERROR_GETTING_UNIQUE_ID');
40 fnd_message.set_token('SEQUENCE', 'GL_ELIMINATION_SETS_S');
41 app_exception.raise_exception;
42 END IF;
43
44 EXCEPTION
45
46 WHEN app_exceptions.application_exception THEN
47 RAISE;
48
49 WHEN OTHERS THEN
50 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
51 fnd_message.set_token(
52 'PROCEDURE',
53 'gl_elimination_sets_pkg.get_unique_id');
54 RAISE;
55 END get_unique_id;
56
57 --
58 -- Procedure
59 -- get_company_description
60 -- Purpose
61 -- Gets the description for the elimination company value
62 -- History
63 -- 05-Nov-98 W Wong Created
64 -- Parameters
65 -- x_coa_id ID of the current chart of accounts
66 -- x_company_val Elimination company value
67 -- Notes
68 -- None
69 --
70 FUNCTION get_company_description(
71 x_coa_id NUMBER,
72 x_company_val VARCHAR2
73 ) RETURN VARCHAR2 IS
74 BEGIN
75 IF (company_seg_num IS NULL) THEN
76 IF (NOT fnd_flex_apis.get_qualifier_segnum(
77 appl_id => 101,
78 key_flex_code => 'GL#',
79 structure_number => x_coa_id,
80 flex_qual_name => 'GL_BALANCING',
81 segment_number => company_seg_num)
82 ) THEN
83 app_exception.raise_exception;
84 END IF;
85 END IF;
86
87 -- Get the description
88 IF (fnd_flex_keyval.validate_segs(
89 operation => 'CHECK_SEGMENTS',
90 appl_short_name => 'SQLGL',
91 key_flex_code => 'GL#',
92 structure_number => x_coa_id,
93 concat_segments => x_company_val,
94 displayable => 'GL_BALANCING',
95 allow_nulls => TRUE,
96 allow_orphans => TRUE)) THEN
97 null;
98 END IF;
99
100 RETURN(fnd_flex_keyval.segment_description(company_seg_num));
101 END get_company_description;
102
103
104 --
105 -- Procedure
106 -- Check_unique_name
107 -- Purpose
108 -- Unique check for name
109 -- History
110 -- 05-Nov-98 W Wong Created
111 -- 31-OCT-02 J Huang sobid-->ledgerid
112 -- Parameters
113 -- x_rowid Rowid
114 -- x_ledgerid Ledger ID
115 -- x_name Name of elimination set
116 --
117 -- Notes
118 -- None
119 --
120 PROCEDURE check_unique_name(X_rowid VARCHAR2,
121 X_ledgerid NUMBER,
122 X_name VARCHAR2) IS
123 counter NUMBER;
124
125 CURSOR name_count IS
126 SELECT 1
127 FROM DUAL
128 WHERE EXISTS (SELECT 1
129 FROM gl_elimination_sets
130 WHERE name = X_name
131 AND ledger_id = X_ledgerid
132 AND ((X_rowid IS NULL) OR (rowid <> X_rowid)));
133 BEGIN
134
135 OPEN name_count;
136 FETCH name_count INTO counter;
137
138 IF name_count%FOUND THEN
139 CLOSE name_count;
140 FND_MESSAGE.set_name('SQLGL', 'GL_DUPLICATE_NAME');
141 APP_EXCEPTION.raise_exception;
142
143 ELSE
144 CLOSE name_count;
145 END IF;
146
147 EXCEPTION
148 WHEN app_exceptions.application_exception THEN
149 RAISE;
150 WHEN OTHERS THEN
151 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
152 fnd_message.set_token(
153 'PROCEDURE',
154 'gl_elimination_sets_pkg.check_unique_name');
155 RAISE;
156 END check_unique_name;
157
158 --
159 -- Function
160 -- Allow_delete_record
161 -- Purpose
162 -- Check if we can allow deletion of the record.
163 -- Deletion is not allowed if an elimination set is marked for tracking
164 -- and it has generated at least once.
165 -- History
166 -- 05-Nov-98 W Wong Created
167 -- Parameters
168 -- x_setid Elimination Set ID
169 --
170 -- Notes
171 -- None
172 --
173 FUNCTION allow_delete_record( X_setid NUMBER ) RETURN BOOLEAN IS
174
175 counter NUMBER;
176
177 CURSOR set_count IS
178 SELECT 1
179 FROM DUAL
180 WHERE EXISTS (SELECT 1
181 FROM gl_elimination_sets
182 WHERE elimination_set_id = X_setid
183 AND track_elimination_status_flag = 'Y'
184 AND last_executed_period IS NOT NULL);
185 BEGIN
186
187 OPEN set_count;
188 FETCH set_count INTO counter;
189
190 IF set_count%FOUND THEN
191 CLOSE set_count;
192 return( FALSE );
193
194 ELSE
195 CLOSE set_count;
196 return( TRUE );
197 END IF;
198
199 EXCEPTION
200 WHEN app_exceptions.application_exception THEN
201 RAISE;
202 WHEN OTHERS THEN
203 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
204 fnd_message.set_token(
205 'PROCEDURE',
206 'gl_elimination_sets_pkg.allow_delete_record');
207 RAISE;
208 END allow_delete_record;
209
210 --
211 -- Procedure
212 -- lock_row
213 -- Purpose
214 -- Locks a row in GL_ELIMINATION_SETS table.
215 -- History
216 -- 10-SEP-03 P Sahay Created (For Definition Access Set Project)
217 -- Parameters
218 -- All the columns of GL_ELIMINATION_SETS table
219 -- (except WHO columns)
220 --
221 -- Notes
222 -- None
223 --
224 PROCEDURE lock_row (X_Rowid IN OUT NOCOPY VARCHAR2,
225 X_Elimination_Set_Id IN OUT NOCOPY NUMBER,
226 X_Name VARCHAR2,
227 X_Ledger_Id NUMBER,
228 X_Track_Elimination_Status VARCHAR2,
229 X_Start_Date_Active DATE,
230 X_End_Date_Active DATE,
231 X_Elimination_Company VARCHAR2,
232 X_Last_Executed_Period VARCHAR2,
233 X_Description VARCHAR2,
234 X_Attribute1 VARCHAR2,
235 X_Attribute2 VARCHAR2,
236 X_Attribute3 VARCHAR2,
237 X_Attribute4 VARCHAR2,
238 X_Attribute5 VARCHAR2,
239 X_Attribute6 VARCHAR2,
240 X_Attribute7 VARCHAR2,
241 X_Attribute8 VARCHAR2,
242 X_Attribute9 VARCHAR2,
243 X_Attribute10 VARCHAR2,
244 X_Attribute11 VARCHAR2,
245 X_Attribute12 VARCHAR2,
246 X_Attribute13 VARCHAR2,
247 X_Attribute14 VARCHAR2,
248 X_Attribute15 VARCHAR2,
249 X_Context VARCHAR2,
250 X_Security_Flag VARCHAR2) IS
251 CURSOR C IS SELECT
252 elimination_set_id,
253 name,
254 ledger_id,
255 track_elimination_status_flag,
256 start_date_active,
257 end_date_active,
258 elimination_company,
259 last_executed_period,
260 description,
261 attribute1,
262 attribute2,
263 attribute3,
264 attribute4,
265 attribute5,
266 attribute6,
267 attribute7,
268 attribute8,
269 attribute9,
270 attribute10,
271 attribute11,
272 attribute12,
273 attribute13,
274 attribute14,
275 attribute15,
276 context,
277 security_flag
278 FROM GL_ELIMINATION_SETS
279 WHERE ROWID = X_Rowid
280 FOR UPDATE OF elimination_set_id NOWAIT;
281 recinfo C%ROWTYPE;
282
283 BEGIN
284 OPEN C;
285 FETCH C INTO recinfo;
286 IF (C%NOTFOUND) THEN
287 CLOSE C;
288 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
289 app_exception.raise_exception;
290 END IF;
291 CLOSE C;
292
293 IF (
294 (recinfo.elimination_set_id = x_elimination_set_id)
295 AND (recinfo.name = x_name)
296 AND (recinfo.ledger_id = x_ledger_id)
297 AND (recinfo.track_elimination_status_flag = x_track_elimination_status)
298 AND (recinfo.security_flag = x_security_flag)
299
300 AND ((recinfo.start_date_active = x_start_date_active)
301 OR ((recinfo.start_date_active is null)
302 AND (x_start_date_active is null)))
303
304 AND ((recinfo.end_date_active = x_end_date_active)
305 OR ((recinfo.end_date_active is null)
306 AND (x_end_date_active is null)))
307
308 AND ((recinfo.elimination_company = x_elimination_company)
309 OR ((recinfo.elimination_company is null)
310 AND (x_elimination_company is null)))
311
312 AND ((recinfo.last_executed_period = x_last_executed_period)
313 OR ((recinfo.last_executed_period is null)
314 AND (x_last_executed_period is null)))
315
316 AND ((recinfo.description = x_description)
317 OR ((recinfo.description is null)
318 AND (x_description is null)))
319
320 AND ((recinfo.context = x_context)
321 OR ((recinfo.context is null)
322 AND (x_context is null)))
323
324 AND ((recinfo.attribute1 = x_attribute1)
325 OR ((recinfo.attribute1 is null)
326 AND (x_attribute1 is null)))
327
328 AND ((recinfo.attribute2 = x_attribute2)
329 OR ((recinfo.attribute2 is null)
330 AND (x_attribute2 is null)))
331
332 AND ((recinfo.attribute3 = x_attribute3)
333 OR ((recinfo.attribute3 is null)
334 AND (x_attribute3 is null)))
335
336 AND ((recinfo.attribute4 = x_attribute4)
337 OR ((recinfo.attribute4 is null)
338 AND (x_attribute4 is null)))
339
340 AND ((recinfo.attribute5 = x_attribute5)
341 OR ((recinfo.attribute5 is null)
342 AND (x_attribute5 is null)))
343
344 AND ((recinfo.attribute6 = x_attribute6)
345 OR ((recinfo.attribute6 is null)
346 AND (x_attribute6 is null)))
347
348 AND ((recinfo.attribute7 = x_attribute7)
349 OR ((recinfo.attribute7 is null)
350 AND (x_attribute7 is null)))
351
352 AND ((recinfo.attribute8 = x_attribute8)
353 OR ((recinfo.attribute8 is null)
354 AND (x_attribute8 is null)))
355
356 AND ((recinfo.attribute9 = x_attribute9)
357 OR ((recinfo.attribute9 is null)
358 AND (x_attribute9 is null)))
359
360 AND ((recinfo.attribute10 = x_attribute10)
361 OR ((recinfo.attribute10 is null)
362 AND (x_attribute10 is null)))
363
364 AND ((recinfo.attribute11 = x_attribute11)
365 OR ((recinfo.attribute11 is null)
366 AND (x_attribute11 is null)))
367
368 AND ((recinfo.attribute12 = x_attribute12)
369 OR ((recinfo.attribute12 is null)
370 AND (x_attribute12 is null)))
371
372 AND ((recinfo.attribute13 = x_attribute13)
373 OR ((recinfo.attribute13 is null)
374 AND (x_attribute13 is null)))
375
376 AND ((recinfo.attribute14 = x_attribute14)
377 OR ((recinfo.attribute14 is null)
378 AND (x_attribute14 is null)))
379
380 AND ((recinfo.attribute15 = x_attribute15)
381 OR ((recinfo.attribute15 is null)
382 AND (x_attribute15 is null)))
383 ) THEN
384 return;
385 ELSE
386 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
387 app_exception.raise_exception;
388 END IF;
389
390 END lock_row;
391
392 End gl_elimination_sets_pkg;
[Home] [Help]