[Home] [Help]
PACKAGE BODY: APPS.PSB_WS_PERIOD_TOTAL
Source
1 PACKAGE body PSB_WS_PERIOD_TOTAL as
2 /* $Header: PSBVWPTB.pls 120.8 2006/01/09 06:08:35 maniskum ship $ */
3
4
5 PROCEDURE Get_Totals
6 (
7 p_worksheet_id NUMBER,
8 p_profile_worksheet_id NUMBER,
9 p_budget_year_id NUMBER,
10 p_balance_type VARCHAR2,
11 p_user_id NUMBER,
12 p_template_id NUMBER,
13 p_account_flag VARCHAR2,
14 p_currency_flag VARCHAR2,
15 p_spkg_flag VARCHAR2,
16 p_spkg_selection_exists VARCHAR2,
17 /* Bug No 2543015 Start */
18 p_spkg_name VARCHAR2,
19 /* Bug No 2543015 End */
20 p_flexfield_low VARCHAR2,
21 p_flexfield_high VARCHAR2,
22 p_flexfield_delimiter VARCHAR2,
23 p_chart_of_accounts NUMBER,
24 p1_amount OUT NOCOPY NUMBER,
25 p2_amount OUT NOCOPY NUMBER,
26 p3_amount OUT NOCOPY NUMBER,
27 p4_amount OUT NOCOPY NUMBER,
28 p5_amount OUT NOCOPY NUMBER,
29 p6_amount OUT NOCOPY NUMBER,
30 p7_amount OUT NOCOPY NUMBER,
31 p8_amount OUT NOCOPY NUMBER,
32 p9_amount OUT NOCOPY NUMBER,
33 p10_amount OUT NOCOPY NUMBER,
34 p11_amount OUT NOCOPY NUMBER,
35 p12_amount OUT NOCOPY NUMBER,
36 p_year_amount OUT NOCOPY NUMBER
37 )
38 IS
39
40
41 l1_amount NUMBER;
42 l2_amount NUMBER;
43 l3_amount NUMBER;
44 l4_amount NUMBER;
45 l5_amount NUMBER;
46 l6_amount NUMBER;
47 l7_amount NUMBER;
48 l8_amount NUMBER;
49 l9_amount NUMBER;
50 l10_amount NUMBER;
51 l11_amount NUMBER;
52 l12_amount NUMBER;
53 l_year_amount NUMBER;
54
55
56 l_sql VARCHAR2(5000);
57 l_ignore INTEGER;
58 l_cursor_id INTEGER;
59
60 l_max_num_of_segments NUMBER;
61 l_num_of_low_segments NUMBER;
62 l_num_of_high_segments NUMBER;
63
64 query_condition VARCHAR2(2000) default NULL;
65 high_value VARCHAR2(240);
66 low_value VARCHAR2(240);
67
68 high_segments fnd_flex_ext.SegmentArray;
69 low_segments fnd_flex_ext.SegmentArray;
70 delim VARCHAR2(1);
71
72 l_flex_condition VARCHAR2(2000);
73 l_segment_condition VARCHAR2(2000);
74 l_segment_num NUMBER;
75 l_segment_col VARCHAR2(30);
76 l_acct_type NUMBER(2);
77
78 /* Start bug #4924031 */
79 l_id_flex_code fnd_id_flex_structures.id_flex_code%TYPE;
80 l_application_id fnd_id_flex_structures.application_id%TYPE;
81 /* End bug #4924031 */
82
83 CURSOR flex_cur is
84 SELECT APPLICATION_COLUMN_NAME
85 FROM FND_ID_FLEX_SEGMENTS
86 WHERE ID_FLEX_CODE = l_id_flex_code -- bug #4924031
87 AND application_id = l_application_id -- bug #4924031
88 AND ID_FLEX_NUM = p_chart_of_accounts
89 AND SEGMENT_NUM = l_segment_num;
90
91 flex_rec flex_cur%ROWTYPE;
92
93 BEGIN
94
95 /* Start bug #4924031 */
96 l_id_flex_code := 'GL#';
97 l_application_id := 101;
98 /* End bug #4924031 */
99
100 delim := fnd_flex_ext.get_delimiter(
101 application_short_name => 'SQLGL',
102 key_flex_code => 'GL#',
103 structure_number => p_chart_of_accounts
104 );
105
106 -- breakup the segments into an array
107 l_num_of_low_segments := 0;
108 l_num_of_high_segments := 0;
109 IF p_flexfield_low IS NOT NULL then
110 l_num_of_low_segments := fnd_flex_ext.breakup_segments
111 (concatenated_segs => p_flexfield_low,
112 delimiter => delim,
113 segments => low_segments);
114 END IF;
115
116 IF p_flexfield_high IS NOT NULL then
117 l_num_of_high_segments := fnd_flex_ext.breakup_segments
118 (concatenated_segs => p_flexfield_high,
119 delimiter => delim,
120 segments => high_segments);
121 END IF;
122
123 l_max_num_of_segments := greatest(l_num_of_low_segments,l_num_of_high_segments);
124
125
126 --
127 -- build the query condition.
128 --
129 query_condition := 'AND 1 = 1 ';
130 l_segment_num := 0;
131 IF l_max_num_of_segments >= 1 THEN
132
133 for i in 1.. l_max_num_of_segments
134 loop
135
136 l_segment_num := l_segment_num + 1;
137 OPEN flex_cur;
138 FETCH flex_cur INTO flex_rec;
139 IF flex_cur%FOUND THEN
140 l_segment_col := flex_rec.APPLICATION_COLUMN_NAME;
141 END IF;
142 CLOSE flex_cur;
143
144 --dbms_output.put_line('Segment Number : ' || l_segment_num);
145 --dbms_output.put_line('Segment Name : ' || l_segment_col);
146
147 IF low_segments.exists(i) THEN
148 low_value := low_segments(i);
149 END IF;
150
151 IF high_segments.exists(i) THEN
152 high_value := high_segments(i);
153 END IF;
154
155 --dbms_output.put_line('Low Value :' || low_value);
156 --dbms_output.put_line('High Value :' || high_value);
157
158
159 -- Build the query condition.
160 --
161 IF ((low_value IS NOT NULL) AND (high_value IS NULL)) THEN
162 query_condition := query_condition ||' AND ' || l_segment_col || ' >= ' || '''' ||low_value|| '''';
163
164 ELSIF ((low_value IS NULL) AND (high_value IS NOT NULL)) THEN
165 query_condition := query_condition ||' AND ' || l_segment_col || '<= ' || '''' || high_value|| '''';
166
167 ELSIF (low_value = high_value) THEN
168 query_condition := query_condition ||' AND ' ||l_segment_col || ' Like '|| '''' ||low_value|| '''';
169
170 ELSIF ((low_value IS NOT NULL) AND (high_value IS NOT NULL)) THEN
171 query_condition := query_condition ||' AND ' ||l_segment_col || ' BETWEEN ' ||
172 ''''|| low_value || '''' || ' AND ' || ''''|| high_value|| '''';
173
174 END IF;
175
176
177 end loop;
178
179
180 END IF;
181
182 --dbms_output.put_line(query_condition);
183
184 /* Bug No 3140882 Start */
185
186 IF p_account_flag = 'A' OR p_account_flag = 'E' OR p_account_flag = 'D' THEN
187 l_acct_type := -1;
188 ELSE
189 l_acct_type := 1;
190 END IF;
191
192 l_sql := 'SELECT ' ||
193 ' NVL(SUM(decode(account_type,''A'',-period1_amount,''E'',-period1_amount,''D'',-period1_amount,period1_amount)),0) * :b_acct_type A ' ||
194 ' ,NVL(SUM(decode(account_type,''A'',-period2_amount,''E'',-period2_amount,''D'',-period2_amount,period2_amount)),0) * :b_acct_type B ' ||
195 ' ,NVL(SUM(decode(account_type,''A'',-period3_amount,''E'',-period3_amount,''D'',-period3_amount,period3_amount)),0) * :b_acct_type C ' ||
196 ' ,NVL(SUM(decode(account_type,''A'',-period4_amount,''E'',-period4_amount,''D'',-period4_amount,period4_amount)),0) * :b_acct_type D ' ||
197 ' ,NVL(SUM(decode(account_type,''A'',-period5_amount,''E'',-period5_amount,''D'',-period5_amount,period5_amount)),0) * :b_acct_type E ' ||
198 ' ,NVL(SUM(decode(account_type,''A'',-period6_amount,''E'',-period6_amount,''D'',-period6_amount,period6_amount)),0) * :b_acct_type F ' ||
199 ' ,NVL(SUM(decode(account_type,''A'',-period7_amount,''E'',-period7_amount,''D'',-period7_amount,period7_amount)),0) * :b_acct_type G ' ||
200 ' ,NVL(SUM(decode(account_type,''A'',-period8_amount,''E'',-period8_amount,''D'',-period8_amount,period8_amount)),0) * :b_acct_type H ' ||
201 ' ,NVL(SUM(decode(account_type,''A'',-period9_amount,''E'',-period9_amount,''D'',-period9_amount,period9_amount)),0) * :b_acct_type I ' ||
202 ' ,NVL(SUM(decode(account_type,''A'',-period10_amount,''E'',-period10_amount,''D'',-period10_amount,period10_amount)),0) * :b_acct_type J ' ||
203 ' ,NVL(SUM(decode(account_type,''A'',-period11_amount,''E'',-period11_amount,''D'',-period11_amount,period11_amount)),0) * :b_acct_type K ' ||
204 ' ,NVL(SUM(decode(account_type,''A'',-period12_amount,''E'',-period12_amount,''D'',-period12_amount,period12_amount)),0) * :b_acct_type L ' ||
205 ' ,NVL(SUM(decode(account_type,''A'',-ytd_amount,''E'',-ytd_amount,''D'',-ytd_amount,ytd_amount)),0) * :b_acct_type M ' ||
206 ' FROM psb_ws_line_period_v WLP ' ||
207 ' WHERE worksheet_id = :b_worksheet_id ' ||
208 ' AND budget_year_id = :b_budget_year_id ' ||
209 ' AND balance_type = :b_balance_type ' ||
210 ' AND (:b_account_flag = ''T'' OR account_type = :b_account_flag ' ||
211 ' OR (account_type = DECODE(:b_account_flag,''P'',''R'',''~'') OR account_type = DECODE(:b_account_flag,''P'',''E'',''~'')) '||
212 ' OR (account_type = DECODE(:b_account_flag,''N'',''A'',''~'') OR account_type = DECODE(:b_account_flag,''N'',''L'',''~'')) '||
213 ' OR (account_type = DECODE(:b_account_flag,''B'',''D'',''~'') OR account_type = DECODE(:b_account_flag,''B'',''C'',''~'')) '||
214 ' ) '||
215 /* Bug No 3140882 End */
216 ' AND ( (:b_currency_flag = ''C'' AND currency_code <> ''STAT'') ' ||
217 ' OR ' ||
218 ' (:b_currency_flag = ''S'' AND currency_code = ''STAT'') ' ||
219 ' ) ' ||
220 ' AND ( (:b_template_id is NULL AND template_id is null) ' ||
221 ' OR ' ||
222 ' (:b_template_id is NOT NULL AND template_id = :b_template_id) ' ||
223 ' ) ' ||
224 /* Bug No 2543015 Start */
225 ' AND ( :b_spkg_flag = ''A'' ' ||
226 ' OR (:b_spkg_selection_exists = ''N'' ' ||
227 ' AND service_package_id in ( select sp.service_package_id ' ||
228 ' from PSB_SERVICE_PACKAGES sp, PSB_WORKSHEETS w ' ||
229 ' where sp.global_worksheet_id = nvl(w.global_worksheet_id, w.worksheet_id) ' ||
230 ' and w.worksheet_id = :b_profile_worksheet_id ' ||
231 ' and sp.name like :b_spkg_name) ' ||
232 ' ) ' ||
233 ' OR (:b_spkg_selection_exists = ''Y'' ' ||
234 ' AND service_package_id in ( select service_package_id ' ||
235 ' from PSB_WS_SERVICE_PKG_PROFILES_V ' ||
236 ' where worksheet_id = :b_profile_worksheet_id ' ||
237 ' and (user_id = :b_user_id or (:b_user_id is null and user_id is null)) ' ||
238 ' and service_package_name like :b_spkg_name) ' ||
239 ' ) ' ||
240 ' ) ' ;
241 /* Bug No 2543015 End */
242
243
244 l_flex_condition := ' and (code_combination_id ' ||
245 ' = (select code_combination_id from gl_code_combinations '||
246 ' where WLP.code_combination_id = code_combination_id '||
247 ' and chart_of_accounts_id = :b_chart_of_accounts ' ||
248 query_condition || '))' ;
249
250 l_sql := l_sql || l_flex_condition;
251
252 l_cursor_id := dbms_sql.open_cursor;
253
254 -- Parsing the statement.
255 dbms_sql.parse(l_cursor_id, l_sql, dbms_sql.v7);
256
257 -- Bind input variables
258 dbms_sql.bind_variable(l_cursor_id, ':b_acct_type', l_acct_type);
259 dbms_sql.bind_variable(l_cursor_id, ':b_worksheet_id', p_worksheet_id);
260 dbms_sql.bind_variable(l_cursor_id, ':b_account_flag', p_account_flag);
261 dbms_sql.bind_variable(l_cursor_id, ':b_currency_flag',p_currency_flag);
262 dbms_sql.bind_variable(l_cursor_id, ':b_template_id', p_template_id);
263 dbms_sql.bind_variable(l_cursor_id, ':b_spkg_flag', p_spkg_flag);
264 dbms_sql.bind_variable(l_cursor_id, ':b_spkg_selection_exists', p_spkg_selection_exists);
265 /* Bug No 2543015 Start */
266 dbms_sql.bind_variable(l_cursor_id, ':b_spkg_name', p_spkg_name);
267 /* Bug No 2543015 End */
268 dbms_sql.bind_variable(l_cursor_id, ':b_user_id', p_user_id);
269 dbms_sql.bind_variable(l_cursor_id, ':b_chart_of_accounts', p_chart_of_accounts);
270 dbms_sql.bind_variable(l_cursor_id, ':b_budget_year_id', p_budget_year_id);
271 dbms_sql.bind_variable(l_cursor_id, ':b_balance_type', p_balance_type);
272 dbms_sql.bind_variable(l_cursor_id, ':b_profile_worksheet_id', p_profile_worksheet_id);
273
274 -- define output variables
275
276 dbms_sql.define_column(l_cursor_id, 1, l1_amount);
277 dbms_sql.define_column(l_cursor_id, 2, l2_amount);
278 dbms_sql.define_column(l_cursor_id, 3, l3_amount);
279 dbms_sql.define_column(l_cursor_id, 4, l4_amount);
280 dbms_sql.define_column(l_cursor_id, 5, l5_amount);
281 dbms_sql.define_column(l_cursor_id, 6, l6_amount);
282 dbms_sql.define_column(l_cursor_id, 7, l7_amount);
283 dbms_sql.define_column(l_cursor_id, 8, l8_amount);
284 dbms_sql.define_column(l_cursor_id, 9, l9_amount);
285 dbms_sql.define_column(l_cursor_id, 10, l10_amount);
286 dbms_sql.define_column(l_cursor_id, 11, l11_amount);
287 dbms_sql.define_column(l_cursor_id, 12, l12_amount);
288 dbms_sql.define_column(l_cursor_id, 13, l_year_amount);
289
290 -- execute
291 l_ignore := dbms_sql.execute(l_cursor_id);
292 -- fetch
293 l_ignore := dbms_sql.fetch_rows(l_cursor_id );
294
295 -- retrieve the value
296 dbms_sql.column_value(l_cursor_id,1,l1_amount);
297 dbms_sql.column_value(l_cursor_id,2,l2_amount);
298 dbms_sql.column_value(l_cursor_id,3,l3_amount);
299 dbms_sql.column_value(l_cursor_id,4,l4_amount);
300 dbms_sql.column_value(l_cursor_id,5,l5_amount);
301 dbms_sql.column_value(l_cursor_id,6,l6_amount);
302 dbms_sql.column_value(l_cursor_id,7,l7_amount);
303 dbms_sql.column_value(l_cursor_id,8,l8_amount);
304 dbms_sql.column_value(l_cursor_id,9,l9_amount);
305 dbms_sql.column_value(l_cursor_id,10,l10_amount);
306 dbms_sql.column_value(l_cursor_id,11,l11_amount);
307 dbms_sql.column_value(l_cursor_id,12,l12_amount);
308 dbms_sql.column_value(l_cursor_id,13,l_year_amount);
309
310 p1_amount := l1_amount;
311 p2_amount := l2_amount;
312 p3_amount := l3_amount;
313 p4_amount := l4_amount;
314 p5_amount := l5_amount;
318 p9_amount := l9_amount;
315 p6_amount := l6_amount;
316 p7_amount := l7_amount;
317 p8_amount := l8_amount;
319 p10_amount := l10_amount;
320 p11_amount := l11_amount;
321 p12_amount := l12_amount;
322 p_year_amount := l_year_amount;
323
324 -- close the cursor
325 dbms_sql.close_cursor(l_cursor_id);
326
327
328 END Get_Totals;
329
330
331
332 PROCEDURE Get_Data_Selection_Profile
333 (
334 p_current_worksheet_id IN NUMBER,
335 p_current_user_id IN NUMBER,
336 p_global_profile_user_id IN NUMBER,
337 p_profile_worksheet_id OUT NOCOPY NUMBER,
338 p_profile_user_id OUT NOCOPY NUMBER
339 )
340 IS
341
342 l_global_worksheet_flag varchar2(1):= NULL;
343 l_global_worksheet_id psb_worksheets.global_worksheet_id%TYPE;
344 l_local_copy_flag varchar2(1):= NULL;
345 l_parent_worksheet_id psb_worksheets.copy_of_worksheet_id%TYPE;
346 l_dummy varchar2(1) := '0';
347 /* for bug no 3564160 */
348 l_profile_flag VARCHAR2(1) := '0';
349 l_inherit_profile varchar2(3) := NULL;
350
351 Cursor C (v_ws_id Number, v_user_id Number) IS
352 SELECT '1'
353 FROM psb_ws_user_profiles
354 WHERE worksheet_id = v_ws_id
355 AND user_id = v_user_id;
356
357 Cursor C_global(v_global_worksheet_id Number) IS
358 SELECT '1'
359 FROM psb_ws_user_profiles
360 WHERE worksheet_id = v_global_worksheet_id
361 AND user_id IS NULL;
362
363 Cursor C_CurrWS IS
364 SELECT global_worksheet_flag,
365 global_worksheet_id,
366 local_copy_flag,
367 copy_of_worksheet_id
368 FROM psb_worksheets
369 WHERE worksheet_id = p_current_worksheet_id;
370
371 BEGIN
372 l_inherit_profile := FND_PROFILE.VALUE('PSB_INHERIT_DATA_SELECTION_PROFILE');
373
374 FOR C_rec IN C(p_current_worksheet_id, p_current_user_id)
375 loop
376 l_profile_flag := '1';
377 end loop;
378
379 IF l_profile_flag = '1' THEN
380 p_profile_worksheet_id := p_current_worksheet_id;
381 p_profile_user_id := p_current_user_id;
382 ELSE
383 FOR C_CurrWS_rec IN C_CurrWS
384 Loop
385 l_global_worksheet_flag := C_CurrWS_rec.global_worksheet_flag;
386 l_global_worksheet_id := C_CurrWS_rec.global_worksheet_id;
387 l_local_copy_flag := C_CurrWS_rec.local_copy_flag;
388 l_parent_worksheet_id := C_CurrWS_rec.copy_of_worksheet_id;
389 End Loop;
390
391 IF l_global_worksheet_flag = 'Y' THEN
392
393 Begin
394 FOR C_global_rec IN C_global(p_current_worksheet_id)
395 loop
396 l_profile_flag := '2';
397 end loop;
398 IF l_profile_flag = '2' THEN
399 p_profile_worksheet_id := p_current_worksheet_id;
400 p_profile_user_id := p_global_profile_user_id;
401 RETURN;
402 ELSE
403 p_profile_worksheet_id := p_current_worksheet_id;
404 p_profile_user_id := p_current_user_id;
405 RETURN;
406 END IF;
407 End;
408
409 /* For Bug No. 2544320 : Start
410 If profile option 'Inherit Global Profile' is NULL, it should be defaulted to 'No'. */
411 -- ELSIF nvl(l_inherit_profile, 'Y') = 'Y' THEN
412 ELSIF nvl(l_inherit_profile, 'N') = 'Y' THEN
413 /* For Bug No. 2544320 : End */
414
415 Begin
416 IF l_local_copy_flag = 'Y' THEN
417 FOR C_rec IN C(l_parent_worksheet_id, p_current_user_id)
418 loop
419 l_profile_flag := '3';
420 end loop;
421
422 IF l_profile_flag = '3' THEN
423 p_profile_worksheet_id := l_parent_worksheet_id;
424 p_profile_user_id := p_current_user_id;
425 RETURN;
426 ELSE
427 /* start bug 3564160 */
428 -- check for global data
429 FOR C_Global_Rec IN C_Global (l_global_worksheet_id)
430 LOOP
431 l_profile_flag := '4';
432 END LOOP;
433
434 IF l_profile_flag = '4' THEN
435 p_profile_worksheet_id := l_global_worksheet_id;
436 p_profile_user_id := p_global_profile_user_id;
437 RETURN;
438 ELSE
439 p_profile_worksheet_id := p_current_worksheet_id;
440 p_profile_user_id := p_current_user_id;
441 RETURN;
442 END IF;
443 /* end bug 3564160 */
444 END IF;
445 ELSE
446 FOR C_global_rec IN C_global(l_global_worksheet_id)
447 loop
448 l_profile_flag := '4';
449 end loop;
450 IF l_profile_flag = '4' THEN
451 p_profile_worksheet_id := l_global_worksheet_id;
452 p_profile_user_id := p_global_profile_user_id;
453 RETURN;
454 ELSE
455 p_profile_worksheet_id := p_current_worksheet_id;
456 p_profile_user_id := p_current_user_id;
457 RETURN;
458 END IF;
459 END IF;
460 End;
461
462 ELSE
463 /* start bug 3564160 */
464 IF l_local_copy_flag = 'Y' THEN
465 -- check it with parent
466 FOR C_rec IN C(l_parent_worksheet_id, p_current_user_id)
467 LOOP
468 l_profile_flag := '5';
469 END LOOP;
470
471 IF l_profile_flag = '5' THEN
472 p_profile_worksheet_id := l_parent_worksheet_id;
473 p_profile_user_id := p_current_user_id;
474 RETURN;
475 END IF;
476 END IF;
477 /* end bug 3564160 */
478
479 p_profile_worksheet_id := p_current_worksheet_id;
480 p_profile_user_id := p_current_user_id;
481 END IF;
482
483 END IF;
484
485 End Get_Data_Selection_Profile;
486
487
488
489 END PSB_WS_PERIOD_TOTAL;