[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT paf.person_id
, paf.assignment_id
, paf.assignment_type
, paf.business_group_id
FROM per_people_f ppf
,per_assignments_f paf
,per_person_type_usages_f ptu
,per_person_types pts
,per_business_groups pbg
WHERE
ppf.person_id = paf.person_id
AND ((pts.system_person_type IN ('EMP','CWK') AND paf.primary_flag = 'Y') OR (paf.assignment_type = 'A' AND pts.system_person_type ='APL'))
AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date --added
AND trunc(sysdate) BETWEEN trunc(ptu.effective_start_date) AND trunc(ptu.effective_end_date) --added
AND pts.person_type_id = ptu.person_type_id
AND ptu.person_id = ppf.person_id
AND pts.system_person_type IN ('EMP', 'CWK', 'APL')
AND paf.assignment_type IN ('A','E','C')
AND (p_ota_global_bg_prof_val IS NOT NULL or pbg.business_group_id = p_per_bg_grp_prof_val)
-- AND pbg.business_group_id = p_per_bg_grp_prof_val
AND paf.business_group_id = pbg.business_group_id
AND
((pts.system_person_type = 'APL'
AND NOT EXISTS (SELECT person_id
FROM per_person_type_usages_f ptf,
per_person_types ptp WHERE trunc(sysdate) BETWEEN trunc(ptf.effective_start_date) AND trunc(ptf.effective_end_date)
AND ptf.person_type_id = ptp.person_type_id
AND ptp.system_person_type IN ('EMP', 'CWK')
AND ptf.person_id = ppf.PERSON_ID)
)
OR pts.system_person_type IN ('EMP', 'CWK'))
order by paf.person_id;
select
nvl(processing_status,'A') processing_status_flag
from
ota_user_group_elements
where
user_group_id = p_user_group_id
--and trunc(sysdate) between start_date_active and nvl(end_date_active,trunc(sysdate+1))
--Adding above clause will not display members for end dated user groups
and elig_prfl_id = p_eligibility_profile_id
and person_id is null;
select
processing_status
from
ota_user_group_elements
where
user_group_id = p_user_group_id
--and trunc(sysdate) between start_date_active and nvl(end_date_active,trunc(sysdate+1))
--Adding above clause will not display members for end dated user groups
and elig_prfl_id is not null
and person_id is null
and nvl(processing_status,'A') <> 'A';
select
user_group_name
from ota_user_groups_tl
where
user_group_id = p_ug_id
and language = userenv('LANG');
select person_id
from
ota_user_group_elements
where
user_group_id = p_user_group_id and person_id= p_person_id
and processing_status = nvl(p_processing_status,processing_status)
and elig_prfl_id is not null
and person_id is not null;
select elig_obj_id
from ben_elig_obj_f
where
TABLE_NAME = 'OTA_USER_GROUPS_B'
and COLUMN_NAME='USER_GROUP_ID'
and COLUMN_VALUE= p_user_group_id
and trunc(sysdate) between effective_start_date and effective_end_date;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Record exist for person_id : ' ||TO_CHAR(p_person_id) ||' user_group_id : ' ||TO_CHAR(p_user_group_id) ||' so update.Eligibility profile id is : '||TO_CHAR(p_eligibility_profile_id));
update ota_user_group_elements set processing_status= 'A' ,elig_prfl_id = p_eligibility_profile_id
where
user_group_id= p_user_group_id and
person_id = p_person_id and
elig_prfl_id is not null;
insert into ota_user_group_elements(
USER_GROUP_ELEMENT_ID,
USER_GROUP_ID,
BUSINESS_GROUP_ID,
PERSON_ID,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
ELIG_PRFL_ID,
processing_status)
VALUES(
ota_user_group_elements_s.nextval,
p_user_group_id,
p_business_group_id,
p_person_id,
l_object_version_number,
sysdate,
p_eligibility_profile_id,
'A');
select
eligy_prfl_id,
name,
asmt_to_use_cd,
business_group_id
from ben_eligy_prfl_f
where
eligy_prfl_id = p_elig_prof_id
and trunc(sysdate) between effective_start_date and effective_end_date
and stat_cd in ('A','P');
(select user_group_id from ota_user_group_elements
where elig_prfl_id ='|| TO_CHAR(p_elig_prof_id)|| ')';
get_ug_to_be_processed := 'select * from
(select
ug.user_group_id,
uge.elig_prfl_id,
uge.user_group_element_id,
uge.business_group_id
from
ota_user_groups_b ug,
ota_user_group_elements uge
where
ug.user_group_id = uge.user_group_id
and trunc(sysdate) between trunc(ug.start_date_active) and nvl(ug.end_date_active,trunc(sysdate+1))
and uge.elig_prfl_id is not null
and uge.person_id is null
and nvl(uge.processing_status,''A'') <> ''P''
and ug.business_group_id = ota_general.get_business_group_id) QRSLT WHERE '|| ug_ep_whereclause ;
update ota_user_group_elements set processing_status = 'P' --Parent record to Processing
where user_group_element_id = user_group_rec.user_group_element_id;
update ota_user_group_elements set processing_status = 'H' --Child records to history
where user_group_id = user_group_rec.user_group_id and elig_prfl_id =user_group_rec.elig_prfl_id and person_id is not null;
l_sql_stmnt := 'select
* from(
select
person_id,
assignment_id,
assignment_type,
business_group_id
from
per_assignments_f
where
trunc(sysdate) between effective_start_date and effective_end_date
and((assignment_type in (''E'',''C'') and PRIMARY_FLAG = ''Y'') OR (assignment_type = ''A''))
and ((fnd_profile.value(''OTA_HR_GLOBAL_BUSINESS_GROUP_ID'') is not null and business_group_id = '||l_elig_pro_bg_id||') or (business_group_id = fnd_profile.value(''PER_BUSINESS_GROUP_ID''))))
QRSLT WHERE ' || assignment_whereclause;
update ota_user_group_elements set processing_status= 'A'
where user_group_element_id = user_group_rec.user_group_element_id;--set status of master record to A
update ota_user_group_elements set processing_status= 'E' --Parent record to Error
where user_group_element_id =l_learner_group_element_id;
(select user_group_id from ota_user_group_elements
where elig_prfl_id ='|| TO_CHAR(p_elig_prof_id)|| ')';
get_ug_to_be_processed := 'select * from
(select
ug.user_group_id,
uge.elig_prfl_id,
uge.user_group_element_id,
uge.business_group_id
from
ota_user_groups_b ug,
ota_user_group_elements uge
where
ug.user_group_id = uge.user_group_id
and trunc(sysdate) between trunc(ug.start_date_active) and nvl(ug.end_date_active,trunc(sysdate+1))
and uge.elig_prfl_id is not null
and uge.person_id is null
and nvl(uge.processing_status,''A'') in (''A'',''E'')
and ug.business_group_id = ota_general.get_business_group_id) QRSLT WHERE '|| ug_whereclause ||
' order by user_group_id';
update ota_user_group_elements set processing_status = fnd_global.conc_request_id --Parent record to Processing
where user_group_id = user_elig_pro_rec(ug_print_count).user_group_id
and elig_prfl_id is not null
and person_id is null
and BUSINESS_GROUP_ID = l_general_bg;
update ota_user_group_elements set processing_status = 'H' --Child records to history
where user_group_id = user_elig_pro_rec(ug_print_count).user_group_id
and elig_prfl_id is not null
and person_id is not null
and BUSINESS_GROUP_ID = l_general_bg;
update ota_user_group_elements set processing_status = 'A' --Parent record to completed
where user_group_id = user_elig_pro_rec(ug_print_count).user_group_id
and nvl(processing_status,'A') <> 'E'
and elig_prfl_id is not null
and person_id is null
and BUSINESS_GROUP_ID = l_general_bg;
update ota_user_group_elements set processing_status = 'P' --Parent record to Processed
where user_group_id = user_elig_pro_rec(ug_print_count).user_group_id
and elig_prfl_id is not null
and person_id is null
and BUSINESS_GROUP_ID = l_general_bg;
update ota_user_group_elements set processing_status = 'E' --Parent record to Error
where user_group_id = user_elig_pro_rec(ug_print_count).user_group_id
and elig_prfl_id is not null
and person_id is null
and BUSINESS_GROUP_ID = l_general_bg;
select decode(PARENT_REQUEST_ID,-1,REQUEST_ID,PARENT_REQUEST_ID)
from fnd_concurrent_requests
where REQUEST_ID = p_conc_request_id;
SELECT ran.range_id
,ran.starting_person_action_id
,ran.ending_person_action_id
FROM OTA_BATCH_RANGES ran
WHERE ran.range_status_cd = 'U'
AND ran.batch_source_cd = 'EP'
AND ran.BATCH_ACTION_ID = p_action_id
AND rownum < 2
FOR UPDATE OF ran.range_status_cd;
SELECT perf.person_id
,perf.assignment_id
,perf.mbr_bg_id
FROM ota_bulk_enr_req_members perf
WHERE perf.BULK_ENR_REQUEST_ID = p_action_id
AND perf.person_action_id BETWEEN l_start_person_action_id AND l_end_person_action_id
ORDER BY perf.person_action_id;
SELECT DISTINCT
asmt_to_use_cd
FROM ben_eligy_prfl_f
WHERE eligy_prfl_id = p_elig_prof_id
AND trunc (sysdate) BETWEEN effective_start_date
AND effective_end_date
AND stat_cd IN ('A','P');
ug_whereclause := 'user_group_id in (select user_group_id from ota_user_group_elements
where elig_prfl_id = '
|| to_char (p_elig_prof_id)
|| ' )';
get_ug_to_be_processed := 'select * from
(select
ug.user_group_id user_group_id,
uge.elig_prfl_id elig_prfl_id,
uge.user_group_element_id user_group_element_id,
uge.business_group_id uge_business_group_id,
bep.business_group_id ep_business_group_id
from
ota_user_groups_b ug,
ota_user_group_elements uge,
ben_eligy_prfl_f bep
where
ug.user_group_id = uge.user_group_id
and trunc(sysdate) between trunc(ug.start_date_active) and nvl(ug.end_date_active,trunc(sysdate+1))
and uge.elig_prfl_id is not null
and uge.person_id is null
and bep.eligy_prfl_id = uge.elig_prfl_id
and trunc(sysdate) between bep.effective_start_date AND bep.effective_end_date
and bep.stat_cd IN (''A'',''P'')
and nvl(uge.processing_status,''A'') = '''
|| to_char (conc_parent_request_id(fnd_global.conc_request_id))
|| ''' and ug.business_group_id = ota_general.get_business_group_id) QRSLT WHERE '
|| ug_whereclause
|| ' order by user_group_id';
UPDATE OTA_BATCH_RANGES ran
SET ran.range_status_cd = 'P'
WHERE ran.range_id = l_range_id;
l_score_tab.delete;
UPDATE ota_bulk_enr_req_members
SET action_status_cd = 'P'
WHERE person_id = l_rec.person_id
AND BULK_ENR_REQUEST_ID = p_action_id
AND action_status_cd <> 'P';
lrnr_rec.delete;
update ota_bulk_enr_req_members set action_status_cd = 'P'
WHERE BULK_ENR_REQUEST_ID = p_action_id
AND action_status_cd <> 'P'
AND person_action_id BETWEEN l_start_person_action_id
AND l_end_person_action_id;
user_elig_pro_rec.delete;
l_eligprof_tab.delete;
update ota_user_group_elements set processing_status = 'E' --Parent record to Error
where user_group_id = user_elig_pro_rec(ug_print_count).user_group_id
and elig_prfl_id is not null
and person_id is null
and BUSINESS_GROUP_ID = l_ota_general_bg;
procedure delete_ineligible_records(errbuf out nocopy varchar2,
retcode out nocopy varchar2,
p_learner_group_id in number,
p_elig_prof_id in number) is
--user_group_rec get_ug_to_be_processed%rowtype;
(select user_group_id from ota_user_group_elements
where elig_prfl_id ='|| TO_CHAR(p_elig_prof_id)|| ')';
get_ug_to_be_processed := 'select * from
(select
ug.user_group_id,
uge.elig_prfl_id,
uge.user_group_element_id,
uge.business_group_id
from
ota_user_groups_b ug,
ota_user_group_elements uge
where
ug.user_group_id = uge.user_group_id
and trunc(sysdate) between trunc(ug.start_date_active) and nvl(ug.end_date_active,trunc(sysdate+1))
and uge.elig_prfl_id is not null
and uge.person_id is null
and nvl(uge.processing_status,''A'') <> ''P''
and ug.business_group_id = ota_general.get_business_group_id) QRSLT WHERE '|| ug_ep_whereclause ;
delete from ota_user_group_elements uge where
uge.person_id is not null and
uge.elig_prfl_id is not null and
uge.processing_status = 'H' and
uge.user_group_id = user_group_rec.user_group_id and
uge.elig_prfl_id = user_group_rec.elig_prfl_id
and exists
(
select user_group_element_id from
ota_user_group_elements parent
where
parent.elig_prfl_id = uge.elig_prfl_id
and parent.user_group_id = uge.user_group_id
and nvl(parent.processing_status,'A') = 'A'
and parent.person_id is null
);
update ota_user_group_elements set processing_status= 'E' --Parent record to Error
where user_group_element_id =l_learner_group_element_id;
end delete_ineligible_records;
select user_group_id
from
ota_user_group_elements
where
elig_prfl_id=p_elig_prof_id;
select
elig_obj_id
from
ben_elig_obj_f EO
where
trunc(sysdate) between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE
and TABLE_NAME = 'OTA_USER_GROUPS_B'
and COLUMN_NAME = 'USER_GROUP_ID'
and COLUMN_VALUE = p_user_group_id;
procedure delete_eligibility_object(
p_user_group_id in varchar2,
p_eligibility_profile_id in varchar2,
p_result out nocopy varchar) is
cursor get_elig_object_id (p_user_group_id in number) is
select
elig_obj_id ,
object_version_number
from
ben_elig_obj_f EO
where
trunc(sysdate) between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE
and TABLE_NAME = 'OTA_USER_GROUPS_B'
and COLUMN_NAME = 'USER_GROUP_ID'
and COLUMN_VALUE = p_user_group_id;
select
elig_obj_elig_prfl_id
from
ben_elig_obj_elig_profl_f
where
trunc(sysdate) between effective_start_date and effective_end_date
and elig_obj_id = p_elig_obj_id
and rownum < 2;
savepoint delete_eligibility_object;
ROLLBACK TO delete_eligibility_object;
ben_elig_obj_elig_profl_api.delete_elig_obj_elig_profl
(p_validate => false
,p_elig_obj_elig_prfl_id => l_elig_obj_elig_prf_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_elig_obj_obj_version
,p_effective_date => trunc(sysdate)
,p_datetrack_mode => hr_api.g_zap
);
ben_elig_obj_api.delete_ELIG_OBJ
(p_validate => false
,p_elig_obj_id => l_elig_object_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_obj_version
,p_effective_date => trunc(sysdate)
,p_datetrack_mode => hr_api.g_zap
);
delete from ota_user_group_elements
where user_group_id=p_user_group_id and
elig_prfl_id=l_elig_prof_id and
person_id is not null;
ROLLBACK TO delete_eligibility_object;
end delete_eligibility_object;
Select null
From fnd_concurrent_requests fnd
Where fnd.phase_code <> 'C'
And fnd.request_id = p_request_id;