1 PACKAGE BODY per_us_ethnic AS
2 /* $Header: peusethnic.pkb 120.0.12000000.1 2007/02/06 14:47:41 appldev noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1999 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : per_us_ethnic
21
22 Description : Package that updates ethnic code 9 to ethnic
23 code 3 for 2007.
24
25 Change List
26 -----------
27 Date Name Vers Bug No Description
28 ---- ---- ------ ------- -----------
29 29-OCT-06 ssouresr 115.0 Created
30 */
31
32 procedure ethnic_code_upd (errbuf out nocopy varchar2,
33 retcode out nocopy number)
34 is
35 ln_business_group_id NUMBER;
36 lv_business_group_name VARCHAR2(400);
37 lv_exists VARCHAR2(1);
38
39 v_errortext varchar2(512);
40 v_errorcode number;
41
42 cursor get_all_bgs is
43 select hoi.organization_id, hou.name
44 from hr_organization_units hou,
45 hr_organization_information hoi,
46 hr_organization_information hoi1
47 where hoi.org_information_context = 'Business Group Information'
48 and hoi.org_information9 = 'US'
49 and hou.organization_id = hoi.organization_id
50 and hou.organization_id = hoi1.organization_id
51 and hoi1.org_information_context = 'CLASS'
52 and hoi1.org_information1 = 'HR_BG'
53 and hoi1.org_information2 = 'Y';
54
55 begin
56
57 open get_all_bgs;
58 loop
59 fetch get_all_bgs into ln_business_group_id, lv_business_group_name;
60 if get_all_bgs%notfound then
61 exit;
62 end if;
63
64 UPDATE per_all_people_f
65 SET per_information1 = '3'
66 WHERE per_information1 = '9'
67 AND business_group_id = ln_business_group_id;
68
69 end loop;
70 close get_all_bgs;
71
72 exception
73 when others then
74 v_errorcode := SQLCODE;
75 v_errortext := SQLERRM;
76 hr_utility.trace('Error during update process: ' || v_errortext || ' ' || v_errorcode);
77 errbuf := v_errortext;
78 retcode := v_errorcode;
79 rollback;
80
81 end; -- end of ethnic_code_upd
82
83 END per_us_ethnic; -- end of package
[Home] [Help]