OraFAQ Forum: Oracle Fusion Apps & E-Business Suite

Home » Applications » Oracle Fusion Apps & E-Business Suite » Supplier Ledger

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Supplier Ledger [message #349870]

Tue, 23 September 2008 06:31

zain.siddiqui
Messages: 2
Registered: September 2008
Location: Karachi

Junior Member

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,xsv.vendor_number,xsv.vendor_name,xsv.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 :P_FROM_DATE AND :P_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 = :P_ORG_ID
and aia.gl_date between :P_FROM_DATE AND :P_TO_DATE

) x,
xxxx_suppliers_v xsv
where x.vendor_site_id = xsv.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

Thanks,
Zain A. Siddiqui

Report message to a moderator

Previous Topic:	Oracle WorkFlow
Next Topic:	Invalid Requisitions

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jun 30 09:52:38 CDT 2024