how to join ap_invoices_all,ap_invoice_payments _a

Here you can post the Business Intelligence (BI) related queries and replies.
Post Reply
phanigajarao
Posts: 33
Joined: Sat Mar 29, 2008 10:08 am
Location: India

how to join ap_invoices_all,ap_invoice_payments _a

Post by phanigajarao »

how to join ap_invoices_all,ap_invoice_payments _all,ap_checks_all,jai_ap_tds_payments_all

i need following columns in discoverer

Payment Method Lookup Code
check Amount
Vendor Name
Invoice Num
Invoice Amount SUM
Tax Amount SUM
Check Number
Check Date


i have writen this is query but tds amount is not matching to invoice amount and check amount can anybody correct following query

SELECT I.INVOICE_NUM,I.INVOICE_AMOUNT,T.TAX_AMOUNT,
P.PERIOD_NAME,
C.AMOUNT,C.BANK_ACCOUNT_NAME,C.VENDOR_NAME,C.CHECK_DATE,C.CHECK_NUMBER
FROM AP_INVOICES_ALL I,AP_INVOICE_PAYMENTS_ALL P, JAI_AP_TDS_PAYMENTS T,ap_checks_all C
WHERE
I.INVOICE_ID=P.INVOICE_ID
and
P.CHECK_ID=C.CHECK_ID
and
P.INVoice_id=T.INVOICE_ID
C.CHECK_DATE=TO_DATE(:date_START,'DD/MM/YYYY');


Thanks

Gajarao Phani
eswar_chinni
Posts: 32
Joined: Mon Mar 12, 2007 4:08 am
Location: India

Post by eswar_chinni »

Hi

If you need tax amount you need to consider ja_in_ap_tds_invoices table...

Regards
Eshwar
Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest