[Home] [Help]
PACKAGE BODY: APPS.HR_NL_CALC_TARGET_GROUP
Source
1 PACKAGE BODY HR_NL_CALC_TARGET_GROUP AS
2 /* $Header: pernlctg.pkb 115.3 2002/06/12 02:40:49 pkm ship $ */
3
4 --
5 FUNCTION get_country_code (p_person_id per_all_people_f.person_id%type,
6 p_contact_type per_contact_relationships.contact_type%type,
7 p_session_date date)
8 return VARCHAR2 is
9
10 l_cnty_of_birth per_all_people_f.country_of_birth%type;
11 l_data_flag varchar2(1);
12
13 begin
14 l_data_flag := 'Y';
15 begin
16 select a.country_of_birth
17 into l_cnty_of_birth
18 from per_all_people_f a,
19 per_contact_relationships b
20 where a.person_id = b.contact_person_id and
21 b.person_id = p_person_id and
22 b.contact_type = p_contact_type and
23 p_session_date between a.effective_start_date and a.effective_end_date and
24 p_session_date between nvl(b.date_start, p_session_date) and
25 nvl(b.date_end, p_session_date) and
26 ROWNUM = 1
27 order by b.date_start desc;
28 exception
29 when no_data_found then
30 l_cnty_of_birth := null;
31 l_data_flag := 'N';
32 end;
33
34 if l_data_flag = 'N' then
35 begin
36 select a.country_of_birth
37 into l_cnty_of_birth
38 from per_all_people_f a,
39 per_contact_relationships b
40 where a.person_id = b.contact_person_id and
41 b.person_id = p_person_id and
42 b.contact_type = p_contact_type and
43 p_session_date between nvl(b.date_start, p_session_date) and
44 nvl(b.date_end, p_session_date) and
45 ROWNUM = 1
46 order by b.date_start desc, a.effective_start_date desc;
47 exception
48 when no_data_found then
49 l_cnty_of_birth := null;
50 end;
51 end if;
52
53 return l_cnty_of_birth ;
54
55 end;
56 --
57 FUNCTION run_formula (p_country_of_birth_fth per_all_people_f.country_of_birth%type,
58 p_country_of_birth_mth per_all_people_f.country_of_birth%type,
59 p_country_of_birth_emp per_all_people_f.country_of_birth%type,
60 p_business_group_id per_all_people_f.business_group_id%type,
61 p_session_date date)
62 return VARCHAR2 is
63
64 l_formula_id ff_formulas_f_v.formula_id%type;
65 l_effective_start_date ff_formulas_f_v.effective_start_date%type;
66 l_target_group varchar2(10);
67
68 begin
69 begin
70 select formula_id ,effective_start_date
71 into l_formula_id,l_effective_start_date
72 from ff_formulas_f_v
73 where formula_name = 'NL_TARGET_GROUP'
74 and p_session_date between effective_start_date and
75 effective_end_date
76 and verified = 'Y';
77 exception
78 when no_data_found then
79 hr_utility.set_message(800,'HR_NL_INVALID_FORMULA');
80 hr_utility.raise_error;
81 end;
82 --insert into fnd_sessions values (userenv('sessionid'),p_session_date);
83 ff_client_engine.init_formula(l_formula_id,l_effective_start_date);
84 ff_client_engine.set_input('BIRTH_COUNTRY_EMP', NVL(p_country_of_birth_emp,' '));
85 ff_client_engine.set_input('BIRTH_COUNTRY_FATHER',p_country_of_birth_fth);
86 ff_client_engine.set_input('BIRTH_COUNTRY_MOTHER',p_country_of_birth_mth);
87 ff_client_engine.set_input('BUSINESS_GROUP_ID',p_business_group_id);
88 ff_client_engine.run_formula;
89 ff_client_engine.get_output('RETURN_VALUE',l_target_group);
90
91 return l_target_group;
92 end;
93 --
94 FUNCTION get_target_group (p_person_id per_all_people_f.person_id%type,
95 p_session_date date) return VARCHAR2 is
96
97 l_country_of_birth_fth per_all_people_f.country_of_birth%type;
98 l_country_of_birth_mth per_all_people_f.country_of_birth%type;
99 l_country_of_birth_emp per_all_people_f.country_of_birth%type;
100 l_business_group_id per_all_people_f.business_group_id%type;
101 l_formula_id ff_formulas_f.formula_id%type;
102 l_effective_start_date ff_formulas_f.effective_start_date%type;
103 l_target_group varchar2(10);
104 l_label_text hr_lookups.meaning%type;
105 l_rec_status varchar2(20);
106 l_return_target_group varchar2(1);
107 l_local_birth_country_fth per_all_people_f.per_information11%type;
108 l_local_birth_country_mth per_all_people_f.per_information12%type;
109
110 begin
111 begin
112 select per_information11, per_information12
113 into l_local_birth_country_fth, l_local_birth_country_mth
114 from per_all_people_f
115 where person_id = p_person_id
116 and p_session_date between effective_start_date
117 and effective_end_date;
118 exception
119 when no_data_found then
120 l_local_birth_country_fth:='';
121 l_local_birth_country_mth:='';
122 end;
123
124 l_country_of_birth_fth:= get_country_code(p_person_id,'JP_FT',p_session_date);
125
126 l_country_of_birth_mth:= get_country_code(p_person_id,'JP_MT',p_session_date);
127 if l_country_of_birth_fth is null then
128 if l_local_birth_country_fth is not null then
129 l_country_of_birth_fth := l_local_birth_country_fth;
130 else
131 l_country_of_birth_fth := ' ';
132 end if;
133 end if;
134 if l_country_of_birth_mth is null then
135 if l_local_birth_country_mth is not null then
136 l_country_of_birth_mth := l_local_birth_country_mth;
137 else
138 l_country_of_birth_mth := ' ';
139 end if;
140 end if;
141 begin
142 select country_of_birth,
143 business_group_id
144 into l_country_of_birth_emp,
145 l_business_group_id
146 from per_all_people_f
147 where person_id = p_person_id and
148 p_session_date between effective_start_date and effective_end_date;
149 exception
150 when no_data_found then
151 l_country_of_birth_emp := null;
152 l_business_group_id := null;
153 end;
154
155 if l_country_of_birth_emp is null then
156 l_country_of_birth_emp := ' ';
157 end if;
158
159 if (l_country_of_birth_emp = ' ' and
160 l_country_of_birth_fth = ' ' and
161 l_country_of_birth_mth = ' ') then
162
163 l_return_target_group := 'N';
164
165 else
166
167 l_target_group := run_formula(l_country_of_birth_fth,
168 l_country_of_birth_mth,
169 l_country_of_birth_emp,
170 l_business_group_id,
171 p_session_date
172 );
173
174 if l_target_group = '1' then
175 l_return_target_group := 'Y';
176 else
177 l_return_target_group := 'N';
178 end if;
179
180 end if;
181
182 return l_return_target_group;
183
184 end;
185
186 END HR_NL_CALC_TARGET_GROUP;