[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select max(to_date(pei_information1,'yyyy/mm/dd hh24:mi:ss'))
into v_ir21_date
from per_people_extra_info
--where person_id = p_person_id
where person_id = lp_person_id
and information_type = 'HR_IR21_PROCESSING_DATES_SG';
select SUBSTR(argument1,INSTR(argument1,'=',1)+1,LENGTH(argument1)),
SUBSTR(argument2,INSTR(argument2,'=',1)+1,LENGTH(argument2)),
SUBSTR(argument3,INSTR(argument3,'=',1)+1,LENGTH(argument3)),
SUBSTR(argument4,INSTR(argument4,'=',1)+1,LENGTH(argument4)),
SUBSTR(argument5,INSTR(argument5,'=',1)+1,LENGTH(argument5)),
SUBSTR(argument6,INSTR(argument6,'=',1)+1,LENGTH(argument6)),
SUBSTR(argument7,INSTR(argument7,'=',1)+1,LENGTH(argument7)),
SUBSTR(argument8,INSTR(argument8,'=',1)+1,LENGTH(argument8))
into
LP_BUSINESS_GROUP_ID,
Lp_basis_year,
LP_PERSON_ID,
LP_IR21_MODE,
LP_CR_YEAR_AMOUNT,
LP_PR_YEAR_AMOUNT,
t_run,
DebugFlag
from FND_CONCURRENT_REQUESTS
where request_id = FND_GLOBAL.conc_request_id;
select hl.meaning pay_basis
from hr_organization_units hou,
hr_soft_coding_keyflex hsc,
per_assignments_f paaf,
per_pay_bases ppb,
hr_lookups hl
where to_char(hou.organization_id) = hsc.segment1
and hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
and paaf.pay_basis_id = ppb.pay_basis_id
and ppb.pay_basis = hl.lookup_code
and hl.lookup_type = 'PAY_BASIS'
--and hou.organization_id = organization_id
and hou.organization_id = organization_id_V
and paaf.person_id = person_id
and actual_termination_date between paaf.effective_start_date and paaf.effective_end_date
and paaf.primary_flag = 'Y';
select decode(sign(fnd_date.canonical_to_date(ppeo.pei_information1) -
to_date('1/1/'||to_char(yrtbl.yr),'dd/mm/yyyy')),
-1, to_date('1/1/'||to_char(yrtbl.yr),'dd/mm/yyyy'),
fnd_date.canonical_to_date(ppeo.pei_information1)) from_date,
decode(sign(fnd_date.canonical_to_date(ppeo.pei_information2) -
to_date('31/12/'||to_char(yrtbl.yr),'dd/mm/yyyy')),
-1, fnd_date.canonical_to_date(ppeo.pei_information2),
to_date('31/12/'||to_char(yrtbl.yr),'dd/mm/yyyy')) to_date,
ppeo.pei_information3 no_days,
ppeo.pei_information4 no_employees,
yrtbl.yr yr
from per_people_extra_info ppeo,
(select (lp_basis_year) yr from dual union
select (lp_basis_year - 1) yr from dual union
select (lp_basis_year - 2) yr from dual) yrtbl
where ppeo.person_id = person_id
and ppeo.information_type = 'HR_IR21A_CESSATION_INFO_SG'
and fnd_date.canonical_to_date(ppeo.pei_information1) =
(select max(fnd_date.canonical_to_date(ppeo2.pei_information1))
from per_people_extra_info ppeo2
where ppeo.person_id = ppeo2.person_id
and ppeo2.information_type = 'HR_IR21A_CESSATION_INFO_SG'
and fnd_date.canonical_to_date(ppeo2.pei_information1) <=
to_date('31-12-'||to_char(yrtbl.yr),'dd-mm-yyyy')
and nvl(fnd_date.canonical_to_date(ppeo2.pei_information2),to_date('31/12/4712','dd/mm/yyyy')) >=
to_date('1-01-'||to_char(yrtbl.yr),'dd-mm-yyyy')
);
select fnd_date.canonical_to_date(pei_information1) ir21_date
from per_people_extra_info ppeo
--where ppeo.person_id = lp_person_id
where ppeo.person_id = lp_person_id
and information_type = 'HR_IR21_PROCESSING_DATES_SG'
and fnd_date.canonical_to_date(pei_information1) = trunc(sysdate);
insert into per_people_extra_info (
person_extra_info_id,
person_id,
information_type,
pei_information_category,
pei_information1,
object_version_number,
last_update_date,
creation_date
)
values (
per_people_extra_info_s.nextval,
--p_person_id,
lp_person_id,
'HR_IR21_PROCESSING_DATES_SG',
'HR_IR21_PROCESSING_DATES_SG',
fnd_date.date_to_canonical(trunc(sysdate)),
1,
sysdate,
sysdate
);
select
sum(pei2.pei_information4)
from per_people_extra_info pei2
--where pei2.person_id = lp_person_id
where pei2.person_id = lp_person_id
and pei2.information_type = 'HR_STOCK_GRANT_SG'
and to_date(pei2.pei_information3,'YYYY/MM/DD HH24:MI:SS')< to_date('01/01/2003','DD/MM/YYYY')
group by pei2.person_id;
select sum(pei.pei_information6)
from per_people_extra_info pei,
per_people_extra_info pei2
--where pei.person_id = p_person_id
where pei.person_id = lp_person_id
and pei.person_id = pei2.person_id
and pei.information_type = 'HR_STOCK_EXERCISE_SG'
and pei2.information_type = 'HR_STOCK_GRANT_SG'
and pei2.person_extra_info_id = pei.pei_information2
and to_date(pei2.pei_information3,'YYYY/MM/DD HH24:MI:SS')< to_date('01/01/2003','DD/MM/YYYY')
group by pei.person_id;
select 1
from per_assignments_f paf,
hr_soft_coding_keyflex hsc
--where paf.person_id = p_person_id
where paf.person_id = lp_person_id
and paf.business_group_id = lp_business_group_id
and paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
and hsc.segment1 = organization_id
and paf.primary_flag = 'Y'
and paf.effective_start_date =
(select max(paf1.effective_start_date)
from per_assignments_f paf1,
per_assignment_status_types past
where paf1.person_id = paf.person_id
and paf1.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status = 'ACTIVE_ASSIGN'
and paf1.assignment_type = 'E'
and paf1.effective_start_date <= to_date('31/12/'||lp_basis_year,'DD/MM/YYYY')
and paf1.effective_end_date >= to_date('01/01/'||lp_basis_year,'DD/MM/YYYY')
and paf1.primary_flag='Y')
and paf.effective_start_date <= to_date('31/12/'||lp_basis_year,'DD/MM/YYYY')
and paf.effective_end_date >= to_date('01/01/'||lp_basis_year,'DD/MM/YYYY')
and paf.effective_start_date between date_start and actual_termination_date;