[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Parameters from insert_row procedure.
05-May-05 Shisriva 115.4 Fixes for bug 4350976. Changed defualting
of parameters in update_row and lock_row.
19-Aug-05 A.Rashid 115.5 Added validation and database item
generation code.
22-Jun-06 mseshadr 115.6 Added Load_Row procedure for lct support
08-Mar-07 ajeyam 115.7 Commented out the if condition to check
for max_updated_date > last_updated_date
on Load_Row procedure for bug 5921008.
============================================================================*/
--
--For MLS-----------------------------------------------------------------------
g_dummy_number number (30);
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Global_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_Data_Type VARCHAR2,
X_Global_Name IN OUT NOCOPY VARCHAR2,
X_Global_Description VARCHAR2,
X_Global_Value VARCHAR2) IS
--
CURSOR C IS SELECT rowid FROM ff_globals_f
WHERE global_id = X_Global_Id;
CURSOR C2 IS SELECT ff_globals_s.nextval FROM sys.dual;
INSERT INTO ff_globals_f
(global_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
data_type,
global_name,
global_description,
global_value)
VALUES
(X_Global_Id,
X_Effective_Start_Date,
X_Effective_End_Date,
X_Business_Group_Id,
X_Legislation_Code,
X_Data_Type,
X_Global_Name,
X_Global_Description,
X_Global_Value);
'ff_globals_f_pkg.insert_row');
END Insert_Row;
CURSOR C IS SELECT * FROM ff_globals_f
WHERE rowid = X_Rowid FOR UPDATE of Global_Id NOWAIT;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Global_Id NUMBER,
X_Effective_Start_Date DATE,
X_Effective_End_Date DATE,
X_Business_Group_Id NUMBER,
X_Legislation_Code VARCHAR2,
X_Data_Type VARCHAR2,
X_Global_Name VARCHAR2,
X_Global_Description VARCHAR2,
X_Global_Value VARCHAR2,
X_Base_Global_Name VARCHAR2 default hr_api.g_varchar2,
X_Base_Global_Description VARCHAR2 default hr_api.g_varchar2) IS
--
l_global_name varchar2(80);
UPDATE ff_globals_f
SET global_id = X_Global_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,
data_type = X_Data_Type,
global_name = l_global_name,
global_description = l_global_description,
global_value = X_Global_Value
WHERE rowid = X_rowid;
'ff_globals_f_pkg.update_row');
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2,
--X_Global_Id NUMBER,-- Extra Columns
X_Global_Name VARCHAR2,
X_Business_Group_Id NUMBER,
X_Legislation_Code VARCHAR2) IS
--
x_global_id NUMBER(9);
ffdict.delete_dbitem_check
(X_Global_Name,
X_Business_Group_Id,
X_Legislation_Code);
select Global_Id into x_global_id from ff_globals_f
where rowid = X_Rowid;
DELETE FROM ff_globals_f
WHERE rowid = X_Rowid;
'ff_globals_f_pkg.delete_row');
END Delete_Row;
delete from FF_GLOBALS_F_TL T
where not exists
(select NULL
from FF_GLOBALS_F B
where B.GLOBAL_ID = T.GLOBAL_ID
);
update FF_GLOBALS_F_TL T set (
GLOBAL_NAME,
GLOBAL_DESCRIPTION
) = (select
B.GLOBAL_NAME,
B.GLOBAL_DESCRIPTION
from FF_GLOBALS_F_TL B
where B.GLOBAL_ID = T.GLOBAL_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.GLOBAL_ID,
T.LANGUAGE
) in (select
SUBT.GLOBAL_ID,
SUBT.LANGUAGE
from FF_GLOBALS_F_TL SUBB, FF_GLOBALS_F_TL SUBT
where SUBB.GLOBAL_ID = SUBT.GLOBAL_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.GLOBAL_NAME <> SUBT.GLOBAL_NAME
or SUBB.GLOBAL_DESCRIPTION <> SUBT.GLOBAL_DESCRIPTION
or (SUBB.GLOBAL_DESCRIPTION is null
and SUBT.GLOBAL_DESCRIPTION is not null)
or (SUBB.GLOBAL_DESCRIPTION is not null
and SUBT.GLOBAL_DESCRIPTION is null)
));
insert into FF_GLOBALS_F_TL (
GLOBAL_ID,
GLOBAL_NAME,
GLOBAL_DESCRIPTION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LANGUAGE,
SOURCE_LANG
) select
B.GLOBAL_ID,
B.GLOBAL_NAME,
B.GLOBAL_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_GLOBALS_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_GLOBALS_F_TL T
where T.GLOBAL_ID = B.GLOBAL_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
select glb.global_id
from ff_globals_f glb
where glb.global_name = p_b_global_name
and glb.business_group_id is null
and (glb.legislation_code = p_legislation_code or
p_legislation_code is null and glb.legislation_code is null)
;
UPDATE ff_globals_f_tl
SET GLOBAL_NAME = nvl(X_GLOBAL_NAME,GLOBAL_NAME),
GLOBAL_DESCRIPTION = nvl(X_GLOBAL_DESCRIPTION,GLOBAL_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 GLOBAL_ID = l_global_id
;
,P_MAX_UPDATE_DATE DATE)is
cursor csr_glb_exists_for_bg(r_global_name varchar2,r_legislation_code varchar2) is
select null
from ff_globals_f ffg
where ffg.global_name = r_global_name
and ffg.business_group_id is not null
and exists (select null
from per_business_groups pbg
where pbg.business_group_id = ffg.business_group_id
and pbg.legislation_code = r_legislation_code);
Select data_type,global_id,creation_date,created_by,
max(trunc(last_update_date)) over(order by last_update_date) lud
from ff_globals_f ffg
where ffg.global_name=r_global_name
and ffg.legislation_code=r_legislation_code;
,P_LAST_UPDATE_DATE DATE
,P_LAST_UPDATED_BY NUMBER
,P_LAST_UPDATE_LOGIN NUMBER
,P_CREATED_BY NUMBER
,P_CREATION_DATE DATE)
is
Begin
--Assumption Global has been validated
--Idea is to enter a global row alonwith the WHO columns
hr_utility.trace('Inserting global '||P_BASE_GLOBAL_NAME||'Date '||P_EFFECTIVE_START_DATE);
Insert into FF_GLOBALS_F
(GLOBAL_ID
,EFFECTIVE_START_DATE
,EFFECTIVE_END_DATE
,BUSINESS_GROUP_ID
,LEGISLATION_CODE
,DATA_TYPE
,GLOBAL_NAME
,GLOBAL_DESCRIPTION
,GLOBAL_VALUE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE)
values
(P_GLOBAL_ID
,P_EFFECTIVE_START_DATE
,P_EFFECTIVE_END_DATE
,NULL
,P_LEGISLATION_CODE
,P_DATA_TYPE
,P_BASE_GLOBAL_NAME
,P_BASE_GLOBAL_DESCRIPTION
,P_GLOBAL_VALUE
,P_LAST_UPDATE_DATE
,P_LAST_UPDATED_BY
,P_LAST_UPDATE_LOGIN
,P_CREATED_BY
,P_CREATION_DATE);
hr_utility.trace('Inserting tl global '||P_GLOBAL_NAME_TL);
Procedure Delete_glb_and_child_entities(p_global_id number) is
CURSOR csr_global_bsd(r_global_id number) IS
select distinct ffu.formula_id
from ff_fdi_usages_f ffu
where ffu.item_name in (select fdbi.user_name
from ff_database_items fdbi,
ff_user_entities ffue
where fdbi.user_entity_id = ffue.user_entity_id
and ffue.creator_id = r_global_id
and ffue.creator_type = 'S');
select user_entity_id
from ff_user_entities
where creator_id = p_global_id
and creator_type = 'S'
and user_entity_name = r_base_global_name||'_GLOBAL_UE'
and legislation_code = r_legislation_code;
delete ff_fdi_usages_f where formula_id = i.formula_id;
delete ff_compiled_info_f where formula_id =i.formula_id;
delete ff_route_parameter_values
where user_entity_id = i.user_entity_id
and value =to_char(p_global_id) ;
delete ff_database_items_tl
where user_entity_id = i.user_entity_id;
delete ff_user_entities
where user_entity_id=i.user_entity_id;
delete ff_globals_f_tl
where global_id=p_global_id;
delete ff_globals_f
where global_id=p_global_id;
End Delete_glb_and_child_entities;
delete_glb_and_child_entities(p_global_id=>l_glb_exists_for_current_leg.global_id);
,P_LAST_UPDATE_DATE => P_MAX_UPDATE_DATE
,P_LAST_UPDATED_BY => fnd_global.user_id
,P_LAST_UPDATE_LOGIN => fnd_global.login_id
,P_CREATED_BY => l_glb_exists_for_current_leg.created_by
,P_CREATION_DATE => l_glb_exists_for_current_leg.creation_date
);
elsif ( p_max_update_date = l_glb_exists_for_current_leg.lud and g_glb_record.global_upload_flag)then
hr_utility.trace(P_BASE_GLOBAL_NAME||'*40');
,P_LAST_UPDATE_DATE => P_MAX_UPDATE_DATE
,P_LAST_UPDATED_BY => fnd_global.user_id
,P_LAST_UPDATE_LOGIN => fnd_global.login_id
,P_CREATED_BY => g_glb_record.created_by
,P_CREATION_DATE => g_glb_record.creation_date
);
select ff_globals_s.nextval
into l_new_global_id
from dual;
,P_LAST_UPDATE_DATE => P_MAX_UPDATE_DATE
,P_LAST_UPDATED_BY => fnd_global.user_id
,P_LAST_UPDATE_LOGIN => fnd_global.login_id
,P_CREATED_BY => fnd_global.user_id
,P_CREATION_DATE => sysdate
);
,P_LAST_UPDATE_DATE => P_MAX_UPDATE_DATE
,P_LAST_UPDATED_BY => fnd_global.user_id
,P_LAST_UPDATE_LOGIN => fnd_global.login_id
,P_CREATED_BY => g_glb_record.created_by
,P_CREATION_DATE => g_glb_record.creation_date
);