Oracle SSHR How to add validations on SIT (Special Information Type) – Fnd_flex_plsql

Posted by & filed under .

Validation on HR Special Info (SIT) in SSHR Pages:

Requirement: Our client has requirement, to apply validations on one of  SSHR request which was based on SIT. There is a Loan Request Form in SSHR which need to validate based on certain criteria while user filling form.

There is option available thru User hooks, which doesn’t work on SIT SSHR Pages for validations but works once the final approval is done.
To overcome above issue we will implement logic in FND_FLEX_PLSQL package.

 

 

Requirement: Validate Key Flexfield Combinations Using Fnd_flex_plsql

Filed under: Foundation — Tags: ebusiness, fnd_flex_plsql, fnd_flex_server, key flexfields, Oracle Applications, validations for key flexfields .
There are about 39 seeded Key flexfields (KFF) in 12.0.4 vision instance. For each of these KFFs we can create countless structures (depending the KFF) under respective applications. The beauty of these flexfields is that we can create a combination of values from a number of sources and yet store it in a single field. We have a number of features around flexfileds like using value sets that are vitually source values from any where in apps, security rules that can impose security of usage of values by a specific person or responsibility, cross validation rules that will not let you create undesired combination of values, if the combination is not created yet and more.

But no matter what Oracle gives as standard feature, we still need more. This question from an unappreciative user of forums resulted in a good find. If you have any need to write your own logic to validate creation of a combination, Oracle gives you a pre-hook for all key flexfields.You can write all your validations that you need (apart from using above mentioned features) before eBusiness suite creates one. If your validations fail, you can stop the creation of that combination.

The package name is fnd_flex_plsql. It comes with no logic in the body, but just returns boolean value of Ture. Specifically as the gl_code_combinations table does not have standard “WHO” columns, this might come handy to solve that issue.

 

You can check for the custom code by using the following SQLs

1) select text from all_source where name like ‘FND_FLEX_PLSQL’
2) select owner, object_name, object_type, status from dba_objects where
object_name = ‘FND_FLEX_PLSQL’;

 

 

 

Sample Code: 

CREATE OR REPLACE PACKAGE BODY APPS.”FND_FLEX_PLSQL” AS
/* $Header: AFFFPLVB.pls 115.0 99/07/16 23:18:46 porting ship $ */

FUNCTION validate(application_id IN NUMBER,
id_flex_code IN VARCHAR2,
id_flex_num IN NUMBER,
vdate IN DATE,
segment_delimiter IN VARCHAR2,
concatenated_segments IN VARCHAR2,
numsegs IN NUMBER,
user_segment1 IN VARCHAR2,
user_segment2 IN VARCHAR2,
user_segment3 IN VARCHAR2,
user_segment4 IN VARCHAR2,
user_segment5 IN VARCHAR2,
user_segment6 IN VARCHAR2,
user_segment7 IN VARCHAR2,
user_segment8 IN VARCHAR2,
user_segment9 IN VARCHAR2,
user_segment10 IN VARCHAR2,
user_segment11 IN VARCHAR2,
user_segment12 IN VARCHAR2,
user_segment13 IN VARCHAR2,
user_segment14 IN VARCHAR2,
user_segment15 IN VARCHAR2,
user_segment16 IN VARCHAR2,
user_segment17 IN VARCHAR2,
user_segment18 IN VARCHAR2,
user_segment19 IN VARCHAR2,
user_segment20 IN VARCHAR2,
user_segment21 IN VARCHAR2,
user_segment22 IN VARCHAR2,
user_segment23 IN VARCHAR2,
user_segment24 IN VARCHAR2,
user_segment25 IN VARCHAR2,
user_segment26 IN VARCHAR2,
user_segment27 IN VARCHAR2,
user_segment28 IN VARCHAR2,
user_segment29 IN VARCHAR2,
user_segment30 IN VARCHAR2,
error_message OUT VARCHAR2)
return BOOLEAN IS

v_flex_name varchar2(100);
v_person_id number;
v_business_group_id number;
v_assignment_id number;
v_contribution_bal_id number;
v_loan_bal_id number;
v_loan_recovery_elem_id number;
BEGIN
——————–Custom For LOAN System—————————
v_person_id:=fnd_profile.VALUE(‘PER_PERSON_ID’);
v_business_group_id:=fnd_profile.value(‘PER_BUSINESS_GROUP_ID’);
v_assignment_id:=xxABC_LOAN_pkg.get_assignment_id(v_business_group_id,v_person_id);
v_flex_name:=xxABC_LOAN_pkg.get_id_flex_name(v_business_group_id,id_flex_num);
——————–Get Balances From setup————————-
select xps.contribution_bal_id,xps.loan_bal_id,xps.loan_recovery_elem_id
into v_contribution_bal_id,v_loan_bal_id,v_loan_recovery_elem_id
from xxABCLOAN_payroll_setup xps;

If v_flex_name=’Loan Proposal’
then
——–Check That Employee Don’t Have Balance———————
if xxABC_LOAN_pkg.get_employee_balance(v_assignment_id,last_day(to_date(sysdate,’dd-mm-rrrr’)),last_day(to_date(sysdate,’dd-mm-rrrr’)),v_contribution_bal_id)<=0
then
error_message:=’LOAN System:You Have To Join The Contribution First ‘;
return FALSE;
elsif xxABC_LOAN_pkg.get_loan_segment_value(v_business_group_id,v_person_id,’PERIOD_OF_SERVICE_MONTH’)<=3
then
error_message:=’LOAN System: Minimum Period To Request Loan Is 3 Months’;
return FALSE;

else
return TRUE;
end if;

else
return TRUE;

end if;

END validate;

END FND_FLEX_PLSQL;
/

Leave a Reply

Your email address will not be published. Required fields are marked *