Monday, 22 January 2018

Oracle Apps Creditor Ledger SQL query


Creditor Ledger SQL query in Oracle Apps R12



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

0 comments:

Post a Comment