1 PACKAGE EDW_TIME_PKG AS
2 /* $Header: FIICATMS.pls 120.0 2002/08/24 04:50:26 appldev noship $ */
3 VERSION CONSTANT CHAR(80) := '$Header: FIICATMS.pls 120.0 2002/08/24 04:50:26 appldev noship $';
4
5 -- ------------------------
6 -- Public Functions
7 -- ------------------------
8
9 -- --------------------------------------------------------------
10 -- Name: cal_day_fk
11 -- Desc: This gives foreign key to the fiscal calendar day level in
12 -- the Time dimension for a given day and set of book.
13 -- Input : cal_date : Fiscal period name
14 -- p_set_of_books_id: The owning set of book for the transaction
15 -- Output: Date - Period Set - 'FIN' - Set of book id - instance - 'CD'
16 -- Error: If p_set_of_books_id is invalid or any sql errors occurs during
17 -- execution, an exception is raised. However, we don't check
18 -- if cal_date fails within a valid fiscal period. If this happens,
19 -- the record will get rejected in the warehouse
20 -- --------------------------------------------------------------
21 Function cal_day_fk(cal_date date,
22 p_set_of_books_id number,
23 p_instance_code in VARCHAR2:=NULL) return VARCHAR2;
24
25 -- --------------------------------------------------------------
26 -- Name: cal_period_fk
27 -- Desc: This gives foreign key to the fiscal period level pushed down
28 -- to the fiscal calendar day level give the fiscal period name
29 -- and set of books id. Mostly used for GL data stored
30 -- at the fiscal period level.
31 -- Input : cal_period : Fiscal period name for the transaction
32 -- p_set_of_books_id: set of books id
33 -- Output: Period Set - 'FIN' - Fiscal period - Instance - 'GL';
34 -- Error: If p_set_of_books_id is invalid or any sql errors occurs
35 -- during execution, exception is raised. However, we don't
36 -- check if cal_period is a valid period name.
37 -- --------------------------------------------------------------
38 Function cal_period_fk(cal_period varchar2,
39 p_set_of_books_id number,
40 p_instance_code in VARCHAR2:=NULL) return VARCHAR2;
41
42
43 -- ------------------------------------------------------------------
44 -- Name: cal_da_to_cal_period_fk
45 -- Desc: This gives foreign key to the fiscal period level pushed down
46 -- to the fiscal calendar day level given the calendar date
47 -- and set of book id. It uses the start/end dates of a fiscal
48 -- period to determine which period a calendar day belongs to.
49 -- Adjustment periods are excluded.
50 -- Input: cal_date - Date of the transaction
51 -- p_set_of_book_id - The owning set of book for the transaction
52 -- Output: Period Set - 'FIN' - Fiscal period - Instance - 'GL'
53 -- Error: If p_set_of_books_id or cal_date are invalid. An exception is
54 -- raised. Likewise if any sql errors occurs during execution
55 -- -------------------------------------------------------------------
56 Function cal_day_to_cal_period_fk(cal_date date,
57 p_set_of_books_id number,
58 p_instance_code in VARCHAR2:=NULL) return VARCHAR2;
59
60
61 -- -------------------------------------------------------------------
62 -- Name: pa_cal_day_fk
63 -- Desc: Returns the foreign key mapping to the fiscal calendar
64 -- day level of the Time dimension for PA related transactions.
65 -- The fiscal calendar day level holds a record for each
66 -- day in all financial calendars, including PA calendars.
67 -- This API returns the foreign keys which points to the
68 -- PA calendar days.
69 -- Input: cal_date - Date of the transaction
70 -- p_org_id - The owning operating unit for the PA transaction
71 -- NULL if single-org implementation
72 -- Output: Date - Period Set - Period Type - 'FIN' - Instance - 'PD'
73 -- Error: If p_org_id is invalid or any sql errors occurs during
74 -- execution, an exception is raised. However, we don't check
75 -- if p_cal_date fails within a valid PA period. If this happens,
76 -- the record will get rejected in the warehouse
77 -- --------------------------------------------------------------------
78 Function pa_cal_day_fk(p_cal_date IN date,
79 p_org_id IN number DEFAULT NULL,
80 p_instance_code in VARCHAR2:=NULL) return VARCHAR2;
81
82 PRAGMA RESTRICT_REFERENCES (cal_day_fk,WNDS, WNPS, RNPS);
83 PRAGMA RESTRICT_REFERENCES (cal_period_fk,WNDS, WNPS, RNPS);
84 PRAGMA RESTRICT_REFERENCES (cal_day_to_cal_period_fk,WNDS, WNPS, RNPS);
85 PRAGMA RESTRICT_REFERENCES (pa_cal_day_fk,WNDS, WNPS, RNPS);
86
87 end;