Rate Interface Design Idea for Oracle Apps

Reuters, Bloomberg are vendors who provide currency rates. Many applications in the business world fetches the daily rates from them and import them into the applications. The same can also be done in oracle EBS by following the design steps below,

1. Generate a rate request file according the guide provided by the vendor. Can be produced by running a concurrent program which will write the rate request file in the form of a CP output file.
2. A shell script can be called from EBS to encrypt the out file and send it to the vendor through FTP or any other suitable media.
3. Send the encrypted request file to the vendor through ftp or any other media.
4. Obtain the response file from the vendor and decrypt it.
5. Again use a shell program to load the data into GL_DAILY_RATES_INTERFACE table.
6. Call the concurrent program “Program - Daily Rates Import and Calculation”.
7. Follow the standard oracle apps processes for rate import until the end.

This article can also be helpful.
http://realworldoracleapps.blogspot.se/2008/02/currency-exchange-rate-interface.html

Import a package by Using SQL Server Management Studio

1. Click Start, point to Microsoft SQL Server, and then click SQL Server Management Studio.
2. In the Connect to Server dialog box set the following options:
   
   • In the Server type box, select Integration Services.
   • In the Server name box, provide a server name or click <Browse for more…> and locate the server to use.
3. If Object Explorer is not open, on the View menu, click Object Explorer.
4. In Object Explorer, expand the Stored Packages folder.
5. Expand the subfolders to locate the folder into which you want to import a package.
6. Right-click the folder, click Import Package. and then do one of the following:
   
   • To import from an instance of SQL Server, select the SQL Server option, and then specify the server and select the authentication mode. If you select SQL Server Authentication, provide a user name and a password.
     Click the browse button (…), select the package to import, and then click OK.
   • To import from the file system, select the File system option.
     Click the browse button (…), select the package to import, and then click Open.
   • To import from the SSIS Package Store, select the SSIS Package Store option and specify the server.
     Click the browse button (…), select the package to import, and then click OK.
7. Optionally, update the package name.
8. To update the protection level of the package, click the browse button (…) and choose a different protection level by using the Package Protection Level dialog box. If the Encrypt sensitive data with password or the Encrypt all data with password option is selected, type and confirm a password.
9. Click OK to complete the import.

 Source: http://msdn.microsoft.com/en-us/library/ms141235%28v=sql.105%29.aspx

Export an SSIS package by Using SQL Server Management Studio

1. Click Start, point to Microsoft SQL Server, and then click SQL Server Management Studio.
2. In the Connect to Server dialog box, set the following options:
   
   • In the Server type box, select Integration Services.
   • In the Server name box, provide a server name or click <Browse for more…> and locate the server to use.
3. If Object Explorer is not open, on the View menu, click Object Explorer.
4. In Object Explorer, expand the Stored Packages folder.
5. Expand the subfolders to locate the package you want to export.
6. Right-click the package, click Export, and then do one of the following:
   
   • To export to an instance of SQL Server, select the SQL Server option, and then specify the server and select the authentication mode. If you select SQL Server Authentication, provide a user name and a password.
     Click the browse button (…), and expand the SSIS Packages folder to locate the folder to which you want to save the package. Optionally, update the default name of the package, and then click OK.
   • To export to the file system, select the File System option.
     Click the browse button (…) to locate the folder to which you want to export the package, type the name of the package file, and then click Save.
   • To export to the SSIS package store, select the SSIS Package Store option, and specify the server.
     Click the browse button (…), expand the SSIS Packages folder, and select the folder to which you want to save the package. Optionally, enter a new name for the package in the Package Name text box. Click OK.
7. To update the protection level of the package, click the browse button (…) and choose a different protection level by using the Package Protection Level dialog box. If the Encrypt sensitive data with password or the Encrypt all data with password option is selected, type and confirm a password.
8. Click OK to complete the export.

 Source: http://msdn.microsoft.com/en-us/library/ms141235%28v=sql.105%29.aspx

Oracle Apps framework to delete concurrent program

Oracle Apps framework to delete concurrent program

Begin
fnd_program.delete_program('short name of the concurrent program to be deleted','Schema');
fnd_program.delete_executable('short name of the concurrent program to be deleted','Schema');
commit;

End;

 

MS SQL Server Basic Queries

1. List all the database objects 

Select * from Sysobjects

The list of all of the possible values for the xtype column in the sysobjects table of a SQL Server database:

• C - CHECK constraint
• D - Default or DEFAULT constraint
• F - FOREIGN KEY constraint
• L - Log
• P - Stored procedure
• PK - PRIMARY KEY constraint
• RF - Replication filter stored procedure
• S - System table
• TR - Trigger
• U - User table
• UQ - UNIQUE constraint
• V - View
• X - Extended stored procedure

2. List all the columns on the database

Select * from syscolumns

