[Home] [Help]
PACKAGE BODY: APPS.PAY_SA_UPGRADE
Source
1 PACKAGE BODY pay_sa_upgrade AS
2 /* $Header: pysaupgr.pkb 115.2 2004/01/31 04:57:08 atrivedi noship $ */
3 --
4 --
5 -- Global variables.
6 --
7 g_package VARCHAR2(31) := 'pay_sa_upgrade.';
8 --
9 --
10 -- -------------------------------------------------------------------------------------------
11 -- Return the ID for a given context.
12 -- -------------------------------------------------------------------------------------------
13 --
14 FUNCTION get_context_id(p_context_name VARCHAR2) RETURN NUMBER IS
15 --
16 --
17 -- Return the ID for a given context.
18 --
19 CURSOR csr_context(p_context_name VARCHAR2) IS
20 SELECT context_id
21 FROM ff_contexts
22 WHERE context_name = p_context_name;
23 --
24 --
25 -- Local variables.
26 --
27 l_proc VARCHAR2(61) := g_package || 'get_context_id';
28 l_context_id NUMBER;
29 --
30 BEGIN
31 --
32 hr_utility.set_location('Entering: ' || l_proc, 10);
33 --
34 OPEN csr_context(p_context_name);
35 FETCH csr_context INTO l_context_id;
36 CLOSE csr_context;
37 --
38 hr_utility.set_location('Leaving: ' || l_proc, 20);
39 --
40 RETURN l_context_id;
41 --
42 END get_context_id;
43 --
44 --
45 -- -------------------------------------------------------------------------------------------
46 -- Upgrade the run results.
47 -- -------------------------------------------------------------------------------------------
48 --
49 PROCEDURE upgrade_run_results IS
50 --
51 --
52 -- Get all run results for a set of elements where there doesnt exist a run result
53 -- value for the Joiner input value.
54 --
55 CURSOR csr_results IS
56 SELECT /*+ INDEX(prr PAY_RUN_RESULTS_N50,PAY_RUN_RESULTS_N1) */ et.element_type_id
57 ,et.element_name
58 ,iv.input_value_id
59 ,iv.name input_value_name
60 ,rr.run_result_id
61 ,aa.assignment_action_id
62 ,aa.assignment_id
63 ,DECODE(et.element_name, 'GOSI', rr.element_entry_id, rr.source_id) element_entry_id
64 FROM pay_element_types_f et
65 ,pay_input_values_f iv
66 ,pay_run_results rr
67 ,pay_assignment_actions aa
68 WHERE et.element_name IN ('Employer GOSI Hazards'
69 ,'GOSI'
70 ,'Employee GOSI Annuities'
71 ,'Employee GOSI Arrears'
72 ,'Employer GOSI Annuities'
73 ,'Employer GOSI Hazards'
74 ,'Employer GOSI Subsidy'
75 ,'GOSI Reference Salary')
76 AND et.legislation_code = 'SA'
77 AND iv.element_type_id = et.element_type_id
78 AND iv.name = 'Joiner'
79 AND rr.element_type_id = et.element_type_id
80 AND aa.assignment_action_id = rr.assignment_action_id
81 AND NOT EXISTS (SELECT NULL
82 FROM pay_run_result_values rrv
83 WHERE rrv.run_result_id = rr.run_result_id
84 AND rrv.input_value_id = iv.input_value_id)
85 ORDER BY aa.assignment_action_id;
86 --
87 --
88 -- Local variables.
89 --
90 l_proc VARCHAR2(61) := g_package || 'upgrade_run_results';
91 l_result_rec csr_results%ROWTYPE;
92 l_assact_id NUMBER := -1;
93 l_assact_count NUMBER := 0;
94 l_joiner_context_id NUMBER;
95 l_leaver_context_id NUMBER;
96 l_nationality_context_id NUMBER;
97 l_joiner VARCHAR2(30);
98 l_leaver VARCHAR2(30);
99 l_nationality VARCHAR2(30);
100 --
101 BEGIN
102 --
103 hr_utility.set_location('Entering: ' || l_proc, 10);
104 --
105 --
106 -- Get the IDs for the three contexts that are used.
107 --
108 l_joiner_context_id := get_context_id('SOURCE_TEXT');
109 l_leaver_context_id := get_context_id('SOURCE_TEXT2');
110 l_nationality_context_id := get_context_id('SOURCE_NUMBER');
111 --
112 --
113 -- Loop through all run results.
114 --
115 OPEN csr_results;
116 LOOP
117 --
118 --
119 -- Get the next run result.
120 --
121 FETCH csr_results INTO l_result_rec;
122 EXIT WHEN csr_results%NOTFOUND;
123 --
124 --
125 -- New assignment action being processed.
126 --
127 IF l_assact_id <> l_result_rec.assignment_action_id THEN
128 --
129 --
130 -- Store the latest assignmment action and keep count of the total number
131 -- of assignment actions that are being processed.
132 --
133 l_assact_id := l_result_rec.assignment_action_id;
134 l_assact_count := l_assact_count + 1;
135 --
136 --
137 -- Commit every 100 assignment actions to reduce the transaction size.
138 --
139 IF MOD(l_assact_count, 100) = 0 THEN
140 COMMIT;
141 END IF;
142 --
143 --
144 -- Derive the contexts.
145 --
146 pay_sa_rules.get_source_number_context
147 (l_result_rec.assignment_action_id
148 ,l_result_rec.element_entry_id
149 ,l_nationality);
150 --
151 pay_sa_rules.get_source_text_context
152 (l_result_rec.assignment_action_id
153 ,l_result_rec.element_entry_id
154 ,l_joiner);
155 --
156 pay_sa_rules.get_source_text2_context
157 (l_result_rec.assignment_action_id
158 ,l_result_rec.element_entry_id
159 ,l_leaver);
160 --
161 --
162 -- Create action contexts.
163 --
164 INSERT INTO pay_action_contexts
165 (assignment_action_id
166 ,assignment_id
167 ,context_id
168 ,context_value)
169 VALUES
170 (l_result_rec.assignment_action_id
171 ,l_result_rec.assignment_id
172 ,l_joiner_context_id
173 ,l_joiner);
174 --
175 INSERT INTO pay_action_contexts
176 (assignment_action_id
177 ,assignment_id
178 ,context_id
179 ,context_value)
180 VALUES
181 (l_result_rec.assignment_action_id
182 ,l_result_rec.assignment_id
183 ,l_leaver_context_id
184 ,l_leaver);
185 --
186 INSERT INTO pay_action_contexts
187 (assignment_action_id
188 ,assignment_id
189 ,context_id
190 ,context_value)
191 VALUES
192 (l_result_rec.assignment_action_id
193 ,l_result_rec.assignment_id
194 ,l_nationality_context_id
195 ,l_nationality);
196 END IF;
197 --
198 --
199 -- Create the run result values.
200 --
201 INSERT INTO pay_run_result_values
202 (run_result_id
203 ,input_value_id
204 ,result_value)
205 SELECT l_result_rec.run_result_id
206 ,iv.input_value_id
207 ,DECODE(iv.name, 'Joiner' , l_joiner
208 , 'Leaver' , l_leaver
209 , 'Nationality', l_nationality)
210 FROM pay_input_values_f iv
211 WHERE iv.element_type_id = l_result_rec.element_type_id
212 AND iv.name IN ('Joiner', 'Leaver', 'Nationality');
213 END LOOP;
214 --
215 CLOSE csr_results;
216 COMMIT;
217 --
218 hr_utility.set_location('Leaving: ' || l_proc, 20);
219 --
220 END upgrade_run_results;
221 --
222 --
223 -- -------------------------------------------------------------------------------------------
224 -- The main upgrade.
225 -- -------------------------------------------------------------------------------------------
226 --
227 PROCEDURE run(p_errbuf OUT NOCOPY VARCHAR2
228 ,p_retcode OUT NOCOPY NUMBER
229 ) IS
230 --
231 --
232 -- Local variables.
233 --
234 l_proc VARCHAR2(61) := g_package || 'run';
235 BEGIN
236 --
237 hr_utility.set_location('Entering: ' || l_proc, 10);
238 --
239 --
240 -- Check to see if the nationality profile is set.
241 --
242 if FND_PROFILE.VALUE('PER_LOCAL_NATIONALITY') is null then
243 FND_MESSAGE.SET_NAME('PAY', 'HR_374812_SA_LOC_NAT_NOT_DEF');
244 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
245 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
246 end if;
247 --
248 --
249 -- Correct run results and action contexts.
250 --
251 upgrade_run_results;
252 --
253 hr_utility.set_location('Leaving: ' || l_proc, 20);
254 --
255 EXCEPTION
256 WHEN OTHERS THEN
257 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM);
258 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
259 ROLLBACK;
260 p_errbuf := NULL;
261 p_retcode := 2;
262 RAISE_APPLICATION_ERROR(-20001, SQLERRM);
263 END run;
264 --
265 END pay_sa_upgrade;