[Home] [Help]
PACKAGE BODY: APPS.HR_ORGANIZATION_INTERNAL
Source
1 package body HR_ORGANIZATION_INTERNAL as
2 /* $Header: hrorgbsi.pkb 120.5.12000000.2 2007/04/13 08:12:39 brsinha ship $ */
3
4 g_package varchar2(60) := 'hr_organization_internal.';
5
6 procedure HR_ORG_OPERATING_UNIT_UPLOAD
7 (
8 p_name in varchar2
9 ,p_organization_id in out nocopy number
10 ,p_date_from in date
11 ,p_date_to in date
12 ,p_internal_external_flag in varchar2
13 ,p_operating_unit in varchar2
14 )
15 IS
16 --
17 -- Declare cursors and local variables
18 l_organization_name varchar2(240) :=p_name;
19 l_organization_id number :=p_organization_id ;
20 l_date_from date :=p_date_from;
21 l_date_to date :=p_date_to;
22 l_internal_external_flag varchar2(30) :=p_internal_external_flag;
23 l_operating_unit_id number :=fnd_number.canonical_to_number(p_operating_unit);
24
25 ---
26 l_proc varchar2(80) := g_package||'hr_org_operating_unit_upload';
27 --
28 l_int_ext_temp varchar2(30);
29 l_business_group_id number;
30 l_org_information_id number;
31 l_duplicate_org_warning boolean;
32 l_object_version_number_org number;
33 l_object_version_number_inf number;
34 l_object_version_number number;
35 l_org_info_type_code varchar2(40);
36 l_chk_org_par varchar2(1):='N';
37 l_int_ext_check varchar2(1):='Y';
38 l_ou_check varchar2(1) := 'N';
39 l_name_check varchar2(1) := 'N';
40 --
41 --
42 --Cursor to check if Operating unit has been assigned or not
43 cursor check_ou_exists is
44 select hou1.org_information_id,hou1.org_information_context,hou1.object_version_number
45 ,ho1.object_version_number
46 from
47 hr_all_organization_units ho1,
48 hr_organization_information hou1
49 where ho1.organization_id=l_organization_id
50 and hou1.organization_id=ho1.organization_id
51 and hou1.org_information_context='Exp Organization Defaults';
52
53 -- Cursor to make sure that internal_external flag is not allowed to update
54 cursor get_internal_external_flag is
55 select 'Y'
56 from hr_all_organization_units
57 where organization_id=l_organization_id
58 and nvl(internal_external_flag,'X')=nvl(l_internal_external_flag,'X');
59
60 -- Cursor to check orgname, start_date or end_date is modified or not, If modified return object_version_number.
61 cursor get_object_version_number is
62 select object_version_number
63 from hr_all_organization_units
64 where organization_id=l_organization_id
65 and
66 (name <> l_organization_name
67 or date_from <> l_date_from
68 or nvl(date_to,to_date('31/12/4712','dd/mm/rrrr')) <>
69 nvl(l_date_to,to_date('31/12/4712','dd/mm/rrrr')));
70
71
72 --Cursor to check valid operating unit id is passed or not
73 cursor is_valid_ou_id is
74 select 'Y' from HR_OPERATING_UNITS
75 where trunc(sysdate) between trunc(date_from) and nvl(trunc(date_to), trunc(sysdate))
76 and organization_id = p_operating_unit;
77
78 -- Cursor to check valid organization name is passed or not.
79 cursor is_valid_name is
80 select 'Y' from hr_all_organization_units
81 where business_group_id = l_business_group_id
82 and name = l_organization_name ;
83 --
84 begin
85 --
86 l_business_group_id:=fnd_global.per_business_group_id ;
87 hr_utility.set_location('id'||l_operating_unit_id,1000);
88 hr_utility.set_location('name'||l_organization_name,1000);
89 hr_utility.set_location('OID'||l_organization_id,1000);
90 hr_utility.set_location('date'||l_date_from,1000);
91 hr_utility.set_location('flag'||l_internal_external_flag,1000);
92 hr_utility.set_location('Entering '||l_proc,10);
93 --
94 -- Validation of incoming values.
95 if (l_organization_name is null ) then
96 hr_utility.set_location ('Error!!' || l_organization_name, 1001);
97 Hr_utility.set_message(800,'HR_ORG_NAME_INVALID');
98 Hr_utility.raise_error;
99 end if ;
100 if ( l_organization_id is null) then
101 open is_valid_name ;
102 fetch is_valid_name into l_name_check ;
103 if ( is_valid_name %found ) THEN -- Organization exists, update needed
104 select organization_id into l_organization_id
105 from hr_all_organization_units
106 where business_group_id = l_business_group_id
107 and name = l_organization_name ;
108 /***********
109 hr_utility.set_location ('Error!!' || l_organization_name, 1002);
110 Hr_utility.set_message(800,'HR_289773_MULTI_ORG_DUPLICATE');
111 Hr_utility.raise_error;
112 */
113 else -- Organization does not exist.New org to be created
114 null;
115 end if;
116 end if;
117 if (l_date_from is null) then
118 hr_utility.set_location ('Error!!' || l_organization_name, 1003);
119 Hr_utility.set_message(800,'HR_START_DATE_INVALID');
120 Hr_utility.raise_error;
121 end if;
122 if ((p_internal_external_flag is null) or (p_internal_external_flag not in ('INT', 'EXT'))) then
123 hr_utility.set_location ('Error!!' , 1004);
124 Hr_utility.set_message(800,'HR_INT_EXT_INVALID');
125 Hr_utility.raise_error;
126 end if;
127
128 if (l_date_to is not null) then
129 if (l_date_to < l_date_from) then
130 hr_utility.set_location ('Error!!' , 1005);
131 Hr_utility.set_message(800,'HR_END_DATE_INVALID');
132 Hr_utility.raise_error;
133 end if;
134 end if;
135
136 if ( p_operating_unit is not null) then
137 open is_valid_ou_id;
138 fetch is_valid_ou_id into l_ou_check ;
139 if (is_valid_ou_id%NOTFOUND) then
140 hr_utility.set_location ('Error!!' , 1006);
141 Hr_utility.set_message(800,'HR_OU_INVALID');
142 Hr_utility.raise_error;
143 end if;
144 end if;
145 -- Validation ends
146 hr_utility.set_location(l_proc,20);
147
148 --check if hr organization exists
149 if (l_organization_id is not null) then
150
151 --Update row in HR_ORGANIZATION_INFORMATION Table
152
153 open get_internal_external_flag;
154 fetch get_internal_external_flag into l_int_ext_check;
155 if (get_internal_external_flag%notfound) then
156 Hr_utility.set_message(800,'HR_449749_INT_EXT_ERROR');
157 Hr_utility.raise_error;
158 end if;
159 open check_ou_exists;
160 fetch check_ou_exists into l_org_information_id,l_org_info_type_code,l_object_version_number_inf
161 ,l_object_version_number_org;
162
163 if (check_ou_exists%found) then
164 begin
165 hr_organization_api.update_org_information(
166 p_effective_date =>sysdate
167 ,p_org_information_id =>l_org_information_id
168 ,p_org_info_type_code =>l_org_info_type_code
169 ,p_org_information1 =>l_operating_unit_id
170 ,p_object_version_number=>l_object_version_number_inf
171 );
172 exception
173 when others then
174 Hr_utility.set_message(800,'HR_449738_UNABLE_TO_UPD_HRORG');
175 Hr_utility.raise_error;
176 end;
177
178 elsif(l_operating_unit_id is not null) then
179 begin
180 hr_organization_api.create_org_information(
181 p_effective_date =>sysdate
182 ,p_organization_id =>l_organization_id
183 ,p_org_information1 =>l_operating_unit_id
184 ,p_org_info_type_code =>'Exp Organization Defaults'
185 ,p_org_information_id =>l_org_information_id
186 ,p_object_version_number =>l_object_version_number);
187 exception
188 when others then
189 Hr_utility.set_message(800,'HR_449738_UNABLE_TO_UPD_HRORG');
190 Hr_utility.raise_error;
191 end;
192 end if;
193
194 -----verify if other parameters modified or not
195 begin
196 open get_object_version_number;
197 fetch get_object_version_number into l_object_version_number_org;
198 hr_utility.set_location(l_object_version_number_org,99);
199 --Update row in HR_ALL_ORGANIZATION_UNITS Table
200 if get_object_version_number%found then
201 hr_organization_api.update_organization(
202 p_effective_date =>sysdate
203 ,p_name =>l_organization_name
204 ,p_organization_id =>l_organization_id
205 ,p_internal_external_flag =>l_internal_external_flag
206 ,p_date_from =>l_date_from
207 ,p_date_to =>l_date_to
208 ,p_object_version_number =>l_object_version_number_org
209 ,p_duplicate_org_warning =>l_duplicate_org_warning
210 );
211 end if;
212 exception
213 when others then
214 Hr_utility.set_message(800,'HR_449738_UNABLE_TO_UPD_HRORG');
215 Hr_utility.raise_error;
216 end;
217 close get_internal_external_flag;
218 close get_object_version_number;
219 close check_ou_exists;
220
221
222 --
223 else
224 --Create an Hr organization
225 hr_utility.set_location(l_proc,30);
226 begin
227 hr_organization_api.create_hr_organization(
228 p_effective_date =>sysdate
229 ,p_business_group_id =>l_business_group_id
230 ,p_name =>l_organization_name
231 ,p_date_to =>l_date_to
232 ,p_date_from =>l_date_from
233 ,p_internal_external_flag =>l_internal_external_flag
234 ,p_enabled_flag =>'Y'
235 ,p_object_version_number_inf =>l_object_version_number_inf
236 ,p_object_version_number_org =>l_object_version_number_org
237 ,p_organization_id =>l_organization_id
238 ,p_org_information_id =>l_org_information_id
239 ,p_duplicate_org_warning =>l_duplicate_org_warning);
240 --
241 exception
242 when others then
243 Hr_utility.set_message(800,'HR_449737_UNABLE_TO_CREATE');
244 Hr_utility.raise_error;
245 end;
246
247 if(l_operating_unit_id is not null) then
248 begin
249 hr_organization_api.create_org_information(
250 p_effective_date =>sysdate
251 ,p_organization_id =>l_organization_id
252 ,p_org_information1 =>l_operating_unit_id
253 ,p_org_info_type_code =>'Exp Organization Defaults'
254 ,p_org_information_id =>l_org_information_id
255 ,p_object_version_number =>l_object_version_number
256 );
257 exception
258 when others then
259 Hr_utility.set_message(800,'HR_449737_UNABLE_TO_CREATE');
260 Hr_utility.raise_error;
261 end;
262 end if;
263 end if;
264 p_organization_id := l_organization_id ;
265 commit; -- bug 5722328
266 --
267 --
268 hr_utility.set_location('Leaving '||l_proc,40);
269 --
270 end HR_ORG_OPERATING_UNIT_UPLOAD;
271 end HR_ORGANIZATION_INTERNAL;