1 PACKAGE BODY PER_GB_ORG_INFO AS
2 /* $Header: pegborgp.pkb 120.12.12020000.4 2013/01/04 04:50:55 sampmand ship $ */
3 PROCEDURE CREATE_GB_ORG_INFO(
4 p_organization_id NUMBER
5 ,p_org_info_type_code VARCHAR2
6 ,p_org_information1 VARCHAR2
7 ,p_org_information3 VARCHAR2 --Added for bug 7338614
8 ,p_org_information10 VARCHAR2
9 ,p_org_information6 VARCHAR2 -- Added for the bug fix 13076448
10 ,p_org_information20 VARCHAR2
11 ) is
12 l_tax_string varchar2(30) ;
13 l_tax_district_reference varchar2(10);
14 l_tax_reference_number varchar2(15);
15 l_acc_off_ref varchar2(15); -- Added for the bug fix 13076448
16
17 --
18 Cursor csr_org_info_exists is
19 select 1
20 from hr_organization_information
21 where organization_id = p_organization_id
22 and org_information_context = p_org_info_type_code
23 and org_information1 = p_org_information1;
24 --
25 l_found NUMBER;
26 l_flag varchar2(1); -- Added for the bug fix 13076448
27 BEGIN
28 --
29 -- Added for GSI Bug 5472781
30 --
31 IF hr_utility.chk_product_install('Oracle Human Resources', 'GB') THEN
32 --
33 IF (p_org_info_type_code = 'Tax Details References') THEN
34 open csr_org_info_exists;
35 fetch csr_org_info_exists into l_found;
36 if csr_org_info_exists%found then
37 close csr_org_info_exists;
38 hr_utility.set_message (800, 'HR_GB_78132_EMP_PAYE_REF_EXIST');
39 hr_utility.set_message_token('PAYE_REF', p_org_information1);
40 hr_utility.raise_error;
41 end if;
42 close csr_org_info_exists;
43
44 --Modification starts - Moving the below error inside the above IF condition for the bug 8479004
45 --Added for bug 7338614
46 IF length(p_org_information3)>35 THEN
47 hr_utility.set_message (800, 'HR_GB_78140_EMP_STAT_NAME_MAX');
48 hr_utility.raise_error;
49 END IF;
50
51 IF length(p_org_information20)>12 THEN
52 hr_utility.set_message (800, 'PAY_GB_CMS_DEO_ERN_NUM');
53 hr_utility.raise_error;
54 END IF;
55
56 IF (p_org_information20 is not null or p_org_information20 <> '') then
57 IF (substr(p_org_information20,1,2) <> '50' and substr(p_org_information20,1,2) <> '51') THEN
58 hr_utility.set_message (800, 'PAY_GB_CMS_DEO_ERN');
59 hr_utility.raise_error;
60 END IF;
61 END IF;
62
63 --Bug 7338614 Ends
64 --Modification ends - Moving the below error inside the above IF condition for the bug 8479004
65
66 END IF;
67 --
68
69 IF p_org_information10 = 'UK' THEN /*Bug 5084055*/
70 IF (p_org_info_type_code = 'Tax Details References') THEN
71 BEGIN
72 l_tax_district_reference := substr( p_org_information1, 1, INSTR(p_org_information1,'/')-1 );
73 l_tax_reference_number := substr( p_org_information1, INSTR(p_org_information1,'/')+1 , length(p_org_information1) );
74 l_acc_off_ref := p_org_information6; -- Added for the bug fix 13076448
75
76 IF (l_tax_district_reference is NULL OR l_tax_reference_number is NULL
77 OR length(l_tax_district_reference) <> 3
78 OR length(l_tax_reference_number) < 0 OR length(l_tax_reference_number) > 10
79 OR pay_gb_eoy_magtape.validate_input(l_tax_reference_number, 'PAYE_REF') <> 0) THEN
80 hr_utility.set_message (800, 'HR_GB_78049_INV_EMP_PAYE_REF');
81 hr_utility.raise_error;
82 END IF;
83
84 IF( to_number(l_tax_district_reference) = to_number(l_tax_district_reference) ) THEN
85 IF to_number(l_tax_district_reference) < 1 then
86 hr_utility.set_message (800, 'HR_GB_78049_INV_EMP_PAYE_REF');
87 hr_utility.raise_error;
88 ELSE
89 null;
90 END IF;
91 END IF;
92
93 --Modification starts -Account office reference field validation
94 -- Added for the bug fix 13076448
95 IF l_acc_off_ref <> ' ' and l_acc_off_ref is not null then
96 IF (length(l_acc_off_ref) > 13
97 OR REGEXP_INSTR(l_acc_off_ref,'^([[:digit:]]{1,3})P([[:alpha:]]{1})([[:digit:]]{1,7})([[:digit:]]|X)$') = 0 ) THEN
98 l_flag := 'Y';
99 hr_utility.trace('ERROR. Account Reference Number is Invalid.');
100 hr_utility.raise_error;
101 END IF;
102 END IF;
103 -- Bug 13076448 Ends
104 --Modification ends - Account office reference field validation
105
106 EXCEPTION
107 WHEN OTHERS THEN
108 if l_flag = 'Y' then -- Added for the bug fix 13076448
109 hr_utility.set_message (800, 'HR_GB_78156_INV_ACC_OFF_REF_NO'); -- Added for the bug fix 13076448
110 else
111 hr_utility.set_message (800, 'HR_GB_78049_INV_EMP_PAYE_REF');
112 end if;
113 hr_utility.raise_error;
114 END;
115 END IF;
116 END IF;
117 END IF;
118 END CREATE_GB_ORG_INFO;
119
120
121
122 PROCEDURE UPDATE_GB_ORG_INFO(
123 p_org_info_type_code VARCHAR2
124 ,p_org_information1 VARCHAR2
125 ,p_org_information3 VARCHAR2 --Added for bug 7338614
126 ,p_org_information10 VARCHAR2
127 ,p_org_information6 VARCHAR2 -- Added for the bug fix 13076448
128 ,p_org_information20 VARCHAR2
129 ,p_org_information_id NUMBER
130 ) is
131
132 l_tax_district_reference varchar2(10);
133 l_tax_reference_number varchar2(15);
134 l_acc_off_ref varchar2(15); -- Added for the bug fix 13076448
135
136 --
137 Cursor csr_org_info_exists is
138 select 1
139 from hr_organization_information
140 where organization_id = (select organization_id
141 from hr_organization_information
142 where org_information_id = p_org_information_id)
143 and org_information_context = p_org_info_type_code
144 and org_information1 = p_org_information1
145 and org_information_id <> p_org_information_id;
146 --
147 l_found NUMBER;
148 l_flag varchar(1); -- Added for the bug fix 13076448
149 BEGIN
150 --
151 -- Added for GSI Bug 5472781
152 --
153
154
155 IF hr_utility.chk_product_install('Oracle Human Resources', 'HU') THEN
156 --
157 IF (p_org_info_type_code = 'Tax Details References') THEN
158 IF (p_org_information1 <> hr_api.g_varchar2) THEN
159 open csr_org_info_exists;
160 fetch csr_org_info_exists into l_found;
161 if csr_org_info_exists%found then
162 close csr_org_info_exists;
163 hr_utility.set_message (800, 'HR_GB_78132_EMP_PAYE_REF_EXIST');
164 hr_utility.set_message_token('PAYE_REF', p_org_information1);
165 hr_utility.raise_error;
166 end if;
167 close csr_org_info_exists;
168 END IF;
169 --Modification starts - Moving the below error inside the above IF condition for the bug 8479004
170 --Added for bug 7338614
171 IF length(p_org_information3)>35 THEN
172 hr_utility.set_message (800, 'HR_GB_78140_EMP_STAT_NAME_MAX');
173 hr_utility.raise_error;
174 END IF;
175
176 IF length(p_org_information20)>12 THEN
177 hr_utility.set_message (800, 'PAY_GB_CMS_DEO_ERN_NUM');
178 hr_utility.raise_error;
179 END IF;
180
181 IF (p_org_information20 is not null or p_org_information20 <> '') then
182 IF (substr(p_org_information20,1,2) <> '50' and substr(p_org_information20,1,2) <> '51') THEN
183 hr_utility.set_message (800, 'PAY_GB_CMS_DEO_ERN');
184 hr_utility.raise_error;
185 END IF;
186 END IF;
187 --Bug 7338614 Ends
188 --Modification ends - Moving the below error inside the above IF condition for the bug 8479004
189 END IF;
190 --
191
192 IF p_org_information10 = 'UK' then /*Bug 5084055*/
193 IF (p_org_info_type_code = 'Tax Details References') THEN
194 IF (p_org_information1 <> hr_api.g_varchar2) THEN
195 BEGIN
196 l_tax_district_reference := substr( p_org_information1, 1, INSTR(p_org_information1,'/')-1 );
197 l_tax_reference_number := substr( p_org_information1, INSTR(p_org_information1,'/')+1 , length(p_org_information1) );
198 l_acc_off_ref := p_org_information6; -- Added for the bug fix 13076448
199
200 IF (l_tax_district_reference is NULL OR l_tax_reference_number is NULL
201 OR length(l_tax_district_reference) <> 3
202 OR length(l_tax_reference_number) < 0 OR length(l_tax_reference_number) > 10
203 OR pay_gb_eoy_magtape.validate_input(l_tax_reference_number, 'PAYE_REF') <> 0 ) THEN
204 hr_utility.set_message (800, 'HR_GB_78049_INV_EMP_PAYE_REF');
205 hr_utility.raise_error;
206 END IF;
207
208 IF (to_number(l_tax_district_reference) = to_number(l_tax_district_reference) ) THEN
209 IF to_number(l_tax_district_reference) < 1 then
210 hr_utility.set_message (800, 'HR_GB_78049_INV_EMP_PAYE_REF');
211 hr_utility.raise_error;
212 ELSE
213 null;
214 END IF;
215 END IF;
216
217 --Modification starts -Account office reference field validation
218 -- Added for the bug fix 13076448
219 IF l_acc_off_ref <> ' ' and l_acc_off_ref is not null then
220 IF (length(l_acc_off_ref) > 13
221 OR REGEXP_INSTR(l_acc_off_ref,'^([[:digit:]]{1,3})P([[:alpha:]]{1})([[:digit:]]{1,7})([[:digit:]]|X)$') = 0 ) THEN
222 l_flag := 'Y';
223 hr_utility.trace('ERROR. Account Reference Number is Invalid.');
224 hr_utility.raise_error;
225 END IF;
226 END IF;
227 -- Bug 13076448 Ends
228 --Modification ends - Account office reference field validation
229
230 EXCEPTION
231 WHEN OTHERS THEN
232 if l_flag = 'Y' then -- Added for the bug fix 13076448
233 hr_utility.set_message (800, 'HR_GB_78156_INV_ACC_OFF_REF_NO'); -- Added for the bug fix 13076448
234 else
235 hr_utility.set_message (800, 'HR_GB_78049_INV_EMP_PAYE_REF');
236 end if;
237 hr_utility.raise_error;
238 END;
239 END IF;
240 END IF;
241 END IF;
242 END IF;
243 END UPDATE_GB_ORG_INFO;
244
245 END PER_GB_ORG_INFO;
[Home] [Help]