[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT business_group_id
FROM per_business_groups
WHERE UPPER(name) = UPPER(p_business_group_name);
SELECT dated_table_id
FROM pay_dated_tables
WHERE UPPER(table_name) = UPPER(p_table_name);
SELECT default_priority
FROM pay_element_classifications
WHERE NVL(legislation_code, 'GB') = 'GB'
AND UPPER(classification_name) = UPPER(p_primary_class);
SELECT element_type_id
FROM pay_element_types_f
WHERE UPPER(element_name) = UPPER(LTRIM(RTRIM(p_ele_name)));
SELECT input_value_id
FROM pay_input_values_f
WHERE element_type_id = p_element_type_id
AND name = LTRIM(RTRIM(p_input_value_name));
SELECT formula_id
FROM ff_formulas_f
WHERE formula_name = UPPER(p_formula_name);
SELECT COUNT(*) count
FROM pay_element_type_extra_info
WHERE element_type_id = p_ele_id
AND information_type = 'PQP_UK_ELEMENT_ATTRIBUTION'
AND eei_information_category = 'PQP_UK_ELEMENT_ATTRIBUTION';
SELECT element_type_extra_info_id,
object_version_number
FROM pay_element_type_extra_info
WHERE element_type_id = p_ele_id
AND information_type = 'PQP_UK_ELEMENT_ATTRIBUTION'
AND eei_information_category = 'PQP_UK_ELEMENT_ATTRIBUTION';
SELECT event_group_id
FROM pay_event_groups
WHERE UPPER(event_group_name) = UPPER(p_pg_name);
SELECT formula_text
FROM ff_formulas_f ff
WHERE ff.formula_name = p_formula_name
AND ff.legislation_code = 'GB'
AND ff.business_group_id IS NULL;
SELECT formula_id
FROM ff_formulas_f ff
WHERE RTRIM(LTRIM(UPPER(ff.formula_name)))
= RTRIM(LTRIM(UPPER(p_formula_name)))
AND ff.legislation_code IS NULL
AND ff.business_group_id = p_business_group_id;
SELECT 'Y'
INTO l_exists
FROM dual
WHERE EXISTS
(SELECT null
FROM PER_BUSINESS_GROUPS
WHERE name = p_business_group_name
);
l_select_statement VARCHAR2(1000) ;
l_select_flag VARCHAR2(6) ;
SELECT area_id
FROM pay_functional_areas
WHERE short_name = 'INCIDENT REGISTER';
SELECT COUNT(*) count
FROM pay_functional_usages
WHERE area_id = p_area_id
AND business_group_id = p_business_group_id;
l_select_statement := 'SELECT pte.short_name,
pte.event_id
FROM pay_functional_areas pfa,
pay_functional_triggers pft,
pay_trigger_events pte
WHERE pte.event_id = pft.event_id
AND pft.area_id = pfa.area_id
AND pfa.short_name = ''INCIDENT REGISTER''
AND pte.short_name IN (';
l_select_flag := 'FALSE';
l_select_flag := 'TRUE';
l_select_statement := l_select_statement ||
'''PAY_ELEMENT_ENTRIES_F_ARD'',
''PAY_ELEMENT_ENTRIES_F_ARI'',
''PAY_ELEMENT_ENTRIES_F_ARU'',
''PAY_ELEMENT_ENTRY_VALUES_F_ARU''';
IF (l_select_flag = 'TRUE') THEN
l_select_statement := l_select_statement ||
', ''PAY_GRADE_RULES_F_ARU'',
''PER_ALL_ASSIGNMENTS_F_ARU''';
l_select_statement := l_select_statement ||
'''PAY_GRADE_RULES_F_ARU'',
''PER_ALL_ASSIGNMENTS_F_ARU''';
l_select_flag := 'TRUE';
IF (l_select_flag = 'TRUE') THEN
l_select_statement := l_select_statement ||
',''PER_SPINAL_POINT_PLACEMENTS_F_ARU'',
''PER_ALL_ASSIGNMENTS_F_ARU''';
l_select_statement := l_select_statement ||
'''PER_SPINAL_POINT_PLACEMENTS_F_ARU'',
''PER_ALL_ASSIGNMENTS_F_ARU''';
l_select_flag := 'TRUE';
IF (l_select_flag = 'TRUE') THEN
l_select_statement := l_select_statement ||
',''PER_ADDRESSES_ARU''';
l_select_statement := l_select_statement ||
'''PER_ADDRESSES_ARU''';
l_select_flag := 'TRUE';
IF (l_select_flag = 'TRUE') THEN
l_select_statement := l_select_statement ||
',''PER_ALL_ASSIGNMENTS_F_ARU''';
l_select_statement := l_select_statement ||
'''PER_ALL_ASSIGNMENTS_F_ARU''';
l_select_flag := 'TRUE';
IF (l_select_flag = 'FALSE') THEN
l_select_statement := l_select_statement || 'NULL)';
l_select_statement := l_select_statement || ')';
DBMS_SQL.PARSE(l_cursor_num, l_select_statement, DBMS_SQL.V7);
UPDATE pay_trigger_components
SET enabled_flag = 'Y'
WHERE event_id = l_event_id;
UPDATE pay_trigger_events
SET generated_flag = 'Y',
enabled_flag = 'Y'
WHERE event_id = l_event_id;
pay_functional_usages_pkg.insert_row(
p_row_id => l_row_id ,
p_usage_id => l_usage_id ,
p_area_id => l_area_id ,
p_legislation_code => NULL ,
p_business_group_id => l_business_group_id ,
p_payroll_id => NULL );
SELECT COUNT(*)
INTO l_count
FROM pay_event_groups
WHERE event_group_name = UPPER(l_modified_base_pg || ' sal pg');
SELECT COUNT(*)
INTO l_count
FROM pay_datetracked_events
WHERE column_name = 'EFFECTIVE_START_DATE'
AND event_group_id = l_event_group_id
AND dated_table_id = l_dated_table_id ;
,p_update_type => 'U'
,p_column_name => 'EFFECTIVE_START_DATE'
,p_business_group_id => l_business_group_id
,p_legislation_code => NULL
,p_datetracked_event_id => l_dt_event_id
,p_object_version_number => l_ovn
) ;
SELECT COUNT(*)
INTO l_count
FROM pay_datetracked_events
WHERE column_name = 'EFFECTIVE_END_DATE'
AND event_group_id = l_event_group_id
AND dated_table_id = l_dated_table_id ;
,p_update_type => 'U'
,p_column_name => 'EFFECTIVE_END_DATE'
,p_business_group_id => l_business_group_id
,p_legislation_code => NULL
,p_datetracked_event_id => l_dt_event_id
,p_object_version_number => l_ovn );
SELECT COUNT(*)
INTO l_count
FROM pay_element_types_f
WHERE UPPER(element_name) =
UPPER(l_modified_base_ele || ' sal ele');
SELECT COUNT(*)
INTO l_count
FROM pay_element_types_f
WHERE UPPER(element_name) =
UPPER(l_modified_base_ele || ' sal ele1');
SELECT COUNT(*)
INTO l_count
FROM ff_formulas_f
WHERE formula_name = UPPER(l_modified_base_formula || '_sal_ff');
SELECT formula_type_id
INTO l_formula_type_id
FROM ff_formula_types
WHERE formula_type_name = 'Oracle Payroll';
INSERT INTO ff_formulas_f
(formula_id ,
effective_start_date ,
effective_end_date ,
business_group_id ,
legislation_code ,
formula_type_id ,
formula_name ,
description ,
formula_text ,
last_update_date ,
last_updated_by ,
last_update_login ,
created_by ,
creation_date )
VALUES
(ff_formulas_s.NEXTVAL , -- formula_id
TO_DATE(l_date,'dd/mm/yyyy') , -- effective_start_date
TO_DATE('31/12/4712', 'DD/MM/YYYY') , -- effective_end_date
l_business_group_id , -- business_group_id
NULL , -- legislation_code
l_formula_type_id , -- formula_type_id
l_formula_name , -- formula_name
l_description , -- description
l_formula_text , -- formula_text
SYSDATE , -- last_update_date
-1 , -- last_updated_by
-1 , -- last_update_login
-1 , -- created_by
SYSDATE ); -- creation_date
SELECT COUNT(*)
INTO l_count
FROM ff_formulas_f
WHERE formula_name = UPPER(l_modified_base_formula || '_sal_ff1');
SELECT formula_type_id
INTO l_formula_type_id
FROM ff_formula_types
WHERE formula_type_name = 'Oracle Payroll';
INSERT INTO ff_formulas_f
(formula_id ,
effective_start_date ,
effective_end_date ,
business_group_id ,
legislation_code ,
formula_type_id ,
formula_name ,
description ,
formula_text ,
last_update_date ,
last_updated_by ,
last_update_login ,
created_by ,
creation_date )
VALUES
(ff_formulas_s.NEXTVAL , -- formula_id
TO_DATE(l_date,'dd/mm/yyyy') , -- effective_start_date
TO_DATE('31/12/4712', 'DD/MM/YYYY') , -- effective_end_date
l_business_group_id , -- business_group_id
NULL , -- legislation_code
l_formula_type_id , -- formula_type_id
l_formula_name , -- formula_name
l_description , -- description
l_formula_text , -- formula_text
SYSDATE , -- last_update_date
-1 , -- last_updated_by
-1 , -- last_update_login
-1 , -- created_by
SYSDATE ); -- creation_date
SELECT COUNT(*)
INTO l_count
FROM pay_event_groups
WHERE event_group_name = UPPER(l_modified_base_pg || ' GRADE pg');
SELECT COUNT(*)
INTO l_count
FROM pay_datetracked_events
WHERE column_name = 'VALUE'
AND event_group_id = l_event_group_id
AND dated_table_id = l_dated_table_id;
,p_update_type => 'U'
,p_column_name => 'VALUE'
,p_business_group_id => l_business_group_id
,p_legislation_code => NULL
,p_datetracked_event_id => l_dt_event_id
,p_object_version_number => l_ovn
) ;
SELECT COUNT(*)
INTO l_count
FROM pay_datetracked_events
WHERE column_name = 'GRADE_ID'
AND event_group_id = l_event_group_id
AND dated_table_id = l_dated_table_id ;
,p_update_type => 'U'
,p_column_name => 'GRADE_ID'
,p_business_group_id => l_business_group_id
,p_legislation_code => NULL
,p_datetracked_event_id => l_dt_event_id
,p_object_version_number => l_ovn );
SELECT COUNT(*)
INTO l_count
FROM pay_element_types_f
WHERE UPPER(element_name) = UPPER(l_modified_base_ele || ' grade ele');
pay_element_extra_info_api.update_element_extra_info
(p_element_type_extra_info_id => l_etei_id
,p_object_version_number => l_etei_ovn
,p_eei_information_category => 'PQP_UK_ELEMENT_ATTRIBUTION'
,p_eei_information1 => 'H'
-- For Hourly Time Dimension
,p_eei_information2 => 'GR'
-- Spinal Points Pay Source Value
,p_eei_information3 => p_ele_gr_name
,p_eei_information4 => 'N'
-- No FTE
,p_eei_information5 => 'N'
-- 'No' Service History
);
SELECT COUNT(*)
INTO l_count
FROM ff_formulas_f
WHERE formula_name = UPPER(l_modified_base_formula || '_grade_ff');
SELECT formula_type_id
INTO l_formula_type_id
FROM ff_formula_types
WHERE formula_type_name = 'Oracle Payroll';
INSERT INTO ff_formulas_f
(formula_id ,
effective_start_date ,
effective_end_date ,
business_group_id ,
legislation_code ,
formula_type_id ,
formula_name ,
description ,
formula_text ,
last_update_date ,
last_updated_by ,
last_update_login ,
created_by ,
creation_date )
VALUES
(ff_formulas_s.NEXTVAL , -- formula_id
TO_DATE(l_date,'dd/mm/yyyy') , -- effective_start_date
TO_DATE('31/12/4712', 'DD/MM/YYYY') , -- effective_end_date
l_business_group_id , -- business_group_id
NULL , -- legislation_code
l_formula_type_id , -- formula_type_id
l_formula_name , -- formula_name
l_description , -- description
l_formula_text , -- formula_text
SYSDATE , -- last_update_date
-1 , -- last_updated_by
-1 , -- last_update_login
-1 , -- created_by
SYSDATE ); -- creation_date
SELECT COUNT(*)
INTO l_count
FROM pay_event_groups
WHERE event_group_name = UPPER(l_modified_base_pg || ' PAYSCALE pg');
hr_utility.trace('else condition ...selecting from event group');
SELECT COUNT(*)
INTO l_count
FROM pay_datetracked_events
WHERE column_name = 'STEP_ID'
AND event_group_id = l_event_group_id
AND dated_table_id = l_dated_table_id ;
,p_update_type => 'U'
,p_column_name => 'STEP_ID'
,p_business_group_id => l_business_group_id
,p_legislation_code => NULL
,p_datetracked_event_id => l_dt_event_id
,p_object_version_number => l_ovn
) ;
SELECT COUNT(*)
INTO l_count
FROM pay_datetracked_events
WHERE column_name = 'VALUE'
AND event_group_id = l_event_group_id
AND dated_table_id = l_dated_table_id;
,p_update_type => 'U'
,p_column_name => 'VALUE'
,p_business_group_id => l_business_group_id
,p_legislation_code => NULL
,p_datetracked_event_id => l_dt_event_id
,p_object_version_number => l_ovn
) ;
SELECT COUNT(*)
INTO l_count
FROM pay_datetracked_events
WHERE column_name = 'GRADE_ID'
AND event_group_id = l_event_group_id
AND dated_table_id = l_dated_table_id ;
,p_update_type => 'U'
,p_column_name => 'GRADE_ID'
,p_business_group_id => l_business_group_id
,p_legislation_code => NULL
,p_datetracked_event_id => l_dt_event_id
,p_object_version_number => l_ovn );
SELECT COUNT(*)
INTO l_count
FROM pay_element_types_f
WHERE UPPER(element_name) =
UPPER(l_modified_base_ele || ' payscale ele');
pay_element_extra_info_api.update_element_extra_info
(p_element_type_extra_info_id => l_etei_id
,p_object_version_number => l_etei_ovn
,p_eei_information_category => 'PQP_UK_ELEMENT_ATTRIBUTION'
,p_eei_information1 => 'H'
-- For Hourly Time Dimension
,p_eei_information2 => 'SP'
-- Spinal Points Pay Source Value
,p_eei_information3 => p_ele_psr_name
,p_eei_information4 => 'N'
-- No FTE
,p_eei_information5 => 'N'
-- 'No' Service History
);
SELECT COUNT(*)
INTO l_count
FROM ff_formulas_f
WHERE formula_name = UPPER(l_modified_base_ele || '_payscale_ff');
SELECT formula_type_id
INTO l_formula_type_id
FROM ff_formula_types
WHERE formula_type_name = 'Oracle Payroll';
INSERT INTO ff_formulas_f
( formula_id ,
effective_start_date ,
effective_end_date ,
business_group_id ,
legislation_code ,
formula_type_id ,
formula_name ,
description ,
formula_text ,
last_update_date ,
last_updated_by ,
last_update_login ,
created_by ,
creation_date )
VALUES
( ff_formulas_s.NEXTVAL , -- formula_id
TO_DATE(l_date,'dd/mm/yyyy') , -- effective_start_date
TO_DATE('31/12/4712', 'DD/MM/YYYY') , -- effective_end_date
l_business_group_id , -- business_group_id
NULL , -- legislation_code
l_formula_type_id , -- formula_type_id
l_formula_name , -- formula_name
l_description , -- description
l_formula_text , -- formula_text
SYSDATE , -- last_update_date
-1 , -- last_updated_by
-1 , -- last_update_login
-1 , -- created_by
SYSDATE ); -- creation_date
SELECT COUNT(*)
INTO l_count
FROM pay_event_groups
WHERE event_group_name = l_pg_name;
SELECT COUNT(*)
INTO l_count
FROM pay_datetracked_events
WHERE column_name = 'GRADE_ID'
AND event_group_id = l_event_group_id
AND dated_table_id = l_dated_table_id ;
,p_update_type => 'U'
,p_column_name => 'GRADE_ID'
,p_business_group_id => l_business_group_id
,p_legislation_code => NULL
,p_datetracked_event_id => l_dt_event_id
,p_object_version_number => l_ovn );
SELECT COUNT(*)
INTO l_count
FROM pay_datetracked_events
WHERE column_name = 'STEP_ID'
AND event_group_id = l_event_group_id
AND dated_table_id = l_dated_table_id ;
,p_update_type => 'U'
,p_column_name => 'STEP_ID'
,p_business_group_id => l_business_group_id
,p_legislation_code => NULL
,p_datetracked_event_id => l_dt_event_id
,p_object_version_number => l_ovn
) ;
SELECT COUNT(*)
INTO l_count
FROM pay_datetracked_events
WHERE column_name = 'VALUE'
AND event_group_id = l_event_group_id
AND dated_table_id = l_dated_table_id ;
,p_update_type => 'U'
,p_column_name => 'VALUE'
,p_business_group_id => l_business_group_id
,p_legislation_code => NULL
,p_datetracked_event_id => l_dt_event_id
,p_object_version_number => l_ovn
) ;
SELECT COUNT(*)
INTO l_count
FROM pay_datetracked_events
WHERE column_name = 'EFFECTIVE_START_DATE'
AND event_group_id = l_event_group_id
AND dated_table_id = l_dated_table_id ;
,p_update_type => 'U'
,p_column_name => 'EFFECTIVE_START_DATE'
,p_business_group_id => l_business_group_id
,p_legislation_code => NULL
,p_datetracked_event_id => l_dt_event_id
,p_object_version_number => l_ovn
) ;
SELECT COUNT(*)
INTO l_count
FROM pay_datetracked_events
WHERE column_name = 'EFFECTIVE_END_DATE'
AND event_group_id = l_event_group_id
AND dated_table_id = l_dated_table_id;
,p_update_type => 'U'
,p_column_name => 'EFFECTIVE_END_DATE'
,p_business_group_id => l_business_group_id
,p_legislation_code => NULL
,p_datetracked_event_id => l_dt_event_id
,p_object_version_number => l_ovn );
SELECT COUNT(*)
INTO l_count
FROM pay_datetracked_events
WHERE column_name = 'LOCATION_ID'
AND event_group_id = l_event_group_id
AND dated_table_id = l_dated_table_id ;
,p_update_type => 'U'
,p_column_name => 'LOCATION_ID'
,p_business_group_id => l_business_group_id
,p_legislation_code => NULL
,p_datetracked_event_id => l_dt_event_id
,p_object_version_number => l_ovn
) ;
SELECT COUNT(*)
INTO l_count
FROM pay_element_types_f
WHERE UPPER(element_name) = UPPER(LTRIM(RTRIM(p_ele_name)));
pay_element_extra_info_api.update_element_extra_info
(p_element_type_extra_info_id => l_etei_id
,p_object_version_number => l_etei_ovn
,p_eei_information_category => 'PQP_UK_ELEMENT_ATTRIBUTION'
,p_eei_information1 => NVL(p_ele_extra_td, 'H')
-- For Hourly Time Dimension
,p_eei_information2 => p_ele_extra_psv
-- Spinal Points Pay Source Value
,p_eei_information3 => p_ele_extra_qualifier
,p_eei_information4 => NVL(p_ele_extra_fte, 'N')
-- No FTE
,p_eei_information5 => NVL(p_ele_extra_sh, 'N')
-- 'No' Service History
);
SELECT COUNT(*)
INTO l_count
FROM ff_formulas_f
WHERE formula_name = l_new_formula_name ;
SELECT formula_type_id
INTO l_formula_type_id
FROM ff_formula_types
WHERE formula_type_name = 'Oracle Payroll';
INSERT INTO ff_formulas_f
(formula_id ,
effective_start_date ,
effective_end_date ,
business_group_id ,
legislation_code ,
formula_type_id ,
formula_name ,
description ,
formula_text ,
last_update_date ,
last_updated_by ,
last_update_login ,
created_by ,
creation_date )
VALUES
(ff_formulas_s.NEXTVAL , -- formula_id
TO_DATE(p_ele_startdate_fr,'dd/mm/yyyy') ,
-- effective_start_date
TO_DATE('31/12/4712', 'DD/MM/YYYY') , -- effective_end_date
l_business_group_id , -- business_group_id
NULL , -- legislation_code
l_formula_type_id , -- formula_type_id
l_new_formula_name , -- formula_name
l_description , -- description
l_formula_text , -- formula_text
SYSDATE , -- last_update_date
-1 , -- last_updated_by
-1 , -- last_update_login
-1 , -- created_by
SYSDATE ); -- creation_date
SELECT aat.contract_type
FROM pqp_assignment_attributes_f aat
WHERE aat.assignment_id = p_assignment_id
AND p_effective_date between aat.effective_start_date
AND aat.effective_end_date;