[Home] [Help]
PACKAGE BODY: APPS.PSB_WS_MATRIX
Source
1 PACKAGE body PSB_WS_MATRIX as
2 /* $Header: PSBVWSMB.pls 115.14 2003/12/02 11:25:40 vbellur ship $ */
3 ------------------------------------------------------------------------------------------
4 -- Worksheet Line Year
5 ------------------------------------------------------------------------------------------
6
7 PROCEDURE Set_Form_WS_Line_Years
8 (
9 p_session_id IN NUMBER,
10 p_worksheet_id IN NUMBER,
11 p_modify_ws IN VARCHAR2 := 'N',
12 p_stage IN NUMBER,
13 /*For Bug No : 1756051 Start*/
14 p_account_flag IN VARCHAR2,
15 /*For Bug No : 1756051 End*/
16 p1_year_id IN NUMBER,
17 p2_year_id IN NUMBER,
18 p3_year_id IN NUMBER,
19 p4_year_id IN NUMBER,
20 p5_year_id IN NUMBER,
21 p6_year_id IN NUMBER,
22 p7_year_id IN NUMBER,
23 p8_year_id IN NUMBER,
24 p9_year_id IN NUMBER,
25 p10_year_id IN NUMBER,
26 p11_year_id IN NUMBER,
27 p12_year_id IN NUMBER,
28 p1_amount_type IN VARCHAR2,
29 p2_amount_type IN VARCHAR2,
30 p3_amount_type IN VARCHAR2,
31 p4_amount_type IN VARCHAR2,
32 p5_amount_type IN VARCHAR2,
33 p6_amount_type IN VARCHAR2,
34 p7_amount_type IN VARCHAR2,
35 p8_amount_type IN VARCHAR2,
36 p9_amount_type IN VARCHAR2,
37 p10_amount_type IN VARCHAR2,
38 p11_amount_type IN VARCHAR2,
39 p12_amount_type IN VARCHAR2,
40 p_total_flag IN VARCHAR2 := 'N'
41
42 )IS
43 --added for Bug : 1756051
44 l_account_flag VARCHAR2(1);
45 BEGIN
46
47 /*For Bug No : 1756051 Start*/
48 --here the account flag is coverted to two types
49 --X = decrease type, which is valid for Assests, Expenses, and Assets-Expenses
50 --Y = for all other types
51 If(p_account_flag IN ('A','E','N','D')) then
52 l_account_flag := 'X';
53 Else
54 l_account_flag := 'Y';
55 End if;
56 /*For Bug No : 1756051 End*/
57
58 /* Added the following If condition for bug 3206280 */
59 If nvl(p_modify_ws,'N') = 'Y' then
60 Update psb_worksheet_context
61 Set
62 stage = p_stage,
63 account_flag = l_account_flag,
64 year1_id = p1_year_id,
65 year2_id = p2_year_id,
66 year3_id = p3_year_id,
67 year4_id = p4_year_id,
68 year5_id = p5_year_id,
69 year6_id = p6_year_id,
70 year7_id = p7_year_id,
71 year8_id = p8_year_id,
72 year9_id = p9_year_id,
73 year10_id = p10_year_id,
74 year11_id = p11_year_id,
75 year12_id = p12_year_id,
76 amount1_type = p1_amount_type,
77 amount2_type = p2_amount_type,
78 amount3_type = p3_amount_type,
79 amount4_type = p4_amount_type,
80 amount5_type = p5_amount_type,
81 amount6_type = p6_amount_type,
82 amount7_type = p7_amount_type,
83 amount8_type = p8_amount_type,
84 amount9_type = p9_amount_type,
85 amount10_type = p10_amount_type,
86 amount11_type = p11_amount_type,
87 amount12_type = p12_amount_type,
88 total_flag = p_total_flag
89 Where worksheet_id = p_worksheet_id
90 and session_id = p_session_id;
91
92 If SQL%NOTFOUND Then
93
94 Insert Into psb_worksheet_context
95 (
96 session_id ,
97 worksheet_id ,
98 stage ,
99 account_flag ,
100 year1_id ,
101 year2_id ,
102 year3_id ,
103 year4_id ,
104 year5_id ,
105 year6_id ,
106 year7_id ,
107 year8_id ,
108 year9_id ,
109 year10_id ,
110 year11_id ,
111 year12_id ,
112 amount1_type ,
113 amount2_type ,
114 amount3_type ,
115 amount4_type ,
116 amount5_type ,
117 amount6_type ,
118 amount7_type ,
119 amount8_type ,
120 amount9_type ,
121 amount10_type ,
122 amount11_type ,
123 amount12_type ,
124 total_flag
125 )
126 Values
127 (
128 p_session_id ,
129 p_worksheet_id ,
130 p_stage ,
131 p_account_flag ,
132 p1_year_id ,
133 p2_year_id ,
134 p3_year_id ,
135 p4_year_id ,
136 p5_year_id ,
137 p6_year_id ,
138 p7_year_id ,
139 p8_year_id ,
140 p9_year_id ,
141 p10_year_id ,
142 p11_year_id ,
143 p12_year_id ,
144 p1_amount_type ,
145 p2_amount_type ,
146 p3_amount_type ,
147 p4_amount_type ,
148 p5_amount_type ,
149 p6_amount_type ,
150 p7_amount_type ,
151 p8_amount_type ,
152 p9_amount_type ,
153 p10_amount_type,
154 p11_amount_type,
155 p12_amount_type,
156 p_total_flag
157 );
158
159 End If;
160 End If; -- End If p_modify_Ws = 'Y'
161 ws_line_year_rec.stage := p_stage ;
162 /*For Bug No : 1756051 Start*/
163 ws_line_year_rec.account_flag := l_account_flag ;
164 /*For Bug No : 1756051 End*/
165 ws_line_year_rec.c1_year_id := p1_year_id ;
166 ws_line_year_rec.c2_year_id := p2_year_id ;
167 ws_line_year_rec.c3_year_id := p3_year_id ;
168 ws_line_year_rec.c4_year_id := p4_year_id ;
169 ws_line_year_rec.c5_year_id := p5_year_id ;
170 ws_line_year_rec.c6_year_id := p6_year_id ;
171 ws_line_year_rec.c7_year_id := p7_year_id ;
172 ws_line_year_rec.c8_year_id := p8_year_id ;
173 ws_line_year_rec.c9_year_id := p9_year_id ;
174 ws_line_year_rec.c10_year_id := p10_year_id ;
175 ws_line_year_rec.c11_year_id := p11_year_id ;
176 ws_line_year_rec.c12_year_id := p12_year_id ;
177 ws_line_year_rec.c1_amount_type := p1_amount_type ;
178 ws_line_year_rec.c2_amount_type := p2_amount_type ;
179 ws_line_year_rec.c3_amount_type := p3_amount_type ;
180 ws_line_year_rec.c4_amount_type := p4_amount_type ;
181 ws_line_year_rec.c5_amount_type := p5_amount_type ;
182 ws_line_year_rec.c6_amount_type := p6_amount_type ;
183 ws_line_year_rec.c7_amount_type := p7_amount_type ;
184 ws_line_year_rec.c8_amount_type := p8_amount_type ;
185 ws_line_year_rec.c9_amount_type := p9_amount_type ;
186 ws_line_year_rec.c10_amount_type := p10_amount_type ;
187 ws_line_year_rec.c11_amount_type := p11_amount_type ;
188 ws_line_year_rec.c12_amount_type := p12_amount_type ;
189
190 commit work;
191
192 END;
193
194
195
196
197
198 PROCEDURE Set_WS_Line_Years
199 (
200 p_ws_line_year_rec IN ws_line_year_rec_type
201 )
202 IS
203 BEGIN
204 ws_line_year_rec := p_ws_line_year_rec;
205 END;
206
207
208 FUNCTION Display_year_id RETURN NUMBER IS
209 BEGIN
210 Return ws_line_year_rec.c2_year_id;
211 END Display_year_id;
212
213 FUNCTION Display_year_type RETURN VARCHAR2 IS
214 BEGIN
215 Return ws_line_year_rec.c1_amount_type;
216 END Display_year_type;
217
218
219 -- Local function that indicates what to return - (F)TE, (A)mount, (N)othing
220 FUNCTION Amount_OR_FTE(p_budget_year_id IN NUMBER,p_amount_type IN VARCHAR2,
221 p_rec_year_id IN NUMBER, p_rec_amount_type IN VARCHAR2)
222 RETURN VARCHAR2
223 IS
224 BEGIN
225 IF p_budget_year_id = p_rec_year_id THEN
226 IF p_rec_amount_type = 'P' AND p_amount_type = 'E' THEN
227 RETURN 'P';
228 ELSIF p_rec_amount_type = 'F' THEN
229 RETURN 'F';
230 ELSIF p_rec_amount_type = p_amount_type THEN
231 RETURN 'A';
232 END IF;
233 END IF;
234 RETURN 'N';
235 END Amount_OR_FTE;
236
237 FUNCTION Get_WS_Line_Year_ST RETURN VARCHAR2
238 IS
239 BEGIN
240 RETURN ws_line_year_rec.stage;
241 END;
242
243 FUNCTION Get_WS_Line_YearC1(p_budget_year_id IN NUMBER,p_amount_type IN VARCHAR2) RETURN VARCHAR2
244 IS
245 BEGIN
246 return Amount_OR_FTE(p_budget_year_id,p_amount_type,ws_line_year_rec.c1_year_id,ws_line_year_rec.c1_amount_type);
247 END Get_WS_Line_YearC1;
248
249 FUNCTION Get_WS_Line_YearC2(p_budget_year_id IN NUMBER,p_amount_type IN VARCHAR2) RETURN VARCHAR2
250 IS
251 BEGIN
252 return Amount_OR_FTE(p_budget_year_id,p_amount_type,ws_line_year_rec.c2_year_id,ws_line_year_rec.c2_amount_type);
253 END Get_WS_Line_YearC2;
254
255 FUNCTION Get_WS_Line_YearC3(p_budget_year_id IN NUMBER,p_amount_type IN VARCHAR2) RETURN VARCHAR2
256 IS
257 BEGIN
258 return Amount_OR_FTE(p_budget_year_id,p_amount_type,ws_line_year_rec.c3_year_id,ws_line_year_rec.c3_amount_type);
259 END Get_WS_Line_YearC3;
260
261 FUNCTION Get_WS_Line_YearC4(p_budget_year_id IN NUMBER,p_amount_type IN VARCHAR2) RETURN VARCHAR2
262 IS
263 BEGIN
264 return Amount_OR_FTE(p_budget_year_id,p_amount_type,ws_line_year_rec.c4_year_id,ws_line_year_rec.c4_amount_type);
265 END Get_WS_Line_YearC4;
266
267 FUNCTION Get_WS_Line_YearC5(p_budget_year_id IN NUMBER,p_amount_type IN VARCHAR2) RETURN VARCHAR2
268 IS
269 BEGIN
270 return Amount_OR_FTE(p_budget_year_id,p_amount_type,ws_line_year_rec.c5_year_id,ws_line_year_rec.c5_amount_type);
271 END Get_WS_Line_YearC5;
272
273 FUNCTION Get_WS_Line_YearC6(p_budget_year_id IN NUMBER,p_amount_type IN VARCHAR2) RETURN VARCHAR2
274 IS
275 BEGIN
276 return Amount_OR_FTE(p_budget_year_id,p_amount_type,ws_line_year_rec.c6_year_id,ws_line_year_rec.c6_amount_type);
277 END Get_WS_Line_YearC6;
278
279 FUNCTION Get_WS_Line_YearC7(p_budget_year_id IN NUMBER,p_amount_type IN VARCHAR2) RETURN VARCHAR2
280 IS
281 BEGIN
282 return Amount_OR_FTE(p_budget_year_id,p_amount_type,ws_line_year_rec.c7_year_id,ws_line_year_rec.c7_amount_type);
283 END Get_WS_Line_YearC7;
284
285 FUNCTION Get_WS_Line_YearC8(p_budget_year_id IN NUMBER,p_amount_type IN VARCHAR2) RETURN VARCHAR2
286 IS
287 BEGIN
288 return Amount_OR_FTE(p_budget_year_id,p_amount_type,ws_line_year_rec.c8_year_id,ws_line_year_rec.c8_amount_type);
289 END Get_WS_Line_YearC8;
290
291 FUNCTION Get_WS_Line_YearC9(p_budget_year_id IN NUMBER,p_amount_type IN VARCHAR2) RETURN VARCHAR2
292 IS
293 BEGIN
294 return Amount_OR_FTE(p_budget_year_id,p_amount_type,ws_line_year_rec.c9_year_id,ws_line_year_rec.c9_amount_type);
295 END Get_WS_Line_YearC9;
296
297 FUNCTION Get_WS_Line_YearC10(p_budget_year_id IN NUMBER,p_amount_type IN VARCHAR2) RETURN VARCHAR2
298 IS
299 BEGIN
300 return Amount_OR_FTE(p_budget_year_id,p_amount_type,ws_line_year_rec.c10_year_id,ws_line_year_rec.c10_amount_type);
301 END Get_WS_Line_YearC10;
302
303 FUNCTION Get_WS_Line_YearC11(p_budget_year_id IN NUMBER,p_amount_type IN VARCHAR2) RETURN VARCHAR2
304 IS
305 BEGIN
306 return Amount_OR_FTE(p_budget_year_id,p_amount_type,ws_line_year_rec.c11_year_id,ws_line_year_rec.c11_amount_type);
307 END Get_WS_Line_YearC11;
308
309 FUNCTION Get_WS_Line_YearC12(p_budget_year_id IN NUMBER,p_amount_type IN VARCHAR2) RETURN VARCHAR2
310 IS
311 BEGIN
312 return Amount_OR_FTE(p_budget_year_id,p_amount_type,ws_line_year_rec.c12_year_id,ws_line_year_rec.c12_amount_type);
313 END Get_WS_Line_YearC12;
314
315
316 ------------------------------------------------------------------------------------------
317 -- Worksheet Line Periods
318 ------------------------------------------------------------------------------------------
319
320 PROCEDURE Set_WS_Line_Periods
321 (
322 p_ws_line_period_rec IN ws_line_period_rec_type
323 )
324 IS
325 BEGIN
326 ws_line_period_rec := p_ws_line_period_rec;
327 END;
328
329
330 -- Only Stage is used in the version 1.0
331 PROCEDURE Set_Form_WS_Line_Periods
332 (
333 p_stage IN NUMBER
334 )IS
335 BEGIN
336 ws_line_period_rec.stage := p_stage;
337 END;
338
339
340 FUNCTION Get_WS_Line_Period_ST RETURN NUMBER
341 IS
342 BEGIN
343 RETURN ws_line_period_rec.stage;
344 END;
345
346 FUNCTION Get_WS_Line_Period1 RETURN NUMBER
347 IS
348 BEGIN
349 RETURN ws_line_period_rec.period1;
350 END;
351
352 FUNCTION Get_WS_Line_Period2 RETURN NUMBER
353 IS
354 BEGIN
355 RETURN ws_line_period_rec.period2;
356 END;
357
358 FUNCTION Get_WS_Line_Period3 RETURN NUMBER
359 IS
360 BEGIN
361 RETURN ws_line_period_rec.period3;
362 END;
363
364 FUNCTION Get_WS_Line_Period4 RETURN NUMBER
365 IS
366 BEGIN
367 RETURN ws_line_period_rec.period4;
368 END;
369
370 FUNCTION Get_WS_Line_Period5 RETURN NUMBER
371 IS
372 BEGIN
373 RETURN ws_line_period_rec.period5;
374 END;
375
376
377 FUNCTION Get_WS_Line_Period6 RETURN NUMBER
378 IS
379 BEGIN
380 RETURN ws_line_period_rec.period6;
381 END;
382
383 FUNCTION Get_WS_Line_Period7 RETURN NUMBER
384 IS
385 BEGIN
386 RETURN ws_line_period_rec.period7;
387 END;
388
389 FUNCTION Get_WS_Line_Period8 RETURN NUMBER
390 IS
391 BEGIN
392 RETURN ws_line_period_rec.period8;
393 END;
394
395 FUNCTION Get_WS_Line_Period9 RETURN NUMBER
396 IS
397 BEGIN
398 RETURN ws_line_period_rec.period9;
399 END;
400
401 FUNCTION Get_WS_Line_Period10 RETURN NUMBER
402 IS
403 BEGIN
404 RETURN ws_line_period_rec.period10;
405 END;
406
407 FUNCTION Get_WS_Line_Period11 RETURN NUMBER
408 IS
409 BEGIN
410 RETURN ws_line_period_rec.period11;
411 END;
412
413 FUNCTION Get_WS_Line_Period12 RETURN NUMBER
414 IS
415 BEGIN
416 RETURN ws_line_period_rec.period12;
417 END;
418
419 /*For Bug No : 2708720 Start*/
420 PROCEDURE Set_Total_Flag(p_total_flag IN VARCHAR2)
421 IS
422 BEGIN
423 ws_line_year_rec.total_flag := p_total_flag;
424 END;
425
426 FUNCTION Get_Total_Flag RETURN VARCHAR2
427 IS
428 BEGIN
429 RETURN NVL(ws_line_year_rec.total_flag,'N');
430 END;
431 /*For Bug No : 2708720 End*/
432
433 /*For Bug No : 1756051 Start*/
434 FUNCTION Get_Account_Flag RETURN VARCHAR2
435 IS
436 BEGIN
437 /* Changed default value to 'T' from 'C' for Bug 3191611 */
438 RETURN NVL(ws_line_year_rec.account_flag,'T');
439 END;
440 /*For Bug No : 1756051 End*/
441
442 /* Added the following procedure for bug 3206280
443 This procedure deletes session information for a
444 worksheet from PSB_WORKSHEET_CONTEXT */
445
446 PROCEDURE Delete_Session_Information
447 (
448 p_worksheet_id IN NUMBER,
449 p_session_id IN NUMBER
450 )
451 IS
452 BEGIN
453 SAVEPOINT DELETE_SESSION;
454
455 delete from psb_worksheet_context
456 where session_id = p_session_id;
457
458 COMMIT work;
459
460 EXCEPTION
461
462 when others then
463 ROLLBACK to DELETE_SESSION;
464
465 END delete_session_information;
466
467 END PSB_WS_MATRIX;