AR and AP reconcilliation

[otenyop]

Hi,
Please tell me which reports i can run to reconcile AP-GL and AR-GL. I want to make sure that everything has been transferred to GL from these subledgers.
Regards
Phil

[admin]

When you will close the period there will be exception report and you can also use "Period Close Exceptions Report". thanks

[otenyop]

Dear All,
Please Use the following script for AR-GL reconcilliation.

set heading off
set linesize 136
set pagesize 55
column datetime noprint new_value datetime
column coy noprint new_value coy
column Company noprint new_value Company
column cus_num format a6 heading 'Cust No.'
column cus_name format a40 heading 'Cust Name.'
column cus format 999999 heading 'CUS'
column prvbal format 9,999,999,999 heading 'Prv.Age Bal'
column curbal format 9,999,999,999 heading 'Cur.Age Bal'
column armove format 9,999,999,999 heading 'AR Movement'
column glmove format 9,999,999,999 heading 'GL Movement'
column argldiff format 9,999,999,999 heading 'AR-GL Difference'
break on report skip 1
compute sum of prvbal curbal armove glmove argldiff on report
select
to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') datetime
from dual;
set heading on
ttitle skip left 'AR-GL RECONCILIATION REPORT' -
right 'Date: ' datetime -
Skip 1 right 'Page :' sql.pno -
skip 1 Company -
skip 2 'PERIOD : ' &1 -
skip 2 ' '
select
--1 Customer
bl.cus,
max(rc.customer_number) cus_num,
max(rc.customer_name) cus_name,
--2 Previous Ageing Balance
sum(decode(bl.sos
,1,nvl(bl.amt,0)
,0)) PrvBal,
--3 Current Ageing Balance
sum(decode(bl.sos
,2,nvl(bl.amt,0)
,0)) CurBal,
--4 AR movement
sum(decode(bl.sos
,2,nvl(bl.amt,0)
,0) -
decode(bl.sos
,1,nvl(bl.amt,0)
,0)) ARMove,
--5 Gl Movement
sum(decode(bl.sos
,3,nvl(bl.amt,0)
,0)) GLMove,
--6 ARGL Difference
sum(decode(bl.sos
,2,nvl(bl.amt,0)
,0) -
decode(bl.sos
,1,nvl(bl.amt,0)
,0) -
decode(bl.sos
,3,nvl(bl.amt,0)
,0)) ARGLDiff
from
(
select
aps.CUSTOMER_ID cus,
1 sos,
(apps.gen_fix.AR_TRX_BALANCE(
last_day(add_months(to_date('01-'||'&1','DD-MON-YYYY'),-1)),
aps.PAYMENT_SCHEDULE_ID,
aps.ACCTD_AMOUNT_DUE_REMAINING,
aps.AMOUNT_APPLIED,
aps.AMOUNT_CREDITED,
aps.AMOUNT_ADJUSTED,
aps.CLASS )) amt
from
ar_payment_schedules aps
where
aps.INVOICE_CURRENCY_CODE <> 'STAT'
and aps.gl_date <= last_day(add_months(to_date('01-'||'&1','DD-MON-YYYY'),-1))
and aps.gl_date_closed >= last_day(add_months(to_date('01-'||'&1','DD-MON-YYYY'),-1))
union all
select
aps.CUSTOMER_ID cus,
2 sos,
(apps.gen_fix.AR_TRX_BALANCE(
last_day(to_date('01-'||'&1','DD-MON-YYYY')),
aps.PAYMENT_SCHEDULE_ID,
aps.ACCTD_AMOUNT_DUE_REMAINING,
aps.AMOUNT_APPLIED,
aps.AMOUNT_CREDITED,
aps.AMOUNT_ADJUSTED,
aps.CLASS )) amt
from
ar_payment_schedules aps
where
aps.INVOICE_CURRENCY_CODE <> 'STAT'
and aps.gl_date <= last_day(to_date('01-'||'&1','DD-MON-YYYY'))
and aps.gl_date_closed >= last_day(to_date('01-'||'&1','DD-MON-YYYY'))
union all
select
to_number(gjl.reference_7),
3 sos ,
(nvl(gjl.accounted_dr,0) - nvl(gjl.accounted_cr,0)) amt
from
gl_je_lines gjl,
gl_code_combinations gcc
where
gjl.code_combination_id = gcc.code_combination_id
and gcc.SEGMENT3 in ('AR Control Account','AR Unapplied Receipts Account')
and period_name = '&1'
and gjl.SET_OF_BOOKS_ID = &2
) bl,
ra_customers rc
where
bl.cus (+) = rc.customer_id
group by
bl.cus


/

[admin]

Pl provide the list of parameters to be passed and the format of parameters. Thanks

[otenyop]

The parameters are as follows:
&1 is the period which is a string.It should be of this format eg. 'JAN-2007'
&2 is the set of books id which is a number e.g 101 this applies if you are in a multi-org environment.
Thats all.
Rgds.
Phil