· 8 years ago · Mar 28, 2018, 10:32 AM
1create or replace
2PROCEDURE ELR_XCN_EMP_IMPORT
3AS
4
5 -- constants declaration
6 c_interface_name CONSTANT integrator.tbl_interface_inbound.iinb_object_type%TYPE := 'PersonnelEMEA';
7 c_process_name CONSTANT el_intstpee.process_name%TYPE := 'ELR_XCN_EMP_IMPORT';
8 -- constants for error type
9 c_middleware CONSTANT el_intstsee.job_err_type%TYPE := 'MIDDLEWARE';
10 c_setup CONSTANT el_intstsee.job_err_type%TYPE := 'SETUP';
11 c_oracle CONSTANT el_intstsee.job_err_type%TYPE := 'ORACLE';
12 c_trailer_xtn CONSTANT NVARCHAR2(10) := '.trl';
13
14 -- variables declaration
15 isHeaderCreatedForEmpExists BOOLEAN := FALSE;
16 isHeaderCreatedForEmpNotExists BOOLEAN := FALSE;
17 isTrlrCreatedForEmpExists BOOLEAN := FALSE;
18 isTrlrCreatedForEmpNotExists BOOLEAN := FALSE;
19 vc_error_file NVARCHAR2(100);
20 vc_trailer_file NVARCHAR2(100);
21 v_empExistStore NVARCHAR2(100);
22 v_empPasswordDeleteStore NVARCHAR2(100);
23
24 vc_emp_trl_file NVARCHAR2(100);
25 vc_empstore_trl_file NVARCHAR2(100);
26 vc_party_trl_file NVARCHAR2(100);
27 vc_pwd_trl_file NVARCHAR2(100);
28 vc_ans_trl_file NVARCHAR2(100);
29
30
31 exist_employee_varray dbms_sql.varchar2_table;
32 exist_party_varray dbms_sql.varchar2_table;
33 exist_employee_store_varray dbms_sql.varchar2_table;
34 employee_varray dbms_sql.varchar2_table;
35 l_text_table dbms_sql.varchar2_table;
36 party_varray dbms_sql.varchar2_table;
37 employee_store_varray dbms_sql.varchar2_table;
38 employee_password_varray dbms_sql.varchar2_table;
39 employee_delete_store_varray dbms_sql.varchar2_table;
40 employee_answers_varray dbms_sql.varchar2_table;
41
42 v_stg_serial PLS_INTEGER := 0;
43 v_stgRecordStore NVARCHAR2(100);
44 v_stgRecord NVARCHAR2(4000);
45
46 v_mnt_serial PLS_INTEGER := 1;
47
48 v_storeId NVARCHAR2(100);
49 v_mntType NVARCHAR2(100);
50 v_recordsCount PLS_INTEGER := 0;
51 v_mntType_recordsMap dbms_sql.varchar2_table;
52 v_mntRecordsArray dbms_sql.varchar2_table;
53 v_newMntRecordsArray dbms_sql.varchar2_table;
54
55
56 grp_membership_binary_value ELR_XCN_EMP_GROUP_MEMBERSHIP.group_id%TYPE;
57 vc_rec_set_id integrator.tbl_interface_inbound.iinb_iz_record_set_id%TYPE;
58 vc_file_directory NVARCHAR2(500);
59 vc_emp_filename NVARCHAR2(500);
60 vc_exist_emp_filename NVARCHAR2(500);
61 vc_file_header NVARCHAR2(1000);
62
63 vc_file_header_delete_store NVARCHAR2(1000);
64 vc_party_filename NVARCHAR2(500);
65 vc_exist_party_filename NVARCHAR2(500);
66 vc_empstore_filename NVARCHAR2(500);
67 vc_exist_empstore_filename NVARCHAR2(500);
68 vc_emp_password_filename NVARCHAR2(500);
69 vc_emp_answers_filename NVARCHAR2(500);
70 v_mnt_couunter PLS_INTEGER := 1;
71
72 isEmployeeFileCreated boolean := false;
73 isEmployeeStoreFileCreated boolean := false;
74 isPartyFileCreated boolean := false;
75 isPasswordFileCreated boolean := false;
76 isAnswersFileCreated boolean := false;
77 v_empStoreRecordsCount PLS_INTEGER := 0;
78 v_partyRecrodsCount PLS_INTEGER := 0;
79 v_empRecordsCount PLS_INTEGER := 0;
80 v_empPwdRecordsCount PLS_INTEGER := 0;
81 v_empAnsRecordsCount PLS_INTEGER := 0;
82
83 vc_file_line NVARCHAR2(4000);
84 vc_emp_cust_aff_filename NVARCHAR2(500);
85 vc_file_line_employee NVARCHAR2(4000);
86 vc_emp_prev_line NVARCHAR2(4000);
87 vc_party_prev_line NVARCHAR2(4000);
88 vc_error_code_out NVARCHAR2(200);
89 vc_error_text_out NVARCHAR2(4000);
90 vc_error_level_out NVARCHAR2(200);
91 vc_write_file NVARCHAR2(25);
92 vi_emp_index PLS_INTEGER := 0;
93 vi_assignment_index PLS_INTEGER := 0;
94 vi_iz_fail_count PLS_INTEGER := 0;
95 vi_store_count PLS_INTEGER := 0;
96 vi_mnt_store_count PLS_INTEGER := 0;
97 vi_store_fail_count PLS_INTEGER := 0;
98 vi_num_iz_processed PLS_INTEGER := 0;
99 --variables for write mnt for existing employee in case Action "Available_change_loc"
100 vi_exists_employee_store PLS_INTEGER :=0;
101 vi_exists_xcenter_employee PLS_INTEGER :=0;
102 vi_current_employee PLS_INTEGER:=0;
103 vi_current_emp_action_code NVARCHAR2(100);
104 --variable use for check current employeee retail_loc_id is null or not
105 retail_loc_id_flag BOOLEAN :=FALSE;
106 --variable for check emp_record_already exists with same employee id and retail_loc_id
107 emp_record_already_exists BOOLEAN := FALSE;
108 -- flag for generating mnt file for delete hrs_employee_password record
109 emp_password_flag BOOLEAN :=FALSE;
110 --flag for delete all store assignment in case "Available_terminate"
111 emp_delete_store_flag BOOLEAN :=FALSE;
112
113 -- variable use in remove all store assignment
114 charIndex PLS_INTEGER :=-1;
115 v_subStr NVARCHAR2(1000);
116 store_delete_count PLS_INTEGER :=0;
117
118 --variable for parsing
119 v_parser xmlparser.parser;
120 v_document xmldom.domdocument;
121 v_nodelist xmldom.domnodelist;
122 v_node xmldom.domnode;
123 v_asgn_nodelist xmldom.domnodelist;
124 v_asgn_node xmldom.domnode;
125 v_assingment_nodelist xmldom.domnodelist;
126 -- file handeler for mnt
127 file_handler_emp UTL_FILE.FILE_TYPE;
128 file_handler_party UTL_FILE.FILE_TYPE;
129 file_handler_empstore UTL_FILE.FILE_TYPE;
130 file_handler_empstore_header UTL_FILE.FILE_TYPE;
131
132 file_handler_emp_trl UTL_FILE.FILE_TYPE;
133 file_handler_party_trl UTL_FILE.FILE_TYPE;
134 file_handler_empstore_trl UTL_FILE.FILE_TYPE;
135 file_handler_pwd_trl UTL_FILE.FILE_TYPE;
136 file_handler_ans_trl UTL_FILE.FILE_TYPE;
137
138 file_handler_emp_password UTL_FILE.FILE_TYPE;
139 file_handler_emp_answers UTL_FILE.FILE_TYPE;
140 file_handler_emp_exists UTL_FILE.FILE_TYPE;
141 file_handler_party_exists UTL_FILE.FILE_TYPE;
142 file_handler_empstore_exists UTL_FILE.FILE_TYPE;
143
144 v_employee_id elr_xcn_emp_staging_log.employee_id%TYPE;
145 v_action_code elr_xcn_emp_staging_log.action_code%TYPE;
146 v_passwordMntActionCode elr_xcn_emp_staging_log.action_code%TYPE;
147 v_employee_status elr_xcn_emp_staging_log.employee_status%TYPE;
148 v_first_name elr_xcn_emp_staging_log.first_name%TYPE;
149 v_last_name elr_xcn_emp_staging_log.last_name%TYPE;
150 v_middle_name elr_xcn_emp_staging_log.middle_name%TYPE;
151 v_hire_date elr_xcn_emp_staging_log.hire_date%TYPE;
152 v_original_hire_date elr_xcn_emp_staging_log.original_hire_date%TYPE;
153 v_termination_date elr_xcn_emp_staging_log.termination_date%TYPE;
154 v_market_code elr_xcn_emp_staging_log.market_code%TYPE;
155 v_job_title elr_xcn_emp_assgn_staging_log.job_title%TYPE;
156 v_primary_group_id elr_xcn_emp_assgn_staging_log.primary_group_id%TYPE;
157 v_division_code elr_xcn_emp_assgn_staging_log.division_code%TYPE;
158 v_retail_loc_id elr_xcn_emp_assgn_staging_log.retail_loc_id%TYPE;
159 v_assignment_date elr_xcn_emp_assgn_staging_log.assignment_date%TYPE;
160 v_preferred_locale elr_xcn_emp_staging_log.preferred_locale%TYPE;
161 v_record_type elr_xcn_emp_staging_log.record_type%TYPE;
162
163 -- variables for logging
164 v_integration_id el_intstpee.integration_id%TYPE;
165 v_job_id el_intstpee.job_id%TYPE;
166 v_job_sequence el_intstsee.step_sequence%TYPE := 0;
167 v_err_type el_intstsee.job_err_type%TYPE;
168 v_iz_created_timestamp elr_xcn_emp_staging_log.iz_created_timestamp%TYPE;
169 vc_job_log NVARCHAR2(1000);
170 vc_locale NVARCHAR2(100);
171 emp_status NVARCHAR2(100);
172 -- table 'hrs_employee_store' type for storing value ,fetching from xcenter table 'hrs_employee_store'
173 xcenter_process_emp dtv.hrs_employee_store%rowtype;
174 xcenter_rtl_loc_id dtv.hrs_employee_store%rowtype;
175 vb_start_ind BOOLEAN;
176 vb_first BOOLEAN;
177 vb_file_close BOOLEAN := TRUE;
178 e_iz_error EXCEPTION;
179 e_next_iz EXCEPTION;
180 e_next_store EXCEPTION;
181 --table type for 'elr_xcn_emp_assgn_staging_log'
182TYPE tbl_emp_assingment_staging
183IS
184 RECORD
185 (
186 employee_id elr_xcn_emp_assgn_staging_log.employee_id%TYPE,
187 job_title elr_xcn_emp_assgn_staging_log.job_title%TYPE,
188 primary_group_id elr_xcn_emp_assgn_staging_log.primary_group_id%TYPE,
189 division_code elr_xcn_emp_assgn_staging_log.division_code%TYPE,
190 retail_loc_id elr_xcn_emp_assgn_staging_log.retail_loc_id%TYPE,
191 assignment_date elr_xcn_emp_assgn_staging_log.assignment_date%TYPE,
192 group_membership elr_xcn_emp_assgn_staging_log.group_membership%TYPE );
193 --table type for 'elr_xcn_emp_staging_log'
194TYPE tbl_pos_emp_staging
195IS
196 RECORD
197 (
198 iinb_iz_id integrator.tbl_interface_inbound.iinb_iz_id%TYPE,
199 employee_id elr_xcn_emp_staging_log.employee_id%TYPE,
200 action_code elr_xcn_emp_staging_log.action_code%TYPE,
201 employee_status elr_xcn_emp_staging_log.employee_status%TYPE,
202 first_name elr_xcn_emp_staging_log.first_name%TYPE,
203 last_name elr_xcn_emp_staging_log.last_name%TYPE,
204 middle_name elr_xcn_emp_staging_log.middle_name%TYPE,
205 hire_date elr_xcn_emp_staging_log.hire_date%TYPE,
206 original_hire_date elr_xcn_emp_staging_log.original_hire_date%TYPE,
207 market_code elr_xcn_emp_staging_log.market_code%TYPE,
208 termination_date elr_xcn_emp_staging_log.termination_date%TYPE,
209 preferred_locale elr_xcn_emp_staging_log.preferred_locale%TYPE,
210 record_type elr_xcn_emp_staging_log.record_type%TYPE);
211 --type elr_xcn_emp_staging_log
212TYPE typ_tbl_pos_emp_staging
213IS
214 TABLE OF tbl_pos_emp_staging INDEX BY BINARY_INTEGER;
215 vtyp_tbl_pos_emp_staging typ_tbl_pos_emp_staging;
216 -- type elr_xcn_emp_assgn_staging_log
217
218TYPE typ_tbl_assignment_staging
219IS
220 TABLE OF elr_xcn_emp_assgn_staging_log%ROWTYPE;
221 vtyp_tbl_assingment_staging typ_tbl_assignment_staging := typ_tbl_assignment_staging();
222
223 --cursor to fetch employee status from 'elr_xcn_emp_staging_log' table
224 CURSOR cur_employee_status(emp_status elr_xcn_emp_staging_log.employee_status%TYPE)
225 IS
226 SELECT DECODE(TRIM(emp_status), 'Active', 'A', 'Terminated', 'T', 'I') status
227 FROM DUAL;
228
229 --cursor to fetch Action type from 'elr_xcn_emp_staging_log' table
230 CURSOR cur_action_type(action_type elr_xcn_emp_staging_log.action_code%TYPE)
231 IS
232 SELECT DECODE(TRIM(action_type), 'Available_CREATE', 'INSERT_ONLY', 'Available_TERMINATE', 'UPDATE' ,'Available_CHANGE_LOC' ,'UPDATE','Available_REHIRE','INSERT','Available_REV_TERMINATE','UPDATE_SELECT','Available_CHANGE','UPDATE','UPDATE_SELECT') action
233 FROM DUAL;
234
235 -- main cursor to fetch AVAILABLE IZs from inbound table
236 CURSOR cur_iz_id
237 IS
238 SELECT iinb_iz_id
239 FROM integrator.tbl_interface_inbound
240 WHERE iinb_object_type = c_interface_name
241 AND iinb_iz_record_status = 'AVAILABLE'
242 AND iinb_iz_record_set_id IS NULL
243 AND iinb_batch_control_record_type IS NULL
244 ORDER BY iinb_iz_id;
245
246 -- cursor to fetch store 'elr_xcn_emp_assgn_staging_log' table
247 CURSOR cur_store
248 IS
249 SELECT distinct retail_loc_id
250 FROM elr_xcn_emp_assgn_staging_log
251 WHERE processed_date IS NULL
252 AND retail_loc_id IS NOT NULL;
253
254 -- cursor to fetch employee password records when employee_status Terminated
255 CURSOR cur_emp_password(p_employee_id IN elr_xcn_emp_staging_log.employee_id%TYPE)
256 IS
257 SELECT * FROM dtv.hrs_employee_password where employee_id=p_employee_id and CURRENT_PASSWORD_FLAG=1;
258
259 -- cursor to fetch employee security answers records when employee_status Terminated
260 CURSOR cur_emp_answers(p_employee_id IN elr_xcn_emp_staging_log.employee_id%TYPE)
261 IS
262 SELECT ORGANIZATION_ID,employee_id,challenge_code FROM dtv.hrs_employee_answers where employee_id=p_employee_id;
263
264 -- cursor to fetch retail_loc id to remove store assignement
265 CURSOR cur_emp_store_delete(p_employee_id IN elr_xcn_emp_staging_log.employee_id%TYPE)
266 IS
267 SELECT rtl_loc_Id FROM dtv.hrs_employee_store where employee_id = p_employee_id and end_date is null;
268
269 -- Cursor to fetch distinct stores from staging table.
270 CURSOR cur_distinct_stores
271 IS
272 SELECT distinct store_id FROM ELR_XCN_EMP_STORE_RECORD_STG;
273
274 -- Cursor to fetch distinct stores from staging table.
275 CURSOR cur_store_records(p_store_in IN ELR_XCN_EMP_STORE_RECORD_STG.store_id%TYPE)
276 IS
277 SELECT * FROM ELR_XCN_EMP_STORE_RECORD_STG where store_id = p_store_in order by record_type, serial;
278
279 -- cursor to fetch employee data from 'elr_xcn_emp_staging_log' and 'elr_xcn_emp_assgn_staging_log' table
280 CURSOR cur_emp_store(p_store_id_in IN elr_xcn_emp_assgn_staging_log.retail_loc_id%TYPE)
281 IS
282 SELECT *
283 FROM
284 (SELECT DISTINCT elog.IINB_IZ_ID, elog.employee_id,
285 elog.hire_date,
286 elog.original_hire_date,
287 elog.first_name,
288 elog.last_name,
289 elog.middle_name,
290 elog.preferred_locale,
291 elog.termination_date,
292 elog.action_code,
293 elog.market_code,
294 elog.employee_status,
295 elog.record_type,
296 assign_log.job_title,
297 assign_log.division_code,
298 assign_log.assignment_date,
299 assign_log.retail_loc_id,
300 assign_log.primary_group_id,
301 assign_log.group_membership,
302 DECODE(TRIM(employee_status),'A','TRUE','T','FALSE','FALSE')party_status
303 FROM elr_xcn_emp_staging_log elog,
304 elr_xcn_emp_assgn_staging_log assign_log
305 WHERE assign_log.retail_loc_id = p_store_id_in
306 AND trim(elog.employee_id) = trim(assign_log.employee_id)
307 AND assign_log.processed_date IS NULL
308 AND elog.processed_date IS NULL
309 AND assign_log.retail_loc_id IS NOT NULL
310 )
311 ORDER BY IINB_IZ_ID asc;
312 curr_interface_rec icc.pkg_interface.curinterface_inbound;
313 rec_ref_interface integrator.tbl_interface_inbound%ROWTYPE;
314
315 -- this internal proc is used to update the IZ status to FAIL or PROCESSED
316 -- if any error occurs, it will RAISE e_next_iz exception to continue processing with next IZ record
317 PROCEDURE update_iz_status(
318 p_status_in IN integrator.interface_inbound.iinb_iz_record_status%TYPE,
319 p_iinb_iz_id_in IN integrator.interface_inbound.iinb_iz_id%TYPE,
320 p_iinb_iz_uuid_in IN integrator.interface_inbound.iinb_iz_uuid%TYPE,
321 p_iinb_iz_record_set_id_in IN integrator.interface_inbound.iinb_iz_record_set_id%TYPE,
322 p_iinb_object_type_in IN integrator.interface_inbound.iinb_object_type%TYPE)
323 IS
324 BEGIN
325 vc_error_text_out := NULL;
326 icc.pkg_interface.set_status_inbound_document ( vc_error_code_out, vc_error_level_out, vc_error_text_out, p_iinb_iz_id_in, p_iinb_iz_uuid_in, SYSDATE, p_status_in, p_iinb_iz_record_set_id_in, p_iinb_object_type_in, NULL, NULL,
327 CASE
328 WHEN p_status_in = 'FAIL' THEN
329 SQLCODE
330 ELSE
331 NULL
332 END,
333 CASE
334 WHEN p_status_in = 'FAIL' THEN
335 'S'
336 ELSE
337 NULL
338 END,
339 CASE
340 WHEN p_status_in = 'FAIL' THEN
341 SQLERRM
342 ELSE
343 NULL
344 END );
345
346 -- increment the count variable accordingly
347 IF p_status_in = 'FAIL' THEN
348 vi_iz_fail_count := vi_iz_fail_count + 1;
349 END IF;
350 -- if middleware returns error then raise exception
351 IF vc_error_text_out IS NOT NULL THEN
352 RAISE e_iz_error;
353 END IF;
354 COMMIT;
355 EXCEPTION
356 WHEN e_iz_error THEN
357 v_err_type := c_middleware;
358 vc_job_log := 'Error returned from ICC.PKG_INTERFACE.SET_STATUS_INBOUND_DOCUMENT, while updating IZ status to ' || p_status_in || ' for IZ ID: ' || TO_CHAR(p_iinb_iz_id_in) || ' with VC_ERROR_LEVEL: ' || vc_error_level_out || ', VC_ERROR_CODE: ' || vc_error_code_out || ' and VC_ERROR_TEXT_OUT: ' || vc_error_text_out || CHR (10);
359 el_intstspk.process_skip_warning (v_err_type, vc_job_log);
360 RAISE e_next_iz;
361 WHEN OTHERS THEN
362 v_err_type := c_oracle;
363 vc_job_log := 'Error in procedure UPDATE_IZ_STATUS, while calling procedure ICC.PKG_INTERFACE.SET_STATUS_INBOUND_DOCUMENT to mark the IZ status to ' || p_status_in || '. Skipping IZ ID: ' || TO_CHAR(p_iinb_iz_id_in) || CHR (10);
364 el_intstspk.process_skip_warning (v_err_type, vc_job_log);
365 RAISE e_next_iz;
366 END update_iz_status;
367
368PROCEDURE recordsCount(p_store_in IN ELR_XCN_EMP_STORE_RECORD_STG.store_id%TYPE,
369 p_recordType_in IN ELR_XCN_EMP_STORE_RECORD_STG.record_type%TYPE,
370 recordsCount OUT number) IS
371begin
372 recordsCount := 0;
373 SELECT count(*) into recordsCount FROM ELR_XCN_EMP_STORE_RECORD_STG where store_id = p_store_in
374 and record_type = p_recordType_in order by record_type;
375
376EXCEPTION
377 WHEN OTHERS THEN
378 v_err_type := c_oracle;
379 vc_job_log := 'Error in procedure recordsCount for store: ' || TO_CHAR(p_store_in) || CHR (10);
380 el_intstspk.process_skip_warning (v_err_type, vc_job_log);
381 RAISE;
382end recordsCount;
383
384 ---------------------------------------------------------------------------------------------------------------------------------------------
385 ---------------------------------------------------------------------------------------------------------------------------------------------
386 -------------------------------------------------------------Main Proc Begins Here-----------------------------------------------------------
387 ---------------------------------------------------------------------------------------------------------------------------------------------
388 ---------------------------------------------------------------------------------------------------------------------------------------------
389 BEGIN
390 v_job_sequence := 1;
391 el_intstspk.status_rec.job_log := '';
392 BEGIN
393 SELECT integration_id,
394 job_id
395 INTO v_integration_id,
396 v_job_id
397 FROM el_intstpee
398 WHERE process_name = c_process_name;
399 EXCEPTION
400 WHEN OTHERS THEN
401 -- process failed setup error no job defined
402 el_intstspk.status_rec.step_sequence := v_job_sequence;
403 el_intstspk.status_rec.integration_id := 0;
404 el_intstspk.status_rec.job_id := 0;
405 el_intstspk.create_status(el_intstspk.status_rec);
406 v_err_type := c_setup;
407 vc_job_log := 'No job defined in the status table, exiting out'|| CHR(10);
408 RAISE;
409 END;
410 vb_start_ind := el_intstspk.start_job( v_integration_id, v_job_id );
411 -- previous job failed, do not go ahead
412 IF NOT vb_start_ind THEN
413 -- process failed setup error
414 el_intstspk.status_rec.step_sequence := v_job_sequence;
415 el_intstspk.create_status(el_intstspk.status_rec);
416 v_err_type := c_setup;
417 vc_job_log := 'Previous step failed or job out of sync, exiting out'|| CHR(10);
418 RAISE_APPLICATION_ERROR(-20012, vc_job_log);
419 END IF;
420 -- process started
421 el_intstspk.process_started;
422 el_intstspk.status_rec.job_status := 'IN-PROGRESS';
423 -- get file directory name
424 BEGIN
425 SELECT setting
426 INTO vc_file_directory
427 FROM el_com_configcd
428 WHERE application ='ROS_SUB_EMP'
429 AND element = 'FILEDIR_EMP';
430 el_intstspk.status_rec.job_log := el_intstspk.status_rec.job_log || 'Fetching FILEDIR_EMP parameter: ' || vc_file_directory || ' @ ' || TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS') || CHR(10);
431 EXCEPTION
432 WHEN NO_DATA_FOUND THEN
433 v_err_type := c_setup;
434 vc_job_log := 'Configuration missing for the FILEDIR_EMP parameter in config table for application ROS_SUB_EMP' || CHR(10);
435 RAISE;
436 WHEN OTHERS THEN
437 v_err_type := c_oracle;
438 vc_job_log := 'Error while getting FILEDIR_EMP parameter from config table for application ROS_SUB_EMP' || CHR(10);
439 RAISE;
440 END;
441 el_intstspk.status_rec.job_log := el_intstspk.status_rec.job_log || 'Parsing the XML data from inbound table @ ' || TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS') || CHR(10);
442 -- process all AVAILABLE IZs
443 FOR rec_iz_id IN cur_iz_id
444 LOOP -- for cur_iz_id
445 BEGIN
446 --for flushing array
447 vtyp_tbl_assingment_staging.DELETE;
448 vtyp_tbl_pos_emp_staging.DELETE;
449 vi_num_iz_processed := vi_num_iz_processed + 1;
450 -- reset the record variable
451 rec_ref_interface := NULL;
452 BEGIN
453 -- generating a sequence value for passing to an inbound function
454 SELECT el_iz_recordset_id.NEXTVAL
455 INTO vc_rec_set_id
456 FROM DUAL;
457 --Calling procedure get_inbound_document which belongs to ICC Schema and
458 --is a part of PKG_INTERFACE package with IN and Out variable parameter
459 icc.pkg_interface.get_inbound_document ( vc_error_code_out, vc_error_level_out, vc_error_text_out, 500, rec_iz_id.iinb_iz_id, NULL, c_interface_name, vc_rec_set_id, curr_interface_rec );
460 -- check for the error from the middleware
461 IF (vc_error_text_out IS NOT NULL) THEN
462 RAISE e_iz_error;
463 END IF;
464 EXCEPTION
465 WHEN e_iz_error THEN
466 -- write error while calling procedure put_outbound_document to write XML to IZ
467 v_err_type := c_middleware;
468 vc_job_log := 'Read error '|| vc_error_text_out || ', while calling the procedure ICC.PKG_INTERFACE.GET_INBOUND_DOCUMENT for IZ ID: ' || TO_CHAR(rec_iz_id.iinb_iz_id) || CHR(10);
469 el_intstspk.process_skip_warning(v_err_type, vc_job_log);
470 RAISE e_next_iz;
471 WHEN OTHERS THEN
472 v_err_type := c_oracle;
473 vc_job_log := 'Error while calling the procedure ICC.PKG_INTERFACE.GET_INBOUND_DOCUMENT for IZ ID: ' || TO_CHAR(rec_iz_id.iinb_iz_id) || CHR(10);
474 el_intstspk.process_skip_warning(v_err_type, vc_job_log);
475 RAISE e_next_iz;
476 END;
477 -- logic to parse the XML data
478 BEGIN
479 FETCH curr_interface_rec INTO rec_ref_interface;
480 CLOSE curr_interface_rec;
481 v_parser := xmlparser.newparser;
482 IF rec_ref_interface.iinb_application_payload IS NULL THEN
483 v_err_type := c_middleware;
484 vc_job_log := 'Payload is blank for IZ ID: ' || TO_CHAR(rec_iz_id.iinb_iz_id) || CHR(10);
485 el_intstspk.process_skip_warning(v_err_type, vc_job_log);
486 RAISE e_next_iz;
487 END IF;
488 xmlparser.parseclob (v_parser,icc.pkg_integration_common.blob_to_clob (rec_ref_interface.iinb_application_payload));
489 v_document := xmlparser.getdocument (v_parser);
490 xmlparser.freeparser (v_parser);
491 v_nodelist := xslprocessor.selectnodes(xmldom.makenode (v_document),'/PersonnelList/Personnel');
492 vi_emp_index := 0;
493 vi_assignment_index:=0;
494
495 -- process the XML if nodelist is found
496 IF xmldom.getlength (v_nodelist) > 0 THEN
497 -- process looping nodes
498 FOR i IN 0 .. (xmldom.getlength (v_nodelist)) - 1
499 LOOP
500 v_node := xmldom.item (v_nodelist, i);
501 vi_emp_index := vi_emp_index + 1;
502 -- these tags will not repeat for Assignment node. Hence, parse only once.
503 v_employee_id := trim(xslprocessor.valueof (v_node, 'EmployeeID'));
504 v_action_code := xslprocessor.valueof (v_node, '@Action');
505 v_record_type := v_action_code;
506 v_first_name := xslprocessor.valueof (v_node, 'Name/FirstName');
507 v_last_name := xslprocessor.valueof (v_node, 'Name/LastName');
508 v_middle_name := xslprocessor.valueof (v_node, 'Name/MiddleName');
509 v_hire_date := TO_DATE(xslprocessor.valueof (v_node, 'HiredDate'),'YYYY-MM-DD');
510 v_original_hire_date := TO_DATE(xslprocessor.valueof (v_node, 'OriginalHiredDate'),'YYYY-MM-DD');
511 v_termination_date := TO_DATE(xslprocessor.valueof (v_node, 'TerminationDate'),'YYYY-MM-DD');
512 v_employee_status := xslprocessor.valueof (v_node, 'EmployeeStatus');
513 v_market_code := xslprocessor.valueof (v_node, 'MarketCode');
514
515 -- fetching employee status for employee
516 FOR emp_status IN cur_employee_status(trim(v_employee_status))
517 LOOP
518 BEGIN
519 v_employee_status:= emp_status.status;
520 EXCEPTION
521 WHEN OTHERS THEN
522 v_err_type := c_oracle;
523 vc_job_log := 'Error while calling cur_employee_status ' || CHR(10);
524 el_intstspk.process_warning (v_err_type, vc_job_log);
525 RAISE e_iz_error;
526 END;
527 END LOOP;
528
529 -- parsing for employee Assignment if Assignement NodeList found
530 v_assingment_nodelist :=xslprocessor.selectnodes(v_node,'Assignment');
531 IF xmldom.getlength (v_assingment_nodelist) > 0 THEN
532 -- process Assignement NodeList
533 FOR j IN 0 .. xmldom.getLength(v_assingment_nodelist) - 1
534 LOOP
535 v_asgn_node := xmldom.item(v_assingment_nodelist, j);
536 v_job_title := xslprocessor.valueof (v_asgn_node, 'JobTitle');
537 v_primary_group_id := xslprocessor.valueof (v_asgn_node, 'PrimaryGroupID');
538 v_division_code := xslprocessor.valueof (v_asgn_node, 'DivisionCode');
539 v_retail_loc_id := xslprocessor.valueof (v_asgn_node, 'RetailStoreID');
540 v_assignment_date := TO_DATE(xslprocessor.valueof (v_asgn_node, 'AssignmentDate'),'YYYY-MM-DD');
541 vtyp_tbl_assingment_staging.EXTEND();
542 vi_assignment_index := vtyp_tbl_assingment_staging.COUNT;
543 vtyp_tbl_assingment_staging(vi_assignment_index).employee_id := trim(v_employee_id);
544 vtyp_tbl_assingment_staging(vi_assignment_index).job_title := v_job_title;
545 vtyp_tbl_assingment_staging(vi_assignment_index).primary_group_id := v_primary_group_id;
546 vtyp_tbl_assingment_staging(vi_assignment_index).division_code := v_division_code;
547 vtyp_tbl_assingment_staging(vi_assignment_index).retail_loc_id := v_retail_loc_id;
548 vtyp_tbl_assingment_staging(vi_assignment_index).assignment_date := v_assignment_date;
549
550 -- When location changed from store to office.
551 if v_retail_loc_id is null then
552 v_employee_status := 'T';
553 end if;
554
555 --for empty assigment fetch retail_loc_id from xcenter 'hrs_employee_store' for update the in employee_store for same employee
556 IF(v_retail_loc_id IS NULL)THEN
557 BEGIN
558 -- if current employee retail_loc_id is null then flag true;
559 retail_loc_id_flag:=TRUE;
560 SELECT *
561 INTO xcenter_rtl_loc_id
562 FROM DTV.hrs_employee_store
563 WHERE employee_id = trim(v_employee_id)
564 AND end_date IS NULL;
565 vtyp_tbl_assingment_staging(vi_assignment_index).retail_loc_id := xcenter_rtl_loc_id.rtl_loc_id;
566
567 EXCEPTION
568 WHEN NO_DATA_FOUND THEN
569 xcenter_rtl_loc_id := NULL;
570 END;
571 END IF;
572
573 -- fetch group_membership base64 encoding based on primary_group_id
574 IF(v_primary_group_id IS NOT NULL) AND (v_employee_status !='T') THEN
575 BEGIN
576 SELECT base64_encoding
577 INTO grp_membership_binary_value
578 FROM ELR_XCN_EMP_GROUP_MEMBERSHIP
579 WHERE group_id = trim(v_primary_group_id);
580 END;
581 ELSE
582 BEGIN
583 SELECT base64_encoding
584 INTO grp_membership_binary_value
585 FROM ELR_XCN_EMP_GROUP_MEMBERSHIP
586 WHERE trim(group_id) = 'EVERYONE';
587 END;
588 END IF;
589 -- group membership value in vtyp_tbl_assingment_staging array
590 vtyp_tbl_assingment_staging(vi_assignment_index).group_membership := grp_membership_binary_value;
591 END LOOP; -- closed employee Assignment loop
592 END IF;
593
594 -- fetching action code
595 FOR action_type IN cur_action_type(v_action_code)
596 LOOP
597 BEGIN
598 IF(v_action_code='Available_CHANGE_LOC')THEN
599 --- change action code INSERT_ONLY if employee does not exist in xcenter same as current employee id
600 BEGIN
601 SELECT *
602 INTO xcenter_process_emp
603 FROM DTV.hrs_employee_store
604 WHERE employee_id= trim(v_employee_id)
605 AND end_date IS NULL;
606 v_action_code:=action_type.action;
607 EXCEPTION
608 WHEN NO_DATA_FOUND THEN
609 xcenter_process_emp := NULL;
610 v_action_code:='INSERT_ONLY';
611 END;
612
613 ELSE
614 v_action_code:= action_type.action;
615 END IF;
616
617 EXCEPTION
618 WHEN OTHERS THEN
619 v_err_type := c_oracle;
620 vc_job_log := 'Error while calling cur_action_type ' || CHR(10);
621 el_intstspk.process_warning (v_err_type, vc_job_log);
622 RAISE e_iz_error;
623 END;
624
625 -- fetch preferred locale for country from Market Code when action code is insert only
626 IF v_market_code IS NOT NULL AND (v_employee_status !='T') THEN
627 BEGIN
628 SELECT locale
629 INTO v_preferred_locale
630 FROM ELR_EMP_COUNTRY_LOCALE_MAPPING
631 WHERE COUNTRY = trim(v_market_code);
632 END;
633 ELSE
634 v_preferred_locale:=NULL;
635 END IF;
636
637 END LOOP;-- closed action code
638
639 vtyp_tbl_pos_emp_staging(vi_emp_index).employee_id := v_employee_id;
640 vtyp_tbl_pos_emp_staging(vi_emp_index).action_code := v_action_code;
641 vtyp_tbl_pos_emp_staging(vi_emp_index).first_name := v_first_name;
642 vtyp_tbl_pos_emp_staging(vi_emp_index).last_name := v_last_name;
643 vtyp_tbl_pos_emp_staging(vi_emp_index).middle_name := v_middle_name;
644 vtyp_tbl_pos_emp_staging(vi_emp_index).hire_date := v_hire_date;
645 vtyp_tbl_pos_emp_staging(vi_emp_index).original_hire_date := v_original_hire_date;
646 vtyp_tbl_pos_emp_staging(vi_emp_index).employee_status := v_employee_status;
647 vtyp_tbl_pos_emp_staging(vi_emp_index).market_code := v_market_code;
648 vtyp_tbl_pos_emp_staging(vi_emp_index).preferred_locale := v_preferred_locale;
649 vtyp_tbl_pos_emp_staging(vi_emp_index).termination_date := v_termination_date;
650 vtyp_tbl_pos_emp_staging(vi_emp_index).record_type := v_record_type;
651 v_iz_created_timestamp := rec_ref_interface.zzzz_created_timestamp;
652 END LOOP; -- closed nodelist loop
653 END IF;
654 EXCEPTION
655 WHEN OTHERS THEN
656 IF v_err_type IS NULL THEN
657 v_err_type := c_oracle;
658 vc_job_log := 'Error in procedure while parsing for IZ ID: ' || TO_CHAR(rec_ref_interface.iinb_iz_id) || CHR(10);
659 el_intstspk.process_skip_warning(v_err_type, vc_job_log);
660 END IF;
661 RAISE e_next_iz;
662 END; -- close logic to parse the XML data
663
664 -- logic to insert/update EMP data into the log table from type vtyp_tbl_pos_emp_staging
665 IF (vi_emp_index > 0) THEN
666 -- insert in to log table elr_xcn_emp_staging_log
667 DECLARE
668 vi_error_count PLS_INTEGER := 0;
669 e_bulk_errors EXCEPTION;
670 PRAGMA EXCEPTION_INIT(e_bulk_errors, -24381);
671 BEGIN
672 FORALL emp_cntr IN 1..vi_emp_index SAVE EXCEPTIONS
673 INSERT
674 INTO elr_xcn_emp_staging_log
675 (
676 iinb_iz_id,
677 employee_id,
678 employee_status,
679 action_code,
680 first_name,
681 last_name,
682 middle_name,
683 preferred_locale,
684 iz_created_timestamp,
685 create_date,
686 hire_date,
687 original_hire_date,
688 market_code,
689 termination_date,
690 record_type
691 )
692 VALUES
693 (
694 rec_ref_interface.iinb_iz_id,
695 vtyp_tbl_pos_emp_staging (emp_cntr).employee_id,
696 vtyp_tbl_pos_emp_staging(emp_cntr).employee_status,
697 vtyp_tbl_pos_emp_staging (emp_cntr).action_code,
698 vtyp_tbl_pos_emp_staging (emp_cntr).first_name,
699 vtyp_tbl_pos_emp_staging (emp_cntr).last_name,
700 vtyp_tbl_pos_emp_staging (emp_cntr).middle_name,
701 TRIM(vtyp_tbl_pos_emp_staging(emp_cntr).preferred_locale), -- Pref Locale
702 v_iz_created_timestamp,
703 SYSDATE,
704 vtyp_tbl_pos_emp_staging(emp_cntr).hire_date,
705 vtyp_tbl_pos_emp_staging(emp_cntr).original_hire_date,
706 vtyp_tbl_pos_emp_staging(emp_cntr).market_code,
707 vtyp_tbl_pos_emp_staging(emp_cntr).termination_date,
708 vtyp_tbl_pos_emp_staging(emp_cntr).record_type
709 );
710 EXCEPTION
711 -- do not propogate the exception, allow to process next record
712 WHEN e_bulk_errors THEN
713 vi_error_count := SQL%BULK_EXCEPTIONS.COUNT;
714 FOR i IN 1 .. vi_error_count
715 LOOP
716 v_err_type := c_oracle;
717 vc_job_log := 'Error: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE) || ', while inserting into log table ELR_XCN_EMP_STAGING_LOG for IZ ID: ' || TO_CHAR(rec_ref_interface.iinb_iz_id) || ' and Party ID: ' || TRIM(vtyp_tbl_pos_emp_staging (SQL%BULK_EXCEPTIONS(i).error_index).employee_id) || '. Skipping this record.' || CHR(10);
718 el_intstspk.process_warning(v_err_type, vc_job_log);
719 END LOOP;
720 v_err_type := NULL;
721 vc_job_log := NULL;
722 RAISE e_next_iz;
723 END;
724 -- No data matching for Personnel node
725 ELSE
726 v_err_type := c_oracle;
727 vc_job_log := 'No data found for the object type: ' || c_interface_name || ' in XML for IZ ID: ' || TO_CHAR(rec_iz_id.iinb_iz_id) || CHR(10);
728 el_intstspk.process_warning(v_err_type, vc_job_log);
729 RAISE e_next_iz;
730 END IF; -- closed logic insert into elr_xcn_emp_staging_log
731
732 --- inseting in elr_xcn_emp_assgn_staging_log table
733 IF (vi_assignment_index > 0) THEN
734 -- insert in to log table elr_xcn_emp_staging_log
735 DECLARE
736 vi_error_count PLS_INTEGER := 0;
737 e_bulk_errors EXCEPTION;
738 PRAGMA EXCEPTION_INIT(e_bulk_errors, -24381);
739 BEGIN
740 FORALL emp_assignment IN 1..vi_assignment_index SAVE EXCEPTIONS
741 INSERT
742 INTO elr_xcn_emp_assgn_staging_log
743 (
744 iinb_iz_id,
745 employee_id,
746 job_title,
747 primary_group_id,
748 division_code,
749 retail_loc_id,
750 assignment_date,
751 group_membership,
752 processed_date
753 )
754 VALUES
755 (
756 rec_ref_interface.iinb_iz_id,
757 vtyp_tbl_assingment_staging (emp_assignment).employee_id,
758 vtyp_tbl_assingment_staging(emp_assignment).job_title,
759 TRIM(vtyp_tbl_assingment_staging(emp_assignment).primary_group_id),
760 vtyp_tbl_assingment_staging(emp_assignment).division_code,
761 vtyp_tbl_assingment_staging(emp_assignment).retail_loc_id,
762 vtyp_tbl_assingment_staging(emp_assignment).assignment_date,
763 vtyp_tbl_assingment_staging(emp_assignment).group_membership,
764 NULL
765 );
766 EXCEPTION
767 -- do not propogate the exception, allow to process next record
768 WHEN e_bulk_errors THEN
769 vi_error_count := SQL%BULK_EXCEPTIONS.COUNT;
770 FOR i IN 1 .. vi_error_count
771 LOOP
772 v_err_type := c_oracle;
773 vc_job_log := 'Error: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE) || ', while inserting into log table elr_xcn_emp_assgn_staging_log for IZ ID: ' || TO_CHAR(rec_ref_interface.iinb_iz_id) || ', Store #: ' || TRIM(vtyp_tbl_assingment_staging(SQL%BULK_EXCEPTIONS(i).error_index).retail_loc_id) || ' and Employee ID: ' || TRIM(vtyp_tbl_assingment_staging (SQL%BULK_EXCEPTIONS(i).error_index).employee_id) || '. Skipping this record.' || CHR(10);
774 el_intstspk.process_warning(v_err_type, vc_job_log);
775 END LOOP;
776 v_err_type := NULL;
777 vc_job_log := NULL;
778 RAISE e_next_iz;
779 END;
780 -- No data matching for Personnel node
781 ELSE
782 v_err_type := c_oracle;
783 vc_job_log := 'No data found for the object type: ' || c_interface_name || ' in XML for IZ ID: ' || TO_CHAR(rec_iz_id.iinb_iz_id) || CHR(10);
784 el_intstspk.process_warning(v_err_type, vc_job_log);
785 RAISE e_next_iz;
786 END IF; -- closed logic insert into elr_xcn_emp_assgn_staging_log
787 -- update IZ status to PROCESSED
788 update_iz_status( 'PROCESSED', rec_iz_id.iinb_iz_id, rec_ref_interface.iinb_iz_uuid, rec_ref_interface.iinb_iz_record_set_id, rec_ref_interface.iinb_object_type );
789 -- commit the changes for current IZ
790 COMMIT;
791 -- main exception for cur_iz_id FOR LOOP
792 EXCEPTION
793 WHEN e_next_iz THEN
794 -- set the current IZ status to FAIL in inbound table.
795 BEGIN
796 update_iz_status( 'FAIL', rec_iz_id.iinb_iz_id, rec_ref_interface.iinb_iz_uuid, rec_ref_interface.iinb_iz_record_set_id, rec_ref_interface.iinb_object_type );
797 EXCEPTION
798 WHEN OTHERS THEN
799 NULL;
800 END;
801 -- reset error variables
802 v_err_type := NULL;
803 vc_job_log := NULL;
804 WHEN OTHERS THEN
805 -- do not propogate the exception, allow to process next record
806 IF vc_job_log IS NULL THEN
807 v_err_type := c_oracle;
808 vc_job_log := 'Error in procedure while parsing the XML for IZ ID: ' || TO_CHAR(rec_ref_interface.iinb_iz_id) || CHR(10);
809 el_intstspk.process_skip_warning(v_err_type, vc_job_log);
810 -- set the current IZ status to FAIL in inbound table.
811 BEGIN
812 update_iz_status( 'FAIL', rec_iz_id.iinb_iz_id, rec_ref_interface.iinb_iz_uuid, rec_ref_interface.iinb_iz_record_set_id, rec_ref_interface.iinb_object_type );
813 EXCEPTION
814 WHEN OTHERS THEN
815 NULL;
816 END;
817 END IF;
818 --reset error variables
819 v_err_type := NULL;
820 vc_job_log := NULL;
821 END;
822 END LOOP; -- for cur_iz_id
823
824 IF (vi_num_iz_processed > 0) THEN
825 el_intstspk.status_rec.job_log := el_intstspk.status_rec.job_log || 'Writing into EMPLOYEE, PARTY and EMPLOYEE STORE MNT files @ ' || TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS') || CHR(10);
826
827 delete from ELR_XCN_EMP_STORE_RECORD_STG;
828 commit;
829
830 -- start wrting into MNT files for each store
831 --cursor for fetch store from elr_xcn_emp_assgn_staging_log
832 FOR rec_store IN cur_store
833 LOOP
834 DECLARE
835
836 --count for emp record
837 vi_emp_count PLS_INTEGER := 1;
838 vi_party_count PLS_INTEGER := 1;
839 vi_emp_store_count PLS_INTEGER := 1;
840 vi_emp_password_count PLS_INTEGER := 1;
841 vi_emp_answers_count PLS_INTEGER := 1;
842 vi_emp_delete_store_count PLS_INTEGER := 1;
843
844 -- count for exist emp record
845 vi_exist_emp_count PLS_INTEGER := 1;
846 vi_exist_party_count PLS_INTEGER := 1;
847 vi_exist_emp_store_count PLS_INTEGER :=1;
848
849 BEGIN
850 vb_first := TRUE;
851 vb_file_close := TRUE;
852 vc_emp_prev_line := NULL;
853 vc_party_prev_line := NULL;
854 -- increment store count
855 vi_store_count := vi_store_count + 1;
856
857-- isHeaderCreatedForEmpExists := FALSE;
858-- isHeaderCreatedForEmpNotExists := FALSE;
859-- isTrlrCreatedForEmpExists := FALSE;
860-- isTrlrCreatedForEmpNotExists := FALSE;
861
862 exist_employee_varray.delete;
863 exist_party_varray.delete;
864 exist_employee_store_varray.delete;
865 employee_varray.delete;
866 party_varray.delete;
867 employee_store_varray.delete;
868 employee_password_varray.delete;
869 employee_delete_store_varray.delete;
870 employee_answers_varray.delete;
871
872 -- Writing data into mnt files.
873 FOR rec_emp_store IN cur_emp_store(trim(rec_store.retail_loc_id))
874 LOOP
875
876 BEGIN
877 -- Fetch Employee records from Xcenter.
878 BEGIN
879 SELECT *
880 INTO xcenter_process_emp
881 FROM DTV.hrs_employee_store
882 WHERE employee_id = trim(rec_emp_store.employee_id)
883 AND end_date IS NULL
884 and begin_date is not null;
885 EXCEPTION
886 WHEN NO_DATA_FOUND THEN
887 xcenter_process_emp := NULL;
888 END;
889
890
891 -- insert retail_loc_id value of existing employee for generating Available_change_loc MNT
892 vi_exists_employee_store:=xcenter_process_emp.rtl_loc_id;
893 vi_exists_xcenter_employee:=xcenter_process_emp.employee_id;
894 vi_current_employee:=rec_emp_store.employee_id;
895 vi_current_emp_action_code:=rec_emp_store.action_code;
896 v_empPasswordDeleteStore := null;
897
898 -- This is the only condition used for terminating employee with Future date. It just sets employee termination date as
899 -- future termination date in 'hrs_employee' table only.
900 if rec_emp_store.record_type = 'Available_TERMINATE' and rec_emp_store.employee_status = 'A' then
901 exist_employee_varray(vi_exist_emp_count):= xcenter_process_emp.rtl_loc_id || ':' || 'UPDATE_SELECT'|| '|' || 'EMPLOYEE' || '|' || TRIM(rec_emp_store.employee_id) || '|' || TRIM(rec_emp_store.employee_id) || '|' || TO_CHAR(rec_emp_store.HIRE_DATE,'YYYY-MM-DD') || '|' || TO_CHAR(rec_emp_store.original_hire_date,'YYYY-MM-DD') || '|' || TO_CHAR(rec_emp_store.HIRE_DATE,'YYYY-MM-DD') || '|' || TO_CHAR(rec_emp_store.termination_date,'YYYY-MM-DD') || '|' || TRIM(rec_emp_store.employee_status) || '|||||' || rec_emp_store.JOB_TITLE || '|||||||||||||||||||' || 'EXEMPT' || '|' || TRIM(rec_emp_store.primary_group_id) || '|' || TRIM(rec_emp_store.group_membership) || '|' || TRIM(rec_emp_store.employee_id) || '||' ;
902 vi_exist_emp_count := vi_exist_emp_count+1;
903 continue;
904 end if;
905
906 -- Updating exising employee information.
907 if rec_emp_store.record_type = 'Available_CHANGE' and rec_emp_store.employee_status = 'A' then
908 exist_employee_varray(vi_exist_emp_count):= xcenter_process_emp.rtl_loc_id || ':' || 'UPDATE_SELECT'|| '|' || 'EMPLOYEE' || '|' || TRIM(rec_emp_store.employee_id) || '|' || TRIM(rec_emp_store.employee_id) || '|' || TO_CHAR(rec_emp_store.HIRE_DATE,'YYYY-MM-DD') || '|' || TO_CHAR(rec_emp_store.original_hire_date,'YYYY-MM-DD') || '|' || TO_CHAR(rec_emp_store.HIRE_DATE,'YYYY-MM-DD') || '|' || TO_CHAR(rec_emp_store.termination_date,'YYYY-MM-DD') || '|' || TRIM(rec_emp_store.employee_status) || '|||||' || rec_emp_store.JOB_TITLE || '|||||||||||||||||||' || 'EXEMPT' || '|' || TRIM(rec_emp_store.primary_group_id) || '|' || TRIM(rec_emp_store.group_membership) || '|' || TRIM(rec_emp_store.employee_id) || '||' ;
909 vi_exist_emp_count := vi_exist_emp_count+1;
910
911 exist_party_varray (vi_exist_party_count ):= xcenter_process_emp.rtl_loc_id || ':' || 'UPDATE_SELECT' || '|' || 'PARTY' || '|' || TRIM(rec_emp_store.employee_id) || '|' || TRIM(rec_emp_store.employee_id) || '|' || 'EMPLOYEE' || '|' || NULL || '|' || TRIM(rec_emp_store.first_name)|| '|' || TRIM(rec_emp_store.middle_name) || '|' ||TRIM(rec_emp_store.last_name) || '||||||||||||||||' || NULL|| '|' || NULL || '||||||||||' || TRIM(rec_emp_store.employee_id) || '|' || '|||||||||||' || TRIM(rec_emp_store.preferred_locale) || '|||' || TRIM(rec_emp_store.party_status) || '|';
912 vi_exist_party_count :=vi_exist_party_count +1;
913
914 continue;
915 end if;
916
917 BEGIN
918 -- Check if the same record is already present in Xcenter.
919 IF(rec_emp_store.action_code='INSERT_ONLY' AND trim(xcenter_process_emp.rtl_loc_id) = trim(rec_emp_store.retail_loc_id)
920 AND trim(xcenter_process_emp.employee_id) = trim(rec_emp_store.employee_id)) THEN
921
922 v_err_type := c_oracle;
923 vc_job_log := 'Employee'|| TRIM(xcenter_process_emp.employee_id) || 'RECORD ALRADY EXITS IN '|| xcenter_process_emp.rtl_loc_id || 'store '|| CHR(10);
924 --el_intstspk.process_warning (v_err_type, vc_job_log);
925
926 --skip current record and pick next record
927 CONTINUE
928 WHEN xcenter_process_emp.employee_id=rec_emp_store.employee_id AND xcenter_process_emp.rtl_loc_id=rec_emp_store.retail_loc_id;
929 END IF;
930 END;
931
932 -- increment Store count for which MNT file is generated
933 vi_mnt_store_count := vi_mnt_store_count + 1;
934
935 ----------------------------------Employee mnt--------------------------------------------------
936 -- Check if Employee is Active.
937 IF rec_emp_store.employee_status = 'A' THEN
938 vc_write_file := 'Employee';
939
940 -- Action = Available Change Loc.
941 IF(rec_emp_store.action_code='UPDATE' AND trim(xcenter_process_emp.rtl_loc_id) != trim(rec_emp_store.retail_loc_id)
942 AND trim(xcenter_process_emp.employee_id) = trim(rec_emp_store.employee_id))THEN
943
944 -- Create record for new store mnt
945 employee_varray(vi_emp_count) := rec_emp_store.retail_loc_id || ':' || 'INSERT_ONLY' || '|' || 'EMPLOYEE' || '|' || TRIM(rec_emp_store.employee_id) || '|' || TRIM(rec_emp_store.employee_id) || '|' || TO_CHAR(rec_emp_store.HIRE_DATE,'YYYY-MM-DD') || '|' || TO_CHAR(rec_emp_store.original_hire_date,'YYYY-MM-DD') || '|' || TO_CHAR(rec_emp_store.HIRE_DATE,'YYYY-MM-DD') || '|' || NULL || '|' || TRIM(rec_emp_store.employee_status) || '|||||' || rec_emp_store.JOB_TITLE || '|||||||||||||||||||' || 'EXEMPT' || '|' || TRIM(rec_emp_store.primary_group_id) || '|' || TRIM(rec_emp_store.group_membership) || '|' || TRIM(rec_emp_store.employee_id) || '||' ;
946 vi_emp_count :=vi_emp_count + 1;
947
948 -- Create record for old Xcenter store mnt
949 exist_employee_varray(vi_exist_emp_count):= xcenter_process_emp.rtl_loc_id || ':' || TRIM(rec_emp_store.action_code)|| '|' || 'EMPLOYEE' || '|' || TRIM(rec_emp_store.employee_id) || '|' || TRIM(rec_emp_store.employee_id) || '|' || TO_CHAR(rec_emp_store.HIRE_DATE,'YYYY-MM-DD') || '|' || TO_CHAR(rec_emp_store.original_hire_date,'YYYY-MM-DD') || '|' || TO_CHAR(rec_emp_store.HIRE_DATE,'YYYY-MM-DD') || '|' || NULL || '|' || TRIM(rec_emp_store.employee_status) || '|||||' || rec_emp_store.JOB_TITLE || '|||||||||||||||||||' || 'EXEMPT' || '|' || TRIM(rec_emp_store.primary_group_id) || '|' || TRIM(rec_emp_store.group_membership) || '|' || TRIM(rec_emp_store.employee_id) || '||' ;
950 vi_exist_emp_count := vi_exist_emp_count+1;
951
952 ELSE
953 employee_varray(vi_emp_count) := rec_emp_store.retail_loc_id || ':' || TRIM(rec_emp_store.action_code) || '|' || 'EMPLOYEE' || '|' || TRIM(rec_emp_store.employee_id) || '|' || TRIM(rec_emp_store.employee_id) || '|' || TO_CHAR(rec_emp_store.HIRE_DATE,'YYYY-MM-DD') || '|' || TO_CHAR(rec_emp_store.original_hire_date,'YYYY-MM-DD') || '|' || TO_CHAR(rec_emp_store.HIRE_DATE,'YYYY-MM-DD') || '|' || NULL || '|' || TRIM(rec_emp_store.employee_status) || '|||||' || rec_emp_store.JOB_TITLE || '|||||||||||||||||||' || 'EXEMPT' || '|' || TRIM(rec_emp_store.primary_group_id) || '|' || TRIM(rec_emp_store.group_membership) || '|' || TRIM(rec_emp_store.employee_id) || '||' ;
954 vi_emp_count :=vi_emp_count+1;
955 END IF;
956 END IF;
957
958 -- Check if Employee is Terminated.
959 IF rec_emp_store.employee_status ='T' THEN
960
961 vc_write_file := 'Employee';
962 if rec_emp_store.termination_date is not null then
963 employee_varray(vi_emp_count) := rec_emp_store.retail_loc_id || ':' || TRIM(rec_emp_store.action_code) || '|' || 'EMPLOYEE' || '|' || TRIM(rec_emp_store.employee_id) || '|' || NULL || '|' || NULL || '|' ||NULL || '|' || NULL || '|' || NULL || '|' || NULL || '|||||' || NULL || '|||||||||||||||||||' || NULL || '|' || NULL || '|' || NULL || '|' || NULL || '||' ;
964 else
965 employee_varray(vi_emp_count) := rec_emp_store.retail_loc_id || ':' || TRIM(rec_emp_store.action_code) || '|' || 'EMPLOYEE' || '|' || TRIM(rec_emp_store.employee_id) || '|' || NULL || '|' || NULL || '|' ||NULL || '|' || NULL || '|' || NULL || '|' || NULL || '|||||' || NULL || '|||||||||||||||||||' || NULL || '|' || NULL || '|' || NULL || '|' || NULL || '||' ;
966 end if;
967
968 vi_emp_count :=vi_emp_count+1;
969
970 END IF;
971
972 ----------------------------------Party mnt--------------------------------------------
973
974 IF rec_emp_store.employee_status ='A' THEN
975
976 vc_write_file := 'Party';
977
978 IF(rec_emp_store.action_code = 'UPDATE' AND trim(xcenter_process_emp.rtl_loc_id) != trim(rec_emp_store.retail_loc_id)
979 AND trim(xcenter_process_emp.employee_id) = trim(rec_emp_store.employee_id))THEN
980
981 party_varray(vi_party_count) := rec_emp_store.retail_loc_id || ':' || 'INSERT_ONLY' || '|' || 'PARTY' || '|' || TRIM(rec_emp_store.employee_id) || '|' || TRIM(rec_emp_store.employee_id) || '|' || 'EMPLOYEE' || '|' || NULL || '|' || TRIM(rec_emp_store.first_name)|| '|' || TRIM(rec_emp_store.middle_name) || '|' ||TRIM(rec_emp_store.last_name) || '||||||||||||||||' || NULL|| '|' || NULL || '||||||||||' || TRIM(rec_emp_store.employee_id) || '|' || '|||||||||||' || TRIM(rec_emp_store.preferred_locale) || '|||' || TRIM(rec_emp_store.party_status) || '|';
982 vi_party_count :=vi_party_count+1;
983
984 exist_party_varray (vi_exist_party_count ):= xcenter_process_emp.rtl_loc_id || ':' || TRIM(rec_emp_store.action_code) || '|' || 'PARTY' || '|' || TRIM(rec_emp_store.employee_id) || '|' || TRIM(rec_emp_store.employee_id) || '|' || 'EMPLOYEE' || '|' || NULL || '|' || TRIM(rec_emp_store.first_name)|| '|' || TRIM(rec_emp_store.middle_name) || '|' ||TRIM(rec_emp_store.last_name) || '||||||||||||||||' || NULL|| '|' || NULL || '||||||||||' || TRIM(rec_emp_store.employee_id) || '|' || '|||||||||||' || TRIM(rec_emp_store.preferred_locale) || '|||' || TRIM(rec_emp_store.party_status) || '|';
985 vi_exist_party_count :=vi_exist_party_count +1;
986
987 -- Action = Others
988 ELSE
989 party_varray(vi_party_count) := rec_emp_store.retail_loc_id || ':' || TRIM(rec_emp_store.action_code) || '|' || 'PARTY' || '|' || TRIM(rec_emp_store.employee_id) || '|' || TRIM(rec_emp_store.employee_id) || '|' || 'EMPLOYEE' || '|' || NULL || '|' || TRIM(rec_emp_store.first_name)|| '|' || TRIM(rec_emp_store.middle_name) || '|' ||TRIM(rec_emp_store.last_name) || '||||||||||||||||' || NULL|| '|' || NULL || '||||||||||' || TRIM(rec_emp_store.employee_id) || '|' || '|||||||||||' || TRIM(rec_emp_store.preferred_locale) || '|||' || TRIM(rec_emp_store.party_status) || '|';
990 vi_party_count :=vi_party_count+1;
991 END IF;
992
993 END IF;
994
995 -- Check if Employee is Terminated.
996 IF rec_emp_store.employee_status ='T' THEN
997
998 vc_write_file := 'Party';
999 party_varray(vi_party_count) := rec_emp_store.retail_loc_id || ':' || TRIM(rec_emp_store.action_code) || '|' || 'PARTY' || '|' || TRIM(rec_emp_store.employee_id) || '|' || NULL || '|' || NULL || '|' || NULL || '|' || NULL|| '|' || NULL || '|' || NULL || '||||||||||||||||' || NULL|| '|' || NULL || '||||||||||' || NULL || '|' || '|||||||||||' || NULL || '|||' || TRIM(rec_emp_store.party_status) || '|';
1000 vi_party_count :=vi_party_count+1;
1001
1002 END IF;
1003
1004 -----------------------------------Employee Store mnt---------------------------------------------
1005
1006 IF(rec_emp_store.employee_status = 'A') THEN
1007 -- Available_TERMINATE','Available_CHANGE_LOC','Available_REHIRE','Available_REV_TERMINATE'
1008 IF(rec_emp_store.action_code = 'UPDATE' AND trim(xcenter_process_emp.rtl_loc_id) != trim(rec_emp_store.retail_loc_id)
1009 AND trim(xcenter_process_emp.employee_id) = trim(rec_emp_store.employee_id))THEN
1010
1011 -- writing data into employee store file for Action code, EmployeeId, PreviouStore, NULL, AssignmentEndDate ,and end_date is sysdate
1012 exist_employee_store_varray(vi_exist_emp_store_count) := xcenter_process_emp.rtl_loc_id || ':' || TRIM(rec_emp_store.action_code) || '|EMPLOYEE_STORE|' || TRIM(rec_emp_store.employee_id) || '|' || TRIM(xcenter_process_emp.rtl_loc_id) || '|' || TO_CHAR(rec_emp_store.assignment_date,'YYYY-MM-DD') || '|' || TO_CHAR(sysdate-1,'YYYY-MM-DD') ||'|FALSE';
1013 vi_exist_emp_store_count:=vi_exist_emp_store_count+1;
1014
1015 employee_store_varray(vi_emp_store_count) := rec_emp_store.retail_loc_id || ':' || 'INSERT_ONLY' || '|EMPLOYEE_STORE|' || TRIM(rec_emp_store.employee_id) || '|' || TRIM(rec_emp_store.retail_loc_id) || '|' || TO_CHAR(rec_emp_store.assignment_date,'YYYY-MM-DD') || '|' || NULL ||'|FALSE';
1016 vi_emp_store_count :=vi_emp_store_count+1;
1017
1018 -- Action = Others
1019 ELSE
1020 employee_store_varray(vi_emp_store_count) := rec_emp_store.retail_loc_id || ':' || TRIM(rec_emp_store.action_code) || '|EMPLOYEE_STORE|' || TRIM(rec_emp_store.employee_id) || '|' || TRIM(rec_emp_store.retail_loc_id) || '|' || TO_CHAR(rec_emp_store.assignment_date,'YYYY-MM-DD') || '|' || TO_CHAR(rec_emp_store.termination_date,'YYYY-MM-DD') ||'|FALSE';
1021 vi_emp_store_count:=vi_emp_store_count+1;
1022
1023 END IF;
1024
1025 elsif rec_emp_store.employee_status = 'T' then
1026
1027 if rec_emp_store.action_code = 'UPDATE' then
1028
1029 -- fetching all stores from Xcenter stores where employee has assignment.
1030 FOR rec_emp_store_delete IN cur_emp_store_delete(trim(rec_emp_store.employee_id))
1031 LOOP
1032 BEGIN
1033 vc_write_file :='Employee Store';
1034
1035 if rec_emp_store.termination_date is not null then
1036 employee_delete_store_varray(vi_emp_delete_store_count) := rec_emp_store_delete.rtl_loc_id || ':' || 'UPDATE' || '|EMPLOYEE_STORE|' || TRIM(rec_emp_store.employee_id) || '|' || TRIM(rec_emp_store_delete.rtl_loc_id) || '|' || null|| '|' || null ||'|';
1037 else
1038 employee_delete_store_varray(vi_emp_delete_store_count) := rec_emp_store_delete.rtl_loc_id || ':' || 'UPDATE' || '|EMPLOYEE_STORE|' || TRIM(rec_emp_store.employee_id) || '|' || TRIM(rec_emp_store_delete.rtl_loc_id) || '|' || null|| '|' || null ||'|';
1039 end if;
1040
1041 -- Deleting all employee assignments from all stores in Xcenter.
1042
1043 vi_emp_delete_store_count := vi_emp_delete_store_count+1;
1044 END ;
1045 END LOOP;
1046
1047 -- fetching all Employee password records from Xcenter stores.
1048 FOR rec_emp_password IN cur_emp_password(trim(rec_emp_store.employee_id))
1049 LOOP
1050 BEGIN
1051 vc_write_file := 'EMPLOYEE_PASSWORD';
1052 --emp_password_flag check use for generating mnt file
1053 v_empPasswordDeleteStore := rec_emp_store.retail_loc_id;
1054 -- writing data into employee_Password mnt file: Action Code,Record Identifier,Employee Id(PK),Password, Sequence (PK),Effective Date,Current Password Flag,Temporary Flag
1055 employee_password_varray(vi_emp_password_count) := rec_emp_store.retail_loc_id || ':' || 'UPDATE' || '|EMPLOYEE_PASSWORD|' || TRIM(rec_emp_password.employee_id) || '|' || NULL || '|' || TO_CHAR(rec_emp_password.password_seq) || '|' || TO_CHAR(rec_emp_password.effective_date,'YYYY-MM-DD') || '|' || TO_CHAR(rec_emp_password.current_password_flag)||'|' || TO_CHAR(rec_emp_password.temp_password_flag);
1056 vi_emp_password_count :=vi_emp_password_count+1;
1057 END;
1058 END LOOP;
1059
1060 -- fetching all Employee security questions answers records from Xcenter stores.
1061 FOR rec_emp_answers IN cur_emp_answers(trim(rec_emp_store.employee_id))
1062 LOOP
1063 BEGIN
1064 vc_write_file := 'EMPLOYEE_ANSWERS';
1065 employee_answers_varray(vi_emp_answers_count) := rec_emp_store.retail_loc_id || ':' || 'DELETE' || '|EMPLOYEE_ANSWERS|' || rec_emp_answers.ORGANIZATION_ID || '|' || rec_emp_answers.employee_id || '|' || rec_emp_answers.CHALLENGE_CODE;
1066 vi_emp_answers_count :=vi_emp_answers_count+1;
1067 END;
1068 END LOOP;
1069
1070
1071 -- Action = Others, like 'Available_Change' for rev
1072 else
1073 employee_store_varray(vi_emp_store_count) := rec_emp_store.retail_loc_id || ':' || TRIM(rec_emp_store.action_code) || '|EMPLOYEE_STORE|' || TRIM(rec_emp_store.employee_id) || '|' || TRIM(rec_emp_store.retail_loc_id) || '|' || TO_CHAR(rec_emp_store.assignment_date,'YYYY-MM-DD') || '|' || TO_CHAR(rec_emp_store.termination_date,'YYYY-MM-DD') ||'|FALSE';
1074 vi_emp_store_count:=vi_emp_store_count+1;
1075
1076 end if;
1077
1078 end if;
1079
1080 ------------------Password mnt for 'Available_REHIRE' and 'Available_REV_TERMINATE'-----------------------------
1081
1082 if (rec_emp_store.record_type = 'Available_REHIRE' or rec_emp_store.record_type = 'Available_REV_TERMINATE')then
1083 FOR rec_emp_password IN cur_emp_password(trim(rec_emp_store.employee_id))
1084 LOOP
1085 BEGIN
1086 vc_write_file := 'EMPLOYEE_PASSWORD';
1087
1088 --emp_password_flag check use for generating mnt file
1089 v_empPasswordDeleteStore := rec_emp_store.retail_loc_id;
1090 -- writing data into employee_Password mnt file: Action Code,Record Identifier,Employee Id(PK),Password, Sequence (PK),Effective Date,Current Password Flag,Temporary Flag
1091 employee_password_varray(vi_emp_password_count) := rec_emp_store.retail_loc_id || ':' || TRIM(rec_emp_store.action_code) || '|EMPLOYEE_PASSWORD|' || TRIM(rec_emp_password.employee_id) || '|' || 'Z2XD2xI4rYk' || '|' || TO_CHAR(rec_emp_password.password_seq) || '|' || TO_CHAR(rec_emp_password.effective_date,'YYYY-MM-DD') || '|' || TO_CHAR(rec_emp_password.current_password_flag)||'|' || '1';
1092 vi_emp_password_count :=vi_emp_password_count+1;
1093 END;
1094 END LOOP;
1095 end if;
1096
1097 EXCEPTION
1098 WHEN OTHERS THEN
1099 -- log the error if not already logged
1100 IF v_err_type IS NULL THEN
1101 v_err_type := c_oracle;
1102 vc_job_log := 'Error in procedure while writing into ' || vc_write_file || ' file for Store #: ' || TRIM(rec_emp_store.retail_loc_id) || ' and Employee ID: ' || rec_emp_store.employee_id || '. Skipping this record' || CHR(10);
1103 el_intstspk.process_skip_warning(v_err_type, vc_job_log);
1104 END IF;
1105 RAISE e_next_store;
1106 END;
1107
1108 END LOOP; -- cur_emp_store
1109
1110 EXCEPTION
1111 WHEN e_next_store THEN
1112 -- increment fail count
1113 vi_store_fail_count := vi_store_fail_count + 1;
1114 -- close the open files and remove them
1115 -- emp file
1116 BEGIN
1117 IF UTl_FILE.IS_OPEN(file_handler_emp) = TRUE THEN
1118 UTL_FILE.FCLOSE(file_handler_emp);
1119 UTL_FILE.FREMOVE(vc_file_directory, vc_emp_filename);
1120 END IF;
1121 EXCEPTION
1122 WHEN OTHERS THEN
1123 v_err_type := c_oracle;
1124 vc_job_log := 'Error while closing and removing the file ' || vc_emp_filename || ' for the Store #: ' || TRIM(rec_store.RETAIL_LOC_ID) || CHR(10) || '!!! REMOVE THIS FILE FROM DIRECTORY: ' || vc_file_directory || ' !!!' || CHR(10);
1125 el_intstspk.process_skip_warning(v_err_type, vc_job_log);
1126 END;
1127 v_err_type := NULL;
1128 vc_job_log := NULL;
1129 WHEN OTHERS THEN
1130 -- increment fail count
1131 vi_store_fail_count := vi_store_fail_count + 1;
1132 v_err_type := c_oracle;
1133 vc_job_log := 'Error occured in procedure while generating MNT file for Store #: ' || TRIM(v_retail_loc_id) || '. Skipping this record.' || CHR(10);
1134 el_intstspk.process_skip_warning(v_err_type, vc_job_log);
1135 -- reset error variables
1136 v_err_type := NULL;
1137 vc_job_log := NULL;
1138 END;
1139
1140 --start trailer file for each store and count in trailer file
1141 DECLARE
1142 vc_error_file NVARCHAR2(100);
1143 vc_trailer_file NVARCHAR2(100);
1144
1145 BEGIN
1146 --Creating mnt headers.
1147 vc_error_file := 'creating Employee';
1148 END;
1149
1150
1151 v_stg_serial := 0;
1152 v_stgRecordStore := null;
1153 v_stgRecord := null;
1154
1155 FOR indexCount IN 1 .. exist_employee_varray.COUNT
1156 LOOP
1157 v_stg_serial := v_stg_serial + 1;
1158
1159 SELECT INSTR(exist_employee_varray(indexCount), ':') into charIndex from dual;
1160 v_stgRecordStore := substr(exist_employee_varray(indexCount),0,charIndex -1);
1161 v_stgRecord := substr(exist_employee_varray(indexCount),charIndex+1);
1162
1163 insert into ELR_XCN_EMP_STORE_RECORD_STG(serial,store_id,record_type,record_str) values(
1164 v_stg_serial, v_stgRecordStore, 'Employee', v_stgRecord);
1165 commit;
1166 END LOOP;
1167
1168 v_stgRecordStore := null;
1169 v_stgRecord := null;
1170
1171 FOR indexCount IN 1 .. exist_party_varray.COUNT
1172 LOOP
1173 v_stg_serial := v_stg_serial + 1;
1174
1175 SELECT INSTR(exist_party_varray(indexCount), ':') into charIndex from dual;
1176 v_stgRecordStore := substr(exist_party_varray(indexCount),0,charIndex -1);
1177 v_stgRecord := substr(exist_party_varray(indexCount),charIndex+1);
1178
1179 insert into ELR_XCN_EMP_STORE_RECORD_STG(serial,store_id,record_type,record_str) values(
1180 v_stg_serial, v_stgRecordStore, 'Party', v_stgRecord);
1181 commit;
1182 END LOOP;
1183
1184 v_stgRecordStore := null;
1185 v_stgRecord := null;
1186
1187 FOR indexCount IN 1 .. exist_employee_store_varray.COUNT
1188 LOOP
1189 v_stg_serial := v_stg_serial + 1;
1190
1191 SELECT INSTR(exist_employee_store_varray(indexCount), ':') into charIndex from dual;
1192 v_stgRecordStore := substr(exist_employee_store_varray(indexCount),0,charIndex -1);
1193 v_stgRecord := substr(exist_employee_store_varray(indexCount),charIndex+1);
1194
1195 insert into ELR_XCN_EMP_STORE_RECORD_STG(serial,store_id,record_type,record_str) values(
1196 v_stg_serial, v_stgRecordStore, 'EmployeeStore', v_stgRecord);
1197 commit;
1198 END LOOP;
1199
1200 v_stgRecordStore := null;
1201 v_stgRecord := null;
1202
1203 FOR indexCount IN 1 .. employee_varray.COUNT
1204 LOOP
1205 v_stg_serial := v_stg_serial + 1;
1206
1207 SELECT INSTR(employee_varray(indexCount), ':') into charIndex from dual;
1208 v_stgRecordStore := substr(employee_varray(indexCount),0,charIndex -1);
1209 v_stgRecord := substr(employee_varray(indexCount),charIndex+1);
1210
1211 insert into ELR_XCN_EMP_STORE_RECORD_STG(serial,store_id,record_type,record_str) values(
1212 v_stg_serial, v_stgRecordStore, 'Employee', v_stgRecord);
1213 commit;
1214 END LOOP;
1215
1216 v_stgRecordStore := null;
1217 v_stgRecord := null;
1218
1219 FOR indexCount IN 1 .. party_varray.COUNT
1220 LOOP
1221 v_stg_serial := v_stg_serial + 1;
1222
1223 SELECT INSTR(party_varray(indexCount), ':') into charIndex from dual;
1224 v_stgRecordStore := substr(party_varray(indexCount),0,charIndex -1);
1225 v_stgRecord := substr(party_varray(indexCount),charIndex+1);
1226
1227 insert into ELR_XCN_EMP_STORE_RECORD_STG(serial,store_id,record_type,record_str) values(
1228 v_stg_serial, v_stgRecordStore, 'Party', v_stgRecord);
1229 commit;
1230 END LOOP;
1231
1232 v_stgRecordStore := null;
1233 v_stgRecord := null;
1234
1235 FOR indexCount IN 1 .. employee_store_varray.COUNT
1236 LOOP
1237 v_stg_serial := v_stg_serial + 1;
1238
1239 SELECT INSTR(employee_store_varray(indexCount), ':') into charIndex from dual;
1240 v_stgRecordStore := substr(employee_store_varray(indexCount),0,charIndex -1);
1241 v_stgRecord := substr(employee_store_varray(indexCount),charIndex+1);
1242
1243 insert into ELR_XCN_EMP_STORE_RECORD_STG(serial,store_id,record_type,record_str) values(
1244 v_stg_serial, v_stgRecordStore, 'EmployeeStore', v_stgRecord);
1245 commit;
1246 END LOOP;
1247
1248 v_stgRecordStore := null;
1249 v_stgRecord := null;
1250
1251 FOR indexCount IN 1 .. employee_password_varray.COUNT
1252 LOOP
1253 v_stg_serial := v_stg_serial + 1;
1254
1255 SELECT INSTR(employee_password_varray(indexCount), ':') into charIndex from dual;
1256 v_stgRecordStore := substr(employee_password_varray(indexCount),0,charIndex -1);
1257 v_stgRecord := substr(employee_password_varray(indexCount),charIndex+1);
1258
1259 insert into ELR_XCN_EMP_STORE_RECORD_STG(serial,store_id,record_type,record_str) values(
1260 v_stg_serial, v_stgRecordStore, 'Password', v_stgRecord);
1261 commit;
1262 END LOOP;
1263
1264 v_stgRecordStore := null;
1265 v_stgRecord := null;
1266
1267 FOR indexCount IN 1 .. employee_answers_varray.COUNT
1268 LOOP
1269 v_stg_serial := v_stg_serial + 1;
1270
1271 SELECT INSTR(employee_answers_varray(indexCount), ':') into charIndex from dual;
1272 v_stgRecordStore := substr(employee_answers_varray(indexCount),0,charIndex -1);
1273 v_stgRecord := substr(employee_answers_varray(indexCount),charIndex+1);
1274
1275 insert into ELR_XCN_EMP_STORE_RECORD_STG(serial,store_id,record_type,record_str) values(
1276 v_stg_serial, v_stgRecordStore, 'Answers', v_stgRecord);
1277 commit;
1278 END LOOP;
1279
1280 v_stgRecordStore := null;
1281 v_stgRecord := null;
1282
1283 FOR indexCount IN 1 .. employee_delete_store_varray.COUNT
1284 LOOP
1285 v_stg_serial := v_stg_serial + 1;
1286
1287 SELECT INSTR(employee_delete_store_varray(indexCount), ':') into charIndex from dual;
1288 v_stgRecordStore := substr(employee_delete_store_varray(indexCount),0,charIndex -1);
1289 v_stgRecord := substr(employee_delete_store_varray(indexCount),charIndex+1);
1290
1291 insert into ELR_XCN_EMP_STORE_RECORD_STG(serial,store_id,record_type,record_str) values(
1292 v_stg_serial, v_stgRecordStore, 'EmployeeStore', v_stgRecord);
1293 commit;
1294 END LOOP;
1295
1296 commit;
1297
1298 END LOOP; -- cur_store
1299
1300 ---------------------------------------Create mnt file and trailer file-----------------------------------------------------
1301el_intstspk.status_rec.job_log := el_intstspk.status_rec.job_log || 'Generating mnt files: ' || CHR(10);
1302
1303 FOR stores IN cur_distinct_stores
1304 LOOP
1305 BEGIN
1306 isEmployeeFileCreated := false;
1307 isEmployeeStoreFileCreated := false;
1308 isPartyFileCreated := false;
1309 isPasswordFileCreated := false;
1310 isAnswersFileCreated := false;
1311 v_empRecordsCount := 0;
1312 v_empStoreRecordsCount := 0;
1313 v_partyRecrodsCount := 0;
1314 v_empPwdRecordsCount := 0;
1315 v_empAnsRecordsCount := 0;
1316
1317 if stores.store_id is null then
1318 continue;
1319 end if;
1320
1321 for storeRecord IN cur_store_records(trim(stores.store_id))
1322 Loop
1323 BEGIN
1324
1325 if storeRecord.record_type = 'Employee' then
1326 if isEmployeeFileCreated = false then
1327 isEmployeeFileCreated := true;
1328
1329 recordsCount(storeRecord.store_id, 'Employee', v_empRecordsCount);
1330
1331 vc_emp_filename := 'EMPLOYEE_' || storeRecord.store_id || '_' || to_char(systimestamp,'YYYYMMDDHH24MISSSSFF') || '.mnt';
1332
1333 -- creating employee trl file
1334 vc_emp_trl_file := 'EMPLOYEE_' || storeRecord.store_id || '_' || to_char(systimestamp,'YYYYMMDDHH24MISSSSFF') || '.mnt.trl';
1335 file_handler_emp_trl := UTL_FILE.FOPEN (vc_file_directory, vc_emp_trl_file, 'W');
1336 vc_file_header := vc_emp_filename || ' ' || v_empRecordsCount;
1337 UTL_FILE.PUT_LINE (file_handler_emp_trl, vc_file_header);
1338
1339 UTL_FILE.FCLOSE(file_handler_emp_trl);
1340
1341 -- create mnt
1342 file_handler_emp := UTL_FILE.FOPEN (vc_file_directory, vc_emp_filename, 'W');
1343 vc_file_header := '<Header line_count="'|| TO_CHAR(v_empRecordsCount) || '" application_date="' ||TO_CHAR(SYSDATE,'YYYY-MM-DD')|| '" target_org_node="STORE:'|| storeRecord.store_id || '" deployment_name="'|| vc_emp_filename || '" download_time="IMMEDIATE" apply_immediately="true"/>';
1344 UTL_FILE.PUT_LINE (file_handler_emp, vc_file_header);
1345
1346 -- write record.
1347 UTL_FILE.PUT_LINE (file_handler_emp, storeRecord.record_str);
1348 elsif isEmployeeFileCreated = true then
1349 -- write record.
1350 UTL_FILE.PUT_LINE (file_handler_emp, storeRecord.record_str);
1351 end if;
1352 end if;
1353
1354 if storeRecord.record_type = 'EmployeeStore' then
1355 if isEmployeeStoreFileCreated = false then
1356 isEmployeeStoreFileCreated := true;
1357
1358 recordsCount(storeRecord.store_id, 'EmployeeStore', v_empStoreRecordsCount);
1359
1360 vc_empstore_filename := 'EMPLOYEE_STORE_' || storeRecord.store_id || '_' || to_char(systimestamp,'YYYYMMDDHH24MISSSSFF') || '.mnt';
1361
1362 -- creating employee_store trl file
1363 vc_empstore_trl_file := 'EMPLOYEE_STORE_' || storeRecord.store_id || '_' || to_char(systimestamp,'YYYYMMDDHH24MISSSSFF') || '.mnt.trl';
1364 file_handler_empstore_trl := UTL_FILE.FOPEN (vc_file_directory, vc_empstore_trl_file, 'W');
1365 vc_file_header := vc_empstore_filename || ' ' || v_empStoreRecordsCount;
1366 UTL_FILE.PUT_LINE (file_handler_empstore_trl, vc_file_header);
1367
1368 UTL_FILE.FCLOSE(file_handler_empstore_trl);
1369
1370 -- create mnt
1371 file_handler_empstore := UTL_FILE.FOPEN (vc_file_directory, vc_empstore_filename, 'W');
1372 vc_file_header := '<Header line_count="' || TO_CHAR(v_empStoreRecordsCount) || '" application_date="' || TO_CHAR(SYSDATE,'YYYY-MM-DD') ||'" target_org_node="STORE:'|| storeRecord.store_id || '" deployment_name="' || vc_empstore_filename || '" download_time="IMMEDIATE" apply_immediately="true"/>';
1373 UTL_FILE.PUT_LINE (file_handler_empstore, vc_file_header);
1374
1375 -- write record.
1376 UTL_FILE.PUT_LINE (file_handler_empstore, storeRecord.record_str);
1377 elsif isEmployeeStoreFileCreated = true then
1378 -- write record.
1379 UTL_FILE.PUT_LINE (file_handler_empstore, storeRecord.record_str);
1380 end if;
1381 end if;
1382
1383 if storeRecord.record_type = 'Party' then
1384 if isPartyFileCreated = false then
1385 isPartyFileCreated := true;
1386
1387 recordsCount(storeRecord.store_id, 'Party', v_partyRecrodsCount);
1388
1389 vc_party_filename := 'PARTY_' || storeRecord.store_id || '_' || to_char(systimestamp,'YYYYMMDDHH24MISSSSFF') || '.mnt';
1390
1391 -- creating party trl file
1392 vc_party_trl_file := 'PARTY_' || storeRecord.store_id || '_' || to_char(systimestamp,'YYYYMMDDHH24MISSSSFF') || '.mnt.trl';
1393 file_handler_party_trl := UTL_FILE.FOPEN (vc_file_directory, vc_party_trl_file, 'W');
1394 vc_file_header := vc_party_filename || ' ' || v_partyRecrodsCount;
1395 UTL_FILE.PUT_LINE (file_handler_party_trl, vc_file_header);
1396
1397 UTL_FILE.FCLOSE(file_handler_party_trl);
1398
1399 -- create mnt
1400 file_handler_party := UTL_FILE.FOPEN (vc_file_directory, vc_party_filename, 'W');
1401 vc_file_header := '<Header line_count="' || TO_CHAR(v_partyRecrodsCount) || '" application_date="' || TO_CHAR(SYSDATE,'YYYY-MM-DD') || '" target_org_node="STORE:' || storeRecord.store_id || '" deployment_name="' || vc_party_filename || '" download_time="IMMEDIATE" apply_immediately="true"/>';
1402 UTL_FILE.PUT_LINE (file_handler_party, vc_file_header);
1403
1404 -- write record.
1405 UTL_FILE.PUT_LINE (file_handler_party, storeRecord.record_str);
1406 elsif isPartyFileCreated = true then
1407 -- write record.
1408 UTL_FILE.PUT_LINE (file_handler_party, storeRecord.record_str);
1409 end if;
1410 end if;
1411
1412 if storeRecord.record_type ='Password' then
1413 if isPasswordFileCreated = false then
1414 isPasswordFileCreated := true;
1415
1416 recordsCount(storeRecord.store_id, 'Password', v_empPwdRecordsCount);
1417
1418 vc_emp_password_filename := 'EMPLOYEE_PASSWORD_' || storeRecord.store_id || '_' || to_char(systimestamp,'YYYYMMDDHH24MISSSSFF') || '.mnt';
1419
1420 -- creating employee_password_trl file
1421 vc_pwd_trl_file := 'EMPLOYEE_PASSWORD_' || storeRecord.store_id || '_' || to_char(systimestamp,'YYYYMMDDHH24MISSSSFF') || '.mnt.trl';
1422 file_handler_pwd_trl := UTL_FILE.FOPEN (vc_file_directory, vc_pwd_trl_file, 'W');
1423 vc_file_header := vc_emp_password_filename || ' ' || v_empPwdRecordsCount;
1424 UTL_FILE.PUT_LINE (file_handler_pwd_trl, vc_file_header);
1425
1426 UTL_FILE.FCLOSE(file_handler_pwd_trl);
1427
1428 file_handler_emp_password := UTL_FILE.FOPEN (vc_file_directory, vc_emp_password_filename, 'W');
1429 vc_file_header := '<Header line_count="' || TO_CHAR(v_empPwdRecordsCount) || '" application_date="' || TO_CHAR(SYSDATE,'YYYY-MM-DD') ||'" target_org_node="STORE:'|| storeRecord.store_id || '" deployment_name="' || vc_emp_password_filename || '" download_time="IMMEDIATE" apply_immediately="true"/>';
1430 UTL_FILE.PUT_LINE (file_handler_emp_password, vc_file_header);
1431
1432 -- write record.
1433 UTL_FILE.PUT_LINE (file_handler_emp_password, storeRecord.record_str);
1434 elsif isPasswordFileCreated = true then
1435 -- write record.
1436 UTL_FILE.PUT_LINE (file_handler_emp_password, storeRecord.record_str);
1437 end if;
1438 end if;
1439
1440 if storeRecord.record_type ='Answers' then
1441 if isAnswersFileCreated = false then
1442 isAnswersFileCreated := true;
1443
1444 recordsCount(storeRecord.store_id, 'Answers', v_empAnsRecordsCount);
1445
1446 vc_emp_answers_filename := 'EMPLOYEE_ANSWERS_' || storeRecord.store_id || '_' || to_char(systimestamp,'YYYYMMDDHH24MISSSSFF') || '.mnt';
1447
1448 -- creating employee_answers_trl file
1449 vc_ans_trl_file := 'EMPLOYEE_ANSWERS_' || storeRecord.store_id || '_' || v_mnt_couunter || '_' || to_char(systimestamp,'YYYYMMDDHH24MISSSSFF') || '.mnt.trl';
1450 file_handler_ans_trl := UTL_FILE.FOPEN (vc_file_directory, vc_ans_trl_file, 'W');
1451 vc_file_header := vc_emp_answers_filename || ' ' || v_empAnsRecordsCount;
1452 UTL_FILE.PUT_LINE (file_handler_ans_trl, vc_file_header);
1453
1454 UTL_FILE.FCLOSE(file_handler_ans_trl);
1455
1456 file_handler_emp_answers := UTL_FILE.FOPEN (vc_file_directory, vc_emp_answers_filename, 'W');
1457 vc_file_header := '<Header line_count="' || TO_CHAR(v_empAnsRecordsCount) || '" application_date="' || TO_CHAR(SYSDATE,'YYYY-MM-DD') ||'" target_org_node="STORE:'|| storeRecord.store_id || '" deployment_name="' || vc_emp_password_filename || '" download_time="IMMEDIATE" apply_immediately="true"/>';
1458 UTL_FILE.PUT_LINE (file_handler_emp_answers, vc_file_header);
1459
1460 -- write record.
1461 UTL_FILE.PUT_LINE (file_handler_emp_answers, storeRecord.record_str);
1462 elsif isAnswersFileCreated = true then
1463 -- write record.
1464 UTL_FILE.PUT_LINE (file_handler_emp_answers, storeRecord.record_str);
1465 end if;
1466 end if;
1467
1468 END;
1469 END LOOP;
1470
1471IF UTl_FILE.IS_OPEN(file_handler_emp) = TRUE
1472 THEN
1473 UTL_FILE.FCLOSE(file_handler_emp);
1474END IF;
1475IF UTl_FILE.IS_OPEN(file_handler_empstore) = TRUE
1476 THEN
1477 UTL_FILE.FCLOSE(file_handler_empstore);
1478END IF;
1479IF UTl_FILE.IS_OPEN(file_handler_party) = TRUE
1480 THEN
1481 UTL_FILE.FCLOSE(file_handler_party);
1482END IF;
1483IF UTl_FILE.IS_OPEN(file_handler_emp_password) = TRUE
1484 THEN
1485 UTL_FILE.FCLOSE(file_handler_emp_password);
1486END IF;
1487IF UTl_FILE.IS_OPEN(file_handler_emp_answers) = TRUE
1488 THEN
1489 UTL_FILE.FCLOSE(file_handler_emp_answers);
1490END IF;
1491
1492-- Close open trailer files.
1493 IF UTl_FILE.IS_OPEN(file_handler_emp_trl) = TRUE
1494 THEN
1495 UTL_FILE.FCLOSE(file_handler_emp_trl);
1496 END IF;
1497 IF UTl_FILE.IS_OPEN(file_handler_empstore_trl) = TRUE
1498 THEN
1499 UTL_FILE.FCLOSE(file_handler_empstore_trl);
1500 END IF;
1501 IF UTl_FILE.IS_OPEN(file_handler_party_trl) = TRUE
1502 THEN
1503 UTL_FILE.FCLOSE(file_handler_party_trl);
1504 END IF;
1505 IF UTl_FILE.IS_OPEN(file_handler_pwd_trl) = TRUE
1506 THEN
1507 UTL_FILE.FCLOSE(file_handler_pwd_trl);
1508 END IF;
1509 IF UTl_FILE.IS_OPEN(file_handler_ans_trl) = TRUE
1510 THEN
1511 UTL_FILE.FCLOSE(file_handler_ans_trl);
1512 END IF;
1513
1514 END;
1515 end loop;
1516 commit;
1517
1518el_intstspk.status_rec.job_log := el_intstspk.status_rec.job_log || 'All mnts have been generated for records: ' || CHR(10);
1519
1520 -- update the staging table processed date to SYSDATE
1521 DECLARE
1522 vc_stg_table VARCHAR2(50);
1523 BEGIN
1524 vc_stg_table := ' elr_xcn_emp_assgn_staging_log';
1525 UPDATE elr_xcn_emp_assgn_staging_log
1526 SET processed_date = SYSDATE
1527 WHERE processed_date IS NULL ;
1528 VC_STG_TABLE := 'ELR_XCN_EMP_STAGING_LOG';
1529 UPDATE ELR_XCN_EMP_STAGING_LOG
1530 SET processed_date = SYSDATE
1531 WHERE processed_date IS NULL;
1532 COMMIT;
1533 END;
1534 END IF;
1535
1536 -- log the statistics
1537 IF vi_num_iz_processed > 0 THEN
1538 el_intstspk.status_rec.job_log := el_intstspk.status_rec.job_log || 'Total # of IZs processed: ' || TO_CHAR(vi_num_iz_processed) || ' @ ' || TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS') || CHR(10);
1539 IF (vi_iz_fail_count > 0) THEN
1540 el_intstspk.status_rec.job_log := el_intstspk.status_rec.job_log || '# of IZs failed processing: ' || TO_CHAR(vi_iz_fail_count) || ' @ ' || TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS') || CHR(10);
1541 END IF;
1542 el_intstspk.status_rec.job_log := el_intstspk.status_rec.job_log || 'Total # of Stores processed: ' || TO_CHAR(vi_store_count) || ' @ ' || TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS') || CHR(10);
1543 el_intstspk.status_rec.job_log := el_intstspk.status_rec.job_log || '# of Stores for which MNT files are created: ' || TO_CHAR(vi_mnt_store_count) || ' @ ' || TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS') || CHR(10);
1544 IF (vi_store_fail_count > 0) THEN
1545 el_intstspk.status_rec.job_log := el_intstspk.status_rec.job_log || '# of Stores failed processing: ' || TO_CHAR(vi_store_fail_count) || ' @ ' || TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS') || CHR(10);
1546 END IF;
1547 IF (vi_iz_fail_count > 0 OR vi_store_fail_count > 0) THEN
1548 el_intstspk.process_warning(c_oracle, 'Process completed with errors.' || CHR(10));
1549 END IF;
1550 ELSE
1551 el_intstspk.status_rec.job_log := el_intstspk.status_rec.job_log || 'There are NO EMPLOYEE records to process @ ' || TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS') || CHR(10);
1552 END IF;
1553 el_intstspk.process_completed;
1554 EXCEPTION
1555 WHEN OTHERS THEN
1556 IF v_err_type IS NULL THEN
1557 v_err_type := c_oracle;
1558 vc_job_log := 'Error: ';
1559 END IF;
1560 -- Close open mnt files.
1561 IF UTl_FILE.IS_OPEN(file_handler_emp) = TRUE
1562 THEN
1563 UTL_FILE.FCLOSE(file_handler_emp);
1564 END IF;
1565 IF UTl_FILE.IS_OPEN(file_handler_empstore) = TRUE
1566 THEN
1567 UTL_FILE.FCLOSE(file_handler_empstore);
1568 END IF;
1569 IF UTl_FILE.IS_OPEN(file_handler_party) = TRUE
1570 THEN
1571 UTL_FILE.FCLOSE(file_handler_party);
1572 END IF;
1573 IF UTl_FILE.IS_OPEN(file_handler_emp_password) = TRUE
1574 THEN
1575 UTL_FILE.FCLOSE(file_handler_emp_password);
1576 END IF;
1577 IF UTl_FILE.IS_OPEN(file_handler_emp_answers) = TRUE
1578 THEN
1579 UTL_FILE.FCLOSE(file_handler_emp_answers);
1580 END IF;
1581
1582 -- Close open trailer files.
1583 IF UTl_FILE.IS_OPEN(file_handler_emp_trl) = TRUE
1584 THEN
1585 UTL_FILE.FCLOSE(file_handler_emp_trl);
1586 END IF;
1587 IF UTl_FILE.IS_OPEN(file_handler_empstore_trl) = TRUE
1588 THEN
1589 UTL_FILE.FCLOSE(file_handler_empstore_trl);
1590 END IF;
1591 IF UTl_FILE.IS_OPEN(file_handler_party_trl) = TRUE
1592 THEN
1593 UTL_FILE.FCLOSE(file_handler_party_trl);
1594 END IF;
1595 IF UTl_FILE.IS_OPEN(file_handler_pwd_trl) = TRUE
1596 THEN
1597 UTL_FILE.FCLOSE(file_handler_pwd_trl);
1598 END IF;
1599 IF UTl_FILE.IS_OPEN(file_handler_ans_trl) = TRUE
1600 THEN
1601 UTL_FILE.FCLOSE(file_handler_ans_trl);
1602 END IF;
1603
1604 vc_job_log := 'Procedure is failed with error: ';
1605 el_intstspk.process_error(v_err_type, vc_job_log);
1606
1607 RAISE;
1608 END;