 Payable Invoice Aging Report (merged by LF) [message #265670] |
Thu, 06 September 2007 21:58  |
djp1976
Messages: 15
Registered: September 2007
|
Junior Member |
|
|
Hello. My name is Dustin. I work for Unisys Corporation in their financial services division on their reporting department. I have 2 inquiries in which i need feedback;
1. I am using TOAD application in conjunction with our Oracle system to provide reporting. Is there any clear cut method of identifying the Oracle Tables and the columns contained within those tables? I am finding it very hard to find where speicific data and the areas that they are stored in in relationship to where the data is entered into Oracle.
2. I am trying to devise a TOAD script that will emulate the Oracle AP module report named Unisys Payables Invoice Aging Report. I am not able to see how this report constructs in Oracle. I want to be able to create and aging report in TOAD using sql. I am not very familiar with how aging buckets work and how they are applied to a date like an invoice date. Does anyone have any idea of how this can be accomplished using a toad script. And if so can you provide me with sample code and emplanation of the aging buckets.
Please send responses to dustyel@hotmail.com Thanks and regards
[Updated on: Fri, 07 September 2007 05:49] Report message to a moderator |
|
|
|
| Re: TOAD SQL Questions associated with Oracle E-Business Suite [message #265701 is a reply to message #265670] |
Fri, 07 September 2007 01:35  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
As of your first question: TOAD uses Schema Browser to display database objects (tables, views, procedures, ...). Once you open it, find a tab which will show you information you are looking for. Note that some tabs may be "hidden" (either not displayed by default or someone (you?) chose not to show them, or tabs may be positioned on a "scrollable" bar so you'd have to scroll left/right to see what's available).
"Objects contained within tables": what do you call "objects"? Tables in Oracle have columns, and those columns contain values (numeric, character, dates, LOBs, ...). Some of them may be viewed by TOAD, some may not (LOBs, for example; for those, you'll have to use an application which is capable of doing that).
I must admit that I don't understand what should "finding relative data and the areas that they are stored in" mean. What kind of data is "relative", and what are those "areas"?
If those terms are E-Business Suite related, I'm sorry - I don't know anything about it.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: AR Invoice Detail Table in TOAD [message #267529 is a reply to message #267214] |
Thu, 13 September 2007 11:05 |
appsmichael
Messages: 6
Registered: September 2007
Location: NY
|
Junior Member |
|
|
You can find the invoice details in the following tables:
RA_CUSTOMER_TRX_ALL --->This has the header record
RA_CUSTOMER_TRX_LINES_ALL ---->This has the lines(line item,associated tax line etc) for the header record in the above table.
link b/w RA_CUSTOMER_TRX_ALL and RA_CUSTOMER_TRX_LINES_ALL is CUST_TRX_ID
Hope this helps you.
|
|
|
|
| Re: Acounts Payable Invoice Aging Report Script [message #267531 is a reply to message #267137] |
Thu, 13 September 2007 11:08 |
appsmichael
Messages: 6
Registered: September 2007
Location: NY
|
Junior Member |
|
|
For finding the Aging Bucket Query,you can take out the RDF file from your apps report directory and open the rdf file in Report Builder and from the datamodel you can open the query which can be used in TOAD with some minor modifications in the query.
Hope this helps you.
|
|
|
|
|
|
| Oracle Payables Invoice Aging Report again?? [message #267618 is a reply to message #266778] |
Thu, 13 September 2007 21:47 |
djp1976
Messages: 15
Registered: September 2007
|
Junior Member |
|
|
Hello. Can anyone provide me with the background SQL coding for this report in Oracle? I do not have to option to trace the report. Please advise how i can emulate in TOAD.
Second if the first is not an option. Can anyone supply me with how to write SQL for an aging report for payables? aging buckets? etc.
|
|
|
|
|
|
| Re: AR Invoice Detail Reports another inquiry?? [message #267719 is a reply to message #267619] |
Fri, 14 September 2007 06:23 |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Check the datamodel in eTRM on Metalink.
EBS is quite complicated, so I recommend to study the whole module's datamodel before you start writing SQL on it.
I'm not very fond of the idea of posting SQL statements behind EBS reports here. I think it's proprietary of Oracle Corporation. Reverse engineering it from you company's installation of EBS or studying the datamodel and writing your own code is one thing, posting code on a public forum is another thing. All technical documentation of EBS is on Metalink (requiring registration), contrary to the documentation of all other products. That's why I think it has another status.
|
|
|
|
| Re: AR Invoice Detail Reports another inquiry?? [message #267784 is a reply to message #267719] |
Fri, 14 September 2007 23:47 |
appsmichael
Messages: 6
Registered: September 2007
Location: NY
|
Junior Member |
|
|
Yep true..I agree with Skooman....Posting the sql statment here would be wrong..Its a propreitary of Oracle Corp...thats y I suggested the above option and didnot put the sql statement from the standard report.
You can get all the table details that you need from Oracle documentation which you can d/l thro OTN or u can check the technical documentation in eTRM of metalink,if u are given the login details for metalink by your organisation.
RA_CUST_ACCT_TRX_ALL contains the invoice header information...There are many more tables that can be joined to this table depending on what details you want.
RA_CUST_ACCT_TRX_LINES_ALL contains distribution lines of invoice.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
HTH