[Home] [Help]
PACKAGE BODY: APPS.PER_MM_ASSIGNMENTS_PKG
Source
1 package body PER_MM_ASSIGNMENTS_PKG as
2 /* $Header: pemmv03t.pkb 120.1 2005/11/07 02:39:04 pchowdav noship $ */
3 --
4 --
5 procedure update_row
6 (p_default_from in varchar2,
7 p_select_assignment in varchar2,
8 p_grade_id in number,
9 p_tax_unit_id in number,
10 p_row_id in varchar2)
11
12 is
13 begin
14 update per_mm_assignments
15 set default_from = p_default_from,
16 select_assignment = p_select_assignment,
17 grade_id = p_grade_id,
18 tax_unit_id = p_tax_unit_id
19 where rowid = p_row_id;
20 if (sql%notfound) then
21 raise no_data_found;
22 end if;
23
24 end update_row;
25 --
26 --
27 procedure load_rows
28 (p_mass_move_id in number,
29 p_session_date in date)
30 is
31 -- fix for bug 4704865 starts here.
32 l_rule_mode VARCHAR2(30);
33
34 cursor csr_chk_rule_mode is
35 select rule_mode
36 from pay_legislation_rules plr,
37 per_business_groups bg
38 where plr.legislation_code = bg.legislation_code
39 and bg.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
40 and rule_type = 'PERWSMMV_GRE';
41
42 begin
43
44 open csr_chk_rule_mode;
45 fetch csr_chk_rule_mode into l_rule_mode;
46 close csr_chk_rule_mode;
47
48 if nvl(l_rule_mode,'N') = 'Y'
49 then
50 -- fix for bug 4704865 ends here.
51 insert into per_mm_assignments
52 (MASS_MOVE_ID,
53 ASSIGNMENT_ID,
54 OBJECT_VERSION_NUMBER,
55 POSITION_ID,
56 DEFAULT_FROM,
57 SELECT_ASSIGNMENT,
58 ASSIGNMENT_MOVED,
59 GRADE_ID,
60 TAX_UNIT_ID
61 )
62 select
63 p_mass_move_id,
64 asg.assignment_id,
65 asg.object_version_number,
66 asg.position_id,
67 'A',
68 'Y',
69 'N',
70 gra.grade_id,
71 to_number(scl.segment1)
72 from per_assignments_f asg,
73 per_mm_positions mmpos,
74 per_assignment_status_types stat,
75 hr_soft_coding_keyflex scl,
76 per_grades gra
77 where asg.position_id = mmpos.position_id
78 and mmpos.mass_move_id = p_mass_move_id
79 and p_session_date between
80 asg.effective_start_date and
81 asg.effective_end_date
82 and asg.grade_id = gra.grade_id (+)
83 and asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id (+)
84 and stat.assignment_status_type_id =
85 asg.assignment_status_type_id
86 and stat.per_system_status in ('ACTIVE_ASSIGN'
87 ,'ACTIVE_APL'
88 ,'ACCEPTED'
89 ,'ACTIVE_CWK')
90 and asg.assignment_type in ('E','A','C');
91
92 -- fix for bug 4704865 starts here.
93 else
94 insert into per_mm_assignments
95 (MASS_MOVE_ID,
96 ASSIGNMENT_ID,
97 OBJECT_VERSION_NUMBER,
98 POSITION_ID,
99 DEFAULT_FROM,
100 SELECT_ASSIGNMENT,
101 ASSIGNMENT_MOVED,
102 GRADE_ID,
103 TAX_UNIT_ID
104 )
105 select
106 p_mass_move_id,
107 asg.assignment_id,
108 asg.object_version_number,
109 asg.position_id,
110 'A',
111 'Y',
112 'N',
113 gra.grade_id,
114 null
115 from per_assignments_f asg,
116 per_mm_positions mmpos,
117 per_assignment_status_types stat,
118 per_grades gra
119 where asg.position_id = mmpos.position_id
120 and mmpos.mass_move_id = p_mass_move_id
121 and p_session_date between
122 asg.effective_start_date and
123 asg.effective_end_date
124 and asg.grade_id = gra.grade_id (+)
125 and stat.assignment_status_type_id =
126 asg.assignment_status_type_id
127 and stat.per_system_status in ('ACTIVE_ASSIGN'
128 ,'ACTIVE_APL'
129 ,'ACCEPTED'
130 ,'ACTIVE_CWK')
131 and asg.assignment_type in ('E','A','C');
132 end if;
133 -- fix for bug 4704865 ends here.
134 exception
135 when no_data_found then
136 null;
137 when others then
138 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
139 hr_utility.set_message_token('PROCEDURE','per_mm_assignments_pkg.load_rows');
140 hr_utility.set_message_token('STEP','1');
141 hr_utility.raise_error;
142
143 end load_rows;
144 --
145 --
146 procedure lock_row
147 (p_mass_move_id in number,
148 p_assignment_id in number,
149 p_position_id in number,
150 p_default_from in varchar2,
151 p_select_assignment in varchar2,
152 p_grade_id in number,
153 p_tax_unit_id in number,
154 p_row_id in varchar2)
155
156 is
157 counter number;
158 cursor c is
159 select *
160 from per_mm_assignments
161 where rowid = p_row_id
162 for update of select_assignment nowait;
163 recinfo c%rowtype;
164 begin
165 counter := 0;
166 loop
167 begin
168 counter := counter + 1;
169 open c;
170 fetch c into recinfo;
171 if (c%notfound) then
172 close c;
173 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
174 hr_utility.set_message_token('PROCEDURE','mm_assignment_pkg.lock_rows');
175 hr_utility.set_message_token('STEP','1');
176 hr_utility.raise_error;
177 end if;
178 close c;
179 if (
180 (recinfo.mass_move_id = p_mass_move_id)
181 AND
182 (recinfo.assignment_id = p_assignment_id)
183 AND
184 (recinfo.position_id = p_position_id)
185 AND
186 (recinfo.default_from = p_default_from)
187 AND(
188 (recinfo.select_assignment = p_select_assignment)
189 OR ( (recinfo.select_assignment is null)
190 AND (p_select_assignment is null)))
191 AND(
192 (recinfo.grade_id = p_grade_id)
193 OR ( (recinfo.grade_id is null)
194 AND (p_grade_id is null)))
195 AND(
196 (recinfo.tax_unit_id = p_tax_unit_id)
197 OR ( (recinfo.tax_unit_id is null)
198 AND (p_tax_unit_id is null)))
199 ) then
200 return;
201 else
202 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
203 hr_utility.set_message_token('PROCEDURE','mm_assignment_pkg.lock_rows');
204 hr_utility.set_message_token('STEP','2');
205 hr_utility.raise_error;
206 end if;
207 exception
208 when app_exceptions.record_lock_exception then
209 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
210 hr_utility.set_message_token('PROCEDURE','mm_assignment_pkg.lock_rows');
211 hr_utility.set_message_token('STEP','3');
212 hr_utility.raise_error;
213 end;
214 end loop;
215 end lock_row ;
216 --
217 --
218 procedure restore_defaults
219 (p_mass_move_id in number,
220 p_assignment_id in number,
221 p_grade_id out nocopy number,
222 p_grade_name out nocopy varchar2,
223 p_tax_unit_id out nocopy number,
224 p_tax_unit_name out nocopy varchar2)
225 is
226
227 l_grade_id number(15);
228 l_grade_name varchar(240);
229 l_tax_unit_id number(15);
230
231 -- 4385302 starts here
232 -- previously defined local var has been commented and newly defined
233 --l_tax_unit_name varchar(30);
234 l_tax_unit_name per_mm_assignments_v.tax_unit_name%type;
235 -- 4385302 ends here
236
237 cursor c is
238 select grade_id,
239 grade_name,
240 tax_unit_id,
241 tax_unit_name
242 from per_mm_assignments_v
243 where mass_move_id = p_mass_move_id
244 and assignment_id = p_assignment_id;
245
246 begin
247 open c;
248 fetch c into l_grade_id,
249 l_grade_name,
250 l_tax_unit_id,
251 l_tax_unit_name;
252 if c%notfound then
253 close c;
254 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
255 hr_utility.set_message_token('PROCEDURE','mm_assignment_pkg.restore_defaults');
256 hr_utility.set_message_token('STEP','1');
257 hr_utility.raise_error;
258 end if;
259 close c;
260
261 p_grade_id := l_grade_id;
262 p_grade_name := l_grade_name;
263 p_tax_unit_id := l_tax_unit_id;
264 p_tax_unit_name := l_tax_unit_name;
265
266
267 end restore_defaults;
268 --
269 --
270 end per_mm_assignments_pkg;