[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
hr_utility.set_location('China : Inserting into pay_action_information ', 30);
INSERT INTO pay_action_information
( action_information_id
, action_context_id -- Request Id
, action_context_type -- EXT
, action_information_category -- EXT_INFO
, action_information1 -- PHF / SI Type
, action_information2 -- Start Date
, action_information3 -- End Date
, action_information4 -- Legal Employer Id
, action_information5 -- Business Group Id
, action_information6 -- Contribution Area
, action_information7 -- Contribution Year
, action_information8 -- Filling Date
, action_information9 -- Report Type
)
VALUES
( pay_action_information_s.nextval
, l_request_id
, 'EXT'
, 'EXT_INFO'
, p_phf_si_type
, p_start_date
, p_end_date
, p_legal_employer_id
, p_business_group_id
, p_contribution_area
, p_contribution_year
, p_filling_date
, p_report_type
);
hr_utility.set_location('China : Inserted into pay_action_information ', 40);
PROCEDURE delete_globals
IS
--
l_proc_name VARCHAR2(150);
l_proc_name := 'pay_cn_ext.delete_globals';
DELETE FROM pay_action_information
WHERE action_context_id = l_request_id
AND action_context_type = 'EXT'
AND action_information_category = 'EXT_INFO';
hr_utility.set_location('China : Deleted from pay_action_information ', 40);
END delete_globals;
SELECT decode(parent_request_id,-1,null,parent_request_id)
FROM fnd_concurrent_requests
WHERE request_id = p_request_id;
SELECT action_information1 -- PHF / SI Type
, action_information2 -- Start Date
, action_information3 -- End Date
, action_information4 -- Legal Employer Id
, action_information5 -- Business Group Id
, action_information6 -- Contribution Area
, action_information7 -- Contribution Year
, action_information8 -- Filling Date
, action_information9 -- Report Type
FROM pay_action_information
WHERE action_context_id IN ( p_request_id, p_parent_id)
AND action_context_type = 'EXT'
AND action_information_category = 'EXT_INFO';
SELECT ed.ext_dfn_id
FROM ben_ext_dfn ed, hr_lookups hrl, per_business_groups bg
WHERE ((bg.business_group_id = ed.business_group_id)
OR (bg.legislation_code = ed.legislation_code)
OR (ed.business_group_id IS NULL AND ed.legislation_code IS NULL))
AND bg.business_group_id = p_business_group_id
AND ed.data_typ_cd = hrl.lookup_code
AND hrl.lookup_type = 'BEN_EXT_DATA_TYP'
AND SUBSTR(ed.NAME,1,240) = 'CB Extract';
SELECT org_information11 -- Switch Period Month
FROM hr_organization_information
WHERE organization_id = p_business_group_id
AND org_information_context = 'PER_CONT_AREA_CONT_BASE_CN'
AND org_information1 = p_contribution_area
AND org_information2 = p_phf_si_type
AND org_information10 = 'YEARLY' -- Switch Period Periodicity is hardcoded.
AND org_information3 IS NULL
AND SYSDATE BETWEEN TO_DATE(org_information15,'YYYY/MM/DD HH24:MI:SS')
AND TO_DATE(NVL(org_information16,'4712/12/31 00:00:00'),'YYYY/MM/DD HH24:MI:SS');
delete_globals;
delete_globals;
SELECT ed.ext_dfn_id
FROM ben_ext_dfn ed, hr_lookups hrl, per_business_groups bg
WHERE ((bg.business_group_id = ed.business_group_id)
OR (bg.legislation_code = ed.legislation_code)
OR (ed.business_group_id is null and ed.legislation_code is null))
AND bg.business_group_id = p_business_group_id
AND ed.data_typ_cd = hrl.lookup_code
AND hrl.lookup_type = 'BEN_EXT_DATA_TYP'
AND substr(ed.NAME,1,240) = 'CA Extract';
delete_globals;
delete_globals;
SELECT ed.ext_dfn_id
FROM ben_ext_dfn ed, hr_lookups hrl, per_business_groups bg
WHERE ((bg.business_group_id = ed.business_group_id)
OR (bg.legislation_code = ed.legislation_code)
OR (ed.business_group_id is null and ed.legislation_code is null))
AND bg.business_group_id = p_business_group_id
AND ed.data_typ_cd = hrl.lookup_code
AND hrl.lookup_type = 'BEN_EXT_DATA_TYP'
AND substr(ed.NAME,1,240) = 'EM Extract';
delete_globals;
delete_globals;
SELECT pap.per_information8 exp_indicator
,fnd_number.canonical_to_number(hsck.segment1) Legal_Employer
FROM per_all_assignments_f paa
,per_all_people_f pap
,hr_soft_coding_keyflex hsck
WHERE paa.assignment_id = p_assignment_id
AND paa.business_group_id = p_business_group_id
AND paa.person_id = pap.person_id
AND paa.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND paa.assignment_type = 'E'
AND p_start_date BETWEEN pap.effective_start_date
AND pap.effective_end_date
AND p_start_date BETWEEN paa.effective_start_date
AND paa.effective_end_date;
SELECT pap.per_information8 exp_indicator
,fnd_number.canonical_to_number(hsck.segment1) Legal_Employer
FROM per_all_assignments_f paa
,per_all_people_f pap
,hr_soft_coding_keyflex hsck
WHERE paa.assignment_id = p_assignment_id
AND paa.business_group_id = p_business_group_id
AND paa.person_id = pap.person_id
AND paa.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND paa.assignment_type = 'E'
AND p_end_date BETWEEN pap.effective_start_date
AND pap.effective_end_date
AND p_end_date BETWEEN paa.effective_start_date
AND paa.effective_end_date;
SELECT eev.screen_entry_value
FROM pay_element_entry_values_f eev
,pay_element_entries_f pee
,pay_element_links_f pil
,pay_input_values_f piv
,pay_element_types_f pet
WHERE pet.element_name = p_element_name
AND pet.element_type_id = piv.element_type_id
AND piv.name = p_input_value
AND pet.element_type_id = pil.element_type_id
AND pil.element_link_id = pee.element_link_id
AND pee.assignment_id = p_assignment_id
AND pee.element_entry_id = eev.element_entry_id
AND eev.input_value_id = piv.input_value_id
AND p_date_earned BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND p_date_earned BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND p_date_earned BETWEEN pil.effective_start_date
AND pil.effective_end_date
AND p_date_earned BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND p_date_earned BETWEEN eev.effective_start_date
AND eev.effective_end_date
AND eev.effective_start_date BETWEEN p_start_date
AND p_end_date
AND nvl(pee.entry_type, 'E') = 'E';
SELECT eev.screen_entry_value
FROM pay_element_entry_values_f eev
,pay_element_entries_f pee
,pay_element_links_f pil
,pay_input_values_f piv
,pay_element_types_f pet
WHERE pet.element_name = p_element_name
AND pet.element_type_id = piv.element_type_id
AND piv.name = p_input_value
AND pet.element_type_id = pil.element_type_id
AND pil.element_link_id = pee.element_link_id
AND pee.assignment_id = p_assignment_id
AND pee.element_entry_id = eev.element_entry_id
AND eev.input_value_id = piv.input_value_id
AND p_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND p_effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND p_effective_date BETWEEN pil.effective_start_date
AND pil.effective_end_date
AND p_effective_date BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND p_effective_date BETWEEN eev.effective_start_date
AND eev.effective_end_date
AND nvl(pee.entry_type, 'E') = 'E';
SELECT pap.per_information8 exp_indicator
,fnd_number.canonical_to_number(hsck.segment1) Legal_Employer
FROM per_all_assignments_f paa
,per_all_people_f pap
,hr_soft_coding_keyflex hsck
WHERE paa.assignment_id = p_assignment_id
AND paa.business_group_id = p_business_group_id
AND paa.person_id = pap.person_id
AND paa.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND paa.assignment_type = 'E'
AND p_end_date BETWEEN paa.effective_start_date
AND paa.effective_end_date
AND p_end_date BETWEEN pap.effective_start_date
AND pap.effective_end_date;
SELECT name
FROM hr_all_organization_units
WHERE organization_id = p_legal_employer;
SELECT hoi.org_information10
FROM hr_organization_information hoi
WHERE hoi.organization_id = p_business_group_id
AND REPLACE(ltrim(rtrim(hoi.org_information_context)),' ','_') = 'Business_Group_Information';
SELECT hr_general.decode_lookup('CN_ENTRP_CATEGORY',hoi.org_information8)
FROM hr_organization_information hoi
WHERE hoi.organization_id = p_legal_employer_id
AND REPLACE(ltrim(rtrim(hoi.org_information_context)),' ','_') = 'PER_CORPORATE_INFO_CN';
SELECT hoi.org_information7
FROM hr_organization_information hoi
WHERE hoi.organization_id = p_legal_employer_id
AND REPLACE(ltrim(rtrim(hoi.org_information_context)),' ','_') = 'PER_CORPORATE_INFO_CN';
SELECT hoi.org_information5 -- PHF
,hoi.org_information6 -- Pension
,hoi.org_information15 -- Injury
,hoi.org_information17 -- Maternity
,hoi.org_information19 -- Unemployment
,hoi.org_information7 -- Medical
,hoi.org_information3 -- Enterprise Annuity. (Bug 3593118)
FROM hr_organization_information hoi
WHERE hoi.organization_id = p_legal_employer_id
AND REPLACE(ltrim(rtrim(hoi.org_information_context)),' ','_') = 'PER_EMPLOYER_INFO_CN';
SELECT ppf.PER_INFORMATION4 -- Hukuo Type
FROM per_assignments_f paf
,per_people_f ppf
WHERE paf.assignment_id = p_assignment_id
AND paf.person_id = ppf.person_id;
SELECT hr_general.decode_lookup('CN_RACE',pap.per_information17)
FROM per_all_assignments_f paa
,per_all_people_f pap
WHERE paa.assignment_id = p_assignment_id
AND pap.person_id = paa.person_id
AND p_date_earned BETWEEN paa.effective_start_date
AND paa.effective_end_date
AND p_date_earned BETWEEN pap.effective_start_date
AND pap.effective_end_date;
SELECT hr_general.decode_lookup('CN_HUKOU_TYPE',pap.per_information4 )
FROM per_all_assignments_f paa
,per_all_people_f pap
WHERE paa.assignment_id = p_assignment_id
AND pap.person_id = paa.person_id
AND p_date_earned BETWEEN paa.effective_start_date
AND paa.effective_end_date
AND p_date_earned BETWEEN pap.effective_start_date
AND pap.effective_end_date;
SELECT ppei.pei_information2 Work_Life_Start_Date
FROM per_all_assignments_f paa
,per_all_people_f pap
,per_people_extra_info ppei
WHERE paa.assignment_id = p_assignment_id
AND pap.person_id = paa.person_id
AND ppei.person_id = pap.person_id (+)
AND ppei.information_type = 'PER_OTH_EMP_DATA_CN'
AND p_date_earned BETWEEN paa.effective_start_date
AND paa.effective_end_date
AND p_date_earned BETWEEN pap.effective_start_date
AND pap.effective_end_date;
SELECT hr_general.decode_lookup('JOB_CATEGORIES', pjei.jei_information1)
FROM per_all_assignments_f paa
,per_job_extra_info pjei
WHERE paa.assignment_id = p_assignment_id
AND pjei.job_id = paa.job_id
AND information_type = 'Job Category'
AND jei_information_category = 'Job Category'
AND p_date_earned BETWEEN paa.effective_start_date
AND paa.effective_end_date;
SELECT ppf.PER_INFORMATION4 -- Hukuo Type
FROM per_assignments_f paf
,per_people_f ppf
WHERE paf.assignment_id = p_assignment_id
AND paf.person_id = ppf.person_id;
SELECT defined.defined_balance_id
FROM pay_balance_types bal
, pay_balance_dimensions dim
, pay_defined_balances defined
WHERE bal.legislation_code = 'CN'
AND bal.balance_name = p_balance_name
AND dim.legislation_code = 'CN'
AND dim.dimension_name = p_balance_dimension
AND bal.balance_type_id = defined.balance_type_id
AND dim.balance_dimension_id = defined.balance_dimension_id;
SELECT max(paa.assignment_action_id)
FROM pay_assignment_actions paa
, pay_payroll_actions ppa
, per_all_assignments_f paf
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND paf.assignment_id = p_assignment_id
AND paf.assignment_id = paa.assignment_id
AND paa.action_status = 'C'
AND ppa.action_status = 'C'
AND ppa.action_type IN ('R','Q')
AND ppa.effective_date BETWEEN p_start_date
AND p_end_date
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND ppa.business_group_id = p_business_group_id;
SELECT ppa.date_earned
FROM pay_assignment_actions paa
, pay_payroll_actions ppa
WHERE paa.assignment_action_id = p_assg_act_id
AND paa.payroll_action_id = ppa.payroll_action_id;
SELECT COUNT(*)
FROM pay_element_entries_f pee
,pay_element_links_f pel
,pay_element_types_f pet
,per_all_assignments_f paa
WHERE paa.assignment_id = p_assignment_id
AND paa.business_group_id = p_business_group_id
AND p_effective_date BETWEEN paa.effective_start_date
AND paa.effective_end_date
AND pee.assignment_id = paa.assignment_id
AND p_effective_date BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND pee.element_link_id = pel.element_link_id
AND p_effective_date BETWEEN pel.effective_start_date
AND pel.effective_end_date
AND pel.element_type_id = pet.element_type_id
AND pet.element_name = p_element_name
AND p_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date;
SELECT target.ENTRY_INFORMATION1
FROM per_all_assignments_f assign
,pay_element_entries_f target
,pay_element_links_f link
,pay_element_types_f type
WHERE assign.assignment_id = p_assignment_id
AND target.assignment_id = assign.assignment_id
AND target.entry_information_category = 'CN_PHF AND SI INFORMATION'
AND target.element_link_id = link.element_link_id
AND link.element_type_id = type.element_type_id
AND type.element_name = p_element_name
AND p_date_earned BETWEEN assign.effective_start_date
AND assign.effective_end_date
AND p_date_earned BETWEEN target.effective_start_date
AND target.effective_end_date
AND p_date_earned BETWEEN link.effective_start_date
AND link.effective_end_date
AND p_date_earned BETWEEN type.effective_start_date
AND type.effective_end_date;
SELECT max(paa.assignment_action_id)
FROM pay_assignment_actions paa
, pay_payroll_actions ppa
, per_all_assignments_f paf
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND paf.assignment_id = p_assignment_id
AND paf.assignment_id = paa.assignment_id
AND paa.action_status = 'C'
AND ppa.action_status = 'C'
AND ppa.action_type IN ('R','Q')
AND ppa.effective_date BETWEEN p_start_date
AND p_end_date
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND ppa.business_group_id = p_business_group_id;