[Home] [Help]
1: PACKAGE BODY hr_interview AS
2: /* $Header: peintviw.pkb 120.1 2006/01/13 06:10:33 irgonzal noship $ */
3: /*
4: ******************************************************************
5: * *
19: * England. *
20: * *
21: ****************************************************************** */
22: /*
23: Name : hr_interview (BODY)
24:
25: Description : This package declares procedures required to
26: INSERT, UPDATE and DELETE Assignment Statuses for
27: Applicant Interviews called from PERREAB.
89: p_int_date := p_idate;
90: --
91: BEGIN
92: --
93: hr_utility.set_location('hr_interview.insert_interview',1);
94: --
95: -- This for loop has a purpose to the lock all the assignment records
96: -- specified by the cursor
97: --
98: FOR ass_rec_ins IN select_ass_for_insert LOOP
99: NULL;
100: END LOOP;
101: --
102: hr_utility.set_location('hr_interview.insert_interview',2);
103: --
104: SELECT 'Y'
105: INTO p_dummy
106: FROM sys.dual
121: hr_utility.raise_error;
122: END IF;
123: --
124: -- Insert the Interview
125: hr_utility.set_location('hr_interview.insert_interview',3);
126: --
127: INSERT INTO per_assignments_f
128: (
129: assignment_id
318: hr_utility.set_message_token('PROCEDURE','INSERT_INTERVIEW');
319: hr_utility.set_message_token('STEP','3');
320: hr_utility.raise_error;
321: ELSE
322: hr_utility.set_location('hr_interview.insert_interview',4);
323: --
324: UPDATE per_assignments_f
325: SET effective_start_date = p_int_date
326: , assignment_status_type_id = p_assignment_status_type_id
534: -- Check if status was the only field to change
535: -- IF it is then CASE A B and C
536: --
537: -- CASE A B C D
538: hr_utility.set_location('hr_interview.delete_interview',1);
539: --
540: -- This for loop has a purpose to the lock all the assignment records
541: -- specified by the cursor
542: FOR ass_rec_del IN select_ass_for_delete LOOP
542: FOR ass_rec_del IN select_ass_for_delete LOOP
543: NULL;
544: END LOOP;
545: --
546: hr_utility.set_location('hr_interview.delete_interview',2);
547: --
548: -- #4894555: replaced SQL statement with function call.
549: --
550: p_dummy := chk_duplicate(p_assignment_id, p_int_date, TRUE);
548: -- #4894555: replaced SQL statement with function call.
549: --
550: p_dummy := chk_duplicate(p_assignment_id, p_int_date, TRUE);
551: --
552: hr_utility.set_location('hr_interview.delete_interview',25);
553: --
554: --
555: BEGIN
556: --
553: --
554: --
555: BEGIN
556: --
557: hr_utility.set_location('hr_interview.delete_interview',3);
558: --
559: SELECT a.assignment_status_type_id
560: INTO p_old_interview_status
561: FROM per_assignments_f a
580: --
581: -- delete the row that has effective start date = interview start date
582: -- done For CASE A, B, and C
583: --
584: hr_utility.set_location('hr_interview.delete_interview',4);
585: --
586: UPDATE per_assignments_f a
587: SET effective_end_date = (SELECT effective_end_date
588: FROM per_assignments_f b
601: hr_utility.set_message_token('STEP','3');
602: hr_utility.raise_error;
603: END IF;
604: --
605: hr_utility.set_location('hr_interview.delete_interview',5);
606: --
607: DELETE per_assignments_f a
608: WHERE a.assignment_id = p_assignment_id
609: AND a.effective_start_date = p_int_date;
621: -- Check for CASE A if effective_end_date = EOT then no need to ripple
622: --
623: BEGIN
624: --
625: hr_utility.set_location('hr_interview.delete_interview',6);
626: --
627: SELECT a.effective_end_date
628: INTO p_dummy_date
629: FROM per_assignments_f a
644: BEGIN
645: --
646: -- CASE D when the last record no need to ripple forward.
647: --
648: hr_utility.set_location('hr_interview.delete_interview',7);
649: --
650: SELECT d.assignment_status_type_id
651: INTO p_previous_status
652: FROM per_assignments_f d
675: --
676: -- Test for Case C and D a new assignment corresponds to start date of
677: -- another interview.
678: --
679: hr_utility.set_location('hr_interview.delete_interview',8);
680: --
681: SELECT MIN(e.date_start)
682: INTO p_nxt_interview_date
683: FROM per_events e
694: IF p_dummy = 'Y' THEN
695: --
696: BEGIN
697: --
698: hr_utility.set_location('hr_interview.delete_interview',9);
699: --
700: SELECT d.assignment_status_type_id
701: INTO p_previous_status
702: FROM per_assignments_f d
715: ELSE
716: --
717: BEGIN
718: --
719: hr_utility.set_location('hr_interview.delete_interview',10);
720: --
721: SELECT d.assignment_status_type_id
722: INTO p_previous_status
723: FROM per_assignments_f d
736: --
737: hr_utility.trace('p_previous_status '|| p_previous_status);
738: hr_utility.trace('p_old_interview_status '|| p_old_interview_status);
739: --
740: hr_utility.set_location('hr_interview.delete_interview',11);
741: --
742: SELECT MIN(c.effective_start_date)
743: INTO p_new_status_type_date
744: FROM per_assignments_f c
750: substr(to_char(p_new_status_type_date,'DD-MON-YYYY'),1,11));
751: --
752: -- Ripple the pre interview status CASE B,C and D.
753: --
754: hr_utility.set_location('hr_interview.delete_interview',12);
755: --
756: UPDATE per_assignments_f a
757: SET assignment_status_type_id = p_previous_status
758: , last_updated_by = p_last_updated_by
766: to_date('31/12/4712','DD/MM/YYYY')))));
767: --
768: IF SQL%ROWCOUNT = 0 THEN
769: --
770: hr_utility.set_location('hr_interview.delete_interview',13);
771: --
772: IF p_nxt_interview_date IS NULL AND p_new_status_type_date IS NULL THEN
773: --
774: hr_utility.set_location('hr_interview.delete_interview',14);
770: hr_utility.set_location('hr_interview.delete_interview',13);
771: --
772: IF p_nxt_interview_date IS NULL AND p_new_status_type_date IS NULL THEN
773: --
774: hr_utility.set_location('hr_interview.delete_interview',14);
775: --
776: UPDATE per_assignments_f a
777: SET assignment_status_type_id = p_previous_status
778: , last_updated_by = p_last_updated_by
836: p_matches := 'N';
837: --
838: BEGIN
839: --
840: hr_utility.set_location('hr_interview.update_interview',1);
841: --
842: -- This for loop has a purpose to the lock all the assignment records
843: -- specified by the cursor
844: --
845: FOR ass_rec_upd IN select_ass_for_update LOOP
846: NULL;
847: END LOOP;
848: --
849: hr_utility.set_location('hr_interview.update_interview',2);
850: --
851: SELECT 'Y'
852: INTO p_no_update
853: FROM sys.dual
871: hr_utility.set_message(801,'HR_6629_APPL_STATUS_MOVE');
872: hr_utility.raise_error;
873: END IF;
874: --
875: hr_utility.set_location('hr_interview.update_interview',3);
876: --
877: -- #4894555: replace SQL with function call.
878: --
879: p_dummy := chk_duplicate(p_assignment_id, p_old_int_date, FALSE);
881: --
882: hr_utility.trace('p_dummy is'||p_dummy);
883: IF p_dummy = 'Y' THEN
884: --
885: hr_utility.set_location('hr_interview.update_interview',4);
886: BEGIN
887: SELECT 'Y'
888: INTO p_matches
889: FROM per_assignments_f a
892: EXCEPTION
893: WHEN NO_DATA_FOUND THEN NULL;
894: END;
895: --
896: hr_utility.set_location('hr_interview.update_interview',5);
897: --
898: IF p_matches = 'N' THEN /* CASE A */
899: --
900: hr_utility.set_location('hr_interview.update_interview',6);
896: hr_utility.set_location('hr_interview.update_interview',5);
897: --
898: IF p_matches = 'N' THEN /* CASE A */
899: --
900: hr_utility.set_location('hr_interview.update_interview',6);
901: UPDATE per_assignments_f a
902: SET a.effective_start_date = p_int_date
903: , a.last_updated_by = p_last_updated_by
904: , a.last_update_login = p_last_update_login
912: hr_utility.set_message_token('STEP','1');
913: hr_utility.raise_error;
914: END IF;
915: --
916: hr_utility.set_location('hr_interview.update_interview',7);
917: UPDATE per_assignments_f a
918: SET a.effective_end_date = p_int_date -1
919: , a.last_updated_by = p_last_updated_by
920: , a.last_update_login = p_last_update_login
930: END IF;
931: --
932: ELSE /* CASE C */
933: --
934: hr_utility.set_location('hr_interview.update_interview',8);
935: DELETE per_assignments_f a
936: WHERE a.assignment_id = p_assignment_id
937: AND a.effective_start_date = p_old_int_date;
938: --
942: hr_utility.set_message_token('STEP','3');
943: hr_utility.raise_error;
944: END IF;
945: --
946: hr_utility.set_location('hr_interview.update_interview',9);
947: UPDATE per_assignments_f a
948: SET a.effective_end_date = p_int_date -1
949: , a.last_updated_by = p_last_updated_by
950: , a.last_update_login = p_last_update_login
968: IF p_int_date > p_old_int_date THEN
969: --
970: p_no_update := 'N';
971: --
972: hr_utility.set_location('hr_interview.update_interview',10);
973: BEGIN
974: --
975: SELECT 'Y'
976: INTO p_no_update
990: hr_utility.set_message(801,'HR_6629_APPL_STATUS_MOVE');
991: hr_utility.raise_error;
992: END IF;
993: --
994: hr_utility.set_location('hr_interview.update_interview',11);
995: --
996: SELECT a.assignment_status_type_id
997: INTO p_ass_status_type_id
998: FROM per_assignments_f a
1005: hr_utility.set_message_token('STEP','5');
1006: hr_utility.raise_error;
1007: END IF;
1008: --
1009: hr_utility.set_location('hr_interview.update_interview',12);
1010: --
1011: INSERT INTO per_assignments_f
1012: (
1013: assignment_id
1201: hr_utility.set_message_token('PROCEDURE','UPDATE_INTERVIEW');
1202: hr_utility.set_message_token('STEP','6');
1203: hr_utility.raise_error;
1204: ELSE
1205: hr_utility.set_location('hr_interview.update_interview',13);
1206: UPDATE per_assignments_f a
1207: SET a.effective_start_date = p_int_date
1208: , a.last_updated_by = p_last_updated_by
1209: , a.last_update_login = p_last_update_login
1220: END IF;
1221: ELSE
1222: -- insert back in time
1223: --
1224: hr_utility.set_location('hr_interview.update_interview',14);
1225: SELECT a.assignment_status_type_id
1226: INTO p_ass_status_type_id
1227: FROM per_assignments_f a
1228: WHERE a.assignment_id = p_assignment_id
1234: hr_utility.set_message_token('STEP','8');
1235: hr_utility.raise_error;
1236: END IF;
1237: --
1238: hr_utility.set_location('hr_interview.update_interview',15);
1239: SELECT a.effective_start_date
1240: INTO p_old_prev_start_date
1241: FROM per_assignments_f a
1242: WHERE a.assignment_id = p_assignment_id
1249: hr_utility.raise_error;
1250: END IF;
1251: --
1252: -- Insert the Interview
1253: hr_utility.set_location('hr_interview.update_interview',16);
1254: --
1255: INSERT INTO per_assignments_f
1256: (
1257: assignment_id
1445: hr_utility.set_message_token('PROCEDURE','UPDATE_INTERVIEW');
1446: hr_utility.set_message_token('STEP','10');
1447: hr_utility.raise_error;
1448: ELSE
1449: hr_utility.set_location('hr_interview.update_interview',17);
1450: --
1451: UPDATE per_assignments_f a
1452: SET a.effective_end_date = p_int_date -1
1453: , a.last_updated_by = p_last_updated_by
1471: --
1472: END update_interview;
1473: --
1474: --
1475: end hr_interview;