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