Examine Password Enable/Disable.

Set Utilities:Diagnostics Profile value to Yes/No to disable/enable password to use the examine feature in Oracle Apps.

Remove non ASCII Characters from a string

It's simple .....

SQL is here

SELECT
REPLACE(TRANSLATE(UPPER('YOUR_STRING'),
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ,./;''[]\`~!@#$%^\*()__+{}-?><', '0123456789' ), ' ', '') FROM DUAL; Is it useful .. enSoy madi

SP for removing Specail characters

v_nam := replace(v_nam,'''');

v_nam := replace(v_nam,';');

v_nam := utl_i18n.escape_reference(v_nam,'us7ascii');

DECLARE

v_pos1 NUMBER;

v_pos2 NUMBER;

v_pointer NUMBER;

BEGIN

v_pointer :=1;

LOOP

v_pos1:=0;

v_pos2:=0;

v_pos1 :=INSTR(v_nam,'&',v_pointer);

v_pos2 :=INSTR(v_nam,';',v_pointer);

EXIT WHEN v_pointer > 360 OR v_pos1 =0 OR v_pos2 = 0; --Party Name max size is 360; v_nam := SUBSTR(v_nam,1,v_pos1-1)SUBSTR(v_nam,v_pos2+1);

v_pointer:= v_pos2+1;

END LOOP;

END;

How to display Html Tags in Blogs

Replace < symbol with & l t ;
Replace > symbol with & g t ;
Replace " symbol with & q u o t ;
Add <br/> end of each line

in your text that's it it :-)
Note : Remove spaces between &-;





Visit http://www.plus2net.com/html_tutorial/tags-page.php for online convertor

Trace for a specific user in Oracle APPS

Oracle Applications Trace for a specific user. This describes the method for running a trace for a specific user in Oracle application. The Oracle Applications modules impacted are the JTF Framework packages like iSupport, iSupplier etc. This method enables us to trace self service applications and debug user specific error logging issues.
How to create a Database Trace for a specific user.
This is to explain how to to create a database trace for a specific user. This process can be used to trace any action, anywhere in Oracle Applications and be very useful for the Self-Service Web Applications as there is no utility defined like in the Forms.
a. First make sure the necessary profile has the proper permissions. 1. Log onto the Applications Forms with the Application Developer Responsibility 2. Navigate to the Profile menu 3. Query up the profile name "FND_INIT_SQL" 4. In the bottom block of the form, make sure that ALL checkboxes are checked
Typically, you will have to enable the checkboxes under "User Access" to make it "Visible" and "Updatable".
b. Now switch to the System Administrator Responsibility
1. Navigate to - Profile - System 2. On the "Find System Profile Values" form, make sure the checkboxes for "User" and "Profiles with no Values" are checked 3. Beside the "User" checkbox, use the LOV to select the user who's activity you need to trace 4. In the "Profile" field, enter the following profile and click the button: 'Initialization SQL Statement - Custom' 5. In the "System Profile Values" form, enter the following in the User Field: (This is one line and all single quotes)
begin fnd_ctl.fnd_sess_ctl('','','TRUE','TRUE','LOG','ALTER SESSION SET EVENTS=''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'' tracefile_identifier=''OracleSupport'''); end;
6. DO NOT SAVE THE PROFILE YET
7. In another browser window, login as the user you are going to trace and prepare to reproduce the problem 8. Once you are ready to reproduce the problem, go back to the Applications Forms and Save the profile change 9. Reproduce the problem 10. Back in the Applications form, set profile to null so it does not trace anymore and Save the change 11. The trace will be located in the user_dump_dest. To find location run the following in SQL*Plus: select value from v$parameter where name = 'user_dump_dest'; 12. The trace file will have current date/time and can be identified with the word OracleSupport in it.

Sys.xmlType datatype and parsing XML

Oracle9i onwards there is new datatype called sys.xmlType. This provides a great way to handle XML documents with minimal or no parsing required.

Listed below is a sample of how to use the xmlType.

Step 1. Create Table of XML type

CREATE TABLE xml_table( xml_col SYS.XMLTYPE );
Step 2. Insert the following XML into the xml_table defined above
<?xml version="1.0"?>
<email>
<from>xyz@gmail.com</from>
<to>xyz1@gmail.com</to>
<subject>some subject</subject>
<body>some body</body>
</email>

declare

l_temp sys.xmlType;

begin

l_temp := sys.xmlType.createXML('

<?xml version="1.0"?>
<email>
<from>xyz@gmail.com</from>
<to>xyz1@gmail.com</to>
<subject>some subject</subject>
<body>some body</body>
</email>'
);

insert into xml_table values ( l_Temp);

Commit;

end;

Step 3. Write select query to extract data from this table. Sample extract query listed below.

select a.xml_col.extract("//from/text()").getStringVal() as from from xml_table a

FND_MSG_PUB api in Oracle Applications

FND_MSG_PUB for error logging in Oracle Applications.

Use the following snipped of code while calling any seeded API in Oracle Applications, like CRM, or Financials.

Put this block exactly under the section where you invoke the private or public API. The errors raised in the API are logged in the following API. Use this code while error logging or while debugging. Comment out the code before moving into production.

IF ( FND_MSG_PUB.Count_Msg > 0) THEN
FOR i IN 1..FND_MSG_PUB.Count_Msg LOOP
FND_MSG_PUB.Get(p_msg_index => i,
p_encoded => 'F',
p_data => out_message,
p_msg_index_OUT => l_msg_index_OUT );
dbms_output.put_line('l_msg_data :' ||out_message);
END LOOP;
END IF;

Adding message to message stack is
FND_MSG_PUB.add

Retrieving HR Person ID from Email address

Retrieving HR Person ID from Email address in Oracle Applications 11i eBusiness Suite.

Use the following query to retrieve the HR person ID for any employee by using the email address. This can be used in any modules of Oracle Applications 11i.

SELECT person_id FROM per_people_x WHERE
UPPER(email_address) = p_email
and current_employee_flag = 'Y';

Registering Table in Oracle.

Registering a table in Oracle Applications 11i Ebusiness suite to be used in Alerts and Flexfields
Flexfields and Oracle Alert are the only features or products that require the custom tables to be registered in Oracle Applications (Application Object Library) before they can be used. Custom application tables can be registered by using the AD_DD PL/SQL Package. If you are planning to use custom tables either in Alerts or in Flexfields, they need to be registered. The following methods describe how you can register the tables.
ADD_DD.REGISTER_TABLE
ad_dd.register_table ('Application short name', 'EMAIL_TEMPLATES', 'T');
ADD_DD.REGISTER_COLUMN
ad_dd.register_column ('Application short name','EMAIL_TEMPLATES','EMAIL_TEMPLATE_ID',--Column1,--Sequence'number',--type4,--width'N','N');
Register all the columns one by one . Now you can use these custom table in your flex field definitions and in Oracle Alerts

Oralce apps Modules and their Short Names

Use the following query to retrieve this information on your specific instance.

SELECT FND.APPLICATION_ID, APPLICATION_SHORT_NAME, PRODUCT_CODE, APPLICATION_NAME
FROM FND_APPLICATION FND , FND_APPLICATION_TL FNDTL WHERE FND.APPLICATION_ID=FNDTL.APPLICATION_ID

CallingWebServices From Oracle using UTL_HTTP

Calling Web Services from Oracle 9i or 10g using UTL_HTTP package in PLSQLBy using PL/SQL we eliminate the need to write wrapper API's and the Web Service calls are reduced to simple PL/SQL Database selects statements. This way the Web Service calls can be natively used within PL/SQL functions, procedures etc. In addition there is no need to build new Web Service calls for client layers (forms, jsps etc).
First Identify the webservice and the "envelope" that you need to post to the webservice using UTL_HTTP post method. In this example, I am using the following WebService

http://www.webservicex.net/stockquote.asmx?op=GetQuote

The WebService is expecting the following Envelope for the GetQuote Method

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<GetQuote xmlns="http://www.webserviceX.NET/">
<symbol>
string</symbol>
</GetQuote>
</soap:Body>
</soap:Envelope>


PL/SQL routine for invoking the webservice
create or replace FUNCTION WS_QUOTE( symbol in varchar2) RETURN sys.xmltypeas env VARCHAR2(32767); http_req utl_http.req; http_resp utl_http.resp; resp sys.xmltype; in_xml sys.xmltype; url varchar2(2000):='http://www.webservicex.net/stockquote.asmx?WSDL'; BEGIN-- generate_envelope(req, env);

env:='<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<GetQuote xmlns="http://www.webserviceX.NET/">
<symbol>' symbol '</symbol>
</GetQuote>
</soap:Body>
</soap:Envelope>';
http_req := utl_http.begin_request(url, 'POST','HTTP/1.1'); utl_http.set_body_charset(http_req, 'UTF-8');-- utl_http.set_proxy('proxy:80', NULL);-- utl_http.set_persistent_conn_support(TRUE);-- UTL_HTTP.set_authentication(http_req, '', '3', 'Basic', TRUE ); utl_http.set_header(http_req, 'Content-Type', 'text/xml'); utl_http.set_header(http_req, 'Content-Length', length(env)); utl_http.set_header(http_req, 'SOAPAction', 'http://www.webserviceX.NET/GetQuote'); utl_http.write_text(http_req, env); http_resp := utl_http.get_response(http_req); utl_http.read_text(http_resp, env); utl_http.end_response(http_resp); in_xml := sys.xmltype.createxml(env); resp := xmltype.createxml(env); dbms_output.put_line('same output'); dbms_output.put_line(SUBSTR(env, 1, 245)); RETURN resp; END; Observe here that SYMBOL is the only parameter required for this function. All other is required just to create the envelope.
Sample extract Query for extracting the information
select extract(ws_quote('GOOG'),'//GetQuoteResult/text()',
'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"').getStringVal() from dual
Using this technique, all webservices calls are reduced to simple queries in the database and can be used to virtualize the database.

FND_GLOBAL.APPS_INITIALIZE for initializing

FND_GLOBAL.APPS_INITIALIZE is used for initializing the session before calling any public or private API's in Oracle Ebusiness suite.

Its not required for all the API's but its recommended that you set this profile before making any calls to either private or public API.

Listed below is a sample call to FND_GLOBAL.APPS_INITIALIZE function

fnd_global.APPS_INITIALIZE(user_id=>l_user_id,
resp_id=>l_resp_id,
resp_appl_id=>l_resp_appl_id);

l_user_id is the fnd user ID which will be utilized during the call.
l_resp_id is the responsibility ID
l_resp_appl_id is the responsibility application ID.

SQL to get the above values for particular user and Resp

select fnd.user_id ,
fresp.responsibility_id,
fresp.application_id
from fnd_user fnd
, fnd_responsibility_tl fresp
where fnd.user_name = :User Name
and fresp.responsibility_name = :Responsibility;

Another option is Help > Diagnostics > Examine and get the values from $profile$

Concurrent requests in Oracle

Use the following query to list all the concurrent requests in Oracle



select a.request_id,a.request_date,b.user_concurrent_program_name, a.requested_by,a.responsibility_application_id, a.responsibility_id,
a.completion_text, a.logfile_name from FND_CONCURRENT_REQUESTS a, FND_CONCURRENT_PROGRAMS_TL b where a.concurrent_program_id=b.concurrent_program_id
and a.status_code=:status
and a.request_date > :Date
order by a.request_date desc

Custom.pll commands

Convert .pll to .pld
f60gen MODULE=CUSTOM USERID=apps/ MODULE_TYPE=LIBRARY SCRIPT=YES

Convert .pld to .pll

f60gen MODULE=CUSTOM USERID=apps/ MODULE_TYPE=LIBRARY PARSE=YES

Compile all modules

f60gen MODULE=CUSTOM USERID=apps/ MODULE_TYPE=LIBRARY COMPILE_ALL=yes

User Hooks in Oracle application

User hooks provide the client with the ability to add logic to application processing and to disable optional product processing. These User Hooks take the form of procedures that may be called by the application, in sequence, when the application takes a specified action on a specified object type.

Not all the Oracle Applications API's have user hooks. The API's which have users hooks are listed in the following table

jtf_user_hooks.

So if you are looking to modify/customize a particular API, look for the that API in the the above table.

For example,

select * from jtf_user_hooks where API_NAME = 'CANCEL_ORDER'

Just like triggers, User Hooks can be made to fire pre/post. In order to set a user hook as active , the execute flag has to be set to 'Y'.

Responsibilities by User in Oracle Apps eBusiness Suite.

List Responsibilities by User in Oracle Apps eBusiness Suite. Use the following query to list the responsibilities assigned to a particular user in Oracle Applications (Ebusiness Suite)

SELECT FNDRESP.* FROM fnd_user fnduser, fnd_user_resp_groups FNDRESPGROUP, fnd_responsibility_TL FNDRESP WHERE
fnduser.user_id=FNDRESPGROUP.user_id
AND FNDRESP.responsibility_id=FNDRESPGROUP.responsibility_id
and upper(fnduser.user_name) like upper('%your user%');

Bouncing Apache in Oracle Applications 11i

Bouncing Apache in Oracle Applications 11i.

In case you are doing development in Oracle Applications 11i and are working on JTT Framework or OAFramework, then you will need to bounce the Apache in order for your changes to be picked up. This is required for "Self Service Applications" and may not apply to forms 6i or oracle reports development.

In order to bounce the Apache, you will have to request the DBA access to the MidTier and the required privileges to bounce.

The script that does the bouncing for you is adapcctl.sh . Its commonly located under $COMMON_TOP/admin/scripts

Command for executing the bounce are

$ adapcctl.sh stop

$ adapcctl.sh start

Now there are cases when only bouncing is not enough and you might have to clear the cache. JSP cache is created each time SSA Applications are accessed in Oracle 11i. Its wise to clear the cache to be on the safer side.

The cache is located under

$OA_HTML/_pages/_oa__html

Location of Cache/JSP Cache for jServ / Apache

Here is where the JSERV cache is located

$OA_HTML/_pages/_oa__html

Clear this Cache, especially when working on the login page. This ensures that the page is recompile each time.

Related to , Oracle Appliacations 11i, Cache, Self Service Applications and OA Framework.

Forward Mails: World in Short

Worldwide survey was conducted by the UN. The only question asked was: "Would you please give your honest opinion about solutions to the food shortage in the rest of the world?"

The survey was a huge failure,
In Africa they didn't know what 'food' meant,
In India they didn't know what 'honest' meant,
In Europe they didn't know what 'shortage' meant,
In China they didn't know what 'opinion' meant,
In the Middle East they didn't know what 'solution' meant,
In South America they didn't know what 'please' meant,

And in the USA they didn't know what 'the rest of the world' meant!

Medical Tip: Probiotic Dietary Supplement

Florajen (Acidophilus)

For Antibiotic Side Effeects (Vaginitis Diarhea)

Antibiotics are usually taken to kill harmful bacteria causing a disease. Unfortunately, antibiotics simultaneously kill the good bacteria the body needs to stay healthy.

The result can be diarrhea or Vaginitis.

Thes unpleasant side effects can cause patients to stop taking their prescribed antibiotics, and harmful bacteria might not be completely eliminated.

Florajen(Acidophilus) can help ensure that patients take the full course of their antibiotic prescriptions by restoring the body's natural microbial balance.

Baby Tips: Meaning for Different Sounds

Usually people find difficult to identify what exactly baby needs when she is crying. In general they do different sounds depending on their needs ...

For example :

Eh : I need to burp

Neh : I'm Hungry

Heh : I'm uncomfortable

Ow: I'm tired

Eairh: I have Gas

Short Film: Cute One

Few more reasons to Quit Smoking

Happy Days -- In Short

Hanuman

Hanuman Chalisa -Short Film

Video Source : Mega Video

Bhagvad Gita -- In Telugu

Video Source : Mega Video

Telugu Film :Pathala Bhairavudu

Video Source : Mega Video