[Home] [Help]
PACKAGE BODY: APPS.JA_CN_PS_BI_EXPORT_PKG
Source
1 PACKAGE BODY JA_CN_PS_BI_EXPORT_PKG AS
2 --$Header: JACNPBIB.pls 120.8 2010/12/03 04:52:20 chuansha noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNPBIB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| To export Budget Income information under current legal |
13 --| entity. |
14 --| |
15 --| PROCEDURE LIST |
16 --| PROCEDURE Add_Budget_Income |
17 --| FUNCTION Get_Budget_Income_Amount |
18 --| FUNCTION Get_Actual_Income_Amount |
19 --| |
20 --| |
21 --| |
22 --| HISTORY |
23 --| 23-Aug-2010 Richard Qi Created |
24 --| 15-Sep-2010 Richard Qi FIXED Bug# 10110709 |
25 --| 03-Dec-2010 ChuanLing Shao FIXED Bug# 10316565 |
26 --+======================================================================*/
27 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_PS_BI_EXPORT_PKG';
28
29 --==========================================================================
30 -- PROCEDURE NAME:
31 --
32 -- Get_Budget_Income_Amount Private
33 --
34 -- DESCRIPTION:
35 --
36 -- Function to get budget income amount:
37 -- a) Get current year budget amount if accounting year is not null,
38 -- period from and period to are null
39 -- b) Get accumulated increasing/decreasing amount if period from is null,
40 -- accounting year and period to are not null
41 -- c) Get current period accumulated increasing/decreasing amount
42 -- if accounting year is null, period from and period to are not null
43 --
44 -- PARAMETERS:
45 -- In: pn_ledger_id NUMBER Ledger Id
46 -- lv_income_cat_seg_col VARCHAR2 Income Category Segment Column
47 -- lv_income_cat_seg_code VARCHAR2 Income Category Segment Code
48 -- lv_income_proj_seg_col VARCHAR2 Income Project Segment Column
49 -- lv_income_proj_seg_code VARCHAR2 Income Project Segment Code
50 -- lv_budget_type_seg_col VARCHAR2 Budget Type Segment Column
51 -- lv_budget_type_value VARCHAR2 Budget Type Value
52 -- lv_accounting_year VARCHAR2 Accounting Year
53 -- lv_period_from VARCHAR2 Period From
54 -- lv_period_to VARCHAR2 Period To
55 --
56 -- DESIGN REFERENCES:
57 -- TDD_1213_FIN_GL_P_CNAOV2_GL.doc
58 -- See paragraph #15
59 --
60 -- CHANGE HISTORY:
61 -- 13-Aug-2010 Richard Qi created
62 --==========================================================================
63
64 FUNCTION Get_Budget_Income_Amount(pn_ledger_id IN NUMBER -- Ledger Id
65 ,lv_income_cat_seg_col IN VARCHAR2 -- Income_Category_Code
66 ,lv_income_cat_seg_code IN VARCHAR2 -- Income_Category_Code,
67 ,lv_income_proj_seg_col IN VARCHAR2 -- Income_Project_Code
68 ,lv_income_proj_seg_code IN VARCHAR2 -- Income_Project_Code,
69 ,lv_budget_type_seg_col IN VARCHAR2
70 ,lv_budget_type_value IN VARCHAR2
71 ,lv_accounting_year IN VARCHAR2
72 ,lv_period_from IN VARCHAR2
73 ,lv_period_to IN VARCHAR2
74 ) RETURN NUMBER IS
75 /* define variables */
76 lv_procedure_name VARCHAR2(40) := 'Get_Budget_Income_Amount';
77 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
78 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
79 --ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
80 --lv_error_msg VARCHAR2(2000) := NULL;
81
82 ln_amount NUMBER;
83 lv_sql_text VARCHAR2(2000);
84
85 BEGIN
86 --logging for debug
87 IF (ln_proc_level >= ln_dbg_level)
88 THEN
89 FND_LOG.STRING(ln_proc_level,
90 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
91 '.begin',
92 'Enter procedure');
93 -- logging the parameters
94 FND_LOG.STRING(ln_proc_level,
95 lv_procedure_name ||
96 '.parameters',
97 'pn_ledger_id=' || pn_ledger_id || ',' ||
98 'lv_income_cat_seg_col =' || lv_income_cat_seg_col || ',' ||
99 'lv_income_cat_seg_code=' || lv_income_cat_seg_code || ',' ||
100 'lv_income_proj_seg_col=' || lv_income_proj_seg_col || ',' ||
101 'lv_income_proj_seg_code=' || lv_income_proj_seg_code || ',' ||
102 'lv_budget_type_seg_col=' || lv_budget_type_seg_col || ',' ||
103 'lv_budget_type_value=' || lv_budget_type_value || ',' ||
104 'lv_accounting_year=' || lv_accounting_year || ',' ||
105 'lv_period_from=' || lv_period_from || ',' ||
106 'lv_period_to=' || lv_period_to);
107 END IF; --ln_proc_level>=ln_dbg_level
108
109 ln_amount := 0;
110
111 lv_sql_text := 'SELECT SUM(NVL(Jel.Accounted_Cr,0) - NVL(Jel.Accounted_Dr,0))';
112 lv_sql_text := lv_sql_text || ' FROM gl_je_lines jel,';
113 lv_sql_text := lv_sql_text || ' gl_je_headers jeh,';
114 lv_sql_text := lv_sql_text || ' gl_code_combinations gcc,';
115 lv_sql_text := lv_sql_text || ' Gl_Periods Gp,';
116 lv_sql_text := lv_sql_text || ' Gl_Ledgers Gle';
117 lv_sql_text := lv_sql_text || ' WHERE jeh.ledger_id = ' || pn_ledger_id; --parameter: pn_ledger_id
118 lv_sql_text := lv_sql_text || ' AND jel.Code_Combination_ID = gcc.Code_Combination_ID';
119 --1) Collecting budget journals with posted status
120 lv_sql_text := lv_sql_text || ' AND jel.je_header_id = jeh.je_header_id';
121 lv_sql_text := lv_sql_text || ' AND jeh.actual_flag = ''B''';
122 lv_sql_text := lv_sql_text || ' AND jeh.status = ''P''';
123 --2) Budget related condition
124
125 --variable: lv_income_category_segment_column, Get_Attribute_Column(101,'CICA')
126 --variable: lv_income_category_code, retrived from Cursor
127 lv_sql_text := lv_sql_text || ' AND gcc.' || lv_income_cat_seg_col || ' = ''' || lv_income_cat_seg_code || '''';
128
129 --variable: lv_income_project_segment_column, Get_Attribute_Column(101,'CIPR')
130 --variable: lv_income_project_code, retrived from Cursor
131 lv_sql_text := lv_sql_text || ' AND gcc.' || lv_income_proj_seg_col || ' = ''' || lv_income_proj_seg_code || '''';
132
133 --variable: lv_budget_type_segment_column, Get_Attribute_Column(101,'CBTY')
134 --variable: lv_budget_type_value, Get_Budget_Type_Value('CYBA'/'ICPA'/'DCPA')
135 IF (lv_budget_type_value IS NULL)
136 THEN
137 lv_sql_text := lv_sql_text || ' AND gcc.' || lv_budget_type_seg_col || ' IS NULL ';
138 ELSE
139 lv_sql_text := lv_sql_text || ' AND gcc.' || lv_budget_type_seg_col || ' = ''' || lv_budget_type_value || '''';
140 END IF;
141 --3) Period condition
142 lv_sql_text := lv_sql_text || ' AND Gle.period_set_name = Gp.period_set_name';
143 lv_sql_text := lv_sql_text || ' AND Gle.accounted_period_type = Gp.period_type';
144 lv_sql_text := lv_sql_text || ' AND Gle.Ledger_Id = jeh.ledger_id';
145 lv_sql_text := lv_sql_text || ' AND Gp.Period_Name = Jeh.Period_Name';
146 lv_sql_text := lv_sql_text || ' AND jeh.Default_Effective_Date BETWEEN';
147 lv_sql_text := lv_sql_text || ' (SELECT MIN(Gp1.Start_Date)';
148 lv_sql_text := lv_sql_text || ' FROM Gl_Periods Gp1';
149 lv_sql_text := lv_sql_text || ' WHERE Gle.period_set_name = Gp1.period_set_name';
150 lv_sql_text := lv_sql_text || ' AND Gle.accounted_period_type = Gp1.period_type';
151 IF lv_accounting_year IS NOT NULL THEN
152 lv_sql_text := lv_sql_text || ' AND Gp1.period_year = ''' || lv_accounting_year || ''''; -- variable: lv_accounting_year
153 ELSE
154 lv_sql_text := lv_sql_text || ' AND Gp1.period_name = ''' || lv_period_from || ''''; --variable: lv_period_from
155 END IF;
156 lv_sql_text := lv_sql_text || ' )';
157 lv_sql_text := lv_sql_text || ' AND (SELECT MAX(Gp2.End_Date)';
158 lv_sql_text := lv_sql_text || ' FROM Gl_Periods Gp2';
159 lv_sql_text := lv_sql_text || ' WHERE Gle.period_set_name = Gp2.period_set_name';
160 lv_sql_text := lv_sql_text || ' AND Gle.accounted_period_type = Gp2.period_type';
161 IF lv_period_to IS NULL THEN
162 lv_sql_text := lv_sql_text || ' AND Gp2.period_year = ''' || lv_accounting_year || ''''; -- variable: lv_accounting_year
163 ELSE
164 lv_sql_text := lv_sql_text || ' AND Gp2.period_name = ''' || lv_period_to || ''''; --variable: lv_period_to
165 END IF;
166 lv_sql_text := lv_sql_text || ' )';
167
168 --FND_FILE.put_line(FND_FILE.log, 'Get_Budget_Income_Amount' ||
169 -- '.lv_sql_text:' || lv_sql_text);
170
171 EXECUTE IMMEDIATE lv_sql_text INTO ln_amount;
172
173 --logging for debug
174 IF (ln_proc_level >= ln_dbg_level)
175 THEN
176 FND_LOG.STRING(ln_proc_level,
177 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
178 'Exit procedure');
179 END IF; -- (ln_proc_level>=ln_dbg_level)
180
181 RETURN ln_amount;
182
183 EXCEPTION
184 WHEN OTHERS THEN
185 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
186 THEN
187 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
188 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
189 '.Other_Exception ',
190 SQLCODE || SQLERRM);
191 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
192 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
193
194 END Get_Budget_Income_Amount;
195
196 --==========================================================================
197 -- PROCEDURE NAME:
198 --
199 -- Get_Actual_Income_Amount Private
200 --
201 -- DESCRIPTION:
202 --
203 -- Function to get actual income amount:
204 -- a) Get accumulated actual income amount if period from is null,
205 -- accounting year and period to are not null
206 -- b) Get current period accumulated actual income amount
207 -- if accounting year is null, period from and period from are not null
208 --
209 -- PARAMETERS:
210 -- In: pn_ledger_id NUMBER Ledger Id
211 -- lv_income_cat_seg_col VARCHAR2 Income Category Segment Column
212 -- lv_income_cat_seg_code VARCHAR2 Income Category Segment Code
213 -- lv_income_proj_seg_col VARCHAR2 Income Project Segment Column
214 -- lv_income_proj_seg_code VARCHAR2 Income Project Segment Code
215 -- lv_accounting_year VARCHAR2 Accounting Year
216 -- lv_period_from VARCHAR2 Period From
217 -- lv_period_to VARCHAR2 Period To
218 --
219 -- DESIGN REFERENCES:
220 -- TDD_1213_FIN_GL_P_CNAOV2_GL.doc
221 -- See paragraph #15
222 --
223 -- CHANGE HISTORY:
224 -- 13-Aug-2010 Richard Qi created
225 --==========================================================================
226
227 FUNCTION Get_Actual_Income_Amount(pn_ledger_id IN NUMBER -- Ledger Id
228 ,lv_income_cat_seg_col IN VARCHAR2 -- Income_Category_Code
229 ,lv_income_cat_seg_code IN VARCHAR2 -- Income_Category_Code,
230 ,lv_income_proj_seg_col IN VARCHAR2 -- Income_Project_Code
231 ,lv_income_proj_seg_code IN VARCHAR2 -- Income_Project_Code,
232 ,lv_accounting_year IN VARCHAR2
233 ,lv_period_from IN VARCHAR2
234 ,lv_period_to IN VARCHAR2
235 ) RETURN NUMBER IS
236 /* define variables */
237 lv_procedure_name VARCHAR2(40) := 'Get_Actual_Income_Amount';
238 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
239 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
240 --ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
241 --lv_error_msg VARCHAR2(2000) := NULL;
242
243 ln_amount NUMBER;
244 lv_sql_text VARCHAR2(2000);
245
246 BEGIN
247 --logging for debug
248 IF (ln_proc_level >= ln_dbg_level)
249 THEN
250 FND_LOG.STRING(ln_proc_level,
251 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
252 '.begin',
253 'Enter procedure');
254 -- logging the parameters
255 FND_LOG.STRING(ln_proc_level,
256 lv_procedure_name ||
257 '.parameters',
258 'pn_ledger_id=' || pn_ledger_id || ',' ||
259 'lv_income_cat_seg_col =' || lv_income_cat_seg_col || ',' ||
260 'lv_income_cat_seg_code=' || lv_income_cat_seg_code || ',' ||
261 'lv_income_proj_seg_col=' || lv_income_proj_seg_col || ',' ||
262 'lv_income_proj_seg_code=' || lv_income_proj_seg_code || ',' ||
263 'lv_accounting_year=' || lv_accounting_year || ',' ||
264 'lv_period_from=' || lv_period_from || ',' ||
265 'lv_period_to=' || lv_period_to);
266 END IF; --ln_proc_level>=ln_dbg_level
267
268 ln_amount := 0;
269
270 lv_sql_text := 'SELECT SUM(NVL(Jel.Accounted_Cr,0) - NVL(Jel.Accounted_Dr,0))';
271 lv_sql_text := lv_sql_text || ' FROM gl_je_lines jel,';
272 lv_sql_text := lv_sql_text || ' gl_je_headers jeh,';
273 lv_sql_text := lv_sql_text || ' gl_code_combinations gcc,';
274 lv_sql_text := lv_sql_text || ' Gl_Periods Gp,';
275 lv_sql_text := lv_sql_text || ' Gl_Ledgers Gle';
276 lv_sql_text := lv_sql_text || ' WHERE jeh.ledger_id = ' || pn_ledger_id; --parameter: pn_ledger_id
277 lv_sql_text := lv_sql_text || ' AND jel.Code_Combination_ID = gcc.Code_Combination_ID';
278 --1) Collecting non-budget journals with posted status
279
280 lv_sql_text := lv_sql_text || ' AND jel.je_header_id = jeh.je_header_id';
281 lv_sql_text := lv_sql_text || ' AND jeh.actual_flag <> ''B''';
282 lv_sql_text := lv_sql_text || ' AND jeh.status = ''P''';
283 --2) Budget related condition
284
285 --variable: lv_income_category_segment_column, Get_Attribute_Column(101,'CICA')
286 --variable: lv_income_category_code, retrived from Cursor
287 lv_sql_text := lv_sql_text || ' AND gcc.' || lv_income_cat_seg_col || ' = ''' || lv_income_cat_seg_code || '''';
288
289 --variable: lv_income_project_segment_column, Get_Attribute_Column(101,'CIPR')
290 --variable: lv_income_project_code, retrived from Cursor
294
291 lv_sql_text := lv_sql_text || ' AND gcc.' || lv_income_proj_seg_col || ' = ''' || lv_income_proj_seg_code || '''';
292
293 --3) Period condition
295 lv_sql_text := lv_sql_text || ' AND Gle.period_set_name = Gp.period_set_name';
296 lv_sql_text := lv_sql_text || ' AND Gle.accounted_period_type = Gp.period_type';
297 lv_sql_text := lv_sql_text || ' AND Gle.Ledger_Id = jeh.ledger_id';
298 lv_sql_text := lv_sql_text || ' AND Gp.Period_Name = Jeh.Period_Name';
299 lv_sql_text := lv_sql_text || ' AND jeh.Default_Effective_Date BETWEEN';
300 lv_sql_text := lv_sql_text || ' (SELECT MIN(Gp1.Start_Date)';
301 lv_sql_text := lv_sql_text || ' FROM Gl_Periods Gp1';
302 lv_sql_text := lv_sql_text || ' WHERE Gle.period_set_name = Gp1.period_set_name';
303 lv_sql_text := lv_sql_text || ' AND Gle.accounted_period_type = Gp1.period_type';
304 IF lv_accounting_year IS NOT NULL THEN
305 lv_sql_text := lv_sql_text || ' AND Gp1.period_year = ''' || lv_accounting_year || ''''; -- variable: lv_accounting_year
306 ELSE
307 lv_sql_text := lv_sql_text || ' AND Gp1.period_name = ''' || lv_period_from || ''''; --variable: lv_period_from
308 END IF;
309 lv_sql_text := lv_sql_text || ' )';
310 lv_sql_text := lv_sql_text || ' AND (SELECT Gp2.End_Date';
311 lv_sql_text := lv_sql_text || ' FROM Gl_Periods Gp2';
312 lv_sql_text := lv_sql_text || ' WHERE Gle.period_set_name = Gp2.period_set_name';
313 lv_sql_text := lv_sql_text || ' AND Gle.accounted_period_type = Gp2.period_type';
314 lv_sql_text := lv_sql_text || ' AND Gp2.period_name = ''' || lv_period_to || ''''; --variable: lv_period_to
315 lv_sql_text := lv_sql_text || ' )';
316
317 --FND_FILE.put_line(FND_FILE.log, 'Get_Actual_Income_Amount' ||
318 -- '.lv_sql_text:' || lv_sql_text);
319
320 EXECUTE IMMEDIATE lv_sql_text INTO ln_amount;
321
322 --logging for debug
323 IF (ln_proc_level >= ln_dbg_level)
324 THEN
325 FND_LOG.STRING(ln_proc_level,
326 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
327 'Exit procedure');
328 END IF; -- (ln_proc_level>=ln_dbg_level)
329
330 RETURN ln_amount;
331
332 EXCEPTION
333 WHEN OTHERS THEN
334 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
335 THEN
336 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
337 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
338 '.Other_Exception ',
339 SQLCODE || SQLERRM);
340 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
341 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
342
343 END Get_Actual_Income_Amount;
344
345 --==========================================================================
346 -- PROCEDURE NAME:
347 --
348 -- Add_Budget_Income Public
349 --
350 -- DESCRIPTION:
351 --
352 -- This procedure is to export Budget Income records under current
353 -- legal entity.
354 --
355 -- PARAMETERS:
356 -- In: pn_legal_entity_id NUMBER Legal Entity Id
357 -- pn_ledger_id NUMBER Ledger Id
358 -- pn_coa_id NUMBER Chart of account Id
359 -- pv_accounting_year VARCHAR2 Accounting Year
360 -- pv_period_from VARCHAR2 Period From
361 -- pv_period_to VARCHAR2 Period To
362 --
363 -- DESIGN REFERENCES:
364 -- TDD_1213_FIN_GL_P_CNAOV2_GL.doc
365 -- See paragraph #15
366 --
367 -- CHANGE HISTORY:
368 -- 23-Aug-2010 Richard Qi created
369 -- 15-Sep-2010 Richard Qi FIXED Bug# 10110709
370 --==========================================================================
371
372 PROCEDURE Add_Budget_Income(pn_legal_entity_id IN NUMBER -- Legal Entity Id
373 ,pn_ledger_id IN NUMBER -- Ledger Id
374 ,Pn_coa_id IN NUMBER -- Chart of account Id
375 ,pv_accounting_year IN VARCHAR2 -- Accounting Year
376 ,pv_period_from IN VARCHAR2 -- Period From
377 ,pv_period_to IN VARCHAR2 -- Period To
378 ) IS
379
380 lv_procedure_name VARCHAR2(40) := 'Add_Budget_Income';
381 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
382 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
383 ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
384 --lv_error_msg VARCHAR2(2000) := NULL;
385 ln_rec_count NUMBER := 0;
386
387 lv_accounting_period_num NUMBER; -- Accounting_Period_Num,
388 lv_accounting_period VARCHAR2(15); -- Accounting_Period,
389 lv_income_cat_seg_col VARCHAR2(30); -- Income_Category_Code, Get_Attribute_Column(101,'CICA')
390 lv_income_proj_seg_col VARCHAR2(30); -- Income_Project_Code, Get_Attribute_Column(101,'CIPR')
391 lv_budget_type_seg_col VARCHAR2(30);
392 lv_income_cat_seg_code VARCHAR2(25); -- Income_Category_Code,
393 lv_income_proj_seg_code VARCHAR2(25); -- Income_Project_Code,
394 lv_budget_type_value VARCHAR2(25);
395
396 lv_bi_cursor_sql VARCHAR2(2000);
397 lv_pi_cursor_sql VARCHAR2(3000);
398 TYPE ref_cursor IS REF CURSOR;
399 lc_budget_information ref_cursor;
400 lc_budget_period_information ref_cursor;
401
402 BEGIN
403 --logging for debug
404 IF (ln_proc_level >= ln_dbg_level)
405 THEN
406 FND_LOG.STRING(ln_proc_level,
407 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
408 '.begin',
409 'Enter procedure');
413 '.parameters',
410 -- logging the parameters
411 FND_LOG.STRING(ln_proc_level,
412 lv_procedure_name ||
414 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
415 'pn_ledger_id=' || pn_ledger_id || ',' ||
416 'pv_accounting_year=' || pv_accounting_year);
417 END IF; --ln_proc_level>=ln_dbg_level
418 -- FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
419 -- '.parameters:' ||
420 -- 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
421 -- 'pn_ledger_id=' || pn_ledger_id || ',' ||
422 -- 'pv_accounting_year=' || pv_accounting_year);
423
424 lv_income_cat_seg_col := JA_CN_UTILITY.Get_Attribute_Column(pv_flexfield_code => 'CICA'
425 ,pn_application_id => 101
426 ,pn_coa_id => pn_coa_id
427 );
428 lv_income_proj_seg_col := JA_CN_UTILITY.Get_Attribute_Column(pv_flexfield_code => 'CIPR'
429 ,pn_application_id => 101
430 ,pn_coa_id => pn_coa_id
431 );
432 lv_budget_type_seg_col := JA_CN_UTILITY.Get_Attribute_Column(pv_flexfield_code => 'CBTY'
433 ,pn_application_id => 101
434 ,pn_coa_id => pn_coa_id
435 );
436 lv_budget_type_value := JA_CN_UTILITY.Get_Budget_Type_Value(pn_coa_id => pn_coa_id
437 ,pv_flexfield_code => 'LYBA'
438 );
439
440 --fixed bug 10316565 by ChuanLing Shao
441 lv_pi_cursor_sql := ' SELECT DISTINCT Gp.Period_Num Accounting_Period_Num, Gp.Period_Name Accounting_Period ';
442 lv_pi_cursor_sql := lv_pi_cursor_sql || ' FROM Gl_Periods Gp , ';
443 lv_pi_cursor_sql := lv_pi_cursor_sql || ' Gl_Ledgers Gle ';
444 lv_pi_cursor_sql := lv_pi_cursor_sql || ' WHERE ' ;
445 lv_pi_cursor_sql := lv_pi_cursor_sql || ' Gle.period_set_name = Gp.period_set_name ';
446 lv_pi_cursor_sql := lv_pi_cursor_sql || ' AND Gle.accounted_period_type = Gp.period_type ';
447 lv_pi_cursor_sql := lv_pi_cursor_sql || ' AND Gle.Ledger_Id = '''||pn_ledger_id||'''' ; --pn_ledger_id
448 lv_pi_cursor_sql := lv_pi_cursor_sql || ' AND ';
449 lv_pi_cursor_sql := lv_pi_cursor_sql || ' Gp.Start_date BETWEEN ' ;
450 lv_pi_cursor_sql := lv_pi_cursor_sql || ' ( ';
451 lv_pi_cursor_sql := lv_pi_cursor_sql || ' SELECT MIN(Gp1.Start_Date) ';
452 lv_pi_cursor_sql := lv_pi_cursor_sql || ' FROM Gl_Periods Gp1 ';
453 lv_pi_cursor_sql := lv_pi_cursor_sql || ' WHERE Gle.period_set_name = Gp1.period_set_name ';
454 lv_pi_cursor_sql := lv_pi_cursor_sql || ' AND Gle.accounted_period_type = Gp1.period_type ';
455 lv_pi_cursor_sql := lv_pi_cursor_sql || ' AND Gp1.period_name = '''||pv_period_from||'''';
456 lv_pi_cursor_sql := lv_pi_cursor_sql || ' ) ';
457 lv_pi_cursor_sql := lv_pi_cursor_sql || ' AND ';
458 lv_pi_cursor_sql := lv_pi_cursor_sql || ' ( ';
459 lv_pi_cursor_sql := lv_pi_cursor_sql || ' SELECT MAX(Gp2.End_Date) ';
460 lv_pi_cursor_sql := lv_pi_cursor_sql || ' FROM Gl_Periods Gp2 ';
461 lv_pi_cursor_sql := lv_pi_cursor_sql || ' WHERE Gle.period_set_name = Gp2.period_set_name ';
462 lv_pi_cursor_sql := lv_pi_cursor_sql || ' AND Gle.accounted_period_type = Gp2.period_type ';
463 lv_pi_cursor_sql := lv_pi_cursor_sql || ' AND Gp2.period_name = '''||pv_period_to||'''';
464 lv_pi_cursor_sql := lv_pi_cursor_sql || ' ) ';
465 lv_pi_cursor_sql := lv_pi_cursor_sql || ' order by Gp.Period_Num ';
466
467 lv_bi_cursor_sql := 'SELECT DISTINCT *'; ----fixed bug 10316565 by ChuanLing Shao
468 --lv_bi_cursor_sql := lv_bi_cursor_sql || ' FROM (SELECT Gp.Period_Num Accounting_Period_Num,';
469 --lv_bi_cursor_sql := lv_bi_cursor_sql || ' Gp.Period_Name Accounting_Period,';
470 lv_bi_cursor_sql := lv_bi_cursor_sql || ' FROM (SELECT ';
471 lv_bi_cursor_sql := lv_bi_cursor_sql || ' gcc.'|| lv_income_cat_seg_col ||' Income_Category_Code,';
472 lv_bi_cursor_sql := lv_bi_cursor_sql || ' gcc.'|| lv_income_proj_seg_col ||' Income_Project_Code';
473 lv_bi_cursor_sql := lv_bi_cursor_sql || ' FROM gl_je_lines jel,';
474 lv_bi_cursor_sql := lv_bi_cursor_sql || ' gl_je_headers jeh,';
475 lv_bi_cursor_sql := lv_bi_cursor_sql || ' gl_code_combinations gcc,';
476 lv_bi_cursor_sql := lv_bi_cursor_sql || ' Gl_Periods Gp,';
477 lv_bi_cursor_sql := lv_bi_cursor_sql || ' Gl_Ledgers Gle';
478 lv_bi_cursor_sql := lv_bi_cursor_sql || ' WHERE jeh.ledger_id = ' || pn_ledger_id; --parameter: pn_ledger_id
479 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND jel.Code_Combination_ID = gcc.Code_Combination_ID';
480 -- added to fix bug# 10110709 --BEGIN--
481 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND (gcc.'|| lv_income_cat_seg_col ||' IS NOT NULL';
482 lv_bi_cursor_sql := lv_bi_cursor_sql || ' OR gcc.'|| lv_income_proj_seg_col ||' IS NOT NULL)';
483 -- added to fix bug# 10110709 --END--
484 --1) Collecting budget journals with posted status
485 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND jel.je_header_id = jeh.je_header_id';
486 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND jeh.actual_flag = ''B''';
487 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND jeh.status = ''P''';
488 IF (lv_budget_type_value IS NULL)
489 THEN
490 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND gcc.'|| lv_budget_type_seg_col ||' IS NOT NULL ';
491 ELSE
492 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND gcc.'|| lv_budget_type_seg_col ||' <> ''' || lv_budget_type_value || '''';
493 END IF;
497 lv_bi_cursor_sql := lv_bi_cursor_sql || ' WHERE JA_CN_UTILITY.get_balancing_segment(gcc.CODE_COMBINATION_ID) = llbg.Bal_Seg_Value';
494 --2) BSV condition
495 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND EXISTS (SELECT llbg.Bal_Seg_Value ';
496 lv_bi_cursor_sql := lv_bi_cursor_sql || ' FROM ja_cn_ledger_le_bsv_gt llbg';
498 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND llbg.Ledger_Id = ' || pn_ledger_id; --parameter: pn_ledger_id
499 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND llbg.Legal_Entity_Id = ' || pn_legal_entity_id || ')'; --parameter: pn_legal_entity_id
500 --4) Period condition
501 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND Gle.period_set_name = Gp.period_set_name';
502 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND Gle.accounted_period_type = Gp.period_type';
503 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND Gle.Ledger_Id = jeh.ledger_id';
504 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND Gp.Period_Name = Jeh.Period_Name';
505 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND jeh.Default_Effective_Date BETWEEN';
506 lv_bi_cursor_sql := lv_bi_cursor_sql || ' (SELECT MIN(Gp1.Start_Date)';
507 lv_bi_cursor_sql := lv_bi_cursor_sql || ' FROM Gl_Periods Gp1';
508 lv_bi_cursor_sql := lv_bi_cursor_sql || ' WHERE Gle.period_set_name = Gp1.period_set_name';
509 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND Gle.accounted_period_type = Gp1.period_type';
510 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND Gp1.period_year = '''||pv_accounting_year||''')'; -- variable: pv_accounting_year
511 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND (SELECT MAX(Gp2.End_Date)';
512 lv_bi_cursor_sql := lv_bi_cursor_sql || ' FROM Gl_Periods Gp2';
513 lv_bi_cursor_sql := lv_bi_cursor_sql || ' WHERE Gle.period_set_name = Gp2.period_set_name';
514 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND Gle.accounted_period_type = Gp2.period_type';
515 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND Gp2.period_year = '''||pv_accounting_year||''')'; --parameter: pv_accounting_year
516 lv_bi_cursor_sql := lv_bi_cursor_sql || ' )';
517 lv_bi_cursor_sql := lv_bi_cursor_sql || ' ORDER BY ';
518 --lv_bi_cursor_sql := lv_bi_cursor_sql || ' ORDER BY Accounting_Period_Num';
519 lv_bi_cursor_sql := lv_bi_cursor_sql || ' Income_Category_Code';
520 lv_bi_cursor_sql := lv_bi_cursor_sql || ' ,Income_Project_Code';
521
522 /* --fixed bug 10316565 by ChuanLing Shao
523 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND Gle.period_set_name = Gp.period_set_name';
524 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND Gle.accounted_period_type = Gp.period_type';
525 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND Gle.Ledger_Id = jeh.ledger_id';
526 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND Gp.Period_Name = Jeh.Period_Name';
527 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND jeh.Default_Effective_Date BETWEEN';
528 lv_bi_cursor_sql := lv_bi_cursor_sql || ' (SELECT Gp1.Start_Date';
529 lv_bi_cursor_sql := lv_bi_cursor_sql || ' FROM Gl_Periods Gp1';
530 lv_bi_cursor_sql := lv_bi_cursor_sql || ' WHERE Gle.period_set_name = Gp1.period_set_name';
531 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND Gle.accounted_period_type = Gp1.period_type';
532 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND Gp1.period_name = ''' || pv_period_from || ''')'; --parameter: pv_period_from
533 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND (SELECT Gp2.End_Date';
534 lv_bi_cursor_sql := lv_bi_cursor_sql || ' FROM Gl_Periods Gp2';
535 lv_bi_cursor_sql := lv_bi_cursor_sql || ' WHERE Gle.period_set_name = Gp2.period_set_name';
536 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND Gle.accounted_period_type = Gp2.period_type';
537 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND Gp2.period_name = ''' || pv_period_to || ''')'; --parameter: pv_period_toe
538 lv_bi_cursor_sql := lv_bi_cursor_sql || ' )';
539 lv_bi_cursor_sql := lv_bi_cursor_sql || ' ORDER BY Accounting_Period_Num';
540 lv_bi_cursor_sql := lv_bi_cursor_sql || ' ,Income_Category_Code';
541 lv_bi_cursor_sql := lv_bi_cursor_sql || ' ,Income_Project_Code';
542 */
543 --FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
544 --'.lv_bi_cursor_sql:' || lv_bi_cursor_sql);
545
546 --fixed bug 10316565 by ChuanLing Shao
547
548 OPEN lc_budget_period_information FOR lv_pi_cursor_sql;
549 LOOP
550 FETCH lc_budget_period_information
551 INTO lv_accounting_period_num,lv_accounting_period;
552 EXIT WHEN lc_budget_period_information%NOTFOUND;
553
554 -----------------------------------------------------------------------------------------------------------
555
556 OPEN lc_budget_information FOR lv_bi_cursor_sql;
557 LOOP
558 FETCH lc_budget_information ----fixed bug 10316565 by ChuanLing Shao
559 --INTO lv_accounting_period_num,lv_accounting_period,lv_income_cat_seg_code,lv_income_proj_seg_code;
560 INTO lv_income_cat_seg_code,lv_income_proj_seg_code;
561 EXIT WHEN lc_budget_information%NOTFOUND;
562
563 Ja_Cn_Utility.Add_Sub_Root_Node('BUDGET_INCOME'
564 ,Ja_Cn_Utility.GV_TAG_TYPE_START
565 );
566 Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_YEAR'
567 ,pv_accounting_year
568 );
569 Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_PERIOD_NUMBER'
570 ,lv_accounting_period_num
571 );
572 Ja_Cn_Utility.Add_Child_Node('INCOME_CATEGORY_CODE'
573 ,lv_income_cat_seg_code
577 ,pv_flex_value => lv_income_cat_seg_code
574 );
575 Ja_Cn_Utility.Add_Child_Node('INCOME_CATEGORY_NAME'
576 ,JA_CN_UTILITY.Get_Segment_Value_Desc(pv_flexfield_code => 'CICA'
578 ,pn_coa_id => pn_coa_id)
579 );
580 Ja_Cn_Utility.Add_Child_Node('INCOME_PROJECT_CODE'
581 ,lv_income_proj_seg_code
582 );
583 Ja_Cn_Utility.Add_Child_Node('INCOME_PROJECT_NAME'
584 ,JA_CN_UTILITY.Get_Segment_Value_Desc(pv_flexfield_code => 'CIPR'
585 ,pv_flex_value => lv_income_proj_seg_code
586 ,pn_coa_id => pn_coa_id)
587 );
588 Ja_Cn_Utility.Add_Child_Node('CURRENT_YEAR_BUDGET_AMOUNT'
589 ,Get_Budget_Income_Amount(pn_ledger_id
590 ,lv_income_cat_seg_col
591 ,lv_income_cat_seg_code
592 ,lv_income_proj_seg_col
593 ,lv_income_proj_seg_code
594 ,lv_budget_type_seg_col
595 ,JA_CN_UTILITY.Get_Budget_Type_Value(pn_coa_id => pn_coa_id
596 ,pv_flexfield_code => 'CYBA')
597 ,pv_accounting_year
598 ,NULL
599 ,NULL
600 )
601 ,Ja_Cn_Utility.GV_TYPE_NUMBER
602 ,Ja_Cn_Utility.GV_REQUIRED_YES
603 );
604 Ja_Cn_Utility.Add_Child_Node('CURRENT_PERIOD_INC_AMOUNT'
605 ,Get_Budget_Income_Amount(pn_ledger_id
606 ,lv_income_cat_seg_col
607 ,lv_income_cat_seg_code
608 ,lv_income_proj_seg_col
609 ,lv_income_proj_seg_code
610 ,lv_budget_type_seg_col
611 ,JA_CN_UTILITY.Get_Budget_Type_Value(pn_coa_id => pn_coa_id
612 ,pv_flexfield_code => 'ICPA')
613 ,NULL
614 ,lv_accounting_period
615 ,lv_accounting_period
616 )
617 ,Ja_Cn_Utility.GV_TYPE_NUMBER
618 ,Ja_Cn_Utility.GV_REQUIRED_YES
619 );
620 Ja_Cn_Utility.Add_Child_Node('CURRENT_PERIOD_DEC_AMOUNT'
621 ,Get_Budget_Income_Amount(pn_ledger_id
622 ,lv_income_cat_seg_col
623 ,lv_income_cat_seg_code
624 ,lv_income_proj_seg_col
625 ,lv_income_proj_seg_code
626 ,lv_budget_type_seg_col
627 ,JA_CN_UTILITY.Get_Budget_Type_Value(pn_coa_id => pn_coa_id
628 ,pv_flexfield_code => 'DCPA')
629 ,NULL
630 ,lv_accounting_period
631 ,lv_accounting_period
632 )
633 ,Ja_Cn_Utility.GV_TYPE_NUMBER
634 ,Ja_Cn_Utility.GV_REQUIRED_YES
635 );
636 Ja_Cn_Utility.Add_Child_Node('ACCUMULATED_INCREASING_AMOUNT'
637 ,Get_Budget_Income_Amount(pn_ledger_id
638 ,lv_income_cat_seg_col
639 ,lv_income_cat_seg_code
640 ,lv_income_proj_seg_col
641 ,lv_income_proj_seg_code
642 ,lv_budget_type_seg_col
643 ,JA_CN_UTILITY.Get_Budget_Type_Value(pn_coa_id => pn_coa_id
644 ,pv_flexfield_code => 'ICPA')
645 ,pv_accounting_year
646 ,NULL
647 ,lv_accounting_period
648 )
649 ,Ja_Cn_Utility.GV_TYPE_NUMBER
650 ,Ja_Cn_Utility.GV_REQUIRED_YES
654 ,lv_income_cat_seg_col
651 );
652 Ja_Cn_Utility.Add_Child_Node('ACCUMULATED_DECREASING_AMOUNT'
653 ,Get_Budget_Income_Amount(pn_ledger_id
655 ,lv_income_cat_seg_code
656 ,lv_income_proj_seg_col
657 ,lv_income_proj_seg_code
658 ,lv_budget_type_seg_col
659 ,JA_CN_UTILITY.Get_Budget_Type_Value(pn_coa_id => pn_coa_id
660 ,pv_flexfield_code => 'DCPA')
661 ,pv_accounting_year
662 ,NULL
663 ,lv_accounting_period
664 )
665 ,Ja_Cn_Utility.GV_TYPE_NUMBER
666 ,Ja_Cn_Utility.GV_REQUIRED_YES
667 );
668 Ja_Cn_Utility.Add_Child_Node('CURRENT_PERIOD_INCOME_AMOUNT'
669 ,Get_Actual_Income_Amount(pn_ledger_id
670 ,lv_income_cat_seg_col
671 ,lv_income_cat_seg_code
672 ,lv_income_proj_seg_col
673 ,lv_income_proj_seg_code
674 ,NULL
675 ,lv_accounting_period
676 ,lv_accounting_period
677 )
678 ,Ja_Cn_Utility.GV_TYPE_NUMBER
679 ,Ja_Cn_Utility.GV_REQUIRED_YES
680 );
681 Ja_Cn_Utility.Add_Child_Node('ACCUMULATED_ACTUAL_INCOME_AMT'
682 ,Get_Actual_Income_Amount(pn_ledger_id
683 ,lv_income_cat_seg_col
684 ,lv_income_cat_seg_code
685 ,lv_income_proj_seg_col
686 ,lv_income_proj_seg_code
687 ,pv_accounting_year
688 ,NULL
689 ,lv_accounting_period
690 )
691 ,Ja_Cn_Utility.GV_TYPE_NUMBER
692 ,Ja_Cn_Utility.GV_REQUIRED_YES
693 );
694 Ja_Cn_Utility.Add_Sub_Root_Node('BUDGET_INCOME'
695 ,Ja_Cn_Utility.GV_TAG_TYPE_END
696 );
697 ln_rec_count := ln_rec_count + 1;
698
699 END LOOP;
700 CLOSE lc_budget_information;
701
702 --fixed bug 10316565 by ChuanLing Shao
703 END LOOP;
704 CLOSE lc_budget_period_information;
705
706 --logging the variables
707 IF (ln_statement_level >= ln_dbg_level)
708 THEN
709 FND_LOG.STRING(ln_statement_level,
710 lv_procedure_name,
711 'ln_rec_count:' || ln_rec_count);
712 END IF; --(ln_statement_level >= ln_dbg_level)
713 -- FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
714 -- '.variable:' ||
715 -- 'ln_rec_count=' || ln_rec_count);
716
717 -- There is no data found for employee record
718 IF ln_rec_count = 0
719 THEN
720 Ja_Cn_Utility.Add_Sub_Root_Node('BUDGET_INCOME'
721 ,Ja_Cn_Utility.GV_TAG_TYPE_START
722 );
723 Ja_Cn_Utility.Add_Fixed_Child_Node('ACCOUNTING_YEAR'
724 ,pv_accounting_year
725 ,4
726 ,Ja_Cn_Utility.GV_REQUIRED_YES
727 ,Ja_Cn_Utility.GV_MODULE_GLSI
728 );
729 Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_PERIOD_NUMBER'
730 ,NULL
731 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
732 ,Ja_Cn_Utility.GV_REQUIRED_YES
733 );
734 Ja_Cn_Utility.Add_Child_Node('INCOME_CATEGORY_CODE'
735 ,NULL
736 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
737 ,Ja_Cn_Utility.GV_REQUIRED_YES
738 );
739 Ja_Cn_Utility.Add_Child_Node('INCOME_CATEGORY_NAME'
740 ,NULL
741 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
742 ,Ja_Cn_Utility.GV_REQUIRED_YES
743 );
744 Ja_Cn_Utility.Add_Child_Node('INCOME_PROJECT_CODE'
745 ,NULL
746 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
747 ,Ja_Cn_Utility.GV_REQUIRED_YES
748 );
749 Ja_Cn_Utility.Add_Child_Node('INCOME_PROJECT_NAME'
750 ,NULL
751 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
752 ,Ja_Cn_Utility.GV_REQUIRED_YES
753 );
754 Ja_Cn_Utility.Add_Child_Node('CURRENT_YEAR_BUDGET_AMOUNT'
755 ,NULL
756 ,Ja_Cn_Utility.GV_TYPE_NUMBER
757 ,Ja_Cn_Utility.GV_REQUIRED_YES
758 );
759 Ja_Cn_Utility.Add_Child_Node('CURRENT_PERIOD_INC_AMOUNT'
760 ,NULL
761 ,Ja_Cn_Utility.GV_TYPE_NUMBER
762 ,Ja_Cn_Utility.GV_REQUIRED_YES
763 );
764 Ja_Cn_Utility.Add_Child_Node('CURRENT_PERIOD_DEC_AMOUNT'
765 ,NULL
766 ,Ja_Cn_Utility.GV_TYPE_NUMBER
767 ,Ja_Cn_Utility.GV_REQUIRED_YES
768 );
769 Ja_Cn_Utility.Add_Child_Node('ACCUMULATED_INCREASING_AMOUNT'
770 ,NULL
771 ,Ja_Cn_Utility.GV_TYPE_NUMBER
772 ,Ja_Cn_Utility.GV_REQUIRED_YES
773 );
774 Ja_Cn_Utility.Add_Child_Node('ACCUMULATED_DECREASING_AMOUNT'
775 ,NULL
776 ,Ja_Cn_Utility.GV_TYPE_NUMBER
777 ,Ja_Cn_Utility.GV_REQUIRED_YES
778 );
779 Ja_Cn_Utility.Add_Child_Node('CURRENT_PERIOD_INCOME_AMOUNT'
780 ,NULL
781 ,Ja_Cn_Utility.GV_TYPE_NUMBER
782 ,Ja_Cn_Utility.GV_REQUIRED_YES
783 );
784 Ja_Cn_Utility.Add_Child_Node('ACCUMULATED_ACTUAL_INCOME_AMT'
785 ,NULL
786 ,Ja_Cn_Utility.GV_TYPE_NUMBER
787 ,Ja_Cn_Utility.GV_REQUIRED_YES
788 );
789 Ja_Cn_Utility.Add_Sub_Root_Node('BUDGET_INCOME'
790 ,Ja_Cn_Utility.GV_TAG_TYPE_END
791 );
792 END IF; --ln_rec_count = 0
793
794 --logging for debug
795 IF (ln_proc_level >= ln_dbg_level)
796 THEN
797 FND_LOG.STRING(ln_proc_level,
798 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
799 'Exit procedure');
800 END IF; -- (ln_proc_level>=ln_dbg_level)
801
802 EXCEPTION
803 WHEN OTHERS THEN
804 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
805 THEN
806 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
807 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
808 '.Other_Exception ',
809 SQLCODE || SQLERRM);
810 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
811 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
812 --RAISE;
813
814 END Add_Budget_Income;
815
816 END JA_CN_PS_BI_EXPORT_PKG;