[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PER_NL_FDR_ARCHIVE.get_parameter(legislative_parameters,'REPORT_DATE')
,TO_NUMBER(PER_NL_FDR_ARCHIVE.get_parameter(legislative_parameters,'ORG_STRUCT_ID'))
,TO_NUMBER(PER_NL_FDR_ARCHIVE.get_parameter(legislative_parameters,'PERSON_ID'))
,TO_NUMBER(PER_NL_FDR_ARCHIVE.get_parameter(legislative_parameters,'ORG_ID'))
,business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
select ppa.legislative_parameters into l_parameter_string
from pay_payroll_actions ppa
where ppa.payroll_action_id = p_payroll_action_id;
SELECT tag
FROM fnd_lookup_values
WHERE lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
AND lookup_code = SUBSTR(USERENV('LANGUAGE'),
INSTR(USERENV('LANGUAGE'), '.') + 1)
AND language = 'US';
select 1 INTO l_return_val
from hr_organization_units hou
where hou.organization_id = p_org_id
and EXISTS (SELECT 1 FROM hr_organization_information hoi1
WHERE hoi1.org_information_context= 'NL_ORG_INFORMATION'
AND hoi1.organization_id=hou.organization_id
AND hoi1.org_information4 IS NOT NULL
AND hoi1.org_information3 IS NOT NULL
UNION
SELECT 1 FROM hr_organization_information hoi2
WHERE hoi2.org_information_context= 'NL_LE_TAX_DETAILS'
AND hoi2.organization_id=hou.organization_id
AND hoi2.org_information1 IS NOT NULL
AND hoi2.org_information2 IS NOT NULL
);
select ppos.date_start into l_ref_date
from per_periods_of_service ppos
where ppos.person_id = p_person_id
and ppos.date_start =
(select max(ppos1.date_start)
from per_periods_of_service ppos1
where ppos1.person_id = p_person_id);
select distinct pap.effective_start_date into l_ref_date
from per_all_people_f pap
where pap.person_id = p_person_id
and pap.effective_start_date =
(select max(pap1.effective_start_date)
from per_all_people_f pap1
where pap1.person_id = p_person_id
and pap1.current_employee_flag = 'Y')
and exists (select 1 from per_people_extra_info ppei
where ppei.person_id=p_person_id
and information_type='NL_FIRST_DAY_REPORT'
and ppei.pei_information2='Y');
select 1 INTO l_return_val
from per_all_assignments_f paa,
per_assignment_status_types past,
per_all_people_f pap,
per_periods_of_service ppos,
per_org_structure_versions posv
where posv.organization_structure_id=p_org_struct_id
and p_report_date between posv.date_from and nvl(posv.date_to,hr_general.end_of_time)
and (paa.organization_id in
((SELECT pose.organization_id_child
FROM per_org_structure_elements pose
WHERE pose.org_structure_version_id = posv.org_structure_version_id
START with pose.organization_id_parent = p_org_id
CONNECT BY prior organization_id_child = organization_id_parent)
UNION
(select p_org_id from dual))
OR
nvl(paa.establishment_id,-1) = p_org_id)
and paa.person_id = p_person_id
and paa.primary_flag = 'Y'
and past.assignment_status_type_id = paa.assignment_status_type_id
and past.per_system_status = 'ACTIVE_ASSIGN'
and get_ref_date(pap.person_id) between paa.effective_start_date and paa.effective_end_date
and paa.business_group_id = p_bg_id
and pap.person_id = p_person_id
and ppos.business_group_id = pap.business_group_id
and pap.effective_start_date >= to_date('04-07-2006','DD-MM-RRRR')
and ppos.person_id = pap.person_id
and ppos.date_start =
(select max(ppos1.date_start)
from per_periods_of_service ppos1
where ppos1.person_id = ppos.person_id)
and ppos.date_start >= to_date('04-07-2006','DD-MM-RRRR')
and (exists(select 1 from per_people_extra_info ppei
where ppei.person_id=p_person_id
and information_type='NL_FIRST_DAY_REPORT'
and ppei.pei_information2='Y')
or not exists(select 1 from per_people_extra_info ppei
where ppei.person_id=p_person_id
and information_type='NL_FIRST_DAY_REPORT')
);
select 1 INTO l_return_val
from per_all_assignments_f paa,
per_assignment_status_types past,
per_all_people_f pap,
per_periods_of_service ppos,
per_org_structure_versions posv
where posv.organization_structure_id=p_org_struct_id
and p_report_date between posv.date_from and nvl(posv.date_to,hr_general.end_of_time)
and paa.person_id = p_person_id
and paa.primary_flag = 'Y'
and past.assignment_status_type_id = paa.assignment_status_type_id
and past.per_system_status = 'ACTIVE_ASSIGN'
and get_ref_date(pap.person_id) between paa.effective_start_date and paa.effective_end_date
and paa.business_group_id = p_bg_id
and pap.person_id = p_person_id
and pap.effective_start_date >= to_date('04-07-2006','DD-MM-RRRR')
and ppos.business_group_id = pap.business_group_id
and ppos.person_id = pap.person_id
and ppos.date_start =
(select max(ppos1.date_start)
from per_periods_of_service ppos1
where ppos1.person_id = ppos.person_id)
and ppos.date_start >= to_date('04-07-2006','DD-MM-RRRR')
and (exists(select 1 from per_people_extra_info ppei
where ppei.person_id=p_person_id
and information_type='NL_FIRST_DAY_REPORT'
and ppei.pei_information2='Y')
or not exists(select 1 from per_people_extra_info ppei
where ppei.person_id=p_person_id
and information_type='NL_FIRST_DAY_REPORT')
)
and (hr_nl_org_info.get_tax_org_id(posv.org_structure_version_id,paa.organization_id) is not null
OR
per_nl_fdr_archive.org_check(pap.business_group_id, null, nvl(paa.establishment_id,-1), to_date(p_report_date,'RRRR/MM/DD')) = 1)
and paa.organization_id in
(select pose.organization_id_parent
from per_org_structure_elements pose
where posv.org_structure_version_id = pose.org_structure_version_id
UNION
select pose.organization_id_child
from per_org_structure_elements pose
where posv.org_structure_version_id = pose.org_structure_version_id);
select 1 INTO l_return_val
from hr_organization_units hou
WHERE HOU.BUSINESS_GROUP_ID = p_bg_id
AND HOU.organization_id = p_org_id
AND EXISTS (SELECT 1 FROM HR_ALL_ORGANIZATION_UNITS HOU1, HR_ORGANIZATION_INFORMATION HOI1
WHERE HOU1.BUSINESS_GROUP_ID = p_bg_id
AND HOI1.ORG_INFORMATION_CONTEXT = 'NL_LE_TAX_DETAILS'
AND HOI1.ORG_INFORMATION1 IS NOT NULL
AND HOI1.ORG_INFORMATION2 IS NOT NULL
AND HOU1.ORGANIZATION_ID = HOI1.ORGANIZATION_ID
AND HOU1.ORGANIZATION_ID = HOU.ORGANIZATION_ID
UNION
SELECT 1 FROM HR_ALL_ORGANIZATION_UNITS HOU2, HR_ORGANIZATION_INFORMATION HOI2
WHERE HOU2.BUSINESS_GROUP_ID = p_bg_id
AND HOI2.ORG_INFORMATION_CONTEXT = 'NL_ORG_INFORMATION'
AND HOI2.ORG_INFORMATION4 IS NOT NULL
AND HOI2.ORG_INFORMATION3 IS NOT NULL
AND HOU2.ORGANIZATION_ID = HOI2.ORGANIZATION_ID
AND HOU2.ORGANIZATION_ID = HOU.ORGANIZATION_ID);
SELECT 1 INTO l_return_val
FROM hr_organization_units hou
WHERE hou.organization_id = p_org_id
AND hou.business_group_id = p_bg_id
AND EXISTS (SELECT 1 FROM hr_organization_units hou1, hr_organization_information hoi1
WHERE hoi1.org_information_context= 'NL_ORG_INFORMATION'
AND hou1.business_group_id=p_bg_id
AND hou1.organization_id=hou.organization_id
AND hou1.organization_id= hoi1.organization_id
AND hoi1.org_information4 IS NOT NULL
AND hoi1.org_information3 IS NOT NULL
AND hou1.organization_id in
(SELECT pose.organization_id_parent
FROM per_org_structure_elements pose,per_org_structure_versions posv
WHERE posv.org_structure_version_id = pose.org_structure_version_id
AND posv.organization_structure_id=p_org_struct_id
AND p_report_date between posv.date_from and nvl(posv.date_to,hr_general.end_of_time)
UNION
SELECT pose.organization_id_child
FROM per_org_structure_elements pose,per_org_structure_versions posv
WHERE posv.org_structure_version_id = pose.org_structure_version_id
AND posv.organization_structure_id=p_org_struct_id
AND p_report_date between posv.date_from and nvl(posv.date_to,hr_general.end_of_time))
UNION
SELECT 1 FROM hr_organization_units hou2, hr_organization_information hoi2
WHERE hoi2.org_information_context= 'NL_LE_TAX_DETAILS'
AND hou2.business_group_id=p_bg_id
AND hou2.organization_id=hou.organization_id
AND hou2.organization_id= hoi2.organization_id
AND hoi2.org_information1 IS NOT NULL
AND hoi2.org_information2 IS NOT NULL
AND hou2.organization_id in
(SELECT pose.organization_id_parent
FROM per_org_structure_elements pose,per_org_structure_versions posv
WHERE posv.org_structure_version_id = pose.org_structure_version_id
AND posv.organization_structure_id=p_org_struct_id
AND p_report_date between posv.date_from and nvl(posv.date_to,hr_general.end_of_time)
UNION
SELECT pose.organization_id_child
FROM per_org_structure_elements pose,per_org_structure_versions posv
WHERE posv.org_structure_version_id = pose.org_structure_version_id
AND posv.organization_structure_id=p_org_struct_id
AND p_report_date between posv.date_from and nvl(posv.date_to,hr_general.end_of_time))
);
|Description: This procedure returns an sql string to select a range |
| of assignments eligible for reporting |
----------------------------------------------------------------------*/
PROCEDURE RANGE_CODE (pactid IN NUMBER
,sqlstr OUT NOCOPY VARCHAR2) is
l_format VARCHAR2(40);
sqlstr := 'SELECT DISTINCT person_id
FROM per_all_people_f pap
,pay_payroll_actions ppa
WHERE ppa.payroll_action_id = :payroll_action_id
AND ppa.business_group_id = pap.business_group_id
ORDER BY pap.person_id';
-- Return cursor that selects no rows
sqlstr := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
select distinct paa.assignment_id assignment_id
from per_all_assignments_f paa,
per_assignment_status_types past,
pay_payroll_actions ppa,
per_all_people_f pap,
per_periods_of_service ppos
where p_person_id between p_start_person_id and p_end_person_id
and pap.person_id = p_person_id
and paa.person_id = pap.person_id
and paa.primary_flag = 'Y'
and past.assignment_status_type_id = paa.assignment_status_type_id
and past.per_system_status = 'ACTIVE_ASSIGN'
and get_ref_date(pap.person_id) between paa.effective_start_date and paa.effective_end_date
and pap.effective_start_date >= to_date('04-07-2006','DD-MM-RRRR')
and ppos.business_group_id = pap.business_group_id
and ppos.person_id = pap.person_id
and ppos.date_start =
(select max(ppos1.date_start)
from per_periods_of_service ppos1
where ppos1.person_id = ppos.person_id)
and ppos.date_start >= to_date('04-07-2006','DD-MM-RRRR')
and pap.business_group_id=ppa.business_group_id
and ppa.payroll_action_id=p_payroll_action_id
and (EXISTS (select 1
from per_people_extra_info pei
where pei.person_id = p_person_id
and pei.information_type = 'NL_FIRST_DAY_REPORT'
and pei.pei_information2 = 'Y')
or NOT EXISTS
(select 1
from per_people_extra_info pei
where pei.person_id = p_person_id
and pei.information_type = 'NL_FIRST_DAY_REPORT'));
select distinct paa.assignment_id assignment_id
from per_all_assignments_f paa,
per_assignment_status_types past,
per_all_people_f pap,
pay_payroll_actions ppa,
per_periods_of_service ppos,
per_org_structure_versions posv
where posv.organization_structure_id=p_org_struct_id
and to_date(p_report_date,'RRRR/MM/DD') between posv.date_from and nvl(posv.date_to,hr_general.end_of_time)
and (paa.organization_id in
((SELECT pose.organization_id_child
FROM per_org_structure_elements pose
WHERE pose.org_structure_version_id = posv.org_structure_version_id
START with pose.organization_id_parent = p_org_id
CONNECT BY prior organization_id_child = organization_id_parent)
UNION
(select p_org_id from dual))
OR
nvl(paa.establishment_id,-1) = p_org_id
)
and paa.person_id = pap.person_id
and paa.primary_flag = 'Y'
and past.assignment_status_type_id = paa.assignment_status_type_id
and past.per_system_status = 'ACTIVE_ASSIGN'
and get_ref_date(pap.person_id) between paa.effective_start_date and paa.effective_end_date
and pap.person_id between p_start_person_id and p_end_person_id
and pap.business_group_id = ppa.business_group_id
and ppa.payroll_action_id = p_payroll_action_id
and pap.effective_start_date >= to_date('04-07-2006','DD-MM-RRRR')
and ppos.business_group_id = pap.business_group_id
and ppos.person_id = pap.person_id
and ppos.date_start =
(select max(ppos1.date_start)
from per_periods_of_service ppos1
where ppos1.person_id = ppos.person_id)
and ppos.date_start >= to_date('04-07-2006','DD-MM-RRRR')
and (EXISTS (select 1
from per_people_extra_info pei
where pei.person_id = pap.person_id
and pei.information_type = 'NL_FIRST_DAY_REPORT'
and pei.pei_information2 = 'Y')
or NOT EXISTS
(select 1
from per_people_extra_info pei
where pei.person_id = pap.person_id
and pei.information_type = 'NL_FIRST_DAY_REPORT'));
select distinct paa.assignment_id assignment_id
from per_all_assignments_f paa,
per_assignment_status_types past,
per_all_people_f pap,
pay_payroll_actions ppa,
per_periods_of_service ppos,
per_org_structure_versions posv
where posv.organization_structure_id=p_org_struct_id
and to_date(p_report_date,'RRRR/MM/DD') between posv.date_from and nvl(posv.date_to,hr_general.end_of_time)
and pap.person_id between p_start_person_id and p_end_person_id
and pap.business_group_id = ppa.business_group_id
and ppa.payroll_action_id = p_payroll_action_id
and pap.effective_start_date >= to_date('04-07-2006','DD-MM-RRRR')
and ppos.business_group_id = pap.business_group_id
and ppos.person_id = pap.person_id
and ppos.date_start =
(select max(ppos1.date_start)
from per_periods_of_service ppos1
where ppos1.person_id = ppos.person_id)
and ppos.date_start >= to_date('04-07-2006','DD-MM-RRRR')
and (EXISTS (select 1
from per_people_extra_info pei
where pei.person_id = pap.person_id
and pei.information_type = 'NL_FIRST_DAY_REPORT'
and pei.pei_information2 = 'Y')
or NOT EXISTS
(select 1
from per_people_extra_info pei
where pei.person_id = pap.person_id
and pei.information_type = 'NL_FIRST_DAY_REPORT'))
and pap.person_id = paa.person_id
and paa.primary_flag = 'Y'
and past.assignment_status_type_id = paa.assignment_status_type_id
and past.per_system_status = 'ACTIVE_ASSIGN'
and get_ref_date(pap.person_id) between paa.effective_start_date and paa.effective_end_date
and (hr_nl_org_info.get_tax_org_id(posv.org_structure_version_id,paa.organization_id) is not null
OR
per_nl_fdr_archive.org_check(pap.business_group_id, null, nvl(paa.establishment_id,-1), to_date(p_report_date,'RRRR/MM/DD')) = 1)
and paa.organization_id in
(select pose.organization_id_parent
from per_org_structure_elements pose
where posv.org_structure_version_id = pose.org_structure_version_id
UNION
select pose.organization_id_child
from per_org_structure_elements pose
where posv.org_structure_version_id = pose.org_structure_version_id);
--hr_utility.set_location('Person selected',40);
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_asg_act_id
FROM dual;
--hr_utility.set_location('Assignment id - '||to_char(v_csr_get_asg_person.assignment_id)||' selected',45);
--hr_utility.set_location('Org selected',50);
--hr_utility.set_location('Selecting assignment '||to_char(v_csr_get_asg_org.assignment_id),55);
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_asg_act_id
FROM dual;
--hr_utility.set_location('Assignment id - '||to_char(v_csr_get_asg_org.assignment_id)||' selected',57);
--hr_utility.set_location('Hier selected',60);
--hr_utility.set_location('Assignment id - '||to_char(v_csr_get_asg_hier.assignment_id)||' selected before inserting', 62);
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_asg_act_id
FROM dual;
--hr_utility.set_location('Assignment id - '||to_char(v_csr_get_asg_hier.assignment_id)||' inserted', 65);
select hou.organization_id org_id,
hou.name org_name,
hoi.org_information4 tax_reg
from pay_assignment_actions paa,
per_all_assignments_f pas,
per_assignment_status_types past,
hr_organization_units hou,
hr_organization_information hoi,
per_org_structure_versions posv
where posv.organization_structure_id=p_org_struct_id
and to_date(p_report_date,'RRRR/MM/DD') between posv.date_from and nvl(posv.date_to,hr_general.end_of_time)
and paa.assignment_action_id = p_assignment_action_id
and pas.assignment_id = paa.assignment_id
and past.assignment_status_type_id = pas.assignment_status_type_id
and past.per_system_status = 'ACTIVE_ASSIGN'
and get_ref_date(pas.person_id) between pas.effective_start_date and pas.effective_end_date
and hou.organization_id = hr_nl_org_info.get_tax_org_id(posv.org_structure_version_id, pas.organization_id)
and hoi.organization_id = hou.organization_id
and hoi.org_information_context = 'NL_ORG_INFORMATION';
select hou.organization_id org_id,
hou.name org_name,
hoi.org_information1 tax_reg
from pay_assignment_actions paa,
per_all_assignments_f pas,
per_assignment_status_types past,
hr_organization_units hou,
hr_organization_information hoi
where paa.assignment_action_id = p_assignment_action_id
and pas.assignment_id = paa.assignment_id
and past.assignment_status_type_id = pas.assignment_status_type_id
and past.per_system_status = 'ACTIVE_ASSIGN'
and get_ref_date(pas.person_id) between pas.effective_start_date and pas.effective_end_date
and hou.organization_id = pas.establishment_id
and hoi.organization_id = hou.organization_id
and hoi.org_information_context = 'NL_LE_TAX_DETAILS';
select ppos.date_start hire_date,
pap.employee_number employee_number,
pap.national_identifier sofi_number,
pap.per_information1 init,
pap.pre_name_adjunct prefix,
pap.last_name last_name,
pap.full_name full_name,
pap.date_of_birth date_of_birth,
pap.person_id person_id,
pap.business_group_id bg_id,
pas.establishment_id establishment_id
from per_all_people_f pap,
per_all_assignments_f pas,
per_assignment_status_types past,
pay_assignment_actions paa,
per_periods_of_service ppos
where paa.assignment_action_id = p_assignment_action_id
and pas.assignment_id = paa.assignment_id
and pap.person_id = pas.person_id
and pas.primary_flag = 'Y'
and past.assignment_status_type_id = pas.assignment_status_type_id
and past.per_system_status = 'ACTIVE_ASSIGN'
and get_ref_date(pap.person_id) between pas.effective_start_date and pas.effective_end_date
and pap.effective_start_date =
(select max(pap1.effective_start_date)
from per_all_people_f pap1
where pap.person_id = pap1.person_id)
and ppos.person_id = pap.person_id
and ppos.date_start =
(select max(date_start)
from per_periods_of_service ppos1
where ppos1.person_id = pap.person_id);
select ppei.person_extra_info_id person_extra_info_id,
ppei.object_version_number object_version_number
from per_people_extra_info ppei
where ppei.person_id = p_person_id
and ppei.information_type = 'NL_FIRST_DAY_REPORT'
and ppei.pei_information2 = 'Y';
SELECT PER_NL_FDR_ARCHIVE.get_parameter(ppa.legislative_parameters,'REPORT_DATE')
INTO l_report_date
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_action_id = p_assignment_action_id;
SELECT ppa.legislative_parameters
INTO l_parameter_string
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_action_id = p_assignment_action_id;
select TO_NUMBER(PER_NL_FDR_ARCHIVE.get_parameter(ppa.legislative_parameters,'ORG_STRUCT_ID'))
INTO l_org_struct_id
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_action_id = p_assignment_action_id;
SELECT value INTO l_directory_path
FROM v$parameter WHERE LOWER(name)='utl_file_dir';
--hr_utility.set_location('Employee number - '||l_employee_number||' selected', 100);
hr_person_extra_info_api.update_person_extra_info
(p_person_extra_info_id=>v_csr_get_peit.person_extra_info_id
,p_object_version_number=>v_csr_get_peit.object_version_number
,p_pei_information1=>to_char(to_date(l_report_date, 'RRRR/MM/DD HH24:MI:SS'),'RRRR/MM/DD HH24:MI:SS')
,p_pei_information2=>'N');
select pai.action_information_id act_id,
pai.object_version_number ovn
from pay_action_information pai,
pay_assignment_actions paa
where paa.payroll_action_id = p_actid
and pai.action_context_id = paa.assignment_action_id
and pai.action_information_category = 'NL FDR EMPLOYEE DETAILS';
select distinct pai.action_information4 org_name,
pai.action_information5 tax_reg_num
from pay_action_information pai,
pay_assignment_actions paa
where paa.payroll_action_id = p_actid
and pai.action_context_id = paa.assignment_action_id
and pai.action_information_category = 'NL FDR EMPLOYEE DETAILS';
select pai.action_information1 employee_number,
pai.action_information2 full_name,
pai.action_information3 sofi_number,
pai.action_information6 hire_date,
pai.action_information7 date_of_birth
from pay_action_information pai,
pay_assignment_actions paa
where paa.payroll_action_id = p_actid
and pai.action_context_id = paa.assignment_action_id
and pai.action_information_category = 'NL FDR EMPLOYEE DETAILS'
and pai.action_information4 = p_org_name;
select distinct pap.employee_number emp_no,
pap.full_name name
from per_all_assignments_f paa,
per_assignment_status_types past,
per_all_people_f pap,
pay_payroll_actions ppa,
per_periods_of_service ppos,
per_org_structure_versions posv
where posv.organization_structure_id=p_org_struct_id
and to_date(p_report_date,'RRRR/MM/DD') between posv.date_from and nvl(posv.date_to,hr_general.end_of_time)
and pap.business_group_id = ppa.business_group_id
and ppa.payroll_action_id = p_payroll_action_id
and pap.effective_start_date >= to_date('04-07-2006','DD-MM-RRRR')
and ppos.business_group_id = pap.business_group_id
and ppos.person_id = pap.person_id
and ppos.date_start =
(select max(ppos1.date_start)
from per_periods_of_service ppos1
where ppos1.person_id = ppos.person_id)
and ppos.date_start >= to_date('04-07-2006','DD-MM-RRRR')
and (EXISTS (select 1
from per_people_extra_info pei
where pei.person_id = pap.person_id
and pei.information_type = 'NL_FIRST_DAY_REPORT'
and pei.pei_information2 = 'Y')
or NOT EXISTS
(select 1
from per_people_extra_info pei
where pei.person_id = pap.person_id
and pei.information_type = 'NL_FIRST_DAY_REPORT'))
and pap.person_id = paa.person_id
and paa.primary_flag = 'Y'
and past.assignment_status_type_id = paa.assignment_status_type_id
and past.per_system_status = 'ACTIVE_ASSIGN'
and get_ref_date(pap.person_id) between paa.effective_start_date and paa.effective_end_date
and hr_nl_org_info.get_tax_org_id(posv.org_structure_version_id,paa.organization_id) is null
and per_nl_fdr_archive.org_check(pap.business_group_id, null, nvl(paa.establishment_id,-1), to_date(p_report_date,'RRRR/MM/DD')) = 0
and paa.organization_id in
(select pose.organization_id_parent
from per_org_structure_elements pose
where posv.org_structure_version_id = pose.org_structure_version_id
UNION
select pose.organization_id_child
from per_org_structure_elements pose
where posv.org_structure_version_id = pose.org_structure_version_id);
SELECT pbg.name
INTO l_bg_name
FROM per_business_groups pbg
WHERE pbg.business_group_id = l_bg_id;
pay_action_information_api.delete_action_information (p_action_information_id => v_csr_get_action_information.act_id
,p_object_version_number => v_csr_get_action_information.ovn);
delete from pay_assignment_actions
where payroll_action_id = p_actid;