[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT org_information10
FROM hr_organization_information
WHERE organization_id = p_business_group_id
AND org_information_context = 'Business Group Information';
vXMLTable.DELETE;
select pose.organization_id_child org
from per_org_structure_elements pose
connect by pose.organization_id_parent = prior pose.organization_id_child
and pose.org_structure_version_id = p_org_structure_version_id
start with pose.organization_id_parent = p_organisation_id
and pose.org_structure_version_id = p_org_structure_version_id
union
select p_organisation_id org
from dual;
select org_information1
from hr_organization_information
where organization_id = p_GOSI_office_id
and org_information_context = 'SA_GOSI_OFFICE_DETAILS';
select org_information1,org_information2
from hr_organization_information
where organization_id = p_org_id
and org_information_context = 'SA_EMPLOYER_GOSI_DETAILS';
select name
from hr_all_organization_units
where organization_id = p_org_id;
select distinct asg.person_id,paa.assignment_action_id
from per_all_assignments_f asg
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_periods_of_service pos
where asg.assignment_id = paa.assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and pos.period_of_service_id = asg.period_of_service_id
and ppa.action_type in ('R','Q')
and ppa.action_status = 'C'
and paa.action_status = 'C'
and trunc(ppa.date_earned,'MM') = TRUNC(to_date(l_effective_date,'DD-MM-YYYY'), 'MM')
and NVL(pos.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY'))
not between to_date(l_effective_date,'DD-MM-YYYY') and to_date(l_test_curr_month_date,'DD-MM-YYYY')
and trunc(pos.date_start, 'MM') = trunc(to_date(l_effective_date,'DD-MM-YYYY'), 'MM')
and trunc(to_date(l_effective_date,'DD-MM-YYYY'), 'MM') between asg.effective_start_date and asg.effective_end_date
and hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
and hscl.segment1 = to_char(p_org_id)
ORDER BY asg.person_id;
select distinct asg.person_id, paa.assignment_action_id
from per_all_assignments_f asg
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_periods_of_service pos
where asg.assignment_id = paa.assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and pos.period_of_service_id = asg.period_of_service_id
and ppa.action_type in ('R','Q')
and ppa.action_status = 'C'
and paa.action_status = 'C'
and trunc(ppa.date_earned,'MM') = TRUNC(to_date(l_test_prev_month_date,'DD-MM-YYYY'), 'MM')
and (trunc(NVL(pos.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY')))
between to_date(l_test_prev_month_date,'DD-MM-YYYY') and to_date(l_last_prev_month_date,'DD-MM-YYYY')
or
trunc(NVL(pos.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY')))
between p_date and to_date(l_test_curr_month_date,'DD-MM-YYYY')
)
-- and trunc(pos.date_start, 'MM') <> trunc(to_date(l_test_prev_month_date,'DD-MM-YYYY'), 'MM')
and
(trunc(to_date(l_effective_date,'DD-MM-YYYY'), 'MM') between asg.effective_start_date and asg.effective_end_date
or
trunc(to_date(l_test_prev_month_date,'DD-MM-YYYY'),'MM') between asg.effective_start_date and asg.effective_end_date
)
and hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
and hscl.segment1 = to_char(p_org_id)
ORDER BY asg.person_id;********************/
select distinct asg.person_id,asg.assignment_id from per_all_assignments_f asg
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_periods_of_service pos
where asg.assignment_id = paa.assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and pos.period_of_service_id = asg.period_of_service_id
and ppa.action_type in ('R','Q')
and ppa.action_status = 'C'
and paa.action_status IN ('C','S')
and trunc(ppa.date_earned,'MM') = trunc(to_date(l_effective_date,'DD-MM-YYYY'), 'MM')
and trunc(pos.date_start, 'MM') = trunc(to_date(l_effective_date,'DD-MM-YYYY'), 'MM')
and trunc(to_date(l_effective_date,'DD-MM-YYYY'),'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
and hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
and hscl.segment1 = to_char(p_org_id)
order by asg.person_id;
select paa.assignment_action_id from per_all_assignments_f asg
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_periods_of_service pos
where rownum < 2
and asg.assignment_id = paa.assignment_id
and asg.person_id = p_person_id
and paa.payroll_action_id = ppa.payroll_action_id
and pos.period_of_service_id = asg.period_of_service_id
and ppa.action_type in ('R','Q')
and ppa.action_status = 'C'
and paa.action_status IN ('C','S')
and trunc(ppa.date_earned,'MM') = trunc(to_date(l_effective_date,'DD-MM-YYYY'), 'MM')
and trunc(pos.date_start, 'MM') = trunc(to_date(l_effective_date,'DD-MM-YYYY'), 'MM')
and trunc(to_date(l_effective_date,'DD-MM-YYYY'),'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
and hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
and hscl.segment1 = to_char(p_org_id)
order by asg.person_id;
select /*+ INDEX(hscl, HR_SOFT_CODING_KEYFLEX_PK) */ distinct asg.person_id, asg.assignment_id
from per_all_assignments_f asg
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_periods_of_service pos
where asg.assignment_id = paa.assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and pos.period_of_service_id = asg.period_of_service_id
and ppa.action_type in ('R','Q')
and ppa.action_status = 'C'
and paa.action_status IN ('C','S')
and trunc(ppa.date_earned,'MM') = TRUNC(to_date(l_test_prev_month_date,'DD-MM-YYYY'), 'MM')
and (
( trunc(NVL(pos.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY')))
between to_date(l_test_prev_month_date,'DD-MM-YYYY') and to_date(l_last_prev_month_date,'DD-MM-YYYY')
)
or
trunc(NVL(pos.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY')))
between p_date and to_date(l_test_curr_month_date,'DD-MM-YYYY')
or
(
trunc(NVL(pos.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY')))
between TRUNC(to_date(l_test_prev_month_date,'DD-MM-YYYY'),'MM') and to_date(l_last_prev_month_date,'DD-MM-YYYY')
AND
trunc(pos.date_start, 'MM') = TRUNC(to_date(l_test_prev_month_date,'DD-MM-YYYY'), 'MM')
)
)
and trunc(pos.date_start, 'MM') <> trunc(to_date(l_test_curr_month_date,'DD-MM-YYYY'), 'MM')
and
(
trunc(to_date(l_effective_date,'DD-MM-YYYY'), 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
or
trunc(to_date(l_test_prev_month_date,'DD-MM-YYYY'),'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
)
and hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
and hscl.segment1 = to_char(p_org_id)
and hscl.id_flex_num = 20
ORDER BY asg.person_id;
select paa.assignment_action_id
from per_all_assignments_f asg
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_periods_of_service pos
where rownum < 2
and asg.assignment_id = paa.assignment_id
and asg.person_id = p_person_id
and paa.payroll_action_id = ppa.payroll_action_id
and pos.period_of_service_id = asg.period_of_service_id
and ppa.action_type in ('R','Q')
and ppa.action_status = 'C'
and paa.action_status IN ('C','S')
and trunc(ppa.date_earned,'MM') = TRUNC(to_date(l_test_prev_month_date,'DD-MM-YYYY'), 'MM')
and (
( trunc(NVL(pos.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY')))
between to_date(l_test_prev_month_date,'DD-MM-YYYY') and to_date(l_last_prev_month_date,'DD-MM-YYYY')
)
or
trunc(NVL(pos.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY')))
between p_date and to_date(l_test_curr_month_date,'DD-MM-YYYY')
or
(
trunc(NVL(pos.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY')))
between TRUNC(to_date(l_test_prev_month_date,'DD-MM-YYYY'),'MM') and to_date(l_last_prev_month_date,'DD-MM-YYYY')
AND
trunc(pos.date_start, 'MM') = TRUNC(to_date(l_test_prev_month_date,'DD-MM-YYYY'), 'MM')
)
)
and trunc(pos.date_start, 'MM') <> trunc(to_date(l_test_curr_month_date,'DD-MM-YYYY'), 'MM')
and
(
trunc(to_date(l_effective_date,'DD-MM-YYYY'), 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
or
trunc(to_date(l_test_prev_month_date,'DD-MM-YYYY'),'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
)
and hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
and hscl.segment1 = to_char(p_org_id)
ORDER BY asg.person_id;
select name
from per_jobs pj, per_all_assignments_f paf
where pj.job_id = paf.job_id
and paf.assignment_id = p_assignment_id
and trunc(p_date,'MM') between trunc(paf.effective_start_date,'MM') and paf.effective_end_date;
SELECT pei.pei_information1 FROM per_people_extra_info pei
WHERE pei.person_id = pn_person_id
AND pei.information_type = 'SA_PASSPORT' AND pei.pei_information_category = 'SA_PASSPORT'
AND p_date between trunc(fnd_date.canonical_to_date(pei.pei_information3),'MM') and fnd_date.canonical_to_date(pei.pei_information4);
SELECT NATIONAL_IDENTIFIER from per_all_people_f pap WHERE pap.person_id = p_person_id
and trunc(p_date,'MM') between trunc(pap.effective_start_date,'MM') and pap.effective_end_date;
select date_start from per_periods_of_service where person_id = p_person_id and trunc(date_start,'MM') = to_date(l_effective_date,'DD-MM-YYYY');
select actual_termination_date from per_periods_of_service where person_id = p_person_id;
select full_name,nationality,date_of_birth from per_all_people_f where person_id = p_person_id
and trunc(p_date,'MM') between trunc(effective_start_date,'MM') and effective_end_date;
select segment2
from hr_soft_coding_keyflex hsc, per_all_assignments_f paf
where hsc.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
and paf.person_id = p_person_id;
select full_name from per_all_people_f where person_id = p_person_id
and trunc(p_date,'MM') between trunc(effective_start_date,'MM') and effective_end_date;
select segment2
from hr_soft_coding_keyflex hsc, per_all_assignments_f paf
where hsc.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
and paf.person_id = p_person_id;
select actual_termination_date,leaving_reason from per_periods_of_service where person_id = p_person_id;
SELECT global_value
FROM ff_globals_f
WHERE global_name = 'SA_GOSI_BASE_LOWER_LIMIT'
AND legislation_code = 'SA'
AND business_group_id IS NULL
AND l_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT global_value
FROM ff_globals_f
WHERE global_name = 'SA_GOSI_BASE_UPPER_LIMIT'
AND legislation_code = 'SA'
AND business_group_id IS NULL
AND l_effective_date BETWEEN effective_start_date
AND effective_end_date;
insert into fnd_sessions(session_id,effective_date) values(userenv('sessionid'),to_date(l_effective_date,'DD-MM-YYYY'));
select organization_id
into l_gre_id
from hr_all_organization_units
where name = l_gre_name
and business_group_id = p_business_group_id;
select organization_id
into l_gre_id
from hr_all_organization_units
where name = l_gre_name
and business_group_id =p_business_group_id;
select name
into l_gre_name
from hr_all_organization_units
where organization_id= l_gre_id
and business_group_id =p_business_group_id;
vXMLTable.DELETE;
select u.creator_id
into l_defined_balance_id
from ff_user_entities u,
ff_database_items d
where d.user_name = 'GOSI_REFERENCE_EARNINGS_ASG_YTD'
and u.user_entity_id = d.user_entity_id
and u.legislation_code = 'SA'
and u.business_group_id is null
and u.creator_type = 'B';
select name
into l_employer_gosi_office_name
from hr_organization_units
where organization_id = l_employer_gosi_office;
SELECT meaning INTO l_nationality_mn
FROM HR_LOOKUPS H, FND_SESSIONS S
WHERE LOOKUP_TYPE = 'NATIONALITY'
AND ENABLED_FLAG = 'Y'
AND LOOKUP_CODE = l_nationality
AND SESSION_ID = USERENV('SESSIONID')
AND S.EFFECTIVE_DATE BETWEEN NVL(H.START_DATE_ACTIVE, S.EFFECTIVE_DATE)
AND NVL(END_DATE_ACTIVE, S.EFFECTIVE_DATE)
ORDER BY MEANING;
/*SELECT meaning INTO l_term_reason_mn
FROM hr_lookups hl
WHERE hl.lookup_type = 'LEAV_REAS'
AND to_date(l_effective_date'DD-MM-YYYY')
between nvl(hl.start_date_active,to_date( and hl.end_date_active
AND hl.lookup_code = l_term_reason;*/
SELECT meaning INTO l_term_reason_mn
FROM HR_LOOKUPS H, FND_SESSIONS S
WHERE LOOKUP_TYPE = 'LEAV_REAS'
AND ENABLED_FLAG = 'Y'
AND LOOKUP_CODE = l_term_reason
AND SESSION_ID = USERENV('SESSIONID')
AND S.EFFECTIVE_DATE BETWEEN NVL(H.START_DATE_ACTIVE, S.EFFECTIVE_DATE)
AND NVL(END_DATE_ACTIVE, S.EFFECTIVE_DATE)
ORDER BY MEANING;
select pose.organization_id_child org
from per_org_structure_elements pose
connect by pose.organization_id_parent = prior pose.organization_id_child
and pose.org_structure_version_id = p_org_structure_version_id
start with pose.organization_id_parent = (nvl(p_organisation_id,l_parent_id))
and pose.org_structure_version_id = p_org_structure_version_id
union
select (nvl(p_organisation_id,l_parent_id)) org
from dual;
select org_information1
,org_information2
from hr_organization_information
where organization_id = l_tax_unit_id
and org_information_context = 'SA_EMPLOYER_GOSI_DETAILS';
select org_information1
from hr_organization_information
where organization_id = l_gosi_office_id
and org_information_context = 'SA_GOSI_OFFICE_DETAILS';
insert into fnd_sessions(session_id,effective_date) values(userenv('sessionid'),l_effective_date);
vXMLTable.DELETE;
select distinct pose.organization_id_parent
into l_parent_id
from per_org_structure_elements pose
where pose.org_structure_version_id = p_org_structure_version_id
and pose.organization_id_parent not in (select pose1.organization_id_child
from per_org_structure_elements pose1
where pose1.org_structure_version_id = p_org_structure_version_id);
select organization_id
into l_gre_id
from hr_all_organization_units
where name = l_gre_name
and business_group_id = p_business_group_id;
select add_months(l_effective_date,-1)
into l_prev_mon_date
from dual;
select add_months(l_prev_mon_date,-1)
into l_prev2_mon_date
from dual;
SELECT meaning INTO l_def_nationality
FROM hr_lookups
WHERE lookup_type = 'NATIONALITY'
AND lookup_code = l_def_nationality_cd;
select u.creator_id
into l_ann_month_db
from ff_user_entities u,
ff_database_items d
where d.user_name = 'GOSI_ANNUITIES_NAT_JOINER_LEAVER_TU_MONTH'
and u.user_entity_id = d.user_entity_id
and u.legislation_code = 'SA'
and u.business_group_id is null
and u.creator_type = 'B';
select u.creator_id
into l_haz_month_db
from ff_user_entities u,
ff_database_items d
where d.user_name = 'GOSI_HAZARDS_NAT_JOINER_LEAVER_TU_MONTH'
and u.user_entity_id = d.user_entity_id
and u.legislation_code = 'SA'
and u.business_group_id is null
and u.creator_type = 'B';
select organization_id
into l_gre_id
from hr_all_organization_units
where name = l_gre_name
and business_group_id = p_business_group_id;
select name
into l_gre_name
from hr_all_organization_units
where organization_id= l_gre_id
and business_group_id =p_business_group_id;
select name
into l_gosi_office
from hr_all_organization_units
where organization_id = l_gosi_office_id;
SELECT element_type_id
INTO l_gosi_id
FROM pay_element_types_f
WHERE element_name = 'GOSI'
AND legislation_code = 'SA'
AND l_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT element_type_id
INTO l_employer_gosi_haz_id
FROM pay_element_types_f
WHERE element_name = 'Employer GOSI Hazards'
AND legislation_code = 'SA'
AND l_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT element_type_id
INTO l_employee_gosi_ann_id
FROM pay_element_types_f
WHERE element_name = 'Employee GOSI Annuities'
AND legislation_code = 'SA'
AND l_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT SUM(DECODE(ac1.context_value
,'1' ,DECODE(rr3.run_result_id
,NULL, 0
,1)
,0)) SAUDI_ANNUITIES
,SUM(DECODE(ac1.context_value
,'1', DECODE(rr3.run_result_id
,NULL, DECODE(rr2.run_result_id
,NULL, 0
,1)
,1)
,0)) SAUDI_ANNUITIES_HAZARDS
,SUM(DECODE(rr2.run_result_id
,NULL, 0
,1)) HAZARDS
,SUM(DECODE(ac2.context_value
,'Y', DECODE(ac1.context_value
,'1' ,DECODE(rr3.run_result_id
,NULL, 0
,1)
,0)
,0)) JOINER_SAUDI_ANNUITIES
,SUM(DECODE(ac2.context_value
,'Y', DECODE(ac1.context_value
,'1', DECODE(rr3.run_result_id
,NULL, DECODE(rr2.run_result_id
,NULL, 0
,1)
,1)
,0)
,0)) JOINER_SAUDI_ANNUITIES_HAZARDS
,SUM(DECODE(ac2.context_value
,'Y', DECODE(ac1.context_value
,'2' ,DECODE(rr2.run_result_id
,NULL, 0
,1)
,0)
,0)) JOINER_NONSAUDI_HAZARDS
,SUM(DECODE(ac3.context_value
,'Y', DECODE(ac1.context_value
,'2' ,DECODE(rr2.run_result_id
,NULL, 0
,1)
,0)
,0)) LEAVER_NONSAUDI_HAZARDS
,SUM(DECODE(ac1.context_value
,'2' ,DECODE(rr2.run_result_id
,NULL, 0
,1)
,0)) NONSAUDI_HAZARDS
,SUM(DECODE(ac2.context_value
,'Y', DECODE(rr2.run_result_id
,NULL, 0
,1)
,0)) JOINER_HAZARDS
,SUM(DECODE(ac3.context_value
,'Y', DECODE(ac1.context_value
,'1' ,DECODE(rr3.run_result_id
,NULL, 0
,1)
,0)
,0)) LEAVER_SAUDI_ANNUITIES
,SUM(DECODE(ac3.context_value
,'Y', DECODE(ac1.context_value
,'1', DECODE(rr3.run_result_id
,NULL, DECODE(rr2.run_result_id
,NULL, 0
,1)
,1)
,0)
,0)) LEAVER_SAUDI_ANNUITIES_HAZARDS
,SUM(DECODE(ac3.context_value
,'Y', DECODE(rr2.run_result_id
,NULL, 0
,1)
,0)) LEAVER_HAZARDS
INTO l_p_saudi_ann
,l_p_saudi_ann_haz
,l_p_haz
,l_p_joiner_saudi_ann
,l_p_joiner_saudi_ann_haz
,l_p_joiner_nonsaudi_haz /*Added for enhancement*/
,l_p_leaver_nonsaudi_haz /*Added for enhancement*/
,l_p_nonsaudi_haz /*Added for enhancement*/
,l_p_joiner_haz
,l_p_leaver_saudi_ann
,l_p_leaver_saudi_ann_haz
,l_p_leaver_haz
FROM pay_assignment_actions paa
,pay_action_contexts ac1
,ff_contexts ct1
,pay_action_contexts ac2
,ff_contexts ct2
,pay_action_contexts ac3
,ff_contexts ct3
,pay_payroll_actions ppa
,pay_run_results rr1
,pay_run_results rr2
,pay_run_results rr3
WHERE ppa.business_group_id = p_business_group_id
AND ppa.action_type IN ('R','Q')
AND ppa.action_status = 'C'
AND ppa.date_earned BETWEEN TRUNC(l_prev_mon_date,'MM')
AND l_prev_mon_date
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.tax_unit_id = l_tax_unit_id -- Employer
AND ct1.context_name = 'SOURCE_NUMBER'
AND ac1.context_id = ct1.context_id
AND ac1.assignment_action_id = paa.assignment_action_id
AND ct2.context_name = 'SOURCE_TEXT'
AND ac2.context_id = ct2.context_id
AND ac2.assignment_action_id = paa.assignment_action_id
AND ct3.context_name = 'SOURCE_TEXT2'
AND ac3.context_id = ct3.context_id
AND ac3.assignment_action_id = paa.assignment_action_id
AND rr1.assignment_action_id = paa.assignment_action_id
AND rr1.element_type_id = l_gosi_id
AND rr2.assignment_action_id (+) = rr1.assignment_action_id
AND rr2.source_id (+) = rr1.element_entry_id
AND rr2.source_type (+) = 'I'
AND rr2.element_type_id (+) = l_employer_gosi_haz_id
AND rr3.assignment_action_id (+) = rr1.assignment_action_id
AND rr3.source_id (+) = rr1.element_entry_id
AND rr3.source_type (+) = 'I'
AND rr3.element_type_id (+) = l_employee_gosi_ann_id;
SELECT SUM(DECODE(ac1.context_value
,'1' ,DECODE(rr3.run_result_id
,NULL, 0
,1)
,0)) SAUDI_ANNUITIES
,SUM(DECODE(ac1.context_value
,'1', DECODE(rr3.run_result_id
,NULL, DECODE(rr2.run_result_id
,NULL, 0
,1)
,1)
,0)) SAUDI_ANNUITIES_HAZARDS
,SUM(DECODE(rr2.run_result_id
,NULL, 0
,1)) HAZARDS
,SUM(DECODE(ac2.context_value
,'Y', DECODE(ac1.context_value
,'1' ,DECODE(rr3.run_result_id
,NULL, 0
,1)
,0)
,0)) JOINER_SAUDI_ANNUITIES
,SUM(DECODE(ac2.context_value
,'Y', DECODE(ac1.context_value
,'1', DECODE(rr3.run_result_id
,NULL, DECODE(rr2.run_result_id
,NULL, 0
,1)
,1)
,0)
,0)) JOINER_SAUDI_ANNUITIES_HAZARDS
,SUM(DECODE(ac2.context_value
,'Y', DECODE(ac1.context_value
,'2' ,DECODE(rr2.run_result_id
,NULL, 0
,1)
,0)
,0)) JOINER_NONSAUDI_HAZARDS
,SUM(DECODE(ac3.context_value
,'Y', DECODE(ac1.context_value
,'2' ,DECODE(rr2.run_result_id
,NULL, 0
,1)
,0)
,0)) LEAVER_NONSAUDI_HAZARDS
,SUM(DECODE(ac1.context_value
,'2' ,DECODE(rr2.run_result_id
,NULL, 0
,1)
,0)) NONSAUDI_HAZARDS
,SUM(DECODE(ac2.context_value
,'Y', DECODE(rr2.run_result_id
,NULL, 0
,1)
,0)) JOINER_HAZARDS
,SUM(DECODE(ac3.context_value
,'Y', DECODE(ac1.context_value
,'1' ,DECODE(rr3.run_result_id
,NULL, 0
,1)
,0)
,0)) LEAVER_SAUDI_ANNUITIES
,SUM(DECODE(ac3.context_value
,'Y', DECODE(ac1.context_value
,'1', DECODE(rr3.run_result_id
,NULL, DECODE(rr2.run_result_id
,NULL, 0
,1)
,1)
,0)
,0)) LEAVER_SAUDI_ANNUITIES_HAZARDS
,SUM(DECODE(ac3.context_value
,'Y', DECODE(rr2.run_result_id
,NULL, 0
,1)
,0)) LEAVER_HAZARDS
INTO l_c_saudi_ann
,l_c_saudi_ann_haz
,l_c_haz
,l_c_joiner_saudi_ann
,l_c_joiner_saudi_ann_haz
,l_c_joiner_nonsaudi_haz /*Added for enhancement*/
,l_c_leaver_nonsaudi_haz /*Added for enhancement*/
,l_c_nonsaudi_haz /*Added for enhancement*/
,l_c_joiner_haz
,l_c_leaver_saudi_ann
,l_c_leaver_saudi_ann_haz
,l_c_leaver_haz
FROM pay_assignment_actions paa
,pay_action_contexts ac1
,ff_contexts ct1
,pay_action_contexts ac2
,ff_contexts ct2
,pay_action_contexts ac3
,ff_contexts ct3
,pay_payroll_actions ppa
,pay_run_results rr1
,pay_run_results rr2
,pay_run_results rr3
WHERE ppa.business_group_id = p_business_group_id
AND ppa.action_type IN ('R','Q')
AND ppa.action_status = 'C'
AND ppa.date_earned BETWEEN TRUNC(l_effective_date,'MM')
AND l_effective_date
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.tax_unit_id = l_tax_unit_id -- Employer
AND ct1.context_name = 'SOURCE_NUMBER'
AND ac1.context_id = ct1.context_id
AND ac1.assignment_action_id = paa.assignment_action_id
AND ct2.context_name = 'SOURCE_TEXT'
AND ac2.context_id = ct2.context_id
AND ac2.assignment_action_id = paa.assignment_action_id
AND ct3.context_name = 'SOURCE_TEXT2'
AND ac3.context_id = ct3.context_id
AND ac3.assignment_action_id = paa.assignment_action_id
AND rr1.assignment_action_id = paa.assignment_action_id
AND rr1.element_type_id = l_gosi_id
AND rr2.assignment_action_id (+) = rr1.assignment_action_id
AND rr2.source_id (+) = rr1.element_entry_id
AND rr2.source_type (+) = 'I'
AND rr2.element_type_id (+) = l_employer_gosi_haz_id
AND rr3.assignment_action_id (+) = rr1.assignment_action_id
AND rr3.source_id (+) = rr1.element_entry_id
AND rr3.source_type (+) = 'I'
AND rr3.element_type_id (+) = l_employee_gosi_ann_id;
select distinct paa.assignment_action_id, paf.person_id
from pay_assignment_actions paa
,pay_payroll_actions ppa
,per_all_assignments_f paf
,per_periods_of_service pps
,hr_soft_coding_keyflex hscl
,per_time_periods ptp
,pay_run_results prr
where paf.period_of_service_id = pps.period_of_service_id
and nvl(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY'))
between trunc(l_effective_date,'MM') and trunc(l_eff_term_date-1)
/*****between trunc(l_effective_date,'MM') and trunc(l_eff_term_date-1)********* CHECK THIS*/
--between l_prev_term_date and trunc(l_eff_term_date-1)
and paf.assignment_id = paa.assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R','Q')
and ppa.action_status = 'C'
and paa.action_status = 'C'
and prr.assignment_action_id = paa.assignment_action_id
and prr.element_type_id = l_gosi_id
and paf.soft_coding_keyflex_id = hscl.soft_coding_keyflex_id
and ppa.time_period_id = ptp.time_period_id
and ptp.end_date = l_prev_mon_date
and trunc(l_prev_mon_date,'MM') between trunc(paf.effective_start_date,'MM') and paf.effective_end_date
and hscl.segment1 = to_char(l_tax_unit_id);
select distinct paa.assignment_action_id, paf.person_id
from pay_assignment_actions paa
,pay_payroll_actions ppa
,per_all_assignments_f paf
,per_periods_of_service pps
,hr_soft_coding_keyflex hscl
,per_time_periods ptp
,pay_run_results prr
where paf.period_of_service_id = pps.period_of_service_id
and nvl(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY'))
between l_prev_term_date and trunc(l_eff_term_date-1)
and paf.assignment_id = paa.assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R','Q')
and ppa.action_status = 'C'
and paa.action_status = 'C'
and prr.assignment_action_id = paa.assignment_action_id
and prr.element_type_id = l_gosi_id
and paf.soft_coding_keyflex_id = hscl.soft_coding_keyflex_id
and ppa.time_period_id = ptp.time_period_id
and ptp.end_date = l_prev_mon_date
and trunc(l_prev_mon_date,'MM') between trunc(paf.effective_start_date,'MM') and paf.effective_end_date
and hscl.segment1 = to_char(l_tax_unit_id)
and hscl.segment3 = 'Y'
and hscl.segment5 = 'N';
select distinct paa.assignment_action_id, paf.person_id
from pay_assignment_actions paa
,pay_payroll_actions ppa
,per_all_assignments_f paf
,per_periods_of_service pps
,hr_soft_coding_keyflex hscl
,per_time_periods ptp
,pay_run_results prr
where paf.period_of_service_id = pps.period_of_service_id
and nvl(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY'))
not between trunc(l_effective_date,'MM') and trunc(l_eff_term_date-1)
and trunc(pps.date_start, 'MM') = trunc(l_effective_date,'MM')
and paf.assignment_id = paa.assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R','Q')
and ppa.action_status = 'C'
and paa.action_status = 'C'
and prr.assignment_action_id = paa.assignment_action_id
and prr.element_type_id = l_gosi_id
and paf.soft_coding_keyflex_id = hscl.soft_coding_keyflex_id
and ppa.time_period_id = ptp.time_period_id
and ptp.end_date = l_effective_date
and trunc(l_effective_date,'MM') between trunc(paf.effective_start_date,'MM') and paf.effective_end_date
and hscl.segment1 = to_char(l_tax_unit_id)
and hscl.segment3 = 'Y'
and hscl.segment5 = 'N';
select u.creator_id
into l_gosi_haz_asg_tu_mth_db
from ff_user_entities u,
ff_database_items d
where d.user_name = 'GOSI_HAZARDS_ASG_TU_MONTH'
and u.user_entity_id = d.user_entity_id
and u.legislation_code = 'SA'
and u.business_group_id is null
and u.creator_type = 'B';
select u.creator_id
into l_emp_gosi_ann_asg_ptd_db
from ff_user_entities u,
ff_database_items d
where d.user_name = 'GOSI_ANNUITIES_ASG_TU_MONTH'
and u.user_entity_id = d.user_entity_id
and u.legislation_code = 'SA'
and u.business_group_id is null
and u.creator_type = 'B';
select count(*)
into l_loc_nat
from per_all_people_f
where person_id = rec_leav_assact.person_id
and upper(nationality) = FND_PROFILE.VALUE('PER_LOCAL_NATIONALITY')
and trunc(l_prev_mon_date,'MM') between trunc(effective_start_date,'MM') and effective_end_date;
SELECT global_value
INTO l_hazards_pct
FROM ff_globals_f
WHERE legislation_code='SA'
AND business_group_id IS NULL
AND global_name = 'SA_ER_HAZARDS_PCT';
SELECT global_value
INTO l_ee_annuities_pct
FROM ff_globals_f
WHERE legislation_code='SA'
AND business_group_id IS NULL
AND global_name = 'SA_EE_ANNUITIES_PCT';
SELECT global_value
INTO l_er_annuities_pct
FROM ff_globals_f
WHERE legislation_code='SA'
AND business_group_id IS NULL
AND global_name = 'SA_ER_ANNUITIES_PCT';
SELECT value
INTO l_audit_log_dir1
FROM v$parameter
WHERE LOWER(name) = 'utl_file_dir';
select org_information1
from hr_organization_information
where organization_id = p_Gosi_Office_Id
and org_information_context = 'SA_GOSI_OFFICE_DETAILS';
select org_information1,org_information2
from hr_organization_information
where organization_id = p_gre_id
and org_information_context = 'SA_EMPLOYER_GOSI_DETAILS';
/*Cursor to select personal information for employee*/
cursor get_info_per (l_assignment_id number, l_effective_date date) is
select first_name,
per_information1,
per_information2,
per_information10,
last_name
from per_all_people_f peo
,per_all_assignments_f paa
where peo.person_id = paa.person_id
and paa.assignment_id = l_assignment_id;
/*Cursor to select assignments from a given GRE*/
cursor csr_get_gre_assignments (l_employer_id number, l_business_group_id number, l_effective_date date,l_form_type varchar2) is
select /*+ INDEX(hsck, HR_SOFT_CODING_KEYFLEX_PK) */ distinct assignment_id
from per_all_assignments_f paa,
hr_soft_coding_keyflex hsck,
per_periods_of_service pos
where hsck.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
and (nvl(hsck.segment3,'N') = 'Y' OR nvl(hsck.segment5,'N') = 'Y')
and paa.business_group_id = l_business_group_id
--and l_effective_date between paa.effective_start_date and paa.effective_end_date
and hsck.ID_FLEX_NUM = 20
and hsck.segment1= to_char(l_employer_id)
and paa.period_of_service_id = pos.period_of_service_id
and ((l_form_type = 'NU'
and trunc(pos.date_start,'MM') = trunc(l_effective_date,'MM')
and trunc(pos.date_start) <= trunc(l_effective_date)
and hsck.segment2 is null)
or (l_form_type = 'NR'
and trunc(pos.date_start,'MM') = trunc(l_effective_date,'MM')
and trunc(pos.date_start) <= trunc(l_effective_date)
and hsck.segment2 is not null)
or (l_form_type = 'TM'
and trunc(pos.actual_termination_date,'MM') = trunc(l_effective_date,'MM')
and trunc(pos.actual_termination_date) <= trunc(l_effective_date)));
/*Cursor to select assignments for a given assignment set*/
cursor csr_get_assignment(l_assignment_set_id number, l_form_type varchar2) is
select distinct has.assignment_id
from hr_assignment_set_amendments has
,per_all_assignments_f paa
,per_periods_of_service pos
,hr_soft_coding_keyflex hsck
where assignment_set_id = l_assignment_set_id
and include_or_exclude = 'I'
and has.assignment_id = paa.assignment_id
--and p_effective_date between paa.effective_start_date and paa.effective_end_date
and hsck.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
and hsck.ID_FLEX_NUM = 20
and hsck.segment1= to_char(p_organisation_id)
and (nvl(hsck.segment3,'N') = 'Y' OR nvl(hsck.segment5,'N') = 'Y')
and paa.period_of_service_id = pos.period_of_service_id
and ((l_form_type = 'NU'
and trunc(pos.date_start,'MM') = trunc(l_effective_date,'MM')
and trunc(pos.date_start) <= trunc(l_effective_date)
and hsck.segment2 is null)
or (l_form_type = 'NR'
and trunc(pos.date_start,'MM') = trunc(l_effective_date,'MM')
and trunc(pos.date_start) <= trunc(l_effective_date)
and hsck.segment2 is not null)
or (l_form_type = 'TM'
and trunc(pos.actual_termination_date,'MM') = trunc(l_effective_date,'MM')
and trunc(pos.actual_termination_date) <= trunc(l_effective_date)));
/* Cursor to select Employee GOSI Number */
cursor get_emp_gosi (l_assignment_id NUMBER, l_effective_date Date) is
select hscl.segment2
from hr_soft_coding_keyflex hscl
,per_all_assignments_f paa
where hscl.SOFT_CODING_KEYFLEX_ID = paa.soft_CODING_KEYFLEX_ID
and paa.assignment_id = l_assignment_id
and hscl.id_flex_num = 20;
select hla.town_or_city
,hla.region_2
,hla.region_3
from hr_locations_all hla
,hr_organization_units hou
,per_all_assignments_f paa
where paa.organization_id = hou.organization_id
and hou.location_id = hla.location_id
and paa.assignment_id = l_assignment_id
--and l_effective_date between paa.effective_start_date and paa.effective_end_date
and paa.business_group_id = hou.business_group_id;
SELECT person_id
FROM per_all_assignments_f
WHERE assignment_id = l_assignment_id;
SELECT pa.town_or_city city,
pa.region_1 R1,
pa.region_2 R2,
pa.region_3 R3,
pa.postal_code ZIP
FROM per_addresses pa
WHERE pa.primary_flag = 'Y'
AND pa.person_id = l_person_id
AND l_effective_date BETWEEN trunc(pa.date_from,'MM')
AND nvl(pa.date_to,to_date('31-12-4712','DD-MM-YYYY'));
select peo.national_identifier
,peo.sex
,peo.marital_status
,peo.date_of_birth
from per_all_people_f peo
,per_all_assignments_f paa
where peo.person_id = paa.person_id
and paa.assignment_id = l_assignment_id;
select name
from per_jobs pj,
per_all_assignments_f paa
where pj.job_id = paa.job_id
and paa.business_group_id = pj.business_group_id
and paa.assignment_id = l_assignment_id;
select hlp.meaning --distinct TOWN_OR_CITY
from hr_locations hl,
hr_lookups hlp,
per_all_assignments_f paa
where hl.location_id = paa.location_id
and paa.assignment_id = l_assignment_id
and hlp.lookup_type = 'SA_CITY'
and hlp.lookup_code = hl.TOWN_OR_CITY;
select nationality,employee_number,email_address
from per_all_people_f ppf
where ppf.person_id = l_person_id
and l_date between ppf.effective_start_date and ppf.effective_end_date;
SELECT pei.pei_information1 , fnd_date.canonical_to_date(pei.pei_information3) , pei.pei_information5
FROM per_people_extra_info pei
WHERE pei.person_id = l_person_id
AND pei.information_type = 'SA_PASSPORT' AND pei.pei_information_category = 'SA_PASSPORT'
AND l_date between trunc(fnd_date.canonical_to_date(pei.pei_information3),'MM') and fnd_date.canonical_to_date(pei.pei_information4);
SELECT pei.pei_information1,fnd_date.canonical_to_date(pei.pei_information2),pei.pei_information3
FROM per_people_extra_info pei
WHERE pei.person_id = l_person_id
AND pei.information_type = 'SA_HAFIZA' AND pei.pei_information_category = 'SA_HAFIZA'
AND rowid = (select max(rowid) from per_people_extra_info where person_id = l_person_id ) ;
select town_or_city
from hr_locations hl, hr_all_organization_units hau
where hau.organization_id = l_organization_id
and hau.location_id = hl.location_id;
select pps.date_start
,pps.actual_termination_date
,pps.leaving_reason
from per_periods_of_service pps
,per_all_assignments_f paa
where /*pps.person_id = paa.person_id*/
pps.period_of_service_id = paa.period_of_service_id
and paa.business_group_id = pps.business_group_id
and paa.assignment_id = l_assignment_id;
select hscl.segment4,
hscl.segment6
from hr_soft_coding_keyflex hscl
,per_all_assignments_f paa
where hscl.SOFT_CODING_KEYFLEX_ID = paa.soft_CODING_KEYFLEX_ID
and paa.assignment_id = l_assignment_id
and hscl.id_flex_num = 20;
select hscl.segment1
from hr_soft_coding_keyflex hscl
,per_all_assignments_f paa
where hscl.SOFT_CODING_KEYFLEX_ID = paa.soft_CODING_KEYFLEX_ID
and paa.assignment_id = l_assignment_id
and hscl.id_flex_num = 20;
select paa.assignment_action_id
from pay_assignment_actions paa
,per_all_assignments_f paf
,pay_payroll_actions ppa
where paa.assignment_id = paf.assignment_id
and paf.assignment_id = l_assignment_id
and ppa.payroll_id = paf.payroll_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('R','Q')
and ppa.action_status = 'C'
and paa.action_status IN ('C','S')
and trunc(ppa.date_earned,'MM') = trunc(l_effective_date,'MM');
select pose.organization_id_child org
from per_org_structure_elements pose
connect by pose.organization_id_parent = prior pose.organization_id_child
and pose.org_structure_version_id = p_org_structure_version_id
start with pose.organization_id_parent = p_organisation_id
and pose.org_structure_version_id = p_org_structure_version_id
union
select p_organisation_id org
from dual;
SELECT global_value
FROM ff_globals_f
WHERE global_name = 'SA_GOSI_BASE_LOWER_LIMIT'
AND legislation_code = 'SA'
AND business_group_id IS NULL
AND l_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT global_value
FROM ff_globals_f
WHERE global_name = 'SA_GOSI_BASE_UPPER_LIMIT'
AND legislation_code = 'SA'
AND business_group_id IS NULL
AND l_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT hoi.org_information3 mob_type
,hoi.org_information4 tel_type
FROM hr_organization_information hoi
WHERE hoi.organization_id = l_bus_grp_id
AND hoi.org_information_context = 'SA_HR_BG_INFO';
SELECT phone_number
FROM per_phones pp,
per_all_people_f pap
WHERE pp.parent_id = pap.person_id
AND pp.phone_type = l_phone_type
AND pap.person_id = l_person_id
AND l_effective_date BETWEEN pp.date_from
AND nvl(date_to,to_date('31-12-4712','DD-MM-YYYY'));
vXMLTable.DELETE;
insert into fnd_sessions(session_id,effective_date) values (userenv('sessionid'), l_effective_date);
T_EMP.DELETE;
T_LEGAL_ENTITY.DELETE;
select 1 into i
from hr_assignment_set_amendments haa
,per_all_assignments_f paa
,hr_soft_coding_keyflex hscl
,per_periods_of_service pos
where assignment_set_id = p_assignment_set_id
and include_or_exclude = 'I'
and hscl.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
and paa.assignment_id = haa.assignment_id
and haa.assignment_id = p_assignment_id
--and l_effective_date between paa.effective_start_date and paa.effective_end_date
and hscl.segment1 = to_char(p_organisation_id)
and hscl.id_flex_num = 20
and (nvl(hscl.segment3,'N') = 'Y' OR nvl(hscl.segment5,'N') = 'Y')
and paa.period_of_service_id = pos.period_of_service_id
and ((l_form = 'NU'
and trunc(pos.date_start,'MM') = trunc(l_effective_date,'MM')
and trunc(pos.date_start) <= trunc(l_effective_date)
and hscl.segment2 is null)
or (l_form = 'NR'
and trunc(pos.date_start,'MM') = trunc(l_effective_date,'MM')
and trunc(pos.date_start) <= trunc(l_effective_date)
and hscl.segment2 is not null)
or (l_form = 'TM'
and trunc(pos.actual_termination_date,'MM') = trunc(l_effective_date,'MM')
and trunc(pos.actual_termination_date) <= trunc(l_effective_date)))
and rownum < 2;
select distinct paa.assignment_id
into t_emp(0).emp_id
from per_all_assignments_f paa,
per_periods_of_service pos,
hr_soft_coding_keyflex hsck
where paa.assignment_id = p_assignment_id
--and p_effective_date between paa.effective_start_date and paa.effective_end_date
and hsck.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
and hsck.ID_FLEX_NUM = 20
and hsck.segment1= to_char(p_organisation_id)
and (nvl(hsck.segment3,'N') = 'Y' OR nvl(hsck.segment5,'N') = 'Y')
and paa.period_of_service_id = pos.period_of_service_id
and ((l_form = 'NU'
and trunc(pos.date_start,'MM') = trunc(l_effective_date,'MM')
and trunc(pos.date_start) <= trunc(l_effective_date)
and hsck.segment2 is null)
or (l_form = 'NR'
and trunc(pos.date_start,'MM') = trunc(l_effective_date,'MM')
and trunc(pos.date_start) <= trunc(l_effective_date)
and hsck.segment2 is not null)
or (l_form = 'TM'
and trunc(pos.actual_termination_date,'MM') = trunc(l_effective_date,'MM')
and trunc(pos.actual_termination_date) <= trunc(l_effective_date)));
select name
into l_employer_name
from hr_organization_units
where organization_id = l_org_id;
select name
into l_employer_gosi_office_name
from hr_organization_units
where organization_id = l_employer_gosi_office;
SELECT hl.meaning
INTO l_city_mn
FROM hr_lookups hl
WHERE hl.lookup_type = 'SA_CITY'
AND hl.lookup_code = l_city
AND hl.enabled_flag = 'Y';
select u.creator_id
into l_defined_balance_id
from ff_user_entities u,
ff_database_items d
where d.user_name = 'GOSI_REFERENCE_EARNINGS_ASG_YTD'
and u.user_entity_id = d.user_entity_id
and u.legislation_code = 'SA'
and u.business_group_id is null
and u.creator_type = 'B';
select distinct TOWN_OR_CITY
into l_work_location
from hr_locations hl,
per_all_assignments_f paa
where hl.location_id = paa.location_id
And paa.assignment_id = t_emp(j).emp_id;
SELECT hl.meaning
INTO l_nationality_mn
FROM hr_lookups hl
WHERE hl.lookup_type = 'NATIONALITY'
and hl.lookup_code = l_nationality
and hl.enabled_flag = 'Y';
select userenv('LANGUAGE') into g_nls_db_char from dual;
Select file_data
Into p_pdf_blob
From fnd_lobs
Where file_id = (select max(file_id) from per_gb_xdo_templates where file_name like '%PAY_G32003_ar_SA.pdf');
Select file_data
Into p_pdf_blob
From fnd_lobs
Where file_id = (select max(file_id) from per_gb_xdo_templates where file_name like '%PAY_G42003_ar_SA.pdf');
Select file_data
Into p_pdf_blob
From fnd_lobs
Where file_id = (select max(file_id) from per_gb_xdo_templates where file_name like '%PAY_G52003_ar_SA.pdf');