Registering a table AD_DD.register_table in Oracle Applications 11i Ebusiness suite

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',--Column
1,--Sequence
'number',--type
4,--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

Setting proxy in UTL_HTTP package method calls for Oracle 9i, 10g

Oracle provides PL/SQL  UTL_HTTP package for calling HTTP from Oracle database. While using UTL_HTTP, it is required to set the proxy settings when calling a URL which is out side the firewall or beyond the proxy. In order to set the proxy, Oracle provides the following methods to set the proxy information.

Utl_Http.Set_Proxy (
  proxy => 'yourproxy',
  no_proxy_domains => 'yourdomains' );

req := Utl_Http.Begin_Request (
  url => v_url,
  method => 'GET' );

Utl_Http.Set_Authentication (
  r => req,
  username => 'username',

  password => 'password',
  scheme => 'Basic',
  for_proxy => false );

Technorati Tags: Oracle, oracle 10g, Oracle 9i, proxy, UTL_HTTP, webservices

Querying group name from JTF_RS_GROUPS_VL using Group ID in Oracle 11i Ebusiness suite

SELECT group_name FROM jtf_rs_groups_vl WHERE group_id = p_group_id;

pass p_group_id as the parameter.

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

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';

jtf_notes used for Notes module in Oracle

jtf_notes used for Notes module in Oracle

Oracle Applications use jtf_notes for storing all notes related information across various modules. The tables used are jtf_notes_B, jtf_note_types. There is a translation table called jtf_notes_tl

FND_MSG_PUB for error logging 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;

Technorati Tags: CRM, fnd_msg_pub, iAS, Oracle Apps, service request, teleservices

Calling cs_servicerequest_pvt for updating service request in Oracle

Calling cs_servicerequest_pvt for updating service request in Oracle Applications

cs_servicerequest_pvt.Update_ServiceRequest(
    P_API_VERSION                   => 3.0,
    P_INIT_MSG_LIST                 => FND_API.G_TRUE,
    P_COMMIT                        => FND_API.G_FALSE,
    P_VALIDATION_LEVEL              => fnd_api.G_VALID_LEVEL_NONE ,
    X_RETURN_STATUS                 => out_status,
    X_MSG_COUNT                     => x_msg_count,
    X_MSG_DATA                      => out_message,
    P_REQUEST_ID                    => x_sr_id,      --in_request_id ,
    P_OBJECT_VERSION_NUMBER         => m_obj_version,
    P_LAST_UPDATED_BY               => m_user_id,
    P_LAST_UPDATE_DATE              => m_update_date,
    P_SERVICE_REQUEST_REC           => m_sr_rec_upd,
    P_NOTES                         => m_notes_tbl,
    P_CONTACTS                      => m_contacts_tbl,
    X_INTERACTION_ID                => X_INTER_ID,
    X_WORKFLOW_PROCESS_ID           => X_WORKFLOW_ID);

Calling cs_servicerequest_pvt API for create service request

Calling cs_servicerequest_pvt API for create service request in Oracle 11i.

cs_servicerequest_pvt.Create_ServiceRequest(
        P_API_VERSION           => 3.0,
        P_INIT_MSG_LIST         => FND_API.G_TRUE,
        P_COMMIT                => FND_API.G_FALSE,
        P_VALIDATION_LEVEL      => fnd_api.g_valid_level_full ,
        X_RETURN_STATUS         => out_status,
        X_MSG_COUNT             => x_msg_count,
        X_MSG_DATA              => out_message,
        P_RESP_APPL_ID          => NULL,
        P_ORG_ID                => NULL,
        P_REQUEST_NUMBER       =>  in_request_number,
        P_SERVICE_REQUEST_REC   => m_sr_rec,
        P_NOTES                 => m_notes_tbl,
        P_CONTACTS              => m_contacts_tbl,
        P_USER_ID               => m_user_id,
        P_RESP_ID               => in_respId ,
        X_REQUEST_ID            => x_sr_id,
        P_AUTO_ASSIGN           => in_autoassign,
        X_REQUEST_NUMBER        => out_sr_number,
        X_INTERACTION_ID        => x_inter_id,
        X_WORKFLOW_PROCESS_ID   => x_workflow_id,
        X_INDIVIDUAL_OWNER      =>P_INDIVIDUAL_OWNER,
        X_GROUP_OWNER           =>P_GROUP_OWNER,
        X_INDIVIDUAL_TYPE       =>P_INDIVIDUAL_TYPE );

Technorati Tags: Oracle, Oracle Apps, service request

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.

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

Adding a new JSP to existing Oracle Applications

Adding a new JSP to existing Oracle Applications menu. JSP can be added under any responsibility. First define the menu as a JSP Function and then add it to the respective responsibility.

