Here is sample program unit to create note for Installaed Base Instance.
(JTF_NOTES_PUB.CREATE_NOTE)
DECLARE
x_return_status varchar2 (1);
x_msg_count number;
x_msg_data varchar2 (200);
x_jtf_note_id number;
x_msg_index_out number;
BEGIN
DBMS_APPLICATION_INFO.set_client_info (ORG_ID);
fnd_global.apps_initialize (USER_ID, RESP_ID, APPL_ID);
jtf_notes_pub.create_note (p_api_version => 1.0,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_source_object_id => 999999999, -- INSTANCE_ID
p_source_object_code => 'CP', --Customer Product
p_notes => 'Customer Note',
p_notes_detail => NULL,
p_note_status => 'I',
x_jtf_note_id => x_jtf_note_id,
p_last_update_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_created_by => fnd_global.user_id,
p_note_type => 'ASF_CONVERSATION');
IF x_return_status = Fnd_Api.G_RET_STS_SUCCESS
THEN
DBMS_OUTPUT.put_line (
'Note has been created with Note Id:' || x_jtf_note_id
);
ELSE
FOR j IN 1 .. x_msg_count
LOOP
x_msg_data := NULL;
fnd_msg_pub.get (j,
FND_API.G_FALSE,
x_msg_data,
x_msg_index_out);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Error at Created Acct Relationship. ' || x_msg_data
);
DBMS_OUTPUT.put_line (x_msg_data);
END LOOP;
END IF;
END;
Visit for more information http://blog.optiosys.com/
Wednesday, March 9, 2011
Tuesday, March 8, 2011
OS Command from PL/SQL
=========================================
Execute operating system commands from PL/SQL
Execute operating system commands in Oracle
=========================================
You can execute OS command from your PL/SQL program
/*
============= STEP 1 ==================
Grant Java executable permission to user
where you want to create java program
========================================
*/
begin
-- this grants read privilege on STDIN
dbms_java.grant_permission(
grantee => 'SCOTT',
permission_type => 'SYS:java.lang.RuntimePermission',
permission_name => 'readFileDescriptor',
permission_action => null
);
-- this grants write permission on STDOUT
dbms_java.grant_permission(
grantee => 'SCOTT',
permission_type => 'SYS:java.lang.RuntimePermission',
permission_name => 'writeFileDescriptor',
permission_action => null
);
-- this grants execute privilege for the 'ls -la' command
dbms_java.grant_permission(
grantee => 'SCOTT',
permission_type => 'SYS:java.io.FilePermission',
permission_name => '/bin/ls',
permission_action => 'execute'
);
-- this grants execute privilege for ALL shell commands: VERY DANGEROUS!
dbms_java.grant_permission(
grantee => 'SCOTT',
permission_type => 'SYS:java.io.FilePermission',
permission_name => '<<ALL FILES>>',
permission_action => 'execute'
);
end;
============= STEP 2 ==================
Create java program
========================================
CREATE OR REPLACE JAVA SOURCE NAMED "XXHostCommand"
AS import java.io.*;
import oracle.jdbc.*;
import oracle.sql.*;
import java.sql.*;
public class XXHostCommand {
private static oracle.sql.Datum exec(String sCommand, Datum data, int iReturnType) throws Exception {
Process p = null;
InputStreamReader pStdOutR = null;
InputStream pStdOutS = null;
OutputStreamWriter pStdInW = null;
OutputStream pStdInS = null;
Writer paramClobWriter = null;
OutputStream paramBlobOStream = null;
Reader paramClobReader = null;
InputStream paramBlobIStream = null;
Datum tempLob;
char[] caStdInBuffer = null;
char[] caStdOutBuffer = null;
byte[] baStdInBuffer = null;
byte[] baStdOutBuffer = null;
int iCharsRead = 0;
Connection con = DriverManager.getConnection("jdbc:default:connection:");
if (iReturnType == oracle.jdbc.OracleTypes.BLOB) {
tempLob = BLOB.createTemporary(con, true, BLOB.DURATION_CALL);
paramBlobOStream = ((BLOB)tempLob).setBinaryStream(0L);
baStdOutBuffer = new byte[((BLOB)tempLob).getChunkSize()];
} else if (iReturnType == oracle.jdbc.OracleTypes.CLOB) {
tempLob = CLOB.createTemporary(con, true, CLOB.DURATION_CALL);
paramClobWriter = ((CLOB)tempLob).setCharacterStream(0L);
caStdOutBuffer = new char[((CLOB)tempLob).getChunkSize()];
} else {
throw new IllegalArgumentException("ReturnType must be CLOB or BLOB");
}
if (data != null) {
if (data instanceof oracle.sql.BLOB) {
paramBlobIStream = ((BLOB)data).getBinaryStream();
baStdInBuffer = new byte[((BLOB)data).getChunkSize()];
} else if (data instanceof oracle.sql.CLOB) {
paramClobReader = ((CLOB)data).getCharacterStream();
caStdInBuffer = new char[((CLOB)data).getChunkSize()];
} else {
throw new IllegalArgumentException("Argument for Standard Input must be of type CLOB or BLOB");
}
}
p = Runtime.getRuntime().exec(sCommand);
iCharsRead = 0;
if (data != null) {
if (data instanceof oracle.sql.BLOB) {
pStdInS = p.getOutputStream();
while ((iCharsRead = paramBlobIStream.read(baStdInBuffer, 0, baStdInBuffer.length)) != -1) {
pStdInS.write(baStdInBuffer, 0, iCharsRead);
}
paramBlobIStream.close();
pStdInS.flush();
pStdInS.close();
} else {
pStdInW = new OutputStreamWriter(p.getOutputStream());
while ((iCharsRead = paramClobReader.read(caStdInBuffer, 0, caStdInBuffer.length)) != -1) {
pStdInW.write(caStdInBuffer, 0, iCharsRead);
}
paramClobReader.close();
pStdInW.flush();
pStdInW.close();
}
}
p.waitFor();
iCharsRead = 0;
if (iReturnType == oracle.jdbc.OracleTypes.CLOB) {
if (p.exitValue() == 0) {
pStdOutR = new InputStreamReader(p.getInputStream());
} else {
pStdOutR = new InputStreamReader(p.getErrorStream());
}
while ((iCharsRead = pStdOutR.read(caStdOutBuffer, 0, caStdOutBuffer.length)) != -1) {
paramClobWriter.write(caStdOutBuffer, 0, iCharsRead);
}
pStdOutR.close();
paramClobWriter.flush();
paramClobWriter.close();
} else {
if (p.exitValue() == 0) {
pStdOutS = p.getInputStream();
} else {
pStdOutS = p.getErrorStream();
}
while ((iCharsRead = pStdOutS.read(baStdOutBuffer, 0, baStdOutBuffer.length)) != -1) {
paramBlobOStream.write(baStdOutBuffer, 0, iCharsRead);
}
pStdOutS.close();
paramBlobOStream.flush();
paramBlobOStream.close();
}
return tempLob;
}
public static Datum execClob(String sCommand, CLOB dataClob) throws Exception {
return exec(sCommand, dataClob, oracle.jdbc.OracleTypes.CLOB);
}
public static Datum execClob(String sCommand, BLOB dataBlob) throws Exception {
return exec(sCommand, dataBlob, oracle.jdbc.OracleTypes.CLOB);
}
public static Datum execBlob(String sCommand, BLOB dataBlob) throws Exception {
return exec(sCommand, dataBlob, oracle.jdbc.OracleTypes.BLOB);
}
public static Datum execBlob(String sCommand, CLOB dataClob) throws Exception {
return exec(sCommand, dataClob, oracle.jdbc.OracleTypes.BLOB);
}
public static Datum execClob(String sCommand) throws Exception {
return exec(sCommand, null, oracle.jdbc.OracleTypes.CLOB);
}
public static Datum execBlob(String sCommand) throws Exception {
return exec(sCommand, null, oracle.jdbc.OracleTypes.BLOB);
}
}
/
============= STEP 3 ======================
Create Package specification :xx_os_command
===========================================
CREATE OR REPLACE PACKAGE xx_os_command
IS
FUNCTION exec_CLOB (p_command IN varchar2, p_stdin IN BLOB)
RETURN CLOB;
FUNCTION exec_CLOB (p_command IN varchar2, p_stdin IN CLOB)
RETURN CLOB;
FUNCTION exec_BLOB (p_command IN varchar2, p_stdin IN BLOB)
RETURN BLOB;
FUNCTION exec_BLOB (p_command IN varchar2, p_stdin IN CLOB)
RETURN BLOB;
FUNCTION exec_CLOB (p_command IN varchar2)
RETURN CLOB;
FUNCTION exec_BLOB (p_command IN varchar2)
RETURN BLOB;
END xx_os_command;
--alter java source "xx_os_command" compile
============= STEP 4 ======================
Create Package body :xx_os_command
===========================================
CREATE OR REPLACE PACKAGE BODY xx_os_command
IS
FUNCTION exec_CLOB (
p_command IN varchar2,
p_stdin IN BLOB
)
RETURN CLOB
IS
LANGUAGE JAVA
NAME 'XXHostCommand.execClob(java.lang.String, oracle.sql.BLOB) return oracle.sql.CLOB';
FUNCTION exec_CLOB (
p_command IN varchar2,
p_stdin IN CLOB
)
RETURN CLOB
IS
LANGUAGE JAVA
NAME 'XXHostCommand.execClob(java.lang.String, oracle.sql.CLOB) return oracle.sql.CLOB';
FUNCTION exec_BLOB (
p_command IN varchar2,
p_stdin IN BLOB
)
RETURN BLOB
IS
LANGUAGE JAVA
NAME 'XXHostCommand.execBlob(java.lang.String, oracle.sql.BLOB) return oracle.sql.BLOB';
FUNCTION exec_BLOB (
p_command IN varchar2,
p_stdin IN CLOB
)
RETURN BLOB
IS
LANGUAGE JAVA
NAME 'XXHostCommand.execBlob(java.lang.String, oracle.sql.CLOB) return oracle.sql.BLOB';
FUNCTION exec_CLOB (p_command IN varchar2)
RETURN CLOB
IS
LANGUAGE JAVA
NAME 'XXHostCommand.execClob(java.lang.String) return oracle.sql.CLOB';
FUNCTION exec_BLOB (p_command IN varchar2)
RETURN BLOB
IS
LANGUAGE JAVA
NAME 'XXHostCommand.execBlob(java.lang.String) return oracle.sql.BLOB';
END xx_os_command;
============= STEP 5 ======================
Now you are ready to run host command using
java package
===========================================
--==== File listing from "/usr/tmp" directiory
SELECT DBMS_LOB.SUBSTR (os_command.exec_clob('ls /usr/tmp'),
32000,
1)
Output
FROM DUAL;
--==== Run bourne shell script listing
SELECT DBMS_LOB.SUBSTR (os_command.exec_clob ('bash /usr/tmp/test.sh'),
32000,
1)
Output
FROM DUAL;
--==== Run korn shell script listing
SELECT DBMS_LOB.SUBSTR (os_command.exec_clob ('bash /usr/tmp/test.sh'),
32000,
1)
Output
FROM DUAL;
--==== call executable program with two or more parameter
SELECT DBMS_LOB.SUBSTR (os_command.exec_clob ('/cbs_x80_lic F5060007 001919'),
32000,
1)
Output
FROM DUAL;
Visit for more information http://blog.optiosys.com/
Execute operating system commands from PL/SQL
Execute operating system commands in Oracle
=========================================
You can execute OS command from your PL/SQL program
/*
============= STEP 1 ==================
Grant Java executable permission to user
where you want to create java program
========================================
*/
begin
-- this grants read privilege on STDIN
dbms_java.grant_permission(
grantee => 'SCOTT',
permission_type => 'SYS:java.lang.RuntimePermission',
permission_name => 'readFileDescriptor',
permission_action => null
);
-- this grants write permission on STDOUT
dbms_java.grant_permission(
grantee => 'SCOTT',
permission_type => 'SYS:java.lang.RuntimePermission',
permission_name => 'writeFileDescriptor',
permission_action => null
);
-- this grants execute privilege for the 'ls -la' command
dbms_java.grant_permission(
grantee => 'SCOTT',
permission_type => 'SYS:java.io.FilePermission',
permission_name => '/bin/ls',
permission_action => 'execute'
);
-- this grants execute privilege for ALL shell commands: VERY DANGEROUS!
dbms_java.grant_permission(
grantee => 'SCOTT',
permission_type => 'SYS:java.io.FilePermission',
permission_name => '<<ALL FILES>>',
permission_action => 'execute'
);
end;
============= STEP 2 ==================
Create java program
========================================
CREATE OR REPLACE JAVA SOURCE NAMED "XXHostCommand"
AS import java.io.*;
import oracle.jdbc.*;
import oracle.sql.*;
import java.sql.*;
public class XXHostCommand {
private static oracle.sql.Datum exec(String sCommand, Datum data, int iReturnType) throws Exception {
Process p = null;
InputStreamReader pStdOutR = null;
InputStream pStdOutS = null;
OutputStreamWriter pStdInW = null;
OutputStream pStdInS = null;
Writer paramClobWriter = null;
OutputStream paramBlobOStream = null;
Reader paramClobReader = null;
InputStream paramBlobIStream = null;
Datum tempLob;
char[] caStdInBuffer = null;
char[] caStdOutBuffer = null;
byte[] baStdInBuffer = null;
byte[] baStdOutBuffer = null;
int iCharsRead = 0;
Connection con = DriverManager.getConnection("jdbc:default:connection:");
if (iReturnType == oracle.jdbc.OracleTypes.BLOB) {
tempLob = BLOB.createTemporary(con, true, BLOB.DURATION_CALL);
paramBlobOStream = ((BLOB)tempLob).setBinaryStream(0L);
baStdOutBuffer = new byte[((BLOB)tempLob).getChunkSize()];
} else if (iReturnType == oracle.jdbc.OracleTypes.CLOB) {
tempLob = CLOB.createTemporary(con, true, CLOB.DURATION_CALL);
paramClobWriter = ((CLOB)tempLob).setCharacterStream(0L);
caStdOutBuffer = new char[((CLOB)tempLob).getChunkSize()];
} else {
throw new IllegalArgumentException("ReturnType must be CLOB or BLOB");
}
if (data != null) {
if (data instanceof oracle.sql.BLOB) {
paramBlobIStream = ((BLOB)data).getBinaryStream();
baStdInBuffer = new byte[((BLOB)data).getChunkSize()];
} else if (data instanceof oracle.sql.CLOB) {
paramClobReader = ((CLOB)data).getCharacterStream();
caStdInBuffer = new char[((CLOB)data).getChunkSize()];
} else {
throw new IllegalArgumentException("Argument for Standard Input must be of type CLOB or BLOB");
}
}
p = Runtime.getRuntime().exec(sCommand);
iCharsRead = 0;
if (data != null) {
if (data instanceof oracle.sql.BLOB) {
pStdInS = p.getOutputStream();
while ((iCharsRead = paramBlobIStream.read(baStdInBuffer, 0, baStdInBuffer.length)) != -1) {
pStdInS.write(baStdInBuffer, 0, iCharsRead);
}
paramBlobIStream.close();
pStdInS.flush();
pStdInS.close();
} else {
pStdInW = new OutputStreamWriter(p.getOutputStream());
while ((iCharsRead = paramClobReader.read(caStdInBuffer, 0, caStdInBuffer.length)) != -1) {
pStdInW.write(caStdInBuffer, 0, iCharsRead);
}
paramClobReader.close();
pStdInW.flush();
pStdInW.close();
}
}
p.waitFor();
iCharsRead = 0;
if (iReturnType == oracle.jdbc.OracleTypes.CLOB) {
if (p.exitValue() == 0) {
pStdOutR = new InputStreamReader(p.getInputStream());
} else {
pStdOutR = new InputStreamReader(p.getErrorStream());
}
while ((iCharsRead = pStdOutR.read(caStdOutBuffer, 0, caStdOutBuffer.length)) != -1) {
paramClobWriter.write(caStdOutBuffer, 0, iCharsRead);
}
pStdOutR.close();
paramClobWriter.flush();
paramClobWriter.close();
} else {
if (p.exitValue() == 0) {
pStdOutS = p.getInputStream();
} else {
pStdOutS = p.getErrorStream();
}
while ((iCharsRead = pStdOutS.read(baStdOutBuffer, 0, baStdOutBuffer.length)) != -1) {
paramBlobOStream.write(baStdOutBuffer, 0, iCharsRead);
}
pStdOutS.close();
paramBlobOStream.flush();
paramBlobOStream.close();
}
return tempLob;
}
public static Datum execClob(String sCommand, CLOB dataClob) throws Exception {
return exec(sCommand, dataClob, oracle.jdbc.OracleTypes.CLOB);
}
public static Datum execClob(String sCommand, BLOB dataBlob) throws Exception {
return exec(sCommand, dataBlob, oracle.jdbc.OracleTypes.CLOB);
}
public static Datum execBlob(String sCommand, BLOB dataBlob) throws Exception {
return exec(sCommand, dataBlob, oracle.jdbc.OracleTypes.BLOB);
}
public static Datum execBlob(String sCommand, CLOB dataClob) throws Exception {
return exec(sCommand, dataClob, oracle.jdbc.OracleTypes.BLOB);
}
public static Datum execClob(String sCommand) throws Exception {
return exec(sCommand, null, oracle.jdbc.OracleTypes.CLOB);
}
public static Datum execBlob(String sCommand) throws Exception {
return exec(sCommand, null, oracle.jdbc.OracleTypes.BLOB);
}
}
/
============= STEP 3 ======================
Create Package specification :xx_os_command
===========================================
CREATE OR REPLACE PACKAGE xx_os_command
IS
FUNCTION exec_CLOB (p_command IN varchar2, p_stdin IN BLOB)
RETURN CLOB;
FUNCTION exec_CLOB (p_command IN varchar2, p_stdin IN CLOB)
RETURN CLOB;
FUNCTION exec_BLOB (p_command IN varchar2, p_stdin IN BLOB)
RETURN BLOB;
FUNCTION exec_BLOB (p_command IN varchar2, p_stdin IN CLOB)
RETURN BLOB;
FUNCTION exec_CLOB (p_command IN varchar2)
RETURN CLOB;
FUNCTION exec_BLOB (p_command IN varchar2)
RETURN BLOB;
END xx_os_command;
--alter java source "xx_os_command" compile
============= STEP 4 ======================
Create Package body :xx_os_command
===========================================
CREATE OR REPLACE PACKAGE BODY xx_os_command
IS
FUNCTION exec_CLOB (
p_command IN varchar2,
p_stdin IN BLOB
)
RETURN CLOB
IS
LANGUAGE JAVA
NAME 'XXHostCommand.execClob(java.lang.String, oracle.sql.BLOB) return oracle.sql.CLOB';
FUNCTION exec_CLOB (
p_command IN varchar2,
p_stdin IN CLOB
)
RETURN CLOB
IS
LANGUAGE JAVA
NAME 'XXHostCommand.execClob(java.lang.String, oracle.sql.CLOB) return oracle.sql.CLOB';
FUNCTION exec_BLOB (
p_command IN varchar2,
p_stdin IN BLOB
)
RETURN BLOB
IS
LANGUAGE JAVA
NAME 'XXHostCommand.execBlob(java.lang.String, oracle.sql.BLOB) return oracle.sql.BLOB';
FUNCTION exec_BLOB (
p_command IN varchar2,
p_stdin IN CLOB
)
RETURN BLOB
IS
LANGUAGE JAVA
NAME 'XXHostCommand.execBlob(java.lang.String, oracle.sql.CLOB) return oracle.sql.BLOB';
FUNCTION exec_CLOB (p_command IN varchar2)
RETURN CLOB
IS
LANGUAGE JAVA
NAME 'XXHostCommand.execClob(java.lang.String) return oracle.sql.CLOB';
FUNCTION exec_BLOB (p_command IN varchar2)
RETURN BLOB
IS
LANGUAGE JAVA
NAME 'XXHostCommand.execBlob(java.lang.String) return oracle.sql.BLOB';
END xx_os_command;
============= STEP 5 ======================
Now you are ready to run host command using
java package
===========================================
--==== File listing from "/usr/tmp" directiory
SELECT DBMS_LOB.SUBSTR (os_command.exec_clob('ls /usr/tmp'),
32000,
1)
Output
FROM DUAL;
--==== Run bourne shell script listing
SELECT DBMS_LOB.SUBSTR (os_command.exec_clob ('bash /usr/tmp/test.sh'),
32000,
1)
Output
FROM DUAL;
--==== Run korn shell script listing
SELECT DBMS_LOB.SUBSTR (os_command.exec_clob ('bash /usr/tmp/test.sh'),
32000,
1)
Output
FROM DUAL;
--==== call executable program with two or more parameter
SELECT DBMS_LOB.SUBSTR (os_command.exec_clob ('/cbs_x80_lic F5060007 001919'),
32000,
1)
Output
FROM DUAL;
Visit for more information http://blog.optiosys.com/
Monday, March 7, 2011
Oracle Form Session
==================================================
Oracle form session (Oracle E-Business Suite Form Session) Query
==================================================
SELECT p.spid,
s.process,
s.status,
s.machine,
TO_CHAR (s.logon_time, 'mm-dd-yy hh24:mi:ss') Logon_Time,
s.last_call_et / 3600 Last_Call_ET,
s.action,
s.module,
s.sid,
s.serial#,
s.osuser,
SUBSTR (REPLACE (s.action, 'FRM:', ''),
1,
INSTR (REPLACE (s.action, 'FRM:', ''), ':') - 1)
user_name
FROM V$SESSION s, V$PROCESS p
WHERE s.paddr = p.addr
AND s.username IS NOT NULL
AND s.username = 'APPS'
AND s.action LIKE 'FRM%'
============================================================
How to kill inactive oracle form session (Oracle E-Business Suite Form Session)============================================================
DECLARE
CURSOR CUR_INACTIVE_SESSION
IS
SELECT p.spid,
s.process,
s.status,
s.machine,
TO_CHAR (s.logon_time, 'mm-dd-yy hh24:mi:ss') Logon_Time,
s.last_call_et / 3600 Last_Call_ET,
s.action,
s.module,
s.sid,
s.serial#,
s.osuser,
'alter system kill session '''
|| s.sid
|| ','
|| s.serial#
|| ''''
sql_string
FROM V$SESSION s, V$PROCESS p
WHERE s.paddr = p.addr
AND s.username IS NOT NULL
AND s.username = 'APPS'
AND UPPER (s.osuser) = 'APPCBSP'
AND s.last_call_et / 3600 > 8 /* (Inactive sessions running for more than 8 hrs) */
AND s.action LIKE 'FRM%'
AND s.status = 'INACTIVE'
ORDER BY p.spid;
BEGIN
FOR REC1 IN CUR_INACTIVE_SESSION
LOOP
BEGIN
EXECUTE IMMEDIATE REC1.sql_string;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
END LOOP;
END;
Visit for more information http://blog.optiosys.com/
Oracle form session (Oracle E-Business Suite Form Session) Query
==================================================
SELECT p.spid,
s.process,
s.status,
s.machine,
TO_CHAR (s.logon_time, 'mm-dd-yy hh24:mi:ss') Logon_Time,
s.last_call_et / 3600 Last_Call_ET,
s.action,
s.module,
s.sid,
s.serial#,
s.osuser,
SUBSTR (REPLACE (s.action, 'FRM:', ''),
1,
INSTR (REPLACE (s.action, 'FRM:', ''), ':') - 1)
user_name
FROM V$SESSION s, V$PROCESS p
WHERE s.paddr = p.addr
AND s.username IS NOT NULL
AND s.username = 'APPS'
AND s.action LIKE 'FRM%'
============================================================
How to kill inactive oracle form session (Oracle E-Business Suite Form Session)============================================================
DECLARE
CURSOR CUR_INACTIVE_SESSION
IS
SELECT p.spid,
s.process,
s.status,
s.machine,
TO_CHAR (s.logon_time, 'mm-dd-yy hh24:mi:ss') Logon_Time,
s.last_call_et / 3600 Last_Call_ET,
s.action,
s.module,
s.sid,
s.serial#,
s.osuser,
'alter system kill session '''
|| s.sid
|| ','
|| s.serial#
|| ''''
sql_string
FROM V$SESSION s, V$PROCESS p
WHERE s.paddr = p.addr
AND s.username IS NOT NULL
AND s.username = 'APPS'
AND UPPER (s.osuser) = 'APPCBSP'
AND s.last_call_et / 3600 > 8 /* (Inactive sessions running for more than 8 hrs) */
AND s.action LIKE 'FRM%'
AND s.status = 'INACTIVE'
ORDER BY p.spid;
BEGIN
FOR REC1 IN CUR_INACTIVE_SESSION
LOOP
BEGIN
EXECUTE IMMEDIATE REC1.sql_string;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
END LOOP;
END;
Visit for more information http://blog.optiosys.com/
Oracle Web ADI
Oracle Web ADI and Excel 2007/2010 --Not creating document
============================================
You need to change security option for Excel 2007 or Excel 2010 and IE.
1. Go to Internet Explorere --> Tools --> Internet Options -->Security Tab-->Custom Level
and change "Initialize and script ActiveX control not marked as safe for scription" to "Prompt"
2. Go to Internet Explorere --> Tools --> Internet Options -->Security Tab-->Custom Level
and change "Allow status bar updates via script" to "Enable"
3. Open Microsoft Excel --> Tools --> Macro-->Security --> Change Security Level to "Medium"
4. Microsoft Excel --> Tools --> Macro-->Security --> Trusted Publishers Tab and turn on "Trust access to Visual Basic Project"
Now Logon to Oracle E-Business Suite --> Oracle Web ADI Resposibility and try to create document using "Create Document" option.
Visit for more information http://blog.optiosys.com/
============================================
You need to change security option for Excel 2007 or Excel 2010 and IE.
1. Go to Internet Explorere --> Tools --> Internet Options -->Security Tab-->Custom Level
and change "Initialize and script ActiveX control not marked as safe for scription" to "Prompt"
2. Go to Internet Explorere --> Tools --> Internet Options -->Security Tab-->Custom Level
and change "Allow status bar updates via script" to "Enable"
3. Open Microsoft Excel --> Tools --> Macro-->Security --> Change Security Level to "Medium"
4. Microsoft Excel --> Tools --> Macro-->Security --> Trusted Publishers Tab and turn on "Trust access to Visual Basic Project"
Now Logon to Oracle E-Business Suite --> Oracle Web ADI Resposibility and try to create document using "Create Document" option.
Visit for more information http://blog.optiosys.com/
Sunday, March 6, 2011
BI Publisher bursting
Oracle BI Publisher (xml Publisher) pdf file bursting
You can use Oracle BI Publisher (E-Business suite XML Publisher) to generate report out in pdf format and send directly to the following source system using bursting API.
STEP 2: Define Data definition using xml publisher administrator and upload bursting control file according to your requirement.
==================
Email control file
==================
<?xml version="1.0" encoding="UTF-8"?>
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi"
listener="oracle.apps.xdo.batch.SampleListener">
<xapi:request select="/RAXINV/LIST_G_ORDER_BY/G_ORDER_BY/LIST_G_INVOICE/G_INVOICE">
<xapi:delivery>
<xapi:email server="Mail server"
port="25"
from="from email address"
reply-to ="Reply to email address">
<xapi:message id="Myemail"
to="guest@mymail.us.oracle.com" cc="guest@mymail.us.oracle.com" attachment="true" content-type="html/text"
subject="Your Invoice #${TRX_NUMBER} ${BILL_CUST_NAME}">Dear Sir/Madam,
Please find attached your invoice <B> #${TRX_NUMBER} </B>
....
....
....
Regards
Account Receivables Manager
</xapi:message>
</xapi:email>
</xapi:delivery>
<xapi:document key="${TRX_NUMBER}" output="${TRX_NUMBER}" output-type="pdf" delivery="Myemail">
<xapi:template type="rtf" locale="" location="xdo://AR.RAXINV.en.US/?getSource=true" translation="" filter="">
</xapi:template>
</xapi:document>
</xapi:request>
</xapi:requestset>
==============
FTP control file
==============
<?xml version="1.0" encoding="UTF-8"?>
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi"
listener="oracle.apps.xdo.batch.SampleListener">
<xapi:request select="/RAXINV/LIST_G_ORDER_BY/G_ORDER_BY/LIST_G_INVOICE/G_INVOICE">
<xapi:delivery>
<xapi:ftp id="Myftp"
server="ftp address"
user="${ftp_user}"
password="${ftp_password}"
remote-directory="/output"
remote-file="${${TRX_NUMBER}.pdf">
</xapi:ftp>
</xapi:delivery>
<xapi:document key="${TRX_NUMBER}" output="${TRX_NUMBER}" output-type="pdf" delivery="Myftp">
<xapi:template type="rtf" locale="" location="xdo://AR.RAXINV.en.US/?getSource=true" translation="" filter="">
</xapi:template>
</xapi:document>
</xapi:request>
</xapi:requestset>
===============
Fax control file
===============
<?xml version="1.0" encoding="UTF-8"?>
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi"
listener="oracle.apps.xdo.batch.SampleListener">
<xapi:request select="/RAXINV/LIST_G_ORDER_BY/G_ORDER_BY/LIST_G_INVOICE/G_INVOICE">
<xapi:delivery>
<xapi:fax server="faxprintername">
<xapi:number id="MyFax">fax number</xapi:number>
</xapi:fax>
</xapi:delivery>
<xapi:document key="${TRX_NUMBER}" output="${TRX_NUMBER}" output-type="pdf" delivery="MyFax">
<xapi:template type="rtf" locale="" location="xdo://AR.RAXINV.en.US/?getSource=true" translation="" filter="">
</xapi:template>
</xapi:document>
</xapi:request>
</xapi:requestset>
===============
Printer control file
===============
<?xml version="1.0" encoding="UTF-8"?>
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi"
listener="oracle.apps.xdo.batch.SampleListener">
<xapi:request select="/RAXINV/LIST_G_ORDER_BY/G_ORDER_BY/LIST_G_INVOICE/G_INVOICE">
<xapi:delivery>
<xapi:print id="Myprinter"printer="Printer Name" copies="1" />
</xapi:delivery>
<xapi:document key="${TRX_NUMBER}" output="${TRX_NUMBER}" output-type="pdf" delivery="Myprinter">
<xapi:template type="rtf" locale="" location="xdo://AR.RAXINV.en.US/?getSource=true" translation="" filter="">
</xapi:template>
</xapi:document>
</xapi:request>
</xapi:requestset>
STEP 3: Upload report template using XML Publisher administrator responsibility.
Now you are ready to use bursting API to send report output to the target.
Visit for more information http://blog.optiosys.com/
You can use Oracle BI Publisher (E-Business suite XML Publisher) to generate report out in pdf format and send directly to the following source system using bursting API.
- Fax
- FTP
- Printer
- FileSystem
STEP 2: Define Data definition using xml publisher administrator and upload bursting control file according to your requirement.
==================
Email control file
==================
<?xml version="1.0" encoding="UTF-8"?>
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi"
listener="oracle.apps.xdo.batch.SampleListener">
<xapi:request select="/RAXINV/LIST_G_ORDER_BY/G_ORDER_BY/LIST_G_INVOICE/G_INVOICE">
<xapi:delivery>
<xapi:email server="Mail server"
port="25"
from="from email address"
reply-to ="Reply to email address">
<xapi:message id="Myemail"
to="guest@mymail.us.oracle.com" cc="guest@mymail.us.oracle.com" attachment="true" content-type="html/text"
subject="Your Invoice #${TRX_NUMBER} ${BILL_CUST_NAME}">Dear Sir/Madam,
Please find attached your invoice <B> #${TRX_NUMBER} </B>
....
....
....
Regards
Account Receivables Manager
</xapi:message>
</xapi:email>
</xapi:delivery>
<xapi:document key="${TRX_NUMBER}" output="${TRX_NUMBER}" output-type="pdf" delivery="Myemail">
<xapi:template type="rtf" locale="" location="xdo://AR.RAXINV.en.US/?getSource=true" translation="" filter="">
</xapi:template>
</xapi:document>
</xapi:request>
</xapi:requestset>
==============
FTP control file
==============
<?xml version="1.0" encoding="UTF-8"?>
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi"
listener="oracle.apps.xdo.batch.SampleListener">
<xapi:request select="/RAXINV/LIST_G_ORDER_BY/G_ORDER_BY/LIST_G_INVOICE/G_INVOICE">
<xapi:delivery>
<xapi:ftp id="Myftp"
server="ftp address"
user="${ftp_user}"
password="${ftp_password}"
remote-directory="/output"
remote-file="${${TRX_NUMBER}.pdf">
</xapi:ftp>
</xapi:delivery>
<xapi:document key="${TRX_NUMBER}" output="${TRX_NUMBER}" output-type="pdf" delivery="Myftp">
<xapi:template type="rtf" locale="" location="xdo://AR.RAXINV.en.US/?getSource=true" translation="" filter="">
</xapi:template>
</xapi:document>
</xapi:request>
</xapi:requestset>
===============
Fax control file
===============
<?xml version="1.0" encoding="UTF-8"?>
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi"
listener="oracle.apps.xdo.batch.SampleListener">
<xapi:request select="/RAXINV/LIST_G_ORDER_BY/G_ORDER_BY/LIST_G_INVOICE/G_INVOICE">
<xapi:delivery>
<xapi:fax server="faxprintername">
<xapi:number id="MyFax">fax number</xapi:number>
</xapi:fax>
</xapi:delivery>
<xapi:document key="${TRX_NUMBER}" output="${TRX_NUMBER}" output-type="pdf" delivery="MyFax">
<xapi:template type="rtf" locale="" location="xdo://AR.RAXINV.en.US/?getSource=true" translation="" filter="">
</xapi:template>
</xapi:document>
</xapi:request>
</xapi:requestset>
===============
Printer control file
===============
<?xml version="1.0" encoding="UTF-8"?>
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi"
listener="oracle.apps.xdo.batch.SampleListener">
<xapi:request select="/RAXINV/LIST_G_ORDER_BY/G_ORDER_BY/LIST_G_INVOICE/G_INVOICE">
<xapi:delivery>
<xapi:print id="Myprinter"printer="Printer Name" copies="1" />
</xapi:delivery>
<xapi:document key="${TRX_NUMBER}" output="${TRX_NUMBER}" output-type="pdf" delivery="Myprinter">
<xapi:template type="rtf" locale="" location="xdo://AR.RAXINV.en.US/?getSource=true" translation="" filter="">
</xapi:template>
</xapi:document>
</xapi:request>
</xapi:requestset>
STEP 3: Upload report template using XML Publisher administrator responsibility.
Now you are ready to use bursting API to send report output to the target.
Pasta Landscape driver setup
Past Landscape driver setup for Oracle BI Publisher (XML Publisher) PDF printing
Step 1: Go to Unix
Step 1: Go to Unix
$FND_TOP/11.5.0/resource
Step 2: Copy pasta_pdf.cfg to the new file pasta_pdf_land.cfg
cp pasta_pdf.cfg pasta_pdf_land.cfg
Step 3: Open New created cfg file
vi pasta_pdf_land.cfg
Go to following section
Comment all lines and add new command line
preprocess=acroread -toPostScript -landscape {infile} {outfile}
Save fine and close the file editor
Save fine and close the file editor
Step 4: Now logon to oracle application using system administrator responsibility
Navigate to Install à Printer à Driver
Add argument line and call new created cfg file for landscape printing (pasta_pdf_land.cfg)
-pn$PROFILES$.PRINTER -f$PROFILES$.FILENAME -c$PROFILES$.CONC_COPIES -Fpasta_pdf_land.cfg
Save record and close the form.
Step 5: Navigate to Install à Printer à Type
Add new style PDF Publisher (Landscape ) with driver name PASTA_PDF_LAND and save record.
Now you are ready to print BI Publisher (xml Publisher ) Landscape PDF report
Visit for more information http://blog.optiosys.com/
Now you are ready to print BI Publisher (xml Publisher ) Landscape PDF report
Visit for more information http://blog.optiosys.com/
Saturday, March 5, 2011
Export Compliance (ECS) Integration
Oracle (E-Business Suite 11i/R12) - Export Compliance Web Service Integration.
This document will give you high level overview about export compliance service integration with your Oracle E-bussiness suite (Order Management and Shipping Execution).
STEP 1: Create a view to get ship to party information from Order Management.
SELECT oeh.header_id,
oeh.order_number,
stc.customer_id ship_to_cust_id,
stc.customer_number ship_to_cust_number,
stc.customer_name ship_to_customer,
TRIM(TRIM( slc.address1
|| ' '
|| slc.address2
|| ' '
|| slc.address3
|| ' '
|| slc.address4)
|| ' '
|| slc.city
|| ' '
|| slc.state
|| ' '
|| slc.postal_code)
ship_to_address,
slc.country ship_to_country,
sctc.contact_id ship_to_contact_id,
sctc.first_name || ' ' || sctc.last_name ship_to_contact
FROM oe_order_headers_all oeh,
hz_cust_site_uses_all stsu,
hz_cust_acct_sites_all stcas,
hz_party_sites sps,
hz_locations slc,
ra_customers stc,
ra_contacts sctc
WHERE 1 = 1
AND stsu.site_use_id = oeh.ship_to_org_id
AND stcas.cust_acct_site_id = stsu.cust_acct_site_id
AND stc.customer_id = stcas.cust_account_id
AND sps.party_site_id = stcas.party_site_id
AND slc.location_id = sps.location_id
AND sctc.contact_id(+) = oeh.ship_to_contact_id
AND oeh.header_id = p_oe_hdr_id;
STEP 2: Write a custom PL/SQL program to generate SOAP message and populate SHIP TO information.
Note: Following SOAP request example based on Kewill export compliance WSDL structure.
<?xml version="1.0" encoding="utf-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<soapenv:Body>
<executeScreening xmlns="'||gbl_exp_url||'">
<request>
<administration>
<subscriberId>'||gbl_user_id||'</subscriberId>
<subscriberPassword>'||gbl_user_pwd||'</subscriberPassword>
</administration>
<serviceRequest>
<transaction>
<userDefined>'||rec1.order_number||'</userDefined>
<shipFromCountry>
<domain>COUNTRY</domain>
<code>US</code>
</shipFromCountry>
<party>
<id>'||rec1.ship_to_cust_id||'</id>
<type>SHIP_TO</type>
<name>'||replace_special_chr(rec1.ship_to_customer)||'</name>
<address>'||replace_special_chr(rec1.ship_to_address)||'</address>
<country>
<domain>COUNTRY</domain>
<code>'||replace_special_chr(rec1.ship_to_country)||'</code>
</country>
<userDefined>'||rec1.ship_to_cust_id||'-SHTO</userDefined>
</party>
<party>
<id>'||rec1.ship_to_contact_id||'</id>
<type>SHTO_CONTACT</type>
<name>'||replace_special_chr(rec1.ship_to_contact)||'</name>
<address>'||replace_special_chr(rec1.ship_to_address)||'</address>
<country>
<domain>COUNTRY</domain>
<code>'||replace_special_chr(rec1.ship_to_country)||'</code>
</country>
<userDefined>'||rec1.ship_to_contact_id||'-SHTO-CTC</userDefined>
</party>
</transaction>
</serviceRequest>
<parameters>
<threshold>'||gbl_threshold||'</threshold>
<countryMatch>'||gbl_countryMatch||'</countryMatch>
<redflag>'||gbl_redflag||'</redflag>
</parameters>
</request>
</executeScreening>
</soapenv:Body>
</soapenv:Envelope>
STEP 3: Use oracle's UTL_HTTP to call export compliance web service and submit SOAP request with POST method
CREATE OR REPLACE PROCEDURE xx_ECS_check_prc (
p_soap_request IN CLOB,
x_soap_respond OUT CLOB,
x_return_status OUT varchar2,
x_message OUT varchar2
)
IS
http_req UTL_HTTP.req;
http_resp UTL_HTTP.resp;
resp xmltype;
i integer;
BEGIN
UTL_HTTP.SET_WALLET (PATH => p_path, password => p_password);
http_req := UTL_HTTP.begin_request (p_url, 'POST', 'HTTP/1.1');
UTL_HTTP.set_header (http_req, 'Content-Type', 'text/xml');
UTL_HTTP.set_header (http_req, 'Content-Length', LENGTH (p_soap_request));
UTL_HTTP.set_header (http_req, 'SOAPAction', '');
UTL_HTTP.write_text (http_req, p_soap_request);
http_resp := UTL_HTTP.get_response (http_req);
UTL_HTTP.read_text (http_resp, x_soap_respond);
UTL_HTTP.end_response (http_resp);
x_return_status := 'S';
EXCEPTION
WHEN UTL_HTTP.Request_Failed
THEN
UTL_HTTP.clear_cookies;
UTL_HTTP.end_response (http_resp);
x_return_status := 'E';
x_message := SQLERRM;
--=================
WHEN UTL_HTTP.Http_Server_Error
THEN
UTL_HTTP.clear_cookies;
UTL_HTTP.end_response (http_resp);
x_return_status := 'E';
x_message := SQLERRM;
--=================
WHEN UTL_HTTP.Http_Client_Error
THEN
UTL_HTTP.clear_cookies;
UTL_HTTP.end_response (http_resp);
x_return_status := 'E';
x_message := SQLERRM;
--=================
WHEN OTHERS
THEN
UTL_HTTP.clear_cookies;
UTL_HTTP.end_response (http_resp);
x_return_status := 'E';
x_message := SQLERRM;
END;
STEP 4: Parse SOAP response and check party denial status. If ship to party under denial status then call Order Management API to put order on hold.
CREATE OR REPLACE PROCEDURE appy_hold (
p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type,
x_return_status OUT varchar2,
x_msg_data OUT varchar2
)
IS
x_msg_count number;
x_msg_index NUMBER;
x_data VARCHAR2 (2000);
BEGIN
oe_debug_pub.initialize;
oe_debug_pub.setdebuglevel (5);
oe_msg_pub.initialize;
/* --Sample Data for Hold
p_hold_source_rec := OE_HOLDS_PVT.G_MISS_HOLD_SOURCE_REC;
p_hold_source_rec.hold_id := 23; -- hold_id--OE_HOLD_DEFINITIONS
p_hold_source_rec.hold_entity_code := 'O'; -- order level hold
p_hold_source_rec.hold_entity_id := 985736; -- header_id of the order
p_hold_source_rec.header_id := 985736; -- header_id of the order
p_hold_source_rec.HOLD_COMMENT :='Export Compliance Hold based on ship to country';
*/
OE_Holds_PUB.Apply_Holds (
p_api_version => 1.0,
p_commit => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_NONE,
p_hold_source_rec => p_hold_source_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
IF x_return_status <> FND_API.G_RET_STS_SUCCESS
THEN
FOR i IN 1 .. x_msg_count
LOOP
OE_MSG_PUB.get (p_msg_index => i,
p_encoded => FND_API.G_FALSE,
p_data => x_data,
p_msg_index_out => x_msg_index);
DBMS_OUTPUT.put_line ('OE Hold Error:' || x_data);
x_msg_data := TRIM (x_msg_data) || ' ' || x_data;
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS
THEN
x_return_status := 'E';
x_msg_data := SQLERRM;
END appy_hold;
STEP 5: Now customize Order Header Workflow to call custom ECS program at booking event.
Web service integration overview.
Now your System (Order Management/Shipping Execution) is ready to go.
Benefits:
This document will give you high level overview about export compliance service integration with your Oracle E-bussiness suite (Order Management and Shipping Execution).
It is very simple.
What you need to know for this integration.
- Oracle E-Busniess Suite 11i/R12 ( Atleast Order Management and Shipping Execution)
- Oracle workflow customization
- Oracle PL/SQL and web service
- ECS Account (Kewill, eCustoms etc.)
STEP 1: Create a view to get ship to party information from Order Management.
SELECT oeh.header_id,
oeh.order_number,
stc.customer_id ship_to_cust_id,
stc.customer_number ship_to_cust_number,
stc.customer_name ship_to_customer,
TRIM(TRIM( slc.address1
|| ' '
|| slc.address2
|| ' '
|| slc.address3
|| ' '
|| slc.address4)
|| ' '
|| slc.city
|| ' '
|| slc.state
|| ' '
|| slc.postal_code)
ship_to_address,
slc.country ship_to_country,
sctc.contact_id ship_to_contact_id,
sctc.first_name || ' ' || sctc.last_name ship_to_contact
FROM oe_order_headers_all oeh,
hz_cust_site_uses_all stsu,
hz_cust_acct_sites_all stcas,
hz_party_sites sps,
hz_locations slc,
ra_customers stc,
ra_contacts sctc
WHERE 1 = 1
AND stsu.site_use_id = oeh.ship_to_org_id
AND stcas.cust_acct_site_id = stsu.cust_acct_site_id
AND stc.customer_id = stcas.cust_account_id
AND sps.party_site_id = stcas.party_site_id
AND slc.location_id = sps.location_id
AND sctc.contact_id(+) = oeh.ship_to_contact_id
AND oeh.header_id = p_oe_hdr_id;
STEP 2: Write a custom PL/SQL program to generate SOAP message and populate SHIP TO information.
Note: Following SOAP request example based on Kewill export compliance WSDL structure.
<?xml version="1.0" encoding="utf-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<soapenv:Body>
<executeScreening xmlns="'||gbl_exp_url||'">
<request>
<administration>
<subscriberId>'||gbl_user_id||'</subscriberId>
<subscriberPassword>'||gbl_user_pwd||'</subscriberPassword>
</administration>
<serviceRequest>
<transaction>
<userDefined>'||rec1.order_number||'</userDefined>
<shipFromCountry>
<domain>COUNTRY</domain>
<code>US</code>
</shipFromCountry>
<party>
<id>'||rec1.ship_to_cust_id||'</id>
<type>SHIP_TO</type>
<name>'||replace_special_chr(rec1.ship_to_customer)||'</name>
<address>'||replace_special_chr(rec1.ship_to_address)||'</address>
<country>
<domain>COUNTRY</domain>
<code>'||replace_special_chr(rec1.ship_to_country)||'</code>
</country>
<userDefined>'||rec1.ship_to_cust_id||'-SHTO</userDefined>
</party>
<party>
<id>'||rec1.ship_to_contact_id||'</id>
<type>SHTO_CONTACT</type>
<name>'||replace_special_chr(rec1.ship_to_contact)||'</name>
<address>'||replace_special_chr(rec1.ship_to_address)||'</address>
<country>
<domain>COUNTRY</domain>
<code>'||replace_special_chr(rec1.ship_to_country)||'</code>
</country>
<userDefined>'||rec1.ship_to_contact_id||'-SHTO-CTC</userDefined>
</party>
</transaction>
</serviceRequest>
<parameters>
<threshold>'||gbl_threshold||'</threshold>
<countryMatch>'||gbl_countryMatch||'</countryMatch>
<redflag>'||gbl_redflag||'</redflag>
</parameters>
</request>
</executeScreening>
</soapenv:Body>
</soapenv:Envelope>
STEP 3: Use oracle's UTL_HTTP to call export compliance web service and submit SOAP request with POST method
CREATE OR REPLACE PROCEDURE xx_ECS_check_prc (
p_soap_request IN CLOB,
x_soap_respond OUT CLOB,
x_return_status OUT varchar2,
x_message OUT varchar2
)
IS
http_req UTL_HTTP.req;
http_resp UTL_HTTP.resp;
resp xmltype;
i integer;
BEGIN
UTL_HTTP.SET_WALLET (PATH => p_path, password => p_password);
http_req := UTL_HTTP.begin_request (p_url, 'POST', 'HTTP/1.1');
UTL_HTTP.set_header (http_req, 'Content-Type', 'text/xml');
UTL_HTTP.set_header (http_req, 'Content-Length', LENGTH (p_soap_request));
UTL_HTTP.set_header (http_req, 'SOAPAction', '');
UTL_HTTP.write_text (http_req, p_soap_request);
http_resp := UTL_HTTP.get_response (http_req);
UTL_HTTP.read_text (http_resp, x_soap_respond);
UTL_HTTP.end_response (http_resp);
x_return_status := 'S';
EXCEPTION
WHEN UTL_HTTP.Request_Failed
THEN
UTL_HTTP.clear_cookies;
UTL_HTTP.end_response (http_resp);
x_return_status := 'E';
x_message := SQLERRM;
--=================
WHEN UTL_HTTP.Http_Server_Error
THEN
UTL_HTTP.clear_cookies;
UTL_HTTP.end_response (http_resp);
x_return_status := 'E';
x_message := SQLERRM;
--=================
WHEN UTL_HTTP.Http_Client_Error
THEN
UTL_HTTP.clear_cookies;
UTL_HTTP.end_response (http_resp);
x_return_status := 'E';
x_message := SQLERRM;
--=================
WHEN OTHERS
THEN
UTL_HTTP.clear_cookies;
UTL_HTTP.end_response (http_resp);
x_return_status := 'E';
x_message := SQLERRM;
END;
STEP 4: Parse SOAP response and check party denial status. If ship to party under denial status then call Order Management API to put order on hold.
CREATE OR REPLACE PROCEDURE appy_hold (
p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type,
x_return_status OUT varchar2,
x_msg_data OUT varchar2
)
IS
x_msg_count number;
x_msg_index NUMBER;
x_data VARCHAR2 (2000);
BEGIN
oe_debug_pub.initialize;
oe_debug_pub.setdebuglevel (5);
oe_msg_pub.initialize;
/* --Sample Data for Hold
p_hold_source_rec := OE_HOLDS_PVT.G_MISS_HOLD_SOURCE_REC;
p_hold_source_rec.hold_id := 23; -- hold_id--OE_HOLD_DEFINITIONS
p_hold_source_rec.hold_entity_code := 'O'; -- order level hold
p_hold_source_rec.hold_entity_id := 985736; -- header_id of the order
p_hold_source_rec.header_id := 985736; -- header_id of the order
p_hold_source_rec.HOLD_COMMENT :='Export Compliance Hold based on ship to country';
*/
OE_Holds_PUB.Apply_Holds (
p_api_version => 1.0,
p_commit => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_NONE,
p_hold_source_rec => p_hold_source_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
IF x_return_status <> FND_API.G_RET_STS_SUCCESS
THEN
FOR i IN 1 .. x_msg_count
LOOP
OE_MSG_PUB.get (p_msg_index => i,
p_encoded => FND_API.G_FALSE,
p_data => x_data,
p_msg_index_out => x_msg_index);
DBMS_OUTPUT.put_line ('OE Hold Error:' || x_data);
x_msg_data := TRIM (x_msg_data) || ' ' || x_data;
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS
THEN
x_return_status := 'E';
x_msg_data := SQLERRM;
END appy_hold;
STEP 5: Now customize Order Header Workflow to call custom ECS program at booking event.
Web service integration overview.
Now your System (Order Management/Shipping Execution) is ready to go.
Benefits:
- Realtime ECS integration
- No Third party service/application required
- Easy Development/Customizable
- Maintenance free and
- Cost effective
Friday, March 4, 2011
Coupa Integration
Oracle (E-Business Suite 11i/R12) - Coupa (e-Procurement) Integration.
Are you looking for Oracle E-Business Suite 11i/R12 and Coupa Integration?
Are you looking for Oracle E-Business Suite 11i/R12 and Coupa Integration?
No Problem, we can help you.
Our (OptioSys Technologies) cloud integration team has done Oracle-Coupa integration with and wihtout third party application/service.
You can use your IT/consulating resource to get develop own integration process using oracle based web service solution like Oracle SOA, BPEL, Oracle's utility (UTL_HTTP) or buy third party integration services like Dell Boomi, IBM Cast Iron etc.
If you want to develop your own integration program then get IT person who has following knowledge.
- Oracle E-Business Suite (at least AP and PO)
- Oracle PL/SQL programming
- Web Services
STEP 1: Write custom PL/SQL program to get approved invoices from coupa using UTL_HTTP
https://mycoupa.coupahost.com/api/invoices?exported=false&status=approved
STEP 2: Parse Coupa XML response and insert data into statging table
--xx_coupa_ap_inv_staging
STEP 3: Run data validation and insert validated staging data into AP interface tables
- ap_invoices_interface
- ap_invoice_lines_interface
STEP 4: Call AP Invoice Open Interface program to import interface data
STEP 5: Update coupa invoice with "exported = true"
(Invoices imported from coupa and created into AP module)
STEP 6: Generate log/email alert and send to AP Admin/user
Coupa integration overview
You can sync following data between oracle and Coupa.
- Suppliers
- Addresses
- Accounts
- Currencies
- Items
- Requisitions
- Purchase Orders
- Inventory Transactions
- Receipts
- Invoices
- Users
It is very simple, flexible, maintenance free and cost effective.
Visit for more information http://blog.optiosys.com/
Thursday, March 3, 2011
Salesforce.com Integration
Oracle (E-Business Suite 11i/R12) – Salesforce.com (SFDC) Integration
Are you looking for Oracle - Salesforce.com (SFDC) data integration?
These days many organizations are using cloud based applications like Salesforce.com, Coupa, Kewill Export Compliance, Service-Now along with internal oracle based application.
Data integration is a big question for those organizations who can not or don't want to spend too much money on integration tool.
No worry about it,
You can use oracle based web service solution like Oracle SOA, BPEL or oracle's web service utility like UTL_HTTP.
You can write simple SOAP request and call salesforce.com API then salesforce.com (SFDC) API will response you in XML format.
Now you can parse XML data and insert into your database table.
Key Feature
Our Cloud integration team has done Oracle E-Business Suite 11i/R12- Salesforce.com integration and able to do following activities.
Visit for more information http://blog.optiosys.com/
Data integration is a big question for those organizations who can not or don't want to spend too much money on integration tool.
No worry about it,
You can use oracle based web service solution like Oracle SOA, BPEL or oracle's web service utility like UTL_HTTP.
You can write simple SOAP request and call salesforce.com API then salesforce.com (SFDC) API will response you in XML format.
Now you can parse XML data and insert into your database table.
Key Feature
- Oracle based integration
- No third party tool/application/middleware or Service
- No Licensing
- Auto upgradable with oracle database
- Cost effective
- and more......
You can add integration log email alert
Our Cloud integration team has done Oracle E-Business Suite 11i/R12- Salesforce.com integration and able to do following activities.
- Order Creation/Booking from Salesforce.com Opportunity
- Auto RMA creation from salesforce.com case/RMAs
- Sync Installed Base data from Oracle EBS to SFDC and Oracle EBS to SFDC
- Sync Service Contract from Oracle EBS to SFDC and Oracle EBS to SFDC
- Product/Price List and shipping information from Oracle EBS to SFDC
- Sync Oracle's BOM data to SFDC
- Sync Customer's profile from Oracle EBS to SFDC and Oracle EBS to SFDC
- Oracle's Inventory transactions from Oracle EBS to SFDC
- Discoverer or BI Reporting from salesforce.com data
- and More
Visit for more information http://blog.optiosys.com/
Subscribe to:
Posts (Atom)