1 PACKAGE BODY EDW_TIME_PKG AS
2 /* $Header: FIICATMB.pls 120.0 2002/08/24 04:50:22 appldev noship $ */
3 VERSION CONSTANT CHAR(80) := '$Header: FIICATMB.pls 120.0 2002/08/24 04:50:22 appldev noship $';
4
5 -- --------------------------------------------------------------
6 -- Name: cal_day_fk
7 -- Performance: 4 buffer gets per call
8 -- --------------------------------------------------------------
9 function cal_day_fk(cal_date date,
10 p_set_of_books_id number,
11 p_instance_code in VARCHAR2:=NULL)
12 return varchar2 is
13 l_accounted_period_type Varchar2(15) := NULL;
14 l_period_set_name Varchar2(15) := NULL;
15 l_instance Varchar2(40) := NULL;
16 x_no_data_found EXCEPTION;
17
18 cursor c1 is
19 select ins.instance_code,
20 sob.accounted_period_type,
21 sob.period_set_name
22 from edw_local_instance ins,
23 gl_sets_of_books sob
24 where set_of_books_id = p_set_of_books_id;
25
26 begin
27
28 -- ----------------------------------------------------
29 -- For performance reasons, we will not check if the
30 -- input date falls in a valid GL period. If not
31 -- within a valid period, the record will get rejected
32 -- in the warehouse.
33 -- ----------------------------------------------------
34
35 if (cal_date is null) or
36 (p_set_of_books_id is null)
37 then
38 return('NA_EDW');
39 else
40 OPEN c1;
41 FETCH c1 into l_instance, l_accounted_period_type,l_period_set_name;
42 CLOSE c1;
43 end if;
44
45 if (p_instance_code is not NULL)
46 then
47 l_instance := p_instance_code;
48 end if;
49
50 if (l_period_set_name is NULL)
51 then
52 -- Cursor did not find any records
53 RAISE x_no_data_found;
54 else
55 return(to_char(cal_date,'dd-mm-yyyy') || '-' || l_period_set_name || '-' ||
56 l_accounted_period_type || '-' || l_instance ||'-CD');
57 end if;
58
59 exception
60 when x_no_data_found then
61 raise_application_error(-20000, 'No data found, cal_date='||
62 to_char(cal_date)||',sob_id='||p_set_of_books_id);
63 when others then
64 if c1%ISOPEN then
65 close c1;
66 end if;
67 raise_application_error(-20000, 'Other Error, cal_date='||
68 to_char(cal_date)||',sob_id='||p_set_of_books_id);
69
70 end;
71
72
73 -- --------------------------------------------------------------
74 -- Name: cal_period_fk
75 -- Performance: 4 buffer gets per call
76 -- --------------------------------------------------------------
77 function cal_period_fk(cal_period varchar2,
78 p_set_of_books_id number,
79 p_instance_code in VARCHAR2:=NULL)
80 return varchar2 is
81 l_period_set_name Varchar2(15) := NULL;
82 l_instance Varchar2(40) := NULL;
83 x_no_data_found EXCEPTION;
84
85 cursor c1 is
86 select ins.instance_code,
87 sob.period_set_name
88 from edw_local_instance ins,
89 gl_sets_of_books sob
90 where set_of_books_id = p_set_of_books_id;
91
92 begin
93
94 if ((cal_period is null) or
95 (p_set_of_books_id is null))
96 then
97 return('NA_EDW');
98 else
99 OPEN c1;
100 FETCH c1 into l_instance, l_period_set_name;
101 CLOSE c1;
102 end if;
103
104 if (p_instance_code is not NULL)
105 then
106 l_instance := p_instance_code;
107 end if;
108
109 if (l_period_set_name is NULL)
110 then
111 -- Cursor did not find any records
112 RAISE x_no_data_found;
113 else
114 return(l_period_set_name||'-'||cal_period||'-'||l_instance||'-CPER');
115 end if;
116
117 exception
118 when x_no_data_found then
119 raise_application_error(-20000, 'No data found, cal_period='||
120 cal_period||',sob_id='||p_set_of_books_id);
121 when others then
122 if c1%ISOPEN then
123 close c1;
124 end if;
125 raise_application_error(-20000, 'Other Error, cal_period='||
126 cal_period||',sob_id='||p_set_of_books_id);
127
128 end;
129
130
131
132 -- --------------------------------------------------------------
133 -- Name: cal_day_to_cal_period_fk
134 -- Performance: 15.5 buffer gets per call
135 -- --------------------------------------------------------------
136 function cal_day_to_cal_period_fk(cal_date date,
137 p_set_of_books_id number,
138 p_instance_code in VARCHAR2:=NULL)
139 return varchar2 is
140 l_period_set_name Varchar2(15) := NULL;
141 l_instance Varchar2(40) := NULL;
142 l_gl_period Varchar2(30) := NULL;
143 x_no_data_found EXCEPTION;
144
145 cursor c1 is
146 select ins.instance_code,
147 maps.period_set_name,
148 maps.period_name
149 from edw_local_instance ins,
150 gl_sets_of_books sob,
151 gl_date_period_map maps
152 where maps.period_set_name = sob.period_set_name
153 and maps.period_type = sob.accounted_period_type
154 and maps.accounting_date = trunc(cal_date)
155 and sob.set_of_books_id = p_set_of_books_id;
156
157 begin
158 if (cal_date is null) or
159 (p_set_of_books_id is null)
160 then
161 return('NA_EDW');
162 else
163 OPEN c1;
164 FETCH c1 into l_instance, l_period_set_name, l_gl_period;
165 CLOSE c1;
166 end if;
167
168 if (p_instance_code is not NULL)
169 then
170 l_instance := p_instance_code;
171 end if;
172
173 if (l_period_set_name is NULL) then
174 -- Cursor did not find any records
175 RAISE x_no_data_found;
176 end if;
177
178 return(l_period_set_name
179 ||'-' || l_gl_period || '-' || l_instance ||'-CPER');
180
181 exception
182 when x_no_data_found then
183 raise_application_error(-20000, 'No data found, cal_date='||
184 cal_date||',sob_id='||p_set_of_books_id);
185 when others then
186 if c1%ISOPEN then
187 close c1;
188 end if;
189 raise_application_error(-20000, 'Other Error, cal_date='||
190 cal_date||',sob_id='||p_set_of_books_id);
191 end;
192
193
194 -- --------------------------------------------------------------
195 -- Name: pa_cal_day_fk
196 -- Performance: 6 buffer gets per call
197 -- --------------------------------------------------------------
198 Function pa_cal_day_fk(p_cal_date IN date,
199 p_org_id IN number DEFAULT NULL,
200 p_instance_code IN VARCHAR2:=NULL)
201 return varchar2 is
202 l_period_set_name Varchar2(15) := NULL;
203 l_period_type Varchar2(15) := NULL;
204 l_instance Varchar2(40) := NULL;
205 x_no_data_found EXCEPTION;
206
207
208 -- Cursor for Multi-org install
209 cursor c1 is
210 select ins.instance_code,
211 gl.period_set_name,
212 imp.pa_period_type
213 from edw_local_instance ins,
214 pa_implementations_all imp,
215 gl_sets_of_books gl
216 where imp.org_id = p_org_id
217 and gl.set_of_books_id = imp.set_of_books_id;
218
219 -- Cursor for Single org install
220 cursor c2 is
221 select ins.instance_code,
222 gl.period_set_name,
223 imp.pa_period_type
224 from edw_local_instance ins,
225 pa_implementations_all imp,
226 gl_sets_of_books gl
227 where imp.org_id is NULL
228 and gl.set_of_books_id = imp.set_of_books_id;
229
230 begin
231
232 -- ----------------------------------------------------
233 -- Decided not to check if the date falls in the
234 -- a valid PA period. If not within a valid period,
235 -- the record will get rejected in the warehouse
236 -- ----------------------------------------------------
237
238 if (p_cal_date is NULL)
239 then
240 return 'NA_EDW';
241 elsif (p_org_id is NULL)
242 then
243 OPEN c2;
244 FETCH c2 into l_instance, l_period_set_name, l_period_type;
245 CLOSE c2;
246 else
247 OPEN c1;
248 FETCH c1 into l_instance, l_period_set_name, l_period_type;
249 CLOSE c1;
250 end if;
251
252 if (p_instance_code is not NULL)
253 then
254 l_instance := p_instance_code;
255 end if;
256
257 if (l_period_set_name is NULL)
258 then
259 -- Cursor did not find any records
260 RAISE x_no_data_found;
261 else
262 return (to_char(p_cal_date,'dd-mm-yyyy') || '-' || l_period_set_name
263 || '-' || l_period_type ||'-' || l_instance ||'-PD');
264 end if;
265
266 exception
267 when x_no_data_found then
268 raise_application_error(-20000, 'No data found, cal_date='||
269 to_char(p_cal_date)||',org_id='||p_org_id);
270 when others then
271 if c1%ISOPEN then
272 close c1;
273 end if;
274 if c2%ISOPEN then
275 close c2;
276 end if;
277 raise_application_error(-20000, 'Other Error, cal_date='||
278 to_char(p_cal_date)||',org_id='||p_org_id);
279 end;
280
281
282 end;
[Home] [Help]