[Home] [Help]
PACKAGE BODY: APPS.GL_AUTOPOST_SETS_PKG
Source
1 PACKAGE BODY gl_autopost_sets_pkg AS
2 /* $Header: glistasb.pls 120.4 2005/05/05 01:22:28 kvora ship $ */
3
4 --
5 -- PUBLIC FUNCTIONS
6 --
7
8 PROCEDURE check_unique(x_autopost_set_name VARCHAR2,
9 x_chart_of_accounts_id NUMBER,
10 x_period_set_name VARCHAR2,
11 x_accounted_period_type VARCHAR2,
12 row_id VARCHAR2) IS
13 CURSOR chk_duplicates is
14 SELECT 'Duplicate'
15 FROM GL_AUTOMATIC_POSTING_SETS sets
16 WHERE sets.autopost_set_name = x_autopost_set_name
17 AND sets.chart_of_accounts_id = x_Chart_Of_Accounts_Id
18 AND sets.period_set_name = x_Period_Set_Name
19 AND sets.accounted_period_type = x_Accounted_Period_Type
20 AND ( row_id is null
21 OR sets.rowid <> row_id);
22 dummy VARCHAR2(10);
23 BEGIN
24 OPEN chk_duplicates;
25 FETCH chk_duplicates INTO dummy;
26
27 IF chk_duplicates%FOUND THEN
28 CLOSE chk_duplicates;
29 fnd_message.set_name('SQLGL', 'GL_DUPLICATE_AUTOPOST_SETS');
30 app_exception.raise_exception;
31 END IF;
32
33 CLOSE chk_duplicates;
34
35 EXCEPTION
36 WHEN app_exceptions.application_exception THEN
37 RAISE;
38 WHEN OTHERS THEN
39 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
40 fnd_message.set_token('PROCEDURE',
41 'gl_autopost_sets_pkg.check_unique');
42 RAISE;
43 END check_unique;
44
45 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
46 X_Autopost_Set_Id IN OUT NOCOPY NUMBER,
47 X_Autopost_Set_Name VARCHAR2,
48 X_Chart_Of_Accounts_Id NUMBER,
49 X_Period_Set_Name VARCHAR2,
50 X_Accounted_Period_Type VARCHAR2,
51 X_Enabled_Flag VARCHAR2,
52 X_Security_Flag VARCHAR2,
53 X_Submit_All_Priorities_Flag VARCHAR2,
54 X_Last_Update_Date DATE,
55 X_Last_Updated_By NUMBER,
56 X_Creation_Date DATE,
57 X_Created_By NUMBER,
58 X_Last_Update_Login NUMBER,
59 X_description VARCHAR2,
60 X_num_of_priority_options NUMBER,
61 X_effective_days_before NUMBER,
62 X_effective_days_after NUMBER,
63 X_Attribute1 VARCHAR2,
64 X_Attribute2 VARCHAR2,
65 X_Attribute3 VARCHAR2,
66 X_Attribute4 VARCHAR2,
67 X_Attribute5 VARCHAR2,
68 X_Attribute6 VARCHAR2,
69 X_Attribute7 VARCHAR2,
70 X_Attribute8 VARCHAR2,
71 X_Attribute9 VARCHAR2,
72 X_Attribute10 VARCHAR2,
73 X_Attribute11 VARCHAR2,
74 X_Attribute12 VARCHAR2,
75 X_Attribute13 VARCHAR2,
76 X_Attribute14 VARCHAR2,
77 X_Attribute15 VARCHAR2,
78 X_Context VARCHAR2
79 ) IS
80 CURSOR C IS SELECT rowid FROM GL_AUTOMATIC_POSTING_SETS
81 WHERE autopost_set_id = X_autopost_set_id
82 AND autopost_set_name = X_autopost_set_name;
83
84 CURSOR C2 IS SELECT gl_automatic_posting_sets_s.nextval FROM DUAL;
85
86
87 BEGIN
88
89 if (X_autopost_set_id IS NULL) then
90 OPEN C2;
91 FETCH C2 INTO X_autopost_set_id;
92 CLOSE C2;
93 end if;
94 INSERT INTO GL_AUTOMATIC_POSTING_SETS(
95 autopost_set_id,
96 autopost_set_name,
97 chart_of_accounts_id,
98 period_set_name,
99 accounted_period_type,
100 enabled_flag,
101 security_flag,
102 submit_all_priorities_flag,
103 last_update_date,
104 last_updated_by,
105 creation_date,
106 created_by,
107 last_update_login,
108 description,
109 num_of_priority_options,
110 effective_days_before,
111 effective_days_after,
112 attribute1,
113 attribute2,
114 attribute3,
115 attribute4,
116 attribute5,
117 attribute6,
118 attribute7,
119 attribute8,
120 attribute9,
121 attribute10,
122 attribute11,
123 attribute12,
124 attribute13,
125 attribute14,
126 attribute15,
127 context
128 ) VALUES (
129 X_Autopost_Set_Id,
130 X_Autopost_Set_Name,
131 X_Chart_Of_Accounts_Id,
132 X_Period_Set_Name,
133 X_Accounted_Period_Type,
134 X_Enabled_Flag,
135 X_Security_Flag,
136 X_Submit_All_Priorities_Flag,
137 X_Last_Update_Date,
138 X_Last_Updated_By,
139 X_Creation_Date,
140 X_Created_By,
141 X_Last_Update_Login,
142 X_Description,
143 X_Num_Of_Priority_Options,
144 X_Effective_Days_Before,
145 X_Effective_Days_After,
146 X_Attribute1,
147 X_Attribute2,
148 X_Attribute3,
149 X_Attribute4,
150 X_Attribute5,
151 X_Attribute6,
152 X_Attribute7,
153 X_Attribute8,
154 X_Attribute9,
155 X_Attribute10,
156 X_Attribute11,
157 X_Attribute12,
158 X_Attribute13,
159 X_Attribute14,
160 X_Attribute15,
161 X_Context
162
163 );
164
165 OPEN C;
166 FETCH C INTO X_Rowid;
167 if (C%NOTFOUND) then
168 CLOSE C;
169 RAISE NO_DATA_FOUND;
170 end if;
171 CLOSE C;
172 END Insert_Row;
173
174 PROCEDURE Lock_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
175 X_Autopost_Set_Id IN OUT NOCOPY NUMBER,
176 X_Autopost_Set_Name VARCHAR2,
177 X_Chart_Of_Accounts_Id NUMBER,
178 X_Period_Set_Name VARCHAR2,
179 X_Accounted_Period_Type VARCHAR2,
180 X_Enabled_Flag VARCHAR2,
181 X_Security_Flag VARCHAR2,
182 X_Submit_All_Priorities_Flag VARCHAR2,
183 X_description VARCHAR2,
184 X_Num_Of_Priority_Options NUMBER,
185 X_Effective_Days_Before NUMBER,
186 X_Effective_Days_After NUMBER,
187 X_Attribute1 VARCHAR2,
188 X_Attribute2 VARCHAR2,
189 X_Attribute3 VARCHAR2,
190 X_Attribute4 VARCHAR2,
191 X_Attribute5 VARCHAR2,
192 X_Attribute6 VARCHAR2,
193 X_Attribute7 VARCHAR2,
194 X_Attribute8 VARCHAR2,
195 X_Attribute9 VARCHAR2,
196 X_Attribute10 VARCHAR2,
197 X_Attribute11 VARCHAR2,
198 X_Attribute12 VARCHAR2,
199 X_Attribute13 VARCHAR2,
200 X_Attribute14 VARCHAR2,
201 X_Attribute15 VARCHAR2,
202 X_Context VARCHAR2
203 ) IS
204 CURSOR C IS
205 SELECT *
206 FROM GL_AUTOMATIC_POSTING_SETS
207 WHERE rowid = X_Rowid
208 FOR UPDATE of autopost_set_id NOWAIT;
209 Recinfo C%ROWTYPE;
210 BEGIN
211 OPEN C;
212 FETCH C INTO Recinfo;
213 if (C%NOTFOUND) then
214 CLOSE C;
215 RAISE NO_DATA_FOUND;
216 end if;
217 CLOSE C;
218 if (
219 ( (Recinfo.autopost_set_id = X_autopost_set_id)
220 OR ( (Recinfo.autopost_set_id IS NULL)
221 AND (X_autopost_set_id IS NULL)))
222 AND ( (Recinfo.autopost_set_name = X_autopost_set_name)
223 OR ( (Recinfo.autopost_set_name IS NULL)
224 AND (X_autopost_set_name IS NULL)))
225 AND ( (Recinfo.chart_of_accounts_id = X_chart_of_accounts_id)
226 OR ( (Recinfo.chart_of_accounts_id IS NULL)
227 AND (X_chart_of_accounts_id IS NULL)))
228 AND ( (Recinfo.period_set_name = X_period_set_name)
229 OR ( (Recinfo.period_set_name IS NULL)
230 AND (X_period_set_name IS NULL)))
231 AND ( (Recinfo.accounted_period_type = X_accounted_period_type)
232 OR ( (Recinfo.accounted_period_type IS NULL)
233 AND (X_accounted_period_type IS NULL)))
234 AND ( (Recinfo.enabled_flag = X_enabled_flag)
235 OR ( (Recinfo.enabled_flag IS NULL)
236 AND (X_enabled_flag IS NULL)))
237 AND ( (Recinfo.security_flag = X_security_flag)
238 OR ( (Recinfo.security_flag IS NULL)
239 AND (X_security_flag IS NULL)))
240 AND ( (Recinfo.submit_all_priorities_flag = X_submit_all_priorities_flag)
241 OR ( (Recinfo.submit_all_priorities_flag IS NULL)
242 AND (X_submit_all_priorities_flag IS NULL)))
243 AND ( (Recinfo.description = X_Description)
244 OR ( (Recinfo.description IS NULL)
245 AND (X_Description IS NULL)))
246 AND ( (Recinfo.num_of_priority_options = X_Num_Of_Priority_Options)
247 OR ( (Recinfo.num_of_priority_options IS NULL)
248 AND (X_Num_Of_Priority_Options IS NULL)))
249 AND ( (Recinfo.effective_days_before = X_Effective_Days_Before)
250 OR ( (Recinfo.effective_days_before IS NULL)
251 AND (X_Effective_Days_Before IS NULL)))
252 AND ( (Recinfo.effective_days_after = X_Effective_Days_After)
253 OR ( (Recinfo.effective_days_after IS NULL)
254 AND (X_Effective_Days_After IS NULL)))
255 AND ( (Recinfo.attribute1 = X_Attribute1)
256 OR ( (Recinfo.attribute1 IS NULL)
257 AND (X_Attribute1 IS NULL)))
258 AND ( (Recinfo.attribute2 = X_Attribute2)
259 OR ( (Recinfo.attribute2 IS NULL)
260 AND (X_Attribute2 IS NULL)))
261 AND ( (Recinfo.attribute3 = X_Attribute3)
262 OR ( (Recinfo.attribute3 IS NULL)
263 AND (X_Attribute3 IS NULL)))
264 AND ( (Recinfo.attribute4 = X_Attribute4)
265 OR ( (Recinfo.attribute4 IS NULL)
266 AND (X_Attribute4 IS NULL)))
267 AND ( (Recinfo.attribute5 = X_Attribute5)
268 OR ( (Recinfo.attribute5 IS NULL)
269 AND (X_Attribute5 IS NULL)))
270 AND ( (Recinfo.attribute6 = X_Attribute6)
271 OR ( (Recinfo.attribute6 IS NULL)
272 AND (X_Attribute6 IS NULL)))
273 AND ( (Recinfo.attribute7 = X_Attribute7)
274 OR ( (Recinfo.attribute7 IS NULL)
275 AND (X_Attribute7 IS NULL)))
276 AND ( (Recinfo.attribute8 = X_Attribute8)
277 OR ( (Recinfo.attribute8 IS NULL)
278 AND (X_Attribute8 IS NULL)))
279 AND ( (Recinfo.attribute9 = X_Attribute9)
280 OR ( (Recinfo.attribute9 IS NULL)
281 AND (X_Attribute9 IS NULL)))
282 AND ( (Recinfo.attribute10 = X_Attribute10)
283 OR ( (Recinfo.attribute10 IS NULL)
284 AND (X_Attribute10 IS NULL)))
285 AND ( (Recinfo.attribute11 = X_Attribute11)
286 OR ( (Recinfo.attribute11 IS NULL)
287 AND (X_Attribute11 IS NULL)))
288 AND ( (Recinfo.attribute12 = X_Attribute12)
289 OR ( (Recinfo.attribute12 IS NULL)
290 AND (X_Attribute12 IS NULL)))
291 AND ( (Recinfo.attribute13 = X_Attribute13)
292 OR ( (Recinfo.attribute13 IS NULL)
293 AND (X_Attribute13 IS NULL)))
294 AND ( (Recinfo.attribute14 = X_Attribute14)
295 OR ( (Recinfo.attribute14 IS NULL)
296 AND (X_Attribute14 IS NULL)))
297 AND ( (Recinfo.attribute15 = X_Attribute15)
298 OR ( (Recinfo.attribute15 IS NULL)
299 AND (X_Attribute15 IS NULL)))
300 AND ( (Recinfo.context = X_Context)
301 OR ( (Recinfo.context IS NULL)
302 AND (X_Context IS NULL)))
303 ) then
304 return;
305 else
306 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
307 APP_EXCEPTION.RAISE_EXCEPTION;
308 end if;
309 END Lock_Row;
310
311 PROCEDURE Update_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
312 X_Autopost_Set_ID IN OUT NOCOPY NUMBER,
313 X_Autopost_Set_Name VARCHAR2,
314 X_Chart_Of_Accounts_Id NUMBER,
315 X_Period_Set_Name VARCHAR2,
316 X_Accounted_Period_Type VARCHAR2,
317 X_Enabled_Flag VARCHAR2,
318 X_Security_Flag VARCHAR2,
319 X_Submit_All_Priorities_Flag VARCHAR2,
320 X_Last_Update_Date DATE,
321 X_Last_Updated_By NUMBER,
322 X_Last_Update_Login NUMBER,
323 X_Creation_Date DATE,
324 X_Created_By NUMBER,
325 X_Description VARCHAR2,
326 X_Num_Of_Priority_Options NUMBER,
327 X_Effective_Days_Before NUMBER,
328 X_Effective_Days_After NUMBER,
329 X_Attribute1 VARCHAR2,
330 X_Attribute2 VARCHAR2,
331 X_Attribute3 VARCHAR2,
332 X_Attribute4 VARCHAR2,
333 X_Attribute5 VARCHAR2,
334 X_Attribute6 VARCHAR2,
335 X_Attribute7 VARCHAR2,
336 X_Attribute8 VARCHAR2,
337 X_Attribute9 VARCHAR2,
338 X_Attribute10 VARCHAR2,
339 X_Attribute11 VARCHAR2,
340 X_Attribute12 VARCHAR2,
341 X_Attribute13 VARCHAR2,
342 X_Attribute14 VARCHAR2,
343 X_Attribute15 VARCHAR2,
344 X_Context VARCHAR2
345 ) IS
346 BEGIN
347 UPDATE GL_AUTOMATIC_POSTING_SETS
348 SET
349
350 autopost_set_id = X_Autopost_Set_Id,
351 autopost_set_name = X_autopost_set_name,
352 chart_of_accounts_id = X_Chart_Of_Accounts_Id,
353 period_set_name = X_Period_Set_Name,
354 accounted_period_type = X_Accounted_Period_Type,
355 enabled_flag = X_enabled_flag,
356 security_flag = X_Security_Flag,
357 submit_all_priorities_flag = X_submit_all_priorities_flag,
358 last_update_date = X_Last_Update_Date,
359 last_updated_by = X_Last_Updated_By,
360 last_update_login = X_Last_Update_Login,
361 creation_date = X_Creation_Date,
362 created_by = X_Created_By,
363 description = X_Description,
364 num_of_priority_options = X_Num_Of_Priority_Options,
365 effective_days_before = X_effective_days_before,
366 effective_days_after = X_effective_days_after,
367 attribute1 = X_Attribute1,
368 attribute2 = X_Attribute2,
369 attribute3 = X_Attribute3,
370 attribute4 = X_Attribute4,
371 attribute5 = X_Attribute5,
372 attribute6 = X_Attribute6,
373 attribute7 = X_Attribute7,
374 attribute8 = X_Attribute8,
375 attribute9 = X_Attribute9,
376 attribute10 = X_Attribute10,
377 attribute11 = X_Attribute11,
378 attribute12 = X_Attribute12,
379 attribute13 = X_Attribute13,
380 attribute14 = X_Attribute14,
381 attribute15 = X_Attribute15,
382 context = X_Context
383 WHERE rowid = X_rowid;
384
385 if (SQL%NOTFOUND) then
386 RAISE NO_DATA_FOUND;
387 end if;
388
389 END Update_Row;
390
391
392 PROCEDURE Delete_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
393 X_Autopost_Set_ID IN OUT NOCOPY NUMBER ) IS
394 BEGIN
395 DELETE FROM GL_AUTOMATIC_POSTING_OPTIONS
396 WHERE autopost_set_id = X_Autopost_Set_Id;
397
398 DELETE FROM GL_AUTOMATIC_POSTING_SETS
399 WHERE rowid = X_Rowid;
400
401 if (SQL%NOTFOUND) then
402 RAISE NO_DATA_FOUND;
403 end if;
404 END Delete_Row;
405
406 FUNCTION submit_request (
407 X_access_set_id NUMBER,
408 X_autopost_set_id NUMBER) RETURN NUMBER IS
409 ret_code NUMBER;
410 BEGIN
411 ret_code := FND_REQUEST.SUBMIT_REQUEST(
412 'SQLGL',
413 'GLPAUTOP',
414 '',
415 '',
416 FALSE,
417 to_char(X_access_set_id),
418 to_char(X_autopost_set_id),
419 chr(0), '', '', '', '',
420 '','','','','','','','','','','','','','','',
421 '','','','','','','','','','','','','','','',
422 '','','','','','','','','','','','','','','',
423 '','','','','','','','','','','','','','','',
424 '','','','','','','','','','','','','','','',
425 '','','','','','','','','','','','','','','',
426 '','','');
427 COMMIT;
428 RETURN (ret_code);
429 END submit_request;
430
431 END gl_autopost_sets_pkg;