September 19, 2010

Call DFF segments e.g SIT in hrms

How to create a package to call the SIT which will store the flexvalues in different segments.

e,g Code:

1.Note down the flexstrcture name and segment.
2.Call the flex value in the Function.
3.pass the flex value in query


CREATE OR REPLACE function xxri_passport_num(p_id_flex_num in number,p_person_id in number) return varchar2

is
l_passport varchar2(240);
begin
select pac.segment1 into l_passport from per_person_analyses ppa,per_analysis_criteria pac
where ppa.ANALYSIS_CRITERIA_ID=pac.ANALYSIS_CRITERIA_ID and ppa.id_flex_num =p_id_flex_num and ppa.person_id=p_person_id;
return l_passport;
end xxri_passport_num;
/

select xxri_passport_num(50348,61) passport from dual;

COALESCE-SQL

Coalesce- return the null values from the expression. It works similar to a case statement where if expression 1 is false then goes to expr...