Oracle Apps Notes

A collection of my random notes, primarily on Oracle Apps

Custom Commands in WinSCP

The WinSCP tool provides a couple of handy features which allow users to execute custom commands directly on the server without having to open a separate terminal.
One such feature is the Command Line which you can enable from Options>Command Line. This will display the command line at the bottom of the GUI where you can execute arbitrary commands. The restriction is that you cannot enter any commands which require user-inputs or involve data transfer. You can enter commands like ‘ls -ltr’ to list files and the results will be displayed in a console window which pops up to display the output.
The second feature can be enabled from Options>Toolbars>Customer Command Buttons. Once enabled, a set of pre-existing commands such as Execute, Touch, Grep etc will be displayed. So if , for example, you want to execute a shell script on the remote server, you select the appropriate file and then click on the Execute command. The shell script will get executed. This saves you from the trouble of having to open a new terminal and then typing the command to execute the script.
However, the most interesting thing about this feature is the little gear symbol. Click on this gear symbol and you will realise that you can modify the pre-existing commands and add your own ones. This is especially useful when you have to do some repetitive task such as compiling a form or uploading a workflow in Oracle EBS.

Custom commands overview

You can write you own custom command to compile a form or to upload a workflow file. So the next time you make changes to the form or workflow you can just copy it to the server, select the file on the server and click on your custom command. You do not have to perform the additional step of opening a server session on PuTTY to execute the compile or upload command. The screenshot below shows the details of the XXUploadWF custom command which I use to upload workflow files.

Custom command details

Another useful custom command I have defined is XXCompile_Form. The command for this is


frmcmp_batch module="!" userid=apps/apps output_file=`echo "!" | awk '{print substr($0,0,index($0, ".fmb")-1)}'`.fmx module_type=form compile_all=special

It compiles the selected .fmb file(in an EBS R12 environment) and generates an .fmx file of the same name.

More details on writing custom commands for WinSCP is available here.

 

Notes on Extracting the Day of the Week

There are a couple of date formats which can be used to extract the day of the week.

D – Returns a number from 1 to 7 indicating the day of the week

Day – Returns the name of the day of the week

Here are these two formats in use:

1

2

But there is a catch, using the ‘D’ format will return different values depending on the the NLS_TERRITORY that is set. While some cultures consider Sunday to be the first day of the week, others grant that privilege to Monday. Consequently, if the NLS_TERRITORY is set to AMERICA, Saturday will be the seventh day of the week starting from Sunday and a ’7′ will be returned. Setting the NLS_TERRITORY to ITALY, however, will return ’6′ indicating that Saturday is the sixth day of the week starting from Monday.

3

With the ‘Day’ format, Oracle returns the “Name of day, padded with blanks to display width of the widest name of day in the date language used for this element“. In English, Wednesday is the day with the longest name with a length of 9 characters. Hence the ‘Day’ format returns the name of the day padded with blanks till the ninth character.

4

I wouldn’t have come across the quirks of these two date formats if I hadn’t burnt my fingers recently using one of them. So, if you are reading this, please consider yourself warned :)

Outbound Customer Interface Using Business Events – The Bare Bones

It is possible to write an outbound customer interface in Oracle without using Business Events. You would start by writing an SQL query which fetches data from the various TCA tables containing the entities that you are interested in. So, for example if you want to write an outbound interface which fetches all new and updated parties, you would start with something like

SELECT party_name
      ,party_number
      ,party_type
FROM   hz_parties
WHERE TRUNC(last_update_date) = TRUNC(SYSDATE);

However, as the TCA entities and their relationships increase in both number and complexity in the EBS instance, it becomes more practical and easier to use Business Events to track and record the changes. What are business events? Well, they are just some events which are fired whenever specific business actions take place.For more information on business events, Google is your friend. Using business events to implement an outbound interface essentially involves two steps:

  1. Identifying the appropriate business event
  2. Creating a subscription for the event

Let us assume, for simplicity’s sake, that we want to implement an outbound interface which is used to send updated customer account information to an external system. The first step, in such a scenario, is identifying the business event which would be raised when a customer account is updated. One of the better ways to do this is from the Integration Repository which is accessible from the Integrated SOA Gateway responsibility in R12. You can also query the WF_EVENTS_VL or try looking up the documents. The business event raised when a custom account is updated is ‘oracle.apps.ar.hz.CustAccount.update’.

Once this is known, we need to create a subscription for this. You create a subscription to specify a set of actions which need to be performed whenever a certain business event takes place. In our case, let us assume that whenever the ‘oracle.apps.ar.hz.CustAccount.update’ event is raised (that is, whenever a customer account is updated), we want to store the account information in a staging table. At the end of the day, we will collect the information for all accounts that were updated during the day and send it to an external system. So, in our case the subscription that we will create will fetch information for the updated customer account and store it in a staging table. The subscription can perform actions such as calling a Java class, a PL/SQL function or a Workflow Process. In this case, we will call a PL/SQL function. As a rule, the PL/SQL function should have the following input parameters and should return a VARCHAR2 data type.