1. Log into to Application developer . Define the region if you want.  Say IBU_SUPPORT_MENU and add the prompt and function name here.

2. Create a new function

3. Define it as a SSWA jsp function and give the JSP name

4. Find the menu under which you want to add this function.

5. Run concurrent request/ bounce the apache.

Trim SR numbers and other parameters in JSP

This can be used to trimming SR or other data that is passed to JSP's

function trim(strText) {
    // this will get rid of leading spaces
    while (strText.substring(0,1) == ' ')
        strText = strText.substring(1, strText.length);

    // this will get rid of trailing spaces
    while (strText.substring(strText.length-1,strText.length) == ' ')
        strText = strText.substring(0, strText.length-1);

   return strText;
}

Oracle 9iAS : Location of logs files

Apache, Jserv Logs files and where are log files locatoin on the file system?

error_log is in $APACHE_TOP/Apache/logs/ (ie. /oracle/visora/iAS/Apache/Apache/logs) access_log is in $APACHE_TOP/Apache/logs/ (ie. /oracle/visora/iAS/Apache/Apache/logs) mod_jserv_log is in $APACHE_TOP/Jserv/logs/ (ie. /oracle/visora/iAS/Apache/Jserv/logs) jserv_log is in $APACHE_TOP/Jserv/logs/ (ie. /oracle/visora/iAS/Apache/Jserv/logs)

Information  written to the Apache/JServ Log files?

error_log: Contains a record of errors encountered by the Apache server. access_log: Contains a record of the URLs requested from the Apache server. mod_jserv_log: Contains a record of messages and errors encountered by JServ (c-side). jserv_log: Contains a record of messages and errors encountered by Java (Java-side).

 

Technorati Tags: 11i, 9iAS, Ebusiness Suite, iAS, loggin, logging

Enabling Loggin in Oracle iAS

Enabling Loggin in Oracle 9iAS.

Steps involved

1. Navigate to jserv.properties. Typically its located under $ORACLE_HOME/Apache/Jserv/etc/jserv.properties

2. Verify log = true

3. log.file =~/jserv.properties

verify that the log file has proper permissions (Basically 777)

Useful links http://www.bupaireland.ie/contactus/jserv.properties

Oracle Applications Trace for a specific user

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 <Find> 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.

Parameterized views in Oracle

Parameterized views in Oracle. It is not possible to create Parameterized Views in Oracle. In order to create Parameterized one has to make use of some work around like sys_context.

create context params using setmm;

create package setmm is
procedure minmax(vmin in number, vmax in number);
end setmm;
/

create package body setmm is
procedure minmax(vmin in number, vmax in number) is
begin
dbms_session.set_context(
namespace => 'params', attribute => 'minval', value => vmin);
dbms_session.set_context(
namespace => 'params', attribute => 'maxval', value => vmax);
end minmax;
end setmm;
/

create view parametrized as
  select * from (select level n from dual connect by level <= 1000)
  where n between to_number(sys_context('params', 'minval'))

and to_number(sys_context('params', 'maxval'));

SQL> exec setmm.minmax(vmin => 5, vmax => 14)

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

SQL> select * from parametrized;
5
6
7
8
9
10
11
12
13
14

SQL> exec setmm.minmax(vmin => 2, vmax => 4)

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

SQL> select * from parametrized;
2
3
4

CSZ_SR_T2_AGENT_ROOT_MENU

CSZ_SR_T2_AGENT_ROOT_MENU is the root menu for customer support specialist responsibility for 11.5.10. The function associated with the menu is CSZ_SR_T2_H_FN.

The user function name is Customer Support Specialist

Oracle Apps Query to list functions, responsibilities and user

Oracle Apps Query to list the functions, responsibilities and the users assigned to these responsibilities. Number of times, we need to find out the list of users having access to a perticular user function in Oracle applications. The following query can be used to produce this report.

SELECT DISTINCT
u.user_name, rtl.RESPONSIBILITY_NAME, ff.function_name, ffl.user_function_name
FROM fnd_compiled_menu_functions cmf
, fnd_form_functions ff
, fnd_form_functions_tl ffl
, fnd_responsibility r
, fnd_responsibility_TL rtl
, fnd_user_resp_groups urg
, fnd_user u
WHERE cmf.function_id = ff.function_id
AND r.menu_id = cmf.menu_id
AND urg.responsibility_id = r.responsibility_id
AND rtl.responsibility_id = r.responsibility_id
AND cmf.GRANT_FLAG='Y'
and r.APPLICATION_ID=urg.RESPONSIBILITY_APPLICATION_ID
AND u.user_id = urg.user_id
--and ff.function_id=19438
and upper(ffl.user_function_name) like upper('Agent%Dashboard')
and ff.function_id=ffl.function_id
order by u.user_name