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.
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
select aia.org_id 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
,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
5 comments:
Thanks for sharing this informative article ,It would be helpful to all
Oracle Fusion HCM Online Training
Thanks for Sharing This informative article. It would be helpful if you write a all.
Oracle Fusion HCM Online Training
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
Nice blog,Very useful article,Thanks for sharing this information.
Oracle Fusion HCM Training | Oracle Fusion HCM Training in Hyderabad
Nice artical
Oracle Fusion HCM Training
Post a Comment