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