[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_UPLOAD_ACCUM_MILES_PKG
Source
1 PACKAGE BODY ap_web_upload_accum_miles_pkg AS
2 /* $Header: apwacmub.pls 120.2 2006/07/26 23:08:57 krmenon noship $ */
3 -- ---------------------------------------------------
4 -- Procedure PutLine
5 -- Description Used for logging
6 --
7 -- ----------------------------------------------------
8 PROCEDURE PutLine(p_buff IN VARCHAR2) IS
9 BEGIN
10 fnd_file.put_line(fnd_file.log, p_buff);
11 END PutLine;
12
13 -- --------------------------------------------------------
14 -- Procedure OpenDataFile
15 -- Description Opens a given data file
16 --
17 --
18 -- ---------------------------------------------------------
19 PROCEDURE OpenDataFile(P_DataFile IN VARCHAR2,
20 P_FileHandle OUT NOCOPY utl_file.file_type,
21 P_ErrorBuffer OUT NOCOPY VARCHAR2,
22 P_ReturnCode OUT NOCOPY NUMBER) IS
23 l_DatafilePath VARCHAR2(240);
24 l_Datafile VARCHAR2(240);
25 l_DatafilePtr utl_file.file_type;
26 l_Ntdir NUMBER;
27 l_Unixdir NUMBER;
28 BEGIN
29
30 IF ( g_DebugSwitch = 'Y' ) THEN
31 -- ----------- Begin Loggin ------------------
32 PutLine('Begin OpenDataFile ( ' || P_DataFile || ' )');
33 -- ----------- End Logging -------------------
34 END IF;
35
36 l_NtDir := instrb(P_DataFile, '\', -1);
37 l_UnixDir := instrb(P_DataFile, '/', -1);
38 IF (l_NtDir > 0) THEN
39 l_DatafilePath := substrb(P_Datafile, 0, l_NtDir-1);
40 l_Datafile := substrb(P_DataFile, l_NtDir+1);
41 ELSIF (l_unixdir > 0) THEN
42 l_DatafilePath := substrb(P_DataFile, 0, l_UnixDir-1);
43 l_Datafile := substrb(P_DataFile, l_UnixDir+1);
44 ELSE
45 l_DatafilePath := '';
46 l_Datafile := P_DataFile;
47 END IF;
48
49 IF ( g_DebugSwitch = 'Y' ) THEN
50 -- --------- Begin logging ----------------------
51 PutLine('------------ Begin File Breakup Info ----------------');
52 PutLine(' l_NtDir: '|| to_char(l_NtDir));
53 PutLine(' l_UnixDir: '|| to_char(l_UnixDir));
54 PutLine(' l_DatafilePath: '|| l_DatafilePath);
55 PutLine(' l_Datafile: '|| l_Datafile);
56 PutLine('------------ End File Breakup Info ------------------');
57 -- --------- End logging -------------------------
58 END IF;
59
60 --
61 -- Open the datafile for read
62 P_FileHandle := utl_file.fopen(l_DatafilePath, l_Datafile, 'r');
63 P_ReturnCode := 0;
64
65 EXCEPTION
66 WHEN OTHERS THEN
67 utl_file.fclose(l_datafileptr);
68 fnd_message.set_name('AK', 'AK_INVALID_FILE_OPERATION');
69 fnd_message.set_token('PATH', l_datafilepath);
70 fnd_message.set_token('FILE', l_datafile);
71 P_ErrorBuffer := fnd_message.get();
72 P_ReturnCode := 2;
73
74 END OpenDataFile;
75
76 -- ---------------------------------------------------------
77 -- Procedure CloseDataFile
78 -- Descrition Closes a data file for a given handle
79 --
80 -- ---------------------------------------------------------
81 PROCEDURE CloseDataFile(P_FileHandle IN utl_file.file_type) IS
82 l_FileHandle utl_file.FILE_TYPE;
83 BEGIN
84
85 IF ( g_DebugSwitch = 'Y' ) THEN
86 -- ----------- Begin Loggin ------------------
87 PutLine('Begin CloseDataFile');
88 -- ----------- End Logging -------------------
89 END IF;
90
91 l_FileHandle := P_FileHandle;
92 utl_file.fclose(l_FileHandle);
93
94 END CloseDataFile;
95
96
97 -- -----------------------------------------------------------
98 -- Function GetEmployeeId
99 -- Description Returns employee id for an employee number if
100 -- employee is active; else returns -1
101 --
102 -- ------------------------------------------------------------
103 FUNCTION GetEmployeeId ( P_EmployeeNum IN VARCHAR2,
104 P_OrgId IN NUMBER) RETURN NUMBER IS
105 l_EmployeeId NUMBER;
106
107 BEGIN
108
109 SELECT employee_id
110 INTO l_EmployeeId
111 FROM (
112 SELECT h.employee_id
113 FROM per_employees_current_x h,
114 financials_system_params_all f
115 WHERE h.employee_num = P_EmployeeNum
116 AND AP_WEB_DB_HR_INT_PKG.isPersonCwk(h.employee_id)='N'
117 AND h.business_group_id = f.business_group_id
118 AND f.org_id = P_OrgId
119 UNION ALL
120 SELECT h.person_id employee_id
121 FROM PER_CONT_WORKERS_CURRENT_X h,
122 financials_system_params_all f
123 WHERE h.npw_number = P_EmployeeNum
124 AND h.business_group_id = f.business_group_id
125 AND f.org_id = P_OrgId
126 );
127
128 RETURN l_EmployeeId;
129
130 EXCEPTION
131 WHEN TOO_MANY_ROWS THEN
132 RETURN -2;
133 WHEN OTHERS THEN
134 RETURN -1;
135
136 END GetEmployeeId;
137
138
139 -- -----------------------------------------------------------------------
140 -- Procedure UploadAccumulatedMiles
141 -- Description Validates and uploads accumulated mileage for employees
142 --
143 --
144 -- ------------------------------------------------------------------------
145 PROCEDURE UploadAccumulatedMiles ( P_ErrorBuffer OUT NOCOPY VARCHAR2,
146 P_ReturnCode OUT NOCOPY NUMBER,
147 P_DataFile IN VARCHAR2,
148 P_OrgId IN NUMBER,
149 P_PeriodId IN NUMBER,
150 P_UOM IN VARCHAR2,
151 P_DebugSwitch IN VARCHAR2) IS
152 -- Enter the procedure variables here. As shown below
153 l_RequestId NUMBER;
154 l_Result BOOLEAN;
155 l_Status VARCHAR2(240);
156 l_Message VARCHAR2(240);
157 l_Errors NUMBER;
158 l_FileHandle utl_file.FILE_TYPE;
159 l_NumLines NUMBER;
160 l_RowIndex NUMBER;
161 l_NumRejected NUMBER;
162 l_Line VARCHAR2(240);
163 l_Length NUMBER;
164 l_DelimPos NUMBER;
165 l_EmployeeNum VARCHAR2(30);
166 l_EmployeeId NUMBER;
167 l_AccumMiles NUMBER;
168 l_ErrorLine NUMBER;
169 l_LastUpdatedBy NUMBER;
170 l_LastUpdateLogin NUMBER;
171 l_DmlErrors EXCEPTION;
172 l_InvalidFormat EXCEPTION;
173
174
175 TYPE EmployeeIdTabType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
176 TYPE AccumMilesTabType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
177 TYPE EmployeeNumTabType IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
178 TYPE InvalidEmpTabType IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
179
180 l_EmployeeIdTab EmployeeIdTabType;
181 l_AccumMilesTab AccumMilesTabType;
182 l_EmployeeNumTab EmployeeNumTabType;
183 l_InvalidEmpTab InvalidEmpTabType;
184
185 BEGIN
186
187 g_DebugSwitch := P_DebugSwitch;
188 l_RequestId := FND_GLOBAL.CONC_REQUEST_ID;
189 l_LastUpdatedBy := to_number(FND_GLOBAL.USER_ID);
190 l_LastUpdateLogin := to_number(FND_GLOBAL.LOGIN_ID);
191
192 PutLine('==========================================================================');
193 PutLine(' REQUEST ID: '||l_RequestId);
194 PutLine('==========================================================================');
195
196 IF ( g_DebugSwitch = 'Y' ) THEN
197 -- ----------- Begin Loggin ------------------
198 PutLine('--------------- Begin UploadAccumulatedMiles ------------------');
199
200
201 PutLine('------------------------------------------------------------');
202 PutLine('-- P A R A M E T E R S --');
203 PutLine('------------------------------------------------------------');
204 PutLine('File Name : '||P_DataFile);
205 PutLine('Organization Id : '||P_OrgId);
206 PutLine('Schedule Period Id : '||P_PeriodId);
207 PutLine('Distance Unit Of Measure: '||P_UOM);
208 PutLine('Debug Switch : '||P_DebugSwitch);
209 -- ----------- End Logging -------------------
210 END IF;
211
212 -- Open the data file
213 OpenDataFile(P_DataFile,
214 l_FileHandle,
215 l_Message,
216 l_Errors);
217
218 IF ( l_Errors > 0 ) THEN
219 P_ErrorBuffer := l_Message;
220 P_ReturnCode := 2;
221 --P_RequestStatus := 'FAILED';
222 -- ----------- Logging ------------
223 PutLine('Error opening file: '||l_Message);
224 RETURN;
225 END IF;
226
227 l_NumLines := 0;
228 l_NumRejected := 0;
229 l_RowIndex := 0;
230
231 LOOP
232 BEGIN
233 utl_file.get_line(l_FileHandle, l_Line);
234 l_NumLines := l_NumLines + 1;
235 l_Length := length(l_Line);
236
237 l_DelimPos := instr(l_Line, ';', 1, 1);
238 l_EmployeeNum := substr ( l_Line, 1, l_DelimPos - 1 );
239 l_AccumMiles := to_number( trim(substr ( l_Line, l_DelimPos + 1, l_Length - l_DelimPos)));
240
241 -- If the mileage is stored in miles, we need to convert into
242 -- km. Now, I am rounding to 2 digit precission but is this valid?
243 IF ( 'MILES' = P_UOM ) THEN
244 l_AccumMiles := round(l_AccumMiles * 1.609, 2);
245 ELSIF ( 'SWMILES' = P_UOM ) THEN
246 l_AccumMiles := round(l_AccumMiles * 10 * 1.609, 2);
247 END IF;
248
249 l_EmployeeId := GetEmployeeId (l_EmployeeNum, P_OrgId) ;
250
251 -- If we found the employee id, then insert a row
252 IF ( l_EmployeeId > -1 ) THEN
253 -- Increment the index
254 l_RowIndex := l_RowIndex + 1;
255
256 -- Insert data
257 l_EmployeeIdTab(l_RowIndex) := l_EmployeeId;
258 l_AccumMilesTab(l_RowIndex) := l_AccumMiles;
259 l_EmployeeNumTab(l_RowIndex) := l_EmployeeNum;
260
261 ELSIF ( l_EmployeeId = -1 ) THEN
262 l_NumRejected := l_NumRejected + 1;
263 l_InvalidEmpTab(l_NumRejected) := rpad(l_EmployeeNum, 15,' ')||' ERROR No active employee found';
264 ELSIF ( l_EmployeeId = -2 ) THEN
265 l_NumRejected := l_NumRejected + 1;
266 l_InvalidEmpTab(l_NumRejected) := rpad(l_EmployeeNum, 15,' ')||' ERROR Multiple employees found';
267 END IF;
268
269 EXCEPTION
270 WHEN NO_DATA_FOUND THEN
271 EXIT;
272 WHEN OTHERS THEN
273 RAISE l_InvalidFormat;
274 END;
275 END LOOP;
276
277 -- Close the data file
278 CloseDataFile(l_FileHandle);
279
280 IF ( g_DebugSwitch = 'Y' ) THEN
281 -- --------------------- Logging ------------------------------------
282 PutLine('Completed reading data file. Begin processing data ....');
283 -- ------------------------------------------------------------------
284 END IF;
285
286 -- If any of the rows were rejected because of invalid employee, record
287 -- those in the log file.
288 IF (l_NumRejected > 0 ) THEN
289 PutLine('======================================================================');
290 PutLine('Following data has not been loaded because it failed validation: ');
291 PutLine('----------------------------------------------------------------------');
292 PutLine('Employee Number Status Comments');
293 PutLine('----------------------------------------------------------------------');
294
295 FOR i IN 1..l_NumRejected LOOP
296 PutLine(l_InvalidEmpTab(i));
297 END LOOP;
298
299 PutLine('======================================================================');
300
301 END IF;
302
303 IF ( g_DebugSwitch = 'Y' ) THEN
304 -- ------------ Logging -------------------------------------------------------
305 PutLine('Calling bulk insert for '||to_char(l_RowIndex)||' records ...');
306 -- ----------------------------------------------------------------------------
307 END IF;
308
309 -- -----------------------------------------
310 -- This is the buld insert call
311 -- -----------------------------------------
312 BEGIN
313 FORALL i IN 1..l_RowIndex SAVE EXCEPTIONS
314
315 INSERT INTO ap_web_employee_info_all
316 ( employee_id,
317 value_type,
318 numeric_value,
319 period_id,
320 creation_date,
321 created_by,
322 last_update_date,
323 last_updated_by,
324 last_update_login,
325 org_id)
326 VALUES( l_EmployeeIdTab(i),
327 'CUM_REIMB_DISTANCE',
328 l_AccumMilesTab(i),
329 P_PeriodId,
330 sysdate,
331 l_LastUpdatedBy,
332 sysdate,
333 l_LastUpdatedBy,
334 l_LastUpdateLogin,
335 P_OrgId );
336 EXCEPTION
337 WHEN OTHERS THEN
338 l_Errors := SQL%BULK_EXCEPTIONS.COUNT;
339
340 PutLine('======================================================================');
341 PutLine('Errors reported during insert: ');
342 PutLine('----------------------------------------------------------------------');
343 PutLine('Employee Number Accumulated Miles Error');
344 PutLine('----------------------------------------------------------------------');
345
346 FOR i IN 1..l_Errors LOOP
347 l_ErrorLine := SQL%BULK_EXCEPTIONS(i).ERROR_INDEX;
348 PutLine(rpad(l_EmployeeNumTab(l_ErrorLine),19)||
349 lpad(l_AccumMilesTab(l_ErrorLine),17)||
350 ' '||
351 SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
352 END LOOP;
353 END; -- end of bulk insert
354
355 PutLine('=============================================================');
356 PutLine('LOAD SUMMARY');
357 PutLine('=============================================================');
358 PutLine('Total number of records in file:'||lpad(l_NumLines, 10 ));
359 PutLine('Number of loaded records :'||lpad(l_NumLines -l_NumRejected -l_Errors, 10 ));
360 PutLine('Number of invalid records :'||lpad(l_NumRejected, 10 ));
361 PutLine('Number of errored records :'||lpad(l_Errors, 10 ));
362 PutLine('=============================================================');
363
364 -- Set the return status anc code
365 P_ReturnCode := 0;
366 COMMIT;
367
368 IF ( g_DebugSwitch = 'Y' ) THEN
369 -- ----------- Begin Loggin ------------------
370 PutLine('End UploadAccumulatedMiles');
371 -- ----------- End Logging -------------------
372 END IF;
373
374 END UploadAccumulatedMiles;
375
376 END AP_WEB_UPLOAD_ACCUM_MILES_PKG;