Default parameter values in package subprograms

For a procedure (or function) in a package, you can assign default values only in the package specifications without assigning them in the package body:

SQL> CREATE OR REPLACE PACKAGE test_default_pkg AS
PROCEDURE set_global (p_num IN NUMBER DEFAULT 1);
END;
/

Package TEST_DEFAULT_PKG compiled

SQL> SHOW ERRORS;
No errors.
SQL> CREATE OR REPLACE PACKAGE BODY test_default_pkg AS
PROCEDURE set_global (p_num IN NUMBER) AS
BEGIN
NULL;
END;
END;
/

Package body TEST_DEFAULT_PKG compiled

SQL> SHOW ERRORS;
No errors.

The reverse is not allowed though:

SQL> CREATE OR REPLACE PACKAGE test_default_pkg AS
PROCEDURE set_global (p_num IN NUMBER);
END;
/

Package TEST_DEFAULT_PKG compiled

SQL> SHOW ERRORS;
No errors.
SQL> CREATE OR REPLACE PACKAGE BODY test_default_pkg AS
PROCEDURE set_global (p_num IN NUMBER DEFAULT 1) AS
BEGIN
NULL;
END;
END;
/

Package body TEST_DEFAULT_PKG compiled

Errors: check compiler log
SQL> SHOW ERRORS;
Errors for PACKAGE BODY HR.TEST_DEFAULT_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------------------
2/25 PLS-00593: default value of parameter "P_NUM" in body must match that of spec

Also, if the default value is specified in both the package specification and package body, they must match:

SQL> CREATE OR REPLACE PACKAGE test_default_pkg AS
PROCEDURE set_global (p_num IN NUMBER DEFAULT 1);
END;
/

Package TEST_DEFAULT_PKG compiled

SQL> SHOW ERRORS;
No errors.
SQL> CREATE OR REPLACE PACKAGE BODY test_default_pkg AS
PROCEDURE set_global (p_num IN NUMBER DEFAULT NULL) AS
BEGIN
NULL;
END;
END;
/

Package body TEST_DEFAULT_PKG compiled

Errors: check compiler log
SQL> SHOW ERRORS;
Errors for PACKAGE BODY HR.TEST_DEFAULT_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------------------
2/25 PLS-00593: default value of parameter "P_NUM" in body must match that of spec

 

Logical operators in RETURN clause of a function

Here is what I learnt today.

You can use logical operators in the RETURN clause of a function whose return type is BOOLEAN. So for example, you can write a function with the following structure:

create or replace function test_fn(p_param varchar2)
return boolean
as
l_value varchar2(10):= 'TEST';
begin
return false
or p_param = l_value
or length(p_param) = 5 ;
end;
/
show errors;

The logical truth table is used to evaluate the expression in the RETURN clause and hence this function will return TRUE if the value passed to it is either TEST or any five-characters long string. For all other cases it will return FALSE.

Of course, the same logic could have been coded in lots of different ways but the example was just to show that this was one of the ways.

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’

Click on the images for a larger view.

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

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

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.

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.

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.