[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT eev.screen_entry_value payment_key
FROM pay_element_types_f pet
,pay_input_values_f piv
,pay_element_entry_values_f eev
,pay_element_entries_f pee
WHERE pee.assignment_id = passignment_id
AND pee.element_type_id = pet.element_type_id
AND pet.element_name = c_element_name
AND pet.legislation_code = 'ES'
AND peffective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND piv.name = c_input_value_name
AND piv.legislation_code = 'ES'
AND peffective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND piv.element_type_id = pee.element_type_id
AND eev.input_value_id + 0 = piv.input_value_id
AND peffective_date BETWEEN eev.effective_start_date
AND eev.effective_end_date
AND pee.element_entry_id = eev.element_entry_id
AND peffective_date BETWEEN pee.effective_start_date
AND pee.effective_end_date ;
SELECT pap.date_of_birth date_of_birth
,pcr.contact_person_id contact_person_id
,pcr.contact_type
,NVL(pcr.cont_information1,'N') fiscal_dependent
,NVL(pcr.cont_information2,'N') single_parent
,pcr.date_start date_start
FROM per_contact_relationships pcr
,per_all_people_f pap
,per_All_assignments_f paaf
WHERE paaf.assignment_id = passignment_id
AND pcr.person_id = paaf.person_id
AND pap.person_id = pcr.contact_person_id
AND pcr.rltd_per_rsds_w_dsgntr_flag = 'Y'
AND pcr.cont_information_category = 'ES'
AND ((pcr.contact_type in ('C','JP_GC','NEPHEW','NIECE','A') AND NVL(pap.marital_status,'S') <> 'M')
OR (pcr.contact_type in ('P','GP','UNCLE','AUNT','BROTHER','SISTER')))
AND ((pcr.cont_information1 = 'Y'
AND pcr.contact_type in ('C','JP_GC','NEPHEW','NIECE','A','P','GP','UNCLE','AUNT'))
OR(pcr.cont_information1 = 'N'
AND pcr.contact_type in ('BROTHER','SISTER')))
AND peffective_date BETWEEN pap.effective_start_date
AND pap.effective_end_date
AND peffective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND peffective_date BETWEEN nvl(pcr.date_start,START_OF_TIME)
AND nvl(pcr.date_end,END_OF_TIME);
SELECT pap.marital_status
,pap.person_id
,paaf.assignment_number
FROM per_all_people_f pap
,per_all_assignments_f paaf
WHERE paaf.assignment_id = passignment_id
AND pap.person_id = paaf.person_id
AND peffective_date BETWEEN pap.effective_start_date
AND pap.effective_end_date
AND peffective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date;
SELECT cont_information1
FROM per_contact_relationships pcr
WHERE pcr.person_id = pperson_id
AND pcr.contact_type = 'S' --Spouse
AND pcr.cont_information_category = 'ES'
AND peffective_date BETWEEN nvl(pcr.date_start,START_OF_TIME)
AND nvl(pcr.date_end,END_OF_TIME);
SELECT person_id
FROM per_all_assignments_f paaf
WHERE paaf.assignment_id = passignment_id
AND peffective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date;
SELECT pdf.dis_information1
,pdf.degree
FROM per_disabilities_f pdf
WHERE pdf.person_id = pperson_id
AND pdf.dis_information_category = 'ES'
AND peffective_date BETWEEN pdf.effective_start_date
AND pdf.effective_end_date;
SELECT to_number(pur.ROW_LOW_RANGE_OR_NAME) Low_value
,to_number(pur.ROW_HIGH_RANGE) High_value
FROM PAY_USER_ROWS_F pur
,PAY_USER_TABLES put
WHERE put.USER_TABLE_NAME = 'ES_WORK_RELATED_EARNINGS_DEDUCTION'
AND put.USER_TABLE_ID = pur.USER_TABLE_ID
AND c_value between to_number(pur.ROW_LOW_RANGE_OR_NAME) AND to_number(pur.ROW_HIGH_RANGE)
AND c_effective_date between pur.effective_start_date AND pur.effective_end_date;
SELECT SUBSTR(legislative_parameters,
INSTR(legislative_parameters,p_token_name)+(LENGTH(p_token_name)+1),
INSTR(legislative_parameters,' ',
INSTR(legislative_parameters,p_token_name)))
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT pa.Region_2
FROM per_addresses pa
,per_All_assignments_f paaf
WHERE paaf.assignment_id = passignment_id
AND paaf.person_id = pa.person_id
AND pa.style IN('ES','ES_GLB')
AND pa.primary_flag = 'Y'
AND pa.Region_2 IN (51,52)
AND peffective_date BETWEEN pa.date_from
AND NVL(pa.date_to,END_OF_TIME)
AND peffective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date;
SELECT pa.Region_2
FROM hr_locations pa
,per_All_assignments_f paaf
WHERE paaf.assignment_id = passignment_id
AND paaf.location_id = pa.location_id
AND pa.style IN ('ES','ES_GLB')
AND peffective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND pa.Region_2 IN (51,52);
SELECT ppa.effective_date
,prt.shortname
FROM pay_payroll_actions ppa
,pay_run_types_f prt
WHERE ppa.payroll_action_id = p_payroll_action_id
AND ppa.run_type_id = prt.run_type_id
AND ppa.effective_date BETWEEN prt.effective_start_date
AND prt.effective_end_date;
SELECT 'Y'
FROM DUAL
WHERE EXISTS(SELECT 1
FROM pay_element_entries_f pee
,pay_element_types_f pet
WHERE pee.assignment_id = c_assignment_id
AND pee.element_type_id = pet.element_type_id
AND pet.element_name = 'Tax Withholding Rate'
AND pet.legislation_code= 'ES'
AND c_effective_date BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND c_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date);
SELECT ptp.period_num
FROM per_time_periods ptp
WHERE ptp.payroll_id = c_payroll_id
AND c_effective_date BETWEEN ptp.start_date
AND ptp.end_date;
SELECT MIN(effective_start_date)
FROM per_all_assignments_f
WHERE assignment_id = c_assignment_id
AND payroll_id = c_payroll_id;
SELECT max(period_num)
FROM pay_payrolls_f ppf
,per_time_periods ptp
WHERE ppf.payroll_id = c_payroll_id
AND ppf.period_type = ptp.period_type
AND ppf.payroll_id = ptp.payroll_id
AND c_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT 'N'
FROM dual
WHERE EXISTS(SELECT NULL
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_run_results prr
,pay_run_result_values prv
,pay_element_types_f petf
,pay_input_values_f pivf
WHERE paa.assignment_id = c_assignment_id
AND paa.ACTION_STATUS IN ('C' ,'U')
AND ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_status IN ('C' ,'U')
AND to_char(ppa.effective_date,'YYYY') = to_char(peffective_date,'YYYY')
AND petf.legislation_code = 'ES'
AND ((petf.element_name = 'Tax Withholding Rate'
AND pivf.name = ('Rate'))
OR (petf.element_name = 'Tax'
AND pivf.name = ('Tax Withholding Rate')))
AND petf.element_type_id = pivf.element_type_id
AND pivf.legislation_code = 'ES'
AND prr.assignment_action_id = paa.assignment_action_id
AND paa.source_action_id IS NOT NULL
AND prr.element_type_id = petf.element_type_id
AND prv.run_result_id = prr.run_result_id
AND prv.input_value_id = pivf.input_value_id
AND prv.result_value IS NOT NULL
AND peffective_date BETWEEN petf.effective_start_date
AND petf.effective_end_date
AND peffective_date BETWEEN pivf.effective_start_date
AND pivf.effective_end_date);
SELECT NVL(prrv.result_value,0) Result_Value
FROM pay_run_results prr
,pay_run_result_values prrv
,pay_element_types_f pet
,pay_input_values_f piv
where pet.element_name = c_element_name
AND pet.legislation_code = 'ES'
AND piv.element_type_id = pet.element_type_id
AND piv.name = c_input_value_name
AND pet.element_type_id = prr.element_type_id
AND prr.assignment_action_id= c_assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id
AND c_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND c_effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date;
SELECT paa.assignment_action_id
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_run_results prr
,pay_run_result_values prv
,pay_element_types_f petf
,pay_input_values_f pivf
WHERE paa.assignment_id = c_assignment_id
AND paa.action_status IN ('C' ,'U')
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ('Q' ,'R')
AND ppa.effective_date < c_effective_date
AND petf.legislation_code = 'ES'
AND ((petf.element_name = 'TWR Employee Information' AND pivf.name = 'Payment Key')
OR (petf.element_name = 'Tax' AND pivf.name = 'Tax Withholding Rate'))
AND petf.element_type_id = pivf.element_type_id
AND pivf.legislation_code = 'ES'
AND prr.assignment_action_id = paa.assignment_action_id
AND paa.source_action_id IS NOT NULL
AND prr.element_type_id = petf.element_type_id
AND prv.run_result_id = prr.run_result_id
AND prv.input_value_id = pivf.input_value_id
AND prv.result_value IS NOT NULL
AND c_effective_date BETWEEN petf.effective_start_date
AND petf.effective_end_date
AND c_effective_date BETWEEN pivf.effective_start_date
AND pivf.effective_end_date
ORDER BY ppa.effective_date DESC;
SELECT pap.full_name
FROM per_all_people_f pap
,per_All_assignments_f paaf
,pay_assignment_actions paa
WHERE paa.payroll_action_id = c_payroll_action_id
AND paa.assignment_id = paaf.assignment_id
AND paaf.person_id = pap.person_id
AND paa.source_action_id IS NULL
AND c_effective_date BETWEEN pap.effective_start_date
AND pap.effective_end_date
AND c_effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date;
SELECT file_data INTO p_pdf_blob
FROM fnd_lobs
WHERE file_id = (SELECT MAX(file_id) FROM per_gb_xdo_templates
WHERE file_name like '%PAY_TWR_e_ES.pdf%');
SELECT paa.assignment_id
,paa.assignment_action_id
,ppa.effective_date
,paa.action_status
,prtf.shortname
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
,per_all_assignments_f paaf
,pay_run_types_f prtf
WHERE ppa.payroll_action_id = p_payroll_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.run_type_id = prtf.run_type_id
AND paaf.assignment_id = paa.assignment_id
AND ((paa.source_action_id IS NULL and paa.action_status = 'E')
or (paa.source_action_id IS NOT NULL
AND exists (select 1
FROM pay_run_results r
,pay_element_types_f pet
WHERE paa.assignment_action_id = r.assignment_action_id
AND pet.element_type_id = r.element_type_id
AND pet.legislation_code = 'ES'
AND pet.element_name in ('Tax Withholding Rate','Tax'))))
AND paaf.person_id = nvl(p_person_id,paaf.person_id)
AND ppa.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND ppa.effective_date BETWEEN prtf.effective_start_date AND prtf.effective_end_date;
SELECT 'N'
FROM PAY_RUN_RESULTs prr
,pay_element_types_f pet
WHERE prr.assignment_action_id = c_assignment_action_id
AND prr.element_type_id = pet.element_type_id
AND pet.element_name = 'TWR Employee Information'
AND c_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date;
SELECT pap.full_name name
,pap.person_id
,paaf.assignment_number
,hr_general.decode_lookup('MAR_STATUS',pap.marital_status) marital_status
,floor(months_between(c_effective_date,pap.date_of_birth)/12) Age
FROM per_all_people_f pap
,per_all_assignments_f paaf
WHERE pap.person_id = paaf.person_id
AND paaf.assignment_id = c_assignment_id
AND c_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date
AND c_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date;
SELECT prr.assignment_action_id
,prr.run_result_id
,min(decode(piv.name, 'Assignment Number', prrv.RESULT_VALUE , null)) Assignment_Number
,min(decode(piv.name, 'Name', prrv.RESULT_VALUE , null)) Name
,min(decode(piv.name, 'Age', prrv.RESULT_VALUE , null)) Age
,min(decode(piv.name, 'Payment Key',prrv.RESULT_VALUE||' - '||hr_general.decode_lookup('ES_PAYMENT_KEY',prrv.RESULT_VALUE), null)) Payment_Key
,min(decode(piv.name, 'Length of Contract', prrv.RESULT_VALUE , null)) Length_Of_Contract
,min(decode(piv.name, 'Change in Residency', hr_general.decode_lookup('YES_NO',prrv.RESULT_VALUE), null)) Change_in_Residency
,min(decode(piv.name, 'Contract Type', prrv.RESULT_VALUE, null)) Contract_Type
,min(decode(piv.name, 'Degree of Disability', prrv.RESULT_VALUE , null)) Emp_DOD
,min(decode(piv.name, 'Disabled', hr_general.decode_lookup('YES_NO',prrv.RESULT_VALUE), null)) Disabled
,min(decode(piv.name, 'Marital Status', hr_general.decode_lookup('MAR_STATUS',prrv.RESULT_VALUE), null)) Marital_Status
,min(decode(piv.name, 'Work Status', substr(prrv.RESULT_VALUE||' - '||hr_general.decode_lookup('ES_WORKER_STATUS',prrv.RESULT_VALUE),1,10) , null)) Work_Status
,min(decode(piv.name, 'Location Benefit', hr_general.decode_lookup('YES_NO',prrv.RESULT_VALUE), null)) Resident_Ceuta_Melila
,min(decode(piv.name, 'Contractual Earnings', prrv.RESULT_VALUE , null)) Calc_Cont_Earnings
,min(decode(piv.name, 'America Cup', hr_general.decode_lookup('YES_NO',prrv.RESULT_VALUE), null)) America_Cup_Flag
FROM pay_run_results prr
,pay_run_result_values prrv
,pay_element_types_f pet
,pay_input_values_f piv
WHERE pet.element_name = 'TWR Employee Information'
AND pet.legislation_code = 'ES'
AND piv.element_type_id =pet.element_type_id
AND pet.element_type_id = prr.element_type_id
AND prr.assignment_action_id= c_assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id
AND c_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND c_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
group BY prr.assignment_action_id , prr.run_result_id;
SELECT prr.assignment_action_id
,prr.run_result_id
,min(decode(piv.name, 'Number of Ascendants', prrv.RESULT_VALUE, null)) No_of_Asc
,min(decode(piv.name, 'Ascendants Greater than 75', prrv.RESULT_VALUE, null)) No_of_Asc_Gr_75
,min(decode(piv.name, 'Disability between 33 and 64', prrv.RESULT_VALUE, null)) No_Asc_disablity_bet_33_65
,min(decode(piv.name, 'Disability greater than 64', prrv.RESULT_VALUE, null)) No_Asc_disablity_gr_65
,min(decode(piv.name, 'Single Descendant', prrv.RESULT_VALUE, null)) No_Asc_Single_Descendent
,min(decode(piv.name, 'Reduced Mobility', prrv.RESULT_VALUE, null)) No_Asc_Reduced_Mobility
,min(decode(piv.name, 'Disability Amount', prrv.RESULT_VALUE, null)) Asc_Disability_Amt
,min(decode(piv.name, 'Special Assistance', prrv.RESULT_VALUE, null)) Asc_Special_Assistance
,min(decode(piv.name, 'Special Allowance', prrv.RESULT_VALUE, null)) Asc_Special_Allowance
,min(decode(piv.name, 'Age Deduction', prrv.RESULT_VALUE, null)) Asc_Age_Deduction
FROM pay_run_results prr
,pay_run_result_values prrv
,pay_element_types_f pet
,pay_input_values_f piv
WHERE pet.element_name = 'TWR Employee Ascendants Information'
AND pet.legislation_code = 'ES'
AND piv.element_type_id =pet.element_type_id
AND pet.element_type_id = prr.element_type_id
AND prr.assignment_action_id= c_assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id
AND c_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND c_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
group BY prr.assignment_action_id , prr.run_result_id;
SELECT prr.assignment_action_id
,prr.run_result_id
,min(decode(piv.name, 'Number of Descendants', prrv.RESULT_VALUE, null)) No_of_Desc
,min(decode(piv.name, 'Age less than 3', prrv.RESULT_VALUE, null)) No_of_Desc_less_3
,min(decode(piv.name, 'Age between 3 and 25', prrv.RESULT_VALUE, null)) No_Desc_bet_3_25
,min(decode(piv.name, 'Disability between 33 and 64', prrv.RESULT_VALUE, null)) No_Desc_disablity_bet_33_65
,min(decode(piv.name, 'Disability greater than 64', prrv.RESULT_VALUE, null)) No_Desc_disablity_gr_65
,min(decode(piv.name, 'Reduced Mobility', prrv.RESULT_VALUE, null)) No_Desc_Reduced_Mobility
,min(decode(piv.name, 'Single Parent', prrv.RESULT_VALUE, null)) No_Desc_Single_Parent
,min(decode(piv.name, 'Adopted less than 3 years ago', prrv.RESULT_VALUE, null)) No_Desc_Adopted_less_3
,min(decode(piv.name, 'Special Assistance', prrv.RESULT_VALUE, null)) Desc_Disability_Amt
,min(decode(piv.name, 'Disability Amount', prrv.RESULT_VALUE, null)) Desc_Special_Assistance
FROM pay_run_results prr
,pay_run_result_values prrv
,pay_element_types_f pet
,pay_input_values_f piv
WHERE pet.element_name = 'TWR Employee Descendants Information'
AND pet.legislation_code = 'ES'
AND piv.element_type_id =pet.element_type_id
AND pet.element_type_id = prr.element_type_id
AND prr.assignment_action_id= c_assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id
AND c_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND c_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
group BY prr.assignment_action_id , prr.run_result_id;
SELECT prr.assignment_action_id
,prr.run_result_id
,min(decode(piv.name, 'Employee Special Assistance', prrv.RESULT_VALUE, null)) Emp_Special_Assistance
,min(decode(piv.name, 'Employee Disability Assistance', prrv.RESULT_VALUE, null)) Emp_Disability_Assistance
,min(decode(piv.name, 'Employee Special Allowance', prrv.RESULT_VALUE, null)) Employee_Special_Allowance
,min(decode(piv.name, 'Employee Age Deduction', prrv.RESULT_VALUE, null)) Emp_Age_Deduction
,min(decode(piv.name, 'Child Support', prrv.RESULT_VALUE, null)) Child_Support
,min(decode(piv.name, 'Deductible Expenses', prrv.RESULT_VALUE, null)) Deductible_Expences
,min(decode(piv.name, 'Irregular Earnings', prrv.RESULT_VALUE, null)) Irregular_Earnings
,min(decode(piv.name, 'Spouse Alimony', prrv.RESULT_VALUE, null)) Spouse_Alimony
,min(decode(piv.name, 'Tax Base', prrv.RESULT_VALUE, null)) Tax_Base
,min(decode(piv.name, 'Override Tax Rate', prrv.RESULT_VALUE, null)) Override_TWR
,min(decode(piv.name, 'Override Contractual Earnings', prrv.RESULT_VALUE, null)) Override_Cont_Earnings
FROM pay_run_results prr
,pay_run_result_values prrv
,pay_element_types_f pet
,pay_input_values_f piv
WHERE pet.element_name = 'TWR Deduction Information'
AND pet.legislation_code = 'ES'
AND piv.element_type_id =pet.element_type_id
AND pet.element_type_id = prr.element_type_id
AND prr.assignment_action_id= c_assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id
AND c_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND c_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
group BY prr.assignment_action_id , prr.run_result_id;
SELECT prr.assignment_action_id
,prr.run_result_id
,min(decode(piv.name, 'Rate', prrv.RESULT_VALUE, null)) Rate
FROM pay_run_results prr
,pay_run_result_values prrv
,pay_element_types_f pet
,pay_input_values_f piv
WHERE pet.element_name = 'Tax Withholding Rate'
AND pet.legislation_code = 'ES'
AND piv.element_type_id =pet.element_type_id
AND pet.element_type_id = prr.element_type_id
AND prr.assignment_action_id= c_assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id
AND c_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND c_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
GROUP BY prr.assignment_action_id , prr.run_result_id;
SELECT prr.assignment_action_id
,prr.run_result_id
,min(decode(piv.name, 'Tax Withholding Rate', prrv.RESULT_VALUE, null)) Rate
FROM pay_run_results prr
,pay_run_result_values prrv
,pay_element_types_f pet
,pay_input_values_f piv
WHERE pet.element_name = 'Tax'
AND pet.legislation_code = 'ES'
AND piv.element_type_id =pet.element_type_id
AND pet.element_type_id = prr.element_type_id
AND prr.assignment_action_id= c_assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND piv.input_value_id = prrv.input_value_id
AND c_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND c_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
GROUP BY prr.assignment_action_id , prr.run_result_id;
SELECT line_text Msg
FROM pay_message_lines
WHERE source_id = c_assignment_action_id
ORDER BY line_sequence DESC;
vXMLTable.DELETE;
SELECT userenv('LANGUAGE') INTO g_nls_db_char FROM dual;
SELECT scl.segment2
,paaf.person_id
FROM per_all_assignments_f paaf
,hr_soft_coding_keyflex scl
WHERE paaf.assignment_id = c_assignment_id
AND paaf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND c_effective_date between paaf.effective_start_date and paaf.effective_end_date;
SELECT hoi2.org_information1 work_center
FROM hr_organization_information hoi1
,hr_organization_information hoi2
WHERE hoi1.organization_id = hoi2.organization_id
AND hoi1.org_information1 = c_work_center
AND hoi1.org_information_context = 'ES_WORK_CENTER_REF'
AND hoi2.org_information_context = 'ES_WORK_CENTER_REF';
SELECT paaf.assignment_id
FROM per_all_assignments_f paaf
,hr_soft_coding_keyflex scl
WHERE paaf.person_id = c_person_id
AND paaf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment2 = c_work_center
AND c_effective_date between paaf.effective_start_date and paaf.effective_end_date;
SELECT to_number(pur.row_low_range_or_name) Low_val
,to_number(pur.ROW_HIGH_RANGE) high_val
FROM pay_user_rows_f pur
,pay_user_tables put
WHERE put.legislation_code = 'ES'
AND pur.user_table_id = put.user_table_id
AND put.user_table_name like 'ES_WITHHOLDING_QUOTAS'
AND c_efective_date BETWEEN pur.effective_start_date AND pur.effective_end_date
ORDER BY 1;
SELECT NVL(fnd_date.canonical_to_date(CTR_INFORMATION4),to_date('31-12-4712','dd-mm-yyyy')) Contract_End_Date
FROM PER_CONTRACTS_f pcf
,per_all_assignments_f paaf
WHERE paaf.assignment_id = c_assignment_id
AND paaf.contract_id = pcf.contract_id
AND pcf.ctr_information_category = 'ES'
AND sysdate BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND sysdate BETWEEN pcf.effective_start_date
AND pcf.effective_end_date;
SELECT scl.segment2
,paaf.person_id
FROM per_all_assignments_f paaf
,hr_soft_coding_keyflex scl
WHERE paaf.assignment_id = c_assignment_id
AND paaf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND c_effective_date between paaf.effective_start_date and paaf.effective_end_date;
SELECT hoi2.org_information1 work_center
FROM hr_organization_information hoi1
,hr_organization_information hoi2
WHERE hoi1.organization_id = hoi2.organization_id
AND hoi1.org_information1 = c_work_center
AND hoi1.org_information_context = 'ES_WORK_CENTER_REF'
AND hoi2.org_information_context = 'ES_WORK_CENTER_REF';
SELECT paaf.assignment_id
,paaf.payroll_id
,pet.element_name element_name
,pee.element_entry_id
,pee.effective_start_date rec_start_date
,pee.effective_end_date rec_end_date
,min(decode(piv.name, 'Amount', peev.screen_entry_value , null)) Amount
,min(decode(piv.name, 'Period Type', peev.screen_entry_value , null)) Period_type
,min(decode(piv.name, 'Start Date', peev.screen_entry_value , null)) Start_date
,min(decode(piv.name, 'End Date', peev.screen_entry_value , null)) End_date
FROM per_all_assignments_f paaf
,hr_soft_coding_keyflex scl
,pay_element_entries_f pee
,pay_element_entry_values_f peev
,pay_element_types_f pet
,pay_input_values_f piv
WHERE paaf.person_id = c_person_id
AND paaf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment2 = c_work_center
AND pee.assignment_id = paaf.assignment_id
AND pee.element_entry_id = peev.element_entry_id
AND pet.legislation_code = 'ES'
AND pet.element_name in ('Child Support','Spouse Alimony')
AND pet.element_type_id = pee.element_type_id
AND piv.element_type_id = pet.element_type_id
AND piv.input_value_id = peev.input_value_id
AND c_effective_date between paaf.effective_start_date and paaf.effective_end_date
AND c_effective_date between pee.effective_start_date and pee.effective_end_date
AND c_effective_date between peev.effective_start_date and peev.effective_end_date
AND c_effective_date between piv.effective_start_date and piv.effective_end_date
AND c_effective_date between pet.effective_start_date and pet.effective_end_date
group by paaf.assignment_id
,paaf.payroll_id
,pet.element_name
,pee.element_entry_id
,pee.effective_start_date
,pee.effective_end_date;