[Home] [Help]
PACKAGE BODY: APPS.JL_BR_AR_BALANCE_MAINTENANCE
Source
1 package body JL_BR_AR_BALANCE_MAINTENANCE as
2 /* $Header: jlbrrbmb.pls 120.5 2005/04/07 18:36:12 appradha ship $ */
3
4 /*----------------------------------------------------------------------------*
5 | PUBLIC FUNCTIONS/PROCEDURES |
6 *----------------------------------------------------------------------------*/
7
8 /*----------------------------------------------------------------------------*
9 | PROCEDURE |
10 | JL_BR_AR_BAL_MAINTENANCE |
11 | |
12 | DESCRIPTION |
13 | |
14 | PARAMETERS |
15 | INPUT |
16 | par_posting_control_id Number -- Posting Control Id |
17 | |
18 | OUTPUT |
19 | |
20 | |
21 | HISTORY |
22 | 28-Aug-97 Aniz Buissa Jr. Created |
23 *----------------------------------------------------------------------------*/
24
25 /*-----------------------------------------------------------*/
26 /*<<<<< JL_BR_AR_BAL_MAINTENANCE >>>>>*/
27 /*-----------------------------------------------------------*/
28 PROCEDURE JL_BR_AR_BAL_MAINTENANCE (
29 par_posting_control_id IN NUMBER) IS
30
31 pl_period_num number;
32 pl_sob number;
33 pl_per varchar2(15);
34 pl_per_set varchar2(15);
35 pl_min_pyear number;
36 pl_max_pyear number;
37 pl_pyear number;
38 pl_min_pnum number;
39 pl_max_pnum number;
40 pl_pnum number;
41 pl_ccid number;
42 pl_cust number;
43 pl_sign varchar2(1);
44 pl_val number;
45 pl_ival2 number;
46 pl_user number := 9999;
47
48 cursor c_bmb is
49 SELECT /*+ ORDERED */
50 racust0.set_of_books_id sob, gldist0.code_combination_id ccid,
51 r0.period_year pyear, r0.period_num pnum,
52 s0.period_set_name perset,
53 gldist0.gl_date accd,
54 racust0.bill_to_customer_id cust,
55 racust0.trx_number num,
56 racust0.customer_trx_id invid,
57 racust0.trx_date idat,
58 racust0.invoice_currency_code cur,
59 gldist0.cust_trx_line_gl_dist_id inst,
60 -- bug 2054372
61 -- decode(type0.type,'CM',
62 -- decode(sign(nvl(gldist0.acctd_amount,gldist0.amount)),-1,
63 -- decode(gldist0.account_class,'REV',
64 -- 'D','C'),
65 -- decode(gldist0.account_class,'REC',
66 -- 'D','C')),
67 -- decode(gldist0.account_class,'REC',
68 -- 'D','C' ) ) isign,
69 decode(type0.type,'CM',
70 decode(sign(nvl(gldist0.acctd_amount,gldist0.amount)),-1,
71 decode(gldist0.account_class,'REC','C','REV','D','UNEARN','D','D'),1,
72 decode(gldist0.account_class,'REC','D','REV','C','UNEARN','C','C'),0,'C'),
73 -- DM
74 decode(sign(nvl(gldist0.acctd_amount,gldist0.amount)),-1,
75 decode(gldist0.account_class,'REC','C','REV','D','UNEARN','D','D'),1,
76 decode(gldist0.account_class,'REC','D','REV','C','UNEARN','C','C'),0,'C')) isign,
77 abs(nvl(gldist0.acctd_amount,nvl(gldist0.amount,0))) ival,
78 glps0.period_name per,
79 decode(type0.type,'CM','Nota de Credito','Nota de Debito') hist,
80 batch0.name bat, batch0.batch_id batid,
81 racust0.org_id
82 FROM ra_cust_trx_line_gl_dist gldist0,
83 ra_customer_trx_all racust0,
84 hz_cust_accounts_all hzcus0,
85 ra_cust_trx_types_all type0,
86 gl_period_statuses glps0,
87 ra_batches_all batch0,
88 gl_sets_of_books s0,
89 gl_periods r0
90 WHERE gldist0.posting_control_id = par_posting_control_id
91 and gldist0.gl_date is not null
92 and racust0.bill_to_customer_id = hzcus0.cust_account_id
93 and gldist0.customer_trx_id = racust0.customer_trx_id
94 and type0.type <>'INV'
95 and racust0.cust_trx_type_id = type0.cust_trx_type_id
96 and glps0.application_id = 222
97 and racust0.set_of_books_id = glps0.set_of_books_id
98 and gldist0.gl_date between glps0.start_date and glps0.end_date
99 and racust0.set_of_books_id = s0.set_of_books_id
100 and s0.period_set_name = r0.period_set_name
101 and glps0.period_name = r0.period_name
102 and racust0.batch_id = batch0.batch_id (+)
103 UNION ALL
104 SELECT /*+ ORDERED */
105 racust1.set_of_books_id sob, gldist1.code_combination_id ccid,
106 r1.period_year pyear, r1.period_num pnum,
107 s1.period_set_name perset,
108 gldist1.gl_date accd,
109 racust1.bill_to_customer_id cust,
110 racust1.trx_number num,
111 racust1.customer_trx_id invid,
112 racust1.trx_date idat,
113 racust1.invoice_currency_code cur,
114 gldist1.cust_trx_line_gl_dist_id inst,
115 -- bug 2054372
116 -- decode(gldist1.account_class,'REC',
117 -- 'D','C') isign,
118 decode(sign(nvl(gldist1.acctd_amount,gldist1.amount)),-1,
119 decode(gldist1.account_class,'REC','C','REV','D','UNEARN','D','D'),1,
120 decode(gldist1.account_class,'REC','D','REV','C','UNEARN','C','C'),0,'C') isign,
121 abs(nvl(gldist1.acctd_amount,nvl(gldist1.amount,0))) ival,
122 glps1.period_name per,
123 'Entrada de Dcto' hist,
124 batch1.name bat, batch1.batch_id batid,
125 racust1.org_id
126 FROM ra_cust_trx_line_gl_dist gldist1,
127 ra_customer_trx_all racust1,
128 hz_cust_accounts_all hzcus1,
129 ra_cust_trx_types_all type1,
130 gl_period_statuses glps1,
131 ra_batches_all batch1,
132 gl_sets_of_books s1,
133 gl_periods r1
134 WHERE gldist1.posting_control_id = par_posting_control_id
135 and gldist1.gl_date is not null
136 and gldist1.customer_trx_id = racust1.customer_trx_id
137 and racust1.bill_to_customer_id = hzcus1.cust_account_id
138 and racust1.cust_trx_type_id = type1.cust_trx_type_id
139 and type1.type = 'INV'
140 and glps1.application_id = 222
141 and glps1.set_of_books_id = racust1.set_of_books_id
142 and racust1.trx_date between glps1.start_date and glps1.end_date
143 and racust1.complete_flag = 'Y'
144 and racust1.batch_id = batch1.batch_id (+)
145 and racust1.set_of_books_id = s1.set_of_books_id
146 and s1.period_set_name = r1.period_set_name
147 and glps1.period_name = r1.period_name
148 UNION ALL
149 SELECT /*+ ORDERED */
150 recapp2.set_of_books_id sob, recapp2.code_combination_id ccid,
151 r2.period_year pyear, r2.period_num pnum,
152 s2.period_set_name perset,
153 recapp2.gl_date accd,
154 cash2.pay_from_customer cust,
155 to_char(cash2.cash_receipt_id) num,
156 cash2.cash_receipt_id invid,
157 recapp2.apply_date idat,
158 cash2.currency_code cur,
159 recapp2.receivable_application_id inst,
160 decode(sign(recapp2.acctd_amount_applied_from),-1,'D','C') isign,
161 abs(recapp2.acctd_amount_applied_from) ival,
162 glps2.period_name per,
163 'Receb. Dcto (Aplicado)' hist,
164 batch2.name bat, batch2.batch_id batid,
165 cash2.org_id
166 FROM ar_receivable_applications recapp2,
167 ar_cash_receipts_all cash2,
168 hz_cust_accounts_all hzcus2,
169 gl_sets_of_books s2,
170 gl_periods r2,
171 gl_period_statuses glps2,
172 ra_batches_all batch2
173 WHERE recapp2.posting_control_id = par_posting_control_id
174 and cash2.cash_receipt_id = recapp2.cash_receipt_id
175 and cash2.pay_from_customer = hzcus2.cust_account_id
176 and cash2.selected_remittance_batch_id = batch2.batch_id (+)
177 and recapp2.gl_date between glps2.start_date and glps2.end_date
178 and glps2.application_id = 222
179 and cash2.set_of_books_id = glps2.set_of_books_id
180 and cash2.set_of_books_id = s2.set_of_books_id
181 and s2.period_set_name = r2.period_set_name
182 and glps2.period_name = r2.period_name
183 UNION ALL
184 SELECT /*+ ORDERED */
185 recapp3.set_of_books_id sob, recapp3.code_combination_id ccid,
186 r3.period_year pyear, r3.period_num pnum,
187 s3.period_set_name perset,
188 recapp3.gl_date accd,
189 inv3.bill_to_customer_id cust,
190 cm3.trx_number num,
191 cm3.customer_trx_id invid,
192 recapp3.apply_date idat,
193 inv3.invoice_currency_code cur,
194 recapp3.receivable_application_id inst,
195 decode(sign(recapp3.acctd_amount_applied_from),-1,'D','C') isign,
196 abs(recapp3.acctd_amount_applied_from) ival,
197 glps3.period_name per,
198 'Nota de Cred (Aplic.)' hist,
199 batch3.name bat, batch3.batch_id batid,
200 inv3.org_id
201 FROM ar_receivable_applications recapp3,
202 ra_customer_trx_all cm3,
203 ra_customer_trx_all inv3,
204 hz_cust_accounts_all hzcus3,
205 ra_batches_all batch3,
206 gl_period_statuses glps3,
207 gl_periods r3,
208 gl_sets_of_books s3
209 WHERE recapp3.posting_control_id = par_posting_control_id
210 and recapp3.customer_trx_id = cm3.customer_trx_id
211 and recapp3.applied_customer_trx_id = inv3.customer_trx_id
212 and inv3.bill_to_customer_id = hzcus3.cust_account_id
213 and cm3.batch_id = batch3.batch_id (+)
214 and recapp3.gl_date between glps3.start_date and glps3.end_date
215 and inv3.set_of_books_id = s3.set_of_books_id
216 and glps3.application_id = 222
217 and inv3.set_of_books_id = glps3.set_of_books_id
218 and s3.period_set_name = r3.period_set_name
219 and glps3.period_name = r3.period_name
220 UNION ALL
221 SELECT /*+ ORDERED */
222 misc4.set_of_books_id sob, misc4.code_combination_id ccid,
223 r4.period_year pyear, r4.period_num pnum,
224 s4.period_set_name perset,
225 misc4.gl_date accd,
226 cash4.pay_from_customer cust,
227 to_char(cash4.cash_receipt_id) num,
228 cash4.cash_receipt_id invid,
229 misc4.apply_date idat,
230 cash4.currency_code cur,
231 misc4.misc_cash_distribution_id inst,
232 decode(sign(misc4.acctd_amount),-1,'D','C') isign,
233 abs(nvl(misc4.acctd_amount,0)) ival,
234 glps4.period_name per,
235 'Receb Dcto (Nao Aplic)' hist,
236 batch4.name bat, batch4.batch_id batid,
237 cash4.org_id
238 FROM ar_misc_cash_distributions misc4,
239 ar_cash_receipts_all cash4,
240 hz_cust_accounts_all hzcus4,
241 ra_batches_all batch4,
242 gl_sets_of_books s4,
243 gl_periods r4,
244 gl_period_statuses glps4
245 WHERE misc4.posting_control_id = par_posting_control_id
246 and misc4.cash_receipt_id = cash4.cash_receipt_id
247 and cash4.pay_from_customer is not null
248 and cash4.pay_from_customer = hzcus4.cust_account_id
249 and cash4.selected_remittance_batch_id = batch4.batch_id (+)
250 and misc4.gl_date between glps4.start_date and glps4.end_date
251 and misc4.set_of_books_id = glps4.set_of_books_id
252 and glps4.application_id = 222
253 and misc4.set_of_books_id = s4.set_of_books_id
254 and s4.period_set_name = r4.period_set_name
255 and glps4.period_name = r4.period_name
256 UNION ALL
257 SELECT /*+ ORDERED */
258 adj5.set_of_books_id sob, dis5.code_combination_id ccid,
259 r5.period_year pyear, r5.period_num pnum,
260 s5.period_set_name perset,
261 adj5.gl_date accd,
262 racust5.bill_to_customer_id cust,
263 adj5.adjustment_number num,
264 adj5.adjustment_id invid,
265 adj5.apply_date idat,
266 racust5.invoice_currency_code cur,
267 adj5.adjustment_id inst,
268 decode (dis5.acctd_amount_dr, NULL, 'C', 'D') isign,
269 nvl (dis5.acctd_amount_dr, dis5.acctd_amount_cr) ival,
270 glps5.period_name per,
271 'Ajuste' hist,
272 batch5.name bat, batch5.batch_id batid,
273 racust5.org_id
274 FROM ar_adjustments adj5,
275 ra_customer_trx_all racust5,
276 hz_cust_accounts_all hzcus5,
277 ra_batches_all batch5,
278 gl_sets_of_books s5,
279 gl_periods r5,
280 gl_period_statuses glps5,
281 ar_distributions_all dis5
282 WHERE adj5.posting_control_id = par_posting_control_id
283 and adj5.customer_trx_id = racust5.customer_trx_id
284 and racust5.bill_to_customer_id = hzcus5.cust_account_id
285 and racust5.batch_id = batch5.batch_id (+)
286 and adj5.set_of_books_id = glps5.set_of_books_id
287 and glps5.application_id = 222
288 and adj5.gl_date between glps5.start_date and glps5.end_date
289 and adj5.set_of_books_id = s5.set_of_books_id
290 and s5.period_set_name = r5.period_set_name
291 and glps5.period_name = r5.period_name
292 and adj5.adjustment_id = dis5.source_id
293 and dis5.source_type = 'ADJ'
294 UNION ALL
295 SELECT /*+ ORDERED */
296 cash6.set_of_books_id sob, hist6.account_code_combination_id ccid,
297 r6.period_year pyear, r6.period_num pnum,
298 s6.period_set_name perset,
299 hist6.gl_date accd,
300 cash6.pay_from_customer cust,
301 to_char(cash6.cash_receipt_id) num,
302 cash6.cash_receipt_id invid,
303 hist6.trx_date idat,
304 cash6.currency_code cur,
305 hist6.cash_receipt_history_id inst,
306 decode(sign(hist6.acctd_amount),1,'D','C') isign,
307 abs(nvl(hist6.acctd_amount,0)) ival,
308 glps6.period_name per,
309 'Recebim de Dcto' hist,
310 batch6.name bat, batch6.batch_id batid,
311 cash6.org_id
312 FROM ar_cash_receipt_history hist6,
313 ar_cash_receipts_all cash6,
314 hz_cust_accounts_all hzcus6,
315 ra_batches_all batch6,
316 gl_sets_of_books s6,
317 gl_periods r6,
318 gl_period_statuses glps6
319 WHERE hist6.posting_control_id = par_posting_control_id
320 and hist6.status = 'CLEARED'
321 and hist6.cash_receipt_id = cash6.cash_receipt_id
322 and cash6.pay_from_customer is not null
323 and cash6.pay_from_customer = hzcus6.cust_account_id
324 and cash6.selected_remittance_batch_id = batch6.batch_id (+)
325 and cash6.set_of_books_id = glps6.set_of_books_id
326 and glps6.application_id = 222
327 and hist6.gl_date between glps6.start_date and glps6.end_date
328 and cash6.set_of_books_id = s6.set_of_books_id
329 and s6.period_set_name = r6.period_set_name
330 and glps6.period_name = r6.period_name
331 UNION ALL
332 SELECT /*+ ORDERED */
333 cash6b.set_of_books_id sob, hist6b.account_code_combination_id ccid,
334 r6b.period_year pyear, r6b.period_num pnum,
335 s6b.period_set_name perset,
336 hist6b.gl_date accd,
337 cash6b.pay_from_customer cust,
338 to_char(cash6b.cash_receipt_id) num,
339 cash6b.cash_receipt_id invid,
340 hist6b.trx_date idat,
341 cash6b.currency_code cur,
342 hist6b.cash_receipt_history_id inst,
343 decode(sign(hist6b.acctd_amount),-1,'D','C') isign,
344 abs(nvl(hist6b.acctd_amount,0)) ival,
345 glps6b.period_name per,
346 'Recebim. Revertido' hist,
347 batch6b.name bat, batch6b.batch_id batid,
348 cash6b.org_id
349 FROM ar_cash_receipt_history hist6b,
350 ar_cash_receipts_all cash6b,
351 hz_cust_accounts_all hzcus6b,
352 gl_period_statuses glps6b,
353 ra_batches_all batch6b,
354 gl_periods r6b,
355 gl_sets_of_books s6b
356 WHERE hist6b.posting_control_id = par_posting_control_id
357 and hist6b.status = 'CLEARED'
358 and hist6b.cash_receipt_id = cash6b.cash_receipt_id
359 and cash6b.status = 'REV'
360 and cash6b.pay_from_customer is not null
361 and cash6b.pay_from_customer = hzcus6b.cust_account_id
362 and glps6b.application_id = 222
363 and cash6b.set_of_books_id = glps6b.set_of_books_id
364 and hist6b.gl_date between glps6b.start_date and glps6b.end_date
365 and cash6b.selected_remittance_batch_id = batch6b.batch_id (+)
366 and cash6b.set_of_books_id = s6b.set_of_books_id
367 and s6b.period_set_name = r6b.period_set_name
368 and glps6b.period_name = r6b.period_name
369 UNION ALL
370 SELECT /*+ ORDERED */
371 gldist7.set_of_books_id sob, gldist7.code_combination_id ccid,
372 r7.period_year pyear, r7.period_num pnum,
373 s7.period_set_name perset,
374 recapp7.gl_date accd,
375 inv7.bill_to_customer_id cust,
376 cm7.trx_number num,
377 cm7.customer_trx_id invid,
378 recapp7.apply_date idat,
379 inv7.invoice_currency_code cur,
380 recapp7.receivable_application_id inst,
381 decode(sign(recapp7.acctd_amount_applied_from),-1,'C','D') isign, --Bug 3934716
382 abs(recapp7.acctd_amount_applied_from) ival,
383 glps7.period_name per,
384 'Nota Cred (Cta Receb)' hist,
385 batch7.name bat, batch7.batch_id batid,
386 inv7.org_id
387 FROM ra_cust_trx_line_gl_dist gldist7,
388 ra_customer_trx_all cm7,
389 ar_receivable_applications_all recapp7,
390 ra_customer_trx_all inv7,
391 hz_cust_accounts_all hzcus7,
392 ra_batches_all batch7,
393 gl_period_statuses glps7,
394 gl_sets_of_books s7,
395 gl_periods r7
396 WHERE gldist7.posting_control_id = par_posting_control_id
397 and gldist7.account_class = 'REC'
398 and gldist7.customer_trx_id = cm7.customer_trx_id
399 and cm7.customer_trx_id = recapp7.customer_trx_id
400 and recapp7.applied_customer_trx_id = inv7.customer_trx_id
401 and inv7.bill_to_customer_id = hzcus7.cust_account_id
402 and cm7.batch_id = batch7.batch_id (+)
403 and glps7.application_id = 222
404 and inv7.set_of_books_id = glps7.set_of_books_id
405 and recapp7.gl_date between glps7.start_date and glps7.end_date
406 and inv7.set_of_books_id = s7.set_of_books_id
407 and s7.period_set_name = r7.period_set_name
408 and glps7.period_name = r7.period_name
409 UNION ALL
410 SELECT /*+ ORDERED */
411 gldist8.set_of_books_id sob, gldist8.code_combination_id ccid,
412 r8.period_year pyear, r8.period_num pnum,
413 s8.period_set_name perset,
414 adj8.gl_date accd,
415 racust8.bill_to_customer_id cust,
416 adj8.adjustment_number num,
417 adj8.adjustment_id invid,
418 adj8.apply_date idat,
419 racust8.invoice_currency_code cur,
420 gldist8.cust_trx_line_gl_dist_id inst,
421 decode (dis8.acctd_amount_dr, NULL, 'C', 'D') isign,
422 nvl (dis8.acctd_amount_dr, dis8.acctd_amount_cr) ival,
423 glps8.period_name per,
424 'Ajuste (Conta Recebim)' hist,
425 batch8.name bat, batch8.batch_id batid,
426 racust8.org_id
427 FROM ra_cust_trx_line_gl_dist gldist8,
428 ar_adjustments_all adj8,
429 ra_customer_trx_all racust8,
430 hz_cust_accounts_all hzcus8,
431 ra_batches_all batch8,
432 gl_sets_of_books s8,
433 gl_period_statuses glps8,
434 gl_periods r8,
435 ar_distributions_all dis8
436 WHERE gldist8.posting_control_id = par_posting_control_id
437 and gldist8.account_class = 'REC'
438 and gldist8.customer_trx_id = adj8.customer_trx_id
439 and adj8.customer_trx_id = racust8.customer_trx_id
440 and racust8.bill_to_customer_id = hzcus8.cust_account_id
441 and racust8.batch_id = batch8.batch_id (+)
442 and glps8.application_id = 222
443 and adj8.set_of_books_id = glps8.set_of_books_id
444 and adj8.gl_date between glps8.start_date and glps8.end_date
445 and adj8.set_of_books_id = s8.set_of_books_id
446 and s8.period_set_name = r8.period_set_name
447 and glps8.period_name = r8.period_name
448 and adj8.adjustment_id = dis8.source_id
449 and dis8.source_type = 'REC'
450 order by 1,3,4;
451
452 /* r_bmb c_bmb%rowtype; */
453 l_org_id NUMBER;
454 l_country_code VARCHAR2(5);
455 l_product_code VARCHAR2(5);
456 begin
457 -- fix for bug # 2587958
458 l_org_id := MO_GLOBAL.get_current_org_id;
459 l_country_code := JG_ZZ_SHARED_PKG.GET_COUNTRY(l_org_id,null);
460 l_product_code := JG_ZZ_SHARED_PKG.GET_PRODUCT(l_org_id,null);
461 IF l_product_code <> 'JL' or l_country_code <> 'BR' then
462 return;
463 end if;
464
465 FOR r_bmb in c_bmb LOOP
466
467 pl_sob := r_bmb.sob;
468 pl_per := r_bmb.per;
469 pl_per_set := r_bmb.perset;
470 pl_ccid := r_bmb.ccid;
471 pl_cust := r_bmb.cust;
472 pl_sign := r_bmb.isign;
473 pl_val := r_bmb.ival;
474 pl_pyear := r_bmb.pyear;
475 pl_pnum := r_bmb.pnum;
476
477 select decode(r_bmb.isign,'D',-1*r_bmb.ival,r_bmb.ival)
478 into pl_ival2
479 from dual;
480
481 /* Always insert all posted transactions from AR to GL */
482
483 begin
484 insert into JL_BR_JOURNALS (
485 APPLICATION_ID ,
486 SET_OF_BOOKS_ID ,
487 PERIOD_SET_NAME ,
488 PERIOD_NAME ,
489 CODE_COMBINATION_ID ,
490 PERSONNEL_ID ,
491 TRANS_CURRENCY_CODE ,
492 BATCH_ID,
493 BATCH_NAME ,
494 ACCOUNTING_DATE ,
495 TRANS_ID,
496 TRANS_NUM ,
497 TRANS_DATE ,
498 TRANS_DESCRIPTION ,
499 INSTALLMENT,
500 TRANS_VALUE_SIGN ,
501 TRANS_VALUE ,
502 JOURNAL_BALANCE_FLAG,
503 LAST_UPDATE_DATE ,
504 LAST_UPDATED_BY ,
505 LAST_UPDATE_LOGIN ,
506 CREATION_DATE ,
507 CREATED_BY,
508 ORG_ID ) VALUES (
509 222,
510 r_bmb.sob,
511 r_bmb.perset,
512 r_bmb.per,
513 r_bmb.ccid,
514 r_bmb.cust,
515 r_bmb.cur,
516 r_bmb.batid,
517 r_bmb.bat,
518 r_bmb.accd,
519 r_bmb.invid,
520 r_bmb.num,
521 r_bmb.idat,
522 r_bmb.hist,
523 r_bmb.inst,
524 r_bmb.isign,
525 r_bmb.ival,
526 'N',
527 sysdate,
528 pl_user,
529 '',
530 '',
531 '',
532 r_bmb.org_id);
533
534
535 end;
536 end loop;
537
538 END JL_BR_AR_BAL_MAINTENANCE;
539
540 END JL_BR_AR_BALANCE_MAINTENANCE;