Oracle Service contracts user guide can be found here
April 25, 2008
oks_reprocessing , query failed transactions
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.
March 27, 2008
Query Serial Number for an Order in Oracle
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
January 18, 2008
List all concurrent requests in Oracle
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
January 10, 2008
Custom.pll
Steps to convert/compile custom.pll files.
Convert .pll to .pldf60gen MODULE=CUSTOM USERID=apps/sldfkjns5 MODULE_TYPE=LIBRARY SCRIPT=YES Convert .pld to .pllf60gen MODULE=CUSTOM USERID=apps/sldfkjns5 MODULE_TYPE=LIBRARY PARSE=YES Compile all modulesf60gen MODULE=CUSTOM USERID=apps/sldfkjns5 MODULE_TYPE=LIBRARY COMPILE_ALL=yes Copy the pll and plx back to $AU_TOP/resourceThen bounce forms port (f60down and f60up should not be used as because it kills existing connections)
January 7, 2008
List Responsibilities by User in Oracle Apps eBusiness Suite.
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%’);
January 3, 2008
Sys.xmlType datatype and parsing XML in Oracle9i, 10g onwards
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
January 2, 2008
User Hooks in Oracle
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’.
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);
- 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.
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
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;
/