The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT hou.name,
hou.date_from,
hou.date_to,
hou.internal_external_flag,
hoi.*,
decode(hoi.org_information11,'YY','YY'
,'Y','YN',
'YYY','YYY'
,'NN') migrated_flag
FROM hr_organization_units hou,
hr_organization_information hoi
WHERE hou.organization_id = l_bg_id
AND hoi.organization_id = hou.organization_id
AND hoi.org_information_context = 'IE_ORG_INFORMATION'
AND NVL(hoi.org_information11,'NN') <> 'NN';
SELECT hou.organization_id,hou.name
FROM hr_organization_units hou
,hr_organization_information hoi1
,hr_organization_information hoi2
WHERE hou.organization_id = hoi1.organization_id
AND hou.organization_id = hoi2.organization_id
AND hou.business_group_id = l_bg_id
AND hoi1.org_information_context = 'CLASS'
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information2 = 'Y'
AND hoi2.org_information_context = 'IE_EMPLOYER_INFO'
AND hou.name = l_name;
SELECT paa.*,ppa.action_type action_type,ppa.effective_date effective_date,
ppa.report_type,ppa.report_qualifier
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = 'C'
AND ppa.business_group_id = p_bg_id
AND ppa.action_type in ('R','Q','P','U','I','B','V','X')
AND (ppa.action_type <> 'X' OR ppa.report_type <> 'P45')
AND to_char(ppa.effective_date,'YYYY') = 2005
AND paa.tax_unit_id = p_tax_unit_id;
SELECT def_old.defined_balance_id old_id
,def_new.defined_balance_id new_id
FROM pay_balance_types pbt
,pay_balance_dimensions dim_old
,pay_balance_dimensions dim_new
,pay_defined_balances def_old
,pay_defined_balances def_new
WHERE pbt.balance_name = p_balance_name
AND dim_old.database_item_suffix = '_PER_PAYE_REF_YTD'
AND dim_old.legislation_code = 'IE'
AND dim_new.legislation_code = 'IE'
AND dim_new.database_item_suffix = '_PER_YTD'
AND pbt.legislation_code = 'IE'
AND def_old.balance_type_id = pbt.balance_type_id
AND def_old.balance_dimension_id = dim_old.balance_dimension_id
AND def_new.balance_type_id = pbt.balance_type_id
AND def_new.balance_dimension_id = dim_new.balance_dimension_id;
SELECT pai.action_information_id,pai.object_version_number,pai.source_id,pai.action_information4
FROM pay_action_information pai
WHERE pai.action_context_id = p_context_id
AND pai.action_information1 = p_defbal_id
AND pai.action_context_type = 'AAP'
AND pai.action_information_category = 'EMEA BALANCES';
UPDATE pay_assignment_actions paa
SET tax_unit_id = NULL
WHERE paa.assignment_action_id = v_assact.assignment_action_id;
pay_action_information_api.update_action_information
(p_action_information_id => l_act_info_id
,p_object_version_number => l_ovn
,p_action_information1 => g_statutory_balance_table(l_index).new_defined_balance_id
,p_action_information4 => fnd_number.number_to_canonical(l_value)
);
HR_ORGanization_api.update_org_information
(
p_validate => FALSE,
p_effective_date => sysdate,
p_org_information_id => v_er_bg.org_information_id,
p_org_info_type_code => 'IE_ORG_INFORMATION',
p_org_information2 => v_er_bg.org_information2,
p_org_information11 => 'Y',
p_object_version_number => l_object_version_number
);
SELECT distinct paa.*,ppa.action_type action_type,ppa.effective_date effective_date,
ppa.report_type,ppa.report_qualifier,asg.person_id person_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_all_assignments_f asg
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = 'C'
AND ppa.effective_date between p_start_date and p_end_date
AND paa.assignment_id = asg.assignment_id
--AND ppa.effective_date between asg.effective_start_date and asg.effective_end_date
AND asg.payroll_id = p_payroll_id
AND ppa.business_group_id = p_bg_id
AND (
(p_migrate_archive_data = 'N' and ppa.action_type in ('R','Q','P','U','I','B','V')) or
(p_migrate_archive_data = 'Y' and ppa.action_type in ('R','Q','P','U','I','B','V','X'))
)
-- AND ppa.action_type in ('R','Q','P','U','I','B','V','X')
AND (ppa.action_type <> 'X' OR ppa.report_type <> 'P45' OR p_p45_migrated = 1)
AND to_char(ppa.effective_date,'YYYY') = 2005
AND paa.tax_unit_id IS NULL
ORDER BY ppa.action_type ASC,
ppa.report_qualifier,
ppa.report_type,
paa.assignment_id,
paa.assignment_action_id DESC;
SELECT pai.action_information_id,pai.object_version_number,pai.source_id,pai.action_information4
FROM pay_action_information pai
WHERE pai.action_context_id = p_context_id
AND pai.action_information1 = p_defbal_id
AND pai.action_context_type = 'AAP'
AND pai.action_information_category = 'EMEA BALANCES';
SELECT pai.action_information_id,pai.object_version_number
FROM pay_action_information pai
WHERE pai.action_context_id = p_asg_action_id
AND pai.action_information_category = p_act_info_cat;
SELECT act_inf.action_information11
FROM pay_action_information act_inf
WHERE act_inf.action_context_id = p_asg_action_id
AND act_inf.action_information_category = 'EMPLOYEE DETAILS'
AND act_inf.action_context_type = 'AAP';
l_stage := 'Update Asg Action' || v_assact.assignment_action_id;
UPDATE pay_assignment_actions paa
SET tax_unit_id = p_org_id
WHERE paa.assignment_action_id = v_assact.assignment_action_id;
l_stage := 'Update Employee Details' || v_assact.assignment_action_id || ' date '|| l_comm_date;
pay_action_information_api.update_action_information
(p_action_information_id => l_act_info_id
,p_object_version_number => l_object_version_number
,p_action_information30 => l_comm_date
);
l_stage := 'Update P45 Information' || v_assact.assignment_action_id || ' person '|| v_assact.person_id;
pay_action_information_api.update_action_information
(p_action_information_id => l_act_info_id
,p_object_version_number => l_object_version_number
,p_action_information8 => v_assact.person_id
);
l_stage := 'Update Action Information' || v_assact.assignment_action_id;
pay_action_information_api.update_action_information
(p_action_information_id => l_act_info_id
,p_object_version_number => l_ovn
,p_action_information1 => g_statutory_balance_table(l_index).new_defined_balance_id
,p_action_information4 => fnd_number.number_to_canonical(l_value)
);
SELECT hou.name,
hou.date_from,
hou.date_to,
hou.internal_external_flag,
hoi.*,
decode(hoi.org_information11,'YY','YY'
,'Y','YN',
'YYY','YYY'
,'NN') migrated_flag
FROM hr_organization_units hou,
hr_organization_information hoi
WHERE hou.organization_id = l_bg_id
AND hoi.organization_id = hou.organization_id
AND hoi.org_information_context = 'IE_ORG_INFORMATION'
AND (
( nvl(hoi.org_information11,'NN') <> 'YY' AND l_p45_migrated = 0) OR
( nvl(hoi.org_information11,'NN') <> 'YYY' AND l_p45_migrated = 1)
);
SELECT scl.id_flex_num id_flex_num,scl.segment2 segment2,
scl.segment1 segment1,scl.segment3 segment3,
pap.*
FROM pay_all_payrolls_f pap,
hr_soft_coding_keyflex scl
WHERE pap.business_group_id = p_bg_id
AND pap.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment1 = p_tax_ref
AND scl.segment3 = p_paye_ref
AND scl.segment4 IS NULL;
SELECT hou.organization_id,hou.name
FROM hr_organization_units hou
,hr_organization_information hoi1
,hr_organization_information hoi2
WHERE hou.organization_id = hoi1.organization_id
AND hou.organization_id = hoi2.organization_id
AND hou.business_group_id = l_bg_id
AND hoi1.org_information_context = 'CLASS'
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information2 = 'Y'
AND hoi2.org_information_context = 'IE_EMPLOYER_INFO'
AND hou.name = l_name;
SELECT papf.payroll_id,papf.payroll_name,papf.effective_start_date,papf.effective_end_date
FROM pay_all_payrolls_f papf
,hr_soft_coding_keyflex hsck
WHERE papf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND papf.business_group_id = p_bg_id
AND hsck.segment4 = to_char(p_tax_unit_id);
SELECT def_old.defined_balance_id old_id
,def_new.defined_balance_id new_id
FROM pay_balance_types pbt
,pay_balance_dimensions dim_old
,pay_balance_dimensions dim_new
,pay_defined_balances def_old
,pay_defined_balances def_new
WHERE pbt.balance_name = p_balance_name
AND dim_old.database_item_suffix = '_PER_YTD'
AND dim_old.legislation_code = 'IE'
AND dim_new.legislation_code = 'IE'
AND dim_new.database_item_suffix = '_PER_PAYE_REF_YTD'
AND pbt.legislation_code = 'IE'
AND def_old.balance_type_id = pbt.balance_type_id
AND def_old.balance_dimension_id = dim_old.balance_dimension_id
AND def_new.balance_type_id = pbt.balance_type_id
AND def_new.balance_dimension_id = dim_new.balance_dimension_id;
SELECT 1
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE DESCRIPTIVE_FLEXFIELD_NAME = 'Action Information DF'
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = 'IE P45 INFORMATION'
AND APPLICATION_COLUMN_NAME = 'ACTION_INFORMATION8'
AND END_USER_COLUMN_NAME = 'Person ID';
l_stage := 'Update Payroll '||v_payroll.payroll_name;
-- Update the payroll
pay_payroll_api.update_payroll
(
p_validate => FALSE,
p_effective_date => v_payroll.effective_start_date,
p_datetrack_mode => 'CORRECTION',
p_payroll_id => l_payroll_id,
p_object_version_number => l_object_version_number,
p_soft_coding_keyflex_id => l_scl_keyflex_id,
p_prl_effective_start_date => l_start_date,
p_prl_effective_end_date => l_end_date,
p_comment_id => l_comment_id
);
HR_ORGanization_api.update_org_information
(
p_validate => FALSE,
p_effective_date => sysdate,
p_org_information_id => v_org.org_information_id,
p_org_info_type_code => 'IE_ORG_INFORMATION',
p_org_information2 => v_org.org_information2,
p_org_information11 => 'YYY',
p_object_version_number => l_object_version_number
);