[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select max(process_date)
from ota_upgrade_log
where upgrade_id = p_upgrade_id
and upgrade_name = p_upgrade_name
and source_primary_key = '-1';
select nvl(max(upgrade_id),1)
into l_upgrade_id
from ota_upgrade_log ;
select fl.language_code
from fnd_languages fl
where fl.language_id = Nvl(p_evt_language_id,p_tav_language_id);
p_update_id in number default 1 ) is
l_category_usage_id ota_category_usages.category_usage_id%Type ;
Select Ocu.Category_usage_id
From Ota_category_usages_vl ocu
Where ocu.Business_group_id = p_business_group_id
and ocu.Category = p_dm
and ocu.Type = 'DM' ;
Select lkp.Meaning into l_dm_name
From Hr_lookups lkp
Where lkp.lookup_type = 'ACTIVITY_CATEGORY'
And lkp.lookup_code = 'INCLASS' ;
SELECT MIN(TAV.START_DATE) INTO l_course_min_st_dt
FROM OTA_ACTIVITY_VERSIONS TAV
WHERE NOT EXISTS
(SELECT ACI.ACTIVITY_VERSION_ID FROM OTA_ACT_CAT_INCLUSIONS ACI,OTA_CATEGORY_USAGES CTU
WHERE ACI.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID
AND ACI.CATEGORY_USAGE_ID = CTU.CATEGORY_USAGE_ID
AND CTU.TYPE = 'DM') ;
insert into ota_category_usages_tl
( CATEGORY_USAGE_ID,
LANGUAGE ,
CATEGORY ,
DESCRIPTION ,
SOURCE_LANG )
select
l_category_usage_id,
lkp.language,
lkp.meaning,
lkp.meaning,
lkp.source_lang
from fnd_lookup_values lkp
where lkp.lookup_type = 'ACTIVITY_CATEGORY'
and lkp.lookup_code = 'INCLASS'
and lkp.security_group_id = 0 -- added for bug#4116886
and lkp.view_application_id = 3; -- added for bug#4116886
,p_upgrade_id => p_update_id
,p_process_date => get_process_date(p_update_id,UPGRADE_NAME)
,p_log_type => LOG_TYPE_E
,p_upgrade_name => UPGRADE_NAME );
p_update_id in number default 1 ) IS
Cursor Csr_chk_root_ctg_exists Is
Select ctu.Category_usage_id from ota_Category_usages ctu, ota_Category_usages ct
where ctu.parent_cat_usage_id is NULL
and ctu.category_usage_id = ct.parent_cat_usage_id
and ctu.business_group_id = p_business_group_id
and ctu.business_group_id = ct.business_group_id
and ctu.type = 'C'
and ct.type = 'C' ;
SELECT ocu.category_usage_id,ocu.start_date_active
FROM ota_category_usages ocu
WHERE ocu.category = p_business_group_name -- business_group_id AND name is unique
AND ocu.business_group_id = p_business_group_id
AND type = 'C' ;
Update Ota_category_usages
Set Parent_cat_usage_id = Null
Where Category_usage_id = l_category_usage_id
and Business_group_id = p_business_group_id
and Parent_cat_usage_id = -1 ;
Insert into ota_category_usages_tl
(Category_usage_Id,
Language,
Category,
Description,
Source_Lang,
Created_By,
Creation_Date,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login )
Select l_category_usage_id,
orgtl.language,
orgtl.Name,
orgtl.name,
orgtl.source_lang,
orgtl.Created_By,
orgtl.Creation_date,
orgtl.Last_Updated_By,
orgtl.Last_Update_Date,
orgtl.Last_Update_Login
From hr_organization_units org, hr_all_organization_units_tl orgtl
Where orgtl.organization_id = p_business_group_id
and org.organization_id = org.business_group_id
and org.organization_id = orgtl.organization_id
And Not Exists (Select '1'
From OTA_CATEGORY_USAGES_TL T
Where T.Category_usage_Id = l_Category_usage_Id
And T.Language = Orgtl.Language ) ;
,p_upgrade_id => p_update_id
,p_process_date => get_process_date(UPGRADE_NAME,UPGRADE_NAME)
,p_log_type => LOG_TYPE_E
,p_upgrade_name => UPGRADE_NAME );
Update Ota_category_usages
Set parent_cat_usage_id = l_parent_cat_usage_id
Where parent_cat_usage_id is null
and Type = 'C'
and Business_group_id = p_business_group_id
and Category_usage_id <> l_parent_cat_usage_id ;
update ota_act_cat_inclusions
set activity_category = null
where activity_category is not null
and category_usage_id is not null;
select category_usage_id
from ota_category_usages_vl
where category = p_cat_name
and type = 'DM'
and business_group_id = p_bg_id;
select ctu.category_usage_id
,ctu.category
,ctu.online_flag
,ctu.synchronous_flag
,ctu.business_group_id
from ota_category_usages_vl ctu
where ctu.type ='DM'
and nvl(ctu.online_flag,'N') = 'N'
and nvl(ctu.synchronous_flag,'Y')= 'Y'
and ctu.category in (select lkp.meaning
from hr_lookups lkp
where lkp.lookup_type = 'ACTIVITY_CATEGORY'
and lkp.lookup_code = 'INCLASS');
select tav.activity_version_id
from ota_category_usages ctu
,ota_activity_versions tav
,ota_act_cat_inclusions aci
where aci.category_usage_id = ctu.category_usage_id
and tav.activity_version_id = aci.activity_version_id
and ctu.type = 'DM'
and tav.rco_id is not null
and ctu.category_usage_id = p_dm_id
and ( aci.primary_flag = 'Y'
or not exists(select 1 from ota_act_cat_inclusions aci1
where aci1.activity_version_id = tav.activity_version_id
and aci1.category_usage_id <> p_dm_id)) ;
select count(decode(evt.event_type,'SELFPACED',1,null)) selfpaced_c
,count(decode(evt.event_type,'SCHEDULED',1,null)) scheduled_c
from ota_events evt
where evt.activity_version_id = p_act_ver_id;
delete from ota_act_cat_inclusions
where activity_version_id = c_ol_tav.activity_version_id
and category_usage_id = c_ol_dms.category_usage_id;
Insert into ota_act_cat_inclusions
( activity_category
,activity_version_id
,category_usage_id
,object_version_number
,primary_flag
) values
(null
,c_ol_tav.activity_version_id
,l_category_usage_id
,1
,'Y');
select ctu.category_usage_id
,ctu.category
,ctu.online_flag
,ctu.synchronous_flag
,ctu.business_group_id
from ota_category_usages_vl ctu
where ctu.type = 'DM'
and nvl(ctu.online_flag,'N') = 'N'
and nvl(ctu.synchronous_flag,'Y')= 'Y'
and ctu.category not in (select lkp.meaning
from hr_lookups lkp
where lkp.lookup_type = 'ACTIVITY_CATEGORY'
and lkp.lookup_code = 'INCLASS');
select tav.activity_version_id
from ota_category_usages ctu
,ota_activity_versions tav
,ota_act_cat_inclusions aci
where aci.category_usage_id = ctu.category_usage_id
and tav.activity_version_id = aci.activity_version_id
and ctu.type = 'DM'
and tav.rco_id is not null
and ctu.category_usage_id = p_dm_id ;
select count(decode(evt.event_type,'SELFPACED',1,null)) selfpaced_c
,count(decode(evt.event_type,'SCHEDULED',1,null)) scheduled_c
from ota_events evt
where evt.activity_version_id = p_act_ver_id;
update ota_act_cat_inclusions aci
set primary_flag = decode(category_usage_id, c_ol_dms.category_usage_id,'Y','N') --'N'
where aci.activity_version_id = c_ol_tav.activity_version_id
and aci.category_usage_id in (select ctu.category_usage_id
from ota_Category_usages ctu
where ctu.type = 'DM' ) ;
update ota_category_usages
set online_flag = l_online_flag
,synchronous_flag =nvl(l_sync_flag,'N')
where category_usage_id = c_ol_dms.category_usage_id;
select ctu.category_usage_id
,ctu.category
,ctu.business_group_id
from ota_category_usages_vl ctu
where ctu.online_flag = 'Y'
and ctu.type = 'DM';
select tav.activity_version_id
,aci.primary_flag
from ota_activity_versions tav
,ota_act_cat_inclusions aci
where aci.category_usage_id = p_dm_id
and tav.activity_version_id = aci.activity_version_id
and tav.rco_id is null
and not exists (select 1 from ota_offerings off
where off.activity_version_id = tav.activity_version_id
and rownum =1);
delete from ota_act_cat_inclusions
where category_usage_id = c_ol_dms.category_usage_id
and activity_version_id = l_activity_version_id;
Insert into ota_act_cat_inclusions
( activity_category
,activity_version_id
,category_usage_id
,object_version_number
,primary_flag
) values
(null
,l_activity_version_id
,l_category_usage_id
,1
,l_primary_flag);
Select ctu.business_group_id
From Ota_Category_Usages ctu
Group by ctu.business_group_id ;
Select hou.name Bg_name
From hr_organization_units hou
Where hou.business_group_id = p_bg_id
and hou.business_group_id = hou.organization_id ;
p_update_id in number default 1 ) is
CURSOR csr_installed_languages is
SELECT lng.language_code,
lng.nls_Language
FROM fnd_languages lng
WHERE lng.installed_flag in ('I', 'B');
UPDATE ota_booking_deals tbd
SET tbd.category = (select ocu.category_usage_id
from ota_category_usages ocu
where tbd.category = ocu.category
and tbd.business_group_id = ocu.business_group_id
and ocu.type = 'D')
Where exists (select ocu.category_usage_id
from ota_category_usages ocu
where tbd.category = ocu.category
and tbd.business_group_id = ocu.business_group_id
and ocu.type = 'D');
add_log_entry( p_table_name=>'UPDATE_BOOKING_DEALS'
,p_source_primary_key => 1
,p_object_value => 'No record in Booking Deals record updated with Category Usage id'
,p_message_text => l_err_msg
,p_upgrade_id => p_update_id
,p_process_date => get_process_date(UPGRADE_NAME,UPGRADE_NAME)
,p_log_type => LOG_TYPE_E
,p_upgrade_name => UPGRADE_NAME );
update ota_category_usages ocu
set ocu.category = (SELECT lkp.meaning
FROM hr_lookups lkp
WHERE lkp.lookup_code = ocu.category
AND lkp.lookup_type = 'ACTIVITY_CATEGORY')
,Synchronous_flag = Decode(Type,'C',NULL,'DM','Y')
,Online_Flag = Decode(Type,'C',NULL,'DM','N')
WHERE category_usage_id between p_start_pkid and p_end_pkid
AND exists (SELECT lkp.meaning
FROM hr_lookups lkp
WHERE lkp.lookup_code = ocu.category
AND lkp.lookup_type = 'ACTIVITY_CATEGORY') ;
** For each installed language insert a new record into the TL table for
** each record in the range provided that is present in the base table.
*/
for c_language in csr_installed_languages loop
begin
/*
** Set language for iteration....
*/
ota_mls_utility.set_session_nls_language(c_language.nls_language);
** Insert the TL rows.
*/
Insert into OTA_CATEGORY_USAGES_TL
(Category_usage_Id,
Language,
Category,
Description,
Source_Lang,
Created_By,
Creation_Date,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login )
Select
M.Category_usage_Id,
L_Current_Language,
M.Category,
M.Category,
L_Userenv_language_code,
M.Created_By,
M.Creation_date,
M.Last_Updated_By,
M.Last_Update_Date,
M.Last_Update_Login
From OTA_CATEGORY_USAGES M
Where M.Category_usage_id Between P_start_pkid AND P_end_pkid
And Not Exists (Select '1'
From OTA_CATEGORY_USAGES_TL T
Where T.Category_usage_Id = M.Category_usage_Id
And T.Language = L_Current_Language ) ;
,p_upgrade_id => p_update_id
,p_process_date => get_process_date(UPGRADE_NAME,UPGRADE_NAME)
,p_log_type => LOG_TYPE_E
,p_upgrade_name => UPGRADE_NAME) ;
Select Nvl(Count(Category_usage_id),0) into l_rows_processed from ota_category_usages
Where Category_usage_id between p_start_pkid and p_end_pkid ;
PROCEDURE Upgrade_Delivery_Mode(p_update_id in number default 1 ) is
CURSOR csr_del_mode is
select ocu.category_usage_id, ocu.type,ocu.business_group_id,ocu.start_date_active,ocu.end_date_active
from ota_category_usages ocu
Where type = 'DM' or parent_cat_usage_id is not null ;
Select Min(tav.Start_Date),Max(tav.End_date),Max(Decode(tav.End_date,NULL,'Y','N'))
into l_course_min_st_dt, l_course_max_end_dt,l_course_end_dt_has_null
From ota_activity_versions tav,ota_act_cat_inclusions aci
Where tav.activity_version_id = aci.activity_version_id
and aci.category_usage_id = dm.category_usage_id;
Update Ota_Category_Usages
Set Start_date_active = l_course_min_st_dt
Where Category_usage_id = dm.category_usage_id
and Business_group_id = dm.business_group_id ;
Update Ota_Category_Usages
Set End_date_active = l_course_max_end_dt
Where Category_usage_id = dm.category_usage_id
and Business_group_id = dm.business_group_id ;
,p_update_id in number default 1) is
CURSOR csr_activity_versions (p_act_id number)is
SELECT tav.activity_version_id
FROM OTA_ACTIVITY_VERSIONS TAV
WHERE tav.activity_id = p_act_id;
SELECT ctu.category_usage_id
FROM ota_act_cat_inclusions cat,
ota_category_usages ctu
WHERE ctu.category_usage_id = cat.category_usage_id
AND ctu.type = 'C'
AND cat.primary_flag='Y'
AND cat.activity_version_id = p_act_ver_id;
select activity_id
from ota_activity_definitions
where category_usage_id = p_ctu_id;
SELECT 1
FROM ota_act_cat_inclusions cat
WHERE cat.category_usage_id =p_category_usage_id
AND cat.activity_version_id = p_act_ver_id;
update Ota_Activity_Versions
set activity_id = l_equivalent_tad
where activity_version_id = l_act_ver_id;
Insert into ota_act_cat_inclusions
( activity_category
,activity_version_id
,category_usage_id
,object_version_number
,primary_flag
) values
(null
,l_act_ver_id
,p_category_usage_id
,1
,l_primary_flag);
,p_upgrade_id => p_update_id
,p_process_date => get_process_date(UPGRADE_NAME,UPGRADE_NAME)
,p_log_type => LOG_TYPE_E
,p_upgrade_name => UPGRADE_NAME );
update ota_act_cat_inclusions
set primary_flag = 'Y'
where category_usage_id = p_category_usage_id
and activity_version_id = l_act_ver_id;
procedure update_act_ver_bg( p_business_group_id in number
,p_activity_id in number)
is
begin
Update Ota_Activity_Versions
Set Business_group_id = p_business_group_id
Where Activity_id = p_activity_id
and Business_group_id is NULL ;
end update_act_ver_bg;
procedure update_tad_cat_usg_id( p_business_group_id in number
,p_activity_id in number
,p_category_usage_id in number)
is
begin
Update Ota_activity_Definitions
Set Category_usage_id = p_category_usage_id
Where Business_group_id = p_business_group_id
and Activity_id = p_activity_id ;
end update_tad_cat_usg_id;
p_update_id in number default 1 ) is
CURSOR c_activity_def is
select ocu.category_usage_id, oct.category, ocu.business_group_id,oct.description
from ota_category_usages ocu, ota_category_usages_tl oct
where ocu.category_usage_id = oct.category_usage_id
and oct.language = Userenv('LANG')
and ocu.type = 'C'
and ocu.category_usage_id between p_start_pkid and p_end_pkid
and ocu. category_usage_id not in (select category_usage_id
from ota_activity_definitions
where category_usage_id is not null) ;
Select tad.Activity_id into l_activity_id
from ota_activity_definitions tad, ota_activity_definitions_tl adt
Where tad.Business_group_id = l_business_group_id
and tad.activity_id = adt.activity_id
and adt.Name = l_category
-- and Category_usage_id is NULL
Group by Tad.Activity_id ;
update_tad_cat_usg_id(l_business_group_id, l_activity_id,l_category_usage_id);
update_act_ver_bg(l_business_group_id, l_activity_id);
set_primary_category(l_activity_id,l_business_group_id,l_category_usage_id,p_update_id);
,p_upgrade_id => p_update_id
,p_process_date => get_process_date(p_update_id,UPGRADE_NAME)
,p_log_type => LOG_TYPE_E
,p_upgrade_name => UPGRADE_NAME);
p_update_id in number default 1 )
is
CURSOR csr_activity_type is
SELECT tad.activity_id,tad.business_group_id,
tad.name , tad.description,tad.Created_By,
tad.Creation_Date,tad.Last_Updated_By,
tad.Last_Update_Date,tad.Last_Update_Login,
Comments
,TAD_INFORMATION_CATEGORY
,TAD_INFORMATION1
,TAD_INFORMATION2
,TAD_INFORMATION3
,TAD_INFORMATION4
,TAD_INFORMATION5
,TAD_INFORMATION6
,TAD_INFORMATION7
,TAD_INFORMATION8
,TAD_INFORMATION9
,TAD_INFORMATION10
,TAD_INFORMATION11
,TAD_INFORMATION12
,TAD_INFORMATION13
,TAD_INFORMATION14
,TAD_INFORMATION15
,TAD_INFORMATION16
,TAD_INFORMATION17
,TAD_INFORMATION18
,TAD_INFORMATION19
,TAD_INFORMATION20
FROM ota_activity_definitions tad, ota_activity_definitions_tl adt
WHERE tad.activity_id between p_start_pkid AND p_end_pkid
and tad.activity_id = adt.activity_id
and adt.language = Userenv('LANG')
AND tad.category_usage_id is null ;
SELECT lng.language_code, lng.nls_Language
FROM fnd_languages lng
WHERE lng.installed_flag in ('I', 'B');
SELECT ocu.category_usage_id
FROM ota_category_usages ocu, ota_category_usages_tl oct
WHERE oct.category = p_act_name
AND ocu.type ='C'
AND ocu.category_usage_id = oct.category_usage_id
AND ocu.business_group_id = p_business_group_id;
select min(start_date)
from ota_activity_versions
where activity_id = p_act_id;
Select Category_usage_id into l_parent_category_usage_id
From Ota_category_usages
Where business_group_id = l_business_group_id
and parent_cat_usage_id is NULL
and type = 'C';
Insert into ota_category_usages_tl
(Category_usage_Id,
Language,
Category,
Description,
Source_Lang,
Created_By,
Creation_Date,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login )
Select l_category_usage_id,
M.language,
M.name,
M.description,
M.source_lang,
M.Created_By,
M.Creation_date,
M.Last_Updated_By,
M.Last_Update_Date,
M.Last_Update_Login
From Ota_activity_definitions_tl M
Where M.activity_id = l_act_id ;
,p_upgrade_id => p_update_id
,p_process_date => get_process_date(p_update_id,UPGRADE_NAME)
,p_log_type => LOG_TYPE_E
,p_upgrade_name => UPGRADE_NAME);
update_act_ver_bg(l_business_group_id, l_act_id);
update_tad_cat_usg_id(l_business_group_id, l_act_id,l_category_usage_id);
set_primary_category(l_act_id,l_business_group_id,l_category_usage_id,p_update_id);
select nvl(count(1),0)
into l_rows_processed
from ota_activity_definitions
where activity_id between p_start_pkid and p_end_pkid;
p_update_id in number default 1 ) is
l_dm_id number;
SELECT tav.activity_version_id
,tad.BUSINESS_GROUP_ID
,tav.version_name
,tav.start_date
,tav.end_date
,tav.duration
,tav.description
,tav.language_id
,tav.duration_units
,tav.maximum_attendees
,tav.maximum_internal_attendees
,tav.minimum_attendees
,tav.actual_cost
,tav.budget_cost
,tav.budget_currency_code
,tav.vendor_id
,tav.rco_id
FROM ota_activity_versions_vl tav, ota_activity_definitions tad
WHERE tav.activity_id =tad.activity_id
AND tav.activity_version_id between p_start_pkid and p_end_pkid ;
SELECT aci.CATEGORY_USAGE_ID
FROM ota_act_cat_inclusions aci ,
OTA_CATEGORY_USAGES TCU
WHERE ACI.CATEGORY_USAGE_ID = TCU.CATEGORY_USAGE_ID
AND TCU.TYPE = 'DM'
AND primary_flag = 'Y'
AND activity_version_id = p_act_ver_id;
SELECT aci.CATEGORY_USAGE_ID
FROM ota_act_cat_inclusions aci ,
OTA_CATEGORY_USAGES TCU
WHERE ACI.CATEGORY_USAGE_ID = TCU.CATEGORY_USAGE_ID
AND rownum = 1
AND TCU.TYPE = 'DM'
And Aci.activity_Version_id = p_act_ver_id;
SELECT language_id, decode(event_type,'SELFPACED','SELFPACED','SCHEDULED') event_type
FROM ota_events
WHERE activity_version_id = p_act_ver_id
AND parent_offering_id is NULL
Group by Language_id , decode(event_type,'SELFPACED','SELFPACED','SCHEDULED');
SELECT Oft.Offering_id FROM ota_offerings off, ota_offerings_tl oft
WHERE off.offering_id = oft.offering_id
AND oft.name = p_name
AND oft.language = USERENV('LANG')
AND off.activity_version_id = p_act_ver_id;
Select ocu.category_usage_id from
Ota_category_usages_vl ocu, hr_lookups lkp
Where Ocu.Category = Meaning
and lkp.Lookup_type = 'ACTIVITY_CATEGORY'
and lkp.lookup_code = 'INCLASS'
and ocu.type = 'DM'
and ocu.business_group_id = l_bg_id;
select category
from ota_category_usages
where type='DM'
and category_usage_id = l_dm_id;
select ctu.category_usage_id,
ctu.category
from ota_act_cat_inclusions aci ,
ota_category_usages_vl ctu
where aci.activity_version_id = p_tav_id
and ctu.category_usage_id = aci.category_usage_id
and ctu.type = 'DM';
select b.language_code LanguageCode
from (
SELECT fl.language_id, fnl.language_code FROM fnd_languages fl, fnd_natural_languages fnl
WHERE fl.iso_language_3 = UPPER(fnl.iso_language_3)
AND fl.iso_territory = fnl.iso_territory ) a,
ota_natural_languages_v b
where a.language_code(+) = b.language_code
and a.language_id = p_lang_id;
Select lng.Language_id into l_default_language
From Fnd_Languages lng
Where lng.Installed_Flag = 'B' ;
Select count(Resource_Usage_id) into l_res_usg_cnt
From Ota_Resource_Usages
Where Activity_Version_id = l_act_ver_id ;
Select count(Competence_element_id) into l_cmp_cnt
From Per_Competence_Elements
Where Type = 'TRAINER'
and Object_id = l_act_ver_id ;
select Max(offering_id)
-- into l_off_dup_lang
into l_parent_offering_id
from ota_offerings
where language_id = l_language_id
and activity_version_id =l_act_ver_id;
Insert into ota_offerings_tl
(offering_Id,
Language,
name,
Description,
Source_Lang,
Created_By,
Creation_Date,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login )
Select l_offering_id,
M.language,
decode(greatest(l_iteration,1) ,1,M.version_name,
get_offering_name_with_lang(M.version_name,l_language_id,M.language)),
M.description,
M.source_lang,
M.Created_By,
M.Creation_date,
M.Last_Updated_By,
M.Last_Update_Date,
M.Last_Update_Login
From Ota_activity_versions_tl M
Where M.activity_version_id = l_act_ver_id;
Insert into ota_resource_usages
(resource_usage_id
,supplied_resource_id
,activity_version_id
,object_version_number
,required_flag
,start_date
,comments
,end_date
,quantity
,resource_type
,role_to_play
,usage_reason
,rud_information_category
,rud_information1
,rud_information2
,rud_information3
,rud_information4
,rud_information5
,rud_information6
,rud_information7
,rud_information8
,rud_information9
,rud_information10
,rud_information11
,rud_information12
,rud_information13
,rud_information14
,rud_information15
,rud_information16
,rud_information17
,rud_information18
,rud_information19
,rud_information20
,offering_id )
Select ota_resource_usages_s.nextval
,res.supplied_resource_id
,l_act_ver_id
,1
,res.required_flag
,res.start_date
,res.comments
,res.end_date
,res.quantity
,res.resource_type
,res.role_to_play
,res.usage_reason
,res.rud_information_category
,res.rud_information1
,res.rud_information2
,res.rud_information3
,res.rud_information4
,res.rud_information5
,res.rud_information6
,res.rud_information7
,res.rud_information8
,res.rud_information9
,res.rud_information10
,res.rud_information11
,res.rud_information12
,res.rud_information13
,res.rud_information14
,res.rud_information15
,res.rud_information16
,res.rud_information17
,res.rud_information18
,res.rud_information19
,res.rud_information20
,l_offering_id
From ota_resource_usages res
where res.activity_version_id = l_act_ver_id
and Offering_id is NULL ;
,p_upgrade_id => p_update_id
,p_process_date => get_process_date(p_update_id,UPGRADE_NAME)
,p_log_type => LOG_TYPE_E
,p_upgrade_name => UPGRADE_NAME);
Insert into PER_COMPETENCE_ELEMENTS
(competence_element_id
,business_group_id
,object_version_number
,type
,competence_id
,member_competence_set_id
,proficiency_level_id
,effective_date_from
,effective_date_to
,object_id
,object_name)
SELECT per_competence_elements_s.nextval
,nvl(Ids.business_group_id,0)
,1
,'OTA_OFFERING'
,cmp.competence_id
,cmp.member_competence_set_id
,cmp.proficiency_level_id
,cmp.effective_date_from
,cmp.effective_date_to
,l_offering_id
,'OTA'
FROM PER_COMPETENCE_ELEMENTS cmp
WHERE cmp.type = 'TRAINER'
AND cmp.object_id = l_act_ver_id
AND not exists (select 1 from per_competence_elements pce
where pce.object_id = l_act_ver_id and type = 'OTA_OFFERING');
,p_upgrade_id => p_update_id
,p_process_date => get_process_date(p_update_id,UPGRADE_NAME)
,p_log_type => LOG_TYPE_E
,p_upgrade_name => UPGRADE_NAME);
Update Ota_events Evt
Set Evt.Parent_Offering_id = l_offering_id
Where Evt.activity_Version_id = Ids.Activity_Version_id
-- and Nvl(Evt.language_id,0) = Decode(Offering_RecSet(Evt_lang).language_id,NULL,0,l_Language_id)
and (
(l_evt_language_id is NULL and Evt.language_id is NULL)
OR
Evt.language_id = l_evt_language_id
)
-- and Nvl(Evt.language_id,0) = Decode(l_evt_language_id,NULL,Nvl(Evt.language_id,0),l_evt_language_id) commented for bug# 4139874
and Evt.business_group_id = Ids.Business_group_id
and Parent_Offering_id is NULL ;
,p_upgrade_id => p_update_id
,p_process_date => get_process_date(p_update_id,UPGRADE_NAME)
,p_log_type => LOG_TYPE_E
,p_upgrade_name => UPGRADE_NAME);
Update Ota_events Evt
Set Evt.Parent_Offering_id = l_parent_offering_id
Where Evt.activity_Version_id = Ids.Activity_Version_id
-- and Nvl(Evt.language_id,0) = Decode(l_evt_language_id,NULL,Nvl(Evt.language_id,0),l_evt_language_id) Commented for bug# 4139874
and (
(l_evt_language_id is NULL and Evt.language_id is NULL)
OR
Evt.language_id = l_evt_language_id
)
and Evt.business_group_id = Ids.Business_group_id
and Parent_offering_id is NULL ;
delete from ota_resource_usages
where activity_version_id = l_act_ver_id
and offering_id is null;
add_log_entry(p_upgrade_id=>p_update_id
,p_table_name =>substr('DM_DEL_INFO'||'|'||ids.activity_version_id,1,30)
,p_source_primary_key => csr_dm_list.category_usage_id
,p_object_value => ids.version_name
,p_message_text =>csr_dm_list.category
,p_process_date => get_process_date(p_update_id,UPGRADE_NAME)
,p_log_type => LOG_TYPE_I
,p_upgrade_name => UPGRADE_NAME);
delete from ota_act_cat_inclusions
where activity_version_id = l_act_ver_id
and category_usage_id in (select category_usage_id
from ota_category_usages
where type = 'DM');
delete from per_competence_elements
where object_id = l_act_ver_id
and type = 'TRAINER';
Select count(Activity_version_id) into l_rows_processed
from ota_activity_versions
Where activity_Version_id between p_start_pkid and p_end_pkid ;
p_update_id in number default 1 ) is
l_rows_processed number;
update OTA_EVENT_ASSOCIATIONS
set self_enrollment_flag = 'N'
where event_association_id between p_start_pkid and p_end_pkid
and self_enrollment_flag is null -- Bug#6804783
and ( customer_id is not null
or job_id is not null
or organization_id is not null
or position_id is not null);
select nvl(count(event_association_id),0)
into l_rows_processed
from OTA_EVENT_ASSOCIATIONS
where event_association_id between p_start_pkid and p_end_pkid ;
p_update_id in number default 1 ) is
l_rows_processed number := 0;
update OTA_EVENTS
set book_independent_flag = nvl(book_independent_flag,'N'),
Maximum_internal_attendees = Decode(Price_basis,'C',0,'O',0,Maximum_internal_attendees)
where event_id between p_start_pkid and p_end_pkid;
update OTA_EVENTS
set TIMEZONE = get_apps_timezone(timezone)
where offering_id is not null --iLearning imported events.
and event_id between p_start_pkid and p_end_pkid
and TIMEZONE is NOT NULL;
select nvl(count(event_id),0)
into l_rows_processed
from ota_events
where event_id between p_start_pkid and p_end_pkid ;
update ota_offerings a
set a.language_code=decode(a.language_code, null, (select fnl.language_code
FROM fnd_languages fl,
fnd_natural_languages fnl WHERE
fl.iso_language_3 = UPPER(fnl.iso_language_3) AND fl.iso_territory =
fnl.iso_territory and fl.language_id=a.language_id),a.language_code);
update ota_learning_objects a
set a.language_code=decode(a.language_code, null, (select fnl.language_code
FROM fnd_languages fl,
fnd_natural_languages fnl WHERE
fl.iso_language_3 = UPPER(fnl.iso_language_3) AND fl.iso_territory =
fnl.iso_territory and fl.language_id=a.language_id),a.language_code);
update ota_competence_languages a
set a.language_code=decode(a.language_code, null, (select fnl.language_code
FROM fnd_languages fl,
fnd_natural_languages fnl WHERE
fl.iso_language_3 = UPPER(fnl.iso_language_3) AND fl.iso_territory =
fnl.iso_territory and fl.language_id=a.language_id),a.language_code);
select 1
from ota_offerings
where language_code is null;
select 1
from ota_learning_objects
where language_code is null;
select 1
from ota_competence_languages
where language_code is null;
,p_object_value => 'No Language code updated for OTA_OFFERINGS'
,p_message_text => l_err_msg
,p_upgrade_id => l_upg_id
,p_process_date => get_process_date(l_upg_id,'UPG_LANGUAGE_CODE')
,p_log_type => LOG_TYPE_E
,p_upgrade_name => 'UPG_LANGUAGE_CODE');
,p_object_value => 'No Language code updated for OTA_LEARNING_OBJETCS'
,p_message_text => l_err_msg
,p_upgrade_id => l_upg_id
,p_process_date => get_process_date(l_upg_id,'UPG_LANGUAGE_CODE')
,p_log_type => LOG_TYPE_E
,p_upgrade_name => 'UPG_LANGUAGE_CODE');
,p_object_value => 'No Language code updated for OTA_COMPETENCE_LANGUAGES'
,p_message_text => l_err_msg
,p_upgrade_id => l_upg_id
,p_process_date => get_process_date(l_upg_id,'UPG_LANGUAGE_CODE')
,p_log_type => LOG_TYPE_E
,p_upgrade_name => 'UPG_LANGUAGE_CODE');
Insert into ota_upgrade_log(upgrade_id,table_name,business_group_id,source_primary_key,object_value,message_text,process_date,log_type,upgrade_name)
Values(p_upgrade_id,p_table_name,p_business_group_id,p_source_primary_key,p_object_value,p_message_text,p_process_date,p_log_type,p_upgrade_name) ;
Select Nvl(Max(source_primary_key),0)+1 into l_upgrade_id
From Ota_Upgrade_Log
Where Table_name = 'OTA_UPGRADE_LOG' ;
Insert into ota_upgrade_log(upgrade_id,table_name,business_group_id,source_primary_key,object_value,message_text,process_date,Target_primary_key,log_type,upgrade_name)
Values(p_upgrade_id,'OTA_UPGRADE_LOG',p_business_group_id,l_upgrade_id,p_table_name,p_message_text,p_process_date,p_source_primary_key,p_log_type,p_upgrade_name) ;
Procedure Migrate_Lookup(p_update_id in number default 1 ) is
l_err_code varchar2(72);
Insert into FND_LOOKUP_VALUES
(LOOKUP_TYPE,LANGUAGE,LOOKUP_CODE,MEANING,DESCRIPTION,ENABLED_FLAG,START_DATE_ACTIVE,
END_DATE_ACTIVE,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,SOURCE_LANG,
SECURITY_GROUP_ID,VIEW_APPLICATION_ID,TERRITORY_CODE,ATTRIBUTE_CATEGORY,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,
ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,
ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15,TAG)
Select 'OTA_DURATION_UNITS',lkp.LANGUAGE,lkp.LOOKUP_CODE,lkp.MEANING,lkp.DESCRIPTION,lkp.ENABLED_FLAG,lkp.START_DATE_ACTIVE,
lkp.END_DATE_ACTIVE,lkp.CREATED_BY,lkp.CREATION_DATE,lkp.LAST_UPDATED_BY,lkp.LAST_UPDATE_LOGIN,lkp.LAST_UPDATE_DATE,lkp.SOURCE_LANG,
lkp.SECURITY_GROUP_ID,lkp.VIEW_APPLICATION_ID,lkp.TERRITORY_CODE,lkp.ATTRIBUTE_CATEGORY,lkp.ATTRIBUTE1,lkp.ATTRIBUTE2,lkp.ATTRIBUTE3,
lkp.ATTRIBUTE4,lkp.ATTRIBUTE5,lkp.ATTRIBUTE6,lkp.ATTRIBUTE7,lkp.ATTRIBUTE8,lkp.ATTRIBUTE9,lkp.ATTRIBUTE10,lkp.ATTRIBUTE11,lkp.ATTRIBUTE12,
lkp.ATTRIBUTE13,lkp.ATTRIBUTE14,lkp.ATTRIBUTE15,TAG
From Fnd_Lookup_values lkp
Where lkp.Lookup_type = 'FREQUENCY'
and lkp.created_by not in (1,2)
and (lkp.Lookup_code,lkp.language)
not in (Select flk.Lookup_code,flk.language from Fnd_lookup_values flk
Where flk.Lookup_type = 'OTA_DURATION_UNITS') ;
,p_upgrade_id => p_update_id
,p_process_date => get_process_date(p_update_id,UPGRADE_NAME)
,p_log_type => LOG_TYPE_E
,p_upgrade_name => UPGRADE_NAME);
Procedure create_ctg_dm_for_act_bg(p_update_id in number default 1 ) is
Cursor Csr_Activity_Bg is
Select tad.business_group_id
From Ota_Activity_definitions tad
Group by tad.business_group_id ;
Select hou.name Bg_name
From hr_organization_units hou
Where hou.business_group_id = p_bg_id
and hou.business_group_id = hou.organization_id ;
select flt.description
from fnd_languages fl,
fnd_languages_tl flt
where flt.language_code = fl.language_code
and flt.language = p_language
and fl.language_id = p_language_id ;
select nvl(min(start_date_active),trunc(sysdate))
from ota_category_usages
where parent_cat_usage_id = parent_ctg
and type = 'C';
select category_usage_id,start_date_active
from ota_category_usages
where type = 'C'
and parent_cat_usage_id is null;
update ota_category_usages
set start_date_active = l_min_start_date
where category_usage_id = c_roots.category_usage_id;
select fpi.status
from fnd_product_installations fpi
where fpi.application_id = OTA_APPLICATION_ID;
select frp.application_id, frp.responsibility_id
from fnd_responsibility frp
where frp.responsibility_key='SYSTEM_ADMINISTRATOR';
select fu.user_id
from fnd_user fu
where fu.user_name = 'SYSADMIN';
** The update is required so submit a request for the SYSADMIN user using
** the System Administrator responsibility.
*/
/* Get the required IDs...
*/
open csr_ota_installed;
select fpi.status
from fnd_product_installations fpi
where fpi.application_id = OTA_APPLICATION_ID;
is Select fcu.*,fvs.flex_value_set_name
From Fnd_Descr_Flex_Col_Usage_Vl fcu, fnd_flex_value_sets fvs
Where fcu.Application_id = 810
and fcu.Descriptive_FlexField_Name = 'OTA_ACTIVITY_DEFINITIONS'
and fcu.Descriptive_Flex_Context_code = p_context_code
and fcu.flex_value_set_id = fvs.flex_value_set_id(+)
and Not exists (SELECT 'Y'
From Fnd_Descr_Flex_Col_Usage_Vl cat_fcu
Where cat_fcu.Application_id = fcu.application_id
and cat_fcu.Descriptive_FlexField_Name = 'OTA_CATEGORY_USAGES'
and cat_fcu.Descriptive_Flex_Context_code = fcu.Descriptive_Flex_Context_code
and cat_fcu.end_user_column_name = fcu.end_user_column_name );
Cursor Csr_DFF_contexts is Select * from FND_DESCR_FLEX_CONTEXTS_vl
Where Application_id = 810
and Descriptive_FLexfield_Name = 'OTA_ACTIVITY_DEFINITIONS'
and Enabled_Flag = 'Y';
Cursor Csr_Segment_exists(p_context_name in varchar2) is SELECT 'Y'
FROM fnd_descriptive_flexs
WHERE application_id = 810
AND descriptive_flexfield_name = p_context_name ;
Select Trunc(sysdate) into l_date from dual ;
Select Max('Y') into l_context_exists
From FND_DESCR_FLEX_CONTEXTS_vl
Where Descriptive_Flexfield_name = 'OTA_CATEGORY_USAGES'
and Descriptive_Flex_COntext_Code = dff_context.Descriptive_Flex_Context_code ;
p_update_id in number default 1 ) is
l_rows_processed number := 0;
SELECT Booking_id, event_id,content_player_status,business_group_id,object_version_number,
delegate_person_id,
contact_id,
customer_id ,
delegate_contact_id ,
organization_id,
sponsor_person_id,
sponsor_assignment_id,
delegate_assignment_id ,
is_history_flag,
booking_status_type_id
FROM ota_delegate_bookings
WHERE BOOKING_STATUS_TYPE_ID in
(select booking_status_type_id from ota_booking_status_types
where type ='P')
AND RowID Between p_start_rowid and p_end_rowid ;
SELECT off.learning_object_id
FROM ota_events evt, ota_offerings off
WHERE evt.event_id = l_event_id
and evt.parent_offering_id = off.offering_id;
SELECT lesson_status
FROM ota_performances
WHERE learning_object_id = p_lo_id
AND user_id = p_user_id
AND lesson_status IN ('P', 'C');
SELECT booking_status_type_id
FROM ota_booking_status_types
WHERE type ='A'
AND business_group_id = l_business_group_id
ORDER BY Nvl(Default_flag,'N') Desc;
UPDATE /*+ ROWID (TDB) */ OTA_DELEGATE_BOOKINGS TDB
SET IS_HISTORY_FLAG = Decode(Is_History_flag,NULL,'Y',Is_History_Flag),
successful_attendance_flag = Decode(successful_attendance_flag,NULL,'Y',successful_attendance_flag)
WHERE BOOKING_STATUS_TYPE_ID in
(select booking_status_type_id from ota_booking_status_types
where type ='A')
AND RowID Between p_start_rowid and p_end_rowid ;
ota_tdb_api_upd2.Update_Enrollment
(
p_booking_id => l_c_booking_id.booking_id,
p_delegate_person_id => l_c_booking_id.delegate_person_id,
p_contact_id => l_c_booking_id.contact_id,
p_customer_id => l_c_booking_id.customer_id,
p_booking_status_type_id => l_booking_status_type_id,
p_business_group_id => l_c_booking_id.business_group_id,
p_event_id => l_event_id,
p_object_version_number => l_c_booking_id.object_version_number,
p_date_status_changed => sysdate,
p_successful_attendance_flag => 'Y',
p_tfl_object_version_number => l_tfl_ovn,
p_finance_line_id => l_fin_line,
p_source_cancel => NULL,
p_override_learner_access => 'Y',
p_organization_id => l_c_booking_id.organization_id ,
p_sponsor_person_id => l_c_booking_id.sponsor_person_id,
p_sponsor_assignment_id => l_c_booking_id.sponsor_assignment_id
) ;
,p_message_text => 'Enrollment status updated successfully from '|| l_c_booking_id.booking_status_type_id || ' to ' || l_booking_status_type_id
,p_upgrade_id => p_update_id
,p_process_date => get_process_date(p_update_id,UPGRADE_NAME)
,p_log_type => LOG_TYPE_I
,p_upgrade_name => UPGRADE_NAME );
,p_upgrade_id => p_update_id
,p_process_date => get_process_date(p_update_id,UPGRADE_NAME)
,p_log_type => LOG_TYPE_E
,p_upgrade_name => UPGRADE_NAME );
,p_upgrade_id => p_update_id
,p_process_date => get_process_date(p_update_id,UPGRADE_NAME)
,p_log_type => LOG_TYPE_E
,p_upgrade_name => UPGRADE_NAME );
p_update_id in number default 1 ) is
l_rows_processed number := 0;
UPDATE /*+ ROWID (LPE) */ OTA_LP_ENROLLMENTS LPE
SET IS_HISTORY_FLAG = 'Y'
WHERE ROWID between p_start_rowid and p_end_rowid
AND PATH_STATUS_CODE = 'COMPLETED'
AND COMPLETION_DATE IS NOT NULL
AND IS_HISTORY_FLAG IS NULL ;
,p_upgrade_id => p_update_id
,p_process_date => get_process_date(p_update_id,UPGRADE_NAME)
,p_log_type => LOG_TYPE_E
,p_upgrade_name => UPGRADE_NAME );