[Home] [Help]
PACKAGE BODY: APPS.PER_CN_EXTRA_PER_INFO_LEG_HOOK
Source
1 PACKAGE BODY per_cn_extra_per_info_leg_hook AS
2 /* $Header: pecnlhep.pkb 120.0 2005/05/31 06:52 appldev noship $ */
3 --
4 --
5 g_debug BOOLEAN ;
6 g_package VARCHAR2(32) ;
7 --------------------------------------------------------------------------
8 -- --
9 -- Name : CHECK_EXTRA_INFORMATION_EXISTS --
10 -- Type : Procedure --
11 -- Access : Public --
12 -- Description : Procedure is the driver procedure for the validation--
13 -- of the Extra Person Information. --
14 -- This procedure is the hook procedure for the --
15 -- Checking Extra person Information. --
16 -- Parameters : --
17 -- IN : p_person_extra_info_id NUMBER --
18 -- p_object_version_number NUMBER --
19 -- OUT : N/A --
20 -- RETURN : N/A --
21 -- --
22 -- Change History : --
23 --------------------------------------------------------------------------
24 -- Rev# Date Userid Description --
25 --------------------------------------------------------------------------
26 -- 1.0 13-Jul-2004 snekkala Created this procedure --
27 --------------------------------------------------------------------------
28 PROCEDURE check_extra_information_exists
29 (p_person_extra_info_id IN NUMBER
30 ,p_object_version_number IN NUMBER
31 )
32 IS
33 l_person_extra_info_id per_people_extra_info.person_extra_info_id%TYPE;
34 l_procedure VARCHAR2(80);
35 l_org_information_type per_people_extra_info.information_type%TYPE;
36 l_departure_date VARCHAR2(20);
37 l_visa_number VARCHAR2(80);
38
39 CURSOR csr_get_org_information_type IS
40 SELECT information_type
41 FROM per_people_extra_info
42 WHERE person_extra_info_id = p_person_extra_info_id;
43
44 CURSOR csr_check_detail_info_exists IS
45 SELECT detail.person_extra_info_id
46 FROM per_people_extra_info detail
47 WHERE detail.information_type = 'PER_ABROAD_DETAIL_INFO_CN'
48 AND p_person_extra_info_id=detail.pei_information1;
49
50 CURSOR csr_check_visa_info_used IS
51 SELECT detail.person_extra_info_id
52 FROM per_people_extra_info detail,per_people_extra_info visa
53 WHERE detail.information_type = 'PER_ABROAD_DETAIL_INFO_CN'
54 AND detail.pei_information5 = visa.pei_information1
55 AND visa.person_extra_info_id = p_person_extra_info_id;
56
57 CURSOR csr_get_departure_date IS
58 SELECT pei_information1
59 FROM per_people_extra_info
60 WHERE information_type = 'PER_ABROAD_MASTER_INFO_CN'
61 AND person_extra_info_id = p_person_extra_info_id;
62
63 CURSOR csr_get_visa_number IS
64 SELECT pei_information1
65 FROM per_people_extra_info
66 WHERE information_type = 'PER_VISA_INFO_CN'
67 AND person_extra_info_id = p_person_extra_info_id;
68
69 BEGIN
70 l_procedure := g_package || 'check_extra_information_exists';
71 g_debug := hr_utility.debug_enabled;
72
73 OPEN csr_get_org_information_type;
74 FETCH csr_get_org_information_type INTO l_org_information_type;
75 CLOSE csr_get_org_information_type;
76
77 IF l_org_information_type='PER_ABROAD_MASTER_INFO_CN' THEN
78 hr_cn_api.set_location(g_debug,l_procedure,10);
79
80 OPEN csr_check_detail_info_exists;
81 FETCH csr_check_detail_info_exists INTO l_person_extra_info_id;
82
83 IF csr_check_detail_info_exists%FOUND THEN
84
85 OPEN csr_get_departure_date;
86 FETCH csr_get_departure_date INTO l_departure_date;
87 CLOSE csr_get_departure_date;
88
89 CLOSE csr_check_detail_info_exists;
90
91 l_departure_date:=fnd_date.date_to_displaydate(fnd_date.string_to_date(SUBSTR(l_departure_date,1,10),'YYYY/MM/DD'));
92
93 hr_cn_api.set_location(g_debug,l_procedure,20);
94 hr_utility.set_message(800, 'HR_374619_FT_MASTER_EXISTS');
95 hr_utility.set_message_token('FIELD1',l_departure_date);
96 hr_utility.raise_error;
97 END IF;
98 CLOSE csr_check_detail_info_exists;
99
100 ELSIF l_org_information_type='PER_VISA_INFO_CN' THEN
101 hr_cn_api.set_location(g_debug,l_procedure,10);
102
103 OPEN csr_check_visa_info_used;
104 FETCH csr_check_visa_info_used INTO l_person_extra_info_id;
105
106 IF csr_check_visa_info_used%FOUND THEN
107
108 OPEN csr_get_visa_number;
109 FETCH csr_get_visa_number INTO l_visa_number;
110 CLOSE csr_get_visa_number;
111
112 CLOSE csr_check_visa_info_used;
113
114 hr_cn_api.set_location(g_debug,l_procedure,20);
115 hr_utility.set_message(800, 'HR_374620_FT_VISA_EXISTS');
116 hr_utility.set_message_token('VISA_NUMBER',l_visa_number);
117 hr_utility.raise_error;
118 END IF;
119 CLOSE csr_check_visa_info_used;
120 END IF;
121
122 EXCEPTION
123 WHEN OTHERS THEN
124 RAISE;
125
126 END check_extra_information_exists;
127
128 BEGIN
129
130 g_package := 'per_cn_extra_per_info_leg_hook.';
131 g_debug := hr_utility.debug_enabled;
132
133 END per_cn_extra_per_info_leg_hook;