[Home] [Help]
PACKAGE BODY: APPS.FV_STATUS_OF_FUNDS_PKG
Source
1 PACKAGE BODY fv_status_of_funds_pkg AS
2 /* $Header: FVXSFFDB.pls 120.10 2005/10/06 21:04:27 vtreiger ship $ | */
3
4 /*******************************************************************/
5 /***** Variable Declaration For All Processes ******/
6 /*******************************************************************/
7 g_module_name varchar2(100) := 'fv.plsql.fv_status_of_funds_pkg.' ;
8 v_budget fv_fund_parameters.budget_authority%TYPE;
9 v_commitment fv_fund_parameters.unliquid_commitments%TYPE;
10 v_obligation fv_fund_parameters.unliquid_obligations%TYPE;
11 v_expenditure fv_fund_parameters.expended_amount%TYPE;
12 v_transfers_in fv_fund_parameters.transfers_in%TYPE;
13 v_transfers_out fv_fund_parameters.transfers_out%TYPE;
14 v_fund_value fv_fund_parameters.fund_value%TYPE;
15 v_fund_value1 fv_fund_parameters.fund_value%TYPE;
16 v_treasury_symbol fv_fund_parameters.treasury_symbol%TYPE;
17 v_treasury_symbol1 fv_fund_parameters.treasury_symbol%TYPE;
18 v_statement varchar2(2);
19 err_message varchar2(300);
20 acct_flex_id fnd_flex_values.flex_value_id%TYPE;
21 v_ca_id gl_sets_of_books.chart_of_accounts_id%TYPE;
22 x_period_num gl_period_statuses.period_num%TYPE;
23 v_segment_low_name number;
24 v_segment_num number;
25 parent_flag varchar2(1);
26 rollup_type varchar2(1);
27 bal_seg_name_num number(2);
28 acct_ff_low varchar2(2000);
29 acct_ff_high varchar2(2000);
30 acct_seg_name_num number(2);
31 sob_id gl_balances.ledger_id%TYPE;
32 sob_id1 gl_balances.ledger_id%TYPE;
33 curr_code gl_balances.currency_code%TYPE;
34 pd_name gl_balances.period_name%TYPE;
35 v_value_low varchar2(60);
36 v_value_high varchar2(60);
37 acct_seg_name varchar2(25);
38 bal_seg_name varchar2(25);
39 v_pagebreak_seg1 varchar2(25);
40 v_pagebreak_seg2 varchar2(25);
41 v_pagebreak_seg3 varchar2(25);
42 v_acct_type varchar2(12);
43 v_trans_type varchar2(12);
44 v_spend_type varchar2(12);
45 v_sign varchar2(1);
46
47 -- New variable declared by pkpatel to fix Bug.1575992
48 v_treasury_symbol_id fv_treasury_symbols.treasury_symbol_id%TYPE;
49 -- Bug 1575992 Variable temp_v_treasury_symbol_id defined
50 --to be used in the cursor c_treasury_fund_values
51 temp_v_treasury_symbol_id varchar2(20) ;
52 /*******************************************************************/
53 /***** Cursor Declaration For All Processes ******/
54 /*******************************************************************/
55
56 CURSOR c_segment_info IS
57 SELECT UPPER(application_column_name) application_column_name
58 FROM fnd_id_flex_segments
59 WHERE application_id = 101
60 AND id_flex_code = 'GL#'
61 AND id_flex_num = v_ca_id
62 ORDER BY segment_num;
63
64 CURSOR c_balances_spending IS
65 SELECT sum((nvl(glb.begin_balance_dr,0) - nvl(glb.begin_balance_cr,0)) +
66 (nvl(glB.PERIOD_NET_DR,0) - nvl(glB.PERIOD_NET_cr,0))) bal_spend,
67 decode(v_pagebreak_seg1,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
68 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
69 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
70 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
71 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
72 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
73 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
74 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
75 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
76 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
77 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
78 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
79 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
80 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
81 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30) pg1,
82 decode(v_pagebreak_seg2,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
83 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
84 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
85 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
86 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
87 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
88 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
89 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
90 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
91 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
92 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
93 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
94 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
95 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
96 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30) pg2,
97 decode(v_pagebreak_seg3,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
98 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
99 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
100 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
101 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
102 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
103 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
104 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
105 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
106 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
107 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
108 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
109 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
110 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
111 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30) pg3
112 FROM gl_BALANCES glB,
113 gl_code_combinations glc
114 WHERE glb.code_combination_id = glc.code_combination_id
115 AND glc.chart_of_accounts_id = v_ca_id
116 AND glb.ledger_id = sob_id
117 AND glb.currency_code = curr_code
118 AND glb.actual_flag = 'A'
119 AND glc.enabled_flag = 'Y'
120 AND glc.template_id IS NULL
121 AND glb.period_name = pd_name
122 AND decode(bal_seg_name,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
123 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
124 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
125 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
126 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
127 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
128 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
129 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
130 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
131 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
132 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
133 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
134 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
135 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
136 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30) = v_fund_value
137 AND decode(acct_seg_name,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
138 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
139 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
140 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
141 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
142 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
143 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
144 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
145 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
146 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
147 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
148 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
149 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
150 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
151 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30)
152 between v_value_low and v_value_high
153 group by decode(v_pagebreak_seg1,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
154 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
155 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
156 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
157 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
158 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
159 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
160 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
161 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
162 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
163 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
164 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
165 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
166 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
167 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30),
168 decode(v_pagebreak_seg2,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
169 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
170 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
171 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
172 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
173 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
174 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
175 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
176 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
177 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
178 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
179 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
180 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
181 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
182 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30),
183 decode(v_pagebreak_seg3,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
184 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
185 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
186 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
187 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
188 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
189 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
190 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
191 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
192 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
193 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
194 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
195 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
196 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
197 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30) ;
198
199
200 CURSOR c_balances_budget IS
201 SELECT sum((nvl(glb.begin_balance_dr,0) - nvl(glb.begin_balance_cr,0)) +
202 (nvl(glB.PERIOD_NET_DR,0) - nvl(glB.PERIOD_NET_cr,0))) bal_bud,
203 decode(v_pagebreak_seg1,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
204 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
205 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
206 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
207 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
208 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
209 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
210 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
211 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
212 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
213 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
214 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
215 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
216 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
217 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30) pg1,
218 decode(v_pagebreak_seg2,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
219 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
220 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
221 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
222 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
223 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
224 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
225 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
226 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
227 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
228 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
229 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
230 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
231 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
232 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30) pg2,
233 decode(v_pagebreak_seg3,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
234 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
235 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
236 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
237 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
238 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
239 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
240 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
241 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
242 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
243 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
244 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
245 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
246 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
247 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30) pg3
248 FROM gl_BALANCES glB,
249 gl_code_combinations glc
250 WHERE glb.code_combination_id = glc.code_combination_id
251 AND glc.chart_of_accounts_id = v_ca_id
252 AND glb.ledger_id = sob_id
253 AND glb.currency_code = curr_code
254 AND glb.actual_flag = 'A'
255 AND glc.enabled_flag = 'Y'
256 AND glc.template_id IS NULL
257 AND glb.period_name = pd_name
258 AND decode(bal_seg_name,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
259 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
260 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
261 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
262 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
263 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
264 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
265 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
266 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
267 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
268 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
269 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
270 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
271 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
272 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30) = v_fund_value
273 AND decode(acct_seg_name,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
274 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
275 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
276 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
277 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
278 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
279 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
280 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
281 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
282 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
283 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
284 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
285 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
286 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
287 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30)
288 between v_value_low and v_value_high
289 group by decode(v_pagebreak_seg1,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
290 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
291 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
292 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
293 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
294 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
295 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
296 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
297 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
298 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
299 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
300 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
301 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
302 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
303 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30),
304 decode(v_pagebreak_seg2,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
305 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
306 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
307 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
308 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
309 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
310 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
311 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
312 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
313 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
314 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
315 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
316 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
317 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
318 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30),
319 decode(v_pagebreak_seg3,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
320 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
321 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
322 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
323 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
324 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
325 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
326 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
327 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
328 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
329 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
330 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
331 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
332 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
333 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30) ;
334 -- group by SEGMENT1, SEGMENT3, SEGMENT30;
335
336 CURSOR c_balances_transfers IS
337 SELECT sum((nvl(glb.begin_balance_dr,0) - nvl(glb.begin_balance_cr,0)) +
338 (nvl(glB.PERIOD_NET_DR,0) - nvl(glB.PERIOD_NET_cr,0))) bal_trans,
339 decode(v_pagebreak_seg1,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
340 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
341 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
342 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
343 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
344 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
345 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
346 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
347 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
348 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
349 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
350 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
351 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
352 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
353 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30) pg1,
354 decode(v_pagebreak_seg2,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
355 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
356 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
357 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
358 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
359 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
360 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
361 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
362 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
363 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
364 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
365 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
366 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
367 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
368 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30) pg2,
369 decode(v_pagebreak_seg3,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
370 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
371 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
372 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
373 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
374 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
375 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
376 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
377 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
378 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
379 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
380 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
381 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
382 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
383 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30) pg3
384 FROM gl_balances glB,
385 gl_code_combinations glc
386 WHERE glb.code_combination_id = glc.code_combination_id
387 AND glc.chart_of_accounts_id = v_ca_id
388 AND glb.ledger_id = sob_id
389 AND glb.currency_code = curr_code
390 AND glb.actual_flag = 'A'
391 AND glc.enabled_flag = 'Y'
392 AND glc.template_id IS NULL
393 AND glb.period_name = pd_name
394 AND decode(bal_seg_name,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
395 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
396 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
397 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
398 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
399 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
400 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
401 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
402 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
403 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
404 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
405 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
406 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
407 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
408 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30) = v_fund_value
409 AND decode(acct_seg_name,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
410 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
411 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
412 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
413 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
414 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
415 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
416 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
417 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
418 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
419 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
420 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
421 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
422 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
423 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30)
424 between v_value_low and v_value_high
425 group by decode(v_pagebreak_seg1,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
426 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
427 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
428 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
429 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
430 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
431 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
432 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
433 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
434 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
435 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
436 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
437 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
438 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
439 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30),
440 decode(v_pagebreak_seg2,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
441 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
442 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
443 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
444 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
445 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
446 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
447 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
448 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
449 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
450 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
451 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
452 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
453 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
454 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30),
455 decode(v_pagebreak_seg3,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
456 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
457 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
458 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
459 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
460 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
461 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
462 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
463 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
464 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
465 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
466 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
467 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
468 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
469 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30) ;
470
471
472 CURSOR c_packets_spending IS
473 SELECT sum(nvl(accounted_dr,0) - nvl(accounted_cr,0)) pac_spend,
474 decode(v_pagebreak_seg1,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
475 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
476 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
477 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
478 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
479 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
480 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
481 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
482 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
483 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
484 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
485 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
486 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
487 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
488 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30) pg1,
489 decode(v_pagebreak_seg2,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
490 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
491 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
492 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
493 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
494 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
495 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
496 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
497 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
498 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
499 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
500 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
501 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
502 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
503 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30) pg2,
504 decode(v_pagebreak_seg3,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
505 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
506 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
507 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
508 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
509 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
510 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
511 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
512 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
513 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
514 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
515 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
516 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
517 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
518 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30) pg3
519 FROM gl_bc_packets pac,
520 gl_code_combinations glc
521 WHERE pac.code_combination_id = glc.code_combination_id
522 AND glc.chart_of_accounts_id = v_ca_id
523 AND pac.ledger_id = sob_id
524 AND pac.currency_code = curr_code
525 AND pac.actual_flag = 'A'
526 AND pac.status_code in ('A','P')
527 AND glc.enabled_flag = 'Y'
528 AND glc.template_id IS NULL
529 AND substr(period_year,3,2) = substr(pd_name,8,2)
530 AND period_num between 1 and x_period_num
531 AND decode(bal_seg_name,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
532 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
533 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
534 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
535 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
536 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
537 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
538 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
539 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
540 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
541 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
542 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
543 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
544 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
545 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30) = v_fund_value
546 AND decode(acct_seg_name,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
547 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
548 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
549 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
550 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
551 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
552 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
553 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
554 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
555 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
556 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
557 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
558 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
559 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
560 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30)
561 between v_value_low and v_value_high
562 group by decode(v_pagebreak_seg1,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
563 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
564 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
565 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
566 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
567 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
568 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
569 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
570 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
571 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
572 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
573 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
574 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
575 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
576 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30),
577 decode(v_pagebreak_seg2,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
578 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
579 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
580 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
581 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
582 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
583 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
584 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
585 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
586 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
587 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
588 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
589 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
590 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
591 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30),
592 decode(v_pagebreak_seg3,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
593 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
594 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
595 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
596 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
597 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
598 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
599 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
600 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
601 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
602 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
603 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
604 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
605 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
606 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30) ;
607
608
609
610 CURSOR c_packets_budget IS
611 SELECT sum(nvl(accounted_dr,0) - nvl(accounted_cr,0)) pac_bud,
612 decode(v_pagebreak_seg1,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
613 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
614 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
615 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
616 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
617 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
618 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
619 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
620 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
621 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
622 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
623 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
624 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
625 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
626 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30) pg1,
627 decode(v_pagebreak_seg2,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
628 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
629 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
630 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
631 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
632 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
633 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
634 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
635 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
636 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
637 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
638 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
639 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
640 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
641 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30) pg2,
642 decode(v_pagebreak_seg3,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
643 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
644 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
645 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
646 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
647 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
648 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
649 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
650 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
651 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
652 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
653 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
654 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
655 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
656 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30) pg3
657 FROM gl_bc_packets pac,
658 gl_code_combinations glc
659 WHERE pac.code_combination_id = glc.code_combination_id
660 AND pac.ledger_id = sob_id
661 AND glc.chart_of_accounts_id = v_ca_id
662 AND pac.currency_code = curr_code
663 AND pac.actual_flag = 'A'
664 AND pac.status_code in ('A','P')
665 AND glc.enabled_flag = 'Y'
666 AND glc.template_id IS NULL
667 AND substr(period_year,3,2) = substr(pd_name,8,2)
668 AND period_num between 1 and x_period_num
669 AND decode(bal_seg_name,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
670 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
671 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
672 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
673 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
674 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
675 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
676 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
677 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
678 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
679 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
680 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
681 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
682 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
683 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30) = v_fund_value
684 AND decode(acct_seg_name,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
685 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
686 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
687 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
688 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
689 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
690 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
691 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
692 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
693 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
694 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
695 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
696 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
697 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
698 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30)
699 between v_value_low and v_value_high
700 group by decode(v_pagebreak_seg1,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
701 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
702 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
703 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
704 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
705 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
706 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
707 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
708 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
709 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
710 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
711 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
712 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
713 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
714 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30),
715 decode(v_pagebreak_seg2,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
716 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
717 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
718 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
719 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
720 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
721 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
722 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
723 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
724 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
725 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
726 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
727 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
728 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
729 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30),
730 decode(v_pagebreak_seg3,'SEGMENT1',glc.segment1,'SEGMENT2',glc.segment2,
731 'SEGMENT3',glc.segment3,'SEGMENT4',glc.segment4,
732 'SEGMENT5',glc.segment5,'SEGMENT6',glc.segment6,
733 'SEGMENT7',glc.segment7,'SEGMENT8',glc.segment8,
734 'SEGMENT9',glc.segment9,'SEGMENT10',glc.segment10,
735 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12,
736 'SEGMENT13',glc.segment13,'SEGMENT14',glc.segment14,
737 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16,
738 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18,
739 'SEGMENT19',glc.segment19,'SEGMENT20',glc.segment20,
740 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22,
741 'SEGMENT23',glc.segment23,'SEGMENT24',glc.segment24,
742 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26,
743 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28,
744 'SEGMENT29',glc.segment29,'SEGMENT30',glc.segment30) ;
745
746 /* Changed treasury_symbol to Treasury_symbol_id #bug 1575992
747 Bug 1575992 Modified the decode statement
748 Added treasury_symbol_id for Bug 1829771 */
749 CURSOR c_treasury_fund_values IS
750 SELECT fp.fund_value,
751 fp.budget_authority,
752 fp.transfers_in,
753 fp.transfers_out,
754 fp.unliquid_commitments,
755 fp.unliquid_obligations,
756 fp.expended_amount ,
757 fp.treasury_symbol_id
758 FROM fv_fund_parameters fp
759 WHERE fp.set_of_books_id = sob_id
760 AND decode(rollup_type,'T',to_char(fp.treasury_symbol_id),'F',fp.fund_value)
761 between decode(rollup_type,'T',nvl(temp_v_treasury_symbol_id,fp.treasury_symbol_id),
762 'F',nvl(acct_ff_low,fp.fund_value) )
763 and decode(rollup_type,'T',nvl(temp_v_treasury_symbol_id,fp.treasury_symbol_id),
764 'F',nvl(acct_ff_high,fp.fund_value) );
765
766
767 CURSOR c_get_temptable_info IS
768 SELECT pagebk1,pagebk2,pagebk3,sum(acct_total) atot
769 FROM fv_status_funds_temp
770 GROUP BY pagebk1,pagebk2,pagebk3;
771
772 /******************************************************************/
773 /* This procedures sums the budget authority */
774 /******************************************************************/
775 PROCEDURE sum_children_budget
776 (v_parent_value IN fnd_flex_values.flex_value%TYPE,
777 parent_value_id IN fnd_flex_values.flex_value_id%TYPE,
778 sob_id1 IN gl_balances.ledger_id%TYPE,
779 v_treasury_symbol1 IN fv_fund_parameters.treasury_symbol%TYPE,
780 v_fund_value1 IN fv_fund_parameters.fund_value%TYPE,
781 v_treasury_symbol_id1 IN fv_treasury_symbols.treasury_symbol_id%TYPE ) --Changed to Fix 1575992
782 Is
783 CURSOR c_parent_value_budget IS
784 SELECT child_flex_value_low, child_flex_value_high
785 FROM fnd_flex_value_hierarchies
786 WHERE parent_flex_value = v_parent_value
787 AND flex_value_set_id = parent_value_id;
788 l_module_name varchar2(200) := g_module_name || 'sum_children_budget';
789 l_errbuf varchar2(300);
790 BEGIN
791 parent_flag := 'N';
792
793 begin
794 select substr(compiled_value_attributes,5,1)
795 into v_sign
796 from fnd_flex_values
797 where flex_value = v_parent_value
798 and flex_value_set_id = parent_value_id;
799
800 EXCEPTION
801 WHEN NO_DATA_FOUND THEN
802 null;
803
804
805 end;
806
807
808 DELETE FROM fv_status_funds_temp;
809 COMMIT;
810
811 OPEN c_parent_value_budget;
812 LOOP
813 FETCH c_parent_value_budget into v_value_low, v_value_high;
814
815 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
816 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V_PARENT_VALUE FOR BUDGET'||V_PARENT_VALUE);
817 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V_VALUE_LOW FOR BUDGET'||V_VALUE_LOW);
818 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V_VALUE_HIGH FOR BUDGET'||V_VALUE_HIGH);
819 END IF;
820
821 IF c_parent_value_budget%FOUND THEN
822 FOR c_balances_budget_rec IN c_balances_budget LOOP
823
824 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
825 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V_PARENT_VALUE FOR BUDGET'||V_PARENT_VALUE);
826 END IF;
827 INSERT INTO fv_status_funds_temp
828 (pagebk1,pagebk2,pagebk3,acct_total) VALUES
829 (c_balances_budget_rec.pg1,c_balances_budget_rec.pg2,
830 c_balances_budget_rec.pg3,
831 c_balances_budget_rec.bal_bud);
832 END LOOP;
833 FOR c_packets_budget_rec IN c_packets_budget LOOP
834 INSERT INTO fv_status_funds_temp
835 (pagebk1,pagebk2,pagebk3,acct_total) VALUES
836 (c_packets_budget_rec.pg1,c_packets_budget_rec.pg2,
837 c_packets_budget_rec.pg3,c_packets_budget_rec.pac_bud);
838 END LOOP;
839 parent_flag := 'Y';
840 ELSE
841 -- no child record found
842
843 IF parent_flag = 'N' THEN
844 -- since there are no children assign account to high and low
845 v_value_low := v_parent_value;
846 v_value_high := v_parent_value;
847 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
848 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'IN THE LOOP WHERE NO CHILD FOUND');
849 END IF;
850
851 FOR c_balances_budget_rec IN c_balances_budget LOOP
852 INSERT INTO fv_status_funds_temp
853 (pagebk1,pagebk2,pagebk3,acct_total) VALUES
854 (c_balances_budget_rec.pg1,c_balances_budget_rec.pg2,
855 c_balances_budget_rec.pg3,c_balances_budget_rec.bal_bud);
856 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
857 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'C_BAL_BUD_REC.PG1 :'||C_BALANCES_BUDGET_REC.PG1);
858 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'C_BAL_BUD_REC.PG2 :'||C_BALANCES_BUDGET_REC.PG2);
859 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'C_BAL_BUD_REC.PG3 :'||C_BALANCES_BUDGET_REC.PG3);
860 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'C_BAL_BUD_REC.BAL_BUD :'||C_BALANCES_BUDGET_REC.BAL_BUD);
861 END IF;
862 END LOOP;
863 FOR c_packets_budget_rec IN c_packets_budget LOOP
864 INSERT INTO fv_status_funds_temp
865 (pagebk1,pagebk2,pagebk3,acct_total) VALUES
866 (c_packets_budget_rec.pg1,c_packets_budget_rec.pg2,
867 c_packets_budget_rec.pg3,c_packets_budget_rec.pac_bud);
868 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
869 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'C_PACKATS.PG1 :'||C_PACKETS_BUDGET_REC.PG1);
870 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'C_PACKATES.PG2 :'||C_PACKETS_BUDGET_REC.PG2);
871 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'C_PACKETS.PG3 :'||C_PACKETS_BUDGET_REC.PG3);
872 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'C_PACKETS.PAC_BUD :'||C_PACKETS_BUDGET_REC.PAC_BUD);
873 END IF;
874 END LOOP;
875 FOR c_get_temptable_info_rec IN c_get_temptable_info LOOP
876
877 -- modified to Fix Bug.1575992
878 INSERT INTO fv_status_funds_final
879 (set_of_books_id,treasury_symbol,fund,pagebreak_seg1,
880 pagebreak_seg2,pagebreak_seg3,
881 treasury_symbol_id,
882 budget_auth_total)
883 VALUES (sob_id1,v_treasury_symbol1,v_fund_value1,
884 c_get_temptable_info_rec.pagebk1,
885 c_get_temptable_info_rec.pagebk2,
886 c_get_temptable_info_rec.pagebk3,
887 v_treasury_symbol_id1,
888 decode(v_sign,'C',(c_get_temptable_info_rec.atot*-1),c_get_temptable_info_rec.atot));
889
890 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
891 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'C_GET_TEMPTABLE_INFO_REC.PAGEBK1 :'||C_GET_TEMPTABLE_INFO_REC.PAGEBK1);
892 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'C_GET_TEMPTABLE_INFO_REC.PAGEBK2 :'||C_GET_TEMPTABLE_INFO_REC.PAGEBK2);
893 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'C_GET_TEMPTABLE_INFO_REC.PAGEBK3 :'||C_GET_TEMPTABLE_INFO_REC.PAGEBK3);
894 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'C_GET_TEMPTABLE_INFO_REC.ATOT :'||C_GET_TEMPTABLE_INFO_REC.ATOT);
895 END IF;
896 END LOOP;
897 END IF;
898 exit;
899 END IF;
900 END LOOP;
901
902 CLOSE c_parent_value_budget;
903 IF parent_flag = 'Y' then
904 FOR c_get_temptable_info_rec IN c_get_temptable_info LOOP
905
906 --Modified to Fix bug.1575992
907 INSERT INTO fv_status_funds_final
908 (set_of_books_id,treasury_symbol,treasury_symbol_id,fund,pagebreak_seg1,
909 pagebreak_seg2,pagebreak_seg3,budget_auth_total)
910 VALUES (sob_id1,v_treasury_symbol1,v_treasury_symbol_id1,v_fund_value1,
911 c_get_temptable_info_rec.pagebk1,
912 c_get_temptable_info_rec.pagebk2,
913 c_get_temptable_info_rec.pagebk3,
914 decode(v_sign,'C',(c_get_temptable_info_rec.atot*-1),c_get_temptable_info_rec.atot));
915 END LOOP;
916 END IF;
917 EXCEPTION
918 WHEN OTHERS THEN
919 l_errbuf := SQLERRM;
920 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',l_errbuf);
921 raise;
922 END sum_children_budget;
923
924
925 /******************************************************************/
926 /* This procedures sums the transfers accounts for the account */
927 /* passed to it. */
928 /******************************************************************/
929 PROCEDURE sum_children_transfers
930 (v_parent_value IN fnd_flex_values.flex_value%TYPE,
931 parent_value_id IN fnd_flex_values.flex_value_id%TYPE,
932 v_trans_type IN VARCHAR2,
933 sob_id1 IN gl_balances.ledger_id%TYPE,
934 v_treasury_symbol1 IN fv_fund_parameters.treasury_symbol%TYPE,
935 v_fund_value1 IN fv_fund_parameters.fund_value%TYPE,
936 v_treasury_symbol_id1 IN fv_treasury_symbols.treasury_symbol_id%TYPE )--Added to fix bug.1575992
937
938 IS
939 CURSOR c_parent_value_transfers IS
940 SELECT child_flex_value_low, child_flex_value_high
941 FROM fnd_flex_value_hierarchies
942 WHERE parent_flex_value = v_parent_value
943 AND flex_value_set_id = parent_value_id;
944 l_module_name varchar2(200) := g_module_name || 'sum_children_transfers';
945 l_errbuf varchar2(300);
946
947 BEGIN
948 parent_flag := 'N';
949
950 begin
951 select substr(compiled_value_attributes,5,1)
952 into v_sign
953 from fnd_flex_values
954 where flex_value = v_parent_value
955 and flex_value_set_id = parent_value_id;
956
957 EXCEPTION
958 WHEN NO_DATA_FOUND THEN
959 null;
960 end;
961
962 DELETE FROM fv_status_funds_temp;
963 COMMIT;
964
965 OPEN c_parent_value_transfers;
966 LOOP
967 FETCH c_parent_value_transfers into v_value_low, v_value_high ;
968
969 IF c_parent_value_transfers%FOUND THEN
970 FOR c_balances_transfers_rec IN c_balances_transfers LOOP
971 INSERT INTO fv_status_funds_temp
972 (pagebk1,pagebk2,pagebk3,acct_total) VALUES
973 (c_balances_transfers_rec.pg1,c_balances_transfers_rec.pg2,
974 c_balances_transfers_rec.pg3,c_balances_transfers_rec.bal_trans);
975 END LOOP;
976 FOR c_packets_spending_rec IN c_packets_spending LOOP
977 INSERT INTO fv_status_funds_temp
978 (pagebk1,pagebk2,pagebk3,acct_total) VALUES
979 (c_packets_spending_rec.pg1,c_packets_spending_rec.pg2,
980 c_packets_spending_rec.pg3,c_packets_spending_rec.pac_spend);
981 END LOOP;
982
983 parent_flag := 'Y';
984 ELSE
985 -- no child record found
986
987 IF parent_flag = 'N' THEN
988 -- since there are no children assign account to high and low
989 v_value_low := v_parent_value;
990 v_value_high:= v_parent_value;
991
992 FOR c_balances_transfers_rec IN c_balances_transfers LOOP
993 INSERT INTO fv_status_funds_temp
994 (pagebk1,pagebk2,pagebk3,acct_total) VALUES
995 (c_balances_transfers_rec.pg1,c_balances_transfers_rec.pg2,
996 c_balances_transfers_rec.pg3,c_balances_transfers_rec.bal_trans);
997 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
998 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'C_BAL_TRANS_REC.PG1 :'||C_BALANCES_TRANSFERS_REC.PG1);
999 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'C_BAL_TRANS_REC.PG2 :'||C_BALANCES_TRANSFERS_REC.PG2);
1000 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'C_BAL_TRANS_REC.PG3 :'||C_BALANCES_TRANSFERS_REC.PG3);
1001 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'C_BAL_TRANS_REC.BAL_TRANS :'||C_BALANCES_TRANSFERS_REC.BAL_TRANS);
1002 END IF;
1003 END LOOP;
1004 FOR c_packets_spending_rec IN c_packets_spending LOOP
1005 INSERT INTO fv_status_funds_temp
1006 (pagebk1,pagebk2,pagebk3,acct_total) VALUES
1007 (c_packets_spending_rec.pg1,c_packets_spending_rec.pg2,
1008 c_packets_spending_rec.pg3,c_packets_spending_rec.pac_spend);
1009 END LOOP;
1010 FOR c_get_temptable_info_rec IN c_get_temptable_info LOOP
1011 IF v_trans_type = 'TRANS_IN' THEN
1012 UPDATE fv_status_funds_final SET
1013 transfers_in_total = decode(v_sign,'C',(c_get_temptable_info_rec.atot*-1),c_get_temptable_info_rec.atot)
1014 WHERE pagebreak_seg1 = c_get_temptable_info_rec.pagebk1
1015 AND set_of_books_id = sob_id1
1016 AND pagebreak_seg2 = c_get_temptable_info_rec.pagebk2
1017 AND pagebreak_seg3 = c_get_temptable_info_rec.pagebk3
1018 AND fund = v_fund_value1;
1019
1020 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1021 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'TRANS_IN:C_GET_TEMPTABLE_INFO_REC.ATOT :'||C_GET_TEMPTABLE_INFO_REC.ATOT);
1022 END IF;
1023
1024 -- Modified to fix Bug.1575992
1025 IF SQL%ROWCOUNT = 0 THEN
1026 INSERT INTO fv_status_funds_final
1027 (set_of_books_id,treasury_symbol,treasury_symbol_id,fund,pagebreak_seg1,
1028 pagebreak_seg2,pagebreak_seg3,transfers_in_total)
1029 VALUES (sob_id1,v_treasury_symbol1,v_treasury_symbol_id1,v_fund_value1,
1030 c_get_temptable_info_rec.pagebk1,
1031 c_get_temptable_info_rec.pagebk2,
1032 c_get_temptable_info_rec.pagebk3,
1033 decode(v_sign,'C',(c_get_temptable_info_rec.atot*-1),c_get_temptable_info_rec.atot));
1034
1035 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1036 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'TRANS_IN_2:C_GET_TEMPTABLE_INFO_REC.ATOT :'||C_GET_TEMPTABLE_INFO_REC.ATOT);
1037 END IF;
1038 END IF;
1039 ELSIF v_trans_type = 'TRANS_OUT' THEN
1040 UPDATE fv_status_funds_final SET
1041 transfers_out_total =decode(v_sign,'C',(c_get_temptable_info_rec.atot*-1),c_get_temptable_info_rec.atot)
1042 WHERE pagebreak_seg1 = c_get_temptable_info_rec.pagebk1
1043 AND set_of_books_id = sob_id1
1044 AND pagebreak_seg2 = c_get_temptable_info_rec.pagebk2
1045 AND pagebreak_seg3 = c_get_temptable_info_rec.pagebk3
1046 AND fund = v_fund_value1;
1047
1048 IF SQL%ROWCOUNT = 0 THEN
1049 --Modified to fix Bug.1575992
1050 INSERT INTO fv_status_funds_final
1051 (set_of_books_id,treasury_symbol,treasury_symbol_id,fund,pagebreak_seg1,
1052 pagebreak_seg2,pagebreak_seg3,transfers_out_total)
1053 VALUES (sob_id1,v_treasury_symbol1,v_treasury_symbol_id1,v_fund_value1,
1054 c_get_temptable_info_rec.pagebk1,
1055 c_get_temptable_info_rec.pagebk2,
1056 c_get_temptable_info_rec.pagebk3,
1057 decode(v_sign,'C',(c_get_temptable_info_rec.atot*-1),c_get_temptable_info_rec.atot));
1058 END IF;
1059 END IF;-- checking for trans type
1060 END LOOP;-- c_get_temptable_info 'for' loop
1061 END IF;
1062 exit;
1063 END IF;
1064 END LOOP;
1065
1066 CLOSE c_parent_value_transfers;
1067 IF parent_flag = 'Y' THEN
1068 FOR c_get_temptable_info_rec IN c_get_temptable_info LOOP
1069 IF v_trans_type = 'TRANS_IN' THEN
1070 UPDATE fv_status_funds_final SET
1071 transfers_in_total = decode(v_sign,'C',(c_get_temptable_info_rec.atot*-1),c_get_temptable_info_rec.atot)
1072 WHERE pagebreak_seg1 = c_get_temptable_info_rec.pagebk1
1073 AND set_of_books_id = sob_id1
1074 AND pagebreak_seg2 = c_get_temptable_info_rec.pagebk2
1075 AND pagebreak_seg3 = c_get_temptable_info_rec.pagebk3
1076 AND fund = v_fund_value1;
1077
1078 IF SQL%ROWCOUNT = 0 THEN
1079 --Modified to Fix bug.1575992
1080 INSERT INTO fv_status_funds_final
1081 (set_of_books_id,treasury_symbol,treasury_symbol_id,fund,pagebreak_seg1,
1082 pagebreak_seg2,pagebreak_seg3,transfers_in_total)
1083 VALUES (sob_id1,v_treasury_symbol1,v_treasury_symbol_id1,v_fund_value1,
1084 c_get_temptable_info_rec.pagebk1,
1085 c_get_temptable_info_rec.pagebk2,
1086 c_get_temptable_info_rec.pagebk3,
1087 decode(v_sign,'C',(c_get_temptable_info_rec.atot*-1),c_get_temptable_info_rec.atot));
1088
1089 END IF;
1090 ELSIF v_trans_type = 'TRANS_OUT' THEN
1091 UPDATE fv_status_funds_final SET
1092 transfers_out_total = decode(v_sign,'C',(c_get_temptable_info_rec.atot*-1),c_get_temptable_info_rec.atot)
1093 WHERE pagebreak_seg1 = c_get_temptable_info_rec.pagebk1
1094 AND pagebreak_seg2 = c_get_temptable_info_rec.pagebk2
1095 AND pagebreak_seg3 = c_get_temptable_info_rec.pagebk3
1096 AND set_of_books_id = sob_id1
1097 AND fund = v_fund_value1;
1098
1099 IF SQL%ROWCOUNT = 0 THEN
1100 --Modified to fix Bug.1575992
1101 INSERT INTO fv_status_funds_final
1102 (set_of_books_id,treasury_symbol,treasury_symbol_id,fund,pagebreak_seg1,
1103 pagebreak_seg2,pagebreak_seg3,transfers_out_total)
1104 VALUES (sob_id1,v_treasury_symbol1,v_treasury_symbol_id1,v_fund_value1,
1105 c_get_temptable_info_rec.pagebk1,
1106 c_get_temptable_info_rec.pagebk2,
1107 c_get_temptable_info_rec.pagebk3,
1108 decode(v_sign,'C',(c_get_temptable_info_rec.atot*-1),c_get_temptable_info_rec.atot));
1109
1110 END IF;
1111 END IF;-- checking for trans type
1112 END LOOP;-- c_get_temptable_info 'for' loop
1113 END IF;
1114 EXCEPTION
1115 WHEN OTHERS THEN
1116 l_errbuf := SQLERRM;
1117 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',l_errbuf);
1118 raise;
1119 END sum_children_transfers;
1120
1121
1122 /******************************************************************/
1123 /* This procedure sums the spending accounts for the account */
1124 /* passed to it. */
1125 /******************************************************************/
1126 -- Modified to Fix bug.1575992
1127 PROCEDURE sum_children_spending
1128 (v_parent_value IN fnd_flex_values.flex_value%TYPE,
1129 parent_value_id IN fnd_flex_values.flex_value_id%TYPE,
1130 v_spend_type IN varchar2,
1131 sob_id1 IN gl_balances.ledger_id%TYPE,
1132 v_treasury_symbol1 IN fv_fund_parameters.treasury_symbol%TYPE,
1133 v_fund_value1 IN fv_fund_parameters.fund_value%TYPE,
1134 v_treasury_symbol_id1 IN fv_treasury_symbols.treasury_symbol_id%TYPE )
1135
1136 IS
1137 CURSOR c_parent_value_spending IS
1138 SELECT child_flex_value_low, child_flex_value_high
1139 FROM fnd_flex_value_hierarchies
1140 WHERE parent_flex_value = v_parent_value
1141 AND flex_value_set_id = parent_value_id;
1142 l_module_name varchar2(200) := g_module_name || 'sum_children_spending';
1143 l_errbuf varchar2(300);
1144 BEGIN
1145 parent_flag := 'N';
1146
1147 DELETE FROM fv_status_funds_temp;
1148 COMMIT;
1149
1150 OPEN c_parent_value_spending;
1151 LOOP
1152 FETCH c_parent_value_spending into v_value_low, v_value_high;
1153 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1154 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V_VALUE_LOW '||V_VALUE_LOW);
1155 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V_VALUE_HIGH '||V_VALUE_HIGH);
1156 END IF;
1157 IF c_parent_value_spending%FOUND THEN
1158 FOR c_balances_spending_rec IN c_balances_spending LOOP
1159 INSERT INTO fv_status_funds_temp
1160 (pagebk1,pagebk2,pagebk3,acct_total) VALUES
1161 (c_balances_spending_rec.pg1,c_balances_spending_rec.pg2,
1162 c_balances_spending_rec.pg3,c_balances_spending_rec.bal_spend);
1163 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1164 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'C_BAL_SPEND_REC.PG1 :'||C_BALANCES_SPENDING_REC.PG1);
1165 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'C_BAL_SPEND_REC.PG2 :'||C_BALANCES_SPENDING_REC.PG2);
1166 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'C_BAL_SPEND_REC.PG3 :'||C_BALANCES_SPENDING_REC.PG3);
1167 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'C_BAL_SPEND_REC.BAL_SPEND :'||C_BALANCES_SPENDING_REC.BAL_SPEND);
1168 END IF;
1169
1170 END LOOP;
1171 FOR c_packets_spending_rec IN c_packets_spending LOOP
1172 INSERT INTO fv_status_funds_temp
1173 (pagebk1,pagebk2,pagebk3,acct_total) VALUES
1174 (c_packets_spending_rec.pg1,c_packets_spending_rec.pg2,
1175 c_packets_spending_rec.pg3,c_packets_spending_rec.pac_spend);
1176 END LOOP;
1177 parent_flag := 'Y';
1178 ELSIF c_parent_value_spending%NOTFOUND THEN
1179 -- no child record found
1180 IF parent_flag = 'N' THEN
1181 -- since there are no children assign account to high and low
1182 v_value_low := v_parent_value;
1183 v_value_high := v_parent_value;
1184
1185 FOR c_balances_spending_rec IN c_balances_spending LOOP
1186 INSERT INTO fv_status_funds_temp
1187 (pagebk1,pagebk2,pagebk3,acct_total) VALUES
1188 (c_balances_spending_rec.pg1,c_balances_spending_rec.pg2,
1189 c_balances_spending_rec.pg3,c_balances_spending_rec.bal_spend);
1190 END LOOP;
1191 FOR c_packets_spending_rec IN c_packets_spending LOOP
1192 INSERT INTO fv_status_funds_temp
1193 (pagebk1,pagebk2,pagebk3,acct_total) VALUES
1194 (c_packets_spending_rec.pg1,c_packets_spending_rec.pg2,
1195 c_packets_spending_rec.pg3,c_packets_spending_rec.pac_spend);
1196 END LOOP;
1197 FOR c_get_temptable_info_rec IN c_get_temptable_info LOOP
1198 IF v_spend_type = 'UNLIQ_COMM' THEN
1199 UPDATE fv_status_funds_final SET
1200 unliquid_comm_total = decode(v_sign,'C',nvl(c_get_temptable_info_rec.atot,0) * (-1),nvl(c_get_temptable_info_rec.atot,0))
1201 WHERE pagebreak_seg1 = c_get_temptable_info_rec.pagebk1
1202 AND pagebreak_seg2 = c_get_temptable_info_rec.pagebk2
1203 AND pagebreak_seg3 = c_get_temptable_info_rec.pagebk3
1204 AND set_of_books_id = sob_id1
1205 AND fund = v_fund_value1;
1206
1207 IF SQL%ROWCOUNT = 0 THEN
1208 --Modified to Fix bug.1575992
1209 INSERT INTO fv_status_funds_final
1210 (set_of_books_id,treasury_symbol,treasury_symbol_id,fund,pagebreak_seg1,
1211 pagebreak_seg2,pagebreak_seg3,unliquid_comm_total)
1212 VALUES (sob_id1,v_treasury_symbol1,v_treasury_symbol_id1,v_fund_value1,
1213 c_get_temptable_info_rec.pagebk1,
1214 c_get_temptable_info_rec.pagebk2,
1215 c_get_temptable_info_rec.pagebk3,
1216 decode(v_sign,'C',nvl(c_get_temptable_info_rec.atot,0) * (-1),nvl(c_get_temptable_info_rec.atot,0)));
1217 END IF;
1218 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1219 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'C_GET_TEMP.PAGEBK1 :'||C_GET_TEMPTABLE_INFO_REC.PAGEBK1);
1220 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'C_GET_TEMPTABLE_INFO_REC.PAGEBK2 :'||C_GET_TEMPTABLE_INFO_REC.PAGEBK2);
1221 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'C_GET_TEMPTABLE_INFO_REC.PAGEBK3 :'||C_GET_TEMPTABLE_INFO_REC.PAGEBK3);
1222 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'C_GET_TEMPTABLE_INFO_REC.ATOT :'||C_GET_TEMPTABLE_INFO_REC.ATOT);
1223 END IF;
1224 ELSIF v_spend_type = 'UNLIQ_OBLIG' THEN
1225 UPDATE fv_status_funds_final SET
1226 unliquid_oblig_total = decode(v_sign,'C',nvl(c_get_temptable_info_rec.atot,0) * (-1),nvl(c_get_temptable_info_rec.atot,0))
1227 WHERE pagebreak_seg1 = c_get_temptable_info_rec.pagebk1
1228 AND pagebreak_seg2 = c_get_temptable_info_rec.pagebk2
1229 AND pagebreak_seg3 = c_get_temptable_info_rec.pagebk3
1230 AND set_of_books_id = sob_id1
1231 AND fund = v_fund_value1;
1232
1233 IF SQL%ROWCOUNT = 0 THEN
1234 --Modified to Fix bug.1575992
1235 INSERT INTO fv_status_funds_final
1236 (set_of_books_id,treasury_symbol,treasury_symbol_id,fund,pagebreak_seg1,
1237 pagebreak_seg2,pagebreak_seg3,unliquid_oblig_total)
1238 VALUES (sob_id1,v_treasury_symbol1,v_treasury_symbol_id1,v_fund_value1,
1239 c_get_temptable_info_rec.pagebk1,
1240 c_get_temptable_info_rec.pagebk2,
1241 c_get_temptable_info_rec.pagebk3,
1242 decode(v_sign,'C',nvl(c_get_temptable_info_rec.atot,0) * (-1),nvl(c_get_temptable_info_rec.atot,0)));
1243 END IF;
1244 ELSIF v_spend_type = 'EXPEN_AMT' THEN
1245 UPDATE fv_status_funds_final SET
1246 expen_amt_total = decode(v_sign,'C',nvl(c_get_temptable_info_rec.atot,0) * (-1),nvl(c_get_temptable_info_rec.atot,0))
1247 WHERE pagebreak_seg1 = c_get_temptable_info_rec.pagebk1
1248 AND pagebreak_seg2 = c_get_temptable_info_rec.pagebk2
1249 AND pagebreak_seg3 = c_get_temptable_info_rec.pagebk3
1250 AND set_of_books_id = sob_id1
1251 AND fund = v_fund_value1;
1252
1253 IF SQL%ROWCOUNT = 0 THEN
1254 --Modified to fix Bug.1575992
1255 INSERT INTO fv_status_funds_final
1256 (set_of_books_id,treasury_symbol,treasury_symbol_id,fund,pagebreak_seg1,
1257 pagebreak_seg2,pagebreak_seg3,expen_amt_total)
1258 VALUES (sob_id1,v_treasury_symbol1,v_treasury_symbol_id1,v_fund_value1,
1259 c_get_temptable_info_rec.pagebk1,
1260 c_get_temptable_info_rec.pagebk2,
1261 c_get_temptable_info_rec.pagebk3,
1262 decode(v_sign,'C',nvl(c_get_temptable_info_rec.atot,0) * (-1),nvl(c_get_temptable_info_rec.atot,0)));
1263 END IF;
1264 END IF;-- checking for trans type
1265 END LOOP;-- c_get_temptable_info 'for' loop
1266 END IF;
1267 exit;
1268 END IF;
1269 END LOOP;
1270 CLOSE c_parent_value_spending;
1271 IF parent_flag = 'Y' THEN
1272 FOR c_get_temptable_info_rec IN c_get_temptable_info LOOP
1273 IF v_spend_type = 'UNLIQ_COMM' THEN
1274 UPDATE fv_status_funds_final SET
1275 unliquid_comm_total = decode(v_sign,'C',nvl(c_get_temptable_info_rec.atot,0) * (-1),nvl(c_get_temptable_info_rec.atot,0))
1276 WHERE pagebreak_seg1 = c_get_temptable_info_rec.pagebk1
1277 AND pagebreak_seg2 = c_get_temptable_info_rec.pagebk2
1278 AND pagebreak_seg3 = c_get_temptable_info_rec.pagebk3
1279 AND set_of_books_id = sob_id1
1280 AND fund = v_fund_value1;
1281
1282 IF SQL%ROWCOUNT = 0 THEN
1283 --Modified to fix bug.1575992
1284 INSERT INTO fv_status_funds_final
1285 (set_of_books_id,treasury_symbol,treasury_symbol_id,fund,pagebreak_seg1,
1286 pagebreak_seg2,pagebreak_seg3,unliquid_comm_total)
1287 VALUES (sob_id1,v_treasury_symbol1,v_treasury_symbol_id1,v_fund_value1,
1288 c_get_temptable_info_rec.pagebk1,
1289 c_get_temptable_info_rec.pagebk2,
1290 c_get_temptable_info_rec.pagebk3,
1291 decode(v_sign,'C',nvl(c_get_temptable_info_rec.atot,0) * (-1),nvl(c_get_temptable_info_rec.atot,0)));
1292 END IF;
1293 ELSIF v_spend_type = 'UNLIQ_OBLIG' THEN
1294 UPDATE fv_status_funds_final SET
1295 unliquid_oblig_total = decode(v_sign,'C',nvl(c_get_temptable_info_rec.atot,0) * (-1),nvl(c_get_temptable_info_rec.atot,0))
1296 WHERE pagebreak_seg1 = c_get_temptable_info_rec.pagebk1
1297 AND pagebreak_seg2 = c_get_temptable_info_rec.pagebk2
1298 AND pagebreak_seg3 = c_get_temptable_info_rec.pagebk3
1299 AND set_of_books_id = sob_id1
1300 AND fund = v_fund_value1;
1301
1302 IF SQL%ROWCOUNT = 0 THEN
1303 --Modified to fix Bug.1575992
1304 INSERT INTO fv_status_funds_final
1305 (set_of_books_id,treasury_symbol,treasury_symbol_id,fund,pagebreak_seg1,
1306 pagebreak_seg2,pagebreak_seg3,unliquid_oblig_total)
1307 VALUES (sob_id1,v_treasury_symbol1,v_treasury_symbol_id1,v_fund_value1,
1308 c_get_temptable_info_rec.pagebk1,
1309 c_get_temptable_info_rec.pagebk2,
1310 c_get_temptable_info_rec.pagebk3,
1311 decode(v_sign,'C',nvl(c_get_temptable_info_rec.atot,0) * (-1),nvl(c_get_temptable_info_rec.atot,0)));
1312
1313 END IF;
1314 ELSIF v_spend_type = 'EXPEN_AMT' THEN
1315 UPDATE fv_status_funds_final SET
1316 expen_amt_total = decode(v_sign,'C',nvl(c_get_temptable_info_rec.atot,0) * (-1),nvl(c_get_temptable_info_rec.atot,0))
1317 WHERE pagebreak_seg1 = c_get_temptable_info_rec.pagebk1
1318 AND pagebreak_seg2 = c_get_temptable_info_rec.pagebk2
1319 AND pagebreak_seg3 = c_get_temptable_info_rec.pagebk3
1320 AND set_of_books_id = sob_id1
1321 ANd fund = v_fund_value1;
1322
1323 IF SQL%ROWCOUNT = 0 THEN
1324 --Modified to fix Bug.1575992
1325 INSERT INTO fv_status_funds_final
1326 (set_of_books_id,treasury_symbol,treasury_symbol_id,fund,pagebreak_seg1,
1327 pagebreak_seg2,pagebreak_seg3,expen_amt_total)
1328 VALUES (sob_id1,v_treasury_symbol1,v_treasury_symbol_id1,v_fund_value1,
1329 c_get_temptable_info_rec.pagebk1,
1330 c_get_temptable_info_rec.pagebk2,
1331 c_get_temptable_info_rec.pagebk3,
1332 decode(v_sign,'C',nvl(c_get_temptable_info_rec.atot,0) * (-1),nvl(c_get_temptable_info_rec.atot,0)));
1333 END IF;
1334 END IF;-- checking for trans type
1335 END LOOP;-- c_get_temptable_info 'for' loop
1336 END IF;
1337 EXCEPTION
1338 WHEN OTHERS THEN
1339 l_errbuf := SQLERRM;
1340 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',l_errbuf);
1341 raise;
1342 END sum_children_spending;
1343
1344 /*************************************************************************/
1345 /* This function finds the flex_value_id for the account being processed.*/
1346 /*************************************************************************/
1347 FUNCTION get_flex_value_id(v_flex_value fnd_flex_values.flex_value%TYPE)
1348 return number
1349 IS
1350 v_flex_value_id fnd_flex_values.flex_value_id%TYPE;
1351 l_module_name varchar2(200) := g_module_name || 'get_flax_value_id';
1352 BEGIN
1353 v_statement := 'L';
1354 SELECT flex_value_set_id
1355 INTO v_flex_value_id
1356 FROM fnd_id_flex_segments_vl
1357 WHERE id_flex_num = v_ca_id
1358 AND application_id = 101
1359 AND application_column_name = acct_seg_name
1360 AND id_flex_code='GL#';
1361
1362
1363 RETURN v_flex_value_id;
1364 EXCEPTION
1365 when others then
1366 err_message := 'FV_FUNDS_AVAILABLE_PKG.GET_FLEX_VALUE_ID.L.'||sqlerrm;
1367 fnd_message.set_name('FV','FV_FAI_GENERAL');
1368 fnd_message.set_token('MGS',err_message);
1369 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1370 FV_UTILITY.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module_name);
1371 END IF;
1372 app_exception.raise_exception;
1373
1374 END get_flex_value_id;
1375
1376 /***************************************************************************/
1377 /* This procedure deletes all the rows from the fv_status_funds_final table.*/
1378 /***************************************************************************/
1379 PROCEDURE delete_from_final
1380 IS
1381 l_module_name varchar2(200) := g_module_name || 'delete_from_final';
1382 l_errbuf varchar2(300);
1383 BEGIN
1384 delete from fv_status_funds_final;
1385 commit;
1386 EXCEPTION
1387 WHEN OTHERS THEN
1388 l_errbuf := SQLERRM;
1389 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.final_exception',l_errbuf);
1390 raise;
1391 END delete_from_final;
1392
1393
1394 /*************************************************************************/
1395 /* This procedure calc_funds calculates all the totals for the */
1396 /* different accounts. */
1397 /*************************************************************************/
1398 PROCEDURE calc_funds(
1399 x_acct_ff_low IN VARCHAR2,
1400 x_acct_ff_high IN VARCHAR2,
1401 x_rollup_type IN VARCHAR2,
1402 x_treasury_symbol IN VARCHAR2,
1403 x_balance_seg_name IN VARCHAR2,
1404 x_acct_seg_name IN VARCHAR2,
1405 x_set_of_books_id IN NUMBER,
1406 x_currency_code IN VARCHAR2,
1407 x_period_name IN VARCHAR2,
1408 x_pagebreak_seg1 IN VARCHAR2,
1409 x_pagebreak_seg2 IN VARCHAR2,
1410 x_pagebreak_seg3 IN VARCHAR2
1411 ) IS
1412 l_module_name varchar2(200) := g_module_name || 'calc_funds';
1413
1414
1415 BEGIN
1416 -- delete all the rows from the final table
1417 delete_from_final;
1418 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1419 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'BEGIN');
1420 END IF;
1421 -- reassign incoming variables
1422 acct_ff_low := x_acct_ff_low;
1423 acct_ff_high := x_acct_ff_high;
1424 rollup_type := x_rollup_type;
1425 sob_id := x_set_of_books_id;
1426 curr_code := x_currency_code;
1427 pd_name := x_period_name;
1428 acct_seg_name_num := to_number(substr(x_acct_seg_name,8,2));
1429 bal_seg_name_num := to_number(substr(x_balance_seg_name,8,2));
1430 acct_seg_name := x_acct_seg_name;
1431 bal_seg_name := x_balance_seg_name;
1432 v_treasury_symbol := x_treasury_symbol;
1433 v_pagebreak_seg1 := x_pagebreak_seg1;
1434 v_pagebreak_seg2 := x_pagebreak_seg2;
1435 v_pagebreak_seg3 := x_pagebreak_seg3;
1436 --Added to fix Bug.1575992
1437 /* Bug 1829771 */
1438 /* Added a condition sob and checking for rollup type in If condition */
1439 IF v_treasury_symbol IS NOT NULL and rollup_type='T'
1440 THEN
1441 SELECT treasury_symbol_id
1442 INTO v_treasury_symbol_id
1443 FROM fv_treasury_symbols
1444 WHERE treasury_symbol = v_treasury_symbol
1445 AND set_of_books_id=sob_id;
1446 END IF;
1447 temp_v_treasury_symbol_id := v_treasury_symbol_id ;
1448 v_statement := 'A';
1449 -- find period number
1450 SELECT distinct(period_num)
1451 INTO x_period_num
1452 FROM gl_period_statuses
1453 WHERE period_name = x_period_name
1454 AND ledger_id = sob_id
1455 AND application_id = 101;
1456
1457 v_statement := 'B';
1458 -- find chart of account id
1459 SELECT chart_of_accounts_id
1460 INTO v_ca_id
1461 FROM gl_ledgers_public_v
1462 WHERE ledger_id = sob_id;
1463
1464 v_statement := 'C';
1465 /* Bug No 1829771 */
1466 /* Added the treasury_symbol_id */
1467 FOR c_treasury_fund_value_rec IN c_treasury_fund_values LOOP
1468 -- assign fund and process
1469 v_fund_value := c_treasury_fund_value_rec.fund_value;
1470 v_budget := nvl(c_treasury_fund_value_rec.budget_authority,'-1');
1471 v_transfers_in := nvl(c_treasury_fund_value_rec.transfers_in,'-1');
1472 v_transfers_out := nvl(c_treasury_fund_value_rec.transfers_out,'-1');
1473 v_commitment := nvl(c_treasury_fund_value_rec.unliquid_commitments,'-1');
1474 v_obligation := nvl(c_treasury_fund_value_rec.unliquid_obligations,'-1');
1475 v_expenditure := nvl(c_treasury_fund_value_rec.expended_amount,'-1');
1476 v_treasury_symbol_id:=c_treasury_fund_value_rec.treasury_symbol_id ;
1477 v_statement := 'D';
1478 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1479 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FUND VALUE :' ||V_FUND_VALUE);
1480 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'BALANCING SEGMENT NAME :' ||BAL_SEG_NAME);
1481 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'ACCOUNTING SEG NAME:' || ACCT_SEG_NAME);
1482 END IF;
1483
1484 -- Processing the budget authority account
1485
1486 -- Find flex_value_id
1487 acct_flex_id := get_flex_value_id(v_budget);
1488 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1489 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'BUDGET_ID = '||ACCT_FLEX_ID);
1490 END IF;
1491 --FV_UTILITY.DEBUG_MESG('budget = '||v_budget);
1492
1493 -- sum budget authority accounts (parent and children)
1494 --Modified to fix Bug.1575992
1495 sum_children_budget(v_budget,acct_flex_id,sob_id,v_treasury_symbol,v_fund_value,v_treasury_symbol_id);
1496
1497 -- Processing the transfers-in account
1498
1499 -- Find flex_value_id
1500 acct_flex_id := get_flex_value_id(v_transfers_in);
1501 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1502 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'TRANSFERS_IN = '||V_TRANSFERS_IN);
1503 END IF;
1504 v_acct_type := 'TRANS_IN';
1505
1506 -- sum transfers_in accounts (parent and children)
1507 --Modified to fix Bug.1575992
1508 sum_children_transfers(v_transfers_in,acct_flex_id,v_acct_type,sob_id,v_treasury_symbol,v_fund_value,v_treasury_symbol_id);
1509
1510 -- Processing the transfers-out account
1511
1512 -- Find flex_value_id
1513 acct_flex_id := get_flex_value_id(v_transfers_out);
1514 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1515 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'TRANSFERS_OUT = '||V_TRANSFERS_OUT);
1516 END IF;
1517 v_acct_type := 'TRANS_OUT';
1518
1519 -- sum transfers_out accounts (parent and children)
1520 --Modified to fix Bug.1575992
1521 sum_children_transfers(v_transfers_out,acct_flex_id,v_acct_type,sob_id,v_treasury_symbol,v_fund_value,v_treasury_symbol_id);
1522
1523
1524 -- Processing the commitment account
1525
1526 -- Find flex_value_id
1527 acct_flex_id := get_flex_value_id(v_commitment);
1528 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1529 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'COMMIT = '||V_COMMITMENT);
1530 END IF;
1531 v_acct_type := 'UNLIQ_COMM';
1532
1533 -- sum commitment accounts (parent and children)
1534 --Modified to fix Bug.1575992
1535 sum_children_spending(v_commitment,acct_flex_id,v_acct_type,sob_id,v_treasury_symbol,v_fund_value,v_treasury_symbol_id);
1536
1537
1538 -- Processing the obligation account
1539
1540 -- Find flex_value_id
1541 acct_flex_id := get_flex_value_id(v_obligation);
1542 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1543 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'OBL = '||V_OBLIGATION);
1544 END IF;
1545 v_acct_type := 'UNLIQ_OBLIG';
1546
1547 -- sum obligation accounts (parent and children)
1548 --Modified to fix Bug.1575992
1549 sum_children_spending(v_obligation,acct_flex_id,v_acct_type,sob_id,v_treasury_symbol,v_fund_value,v_treasury_symbol_id);
1550
1551
1552 -- Processing the expenditure account
1553
1554 -- Find flex_value_id
1555 acct_flex_id := get_flex_value_id(v_expenditure);
1556 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1557 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'EXP = '||V_EXPENDITURE);
1558 END IF;
1559 v_acct_type := 'EXPEN_AMT';
1560
1561 -- sum expenditure accounts (parent and children)
1562 --Modified to fix Bug.1575992
1563 sum_children_spending(v_expenditure,acct_flex_id,v_acct_type,sob_id,v_treasury_symbol,v_fund_value,v_treasury_symbol_id);
1564
1565 END LOOP;
1566
1567 -- Calculating the values for total_budgetary, total_actuals and funds_available
1568 --Update statement is modified as part of BE enhancement
1569 UPDATE fv_status_funds_final set
1570 --total_budgetary = (nvl(budget_Auth_total,0) + nvl(transfers_in_total,0)) - nvl(transfers_out_total,0),
1571 total_budgetary = (nvl(budget_Auth_total,0)) ,
1572 total_actuals = nvl(unliquid_comm_total,0) + nvl(unliquid_oblig_total,0) + nvl(expen_amt_total,0);
1573 UPDATE fv_status_funds_final set
1574 funds_available = nvl(total_budgetary,0) - nvl(total_actuals,0);
1575
1576 EXCEPTION
1577 when others then
1578 IF c_treasury_fund_values%ISOPEN THEN
1579 close c_treasury_fund_values;
1580 END IF;
1581 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1582 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'ERROR = '||SQLERRM);
1583 END IF;
1584 err_message := 'FV_FUNDS_AVAILABLE_PKG.CALC_FUNDS.'||v_statement||'.'||sqlerrm;
1585 fnd_message.set_name('FV','FV_FAI_GENERAL');
1586 fnd_message.set_token('MSG',err_message);
1587 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1588 FV_UTILITY.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,l_module_name);
1589 END IF;
1590 -- app_exception.raise_exception;
1591
1592 END calc_funds;
1593 END fv_status_of_funds_pkg;