Monday, December 31, 2007

FND_GLOBAL.APPS_INITIALIZE for initializing session in Oracle Ebusiness suite

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

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

You can use either sysadmin or use some user who has all the above listed responsibilities.

For SYSADMIN, utilize the following query to get the respective values

select fnd.user_id ,
       fresp.responsibility_id,
       fresp.application_id
from   fnd_user fnd
,      fnd_responsibility_tl fresp
where  fnd.user_name = 'SYSADMIN'
and    fresp.responsibility_name = 'Order Management Super User';

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

Sunday, December 30, 2007

Business Events Oracle, step by step guide

Business Events Oracle, step by step guide.

Described below is a sample of setting up Business Events in Oracle Applications ( 11.5.10) onwards.

The first step to setting up business events is to making sure you have the right responsibilities. You will have to request the system administrator to provide you "Work Flow Administrator" responsibility.

Work Flow Administrator --> Business Events --> Search

Image1

Search for the events you want to attach custom logic to. In this example, the event used is

oracle.apps.jtf.cac.task.createTask

Each time a new task gets created the events will get triggered. Attach the custom pl/sql function that you want to fire associated with this event.

Image2

Now the next step is defining the custom function handling the logic. The standard signature of all subscription functions is

myfunc(p_guid in RAW, p_event in ou noCopy WF_EVENT_T) return varchar2.

You can get the values passed by the event using GeValueForParameter();

CREATE OR REPLACE PACKAGE BODY APPS.test_BE_Pkg IS

FUNCTION my_test_task_business_event(p_subscrition_guid IN RAW,

p_event IN OUT NOCOPY WF_EVENT_T )RETURN VARCHAR2 IS

l_task_id NUMBER;

l_task_status VARCHAR2(30);

l_task_num VARCHAR2(30);

l_event_name VARCHAR2(240) := p_event.getEventName();

BEGIN

l_task_id := p_event.GetValueForParameter('TASK_ID');

INSERT INTO my_temp VALUES ( l_task_id ,l_task_id);

COMMIT;

SELECT sts.NAME,

a.task_number

INTO l_task_status ,

l_task_num

FROM jtf_task_statuses_vl sts,

jtf_tasks_b a

WHERE a.task_id = l_task_id

AND a.task_status_id = sts.task_status_id;

INSERT INTO my_temp VALUES(l_task_num,l_task_status);

RETURN 'SUCCESS';

COMMIT;

END my_test_task_business_event;

END test_BE_Pkg;

/

Friday, December 28, 2007

Invoking WebServices From Oracle 9i or 10g using UTL_HTTP

Invoking WebServices From Oracle 9i or 10g using UTL_HTTP

Here is the a very simple method to invoke WebServices from Oracle 9i or 10g using the UTL_HTTP method.

  • 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.xmltype
    as
        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.

Oracle Applications 11i Modules and their Short Names (Abbreviations)

Oracle Applications 11i Modules and their Short Names (Abbreviations)

Listed below are the various Oracle Application Modules, their short names and their App ID for reference

