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