HRMS Packages
Last updated: 2/1/2026
create or replace PACKAGE BODY xxaa_abc_emp_int_pkg
AS
/****************************************************************************************
* Name : populate_conv_clock_num *
* Object Type : Procedure *
****************************************************************************************/
g_bulk_limit NUMBER := 500;
g_request_id NUMBER := fnd_global.conc_request_id;
g_effective_date DATE;
g_chunk_size NUMBER := 100;
g_debug_mode BOOLEAN := FALSE;
TYPE g_num_tab_typ IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
g_conc_mgr_table g_num_tab_typ;
g_conc_mgr_ndx PLS_INTEGER := 0;
g_curr_run_date DATE;
g_last_run_date DATE;
g_run_number NUMBER;
-- -----------------------------------------------------------------------------
-- Function to check if record is back dated
-- -----------------------------------------------------------------------------
FUNCTION is_back_dated
( p_person_id NUMBER
, p_effective_date DATE
, p_end_date DATE
, p_record_id NUMBER
, p_mode VARCHAR2
, p_cur_query VARCHAR2
)
RETURN VARCHAR2
IS
-- Local Variables
l_back_dated VARCHAR2(10) := 'FALSE';
l_rec_type VARCHAR2(50) := NULL ;
l_job_acc_exp_date VARCHAR2(20) := NULL ;
CURSOR csr_status_backdated(p_person_id NUMBER)
IS
SELECT 'TRUE'
FROM xxaa_abc_emp_tab emp ,
xxaa_abc_emp_tab emp2,
per_all_assignments_f paaf
WHERE emp.person_id = emp2.person_id
AND emp.person_id = paaf.person_id
AND emp.record_id =( SELECT MAX(emp1.record_id)
FROM xxaa_abc_emp_tab emp1
WHERE emp1.person_id =emp.person_id
AND emp1.record_id <> p_record_id
AND NVL(emp1.future_use10,'X')<>'FUTURE')
AND emp.employment_status <> emp2.employment_status
AND emp2.person_id = p_person_id
AND emp2.record_id = p_record_id
AND paaf.assignment_id = (SELECT MAX(assignment_id) FROM per_all_assignments_f paaf2
WHERE TRUNC(SYSDATE) BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
AND paaf2.person_id=paaf.person_id)
AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paaf.effective_start_date <> TRUNC(SYSDATE);
CURSOR csr_curr_rec_type
IS
SELECT future_use10, pri_job_account_exp_date FROM xxaa_abc_emp_tab
WHERE record_id = p_record_id;
-- Cursor to check back dated value for labour account
CURSOR csr_account_backdated(p_person_id NUMBER,p_job_acc_exp_date VARCHAR2)
IS
SELECT 'TRUE'
FROM xxaa_abc_emp_tab emp
, per_all_assignments_f paaf
, per_jobs pj
, hr_locations_all hla
, fnd_lookup_values flv
, fnd_lookup_values flvd
,(SELECT ffvt.flex_value_meaning dept_code
, ffvt.description dept_display
FROM fnd_flex_value_sets ffvs
, fnd_flex_values ffv
, fnd_flex_values_tl ffvt
WHERE ffv.flex_value_set_id = ffvs.flex_value_set_id
AND ffv.flex_value_id = ffvt.flex_value_id
AND ffvs.flex_value_set_name = 'ABC_RETAIL_DEPT'
) dept_map
WHERE emp.person_id = paaf.person_id
AND paaf.job_id = pj.job_id
AND paaf.location_id = hla.location_id
AND flv.lookup_type(+) = 'xxaa_abc_STORE_CONTROL'
-- AND flv.enabled_flag = 'Y'
AND flv.attribute3(+) = paaf.location_id
-- AND flv.attribute2 IS NOT NULL
AND flv.security_group_id(+) = 0
AND flvd.lookup_type(+) = 'xxaa_ABC_DEPT_MAPPING'
AND flvd.enabled_flag(+) = 'Y'
AND flvd.security_group_id(+) = 0
AND flvd.lookup_code(+) = paaf.ass_attribute1
AND dept_map.dept_code (+) = paaf.ass_attribute1
AND emp.record_id = (SELECT MAX(emp1.record_id)
FROM xxaa_abc_emp_tab emp1
WHERE emp1.person_id = p_person_id
AND emp1.record_id <> p_record_id
AND NVL(emp1.future_use10,'X')<>'FUTURE')
AND ( emp.job_code <> SUBSTR(pj.name,1,5)
OR emp.location <> hla.location_code
OR emp.department <> DECODE(flv.attribute9
,'Retail',dept_map.dept_display
,'Convenience','Store'
,'Small Retail',NVL(FLVD.ATTRIBUTE2,DEPT_MAP.DEPT_DISPLAY))
OR TRUNC(to_date(p_job_acc_exp_date,'DD-MON-RRRR HH24:MI:SS'))<>TRUNC(to_date(emp.pri_job_account_exp_date,'DD-MON-RRRR HH24:MI:SS')) -- V4.6
)
AND paaf.assignment_id = (SELECT MAX(assignment_id) FROM per_all_assignments_f paaf2
WHERE TRUNC(SYSDATE) BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
AND paaf2.person_id=paaf.person_id)
AND paaf.effective_start_date <> TRUNC(SYSDATE)
AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND emp.person_id = p_person_id;
BEGIN
xxaa_abc_util_pkg.write_debug ('V4.3 Begin is_back_dated '||p_person_id||' for '||p_mode);
xxaa_abc_util_pkg.write_debug ('V4.3 is_back_dated g_curr_run_date - '||g_curr_run_date||';g_last_run_date - '||g_last_run_date||';p_effective_date - '||p_effective_date||';p_end_date - '||p_end_date||';p_record_id - '||p_record_id);
IF g_curr_run_date is null
THEN
-- Called from Resync Program or Cutover Program
l_back_dated := 'FALSE';
-- xxaa_abc_util_pkg.write_debug ('V4.3 is_back_dated resync or cutover record'); --V4.4
ELSIF p_effective_date > g_curr_run_date
THEN
-- Future Record
l_back_dated := 'FALSE';
-- xxaa_abc_util_pkg.write_debug ('V4.3 is_back_dated future record'); --V4.4
ELSIF (p_effective_date < g_curr_run_date
AND p_end_date < g_curr_run_date)
THEN
-- Past Record
l_back_dated := 'FALSE';
-- xxaa_abc_util_pkg.write_debug ('V4.3 is_back_dated past record'); --V4.4
ELSE
OPEN csr_curr_rec_type;
FETCH csr_curr_rec_type INTO l_rec_type,l_job_acc_exp_date;
CLOSE csr_curr_rec_type;
IF NVL(l_rec_type,'X') IN ('FUTURE','FUTURE_HIRE') THEN
l_back_dated := 'FALSE';
ELSE
-- Current Record
IF p_mode = 'Status'
THEN
OPEN csr_status_backdated(p_person_id);
FETCH csr_status_backdated INTO l_back_dated;
CLOSE csr_status_backdated;
ELSIF p_mode = 'Account'
THEN
OPEN csr_account_backdated(p_person_id,l_job_acc_exp_date);
FETCH csr_account_backdated INTO l_back_dated;
CLOSE csr_account_backdated;
END IF;
END IF;
END IF;
xxaa_abc_util_pkg.write_debug ('End is_back_dated '||l_back_dated);
RETURN l_back_dated;
EXCEPTION
WHEN OTHERS THEN
xxaa_abc_util_pkg.write_log ( 'Exception - is_back_dated - '||SQLERRM);
END is_back_dated;
-- -----------------------------------------------------------------------------
-- V4.3 Procedure to populate calendar records
-- -----------------------------------------------------------------------------
PROCEDURE pop_calendar_records
(p_record_id NUMBER
,p_person_id NUMBER
,p_mode VARCHAR2
,p_effective_date VARCHAR2
)
IS
l_badge_data xxaa_abc_badges_tab;
l_status_data xxaa_abc_status_tab;
l_account_data xxaa_abc_pri_account_tab;
l_record_id NUMBER;
c_limit PLS_INTEGER := 100;
l_term_upp_limit NUMBER;
-- Cursor to get back dated termination limit
CURSOR csr_get_term_limit
IS
SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type ='xxaa_CLOCKNUM_REL_DAYS'
AND lookup_code ='TERM_LIMIT'
AND enabled_flag ='Y';
-- Cursor to get Badge Details
CURSOR csr_badge_details(p_person_id NUMBER)
IS
--Initial + Incremental
SELECT badge_data
FROM (SELECT xxaa_abc_badges_typ
( DECODE( NVL(paaf.ass_attribute6, 'NA'), 'NA', NULL, SUBSTR(haou.name,2,4)||paaf.ass_attribute6)
, TO_CHAR(MIN(paaf.effective_start_date),'DD-MON-YYYY HH24:MI')
, '01-JAN-3000 00:00'
) badge_data
, SUBSTR(haou.name,2,4)||DECODE(paaf.ass_attribute6, 'NA', NULL, paaf.ass_attribute6) - lag(SUBSTR(haou.name,2,4)||DECODE(paaf.ass_attribute6, 'NA', NULL, paaf.ass_attribute6)) over(order by paaf.effective_start_date) badge_delta
FROM per_all_assignments_f paaf
, hr_all_organization_units haou
, per_periods_of_service ppos
WHERE paaf.organization_id = haou.organization_id
AND paaf.ass_attribute6 is not null
AND paaf.assignment_type = 'E'
AND paaf.primary_flag = 'Y'
AND paaf.ass_attribute30 IN ('A', 'P')
AND paaf.person_id = p_person_id
AND TRUNC(paaf.effective_end_date) >= TRUNC(NVL(g_effective_date, SYSDATE))
--Exclude during transfer to non-live
AND ( paaf.location_id IN ( SELECT attribute3
FROM fnd_lookup_values flv
WHERE lookup_type = 'xxaa_abc_STORE_CONTROL'
AND attribute2 IS NOT NULL
)
)
--Exclude during termination
AND paaf.person_id = ppos.person_id
/*AND ppos.actual_termination_date IS NULL
AND (ppos.date_start = (SELECT ppos1.date_start
FROM per_periods_of_service ppos1
WHERE ppos1.person_id = paaf.person_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(ppos1.date_start) AND NVL(TRUNC(ppos1.actual_termination_date), TO_DATE('31-Dec-4712')))
OR ppos.date_start = (SELECT ppos1.date_start
FROM per_periods_of_service ppos1
WHERE ppos1.person_id = paaf.person_id
AND TRUNC(ppos1.date_start) >= TRUNC(SYSDATE))
OR ppos.actual_termination_date BETWEEN (TRUNC(SYSDATE)- l_term_upp_limit) AND (TRUNC(SYSDATE)))*/ --V5.1
AND ppos.period_of_service_id = (SELECT MAX(period_of_service_id) FROM per_periods_of_service a WHERE a.person_id = ppos.person_id AND ppos.actual_termination_date IS NULL) --V5.2
GROUP BY paaf.person_id
, paaf.ass_attribute6
, SUBSTR(haou.name,2,4)
, paaf.effective_start_date
)
WHERE badge_delta IS NULL
OR badge_delta <> 0
UNION
--Termination
SELECT badge_data
FROM (SELECT xxaa_abc_badges_typ
( DECODE( NVL(paaf.ass_attribute6, 'NA'), 'NA', NULL, SUBSTR(haou.name,2,4)||paaf.ass_attribute6)
, DECODE(ROWNUM, 1, TO_CHAR(MIN(paaf.effective_start_date),'DD-MON-YYYY HH24:MI'), lag(TO_CHAR(MIN(paaf.effective_start_date),'DD-MON-YYYY HH24:MI'))over(order by paaf.effective_start_date))
, DECODE(ROWNUM, 1, '01-JAN-3000 00:00', TO_CHAR(actual_termination_date,'DD-MON-YYYY')||' 23:59') --V4.7
) badge_data
, SUBSTR(haou.name,2,4)||DECODE(paaf.ass_attribute6, 'NA', NULL, paaf.ass_attribute6) - lag(SUBSTR(haou.name,2,4)||DECODE(paaf.ass_attribute6, 'NA', NULL, paaf.ass_attribute6)) over(order by paaf.effective_start_date) badge_delta
, paaf.assignment_status_type_id - lag(paaf.assignment_status_type_id) over(order by paaf.effective_start_date) status_delta
, ppos.actual_termination_date
FROM per_all_assignments_f paaf
, hr_all_organization_units haou
, per_periods_of_service ppos
WHERE paaf.organization_id = haou.organization_id
AND paaf.ass_attribute6 is not null
AND paaf.assignment_type = 'E'
AND paaf.primary_flag = 'Y'
AND paaf.ass_attribute30 IN ('A', 'P')
AND paaf.person_id = p_person_id
AND paaf.person_id = ppos.person_id
/* AND (ppos.date_start = (SELECT ppos1.date_start
FROM per_periods_of_service ppos1
WHERE ppos1.person_id = paaf.person_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(ppos1.date_start) AND NVL(TRUNC(ppos1.actual_termination_date), TO_DATE('31-Dec-4712')))
OR ppos.date_start = (SELECT ppos1.date_start
FROM per_periods_of_service ppos1
WHERE ppos1.person_id = paaf.person_id
AND TRUNC(ppos1.date_start) >= TRUNC(SYSDATE))
OR ppos.actual_termination_date BETWEEN (TRUNC(SYSDATE)- l_term_upp_limit) AND (TRUNC(SYSDATE))) --V5.1
AND ppos.actual_termination_date IS NOT NULL*/
AND ppos.period_of_service_id = (SELECT MAX(period_of_service_id) FROM per_periods_of_service a WHERE a.person_id = ppos.person_id AND ppos.actual_termination_date IS NOT NULL) --V5.2
AND (TRUNC(paaf.effective_end_date) >= TRUNC(NVL(g_effective_date, SYSDATE))
OR TRUNC(paaf.effective_end_date) BETWEEN (TRUNC(SYSDATE)-l_term_upp_limit) AND (TRUNC(SYSDATE))) --V4.5
GROUP BY paaf.person_id
, paaf.ass_attribute6
, SUBSTR(haou.name,2,4)
, paaf.effective_start_date
, ppos.actual_termination_date
, paaf.assignment_status_type_id
, ROWNUM
)
WHERE badge_delta IS NULL
OR badge_delta <> 0
OR status_delta IS NULL
OR status_delta <> 0
UNION
--Transfer to Non-live
SELECT badge_data
FROM (SELECT xxaa_abc_badges_typ
( xket.badge_number
, xket.badge_number_effective_date
, TO_CHAR(MIN(paaf.effective_start_date-1),'DD-MON-YYYY')||' 23:59' --V4.7
) badge_data
, SUBSTR(haou.name,2,4)||DECODE(paaf.ass_attribute6, 'NA', NULL, paaf.ass_attribute6) - lag(SUBSTR(haou.name,2,4)||DECODE(paaf.ass_attribute6, 'NA', NULL, paaf.ass_attribute6)) over(order by paaf.effective_start_date) badge_delta
FROM per_all_assignments_f paaf
, hr_all_organization_units haou
, xxaa_abc_emp_tab xket
WHERE paaf.organization_id = haou.organization_id
AND paaf.ass_attribute6 is not null
AND paaf.assignment_type = 'E'
AND paaf.primary_flag = 'Y'
AND paaf.ass_attribute30 IN ('A', 'P')
AND paaf.person_id = p_person_id
--AND TRUNC(paaf.effective_end_date) >= TRUNC(SYSDATE)
AND TRUNC(paaf.effective_end_date) >= TRUNC(NVL(g_effective_date, SYSDATE))
AND ( paaf.location_id NOT IN ( SELECT attribute3
FROM fnd_lookup_values flv
WHERE lookup_type = 'xxaa_abc_STORE_CONTROL'
AND attribute2 IS NOT NULL
)
)
AND xket.person_id = paaf.person_id
AND xket.record_id = (SELECT MAX(record_id) FROM xxaa_abc_emp_tab WHERE person_id = paaf.person_id
AND record_id < (SELECT MAX(record_id) FROM xxaa_abc_emp_tab WHERE person_id = paaf.person_id))
GROUP BY paaf.person_id
, paaf.ass_attribute6
, SUBSTR(haou.name,2,4)
, paaf.effective_start_date
, xket.badge_number
, xket.badge_number_effective_date
)
WHERE badge_delta IS NULL
OR badge_delta <> 0
UNION
--Transfer to Convenience to Supermarket V5.3
SELECT badge_data
FROM (SELECT xxaa_abc_badges_typ
( xket.badge_number
, xket.badge_number_effective_date
, TO_CHAR(MIN(paaf.effective_start_date-1),'DD-MON-YYYY')||' 23:59' --V4.7
) badge_data
, SUBSTR(haou.name,2,4)||DECODE(paaf.ass_attribute6, 'NA', NULL, paaf.ass_attribute6) - lag(SUBSTR(haou.name,2,4)||DECODE(paaf.ass_attribute6, 'NA', NULL, paaf.ass_attribute6)) over(order by paaf.effective_start_date) badge_delta
FROM per_all_assignments_f paaf
, hr_all_organization_units haou
, xxaa_abc_emp_tab xket
WHERE paaf.organization_id = haou.organization_id
AND paaf.ass_attribute6 is not null
AND paaf.assignment_type = 'E'
AND paaf.primary_flag = 'Y'
AND paaf.ass_attribute30 IN ('A', 'P')
AND paaf.person_id = p_person_id
--AND TRUNC(paaf.effective_end_date) >= TRUNC(NVL(g_effective_date, SYSDATE))
AND TRUNC(paaf.effective_end_date) = TRUNC(TO_DATE('31-DEC-4712'))
AND ( paaf.location_id IN ( SELECT attribute3
FROM fnd_lookup_values flv
WHERE lookup_type = 'xxaa_abc_STORE_CONTROL'
AND attribute2 IS NOT NULL
AND attribute1 = 'Supermarket'
AND NOT EXISTS (SELECT 'Y' FROM fnd_lookup_values flv1 WHERE lookup_type = 'xxaa_abc_STORE_EXCP_CONTROL' AND flv1.lookup_code = flv.lookup_code
AND flv1.attribute1 = 'Y'
AND flv1.enabled_flag = 'Y')
)
)
AND xket.person_id = paaf.person_id
AND xket.record_id = (SELECT MAX(record_id) FROM xxaa_abc_emp_tab WHERE person_id = paaf.person_id
AND record_id < (SELECT MAX(record_id) FROM xxaa_abc_emp_tab WHERE person_id = paaf.person_id))
GROUP BY paaf.person_id
, paaf.ass_attribute6
, SUBSTR(haou.name,2,4)
, paaf.effective_start_date
, xket.badge_number
, xket.badge_number_effective_date
)
WHERE badge_delta IS NULL
OR badge_delta <> 0;
-- Cursor to get Employment Status
CURSOR csr_emp_status_details(p_person_id NUMBER)
IS
--Initial (Current + Future)
SELECT xxaa_abc_status_typ
( user_status
, DECODE (ROWNUM, 1, TO_CHAR(date_start,'DD-MON-YY'), TO_CHAR(min_effective_date,'DD-MON-YY'))
, '01-JAN-3000'
, is_back_dated
( person_id
, min_effective_date
, NVL(lead(min_effective_date)over(order by min_effective_date)-1,'31-Dec-4712')
, p_record_id
, 'Status'
, NULL
)
) status_data
FROM (SELECT paaf.person_id person_id
, MIN(paaf.effective_start_date) min_effective_date
, past.user_status user_status
, paaf.assignment_status_type_id - lag(paaf.assignment_status_type_id) over(order by paaf.effective_start_date) status_delta
, ppos.date_start date_start
FROM per_all_assignments_f paaf
, per_assignment_status_types past
, per_periods_of_service ppos
WHERE past.assignment_status_type_id = paaf.assignment_status_type_id
AND paaf.assignment_type = 'E'
AND paaf.primary_flag = 'Y'
AND paaf.ass_attribute30 IN ('A','P')
AND paaf.person_id = p_person_id
AND paaf.person_id = ppos.person_id
/*AND ppos.actual_termination_date IS NULL
AND (ppos.date_start = (SELECT ppos1.date_start
FROM per_periods_of_service ppos1
WHERE ppos1.person_id = paaf.person_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(ppos1.date_start) AND NVL(TRUNC(ppos1.actual_termination_date), TO_DATE('31-Dec-4712')))
OR ppos.date_start = (SELECT ppos1.date_start
FROM per_periods_of_service ppos1
WHERE ppos1.person_id = paaf.person_id
AND TRUNC(ppos1.date_start) >= TRUNC(SYSDATE))
OR ppos.actual_termination_date BETWEEN (TRUNC(SYSDATE)- l_term_upp_limit) AND (TRUNC(SYSDATE)))*/ --V5.1
AND ppos.period_of_service_id = (SELECT MAX(period_of_service_id) FROM per_periods_of_service a WHERE a.person_id = ppos.person_id AND ppos.actual_termination_date IS NULL) --V5.2
AND TRUNC(paaf.effective_end_date) >= TRUNC(NVL(g_effective_date, SYSDATE))
AND NOT EXISTS ( SELECT 'Y' FROM xxaa_abc_emp_tab xket WHERE xket.person_id = paaf.person_id AND record_mode = 'UPDATE')
AND ( paaf.location_id IN ( SELECT attribute3
FROM fnd_lookup_values flv
WHERE lookup_type = 'xxaa_abc_STORE_CONTROL'
AND attribute2 IS NOT NULL
)
)
GROUP BY paaf.person_id
, past.user_status
, paaf.assignment_status_type_id
, paaf.effective_start_date
, ppos.date_start
ORDER BY paaf.effective_start_date
)
WHERE status_delta IS NULL
OR status_delta <> 0
UNION
--Incremental (Current + Future)
SELECT xxaa_abc_status_typ
( user_status
, TO_CHAR(min_effective_date,'DD-MON-YY')
, '01-JAN-3000'
, is_back_dated
( person_id
, min_effective_date
, NVL(lead(min_effective_date)over(order by min_effective_date)-1,'31-Dec-4712')
, p_record_id
, 'Status'
, NULL -- V4.6
)
) status_data
FROM (SELECT paaf.person_id person_id
, MIN(paaf.effective_start_date) min_effective_date
, past.user_status user_status
, paaf.assignment_status_type_id - lag(paaf.assignment_status_type_id) over(order by paaf.effective_start_date) status_delta
, ppos.date_start date_start
FROM per_all_assignments_f paaf
, per_assignment_status_types past
, per_periods_of_service ppos
WHERE past.assignment_status_type_id = paaf.assignment_status_type_id
AND paaf.assignment_type = 'E'
AND paaf.primary_flag = 'Y'
AND paaf.ass_attribute30 IN ('A','P')
AND paaf.person_id = p_person_id
AND paaf.person_id = ppos.person_id
/*AND (ppos.date_start = (SELECT ppos1.date_start
FROM per_periods_of_service ppos1
WHERE ppos1.person_id = paaf.person_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(ppos1.date_start) AND NVL(TRUNC(ppos1.actual_termination_date), TO_DATE('31-Dec-4712')))
OR ppos.date_start = (SELECT ppos1.date_start
FROM per_periods_of_service ppos1
WHERE ppos1.person_id = paaf.person_id
AND TRUNC(ppos1.date_start) >= TRUNC(SYSDATE))
OR ppos.actual_termination_date BETWEEN (TRUNC(SYSDATE)- l_term_upp_limit) AND (TRUNC(SYSDATE))) */--V5.1
AND TRUNC(paaf.effective_end_date) >= TRUNC(NVL(g_effective_date, SYSDATE))
AND EXISTS ( SELECT 'Y' FROM xxaa_abc_emp_tab xket WHERE xket.person_id = paaf.person_id AND record_mode IN ('UPDATE', 'RESYNC'))
--Exclude during transfer to non-live
AND ( paaf.location_id IN ( SELECT attribute3
FROM fnd_lookup_values flv
WHERE lookup_type = 'xxaa_abc_STORE_CONTROL'
AND attribute2 IS NOT NULL
)
)
--Exclude during termination
--AND ppos.actual_termination_date IS NULL
AND ppos.period_of_service_id = (SELECT MAX(period_of_service_id) FROM per_periods_of_service a WHERE a.person_id = ppos.person_id AND ppos.actual_termination_date IS NULL) --V5.2
GROUP BY paaf.person_id
, past.user_status
, paaf.assignment_status_type_id
, paaf.effective_start_date
, ppos.date_start
ORDER BY paaf.effective_start_date
)
WHERE status_delta IS NULL
OR status_delta <> 0
UNION
--Termination
SELECT xxaa_abc_status_typ
( user_status
, DECODE(ROWNUM, 1, TO_CHAR(min_effective_date,'DD-MON-YY'), TO_CHAR(actual_termination_date+1,'DD-MON-YY'))
, '01-JAN-3000'
, is_back_dated
( person_id
, min_effective_date
, NVL(lead(min_effective_date)over(order by min_effective_date)-1,'31-Dec-4712')
, p_record_id
, 'Status'
, NULL -- V4.6
)
) status_data
FROM (SELECT paaf.person_id person_id
, MIN(paaf.effective_start_date) min_effective_date
, past.user_status user_status
, paaf.assignment_status_type_id - lag(paaf.assignment_status_type_id) over(order by paaf.effective_start_date) status_delta
, ppos.actual_termination_date
FROM per_all_assignments_f paaf
, per_assignment_status_types past
, per_periods_of_service ppos
WHERE past.assignment_status_type_id = paaf.assignment_status_type_id
AND paaf.assignment_type = 'E'
AND paaf.primary_flag = 'Y'
AND paaf.ass_attribute30 IN ('A','P')
AND paaf.person_id = p_person_id
AND paaf.person_id = ppos.person_id
/*AND (ppos.date_start = (SELECT ppos1.date_start
FROM per_periods_of_service ppos1
WHERE ppos1.person_id = paaf.person_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(ppos1.date_start) AND NVL(TRUNC(ppos1.actual_termination_date), TO_DATE('31-Dec-4712')))
OR ppos.date_start = (SELECT ppos1.date_start
FROM per_periods_of_service ppos1
WHERE ppos1.person_id = paaf.person_id
AND TRUNC(ppos1.date_start) >= TRUNC(SYSDATE))
OR ppos.actual_termination_date BETWEEN (TRUNC(SYSDATE)- l_term_upp_limit) AND (TRUNC(SYSDATE))) --V5.1
AND ppos.actual_termination_date IS NOT NULL*/
AND ppos.period_of_service_id = (SELECT MAX(period_of_service_id) FROM per_periods_of_service a WHERE a.person_id = ppos.person_id AND ppos.actual_termination_date IS NOT NULL) --V5.2
AND (TRUNC(paaf.effective_end_date) >= TRUNC(NVL(g_effective_date, SYSDATE))
OR TRUNC(paaf.effective_end_date) BETWEEN (TRUNC(SYSDATE)-l_term_upp_limit) AND (TRUNC(SYSDATE))) --V4.5
GROUP BY paaf.person_id
, past.user_status
, paaf.assignment_status_type_id
, paaf.effective_start_date
, ppos.actual_termination_date
ORDER BY paaf.effective_start_date
)
WHERE status_delta IS NULL
OR status_delta <> 0
UNION
--Transfer to non-live
SELECT xxaa_abc_status_typ
( employment_status
, TO_CHAR(min_effective_date,'DD-MON-YY')
, '01-JAN-3000'
, is_back_dated
( person_id
, min_effective_date
, NVL(lead(min_effective_date)over(order by min_effective_date)-1,'31-Dec-4712')
, p_record_id
, 'Status'
, NULL -- V4.6
)
) status_data
FROM (SELECT paaf.person_id person_id
, MIN(paaf.effective_start_date) min_effective_date
, past.user_status user_status
, paaf.assignment_status_type_id - lag(paaf.assignment_status_type_id) over(order by paaf.effective_start_date) status_delta
, ppos.date_start date_start
, 'Terminate Assignment' employment_status
, paaf.location_id - lag(paaf.location_id) over(order by paaf.effective_start_date) loc_delta
FROM per_all_assignments_f paaf
, per_assignment_status_types past
, per_periods_of_service ppos
, xxaa_abc_emp_tab xket
WHERE past.assignment_status_type_id = paaf.assignment_status_type_id
AND paaf.assignment_type = 'E'
AND paaf.primary_flag = 'Y'
AND paaf.ass_attribute30 IN ('A','P')
AND paaf.person_id = p_person_id
AND paaf.person_id = ppos.person_id
/*AND ppos.actual_termination_date IS NULL
AND (ppos.date_start = (SELECT ppos1.date_start
FROM per_periods_of_service ppos1
WHERE ppos1.person_id = paaf.person_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(ppos1.date_start) AND NVL(TRUNC(ppos1.actual_termination_date), TO_DATE('31-Dec-4712')))
OR ppos.date_start = (SELECT ppos1.date_start
FROM per_periods_of_service ppos1
WHERE ppos1.person_id = paaf.person_id
AND TRUNC(ppos1.date_start) >= TRUNC(SYSDATE))
OR ppos.actual_termination_date BETWEEN (TRUNC(SYSDATE)- l_term_upp_limit) AND (TRUNC(SYSDATE)))*/ --V5.1
AND ppos.period_of_service_id = (SELECT MAX(period_of_service_id) FROM per_periods_of_service a WHERE a.person_id = ppos.person_id AND ppos.actual_termination_date IS NULL) --V5.2
AND TRUNC(paaf.effective_end_date) >= TRUNC(NVL(g_effective_date, SYSDATE))
AND ( paaf.location_id NOT IN ( SELECT attribute3
FROM fnd_lookup_values flv
WHERE lookup_type = 'xxaa_abc_STORE_CONTROL'
AND attribute2 IS NOT NULL
)
)
AND xket.person_id = paaf.person_id
AND xket.record_id = (SELECT MAX(record_id) FROM xxaa_abc_emp_tab WHERE person_id = paaf.person_id
AND record_id < (SELECT MAX(record_id) FROM xxaa_abc_emp_tab WHERE person_id = paaf.person_id))
GROUP BY paaf.person_id
, past.user_status
, paaf.assignment_status_type_id
, paaf.effective_start_date
, ppos.date_start
, xket.employment_status
, paaf.location_id
ORDER BY paaf.effective_start_date
)
WHERE status_delta IS NULL
OR status_delta <> 0
OR loc_delta IS NULL
OR loc_delta <> 0;
-- Cursor to get Labour Account Details
CURSOR csr_account_details(p_person_id NUMBER)
IS
--Initial + Incremental
SELECT xxaa_abc_pri_account_typ
( 'Sample'
|| '/'|| 'Retail'
|| '/'|| attribute5
|| '/'|| attribute6
|| '/'|| attribute7
|| '/'|| attribute1
|| '/'|| attribute13
|| '/'||DECODE(attribute9, 'Retail',dept_display,'Convenience', 'Store','Small Retail',d_attribute2)
|| '/'|| SUBSTR (job_name,INSTR (job_name, '|') + 1,INSTR (SUBSTR (job_name, INSTR (job_name, '|') + 1), '|', -1) - 1)
, attribute1
|| '/'|| attribute5
|| '/'|| attribute6
|| '/'|| attribute7
|| '/'|| attribute8
|| '/'|| DECODE (attribute1, 'Convenience', 'Store', 'Small Retail',d_attribute2,'Retail',dept_display)
|| '/'|| SUBSTR (job_name, 2, 4)
, TO_CHAR(min_effective_date,'DD-MON-YY')
, '01-JAN-3000'
, is_back_dated
( person_id
, min_effective_date
, NVL(lead(min_effective_date)over(order by min_effective_date)-1,'31-Dec-4712')
, p_record_id
, 'Account'
, NULL ) -- V4.6
) account_data
FROM (SELECT paaf.person_id person_id
, MIN(paaf.effective_start_date) min_effective_date
, flv.attribute1
, flv.attribute5
, flv.attribute6
, flv.attribute7
, flv.attribute8
, flv.attribute13
, flv.attribute9
, dept_map.dept_display dept_display
, NVL(FLVD.ATTRIBUTE2,DEPT_MAP.DEPT_DISPLAY) d_attribute2 --V4.8
, pj.name job_name
, paaf.job_id - lag(paaf.job_id) over(order by paaf.effective_start_date) job_delta
, paaf.location_id - lag(paaf.location_id) over(order by paaf.effective_start_date) loc_delta
, CASE WHEN paaf.ass_attribute1 <> lag(paaf.ass_attribute1) over(order by paaf.effective_start_date)
THEN 1
ELSE 0
END dept_delta
FROM per_all_assignments_f paaf
, per_jobs pj
, hr_all_organization_units haou
, fnd_lookup_values flv
, fnd_lookup_values flvd
, (SELECT ffvt.flex_value_meaning dept_code
, ffvt.description dept_display
FROM fnd_flex_value_sets ffvs
, fnd_flex_values ffv
, fnd_flex_values_tl ffvt
WHERE ffv.flex_value_set_id = ffvs.flex_value_set_id
AND ffv.flex_value_id = ffvt.flex_value_id
AND ffvs.flex_value_set_name = 'ABC_RETAIL_DEPT'
) dept_map
, per_periods_of_service ppos
WHERE paaf.organization_id = haou.organization_id
AND paaf.job_id = pj.job_id
AND flv.lookup_type = 'xxaa_abc_STORE_CONTROL'
AND flv.enabled_flag = 'Y'
AND flv.lookup_code = SUBSTR(haou.name,2,4)
AND flv.security_group_id = 0
AND flvd.lookup_type(+) = 'xxaa_ABC_DEPT_MAPPING'
AND flvd.enabled_flag(+) = 'Y'
AND flvd.security_group_id(+) = 0
AND flvd.lookup_code(+) = paaf.ass_attribute1
AND dept_map.dept_code (+) = paaf.ass_attribute1
AND paaf.assignment_type = 'E'
AND paaf.primary_flag = 'Y'
AND paaf.ass_attribute30 IN ('A','P')
AND paaf.person_id = p_person_id
--AND TRUNC(paaf.effective_end_date) >= TRUNC(SYSDATE)
AND TRUNC(paaf.effective_end_date) >= TRUNC(NVL(g_effective_date, SYSDATE))
--Exclude during transfer to non-live
AND ( paaf.location_id IN ( SELECT attribute3
FROM fnd_lookup_values flv
WHERE lookup_type = 'xxaa_abc_STORE_CONTROL'
AND attribute2 IS NOT NULL
)
)
--Exclude during termination
AND paaf.person_id = ppos.person_id
/*AND ppos.actual_termination_date IS NULL
AND (ppos.date_start = (SELECT ppos1.date_start
FROM per_periods_of_service ppos1
WHERE ppos1.person_id = paaf.person_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(ppos1.date_start) AND NVL(TRUNC(ppos1.actual_termination_date), TO_DATE('31-Dec-4712')))
OR ppos.date_start = (SELECT ppos1.date_start
FROM per_periods_of_service ppos1
WHERE ppos1.person_id = paaf.person_id
AND TRUNC(ppos1.date_start) >= TRUNC(SYSDATE))
OR ppos.actual_termination_date BETWEEN (TRUNC(SYSDATE)- l_term_upp_limit) AND (TRUNC(SYSDATE))) */--V5.1
AND ppos.period_of_service_id = (SELECT MAX(period_of_service_id) FROM per_periods_of_service a WHERE a.person_id = ppos.person_id AND ppos.actual_termination_date IS NULL) --V5.2
GROUP BY paaf.person_id
, flv.lookup_code,flv.attribute1,flv.attribute5,flv.attribute6,flv.attribute7,flv.attribute8,flv.attribute13,flv.attribute9
, dept_map.dept_display,flvd.attribute2
, pj.name
, paaf.effective_start_date
, paaf.job_id
, paaf.location_id
, paaf.ass_attribute1
ORDER BY MIN(paaf.effective_start_date)
)
WHERE job_delta IS NULL
OR job_delta <> 0
OR loc_delta IS NULL
OR loc_delta <> 0
OR dept_delta IS NULL
OR dept_delta <> 0
UNION
--Termination
SELECT xxaa_abc_pri_account_typ
( 'Sample'
|| '/'|| 'Retail'
|| '/'|| attribute5
|| '/'|| attribute6
|| '/'|| attribute7
|| '/'|| attribute1
|| '/'|| attribute13
|| '/'||DECODE(attribute9, 'Retail',dept_display,'Convenience', 'Store','Small Retail',d_attribute2)
|| '/'|| SUBSTR (job_name,INSTR (job_name, '|') + 1,INSTR (SUBSTR (job_name, INSTR (job_name, '|') + 1), '|', -1) - 1)
, attribute1
|| '/'|| attribute5
|| '/'|| attribute6
|| '/'|| attribute7
|| '/'|| attribute8
|| '/'|| DECODE (attribute1, 'Convenience', 'Store', 'Small Retail',d_attribute2,'Retail',dept_display)
|| '/'|| SUBSTR (job_name, 2, 4)
, DECODE(ROWNUM, 1, TO_CHAR(min_effective_date,'DD-MON-YY'), lag(TO_CHAR(min_effective_date,'DD-MON-YY'))over(order by min_effective_date))
, DECODE(ROWNUM, 1, '01-JAN-3000', TO_CHAR(actual_termination_date,'DD-MON-YYYY'))
, is_back_dated
( person_id
, min_effective_date
, NVL(lead(min_effective_date)over(order by min_effective_date)-1,'31-Dec-4712')
, p_record_id
, 'Account'
, 'TERMINATION')
) account_data
FROM (SELECT paaf.person_id person_id
, MIN(paaf.effective_start_date) min_effective_date
, flv.attribute1
, flv.attribute5
, flv.attribute6
, flv.attribute7
, flv.attribute8
, flv.attribute13
, flv.attribute9
, dept_map.dept_display dept_display
, NVL(FLVD.ATTRIBUTE2,DEPT_MAP.DEPT_DISPLAY) d_attribute2
, pj.name job_name
, paaf.job_id - lag(paaf.job_id) over(order by paaf.effective_start_date) job_delta
, paaf.location_id - lag(paaf.location_id) over(order by paaf.effective_start_date) loc_delta
, CASE WHEN paaf.ass_attribute1 <> lag(paaf.ass_attribute1) over(order by paaf.effective_start_date)
THEN 1
ELSE 0
END dept_delta
, paaf.assignment_status_type_id - lag(paaf.assignment_status_type_id) over(order by paaf.effective_start_date) status_delta
, ppos.actual_termination_date
FROM per_all_assignments_f paaf
, per_jobs pj
, hr_all_organization_units haou
, fnd_lookup_values flv
, fnd_lookup_values flvd
, (SELECT ffvt.flex_value_meaning dept_code
, ffvt.description dept_display
FROM fnd_flex_value_sets ffvs
, fnd_flex_values ffv
, fnd_flex_values_tl ffvt
WHERE ffv.flex_value_set_id = ffvs.flex_value_set_id
AND ffv.flex_value_id = ffvt.flex_value_id
AND ffvs.flex_value_set_name = 'ABC_RETAIL_DEPT'
) dept_map
, per_periods_of_service ppos
WHERE paaf.organization_id = haou.organization_id
AND paaf.job_id = pj.job_id
AND flv.lookup_type = 'xxaa_abc_STORE_CONTROL'
AND flv.enabled_flag = 'Y'
AND flv.lookup_code = SUBSTR(haou.name,2,4)
AND flv.security_group_id = 0
AND flvd.lookup_type(+) = 'xxaa_ABC_DEPT_MAPPING'
AND flvd.enabled_flag(+) = 'Y'
AND flvd.security_group_id(+) = 0
AND flvd.lookup_code(+) = paaf.ass_attribute1
AND dept_map.dept_code (+) = paaf.ass_attribute1
AND paaf.assignment_type = 'E'
AND paaf.primary_flag = 'Y'
AND paaf.ass_attribute30 IN ('A','P')
AND paaf.person_id = p_person_id
AND ppos.person_id = paaf.person_id
--AND ppos.last_update_date BETWEEN NVL(g_last_run_date, SYSDATE) AND NVL(g_curr_run_date, SYSDATE)
/*AND ppos.actual_termination_date IS NOT NULL
AND (ppos.date_start = (SELECT ppos1.date_start
FROM per_periods_of_service ppos1
WHERE ppos1.person_id = paaf.person_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(ppos1.date_start) AND NVL(TRUNC(ppos1.actual_termination_date), TO_DATE('31-Dec-4712')))
OR ppos.date_start = (SELECT ppos1.date_start
FROM per_periods_of_service ppos1
WHERE ppos1.person_id = paaf.person_id
AND TRUNC(ppos1.date_start) >= TRUNC(SYSDATE))
OR ppos.actual_termination_date BETWEEN (TRUNC(SYSDATE)- l_term_upp_limit) AND (TRUNC(SYSDATE)))*/ --V5.1
AND ppos.period_of_service_id = (SELECT MAX(period_of_service_id) FROM per_periods_of_service a WHERE a.person_id = ppos.person_id AND ppos.actual_termination_date IS NOT NULL) --V5.2
AND (TRUNC(paaf.effective_end_date) >= TRUNC(NVL(g_effective_date, SYSDATE))
OR TRUNC(paaf.effective_end_date) BETWEEN (TRUNC(SYSDATE)-l_term_upp_limit) AND (TRUNC(SYSDATE))) --V4.5
GROUP BY paaf.person_id
, flv.lookup_code,flv.attribute1,flv.attribute5,flv.attribute6,flv.attribute7,flv.attribute8,flv.attribute13,flv.attribute9
, dept_map.dept_display,flvd.attribute2
, pj.name
, paaf.effective_start_date
, paaf.job_id
, paaf.location_id
, paaf.ass_attribute1
, ppos.actual_termination_date
, paaf.assignment_status_type_id
ORDER BY MIN(paaf.effective_start_date)
)
WHERE job_delta IS NULL
OR job_delta <> 0
OR loc_delta IS NULL
OR loc_delta <> 0
OR dept_delta IS NULL
OR dept_delta <> 0
OR status_delta IS NULL
OR status_delta <> 0
UNION
--Transfer to non-live
SELECT xxaa_abc_pri_account_typ
( primary_job_account
, primary_labour_account
, PRI_JOB_ACCOUNT_EFFECTIVE_DATE
, TO_CHAR(min_effective_date-1,'DD-MON-YYYY')
, is_back_dated
( person_id
, min_effective_date
, NVL(lead(min_effective_date)over(order by min_effective_date)-1,'31-Dec-4712')
, p_record_id
, 'Account'
, 'TRANSFER_TO_NON_LIVE')
) account_data
FROM (SELECT paaf.person_id person_id
, MIN(paaf.effective_start_date) min_effective_date
, paaf.job_id - lag(paaf.job_id) over(order by paaf.effective_start_date) job_delta
, paaf.location_id - lag(paaf.location_id) over(order by paaf.effective_start_date) loc_delta
, CASE WHEN paaf.ass_attribute1 <> lag(paaf.ass_attribute1) over(order by paaf.effective_start_date)
THEN 1
ELSE 0
END dept_delta
, xket.primary_job_account primary_job_account
, xket.primary_labour_account primary_labour_account
, xket.PRI_JOB_ACCOUNT_EFFECTIVE_DATE PRI_JOB_ACCOUNT_EFFECTIVE_DATE
FROM per_all_assignments_f paaf
, hr_all_organization_units haou
, xxaa_abc_emp_tab xket
WHERE paaf.organization_id = haou.organization_id
AND paaf.assignment_type = 'E'
AND paaf.primary_flag = 'Y'
AND paaf.ass_attribute30 IN ('A','P')
AND paaf.person_id = p_person_id
AND TRUNC(paaf.effective_end_date) >= TRUNC(NVL(g_effective_date, SYSDATE))
AND ( paaf.location_id NOT IN ( SELECT attribute3
FROM fnd_lookup_values flv
WHERE lookup_type = 'xxaa_abc_STORE_CONTROL'
AND attribute2 IS NOT NULL
)
)
AND xket.person_id = paaf.person_id
AND xket.record_id = (SELECT MAX(record_id) FROM xxaa_abc_emp_tab WHERE person_id = paaf.person_id
AND record_id < (SELECT MAX(record_id) FROM xxaa_abc_emp_tab WHERE person_id = paaf.person_id))
GROUP BY paaf.person_id
, paaf.effective_start_date
, paaf.job_id
, paaf.location_id
, paaf.ass_attribute1
, xket.primary_job_account
, xket.primary_labour_account
, xket.PRI_JOB_ACCOUNT_EFFECTIVE_DATE
ORDER BY MIN(paaf.effective_start_date)
)
WHERE job_delta IS NULL
OR job_delta <> 0
OR loc_delta IS NULL
OR loc_delta <> 0
OR dept_delta IS NULL
OR dept_delta <> 0;
BEGIN
g_effective_date := TO_DATE(p_effective_date,'RRRR/MM/DD HH24:MI:SS');
xxaa_abc_util_pkg.write_debug('--------------------------------------------------');
xxaa_abc_util_pkg.write_debug ('Begin pop_calendar_records g_last_run_date - '||g_last_run_date||' g_curr_run_date - '||g_curr_run_date||'g_effective_date '||g_effective_date||'p_person_id - '||p_person_id||' for p_record_id - '||p_record_id||'p_effective_date '||p_effective_date);
l_record_id := p_record_id;
OPEN csr_get_term_limit;
FETCH csr_get_term_limit INTO l_term_upp_limit;
CLOSE csr_get_term_limit;
-- Update Badge Details
OPEN csr_badge_details(p_person_id);
LOOP
FETCH csr_badge_details
BULK COLLECT INTO l_badge_data LIMIT c_limit;
EXIT WHEN l_badge_data.COUNT = 0;
FOR i IN l_badge_data.first .. l_badge_data.last
LOOP
/*IF p_mode = 'Calendar'
THEN
xxaa_abc_util_pkg.write_debug ('V4.3 pop_calendar_records In calendar');
INSERT INTO
TABLE(SELECT xxaa_abc_emp_data_stg.badge_details
FROM xxaa_abc_emp_data_stg
WHERE xxaa_abc_emp_data_stg.person_id = p_person_id)
VALUES(l_badge_data(i).badge_number,l_badge_data(i).effective_date,l_badge_data(i).expiration_date);
xxaa_abc_util_pkg.write_debug ('V4.3 pop_calendar_records after calendar');
ELSE*/
xxaa_abc_util_pkg.write_debug('Insert into BADGE_DETAILS column '||l_badge_data(i).badge_number||l_badge_data(i).effective_date||l_badge_data(i).expiration_date);
INSERT INTO
TABLE(SELECT xxaa_abc_emp_tab.badge_details
FROM xxaa_abc_emp_tab
WHERE xxaa_abc_emp_tab.record_id = l_record_id)
VALUES(l_badge_data(i).badge_number,l_badge_data(i).effective_date,l_badge_data(i).expiration_date);
--END IF;
END LOOP;
END LOOP;
IF p_mode = 'Calendar'
THEN
DELETE FROM TABLE(SELECT xxaa_abc_emp_data_stg.badge_details
FROM xxaa_abc_emp_data_stg
WHERE xxaa_abc_emp_data_stg.person_id = p_person_id)
WHERE badge_number is null;
ELSE
DELETE FROM TABLE(SELECT xxaa_abc_emp_tab.badge_details
FROM xxaa_abc_emp_tab
WHERE xxaa_abc_emp_tab.record_id = l_record_id)
WHERE badge_number is null;
END IF;
-- Employment Status Details
OPEN csr_emp_status_details(p_person_id);
LOOP
FETCH csr_emp_status_details
BULK COLLECT INTO l_status_data LIMIT c_limit;
-- Employment Status Details
EXIT WHEN l_status_data.COUNT = 0;
FOR i IN l_status_data.first .. l_status_data.last
LOOP
/*IF p_mode = 'Calendar'
THEN
INSERT INTO
TABLE(SELECT xxaa_abc_emp_data_stg.employment_details
FROM xxaa_abc_emp_data_stg
WHERE xxaa_abc_emp_data_stg.person_id = p_person_id)
VALUES(l_status_data(i).employment_status,l_status_data(i).effective_date,l_status_data(i).expiration_date,l_status_data(i).back_dated);
ELSE*/
xxaa_abc_util_pkg.write_debug('Insert into EMPLOYMENT_DETAILS column '||l_status_data(i).employment_status||l_status_data(i).effective_date||l_status_data(i).expiration_date||l_status_data(i).back_dated);
INSERT INTO
TABLE(SELECT xxaa_abc_emp_tab.employment_details
FROM xxaa_abc_emp_tab
WHERE xxaa_abc_emp_tab.record_id = l_record_id)
VALUES(l_status_data(i).employment_status,l_status_data(i).effective_date,l_status_data(i).expiration_date,l_status_data(i).back_dated);
--END IF;
END LOOP;
END LOOP;
IF p_mode = 'Calendar'
THEN
DELETE FROM TABLE(SELECT xxaa_abc_emp_data_stg.employment_details
FROM xxaa_abc_emp_data_stg
WHERE xxaa_abc_emp_data_stg.person_id = p_person_id)
WHERE employment_status is null;
ELSE
DELETE FROM TABLE(SELECT xxaa_abc_emp_tab.employment_details
FROM xxaa_abc_emp_tab
WHERE xxaa_abc_emp_tab.record_id = l_record_id)
WHERE employment_status is null;
END IF;
-- Account Details
OPEN csr_account_details(p_person_id);
LOOP
FETCH csr_account_details
BULK COLLECT INTO l_account_data LIMIT c_limit;
EXIT WHEN l_account_data.COUNT = 0;
FOR i IN l_account_data.first .. l_account_data.last
LOOP
/*IF p_mode = 'Calendar'
THEN
INSERT INTO
TABLE(SELECT xxaa_abc_emp_data_stg.account_details
FROM xxaa_abc_emp_data_stg
WHERE xxaa_abc_emp_data_stg.person_id = p_person_id)
VALUES(l_account_data(i).job_account
,l_account_data(i).labour_account
,l_account_data(i).effective_date
,l_account_data(i).expiration_date
,l_account_data(i).back_dated);
ELSE*/
xxaa_abc_util_pkg.write_debug('Insert into ACCOUNT_DETAILS column '||l_account_data(i).job_account||l_account_data(i).labour_account||l_account_data(i).effective_date||l_account_data(i).expiration_date||l_account_data(i).back_dated);
INSERT INTO
TABLE(SELECT xxaa_abc_emp_tab.account_details
FROM xxaa_abc_emp_tab
WHERE xxaa_abc_emp_tab.record_id = l_record_id)
VALUES(l_account_data(i).job_account
,l_account_data(i).labour_account
,l_account_data(i).effective_date
,l_account_data(i).expiration_date
,l_account_data(i).back_dated);
--END IF;
END LOOP;
END LOOP;
IF p_mode = 'Calendar'
THEN
DELETE FROM TABLE(SELECT xxaa_abc_emp_data_stg.account_details
FROM xxaa_abc_emp_data_stg
WHERE xxaa_abc_emp_data_stg.person_id = p_person_id)
WHERE job_account IS NULL;
ELSE
DELETE FROM TABLE(SELECT xxaa_abc_emp_tab.account_details
FROM xxaa_abc_emp_tab
WHERE xxaa_abc_emp_tab.record_id = l_record_id)
WHERE job_account IS NULL;
END IF;
xxaa_abc_util_pkg.write_debug('--------------------------------------------------');
EXCEPTION
WHEN OTHERS THEN
xxaa_abc_util_pkg.write_log ( 'Exception - pop_calendar_records - '||SQLERRM);
END pop_calendar_records;
-- -----------------------------------------------------------------------------
-- V4.3 Function to check if record has been processed in current run
-- -----------------------------------------------------------------------------
FUNCTION is_record_processed
(p_person_id NUMBER
,p_mode VARCHAR2)
RETURN VARCHAR2
IS
-- Local Variables
l_curr_run_processed VARCHAR2(1) := 'N';
-- Cursor to check if record has been created for employee in current run
CURSOR csr_curr_run_rec(p_person_id NUMBER)
IS
SELECT 'Y'
FROM xxaa_abc_emp_tab xket
WHERE xket.person_id = p_person_id
AND xket.run_number = g_run_number;
-- Cursor to check if record has been created for employee in current run
CURSOR csr_stg_run_rec(p_person_id NUMBER)
IS
SELECT 'Y'
FROM xxaa_abc_emp_data_stg stg
WHERE stg.person_id = p_person_id;
BEGIN
l_curr_run_processed := 'N';
IF p_mode = 'Normal'
THEN
-- Check if person has already been processed in current run
OPEN csr_curr_run_rec(p_person_id);
FETCH csr_curr_run_rec INTO l_curr_run_processed;
CLOSE csr_curr_run_rec;
ELSE
-- Check if person has already been processed in staging table
OPEN csr_stg_run_rec(p_person_id);
FETCH csr_stg_run_rec INTO l_curr_run_processed;
CLOSE csr_stg_run_rec;
END IF;
RETURN l_curr_run_processed;
EXCEPTION
WHEN OTHERS THEN
xxaa_abc_util_pkg.write_log ( 'Exception - is_record_processed - '||SQLERRM);
END is_record_processed;
-- -----------------------------------------------------------------------------
-- V4.3 Procedure to check for future or past changes
-- -----------------------------------------------------------------------------
PROCEDURE pop_past_future_change
(p_person_id NUMBER
,p_store_id NUMBER
,p_thread_id NUMBER
,p_last_run_date DATE
,p_curr_run_date DATE
)
IS
-- Local Variables
l_person_id NUMBER;
l_is_record_processed VARCHAR2(1);
l_rec_count NUMBER;
l_rtc NUMBER;
l_rowcount NUMBER;
l_thread_id NUMBER := 1;
l_badge_number VARCHAR2(50);
l_badge_eff_date VARCHAR2(50);
l_emp_status_eff_date VARCHAR2(50);
l_emp_status VARCHAR2(100);
l_pri_lab_date VARCHAR2(50);
l_pri_lab_acnt VARCHAR2(100);
l_pri_job_date VARCHAR2(100);
l_pri_job_acnt VARCHAR2(1000);
l_record_type VARCHAR2(50);
-- Cursor to check if a past or future change has taken place for a calendar entity
CURSOR csr_past_fut_chg
IS
--Future Initial for a store which go-live today (Initial records having future events)
SELECT papf.person_id, 'INITIAL' record_type -- V4.5 Removed Distinct
FROM per_all_people_f papf
, per_all_assignments_f paaf
, per_periods_of_service ppos
, pay_people_groups ppg
, hr_all_organization_units haou
, fnd_lookup_values flv
WHERE 1=1
AND papf.person_id = paaf.person_id
AND papf.current_employee_flag = 'Y'
AND paaf.assignment_type = 'E'
AND paaf.primary_flag = 'Y'
AND paaf.ass_attribute30 IN ('A','P')
--AND g_effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.person_id = ppos.person_id
AND ppos.date_start = (SELECT ppos1.date_start
FROM per_periods_of_service ppos1
WHERE ppos1.person_id = papf.person_id
AND g_effective_date BETWEEN ppos1.date_start
AND NVL(ppos1.actual_termination_date
, TO_DATE('31-Dec-4712'))
)
AND ppg.segment3 IN ('Retail 4 Weekly','Retail Salaried')
AND paaf.people_group_id = ppg.people_group_id
AND paaf.organization_id = haou.organization_id
AND flv.lookup_type = 'xxaa_abc_STORE_CONTROL'
AND flv.enabled_flag = 'Y'
AND flv.lookup_code = SUBSTR(haou.name,2,4)
AND flv.attribute2 IS NOT NULL
AND flv.security_group_id = 0
AND TO_DATE(flv.attribute2, 'RRRR/MM/DD HH24:MI:SS') <= g_effective_date
AND flv.lookup_code = NVL(TO_CHAR(p_store_id), flv.lookup_code)
AND paaf.person_id = NVL(p_person_id, paaf.person_id)
AND NOT EXISTS ( SELECT 'Y'
FROM xxaa_abc_emp_header_tab xkeiht
WHERE 1=1
--AND xkeiht.store_id = SUBSTR(haou.name,2,4)
AND xkeiht.store_id = flv.lookup_code
)
AND (paaf.effective_start_date > TRUNC(g_effective_date) AND papf.effective_start_date > TRUNC(g_effective_date)
AND TRUNC(flv.last_update_date) BETWEEN p_last_run_date AND p_curr_run_date)
UNION ALL --V4.5
-- Future Incremental
SELECT papf.person_id, 'INCREMENTAL' record_type -- V4.5 Removed Distinct
FROM per_all_people_f papf
, per_all_assignments_f paaf
, per_periods_of_service ppos
, pay_people_groups ppg
, hr_all_organization_units haou
, fnd_lookup_values flv
WHERE 1=1
AND papf.person_id = paaf.person_id
AND papf.current_employee_flag = 'Y'
AND paaf.assignment_type = 'E'
AND paaf.primary_flag = 'Y'
AND paaf.ass_attribute30 IN ('A','P')
AND g_effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.person_id = ppos.person_id
AND ppos.date_start = (SELECT ppos1.date_start
FROM per_periods_of_service ppos1
WHERE ppos1.person_id = papf.person_id
AND g_effective_date BETWEEN ppos1.date_start
AND NVL(ppos1.actual_termination_date
, TO_DATE('31-Dec-4712'))
)
AND ppos.actual_termination_date IS NULL
AND ppg.segment3 IN ('Retail 4 Weekly','Retail Salaried')
AND paaf.people_group_id = ppg.people_group_id
AND paaf.organization_id = haou.organization_id
AND flv.lookup_type = 'xxaa_abc_STORE_CONTROL'
AND flv.enabled_flag = 'Y'
AND flv.lookup_code = SUBSTR(haou.name,2,4)
AND flv.attribute2 IS NOT NULL
AND flv.security_group_id = 0
AND TO_DATE(flv.attribute2, 'RRRR/MM/DD HH24:MI:SS') <= g_effective_date
AND flv.lookup_code = NVL(TO_CHAR(p_store_id), flv.lookup_code)
AND paaf.person_id = NVL(p_person_id, paaf.person_id)
AND EXISTS ( SELECT 'Y'
FROM xxaa_abc_emp_header_tab xkeiht
WHERE 1=1
--AND xkeiht.store_id = SUBSTR(haou.name,2,4)
AND xkeiht.store_id = flv.lookup_code
)
AND (paaf.effective_start_date > TRUNC(g_effective_date)
AND EXISTS (SELECT 'Y' from per_all_assignments_f paaf1
WHERE paaf1.person_id = paaf.person_id
AND paaf1.last_update_date BETWEEN p_last_run_date AND p_curr_run_date)
)
UNION ALL -- V4.5
-- Future Termination
SELECT xket.person_id, 'TERMINATION' record_type -- V4.5 Removed Distinct
FROM per_periods_of_service ppos
, xxaa_abc_emp_tab xket
WHERE 1=1
AND ppos.actual_termination_date > g_effective_date
AND ppos.last_update_date BETWEEN p_last_run_date AND p_curr_run_date
AND xket.person_id = ppos.person_id
AND ppos.person_id = NVL(p_person_id, ppos.person_id)
AND ppos.actual_termination_date IS NOT NULL
AND xket.record_id = ( SELECT MAX(record_id)
FROM xxaa_abc_emp_tab
WHERE person_id = ppos.person_id)
UNION ALL -- V4.5
-- Future transfer to Non-Live store
SELECT papf.person_id, 'NON_LIVE' record_type -- V4.5 Removed Distinct
FROM per_all_people_f papf
, per_all_assignments_f paaf
, per_periods_of_service ppos
, hr_all_organization_units haou
, xxaa_abc_emp_tab xket
WHERE 1 = 1
AND papf.person_id = paaf.person_id
AND papf.person_id = ppos.person_id
AND paaf.organization_id = haou.organization_id
AND paaf.person_id = xket.person_id
AND ( paaf.location_id NOT IN ( SELECT attribute3
FROM fnd_lookup_values flv
WHERE lookup_type = 'xxaa_abc_STORE_CONTROL'
AND attribute2 IS NOT NULL
)
)
AND papf.current_employee_flag = 'Y'
AND paaf.assignment_type = 'E'
AND paaf.ass_attribute30 = 'A'
AND paaf.primary_flag = 'Y'
AND xket.attribute1 IS NULL -- 'TRANSFERRED'
AND xket.record_id = ( SELECT MAX(record_id)
FROM xxaa_abc_emp_tab
WHERE person_id = paaf.person_id
)
AND paaf.person_id = NVL(p_person_id, paaf.person_id)
AND ppos.date_start = ( SELECT ppos1.date_start
FROM per_periods_of_service ppos1
WHERE ppos1.person_id = papf.person_id
AND g_effective_date BETWEEN ppos1.date_start AND NVL ( ppos1.actual_termination_date,TO_DATE ('31-Dec-4712'))
)
AND g_effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
AND paaf.effective_start_date > TRUNC(g_effective_date)
AND SUBSTR (haou.name, 2, 4) = NVL (TO_CHAR (p_store_id), SUBSTR (haou.name, 2, 4))
AND ( papf.last_update_date BETWEEN p_last_run_date AND p_curr_run_date
OR paaf.last_update_date BETWEEN p_last_run_date AND p_curr_run_date);
-- Cursor to get details to populate into staging table
CURSOR csr_get_fp_emp_details(p_person_id NUMBER)
IS
/*SELECT *
FROM xxaa_abc_emp_tab
WHERE record_id = ( SELECT MAX(record_id)
FROM xxaa_abc_emp_tab
WHERE person_id = p_person_id
);*/
SELECT xket.person_id
, xket.assignment_id
, xket.employee_number
, xket.ni_number
, xket.title
, xket.first_name
, xket.last_name
, TO_CHAR(xket.date_of_birth, 'DD-MON-YYYY') date_of_birth --V5.5
, xket.age
, xket.payroll_number
, xket.organization
, xket.location
, xket.job
, xket.grade
, xket.normal_hours
, xket.trade_union_shop_steward
, xket.date_start date_start
, TO_NUMBER(xket.length_of_service) length_of_service
, xket.addr_line_1
, xket.addr_line_2
, xket.addr_line_3
, xket.city
, xket.county
, xket.postal_code
, xket.country
, xket.address_date_from
, xket.home_phone_number
, xket.home_phone_date_from
, xket.mobile_phone_number
, xket.mobile_phone_date_from
, xket.email_address
, xket.store_number
, xket.workforce_timekeeper_license
, xket.middle_initial
, past.user_status employment_status
, paaf.effective_start_date empl_status_effective_date
, xket.biometric_employee_flag
, xket.channel
, xket.zone
, xket.region
, xket.area
, xket.store
, xket.department
, xket.job_code job_code
, flv.attribute1
|| '/'
|| flv.attribute5
|| '/'
|| flv.attribute6
|| '/'
|| flv.attribute7
|| '/'
|| flv.attribute8
|| '/'
|| DECODE (flv.attribute9, 'Convenience', 'Store', 'Small Retail',NVL(FLVD.ATTRIBUTE2,DEPT_MAP.DEPT_DISPLAY),'Retail',DEPT_MAP.DEPT_DISPLAY) --V4.1
|| '/'
|| SUBSTR (pj.name, 2, 4) primary_labour_account
, paaf.effective_start_date pri_lab_account_effective_date
, xket.device_group
, DECODE( NVL(paaf.ass_attribute6, 'NA'), 'NA', NULL , SUBSTR(haou.name,2,4)||paaf.ass_attribute6) BADGE_NUMBER
, DECODE(l_record_type, 'NON_LIVE', xket.badge_number_effective_date,
DECODE( NVL(paaf.ass_attribute6, 'NA'), 'NA', NULL, TO_CHAR(paaf.effective_start_date, 'DD-MON-YYYY')|| ' 00:00')) badge_number_effective_date
, xket.pay_rule
, NULL live_date
, TO_DATE(xket.role_hire_date) role_hire_date
, xket.clock_indicator
, xket.store_type
, 'INCREMENTAL' type
, NULL attribute1
, xket.employment_expiration_date employment_expiration_date
, xket.badge_expiration_date badge_expiration_date
, xket.labour_account_expiration_date labour_account_expiration_date
, xket.pay_rule_effective_date pay_rule_effective_date --V4.2
, xket.job_acc_segment7 job_acc_segment7 --V2.3
, xket.job_name job_name --V2.3
, TO_CHAR('01-JAN-3000') pri_job_account_exp_date --V2.3
, 'Sample'
|| '/'
|| 'Retail'
|| '/'
|| flv.attribute5
|| '/'
|| flv.attribute6
|| '/'
|| flv.attribute7
|| '/'
|| flv.attribute1
|| '/'
|| flv.attribute13
|| '/'
||DECODE(FLV.ATTRIBUTE9, 'Retail',DEPT_MAP.DEPT_DISPLAY,'Convenience', 'Store',
'Small Retail',NVL(FLVD.ATTRIBUTE2,DEPT_MAP.DEPT_DISPLAY)) --V4.1
|| '/'
|| SUBSTR (pj.name,INSTR (pj.name, '|') + 1,INSTR (SUBSTR (pj.name, INSTR (pj.name, '|') + 1), '|', -1) - 1) primary_job_account
, TO_CHAR ((paaf.effective_start_date), 'DD-MON-YYYY') pri_job_account_effective_date
, xket.worker_type worker_type
, xket.right_to_work_type right_to_work_type --V4.2
, xket.curr_store_cps_config curr_store_cps_config --V4.2
, xket.curr_store_cps_config old_store_cps_config --V4.8
FROM per_all_people_f papf
, per_all_assignments_f paaf
, per_assignment_status_types past
, hr_all_organization_units haou
, per_jobs pj
, fnd_lookup_values flv
, fnd_lookup_values flvd
,(SELECT ffvt.flex_value_meaning dept_code
, ffvt.description dept_display
FROM fnd_flex_value_sets ffvs
, fnd_flex_values ffv
, fnd_flex_values_tl ffvt
WHERE ffv.flex_value_set_id = ffvs.flex_value_set_id
AND ffv.flex_value_id = ffvt.flex_value_id
AND ffvs.flex_value_set_name = 'ABC_RETAIL_DEPT'
) dept_map --V4.1
, xxaa_rtw_audit xra --V4.2
, xxaa_abc_emp_tab xket
WHERE 1=1
AND papf.person_id = paaf.person_id
AND paaf.person_id = p_person_id
--AND record_type = p_record_type
AND papf.current_employee_flag = 'Y'
AND paaf.assignment_type = 'E'
AND paaf.primary_flag = 'Y'
AND paaf.ass_attribute30 IN ('A','P') --V3.6
AND g_effective_date between papf.effective_start_date and papf.effective_end_date
AND past.assignment_status_type_id = paaf.assignment_status_type_id
AND paaf.organization_id = haou.organization_id
AND paaf.job_id = pj.job_id
AND flv.lookup_type = 'xxaa_abc_STORE_CONTROL'
AND flv.enabled_flag = 'Y'
AND flv.lookup_code = SUBSTR(haou.name,2,4)
AND flv.security_group_id = 0
AND flvd.lookup_type(+) = 'xxaa_ABC_DEPT_MAPPING' --V4.1
AND flvd.enabled_flag(+) = 'Y'
AND flvd.security_group_id(+) = 0
AND flvd.lookup_code(+) = paaf.ass_attribute1
AND dept_map.dept_code (+) = paaf.ass_attribute1 --V4.1
AND xra.person_id(+) = papf.person_id --V4.2
AND NVL(xra.version_number, 1) = NVL((SELECT MAX(version_number) FROM xxaa_rtw_audit WHERE person_id = xra.person_id), 1)
AND xket.person_id = papf.person_id
AND xket.record_id = ( SELECT MAX(record_id)
FROM xxaa_abc_emp_tab
WHERE person_id = papf.person_id
)
AND paaf.effective_start_date > TRUNC(g_effective_date)
ORDER BY paaf.effective_start_date;
-- Type Variables
TYPE fp_emp_details_tbl_type IS TABLE OF csr_get_fp_emp_details%ROWTYPE;
l_fp_emp_details_tab fp_emp_details_tbl_type := fp_emp_details_tbl_type();
TYPE recon_emp_tbl_type IS TABLE OF xxaa_abc_emp_data_stg%ROWTYPE;
l_recon_emp_tab recon_emp_tbl_type := recon_emp_tbl_type();
l_recon_emp_live_tab recon_emp_tbl_type := recon_emp_tbl_type();
CURSOR csr_get_eff_date( l_person_id NUMBER
-- , l_location VARCHAR2
)
IS
SELECT badge_number
, badge_number_effective_date
, empl_status_effective_date
, employment_status
, pri_lab_account_effective_date
, primary_labour_account
, pri_job_account_effective_date
, primary_job_account
FROM xxaa_abc_emp_tab
WHERE record_id = ( SELECT MAX(record_id)
FROM xxaa_abc_emp_tab
WHERE person_id = l_person_id
--AND location = l_location
AND attribute1 IS NULL
);
l_badge_exp_date VARCHAR2(50);
l_term_exp_date VARCHAR2(50);
--Cursor to get dates for termination
CURSOR csr_term(p_person_id NUMBER)
IS
SELECT --TO_CHAR(ppos.actual_termination_date, 'DD-MON-YYYY') || ' 23:59' expiration_date
DECODE( NVL(paaf.ass_attribute6, 'NA'), 'NA', NULL , TO_CHAR(ppos.actual_termination_date, 'DD-MON-YYYY') || ' 23:59') expiration_date -- V5.4
, TO_CHAR(ppos.actual_termination_date, 'DD-MON-YYYY') || ' 23:59' expiration_date
FROM per_periods_of_service ppos, per_all_assignments_f paaf
WHERE 1=1
AND ppos.person_id = p_person_id
AND paaf.person_id = ppos.person_id
AND ppos.date_start = (SELECT ppos1.date_start
FROM per_periods_of_service ppos1
WHERE ppos1.person_id = paaf.person_id
AND g_effective_date BETWEEN ppos1.date_start AND NVL(ppos1.actual_termination_date, TO_DATE('31-Dec-4712'))
)
AND paaf.person_id = ppos.person_id
AND paaf.assignment_type = 'E'
AND paaf.primary_flag = 'Y'
AND paaf.ass_attribute30 IN ('A','P')
AND g_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
ORDER BY ppos.period_of_service_id DESC;
l_nonlive_date VARCHAR2(50);
-- Cursor to get dates for transfer to non-live
CURSOR csr_non_live(p_person_id NUMBER)
IS
SELECT (TO_CHAR(paaf.effective_start_date-1, 'DD-MON-YYYY')||' 23:59')
FROM per_all_assignments_f paaf WHERE person_id = p_person_id
AND ( paaf.location_id NOT IN ( SELECT attribute3
FROM fnd_lookup_values flv
WHERE lookup_type = 'xxaa_abc_STORE_CONTROL'
AND attribute2 IS NOT NULL
)
)
ORDER BY paaf.effective_start_date DESC;
BEGIN
xxaa_abc_util_pkg.write_debug ('Begin pop_past_future_change '||'; p_last_run_date - '||p_last_run_date||'; p_curr_run_date - '||p_curr_run_date);
-- Get records with past or future change
FOR rec_past_fut_chg IN csr_past_fut_chg
LOOP
xxaa_abc_util_pkg.write_log ( 'Calendar event exists for person_id - '||rec_past_fut_chg.person_id||' in '||rec_past_fut_chg.record_type);
l_record_type := rec_past_fut_chg.record_type;
OPEN csr_term(rec_past_fut_chg.person_id);
FETCH csr_term INTO l_badge_exp_date, l_term_exp_date;
CLOSE csr_term;
OPEN csr_non_live(rec_past_fut_chg.person_id);
FETCH csr_non_live INTO l_nonlive_date;
CLOSE csr_non_live;
-- Create row for record in staging table
OPEN csr_get_fp_emp_details(rec_past_fut_chg.person_id);
LOOP
l_fp_emp_details_tab.DELETE;
l_rec_count := 0;
l_rtc := 0;
FETCH csr_get_fp_emp_details BULK COLLECT INTO l_fp_emp_details_tab;-- LIMIT g_bulk_limit;
l_rowcount := csr_get_fp_emp_details%ROWCOUNT;
l_thread_id := p_thread_id;
FOR i IN 1..l_fp_emp_details_tab.COUNT
LOOP
l_recon_emp_tab.EXTEND;
l_rtc := l_rtc + 1;
l_rec_count := l_rec_count + 1;
--Cursor to fetch values from staging table
OPEN csr_get_eff_date(l_fp_emp_details_tab(i).person_id);
FETCH csr_get_eff_date INTO l_badge_number, l_badge_eff_date, l_emp_status_eff_date, l_emp_status
, l_pri_lab_date, l_pri_lab_acnt, l_pri_job_date, l_pri_job_acnt;
CLOSE csr_get_eff_date;
l_recon_emp_tab(l_rtc).person_id := l_fp_emp_details_tab(i).person_id;
l_recon_emp_tab(l_rtc).assignment_id := l_fp_emp_details_tab(i).assignment_id;
l_recon_emp_tab(l_rtc).thread_id := l_thread_id;
l_recon_emp_tab(l_rtc).employee_number := l_fp_emp_details_tab(i).employee_number;
l_recon_emp_tab(l_rtc).ni_number := l_fp_emp_details_tab(i).ni_number;
l_recon_emp_tab(l_rtc).title := l_fp_emp_details_tab(i).title;
l_recon_emp_tab(l_rtc).first_name := l_fp_emp_details_tab(i).first_name;
l_recon_emp_tab(l_rtc).last_name := l_fp_emp_details_tab(i).last_name;
l_recon_emp_tab(l_rtc).date_of_birth := l_fp_emp_details_tab(i).date_of_birth;
l_recon_emp_tab(l_rtc).age := l_fp_emp_details_tab(i).age;
l_recon_emp_tab(l_rtc).payroll_number := l_fp_emp_details_tab(i).payroll_number;
l_recon_emp_tab(l_rtc).organization := l_fp_emp_details_tab(i).organization;
l_recon_emp_tab(l_rtc).location := l_fp_emp_details_tab(i).location;
l_recon_emp_tab(l_rtc).job := l_fp_emp_details_tab(i).job;
l_recon_emp_tab(l_rtc).grade := l_fp_emp_details_tab(i).grade;
l_recon_emp_tab(l_rtc).normal_hours := l_fp_emp_details_tab(i).normal_hours;
l_recon_emp_tab(l_rtc).trade_union_shop_steward := l_fp_emp_details_tab(i).trade_union_shop_steward;
l_recon_emp_tab(l_rtc).date_start := l_fp_emp_details_tab(i).date_start;
l_recon_emp_tab(l_rtc).length_of_service := l_fp_emp_details_tab(i).length_of_service;
l_recon_emp_tab(l_rtc).addr_line_1 := l_fp_emp_details_tab(i).addr_line_1;
l_recon_emp_tab(l_rtc).addr_line_2 := l_fp_emp_details_tab(i).addr_line_2;
l_recon_emp_tab(l_rtc).addr_line_3 := l_fp_emp_details_tab(i).addr_line_3;
l_recon_emp_tab(l_rtc).city := l_fp_emp_details_tab(i).city;
l_recon_emp_tab(l_rtc).county := l_fp_emp_details_tab(i).county;
l_recon_emp_tab(l_rtc).postal_code := l_fp_emp_details_tab(i).postal_code;
l_recon_emp_tab(l_rtc).country := l_fp_emp_details_tab(i).country;
l_recon_emp_tab(l_rtc).address_date_from := l_fp_emp_details_tab(i).address_date_from;
l_recon_emp_tab(l_rtc).home_phone_number := l_fp_emp_details_tab(i).home_phone_number;
l_recon_emp_tab(l_rtc).home_phone_date_from := l_fp_emp_details_tab(i).home_phone_date_from;
l_recon_emp_tab(l_rtc).mobile_phone_number := l_fp_emp_details_tab(i).mobile_phone_number;
l_recon_emp_tab(l_rtc).mobile_phone_date_from := l_fp_emp_details_tab(i).mobile_phone_date_from;
l_recon_emp_tab(l_rtc).email_address := l_fp_emp_details_tab(i).email_address;
--BANK
l_recon_emp_tab(l_rtc).store_number := l_fp_emp_details_tab(i).store_number;
l_recon_emp_tab(l_rtc).workforce_timekeeper_license := l_fp_emp_details_tab(i).workforce_timekeeper_license;
l_recon_emp_tab(l_rtc).middle_initial := l_fp_emp_details_tab(i).middle_initial;
l_recon_emp_tab(l_rtc).biometric_employee_flag := l_fp_emp_details_tab(i).biometric_employee_flag;
l_recon_emp_tab(l_rtc).channel := l_fp_emp_details_tab(i).channel;
l_recon_emp_tab(l_rtc).zone := l_fp_emp_details_tab(i).zone;
l_recon_emp_tab(l_rtc).region := l_fp_emp_details_tab(i).region;
l_recon_emp_tab(l_rtc).area := l_fp_emp_details_tab(i).area;
l_recon_emp_tab(l_rtc).store := l_fp_emp_details_tab(i).store;
l_recon_emp_tab(l_rtc).department := l_fp_emp_details_tab(i).department;
l_recon_emp_tab(l_rtc).job_code := l_fp_emp_details_tab(i).job_code;
l_recon_emp_tab(l_rtc).device_group := l_fp_emp_details_tab(i).device_group;
l_recon_emp_tab(l_rtc).pay_rule := l_fp_emp_details_tab(i).pay_rule;
--l_recon_emp_tab(l_rtc).live_date := l_fp_emp_details_tab(i).live_date;
l_recon_emp_tab(l_rtc).run_number := g_run_number;
l_recon_emp_tab(l_rtc).role_hire_date := l_fp_emp_details_tab(i).role_hire_date;
l_recon_emp_tab(l_rtc).clock_indicator := l_fp_emp_details_tab(i).clock_indicator;
l_recon_emp_tab(l_rtc).store_type := l_fp_emp_details_tab(i).store_type;
l_recon_emp_tab(l_rtc).attribute1 := 'Calendar';
l_recon_emp_tab(l_rtc).attribute2 := l_fp_emp_details_tab(i).attribute1;
l_recon_emp_tab(l_rtc).attribute3 := 'INCREMENTAL';--l_fp_emp_details_tab(i).attribute3;
l_recon_emp_tab(l_rtc).creation_date := SYSDATE;
l_recon_emp_tab(l_rtc).last_updated_date := SYSDATE;
l_recon_emp_tab(l_rtc).request_id := g_request_id;
l_recon_emp_tab(l_rtc).employment_expiration_date := l_fp_emp_details_tab(i).employment_expiration_date;
l_recon_emp_tab(l_rtc).badge_expiration_date := l_fp_emp_details_tab(i).badge_expiration_date;
l_recon_emp_tab(l_rtc).labour_account_expiration_date := l_fp_emp_details_tab(i).labour_account_expiration_date;
l_recon_emp_tab(l_rtc).pay_rule_effective_date := l_fp_emp_details_tab(i).pay_rule_effective_date;
l_recon_emp_tab(l_rtc).job_acc_segment7 := l_fp_emp_details_tab(i).job_acc_segment7;
l_recon_emp_tab(l_rtc).job_name := l_fp_emp_details_tab(i).job_name;
l_recon_emp_tab(l_rtc).pri_job_account_exp_date := l_fp_emp_details_tab(i).pri_job_account_exp_date;
l_recon_emp_tab(l_rtc).worker_type := l_fp_emp_details_tab(i).worker_type;
l_recon_emp_tab(l_rtc).right_to_work_type := l_fp_emp_details_tab(i).right_to_work_type;
l_recon_emp_tab(l_rtc).curr_store_cps_config := l_fp_emp_details_tab(i).curr_store_cps_config;
l_recon_emp_tab(l_rtc).old_store_cps_config := l_fp_emp_details_tab(i).old_store_cps_config;
l_recon_emp_tab(l_rtc).last_run_date := p_last_run_date;
l_recon_emp_tab(l_rtc).curr_run_date := p_curr_run_date;
l_recon_emp_tab(l_rtc).future_use9 := '5' ; -- V4.5
l_recon_emp_tab(l_rtc).future_use10 := 'FUTURE';
l_recon_emp_tab(l_rtc).badge_details := xxaa_abc_badges_tab(xxaa_abc_badges_typ(null,null,null));
l_recon_emp_tab(l_rtc).employment_details := xxaa_abc_status_tab(xxaa_abc_status_typ(null,null,null,null));
l_recon_emp_tab(l_rtc).account_details := xxaa_abc_pri_account_tab(xxaa_abc_pri_account_typ(null,null,null,null,null));
IF l_record_type = 'TERMINATION'
THEN l_recon_emp_tab(l_rtc).badge_expiration_date := l_badge_exp_date; --V5.4
l_recon_emp_tab(l_rtc).pri_job_account_exp_date := l_term_exp_date;
l_recon_emp_tab(l_rtc).labour_account_expiration_date := l_term_exp_date;
--l_recon_emp_tab(l_rtc).employment_status := 'Terminate Assignment';
l_fp_emp_details_tab(i).employment_status := 'Terminate Assignment';
END IF;
IF l_record_type = 'NON_LIVE'
THEN l_recon_emp_tab(l_rtc).badge_expiration_date := l_nonlive_date;
l_recon_emp_tab(l_rtc).pri_job_account_exp_date := l_nonlive_date;
l_recon_emp_tab(l_rtc).labour_account_expiration_date := l_nonlive_date;
--l_recon_emp_tab(l_rtc).employment_status := 'Terminate Assignment';--V5.4
l_fp_emp_details_tab(i).employment_status := 'Terminate Assignment'; --V5.4
END IF;
--To verify change in previous value with current value
IF l_emp_status <> l_fp_emp_details_tab(i).employment_status
THEN
l_recon_emp_tab(l_rtc).employment_status := l_fp_emp_details_tab(i).employment_status;
l_recon_emp_tab(l_rtc).empl_status_effective_date := l_fp_emp_details_tab(i).empl_status_effective_date;
ELSE
l_recon_emp_tab(l_rtc).employment_status := l_emp_status;
l_recon_emp_tab(l_rtc).empl_status_effective_date := l_emp_status_eff_date;
END IF;
IF NVL(l_badge_number,'NA') <> NVL(l_fp_emp_details_tab(i).badge_number,'NA') --V4.9
THEN
l_recon_emp_tab(l_rtc).badge_number := l_fp_emp_details_tab(i).badge_number;
l_recon_emp_tab(l_rtc).badge_number_effective_date := l_fp_emp_details_tab(i).badge_number_effective_date;
ELSE
l_recon_emp_tab(l_rtc).badge_number := l_badge_number;
l_recon_emp_tab(l_rtc).badge_number_effective_date := l_badge_eff_date;
END IF;
IF l_pri_lab_acnt <> l_fp_emp_details_tab(i).primary_labour_account
THEN
l_recon_emp_tab(l_rtc).primary_labour_account := l_fp_emp_details_tab(i).primary_labour_account;
l_recon_emp_tab(l_rtc).pri_lab_account_effective_date := l_fp_emp_details_tab(i).pri_lab_account_effective_date;
ELSE
l_recon_emp_tab(l_rtc).primary_labour_account := l_pri_lab_acnt;
l_recon_emp_tab(l_rtc).pri_lab_account_effective_date := l_pri_lab_date;
END IF;
IF l_pri_job_acnt <> l_fp_emp_details_tab(i).primary_job_account
THEN
l_recon_emp_tab(l_rtc).primary_job_account := l_fp_emp_details_tab(i).primary_job_account;
l_recon_emp_tab(l_rtc).pri_job_account_effective_date := l_fp_emp_details_tab(i).pri_job_account_effective_date;
ELSE
l_recon_emp_tab(l_rtc).primary_job_account := l_pri_job_acnt;
l_recon_emp_tab(l_rtc).pri_job_account_effective_date := l_pri_job_date;
END IF;
xxaa_abc_util_pkg.write_log('future_badge_number '|| l_fp_emp_details_tab(i).badge_number);
xxaa_abc_util_pkg.write_log('future_badge_number_effective_date '|| l_fp_emp_details_tab(i).badge_number_effective_date);
xxaa_abc_util_pkg.write_log('future_primary_labour_account '|| l_fp_emp_details_tab(i).primary_labour_account);
xxaa_abc_util_pkg.write_log('future_pri_lab_account_effective_date '|| l_fp_emp_details_tab(i).pri_lab_account_effective_date);
xxaa_abc_util_pkg.write_log('future_primary_job_account '|| l_fp_emp_details_tab(i).primary_job_account);
xxaa_abc_util_pkg.write_log('future_pri_job_account_effective_date '|| l_fp_emp_details_tab(i).pri_job_account_effective_date);
END LOOP;
FORALL i IN 1..l_recon_emp_tab.COUNT
INSERT INTO xxaa_abc_emp_data_stg VALUES l_recon_emp_tab(i);
COMMIT;
l_rtc := 0;
l_recon_emp_tab.DELETE;
EXIT WHEN l_fp_emp_details_tab.COUNT < g_bulk_limit;
END LOOP;
CLOSE csr_get_fp_emp_details;
END LOOP; -- Future Past Changes Colleagues
COMMIT;
EXCEPTION
WHEN OTHERS THEN
xxaa_abc_util_pkg.write_log ( 'Exception - pop_past_future_change - '||SQLERRM);
END pop_past_future_change;
-- -----------------------------------------------------------------------------
-- V4.3 Function to compare calendar columns
-- -----------------------------------------------------------------------------
FUNCTION compare_cal_columns
(p_person_id NUMBER)
RETURN VARCHAR2
IS
-- Local Variables
l_cal_chg_col VARCHAR2(100);
-- Cursor to compare calendar status
CURSOR csr_compare_calendar(p_person_id NUMBER)
IS
SELECT CASE WHEN emp.badge_details <> stg.badge_details
THEN 'BADGE_NUMBER|'
END
|| CASE WHEN emp.employment_details <> stg.employment_details THEN 'EMPLOYMENT_STATUS|' END
|| CASE WHEN emp.account_details <> stg.account_details THEN 'PRIMARY_ACCOUNT|' END
change_col
FROM xxaa_abc_emp_tab emp
, xxaa_abc_emp_data_stg stg
WHERE emp.person_id = stg.person_id
AND emp.record_id = (SELECT MAX(record_id)
FROM xxaa_abc_emp_tab emp1
WHERE emp1.person_id = p_person_id
)
AND stg.attribute1 = 'Calendar'
AND ( emp.badge_details <> stg.badge_details
OR emp.account_details <> stg.account_details
OR emp.employment_details <> stg.employment_details
);
BEGIN
-- Compare calendar column
xxaa_abc_util_pkg.write_debug ('V4.3 compare_cal_columns '||p_person_id);
OPEN csr_compare_calendar(p_person_id);
FETCH csr_compare_calendar INTO l_cal_chg_col;
CLOSE csr_compare_calendar;
xxaa_abc_util_pkg.write_debug ('V4.3 compare_cal_columns l_cal_chg_col - '||l_cal_chg_col);
RETURN l_cal_chg_col;
END compare_cal_columns;
-- -----------------------------------------------------------------------------
-- V4.3 Function to compare calendar columns
-- -----------------------------------------------------------------------------
FUNCTION mod_cal_columns
(p_chg_columns VARCHAR2)
RETURN VARCHAR2
IS
-- Local Variables
l_mod_chg_column VARCHAR2(2000);
l_badge_column VARCHAR2(2000);
l_status_cnt NUMBER;
l_badge_cnt NUMBER;
l_account_cnt NUMBER;
BEGIN
l_mod_chg_column := REPLACE(p_chg_columns,'|','~');
l_status_cnt := NULL;
l_badge_cnt := NULL;
l_account_cnt := NULL;
xxaa_abc_util_pkg.write_debug ('Begin of mod_cal_columns - '||l_mod_chg_column);
IF p_chg_columns LIKE '%EMPLOYMENT_STATUS%'
THEN
l_mod_chg_column := REPLACE(l_mod_chg_column,'EMPLOYMENT_STATUS','EMPLOYMENT_STATUS_DETAILS');
END IF;
IF p_chg_columns LIKE '%EMPL_STATUS_EFFECTIVE_DATE%'
THEN
l_mod_chg_column := REPLACE(l_mod_chg_column,'EMPL_STATUS_EFFECTIVE_DATE','EMPLOYMENT_STATUS_DETAILS');
END IF;
IF p_chg_columns LIKE '%EMPLOYMENT_EXPIRATION_DATE%'
THEN
l_mod_chg_column := REPLACE(l_mod_chg_column,'EMPLOYMENT_EXPIRATION_DATE','EMPLOYMENT_STATUS_DETAILS');
END IF;
IF p_chg_columns LIKE '%PRIMARY_LABOUR_ACCOUNT%'
THEN
l_mod_chg_column := REPLACE(l_mod_chg_column,'PRIMARY_LABOUR_ACCOUNT','PRIMARY_LABOUR_ACCOUNT_DETAILS');
END IF;
IF p_chg_columns LIKE '%PRI_LAB_ACCOUNT_EFFECTIVE_DATE%'
THEN
l_mod_chg_column := REPLACE(l_mod_chg_column,'PRI_LAB_ACCOUNT_EFFECTIVE_DATE','PRIMARY_LABOUR_ACCOUNT_DETAILS');
END IF;
IF p_chg_columns LIKE '%LABOUR_ACCOUNT_EXPIRATION_DATE%'
THEN
l_mod_chg_column := REPLACE(l_mod_chg_column,'LABOUR_ACCOUNT_EXPIRATION_DATE','PRIMARY_LABOUR_ACCOUNT_DETAILS');
END IF;
IF p_chg_columns LIKE '%PRIMARY_JOB_ACCOUNT%'
THEN
l_mod_chg_column := REPLACE(l_mod_chg_column,'PRIMARY_JOB_ACCOUNT','PRIMARY_LABOUR_ACCOUNT_DETAILS');
END IF;
IF p_chg_columns LIKE '%PRI_JOB_ACCOUNT_EFFECTIVE_DATE%'
THEN
l_mod_chg_column := REPLACE(l_mod_chg_column,'PRI_JOB_ACCOUNT_EFFECTIVE_DATE','PRIMARY_LABOUR_ACCOUNT_DETAILS');
END IF;
IF p_chg_columns LIKE '%PRI_JOB_ACCOUNT_EXP_DATE%'
THEN
l_mod_chg_column := REPLACE(l_mod_chg_column,'PRI_JOB_ACCOUNT_EXP_DATE','PRIMARY_LABOUR_ACCOUNT_DETAILS');
END IF;
l_badge_column := REPLACE(p_chg_columns,'BADGE_NUMBER_EFFECTIVE_DATE','BADGE_NUM_EFFECTIVE_DATE');
IF l_badge_column LIKE '%BADGE_NUMBER%'
THEN
l_mod_chg_column := REPLACE(REPLACE(l_mod_chg_column,'BADGE_NUMBER','BADGE_NUMBER_DETAILS'), 'BADGE_NUMBER_DETAILS_EFFECTIVE_DATE', 'BADGE_NUMBER_DETAILS');
END IF;
IF l_badge_column LIKE '%BADGE_NUM_EFFECTIVE_DATE%'
THEN
l_mod_chg_column := REPLACE(REPLACE(l_mod_chg_column,'BADGE_NUMBER_EFFECTIVE_DATE','BADGE_NUMBER_DETAILS'), 'BADGE_NUMBER_DETAILS_EFFECTIVE_DATE', 'BADGE_NUMBER_DETAILS');
END IF;
IF l_badge_column LIKE '%BADGE_EXPIRATION_DATE%'
THEN
l_mod_chg_column := REPLACE(l_mod_chg_column,'BADGE_EXPIRATION_DATE','BADGE_NUMBER_DETAILS');
END IF;
l_status_cnt := REGEXP_COUNT(l_mod_chg_column, 'EMPLOYMENT_STATUS_DETAILS', 1, 'i') ;
l_badge_cnt := REGEXP_COUNT(l_mod_chg_column, 'BADGE_NUMBER_DETAILS', 1, 'i') ;
l_account_cnt := REGEXP_COUNT(l_mod_chg_column, 'PRIMARY_LABOUR_ACCOUNT_DETAILS', 1, 'i') ;
IF l_status_cnt > 1
THEN
l_mod_chg_column := 'EMPLOYMENT_STATUS_DETAILS~'||REGEXP_REPLACE(l_mod_chg_column,'EMPLOYMENT_STATUS_DETAILS~',NULL);
END IF;
IF l_badge_cnt > 1
THEN
l_mod_chg_column := 'BADGE_NUMBER_DETAILS~'||REGEXP_REPLACE(l_mod_chg_column,'BADGE_NUMBER_DETAILS~',NULL);
END IF;
IF l_account_cnt > 1
THEN
l_mod_chg_column := 'PRIMARY_LABOUR_ACCOUNT_DETAILS~'||REGEXP_REPLACE(l_mod_chg_column,'PRIMARY_LABOUR_ACCOUNT_DETAILS~',NULL);
END IF;
l_mod_chg_column := REPLACE(l_mod_chg_column,'~','|');
xxaa_abc_util_pkg.write_debug ('End of mod_cal_columns - '||l_mod_chg_column);
RETURN l_mod_chg_column;
END mod_cal_columns;
-- -----------------------------------------------------------------------------
-- V4.3 Function to compare calendar columns
-- -----------------------------------------------------------------------------
FUNCTION compare_cal_columns1
(p_person_id NUMBER
, p_chg_columns VARCHAR2)
RETURN VARCHAR2
IS
-- Local Variables
l_cal_chg_col VARCHAR2(1000);
-- Cursor to compare calendar status
CURSOR csr_current_calendar(p_person_id NUMBER)
IS
SELECT badge_details, employment_details, account_details
FROM xxaa_abc_emp_tab emp
WHERE emp.person_id = p_person_id
AND emp.record_id = (SELECT MAX(record_id)
FROM xxaa_abc_emp_tab emp1
WHERE emp1.person_id = p_person_id
);
l_badge_curr_data xxaa_abc_badges_tab;
l_status_curr_data xxaa_abc_status_tab;
l_account_curr_data xxaa_abc_pri_account_tab;
CURSOR csr_previous_calendar(p_person_id NUMBER)
IS
SELECT badge_details, employment_details, account_details
FROM xxaa_abc_emp_tab emp
WHERE emp.person_id = p_person_id
AND emp.record_id = (SELECT MAX(record_id) FROM xxaa_abc_emp_tab WHERE person_id = p_person_id
AND record_id < (SELECT MAX(record_id) FROM xxaa_abc_emp_tab WHERE person_id = p_person_id));
l_badge_prev_data xxaa_abc_badges_tab;
l_status_prev_data xxaa_abc_status_tab;
l_account_prev_data xxaa_abc_pri_account_tab;
l_mod_chg_column VARCHAR2(2000);
BEGIN
l_mod_chg_column := p_chg_columns;
xxaa_abc_util_pkg.write_log ('Begin compare_cal_columns1: '||l_mod_chg_column);
OPEN csr_current_calendar(p_person_id);
FETCH csr_current_calendar INTO l_badge_curr_data, l_status_curr_data, l_account_curr_data;
CLOSE csr_current_calendar;
OPEN csr_previous_calendar(p_person_id);
FETCH csr_previous_calendar INTO l_badge_prev_data, l_status_prev_data, l_account_prev_data;
CLOSE csr_previous_calendar;
--xxaa_abc_util_pkg.write_log ('l_badge_curr_data: '||l_badge_curr_data);
--xxaa_abc_util_pkg.write_log ('l_badge_prev_data: '||l_badge_prev_data);
IF l_badge_curr_data = l_badge_prev_data
THEN
xxaa_abc_util_pkg.write_log ('l_badge_curr_data');
l_mod_chg_column := REPLACE(l_mod_chg_column,'BADGE_NUMBER_DETAILS','');
END IF;
--xxaa_abc_util_pkg.write_log ('l_status_curr_data: '||l_status_curr_data);
--xxaa_abc_util_pkg.write_log ('l_status_prev_data: '||l_status_prev_data);
IF l_status_curr_data = l_status_prev_data
THEN
xxaa_abc_util_pkg.write_log ('l_status_curr_data');
l_mod_chg_column := REPLACE(l_mod_chg_column,'EMPLOYMENT_STATUS_DETAILS','');
END IF;
--xxaa_abc_util_pkg.write_log ('l_account_curr_data: '||l_account_curr_data);
--xxaa_abc_util_pkg.write_log ('l_account_prev_data: '||l_account_prev_data);
IF l_account_curr_data = l_account_prev_data
THEN
xxaa_abc_util_pkg.write_log ('l_account_curr_data');
l_mod_chg_column := REPLACE(l_mod_chg_column,'PRIMARY_LABOUR_ACCOUNT_DETAILS','');
END IF;
xxaa_abc_util_pkg.write_log ('End compare_cal_columns1: '||l_mod_chg_column);
RETURN l_mod_chg_column;
END compare_cal_columns1;
-- -----------------------------------------------------------------------------
PROCEDURE process_threads( p_errbuf OUT NOCOPY VARCHAR2
, p_retcode OUT NOCOPY VARCHAR2
, p_effective_date IN VARCHAR2
, p_thread_no IN NUMBER
)
IS
l_proc_name CONSTANT VARCHAR2 (100) := 'xxaa_abc_emp_int_pkg.process_threads';
l_chg_columns VARCHAR2 (4000) := NULL;
l_chg_columns1 VARCHAR2 (4000) := NULL;
l_old_values VARCHAR2 (4000) := NULL;
l_new_values VARCHAR2 (4000) := NULL;
l_column_list VARCHAR2 (4000) := NULL;
l_errbuf VARCHAR2 (4000) := NULL;
l_retcode NUMBER;
l_store_number VARCHAR2 (20);
l_rec_store_number VARCHAR2 (20):= NULL; --V4.5
l_chg_store_number VARCHAR2 (20);
l_initial_rec_count NUMBER := 0;
l_inc_rec_count NUMBER := 0;
l_record_id NUMBER; -- V4.3
l_record_processed VARCHAR2 (1) := 'N'; -- V4.3
l_cal_chg_column VARCHAR2(100); -- V4.3
l_person_id NUMBER;
l_person_processed VARCHAR2 (1) := 'N'; -- V4.3
l_prev_store_id VARCHAR2 (20); -- V4.5
l_old_store_cps_config VARCHAR2 (1):=NULL;
CURSOR csr_emp_records
IS
SELECT *
FROM xxaa_abc_emp_data_stg
WHERE thread_id = p_thread_no
ORDER BY store_number, person_id, future_use9; --4.4
-- Cursor to get previous employee data from initial run
CURSOR csr_emp_init(l_person_id NUMBER)
IS
SELECT *
FROM xxaa_abc_emp_tab
WHERE 1 = 1
--AND status = 'Processed'
--AND run_mode = 'Write'
--AND person_id = l_person_id
AND record_id = ( SELECT MAX(record_id)
FROM xxaa_abc_emp_tab
WHERE person_id = l_person_id
);
rec_emp_init csr_emp_init%ROWTYPE;
v_data_stg NUMBER;
v_old_cps_config VARCHAR2(1);
-- V4.2 Cursor to get Resync record
CURSOR csr_emp_resync(l_person_id NUMBER)
IS
SELECT TRUNC(creation_date)
FROM xxaa_abc_emp_tab
WHERE 1 = 1
AND status = 'UNPROCESSED'
AND record_mode = 'RESYNC'
AND record_id = ( SELECT MAX(record_id)
FROM xxaa_abc_emp_tab
WHERE person_id = l_person_id);
v_resync_date DATE;
v_rec_mode VARCHAR2(20);
lv_effective_date DATE;
CURSOR get_lookup_details
IS
SELECT tl.security_group_id
, tl.view_application_id
FROM fnd_lookup_types_tl tl,
fnd_lookup_types ltype
WHERE ltype.lookup_type = 'xxaa_abc_STORE_CONTROL'
AND ltype.lookup_type = tl.lookup_type
AND language = 'US';
rec_get_lookup_dtl get_lookup_details%ROWTYPE;
CURSOR csr_get_live_stores(l_store VARCHAR2)
IS
SELECT *
FROM fnd_lookup_values
WHERE lookup_type = 'xxaa_abc_STORE_CONTROL'
AND enabled_flag = 'Y'
AND lookup_code = l_store
AND attribute2 IS NOT NULL;
--and TO_DATE (attribute2, 'YYYY/MM/DD HH24:MI:SS')<=SYSDATE;
rec_get_live_stores csr_get_live_stores%ROWTYPE;
l_header_store_c NUMBER;
FUNCTION xxaa_header_str_count(l_store_id number)
RETURN NUMBER IS
l_count NUMBER;
BEGIN
SELECT COUNT(*) INTO l_count
FROM xxaa_abc_EMP_HEADER_TAB
WHERE store_id = l_store_id;
RETURN (l_count);
END;
BEGIN
xxaa_abc_util_pkg.write_in_audit
( p_proc_name => l_proc_name
, p_param1 => NVL(p_effective_date,SYSDATE)
, p_param2 => NULL
, p_param3 => NULL
, x_audit_id => xxaa_abc_util_pkg.g_audit_id
);
xxaa_abc_util_pkg.write_log ( 'Entering for effective date of process_threads:'|| p_effective_date);
xxaa_abc_util_pkg.start_time;
xxaa_abc_util_pkg.write_log ( 'V4.3 process_threads g_run_number of process_threads = '||g_run_number);
FOR rec_emp_records IN csr_emp_records
LOOP
l_prev_store_id:=NULL; -- V4.5
v_data_stg := csr_emp_records%ROWCOUNT;
xxaa_abc_util_pkg.write_log ('xxaa_abc_EMP_DATA_STG Count: '||v_data_stg);
-- V4.3
IF g_run_number IS NULL
THEN
g_run_number := rec_emp_records.run_number;
g_curr_run_date := rec_emp_records.curr_run_date;
g_last_run_date := rec_emp_records.last_run_date;
END IF;
-- V4.3
xxaa_abc_util_pkg.write_log ('Record Type: '||rec_emp_records.attribute1);
IF rec_emp_records.attribute1 IN ('INCREMENTAL','Calendar') -- V4.3
THEN
l_person_processed := 'N';
l_person_processed := is_record_processed(rec_emp_records.person_id,'Normal');
IF NVL(l_person_processed,'N') = 'N'
THEN
l_chg_columns := NULL;
l_chg_columns1 := NULL;
END IF;
--Get previous value for comparison
OPEN csr_emp_init (rec_emp_records.person_id);
FETCH csr_emp_init INTO rec_emp_init;
IF csr_emp_init%NOTFOUND
THEN
xxaa_abc_util_pkg.write_log ('No previous record found. This is a New Hire');
l_chg_columns := 'ALL';
ELSE
xxaa_abc_util_pkg.write_log ('Begin of comparing previous records with new record of person_id - '||rec_emp_init.person_id);
--l_column_list := 'EMPLOYEE_NUMBER~NI_NUMBER~TITLE~FIRST_NAME~LAST_NAME~PAYROLL_NUMBER~JOB~NORMAL_HOURS~DATE_START~LENGTH_OF_SERVICE~ADDR_LINE_1~ADDR_LINE_2~ADDR_LINE_3~CITY~COUNTY~POST_CODE~COUNTRY~HOME_PHONE_NUMBER~MOBILE_PHONE_NUMBER~EMAIL_ADDRESS~STORE_NUMBER~EMPLOYMENT_STATUS~EMPL_STATUS_EFFECTIVE_DATE~BIOMETRIC_EMPLOYEE_FLAG~PRIMARY_LABOUR_ACCOUNT~PRI_LAB_ACCOUNT_EFFECTIVE_DATE~DEVICE_GROUP~BADGE_NUMBER~BADGE_NUMBER_EFFECTIVE_DATE~PAY_RULE~ROLE_HIRE_DATE~STORE_TYPE~CLOCK_INDICATOR~MIDDLE_INITIAL~EMPLOYMENT_EXPIRATION_DATE~BADGE_EXPIRATION_DATE~LABOUR_ACCOUNT_EXPIRATION_DATE~PAY_RULE_EFFECTIVE_DATE~WORKFORCE_TIMEKEEPER_LICENSE~DATE_OF_BIRTH~WORK_PHONE_NUMBER~PRIMARY_JOB_ACCOUNT~PRI_JOB_ACCOUNT_EFFECTIVE_DATE~PRI_JOB_ACCOUNT_EXP_DATE~WORKER_TYPE~DEPARTMENT~GRADE~RIGHT_TO_WORK_TYPE~CURR_STORE_CPS_CONFIG~OLD_STORE_CPS_CONFIG~JOB_CODE~JOB_NAME~REGION~'; --V2.3
l_prev_store_id:= rec_emp_init.store_number; --V4.5
--CPS3 CHG_COL_LIST fields
l_column_list := 'FIRST_NAME~LAST_NAME~NORMAL_HOURS~HOME_PHONE_NUMBER~MOBILE_PHONE_NUMBER~STORE_NUMBER~EMPLOYMENT_STATUS~EMPL_STATUS_EFFECTIVE_DATE~BIOMETRIC_EMPLOYEE_FLAG~PRIMARY_LABOUR_ACCOUNT~PRI_LAB_ACCOUNT_EFFECTIVE_DATE~DEVICE_GROUP~BADGE_NUMBER~BADGE_NUMBER_EFFECTIVE_DATE~ROLE_HIRE_DATE~STORE_TYPE~MIDDLE_INITIAL~EMPLOYMENT_EXPIRATION_DATE~BADGE_EXPIRATION_DATE~LABOUR_ACCOUNT_EXPIRATION_DATE~DATE_OF_BIRTH~PRIMARY_JOB_ACCOUNT~PRI_JOB_ACCOUNT_EFFECTIVE_DATE~PRI_JOB_ACCOUNT_EXP_DATE~WORKER_TYPE~DEPARTMENT~RIGHT_TO_WORK_TYPE~CURR_STORE_CPS_CONFIG~JOB_CODE~JOB_NAME~';
l_old_values :=
rec_emp_init.first_name || '~'|| rec_emp_init.last_name || '~'|| rec_emp_init.normal_hours
|| '~'|| rec_emp_init.home_phone_number || '~'|| rec_emp_init.mobile_phone_number || '~'|| rec_emp_init.store_number
|| '~'|| rec_emp_init.employment_status || '~'|| rec_emp_init.empl_status_effective_date || '~'|| rec_emp_init.biometric_employee_flag
|| '~'|| rec_emp_init.primary_labour_account || '~'|| rec_emp_init.pri_lab_account_effective_date || '~'|| rec_emp_init.device_group
|| '~'|| rec_emp_init.badge_number || '~'|| rec_emp_init.badge_number_effective_date || '~'|| rec_emp_init.role_hire_date
|| '~'|| rec_emp_init.store_type || '~'|| rec_emp_init.middle_initial || '~'|| rec_emp_init.employment_expiration_date
|| '~'|| rec_emp_init.badge_expiration_date || '~'|| rec_emp_init.labour_account_expiration_date || '~'|| TO_CHAR(rec_emp_init.date_of_birth, 'DD-MON-YYYY') --V5.5
|| '~'|| rec_emp_init.primary_job_account || '~'|| rec_emp_init.pri_job_account_effective_date || '~'|| rec_emp_init.pri_job_account_exp_date
|| '~'|| rec_emp_init.worker_type || '~'|| rec_emp_init.department || '~'|| rec_emp_init.right_to_work_type
|| '~'|| rec_emp_init.curr_store_cps_config || '~'|| rec_emp_init.job_code || '~'|| rec_emp_init.job_name
|| '~';
--xxaa_abc_util_pkg.write_log ('l_old_values - '||l_old_values);
xxaa_abc_util_pkg.write_log ('l_old_values - '||'FIRST_NAME: '||rec_emp_init.first_name||'LAST_NAME: '||rec_emp_init.last_name||'NORMAL_HOURS: '||rec_emp_init.normal_hours|| 'HOME_PHONE_NUMBER: '||rec_emp_init.home_phone_number|| 'MOBILE_PHONE_NUMBER: '||rec_emp_init.mobile_phone_number|| 'STORE_NUMBER: '||rec_emp_init.store_number|| ' EMPLOYMENT_STATUS: '||rec_emp_init.employment_status|| ' EMPL_STATUS_EFFECTIVE_DATE: '||rec_emp_init.empl_status_effective_date|| ' BIOMETRIC_EMPLOYEE_FLAG: '||rec_emp_init.biometric_employee_flag|| ' PRIMARY_LABOUR_ACCOUNT: '||rec_emp_init.primary_labour_account|| ' PRI_LAB_ACCOUNT_EFFECTIVE_DATE: '||rec_emp_init.pri_lab_account_effective_date|| ' DEVICE_GROUP: '||rec_emp_init.device_group|| ' BADGE_NUMBER: '||rec_emp_init.badge_number|| ' BADGE_NUMBER_EFFECTIVE_DATE: '||rec_emp_init.badge_number_effective_date|| ' ROLE_HIRE_DATE: '||rec_emp_init.role_hire_date|| ' STORE_TYPE: '||rec_emp_init.store_type|| ' MIDDLE_INITIAL: '||rec_emp_init.middle_initial|| ' EMPLOYMENT_EXPIRATION_DATE: '||rec_emp_init.employment_expiration_date|| ' BADGE_EXPIRATION_DATE: '||rec_emp_init.badge_expiration_date|| ' LABOUR_ACCOUNT_EXPIRATION_DATE: '||rec_emp_init.labour_account_expiration_date|| ' DATE_OF_BIRTH: '||TO_CHAR(rec_emp_init.date_of_birth,'DD-MON-YYYY')|| ' PRIMARY_JOB_ACCOUNT: '||rec_emp_init.primary_job_account|| ' PRI_JOB_ACCOUNT_EFFECTIVE_DATE: '||rec_emp_init.pri_job_account_effective_date|| ' PRI_JOB_ACCOUNT_EXP_DATE: '||rec_emp_init.pri_job_account_exp_date|| ' WORKER_TYPE: '||rec_emp_init.worker_type|| ' DEPARTMENT: '||rec_emp_init.department|| ' RIGHT_TO_WORK_TYPE: '||rec_emp_init.right_to_work_type|| ' CURR_STORE_CPS_CONFIG: '||rec_emp_init.curr_store_cps_config|| ' JOB_CODE: '||rec_emp_init.job_code|| ' JOB_NAME: '||rec_emp_init.job_name); --V5.5
l_new_values :=
rec_emp_records.first_name || '~'|| rec_emp_records.last_name || '~'|| rec_emp_records.normal_hours
|| '~'|| rec_emp_records.home_phone_number || '~'|| rec_emp_records.mobile_phone_number || '~'|| rec_emp_records.store_number
|| '~'|| rec_emp_records.employment_status || '~'|| rec_emp_records.empl_status_effective_date || '~'|| rec_emp_records.biometric_employee_flag
|| '~'|| rec_emp_records.primary_labour_account || '~'|| rec_emp_records.pri_lab_account_effective_date || '~'|| rec_emp_records.device_group
|| '~'|| rec_emp_records.badge_number || '~'|| rec_emp_records.badge_number_effective_date || '~'|| rec_emp_records.role_hire_date
|| '~'|| rec_emp_records.store_type || '~'|| rec_emp_records.middle_initial || '~'|| rec_emp_records.employment_expiration_date
|| '~'|| rec_emp_records.badge_expiration_date || '~'|| rec_emp_records.labour_account_expiration_date || '~'|| TO_CHAR(rec_emp_records.date_of_birth, 'DD-MON-YYYY') --V5.5
|| '~'|| rec_emp_records.primary_job_account || '~'|| rec_emp_records.pri_job_account_effective_date || '~'|| rec_emp_records.pri_job_account_exp_date
|| '~'|| rec_emp_records.worker_type || '~'|| rec_emp_records.department || '~'|| rec_emp_records.right_to_work_type
|| '~'|| rec_emp_records.curr_store_cps_config || '~'|| rec_emp_records.job_code || '~'|| rec_emp_records.job_name
|| '~';
--xxaa_abc_util_pkg.write_log ('l_new_values - '||l_new_values);
xxaa_abc_util_pkg.write_log ('l_new_values - '||'FIRST_NAME: '||rec_emp_records.first_name||'LAST_NAME: '||rec_emp_records.last_name||'NORMAL_HOURS: '||rec_emp_records.normal_hours|| 'HOME_PHONE_NUMBER: '||rec_emp_records.home_phone_number|| 'MOBILE_PHONE_NUMBER: '||rec_emp_records.mobile_phone_number|| 'STORE_NUMBER: '||rec_emp_records.store_number|| ' EMPLOYMENT_STATUS: '||rec_emp_records.employment_status|| ' EMPL_STATUS_EFFECTIVE_DATE: '||rec_emp_records.empl_status_effective_date|| ' BIOMETRIC_EMPLOYEE_FLAG: '||rec_emp_records.biometric_employee_flag|| ' PRIMARY_LABOUR_ACCOUNT: '||rec_emp_records.primary_labour_account|| ' PRI_LAB_ACCOUNT_EFFECTIVE_DATE: '||rec_emp_records.pri_lab_account_effective_date|| ' DEVICE_GROUP: '||rec_emp_records.device_group|| ' BADGE_NUMBER: '||rec_emp_records.badge_number|| ' BADGE_NUMBER_EFFECTIVE_DATE: '||rec_emp_records.badge_number_effective_date|| ' ROLE_HIRE_DATE: '||rec_emp_records.role_hire_date|| ' STORE_TYPE: '||rec_emp_records.store_type|| ' MIDDLE_INITIAL: '||rec_emp_records.middle_initial|| ' EMPLOYMENT_EXPIRATION_DATE: '||rec_emp_records.employment_expiration_date|| ' BADGE_EXPIRATION_DATE: '||rec_emp_records.badge_expiration_date|| ' LABOUR_ACCOUNT_EXPIRATION_DATE: '||rec_emp_records.labour_account_expiration_date|| ' DATE_OF_BIRTH: '||TO_CHAR(rec_emp_records.date_of_birth,'DD-MON-YYYY')|| ' PRIMARY_JOB_ACCOUNT: '||rec_emp_records.primary_job_account|| ' PRI_JOB_ACCOUNT_EFFECTIVE_DATE: '||rec_emp_records.pri_job_account_effective_date|| ' PRI_JOB_ACCOUNT_EXP_DATE: '||rec_emp_records.pri_job_account_exp_date|| ' WORKER_TYPE: '||rec_emp_records.worker_type|| ' DEPARTMENT: '||rec_emp_records.department|| ' RIGHT_TO_WORK_TYPE: '||rec_emp_records.right_to_work_type|| ' CURR_STORE_CPS_CONFIG: '||rec_emp_records.curr_store_cps_config|| ' JOB_CODE: '||rec_emp_records.job_code|| ' JOB_NAME: '||rec_emp_records.job_name); --V5.5
--compare old vs current;
compare_rows ( l_column_list, l_old_values, l_new_values, l_chg_columns);
-- V4.3 Compare Calendar Columns
/*IF rec_emp_records.attribute1 = 'Calendar'
THEN
l_cal_chg_column := compare_cal_columns(rec_emp_records.person_id);
END IF;*/
IF l_chg_columns IS NOT NULL
THEN
l_chg_columns := mod_cal_columns(l_chg_columns);
END IF;
/*IF l_cal_chg_column IS NOT NULL
THEN
IF l_chg_columns IS NULL
THEN
l_chg_columns := SUBSTR(l_cal_chg_column,0,length(l_cal_chg_column)-1);
ELSE
l_chg_columns := l_chg_columns||'|'||SUBSTR(l_cal_chg_column,0,length(l_cal_chg_column)-1);
END IF;
END IF; */
-- V4.3 End
END IF;
CLOSE csr_emp_init;
--V4.2
v_resync_date := NULL;
OPEN csr_emp_resync (rec_emp_init.person_id);
FETCH csr_emp_resync INTO v_resync_date;
CLOSE csr_emp_resync;
lv_effective_date := TRUNC(TO_DATE(p_effective_date,'RRRR/MM/DD HH24:MI:SS'))-1;
IF v_resync_date = lv_effective_date
THEN
v_rec_mode := 'RESYNC';
ELSE
v_rec_mode := 'UPDATE';
END IF;
--V4.2
IF rec_emp_records.age = 18 AND l_chg_columns IS NULL
THEN l_chg_columns := 'AGE';
ELSIF rec_emp_records.age = 18 AND l_chg_columns IS NOT NULL
THEN l_chg_columns := l_chg_columns||'|AGE';
ELSE l_chg_columns := l_chg_columns;
END IF;
-- V4.3
l_record_processed := 'N';
l_record_processed := is_record_processed(rec_emp_records.person_id,'Normal');
xxaa_abc_util_pkg.write_debug ('l_record_processed - '||l_record_processed);
xxaa_abc_util_pkg.write_debug ('IF l_record_processed = Y, Then this record will not be inserted');
-- V4.3
l_chg_columns1 := l_chg_columns1||'|'||l_chg_columns||'|';
l_record_id := NULL; -- V4.3 commented --V4.4 Uncommented
IF l_chg_columns IS NOT NULL AND (NOT g_debug_mode)
AND NVL(l_record_processed,'N') = 'N' -- V4.3
THEN
l_inc_rec_count := l_inc_rec_count + 1;
l_record_id := xxaa_abc_emp_tab_s.NEXTVAL; -- V4.3
xxaa_abc_util_pkg.write_log ('Inserting record for the store : '||rec_emp_records.store_number||' for person_id : '||rec_emp_records.person_id);
INSERT INTO xxaa_abc_emp_tab
(record_id
, person_id
, assignment_id
, employee_number
, ni_number
, title
, first_name
, last_name
, date_of_birth
, age
, payroll_number
, organization
, location
, job
, grade
, normal_hours
, trade_union_shop_steward
, date_start
, length_of_service
, addr_line_1
, addr_line_2
, addr_line_3
, city
, county
, postal_code
, country
, address_date_from
, home_phone_number
, home_phone_date_from
, mobile_phone_number
, mobile_phone_date_from
, work_phone_number --V2.3
, work_phone_date_from --V2.3
, email_address
, bank_sort_code
, bank_account_number
, store_number
, workforce_timekeeper_license
, middle_initial
, employment_status
, empl_status_effective_date
, biometric_employee_flag
, channel
, zone
, region
, area
, store
, department
, job_code
, primary_labour_account
, pri_lab_account_effective_date
, device_group
, badge_number
, badge_number_effective_date
, pay_rule
, run_number
, role_hire_date
, store_type
, clock_indicator
, employment_expiration_date
, badge_expiration_date
, labour_account_expiration_date
, pay_rule_effective_date
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, created_by
, creation_date
, last_updated_by
, last_updated_date
, record_mode
, record_created_by
, chg_col_list
, run_mode
, status
, error_message
, worker_type --V2.3
, job_acc_segment7 --V2.3
, primary_job_account --V2.3
, job_name --V2.3
, pri_job_account_exp_date --V2.3
, pri_job_account_effective_date --V2.3
, future_use1
, future_use2
, future_use3
, future_use4
, future_use5
, future_use6
, future_use7
, future_use8
, future_use9
, future_use10
, right_to_work_type
, curr_store_cps_config -- V4.2
, old_store_cps_config -- V4.2
, badge_details -- V4.3
, employment_details -- V4.3
, account_details -- V4.3
)
VALUES
( l_record_id
, rec_emp_records.person_id
, rec_emp_records.assignment_id
, rec_emp_records.employee_number
, rec_emp_records.ni_number
, rec_emp_records.title
, rec_emp_records.first_name
, rec_emp_records.last_name
, rec_emp_records.date_of_birth
, rec_emp_records.age
, rec_emp_records.payroll_number
, rec_emp_records.organization
, rec_emp_records.location
, rec_emp_records.job
, rec_emp_records.grade
, rec_emp_records.normal_hours
, rec_emp_records.trade_union_shop_steward
, rec_emp_records.date_start
, rec_emp_records.length_of_service
, rec_emp_records.addr_line_1
, rec_emp_records.addr_line_2
, rec_emp_records.addr_line_3
, rec_emp_records.city
, rec_emp_records.county
, rec_emp_records.postal_code
, rec_emp_records.country
, rec_emp_records.address_date_from
, rec_emp_records.home_phone_number
, rec_emp_records.home_phone_date_from
, rec_emp_records.mobile_phone_number
, rec_emp_records.mobile_phone_date_from
, rec_emp_records.work_phone_number --V2.3
, rec_emp_records.work_phone_date_from --V2.3
, rec_emp_records.email_address
, rec_emp_records.bank_sort_code
, rec_emp_records.bank_account_number
, rec_emp_records.store_number
, rec_emp_records.workforce_timekeeper_license
, rec_emp_records.middle_initial
, rec_emp_records.employment_status
, rec_emp_records.empl_status_effective_date
, rec_emp_records.biometric_employee_flag
, rec_emp_records.channel
, rec_emp_records.zone
, rec_emp_records.region
, rec_emp_records.area
, rec_emp_records.store
, rec_emp_records.department
, rec_emp_records.job_code
, rec_emp_records.primary_labour_account
, rec_emp_records.pri_lab_account_effective_date
, rec_emp_records.device_group
, rec_emp_records.badge_number
, rec_emp_records.badge_number_effective_date
, rec_emp_records.pay_rule
, rec_emp_records.run_number
, rec_emp_records.role_hire_date
, rec_emp_records.store_type
, rec_emp_records.clock_indicator
, rec_emp_records.employment_expiration_date
, rec_emp_records.badge_expiration_date
, rec_emp_records.labour_account_expiration_date
, rec_emp_records.pay_rule_effective_date
, rec_emp_records.attribute2
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, fnd_profile.VALUE ('USER_ID')
, SYSDATE
, fnd_profile.VALUE ('USER_ID')
, SYSDATE
, v_rec_mode -- V4.2
, 'INCREMENTAL'
, NULL--l_chg_columns
, 'Write'
, 'UNPROCESSED'
, NULL
, rec_emp_records.worker_type
, rec_emp_records.job_acc_segment7
, rec_emp_records.primary_job_account
, rec_emp_records.job_name
, rec_emp_records.pri_job_account_exp_date
, rec_emp_records.pri_job_account_effective_date
, NULL -- future_use1
, NULL -- future_use2
, NULL -- future_use3
, NULL -- future_use4
, NULL -- future_use5
, NULL -- future_use6
, NULL -- future_use7
, NULL -- future_use8
, rec_emp_records.future_use9 -- future_use9 --V4.4
, rec_emp_records.future_use10 -- future_use10
, rec_emp_records.right_to_work_type
, rec_emp_records.curr_store_cps_config
, rec_emp_records.old_store_cps_config
, xxaa_abc_badges_tab(xxaa_abc_badges_typ(null,null,null)) -- V4.3
, xxaa_abc_status_tab(xxaa_abc_status_typ(null,null,null,null)) -- V4.3
, xxaa_abc_pri_account_tab(xxaa_abc_pri_account_typ(null,null,null,null,null)) -- V4.3
);
-- V4.3 Populate calendar columns
pop_calendar_records
(p_record_id => l_record_id
,p_person_id => rec_emp_records.person_id
,p_mode => 'Normal'
,p_effective_date => p_effective_date
);
COMMIT;
xxaa_abc_util_pkg.write_debug ('--------------------------------------------------');
BEGIN
OPEN csr_get_live_stores(rec_emp_records.store_number); --V4.5
FETCH csr_get_live_stores INTO rec_get_live_stores; --V4.5
CLOSE csr_get_live_stores; --V4.5
--Start of V4.5
IF NVL(l_rec_store_number,'X')<>rec_emp_records.store_number THEN
l_rec_store_number:=rec_emp_records.store_number;
l_old_store_cps_config:=rec_get_live_stores.attribute15;
xxaa_abc_util_pkg.write_log ('Old store cps config for store: '||rec_emp_records.store_number||' : '||l_old_store_cps_config );
END IF;
--End of V4.5
IF rec_emp_records.curr_store_cps_config <> NVL(rec_emp_records.old_store_cps_config, 'A')
THEN
OPEN get_lookup_details;
FETCH get_lookup_details INTO rec_get_lookup_dtl;
CLOSE get_lookup_details;
xxaa_abc_util_pkg.write_debug ('Updating store config values');
xxaa_abc_util_pkg.write_debug ('rec_emp_records.curr_store_cps_config: '||rec_emp_records.curr_store_cps_config);
xxaa_abc_util_pkg.write_debug ('rec_emp_records.old_store_cps_config: '||rec_emp_records.old_store_cps_config);
xxaa_abc_util_pkg.write_debug ('rec_get_live_stores.lookup_code: '||rec_get_live_stores.lookup_code);
fnd_lookup_values_pkg.update_row( x_lookup_type => 'xxaa_abc_STORE_CONTROL'
, x_security_group_id => rec_get_lookup_dtl.security_group_id
, x_view_application_id => rec_get_lookup_dtl.view_application_id
, x_lookup_code => rec_get_live_stores.lookup_code
, x_tag => rec_get_live_stores.tag
, x_attribute_category => rec_get_live_stores.attribute_category
, x_attribute1 => rec_get_live_stores.attribute1
, x_attribute2 => rec_get_live_stores.attribute2
, x_attribute3 => rec_get_live_stores.attribute3
, x_attribute4 => rec_get_live_stores.attribute4
, x_enabled_flag => 'Y'
, x_start_date_active => rec_get_live_stores.start_date_active
, x_end_date_active => rec_get_live_stores.end_date_active
, x_territory_code => rec_get_live_stores.territory_code
, x_attribute5 => rec_get_live_stores.attribute5
, x_attribute6 => rec_get_live_stores.attribute6
, x_attribute7 => rec_get_live_stores.attribute7
, x_attribute8 => rec_get_live_stores.attribute8
, x_attribute9 => rec_get_live_stores.attribute9
, x_attribute10 => rec_get_live_stores.attribute10
, x_attribute11 => rec_get_live_stores.attribute11
, x_attribute12 => rec_get_live_stores.attribute12
, x_attribute13 => rec_get_live_stores.attribute13
, x_attribute14 => rec_emp_records.curr_store_cps_config
, x_attribute15 => rec_emp_records.curr_store_cps_config
, x_meaning => rec_get_live_stores.meaning
, x_description => rec_get_live_stores.description
, x_last_update_date => SYSDATE
, x_last_updated_by => fnd_profile.VALUE ('USER_ID')
, x_last_update_login => fnd_profile.VALUE ('USER_ID') );
END IF;
COMMIT;
END;
END IF;
BEGIN
l_chg_columns1 := compare_cal_columns1(rec_emp_records.person_id, l_chg_columns1);
SELECT REPLACE (listagg (names, ',') within GROUP (ORDER BY rn), ',', '|') INTO l_chg_columns1
FROM
(SELECT level rn
, regexp_substr ((SELECT REPLACE (listagg (l_chg_columns1, '|') within GROUP (ORDER BY 1), '|', ',')
FROM DUAL), '[^,]+', 1, level) names
, row_number () over (partition BY regexp_substr (
(SELECT REPLACE (listagg (l_chg_columns1, '|') within GROUP (ORDER BY 1), '|', ',')
FROM DUAL
), '[^,]+', 1, level) order by level) rnn
FROM DUAL
CONNECT BY regexp_substr (
(SELECT REPLACE (listagg (l_chg_columns1, '|') within GROUP (ORDER BY 1), '|', ',')
FROM DUAL
), '[^,]+', 1, level) IS NOT NULL
)
WHERE
rnn=1;
xxaa_abc_util_pkg.write_log('Values in CHG_COL_LIST : '||l_chg_columns1||' for record_id : '||l_record_id);
BEGIN --V4.4
/*V5.0-- Override record_mode AND chg_col_list in case of INCREMENTAL Re-Hire as part of Defect #623*/
IF NVL(v_rec_mode, 'X') = 'UPDATE'
AND INSTR (NVL(l_chg_columns1, 'X'), 'ROLE_HIRE_DATE') >= 1
THEN
v_rec_mode := 'CREATE';
l_chg_columns1 := 'ALL';
UPDATE xxaa_abc_emp_tab
SET chg_col_list = l_chg_columns1
,record_mode = v_rec_mode
WHERE record_id = l_record_id;
xxaa_abc_util_pkg.write_log('Updated successfully CHG_COL_lIST : '||l_chg_columns1||', RECORD_MODE : '||v_rec_mode||' for record_id : '||l_record_id);
ELSE
-- Start of V5.5 --
IF l_chg_columns1 IS NOT NULL THEN
UPDATE xxaa_abc_emp_tab --Existing code
SET chg_col_list = l_chg_columns1
WHERE record_id = l_record_id;
xxaa_abc_util_pkg.write_log('Updated successfully CHG_COL_lIST : '||l_chg_columns1||' for record_id : '||l_record_id); --V4.4
ELSE
DELETE FROM xxaa_abc_emp_tab WHERE record_id=l_record_id;
xxaa_abc_util_pkg.write_log('Deleted successfully as chg col list null for record: '||l_record_id); --V4.4
END IF;
-- End of V5.5 --
END IF; /* V5.0 End */
EXCEPTION
WHEN OTHERS THEN xxaa_abc_util_pkg.write_log('Exception raised while updating record for the record_id -'||l_record_id||' : '||SQLERRM); --V4.4
END;
COMMIT; --V4.4
END;
ELSIF rec_emp_records.attribute1 = 'INITIAL' AND (NOT g_debug_mode)
THEN
l_store_number := rec_emp_records.store_number ;
l_header_store_c := xxaa_header_str_count(rec_emp_records.store_number); --V4.4
IF (l_store_number <> NVL(l_chg_store_number,0) AND (l_header_store_c = 0))
THEN
xxaa_abc_util_pkg.write_log('Inserting Store record into header table : '||l_store_number);
l_chg_store_number := l_store_number;
IF rec_emp_records.attribute3 = 'Y' --V2.0 to avoid multiple entries in the header table
THEN
BEGIN
INSERT INTO xxaa_abc_EMP_HEADER_TAB -- Store numbers
( record_id
, store_id
, live_date
, ini_run_date
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, created_by
, creation_date
, last_updated_by
, last_updated_date)
VALUES
( xxaa_abc_emp_header_tab_s.nextval
, l_store_number
, rec_emp_records.live_date
, TRUNC(SYSDATE)
, rec_emp_records.run_number --attribute1
, NULL --attribute2
, NULL --attribute3
, NULL --attribute4
, NULL --attribute5
, NULL --attribute6
, NULL --attribute7
, NULL --attribute8
, NULL --attribute9
, NULL --attribute10
, fnd_profile.value('USER_ID') --created_by
, SYSDATE --creation_date
, fnd_profile.value('USER_ID') --last_updated_by
, SYSDATE --last_updated_date
);
EXCEPTION
WHEN OTHERS THEN xxaa_abc_util_pkg.write_log('Exception raised while Inserting record for the store : '||rec_emp_records.store_number||'in Header table : '||SQLERRM);
END;
COMMIT;
END IF; --V2.0
END IF;
l_record_id := NULL; -- V4.3
IF NOT g_debug_mode
THEN
l_initial_rec_count := l_initial_rec_count + 1;
l_record_id := xxaa_abc_emp_tab_s.NEXTVAL; -- V4.3
INSERT INTO xxaa_abc_emp_tab
( record_id
, person_id
, assignment_id
, employee_number
, ni_number
, title
, first_name
, last_name
, date_of_birth
, age
, payroll_number
, organization
, location
, job
, grade
, normal_hours
, trade_union_shop_steward
, date_start
, length_of_service
, addr_line_1
, addr_line_2
, addr_line_3
, city
, county
, postal_code
, country
, address_date_from
, home_phone_number
, home_phone_date_from
, mobile_phone_number
, mobile_phone_date_from
, work_phone_number --V2.3
, work_phone_date_from --V2.3
, email_address
, bank_sort_code
, bank_account_number
, store_number
, workforce_timekeeper_license
, middle_initial
, employment_status
, empl_status_effective_date
, biometric_employee_flag
, channel
, zone
, region
, area
, store
, department
, job_code
, primary_labour_account
, pri_lab_account_effective_date
, device_group
, badge_number
, badge_number_effective_date
, pay_rule
, run_number
, role_hire_date
, store_type
, clock_indicator
, employment_expiration_date
, badge_expiration_date
, labour_account_expiration_date
, pay_rule_effective_date
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, created_by
, creation_date
, last_updated_by
, last_updated_date
, record_mode --CREATE/UPDATE
, record_created_by --INITAL/INCREMENTAL
, chg_col_list --ALL
, run_mode --READ/WRITE
, status
, error_message
, worker_type --V2.3
, job_acc_segment7 --V2.3
, primary_job_account --V2.3
, job_name --V2.3
, pri_job_account_exp_date --V2.3
, pri_job_account_effective_date --V2.3
, future_use1
, future_use2
, future_use3
, future_use4
, future_use5
, future_use6
, future_use7
, future_use8
, future_use9
, future_use10
, right_to_work_type
, curr_store_cps_config
, old_store_cps_config
, badge_details -- V4.3
, employment_details -- V4.3
, account_details -- V4.3
)
VALUES
( l_record_id
, rec_emp_records.person_id
, rec_emp_records.assignment_id
, rec_emp_records.employee_number
, rec_emp_records.ni_number
, rec_emp_records.title
, rec_emp_records.first_name
, rec_emp_records.last_name
, rec_emp_records.date_of_birth
, rec_emp_records.age
, rec_emp_records.payroll_number
, rec_emp_records.organization
, rec_emp_records.location
, rec_emp_records.job
, rec_emp_records.grade
, rec_emp_records.normal_hours
, rec_emp_records.trade_union_shop_steward
, rec_emp_records.date_start
, rec_emp_records.length_of_service
, rec_emp_records.addr_line_1
, rec_emp_records.addr_line_2
, rec_emp_records.addr_line_3
, rec_emp_records.city
, rec_emp_records.county
, rec_emp_records.postal_code
, rec_emp_records.country
, rec_emp_records.address_date_from
, rec_emp_records.home_phone_number
, rec_emp_records.home_phone_date_from
, rec_emp_records.mobile_phone_number
, rec_emp_records.mobile_phone_date_from
, rec_emp_records.work_phone_number --V2.3
, rec_emp_records.work_phone_date_from --V2.3
, rec_emp_records.email_address
, rec_emp_records.bank_sort_code
, rec_emp_records.bank_account_number
, rec_emp_records.store_number
, rec_emp_records.workforce_timekeeper_license
, rec_emp_records.middle_initial
, rec_emp_records.employment_status
, rec_emp_records.empl_status_effective_date
, rec_emp_records.biometric_employee_flag
, rec_emp_records.channel
, rec_emp_records.zone
, rec_emp_records.region
, rec_emp_records.area
, rec_emp_records.store
, rec_emp_records.department
, rec_emp_records.job_code
, rec_emp_records.primary_labour_account
, rec_emp_records.pri_lab_account_effective_date
, rec_emp_records.device_group
, rec_emp_records.badge_number
, rec_emp_records.badge_number_effective_date
, rec_emp_records.pay_rule
, rec_emp_records.run_number
, rec_emp_records.role_hire_date
, rec_emp_records.store_type
, rec_emp_records.clock_indicator
, rec_emp_records.employment_expiration_date
, rec_emp_records.badge_expiration_date
, rec_emp_records.labour_account_expiration_date
, rec_emp_records.pay_rule_effective_date
, rec_emp_records.attribute2 --attribute1
, NULL --attribute2
, NULL --attribute3
, NULL --attribute4
, NULL --attribute5
, NULL --attribute6
, NULL --attribute7
, NULL --attribute8
, NULL --attribute9
, NULL --attribute10
, fnd_profile.value('USER_ID') --created_by
, SYSDATE --creation_date
, fnd_profile.value('USER_ID') --last_updated_by
, SYSDATE --last_updated_date
, 'CREATE' --record_mode
, 'INITIAL' --record_created_by --INITAL/INCREMENTAL
, 'ALL' --chg_col_list
, NULL --run_mode
, 'UNPROCESSED'-- status
, NULL --error_message
, rec_emp_records.worker_type --V2.3
, rec_emp_records.job_acc_segment7 --V2.3
, rec_emp_records.primary_job_account --V2.3
, rec_emp_records.job_name --V2.3
, rec_emp_records.pri_job_account_exp_date --V2.3
, rec_emp_records.pri_job_account_effective_date --V2.3
, NULL -- future_use1
, NULL -- future_use2
, NULL -- future_use3
, NULL -- future_use4
, NULL -- future_use5
, NULL -- future_use6
, NULL -- future_use7
, NULL -- future_use8
, rec_emp_records.future_use9 -- future_use9 -- V4.4
, rec_emp_records.future_use10 -- future_use10
, rec_emp_records.right_to_work_type
, rec_emp_records.curr_store_cps_config --V4.2
, rec_emp_records.old_store_cps_config --V4.2
, xxaa_abc_badges_tab(xxaa_abc_badges_typ(null,null,null)) -- V4.3
, xxaa_abc_status_tab(xxaa_abc_status_typ(null,null,null,null)) -- V4.3
, xxaa_abc_pri_account_tab(xxaa_abc_pri_account_typ(null,null,null,null,null)) -- V4.3
);
xxaa_abc_util_pkg.write_debug ('curr_store_cps_config1: '||rec_emp_records.curr_store_cps_config);
xxaa_abc_util_pkg.write_debug ('old_store_cps_config1: '||rec_emp_records.old_store_cps_config);
-- V4.3 Populate calendar columns
pop_calendar_records
(p_record_id => l_record_id
,p_person_id => rec_emp_records.person_id
,p_mode => 'Normal'
,p_effective_date => p_effective_date
);
COMMIT;
BEGIN
IF rec_emp_records.curr_store_cps_config <> NVL(rec_emp_records.old_store_cps_config, 'A')
THEN
OPEN get_lookup_details;
FETCH get_lookup_details INTO rec_get_lookup_dtl;
CLOSE get_lookup_details;
OPEN csr_get_live_stores(rec_emp_records.store_number);
FETCH csr_get_live_stores INTO rec_get_live_stores;
CLOSE csr_get_live_stores;
xxaa_abc_util_pkg.write_debug ('Updating store config values');
fnd_lookup_values_pkg.update_row( x_lookup_type => 'xxaa_abc_STORE_CONTROL'
, x_security_group_id => rec_get_lookup_dtl.security_group_id
, x_view_application_id => rec_get_lookup_dtl.view_application_id
, x_lookup_code => rec_get_live_stores.lookup_code
, x_tag => rec_get_live_stores.tag
, x_attribute_category => rec_get_live_stores.attribute_category
, x_attribute1 => rec_get_live_stores.attribute1
, x_attribute2 => rec_get_live_stores.attribute2
, x_attribute3 => rec_get_live_stores.attribute3
, x_attribute4 => rec_get_live_stores.attribute4
, x_enabled_flag => 'Y'
, x_start_date_active => rec_get_live_stores.start_date_active
, x_end_date_active => rec_get_live_stores.end_date_active
, x_territory_code => rec_get_live_stores.territory_code
, x_attribute5 => rec_get_live_stores.attribute5
, x_attribute6 => rec_get_live_stores.attribute6
, x_attribute7 => rec_get_live_stores.attribute7
, x_attribute8 => rec_get_live_stores.attribute8
, x_attribute9 => rec_get_live_stores.attribute9
, x_attribute10 => rec_get_live_stores.attribute10
, x_attribute11 => rec_get_live_stores.attribute11
, x_attribute12 => rec_get_live_stores.attribute12
, x_attribute13 => rec_get_live_stores.attribute13
, x_attribute14 => rec_emp_records.curr_store_cps_config
, x_attribute15 => rec_emp_records.curr_store_cps_config
, x_meaning => rec_get_live_stores.meaning
, x_description => rec_get_live_stores.description
, x_last_update_date => SYSDATE
, x_last_updated_by => fnd_profile.VALUE ('USER_ID')
, x_last_update_login => fnd_profile.VALUE ('USER_ID') );
END IF;
COMMIT;
END;
xxaa_abc_util_pkg.write_debug ('--------------------------------------------------');
ELSE
fnd_file.put_line (fnd_file.OUTPUT,'*********myTime Employee Interface*********');
fnd_file.put_line (fnd_file.OUTPUT,'***************'||rec_emp_records.employee_number||'**************');
fnd_file.put_line (fnd_file.OUTPUT,'NI Number : '||rec_emp_records.ni_number);
fnd_file.put_line (fnd_file.OUTPUT,'Title : '||rec_emp_records.title);
fnd_file.put_line (fnd_file.OUTPUT,'First Name : '||rec_emp_records.first_name);
fnd_file.put_line (fnd_file.OUTPUT,'Last Name : '||rec_emp_records.last_name);
fnd_file.put_line (fnd_file.OUTPUT,'Date of Birth : '||rec_emp_records.date_of_birth);
fnd_file.put_line (fnd_file.OUTPUT,'Age : '||rec_emp_records.age);
fnd_file.put_line (fnd_file.OUTPUT,'Payroll Number : '||rec_emp_records.payroll_number);
fnd_file.put_line (fnd_file.OUTPUT,'Organization : '||rec_emp_records.organization);
fnd_file.put_line (fnd_file.OUTPUT,'Location : '||rec_emp_records.location);
fnd_file.put_line (fnd_file.OUTPUT,'Job : '||rec_emp_records.job);
fnd_file.put_line (fnd_file.OUTPUT,'Grade : '||rec_emp_records.grade);
fnd_file.put_line (fnd_file.OUTPUT,'Tradde Union Shop Steward : '||rec_emp_records.trade_union_shop_steward);
fnd_file.put_line (fnd_file.OUTPUT,'Service Date Start : '||rec_emp_records.date_start);
fnd_file.put_line (fnd_file.OUTPUT,'Length of Service : '||rec_emp_records.length_of_service);
fnd_file.put_line (fnd_file.OUTPUT,'Address - Line 1 : '||rec_emp_records.addr_line_1);
fnd_file.put_line (fnd_file.OUTPUT,'Address - Line 2 : '||rec_emp_records.addr_line_2);
fnd_file.put_line (fnd_file.OUTPUT,'Address - Line 3 : '||rec_emp_records.addr_line_3);
fnd_file.put_line (fnd_file.OUTPUT,'City : '||rec_emp_records.city);
fnd_file.put_line (fnd_file.OUTPUT,'County : '||rec_emp_records.county);
fnd_file.put_line (fnd_file.OUTPUT,'Postal Code : '||rec_emp_records.postal_code);
fnd_file.put_line (fnd_file.OUTPUT,'Country : '||rec_emp_records.country);
fnd_file.put_line (fnd_file.OUTPUT,'Address Effective Date : '||rec_emp_records.address_date_from);
fnd_file.put_line (fnd_file.OUTPUT,'Home Phone Number : '||rec_emp_records.home_phone_number);
fnd_file.put_line (fnd_file.OUTPUT,'Home Phone Number - Effective Date : '||rec_emp_records.home_phone_date_from);
fnd_file.put_line (fnd_file.OUTPUT,'Mobile Phone Number : '||rec_emp_records.mobile_phone_number);
fnd_file.put_line (fnd_file.OUTPUT,'Mobile Phone Number - Effective Date : '||rec_emp_records.mobile_phone_date_from);
fnd_file.put_line (fnd_file.OUTPUT,'Work Phone Number : '||rec_emp_records.work_phone_number);
fnd_file.put_line (fnd_file.OUTPUT,'Work Phone Number - Effective Date : '||rec_emp_records.work_phone_date_from);
fnd_file.put_line (fnd_file.OUTPUT,'Email Address : '||rec_emp_records.email_address);
fnd_file.put_line (fnd_file.OUTPUT,'Bank Sort Code : '||rec_emp_records.bank_sort_code);
fnd_file.put_line (fnd_file.OUTPUT,'Bank Account Number : '||rec_emp_records.bank_account_number);
fnd_file.put_line (fnd_file.OUTPUT,'Store Number : '||rec_emp_records.store_number);
fnd_file.put_line (fnd_file.OUTPUT,'Workforce TimeKeeper License : '||rec_emp_records.workforce_timekeeper_license);
fnd_file.put_line (fnd_file.OUTPUT,'Middle Initial : '||rec_emp_records.middle_initial);
fnd_file.put_line (fnd_file.OUTPUT,'Employment Status : '||rec_emp_records.employment_status);
fnd_file.put_line (fnd_file.OUTPUT,'Employment Status - Effective Date : '||rec_emp_records.empl_status_effective_date);
fnd_file.put_line (fnd_file.OUTPUT,'Biometric Employee Flag : '||rec_emp_records.biometric_employee_flag);
fnd_file.put_line (fnd_file.OUTPUT,'Channel : '||rec_emp_records.channel);
fnd_file.put_line (fnd_file.OUTPUT,'Zone : '||rec_emp_records.zone);
fnd_file.put_line (fnd_file.OUTPUT,'Region : '||rec_emp_records.region);
fnd_file.put_line (fnd_file.OUTPUT,'Area : '||rec_emp_records.area);
fnd_file.put_line (fnd_file.OUTPUT,'Store : '||rec_emp_records.store);
fnd_file.put_line (fnd_file.OUTPUT,'Department : '||rec_emp_records.department);
fnd_file.put_line (fnd_file.OUTPUT,'Job Code : '||rec_emp_records.job_code);
fnd_file.put_line (fnd_file.OUTPUT,'Primary Labour Account : '||rec_emp_records.primary_labour_account);
fnd_file.put_line (fnd_file.OUTPUT,'Primary Labour Account - Effective Date : '||rec_emp_records.pri_lab_account_effective_date);
fnd_file.put_line (fnd_file.OUTPUT,'Device Group : '||rec_emp_records.device_group);
fnd_file.put_line (fnd_file.OUTPUT,'Badge Number : '||rec_emp_records.badge_number);
fnd_file.put_line (fnd_file.OUTPUT,'Badge Number - Effective Date : '||rec_emp_records.badge_number_effective_date);
fnd_file.put_line (fnd_file.OUTPUT,'Pay rule : '||rec_emp_records.pay_rule);
fnd_file.put_line (fnd_file.OUTPUT,'Role Hire Date : '||rec_emp_records.role_hire_date);
fnd_file.put_line (fnd_file.OUTPUT,'Store Type : '||rec_emp_records.store_type);
fnd_file.put_line (fnd_file.OUTPUT,'Clock Indicator : '||rec_emp_records.clock_indicator);
fnd_file.put_line (fnd_file.OUTPUT,'Employment Expiration Date : '||rec_emp_records.employment_expiration_date);
fnd_file.put_line (fnd_file.OUTPUT,'Badge Expiration Date : '||rec_emp_records.badge_expiration_date);
fnd_file.put_line (fnd_file.OUTPUT,'Labour Account Expiration Date : '||rec_emp_records.labour_account_expiration_date);
fnd_file.put_line (fnd_file.OUTPUT,'Pay Rule Effective Date : '||rec_emp_records.pay_rule_effective_date);
fnd_file.put_line (fnd_file.OUTPUT,'Worker Type : '||rec_emp_records.worker_type);
fnd_file.put_line (fnd_file.OUTPUT,'Job Acc Segment7 : '||rec_emp_records.job_acc_segment7);
fnd_file.put_line (fnd_file.OUTPUT,'Primary Job Account : '||rec_emp_records.primary_job_account);
fnd_file.put_line (fnd_file.OUTPUT,'Job Name : '||rec_emp_records.job_name);
fnd_file.put_line (fnd_file.OUTPUT,'Pri Job Account Exp Date : '||rec_emp_records.pri_job_account_exp_date);
fnd_file.put_line (fnd_file.OUTPUT,'Pri Job Account Effective Date : '||rec_emp_records.pri_job_account_effective_date);
fnd_file.put_line (fnd_file.OUTPUT,'Right To Work Type : '||rec_emp_records.right_to_work_type);
END IF;
END IF;
-- Start of V4.5 --
/*IF (rec_emp_records.curr_store_cps_config IS NULL OR rec_emp_records.old_store_cps_config IS NULL) AND l_record_id IS NOT NULL AND l_prev_store_id IS NOT NULL
THEN
pop_curr_old_cps_config(l_record_id,rec_emp_records.future_use10,rec_emp_records.store_number,l_prev_store_id,l_old_store_cps_config);
COMMIT;
END IF;*/
-- End of V4.5 --
END LOOP;
IF NOT g_debug_mode
THEN
xxaa_abc_util_pkg.write_log ( 'Number of Initial Records Processed by thread '||p_thread_no||' - '||l_initial_rec_count);
xxaa_abc_util_pkg.write_log ( 'Number of Incremental Records Processed by thread '||p_thread_no||' - '||l_inc_rec_count);
xxaa_abc_util_pkg.write_log ( 'Total Records Processed by the thread '||p_thread_no||' - '||(l_inc_rec_count+l_initial_rec_count));
END IF;
--COMMIT;
EXCEPTION
WHEN OTHERS THEN xxaa_abc_util_pkg.write_log ( 'Exception - process_threads - '||SQLERRM);
p_retcode := '2';
END process_threads;
PROCEDURE call_conc_program ( p_prog_name IN VARCHAR2
, p_app_short_name IN VARCHAR2
, p_effective_date IN DATE
, p_thread_id IN NUMBER
, p_request_id OUT NUMBER
)
IS
l_request_id NUMBER;
l_procedure VARCHAR2(50) := '.call_conc_program';
BEGIN
l_request_id := fnd_request.submit_request( p_app_short_name
, p_prog_name
, NULL
, SYSDATE
, FALSE
, TO_CHAR(p_effective_date,'RRRR/MM/DD HH24:MI:SS')
, p_thread_id
);
p_request_id := l_request_id;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
xxaa_abc_util_pkg.write_log ( 'Exception - call_conc_program - '||SQLERRM);
END call_conc_program;
PROCEDURE invoke_threads( p_thread_id IN NUMBER)
IS
l_request_id NUMBER := NULL;
l_procedure VARCHAR2(50) := '.invoke_threads';
i NUMBER := 0;
BEGIN
FOR i IN 1 .. p_thread_id
LOOP
call_conc_program( 'xxaa_abc_EMP_INT_CHILD'
, 'xxaaDATA'
, g_effective_date
, i
, l_request_id
);
g_conc_mgr_ndx := g_conc_mgr_ndx + 1;
g_conc_mgr_table(g_conc_mgr_ndx) := l_request_id;
xxaa_abc_util_pkg.write_log ( 'Child Program '||i||' Submitted - '||l_request_id);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
xxaa_abc_util_pkg.write_log ( 'Exception - invoke_threads - '||SQLERRM);
END invoke_threads;
PROCEDURE populate_emp_data ( p_err_buf OUT VARCHAR2
, p_ret_code OUT NUMBER
, p_effective_date IN VARCHAR2
, p_store_id IN VARCHAR2
, p_person_id IN NUMBER DEFAULT NULL
)
IS
-- Local variables
l_proc_name CONSTANT VARCHAR2 (100) := 'xxaa_abc_emp_int_pkg.populate_emp_data';
l_location_id NUMBER;
l_request_id NUMBER;
l_req_phase VARCHAR2 (50);
l_req_status BOOLEAN;
l_status VARCHAR2 (50);
l_req_dev_phase VARCHAR2 (50);
l_req_dev_status VARCHAR2 (50);
l_req_message VARCHAR2 (50);
l_rec_count NUMBER;
l_emp_processed NUMBER;
l_rtc NUMBER;
l_exist VARCHAR2(1);
l_type VARCHAR2(25);
l_abc_location VARCHAR2(100);
--V2.1
l_per_id NUMBER;
l_assignment_id NUMBER;
--V2.1 End
l_badge_number VARCHAR2(50);
l_badge_eff_date VARCHAR2(50);
l_pay_effect_date VARCHAR2(50);
l_emp_status_eff_date VARCHAR2(50);
l_emp_status VARCHAR2(100);
l_pri_lab_date VARCHAR2(50); --V1.6 End
l_pri_lab_acnt VARCHAR2(100);
l_e_status VARCHAR2(100);
l_status_eff_date VARCHAR2(100);
l_pay_rule_date VARCHAR2(50);
l_location VARCHAR2(100);
l_job VARCHAR2(100);
l_thread_id NUMBER := 1;
l_status_code NUMBER := 1;
l_start_time NUMBER;
l_cnt NUMBER := 0;
l_no NUMBER := 0;
l_new_eff_date DATE;
l_transfer_mode VARCHAR2(10);
l_count NUMBER;
l_run_number NUMBER;
l_last_run_date DATE;
l_curr_run_date DATE;
l_rowcount NUMBER;
l_header_flag VARCHAR2(1);
--V2.2 Start
l_home_phone_end DATE;
l_mobile_phone_end DATE;
--V2.2 End
--V2.3 Start
l_work_phone_end DATE;
l_worker_type VARCHAR2(100);
--l_primary_job_account VARCHAR2(1000);
l_pri_job_date VARCHAR2(100);
l_pri_job_acnt VARCHAR2(1000);
l_old_cps_config NUMBER;
--V2.3 End
CURSOR csr_get_location_id
IS
SELECT location_id
FROM hr_all_organization_units
WHERE SUBSTR (name, 2, 4) = TO_CHAR(p_store_id)
AND NAME LIKE 'A%';
CURSOR csr_get_newly_live_stores
IS
SELECT DISTINCT flv.attribute2
FROM fnd_lookup_values flv
WHERE flv.lookup_type = 'xxaa_abc_STORE_CONTROL'
AND flv.enabled_flag = 'Y'
AND TO_DATE (flv.attribute2, 'RRRR/MM/DD HH24:MI:SS') <= g_effective_date
AND flv.attribute3 = NVL(l_location_id, attribute3)
AND flv.attribute4 = 'N'
AND ( flv.attribute1 = 'Convenience'
OR ( flv.attribute1 = 'Supermarket'
AND flv.lookup_code IN
(SELECT flv1.lookup_code
FROM fnd_lookup_values flv1
WHERE flv1.lookup_type =
'xxaa_abc_STORE_EXCP_CONTROL'
AND flv1.attribute1 = 'Y'
AND flv1.enabled_flag = 'Y'
AND NVL (p_store_id, flv.lookup_code) =
flv1.lookup_code)));
l_live_store_date VARCHAR2(50);
--Cursor to get concurrent program last run completion date
CURSOR csr_get_last_run_date
IS
SELECT actual_completion_date
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id = ( SELECT MAX (request_id)
FROM fnd_concurrent_requests fcr1
, fnd_concurrent_programs_tl fcpt
WHERE fcpt.concurrent_program_id = fcr1.concurrent_program_id
AND fcpt.user_concurrent_program_name = 'xxaa ABC Employee Interface Batch'
AND fcr1.status_code IN ('C', 'G')
);
--Cursor to get concurrent program current run start date
CURSOR csr_get_curr_run_date
IS
SELECT actual_start_date
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id = ( SELECT MAX (request_id)
FROM fnd_concurrent_requests fcr1
, fnd_concurrent_programs_tl fcpt
WHERE fcpt.concurrent_program_id = fcr1.concurrent_program_id
AND fcpt.user_concurrent_program_name = 'xxaa ABC Employee Interface Batch'
AND fcr1.status_code = 'R'
AND fcr1.phase_code = 'R'
);
--Cursor to get concurrent program last run completion date
CURSOR csr_get_clck_last_run_date
IS
SELECT actual_completion_date
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id = ( SELECT MAX (request_id)
FROM fnd_concurrent_requests fcr1
, fnd_concurrent_programs_tl fcpt
WHERE fcpt.concurrent_program_id = fcr1.concurrent_program_id
AND fcpt.user_concurrent_program_name = 'xxaa ABC Convenience Clock Number Allocation'
AND fcr1.status_code IN ('C', 'G')
);
-- Cursor to get latest employee data for incremental run
CURSOR csr_get_emp_details
IS
--Initial (Convenience + Supermarket)
SELECT /*+ PARALLEL */ --V4.4
papf.person_id
, paaf.assignment_id
, papf.employee_number
, papf.national_identifier ni_number
, papf.title
, papf.first_name
, papf.last_name
, TO_CHAR(papf.date_of_birth, 'DD-MON-YYYY') date_of_birth --V5.5
, (MONTHS_BETWEEN(g_effective_date, papf.date_of_birth) / 12) age
, ppg.segment4 payroll_number
, haou.name organization
, hla.location_code location
, pj.name job
, pg.name grade
, paaf.normal_hours
, DECODE (paaf.ass_attribute18,'Y','Yes','N','No',NULL) trade_union_shop_steward
, ppos.date_start date_start
, ROUND(MONTHS_BETWEEN(g_effective_date, ppos.adjusted_svc_date) / 12,2) length_of_service
, pa.address_line1 addr_line_1
, pa.address_line2 addr_line_2
, pa.address_line3 addr_line_3
, pa.town_or_city city
, pa.region_1 county
, pa.postal_code postal_code
, pa.country country
, pa.date_from address_date_from
, pph.phone_number home_phone_number
, pph.date_from home_phone_date_from
, ppm.phone_number mobile_phone_number
, ppm.date_from mobile_phone_date_from
, ppw.phone_number work_phone_number
, ppw.date_from work_phone_date_from
, papf.email_address email_address
, SUBSTR(haou.name,2,4) store_number
, 'TRUE' workforce_timekeeper_license
, UPPER(substr(papf.middle_names,1,1)) middle_initial
, past.user_status employment_status
, ppos.date_start empl_status_effective_date
, 'TRUE' biometric_employee_flag
, flv.attribute1 channel
, flv.attribute5 zone
, flv.attribute6 region
, flv.attribute7 area
, FLV.ATTRIBUTE8 STORE_CODE
, DECODE(FLV.ATTRIBUTE9,'Retail',DEPT_MAP.DEPT_DISPLAY,'Convenience','Store',
'Small Retail',NVL(FLVD.ATTRIBUTE2,DEPT_MAP.DEPT_DISPLAY)) department --V4.1 and V4.8
, SUBSTR(pj.name,1,5) job_code
, flv.attribute1
||'/'|| flv.attribute5
||'/'|| flv.attribute6
||'/'|| FLV.ATTRIBUTE7
||'/'|| FLV.ATTRIBUTE8
||'/'|| DECODE(FLV.ATTRIBUTE9,'Convenience','Store',
'Small Retail',NVL(FLVD.ATTRIBUTE2,DEPT_MAP.DEPT_DISPLAY),'Retail',DEPT_MAP.DEPT_DISPLAY) --V4.1 and V4.8
||'/'|| SUBSTR(pj.name,2,4) primary_labour_account
, paaf.effective_start_date pri_lab_account_effective_date
, flv.attribute11 device_group
--, SUBSTR(haou.name,2,4)||paaf.ass_attribute6 badge_number --V1.2
, DECODE( NVL(paaf.ass_attribute6, 'NA'), 'NA', NULL, SUBSTR(haou.name,2,4)||paaf.ass_attribute6) badge_number
, DECODE( NVL(paaf.ass_attribute6, 'NA'), 'NA', NULL, TO_CHAR(paaf.effective_start_date, 'DD-MON-YYYY')|| ' 00:00')badge_number_effective_date
, 'Punch Capture' pay_rule
, flv.attribute2 live_date
--, paaf.effective_start_date role_hire_date
, ppos.date_start role_hire_date --V3.8
, 'TRUE' clock_indicator
, flv.attribute9 store_type
, 'INITIAL' type
, NULL attribute1
, TO_CHAR('01-JAN-3000') employment_expiration_date
, DECODE( NVL(paaf.ass_attribute6, 'NA'), 'NA', NULL, TO_CHAR('01-JAN-3000 00:00')) badge_expiration_date
, TO_CHAR('01-JAN-3000') labour_account_expiration_date
--, TO_CHAR((SYSDATE),'DD-MON-YYYY') pay_rule_effective_date --V4.2
, TO_CHAR( paaf.effective_start_date,'DD-MON-YY') pay_rule_effective_date --V4.2
, flv.lookup_code || '-' || UPPER(flv.meaning) job_acc_segment7 --V2.3
, SUBSTR(pj.name,INSTR(pj.name,'|')+1,INSTR(SUBSTR(pj.name,INSTR(pj.name,'|')+1),'|',-1)-1) job_name --V2.3
, TO_CHAR('01-JAN-3000') pri_job_account_exp_date --V2.3
, 'Sample'
|| '/'
|| 'Retail'
|| '/'
|| flv.attribute5
|| '/'
|| flv.attribute6
|| '/'
|| flv.attribute7
|| '/'
|| flv.attribute1
|| '/'
|| flv.attribute13
|| '/'
--|| DECODE(flv.attribute9, 'Retail', flvd.description, 'Small Retail', flvd.tag, 'Convenience', 'Store') --V4.1
||DECODE(FLV.ATTRIBUTE9, 'Retail',DEPT_MAP.DEPT_DISPLAY,'Convenience', 'Store',
'Small Retail',NVL(FLVD.ATTRIBUTE2,DEPT_MAP.DEPT_DISPLAY) ) --V4.1 and V4.8
|| '/'
|| SUBSTR (pj.name,INSTR (pj.name, '|') + 1,INSTR (SUBSTR (pj.name, INSTR (pj.name, '|') + 1), '|', -1) - 1) primary_job_account
, TO_CHAR ( (paaf.effective_start_date), 'DD-MON-YYYY') pri_job_account_effective_date
, flvw.meaning worker_type
, xra.rtw_type right_to_work_type --V4.2
, flv.attribute14 curr_store_cps_config --V4.2
, flv.attribute15 old_store_cps_config --V4.2
, 'INITIAL' cur_query
, '3' rec_priority --V4.4
FROM per_all_people_f papf
, per_all_assignments_f paaf
, per_assignment_status_types past
, per_periods_of_service ppos
, pay_people_groups ppg
, hr_all_organization_units haou
, hr_locations_all hla
, per_jobs pj
, per_grades pg
, per_addresses pa
, per_phones ppm
, per_phones pph
, per_phones ppw
, fnd_lookup_values flv
, fnd_lookup_values flvd
, fnd_lookup_values flvw
,(SELECT ffvt.flex_value_meaning dept_code
, ffvt.description dept_display
FROM fnd_flex_value_sets ffvs
, fnd_flex_values ffv
, fnd_flex_values_tl ffvt
WHERE ffv.flex_value_set_id = ffvs.flex_value_set_id
AND ffv.flex_value_id = ffvt.flex_value_id
AND ffvs.flex_value_set_name = 'ABC_RETAIL_DEPT'
) dept_map --V4.1
, xxaa_rtw_audit xra --V4.2
WHERE 1=1
AND papf.person_id = paaf.person_id
AND paaf.person_id = NVL(p_person_id, paaf.person_id)
AND papf.current_employee_flag = 'Y'
AND paaf.assignment_type = 'E'
AND paaf.primary_flag = 'Y'
AND paaf.ass_attribute30 IN ('A','P') --V3.6
AND g_effective_date between papf.effective_start_date and papf.effective_end_date
AND g_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND past.assignment_status_type_id = paaf.assignment_status_type_id
AND papf.person_id = ppos.person_id
AND ppos.date_start = (SELECT ppos1.date_start
FROM per_periods_of_service ppos1
WHERE ppos1.person_id = papf.person_id
AND g_effective_date BETWEEN ppos1.date_start
AND NVL(ppos1.actual_termination_date
, TO_DATE('31-Dec-4712'))
)
AND ppg.segment3 IN ('Retail 4 Weekly','Retail Salaried')
AND paaf.people_group_id = ppg.people_group_id
AND paaf.organization_id = haou.organization_id
AND paaf.location_id = hla.location_id
AND paaf.job_id = pj.job_id
AND paaf.grade_id = pg.grade_id
AND papf.person_id = pa.person_id
AND pa.primary_flag = 'Y'
AND g_effective_date between pa.date_from and NVL(pa.date_to, to_date('31-Dec-4712'))
--V1.7 Start
AND ppm.phone_type(+) = 'M'
AND ppm.parent_table(+) = 'PER_ALL_PEOPLE_F'
AND ppm.parent_id(+) = papf.person_id
AND pph.phone_type(+) = 'H1'
AND pph.parent_table(+) = 'PER_ALL_PEOPLE_F'
AND pph.parent_id(+) = papf.person_id
AND ppw.phone_type(+) = 'W1'
AND ppw.parent_table(+) = 'PER_ALL_PEOPLE_F'
AND ppw.parent_id(+) = papf.person_id
AND ( TRUNC(g_effective_date) BETWEEN NVL(pph.date_from, g_effective_date) AND NVL(pph.date_to, TO_DATE('31-Dec-4712')) --V1.7
--V2.2 Start
OR ( NVL (pph.date_to, TO_DATE ('31-Dec-4712')) < g_effective_date
AND NOT EXISTS ( SELECT 'Y'
FROM per_phones
WHERE phone_type = 'H1'
AND parent_id = papf.person_id
AND NVL(date_from, g_effective_date) < NVL(pph.date_to, TO_DATE ('31-Dec-4712'))
)
)
OR ( NVL (pph.date_from, TO_DATE ('31-Dec-4712')) > g_effective_date
AND NOT EXISTS ( SELECT 'Y'
FROM per_phones
WHERE phone_type = 'H1'
AND parent_id = papf.person_id
AND TRUNC(g_effective_date) BETWEEN date_from AND NVL(date_to, TO_DATE ('31-Dec-4712'))
)
)
)
--V2.2 End
AND ( TRUNC(g_effective_date) BETWEEN NVL(ppm.date_from, g_effective_date) AND NVL(ppm.date_to, TO_DATE('31-Dec-4712')) --V1.7
--V2.2 Start
OR ( NVL (ppm.date_to, TO_DATE ('31-Dec-4712')) < g_effective_date
AND NOT EXISTS ( SELECT 'Y'
FROM per_phones
WHERE phone_type = 'M'
AND parent_id = papf.person_id
AND NVL(date_from, g_effective_date) < NVL(ppm.date_to, TO_DATE ('31-Dec-4712'))
)
)
OR ( NVL (ppm.date_from, TO_DATE ('31-Dec-4712')) > g_effective_date
AND NOT EXISTS ( SELECT 'Y'
FROM per_phones
WHERE phone_type = 'M'
AND parent_id = papf.person_id
AND TRUNC(g_effective_date) BETWEEN date_from AND NVL(date_to, TO_DATE ('31-Dec-4712'))
)
)
)
--V2.2 End
--V1.7 End
AND ( TRUNC(g_effective_date) BETWEEN NVL(ppw.date_from, g_effective_date) AND NVL(ppw.date_to, TO_DATE('31-Dec-4712')) --V1.7
--V2.2 Start
OR ( NVL (ppw.date_to, TO_DATE ('31-Dec-4712')) < g_effective_date
AND NOT EXISTS ( SELECT 'Y'
FROM per_phones
WHERE phone_type = 'W1'
AND parent_id = papf.person_id
AND NVL(date_from, g_effective_date) < NVL(ppw.date_to, TO_DATE ('31-Dec-4712'))
)
)
OR ( NVL (ppw.date_from, TO_DATE ('31-Dec-4712')) > g_effective_date
AND NOT EXISTS ( SELECT 'Y'
FROM per_phones
WHERE phone_type = 'W1'
AND parent_id = papf.person_id
AND TRUNC(g_effective_date) BETWEEN date_from AND NVL(date_to, TO_DATE ('31-Dec-4712'))
)
)
)
--V2.2 End
AND flv.lookup_type = 'xxaa_abc_STORE_CONTROL'
AND flv.enabled_flag = 'Y'
AND flv.lookup_code = SUBSTR(haou.name,2,4)
AND flv.attribute2 IS NOT NULL
AND flv.security_group_id = 0
AND To_date(flv.attribute2,'RRRR/MM/DD HH24:MI:SS') <= g_effective_date
AND flvd.lookup_type(+) = 'xxaa_ABC_DEPT_MAPPING' --V4.1
AND flvd.enabled_flag(+) = 'Y'
AND flvd.security_group_id(+) = 0
AND flvd.lookup_code(+) = paaf.ass_attribute1
AND flvw.lookup_type = 'EMP_CAT'
AND flvw.enabled_flag='Y'
AND flvw.lookup_code = paaf.employment_category
AND dept_map.dept_code (+) = paaf.ass_attribute1 --V4.1
AND xra.person_id(+) = papf.person_id --V4.2
AND NVL(xra.version_number, 1) = NVL((SELECT MAX(version_number) FROM xxaa_rtw_audit WHERE person_id = xra.person_id), 1)
AND flv.lookup_code = NVL(TO_CHAR(p_store_id), flv.lookup_code)
AND NOT EXISTS ( SELECT 'Y'
FROM xxaa_abc_emp_header_tab xkeiht
WHERE 1=1
--AND xkeiht.store_id = SUBSTR(haou.name,2,4)
AND xkeiht.store_id = flv.lookup_code
)
UNION ALL --V4.4
--Incremental (Convenience + Supermarket) + Transfer to Live
SELECT /*+ PARALLEL */ --V4.4
papf.person_id
, paaf.assignment_id
, papf.employee_number
, papf.national_identifier ni_number
, papf.title
, papf.first_name
, papf.last_name
, TO_CHAR(papf.date_of_birth, 'DD-MON-YYYY') date_of_birth --V5.5
, MONTHS_BETWEEN(g_effective_date, papf.date_of_birth)/12 age --V4.2
, ppg.segment4 payroll_number
, haou.name organization
, hla.location_code location
, pj.name job
, pg.name grade
, paaf.normal_hours
, DECODE (paaf.ass_attribute18, 'Y', 'Yes', 'N', 'No', NULL) trade_union_shop_steward
, ppos.date_start date_start
, ROUND (MONTHS_BETWEEN (g_effective_date, ppos.adjusted_svc_date) / 12, 2) length_of_service
, pa.address_line1 addr_line_1
, pa.address_line2 addr_line_2
, pa.address_line3 addr_line_3
, pa.town_or_city city
, pa.region_1 county
, pa.postal_code postal_code
, pa.country country
, pa.date_from address_date_from
, pph.phone_number home_phone_number
, pph.date_from home_phone_date_from
, ppm.phone_number mobile_phone_number
, ppm.date_from mobile_phone_date_from
, ppw.phone_number work_phone_number
, ppw.date_from work_phone_date_from
, papf.email_address email_address
, SUBSTR (haou.name, 2, 4) store_number
, 'TRUE' workforce_timekeeper_license
, UPPER (SUBSTR (papf.middle_names, 1, 1)) middle_initial
, past.user_status employment_status
, paaf.effective_start_date empl_status_effective_date
, 'TRUE' biometric_employee_flag
, flv.attribute1 channel
, flv.attribute5 zone
, flv.attribute6 region
, flv.attribute7 area
, FLV.ATTRIBUTE8 STORE_CODE
, DECODE(FLV.ATTRIBUTE9,'Retail',DEPT_MAP.DEPT_DISPLAY,'Convenience','Store',
'Small Retail',NVL(FLVD.ATTRIBUTE2,DEPT_MAP.DEPT_DISPLAY)) department --V4.1 and V4.8
, SUBSTR (pj.name, 1,5) job_code
, flv.attribute1
|| '/'
|| flv.attribute5
|| '/'
|| flv.attribute6
|| '/'
|| flv.attribute7
|| '/'
|| flv.attribute8
|| '/'
|| DECODE (flv.attribute9, 'Convenience', 'Store', 'Small Retail',NVL(FLVD.ATTRIBUTE2,DEPT_MAP.DEPT_DISPLAY),'Retail',DEPT_MAP.DEPT_DISPLAY) --V4.1 and V4.8
|| '/'
|| SUBSTR (pj.name, 2, 4) primary_labour_account
, paaf.effective_start_date pri_lab_account_effective_date
, flv.attribute11 device_group
, DECODE( NVL(paaf.ass_attribute6, 'NA'), 'NA', NULL , SUBSTR(haou.name,2,4)||paaf.ass_attribute6) BADGE_NUMBER
, DECODE( NVL(paaf.ass_attribute6, 'NA'), 'NA', NULL, TO_CHAR(paaf.effective_start_date, 'DD-MON-YYYY')|| ' 00:00') badge_number_effective_date
, 'Punch Capture' pay_rule
, flv.attribute2 live_date
--, paaf.effective_start_date role_hire_date
, ppos.date_start role_hire_date --V3.8
, 'TRUE' clock_indicator
, flv.attribute9 store_type
, 'INCREMENTAL' type
, NULL attribute1
, TO_CHAR('01-JAN-3000') employment_expiration_date
, DECODE( NVL(paaf.ass_attribute6, 'NA'), 'NA', NULL, TO_CHAR('01-JAN-3000 00:00')) badge_expiration_date
, TO_CHAR('01-JAN-3000') labour_account_expiration_date
--, TO_CHAR((SYSDATE),'DD-MON-YYYY') pay_rule_effective_date --V4.2
, TO_CHAR( paaf.effective_start_date,'DD-MON-YY') pay_rule_effective_date --V4.2
, flv.lookup_code || '-' || UPPER(flv.meaning) job_acc_segment7 --V2.3
, SUBSTR(pj.name,INSTR(pj.name,'|')+1,INSTR(SUBSTR(pj.name,INSTR(pj.name,'|')+1),'|',-1)-1) job_name --V2.3
, TO_CHAR('01-JAN-3000') pri_job_account_exp_date --V2.3
, 'Sample'
|| '/'
|| 'Retail'
|| '/'
|| flv.attribute5
|| '/'
|| flv.attribute6
|| '/'
|| flv.attribute7
|| '/'
|| flv.attribute1
|| '/'
|| flv.attribute13
|| '/'
--|| DECODE(flv.attribute9, 'Retail', flvd.description, 'Small Retail', flvd.tag, 'Convenience', 'Store') --V4.1
||DECODE(FLV.ATTRIBUTE9, 'Retail',DEPT_MAP.DEPT_DISPLAY,'Convenience', 'Store',
'Small Retail',NVL(FLVD.ATTRIBUTE2,DEPT_MAP.DEPT_DISPLAY)) --V4.1 and V4.8
|| '/'
|| SUBSTR (pj.name,INSTR (pj.name, '|') + 1,INSTR (SUBSTR (pj.name, INSTR (pj.name, '|') + 1), '|', -1) - 1) primary_job_account
, TO_CHAR ((paaf.effective_start_date), 'DD-MON-YYYY') pri_job_account_effective_date
, flvw.meaning worker_type
, xra.rtw_type right_to_work_type --V4.2
, flv.attribute14 curr_store_cps_config --V4.2
, flv.attribute15 old_store_cps_config --V4.2
, 'INCREMENTAL' cur_query
, '7' rec_priority --V4.4
FROM per_all_people_f papf
, per_all_assignments_f paaf
, per_assignment_status_types past
, per_periods_of_service ppos
, pay_people_groups ppg
, hr_all_organization_units haou
, hr_locations_all hla
, per_jobs pj
, per_grades pg
, per_addresses pa
, per_phones ppm
, per_phones pph
, per_phones ppw
, fnd_lookup_values flv
, fnd_lookup_values flvd
, fnd_lookup_values flvw
,(SELECT ffvt.flex_value_meaning dept_code
, ffvt.description dept_display
FROM fnd_flex_value_sets ffvs
, fnd_flex_values ffv
, fnd_flex_values_tl ffvt
WHERE ffv.flex_value_set_id = ffvs.flex_value_set_id
AND ffv.flex_value_id = ffvt.flex_value_id
AND ffvs.flex_value_set_name = 'ABC_RETAIL_DEPT'
) dept_map --V4.1
, xxaa_rtw_audit xra --V4.2
WHERE 1=1
AND papf.person_id = paaf.person_id
AND paaf.person_id = NVL(p_person_id, paaf.person_id)
AND papf.current_employee_flag = 'Y'
AND paaf.assignment_type = 'E'
AND paaf.primary_flag = 'Y'
AND paaf.ass_attribute30 IN ('A','P') --V3.6
AND g_effective_date between papf.effective_start_date and papf.effective_end_date
AND g_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND past.assignment_status_type_id = paaf.assignment_status_type_id
AND papf.person_id = ppos.person_id
AND ppos.date_start = (SELECT ppos1.date_start
FROM per_periods_of_service ppos1
WHERE ppos1.person_id = papf.person_id
AND g_effective_date BETWEEN ppos1.date_start
AND NVL(ppos1.actual_termination_date
, TO_DATE('31-Dec-4712'))
)
AND ppg.segment3 IN ('Retail 4 Weekly','Retail Salaried')
AND paaf.people_group_id = ppg.people_group_id
AND paaf.organization_id = haou.organization_id
AND paaf.location_id = hla.location_id
AND paaf.job_id = pj.job_id
AND paaf.grade_id = pg.grade_id
AND papf.person_id = pa.person_id
AND pa.primary_flag = 'Y'
AND g_effective_date between pa.date_from and NVL(pa.date_to, to_date('31-Dec-4712'))
--V1.7 Start
AND ppm.phone_type(+) = 'M'
AND ppm.parent_table(+) = 'PER_ALL_PEOPLE_F'
AND ppm.parent_id(+) = papf.person_id
AND pph.phone_type(+) = 'H1'
AND pph.parent_table(+) = 'PER_ALL_PEOPLE_F'
AND pph.parent_id(+) = papf.person_id
AND ppw.phone_type(+) = 'W1'
AND ppw.parent_table(+) = 'PER_ALL_PEOPLE_F'
AND ppw.parent_id(+) = papf.person_id
AND ( TRUNC(g_effective_date) BETWEEN NVL(pph.date_from, g_effective_date) AND NVL(pph.date_to, TO_DATE('31-Dec-4712')) --V1.7
--V2.2 Start
OR ( NVL (pph.date_to, TO_DATE ('31-Dec-4712')) < g_effective_date
AND NOT EXISTS ( SELECT 'Y'
FROM per_phones
WHERE phone_type = 'H1'
AND parent_id = papf.person_id
AND NVL(date_from, g_effective_date) < NVL(pph.date_to, TO_DATE ('31-Dec-4712'))
)
)
OR ( NVL (pph.date_from, TO_DATE ('31-Dec-4712')) > g_effective_date
AND NOT EXISTS ( SELECT 'Y'
FROM per_phones
WHERE phone_type = 'H1'
AND parent_id = papf.person_id
AND TRUNC(g_effective_date) BETWEEN date_from AND NVL(date_to, TO_DATE ('31-Dec-4712'))
)
)
)
--V2.2 End
AND ( TRUNC(g_effective_date) BETWEEN NVL(ppm.date_from, g_effective_date) AND NVL(ppm.date_to, TO_DATE('31-Dec-4712')) --V1.7
--V2.2 Start
OR ( NVL (ppm.date_to, TO_DATE ('31-Dec-4712')) < g_effective_date
AND NOT EXISTS ( SELECT 'Y'
FROM per_phones
WHERE phone_type = 'M'
AND parent_id = papf.person_id
AND NVL(date_from, g_effective_date) < NVL(ppm.date_to, TO_DATE ('31-Dec-4712'))
)
)
OR ( NVL (ppm.date_from, TO_DATE ('31-Dec-4712')) > g_effective_date
AND NOT EXISTS ( SELECT 'Y'
FROM per_phones
WHERE phone_type = 'M'
AND parent_id = papf.person_id
AND TRUNC(g_effective_date) BETWEEN date_from AND NVL(date_to, TO_DATE ('31-Dec-4712'))
)
)
)
--V2.2 End
--V1.7 End
AND ( TRUNC(g_effective_date) BETWEEN NVL(ppw.date_from, g_effective_date) AND NVL(ppw.date_to, TO_DATE('31-Dec-4712')) --V1.7
--V2.2 Start
OR ( NVL (ppw.date_to, TO_DATE ('31-Dec-4712')) < g_effective_date
AND NOT EXISTS ( SELECT 'Y'
FROM per_phones
WHERE phone_type = 'W1'
AND parent_id = papf.person_id
AND NVL(date_from, g_effective_date) < NVL(ppw.date_to, TO_DATE ('31-Dec-4712'))
)
)
OR ( NVL (ppw.date_from, TO_DATE ('31-Dec-4712')) > g_effective_date
AND NOT EXISTS ( SELECT 'Y'
FROM per_phones
WHERE phone_type = 'W1'
AND parent_id = papf.person_id
AND TRUNC(g_effective_date) BETWEEN date_from AND NVL(date_to, TO_DATE ('31-Dec-4712'))
)
)
)
--V2.2 End
AND flv.lookup_type = 'xxaa_abc_STORE_CONTROL'
AND flv.enabled_flag = 'Y'
AND flv.lookup_code = SUBSTR(haou.name,2,4)
AND flv.attribute2 IS NOT NULL
AND flv.security_group_id = 0
AND To_date(flv.attribute2,'RRRR/MM/DD HH24:MI:SS') <= g_effective_date
AND flvd.lookup_type(+) = 'xxaa_ABC_DEPT_MAPPING' --V4.1
AND flvd.enabled_flag(+) = 'Y'
AND flvd.security_group_id(+) = 0
AND flvd.lookup_code(+) = paaf.ass_attribute1
AND flvw.lookup_type = 'EMP_CAT'
AND flvw.enabled_flag='Y'
AND flvw.lookup_code = paaf.employment_category
AND dept_map.dept_code (+) = paaf.ass_attribute1 --V4.1
AND xra.person_id(+) = papf.person_id --V4.2
AND NVL(xra.version_number, 1) = NVL((SELECT MAX(version_number) FROM xxaa_rtw_audit WHERE person_id = xra.person_id), 1)
AND flv.lookup_code = NVL(TO_CHAR(p_store_id),flv.lookup_code)
AND EXISTS ( SELECT 'Y'
FROM xxaa_abc_emp_header_tab xkeiht
WHERE 1=1
--AND xkeiht.store_id = SUBSTR(haou.name,2,4)
AND xkeiht.store_id = flv.lookup_code
)
AND ( papf.last_update_date BETWEEN l_last_run_date AND l_curr_run_date
OR paaf.last_update_date BETWEEN l_last_run_date AND l_curr_run_date
OR pph.last_update_date BETWEEN l_last_run_date AND l_curr_run_date
OR ppm.last_update_date BETWEEN l_last_run_date AND l_curr_run_date
OR ppw.last_update_date BETWEEN l_last_run_date AND l_curr_run_date
OR pa.last_update_date BETWEEN l_last_run_date AND l_curr_run_date
OR xra.last_update_date BETWEEN l_last_run_date AND l_curr_run_date --V4.8
OR (flv.last_update_date BETWEEN l_last_run_date AND l_curr_run_date
AND flv.attribute14 <> flv.attribute15)
OR papf.effective_start_date = TRUNC(g_effective_date)
OR paaf.effective_start_date = TRUNC(g_effective_date)
OR pph.date_from = TRUNC(g_effective_date)
OR ppm.date_from = TRUNC(g_effective_date)
OR pa.date_from = TRUNC(g_effective_date)
)
UNION ALL --V4.4
--Termination
SELECT /*+ PARALLEL */ --V4.4
xket.person_id
, xket.assignment_id
, xket.employee_number
, xket.ni_number
, xket.title
, xket.first_name
, xket.last_name
, TO_CHAR(xket.date_of_birth, 'DD-MON-YYYY') date_of_birth --V5.5
, xket.age
, xket.payroll_number
, xket.organization
, xket.location
, xket.job
, xket.grade
, xket.normal_hours
, xket.trade_union_shop_steward
, xket.date_start date_start
, TO_NUMBER(xket.length_of_service) length_of_service
, xket.addr_line_1
, xket.addr_line_2
, xket.addr_line_3
, xket.city
, xket.county
, xket.postal_code
, xket.country
, xket.address_date_from
, xket.home_phone_number
, xket.home_phone_date_from
, xket.mobile_phone_number
, xket.mobile_phone_date_from
, xket.work_phone_number
, xket.work_phone_date_from
, xket.email_address
, xket.store_number
, 'FALSE' workforce_timekeeper_license--xket.workforce_timekeeper_license --V1.9
, xket.middle_initial
, 'Terminate Assignment' employment_status
, ppos.actual_termination_date+1 empl_status_effective_date
, xket.biometric_employee_flag
, xket.channel
, xket.zone
, xket.region
, xket.area
, xket.store
, xket.department
, xket.job_code
, xket.primary_labour_account
, TO_DATE(xket.pri_lab_account_effective_date) pri_lab_account_effective_date
, xket.device_group
, xket.badge_number
--, TO_CHAR(ppos.actual_termination_date, 'DD-MON-YYYY HH:MM') badge_number_effective_date--xket.badge_number_effective_date V1.3
, TO_CHAR(xket.badge_number_effective_date) badge_number_effective_date
, xket.pay_rule
, NULL
, TO_DATE(xket.role_hire_date) role_hire_date
, xket.clock_indicator
, xket.store_type
, 'INCREMENTAL' type
, 'TERMINATED' attribute1
--, TO_CHAR(ppos.actual_termination_date+1, 'DD-MON-YYYY') employment_expiration_date
, TO_CHAR('01-JAN-3000') employment_expiration_date
, DECODE( NVL(xket.badge_number, 'NA'), 'NA', NULL, TO_CHAR(ppos.actual_termination_date, 'DD-MON-YYYY') || ' 23:59')badge_expiration_date
--, TO_CHAR('01-JAN-3000') labour_account_expiration_date
, TO_CHAR(ppos.actual_termination_date, 'DD-MON-YYYY') || ' 23:59' labour_account_expiration_date --V5.4
, xket.pay_rule_effective_date pay_rule_effective_date
, xket.job_acc_segment7 job_acc_segment7 --V2.3
, xket.job_name job_name --V2.3
, TO_CHAR(ppos.actual_termination_date, 'DD-MON-YYYY') || ' 23:59' pri_job_account_exp_date --V2.3
, xket.primary_job_account primary_job_account
, xket.pri_job_account_effective_date pri_job_account_effective_date
, xket.worker_type worker_type
, xket.right_to_work_type right_to_work_type --V4.2
, xket.curr_store_cps_config curr_store_cps_config --V4.2
, xket.old_store_cps_config old_store_cps_config --V4.2
, 'TERMINATION' cur_query
, '1' rec_priority --V4.4
FROM per_periods_of_service ppos
, xxaa_abc_emp_tab xket
WHERE 1=1 --ppos.person_id = 6463366
AND NVL(ppos.actual_termination_date, TO_DATE('01-Jan-1951')) <= g_effective_date
--V2.0 Start
--AND ppos.last_update_date BETWEEN l_last_run_date AND l_curr_run_date
AND (( ppos.last_update_date BETWEEN l_last_run_date AND l_curr_run_date)
OR (ppos.actual_termination_date = TRUNC(g_effective_date))
)
--V2.0 End
AND xket.person_id = ppos.person_id
AND ppos.person_id = NVL(p_person_id, ppos.person_id)
AND ppos.actual_termination_date IS NOT NULL --V1.8
AND xket.record_id = ( SELECT MAX(record_id)
FROM xxaa_abc_emp_tab
WHERE person_id = ppos.person_id
)
UNION ALL --V4.4
--Transfer to Non-Live
SELECT /*+ PARALLEL */ --V4.4
xket.person_id
, xket.assignment_id
, xket.employee_number
, xket.ni_number
, xket.title
, xket.first_name
, xket.last_name
, TO_CHAR(xket.date_of_birth, 'DD-MON-YYYY') --V5.5
, xket.age
, xket.payroll_number
, xket.organization
, xket.location
, xket.job
, xket.grade
, xket.normal_hours
, xket.trade_union_shop_steward
, xket.date_start
, TO_NUMBER(xket.length_of_service) length_of_service
, xket.addr_line_1
, xket.addr_line_2
, xket.addr_line_3
, xket.city
, xket.county
, xket.postal_code
, xket.country
, xket.address_date_from
, xket.home_phone_number
, xket.home_phone_date_from
, xket.mobile_phone_number
, xket.mobile_phone_date_from
, xket.work_phone_number
, xket.work_phone_date_from
, xket.email_address
, xket.store_number
, 'FALSE' workforce_timekeeper_license --xket.workforce_timekeeper_license--V1.9
, xket.middle_initial
, 'Terminate Assignment'
--, paaf.effective_start_date--V1.3
, TO_DATE(DECODE(NVL(paaf.ass_attribute6, 'NA'), 'NA', paaf.effective_start_date, ppos.date_start)) empl_status_effective_date
, xket.biometric_employee_flag
, xket.channel
, xket.zone
, xket.region
, xket.area
, xket.store
, xket.department
, xket.job_code
, xket.primary_labour_account
, TO_DATE(DECODE( NVL(paaf.ass_attribute6, 'NA'), 'NA', xket.pri_lab_account_effective_date, paaf.effective_start_date)) pri_lab_account_effective_date
, xket.device_group
--, DECODE( NVL(paaf.ass_attribute6, 'NA'), 'NA', xket.badge_number, SUBSTR(haou.NAME,2,4)||paaf.ass_attribute6) badge_number --V2.0
, xket.badge_number badge_number --V2.0
, DECODE( NVL(paaf.ass_attribute6, 'NA'), 'NA', TO_CHAR(xket.badge_number_effective_date),
TO_CHAR(paaf.effective_start_date, 'DD-MON-YYYY')|| ' 00:00') badge_number_effective_date
, xket.pay_rule
, NULL live_date
, TO_DATE(xket.role_hire_date) role_hire_date
, xket.clock_indicator
, xket.store_type
, 'INCREMENTAL' type
, 'TRANSFERRED' attribute1
, TO_CHAR('01-JAN-3000') employment_expiration_date
, DECODE( NVL(paaf.ass_attribute6, 'NA'), 'NA', TO_CHAR(paaf.effective_start_date-1, 'DD-MON-YYYY')||' 23:59') badge_expiration_date
, TO_CHAR('01-JAN-3000') labour_account_expiration_date
, xket.pay_rule_effective_date pay_rule_effective_date
, xket.job_acc_segment7 job_acc_segment7 --V2.3
, xket.job_name job_name --V2.3
, DECODE( NVL(paaf.ass_attribute6, 'NA'), 'NA', TO_CHAR(paaf.effective_start_date-1, 'DD-MON-YYYY')||' 23:59', TO_CHAR('01-JAN-3000')) pri_job_account_exp_date
, xket.primary_job_account primary_job_account
, xket.pri_job_account_effective_date pri_job_account_effective_date
, xket.worker_type worker_type
, xket.right_to_work_type right_to_work_type --V4.2
, xket.curr_store_cps_config curr_store_cps_config --V4.2
, xket.old_store_cps_config old_store_cps_config --V4.2
, 'TRANSFER_TO_NON_LIVE' cur_query
, '2' rec_priority --V4.4
FROM per_all_people_f papf
, per_all_assignments_f paaf
, per_periods_of_service ppos
, hr_all_organization_units haou
, xxaa_abc_emp_tab xket
WHERE 1 = 1
AND papf.person_id = paaf.person_id
AND papf.person_id = ppos.person_id
AND paaf.organization_id = haou.organization_id
AND paaf.person_id = xket.person_id
AND ( paaf.location_id NOT IN ( SELECT attribute3
FROM fnd_lookup_values flv
WHERE lookup_type = 'xxaa_abc_STORE_CONTROL'
AND attribute2 IS NOT NULL
)
)
AND papf.current_employee_flag = 'Y'
AND paaf.assignment_type = 'E'
AND paaf.ass_attribute30 IN ('A','P') --V4.4
AND paaf.primary_flag = 'Y'
AND paaf.effective_start_date <= g_effective_date --V2.0
AND papf.effective_start_date <= g_effective_date --V2.0
AND xket.attribute1 IS NULL -- 'TRANSFERRED'
AND xket.record_id = ( SELECT MAX(record_id)
FROM xxaa_abc_emp_tab
WHERE person_id = paaf.person_id
)
AND paaf.person_id = NVL(p_person_id, paaf.person_id)
AND ppos.date_start = ( SELECT ppos1.date_start
FROM per_periods_of_service ppos1
WHERE ppos1.person_id = papf.person_id
AND g_effective_date BETWEEN ppos1.date_start AND NVL ( ppos1.actual_termination_date,TO_DATE ('31-Dec-4712'))
)
AND g_effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
AND g_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND SUBSTR(haou.name, 2, 4) = NVL (TO_CHAR (p_store_id), SUBSTR(haou.name, 2, 4))
AND ( papf.last_update_date BETWEEN l_last_run_date AND l_curr_run_date
OR paaf.last_update_date BETWEEN l_last_run_date AND l_curr_run_date
OR paaf.effective_start_date = TRUNC(g_effective_date)
--V2.0 End
)
UNION ALL --V4.4
--Age_18
SELECT /*+ PARALLEL */ --V4.4
xket.person_id
, xket.assignment_id
, xket.employee_number
, xket.ni_number
, xket.title
, xket.first_name
, xket.last_name
, TO_CHAR(xket.date_of_birth, 'DD-MON-YYYY') --V5.5
, MONTHS_BETWEEN(g_effective_date, papf.date_of_birth)/12 age --V4.2
, xket.payroll_number
, xket.organization
, xket.location
, xket.job
, xket.grade
, xket.normal_hours
, xket.trade_union_shop_steward
, xket.date_start
, TO_NUMBER(xket.length_of_service) length_of_service
, xket.addr_line_1
, xket.addr_line_2
, xket.addr_line_3
, xket.city
, xket.county
, xket.postal_code
, xket.country
, xket.address_date_from
, xket.home_phone_number
, xket.home_phone_date_from
, xket.mobile_phone_number
, xket.mobile_phone_date_from
, xket.work_phone_number
, xket.work_phone_date_from
, xket.email_address
, xket.store_number
, xket.workforce_timekeeper_license--V1.9
, xket.middle_initial
, xket.employment_status
, TO_DATE(xket.empl_status_effective_date) empl_status_effective_date
, xket.biometric_employee_flag
, xket.channel
, xket.zone
, xket.region
, xket.area
, xket.store
, xket.department
, xket.job_code
, xket.primary_labour_account
, TO_DATE(xket.pri_lab_account_effective_date)
, xket.device_group
, xket.badge_number
, xket.badge_number_effective_date
, xket.pay_rule
, NULL live_date
, TO_DATE(xket.role_hire_date) role_hire_date
, xket.clock_indicator
, xket.store_type
, 'INCREMENTAL' type
, NULL attribute1
, TO_CHAR('01-JAN-3000') employment_expiration_date
, xket.badge_expiration_date
, TO_CHAR('01-JAN-3000') labour_account_expiration_date
, xket.pay_rule_effective_date pay_rule_effective_date
, xket.job_acc_segment7 job_acc_segment7 --V2.3
, xket.job_name job_name --V2.3
, xket.pri_job_account_exp_date
, xket.primary_job_account
, xket.pri_job_account_effective_date
, xket.worker_type
, xket.right_to_work_type
, xket.curr_store_cps_config
, xket.old_store_cps_config
, 'AGE_18' cur_query
, '6' rec_priority --V4.4
FROM per_all_people_f papf
, xxaa_abc_emp_tab xket
WHERE 1 = 1
AND papf.current_employee_flag = 'Y'
AND papf.person_id = NVL(p_person_id, papf.person_id)
AND MONTHS_BETWEEN(g_effective_date, papf.date_of_birth)/12 = 18
AND g_effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.last_update_date NOT BETWEEN l_last_run_date AND l_curr_run_date
AND xket.attribute1 IS NULL
AND xket.record_id = ( SELECT MAX(record_id)
FROM xxaa_abc_emp_tab
WHERE person_id = papf.person_id)
UNION ALL --V4.4
--Initial Future for a store which went live earlier (New hire with future date)
SELECT /*+ PARALLEL */ --V4.4
papf.person_id
, paaf.assignment_id
, papf.employee_number
, papf.national_identifier ni_number
, papf.title
, papf.first_name
, papf.last_name
, TO_CHAR(papf.date_of_birth, 'DD-MON-YYYY') date_of_birth --V5.5
, MONTHS_BETWEEN(g_effective_date, papf.date_of_birth)/12 age --V4.2
, ppg.segment4 payroll_number
, haou.name organization
, hla.location_code location
, pj.name job
, pg.name grade
, paaf.normal_hours
, DECODE (paaf.ass_attribute18, 'Y', 'Yes', 'N', 'No', NULL) trade_union_shop_steward
, ppos.date_start date_start
, ROUND (MONTHS_BETWEEN (g_effective_date, ppos.adjusted_svc_date) / 12, 2) length_of_service
, pa.address_line1 addr_line_1
, pa.address_line2 addr_line_2
, pa.address_line3 addr_line_3
, pa.town_or_city city
, pa.region_1 county
, pa.postal_code postal_code
, pa.country country
, pa.date_from address_date_from
, pph.phone_number home_phone_number
, pph.date_from home_phone_date_from
, ppm.phone_number mobile_phone_number
, ppm.date_from mobile_phone_date_from
, ppw.phone_number work_phone_number
, ppw.date_from work_phone_date_from
, papf.email_address email_address
, SUBSTR (haou.name, 2, 4) store_number
, 'TRUE' workforce_timekeeper_license
, UPPER (SUBSTR (papf.middle_names, 1, 1)) middle_initial
, past.user_status employment_status
, paaf.effective_start_date empl_status_effective_date
, 'TRUE' biometric_employee_flag
, flv.attribute1 channel
, flv.attribute5 zone
, flv.attribute6 region
, flv.attribute7 area
, FLV.ATTRIBUTE8 STORE_CODE
, DECODE(FLV.ATTRIBUTE9,'Retail',DEPT_MAP.DEPT_DISPLAY,'Convenience','Store',
'Small Retail',NVL(FLVD.ATTRIBUTE2,DEPT_MAP.DEPT_DISPLAY)) department --V4.1 and V4.8
, SUBSTR (pj.name, 1,5) job_code
, flv.attribute1
|| '/'
|| flv.attribute5
|| '/'
|| flv.attribute6
|| '/'
|| flv.attribute7
|| '/'
|| flv.attribute8
|| '/'
|| DECODE (flv.attribute9, 'Convenience', 'Store', 'Small Retail',NVL(FLVD.ATTRIBUTE2,DEPT_MAP.DEPT_DISPLAY),'Retail',DEPT_MAP.DEPT_DISPLAY) --V4.1 and V4.8
|| '/'
|| SUBSTR (pj.name, 2, 4) primary_labour_account
, paaf.effective_start_date pri_lab_account_effective_date
, flv.attribute11 device_group
, DECODE( NVL(paaf.ass_attribute6, 'NA'), 'NA', NULL , SUBSTR(haou.name,2,4)||paaf.ass_attribute6) BADGE_NUMBER
, DECODE( NVL(paaf.ass_attribute6, 'NA'), 'NA', NULL, TO_CHAR(paaf.effective_start_date, 'DD-MON-YYYY')|| ' 00:00') badge_number_effective_date
, 'Punch Capture' pay_rule
, flv.attribute2 live_date
--, paaf.effective_start_date role_hire_date
, ppos.date_start role_hire_date --V3.8
, 'TRUE' clock_indicator
, flv.attribute9 store_type
, 'INCREMENTAL' type
, NULL attribute1
, TO_CHAR('01-JAN-3000') employment_expiration_date
, DECODE( NVL(paaf.ass_attribute6, 'NA'), 'NA', NULL, TO_CHAR('01-JAN-3000 00:00')) badge_expiration_date
, TO_CHAR('01-JAN-3000') labour_account_expiration_date
--, TO_CHAR((SYSDATE),'DD-MON-YYYY') pay_rule_effective_date --V4.2
, TO_CHAR( paaf.effective_start_date,'DD-MON-YY') pay_rule_effective_date --V4.2
, flv.lookup_code || '-' || UPPER(flv.meaning) job_acc_segment7 --V2.3
, SUBSTR(pj.name,INSTR(pj.name,'|')+1,INSTR(SUBSTR(pj.name,INSTR(pj.name,'|')+1),'|',-1)-1) job_name --V2.3
, TO_CHAR('01-JAN-3000') pri_job_account_exp_date --V2.3
, 'Sample'
|| '/'
|| 'Retail'
|| '/'
|| flv.attribute5
|| '/'
|| flv.attribute6
|| '/'
|| flv.attribute7
|| '/'
|| flv.attribute1
|| '/'
|| flv.attribute13
|| '/'
--|| DECODE(flv.attribute9, 'Retail', flvd.description, 'Small Retail', flvd.tag, 'Convenience', 'Store') --V4.1
||DECODE(FLV.ATTRIBUTE9, 'Retail',DEPT_MAP.DEPT_DISPLAY,'Convenience', 'Store',
'Small Retail',NVL(FLVD.ATTRIBUTE2,DEPT_MAP.DEPT_DISPLAY)) --V4.1 and V4.8
|| '/'
|| SUBSTR (pj.name,INSTR (pj.name, '|') + 1,INSTR (SUBSTR (pj.name, INSTR (pj.name, '|') + 1), '|', -1) - 1) primary_job_account
, TO_CHAR ((paaf.effective_start_date), 'DD-MON-YYYY') pri_job_account_effective_date
, flvw.meaning worker_type
, xra.rtw_type right_to_work_type --V4.2
, flv.attribute14 curr_store_cps_config --V4.2
, flv.attribute15 old_store_cps_config --V4.2
, 'FUTURE_HIRE' cur_query
, '4' rec_priority --V4.4
FROM per_all_people_f papf
, per_all_assignments_f paaf
, per_assignment_status_types past
, per_periods_of_service ppos
, pay_people_groups ppg
, hr_all_organization_units haou
, hr_locations_all hla
, per_jobs pj
, per_grades pg
, per_addresses pa
, per_phones ppm
, per_phones pph
, per_phones ppw
, fnd_lookup_values flv
, fnd_lookup_values flvd
, fnd_lookup_values flvw
,(SELECT ffvt.flex_value_meaning dept_code
, ffvt.description dept_display
FROM fnd_flex_value_sets ffvs
, fnd_flex_values ffv
, fnd_flex_values_tl ffvt
WHERE ffv.flex_value_set_id = ffvs.flex_value_set_id
AND ffv.flex_value_id = ffvt.flex_value_id
AND ffvs.flex_value_set_name = 'ABC_RETAIL_DEPT'
) dept_map --V4.1
, xxaa_rtw_audit xra --V4.2
WHERE 1=1
AND papf.person_id = paaf.person_id
AND paaf.person_id = NVL(p_person_id, paaf.person_id)
AND papf.current_employee_flag = 'Y'
AND paaf.assignment_type = 'E'
AND paaf.primary_flag = 'Y'
AND paaf.ass_attribute30 IN ('A','P') --V3.6
--AND g_effective_date between papf.effective_start_date and papf.effective_end_date
--AND g_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND past.assignment_status_type_id = paaf.assignment_status_type_id
AND papf.person_id = ppos.person_id
AND ppos.date_start > g_effective_date
AND ppg.segment3 IN ('Retail 4 Weekly','Retail Salaried')
AND paaf.people_group_id = ppg.people_group_id
AND paaf.organization_id = haou.organization_id
AND paaf.location_id = hla.location_id
AND paaf.job_id = pj.job_id
AND paaf.grade_id = pg.grade_id
AND papf.person_id = pa.person_id
AND pa.primary_flag = 'Y'
--AND pa.date_from > g_effective_date
AND pa.date_to IS NULL
--V1.7 Start
AND ppm.phone_type(+) = 'M'
AND ppm.parent_table(+) = 'PER_ALL_PEOPLE_F'
AND ppm.parent_id(+) = papf.person_id
AND pph.phone_type(+) = 'H1'
AND pph.parent_table(+) = 'PER_ALL_PEOPLE_F'
AND pph.parent_id(+) = papf.person_id
AND ppw.phone_type(+) = 'W1'
AND ppw.parent_table(+) = 'PER_ALL_PEOPLE_F'
AND ppw.parent_id(+) = papf.person_id
AND flv.lookup_type = 'xxaa_abc_STORE_CONTROL'
AND flv.enabled_flag = 'Y'
AND flv.lookup_code = SUBSTR(haou.name,2,4)
AND flv.attribute2 IS NOT NULL
AND flv.security_group_id = 0
AND To_date(flv.attribute2,'RRRR/MM/DD HH24:MI:SS') <= g_effective_date
AND flvd.lookup_type(+) = 'xxaa_ABC_DEPT_MAPPING' --V4.1
AND flvd.enabled_flag(+) = 'Y'
AND flvd.security_group_id(+) = 0
AND flvd.lookup_code(+) = paaf.ass_attribute1
AND flvw.lookup_type = 'EMP_CAT'
AND flvw.enabled_flag='Y'
AND flvw.lookup_code = paaf.employment_category
AND dept_map.dept_code (+) = paaf.ass_attribute1 --V4.1
AND xra.person_id(+) = papf.person_id --V4.2
AND NVL(xra.version_number, 1) = (SELECT NVL(MAX(version_number), 1) FROM xxaa_rtw_audit WHERE person_id = xra.person_id)
AND flv.lookup_code = NVL(TO_CHAR(p_store_id),flv.lookup_code)
AND EXISTS ( SELECT 'Y'
FROM xxaa_abc_emp_header_tab xkeiht
WHERE 1=1
--AND xkeiht.store_id = SUBSTR(haou.name,2,4)
AND xkeiht.store_id = flv.lookup_code
)
AND ( papf.last_update_date BETWEEN l_last_run_date AND l_curr_run_date
AND paaf.last_update_date BETWEEN l_last_run_date AND l_curr_run_date
AND papf.effective_start_date > TRUNC(g_effective_date)
AND paaf.effective_start_date > TRUNC(g_effective_date)
);
CURSOR csr_is_colleague_exist(l_assignment_id NUMBER) --V2.1
IS
SELECT 'Y'
FROM xxaa_abc_emp_tab
WHERE 1 = 1
--AND person_id = l_person_id --V2.1
AND assignment_id = l_assignment_id; --V2.1
CURSOR csr_get_existing_abc_loc(l_person_id NUMBER)
IS
SELECT location
, person_id
, assignment_id
FROM xxaa_abc_emp_tab
WHERE record_id = ( SELECT MAX(record_id)
FROM xxaa_abc_emp_tab
WHERE person_id = l_person_id
);
--V1.6
CURSOR csr_get_eff_date( l_person_id NUMBER
-- , l_location VARCHAR2
)
IS
SELECT badge_number
, badge_number_effective_date
, pay_rule_effective_date
, empl_status_effective_date
, employment_status
, pri_lab_account_effective_date
, primary_labour_account
, pri_job_account_effective_date
, primary_job_account
, location
, person_id
, assignment_id
, curr_store_cps_config
FROM xxaa_abc_emp_tab
WHERE record_id = ( SELECT MAX(record_id)
FROM xxaa_abc_emp_tab
WHERE person_id = l_person_id
--AND location = l_location
AND attribute1 IS NULL
);
--V1.6 End
CURSOR csr_get_emp_status( l_person_id NUMBER)
IS
SELECT employment_status
, empl_status_effective_date
FROM xxaa_abc_emp_tab
WHERE record_id = ( SELECT MAX(record_id)
FROM xxaa_abc_emp_tab
WHERE person_id = l_person_id
);
TYPE emp_details_tbl_type IS TABLE OF csr_get_emp_details%ROWTYPE;
l_emp_details_tab emp_details_tbl_type := emp_details_tbl_type();
TYPE recon_emp_tbl_type IS TABLE OF xxaa_abc_emp_data_stg%ROWTYPE;
l_recon_emp_tab recon_emp_tbl_type := recon_emp_tbl_type();
l_recon_emp_live_tab recon_emp_tbl_type := recon_emp_tbl_type();
CURSOR csr_get_existing_rec(l_person_id NUMBER)
IS
SELECT *
FROM xxaa_abc_emp_tab
WHERE record_id = ( SELECT MAX(record_id)
FROM xxaa_abc_emp_tab
WHERE person_id = l_person_id
);
TYPE l_emp_details_live_tab_type IS TABLE OF csr_get_existing_rec%ROWTYPE;
l_emp_details_live_tab l_emp_details_live_tab_type := l_emp_details_live_tab_type();
CURSOR csr_get_new_eff_date(l_assignment_id NUMBER)
IS
SELECT effective_start_date
FROM per_all_assignments_f
WHERE assignment_id = l_assignment_id
AND assignment_type = 'E'
AND ass_attribute30 IN ('A','P') --V3.6
AND g_effective_date BETWEEN effective_start_date AND effective_end_date;
CURSOR csr_get_pay_rule_date(l_person_id NUMBER)
IS
SELECT pay_rule_effective_date
FROM xxaa_abc_emp_tab
WHERE 1 = 1
AND record_id = ( SELECT MAX(record_id)
FROM xxaa_abc_emp_tab
WHERE person_id = l_person_id
);
CURSOR csr_get_home_phone( p_person_id NUMBER
, p_date_from DATE
)
IS
SELECT date_to
FROM per_phones
WHERE phone_type = 'H1'
AND parent_id = p_person_id
AND date_from = p_date_from;
CURSOR csr_get_mobile_phone( p_person_id NUMBER
, p_date_from DATE
)
IS
SELECT date_to
FROM per_phones
WHERE phone_type = 'M'
AND parent_id = p_person_id
AND date_from = p_date_from;
CURSOR csr_get_work_phone( p_person_id NUMBER
, p_date_from DATE
)
IS
SELECT date_to
FROM per_phones
WHERE phone_type = 'W1'
AND parent_id = p_person_id
AND date_from = p_date_from;
BEGIN
g_effective_date := TO_DATE(p_effective_date,'RRRR/MM/DD HH24:MI:SS');
EXECUTE IMMEDIATE 'TRUNCATE TABLE xxaadata.xxaa_abc_emp_data_stg';
l_start_time := xxaa_abc_util_pkg.get_time;
l_run_number := xxaa_abc_emp_tab_run_s.NEXTVAL;
g_run_number := l_run_number; --V4.3
xxaa_abc_util_pkg.write_in_audit
( p_proc_name => l_proc_name
, p_param1 => NVL(g_effective_date,SYSDATE)
, p_param2 => p_store_id
, p_param3 => l_run_number
, x_audit_id => xxaa_abc_util_pkg.g_audit_id
);
xxaa_abc_util_pkg.write_log ( 'Entering for effective date :'|| g_effective_date|| '; p_store_id: '|| p_store_id || '; p_person_id: '|| p_person_id);
xxaa_abc_util_pkg.start_time;
xxaa_abc_util_pkg.write_debug ('V4.3 1 g_run_number '||g_run_number);
IF p_store_id IS NOT NULL
THEN
OPEN csr_get_location_id;
FETCH csr_get_location_id INTO l_location_id;
CLOSE csr_get_location_id;
xxaa_abc_util_pkg.write_log ('Location_id - '||l_location_id);
END IF;
IF p_person_id IS NOT NULL
THEN
g_debug_mode := TRUE;
END IF;
OPEN csr_get_newly_live_stores;
LOOP
FETCH csr_get_newly_live_stores INTO l_live_store_date;
IF csr_get_newly_live_stores%NOTFOUND
THEN
xxaa_abc_util_pkg.write_log ('No new Live Stores');
EXIT WHEN csr_get_newly_live_stores%NOTFOUND;
ELSIF p_person_id IS NULL
THEN
xxaa_abc_util_pkg.write_log ('Submitting Clock Number Program with the date - '||l_live_store_date);
l_request_id := fnd_request.submit_request
( application => 'xxaaDATA'
, program => 'xxaa_abc_UPD_CONV_CLOCK_NUMBER'
, description => NULL
, start_time => NULL
, sub_request => FALSE
, argument1 => l_live_store_date
, argument2 => p_person_id
, argument3 => p_store_id
);
COMMIT;
xxaa_abc_util_pkg.write_log ('Request submitted - '||l_request_id);
IF l_request_id = 0
THEN
xxaa_abc_util_pkg.write_log ('Request not submitted');
p_ret_code := 1;
p_err_buf := 'Request not submitted';
ELSE
LOOP
l_req_status := fnd_concurrent.wait_for_request
( l_request_id
, 15
, 0
, l_req_phase
, l_status
, l_req_dev_phase
, l_req_dev_status
, l_req_message
);
EXIT
WHEN UPPER(l_req_phase) = 'COMPLETED' OR UPPER (l_status) IN ('CANCELLED', 'ERROR', 'TERMINATED');
END LOOP;
END IF;
xxaa_abc_util_pkg.write_log ('Clock Number Allocation Program Completion status - '||l_status);
xxaa_abc_util_pkg.write_log ('Clock Number Allocation Program Completion Phase - '||l_req_dev_phase);
IF l_status = 'Normal'
THEN
p_ret_code := 0;
END IF;
END IF;
END LOOP;
CLOSE csr_get_newly_live_stores;
l_curr_run_date := NULL;
l_last_run_date := NULL;
OPEN csr_get_last_run_date;
FETCH csr_get_last_run_date INTO l_last_run_date;
CLOSE csr_get_last_run_date;
xxaa_abc_util_pkg.write_log ( 'l_last_run_date :'|| TO_CHAR(l_last_run_date, 'DD-MON-YYYY HH24:MI:SS'));
OPEN csr_get_curr_run_date;
FETCH csr_get_curr_run_date INTO l_curr_run_date;
CLOSE csr_get_curr_run_date;
IF NVL(l_request_id, 0) > 0
THEN
OPEN csr_get_clck_last_run_date;
FETCH csr_get_clck_last_run_date INTO l_curr_run_date;
CLOSE csr_get_clck_last_run_date;
END IF;
xxaa_abc_util_pkg.write_log ( 'l_curr_run_date :'|| TO_CHAR(l_curr_run_date, 'DD-MON-YYYY HH24:MI:SS'));
-- V4.3 Setting run date globals
g_curr_run_date := l_curr_run_date;
g_last_run_date := l_last_run_date;
OPEN csr_get_emp_details;
LOOP
l_emp_details_tab.DELETE;
l_rec_count := 0;
l_rtc := 0;
l_no := 0;
FETCH csr_get_emp_details BULK COLLECT INTO l_emp_details_tab;-- LIMIT g_bulk_limit;
l_rowcount := csr_get_emp_details%ROWCOUNT;
xxaa_abc_util_pkg.write_log('Main query count '|| l_rowcount);
FOR i IN 1..l_emp_details_tab.COUNT
LOOP
l_recon_emp_tab.EXTEND;
l_rtc := l_rtc + 1;
l_rec_count := l_rec_count + 1;
l_transfer_mode := NULL;
l_abc_location := NULL;
IF l_rec_count > g_chunk_size
THEN
l_thread_id := l_thread_id + 1;
l_rec_count := 1;
END IF;
xxaa_abc_util_pkg.write_log('Cursor csr_get_emp_details query '|| l_emp_details_tab(i).cur_query);
xxaa_abc_util_pkg.write_log('Person_ID '|| l_emp_details_tab(i).person_id);
OPEN csr_is_colleague_exist(l_emp_details_tab(i).assignment_id); --V2.1
FETCH csr_is_colleague_exist INTO l_exist;
CLOSE csr_is_colleague_exist;
--V2.0 avoid multiple entries in header table
IF l_emp_details_tab(i).TYPE = 'INCREMENTAL'
THEN
l_header_flag := 'N';
ELSIF l_emp_details_tab(i).TYPE = 'INITIAL'
THEN
l_header_flag := 'Y';
END IF; --V2.0 End
IF NVL(l_exist, 'N') = 'Y'
THEN
l_type := 'INCREMENTAL';
ELSE
l_type := 'INITIAL';
END IF;
--Cursor to fetch values from staging table
OPEN csr_get_eff_date(l_emp_details_tab(i).person_id);
FETCH csr_get_eff_date INTO l_badge_number, l_badge_eff_date, l_pay_effect_date, l_emp_status_eff_date, l_emp_status
, l_pri_lab_date, l_pri_lab_acnt, l_pri_job_date, l_pri_job_acnt, l_abc_location, l_per_id, l_assignment_id, l_old_cps_config;
CLOSE csr_get_eff_date;
--Incremental records
IF NVL(l_exist, 'N') = 'Y'
THEN
--To verify change in previous value with current value
IF l_emp_status <> l_emp_details_tab(i).employment_status
THEN
l_recon_emp_tab(l_rtc).employment_status := l_emp_details_tab(i).employment_status;
l_recon_emp_tab(l_rtc).empl_status_effective_date := l_emp_details_tab(i).empl_status_effective_date;
ELSE
l_recon_emp_tab(l_rtc).employment_status := l_emp_status;
l_recon_emp_tab(l_rtc).empl_status_effective_date := l_emp_status_eff_date;
END IF;
IF NVL(l_badge_number,'NA') <> NVL(l_emp_details_tab(i).badge_number,'NA') --V4.9
THEN
l_recon_emp_tab(l_rtc).badge_number := l_emp_details_tab(i).badge_number;
l_recon_emp_tab(l_rtc).badge_number_effective_date := l_emp_details_tab(i).badge_number_effective_date;
ELSE
l_recon_emp_tab(l_rtc).badge_number := l_badge_number;
l_recon_emp_tab(l_rtc).badge_number_effective_date := l_badge_eff_date;
END IF;
IF l_pri_lab_acnt <> l_emp_details_tab(i).primary_labour_account
THEN
l_recon_emp_tab(l_rtc).primary_labour_account := l_emp_details_tab(i).primary_labour_account;
l_recon_emp_tab(l_rtc).pri_lab_account_effective_date := l_emp_details_tab(i).pri_lab_account_effective_date;
ELSE
l_recon_emp_tab(l_rtc).primary_labour_account := l_pri_lab_acnt;
l_recon_emp_tab(l_rtc).pri_lab_account_effective_date := l_pri_lab_date;
END IF;
IF NVL(l_pri_job_acnt,'X') <> l_emp_details_tab(i).primary_job_account --V4.5
THEN
l_recon_emp_tab(l_rtc).primary_job_account := l_emp_details_tab(i).primary_job_account;
l_recon_emp_tab(l_rtc).pri_job_account_effective_date := l_emp_details_tab(i).pri_job_account_effective_date;
ELSE
l_recon_emp_tab(l_rtc).primary_job_account := l_pri_job_acnt;
l_recon_emp_tab(l_rtc).pri_job_account_effective_date := l_pri_job_date;
END IF;
l_recon_emp_tab(l_rtc).pay_rule_effective_date := l_pay_effect_date;
l_recon_emp_tab(l_rtc).old_store_cps_config := l_old_cps_config; --V4.2
ELSE --Initial records
l_recon_emp_tab(l_rtc).employment_status := l_emp_details_tab(i).employment_status;
l_recon_emp_tab(l_rtc).empl_status_effective_date := l_emp_details_tab(i).empl_status_effective_date;
l_recon_emp_tab(l_rtc).badge_number := l_emp_details_tab(i).badge_number;
l_recon_emp_tab(l_rtc).badge_number_effective_date := l_emp_details_tab(i).badge_number_effective_date;
l_recon_emp_tab(l_rtc).primary_labour_account := l_emp_details_tab(i).primary_labour_account;
l_recon_emp_tab(l_rtc).pri_lab_account_effective_date := l_emp_details_tab(i).pri_lab_account_effective_date;
l_recon_emp_tab(l_rtc).primary_job_account := l_emp_details_tab(i).primary_job_account;
l_recon_emp_tab(l_rtc).pri_job_account_effective_date := l_emp_details_tab(i).pri_job_account_effective_date;
l_recon_emp_tab(l_rtc).pay_rule_effective_date := l_emp_details_tab(i).pay_rule_effective_date;
l_recon_emp_tab(l_rtc).old_store_cps_config := l_emp_details_tab(i).old_store_cps_config; --V4.2
END IF;
--V1.6 End
xxaa_abc_util_pkg.write_log('badge_number '|| l_recon_emp_tab(l_rtc).badge_number);
xxaa_abc_util_pkg.write_log('badge_number_effective_date '|| l_recon_emp_tab(l_rtc).badge_number_effective_date);
xxaa_abc_util_pkg.write_log('primary_labour_account '|| l_recon_emp_tab(l_rtc).primary_labour_account);
xxaa_abc_util_pkg.write_log('pri_lab_account_effective_date '|| l_recon_emp_tab(l_rtc).pri_lab_account_effective_date);
xxaa_abc_util_pkg.write_log('primary_job_account '|| l_recon_emp_tab(l_rtc).primary_job_account);
xxaa_abc_util_pkg.write_log('pri_job_account_effective_date '|| l_recon_emp_tab(l_rtc).pri_job_account_effective_date);
xxaa_abc_util_pkg.write_log('employment_status '|| l_recon_emp_tab(l_rtc).employment_status);
xxaa_abc_util_pkg.write_log('empl_status_effective_date '|| l_recon_emp_tab(l_rtc).empl_status_effective_date);
l_recon_emp_tab(l_rtc).future_use9 := l_emp_details_tab(i).rec_priority; --V4.4
l_recon_emp_tab(l_rtc).future_use10 := l_emp_details_tab(i).cur_query;
l_recon_emp_tab(l_rtc).person_id := l_emp_details_tab(i).person_id;
l_recon_emp_tab(l_rtc).assignment_id := l_emp_details_tab(i).assignment_id;
l_recon_emp_tab(l_rtc).thread_id := l_thread_id;
l_recon_emp_tab(l_rtc).employee_number := l_emp_details_tab(i).employee_number;
l_recon_emp_tab(l_rtc).ni_number := l_emp_details_tab(i).ni_number;
l_recon_emp_tab(l_rtc).title := l_emp_details_tab(i).title;
l_recon_emp_tab(l_rtc).first_name := l_emp_details_tab(i).first_name;
l_recon_emp_tab(l_rtc).last_name := l_emp_details_tab(i).last_name;
l_recon_emp_tab(l_rtc).date_of_birth := l_emp_details_tab(i).date_of_birth;
l_recon_emp_tab(l_rtc).age := l_emp_details_tab(i).age;
l_recon_emp_tab(l_rtc).payroll_number := l_emp_details_tab(i).payroll_number;
l_recon_emp_tab(l_rtc).organization := l_emp_details_tab(i).organization;
l_recon_emp_tab(l_rtc).location := l_emp_details_tab(i).location;
l_recon_emp_tab(l_rtc).job := l_emp_details_tab(i).job;
l_recon_emp_tab(l_rtc).grade := l_emp_details_tab(i).grade;
l_recon_emp_tab(l_rtc).normal_hours := l_emp_details_tab(i).normal_hours;
l_recon_emp_tab(l_rtc).trade_union_shop_steward := l_emp_details_tab(i).trade_union_shop_steward;
l_recon_emp_tab(l_rtc).date_start := l_emp_details_tab(i).date_start;
l_recon_emp_tab(l_rtc).length_of_service := l_emp_details_tab(i).length_of_service;
l_recon_emp_tab(l_rtc).addr_line_1 := l_emp_details_tab(i).addr_line_1;
l_recon_emp_tab(l_rtc).addr_line_2 := l_emp_details_tab(i).addr_line_2;
l_recon_emp_tab(l_rtc).addr_line_3 := l_emp_details_tab(i).addr_line_3;
l_recon_emp_tab(l_rtc).city := l_emp_details_tab(i).city;
l_recon_emp_tab(l_rtc).county := l_emp_details_tab(i).county;
l_recon_emp_tab(l_rtc).postal_code := l_emp_details_tab(i).postal_code;
l_recon_emp_tab(l_rtc).country := l_emp_details_tab(i).country;
l_recon_emp_tab(l_rtc).address_date_from := l_emp_details_tab(i).address_date_from;
--V2.2 Start
OPEN csr_get_home_phone(l_emp_details_tab(i).person_id, l_emp_details_tab(i).home_phone_date_from);
FETCH csr_get_home_phone INTO l_home_phone_end;
CLOSE csr_get_home_phone;
IF l_emp_details_tab(i).home_phone_date_from > TRUNC(g_effective_date)
THEN
l_recon_emp_tab(l_rtc).home_phone_number := NULL;
l_recon_emp_tab(l_rtc).home_phone_date_from := NULL;
ELSE
IF NVL(l_home_phone_end, TO_DATE('31-Dec-4712')) < TRUNC(g_effective_date)
THEN
l_recon_emp_tab(l_rtc).home_phone_number := NULL;
l_recon_emp_tab(l_rtc).home_phone_date_from := NULL;
ELSE
l_recon_emp_tab(l_rtc).home_phone_number := l_emp_details_tab(i).home_phone_number;
l_recon_emp_tab(l_rtc).home_phone_date_from := l_emp_details_tab(i).home_phone_date_from;
END IF;
END IF;
IF l_emp_details_tab(i).rec_priority = 4
THEN
l_recon_emp_tab(l_rtc).home_phone_number := l_emp_details_tab(i).home_phone_number;
l_recon_emp_tab(l_rtc).home_phone_date_from := l_emp_details_tab(i).home_phone_date_from;
END IF;
OPEN csr_get_mobile_phone(l_emp_details_tab(i).person_id, l_emp_details_tab(i).mobile_phone_date_from);
FETCH csr_get_mobile_phone INTO l_mobile_phone_end;
CLOSE csr_get_mobile_phone;
IF l_emp_details_tab(i).mobile_phone_date_from > TRUNC(g_effective_date)
THEN
l_recon_emp_tab(l_rtc).mobile_phone_number := NULL;
l_recon_emp_tab(l_rtc).mobile_phone_date_from := NULL;
ELSE
IF NVL(l_mobile_phone_end, TO_DATE('31-Dec-4712')) < TRUNC(g_effective_date)
THEN
l_recon_emp_tab(l_rtc).mobile_phone_number := NULL;
l_recon_emp_tab(l_rtc).mobile_phone_date_from := NULL;
ELSE
l_recon_emp_tab(l_rtc).mobile_phone_number := l_emp_details_tab(i).mobile_phone_number;
l_recon_emp_tab(l_rtc).mobile_phone_date_from := l_emp_details_tab(i).mobile_phone_date_from;
END IF;
END IF;
--V2.2 End
IF l_emp_details_tab(i).rec_priority = 4
THEN
l_recon_emp_tab(l_rtc).mobile_phone_number := l_emp_details_tab(i).home_phone_number;
l_recon_emp_tab(l_rtc).mobile_phone_date_from := l_emp_details_tab(i).home_phone_date_from;
END IF;
--V2.3 Start
OPEN csr_get_work_phone(l_emp_details_tab(i).person_id, l_emp_details_tab(i).work_phone_date_from);
FETCH csr_get_work_phone INTO l_work_phone_end;
CLOSE csr_get_work_phone;
IF l_emp_details_tab(i).work_phone_date_from > TRUNC(g_effective_date)
THEN
l_recon_emp_tab(l_rtc).work_phone_number := NULL;
l_recon_emp_tab(l_rtc).work_phone_date_from := NULL;
ELSE
IF NVL(l_work_phone_end, TO_DATE('31-Dec-4712')) < TRUNC(g_effective_date)
THEN
l_recon_emp_tab(l_rtc).work_phone_number := NULL;
l_recon_emp_tab(l_rtc).work_phone_date_from := NULL;
ELSE
l_recon_emp_tab(l_rtc).work_phone_number := l_emp_details_tab(i).work_phone_number;
l_recon_emp_tab(l_rtc).work_phone_date_from := l_emp_details_tab(i).work_phone_date_from;
END IF;
END IF;
IF l_emp_details_tab(i).rec_priority = 4
THEN
l_recon_emp_tab(l_rtc).work_phone_number := l_emp_details_tab(i).home_phone_number;
l_recon_emp_tab(l_rtc).work_phone_date_from := l_emp_details_tab(i).home_phone_date_from;
END IF;
l_recon_emp_tab(l_rtc).worker_type := l_emp_details_tab(i).worker_type;
l_recon_emp_tab(l_rtc).right_to_work_type := l_emp_details_tab(i).right_to_work_type; --V4.2
l_recon_emp_tab(l_rtc).curr_store_cps_config := l_emp_details_tab(i).curr_store_cps_config; --V4.2
--l_recon_emp_tab(l_rtc).old_store_cps_config := l_emp_details_tab(i).old_store_cps_config; --V4.2
l_recon_emp_tab(l_rtc).job_acc_segment7 := l_emp_details_tab(i).job_acc_segment7;
l_recon_emp_tab(l_rtc).job_name := l_emp_details_tab(i).job_name;
--V2.3 End
l_recon_emp_tab(l_rtc).email_address := l_emp_details_tab(i).email_address;
l_recon_emp_tab(l_rtc).store_number := l_emp_details_tab(i).store_number;
l_recon_emp_tab(l_rtc).workforce_timekeeper_license := l_emp_details_tab(i).workforce_timekeeper_license;
l_recon_emp_tab(l_rtc).middle_initial := l_emp_details_tab(i).middle_initial;
l_recon_emp_tab(l_rtc).employment_status := l_emp_details_tab(i).employment_status;
--V1.6 commented below
--l_recon_emp_tab(l_rtc).empl_status_effective_date := l_emp_details_tab(i).empl_status_effective_date;
l_recon_emp_tab(l_rtc).biometric_employee_flag := l_emp_details_tab(i).biometric_employee_flag;
l_recon_emp_tab(l_rtc).channel := l_emp_details_tab(i).channel;
l_recon_emp_tab(l_rtc).zone := l_emp_details_tab(i).zone;
l_recon_emp_tab(l_rtc).region := l_emp_details_tab(i).region;
l_recon_emp_tab(l_rtc).area := l_emp_details_tab(i).area;
l_recon_emp_tab(l_rtc).store := l_emp_details_tab(i).store_code;
l_recon_emp_tab(l_rtc).department := l_emp_details_tab(i).department;
l_recon_emp_tab(l_rtc).job_code := l_emp_details_tab(i).job_code;
--V1.6 Commented below
--l_recon_emp_tab(l_rtc).pri_lab_account_effective_date := l_emp_details_tab(i).pri_lab_account_effective_date;
-- l_recon_emp_tab(l_rtc).device_group := l_emp_details_tab(i).device_group;
l_recon_emp_tab(l_rtc).badge_number := l_emp_details_tab(i).badge_number; --V1.2
--V1.6 Commented below
--l_recon_emp_tab(l_rtc).badge_number_effective_date := l_emp_details_tab(i).badge_number_effective_date;
l_recon_emp_tab(l_rtc).pay_rule := l_emp_details_tab(i).pay_rule;
l_recon_emp_tab(l_rtc).live_date := l_emp_details_tab(i).live_date;
l_recon_emp_tab(l_rtc).run_number := l_run_number;
--l_recon_emp_tab(l_rtc).role_hire_date := NVL(l_role_hire_date, l_emp_details_tab(i).role_hire_date);
l_recon_emp_tab(l_rtc).role_hire_date := TO_DATE(l_emp_details_tab(i).role_hire_date, 'DD-MON-YY'); --V3.8
l_recon_emp_tab(l_rtc).clock_indicator := l_emp_details_tab(i).clock_indicator;
l_recon_emp_tab(l_rtc).store_type := l_emp_details_tab(i).store_type;
l_recon_emp_tab(l_rtc).employment_expiration_date := l_emp_details_tab(i).employment_expiration_date;
--l_recon_emp_tab(l_rtc).badge_expiration_date := l_emp_details_tab(i).badge_expiration_date;
--V3.7
IF TO_DATE(l_emp_details_tab(i).badge_expiration_date, 'DD/MM/RRRR HH24:MI:SS') < TO_DATE(l_emp_details_tab(i).badge_number_effective_date, 'DD/MM/RRRR HH24:MI:SS')
THEN
l_recon_emp_tab(l_rtc).badge_expiration_date := TO_CHAR(TRUNC(TO_DATE (l_emp_details_tab(i).badge_number_effective_date, 'DD/MM/RRRR HH24:MI:SS')),'DD-MON-YYYY')||' 23:59';
l_recon_emp_tab(l_rtc).pri_job_account_exp_date := TO_CHAR(TRUNC(TO_DATE (l_pri_job_date, 'DD/MM/RRRR HH24:MI:SS')),'DD-MON-YYYY')||' 23:59';
l_recon_emp_tab(l_rtc).device_group := NULL;
ELSE
l_recon_emp_tab(l_rtc).badge_expiration_date := l_emp_details_tab(i).badge_expiration_date;
l_recon_emp_tab(l_rtc).pri_job_account_exp_date := l_emp_details_tab(i).pri_job_account_exp_date;
l_recon_emp_tab(l_rtc).device_group := l_emp_details_tab(i).device_group;
END IF;
l_recon_emp_tab(l_rtc).labour_account_expiration_date := l_emp_details_tab(i).labour_account_expiration_date;
--V2.1 - Rehire Scenario
IF l_emp_details_tab(i).person_id = l_per_id AND l_emp_details_tab(i).assignment_id <> l_assignment_id
THEN
l_type := 'INCREMENTAL';
END IF;
--V2.1 End
xxaa_abc_util_pkg.write_log('-----------------------------------------------------------------');
l_recon_emp_tab(l_rtc).attribute1 := l_type;
l_recon_emp_tab(l_rtc).attribute2 := l_emp_details_tab(i).attribute1;
l_recon_emp_tab(l_rtc).attribute3 := l_header_flag;
l_recon_emp_tab(l_rtc).creation_date := SYSDATE;
l_recon_emp_tab(l_rtc).last_updated_date := SYSDATE;
l_recon_emp_tab(l_rtc).request_id := g_request_id;
l_recon_emp_tab(l_rtc).last_run_date := l_last_run_date; -- V4.3
l_recon_emp_tab(l_rtc).curr_run_date := l_curr_run_date; -- V4.3
--V2.1 Clear local variables
l_exist := '';
l_abc_location := '';
l_per_id := '';
l_assignment_id := '';
l_badge_eff_date := '';
l_pay_effect_date := '';
l_emp_status_eff_date := '';
l_emp_status := '';
l_pri_lab_date := '';
l_pri_lab_acnt := '';
l_e_status := '';
l_status_eff_date := '';
l_pay_rule_date := '';
l_header_flag := '';
l_location_id := '';
l_live_store_date := '';
l_rowcount := '';
--l_last_run_date := '';
--l_curr_run_date := '';
l_pri_job_acnt := '';
l_pri_job_date := '';
l_old_cps_config := '';
--V2.1 End
END LOOP;
FORALL i IN 1..l_recon_emp_tab.COUNT
INSERT INTO xxaa_abc_emp_data_stg VALUES l_recon_emp_tab(i);
COMMIT;
l_emp_processed := l_rtc + l_no;
l_rtc := 0;
l_recon_emp_tab.DELETE;
EXIT WHEN l_emp_details_tab.COUNT < g_bulk_limit;
END LOOP;
CLOSE csr_get_emp_details;
xxaa_abc_util_pkg.write_log ( 'Number of records Staged - '||l_emp_processed);
xxaa_abc_util_pkg.write_debug ('V4.3 1.2 g_run_number '||g_run_number);
-- V4.3 Populate staging table
pop_past_future_change
(p_person_id => p_person_id
,p_store_id => p_store_id
,p_thread_id => l_thread_id
,p_last_run_date => g_last_run_date
,p_curr_run_date => g_curr_run_date
);
invoke_threads(l_thread_id);
xxaa_abc_util_pkg.write_debug ('V4.3 1.3 g_run_number '||g_run_number);
xxaa_abc_util_pkg.stop_time ('Total for ' || l_proc_name, l_start_time);
EXCEPTION
WHEN OTHERS THEN
xxaa_abc_util_pkg.write_exception ('xxaa_abc_emp_int_pkg.populate_emp_data failed :' || SQLERRM);
xxaa_abc_util_pkg.write_log ( 'SQLERRM - '||SQLERRM);
ROLLBACK;
END populate_emp_data;
PROCEDURE compare_rows ( p_column_list IN VARCHAR2
, p_old_values IN VARCHAR2
, p_new_values IN VARCHAR2
, p_chg_cols OUT VARCHAR2
)
IS
CURSOR c_chg_columns
IS
SELECT LISTAGG (colname, '~') WITHIN GROUP (ORDER BY sequence) diff_cols
FROM ( SELECT LEVEL sequence,
REGEXP_SUBSTR (p_column_list,
'[^~]+',
1,
LEVEL)
colname,
DECODE (
NVL (
REGEXP_SUBSTR (
REPLACE (p_old_values, '~', '~ '), ---V1.5
'[^~]+',
1,
LEVEL),
''),
NVL (
REGEXP_SUBSTR (
REPLACE (p_new_values, '~', '~ '), ---V1.5
'[^~]+',
1,
LEVEL),
''), 'MATCH',
'MISMATCH')
compare
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT (p_column_list, '[^~]+')
)
WHERE compare = 'MISMATCH';
l_chg_cols c_chg_columns%ROWTYPE;
BEGIN
--xxaa_abc_util_pkg.write_debug ('Inside Compare_Rows - '||p_column_list);
OPEN c_chg_columns;
FETCH c_chg_columns INTO l_chg_cols;
CLOSE c_chg_columns;
--xxaa_abc_util_pkg.write_debug ('p_chg_cols - Before Replace - '||p_chg_cols);
p_chg_cols := REPLACE(l_chg_cols.diff_cols, '~', '|');
--xxaa_abc_util_pkg.write_debug ('p_chg_cols - After Replace - '||p_chg_cols);
--xxaa_abc_util_pkg.write_log (p_chg_cols);
END compare_rows;
-- Start of V4.5 --
PROCEDURE pop_curr_old_cps_config(p_record_id IN NUMBER ,p_cur_query IN VARCHAR2, p_curr_store_id IN VARCHAR2,p_prev_store_id IN VARCHAR2,p_old_store_cps_config IN VARCHAR2) IS
CURSOR csr_get_lkp_dtls(p_store_id IN VARCHAR2)
IS
SELECT flv.attribute14
FROM fnd_lookup_values flv
WHERE lookup_type = 'xxaa_abc_STORE_CONTROL'
AND lookup_code=p_store_id;
l_record_id NUMBER;
l_curr_store_cps_config VARCHAR2(5);
l_old_store_cps_config VARCHAR2(5);
BEGIN
l_record_id:=NULL;
l_curr_store_cps_config:=NULL;
l_old_store_cps_config :=NULL;
l_record_id:=p_record_id;
OPEN csr_get_lkp_dtls(p_curr_store_id);
FETCH csr_get_lkp_dtls into l_curr_store_cps_config;
CLOSE csr_get_lkp_dtls;
IF p_curr_store_id<>p_prev_store_id THEN
OPEN csr_get_lkp_dtls(p_prev_store_id);
FETCH csr_get_lkp_dtls into l_old_store_cps_config;
CLOSE csr_get_lkp_dtls;
ELSE
l_old_store_cps_config:=p_old_store_cps_config;
END IF;
IF p_cur_query IN ('TERMINATION','TRANSFER_TO_NON_LIVE','AGE_18','FUTURE')
THEN
UPDATE xxaa_abc_emp_tab
SET curr_store_cps_config=l_curr_store_cps_config,old_store_cps_config=l_old_store_cps_config,chg_col_list=chg_col_list||'|CURR_STORE_CPS_CONFIG'
WHERE record_id = l_record_id;
ELSE
UPDATE xxaa_abc_emp_tab
SET curr_store_cps_config=l_curr_store_cps_config,old_store_cps_config=l_old_store_cps_config
WHERE record_id = l_record_id;
END IF;
xxaa_abc_util_pkg.write_log ( 'Sucessfully updating staging table with store config values for record : '||l_record_id);
EXCEPTION
WHEN OTHERS THEN
xxaa_abc_util_pkg.write_log ( 'Exception while updating staging table with store config values - '||SQLERRM|| ' for record '|| l_record_id);
END;
-- End of V4.5 --
END xxaa_abc_emp_int_pkg;