1 PACKAGE gl_period_statuses_pkg AS
2 /* $Header: glipstas.pls 120.6 2006/08/11 12:31:06 aktelang ship $ */
3 --
4 -- Package
5 -- gl_period_statuses_pkg
6 -- Purpose
7 -- To contain validation and insertion routines for gl_period_statuses
8 -- History
9 -- 10-07-93 D. J. Ogg Created
10 -- 01-10-94 D. J. Ogg Modified select_columns to also return the
11 -- period number and year
12
13 --
14 -- Procedure
15 -- default_actual_period
16 -- Purpose
17 -- Finds the default period for actual batches in the given
18 -- ledger
19 -- History
20 -- 12-30-93 D. J. Ogg Created
21 -- Arguments
22 -- acc_id The current access set id
23 -- led_id The ledger to find the default period
24 -- for.
25 -- Example
26 -- default_period := gl_je_batches_pkg.default_actual_period(2);
27 -- Notes
28 --
29 FUNCTION default_actual_period(acc_id NUMBER, led_id NUMBER) RETURN VARCHAR2;
30
31 --
32 -- Procedure
33 -- get_next_period
34 -- Purpose
35 -- To get the next open period and return it in x_next_period.
36 -- History
37 -- 01-06-94 E. Rumanang Created
38 -- Arguments
39 -- x_ledger_id Ledger id
40 -- x_period Last executed period
41 -- x_next_period Next open period
42 -- Example
43 -- gl_period_statuses_pkg.get_next_period(
44 -- :block.ledger_id,
45 -- :block.period,
46 -- :block.next_period );
47 -- Notes
48 --
49 PROCEDURE get_next_period(
50 x_ledger_id NUMBER,
51 x_period VARCHAR2,
52 x_next_period IN OUT NOCOPY VARCHAR2 );
53
54
55 --
56 -- Procedure
57 -- insert_led_ps
58 -- Purpose
59 -- Used to insert records into gl_period_statuses
60 -- for a new ledger.
61 -- History
62 -- 12-06-93 E. Rumanang Created
63 -- Arguments
64 -- x_ledger_id ID of new created ledger
65 -- x_period_set_name Name of the calendar
66 -- x_accounted_period_type Name of the period type
67 -- x_last_update_date The user's data who create/update the row
68 -- x_last_updated_by
69 -- x_last_update_login
70 -- x_creation_date
71 -- x_created_by
72 -- Example
73 -- gl_period_statuses_pkg.insert_led_ps(
74 -- '1', 'Standard', 'Month', '01-JAN-93', 100, 100, '01-JAN-93', 100)
75 -- Notes
76 --
77 PROCEDURE insert_led_ps(
78 x_ledger_id NUMBER,
79 x_period_set_name VARCHAR2,
80 x_accounted_period_type VARCHAR2,
81 x_last_update_date DATE,
82 x_last_updated_by NUMBER,
83 x_last_update_login NUMBER,
84 x_creation_date DATE,
85 x_created_by NUMBER );
86
87
88 --
89 -- Procedure
90 -- insert_ps_api
91 -- Purpose
92 -- API for inserting period statuses records for non-GL
93 -- products that need to maintain statuses for non-accounting
94 -- calendars, i.e. period_set_name different from that of led.
95 -- History
96 -- 08-06-96 U. Thimmappa Created
97 -- Arguments
98 -- x_appl_id Applcation ID
99 -- x_ledger_id Ledger ID
100 -- x_period_name Name of the period
101 -- x_status Status of the period
102 -- x_period_set_name Name of the calendar
103 -- x_user_id User ID
104 -- x_login_id Login ID
105 -- Example
106 -- 1. gl_period_statuses_pkg.insert_ps_api(
107 -- 275, 1, 'JAN-93', 'O','', 1034, 1034 )
108 -- 2. gl_period_statuses_pkg.insert_ps_api(
109 -- 275, 1, 'JAN-93', 'O', 'Standard', 1034, 1034 )
110 -- Notes
111 -- 1. If x_period_set_name is NULL, period_set_name
112 -- from led will be used.
113 -- 2. This api is only used by PA in 10.7.
114 --
115 PROCEDURE insert_ps_api(
116 x_appl_id NUMBER,
117 x_ledger_id NUMBER,
118 x_period_name VARCHAR2,
119 x_status VARCHAR2,
120 x_period_set_name VARCHAR2,
121 x_user_id NUMBER,
122 x_login_id NUMBER );
123 --
124 -- Procedure
125 -- insert_period
126 -- Purpose
127 -- Used to insert records into gl_period_statuses
128 -- for a new period.
129 -- History
130 -- 10-07-93 D. J. Ogg Created
131 -- Arguments
132 -- x_calendar_name Name of the calendar the
133 -- period is in
134 -- x_period_name Name of the period
135 -- x_start_date Start date of period
136 -- x_end_date End date of period
137 -- x_period_type Type of period
138 -- x_period_year Year of period
139 -- x_period_num Number of period
140 -- x_quarter_num Number of quarter that
141 -- the period belongs in
142 -- x_adj_period_flag Adjustment period flag
143 -- x_last_updated_by User ID of last person to
144 -- update the period
145 -- x_last_update_login Login ID of the last person
146 -- to update the period
147 -- Example
148 -- gl_period_statuses_pkg.insert_period(
149 -- 'Standard', 'JAN-93', '01-JAN-93', '31-JAN-93', 'Month',
150 -- 1993, 1, 1, 'N', 0, 0);
151 -- Notes
152 --
153 PROCEDURE insert_period(
154 x_calendar_name VARCHAR2,
155 x_period_name VARCHAR2,
156 x_start_date DATE,
157 x_end_date DATE,
158 x_period_type VARCHAR2,
159 x_period_year NUMBER,
160 x_period_num NUMBER,
161 x_quarter_num NUMBER,
162 x_adj_period_flag VARCHAR2,
163 x_last_updated_by NUMBER,
164 x_last_update_login NUMBER,
165 x_quarter_start_date DATE,
166 x_year_start_date DATE);
167
168 --
169 -- Procedure
170 -- update_period
171 -- Purpose
172 -- Updates the entries in gl_period_statuses associated with a given
173 -- period.
174 -- History
175 -- 10-07-93 D. J. Ogg Created
176 -- Arguments
177 -- x_calendar_name Name of the calendar the
178 -- period is in
179 -- x_old_period_name Name of the period before
180 -- the update
181 -- x_period_name Name of the period after
182 -- the update
183 -- x_start_date Start date of period
184 -- x_end_date End date of period
185 -- x_period_type Type of period
186 -- x_period_year Year of period
187 -- x_period_num Number of period
188 -- x_quarter_num Number of quarter that
189 -- the period belongs in
190 -- x_adj_period_flag Adjustment period flag
191 -- x_last_updated_by User ID of last person to
192 -- update the period
193 -- x_last_update_login Login ID of the last person
194 -- to update the period
195 -- Example
196 -- options.update_period('Standard', 'Jan-91', 'JAN-91', '01-JAN-91',
197 -- '31-JAN-91', 'Month', 1991, 1, 1, 'N', 0, 0);
198 -- Notes
199 --
200 PROCEDURE update_period(
201 x_calendar_name VARCHAR2,
202 x_old_period_name VARCHAR2,
203 x_period_name VARCHAR2,
204 x_start_date DATE,
205 x_end_date DATE,
206 x_period_type VARCHAR2,
207 x_period_year NUMBER,
208 x_period_num NUMBER,
209 x_quarter_num NUMBER,
210 x_adj_period_flag VARCHAR2,
211 x_last_updated_by NUMBER,
212 x_last_update_login NUMBER);
213
214 --
215 -- Procedure
216 -- delete_period
217 -- Purpose
218 -- Deletes the rows in gl_period_statuses associated with
219 -- a period.
220 -- History
221 -- 10-07-93 D. J. Ogg Created
222 -- Arguments
223 -- x_calendar_name Name of the calendar the
224 -- period is in
225 -- x_old_period_name Name of the period before
226 -- the update
227 -- Example
228 -- options.delete_period('Standard', 'JAN-91');
229 -- Notes
230 --
231 PROCEDURE delete_period(
232 x_calendar_name VARCHAR2,
233 x_old_period_name VARCHAR2);
234
235 --
236 -- Procedure
237 -- select_row
238 -- Purpose
239 -- Gets the row from gl_period_statuses associated with
240 -- the given period.
241 -- History
242 -- 11-05-93 E. Rumanang Created
243 -- Arguments
244 -- recinfo gl_period_statuses
245 -- Example
246 -- select_row.recinfo;
247 -- Notes
248 --
249 PROCEDURE select_row( recinfo IN OUT NOCOPY gl_period_statuses%ROWTYPE );
250
251 --
252 -- Procedure
253 -- select_columns
254 -- Purpose
255 -- Gets the value for closing_status, start_date, end_date, period_num,
256 -- and period_year columns from gl_period_statuses associated with
257 -- the given period.
258 -- History
259 -- 11-05-93 E. Rumanang Created
260 -- 01-10-94 D J Ogg Added arguments for period num and period year
261 -- Arguments
262 -- x_application_id Application id of the product
263 -- x_ledger_id Ledger id
264 -- x_period_name Name of the period
265 -- x_closing_status Status of the period
266 -- x_start_date Start date of the period
267 -- x_end_date End date of the period
268 -- x_period_num Number of the period
269 -- x_period_year Year containing the period
270 -- Example
271 -- gl_period_statuses_pkg.select_columns( :block.application_id,
272 -- :block.ledger_id, :block.period_name, :block.closing_status,
273 -- :block.start_date, :block.end_date, :block.pnum, :block.pyear );
274 -- Notes
275 --
276 PROCEDURE select_columns(
277 x_application_id NUMBER,
278 x_ledger_id NUMBER,
279 x_period_name VARCHAR2,
280 x_closing_status IN OUT NOCOPY VARCHAR2,
281 x_start_date IN OUT NOCOPY DATE,
282 x_end_date IN OUT NOCOPY DATE,
283 x_period_num IN OUT NOCOPY NUMBER,
284 x_period_year IN OUT NOCOPY NUMBER );
285
286
287 --
288 -- Procedure
289 -- initialize_period_statuses
290 -- Purpose
291 -- Initialize the open or future enterable period statuses.
292 -- History
293 -- 11-08-93 E. Rumanang Created
294 -- Arguments
295 -- x_application_id Application id of the product
296 -- x_ledger_id Ledger id
297 -- x_period_year period year
298 -- x_period_num period number
299 -- x_user_id User Id
300 -- Example
301 -- gl_period_statuses_pkg.initialize_period_statuses(
302 -- :block.application_id,
303 -- :block.ledger_id,
304 -- :block.period_year,
305 -- :block.period_num,
306 -- :block.user_id )
307 -- Notes
308 --
309 PROCEDURE initialize_period_statuses(
310 x_application_id NUMBER,
311 x_ledger_id NUMBER,
312 x_period_year NUMBER,
313 x_period_num NUMBER,
314 x_user_id NUMBER );
315
316
317 --
318 -- Procedure
319 -- select_encumbrance_periods
320 -- Purpose
321 -- selects 2 period_names:
322 -- PS2 is the first period of year, which is prior to
323 -- latest_encumbrance_year in ledgers.
324 -- PS1 is prior closed/permanently closed period prior to PS2.
325 -- History
326 -- 06-14-94 Kai Pigg Created
327 -- Arguments
328 -- x_application_id Application id of the product
329 -- x_ledger_id Ledger id
330 -- x_first_period PS1.period_name
331 -- x_first_period_start_date PS1.start_date
332 -- x_second_period PS2.period_name
333 -- x_second_period_year PS2.period_year
334 -- x_second_period_start_date PS2.start_date
335 -- Example
336 -- gl_period_statuses_pkg.select_encumbrance_periods
337 -- :block.application_id,
338 -- :block.ledger_id,
339 -- :block.first_period,
340 -- :block.first_start,
341 -- :block.second_period,
342 -- :block.second_year
343 -- :block.second_start
344 -- Notes
345 --
346 PROCEDURE select_encumbrance_periods (
347 x_application_id NUMBER,
348 x_ledger_id NUMBER,
349 x_first_period IN OUT NOCOPY VARCHAR2,
350 x_first_period_start_date IN OUT NOCOPY DATE,
351 x_second_period IN OUT NOCOPY VARCHAR2,
352 x_second_period_year IN OUT NOCOPY NUMBER,
353 x_second_period_start_date IN OUT NOCOPY DATE);
354 --
355 -- Procedure
356 -- select_prior_year_1st_period
357 -- Purpose
358 -- selects prior years first period
359 -- History
360 -- 06-14-94 Kai Pigg Created
361 -- Arguments
362 -- x_application_id Application id of the product
363 -- x_ledger_id Ledger id
364 -- x_period_year Period Year
365 -- x_period_name Period Name
366 -- Example
367 -- gl_period_statuses_pkg.select_prior_year_1st_period
368 -- :block.application_id,
369 -- :block.ledger_id,
370 -- :block.period_year,
371 -- :block.period_name)
372 -- Notes
373 --
374 PROCEDURE select_prior_year_1st_period(
375 x_application_id NUMBER,
376 x_ledger_id NUMBER,
377 x_period_year NUMBER,
378 x_period_name IN OUT NOCOPY VARCHAR2);
379
380 --
381 -- Procedure
382 -- select_year_1st_period
383 -- Purpose
384 -- selects years first period
385 -- History
386 -- 06-SEP-94 ERumanan Created.
387 -- Arguments
388 -- x_application_id Application id of the product
389 -- x_ledger_id Ledger id
390 -- x_period_year Period Year
391 -- x_period_name Period Name
392 -- Example
393 -- gl_period_statuses_pkg.select_year_1st_period
394 -- :block.application_id,
395 -- :block.ledger_id,
396 -- :block.period_year,
397 -- :block.period_name)
398 -- Notes
399 --
400 PROCEDURE select_year_1st_period(
401 x_application_id NUMBER,
402 x_ledger_id NUMBER,
403 x_period_year NUMBER,
404 x_period_name IN OUT NOCOPY VARCHAR2);
405
406
407 -- Procedure
408 -- get_extended_quarter
409 -- Purpose
410 -- Get extended quarter period.
411 -- History
412 -- 06-SEP-94 ERumanan Created.
413 -- Arguments
414 -- x_application_id
415 -- x_ledger_id
416 -- x_period_year
417 -- x_period_name
418 -- x_period_set_name
419 -- x_accounted_period_type
420 -- x_period_used_for_ext_actuals
421 -- x_num_used_for_ext_actuals
422 -- x_year_used_for_ext_actuals
423 -- x_quarter_used_for_ext_actuals
424 --
425 PROCEDURE get_extended_quarter(
426 x_application_id NUMBER,
427 x_ledger_id NUMBER,
428 x_period_year NUMBER,
429 x_period_name VARCHAR2,
433 x_num_used_for_ext_actuals IN OUT NOCOPY NUMBER,
430 x_period_set_name VARCHAR2,
431 x_accounted_period_type VARCHAR2,
432 x_period_used_for_ext_actuals IN OUT NOCOPY VARCHAR2,
434 x_year_used_for_ext_actuals IN OUT NOCOPY NUMBER,
435 x_quarter_used_for_ext_actuals IN OUT NOCOPY NUMBER );
436
437
438
439 -- Procedure
440 -- get_extended_year
441 -- Purpose
442 -- Get extended year period.
443 -- History
444 -- 07-SEP-94 ERumanan Created.
445 -- Arguments
446 -- x_application_id
447 -- x_ledger_id
448 -- x_period_year
449 -- x_accounted_period_type
450 -- x_period_used_for_ext_actuals
451 -- x_num_used_for_ext_actuals
452 -- x_year_used_for_ext_actuals
453 -- x_quarter_used_for_ext_actuals
454
455 PROCEDURE get_extended_year(
456 x_application_id NUMBER,
457 x_ledger_id NUMBER,
458 x_period_year NUMBER,
459 x_accounted_period_type VARCHAR2,
460 x_period_used_for_ext_actuals IN OUT NOCOPY VARCHAR2,
461 x_num_used_for_ext_actuals IN OUT NOCOPY NUMBER,
462 x_year_used_for_ext_actuals IN OUT NOCOPY NUMBER,
463 x_quarter_used_for_ext_actuals IN OUT NOCOPY NUMBER );
464
465
466
467 -- The following procedures are necessary to handle the base view form.
468
469 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
470 X_Application_Id NUMBER,
471 X_Ledger_Id NUMBER,
472 X_Period_Name VARCHAR2,
473 X_Last_Update_Date DATE,
474 X_Last_Updated_By NUMBER,
475 X_Closing_Status VARCHAR2,
476 X_Start_Date DATE,
477 X_End_Date DATE,
478 X_Period_Type VARCHAR2,
479 X_Period_Year NUMBER,
480 X_Period_Num NUMBER,
481 X_Quarter_Num NUMBER,
482 X_Adjustment_Period_Flag VARCHAR2,
483 X_Creation_Date DATE,
484 X_Created_By NUMBER,
485 X_Last_Update_Login NUMBER,
486 X_Attribute1 VARCHAR2,
487 X_Attribute2 VARCHAR2,
488 X_Attribute3 VARCHAR2,
489 X_Attribute4 VARCHAR2,
490 X_Attribute5 VARCHAR2,
491 X_Context VARCHAR2
492 );
493
494
495
496 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
497 X_Application_Id NUMBER,
498 X_Ledger_Id NUMBER,
499 X_Period_Name VARCHAR2,
500 X_Closing_Status VARCHAR2,
501 X_Start_Date DATE,
502 X_End_Date DATE,
503 X_Period_Type VARCHAR2,
504 X_Period_Year NUMBER,
505 X_Period_Num NUMBER,
506 X_Quarter_Num NUMBER,
507 X_Adjustment_Period_Flag VARCHAR2,
508 X_Attribute1 VARCHAR2,
509 X_Attribute2 VARCHAR2,
510 X_Attribute3 VARCHAR2,
511 X_Attribute4 VARCHAR2,
512 X_Attribute5 VARCHAR2,
513 X_Context VARCHAR2
514 );
515
516 PROCEDURE Update_Row(X_Rowid VARCHAR2,
517 X_Application_Id NUMBER,
518 X_Ledger_Id NUMBER,
519 X_Period_Name VARCHAR2,
520 X_Last_Update_Date DATE,
521 X_Last_Updated_By NUMBER,
522 X_Closing_Status VARCHAR2,
523 X_Start_Date DATE,
524 X_End_Date DATE,
525 X_Period_Type VARCHAR2,
526 X_Period_Year NUMBER,
527 X_Period_Num NUMBER,
528 X_Quarter_Num NUMBER,
529 X_Adjustment_Period_Flag VARCHAR2,
530 X_Last_Update_Login NUMBER,
531 X_Attribute1 VARCHAR2,
535 X_Attribute5 VARCHAR2,
532 X_Attribute2 VARCHAR2,
533 X_Attribute3 VARCHAR2,
534 X_Attribute4 VARCHAR2,
536 X_Context VARCHAR2
537 );
538
539
540 PROCEDURE Delete_Row(X_Rowid VARCHAR2);
541
542 --
543 -- Procedure
544 -- update_row_dff
545 -- Purpose
546 -- Updates the value for attribute columns of
547 -- gl_period_statuses associated with the given period of the supplied rowid.
548 -- History
549 -- 08/10/06 aktelang Created
550 -- Arguments
551 -- X_rowid Row id for the period
552 -- X_attribute1 Attribute column
553 -- X_attribute2 Attribute column
554 -- X_attribute3 Attribute column
555 -- X_attribute4 Attribute column
556 -- X_attribute5 Attribute column
557 -- X_context Context column
558 -- X_Last_Update_Date Who column
559 -- X_Last_Updated_By Who column
560 -- X_Last_Update_Login Who column
561
562 -- Example
563 -- gl_period_statuses_pkg.update_row_dff( :block.rowid,
564 -- :block.attribute1, :block.attribute2,
565 -- :block.attribute3, :block.attribute4,
566 -- :block.attribute4, :block.context,
567 -- :block.Last_Update_Date, :block.Last_Updated_By,
568 -- :block.Last_Update_Login)
569 -- Notes
570 --
571 PROCEDURE update_row_dff(
572 X_rowid VARCHAR2,
573 X_attribute1 VARCHAR2,
574 X_attribute2 VARCHAR2,
575 X_attribute3 VARCHAR2,
576 X_attribute4 VARCHAR2,
577 X_attribute5 VARCHAR2,
578 X_context VARCHAR2,
579 X_Last_Update_Date DATE,
580 X_Last_Updated_By NUMBER,
581 X_Last_Update_Login NUMBER );
582
583 --
584 -- Procedure
585 -- get_period_by_date
586 -- Purpose
587 -- Gets the value for closing_status, period_name from
588 -- gl_period_statuses associated with the given date.
589 -- History
590 -- 07/31/95 Eugene Weinstein Created
591 -- Arguments
592 -- x_application_id Application id of the product
593 -- x_ledger_id Ledger id
594 -- x_given_date the date which being analyzed
595 -- x_period_name Name of the period
596 -- x_closing_status Status of the period
597 -- Example
598 -- gl_period_statuses_pkg.get_period_by_date( :block.application_id,
599 -- :block.ledger_id, :block.x_given_date,
600 -- :block.period_name, :block.closing_status);
601 -- Notes
602 --
603 PROCEDURE get_period_by_date(
604 x_application_id NUMBER,
605 x_ledger_id NUMBER,
606 x_given_date DATE,
607 x_period_name IN OUT NOCOPY VARCHAR2,
608 x_closing_status IN OUT NOCOPY VARCHAR2,
609 x_period_year IN OUT NOCOPY NUMBER,
610 x_period_num IN OUT NOCOPY NUMBER,
611 x_period_type IN OUT NOCOPY VARCHAR2);
612
613
614 --
615 -- Procedure
616 -- get_calendar_range
617 -- Purpose
618 -- Gets the minimum start date and maximum end date for the
619 -- first ever opened period and the latest future enterable period
620 -- for a given ledger.
621 -- History
622 -- 25-APR-96 R Goyal Created
623 -- Arguments
624 -- x_ledger_id Ledger id
625 -- x_start_date Start date for the range
626 -- x_end_date End date for the range
627 -- Example
628 -- gl_period_statuses_pkg.get_calendar_range(:led_id, :start_date, :end_date)
629 -- Notes
630 --
631 PROCEDURE get_calendar_range(
632 x_ledger_id NUMBER,
633 x_start_date IN OUT NOCOPY DATE,
634 x_end_date IN OUT NOCOPY DATE);
635
636
637 --
638 -- Procedure
639 -- get_open_closed_calendar_range
640 -- Purpose
641 -- Gets the minimum start date and maximum end date for periods
642 -- with a status in 'O', 'P', 'C' for a given ledger.
643 -- History
644 -- 06-MAR-97 R Goyal Created
645 -- Arguments
646 -- x_ledger_id Ledger id
647 -- x_start_date Start date for the range
648 -- x_end_date End date for the range
649 -- Example
650 -- gl_period_statuses_pkg.get_open_closed_calendar_range(:led_id,
651 -- :start_date, :end_date)
652 -- Notes
653 --
654 PROCEDURE get_open_closed_calendar_range(
655 x_ledger_id NUMBER,
656 x_start_date IN OUT NOCOPY DATE,
657 x_end_date IN OUT NOCOPY DATE);
658
659
660
661 --
662 -- Procedure
663 -- get_journal_range
664 -- Purpose
665 -- Gets the minimum start date and maximum end date for all open
666 -- or future enterable periods
667 -- History
668 -- 19-JUN-96 D J Ogg Created
669 -- Arguments
670 -- x_ledger_id Ledger id
671 -- x_start_date Start date for the range
672 -- x_end_date End date for the range
673 -- Example
674 -- gl_period_statuses_pkg.get_journal_range(:led_id,:start_date,:end_date)
675 -- Notes
676 --
677 PROCEDURE get_journal_range(
678 x_ledger_id NUMBER,
679 x_start_date IN OUT NOCOPY DATE,
680 x_end_date IN OUT NOCOPY DATE);
681
682 END gl_period_statuses_pkg;
[Home] [Help]