[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT e.assignment_id AS Assignment_Id
,min(decode(i.name,'Unemployment Status',v.screen_entry_value,NULL)) AS Unemployment_Status_Code
,min(decode(i.name,'Contract Indicators',substr(v.screen_entry_value,2,1),NULL)) AS Rehired_Disabled_Code
,min(decode(i.name,'Contract Indicators',substr(v.screen_entry_value,3,1),NULL)) AS First_Contractor_Code
,min(decode(i.name,'Contract Indicators',substr(v.screen_entry_value,5,1),NULL)) AS Under_Represented_Women_Code
,min(decode(i.name,'Contract Indicators',substr(v.screen_entry_value,6,1),NULL)) AS After_Childbirth_Code
,min(decode(i.name,'Contract Indicators',substr(v.screen_entry_value,1,1),NULL)) AS Active_Rent_Flag
,min(decode(i.name,'Contract Indicators',substr(v.screen_entry_value,4,1),NULL)) AS Minority_Group_Flag
FROM pay_element_entries_f e
,pay_input_values_f i
,pay_element_entry_values_f v
,pay_element_types_f t
,pay_element_links_f l
WHERE e.element_entry_id = v.element_entry_id
AND v.input_value_id = i.input_value_id
AND i.legislation_code = 'ES'
AND i.element_type_id = t.element_type_id
AND t.element_type_id = l.element_type_id
AND l.element_link_id = e.element_link_id
AND t.element_name = 'Social Security Details'
AND t.legislation_code = 'ES'
AND e.assignment_id = p_assignment_id
AND p_reporting_date BETWEEN e.effective_start_date AND e.effective_end_date
AND p_reporting_date BETWEEN v.effective_start_date AND v.effective_end_date
AND p_reporting_date BETWEEN i.effective_start_date AND i.effective_end_date
AND p_reporting_date BETWEEN t.effective_start_date AND t.effective_end_date
AND p_reporting_date BETWEEN l.effective_start_date AND l.effective_end_date
GROUP BY e.assignment_id;
SELECT pdf.degree
FROM per_all_people_f pap
,per_disabilities_f pdf
,per_all_assignments_f paa
WHERE pap.person_id = pdf.person_id
AND pap.person_id = paa.person_id
AND paa.assignment_id = p_assignment_id
AND p_reporting_date BETWEEN pdf.effective_start_date
AND pdf.effective_end_date
AND p_reporting_date BETWEEN pap.effective_start_date
AND pap.effective_end_date
AND p_reporting_date BETWEEN paa.effective_start_date
AND paa.effective_end_date;
SELECT system_type_cd
FROM per_shared_types
WHERE lookup_type = 'ES_NATIONALITY'
AND NVL(business_group_id,p_business_group_id)
= p_business_group_id
AND information1 = p_lookup_code;
SELECT iso_numeric_code
FROM fnd_territories
WHERE territory_code = p_lookup_code;
SELECT effective_date
,PER_ES_SS_REP_ARCHIVE_PKG.get_parameter(legislative_parameters, 'TEST_FLAG')
,PER_ES_SS_REP_ARCHIVE_PKG.get_parameter(legislative_parameters, 'SES_DATE')
,PER_ES_SS_REP_ARCHIVE_PKG.get_parameter(legislative_parameters, 'ORG_ID')
,PER_ES_SS_REP_ARCHIVE_PKG.get_parameter(legislative_parameters, 'ASG_SET_ID')
,business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT per_es_ss_rep_archive_pkg.get_parameter(legislative_parameters
,'PAYROLL_ACTION_ID')
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT effective_date
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT /* Getting ETI and ETF Information */
/* Segment Header, Msg Syntax ID, Syntax version, Process Syntax ID and Version are defaulted */
hoi.org_information12 Authorization_key
,hoi.org_information11 Silicon_key
/* Session Date and time is taken as a parameter */
/* File extension and Proc. priority code are defaulted */
/* Test Flag is taken from parameter */
/* Segment Header is defaulted, New Password and Reserved flag are left blank */
,hoi.org_information13 Current_password
/* Getting EMP Information */
/* Segment Header defaulted*/
,'0111' SS_Scheme
,substr(hoi.org_information8,1,2) SS_Province
,substr(hoi.org_information8,-9) SS_Number
,'9' ID_Type --code for cif
,hloc.country country
,hoi.org_information5 Employer_ID
/* Open, Main CAC SS Scheme, Province, SS Number and Reserved are left blank */
,' ' Action_Event
/* Segment Header and Cmp Reg Flag are defaulted */
,hoi.org_information4 Employer_Type
,hoi.org_information1 Registered_Name
,hoi.organization_id Legal_emp_org_id
/* Reserved, Seg. Hdr, Start and End date are defaulted */
FROM hr_all_organization_units hou
,hr_organization_information hoi
,hr_locations_all hloc
WHERE hou.business_group_id = c_business_group_id
AND hoi.organization_id = hou.organization_id
AND hoi.org_information_context = 'ES_STATUTORY_INFO'
AND hloc.location_id (+) = hou.location_id
AND hoi.organization_id = nvl(c_organization_id,hoi.organization_id)
AND EXISTS (SELECT asg_run.assignment_id
FROM per_assignment_extra_info asg_extra
,per_all_assignments_f asg_run
WHERE asg_extra.aei_information_category = 'ES_SS_REP'
AND asg_extra.INFORMATION_TYPE = 'ES_SS_REP'
AND asg_extra.aei_information5 = 'Y'
AND asg_run.assignment_id = asg_extra.assignment_id
AND asg_run.business_group_id = g_business_group_id
AND fnd_date.canonical_to_date(asg_extra.aei_information7) <= c_effective_end_date);
sqlstr := 'SELECT 1 FROM dual WHERE to_char(:payroll_action_id) = dummy';
sqlstr := 'SELECT distinct person_id
FROM per_people_f ppf
,pay_payroll_actions ppa
WHERE ppa.payroll_action_id = :payroll_action_id
AND ppa.business_group_id = ppf.business_group_id
ORDER BY ppf.person_id';
sqlstr := 'SELECT 1 FROM dual WHERE to_char(:payroll_action_id) = dummy';
SELECT /* Get the TRA Information */
/* Segment Header is defaulted, Province and SS Number are left blank */
-- '12' province
--'0' SS_NUMBER
DECODE (pap.national_identifier, NULL, DECODE(pap.per_information2, 'DNI', 1, 'Passport', 2, 6), 1) ID_Type
,pap.nationality country_of_birth
,DECODE (pap.national_identifier, NULL, pap.per_information3, pap.national_identifier) ID_Number
/* Reserved flags are left blank */
,pap.nationality Nationality
/* Employee flag and reserved are left blank and Segment Header is defaulted */
,RPAD(pap.last_name ,20,' ') first_last_name
,RPAD(pap.per_information1,20,' ') second_last_name
,RPAD(pap.first_name,15,' ') name
/* Reserved flag is left blank */
,paa.assignment_id assignment_id
FROM per_all_people_f pap
,(SELECT DISTINCT asg_run.assignment_id assignment_id, asg_run.person_id person_id
FROM per_assignment_extra_info asg_extra
,per_all_assignments_f asg_run
WHERE asg_extra.aei_information_category = 'ES_SS_REP'
AND asg_extra.aei_information5 = 'Y'
AND asg_run.business_group_id = g_business_group_id
AND asg_run.assignment_id = asg_extra.assignment_id
AND fnd_date.canonical_to_date(asg_extra.aei_information7) <= c_effective_end_date
AND asg_run.person_id BETWEEN stperson
AND endperson) paa
WHERE pap.person_id = paa.person_id
AND pap.business_group_id = g_business_group_id
AND c_effective_end_date BETWEEN pap.effective_start_date
AND pap.effective_end_date
AND pap.per_information_category = 'ES';
SELECT paf.assignment_id assignment_id,
leg.organization_id legal_employer
FROM per_all_assignments_f paf
,hr_soft_coding_keyflex sck
,hr_organization_information wcr
,hr_organization_information leg
WHERE paf.effective_start_date = (SELECT max (paf1.effective_start_date)
FROM per_all_assignments_f paf1
WHERE paf.assignment_id = paf1.assignment_id
AND paf1.effective_start_date <= p_reporting_date)
AND sck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
AND sck.segment2 = wcr.org_information1
AND wcr.org_information_context = 'ES_WORK_CENTER_REF'
AND wcr.organization_id = leg.organization_id
AND leg.org_information_context = 'CLASS'
AND leg.org_information1 = 'HR_LEGAL_EMPLOYER'
AND leg.organization_id = NVL(p_legal_employer,leg.organization_id)
AND paf.assignment_id = p_assignment_id
AND ((paf.payroll_id IS NULL AND p_payroll_id IS NULL)OR
paf.payroll_id = nvl(p_payroll_id,paf.payroll_id));
SELECT pcf.effective_start_date
,pcf.ctr_information6 replaced_person_id
,pcf.ctr_information7 replacement_reason_code
FROM per_contracts_f pcf
,per_all_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND paf.person_id = pcf.person_id
AND p_reporting_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND p_reporting_date BETWEEN pcf.effective_start_date AND pcf.effective_end_date;
SELECT nvl(pev.screen_entry_value, 'X') screen_entry_value
FROM pay_element_entry_values_f pev
,pay_input_values_f piv
,pay_element_types_f pet
,pay_element_entries_f pee
,pay_element_links_f pel
,per_all_assignments_f paf
WHERE paf.person_id = c_person_id
AND pee.assignment_id = paf.assignment_id
AND pev.element_entry_id = pee.element_entry_id
AND piv.input_value_id = pev.input_value_id
AND piv.name = 'Social Security Identifier'
AND piv.legislation_code = 'ES'
AND pet.element_type_id = piv.element_type_id
AND pet.element_name = 'Social Security Details'
AND pet.legislation_code = 'ES'
AND pel.element_type_id = pet.element_type_id
AND pee.element_link_id = pel.element_link_id
AND paf.business_group_id = pel.business_group_id
AND c_reporting_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND c_reporting_date BETWEEN pev.effective_start_date AND pev.effective_end_date
AND c_reporting_date BETWEEN pee.effective_start_date AND pee.effective_end_date
AND c_reporting_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND c_reporting_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND c_reporting_date BETWEEN pel.effective_start_date AND pel.effective_end_date;
SELECT nvl(pev.screen_entry_value, 'X') screen_entry_value
FROM pay_element_entry_values_f pev
,pay_input_values_f piv
,pay_element_types_f pet
,pay_element_entries_f pee
,pay_element_links_f pel
WHERE pee.assignment_id = c_assignment_id
AND pev.element_entry_id = pee.element_entry_id
AND piv.input_value_id = pev.input_value_id
AND piv.name = 'Social Security Identifier'
AND piv.legislation_code = 'ES'
AND pet.element_type_id = piv.element_type_id
AND pet.element_name = 'Social Security Details'
AND pet.legislation_code = 'ES'
AND pel.element_type_id = pet.element_type_id
AND pee.element_link_id = pel.element_link_id
AND c_reporting_date BETWEEN pev.effective_start_date AND pev.effective_end_date
AND c_reporting_date BETWEEN pee.effective_start_date AND pee.effective_end_date
AND c_reporting_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND c_reporting_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND c_reporting_date BETWEEN pel.effective_start_date AND pel.effective_end_date;
SELECT e.assignment_id AS Assignment_Id
,min(decode(i.name,'Special Relationship Type',v.screen_entry_value,NULL)) AS Active_Rent_Flag
,min(decode(i.name,'Retirement Age Reduction',v.screen_entry_value,NULL)) AS Minority_Group_Flag
FROM pay_element_entries_f e
,pay_input_values_f i
,pay_element_entry_values_f v
,pay_element_types_f t
,pay_element_links_f l
WHERE e.element_entry_id = v.element_entry_id
AND v.input_value_id = i.input_value_id
AND i.element_type_id = t.element_type_id
AND i.legislation_code = 'ES'
AND t.element_type_id = l.element_type_id
AND l.element_link_id = e.element_link_id
AND t.element_name = 'Multiple Employment Details'
AND t.legislation_code = 'ES'
AND e.assignment_id = p_assignment_id
AND p_reporting_date BETWEEN e.effective_start_date AND e.effective_end_date
AND p_reporting_date BETWEEN v.effective_start_date AND v.effective_end_date
AND p_reporting_date BETWEEN i.effective_start_date AND i.effective_end_date
AND p_reporting_date BETWEEN t.effective_start_date AND t.effective_end_date
AND p_reporting_date BETWEEN l.effective_start_date AND l.effective_end_date
GROUP BY e.assignment_id;
SELECT count(1)
FROM pay_action_information
WHERE action_information_category = 'ES_SS_REPORT_TRA'
AND action_context_type = 'AAP'
AND action_context_id = c_actid
AND assignment_id = c_assignment_id;
SELECT paf.assignment_number asg_no
FROM per_all_assignments_f paf
WHERE paf.assignment_id = c_assignment_id
ORDER BY paf.effective_start_date DESC;
SELECT has.payroll_id
FROM hr_assignment_sets has
WHERE has.assignment_set_id = c_assignment_set_id
AND has.business_group_id = c_business_group_id;
SELECT include_or_exclude
FROM hr_assignment_set_amendments hasa
WHERE hasa.assignment_set_id = c_assignment_set_id
AND hasa.assignment_id = c_assignment_id;
SELECT region_2
FROM per_addresses pas
,per_all_people_f pap
,per_all_assignments_f paa
WHERE paa.person_id = pap.person_id
AND pas.person_id = pap.person_id
AND paa.assignment_id = c_assignment_id
AND pas.business_group_id = c_business_group_id
AND pas.primary_flag = 'Y'
AND c_reporting_date BETWEEN pap.effective_start_date
AND pap.effective_end_date
AND c_reporting_date BETWEEN paa.effective_start_date
AND paa.effective_end_date;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
SELECT aei_information2 effective_report_date,
aei_information3 event,
nvl(aei_information4, 'X') value,
aei_information6 action_type,
aei_information7 first_changed_date
FROM per_assignment_extra_info
WHERE assignment_id = c_assignment_id
AND aei_information5 = 'Y'
AND fnd_date.canonical_to_date(aei_information7) <= c_effective_end_date
ORDER BY aei_information3;
SELECT pap.date_of_birth
,pap.sex
,paa.assignment_status_type_id
,nvl(paa.employment_category, 'X') employment_category
,paa.soft_coding_keyflex_id
,paa.employee_category employee_category
,paa.collective_agreement_id
FROM per_all_assignments_f paa
,per_all_people_f pap
WHERE paa.assignment_id = c_assignment_id
AND paa.person_id = pap.person_id
AND paa.effective_start_date = fnd_date.canonical_to_date(c_effective_start_date)
AND paa.effective_start_date BETWEEN pap.effective_start_date
AND pap.effective_end_date;
SELECT nvl(sck.segment5,'X') contribution_group
FROM hr_soft_coding_keyflex sck
WHERE sck.soft_coding_keyflex_id = c_soft_coding_keyflex_id;
SELECT pee.assignment_id AS assignment_Id
,min(decode(piv.name,'SS Epigraph Code',nvl(pev.screen_entry_value, 'X'),NULL)) AS epigraph_code
,min(decode(piv.name,'Contract Key',nvl(pev.screen_entry_value, 'X'),NULL)) AS Contract_Key
FROM pay_element_entry_values_f pev
,pay_input_values_f piv
,pay_element_types_f pet
,pay_element_entries_f pee
,pay_element_links_f pel
WHERE pev.element_entry_id = pee.element_entry_id
AND pee.assignment_id = c_assignment_id
AND pev.input_value_id = piv.input_value_id
AND piv.element_type_id = pet.element_type_id
AND piv.legislation_code = 'ES'
AND pet.element_type_id = pel.element_type_id
AND pel.element_link_id = pee.element_link_id
AND pet.element_name = 'Social Security Details'
AND pet.legislation_code = 'ES'
AND pev.effective_start_date = fnd_date.canonical_to_date(c_effective_start_date)
AND pev.effective_start_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND pev.effective_start_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND pev.effective_start_date BETWEEN pee.effective_start_date
AND pee.effective_end_date
GROUP BY pee.assignment_id;
SELECT per_system_status
FROM per_assignment_status_types
WHERE assignment_status_type_id = c_assignment_status_type_id;
sql_str := 'select paa.assignment_status_type_id asg_value
,paa.effective_start_date actual_date
,pap.date_of_birth date_of_birth
,pap.sex sex
from per_all_assignments_f paa
,per_all_people_f pap
,per_assignment_status_types pas
where paa.assignment_id = '||p_assignment_id||'
and paa.person_id = pap.person_id
and paa.assignment_status_type_id = pas.assignment_status_type_id
and pas.per_system_status = ''ACTIVE_ASSIGN''
and fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(p_effective_end_date) ||''')
between pap.effective_start_date
and pap.effective_end_date
and paa.effective_start_date
between fnd_date.canonical_to_date('''||l_first_changed_date(l_unused_number)||''')
and fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(p_effective_end_date) ||''')
order by paa.effective_start_date';
sql_str := 'select distinct nvl(pev.screen_entry_value, ''X'') screen_entry_value
,pev.effective_start_date actual_date
,pap.date_of_birth date_of_birth
,pap.sex sex
from pay_element_entry_values_f pev
,pay_input_values_f piv
,pay_element_types_f pet
,pay_element_entries_f pee
,per_all_assignments_f paa
,per_all_people_f pap
where pev.element_entry_id = pee.element_entry_id
and paa.person_id = pap.person_id
and paa.assignment_id = pee.assignment_id
and pee.assignment_id = '||p_assignment_id||'
and pev.input_value_id = piv.input_value_id
and piv.element_type_id = pet.element_type_id
and pet.element_name = ''Social Security Details''
and pet.legislation_code = ''ES''
and piv.name = ''Contract Key''
AND piv.legislation_code = ''ES''
and pee.element_type_id = pet.element_type_id
and pev.effective_start_date
between fnd_date.canonical_to_date('''||l_first_changed_date(l_unused_number)||''')
and fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(p_effective_end_date) ||''')
and fnd_date.canonical_to_date('''||l_first_changed_date(l_unused_number)||''')
between pap.effective_start_date
and pap.effective_end_date
and fnd_date.canonical_to_date('''||l_first_changed_date(l_unused_number)||''')
between paa.effective_start_date
and paa.effective_end_date
AND pev.effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND pev.effective_start_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND pev.effective_start_date BETWEEN pee.effective_start_date AND pee.effective_end_date
order by pev.effective_start_date';
sql_str := 'select nvl(sck.segment5, ''X'') asg_value
,paa.effective_start_date actual_date
,pap.date_of_birth date_of_birth
,pap.sex sex
from per_all_assignments_f paa
,per_all_people_f pap
,hr_soft_coding_keyflex sck
where paa.assignment_id = '||p_assignment_id||'
and paa.person_id = pap.person_id
and paa.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
and fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(p_effective_end_date) ||''')
between pap.effective_start_date
and pap.effective_end_date
and paa.effective_start_date
between fnd_date.canonical_to_date('''||l_first_changed_date(l_unused_number)||''')
and fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(p_effective_end_date) ||''')
order by paa.effective_start_date';
sql_str := 'select paa.assignment_status_type_id asg_value
,pps.actual_termination_date actual_date
,pap.date_of_birth date_of_birth
,pap.sex sex
from per_all_assignments_f paa
,per_all_people_f pap
,per_periods_of_service pps
where paa.assignment_id = '||p_assignment_id||'
and paa.person_id = pap.person_id
and pps.person_id = pap.person_id
and paa.period_of_service_id = pps.period_of_service_id
and pps.actual_termination_date is not null
and fnd_date.canonical_to_date('''||l_first_changed_date(l_unused_number)||''')
between paa.effective_start_date
and paa.effective_end_date
and fnd_date.canonical_to_date('''||l_first_changed_date(l_unused_number)||''')
between pap.effective_start_date
and pap.effective_end_date
order by paa.effective_start_date';
sql_str := 'select distinct nvl(pev.screen_entry_value, ''X'') screen_entry_value
,pev.effective_start_date actual_date
,pap.date_of_birth date_of_birth
,pap.sex sex
from pay_element_entry_values_f pev
,pay_input_values_f piv
,pay_element_types_f pet
,pay_element_entries_f pee
,per_all_assignments_f paa
,per_all_people_f pap
where pev.element_entry_id = pee.element_entry_id
and paa.person_id = pap.person_id
and paa.assignment_id = pee.assignment_id
and pee.assignment_id = '||p_assignment_id||'
and pev.input_value_id = piv.input_value_id
and piv.element_type_id = pet.element_type_id
and pet.element_name = ''Social Security Details''
and pet.legislation_code = ''ES''
and piv.name = ''SS Epigraph Code''
and pee.element_type_id = pet.element_type_id
AND piv.legislation_code = ''ES''
and pev.effective_start_date
between fnd_date.canonical_to_date('''||l_first_changed_date(l_unused_number)||''')
and fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(p_effective_end_date) ||''')
and fnd_date.canonical_to_date('''||l_first_changed_date(l_unused_number)||''')
between pap.effective_start_date
and pap.effective_end_date
and fnd_date.canonical_to_date('''||l_first_changed_date(l_unused_number)||''')
between paa.effective_start_date
and paa.effective_end_date
AND pev.effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND pev.effective_start_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND pev.effective_start_date BETWEEN pee.effective_start_date AND pee.effective_end_date
order by pev.effective_start_date';
SELECT paa.employee_category employee_category
,paa.collective_agreement_id
FROM per_all_assignments_f paa
WHERE paa.assignment_id = c_assignment_id
AND c_reporting_date BETWEEN paa.effective_start_date
AND paa.effective_end_date;
SELECT leaving_reason
FROM per_periods_of_service pps
,per_all_assignments_f paa
WHERE paa.period_of_service_id = pps.period_of_service_id
AND paa.assignment_id = c_assignment_id
AND pps.business_group_id = c_business_group_id
AND c_actual_termination_dt BETWEEN paa.effective_start_date
AND paa.effective_end_date;
SELECT information1
FROM per_shared_types
WHERE lookup_type ='LEAV_REAS'
AND system_type_cd = c_leaving_reason
AND business_group_id = c_business_group_id;
SELECT information1
FROM per_shared_types
WHERE lookup_type ='LEAV_REAS'
AND system_type_cd = c_leaving_reason
AND business_group_id IS NULL;
sqlstr := 'select distinct person_id
from per_people_f ppf
,pay_payroll_actions ppa
where ppa.payroll_action_id = :payroll_action_id
and ppa.business_group_id = ppf.business_group_id
order by ppf.person_id';
sqlstr := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
SELECT asg_run.assignment_id assignment_id
,pai.action_context_id action_context_id
,pai.action_information1 action_status
,max(pai.effective_date) current_reporting_date
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
,per_all_assignments_f asg_run
,pay_action_information pai
WHERE ppa.payroll_action_id = c_payroll_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND asg_run.business_group_id = ppa.business_group_id
AND asg_run.assignment_id = paa.assignment_id
AND asg_run.person_id BETWEEN stperson
AND endperson
AND pai.action_context_id = paa.assignment_action_id
AND pai.action_context_type = 'AAP'
AND pai.action_information_category = 'ES_SS_REPORT_FAB'
GROUP BY asg_run.assignment_id, pai.action_context_id, pai.action_information1
ORDER BY asg_run.assignment_id, current_reporting_date;
SELECT pai.action_information4 assignment_status_type_id
,pai.action_information5 employment_category
,pai.action_information6 epigraph_code
,pai.action_information18 contribution_group
FROM pay_action_information pai
WHERE pai.action_context_id = c_action_context_id
AND pai.action_information1 = c_action_status
AND effective_date = c_current_reporting_date;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
SELECT count(assignment_status_type_id), min(effective_start_date)
INTO l_unused_number, l_effective_start_date
FROM per_all_assignments_f
WHERE assignment_status_type_id <> l_assignment_status_type_id
AND effective_start_date >= qualifying_assignments.current_reporting_date
AND assignment_id = qualifying_assignments.assignment_id;
UPDATE per_assignment_extra_info
SET aei_information4 = l_assignment_status_type_id
,aei_information6 = 'U'
,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
,aei_information7 = fnd_date.date_to_canonical(g_effective_end_date)
,aei_information5 = 'N'
WHERE assignment_id = qualifying_assignments.assignment_id
AND aei_information3 = 'AS';
UPDATE per_assignment_extra_info
SET aei_information4 = l_assignment_status_type_id
,aei_information6 = 'U'
,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
,aei_information7 = fnd_date.date_to_canonical(l_effective_start_date)
WHERE assignment_id = qualifying_assignments.assignment_id
AND aei_information3 = 'AS';
/* SELECT count(employment_category), min(effective_start_date)
INTO l_unused_number, l_effective_start_date
FROM per_all_assignments_f
WHERE nvl(employment_category, 'X') <> nvl(l_employment_category, 'X')
AND effective_start_date >= qualifying_assignments.current_reporting_date
AND assignment_id = qualifying_assignments.assignment_id;*/
SELECT count(pev.screen_entry_value), min(pev.effective_start_date)
INTO l_unused_number , l_effective_start_date
FROM pay_element_entry_values_f pev
,pay_input_values_f piv
,pay_element_types_f pet
,pay_element_entries_f pee
WHERE pev.element_entry_id = pee.element_entry_id
AND pev.screen_entry_value <> l_employment_category
AND pee.assignment_id = qualifying_assignments.assignment_id
AND pev.input_value_id = piv.input_value_id
AND piv.element_type_id = pet.element_type_id
AND pee.element_type_id = pet.element_type_id
AND pet.element_name = 'Social Security Details'
AND pet.legislation_code = 'ES'
AND piv.name = 'Contract Key'
AND piv.legislation_code = 'ES'
AND pev.effective_start_date >= qualifying_assignments.current_reporting_date
AND pev.effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND pev.effective_start_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND pev.effective_start_date BETWEEN pee.effective_start_date AND pee.effective_end_date;
UPDATE per_assignment_extra_info
SET aei_information4 = l_employment_category
,aei_information6 = 'U'
,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
,aei_information7 = fnd_date.date_to_canonical(g_effective_end_date)
,aei_information5 = 'N'
WHERE assignment_id = qualifying_assignments.assignment_id
AND aei_information3 = 'EC';
UPDATE per_assignment_extra_info
SET aei_information4 = l_employment_category
,aei_information6 = 'U'
,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
,aei_information7 = fnd_date.date_to_canonical(l_effective_start_date)
WHERE assignment_id = qualifying_assignments.assignment_id
AND aei_information3 = 'EC';
SELECT count(sck.segment5), min(paa.effective_start_date)
INTO l_unused_number, l_effective_start_date
FROM per_all_assignments_f paa
,hr_soft_coding_keyflex sck
WHERE nvl(sck.segment5,'X') <> nvl(l_contribution_group,'X')
AND paa.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
AND paa.effective_start_date >= qualifying_assignments.current_reporting_date
AND paa.assignment_id = qualifying_assignments.assignment_id;
UPDATE per_assignment_extra_info
SET aei_information4 = l_contribution_group
,aei_information6 = 'U'
,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
,aei_information7 = fnd_date.date_to_canonical(g_effective_end_date)
,aei_information5 = 'N'
WHERE assignment_id = qualifying_assignments.assignment_id
AND aei_information3 = 'CG';
UPDATE per_assignment_extra_info
SET aei_information4 = l_contribution_group
,aei_information6 = 'U'
,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
,aei_information7 = fnd_date.date_to_canonical(l_effective_start_date)
WHERE assignment_id = qualifying_assignments.assignment_id
AND aei_information3 = 'CG';
SELECT count(pev.screen_entry_value), min(pev.effective_start_date)
INTO l_unused_number , l_effective_start_date
FROM pay_element_entry_values_f pev
,pay_input_values_f piv
,pay_element_types_f pet
,pay_element_entries_f pee
WHERE pev.element_entry_id = pee.element_entry_id
AND pev.screen_entry_value <> l_epigraph_code
AND pee.assignment_id = qualifying_assignments.assignment_id
AND pev.input_value_id = piv.input_value_id
AND piv.element_type_id = pet.element_type_id
AND pee.element_type_id = pet.element_type_id
AND pet.element_name = 'Social Security Details'
AND pet.legislation_code = 'ES'
AND piv.name = 'SS Epigraph Code'
AND piv.legislation_code = 'ES'
AND pev.effective_start_date >= qualifying_assignments.current_reporting_date
AND pev.effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND pev.effective_start_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND pev.effective_start_date BETWEEN pee.effective_start_date AND pee.effective_end_date;
UPDATE per_assignment_extra_info
SET aei_information4 = l_epigraph_code
,aei_information6 = 'U'
,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
,aei_information7 = fnd_date.date_to_canonical(g_effective_end_date)
,aei_information5 = 'N'
WHERE assignment_id = qualifying_assignments.assignment_id
AND aei_information3 = 'EP';
UPDATE per_assignment_extra_info
SET aei_information4 = l_epigraph_code
,aei_information6 = 'U'
,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
,aei_information7 = fnd_date.date_to_canonical(l_effective_start_date)
WHERE assignment_id = qualifying_assignments.assignment_id
AND aei_information3 = 'EP';
SELECT count(assignment_status_type_id), min(effective_start_date)
INTO l_unused_number, l_effective_start_date
FROM per_all_assignments_f
WHERE assignment_status_type_id <> l_assignment_status_type_id
AND effective_start_date >= qualifying_assignments.current_reporting_date
AND assignment_id = qualifying_assignments.assignment_id;
UPDATE per_assignment_extra_info
SET aei_information4 = l_assignment_status_type_id
,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
,aei_information7 = fnd_date.date_to_canonical(g_effective_end_date)
,aei_information5 = 'N'
WHERE assignment_id = qualifying_assignments.assignment_id
AND aei_information3 = 'AS';
UPDATE per_assignment_extra_info
SET aei_information4 = l_assignment_status_type_id
,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
,aei_information7 = fnd_date.date_to_canonical(l_effective_start_date)
WHERE assignment_id = qualifying_assignments.assignment_id
AND aei_information3 = 'AS';
updated back to N*/
--
UPDATE per_assignment_extra_info
SET aei_information4 = l_assignment_status_type_id
,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
,aei_information7 = fnd_date.date_to_canonical(g_effective_end_date)
,aei_information5 = 'N'
WHERE assignment_id = qualifying_assignments.assignment_id
AND aei_information3 IN ('TS','AS');
/*SELECT count(employment_category), min(effective_start_date)
INTO l_unused_number, l_effective_start_date
FROM per_all_assignments_f
WHERE nvl(employment_category, 'X') <> nvl(l_employment_category, 'X')
AND effective_start_date >= qualifying_assignments.current_reporting_date
AND assignment_id = qualifying_assignments.assignment_id;*/
SELECT count(pev.screen_entry_value), min(pev.effective_start_date)
INTO l_unused_number , l_effective_start_date
FROM pay_element_entry_values_f pev
,pay_input_values_f piv
,pay_element_types_f pet
,pay_element_entries_f pee
WHERE pev.element_entry_id = pee.element_entry_id
AND pev.screen_entry_value <> l_employment_category
AND pee.assignment_id = qualifying_assignments.assignment_id
AND pev.input_value_id = piv.input_value_id
AND piv.element_type_id = pet.element_type_id
AND pee.element_type_id = pet.element_type_id
AND pet.element_name = 'Social Security Details'
AND pet.legislation_code = 'ES'
AND piv.name = 'Contract Key'
AND piv.legislation_code = 'ES'
AND pev.effective_start_date >= qualifying_assignments.current_reporting_date
AND pev.effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND pev.effective_start_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND pev.effective_start_date BETWEEN pee.effective_start_date AND pee.effective_end_date;
UPDATE per_assignment_extra_info
SET aei_information4 = l_employment_category
,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
,aei_information7 = fnd_date.date_to_canonical(g_effective_end_date)
,aei_information5 = 'N'
WHERE assignment_id = qualifying_assignments.assignment_id
AND aei_information3 = 'EC';
UPDATE per_assignment_extra_info
SET aei_information4 = l_employment_category
,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
,aei_information7 = fnd_date.date_to_canonical(l_effective_start_date)
WHERE assignment_id = qualifying_assignments.assignment_id
AND aei_information3 = 'EC';
SELECT count(sck.segment5), min(paa.effective_start_date)
INTO l_unused_number, l_effective_start_date
FROM per_all_assignments_f paa
,hr_soft_coding_keyflex sck
WHERE nvl(sck.segment5, 'X') <> nvl(l_contribution_group, 'X')
AND paa.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
AND paa.effective_start_date >= qualifying_assignments.current_reporting_date
AND paa.assignment_id = qualifying_assignments.assignment_id;
UPDATE per_assignment_extra_info
SET aei_information4 = l_contribution_group
,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
,aei_information7 = fnd_date.date_to_canonical(g_effective_end_date)
,aei_information5 = 'N'
WHERE assignment_id = qualifying_assignments.assignment_id
AND aei_information3 = 'CG';
UPDATE per_assignment_extra_info
SET aei_information4 = l_contribution_group
,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
,aei_information7 = fnd_date.date_to_canonical(l_effective_start_date)
WHERE assignment_id = qualifying_assignments.assignment_id
AND aei_information3 = 'CG';
SELECT count(pev.screen_entry_value), min(pev.effective_start_date)
INTO l_unused_number , l_effective_start_date
FROM pay_element_entry_values_f pev
,pay_input_values_f piv
,pay_element_types_f pet
,pay_element_entries_f pee
WHERE pev.element_entry_id = pee.element_entry_id
AND pev.screen_entry_value <> l_epigraph_code
AND pee.assignment_id = qualifying_assignments.assignment_id
AND pev.input_value_id = piv.input_value_id
AND piv.element_type_id = pet.element_type_id
AND pee.element_type_id = pet.element_type_id
AND pet.element_name = 'Social Security Details'
AND pet.legislation_code = 'ES'
AND piv.name = 'SS Epigraph Code'
AND piv.legislation_code = 'ES'
AND pev.effective_start_date >= qualifying_assignments.current_reporting_date
AND pev.effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND pev.effective_start_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND pev.effective_start_date BETWEEN pee.effective_start_date AND pee.effective_end_date;
UPDATE per_assignment_extra_info
SET aei_information4 = l_epigraph_code
,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
,aei_information7 = fnd_date.date_to_canonical(g_effective_end_date)
,aei_information5 = 'N'
WHERE assignment_id = qualifying_assignments.assignment_id
AND aei_information3 = 'EP';
UPDATE per_assignment_extra_info
SET aei_information4 = l_epigraph_code
,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
,aei_information7 = fnd_date.date_to_canonical(l_effective_start_date)
WHERE assignment_id = qualifying_assignments.assignment_id
AND aei_information3 = 'EP';