The following lines contain the word 'select', 'insert', 'update' or 'delete':
hr_organization_units_pkg.insert_row(
x_rowid => l_org_rowid,
x_organization_id => l_organization_id,
x_business_group_id => p_business_group_id,
x_cost_allocation_keyflex_id => NULL,
x_location_id => l_location_id,
x_soft_coding_keyflex_id => NULL,
x_date_from => p_effective_start_date,
x_name => p_pension_provider_name,
x_comments => p_pension_provider_name,
x_date_to => NULL,
x_internal_external_flag => p_internal_external_flag,
x_internal_address_line => NULL,
x_type => p_type,
x_security_profile_id => p_Security_Profile_Id,
x_view_all_orgs => 'Y',
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL );
hr_org_information_pkg.insert_row(
x_rowid => l_org_info_rowid,
x_org_information_id => l_org_information_id,
x_org_information_context => p_org_information_context,
x_organization_id => l_organization_id,
x_org_information1 => p_org_classification,
x_org_information2 => 'Y',
x_org_information3 => NULL,
x_org_information4 => NULL,
x_org_information5 => NULL,
x_org_information6 => NULL,
x_org_information7 => NULL,
x_org_information8 => NULL,
x_org_information9 => NULL,
x_org_information10 => NULL,
x_org_information11 => NULL,
x_org_information12 => NULL,
x_org_information13 => NULL,
x_org_information14 => NULL,
x_org_information15 => NULL,
x_org_information16 => NULL,
x_org_information17 => NULL,
x_org_information18 => NULL,
x_org_information19 => NULL,
x_org_information20 => NULL,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL);
Procedure update_pension_provider
(p_organization_id in number
,p_business_group_id in number
,p_provider_name in varchar2
,p_location_id in number
,p_date_from in date
,p_date_to in date ) Is
Cursor csr_org Is
Select *
from hr_organization_units_v
where organization_id = p_organization_id;
l_proc varchar2(150) := g_package||'update_pension_provider';
hr_organization_units_pkg.Update_Row
(x_Rowid => l_org_rec.row_id,
x_Organization_Id => l_org_rec.organization_id,
x_Business_Group_Id => l_org_rec.business_group_id,
x_Cost_Allocation_Keyflex_Id => l_org_rec.Cost_Allocation_Keyflex_Id,
x_Location_Id => p_location_id,
x_Soft_Coding_Keyflex_Id => l_org_rec.Soft_Coding_Keyflex_Id,
x_Date_From => p_date_from,
x_Name => p_provider_name,
x_Comments => l_org_rec.Comments,
x_Date_To => p_date_to,
x_Internal_External_Flag => l_org_rec.Internal_External_Flag,
x_Internal_Address_Line => l_org_rec.Internal_Address_Line,
x_Type => l_org_rec.Type,
x_Attribute_Category => l_org_rec.Attribute_Category,
x_Attribute1 => l_org_rec.Attribute1,
x_Attribute2 => l_org_rec.Attribute2,
x_Attribute3 => l_org_rec.Attribute3,
x_Attribute4 => l_org_rec.Attribute4,
x_Attribute5 => l_org_rec.Attribute5,
x_Attribute6 => l_org_rec.Attribute6,
x_Attribute7 => l_org_rec.Attribute7,
x_Attribute8 => l_org_rec.Attribute8,
x_Attribute9 => l_org_rec.Attribute9,
x_Attribute10 => l_org_rec.Attribute10,
x_Attribute11 => l_org_rec.Attribute11,
x_Attribute12 => l_org_rec.Attribute12,
x_Attribute13 => l_org_rec.Attribute13,
x_Attribute14 => l_org_rec.Attribute14,
x_Attribute15 => l_org_rec.Attribute15,
x_Attribute16 => l_org_rec.Attribute16,
x_Attribute17 => l_org_rec.Attribute17,
x_Attribute18 => l_org_rec.Attribute18,
x_Attribute19 => l_org_rec.Attribute19,
x_Attribute20 => l_org_rec.Attribute20
);
End update_pension_provider;
hr_org_information_pkg.insert_row
(x_rowid => l_hoi_rowid,
x_org_information_id => l_org_information_id,
x_org_information_context => p_org_information_context,
x_organization_id => p_organization_id,
x_org_information1 => to_char(p_pension_type_id),
x_org_information2 => NULL,
x_org_information3 => NULL,
x_org_information4 => NULL,
x_org_information5 => NULL,
x_org_information6 => NULL,
x_org_information7 => NULL,
x_org_information8 => NULL,
x_org_information9 => NULL,
x_org_information10 => NULL,
x_org_information11 => NULL,
x_org_information12 => NULL,
x_org_information13 => NULL,
x_org_information14 => NULL,
x_org_information15 => NULL,
x_org_information16 => NULL,
x_org_information17 => NULL,
x_org_information18 => NULL,
x_org_information19 => NULL,
x_org_information20 => NULL,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL
);
hr_org_information_pkg.insert_row
(x_rowid => l_hoi_rowid,
x_org_information_id => l_org_information_id,
x_org_information_context => p_org_information_context,
x_organization_id => p_organization_id,
x_org_information1 => p_provider_reference_number,
x_org_information2 => NULL,
x_org_information3 => NULL,
x_org_information4 => NULL,
x_org_information5 => NULL,
x_org_information6 => NULL,
x_org_information7 => NULL,
x_org_information8 => NULL,
x_org_information9 => NULL,
x_org_information10 => NULL,
x_org_information11 => NULL,
x_org_information12 => NULL,
x_org_information13 => NULL,
x_org_information14 => NULL,
x_org_information15 => NULL,
x_org_information16 => NULL,
x_org_information17 => NULL,
x_org_information18 => NULL,
x_org_information19 => NULL,
x_org_information20 => NULL,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL
);
hr_org_information_pkg.insert_row
(x_rowid => l_hoi_rowid,
x_org_information_id => l_org_information_id,
x_org_information_context => 'CLASS',
x_organization_id => p_organization_id,
x_org_information1 => 'HR_PAYEE',
x_org_information2 => 'Y',
x_org_information3 => NULL,
x_org_information4 => NULL,
x_org_information5 => NULL,
x_org_information6 => NULL,
x_org_information7 => NULL,
x_org_information8 => NULL,
x_org_information9 => NULL,
x_org_information10 => NULL,
x_org_information11 => NULL,
x_org_information12 => NULL,
x_org_information13 => NULL,
x_org_information14 => NULL,
x_org_information15 => NULL,
x_org_information16 => NULL,
x_org_information17 => NULL,
x_org_information18 => NULL,
x_org_information19 => NULL,
x_org_information20 => NULL,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL
);
Select min(effective_start_date), max(effective_end_date)
from pqp_pension_types_f
where pension_type_id = p_pension_type_id;
Select rowid
from hr_organization_information
where org_information_context = 'PQP_NL_ER_PENSION_TYPES'
and org_information_id = p_org_information_id
and organization_id = p_organization_id;
Select pty.pension_type_name
from hr_organization_information hoi,
pqp_pension_types_f pty
where hoi.org_information_context = 'PQP_NL_ER_PENSION_TYPES'
and hoi.org_information2 = to_char(p_pension_type_id)
and hoi.organization_id = p_organization_id
and to_char(pty.pension_type_id) = hoi.org_information2
and to_char(p_pension_provider_id) = hoi.org_information1
and rownum = 1;
If p_transaction_mode = 'INSERT' Then
hr_utility.set_location(' INSERT Mode, Calling: hr_org_information_pkg.Insert_Row', 15);
hr_org_information_pkg.insert_row
(x_rowid => l_hoi_rowid,
x_org_information_id => l_org_information_id,
x_org_information_context => 'PQP_NL_ER_PENSION_TYPES',
x_organization_id => p_organization_id,
x_org_information1 => to_char(p_pension_provider_id), -- Pension Provider
x_org_information2 => to_char(p_pension_type_id), -- Pension Type
x_org_information3 => p_registration_number, -- Registration Number
x_org_information4 => fnd_date.date_to_canonical(p_start_date),-- Start Date
x_org_information5 => fnd_date.date_to_canonical(p_end_date), -- End Date
x_org_information6 => NULL,
x_org_information7 => NULL,
x_org_information8 => NULL,
x_org_information9 => NULL,
x_org_information10 => NULL,
x_org_information11 => NULL,
x_org_information12 => NULL,
x_org_information13 => NULL,
x_org_information14 => NULL,
x_org_information15 => NULL,
x_org_information16 => NULL,
x_org_information17 => NULL,
x_org_information18 => NULL,
x_org_information19 => NULL,
x_org_information20 => NULL,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL
);
ElsIf p_transaction_mode = 'UPDATE' Then
hr_utility.set_location(' UPDATE Mode, Calling: hr_org_information_pkg.Update_Row', 25);
hr_org_information_pkg.Update_Row
(X_Rowid => l_hoi_rowid,
X_Org_Information_Id => p_org_information_id,
x_org_information_context => 'PQP_NL_ER_PENSION_TYPES',
x_organization_id => p_organization_id,
x_org_information1 => to_char(p_pension_provider_id), -- Pension Provider
x_org_information2 => to_char(p_pension_type_id), -- Pension Type
x_org_information3 => p_registration_number, -- Registration Number
x_org_information4 => fnd_date.date_to_canonical(p_start_date),-- Start Date
x_org_information5 => fnd_date.date_to_canonical(p_end_date), -- End Date
x_org_information6 => NULL,
x_org_information7 => NULL,
x_org_information8 => NULL,
x_org_information9 => NULL,
x_org_information10 => NULL,
x_org_information11 => NULL,
x_org_information12 => NULL,
x_org_information13 => NULL,
x_org_information14 => NULL,
x_org_information15 => NULL,
x_org_information16 => NULL,
x_org_information17 => NULL,
x_org_information18 => NULL,
x_org_information19 => NULL,
x_org_information20 => NULL,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL
);
Procedure delete_pension_types
(p_organization_id in number
,p_business_group_id in number
,p_legislation_code in varchar2
,p_pension_type_id in number
,p_org_information_id in number
) IS
Cursor csr_org_info(c_org_info_context in varchar2) Is
Select rowid
from hr_organization_information
where org_information_context = c_org_info_context
and org_information_id = p_org_information_id
and organization_id = p_organization_id;
select 'x'
from hr_organization_information
where org_information_context = 'PQP_NL_ER_PENSION_TYPES'
-- ORG_INFORMATION2 is the pension_type_id from NL
and ORG_INFORMATION2 = to_char(p_pension_type_id)
and ORG_INFORMATION1 = to_char(p_organization_id);
Select 1
from pay_element_type_extra_info eei
where ((eei.information_type = 'PQP_NL_PRE_TAX_DEDUCTIONS'
and eei.eei_information_category = 'PQP_NL_PRE_TAX_DEDUCTIONS')
or (eei.information_type = 'PQP_NL_SAVINGS_DEDUCTIONS'
and eei.eei_information_category = 'PQP_NL_SAVINGS_DEDUCTIONS')
or (eei.information_type = 'PQP_NL_ABP_DEDUCTION'
and eei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'))
and eei.eei_information2 = p_pension_type_id
and eei.eei_information3 = p_organization_id;
Select 1
from pay_element_type_extra_info eei
where eei.information_type = 'PQP_GB_PENSION_SCHEME_INFO'
and eei.eei_information_category = 'PQP_GB_PENSION_SCHEME_INFO'
and eei.eei_information3 = p_pension_type_id
and eei.eei_information2 = p_organization_id;
Select 1
from pay_element_type_extra_info eei
where eei.information_type = 'HU_PENSION_SCHEME_INFO'
and eei.eei_information_category = 'HU_PENSION_SCHEME_INFO'
and eei.eei_information3 = p_pension_type_id
and eei.eei_information2 = p_organization_id;
Select 1
from pay_element_type_extra_info eei
where eei.information_type = 'IE_PENSION_SCHEME_INFO'
and eei.eei_information_category = 'IE_PENSION_SCHEME_INFO'
and eei.eei_information1 = p_pension_type_id
and eei.eei_information2 = p_organization_id;
hr_org_information_pkg.delete_row(l_org_info_rowid);
hr_org_information_pkg.delete_row(l_org_info_rowid);
hr_org_information_pkg.delete_row(l_org_info_rowid);
hr_org_information_pkg.delete_row(l_org_info_rowid);
End Delete_Pension_Types;
Procedure delete_third_party_paymnt
(p_organization_id in number
) IS
-- cursor to select the rowid for the org information row
Cursor csr_org_info Is
Select rowid
from hr_organization_information
where org_information_context = 'CLASS'
and org_information1 = 'HR_PAYEE'
and org_information2 = 'Y'
and organization_id = p_organization_id;
hr_org_information_pkg.delete_row(l_org_info_rowid);
End Delete_third_party_paymnt;
Procedure delete_pension_provider_nl
(p_organization_id in number)
Is
Cursor csr_org_info Is
Select rowid,org_information_id,org_information_context,org_information1
from hr_organization_information
where org_information_context = 'CLASS'
and org_information1 = 'FR_PENSION'
and organization_id = p_organization_id;
Select rowid
from hr_organization_information
where org_information_context = 'PQP_NL_ER_PENSION_TYPES'
and org_information1 = p_organization_id;
Select rowid
from hr_organization_information
where org_information_context = 'PQP_NL_PENSION_TYPES'
and organization_id = p_organization_id;
Select 1
from pay_element_type_extra_info eei
where ((eei.information_type = 'PQP_NL_PRE_TAX_DEDUCTIONS'
and eei.eei_information_category = 'PQP_NL_PRE_TAX_DEDUCTIONS')
or (eei.information_type = 'PQP_NL_SAVINGS_DEDUCTIONS'
and eei.eei_information_category = 'PQP_NL_SAVINGS_DEDUCTIONS')
or (eei.information_type = 'PQP_NL_ABP_DEDUCTION'
and eei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'))
and eei.eei_information3 = p_organization_id;
hr_org_information_pkg.delete_row(l_org_info_rowid);
hr_org_information_pkg.update_row(
x_rowid => l_org_info_rowid,
x_org_information_id => l_org_information_id,
x_org_information_context => l_org_information_context,
x_organization_id => p_organization_id,
x_org_information1 => l_org_classification,
x_org_information2 => 'N',
x_org_information3 => NULL,
x_org_information4 => NULL,
x_org_information5 => NULL,
x_org_information6 => NULL,
x_org_information7 => NULL,
x_org_information8 => NULL,
x_org_information9 => NULL,
x_org_information10 => NULL,
x_org_information11 => NULL,
x_org_information12 => NULL,
x_org_information13 => NULL,
x_org_information14 => NULL,
x_org_information15 => NULL,
x_org_information16 => NULL,
x_org_information17 => NULL,
x_org_information18 => NULL,
x_org_information19 => NULL,
x_org_information20 => NULL,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL);
End delete_pension_provider_nl;
Procedure delete_pension_provider_gb
(p_organization_id in number
,p_effective_date in date)
Is
Cursor csr_org Is
Select *
from hr_organization_units_v
where organization_id = p_organization_id;
Select rowid,org_information_id,org_information_context,org_information1
from hr_organization_information
where org_information_context = 'CLASS'
and org_information1 = 'FR_PENSION'
and organization_id = p_organization_id;
Select rowid,org_information_id,org_information_context,org_information1
from hr_organization_information
where org_information_context = 'CLASS'
and org_information1 = 'HR_PAYEE'
and organization_id = p_organization_id;
Select rowid
from hr_organization_information
where org_information_context = 'PQP_GB_PENSION_TYPES_INFO'
and organization_id = p_organization_id;
Select 1
from pay_element_type_extra_info eei
where eei.information_type = 'PQP_GB_PENSION_SCHEME_INFO'
and eei.eei_information_category = 'PQP_GB_PENSION_SCHEME_INFO'
and eei.eei_information2 = p_organization_id;
hr_org_information_pkg.update_row(
x_rowid => l_org_info_rowid,
x_org_information_id => l_org_information_id,
x_org_information_context => l_org_information_context,
x_organization_id => p_organization_id,
x_org_information1 => l_org_classification,
x_org_information2 => 'N',
x_org_information3 => NULL,
x_org_information4 => NULL,
x_org_information5 => NULL,
x_org_information6 => NULL,
x_org_information7 => NULL,
x_org_information8 => NULL,
x_org_information9 => NULL,
x_org_information10 => NULL,
x_org_information11 => NULL,
x_org_information12 => NULL,
x_org_information13 => NULL,
x_org_information14 => NULL,
x_org_information15 => NULL,
x_org_information16 => NULL,
x_org_information17 => NULL,
x_org_information18 => NULL,
x_org_information19 => NULL,
x_org_information20 => NULL,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL);
hr_org_information_pkg.delete_row(l_org_info_rowid);
hr_org_information_pkg.update_row(
x_rowid => l_org_info_rowid,
x_org_information_id => l_org_information_id,
x_org_information_context => l_org_information_context,
x_organization_id => p_organization_id,
x_org_information1 => l_org_classification,
x_org_information2 => 'N',
x_org_information3 => NULL,
x_org_information4 => NULL,
x_org_information5 => NULL,
x_org_information6 => NULL,
x_org_information7 => NULL,
x_org_information8 => NULL,
x_org_information9 => NULL,
x_org_information10 => NULL,
x_org_information11 => NULL,
x_org_information12 => NULL,
x_org_information13 => NULL,
x_org_information14 => NULL,
x_org_information15 => NULL,
x_org_information16 => NULL,
x_org_information17 => NULL,
x_org_information18 => NULL,
x_org_information19 => NULL,
x_org_information20 => NULL,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL);
hr_organization_units_pkg.Update_Row
(x_Rowid => l_org_rec.row_id,
x_Organization_Id => l_org_rec.organization_id,
x_Business_Group_Id => l_org_rec.business_group_id,
x_Cost_Allocation_Keyflex_Id => l_org_rec.Cost_Allocation_Keyflex_Id,
x_Location_Id => l_org_rec.Location_Id,
x_Soft_Coding_Keyflex_Id => l_org_rec.Soft_Coding_Keyflex_Id,
x_Date_From => l_org_rec.Date_From,
x_Name => l_org_rec.Name,
x_Comments => l_org_rec.Comments,
x_Date_To => p_effective_date,
x_Internal_External_Flag => l_org_rec.Internal_External_Flag,
x_Internal_Address_Line => l_org_rec.Internal_Address_Line,
x_Type => l_org_rec.Type,
x_Attribute_Category => l_org_rec.Attribute_Category,
x_Attribute1 => l_org_rec.Attribute1,
x_Attribute2 => l_org_rec.Attribute2,
x_Attribute3 => l_org_rec.Attribute3,
x_Attribute4 => l_org_rec.Attribute4,
x_Attribute5 => l_org_rec.Attribute5,
x_Attribute6 => l_org_rec.Attribute6,
x_Attribute7 => l_org_rec.Attribute7,
x_Attribute8 => l_org_rec.Attribute8,
x_Attribute9 => l_org_rec.Attribute9,
x_Attribute10 => l_org_rec.Attribute10,
x_Attribute11 => l_org_rec.Attribute11,
x_Attribute12 => l_org_rec.Attribute12,
x_Attribute13 => l_org_rec.Attribute13,
x_Attribute14 => l_org_rec.Attribute14,
x_Attribute15 => l_org_rec.Attribute15,
x_Attribute16 => l_org_rec.Attribute16,
x_Attribute17 => l_org_rec.Attribute17,
x_Attribute18 => l_org_rec.Attribute18,
x_Attribute19 => l_org_rec.Attribute19,
x_Attribute20 => l_org_rec.Attribute20
);
End delete_pension_provider_gb;
Procedure delete_pension_provider_hu
(p_organization_id in number
,p_effective_date in date)
Is
Cursor csr_org Is
Select *
from hr_organization_units_v
where organization_id = p_organization_id;
Select rowid,org_information_id,org_information_context,org_information1
from hr_organization_information
where org_information_context = 'CLASS'
and org_information1 = 'FR_PENSION'
and organization_id = p_organization_id;
Select rowid,org_information_id,org_information_context,org_information1
from hr_organization_information
where org_information_context = 'CLASS'
and org_information1 = 'HR_PAYEE'
and organization_id = p_organization_id;
Select rowid
from hr_organization_information
where org_information_context = 'HU_PENSION_TYPES_INFO'
and organization_id = p_organization_id;
Select 1
from pay_element_type_extra_info eei
where eei.information_type = 'HU_PENSION_SCHEME_INFO'
and eei.eei_information_category = 'HU_PENSION_SCHEME_INFO'
and eei.eei_information2 = p_organization_id;
hr_org_information_pkg.update_row(
x_rowid => l_org_info_rowid,
x_org_information_id => l_org_information_id,
x_org_information_context => l_org_information_context,
x_organization_id => p_organization_id,
x_org_information1 => l_org_classification,
x_org_information2 => 'N',
x_org_information3 => NULL,
x_org_information4 => NULL,
x_org_information5 => NULL,
x_org_information6 => NULL,
x_org_information7 => NULL,
x_org_information8 => NULL,
x_org_information9 => NULL,
x_org_information10 => NULL,
x_org_information11 => NULL,
x_org_information12 => NULL,
x_org_information13 => NULL,
x_org_information14 => NULL,
x_org_information15 => NULL,
x_org_information16 => NULL,
x_org_information17 => NULL,
x_org_information18 => NULL,
x_org_information19 => NULL,
x_org_information20 => NULL,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL);
hr_org_information_pkg.delete_row(l_org_info_rowid);
hr_org_information_pkg.update_row(
x_rowid => l_org_info_rowid,
x_org_information_id => l_org_information_id,
x_org_information_context => l_org_information_context,
x_organization_id => p_organization_id,
x_org_information1 => l_org_classification,
x_org_information2 => 'N',
x_org_information3 => NULL,
x_org_information4 => NULL,
x_org_information5 => NULL,
x_org_information6 => NULL,
x_org_information7 => NULL,
x_org_information8 => NULL,
x_org_information9 => NULL,
x_org_information10 => NULL,
x_org_information11 => NULL,
x_org_information12 => NULL,
x_org_information13 => NULL,
x_org_information14 => NULL,
x_org_information15 => NULL,
x_org_information16 => NULL,
x_org_information17 => NULL,
x_org_information18 => NULL,
x_org_information19 => NULL,
x_org_information20 => NULL,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL);
hr_organization_units_pkg.Update_Row
(x_Rowid => l_org_rec.row_id,
x_Organization_Id => l_org_rec.organization_id,
x_Business_Group_Id => l_org_rec.business_group_id,
x_Cost_Allocation_Keyflex_Id => l_org_rec.Cost_Allocation_Keyflex_Id,
x_Location_Id => l_org_rec.Location_Id,
x_Soft_Coding_Keyflex_Id => l_org_rec.Soft_Coding_Keyflex_Id,
x_Date_From => l_org_rec.Date_From,
x_Name => l_org_rec.Name,
x_Comments => l_org_rec.Comments,
x_Date_To => p_effective_date,
x_Internal_External_Flag => l_org_rec.Internal_External_Flag,
x_Internal_Address_Line => l_org_rec.Internal_Address_Line,
x_Type => l_org_rec.Type,
x_Attribute_Category => l_org_rec.Attribute_Category,
x_Attribute1 => l_org_rec.Attribute1,
x_Attribute2 => l_org_rec.Attribute2,
x_Attribute3 => l_org_rec.Attribute3,
x_Attribute4 => l_org_rec.Attribute4,
x_Attribute5 => l_org_rec.Attribute5,
x_Attribute6 => l_org_rec.Attribute6,
x_Attribute7 => l_org_rec.Attribute7,
x_Attribute8 => l_org_rec.Attribute8,
x_Attribute9 => l_org_rec.Attribute9,
x_Attribute10 => l_org_rec.Attribute10,
x_Attribute11 => l_org_rec.Attribute11,
x_Attribute12 => l_org_rec.Attribute12,
x_Attribute13 => l_org_rec.Attribute13,
x_Attribute14 => l_org_rec.Attribute14,
x_Attribute15 => l_org_rec.Attribute15,
x_Attribute16 => l_org_rec.Attribute16,
x_Attribute17 => l_org_rec.Attribute17,
x_Attribute18 => l_org_rec.Attribute18,
x_Attribute19 => l_org_rec.Attribute19,
x_Attribute20 => l_org_rec.Attribute20
);
End delete_pension_provider_hu;
Procedure delete_pension_provider_ie
(p_organization_id in number
,p_effective_date in date)
Is
Cursor csr_org Is
Select *
from hr_organization_units_v
where organization_id = p_organization_id;
Select rowid,org_information_id,org_information_context,org_information1
from hr_organization_information
where org_information_context = 'CLASS'
and org_information1 = 'IE_PENSION'
and organization_id = p_organization_id;
Select rowid,org_information_id,org_information_context,org_information1
from hr_organization_information
where org_information_context = 'CLASS'
and org_information1 = 'HR_PAYEE'
and organization_id = p_organization_id;
Select rowid
from hr_organization_information
where org_information_context = 'IE_PENSION_TYPES_INFO'
and organization_id = p_organization_id;
Select 1
from pay_element_type_extra_info eei
where eei.information_type = 'IE_PENSION_SCHEME_INFO'
and eei.eei_information_category = 'IE_PENSION_SCHEME_INFO'
and eei.eei_information2 = p_organization_id;
hr_org_information_pkg.update_row(
x_rowid => l_org_info_rowid,
x_org_information_id => l_org_information_id,
x_org_information_context => l_org_information_context,
x_organization_id => p_organization_id,
x_org_information1 => l_org_classification,
x_org_information2 => 'N',
x_org_information3 => NULL,
x_org_information4 => NULL,
x_org_information5 => NULL,
x_org_information6 => NULL,
x_org_information7 => NULL,
x_org_information8 => NULL,
x_org_information9 => NULL,
x_org_information10 => NULL,
x_org_information11 => NULL,
x_org_information12 => NULL,
x_org_information13 => NULL,
x_org_information14 => NULL,
x_org_information15 => NULL,
x_org_information16 => NULL,
x_org_information17 => NULL,
x_org_information18 => NULL,
x_org_information19 => NULL,
x_org_information20 => NULL,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL);
hr_org_information_pkg.delete_row(l_org_info_rowid);
hr_org_information_pkg.update_row(
x_rowid => l_org_info_rowid,
x_org_information_id => l_org_information_id,
x_org_information_context => l_org_information_context,
x_organization_id => p_organization_id,
x_org_information1 => l_org_classification,
x_org_information2 => 'N',
x_org_information3 => NULL,
x_org_information4 => NULL,
x_org_information5 => NULL,
x_org_information6 => NULL,
x_org_information7 => NULL,
x_org_information8 => NULL,
x_org_information9 => NULL,
x_org_information10 => NULL,
x_org_information11 => NULL,
x_org_information12 => NULL,
x_org_information13 => NULL,
x_org_information14 => NULL,
x_org_information15 => NULL,
x_org_information16 => NULL,
x_org_information17 => NULL,
x_org_information18 => NULL,
x_org_information19 => NULL,
x_org_information20 => NULL,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL);
hr_organization_units_pkg.Update_Row
(x_Rowid => l_org_rec.row_id,
x_Organization_Id => l_org_rec.organization_id,
x_Business_Group_Id => l_org_rec.business_group_id,
x_Cost_Allocation_Keyflex_Id => l_org_rec.Cost_Allocation_Keyflex_Id,
x_Location_Id => l_org_rec.Location_Id,
x_Soft_Coding_Keyflex_Id => l_org_rec.Soft_Coding_Keyflex_Id,
x_Date_From => l_org_rec.Date_From,
x_Name => l_org_rec.Name,
x_Comments => l_org_rec.Comments,
x_Date_To => p_effective_date,
x_Internal_External_Flag => l_org_rec.Internal_External_Flag,
x_Internal_Address_Line => l_org_rec.Internal_Address_Line,
x_Type => l_org_rec.Type,
x_Attribute_Category => l_org_rec.Attribute_Category,
x_Attribute1 => l_org_rec.Attribute1,
x_Attribute2 => l_org_rec.Attribute2,
x_Attribute3 => l_org_rec.Attribute3,
x_Attribute4 => l_org_rec.Attribute4,
x_Attribute5 => l_org_rec.Attribute5,
x_Attribute6 => l_org_rec.Attribute6,
x_Attribute7 => l_org_rec.Attribute7,
x_Attribute8 => l_org_rec.Attribute8,
x_Attribute9 => l_org_rec.Attribute9,
x_Attribute10 => l_org_rec.Attribute10,
x_Attribute11 => l_org_rec.Attribute11,
x_Attribute12 => l_org_rec.Attribute12,
x_Attribute13 => l_org_rec.Attribute13,
x_Attribute14 => l_org_rec.Attribute14,
x_Attribute15 => l_org_rec.Attribute15,
x_Attribute16 => l_org_rec.Attribute16,
x_Attribute17 => l_org_rec.Attribute17,
x_Attribute18 => l_org_rec.Attribute18,
x_Attribute19 => l_org_rec.Attribute19,
x_Attribute20 => l_org_rec.Attribute20
);
End delete_pension_provider_ie;
Procedure update_pension_provider_swi
(p_organization_id in number
,p_business_group_id in number
,p_provider_name in varchar2
,p_location_id in number
,p_date_from in date
,p_date_to in date ) Is
--
-- Variables for API Boolean parameters
l_validate boolean;
l_proc varchar2(72) := 'update_pension_provider';
savepoint update_pension_provider;
pqp_pension_providers.update_pension_provider
(p_organization_id => p_organization_id
,p_business_group_id => p_business_group_id
,p_provider_name => p_provider_name
,p_location_id => p_location_id
,p_date_from => p_date_from
,p_date_to => p_date_to
);
rollback to update_pension_provider;
rollback to update_pension_provider;
End update_pension_provider_swi;
Procedure delete_pension_provider_swi
(p_organization_id in number
,p_effective_date in date
,p_business_group_id in number)
Is
--
-- Variables for API Boolean parameters
l_validate boolean;
l_proc varchar2(72) := 'delete_pension_provider';
savepoint delete_pension_provider;
pqp_pension_providers.delete_pension_provider_nl
(p_organization_id => p_organization_id);
pqp_pension_providers.delete_pension_provider_gb
(p_organization_id => p_organization_id
,p_effective_date => p_effective_date);
pqp_pension_providers.delete_pension_provider_hu
(p_organization_id => p_organization_id
,p_effective_date => p_effective_date);
pqp_pension_providers.delete_pension_provider_ie
(p_organization_id => p_organization_id
,p_effective_date => p_effective_date);
rollback to delete_pension_provider;
rollback to delete_pension_provider;
End delete_pension_provider_swi;
procedure delete_pension_types_swi
(p_organization_id in number
,p_business_group_id in number
,p_legislation_code in varchar2
,p_pension_type_id in number
,p_org_information_id in number
) IS
--
-- Variables for API Boolean parameters
l_validate boolean;
l_proc varchar2(72) := 'delete_pension_types';
savepoint delete_pension_types;
pqp_pension_providers.delete_pension_types
(p_organization_id => p_organization_id
,p_business_group_id => p_business_group_id
,p_legislation_code => p_legislation_code
,p_pension_type_id => p_pension_type_id
,p_org_information_id => p_org_information_id
);
rollback to delete_pension_types;
rollback to delete_pension_types;
END delete_pension_types_swi;
procedure delete_third_party_paymnt_swi
(p_organization_id in number
) IS
--
-- Variables for API Boolean parameters
l_validate boolean;
l_proc varchar2(72) := 'delete_third_party_paymnt_swi';
savepoint delete_third_party_paymnt;
pqp_pension_providers.delete_third_party_paymnt
(p_organization_id => p_organization_id
);
rollback to delete_third_party_paymnt;
rollback to delete_third_party_paymnt;
END delete_third_party_paymnt_swi;
procedure delete_assigned_providers_swi
( p_organization_id in number
,p_org_information_id in number
) Is
--
-- Variables for API Boolean parameters
l_validate boolean;
pqp_pension_providers.delete_assigned_providers
(p_organization_id => p_organization_id
,p_org_information_id => p_org_information_id
);
End delete_assigned_providers_swi;
procedure delete_assigned_providers
( p_organization_id in number
,p_org_information_id in number
) Is
CURSOR csr_org_info IS
Select rowid
From hr_organization_information
Where org_information_context = 'PQP_NL_ER_PENSION_TYPES'
AND organization_id = p_organization_id
AND org_information_id = p_org_information_id;
hr_org_information_pkg.delete_row(l_hoi_rowid);
End delete_assigned_providers;