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.

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

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

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

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