1 Package pqh_fr_stat_sit_util AS
2 /* $Header: pqstsutl.pkh 120.0 2005/05/29 02:44 appldev noship $ */
3
4
5 /* Following three functions will be used in FR PS Predefined Statutory Situations
6 Get_txn_catg_attr_meaning : Returns the valueset meaning for a given Situation Id.
7 Get_txn_value_query : Returns gets the value set query for a given transaction category attribute id
8 Rules_exist : Checks whether rules exist for a given situation or not
9 */
10 FUNCTION Get_txn_catg_attr_meaning(p_stat_situation_rule_id NUMBER, p_value_for varchar2 DEFAULT 'FROM') return VARCHAR2;
11 FUNCTION Get_txn_value_query (p_txn_category_attribute_id NUMBER) return VARCHAR2;
12 FUNCTION Rules_exist(p_stat_situation_id NUMBER) return VARCHAR2;
13 FUNCTION Is_input_is_valid(p_txn_category_attribute_id NUMBER, p_from_value varchar2 ) return varchar2;
14 /* following functions are added for transaction attributes processing */
15 Function get_los_in_ps ( p_person_id IN NUMBER default NULL,
16 p_determination_date IN DATE default NULL)
17 return number;
18 Function get_general_los (p_person_id IN NUMBER default NULL,
19 p_determination_date IN DATE default NULL)
20 return number;
21 Function get_employee_type (p_person_id IN per_all_people_f.person_id%TYPE,
22 p_determination_date IN DATE) return varchar2;
23 Function get_situation_type (p_person_id IN per_all_people_f.person_id%TYPE,
24 p_determination_date IN DATE) return varchar2;
25 Function get_relationship_type (p_person_id IN per_all_people_f.person_id%TYPE,
26 p_determination_date IN DATE) return varchar2;
27 Function get_dependent_age (p_person_id IN per_all_people_f.person_id%TYPE,
28 p_determination_date IN DATE) return number;
29 --
30 FUNCTION is_situation_renewable(p_emp_stat_situation_id NUMBER,
31 p_statutory_situation_id NUMBER) RETURN VARCHAR2;
32 --
33 FUNCTION get_number_of_renewals(p_emp_stat_situation_id NUMBER) RETURN NUMBER;
34 --
35 --deenath - New function to get number of renewals created since in Update Renewal Situation,
36 --we dont want to count the situation being updated as a renewal
37 FUNCTION get_num_renewals(p_emp_stat_situation_id IN NUMBER,
38 p_renew_stat_situation_id IN NUMBER) RETURN NUMBER;
39 --
40 Function is_situation_valid(p_person_id NUMBER,
41 p_emp_stat_situation_id NUMBER,
42 p_statutory_situation_id NUMBER) RETURN VARCHAR2 ;
43 FUNCTION chk_rule_condition(p_emp_stat_situation_id IN NUMBER,
44 p_statutory_situation_id IN NUMBER,
45 p_txn_category_attribute_id IN NUMBER,
46 p_from_value IN VARCHAR2,
47 p_to_value IN VARCHAR2,
48 p_negate IN VARCHAR2) RETURN BOOLEAN;
49 Function is_current_situation(p_emp_stat_situation_id NUMBER) RETURN varchar2;
50 Function get_dflt_situation(p_business_group_id IN NUMBER,
51 p_situation_type IN VARCHAR2,
52 p_sub_type IN VARCHAR2 DEFAULT NULL,
53 p_effective_date IN DATE DEFAULT SYSDATE) RETURN NUMBER ;
54 Function get_time_line(p_provisional_start_date IN DATE, p_provisional_end_date IN DATE,
55 p_effective_date IN DATE) RETURN VARCHAR2;
56 -- Function get_time_line_code(p_provisional_start_date IN DATE, p_provisional_end_date IN DATE,
57 -- p_effective_date IN DATE) RETURN VARCHAR2;
58 FUNCTION get_time_line_code(p_provisional_start_date IN DATE,
59 p_actual_end_date IN DATE,
60 p_provisional_end_date IN DATE,
61 p_effective_date IN DATE) RETURN VARCHAR2;
62 FUNCTION get_update_time_line_code(p_provisional_start_date IN DATE,
63 p_provisional_end_date IN DATE,
64 p_effective_date IN DATE,
65 p_approval_flag IN VARCHAR2,
66 p_renew_flag IN VARCHAR2,
67 p_situation_type IN VARCHAR2,
68 p_sub_type IN VARCHAR2,
69 p_default_flag IN VARCHAR2) RETURN VARCHAR2;
70 FUNCTION get_delete_time_line_code(p_person_id IN NUMBER,
71 p_provisional_start_date IN DATE,
72 p_provisional_end_date IN DATE,
73 p_effective_date IN DATE) RETURN VARCHAR2;
74
75 End pqh_fr_stat_sit_util;