Oracle CRM Blog

Oracle Service Contracts User Guide

2008-04-25T14:30:00.001-07:00

Oracle Service contracts user guide can be found here

oks_reprocessing , query failed transactions

2008-04-25T14:26:00.001-07:00

select order_number
from oks_reprocessing a
where a.success_flag = 'E' and order_number not in (Select nvl(source_header_ref,'11111') from csi_txn_errors)

List the orders that have failed in transaction and have an entry in the CSI_TXN_ERRORS table.

Query Serial Number for an Order in Oracle

2008-03-27T14:49:00.001-07:00

SELECT wl.meaning release_status
     , wdd.released_status
     , wdd.source_line_id
     , wdd.source_code
     , wser.fm_serial_number
     , wser.TO_SERIAL_NUMBER
     , wdd.CREATION_DATE
     , oelines.header_id
     , oeheader.sold_to_org_id
     , wdd.shipped_quantity
     , wdd.delivery_detail_id
FROM wsh_delivery_details wdd
     , apps.wsh_lookups wl
     , OE_ORDER_LINES_ALL oelines
     , oe_order_headers_all oeheader
     , wsh_serial_numbers wser
WHERE 1 = 1
   AND wl.lookup_type = 'PICK_STATUS'
   AND wl.lookup_code = wdd.released_status
   AND wdd.source_code = 'OE'
   AND wdd.source_line_id=oelines.line_id
   and oelines.header_id = oeheader.header_id
   AND WDD.DELIVERY_DETAIL_ID=wser.delivery_detail_id

List all concurrent requests in Oracle

2008-01-18T14:14:00.001-08:00

Use the following query to list all the concurrent requests in Oracle which are in errored or error status.

Modify the query to get the reports that you want

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='E' and a.request_date > to_date('13-jan-2008','DD-MON-YYYY') order by a.request_date desc

Custom.pll

2008-01-10T14:03:00.001-08:00

Steps to convert/compile custom.pll files.

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

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

Compile all modules
f60gen MODULE=CUSTOM USERID=apps/sldfkjns5 MODULE_TYPE=LIBRARY 
COMPILE_ALL=yes

Copy the pll and plx back to $AU_TOP/resource
Then bounce forms port (f60down and f60up should not be used as because it kills existing connections)

List Responsibilities by User in Oracle Apps eBusiness Suite.

2008-01-07T11:31:00.001-08:00

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

Sys.xmlType datatype and parsing XML in Oracle9i, 10g onwards

2008-01-03T21:23:00.001-08:00

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

User Hooks in Oracle

2008-01-02T10:33:00.001-08:00

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

FND_GLOBAL.APPS_INITIALIZE for initializing session in Oracle Ebusiness suite

2007-12-31T21:13:00.001-08:00

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.

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.

Business Events Oracle, step by step guide

2007-12-30T21:11:00.001-08:00

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

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.

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;

Invoking WebServices From Oracle 9i or 10g using UTL_HTTP

2007-12-28T17:27:00.001-08:00

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)

2007-12-28T12:24:00.001-08:00

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

Tracking Oracle Installed base error transactions

2007-12-20T16:27:00.001-08:00

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

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

2007-06-19T18:28:00.001-07:00

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

2007-06-19T07:52:00.001-07:00

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

2007-06-18T22:09:00.001-07:00

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

2007-06-18T22:05:00.001-07:00

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

2007-06-18T19:00:00.001-07:00

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

2007-06-18T18:49:00.001-07:00

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

2007-06-18T18:35:00.001-07:00

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

2007-06-18T18:33:00.001-07:00

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

2007-06-18T17:21:00.001-07:00

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

2007-06-18T17:20:00.001-07:00

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

2007-06-18T17:11:00.001-07:00

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

2007-06-18T17:02:00.001-07:00

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

2007-06-18T17:00:00.001-07:00

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

2007-06-18T16:58:00.001-07:00

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

2007-06-18T16:57:00.001-07:00

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

2007-06-18T13:38:00.000-07:00

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

2007-06-18T13:15:00.000-07:00

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

2007-06-18T12:55:00.000-07:00

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

Calling Web Services from Oracle 9i or 10g using UTL_HTTP

2006-12-29T22:35:00.000-08:00

Calling Web Services from Oracle 9i or 10g using UTL_HTTP package in PLSQL

By 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).