Step1 : Create the Element and Element links
Step2: write the fast formula
Step3: Attach the fast formula in Formula Results
Package used to call the fast formula from the backed : ff_exec
Simple Code Snippet below:
l_formula_id NUMBER;
l_element_inputs ff_exec.inputs_t;
l_element_outputs ff_exec.outputs_t;
l_in_count NUMBER;
l_out_count NUMBER;
l_pay_value NUMBER;
BEGIN
BEGIN
SELECT formula_id
INTO l_formula_id
FROM ff_formulas_f
WHERE formula_name = ‘XX_PAYROLL_FORMULA’
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘NO formula exists’);
END;
IF l_formula_id IS NOT NULL
THEN
— Insert FND_SESSIONS row ( Optional )
INSERT INTO fnd_sessions
( session_id,
effective_date
)
VALUES
( USERENV (‘sessionid’),
p_effective_date
);
—
— Initialize the formula.
—
ff_exec.init_formula (l_formula_id,
p_effective_date,
l_element_inputs,
l_element_outputs
);
—
— Loop through the Input Values
—
FOR l_in_count IN l_element_inputs.FIRST .. l_element_inputs.LAST
LOOP
—
— Pass The each Input value name and its Value : Eg: START_DATE and p_start_date
—
IF (l_element_inputs (l_in_count).NAME = ‘START_DATE’)
THEN
l_element_inputs (l_in_count).VALUE :=
fnd_date.date_to_canonical (p_start_date);
END IF;
END LOOP;
—
–Run The formula
—
ff_exec.run_formula (l_element_inputs, l_element_outputs);
—
— Get the Out Put Values
—
FOR l_out_count IN l_element_outputs.FIRST .. l_element_outputs.LAST
LOOP
—
— Get all the Out Put Values Here L_PAY_VALUE is the out put value
—
IF (l_element_outputs (l_out_count).NAME = ‘L_PAY_VALUE’)
THEN
l_pay_value := l_element_outputs (l_out_count).VALUE;
END IF;
END LOOP;
RETURN (l_pay_value);
END;
Here i am Explaining how to create Fnd Messages via E-Business suite and the implementation of message retrieval via the pl/sql API package provided with Oracle Applications.
Creating an Oracle E-Business Suite Message
To create a message in the E-Business suite message library you will need the “Application Developer” responsibility.
Navigate to Application Developer > Application > Messages. This will launch a form
Enter a unique name for your message
Eg: XX_CUSTOMER_MSG
Select the language that your message is written in and the application that the message belongs
Enter the message text in the “Current Message Text” box.
Eg: This is my first message
Click the save icon.
Retrieving a message using PL/SQL:
In order to retrieve the message from the database we need to use a standard API’s in the FND_MESSAGE package.
An E-Business suite message should be retrieved as follows:
1. Clear the current session of any message variables that may already be set
2. Tell E-Business suite which message you wish to retrieve
3. Retrieve the actual message string
4. Clear the session (Optional)
Below is the PL/SQL Block to retrive the message
DECLARE
my_message VARCHAR2(100);
BEGIN
–Initialize Apps Session
fnd_global.apps_initialize( user_id => 1234
,resp_id => 1235
,resp_appl_id => 1236
);
/*–Note: You will get the uer_id, resp_id and Resp_appl_id using below Query
select fnd.user_id ,
fresp.responsibility_id,
fresp.application_id
from fnd_user fnd,
fnd_responsibility_tl fresp
where fnd.user_name = ‘OEAG’
and fresp.responsibility_name = ‘Custom HRMS
*/
–Clear the existing session
FND_MESSAGE.CLEAR;
–Tell e business suite which message you want (custom application short name/message name)
FND_MESSAGE.SET_NAME(‘XXERP’,’XX_CUSTOMER_MSG’);
–Retrieve the message
my_message := FND_MESSAGE.GET;
–Output the message
DBMS_OUTPUT.PUT_LINE(my_message);
END;
Output for the Above Block Is : This is my first message
Using Tokens in the message:
The Oracle E-Business suite allows the substitution of tokens within a message string to enable the programmer to add dynamic content to the message at run time.
Open the E-Business Suite message create a New Message
Navigate to Application Developer > Application > Messages. This will launch a form
Enter a unique name for your message
Eg: XX_UNAME_TOKEN_MSG
Select the language that your message is written in and the application that the message belongs
Enter the message text in the “Current Message Text” box.
Eg: This is my second message and the Token User name is &USERNAME
Click the save icon.
Note: In order to insert a token into a message it is necessary to prefix the token with a ampersand e.g. &USERNAME
Retrieving message With Token Substitution
Here USERNAME is called as TOKEN, we will Add the value dynamically
Example Block is below:
DECLARE
my_message VARCHAR2(100);
BEGIN
–Initialize Apps Session
fnd_global.apps_initialize( user_id => 1234
,resp_id => 1235
,resp_appl_id => 1236
);
/*–Note: You will get the uer_id, resp_id and Resp_appl_id using below Query
select fnd.user_id ,
fresp.responsibility_id,
fresp.application_id
from fnd_user fnd,
fnd_responsibility_tl fresp
where fnd.user_name = ‘OEAG’
and fresp.responsibility_name = ‘Custom HRMS Responsibility’;
*/
–Clear the existing session
FND_MESSAGE.CLEAR;
–Tell e business suite which message you want (Application short name/message name)
FND_MESSAGE.SET_NAME(‘
–Set the username message token with the current applications user
FND_MESSAGE.SET_TOKEN(‘USERNAME’,FND_GLOBAL.USER_NAME);
–Retrieve the message
my_message := FND_MESSAGE.GET;
–Output the message
DBMS_OUTPUT.PUT_LINE(my_message);
END;
Out put for above block is :
Downloading and Uploading Messages using the Generic Loader
To download our example message we would use the following command at the Unix prompt on the mid-tier:
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_UNAME_TOKEN_MSG.ldt
FND_NEW_MESSAGES APPLICATION_SHORT_NAME=’PER’ MESSAGE_NAME=”XX_UNAME_TOKEN_MSG”
To Upload our example message we would use the following command at the Unix prompt on the mid-tier:
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_UNAME_TOKEN_MSG.ldt
————–
a) set of books should be defined
b) Current Conversion rates and accounting periods need to be defines
c) Source and Category Name Should be defined
Interface Tables:
—————-
GL_INTERFACE
Base Tables:
———–
GL_JE_HEADERS
GL_JE_LINES
GL_JE_BATCHES
Standard Program:
Go to General Ledger Vision Operations(USA)
Run =>Import
Here Give the Source name and Save.
While Click on the save button Back end One Program Concurrent Program is running.If it is Success the Records are Successfully loaded from interface table to base Table Others wise Some Error are there.
Copy that Request_id and Enter into Generals our Records will be there………..
Validation Columns:
——————
Source period_name currency_code set_of_books_id
je_source je_catregory accounting_date entered_dr , entered_cr
accounted_cr accounted_dr encumberance_type_id
Source = ‘NEW’
period need to be open status in gl_period_statuses
souce_name defined in gl_je_source table
category_name defines gl_je_Category
currency available in fnd_Currencies
accounted_cr and accounted_dr total should be same.
Control file for GL_Interface:
LOAD DATA
INFILE *
TRUNCATE INTO TABLE GL_INTERFACE_TEMP
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
TRAILING NULLCOLS
(STATUS,
SET_OF_BOOKS_ID,
ACCOUNTING_DATE,
CURRENCY_CODE,
DATE_CREATED,
CREATED_BY,
ACTUAL_FLAG,
USER_JE_CATEGORY_NAME,
USER_JE_SOURCE_NAME,
SEGMENT1,
SEGMENT2,
SEGMENT3,
SEGMENT4,
SEGMENT5,
ENTERED_DR,
ENTERED_CR,
ACCOUNTED_DR,
ACCOUNTED_CR,
GROUP_ID)
BEGIN DATA
NEW,1,11-AUG-2002,USD,11-AUG-2002,1318,A,Inventory,JETFORMS,01,110,7730,0000,000,555,555,555,555,11
NEW,1,11-AUG-2002,USD,11-AUG-2002,1318,A,Inventory,JETFORMS,01,110,7730,0000,000,554,554,554,554,11
Script
sqlldr apps/apps control=’/apps/aptest/visappl/xxcus/11.5.0/bin/xx_gl.ctl’ log=’/apps/aptest/visappl/xxcus/11.5.0/bin/xx_gl.log’
exit 0
GL Interface Package:
CREATE OR REPLACE package body APPS.xx_gl_int_pkg
is
procedure dis_log(p_msg in varchar2)
is
begin
fnd_file.put_line(fnd_file.log,p_msg);
end;
procedure main(errbuf out varchar2,
retcode out varchar2
)
is
cursor c1 is select a.rowid row_id,a.* from GL_INTERFACE_TEMP a;
v_gl_int gl_interface%rowtype;
v_process_flag varchar2(10);
v_error_msg varchar2(100);
v_tot_err_msg varchar2(1000);
begin
dis_log(‘before entering the loop’);
for i in c1 loop
v_error_msg :=null;
v_process_flag:=’S’;
v_tot_err_msg:=null;
v_gl_int:=null;
–currency_code validation
begin
select currency_code into v_gl_int.currency_code
from fnd_currencies
where currency_code=i.currency_code;
exception
when no_data_found then
v_process_flag:=’E’;
v_error_msg := ‘Invalid Currency Code =>’||i.currency_code;
v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
when others then
v_process_flag:=’E’;
v_error_msg := ‘ Exception at Currency Code =>’||i.currency_code;
v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
end;
–user_je_source_name validation
begin
select user_je_source_name into v_gl_int.user_je_source_name
from gl_je_sources
where user_je_source_name=i.user_je_source_name;
exception
when no_data_found then
v_process_flag:=’E’;
v_error_msg := ‘Invalid Sourec Name =>’||i.user_je_source_name;
v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
when others then
v_process_flag:=’E’;
v_error_msg := ‘ Exception at Sourec Name =>’||i.user_je_source_name;
v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
end;
–category_name validation
begin
select user_je_category_name into v_gl_int.user_je_category_name
from gl_je_categories
where user_je_category_name=i.user_je_category_name;
exception
when no_data_found then
v_process_flag:=’E’;
v_error_msg := ‘Invalid category_name =>’||i.user_je_category_name;
v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
when others then
v_process_flag:=’E’;
v_error_msg := ‘ Exception at category_name =>’||i.user_je_category_name;
v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
end;
–user id validation
begin
select user_id into v_gl_int.created_by from fnd_user
where user_id = i.created_by;
exception
when no_data_found then
v_process_flag:=’E’;
v_error_msg := ‘Invalid user id =>’||i.created_by;
v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
when others then
v_process_flag:=’E’;
v_error_msg := ‘ Exception at user id =>’||i.created_by;
v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
end;
— set of books id validation
begin
SELECT SET_OF_BOOKS_ID INTO v_gl_int.set_of_books_id
FROM GL_SETS_OF_BOOKS WHERE SET_OF_BOOKS_ID=i.set_of_books_id;
exception
when no_data_found then
v_process_flag:=’E’;
v_error_msg := ‘Invalid set of books id =>’||i.set_of_books_id;
v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
when others then
v_process_flag:=’E’;
v_error_msg := ‘ Exception atset of books id =>’||i.set_of_books_id;
v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
end;
v_gl_int.status :=i.status;
— v_gl_int.set_of_books_id :=i.set_of_books_id;
v_gl_int.accounting_date :=i.accounting_date;
— v_gl_int.currency_code :=i.currency_code;
v_gl_int.date_created :=i.date_created;
–v_gl_int.created_by :=i.created_by;
v_gl_int.actual_flag :=i.actual_flag ;
–v_gl_int.user_je_category_name :=i.user_je_category_name;
–v_gl_int.user_je_source_name :=i.user_je_source_name;
v_gl_int.segment1 :=i.segment1;
v_gl_int.segment2 :=i.segment2;
v_gl_int.segment3 :=i.segment3;
v_gl_int.segment4 :=i.segment4;
v_gl_int.segment5 :=i.segment5 ;
v_gl_int.entered_dr :=i.entered_dr;
v_gl_int.entered_cr :=i.entered_cr;
v_gl_int.accounted_dr :=i.accounted_dr;
v_gl_int.accounted_cr :=i.accounted_cr;
v_gl_int.group_id :=i.group_id;
dis_log(‘before inserting the loop’);
if v_process_flag = ‘S’ then
insert into gl_interface values v_gl_int;
end if;
update GL_INTERFACE_TEMP set process_flag=v_process_flag,
error_message=v_tot_err_msg
where rowid=i.row_id;
dis_log(‘after inserting the loop’);
end loop;
exception
when others then
dis_log(‘exception occured at main loop’);
end main;
end xx_gl_int_pkg;
SELECT SUM (target_qty), item_id
FROM ( SELECT moqv.subinventory_code subinv,
moqv.inventory_item_id item_id,
SUM (transaction_quantity) target_qty
FROM mtl_onhand_qty_cost_v moqv
WHERE moqv.organization_id = :org_id
AND moqv.inventory_item_id = :item_id
GROUP BY moqv.subinventory_code,
moqv.inventory_item_id,
moqv.item_cost
UNION
SELECT mmt.subinventory_code subinv,
mmt.inventory_item_id item_id,
-SUM (primary_quantity) target_qty
FROM mtl_material_transactions mmt, mtl_txn_source_types mtst
WHERE mmt.organization_id = :org_id
AND transaction_date >= TO_DATE (:hist_date) + 1
AND mmt.transaction_source_type_id =
mtst.transaction_source_type_id
AND mmt.inventory_item_id = :item_id
GROUP BY mmt.subinventory_code, mmt.inventory_item_id) oq
GROUP BY oq.item_id
Two types of classification s:
I.STATIC CURSOR S:
Static : Normal cursor (implicit or explicit)
Cursor attributes for implicit and explicit:
%FOUND – records fetched successfully
%NOTFOUND – no records fetched
%ROWCOUNT – Number of records fetched
%ISOPEN – returns TRUE if cursor is open
a. Implicit :
Cannot be opened outside the statement
More fast and less coding effort.
Will never raise INVALID_CURSOR error
Raises NO_DATA_FOUND and TOO_MANY_ROWS exceptions (eg: select <stmt>)
Example Implicit Cursor:
select * from emp
If SQL%FOUND then
v_count:= SQL%ROWCOUNT
end if;
b. Explicit : 2 network round trips. Store data first then retrieve data.
More programmatic control.
Programmer could open; fetch data, close, check attributes etc.
Syntax:
open c1; — cursor c1 is select <stmt>
fetch <>
exit when c1%NOTFOUND
Example Explicit cursor:
Without Using Loop s
Declare
Cursor cur1 is
select ename,empno,sal from emp
where sal<50000 and deptno=50
begin
open cur1;
fetch cur1 into v_ename,v_empno,v_sal;
exit when cur1%notfound;
—<do processing>
close cur1;
end;
Using Loops:
Declare
Cursor cur1 is
select ename,empno,sal from emp
where sal<50000 and deptno=50
begin
For rec in cur1
loop
dbms_output.put_line(‘Employee Number ‘||rec.empno);
end loop;
end;
Using Loops with Cursor Parameters:
Declare
Cursor cur1( cp_deptNo Number)
is
select ename,empno,sal from emp
where sal<50000 and deptno=cp_deptNo
l_deptNo Number :=50;
begin
For rec in cur1(l_deptNo)
loop
dbms_output.put_line(‘Employee Number ‘||rec.empno);
end loop;
end;
II. DYNAMIC CURSOR s :
Oracle REF CURSOR Types:
With the REF_CURSOR you can return a recordset/cursor from a stored procedure
(i.e Ref Cursors can have Record/s as return types.)
Could be declared once and defined many times in different procedures.
a)Strong : For the strong ref cursor the returning columns with data type and length need to be known at compile time.
b)Weak :For the weak ref cursor the structure does not need to be known at compile time.
Example For the Ref Cursor :
–SPECK PACKAGE
CREATE OR REPLACE PACKAGE REFCURSOR_PKG
AS
TYPE WEAK_REF_CURSOR IS REF CURSOR; — Until 9i
TYPE STRONG_REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;
END REFCURSOR_PKG;
The pl/sql procedure that returns a ref-cursor looks like this:
–BODY PACKAGE
CREATE OR REPLACE PACKAGE BODY REFCURSOR_PKG
AS
— For Weak Ref Cursor:
PROCEDURE
WEAK_REF_CUR_PRC( p_deptno IN number,
p_cursor OUT REFCURSOR_PKG.WEAK_REF_CURSOR — Until 9i
—- From 9i (p_cursor OUT SYS_REFCURSOR )—-
)
IS
BEGIN
OPEN p_cursor FOR
SELECT * FROM emp
WHERE deptno = p_deptno;
end WEAK_REF_CUR_PRC;
— For Strong Ref Cursor:
PROCEDURE
STRONG_REF_CUR_PRC( p_deptno IN number,
p_cursor OUT REFCURSOR_PKG.STRONG_REF_CURSOR
)
IS
BEGIN
SELECT * FROM emp
WHERE deptno = p_deptno;
end STRONG_REF_CUR_PRC;
END REFCURSOR_PKG;
Latest Posts
- R12 – How to Handle NULL for :$FLEX$.VALUE_SET_NAME In Oracle ERPAugust 25, 2023 - 1:20 pm
- R12 – How to Delete Oracle AR TransactionsMarch 22, 2019 - 8:37 pm
- How to Define Custom Key Flexfield (KFF) in R12January 19, 2018 - 5:43 pm
- AutoLock Box Concepts In R12November 10, 2017 - 8:30 am
- R12 – java.sql.SQLException: Invalid column type in OAFSeptember 15, 2017 - 9:39 am
Recent Comments