This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(99.2, 57.99999999999999%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EKJ%3C/text%3E%3C/svg%3E)
dbms_output.put_line(to_char(sysdate, 'hh24:mi:ss') || ' - ' || to_char(SQL%ROWCOUNT) || ' ${TEMP_TBL} rows loaded');
declare
num1 number:=0;
if 1=1 then
dbms_output.put_line('data not inserted in staging table!');
else
/* validate tax_jurisdiction_codes and insert into tmp invalid tax location table for sending email to PeopleSoft */
/* first delete temp invalid tax location table */
delete from ${TEMP_INVALID_TAX_LOC_TBL};
/* new pers_id from PeopleSoft with invalid tax_jurisdiction_code */
insert into ${TEMP_INVALID_TAX_LOC_TBL}
(pers_id, full_name, tax_jurisdiction_code)
select t.pers_id, t.full_name, t.tax_jurisdiction_code
from ${TEMP_TBL} t
where not exists (select 1
from abc.hr_tax_jurisdiction_hist tjh
where t.tax_jurisdiction_code = tjh.tax_jurisdiction_code)
and t.reg_temp_code = 'R'
and t.active_flag = 1
group by t.pers_id, t.full_name, t.tax_jurisdiction_code
order by t.pers_id;
dbms_output.put_line(to_char(sysdate, 'hh24:mi:ss') || ' - ' || to_char(SQL%ROWCOUNT) || ' ${TEMP_INVALID_TAX_LOC_TBL} new rows loaded');
/* deleting the invalid tax_jurisdiction_code pers_id's from tmp_hr_people table */
delete
from ${TEMP_TBL} t
where not exists (select 1
from abc.hr_tax_jurisdiction_hist tjh
where t.tax_jurisdiction_code = tjh.tax_jurisdiction_code)
and t.reg_temp_code = 'R'
and t.active_flag = 1;
dbms_output.put_line(to_char(sysdate, 'hh24:mi:ss') || ' - ' || to_char(SQL%ROWCOUNT) || ' rows deleted from ${TEMP_TBL} table');
/* end validating tax_jurisdiction_code */
update ${TEMP_TBL}
set preferred_first_name = first_name
where ltrim(preferred_first_name) is null;
dbms_output.put_line(to_char(sysdate, 'hh24:mi:ss') || ' - ' || to_char(SQL%ROWCOUNT) || ' pref. first names updated');
dbms_output.put_line(to_char(sysdate, 'hh24:mi:ss') || ' - ' || to_char(SQL%ROWCOUNT) || ' update_ts_flag updated');
declare
cursor fix_name is
select full_name
from ${TEMP_TBL}
for update of full_name;
vv_formated_name varchar2(50);
l_num_rows number := 0;
begin
for fix_rec in fix_name loop
vv_formated_name := format_name( fix_rec.full_name );
update ${TEMP_TBL}
set full_name = vv_formated_name
where current of fix_name;
l_num_rows := l_num_rows + 1;
end loop;
dbms_output.put_line(to_char(sysdate, 'hh24:mi:ss') || ' - ' || to_char(l_num_rows) || ' full names reformatted');
end;
declare
cursor c_emp is
select p.system_email
from tmp_hr_people h, dss_employee p
where h.pers_id = p.employee_id
and p.system_email is not null
for update of email_address;
l_num_rows number := 0;
begin
for c_upd_rec in c_emp loop
update ${TEMP_TBL}
set email_address = c_upd_rec.system_email
where current of c_emp;
l_num_rows := l_num_rows + 1;
end loop;
dbms_output.put_line(to_char(sysdate, 'hh24:mi:ss') || ' - ' || to_char(l_num_rows) || ' e-mail addresses updated');
end;
declare
cursor c_ts_category is
select c.code
from tmp_hr_people p, hr_control c
where c.code_type = 'TS_CATEGORY'
and c.entity_type = 'ALL'
and c.entity = 'ALL'
and p.craft_flag = nvl(c.craft_flag, p.craft_flag)
-- and p.employee_class_code = nvl(c.employee_class_code, p.employee_class_code)
-- and p.contingent_worker_flag = nvl(c.contingent_worker_flag, p.contingent_worker_flag)
for update of p.ts_category;
l_num_rows number := 0;
begin
for c_catrec in c_ts_category loop
update ${TEMP_TBL}
set ts_category = c_catrec.code
where current of c_ts_category;
l_num_rows := l_num_rows + 1;
end loop;
dbms_output.put_line(to_char(sysdate, 'hh24:mi:ss') || ' - ' || to_char(l_num_rows) || ' TS Category records updated');
end;
declare
cursor c_holiday_schedule is
select c.code
from tmp_hr_people p, hr_control c, department d
where c.code_type = 'HOLIDAY_SCHEDULE'
and c.entity_type = 'FIRM'
and d.firm = c.entity
and p.dept = d.dept
and p.employee_status_code = nvl(c.employee_status_code, p.employee_status_code)
and p.full_part_time_code = nvl(c.full_part_time_code, p.full_part_time_code)
and p.reg_temp_code = nvl(c.reg_temp_code, p.reg_temp_code)
and p.employee_class_code = nvl(c.employee_class_code, p.employee_class_code)
and p.active_flag = nvl(c.active_flag, p.active_flag)
and p.employee_flag = nvl(c.employee_flag, p.employee_flag)
and p.craft_flag = nvl(c.craft_flag, p.craft_flag)
and p.contingent_worker_flag = nvl(c.contingent_worker_flag, p.contingent_worker_flag)
and p.work_schedule = nvl(c.work_schedule, p.work_schedule)
and p.work_location_code = nvl(c.location_code, p.work_location_code)
and p.min_work_week_minutes = nvl(c.min_work_week_minutes, p.min_work_week_minutes)
and p.max_work_week_minutes = nvl(c.max_work_week_minutes, p.max_work_week_minutes)
for update of p.holiday_schedule;
l_num_rows number := 0;
begin
for c_holidayrec in c_holiday_schedule loop
update ${TEMP_TBL}
set holiday_schedule = c_holidayrec.code
where current of c_holiday_schedule;
l_num_rows := l_num_rows + 1;
end loop;
dbms_output.put_line(to_char(sysdate, 'hh24:mi:ss') || ' - ' || to_char(l_num_rows) || ' holiday schedule records updated');
end;
declare
cursor c_pay_type_set is
select c.code
from tmp_hr_people p, hr_control c, department d
where c.code_type = 'PAY_TYPE_SET'
and c.entity_type = 'FIRM'
and d.firm = c.entity
and p.dept = d.dept
and p.employee_status_code = nvl(c.employee_status_code, p.employee_status_code)
and p.full_part_time_code = nvl(c.full_part_time_code, p.full_part_time_code)
and p.reg_temp_code = nvl(c.reg_temp_code, p.reg_temp_code)
and p.employee_class_code = nvl(c.employee_class_code, p.employee_class_code)
and p.active_flag = nvl(c.active_flag, p.active_flag)
and p.employee_flag = nvl(c.employee_flag, p.employee_flag)
and p.craft_flag = nvl(c.craft_flag, p.craft_flag)
and p.contingent_worker_flag = nvl(c.contingent_worker_flag, p.contingent_worker_flag)
and p.ehp_ts_flag = nvl(c.ehp_flag, p.ehp_ts_flag)
and p.work_schedule = nvl(c.work_schedule, p.work_schedule)
and p.work_location_code = nvl(c.location_code, p.work_location_code)
and p.min_work_week_minutes = nvl(c.min_work_week_minutes, p.min_work_week_minutes)
and p.max_work_week_minutes = nvl(c.max_work_week_minutes, p.max_work_week_minutes)
and p.paygroup = nvl(c.paygroup, p.paygroup)
for update of p.pay_type_set;
l_num_rows number := 0;
begin
for c_payrec in c_pay_type_set loop
update ${TEMP_TBL}
set pay_type_set = c_payrec.code
where current of c_pay_type_set;
l_num_rows := l_num_rows + 1;
end loop;
dbms_output.put_line(to_char(sysdate, 'hh24:mi:ss') || ' - ' || to_char(l_num_rows) || ' pay type set records updated');
end;
/* apply pay type set for people meeting CA overtime eligibility */
update ${TEMP_TBL} p
set pay_type_set = 'US-X'
where p.pay_type_set = 'US'
and p.full_part_time_code = 'F'
and p.flsa_code = 'N'
and p.work_schedule in ('A', 'B', 'C')
and exists (select 1
from hr_locations l
where p.work_location_code = l.location_code
and l.state = 'CA'
and l.country = 'USA');
dbms_output.put_line(to_char(sysdate, 'hh24:mi:ss') || ' - ' || (sql%rowcount) || ' pay type set (US-X) records updated');
declare
cursor c_abc_rule_set is
select c.code
from tmp_hr_people p, hr_control c, department d
where c.code_type = 'abc_RULE_SET'
and c.entity_type = 'FIRM'
and d.firm = c.entity
and p.dept = d.dept
and p.employee_status_code = nvl(c.employee_status_code, p.employee_status_code)
and p.full_part_time_code = nvl(c.full_part_time_code, p.full_part_time_code)
and p.reg_temp_code = nvl(c.reg_temp_code, p.reg_temp_code)
and p.employee_class_code = nvl(c.employee_class_code, p.employee_class_code)
and p.active_flag = nvl(c.active_flag, p.active_flag)
and p.employee_flag = nvl(c.employee_flag, p.employee_flag)
and p.craft_flag = nvl(c.craft_flag, p.craft_flag)
and p.contingent_worker_flag = nvl(c.contingent_worker_flag, p.contingent_worker_flag)
and p.ehp_ts_flag = nvl(c.ehp_flag, p.ehp_ts_flag)
and p.work_schedule = nvl(c.work_schedule, p.work_schedule)
and p.work_location_code = nvl(c.location_code, p.work_location_code)
and p.min_work_week_minutes = nvl(c.min_work_week_minutes, p.min_work_week_minutes)
and p.max_work_week_minutes = nvl(c.max_work_week_minutes, p.max_work_week_minutes)
for update of p.abc_rule_set;
l_num_rows number := 0;
begin
for c_abcrulerec in c_abc_rule_set loop
update ${TEMP_TBL}
set abc_rule_set = c_abcrulerec.code
where current of c_abc_rule_set;
l_num_rows := l_num_rows + 1;
end loop;
dbms_output.put_line(to_char(sysdate, 'hh24:mi:ss') || ' - ' || to_char(l_num_rows) || ' abc rule set records updated');
end;
delete from ${DEST_TBL} d
where not exists (select 1
from ${TEMP_TBL} s
where d.pers_id = s.pers_id)
and historical_record = 0;
dbms_output.put_line(to_char(sysdate, 'hh24:mi:ss') || ' - ' || to_char(SQL%ROWCOUNT) || ' obsolete rows removed');
declare
cursor c_upd is
select s.full_name, s.last_name, s.first_name, s.middle_name, s.preferred_first_name,
s.previous_last_name, s.work_phone, s.mail_drop, s.work_location_code,
s.tax_jurisdiction_code, s.country_of_residence_code, s.work_schedule,
s.dept, s.section, s.salary_admin_plan, s.salary_grade, s.job_code, s.employee_status_code,
s.full_part_time_code, s.reg_temp_code, s.flsa_code, s.employee_class_code, s.officer_code,
s.standard_hours, s.min_work_week_minutes, s.max_work_week_minutes, s.union_code, s.set_id,
s.action_code, s.action_reason_code, s.email_address, s.holiday_schedule, s.pay_type_set,
s.abc_rule_set, s.ts_category, s.active_flag, s.employee_flag, s.craft_flag,
s.contingent_worker_flag, s.ehp_ts_flag, s.base_pay_rate,
s.weekly_taxable_per_diem, s.weekly_nontaxable_per_diem,
s.weekly_per_diem_fares, s.weekly_per_diem_travel, s.weekly_per_diem_subsistence,
s.employment_count_date, s.sick_eligible_date, s.abs_plan_year,
s.sick_eligible_code, s.sick_leave_minutes_available,
s.dep_sick_minutes_available, s.vac_plan_year, s.vacation_eligible_flag, s.vacation_minutes_available,
s.hfl_plan_year, s.floater_eligible_flag, s.floater_taken_date, s.floater_hours_taken,
s.last_hire_date, s.craft_sick_mins_taken, s.reg_region, s.paygroup
from ${TEMP_TBL} s, ${DEST_TBL} d
where s.pers_id = d.pers_id
and (s.full_name <> d.full_name
or s.last_name <> d.last_name
or s.first_name <> d.first_name
or s.middle_name <> d.middle_name
or s.preferred_first_name <> d.preferred_first_name
or s.previous_last_name <> d.previous_last_name
or s.work_phone <> d.work_phone
or s.mail_drop <> d.mail_drop
or s.work_location_code <> d.work_location_code
or s.tax_jurisdiction_code <> d.tax_jurisdiction_code
or s.country_of_residence_code <> d.country_of_residence_code
or s.work_schedule <> d.work_schedule
or s.dept <> d.dept
or decode(s.section, d.section, 1, 0) = 0
or s.salary_admin_plan <> d.salary_admin_plan
or s.salary_grade <> d.salary_grade
or s.job_code <> d.job_code
or s.employee_status_code <> d.employee_status_code
or s.full_part_time_code <> d.full_part_time_code
or s.reg_temp_code <> d.reg_temp_code
or s.flsa_code <> d.flsa_code
or s.employee_class_code <> d.employee_class_code
or s.officer_code <> d.officer_code
or s.standard_hours <> d.standard_hours
or s.min_work_week_minutes <> d.min_work_week_minutes
or s.max_work_week_minutes <> d.max_work_week_minutes
or s.union_code <> d.union_code
or s.set_id <> d.set_id
or s.action_code <> d.action_code
or s.action_reason_code <> d.action_reason_code
or decode(s.email_address, d.email_address, 1, 0) = 0
or decode(s.holiday_schedule, d.holiday_schedule, 1, 0) = 0
or decode(s.pay_type_set, d.pay_type_set, 1, 0) = 0
or decode(s.abc_rule_set, d.abc_rule_set, 1, 0) = 0
or decode(s.ts_category, d.ts_category, 1, 0) = 0
or s.active_flag <> d.active_flag
or s.employee_flag <> d.employee_flag
or s.craft_flag <> d.craft_flag
or s.contingent_worker_flag <> d.contingent_worker_flag
or s.ehp_ts_flag <> d.ehp_ts_flag
or s.base_pay_rate <> d.base_pay_rate
or s.weekly_taxable_per_diem <> d.weekly_taxable_per_diem
or s.weekly_nontaxable_per_diem <> d.weekly_nontaxable_per_diem
or s.weekly_per_diem_fares <> d.weekly_per_diem_fares
or s.weekly_per_diem_travel <> d.weekly_per_diem_travel
or s.weekly_per_diem_subsistence <> d.weekly_per_diem_subsistence
or decode(s.employment_count_date, d.employment_count_date, 1, 0) = 0
or decode(s.sick_eligible_date, d.sick_eligible_date, 1, 0) = 0
or s.abs_plan_year <> d.abs_plan_year
or s.sick_eligible_code <> d.sick_eligible_code
or s.sick_leave_minutes_available <> d.sick_leave_minutes_available
or s.dep_sick_minutes_available <> d.dep_sick_minutes_available
or s.vac_plan_year <> d.vac_plan_year
or s.vacation_eligible_flag <> d.vacation_eligible_flag
or s.vacation_minutes_available <> d.vacation_minutes_available
or s.hfl_plan_year <> d.hfl_plan_year
or s.floater_eligible_flag <> d.floater_eligible_flag
or decode(s.floater_taken_date, d.floater_taken_date, 1, 0) = 0
or s.floater_hours_taken <> d.floater_hours_taken
or decode(s.last_hire_date, d.last_hire_date, 1, 0) = 0
or decode(s.craft_sick_mins_taken, d.craft_sick_mins_taken, 1, 0) = 0
or decode(s.reg_region, d.reg_region, 1, 0) = 0
or decode(s.paygroup, d.paygroup, 1, 0) = 0)
for update of d.full_name, d.last_name, d.first_name, d.middle_name, d.preferred_first_name,
d.previous_last_name, d.work_phone, d.mail_drop, d.work_location_code, d.work_schedule,
d.dept, d.section, d.salary_admin_plan, d.salary_grade, d.job_code, d.employee_status_code,
d.full_part_time_code, d.reg_temp_code, d.flsa_code, d.employee_class_code, d.officer_code,
d.standard_hours, d.min_work_week_minutes, d.max_work_week_minutes, d.union_code, d.set_id,
d.action_code, d.action_reason_code, d.email_address, d.holiday_schedule, d.pay_type_set,
d.abc_rule_set, d.ts_category, d.active_flag, d.employee_flag, d.craft_flag,
d.contingent_worker_flag, d.ehp_ts_flag, d.base_pay_rate,
d.weekly_taxable_per_diem, d.weekly_nontaxable_per_diem,
d.weekly_per_diem_fares, d.weekly_per_diem_travel, d.weekly_per_diem_subsistence,
d.employment_count_date, d.sick_eligible_date, d.abs_plan_year,
d.sick_eligible_code, d.sick_leave_minutes_available,
d.dep_sick_minutes_available, d.vac_plan_year, d.vacation_eligible_flag, d.vacation_minutes_available,
d.hfl_plan_year, d.floater_eligible_flag, d.floater_taken_date, d.floater_hours_taken, d.control_date,
d.last_hire_date, d.craft_sick_mins_taken, d.reg_region, d.paygroup;
l_num_rows number := 0;
begin
for c_ref in c_upd loop
update ${DEST_TBL}
set full_name = c_ref.full_name,
last_name = c_ref.last_name,
first_name = c_ref.first_name,
middle_name = c_ref.middle_name,
preferred_first_name = c_ref.preferred_first_name,
previous_last_name = c_ref.previous_last_name,
work_phone = c_ref.work_phone,
mail_drop = c_ref.mail_drop,
work_location_code = c_ref.work_location_code,
tax_jurisdiction_code = c_ref.tax_jurisdiction_code,
country_of_residence_code = c_ref.country_of_residence_code,
work_schedule = c_ref.work_schedule,
dept = c_ref.dept,
section = c_ref.section,
salary_admin_plan = c_ref.salary_admin_plan,
salary_grade = c_ref.salary_grade,
job_code = c_ref.job_code,
employee_status_code = c_ref.employee_status_code,
full_part_time_code = c_ref.full_part_time_code,
reg_temp_code = c_ref.reg_temp_code,
flsa_code = c_ref.flsa_code,
employee_class_code = c_ref.employee_class_code,
officer_code = c_ref.officer_code,
standard_hours = c_ref.standard_hours,
min_work_week_minutes = c_ref.min_work_week_minutes,
max_work_week_minutes = c_ref.max_work_week_minutes,
union_code = c_ref.union_code,
set_id = c_ref.set_id,
action_code = c_ref.action_code,
action_reason_code = c_ref.action_reason_code,
email_address = c_ref.email_address,
holiday_schedule = c_ref.holiday_schedule,
pay_type_set = c_ref.pay_type_set,
abc_rule_set = c_ref.abc_rule_set,
ts_category = c_ref.ts_category,
active_flag = c_ref.active_flag,
employee_flag = c_ref.employee_flag,
craft_flag = c_ref.craft_flag,
contingent_worker_flag = c_ref.contingent_worker_flag,
ehp_ts_flag = c_ref.ehp_ts_flag,
base_pay_rate = c_ref.base_pay_rate,
weekly_taxable_per_diem = c_ref.weekly_taxable_per_diem,
weekly_nontaxable_per_diem = c_ref.weekly_nontaxable_per_diem,
weekly_per_diem_fares = c_ref.weekly_per_diem_fares,
weekly_per_diem_travel = c_ref.weekly_per_diem_travel,
weekly_per_diem_subsistence = c_ref.weekly_per_diem_subsistence,
employment_count_date = c_ref.employment_count_date,
sick_eligible_date = c_ref.sick_eligible_date,
abs_plan_year = c_ref.abs_plan_year,
sick_eligible_code = c_ref.sick_eligible_code,
sick_leave_minutes_available = c_ref.sick_leave_minutes_available,
dep_sick_minutes_available = c_ref.dep_sick_minutes_available,
vac_plan_year = c_ref.vac_plan_year,
vacation_eligible_flag = c_ref.vacation_eligible_flag,
vacation_minutes_available = c_ref.vacation_minutes_available,
hfl_plan_year = c_ref.hfl_plan_year,
floater_eligible_flag = c_ref.floater_eligible_flag,
floater_taken_date = c_ref.floater_taken_date,
floater_hours_taken = c_ref.floater_hours_taken,
control_date = sysdate,
historical_record = 0,
last_hire_date = c_ref.last_hire_date,
craft_sick_mins_taken = c_ref.craft_sick_mins_taken,
reg_region = c_ref.reg_region,
paygroup = c_ref.paygroup
where current of c_upd;
l_num_rows := l_num_rows + 1;
end loop;
dbms_output.put_line(to_char(sysdate, 'hh24:mi:ss') || ' - ' || to_char(l_num_rows) || ' rows updated');
end;
insert into ${DEST_TBL}
(pers_id, full_name, last_name, first_name, middle_name,
preferred_first_name, previous_last_name, work_phone, mail_drop,
work_location_code, tax_jurisdiction_code, country_of_residence_code,
work_schedule, dept, section, salary_admin_plan,
salary_grade, job_code, employee_status_code, full_part_time_code,
reg_temp_code, flsa_code, employee_class_code, officer_code,
standard_hours, min_work_week_minutes, max_work_week_minutes,
union_code, set_id, action_code, action_reason_code, active_flag,
employee_flag, craft_flag, contingent_worker_flag,
ehp_ts_flag, base_pay_rate,
weekly_taxable_per_diem, weekly_nontaxable_per_diem,
weekly_per_diem_fares, weekly_per_diem_travel, weekly_per_diem_subsistence,
abs_plan_year, sick_eligible_code, sick_leave_minutes_available,
dep_sick_minutes_available, vac_plan_year, vacation_eligible_flag,
vacation_minutes_available, hfl_plan_year, floater_eligible_flag, floater_taken_date,
floater_hours_taken, control_date, historical_record, last_hire_date,
craft_sick_mins_taken, reg_region, paygroup)
select pers_id, full_name, last_name, first_name, middle_name,
preferred_first_name, previous_last_name, work_phone, mail_drop,
work_location_code, tax_jurisdiction_code, country_of_residence_code,
work_schedule, dept, section, salary_admin_plan,
salary_grade, job_code, employee_status_code, full_part_time_code,
reg_temp_code, flsa_code, employee_class_code, officer_code,
standard_hours, min_work_week_minutes, max_work_week_minutes,
union_code, set_id, action_code, action_reason_code, active_flag,
employee_flag, craft_flag, contingent_worker_flag,
ehp_ts_flag, base_pay_rate,
weekly_taxable_per_diem, weekly_nontaxable_per_diem,
weekly_per_diem_fares, weekly_per_diem_travel, weekly_per_diem_subsistence,
abs_plan_year, sick_eligible_code, sick_leave_minutes_available,
dep_sick_minutes_available, vac_plan_year, vacation_eligible_flag,
vacation_minutes_available, hfl_plan_year, floater_eligible_flag, floater_taken_date,
floater_hours_taken, sysdate, 0, last_hire_date,
craft_sick_mins_taken, reg_region, paygroup
from ${TEMP_TBL} s
where not exists (select 1
from ${DEST_TBL} d
where s.pers_id = d.pers_id);
dbms_output.put_line(to_char(sysdate, 'hh24:mi:ss') || ' - ' || to_char(SQL%ROWCOUNT) || ' rows inserted');
-- Start - Add Supervisor ID
delete from tmp_emp_supervisor;
insert into tmp_emp_supervisor (pers_id, supervisor_id)
select employee_id, supervisor_id
from SSDW_EMPLOYEE_ENHANCED
where decode(supervisor_id, null, 0, supervisor_id) > 0;
declare
cursor c_supervisor is
select t.pers_id, t.supervisor_id
from tmp_emp_supervisor t, hr_people h
where t.pers_id = h.pers_id
and decode(t.supervisor_id, h.supervisor_id, 1, 0) = 0;
l_num_rows number := 0;
begin
for c_rec in c_supervisor loop
update hr_people
set supervisor_id = c_rec.supervisor_id
where pers_id = c_rec.pers_id;
l_num_rows := l_num_rows + 1;
end loop;
end;
-- End - Add Supervisor ID
commit;
exception
when others then
rollback;
dbms_output.put_line(substr(SQLERRM, 1, 4000));
end;
end if
end ;
/
ISQL_EOF
$UPDATE_STATS ${DEST_TBL}
EMAIL_HR_COUNT=`run_sql <<-endsql
set arraysize 5000
set feedback off
set pagesize 0
set recsep off
set trimout on
set echo off
select count(*)
from ${TEMP_INVALID_TAX_LOC_TBL} ;
endsql
`
if [ ${EMAIL_HR_COUNT} -gt 0 ]; then
# sending email to PeopleSoft
echo "Sending email to PeopleSoft with invalid tax_jurisdiction_codes..."
run_sql <<-endsql >|${DATA_FILE}
set feedback off
set echo off
set trimout on
set tab off
prompt Payroll,
prompt
prompt The following have missing or invalid tax jurisdictions and is not accepted by abc. Please update the correct PeopleSoft tables immediately so the professional can create a time sheet. If the tax location is invalid, please update the professionals PeopleSoft record to a valid tax location.
select pers_id as pers_id, full_name as name, tax_jurisdiction_code as tax_jurisdiction_code
from ${TEMP_INVALID_TAX_LOC_TBL}
order by to_number(pers_id);
endsql
E_RECIP=`run_sql <<-endsql
set arraysize 5000
set feedback off
set pagesize 0
set recsep off
set trimout on
set echo off
select description
from std_valids s
where code_type = 'PPLSFT_INVALID_TAX_LOC';
endsql
`
BOX=`uname -n`
USER=${USER}
MAIL_SENDER="${BOX}.${USER}@gmail.com"
E_SUBJECT="Urgent Action Necessary. Tax Location Invalid in PeopleSoft"
echo subject = ${E_SUBJECT}
echo email body = ${DATA_FILE}
echo recip = ${E_RECIP}
echo sender = ${MAIL_SENDER}
################
# send the email
################
(cat <<-endcat; cat ${DATA_FILE}) | /usr/lib/sendmail -t -i -f ${MAIL_SENDER}
To: ${E_RECIP}
Subject: ${E_SUBJECT}
endcat
##${abc_BIN}/send_email.com PPLSFT_INVALID_TAX_LOC ${DATA_FILE}
else
echo "No invalid tax locations. No email is sending."
fi
echo `date` HR People script finished.
Given below are the errors that I get when I run the above script Can someone please help me solve this errors.
Loading People data
Wed Mar 15 04:45:40 CDT 2023 HR People script started.
mode real
Table (tmp_hr_people) already exists. Exiting...
if 1=1 then
*
ERROR at line 88:
ORA-06550: line 88, column 5:
PLS-00103: Encountered the symbol "IF" when expecting one of the following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior
The symbol "begin" was substituted for "IF" to continue.
ORA-06550: line 108, column 9:
PLS-00103: Encountered the symbol "ORDER" when expecting one of the following:
. ( , * @ % & - + ; / at mod remainder rem return returning
<an exponent (**)> group having intersect minus start union
where connect || multiset
ORA-06550: line 110, column 4:
PLS-00103: Encountered the symbol "DBMS_OUTPUT"
ORA-06550: line 110, column 135:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
. ( , * % & - + / at mod remainder rem <an identifier>
<a double-quoted delimited-identifier> <an exponent (**)> as
from into || multiset bulk
ORA-06550: line 443, column 44:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
set
ORA-06550: line 508, column 11:
PLS-00103: Encountered the symbol "WHERE" when expecting one of the following:
. ( ) , * @ % & - + / at mod remainder rem <an exponent (**)>
and or || multiset
ORA-06550: line 510, column 9:
PLS-00103: Encountered the symbol "L_NUM_ROWS"
ORA-06550: line 510, column 37:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
) , * & = - + < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec between || member submultiset
Usage: /u/abc/bin/update_db_tbl_stats.ksh <table_name>
No invalid tax locations. No email is sending.
Wed Mar 15 04:45:40 CDT 2023 HR People script finished.
We have added this statement to verify if the if statement is working or not. But it is not going into the if statement.
declare
num1 number:=0;
if 1=1 then
dbms_output.put_line('data not inserted in staging table!');
This is a forum Mcrosoft products, and you are using Oracle. I would recommend that you ask in an Oracle forum instead.
How to ask in that forum? Can you please send me the steps?
As I said, this is a Microsoft forum. We don't know Oracle and we don't know their forums. Or, well, at least I don't. But, what about going to Google and enter Oracle forum or something like that?