APPLICATION_SHORT_NAME APPLICATION_ID APPLICATION_NAME
FND 0 Application Object Library
SYSADMIN 1 System Administration
AU 3 Application Utilities
AD 50 Applications DBA
SHT 60 Applications Shared Technology
SQLGL 101 General Ledger
OFA 140 Assets
ALR 160 Alert
RG 168 Application Report Generator
CS 170 Service
CCT 172 Telephony Manager
ECX 174 XML Gateway
EC 175 e-Commerce Gateway
ICX 178 Self-Service Web Applications
XTR 185 Treasury
AZ 190 Application Implementation
BIS 191 Applications BIS
SQLAP 200 Payables
PO 201 Purchasing
CHV 202 Supplier Scheduling
AR 222 Receivables
PN 240 Property Manager
QA 250 Quality
CE 260 Cash Management
FRM 265 Report Manager
EAA 270 SEM Exchange
BSC 271 Balanced Scorecard
ABM 272 Activity Based Management
EVM 273 Value Based Management
FEM 274 Strategic Enterprise Management
PA 275 Projects
AS 279 Sales Foundation
CN 283 Incentive Compensation
POM 298 Exchange
OE 300 Order Entry
WMS 385 Warehouse Management
WPS 388 Manufacturing Scheduling
INV 401 Inventory
MWA 405 Mobile Applications
WSM 410 Shop Floor Management
FII 450 Financial Intelligence
OPI 451 Operations Intelligence
POA 452 Purchasing Intelligence
HRI 453 Human Resources Intelligence
ISC 454 Supply Chain Intelligence
OKC 510 Contracts Core
CSC 511 Customer Care
CSD 512 Depot Repair
CSF 513 Field Service
CSS 514 Support
OKS 515 Service Contracts
ME 516 Controlled Availability Product
BIM 517 Marketing Intelligence
BIC 518 Customer Intelligence
IES 519 Scripting
AMV 520 Marketing Encyclopedia System
AST 521 TeleSales
ASF 522 Sales Online
CSP 523 Spares Management
OKX 524 Contracts Integration
AMS 530 Marketing
XNM 531 Marketing for Communications
XNC 532 Sales for Communications
XNS 533 Service for Communications
XNP 534 Number Portability
XDP 535 Provisioning
FPT 538 Banking Center
IEO 539 Interaction Center Technology
GMA 550 Process Manufacturing Systems
GMI 551 Process Manufacturing Inventory
GMD 552 Process Manufacturing Product Development
GME 553 Process Manufacturing Process Execution
GMP 554 Process Manufacturing Process Planning
GMF 555 Process Manufacturing Financials
GML 556 Process Manufacturing Logistics
GR 557 Process Manufacturing Regulatory Management
PMI 558 Process Manufacturing Intelligence
AX 600 Global Accounting Engine
AK 601 Common Modules-AK
XLA 602 Subledger Accounting
ONT 660 Order Management
QP 661 Advanced Pricing
RLM 662 Release Management
VEA 663 Automotive
WSH 665 Shipping Execution
IBA 670 iMarketing
IBE 671 iStore
IBU 672 iSupport
IBY 673 iPayment
IBP 674 Bill Presentment & Payment
BIL 676 Sales Intelligence
BIX 677 Interaction Center Intelligence
IEM 680 Email Center
OZP 681 Trade Planning
OZF 682 Trade Management
OZS 683 iClaims
ASG 689 CRM Gateway for Mobile Devices
JTF 690 CRM Foundation
IEX 695 Collections
IEU 696 Universal Work Queue
ASO 697 Order Capture
CSR 698 Scheduler
IEB 699 Interaction Blending
MFG 700 Manufacturing
BOM 702 Bills of Material
ENG 703 Engineering
MRP 704 Master Scheduling/MRP
CRP 705 Capacity
WIP 706 Work in Process
CZ 708 Configurator
RLA 710 Release Management Integration Kit
VEH 711 Automotive Integration Kit
PJM 712 Project Manufacturing
FLM 714 Flow Manufacturing
MSD 722 Demand Planning
MSO 723 Constraint Based Optimization
MSC 724 Advanced Supply Chain Planning
RHX 725 Advanced Planning Foundation
OKE 777 Project Contracts
PER 800 Human Resources
PAY 801 Payroll
FF 802 FastFormula
DT 803 DateTrack
SSP 804 SSP
BEN 805 Advanced Benefits
HXT 808 Time and Labor
HXC 809 Time and Labor Engine
OTA 810 Learning Management
JA 7000 Asia/Pacific Localizations
JE 7002 European Localizations
JG 7003 Regional Localizations
JL 7004 Latin America Localizations
GHR 8301 US Federal Human Resources
PQH 8302 Public Sector HR
PQP 8303 Public Sector Payroll
PSB 8401 Public Sector Budgeting
GMS 8402 Grants Accounting
PSP 8403 Labor Distribution
IGW 8404 Grants Proposal
IGS 8405 Student Systems
IGF 8406 Financial Aid
IGC 8407 Contract Commitment
PSA 8450 Public Sector Financials
IPA 8721 Capital Resource Logistics - Projects
CUI 8722 Network Logistics - Inventory
CUP 8723 Network Logistics - Purchasing
CUF 8724 Capital Resource Logistics - Financials
CUS 8727 Network Logistics
CUN 8729 Network Logistics - NATS
CUA 8731 Capital Resource Logistics - Assets
FV 8901 Federal Financials
IMC 879 Customers Online
XNI 872 Install Base Intelligence
POS 177 iSupplier Portal
AHM 864 Hosting Manager
ASP 869 Field Sales/Palm Devices
BIV 862 Service Intelligence
CSI 542 Install Base
PV 691 Partner Management
ASL 544 Sales Offline
EAM 426 Enterprise Asset Management
FTE 716 Transportation Execution
IGI 8400 Public Sector Financials International
ITG 230 Internet Procurement Enterprise Connector
MSR 726 Inventory Optimization
IPD 420 Product Development
ENI 455 Product Intelligence
CUE 543 Billing Connect
OKR 541 Contracts for Rights
IZU 278 Oracle Support Diagnostic Tools
CSL 868 Field Service/Laptop
CUG 866 Citizen Interaction Center
IMT 861 iMeeting (obsolete)
OKI 870 Contracts Intelligence
IEC 545 Advanced Outbound Telephony
CSE 873 Enterprise Install Base
OKO 871 Contracts for Sales
JTS 875 CRM Self Service Administration
JTM 874 Mobile Application Foundation
AHL 867 Complex Maintenance Repair and Overhaul
OKB 865 Contracts for Subscriptions (obsolete)
BNE 231 Web Applications Desktop Integrator
QRM 186 Risk Management
PON 396 Sourcing
OKL 540 Lease Management
IBC 549 Content Manager
AMF 882 Fulfillment Services
QOT 880 Quoting
CSM 883 Field Service/Palm
DOM 432 Document Managment and Collaboration
EGO 431 Advanced Product Catalog
DDD 430 CADView-3D
PJI 1292 Project Intelligence
XDO 603 XML Publisher
XNB 881 eBusiness Billing
ZFA 505 Financial Analyzer
ZSA 506 Sales Analyzer
CLN 701 Supply Chain Trading Connector for RosettaNet
EDR 709 E-Records
PRP 694 Proposals
AMW 242 Internal Controls Manager
XLE 204 Legal Entity Configurator
ASN 280 Sales
MST 390 Transportation Planning
FUN 435 Financials Common Modules
GCS 266 Global Consolidation System
ZX 235 E-Business Tax
LNS 206 Loans
IA 205 iAssets
FPA 440 Portfolio Analyzer
ZPB 210 Enterprise Planning and Budgeting

 

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

Thursday, December 20, 2007

Tracking Oracle Installed base error transactions

Most of the Oracle Installed base error transactions reside in

csi_txn_errors table. source_header_ref_id is nothing but the order header ID. In case it is required to get the IB failed transactions based on the order number, one can utilize the following query.

select * from csi_txn_errors csie, oe_order_headers_all_b oedh where
csie.source_header_ref_id = oedh.header_id and oedh.order_number= &order_number

Tuesday, June 19, 2007

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

Monday, June 18, 2007

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;

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

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: , , , , ,

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