[Home] [Help]
PACKAGE BODY: APPS.BIS_POSTACTUAL
Source
1 package body BIS_PostActual as
2 /* $Header: BISPACTB.pls 120.1 2005/10/06 06:58:41 ankgoel noship $ */
3
4 G_ACTION_INSERT CONSTANT VARCHAR2(10) := 'INSERT';
5 G_ACTION_UPDATE CONSTANT VARCHAR2(10) := 'UPDATE';
6
7 PROCEDURE Post_Actual
8 ( x_target_lvl_short_name IN VARCHAR2
9 , x_organization_id IN NUMBER
10 , x_actual_value IN NUMBER
11 , x_timestamp IN DATE DEFAULT NULL
12 , x_DIMENSION1_LEVEL_VALUE IN VARCHAR2 DEFAULT NULL
13 , x_DIMENSION2_LEVEL_VALUE IN VARCHAR2 DEFAULT NULL
14 , x_DIMENSION3_LEVEL_VALUE IN VARCHAR2 DEFAULT NULL
15 , x_DIMENSION4_LEVEL_VALUE IN VARCHAR2 DEFAULT NULL
16 , x_DIMENSION5_LEVEL_VALUE IN VARCHAR2 DEFAULT NULL
17 )
18 IS
19 l_target_level_id NUMBER;
20 l_organization_id NUMBER;
21 l_organization_ID_char VARCHAR2(250);
22 l_time_level_Value VARCHAR2(250);
23 l_actual_rec BIS_ACTUAL_VALUES%ROWTYPE;
24 l_action VARCHAR2(10);
25 l_Return_Status VARCHAR2(1);
26 e_invalidActualException EXCEPTION;
27
28 CURSOR cr_actual IS
29 SELECT *
30 -- SELECT creation_date, last_update_date
31 FROM BIS_ACTUAL_VALUES
32 WHERE TARGET_LEVEL_ID = l_target_level_id
33 AND ORG_LEVEL_VALUE = l_organization_ID_char
34 AND TIME_LEVEL_VALUE = l_time_level_value
35 AND NVL(DIMENSION1_LEVEL_VALUE,'-999')=NVL(x_DIMENSION1_LEVEL_VALUE,'-999')
36 AND NVL(DIMENSION2_LEVEL_VALUE,'-999')=NVL(x_DIMENSION2_LEVEL_VALUE,'-999')
37 AND NVL(DIMENSION3_LEVEL_VALUE,'-999')=NVL(x_DIMENSION3_LEVEL_VALUE,'-999')
38 AND NVL(DIMENSION4_LEVEL_VALUE,'-999')=NVL(x_DIMENSION4_LEVEL_VALUE,'-999')
39 AND NVL(DIMENSION5_LEVEL_VALUE,'-999')=NVL(x_DIMENSION5_LEVEL_VALUE,'-999')
40 ORDER BY CREATION_DATE
41 FOR UPDATE;
42
43 --l_create_date DATE;
44 --l_update_date DATE;
45
46 BEGIN
47 -- check if target_level, organization IS valid
48 --
49 SELECT tg.target_level_id
50 INTO l_target_level_id
51 FROM bis_target_levels tg
52 WHERE tg.short_name = x_target_lvl_short_name;
53
54 IF x_organization_id <> -1 THEN
55 SELECT DISTINCT o.organization_id
56 INTO l_organization_id
57 FROM hr_all_organization_units o
58 WHERE o.organization_id = x_organization_id;
59 ELSE
60 l_organization_id := x_organization_id;
61 END IF;
62
63 l_organization_id_char := TO_CHAR(l_organization_id);
64
65 -- get the period_name for the current date
66 --
67 BIS_UTIL.Get_Time_Level_Value
68 ( p_date => x_Timestamp
69 , p_Target_Level_ID => l_target_level_id
70 , p_Organization_ID => l_organization_id
71 , x_Time_Level_Value => l_time_level_value
72 , x_Return_Status => l_Return_Status
73 );
74
75 IF l_Return_Status = FND_API.G_RET_STS_UNEXP_ERROR THEN
76 RAISE e_invalidActualException;
77 END IF;
78
79 -- check if previous record exist and set action flag
80 --
81 OPEN cr_actual;
82 FETCH cr_actual INTO l_actual_rec;
83 -- FETCH cr_actual INTO l_create_date, l_update_date;
84 IF cr_actual%FOUND THEN
85 l_action := G_ACTION_UPDATE;
86 ELSE
87 l_action := G_ACTION_INSERT;
88 END IF;
89
90 IF l_action = G_ACTION_UPDATE AND l_time_level_value IS NOT NULL THEN
91
92 UPDATE bis_actual_values
93 SET TARGET_LEVEL_ID = l_target_level_id
94 , ORG_LEVEL_VALUE = l_organization_id_char
95 , TIME_LEVEL_VALUE = l_time_level_value
96 , DIMENSION1_LEVEL_VALUE = x_dimension1_level_value
97 , DIMENSION2_LEVEL_VALUE = x_dimension2_level_value
98 , DIMENSION3_LEVEL_VALUE = x_dimension3_level_value
99 , DIMENSION4_LEVEL_VALUE = x_dimension4_level_value
100 , DIMENSION5_LEVEL_VALUE = x_dimension5_level_value
101 , ACTUAL_VALUE = x_actual_value
102 , LAST_UPDATE_DATE = SYSDATE
103 , LAST_UPDATED_BY = FND_GLOBAL.USER_ID
104 , LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
105 WHERE CURRENT OF cr_actual;
106
107 COMMIT;
108 IF cr_actual%ISOPEN THEN CLOSE cr_actual; END IF;
109
110 -- l_update_date := SYSDATE;
111
112 ELSIF l_action = G_ACTION_INSERT AND l_time_level_value IS NOT NULL THEN
113
114 IF cr_actual%ISOPEN THEN CLOSE cr_actual; END IF;
115
116 INSERT INTO bis_actual_values
117 (
118 ACTUAL_ID -- Fix for #3493470
119 , TARGET_LEVEL_ID
120 , ORG_LEVEL_VALUE
121 , TIME_LEVEL_VALUE
122 , DIMENSION1_LEVEL_VALUE
123 , DIMENSION2_LEVEL_VALUE
124 , DIMENSION3_LEVEL_VALUE
125 , DIMENSION4_LEVEL_VALUE
126 , DIMENSION5_LEVEL_VALUE
127 , ACTUAL_VALUE
128 , CREATION_DATE
129 , CREATED_BY
130 , LAST_UPDATE_DATE
131 , LAST_UPDATED_BY
132 , LAST_UPDATE_LOGIN
133 )
134 VALUES
135 ( BIS_ACTUAL_VALUES_S.NEXTVAL -- Fix for #3493470.
136 , l_target_level_id
137 , l_organization_id_char
138 , l_time_level_value
139 , x_dimension1_level_value
140 , x_dimension2_level_value
141 , x_dimension3_level_value
142 , x_dimension4_level_value
143 , x_dimension5_level_value
144 , x_actual_value
145 , SYSDATE
146 , FND_GLOBAL.USER_ID
147 , SYSDATE
148 , FND_GLOBAL.USER_ID
149 , FND_GLOBAL.LOGIN_ID
150 );
151
152 COMMIT;
153
154 -- l_update_date := SYSDATE;
155 -- l_create_date := SYSDATE;
156
157 ELSE
158 IF cr_actual%ISOPEN THEN CLOSE cr_actual; END IF;
159 RAISE e_invalidActualException;
160 END IF;
161
162 -- dbms_output.put_line('action: '||l_action
163 -- ||'. Time Val: '||SUBSTR(l_time_level_value,1,20)
164 -- ||'-- Org Val: '||SUBSTR(l_organization_id_char,1,5)
165 -- ||'-- Create date: '||l_create_date
166 -- ||'-- Update date: '||l_update_date);
167
168 EXCEPTION
169 WHEN NO_DATA_FOUND OR e_invalidActualException THEN
170 IF cr_actual%ISOPEN THEN CLOSE cr_actual; END IF;
171 RETURN;
172
173 WHEN OTHERS THEN
174 IF cr_actual%ISOPEN THEN CLOSE cr_actual; END IF;
175 RAISE;
176
177 END Post_Actual;
178
179 /*PROCEDURE get_trgt_level_orgs
180 ( p_target_lvl_short_name IN VARCHAR2
181 , x_orgtable OUT NOCOPY t_orgTable
182 )
183 IS
184 l_target_level_id NUMBER;
185 l_organization_id NUMBER;
186 l_time_level_name VARCHAR2(15);
187 l_calendar VARCHAR2(15);
188 l_start_end_date VARCHAR2(30);
189 l_msg VARCHAR2(100);
190 CURSOR c_orgs is
191 SELECT distinct sel.organization_id
192 FROM bis_user_ind_selections sel
193 WHERE sel.target_level_id = l_target_level_id;
194
195 BEGIN
196 -- get time_level
197 --
198 SELECT target_level_id, time_level_name
199 INTO l_target_level_id, l_time_level_name
200 FROM bis_target_levels_v
201 WHERE short_name = p_target_lvl_short_name;
202
203 -- get organization and the start date and end date of the current period
204 --
205 OPEN c_orgs;
206 FETCH c_orgs INTO l_organization_id;
207
208 WHILE c_orgs%FOUND LOOP
209 IF UPPER(l_time_level_name) = 'TOTAL_TIME' THEN
210 l_calendar := 'Accounting';
211 l_start_end_date := to_char(sysdate)||'+'||to_char(sysdate);
212
213 ELSIF UPPER(l_time_level_name) = 'HR MONTH' THEN
214 l_start_end_date := to_char(sysdate, 'DD-MM-YYYY')
215 ||'+'||to_char(add_months (sysdate, 1) -1,'DD-MM-YYYY');
216
217 ELSIF UPPER(l_time_level_name) = 'HR YEAR' THEN
218 l_start_end_date := to_char(sysdate, 'DD-MM-YYYY')
219 ||'+'||to_char(add_months (sysdate, 12) -1,'DD-MM-YYYY');
220
221 ELSIF UPPER(l_time_level_name) = 'HR QUARTER' THEN
222 l_start_end_date := to_char(sysdate, 'DD-MM-YYYY')
223 ||'+'||to_char(add_months (sysdate, 3) -1,'DD-MM-YYYY');
224
225 ELSE
226 Get_Indicator_Calendar
227 ( p_target_lvl_short_name
228 , l_organization_id
229 , l_calendar
230 , l_msg
231 );
232 l_start_end_date := Get_Start_End_Date
233 ( l_calendar
234 , l_time_level_name
235 );
236 END if;
237
238 -- fill in table
239 --
240 x_orgtable(l_organization_id) := l_start_end_date;
241 FETCH c_orgs INTO l_organization_id;
242
243 END LOOP;
244
245 IF c_orgs%ISOPEN THEN CLOSE c_orgs; END IF;
246
247 EXCEPTION
248 WHEN NO_DATA_FOUND THEN
249 IF c_orgs%ISOPEN THEN CLOSE c_orgs; END IF;
250 RETURN;
251
252 WHEN OTHERS THEN
253 IF c_orgs%ISOPEN THEN CLOSE c_orgs; END IF;
254 RAISE;
255
256 END get_trgt_level_orgs;*/
257
258 FUNCTION Get_Start_End_Date
259 ( p_calendar IN VARCHAR2
260 , p_period_type IN VARCHAR2
261 )
262 RETURN VARCHAR2
263 IS
264 x_start_end_date VARCHAR2(20);
265 l_start_date DATE;
266 l_end_date DATE;
267
268 cursor c_date IS
269 SELECT start_date, end_date
270 FROM gl_periods
271 WHERE UPPER(period_type) = UPPER(p_period_type)
272 AND UPPER(period_set_name) = UPPER(p_calendar);
273
274 BEGIN
275
276 OPEN c_date;
277 FETCH c_date INTO l_start_date, l_end_date;
278 WHILE c_date%found LOOP
279 IF sysdate >= l_start_date AND sysdate <= l_end_date THEN
280 x_start_end_date := to_char(l_start_date)||'+'||to_char(l_end_date);
281 CLOSE c_date;
282 RETURN x_start_end_date;
283 ELSE
284 FETCH c_date INTO l_start_date, l_end_date;
285 END IF;
286 END LOOP;
287
288 IF c_date%ISOPEN THEN CLOSE c_date; END IF;
289
290 EXCEPTION
291 WHEN NO_DATA_FOUND THEN
292 IF c_date%ISOPEN THEN CLOSE c_date; END IF;
293 RETURN ' ';
294 WHEN TOO_MANY_ROWS THEN
295 IF c_date%ISOPEN THEN CLOSE c_date; END IF;
296 RETURN TO_CHAR(sysdate)||'+'||to_char(sysdate);
297 WHEN OTHERS THEN
298 IF c_date%ISOPEN THEN CLOSE c_date; END IF;
299 RETURN to_char(sysdate)||'+'||to_char(sysdate);
300
301 END get_start_end_date;
302
303 /*PROCEDURE Get_SOB
304 ( p_organization_id IN NUMBER
305 , x_sob OUT NOCOPY NUMBER
306 , x_msg OUT NOCOPY VARCHAR2
307 )
308 IS
309 cursor c_sob is
310 SELECT TO_NUMBER(le.set_of_books_id)
311 FROM hr_legal_entities le
312 WHERE le.organization_id = p_organization_id
313 --
314 UNION SELECT TO_NUMBER(ou.set_of_books_id)
315 FROM hr_legal_entities le, hr_operating_units ou
316 WHERE ou.organization_id = p_organization_id
317 AND ou.legal_entity_id = le.organization_id
318 --
319 UNION SELECT od.set_of_books_id
320 FROM org_organization_definitions od
321 WHERE od.organization_id = p_organization_id;
322
323 BEGIN
324 OPEN c_sob;
325 FETCH c_sob INTO x_sob;
326 IF c_sob%FOUND THEN
327 x_msg := FND_API.G_RET_STS_SUCCESS;
328 ELSE
329 x_msg := FND_API.G_RET_STS_SUCCESS;
330 x_sob := -1;
331 END IF;
332
333 IF c_sob%ISOPEN THEN CLOSE c_sob; END IF;
334
335 EXCEPTION
336 WHEN OTHERS THEN
337 IF c_sob%ISOPEN THEN CLOSE c_sob; END IF;
338
339 END get_sob;*/
340
341 -- to get GL calendars only
342 /*PROCEDURE Get_Indicator_Calendar
343 ( p_target_lvl_short_name IN VARCHAR2
344 , p_organization_id IN NUMBER
345 , x_calendar OUT NOCOPY VARCHAR2
346 , x_msg OUT NOCOPY VARCHAR2
347 )
348 IS
349 sob_id gl_sets_of_books.set_of_books_id%TYPE;
350 l_msg VARCHAR2(100);
351
352 CURSOR c_cal IS
353 SELECT sob.period_set_name
354 FROM gl_periods gl,
355 gl_sets_of_books sob,
356 bis_levels l,
357 BIS_TARGET_LEVELS TL
358 WHERE
359 UPPER(TL.short_name) = UPPER(p_target_lvl_short_name)
360 AND TL.time_level_id = l.level_id
361 AND upper(l.short_name) = UPPER(gl.period_type)
362 AND sob_id = sob.set_of_books_id
363 AND gl.period_set_name = sob.period_set_name;
364
365 BEGIN
366
367 Get_Sob
368 ( p_organization_id
369 , sob_id
370 , l_msg);
371
372 OPEN c_cal;
373 FETCH c_cal INTO x_calendar;
374
375 IF c_cal%FOUND THEN
376 x_msg := 'ok';
377 CLOSE c_cal;
378 ELSE
379 x_calendar := 'Accounting';
380 x_msg := 'Defaulting to Accounting Calendar';
381 CLOSE c_cal;
382 END IF;
383
384 EXCEPTION
385 WHEN OTHERS THEN
386 IF c_cal%ISOPEN THEN CLOSE c_cal; END IF;
387
388 END get_indicator_calendar;*/
389
390 END BIS_PostActual;