[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_dt_delete_mode varchar2,
p_validation_start_date date,
p_validation_end_date date,
p_formula_details FormulaRec
) Is
--
v_dummy number;
select fqr.formula_id
from ff_qp_reports fqr
where fqr.formula_id = p_formula_id;
SELECT has.formula_id
from hr_assignment_sets has
where has.formula_id = p_formula_id;
select puc.formula_id
from pay_user_columns puc
where puc.formula_id = p_formula_id;
select piv.formula_id
from pay_input_values_f piv
where piv.formula_id = p_formula_id
and piv.effective_start_date <= p_validation_end_date
and piv.effective_end_date >= p_validation_start_date;
select psr.formula_id
from pay_status_processing_rules_f psr
where psr.formula_id = p_formula_id
and psr.effective_start_date <= p_validation_end_date
and psr.effective_end_date >= p_validation_start_date;
select pmr.formula_id
from pay_magnetic_records pmr
where pmr.formula_id = p_formula_id;
select 1
from pay_accrual_plans pap
where pap.accrual_formula_id = p_formula_id
or pap.ineligibility_formula_id = p_formula_id
or pap.co_formula_id = p_formula_id
or pap.payroll_formula_id = p_formula_id;
select 1
from pay_element_types_f pet
where ( pet.proration_formula_id = p_formula_id
or pet.formula_id = p_formula_id
or pet.iterative_formula_id = p_formula_id )
and pet.effective_start_date <= p_validation_end_date
and pet.effective_end_date >= p_validation_start_date;
select pce.formula_id
from per_cagr_entitlements pce
where pce.formula_id = p_formula_id
and pce.start_date <= p_validation_end_date
and (pce.end_date is null or pce.end_date >= p_validation_start_date );
select pcei.beneficial_formula_id
from per_cagr_entitlement_items pcei
where pcei.beneficial_formula_id = p_formula_id;
select pcer.formula_id
from per_cagr_entitlement_results pcer
where pcer.formula_id = p_formula_id
and pcer.start_date <= p_validation_end_date
and (pcer.end_date is null or pcer.end_date >= p_validation_start_date );
select pcrr.formula_id
from per_cagr_retained_rights pcrr
where pcrr.formula_id = p_formula_id
and pcrr.start_date <= p_validation_end_date
and (pcrr.end_date is null or pcrr.end_date >= p_validation_start_date );
select 1
from pay_au_modules pam
where pam.formula_name = p_formula_details.formula_name
and (
(pam.legislation_code is null and pam.business_group_id is null)
or (pam.business_group_id is null and pam.legislation_code=p_formula_details.legislation_code)
or (pam.legislation_code is null and pam.business_group_id=p_formula_details.business_group_id)
);
select 1
from pay_shadow_element_types pset
where (pset.skip_formula = p_formula_details.formula_name or pset.iterative_formula_name=
p_formula_details.formula_name )
and exists(
select null
from pay_element_templates pet
where pet.template_id = pset.template_id
and (
( pet.legislation_code is null and pet.business_group_id is null)
or (pet.legislation_code is null
and (
pet.business_group_id = p_formula_details.business_group_id
or p_formula_details.legislation_code =
(
select legislation_code
from per_business_groups
where business_group_id = pet.business_group_id
)
)
)
or (pet.business_group_id is null
and pet.legislation_code = p_formula_details.legislation_code)
)
);
select upper(formula_type_name)
from ff_formula_types
where formula_type_id = p_formula_details.formula_type_id;
if p_dt_delete_mode = 'ZAP' then
open csr_element_template;
if p_dt_delete_mode in ('ZAP','DELETE') then
--
open csr_cagr_retained_rights;
,p_dt_delete_mode varchar2
,p_validation_start_date date
,p_validation_end_date date
,p_formula_details FormulaRec
) is
--
--
cursor csr_future_clash(p_formula_name varchar2
,p_formula_type_id number
,p_business_group_id number
,p_legislation_code varchar2
,p_val_start_date date
,p_startup_mode varchar2) is
select 'X'
from ff_formulas_f ff
where ff.formula_name = p_formula_name
and ff.formula_type_id = p_formula_type_id
and ff.effective_start_date > p_val_start_date
and ((p_startup_mode = 'MASTER')
or (p_startup_mode = 'SEED'
and ((ff.legislation_code = p_legislation_code)
or (ff.legislation_code is null
and ff.business_group_id is null)))
or (p_startup_mode = 'NON-SEED'
and ((ff.business_group_id +0 = p_business_group_id)
or (ff.legislation_code is null
and ff.business_group_id is null)
or (ff.business_group_Id is null
and ff.legislation_code = p_legislation_code))));
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Formula_Id IN OUT NOCOPY NUMBER,
X_Effective_Start_Date DATE,
X_Effective_End_Date DATE,
X_Business_Group_Id NUMBER,
X_Legislation_Code VARCHAR2,
X_Formula_Type_Id NUMBER,
X_Formula_Name IN OUT NOCOPY VARCHAR2,
X_Description VARCHAR2,
X_Formula_Text VARCHAR2,
X_Sticky_Flag VARCHAR2,
X_Last_Update_Date IN OUT NOCOPY DATE) IS
--
CURSOR C IS SELECT rowid row_id, last_update_date FROM ff_formulas_f
WHERE formula_id = X_Formula_Id;
CURSOR C2 IS SELECT ff_formulas_s.nextval FROM sys.dual;
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,
sticky_flag)
VALUES
(X_Formula_Id,
X_Effective_Start_Date,
L_Effective_End_Date,
X_Business_Group_Id,
X_Legislation_Code,
X_Formula_Type_Id,
X_Formula_Name,
X_Description,
X_Formula_Text,
X_Sticky_Flag);
'ff_formulas_f_pkg.insert_row');
X_Last_Update_Date := ReturnInfo.last_update_date;
END Insert_Row;
CURSOR C IS SELECT * FROM ff_formulas_f
WHERE rowid = X_Rowid FOR UPDATE NOWAIT;
x_last_update_date DATE) IS
--
CURSOR C IS SELECT last_update_date FROM ff_formulas_f
WHERE rowid = x_rowid FOR UPDATE NOWAIT;
v_current_update_date date;
FETCH C INTO v_current_update_date;
if nvl(x_last_update_date,to_date('01/01/0001','DD/MM/YYYY')) <>
nvl(v_current_update_date,to_date('01/01/0001','DD/MM/YYYY')) then
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Formula_Id NUMBER,
X_Effective_Start_Date DATE,
X_Effective_End_Date DATE,
X_Business_Group_Id NUMBER,
X_Legislation_Code VARCHAR2,
X_Formula_Type_Id NUMBER,
X_Formula_Name VARCHAR2,
X_Description VARCHAR2,
X_Formula_Text VARCHAR2,
X_Sticky_Flag VARCHAR2,
X_Last_Update_Date IN OUT NOCOPY DATE,
X_Base_Formula_Name VARCHAR2 default hr_api.g_varchar2,
X_Base_Description VARCHAR2 default hr_api.g_varchar2) IS
--
CURSOR C IS SELECT last_update_date FROM ff_formulas_f
WHERE rowid = x_rowid;
v_current_update_date date;
UPDATE ff_formulas_f
SET formula_id = X_Formula_Id,
effective_start_date = X_Effective_Start_Date,
effective_end_date = X_Effective_End_Date,
business_group_id = X_Business_Group_Id,
legislation_code = X_Legislation_Code,
formula_type_id = X_Formula_Type_Id,
formula_name = l_formula_name,
description = l_description,
formula_text = X_Formula_Text,
sticky_flag = X_Sticky_Flag
WHERE rowid = X_rowid;
'ff_formulas_f_pkg.update_row');
FETCH C INTO v_current_update_date;
'ff_formulas_f_pkg.update_row');
X_Last_Update_Date := v_current_update_date;
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2,
X_Formula_Id NUMBER,
X_Dt_Delete_Mode VARCHAR2,
X_Validation_Start_Date DATE,
X_Validation_End_Date DATE,
X_Effective_Date DATE) IS
l_oab_installed boolean;
select ff.formula_name
, ff.formula_type_id
, ff.business_group_id
, nvl(bg.legislation_code,ff.legislation_code)
, ff.effective_start_date
, ff.effective_end_date
from ff_formulas_f ff
, per_business_groups bg
where ff.formula_id = X_formula_id
and ff.business_group_id = bg.business_group_id (+)
and (
(X_Dt_Delete_Mode in ('DELETE_NEXT_CHANGE','FUTURE_CHANGE')
and ff.effective_end_date+1 = X_validation_start_date)
or ( X_Dt_Delete_Mode = 'DELETE' and X_Effective_Date+1 = X_validation_start_date)
or ( X_Dt_Delete_Mode = 'ZAP' and l_start_of_time = X_validation_start_date)
);
,X_Dt_Delete_Mode
,X_Validation_Start_Date
,X_Validation_End_Date
,Formula_Details);
if X_Dt_Delete_Mode NOT IN ('ZAP','DELETE') then
--
payroll_dnc_validation
(X_Formula_Id
,X_Dt_Delete_Mode
,X_Validation_Start_Date
,X_Validation_End_Date
,Formula_Details);
if X_Dt_Delete_Mode IN ('ZAP') then
begin
g_dml_status := TRUE;
DELETE FROM ff_formulas_f
WHERE rowid = X_Rowid;
'ff_formulas_f_pkg.delete_row');
END Delete_Row;
delete from FF_FORMULAS_F_TL T
where not exists
(select NULL
from FF_FORMULAS_F B
where B.FORMULA_ID = T.FORMULA_ID
);
update FF_FORMULAS_F_TL T set (
FORMULA_NAME,
DESCRIPTION
) = (select
B.FORMULA_NAME,
B.DESCRIPTION
from FF_FORMULAS_F_TL B
where B.FORMULA_ID = T.FORMULA_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.FORMULA_ID,
T.LANGUAGE
) in (select
SUBT.FORMULA_ID,
SUBT.LANGUAGE
from FF_FORMULAS_F_TL SUBB, FF_FORMULAS_F_TL SUBT
where SUBB.FORMULA_ID = SUBT.FORMULA_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.FORMULA_NAME <> SUBT.FORMULA_NAME
or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
));
insert into FF_FORMULAS_F_TL (
FORMULA_ID,
FORMULA_NAME,
DESCRIPTION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LANGUAGE,
SOURCE_LANG
) select
B.FORMULA_ID,
B.FORMULA_NAME,
B.DESCRIPTION,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN,
B.CREATED_BY,
B.CREATION_DATE,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from FF_FORMULAS_F_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from FF_FORMULAS_F_TL T
where T.FORMULA_ID = B.FORMULA_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
UPDATE ff_formulas_f_tl
SET FORMULA_NAME = nvl(X_FORMULA_NAME,FORMULA_NAME),
DESCRIPTION = nvl(X_DESCRIPTION,DESCRIPTION),
last_update_date = SYSDATE,
last_updated_by = decode(x_owner,'SEED',1,0),
last_update_login = 0,
source_lang = userenv('LANG')
WHERE userenv('LANG') IN (language,source_lang)
AND FORMULA_ID in
(select FORMULA_ID
from FF_FORMULAS_F
where nvl(FORMULA_NAME,'~null~')=nvl(X_B_FORMULA_NAME,'~null~')
and nvl(LEGISLATION_CODE,'~null~') = nvl(X_B_LEGISLATION_CODE,'~null~')
and BUSINESS_GROUP_ID is NULL);
SELECT 1
FROM ff_formulas_f_tl fft,
ff_formulas_f fff
WHERE upper(fft.formula_name)=upper(p_formula_name)
AND fft.formula_id = fff.formula_id
AND fft.language = p_language
AND (fff.formula_id <> p_formula_id OR p_formula_id IS NULL)
AND (nvl(fff.business_group_id,-1) = nvl(p_bus_grp_id,-1) OR p_bus_grp_id IS NULL)
AND (nvl(fff.LEGISLATION_CODE,'~null~') = nvl(p_leg_code,'~null~') OR p_leg_code IS NULL);