DBA Data[Home] [Help]

PACKAGE BODY: APPS.HR_AE_PREVIOUS_EMPLOYMENT_API

Source


1 package body hr_ae_previous_employment_api as
2 /* $Header: pepemaei.pkb 120.0 2005/06/03 05:07:23 appldev noship $ */
3 
4    -- Package Variables
5    g_package   VARCHAR2(33) := 'hr_ae_previous_employment_api.';
6    g_debug boolean := hr_utility.debug_enabled;
7 
8 procedure create_ae_previous_employer
9 (  p_effective_date               IN      date
10   ,p_validate                     IN      boolean   default false
11   ,p_business_group_id            IN      number
12   ,p_person_id                    IN      number
13   ,p_party_id                     IN      number    default null
14   ,p_start_date                   IN      date      default null
15   ,p_end_date                     IN      date      default null
16   ,p_period_years                 IN      number    default null
17   ,p_period_months                IN      number    default null
18   ,p_period_days                  IN      number    default null
19   ,p_employer_name                IN      varchar2
20   ,p_employer_country             IN      varchar2  default null
21   ,p_employer_address             IN      varchar2  default null
22   ,p_employer_type                IN      varchar2  default null
23   ,p_employer_subtype             IN      varchar2  default null
24   ,p_description                  IN      varchar2  default null
25   ,p_all_assignments              IN      varchar2  default 'N'
26   ,p_pem_attribute_category       IN      varchar2  default null
27   ,p_pem_attribute1               IN      varchar2  default null
28   ,p_pem_attribute2               IN      varchar2  default null
29   ,p_pem_attribute3               IN      varchar2  default null
30   ,p_pem_attribute4               IN      varchar2  default null
31   ,p_pem_attribute5               IN      varchar2  default null
32   ,p_pem_attribute6               IN      varchar2  default null
33   ,p_pem_attribute7               IN      varchar2  default null
34   ,p_pem_attribute8               IN      varchar2  default null
35   ,p_pem_attribute9               IN      varchar2  default null
36   ,p_pem_attribute10              IN      varchar2  default null
37   ,p_pem_attribute11              IN      varchar2  default null
38   ,p_pem_attribute12              IN      varchar2  default null
39   ,p_pem_attribute13              IN      varchar2  default null
40   ,p_pem_attribute14              IN      varchar2  default null
41   ,p_pem_attribute15              IN      varchar2  default null
42   ,p_pem_attribute16              IN      varchar2  default null
43   ,p_pem_attribute17              IN      varchar2  default null
44   ,p_pem_attribute18              IN      varchar2  default null
45   ,p_pem_attribute19              IN      varchar2  default null
46   ,p_pem_attribute20              IN      varchar2  default null
47   ,p_pem_attribute21              IN      varchar2  default null
48   ,p_pem_attribute22              IN      varchar2  default null
49   ,p_pem_attribute23              IN      varchar2  default null
50   ,p_pem_attribute24              IN      varchar2  default null
51   ,p_pem_attribute25              IN      varchar2  default null
52   ,p_pem_attribute26              IN      varchar2  default null
53   ,p_pem_attribute27              IN      varchar2  default null
54   ,p_pem_attribute28              IN      varchar2  default null
55   ,p_pem_attribute29              IN      varchar2  default null
56   ,p_pem_attribute30              IN      varchar2  default null
57   ,p_termination_reason           IN      varchar2  default null
58   ,p_previous_employer_id         OUT NOCOPY     number
59   ,p_object_version_number        OUT NOCOPY     number
60   ) is
61 
62   --
63   -- Declare cursors and local variables
64   --
65 
66   l_proc                 varchar2(72) ;
67   l_effective_date       date;
68   l_legislation_code     per_business_groups.legislation_code%type;
69   l_discard_varchar2     varchar2(30);
70   --
71 
72     cursor check_legislation
73     (c_person_id      per_people_f.person_id%TYPE,
74      c_effective_date date
75     )
76   is
77     select bgp.legislation_code
78     from per_people_f per,
79          per_business_groups bgp
80     where per.business_group_id = bgp.business_group_id
81     and   per.person_id     = c_person_id
82     and   c_effective_date
83       between per.effective_start_date and per.effective_end_date;
84   --
85 begin
86 
87  l_proc :=  g_package||'create_ae_previous_employer';
88  if g_debug then
89   hr_utility.set_location('Entering:'|| l_proc, 5);
90  end if;
91   --
92   -- Initialise local variables
93   --
94   l_effective_date := trunc(p_effective_date);
95   --
96   -- Validation in addition to Row Handlers
97   --
98   -- Check that the person exists.
99   --
100   open check_legislation(p_person_id, l_effective_date);
101   fetch check_legislation into l_legislation_code;
102   if check_legislation%notfound then
103     close check_legislation;
104     hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
105     hr_utility.raise_error;
106   end if;
107   close check_legislation;
108  if g_debug then
109   hr_utility.set_location(l_proc, 20);
110  end if;
111   --
112   -- Check that the legislation of the specified business group is 'AE'.
113   --
114   if l_legislation_code <> 'AE' then
115     hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
116     hr_utility.set_message_token('LEG_CODE','AE');
117     hr_utility.raise_error;
118   end if;
119  if g_debug then
120   hr_utility.set_location(l_proc, 30);
121  end if;
122   --
123   -- Create the previous employer record using the create_previous_employer
124   --
125   hr_previous_employment_api.create_previous_employer
126 (  p_effective_date               =>      l_effective_date
127   ,p_validate                     =>      p_validate
128   ,p_business_group_id            =>      p_business_group_id
129   ,p_person_id                    =>      p_person_id
130   ,p_party_id                     =>      p_party_id
131   ,p_start_date                   =>      p_start_date
132   ,p_end_date                     =>      p_end_date
133   ,p_period_years                 =>      p_period_years
134   ,p_period_months                =>      p_period_months
135   ,p_period_days                  =>      p_period_days
136   ,p_employer_name                =>      p_employer_name
137   ,p_employer_country             =>      p_employer_country
138   ,p_employer_address             =>      p_employer_address
139   ,p_employer_type                =>      p_employer_type
140   ,p_employer_subtype             =>      p_employer_subtype
141   ,p_description                  =>      p_description
142   ,p_all_assignments              =>      p_all_assignments
143   ,p_pem_attribute_category       =>      p_pem_attribute_category
144   ,p_pem_attribute1               =>      p_pem_attribute1
145   ,p_pem_attribute2               =>      p_pem_attribute2
146   ,p_pem_attribute3               =>      p_pem_attribute3
147   ,p_pem_attribute4               =>      p_pem_attribute4
148   ,p_pem_attribute5               =>      p_pem_attribute5
149   ,p_pem_attribute6               =>      p_pem_attribute6
150   ,p_pem_attribute7               =>      p_pem_attribute7
151   ,p_pem_attribute8               =>      p_pem_attribute8
152   ,p_pem_attribute9               =>      p_pem_attribute9
153   ,p_pem_attribute10              =>      p_pem_attribute10
154   ,p_pem_attribute11              =>      p_pem_attribute11
155   ,p_pem_attribute12              =>      p_pem_attribute12
156   ,p_pem_attribute13              =>      p_pem_attribute13
157   ,p_pem_attribute14              =>      p_pem_attribute14
158   ,p_pem_attribute15              =>      p_pem_attribute15
159   ,p_pem_attribute16              =>      p_pem_attribute16
160   ,p_pem_attribute17              =>      p_pem_attribute17
161   ,p_pem_attribute18              =>      p_pem_attribute18
162   ,p_pem_attribute19              =>      p_pem_attribute19
163   ,p_pem_attribute20              =>      p_pem_attribute20
164   ,p_pem_attribute21              =>      p_pem_attribute21
165   ,p_pem_attribute22              =>      p_pem_attribute22
166   ,p_pem_attribute23              =>      p_pem_attribute23
167   ,p_pem_attribute24              =>      p_pem_attribute24
168   ,p_pem_attribute25              =>      p_pem_attribute25
169   ,p_pem_attribute26              =>      p_pem_attribute26
170   ,p_pem_attribute27              =>      p_pem_attribute27
171   ,p_pem_attribute28              =>      p_pem_attribute28
172   ,p_pem_attribute29              =>      p_pem_attribute29
173   ,p_pem_attribute30              =>      p_pem_attribute30
174   ,p_pem_information_category     =>      'AE'
175   ,p_pem_information1             =>      p_termination_reason
176   ,p_previous_employer_id         =>      p_previous_employer_id
177   ,p_object_version_number        =>      p_object_version_number
178   );
179 
180   --
181  if g_debug then
182   hr_utility.set_location(' Leaving:'||l_proc, 40);
183  end if;
184   --
185 end create_ae_previous_employer;
186 --------------------------------------------------------------------------------- -
187 
188 procedure update_ae_previous_employer
189 (  p_effective_date             IN      date
190   ,p_validate                   IN      boolean   default false
191   ,p_previous_employer_id       IN      number
192   ,p_start_date                 IN      date      default hr_api.g_date
193   ,p_end_date                   IN      date      default hr_api.g_date
194   ,p_period_years               IN      number    default hr_api.g_number
195   ,p_period_months              IN      number    default hr_api.g_number
196   ,p_period_days                IN      number    default hr_api.g_number
197   ,p_employer_name              IN      varchar2  default hr_api.g_varchar2
198   ,p_employer_country           IN      varchar2  default hr_api.g_varchar2
199   ,p_employer_address           IN      varchar2  default hr_api.g_varchar2
200   ,p_employer_type              IN      varchar2  default hr_api.g_varchar2
201   ,p_employer_subtype           IN      varchar2  default hr_api.g_varchar2
202   ,p_description                IN      varchar2  default hr_api.g_varchar2
203   ,p_all_assignments            IN      varchar2  default 'N'
204   ,p_pem_attribute_category     IN      varchar2  default hr_api.g_varchar2
205   ,p_pem_attribute1             IN      varchar2  default hr_api.g_varchar2
206   ,p_pem_attribute2             IN      varchar2  default hr_api.g_varchar2
207   ,p_pem_attribute3             IN      varchar2  default hr_api.g_varchar2
208   ,p_pem_attribute4             IN      varchar2  default hr_api.g_varchar2
209   ,p_pem_attribute5             IN      varchar2  default hr_api.g_varchar2
210   ,p_pem_attribute6             IN      varchar2  default hr_api.g_varchar2
211   ,p_pem_attribute7             IN      varchar2  default hr_api.g_varchar2
212   ,p_pem_attribute8             IN      varchar2  default hr_api.g_varchar2
213   ,p_pem_attribute9             IN      varchar2  default hr_api.g_varchar2
214   ,p_pem_attribute10            IN      varchar2  default hr_api.g_varchar2
215   ,p_pem_attribute11            IN      varchar2  default hr_api.g_varchar2
216   ,p_pem_attribute12            IN      varchar2  default hr_api.g_varchar2
217   ,p_pem_attribute13            IN      varchar2  default hr_api.g_varchar2
218   ,p_pem_attribute14            IN      varchar2  default hr_api.g_varchar2
219   ,p_pem_attribute15            IN      varchar2  default hr_api.g_varchar2
220   ,p_pem_attribute16            IN      varchar2  default hr_api.g_varchar2
221   ,p_pem_attribute17            IN      varchar2  default hr_api.g_varchar2
222   ,p_pem_attribute18            IN      varchar2  default hr_api.g_varchar2
223   ,p_pem_attribute19            IN      varchar2  default hr_api.g_varchar2
224   ,p_pem_attribute20            IN      varchar2  default hr_api.g_varchar2
225   ,p_pem_attribute21            IN      varchar2  default hr_api.g_varchar2
226   ,p_pem_attribute22            IN      varchar2  default hr_api.g_varchar2
227   ,p_pem_attribute23            IN      varchar2  default hr_api.g_varchar2
228   ,p_pem_attribute24            IN      varchar2  default hr_api.g_varchar2
229   ,p_pem_attribute25            IN      varchar2  default hr_api.g_varchar2
230   ,p_pem_attribute26            IN      varchar2  default hr_api.g_varchar2
231   ,p_pem_attribute27            IN      varchar2  default hr_api.g_varchar2
232   ,p_pem_attribute28            IN      varchar2  default hr_api.g_varchar2
233   ,p_pem_attribute29            IN      varchar2  default hr_api.g_varchar2
234   ,p_pem_attribute30            IN      varchar2  default hr_api.g_varchar2
235   ,p_termination_reason         IN      varchar2  default hr_api.g_varchar2
236   ,p_object_version_number      IN OUT NOCOPY  number
237   ) is
238 
239   --
240   -- Declare cursors and local variables
241   --
242 
243   l_proc                 varchar2(72) ;
244   l_effective_date       date;
245   l_legislation_code     per_business_groups.legislation_code%type;
246   l_discard_varchar2     varchar2(30);
247   --
248 
249     cursor check_legislation
250     (c_previous_employer_id      per_previous_employers.previous_employer_id%TYPE,
251      c_effective_date date
252     )
253   is
254     select bgp.legislation_code
255     from per_people_f per,
256          per_business_groups bgp,
257          per_previous_employers   pem
258     where per.business_group_id = bgp.business_group_id
259     and   per.person_id     =  pem.person_id
260     and   pem.previous_employer_id = c_previous_employer_id
261     and   c_effective_date
262       between per.effective_start_date and per.effective_end_date;
263   --
264 begin
265 
266  l_proc :=  g_package||'update_ae_previous_employer';
267  if g_debug then
268   hr_utility.set_location('Entering:'|| l_proc, 5);
269  end if;
270   --
271   -- Initialise local variables
272   --
273   l_effective_date := trunc(p_effective_date);
274   --
275   -- Validation in addition to Row Handlers
276   --
277   -- Check that the person exists.
278   --
279   open check_legislation(p_previous_employer_id, l_effective_date);
280   fetch check_legislation into l_legislation_code;
281   if check_legislation%notfound then
282     close check_legislation;
283     hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
284     hr_utility.raise_error;
285   end if;
286   close check_legislation;
287  if g_debug then
288   hr_utility.set_location(l_proc, 20);
289  end if;
290   --
291   -- Check that the legislation of the specified business group is 'AE'.
292   --
293   if l_legislation_code <> 'AE' then
294     hr_utility.set_message(801, 'HR_7961_PER_BUS_GRP_INVALID');
295     hr_utility.set_message_token('LEG_CODE','AE');
296     hr_utility.raise_error;
297   end if;
298  if g_debug then
299   hr_utility.set_location(l_proc, 30);
300  end if;
301   --
302   -- Create the previous employer record using the create_previous_employer
303   --
304   hr_previous_employment_api.update_previous_employer
305 (  p_effective_date             =>      l_effective_date
306   ,p_validate                   =>      p_validate
307   ,p_previous_employer_id       =>      p_previous_employer_id
308   ,p_start_date                 =>      p_start_date
309   ,p_end_date                   =>      p_end_date
310   ,p_period_years               =>      p_period_years
311   ,p_period_months              =>      p_period_months
312   ,p_period_days                =>      p_period_days
313   ,p_employer_name              =>      p_employer_name
314   ,p_employer_country           =>      p_employer_country
315   ,p_employer_address           =>      p_employer_address
316   ,p_employer_type              =>      p_employer_type
317   ,p_employer_subtype           =>      p_employer_subtype
318   ,p_description                =>      p_description
319   ,p_all_assignments            =>      p_all_assignments
320   ,p_pem_attribute_category     =>      p_pem_attribute_category
321   ,p_pem_attribute1             =>      p_pem_attribute1
322   ,p_pem_attribute2             =>      p_pem_attribute2
323   ,p_pem_attribute3             =>      p_pem_attribute3
324   ,p_pem_attribute4             =>      p_pem_attribute4
325   ,p_pem_attribute5             =>      p_pem_attribute5
326   ,p_pem_attribute6             =>      p_pem_attribute6
327   ,p_pem_attribute7             =>      p_pem_attribute7
328   ,p_pem_attribute8             =>      p_pem_attribute8
329   ,p_pem_attribute9             =>      p_pem_attribute9
330   ,p_pem_attribute10            =>      p_pem_attribute10
331   ,p_pem_attribute11            =>      p_pem_attribute11
332   ,p_pem_attribute12            =>      p_pem_attribute12
333   ,p_pem_attribute13            =>      p_pem_attribute13
334   ,p_pem_attribute14            =>      p_pem_attribute14
335   ,p_pem_attribute15            =>      p_pem_attribute15
336   ,p_pem_attribute16            =>      p_pem_attribute16
337   ,p_pem_attribute17            =>      p_pem_attribute17
338   ,p_pem_attribute18            =>      p_pem_attribute18
339   ,p_pem_attribute19            =>      p_pem_attribute19
340   ,p_pem_attribute20            =>      p_pem_attribute20
341   ,p_pem_attribute21            =>      p_pem_attribute21
342   ,p_pem_attribute22            =>      p_pem_attribute22
343   ,p_pem_attribute23            =>      p_pem_attribute23
344   ,p_pem_attribute24            =>      p_pem_attribute24
345   ,p_pem_attribute25            =>      p_pem_attribute25
346   ,p_pem_attribute26            =>      p_pem_attribute26
347   ,p_pem_attribute27            =>      p_pem_attribute27
348   ,p_pem_attribute28            =>      p_pem_attribute28
349   ,p_pem_attribute29            =>      p_pem_attribute29
350   ,p_pem_attribute30            =>      p_pem_attribute30
351   ,p_pem_information_category   =>      'AE'
352   ,p_pem_information1           =>      p_termination_reason
353   ,p_object_version_number      =>      p_object_version_number
354   );
355     --
356  if g_debug then
357   hr_utility.set_location(' Leaving:'||l_proc, 40);
358  end if;
359   --
360 end update_ae_previous_employer;
361 
362 end hr_ae_previous_employment_api;