[Home] [Help]
PACKAGE BODY: APPS.PER_IN_ASG_LEG_HOOK
Source
1 PACKAGE BODY per_in_asg_leg_hook AS
2 /* $Header: peinlhas.pkb 120.6 2012/01/19 09:13:48 rpahune ship $ */
3
4 g_package CONSTANT VARCHAR2(100) :='per_in_asg_leg_hook.';
5 g_debug BOOLEAN;
6 g_procedure_name VARCHAR2(100);
7
8 --------------------------------------------------------------------------
9 -- --
10 -- Name : CHECK_ASG --
11 -- Type : Procedure --
12 -- Access : Private --
13 -- Description : Procedure is the driver procedure for the validation--
14 -- of the assignment. --
15 -- This procedure is the internal procedure to be used --
16 -- as common point to code for INS and UPD --
17 -- Parameters : --
18 -- IN : p_dt_mode IN VARCHAR2 --
19 -- p_pt_org_id IN VARCHAR2 --
20 -- p_effective_date IN DATE --
21 -- p_assignment_id IN NUMBER DEFAULT null --
22 -- --
23 -- Change History : --
24 --------------------------------------------------------------------------
25 -- Rev# Date Userid Description --
26 --------------------------------------------------------------------------
27 -- 1.0 06-Sep-04 statkar 3768210 - created --
28 -- 1.1 04-Dec-04 statkar 4029400 - added 2 new procs for ESI/PF --
29 -- 1.2 18-Jan-05 rpalli 4114216 - Corrected code for support for --
30 -- ESI/PF --
31 -- 1.3 02-Feb-05 rpalli 4158566 - Modified message_name to --
32 -- 'SUCCESS' --
33 -- 1.4 10-Apr-05 abhjain 4270513 - Removed call to check_pt_update --
34 -- and check_esi_update --
35 -- 1.5 02-Sep-05 aaagarwa 4589599 - Added p_gre parameter. --
36 -- 1.6 13-Jul-11 prasrang 12711602- Added check_number function --
37 -- and handled Change Manager --
38 -- SSHR functionality. --
39 --------------------------------------------------------------------------
40 PROCEDURE check_asg (p_dt_mode IN VARCHAR2
41 ,p_effective_date IN DATE
42 ,p_assignment_id IN NUMBER
43 ,p_gre_id IN VARCHAR2
44 ,p_pf_org_id IN VARCHAR2
45 ,p_pt_org_id IN VARCHAR2
46 ,p_esi_org_id IN VARCHAR2
47 ,p_factory_id IN VARCHAR2
48 ,p_estab_id IN VARCHAR2
49 ,p_pga_flag IN VARCHAR2
50 ,p_subint_flag IN VARCHAR2
51 ,p_director IN VARCHAR2
52 ,p_specified IN VARCHAR2
53 )
54 IS
55
56 CURSOR c_org_name (p_org_id IN NUMBER) IS
57 SELECT name
58 FROM hr_all_organization_units_tl
59 WHERE organization_id = p_org_id
60 AND language = userenv('LANG');
61
62 CURSOR c_previous_gre IS
63 SELECT scl.segment1,scl.segment2,scl.segment4
64 FROM per_all_assignments_f asg
65 ,hr_soft_coding_keyflex scl
66 ,fnd_sessions ses
67 WHERE asg.assignment_id = p_assignment_id
68 AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
69 AND ses.session_id = userenv('SESSIONID')
70 AND ses.effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
71
72 l_pf_org hr_all_organization_units_tl.name%TYPE;
73 l_gre_org hr_all_organization_units_tl.name%TYPE;
74 l_esi_org hr_all_organization_units_tl.name%TYPE;
75 l_gre_id hr_all_organization_units_tl.name%TYPE;
76 l_pf_id hr_all_organization_units_tl.name%TYPE;
77 l_esi_id hr_all_organization_units_tl.name%TYPE;
78 l_message VARCHAR2(255);
79 l_procedure VARCHAR2(100);
80 g_gre_id NUMBER;
81 g_pf_id NUMBER;
82 g_esi_id NUMBER;
83
84 -- Bug 12711602: Handled ORA-06502 in Change Manager of INDIA HR MANAGER SELF SERVICE
85 FUNCTION check_number(p_param VARCHAR2)
86 RETURN BOOLEAN IS
87 v_temp NUMBER;
88 BEGIN
89 v_temp := TO_NUMBER(p_param);
90 RETURN TRUE;
91 EXCEPTION
92 WHEN value_error THEN
93 RETURN FALSE;
94 END check_number;
95
96 BEGIN
97
98 g_debug := hr_utility.debug_enabled ;
99 l_procedure := g_package ||'check_asg';
100 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
101
102 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'IN') THEN
103 hr_utility.trace ('IN Legislation not installed. Not performing the validations');
104 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
105 RETURN;
106 END IF;
107
108 pay_in_utils.set_location(g_debug,l_procedure,30);
109 --
110 -- Code for PF Information
111 --
112 l_message := 'SUCCESS';
113
114 -- Bug 12711602: Handled ORA-06502 in Change Manager of INDIA HR MANAGER SELF SERVICE
115 IF check_number(p_gre_id) AND check_number(p_pf_org_id) AND check_number(p_esi_org_id) THEN
116 g_gre_id:=p_gre_id;
117 g_pf_id:=p_pf_org_id;
118 g_esi_id:=p_esi_org_id;
119 ELSIF NOT(check_number(p_gre_id) AND check_number(p_pf_org_id) AND check_number(p_esi_org_id)) THEN
120 OPEN c_previous_gre;
121 FETCH c_previous_gre INTO g_gre_id,g_pf_id,g_esi_id;
122 CLOSE c_previous_gre;
123 END IF;
124
125 OPEN c_org_name(g_gre_id);
126 FETCH c_org_name
127 INTO l_gre_org;
128 IF c_org_name%NOTFOUND THEN
129 pay_in_utils.set_location(g_debug,l_procedure,40);
130 l_gre_org := NULL;
131 END IF;
132 CLOSE c_org_name;
133
134 OPEN c_org_name(g_pf_id);
135 FETCH c_org_name
136 INTO l_pf_org;
137 IF c_org_name%NOTFOUND THEN
138 pay_in_utils.set_location(g_debug,l_procedure,40);
139 l_pf_org := NULL;
140 END IF;
141 CLOSE c_org_name;
142
143 OPEN c_org_name(g_esi_id);
144 FETCH c_org_name
145 INTO l_esi_org;
146 IF c_org_name%NOTFOUND THEN
147 pay_in_utils.set_location(g_debug,l_procedure,40);
148 l_esi_org := NULL;
149 END IF;
150 CLOSE c_org_name;
151
152 OPEN c_previous_gre;
153 FETCH c_previous_gre INTO g_gre_id,g_pf_id,g_esi_id;
154 CLOSE c_previous_gre;
155
156
157 l_gre_id := g_gre_id;
158 l_pf_id := g_pf_id;
159 l_esi_id := g_esi_id;
160
161 pay_in_utils.set_location(g_debug,l_procedure,50);
162
163 pay_in_ff_pkg.check_pf_update
164 (p_effective_date => p_effective_date
165 ,p_dt_mode => p_dt_mode
166 ,p_assignment_id => p_assignment_id
167 ,p_gre_org => l_gre_org
168 ,p_pf_org => l_pf_org
169 ,p_esi_org => l_esi_org
170 ,p_message => l_message
171 ,p_gre => l_gre_id
172 ,p_pf => l_pf_id
173 ,p_esi => l_esi_id
174 );
175
176 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,60);
177
178 /*EXCEPTION
179 WHEN OTHERS THEN
180 IF c_org_name%ISOPEN THEN close c_org_name; END IF;
181 hr_utility.set_message(800, 'PER_IN_ORACLE_GENERIC_ERROR');
182 hr_utility.set_message_token('FUNCTION',l_procedure);
183 hr_utility.set_message_token('SQLERRMC',sqlerrm);
184 hr_utility.raise_error;*/
185 END check_asg;
186
187
188 --------------------------------------------------------------------------
189 -- --
190 -- Name : CHECK_ASG_UPDATE --
191 -- Type : Procedure --
192 -- Access : Public --
193 -- Description : Procedure is the driver procedure for the validation--
194 -- of the assignment. --
195 -- This procedure is the hook procedure for update --
196 -- employee assignment. --
197 -- Parameters : --
198 -- IN : p_datetrack_update_mode VARCHAR2 --
199 -- p_effective_date DATE --
200 -- p_assignment_id NUMBER --
201 -- p_segment1 VARCHAR2 --
202 -- p_segment2 VARCHAR2 --
203 -- p_segment3 VARCHAR2 --
204 -- p_segment4 VARCHAR2 --
205 -- p_segment5 VARCHAR2 --
206 -- p_segment6 VARCHAR2 --
207 -- p_segment8 VARCHAR2 --
208 -- p_segment9 VARCHAR2 --
209 -- p_segment10 VARCHAR2 --
210 -- p_segment11 VARCHAR2 --
211 -- --
212 -- Change History : --
213 --------------------------------------------------------------------------
214 -- Rev# Date Userid Description --
215 --------------------------------------------------------------------------
216 -- 1.0 06-Sep-04 statkar 3768210 - created --
217 -- 1.1 04-Dec-04 statkar 4029400 - added PF/ESI support --
218 -- 1.2 09-Feb-05 rpalli 4158566 - Commented out call for --
219 -- validations for assignment api --
220 --------------------------------------------------------------------------
221 PROCEDURE check_asg_update (p_datetrack_update_mode IN VARCHAR2
222 ,p_effective_date IN DATE
223 ,p_assignment_id IN NUMBER
224 ,p_segment1 IN VARCHAR2 -- tax unit
225 ,p_segment2 IN VARCHAR2 -- pf_org
226 ,p_segment3 IN VARCHAR2 -- pt_org
227 ,p_segment4 IN VARCHAR2 -- esi_org
228 ,p_segment5 IN VARCHAR2 -- factory
229 ,p_segment6 IN VARCHAR2 -- estb
230 ,p_segment8 IN VARCHAR2 -- PGA flag
231 ,p_segment9 IN VARCHAR2 -- Sub Interest
232 ,p_segment10 IN VARCHAR2 -- Director
233 ,p_segment11 IN VARCHAR2 -- Specified
234 )
235 IS
236 l_procedure VARCHAR2(100);
237 BEGIN
238 g_debug := hr_utility.debug_enabled;
239 l_procedure := g_package ||'check_asg_update';
240 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
241 --
242
243 check_asg
244 (p_dt_mode => p_datetrack_update_mode
245 ,p_effective_date => p_effective_date
246 ,p_assignment_id => p_assignment_id
247 ,p_gre_id => p_segment1
248 ,p_pf_org_id => p_segment2
249 ,p_pt_org_id => p_segment3
250 ,p_esi_org_id => p_segment4
251 ,p_factory_id => p_segment5
252 ,p_estab_id => p_segment6
253 ,p_pga_flag => p_segment8
254 ,p_subint_flag => p_segment9
255 ,p_director => p_segment10
256 ,p_specified => p_segment11
257 );
258
259 pay_in_utils.set_location(g_debug,'Leaving: '||g_procedure_name,20);
260
261 /*EXCEPTION
262 WHEN OTHERS THEN
263 hr_utility.set_message(800, 'PER_IN_ORACLE_GENERIC_ERROR');
264 hr_utility.set_message_token('FUNCTION',l_procedure);
265 hr_utility.set_message_token('SQLERRMC',sqlerrm);
266 hr_utility.raise_error; */
267
268 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
269 END check_asg_update;
270
271
272 END per_in_asg_leg_hook;