[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO pay_user_column_instances_f
(user_column_instance_id
,effective_start_date
,effective_end_date
,user_row_id
,user_column_id
,business_group_id
,legislation_code
,legislation_subgroup
,value
)
SELECT
pay_user_column_instances_s.NEXTVAL
,urws.effective_start_date
,urws.effective_end_date
,urws.user_row_id
,ucol.user_column_id
,p_bg_id
,NULL
,NULL
,p_entit -- -999999 -999999 Band1
FROM pay_user_columns ucol
,pay_user_rows_f urws
WHERE ucol.user_table_id = p_user_tbl_id --fnd_number.canonical_to_number(l_eei_information9)
AND ucol.user_column_name = p_band -- bad bad refer 'PQP_GAP_ENTITLEMENT_BANDS'.'BAND1'
AND urws.user_table_id = ucol.user_table_id
AND urws.row_low_range_or_name = p_lower -- bad bad refer lookup code 'PQP_GAP_ENTITLEMENT_ROWS'.'GB_GAP_PERCENTAGE_ROW'
AND SYSDATE BETWEEN urws.effective_start_date
AND urws.effective_end_date;
SELECT acty_base_rt_id
INTO l_acty_base_rt_id
FROM ben_acty_base_rt_f
WHERE pl_id = p_pl_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
DELETE FROM ben_extra_input_values
WHERE acty_base_rt_id = l_acty_base_rt_id;
UPDATE ben_acty_base_rt_f
SET element_type_id = NULL
,input_value_id = NULL
,ele_rqd_flag = 'N'
,object_version_number = object_version_number + 1
WHERE acty_base_rt_id = l_acty_base_rt_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
UPDATE ben_pl_f
SET pl_stat_cd = 'P'
,object_version_number = object_version_number + 1
WHERE pl_id = p_pl_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
UPDATE ben_pl_f
SET pl_stat_cd = 'A'
,object_version_number = object_version_number + 1
WHERE pl_id = p_pl_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT acty_base_rt_id
INTO l_acty_base_rt_id
FROM ben_acty_base_rt_f
WHERE pl_id = p_pl_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
UPDATE ben_acty_base_rt_f
SET element_type_id = p_element_type_id
,input_value_id = (SELECT input_value_id
FROM pay_input_values_f
WHERE element_type_id = p_element_type_id
AND UPPER(name) = l_input_name
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date)
,ele_rqd_flag = 'Y'
,object_version_number = object_version_number + 1
WHERE acty_base_rt_id = l_acty_base_rt_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
INSERT INTO ben_extra_input_values
(extra_input_value_id -- NOT NULL NUMBER(15)
,acty_base_rt_id -- NOT NULL NUMBER(15)
,input_value_id -- NOT NULL NUMBER(15)
,input_text -- VARCHAR2(240)
,upd_when_ele_ended_cd -- VARCHAR2(30)
,return_var_name -- NOT NULL VARCHAR2(240)
,business_group_id -- NOT NULL NUMBER(15)
,object_version_number
)
(SELECT
ben_extra_input_values_s.NEXTVAL
,l_acty_base_rt_id
,input_value_id
,NULL
,DECODE(UPPER(name),'ABSENCE END DATE','U','C')
,'L_'||TRANSLATE(UPPER(name),' ','_')
,business_group_id
,1
FROM pay_input_values_f
WHERE element_type_id = p_element_type_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date
AND UPPER(name) in ('ABSENCE TYPE','ABSENCE END DATE','ABSENCE START DATE','PLAN ID','ABSENCE ID')
);
INSERT INTO ben_extra_input_values
(extra_input_value_id -- NOT NULL NUMBER(15)
,acty_base_rt_id -- NOT NULL NUMBER(15)
,input_value_id -- NOT NULL NUMBER(15)
,input_text -- VARCHAR2(240)
,upd_when_ele_ended_cd -- VARCHAR2(30)
,return_var_name -- NOT NULL VARCHAR2(240)
,business_group_id -- NOT NULL NUMBER(15)
,object_version_number
)
(SELECT
ben_extra_input_values_s.NEXTVAL
,l_acty_base_rt_id
,input_value_id
,NULL
,DECODE(UPPER(name),'MATERNITY END DATE','U','C')
,'L_'||TRANSLATE(UPPER(name),' ','_')
,business_group_id
,1
FROM pay_input_values_f
WHERE element_type_id = p_element_type_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date
AND UPPER(name) in ('ABSENCE TYPE','MATERNITY END DATE','MATERNITY START DATE','PLAN ID','ABSENCE ID','EWC')
);
INSERT INTO ben_ler_per_info_cs_ler_f
(ler_per_info_cs_ler_id -- NOT NULL NUMBER(15)
,effective_start_date -- NOT NULL DATE
,effective_end_date -- NOT NULL DATE
,business_group_id -- NOT NULL NUMBER(15)
,ler_id -- NOT NULL NUMBER(15)
,per_info_chg_cs_ler_id -- NOT NULL NUMBER(15)
,object_version_number -- NUMBER(9)
)
SELECT ben_ler_per_info_cs_ler_f_s.NEXTVAL
,p_effective_date
,ler.effective_end_date
,p_business_group_id
,ler.ler_id
,pcd.per_info_chg_cs_ler_id
,1
FROM ben_ler_f ler
,ben_per_info_chg_cs_ler_f pcd
WHERE ler.business_group_id = p_business_group_id
AND ler.typ_cd = 'ABS'
AND pcd.name = ler.name
AND pcd.business_group_id = ler.business_group_id
AND p_effective_date BETWEEN ler.effective_start_date
AND ler.effective_end_date
AND p_effective_date BETWEEN pcd.effective_start_date
AND pcd.effective_end_date
AND NOT EXISTS -- do not insert if there a person change allready for this ler
(SELECT 1
FROM ben_ler_per_info_cs_ler_f pchg
WHERE pchg.ler_id = ler.ler_id -- no need to check effectiveness
);
INSERT INTO PQP_CONFIGURATION_MODULES
( MODULE_ID -- pqp_configuration_modules_s.nextval
,MODULE_NAME -- 'Absence Schemes'
,BUSINESS_GROUP_ID -- NULL
,LEGISLATION_CODE -- 'GB'
,DESCRIPTION -- 'GB Absence Schemes OSP/OMP'
,APPLICATION_ID -- 8303
,OBJECT_VERSION_NUMBER -- 1
,LAST_UPDATE_DATE -- hr_api.g_sot -- DO NOT USE SYSDATE
,LAST_UPDATED_BY -- 2
,LAST_UPDATE_LOGIN -- 2
,CREATED_BY -- 2
,CREATION_DATE -- hr_api.g_sot -- DO NOT USE SYSDATE
)
SELECT
pqp_configuration_modules_s.nextval
,'Absence Schemes'
,NULL
,'GB'
,'GB Absence Schemes OSP/OMP'
,8303
,1
,hr_api.g_sot
,2
,2
,2
,hr_api.g_sot
FROM DUAL
WHERE NOT EXISTS
( SELECT 1 FROM pqp_configuration_modules
WHERE module_name = 'Absence Schemes'
and legislation_code = 'GB'
);
SELECT module_id
INTO l_module_id
FROM pqp_configuration_modules
where module_name = 'Absence Schemes'
and legislation_code = 'GB' ;
INSERT INTO PQP_CONFIGURATION_TYPES
( CONFIGURATION_TYPE -- 'PQP_GB_OSP_OMP_CONFIG'
,MODULE_ID -- l_module_id -- sequence of above row
,ACTIVE_INACTIVE_FLAG -- 'Y'
,DESCRIPTION -- 'OSP/OMP Absence Schemes Configuration'
,MULTIPLE_OCCURENCES_FLAG -- 'N'
,LEGISLATION_CODE -- 'GB'
,PROTECTED_FLAG -- 'Y'
,PROGRAM_APPLICATION_ID -- NULL
,PROGRAM_ID -- NULL
,REQUEST_ID -- NULL
,PROGRAM_UPDATE_DATE -- NULL
,OBJECT_VERSION_NUMBER -- 1
,LAST_UPDATE_DATE -- hr_api.g_sot -- DO NOT USE SYSDATE
,LAST_UPDATED_BY -- 2
,LAST_UPDATE_LOGIN -- 2
,CREATED_BY -- 2
,CREATION_DATE -- hr_api.g_sot -- DO NOT USE SYSDATE
,TOTAL_UNIQUE_COLUMNS -- NULL
)
SELECT
'PQP_GB_OSP_OMP_CONFIG'
,l_module_id
,'Y'
,'OSP/OMP Absence Schemes Configuration'
,'N'
,'GB'
,'Y'
,NULL
,NULL
,NULL
,NULL
,1
,hr_api.g_sot
,2
,2
,2
,hr_api.g_sot
,NULL
FROM DUAL
WHERE NOT EXISTS
( SELECT 1 FROM pqp_configuration_types
WHERE configuration_type = 'PQP_GB_OSP_OMP_CONFIG'
AND module_id = l_module_id
AND legislation_code = 'GB'
) ;
SELECT element_type_id
,object_version_number
FROM pay_shadow_element_types
WHERE template_id = l_template_id
AND element_name = c_ele_name;
SELECT balance_type_id
,object_version_number
FROM pay_shadow_balance_types
WHERE template_id = l_template_id
AND balance_name = c_bal_name;
SELECT 'X'
FROM pay_element_type_extra_info
WHERE eei_information1 = fnd_number.number_to_canonical(p_plan_id)
AND eei_information16 = 'Y'
AND information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
AND rownum = 1;
SELECT template_id
FROM pay_element_templates
WHERE template_name = l_template_name
AND legislation_code = p_legislation_code
AND template_type = 'T'
AND business_group_id is NULL;
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name = c_object_name
AND business_group_id = p_bg_id;
SELECT ptco.core_object_id
FROM pay_shadow_balance_types psbt,
pay_template_core_objects ptco
WHERE psbt.template_id = l_template_id
AND psbt.balance_name = c_object_name
AND ptco.template_id = psbt.template_id
AND ptco.shadow_object_id = psbt.balance_type_id;
PROCEDURE update_ele_retro_info (p_main_ele_name in varchar2
,p_retro_ele_name in varchar2
) IS
--
l_main_ele_type_id pay_element_types_f.element_type_id%TYPE;
'update_ele_retro_info';
UPDATE pay_element_types_f
SET retro_summ_ele_id = l_retro_ele_type_id
WHERE element_type_id = l_main_ele_type_id;
END update_ele_retro_info;
PROCEDURE update_ipval_defval(p_ele_name IN VARCHAR2
,p_ip_name IN VARCHAR2
,p_def_value IN VARCHAR2)
IS
CURSOR csr_getinput(c_ele_name varchar2
,c_iv_name varchar2)
IS
SELECT input_value_id
,piv.name
,piv.element_type_id
FROM pay_input_values_f piv
,pay_element_types_f pet
WHERE element_name = c_ele_name
AND piv.element_type_id = pet.element_type_id
AND (piv.business_group_id = p_bg_id OR piv.business_group_id IS NULL)
AND piv.name = c_iv_name
AND (piv.legislation_code = 'GB' OR piv.legislation_code IS NULL);
SELECT rowid
FROM pay_input_values_f
WHERE input_value_id = c_ip_id
AND element_type_id = c_element_type_id
FOR UPDATE NOWAIT;
'update_ipval_defval';
UPDATE pay_input_values_f
SET default_value = p_def_value
WHERE rowid = csr_updinput_rec.rowid;
END update_ipval_defval;
SELECT user_table_id
FROM pay_user_tables
WHERE user_table_name = p_udt_name
AND (business_group_id = p_bg_id OR
business_group_id IS NULL);
SELECT meaning
FROM hr_lookups
WHERE lookup_type = p_lookup_type
AND lookup_code like p_lookup_code
AND enabled_flag = 'Y'
ORDER BY lookup_code;
p_udt_cols.delete;
SELECT meaning
FROM hr_lookups
WHERE lookup_type = p_lookup_type
AND lookup_code like p_lookup_code
AND enabled_flag = 'Y'
ORDER BY lookup_code;
SELECT pay_user_rows_s.NEXTVAL
FROM dual;
pay_user_tables_pkg.insert_row
(p_rowid => l_udt_rowid
,p_user_table_id => l_user_table_id
,p_business_group_id => p_bg_id
,p_legislation_code => NULL
,p_legislation_subgroup => NULL
,p_range_or_match => p_udt_type.range_or_match
,p_user_key_units => p_udt_type.user_key_units
,p_user_table_name => p_udt_type.user_table_name
,p_user_row_title => p_udt_type.user_row_title
);
pay_user_columns_pkg.insert_row
(p_rowid => l_udt_cols_rowid
,p_user_column_id => l_user_column_id
,p_user_table_id => l_user_table_id
,p_business_group_id => p_udt_cols(i).business_group_id
,p_legislation_code => p_udt_cols(i).legislation_code
,p_legislation_subgroup => NULL
,p_user_column_name => p_udt_cols(i).user_column_name
,p_formula_id => p_udt_cols(i).formula_id
);
pay_user_rows_pkg.pre_insert
(p_rowid => l_udt_rows_rowid
,p_user_table_id => l_user_table_id
,p_row_low_range_or_name => p_udt_rows(i).row_low_range_or_name
,p_user_row_id => l_user_row_id
,p_business_group_id => p_bg_id
);
INSERT INTO pay_user_rows_f
(user_row_id
,effective_start_date
,effective_end_date
,business_group_id
,legislation_code
,user_table_id
,row_low_range_or_name
,display_sequence
,legislation_subgroup
,row_high_range
)
VALUES
(l_user_row_id
,p_ele_eff_start_date
,nvl(p_ele_eff_end_date, hr_api.g_eot)
,p_udt_rows(i).business_group_id
,p_udt_rows(i).legislation_code
,l_user_table_id
,p_udt_rows(i).row_low_range_or_name
,p_udt_rows(i).display_sequence
,NULL
,p_udt_rows(i).row_high_range
);
SELECT 'X'
FROM fnd_lookup_types_vl
WHERE lookup_type = p_lookup_type
AND security_group_id = p_security_group_id
AND view_application_id = 3;
SELECT 'X'
FROM fnd_lookup_types_vl
WHERE meaning = p_lookup_meaning
AND security_group_id = p_security_group_id
AND view_application_id = 3;
fnd_lookup_types_pkg.insert_row
(
x_rowid => l_rowid
,x_lookup_type => p_lookup_type
,x_security_group_id => p_security_group_id
,x_view_application_id => 3
,x_application_id => 800
,x_customization_level => 'U'
,x_meaning => p_lookup_meaning
,x_description => NULL
,x_creation_date => SYSDATE
,x_created_by => l_user_id
,x_last_update_date => SYSDATE
,x_last_updated_by => l_user_id
,x_last_update_login => l_login_id
);
fnd_lookup_values_pkg.insert_row
(
x_rowid => l_rowid
,x_lookup_type => p_lookup_type
,x_security_group_id => p_security_group_id
,x_view_application_id => 3
,x_lookup_code => fnd_number.number_to_canonical(
p_lookup_values(i).abs_type_id)
,x_tag => NULL
,x_attribute_category => NULL
,x_attribute1 => NULL
,x_attribute2 => NULL
,x_attribute3 => NULL
,x_attribute4 => NULL
,x_attribute5 => NULL
,x_attribute6 => NULL
,x_attribute7 => NULL
,x_attribute8 => NULL
,x_attribute9 => NULL
,x_attribute10 => NULL
,x_attribute11 => NULL
,x_attribute12 => NULL
,x_attribute13 => NULL
,x_attribute14 => NULL
,x_attribute15 => NULL
,x_enabled_flag => 'Y'
,x_start_date_active => p_ele_eff_start_date
,x_end_date_active => NULL
,x_territory_code => NULL
,x_meaning => p_lookup_values(i).abs_type_name
,x_description => NULL
,x_creation_date => SYSDATE
,x_created_by => l_user_id
,x_last_update_date => SYSDATE
,x_last_updated_by => l_user_id
,x_last_update_login => l_login_id
);
SELECT 'X'
FROM fnd_lookup_types_vl
WHERE lookup_type = p_lookup_type
AND security_group_id = p_security_group_id
AND view_application_id = 3;
SELECT 'X'
FROM fnd_lookup_types_vl
WHERE meaning = p_lookup_meaning
AND security_group_id = p_security_group_id
AND view_application_id = 3;
fnd_lookup_types_pkg.insert_row
(
x_rowid => l_rowid
,x_lookup_type => p_lookup_type
,x_security_group_id => p_security_group_id
,x_view_application_id => 3
,x_application_id => 800
,x_customization_level => 'U'
,x_meaning => p_lookup_meaning
,x_description => NULL
,x_creation_date => SYSDATE
,x_created_by => l_user_id
,x_last_update_date => SYSDATE
,x_last_updated_by => l_user_id
,x_last_update_login => l_login_id
);
fnd_lookup_values_pkg.insert_row
(
x_rowid => l_rowid
,x_lookup_type => p_lookup_type
,x_security_group_id => p_security_group_id
,x_view_application_id => 3
,x_lookup_code => fnd_number.number_to_canonical(
p_lookup_values(i).plan_type_id)
,x_tag => NULL
,x_attribute_category => NULL
,x_attribute1 => NULL
,x_attribute2 => NULL
,x_attribute3 => NULL
,x_attribute4 => NULL
,x_attribute5 => NULL
,x_attribute6 => NULL
,x_attribute7 => NULL
,x_attribute8 => NULL
,x_attribute9 => NULL
,x_attribute10 => NULL
,x_attribute11 => NULL
,x_attribute12 => NULL
,x_attribute13 => NULL
,x_attribute14 => NULL
,x_attribute15 => NULL
,x_enabled_flag => 'Y'
,x_start_date_active => p_ele_eff_start_date
,x_end_date_active => NULL
,x_territory_code => NULL
,x_meaning => p_lookup_values(i).name
,x_description => NULL
,x_creation_date => SYSDATE
,x_created_by => l_user_id
,x_last_update_date => SYSDATE
,x_last_updated_by => l_user_id
,x_last_update_login => l_login_id
);
pay_shadow_element_api.update_shadow_element
(p_validate => false
,p_effective_date => p_ele_eff_start_date
,p_element_type_id => l_element_type_id
,p_element_name => l_ele_name(l_idx)
,p_reporting_name => l_ele_reporting_name(l_idx)
,p_description => l_ele_description(l_idx)
,p_relative_processing_priority => l_ele_pp(l_idx)
,p_object_version_number => l_ele_obj_ver_number
);
pay_shadow_element_api.update_shadow_element
(p_validate => false
,p_effective_date => p_ele_eff_start_date
,p_element_type_id => l_element_type_id
,p_element_name => l_ele_new_name(l_idx)
,p_relative_processing_priority => l_ele_pp(l_idx)
,p_object_version_number => l_ele_obj_ver_number
);
update_ele_retro_info
(p_main_ele_name => l_main_ele_name(l_idx)
,p_retro_ele_name => l_retro_ele_name(l_idx)
);
update_ipval_defval (p_ele_name => l_main_ele_name(l_main_ele_name.FIRST)
,p_ip_name => 'Pay Component Rate Type'
,p_def_value => p_pay_src_pay_component
);
l_udt_cols.DELETE;
l_udt_rows.DELETE;
PROCEDURE DELETE_ROW
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_ROWID VARCHAR2 IN
PROCEDURE INSERT_ROW
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_ROWID VARCHAR2 IN/OUT
P_USER_COLUMN_INSTANCE_ID NUMBER IN/OUT
P_EFFECTIVE_START_DATE DATE IN
P_EFFECTIVE_END_DATE DATE IN
P_USER_ROW_ID NUMBER IN
P_USER_COLUMN_ID NUMBER IN
P_BUSINESS_GROUP_ID NUMBER IN
P_LEGISLATION_CODE VARCHAR2 IN
P_LEGISLATION_SUBGROUP VARCHAR2 IN
P_VALUE VARCHAR2 IN
*/
INSERT INTO pay_user_column_instances_f
(user_column_instance_id
,effective_start_date
,effective_end_date
,user_row_id
,user_column_id
,business_group_id
,legislation_code
,legislation_subgroup
,value
)
SELECT
pay_user_column_instances_s.NEXTVAL
,urws.effective_start_date
,urws.effective_end_date
,urws.user_row_id
,ucol.user_column_id
,p_bg_id
,NULL
,NULL
,'100' -- -999999 -999999 Band1
FROM pay_user_columns ucol
,pay_user_rows_f urws
WHERE ucol.user_table_id = fnd_number.canonical_to_number(l_eei_information9)
AND ucol.user_column_name = 'Band1' -- bad bad refer 'PQP_GAP_ENTITLEMENT_BANDS'.'BAND1'
AND urws.user_table_id = ucol.user_table_id
AND urws.row_low_range_or_name = '-999999' -- bad bad refer lookup code 'PQP_GAP_ENTITLEMENT_ROWS'.'GB_GAP_PERCENTAGE_ROW'
AND SYSDATE BETWEEN urws.effective_start_date
AND urws.effective_end_date;
INSERT INTO pay_user_column_instances_f
(user_column_instance_id
,effective_start_date
,effective_end_date
,user_row_id
,user_column_id
,business_group_id
,legislation_code
,legislation_subgroup
,value
)
SELECT
pay_user_column_instances_s.NEXTVAL
,urws.effective_start_date
,urws.effective_end_date
,urws.user_row_id
,ucol.user_column_id
,p_bg_id
,NULL
,NULL
,'50' -- -999999 -999999 Band2
FROM pay_user_columns ucol
,pay_user_rows_f urws
WHERE ucol.user_table_id = fnd_number.canonical_to_number(l_eei_information9)
AND ucol.user_column_name = 'Band2' -- bad bad refer 'PQP_GAP_ENTITLEMENT_BANDS'.'BAND1'
AND urws.user_table_id = ucol.user_table_id
AND urws.row_low_range_or_name = '-999999' -- bad bad refer lookup code 'PQP_GAP_ENTITLEMENT_ROWS'.'GB_GAP_PERCENTAGE_ROW'
AND SYSDATE BETWEEN urws.effective_start_date
AND urws.effective_end_date;
INSERT INTO pay_user_column_instances_f
(user_column_instance_id
,effective_start_date
,effective_end_date
,user_row_id
,user_column_id
,business_group_id
,legislation_code
,legislation_subgroup
,value
)
SELECT
pay_user_column_instances_s.NEXTVAL
,urws.effective_start_date
,urws.effective_end_date
,urws.user_row_id
,ucol.user_column_id
,p_bg_id
,NULL
,NULL
,'182' -- 0 999999 Band1
FROM pay_user_columns ucol
,pay_user_rows_f urws
WHERE ucol.user_table_id = fnd_number.canonical_to_number(l_eei_information9)
AND ucol.user_column_name = 'Band1' -- bad bad refer 'PQP_GAP_ENTITLEMENT_BANDS'.'BAND1'
AND urws.user_table_id = ucol.user_table_id
AND urws.row_low_range_or_name = '0' -- bad bad refer lookup code 'PQP_GAP_ENTITLEMENT_ROWS'.'GB_GAP_PERCENTAGE_ROW'
AND SYSDATE BETWEEN urws.effective_start_date
AND urws.effective_end_date;
INSERT INTO pay_user_column_instances_f
(user_column_instance_id
,effective_start_date
,effective_end_date
,user_row_id
,user_column_id
,business_group_id
,legislation_code
,legislation_subgroup
,value
)
SELECT
pay_user_column_instances_s.NEXTVAL
,urws.effective_start_date
,urws.effective_end_date
,urws.user_row_id
,ucol.user_column_id
,p_bg_id
,NULL
,NULL
,'183' -- 0 999999 Band2
FROM pay_user_columns ucol
,pay_user_rows_f urws
WHERE ucol.user_table_id = fnd_number.canonical_to_number(l_eei_information9)
AND ucol.user_column_name = 'Band2' -- bad bad refer 'PQP_GAP_ENTITLEMENT_BANDS'.'BAND1'
AND urws.user_table_id = ucol.user_table_id
AND urws.row_low_range_or_name = '0' -- bad bad refer a variable set in prev section when creating this row
AND SYSDATE BETWEEN urws.effective_start_date
AND urws.effective_end_date;
PROCEDURE DELETE_ROW
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_ROWID VARCHAR2 IN
PROCEDURE INSERT_ROW
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_ROWID VARCHAR2 IN/OUT
P_USER_COLUMN_INSTANCE_ID NUMBER IN/OUT
P_EFFECTIVE_START_DATE DATE IN
P_EFFECTIVE_END_DATE DATE IN
P_USER_ROW_ID NUMBER IN
P_USER_COLUMN_ID NUMBER IN
P_BUSINESS_GROUP_ID NUMBER IN
P_LEGISLATION_CODE VARCHAR2 IN
P_LEGISLATION_SUBGROUP VARCHAR2 IN
P_VALUE VARCHAR2 IN
*/
create_udt_entry(
p_bg_id =>p_bg_id
,p_band =>'Band1'
,p_entit =>'100'
,p_lower =>'-999999'
,p_user_tbl_id => fnd_number.canonical_to_number(l_eei_information9)
);
l_udt_cols.DELETE;
l_udt_rows.DELETE;
PROCEDURE delete_user_template
(p_plan_id in number
,p_business_group_id in number
,p_abs_ele_name in varchar2
,p_abs_ele_type_id in number
,p_abs_primary_yn in varchar2
,p_security_group_id in number
,p_effective_date in date
) IS
--
l_template_id NUMBER(9);
l_proc_name varchar2(72) := g_package_name || 'delete_user_template';
SELECT pee.eei_information8 entitlements_uom
,pee.eei_information11 daily_rate_uom
FROM pay_element_type_extra_info pee
WHERE element_type_id = p_ele_type_id
AND information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO' ;
SELECT element_type_id
FROM pay_template_core_objects pet
,pay_element_types_f petf
WHERE pet.template_id = c_template_id
AND petf.element_type_id = pet.core_object_id
AND pet.core_object_type = 'ET';
SELECT element_type_extra_info_id
,fnd_number.canonical_to_number(eei_information9) entitlement_udt
,fnd_number.canonical_to_number(eei_information10) calendar_udt
,eei_information18 lookup_type
,eei_information24 plan_type_lookup
FROM pay_element_type_extra_info petei
WHERE element_type_id = c_element_type_id ;
SELECT 'X'
FROM pay_element_type_extra_info
WHERE eei_information1 <> fnd_number.number_to_canonical(p_plan_id)
AND eei_information9 = fnd_number.number_to_canonical(c_udt_id)
AND information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
AND rownum = 1;
SELECT 'X'
FROM pay_element_type_extra_info
WHERE eei_information1 <> fnd_number.number_to_canonical(p_plan_id)
AND eei_information10 = fnd_number.number_to_canonical(c_udt_id)
AND information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
AND rownum = 1;
SELECT 'X'
FROM pay_element_type_extra_info
WHERE eei_information1 <> fnd_number.number_to_canonical(p_plan_id)
AND eei_information18 = c_lookup_type
AND information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
AND rownum = 1;
SELECT 'X'
FROM pay_element_templates pets
,pay_shadow_element_types pset
,pay_template_core_objects ptco
,pay_element_type_extra_info peei
WHERE pets.template_id <> c_te_usrstr_id
-- For the given user structure
AND pets.template_name = p_template_name -- 'PQP OSP'
AND pets.template_type = 'U'
AND pets.business_group_id = p_business_group_id
AND pset.template_id = pets.template_id -- find the base element
AND pset.element_name = pets.base_name || ' OSP '||p_days_hours||'Absence'
AND ptco.template_id = pset.template_id -- For the base element
AND ptco.shadow_object_id = pset.element_type_id -- find the core element
AND ptco.core_object_type = 'ET'
AND ptco.core_object_id = peei.element_type_id -- For the core element
AND peei.eei_information1 = fnd_number.number_to_canonical(p_plan_id)
AND peei.information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO';
SELECT template_id
FROM pay_element_templates
WHERE base_name = p_abs_ele_name
AND template_name = p_template_name --'PQP OSP'
AND business_group_id = p_business_group_id
AND template_type = 'U';
SELECT 'X'
FROM ben_acty_base_rt_f
WHERE pl_id = p_plan_id
AND element_type_id = c_element_type_id
AND business_group_id = p_business_group_id;
SELECT lookup_code
FROM fnd_lookup_values_vl
WHERE lookup_type = c_lookup_type
AND security_group_id = p_security_group_id
AND view_application_id = 3;
SELECT DISTINCT(pete.eei_information18) lookup_type
FROM pay_element_type_extra_info pete
,pay_element_types_f petf
-- ,pay_element_templates pet
WHERE pete.element_type_id = petf.element_type_id
AND pete.information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
AND pete.eei_information16 = 'Y'
AND petf.element_name = c_base_name || ' OSP '||p_days_hours||'Absence'
-- pet.base_name || ' OSP '||p_days_hours||'Absence'
AND petf.business_group_id = p_business_group_id
--AND pet.template_name = p_template_name -- 'PQP OSP'
--AND pet.template_type = 'U'
--AND pet.business_group_id = p_business_group_id;
SELECT pet.base_name
FROM pay_element_templates pet
WHERE pet.template_name = p_template_name
AND pet.template_type = 'U'
AND pet.business_group_id = p_business_group_id ;
p_lookup_collection.delete;
PROCEDURE delete_lookup (p_lookup_type in varchar2
,p_security_group_id in number
,p_view_application_id in number
,p_lookup_collection in t_number
,p_delete_gap_entry in boolean default true )
IS
--
CURSOR csr_get_lkt_info
IS
SELECT 'X'
FROM fnd_lookup_types_vl
WHERE lookup_type = p_lookup_type
AND security_group_id = p_security_group_id
AND view_application_id = p_view_application_id;
SELECT lookup_code
FROM fnd_lookup_values_vl
WHERE lookup_type = p_lookup_type
AND security_group_id = p_security_group_id
AND view_application_id = p_view_application_id;
l_proc_name VARCHAR2(72) := g_package_name || 'delete_lookup';
IF p_delete_gap_entry THEN
IF NOT p_lookup_collection.EXISTS(fnd_number.canonical_to_number(
l_lookup_code)) THEN
fnd_lookup_values_pkg.delete_row
(x_lookup_type => 'PQP_GAP_ABSENCE_TYPES_LIST'
,x_security_group_id => p_security_group_id
,x_view_application_id => p_view_application_id
,x_lookup_code => l_lookup_code
);
END IF ; -- IF p_delete_gap_entry THEN
fnd_lookup_values_pkg.delete_row
(x_lookup_type => p_lookup_type
,x_security_group_id => p_security_group_id
,x_view_application_id => p_view_application_id
,x_lookup_code => l_lookup_code
);
fnd_lookup_types_pkg.delete_row
(x_lookup_type => p_lookup_type
,x_security_group_id => p_security_group_id
,x_view_application_id => p_view_application_id
);
END delete_lookup;
PROCEDURE delete_udt (p_udt_id in number)
IS
--
CURSOR csr_get_usr_table_id
IS
SELECT rowid
FROM pay_user_tables
WHERE user_table_id = p_udt_id
AND business_group_id = p_business_group_id;
SELECT user_column_id
FROM pay_user_columns
WHERE user_table_id = p_udt_id;
SELECT user_row_id
FROM pay_user_rows_f
WHERE user_table_id = p_udt_id;
l_proc_name VARCHAR(72) := g_package_name || 'delete_udt';
DELETE pay_user_column_instances_f
WHERE user_column_id = l_usr_col_id;
DELETE pay_user_columns
WHERE user_table_id = p_udt_id;
pay_user_rows_pkg.check_delete_row
(p_user_row_id => l_usr_row_id
,p_validation_start_date => NULL
,p_dt_delete_mode => 'ZAP'
);
DELETE pay_user_rows_f
WHERE user_row_id = l_usr_row_id;
pay_user_tables_pkg.delete_row
(p_rowid => l_rowid
,p_user_table_id => p_udt_id
);
END delete_udt;
BEGIN -- delete_user_template
-- for Multi Messages
hr_multi_message.enable_message_list;
IF csr_get_eei_info%FOUND -- if an EIT exists only then delete else ignore
THEN
-- Delete the EEI row
l_proc_step := 50;
pay_element_extra_info_api.delete_element_extra_info
(p_validate => FALSE
,p_element_type_extra_info_id => l_eei_info_id
,p_object_version_number => l_ovn_eei);
delete_udt (p_udt_id => l_entudt_id);
delete_udt (p_udt_id => l_caludt_id);
delete_lookup (p_lookup_type => l_lookup_type
,p_security_group_id => p_security_group_id
,p_view_application_id => 3
,p_lookup_collection => l_lookup_collection
);
delete_lookup (p_lookup_type => l_plan_type_lookup
,p_security_group_id => p_security_group_id
,p_view_application_id => 3
,p_lookup_collection => l_plan_type_lookup_collection
,p_delete_gap_entry => FALSE
);
fnd_lookup_types_pkg.delete_row
(x_lookup_type => 'PQP_GAP_ABSENCE_TYPES_LIST'
,x_security_group_id => p_security_group_id
,x_view_application_id => 3
);
pqp_gb_omp_template.delete_element_links
(p_business_group_id => p_business_group_id
,p_effective_start_date => p_effective_date
,p_effective_end_date => p_effective_date
,p_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);
END delete_user_template;
SELECT 'X'
FROM fnd_lookup_types_vl
WHERE lookup_type = p_lookup_type
AND security_group_id = p_security_group_id
AND view_application_id = 3;
SELECT 'X'
FROM fnd_lookup_types_vl
WHERE meaning = p_lookup_meaning
AND security_group_id = p_security_group_id
AND view_application_id = 3;
SELECT 'X'
FROM fnd_lookup_values_vl
WHERE lookup_type = p_lookup_type
AND lookup_code = c_lookup_code
AND security_group_id = p_security_group_id
AND view_application_id = 3;
SELECT 'X'
FROM fnd_lookup_values_vl
WHERE lookup_type = p_lookup_type
AND meaning = c_lookup_meaning
AND security_group_id = p_security_group_id
AND view_application_id = 3;
fnd_lookup_types_pkg.insert_row
(
x_rowid => l_rowid
,x_lookup_type => p_lookup_type
,x_security_group_id => p_security_group_id
,x_view_application_id => 3
,x_application_id => 800
,x_customization_level => 'U'
,x_meaning => p_lookup_meaning
,x_description => NULL
,x_creation_date => SYSDATE
,x_created_by => l_user_id
,x_last_update_date => SYSDATE
,x_last_updated_by => l_user_id
,x_last_update_login => l_login_id
);
fnd_lookup_values_pkg.insert_row
(
x_rowid => l_rowid
,x_lookup_type => p_lookup_type
,x_security_group_id => p_security_group_id
,x_view_application_id => 3
,x_lookup_code => fnd_number.number_to_canonical(
p_lookup_values(i).abs_type_id)
,x_tag => NULL
,x_attribute_category => NULL
,x_attribute1 => NULL
,x_attribute2 => NULL
,x_attribute3 => NULL
,x_attribute4 => NULL
,x_attribute5 => NULL
,x_attribute6 => NULL
,x_attribute7 => NULL
,x_attribute8 => NULL
,x_attribute9 => NULL
,x_attribute10 => NULL
,x_attribute11 => NULL
,x_attribute12 => NULL
,x_attribute13 => NULL
,x_attribute14 => NULL
,x_attribute15 => NULL
,x_enabled_flag => 'Y'
,x_start_date_active => p_ele_eff_start_date
,x_end_date_active => NULL
,x_territory_code => NULL
,x_meaning => p_lookup_values(i).abs_type_name
,x_description => NULL
,x_creation_date => SYSDATE
,x_created_by => l_user_id
,x_last_update_date => SYSDATE
,x_last_updated_by => l_user_id
,x_last_update_login => l_login_id
);