1 PACKAGE body PSB_WS_YEAR_TOTAL as
2 /* $Header: PSBVWYTB.pls 115.32 2004/01/22 08:01:33 sajgeo ship $ */
3 ------------------------------------------------------------------------------------------
4 -- Worksheet Line Year
5 ------------------------------------------------------------------------------------------
6
7 g_segment_values FND_FLEX_EXT.SegmentArray;
8 g_segment_low FND_FLEX_EXT.SegmentArray;
9 g_segment_high FND_FLEX_EXT.SegmentArray;
10 g_segment_count NUMBER;
11 g_flex_delimiter VARCHAR2(1);
12
13
14 PROCEDURE assign_seg_values
15 IS
16
17 l_segment_index NUMBER;
18 l_segment_value VARCHAR2(200);
19 l_return_value VARCHAR2(200);
20
21 BEGIN
22 for l_temp in 1..30 loop
23
24 for l_index in 1..PSB_WS_ACCT1.g_num_segs loop
25
26 if PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT'||l_temp then
27 l_segment_index := l_index;
28 exit;
29 end if;
30
31 end loop;
32
33 if l_segment_index is null then
34 g_segment_low(l_temp) := null;
35 g_segment_high(l_temp) := null;
36 else
37 begin
38
39 l_segment_value := g_segment_values(l_segment_index);
40
41 if (l_segment_value = '' or l_segment_value is null) then
42 g_segment_low(l_temp) := null;
43 g_segment_high(l_temp) := null;
44 elsif instr(l_segment_value, '#BETWEEN') <> 0 then
45 l_return_value := substr(l_segment_value, 0, (instr(l_segment_value, 'AND')-2));
46 g_segment_low(l_temp) := substr(l_return_value, instr(l_return_value, '#BETWEEN') + 9);
47 g_segment_high(l_temp) := substr(l_segment_value, instr(l_segment_value, 'AND') + 4);
48 elsif instr(l_segment_value, '<=') <> 0 then
49 g_segment_low(l_temp) := null;
50 g_segment_high(l_temp) := substr(l_segment_value, instr(l_segment_value, '<=') + 3);
51 elsif instr(l_segment_value, '>=') <> 0 then
52 g_segment_low(l_temp) := substr(l_segment_value, instr(l_segment_value, '>=') + 3);
53 g_segment_high(l_temp) := null;
54 elsif instr(l_segment_value, '%') <> 0 then
55 g_segment_low(l_temp) := 'LK';
56 g_segment_high(l_temp) := l_segment_value;
57 else
58 g_segment_low(l_temp) := l_segment_value;
59 g_segment_high(l_temp) := l_segment_value;
60 end if;
61
62 end;
63 end if;
64
65 end loop;
66
67 END assign_seg_values;
68
69 PROCEDURE Get_Totals
70 (
71 p_worksheet_id NUMBER,
72 --following 1 parameter added for DDSP
73 p_profile_worksheet_id NUMBER,
74 p_user_id NUMBER,
75 p_template_id NUMBER,
76 p_account_flag VARCHAR2,
77 p_currency_flag VARCHAR2,
78 p_spkg_flag VARCHAR2,
79 p_spkg_selection_exists VARCHAR2,
80 p_spkg_name VARCHAR2,
81 p_flexfield_low VARCHAR2,
82 p_flexfield_high VARCHAR2,
83 p_flexfield_delimiter VARCHAR2,
84 p_chart_of_accounts NUMBER,
85 p_flex_value VARCHAR2,
86 p1_amount OUT NOCOPY NUMBER,
87 p2_amount OUT NOCOPY NUMBER,
88 p3_amount OUT NOCOPY NUMBER,
89 p4_amount OUT NOCOPY NUMBER,
90 p5_amount OUT NOCOPY NUMBER,
91 p6_amount OUT NOCOPY NUMBER,
92 p7_amount OUT NOCOPY NUMBER,
93 p8_amount OUT NOCOPY NUMBER,
94 p9_amount OUT NOCOPY NUMBER,
95 p10_amount OUT NOCOPY NUMBER,
96 p11_amount OUT NOCOPY NUMBER,
97 p12_amount OUT NOCOPY NUMBER
98 ) IS
99 --
100
101 /* Bug 3331024 Removed Rule hint */
102 cursor c_sum_all is
103 select NVL(SUM(column1),0) A, NVL(SUM(column2),0) B, NVL(SUM(column3),0) C, NVL(SUM(column4),0) D,
104 NVL(SUM(column5),0) E, NVL(SUM(column6),0) F, NVL(SUM(column7),0) G, NVL(SUM(column8),0) H,
105 NVL(SUM(column9),0) I, NVL(SUM(column10),0) J, NVL(SUM(column11),0) K, NVL(SUM(column12),0) L,
106 DECODE(UPPER(account_type),'L','I','O','I','R','I','C','I','A','E','E') account_type
107 FROM PSB_WS_YEAR_AMOUNTS_V WYA
108 WHERE worksheet_id = p_worksheet_id
109 -- Changed reference to 'T' from 'C' for All accounts for bug 3191611
110 AND (p_account_flag = 'T' OR account_type = p_account_flag
111 OR account_type = DECODE(p_account_flag,'P','R','~')
112 OR account_type = DECODE(p_account_flag,'P','E','~')
113 OR account_type = DECODE(p_account_flag,'N','A','~')
114 OR account_type = DECODE(p_account_flag,'N','L','~')
115 OR account_type = DECODE(p_account_flag,'B','C','~')
116 OR account_type = DECODE(p_account_flag,'B','D','~'))
117 AND ((p_currency_flag = 'C' AND currency_code <> 'STAT')
118 OR (p_currency_flag = 'S' AND currency_code = 'STAT'))
119 AND ((p_template_id is NULL AND template_id is null) OR (p_template_id is NOT NULL AND template_id = p_template_id))
120 /* Bug No 2543015 Start */
121 AND (p_spkg_flag = 'A'
122 OR (p_spkg_selection_exists = 'N'
123 AND service_package_id IN
124 (SELECT sp.service_package_id
125 FROM PSB_SERVICE_PACKAGES sp, PSB_WORKSHEETS w
126 WHERE sp.global_worksheet_id = nvl(w.global_worksheet_id, w.worksheet_id)
127 AND w.worksheet_id = p_worksheet_id
128 AND sp.name like p_spkg_name))
129 OR (p_spkg_selection_exists = 'Y'
130 AND service_package_id IN
131 (SELECT service_package_id
132 FROM PSB_WS_SERVICE_PKG_PROFILES_V
133 WHERE worksheet_id = p_profile_worksheet_id
134 AND ((user_id = p_user_id) or (p_user_id is null and user_id is null))
135 AND service_package_name like decode(p_spkg_name, '%', service_package_name, p_spkg_name))))
136 /* Bug No 2543015 End */
137 GROUP by DECODE(UPPER(account_type),'L','I','O','I','R','I','C','I','A','E','E');
138
139 /* Bug 3331024 Removed Rule hint */
140 cursor c_sum_partial is
141 select NVL(SUM(column1),0) A, NVL(SUM(column2),0) B, NVL(SUM(column3),0) C, NVL(SUM(column4),0) D,
142 NVL(SUM(column5),0) E, NVL(SUM(column6),0) F, NVL(SUM(column7),0) G, NVL(SUM(column8),0) H,
143 NVL(SUM(column9),0) I, NVL(SUM(column10),0) J, NVL(SUM(column11),0) K, NVL(SUM(column12),0) L,
144 DECODE(UPPER(account_type),'L','I','O','I','R','I','C','I','A','E','E') account_type
145 FROM PSB_WS_YEAR_AMOUNTS_V WYA
146 WHERE worksheet_id = p_worksheet_id
147 -- Changed reference to 'T' from 'C' for All accounts for bug 3191611
148 AND (p_account_flag = 'T' OR account_type = p_account_flag
149 OR account_type = DECODE(p_account_flag,'P','R','~')
150 OR account_type = DECODE(p_account_flag,'P','E','~')
151 OR account_type = DECODE(p_account_flag,'N','A','~')
152 OR account_type = DECODE(p_account_flag,'N','L','~')
153 OR account_type = DECODE(p_account_flag,'B','C','~')
154 OR account_type = DECODE(p_account_flag,'B','D','~'))
155 AND ((p_currency_flag = 'C' AND currency_code <> 'STAT')
156 OR (p_currency_flag = 'S' AND currency_code = 'STAT'))
157 AND ((p_template_id is NULL AND template_id is null)
158 OR (p_template_id is NOT NULL AND template_id = p_template_id))
159 /* Bug No 2543015 Start */
160 AND (p_spkg_flag = 'A'
161 OR (p_spkg_selection_exists = 'N'
162 AND service_package_id IN
163 (SELECT sp.service_package_id
164 FROM PSB_SERVICE_PACKAGES sp, PSB_WORKSHEETS w
165 WHERE sp.global_worksheet_id = nvl(w.global_worksheet_id, w.worksheet_id)
166 AND w.worksheet_id = p_worksheet_id
167 AND sp.name like p_spkg_name))
168 OR (p_spkg_selection_exists = 'Y'
169 AND service_package_id IN
170 (SELECT service_package_id
171 FROM PSB_WS_SERVICE_PKG_PROFILES_V
172 WHERE worksheet_id = p_profile_worksheet_id
173 AND ((user_id = p_user_id) or (p_user_id is null and user_id is null))
174 AND service_package_name like decode(p_spkg_name, '%', service_package_name, p_spkg_name))))
175 /* Bug No 2543015 End */
176 AND EXISTS
177 (select 1 from gl_code_combinations
178 where code_combination_id = WYA.code_combination_id
179 and chart_of_accounts_id = p_chart_of_accounts
180 and ( (segment1 is null) or (g_segment_low(1) is null and g_segment_high(1) is null) or (g_segment_low(1) is null and segment1 <= g_segment_high(1)) or (g_segment_high(1) is null and segment1 >= g_segment_low(1)) or
181 (g_segment_low(1) = 'LK' and segment1 like g_segment_high(1)) or (g_segment_low(1) is not null and g_segment_high(1) is not null and g_segment_low(1) <> 'LK' and segment1 between g_segment_low(1) and g_segment_high(1)) )
182 and ( (segment2 is null) or (g_segment_low(2) is null and g_segment_high(2) is null) or (g_segment_low(2) is null and segment2 <= g_segment_high(2)) or (g_segment_high(2) is null and segment2 >= g_segment_low(2)) or
183 (g_segment_low(2) = 'LK' and segment2 like g_segment_high(2)) or (g_segment_low(2) is not null and g_segment_high(2) is not null and g_segment_low(2) <> 'LK' and segment2 between g_segment_low(2) and g_segment_high(2)) )
184 and ( (segment3 is null) or (g_segment_low(3) is null and g_segment_high(3) is null) or (g_segment_low(3) is null and segment3 <= g_segment_high(3)) or (g_segment_high(3) is null and segment3 >= g_segment_low(3)) or
185 (g_segment_low(3) = 'LK' and segment3 like g_segment_high(3)) or (g_segment_low(3) is not null and g_segment_high(3) is not null and g_segment_low(3) <> 'LK' and segment3 between g_segment_low(3) and g_segment_high(3)) )
186 and ( (segment4 is null) or (g_segment_low(4) is null and g_segment_high(4) is null) or (g_segment_low(4) is null and segment4 <= g_segment_high(4)) or (g_segment_high(4) is null and segment4 >= g_segment_low(4)) or
187 (g_segment_low(4) = 'LK' and segment4 like g_segment_high(4)) or (g_segment_low(4) is not null and g_segment_high(4) is not null and g_segment_low(4) <> 'LK' and segment4 between g_segment_low(4) and g_segment_high(4)) )
188 and ( (segment5 is null) or (g_segment_low(5) is null and g_segment_high(5) is null) or (g_segment_low(5) is null and segment5 <= g_segment_high(5)) or (g_segment_high(5) is null and segment5 >= g_segment_low(5)) or
189 (g_segment_low(5) = 'LK' and segment5 like g_segment_high(5)) or (g_segment_low(5) is not null and g_segment_high(5) is not null and g_segment_low(5) <> 'LK' and segment5 between g_segment_low(5) and g_segment_high(5)) )
190 and ( (segment6 is null) or (g_segment_low(6) is null and g_segment_high(6) is null) or (g_segment_low(6) is null and segment6 <= g_segment_high(6)) or (g_segment_high(6) is null and segment6 >= g_segment_low(6)) or
191 (g_segment_low(6) = 'LK' and segment6 like g_segment_high(6)) or (g_segment_low(6) is not null and g_segment_high(6) is not null and g_segment_low(6) <> 'LK' and segment6 between g_segment_low(6) and g_segment_high(6)) )
192 and ( (segment7 is null) or (g_segment_low(7) is null and g_segment_high(7) is null) or (g_segment_low(7) is null and segment7 <= g_segment_high(7)) or (g_segment_high(7) is null and segment7 >= g_segment_low(7)) or
193 (g_segment_low(7) = 'LK' and segment7 like g_segment_high(7)) or (g_segment_low(7) is not null and g_segment_high(7) is not null and g_segment_low(7) <> 'LK' and segment7 between g_segment_low(7) and g_segment_high(7)) )
194 and ( (segment8 is null) or (g_segment_low(8) is null and g_segment_high(8) is null) or (g_segment_low(8) is null and segment8 <= g_segment_high(8)) or (g_segment_high(8) is null and segment8 >= g_segment_low(8)) or
195 (g_segment_low(8) = 'LK' and segment8 like g_segment_high(8)) or (g_segment_low(8) is not null and g_segment_high(8) is not null and g_segment_low(8) <> 'LK' and segment8 between g_segment_low(8) and g_segment_high(8)) )
196 and ( (segment9 is null) or (g_segment_low(9) is null and g_segment_high(9) is null) or (g_segment_low(9) is null and segment9 <= g_segment_high(9)) or (g_segment_high(9) is null and segment9 >= g_segment_low(9)) or
197 (g_segment_low(9) = 'LK' and segment9 like g_segment_high(9)) or (g_segment_low(9) is not null and g_segment_high(9) is not null and g_segment_low(9) <> 'LK' and segment9 between g_segment_low(9) and g_segment_high(9)) )
198 and ( (segment10 is null) or (g_segment_low(10) is null and g_segment_high(10) is null) or (g_segment_low(10) is null and segment10 <= g_segment_high(10)) or (g_segment_high(10) is null and segment10 >= g_segment_low(10)) or
199 (g_segment_low(10) = 'LK' and segment10 like g_segment_high(10)) or (g_segment_low(10) is not null and g_segment_high(10) is not null and g_segment_low(10) <> 'LK' and segment10 between g_segment_low(10) and g_segment_high(10)) )
200 and ( (segment11 is null) or (g_segment_low(11) is null and g_segment_high(11) is null) or (g_segment_low(11) is null and segment11 <= g_segment_high(11)) or (g_segment_high(11) is null and segment11 >= g_segment_low(11)) or
201 (g_segment_low(11) = 'LK' and segment11 like g_segment_high(11)) or (g_segment_low(11) is not null and g_segment_high(11) is not null and g_segment_low(11) <> 'LK' and segment11 between g_segment_low(11) and g_segment_high(11)) )
202 and ( (segment12 is null) or (g_segment_low(12) is null and g_segment_high(12) is null) or (g_segment_low(12) is null and segment12 <= g_segment_high(12)) or (g_segment_high(12) is null and segment12 >= g_segment_low(12)) or
203 (g_segment_low(12) = 'LK' and segment12 like g_segment_high(12)) or (g_segment_low(12) is not null and g_segment_high(12) is not null and g_segment_low(12) <> 'LK' and segment12 between g_segment_low(12) and g_segment_high(12)) )
204 and ( (segment13 is null) or (g_segment_low(13) is null and g_segment_high(13) is null) or (g_segment_low(13) is null and segment13 <= g_segment_high(13)) or (g_segment_high(13) is null and segment13 >= g_segment_low(13)) or
205 (g_segment_low(13) = 'LK' and segment13 like g_segment_high(13)) or (g_segment_low(13) is not null and g_segment_high(13) is not null and g_segment_low(13) <> 'LK' and segment13 between g_segment_low(13) and g_segment_high(13)) )
206 and ( (segment14 is null) or (g_segment_low(14) is null and g_segment_high(14) is null) or (g_segment_low(14) is null and segment14 <= g_segment_high(14)) or (g_segment_high(14) is null and segment14 >= g_segment_low(14)) or
207 (g_segment_low(14) = 'LK' and segment14 like g_segment_high(14)) or (g_segment_low(14) is not null and g_segment_high(14) is not null and g_segment_low(14) <> 'LK' and segment14 between g_segment_low(14) and g_segment_high(14)) )
208 and ( (segment15 is null) or (g_segment_low(15) is null and g_segment_high(15) is null) or (g_segment_low(15) is null and segment15 <= g_segment_high(15)) or (g_segment_high(15) is null and segment15 >= g_segment_low(15)) or
209 (g_segment_low(15) = 'LK' and segment15 like g_segment_high(15)) or (g_segment_low(15) is not null and g_segment_high(15) is not null and g_segment_low(15) <> 'LK' and segment15 between g_segment_low(15) and g_segment_high(15)) )
210 and ( (segment16 is null) or (g_segment_low(16) is null and g_segment_high(16) is null) or (g_segment_low(16) is null and segment16 <= g_segment_high(16)) or (g_segment_high(16) is null and segment16 >= g_segment_low(16)) or
211 (g_segment_low(16) = 'LK' and segment16 like g_segment_high(16)) or (g_segment_low(16) is not null and g_segment_high(16) is not null and g_segment_low(16) <> 'LK' and segment16 between g_segment_low(16) and g_segment_high(16)) )
212 and ( (segment17 is null) or (g_segment_low(17) is null and g_segment_high(17) is null) or (g_segment_low(17) is null and segment17 <= g_segment_high(17)) or (g_segment_high(17) is null and segment17 >= g_segment_low(17)) or
213 (g_segment_low(17) = 'LK' and segment17 like g_segment_high(17)) or (g_segment_low(17) is not null and g_segment_high(17) is not null and g_segment_low(17) <> 'LK' and segment17 between g_segment_low(17) and g_segment_high(17)) )
214 and ( (segment18 is null) or (g_segment_low(18) is null and g_segment_high(18) is null) or (g_segment_low(18) is null and segment18 <= g_segment_high(18)) or (g_segment_high(18) is null and segment18 >= g_segment_low(18)) or
215 (g_segment_low(18) = 'LK' and segment18 like g_segment_high(18)) or (g_segment_low(18) is not null and g_segment_high(18) is not null and g_segment_low(18) <> 'LK' and segment18 between g_segment_low(18) and g_segment_high(18)) )
216 and ( (segment19 is null) or (g_segment_low(19) is null and g_segment_high(19) is null) or (g_segment_low(19) is null and segment19 <= g_segment_high(19)) or (g_segment_high(19) is null and segment19 >= g_segment_low(19)) or
217 (g_segment_low(19) = 'LK' and segment19 like g_segment_high(19)) or (g_segment_low(19) is not null and g_segment_high(19) is not null and g_segment_low(19) <> 'LK' and segment19 between g_segment_low(19) and g_segment_high(19)) )
218 and ( (segment20 is null) or (g_segment_low(20) is null and g_segment_high(20) is null) or (g_segment_low(20) is null and segment20 <= g_segment_high(20)) or (g_segment_high(20) is null and segment20 >= g_segment_low(20)) or
219 (g_segment_low(20) = 'LK' and segment20 like g_segment_high(20)) or (g_segment_low(20) is not null and g_segment_high(20) is not null and g_segment_low(20) <> 'LK' and segment20 between g_segment_low(20) and g_segment_high(20)) )
220 and ( (segment21 is null) or (g_segment_low(21) is null and g_segment_high(21) is null) or (g_segment_low(21) is null and segment21 <= g_segment_high(21)) or (g_segment_high(21) is null and segment21 >= g_segment_low(21)) or
221 (g_segment_low(21) = 'LK' and segment21 like g_segment_high(21)) or (g_segment_low(21) is not null and g_segment_high(21) is not null and g_segment_low(21) <> 'LK' and segment21 between g_segment_low(21) and g_segment_high(21)) )
222 and ( (segment22 is null) or (g_segment_low(22) is null and g_segment_high(22) is null) or (g_segment_low(22) is null and segment22 <= g_segment_high(22)) or (g_segment_high(22) is null and segment22 >= g_segment_low(22)) or
223 (g_segment_low(22) = 'LK' and segment22 like g_segment_high(22)) or (g_segment_low(22) is not null and g_segment_high(22) is not null and g_segment_low(22) <> 'LK' and segment22 between g_segment_low(22) and g_segment_high(22)) )
224 and ( (segment23 is null) or (g_segment_low(23) is null and g_segment_high(23) is null) or (g_segment_low(23) is null and segment23 <= g_segment_high(23)) or (g_segment_high(23) is null and segment23 >= g_segment_low(23)) or
225 (g_segment_low(23) = 'LK' and segment23 like g_segment_high(23)) or (g_segment_low(23) is not null and g_segment_high(23) is not null and g_segment_low(23) <> 'LK' and segment23 between g_segment_low(23) and g_segment_high(23)) )
226 and ( (segment24 is null) or (g_segment_low(24) is null and g_segment_high(24) is null) or (g_segment_low(24) is null and segment24 <= g_segment_high(24)) or (g_segment_high(24) is null and segment24 >= g_segment_low(24)) or
227 (g_segment_low(24) = 'LK' and segment24 like g_segment_high(24)) or (g_segment_low(24) is not null and g_segment_high(24) is not null and g_segment_low(24) <> 'LK' and segment24 between g_segment_low(24) and g_segment_high(24)) )
228 and ( (segment25 is null) or (g_segment_low(25) is null and g_segment_high(25) is null) or (g_segment_low(25) is null and segment25 <= g_segment_high(25)) or (g_segment_high(25) is null and segment25 >= g_segment_low(25)) or
229 (g_segment_low(25) = 'LK' and segment25 like g_segment_high(25)) or (g_segment_low(25) is not null and g_segment_high(25) is not null and g_segment_low(25) <> 'LK' and segment25 between g_segment_low(25) and g_segment_high(25)) )
230 and ( (segment26 is null) or (g_segment_low(26) is null and g_segment_high(26) is null) or (g_segment_low(26) is null and segment26 <= g_segment_high(26)) or (g_segment_high(26) is null and segment26 >= g_segment_low(26)) or
231 (g_segment_low(26) = 'LK' and segment26 like g_segment_high(26)) or (g_segment_low(26) is not null and g_segment_high(26) is not null and g_segment_low(26) <> 'LK' and segment26 between g_segment_low(26) and g_segment_high(26)) )
232 and ( (segment27 is null) or (g_segment_low(27) is null and g_segment_high(27) is null) or (g_segment_low(27) is null and segment27 <= g_segment_high(27)) or (g_segment_high(27) is null and segment27 >= g_segment_low(27)) or
233 (g_segment_low(27) = 'LK' and segment27 like g_segment_high(27)) or (g_segment_low(27) is not null and g_segment_high(27) is not null and g_segment_low(27) <> 'LK' and segment27 between g_segment_low(27) and g_segment_high(27)) )
234 and ( (segment28 is null) or (g_segment_low(28) is null and g_segment_high(28) is null) or (g_segment_low(28) is null and segment28 <= g_segment_high(28)) or (g_segment_high(28) is null and segment28 >= g_segment_low(28)) or
235 (g_segment_low(28) = 'LK' and segment28 like g_segment_high(28)) or (g_segment_low(28) is not null and g_segment_high(28) is not null and g_segment_low(28) <> 'LK' and segment28 between g_segment_low(28) and g_segment_high(28)) )
236 and ( (segment29 is null) or (g_segment_low(29) is null and g_segment_high(29) is null) or (g_segment_low(29) is null and segment29 <= g_segment_high(29)) or (g_segment_high(29) is null and segment29 >= g_segment_low(29)) or
237 (g_segment_low(29) = 'LK' and segment29 like g_segment_high(29)) or (g_segment_low(29) is not null and g_segment_high(29) is not null and g_segment_low(29) <> 'LK' and segment29 between g_segment_low(29) and g_segment_high(29)) )
238 and ( (segment30 is null) or (g_segment_low(30) is null and g_segment_high(30) is null) or (g_segment_low(30) is null and segment30 <= g_segment_high(30)) or (g_segment_high(30) is null and segment30 >= g_segment_low(30)) or
239 (g_segment_low(30) = 'LK' and segment30 like g_segment_high(30)) or (g_segment_low(30) is not null and g_segment_high(30) is not null and g_segment_low(30) <> 'LK' and segment30 between g_segment_low(30) and g_segment_high(30)) ))
240 GROUP BY DECODE(UPPER(account_type),'L','I','O','I','R','I','C','I','A','E','E');
241 --
242 l_cr_dr NUMBER;
243 ltot1 number:=0;
244 ltot2 number:=0;
245 ltot3 number:=0;
246 ltot4 number:=0;
247 ltot5 number:=0;
248 ltot6 number:=0;
249 ltot7 number:=0;
250 ltot8 number:=0;
251 ltot9 number:=0;
252 ltot10 number:=0;
253 ltot11 number:=0;
254 ltot12 number:=0;
255 l_return_status VARCHAR2(1);
256
257 BEGIN
258
259 if (nvl(p_flex_value, '%') <> '%') then
260 begin
261
262 if p_chart_of_accounts <> nvl(PSB_WS_ACCT1.g_flex_code, 0) then
263 begin
264
265 PSB_WS_ACCT1.Flex_Info (p_flex_code => p_chart_of_accounts, p_return_status => l_return_status);
266
267 end;
268 end if;
269
270 g_flex_delimiter := fnd_flex_ext.get_delimiter(application_short_name => 'SQLGL',
271 key_flex_code => 'GL#',
272 structure_number => p_chart_of_accounts);
273
274 g_segment_count := FND_FLEX_EXT.breakup_segments(p_flex_value, g_flex_delimiter, g_segment_values);
275
276 /*For Bug No : 2012827 Start*/
277 for i in g_segment_count+1..30 loop
278 g_segment_values(i) := null;
279 end loop;
280 /*For Bug No : 2012827 End*/
281
282 assign_seg_values;
283
284 for c_sum_partial_rec in c_sum_partial loop
285
286 if c_sum_partial_rec.account_type = 'I' then
287 ltot1 := ltot1 + c_sum_partial_rec.A;
288 ltot2 := ltot2 + c_sum_partial_rec.B;
289 ltot3 := ltot3 + c_sum_partial_rec.C;
290 ltot4 := ltot4 + c_sum_partial_rec.D;
291 ltot5 := ltot5 + c_sum_partial_rec.E;
292 ltot6 := ltot6 + c_sum_partial_rec.F;
293 ltot7 := ltot7 + c_sum_partial_rec.G;
294 ltot8 := ltot8 + c_sum_partial_rec.H;
295 ltot9 := ltot9 + c_sum_partial_rec.I;
296 ltot10 := ltot10 + c_sum_partial_rec.J;
297 ltot11 := ltot11 + c_sum_partial_rec.K;
298 ltot12 := ltot12 + c_sum_partial_rec.L;
299 ELSE
300 ltot1 := ltot1 - c_sum_partial_rec.A;
301 ltot2 := ltot2 - c_sum_partial_rec.B;
302 ltot3 := ltot3 - c_sum_partial_rec.C;
303 ltot4 := ltot4 - c_sum_partial_rec.D;
304 ltot5 := ltot5 - c_sum_partial_rec.E;
305 ltot6 := ltot6 - c_sum_partial_rec.F;
306 ltot7 := ltot7 - c_sum_partial_rec.G;
307 ltot8 := ltot8 - c_sum_partial_rec.H;
308 ltot9 := ltot9 - c_sum_partial_rec.I;
309 ltot10 := ltot10 - c_sum_partial_rec.J;
310 ltot11 := ltot11 - c_sum_partial_rec.K;
311 ltot12 := ltot12 - c_sum_partial_rec.L;
312 END IF;
313
314 end loop;
315
316 end;
317 else
318 begin
319
320 FOR c_sum_all_rec IN c_sum_all LOOP
321
322 IF c_sum_all_rec.account_type = 'I' THEN
323 ltot1 := ltot1 + c_sum_all_rec.A;
324 ltot2 := ltot2 + c_sum_all_rec.B;
325 ltot3 := ltot3 + c_sum_all_rec.C;
326 ltot4 := ltot4 + c_sum_all_rec.D;
327 ltot5 := ltot5 + c_sum_all_rec.E;
328 ltot6 := ltot6 + c_sum_all_rec.F;
329 ltot7 := ltot7 + c_sum_all_rec.G;
330 ltot8 := ltot8 + c_sum_all_rec.H;
331 ltot9 := ltot9 + c_sum_all_rec.I;
332 ltot10 := ltot10 + c_sum_all_rec.J;
333 ltot11 := ltot11 + c_sum_all_rec.K;
334 ltot12 := ltot12 + c_sum_all_rec.L;
335 ELSE
336 ltot1 := ltot1 - c_sum_all_rec.A;
337 ltot2 := ltot2 - c_sum_all_rec.B;
338 ltot3 := ltot3 - c_sum_all_rec.C;
339 ltot4 := ltot4 - c_sum_all_rec.D;
340 ltot5 := ltot5 - c_sum_all_rec.E;
341 ltot6 := ltot6 - c_sum_all_rec.F;
342 ltot7 := ltot7 - c_sum_all_rec.G;
343 ltot8 := ltot8 - c_sum_all_rec.H;
344 ltot9 := ltot9 - c_sum_all_rec.I;
345 ltot10 := ltot10 - c_sum_all_rec.J;
346 ltot11 := ltot11 - c_sum_all_rec.K;
347 ltot12 := ltot12 - c_sum_all_rec.L;
348 END IF;
349 END LOOP;
350
351 end;
352 END IF;
353
354 IF p_account_flag in ('A','E','N') THEN
355 l_cr_dr := -1;
356 ELSE
357 l_cr_dr := 1;
358 END IF;
359 p1_amount := ltot1 * l_cr_dr;
360 p2_amount := ltot2 * l_cr_dr;
361 p3_amount := ltot3 * l_cr_dr;
362 p4_amount := ltot4 * l_cr_dr;
363 p5_amount := ltot5 * l_cr_dr;
364 p6_amount := ltot6 * l_cr_dr;
365 p7_amount := ltot7 * l_cr_dr;
366 p8_amount := ltot8 * l_cr_dr;
367 p9_amount := ltot9 * l_cr_dr;
368 p10_amount := ltot10 * l_cr_dr;
369 p11_amount := ltot11 * l_cr_dr;
370 p12_amount := ltot12 * l_cr_dr;
371
372 END Get_Totals;
373
374
375 PROCEDURE Position_Totals
376 (
377 pworksheet_id number,
378 --following 1 parameter added for DDSP
379 pprofile_worksheet_id number,
380 pposition_line_id number,
381 paccount_flag varchar2,
382 pcurrency_flag varchar2,
383 pservice_package_flag varchar2,
384 pselection_exists varchar2,
385 puser_id number,
386 pchart_of_accounts_id number,
387 pspkg_name varchar2,
388 pflex_value varchar2,
389 ptcolumn1 OUT NOCOPY number,
390 ptcolumn2 OUT NOCOPY number,
391 ptcolumn3 OUT NOCOPY number,
392 ptcolumn4 OUT NOCOPY number,
393 ptcolumn5 OUT NOCOPY number,
394 ptcolumn6 OUT NOCOPY number,
395 ptcolumn7 OUT NOCOPY number,
396 ptcolumn8 OUT NOCOPY number,
397 ptcolumn9 OUT NOCOPY number,
398 ptcolumn10 OUT NOCOPY number,
399 ptcolumn11 OUT NOCOPY number,
400 ptcolumn12 OUT NOCOPY number
401
402 ) IS
403
404 /* Bug 3331024 Remove Rule Hint */
405 cursor c_sum_all is
406 select NVL(SUM(column1),0) A, NVL(SUM(column2),0) B, NVL(SUM(column3),0) C, NVL(SUM(column4),0) D,
407 NVL(SUM(column5),0) E, NVL(SUM(column6),0) F, NVL(SUM(column7),0) G, NVL(SUM(column8),0) H,
408 NVL(SUM(column9),0) I, NVL(SUM(column10),0) J, NVL(SUM(column11),0) K, NVL(SUM(column12),0) L
409 FROM PSB_WS_YEAR_POSITION_AMOUNTS_V WYA
410 WHERE worksheet_id = pworksheet_id
411 AND position_line_id = pposition_line_id
412 AND ((pcurrency_flag = 'C' AND currency_code <> 'STAT') OR (pcurrency_flag = 'S' AND currency_code = 'STAT'))
413 AND template_id is null
414 /* Bug No 2543015 Start */
415 AND (pservice_package_flag = 'A'
416 OR (pselection_exists = 'N'
417 AND service_package_id IN
418 (SELECT sp.service_package_id
419 FROM PSB_SERVICE_PACKAGES sp, PSB_WORKSHEETS w
420 WHERE sp.global_worksheet_id = nvl(w.global_worksheet_id, w.worksheet_id)
421 AND w.worksheet_id = pworksheet_id
422 AND sp.name like pspkg_name))
423 OR (pselection_exists = 'Y'
424 AND service_package_id IN
425 (SELECT service_package_id
426 FROM PSB_WS_SERVICE_PKG_PROFILES_V
427 WHERE worksheet_id = pprofile_worksheet_id
428 AND ((user_id = puser_id) or (puser_id is null and user_id is null))
429 AND service_package_name like decode(pspkg_name, '%', service_package_name, pspkg_name))));
430 /* Bug No 2543015 End */
431
432 /* Bug 3331024 Remove Rule Hint */
433 cursor c_sum_partial is
434 select NVL(SUM(column1),0) A, NVL(SUM(column2),0) B, NVL(SUM(column3),0) C, NVL(SUM(column4),0) D,
435 NVL(SUM(column5),0) E, NVL(SUM(column6),0) F, NVL(SUM(column7),0) G, NVL(SUM(column8),0) H,
436 NVL(SUM(column9),0) I, NVL(SUM(column10),0) J, NVL(SUM(column11),0) K, NVL(SUM(column12),0) L
437 FROM PSB_WS_YEAR_POSITION_AMOUNTS_V WYA
438 WHERE worksheet_id = pworksheet_id
439 AND position_line_id = pposition_line_id
440 AND ((pcurrency_flag = 'C' AND currency_code <> 'STAT') OR (pcurrency_flag = 'S' AND currency_code = 'STAT'))
441 AND template_id is null
442 /* Bug No 2543015 Start */
443 AND (pservice_package_flag = 'A'
444 OR (pselection_exists = 'N'
445 AND service_package_id IN
446 (SELECT sp.service_package_id
447 FROM PSB_SERVICE_PACKAGES sp, PSB_WORKSHEETS w
448 WHERE sp.global_worksheet_id = nvl(w.global_worksheet_id, w.worksheet_id)
449 AND w.worksheet_id = pworksheet_id
450 AND sp.name like pspkg_name))
451 OR (pselection_exists = 'Y'
452 AND service_package_id IN
453 (SELECT service_package_id
454 FROM PSB_WS_SERVICE_PKG_PROFILES_V
455 WHERE worksheet_id = pprofile_worksheet_id
456 AND ((user_id = puser_id) or (puser_id is null and user_id is null))
457 AND service_package_name like decode(pspkg_name, '%', service_package_name, pspkg_name))))
458 /* Bug No 2543015 End */
459 AND EXISTS
460 (select 1 from gl_code_combinations
461 where code_combination_id = WYA.code_combination_id
462 and chart_of_accounts_id = pchart_of_accounts_id
463 and ( (segment1 is null) or (g_segment_low(1) is null and g_segment_high(1) is null) or (g_segment_low(1) is null and segment1 <= g_segment_high(1)) or (g_segment_high(1) is null and segment1 >= g_segment_low(1)) or
464 (g_segment_low(1) = 'LK' and segment1 like g_segment_high(1)) or (g_segment_low(1) is not null and g_segment_high(1) is not null and g_segment_low(1) <> 'LK' and segment1 between g_segment_low(1) and g_segment_high(1)) )
465 and ( (segment2 is null) or (g_segment_low(2) is null and g_segment_high(2) is null) or (g_segment_low(2) is null and segment2 <= g_segment_high(2)) or (g_segment_high(2) is null and segment2 >= g_segment_low(2)) or
466 (g_segment_low(2) = 'LK' and segment2 like g_segment_high(2)) or (g_segment_low(2) is not null and g_segment_high(2) is not null and g_segment_low(2) <> 'LK' and segment2 between g_segment_low(2) and g_segment_high(2)) )
467 and ( (segment3 is null) or (g_segment_low(3) is null and g_segment_high(3) is null) or (g_segment_low(3) is null and segment3 <= g_segment_high(3)) or (g_segment_high(3) is null and segment3 >= g_segment_low(3)) or
468 (g_segment_low(3) = 'LK' and segment3 like g_segment_high(3)) or (g_segment_low(3) is not null and g_segment_high(3) is not null and g_segment_low(3) <> 'LK' and segment3 between g_segment_low(3) and g_segment_high(3)) )
469 and ( (segment4 is null) or (g_segment_low(4) is null and g_segment_high(4) is null) or (g_segment_low(4) is null and segment4 <= g_segment_high(4)) or (g_segment_high(4) is null and segment4 >= g_segment_low(4)) or
470 (g_segment_low(4) = 'LK' and segment4 like g_segment_high(4)) or (g_segment_low(4) is not null and g_segment_high(4) is not null and g_segment_low(4) <> 'LK' and segment4 between g_segment_low(4) and g_segment_high(4)) )
471 and ( (segment5 is null) or (g_segment_low(5) is null and g_segment_high(5) is null) or (g_segment_low(5) is null and segment5 <= g_segment_high(5)) or (g_segment_high(5) is null and segment5 >= g_segment_low(5)) or
472 (g_segment_low(5) = 'LK' and segment5 like g_segment_high(5)) or (g_segment_low(5) is not null and g_segment_high(5) is not null and g_segment_low(5) <> 'LK' and segment5 between g_segment_low(5) and g_segment_high(5)) )
473 and ( (segment6 is null) or (g_segment_low(6) is null and g_segment_high(6) is null) or (g_segment_low(6) is null and segment6 <= g_segment_high(6)) or (g_segment_high(6) is null and segment6 >= g_segment_low(6)) or
474 (g_segment_low(6) = 'LK' and segment6 like g_segment_high(6)) or (g_segment_low(6) is not null and g_segment_high(6) is not null and g_segment_low(6) <> 'LK' and segment6 between g_segment_low(6) and g_segment_high(6)) )
475 and ( (segment7 is null) or (g_segment_low(7) is null and g_segment_high(7) is null) or (g_segment_low(7) is null and segment7 <= g_segment_high(7)) or (g_segment_high(7) is null and segment7 >= g_segment_low(7)) or
476 (g_segment_low(7) = 'LK' and segment7 like g_segment_high(7)) or (g_segment_low(7) is not null and g_segment_high(7) is not null and g_segment_low(7) <> 'LK' and segment7 between g_segment_low(7) and g_segment_high(7)) )
477 and ( (segment8 is null) or (g_segment_low(8) is null and g_segment_high(8) is null) or (g_segment_low(8) is null and segment8 <= g_segment_high(8)) or (g_segment_high(8) is null and segment8 >= g_segment_low(8)) or
478 (g_segment_low(8) = 'LK' and segment8 like g_segment_high(8)) or (g_segment_low(8) is not null and g_segment_high(8) is not null and g_segment_low(8) <> 'LK' and segment8 between g_segment_low(8) and g_segment_high(8)) )
479 and ( (segment9 is null) or (g_segment_low(9) is null and g_segment_high(9) is null) or (g_segment_low(9) is null and segment9 <= g_segment_high(9)) or (g_segment_high(9) is null and segment9 >= g_segment_low(9)) or
480 (g_segment_low(9) = 'LK' and segment9 like g_segment_high(9)) or (g_segment_low(9) is not null and g_segment_high(9) is not null and g_segment_low(9) <> 'LK' and segment9 between g_segment_low(9) and g_segment_high(9)) )
481 and ( (segment10 is null) or (g_segment_low(10) is null and g_segment_high(10) is null) or (g_segment_low(10) is null and segment10 <= g_segment_high(10)) or (g_segment_high(10) is null and segment10 >= g_segment_low(10)) or
482 (g_segment_low(10) = 'LK' and segment10 like g_segment_high(10)) or (g_segment_low(10) is not null and g_segment_high(10) is not null and g_segment_low(10) <> 'LK' and segment10 between g_segment_low(10) and g_segment_high(10)) )
483 and ( (segment11 is null) or (g_segment_low(11) is null and g_segment_high(11) is null) or (g_segment_low(11) is null and segment11 <= g_segment_high(11)) or (g_segment_high(11) is null and segment11 >= g_segment_low(11)) or
484 (g_segment_low(11) = 'LK' and segment11 like g_segment_high(11)) or (g_segment_low(11) is not null and g_segment_high(11) is not null and g_segment_low(11) <> 'LK' and segment11 between g_segment_low(11) and g_segment_high(11)) )
485 and ( (segment12 is null) or (g_segment_low(12) is null and g_segment_high(12) is null) or (g_segment_low(12) is null and segment12 <= g_segment_high(12)) or (g_segment_high(12) is null and segment12 >= g_segment_low(12)) or
486 (g_segment_low(12) = 'LK' and segment12 like g_segment_high(12)) or (g_segment_low(12) is not null and g_segment_high(12) is not null and g_segment_low(12) <> 'LK' and segment12 between g_segment_low(12) and g_segment_high(12)) )
487 and ( (segment13 is null) or (g_segment_low(13) is null and g_segment_high(13) is null) or (g_segment_low(13) is null and segment13 <= g_segment_high(13)) or (g_segment_high(13) is null and segment13 >= g_segment_low(13)) or
488 (g_segment_low(13) = 'LK' and segment13 like g_segment_high(13)) or (g_segment_low(13) is not null and g_segment_high(13) is not null and g_segment_low(13) <> 'LK' and segment13 between g_segment_low(13) and g_segment_high(13)) )
489 and ( (segment14 is null) or (g_segment_low(14) is null and g_segment_high(14) is null) or (g_segment_low(14) is null and segment14 <= g_segment_high(14)) or (g_segment_high(14) is null and segment14 >= g_segment_low(14)) or
490 (g_segment_low(14) = 'LK' and segment14 like g_segment_high(14)) or (g_segment_low(14) is not null and g_segment_high(14) is not null and g_segment_low(14) <> 'LK' and segment14 between g_segment_low(14) and g_segment_high(14)) )
491 and ( (segment15 is null) or (g_segment_low(15) is null and g_segment_high(15) is null) or (g_segment_low(15) is null and segment15 <= g_segment_high(15)) or (g_segment_high(15) is null and segment15 >= g_segment_low(15)) or
492 (g_segment_low(15) = 'LK' and segment15 like g_segment_high(15)) or (g_segment_low(15) is not null and g_segment_high(15) is not null and g_segment_low(15) <> 'LK' and segment15 between g_segment_low(15) and g_segment_high(15)) )
493 and ( (segment16 is null) or (g_segment_low(16) is null and g_segment_high(16) is null) or (g_segment_low(16) is null and segment16 <= g_segment_high(16)) or (g_segment_high(16) is null and segment16 >= g_segment_low(16)) or
494 (g_segment_low(16) = 'LK' and segment16 like g_segment_high(16)) or (g_segment_low(16) is not null and g_segment_high(16) is not null and g_segment_low(16) <> 'LK' and segment16 between g_segment_low(16) and g_segment_high(16)) )
495 and ( (segment17 is null) or (g_segment_low(17) is null and g_segment_high(17) is null) or (g_segment_low(17) is null and segment17 <= g_segment_high(17)) or (g_segment_high(17) is null and segment17 >= g_segment_low(17)) or
496 (g_segment_low(17) = 'LK' and segment17 like g_segment_high(17)) or (g_segment_low(17) is not null and g_segment_high(17) is not null and g_segment_low(17) <> 'LK' and segment17 between g_segment_low(17) and g_segment_high(17)) )
497 and ( (segment18 is null) or (g_segment_low(18) is null and g_segment_high(18) is null) or (g_segment_low(18) is null and segment18 <= g_segment_high(18)) or (g_segment_high(18) is null and segment18 >= g_segment_low(18)) or
498 (g_segment_low(18) = 'LK' and segment18 like g_segment_high(18)) or (g_segment_low(18) is not null and g_segment_high(18) is not null and g_segment_low(18) <> 'LK' and segment18 between g_segment_low(18) and g_segment_high(18)) )
499 and ( (segment19 is null) or (g_segment_low(19) is null and g_segment_high(19) is null) or (g_segment_low(19) is null and segment19 <= g_segment_high(19)) or (g_segment_high(19) is null and segment19 >= g_segment_low(19)) or
500 (g_segment_low(19) = 'LK' and segment19 like g_segment_high(19)) or (g_segment_low(19) is not null and g_segment_high(19) is not null and g_segment_low(19) <> 'LK' and segment19 between g_segment_low(19) and g_segment_high(19)) )
501 and ( (segment20 is null) or (g_segment_low(20) is null and g_segment_high(20) is null) or (g_segment_low(20) is null and segment20 <= g_segment_high(20)) or (g_segment_high(20) is null and segment20 >= g_segment_low(20)) or
502 (g_segment_low(20) = 'LK' and segment20 like g_segment_high(20)) or (g_segment_low(20) is not null and g_segment_high(20) is not null and g_segment_low(20) <> 'LK' and segment20 between g_segment_low(20) and g_segment_high(20)) )
503 and ( (segment21 is null) or (g_segment_low(21) is null and g_segment_high(21) is null) or (g_segment_low(21) is null and segment21 <= g_segment_high(21)) or (g_segment_high(21) is null and segment21 >= g_segment_low(21)) or
504 (g_segment_low(21) = 'LK' and segment21 like g_segment_high(21)) or (g_segment_low(21) is not null and g_segment_high(21) is not null and g_segment_low(21) <> 'LK' and segment21 between g_segment_low(21) and g_segment_high(21)) )
505 and ( (segment22 is null) or (g_segment_low(22) is null and g_segment_high(22) is null) or (g_segment_low(22) is null and segment22 <= g_segment_high(22)) or (g_segment_high(22) is null and segment22 >= g_segment_low(22)) or
506 (g_segment_low(22) = 'LK' and segment22 like g_segment_high(22)) or (g_segment_low(22) is not null and g_segment_high(22) is not null and g_segment_low(22) <> 'LK' and segment22 between g_segment_low(22) and g_segment_high(22)) )
507 and ( (segment23 is null) or (g_segment_low(23) is null and g_segment_high(23) is null) or (g_segment_low(23) is null and segment23 <= g_segment_high(23)) or (g_segment_high(23) is null and segment23 >= g_segment_low(23)) or
508 (g_segment_low(23) = 'LK' and segment23 like g_segment_high(23)) or (g_segment_low(23) is not null and g_segment_high(23) is not null and g_segment_low(23) <> 'LK' and segment23 between g_segment_low(23) and g_segment_high(23)) )
509 and ( (segment24 is null) or (g_segment_low(24) is null and g_segment_high(24) is null) or (g_segment_low(24) is null and segment24 <= g_segment_high(24)) or (g_segment_high(24) is null and segment24 >= g_segment_low(24)) or
510 (g_segment_low(24) = 'LK' and segment24 like g_segment_high(24)) or (g_segment_low(24) is not null and g_segment_high(24) is not null and g_segment_low(24) <> 'LK' and segment24 between g_segment_low(24) and g_segment_high(24)) )
511 and ( (segment25 is null) or (g_segment_low(25) is null and g_segment_high(25) is null) or (g_segment_low(25) is null and segment25 <= g_segment_high(25)) or (g_segment_high(25) is null and segment25 >= g_segment_low(25)) or
512 (g_segment_low(25) = 'LK' and segment25 like g_segment_high(25)) or (g_segment_low(25) is not null and g_segment_high(25) is not null and g_segment_low(25) <> 'LK' and segment25 between g_segment_low(25) and g_segment_high(25)) )
513 and ( (segment26 is null) or (g_segment_low(26) is null and g_segment_high(26) is null) or (g_segment_low(26) is null and segment26 <= g_segment_high(26)) or (g_segment_high(26) is null and segment26 >= g_segment_low(26)) or
514 (g_segment_low(26) = 'LK' and segment26 like g_segment_high(26)) or (g_segment_low(26) is not null and g_segment_high(26) is not null and g_segment_low(26) <> 'LK' and segment26 between g_segment_low(26) and g_segment_high(26)) )
515 and ( (segment27 is null) or (g_segment_low(27) is null and g_segment_high(27) is null) or (g_segment_low(27) is null and segment27 <= g_segment_high(27)) or (g_segment_high(27) is null and segment27 >= g_segment_low(27)) or
516 (g_segment_low(27) = 'LK' and segment27 like g_segment_high(27)) or (g_segment_low(27) is not null and g_segment_high(27) is not null and g_segment_low(27) <> 'LK' and segment27 between g_segment_low(27) and g_segment_high(27)) )
517 and ( (segment28 is null) or (g_segment_low(28) is null and g_segment_high(28) is null) or (g_segment_low(28) is null and segment28 <= g_segment_high(28)) or (g_segment_high(28) is null and segment28 >= g_segment_low(28)) or
518 (g_segment_low(28) = 'LK' and segment28 like g_segment_high(28)) or (g_segment_low(28) is not null and g_segment_high(28) is not null and g_segment_low(28) <> 'LK' and segment28 between g_segment_low(28) and g_segment_high(28)) )
519 and ( (segment29 is null) or (g_segment_low(29) is null and g_segment_high(29) is null) or (g_segment_low(29) is null and segment29 <= g_segment_high(29)) or (g_segment_high(29) is null and segment29 >= g_segment_low(29)) or
520 (g_segment_low(29) = 'LK' and segment29 like g_segment_high(29)) or (g_segment_low(29) is not null and g_segment_high(29) is not null and g_segment_low(29) <> 'LK' and segment29 between g_segment_low(29) and g_segment_high(29)) )
521 and ( (segment30 is null) or (g_segment_low(30) is null and g_segment_high(30) is null) or (g_segment_low(30) is null and segment30 <= g_segment_high(30)) or (g_segment_high(30) is null and segment30 >= g_segment_low(30)) or
522 (g_segment_low(30) = 'LK' and segment30 like g_segment_high(30)) or (g_segment_low(30) is not null and g_segment_high(30) is not null and g_segment_low(30) <> 'LK' and segment30 between g_segment_low(30) and g_segment_high(30)) ));
523
524 l_return_status VARCHAR2(1);
525 begin
526
527 if (nvl(pflex_value, '%') <> '%') then
528 begin
529
530 if pchart_of_accounts_id <> nvl(PSB_WS_ACCT1.g_flex_code, 0) then
531 begin
532
533 PSB_WS_ACCT1.Flex_Info (p_flex_code => pchart_of_accounts_id, p_return_status => l_return_status);
534
535 end;
536 end if;
537
538 g_flex_delimiter := fnd_flex_ext.get_delimiter(application_short_name => 'SQLGL',
539 key_flex_code => 'GL#',
540 structure_number => pchart_of_accounts_id);
541
542 g_segment_count := FND_FLEX_EXT.breakup_segments(pflex_value, g_flex_delimiter, g_segment_values);
543
544 /*For Bug No : 2012827 Start*/
545 for i in g_segment_count+1..30 loop
546 g_segment_values(i) := null;
547 end loop;
548 /*For Bug No : 2012827 End*/
549
550 assign_seg_values;
551
552 for c_sum_partial_rec in c_sum_partial loop
553
554 ptcolumn1 := c_sum_partial_rec.A;
555 ptcolumn2 := c_sum_partial_rec.B;
556 ptcolumn3 := c_sum_partial_rec.C;
557 ptcolumn4 := c_sum_partial_rec.D;
558 ptcolumn5 := c_sum_partial_rec.E;
559 ptcolumn6 := c_sum_partial_rec.F;
560 ptcolumn7 := c_sum_partial_rec.G;
561 ptcolumn8 := c_sum_partial_rec.H;
562 ptcolumn9 := c_sum_partial_rec.I;
563 ptcolumn10 := c_sum_partial_rec.J;
564 ptcolumn11 := + c_sum_partial_rec.K;
565 ptcolumn12 := c_sum_partial_rec.L;
566
567 end loop;
568
569 end;
570 else
571 begin
572
573 FOR c_sum_all_rec IN c_sum_all LOOP
574
575 ptcolumn1 := c_sum_all_rec.A;
576 ptcolumn2 := c_sum_all_rec.B;
577 ptcolumn3 := c_sum_all_rec.C;
578 ptcolumn4 := c_sum_all_rec.D;
579 ptcolumn5 := c_sum_all_rec.E;
580 ptcolumn6 := c_sum_all_rec.F;
581 ptcolumn7 := c_sum_all_rec.G;
582 ptcolumn8 := c_sum_all_rec.H;
583 ptcolumn9 := c_sum_all_rec.I;
584 ptcolumn10 := c_sum_all_rec.J;
585 ptcolumn11 := c_sum_all_rec.K;
586 ptcolumn12 := c_sum_all_rec.L;
587
588 END LOOP;
589
590 end;
591 end if;
592
593 END Position_Totals;
594
595 END PSB_WS_YEAR_TOTAL;
[Home] [Help]