1 PACKAGE gl_periods_pkg AUTHID CURRENT_USER AS
2 /* $Header: gliprdes.pls 120.6 2005/05/05 01:17:52 kvora ship $ */
3 --
4 -- Package
5 -- gl_period_sets_pkg
6 -- Purpose
7 -- To contain validation and insertion routines for gl_periods
8 -- History
9 -- 10-07-93 D. J. Ogg Created
10
11 --
12 -- Procedure
13 -- check_unique_num
14 -- Purpose
15 -- Checks to make sure that the period type/year/
16 -- number combination is unique within the current
17 -- calendar.
18 -- History
19 -- 10-04-93 D. J. Ogg Created
20 -- Arguments
21 -- calendar_name Name of the calendar
22 -- containing the period
23 -- period_type Type of the period
24 -- period_year Year containing the period
25 -- period_num Number of the period
26 -- row_id The id of the row
27 -- containing the period
28 -- Example
29 -- periods.check_unique_num('Standard', 'Month', 1993,
30 -- 2, 'AA01');
31 -- Notes
32 --
33 PROCEDURE check_unique_num(calendar_name VARCHAR2,
34 period_type VARCHAR2,
35 period_year NUMBER,
36 period_num NUMBER,
37 row_id VARCHAR2);
38
39 --
40 -- Procedure
41 -- check_unique_name
42 -- Purpose
43 -- Checks to make sure that the period name
44 -- is unique within the current calendar.
45 -- History
46 -- 10-04-93 D. J. Ogg Created
47 -- Arguments
48 -- calendar_name Name of the calendar
49 -- containing the period
50 -- period_name Name of the period
51 -- row_id The id of the row
52 -- containing the period
53 -- Example
54 -- periods.check_unique_name('Standard', 'JAN-91',
55 -- 'AA01');
56 -- Notes
57 --
58 PROCEDURE check_unique_name(calendar_name VARCHAR2,
59 period_name VARCHAR2,
60 row_id VARCHAR2);
61
62 --
63 -- Procedure
64 -- check_period_used
65 -- Purpose
66 -- Returns TRUE if the period has been used, or FALSE
67 -- otherwise. Calling this function always causes a
68 -- check of the database to be done. This is
69 -- different from the period_used function, that only
70 -- does the check the first time it is called for a
71 -- given record.
72 -- History
73 -- 10-04-93 D. J. Ogg Created
74 -- Arguments
75 -- row_id The rowid of the period to be checked
76 -- Example
77 -- options.check_period_used('0121.102')
78 -- Notes
79 --
80 FUNCTION check_period_used(row_id VARCHAR2) RETURN BOOLEAN;
81
82 --
83 -- Procedure
84 -- check_overlapping_period
85 -- Purpose
86 -- Returns TRUE if the given start and end date overlaps with
87 -- some period that is not an adjustment period.
88 -- History
89 -- 24-AUG-94 D. J. Ogg Created
90 -- Arguments
91 -- x_period_set_name The name of the calendar to be checked
92 -- x_period_type The name of the period type to be checked
93 -- x_start_date The start date of the range
94 -- x_end_date The end date of the range
95 -- row_id The rowid of the period with the given
96 -- start and end dates
97 -- Example
98 -- options.check_period_used('0121.102')
99 -- Notes
100 --
101 FUNCTION overlapping_period(x_period_set_name VARCHAR2,
102 x_period_type VARCHAR2,
103 x_start_date DATE,
104 x_end_date DATE,
105 row_id VARCHAR2) RETURN BOOLEAN;
106
107 --
108 -- Procedure
109 -- period_changed
110 -- Purpose
111 -- Returns TRUE if some field of the period has been changed,
112 -- other than the descriptive flexfield or the adjustment period
113 -- flag.
114 -- History
115 -- 12-27-93 D. J. Ogg Created
116 -- Notes
117 --
118 FUNCTION period_changed(
119 X_Rowid VARCHAR2,
120 X_Period_Set_Name VARCHAR2,
121 X_Period_Name VARCHAR2,
122 X_Start_Date DATE,
123 X_End_Date DATE,
124 X_Period_Type VARCHAR2,
125 X_Period_Year NUMBER,
126 X_Period_Num NUMBER,
127 X_Quarter_Num NUMBER,
128 X_Entered_Period_Name VARCHAR2,
129 X_Description VARCHAR2
130 ) RETURN BOOLEAN;
131
132 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
133
134 X_Period_Set_Name VARCHAR2,
135 X_Period_Name VARCHAR2,
136 X_Last_Update_Date DATE,
137 X_Last_Updated_By NUMBER,
138 X_Start_Date DATE,
139 X_End_Date DATE,
140 X_Period_Type VARCHAR2,
141 X_Period_Year NUMBER,
142 X_Period_Num NUMBER,
143 X_Quarter_Num NUMBER,
144 X_Entered_Period_Name VARCHAR2,
145 X_Creation_Date DATE,
146 X_Created_By NUMBER,
147 X_Last_Update_Login NUMBER,
148 X_Description VARCHAR2,
149 X_Attribute1 VARCHAR2,
150 X_Attribute2 VARCHAR2,
151 X_Attribute3 VARCHAR2,
152 X_Attribute4 VARCHAR2,
153 X_Attribute5 VARCHAR2,
154 X_Attribute6 VARCHAR2,
155 X_Attribute7 VARCHAR2,
156 X_Attribute8 VARCHAR2,
157 X_Context VARCHAR2,
158 X_Adjustment_Period_Flag VARCHAR2
159 );
160
161 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
162 X_Period_Set_Name VARCHAR2,
163 X_Period_Name VARCHAR2,
164 X_Start_Date DATE,
165 X_End_Date DATE,
166 X_Period_Type VARCHAR2,
167 X_Period_Year NUMBER,
168 X_Period_Num NUMBER,
169 X_Quarter_Num NUMBER,
170 X_Entered_Period_Name VARCHAR2,
171 X_Description VARCHAR2,
172 X_Attribute1 VARCHAR2,
173 X_Attribute2 VARCHAR2,
174 X_Attribute3 VARCHAR2,
175 X_Attribute4 VARCHAR2,
176 X_Attribute5 VARCHAR2,
177 X_Attribute6 VARCHAR2,
178 X_Attribute7 VARCHAR2,
179 X_Attribute8 VARCHAR2,
180 X_Context VARCHAR2,
181 X_Adjustment_Period_Flag VARCHAR2
182 );
183
184 PROCEDURE Update_Row(X_Rowid VARCHAR2,
185 X_Period_Set_Name VARCHAR2,
186 X_Period_Name VARCHAR2,
187 X_Last_Update_Date DATE,
188 X_Last_Updated_By NUMBER,
189 X_Start_Date DATE,
190 X_End_Date DATE,
191 X_Period_Type VARCHAR2,
192 X_Period_Year NUMBER,
193 X_Period_Num NUMBER,
194 X_Quarter_Num NUMBER,
195 X_Entered_Period_Name VARCHAR2,
196 X_Last_Update_Login NUMBER,
197 X_Description VARCHAR2,
198 X_Attribute1 VARCHAR2,
199 X_Attribute2 VARCHAR2,
200 X_Attribute3 VARCHAR2,
201 X_Attribute4 VARCHAR2,
202 X_Attribute5 VARCHAR2,
203 X_Attribute6 VARCHAR2,
204 X_Attribute7 VARCHAR2,
205 X_Attribute8 VARCHAR2,
206 X_Context VARCHAR2,
207 X_Adjustment_Period_Flag VARCHAR2
208 );
209
210 PROCEDURE Delete_Row(X_Rowid VARCHAR2);
211
212 --
213 -- Procedure
214 -- Load_Row
215 -- Purpose
216 -- Loads A row into the gl_periods table. Created for Bug Fix: 2523133
217 -- History
218 -- 10-10-02 N Kasu Created
219 -- Notes
220 --
221
222 PROCEDURE Load_Row(X_Period_Set_Name VARCHAR2,
223 X_Period_Name VARCHAR2,
224 X_Owner VARCHAR2,
225 X_Start_Date VARCHAR2,
226 X_End_Date VARCHAR2,
227 X_Period_Type VARCHAR2,
228 X_Period_Year NUMBER,
229 X_Period_Num NUMBER,
230 X_Quarter_Num NUMBER,
231 X_Entered_Period_Name VARCHAR2,
232 X_Description VARCHAR2,
233 X_Attribute1 VARCHAR2,
234 X_Attribute2 VARCHAR2,
235 X_Attribute3 VARCHAR2,
236 X_Attribute4 VARCHAR2,
237 X_Attribute5 VARCHAR2,
238 X_Attribute6 VARCHAR2,
239 X_Attribute7 VARCHAR2,
240 X_Attribute8 VARCHAR2,
241 X_Context VARCHAR2,
242 X_Adjustment_Period_Flag VARCHAR2
243 );
244
245
246
247
248
249 --
250 -- Procedure
251 -- maintain_quarter_start_date
252 -- Purpose
253 -- maintains quarter_start date in the GL_PERIODS and GL_PERIOD_STATUSES
254 -- History
255 -- 12-04-95 E Weinstein Created
256 -- Arguments
257 -- x_period_set_name Name of the calendar
258 -- containing the period
259 -- x_period_type Type of the period
260 -- x_period_year accounting period year
261 -- x_quarter_num quarter number
262 -- x_operation UPDATE or INSERT for Update_Row or Insert_Row
263 -- x_quarter_start_date returns new quarter_start_date
264 -- Example
265 -- maintain_quarter_start_date('Barclays','Month',1993,2,
266 -- TO_DATE('01-04-1993', 'DD-MM-YYYY'),'UPDATE');
267 -- Notes
268 --
269 PROCEDURE maintain_quarter_start_date
270 (
271 x_period_set_name VARCHAR2,
272 x_period_type VARCHAR2,
273 x_period_year NUMBER,
274 x_quarter_num NUMBER,
275 x_start_date DATE,
276 x_operation VARCHAR2,
277 x_quarter_start_date IN OUT NOCOPY DATE
278 );
279
280 --
281 -- Procedure
282 -- maintain_year_start_date
283 -- Purpose
284 -- maintains year_start_date in the GL_PERIODS and GL_PERIOD_STATUSES
285 -- History
286 -- 12-04-95 E Weinstein Created
287 -- Arguments
288 -- x_period_set_name Name of the calendar
289 -- containing the period
290 -- x_period_type Type of the period
291 -- x_period_year accounting period year
292 -- x_operation UPDATE or INSERT for Update_Row or Insert_Row
293 -- x_year_start_date returns new year_start_date
294 -- Example
295 -- maintain_year_start_date('Barclays','Month',1993,2,
296 -- TO_DATE('01-04-1993', 'DD-MM-YYYY'),'UPDATE');
297 -- Notes
298 --
299 PROCEDURE maintain_year_start_date
300 (
301 x_period_set_name VARCHAR2,
302 x_period_type VARCHAR2,
303 x_period_year NUMBER,
304 x_start_date DATE,
305 x_operation VARCHAR2,
306 x_year_start_date IN OUT NOCOPY DATE
307 );
308 --
309 -- Function
310 -- period_set_with_AB
311 -- Purpose
312 -- returns YES if this period_set_name is being used by
313 -- set of books with Average Balancing enabled. otherwise returns NO
314 -- History
315 -- 12-04-95 E Weinstein Created
316 -- Arguments
317 -- x_period_set_name Name of the calendar
318 -- Example
319 -- period_set_with_AB('Barclays');
320 -- Notes
321 --
322 FUNCTION period_set_with_AB
323 (
324 x_period_set_name VARCHAR2
325 ) RETURN VARCHAR2;
326
327 --
328 -- Procedure
329 -- maintain_AB_data
330 -- Purpose
331 -- higher level procedure which calls other AB-related functions and is
332 -- being called from Insert_Row/Update_Row (gl_period_pkg)
333 -- History
334 -- 12-04-95 E Weinstein Created
335 -- Arguments
336 -- x_operation UPDATE or INSERT for Update_Row or Insert_Row
337 -- x_period_set_name Name of the calendar
338 -- containing the period
339 -- x_period_type Type of the period
340 -- x_period_year accounting period year
341 -- x_adjust_period_flag calendar period adjustment status
342 -- x_start_date_old previous start date for this period
343 -- x_start_date_new new start date for this period
344 -- x_end_date date on which accounting period ends
345 -- x_period_name accounting period name
346 -- x_period_year_old previous period_year for this period
347 -- x_period_year_new new period_year for this period
348 -- x_quarter_num_old previous quarter_num for this period
349 -- x_quarter_num_new new quarter_num for this period
353 -- maintain_quarter_start_date('Barclays','Month',1993,2,
350 -- x_quarter_start_date returns new quarter_start_date
351 -- x_year_start_date returns new year_start_date
352 -- Example
354 -- TO_DATE('01-04-1993', 'DD-MM-YYYY'),'UPDATE');
355 -- Notes
356 --
357 PROCEDURE maintain_AB_data
358 (
359 x_operation VARCHAR2,
360 x_period_set_name VARCHAR2,
361 x_period_type VARCHAR2,
362 x_adjust_period_flag VARCHAR2,
363 x_start_date_old DATE,
364 x_start_date_new DATE,
365 x_end_date DATE,
366 x_period_name VARCHAR2,
367 x_period_year_old NUMBER,
368 x_period_year_new NUMBER,
369 x_quarter_num_old NUMBER,
370 x_quarter_num_new NUMBER,
371 x_quarter_start_date IN OUT NOCOPY DATE,
372 x_year_start_date IN OUT NOCOPY DATE,
373 x_CREATION_DATE DATE,
374 x_CREATED_BY NUMBER,
375 x_LAST_UPDATE_DATE DATE,
376 x_LAST_UPDATED_BY NUMBER,
377 x_LAST_UPDATE_LOGIN NUMBER
378 );
379
380 -- Procedure
381 -- Insert_Period
382 -- Purpose
383 -- Insert into gl_periods table. Called by the Calendar iSpeed API.
384 -- History
385 -- 15-JAN-01 O Monnier Created
386 -- Arguments
387 --
388 PROCEDURE Insert_Period(Y_Rowid IN OUT NOCOPY VARCHAR2,
389 Y_Period_Set_Name VARCHAR2,
390 Y_Period_Name VARCHAR2,
391 Y_Last_Update_Date DATE,
392 Y_Last_Updated_By NUMBER,
393 Y_Start_Date DATE,
394 Y_End_Date DATE,
395 Y_Period_Type VARCHAR2,
396 Y_Period_Year NUMBER,
397 Y_Period_Num NUMBER,
398 Y_Quarter_Num NUMBER,
399 Y_Entered_Period_Name VARCHAR2,
400 Y_Creation_Date DATE,
401 Y_Created_By NUMBER,
402 Y_Last_Update_Login NUMBER,
403 Y_Description VARCHAR2,
404 Y_Attribute1 VARCHAR2,
405 Y_Attribute2 VARCHAR2,
406 Y_Attribute3 VARCHAR2,
407 Y_Attribute4 VARCHAR2,
408 Y_Attribute5 VARCHAR2,
409 Y_Attribute6 VARCHAR2,
410 Y_Attribute7 VARCHAR2,
411 Y_Attribute8 VARCHAR2,
412 Y_Context VARCHAR2,
413 Y_Adjustment_Period_Flag VARCHAR2
414 );
415
416 -- Procedure
417 -- Update_Period
418 -- Purpose
419 -- Update gl_periods table. Called by the Calendar iSpeed API.
420 -- History
421 -- 15-JAN-01 O Monnier Created
422 -- Arguments
423 --
424 PROCEDURE Update_Period(Y_Rowid IN OUT NOCOPY VARCHAR2,
425 Y_Period_Set_Name VARCHAR2,
426 Y_Period_Name VARCHAR2,
427 Y_Last_Update_Date DATE,
428 Y_Last_Updated_By NUMBER,
429 Y_Start_Date DATE,
430 Y_End_Date DATE,
431 Y_Period_Type VARCHAR2,
432 Y_Period_Year NUMBER,
433 Y_Period_Num NUMBER,
434 Y_Quarter_Num NUMBER,
435 Y_Entered_Period_Name VARCHAR2,
436 Y_Last_Update_Login NUMBER,
437 Y_Description VARCHAR2,
438 Y_Attribute1 VARCHAR2,
439 Y_Attribute2 VARCHAR2,
440 Y_Attribute3 VARCHAR2,
441 Y_Attribute4 VARCHAR2,
442 Y_Attribute5 VARCHAR2,
443 Y_Attribute6 VARCHAR2,
444 Y_Attribute7 VARCHAR2,
445 Y_Attribute8 VARCHAR2,
446 Y_Context VARCHAR2,
447 Y_Adjustment_Period_Flag VARCHAR2
448 );
449
450 END gl_periods_pkg;