[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT min(date_status_changed)
FROM ota_events evt,
ota_delegate_bookings tdb,
ota_booking_status_types bst
WHERE evt.activity_version_id=p_activity_version_id
AND evt.event_id = tdb.event_id
AND bst.booking_status_type_id = tdb.booking_status_type_id
AND (tdb.delegate_person_id = p_person_id or tdb.delegate_person_id = p_contact_id)
AND bst.type = 'A';
in varchar2, p_destination_field in varchar2,p_update_id in number) is
l_date date;
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 = p_source_table
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 = p_destination_table
and cat_fcu.Descriptive_Flex_Context_code = fcu.Descriptive_Flex_Context_code
and cat_fcu.end_user_column_name = fcu.end_user_column_name );
Select *
from FND_DESCR_FLEX_CONTEXTS_vl
Where Application_id = 810
and Descriptive_FLexfield_Name = p_source_table
and Enabled_Flag = 'Y';
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 = p_destination_table
and Descriptive_Flex_COntext_Code = dff_context.Descriptive_Flex_Context_code ;
,p_upgrade_id => p_update_id
,p_process_date => ota_classic_upgrade.get_process_date(P_UPDATE_ID,LP_UPGRADE_NAME)--trunc(sysdate)
,p_log_type => LP_LOG_TYPE_E
,p_upgrade_name => LP_UPGRADE_NAME );
,p_upgrade_id => p_update_id
,p_process_date => ota_classic_upgrade.get_process_date(P_UPDATE_ID,LP_UPGRADE_NAME)--trunc(sysdate)
,p_log_type => LP_LOG_TYPE_E
,p_upgrade_name => LP_UPGRADE_NAME );
select target_primary_key
from ota_upgrade_log
where table_name = LP_MAP_TABLE_NAME_E
-- and upgrade_id = LP_MAP_UPGRADE_ID
and source_primary_key = p_concat;
select nvl(target_primary_key,OTA_MIG_FAIL_ID)
into l_ret
from ota_upgrade_log
where table_name = LP_MAP_TABLE_NAME_E
-- and upgrade_id = LP_MAP_UPGRADE_ID
and source_primary_key = l_concat;
select target_primary_key
from ota_upgrade_log
where table_name = LP_MAP_TABLE_NAME_M
-- and upgrade_id = LP_MAP_UPGRADE_ID
and source_primary_key = p_concat;
select nvl(target_primary_key,OTA_MIG_FAIL_ID)
into l_ret
from ota_upgrade_log
where table_name = LP_MAP_TABLE_NAME_M
-- and upgrade_id = LP_MAP_UPGRADE_ID
and source_primary_key = l_concat;
select target_primary_key
from ota_upgrade_log
where table_name = LP_MAP_TABLE_NAME_M_E
and source_primary_key = p_training_plan_member_id;
select nvl(target_primary_key,OTA_MIG_FAIL_ID)
into l_ret
from ota_upgrade_log
where table_name = LP_MAP_TABLE_NAME_M_E
-- and upgrade_id = LP_MAP_UPGRADE_ID
and source_primary_key = p_training_plan_member_id;
select 1
from ota_upgrade_log
where table_name = LP_MAP_TABLE_NAME_E
-- and upgrade_id = LP_MAP_UPGRADE_ID
and source_primary_key = p_concat;
update ota_upgrade_log
set target_primary_key = p_new_lp_enr_id
where table_name = LP_MAP_TABLE_NAME_E
-- and upgrade_id = LP_MAP_UPGRADE_ID
and source_primary_key = l_concat;
insert into ota_upgrade_log(upgrade_id,table_name,source_primary_key,target_primary_key
,log_type,upgrade_name)
values (p_upgrade_id,LP_MAP_TABLE_NAME_E,l_concat,p_new_lp_enr_id
,LP_LOG_TYPE_N,LP_UPGRADE_NAME);
select 1
from ota_upgrade_log
where table_name = LP_MAP_TABLE_NAME_M
-- and upgrade_id = LP_MAP_UPGRADE_ID
and source_primary_key = l_concat;
update ota_upgrade_log
set target_primary_key = p_new_lp_mem_id
where table_name = LP_MAP_TABLE_NAME_M
-- and upgrade_id = LP_MAP_UPGRADE_ID
and source_primary_key = l_concat;
insert into ota_upgrade_log(upgrade_id,table_name,source_primary_key,target_primary_key
,log_type,upgrade_name)
values (p_upgrade_id,LP_MAP_TABLE_NAME_M,l_concat,p_new_lp_mem_id,LP_LOG_TYPE_N,LP_UPGRADE_NAME);
select 1
from ota_upgrade_log
where table_name = LP_MAP_TABLE_NAME_M_E
-- and upgrade_id = LP_MAP_UPGRADE_ID
and source_primary_key = p_training_plan_member_id;
update ota_upgrade_log
set target_primary_key = p_new_lp_mem_enr_id
where table_name = LP_MAP_TABLE_NAME_M_E
-- and upgrade_id = LP_MAP_UPGRADE_ID
and source_primary_key = p_training_plan_member_id;
insert into ota_upgrade_log(upgrade_id,table_name,source_primary_key,target_primary_key
,log_type,upgrade_name)
values (p_upgrade_id,LP_MAP_TABLE_NAME_M_E,p_training_plan_member_id,p_new_lp_mem_enr_id
,LP_LOG_TYPE_N,LP_UPGRADE_NAME);
UPDATE ota_learning_paths
SET path_source_code ='CATALOG'
,display_to_learner_flag = 'Y'
WHERE path_source_code IS null;
UPDATE ota_learning_paths lps
SET public_flag = (SELECT decode(count(tea.learning_path_id),0,'Y','N')
FROM ota_event_associations tea
WHERE lps.learning_path_id = tea.learning_path_id)
WHERE lps.public_flag IS NULL
AND lps.path_source_code = 'CATALOG';
SELECT lps.learning_path_id
FROM ota_learning_paths lps, ota_learning_paths_tl lpst
where lps.learning_path_id = lpst.learning_path_id
and lpst.language = userenv('LANG')
and lpst.name = p_name
and lps.business_group_id = p_business_group_id
and (lps.person_id = p_person_id OR lps.contact_id = p_contact_id);
select lpc.learning_path_section_id
from ota_lp_sections_tl lpct,ota_lp_sections lpc
where lpc.learning_path_section_id = lpct.learning_path_section_id
and lpct.language=userenv('LANG')
and lpc.learning_path_id = p_learning_path_id
and lpct.name = p_section_name ;
p_update_id IN number default 1 --CONC_UPGRADE_ID
) IS
/*
Upgrade existing catalog learning paths to have sections
*/
l_learning_path_id number;
SELECT lps.learning_path_id, lpst.name , lps.business_group_id
FROM ota_learning_paths lps, ota_learning_paths_tl lpst
WHERE lpst.learning_path_id = lps.learning_path_id
AND lpst.language = USERENV('LANG')
AND lps.path_source_code = 'CATALOG'
AND lps.learning_path_id between p_start_pkid and p_end_pkid;
select lpc.learning_path_section_id
from ota_lp_sections_tl lpct,ota_lp_sections lpc
where lpc.learning_path_section_id = lpct.learning_path_section_id
and lpct.language=userenv('LANG')
and lpc.learning_path_id = p_learning_path_id
and lpct.name = p_section_name ;
select max(upgrade_id) INTO l_upgrade_id
from ota_upgrade_log
where upgrade_name = LP_UPGRADE_NAME;
,p_process_date => ota_classic_upgrade.get_process_date(P_UPDATE_ID,LP_UPGRADE_NAME)--trunc(sysdate)
,p_log_type => LP_LOG_TYPE_N
,p_upgrade_name => LP_UPGRADE_NAME );
Insert into ota_lp_sections_tl
(learning_path_section_id,
Language,
name,
Description,
Source_Lang,
Created_By,
Creation_Date,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login )
Select l_learning_path_section_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_learning_paths_tl M
Where M.learning_path_id = l_learning_path_id;
,p_upgrade_id => p_update_id
,p_process_date => ota_classic_upgrade.get_process_date(P_UPDATE_ID,LP_UPGRADE_NAME)--trunc(sysdate)
,p_log_type => LP_LOG_TYPE_E
,p_upgrade_name => LP_UPGRADE_NAME );--ota_classic_upgrade.get_process_date());
UPDATE ota_learning_path_members
SET learning_path_section_id = l_learning_path_section_id --//Section_id returned from create call
where learning_path_id = l_learning_path_id;
Select nvl(count(1),0)
into l_rows_processed
from ota_learning_paths
where learning_path_id between p_start_pkid and p_end_pkid;
p_update_id in number default 1
) is
l_training_plan_id number;
SELECT tps.training_plan_id, tps.business_group_id,
tps.name, tps.description,
tps.START_DATE, tps.end_date,
tps.plan_source, tps.plan_status_type_id
-- Source_Function_Code,
, tps.person_id, tps.contact_id
,tps.ATTRIBUTE_CATEGORY,tps.ATTRIBUTE1
,tps.ATTRIBUTE2 ,tps.ATTRIBUTE3
,tps.ATTRIBUTE4 ,tps.ATTRIBUTE5
,tps.ATTRIBUTE6 ,tps.ATTRIBUTE7
,tps.ATTRIBUTE8 ,tps.ATTRIBUTE9
,tps.ATTRIBUTE10 ,tps.ATTRIBUTE11
,tps.ATTRIBUTE12 ,tps.ATTRIBUTE13
,tps.ATTRIBUTE14 ,tps.ATTRIBUTE15
,tps.ATTRIBUTE16 ,tps.ATTRIBUTE17
,tps.ATTRIBUTE18 ,tps.ATTRIBUTE19
,tps.ATTRIBUTE20 ,tps.ATTRIBUTE21
,tps.ATTRIBUTE22 ,tps.ATTRIBUTE23
,tps.ATTRIBUTE24 ,tps.ATTRIBUTE25
,tps.ATTRIBUTE26 ,tps.ATTRIBUTE27
,tps.ATTRIBUTE28 ,tps.ATTRIBUTE29
,tps.ATTRIBUTE30 ,tps.creator_person_id
FROM ota_training_plans tps
WHERE tps.plan_source NOT IN ('TALENT_MGMT','CATALOG')
AND (tps.PERSON_ID is not NULL OR contact_id is not null)
AND tps.learning_path_id is null
AND tps.training_plan_id between p_start_pkid AND p_end_pkid;
SELECT tpm.training_plan_member_id, tpm.business_group_id
,tpm.activity_version_id
,tpm.member_status_type_id, tpm.target_completion_date
,tpm.ATTRIBUTE_CATEGORY ,tpm.ATTRIBUTE1
,tpm.ATTRIBUTE2 ,tpm.ATTRIBUTE3
,tpm.ATTRIBUTE4 ,tpm.ATTRIBUTE5
,tpm.ATTRIBUTE6 ,tpm.ATTRIBUTE7
,tpm.ATTRIBUTE8 ,tpm.ATTRIBUTE9
,tpm.ATTRIBUTE10 ,tpm.ATTRIBUTE11
,tpm.ATTRIBUTE12 ,tpm.ATTRIBUTE13
,tpm.ATTRIBUTE14 ,tpm.ATTRIBUTE15
,tpm.ATTRIBUTE16 ,tpm.ATTRIBUTE17
,tpm.ATTRIBUTE18 ,tpm.ATTRIBUTE19
,tpm.ATTRIBUTE20 ,tpm.ATTRIBUTE21
,tpm.ATTRIBUTE22 ,tpm.ATTRIBUTE23
,tpm.ATTRIBUTE24 ,tpm.ATTRIBUTE25
,tpm.ATTRIBUTE26 ,tpm.ATTRIBUTE27
,tpm.ATTRIBUTE28 ,tpm.ATTRIBUTE29
,tpm.ATTRIBUTE30
FROM ota_training_plan_members tpm
WHERE tpm.training_plan_id = p_training_plan_id;
,p_upgrade_id => p_update_id
,p_learning_path_id => l_learning_path_id
,p_object_version_number => l_object_version_number
,p_source_function_code => null
,p_assignment_id => null
,p_source_id => null
,p_display_to_learner_flag => 'Y'
,p_training_plan_id => tp_rec.training_plan_id);
,p_upgrade_id => p_update_id
,p_learning_path_id => l_learning_path_id
,p_learning_path_section_id => l_learning_path_section_id
,p_object_version_number => l_object_version_number);
,p_upgrade_id => p_update_id
,p_training_plan_id => tp_rec.training_plan_id
,p_attribute_category => tp_rec.attribute_category
,p_attribute1 => tp_rec.attribute1
,p_attribute2 => tp_rec.attribute2
,p_attribute3 => tp_rec.attribute3
,p_attribute4 => tp_rec.attribute4
,p_attribute5 => tp_rec.attribute5
,p_attribute6 => tp_rec.attribute6
,p_attribute7 => tp_rec.attribute7
,p_attribute8 => tp_rec.attribute8
,p_attribute9 => tp_rec.attribute9
,p_attribute10 => tp_rec.attribute10
,p_attribute11 => tp_rec.attribute11
,p_attribute12 => tp_rec.attribute12
,p_attribute13 => tp_rec.attribute13
,p_attribute14 => tp_rec.attribute14
,p_attribute15 => tp_rec.attribute15
,p_attribute16 => tp_rec.attribute16
,p_attribute17 => tp_rec.attribute17
,p_attribute18 => tp_rec.attribute18
,p_attribute19 => tp_rec.attribute19
,p_attribute20 => tp_rec.attribute20
,p_attribute21 => tp_rec.attribute21
,p_attribute22 => tp_rec.attribute22
,p_attribute23 => tp_rec.attribute23
,p_attribute24 => tp_rec.attribute24
,p_attribute25 => tp_rec.attribute25
,p_attribute26 => tp_rec.attribute26
,p_attribute27 => tp_rec.attribute27
,p_attribute28 => tp_rec.attribute28
,p_attribute29 => tp_rec.attribute29
,p_attribute30 => tp_rec.attribute30
);
,p_upgrade_id => p_update_id
,p_training_plan_member_id => tpm_rec.training_plan_member_id
,p_training_plan_id => tp_rec.training_plan_id);
,p_upgrade_id => p_update_id
,p_object_version_number => l_object_version_number
,p_training_plan_member_id => tpm_rec.training_plan_member_id
,p_attribute_category => tpm_rec.attribute_category
,p_attribute1 => tp_rec.attribute1
,p_attribute2 => tpm_rec.attribute2
,p_attribute3 => tpm_rec.attribute3
,p_attribute4 => tpm_rec.attribute4
,p_attribute5 => tpm_rec.attribute5
,p_attribute6 => tpm_rec.attribute6
,p_attribute7 => tpm_rec.attribute7
,p_attribute8 => tpm_rec.attribute8
,p_attribute9 => tpm_rec.attribute9
,p_attribute10 => tpm_rec.attribute10
,p_attribute11 => tpm_rec.attribute11
,p_attribute12 => tpm_rec.attribute12
,p_attribute13 => tpm_rec.attribute13
,p_attribute14 => tpm_rec.attribute14
,p_attribute15 => tpm_rec.attribute15
,p_attribute16 => tpm_rec.attribute16
,p_attribute17 => tpm_rec.attribute17
,p_attribute18 => tpm_rec.attribute18
,p_attribute19 => tpm_rec.attribute19
,p_attribute20 => tpm_rec.attribute20
,p_attribute21 => tpm_rec.attribute21
,p_attribute22 => tpm_rec.attribute22
,p_attribute23 => tpm_rec.attribute23
,p_attribute24 => tpm_rec.attribute24
,p_attribute25 => tpm_rec.attribute25
,p_attribute26 => tpm_rec.attribute26
,p_attribute27 => tpm_rec.attribute27
,p_attribute28 => tpm_rec.attribute28
,p_attribute29 => tpm_rec.attribute29
,p_attribute30 => tpm_rec.attribute30);
Select nvl(count(1),0)
into l_rows_processed
from ota_training_plans
where training_plan_id between p_start_pkid and p_end_pkid;
p_update_id in number default 1 --CONC_UPGRADE_ID
) is
l_training_plan_id number;
SELECT distinct Tpm.source_id,Tpm.source_function, Tpm.assignment_id, tps.person_id
FROM ota_training_plans tps,Ota_training_plan_members tpm
WHERE PLAN_SOURCE = 'TALENT_MGMT'
AND tpm.training_plan_id = tps.training_plan_id
AND tps.PERSON_ID is not NULL;
SELECT tps.training_plan_id,
Tps.name,
Tps.description,
Tps.business_group_id,
Tps.start_date,
Tps.end_date,
Tps.Plan_status_type_id,
Tps.creator_person_id,
Tps.plan_source,
Tpm.training_plan_member_id,
Tpm.activity_version_id,
Tpm.target_completion_date,
Tpm.cancellation_reason,
Tpm.member_status_type_id,
Tps.person_id,
Tps.contact_id,
Tpm.assignment_id,
Tpm.source_id,
Tpm.source_function,
Tpm.creator_person_id tpm_creator
,tpm.ATTRIBUTE_CATEGORY tpm_ATTRIBUTE_CATEGORY
,tpm.ATTRIBUTE1 tpm_ATTRIBUTE1
,tpm.ATTRIBUTE2 tpm_ATTRIBUTE2
,tpm.ATTRIBUTE3 tpm_ATTRIBUTE3
,tpm.ATTRIBUTE4 tpm_ATTRIBUTE4
,tpm.ATTRIBUTE5 tpm_ATTRIBUTE5
,tpm.ATTRIBUTE6 tpm_ATTRIBUTE6
,tpm.ATTRIBUTE7 tpm_ATTRIBUTE7
,tpm.ATTRIBUTE8 tpm_ATTRIBUTE8
,tpm.ATTRIBUTE9 tpm_ATTRIBUTE9
,tpm.ATTRIBUTE10 tpm_ATTRIBUTE10
,tpm.ATTRIBUTE11 tpm_ATTRIBUTE11
,tpm.ATTRIBUTE12 tpm_ATTRIBUTE12
,tpm.ATTRIBUTE13 tpm_ATTRIBUTE13
,tpm.ATTRIBUTE14 tpm_ATTRIBUTE14
,tpm.ATTRIBUTE15 tpm_ATTRIBUTE15
,tpm.ATTRIBUTE16 tpm_ATTRIBUTE16
,tpm.ATTRIBUTE17 tpm_ATTRIBUTE17
,tpm.ATTRIBUTE18 tpm_ATTRIBUTE18
,tpm.ATTRIBUTE19 tpm_ATTRIBUTE19
,tpm.ATTRIBUTE20 tpm_ATTRIBUTE20
,tpm.ATTRIBUTE21 tpm_ATTRIBUTE21
,tpm.ATTRIBUTE22 tpm_ATTRIBUTE22
,tpm.ATTRIBUTE23 tpm_ATTRIBUTE23
,tpm.ATTRIBUTE24 tpm_ATTRIBUTE24
,tpm.ATTRIBUTE25 tpm_ATTRIBUTE25
,tpm.ATTRIBUTE26 tpm_ATTRIBUTE26
,tpm.ATTRIBUTE27 tpm_ATTRIBUTE27
,tpm.ATTRIBUTE28 tpm_ATTRIBUTE28
,tpm.ATTRIBUTE29 tpm_ATTRIBUTE29
,tpm.ATTRIBUTE30 tpm_ATTRIBUTE30
,tps.ATTRIBUTE_CATEGORY tps_ATTRIBUTE_CATEGORY
,tps.ATTRIBUTE1 tps_attribute1
,tps.ATTRIBUTE2 tps_attribute2
,tps.ATTRIBUTE3 tps_attribute3
,tps.ATTRIBUTE4 tps_attribute4
,tps.ATTRIBUTE5 tps_attribute5
,tps.ATTRIBUTE6 tps_attribute6
,tps.ATTRIBUTE7 tps_attribute7
,tps.ATTRIBUTE8 tps_attribute8
,tps.ATTRIBUTE9 tps_attribute9
,tps.attribute10 tps_attribute10
,tps.attribute11 tps_attribute11
,tps.attribute12 tps_attribute12
,tps.attribute13 tps_attribute13
,tps.ATTRIBUTE14 tps_attribute14
,tps.ATTRIBUTE15 tps_attribute15
,tps.ATTRIBUTE16 tps_attribute16
,tps.ATTRIBUTE17 tps_attribute17
,tps.ATTRIBUTE18 tps_attribute18
,tps.ATTRIBUTE19 tps_attribute19
,tps.ATTRIBUTE20 tps_attribute20
,tps.ATTRIBUTE21 tps_attribute21
,tps.ATTRIBUTE22 tps_attribute22
,tps.ATTRIBUTE23 tps_attribute23
,tps.ATTRIBUTE24 tps_attribute24
,tps.ATTRIBUTE25 tps_attribute25
,tps.ATTRIBUTE26 tps_attribute26
,tps.ATTRIBUTE27 tps_attribute27
,tps.ATTRIBUTE28 tps_attribute28
,tps.ATTRIBUTE29 tps_attribute29
,tps.ATTRIBUTE30 tps_ATTRIBUTE30
FROM ota_training_plans tps, Ota_training_plan_members tpm
WHERE tps.PLAN_SOURCE = 'TALENT_MGMT'
AND tps.PERSON_ID = p_person_id
--AND PERSON_ID is not NULL
AND tpm.training_plan_id = tps.training_plan_id
AND tpm.source_function = p_source_function
AND (tpm.source_id is null or tpm.source_id = p_source_id)
AND (tpm.assignment_id is null or tpm.assignment_id = p_assignment_id)
order by tpm.training_plan_member_id ;
SELECT decode(member_status_type_id, 'OTA_PLANNED', 0,'ACTIVE', 0, 'OTA_AWAITING_APPROVAL',0, 'OTA_COMPLETED',1,
'CANCELLED',2) top_status
FROM Ota_training_plan_members tpm, ota_training_plans tps
WHERE tps.training_plan_id = tpm.training_plan_id
AND tpm.source_function = p_source_function
AND (tpm.source_id is null or tpm.source_id = p_source_id)
AND (tpm.assignment_id is null or tpm.assignment_id = p_assignment_id)
AND tps.person_id = p_person_id
order by top_status;
,p_upgrade_id => p_update_id
,p_learning_path_id => l_learning_path_id
,p_object_version_number => l_object_version_number
,p_source_function_code => talent_mgmt_comb.source_function
,p_assignment_id => talent_mgmt_comb.assignment_id
,p_source_id => talent_mgmt_comb.source_id
,p_display_to_learner_flag =>l_display_to_learner_flag
,p_training_plan_id => talent_mgmt_rec.training_plan_id
);
,p_upgrade_id => p_update_id
,p_learning_path_id => l_learning_path_id
,p_learning_path_section_id => l_learning_path_section_id
,p_object_version_number => l_object_version_number);
,p_upgrade_id => p_update_id
,p_training_plan_id => talent_mgmt_rec.training_plan_id
,p_source_id => talent_mgmt_comb.source_id
,p_source_function => talent_mgmt_comb.source_function
,p_assignment_id => talent_mgmt_comb.assignment_id
,p_attribute_category => talent_mgmt_rec.tps_attribute_category
,p_attribute1 => talent_mgmt_rec.tps_attribute1
,p_attribute2 => talent_mgmt_rec.tps_attribute2
,p_attribute3 => talent_mgmt_rec.tps_attribute3
,p_attribute4 => talent_mgmt_rec.tps_attribute4
,p_attribute5 => talent_mgmt_rec.tps_attribute5
,p_attribute6 => talent_mgmt_rec.tps_attribute6
,p_attribute7 => talent_mgmt_rec.tps_attribute7
,p_attribute8 => talent_mgmt_rec.tps_attribute8
,p_attribute9 => talent_mgmt_rec.tps_attribute9
,p_attribute10 => talent_mgmt_rec.tps_attribute10
,p_attribute11 => talent_mgmt_rec.tps_attribute11
,p_attribute12 => talent_mgmt_rec.tps_attribute12
,p_attribute13 => talent_mgmt_rec.tps_attribute13
,p_attribute14 => talent_mgmt_rec.tps_attribute14
,p_attribute15 => talent_mgmt_rec.tps_attribute15
,p_attribute16 => talent_mgmt_rec.tps_attribute16
,p_attribute17 => talent_mgmt_rec.tps_attribute17
,p_attribute18 => talent_mgmt_rec.tps_attribute18
,p_attribute19 => talent_mgmt_rec.tps_attribute19
,p_attribute20 => talent_mgmt_rec.tps_attribute20
,p_attribute21 => talent_mgmt_rec.tps_attribute21
,p_attribute22 => talent_mgmt_rec.tps_attribute22
,p_attribute23 => talent_mgmt_rec.tps_attribute23
,p_attribute24 => talent_mgmt_rec.tps_attribute24
,p_attribute25 => talent_mgmt_rec.tps_attribute25
,p_attribute26 => talent_mgmt_rec.tps_attribute26
,p_attribute27 => talent_mgmt_rec.tps_attribute27
,p_attribute28 => talent_mgmt_rec.tps_attribute28
,p_attribute29 => talent_mgmt_rec.tps_attribute29
,p_attribute30 => talent_mgmt_rec.tps_attribute30
);
,p_upgrade_id => p_update_id
,p_training_plan_member_id => talent_mgmt_rec.training_plan_member_id
,p_training_plan_id => talent_mgmt_rec.training_plan_id );
,p_upgrade_id => p_update_id
,p_object_version_number => l_object_version_number
,p_training_plan_member_id => talent_mgmt_rec.training_plan_member_id
,p_attribute_category => talent_mgmt_rec.tpm_attribute_category
,p_attribute1 => talent_mgmt_rec.tpm_attribute1
,p_attribute2 => talent_mgmt_rec.tpm_attribute2
,p_attribute3 => talent_mgmt_rec.tpm_attribute3
,p_attribute4 => talent_mgmt_rec.tpm_attribute4
,p_attribute5 => talent_mgmt_rec.tpm_attribute5
,p_attribute6 => talent_mgmt_rec.tpm_attribute6
,p_attribute7 => talent_mgmt_rec.tpm_attribute7
,p_attribute8 => talent_mgmt_rec.tpm_attribute8
,p_attribute9 => talent_mgmt_rec.tpm_attribute9
,p_attribute10 => talent_mgmt_rec.tpm_attribute10
,p_attribute11 => talent_mgmt_rec.tpm_attribute11
,p_attribute12 => talent_mgmt_rec.tpm_attribute12
,p_attribute13 => talent_mgmt_rec.tpm_attribute13
,p_attribute14 => talent_mgmt_rec.tpm_attribute14
,p_attribute15 => talent_mgmt_rec.tpm_attribute15
,p_attribute16 => talent_mgmt_rec.tpm_attribute16
,p_attribute17 => talent_mgmt_rec.tpm_attribute17
,p_attribute18 => talent_mgmt_rec.tpm_attribute18
,p_attribute19 => talent_mgmt_rec.tpm_attribute19
,p_attribute20 => talent_mgmt_rec.tpm_attribute20
,p_attribute21 => talent_mgmt_rec.tpm_attribute21
,p_attribute22 => talent_mgmt_rec.tpm_attribute22
,p_attribute23 => talent_mgmt_rec.tpm_attribute23
,p_attribute24 => talent_mgmt_rec.tpm_attribute24
,p_attribute25 => talent_mgmt_rec.tpm_attribute25
,p_attribute26 => talent_mgmt_rec.tpm_attribute26
,p_attribute27 => talent_mgmt_rec.tpm_attribute27
,p_attribute28 => talent_mgmt_rec.tpm_attribute28
,p_attribute29 => talent_mgmt_rec.tpm_attribute29
,p_attribute30 => talent_mgmt_rec.tpm_attribute30 );
Select nvl(count(1),0)
into l_rows_processed
from ota_training_plans
where training_plan_id between p_start_pkid and p_end_pkid;
p_update_id in number default 1 --CONC_UPGRADE_ID
) IS
l_learning_path_id number;
SELECT tp.training_plan_id, tp.end_date, tp.person_id,tp.name
, tp.learning_path_id,tp.plan_status_type_id, tp.business_group_id
, tp.plan_source, tp.contact_id
,tp.ATTRIBUTE_CATEGORY,tp.ATTRIBUTE1
,tp.ATTRIBUTE2 ,tp.ATTRIBUTE3
,tp.ATTRIBUTE4 ,tp.ATTRIBUTE5
,tp.ATTRIBUTE6 ,tp.ATTRIBUTE7
,tp.ATTRIBUTE8 ,tp.ATTRIBUTE9
,tp.ATTRIBUTE10 ,tp.ATTRIBUTE11
,tp.ATTRIBUTE12 ,tp.ATTRIBUTE13
,tp.ATTRIBUTE14 ,tp.ATTRIBUTE15
,tp.ATTRIBUTE16 ,tp.ATTRIBUTE17
,tp.ATTRIBUTE18 ,tp.ATTRIBUTE19
,tp.ATTRIBUTE20 ,tp.ATTRIBUTE21
,tp.ATTRIBUTE22 ,tp.ATTRIBUTE23
,tp.ATTRIBUTE24 ,tp.ATTRIBUTE25
,tp.ATTRIBUTE26 ,tp.ATTRIBUTE27
,tp.ATTRIBUTE28 ,tp.ATTRIBUTE29
,tp.ATTRIBUTE30 ,tp.creator_person_id
FROM ota_training_plans tp
WHERE tp.PLAN_SOURCE in ('CATALOG','MANAGER')
AND tp.learning_path_id is not null
AND (tp.PERSON_ID is not NULL OR tp.contact_id is not null)
AND tp.training_plan_id between p_start_pkid and p_end_pkid;
SELECT tpm.training_plan_member_id
,tpm.member_status_type_id
,tpm.activity_version_id
,tpm.target_completion_date
,tpm.business_group_id
,Lpm.learning_path_member_id
,Lpm.learning_path_section_id
,tpm.ATTRIBUTE_CATEGORY ,tpm.ATTRIBUTE1
,tpm.ATTRIBUTE2 ,tpm.ATTRIBUTE3
,tpm.ATTRIBUTE4 ,tpm.ATTRIBUTE5
,tpm.ATTRIBUTE6 ,tpm.ATTRIBUTE7
,tpm.ATTRIBUTE8 ,tpm.ATTRIBUTE9
,tpm.ATTRIBUTE10 ,tpm.ATTRIBUTE11
,tpm.ATTRIBUTE12 ,tpm.ATTRIBUTE13
,tpm.ATTRIBUTE14 ,tpm.ATTRIBUTE15
,tpm.ATTRIBUTE16 ,tpm.ATTRIBUTE17
,tpm.ATTRIBUTE18 ,tpm.ATTRIBUTE19
,tpm.ATTRIBUTE20 ,tpm.ATTRIBUTE21
,tpm.ATTRIBUTE22 ,tpm.ATTRIBUTE23
,tpm.ATTRIBUTE24 ,tpm.ATTRIBUTE25
,tpm.ATTRIBUTE26 ,tpm.ATTRIBUTE27
,tpm.ATTRIBUTE28 ,tpm.ATTRIBUTE29
,tpm.ATTRIBUTE30
FROM ota_training_plan_members tpm,
ota_training_plans tp,
ota_learning_path_members lpm,
ota_learning_paths lps
WHERE tp.training_plan_id = p_training_plan_id
AND lpm.activity_version_id = tpm.activity_version_id
AND lps.path_source_code in ('CATALOG','MANAGER')
AND lps.learning_path_id = lpm.learning_path_id
AND tp.training_plan_id = tpm.training_plan_id
AND tp.learning_path_id = lps.learning_path_id;
,p_upgrade_id => p_update_id
,p_training_plan_id => tp_rec.training_plan_id
,p_attribute_category => tp_rec.attribute_category
,p_attribute1 => tp_rec.attribute1
,p_attribute2 => tp_rec.attribute2
,p_attribute3 => tp_rec.attribute3
,p_attribute4 => tp_rec.attribute4
,p_attribute5 => tp_rec.attribute5
,p_attribute6 => tp_rec.attribute6
,p_attribute7 => tp_rec.attribute7
,p_attribute8 => tp_rec.attribute8
,p_attribute9 => tp_rec.attribute9
,p_attribute10 => tp_rec.attribute10
,p_attribute11 => tp_rec.attribute11
,p_attribute12 => tp_rec.attribute12
,p_attribute13 => tp_rec.attribute13
,p_attribute14 => tp_rec.attribute14
,p_attribute15 => tp_rec.attribute15
,p_attribute16 => tp_rec.attribute16
,p_attribute17 => tp_rec.attribute17
,p_attribute18 => tp_rec.attribute18
,p_attribute19 => tp_rec.attribute19
,p_attribute20 => tp_rec.attribute20
,p_attribute21 => tp_rec.attribute21
,p_attribute22 => tp_rec.attribute22
,p_attribute23 => tp_rec.attribute23
,p_attribute24 => tp_rec.attribute24
,p_attribute25 => tp_rec.attribute25
,p_attribute26 => tp_rec.attribute26
,p_attribute27 => tp_rec.attribute27
,p_attribute28 => tp_rec.attribute28
,p_attribute29 => tp_rec.attribute29
,p_attribute30 => tp_rec.attribute30);
,p_upgrade_id => p_update_id
,p_object_version_number => l_object_version_number
,p_training_plan_member_id => tpm_rec.training_plan_member_id
,p_attribute_category => tpm_rec.attribute_category
,p_attribute1 => tp_rec.attribute1
,p_attribute2 => tpm_rec.attribute2
,p_attribute3 => tpm_rec.attribute3
,p_attribute4 => tpm_rec.attribute4
,p_attribute5 => tpm_rec.attribute5
,p_attribute6 => tpm_rec.attribute6
,p_attribute7 => tpm_rec.attribute7
,p_attribute8 => tpm_rec.attribute8
,p_attribute9 => tpm_rec.attribute9
,p_attribute10 => tpm_rec.attribute10
,p_attribute11 => tpm_rec.attribute11
,p_attribute12 => tpm_rec.attribute12
,p_attribute13 => tpm_rec.attribute13
,p_attribute14 => tpm_rec.attribute14
,p_attribute15 => tpm_rec.attribute15
,p_attribute16 => tpm_rec.attribute16
,p_attribute17 => tpm_rec.attribute17
,p_attribute18 => tpm_rec.attribute18
,p_attribute19 => tpm_rec.attribute19
,p_attribute20 => tpm_rec.attribute20
,p_attribute21 => tpm_rec.attribute21
,p_attribute22 => tpm_rec.attribute22
,p_attribute23 => tpm_rec.attribute23
,p_attribute24 => tpm_rec.attribute24
,p_attribute25 => tpm_rec.attribute25
,p_attribute26 => tpm_rec.attribute26
,p_attribute27 => tpm_rec.attribute27
,p_attribute28 => tpm_rec.attribute28
,p_attribute29 => tpm_rec.attribute29
,p_attribute30 => tpm_rec.attribute30);
Select nvl(count(1),0)
into l_rows_processed
from ota_training_plans
where training_plan_id between p_start_pkid and p_end_pkid;
PROCEDURE update_enrollment_status is
begin
UPDATE ota_lp_enrollments
SET path_status_code = 'COMPLETED'
where path_status_code = 'OTA_COMPLETED';
UPDATE ota_lp_member_enrollments
SET member_status_code = decode(member_status_code,'OTA_COMPLETED','COMPLETED'
,'OTA_PLANNED','PLANNED','OTA_AWAITING_APPROVAL','AWAITING_APPROVAL'
,member_status_code);
end update_enrollment_status;
SELECT decode(member_status_code, 'PLANNED', 0,'ACTIVE', 0, 'AWAITING_APPROVAL',0, 'COMPLETED',1,
'CANCELLED',2) top_status
FROM ota_lp_member_enrollments lpme
WHERE lpme.lp_enrollment_id = p_lp_enrollment_id
ORDER BY top_status;
p_update_id in number default 1
) IS
l_lp_enrollment_id number;
SELECT lpe.learning_path_id, lpe.lp_enrollment_id, lpe.person_id, lpe.contact_id
,lpe.completion_date
,lpe.object_version_number
FROM ota_lp_enrollments lpe
WHERE lpe.path_status_code = p_path_status_code
and lpe.lp_enrollment_id between p_start_pkid and p_end_pkid;
SELECT lpm.activity_version_id, lpme.lp_member_enrollment_id, lpme.member_status_code,
lpme.object_version_number
FROM ota_lp_member_enrollments lpme,ota_learning_path_members lpm
WHERE lpme.learning_path_member_id = lpm.learning_path_member_id
AND lpme.lp_enrollment_id = p_lp_enrollment_id
-- AND lpm.learning_path_id = p_learning_path_id
AND lpme.member_status_code IN ('PLANNED','ACTIVE');
SELECT DECODE(bst.type,'C','Z',bst.type) status
FROM ota_events evt,
ota_delegate_bookings tdb,
ota_booking_status_types bst
WHERE evt.activity_version_id=p_activity_version_id
AND evt.event_id = tdb.event_id
AND bst.booking_status_type_id = tdb.booking_status_type_id
AND ((p_person_id is not null and tdb.delegate_person_id = p_person_id )
or (p_contact_id is not null and tdb.delegate_contact_id = p_contact_id))
and rownum=1
ORDER BY status;
SELECT 1 from ota_lp_member_enrollments lpme
WHERE lpme.member_status_code in ('ACTIVE','PLANNED')
AND lpme.LP_ENROLLMENT_ID = P_LP_ENROLLMENT_ID;
SELECT lpme.lp_member_enrollment_id
FROM ota_lp_member_enrollments lpme
WHERE lpme.member_status_code = 'COMPLETED'
AND lpme.LP_ENROLLMENT_ID between p_start_pkid and p_end_pkid;
SELECT trunc(min(nvl(tdb.date_status_changed,tdb.date_booking_placed))) completion_date
FROM ota_delegate_bookings tdb,
ota_lp_member_enrollments lpme,
ota_learning_path_members lpm,
ota_lp_enrollments lpe,
ota_events evt,
ota_booking_status_types bst
WHERE tdb.event_id = evt.event_id
AND evt.activity_version_id = lpm.activity_version_id
AND tdb.booking_status_type_id = bst.booking_status_type_id
AND bst.type = 'A'
AND lpe.lp_enrollment_id = lpme.lp_enrollment_id
AND lpme.learning_path_member_id = lpm.learning_path_member_id
AND ((lpe.person_id IS NOT NULL AND lpe.person_id = tdb.delegate_person_id)
OR (lpe.contact_id IS NOT NULL AND lpe.contact_id = tdb.delegate_contact_id))
AND lpme.lp_member_enrollment_id = p_lpm_enrollment_id;
SELECT trunc(max(lpme.completion_date)) completion_date,
count(lpme.lp_member_enrollment_id) completed_courses
FROM ota_lp_member_enrollments lpme
WHERE lpme.member_status_code = 'COMPLETED'
AND lpme.lp_enrollment_id = p_lp_enrollment_id;
SELECT 1
FROM ota_training_plans tps
WHERE (tps.PERSON_ID is not NULL OR tps.contact_id is not null)
and (tps.ATTRIBUTE_CATEGORY||tps.ATTRIBUTE1||tps.ATTRIBUTE2||tps.ATTRIBUTE3||tps.ATTRIBUTE4||
tps.ATTRIBUTE5||tps.ATTRIBUTE6||tps.ATTRIBUTE7 ||tps.ATTRIBUTE9||tps.ATTRIBUTE10||tps.ATTRIBUTE11||
tps.ATTRIBUTE12||tps.ATTRIBUTE13||tps.ATTRIBUTE14||tps.ATTRIBUTE15||tps.ATTRIBUTE16 ||
tps.ATTRIBUTE17||tps.ATTRIBUTE18||tps.ATTRIBUTE19||tps.ATTRIBUTE20||tps.ATTRIBUTE21 ||
tps.ATTRIBUTE22||tps.ATTRIBUTE23||tps.ATTRIBUTE24 ||tps.ATTRIBUTE25||tps.ATTRIBUTE26||
tps.ATTRIBUTE27||tps.ATTRIBUTE28||tps.ATTRIBUTE29||tps.ATTRIBUTE30) is not null
AND ROWNUM=1;
SELECT 1
FROM ota_training_plan_members tpm, ota_training_plans tps
WHERE (tps.PERSON_ID is not NULL OR contact_id is not null)
AND (TPM.ATTRIBUTE_CATEGORY||TPM.ATTRIBUTE1||TPM.ATTRIBUTE2||TPM.ATTRIBUTE3||TPM.ATTRIBUTE4||
TPM.ATTRIBUTE5||TPM.ATTRIBUTE6||TPM.ATTRIBUTE7 ||TPM.ATTRIBUTE9||TPM.ATTRIBUTE10||TPM.ATTRIBUTE11||
TPM.ATTRIBUTE12||TPM.ATTRIBUTE13||TPM.ATTRIBUTE14||TPM.ATTRIBUTE15||TPM.ATTRIBUTE16 ||
TPM.ATTRIBUTE17||TPM.ATTRIBUTE18||TPM.ATTRIBUTE19||TPM.ATTRIBUTE20||TPM.ATTRIBUTE21 ||
TPM.ATTRIBUTE22||TPM.ATTRIBUTE23||TPM.ATTRIBUTE24 ||TPM.ATTRIBUTE25||TPM.ATTRIBUTE26||
TPM.ATTRIBUTE27||TPM.ATTRIBUTE28||TPM.ATTRIBUTE29|| TPM.ATTRIBUTE30) IS NOT NULL
AND tps.training_plan_id = tpm.training_plan_id
AND ROWNUM=1;
SELECT LPS.SOURCE_FUNCTION_CODE
FROM OTA_LEARNING_PATHS LPS, OTA_LP_ENROLLMENTS LPE
WHERE LPE.LEARNING_PATH_ID = LPS.LEARNING_PATH_ID
AND LPE.LP_ENROLLMENT_ID = P_LP_ENROLLMENT_ID
AND LPS.SOURCE_FUNCTION_CODE = 'SUITABILITY';
/* update the enrollment status of ota_lp_enrollments and ota_lp_member_enrollments */
--update_enrollment_status;
update ota_lp_enrollments lpe
set lpe.no_of_mandatory_courses = (select count(lpme.learning_path_member_id)
from ota_lp_member_enrollments lpme
where lpme.lp_enrollment_id = lpe.lp_enrollment_id);
UPDATE ota_lp_enrollments
SET path_status_code = 'COMPLETED'
, completion_date = to_date('31/12/4712','DD/MM/RRRR')
WHERE lp_enrollment_id = l_lp_enrollment_id;
UPDATE ota_lp_member_enrollments
SET completion_date = l_completion_date
WHERE lp_member_enrollment_id = l_lpm_enrollment_id;
ota_lp_enrollment_api.update_lp_enrollment
(p_effective_date => trunc(sysdate)
,p_lp_enrollment_id => l_lp_enrollment_id
,p_path_status_code => 'COMPLETED'
,p_no_of_completed_courses => nvl(l_completed_courses,0)
,p_completion_date => l_completion_date
,p_object_version_number => l_lp_object_version_number);
ota_classic_upgrade.add_log_entry( p_table_name=>'UPDATE_LP_ENROLLMENTS'
,p_source_primary_key => l_lp_enrollment_id
,p_business_group_id => l_lp_enrollment_id
,p_object_value => l_lp_object_version_number
,p_message_text => l_err_msg
,p_upgrade_id => p_update_id
,p_process_date => ota_classic_upgrade.get_process_date(P_UPDATE_ID,LP_UPGRADE_NAME)--trunc(sysdate)
,p_log_type => LP_LOG_TYPE_E
,p_upgrade_name => LP_UPGRADE_NAME );--ota_classic_upgrade.get_process_date());
UPDATE ota_lp_enrollments
SET completion_date = l_completion_date
,no_of_completed_courses = nvl(l_completed_courses,0)
WHERE lp_enrollment_id = l_lp_enrollment_id;
UPDATE ota_lp_enrollments
SET no_of_completed_courses = nvl(l_completed_courses,0)
WHERE lp_enrollment_id = lpe_rec.lp_enrollment_id;
UPDATE ota_lp_enrollments
SET no_of_completed_courses = nvl(l_completed_courses,0)
WHERE lp_enrollment_id = lpe_rec.lp_enrollment_id;
UPDATE OTA_LP_ENROLLMENTS
SET PATH_STATUS_CODE = 'ACTIVE',
COMPLETION_DATE = NULL
WHERE LP_ENROLLMENT_ID IN (SELECT LPE.LP_ENROLLMENT_ID
FROM OTA_LP_ENROLLMENTS LPE, OTA_LEARNING_PATHS LPS
WHERE LPE.LEARNING_PATH_ID = LPS.LEARNING_PATH_ID
AND LPS.SOURCE_FUNCTION_CODE = 'SUITABILITY' );
migrate_dff_contexts('OTA_TRAINING_PLANS','OTA_LP_ENROLLMENTS','ATTRIBUTE','ATTRIBUTE',p_update_id);
migrate_dff_contexts('OTA_TRAINING_PLAN_MEMBERS','OTA_LP_MEMBER_ENROLLMENTS','ATTRIBUTE','ATTRIBUTE',p_update_id);
SELECT nvl(count(1),0)
INTO l_rows_processed
FROM ota_lp_enrollments lpe
WHERE lpe.lp_enrollment_id between p_start_pkid and p_end_pkid;
,p_source_primary_key => p_update_id
,p_business_group_id => null
,p_object_value => null
,p_message_text => 'Done with LP upgrade'
,p_upgrade_id => p_update_id
,p_process_date => ota_classic_upgrade.get_process_date(P_UPDATE_ID,LP_UPGRADE_NAME)--trunc(sysdate)
,p_log_type => LP_LOG_TYPE_N
,p_upgrade_name => LP_UPGRADE_NAME );