Skip to content

Instantly share code, notes, and snippets.

@akkida746

akkida746/PLSQl XML parsing

Created March 20, 2018 00:00
Show Gist options
  • Save akkida746/50f5450192c76fc3840d7c9b77cfd11b to your computer and use it in GitHub Desktop.
Save akkida746/50f5450192c76fc3840d7c9b77cfd11b to your computer and use it in GitHub Desktop.
Download ZIP
PLSQL XML parsing and staging data into tables
Raw
PLSQl XML parsing
create or replace
PROCEDURE ELR_XCN_EMP_IMPORT
AS
-- constants declaration
c_interface_name CONSTANT integrator.tbl_interface_inbound.iinb_object_type%TYPE := 'PersonnelEMEA';
c_process_name CONSTANT el_intstpee.process_name%TYPE := 'ELR_XCN_EMP_IMPORT';
-- constants for error type
c_middleware CONSTANT el_intstsee.job_err_type%TYPE := 'MIDDLEWARE';
c_setup CONSTANT el_intstsee.job_err_type%TYPE := 'SETUP';
c_oracle CONSTANT el_intstsee.job_err_type%TYPE := 'ORACLE';
c_trailer_xtn CONSTANT NVARCHAR2(10) := '.trl';
-- variables declaration
isHeaderCreatedForEmpExists BOOLEAN := FALSE;
isHeaderCreatedForEmpNotExists BOOLEAN := FALSE;
isTrlrCreatedForEmpExists BOOLEAN := FALSE;
isTrlrCreatedForEmpNotExists BOOLEAN := FALSE;
vc_error_file NVARCHAR2(100);
vc_trailer_file NVARCHAR2(100);
v_empExistStore NVARCHAR2(100);
v_empPasswordDeleteStore NVARCHAR2(100);
vc_emp_trl_file NVARCHAR2(100);
vc_empstore_trl_file NVARCHAR2(100);
vc_party_trl_file NVARCHAR2(100);
vc_pwd_trl_file NVARCHAR2(100);
vc_ans_trl_file NVARCHAR2(100);
exist_employee_varray dbms_sql.varchar2_table;
exist_party_varray dbms_sql.varchar2_table;
exist_employee_store_varray dbms_sql.varchar2_table;
employee_varray dbms_sql.varchar2_table;
l_text_table dbms_sql.varchar2_table;
party_varray dbms_sql.varchar2_table;
employee_store_varray dbms_sql.varchar2_table;
employee_password_varray dbms_sql.varchar2_table;
employee_delete_store_varray dbms_sql.varchar2_table;
employee_answers_varray dbms_sql.varchar2_table;
v_stg_serial PLS_INTEGER := 0;
v_stgRecordStore NVARCHAR2(100);
v_stgRecord NVARCHAR2(4000);
v_mnt_serial PLS_INTEGER := 1;
v_storeId NVARCHAR2(100);
v_mntType NVARCHAR2(100);
v_recordsCount PLS_INTEGER := 0;
v_mntType_recordsMap dbms_sql.varchar2_table;
v_mntRecordsArray dbms_sql.varchar2_table;
v_newMntRecordsArray dbms_sql.varchar2_table;
grp_membership_binary_value ELR_XCN_EMP_GROUP_MEMBERSHIP.group_id%TYPE;
vc_rec_set_id integrator.tbl_interface_inbound.iinb_iz_record_set_id%TYPE;
vc_file_directory NVARCHAR2(500);
vc_emp_filename NVARCHAR2(500);
vc_exist_emp_filename NVARCHAR2(500);
vc_file_header NVARCHAR2(1000);
vc_file_header_delete_store NVARCHAR2(1000);
vc_party_filename NVARCHAR2(500);
vc_exist_party_filename NVARCHAR2(500);
vc_empstore_filename NVARCHAR2(500);
vc_exist_empstore_filename NVARCHAR2(500);
vc_emp_password_filename NVARCHAR2(500);
vc_emp_answers_filename NVARCHAR2(500);
v_mnt_couunter PLS_INTEGER := 1;
isEmployeeFileCreated boolean := false;
isEmployeeStoreFileCreated boolean := false;
isPartyFileCreated boolean := false;
isPasswordFileCreated boolean := false;
isAnswersFileCreated boolean := false;
v_empStoreRecordsCount PLS_INTEGER := 0;
v_partyRecrodsCount PLS_INTEGER := 0;
v_empRecordsCount PLS_INTEGER := 0;
v_empPwdRecordsCount PLS_INTEGER := 0;
v_empAnsRecordsCount PLS_INTEGER := 0;
vc_file_line NVARCHAR2(4000);
vc_emp_cust_aff_filename NVARCHAR2(500);
vc_file_line_employee NVARCHAR2(4000);
vc_emp_prev_line NVARCHAR2(4000);
vc_party_prev_line NVARCHAR2(4000);
vc_error_code_out NVARCHAR2(200);
vc_error_text_out NVARCHAR2(4000);
vc_error_level_out NVARCHAR2(200);
vc_write_file NVARCHAR2(25);
vi_emp_index PLS_INTEGER := 0;
vi_assignment_index PLS_INTEGER := 0;
vi_iz_fail_count PLS_INTEGER := 0;
vi_store_count PLS_INTEGER := 0;
vi_mnt_store_count PLS_INTEGER := 0;
vi_store_fail_count PLS_INTEGER := 0;
vi_num_iz_processed PLS_INTEGER := 0;
--variables for write mnt for existing employee in case Action "Available_change_loc"
vi_exists_employee_store PLS_INTEGER :=0;
vi_exists_xcenter_employee PLS_INTEGER :=0;
vi_current_employee PLS_INTEGER:=0;
vi_current_emp_action_code NVARCHAR2(100);
--variable use for check current employeee retail_loc_id is null or not
retail_loc_id_flag BOOLEAN :=FALSE;
--variable for check emp_record_already exists with same employee id and retail_loc_id
emp_record_already_exists BOOLEAN := FALSE;
-- flag for generating mnt file for delete hrs_employee_password record
emp_password_flag BOOLEAN :=FALSE;
--flag for delete all store assignment in case "Available_terminate"
emp_delete_store_flag BOOLEAN :=FALSE;
-- variable use in remove all store assignment
charIndex PLS_INTEGER :=-1;
v_subStr NVARCHAR2(1000);
store_delete_count PLS_INTEGER :=0;
--variable for parsing
v_parser xmlparser.parser;
v_document xmldom.domdocument;
v_nodelist xmldom.domnodelist;
v_node xmldom.domnode;
v_asgn_nodelist xmldom.domnodelist;
v_asgn_node xmldom.domnode;
v_assingment_nodelist xmldom.domnodelist;
-- file handeler for mnt
file_handler_emp UTL_FILE.FILE_TYPE;
file_handler_party UTL_FILE.FILE_TYPE;
file_handler_empstore UTL_FILE.FILE_TYPE;
file_handler_empstore_header UTL_FILE.FILE_TYPE;
file_handler_emp_trl UTL_FILE.FILE_TYPE;
file_handler_party_trl UTL_FILE.FILE_TYPE;
file_handler_empstore_trl UTL_FILE.FILE_TYPE;
file_handler_pwd_trl UTL_FILE.FILE_TYPE;
file_handler_ans_trl UTL_FILE.FILE_TYPE;
file_handler_emp_password UTL_FILE.FILE_TYPE;
file_handler_emp_answers UTL_FILE.FILE_TYPE;
file_handler_emp_exists UTL_FILE.FILE_TYPE;
file_handler_party_exists UTL_FILE.FILE_TYPE;
file_handler_empstore_exists UTL_FILE.FILE_TYPE;
v_employee_id elr_xcn_emp_staging_log.employee_id%TYPE;
v_action_code elr_xcn_emp_staging_log.action_code%TYPE;
v_passwordMntActionCode elr_xcn_emp_staging_log.action_code%TYPE;
v_employee_status elr_xcn_emp_staging_log.employee_status%TYPE;
v_first_name elr_xcn_emp_staging_log.first_name%TYPE;
v_last_name elr_xcn_emp_staging_log.last_name%TYPE;
v_middle_name elr_xcn_emp_staging_log.middle_name%TYPE;
v_hire_date elr_xcn_emp_staging_log.hire_date%TYPE;
v_original_hire_date elr_xcn_emp_staging_log.original_hire_date%TYPE;
v_termination_date elr_xcn_emp_staging_log.termination_date%TYPE;
v_market_code elr_xcn_emp_staging_log.market_code%TYPE;
v_job_title elr_xcn_emp_assgn_staging_log.job_title%TYPE;
v_primary_group_id elr_xcn_emp_assgn_staging_log.primary_group_id%TYPE;
v_division_code elr_xcn_emp_assgn_staging_log.division_code%TYPE;
v_retail_loc_id elr_xcn_emp_assgn_staging_log.retail_loc_id%TYPE;
v_assignment_date elr_xcn_emp_assgn_staging_log.assignment_date%TYPE;
v_preferred_locale elr_xcn_emp_staging_log.preferred_locale%TYPE;
v_record_type elr_xcn_emp_staging_log.record_type%TYPE;
-- variables for logging
v_integration_id el_intstpee.integration_id%TYPE;
v_job_id el_intstpee.job_id%TYPE;
v_job_sequence el_intstsee.step_sequence%TYPE := 0;
v_err_type el_intstsee.job_err_type%TYPE;
v_iz_created_timestamp elr_xcn_emp_staging_log.iz_created_timestamp%TYPE;
vc_job_log NVARCHAR2(1000);
vc_locale NVARCHAR2(100);
emp_status NVARCHAR2(100);
-- table 'hrs_employee_store' type for storing value ,fetching from xcenter table 'hrs_employee_store'
xcenter_process_emp dtv.hrs_employee_store%rowtype;
xcenter_rtl_loc_id dtv.hrs_employee_store%rowtype;
vb_start_ind BOOLEAN;
vb_first BOOLEAN;
vb_file_close BOOLEAN := TRUE;
e_iz_error EXCEPTION;
e_next_iz EXCEPTION;
e_next_store EXCEPTION;
--table type for 'elr_xcn_emp_assgn_staging_log'
TYPE tbl_emp_assingment_staging
IS
RECORD
(
employee_id elr_xcn_emp_assgn_staging_log.employee_id%TYPE,
job_title elr_xcn_emp_assgn_staging_log.job_title%TYPE,
primary_group_id elr_xcn_emp_assgn_staging_log.primary_group_id%TYPE,
division_code elr_xcn_emp_assgn_staging_log.division_code%TYPE,
retail_loc_id elr_xcn_emp_assgn_staging_log.retail_loc_id%TYPE,
assignment_date elr_xcn_emp_assgn_staging_log.assignment_date%TYPE,
group_membership elr_xcn_emp_assgn_staging_log.group_membership%TYPE );
--table type for 'elr_xcn_emp_staging_log'
TYPE tbl_pos_emp_staging
IS
RECORD
(
iinb_iz_id integrator.tbl_interface_inbound.iinb_iz_id%TYPE,
employee_id elr_xcn_emp_staging_log.employee_id%TYPE,
action_code elr_xcn_emp_staging_log.action_code%TYPE,
employee_status elr_xcn_emp_staging_log.employee_status%TYPE,
first_name elr_xcn_emp_staging_log.first_name%TYPE,
last_name elr_xcn_emp_staging_log.last_name%TYPE,
middle_name elr_xcn_emp_staging_log.middle_name%TYPE,
hire_date elr_xcn_emp_staging_log.hire_date%TYPE,
original_hire_date elr_xcn_emp_staging_log.original_hire_date%TYPE,
market_code elr_xcn_emp_staging_log.market_code%TYPE,
termination_date elr_xcn_emp_staging_log.termination_date%TYPE,
preferred_locale elr_xcn_emp_staging_log.preferred_locale%TYPE,
record_type elr_xcn_emp_staging_log.record_type%TYPE);
--type elr_xcn_emp_staging_log
TYPE typ_tbl_pos_emp_staging
IS
TABLE OF tbl_pos_emp_staging INDEX BY BINARY_INTEGER;
vtyp_tbl_pos_emp_staging typ_tbl_pos_emp_staging;
-- type elr_xcn_emp_assgn_staging_log
TYPE typ_tbl_assignment_staging
IS
TABLE OF elr_xcn_emp_assgn_staging_log%ROWTYPE;
vtyp_tbl_assingment_staging typ_tbl_assignment_staging := typ_tbl_assignment_staging();
--cursor to fetch employee status from 'elr_xcn_emp_staging_log' table
CURSOR cur_employee_status(emp_status elr_xcn_emp_staging_log.employee_status%TYPE)
IS
SELECT DECODE(TRIM(emp_status), 'Active', 'A', 'Terminated', 'T', 'I') status
FROM DUAL;
--cursor to fetch Action type from 'elr_xcn_emp_staging_log' table
CURSOR cur_action_type(action_type elr_xcn_emp_staging_log.action_code%TYPE)
IS
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
FROM DUAL;
-- main cursor to fetch AVAILABLE IZs from inbound table
CURSOR cur_iz_id
IS
SELECT iinb_iz_id
FROM integrator.tbl_interface_inbound
WHERE iinb_object_type = c_interface_name
AND iinb_iz_record_status = 'AVAILABLE'
AND iinb_iz_record_set_id IS NULL
AND iinb_batch_control_record_type IS NULL
ORDER BY iinb_iz_id;
-- cursor to fetch store 'elr_xcn_emp_assgn_staging_log' table
CURSOR cur_store
IS
SELECT distinct retail_loc_id
FROM elr_xcn_emp_assgn_staging_log
WHERE processed_date IS NULL
AND retail_loc_id IS NOT NULL;
-- cursor to fetch employee password records when employee_status Terminated
CURSOR cur_emp_password(p_employee_id IN elr_xcn_emp_staging_log.employee_id%TYPE)
IS
SELECT * FROM dtv.hrs_employee_password where employee_id=p_employee_id and CURRENT_PASSWORD_FLAG=1;
-- cursor to fetch employee security answers records when employee_status Terminated
CURSOR cur_emp_answers(p_employee_id IN elr_xcn_emp_staging_log.employee_id%TYPE)
IS
SELECT ORGANIZATION_ID,employee_id,challenge_code FROM dtv.hrs_employee_answers where employee_id=p_employee_id;
-- cursor to fetch retail_loc id to remove store assignement
CURSOR cur_emp_store_delete(p_employee_id IN elr_xcn_emp_staging_log.employee_id%TYPE)
IS
SELECT rtl_loc_Id FROM dtv.hrs_employee_store where employee_id = p_employee_id and end_date is null;
-- Cursor to fetch distinct stores from staging table.
CURSOR cur_distinct_stores
IS
SELECT distinct store_id FROM ELR_XCN_EMP_STORE_RECORD_STG;
-- Cursor to fetch distinct stores from staging table.
CURSOR cur_store_records(p_store_in IN ELR_XCN_EMP_STORE_RECORD_STG.store_id%TYPE)
IS
SELECT * FROM ELR_XCN_EMP_STORE_RECORD_STG where store_id = p_store_in order by record_type, serial;
-- cursor to fetch employee data from 'elr_xcn_emp_staging_log' and 'elr_xcn_emp_assgn_staging_log' table
CURSOR cur_emp_store(p_store_id_in IN elr_xcn_emp_assgn_staging_log.retail_loc_id%TYPE)
IS
SELECT *
FROM
(SELECT DISTINCT elog.IINB_IZ_ID, elog.employee_id,
elog.hire_date,
elog.original_hire_date,
elog.first_name,
elog.last_name,
elog.middle_name,
elog.preferred_locale,
elog.termination_date,
elog.action_code,
elog.market_code,
elog.employee_status,
elog.record_type,
assign_log.job_title,
assign_log.division_code,
assign_log.assignment_date,
assign_log.retail_loc_id,
assign_log.primary_group_id,
assign_log.group_membership,
DECODE(TRIM(employee_status),'A','TRUE','T','FALSE','FALSE')party_status
FROM elr_xcn_emp_staging_log elog,
elr_xcn_emp_assgn_staging_log assign_log
WHERE assign_log.retail_loc_id = p_store_id_in
AND trim(elog.employee_id) = trim(assign_log.employee_id)
AND assign_log.processed_date IS NULL
AND elog.processed_date IS NULL
AND assign_log.retail_loc_id IS NOT NULL
)
ORDER BY IINB_IZ_ID asc;
curr_interface_rec icc.pkg_interface.curinterface_inbound;
rec_ref_interface integrator.tbl_interface_inbound%ROWTYPE;
-- this internal proc is used to update the IZ status to FAIL or PROCESSED
-- if any error occurs, it will RAISE e_next_iz exception to continue processing with next IZ record
PROCEDURE update_iz_status(
p_status_in IN integrator.interface_inbound.iinb_iz_record_status%TYPE,
p_iinb_iz_id_in IN integrator.interface_inbound.iinb_iz_id%TYPE,
p_iinb_iz_uuid_in IN integrator.interface_inbound.iinb_iz_uuid%TYPE,
p_iinb_iz_record_set_id_in IN integrator.interface_inbound.iinb_iz_record_set_id%TYPE,
p_iinb_object_type_in IN integrator.interface_inbound.iinb_object_type%TYPE)
IS
BEGIN
vc_error_text_out := NULL;
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,
CASE
WHEN p_status_in = 'FAIL' THEN
SQLCODE
ELSE
NULL
END,
CASE
WHEN p_status_in = 'FAIL' THEN
'S'
ELSE
NULL
END,
CASE
WHEN p_status_in = 'FAIL' THEN
SQLERRM
ELSE
NULL
END );
-- increment the count variable accordingly
IF p_status_in = 'FAIL' THEN
vi_iz_fail_count := vi_iz_fail_count + 1;
END IF;
-- if middleware returns error then raise exception
IF vc_error_text_out IS NOT NULL THEN
RAISE e_iz_error;
END IF;
COMMIT;
EXCEPTION
WHEN e_iz_error THEN
v_err_type := c_middleware;
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);
el_intstspk.process_skip_warning (v_err_type, vc_job_log);
RAISE e_next_iz;
WHEN OTHERS THEN
v_err_type := c_oracle;
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);
el_intstspk.process_skip_warning (v_err_type, vc_job_log);
RAISE e_next_iz;
END update_iz_status;
PROCEDURE recordsCount(p_store_in IN ELR_XCN_EMP_STORE_RECORD_STG.store_id%TYPE,
p_recordType_in IN ELR_XCN_EMP_STORE_RECORD_STG.record_type%TYPE,
recordsCount OUT number) IS
begin
recordsCount := 0;
SELECT count(*) into recordsCount FROM ELR_XCN_EMP_STORE_RECORD_STG where store_id = p_store_in
and record_type = p_recordType_in order by record_type;
EXCEPTION
WHEN OTHERS THEN
v_err_type := c_oracle;
vc_job_log := 'Error in procedure recordsCount for store: ' || TO_CHAR(p_store_in) || CHR (10);
el_intstspk.process_skip_warning (v_err_type, vc_job_log);
RAISE;
end recordsCount;
---------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------Main Proc Begins Here-----------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
v_job_sequence := 1;
el_intstspk.status_rec.job_log := '';
BEGIN
SELECT integration_id,
job_id
INTO v_integration_id,
v_job_id
FROM el_intstpee
WHERE process_name = c_process_name;
EXCEPTION
WHEN OTHERS THEN
-- process failed setup error no job defined
el_intstspk.status_rec.step_sequence := v_job_sequence;
el_intstspk.status_rec.integration_id := 0;
el_intstspk.status_rec.job_id := 0;
el_intstspk.create_status(el_intstspk.status_rec);
v_err_type := c_setup;
vc_job_log := 'No job defined in the status table, exiting out'|| CHR(10);
RAISE;
END;
vb_start_ind := el_intstspk.start_job( v_integration_id, v_job_id );
-- previous job failed, do not go ahead
IF NOT vb_start_ind THEN
-- process failed setup error
el_intstspk.status_rec.step_sequence := v_job_sequence;
el_intstspk.create_status(el_intstspk.status_rec);
v_err_type := c_setup;
vc_job_log := 'Previous step failed or job out of sync, exiting out'|| CHR(10);
RAISE_APPLICATION_ERROR(-20012, vc_job_log);
END IF;
-- process started
el_intstspk.process_started;
el_intstspk.status_rec.job_status := 'IN-PROGRESS';
-- get file directory name
BEGIN
SELECT setting
INTO vc_file_directory
FROM el_com_configcd
WHERE application ='ROS_SUB_EMP'
AND element = 'FILEDIR_EMP';
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);
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_err_type := c_setup;
vc_job_log := 'Configuration missing for the FILEDIR_EMP parameter in config table for application ROS_SUB_EMP' || CHR(10);
RAISE;
WHEN OTHERS THEN
v_err_type := c_oracle;
vc_job_log := 'Error while getting FILEDIR_EMP parameter from config table for application ROS_SUB_EMP' || CHR(10);
RAISE;
END;
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);
-- process all AVAILABLE IZs
FOR rec_iz_id IN cur_iz_id
LOOP -- for cur_iz_id
BEGIN
--for flushing array
vtyp_tbl_assingment_staging.DELETE;
vtyp_tbl_pos_emp_staging.DELETE;
vi_num_iz_processed := vi_num_iz_processed + 1;
-- reset the record variable
rec_ref_interface := NULL;
BEGIN
-- generating a sequence value for passing to an inbound function
SELECT el_iz_recordset_id.NEXTVAL
INTO vc_rec_set_id
FROM DUAL;
--Calling procedure get_inbound_document which belongs to ICC Schema and
--is a part of PKG_INTERFACE package with IN and Out variable parameter
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 );
-- check for the error from the middleware
IF (vc_error_text_out IS NOT NULL) THEN
RAISE e_iz_error;
END IF;
EXCEPTION
WHEN e_iz_error THEN
-- write error while calling procedure put_outbound_document to write XML to IZ
v_err_type := c_middleware;
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);
el_intstspk.process_skip_warning(v_err_type, vc_job_log);
RAISE e_next_iz;
WHEN OTHERS THEN
v_err_type := c_oracle;
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);
el_intstspk.process_skip_warning(v_err_type, vc_job_log);
RAISE e_next_iz;
END;
-- logic to parse the XML data
BEGIN
FETCH curr_interface_rec INTO rec_ref_interface;
CLOSE curr_interface_rec;
v_parser := xmlparser.newparser;
IF rec_ref_interface.iinb_application_payload IS NULL THEN
v_err_type := c_middleware;
vc_job_log := 'Payload is blank for IZ ID: ' || TO_CHAR(rec_iz_id.iinb_iz_id) || CHR(10);
el_intstspk.process_skip_warning(v_err_type, vc_job_log);
RAISE e_next_iz;
END IF;
xmlparser.parseclob (v_parser,icc.pkg_integration_common.blob_to_clob (rec_ref_interface.iinb_application_payload));
v_document := xmlparser.getdocument (v_parser);
xmlparser.freeparser (v_parser);
v_nodelist := xslprocessor.selectnodes(xmldom.makenode (v_document),'/PersonnelList/Personnel');
vi_emp_index := 0;
vi_assignment_index:=0;
-- process the XML if nodelist is found
IF xmldom.getlength (v_nodelist) > 0 THEN
-- process looping nodes
FOR i IN 0 .. (xmldom.getlength (v_nodelist)) - 1
LOOP
v_node := xmldom.item (v_nodelist, i);
vi_emp_index := vi_emp_index + 1;
-- these tags will not repeat for Assignment node. Hence, parse only once.
v_employee_id := trim(xslprocessor.valueof (v_node, 'EmployeeID'));
v_action_code := xslprocessor.valueof (v_node, '@Action');
v_record_type := v_action_code;
v_first_name := xslprocessor.valueof (v_node, 'Name/FirstName');
v_last_name := xslprocessor.valueof (v_node, 'Name/LastName');
v_middle_name := xslprocessor.valueof (v_node, 'Name/MiddleName');
v_hire_date := TO_DATE(xslprocessor.valueof (v_node, 'HiredDate'),'YYYY-MM-DD');
v_original_hire_date := TO_DATE(xslprocessor.valueof (v_node, 'OriginalHiredDate'),'YYYY-MM-DD');
v_termination_date := TO_DATE(xslprocessor.valueof (v_node, 'TerminationDate'),'YYYY-MM-DD');
v_employee_status := xslprocessor.valueof (v_node, 'EmployeeStatus');
v_market_code := xslprocessor.valueof (v_node, 'MarketCode');
-- fetching employee status for employee
FOR emp_status IN cur_employee_status(trim(v_employee_status))
LOOP
BEGIN
v_employee_status:= emp_status.status;
EXCEPTION
WHEN OTHERS THEN
v_err_type := c_oracle;
vc_job_log := 'Error while calling cur_employee_status ' || CHR(10);
el_intstspk.process_warning (v_err_type, vc_job_log);
RAISE e_iz_error;
END;
END LOOP;
-- parsing for employee Assignment if Assignement NodeList found
v_assingment_nodelist :=xslprocessor.selectnodes(v_node,'Assignment');
IF xmldom.getlength (v_assingment_nodelist) > 0 THEN
-- process Assignement NodeList
FOR j IN 0 .. xmldom.getLength(v_assingment_nodelist) - 1
LOOP
v_asgn_node := xmldom.item(v_assingment_nodelist, j);
v_job_title := xslprocessor.valueof (v_asgn_node, 'JobTitle');
v_primary_group_id := xslprocessor.valueof (v_asgn_node, 'PrimaryGroupID');
v_division_code := xslprocessor.valueof (v_asgn_node, 'DivisionCode');
v_retail_loc_id := xslprocessor.valueof (v_asgn_node, 'RetailStoreID');
v_assignment_date := TO_DATE(xslprocessor.valueof (v_asgn_node, 'AssignmentDate'),'YYYY-MM-DD');
vtyp_tbl_assingment_staging.EXTEND();
vi_assignment_index := vtyp_tbl_assingment_staging.COUNT;
vtyp_tbl_assingment_staging(vi_assignment_index).employee_id := trim(v_employee_id);
vtyp_tbl_assingment_staging(vi_assignment_index).job_title := v_job_title;
vtyp_tbl_assingment_staging(vi_assignment_index).primary_group_id := v_primary_group_id;
vtyp_tbl_assingment_staging(vi_assignment_index).division_code := v_division_code;
vtyp_tbl_assingment_staging(vi_assignment_index).retail_loc_id := v_retail_loc_id;
vtyp_tbl_assingment_staging(vi_assignment_index).assignment_date := v_assignment_date;
-- When location changed from store to office.
if v_retail_loc_id is null then
v_employee_status := 'T';
end if;
--for empty assigment fetch retail_loc_id from xcenter 'hrs_employee_store' for update the in employee_store for same employee
IF(v_retail_loc_id IS NULL)THEN
BEGIN
-- if current employee retail_loc_id is null then flag true;
retail_loc_id_flag:=TRUE;
SELECT *
INTO xcenter_rtl_loc_id
FROM DTV.hrs_employee_store
WHERE employee_id = trim(v_employee_id)
AND end_date IS NULL;
vtyp_tbl_assingment_staging(vi_assignment_index).retail_loc_id := xcenter_rtl_loc_id.rtl_loc_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xcenter_rtl_loc_id := NULL;
END;
END IF;
-- fetch group_membership base64 encoding based on primary_group_id
IF(v_primary_group_id IS NOT NULL) AND (v_employee_status !='T') THEN
BEGIN
SELECT base64_encoding
INTO grp_membership_binary_value
FROM ELR_XCN_EMP_GROUP_MEMBERSHIP
WHERE group_id = trim(v_primary_group_id);
END;
ELSE
BEGIN
SELECT base64_encoding
INTO grp_membership_binary_value
FROM ELR_XCN_EMP_GROUP_MEMBERSHIP
WHERE trim(group_id) = 'EVERYONE';
END;
END IF;
-- group membership value in vtyp_tbl_assingment_staging array
vtyp_tbl_assingment_staging(vi_assignment_index).group_membership := grp_membership_binary_value;
END LOOP; -- closed employee Assignment loop
END IF;
-- fetching action code
FOR action_type IN cur_action_type(v_action_code)
LOOP
BEGIN
IF(v_action_code='Available_CHANGE_LOC')THEN
--- change action code INSERT_ONLY if employee does not exist in xcenter same as current employee id
BEGIN
SELECT *
INTO xcenter_process_emp
FROM DTV.hrs_employee_store
WHERE employee_id= trim(v_employee_id)
AND end_date IS NULL;
v_action_code:=action_type.action;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xcenter_process_emp := NULL;
v_action_code:='INSERT_ONLY';
END;
ELSE
v_action_code:= action_type.action;
END IF;
EXCEPTION
WHEN OTHERS THEN
v_err_type := c_oracle;
vc_job_log := 'Error while calling cur_action_type ' || CHR(10);
el_intstspk.process_warning (v_err_type, vc_job_log);
RAISE e_iz_error;
END;
-- fetch preferred locale for country from Market Code when action code is insert only
IF v_market_code IS NOT NULL AND (v_employee_status !='T') THEN
BEGIN
SELECT locale
INTO v_preferred_locale
FROM ELR_EMP_COUNTRY_LOCALE_MAPPING
WHERE COUNTRY = trim(v_market_code);
END;
ELSE
v_preferred_locale:=NULL;
END IF;
END LOOP;-- closed action code
vtyp_tbl_pos_emp_staging(vi_emp_index).employee_id := v_employee_id;
vtyp_tbl_pos_emp_staging(vi_emp_index).action_code := v_action_code;
vtyp_tbl_pos_emp_staging(vi_emp_index).first_name := v_first_name;
vtyp_tbl_pos_emp_staging(vi_emp_index).last_name := v_last_name;
vtyp_tbl_pos_emp_staging(vi_emp_index).middle_name := v_middle_name;
vtyp_tbl_pos_emp_staging(vi_emp_index).hire_date := v_hire_date;
vtyp_tbl_pos_emp_staging(vi_emp_index).original_hire_date := v_original_hire_date;
vtyp_tbl_pos_emp_staging(vi_emp_index).employee_status := v_employee_status;
vtyp_tbl_pos_emp_staging(vi_emp_index).market_code := v_market_code;
vtyp_tbl_pos_emp_staging(vi_emp_index).preferred_locale := v_preferred_locale;
vtyp_tbl_pos_emp_staging(vi_emp_index).termination_date := v_termination_date;
vtyp_tbl_pos_emp_staging(vi_emp_index).record_type := v_record_type;
v_iz_created_timestamp := rec_ref_interface.zzzz_created_timestamp;
END LOOP; -- closed nodelist loop
END IF;
EXCEPTION
WHEN OTHERS THEN
IF v_err_type IS NULL THEN
v_err_type := c_oracle;
vc_job_log := 'Error in procedure while parsing for IZ ID: ' || TO_CHAR(rec_ref_interface.iinb_iz_id) || CHR(10);
el_intstspk.process_skip_warning(v_err_type, vc_job_log);
END IF;
RAISE e_next_iz;
END; -- close logic to parse the XML data
-- logic to insert/update EMP data into the log table from type vtyp_tbl_pos_emp_staging
IF (vi_emp_index > 0) THEN
-- insert in to log table elr_xcn_emp_staging_log
DECLARE
vi_error_count PLS_INTEGER := 0;
e_bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(e_bulk_errors, -24381);
BEGIN
FORALL emp_cntr IN 1..vi_emp_index SAVE EXCEPTIONS
INSERT
INTO elr_xcn_emp_staging_log
(
iinb_iz_id,
employee_id,
employee_status,
action_code,
first_name,
last_name,
middle_name,
preferred_locale,
iz_created_timestamp,
create_date,
hire_date,
original_hire_date,
market_code,
termination_date,
record_type
)
VALUES
(
rec_ref_interface.iinb_iz_id,
vtyp_tbl_pos_emp_staging (emp_cntr).employee_id,
vtyp_tbl_pos_emp_staging(emp_cntr).employee_status,
vtyp_tbl_pos_emp_staging (emp_cntr).action_code,
vtyp_tbl_pos_emp_staging (emp_cntr).first_name,
vtyp_tbl_pos_emp_staging (emp_cntr).last_name,
vtyp_tbl_pos_emp_staging (emp_cntr).middle_name,
TRIM(vtyp_tbl_pos_emp_staging(emp_cntr).preferred_locale), -- Pref Locale
v_iz_created_timestamp,
SYSDATE,
vtyp_tbl_pos_emp_staging(emp_cntr).hire_date,
vtyp_tbl_pos_emp_staging(emp_cntr).original_hire_date,
vtyp_tbl_pos_emp_staging(emp_cntr).market_code,
vtyp_tbl_pos_emp_staging(emp_cntr).termination_date,
vtyp_tbl_pos_emp_staging(emp_cntr).record_type
);
EXCEPTION
-- do not propogate the exception, allow to process next record
WHEN e_bulk_errors THEN
vi_error_count := SQL%BULK_EXCEPTIONS.COUNT;
FOR i IN 1 .. vi_error_count
LOOP
v_err_type := c_oracle;
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);
el_intstspk.process_warning(v_err_type, vc_job_log);
END LOOP;
v_err_type := NULL;
vc_job_log := NULL;
RAISE e_next_iz;
END;
-- No data matching for Personnel node
ELSE
v_err_type := c_oracle;
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);
el_intstspk.process_warning(v_err_type, vc_job_log);
RAISE e_next_iz;
END IF; -- closed logic insert into elr_xcn_emp_staging_log
--- inseting in elr_xcn_emp_assgn_staging_log table
IF (vi_assignment_index > 0) THEN
-- insert in to log table elr_xcn_emp_staging_log
DECLARE
vi_error_count PLS_INTEGER := 0;
e_bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(e_bulk_errors, -24381);
BEGIN
FORALL emp_assignment IN 1..vi_assignment_index SAVE EXCEPTIONS
INSERT
INTO elr_xcn_emp_assgn_staging_log
(
iinb_iz_id,
employee_id,
job_title,
primary_group_id,
division_code,
retail_loc_id,
assignment_date,
group_membership,
processed_date
)
VALUES
(
rec_ref_interface.iinb_iz_id,
vtyp_tbl_assingment_staging (emp_assignment).employee_id,
vtyp_tbl_assingment_staging(emp_assignment).job_title,
TRIM(vtyp_tbl_assingment_staging(emp_assignment).primary_group_id),
vtyp_tbl_assingment_staging(emp_assignment).division_code,
vtyp_tbl_assingment_staging(emp_assignment).retail_loc_id,
vtyp_tbl_assingment_staging(emp_assignment).assignment_date,
vtyp_tbl_assingment_staging(emp_assignment).group_membership,
NULL
);
EXCEPTION
-- do not propogate the exception, allow to process next record
WHEN e_bulk_errors THEN
vi_error_count := SQL%BULK_EXCEPTIONS.COUNT;
FOR i IN 1 .. vi_error_count
LOOP
v_err_type := c_oracle;
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);
el_intstspk.process_warning(v_err_type, vc_job_log);
END LOOP;
v_err_type := NULL;
vc_job_log := NULL;
RAISE e_next_iz;
END;
-- No data matching for Personnel node
ELSE
v_err_type := c_oracle;
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);
el_intstspk.process_warning(v_err_type, vc_job_log);
RAISE e_next_iz;
END IF; -- closed logic insert into elr_xcn_emp_assgn_staging_log
-- update IZ status to PROCESSED
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 );
-- commit the changes for current IZ
COMMIT;
-- main exception for cur_iz_id FOR LOOP
EXCEPTION
WHEN e_next_iz THEN
-- set the current IZ status to FAIL in inbound table.
BEGIN
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 );
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
-- reset error variables
v_err_type := NULL;
vc_job_log := NULL;
WHEN OTHERS THEN
-- do not propogate the exception, allow to process next record
IF vc_job_log IS NULL THEN
v_err_type := c_oracle;
vc_job_log := 'Error in procedure while parsing the XML for IZ ID: ' || TO_CHAR(rec_ref_interface.iinb_iz_id) || CHR(10);
el_intstspk.process_skip_warning(v_err_type, vc_job_log);
-- set the current IZ status to FAIL in inbound table.
BEGIN
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 );
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END IF;
--reset error variables
v_err_type := NULL;
vc_job_log := NULL;
END;
END LOOP; -- for cur_iz_id
IF (vi_num_iz_processed > 0) THEN
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);
delete from ELR_XCN_EMP_STORE_RECORD_STG;
commit;
-- start wrting into MNT files for each store
--cursor for fetch store from elr_xcn_emp_assgn_staging_log
FOR rec_store IN cur_store
LOOP
DECLARE
--count for emp record
vi_emp_count PLS_INTEGER := 1;
vi_party_count PLS_INTEGER := 1;
vi_emp_store_count PLS_INTEGER := 1;
vi_emp_password_count PLS_INTEGER := 1;
vi_emp_answers_count PLS_INTEGER := 1;
vi_emp_delete_store_count PLS_INTEGER := 1;
-- count for exist emp record
vi_exist_emp_count PLS_INTEGER := 1;
vi_exist_party_count PLS_INTEGER := 1;
vi_exist_emp_store_count PLS_INTEGER :=1;
BEGIN
vb_first := TRUE;
vb_file_close := TRUE;
vc_emp_prev_line := NULL;
vc_party_prev_line := NULL;
-- increment store count
vi_store_count := vi_store_count + 1;
-- isHeaderCreatedForEmpExists := FALSE;
-- isHeaderCreatedForEmpNotExists := FALSE;
-- isTrlrCreatedForEmpExists := FALSE;
-- isTrlrCreatedForEmpNotExists := FALSE;
exist_employee_varray.delete;
exist_party_varray.delete;
exist_employee_store_varray.delete;
employee_varray.delete;
party_varray.delete;
employee_store_varray.delete;
employee_password_varray.delete;
employee_delete_store_varray.delete;
employee_answers_varray.delete;
-- Writing data into mnt files.
FOR rec_emp_store IN cur_emp_store(trim(rec_store.retail_loc_id))
LOOP
BEGIN
-- Fetch Employee records from Xcenter.
BEGIN
SELECT *
INTO xcenter_process_emp
FROM DTV.hrs_employee_store
WHERE employee_id = trim(rec_emp_store.employee_id)
AND end_date IS NULL
and begin_date is not null;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xcenter_process_emp := NULL;
END;
-- insert retail_loc_id value of existing employee for generating Available_change_loc MNT
vi_exists_employee_store:=xcenter_process_emp.rtl_loc_id;
vi_exists_xcenter_employee:=xcenter_process_emp.employee_id;
vi_current_employee:=rec_emp_store.employee_id;
vi_current_emp_action_code:=rec_emp_store.action_code;
v_empPasswordDeleteStore := null;
-- This is the only condition used for terminating employee with Future date. It just sets employee termination date as
-- future termination date in 'hrs_employee' table only.
if rec_emp_store.record_type = 'Available_TERMINATE' and rec_emp_store.employee_status = 'A' then
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) || '||' ;
vi_exist_emp_count := vi_exist_emp_count+1;
continue;
end if;
-- Updating exising employee information.
if rec_emp_store.record_type = 'Available_CHANGE' and rec_emp_store.employee_status = 'A' then
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) || '||' ;
vi_exist_emp_count := vi_exist_emp_count+1;
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) || '|';
vi_exist_party_count :=vi_exist_party_count +1;
continue;
end if;
BEGIN
-- Check if the same record is already present in Xcenter.
IF(rec_emp_store.action_code='INSERT_ONLY' AND trim(xcenter_process_emp.rtl_loc_id) = trim(rec_emp_store.retail_loc_id)
AND trim(xcenter_process_emp.employee_id) = trim(rec_emp_store.employee_id)) THEN
v_err_type := c_oracle;
vc_job_log := 'Employee'|| TRIM(xcenter_process_emp.employee_id) || 'RECORD ALRADY EXITS IN '|| xcenter_process_emp.rtl_loc_id || 'store '|| CHR(10);
--el_intstspk.process_warning (v_err_type, vc_job_log);
--skip current record and pick next record
CONTINUE
WHEN xcenter_process_emp.employee_id=rec_emp_store.employee_id AND xcenter_process_emp.rtl_loc_id=rec_emp_store.retail_loc_id;
END IF;
END;
-- increment Store count for which MNT file is generated
vi_mnt_store_count := vi_mnt_store_count + 1;
----------------------------------Employee mnt--------------------------------------------------
-- Check if Employee is Active.
IF rec_emp_store.employee_status = 'A' THEN
vc_write_file := 'Employee';
-- Action = Available Change Loc.
IF(rec_emp_store.action_code='UPDATE' AND trim(xcenter_process_emp.rtl_loc_id) != trim(rec_emp_store.retail_loc_id)
AND trim(xcenter_process_emp.employee_id) = trim(rec_emp_store.employee_id))THEN
-- Create record for new store mnt
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) || '||' ;
vi_emp_count :=vi_emp_count + 1;
-- Create record for old Xcenter store mnt
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) || '||' ;
vi_exist_emp_count := vi_exist_emp_count+1;
ELSE
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) || '||' ;
vi_emp_count :=vi_emp_count+1;
END IF;
END IF;
-- Check if Employee is Terminated.
IF rec_emp_store.employee_status ='T' THEN
vc_write_file := 'Employee';
if rec_emp_store.termination_date is not null then
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 || '||' ;
else
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 || '||' ;
end if;
vi_emp_count :=vi_emp_count+1;
END IF;
----------------------------------Party mnt--------------------------------------------
IF rec_emp_store.employee_status ='A' THEN
vc_write_file := 'Party';
IF(rec_emp_store.action_code = 'UPDATE' AND trim(xcenter_process_emp.rtl_loc_id) != trim(rec_emp_store.retail_loc_id)
AND trim(xcenter_process_emp.employee_id) = trim(rec_emp_store.employee_id))THEN
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) || '|';
vi_party_count :=vi_party_count+1;
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) || '|';
vi_exist_party_count :=vi_exist_party_count +1;
-- Action = Others
ELSE
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) || '|';
vi_party_count :=vi_party_count+1;
END IF;
END IF;
-- Check if Employee is Terminated.
IF rec_emp_store.employee_status ='T' THEN
vc_write_file := 'Party';
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) || '|';
vi_party_count :=vi_party_count+1;
END IF;
-----------------------------------Employee Store mnt---------------------------------------------
IF(rec_emp_store.employee_status = 'A') THEN
-- Available_TERMINATE','Available_CHANGE_LOC','Available_REHIRE','Available_REV_TERMINATE'
IF(rec_emp_store.action_code = 'UPDATE' AND trim(xcenter_process_emp.rtl_loc_id) != trim(rec_emp_store.retail_loc_id)
AND trim(xcenter_process_emp.employee_id) = trim(rec_emp_store.employee_id))THEN
-- writing data into employee store file for Action code, EmployeeId, PreviouStore, NULL, AssignmentEndDate ,and end_date is sysdate
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';
vi_exist_emp_store_count:=vi_exist_emp_store_count+1;
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';
vi_emp_store_count :=vi_emp_store_count+1;
-- Action = Others
ELSE
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';
vi_emp_store_count:=vi_emp_store_count+1;
END IF;
elsif rec_emp_store.employee_status = 'T' then
if rec_emp_store.action_code = 'UPDATE' then
-- fetching all stores from Xcenter stores where employee has assignment.
FOR rec_emp_store_delete IN cur_emp_store_delete(trim(rec_emp_store.employee_id))
LOOP
BEGIN
vc_write_file :='Employee Store';
if rec_emp_store.termination_date is not null then
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 ||'|';
else
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 ||'|';
end if;
-- Deleting all employee assignments from all stores in Xcenter.
vi_emp_delete_store_count := vi_emp_delete_store_count+1;
END ;
END LOOP;
-- fetching all Employee password records from Xcenter stores.
FOR rec_emp_password IN cur_emp_password(trim(rec_emp_store.employee_id))
LOOP
BEGIN
vc_write_file := 'EMPLOYEE_PASSWORD';
--emp_password_flag check use for generating mnt file
v_empPasswordDeleteStore := rec_emp_store.retail_loc_id;
-- writing data into employee_Password mnt file: Action Code,Record Identifier,Employee Id(PK),Password, Sequence (PK),Effective Date,Current Password Flag,Temporary Flag
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);
vi_emp_password_count :=vi_emp_password_count+1;
END;
END LOOP;
-- fetching all Employee security questions answers records from Xcenter stores.
FOR rec_emp_answers IN cur_emp_answers(trim(rec_emp_store.employee_id))
LOOP
BEGIN
vc_write_file := 'EMPLOYEE_ANSWERS';
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;
vi_emp_answers_count :=vi_emp_answers_count+1;
END;
END LOOP;
-- Action = Others, like 'Available_Change' for rev
else
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';
vi_emp_store_count:=vi_emp_store_count+1;
end if;
end if;
------------------Password mnt for 'Available_REHIRE' and 'Available_REV_TERMINATE'-----------------------------
if (rec_emp_store.record_type = 'Available_REHIRE' or rec_emp_store.record_type = 'Available_REV_TERMINATE')then
FOR rec_emp_password IN cur_emp_password(trim(rec_emp_store.employee_id))
LOOP
BEGIN
vc_write_file := 'EMPLOYEE_PASSWORD';
--emp_password_flag check use for generating mnt file
v_empPasswordDeleteStore := rec_emp_store.retail_loc_id;
-- writing data into employee_Password mnt file: Action Code,Record Identifier,Employee Id(PK),Password, Sequence (PK),Effective Date,Current Password Flag,Temporary Flag
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';
vi_emp_password_count :=vi_emp_password_count+1;
END;
END LOOP;
end if;
EXCEPTION
WHEN OTHERS THEN
-- log the error if not already logged
IF v_err_type IS NULL THEN
v_err_type := c_oracle;
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);
el_intstspk.process_skip_warning(v_err_type, vc_job_log);
END IF;
RAISE e_next_store;
END;
END LOOP; -- cur_emp_store
EXCEPTION
WHEN e_next_store THEN
-- increment fail count
vi_store_fail_count := vi_store_fail_count + 1;
-- close the open files and remove them
-- emp file
BEGIN
IF UTl_FILE.IS_OPEN(file_handler_emp) = TRUE THEN
UTL_FILE.FCLOSE(file_handler_emp);
UTL_FILE.FREMOVE(vc_file_directory, vc_emp_filename);
END IF;
EXCEPTION
WHEN OTHERS THEN
v_err_type := c_oracle;
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);
el_intstspk.process_skip_warning(v_err_type, vc_job_log);
END;
v_err_type := NULL;
vc_job_log := NULL;
WHEN OTHERS THEN
-- increment fail count
vi_store_fail_count := vi_store_fail_count + 1;
v_err_type := c_oracle;
vc_job_log := 'Error occured in procedure while generating MNT file for Store #: ' || TRIM(v_retail_loc_id) || '. Skipping this record.' || CHR(10);
el_intstspk.process_skip_warning(v_err_type, vc_job_log);
-- reset error variables
v_err_type := NULL;
vc_job_log := NULL;
END;
--start trailer file for each store and count in trailer file
DECLARE
vc_error_file NVARCHAR2(100);
vc_trailer_file NVARCHAR2(100);
BEGIN
--Creating mnt headers.
vc_error_file := 'creating Employee';
END;
v_stg_serial := 0;
v_stgRecordStore := null;
v_stgRecord := null;
FOR indexCount IN 1 .. exist_employee_varray.COUNT
LOOP
v_stg_serial := v_stg_serial + 1;
SELECT INSTR(exist_employee_varray(indexCount), ':') into charIndex from dual;
v_stgRecordStore := substr(exist_employee_varray(indexCount),0,charIndex -1);
v_stgRecord := substr(exist_employee_varray(indexCount),charIndex+1);
insert into ELR_XCN_EMP_STORE_RECORD_STG(serial,store_id,record_type,record_str) values(
v_stg_serial, v_stgRecordStore, 'Employee', v_stgRecord);
commit;
END LOOP;
v_stgRecordStore := null;
v_stgRecord := null;
FOR indexCount IN 1 .. exist_party_varray.COUNT
LOOP
v_stg_serial := v_stg_serial + 1;
SELECT INSTR(exist_party_varray(indexCount), ':') into charIndex from dual;
v_stgRecordStore := substr(exist_party_varray(indexCount),0,charIndex -1);
v_stgRecord := substr(exist_party_varray(indexCount),charIndex+1);
insert into ELR_XCN_EMP_STORE_RECORD_STG(serial,store_id,record_type,record_str) values(
v_stg_serial, v_stgRecordStore, 'Party', v_stgRecord);
commit;
END LOOP;
v_stgRecordStore := null;
v_stgRecord := null;
FOR indexCount IN 1 .. exist_employee_store_varray.COUNT
LOOP
v_stg_serial := v_stg_serial + 1;
SELECT INSTR(exist_employee_store_varray(indexCount), ':') into charIndex from dual;
v_stgRecordStore := substr(exist_employee_store_varray(indexCount),0,charIndex -1);
v_stgRecord := substr(exist_employee_store_varray(indexCount),charIndex+1);
insert into ELR_XCN_EMP_STORE_RECORD_STG(serial,store_id,record_type,record_str) values(
v_stg_serial, v_stgRecordStore, 'EmployeeStore', v_stgRecord);
commit;
END LOOP;
v_stgRecordStore := null;
v_stgRecord := null;
FOR indexCount IN 1 .. employee_varray.COUNT
LOOP
v_stg_serial := v_stg_serial + 1;
SELECT INSTR(employee_varray(indexCount), ':') into charIndex from dual;
v_stgRecordStore := substr(employee_varray(indexCount),0,charIndex -1);
v_stgRecord := substr(employee_varray(indexCount),charIndex+1);
insert into ELR_XCN_EMP_STORE_RECORD_STG(serial,store_id,record_type,record_str) values(
v_stg_serial, v_stgRecordStore, 'Employee', v_stgRecord);
commit;
END LOOP;
v_stgRecordStore := null;
v_stgRecord := null;
FOR indexCount IN 1 .. party_varray.COUNT
LOOP
v_stg_serial := v_stg_serial + 1;
SELECT INSTR(party_varray(indexCount), ':') into charIndex from dual;
v_stgRecordStore := substr(party_varray(indexCount),0,charIndex -1);
v_stgRecord := substr(party_varray(indexCount),charIndex+1);
insert into ELR_XCN_EMP_STORE_RECORD_STG(serial,store_id,record_type,record_str) values(
v_stg_serial, v_stgRecordStore, 'Party', v_stgRecord);
commit;
END LOOP;
v_stgRecordStore := null;
v_stgRecord := null;
FOR indexCount IN 1 .. employee_store_varray.COUNT
LOOP
v_stg_serial := v_stg_serial + 1;
SELECT INSTR(employee_store_varray(indexCount), ':') into charIndex from dual;
v_stgRecordStore := substr(employee_store_varray(indexCount),0,charIndex -1);
v_stgRecord := substr(employee_store_varray(indexCount),charIndex+1);
insert into ELR_XCN_EMP_STORE_RECORD_STG(serial,store_id,record_type,record_str) values(
v_stg_serial, v_stgRecordStore, 'EmployeeStore', v_stgRecord);
commit;
END LOOP;
v_stgRecordStore := null;
v_stgRecord := null;
FOR indexCount IN 1 .. employee_password_varray.COUNT
LOOP
v_stg_serial := v_stg_serial + 1;
SELECT INSTR(employee_password_varray(indexCount), ':') into charIndex from dual;
v_stgRecordStore := substr(employee_password_varray(indexCount),0,charIndex -1);
v_stgRecord := substr(employee_password_varray(indexCount),charIndex+1);
insert into ELR_XCN_EMP_STORE_RECORD_STG(serial,store_id,record_type,record_str) values(
v_stg_serial, v_stgRecordStore, 'Password', v_stgRecord);
commit;
END LOOP;
v_stgRecordStore := null;
v_stgRecord := null;
FOR indexCount IN 1 .. employee_answers_varray.COUNT
LOOP
v_stg_serial := v_stg_serial + 1;
SELECT INSTR(employee_answers_varray(indexCount), ':') into charIndex from dual;
v_stgRecordStore := substr(employee_answers_varray(indexCount),0,charIndex -1);
v_stgRecord := substr(employee_answers_varray(indexCount),charIndex+1);
insert into ELR_XCN_EMP_STORE_RECORD_STG(serial,store_id,record_type,record_str) values(
v_stg_serial, v_stgRecordStore, 'Answers', v_stgRecord);
commit;
END LOOP;
v_stgRecordStore := null;
v_stgRecord := null;
FOR indexCount IN 1 .. employee_delete_store_varray.COUNT
LOOP
v_stg_serial := v_stg_serial + 1;
SELECT INSTR(employee_delete_store_varray(indexCount), ':') into charIndex from dual;
v_stgRecordStore := substr(employee_delete_store_varray(indexCount),0,charIndex -1);
v_stgRecord := substr(employee_delete_store_varray(indexCount),charIndex+1);
insert into ELR_XCN_EMP_STORE_RECORD_STG(serial,store_id,record_type,record_str) values(
v_stg_serial, v_stgRecordStore, 'EmployeeStore', v_stgRecord);
commit;
END LOOP;
commit;
END LOOP; -- cur_store
---------------------------------------Create mnt file and trailer file-----------------------------------------------------
el_intstspk.status_rec.job_log := el_intstspk.status_rec.job_log || 'Generating mnt files: ' || CHR(10);
FOR stores IN cur_distinct_stores
LOOP
BEGIN
isEmployeeFileCreated := false;
isEmployeeStoreFileCreated := false;
isPartyFileCreated := false;
isPasswordFileCreated := false;
isAnswersFileCreated := false;
v_empRecordsCount := 0;
v_empStoreRecordsCount := 0;
v_partyRecrodsCount := 0;
v_empPwdRecordsCount := 0;
v_empAnsRecordsCount := 0;
if stores.store_id is null then
continue;
end if;
for storeRecord IN cur_store_records(trim(stores.store_id))
Loop
BEGIN
if storeRecord.record_type = 'Employee' then
if isEmployeeFileCreated = false then
isEmployeeFileCreated := true;
recordsCount(storeRecord.store_id, 'Employee', v_empRecordsCount);
vc_emp_filename := 'EMPLOYEE_' || storeRecord.store_id || '_' || to_char(systimestamp,'YYYYMMDDHH24MISSSSFF') || '.mnt';
-- creating employee trl file
vc_emp_trl_file := 'EMPLOYEE_' || storeRecord.store_id || '_' || to_char(systimestamp,'YYYYMMDDHH24MISSSSFF') || '.mnt.trl';
file_handler_emp_trl := UTL_FILE.FOPEN (vc_file_directory, vc_emp_trl_file, 'W');
vc_file_header := vc_emp_filename || ' ' || v_empRecordsCount;
UTL_FILE.PUT_LINE (file_handler_emp_trl, vc_file_header);
UTL_FILE.FCLOSE(file_handler_emp_trl);
-- create mnt
file_handler_emp := UTL_FILE.FOPEN (vc_file_directory, vc_emp_filename, 'W');
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"/>';
UTL_FILE.PUT_LINE (file_handler_emp, vc_file_header);
-- write record.
UTL_FILE.PUT_LINE (file_handler_emp, storeRecord.record_str);
elsif isEmployeeFileCreated = true then
-- write record.
UTL_FILE.PUT_LINE (file_handler_emp, storeRecord.record_str);
end if;
end if;
if storeRecord.record_type = 'EmployeeStore' then
if isEmployeeStoreFileCreated = false then
isEmployeeStoreFileCreated := true;
recordsCount(storeRecord.store_id, 'EmployeeStore', v_empStoreRecordsCount);
vc_empstore_filename := 'EMPLOYEE_STORE_' || storeRecord.store_id || '_' || to_char(systimestamp,'YYYYMMDDHH24MISSSSFF') || '.mnt';
-- creating employee_store trl file
vc_empstore_trl_file := 'EMPLOYEE_STORE_' || storeRecord.store_id || '_' || to_char(systimestamp,'YYYYMMDDHH24MISSSSFF') || '.mnt.trl';
file_handler_empstore_trl := UTL_FILE.FOPEN (vc_file_directory, vc_empstore_trl_file, 'W');
vc_file_header := vc_empstore_filename || ' ' || v_empStoreRecordsCount;
UTL_FILE.PUT_LINE (file_handler_empstore_trl, vc_file_header);
UTL_FILE.FCLOSE(file_handler_empstore_trl);
-- create mnt
file_handler_empstore := UTL_FILE.FOPEN (vc_file_directory, vc_empstore_filename, 'W');
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"/>';
UTL_FILE.PUT_LINE (file_handler_empstore, vc_file_header);
-- write record.
UTL_FILE.PUT_LINE (file_handler_empstore, storeRecord.record_str);
elsif isEmployeeStoreFileCreated = true then
-- write record.
UTL_FILE.PUT_LINE (file_handler_empstore, storeRecord.record_str);
end if;
end if;
if storeRecord.record_type = 'Party' then
if isPartyFileCreated = false then
isPartyFileCreated := true;
recordsCount(storeRecord.store_id, 'Party', v_partyRecrodsCount);
vc_party_filename := 'PARTY_' || storeRecord.store_id || '_' || to_char(systimestamp,'YYYYMMDDHH24MISSSSFF') || '.mnt';
-- creating party trl file
vc_party_trl_file := 'PARTY_' || storeRecord.store_id || '_' || to_char(systimestamp,'YYYYMMDDHH24MISSSSFF') || '.mnt.trl';
file_handler_party_trl := UTL_FILE.FOPEN (vc_file_directory, vc_party_trl_file, 'W');
vc_file_header := vc_party_filename || ' ' || v_partyRecrodsCount;
UTL_FILE.PUT_LINE (file_handler_party_trl, vc_file_header);
UTL_FILE.FCLOSE(file_handler_party_trl);
-- create mnt
file_handler_party := UTL_FILE.FOPEN (vc_file_directory, vc_party_filename, 'W');
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"/>';
UTL_FILE.PUT_LINE (file_handler_party, vc_file_header);
-- write record.
UTL_FILE.PUT_LINE (file_handler_party, storeRecord.record_str);
elsif isPartyFileCreated = true then
-- write record.
UTL_FILE.PUT_LINE (file_handler_party, storeRecord.record_str);
end if;
end if;
if storeRecord.record_type ='Password' then
if isPasswordFileCreated = false then
isPasswordFileCreated := true;
recordsCount(storeRecord.store_id, 'Password', v_empPwdRecordsCount);
vc_emp_password_filename := 'EMPLOYEE_PASSWORD_' || storeRecord.store_id || '_' || to_char(systimestamp,'YYYYMMDDHH24MISSSSFF') || '.mnt';
-- creating employee_password_trl file
vc_pwd_trl_file := 'EMPLOYEE_PASSWORD_' || storeRecord.store_id || '_' || to_char(systimestamp,'YYYYMMDDHH24MISSSSFF') || '.mnt.trl';
file_handler_pwd_trl := UTL_FILE.FOPEN (vc_file_directory, vc_pwd_trl_file, 'W');
vc_file_header := vc_emp_password_filename || ' ' || v_empPwdRecordsCount;
UTL_FILE.PUT_LINE (file_handler_pwd_trl, vc_file_header);
UTL_FILE.FCLOSE(file_handler_pwd_trl);
file_handler_emp_password := UTL_FILE.FOPEN (vc_file_directory, vc_emp_password_filename, 'W');
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"/>';
UTL_FILE.PUT_LINE (file_handler_emp_password, vc_file_header);
-- write record.
UTL_FILE.PUT_LINE (file_handler_emp_password, storeRecord.record_str);
elsif isPasswordFileCreated = true then
-- write record.
UTL_FILE.PUT_LINE (file_handler_emp_password, storeRecord.record_str);
end if;
end if;
if storeRecord.record_type ='Answers' then
if isAnswersFileCreated = false then
isAnswersFileCreated := true;
recordsCount(storeRecord.store_id, 'Answers', v_empAnsRecordsCount);
vc_emp_answers_filename := 'EMPLOYEE_ANSWERS_' || storeRecord.store_id || '_' || to_char(systimestamp,'YYYYMMDDHH24MISSSSFF') || '.mnt';
-- creating employee_answers_trl file
vc_ans_trl_file := 'EMPLOYEE_ANSWERS_' || storeRecord.store_id || '_' || v_mnt_couunter || '_' || to_char(systimestamp,'YYYYMMDDHH24MISSSSFF') || '.mnt.trl';
file_handler_ans_trl := UTL_FILE.FOPEN (vc_file_directory, vc_ans_trl_file, 'W');
vc_file_header := vc_emp_answers_filename || ' ' || v_empAnsRecordsCount;
UTL_FILE.PUT_LINE (file_handler_ans_trl, vc_file_header);
UTL_FILE.FCLOSE(file_handler_ans_trl);
file_handler_emp_answers := UTL_FILE.FOPEN (vc_file_directory, vc_emp_answers_filename, 'W');
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"/>';
UTL_FILE.PUT_LINE (file_handler_emp_answers, vc_file_header);
-- write record.
UTL_FILE.PUT_LINE (file_handler_emp_answers, storeRecord.record_str);
elsif isAnswersFileCreated = true then
-- write record.
UTL_FILE.PUT_LINE (file_handler_emp_answers, storeRecord.record_str);
end if;
end if;
END;
END LOOP;
IF UTl_FILE.IS_OPEN(file_handler_emp) = TRUE
THEN
UTL_FILE.FCLOSE(file_handler_emp);
END IF;
IF UTl_FILE.IS_OPEN(file_handler_empstore) = TRUE
THEN
UTL_FILE.FCLOSE(file_handler_empstore);
END IF;
IF UTl_FILE.IS_OPEN(file_handler_party) = TRUE
THEN
UTL_FILE.FCLOSE(file_handler_party);
END IF;
IF UTl_FILE.IS_OPEN(file_handler_emp_password) = TRUE
THEN
UTL_FILE.FCLOSE(file_handler_emp_password);
END IF;
IF UTl_FILE.IS_OPEN(file_handler_emp_answers) = TRUE
THEN
UTL_FILE.FCLOSE(file_handler_emp_answers);
END IF;
-- Close open trailer files.
IF UTl_FILE.IS_OPEN(file_handler_emp_trl) = TRUE
THEN
UTL_FILE.FCLOSE(file_handler_emp_trl);
END IF;
IF UTl_FILE.IS_OPEN(file_handler_empstore_trl) = TRUE
THEN
UTL_FILE.FCLOSE(file_handler_empstore_trl);
END IF;
IF UTl_FILE.IS_OPEN(file_handler_party_trl) = TRUE
THEN
UTL_FILE.FCLOSE(file_handler_party_trl);
END IF;
IF UTl_FILE.IS_OPEN(file_handler_pwd_trl) = TRUE
THEN
UTL_FILE.FCLOSE(file_handler_pwd_trl);
END IF;
IF UTl_FILE.IS_OPEN(file_handler_ans_trl) = TRUE
THEN
UTL_FILE.FCLOSE(file_handler_ans_trl);
END IF;
END;
end loop;
commit;
el_intstspk.status_rec.job_log := el_intstspk.status_rec.job_log || 'All mnts have been generated for records: ' || CHR(10);
-- update the staging table processed date to SYSDATE
DECLARE
vc_stg_table VARCHAR2(50);
BEGIN
vc_stg_table := ' elr_xcn_emp_assgn_staging_log';
UPDATE elr_xcn_emp_assgn_staging_log
SET processed_date = SYSDATE
WHERE processed_date IS NULL ;
VC_STG_TABLE := 'ELR_XCN_EMP_STAGING_LOG';
UPDATE ELR_XCN_EMP_STAGING_LOG
SET processed_date = SYSDATE
WHERE processed_date IS NULL;
COMMIT;
END;
END IF;
-- log the statistics
IF vi_num_iz_processed > 0 THEN
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);
IF (vi_iz_fail_count > 0) THEN
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);
END IF;
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);
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);
IF (vi_store_fail_count > 0) THEN
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);
END IF;
IF (vi_iz_fail_count > 0 OR vi_store_fail_count > 0) THEN
el_intstspk.process_warning(c_oracle, 'Process completed with errors.' || CHR(10));
END IF;
ELSE
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);
END IF;
el_intstspk.process_completed;
EXCEPTION
WHEN OTHERS THEN
IF v_err_type IS NULL THEN
v_err_type := c_oracle;
vc_job_log := 'Error: ';
END IF;
-- Close open mnt files.
IF UTl_FILE.IS_OPEN(file_handler_emp) = TRUE
THEN
UTL_FILE.FCLOSE(file_handler_emp);
END IF;
IF UTl_FILE.IS_OPEN(file_handler_empstore) = TRUE
THEN
UTL_FILE.FCLOSE(file_handler_empstore);
END IF;
IF UTl_FILE.IS_OPEN(file_handler_party) = TRUE
THEN
UTL_FILE.FCLOSE(file_handler_party);
END IF;
IF UTl_FILE.IS_OPEN(file_handler_emp_password) = TRUE
THEN
UTL_FILE.FCLOSE(file_handler_emp_password);
END IF;
IF UTl_FILE.IS_OPEN(file_handler_emp_answers) = TRUE
THEN
UTL_FILE.FCLOSE(file_handler_emp_answers);
END IF;
-- Close open trailer files.
IF UTl_FILE.IS_OPEN(file_handler_emp_trl) = TRUE
THEN
UTL_FILE.FCLOSE(file_handler_emp_trl);
END IF;
IF UTl_FILE.IS_OPEN(file_handler_empstore_trl) = TRUE
THEN
UTL_FILE.FCLOSE(file_handler_empstore_trl);
END IF;
IF UTl_FILE.IS_OPEN(file_handler_party_trl) = TRUE
THEN
UTL_FILE.FCLOSE(file_handler_party_trl);
END IF;
IF UTl_FILE.IS_OPEN(file_handler_pwd_trl) = TRUE
THEN
UTL_FILE.FCLOSE(file_handler_pwd_trl);
END IF;
IF UTl_FILE.IS_OPEN(file_handler_ans_trl) = TRUE
THEN
UTL_FILE.FCLOSE(file_handler_ans_trl);
END IF;
vc_job_log := 'Procedure is failed with error: ';
el_intstspk.process_error(v_err_type, vc_job_log);
RAISE;
END;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment