[Home] [Help]
PACKAGE BODY: APPS.JA_CN_PS_CYB_EXPORT_PKG
Source
1 PACKAGE BODY JA_CN_PS_CYB_EXPORT_PKG AS
2 --$Header: JACNPYBB.pls 120.8 2010/12/03 04:53:54 chuansha noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNPYBB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Validation report includes transaction as below: |
13 --| 1) Transaction in which the legal entity is not same |
14 --| with the legal entity that the Balance Segment Value in |
15 --| payables of the transaction is assigned. |
16 --| 2) Transaction which has not been itemized |
17 --| |
18 --| PROCEDURE LIST |
19 --| PROCEDURE Validate_Payables |
20 --| |
21 --| |
22 --| HISTORY |
23 --| 13-Apr-2010 Chaoqun Wu created |
24 --| 26-May-2010 Chaoqun Wu Updated for fixing bug# 9763810 |
25 --| 13-Sep-2010 Richard Qi Updated for fixing bug# 10103231 |
26 --| 15-Sep-2010 Richard Qi FIXED Bug# 10110709 |
27 --| 03-Dec-2010 ChuanLing Shao FIXED Bug# 10316565 |
28 --+======================================================================*/
29
30 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_PS_CYB_EXPORT_PKG';
31
32 FUNCTION Get_Budget_Expenditure_Amount
33 (pn_ledger_id IN NUMBER
34 ,pn_legal_entity_id IN NUMBER
35 ,pv_exp_fun_cat_segment_column IN VARCHAR2
36 ,pv_exp_fun_category_code IN VARCHAR2
37 ,pv_exp_eco_cat_segment_column IN VARCHAR2
38 ,pv_exp_eco_category_code IN VARCHAR2
39 ,pv_project_segment_column IN VARCHAR2
40 ,pv_project_id IN VARCHAR2
41 ,pv_budget_type_segment_column IN VARCHAR2
42 ,pv_budget_type_value IN VARCHAR2
43 ,pv_accounting_year IN VARCHAR2
44 ,pv_period_to IN VARCHAR2
45 )
46 RETURN NUMBER
47 IS
48 lv_procedure_name VARCHAR2(40) := 'Get_Budget_Expenditure_Amount';
49 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
50 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
51
52 lv_bea_cursor_sql VARCHAR2(4000);
53 TYPE ref_cursor IS REF CURSOR;
54 lc_budget_exp_amount ref_cursor;
55 ln_budget_exp_amount NUMBER := NULL;
56
57 BEGIN
58 --logging for debug
59 IF (ln_proc_level >= ln_dbg_level)
60 THEN
61 FND_LOG.STRING(ln_proc_level,
62 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
63 '.begin',
64 'Enter procedure');
65 -- logging the parameters
66 FND_LOG.STRING(ln_proc_level,
67 lv_procedure_name ||
68 '.parameters',
69 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
70 'pn_ledger_id=' || pn_ledger_id || ',' ||
71 'pv_exp_fun_cat_segment_column =' || pv_exp_fun_cat_segment_column || ',' ||
72 'pv_exp_fun_category_code=' || pv_exp_fun_category_code || ',' ||
73 'pv_exp_eco_cat_segment_column=' || pv_exp_eco_cat_segment_column || ',' ||
74 'pv_exp_eco_category_code=' || pv_exp_eco_category_code || ',' ||
75 'pv_project_segment_column=' || pv_project_segment_column || ',' ||
76 'pv_project_id=' || pv_project_id || ',' ||
77 'pv_budget_type_segment_column=' || pv_budget_type_segment_column || ',' ||
78 'pv_project_id=' || pv_project_id || ',' ||
79 'pv_accounting_year=' || pv_accounting_year || ',' ||
80 'pv_budget_type_value=' || pv_budget_type_value || ',' ||
81 'pv_period_to=' || pv_period_to);
82 END IF; --ln_proc_level>=ln_dbg_level
83
84 --Cursor for getting budget related amount
85 lv_bea_cursor_sql := 'SELECT SUM(NVL(Jel.Accounted_Dr,0) - NVL(Jel.Accounted_Cr,0)) Budget_Related_Amount';
86 lv_bea_cursor_sql := lv_bea_cursor_sql || ' FROM gl_je_lines jel,';
87 lv_bea_cursor_sql := lv_bea_cursor_sql || ' gl_je_headers jeh,';
88 lv_bea_cursor_sql := lv_bea_cursor_sql || ' gl_code_combinations gcc,';
89 lv_bea_cursor_sql := lv_bea_cursor_sql || ' Gl_Periods Gp,';
90 lv_bea_cursor_sql := lv_bea_cursor_sql || ' Gl_Ledgers Gle';
91 lv_bea_cursor_sql := lv_bea_cursor_sql || ' WHERE jeh.ledger_id = ' || pn_ledger_id; --parameter: pn_ledger_id
92 lv_bea_cursor_sql := lv_bea_cursor_sql || ' AND jel.Code_Combination_ID = gcc.Code_Combination_ID';
93 lv_bea_cursor_sql := lv_bea_cursor_sql || ' AND jel.je_header_id = jeh.je_header_id';
94 --1) Collecting budget journals with posted status
95 lv_bea_cursor_sql := lv_bea_cursor_sql || ' AND jeh.actual_flag = ''B''';
96 lv_bea_cursor_sql := lv_bea_cursor_sql || ' AND jeh.status = ''P''';
97 --2) Budget related condition
98 lv_bea_cursor_sql := lv_bea_cursor_sql || ' AND gcc.'|| pv_exp_fun_cat_segment_column ||'= '''|| pv_exp_fun_category_code ||'''';
99 lv_bea_cursor_sql := lv_bea_cursor_sql || ' AND gcc.'|| pv_exp_eco_cat_segment_column ||'= '''|| pv_exp_eco_category_code ||'''';
100 lv_bea_cursor_sql := lv_bea_cursor_sql || ' AND gcc.'|| pv_project_segment_column ||'= '''|| pv_project_id ||'''';
101
102 IF (pv_budget_type_value IS NULL)
103 THEN
104 lv_bea_cursor_sql := lv_bea_cursor_sql || ' AND gcc.'|| pv_budget_type_segment_column ||' IS NULL ';
105 ELSE
106 lv_bea_cursor_sql := lv_bea_cursor_sql || ' AND gcc.'|| pv_budget_type_segment_column ||'= '''|| pv_budget_type_value ||'''';
107 END IF;
108
109 --3) BSV condition
110 lv_bea_cursor_sql:= lv_bea_cursor_sql || ' AND EXISTS (SELECT llbg.Bal_Seg_Value ';
111 lv_bea_cursor_sql := lv_bea_cursor_sql || ' FROM ja_cn_ledger_le_bsv_gt llbg';
112 lv_bea_cursor_sql := lv_bea_cursor_sql || ' WHERE JA_CN_UTILITY.get_balancing_segment(gcc.CODE_COMBINATION_ID) = llbg.Bal_Seg_Value';
113 lv_bea_cursor_sql := lv_bea_cursor_sql || ' AND llbg.Ledger_Id = ' || pn_ledger_id; --parameter: pn_ledger_id
114 lv_bea_cursor_sql := lv_bea_cursor_sql || ' AND llbg.Legal_Entity_Id =' || pn_legal_entity_id || ')'; --parameter: pn_legal_entity_id;
115 --4) Period condition
116 lv_bea_cursor_sql := lv_bea_cursor_sql || ' AND Gle.period_set_name = Gp.period_set_name';
117 lv_bea_cursor_sql := lv_bea_cursor_sql || ' AND Gle.accounted_period_type = Gp.period_type';
118 lv_bea_cursor_sql := lv_bea_cursor_sql || ' AND Gle.Ledger_Id = jeh.ledger_id';
119 lv_bea_cursor_sql := lv_bea_cursor_sql || ' AND Gp.Period_Name = Jeh.Period_Name';
120 lv_bea_cursor_sql := lv_bea_cursor_sql || ' AND jeh.Default_Effective_Date BETWEEN';
121 lv_bea_cursor_sql := lv_bea_cursor_sql || ' (SELECT MIN(Gp1.Start_Date)';
122 lv_bea_cursor_sql := lv_bea_cursor_sql || ' FROM Gl_Periods Gp1';
123 lv_bea_cursor_sql := lv_bea_cursor_sql || ' WHERE Gle.period_set_name = Gp1.period_set_name';
124 lv_bea_cursor_sql := lv_bea_cursor_sql || ' AND Gle.accounted_period_type = Gp1.period_type';
125 lv_bea_cursor_sql := lv_bea_cursor_sql || ' AND Gp1.period_year = '''|| pv_accounting_year ||''')'; --parameter: pv_accounting_year
126 lv_bea_cursor_sql := lv_bea_cursor_sql || ' AND (SELECT MAX(Gp2.End_Date)';
127 lv_bea_cursor_sql := lv_bea_cursor_sql || ' FROM Gl_Periods Gp2';
128 lv_bea_cursor_sql := lv_bea_cursor_sql || ' WHERE Gle.period_set_name = Gp2.period_set_name';
129 lv_bea_cursor_sql := lv_bea_cursor_sql || ' AND Gle.accounted_period_type = Gp2.period_type';
130 lv_bea_cursor_sql := lv_bea_cursor_sql || ' AND (('''|| pv_period_to ||''' IS NULL '; --parameter: pv_period_to
131 lv_bea_cursor_sql := lv_bea_cursor_sql || ' AND Gp2.period_year = '''|| pv_accounting_year ||''')'; --parameter: pv_accounting_year
132 lv_bea_cursor_sql := lv_bea_cursor_sql || ' OR ('''|| pv_period_to ||''' IS NOT NULL'; --parameter: pv_period_to
133 lv_bea_cursor_sql := lv_bea_cursor_sql || ' AND Gp2.period_name = '''|| pv_period_to ||''')))'; --parameter: pv_period_to
134
135 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'lv_bea_cursor_sql='||lv_bea_cursor_sql);
136
137 OPEN lc_budget_exp_amount FOR lv_bea_cursor_sql;
138 FETCH lc_budget_exp_amount
139 INTO ln_budget_exp_amount;
140 CLOSE lc_budget_exp_amount;
141
142 --logging for debug
143 IF (ln_proc_level >= ln_dbg_level)
144 THEN
145 FND_LOG.STRING(ln_proc_level,
146 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
147 'Exit procedure');
148 END IF; -- (ln_proc_level>=ln_dbg_level)
149
150 RETURN ln_budget_exp_amount;
151
152 EXCEPTION
153 WHEN OTHERS THEN
154 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
155 THEN
156 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
157 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
158 '.Other_Exception ',
159 SQLCODE || SQLERRM);
160 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
161 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
162
163 END Get_Budget_Expenditure_Amount;
164 --==========================================================================
165 -- PROCEDURE NAME:
166 --
167 -- Add_Current_Year_Budget Public
168 --
169 -- DESCRIPTION:
170 --
171 -- Validation report includes transaction as below:
172 -- 1) Transaction in which the legal entity is not same
173 -- with the legal entity that the Balance Segment Value in
174 -- payables of the transaction is assigned.
175 -- 2) Transaction which has not been itemized
176 --
177 -- PARAMETERS:
178 -- Out: pv_errbuf NOCOPY VARCHAR2
179 -- pv_retcode NOCOPY VARCHAR2
180 -- In: pn_legal_entity_id NUMBER identifier of legal entity
181 -- pn_ledger_id NUMBER identifier of ledger
182 -- pv_accounting_year VARCHAR2 accounting year
183 -- pv_period_from VARCHAR2 period from
184 -- pv_period_to VARCHAR2 period to
185 --
186 -- DESIGN REFERENCES:
187 -- APAR_Chaoqun.doc
188 --
189 -- CHANGE HISTORY:
190 -- 13-Aug-2010 Chaoqun Wu created
191 -- 15-Sep-2010 Richard Qi FIXED Bug# 10110709
192 --==========================================================================
193
194 PROCEDURE Add_Current_Year_Budget
195 (pn_legal_entity_id IN NUMBER
196 ,pn_ledger_id IN NUMBER
197 ,pn_coa_id IN NUMBER
198 ,pv_accounting_year IN VARCHAR2
199 ,pv_period_from IN VARCHAR2
200 ,pv_period_to IN VARCHAR2
201 )
202 IS
203 lv_procedure_name VARCHAR2(40) := 'Add_Current_Year_Budget';
204 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
205 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
206
207 lv_bi_cursor_sql VARCHAR2(4000);
208 lv_pi_cursor_sql VARCHAR2(3000);
209 TYPE ref_cursor IS REF CURSOR;
210 lc_budget_information ref_cursor;
211 lc_budget_period_information ref_cursor;
212
213 lv_exp_fun_cat_segment_column VARCHAR2(30);
214 lv_exp_eco_cat_segment_column VARCHAR2(30);
215 lv_project_segment_column VARCHAR2(30);
216 lv_budget_type_segment_column VARCHAR2(30);
217 lv_budget_type_value VARCHAR2(25);
218
219 ln_accounting_period_num NUMBER;
220 lv_exp_fun_cat GL_CODE_COMBINATIONS.SEGMENT1%TYPE;
221 lv_exp_eco_cat GL_CODE_COMBINATIONS.SEGMENT1%TYPE;
222 lv_project GL_CODE_COMBINATIONS.SEGMENT1%TYPE;
223 lv_period_name GL_PERIODS.PERIOD_NAME%TYPE;
224
225 lv_fun_nature_code VARCHAR2(100);
226 lv_bud_source_code VARCHAR2(100);
227
228 ln_current_year_budget_amt NUMBER := 0;
229 ln_accumulated_inc_amount NUMBER := 0;
230 ln_accumulated_dec_amount NUMBER := 0;
231
232 ln_rec_count NUMBER := 0; -- added for fixing bug#: 10103231
233
234 BEGIN
235 --logging for debug
236 IF (ln_proc_level >= ln_dbg_level)
237 THEN
238 FND_LOG.STRING(ln_proc_level,
239 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
240 '.begin',
241 'Enter procedure');
242 -- logging the parameters
243 FND_LOG.STRING(ln_proc_level,
244 lv_procedure_name ||
245 '.parameters',
246 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
247 'pn_ledger_id=' || pn_ledger_id || ',' ||
248 'pv_accounting_year=' || pv_accounting_year || ',' ||
249 'pv_period_from=' || pv_period_from || ',' ||
250 'pv_period_to=' || pv_period_to);
251 END IF; --ln_proc_level>=ln_dbg_level
252 -- FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
253 -- '.parameters:' ||
254 -- 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
255 -- 'pn_ledger_id=' || pn_ledger_id || ',' ||
256 -- 'pv_accounting_year=' || pv_accounting_year || ',' ||
257 -- 'pv_period_from=' || pv_period_from || ',' ||
258 -- 'pv_period_to=' || pv_period_to);
259
260 --Step0: Populate balancing segment values to table ja_cn_ledger_le_bsv_gt
261 DELETE
262 FROM JA_CN_LEDGER_LE_BSV_GT;
263 COMMIT ;
264 IF ja_cn_utility.populate_ledger_le_bsv_gt(pn_ledger_id, pn_legal_entity_id) <> 'S'
265 THEN
266 RETURN;
267 END IF;
268
269 lv_exp_fun_cat_segment_column := JA_CN_UTILITY.Get_Attribute_Column(pv_flexfield_code => 'CEFC'
270 ,pn_application_id => 101
271 ,pn_coa_id => pn_coa_id);
272 lv_exp_eco_cat_segment_column := JA_CN_UTILITY.Get_Attribute_Column(pv_flexfield_code => 'CEEC'
273 ,pn_application_id => 101
274 ,pn_coa_id => pn_coa_id);
275 lv_project_segment_column := JA_CN_UTILITY.Get_Attribute_Column(pv_flexfield_code => 'CPRO'
276 ,pn_application_id => 101
277 ,pn_coa_id => pn_coa_id);
278
279 lv_budget_type_segment_column := JA_CN_UTILITY.Get_Attribute_Column(pv_flexfield_code => 'CBTY'
280 ,pn_application_id => 101
281 ,pn_coa_id => pn_coa_id);
282 lv_budget_type_value := JA_CN_UTILITY.Get_Budget_Type_Value(pn_coa_id => pn_coa_id
283 ,pv_flexfield_code => 'LYBA'
284 );
285
286 --fixed bug 10316565 by ChuanLing Shao
287 lv_pi_cursor_sql := ' SELECT DISTINCT Gp.Period_Num Accounting_Period_Num, Gp.Period_Name Accounting_Period ';
288 lv_pi_cursor_sql := lv_pi_cursor_sql || ' FROM Gl_Periods Gp , ';
289 lv_pi_cursor_sql := lv_pi_cursor_sql || ' Gl_Ledgers Gle ';
290 lv_pi_cursor_sql := lv_pi_cursor_sql || ' WHERE ' ;
291 lv_pi_cursor_sql := lv_pi_cursor_sql || ' Gle.period_set_name = Gp.period_set_name ';
292 lv_pi_cursor_sql := lv_pi_cursor_sql || ' AND Gle.accounted_period_type = Gp.period_type ';
293 lv_pi_cursor_sql := lv_pi_cursor_sql || ' AND Gle.Ledger_Id = '''||pn_ledger_id||'''' ; --pn_ledger_id
294 lv_pi_cursor_sql := lv_pi_cursor_sql || ' AND ';
295 lv_pi_cursor_sql := lv_pi_cursor_sql || ' Gp.Start_date BETWEEN ' ;
296 lv_pi_cursor_sql := lv_pi_cursor_sql || ' ( ';
297 lv_pi_cursor_sql := lv_pi_cursor_sql || ' SELECT MIN(Gp1.Start_Date) ';
298 lv_pi_cursor_sql := lv_pi_cursor_sql || ' FROM Gl_Periods Gp1 ';
299 lv_pi_cursor_sql := lv_pi_cursor_sql || ' WHERE Gle.period_set_name = Gp1.period_set_name ';
300 lv_pi_cursor_sql := lv_pi_cursor_sql || ' AND Gle.accounted_period_type = Gp1.period_type ';
301 lv_pi_cursor_sql := lv_pi_cursor_sql || ' AND Gp1.period_name = '''||pv_period_from||'''';
302 lv_pi_cursor_sql := lv_pi_cursor_sql || ' ) ';
303 lv_pi_cursor_sql := lv_pi_cursor_sql || ' AND ';
304 lv_pi_cursor_sql := lv_pi_cursor_sql || ' ( ';
305 lv_pi_cursor_sql := lv_pi_cursor_sql || ' SELECT MAX(Gp2.End_Date) ';
306 lv_pi_cursor_sql := lv_pi_cursor_sql || ' FROM Gl_Periods Gp2 ';
307 lv_pi_cursor_sql := lv_pi_cursor_sql || ' WHERE Gle.period_set_name = Gp2.period_set_name ';
308 lv_pi_cursor_sql := lv_pi_cursor_sql || ' AND Gle.accounted_period_type = Gp2.period_type ';
309 lv_pi_cursor_sql := lv_pi_cursor_sql || ' AND Gp2.period_name = '''||pv_period_to||'''';
310 lv_pi_cursor_sql := lv_pi_cursor_sql || ' ) ';
311 lv_pi_cursor_sql := lv_pi_cursor_sql || ' order by Gp.Period_Num ';
312
313
314 --FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
315 --'.lv_pi_cursor_sql:' || lv_pi_cursor_sql);
316
317 lv_bi_cursor_sql := 'SELECT DISTINCT *'; ----fixed bug 10316565 by ChuanLing Shao as ChaoQun's great help
318 --lv_bi_cursor_sql := lv_bi_cursor_sql || ' FROM (SELECT Gp.Period_Num Accounting_Period_Num,';
319 --lv_bi_cursor_sql := lv_bi_cursor_sql || ' Gp.Period_Name Accounting_Period,';
320 lv_bi_cursor_sql := lv_bi_cursor_sql || ' FROM (SELECT ';
321 lv_bi_cursor_sql := lv_bi_cursor_sql || ' gcc.'|| lv_exp_fun_cat_segment_column ||' Exp_Fun_Category_Code,';
322 lv_bi_cursor_sql := lv_bi_cursor_sql || ' gcc.'|| lv_exp_eco_cat_segment_column ||' Exp_Eco_Category_Code,';
323 lv_bi_cursor_sql := lv_bi_cursor_sql || ' gcc.'|| lv_project_segment_column ||' Project_Id';
324 lv_bi_cursor_sql := lv_bi_cursor_sql || ' FROM gl_je_lines jel,';
325 lv_bi_cursor_sql := lv_bi_cursor_sql || ' gl_je_headers jeh,';
326 lv_bi_cursor_sql := lv_bi_cursor_sql || ' gl_code_combinations gcc,';
327 lv_bi_cursor_sql := lv_bi_cursor_sql || ' Gl_Periods Gp,';
328 lv_bi_cursor_sql := lv_bi_cursor_sql || ' Gl_Ledgers Gle';
329 lv_bi_cursor_sql := lv_bi_cursor_sql || ' WHERE jeh.ledger_id = ' || pn_ledger_id; --parameter: pn_ledger_id
330 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND jel.Code_Combination_ID = gcc.Code_Combination_ID';
331 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND jel.Je_Header_Id = jeh.Je_Header_Id';
332 -- added to fix bug# 10110709 --BEGIN--
333 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND gcc.'|| lv_exp_fun_cat_segment_column ||' IS NOT NULL';
334 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND gcc.'|| lv_exp_eco_cat_segment_column ||' IS NOT NULL';
335 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND gcc.'|| lv_project_segment_column ||' IS NOT NULL';
336 -- added to fix bug# 10110709 --END--
337 --1) Collecting budget journals with posted status
338 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND jeh.actual_flag = ''B''';
339 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND jeh.status = ''P''';
340 --2) Budget type condition
341 IF (lv_budget_type_value IS NULL)
342 THEN
343 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND gcc.'|| lv_budget_type_segment_column ||' IS NOT NULL ';
344 ELSE
345 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND gcc.'|| lv_budget_type_segment_column ||' <> ''' || lv_budget_type_value || '''';
346 END IF;
347 --3) BSV condition
348 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND EXISTS (SELECT llbg.Bal_Seg_Value ';
349 lv_bi_cursor_sql := lv_bi_cursor_sql || ' FROM ja_cn_ledger_le_bsv_gt llbg';
350 lv_bi_cursor_sql := lv_bi_cursor_sql || ' WHERE JA_CN_UTILITY.get_balancing_segment(gcc.CODE_COMBINATION_ID) = llbg.Bal_Seg_Value';
351 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND llbg.Ledger_Id = ' || pn_ledger_id; --parameter: pn_ledger_id
352 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND llbg.Legal_Entity_Id =' || pn_legal_entity_id || ')'; --parameter: pn_legal_entity_id;
353 --4) Period condition
354 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND Gle.period_set_name = Gp.period_set_name';
355 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND Gle.accounted_period_type = Gp.period_type';
356 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND Gle.Ledger_Id = jeh.ledger_id';
357 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND Gp.Period_Name = Jeh.Period_Name';
358 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND jeh.Default_Effective_Date BETWEEN';
359 lv_bi_cursor_sql := lv_bi_cursor_sql || ' (SELECT MIN(Gp1.Start_Date)';
360 lv_bi_cursor_sql := lv_bi_cursor_sql || ' FROM Gl_Periods Gp1';
361 lv_bi_cursor_sql := lv_bi_cursor_sql || ' WHERE Gle.period_set_name = Gp1.period_set_name';
362 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND Gle.accounted_period_type = Gp1.period_type';
363 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND Gp1.period_year = '''||pv_accounting_year||''')'; --parameter: pv_period_from
364 --lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND Gp1.period_name = '''||pv_period_from||''')'; --parameter: pv_period_from
365 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND (SELECT MAX(Gp2.End_Date)';
366 lv_bi_cursor_sql := lv_bi_cursor_sql || ' FROM Gl_Periods Gp2';
367 lv_bi_cursor_sql := lv_bi_cursor_sql || ' WHERE Gle.period_set_name = Gp2.period_set_name';
368 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND Gle.accounted_period_type = Gp2.period_type';
369 lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND Gp2.period_year = '''||pv_accounting_year||'''))'; --parameter: pv_period_to
370 --lv_bi_cursor_sql := lv_bi_cursor_sql || ' AND Gp2.period_name = '''||pv_period_to||'''))'; --parameter: pv_period_to
371 --lv_bi_cursor_sql := lv_bi_cursor_sql || ' ORDER BY Accounting_Period_Num';
372 lv_bi_cursor_sql := lv_bi_cursor_sql || ' ORDER BY ';
373 lv_bi_cursor_sql := lv_bi_cursor_sql || ' Exp_Fun_Category_Code';
374 lv_bi_cursor_sql := lv_bi_cursor_sql || ' ,Exp_Eco_Category_Code';
375 lv_bi_cursor_sql := lv_bi_cursor_sql || ' ,Project_Id';
376
377 --FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
378 --'.lv_bi_cursor_sql:' || lv_bi_cursor_sql);
379
380 --fixed bug 10316565 by ChuanLing Shao
381 OPEN lc_budget_period_information FOR lv_pi_cursor_sql;
382 LOOP
383 FETCH lc_budget_period_information
384 INTO ln_accounting_period_num,lv_period_name;
385 EXIT WHEN lc_budget_period_information%NOTFOUND;
386
387 -----------------------------------------------------------------------------------------------------------
388 OPEN lc_budget_information FOR lv_bi_cursor_sql;
389 LOOP
390 FETCH lc_budget_information
391 INTO lv_exp_fun_cat
392 ,lv_exp_eco_cat
393 ,lv_project;
394 /*INTO ln_accounting_period_num --fixed bug 10316565 by ChuanLing Shao
395 ,lv_period_name
396 ,lv_exp_fun_cat
397 ,lv_exp_eco_cat
398 ,lv_project;
399 */
400 EXIT WHEN lc_budget_information%NOTFOUND;
401
402 Ja_Cn_Utility.Add_Sub_Root_Node('CURRENT_YEAR_BUDGET_TARGET'
403 ,Ja_Cn_Utility.GV_TAG_TYPE_START
404 );
405 Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_YEAR'
406 ,pv_accounting_year
407 );
408 Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_PERIOD_NUMBER'
409 ,ln_accounting_period_num
410 );
411 Ja_Cn_Utility.Add_Child_Node('EXPENDITURE_FUN_CATEGORY_CODE'
412 ,lv_exp_fun_cat
413 );
414 Ja_Cn_Utility.Add_Child_Node('EXPENDITURE_FUN_CATEGORY_NAME'
415 ,JA_CN_UTILITY.Get_Segment_Value_Desc(pv_flexfield_code => 'CEFC'
416 ,pv_flex_value => lv_exp_fun_cat
417 ,pn_coa_id => pn_coa_id)
418 );
419 Ja_Cn_Utility.Add_Child_Node('EXPENDITURE_ECO_CATEGORY_CODE'
420 ,lv_exp_eco_cat
421 );
422 Ja_Cn_Utility.Add_Child_Node('EXPENDITURE_ECO_CATEGORY_NAME'
423 ,JA_CN_UTILITY.Get_Segment_Value_Desc(pv_flexfield_code => 'CEEC'
424 ,pv_flex_value => lv_exp_eco_cat
425 ,pn_coa_id => pn_coa_id)
426 );
427 Ja_Cn_Utility.Add_Child_Node('PROJECT_ID'
428 ,lv_project
429 );
430 lv_fun_nature_code := JA_CN_UTILITY.Get_Attribute_Value(pv_flexfield_code => 'AFTY'
431 ,pv_project_id => lv_project
432 ,pn_coa_id => pn_coa_id);
433
434 Ja_Cn_Utility.Add_Child_Node('FUND_NATURE_CODE'
435 ,lv_fun_nature_code
436 );
437 Ja_Cn_Utility.Add_Child_Node('FUND_NATURE_NAME'
438 ,JA_CN_UTILITY.Get_Attribute_Value_Desc(pv_flexfield_code => 'AFTY'
439 ,pv_flex_value => lv_fun_nature_code
440 ,pn_coa_id => pn_coa_id)
441 );
442 lv_bud_source_code := JA_CN_UTILITY.Get_Attribute_Value(pv_flexfield_code => 'ABSO'
443 ,pv_project_id => lv_project
444 ,pn_coa_id => pn_coa_id);
445 Ja_Cn_Utility.Add_Child_Node('BUDGET_SOURCE_CODE'
446 ,lv_bud_source_code
447 );
448 Ja_Cn_Utility.Add_Child_Node('BUDGET_SOURCE_NAME'
449 ,JA_CN_UTILITY.Get_Attribute_Value_Desc(pv_flexfield_code => 'ABSO'
450 ,pv_flex_value => lv_bud_source_code
451 ,pn_coa_id => pn_coa_id)
452 );
453 ln_current_year_budget_amt := Get_Budget_Expenditure_Amount(pn_ledger_id => pn_ledger_id
454 ,pn_legal_entity_id => pn_legal_entity_id
455 ,pv_exp_fun_cat_segment_column => lv_exp_fun_cat_segment_column
456 ,pv_exp_fun_category_code => lv_exp_fun_cat
457 ,pv_exp_eco_cat_segment_column => lv_exp_eco_cat_segment_column
458 ,pv_exp_eco_category_code => lv_exp_eco_cat
459 ,pv_project_segment_column => lv_project_segment_column
460 ,pv_project_id => lv_project
461 ,pv_budget_type_segment_column => lv_budget_type_segment_column
462 ,pv_budget_type_value => JA_CN_UTILITY.Get_Budget_Type_Value(pn_coa_id => pn_coa_id
463 ,pv_flexfield_code => 'CYBA')
464 ,pv_accounting_year => pv_accounting_year
465 ,pv_period_to => NULL
466 );
467 Ja_Cn_Utility.Add_Child_Node('CURRENT_YEAR_BUDGET_AMOUNT'
468 ,ln_current_year_budget_amt
469 ,Ja_Cn_Utility.GV_TYPE_NUMBER
470 ,Ja_Cn_Utility.GV_REQUIRED_YES
471 );
472 ln_accumulated_inc_amount := Get_Budget_Expenditure_Amount(pn_ledger_id => pn_ledger_id
473 ,pn_legal_entity_id => pn_legal_entity_id
474 ,pv_exp_fun_cat_segment_column => lv_exp_fun_cat_segment_column
475 ,pv_exp_fun_category_code => lv_exp_fun_cat
476 ,pv_exp_eco_cat_segment_column => lv_exp_eco_cat_segment_column
477 ,pv_exp_eco_category_code => lv_exp_eco_cat
478 ,pv_project_segment_column => lv_project_segment_column
479 ,pv_project_id => lv_project
480 ,pv_budget_type_segment_column => lv_budget_type_segment_column
481 ,pv_budget_type_value => JA_CN_UTILITY.Get_Budget_Type_Value(pn_coa_id => pn_coa_id
482 ,pv_flexfield_code => 'ICPA')
483 ,pv_accounting_year => pv_accounting_year
484 ,pv_period_to => lv_period_name
485 );
486 Ja_Cn_Utility.Add_Child_Node('ACCUMULATED_INCREASING_AMOUNT'
487 ,ln_accumulated_inc_amount
488 ,Ja_Cn_Utility.GV_TYPE_NUMBER
489 ,Ja_Cn_Utility.GV_REQUIRED_YES
490 );
491 ln_accumulated_dec_amount := Get_Budget_Expenditure_Amount(pn_ledger_id => pn_ledger_id
492 ,pn_legal_entity_id => pn_legal_entity_id
493 ,pv_exp_fun_cat_segment_column => lv_exp_fun_cat_segment_column
494 ,pv_exp_fun_category_code => lv_exp_fun_cat
495 ,pv_exp_eco_cat_segment_column => lv_exp_eco_cat_segment_column
496 ,pv_exp_eco_category_code => lv_exp_eco_cat
497 ,pv_project_segment_column => lv_project_segment_column
498 ,pv_project_id => lv_project
499 ,pv_budget_type_segment_column => lv_budget_type_segment_column
500 ,pv_budget_type_value => JA_CN_UTILITY.Get_Budget_Type_Value(pn_coa_id => pn_coa_id
501 ,pv_flexfield_code => 'DCPA')
502 ,pv_accounting_year => pv_accounting_year
503 ,pv_period_to => lv_period_name
504 );
505 Ja_Cn_Utility.Add_Child_Node('ACCUMULATED_DECREASING_AMOUNT'
506 ,ln_accumulated_dec_amount
507 ,Ja_Cn_Utility.GV_TYPE_NUMBER
508 ,Ja_Cn_Utility.GV_REQUIRED_YES
509 );
510 Ja_Cn_Utility.Add_Child_Node('TOTAL_AMOUNT'
511 ,NVL(ln_current_year_budget_amt,0)
512 +NVL(ln_accumulated_inc_amount,0)
513 -NVL(ln_accumulated_dec_amount,0)
514 );
515 Ja_Cn_Utility.Add_Sub_Root_Node('CURRENT_YEAR_BUDGET_TARGET'
516 ,Ja_Cn_Utility.GV_TAG_TYPE_END
517 );
518 ln_rec_count := ln_rec_count + 1; -- added for fixing bug#: 10103231
519 END LOOP;
520 CLOSE lc_budget_information;
521
522 --fixed bug 10316565 by ChuanLing Shao
523 END LOOP;
524 CLOSE lc_budget_period_information;
525
526 -- added for fixing bug#: 10103231 --BEGIN--
527 -- There is no data found for employee record
528 IF ln_rec_count = 0
529 THEN
530 Ja_Cn_Utility.Add_Sub_Root_Node('CURRENT_YEAR_BUDGET_TARGET'
531 ,Ja_Cn_Utility.GV_TAG_TYPE_START
532 );
533 Ja_Cn_Utility.Add_Fixed_Child_Node('ACCOUNTING_YEAR'
534 ,pv_accounting_year
535 ,4
536 ,Ja_Cn_Utility.GV_REQUIRED_YES
537 ,Ja_Cn_Utility.GV_MODULE_GLSI
538 );
539 Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_PERIOD_NUMBER'
540 ,NULL
541 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
542 ,Ja_Cn_Utility.GV_REQUIRED_YES
543 );
544 Ja_Cn_Utility.Add_Child_Node('EXPENDITURE_FUN_CATEGORY_CODE'
545 ,NULL
546 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
547 ,Ja_Cn_Utility.GV_REQUIRED_YES
548 );
549 Ja_Cn_Utility.Add_Child_Node('EXPENDITURE_FUN_CATEGORY_NAME'
550 ,NULL
551 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
552 ,Ja_Cn_Utility.GV_REQUIRED_YES
553 );
554 Ja_Cn_Utility.Add_Child_Node('EXPENDITURE_ECO_CATEGORY_CODE'
555 ,NULL
556 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
557 ,Ja_Cn_Utility.GV_REQUIRED_YES
558 );
559 Ja_Cn_Utility.Add_Child_Node('EXPENDITURE_ECO_CATEGORY_NAME'
560 ,NULL
561 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
562 ,Ja_Cn_Utility.GV_REQUIRED_YES
563 );
564 Ja_Cn_Utility.Add_Child_Node('PROJECT_ID'
565 ,NULL
566 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
567 ,Ja_Cn_Utility.GV_REQUIRED_YES
568 );
569 Ja_Cn_Utility.Add_Child_Node('FUND_NATURE_CODE'
570 ,NULL
571 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
572 ,Ja_Cn_Utility.GV_REQUIRED_YES
573 );
574 Ja_Cn_Utility.Add_Child_Node('FUND_NATURE_NAME'
575 ,NULL
576 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
577 ,Ja_Cn_Utility.GV_REQUIRED_YES
578 );
579 Ja_Cn_Utility.Add_Child_Node('BUDGET_SOURCE_CODE'
580 ,NULL
581 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
582 ,Ja_Cn_Utility.GV_REQUIRED_YES
583 );
584 Ja_Cn_Utility.Add_Child_Node('BUDGET_SOURCE_NAME'
585 ,NULL
586 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
587 ,Ja_Cn_Utility.GV_REQUIRED_YES
588 );
589 Ja_Cn_Utility.Add_Child_Node('CURRENT_YEAR_BUDGET_AMOUNT'
590 ,NULL
591 ,Ja_Cn_Utility.GV_TYPE_NUMBER
592 ,Ja_Cn_Utility.GV_REQUIRED_YES
593 );
594 Ja_Cn_Utility.Add_Child_Node('ACCUMULATED_INCREASING_AMOUNT'
595 ,NULL
596 ,Ja_Cn_Utility.GV_TYPE_NUMBER
597 ,Ja_Cn_Utility.GV_REQUIRED_YES
598 );
599 Ja_Cn_Utility.Add_Child_Node('ACCUMULATED_DECREASING_AMOUNT'
600 ,NULL
601 ,Ja_Cn_Utility.GV_TYPE_NUMBER
602 ,Ja_Cn_Utility.GV_REQUIRED_YES
603 );
604 Ja_Cn_Utility.Add_Child_Node('TOTAL_AMOUNT'
605 ,NULL
606 ,Ja_Cn_Utility.GV_TYPE_NUMBER
607 ,Ja_Cn_Utility.GV_REQUIRED_YES
608 );
609 Ja_Cn_Utility.Add_Sub_Root_Node('CURRENT_YEAR_BUDGET_TARGET'
610 ,Ja_Cn_Utility.GV_TAG_TYPE_END
611 );
612 END IF; --ln_rec_count = 0
613 -- added for fixing bug#: 10103231 --END--
614
615 --logging for debug
616 IF (ln_proc_level >= ln_dbg_level)
617 THEN
618 FND_LOG.STRING(ln_proc_level,
619 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
620 'Exit procedure');
621 END IF; -- (ln_proc_level>=ln_dbg_level)
622
623 EXCEPTION
624 WHEN OTHERS THEN
625 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
626 THEN
627 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
628 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
629 '.Other_Exception ',
630 SQLCODE || SQLERRM);
631 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
632 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
633
634 END Add_Current_Year_Budget;
635
636 END JA_CN_PS_CYB_EXPORT_PKG;