[Home] [Help]
PACKAGE BODY: APPS.PAY_IP_ELE_INFO_CAT_UPG
Source
1 PACKAGE BODY PAY_IP_ELE_INFO_CAT_UPG AS
2 /* $Header: payipeleupg.pkb 120.0.12020000.1 2012/09/04 10:14:53 schowl noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 2004, Oracle India Pvt. Ltd., Hyderabad *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19 Package Name : PAY_IP_ELE_INFO_CAT_UPG
20 Package File Name : payipeleupg.pkb
21
22 Description : Used for updating the Element Information Category for
23 the existing Elements in PAY_ELEMENT_TYPES_F table.
24
25 Change List:
26 ------------
27
28 Name Date Version Bug Text
29 ------------- ----------- ------- ------- ------------------------------
30 vvijayku 15-Dec-2011 115.0 Created.
31
32 ***************************************************************************/
33
34 --
35 -- Global Variables
36 --
37 g_proc_name varchar2(240);
38 g_debug boolean;
39
40
41 /****************************************************************************
42 Name : HR_UTILITY_TRACE
43 Description : This procedure prints debug messages.
44 *****************************************************************************/
45 PROCEDURE HR_UTILITY_TRACE
46 (
47 P_TRC_DATA varchar2
48 ) AS
49 BEGIN
50 IF g_debug THEN
51 hr_utility.trace(p_trc_data);
52 END IF;
53 END HR_UTILITY_TRACE;
54
55
56 /****************************************************************************
57 Name : QUAL_PROC
58 Description : Qualifying procedure for generic upgrade process.
59 *****************************************************************************/
60 PROCEDURE QUAL_PROC
61 (
62 P_OBJECT_ID NUMBER,
63 P_QUAL OUT NOCOPY VARCHAR2
64 ) AS
65
66 CURSOR csr_qualified IS
67 SELECT 'Y'
68 FROM pay_element_types_f pet,
69 per_business_groups pbg,
70 hr_legislation_installations hli
71 WHERE pet.element_type_id = p_object_id
72 AND pbg.business_group_id = pet.business_group_id
73 AND pbg.legislation_code = hli.legislation_code;
74
75 CURSOR csr_ele_class_qualified IS
76 SELECT 'Y'
77 FROM pay_element_types_f pet,
78 pay_element_classifications pec
79 WHERE pet.element_type_id = p_object_id
80 AND pec.classification_id = pet.classification_id
81 AND pec.classification_name IN ('Earnings',
82 'Supplemental Earnings',
83 'Taxable Benefits',
84 'Voluntary Deductions',
85 'Tax Deductions',
86 'Direct Payment',
87 'Employer Charges',
88 'Involuntary Deductions',
89 'Pre-Tax Deductions');
90
91 CURSOR csr_ele_qualified IS
92 SELECT 'N'
93 FROM dual
94 WHERE EXISTS (SELECT pet.element_information_category
95 FROM pay_element_types_f pet
96 WHERE pet.element_type_id = p_object_id
97 AND pet.element_information_category IS NOT NULL);
98
99 l_proc_name varchar2(100);
100 p_leg_qual varchar2(5);
101 p_ele_class_qual varchar2(5);
102
103 BEGIN
104 l_proc_name := g_proc_name || 'QUAL_PROC';
105 hr_utility_trace ('Entering '||l_proc_name);
106
107 p_leg_qual := 'N';
108
109 OPEN csr_qualified;
110 FETCH csr_qualified INTO p_leg_qual;
111 CLOSE csr_qualified;
112
113 IF p_leg_qual = 'N' THEN
114
115 p_ele_class_qual := 'N';
116
117 OPEN csr_ele_class_qualified;
118 FETCH csr_ele_class_qualified INTO p_ele_class_qual;
119 CLOSE csr_ele_class_qualified;
120
121 IF p_ele_class_qual = 'Y' THEN
122
123 p_qual := 'Y';
124
125 OPEN csr_ele_qualified;
126 FETCH csr_ele_qualified INTO p_qual;
127 CLOSE csr_ele_qualified;
128 END IF;
129
130 END IF;
131
132 IF p_qual = 'Y' THEN
133 hr_utility_trace('Element ' || p_object_id || ' qualified.');
134 ELSE
135 hr_utility_trace('Element ' || p_object_id || ' did not qualify.');
136 END IF;
137
138 hr_utility_trace ('Leaving '||l_proc_name);
139 END QUAL_PROC;
140
141 /****************************************************************************
142 Name : UPG_PROC
143 Description : Upgrade procedure for generic upgrade process.
144 *****************************************************************************/
145 PROCEDURE UPG_PROC
146 (
147 P_OBJECT_ID NUMBER
148 ) AS
149
150 CURSOR csr_legislation_code IS
151 SELECT upper(pbg.legislation_code),
152 upper(pec.classification_name)
153 FROM pay_element_types_f pet,
154 pay_element_classifications pec,
155 per_business_groups pbg
156 WHERE pet.element_type_id = p_object_id
157 AND pbg.business_group_id = pet.business_group_id
158 AND pec.classification_id = pet.classification_id
159 AND rownum = 1;
160
161 CURSOR csr_ele_class IS
162 SELECT pet.object_version_number,
163 pet.effective_start_date
164 FROM pay_element_types_f pet
165 WHERE pet.element_type_id = p_object_id;
166
167 lv_legislation_code varchar2(10);
168 lv_classification_name varchar2(50);
169 ln_object_version_number number;
170 lv_information_category varchar2(240);
171 ld_eff_start_date date;
172 ld_out_start_date date;
173 ld_out_end_date date;
174 ln_comment_id number;
175 lb_proc_pri_warn boolean;
176 lb_ele_name_warn boolean;
177 lb_ele_name_chng_warn boolean;
178 ln_count number;
179
180 l_proc_name varchar2(100);
181
182 BEGIN
183 l_proc_name := g_proc_name || 'UPG_PROC';
184 hr_utility_trace ('Entering '||l_proc_name);
185
186 OPEN csr_legislation_code;
187 FETCH csr_legislation_code INTO lv_legislation_code, lv_classification_name;
188 CLOSE csr_legislation_code;
189
190 lv_information_category := lv_legislation_code||'_'||lv_classification_name;
191
192 ln_count := 0;
193 OPEN csr_ele_class;
194 LOOP
195 FETCH csr_ele_class INTO ln_object_version_number,
196 ld_eff_start_date;
197 EXIT WHEN csr_ele_class%NOTFOUND;
198
199 pay_element_types_api.update_element_type
200 (p_effective_date => ld_eff_start_date,
201 p_datetrack_update_mode => 'CORRECTION',
202 p_element_type_id => p_object_id,
203 p_object_version_number => ln_object_version_number,
204 p_element_information_category => lv_information_category,
205 p_effective_start_date => ld_out_start_date,
206 p_effective_end_date => ld_out_end_date,
207 p_comment_id => ln_comment_id,
208 p_processing_priority_warning => lb_proc_pri_warn,
209 p_element_name_warning => lb_ele_name_warn,
210 p_element_name_change_warning => lb_ele_name_chng_warn);
211
212 ln_count := ln_count + 1;
213 END LOOP;
214 hr_utility_trace(' Information Category upgraded for element '||p_object_id);
215 CLOSE csr_ele_class;
216 hr_utility_trace ('Leaving '||l_proc_name);
217 END UPG_PROC;
218
219 BEGIN
220 --hr_utility.trace_on(null, 'MX_IDC');
221 g_proc_name := 'PAY_IP_ELE_INFO_CAT_UPG.';
222 g_debug := hr_utility.debug_enabled;
223 END PAY_IP_ELE_INFO_CAT_UPG;