Here below is the detail sql query which helps to find out the supplier opening balance query in oracle apps. This sql query will help to extract all supplier transactions( Invoices/Credit Memo's/Supplier Payments). Using this transactions we do calculate the supplier outstanding as on date. Using this query logic we can calculate the Supplier Opening Balance as on date and also calculate the Supplier closing balances as per the end date.
SELECT ACA.ORG_ID, 'PAYMENTS', ACA.CHECK_DATE, ACA.DESCRIPTION,
ACA.EXCHANGE_RATE PAY_RATE,
ACA.DOC_SEQUENCE_VALUE VOUCHER_NUMBER,
NULL INVOICE_NUM, TO_CHAR (ACA.CHECK_NUMBER), ACA.CHECK_ID,
APS.VENDOR_NAME, ASSA.VENDOR_SITE_CODE, AIPA.ACCOUNTING_DATE,
DECODE (SIGN ((AIPA.AMOUNT)),-1,(AIPA.AMOUNT) * -1,0) * (NVL (ACA.EXCHANGE_RATE, 1) - NVL (AIA.EXCHANGE_RATE, 1)) DR_AMOUNT,
DECODE (SIGN ((AIPA.AMOUNT)),1,(AIPA.AMOUNT),0) * (NVL (ACA.EXCHANGE_RATE, 1) - NVL (AIA.EXCHANGE_RATE, 1)) CR_AMOUNT,
:P_FROM_DATE, :P_TO_DATE
FROM AP_CHECKS_ALL ACA,
AP_INVOICE_PAYMENTS_ALL AIPA
, AP_INVOICES_ALL AIA
, AP_SUPPLIERS APS
, AP_SUPPLIER_SITES_ALL ASSA
WHERE 1=1
AND ACA.CHECK_ID = AIPA.CHECK_ID
AND APS.VENDOR_ID = ASSA.VENDOR_ID
AND ACA.VENDOR_ID = APS.VENDOR_ID
AND ACA.VENDOR_SITE_ID = ASSA.VENDOR_SITE_ID
AND AIPA.INVOICE_ID = AIA.INVOICE_ID
AND ACA.ORG_ID = :P_ORG_ID
AND AIPA.ACCOUNTING_DATE BETWEEN NVL (:P_FROM_DATE, AIPA.ACCOUNTING_DATE)
AND NVL (:P_TO_DATE, AIPA.ACCOUNTING_DATE)
and APS.VENDOR_NAME =:VENDOR
and ASSA.VENDOR_SITE_CODE=:VENDOR_SITE_CODE
GROUP BY ACA.ORG_ID,
ACA.CHECK_DATE,
ACA.DESCRIPTION,
ACA.EXCHANGE_RATE,
ACA.DOC_SEQUENCE_VALUE,
ACA.CHECK_NUMBER,
ACA.CHECK_ID,
APS.VENDOR_NAME,
ASSA.VENDOR_SITE_CODE,
AIPA.ACCOUNTING_DATE,
AIA.EXCHANGE_RATE,
AIPA.AMOUNT
HAVING DECODE (SIGN ((AIPA.AMOUNT)),-1,(AIPA.AMOUNT) * -1,0) * (NVL (ACA.EXCHANGE_RATE, 1) - NVL (AIA.EXCHANGE_RATE, 1)) <> 0
or DECODE (SIGN ((AIPA.AMOUNT)),1,(AIPA.AMOUNT),0) * (NVL (ACA.EXCHANGE_RATE, 1) - NVL (AIA.EXCHANGE_RATE, 1)) <> 0
union all
select aia.org_id
,aia.invoice_type_lookup_code
,aia.invoice_date
,aia.description
,aia.exchange_rate
,aia.doc_sequence_value voucher_number
,to_char(aia.invoice_num) invoice_num
,null check_number
,aia.invoice_id
,aps.vendor_name
,assa.vendor_site_code
,aida.accounting_date
,decode(sign(sum(aida.amount)),-1,(sum(aida.amount))*-1,0) * nvl(aia.exchange_rate,1) dr_amount
,decode(sign(sum(aida.amount)),1,(sum(aida.amount)),0) * nvl(aia.exchange_rate,1) cr_amount
,:p_from_date
,:p_to_date ,
sum(aida.amount) func_dr,
sum(aida.amount) func_cr
from ap_invoices_all aia
,ap_suppliers aps
,ap_supplier_sites_all assa
,ap_invoice_distributions_all aida
where 1=1
and aia.vendor_id=aps.vendor_id
and aia.invoice_id=aida.invoice_id
and aps.vendor_id=assa.vendor_id
and aia.vendor_id=aps.vendor_id
and aia.vendor_site_id=assa.vendor_site_id
and aia.invoice_type_lookup_code!='PREPAYMENT'
and aida.line_type_lookup_code!='PREPAY'
and aia.org_id =:p_org_id
and aida.accounting_date between nvl(:p_from_date,aida.accounting_date) and nvl(:p_to_date,aida.accounting_date)
and aps.vendor_name=:VENDOR
and assa.vendor_site_code=:VENDOR_SITE_CODE
group by aia.org_id
,aia.invoice_type_lookup_code
,aia.invoice_date
,aia.description
,aia.exchange_rate
,aia.doc_sequence_value
,aia.invoice_num
,aia.invoice_id
,aps.vendor_name
,assa.vendor_site_code
,aida.accounting_date
union All
select aia1.org_id
,aia1.invoice_type_lookup_code
,aia1.invoice_date
,aia1.description
,aia1.exchange_rate
,aia1.doc_sequence_value voucher_number
,to_char(aia1.invoice_num) invoice_num
,null check_number
,aia1.invoice_id
,aps.vendor_name
,assa.vendor_site_code
,aida1.accounting_date
,decode(sign((aida1.amount)),1,((aida1.amount)),0) * (nvl(aia.exchange_rate,1)-nvl(aia1.exchange_rate,1)) dr_amount
,decode(sign((aida1.amount)),-1,((aida1.amount))*-1,0) * (nvl(aia.exchange_rate,1)-nvl(aia1.exchange_rate,1)) cr_amount
,:p_from_date
,:p_to_date
from ap_invoices_all aia
,ap_suppliers aps
,ap_supplier_sites_all assa
,ap_invoice_distributions_all aida
,ap_invoice_distributions_all aida1
,ap_invoices_all aia1
where 1=1
and aia.vendor_id=aps.vendor_id
and aia.invoice_id=aida.invoice_id
and aps.vendor_id=assa.vendor_id
and aia.vendor_id=aps.vendor_id
and aia.vendor_site_id=assa.vendor_site_id
and aia.invoice_type_lookup_code!='PREPAYMENT'
and aida.line_type_lookup_code='PREPAY'
and aia.org_id =:p_org_id
and aida.prepay_distribution_id=aida1.invoice_distribution_id
and aida1.invoice_id=aia1.invoice_id
and aida.accounting_date between nvl(:p_from_date,aida.accounting_date) and nvl(:p_to_date,aida.accounting_date)
and aps.vendor_name =:VENDOR
and assa.vendor_site_code=:VENDOR_SITE_CODE
group by aida1.amount
,aia.exchange_rate
,aia1.exchange_rate,
aia1.org_id
,aia1.invoice_type_lookup_code
,aia1.invoice_date
,aia1.description
,aia1.exchange_rate
,aia1.doc_sequence_value
,aia1.invoice_num
,aia1.invoice_id
,aps.vendor_name
,assa.vendor_site_code
,aida1.accounting_date
HAVING decode(sign((aida1.amount)),1,((aida1.amount)),0) * (nvl(aia.exchange_rate,1)-nvl(aia1.exchange_rate,1)) <> 0
or decode(sign((aida1.amount)),-1,((aida1.amount))*-1,0) * (nvl(aia.exchange_rate,1)-nvl(aia1.exchange_rate,1)) <> 0
union all
select aca.org_id
,'PAYMETNS'
,aca.check_date
,aca.description
,aca.exchange_rate
,aca.doc_sequence_value voucher_number
,null invoice_num
,to_char(aca.check_number)
,aca.check_id
,aps.vendor_name
,assa.vendor_site_code
,aipa.accounting_date
,decode(sign(sum(aipa.amount)),1,sum(aipa.amount),0)* nvl(aca.exchange_rate,1) dr_amount
,decode(sign(sum(aipa.amount)),-1,sum(aipa.amount)*-1,0) * nvl(aca.exchange_rate,1) cr_amount
,:p_from_date
,:p_to_date
from ap_checks_all aca
,ap_invoice_payments_all aipa
,ap_suppliers aps
,ap_supplier_sites_all assa
where aca.check_id=aipa.check_id
and aps.vendor_id=assa.vendor_id
and aca.vendor_id=aps.vendor_id
and aca.vendor_site_id=assa.vendor_site_id
and aca.org_id=:p_org_id
and aipa.accounting_date between nvl(:p_from_date,aipa.accounting_date) and nvl(:p_to_date,aipa.accounting_date)
and aps.vendor_name=:VENDOR
and assa.vendor_site_code=:VENDOR_SITE_CODE
group by aca.org_id
,aca.check_date
,aca.description
,aca.exchange_rate
,aca.doc_sequence_value
,to_char(aca.check_number)
,aca.check_id
,aps.vendor_name
,assa.vendor_site_code
,aipa.accounting_date
1 comments:
I have found that this site is very informative, interesting and very well written. keep up the nice high quality writing GestiĆ³n de procesos de negocio
Post a Comment