The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT grade_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
SELECT pg.value
FROM pay_rates pr
,pay_grade_rules_f pg
WHERE pr.rate_type= 'G'
AND pr.rate_id = p_rate_id
AND pr.rate_id = pg.rate_id
AND pg.grade_or_spinal_point_id = l_grade_id
AND pg.rate_type = 'G'
AND pg.business_group_id = p_business_group_id
AND pr.business_group_id = p_business_group_id
AND p_date_earned BETWEEN pg.effective_start_date AND pg.effective_end_date;
SELECT grade_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
SELECT 'Y'
FROM pay_rates pr
WHERE pr.rate_type = 'G'
AND pr.name =p_rate_table
AND pr.business_group_id = p_business_group_id;
SELECT pg.value
FROM pay_rates pr
,pay_grade_rules_f pg
WHERE pr.rate_type= 'G'
AND pr.name = p_rate_table
AND pr.rate_id = pg.rate_id
AND pg.grade_or_spinal_point_id = l_grade_id
AND pg.rate_type = 'G'
AND pg.business_group_id = p_business_group_id
AND pr.business_group_id = p_business_group_id
AND p_date_earned BETWEEN pg.effective_start_date AND pg.effective_end_date;
SELECT paa.date_start start_date
,paa.date_end end_date
FROM per_absence_attendances paa
,per_absence_attendance_types paat
,per_all_assignments_f asg
WHERE paat.absence_category ='UL'
AND paat.business_group_id = paa.business_group_id
AND paat.business_group_id = p_business_group_id
AND paat.absence_attendance_type_id = paa.absence_attendance_type_id
AND paa.person_id = asg.person_id
AND asg.assignment_id = p_assignment_id
AND (paa.date_start between p_start_date AND p_end_date
AND paa.date_end between p_start_date AND p_end_date)
UNION
SELECT paa.date_start start_date
,p_end_date end_date
FROM per_absence_attendances paa
,per_absence_attendance_types paat
,per_all_assignments_f asg
WHERE paat.absence_category ='UL'
AND paat.business_group_id = paa.business_group_id
AND paat.business_group_id = p_business_group_id
AND paat.absence_attendance_type_id = paa.absence_attendance_type_id
AND paa.person_id = asg.person_id
AND asg.assignment_id = p_assignment_id
AND (paa.date_start between p_start_date AND p_end_date
AND paa.date_end > p_end_date)
UNION
SELECT p_start_date start_date
,paa.date_end end_date
FROM per_absence_attendances paa
,per_absence_attendance_types paat
,per_all_assignments_f asg
WHERE paat.absence_category ='UL'
AND paat.business_group_id = paa.business_group_id
AND paat.business_group_id = p_business_group_id
AND paat.absence_attendance_type_id = paa.absence_attendance_type_id
AND paa.person_id = asg.person_id
AND asg.assignment_id = p_assignment_id
AND (paa.date_start < p_start_date
AND paa.date_end between p_start_date AND p_end_date)
UNION
SELECT p_start_date start_date
,p_end_date end_date
FROM per_absence_attendances paa
,per_absence_attendance_types paat
,per_all_assignments_f asg
WHERE paat.absence_category ='UL'
AND paat.business_group_id = paa.business_group_id
AND paat.business_group_id = p_business_group_id
AND paat.absence_attendance_type_id = paa.absence_attendance_type_id
AND paa.person_id = asg.person_id
AND asg.assignment_id = p_assignment_id
AND (paa.date_start < p_start_date
AND paa.date_end > p_end_date);
SELECT marital_status
FROM per_all_people_f ppl
,per_all_assignments_f asg
WHERE asg.assignment_id = p_assignment_id
AND ppl.person_id = asg.person_id
AND p_date_earned BETWEEN asg.effective_start_date AND asg.effective_end_date
AND p_date_earned BETWEEN ppl.effective_start_date AND ppl.effective_end_date;
SELECT COUNT(DISTINCT contact_person_id)
FROM per_contact_relationships pcr
,per_all_assignments_f asg
WHERE asg.person_id = pcr.person_id
AND asg.assignment_id = p_assignment_id
AND pcr.contact_type = 'C'
AND nvl(pcr.dependent_flag, 'N') = 'Y'
AND p_date_earned BETWEEN NVL(pcr.date_start,p_date_earned) AND NVL(pcr.date_end, TO_DATE('4712/12/31','YYYY/MM/DD'));
SELECT base_name
,business_group_id
,template_name
FROM pay_element_templates
WHERE template_id = p_template_id
AND template_type = 'U';
SELECT element_type_id
FROM pay_element_types_f
WHERE business_group_id = cp_business_group_id
AND element_name = cp_element_name;
SELECT input_value_id
,effective_start_date
FROM pay_input_values_f
WHERE element_type_id = cp_element_type_id
AND name = cp_name;
SELECT flex_value_set_id
FROM fnd_flex_value_sets
WHERE flex_value_set_name = 'HR_AE_RATE_NAME';
SELECT pet.element_type_id
FROM pay_element_types_f pet
,pay_sub_classification_rules_f psc
,pay_element_classifications pec
WHERE pet.business_group_id = cp_business_group_id
AND pet.element_name = cp_element_name
AND pet.element_type_id = psc.element_type_id
AND pet.business_group_id = psc.business_group_id
AND pec.classification_name = 'Subject to Social Insurance : Earnings'
AND pec.legislation_code = 'AE'
AND psc.classification_id = pec.classification_id;
SELECT element_type_id
,effective_start_date
,effective_end_date
FROM pay_element_types_f
WHERE business_group_id = cp_business_group_id
AND element_name = cp_element_name;
SELECT classification_id
FROM pay_element_classifications pec
WHERE classification_name = 'Subject to Social Insurance : Information'
AND legislation_code = 'AE';
SELECT object_version_number
FROM pay_input_values_f
WHERE input_value_id = l_input_value_id;
pay_input_value_api.update_input_value
(p_validate => FALSE
,p_effective_date => l_effective_date
,p_datetrack_mode => 'CORRECTION'
,p_input_value_id => l_input_value_id
,p_object_version_number => l_ov_number
,p_value_set_id => l_value_set_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_default_val_warning => l_default_warning
,p_min_max_warning => l_min_max_warning
,p_link_inp_val_warning => l_link_inp_val_warning
,p_pay_basis_warning => l_pay_basis_warning
,p_formula_warning => l_formula_warning
,p_assignment_id_warning => l_assignment_id_warning
,p_formula_message => l_formula_message
);
SELECT pay_sub_classification_rules_s.nextval
INTO l_seq
FROM dual;
pay_sub_class_rules_pkg.insert_row
( p_rowid => l_row_id
,p_sub_classification_rule_Id => l_seq
,p_effective_start_date => l_el_effective_start_date
,p_effective_end_date => l_el_effective_end_date
,p_element_type_id => l_info_element_type_id
,p_classification_id => l_classification_id
,p_business_group_id => l_business_group_id
,p_legislation_code => NULL
,p_last_update_date => SYSDATE
,p_last_updated_by => -1
,p_last_update_login => -1
,p_created_by => -1
,p_creation_date => SYSDATE);
SELECT object_version_number
FROM pay_input_values_f
WHERE input_value_id = l_input_value_id;
pay_input_value_api.update_input_value
(p_validate => FALSE
,p_effective_date => l_effective_date
,p_datetrack_mode => 'CORRECTION'
,p_input_value_id => l_input_value_id
,p_object_version_number => l_ov_number
-- ,p_max_value => '0'
,p_min_value => '0'
,p_warning_or_error => 'E'
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_default_val_warning => l_default_warning
,p_min_max_warning => l_min_max_warning
,p_link_inp_val_warning => l_link_inp_val_warning
,p_pay_basis_warning => l_pay_basis_warning
,p_formula_warning => l_formula_warning
,p_assignment_id_warning => l_assignment_id_warning
,p_formula_message => l_formula_message
);
SELECT object_version_number
FROM pay_input_values_f
WHERE input_value_id = l_input_value_id;
pay_input_value_api.update_input_value
(p_validate => FALSE
,p_effective_date => l_effective_date
,p_datetrack_mode => 'CORRECTION'
,p_input_value_id => l_input_value_id
,p_object_version_number => l_ov_number
,p_max_value => '0'
,p_warning_or_error => 'E'
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_default_val_warning => l_default_warning
,p_min_max_warning => l_min_max_warning
,p_link_inp_val_warning => l_link_inp_val_warning
,p_pay_basis_warning => l_pay_basis_warning
,p_formula_warning => l_formula_warning
,p_assignment_id_warning => l_assignment_id_warning
,p_formula_message => l_formula_message
);
SELECT template_id
FROM pay_element_templates
WHERE template_name = 'Flat Amount Template'
AND template_type = 'T';
DELETE FROM pay_ele_tmplt_class_usages
WHERE template_id = l_template_id;
pay_element_template_api.delete_user_structure
(p_validate => false
,p_drop_formula_packages => true
,p_template_id => l_template_id);
SELECT classification_id
FROM pay_element_classifications
WHERE legislation_code = 'AE'
AND classification_name = 'Earnings';
INSERT INTO pay_ele_tmplt_class_usages
(ele_template_classification_id
,classification_id
,template_id
,display_process_mode
,display_arrearage)
VALUES
(PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
,l_classification_id
,l_template_id
,NULL
,NULL);
SELECT template_id
FROM pay_element_templates
WHERE template_name = 'Percentage of Basic Salary Template'
AND template_type = 'T';
DELETE FROM pay_ele_tmplt_class_usages
WHERE template_id = l_template_id;
pay_element_template_api.delete_user_structure
(p_validate => false
,p_drop_formula_packages => true
,p_template_id => l_template_id);
SELECT classification_id
FROM pay_element_classifications
WHERE legislation_code = 'AE'
AND classification_name = 'Earnings';
INSERT INTO pay_ele_tmplt_class_usages
(ele_template_classification_id
,classification_id
,template_id
,display_process_mode
,display_arrearage)
VALUES
(PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
,l_classification_id
,l_template_id
,NULL
,NULL);
SELECT template_id
FROM pay_element_templates
WHERE template_name = 'Grade Allowance Template'
AND template_type = 'T';
DELETE FROM pay_ele_tmplt_class_usages
WHERE template_id = l_template_id;
pay_element_template_api.delete_user_structure
(p_validate => false
,p_drop_formula_packages => true
,p_template_id => l_template_id);
SELECT classification_id
FROM pay_element_classifications
WHERE legislation_code = 'AE'
AND classification_name = 'Earnings';
INSERT INTO pay_ele_tmplt_class_usages
(ele_template_classification_id
,classification_id
,template_id
,display_process_mode
,display_arrearage)
VALUES
(PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
,l_classification_id
,l_template_id
,NULL
,NULL);
SELECT template_id
FROM pay_element_templates
WHERE template_name = 'Housing Allowance Template'
AND template_type = 'T';
DELETE FROM pay_ele_tmplt_class_usages
WHERE template_id = l_template_id;
pay_element_template_api.delete_user_structure
(p_validate => false
,p_drop_formula_packages => true
,p_template_id => l_template_id);
SELECT classification_id
FROM pay_element_classifications
WHERE legislation_code = 'AE'
AND classification_name = 'Earnings';
INSERT INTO pay_ele_tmplt_class_usages
(ele_template_classification_id
,classification_id
,template_id
,display_process_mode
,display_arrearage)
VALUES
(PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
,l_classification_id
,l_template_id
,NULL
,NULL);
SELECT template_id
FROM pay_element_templates
WHERE template_name = 'Transport Allowance Template'
AND template_type = 'T';
DELETE FROM pay_ele_tmplt_class_usages
WHERE template_id = l_template_id;
pay_element_template_api.delete_user_structure
(p_validate => false
,p_drop_formula_packages => true
,p_template_id => l_template_id);
SELECT classification_id
FROM pay_element_classifications
WHERE legislation_code = 'AE'
AND classification_name = 'Earnings';
INSERT INTO pay_ele_tmplt_class_usages
(ele_template_classification_id
,classification_id
,template_id
,display_process_mode
,display_arrearage)
VALUES
(PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
,l_classification_id
,l_template_id
,NULL
,NULL);
SELECT template_id
FROM pay_element_templates
WHERE template_name = 'Cost of Living Allowance Template'
AND template_type = 'T';
DELETE FROM pay_ele_tmplt_class_usages
WHERE template_id = l_template_id;
pay_element_template_api.delete_user_structure
(p_validate => false
,p_drop_formula_packages => true
,p_template_id => l_template_id);
SELECT classification_id
FROM pay_element_classifications
WHERE legislation_code = 'AE'
AND classification_name = 'Earnings';
INSERT INTO pay_ele_tmplt_class_usages
(ele_template_classification_id
,classification_id
,template_id
,display_process_mode
,display_arrearage)
VALUES
(PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
,l_classification_id
,l_template_id
,NULL
,NULL);
SELECT template_id
FROM pay_element_templates
WHERE template_name = 'Children Social Allowance Template'
AND template_type = 'T';
DELETE FROM pay_ele_tmplt_class_usages
WHERE template_id = l_template_id;
pay_element_template_api.delete_user_structure
(p_validate => false
,p_drop_formula_packages => true
,p_template_id => l_template_id);
SELECT classification_id
FROM pay_element_classifications
WHERE legislation_code = 'AE'
AND classification_name = 'Earnings';
INSERT INTO pay_ele_tmplt_class_usages
(ele_template_classification_id
,classification_id
,template_id
,display_process_mode
,display_arrearage)
VALUES
(PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
,l_classification_id
,l_template_id
,NULL
,NULL);
SELECT template_id
FROM pay_element_templates
WHERE template_name = 'Social Allowance Template'
AND template_type = 'T';
DELETE FROM pay_ele_tmplt_class_usages
WHERE template_id = l_template_id;
pay_element_template_api.delete_user_structure
(p_validate => false
,p_drop_formula_packages => true
,p_template_id => l_template_id);
SELECT classification_id
FROM pay_element_classifications
WHERE legislation_code = 'AE'
AND classification_name = 'Earnings';
INSERT INTO pay_ele_tmplt_class_usages
(ele_template_classification_id
,classification_id
,template_id
,display_process_mode
,display_arrearage)
VALUES
(PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
,l_classification_id
,l_template_id
,NULL
,NULL);
SELECT template_id
FROM pay_element_templates
WHERE template_name = 'Shift Allowance Template'
AND template_type = 'T';
DELETE FROM pay_ele_tmplt_class_usages
WHERE template_id = l_template_id;
pay_element_template_api.delete_user_structure
(p_validate => false
,p_drop_formula_packages => true
,p_template_id => l_template_id);
SELECT classification_id
FROM pay_element_classifications
WHERE legislation_code = 'AE'
AND classification_name = 'Earnings';
INSERT INTO pay_ele_tmplt_class_usages
(ele_template_classification_id
,classification_id
,template_id
,display_process_mode
,display_arrearage)
VALUES
(PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
,l_classification_id
,l_template_id
,NULL
,NULL);
SELECT template_id
FROM pay_element_templates
WHERE template_name = 'Hourly Salary Template'
AND template_type = 'T';
DELETE FROM pay_ele_tmplt_class_usages
WHERE template_id = l_template_id;
pay_element_template_api.delete_user_structure
(p_validate => false
,p_drop_formula_packages => true
,p_template_id => l_template_id);
SELECT classification_id
FROM pay_element_classifications
WHERE legislation_code = 'AE'
AND classification_name = 'Earnings';
INSERT INTO pay_ele_tmplt_class_usages
(ele_template_classification_id
,classification_id
,template_id
,display_process_mode
,display_arrearage)
VALUES
(PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
,l_classification_id
,l_template_id
,NULL
,NULL);
SELECT template_id
FROM pay_element_templates
WHERE template_name = 'Overtime Allowance Template'
AND template_type = 'T';
DELETE FROM pay_ele_tmplt_class_usages
WHERE template_id = l_template_id;
pay_element_template_api.delete_user_structure
(p_validate => false
,p_drop_formula_packages => true
,p_template_id => l_template_id);
SELECT classification_id
FROM pay_element_classifications
WHERE legislation_code = 'AE'
AND classification_name = 'Earnings';
INSERT INTO pay_ele_tmplt_class_usages
(ele_template_classification_id
,classification_id
,template_id
,display_process_mode
,display_arrearage)
VALUES
(PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
,l_classification_id
,l_template_id
,NULL
,NULL);
SELECT template_id
FROM pay_element_templates
WHERE template_name = 'Unpaid Leave Template'
AND template_type = 'T';
DELETE FROM pay_ele_tmplt_class_usages
WHERE template_id = l_template_id;
pay_element_template_api.delete_user_structure
(p_validate => false
,p_drop_formula_packages => true
,p_template_id => l_template_id);
SELECT classification_id
FROM pay_element_classifications
WHERE legislation_code = 'AE'
AND classification_name = 'Absence';
INSERT INTO pay_ele_tmplt_class_usages
(ele_template_classification_id
,classification_id
,template_id
,display_process_mode
,display_arrearage)
VALUES
(PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
,l_classification_id
,l_template_id
,NULL
,NULL);
SELECT enabled_flag
FROM fnd_currencies
WHERE currency_code = 'AED';
UPDATE fnd_currencies
SET enabled_flag = 'Y'
WHERE currency_code = 'AED'
AND enabled_flag <> 'Y';
UPDATE fnd_currencies
SET enabled_flag = l_enabled_flag
WHERE currency_code = 'AED';