1 PACKAGE BODY pay_cn_rules AS
2 /* $Header: pycnrule.pkb 120.1 2006/10/03 07:49:54 rpalli noship $ */
3
4 g_debug BOOLEAN ;
5 g_package_name VARCHAR2(13);
6 --------------------------------------------------------------------------
7 -- --
8 -- Name : GET_DEFAULT_JURISDICTION --
9 -- Type : PROCEDURE --
10 -- Access : Public --
11 -- Description : Procedure to get the default jurisdcition --
12 -- for China tax processing. --
13 -- --
14 -- Parameters : --
15 -- IN : p_asg_act_id NUMBER --
16 -- p_ee_id NUMBER --
17 -- IN/OUT : p_jurisdiction VARCHAR2 --
18 -- --
19 -- Change History : --
20 --------------------------------------------------------------------------
21 -- Rev# Date Userid Description --
22 --------------------------------------------------------------------------
23 -- 1.0 28-FEB-03 vinaraya Created this Procedure --
24 -- 1.1 19-Mar-03 vinaraya Changed the indentation as per review --
25 -- comments. --
26 -- 1.2 13-May-03 statkar Included code for PHF/SI Processing --
27 --------------------------------------------------------------------------
28 PROCEDURE get_default_jurisdiction( p_asg_act_id NUMBER
29 , p_ee_id NUMBER
30 , p_jurisdiction IN OUT NOCOPY VARCHAR2
31 )
32 IS
33 BEGIN
34 NULL;
35
36 END get_default_jurisdiction;
37
38 --------------------------------------------------------------------------
39 -- --
40 -- Name : GET_RETRO_COMPONENT_ID --
41 -- Type : PROCEDURE --
42 -- Access : Public --
43 -- Description : Procedure to get the default retro component id for --
44 -- a particular element entry from Org DDF --
45 -- --
46 -- Parameters : --
47 -- IN : p_ee_id NUMBER --
48 -- : p_element_type_id NUMBER --
49 -- IN/OUT : p_retro_component_id NUMBER --
50 -- --
51 -- Change History : --
52 --------------------------------------------------------------------------
53 -- Rev# Date Userid Description --
54 --------------------------------------------------------------------------
58 -- handled for changes in PHFSI Info elements--
55 -- 1.0 01-Apr-04 statkar Created this Procedure --
56 -- 2.0 14-Sep-04 statkar Changed the signature --
57 -- 3.0 29-Sep-06 rpalli Modified code to support Retropay to be --
59 --------------------------------------------------------------------------
60 PROCEDURE get_retro_component_id ( p_ee_id IN NUMBER
61 , p_element_type_id IN NUMBER
62 , p_retro_component_id IN OUT NOCOPY NUMBER
63 )
64 IS
65
66 l_procedure_name VARCHAR2(255) ;
67
68 CURSOR csr_et(p_effective_date IN DATE) IS
69 SELECT pet.element_name, pee.creator_type
70 FROM pay_element_entries_f pee
71 ,pay_element_links_f pel
72 ,pay_element_types_f pet
73 WHERE pee.element_link_id = pel.element_link_id
74 AND pel.element_type_id = pet.element_type_id
75 AND pee.element_entry_id = p_ee_id
76 AND p_effective_date BETWEEN pel.effective_start_date
77 AND pel.effective_end_date
78 AND p_effective_date BETWEEN pet.effective_start_date
79 AND pet.effective_end_date
80 ORDER BY pee.effective_start_date desc;
81
82 CURSOR csr_ename (p_effective_date IN DATE) IS
83 SELECT element_name, 'F'
84 FROM pay_element_types_f
85 WHERE element_type_id = p_element_type_id
86 AND p_effective_date BETWEEN effective_start_date
87 AND effective_end_date;
88
89 CURSOR csr_tax_area (p_effective_date IN DATE) IS
90 SELECT hsck.segment20, pa.business_group_id
91 FROM pay_element_entries_f pee
92 ,per_assignments_f pa
93 ,hr_soft_coding_keyflex hsck
94 WHERE pee.assignment_id = pa.assignment_id
95 AND pa.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
96 AND pee.element_entry_id = p_ee_id
97 AND p_effective_date BETWEEN pa.effective_start_date
98 AND pa.effective_end_date
99 ORDER BY pee.effective_start_date desc;
100
101 CURSOR csr_rc_id (p_bg_id IN NUMBER, p_tax_area IN VARCHAR2, p_effective_date IN DATE)
102 IS
103 SELECT org_information2
104 FROM hr_organization_information
105 WHERE organization_id = p_bg_id
106 AND org_information_context = 'PER_CONT_AREA_RETRO_USAGE_CN'
107 AND org_information1 = p_tax_area
108 AND p_effective_date BETWEEN TO_DATE(org_information3,'YYYY/MM/DD HH24:MI:SS')
109 AND TO_DATE(NVL(org_information4,'4712/12/31 00:00:00'),'YYYY/MM/DD HH24:MI:SS');
110
111 l_retro_component_id pay_retro_components.retro_component_id%TYPE;
112 l_element_name pay_element_types_f.element_name%TYPE;
113 l_tax_area hr_soft_coding_keyflex.segment20%TYPE;
114 l_bg_id per_all_assignments_f.business_group_id%TYPE;
115 l_effective_date DATE;
116 l_creator_type pay_element_entries_f.creator_type%TYPE;
117
118 BEGIN
119 l_procedure_name := g_package_name ||'get_retro_component_id';
120 g_debug := hr_utility.debug_enabled;
121 hr_cn_api.set_location (g_debug,'Entering : '||l_procedure_name,10);
122
123 l_retro_component_id := p_retro_component_id;
124 l_effective_date := hr_general.effective_date;
125
126 IF g_debug THEN
127 hr_utility.trace ('====================================================');
128 hr_utility.trace (' Element Entry ID : '||p_ee_id);
129 hr_utility.trace ('Old Retro Component ID : '||p_retro_component_id);
130 hr_utility.trace (' Effective Date : '||TO_CHAR(l_effective_date, 'DD/MM/YYYY'));
131 END IF;
132
133 OPEN csr_et(l_effective_date);
134 FETCH csr_et INTO l_element_name, l_creator_type;
135 IF csr_et%NOTFOUND THEN
136 CLOSE csr_et;
137 OPEN csr_ename(l_effective_date);
138 FETCH csr_ename INTO l_element_name, l_creator_type;
139 IF csr_ename%NOTFOUND THEN
140 hr_cn_api.set_location (g_debug,'Leaving : '||l_procedure_name, 20);
141 CLOSE csr_ename;
142 RETURN;
143 ELSE
144 CLOSE csr_ename;
145 END IF;
146 ELSE
147 CLOSE csr_et;
148 END IF;
149
150 IF g_debug THEN
151 hr_utility.trace (' Element Name : '||l_element_name);
152 hr_utility.trace (' Creator Type : '||l_creator_type);
153 hr_utility.trace ('====================================================');
154 END IF;
155
156 IF l_element_name NOT IN
157 ('Taxation Information') -- Bug 5484589
158 AND l_creator_type NOT IN ('EE', 'PR', 'R', 'RR','NR') -- Bug 3619384
159 THEN
160 OPEN csr_tax_area(l_effective_date);
161 FETCH csr_tax_area INTO l_tax_area, l_bg_id;
162 CLOSE csr_tax_area;
163 IF g_debug THEN
164 hr_utility.trace (' Tax Area : '||l_tax_area);
165 hr_utility.trace (' Business Group ID : '||l_bg_id);
166 END IF;
167
168 BEGIN
169 OPEN csr_rc_id (l_bg_id, l_tax_area, l_effective_date);
170 FETCH csr_rc_id INTO l_retro_component_id;
171 CLOSE csr_rc_id;
172
173 IF g_debug THEN
174 hr_utility.trace ('New Retro Component ID : '||p_retro_component_id);
175 END IF;
176
180 CLOSE csr_rc_id;
177 EXCEPTION
178 WHEN NO_DATA_FOUND THEN
179 IF csr_rc_id%ISOPEN THEN
181 END IF;
182 p_retro_component_id := -1;
183 RAISE;
184 END;
185
186 END IF;
187 p_retro_component_id := l_retro_component_id;
188 hr_cn_api.set_location (g_debug,'Leaving : '||l_procedure_name,30);
189
190 EXCEPTION
191 WHEN OTHERS THEN
192 IF csr_et%ISOPEN THEN
193 close csr_et;
194 END IF;
195 IF csr_tax_area%ISOPEN THEN
196 close csr_tax_area;
197 END IF;
198 IF csr_rc_id%isopen THEN
199 close csr_rc_id;
200 END IF;
201 IF csr_ename%ISOPEN THEN
202 close csr_ename;
203 END IF;
204 p_retro_component_id := -1;
205 hr_utility.trace(hr_cn_api.get_pay_message('HR_374610_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure_name, 'SQLERRMC:'||sqlerrm));
206 hr_cn_api.set_location (g_debug,'Leaving : '||l_procedure_name,40);
207 RAISE;
208
209 END get_retro_component_id;
210
211 --------------------------------------------------------------------------
212 -- --
213 -- Name : ELEMENT_TEMPLATE_POST_PROCESS --
214 -- Type : PROCEDURE --
215 -- Access : Public --
216 -- Description : Accepts the Template Name as input and return the --
217 -- Results Setup --
218 -- --
219 -- Parameters : None --
220 -- IN : p_template_id NUMBER --
221 -- --
222 -- Change History : --
223 --------------------------------------------------------------------------
224 -- Rev# Date Userid Description --
225 --------------------------------------------------------------------------
226 -- 1.0 17-Apr-05 snekkala Created this Procedure --
227 --------------------------------------------------------------------------
228 PROCEDURE element_template_post_process
229 (p_template_id IN NUMBER)
230 IS
231 BEGIN
232
233 pay_cn_element_template_pkg.element_template_post_process(p_template_id);
234
235 END element_template_post_process;
236
237 BEGIN
238
239 g_package_name := 'pay_cn_rules.';
240
241 END pay_cn_rules;
[Home] [Help]