[Home] [Help]
PACKAGE BODY: APPS.JL_BR_CALCBAL_PKG
Source
1 PACKAGE BODY JL_BR_CALCBAL_PKG AS
2 /* $Header: jlbrcblb.pls 120.0 2008/10/23 12:15:04 nivnaray noship $ */
3
4 g_current_runtime_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
6 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
7 g_level_event CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
8 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
9 g_level_error CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
10 g_level_exception CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
11 g_pkg_name CONSTANT VARCHAR2(30) := 'JL_CALCBAL_PKG.';
12 g_module_name_1 VARCHAR2(30) := 'JL.BR.PER_BAL_CALCULATION.';
13 g_module_name_2 VARCHAR2(30) := 'JL.BR.DATA_FIX.';
14
15 Procedure Update_Balances(errbuf Out NOCOPY VARCHAR2
16 ,retcode Out NOCOPY NUMBER
17 ,p_appl_id In NUMBER) Is
18
19 l_appl_id_tbl appl_id_type;
20 l_sob_id_tbl sob_id_type;
21 l_period_set_name_tbl period_set_name_type;
22 l_period_name_tbl period_name_type;
23 l_period_year_tbl period_year_type;
24 l_period_num_tbl period_num_type;
25 l_period_ctr_tbl period_num_type;
26 l_ccid_tbl ccid_type;
27 l_personnel_id_tbl personnel_id_type;
28 l_old_end_bal_tbl end_bal_type;
29 l_old_end_bal_sign_tbl end_bal_sign_type;
30 l_old_act_bal_tbl end_bal_type;
31 l_cur_act_bal_tbl end_bal_type;
32 l_new_end_bal_tbl end_bal_type;
33 l_new_end_bal_sign_tbl end_bal_sign_type;
34 l_bal_error_flag_tbl bal_error_flag_type;
35
36 r_appl_id_tbl appl_id_type;
37 r_sob_id_tbl sob_id_type;
38 r_period_set_name_tbl period_set_name_type;
39 r_period_name_tbl period_name_type;
40 r_period_year_tbl period_year_type;
41 r_period_num_tbl period_num_type;
42 r_period_ctr_tbl period_num_type;
43 r_ccid_tbl ccid_type;
44 r_personnel_id_tbl personnel_id_type;
45 r_old_end_bal_tbl end_bal_type;
46 r_old_end_bal_sign_tbl end_bal_sign_type;
47 r_old_act_bal_tbl end_bal_type;
48 r_cur_act_bal_tbl end_bal_type;
49 r_new_end_bal_tbl end_bal_type;
50 r_new_end_bal_sign_tbl end_bal_sign_type;
51 r_bal_error_flag_tbl bal_error_flag_type;
52
53 l_user NUMBER := fnd_global.user_id;
54 l_login_id NUMBER := fnd_global.login_id;
55 l_min_period_year gl_periods.period_year%TYPE;
56 l_min_period_num gl_periods.period_num%TYPE;
57 l_min_period_ctr NUMBER;
58 l_max_period_year gl_periods.period_year%TYPE;
59 l_max_period_num gl_periods.period_num%TYPE;
60 l_max_period_ctr NUMBER;
61 l_max_bal_perctr NUMBER;
62 l_api_name CONSTANT VARCHAR2(30) := 'Update_Balances';
63
64 CURSOR C_PersonnelCCID Is
65 SELECT jl.application_id appl_id
66 ,jl.org_id org_id
67 ,jl.set_of_books_id sob_id
68 ,jl.personnel_id vendor_id
69 ,jl.code_combination_id ccid
70 ,jl.period_set_name period_set_name
71 ,gp.period_type period_type
72 ,min((gp.period_year * 10000) + gp.period_num) min_jrl_perctr
73 ,max((gp.period_year * 10000) + gp.period_num) max_jrl_perctr
74 FROM jl_br_journals jl
75 ,gl_sets_of_books sb
76 ,gl_periods gp
77 WHERE jl.application_id = p_appl_id
78 AND nvl(jl.journal_balance_flag,'N') = 'N'
79 AND sb.set_of_books_id = jl.set_of_books_id
80 AND sb.period_set_name = jl.period_set_name
81 AND gp.period_set_name = sb.period_set_name
82 AND gp.period_type = sb.accounted_period_type
83 AND gp.period_name = jl.period_name
84 GROUP BY jl.application_id, jl.org_id, jl.set_of_books_id, jl.personnel_id,
85 jl.code_combination_id, jl.period_set_name, gp.period_type;
86
87 CURSOR C_Bal_Periods(p_appl_id In jl_br_balances.application_id%Type
88 ,p_sob In jl_br_balances.set_of_books_id%Type
89 ,p_vendor_id In jl_br_balances.personnel_id%Type
90 ,p_ccid In jl_br_balances.code_combination_id%Type
91 ,p_set_name In jl_br_balances.period_set_name%Type) Is
92 SELECT min((period_year * 10000) + period_num) min_bal_perctr
93 ,max((period_year * 10000) + period_num) max_bal_perctr
94 FROM jl_br_balances jb
95 WHERE jb.application_id = p_appl_id
96 AND jb.set_of_books_id = p_sob
97 AND jb.personnel_id = p_vendor_id
98 AND jb.code_combination_id = p_ccid
99 AND jb.period_set_name = p_set_name;
100
101 CURSOR C_Period_Balances(p_set_name In jl_br_balances.period_set_name%Type
102 ,p_period_type In gl_periods.period_type%Type
103 ,p_min_per_ctr In NUMBER
104 ,p_max_per_ctr In NUMBER
105 ,p_appl_id In jl_br_balances.application_id%Type
106 ,p_sob_id In jl_br_balances.set_of_books_id%Type
107 ,p_vendor_id In jl_br_balances.personnel_id%Type
108 ,p_ccid In jl_br_balances.code_combination_id%Type) Is
109 SELECT nvl(jb.application_id, p_appl_id) appl_id
110 ,nvl(jb.set_of_books_id, p_sob_id) sob_id
111 ,nvl(jb.personnel_id, p_vendor_id) vendor_id
112 ,nvl(jb.code_combination_id, p_ccid) ccid
113 ,nvl(jb.period_set_name, gp.period_set_name) persetname
114 ,nvl(gp.period_name,gp.period_name) pername
115 ,nvl(gp.period_year,gp.period_year) peryear
116 ,nvl(gp.period_num,gp.period_num) pernum
117 ,((gp.period_year*10000) + gp.period_num) perctr
118 ,decode(jb.ending_balance_sign, 'D', -1 * nvl(jb.ending_balance,0), nvl(jb.ending_balance, 0)) ending_balance
119 ,nvl(jb.ending_balance_sign,'N') ending_balance_sign
120 FROM gl_periods gp
121 ,jl_br_balances jb
122 WHERE gp.period_set_name = p_set_name
123 AND gp.period_type = p_period_type
124 AND ((gp.period_year*10000)+ gp.period_num) between p_min_per_ctr and p_max_per_ctr
125 AND gp.adjustment_period_flag = 'N'
126 AND jb.application_id(+) = p_appl_id
127 AND jb.set_of_books_id(+) = p_sob_id
128 AND jb.personnel_id(+) = p_vendor_id
129 AND jb.code_combination_id(+) = p_ccid
130 AND jb.period_set_name(+) = gp.period_set_name
131 AND jb.period_name(+) = gp.period_name
132 ORDER BY ((gp.period_year*10000)+ gp.period_num);
133
134 CURSOR C_Journals(p_appl_id In jl_br_balances.application_id%Type
135 ,p_sob_id In jl_br_balances.set_of_books_id%Type
136 ,p_vendor_id In jl_br_balances.personnel_id%Type
137 ,p_ccid In jl_br_balances.code_combination_id%Type
138 ,p_set_name In jl_br_balances.period_set_name%Type
139 ,p_name In jl_br_balances.period_name%Type) Is
140 SELECT
141 NVL(SUM(DECODE(jb.trans_value_sign,'D',-1*jb.trans_value,jb.trans_value)),0) balance
142 FROM jl_br_journals jb
143 WHERE jb.application_id = p_appl_id
144 AND jb.set_of_books_id = p_sob_id
145 AND jb.personnel_id = p_vendor_id
146 AND jb.code_combination_id = p_ccid
147 AND jb.period_set_name = p_set_name
148 AND jb.period_name = p_name
149 AND NVL(jb.journal_balance_flag,'N') = 'N';
150
151 Procedure Init_Tables_And_Variables Is
152 l_api_name CONSTANT VARCHAR2(30) := 'Init_Tables_And_Variables';
153 Begin
154 If (g_level_procedure >= g_current_runtime_level) Then
155 Fnd_Log.String(g_level_procedure, g_module_name_1 || l_api_name ||'.BEGIN', g_pkg_name || l_api_name || '()+');
156 End If;
157
158 l_appl_id_tbl := r_appl_id_tbl;
159 l_sob_id_tbl := r_sob_id_tbl;
160 l_period_set_name_tbl := r_period_set_name_tbl;
161 l_period_name_tbl := r_period_name_tbl;
162 l_period_year_tbl := r_period_year_tbl;
163 l_period_num_tbl := r_period_num_tbl;
164 l_period_ctr_tbl := r_period_ctr_tbl;
165 l_ccid_tbl := r_ccid_tbl;
166 l_personnel_id_tbl := r_personnel_id_tbl;
167 l_old_end_bal_tbl := r_old_end_bal_tbl;
168 l_old_end_bal_sign_tbl := r_old_end_bal_sign_tbl;
169 l_old_act_bal_tbl := r_old_act_bal_tbl;
170 l_cur_act_bal_tbl := r_cur_act_bal_tbl;
171 l_new_end_bal_tbl := r_new_end_bal_tbl;
172 l_new_end_bal_sign_tbl := r_new_end_bal_sign_tbl;
173 l_bal_error_flag_tbl := r_bal_error_flag_tbl;
174
175 l_min_period_year := NULL;
176 l_min_period_num := NULL;
177 l_min_period_ctr := NULL;
178 l_max_period_year := NULL;
179 l_max_period_num := NULL;
180 l_max_period_ctr := NULL;
181 l_max_bal_perctr := NULL;
182
183 If (g_level_procedure >= g_current_runtime_level) Then
184 Fnd_Log.String(g_level_procedure, g_module_name_1 || l_api_name ||'.END', g_pkg_name || l_api_name || '()-');
185 End If;
186 End Init_Tables_And_Variables;
187 Begin
188 If (g_level_procedure >= g_current_runtime_level) Then
189 Fnd_Log.String(g_level_procedure, g_module_name_1 || l_api_name ||'.BEGIN', g_pkg_name || l_api_name || '()+');
190 End If;
191
192 For PersonnelCCID_Rec In C_PersonnelCCID Loop
193 If (g_level_procedure >= g_current_runtime_level) Then
194 Fnd_Log.String(g_level_procedure, g_module_name_1 || l_api_name,
195 'Processing Personnel ID : ' || PersonnelCCID_Rec.vendor_id ||
196 ' Org ID : ' || PersonnelCCID_Rec.org_id ||
197 ' CCID : ' || PersonnelCCID_Rec.ccid ||
198 ' Min Period Ctr : ' || PersonnelCCID_Rec.min_jrl_perctr ||
199 ' Max Period Ctr : ' || PersonnelCCID_Rec.max_jrl_perctr);
200 End If;
201
202 Init_Tables_And_Variables;
203
204 For Bal_Periods_Rec In C_Bal_Periods(PersonnelCCID_Rec.appl_id
205 ,PersonnelCCID_Rec.sob_id
206 ,PersonnelCCID_Rec.vendor_id
207 ,PersonnelCCID_Rec.ccid
208 ,PersonnelCCID_Rec.period_set_name) Loop
209 If (g_level_procedure >= g_current_runtime_level) Then
210 Fnd_Log.String(g_level_procedure, g_module_name_1 || l_api_name,
211 'Min Period Counter in the Balances Table : ' || Bal_Periods_Rec.min_bal_perctr);
212 Fnd_Log.String(g_level_procedure, g_module_name_1 || l_api_name,
213 'Max Period Counter in the Balances Table : ' || Bal_Periods_Rec.min_bal_perctr);
214 End If;
215
216 If PersonnelCCID_Rec.min_jrl_perctr <= nvl(Bal_Periods_Rec.min_bal_perctr,PersonnelCCID_Rec.min_jrl_perctr) Then
217 l_min_period_year := ((PersonnelCCID_Rec.min_jrl_perctr - Mod(PersonnelCCID_Rec.min_jrl_perctr,10000)) / 10000);
218 l_min_period_num := Mod(PersonnelCCID_Rec.min_jrl_perctr,10000);
219 l_min_period_ctr := PersonnelCCID_Rec.min_jrl_perctr;
220 Else
221 l_min_period_year := ((Bal_Periods_Rec.min_bal_perctr - Mod(Bal_Periods_Rec.min_bal_perctr,10000)) / 10000);
222 l_min_period_num := Mod(Bal_Periods_Rec.min_bal_perctr,10000);
223 l_min_period_ctr := Bal_Periods_Rec.min_bal_perctr;
224 End If;
225
226 If PersonnelCCID_Rec.max_jrl_perctr >= nvl(Bal_Periods_Rec.max_bal_perctr,PersonnelCCID_Rec.max_jrl_perctr) Then
227 l_max_period_year := ((PersonnelCCID_Rec.max_jrl_perctr - Mod(PersonnelCCID_Rec.max_jrl_perctr,10000)) / 10000);
228 l_max_period_num := Mod(PersonnelCCID_Rec.max_jrl_perctr,10000);
229 l_max_period_ctr := PersonnelCCID_Rec.max_jrl_perctr;
230 Else
231 l_max_period_year := ((Bal_Periods_Rec.max_bal_perctr - Mod(Bal_Periods_Rec.max_bal_perctr,10000)) / 10000);
232 l_max_period_num := Mod(Bal_Periods_Rec.max_bal_perctr,10000);
233 l_max_period_ctr := Bal_Periods_Rec.max_bal_perctr;
234 End If;
235
236 l_max_bal_perctr := Bal_Periods_Rec.max_bal_perctr;
237
238 If (g_level_procedure >= g_current_runtime_level) Then
239 Fnd_Log.String(g_level_procedure, g_module_name_1 || l_api_name,
240 'Minimum Period Counter set : ' || l_min_period_ctr);
241 Fnd_Log.String(g_level_procedure, g_module_name_1 || l_api_name,
242 'Maximum Period Counter set : ' || l_max_period_ctr);
243 End If;
244 End Loop; -- End Loop of C_Bal_Periods Cursor
245
246 Open C_Period_Balances(PersonnelCCID_Rec.period_set_name
247 ,PersonnelCCID_Rec.period_type
248 ,l_min_period_ctr
249 ,l_max_period_ctr
250 ,PersonnelCCID_Rec.appl_id
251 ,PersonnelCCID_Rec.sob_id
252 ,PersonnelCCID_Rec.vendor_id
253 ,PersonnelCCID_Rec.ccid);
254 Fetch C_Period_Balances Bulk Collect Into
255 l_appl_id_tbl
256 ,l_sob_id_tbl
257 ,l_personnel_id_tbl
258 ,l_ccid_tbl
259 ,l_period_set_name_tbl
260 ,l_period_name_tbl
261 ,l_period_year_tbl
262 ,l_period_num_tbl
263 ,l_period_ctr_tbl
264 ,l_old_end_bal_tbl
265 ,l_old_end_bal_sign_tbl;
266 Close C_Period_Balances;
267
268 Delete From jl_br_balances
269 Where application_id = PersonnelCCID_Rec.appl_id
270 and set_of_books_id = PersonnelCCID_Rec.sob_id
271 and personnel_id = PersonnelCCID_Rec.vendor_id
272 and code_combination_id = PersonnelCCID_Rec.ccid
273 and period_set_name = PersonnelCCID_Rec.period_set_name
274 and ((period_year*10000) + period_num) between l_min_period_ctr and l_max_period_ctr;
275
276 -- Compute the new balances for the Personnel ID/CCID
277 If l_old_end_bal_tbl.count > 0 Then
278 For i in l_old_end_bal_tbl.First .. l_old_end_bal_tbl.Last Loop
279 -- Compute the previous activity for each period
280 If i = 1 Then
281 l_old_act_bal_tbl(i) := l_old_end_bal_tbl(i);
282 Else
283 -- carry forward the last ending balance in the balances table to the new future periods.
284 If ((l_period_ctr_tbl(i) > nvl(l_max_bal_perctr,l_period_ctr_tbl(i))) or l_old_end_bal_sign_tbl(i) = 'N') Then
285 l_old_end_bal_tbl(i) := l_old_end_bal_tbl(i-1);
286 End If;
287 -- calculate the activity for the period.
288 l_old_act_bal_tbl(i) := l_old_end_bal_tbl(i) - l_old_end_bal_tbl(i-1);
289 End If;
293 ,l_sob_id_tbl(i)
290
291 -- compute the current activity for the period
292 For Journals_Rec In C_Journals(l_appl_id_tbl(i)
294 ,l_personnel_id_tbl(i)
295 ,l_ccid_tbl(i)
296 ,l_period_set_name_tbl(i)
297 ,l_period_name_tbl(i)) Loop
298 l_cur_act_bal_tbl(i) := Journals_Rec.balance;
299
300 If i = 1 Then
301 l_new_end_bal_tbl(i) := l_old_act_bal_tbl(i) + l_cur_act_bal_tbl(i);
302 Else
303 l_new_end_bal_tbl(i) := l_new_end_bal_tbl(i- 1) + l_old_act_bal_tbl(i) + l_cur_act_bal_tbl(i);
304 End If;
305
306 If sign(l_new_end_bal_tbl(i)) = -1 Then
307 l_new_end_bal_sign_tbl(i) := 'D';
308 Else
309 l_new_end_bal_sign_tbl(i) := 'C';
310 End If;
311 End Loop; -- End of C_Journals Loop
312 If (g_level_procedure >= g_current_runtime_level) Then
313 Fnd_Log.String(g_level_procedure, g_module_name_1 || l_api_name,
314 'Balances for Personnel ID : ' || l_personnel_id_tbl(i) || ' CCID : ' || l_ccid_tbl(i));
315 Fnd_Log.String(g_level_procedure, g_module_name_1 || l_api_name,
316 'Appl ID : ' || l_appl_id_tbl(i) || ' Sob ID : ' || l_sob_id_tbl(i));
317 Fnd_Log.String(g_level_procedure, g_module_name_1 || l_api_name,
318 'Period Set Name : ' || l_period_set_name_tbl(i) || ' Period Name : ' || l_period_name_tbl(i));
319 Fnd_Log.String(g_level_procedure, g_module_name_1 || l_api_name,
320 'Period Year : ' || l_period_year_tbl(i) || ' Period Num : ' || l_period_num_tbl(i));
321 Fnd_Log.String(g_level_procedure, g_module_name_1 || l_api_name,
322 'Old End Balance : ' || l_old_end_bal_tbl(i) || ' Old End Balance Sign : ' || l_old_end_bal_sign_tbl(i));
323 Fnd_Log.String(g_level_procedure, g_module_name_1 || l_api_name,
324 'Old Activity Balance : ' || l_old_act_bal_tbl(i));
325 Fnd_Log.String(g_level_procedure, g_module_name_1 || l_api_name,
326 'Current Activity Balance : ' || l_cur_act_bal_tbl(i));
327 Fnd_Log.String(g_level_procedure, g_module_name_1 || l_api_name,
328 'New End Balance : ' || l_new_end_bal_tbl(i) || ' New End Balance Sign : ' || l_new_end_bal_sign_tbl(i));
329 End If;
330 End Loop; -- End of the array processay loop
331 End If; -- End of the array count condition
332
333 -- Insert New Computed Balances since old balances were deleted
334 Forall k in l_period_num_tbl.First .. l_period_num_tbl.Last
335 Insert Into jl_br_balances_all
336 (application_id
337 ,set_of_books_id
338 ,period_set_name
339 ,period_name
340 ,period_year
341 ,period_num
342 ,code_combination_id
343 ,personnel_id
344 ,ending_balance_sign
345 ,ending_balance
346 ,creation_date
347 ,last_update_date
348 ,last_updated_by
349 ,last_update_login
350 ,created_by
351 ,org_id)
352 Values (
353 l_appl_id_tbl(k)
354 ,l_sob_id_tbl(k)
355 ,l_period_set_name_tbl(k)
356 ,l_period_name_tbl(k)
357 ,l_period_year_tbl(k)
358 ,l_period_num_tbl(k)
359 ,l_ccid_tbl(k)
360 ,l_personnel_id_tbl(k)
361 ,l_new_end_bal_sign_tbl(k)
362 ,ABS(l_new_end_bal_tbl(k))
363 ,sysdate
364 ,sysdate
365 ,l_user
366 ,l_login_id
367 ,l_user
368 ,PersonnelCCID_Rec.org_id);
369
370 -- Update Journals
371 Forall l in l_period_num_tbl.First .. l_period_num_tbl.Last
372 Update jl_br_journals
373 Set journal_balance_flag = 'Y'
374 WHERE application_id = l_appl_id_tbl(l)
375 AND set_of_books_id = l_sob_id_tbl(l)
376 AND personnel_id = l_personnel_id_tbl(l)
377 AND code_combination_id = l_ccid_tbl(l)
378 AND period_set_name = l_period_set_name_tbl(l)
379 AND period_name = l_period_name_tbl(l);
380 End Loop; -- End Loop of C_PersonnelCCID Cursor
381
382 retcode := 0;
383 Commit;
384 If (g_level_procedure >= g_current_runtime_level) Then
385 Fnd_Log.String(g_level_procedure, g_module_name_1 || l_api_name ||'.END', g_pkg_name || l_api_name || '()-');
386 End If;
387 Exception
388 When Others Then
389 retcode := 2;
390 errbuf := sqlcode || ' : ' || Substr(sqlerrm,1,80);
391 If (g_level_exception >= g_current_runtime_level) Then
392 Fnd_Log.String(g_level_exception, g_module_name_1 || l_api_name , errbuf);
393 End If;
394 ROLLBACK;
395 End Update_Balances;
396
397 -- Datafix Routine
398 Procedure Rebuild_Balances(errbuf Out NOCOPY VARCHAR2
399 ,retcode Out NOCOPY NUMBER
400 ,p_appl_id In NUMBER
401 ,p_org_id In NUMBER
402 ,p_sob_id In NUMBER
403 ,p_initial_period In VARCHAR2
404 ,p_last_period in VARCHAR2
405 ,p_vendor_id In NUMBER) Is
406
407 l_appl_id_tbl appl_id_type;
408 l_sob_id_tbl sob_id_type;
409 l_period_set_name_tbl period_set_name_type;
410 l_period_name_tbl period_name_type;
411 l_period_year_tbl period_year_type;
412 l_period_num_tbl period_num_type;
413 l_ccid_tbl ccid_type;
414 l_personnel_id_tbl personnel_id_type;
415 l_end_bal_sign_tbl end_bal_sign_type;
416 l_end_bal_tbl end_bal_type;
417 l_bal_error_flag_tbl bal_error_flag_type;
418 l_org_id_tbl org_id_type;
419 l_period_bal_tbl end_bal_type;
420
421 r_appl_id_tbl appl_id_type;
422 r_sob_id_tbl sob_id_type;
423 r_period_set_name_tbl period_set_name_type;
424 r_period_name_tbl period_name_type;
425 r_period_year_tbl period_year_type;
426 r_period_num_tbl period_num_type;
427 r_ccid_tbl ccid_type;
428 r_personnel_id_tbl personnel_id_type;
429 r_end_bal_sign_tbl end_bal_sign_type;
430 r_end_bal_tbl end_bal_type;
431 r_bal_error_flag_tbl bal_error_flag_type;
432 r_org_id_tbl org_id_type;
433 r_period_bal_tbl end_bal_type;
434
435 l_user NUMBER := fnd_global.user_id;
436 l_login_id NUMBER := fnd_global.login_id;
437
438 l_min_period_year gl_periods.period_year%TYPE;
439 l_min_period_num gl_periods.period_num%TYPE;
440 l_min_period_ctr NUMBER;
441 l_max_period_year gl_periods.period_year%TYPE;
442 l_max_period_num gl_periods.period_num%TYPE;
443 l_max_period_ctr NUMBER;
444 l_initial_balance jl_br_balances.ending_balance%TYPE;
445
446 l_api_name CONSTANT VARCHAR2(30) := 'Rebuild_Balances';
447
448 l_invalid_initial_period EXCEPTION;
449 l_invalid_last_period EXCEPTION;
450
451 CURSOR C_Sob Is
452 SELECT sb.set_of_books_id sob_id
453 ,sb.period_set_name period_set_name
454 ,sb.accounted_period_type period_type
455 FROM gl_sets_of_books sb
456 ,(SELECT distinct set_of_books_id
457 FROM jl_br_journals_all jl
458 WHERE jl.set_of_books_id = nvl(p_sob_id, jl.set_of_books_id)) jl_sob
459 WHERE sb.set_of_books_id = jl_sob.set_of_books_id;
460
461 CURSOR C_Period_Ctr(p_period_set_name In gl_sets_of_books.period_set_name%TYPE
462 ,p_period_type In gl_sets_of_books.accounted_period_type%TYPE
463 ,p_period_name In gl_periods.period_name%TYPE) Is
464 SELECT period_year peryear
465 ,period_num pernum
466 ,((period_year * 10000) + period_num) perctr
467 FROM gl_periods
468 WHERE period_set_name = p_period_set_name
469 AND period_type = p_period_type
470 AND period_name = p_period_name;
471
472 CURSOR C_PersonnelCCID(cp_sob_id In jl_br_balances.set_of_books_id%Type) Is
473 SELECT jl.application_id appl_id
474 ,jl.org_id
475 ,jl.personnel_id vendor_id
476 ,jl.code_combination_id ccid
477 FROM jl_br_journals_all jl
478 WHERE jl.application_id = nvl(p_appl_id, jl.application_id)
479 AND jl.org_id = nvl(p_org_id, jl.org_id)
480 AND jl.set_of_books_id = cp_sob_id
481 AND jl.personnel_id = nvl(p_vendor_id, jl.personnel_id)
482 GROUP BY jl.application_id, jl.org_id, jl.personnel_id, jl.code_combination_id
483 UNION
484 SELECT jb.application_id appl_id
485 ,jb.org_id
486 ,jb.personnel_id vendor_id
487 ,jb.code_combination_id ccid
488 FROM jl_br_balances_all jb
489 WHERE jb.application_id = nvl(p_appl_id, jb.application_id)
490 AND jb.org_id = nvl(p_org_id, jb.org_id)
491 AND jb.set_of_books_id = cp_sob_id
492 AND jb.personnel_id = nvl(p_vendor_id, jb.personnel_id)
493 GROUP BY jb.application_id, jb.org_id, jb.personnel_id, jb.code_combination_id;
494
495 CURSOR C_PERIODS(p_set_name In gl_periods.period_set_name%Type
496 ,p_period_type In gl_periods.period_type%Type
497 ,p_min_per_ctr In NUMBER
498 ,p_max_per_ctr In NUMBER) Is
499 SELECT gp.period_set_name persetname
500 ,gp.period_name pername
501 ,gp.period_year peryear
502 ,gp.period_num pernum
503 FROM gl_periods gp
504 WHERE gp.period_set_name = p_set_name
505 AND gp.period_type = p_period_type
506 AND ((gp.period_year*10000)+ gp.period_num) between p_min_per_ctr and p_max_per_ctr
507 AND gp.adjustment_period_flag = 'N'
508 ORDER BY ((gp.period_year*10000)+ gp.period_num);
509
510 CURSOR C_Initial_Bal(cp_appl_id In jl_br_balances.application_id%Type
511 ,cp_org_id In jl_br_balances.org_id%Type
512 ,cp_sob In jl_br_balances.set_of_books_id%Type
513 ,cp_vendor_id In jl_br_balances.personnel_id%Type
514 ,cp_ccid In jl_br_balances.code_combination_id%Type
515 ,cp_set_name In jl_br_balances.period_set_name%Type
516 ,cp_name In jl_br_balances.period_name%Type) Is
517 SELECT
518 NVL(sum(decode(ending_balance_sign,'D', -1 * ending_balance,
519 ending_balance)),0) ending_balance
520 FROM jl_br_balances_all jb
521 WHERE jb.application_id = cp_appl_id
522 AND jb.org_id = cp_org_id
523 AND jb.set_of_books_id = cp_sob
524 AND jb.personnel_id = cp_vendor_id
525 AND jb.code_combination_id = cp_ccid
526 AND jb.period_set_name = cp_set_name
527 AND jb.period_name = cp_name;
528
529 CURSOR C_Journals(cp_appl_id In jl_br_balances.application_id%Type
530 ,cp_org_id In jl_br_balances.org_id%Type
531 ,cp_sob In jl_br_balances.set_of_books_id%Type
532 ,cp_vendor_id In jl_br_balances.personnel_id%Type
533 ,cp_ccid In jl_br_balances.code_combination_id%Type
534 ,cp_set_name In jl_br_balances.period_set_name%Type
535 ,cp_name In jl_br_balances.period_name%Type) Is
536 SELECT
537 NVL(SUM(DECODE(jb.trans_value_sign,'D',-1*jb.trans_value,jb.trans_value)),0) balance
538 FROM jl_br_journals_all jb
539 WHERE jb.application_id = cp_appl_id
540 AND jb.org_id = cp_org_id
541 AND jb.set_of_books_id = cp_sob
542 AND jb.personnel_id = cp_vendor_id
543 AND jb.code_combination_id = cp_ccid
544 AND jb.period_set_name = cp_set_name
545 AND jb.period_name = cp_name;
546
547 Procedure Initialize_Tables Is
548 l_api_name CONSTANT VARCHAR2(30) := 'Initialize_Tables';
549 Begin
550 If (g_level_procedure >= g_current_runtime_level) Then
551 Fnd_Log.String(g_level_procedure, g_module_name_2 || l_api_name ||'.BEGIN', g_pkg_name || l_api_name || '()+');
552 End If;
553
554 l_appl_id_tbl := r_appl_id_tbl;
555 l_sob_id_tbl := r_sob_id_tbl;
556 l_period_set_name_tbl := r_period_set_name_tbl;
557 l_period_name_tbl := r_period_name_tbl;
558 l_period_year_tbl := r_period_year_tbl;
559 l_period_num_tbl := r_period_num_tbl;
560 l_ccid_tbl := r_ccid_tbl;
561 l_personnel_id_tbl := r_personnel_id_tbl;
562 l_end_bal_sign_tbl := r_end_bal_sign_tbl;
563 l_end_bal_tbl := r_end_bal_tbl;
564 l_bal_error_flag_tbl := r_bal_error_flag_tbl;
565 l_org_id_tbl := r_org_id_tbl;
566 l_period_bal_tbl := r_end_bal_tbl;
567
568 If (g_level_procedure >= g_current_runtime_level) Then
569 Fnd_Log.String(g_level_procedure, g_module_name_2 || l_api_name ||'.END', g_pkg_name || l_api_name || '()-');
570 End If;
571 End Initialize_Tables;
572 Begin
573 If (g_level_procedure >= g_current_runtime_level) Then
574 Fnd_Log.String(g_level_procedure, g_module_name_2 || l_api_name ||'.BEGIN', g_pkg_name || l_api_name || '()+');
575 End If;
576
577 For Sob_Rec in C_SOB Loop
578 If (g_level_procedure >= g_current_runtime_level) Then
579 Fnd_Log.String(g_level_procedure, g_module_name_2 || l_api_name,
580 'Processing Set of Books ID : ' || Sob_Rec.sob_id ||
581 ' Period Set Name : ' || Sob_Rec.period_set_name ||
582 ' Period Type : ' || Sob_Rec.period_type);
583 End If;
584
585 l_min_period_year := NULL;
586 l_min_period_num := NULL;
587 l_min_period_ctr := NULL;
588
589 For Period_Ctr_Rec In C_Period_Ctr(Sob_Rec.period_set_name
590 ,Sob_Rec.period_type
591 ,p_initial_period) Loop
592 l_min_period_year := Period_Ctr_Rec.peryear;
593 l_min_period_num := Period_Ctr_Rec.pernum;
594 l_min_period_ctr := Period_Ctr_Rec.perctr;
595 End Loop;
596
597 If l_min_period_num is null Then
598 raise l_invalid_initial_period;
599 End If;
600
601 If (g_level_procedure >= g_current_runtime_level) Then
602 Fnd_Log.String(g_level_procedure, g_module_name_2 || l_api_name,
603 'Min Period Counter : ' || l_min_period_ctr);
604 End If;
605
606 l_max_period_year := NULL;
607 l_max_period_num := NULL;
608 l_max_period_ctr := NULL;
609
610 For Period_Ctr_Rec In C_Period_Ctr(Sob_Rec.period_set_name
611 ,Sob_Rec.period_type
612 ,p_last_period) Loop
613 l_max_period_year := Period_Ctr_Rec.peryear;
614 l_max_period_num := Period_Ctr_Rec.pernum;
615 l_max_period_ctr := Period_Ctr_Rec.perctr;
616 End Loop;
617
618 If l_max_period_num is null Then
619 raise l_invalid_last_period;
620 End If;
621
622 If (g_level_procedure >= g_current_runtime_level) Then
623 Fnd_Log.String(g_level_procedure, g_module_name_2 || l_api_name,
624 'Max Period Counter : ' || l_max_period_ctr);
625 End If;
626
627 -- Purge the Balances Tables
628
629 Delete from JL_BR_BALANCES_ALL
630 Where application_id = nvl(p_appl_id, application_id)
631 and org_id = nvl(p_org_id, org_id)
632 and set_of_books_id = Sob_Rec.sob_id
633 and ((period_year*10000) + period_num) > l_min_period_ctr
634 and personnel_id = nvl(p_vendor_id, personnel_id);
635
636 Initialize_Tables;
637
638 For PersonnelCCID_Rec In C_PersonnelCCID(Sob_Rec.sob_id) Loop
639 If (g_level_procedure >= g_current_runtime_level) Then
640 Fnd_Log.String(g_level_procedure, g_module_name_2 || l_api_name,
641 'Processing Personnel ID : ' || PersonnelCCID_Rec.vendor_id ||
642 ' Appl ID : ' || PersonnelCCID_Rec.appl_id ||
643 ' Org ID : ' || PersonnelCCID_Rec.org_id ||
644 ' CCID : ' || PersonnelCCID_Rec.ccid);
645 End If;
646
647 Open C_Periods(Sob_Rec.period_set_name
648 ,Sob_Rec.period_type
649 ,l_min_period_ctr+1
650 ,l_max_period_ctr);
651 Fetch C_Periods Bulk Collect Into
652 l_period_set_name_tbl
653 ,l_period_name_tbl
654 ,l_period_year_tbl
655 ,l_period_num_tbl;
656 Close C_Periods;
657
658 If l_period_num_tbl.count > 0 Then
659
660 l_initial_balance := NULL;
661
662 For i in l_period_num_tbl.First .. l_period_num_tbl.Last Loop
663 l_appl_id_tbl(i) := PersonnelCCID_Rec.appl_id;
664 l_sob_id_tbl(i) := Sob_Rec.sob_id;
665 l_ccid_tbl(i) := PersonnelCCID_Rec.ccid;
666 l_personnel_id_tbl(i) := PersonnelCCID_Rec.vendor_id;
667 l_org_id_tbl(i) := PersonnelCCID_Rec.org_id;
668 l_bal_error_flag_tbl(i) := NULL;
669
670 If (g_level_procedure >= g_current_runtime_level) Then
671 Fnd_Log.String(g_level_procedure, g_module_name_2 || l_api_name,
672 'Period index : ' || i ||
673 ' Period Year : ' || l_period_year_tbl(i) ||
674 ' Period Num : ' || l_period_num_tbl(i) ||
675 ' Period Name : ' || l_period_name_tbl(i));
676 End If;
677
678 If i = 1 Then
679 For Initial_Bal In C_Initial_Bal(l_appl_id_tbl(i)
680 ,l_org_id_tbl(i)
681 ,l_sob_id_tbl(i)
682 ,l_personnel_id_tbl(i)
683 ,l_ccid_tbl(i)
684 ,l_period_set_name_tbl(i)
685 ,p_initial_period) Loop
686 l_initial_balance := Initial_Bal.ending_balance;
687 If (g_level_procedure >= g_current_runtime_level) Then
688 Fnd_Log.String(g_level_procedure, g_module_name_2 || l_api_name,
689 'Initial Balance : ' || l_initial_balance);
690 End If;
691 End Loop;
692 End If;
693
694 For Journals_Rec In C_Journals(l_appl_id_tbl(i)
695 ,l_org_id_tbl(i)
696 ,l_sob_id_tbl(i)
697 ,l_personnel_id_tbl(i)
698 ,l_ccid_tbl(i)
699 ,l_period_set_name_tbl(i)
700 ,l_period_name_tbl(i)) Loop
701
702 l_period_bal_tbl(i) := Journals_Rec.balance;
703
704 If (g_level_procedure >= g_current_runtime_level) Then
705 Fnd_Log.String(g_level_procedure, g_module_name_2 || l_api_name,
706 'Activity : ' || l_period_bal_tbl(i));
707 End If;
708
709 If i = 1 Then
710 l_end_bal_tbl(i) := l_period_bal_tbl(i) + l_initial_balance;
711 Else
712 l_end_bal_tbl(i) := l_end_bal_tbl(i- 1) + l_period_bal_tbl(i);
713 End If;
714
715 If sign(l_end_bal_tbl(i)) = -1 Then
716 l_end_bal_sign_tbl(i) := 'D';
717 Else
718 l_end_bal_sign_tbl(i) := 'C';
719 End If;
720
721 If (g_level_procedure >= g_current_runtime_level) Then
722 Fnd_Log.String(g_level_procedure, g_module_name_2 || l_api_name,
723 'Ending Balance : ' || l_end_bal_tbl(i) ||
724 ' Ending Balance Sign : ' || l_end_bal_sign_tbl(i));
725 End If;
726 End Loop; -- End Loop of C_Journals
727
728 End Loop; -- End Loop of Periods Table
729
730 -- Insert Balances
731 Forall j in l_period_num_tbl.First .. l_period_num_tbl.Last
732 Insert Into jl_br_balances_all
733 (application_id
734 ,set_of_books_id
735 ,period_set_name
736 ,period_name
737 ,period_year
738 ,period_num
739 ,code_combination_id
740 ,personnel_id
741 ,ending_balance_sign
742 ,ending_balance
743 ,creation_date
744 ,last_update_date
745 ,last_updated_by
746 ,last_update_login
747 ,created_by
748 ,org_id)
749 Values (l_appl_id_tbl(j)
750 ,l_sob_id_tbl(j)
751 ,l_period_set_name_tbl(j)
752 ,l_period_name_tbl(j)
753 ,l_period_year_tbl(j)
754 ,l_period_num_tbl(j)
755 ,l_ccid_tbl(j)
756 ,l_personnel_id_tbl(j)
757 ,l_end_bal_sign_tbl(j)
758 ,ABS(l_end_bal_tbl(j))
759 ,sysdate
760 ,sysdate
761 ,l_user
762 ,l_login_id
763 ,l_user
764 ,l_org_id_tbl(j));
765
766 -- Update Journals
767 Forall k in l_period_num_tbl.First .. l_period_num_tbl.Last
768 Update jl_br_journals_all
769 Set journal_balance_flag = 'Y'
770 WHERE application_id = l_appl_id_tbl(k)
771 AND org_id = l_org_id_tbl(k)
772 AND set_of_books_id = l_sob_id_tbl(k)
773 AND personnel_id = l_personnel_id_tbl(k)
774 AND code_combination_id = l_ccid_tbl(k)
775 AND period_set_name = l_period_set_name_tbl(k)
776 AND period_name = l_period_name_tbl(k);
777
778 End If; -- End if of checking for existence of Periods
779 End Loop; -- End Loop of C_PersonnelCCID Cursor
780 End Loop; -- End Loop of C_SOB Cursor
781 retcode := 0;
782 Commit;
783
784 If (g_level_procedure >= g_current_runtime_level) Then
785 Fnd_Log.String(g_level_procedure, g_module_name_2 || l_api_name ||'.END', g_pkg_name || l_api_name || '()-');
786 End If;
787 Exception
788 When l_invalid_initial_period Then
789 errbuf := 'Program aborted due to invalid initial period';
790 If (g_level_exception >= g_current_runtime_level) Then
791 Fnd_Log.String(g_level_exception, g_module_name_2 || l_api_name , errbuf);
792 End If;
793 retcode := 2;
794 ROLLBACK;
795 When l_invalid_last_period Then
796 errbuf := 'Program aborted due to invalid last period';
797 If (g_level_exception >= g_current_runtime_level) Then
798 Fnd_Log.String(g_level_exception, g_module_name_2 || l_api_name , errbuf);
799 End If;
800 retcode := 2;
801 ROLLBACK;
802 When Others Then
803 errbuf := 'Program aborted due to error : ' || sqlcode || ': ' || Substr(sqlerrm,1,80);
804 If (g_level_exception >= g_current_runtime_level) Then
805 Fnd_Log.String(g_level_exception, g_module_name_2 || l_api_name , errbuf);
806 End If;
807 retcode := 2;
808 ROLLBACK;
809 End Rebuild_Balances;
810
811 END JL_BR_CALCBAL_PKG;