Oracle CRM Blog

Friday, April 25, 2008

Oracle Service Contracts User Guide

Oracle Service contracts user guide can be found here

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.

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

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

Thursday, January 10, 2008

Custom.pll

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)

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

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