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