Thursday, October 29, 2009

Calling a PL/SQL Procedure from Form Persnalization

Question: How to invoke PL/SQL Code from Form personalization.

Answer: Using Action Type: "Builtin" and Builtin Type: "Execute a Procedure".
Little background about my requirement here..
- Wanted to gather inventory valuations in system.
- When ? -  At Period closing time in Inventory.
In short - need to capture Inventory On-Hand quanties as at month-end closing period.

So to implement solution for the above requirement, I did small R&D shown below,  before starting actual development.
Initially thought of developing a DB trigger, which will based the standard table - 'org_acct_periods'.  But later droped that idea, as it is difficult to enable or disable, when ever we want.
---
create table temp1(n1 number);

create or replace procedure proc1
is
begin
insert into temp1 values(1);
end ;
/

Do the form personalization in the following way..


















Now create an item and save it. So that form personalization process will invoke the procedure and execute the same.
















Query the tables and verify if data is inserted or not.

SQL> select * from temp1;

N1
----------
1
1

So the conclusion is - It works.


No comments: