[Home] [Help]
PACKAGE BODY: APPS.POR_LOAD_HR_DATA
Source
1 PACKAGE BODY POR_LOAD_HR_DATA as
2 /* $Header: PORLHRLB.pls 115.14 2004/02/05 23:20:36 skaushik ship $ */
3 validation_error EXCEPTION;
4 PROCEDURE insert_update_emp_data
5 IS
6
7 l_employee_number VARCHAR2(30);
8 l_first_name VARCHAR2(30);
9 l_last_name VARCHAR2(40);
10 l_middle_name VARCHAR2(40); /* 2610687 Set the variable size as mentioned in Employee Loader Readme */
11 l_sex VARCHAR2(30);
12 l_start_date VARCHAR2(30);
13 l_end_date VARCHAR2(30);
14 l_business_group_name VARCHAR2(240);
15 l_location_code VARCHAR2(30);
16 l_set_of_books VARCHAR2(30);
17 l_default_expense_account VARCHAR2(50); /*2610687*/
18 l_job_name VARCHAR2(30);
19 l_supervisor_emp_num VARCHAR2(30);
20 l_user_name VARCHAR2(30);
21 l_password VARCHAR2(30);
22 l_email_address VARCHAR2(30);
23 l_work_telephone VARCHAR2(30);
24 l_loader_status VARCHAR2(30);
25
26 CURSOR c_employees IS
27 SELECT employee_number,first_name,last_name, sex, start_date, end_date,
28 business_group_name, location_code,set_of_books,default_expense_account,
29 job_name, supervisor_emp_num, user_name,password, email_address,
30 work_telephone
31 FROM por_employee_loader_values ORDER BY last_update_date ASC;
32
33 BEGIN
34
35 OPEN c_employees;
36 LOOP
37 BEGIN
38 FETCH c_employees INTO l_employee_number,l_first_name,l_last_name,
39 l_sex,l_start_date,l_end_date,l_business_group_name,l_location_code,
40 l_set_of_books,l_default_expense_account,l_job_name,l_supervisor_emp_num,
41 l_user_name,l_password,l_email_address,l_work_telephone;
42
43 EXIT WHEN c_employees%NOTFOUND;
44
45 POR_LOAD_EMPLOYEE.insert_update_employee_info (
46 x_employee_number => l_employee_number,
47 x_first_name => l_first_name,
48 x_last_name => l_last_name,
49 x_sex => l_sex,
50 x_effective_start_date => l_start_date,
51 x_effective_end_date => l_end_date,
52 x_business_group_name => l_business_group_name,
53 x_location_name => l_location_code,
54 x_default_employee_account => l_default_expense_account,
55 x_set_of_books_name => l_set_of_books ,
56 x_supervisor_emp_number => l_supervisor_emp_num,
57 x_job_name => l_job_name,
58 x_email_address => l_email_address,
59 x_work_telephone => l_work_telephone);
60
61 POR_LOAD_FND_USER.insert_update_user_info (
62 x_employee_number => l_employee_number,
63 x_user_name => l_user_name,
64 x_password => l_password,
65 x_email_address => l_email_address);
66
67
68 UPDATE por_employee_loader_values
69 SET loader_status = 'complete'
70 WHERE employee_number = l_employee_number;
71
72 EXCEPTION
73 WHEN OTHERS THEN
74 ERROR_STACK.PUSHMESSAGE('*****************','ICX');
75 ERROR_STACK.PUSHMESSAGE('The employee or assignment information for '||l_last_name||' '||l_first_name||' could not be loaded','ICX');
76
77 IF (hr_utility.get_message() IS NULL OR
78 hr_utility.get_message() = '') THEN
79 ERROR_STACK.PUSHMESSAGE(SQLCODE || ' ' ||SQLERRM,'ICX');
80 ELSE
81 ERROR_STACK.PUSHMESSAGE(hr_utility.get_message(),'ICX');
82 END IF;
83
84 UPDATE por_employee_loader_values
85 SET loader_status = 'failure'
86 WHERE employee_number = l_employee_number;
87
88 END;
89
90 END LOOP;
91
92 CLOSE c_employees;
93
94 COMMIT;
95
96 BEGIN
97 IF (ERROR_STACK.GETMSGCOUNT > 0) THEN
98 RAISE validation_error;
99 END IF;
100 END;
101
102 EXCEPTION
103 WHEN validation_error THEN
104 RAISE;
105 WHEN OTHERS THEN
106 RAISE;
107
108 END insert_update_emp_data;
109
110
111 PROCEDURE insert_update_loc_data
112 IS
113
114 l_location_code VARCHAR2(30);
115 l_business_group VARCHAR2(240);
116 l_effective_date DATE;
117 l_description VARCHAR2(240);
118 l_address_style VARCHAR2(30);
119 l_address_line_1 VARCHAR2(60);
120 l_address_line_2 VARCHAR2(60);
121 l_address_line_3 VARCHAR2(60);
122 l_city VARCHAR2(30);
123 l_state VARCHAR2(30);
124 l_county VARCHAR2(30);
125 l_postal_code VARCHAR2(30);
126 l_country VARCHAR2(30);
127 l_telephone VARCHAR2(30);
128 l_fax VARCHAR2(30);
129 l_ship_to_location VARCHAR2(30);
130 l_ship_to_flag VARCHAR2(30);
131 l_bill_to_flag VARCHAR2(30);
132 l_receiving_to_flag VARCHAR2(30);
133 l_office_site VARCHAR2(30);
134 l_internal_site VARCHAR2(30);
135 l_inventory_Org VARCHAR2(30);
136 l_tax_name VARCHAR2(30);
137
138 CURSOR c_locations IS
139 SELECT location_code,business_group, effective_date,description,
140 address_style,address_line_1, address_line_2,address_line_3,
141 city,state,county,postal_code,country,telephone,fax,ship_to_location,
142 ship_to_flag,bill_to_flag,receiving_to_flag,office_site,
143 internal_site,inventory_Org,tax_name
144 FROM por_location_loader_values ORDER BY last_update_date ASC;
145
146 BEGIN
147
148 OPEN c_locations;
149 LOOP
150 BEGIN
151 FETCH c_locations INTO l_location_code,l_business_group,l_effective_date,
152 l_description,l_address_style,l_address_line_1,l_address_line_2,
153 l_address_line_3,l_city,l_state,l_county,l_postal_code,l_country,
154 l_telephone,l_fax,l_ship_to_location,l_ship_to_flag,l_bill_to_flag,
155 l_receiving_to_flag,l_office_site,l_internal_site,l_inventory_Org,
156 l_tax_name;
157
158 EXIT WHEN c_locations%NOTFOUND;
159
160
161 POR_LOAD_LOCATION.insert_update_location_info (
162 x_location_code => l_location_code,
163 x_business_grp_name => l_business_group,
164 x_effective_date => l_effective_date,
165 x_description => l_description,
166 x_address_style => l_address_style,
167 x_address_line_1 => l_address_line_1,
168 x_address_line_2 => l_address_line_2,
169 x_address_line_3 => l_address_line_3,
170 x_city => l_city,
171 x_state => l_state,
172 x_county => l_county,
173 x_country => l_country,
174 x_postal_code => l_postal_code,
175 x_telephone_number_1 => l_telephone,
176 x_telephone_number_2 => l_fax,
177 x_shipToLocation => l_ship_to_location,
178 x_ship_to_flag => l_ship_to_flag,
179 x_bill_to_flag => l_bill_to_flag,
180 x_receiving_site => l_receiving_to_flag,
181 x_office_site_flag => l_office_site,
182 x_inv_org => l_inventory_Org,
183 x_tax_name => l_tax_name);
184
185
186 UPDATE por_location_loader_values
187 SET loader_status = 'complete'
188 WHERE location_code = l_location_code;
189
190
191 EXCEPTION
192 WHEN OTHERS THEN
193 ERROR_STACK.PUSHMESSAGE('*****************','ICX');
194 ERROR_STACK.PUSHMESSAGE('Location '||l_location_code || ' could not be loaded','ICX');
195
196 IF (hr_utility.get_message() IS NULL OR
197 hr_utility.get_message() = '') THEN
198 ERROR_STACK.PUSHMESSAGE(SQLCODE || ' ' ||SQLERRM,'ICX');
199 ELSE
200 ERROR_STACK.PUSHMESSAGE(hr_utility.get_message(),'ICX');
201 END IF;
202
203 UPDATE por_location_loader_values
204 SET loader_status = 'failure'
205 WHERE location_code = l_location_code;
206
207 END;
208
209 END LOOP;
210 CLOSE c_locations;
211
212 COMMIT;
213
214 BEGIN
215 IF (ERROR_STACK.GETMSGCOUNT > 0) THEN
216 RAISE validation_error;
217 END IF;
218 END;
219
220 EXCEPTION
221 WHEN OTHERS THEN
222 RAISE;
223
224
225 END insert_update_loc_data;
226
227
228 END POR_LOAD_HR_DATA;
229
230