| how to join ap_invoices_all,ap_invoice_payments _all,ap_checks_all,jai_ap_tds_payments_all [message #410369] |
Fri, 26 June 2009 10:12  |
phani2327
Messages: 20
Registered: April 2008
|
Junior Member |
|
|
how to join ap_invoices_all,ap_invoice_payments _all,ap_checks_all,jai_ap_tds_payments_all in discoverer
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
|
|
|
|
| Re: how to join ap_invoices_all,ap_invoice_payments _all,ap_checks_all,jai_ap_tds_payments_all [message #410375 is a reply to message #410369] |
Fri, 26 June 2009 12:03  |
 |
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
|
Senior Member |
|
|
desc JAI_AP_TDS_PAYMENTS
Name Null Type
------------------------------ -------- ----------------------------------------------------------------------------------
CHECK_ID NOT NULL NUMBER(15)
CHECK_DATE NOT NULL DATE
CHECK_NUMBER NOT NULL NUMBER(15)
BANK_NAME NOT NULL VARCHAR2(150)
CURRENCY_CODE NOT NULL VARCHAR2(15)
PAYMENT_AMOUNT NOT NULL NUMBER
CHECK_DEPOSIT_DATE NOT NULL DATE
CHALLAN_NO NOT NULL VARCHAR2(80)
VENDOR_ID NOT NULL NUMBER(15)
VENDOR_SITE_ID NOT NULL NUMBER(15)
CREATION_DATE DATE
CREATED_BY NUMBER(15)
LAST_UPDATE_DATE DATE
LAST_UPDATED_BY NUMBER(15)
LAST_UPDATE_LOGIN NUMBER(15)
OBJECT_VERSION_NUMBER NUMBER
FIN_YEAR NUMBER
FORM16_HDR_ID NUMBER(22)
INVOICE_DATE DATE
OPERATING_UNIT_ID NUMBER(22)
ORG_TAN_NUM VARCHAR2(50)
PARENT_INVOICE_ID NUMBER(22)
TAX_AMOUNT NUMBER
TAX_AUTHORITY_ID NUMBER(22)
TAX_AUTHORITY_SITE_ID NUMBER(22)
TDS_SECTION VARCHAR2(30)
TDS_TAX_ID NUMBER(22)
TDS_TAX_RATE NUMBER
BSR_CODE VARCHAR2(7)
Please post a test case with sample output.
By
Vamsi
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