FUNCTION insert_stg_tbl (p_subscription_guid IN RAW
                        ,p_event IN OUT NOCOPY wf_event_t)
RETURN VARCHAR2;

One thing to note here is that each event has it own parameter(s) which will be passed on to the function called by the event subscription when the event is raised. They are accessible from the p_event parameter in the function. For the  ‘oracle.apps.ar.hz.CustAccount.update’ business event, the p_event  parameter will contain the cust_account_id. A list of parameters for various events is provided in the Oracle Trading Community Architecture Technical Implementation Guide.

With the basic theory out of the way, here are the steps that need to be followed to complete our interface:

From any appropriate Workflow responsibility, go to the Business Events page (For example: Workflow Administrator Web Applications>Administrator Workflow>Business Events) and search for the event ‘oracle.apps.ar.hz.CustAccount.update’

Image

Click on the Subscription icon to view existing subscriptions for the events.

Image

Click on the Create Subscription button to create a new subscription. Most of the required fields should be auto-populated. If not, fill in the details as show in the screenshot. The value for the ‘Sytem’ field will be available in the LOV. The ‘Action Type’ should be Custom. After entering the values in the fields, click on the Next button

Image

In the ‘PL/SQL Rule Function’ field, enter the name of the function which will be used to perform actions when the event is raised. I have entered xx_cust_outbound_pkg.insert_stg_tbl where xx_cust_outbound_pkg is the package which contains the function insert_stg_tbl. Enter values in the ‘Owner Name’ and ‘Owner Tag’ fields. These should be valid application short names. Then click on the Apply button.

Image

For our subscription function, we need to create a table which will store the cust_account_id, the account_name, the action that was performed and the last_update_date

CREATE TABLE xx_cust_outbound_tbl(
cust_account_id NUMBER
,account_name VARCHAR2(254)
,action VARCHAR2(30)
,last_update_date DATE
);

The package specification and body containing the function is provided below

CREATE OR REPLACE PACKAGE xx_cust_outbound_pkg IS

 FUNCTION insert_stg_tbl (p_subscription_guid IN RAW
 ,p_event IN OUT NOCOPY wf_event_t)
 RETURN VARCHAR2;

END xx_cust_outbound_pkg;
/

CREATE OR REPLACE PACKAGE BODY xx_cust_outbound_pkg IS
 --function which will be invoked by the subscription
 FUNCTION insert_stg_tbl (p_subscription_guid IN RAW
 ,p_event IN OUT NOCOPY wf_event_t)
 RETURN VARCHAR2 IS

 lv_cust_account_id NUMBER;
 lv_account_name VARCHAR2(254);

 BEGIN
 --check the event name
 IF UPPER(p_event.geteventname()) = 'ORACLE.APPS.AR.HZ.CUSTACCOUNT.UPDATE' THEN
 --if an account is updated, store the cust_account_id, account_name, action performed and update date in the staging table 

 --get the CUST_ACCOUNT_ID
 lv_cust_account_id := p_event.getvalueforparameter('CUST_ACCOUNT_ID'); 

 --get the account name based on the CUST_ACCOUNT_ID
 SELECT account_name
 INTO lv_account_name
 FROM hz_cust_accounts
 WHERE cust_account_id = lv_cust_account_id; 

 --insert into staging table
 INSERT INTO xx_cust_outbound_tbl VALUES (lv_cust_account_id, lv_account_name, 'ACCOUNT_UPDATE', SYSDATE); 

 END IF;
 COMMIT;

 RETURN 'SUCCESS';

 EXCEPTION
 WHEN OTHERS THEN
 RETURN 'ERROR';
 END insert_stg_tbl;

END xx_cust_outbound_pkg;
/

Once these steps are done all you need to do is update a customer account by navigating to Trading Community Manager>Trading Community>Customers>Standard, wait for the event to be processed and voila! you have the details in your staging table.

Image

Notes:

  1. Anil Passi’s blog has a code listing which you can use to find the parameters for a business event.
  2. To check if an event was raised, you can query the WF_DEFFERED table (provided you entered a phase>99 while creating the subscription) where the CORRID column stores the event name in the form APPS:<event_name>. A value of 2 in the STATE column indicates that the event was processed.
  3. Noticed that sometimes in EBS 12.1.3 changing the name of the subscription function in the front end does not take effect unless the middle tier is bounced. Could be an issue with my instance though.
Follow

Get every new post delivered to your Inbox.

Join 33 other followers