PACKAGE BODY: APPS.PER_IE_ASG_EXT_INFO

Source

1 PACKAGE BODY PER_IE_ASG_EXT_INFO AS
2 /* $Header: perieeit.pkb 120.0.12010000.1 2008/09/30 13:02:41 rsahai noship $ */
3 
4 PROCEDURE CREATE_IE_ASG_EXT_INFO(P_ASSIGNMENT_ID  NUMBER
5 					,P_INFORMATION_TYPE		VARCHAR2
6 					,P_AEI_ATTRIBUTE_CATEGORY	VARCHAR2
7 					,P_AEI_INFORMATION_CATEGORY   VARCHAR2
8 					,P_AEI_INFORMATION1      VARCHAR2
9 					,P_AEI_INFORMATION2      VARCHAR2
10 					,P_AEI_INFORMATION3      VARCHAR2
11 					,P_AEI_INFORMATION4      VARCHAR2
12 					,P_AEI_INFORMATION5      VARCHAR2
13 					)
14 IS
15 
16 CURSOR cur_ppsn_number IS
17 SELECT DISTINCT NATIONAL_IDENTIFIER
18 FROM per_all_assignments_f paaf, per_all_people_f papf
19 WHERE paaf.assignment_id = P_ASSIGNMENT_ID
20 AND paaf.person_id = papf.person_id;
21 
22 l_ppsn_number per_all_people_f.national_identifier%type;
23 
24 CURSOR cur_check_ppsn is
25 SELECT NVL(instr(substr(P_AEI_INFORMATION1,1,length(l_ppsn_number)),l_ppsn_number),0) check_ppsn
26 FROM dual;
27 
28 l_check_ppsn number;
29 
30 BEGIN
31 
32  IF P_AEI_INFORMATION_CATEGORY = 'IE_ASG_OVERRIDE' THEN
33 	 OPEN cur_ppsn_number;
34 	 FETCH cur_ppsn_number INTO l_ppsn_number;
35 	 CLOSE cur_ppsn_number;
36 
37 	 OPEN cur_check_ppsn;
38 	 FETCH cur_check_ppsn INTO l_check_ppsn;
39 	 CLOSE cur_check_ppsn;
40 
41 	 IF l_check_ppsn = 0 THEN
42 	   hr_utility.set_message(800,'HR_IE_PPSN_OVERRIDE');
43 	   hr_utility.raise_error;
44 	 END IF;
45  END IF;
46 END;
47 
48 PROCEDURE UPDATE_IE_ASG_EXT_INFO(P_ASSIGNMENT_EXTRA_INFO_ID  NUMBER
49 					,P_AEI_ATTRIBUTE_CATEGORY	VARCHAR2
50 					,P_AEI_INFORMATION_CATEGORY   VARCHAR2
51 					,P_AEI_INFORMATION1      VARCHAR2
52 					,P_AEI_INFORMATION2      VARCHAR2
53 					,P_AEI_INFORMATION3      VARCHAR2
54 					,P_AEI_INFORMATION4      VARCHAR2
55 					,P_AEI_INFORMATION5      VARCHAR2
56 					)
57 IS
58 
59 CURSOR cur_ppsn_number IS
60 SELECT DISTINCT NATIONAL_IDENTIFIER
61 FROM per_all_assignments_f paaf, per_all_people_f papf, per_assignment_extra_info paei
62 WHERE paei.assignment_extra_info_id = p_assignment_extra_info_id
63 and paei.aei_information_category = p_aei_information_category
64 AND paaf.assignment_id = paei.assignment_id
65 AND paaf.person_id = papf.person_id;
66 
67 l_ppsn_number per_all_people_f.national_identifier%type;
68 
69 CURSOR cur_check_ppsn is
70 SELECT NVL(instr(substr(P_AEI_INFORMATION1,1,length(l_ppsn_number)),l_ppsn_number),0) check_ppsn
71 FROM dual;
72 
73 l_check_ppsn number;
74 
75 BEGIN
76  IF P_AEI_INFORMATION_CATEGORY = 'IE_ASG_OVERRIDE' THEN
77 	 OPEN cur_ppsn_number;
78 	 FETCH cur_ppsn_number INTO l_ppsn_number;
79 	 CLOSE cur_ppsn_number;
80 
81 	 OPEN cur_check_ppsn;
82 	 FETCH cur_check_ppsn INTO l_check_ppsn;
83 	 CLOSE cur_check_ppsn;
84 
85 	 IF l_check_ppsn = 0 THEN
86 	   hr_utility.set_message(800,'HR_IE_PPSN_OVERRIDE');
87 	   hr_utility.raise_error;
88 	 END IF;
89  END IF;
90 END;
91 
92 END PER_IE_ASG_EXT_INFO;