oraclehelps.com

Query for audit changes in Profile Option

Administrator — Thu, 09 Apr 2009 05:36:21 +0000

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
)

Get JV more than Specified Amount

Administrator — Thu, 09 Apr 2009 05:34:08 +0000

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)

Need of Oracle Credit Management

Administrator — Thu, 09 Apr 2009 05:32:34 +0000

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

Bind Referencing / Parameter

Administrator — Thu, 09 Apr 2009 05:10:33 +0000

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

Lexical Parameter

Administrator — Thu, 09 Apr 2009 05:06:52 +0000

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.

Reporting Tools in Oracle Application

Administrator — Thu, 09 Apr 2009 04:25:31 +0000

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.

Welcome to The Oraclehelps.com Blog….

Administrator — Sun, 31 Aug 2008 05:28:00 +0000

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.