Supplier Ledger

[Zain A. Siddiqui]

Dear All,
I have to prepare Supplier ledger in R12, I made a query but the but the supplier balance is not same from front hand. I just want to know criteria how the ebs calculate Balanced owed. Kindly help me for this.
I wrote this query.

select x.vendor_id,x.vendor_site_id,wsv.vendor_number,wsv.vendor_name,wsv.vendor_site_code, x.vch_no,x.doc_number,x.gl_date,x.invoice_type_lookup_code,x.description,
dr+dr1 dr,cr+cr1 cr
from (
select aca.doc_sequence_value vch_no,to_char(aca.check_number) doc_number,aipa.accounting_date gl_date,aca.vendor_id,aca.vendor_site_id
,case when aia.invoice_type_lookup_code = 'PREPAYMENT' THEN 'AGAINST PREPAYMENT' else 'PAYMENT' end invoice_type_lookup_code
,'Payment Against Invoice # '||aia.invoice_num description
, nvl(aca.amount,0) dr,0 dr1,0 cr,0 cr1
from ap_checks_all aca,
ap_invoice_payments_all aipa,
ap_invoices_all aia
where aca.check_id = aipa.check_id
and aipa.invoice_id = aia.invoice_id
and aca.org_id =:P_ORG_ID
and aipa.accounting_date between _FROM_DATE AND _TO_DATE

union all

select aia.doc_sequence_value vch_no,aia.invoice_num doc_number,aia.gl_date,aia.vendor_id,aia.vendor_site_id
,aia.invoice_type_lookup_code,aia.description
,case when aia.invoice_type_lookup_code in ('DEBIT','CREDIT') THEN nvl(aia.invoice_amount,0) ELSE 0 END DR
,case when aia.invoice_type_lookup_code in ('MIXED') AND NVL(AIA.INVOICE_AMOUNT,0 ) < 0 THEN abs(nvl(aia.invoice_amount,0)) ELSE 0 END DR1
,case when aia.invoice_type_lookup_code in ('STANDARD','EXPENSE REPORT') THEN nvl(aia.invoice_amount,0) ELSE 0 END CR
,case when aia.invoice_type_lookup_code in ('MIXED') AND NVL(AIA.INVOICE_AMOUNT,0 ) > 0 THEN nvl(aia.invoice_amount,0) ELSE 0 END CR1
from ap_invoices_all aia
where aia.invoice_type_lookup_code != 'PREPAYMENT'
AND AP_INVOICES_PKG.GET_APPROVAL_STATUS( AIA.INVOICE_ID,
AIA.INVOICE_AMOUNT, AIA.PAYMENT_STATUS_FLAG,
AIA.INVOICE_TYPE_LOOKUP_CODE) = 'APPROVED'
and aia.org_id = _ORG_ID
and aia.gl_date between _FROM_DATE AND _TO_DATE

) x,
wtpo_suppliers_v wsv
where x.vendor_site_id = wsv.vendor_site_id
AND TO_NUMBER(WSV.VENDOR_NUMBER) = NVL(:P_VEN_NUM, WSV.VENDOR_NUMBER)
AND X.VENDOR_SITE_ID = NVL(:P_SITE_ID,X.VENDOR_SITE_ID)
order by x.gl_date