Monday 22 January 2018

Oracle Apps Creditor Ledger SQL query

Oracle Apps Creditor Ledger SQL query

In this post , We will be discuss about the Oracle Apps Creditor Ledger SQL query. This query will help to developed the creditors ledgers report in oracle apps. Creditor ledgers will capture all the Supplier or Creditor Invoices and the Payments and shows the final out-standings. We do call supplier as a creditors in oracle apps. This is one of the important report to find out the supplier or creditor outstanding in oracle apps r12.

Oracle Apps Creditor Ledger SQL query

Important Tables used by Oracle Apps Creditor Ledger SQL query 

1.ap_invoices_all

2.ap_suppliers

3.ap_supplier_sites_all

4.ap_invoice_distributions_all
5.ap_suppliers
6.ap_supplier_sites_all
7.AP_CHECKS_ALL
8.AP_INVOICE_PAYMENTS_ALL


Detail Oracle Apps Creditor Ledger SQL query

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 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
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, '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
order by 12

Oracle Apps Creditor Ledger SQL query

5 comments:

shaik shah said...

Thanks for sharing this informative article ,It would be helpful to all
Oracle Fusion HCM Online Training

Anonymous said...

Thanks for Sharing This informative article. It would be helpful if you write a all.
Oracle Fusion HCM Online Training

shaik shah said...

Excellent blog I visit this blog it's really awesome. The important thing is that in this blog content written clearly and understandable. The content of information is very informative.
Oracle Fusion HCM Online Training

Lakshmi Satya said...

Nice blog,Very useful article,Thanks for sharing this information.
Oracle Fusion HCM Training | Oracle Fusion HCM Training in Hyderabad

raveena said...

Nice artical

Oracle Fusion HCM Training

Post a Comment

Contact us for any Collaboration, Project Support & On Job Support Work

Name

Email *

Message *