1 PACKAGE BODY PER_RO_DEI_INFO AS
2 /* $Header: perrodei.pkb 120.1.12020000.5 2013/05/27 05:49:48 kbajaj noship $ */
3 g_package VARCHAR2(30) := 'PER_RO_DEI_INFO.';
4
5
6 PROCEDURE CHECK_RO_DEI_INFO(P_DOCUMENT_EXTRA_INFO_ID NUMBER,
7 P_DOCUMENT_TYPE_ID NUMBER,
8 P_DOCUMENT_NUMBER VARCHAR2,
9 P_DATE_FROM DATE,
10 P_DATE_TO DATE
11 ) IS
12
13 /*cursor csr_document_type is
14 select system_document_type from hr_document_types
15 where document_type_id = p_document_type_id; */
16
17 cursor csr_doc_no_unique is
18 select 'X' from hr_document_extra_info
19 where document_type_id = P_DOCUMENT_TYPE_ID
20 and document_number = P_DOCUMENT_NUMBER
21 and ( DOCUMENT_EXTRA_INFO_ID <> P_DOCUMENT_EXTRA_INFO_ID or P_DOCUMENT_EXTRA_INFO_ID is null)
22 and sysdate between date_from and date_to;
23
24
25 l_sys_doc_type hr_document_types.system_document_type%TYPE;
26 l_unique varchar2(1);
27 l_expiry_date date;
28 l_doc_exist number default 0;
29 --l_str varchar2(200);
30
31 BEGIN
32
33 BEGIN
34 select 1 into l_doc_exist from dual where p_document_type_id in
35 (select document_type_id from hr_document_types where category_code in ('RO_ID_INFO','RO_WORK_AUTH','RO_FISCAL_CERT'));
36 exception
37 when no_data_found then
38 l_doc_exist :=0;
39 end;
40
41 if l_doc_exist = 0 then
42 hr_utility.set_message(800, 'HR_520016_INVALID_DEI_TYPE');
43 hr_utility.raise_error;
44 end if;
45
46 /* open csr_document_type;
47 fetch csr_document_type into l_sys_doc_type;
48 close csr_document_type;
49
50 IF l_sys_doc_type NOT IN ('RO Identity Card','RO Passport','RO Identity Bulletin'
51 ,'RO Others Identity document','RO Residence card','RO Residence permit'
52 ,'RO Permanent workers','RO Season workers','RO Interns'
53 ,'RO Sportsmen','RO Cross-border workers','RO Nominal authorization'
54 ,'RO High-qualified workers','RO Foreign citizens') THEN
55 hr_utility.set_message(800, 'HR_520016_INVALID_DEI_TYPE');
56 hr_utility.raise_error;
57 end if; */
58
59 /* Document Number si mandatory */
60 if p_document_number is null then
61 hr_utility.set_message(800,'HR_375885_DEI_NO_REQD');
62 hr_utility.raise_error;
63 end if;
64
65 /* Document number should be unique */
66 open csr_doc_no_unique;
67 fetch csr_doc_no_unique into l_unique;
68 IF csr_doc_no_unique%FOUND then
69 hr_utility.set_location('l_unique '||l_unique ,15);
70 hr_utility.set_message(800,'HR_520015_DEI_NO_UNIQUE');
71 hr_utility.raise_error;
72 end if;
73 close csr_doc_no_unique;
74
75 /* Valid To Date is mandatory */
76 if P_DATE_TO is null then
77 hr_utility.set_message(800,'HR_520014_DEI_DATE_TO_REQD');
78 hr_utility.raise_error;
79 end if;
80
81 /* validating the expiry date in case of Fiscal Residence Certificate (usually the respective calendar year plus the next 60 days) */
82 if l_sys_doc_type = 'RO Foreign citizens' then
83 select ((add_months(trunc(P_DATE_FROM,'Y'),12)-1) + 60) into l_expiry_date from dual;
84 if l_expiry_date <> P_DATE_TO THEN
85 hr_utility.set_message(800,'HR_520016_DEI_DATE_TO_CHK');
86 hr_utility.raise_error;
87 end if;
88 end if;
89
90
91
92 END CHECK_RO_DEI_INFO;
93
94
95 PROCEDURE CREATE_RO_DEI_INFO(P_PERSON_ID NUMBER,
96 P_DOCUMENT_TYPE_ID NUMBER,
97 P_DOCUMENT_NUMBER VARCHAR2,
98 P_ISSUED_DATE DATE,
99 P_DATE_FROM DATE,
100 P_DATE_TO DATE) IS
101
102 cursor csr_doc_exists is
103 select 'x' from hr_document_extra_info
104 where person_id = p_person_id
105 and document_type_id = p_document_type_id
106 and (date_from between p_date_from and p_date_to or
107 date_to between p_date_from and p_date_to or
108 p_date_from between date_from and date_to);
109
110 l_exists varchar2(1);
111 l_proc varchar2(60);
112
113 BEGIN
114 l_proc:= g_package||'CREATE_RO_DEI_INFO';
115 hr_utility.set_location('Entering : '||l_proc,5);
116 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'RO') THEN
117 hr_utility.set_location('Leaving : '||l_proc,10);
118 return;
119 END IF;
120
121 CHECK_RO_DEI_INFO(P_DOCUMENT_EXTRA_INFO_ID => NULL,
122 P_DOCUMENT_TYPE_ID => p_document_type_id,
123 P_DOCUMENT_NUMBER => p_document_number,
124 P_DATE_FROM => p_date_from,
125 P_DATE_TO => p_date_to);
126
127 /* Another document of the same type should not exists in the same period when the current record is created */
128 open csr_doc_exists;
129 fetch csr_doc_exists into l_exists;
130 if csr_doc_exists%FOUND then
131 hr_utility.set_message(800,'HR_375877_OVERLAP_DEI_INFO');
132 hr_utility.raise_error;
133 end if;
134 close csr_doc_exists;
135
136 hr_utility.set_location('Leaving : '||l_proc,20);
137
138 END CREATE_RO_DEI_INFO;
139
140
141
142 PROCEDURE UPDATE_RO_DEI_INFO(P_DOCUMENT_EXTRA_INFO_ID NUMBER,
143 P_DOCUMENT_TYPE_ID NUMBER,
144 P_DOCUMENT_NUMBER VARCHAR2,
145 P_PERSON_ID NUMBER,
146 P_DATE_FROM DATE,
147 P_DATE_TO DATE,
148 P_ISSUED_DATE DATE) IS
149
150 cursor csr_doc_exists is
151 select null from hr_document_extra_info
152 where person_id = p_person_id
153 and document_type_id = p_document_type_id
154 and (date_from between p_date_from and p_date_to or
155 date_to between p_date_from and p_date_to or
156 p_date_from between date_from and date_to)
157 and document_extra_info_id <> p_document_extra_info_id;
158
159 l_exists varchar2(1);
160 l_proc varchar2(60);
161 BEGIN
162 l_proc:= g_package||'UPDATE_RO_DEI_INFO';
163 hr_utility.set_location('Entering : '||l_proc,5);
164 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'RO') THEN
165 hr_utility.set_location('Leaving : '||l_proc,10);
166 return;
167 END IF;
168
169
170 CHECK_RO_DEI_INFO(P_DOCUMENT_EXTRA_INFO_ID => p_document_extra_info_id,
171 P_DOCUMENT_TYPE_ID => p_document_type_id,
172 P_DOCUMENT_NUMBER => p_document_number,
173 P_DATE_FROM => p_date_from,
174 P_DATE_TO => p_date_to);
175
176 /* Another document of the same type should not exists in the same period when the current record is created */
177
178 open csr_doc_exists;
179 fetch csr_doc_exists into l_exists;
180 if csr_doc_exists%FOUND then
181 hr_utility.set_message(800,'HR_375877_OVERLAP_DEI_INFO');
182 hr_utility.raise_error;
183 end if;
184 close csr_doc_exists;
185
186 hr_utility.set_location('Leaving : '||l_proc,20);
187 END UPDATE_RO_DEI_INFO;
188
189 END PER_RO_DEI_INFO;
[Home] [Help]