April 9th, 2009
By using the following query we can get number of profile option changed along with new values in given number of days
Connect as apps user to instance
SELECT ‘***Profile Option Name ***’
|| a.user_profile_option_name
|| ‘*** Was Updated with value ‘
|| ‘”‘
|| b.profile_option_value
|| ‘”‘
|| ‘ In The Last ‘
|| &p_no_of_days
|| ‘ days’
|| ‘ by ‘
|| (SELECT user_name
FROM apps.fnd_user u
WHERE u.user_id = b.last_updated_by) mesg
FROM apps.fnd_profile_options_vl a,
apps.fnd_profile_option_values b,
apps.fnd_user c
WHERE a.profile_option_id = b.profile_option_id
AND b.last_updated_by = c.user_id
AND ( b.last_update_date > SYSDATE - &p_no_of_days
OR b.creation_date > SYSDATE - &p_no_of_days
)
Posted in Oracle Application Objects (AOL), SQL | 1 Comment »
April 9th, 2009
Connect by Apps user name and just enter Amount when Prompted.We can use this query in Oracle Alerts to Notify GL Manager
SELECT gjh.NAME, gjh.running_total_cr, gjh.running_total_dr,
gjh.currency_code, gjlv.description, gsnv.NAME,
DECODE (NVL (gjlv.entered_dr, 1),
1, ‘CREDIT’,
gjlv.entered_dr, gjlv.entered_dr
) debit,
DECODE (NVL (gjlv.entered_cr, 1),
1, ‘DEBIT’,
gjlv.entered_cr, gjlv.entered_cr
) credit,
gjlv.period_name,
( gccv.segment1
|| ‘ ‘
|| gccv.segment2
|| ‘ ‘
|| gccv.segment3
|| ‘ ‘
|| gccv.segment4
|| ‘ ‘
|| gccv.segment5
|| ‘ ‘
|| gccv.segment6
|| ‘ ‘
|| gccv.segment7
|| ‘ ‘
|| gccv.segment8
) ACCOUNT,
gjlv.entered_cr, gjlv.entered_dr
FROM gl_je_headers gjh,
gl_je_lines_v gjlv,
gl_sob_names_v gsnv,
gl_code_combinations_v gccv
WHERE ( (gjlv.je_header_id = gjh.je_header_id)
AND (gsnv.set_of_books_id = gjlv.set_of_books_id)
AND (gjlv.code_combination_id = gccv.code_combination_id)
)
AND (gjh.running_total_dr > :amount OR gjh.running_total_cr > :amount)
Posted in Oracle General Ledger | 1 Comment »
April 9th, 2009
Good customer scoring strategies will have better visibility, quicker decision making, reduces manual efforts and increase in the profitability
It can be easily configured on top of an existing 11i implementation
Easy configuration depending on requirements.
On line credit application and approval process will eliminate many home grown systems
Controls escalating and higher incidences of bad debts
Reduces collection cost and diminishing returns
Reduces the credit-to-cash life cycle
We can have aggressive and/or conservative credit policies based on customer classes
Track credit performances and history of credit decisions and run reports as needed
Continuous and scheduled periodic credit evaluations for high risk customers or for all customers
Matches global credit policies ( In MNC Scenario)
Strong internal controls
Lack of visibility in International Credit. Can handle financial risks in global market
Posted in Oracle Credit Management | 1 Comment »
April 9th, 2009
Bind references are used to replace a single value in SQL or PL/SQL, such as a character string, number, or date. Use bind reference when you want the parameter to substitute only one value at runtime. Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH clauses of queries.
Bind parameters are used to substitute single value at runtime for evaluation and are identified by a preceding ‘:’ .
An example of a bind parameter in a select statement is provided below, where :P_Name is the bind parameter reference.
Select Name,Department,Position
From my_Table
Where Dept = :P_Dept
These are used as tokens while registering concurrent program
Posted in Oracle Report | 2 Comments »
April 9th, 2009
Lexical references are placeholders for text that you embed in a SELECT statement. Use Lexical reference when you want the parameter to substitute multiple values at runtime. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.
Lexical parameters are used to substitute multiple values at runtime and are identified by a preceding ‘&’. Lexical s can consist of as little a one line where clause to an entire select statement
Lexical Parameters are used to execute query dynamically.
Example:
Select * from my_table &where.
Posted in Oracle Report | 1 Comment »
April 9th, 2009
- Oracle Reports: Fixed format reports delivered with the 11i release were built on this tool. This is the most used tool for reporting on Oracle Applications. Most of reports customizations are built with this tool. Once customized the output of the report can be in Excel (Not group By Report), word, Acrobat documents or text format.
- Oracle Discoverer: is an intuitive tool for creating reports and performing on-line analysis. Discoverer uses the EUL (End User Layer), a meta data definition, which hides the complexity of the database from the end user and provides easy to use wizards for creating reports to suit individual needs. The flexibility of this tool allows the user to create cross tab reports that perform like pivot tables in Excel.
- Oracle XML Publisher: is a new Oracle tool for reporting. It enables users to utilize a familiar desktop tool, like MS Word or MS Excel, to create and maintain their own report. At runtime, XML Publisher merges the custom templates with the concurrent request extracts data to generate output in RTF, PDF, HTML and EXCEL.
- RXi Report: (Variable reports) – variable format reports delivered with the E-Business 11i. With this tool a user has the ability to print the same report with multiple layouts. The user can also choose which columns he requires on a particular report. This tool is most used on Oracle Financials Applications
- FSG Reports (Financial Statement Generator): is a powerful report building tool for Oracle General Ledger. Some of benefits of using this tool are that a user can generate financial reports, and schedule reports to run automatically. The only drawback of this tool is that it is only available for the general ledger responsibility and can be used to see only financial account balances.
- Business Intelligence System (BI): is a set of tools to provide high level information for the managers (decision makers) to run their business such as the profitability of a particular business unit. The information this tool provides helps managers to take the right decision with the daily data that is uploaded on their systems.
Posted in Oracle Report | 1 Comment »
August 31st, 2008
The OracleHelps.Com is NOT an official Oracle Support site, but it helps to the People who is working with Oracle. You can learn about Oracle, share knowledge and try to help others solve their problems. Through this sites, you can get a wealth of information at the fingertips of Oracle professionals all over the world.
The information presented here is suitable for all programmers from beginner to expert. We hope you find this information useful and return to our site, as we expand our information base.
Posted in Home | 2 Comments »