Increment Date Parameter Doesn't Re-evaluate the Date Value Issue

I have had this issue recently where I had put default type for the concurrent program parameter as SQL statment and put a value. I expected this to be re-evaluated everytime the concurrent program will be executed. But I found it takes the first value I had put for the program. It doesn't increment the parameter value eventhough I had checked the Increment Date Value. I found the metalink note 339849.1(Unexpected Behavior Using Increment Date Parameter Each Run) that explains how this situation can be handled.

 Technology Adapters 10g to 11g Migration

 

Upgrade Tasks associated with Technology Adapters

Ø  Verifying Adapters with the Oracle JDeveloper 11g Adapter Configuration Wizard

After upgrading Oracle SOA Suite 10g projects to Oracle SOA Suite 11g, Oracle recommends to use the adapter configuration wizard to verify the upgrade of your technology adapters. This will ensure that all required fields in 11g are populated and that the adapter connections to new Oracle Fusion Middleware SOA Suite 11g environment are valid.

Ø  Upgrading Adapter Header variables

Issue: After upgrading an application that uses adapter headers, you will likely receive an error after you open the Application in Oracle JDeveloper 11g. The error will indicate that a variable or parameter was not recognized while it was parsing a header function.

This is caused by a change in the way adapter header variables are defined in Oracle Fusion Middleware SOA Suite 11g.  In Oracle Application Server 10g Release 3 (10.1.3), you create adapter header variables in a wsdl file that has a name like, or a similar name for the direction and adapter type you are using: fileAdapterOutboundHeader.wsdl

How to create a JNDI for MQ Adapter on Weblogic

How to create a JNDI for MQ Adapter on Weblogic

This post explains how to create a simple JNDI for MQ Adapter in Weblogic 10.3.x. This JNDI properties can be further changed to configure One-way or Two-way SSL on MQ Adapter as explained in post Enabling SSL on MQ Series Adapter - Part 2.

• Login to Weblogic Server Administration Console and navigate to Deployments > MQSeriesAdapter > Configuration Tab > Outbound Connection Pool.





• Click on New button and select javax.resource.cci.ConnectionFactory and click Next.

Correlating the request/response messages in MQ Series Adapter – Oracle SOA 11g

Correlating the request/response messages in MQ Series Adapter – Oracle SOA 11g

 

Mapping a response to a request in a request-reply interaction requires correlation.The MQ adapter provide the inbuilt options to correlate the request and response messages, there is no need of implementing the custom correlation to correlate the request and response messages.

Each MQSeries request message contains a message ID and a correlation ID. When an application receives a request message from Oracle BPEL PM, it checks for the correlation schema defined for the response message. Based on the correlation schema, the application generates the message ID and correlation ID of the response message. Based on the Message ID/Correlation ID the request/response messages will be correlated in the MQAdapter.

How to create and purge a AQ Queue/Topic

I was looking for testing the AQ Adapter and that forced me to look for steps on how to create and purge a AQ Queue/Topic? I referred the Article which explains all about AQ but in brief the steps are as under,

Creating a AQ Queue:

1. Grant the privileges to the Database User
2. Create a Queue/Topic table
3. Create a Queue/Topic
4. Start the Queue/Topic

In SQL terms,

1. Grant the privileges:

connect as sys;

grant connect, resource, aq_administrator_role to [USER] identified by [USER];
grant execute on sys.dbms_aqadm to [USER];
grant execute on sys.dbms_aq to [USER];
grant execute on sys.dbms_aqin to [USER];
grant execute on sys.dbms_aqjms to [USER];

Oracle SOA 11g AQ Adapter Resource 2

WebLink

http://jamessmith73.wordpress.com/oracle-fusion-middleware/oracle-soa-bpm-11g-blogs/soa-10g/soa-hands-on-4/

Oracle SOA Suite 11g – Configure AQ Adapter Resource 1

AQAdapters (Same type of config works for Apps adapter)

AQ is Oracle’s Advanced Queuing – a database backed channel. We use AQ Queues a lot when doing integration projects and it always helps to have a local install of SOA Suite with AQ capabilities (i.e. your own DB with AQ Queues etc)

Weblogic requires a “weblogic-ra.xml” along with the “ra.xml” file in the “META-INF” folder of the RAR file for the adapter.  The trickiest part is getting the Web console to apply changes … what I mean is that initially I tried to “Update” an existing “Deployment” of AQAdapter from the Weblogic Admin Console and it blew up … later I found out because the AQAdapter was packaged up in a RAR file (and not exploded on the filesystem) …as a result my changes from the console were not making it through.

The steps below show how I extracted the AQAdapter.rar to AQAdapter folder I created under the $SOA_HOME/soa/connectors/  folder. You can use these steps to configure any adapter (I have personally tested Oracle Apps Adapter – screen shots later)