OraFAQ Forum: Oracle Fusion Apps & E-Business Suite » how to join ap_invoices_all,ap_invoice_payments _all,ap_checks_all,jai_ap_tds_payments

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))

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

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

Code: [Select all] [Show/ hide]

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 04 21:31:25 CDT 2025