[Home] [Help]
PACKAGE BODY: APPS.GL_TRANSACTION_CALENDAR_PKG
Source
1 PACKAGE BODY GL_TRANSACTION_CALENDAR_PKG AS
2 /* $Header: glitrclb.pls 120.5 2003/12/05 18:56:24 cma ship $ */
3
4 --
5 -- PUBLIC FUNCTIONS
6 --
7
8 PROCEDURE check_unique(x_name VARCHAR2, row_id VARCHAR2) IS
9 CURSOR chk_duplicates is
10 SELECT 'Duplicate'
11 FROM GL_TRANSACTION_CALENDAR gps
12 WHERE gps.name = x_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_transaction_calendar_pkg.check_unique');
34 RAISE;
35 END check_unique;
36
37 /* Added for Definition Access Sets Project */
38 PROCEDURE lock_row
39 (X_Rowid IN OUT NOCOPY VARCHAR2,
40 x_transaction_calendar_id NUMBER,
41 x_name VARCHAR2,
42 x_sun_business_day_flag VARCHAR2,
43 x_mon_business_day_flag VARCHAR2,
44 x_tue_business_day_flag VARCHAR2,
45 x_wed_business_day_flag VARCHAR2,
46 x_thu_business_day_flag VARCHAR2,
47 x_fri_business_day_flag VARCHAR2,
48 x_sat_business_day_flag VARCHAR2,
49 x_security_flag VARCHAR2,
50 x_description VARCHAR2,
51 x_context VARCHAR2,
52 x_attribute1 VARCHAR2,
53 x_attribute2 VARCHAR2,
54 x_attribute3 VARCHAR2,
55 x_attribute4 VARCHAR2,
56 x_attribute5 VARCHAR2,
57 x_attribute6 VARCHAR2,
58 x_attribute7 VARCHAR2,
59 x_attribute8 VARCHAR2,
60 x_attribute9 VARCHAR2,
61 x_attribute10 VARCHAR2,
62 x_attribute11 VARCHAR2,
63 x_attribute12 VARCHAR2,
64 x_attribute13 VARCHAR2,
65 x_attribute14 VARCHAR2,
66 x_attribute15 VARCHAR2
67 ) IS
68 CURSOR C IS SELECT
69 transaction_calendar_id,
70 name,
71 sun_business_day_flag,
72 mon_business_day_flag,
73 tue_business_day_flag,
74 wed_business_day_flag,
75 thu_business_day_flag,
76 fri_business_day_flag,
77 sat_business_day_flag,
78 security_flag,
79 description,
80 context,
81 attribute1,
82 attribute2,
83 attribute3,
84 attribute4,
85 attribute5,
86 attribute6,
87 attribute7,
88 attribute8,
89 attribute9,
90 attribute10,
91 attribute11,
92 attribute12,
93 attribute13,
94 attribute14,
95 attribute15
96 FROM gl_transaction_calendar
97 WHERE ROWID = X_Rowid
98 FOR UPDATE OF transaction_calendar_id NOWAIT;
99 recinfo C%ROWTYPE;
100
101 BEGIN
102 OPEN C;
103 FETCH C INTO recinfo;
104 IF (C%NOTFOUND) THEN
105 CLOSE C;
106 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
107 app_exception.raise_exception;
108 END IF;
109 CLOSE C;
110
111 IF (
112 (recinfo.transaction_calendar_id = x_transaction_calendar_id)
113 AND (recinfo.name = x_name)
114 AND (recinfo.sun_business_day_flag = x_sun_business_day_flag)
115 AND (recinfo.mon_business_day_flag = x_mon_business_day_flag)
116 AND (recinfo.tue_business_day_flag = x_tue_business_day_flag)
117 AND (recinfo.wed_business_day_flag = x_wed_business_day_flag)
118 AND (recinfo.thu_business_day_flag = x_thu_business_day_flag)
119 AND (recinfo.fri_business_day_flag = x_fri_business_day_flag)
120 AND (recinfo.sat_business_day_flag = x_sat_business_day_flag)
121 AND (recinfo.security_flag = x_security_flag)
122
123
124 AND ((recinfo.description = x_description)
125 OR ((recinfo.description is null)
126 AND (x_description is null)))
127
128 AND ((recinfo.context = x_context)
129 OR ((recinfo.context is null)
130 AND (x_context is null)))
131
132 AND ((recinfo.attribute1 = x_attribute1)
133 OR ((recinfo.attribute1 is null)
134 AND (x_attribute1 is null)))
135
136 AND ((recinfo.attribute2 = x_attribute2)
137 OR ((recinfo.attribute2 is null)
138 AND (x_attribute2 is null)))
139
140 AND ((recinfo.attribute3 = x_attribute3)
141 OR ((recinfo.attribute3 is null)
142 AND (x_attribute3 is null)))
143
144 AND ((recinfo.attribute4 = x_attribute4)
145 OR ((recinfo.attribute4 is null)
146 AND (x_attribute4 is null)))
147
148 AND ((recinfo.attribute5 = x_attribute5)
149 OR ((recinfo.attribute5 is null)
150 AND (x_attribute5 is null)))
151
152 AND ((recinfo.attribute6 = x_attribute6)
153 OR ((recinfo.attribute6 is null)
154 AND (x_attribute6 is null)))
155
156 AND ((recinfo.attribute7 = x_attribute7)
157 OR ((recinfo.attribute7 is null)
158 AND (x_attribute7 is null)))
159
160 AND ((recinfo.attribute8 = x_attribute8)
161 OR ((recinfo.attribute8 is null)
162 AND (x_attribute8 is null)))
163
164 AND ((recinfo.attribute9 = x_attribute9)
165 OR ((recinfo.attribute9 is null)
166 AND (x_attribute9 is null)))
167
168 AND ((recinfo.attribute10 = x_attribute10)
169 OR ((recinfo.attribute10 is null)
170 AND (x_attribute10 is null)))
171
172 AND ((recinfo.attribute11 = x_attribute11)
173 OR ((recinfo.attribute11 is null)
174 AND (x_attribute11 is null)))
175
176 AND ((recinfo.attribute12 = x_attribute12)
177 OR ((recinfo.attribute12 is null)
178 AND (x_attribute12 is null)))
179
180 AND ((recinfo.attribute13 = x_attribute13)
181 OR ((recinfo.attribute13 is null)
182 AND (x_attribute13 is null)))
183
184 AND ((recinfo.attribute14 = x_attribute14)
185 OR ((recinfo.attribute14 is null)
186 AND (x_attribute14 is null)))
187
188 AND ((recinfo.attribute15 = x_attribute15)
189 OR ((recinfo.attribute15 is null)
190 AND (x_attribute15 is null)))
191 ) THEN
192 return;
193 ELSE
194 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
195 app_exception.raise_exception;
196 END IF;
197
198 END lock_row;
199
200 /* Added x_security_flag for Definition Access Sets Project */
201 PROCEDURE insert_row
202 (X_Rowid IN OUT NOCOPY VARCHAR2,
203 x_transaction_calendar_id IN OUT NOCOPY NUMBER,
204 x_name VARCHAR2,
205 x_sun_business_day_flag VARCHAR2,
206 x_mon_business_day_flag VARCHAR2,
207 x_tue_business_day_flag VARCHAR2,
208 x_wed_business_day_flag VARCHAR2,
209 x_thu_business_day_flag VARCHAR2,
210 x_fri_business_day_flag VARCHAR2,
211 x_sat_business_day_flag VARCHAR2,
212 x_security_flag VARCHAR2,
213 x_creation_date DATE,
214 x_created_by NUMBER,
215 x_last_update_date DATE,
216 x_last_updated_by NUMBER,
217 x_last_update_login NUMBER,
218 x_description VARCHAR2,
219 x_context VARCHAR2,
220 x_attribute1 VARCHAR2,
221 x_attribute2 VARCHAR2,
222 x_attribute3 VARCHAR2,
223 x_attribute4 VARCHAR2,
224 x_attribute5 VARCHAR2,
225 x_attribute6 VARCHAR2,
226 x_attribute7 VARCHAR2,
227 x_attribute8 VARCHAR2,
228 x_attribute9 VARCHAR2,
229 x_attribute10 VARCHAR2,
230 x_attribute11 VARCHAR2,
231 x_attribute12 VARCHAR2,
232 x_attribute13 VARCHAR2,
233 x_attribute14 VARCHAR2,
234 x_attribute15 VARCHAR2
235 ) IS
236 CURSOR C_ROWID IS SELECT rowid FROM gl_transaction_calendar
237 WHERE transaction_calendar_id = x_transaction_calendar_id;
238
239 BEGIN
240 -- insert the record
241 INSERT INTO gl_transaction_calendar
242 (
243 transaction_calendar_id,
244 name,
245 sun_business_day_flag,
246 mon_business_day_flag,
247 tue_business_day_flag,
248 wed_business_day_flag,
249 thu_business_day_flag,
250 fri_business_day_flag,
251 sat_business_day_flag,
252 security_flag,
253 creation_date,
254 created_by,
255 last_update_date,
256 last_updated_by,
257 last_update_login,
258 description,
259 context,
260 attribute1,
261 attribute2,
262 attribute3,
263 attribute4,
264 attribute5,
265 attribute6,
266 attribute7,
267 attribute8,
268 attribute9,
269 attribute10,
270 attribute11,
271 attribute12,
272 attribute13,
273 attribute14,
274 attribute15
275 )
276 VALUES
277 (
278 x_transaction_calendar_id,
279 x_name,
280 x_sun_business_day_flag,
281 x_mon_business_day_flag,
282 x_tue_business_day_flag,
283 x_wed_business_day_flag,
284 x_thu_business_day_flag,
285 x_fri_business_day_flag,
286 x_sat_business_day_flag,
287 x_security_flag,
288 x_creation_date,
289 x_created_by,
290 x_last_update_date,
291 x_last_updated_by,
292 x_last_update_login,
293 x_description,
294 x_context,
295 x_attribute1,
296 x_attribute2,
297 x_attribute3,
298 x_attribute4,
299 x_attribute5,
300 x_attribute6,
301 x_attribute7,
302 x_attribute8,
303 x_attribute9,
304 x_attribute10,
305 x_attribute11,
306 x_attribute12,
307 x_attribute13,
308 x_attribute14,
309 x_attribute15
310 );
311
312 -- get rowid to return it back
313 OPEN C_ROWID;
314 FETCH C_ROWID INTO X_Rowid;
315 IF (C_ROWID%NOTFOUND) THEN
316 CLOSE C_ROWID;
317 Raise NO_DATA_FOUND;
318 END IF;
319 CLOSE C_ROWID;
320
321 END insert_row;
322
323 PROCEDURE Delete_Row(x_transaction_calendar_id NUMBER) IS
324 CURSOR check_ledger IS
325 SELECT '1'
326 FROM dual
327 WHERE EXISTS
328 (SELECT 'Used by ledger'
329 FROM GL_LEDGERS
330 WHERE
331 transaction_calendar_id = x_transaction_calendar_id
332 );
333 dummy VARCHAR2(1000);
334 BEGIN
335 -- make sure that this transaction calendar is not being used by any
336 -- of the ledgers
337 OPEN check_ledger;
338 FETCH check_ledger INTO dummy;
339 IF (check_ledger%NOTFOUND) THEN
340 CLOSE check_ledger;
341 ELSE
342 -- it is being used by some ledger, exit
343 CLOSE check_ledger;
344 fnd_message.set_name('SQLGL', 'GL_AB_TR_CAL_IS_USED');
345 app_exception.raise_exception;
346 END IF;
347
348 -- delete from gl_transaction_calendar
349 DELETE FROM GL_TRANSACTION_CALENDAR
350 WHERE transaction_calendar_id = x_transaction_calendar_id;
351
352 -- delete from gl_transaction_dates
353 DELETE FROM GL_TRANSACTION_DATES
354 WHERE transaction_calendar_id = x_transaction_calendar_id;
355
356 END Delete_Row;
357
358 PROCEDURE check_calendar(x_transaction_calendar_id NUMBER) IS
359 CURSOR check_ledger IS
360 SELECT '1'
361 FROM dual
362 WHERE EXISTS
363 (SELECT 'Used by ledger'
364 FROM GL_LEDGERS
365 WHERE
366 transaction_calendar_id = x_transaction_calendar_id
367 );
368 dummy VARCHAR2(1000);
369 BEGIN
370 -- make sure that this transaction calendar is not being used by any
371 -- of the ledgers
372 OPEN check_ledger;
373 FETCH check_ledger INTO dummy;
374 IF (check_ledger%NOTFOUND) THEN
375 CLOSE check_ledger;
376 ELSE
377 -- it is being used by some ledger, exit
378 CLOSE check_ledger;
379 fnd_message.set_name('SQLGL', 'GL_AB_TR_CAL_IS_USED');
380 app_exception.raise_exception;
381 END IF;
382
383 END check_calendar;
384 END GL_TRANSACTION_CALENDAR_PKG;