1 PACKAGE BODY JE_PT_GL_PFTLS_PKG AS
2 -- $Header: jeptglpb.pls 120.0.12000000.1 2007/10/24 09:50:11 sgudupat noship $
3
4 PROCEDURE parent_child(p_parent IN VARCHAR2
5 ,p_summary_flag IN VARCHAR2
6 ,x_lev IN OUT NOCOPY NUMBER ) IS
7
8 /* cursor to find direct children */
9 CURSOR children_csr(p_parent_node VARCHAR2) IS
10 SELECT ffvnh.parent_flex_value parent_flex_value
11 ,ffv.flex_value flex_value,ffv.summary_flag
12 FROM fnd_flex_value_norm_hierarchy ffvnh
13 ,fnd_flex_values ffv
14 WHERE ffvnh.flex_value_set_id=gn_flex_value_set_id
15 AND ffv.flex_value_set_id=ffvnh.flex_value_set_id
16 AND (ffv.flex_value BETWEEN ffvnh.child_flex_value_low
17 AND ffvnh.child_flex_value_high)
18 AND ((ffvnh.range_attribute = 'P' AND ffv.summary_flag = 'Y') OR
19 (ffvnh.range_attribute = 'C' AND ffv.summary_flag = 'N'))
20 AND ffvnh.parent_flex_value = p_parent_node;
21
22
23 BEGIN
24 parent_value_rec(gn_iteration) := p_parent;
25 gn_iteration:=gn_iteration+1;
26 FOR lcr_direct_children_rec IN children_csr(p_parent)
27 LOOP
28 x_lev:=x_lev+1;
29 parent_child(lcr_direct_children_rec.flex_value,lcr_direct_children_rec.summary_flag,x_lev);
30
31 END LOOP;
32
33 FOR i IN parent_value_rec.first..parent_value_rec.last
34 LOOP
35 IF (p_summary_flag = 'N' OR x_lev=1)
36 THEN
37 INSERT INTO je_parent_child_gt(parent_value,child_value)
38 VALUES (parent_value_rec(i),p_parent);
39 END IF;
40 END LOOP;
41
42 parent_value_rec.delete(parent_value_rec.last);
43 gn_iteration:=gn_iteration-1;
44
45 EXCEPTION
46 WHEN OTHERS THEN
47 gc_debug_var :=gc_debug_var||', EXCEPTION IN FINDING DIRECT CHILDREN IN THE PROCEDURE parent_child
48 FOR THE PARENT'||p_parent;
49 RAISE;
50 END;
51
52 /*Procedure to get the parent and child nodes balances into the global temporary table */
53
54 PROCEDURE update_amount(p_period_set_name IN VARCHAR2
55 ,p_period_type IN VARCHAR2
56 ,p_bal_seg_filter IN VARCHAR2) IS
57
58 lc_stmt VARCHAR2(2000);
59 ln_cur_amt NUMBER(20);
60 ln_pre_amt NUMBER(20);
61 ln_amt NUMBER(20);
62 ln_pamt NUMBER(20);
63 BEGIN
64
65 /* Statement to update the Global temporary table amounts with parent and child accouns period to date balances */
66
67 lc_stmt := 'UPDATE je_parent_child_gt jpcg
68 SET amount = (SELECT sum(nvl(gb.period_net_dr,0)-nvl(gb.period_net_cr,0))
69 FROM gl_balances gb
70 ,gl_code_combinations gcc
71 WHERE gcc.code_combination_id=gb.code_combination_id
72 AND gcc.summary_flag=''N''
73 '||p_bal_seg_filter||'
74 AND gb.template_id is null
75 AND '||gc_access_where||'
76 AND gb.actual_flag=''A''
77 AND gb.ledger_id= :p_ledger_id
78 AND gb.translated_flag is null
79 AND gb.period_name in
80 (SELECT gp.period_name
81 FROM gl_periods gp
82 WHERE gp.period_set_name=:p_period_set_name
83 AND gp.period_type=:p_period_type
84 AND gp.start_date between :gd_start_date
85 AND :gd_end_date)
86 AND jpcg.child_value='||gc_natural_account||'
87 GROUP BY '||gc_natural_account||')';
88
89 EXECUTE IMMEDIATE lc_stmt USING p_ledger_id,p_period_set_name,p_period_type,gd_start_date,gd_end_date;
90
91 lc_stmt := 'UPDATE je_parent_child_gt jpcg
92 SET prior_amount = (SELECT sum(nvl(gb.period_net_dr,0)-nvl(gb.period_net_cr,0))
93 FROM gl_balances gb
94 ,gl_code_combinations gcc
95 WHERE gcc.code_combination_id=gb.code_combination_id
96 AND gcc.summary_flag=''N''
97 '||p_bal_seg_filter||'
98 AND gb.template_id is null
99 AND '||gc_access_where||'
100 AND gb.actual_flag=''A''
101 AND gb.ledger_id=:p_ledger_id
102 AND gb.translated_flag is null
103 AND gb.period_name in
104 (SELECT gp.period_name
105 FROM gl_periods gp
106 WHERE gp.period_set_name=:p_period_set_name
107 AND gp.period_type=:p_period_type
108 AND gp.start_date between :gd_prior_start_date
109 AND :gd_prior_end_date)
110 AND jpcg.child_value='||gc_natural_account||'
111 GROUP BY '||gc_natural_account||')';
112
113 EXECUTE IMMEDIATE lc_stmt USING p_ledger_id,p_period_set_name,p_period_type,gd_prior_start_date,gd_prior_end_date;
114
115 INSERT INTO je_profit_loss_rpt_gt( parent_value
116 ,amount
117 ,prior_amount)
118 SELECT parent_value
119 ,ABS(SUM(amount))
120 ,ABS(SUM(prior_amount))
121 FROM je_parent_child_gt
122 GROUP BY parent_value
123 ORDER BY parent_value;
124
125 /* Amount for the item:Increase in stocks of finished goods and in work in progress */
126
127 SELECT NVL(SUM(jplrg.amount),0)
128 ,NVL(SUM(jplrg.prior_amount),0)
129 INTO ln_cur_amt
130 ,ln_pre_amt
131 FROM je_profit_loss_rpt_gt jplrg
132 WHERE jplrg.parent_value IN (SELECT ffv.flex_value
133 FROM fnd_flex_value_sets ffvs,
134 fnd_flex_values ffv
135 WHERE ffvs.flex_value_set_name = 'JE_PT_PL_INCSTK_FGWP'
136 AND ffvs.flex_value_set_id=ffv.flex_value_set_id );
137
138 /* Amount for the item:Increase in stocks of finished goods and in work in progress */
139 BEGIN
140 SELECT NVL(SUM(jpcg.amount),0)
141 ,NVL(SUM(jpcg.prior_amount),0)
142 INTO ln_amt
143 ,ln_pamt
144 FROM je_parent_child_gt jpcg
145 WHERE jpcg.parent_value IN (SELECT ffv.flex_value
146 FROM fnd_flex_value_sets ffvs,
147 fnd_flex_values ffv
148 WHERE ffvs.flex_value_set_name = 'JE_PT_PL_INCSTCK_FGWP'
149 AND ffvs.flex_value_set_id=ffv.flex_value_set_id)
150 GROUP BY jpcg.parent_value;
151 EXCEPTION
152 WHEN NO_DATA_FOUND THEN
153 ln_amt := NVL(ln_amt,0);
154 ln_pamt := NVL(ln_pamt,0);
155 WHEN OTHERS THEN
156 gc_debug_var :=gc_debug_var||', EXCEPTION IN GETTING BALANCE AMOUNTS FOR THE ITEM: Increase in stocks of finished goods and in work in progress';
157 RAISE;
158 END;
159 gn_cur_amt := ln_cur_amt+ln_amt;
160 gn_pre_amt := ln_pre_amt+ln_pamt;
161 /* Amount for the item:Income Tax */
162 BEGIN
163 SELECT (CASE WHEN p_irc_tax IS NULL THEN
164 (CASE WHEN NVL(jplrg.amount,0)<=0 THEN 0 ELSE jplrg.amount END) ELSE p_irc_tax END)
165 ,(CASE WHEN NVL(jplrg.prior_amount,0)<=0 THEN 0 ELSE jplrg.prior_amount END)
166 INTO gn_cur_tax_amt
167 ,gn_pre_tax_amt
168 FROM je_profit_loss_rpt_gt jplrg
169 WHERE jplrg.parent_value IN (SELECT ffv.flex_value
170 FROM fnd_flex_value_sets ffvs,
171 fnd_flex_values ffv
172 WHERE ffvs.flex_value_set_name = 'JE_PT_PL_INCM_TAX'
173 AND ffvs.flex_value_set_id=ffv.flex_value_set_id);
174 EXCEPTION
175 WHEN NO_DATA_FOUND THEN
176 gn_cur_tax_amt := 0;
177 gn_pre_tax_amt := 0;
178 WHEN OTHERS THEN
179 gc_debug_var :=gc_debug_var||', EXCEPTION IN GETTING BALANCE AMOUNTS FOR THE ITEM: INCOME TAX';
180 RAISE;
181 END;
182
183 EXCEPTION
184 WHEN OTHERS THEN
185 gc_debug_var :=gc_debug_var||', EXCEPTION IN GETTING BALANCE AMOUNTS IN THE PROCEDURE update_amount';
186 RAISE;
187 END update_amount;
188
189 --public function
190
191 FUNCTION beforeReport RETURN BOOLEAN IS
192 lc_period_set_name VARCHAR2(15);
193 lc_period_type VARCHAR2(15);
194 ln_lev NUMBER :=1;
195 /*Cursor to find the parent accounts */
196 CURSOR top_parent_csr(p_flex_value_set_id NUMBER) IS
197 SELECT ffvnh.parent_flex_value parent_flex_value
198 FROM fnd_flex_value_norm_hierarchy ffvnh
199 WHERE ffvnh.flex_value_set_id=p_flex_value_set_id
200 AND NOT EXISTS
201 (SELECT 1
202 FROM fnd_flex_value_norm_hierarchy f1
203 WHERE f1.flex_value_set_id=p_flex_value_set_id
204 AND ffvnh.parent_flex_value BETWEEN f1.child_flex_value_low
205 AND f1.child_flex_value_high)
206 UNION
207 SELECT ffv.flex_value parent_flex_value
208 FROM fnd_flex_values ffv
209 WHERE flex_value_set_id=p_flex_value_set_id
210 AND NOT EXISTS
211 (SELECT 1
212 FROM fnd_flex_value_norm_hierarchy f1
213 WHERE flex_value_set_id=p_flex_value_set_id
214 AND ffv.flex_value BETWEEN f1.child_flex_value_low
215 AND f1.child_flex_value_high);
216
217 BEGIN
218 gc_debug_var :=NULL;
219
220 BEGIN
221 SELECT gps.period_set_name
222 ,gps.start_date
223 ,gpe.end_date
224 ,gps.period_year
225 ,gpps.start_date
226 ,gppe.end_date
227 ,gpps.period_year
228 ,gps.period_type
229 INTO
230 lc_period_set_name
231 ,gd_start_date
232 ,gd_end_date
233 ,gn_curr_year
234 ,gd_prior_start_date
235 ,gd_prior_end_date
236 ,gn_prev_year
237 ,lc_period_type
238 FROM
239 gl_ledgers gll
240 ,gl_periods gps
241 ,gl_periods gpe
242 ,gl_periods gpps
243 ,gl_periods gppe
244 WHERE
245 gll.ledger_id=p_ledger_id
246 AND gps.period_name=p_start_period
247 AND gps.period_set_name=gll.period_set_name
248 AND gpe.period_name=p_end_period
249 AND gpe.period_set_name=gll.period_set_name
250 AND gpps.period_year=gps.period_year-1
251 AND gpps.period_type=gps.period_type
252 AND gpps.period_set_name=gps.period_set_name
253 AND gpps.period_num=gps.period_num
254 AND gppe.period_year=gps.period_year-1
255 AND gppe.period_type=gpe.period_type
256 AND gppe.period_set_name=gpe.period_set_name
257 AND gppe.period_num=gpe.period_num;
258 EXCEPTION
259 WHEN NO_DATA_FOUND THEN
260 gc_debug_var :=gc_debug_var||', NO DATA FOUND EXCEPTION in beforeReport for the given parameters';
261 RAISE;
262 WHEN OTHERS THEN
263 gc_debug_var :=gc_debug_var||', EXCEPTION IN beforeReport for the given parameters';
264 RAISE;
265 END;
266
267 /* Check for parameters */
268 IF p_bal_seg_value = 'T'
269 THEN
270 gc_bal_seg_filter:='AND 1=1';
271 ELSE
272 gc_bal_seg_filter:='AND gcc.'||p_balance_segment||'='||''''||p_bal_seg_value||'''';
273 END IF;
274 gc_natural_account:='gcc.'||p_natural_account;
275 gc_access_where:= GL_ACCESS_SET_SECURITY_PKG.get_security_clause
276 (p_access_set_id,
277 'R',
278 'LEDGER_COLUMN',
279 'LEDGER_ID',
280 'gb',
281 'SEG_COLUMN',
282 NULL,
283 'gcc',
284 NULL);
285 IF gc_access_where IS NULL
286 THEN
287 gc_access_where:= '1 = 1';
288 END IF;
289
290 /* get the value set id for account segment */
291 SELECT fifs.flex_value_set_id
292 INTO gn_flex_value_set_id
293 FROM fnd_id_flex_segments fifs
294 WHERE fifs.application_column_name=p_natural_account
295 AND fifs.application_id=101
296 AND fifs.id_flex_num=p_coa_id
297 AND fifs.id_flex_code='GL#';
298
299 FOR j IN top_parent_csr(gn_flex_value_set_id)
300 LOOP
301 parent_child(p_parent => j.parent_flex_value
302 ,p_summary_flag => 'Y'
303 ,x_lev => ln_lev);
304 ln_lev :=1;
305 END LOOP;
306
307 /* update the amounts for each parent */
308
309 update_amount(p_period_set_name =>lc_period_set_name
310 ,p_period_type =>lc_period_type
311 ,p_bal_seg_filter =>gc_bal_seg_filter);
312
313 COMMIT;
314 RETURN TRUE;
315 EXCEPTION
316 WHEN OTHERS THEN
317 gc_debug_var :=gc_debug_var||', EXCEPTION IN THE PROCEDURE beforeReport';
318 fnd_file.put_line(FND_FILE.LOG,gc_debug_var);
319 RAISE;
320 END beforeReport;
321
322
323 END JE_PT_GL_PFTLS_PKG;
[Home] [Help]