[Home] [Help]
PACKAGE BODY: APPS.PER_SSHR_CHANGE_PAY
Source
1 PACKAGE BODY PER_SSHR_CHANGE_PAY as
2 /* $Header: pepypshr.pkb 120.45.12010000.4 2008/11/07 10:40:31 schowdhu ship $ */
3 --
4 -- --------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- --------------------------------------------------------------------------
7 --
8 g_package Varchar2(30) := 'per_sshr_change_pay.';
9 g_debug boolean := hr_utility.debug_enabled;
10 --
11 type t_tx_name is table of varchar2(30) index by binary_integer;
12 type t_tx_char is table of varchar2(2000) index by binary_integer;
13 type t_tx_num is table of number index by binary_integer;
14 type t_tx_date is table of date index by binary_integer;
15 type t_tx_type is table of varchar2(30) index by binary_integer;
16 --
17 --------------------------------------------------------------------------------
18 --
19 --
20
21 function Check_GSP_Manual_Override (p_assignment_id in NUMBER, p_effective_date in DATE,p_transaction_id in NUMBER)
22 RETURN VARCHAR2
23 is
24 --
25 Cursor csr_gsp_ladder_id Is
26 select hatv.number_value
27 from hr_api_transaction_steps hats,
28 hr_api_transactions hat,
29 hr_api_transaction_values hatv
30 where hats.api_name = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API'
31 and hatv.transaction_step_id = hats.transaction_step_id
32 and hatv.name = 'P_GRADE_LADDER_PGM_ID'
33 and hats.TRANSACTION_ID = hat.TRANSACTION_ID
34 and hat.TRANSACTION_ID = p_transaction_id;
35 --
36 Cursor csr_assignment_check Is
37 Select Nvl(Gsp_Allow_Override_Flag,'Y')
38 From Ben_Pgm_f Pgm,
39 Per_all_assignments_F paa
40 Where paa.Assignment_Id = p_assignment_id
41 and p_effective_date between paa.Effective_Start_Date and paa.Effective_End_Date
42 and paa.GRADE_LADDER_PGM_ID is Not NULL
43 and pgm.pgm_id = paa.Grade_Ladder_Pgm_Id
44 and p_effective_date between Pgm.Effective_Start_Date and Pgm.Effective_End_Date
45 and Pgm_typ_Cd = 'GSP'
46 and Pgm_stat_Cd = 'A'
47 and Update_Salary_Cd = 'SALARY_BASIS';
48 --
49 Cursor csr_transaction_check(l_transaction_ladder_id number) Is
50 Select Nvl(Gsp_Allow_Override_Flag,'Y')
51 From Ben_Pgm_f Pgm
52 Where pgm.pgm_id = l_transaction_ladder_id
53 and p_effective_date between Pgm.Effective_Start_Date and Pgm.Effective_End_Date
54 and Pgm_typ_Cd = 'GSP'
55 and Pgm_stat_Cd = 'A'
56 and Update_Salary_Cd = 'SALARY_BASIS';
57 --
58 l_status Varchar2(1) := 'Y';
59 l_txn_grade_ladder_id number;
60 Begin
61 l_txn_grade_ladder_id := -1;
62 if g_debug then
63 hr_utility.set_location('Enter Check_GSP_Manual_Override ', 1);
64 hr_utility.set_location('p_assignment_id '||p_assignment_id, 2);
65 hr_utility.set_location('p_effective_date '||p_effective_date,3);
66 hr_utility.set_location('p_transaction_id '||p_transaction_id,4);
67 end if;
68
69 Open csr_gsp_ladder_id;
70 Fetch csr_gsp_ladder_id into l_txn_grade_ladder_id ;
71 Close csr_gsp_ladder_id;
72
73 if g_debug then
74 hr_utility.set_location('In GSP_CHECK l_txn_grade_ladder_id '||l_txn_grade_ladder_id, 5);
75 end if;
76
77 if l_txn_grade_ladder_id is null or l_txn_grade_ladder_id = -1 then
78 Open csr_assignment_check;
79 Fetch csr_assignment_check into l_Status;
80 Close csr_assignment_check;
81 if g_debug then
82 hr_utility.set_location('In GSP_CHECK_AST l_Status '||l_Status, 6);
83 end if;
84 else
85 Open csr_transaction_check(l_txn_grade_ladder_id);
86 Fetch csr_transaction_check into l_Status;
87 Close csr_transaction_check;
88 if g_debug then
89 hr_utility.set_location('In GSP_CHECK_TXN l_Status '||l_Status, 7);
90 end if;
91 end if;
92 RETURN l_Status;
93 End;
94
95
96 --
97 --
98 PROCEDURE check_base_salary_profile(p_transaction_step_id in NUMBER
99 ,p_item_key in varchar2
100 ,p_item_type in varchar2
101 ,p_effective_date in date
102 ,p_assignment_id in varchar2)
103 is
104 --
105 l_hr_base_salary_required VARCHAR2(10) := fnd_profile.VALUE('HR_BASE_SALARY_REQUIRED');
106 --
107 l_change_date date := null;
108 l_asst_id number;
109 l_txn_basis number;
110 l_ast_basis number;
111 l_transaction_id number;
112 l_transaction_step_id number;
113 l_pay_basis per_all_assignments_f.pay_basis_id%type;
114 --
115 Cursor asg_step is
116 select transaction_id,transaction_step_id
117 from hr_api_transaction_steps
118 where transaction_step_id = (Select transaction_step_id from hr_api_transaction_steps
119 where item_key = p_item_key
120 and item_type = p_item_type
121 and api_name = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API');
122
123 --
124 --
125 Cursor txn_details(c_transaction_step_id number) Is
126 select max(col1) as assignment_id,
127 max(col2) as pay_basis_id
128 from
129 (select decode(NAME, 'P_ASSIGNMENT_ID', NUMBER_VALUE) col1,
130 decode(NAME, 'P_PAY_BASIS_ID', NUMBER_VALUE) col2
131 from hr_api_transaction_values
132 where TRANSACTION_STEP_ID = c_transaction_step_id);
133 --
134 Cursor csr_pay_basis_exists(c_assignment_id number,c_effective_date date) IS
135 select pay_basis_id
136 from per_all_assignments_f
137 where assignment_id = c_assignment_id
138 and c_effective_date between effective_start_date and effective_end_date;
139 --
140 Cursor csr_txn_prop is
141 select change_date
142 from per_pay_transactions
143 where p_transaction_step_id is not null
144 and transaction_step_id = p_transaction_step_id
145 and PARENT_PAY_TRANSACTION_ID is null
146 and status <> 'DELETE';
147 --
148 Cursor csr_prop is
149 select change_date
150 from per_pay_proposals
151 where assignment_id = p_assignment_id
152 and pay_proposal_id not in (select pay_proposal_id
153 from per_pay_transactions
154 where p_transaction_step_id is not null
155 and transaction_step_id = p_transaction_step_id
156 and PARENT_PAY_TRANSACTION_ID is null
157 and status <> 'DELETE');
158 --
159 BEGIN
160 --
161 if g_debug then
162 hr_utility.set_location('Enter check_base_salary_profile ', 1);
163 end if;
164 --
165 -- Get the transaction_step_id of the assignment step.
166 --
167 Open asg_step;
168 Fetch asg_step into l_transaction_id,l_transaction_step_id;
169 Close asg_step;
170 --
171 if g_debug then
172 hr_utility.set_location('l_transaction_id '||l_transaction_id, 2);
173 hr_utility.set_location('l_transaction_step_id '||l_transaction_step_id, 3);
174 end if;
175 --
176 -- There exists an assignment step, Hence get the pay basis from the txn table.
177 -- Note, If new hire flow, there will be an assignment txn step.
178 --
179 if l_transaction_step_id is not null then
180 -- Get pay basis id from assignment step.
181 Open txn_details(l_transaction_step_id);
182 Fetch txn_details into l_asst_id,l_txn_basis;
183 Close txn_details;
184
185 l_pay_basis := l_txn_basis;
186 Else
187 -- Get pay basis from assignment
188 Open csr_pay_basis_exists(p_assignment_id,p_effective_date);
189 Fetch csr_pay_basis_exists into l_ast_basis;
190 Close csr_pay_basis_exists;
191 l_pay_basis := l_ast_basis;
192
193 end if;
194
195 -- There is a pay basis either in assignment or on txn table.
196 --
197 if ((l_hr_base_salary_required is not null)
198 and (l_hr_base_salary_required = 'Y')
199 and (l_pay_basis is not null ))
200 then
201 -- foll cursor wont return any rows if
202 -- 1) no action was done through change pay pages
203 -- 2) only action done through change pay pages was delete
204 --
205 Open csr_txn_prop;
206 Fetch csr_txn_prop into l_change_date;
207 Close csr_txn_prop;
208 --
209 -- If No rows returned by above cursor, we need to check master table.
210 --
211 if l_change_date is null then
212 --
213 -- Foll cursor wont return any rows if
214 -- 1) we are new hire flow and the assignment is new
215 -- 2) we are any other flow, but deleted all the pay proposals
216 --
217 Open csr_prop ;
218 Fetch csr_prop into l_change_date;
219 Close csr_prop ;
220 -- If no row returned above, raise error
221 if l_change_date is null then
222 hr_utility.set_message(800,'PER_33490_CHGPAY_PROPOSAL_REQD');
223 hr_utility.raise_error;
224 end if;
225 --
226 End if;
227 --
228 End if;
229 End;
230 --
231 --
232 FUNCTION get_comp_flex(p_dff_name in varchar2)
233 return VARCHAR2
234 IS
235 l_mandatory_field varchar2(20);
236 cursor flex is
237 select APPLICATION_COLUMN_NAME from
238 fnd_descr_flex_col_usage_vl
239 where APPLICATION_ID = 800
240 and DESCRIPTIVE_FLEXFIELD_NAME = p_dff_name
241 and nvl(REQUIRED_FLAG,'N') = 'Y';
242 begin
243 open flex;
244 fetch flex into l_mandatory_field;
245 close flex;
246
247 if l_mandatory_field is null then
248 l_mandatory_field := '';
249 end if;
250
251 return l_mandatory_field;
252 end get_comp_flex;
253
254 --
255 --
256
257 PROCEDURE create_salary_basis_chg_step
258 (p_item_type in varchar2 ,
259 p_item_key in varchar2 ,
260 p_activity_id in number ,
261 P_ASSIGNMENT_ID IN NUMBER ,
262 P_PAY_BASIS_ID IN NUMBER ,
263 P_DATETRACK_UPDATE_MODE IN VARCHAR2 ,
264 P_EFFECTIVE_DATE IN DATE ,
265 P_EFFECTIVE_DATE_OPTION IN VARCHAR2 ,
266 P_LOGIN_PERSON_ID IN NUMBER ,
267 P_APPROVER_ID IN NUMBER default null,
268 P_SAVE_MODE IN VARCHAR2 default null) IS
269 --
270 --
271 l_tx_name t_tx_name;
272 l_tx_char t_tx_char;
273 l_tx_num t_tx_num;
274 l_tx_date t_tx_date;
275 l_tx_type t_tx_type;
276
277 l_api_error boolean;
278 l_transaction_id number := null;
279 l_transaction_step_id number := null;
280 l_result varchar2(100);
281 l_count number := 1;
282 l_update_mode boolean := true;
283 --
284 l_asg_rec per_all_assignments_f%ROWTYPE;
285 --
286 Cursor csg_asg_details is
287 Select * from per_all_assignments_f
288 Where assignment_id = p_assignment_id
289 and trunc(p_effective_date) between effective_start_date and effective_end_date;
290 --
291 Begin
292
293 -- Check if the step already exists, create if it does not exist.
294 get_pay_transaction
295 (p_item_type => p_item_type,
296 p_item_key => p_item_key,
297 p_activity_id => p_activity_id,
298 p_login_person_id => p_login_person_id,
299 p_api_name => 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API',
300 p_effective_date_option => p_effective_date_option,
301 p_transaction_id => l_transaction_id,
302 p_transaction_step_id => l_transaction_step_id,
303 p_update_mode => l_update_mode);
304 --
305 Update hr_api_transactions
306 set transaction_effective_date = trunc(P_EFFECTIVE_DATE)
307 where transaction_id = l_transaction_id;
308 --
309 wf_engine.setitemattrtext (itemtype => p_item_type
310 ,itemkey => p_item_key
311 ,aname => 'P_EFFECTIVE_DATE'
312 ,avalue => to_char(trunc(P_EFFECTIVE_DATE),'YYYY-MM-DD'));
313
314
315 --
316 -- If it exists, perform update of the transaction values
317 --
318 If l_update_mode then
319 --
320 l_count := 1;
321 --
322 -- Initialise the passed transaction values.
323 --
324 l_tx_name(l_count) := 'P_APPROVER_ID';
325 l_tx_char(l_count) := null;
326 l_tx_num(l_count) := P_APPROVER_ID;
327 l_tx_date(l_count) := null;
328 l_tx_type(l_count) := 'NUMBER';
329
330 /**
331 l_count := l_count + 1;
332 l_tx_name(l_count) := 'P_ASSIGNMENT_ID';
333 l_tx_char(l_count) := null;
334 l_tx_num(l_count) := P_ASSIGNMENT_ID;
335 l_tx_date(l_count) := null;
336 l_tx_type(l_count) := 'NUMBER';
337
338 l_count := l_count + 1;
339 l_tx_name(l_count) := 'P_DATETRACK_UPDATE_MODE';
340 l_tx_char(l_count) := P_DATETRACK_UPDATE_MODE;
341 l_tx_num(l_count) := null;
342 l_tx_date(l_count) := null;
343 l_tx_type(l_count) := 'VARCHAR2';
344 **/
345
346 l_count := l_count + 1;
347 l_tx_name(l_count) := 'P_EFFECTIVE_DATE';
348 l_tx_char(l_count) := null;
349 l_tx_num(l_count) := null;
350 l_tx_date(l_count) := P_EFFECTIVE_DATE;
351 l_tx_type(l_count) := 'DATE';
352
353 l_count := l_count + 1;
354 l_tx_name(l_count) := 'P_EFFECTIVE_DATE_OPTION';
355 l_tx_char(l_count) := P_EFFECTIVE_DATE_OPTION;
356 l_tx_num(l_count) := null;
357 l_tx_date(l_count) := null;
358 l_tx_type(l_count) := 'VARCHAR2';
359
360 l_count := l_count + 1;
361 l_tx_name(l_count) := 'P_LOGIN_PERSON_ID';
362 l_tx_char(l_count) := null;
363 l_tx_num(l_count) := P_LOGIN_PERSON_ID;
364 l_tx_date(l_count) := null;
365 l_tx_type(l_count) := 'NUMBER';
366
367 l_count := l_count + 1;
368 l_tx_name(l_count) := 'P_REVIEW_ACTID';
369 l_tx_char(l_count) := to_char(p_activity_id);
370 l_tx_num(l_count) := null;
371 l_tx_date(l_count) := null;
372 l_tx_type(l_count) := 'VARCHAR2';
373
374 l_count := l_count + 1;
375 l_tx_name(l_count) := 'P_REVIEW_PROC_CALL';
376 l_tx_char(l_count) := 'HrAssignment';
377 l_tx_num(l_count) := null;
378 l_tx_date(l_count) := null;
379 l_tx_type(l_count) := 'VARCHAR2';
380
381 If P_SAVE_MODE is not null then
382 l_count := l_count + 1;
383 l_tx_name(l_count) := 'P_SAVE_MODE';
384 l_tx_char(l_count) := P_SAVE_MODE;
385 l_tx_num(l_count) := null;
386 l_tx_date(l_count) := null;
387 l_tx_type(l_count) := 'VARCHAR2';
388 else
389 l_count := l_count + 1;
390 l_tx_name(l_count) := 'P_SAVE_MODE';
391 l_tx_char(l_count) := 'SAVE';
392 l_tx_num(l_count) := null;
393 l_tx_date(l_count) := null;
394 l_tx_type(l_count) := 'VARCHAR2';
395 End if;
396
397 l_count := l_count + 1;
398 l_tx_name(l_count) := 'P_PAY_BASIS_ID';
399 l_tx_char(l_count) := null;
400 l_tx_num(l_count) := P_PAY_BASIS_ID;
401 l_tx_date(l_count) := null;
402 l_tx_type(l_count) := 'NUMBER';
403 --
404 forall i in 1..l_count
405 update hr_api_transaction_values
406 set
407 varchar2_value = l_tx_char(i),
408 number_value = l_tx_num(i),
409 date_value = l_tx_date(i)
410 where transaction_step_id = l_transaction_step_id
411 and name = l_tx_name(i);
412 --
413 Else
414 --
415 Open csg_asg_details;
416 Fetch csg_asg_details into l_asg_rec;
417 Close csg_asg_details;
418 --
419 l_count := 1;
420 --
421 -- Initialise the passed transaction values.
422 --
423
424 l_tx_name(l_count) := 'P_ASSIGNMENT_ID';
425 l_tx_char(l_count) := null;
426 l_tx_num(l_count) := P_ASSIGNMENT_ID;
427 l_tx_date(l_count) := null;
428 l_tx_type(l_count) := 'NUMBER';
429
430 l_count := l_count + 1;
431 l_tx_name(l_count) := 'P_OBJECT_VERSION_NUMBER';
432 l_tx_char(l_count) := null;
433 l_tx_num(l_count) := l_asg_rec.OBJECT_VERSION_NUMBER;
434 l_tx_date(l_count) := null;
435 l_tx_type(l_count) := 'NUMBER';
436
437 l_count := l_count + 1;
438 l_tx_name(l_count) := 'P_EFFECTIVE_DATE';
439 l_tx_char(l_count) := null;
440 l_tx_num(l_count) := null;
441 l_tx_date(l_count) := P_EFFECTIVE_DATE;
442 l_tx_type(l_count) := 'DATE';
443
444 l_count := l_count + 1;
445 l_tx_name(l_count) := 'P_EFFECTIVE_DATE_OPTION';
446 l_tx_char(l_count) := P_EFFECTIVE_DATE_OPTION;
447 l_tx_num(l_count) := null;
448 l_tx_date(l_count) := null;
449 l_tx_type(l_count) := 'VARCHAR2';
450
451 l_count := l_count + 1;
452 l_tx_name(l_count) := 'P_ELEMENT_CHANGED';
453 l_tx_char(l_count) := null;
454 l_tx_num(l_count) := null;
455 l_tx_date(l_count) := null;
456 l_tx_type(l_count) := 'VARCHAR2';
457
458 l_count := l_count + 1;
459 l_tx_name(l_count) := 'P_DATETRACK_UPDATE_MODE';
460 l_tx_char(l_count) := P_DATETRACK_UPDATE_MODE;
461 l_tx_num(l_count) := null;
462 l_tx_date(l_count) := null;
463 l_tx_type(l_count) := 'VARCHAR2';
464
465 l_count := l_count + 1;
466 l_tx_name(l_count) := 'P_ORGANIZATION_ID';
467 l_tx_char(l_count) := null;
468 l_tx_num(l_count) := l_asg_rec.ORGANIZATION_ID;
469 l_tx_date(l_count) := null;
470 l_tx_type(l_count) := 'NUMBER';
471
472 l_count := l_count + 1;
473 l_tx_name(l_count) := 'P_BUSINESS_GROUP_ID';
474 l_tx_char(l_count) := null;
475 l_tx_num(l_count) := l_asg_rec.BUSINESS_GROUP_ID;
476 l_tx_date(l_count) := null;
477 l_tx_type(l_count) := 'NUMBER';
478
479 l_count := l_count + 1;
480 l_tx_name(l_count) := 'P_PERSON_ID';
481 l_tx_char(l_count) := null;
482 l_tx_num(l_count) := l_asg_rec.PERSON_ID;
483 l_tx_date(l_count) := null;
484 l_tx_type(l_count) := 'NUMBER';
485
486
487 l_count := l_count + 1;
488 l_tx_name(l_count) := 'P_LOGIN_PERSON_ID';
489 l_tx_char(l_count) := null;
490 l_tx_num(l_count) := P_LOGIN_PERSON_ID;
491 l_tx_date(l_count) := null;
492 l_tx_type(l_count) := 'NUMBER';
493
494 l_count := l_count + 1;
495 l_tx_name(l_count) := 'P_ORG_NAME';
496 l_tx_char(l_count) := null;
497 l_tx_num(l_count) := null;
498 l_tx_date(l_count) := null;
499 l_tx_type(l_count) := 'VARCHAR2';
500
501
502 l_count := l_count + 1;
503 l_tx_name(l_count) := 'P_POSITION_ID';
504 l_tx_char(l_count) := null;
505 l_tx_num(l_count) := l_asg_rec.POSITION_ID;
506 l_tx_date(l_count) := null;
507 l_tx_type(l_count) := 'NUMBER';
508
509 l_count := l_count + 1;
510 l_tx_name(l_count) := 'P_POS_NAME';
511 l_tx_char(l_count) := null;
512 l_tx_num(l_count) := null;
513 l_tx_date(l_count) := null;
514 l_tx_type(l_count) := 'VARCHAR2';
515
516 l_count := l_count + 1;
517 l_tx_name(l_count) := 'P_JOB_ID';
518 l_tx_char(l_count) := null;
519 l_tx_num(l_count) := l_asg_rec.JOB_ID;
520 l_tx_date(l_count) := null;
521 l_tx_type(l_count) := 'NUMBER';
522
523 l_count := l_count + 1;
524 l_tx_name(l_count) := 'P_JOB_NAME';
525 l_tx_char(l_count) := null;
526 l_tx_num(l_count) := null;
527 l_tx_date(l_count) := null;
528 l_tx_type(l_count) := 'VARCHAR2';
529
530
531 l_count := l_count + 1;
532 l_tx_name(l_count) := 'P_GRADE_ID';
533 l_tx_char(l_count) := null;
534 l_tx_num(l_count) := l_asg_rec.GRADE_ID;
535 l_tx_date(l_count) := null;
536 l_tx_type(l_count) := 'NUMBER';
537
538 l_count := l_count + 1;
539 l_tx_name(l_count) := 'P_GRADE_NAME';
540 l_tx_char(l_count) := null;
541 l_tx_num(l_count) := null;
542 l_tx_date(l_count) := null;
543 l_tx_type(l_count) := 'VARCHAR2';
544
545 l_count := l_count + 1;
546 l_tx_name(l_count) := 'P_LOCATION_ID';
547 l_tx_char(l_count) := null;
548 l_tx_num(l_count) := l_asg_rec.LOCATION_ID;
549 l_tx_date(l_count) := null;
550 l_tx_type(l_count) := 'NUMBER';
551
552 l_count := l_count + 1;
553 l_tx_name(l_count) := 'P_EMPLOYMENT_CATEGORY';
554 l_tx_char(l_count) := l_asg_rec.EMPLOYMENT_CATEGORY;
555 l_tx_num(l_count) := null;
556 l_tx_date(l_count) := null;
557 l_tx_type(l_count) := 'VARCHAR2';
558
559 l_count := l_count + 1;
560 l_tx_name(l_count) := 'P_SUPERVISOR_ID';
561 l_tx_char(l_count) := null;
562 l_tx_num(l_count) := l_asg_rec.SUPERVISOR_ID;
563 l_tx_date(l_count) := null;
564 l_tx_type(l_count) := 'NUMBER';
565
566
567 l_count := l_count + 1;
568 l_tx_name(l_count) := 'P_MANAGER_FLAG';
569 l_tx_char(l_count) := l_asg_rec.MANAGER_FLAG;
570 l_tx_num(l_count) := null;
571 l_tx_date(l_count) := null;
572 l_tx_type(l_count) := 'VARCHAR2';
573
574 l_count := l_count + 1;
575 l_tx_name(l_count) := 'P_NORMAL_HOURS';
576 l_tx_char(l_count) := null;
577 l_tx_num(l_count) := l_asg_rec.NORMAL_HOURS;
578 l_tx_date(l_count) := null;
579 l_tx_type(l_count) := 'NUMBER';
580
581 l_count := l_count + 1;
582 l_tx_name(l_count) := 'P_FREQUENCY';
583 l_tx_char(l_count) := l_asg_rec.FREQUENCY;
584 l_tx_num(l_count) := null;
585 l_tx_date(l_count) := null;
586 l_tx_type(l_count) := 'VARCHAR2';
587
588 l_count := l_count + 1;
589 l_tx_name(l_count) := 'P_TIME_NORMAL_FINISH';
590 l_tx_char(l_count) := l_asg_rec.TIME_NORMAL_FINISH;
591 l_tx_num(l_count) := null;
592 l_tx_date(l_count) := null;
593 l_tx_type(l_count) := 'VARCHAR2';
594
595 l_count := l_count + 1;
596 l_tx_name(l_count) := 'P_TIME_NORMAL_START';
597 l_tx_char(l_count) := l_asg_rec.TIME_NORMAL_START;
598 l_tx_num(l_count) := null;
599 l_tx_date(l_count) := null;
600 l_tx_type(l_count) := 'VARCHAR2';
601
602 l_count := l_count + 1;
603 l_tx_name(l_count) := 'P_BARGAINING_UNIT_CODE';
604 l_tx_char(l_count) := l_asg_rec.BARGAINING_UNIT_CODE;
605 l_tx_num(l_count) := null;
606 l_tx_date(l_count) := null;
607 l_tx_type(l_count) := 'VARCHAR2';
608
609 l_count := l_count + 1;
610 l_tx_name(l_count) := 'P_LABOUR_UNION_MEMBER_FLAG';
611 l_tx_char(l_count) := l_asg_rec.LABOUR_UNION_MEMBER_FLAG;
612 l_tx_num(l_count) := null;
613 l_tx_date(l_count) := null;
614 l_tx_type(l_count) := 'VARCHAR2';
615
616 l_count := l_count + 1;
617 l_tx_name(l_count) := 'P_SPECIAL_CEILING_STEP_ID';
618 l_tx_char(l_count) := null;
619 l_tx_num(l_count) := l_asg_rec.SPECIAL_CEILING_STEP_ID;
620 l_tx_date(l_count) := null;
621 l_tx_type(l_count) := 'NUMBER';
622
623 l_count := l_count + 1;
624 l_tx_name(l_count) := 'P_ASSIGNMENT_STATUS_TYPE_ID';
625 l_tx_char(l_count) := null;
626 l_tx_num(l_count) := l_asg_rec.ASSIGNMENT_STATUS_TYPE_ID;
627 l_tx_date(l_count) := null;
628 l_tx_type(l_count) := 'NUMBER';
629
630
631 l_count := l_count + 1;
632 l_tx_name(l_count) := 'P_CHANGE_REASON';
633 l_tx_char(l_count) := l_asg_rec.CHANGE_REASON;
634 l_tx_num(l_count) := null;
635 l_tx_date(l_count) := null;
636 l_tx_type(l_count) := 'VARCHAR2';
637
638 l_count := l_count + 1;
639 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE_CATEGORY';
640 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE_CATEGORY;
641 l_tx_num(l_count) := null;
642 l_tx_date(l_count) := null;
643 l_tx_type(l_count) := 'VARCHAR2';
644
645 l_count := l_count + 1;
646 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE1';
647 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE1;
648 l_tx_num(l_count) := null;
649 l_tx_date(l_count) := null;
650 l_tx_type(l_count) := 'VARCHAR2';
651
652 l_count := l_count + 1;
653 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE2';
654 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE2;
655 l_tx_num(l_count) := null;
656 l_tx_date(l_count) := null;
657 l_tx_type(l_count) := 'VARCHAR2';
658
659 l_count := l_count + 1;
660 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE3';
661 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE3;
662 l_tx_num(l_count) := null;
663 l_tx_date(l_count) := null;
664 l_tx_type(l_count) := 'VARCHAR2';
665
666 l_count := l_count + 1;
667 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE4';
668 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE4;
669 l_tx_num(l_count) := null;
670 l_tx_date(l_count) := null;
671 l_tx_type(l_count) := 'VARCHAR2';
672
673 l_count := l_count + 1;
674 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE5';
675 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE5;
676 l_tx_num(l_count) := null;
677 l_tx_date(l_count) := null;
678 l_tx_type(l_count) := 'VARCHAR2';
679
680 l_count := l_count + 1;
681 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE6';
682 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE6;
683 l_tx_num(l_count) := null;
684 l_tx_date(l_count) := null;
685 l_tx_type(l_count) := 'VARCHAR2';
686
687 l_count := l_count + 1;
688 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE7';
689 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE7;
690 l_tx_num(l_count) := null;
691 l_tx_date(l_count) := null;
692 l_tx_type(l_count) := 'VARCHAR2';
693
694 l_count := l_count + 1;
695 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE8';
696 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE8;
697 l_tx_num(l_count) := null;
698 l_tx_date(l_count) := null;
699 l_tx_type(l_count) := 'VARCHAR2';
700
701 l_count := l_count + 1;
702 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE9';
703 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE9;
704 l_tx_num(l_count) := null;
705 l_tx_date(l_count) := null;
706 l_tx_type(l_count) := 'VARCHAR2';
707
708 l_count := l_count + 1;
709 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE10';
710 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE10;
711 l_tx_num(l_count) := null;
712 l_tx_date(l_count) := null;
713 l_tx_type(l_count) := 'VARCHAR2';
714
715 l_count := l_count + 1;
716 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE11';
717 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE11;
718 l_tx_num(l_count) := null;
719 l_tx_date(l_count) := null;
720 l_tx_type(l_count) := 'VARCHAR2';
721
722 l_count := l_count + 1;
723 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE12';
724 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE12;
725 l_tx_num(l_count) := null;
726 l_tx_date(l_count) := null;
727 l_tx_type(l_count) := 'VARCHAR2';
728
729 l_count := l_count + 1;
730 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE13';
731 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE13;
732 l_tx_num(l_count) := null;
733 l_tx_date(l_count) := null;
734 l_tx_type(l_count) := 'VARCHAR2';
735
736 l_count := l_count + 1;
737 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE14';
738 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE14;
739 l_tx_num(l_count) := null;
740 l_tx_date(l_count) := null;
741 l_tx_type(l_count) := 'VARCHAR2';
742
743 l_count := l_count + 1;
744 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE15';
745 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE15;
746 l_tx_num(l_count) := null;
747 l_tx_date(l_count) := null;
748 l_tx_type(l_count) := 'VARCHAR2';
749
750 l_count := l_count + 1;
751 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE16';
752 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE16;
753 l_tx_num(l_count) := null;
754 l_tx_date(l_count) := null;
755 l_tx_type(l_count) := 'VARCHAR2';
756
757 l_count := l_count + 1;
758 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE17';
759 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE17;
760 l_tx_num(l_count) := null;
761 l_tx_date(l_count) := null;
762 l_tx_type(l_count) := 'VARCHAR2';
763
764 l_count := l_count + 1;
765 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE18';
766 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE18;
767 l_tx_num(l_count) := null;
768 l_tx_date(l_count) := null;
769 l_tx_type(l_count) := 'VARCHAR2';
770
771 l_count := l_count + 1;
772 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE19';
773 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE19;
774 l_tx_num(l_count) := null;
775 l_tx_date(l_count) := null;
776 l_tx_type(l_count) := 'VARCHAR2';
777
778 l_count := l_count + 1;
779 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE20';
780 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE20;
781 l_tx_num(l_count) := null;
782 l_tx_date(l_count) := null;
783 l_tx_type(l_count) := 'VARCHAR2';
784
785 l_count := l_count + 1;
786 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE21';
787 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE21;
788 l_tx_num(l_count) := null;
789 l_tx_date(l_count) := null;
790 l_tx_type(l_count) := 'VARCHAR2';
791
792 l_count := l_count + 1;
793 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE22';
794 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE22;
795 l_tx_num(l_count) := null;
796 l_tx_date(l_count) := null;
797 l_tx_type(l_count) := 'VARCHAR2';
798
799 l_count := l_count + 1;
800 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE23';
801 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE23;
802 l_tx_num(l_count) := null;
803 l_tx_date(l_count) := null;
804 l_tx_type(l_count) := 'VARCHAR2';
805
806 l_count := l_count + 1;
807 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE24';
808 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE24;
809 l_tx_num(l_count) := null;
810 l_tx_date(l_count) := null;
811 l_tx_type(l_count) := 'VARCHAR2';
812
813 l_count := l_count + 1;
814 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE25';
815 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE25;
816 l_tx_num(l_count) := null;
817 l_tx_date(l_count) := null;
818 l_tx_type(l_count) := 'VARCHAR2';
819
820 l_count := l_count + 1;
821 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE26';
822 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE26;
823 l_tx_num(l_count) := null;
824 l_tx_date(l_count) := null;
825 l_tx_type(l_count) := 'VARCHAR2';
826
827 l_count := l_count + 1;
828 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE27';
829 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE27;
830 l_tx_num(l_count) := null;
831 l_tx_date(l_count) := null;
832 l_tx_type(l_count) := 'VARCHAR2';
833
834 l_count := l_count + 1;
835 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE28';
836 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE28;
837 l_tx_num(l_count) := null;
838 l_tx_date(l_count) := null;
839 l_tx_type(l_count) := 'VARCHAR2';
840
841 l_count := l_count + 1;
842 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE29';
843 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE29;
844 l_tx_num(l_count) := null;
845 l_tx_date(l_count) := null;
846 l_tx_type(l_count) := 'VARCHAR2';
847
848 l_count := l_count + 1;
849 l_tx_name(l_count) := 'P_ASS_ATTRIBUTE30';
850 l_tx_char(l_count) := l_asg_rec.ASS_ATTRIBUTE30;
851 l_tx_num(l_count) := null;
852 l_tx_date(l_count) := null;
853 l_tx_type(l_count) := 'VARCHAR2';
854
855 l_count := l_count + 1;
856 l_tx_name(l_count) := 'P_PEOPLE_GROUP_ID';
857 l_tx_char(l_count) := null;
858 l_tx_num(l_count) := l_asg_rec.PEOPLE_GROUP_ID;
859 l_tx_date(l_count) := null;
860 l_tx_type(l_count) := 'NUMBER';
861
862 l_count := l_count + 1;
863 l_tx_name(l_count) := 'P_SOFT_CODING_KEYFLEX_ID';
864 l_tx_char(l_count) := null;
865 l_tx_num(l_count) := l_asg_rec.SOFT_CODING_KEYFLEX_ID;
866 l_tx_date(l_count) := null;
867 l_tx_type(l_count) := 'NUMBER';
868
869 l_count := l_count + 1;
870 l_tx_name(l_count) := 'P_PAYROLL_ID';
871 l_tx_char(l_count) := null;
872 l_tx_num(l_count) := l_asg_rec.PAYROLL_ID;
873 l_tx_date(l_count) := null;
874 l_tx_type(l_count) := 'NUMBER';
875
876 l_count := l_count + 1;
877 l_tx_name(l_count) := 'P_PAY_BASIS_ID';
878 l_tx_char(l_count) := null;
879 l_tx_num(l_count) := l_asg_rec.PAY_BASIS_ID;
880 l_tx_date(l_count) := null;
881 l_tx_type(l_count) := 'NUMBER';
882
883 l_count := l_count + 1;
884 l_tx_name(l_count) := 'P_SAL_REVIEW_PERIOD';
885 l_tx_char(l_count) := null;
886 l_tx_num(l_count) := l_asg_rec.SAL_REVIEW_PERIOD;
887 l_tx_date(l_count) := null;
888 l_tx_type(l_count) := 'NUMBER';
889
890 l_count := l_count + 1;
891 l_tx_name(l_count) := 'P_SAL_REVIEW_PERIOD_FREQUENCY';
892 l_tx_char(l_count) := l_asg_rec.SAL_REVIEW_PERIOD_FREQUENCY;
893 l_tx_num(l_count) := null;
894 l_tx_date(l_count) := null;
895 l_tx_type(l_count) := 'VARCHAR2';
896
897 l_count := l_count + 1;
898 l_tx_name(l_count) := 'P_DATE_PROBATION_END';
899 l_tx_char(l_count) := null;
900 l_tx_num(l_count) := null;
901 l_tx_date(l_count) := l_asg_rec.DATE_PROBATION_END;
902 l_tx_type(l_count) := 'DATE';
903
904 l_count := l_count + 1;
905 l_tx_name(l_count) := 'P_PROBATION_PERIOD';
906 l_tx_char(l_count) := null;
907 l_tx_num(l_count) := l_asg_rec.PROBATION_PERIOD;
908 l_tx_date(l_count) := null;
909 l_tx_type(l_count) := 'NUMBER';
910
911 l_count := l_count + 1;
912 l_tx_name(l_count) := 'P_PROBATION_UNIT';
913 l_tx_char(l_count) := l_asg_rec.PROBATION_UNIT;
914 l_tx_num(l_count) := null;
915 l_tx_date(l_count) := null;
916 l_tx_type(l_count) := 'VARCHAR2';
917
918 l_count := l_count + 1;
919 l_tx_name(l_count) := 'P_NOTICE_PERIOD';
920 l_tx_char(l_count) := null;
921 l_tx_num(l_count) := l_asg_rec.NOTICE_PERIOD;
922 l_tx_date(l_count) := null;
923 l_tx_type(l_count) := 'NUMBER';
924
925 l_count := l_count + 1;
926 l_tx_name(l_count) := 'P_NOTICE_PERIOD_UOM';
927 l_tx_char(l_count) := l_asg_rec.NOTICE_PERIOD_UOM;
928 l_tx_num(l_count) := null;
929 l_tx_date(l_count) := null;
930 l_tx_type(l_count) := 'VARCHAR2';
931
932
933 l_count := l_count + 1;
934 l_tx_name(l_count) := 'P_EMPLOYEE_CATEGORY';
935 l_tx_char(l_count) := l_asg_rec.EMPLOYEE_CATEGORY;
936 l_tx_num(l_count) := null;
937 l_tx_date(l_count) := null;
938 l_tx_type(l_count) := 'VARCHAR2';
939
940 l_count := l_count + 1;
941 l_tx_name(l_count) := 'P_WORK_AT_HOME';
942 l_tx_char(l_count) := l_asg_rec.WORK_AT_HOME;
943 l_tx_num(l_count) := null;
944 l_tx_date(l_count) := null;
945 l_tx_type(l_count) := 'VARCHAR2';
946
947
948 l_count := l_count + 1;
949 l_tx_name(l_count) := 'P_JOB_POST_SOURCE_NAME';
950 l_tx_char(l_count) := l_asg_rec.JOB_POST_SOURCE_NAME;
951 l_tx_num(l_count) := null;
952 l_tx_date(l_count) := null;
953 l_tx_type(l_count) := 'VARCHAR2';
954
955 l_count := l_count + 1;
956 l_tx_name(l_count) := 'P_PERF_REVIEW_PERIOD';
957 l_tx_char(l_count) := null;
958 l_tx_num(l_count) := l_asg_rec.PERF_REVIEW_PERIOD;
959 l_tx_date(l_count) := null;
960 l_tx_type(l_count) := 'NUMBER';
961
962 l_count := l_count + 1;
963 l_tx_name(l_count) := 'P_PERF_REVIEW_PERIOD_FREQUENCY';
964 l_tx_char(l_count) := l_asg_rec.PERF_REVIEW_PERIOD_FREQUENCY;
965 l_tx_num(l_count) := null;
966 l_tx_date(l_count) := null;
967 l_tx_type(l_count) := 'VARCHAR2';
968
969
970 l_count := l_count + 1;
971 l_tx_name(l_count) := 'P_INTERNAL_ADDRESS_LINE';
972 l_tx_char(l_count) := l_asg_rec.INTERNAL_ADDRESS_LINE;
973 l_tx_num(l_count) := null;
974 l_tx_date(l_count) := null;
975 l_tx_type(l_count) := 'VARCHAR2';
976
977 l_count := l_count + 1;
978 l_tx_name(l_count) := 'P_CONTRACT_ID';
979 l_tx_char(l_count) := null;
980 l_tx_num(l_count) := l_asg_rec.CONTRACT_ID;
981 l_tx_date(l_count) := null;
982 l_tx_type(l_count) := 'NUMBER';
983
984 l_count := l_count + 1;
985 l_tx_name(l_count) := 'P_ESTABLISHMENT_ID';
986 l_tx_char(l_count) := null;
987 l_tx_num(l_count) := l_asg_rec.ESTABLISHMENT_ID;
988 l_tx_date(l_count) := null;
989 l_tx_type(l_count) := 'NUMBER';
990
991 l_count := l_count + 1;
992 l_tx_name(l_count) := 'P_COLLECTIVE_AGREEMENT_ID';
993 l_tx_char(l_count) := null;
994 l_tx_num(l_count) := l_asg_rec.COLLECTIVE_AGREEMENT_ID;
995 l_tx_date(l_count) := null;
996 l_tx_type(l_count) := 'NUMBER';
997
998
999 l_count := l_count + 1;
1000 l_tx_name(l_count) := 'P_CAGR_ID_FLEX_NUM';
1001 l_tx_char(l_count) := null;
1002 l_tx_num(l_count) := l_asg_rec.CAGR_ID_FLEX_NUM;
1003 l_tx_date(l_count) := null;
1004 l_tx_type(l_count) := 'NUMBER';
1005
1006 l_count := l_count + 1;
1007 l_tx_name(l_count) := 'P_CAGR_GRADE_DEF_ID';
1008 l_tx_char(l_count) := null;
1009 l_tx_num(l_count) := l_asg_rec.CAGR_GRADE_DEF_ID;
1010 l_tx_date(l_count) := null;
1011 l_tx_type(l_count) := 'NUMBER';
1012
1013
1014
1015 l_count := l_count + 1;
1016 l_tx_name(l_count) := 'P_DEFAULT_CODE_COMB_ID';
1017 l_tx_char(l_count) := null;
1018 l_tx_num(l_count) := l_asg_rec.DEFAULT_CODE_COMB_ID;
1019 l_tx_date(l_count) := null;
1020 l_tx_type(l_count) := 'NUMBER';
1021
1022
1023 l_count := l_count + 1;
1024 l_tx_name(l_count) := 'P_SET_OF_BOOKS_ID';
1025 l_tx_char(l_count) := null;
1026 l_tx_num(l_count) := l_asg_rec.SET_OF_BOOKS_ID;
1027 l_tx_date(l_count) := null;
1028 l_tx_type(l_count) := 'NUMBER';
1029
1030
1031
1032 l_count := l_count + 1;
1033 l_tx_name(l_count) := 'P_VENDOR_ID';
1034 l_tx_char(l_count) := null;
1035 l_tx_num(l_count) := l_asg_rec.VENDOR_ID;
1036 l_tx_date(l_count) := null;
1037 l_tx_type(l_count) := 'NUMBER';
1038
1039 l_count := l_count + 1;
1040 l_tx_name(l_count) := 'P_ASSIGNMENT_TYPE';
1041 l_tx_char(l_count) := l_asg_rec.ASSIGNMENT_TYPE;
1042 l_tx_num(l_count) := null;
1043 l_tx_date(l_count) := null;
1044 l_tx_type(l_count) := 'VARCHAR2';
1045
1046
1047
1048 l_count := l_count + 1;
1049 l_tx_name(l_count) := 'P_TITLE';
1050 l_tx_char(l_count) := l_asg_rec.TITLE;
1051 l_tx_num(l_count) := null;
1052 l_tx_date(l_count) := null;
1053 l_tx_type(l_count) := 'VARCHAR2';
1054
1055 l_count := l_count + 1;
1056 l_tx_name(l_count) := 'P_PROJECT_TITLE';
1057 l_tx_char(l_count) := l_asg_rec.PROJECT_TITLE;
1058 l_tx_num(l_count) := null;
1059 l_tx_date(l_count) := null;
1060 l_tx_type(l_count) := 'VARCHAR2';
1061
1062
1063 l_count := l_count + 1;
1064 l_tx_name(l_count) := 'P_SOURCE_TYPE';
1065 l_tx_char(l_count) := l_asg_rec.SOURCE_TYPE;
1066 l_tx_num(l_count) := null;
1067 l_tx_date(l_count) := null;
1068 l_tx_type(l_count) := 'VARCHAR2';
1069
1070
1071
1072 l_count := l_count + 1;
1073 l_tx_name(l_count) := 'P_VENDOR_ASSIGNMENT_NUMBER';
1074 l_tx_char(l_count) := l_asg_rec.VENDOR_ASSIGNMENT_NUMBER;
1075 l_tx_num(l_count) := null;
1076 l_tx_date(l_count) := null;
1077 l_tx_type(l_count) := 'VARCHAR2';
1078
1079 l_count := l_count + 1;
1080 l_tx_name(l_count) := 'P_VENDOR_EMPLOYEE_NUMBER';
1081 l_tx_char(l_count) := l_asg_rec.VENDOR_EMPLOYEE_NUMBER;
1082 l_tx_num(l_count) := null;
1083 l_tx_date(l_count) := null;
1084 l_tx_type(l_count) := 'VARCHAR2';
1085
1086 If P_SAVE_MODE is not null then
1087 l_count := l_count + 1;
1088 l_tx_name(l_count) := 'P_SAVE_MODE';
1089 l_tx_char(l_count) := P_SAVE_MODE;
1090 l_tx_num(l_count) := null;
1091 l_tx_date(l_count) := null;
1092 l_tx_type(l_count) := 'VARCHAR2';
1093 else
1094 l_count := l_count + 1;
1095 l_tx_name(l_count) := 'P_SAVE_MODE';
1096 l_tx_char(l_count) := 'SAVE';
1097 l_tx_num(l_count) := null;
1098 l_tx_date(l_count) := null;
1099 l_tx_type(l_count) := 'VARCHAR2';
1100 End if;
1101
1102
1103 l_count := l_count + 1;
1104 l_tx_name(l_count) := 'P_REVIEW_PROC_CALL';
1105 l_tx_char(l_count) := 'HrAssignment';
1106 l_tx_num(l_count) := null;
1107 l_tx_date(l_count) := null;
1108 l_tx_type(l_count) := 'VARCHAR2';
1109
1110
1111 l_count := l_count + 1;
1112 l_tx_name(l_count) := 'P_REVIEW_ACTID';
1113 l_tx_char(l_count) := to_char(p_activity_id);
1114 l_tx_num(l_count) := null;
1115 l_tx_date(l_count) := null;
1116 l_tx_type(l_count) := 'VARCHAR2';
1117
1118 l_count := l_count + 1;
1119 l_tx_name(l_count) := 'P_HRS_LAST_DATE';
1120 l_tx_char(l_count) := null;
1121 l_tx_num(l_count) := null;
1122 l_tx_date(l_count) := null;
1123 l_tx_type(l_count) := 'DATE';
1124
1125 l_count := l_count + 1;
1126 l_tx_name(l_count) := 'P_DISPLAY_POS';
1127 l_tx_char(l_count) := null;
1128 l_tx_num(l_count) := null;
1129 l_tx_date(l_count) := null;
1130 l_tx_type(l_count) := 'VARCHAR2';
1131
1132 l_count := l_count + 1;
1133 l_tx_name(l_count) := 'P_DISPLAY_ORG';
1134 l_tx_char(l_count) := null;
1135 l_tx_num(l_count) := null;
1136 l_tx_date(l_count) := null;
1137 l_tx_type(l_count) := 'VARCHAR2';
1138
1139 l_count := l_count + 1;
1140 l_tx_name(l_count) := 'P_DISPLAY_JOB';
1141 l_tx_char(l_count) := null;
1142 l_tx_num(l_count) := null;
1143 l_tx_date(l_count) := null;
1144 l_tx_type(l_count) := 'VARCHAR2';
1145
1146
1147 l_count := l_count + 1;
1148 l_tx_name(l_count) := 'P_DISPLAY_ASS_STATUS';
1149 l_tx_char(l_count) := null;
1150 l_tx_num(l_count) := null;
1151 l_tx_date(l_count) := null;
1152 l_tx_type(l_count) := 'VARCHAR2';
1153
1154 If l_asg_rec.grade_id is not null then
1155 l_tx_char(l_count) := 'Y';
1156 End if;
1157
1158
1159 l_count := l_count + 1;
1160 l_tx_name(l_count) := 'P_DISPLAY_GRADE';
1161 l_tx_char(l_count) := null;
1162 l_tx_num(l_count) := null;
1163 l_tx_date(l_count) := null;
1164 l_tx_type(l_count) := 'VARCHAR2';
1165
1166
1167 l_count := l_count + 1;
1168 l_tx_name(l_count) := 'P_GRADE_LOV';
1169 l_tx_char(l_count) := null;
1170 l_tx_num(l_count) := null;
1171 l_tx_date(l_count) := null;
1172 l_tx_type(l_count) := 'VARCHAR2';
1173
1174 l_count := l_count + 1;
1175 l_tx_name(l_count) := 'P_APPROVER_ID';
1176 l_tx_char(l_count) := null;
1177 l_tx_num(l_count) := P_APPROVER_ID;
1178 l_tx_date(l_count) := null;
1179 l_tx_type(l_count) := 'NUMBER';
1180
1181 l_count := l_count + 1;
1182 l_tx_name(l_count) := 'P_GRADE_LADDER_PGM_ID';
1183 l_tx_char(l_count) := null;
1184 l_tx_num(l_count) := l_asg_rec.GRADE_LADDER_PGM_ID;
1185 l_tx_date(l_count) := null;
1186 l_tx_type(l_count) := 'NUMBER';
1187
1188 l_count := l_count + 1;
1189 l_tx_name(l_count) := 'P_PO_HEADER_ID';
1190 l_tx_char(l_count) := null;
1191 l_tx_num(l_count) := l_asg_rec.PO_HEADER_ID;
1192 l_tx_date(l_count) := null;
1193 l_tx_type(l_count) := 'NUMBER';
1194
1195 l_count := l_count + 1;
1196 l_tx_name(l_count) := 'P_PO_LINE_ID';
1197 l_tx_char(l_count) := null;
1198 l_tx_num(l_count) := l_asg_rec.PO_LINE_ID;
1199 l_tx_date(l_count) := null;
1200 l_tx_type(l_count) := 'NUMBER';
1201
1202 l_count := l_count + 1;
1203 l_tx_name(l_count) := 'P_VENDOR_SITE_ID';
1204 l_tx_char(l_count) := null;
1205 l_tx_num(l_count) := l_asg_rec.VENDOR_SITE_ID;
1206 l_tx_date(l_count) := null;
1207 l_tx_type(l_count) := 'NUMBER';
1208
1209 l_count := l_count + 1;
1210 l_tx_name(l_count) := 'P_PROJ_ASGN_END';
1211 l_tx_char(l_count) := null;
1212 l_tx_num(l_count) := null;
1213 l_tx_date(l_count) := l_asg_rec.PROJECTED_ASSIGNMENT_END;
1214 l_tx_type(l_count) := 'DATE';
1215
1216 -- Insert all other assignment values as unchanged.
1217
1218 forall i in 1..l_count
1219 insert into hr_api_transaction_values
1220 ( transaction_value_id,
1221 transaction_step_id,
1222 datatype,
1223 name,
1224 varchar2_value,
1225 number_value,
1226 date_value,
1227 original_varchar2_value,
1228 original_number_value,
1229 original_date_value)
1230 Values
1231 ( hr_api_transaction_values_s.nextval,
1232 l_transaction_step_id,
1233 l_tx_type(i),
1234 l_tx_name(i),
1235 l_tx_char(i),
1236 l_tx_num(i),
1237 l_tx_date(i),
1238 l_tx_char(i),
1239 l_tx_num(i),
1240 l_tx_date(i));
1241
1242 -- Update change in pay basis value
1243
1244 update hr_api_transaction_values
1245 set
1246 number_value = p_pay_basis_id
1247 where transaction_step_id = l_transaction_step_id
1248 and name = 'P_PAY_BASIS_ID';
1249 end if;
1250 --
1251 End;
1252 --
1253 ---------------------------------------------------------------------------------------
1254 --
1255 --
1256 PROCEDURE check_Salary_Basis_Change
1257 ( p_assignment_id in NUMBER
1258 , p_effective_date in DATE
1259 , p_item_key in varchar2
1260 , p_allow_change_date out nocopy varchar2
1261 , p_allow_basis_change out nocopy varchar2)
1262 is
1263
1264 Cursor csr_txn_basis_change_date Is
1265 select hatv1.date_value date_value
1266 from hr_api_transaction_values hatv,
1267 hr_api_transaction_steps hats,
1268 hr_api_transactions hat,
1269 hr_api_transaction_values hatv1
1270 where hatv.NAME = 'P_PAY_BASIS_ID'
1271 and hatv1.NAME = 'P_EFFECTIVE_DATE'
1272 and hatv1.TRANSACTION_STEP_ID = hats.TRANSACTION_STEP_ID
1273 and hatv.NUMBER_VALUE <> hatv.ORIGINAL_NUMBER_VALUE
1274 and hatv.TRANSACTION_STEP_ID = hats.TRANSACTION_STEP_ID
1275 and hats.TRANSACTION_ID = hat.TRANSACTION_ID
1276 and hat.ASSIGNMENT_ID = p_assignment_id
1277 and hat.ITEM_KEY = p_item_key;
1278
1279 Cursor csr_asg_basis_change_date IS
1280 select effective_start_date date_value
1281 from per_all_assignments_f
1282 where assignment_id = p_assignment_id
1283 and effective_start_date >= p_effective_date
1284 and pay_basis_id <> (Select pay_basis_id from per_all_assignments_f
1285 where assignment_id = p_assignment_id
1286 and p_effective_date between effective_start_date and effective_end_date)
1287 order by date_value desc;
1288 l_date date;
1289 Begin
1290 p_allow_change_date := 'YES';
1291 p_allow_basis_change := 'YES';
1292
1293 --hr_utility.trace_on(null, 'TIGER');
1294 --g_debug := TRUE;
1295
1296 if g_debug then
1297 hr_utility.set_location('Enter check_Salary_Basis_Change ', 1);
1298 hr_utility.set_location('p_assignment_id '||p_assignment_id, 2);
1299 hr_utility.set_location('p_effective_date: '||p_effective_date, 3);
1300 hr_utility.set_location('p_item_key '||p_item_key, 4);
1301 end if;
1302
1303 Open csr_asg_basis_change_date;
1304 Fetch csr_asg_basis_change_date into l_date;
1305 if l_date is not null then
1306 p_allow_basis_change := 'ASG_BASIS';
1307 p_allow_change_date := 'NO';
1308 if g_debug then
1309 hr_utility.set_location('ASG_BASIS ', 5);
1310 end if;
1311 return;
1312 end if;
1313 Close csr_asg_basis_change_date;
1314
1315 Open csr_txn_basis_change_date;
1316 Fetch csr_txn_basis_change_date into l_date;
1317 if l_date is not null then
1318 p_allow_basis_change := 'F_BASIS';
1319 p_allow_change_date := 'NO';
1320 if g_debug then
1321 hr_utility.set_location('F_BASIS ', 6);
1322 end if;
1323 end if;
1324 Close csr_txn_basis_change_date;
1325
1326 End check_Salary_Basis_Change;
1327 --
1328 --
1329 --
1330 PROCEDURE delete_transaction(p_assgn_id IN number,
1331 p_effective_dt IN date,
1332 p_transaction_id IN number,
1333 p_transaction_step_id IN number,
1334 p_item_key IN varchar2,
1335 p_item_type IN varchar2,
1336 p_next_change_date In date,
1337 p_changedt_curr IN date,
1338 p_changedt_last IN date default Null,
1339 p_failed_to_delete IN OUT NOCOPY varchar2,
1340 p_busgroup_id IN number)
1341 IS
1342 --
1343 cursor csr_recs_on_top(c_assignment_id number, c_change_date date) is
1344 select max(change_date)
1345 from per_pay_transactions
1346 where ASSIGNMENT_ID = c_assignment_id
1347 and change_date > c_change_date;
1348
1349 cursor csr_delete_recs(c_effective_dt date, c_assgn_id number, c_changedt_curr date, c_changedt_last date
1350 ,c_transaction_id number) is
1351 Select
1352 ppt.PAY_TRANSACTION_ID,
1353 ppt.TRANSACTION_ID,
1354 ppt.TRANSACTION_STEP_ID,
1355 ppt.ITEM_TYPE,
1356 ppt.ITEM_KEY,
1357 ppt.PAY_PROPOSAL_ID,
1358 ppt.ASSIGNMENT_ID,
1359 ppt.COMPONENT_ID,
1360 ppt.REASON,
1361 ppt.PAY_BASIS_ID,
1362 ppt.BUSINESS_GROUP_ID,
1363 ppt.CHANGE_DATE,
1364 ppt.DATE_TO,
1365 ppt.last_change_date,
1366 ppt.PROPOSED_SALARY_N,
1367 ppt.CHANGE_AMOUNT_N,
1368 ppt.CHANGE_PERCENTAGE,
1369 ppb.PAY_ANNUALIZATION_FACTOR,
1370 pet.INPUT_CURRENCY_CODE,
1371 ppt.STATUS,
1372 ppt.DML_OPERATION,
1373 'TRANSACTION' from_tab,
1374 ppt.PRIOR_PROPOSED_SALARY_N,
1375 ppt.PRIOR_PAY_BASIS_ID,
1376 ppt.ATTRIBUTE_CATEGORY,
1377 ppt.ATTRIBUTE1,
1378 ppt.ATTRIBUTE2,
1379 ppt.ATTRIBUTE3,
1380 ppt.ATTRIBUTE4,
1381 ppt.ATTRIBUTE5,
1382 ppt.ATTRIBUTE6,
1383 ppt.ATTRIBUTE7,
1384 ppt.ATTRIBUTE8,
1385 ppt.ATTRIBUTE9,
1386 ppt.ATTRIBUTE10,
1387 ppt.ATTRIBUTE11,
1388 ppt.ATTRIBUTE12,
1389 ppt.ATTRIBUTE13,
1390 ppt.ATTRIBUTE14,
1391 ppt.ATTRIBUTE15,
1392 ppt.ATTRIBUTE16,
1393 ppt.ATTRIBUTE17,
1394 ppt.ATTRIBUTE18,
1395 ppt.ATTRIBUTE19,
1396 ppt.ATTRIBUTE20,
1397 ppt.MULTIPLE_COMPONENTS,
1398 ppt.PARENT_PAY_TRANSACTION_ID,
1399 ppt.PRIOR_PAY_PROPOSAL_ID,
1400 ppt.PRIOR_PAY_TRANSACTION_ID,
1401 ppt.APPROVED,
1402 ppt.object_version_number
1403 from per_pay_transactions ppt,
1404 per_pay_bases ppb,
1405 pay_input_values_f piv,
1406 pay_element_types_f pet
1407 where ppt.assignment_id = c_assgn_id
1408 AND ppt.PARENT_PAY_TRANSACTION_ID is null
1409 AND ppt.TRANSACTION_ID = c_transaction_id
1410 AND ppt.change_date between c_changedt_last and c_changedt_curr
1411 AND ppb.pay_basis_id = ppt.pay_basis_id
1412 AND ppb.input_value_id = piv.input_value_id
1413 AND c_effective_dt BETWEEN piv.effective_start_date AND piv.effective_end_date
1414 AND piv.element_type_id = pet.element_type_id
1415 AND c_effective_dt BETWEEN pet.effective_start_date AND pet.effective_end_date
1416 AND ppt.status <> 'DELETE'
1417 Union
1418 Select
1419 null PAY_TRANSACTION_ID,
1420 null TRANSACTION_ID,
1421 null TRANSACTION_STEP_ID,
1422 null ITEM_TYPE,
1423 null ITEM_KEY,
1424 pay.PAY_PROPOSAL_ID,
1425 pay.ASSIGNMENT_ID ASSIGNMENT_ID,
1426 null COMPONENT_ID,
1427 pay.PROPOSAL_REASON REASON,
1428 paaf.PAY_BASIS_ID PAY_BASIS_ID,
1429 pay.BUSINESS_GROUP_ID,
1430 pay.CHANGE_DATE,
1431 pay.DATE_TO,
1432 pay.last_change_date,
1433 pay.PROPOSED_SALARY_N,
1434 null change_amount_n,
1435 null change_percentage,
1436 ppb.PAY_ANNUALIZATION_FACTOR,
1437 pet.INPUT_CURRENCY_CODE,
1438 null STATUS,
1439 null DML_OPERATION,
1440 'PROPOSAL' from_tab,
1441 null PRIOR_PROPOSED_SALARY_N,
1442 null PRIOR_PAY_BASIS_ID,
1443 pay.ATTRIBUTE_CATEGORY,
1444 pay.ATTRIBUTE1,
1445 pay.ATTRIBUTE2,
1446 pay.ATTRIBUTE3,
1447 pay.ATTRIBUTE4,
1448 pay.ATTRIBUTE5,
1449 pay.ATTRIBUTE6,
1450 pay.ATTRIBUTE7,
1451 pay.ATTRIBUTE8,
1452 pay.ATTRIBUTE9,
1453 pay.ATTRIBUTE10,
1454 pay.ATTRIBUTE11,
1455 pay.ATTRIBUTE12,
1456 pay.ATTRIBUTE13,
1457 pay.ATTRIBUTE14,
1458 pay.ATTRIBUTE15,
1459 pay.ATTRIBUTE16,
1460 pay.ATTRIBUTE17,
1461 pay.ATTRIBUTE18,
1462 pay.ATTRIBUTE19,
1463 pay.ATTRIBUTE20,
1464 pay.MULTIPLE_COMPONENTS,
1465 null PARENT_PAY_TRANSACTION_ID,
1466 null PRIOR_PAY_PROPOSAL_ID,
1467 null PRIOR_PAY_TRANSACTION_ID,
1468 null APPROVED,
1469 pay.object_version_number
1470 from per_pay_proposals pay,
1471 per_all_assignments_f paaf,
1472 per_pay_bases ppb,
1473 pay_input_values_f piv,
1474 pay_element_types_f pet
1475 where pay.assignment_id = c_assgn_id
1476 AND pay.change_date between c_changedt_last and c_changedt_curr
1477 AND pay.assignment_id = paaf.assignment_id
1478 and c_effective_dt BETWEEN paaf.effective_start_date AND paaf.effective_end_date
1479 --AND (p_changedt_curr BETWEEN paaf.effective_start_date AND paaf.effective_end_date
1480 -- OR p_changedt_last BETWEEN paaf.effective_start_date AND paaf.effective_end_date)
1481 AND ppb.pay_basis_id = paaf.pay_basis_id AND ppb.input_value_id = piv.input_value_id
1482 AND c_effective_dt BETWEEN piv.effective_start_date AND piv.effective_end_date
1483 AND piv.element_type_id = pet.element_type_id
1484 AND c_effective_dt BETWEEN pet.effective_start_date AND pet.effective_end_date
1485 AND pay.pay_proposal_id not in (select nvl(pay_proposal_id, -1) from per_pay_transactions
1486 where assignment_id = pay.assignment_id
1487 and TRANSACTION_ID = c_transaction_id)
1488 ORDER by change_date asc;
1489
1490 cursor csr_update_comps(c_parent_proposal_id in number) is
1491 select
1492 component_id ,
1493 pay_proposal_id ,
1494 business_group_id ,
1495 approved ,
1496 component_reason ,
1497 change_amount ,
1498 change_percentage ,
1499 comments ,
1500 new_amount ,
1501 attribute_category ,
1502 attribute1 ,
1503 attribute2 ,
1504 attribute3 ,
1505 attribute4 ,
1506 attribute5 ,
1507 attribute6 ,
1508 attribute7 ,
1509 attribute8 ,
1510 attribute9 ,
1511 attribute10 ,
1512 attribute11 ,
1513 attribute12 ,
1514 attribute13 ,
1515 attribute14 ,
1516 attribute15 ,
1517 attribute16 ,
1518 attribute17 ,
1519 attribute18 ,
1520 attribute19 ,
1521 attribute20 ,
1522 change_amount_n ,
1523 object_version_number
1524 from per_pay_proposal_components
1525 where PAY_PROPOSAL_ID = c_parent_proposal_id;
1526
1527
1528 --
1529 l_count number(3);
1530 --
1531 l_curr_date_to date;
1532 --
1533 l_seq_val Number;
1534 --
1535 l_last_rec_from varchar2(20);
1536 --
1537 l_curr_rec_from varchar2(20);
1538 --
1539 l_curr_rec_proposal_id number;
1540 --
1541 l_last_trans_id number;
1542 --
1543 l_last_row csr_delete_recs%rowtype;
1544 --
1545 l_proc varchar2(72) := g_package||'delete_transaction';
1546 --
1547 l_changedt_last date;
1548 --
1549 l_last_change_date_curr date;
1550 --
1551 l_do_delete varchar2(20);
1552 --
1553 l_failed_to_delete varchar2(2) := 'N';
1554 --
1555 begin
1556 --
1557 --hr_utility.trace_on(null, 'TIGER');
1558 --g_debug := TRUE;
1559 --
1560 if g_debug then
1561 hr_utility.set_location('Entering:'|| l_proc, 10);
1562 end if;
1563 --
1564 if g_debug then
1565 hr_utility.set_location('assgnid:'||p_assgn_id||'effDate:'||p_effective_dt||'transId:'||p_transaction_id, 10);
1566 hr_utility.set_location('transStepId:'||p_transaction_step_id||'itemKey:'||p_item_key||'itemtype:'||p_item_type, 10);
1567 hr_utility.set_location('nextChangedt:'||p_next_change_date||'currChangedt:'||p_changedt_curr||'lastChangedt:'||p_changedt_last, 10);
1568 end if;
1569 --
1570 l_count := 0;
1571 --
1572 if p_changedt_last is null then
1573 --
1574 if g_debug then
1575 hr_utility.set_location('Entering if p_changedt_last:'|| l_proc, 20);
1576 end if;
1577 --
1578 l_changedt_last := p_changedt_curr;
1579 --
1580 else
1581 --
1582 if g_debug then
1583 hr_utility.set_location('Entering else p_changedt_last:'|| l_proc, 30);
1584 end if;
1585 --
1586 l_changedt_last := p_changedt_last;
1587 --
1588 end if;
1589
1590 p_failed_to_delete := l_failed_to_delete;
1591
1592 /*
1593 --
1594 l_do_delete := check_Salary_Basis_Change(p_assgn_id,p_changedt_curr);
1595 --
1596 if l_do_delete = 'NONE' then
1597 --
1598 l_failed_to_delete := 'N';
1599 --
1600 p_failed_to_delete := l_failed_to_delete;
1601 --
1602 elsif l_do_delete = 'F_ASSIGNMENT' then
1603 --
1604 l_failed_to_delete := 'Y';
1605 --
1606 p_failed_to_delete := l_failed_to_delete;
1607 --
1608 return;
1609 --
1610 else
1611 --
1612 l_failed_to_delete := 'N';
1613 --
1614 p_failed_to_delete := l_failed_to_delete;
1615 --
1616 end if;
1617 */
1618
1619 for delete_recs in csr_delete_recs(p_effective_dt, p_assgn_id, p_changedt_curr, l_changedt_last, p_transaction_id) loop
1620 --
1621 if g_debug then
1622 hr_utility.set_location(l_proc, 40);
1623 end if;
1624 --
1625 if l_count = 0 then
1626 --
1627 if g_debug then
1628 hr_utility.set_location('Entering l_count 0:'|| l_proc, 50);
1629 end if;
1630 --
1631 --
1632 l_last_rec_from := delete_recs.from_tab;
1633 --
1634 l_last_trans_id := delete_recs.pay_transaction_id;
1635 --
1636 l_last_row := delete_recs;
1637 --
1638 if l_changedt_last = p_changedt_curr then
1639 --
1640 --
1641 if g_debug then
1642 hr_utility.set_location('Entering when last date NULL:'|| l_proc, 60);
1643 end if;
1644 --
1645 if delete_recs.from_tab = 'TRANSACTION' then
1646 --
1647 if delete_recs.pay_proposal_id is null then
1648 --
1649 delete from per_pay_transactions
1650 where parent_pay_transaction_id = delete_recs.pay_transaction_id;
1651 --
1652 delete from per_pay_transactions
1653 where pay_transaction_id = delete_recs.pay_transaction_id;
1654 --
1655 else
1656 update per_pay_transactions
1657 set STATUS = 'DELETE',
1658 DML_OPERATION = 'DELETE'
1659 where parent_pay_transaction_id = delete_recs.pay_transaction_id;
1660 --
1661 update per_pay_transactions
1662 set STATUS = 'DELETE',
1663 DML_OPERATION = 'DELETE'
1664 where pay_transaction_id = delete_recs.pay_transaction_id;
1665 --
1666 end if;
1667 --
1668 else
1669 --
1670 --
1671 if g_debug then
1672 hr_utility.set_location('Inserting when p_changedt_last NULL:'|| l_proc, 70);
1673 end if;
1674 --
1675 select PER_PAY_TRANSACTIONS_S.NEXTVAL into l_seq_val from dual;
1676 --
1677 insert into per_pay_transactions
1678 (PAY_TRANSACTION_ID ,--PAY_TRANSACTION_ID,
1679 TRANSACTION_ID, -- TRANSACTION_ID,
1680 TRANSACTION_STEP_ID,-- TRANSACTION_STEP_ID,
1681 ITEM_TYPE,-- ITEM_TYPE,
1682 ITEM_KEY,-- ITEM_KEY,
1683 PAY_PROPOSAL_ID,
1684 ASSIGNMENT_ID,-- ASSIGNMENT_ID,
1685 COMPONENT_ID,-- COMPONENT_ID,
1686 REASON,-- REASON,
1687 PAY_BASIS_ID,-- PAY_BASIS_ID,
1688 BUSINESS_GROUP_ID,
1689 CHANGE_DATE,
1690 DATE_TO,
1691 PROPOSED_SALARY_N,
1692 change_amount_n,
1693 change_percentage,
1694 STATUS,-- STATUS,
1695 DML_OPERATION,-- DML_OPERATION,
1696 PRIOR_PROPOSED_SALARY_N,-- PRIOR_PROPOSED_SALARY_N,
1697 PRIOR_PAY_BASIS_ID,-- PRIOR_PAY_BASIS_ID,
1698 ATTRIBUTE_CATEGORY,
1699 ATTRIBUTE1,
1700 ATTRIBUTE2,
1701 ATTRIBUTE3,
1702 ATTRIBUTE4,
1703 ATTRIBUTE5,
1704 ATTRIBUTE6,
1705 ATTRIBUTE7,
1706 ATTRIBUTE8,
1707 ATTRIBUTE9,
1708 ATTRIBUTE10,
1709 ATTRIBUTE11,
1710 ATTRIBUTE12,
1711 ATTRIBUTE13,
1712 ATTRIBUTE14,
1713 ATTRIBUTE15,
1714 ATTRIBUTE16,
1715 ATTRIBUTE17,
1716 ATTRIBUTE18,
1717 ATTRIBUTE19,
1718 ATTRIBUTE20,
1719 MULTIPLE_COMPONENTS,
1720 PARENT_PAY_TRANSACTION_ID,-- PARENT_PAY_TRANSACTION_ID,
1721 PRIOR_PAY_PROPOSAL_ID,-- PRIOR_PAY_PROPOSAL_ID,
1722 PRIOR_PAY_TRANSACTION_ID,-- PRIOR_PAY_TRANSACTION_ID,
1723 APPROVED, -- APPROVED
1724 object_version_number)
1725 values(l_seq_val ,--PAY_TRANSACTION_ID,
1726 p_transaction_id, -- TRANSACTION_ID,
1727 p_transaction_step_id,-- TRANSACTION_STEP_ID,
1728 p_item_type,-- ITEM_TYPE,
1729 p_item_key,-- ITEM_KEY,
1730 l_last_row.PAY_PROPOSAL_ID,
1731 l_last_row.ASSIGNMENT_ID,-- ASSIGNMENT_ID,
1732 l_last_row.COMPONENT_ID,
1733 l_last_row.REASON,-- REASON,
1734 l_last_row.PAY_BASIS_ID,-- PAY_BASIS_ID,
1735 l_last_row.BUSINESS_GROUP_ID,
1736 l_last_row.CHANGE_DATE,
1737 l_curr_date_to, --update last recs date_to to curr_rec
1738 l_last_row.PROPOSED_SALARY_N,-- proposed_salary_n,
1739 l_last_row.change_amount_n, -- change_amount_n,
1740 l_last_row.change_percentage,-- change_percentage,
1741 'DELETE',-- STATUS,
1742 'DELETE',-- DML_OPERATION,
1743 l_last_row.PRIOR_PROPOSED_SALARY_N,
1744 l_last_row.PRIOR_PAY_BASIS_ID,
1745 l_last_row.ATTRIBUTE_CATEGORY,
1746 l_last_row.ATTRIBUTE1,
1747 l_last_row.ATTRIBUTE2,
1748 l_last_row.ATTRIBUTE3,
1749 l_last_row.ATTRIBUTE4,
1750 l_last_row.ATTRIBUTE5,
1751 l_last_row.ATTRIBUTE6,
1752 l_last_row.ATTRIBUTE7,
1753 l_last_row.ATTRIBUTE8,
1754 l_last_row.ATTRIBUTE9,
1755 l_last_row.ATTRIBUTE10,
1756 l_last_row.ATTRIBUTE11,
1757 l_last_row.ATTRIBUTE12,
1758 l_last_row.ATTRIBUTE13,
1759 l_last_row.ATTRIBUTE14,
1760 l_last_row.ATTRIBUTE15,
1761 l_last_row.ATTRIBUTE16,
1762 l_last_row.ATTRIBUTE17,
1763 l_last_row.ATTRIBUTE18,
1764 l_last_row.ATTRIBUTE19,
1765 l_last_row.ATTRIBUTE20,
1766 l_last_row.MULTIPLE_COMPONENTS,
1767 l_last_row.PARENT_PAY_TRANSACTION_ID,
1768 l_last_row.PRIOR_PAY_PROPOSAL_ID,
1769 l_last_row.PRIOR_PAY_TRANSACTION_ID,
1770 l_last_row.APPROVED,
1771 l_last_row.OBJECT_VERSION_NUMBER);
1772 --
1773 if l_last_row.multiple_components = 'Y' then
1774 --
1775 for rec_update_comps in csr_update_comps(l_last_row.pay_proposal_id) loop
1776 --
1777 insert into per_pay_transactions
1778 (PAY_TRANSACTION_ID ,--PAY_TRANSACTION_ID,
1779 TRANSACTION_ID, -- TRANSACTION_ID,
1780 TRANSACTION_STEP_ID,-- TRANSACTION_STEP_ID,
1781 ITEM_TYPE,-- ITEM_TYPE,
1782 ITEM_KEY,-- ITEM_KEY,
1783 PAY_PROPOSAL_ID,
1784 ASSIGNMENT_ID,-- ASSIGNMENT_ID,
1785 COMPONENT_ID,-- COMPONENT_ID,
1786 REASON,-- REASON,
1787 PAY_BASIS_ID,-- PAY_BASIS_ID,
1788 BUSINESS_GROUP_ID,
1789 CHANGE_DATE,
1790 DATE_TO,
1791 PROPOSED_SALARY_N,
1792 change_amount_n,
1793 change_percentage,
1794 STATUS,-- STATUS,
1795 DML_OPERATION,-- DML_OPERATION,
1796 COMMENTS,
1797 PRIOR_PROPOSED_SALARY_N,-- PRIOR_PROPOSED_SALARY_N,
1798 PRIOR_PAY_BASIS_ID,-- PRIOR_PAY_BASIS_ID,
1799 ATTRIBUTE_CATEGORY,
1800 ATTRIBUTE1,
1801 ATTRIBUTE2,
1802 ATTRIBUTE3,
1803 ATTRIBUTE4,
1804 ATTRIBUTE5,
1805 ATTRIBUTE6,
1806 ATTRIBUTE7,
1807 ATTRIBUTE8,
1808 ATTRIBUTE9,
1809 ATTRIBUTE10,
1810 ATTRIBUTE11,
1811 ATTRIBUTE12,
1812 ATTRIBUTE13,
1813 ATTRIBUTE14,
1814 ATTRIBUTE15,
1815 ATTRIBUTE16,
1816 ATTRIBUTE17,
1817 ATTRIBUTE18,
1818 ATTRIBUTE19,
1819 ATTRIBUTE20,
1820 MULTIPLE_COMPONENTS,
1821 PARENT_PAY_TRANSACTION_ID,-- PARENT_PAY_TRANSACTION_ID,
1822 PRIOR_PAY_PROPOSAL_ID,-- PRIOR_PAY_PROPOSAL_ID,
1823 PRIOR_PAY_TRANSACTION_ID,-- PRIOR_PAY_TRANSACTION_ID,
1824 APPROVED,
1825 object_version_number
1826 )
1827 values(PER_PAY_TRANSACTIONS_S.NEXTVAL ,--PAY_TRANSACTION_ID,
1828 p_transaction_id, -- TRANSACTION_ID,
1829 p_transaction_step_id,-- TRANSACTION_STEP_ID,
1830 p_item_type,-- ITEM_TYPE,
1831 p_item_key,-- ITEM_KEY,
1832 rec_update_comps.PAY_PROPOSAL_ID,
1833 l_last_row.ASSIGNMENT_ID,-- ASSIGNMENT_ID,
1834 rec_update_comps.COMPONENT_ID,
1835 rec_update_comps.component_reason,-- REASON,
1836 l_last_row.PAY_BASIS_ID,-- PAY_BASIS_ID,
1837 l_last_row.BUSINESS_GROUP_ID,
1838 null,
1839 null, --update last recs date_to to curr_rec
1840 null,-- proposed_salary_n,
1841 rec_update_comps.CHANGE_AMOUNT_N,-- change_amount_n,
1842 rec_update_comps.CHANGE_PERCENTAGE, -- change_percentage,
1843 'DELETE',-- STATUS,
1844 'DELETE',-- DML_OPERATION,
1845 rec_update_comps.comments,
1846 null, --
1847 null, --l_last_row.PRIOR_PAY_BASIS_ID,
1848 rec_update_comps.ATTRIBUTE_CATEGORY,
1849 rec_update_comps.ATTRIBUTE1,
1850 rec_update_comps.ATTRIBUTE2,
1851 rec_update_comps.ATTRIBUTE3,
1852 rec_update_comps.ATTRIBUTE4,
1853 rec_update_comps.ATTRIBUTE5,
1854 rec_update_comps.ATTRIBUTE6,
1855 rec_update_comps.ATTRIBUTE7,
1856 rec_update_comps.ATTRIBUTE8,
1857 rec_update_comps.ATTRIBUTE9,
1858 rec_update_comps.ATTRIBUTE10,
1859 rec_update_comps.ATTRIBUTE11,
1860 rec_update_comps.ATTRIBUTE12,
1861 rec_update_comps.ATTRIBUTE13,
1862 rec_update_comps.ATTRIBUTE14,
1863 rec_update_comps.ATTRIBUTE15,
1864 rec_update_comps.ATTRIBUTE16,
1865 rec_update_comps.ATTRIBUTE17,
1866 rec_update_comps.ATTRIBUTE18,
1867 rec_update_comps.ATTRIBUTE19,
1868 rec_update_comps.ATTRIBUTE20,
1869 null, --l_last_row.MULTIPLE_COMPONENTS,
1870 l_seq_val, --l_last_row.PARENT_PAY_TRANSACTION_ID,
1871 null, --l_last_row.PRIOR_PAY_PROPOSAL_ID,
1872 null, --l_last_row.PRIOR_PAY_TRANSACTION_ID,
1873 rec_update_comps.APPROVED,
1874 rec_update_comps.OBJECT_VERSION_NUMBER
1875 );
1876 end loop;
1877 --
1878 end if;
1879 --
1880 end if;
1881 --
1882 end if;
1883 --
1884 elsif l_count = 1 then
1885 --
1886 if g_debug then
1887 hr_utility.set_location('Entering l_count 1:'|| l_proc, 80);
1888 end if;
1889 --
1890 l_curr_rec_from := delete_recs.from_tab;
1891 --
1892 l_curr_date_to := delete_recs.date_to;
1893 --
1894 l_curr_rec_proposal_id := delete_recs.pay_proposal_id;
1895 --
1896 if l_last_rec_from = 'TRANSACTION' then
1897 --
1898 if g_debug then
1899 hr_utility.set_location('Entering last rec TRANS:'|| l_proc, 90);
1900 end if;
1901 --
1902 --
1903 --update the last record with current recs date_to
1904 update per_pay_transactions
1905 set date_to = l_curr_date_to
1906 where pay_transaction_id = l_last_trans_id;
1907 --
1908 else
1909 --
1910 --
1911 if g_debug then
1912 hr_utility.set_location('Inserting last rec from PROPO:'|| l_proc, 120);
1913 end if;
1914 --
1915 --
1916 select PER_PAY_TRANSACTIONS_S.NEXTVAL into l_seq_val from dual;--replace by Seq number
1917 --
1918 insert into per_pay_transactions
1919 (PAY_TRANSACTION_ID ,--PAY_TRANSACTION_ID,
1920 TRANSACTION_ID, -- TRANSACTION_ID,
1921 TRANSACTION_STEP_ID,-- TRANSACTION_STEP_ID,
1922 ITEM_TYPE,-- ITEM_TYPE,
1923 ITEM_KEY,-- ITEM_KEY,
1924 PAY_PROPOSAL_ID,
1925 ASSIGNMENT_ID,-- ASSIGNMENT_ID,
1926 COMPONENT_ID,-- COMPONENT_ID,
1927 REASON,-- REASON,
1928 PAY_BASIS_ID,-- PAY_BASIS_ID,
1929 BUSINESS_GROUP_ID,
1930 CHANGE_DATE,
1931 DATE_TO,
1932 last_change_date,
1933 PROPOSED_SALARY_N,
1934 change_amount_n,
1935 change_percentage,
1936 STATUS,-- STATUS,
1937 DML_OPERATION,-- DML_OPERATION,
1938 PRIOR_PROPOSED_SALARY_N,-- PRIOR_PROPOSED_SALARY_N,
1939 PRIOR_PAY_BASIS_ID,-- PRIOR_PAY_BASIS_ID,
1940 ATTRIBUTE_CATEGORY,
1941 ATTRIBUTE1,
1942 ATTRIBUTE2,
1943 ATTRIBUTE3,
1944 ATTRIBUTE4,
1945 ATTRIBUTE5,
1946 ATTRIBUTE6,
1947 ATTRIBUTE7,
1948 ATTRIBUTE8,
1949 ATTRIBUTE9,
1950 ATTRIBUTE10,
1951 ATTRIBUTE11,
1952 ATTRIBUTE12,
1953 ATTRIBUTE13,
1954 ATTRIBUTE14,
1955 ATTRIBUTE15,
1956 ATTRIBUTE16,
1957 ATTRIBUTE17,
1958 ATTRIBUTE18,
1959 ATTRIBUTE19,
1960 ATTRIBUTE20,
1961 MULTIPLE_COMPONENTS,
1962 PARENT_PAY_TRANSACTION_ID,-- PARENT_PAY_TRANSACTION_ID,
1963 PRIOR_PAY_PROPOSAL_ID,-- PRIOR_PAY_PROPOSAL_ID,
1964 PRIOR_PAY_TRANSACTION_ID,-- PRIOR_PAY_TRANSACTION_ID,
1965 APPROVED, -- APPROVED
1966 object_version_number)
1967 values(l_seq_val ,--PAY_TRANSACTION_ID,
1968 p_transaction_id, -- TRANSACTION_ID,
1969 p_transaction_step_id,-- TRANSACTION_STEP_ID,
1970 p_item_type,-- ITEM_TYPE,
1971 p_item_key,-- ITEM_KEY,
1972 l_last_row.PAY_PROPOSAL_ID,
1973 l_last_row.ASSIGNMENT_ID,-- ASSIGNMENT_ID,
1974 l_last_row.COMPONENT_ID,
1975 l_last_row.REASON,-- REASON,
1976 l_last_row.PAY_BASIS_ID,-- PAY_BASIS_ID,
1977 l_last_row.BUSINESS_GROUP_ID,
1978 l_last_row.CHANGE_DATE,
1979 l_curr_date_to, --update last recs date_to to curr_rec
1980 l_last_row.last_change_date,
1981 l_last_row.PROPOSED_SALARY_N, -- proposed_salary_n,
1982 l_last_row.change_amount_n, -- change_amount_n,
1983 l_last_row.change_percentage, -- change_percentage,
1984 'DATE_ADJUSTED',-- STATUS,
1985 'UPDATE',-- DML_OPERATION,
1986 l_last_row.PRIOR_PROPOSED_SALARY_N,
1987 l_last_row.PRIOR_PAY_BASIS_ID,
1988 l_last_row.ATTRIBUTE_CATEGORY,
1989 l_last_row.ATTRIBUTE1,
1990 l_last_row.ATTRIBUTE2,
1991 l_last_row.ATTRIBUTE3,
1992 l_last_row.ATTRIBUTE4,
1993 l_last_row.ATTRIBUTE5,
1994 l_last_row.ATTRIBUTE6,
1995 l_last_row.ATTRIBUTE7,
1996 l_last_row.ATTRIBUTE8,
1997 l_last_row.ATTRIBUTE9,
1998 l_last_row.ATTRIBUTE10,
1999 l_last_row.ATTRIBUTE11,
2000 l_last_row.ATTRIBUTE12,
2001 l_last_row.ATTRIBUTE13,
2002 l_last_row.ATTRIBUTE14,
2003 l_last_row.ATTRIBUTE15,
2004 l_last_row.ATTRIBUTE16,
2005 l_last_row.ATTRIBUTE17,
2006 l_last_row.ATTRIBUTE18,
2007 l_last_row.ATTRIBUTE19,
2008 l_last_row.ATTRIBUTE20,
2009 l_last_row.MULTIPLE_COMPONENTS,
2010 l_last_row.PARENT_PAY_TRANSACTION_ID,
2011 l_last_row.PRIOR_PAY_PROPOSAL_ID,
2012 l_last_row.PRIOR_PAY_TRANSACTION_ID,
2013 l_last_row.APPROVED,
2014 l_last_row.OBJECT_VERSION_NUMBER
2015 );
2016 if l_last_row.MULTIPLE_COMPONENTS = 'Y' then
2017 --
2018 for rec_update_comps in csr_update_comps(l_last_row.pay_proposal_id) loop
2019 insert into per_pay_transactions
2020 (PAY_TRANSACTION_ID ,--PAY_TRANSACTION_ID,
2021 TRANSACTION_ID, -- TRANSACTION_ID,
2022 TRANSACTION_STEP_ID,-- TRANSACTION_STEP_ID,
2023 ITEM_TYPE,-- ITEM_TYPE,
2024 ITEM_KEY,-- ITEM_KEY,
2025 PAY_PROPOSAL_ID,
2026 ASSIGNMENT_ID,-- ASSIGNMENT_ID,
2027 COMPONENT_ID,-- COMPONENT_ID,
2028 REASON,-- REASON,
2029 PAY_BASIS_ID,-- PAY_BASIS_ID,
2030 BUSINESS_GROUP_ID,
2031 CHANGE_DATE,
2032 DATE_TO,
2033 PROPOSED_SALARY_N,
2034 change_amount_n,
2035 change_percentage,
2036 STATUS,-- STATUS,
2037 DML_OPERATION,-- DML_OPERATION,
2038 COMMENTS,
2039 PRIOR_PROPOSED_SALARY_N,-- PRIOR_PROPOSED_SALARY_N,
2040 PRIOR_PAY_BASIS_ID,-- PRIOR_PAY_BASIS_ID,
2041 ATTRIBUTE_CATEGORY,
2042 ATTRIBUTE1,
2043 ATTRIBUTE2,
2044 ATTRIBUTE3,
2045 ATTRIBUTE4,
2046 ATTRIBUTE5,
2047 ATTRIBUTE6,
2048 ATTRIBUTE7,
2049 ATTRIBUTE8,
2050 ATTRIBUTE9,
2051 ATTRIBUTE10,
2052 ATTRIBUTE11,
2053 ATTRIBUTE12,
2054 ATTRIBUTE13,
2055 ATTRIBUTE14,
2056 ATTRIBUTE15,
2057 ATTRIBUTE16,
2058 ATTRIBUTE17,
2059 ATTRIBUTE18,
2060 ATTRIBUTE19,
2061 ATTRIBUTE20,
2062 MULTIPLE_COMPONENTS,
2063 PARENT_PAY_TRANSACTION_ID,-- PARENT_PAY_TRANSACTION_ID,
2064 PRIOR_PAY_PROPOSAL_ID,-- PRIOR_PAY_PROPOSAL_ID,
2065 PRIOR_PAY_TRANSACTION_ID,-- PRIOR_PAY_TRANSACTION_ID,
2066 APPROVED,
2067 object_version_number
2068 )
2069 values(PER_PAY_TRANSACTIONS_S.NEXTVAL ,--PAY_TRANSACTION_ID,
2070 p_transaction_id, -- TRANSACTION_ID,
2071 p_transaction_step_id,-- TRANSACTION_STEP_ID,
2072 p_item_type,-- ITEM_TYPE,
2073 p_item_key,-- ITEM_KEY,
2074 rec_update_comps.PAY_PROPOSAL_ID,
2075 l_last_row.ASSIGNMENT_ID,-- ASSIGNMENT_ID,
2076 rec_update_comps.COMPONENT_ID,
2077 rec_update_comps.component_reason,-- REASON,
2078 l_last_row.PAY_BASIS_ID,-- PAY_BASIS_ID,
2079 l_last_row.BUSINESS_GROUP_ID,
2080 null,
2081 null, --update last recs date_to to curr_rec
2082 null,-- proposed_salary_n,
2083 rec_update_comps.CHANGE_AMOUNT_N,-- change_amount_n,
2084 rec_update_comps.CHANGE_PERCENTAGE, -- change_percentage,
2085 'DATE_ADJUSTED',-- STATUS,
2086 'UPDATE',-- DML_OPERATION,
2087 rec_update_comps.comments,
2088 null, --
2089 null, --l_last_row.PRIOR_PAY_BASIS_ID,
2090 rec_update_comps.ATTRIBUTE_CATEGORY,
2091 rec_update_comps.ATTRIBUTE1,
2092 rec_update_comps.ATTRIBUTE2,
2093 rec_update_comps.ATTRIBUTE3,
2094 rec_update_comps.ATTRIBUTE4,
2095 rec_update_comps.ATTRIBUTE5,
2096 rec_update_comps.ATTRIBUTE6,
2097 rec_update_comps.ATTRIBUTE7,
2098 rec_update_comps.ATTRIBUTE8,
2099 rec_update_comps.ATTRIBUTE9,
2100 rec_update_comps.ATTRIBUTE10,
2101 rec_update_comps.ATTRIBUTE11,
2102 rec_update_comps.ATTRIBUTE12,
2103 rec_update_comps.ATTRIBUTE13,
2104 rec_update_comps.ATTRIBUTE14,
2105 rec_update_comps.ATTRIBUTE15,
2106 rec_update_comps.ATTRIBUTE16,
2107 rec_update_comps.ATTRIBUTE17,
2108 rec_update_comps.ATTRIBUTE18,
2109 rec_update_comps.ATTRIBUTE19,
2110 rec_update_comps.ATTRIBUTE20,
2111 null, --l_last_row.MULTIPLE_COMPONENTS,
2112 l_seq_val, --l_last_row.PARENT_PAY_TRANSACTION_ID,
2113 null, --l_last_row.PRIOR_PAY_PROPOSAL_ID,
2114 null, --l_last_row.PRIOR_PAY_TRANSACTION_ID,
2115 rec_update_comps.APPROVED,
2116 rec_update_comps.OBJECT_VERSION_NUMBER
2117 );
2118 end loop;
2119 --
2120 end if;
2121 --
2122 end if;
2123 --
2124 --if curr rec to be deleted is from Trans
2125 if delete_recs.from_tab = 'TRANSACTION' then
2126 --
2127 --
2128 if g_debug then
2129 hr_utility.set_location('Entering curr rec from TRANS:'|| l_proc, 100);
2130 end if;
2131 --
2132
2133 if delete_recs.pay_proposal_id is null then
2134 --
2135 l_last_change_date_curr := delete_recs.last_change_date;
2136 --
2137 delete from per_pay_transactions
2138 where parent_pay_transaction_id = delete_recs.pay_transaction_id;
2139 --
2140 delete from per_pay_transactions
2141 where pay_transaction_id = delete_recs.pay_transaction_id;
2142 --
2143 else
2144 --
2145 l_last_change_date_curr := delete_recs.last_change_date;
2146 --
2147 update per_pay_transactions
2148 set STATUS = 'DELETE',
2149 DML_OPERATION = 'DELETE'
2150 where parent_pay_transaction_id = delete_recs.pay_transaction_id;
2151 --
2152 update per_pay_transactions
2153 set STATUS = 'DELETE',
2154 DML_OPERATION = 'DELETE'
2155 where pay_transaction_id = delete_recs.pay_transaction_id;
2156
2157 end if;
2158 --
2159 else
2160 --
2161 select PER_PAY_TRANSACTIONS_S.NEXTVAL into l_seq_val from dual; --replace by Seq number
2162 --
2163 --
2164 if g_debug then
2165 hr_utility.set_location('Inserting curr rec PROPOSAL:'|| l_proc, 110);
2166 end if;
2167 --
2168 l_last_change_date_curr := delete_recs.last_change_date;
2169 --
2170 insert into per_pay_transactions
2171 (PAY_TRANSACTION_ID ,--PAY_TRANSACTION_ID,
2172 TRANSACTION_ID, -- TRANSACTION_ID,
2173 TRANSACTION_STEP_ID,-- TRANSACTION_STEP_ID,
2174 ITEM_TYPE,-- ITEM_TYPE,
2175 ITEM_KEY,-- ITEM_KEY,
2176 PAY_PROPOSAL_ID,
2177 ASSIGNMENT_ID,-- ASSIGNMENT_ID,
2178 COMPONENT_ID,-- COMPONENT_ID,
2179 REASON,-- REASON,
2180 PAY_BASIS_ID,-- PAY_BASIS_ID,
2181 BUSINESS_GROUP_ID,
2182 CHANGE_DATE,
2183 DATE_TO,
2184 last_change_date,
2185 PROPOSED_SALARY_N,
2186 change_amount_n,
2187 change_percentage,
2188 STATUS,-- STATUS,
2189 DML_OPERATION,-- DML_OPERATION,
2190 PRIOR_PROPOSED_SALARY_N,-- PRIOR_PROPOSED_SALARY_N,
2191 PRIOR_PAY_BASIS_ID,-- PRIOR_PAY_BASIS_ID,
2192 ATTRIBUTE_CATEGORY,
2193 ATTRIBUTE1,
2194 ATTRIBUTE2,
2195 ATTRIBUTE3,
2196 ATTRIBUTE4,
2197 ATTRIBUTE5,
2198 ATTRIBUTE6,
2199 ATTRIBUTE7,
2200 ATTRIBUTE8,
2201 ATTRIBUTE9,
2202 ATTRIBUTE10,
2203 ATTRIBUTE11,
2204 ATTRIBUTE12,
2205 ATTRIBUTE13,
2206 ATTRIBUTE14,
2207 ATTRIBUTE15,
2208 ATTRIBUTE16,
2209 ATTRIBUTE17,
2210 ATTRIBUTE18,
2211 ATTRIBUTE19,
2212 ATTRIBUTE20,
2213 MULTIPLE_COMPONENTS,
2214 PARENT_PAY_TRANSACTION_ID,-- PARENT_PAY_TRANSACTION_ID,
2215 PRIOR_PAY_PROPOSAL_ID,-- PRIOR_PAY_PROPOSAL_ID,
2216 PRIOR_PAY_TRANSACTION_ID,-- PRIOR_PAY_TRANSACTION_ID,
2217 APPROVED,-- APPROVED
2218 object_version_number)
2219 values(l_seq_val ,--PAY_TRANSACTION_ID,
2220 p_transaction_id, -- TRANSACTION_ID,
2221 p_transaction_step_id,-- TRANSACTION_STEP_ID,
2222 p_item_type,-- ITEM_TYPE,
2223 p_item_key,-- ITEM_KEY,
2224 delete_recs.PAY_PROPOSAL_ID,
2225 delete_recs.ASSIGNMENT_ID,-- ASSIGNMENT_ID,
2226 delete_recs.COMPONENT_ID,
2227 delete_recs.REASON,-- REASON,
2228 delete_recs.PAY_BASIS_ID,-- PAY_BASIS_ID,
2229 delete_recs.BUSINESS_GROUP_ID,
2230 delete_recs.CHANGE_DATE,
2231 delete_recs.DATE_TO,
2232 delete_recs.last_change_date,
2233 delete_recs.PROPOSED_SALARY_N,
2234 delete_recs.change_amount_n,
2235 delete_recs.change_percentage,
2236 'DELETE',-- STATUS,
2237 'DELETE',-- DML_OPERATION,
2238 delete_recs.PRIOR_PROPOSED_SALARY_N,
2239 delete_recs.PRIOR_PAY_BASIS_ID,
2240 delete_recs.ATTRIBUTE_CATEGORY,
2241 delete_recs.ATTRIBUTE1,
2242 delete_recs.ATTRIBUTE2,
2243 delete_recs.ATTRIBUTE3,
2244 delete_recs.ATTRIBUTE4,
2245 delete_recs.ATTRIBUTE5,
2246 delete_recs.ATTRIBUTE6,
2247 delete_recs.ATTRIBUTE7,
2248 delete_recs.ATTRIBUTE8,
2249 delete_recs.ATTRIBUTE9,
2250 delete_recs.ATTRIBUTE10,
2251 delete_recs.ATTRIBUTE11,
2252 delete_recs.ATTRIBUTE12,
2253 delete_recs.ATTRIBUTE13,
2254 delete_recs.ATTRIBUTE14,
2255 delete_recs.ATTRIBUTE15,
2256 delete_recs.ATTRIBUTE16,
2257 delete_recs.ATTRIBUTE17,
2258 delete_recs.ATTRIBUTE18,
2259 delete_recs.ATTRIBUTE19,
2260 delete_recs.ATTRIBUTE20,
2261 delete_recs.MULTIPLE_COMPONENTS,
2262 delete_recs.PARENT_PAY_TRANSACTION_ID,
2263 delete_recs.PRIOR_PAY_PROPOSAL_ID,
2264 delete_recs.PRIOR_PAY_TRANSACTION_ID,
2265 delete_recs.APPROVED,
2266 delete_recs.OBJECT_VERSION_NUMBER
2267 );
2268 --
2269 if delete_recs.MULTIPLE_COMPONENTS = 'Y' then
2270 --
2271 for rec_update_comps in csr_update_comps(delete_recs.pay_proposal_id) loop
2272 insert into per_pay_transactions
2273 (PAY_TRANSACTION_ID ,--PAY_TRANSACTION_ID,
2274 TRANSACTION_ID, -- TRANSACTION_ID,
2275 TRANSACTION_STEP_ID,-- TRANSACTION_STEP_ID,
2276 ITEM_TYPE,-- ITEM_TYPE,
2277 ITEM_KEY,-- ITEM_KEY,
2278 PAY_PROPOSAL_ID,
2279 ASSIGNMENT_ID,-- ASSIGNMENT_ID,
2280 COMPONENT_ID,-- COMPONENT_ID,
2281 REASON,-- REASON,
2282 PAY_BASIS_ID,-- PAY_BASIS_ID,
2283 BUSINESS_GROUP_ID,
2284 CHANGE_DATE,
2285 DATE_TO,
2286 PROPOSED_SALARY_N,
2287 change_amount_n,
2288 change_percentage,
2289 STATUS,-- STATUS,
2290 DML_OPERATION,-- DML_OPERATION,
2291 COMMENTS,
2292 PRIOR_PROPOSED_SALARY_N,-- PRIOR_PROPOSED_SALARY_N,
2293 PRIOR_PAY_BASIS_ID,-- PRIOR_PAY_BASIS_ID,
2294 ATTRIBUTE_CATEGORY,
2295 ATTRIBUTE1,
2296 ATTRIBUTE2,
2297 ATTRIBUTE3,
2298 ATTRIBUTE4,
2299 ATTRIBUTE5,
2300 ATTRIBUTE6,
2301 ATTRIBUTE7,
2302 ATTRIBUTE8,
2303 ATTRIBUTE9,
2304 ATTRIBUTE10,
2305 ATTRIBUTE11,
2306 ATTRIBUTE12,
2307 ATTRIBUTE13,
2308 ATTRIBUTE14,
2309 ATTRIBUTE15,
2310 ATTRIBUTE16,
2311 ATTRIBUTE17,
2312 ATTRIBUTE18,
2313 ATTRIBUTE19,
2314 ATTRIBUTE20,
2315 MULTIPLE_COMPONENTS,
2316 PARENT_PAY_TRANSACTION_ID,-- PARENT_PAY_TRANSACTION_ID,
2317 PRIOR_PAY_PROPOSAL_ID,-- PRIOR_PAY_PROPOSAL_ID,
2318 PRIOR_PAY_TRANSACTION_ID,-- PRIOR_PAY_TRANSACTION_ID,
2319 APPROVED,
2320 object_version_number
2321 )
2322 values(PER_PAY_TRANSACTIONS_S.NEXTVAL ,--PAY_TRANSACTION_ID,
2323 p_transaction_id, -- TRANSACTION_ID,
2324 p_transaction_step_id,-- TRANSACTION_STEP_ID,
2325 p_item_type,-- ITEM_TYPE,
2326 p_item_key,-- ITEM_KEY,
2327 rec_update_comps.PAY_PROPOSAL_ID,
2328 delete_recs.ASSIGNMENT_ID,-- ASSIGNMENT_ID,
2329 rec_update_comps.COMPONENT_ID,
2330 rec_update_comps.component_reason,-- REASON,
2331 delete_recs.PAY_BASIS_ID,-- PAY_BASIS_ID,
2332 delete_recs.BUSINESS_GROUP_ID,
2333 null,
2334 null, --update last recs date_to to curr_rec
2335 null,-- proposed_salary_n,
2336 rec_update_comps.CHANGE_AMOUNT_N,-- change_amount_n,
2337 rec_update_comps.CHANGE_PERCENTAGE, -- change_percentage,
2338 'DELETE',-- STATUS,
2339 'DELETE',-- DML_OPERATION,
2340 rec_update_comps.comments,
2341 null, --
2342 null, --l_last_row.PRIOR_PAY_BASIS_ID,
2343 rec_update_comps.ATTRIBUTE_CATEGORY,
2344 rec_update_comps.ATTRIBUTE1,
2345 rec_update_comps.ATTRIBUTE2,
2346 rec_update_comps.ATTRIBUTE3,
2347 rec_update_comps.ATTRIBUTE4,
2348 rec_update_comps.ATTRIBUTE5,
2349 rec_update_comps.ATTRIBUTE6,
2350 rec_update_comps.ATTRIBUTE7,
2351 rec_update_comps.ATTRIBUTE8,
2352 rec_update_comps.ATTRIBUTE9,
2353 rec_update_comps.ATTRIBUTE10,
2354 rec_update_comps.ATTRIBUTE11,
2355 rec_update_comps.ATTRIBUTE12,
2356 rec_update_comps.ATTRIBUTE13,
2357 rec_update_comps.ATTRIBUTE14,
2358 rec_update_comps.ATTRIBUTE15,
2359 rec_update_comps.ATTRIBUTE16,
2360 rec_update_comps.ATTRIBUTE17,
2361 rec_update_comps.ATTRIBUTE18,
2362 rec_update_comps.ATTRIBUTE19,
2363 rec_update_comps.ATTRIBUTE20,
2364 null, --l_last_row.MULTIPLE_COMPONENTS,
2365 l_seq_val, --l_last_row.PARENT_PAY_TRANSACTION_ID,
2366 null, --l_last_row.PRIOR_PAY_PROPOSAL_ID,
2367 null, --l_last_row.PRIOR_PAY_TRANSACTION_ID,
2368 rec_update_comps.APPROVED,
2369 rec_update_comps.OBJECT_VERSION_NUMBER
2370 );
2371 end loop;
2372 --
2373 end if;
2374 --
2375 end if;
2376 --
2377 end if;
2378 --
2379 --
2380 if g_debug then
2381 hr_utility.set_location('Incrementing l_count'|| l_proc, 55);
2382 end if;
2383 --
2384 l_count := l_count + 1;
2385 --
2386 end loop;
2387 --
2388 update_transaction(p_assgn_id, p_transaction_id, l_changedt_last,l_last_change_date_curr, p_busgroup_id);
2389 --
2390 --
2391 open csr_recs_on_top(p_assgn_id, l_last_row.change_date);
2392 fetch csr_recs_on_top into l_changedt_last;
2393 close csr_recs_on_top;
2394 --
2395 --Delete the only record from transaction
2396 --if it comes from pay_proposal
2397 --and there are no records on top of it in DELETE status
2398 --
2399 if l_last_rec_from = 'TRANSACTION'
2400 and l_curr_rec_from = 'TRANSACTION'
2401 and l_curr_rec_proposal_id is null
2402 and p_next_change_date is null
2403 and l_changedt_last is null
2404 and l_last_row.pay_proposal_id is not null
2405 and l_last_row.status = 'DATE_ADJUSTED' then
2406 --
2407 delete from per_pay_transactions
2408 where PAY_TRANSACTION_ID = l_last_row.PAY_TRANSACTION_ID;
2409 --
2410 end if;
2411 --
2412 End delete_transaction;
2413 --
2414 --
2415 --
2416 function update_component_transaction(p_pay_transaction_id Number
2417 ,p_ASSIGNMENT_ID Number
2418 ,p_change_date date
2419 ,p_prior_proposed_salary Number default Null
2420 ,p_prior_proposal_id Number default Null
2421 ,p_prior_transaction_id Number default Null
2422 ,p_prior_pay_basis_id Number default Null
2423 ,p_update_prior varchar2 default 'N'
2424 ,p_xchg_rate in Number
2425 )
2426 return Number
2427 IS
2428 cursor csr_update_comp(p_pay_transaction_id number,p_ASSIGNMENT_ID number,p_change_date date)
2429 IS
2430 Select
2431 ppt.pay_transaction_id,
2432 ppt.PROPOSED_SALARY_N,
2433 ppt.CHANGE_AMOUNT_N,
2434 ppt.CHANGE_PERCENTAGE
2435 from per_pay_transactions ppt
2436 where ppt.PARENT_PAY_TRANSACTION_ID = p_pay_transaction_id
2437 AND ppt.assignment_id = p_ASSIGNMENT_ID
2438 --AND ppt.change_date = p_change_date
2439 AND ppt.status <> 'DELETE';
2440 --
2441 l_change_amount_comp number;
2442 --
2443 l_proc varchar2(72) := g_package||'update_component_transaction';
2444 --
2445 begin
2446 --
2447 if g_debug then
2448 hr_utility.set_location('Entering:'|| l_proc, 10);
2449 end if;
2450 --
2451 --
2452 --hr_utility.trace_on(null, 'TIGER');
2453 --g_debug := TRUE;
2454 --
2455 l_change_amount_comp := 0 ;
2456 --
2457 for update_comp_recs in csr_update_comp (p_pay_transaction_id,
2458 p_ASSIGNMENT_ID,
2459 p_change_date) loop
2460 --
2461 --computing the change amount for each component and storing it
2462 l_change_amount_comp := l_change_amount_comp + (update_comp_recs.change_percentage * p_prior_proposed_salary*p_xchg_rate/100);
2463 --
2464 --
2465 if g_debug then
2466 hr_utility.set_location('Entering:l_change amount'||l_change_amount_comp||l_proc, 10);
2467 hr_utility.set_location('Entering:prior PROPOSED_SALARY_N'||p_prior_proposed_salary||l_proc, 10);
2468 end if;
2469 --
2470 if p_update_prior = 'Y' then
2471 --
2472 --
2473 if g_debug then
2474 hr_utility.set_location('Entering: prior Update:p_prior_transaction_id'||p_prior_transaction_id, 10);
2475 end if;
2476 --
2477 update per_pay_transactions
2478 set change_amount_n = (update_comp_recs.change_percentage * p_prior_proposed_salary*p_xchg_rate/100),
2479 PRIOR_PROPOSED_SALARY_N = p_prior_proposed_salary,
2480 PRIOR_PAY_PROPOSAL_ID = p_prior_proposal_id,
2481 PRIOR_PAY_TRANSACTION_ID = p_prior_transaction_id,
2482 PRIOR_PAY_BASIS_ID = p_prior_pay_basis_id
2483 where PAY_TRANSACTION_ID = update_comp_recs.PAY_TRANSACTION_ID
2484 --and change_date = p_change_date
2485 and assignment_id = p_ASSIGNMENT_ID;
2486 --
2487 else
2488 --
2489 if g_debug then
2490 hr_utility.set_location('Entering: Else of prior Update'||l_proc, 10);
2491 end if;
2492 --
2493 update per_pay_transactions
2494 set change_amount_n = (update_comp_recs.change_percentage * p_prior_proposed_salary*p_xchg_rate/100)
2495 where PAY_TRANSACTION_ID = update_comp_recs.PAY_TRANSACTION_ID
2496 --and change_date = p_change_date
2497 and assignment_id = p_ASSIGNMENT_ID;
2498 --
2499 end if;
2500 --
2501 end loop;
2502 --
2503 return l_change_amount_comp;
2504 --
2505 end update_component_transaction;
2506 --
2507 --
2508 --
2509 PROCEDURE update_transaction(p_assgn_id IN number,
2510 p_transaction_id IN number,
2511 p_changedate_curr IN date,
2512 p_last_change_date IN date,
2513 p_busgroup_id IN number)
2514 IS
2515 cursor csr_update_recs(c_assgn_id number, c_changedate_curr date, c_transaction_id number) is
2516 --cursor to fetch data from transactions which needs to be updated
2517 Select
2518 ppt.pay_transaction_id,
2519 ppt.pay_proposal_id,
2520 ppt.pay_basis_id,
2521 ppt.assignment_id,
2522 ppt.change_date,
2523 ppt.last_change_date,
2524 ppt.MULTIPLE_COMPONENTS,
2525 ppt.PROPOSED_SALARY_N,
2526 ppt.CHANGE_AMOUNT_N,
2527 ppt.CHANGE_PERCENTAGE,
2528 ppt.PRIOR_PROPOSED_SALARY_N,
2529 ppt.PRIOR_PAY_BASIS_ID,
2530 ppt.PARENT_PAY_TRANSACTION_ID,
2531 ppt.PRIOR_PAY_PROPOSAL_ID,
2532 ppt.PRIOR_PAY_TRANSACTION_ID,
2533 pet.input_currency_code,
2534 ppt.object_version_number
2535 from per_pay_transactions ppt,
2536 per_pay_bases ppb,
2537 pay_input_values_f piv,
2538 pay_element_types_f pet
2539 where ppt.assignment_id = c_assgn_id
2540 AND ppt.PARENT_PAY_TRANSACTION_ID is null
2541 AND ppt.TRANSACTION_ID = c_transaction_id
2542 AND ppt.change_date >= c_changedate_curr
2543 AND ppb.pay_basis_id = ppt.pay_basis_id
2544 AND ppb.input_value_id = piv.input_value_id
2545 AND ppt.change_date BETWEEN piv.effective_start_date AND piv.effective_end_date
2546 AND piv.element_type_id = pet.element_type_id
2547 AND ppt.change_date BETWEEN pet.effective_start_date AND pet.effective_end_date
2548 AND ppt.status <> 'DELETE'
2549 --where ppt.assignment_id = c_assgn_id
2550 -- AND ppt.TRANSACTION_ID = c_transaction_id
2551 -- AND ppt.change_date >= c_changedate_curr
2552 -- AND ppt.status <> 'DELETE'
2553 -- AND ppt.PARENT_PAY_TRANSACTION_ID is null
2554 order by change_date asc;
2555 --
2556 l_count number(3);
2557 --
2558 l_last_rec_from varchar2(20);
2559 --
2560 l_prior_trans_id number;
2561 --
2562 l_prior_proposal_id number;
2563 --
2564 l_prior_proposed_sal number;
2565 --
2566 l_prior_pay_basis_id number;
2567 --
2568 l_change_amount number;
2569 --
2570 l_last_change_date date;
2571 --
2572 l_update_rec csr_update_recs%rowtype;
2573 --
2574 l_proc varchar2(72) := g_package||'update_transaction';
2575 --
2576 l_xchg_rate number;
2577 --
2578 l_last_currency varchar2(10);
2579 --
2580 begin
2581 --
2582 if g_debug then
2583 hr_utility.set_location('Entering:'|| l_proc, 10);
2584 end if;
2585 --
2586 --
2587 --hr_utility.trace_on(null, 'TIGER');
2588 --g_debug := TRUE;
2589 --
2590 l_count := 0;
2591 --
2592 l_change_amount := 0;
2593 --
2594 l_prior_proposed_sal := 0;
2595 --
2596 for update_recs in csr_update_recs(p_assgn_id, p_changedate_curr, p_transaction_id) loop
2597 --
2598 if g_debug then
2599 hr_utility.set_location(l_proc, 25);
2600 end if;
2601 --
2602 l_change_amount := 0;
2603 --
2604 if l_count = 0 then
2605 --
2606 if g_debug then
2607 hr_utility.set_location(l_proc||'l_count 0', 25);
2608 end if;
2609 --
2610 --
2611 l_prior_trans_id := update_recs.pay_transaction_id;
2612 --
2613 l_prior_proposal_id := update_recs.pay_proposal_id;
2614 --
2615 l_prior_proposed_sal := update_recs.PROPOSED_SALARY_N;
2616 --
2617 l_prior_pay_basis_id := update_recs.pay_basis_id;
2618 --
2619 l_last_change_date := update_recs.change_date;
2620 --
2621 l_last_currency := update_recs.input_currency_code;
2622 --
2623 if p_last_change_date is null then
2624 --
2625 if g_debug then
2626 hr_utility.set_location('when last_change_date is null'||l_proc, 30);
2627 hr_utility.set_location('l_prior_trans_id'||l_prior_trans_id||l_proc, 30);
2628 end if;
2629 --need to change prior record as well
2630 --when deleting only rec from PPP
2631 update per_pay_transactions
2632 set CHANGE_PERCENTAGE = null,
2633 CHANGE_AMOUNT_N = 0
2634 where parent_pay_transaction_id = l_prior_trans_id;
2635 --
2636 update per_pay_transactions
2637 set CHANGE_AMOUNT_N = l_prior_proposed_sal,
2638 CHANGE_PERCENTAGE = null,
2639 last_change_date = null,
2640 PRIOR_PAY_PROPOSAL_ID = null,
2641 PRIOR_PAY_TRANSACTION_ID = null,
2642 PRIOR_PROPOSED_SALARY_N = 0
2643 -- PRIOR_PAY_BASIS_ID = null
2644 where pay_transaction_id = l_prior_trans_id;
2645 --
2646 end if;
2647 --
2648 elsif l_count = 1 then
2649 --immediate record to last record
2650 --to be updated in case of UPD/DEL
2651 --
2652 if g_debug then
2653 hr_utility.set_location(l_proc||'l_count 1', 25);
2654 end if;
2655 --
2656 if update_recs.MULTIPLE_COMPONENTS = 'N' then
2657 --
2658 if g_debug then
2659 hr_utility.set_location('No MULTIPLE_COMPONENTS '||l_prior_proposed_sal||l_proc, 25);
2660 end if;
2661 --
2662 if l_last_currency <> update_recs.input_currency_code then
2663 select PER_SALADMIN_UTILITY.get_currency_rate(l_last_currency,update_recs.input_currency_code,update_recs.change_date,p_busgroup_id) into l_xchg_rate
2664 from dual;
2665 else
2666 l_xchg_rate := 1;
2667 end if;
2668 --
2669 --
2670 --update only the % , change_amount remains same
2671 update per_pay_transactions
2672 set PRIOR_PROPOSED_SALARY_N = l_prior_proposed_sal,
2673 PRIOR_PAY_PROPOSAL_ID = l_prior_proposal_id,
2674 PRIOR_PAY_TRANSACTION_ID = l_prior_trans_id,
2675 PRIOR_PAY_BASIS_ID = l_prior_pay_basis_id,
2676 last_change_date = l_last_change_date,
2677 CHANGE_PERCENTAGE = round(((update_recs.proposed_salary_n - (l_prior_proposed_sal*l_xchg_rate))/(l_prior_proposed_sal*l_xchg_rate) * 100), 6),
2678 CHANGE_AMOUNT_N = (update_recs.proposed_salary_n - (l_prior_proposed_sal*l_xchg_rate))
2679 where PAY_TRANSACTION_ID = update_recs.PAY_TRANSACTION_ID;
2680 --
2681 exit;
2682 --
2683 else
2684 --
2685 if l_last_currency <> update_recs.input_currency_code then
2686 select PER_SALADMIN_UTILITY.get_currency_rate(l_last_currency,update_recs.input_currency_code,update_recs.change_date,p_busgroup_id) into l_xchg_rate
2687 from dual;
2688 else
2689 l_xchg_rate := 1;
2690 end if;
2691 --
2692 --
2693 --
2694 --calculate change amount when Components exists
2695 l_change_amount := update_component_transaction(update_recs.pay_transaction_id,
2696 update_recs.ASSIGNMENT_ID,
2697 update_recs.change_date,
2698 l_prior_proposed_sal,
2699 l_prior_proposal_id,
2700 l_prior_trans_id,
2701 l_prior_pay_basis_id,
2702 'Y',
2703 l_xchg_rate);
2704
2705 --
2706 if g_debug then
2707 hr_utility.set_location('l_change_amt'||l_change_amount, 25);
2708 end if;
2709 ----
2710 --Update only change amount , % remains same
2711 update per_pay_transactions
2712 set PRIOR_PROPOSED_SALARY_N = l_prior_proposed_sal,
2713 PRIOR_PAY_PROPOSAL_ID = l_prior_proposal_id,
2714 PRIOR_PAY_TRANSACTION_ID = l_prior_trans_id,
2715 PRIOR_PAY_BASIS_ID = l_prior_pay_basis_id,
2716 last_change_date = l_last_change_date,
2717 PROPOSED_SALARY_N = (l_prior_proposed_sal*l_xchg_rate+l_change_amount),
2718 change_amount_n = l_change_amount,
2719 CHANGE_PERCENTAGE = round((l_change_amount/(l_prior_proposed_sal*l_xchg_rate) * 100), 6)
2720 where PAY_TRANSACTION_ID = update_recs.PAY_TRANSACTION_ID;
2721 end if;
2722 --
2723 --update change amount for next iteration
2724 l_prior_proposed_sal := l_prior_proposed_sal*l_xchg_rate + l_change_amount;
2725 --
2726 elsif l_count > 1 then
2727 --
2728 --
2729 if g_debug then
2730 hr_utility.set_location(l_proc||'l_count :'||l_count, 25);
2731 end if;
2732 --
2733 if update_recs.MULTIPLE_COMPONENTS = 'N' then
2734 --
2735 if g_debug then
2736 hr_utility.set_location(l_proc||'No MULTIPLE_COMPONENTS'||l_count, 25);
2737 end if;
2738 --
2739 if l_last_currency <> update_recs.input_currency_code then
2740 select PER_SALADMIN_UTILITY.get_currency_rate(l_last_currency,update_recs.input_currency_code,update_recs.change_date,p_busgroup_id) into l_xchg_rate
2741 from dual;
2742 else
2743 l_xchg_rate := 1;
2744 end if;
2745 --
2746 --
2747 --update only the % , change_amount: ProposedSal remains same
2748 update per_pay_transactions
2749 set CHANGE_PERCENTAGE = round(((update_recs.proposed_salary_n - (l_prior_proposed_sal*l_xchg_rate))/(l_prior_proposed_sal*l_xchg_rate) * 100), 6),
2750 CHANGE_AMOUNT_N = (update_recs.proposed_salary_n - (l_prior_proposed_sal*l_xchg_rate))
2751 where PAY_TRANSACTION_ID = update_recs.PAY_TRANSACTION_ID;
2752 --
2753 exit;
2754 --
2755 else
2756 --
2757 if g_debug then
2758 hr_utility.set_location('Multiple Comp'||l_proc, 25);
2759 end if;
2760 --
2761 if l_last_currency <> update_recs.input_currency_code then
2762 select PER_SALADMIN_UTILITY.get_currency_rate(l_last_currency,update_recs.input_currency_code,update_recs.change_date,p_busgroup_id) into l_xchg_rate
2763 from dual;
2764 else
2765 l_xchg_rate := 1;
2766 end if;
2767 --
2768 --
2769 --calculate change amount when Components exists
2770 l_change_amount := update_component_transaction(update_recs.pay_transaction_id,
2771 update_recs.ASSIGNMENT_ID,
2772 update_recs.change_date,
2773 l_prior_proposed_sal,
2774 l_prior_proposal_id,
2775 l_prior_trans_id,
2776 l_prior_pay_basis_id,
2777 'Y',
2778 l_xchg_rate);
2779
2780 --
2781 --Update only change amount, proposedSal: % remains same
2782 update per_pay_transactions
2783 set PROPOSED_SALARY_N = (PRIOR_PROPOSED_SALARY_N*l_xchg_rate + l_change_amount),
2784 CHANGE_AMOUNT_N = l_change_amount,
2785 CHANGE_PERCENTAGE = round((l_change_amount/(prior_proposed_salary_n*l_xchg_rate)*100), 6)
2786 where PAY_TRANSACTION_ID = update_recs.PAY_TRANSACTION_ID;
2787 --
2788 --
2789 end if;
2790 --
2791 --
2792 --update change amount for next iteration
2793 l_prior_proposed_sal := l_prior_proposed_sal + l_change_amount;
2794 --
2795 end if;
2796 --
2797 l_count := l_count + 1;
2798 --
2799 end loop;
2800 --
2801 End update_transaction;
2802 --
2803 --
2804 --
2805 Procedure rollback_transactions(p_assignment_id in Number,
2806 p_item_type in varchar2,
2807 p_item_key in varchar2,
2808 p_status OUT NOCOPY varchar2)
2809 IS
2810 cursor csr_rows_to_be_deleted(c_item_type in varchar2, c_item_key in varchar2, c_assgn_id in number) is
2811 select trans.pay_basis_id,
2812 trans.pay_transaction_id
2813 from per_pay_transactions trans,
2814 hr_api_transaction_steps tr_steps,
2815 hr_api_transaction_values tr_values,
2816 hr_api_transaction_values tr_values2
2817 where trans.assignment_id = c_assgn_id
2818 and trans.item_type = c_item_type
2819 and trans.item_key = c_item_key
2820 and tr_steps.item_type = c_item_type
2821 and tr_steps.item_key = c_item_key
2822 and tr_steps . api_name = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API'
2823 and tr_values.TRANSACTION_STEP_ID = tr_steps.transaction_step_id
2824 and tr_values2.TRANSACTION_STEP_ID = tr_steps.TRANSACTION_STEP_ID
2825 and tr_values.name = 'P_EFFECTIVE_DATE'
2826 and tr_values.date_value between trans.change_date and trans.date_to
2827 and tr_values2.name = 'P_PAY_BASIS_ID'
2828 and tr_values2.number_value <> trans.pay_basis_id;
2829 --
2830 --
2831 cursor csr_chk_diff_in_asgn(c_item_type in varchar2, c_item_key in varchar2, c_assgn_id in number) is
2832 select trans.pay_basis_id
2833 from per_pay_transactions trans,
2834 per_all_assignments_f asg
2835 where trans.assignment_id = c_assgn_id
2836 and trans.item_type = c_item_type
2837 and trans.item_key = c_item_key
2838 and asg.assignment_id = trans.assignment_id
2839 and asg.pay_basis_id <> trans.pay_basis_id
2840 and trans.change_date between asg.effective_start_date and asg.effective_end_date
2841 and not exists ( select '1'
2842 from hr_api_transaction_steps
2843 where api_name = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API'
2844 and item_type = c_item_type
2845 and item_key = c_item_key );
2846 --
2847 l_pay_basis_id number;
2848 --
2849 l_pay_trans_id number;
2850 --
2851 l_proc varchar2(72) := g_package||'rollback_transaction';
2852 --
2853 Begin
2854 --
2855 p_status := 'N';
2856 --
2857 if g_debug then
2858 hr_utility.set_location('Entering:'|| l_proc, 10);
2859 end if;
2860 --
2861 open csr_rows_to_be_deleted(p_item_type, p_item_key, p_assignment_id);
2862 fetch csr_rows_to_be_deleted into l_pay_basis_id, l_pay_trans_id;
2863 --
2864 if (csr_rows_to_be_deleted%found AND l_pay_trans_id is not null) then
2865 --
2866 delete from per_pay_transactions
2867 where item_key = p_item_key
2868 and item_type = p_item_type;
2869 --
2870 p_status := 'Y';
2871 --
2872 else
2873 --
2874 open csr_chk_diff_in_asgn(p_item_type, p_item_key, p_assignment_id);
2875 fetch csr_chk_diff_in_asgn into l_pay_basis_id;
2876 --
2877 if(csr_chk_diff_in_asgn%found AND l_pay_basis_id is not null) then
2878 --
2879 delete from per_pay_transactions
2880 where item_key = p_item_key
2881 and item_type = p_item_type;
2882 --
2883 p_status := 'Y';
2884 --
2885 end if;
2886 --
2887 close csr_chk_diff_in_asgn;
2888 --
2889 end if;
2890 --
2891 close csr_rows_to_be_deleted;
2892 --
2893 end rollback_transactions;
2894 --
2895 --
2896 --
2897 Procedure get_transaction_step
2898 (p_item_type in varchar2,
2899 p_item_key in varchar2,
2900 p_activity_id in number,
2901 p_login_person_id in number,
2902 p_api_name in varchar2,
2903 p_transaction_id out nocopy number,
2904 p_transaction_step_id out nocopy number,
2905 p_update_mode out nocopy varchar2,
2906 p_effective_date_option in varchar2)
2907 IS
2908
2909 l_update_mode boolean;
2910 l_transaction_id number;
2911 l_transaction_step_id number;
2912
2913 begin
2914
2915 get_pay_transaction(
2916 p_item_type => p_item_type,
2917 p_item_key => p_item_key,
2918 p_activity_id => p_activity_id,
2919 p_login_person_id => p_login_person_id,
2920 p_api_name => p_api_name,
2921 p_effective_date_option => p_effective_date_option,
2922 p_transaction_id => l_transaction_id,
2923 p_transaction_step_id => l_transaction_step_id,
2924 p_update_mode => l_update_mode);
2925
2926 if l_update_mode then
2927 p_update_mode:='Y';
2928 else
2929 p_update_mode:='N';
2930 end if;
2931
2932 p_transaction_id := l_transaction_id;
2933 p_transaction_step_id := l_transaction_step_id;
2934
2935 end get_transaction_step;
2936
2937 ---------------------- get_pay_transaction --------------------------------------
2938 --
2939 Procedure get_pay_transaction
2940 (p_item_type in varchar2,
2941 p_item_key in varchar2,
2942 p_activity_id in number,
2943 p_login_person_id in number,
2944 p_api_name in varchar2,
2945 p_effective_date_option in varchar2 default null,
2946 p_transaction_id out nocopy number,
2947 p_transaction_step_id out nocopy number,
2948 p_update_mode out nocopy boolean) IS
2949 --
2950 cursor csr_txn_step is
2951 select hats.transaction_step_id
2952 from hr_api_transaction_steps hats
2953 where hats.item_type = p_item_type
2954 and hats.item_key = p_item_key
2955 -- and hats.activity_id = p_activity_id
2956 and hats.api_name = upper(p_api_name)
2957 order by hats.transaction_step_id;
2958 --
2959 l_transaction_id number := null;
2960 l_transaction_step_id number := null;
2961 l_result varchar2(100);
2962 l_trans_obj_vers_num number;
2963 l_processing_order number := 1;
2964 --
2965 l_tx_name t_tx_name;
2966 l_tx_char t_tx_char;
2967 l_tx_num t_tx_num;
2968 l_tx_date t_tx_date;
2969 l_tx_type t_tx_type;
2970 --
2971 l_proc varchar2(61) := 'get_pay_transaction' ;
2972 --
2973 Begin
2974 --
2975 hr_utility.set_location('Entering '||l_proc,10);
2976 --
2977 p_update_mode := true;
2978 -- get the transaction id
2979 l_transaction_id := hr_transaction_ss.get_transaction_id
2980 (p_item_type => p_item_type
2981 ,p_item_key => p_item_key);
2982
2983 -- if it is not available create it.
2984 if l_transaction_id is null then
2985 hr_transaction_ss.start_transaction
2986 (itemtype => p_item_type
2987 ,itemkey => p_item_key
2988 ,actid => p_activity_id
2989 ,funmode => 'RUN'
2990 ,p_login_person_id => p_login_person_id
2991 ,result => l_result);
2992 --
2993
2994 l_transaction_id := hr_transaction_ss.get_transaction_id
2995 (p_item_type => p_item_type
2996 ,p_item_key => p_item_key);
2997 end if;
2998 --
2999 -- get the transaction_step_id
3000 --
3001 Open csr_txn_step;
3002 Fetch csr_txn_step into l_transaction_step_id;
3003 Close csr_txn_step;
3004 --
3005 if upper(p_api_name) = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API' then
3006 l_processing_order := 1;
3007 else
3008 l_processing_order := 5;
3009 end if;
3010 --
3011 -- if it is not available, create it.
3012 if l_transaction_step_id is null then
3013 --
3014 hr_transaction_api.create_trans_step
3015 (p_validate => false
3016 ,p_creator_person_id => p_login_person_id
3017 ,p_transaction_id => l_transaction_id
3018 ,p_api_name => upper(p_api_name)
3019 ,p_api_display_name => upper(p_api_name)
3020 ,p_item_type => p_item_type
3021 ,p_item_key => p_item_key
3022 ,p_activity_id => p_activity_id
3023 ,p_processing_order => l_processing_order
3024 ,p_transaction_step_id => l_transaction_step_id
3025 ,p_object_version_number => l_trans_obj_vers_num);
3026 --
3027 p_update_mode := false;
3028 --
3029 if upper(p_api_name) = 'PER_SSHR_CHANGE_PAY.PROCESS_API' then
3030 --
3031 l_tx_name(1) := 'P_REVIEW_ACTID';
3032 l_tx_char(1) := to_char(p_activity_id);
3033 l_tx_num(1) := null;
3034 l_tx_date(1) := null;
3035 l_tx_type(1) := 'VARCHAR2';
3036
3037 l_tx_name(2) := 'P_REVIEW_PROC_CALL';
3038 l_tx_char(2) := 'HrChangePay';
3039 l_tx_num(2) := null;
3040 l_tx_date(2) := null;
3041 l_tx_type(2) := 'VARCHAR2';
3042 --
3043 forall i in 1..2
3044 insert into hr_api_transaction_values
3045 ( transaction_value_id,
3046 transaction_step_id,
3047 datatype,
3048 name,
3049 varchar2_value,
3050 number_value,
3051 date_value,
3052 original_varchar2_value,
3053 original_number_value,
3054 original_date_value)
3055 Values
3056 ( hr_api_transaction_values_s.nextval,
3057 l_transaction_step_id,
3058 l_tx_type(i),
3059 l_tx_name(i),
3060 l_tx_char(i),
3061 l_tx_num(i),
3062 l_tx_date(i),
3063 l_tx_char(i),
3064 l_tx_num(i),
3065 l_tx_date(i));
3066 --
3067 End if;
3068 end if;
3069 --
3070 p_transaction_id := l_transaction_id;
3071 p_transaction_step_id := l_transaction_step_id;
3072 --
3073 hr_utility.set_location('Leaving '||l_proc,99);
3074 exception
3075 when others then
3076 hr_utility.set_location('Exception Raised',420);
3077 raise;
3078 End get_pay_transaction;
3079 --
3080 ---------------------- process_salary_basis_change --------------------------------------
3081 --
3082 Procedure process_salary_basis_change(
3083 p_transaction_step_id in number) IS
3084 --
3085 --
3086 Cursor csr_sel_item is
3087 Select transaction_step_id,api_name
3088 from hr_api_transaction_steps
3089 where transaction_id = (Select transaction_id
3090 from hr_api_transaction_steps
3091 Where transaction_step_id = p_transaction_step_id)
3092 and api_name = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API';
3093 --
3094 --
3095 l_proc varchar2(61) := 'process_salary_basis_change' ;
3096 --
3097 Begin
3098 --
3099 hr_utility.set_location('Entering '||l_proc,10);
3100 --
3101 for csr_sel in csr_sel_item loop
3102 --
3103 hr_transaction_ss.process_web_api_call
3104 (p_transaction_step_id => csr_sel.transaction_step_id
3105 ,p_api_name => csr_sel.api_name
3106 ,p_validate => false);
3107 end loop;
3108 --
3109 --
3110 hr_utility.set_location('Leaving '||l_proc,99);
3111 exception
3112 when others then
3113 hr_utility.set_location('Exception Raised',420);
3114 raise;
3115 End process_salary_basis_change;
3116 --
3117 --
3118 ---------------------- process_create_pay_action --------------------------------------
3119 --
3120 Procedure process_create_pay_action(
3121 p_transaction_step_id in number,
3122 p_new_hire_flag in varchar2 default null) IS
3123 --
3124 Cursor csr_insert_pay is
3125 Select * from per_pay_transactions
3126 where transaction_step_id = p_transaction_step_id
3127 and dml_operation = 'INSERT'
3128 and PARENT_PAY_TRANSACTION_ID is null
3129 order by CHANGE_DATE;
3130 --
3131 Cursor csr_insert_comp is
3132 Select * from per_pay_transactions
3133 where transaction_step_id = p_transaction_step_id
3134 and dml_operation = 'INSERT'
3135 and PARENT_PAY_TRANSACTION_ID is not null
3136 order by PARENT_PAY_TRANSACTION_ID;
3137 --
3138 Cursor csr_sel_item is
3139 Select item_type,item_key
3140 from hr_api_transaction_steps
3141 where transaction_step_id = p_transaction_step_id;
3142
3143 --
3144 Cursor csr_eff_date is
3145 Select TRANSACTION_EFFECTIVE_DATE, EFFECTIVE_DATE_OPTION
3146 From hr_api_transactions
3147 Where transaction_id = (Select transaction_id from hr_api_transaction_steps where transaction_step_id = p_transaction_step_id);
3148 --
3149 --
3150 l_pay_proposal_id per_pay_proposals.pay_proposal_id%type;
3151 l_pay_ovn per_pay_proposals.object_version_number%type;
3152 l_component_id per_pay_proposal_components.component_id%type;
3153 l_comp_ovn per_pay_proposal_components.object_version_number%type;
3154 l_change_date per_pay_proposals.change_date%type;
3155 l_element_entry_id pay_element_entries_f.element_entry_id%type;
3156 l_inv_next_sal_date_warning boolean;
3157 l_proposed_salary_warning boolean;
3158 l_approved_warning boolean;
3159 l_payroll_warning boolean;
3160 l_assignment_id per_all_assignments_f.assignment_id%type;
3161 l_g_assignment_id per_all_assignments_f.assignment_id%type := null;
3162 --
3163 l_proc varchar2(61) := 'process_create_pay_action' ;
3164 l_item_type hr_api_transaction_steps.item_type%type;
3165 l_item_key hr_api_transaction_steps.item_key%type;
3166 --
3167 --
3168 l_transaction_effective_date hr_api_transactions.TRANSACTION_EFFECTIVE_DATE%type;
3169 l_effective_date_option hr_api_transactions.EFFECTIVE_DATE_OPTION%type;
3170 --
3171 Begin
3172 --
3173 hr_utility.set_location('Entering '||l_proc,10);
3174 --
3175 IF nvl(p_new_hire_flag,'N') = 'Y' THEN
3176 --
3177 hr_utility.set_location(l_proc,95);
3178 Open csr_sel_item;
3179 Fetch csr_sel_item into l_item_type,l_item_key;
3180 Close csr_sel_item;
3181 --
3182 hr_new_user_reg_ss.process_selected_transaction
3183 (p_item_type => l_item_type,
3184 p_item_key => l_item_key);
3185 END IF;
3186 --
3187 --
3188 Open csr_eff_date;
3189 Fetch csr_eff_date into l_transaction_effective_date,l_effective_date_option;
3190 Close csr_eff_date;
3191 --
3192 IF (( hr_process_person_ss.g_assignment_id is not null) and
3193 (hr_process_person_ss.g_session_id= ICX_SEC.G_SESSION_ID)) THEN
3194 --
3195 -- Set the Assignment Id to the one just created, don't use the
3196 -- transaction table.
3197
3198 l_g_assignment_id := hr_process_person_ss.g_assignment_id;
3199 hr_utility.set_location('Getting global assignment id = ' ||to_char(l_g_assignment_id),20);
3200 --
3201 END IF;
3202 --
3203 -- query insert pay actions.
3204 --
3205 For l_pay_rec in csr_insert_pay loop
3206 --
3207 If l_g_assignment_id is not null THEN
3208 l_assignment_id := l_g_assignment_id;
3209 else
3210 l_assignment_id := l_pay_rec.assignment_id;
3211 End if;
3212 --
3213 If nvl(l_effective_date_option,'E') = 'A' then
3214 l_change_date := trunc(l_transaction_effective_date);
3215 Else
3216 l_change_date := l_pay_rec.change_date;
3217 End if;
3218 --
3219 --
3220 -- Insert salary proposal record.
3221 --
3222 hr_maintain_proposal_api.insert_salary_proposal(
3223 p_pay_proposal_id => l_pay_proposal_id,
3224 p_assignment_id => l_assignment_id,
3225 p_business_group_id => l_pay_rec.business_group_id,
3226 p_change_date => l_change_date,
3227 p_comments => l_pay_rec.comments,
3228 p_next_sal_review_date => l_pay_rec.next_sal_review_date,
3229 p_proposal_reason => l_pay_rec.reason,
3230 p_proposed_salary_n => l_pay_rec.proposed_salary_n,
3231 p_date_to => l_pay_rec.date_to ,
3232 p_attribute_category => l_pay_rec.attribute_category,
3233 p_attribute1 => l_pay_rec.attribute1,
3234 p_attribute2 => l_pay_rec.attribute2,
3235 p_attribute3 => l_pay_rec.attribute3,
3236 p_attribute4 => l_pay_rec.attribute4,
3237 p_attribute5 => l_pay_rec.attribute5,
3238 p_attribute6 => l_pay_rec.attribute6,
3239 p_attribute7 => l_pay_rec.attribute7,
3240 p_attribute8 => l_pay_rec.attribute8,
3241 p_attribute9 => l_pay_rec.attribute9,
3242 p_attribute10 => l_pay_rec.attribute10,
3243 p_attribute11 => l_pay_rec.attribute11,
3244 p_attribute12 => l_pay_rec.attribute12,
3245 p_attribute13 => l_pay_rec.attribute13,
3246 p_attribute14 => l_pay_rec.attribute14,
3247 p_attribute15 => l_pay_rec.attribute15,
3248 p_attribute16 => l_pay_rec.attribute16,
3249 p_attribute17 => l_pay_rec.attribute17,
3250 p_attribute18 => l_pay_rec.attribute18,
3251 p_attribute19 => l_pay_rec.attribute19,
3252 p_attribute20 => l_pay_rec.attribute20,
3253 p_object_version_number => l_pay_ovn,
3254 p_multiple_components => l_pay_rec.multiple_components,
3255 p_approved => 'Y',
3256 p_validate => FALSE,
3257 p_element_entry_id => l_element_entry_id,
3258 p_inv_next_sal_date_warning => l_inv_next_sal_date_warning,
3259 p_proposed_salary_warning => l_proposed_salary_warning,
3260 p_approved_warning => l_approved_warning,
3261 p_payroll_warning => l_payroll_warning);
3262
3263 --
3264 -- Write the pay_proposal_id on the component records, if any.
3265 --
3266 Update per_pay_transactions
3267 set PAY_PROPOSAL_ID = l_pay_proposal_id
3268 Where transaction_step_id = p_transaction_step_id
3269 and PARENT_PAY_TRANSACTION_ID = l_pay_rec.pay_transaction_id;
3270 --
3271 End loop;
3272 --
3273 -- Now insert components
3274 --
3275 For l_comp_rec in csr_insert_comp loop
3276 --
3277 hr_maintain_proposal_api.insert_proposal_component(
3278 p_component_id => l_component_id ,
3279 p_pay_proposal_id => l_comp_rec.pay_proposal_id,
3280 p_business_group_id => l_comp_rec.business_group_id ,
3281 p_approved => l_comp_rec.approved,
3282 p_component_reason => l_comp_rec.reason,
3283 p_change_amount_n => l_comp_rec.change_amount_n,
3284 p_change_percentage => l_comp_rec.change_percentage,
3285 p_comments => l_comp_rec.comments,
3286 p_attribute_category => l_comp_rec.attribute_category,
3287 p_attribute1 => l_comp_rec.attribute1,
3288 p_attribute2 => l_comp_rec.attribute2,
3289 p_attribute3 => l_comp_rec.attribute3,
3290 p_attribute4 => l_comp_rec.attribute4,
3291 p_attribute5 => l_comp_rec.attribute5,
3292 p_attribute6 => l_comp_rec.attribute6,
3293 p_attribute7 => l_comp_rec.attribute7,
3294 p_attribute8 => l_comp_rec.attribute8,
3295 p_attribute9 => l_comp_rec.attribute9,
3296 p_attribute10 => l_comp_rec.attribute10,
3297 p_attribute11 => l_comp_rec.attribute11,
3298 p_attribute12 => l_comp_rec.attribute12,
3299 p_attribute13 => l_comp_rec.attribute13,
3300 p_attribute14 => l_comp_rec.attribute14,
3301 p_attribute15 => l_comp_rec.attribute15,
3302 p_attribute16 => l_comp_rec.attribute16,
3303 p_attribute17 => l_comp_rec.attribute17,
3304 p_attribute18 => l_comp_rec.attribute18,
3305 p_attribute19 => l_comp_rec.attribute19,
3306 p_attribute20 => l_comp_rec.attribute20,
3307 p_object_version_number => l_comp_ovn,
3308 p_validation_strength => 'STRONG',
3309 p_validate => FALSE);
3310
3311 End loop;
3312 --
3313 hr_utility.set_location('Leaving '||l_proc,99);
3314 exception
3315 when others then
3316 hr_utility.set_location('Exception Raised',420);
3317 raise;
3318 --
3319 End process_create_pay_action;
3320 --
3321 ---------------------- process_update_pay_action --------------------------------------
3322 --
3323 Procedure process_update_pay_action(
3324 p_transaction_step_id in number) IS
3325 --
3326 --
3327 Cursor csr_update_pay is
3328 Select * from per_pay_transactions
3329 where transaction_step_id = p_transaction_step_id
3330 and dml_operation = 'UPDATE'
3331 and PARENT_PAY_TRANSACTION_ID is null
3332 order by CHANGE_DATE desc;
3333 --
3334 Cursor csr_update_comp is
3335 Select * from per_pay_transactions
3336 where transaction_step_id = p_transaction_step_id
3337 and dml_operation = 'UPDATE'
3338 and PARENT_PAY_TRANSACTION_ID is not null
3339 order by PARENT_PAY_TRANSACTION_ID;
3340 --
3341 l_pay_proposal_id per_pay_proposals.pay_proposal_id%type;
3342 l_pay_ovn per_pay_proposals.object_version_number%type;
3343 l_component_id per_pay_proposal_components.component_id%type;
3344 l_comp_ovn per_pay_proposal_components.object_version_number%type;
3345 l_element_entry_id pay_element_entries_f.element_entry_id%type;
3346 l_inv_next_sal_date_warning boolean;
3347 l_proposed_salary_warning boolean;
3348 l_approved_warning boolean;
3349 l_payroll_warning boolean;
3350 --
3351 l_proc varchar2(61) := 'process_update_pay_action' ;
3352 --
3353 Begin
3354 --
3355 hr_utility.set_location('Entering '||l_proc,10);
3356 --
3357 per_pyp_bus.g_validate_ss_change_pay := 'Y';
3358 For l_pay_rec in csr_update_pay loop
3359 --
3360 -- Query update pay actions.
3361 -- Call Update API to Update salary proposal record.
3362 --
3363 Select object_version_number into l_pay_ovn
3364 From per_pay_proposals where pay_proposal_id = l_pay_rec.pay_proposal_id;
3365 --
3366
3367 hr_maintain_proposal_api.update_salary_proposal(
3368 p_pay_proposal_id => l_pay_rec.pay_proposal_id,
3369 p_change_date => l_pay_rec.change_date,
3370 p_comments => l_pay_rec.comments,
3371 p_next_sal_review_date => l_pay_rec.next_sal_review_date,
3372 p_proposal_reason => l_pay_rec.reason,
3373 p_proposed_salary_n => l_pay_rec.proposed_salary_n,
3374 p_date_to => l_pay_rec.date_to ,
3375 p_attribute_category => l_pay_rec.attribute_category,
3376 p_attribute1 => l_pay_rec.attribute1,
3377 p_attribute2 => l_pay_rec.attribute2,
3378 p_attribute3 => l_pay_rec.attribute3,
3379 p_attribute4 => l_pay_rec.attribute4,
3380 p_attribute5 => l_pay_rec.attribute5,
3381 p_attribute6 => l_pay_rec.attribute6,
3382 p_attribute7 => l_pay_rec.attribute7,
3383 p_attribute8 => l_pay_rec.attribute8,
3384 p_attribute9 => l_pay_rec.attribute9,
3385 p_attribute10 => l_pay_rec.attribute10,
3386 p_attribute11 => l_pay_rec.attribute11,
3387 p_attribute12 => l_pay_rec.attribute12,
3388 p_attribute13 => l_pay_rec.attribute13,
3389 p_attribute14 => l_pay_rec.attribute14,
3390 p_attribute15 => l_pay_rec.attribute15,
3391 p_attribute16 => l_pay_rec.attribute16,
3392 p_attribute17 => l_pay_rec.attribute17,
3393 p_attribute18 => l_pay_rec.attribute18,
3394 p_attribute19 => l_pay_rec.attribute19,
3395 p_attribute20 => l_pay_rec.attribute20,
3396 p_object_version_number => l_pay_ovn,
3397 p_multiple_components => l_pay_rec.multiple_components,
3398 p_approved => 'Y',
3399 p_validate => FALSE,
3400 p_inv_next_sal_date_warning => l_inv_next_sal_date_warning,
3401 p_proposed_salary_warning => l_proposed_salary_warning,
3402 p_approved_warning => l_approved_warning,
3403 p_payroll_warning => l_payroll_warning);
3404
3405 End loop;
3406 per_pyp_bus.g_validate_ss_change_pay := 'N';
3407 --
3408 -- Now Update components
3409 --
3410 For l_comp_rec in csr_update_comp loop
3411 --
3412 hr_maintain_proposal_api.update_proposal_component(
3413 --
3414 p_component_id => l_comp_rec.component_id ,
3415 p_approved => l_comp_rec.approved,
3416 p_component_reason => l_comp_rec.reason,
3417 p_change_amount_n => l_comp_rec.change_amount_n,
3418 p_change_percentage => l_comp_rec.change_percentage,
3419 p_comments => l_comp_rec.comments,
3420 p_attribute_category => l_comp_rec.attribute_category,
3421 p_attribute1 => l_comp_rec.attribute1,
3422 p_attribute2 => l_comp_rec.attribute2,
3423 p_attribute3 => l_comp_rec.attribute3,
3424 p_attribute4 => l_comp_rec.attribute4,
3425 p_attribute5 => l_comp_rec.attribute5,
3426 p_attribute6 => l_comp_rec.attribute6,
3427 p_attribute7 => l_comp_rec.attribute7,
3428 p_attribute8 => l_comp_rec.attribute8,
3429 p_attribute9 => l_comp_rec.attribute9,
3430 p_attribute10 => l_comp_rec.attribute10,
3431 p_attribute11 => l_comp_rec.attribute11,
3432 p_attribute12 => l_comp_rec.attribute12,
3433 p_attribute13 => l_comp_rec.attribute13,
3434 p_attribute14 => l_comp_rec.attribute14,
3435 p_attribute15 => l_comp_rec.attribute15,
3436 p_attribute16 => l_comp_rec.attribute16,
3437 p_attribute17 => l_comp_rec.attribute17,
3438 p_attribute18 => l_comp_rec.attribute18,
3439 p_attribute19 => l_comp_rec.attribute19,
3440 p_attribute20 => l_comp_rec.attribute20,
3441 p_object_version_number => l_comp_ovn,
3442 p_validation_strength => 'STRONG',
3443 p_validate => FALSE);
3444 --
3445 End loop;
3446 --
3447 hr_utility.set_location('Leaving '||l_proc,99);
3448 exception
3449 when others then
3450 per_pyp_bus.g_validate_ss_change_pay := 'N';
3451 hr_utility.set_location('Exception Raised',420);
3452 raise;
3453 --
3454 End process_update_pay_action;
3455 --
3456 ---------------------- process_delete_pay_action --------------------------------------
3457 --
3458 Procedure process_delete_pay_action(
3459 p_transaction_step_id in number) IS
3460 --
3461 --
3462 Cursor csr_delete_pay is
3463 Select * from per_pay_transactions
3464 where transaction_step_id = p_transaction_step_id
3465 and dml_operation = 'DELETE'
3466 and PARENT_PAY_TRANSACTION_ID is null
3467 order by CHANGE_DATE;
3468 --
3469 Cursor csr_delete_comp is
3470 Select * from per_pay_transactions
3471 where transaction_step_id = p_transaction_step_id
3472 and dml_operation = 'DELETE'
3473 and PARENT_PAY_TRANSACTION_ID is not null
3474 order by PARENT_PAY_TRANSACTION_ID;
3475 --
3476 l_pay_ovn per_pay_proposals.object_version_number%type;
3477 l_comp_ovn per_pay_proposal_components.object_version_number%type;
3478 l_salary_warning boolean;
3479 l_proc varchar2(61) := 'process_delete_pay_action' ;
3480 --
3481 Begin
3482 --
3483 hr_utility.set_location('Entering '||l_proc,10);
3484 --
3485 For l_comp_rec in csr_delete_comp loop
3486 --
3487 Select object_version_number into l_comp_ovn
3488 From per_pay_proposal_components where component_id = l_comp_rec.component_id;
3489 --
3490 hr_maintain_proposal_api.delete_proposal_component(
3491 p_component_id => l_comp_rec.component_id,
3492 p_validation_strength => 'STRONG',
3493 p_object_version_number => l_comp_ovn,
3494 p_validate => FALSE);
3495 End loop;
3496 --
3497 For l_pay_rec in csr_delete_pay loop
3498 --
3499 Select object_version_number into l_pay_ovn
3500 From per_pay_proposals where pay_proposal_id = l_pay_rec.pay_proposal_id;
3501 --
3502 hr_maintain_proposal_api.delete_salary_proposal
3503 (p_pay_proposal_id => l_pay_rec.pay_proposal_id
3504 ,p_business_group_id => l_pay_rec.business_group_id
3505 ,p_object_version_number => l_pay_ovn
3506 ,p_validate => FALSE
3507 ,p_salary_warning => l_salary_warning);
3508 End loop;
3509
3510 hr_utility.set_location('Leaving '||l_proc,99);
3511 exception
3512 when others then
3513 hr_utility.set_location('Exception Raised',420);
3514 raise;
3515 --
3516 End process_delete_pay_action;
3517 --
3518 --
3519 ------------------------------------------------------------------------------
3520 -- The following procedure is called from continue button on overview page.
3521 --
3522 Procedure process_pay_api(
3523 p_validate in varchar2,
3524 p_transaction_step_id in number,
3525 p_effective_date in date default null,
3526 p_new_hire_flag in varchar2 default null,
3527 p_item_key in varchar2 default null,
3528 p_item_type in varchar2 default null,
3529 p_assignment_id in varchar2 default null) is
3530 --
3531 l_proc varchar2(61) := 'process_pay_api' ;
3532 l_gsp_assignment varchar2(30);
3533 --
3534 Begin
3535 --
3536
3537 --
3538 hr_utility.set_location('Entering '||l_proc,10);
3539 --
3540 savepoint apply_change_pay_txn;
3541 --
3542 -- gsp support changes --vkodedal 6141175
3543 l_gsp_assignment :=
3544 hr_transaction_api.get_varchar2_value
3545 (p_transaction_step_id => p_transaction_step_id,
3546 p_name =>'P_REVIEW_ACTID');
3547 if (l_gsp_assignment = '-1' ) then
3548 return;
3549 end if;
3550 -- end of gsp support changes --vkodedal
3551 --
3552 -- BUG 6002700. Check for "HR Base Salary Required"
3553 check_base_salary_profile(p_transaction_step_id,p_item_key,p_item_type,p_effective_date,p_assignment_id);
3554 --
3555 hr_utility.set_location('Profile check done '||l_proc,12);
3556
3557 --
3558 if nvl(p_new_hire_flag,'N') = 'N' then
3559 --
3560 process_salary_basis_change(
3561 p_transaction_step_id => p_transaction_step_id);
3562 --
3563 End if;
3564 --
3565 process_delete_pay_action(
3566 p_transaction_step_id => p_transaction_step_id);
3567 --
3568 hr_utility.set_location('After Deletes '||l_proc,10);
3569 --
3570 process_update_pay_action(
3571 p_transaction_step_id => p_transaction_step_id);
3572 --
3573 hr_utility.set_location('After Updates '||l_proc,10);
3574 --
3575 process_create_pay_action(
3576 p_transaction_step_id => p_transaction_step_id,
3577 --p_new_hire_flag => 'Y' );
3578 p_new_hire_flag => p_new_hire_flag );
3579 --
3580 hr_utility.set_location('After Inserts '||l_proc,10);
3581 --
3582 if nvl(p_validate,'N') = 'Y' then
3583 hr_utility.set_location('validate mode '||p_validate,10);
3584 raise hr_api.validate_enabled;
3585 Else
3586 --
3587 -- Purge data from transaction tables.
3588 --
3589 Delete from per_pay_transactions
3590 where transaction_step_id = p_transaction_step_id;
3591 --
3592 end if;
3593 --
3594 hr_utility.set_location('Leaving '||l_proc,99);
3595 --
3596 exception
3597 when hr_api.validate_enabled then
3598 --
3599 -- As the Validate_Enabled exception has been raised
3600 -- we must rollback to the savepoint
3601 --
3602 ROLLBACK TO apply_change_pay_txn;
3603 --
3604 hr_utility.set_location('Leaving after Rollback'||l_proc,99);
3605 --
3606 when others then
3607 --
3608 ROLLBACK TO apply_change_pay_txn;
3609 --
3610 hr_utility.set_location('Exception Raised',420);
3611 raise;
3612 End;
3613 --
3614 --
3615 ---------------------- process_api --------------------------------------
3616 --
3617 -- The pay actions are applied in the following order
3618 -- 1. DELETE
3619 -- 2. UPDATE
3620 -- 3. INSERT
3621 -- The transaction records are then purged.
3622 --
3623 Procedure process_api(
3624 p_validate in boolean default false,
3625 p_transaction_step_id in number,
3626 p_effective_date in varchar2 default null) is
3627 --
3628 l_proc varchar2(61) := 'process_api' ;
3629 l_gsp_assignment varchar2(30);
3630 --
3631 Begin
3632 --
3633 hr_utility.set_location('Entering '||l_proc,10);
3634 --
3635 savepoint apply_change_pay_txn1;
3636 --
3637 -- gsp support changes --vkodedal 6141175
3638 l_gsp_assignment :=
3639 hr_transaction_api.get_varchar2_value
3640 (p_transaction_step_id => p_transaction_step_id,
3641 p_name =>'P_REVIEW_ACTID');
3642 if (l_gsp_assignment = '-1' ) then
3643 return;
3644 end if;
3645 -- end of gsp support changes --vkodedal
3646 --
3647 process_delete_pay_action(
3648 p_transaction_step_id => p_transaction_step_id);
3649 --
3650 hr_utility.set_location('After Deletes '||l_proc,10);
3651 --
3652 process_update_pay_action(
3653 p_transaction_step_id => p_transaction_step_id);
3654 --
3655 hr_utility.set_location('After Updates '||l_proc,10);
3656 --
3657 process_create_pay_action(
3658 p_transaction_step_id => p_transaction_step_id);
3659 --
3660 hr_utility.set_location('After Inserts '||l_proc,10);
3661 --
3662 if p_validate then
3663 hr_utility.set_location('validate mode '||l_proc,10);
3664 raise hr_api.validate_enabled;
3665 Else
3666 --
3667 -- Purge data from transaction tables.
3668 --
3669 Delete from per_pay_transactions
3670 where transaction_step_id = p_transaction_step_id;
3671 --
3672 end if;
3673 --
3674 hr_utility.set_location('Leaving '||l_proc,99);
3675 --
3676 exception
3677 when hr_api.validate_enabled then
3678 --
3679 -- As the Validate_Enabled exception has been raised
3680 -- we must rollback to the savepoint
3681 --
3682 ROLLBACK TO apply_change_pay_txn1;
3683 --
3684 hr_utility.set_location('Leaving after Rollback'||l_proc,99);
3685 --
3686 when others then
3687 --
3688 ROLLBACK TO apply_change_pay_txn1;
3689 --
3690 hr_utility.set_location('Exception Raised',420);
3691 raise;
3692 --
3693 End process_api;
3694 --
3695 --
3696 --
3697
3698 PROCEDURE get_create_date(p_assignment_id in NUMBER
3699 ,p_effective_date in date
3700 ,p_transaction_id in NUMBER
3701 ,p_create_date out NOCOPY date
3702 ,p_default_salary_basis_id out NOCOPY number
3703 ,p_allow_basis_change out NOCOPY varchar2
3704 ,p_min_create_date out NOCOPY date
3705 ,p_allow_date_change out NOCOPY varchar2
3706 ,p_allow_create out NOCOPY varchar2
3707 ,p_status out NOCOPY NUMBER
3708 ,p_basis_default_date out NOCOPY date
3709 ,p_basis_default_min_date out NOCOPY date
3710 ,p_orig_salary_basis_id out NOCOPY number) IS
3711 --
3712 --
3713 Cursor csr_assgn_exists Is
3714 select '1'
3715 from per_all_assignments_f
3716 where assignment_id = p_assignment_id
3717 and p_effective_date between effective_start_date and effective_end_date;
3718 --
3719 Cursor csr_last_change_date Is
3720 select change_date
3721 from per_pay_proposals
3722 where assignment_id = p_assignment_id
3723 union
3724 select change_date
3725 from per_pay_transactions
3726 where assignment_id = p_assignment_id
3727 and PARENT_PAY_TRANSACTION_ID is null
3728 and status <> 'DELETE'
3729 order by change_date desc;
3730 --
3731 Cursor csr_txn_basis_change_date Is
3732 select hatv1.date_value ,hatv.number_value, hatv.original_number_value
3733 from hr_api_transaction_values hatv,
3734 hr_api_transaction_steps hats,
3735 hr_api_transactions hat,
3736 hr_api_transaction_values hatv1
3737 where hatv.NAME = 'P_PAY_BASIS_ID'
3738 and hatv1.NAME = 'P_EFFECTIVE_DATE'
3739 and hatv1.TRANSACTION_STEP_ID = hats.TRANSACTION_STEP_ID
3740 and hatv.NUMBER_VALUE <> hatv.ORIGINAL_NUMBER_VALUE
3741 and hatv.TRANSACTION_STEP_ID = hats.TRANSACTION_STEP_ID
3742 and hats.TRANSACTION_ID = hat.TRANSACTION_ID
3743 and hat.ASSIGNMENT_ID = p_assignment_id
3744 and hat.TRANSACTION_ID = p_transaction_id
3745 order by hatv1.date_value desc ;
3746 --
3747 Cursor csr_txn_asst_change_date Is
3748 select hatv.date_value
3749 from hr_api_transaction_steps hats,
3750 hr_api_transactions hat,
3751 hr_api_transaction_values hatv
3752 where hats.api_name = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API'
3753 and hatv.transaction_step_id = hats.transaction_step_id
3754 and hatv.name = 'P_EFFECTIVE_DATE'
3755 and hats.TRANSACTION_ID = hat.TRANSACTION_ID
3756 and hat.ASSIGNMENT_ID = p_assignment_id
3757 and hat.TRANSACTION_ID = p_transaction_id;
3758 --
3759 Cursor csr_future_asst_change_max(l_min_change_date date,l_change_date date) Is
3760 select effective_start_date
3761 from per_all_assignments_f
3762 where assignment_id = p_assignment_id
3763 and effective_start_date > l_min_change_date
3764 and effective_start_date < l_change_date
3765 order by effective_start_date desc;
3766 --
3767 Cursor csr_asst_start_date Is
3768 select effective_start_date
3769 from per_all_assignments_f
3770 where assignment_id = p_assignment_id
3771 order by effective_start_date asc;
3772 --
3773 Cursor csr_asst_change_date(l_max_change_date date) Is
3774 select effective_start_date
3775 from per_all_assignments_f
3776 where assignment_id = p_assignment_id
3777 and effective_start_date > l_max_change_date
3778 order by effective_start_date asc;
3779 --
3780 Cursor csr_asst_basis_change_date(l_max_change_date date) Is
3781 select effective_start_date,pay_basis_id
3782 from per_all_assignments_f
3783 where assignment_id = p_assignment_id
3784 and effective_start_date > l_max_change_date
3785 and pay_basis_id <> (Select pay_basis_id from per_all_assignments_f
3786 where assignment_id = p_assignment_id
3787 and l_max_change_date between effective_start_date
3788 and effective_end_date)
3789 order by effective_start_date asc;
3790 --
3791 CURSOR csr_get_next_payroll_date
3792 (l_assignment_id NUMBER
3793 ,l_date DATE
3794 )
3795 IS
3796 select min(ptp.start_date) next_payroll_date
3797 from per_time_periods ptp
3798 ,per_all_assignments_f paaf
3799 where ptp.payroll_id = paaf.payroll_id
3800 and paaf.assignment_id = l_assignment_id
3801 and ptp.start_date > l_date ;
3802 --
3803 Cursor csr_pay_basis_exists(c_assignment_id number, c_effective_date date) IS
3804 select pay_basis_id
3805 from per_all_assignments_f
3806 where assignment_id = c_assignment_id
3807 and c_effective_date between effective_start_date and effective_end_date;
3808 --
3809 Cursor csr_txn_basis_id Is
3810 select hatv.number_value,
3811 hatv.original_number_value
3812 from hr_api_transaction_values hatv,
3813 hr_api_transaction_steps hats,
3814 hr_api_transactions hat
3815 where hatv.NAME = 'P_PAY_BASIS_ID'
3816 and hatv.TRANSACTION_STEP_ID = hats.TRANSACTION_STEP_ID
3817 and hats.TRANSACTION_ID = hat.TRANSACTION_ID
3818 and hat.TRANSACTION_ID = p_transaction_id;
3819 --
3820 --
3821 l_last_payroll_run_date date;
3822 l_last_change_date date;
3823 l_txn_basis_change_date date;
3824 l_dflt_txn_basis_id number;
3825 l_orig_txn_basis_id number;
3826 l_txn_asst_change_date date;
3827 l_asst_basis_change_date date;
3828 l_dflt_asst_basis_id number;
3829 l_asst_change_date date;
3830 l_status number;
3831 l_payroll_attached varchar2(10);
3832 l_proposals_exists varchar2(10);
3833 l_assign_on_gsp varchar2(10);
3834 l_csr_asst_chg_count number;
3835 l_csr_asst_basis_chg_count number;
3836 l_max_create_date date;
3837 l_min_create_date date;
3838 l_max_create_date_src varchar2(20);
3839 l_asst_on_gsp varchar2(20);
3840 l_future_asst_change_max date;
3841 l_assgn_exists varchar2(5);
3842 --
3843 --
3844 Begin
3845 --
3846 --
3847 -- hr_utility.trace_on(null, 'TIGER');
3848 -- g_debug := TRUE;
3849
3850 l_proposals_exists := 'YES';
3851 l_payroll_attached := 'YES';
3852 p_allow_date_change := 'YES';
3853 p_allow_basis_change := 'YES';
3854 p_allow_create := 'YES';
3855 p_status := 1;
3856 p_basis_default_date := null;
3857 p_default_salary_basis_id := null;
3858 p_basis_default_min_date := null;
3859 --
3860 if g_debug then
3861 hr_utility.set_location('Enter get_create_date ', 1);
3862 hr_utility.set_location('p_assignment_id '||p_assignment_id, 2);
3863 hr_utility.set_location('p_effective_date: '||p_effective_date, 3);
3864 hr_utility.set_location('p_transaction_id '||p_transaction_id, 4);
3865 end if;
3866
3867 open csr_assgn_exists;
3868 Fetch csr_assgn_exists into l_assgn_exists;
3869 close csr_assgn_exists;
3870
3871 if l_assgn_exists is null then
3872
3873 l_asst_on_gsp := PER_SSHR_CHANGE_PAY.Check_GSP_Manual_Override(p_assignment_id,p_effective_date,p_transaction_id);
3874 if g_debug then
3875 hr_utility.set_location('l_asst_on_gsp '||l_asst_on_gsp, 5);
3876 end if;
3877 if l_asst_on_gsp = 'N' then
3878 p_allow_create := 'Y_GSP';
3879 p_create_date := p_effective_date;
3880 if g_debug then
3881 hr_utility.set_location('GSP EXISTS ', 6);
3882 end if;
3883 return;
3884 end if;
3885
3886
3887
3888 open csr_txn_basis_id;
3889 Fetch csr_txn_basis_id into p_default_salary_basis_id, p_orig_salary_basis_id;
3890 close csr_txn_basis_id;
3891
3892 if p_default_salary_basis_id is null then
3893 if g_debug then
3894 hr_utility.set_location('New Hire and N_BASIS ', 5);
3895 end if;
3896 p_create_date := sysdate;
3897 p_allow_create := 'N_BASIS';
3898 return;
3899 else
3900 Open csr_last_change_date;
3901 Fetch csr_last_change_date into l_last_change_date;
3902 Close csr_last_change_date;
3903
3904 if l_last_change_date is null then
3905 p_create_date := p_effective_date;
3906 --p_default_salary_basis_id
3907 p_allow_basis_change := 'YES';
3908 p_min_create_date := p_effective_date;
3909 p_allow_date_change := 'YES';
3910 p_allow_create := 'YES';
3911 p_status := 1;
3912 --p_basis_default_date
3913 --p_basis_default_min_date
3914 --p_orig_salary_basis_id
3915 if g_debug then
3916 hr_utility.set_location('New Hire and p_create_date '||p_create_date, 6);
3917 end if;
3918 else
3919 if p_effective_date > l_last_change_date then
3920 p_create_date := p_effective_date;
3921 else
3922 p_create_date := l_last_change_date+1;
3923 end if;
3924
3925 p_allow_basis_change := 'NO';
3926 p_min_create_date := l_last_change_date+1;
3927 p_allow_date_change := 'YES';
3928 p_allow_create := 'YES';
3929 p_status := 1;
3930 --p_basis_default_date
3931 --p_basis_default_min_date
3932 --p_orig_salary_basis_id
3933 if g_debug then
3934 hr_utility.set_location('New Hire and p_create_date '||p_create_date, 7);
3935 end if;
3936 end if;
3937
3938 return;
3939 end if;
3940 end if;
3941
3942
3943
3944 --
3945 l_last_payroll_run_date := PER_SALADMIN_UTILITY.get_last_payroll_dt(p_assignment_id);
3946 if(l_last_payroll_run_date is null) then
3947 l_last_payroll_run_date := p_effective_date;
3948 l_payroll_attached := 'NO';
3949 end if;
3950 --
3951 Open csr_last_change_date;
3952 Fetch csr_last_change_date into l_last_change_date;
3953 Close csr_last_change_date;
3954 if(l_last_change_date is null) then
3955 l_last_change_date := p_effective_date;
3956 l_proposals_exists := 'NO';
3957 end if;
3958 --
3959 if g_debug then
3960 hr_utility.set_location('l_payroll_attached '||l_payroll_attached, 10);
3961 hr_utility.set_location('l_last_payroll_run_date: '||l_last_payroll_run_date, 15);
3962 hr_utility.set_location('l_last_change_date '||l_last_change_date, 16);
3963 hr_utility.set_location('l_proposals_exists: '||l_proposals_exists, 17);
3964 end if;
3965
3966 --
3967 -- CASE 1,2,3,4
3968 l_max_create_date := p_effective_date;
3969 l_min_create_date := p_effective_date;
3970 l_max_create_date_src := 'EFFECTIVEDATE';
3971 if l_max_create_date <= l_last_change_date and l_proposals_exists = 'YES' then
3972 if l_payroll_attached = 'NO' then
3973 l_max_create_date := l_last_change_date + 1;
3974 l_min_create_date := l_last_change_date + 1;
3975 else
3976 Open csr_get_next_payroll_date(p_assignment_id,l_last_change_date);
3977 Fetch csr_get_next_payroll_date into l_max_create_date;
3978 Close csr_get_next_payroll_date;
3979 if l_last_payroll_run_date > l_last_change_date then
3980 l_min_create_date := l_last_payroll_run_date + 1;
3981 else
3982 l_min_create_date := l_last_change_date + 1;
3983 end if;
3984 end if;
3985 l_max_create_date_src := 'PAYPROPOSAL';
3986 elsif l_max_create_date <= l_last_payroll_run_date and l_payroll_attached = 'YES' then
3987 Open csr_get_next_payroll_date(p_assignment_id,l_last_payroll_run_date);
3988 Fetch csr_get_next_payroll_date into l_max_create_date;
3989 Close csr_get_next_payroll_date;
3990 l_min_create_date := l_last_payroll_run_date + 1;
3991 l_max_create_date_src := 'PAYROLL';
3992 end if;
3993 if g_debug then
3994 hr_utility.set_location('l_max_create_date '||l_max_create_date, 18);
3995 hr_utility.set_location('l_min_create_date: '||l_min_create_date, 19);
3996 end if;
3997
3998 --
3999 if l_max_create_date_src = 'EFFECTIVEDATE' then
4000 if l_proposals_exists = 'YES' and l_payroll_attached = 'YES' then
4001 if l_last_payroll_run_date > l_last_change_date then
4002 l_min_create_date := l_last_payroll_run_date + 1;
4003 else
4004 l_min_create_date := l_last_change_date + 1;
4005 end if;
4006 if g_debug then
4007 hr_utility.set_location('l_max_create_date '||l_max_create_date, 20);
4008 hr_utility.set_location('l_min_create_date: '||l_min_create_date, 21);
4009 end if;
4010 elsif l_payroll_attached = 'YES' and l_payroll_attached = 'NO' then
4011 l_min_create_date := l_last_payroll_run_date + 1;
4012 if g_debug then
4013 hr_utility.set_location('l_max_create_date '||l_max_create_date, 22);
4014 hr_utility.set_location('l_min_create_date: '||l_min_create_date, 23);
4015 end if;
4016 elsif l_payroll_attached = 'NO' and l_proposals_exists = 'YES' then
4017 l_min_create_date := l_last_change_date + 1;
4018 if g_debug then
4019 hr_utility.set_location('l_max_create_date '||l_max_create_date, 24);
4020 hr_utility.set_location('l_min_create_date: '||l_min_create_date, 25);
4021 end if;
4022 elsif l_payroll_attached = 'NO' and l_proposals_exists = 'NO' then
4023 Open csr_asst_start_date;
4024 Fetch csr_asst_start_date into l_min_create_date;
4025 Close csr_asst_start_date;
4026 if l_min_create_date is null then
4027 l_min_create_date := l_max_create_date;
4028 end if;
4029 if g_debug then
4030 hr_utility.set_location('l_max_create_date '||l_max_create_date, 26);
4031 hr_utility.set_location('l_min_create_date: '||l_min_create_date, 27);
4032 end if;
4033 end if;
4034 end if;
4035
4036
4037
4038 Open csr_future_asst_change_max(l_min_create_date,l_max_create_date);
4039 Fetch csr_future_asst_change_max into l_future_asst_change_max;
4040 if g_debug then
4041 hr_utility.set_location('l_future_asst_change_max '||l_future_asst_change_max, 28);
4042 end if;
4043 Close csr_future_asst_change_max;
4044
4045 if l_future_asst_change_max is not null then
4046 l_min_create_date := l_future_asst_change_max;
4047 if g_debug then
4048 hr_utility.set_location('l_min_create_date '||l_min_create_date, 29);
4049 end if;
4050 end if;
4051 --
4052 p_create_date := l_max_create_date;
4053 p_min_create_date := l_min_create_date;
4054 if g_debug then
4055 hr_utility.set_location('p_create_date '||p_create_date, 30);
4056 hr_utility.set_location('p_min_create_date: '||p_min_create_date, 35);
4057 end if;
4058 --
4059 Open csr_asst_change_date(l_max_create_date);
4060 Fetch csr_asst_change_date into l_asst_change_date;
4061 l_csr_asst_chg_count := csr_asst_change_date%ROWCOUNT;
4062 Close csr_asst_change_date;
4063
4064 --
4065 Open csr_asst_basis_change_date(l_max_create_date);
4066 Fetch csr_asst_basis_change_date into l_asst_basis_change_date,l_dflt_asst_basis_id;
4067 l_csr_asst_basis_chg_count := csr_asst_basis_change_date%ROWCOUNT;
4068 Close csr_asst_basis_change_date;
4069
4070 --
4071 Open csr_txn_asst_change_date;
4072 Fetch csr_txn_asst_change_date into l_txn_asst_change_date;
4073 Close csr_txn_asst_change_date;
4074
4075 --
4076 Open csr_txn_basis_change_date;
4077 Fetch csr_txn_basis_change_date into l_txn_basis_change_date,l_dflt_txn_basis_id,l_orig_txn_basis_id;
4078 Close csr_txn_basis_change_date;
4079
4080 --
4081 if (l_asst_change_date is not null and l_csr_asst_chg_count >1) then
4082 p_allow_create := 'M_BASIS';
4083 p_status := 5;
4084 if g_debug then
4085 hr_utility.set_location('p_create_date '||p_create_date, 38);
4086 hr_utility.set_location('p_min_create_date: '||p_min_create_date, 39);
4087 end if;
4088 end if;
4089 --
4090 -- CASE 5,6,7,8
4091 if (l_txn_basis_change_date is not null and l_asst_basis_change_date is null) then
4092 if (l_csr_asst_chg_count = 0 and
4093 ( l_proposals_exists = 'YES' and l_last_change_date <> l_txn_basis_change_date)
4094 ) then
4095 p_allow_create := 'YES';
4096 p_create_date := l_txn_basis_change_date;
4097 p_min_create_date := l_min_create_date;
4098 p_default_salary_basis_id := l_dflt_txn_basis_id;
4099 p_orig_salary_basis_id := l_orig_txn_basis_id;
4100 p_allow_basis_change := 'YES';
4101 p_allow_date_change := 'YES';
4102 p_status := 1;
4103 if g_debug then
4104 hr_utility.set_location('p_create_date '||p_create_date, 40);
4105 hr_utility.set_location('p_min_create_date: '||p_min_create_date, 45);
4106 end if;
4107 end if;
4108 end if;
4109 --
4110 if (l_txn_basis_change_date is not null and l_asst_basis_change_date is null) then
4111 if (l_csr_asst_chg_count = 0 and
4112 ( l_proposals_exists = 'YES' and l_last_change_date = l_txn_basis_change_date)
4113 ) then
4114 p_default_salary_basis_id := l_dflt_txn_basis_id;
4115 p_orig_salary_basis_id := l_orig_txn_basis_id;
4116 p_allow_basis_change := 'NO';
4117 p_allow_date_change := 'YES';
4118 p_status := 1;
4119 if g_debug then
4120 hr_utility.set_location('p_create_date '||p_create_date, 47);
4121 hr_utility.set_location('p_min_create_date: '||p_min_create_date, 48);
4122 end if;
4123 end if;
4124 end if;
4125 --
4126 -- CASE 9,10,11,12
4127 if (l_txn_asst_change_date is not null and l_txn_basis_change_date is null and l_asst_basis_change_date is null) then
4128 if (l_csr_asst_chg_count = 0) then
4129 p_allow_create := 'YES';
4130 p_create_date := l_max_create_date;
4131 p_min_create_date := l_min_create_date;
4132 p_basis_default_date := l_txn_asst_change_date;
4133 p_basis_default_min_date := l_txn_asst_change_date;
4134 p_allow_basis_change := 'YES';
4135 p_allow_date_change := 'YES';
4136 p_status := 2;
4137 if g_debug then
4138 hr_utility.set_location('p_create_date '||p_create_date, 50);
4139 hr_utility.set_location('p_min_create_date: '||p_min_create_date, 55);
4140 end if;
4141 end if;
4142 end if;
4143 --
4144 -- CASE 13,14,15,16
4145 if (l_asst_basis_change_date is null and l_asst_change_date is not null
4146 and l_txn_asst_change_date is null) then
4147 p_allow_create := 'YES';
4148 p_create_date := l_max_create_date;
4149 p_min_create_date := l_min_create_date;
4150 p_basis_default_date := l_asst_change_date;
4151 p_basis_default_min_date := l_asst_change_date;
4152 p_allow_basis_change := 'YES';
4153 p_allow_date_change := 'YES';
4154 p_status := 3;
4155 if g_debug then
4156 hr_utility.set_location('p_create_date '||p_create_date, 60);
4157 hr_utility.set_location('p_min_create_date: '||p_min_create_date, 65);
4158 end if;
4159 end if;
4160 --
4161 -- CASE 17,18,19,20
4162 if ((l_asst_basis_change_date is null and l_asst_change_date is not null)
4163 and (l_txn_basis_change_date is null and l_txn_asst_change_date is not null)) then
4164 p_allow_create := 'YES';
4165 p_create_date := l_max_create_date;
4166 p_min_create_date := l_min_create_date;
4167 p_basis_default_date := l_txn_asst_change_date;
4168 p_basis_default_min_date := l_asst_change_date;
4169 p_allow_basis_change := 'YES';
4170 p_allow_date_change := 'YES';
4171 p_status := 4;
4172 if g_debug then
4173 hr_utility.set_location('p_create_date '||p_create_date, 70);
4174 hr_utility.set_location('p_min_create_date: '||p_min_create_date, 75);
4175 end if;
4176 end if;
4177 --
4178 -- CASE 21,22,23,24
4179 if ((l_asst_basis_change_date is null and l_asst_change_date is not null)
4180 and (l_txn_basis_change_date is not null)
4181 and ( l_proposals_exists = 'YES' and l_last_change_date <> l_txn_basis_change_date)
4182 ) then
4183 p_allow_create := 'YES';
4184 p_create_date := l_txn_basis_change_date;
4185 p_min_create_date := l_txn_basis_change_date;
4186 p_default_salary_basis_id := l_dflt_txn_basis_id;
4187 p_orig_salary_basis_id := l_orig_txn_basis_id;
4188 p_basis_default_date := l_txn_basis_change_date;
4189 p_allow_basis_change := 'YES';
4190 p_allow_date_change := 'YES';
4191 p_status := 4;
4192 if g_debug then
4193 hr_utility.set_location('p_create_date '||p_create_date, 80);
4194 hr_utility.set_location('p_min_create_date: '||p_min_create_date, 85);
4195 end if;
4196 end if;
4197 --
4198 if ((l_asst_basis_change_date is null and l_asst_change_date is not null)
4199 and (l_txn_basis_change_date is not null)
4200 and ( l_proposals_exists = 'YES' and l_last_change_date = l_txn_basis_change_date)
4201 ) then
4202 p_basis_default_min_date := l_txn_basis_change_date;
4203 p_default_salary_basis_id := l_dflt_txn_basis_id;
4204 p_orig_salary_basis_id := l_orig_txn_basis_id;
4205 p_basis_default_date := l_txn_basis_change_date;
4206 p_allow_basis_change := 'NO';
4207 p_allow_date_change := 'YES';
4208 p_status := 4;
4209 if g_debug then
4210 hr_utility.set_location('p_create_date '||p_create_date, 87);
4211 hr_utility.set_location('p_min_create_date: '||p_min_create_date, 88);
4212 end if;
4213 end if;
4214 --
4215 -- CASE 25 to 28
4216 -- CASE 29,30,31,32 partially
4217 if (l_asst_basis_change_date is not null and l_txn_basis_change_date is null) then
4218 if (l_csr_asst_basis_chg_count > 1) then
4219 p_allow_create := 'M_BASIS';
4220 p_status := 5;
4221 if g_debug then
4222 hr_utility.set_location('p_create_date '||p_create_date, 90);
4223 hr_utility.set_location('p_min_create_date: '||p_min_create_date, 95);
4224 end if;
4225 elsif (l_csr_asst_basis_chg_count = 1) then
4226 p_create_date := l_asst_basis_change_date;
4227 p_min_create_date := l_asst_basis_change_date;
4228 p_default_salary_basis_id := l_dflt_asst_basis_id;
4229 p_orig_salary_basis_id := l_orig_txn_basis_id;
4230 p_basis_default_date := l_asst_basis_change_date;
4231 p_allow_basis_change := 'NO';
4232 p_allow_date_change := 'NO';
4233 p_status := 0;
4234 if g_debug then
4235 hr_utility.set_location('p_create_date '||p_create_date, 100);
4236 hr_utility.set_location('p_min_create_date: '||p_min_create_date, 105);
4237 end if;
4238 end if;
4239 end if;
4240 --
4241 -- CASE 29,30,31,32 partially
4242 if ((l_asst_basis_change_date is not null and l_txn_basis_change_date is not null)
4243 OR (l_csr_asst_basis_chg_count > 1)) then
4244 p_allow_create := 'M_BASIS';
4245 p_status := 5;
4246 if g_debug then
4247 hr_utility.set_location('p_create_date '||p_create_date, 110);
4248 hr_utility.set_location('p_min_create_date: '||p_min_create_date, 115);
4249 end if;
4250 end if;
4251 --
4252 l_asst_on_gsp := PER_SSHR_CHANGE_PAY.Check_GSP_Manual_Override(p_assignment_id,p_create_date,p_transaction_id);
4253 if l_asst_on_gsp = 'N' then
4254 p_allow_create := 'Y_GSP';
4255 if g_debug then
4256 hr_utility.set_location('GSP EXISTS ', 120);
4257 end if;
4258 return;
4259 end if;
4260 --
4261 if p_default_salary_basis_id is null then
4262
4263 Open csr_txn_basis_id;
4264 fetch csr_txn_basis_id into p_default_salary_basis_id, p_orig_salary_basis_id;
4265 Close csr_txn_basis_id;
4266
4267 if p_default_salary_basis_id is null then
4268 Open csr_pay_basis_exists(p_assignment_id,p_create_date);
4269 fetch csr_pay_basis_exists into p_default_salary_basis_id;
4270 Close csr_pay_basis_exists;
4271 end if;
4272
4273 if g_debug then
4274 hr_utility.set_location('p_default_salary_basis_id '||p_default_salary_basis_id, 130);
4275 end if;
4276 end if;
4277
4278 if p_default_salary_basis_id is null then
4279 Open csr_txn_basis_id;
4280 fetch csr_txn_basis_id into p_default_salary_basis_id, p_orig_salary_basis_id;
4281 Close csr_txn_basis_id;
4282 if g_debug then
4283 hr_utility.set_location('p_default_salary_basis_id '||p_default_salary_basis_id, 140);
4284 end if;
4285 end if;
4286
4287
4288 --
4289 --
4290 End get_create_date;
4291
4292 --
4293 --
4294 --
4295 Procedure get_Create_Date_old(p_assignment_id in NUMBER
4296 ,p_effective_date in date
4297 ,p_transaction_id in NUMBER
4298 ,p_create_date out NOCOPY date
4299 ,p_default_salary_basis_id out NOCOPY number
4300 ,p_allow_basis_change out NOCOPY varchar2
4301 ,p_min_create_date out NOCOPY date
4302 ,p_allow_date_change out NOCOPY varchar2
4303 ,p_allow_create out NOCOPY varchar2)
4304 is
4305 --
4306 --
4307 Cursor csr_pay_basis_exists(c_assignment_id number, c_effective_date date) IS
4308 select pay_basis_id
4309 from per_all_assignments_f
4310 where assignment_id = c_assignment_id
4311 and c_effective_date between effective_start_date and effective_end_date;
4312 --
4313 --
4314 Cursor csr_last_change_date Is
4315 select change_date
4316 from per_pay_proposals
4317 where assignment_id = p_assignment_id
4318 union
4319 select change_date
4320 from per_pay_transactions
4321 where assignment_id = p_assignment_id
4322 and PARENT_PAY_TRANSACTION_ID is null
4323 and status <> 'DELETE'
4324 order by change_date desc;
4325 --
4326 --
4327 Cursor csr_txn_basis_change_date Is
4328 select hatv1.date_value ,hatv.number_value
4329 from hr_api_transaction_values hatv,
4330 hr_api_transaction_steps hats,
4331 hr_api_transactions hat,
4332 hr_api_transaction_values hatv1
4333 where hatv.NAME = 'P_PAY_BASIS_ID'
4334 and hatv1.NAME = 'P_EFFECTIVE_DATE'
4335 and hatv1.TRANSACTION_STEP_ID = hats.TRANSACTION_STEP_ID
4336 and hatv.NUMBER_VALUE <> hatv.ORIGINAL_NUMBER_VALUE
4337 and hatv.TRANSACTION_STEP_ID = hats.TRANSACTION_STEP_ID
4338 and hats.TRANSACTION_ID = hat.TRANSACTION_ID
4339 and hat.ASSIGNMENT_ID = p_assignment_id
4340 and hat.TRANSACTION_ID = p_transaction_id
4341 order by hatv1.date_value desc ;
4342 --
4343 --
4344 Cursor csr_curr_asst_change_date(l_curr_change_date date) Is
4345 select effective_start_date
4346 from per_all_assignments_f
4347 where assignment_id = p_assignment_id
4348 and effective_start_date <= l_curr_change_date
4349 order by effective_start_date desc;
4350 --
4351 --
4352 Cursor csr_last_asst_change_date(l_max_change_date date) Is
4353 select effective_start_date,pay_basis_id
4354 from per_all_assignments_f
4355 where assignment_id = p_assignment_id
4356 and effective_start_date > l_max_change_date
4357 order by effective_start_date asc;
4358 --
4359 --
4360 CURSOR csr_get_next_payroll_date
4361 (p_assignment_id NUMBER
4362 ,p_effective_date DATE
4363 )
4364 IS
4365 select min(ptp.start_date) next_payroll_date
4366 from per_time_periods ptp
4367 ,per_all_assignments_f paaf
4368 where ptp.payroll_id = paaf.payroll_id
4369 and paaf.assignment_id = p_assignment_id
4370 and ptp.start_date > p_effective_date ;
4371 --
4372 --
4373 l_last_change_date date;
4374 l_txn_basis_change_date date;
4375 l_last_assignment_change_date date;
4376 l_last_payroll_run_date date;
4377 l_payroll_param_date date;
4378 l_payroll_attached varchar2(10);
4379 l_assign_on_gsp varchar2(10);
4380 l_pay_basis_id number;
4381 l_default_asst_salary_basis_id number;
4382 l_default_txn_salary_basis_id number;
4383 l_csr_last_asst_chg_dt_count number;
4384 l_proposals_exists varchar2(10);
4385 --
4386 --
4387 Begin
4388 --
4389 --hr_utility.trace_on(null, 'TIGER');
4390 g_debug := TRUE;
4391 --
4392 if g_debug then
4393 hr_utility.set_location('Entering '||'get_Create_Date', 5);
4394 end if;
4395 p_create_date := p_effective_date;
4396 p_allow_date_change := 'YES';
4397 p_allow_basis_change := 'YES';
4398 l_payroll_attached := 'YES';
4399 l_proposals_exists := 'YES';
4400
4401 l_last_payroll_run_date := PER_SALADMIN_UTILITY.get_last_payroll_dt(p_assignment_id);
4402
4403 if g_debug then
4404 hr_utility.set_location('Selected p_effective_date '||p_effective_date, 10);
4405 hr_utility.set_location('l_last_payroll_run_date: '||l_last_payroll_run_date, 15);
4406 end if;
4407
4408 if(l_last_payroll_run_date is null) then
4409 l_last_payroll_run_date := p_effective_date;
4410 l_payroll_attached := 'NO';
4411 if g_debug then
4412 hr_utility.set_location('l_last_payroll_run_date is null and set to '||l_last_payroll_run_date,20);
4413 end if;
4414 end if;
4415
4416 Open csr_last_change_date;
4417 Fetch csr_last_change_date into l_last_change_date;
4418 Close csr_last_change_date;
4419
4420 Open csr_txn_basis_change_date;
4421 Fetch csr_txn_basis_change_date into l_txn_basis_change_date,l_default_txn_salary_basis_id;
4422 Close csr_txn_basis_change_date;
4423
4424 if g_debug then
4425 hr_utility.set_location('l_last_change_date '||l_last_change_date, 25);
4426 hr_utility.set_location('l_txn_basis_change_date '||l_txn_basis_change_date, 27);
4427 end if;
4428
4429 if l_last_change_date is null then
4430 l_last_change_date := l_last_payroll_run_date;
4431 l_proposals_exists := 'NO';
4432 if g_debug then
4433 hr_utility.set_location('l_last_change_date is null and set to '||l_last_change_date, 30);
4434 end if;
4435 end if;
4436
4437 Open csr_last_asst_change_date(l_last_change_date);
4438 Fetch csr_last_asst_change_date into l_last_assignment_change_date,l_default_asst_salary_basis_id;
4439 l_csr_last_asst_chg_dt_count:= csr_last_asst_change_date%ROWCOUNT;
4440 Close csr_last_asst_change_date;
4441
4442 if g_debug then
4443 hr_utility.set_location('l_csr_last_asst_chg_dt_count: '||l_csr_last_asst_chg_dt_count, 35);
4444 hr_utility.set_location('l_last_assignment_change_date: '||l_last_assignment_change_date, 38);
4445 hr_utility.set_location('l_default_asst_salary_basis_id: '||l_default_asst_salary_basis_id, 39);
4446 end if;
4447 --
4448 --
4449 if( (l_txn_basis_change_date is not null and l_last_assignment_change_date is not null and l_txn_basis_change_date <> l_last_assignment_change_date)
4450 OR
4451 (l_csr_last_asst_chg_dt_count >1)
4452 ) then
4453 p_allow_create := 'M_BASIS';
4454 if g_debug then
4455 hr_utility.set_location('l_csr_last_asst_chg_dt_count>1:: M_BASIS::return ',40);
4456 end if;
4457 return;
4458 else
4459 if g_debug then
4460 hr_utility.set_location('l_csr_last_asst_chg_dt_count<1:: p_allow_create = Y ',43);
4461 end if;
4462 p_allow_create := 'Y';
4463 end if;
4464 --
4465 --
4466 if (l_txn_basis_change_date is not null) then
4467 if (l_last_change_date = l_txn_basis_change_date) then
4468 p_allow_basis_change := 'NO';
4469
4470 if g_debug then
4471 hr_utility.set_location('TXN_SAL_BASIS_EXISTS and l_last_change_date = l_txn_basis_change_date',45);
4472 end if;
4473
4474 if (l_last_payroll_run_date > l_last_change_date) then
4475
4476 if(l_payroll_attached = 'NO') then
4477 p_create_date := p_effective_date;
4478 p_min_create_date := l_last_change_date;
4479 if g_debug then
4480 hr_utility.set_location('p_create_date '||p_create_date,46);
4481 hr_utility.set_location('p_min_create_date '||p_min_create_date,47);
4482 end if;
4483 else
4484 Open csr_get_next_payroll_date(p_assignment_id,l_last_payroll_run_date);
4485 Fetch csr_get_next_payroll_date into p_create_date;
4486 Close csr_get_next_payroll_date;
4487 p_min_create_date := l_last_payroll_run_date;
4488 if g_debug then
4489 hr_utility.set_location('p_create_date '||p_create_date,48);
4490 hr_utility.set_location('p_min_create_date '||p_min_create_date,49);
4491 end if;
4492 end if;
4493
4494 elsif (l_last_payroll_run_date = l_last_change_date) then
4495
4496 if(l_payroll_attached = 'NO' and l_proposals_exists = 'YES') then
4497 p_create_date := l_last_change_date+1;
4498 p_min_create_date := l_last_change_date;
4499 if g_debug then
4500 hr_utility.set_location('p_create_date '||p_create_date,50);
4501 hr_utility.set_location('p_min_create_date '||p_min_create_date,51);
4502 end if;
4503 elsif(l_payroll_attached = 'NO' and l_proposals_exists = 'NO') then
4504 p_create_date := p_effective_date;
4505 p_min_create_date := p_effective_date;
4506 if g_debug then
4507 hr_utility.set_location('p_create_date '||p_create_date,52);
4508 hr_utility.set_location('p_min_create_date '||p_min_create_date,53);
4509 end if;
4510 else
4511 Open csr_get_next_payroll_date(p_assignment_id,l_last_change_date);
4512 Fetch csr_get_next_payroll_date into p_create_date;
4513 Close csr_get_next_payroll_date;
4514 p_min_create_date := l_last_change_date+1;
4515 if g_debug then
4516 hr_utility.set_location('p_create_date '||p_create_date,54);
4517 hr_utility.set_location('p_min_create_date '||p_min_create_date,55);
4518 end if;
4519 end if;
4520
4521 elsif (l_last_payroll_run_date < l_last_change_date) then
4522
4523 if(l_payroll_attached = 'NO') then
4524 p_create_date := l_last_change_date+1;
4525 p_min_create_date := l_last_change_date;
4526 if g_debug then
4527 hr_utility.set_location('p_create_date '||p_create_date,56);
4528 hr_utility.set_location('p_min_create_date '||p_min_create_date,57);
4529 end if;
4530 else
4531 Open csr_get_next_payroll_date(p_assignment_id,l_last_change_date);
4532 Fetch csr_get_next_payroll_date into p_create_date;
4533 Close csr_get_next_payroll_date;
4534 p_min_create_date := l_last_change_date;
4535 if g_debug then
4536 hr_utility.set_location('p_create_date '||p_create_date,61);
4537 hr_utility.set_location('p_min_create_date '||p_min_create_date,63);
4538 end if;
4539 end if;
4540
4541 end if;
4542
4543 else
4544 --
4545 if l_payroll_attached = 'NO' then
4546 l_last_payroll_run_date := l_last_assignment_change_date;
4547 end if;
4548 --
4549 if l_proposals_exists = 'NO' then
4550 l_last_change_date := l_last_assignment_change_date;
4551 end if;
4552 --
4553 if((l_txn_basis_change_date >= l_last_payroll_run_date) and (l_txn_basis_change_date > l_last_change_date)) then
4554 p_allow_basis_change := 'YES';
4555 p_create_date := l_txn_basis_change_date;
4556 p_min_create_date := l_txn_basis_change_date;
4557 p_allow_date_change := 'NO';
4558 if g_debug then
4559 hr_utility.set_location('l_txn_basis_change_date >= l_last_payroll_run_date l_last_change_date ',66);
4560 hr_utility.set_location('p_create_date '||p_create_date,67);
4561 hr_utility.set_location('p_min_create_date '||p_min_create_date,68);
4562 end if;
4563 else
4564 p_create_date := null;
4565 if g_debug then
4566 hr_utility.set_location('p_create_date is set to null ', 69);
4567 end if;
4568 end if;
4569 end if;
4570 --
4571 --
4572 elsif (l_last_assignment_change_date is not null) then
4573 if (l_last_change_date = l_last_assignment_change_date) then
4574 --p_allow_basis_change := 'NO';
4575
4576 if g_debug then
4577 hr_utility.set_location('AST_SAL_BASIS_CHG_EXISTS', 70);
4578 end if;
4579
4580 if (l_last_payroll_run_date > l_last_change_date) then
4581
4582 if(l_payroll_attached = 'NO') then
4583 p_create_date := p_effective_date;
4584 p_min_create_date := l_last_change_date;
4585 if g_debug then
4586 hr_utility.set_location('p_create_date '||p_create_date,71);
4587 hr_utility.set_location('p_min_create_date '||p_min_create_date,72);
4588 end if;
4589 else
4590 Open csr_get_next_payroll_date(p_assignment_id,l_last_payroll_run_date);
4591 Fetch csr_get_next_payroll_date into p_create_date;
4592 Close csr_get_next_payroll_date;
4593 p_min_create_date := l_last_payroll_run_date;
4594 if g_debug then
4595 hr_utility.set_location('p_create_date '||p_create_date,73);
4596 hr_utility.set_location('p_min_create_date '||p_min_create_date,74);
4597 end if;
4598 end if;
4599
4600 elsif (l_last_payroll_run_date = l_last_change_date) then
4601
4602 if(l_payroll_attached = 'NO' and l_proposals_exists = 'YES') then
4603 p_create_date := l_last_change_date+1;
4604 p_min_create_date := l_last_change_date;
4605 if g_debug then
4606 hr_utility.set_location('p_create_date '||p_create_date,75);
4607 hr_utility.set_location('p_min_create_date '||p_min_create_date,76);
4608 end if;
4609 elsif(l_payroll_attached = 'NO' and l_proposals_exists = 'NO') then
4610 p_create_date := p_effective_date;
4611 p_min_create_date := p_effective_date;
4612 if g_debug then
4613 hr_utility.set_location('p_create_date '||p_create_date,77);
4614 hr_utility.set_location('p_min_create_date '||p_min_create_date,78);
4615 end if;
4616 else
4617 Open csr_get_next_payroll_date(p_assignment_id,l_last_payroll_run_date);
4618 Fetch csr_get_next_payroll_date into p_create_date;
4619 Close csr_get_next_payroll_date;
4620 p_min_create_date := l_last_change_date;
4621 if g_debug then
4622 hr_utility.set_location('p_create_date '||p_create_date,79);
4623 hr_utility.set_location('p_min_create_date '||p_min_create_date,80);
4624 end if;
4625 end if;
4626
4627 elsif (l_last_payroll_run_date < l_last_change_date) then
4628
4629 if(l_payroll_attached = 'NO') then
4630 p_create_date := l_last_change_date+1;
4631 p_min_create_date := l_last_change_date;
4632 if g_debug then
4633 hr_utility.set_location('p_create_date '||p_create_date,83);
4634 hr_utility.set_location('p_min_create_date '||p_min_create_date,84);
4635 end if;
4636 else
4637 Open csr_get_next_payroll_date(p_assignment_id,l_last_change_date);
4638 Fetch csr_get_next_payroll_date into p_create_date;
4639 Close csr_get_next_payroll_date;
4640 p_min_create_date := l_last_change_date;
4641 if g_debug then
4642 hr_utility.set_location('p_create_date '||p_create_date,87);
4643 hr_utility.set_location('p_min_create_date '||p_min_create_date,88);
4644 end if;
4645 end if;
4646
4647 end if;
4648 else
4649 --
4650 if l_payroll_attached = 'NO' then
4651 l_last_payroll_run_date := l_last_assignment_change_date;
4652 end if;
4653 --
4654 if l_proposals_exists = 'NO' then
4655 l_last_change_date := l_last_assignment_change_date;
4656 end if;
4657 --
4658 if((l_last_assignment_change_date >= l_last_payroll_run_date) and (l_last_assignment_change_date > l_last_change_date)) then
4659 p_allow_basis_change := 'YES';
4660 p_create_date := l_last_assignment_change_date;
4661 p_min_create_date := l_txn_basis_change_date;
4662 p_allow_date_change := 'NO';
4663 if g_debug then
4664 hr_utility.set_location('l_last_assignment_change_date > l_last_payroll_run_date ',90);
4665 hr_utility.set_location('p_create_date '||p_create_date,95);
4666 hr_utility.set_location('p_min_create_date '||p_min_create_date,96);
4667 end if;
4668 else
4669 p_create_date := null;
4670 if g_debug then
4671 hr_utility.set_location('p_create_date is set null ', 100);
4672 end if;
4673 end if;
4674 end if;
4675 --
4676 --
4677 else
4678 if g_debug then
4679 hr_utility.set_location('NO_SAL_BASIS_CHG ', 101);
4680 end if;
4681 p_allow_basis_change := 'YES';
4682
4683 if (l_last_payroll_run_date > l_last_change_date) then
4684
4685 if(l_payroll_attached = 'NO') then
4686 p_create_date := p_effective_date;
4687 p_min_create_date := l_last_change_date;
4688 if g_debug then
4689 hr_utility.set_location('p_create_date: '||p_create_date, 101);
4690 hr_utility.set_location('p_min_create_date: '||p_min_create_date, 102);
4691 end if;
4692 else
4693 Open csr_get_next_payroll_date(p_assignment_id,l_last_payroll_run_date);
4694 Fetch csr_get_next_payroll_date into p_create_date;
4695 Close csr_get_next_payroll_date;
4696 p_min_create_date := l_last_payroll_run_date;
4697 if g_debug then
4698 hr_utility.set_location('p_create_date: '||p_create_date, 103);
4699 hr_utility.set_location('p_min_create_date: '||p_min_create_date, 104);
4700 end if;
4701 end if;
4702
4703 elsif (l_last_payroll_run_date = l_last_change_date) then
4704
4705 if(l_payroll_attached = 'NO' and l_proposals_exists = 'YES' ) then
4706 p_create_date := l_last_change_date+1;
4707 p_min_create_date := p_effective_date;
4708 if g_debug then
4709 hr_utility.set_location('p_create_date: '||p_create_date, 105);
4710 hr_utility.set_location('p_min_create_date: '||p_min_create_date, 106);
4711 end if;
4712 elsif(l_payroll_attached = 'NO' and l_proposals_exists = 'NO') then
4713 p_create_date := p_effective_date;
4714 p_min_create_date := p_effective_date;
4715 if g_debug then
4716 hr_utility.set_location('p_create_date '||p_create_date,107);
4717 hr_utility.set_location('p_min_create_date '||p_min_create_date,108);
4718 end if;
4719 else
4720 Open csr_get_next_payroll_date(p_assignment_id,l_last_change_date);
4721 Fetch csr_get_next_payroll_date into p_create_date;
4722 Close csr_get_next_payroll_date;
4723 p_min_create_date := l_last_change_date+1;
4724 if g_debug then
4725 hr_utility.set_location('p_create_date: '||p_create_date, 109);
4726 hr_utility.set_location('p_min_create_date: '||p_min_create_date, 110);
4727 end if;
4728 end if;
4729
4730 elsif (l_last_payroll_run_date < l_last_change_date) then
4731
4732 if(l_payroll_attached = 'NO') then
4733 p_create_date := l_last_change_date+1;
4734 p_min_create_date := l_last_change_date;
4735 if g_debug then
4736 hr_utility.set_location('p_create_date: '||p_create_date, 111);
4737 hr_utility.set_location('p_min_create_date: '||p_min_create_date, 112);
4738 end if;
4739 else
4740 Open csr_get_next_payroll_date(p_assignment_id,l_last_change_date);
4741 Fetch csr_get_next_payroll_date into p_create_date;
4742 Close csr_get_next_payroll_date;
4743 p_min_create_date := l_last_change_date;
4744 if g_debug then
4745 hr_utility.set_location('p_create_date: '||p_create_date, 117);
4746 hr_utility.set_location('p_min_create_date: '||p_min_create_date, 118);
4747 end if;
4748 end if;
4749
4750 end if;
4751 end if;
4752 --
4753 --
4754 --
4755 --
4756 l_assign_on_gsp := PER_SALADMIN_UTILITY.Check_GSP_Manual_Override(p_assignment_id,p_create_date);
4757
4758 if l_assign_on_gsp = 'N' then
4759 p_allow_create := 'Y_GSP';
4760 return;
4761 else
4762 p_allow_create := 'N_GSP';
4763 end if;
4764 --
4765 --
4766 Open csr_pay_basis_exists(p_assignment_id, p_create_date);
4767 Fetch csr_pay_basis_exists into l_pay_basis_id;
4768 Close csr_pay_basis_exists;
4769
4770 if l_pay_basis_id is null then
4771 p_allow_create := 'N_BASIS';
4772 return;
4773 else
4774 p_allow_create := 'Y_BASIS';
4775 end if;
4776 --
4777 --
4778 if g_debug then
4779 hr_utility.set_location('csr_pay_basis_exists : '||l_pay_basis_id, 120);
4780 hr_utility.set_location('p_allow_create : '||p_allow_create, 121);
4781 end if;
4782 --
4783 --
4784 if l_default_txn_salary_basis_id is not null then
4785 p_default_salary_basis_id := l_default_txn_salary_basis_id;
4786 if g_debug then
4787 hr_utility.set_location('l_default_txn_salary_basis_id is not null ', 122);
4788 end if;
4789 elsif l_default_asst_salary_basis_id is not null then
4790 p_default_salary_basis_id := l_default_asst_salary_basis_id;
4791 if g_debug then
4792 hr_utility.set_location('l_default_asst_salary_basis_id is not null ', 124);
4793 end if;
4794 end if;
4795 --
4796 --
4797 if l_proposals_exists = 'NO' then
4798 if l_payroll_attached = 'NO' then
4799 Open csr_curr_asst_change_date(p_create_date);
4800 Fetch csr_curr_asst_change_date into p_min_create_date;
4801 Close csr_curr_asst_change_date;
4802 if g_debug then
4803 hr_utility.set_location('p_min_create_date is not null '||p_min_create_date, 130);
4804 end if;
4805 else
4806 p_min_create_date := l_last_payroll_run_date;
4807 if g_debug then
4808 hr_utility.set_location('p_min_create_date is not null '||p_min_create_date, 140);
4809 end if;
4810 end if;
4811 end if;
4812 --
4813 --
4814 if g_debug then
4815 hr_utility.set_location('Leaving: '||'get_Create_Date', 150);
4816 end if;
4817 --
4818 --
4819 End get_Create_Date_old;
4820 --
4821 --
4822 --
4823 Function get_payroll_period(p_payroll_id in NUMBER)
4824 RETURN VARCHAR2 is
4825
4826 CURSOR csr_period_table is
4827 select nvl(DESCRIPTION,ptt.period_type)
4828 from PER_TIME_PERIOD_TYPES ptt
4829 ,pay_all_payrolls_f pap
4830 ,per_all_assignments_f paa
4831 where pap.payroll_id = p_payroll_id
4832 and ptt.period_type = pap.period_type;
4833
4834 l_period varchar2(30);
4835 BEGIN
4836 Open csr_period_table;
4837 Fetch csr_period_table into l_period;
4838 Close csr_period_table;
4839
4840 return l_period;
4841 END get_payroll_period;
4842
4843
4844 --
4845 PROCEDURE get_update_param
4846 ( p_assignment_id in Number
4847 , p_transaction_id in Number
4848 , p_current_date in Date
4849 , p_previous_date in Date
4850 , p_proposal_exists in Varchar2
4851 , p_allow_basis_change out NOCOPY varchar2
4852 , p_min_update_date out NOCOPY date
4853 , p_allow_date_change out NOCOPY varchar2
4854 , p_status out NOCOPY Number
4855 , p_basis_default_date out NOCOPY date
4856 , p_basis_default_min_date out NOCOPY date
4857 , p_orig_basis_id out NOCOPY Number)
4858 is
4859 --
4860 --
4861 Cursor csr_txn_basis_change_date Is
4862 select hatv1.date_value ,hatv.number_value, hatv.original_number_value
4863 from hr_api_transaction_values hatv,
4864 hr_api_transaction_steps hats,
4865 hr_api_transactions hat,
4866 hr_api_transaction_values hatv1
4867 where hatv.NAME = 'P_PAY_BASIS_ID'
4868 and hatv1.NAME = 'P_EFFECTIVE_DATE'
4869 and hatv1.TRANSACTION_STEP_ID = hats.TRANSACTION_STEP_ID
4870 and hatv.NUMBER_VALUE <> hatv.ORIGINAL_NUMBER_VALUE
4871 and hatv.TRANSACTION_STEP_ID = hats.TRANSACTION_STEP_ID
4872 and hats.TRANSACTION_ID = hat.TRANSACTION_ID
4873 and hat.ASSIGNMENT_ID = p_assignment_id
4874 and hat.TRANSACTION_ID = p_transaction_id
4875 order by hatv1.date_value desc ;
4876 --
4877 Cursor csr_txn_asst_change_date Is
4878 select hatv.date_value
4879 from hr_api_transaction_steps hats,
4880 hr_api_transactions hat,
4881 hr_api_transaction_values hatv
4882 where hats.api_name = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API'
4883 and hatv.transaction_step_id = hats.transaction_step_id
4884 and hatv.name = 'P_EFFECTIVE_DATE'
4885 and hats.TRANSACTION_ID = hat.TRANSACTION_ID
4886 and hat.ASSIGNMENT_ID = p_assignment_id
4887 and hat.TRANSACTION_ID = p_transaction_id;
4888 --
4889 Cursor csr_future_asst_change_max(l_min_change_date date,l_change_date date) Is
4890 select effective_start_date
4891 from per_all_assignments_f
4892 where assignment_id = p_assignment_id
4893 and effective_start_date > l_min_change_date
4894 and effective_start_date < l_change_date
4895 order by effective_start_date desc;
4896 --
4897 Cursor csr_asst_change_date(l_max_change_date date) Is
4898 select effective_start_date
4899 from per_all_assignments_f
4900 where assignment_id = p_assignment_id
4901 and effective_start_date > l_max_change_date
4902 order by effective_start_date asc;
4903 --
4904 Cursor csr_asst_basis_change_date(l_max_change_date date) Is
4905 select effective_start_date,pay_basis_id
4906 from per_all_assignments_f
4907 where assignment_id = p_assignment_id
4908 and effective_start_date > l_max_change_date
4909 and pay_basis_id <> (Select pay_basis_id from per_all_assignments_f
4910 where assignment_id = p_assignment_id
4911 and l_max_change_date between effective_start_date
4912 and effective_end_date)
4913 order by effective_start_date asc;
4914 --
4915 CURSOR csr_get_next_payroll_date
4916 (l_assignment_id NUMBER
4917 ,l_date DATE
4918 )
4919 IS
4920 select min(ptp.start_date) next_payroll_date
4921 from per_time_periods ptp
4922 ,per_all_assignments_f paaf
4923 where ptp.payroll_id = paaf.payroll_id
4924 and paaf.assignment_id = l_assignment_id
4925 and ptp.start_date > l_date ;
4926 --
4927 --
4928 l_last_payroll_run_date date;
4929 l_max_create_date date;
4930 l_min_create_date date;
4931 l_max_create_date_src varchar2(20);
4932 l_future_asst_change_max date;
4933 l_asst_change_date date;
4934 l_csr_asst_chg_count number;
4935 l_asst_basis_change_date date;
4936 l_dflt_asst_basis_id number;
4937 l_txn_asst_change_date date;
4938 l_txn_basis_change_date date;
4939 l_dflt_txn_basis_id number;
4940 l_orig_txn_basis_id number;
4941 l_payroll_attached varchar2(20);
4942 l_csr_asst_basis_chg_count number;
4943 --
4944 --
4945 Begin
4946 --
4947 --
4948 l_payroll_attached := 'YES';
4949 p_allow_date_change := 'YES';
4950 p_allow_basis_change := 'YES';
4951 p_status := 1;
4952 p_basis_default_date := null;
4953 p_basis_default_min_date := null;
4954 p_min_update_date := null;
4955 --
4956 --
4957 l_last_payroll_run_date := PER_SALADMIN_UTILITY.get_last_payroll_dt(p_assignment_id);
4958 if(l_last_payroll_run_date is null) then
4959 l_last_payroll_run_date := p_previous_date;
4960 l_payroll_attached := 'NO';
4961 end if;
4962 --
4963 if g_debug then
4964 hr_utility.set_location('get_update_param ', 5);
4965 hr_utility.set_location('l_payroll_attached '||l_payroll_attached, 10);
4966 hr_utility.set_location('l_last_payroll_run_date: '||l_last_payroll_run_date, 15);
4967 end if;
4968 l_max_create_date := p_previous_date;
4969 l_min_create_date := p_previous_date;
4970 l_max_create_date_src := 'EFFECTIVEDATE';
4971
4972 if (l_max_create_date <= l_last_payroll_run_date and l_payroll_attached = 'YES' )then
4973 Open csr_get_next_payroll_date(p_assignment_id,l_last_payroll_run_date);
4974 Fetch csr_get_next_payroll_date into l_max_create_date;
4975 Close csr_get_next_payroll_date;
4976 l_min_create_date := l_last_payroll_run_date + 1;
4977 l_max_create_date_src := 'PAYROLL';
4978 end if;
4979
4980 if l_max_create_date_src = 'EFFECTIVEDATE' then
4981 if (l_payroll_attached = 'YES' and l_last_payroll_run_date > p_previous_date )then
4982 l_min_create_date := l_last_payroll_run_date + 1;
4983 else
4984 l_min_create_date := p_previous_date + 1;
4985 end if;
4986 end if;
4987
4988 Open csr_future_asst_change_max(l_min_create_date,l_max_create_date);
4989 Fetch csr_future_asst_change_max into l_future_asst_change_max;
4990 Close csr_future_asst_change_max;
4991
4992 if l_future_asst_change_max is not null then
4993 l_min_create_date := l_future_asst_change_max;
4994 end if;
4995
4996 p_min_update_date := l_min_create_date;
4997
4998 if g_debug then
4999 hr_utility.set_location('l_max_create_date '||l_max_create_date, 25);
5000 hr_utility.set_location('p_current_date: '||p_current_date, 26);
5001 hr_utility.set_location('l_min_create_date: '||l_min_create_date, 30);
5002 hr_utility.set_location('l_max_create_date_src: '||l_max_create_date_src, 31);
5003
5004 end if;
5005 --
5006 Open csr_asst_change_date(l_max_create_date);
5007 Fetch csr_asst_change_date into l_asst_change_date;
5008 l_csr_asst_chg_count := csr_asst_change_date%ROWCOUNT;
5009 Close csr_asst_change_date;
5010 --
5011 Open csr_asst_basis_change_date(l_max_create_date);
5012 Fetch csr_asst_basis_change_date into l_asst_basis_change_date,l_dflt_asst_basis_id;
5013 l_csr_asst_basis_chg_count := csr_asst_basis_change_date%ROWCOUNT;
5014 Close csr_asst_basis_change_date;
5015 --
5016 Open csr_txn_asst_change_date;
5017 Fetch csr_txn_asst_change_date into l_txn_asst_change_date;
5018 Close csr_txn_asst_change_date;
5019 --
5020 Open csr_txn_basis_change_date;
5021 Fetch csr_txn_basis_change_date into l_txn_basis_change_date,l_dflt_txn_basis_id,p_orig_basis_id;
5022 Close csr_txn_basis_change_date;
5023 --
5024 if (l_txn_asst_change_date is not null and l_txn_asst_change_date = p_current_date and l_asst_change_date is not null) then
5025 if g_debug then
5026 hr_utility.set_location('l_txn_asst_change_date '||l_txn_asst_change_date, 32);
5027 end if;
5028 l_txn_asst_change_date := null;
5029 end if;
5030
5031 if (l_txn_basis_change_date is not null and l_txn_basis_change_date = p_current_date) then
5032 if g_debug then
5033 hr_utility.set_location('l_txn_asst_change_date '||l_txn_asst_change_date, 33);
5034 end if;
5035 l_txn_basis_change_date := null;
5036 end if;
5037 --
5038 if g_debug then
5039 hr_utility.set_location('l_asst_change_date '||l_asst_change_date, 36);
5040 hr_utility.set_location('l_asst_basis_change_date: '||l_asst_basis_change_date, 37);
5041 hr_utility.set_location('l_txn_asst_change_date: '||l_txn_asst_change_date, 38);
5042 hr_utility.set_location('l_txn_basis_change_date: '||l_txn_basis_change_date, 39);
5043 end if;
5044 --
5045 if (l_txn_basis_change_date is not null and l_asst_basis_change_date is null) then
5046 if (l_csr_asst_chg_count = 0 and
5047 ( p_proposal_exists = 'YES' and p_previous_date <> l_txn_basis_change_date)
5048 ) then
5049 p_min_update_date := l_min_create_date;
5050 p_allow_basis_change := 'YES';
5051 p_allow_date_change := 'YES';
5052 p_status := 1;
5053 if g_debug then
5054 hr_utility.set_location('p_min_update_date '||p_min_update_date, 40);
5055 hr_utility.set_location('p_allow_basis_change '||p_allow_basis_change, 50);
5056 hr_utility.set_location('p_allow_date_change '||p_allow_date_change, 51);
5057 end if;
5058 end if;
5059 end if;
5060 --
5061 if (l_txn_basis_change_date is not null and l_asst_basis_change_date is null) then
5062 if (l_csr_asst_chg_count = 0 and
5063 ( p_proposal_exists = 'YES' and p_previous_date = l_txn_basis_change_date)
5064 ) then
5065 p_allow_basis_change := 'NO';
5066 p_allow_date_change := 'YES';
5067 p_status := 1;
5068 if g_debug then
5069 hr_utility.set_location('p_allow_date_change '||p_allow_date_change, 50);
5070 hr_utility.set_location('p_allow_basis_change '||p_allow_basis_change, 60);
5071 end if;
5072 end if;
5073 end if;
5074 --
5075 if (l_txn_asst_change_date is not null and l_txn_basis_change_date is null and l_asst_basis_change_date is null) then
5076 if (l_csr_asst_chg_count = 0) then
5077 p_min_update_date := l_min_create_date;
5078 p_basis_default_date := l_txn_asst_change_date;
5079 p_basis_default_min_date := l_txn_asst_change_date;
5080 p_allow_basis_change := 'YES';
5081 p_allow_date_change := 'YES';
5082 p_status := 2;
5083 if g_debug then
5084 hr_utility.set_location('p_min_update_date '||p_min_update_date, 65);
5085 hr_utility.set_location('p_basis_default_date '||p_basis_default_date, 70);
5086 hr_utility.set_location('p_basis_default_min_date '||p_basis_default_min_date, 71);
5087 hr_utility.set_location('p_allow_basis_change '||p_allow_basis_change, 72);
5088 end if;
5089 end if;
5090 end if;
5091 --
5092 if (l_asst_basis_change_date is null and l_asst_change_date is not null
5093 and l_txn_asst_change_date is null) then
5094 p_min_update_date := l_min_create_date;
5095 p_basis_default_date := l_asst_change_date;
5096 p_basis_default_min_date := l_asst_change_date;
5097 p_allow_basis_change := 'YES';
5098 p_allow_date_change := 'YES';
5099 p_status := 3;
5100 if g_debug then
5101 hr_utility.set_location('p_min_update_date '||p_min_update_date, 75);
5102 hr_utility.set_location('p_basis_default_date '||p_basis_default_date, 80);
5103 hr_utility.set_location('p_basis_default_min_date '||p_basis_default_min_date, 81);
5104 end if;
5105 end if;
5106 --
5107 if ((l_asst_basis_change_date is null and l_asst_change_date is not null)
5108 and (l_txn_basis_change_date is null and l_txn_asst_change_date is not null)) then
5109 p_min_update_date := l_min_create_date;
5110 p_basis_default_date := l_txn_asst_change_date;
5111 p_basis_default_min_date := l_asst_change_date;
5112 p_allow_basis_change := 'YES';
5113 p_allow_date_change := 'YES';
5114 p_status := 4;
5115 if g_debug then
5116 hr_utility.set_location('p_min_update_date '||p_min_update_date, 85);
5117 hr_utility.set_location('p_basis_default_date '||p_basis_default_date, 90);
5118 hr_utility.set_location('p_basis_default_min_date '||p_basis_default_min_date, 91);
5119 end if;
5120 end if;
5121 --
5122 if ((l_asst_basis_change_date is null and l_asst_change_date is not null)
5123 and (l_txn_basis_change_date is not null)
5124 and ( p_proposal_exists = 'YES' and p_previous_date <> l_txn_basis_change_date)
5125 ) then
5126 p_min_update_date := l_txn_basis_change_date;
5127 p_basis_default_date := l_txn_basis_change_date;
5128 p_allow_basis_change := 'YES';
5129 p_allow_date_change := 'YES';
5130 p_status := 4;
5131 if g_debug then
5132 hr_utility.set_location('p_min_update_date '||p_min_update_date, 95);
5133 hr_utility.set_location('p_basis_default_date '||p_basis_default_date, 100);
5134 end if;
5135 end if;
5136 --
5137 if ((l_asst_basis_change_date is null and l_asst_change_date is not null)
5138 and (l_txn_basis_change_date is not null)
5139 and ( p_proposal_exists = 'YES' and p_previous_date = l_txn_basis_change_date)
5140 ) then
5141 p_basis_default_min_date := l_txn_basis_change_date;
5142 p_basis_default_date := l_txn_basis_change_date;
5143 p_allow_basis_change := 'NO';
5144 p_allow_date_change := 'YES';
5145 p_status := 4;
5146 if g_debug then
5147 hr_utility.set_location('p_min_update_date '||p_min_update_date, 105);
5148 hr_utility.set_location('p_basis_default_date '||p_basis_default_date, 100);
5149 hr_utility.set_location('p_basis_default_min_date '||p_basis_default_min_date, 101);
5150 end if;
5151 end if;
5152 --
5153 if (l_asst_basis_change_date is not null and l_txn_basis_change_date is null) then
5154 if (l_csr_asst_basis_chg_count = 1) then
5155 p_min_update_date := l_asst_basis_change_date;
5156 p_basis_default_date := l_asst_basis_change_date;
5157 p_allow_basis_change := 'NO';
5158 p_allow_date_change := 'NO';
5159 p_status := 0;
5160 if g_debug then
5161 hr_utility.set_location('p_min_update_date '||p_min_update_date, 115);
5162 hr_utility.set_location('p_basis_default_date '||p_basis_default_date, 120);
5163 hr_utility.set_location('p_basis_default_date '||p_basis_default_date, 121);
5164 end if;
5165 end if;
5166 end if;
5167 --
5168 --
5169 --
5170 End get_update_param;
5171 --
5172 --
5173
5174 FUNCTION get_fte_factor(p_assignment_id IN NUMBER
5175 ,p_effective_date IN DATE
5176 ,p_transaction_id IN NUMBER)
5177 return NUMBER IS
5178 --
5179 l_fte_profile_value VARCHAR2(240) := fnd_profile.VALUE('BEN_CWB_FTE_FACTOR');
5180 --
5181 CURSOR csr_fte_BFTE
5182 IS
5183 select nvl(value, 1) val
5184 from per_assignment_budget_values_f
5185 where assignment_id = p_assignment_id
5186 and unit = 'FTE'
5187 and p_effective_date BETWEEN effective_start_date AND effective_end_date;
5188 --
5189 CURSOR csr_fte_BPFT
5190 IS
5191 select nvl(value, 1) val
5192 from per_assignment_budget_values_f
5193 where assignment_id = p_assignment_id
5194 and unit = 'PFT'
5195 and p_effective_date BETWEEN effective_start_date AND effective_end_date;
5196 --
5197 cursor get_asg_hours is
5198 select max(astHoursCol) as astHours,
5199 decode(max(frequencyCol)
5200 ,'Y',1
5201 ,'M',12
5202 ,'W',52
5203 ,'D',365
5204 ,1) as frequency
5205 from(
5206 select decode(NAME, 'P_FREQUENCY', VARCHAR2_VALUE) frequencyCol,
5207 decode(NAME, 'P_NORMAL_HOURS', NUMBER_VALUE) astHoursCol
5208 from hr_api_transaction_values
5209 where TRANSACTION_STEP_ID = (select TRANSACTION_STEP_ID from hr_api_transaction_steps
5210 where API_NAME = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API'
5211 and TRANSACTION_ID = p_transaction_id)
5212 ) ;
5213
5214 --changed by schowdhu for hire flow Bug#7307885 - 05-Sep-08
5215
5216 cursor chk_asg_rec is
5217 select null
5218 from per_all_assignments_f paa
5219 where paa.assignment_id = p_assignment_id;
5220 cursor get_ids is
5221 Select max(position_id) position_id , max(org_id) org_id, max(bg_id) bg_id from (
5222 select decode (NAME, 'P_POSITION_ID', NUMBER_VALUE) position_id,
5223 decode (NAME, 'P_ORGANIZATION_ID', NUMBER_VALUE) org_id,
5224 decode (NAME, 'P_BUSINESS_GROUP_ID', NUMBER_VALUE) bg_id
5225 from hr_api_transaction_values
5226 where TRANSACTION_STEP_ID = (select TRANSACTION_STEP_ID from hr_api_transaction_steps
5227 where API_NAME = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API'
5228 and TRANSACTION_ID = p_transaction_id));
5229
5230 cursor get_pos_hrs (l_pos_id in NUMBER) is
5231 select pos.working_hours,
5232 decode(pos.frequency
5233 ,'Y',1
5234 ,'M',12
5235 ,'W',52
5236 ,'D',365
5237 ,1)
5238 from hr_all_positions pos
5239 where pos.position_id = l_pos_id;
5240
5241 cursor get_org_hrs (l_org_id in NUMBER) is
5242 select fnd_number.canonical_to_number(org.org_information3) normal_hours
5243 , decode(org.org_information4
5244 ,'Y',1
5245 ,'M',12
5246 ,'W',52
5247 ,'D',365
5248 ,1)
5249 from HR_ORGANIZATION_INFORMATION org
5250 where org.organization_id(+) = l_org_id
5251 and org.org_information_context(+) = 'Work Day Information';
5252
5253 cursor get_bus_hrs(l_bg_id in NUMBER) is
5254 select fnd_number.canonical_to_number(bus.working_hours) normal_hours
5255 , decode(bus.frequency
5256 ,'Y',1
5257 ,'M',12
5258 ,'W',52
5259 ,'D',365
5260 ,1)
5261 from per_business_groups bus
5262 where bus.business_group_id = l_bg_id;
5263
5264 --
5265 l_fte_factor number := null;
5266 l_norm_hours_per_year number;
5267 l_hours_per_year number;
5268 l_hours NUMBER;
5269 l_frequency NUMBER;
5270
5271 --added for new hire flow
5272
5273 l_pos_id NUMBER;
5274 l_org_id NUMBER;
5275 l_bg_id NUMBER;
5276 l_exists varchar2(1);
5277
5278 --
5279 --
5280 BEGIN
5281 --
5282 if g_debug then
5283 hr_utility.set_location('get_fte_factor ', 5);
5284 end if;
5285
5286 if (l_fte_profile_value = 'NHBGWH') then
5287 open get_asg_hours;
5288 fetch get_asg_hours into l_hours,l_frequency;
5289
5290 if (get_asg_hours%found and l_hours is not null) THEN
5291 l_hours_per_year:=nvl(l_hours,0)*l_frequency;
5292 else
5293 l_hours_per_year:=null;
5294 end if;
5295 close get_asg_hours;
5296
5297 if l_hours_per_year is null then
5298 l_fte_factor := PER_SALADMIN_UTILITY.get_fte_factor(p_assignment_id,p_effective_date);
5299 RETURN l_fte_factor;
5300 end if;
5301
5302 if(nvl(l_hours_per_year,0) <> 0) then
5303 PER_PAY_PROPOSALS_POPULATE.get_norm_hours(p_assignment_id
5304 ,p_effective_date
5305 ,l_norm_hours_per_year);
5306
5307 --changed by schowdhu for hire flow
5308 l_hours := null;
5309 l_frequency := null;
5310
5311 open chk_asg_rec;
5312 fetch chk_asg_rec into l_exists;
5313 if (chk_asg_rec%notfound) then -- then hire flow
5314
5315 --find all the ids
5316 open get_ids;
5317 fetch get_ids into l_pos_id, l_org_id, l_bg_id;
5318 close get_ids;
5319
5320 --fetch the position hours, freqn
5321 if (l_pos_id is not null) then
5322 open get_pos_hrs(l_pos_id);
5323 fetch get_pos_hrs into l_hours, l_frequency;
5324 close get_pos_hrs;
5325 end if;
5326
5327 if (l_hours is null or l_frequency is null or l_org_id is not null ) then
5328 hr_utility.set_location('-1-', 20);
5329 open get_org_hrs(l_org_id);
5330 fetch get_org_hrs into l_hours, l_frequency;
5331 close get_org_hrs;
5332 end if;
5333
5334 if (l_hours is null or l_frequency is null or l_bg_id is not null) then
5335 hr_utility.set_location('-2-', 20);
5336 open get_bus_hrs(l_bg_id);
5337 fetch get_bus_hrs into l_hours, l_frequency;
5338 close get_bus_hrs;
5339 end if;
5340 l_norm_hours_per_year := nvl(l_hours, 0) * l_frequency;
5341 end if;
5342 close chk_asg_rec;
5343
5344 --changed by schowdhu for hire flow
5345
5346 if ( nvl(l_norm_hours_per_year,0) = 0) then
5347 l_fte_factor := 1;
5348 else
5349 l_fte_factor := l_hours_per_year/l_norm_hours_per_year;
5350 end if;
5351 else
5352 l_fte_factor := 1;
5353 end if;
5354 elsif (l_fte_profile_value = 'BFTE') then
5355 for r1 in csr_fte_BFTE loop
5356 l_fte_factor := r1.val;
5357 end loop;
5358 elsif (l_fte_profile_value = 'BPFT') then
5359 for r1 in csr_fte_BPFT loop
5360 l_fte_factor := r1.val;
5361 end loop;
5362 else
5363 l_fte_factor := 1;
5364 end if;
5365 -- fte can be more than 1. Bug #7497075 schowdhu
5366 --if (l_fte_factor is null or l_fte_factor > 1) then
5367 if (l_fte_factor is null) then
5368 l_fte_factor := 1;
5369 end if;
5370 --
5371
5372 RETURN l_fte_factor;
5373 END get_fte_factor;
5374
5375 --
5376 --
5377 End;
5378
5379