Home » Applications » Oracle Fusion Apps & E-Business Suite » how to join ap_invoices_all,ap_invoice_payments _all,ap_checks_all,jai_ap_tds_payments_all (11.5.10.2(10g discoverer))
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 Go to next message
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 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
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)  
I don't think your code will work without error.

Please post a test case with sample output.

By
Vamsi
Previous Topic: See output of concurrent program run by others
Next Topic: User-Defined Exception in arp_auto_rule .create_distributions
Goto Forum:
  


Current Time: Wed Dec 07 16:18:14 CST 2016

Total time taken to generate the page: 0.08005 seconds