manojgh
asked on
AP & AR module query
I need a query of the following:
Invoices generated in December 1 through March 1
Based on a ship to of:
New Jersey
Texas
The query should contain the following:
Invoice Number
Date
G/L Tax Liability Account
Amount of Invoice
Amount of Tax charged
Can somebody pls help me getting this query done. I'm getting Invoice ID, Amount and Date from AP_INVOICES_ALL, i hope "ship to" will come from "RA_CUSTOMERS", but donno how to join them. Am I looking into correct tables.
Your help is appreciated.
webservices23@yahoo.com
Invoices generated in December 1 through March 1
Based on a ship to of:
New Jersey
Texas
The query should contain the following:
Invoice Number
Date
G/L Tax Liability Account
Amount of Invoice
Amount of Tax charged
Can somebody pls help me getting this query done. I'm getting Invoice ID, Amount and Date from AP_INVOICES_ALL, i hope "ship to" will come from "RA_CUSTOMERS", but donno how to join them. Am I looking into correct tables.
Your help is appreciated.
webservices23@yahoo.com
Plz explain your both tables structure
From,
Syed Faisal
From,
Syed Faisal
ASKER
Thanks Mike.
I'm sorry about the way the Question was put. This Query has to come only from AR module of Oracle Apps 11i. So, the AP_INVOICES_ALL will NOT come in the picture. I'm using APPS.RA_CUSTOMER_TRX A, APPS.RA_CUSTOMER_TRX_LINES B
, APPS.RA_CUST_TRX_LINE_GL_D IST C, APPS.RA_COSTOMERS D.
Let me know if you have any questions.
I'm sorry about the way the Question was put. This Query has to come only from AR module of Oracle Apps 11i. So, the AP_INVOICES_ALL will NOT come in the picture. I'm using APPS.RA_CUSTOMER_TRX A, APPS.RA_CUSTOMER_TRX_LINES
, APPS.RA_CUST_TRX_LINE_GL_D
Let me know if you have any questions.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Im making a few assumption but you can apply the changes yourself or ask again
SELECT a.[Invoice Number], a.[Date], a.[Acct], a.[Invoice Amount], a.[Tax Amount], b.[CustomerID], b.[City]
FROM AP_INVOICES_ALL as a INNER JOIN RA_CUSTOMERS as b
ON a.CustomerID = b.CustomerID
WHERE (b.date between #12/01/03# and #3/1/03#) and (b.city = 'New Jersey' or b.city ='Texas')
The code above assumes the CustomerID column are both present in the RA_CUSTOMER, AP_INVOICES_ALL table. They are both linked as one-to-many relation. The query will pull out all invoices related to a customer and where the customers city is either "new jersey" or "texas". It also narrows the data down more by only selected the records where the invoice date is between 12/01/03 - 3/1/03
-Mike