Supplier Opening Balance query in Oracle Apps
Hi friends, we are going to discuss about the supplier opening balance query in oracle apps. We will share the detail sql query which helps to extract the complete Supplier balance as on date with the opening balance as on date for suppliers. Usually, we do have supplier opening balance in the Supplier ledger or creditor ledger reports. In this post, I will try to share the sql query which will give the supplier running balance including each supplier transactions in oracle apps. We will also share the important tables too which helps to write this sql query which helps to find out the supplier opening balances in oracle apps. Using this sql query we can find out the supplier opening and closing balances as per from date and End date. Please find below the detail about supplier opening balance query in oracle apps
 |
| Supplier opening Balance query in Oracle Apps |
7 Important Tables Uses in the supplier opening balance query in oracle apps
1.AP_INVOICES_ALL
2.AP_INVOICE_LINES_ALL
3.AP_CHECKS_ALL
4.AP_SUPPLIERS
5.AP_SUPPLIER_SITES_ALL
6.AP_INVOICE_PAYMENTS_ALL
7.AP_INVOICE_DISTRIBUTIONS_ALL
Detail SQL Query to find out the Supplier Opening Balance Query in Oracle Apps
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
 |
| Supplier opening Balance query in Oracle Apps |
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