[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pa.assignment_id,
pp.last_name||pp.first_name emp_name,
substrb(pp.national_identifier,1,6) res_reg_num,
pp.national_identifier ni_num,
pp.employee_number emp_num
from per_kr_assignment_yea_info pkayi,
per_people_f pp,
per_periods_of_service ppos,
per_assignments_f pa
where pa.business_group_id + 0 = g_business_group_id
and pa.establishment_id = nvl(g_business_place_id,pa.establishment_id)
and decode(to_char(nvl(g_assignment_set_id,-1)),
'-1','Y',
hr_assignment_set.assignment_in_set(g_assignment_set_id,pa.assignment_id)) = 'Y'
and pa.assignment_type = 'E'
and pa.effective_start_date <= g_target_eoy
and pa.effective_end_date >= g_target_soy
and ppos.period_of_service_id = pa.period_of_service_id
and nvl(ppos.final_process_date,g_target_eoy)
between g_target_soy and g_target_eoy
and pp.person_id = pa.person_id
and pp.effective_start_date <= g_target_eoy
and pp.effective_end_date >= g_target_soy
/* fetch latest emp information */
and not exists(
select null
from per_people_f pp2
where pp2.person_id = pa.person_id
and pp2.effective_start_date <= g_target_eoy
and pp2.effective_end_date >= g_target_soy
and pp2.effective_start_date < pp.effective_start_date)
and ((nvl(g_map_key,'FULL_NAME') = 'FULL_NAME'
and p_map_file_name like pp.last_name||pp.first_name||'('||substrb(pp.national_identifier,1,6)||')%')
or (g_map_key = 'EMPLOYEE_NUMBER'
and cast(replace(lower(substrb(p_map_file_name ,instrb(p_map_file_name,'-','-1')+1)),'.pdf') as INT) = pp.employee_number))
and pkayi.assignment_id (+) = pa.assignment_id
and pkayi.target_year (+) = g_target_yyyy
and pkayi.information_type (+) = 'KR_YEA_ENTRY_STATUS'
and pkayi.ayi_information_category (+) = 'KR_YEA_ENTRY_STATUS'
and decode(nvl(g_force_load,'N'),'Y','X',nvl(pkayi.ayi_information16,'X')) <> c_process_status; /* exclude processing upload or import */
select pkayi.assignment_yea_info_id,
pkayi.assignment_id,
pp.national_identifier ni_num,
pp.last_name||pp.first_name emp_name,
pp.employee_number emp_num,
null exclude,
fl.file_id,
fl.file_data pdf_file
from per_kr_assignment_yea_info pkayi,
per_all_assignments_f pa,
per_periods_of_service ppos,
per_people_f pp,
fnd_lobs fl
where pkayi.assignment_yea_info_id = nvl(p_assignment_yea_info_id,pkayi.assignment_yea_info_id)
and pkayi.information_type = 'KR_YEA_ENTRY_STATUS'
and pkayi.ayi_information_category = 'KR_YEA_ENTRY_STATUS'
and pkayi.target_year = p_target_year
/* and decode(nvl(g_admin,'N'),'Y','N',nvl(pkayi.ayi_information3,'N')) in ('N','IE') -- include case update data after import */
and decode(nvl(g_force_load,'N'),'Y','X',nvl(pkayi.ayi_information16,'X')) <> c_process_status /* exclude processing upload or import */
and pa.assignment_id = pkayi.assignment_id
and pa.business_group_id + 0 = p_business_group_id
and pa.effective_start_date <= g_target_eoy
and pa.effective_end_date >= g_target_soy
and pa.establishment_id = nvl(p_business_place_id,pa.establishment_id)
and decode(to_char(nvl(p_assignment_set_id,-1)),
'-1','Y',
hr_assignment_set.assignment_in_set(p_assignment_set_id,pa.assignment_id)) = 'Y'
and ppos.period_of_service_id = pa.period_of_service_id
and pp.person_id = pa.person_id
and pp.effective_start_date <= g_target_eoy
and pp.effective_end_date >= g_target_soy
/* fetch latest emp information */
and not exists(
select null
from per_people_f pp2
where pp2.person_id = pa.person_id
and pp2.effective_start_date <= g_target_eoy
and pp2.effective_end_date >= g_target_soy
and pp2.effective_start_date < pp.effective_start_date)
and nvl(ppos.final_process_date,g_target_eoy)
between g_target_soy and g_target_eoy
and fl.file_id = fnd_number.canonical_to_number(pkayi.ayi_information15); /* ayi_information14 in ('I','U','E') */
procedure insert_session(
p_effective_date in date)
is
--
l_rowid rowid;
select rowid
from fnd_sessions
where session_id = userenv('sessionid')
for update nowait;
insert into fnd_sessions(
session_id,
effective_date)
values(
userenv('sessionid'),
p_effective_date);
update fnd_sessions
set effective_date = p_effective_date
where rowid = l_rowid;
end insert_session;
procedure delete_session
is
begin
--
delete
from fnd_sessions
where session_id = userenv('sessionid');
end delete_session;
select hoi.org_information1 import_path,
hoi.org_information2 nts_certs_path
from hr_organization_information hoi
where hoi.organization_id = p_business_group_id
and hoi.org_information_context = 'KR_YEA_PDF_SETUP_BG';
insert_session(g_target_eoy);
g_pdf_tbl.delete;
select rowid row_id,
object_version_number ovn
from per_kr_assignment_yea_info
where assignment_yea_info_id = p_assignment_yea_info_id
for update nowait;
update per_kr_assignment_yea_info
set ayi_information14 = c_upload_status,
ayi_information16 = c_process_status,
object_version_number = l_csr_entry_status.ovn + 1
where rowid = l_csr_entry_status.row_id;
select pkayi.assignment_yea_info_id,
fnd_number.canonical_to_number(pkayi.ayi_information15) file_id,
pkayi.object_version_number
from per_kr_assignment_yea_info pkayi
where pkayi.assignment_id = p_assignment_id
and pkayi.target_year = p_target_year
and pkayi.information_type = 'KR_YEA_ENTRY_STATUS'
and pkayi.ayi_information_category = 'KR_YEA_ENTRY_STATUS'
for update nowait;
select file_id
from fnd_lobs
where file_id = p_file_id;
update per_kr_assignment_yea_info
set ayi_information14 = c_import_status,
ayi_information15 = fnd_number.number_to_canonical(p_file_id),
ayi_information16 = nvl(p_proc_status,ayi_information16),
object_version_number = l_object_version_number
where assignment_yea_info_id = l_csr_kr_ass_yea.assignment_yea_info_id;
fnd_gfm.delete_lob(l_csr_file.file_id);
select per_kr_assignment_yea_info_s.nextval
into l_assignment_yea_info_id
from dual;
insert into per_kr_assignment_yea_info(
assignment_yea_info_id,
assignment_id,
information_type,
target_year,
ayi_information_category,
ayi_information3,
ayi_information14,
ayi_information15,
ayi_information16,
object_version_number)
values (
l_assignment_yea_info_id,
p_assignment_id,
'KR_YEA_ENTRY_STATUS',
p_target_year,
'KR_YEA_ENTRY_STATUS',
c_entry_status,
c_import_status,
fnd_number.number_to_canonical(p_file_id),
p_proc_status,
l_object_version_number);
g_ass_yea_tbl.delete;
fnd_gfm.delete_lob(l_file_id);
update per_kr_assignment_yea_info
set ayi_information16 = 'C',
object_version_number = object_version_number + 1
where assignment_yea_info_id = g_ass_yea_tbl(i).assignment_yea_info_id;
update per_kr_assignment_yea_info
set ayi_information14 = null,
ayi_information15 = null,
ayi_information16 = null,
object_version_number = object_version_number + 1
where assignment_yea_info_id = g_ass_yea_tbl(i).assignment_yea_info_id;
fnd_gfm.delete_lob(g_ass_yea_tbl(i).file_id);
select code contact_type
from (select pcr_pp.national_identifier,
nvl(pcr.cont_information11,
decode(pcr.contact_type,
'P','1',
'S','3',
'A','4',
'C','4',
'R','4',
'O','4',
'T','4',
'BROTHER','5',
'SISTER','5',
'6')) code
from per_assignments_f pa,
per_contact_relationships pcr,
per_people_f pcr_pp
where pa.assignment_id = p_assignment_id
and g_target_eoy
between pa.effective_start_date and pa.effective_end_date
and pcr.person_id = pa.person_id
and pcr_pp.person_id = pcr.contact_person_id
and g_target_eoy
between pcr_pp.effective_start_date and pcr_pp.effective_end_date
UNION ALL
select ppf.national_identifier, '0' code
from per_people_f ppf
,per_assignments_f pa
where pa.person_id = ppf.person_id
and pa.assignment_id = p_assignment_id
and g_target_eoy between pa.effective_start_date and pa.effective_end_date
and g_target_eoy between ppf.effective_start_date and ppf.effective_end_date
)
where national_identifier = l_ni_num;
select code contact_type
from (select pcr_pp.national_identifier,
decode(nvl(cont_information11,
decode(contact_type, 'S','3','P','1','B','5','SISTER','5' ,'A','4','C','4','R','4','O','4','T','4','6')),'1','4','5','5','4','3','3','2','7','3','2','4','6') code
from per_assignments_f pa,
per_contact_relationships pcr,
per_people_f pcr_pp
where pa.assignment_id = p_assignment_id
and g_target_eoy between pa.effective_start_date and pa.effective_end_date
and pcr.person_id = pa.person_id
and pcr_pp.person_id = pcr.contact_person_id
and g_target_eoy between pcr_pp.effective_start_date and pcr_pp.effective_end_date
and pcr.cont_information1 ='Y'
and pcr.cont_information2 = 'Y'
UNION ALL
select ppf.national_identifier, '0' code
from per_people_f ppf
,per_assignments_f pa
where pa.person_id = ppf.person_id
and pa.assignment_id = p_assignment_id
and g_target_eoy between pa.effective_start_date and pa.effective_end_date
and g_target_eoy between ppf.effective_start_date and ppf.effective_end_date)
where code in ('5','4','3','2','1') and national_identifier = l_ni_num;
select code contact_type
from (select pcr_pp.national_identifier,
nvl(pcr.cont_information11,
decode(pcr.contact_type,
'P','1',
'S','3',
'A','4',
'C','4',
'R','4',
'O','4',
'T','4',
'BROTHER','5',
'SISTER','5',
'6')) code
from per_assignments_f pa,
per_contact_relationships pcr,
per_people_f pcr_pp
where pa.assignment_id = p_assignment_id
and g_target_eoy
between pa.effective_start_date and pa.effective_end_date
and pa.assignment_type ='E'
and pcr.person_id = pa.person_id
and g_target_eoy
between nvl(pcr.date_start,g_target_eoy) and
decode(pcr.cont_information9,
'D',trunc(add_months(nvl(pcr.date_end,g_target_eoy),12),'YYYY')-1,
nvl(pcr.date_end,g_target_eoy))
and pcr_pp.person_id = pcr.contact_person_id
and g_target_eoy
between pcr_pp.effective_start_date and pcr_pp.effective_end_date
and pcr.cont_information_category = 'KR'
and pcr.cont_information1 = 'Y')
where national_identifier = l_ni_num;
select lookup_code
from hr_lookups
where lookup_type = 'KR_YEA_FIN_INST_CODES'
and enabled_flag = 'Y'
and meaning = p_text;
select assignment_yea_info_id
from per_kr_assignment_yea_info
where assignment_id = p_assignment_id
and target_year = g_target_yyyy
and information_type = 'KR_YEA_DETAIL_DONATION_INFO'
and ((ayi_information7 <> p_information7
and ayi_information8 = p_information8)
or (ayi_information7 = p_information7
and ayi_information8 <> p_information8))
and ayi_information13 = p_information13 ;
procedure delete_kr_ass_yea_info(
p_assignment_id in number,
p_target_year in number,
p_information_type in varchar2,
p_information1 in varchar2 default null,
p_information2 in varchar2 default null,
p_information3 in varchar2 default null,
p_information4 in varchar2 default null,
p_information5 in varchar2 default null,
p_information6 in varchar2 default null,
p_information7 in varchar2 default null,
p_information8 in varchar2 default null,
p_information9 in varchar2 default null,
p_information10 in varchar2 default null,
p_information11 in varchar2 default null,
p_information12 in varchar2 default null,
p_information13 in varchar2 default null,
p_information14 in varchar2 default null,
p_information15 in varchar2 default null,
p_information16 in varchar2 default null,
p_information17 in varchar2 default null,
p_information18 in varchar2 default null,
p_information19 in varchar2 default null,
p_information20 in varchar2 default null,
p_information21 in varchar2 default null,
p_information22 in varchar2 default null,
p_information23 in varchar2 default null,
p_information24 in varchar2 default null,
p_information25 in varchar2 default null,
p_information26 in varchar2 default null,
p_information27 in varchar2 default null,
p_information28 in varchar2 default null,
p_information29 in varchar2 default null,
p_information30 in varchar2 default null,
p_information31 in varchar2 default null,
p_information32 in varchar2 default null,
p_information33 in varchar2 default null,
p_information34 in varchar2 default null,
p_information35 in varchar2 default null)
is
--
l_proc varchar2(80) := c_package||'delete_kr_ass_yea_info';
select pkayi.assignment_yea_info_id
from per_kr_assignment_yea_info pkayi
where pkayi.assignment_id = p_assignment_id
and pkayi.target_year = p_target_year
and pkayi.information_type = p_information_type
and pkayi.ayi_information_category = p_information_type
and nvl(pkayi.ayi_information1,'X') = nvl(p_information1,nvl(pkayi.ayi_information1,'X'))
and nvl(pkayi.ayi_information2,'X') = nvl(p_information2,nvl(pkayi.ayi_information2,'X'))
and nvl(pkayi.ayi_information3,'X') = nvl(p_information3,nvl(pkayi.ayi_information3,'X'))
and nvl(pkayi.ayi_information4,'X') = nvl(p_information4,nvl(pkayi.ayi_information4,'X'))
and nvl(pkayi.ayi_information5,'X') = nvl(p_information5,nvl(pkayi.ayi_information5,'X'))
and nvl(pkayi.ayi_information6,'X') = nvl(p_information6,nvl(pkayi.ayi_information6,'X'))
and nvl(pkayi.ayi_information7,'X') = nvl(p_information7,nvl(pkayi.ayi_information7,'X'))
and nvl(pkayi.ayi_information8,'X') = nvl(p_information8,nvl(pkayi.ayi_information8,'X'))
and nvl(pkayi.ayi_information9,'X') = nvl(p_information9,nvl(pkayi.ayi_information9,'X'))
and nvl(pkayi.ayi_information10,'X') = nvl(p_information10,nvl(pkayi.ayi_information10,'X'))
and nvl(pkayi.ayi_information11,'X') = nvl(p_information11,nvl(pkayi.ayi_information11,'X'))
and nvl(pkayi.ayi_information12,'X') = nvl(p_information12,nvl(pkayi.ayi_information12,'X'))
and nvl(pkayi.ayi_information13,'X') = nvl(p_information13,nvl(pkayi.ayi_information13,'X'))
and nvl(pkayi.ayi_information14,'X') = nvl(p_information14,nvl(pkayi.ayi_information14,'X'))
and nvl(pkayi.ayi_information15,'X') = nvl(p_information15,nvl(pkayi.ayi_information15,'X'))
and nvl(pkayi.ayi_information16,'X') = nvl(p_information16,nvl(pkayi.ayi_information16,'X'))
and nvl(pkayi.ayi_information17,'X') = nvl(p_information17,nvl(pkayi.ayi_information17,'X'))
and nvl(pkayi.ayi_information18,'X') = nvl(p_information18,nvl(pkayi.ayi_information18,'X'))
and nvl(pkayi.ayi_information19,'X') = nvl(p_information19,nvl(pkayi.ayi_information19,'X'))
and nvl(pkayi.ayi_information20,'X') = nvl(p_information20,nvl(pkayi.ayi_information20,'X'))
and nvl(pkayi.ayi_information21,'X') = nvl(p_information21,nvl(pkayi.ayi_information21,'X'))
and nvl(pkayi.ayi_information22,'X') = nvl(p_information22,nvl(pkayi.ayi_information22,'X'))
and nvl(pkayi.ayi_information23,'X') = nvl(p_information23,nvl(pkayi.ayi_information23,'X'))
and nvl(pkayi.ayi_information24,'X') = nvl(p_information24,nvl(pkayi.ayi_information24,'X'))
and nvl(pkayi.ayi_information25,'X') = nvl(p_information25,nvl(pkayi.ayi_information25,'X'))
and nvl(pkayi.ayi_information26,'X') = nvl(p_information26,nvl(pkayi.ayi_information26,'X'))
and nvl(pkayi.ayi_information27,'X') = nvl(p_information27,nvl(pkayi.ayi_information27,'X'))
and nvl(pkayi.ayi_information28,'X') = nvl(p_information28,nvl(pkayi.ayi_information28,'X'))
and nvl(pkayi.ayi_information29,'X') = nvl(p_information29,nvl(pkayi.ayi_information29,'X'))
and nvl(pkayi.ayi_information30,'X') = nvl(p_information30,nvl(pkayi.ayi_information30,'X'))
and nvl(pkayi.ayi_information31,'X') = nvl(p_information31,nvl(pkayi.ayi_information31,'X'))
and nvl(pkayi.ayi_information32,'X') = nvl(p_information32,nvl(pkayi.ayi_information32,'X'))
and nvl(pkayi.ayi_information33,'X') = nvl(p_information33,nvl(pkayi.ayi_information33,'X'))
and nvl(pkayi.ayi_information34,'X') = nvl(p_information34,nvl(pkayi.ayi_information34,'X'))
and nvl(pkayi.ayi_information35,'X') = nvl(p_information35,nvl(pkayi.ayi_information35,'X'))
for update nowait;
delete from
per_kr_assignment_yea_info
where assignment_yea_info_id = l_csr_kr_ass_yea.assignment_yea_info_id;
end delete_kr_ass_yea_info;
procedure insert_kr_ass_yea_info(
p_assignment_id in number,
p_target_year in number,
p_information_type in varchar2,
p_information1 in varchar2 default null,
p_information2 in varchar2 default null,
p_information3 in varchar2 default null,
p_information4 in varchar2 default null,
p_information5 in varchar2 default null,
p_information6 in varchar2 default null,
p_information7 in varchar2 default null,
p_information8 in varchar2 default null,
p_information9 in varchar2 default null,
p_information10 in varchar2 default null,
p_information11 in varchar2 default null,
p_information12 in varchar2 default null,
p_information13 in varchar2 default null,
p_information14 in varchar2 default null,
p_information15 in varchar2 default null,
p_information16 in varchar2 default null,
p_information17 in varchar2 default null,
p_information18 in varchar2 default null,
p_information19 in varchar2 default null,
p_information20 in varchar2 default null,
p_information21 in varchar2 default null,
p_information22 in varchar2 default null,
p_information23 in varchar2 default null,
p_information24 in varchar2 default null,
p_information25 in varchar2 default null,
p_information26 in varchar2 default null,
p_information27 in varchar2 default null,
p_information28 in varchar2 default null,
p_information29 in varchar2 default null,
p_information30 in varchar2 default null,
p_information31 in varchar2 default null,
p_information32 in varchar2 default null,
p_information33 in varchar2 default null,
p_information34 in varchar2 default null,
p_information35 in varchar2 default null,
p_assignment_yea_info_id out nocopy number)
is
--
l_proc varchar2(80) := c_package||'insert_kr_ass_yea_info';
select per_kr_assignment_yea_info_s.nextval
into l_assignment_yea_info_id
from dual;
insert into per_kr_assignment_yea_info(
assignment_yea_info_id,
assignment_id,
information_type,
target_year,
ayi_information_category,
ayi_information1,
ayi_information2,
ayi_information3,
ayi_information4,
ayi_information5,
ayi_information6,
ayi_information7,
ayi_information8,
ayi_information9,
ayi_information10,
ayi_information11,
ayi_information12,
ayi_information13,
ayi_information14,
ayi_information15,
ayi_information16,
ayi_information17,
ayi_information18,
ayi_information19,
ayi_information20,
ayi_information21,
ayi_information22,
ayi_information23,
ayi_information24,
ayi_information25,
ayi_information26,
ayi_information27,
ayi_information28,
ayi_information29,
ayi_information30,
ayi_information31,
ayi_information32,
ayi_information33,
ayi_information34,
ayi_information35,
object_version_number)
values (
l_assignment_yea_info_id,
p_assignment_id,
p_information_type,
p_target_year,
p_information_type,
p_information1,
p_information2,
p_information3,
p_information4,
p_information5,
p_information6,
p_information7,
p_information8,
p_information9,
p_information10,
p_information11,
p_information12,
p_information13,
p_information14,
p_information15,
p_information16,
p_information17,
p_information18,
p_information19,
p_information20,
p_information21,
p_information22,
p_information23,
p_information24,
p_information25,
p_information26,
p_information27,
p_information28,
p_information29,
p_information30,
p_information31,
p_information32,
p_information33,
p_information34,
p_information35,
l_object_version_number);
end insert_kr_ass_yea_info;
p_force_update in varchar2 default null,
p_assignment_yea_info_id out nocopy number)
is
--
l_proc varchar2(80) := c_package||'load_kr_ass_yea_info';
select pkayi.assignment_yea_info_id,
pkayi.object_version_number
from per_kr_assignment_yea_info pkayi
where pkayi.assignment_id = p_assignment_id
and pkayi.target_year = p_target_year
and pkayi.information_type = p_information_type
and pkayi.ayi_information_category = p_information_type
for update nowait;
update per_kr_assignment_yea_info
set ayi_information1 = decode(p_force_update,'Y',decode(p_information1,'NULL',null,nvl(p_information1,ayi_information1)),nvl(p_information1,ayi_information1)),
ayi_information2 = decode(p_force_update,'Y',decode(p_information2,'NULL',null,nvl(p_information2,ayi_information2)),nvl(p_information2,ayi_information2)),
ayi_information3 = decode(p_force_update,'Y',decode(p_information3,'NULL',null,nvl(p_information3,ayi_information3)),nvl(p_information3,ayi_information3)),
ayi_information4 = decode(p_force_update,'Y',decode(p_information4,'NULL',null,nvl(p_information4,ayi_information4)),nvl(p_information4,ayi_information4)),
ayi_information5 = decode(p_force_update,'Y',decode(p_information5,'NULL',null,nvl(p_information5,ayi_information5)),nvl(p_information5,ayi_information5)),
ayi_information6 = decode(p_force_update,'Y',decode(p_information6,'NULL',null,nvl(p_information6,ayi_information6)),nvl(p_information6,ayi_information6)),
ayi_information7 = decode(p_force_update,'Y',decode(p_information7,'NULL',null,nvl(p_information7,ayi_information7)),nvl(p_information7,ayi_information7)),
ayi_information8 = decode(p_force_update,'Y',decode(p_information8,'NULL',null,nvl(p_information8,ayi_information8)),nvl(p_information8,ayi_information8)),
ayi_information9 = decode(p_force_update,'Y',decode(p_information9,'NULL',null,nvl(p_information9,ayi_information9)),nvl(p_information9,ayi_information9)),
ayi_information10 = decode(p_force_update,'Y',decode(p_information10,'NULL',null,nvl(p_information10,ayi_information10)),nvl(p_information10,ayi_information10)),
ayi_information11 = decode(p_force_update,'Y',decode(p_information11,'NULL',null,nvl(p_information11,ayi_information11)),nvl(p_information11,ayi_information11)),
ayi_information12 = decode(p_force_update,'Y',decode(p_information12,'NULL',null,nvl(p_information12,ayi_information12)),nvl(p_information12,ayi_information12)),
ayi_information13 = decode(p_force_update,'Y',decode(p_information13,'NULL',null,nvl(p_information13,ayi_information13)),nvl(p_information13,ayi_information13)),
ayi_information14 = decode(p_force_update,'Y',decode(p_information14,'NULL',null,nvl(p_information14,ayi_information14)),nvl(p_information14,ayi_information14)),
ayi_information15 = decode(p_force_update,'Y',decode(p_information15,'NULL',null,nvl(p_information15,ayi_information15)),nvl(p_information15,ayi_information15)),
ayi_information16 = decode(p_force_update,'Y',decode(p_information16,'NULL',null,nvl(p_information16,ayi_information16)),nvl(p_information16,ayi_information16)),
ayi_information17 = decode(p_force_update,'Y',decode(p_information17,'NULL',null,nvl(p_information17,ayi_information17)),nvl(p_information17,ayi_information17)),
ayi_information18 = decode(p_force_update,'Y',decode(p_information18,'NULL',null,nvl(p_information18,ayi_information18)),nvl(p_information18,ayi_information18)),
ayi_information19 = decode(p_force_update,'Y',decode(p_information19,'NULL',null,nvl(p_information19,ayi_information19)),nvl(p_information19,ayi_information19)),
ayi_information20 = decode(p_force_update,'Y',decode(p_information20,'NULL',null,nvl(p_information20,ayi_information20)),nvl(p_information20,ayi_information20)),
ayi_information21 = decode(p_force_update,'Y',decode(p_information21,'NULL',null,nvl(p_information21,ayi_information21)),nvl(p_information21,ayi_information21)),
ayi_information22 = decode(p_force_update,'Y',decode(p_information22,'NULL',null,nvl(p_information22,ayi_information22)),nvl(p_information22,ayi_information22)),
ayi_information23 = decode(p_force_update,'Y',decode(p_information23,'NULL',null,nvl(p_information23,ayi_information23)),nvl(p_information23,ayi_information23)),
ayi_information24 = decode(p_force_update,'Y',decode(p_information24,'NULL',null,nvl(p_information24,ayi_information24)),nvl(p_information24,ayi_information24)),
ayi_information25 = decode(p_force_update,'Y',decode(p_information25,'NULL',null,nvl(p_information25,ayi_information25)),nvl(p_information25,ayi_information25)),
ayi_information26 = decode(p_force_update,'Y',decode(p_information26,'NULL',null,nvl(p_information26,ayi_information26)),nvl(p_information26,ayi_information26)),
ayi_information27 = decode(p_force_update,'Y',decode(p_information27,'NULL',null,nvl(p_information27,ayi_information27)),nvl(p_information27,ayi_information27)),
ayi_information28 = decode(p_force_update,'Y',decode(p_information28,'NULL',null,nvl(p_information28,ayi_information28)),nvl(p_information28,ayi_information28)),
ayi_information29 = decode(p_force_update,'Y',decode(p_information29,'NULL',null,nvl(p_information29,ayi_information29)),nvl(p_information29,ayi_information29)),
ayi_information30 = decode(p_force_update,'Y',decode(p_information30,'NULL',null,nvl(p_information30,ayi_information30)),nvl(p_information30,ayi_information30)),
ayi_information31 = decode(p_force_update,'Y',decode(p_information31,'NULL',null,nvl(p_information31,ayi_information31)),nvl(p_information31,ayi_information31)),
ayi_information32 = decode(p_force_update,'Y',decode(p_information32,'NULL',null,nvl(p_information32,ayi_information32)),nvl(p_information32,ayi_information32)),
ayi_information33 = decode(p_force_update,'Y',decode(p_information33,'NULL',null,nvl(p_information33,ayi_information33)),nvl(p_information33,ayi_information33)),
ayi_information34 = decode(p_force_update,'Y',decode(p_information34,'NULL',null,nvl(p_information34,ayi_information34)),nvl(p_information34,ayi_information34)),
ayi_information35 = decode(p_force_update,'Y',decode(p_information35,'NULL',null,nvl(p_information35,ayi_information35)),nvl(p_information35,ayi_information35)),
object_version_number = l_object_version_number
where assignment_yea_info_id = l_csr_kr_ass_yea.assignment_yea_info_id;
insert_kr_ass_yea_info(
p_assignment_id => p_assignment_id,
p_target_year => p_target_year,
p_information_type => p_information_type,
p_information1 => p_information1,
p_information2 => p_information2,
p_information3 => p_information3,
p_information4 => p_information4,
p_information5 => p_information5,
p_information6 => p_information6,
p_information7 => p_information7,
p_information8 => p_information8,
p_information9 => p_information9,
p_information10 => p_information10,
p_information11 => p_information11,
p_information12 => p_information12,
p_information13 => p_information13,
p_information14 => p_information14,
p_information15 => p_information15,
p_information16 => p_information16,
p_information17 => p_information17,
p_information18 => p_information18,
p_information19 => p_information19,
p_information20 => p_information20,
p_information21 => p_information21,
p_information22 => p_information22,
p_information23 => p_information23,
p_information24 => p_information24,
p_information25 => p_information25,
p_information26 => p_information26,
p_information27 => p_information27,
p_information28 => p_information28,
p_information29 => p_information29,
p_information30 => p_information30,
p_information31 => p_information31,
p_information32 => p_information32,
p_information33 => p_information33,
p_information34 => p_information34,
p_information35 => p_information35,
p_assignment_yea_info_id => l_assignment_yea_info_id);
function cei_datetrack_update_mode(
p_contact_extra_info_id in number,
p_effective_start_date in date,
p_effective_end_date in date,
p_effective_date in date)
return varchar2
is
--
l_datetrack_mode varchar2(30);
select 'Y'
from dual
where exists(
select null
from per_contact_extra_info_f
where contact_extra_info_id = p_contact_extra_info_id
and effective_start_date = p_effective_end_date + 1);
l_datetrack_mode := 'UPDATE';
l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
end cei_datetrack_update_mode;
l_update_datetrack_mode varchar2(30);
select pcr.contact_relationship_id
from per_all_assignments_f pa,
per_contact_relationships pcr,
per_all_people_f pp_con
where pa.assignment_id = p_assignment_id
and p_effective_date
between pa.effective_start_date and pa.effective_end_date
and pcr.person_id = pa.person_id
and pp_con.person_id = pcr.contact_person_id
and p_effective_date
between pp_con.effective_start_date and pp_con.effective_end_date
and cnv_ni(pp_con.national_identifier) = p_con_ni;
select pcei.contact_extra_info_id,
pcei.effective_start_date,
pcei.effective_end_date,
pcei.object_version_number,
pcei.cei_information1,
pcei.cei_information2,
pcei.cei_information3,
pcei.cei_information4,
pcei.cei_information5,
pcei.cei_information6,
pcei.cei_information7,
pcei.cei_information8,
pcei.cei_information9,
pcei.cei_information10,
pcei.cei_information11,
pcei.cei_information12,
pcei.cei_information13,
pcei.cei_information14,
pcei.cei_information15,
pcei.cei_information16,
pcei.cei_information17,
pcei.cei_information18,
pcei.cei_information19,
pcei.cei_information20,
pcei.cei_information21,
pcei.cei_information22,
pcei.cei_information23,
pcei.cei_information24,
pcei.cei_information25,
pcei.cei_information26,
pcei.cei_information27,
pcei.cei_information28,
pcei.cei_information29,
pcei.cei_information30
from per_contact_extra_info_f pcei
where pcei.contact_relationship_id = p_contact_relationship_id
and pcei.cei_information_category = p_information_type
and p_effective_date
between pcei.effective_start_date and pcei.effective_end_date;
l_update_datetrack_mode := cei_datetrack_update_mode(
p_contact_extra_info_id => l_contact_extra_info_id,
p_effective_start_date => l_csr_cei_dpnt_info.effective_start_date,
p_effective_end_date => l_csr_cei_dpnt_info.effective_end_date,
p_effective_date => p_effective_date);
hr_contact_extra_info_api.update_contact_extra_info(
p_validate => false,
p_effective_date => p_effective_date,
p_datetrack_update_mode => l_update_datetrack_mode,
p_contact_extra_info_id => l_contact_extra_info_id,
p_object_version_number => l_object_version_number,
p_cei_information1 => nvl(p_information1,l_csr_cei_dpnt_info.cei_information1),
p_cei_information2 => nvl(p_information2,l_csr_cei_dpnt_info.cei_information2),
p_cei_information3 => nvl(p_information3,l_csr_cei_dpnt_info.cei_information3),
p_cei_information4 => nvl(p_information4,l_csr_cei_dpnt_info.cei_information4),
p_cei_information5 => nvl(p_information5,l_csr_cei_dpnt_info.cei_information5),
p_cei_information6 => nvl(p_information6,l_csr_cei_dpnt_info.cei_information6),
p_cei_information7 => nvl(p_information7,l_csr_cei_dpnt_info.cei_information7),
p_cei_information8 => nvl(p_information8,l_csr_cei_dpnt_info.cei_information8),
p_cei_information9 => nvl(p_information9,l_csr_cei_dpnt_info.cei_information9),
p_cei_information10 => nvl(p_information10,l_csr_cei_dpnt_info.cei_information10),
p_cei_information11 => nvl(p_information11,l_csr_cei_dpnt_info.cei_information11),
p_cei_information12 => nvl(p_information12,l_csr_cei_dpnt_info.cei_information12),
p_cei_information13 => nvl(p_information13,l_csr_cei_dpnt_info.cei_information13),
p_cei_information14 => nvl(p_information14,l_csr_cei_dpnt_info.cei_information14),
p_cei_information15 => nvl(p_information15,l_csr_cei_dpnt_info.cei_information15),
p_cei_information16 => nvl(p_information16,l_csr_cei_dpnt_info.cei_information16),
p_cei_information17 => nvl(p_information17,l_csr_cei_dpnt_info.cei_information17),
p_cei_information18 => nvl(p_information18,l_csr_cei_dpnt_info.cei_information18),
p_cei_information19 => nvl(p_information19,l_csr_cei_dpnt_info.cei_information19),
p_cei_information20 => nvl(p_information20,l_csr_cei_dpnt_info.cei_information20),
p_cei_information21 => nvl(p_information21,l_csr_cei_dpnt_info.cei_information21),
p_cei_information22 => nvl(p_information22,l_csr_cei_dpnt_info.cei_information22),
p_cei_information23 => nvl(p_information23,l_csr_cei_dpnt_info.cei_information23),
p_cei_information24 => nvl(p_information24,l_csr_cei_dpnt_info.cei_information24),
p_cei_information25 => nvl(p_information25,l_csr_cei_dpnt_info.cei_information25),
p_cei_information26 => nvl(p_information26,l_csr_cei_dpnt_info.cei_information26),
p_cei_information27 => nvl(p_information27,l_csr_cei_dpnt_info.cei_information27),
p_cei_information28 => nvl(p_information28,l_csr_cei_dpnt_info.cei_information28),
p_cei_information29 => nvl(p_information29,l_csr_cei_dpnt_info.cei_information29),
p_cei_information30 => nvl(p_information30,l_csr_cei_dpnt_info.cei_information30),
p_effective_start_date => l_esd,
p_effective_end_date => l_eed);
select sptei.ayi_information5 pers_ins_prem,
sptei.ayi_information6 disabled_ins_prem,
sptei.ayi_information15 housing_loan_repay,
sptei.ayi_information14 housing_loan_date,
sptei.ayi_information17 lt_housing_loan_interest_rep,
sptei.ayi_information26 lt_housing_loan_int_repay_1,
sptei2.ayi_information2 lt_housing_loan_int_repay_2,
sptei3.ayi_information6 lt_2012_fixed_intr_wo_grace,
sptei3.ayi_information7 lt_2012_housing_other_intr,
sptei.ayi_information9 med_exp_aged,
sptei.ayi_information24 med_exp_emp,
sptei.ayi_information7 med_exp,
sptei.ayi_information19 political_donation1,
sptei.ayi_information10 ee_educ_exp,
sptei2.ayi_information2 ee_occupation_educ_exp,
tei.ayi_information3 pers_pension_prem,
tei.ayi_information20 small_bus_install,
tei.ayi_information7 credit_card_exp,
tei.ayi_information13 cash_receipt_exp,
tei.ayi_information10 direct_card_exp,
tei2.ayi_information3 credit_tm_exp,
tei2.ayi_information5 cash_tm_exp,
tei2.ayi_information7 direct_tm_exp,
eed.ayi_information6 ins_exp_nts,
eed.ayi_information2 med_exp_nts,
eed.ayi_information3 educ_exp_nts,
eed.ayi_information4 card_exp_nts,
eed.ayi_information8 card_tm_nts,
eed.ayi_information7 direct_payment_nts,
eed.ayi_information9 direct_tm_nts,
eed.ayi_information5 don_exp_nts,
--
es.ayi_information3 entry_status,
--
sptei.ayi_information11 housing_saving_type,
sptei.ayi_information12 housing_saving,
sptei.ayi_information13 housing_purchase_date,
sptei.ayi_information16 lt_housing_loan_date,
sptei.ayi_information18 donation1,
sptei.ayi_information2 hi_prem,
sptei.ayi_information20 political_donation2,
sptei.ayi_information21 political_donation3,
sptei.ayi_information22 donation2,
sptei.ayi_information23 donation3,
sptei.ayi_information25 lt_housing_loan_date_1,
sptei.ayi_information27 mfr_marriage_occasions,
sptei.ayi_information28 mfr_funeral_occasions,
sptei.ayi_information29 mfr_relocation_occasions,
sptei.ayi_information3 ei_prem,
sptei.ayi_information30 esoa_donation,
sptei.ayi_information4 pers_ins_name,
sptei.ayi_information8 med_exp_disabled,
fwtbi.ayi_information1 immigration_purpose,
fwtbi.ayi_information2 contract_date,
fwtbi.ayi_information3 expiry_date,
fwtbi.ayi_information4 stax_applicable_flag,
fwtbi.ayi_information5 fw_application_date,
fwtbi.ayi_information6 fw_submission_date,
ovtbi.ayi_information1 tax_paid_date,
ovtbi.ayi_information10 ovs_submission_date,
ovtbi.ayi_information11 kr_ovs_location,
ovtbi.ayi_information12 kr_ovs_work_period,
ovtbi.ayi_information13 kr_ovs_responsibility,
ovtbi.ayi_information2 territory_code,
ovtbi.ayi_information3 currency_code,
ovtbi.ayi_information4 taxable,
ovtbi.ayi_information5 taxable_subj_tax_break,
ovtbi.ayi_information6 tax_break_rate,
ovtbi.ayi_information7 tax_foreign_currency,
ovtbi.ayi_information8 tax,
ovtbi.ayi_information9 ovs_application_date,
tbi.ayi_information2 housing_loan_interest_repay,
tbi.ayi_information3 stock_saving,
tbi.ayi_information4 lt_stock_saving1,
tbi.ayi_information5 lt_stock_saving2,
tei.ayi_information11 dpnt_direct_exp,
tei.ayi_information12 giro_tuition_paid_exp,
tei.ayi_information2 np_prem,
tei.ayi_information4 pers_pension_saving,
tei.ayi_information5 invest_partnership_fin1,
tei.ayi_information6 invest_partnership_fin2,
tei.ayi_information8 emp_stock_own_plan_contri,
tei.ayi_information9 credit_card_exp_dpnt,
tei.ayi_information15 pen_prem,
sptei2.ayi_information11 ltci_prem
from per_kr_assignment_yea_info sptei,
per_kr_assignment_yea_info sptei2,
per_kr_assignment_yea_info sptei3,
per_kr_assignment_yea_info fwtbi,
per_kr_assignment_yea_info ovtbi,
per_kr_assignment_yea_info tbi,
per_kr_assignment_yea_info tei,
per_kr_assignment_yea_info tei2,
per_kr_assignment_yea_info eed,
per_kr_assignment_yea_info es
where sptei.assignment_id = p_assignment_id
and sptei.target_year = g_target_yyyy
and sptei.information_type = 'KR_YEA_SP_TAX_EXEM_INFO'
and sptei2.assignment_id = sptei.assignment_id
and sptei2.target_year = sptei.target_year
and sptei2.information_type = 'KR_YEA_SP_TAX_EXEM_INFO2'
and sptei3.assignment_id = sptei.assignment_id
and sptei3.target_year = sptei.target_year
and sptei3.information_type = 'KR_YEA_SP_TAX_EXEM_INFO3'
and fwtbi.assignment_id (+) = sptei.assignment_id
and fwtbi.target_year (+) = sptei.target_year
and fwtbi.information_type (+) = 'KR_YEA_FW_TAX_BREAK_INFO'
and ovtbi.assignment_id = sptei.assignment_id
and ovtbi.target_year = sptei.target_year
and ovtbi.information_type = 'KR_YEA_OVS_TAX_BREAK_INFO'
and tbi.assignment_id (+) = sptei.assignment_id
and tbi.target_year (+) = sptei.target_year
and tbi.information_type (+) = 'KR_YEA_TAX_BREAK_INFO'
and tei.assignment_id = sptei.assignment_id
and tei.target_year = sptei.target_year
and tei.information_type = 'KR_YEA_TAX_EXEM_INFO'
and tei2.assignment_id = sptei.assignment_id
and tei2.target_year = sptei.target_year
and tei2.information_type = 'KR_YEA_TAX_EXEM_INFO2'
and eed.assignment_id (+) = sptei.assignment_id
and eed.target_year (+) = sptei.target_year
and eed.information_type (+) = 'KR_YEA_EMP_EXPENSE_DETAILS'
and es.assignment_id = sptei.assignment_id
and es.target_year = sptei.target_year
and es.information_type = 'KR_YEA_ENTRY_STATUS';
select max(fnd_number.canonical_to_number(nvl(ayi_information10,0)))
from per_kr_assignment_yea_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
and ayi_information_category = 'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
and target_year = g_target_yyyy;
select sum(to_number(decode(ayi_information7,
'0',to_char(nvl(fnd_number.canonical_to_number(ayi_information3),0) + nvl(fnd_number.canonical_to_number(ayi_information11),0)),null))) emp,
sum(to_number(decode(ayi_information7,
'0',null,to_char(nvl(fnd_number.canonical_to_number(ayi_information3),0) + nvl(fnd_number.canonical_to_number(ayi_information11),0))))) dep,
sum(to_number(decode(ayi_information9,
'B',to_char(nvl(fnd_number.canonical_to_number(ayi_information3),0) + nvl(fnd_number.canonical_to_number(ayi_information11),0)),null))) aged
from per_kr_assignment_yea_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
and ayi_information_category = 'KR_YEA_DETAIL_MEDICAL_EXP_INFO'
and target_year = g_target_yyyy
and ayi_information13 <> '1';
select sum(to_number(decode(ayi_information5,
'10',to_char(nvl(fnd_number.canonical_to_number(ayi_information3),0)),null))) statutory,
sum(to_number(decode(ayi_information5,
'20',to_char(nvl(fnd_number.canonical_to_number(ayi_information3),0)),null))) political,
sum(to_number(decode(ayi_information5,
'21',to_char(nvl(fnd_number.canonical_to_number(ayi_information3),0)),null))) promotional,
sum(to_number(decode(ayi_information5,
'30',to_char(nvl(fnd_number.canonical_to_number(ayi_information3),0)),null))) special,
sum(to_number(decode(ayi_information5,
'31',to_char(nvl(fnd_number.canonical_to_number(ayi_information3),0)),null))) publicLegal,
sum(to_number(decode(ayi_information5,
'40',to_char(nvl(fnd_number.canonical_to_number(ayi_information3),0)),null))) specified,
sum(to_number(decode(ayi_information5,
'41',to_char(nvl(fnd_number.canonical_to_number(ayi_information3),0)),null))) religious,
sum(to_number(decode(ayi_information5,
'42',to_char(nvl(fnd_number.canonical_to_number(ayi_information3),0)),null))) esoa,
sum(to_number(decode(ayi_information5,
'50',to_char(nvl(fnd_number.canonical_to_number(ayi_information3),0)),null))) other
from per_kr_assignment_yea_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DETAIL_DONATION_INFO'
and ayi_information_category = 'KR_YEA_DETAIL_DONATION_INFO'
and target_year = g_target_yyyy;
select sum(to_number(decode(ayi_information2,
'1',to_char(nvl(fnd_number.canonical_to_number(ayi_information5),0)),null))) employment,
sum(to_number(decode(ayi_information2,
'2',to_char(nvl(fnd_number.canonical_to_number(ayi_information5),0)),null))) scientific
from per_kr_assignment_yea_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_SEP_PEN_DETAILS'
and ayi_information_category = 'KR_YEA_SEP_PEN_DETAILS'
and target_year = g_target_yyyy;
select sum(to_number(decode(ayi_information2,
'1',to_char(nvl(fnd_number.canonical_to_number(ayi_information5),0)),null))) year1,
sum(to_number(decode(ayi_information2,
'2',to_char(nvl(fnd_number.canonical_to_number(ayi_information5),0)),null))) year2,
sum(to_number(decode(ayi_information2,
'3',to_char(nvl(fnd_number.canonical_to_number(ayi_information5),0)),null))) year3
from per_kr_assignment_yea_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_LT_STOCK_SAVING_DETAILS'
and ayi_information_category = 'KR_YEA_LT_STOCK_SAVING_DETAILS'
and target_year = g_target_yyyy;
select sum(to_number(decode(ayi_information2,
'2',to_char(nvl(fnd_number.canonical_to_number(ayi_information5),0)),null))) pen_saving
from per_kr_assignment_yea_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_PEN_SAVING_DETAILS'
and ayi_information_category = 'KR_YEA_PEN_SAVING_DETAILS'
and target_year = g_target_yyyy;
l_med_tbl.delete;
l_don_tbl_tmp.delete;
l_don_tbl.delete;
l_educ_tbl.delete;
l_saving_tbl.delete;
l_cards_tbl.delete;
l_lthouse_tbl.delete;
l_uniform_tbl.delete;
l_sep_pen_saving_tbl.delete;
l_hou_saving_tbl.delete;
l_lts_saving_tbl.delete;
l_dpnt_tbl.delete;
l_dpnt_ind_tbl.delete;
select substr(l_lthouse_tbl(j02cnt).start_date,1,4) into l_start_dt from dual;
l_saving_tbl.delete;
l_saving_tbl.delete;
delete_kr_ass_yea_info(
p_assignment_id => p_assignment_id,
p_target_year => g_target_yyyy,
p_information13 => '1',
p_information_type => 'KR_YEA_DETAIL_MEDICAL_EXP_INFO');
insert_kr_ass_yea_info(
p_assignment_id => p_assignment_id,
p_target_year => g_target_yyyy,
p_information_type => 'KR_YEA_DETAIL_MEDICAL_EXP_INFO',
p_information1 => fnd_date.date_to_canonical(g_target_eoy),
p_information3 => l_ass_med_tbl(i).paid_amount,
p_information7 => get_relship(p_assignment_id,'KR_YEA_DETAIL_MEDICAL_EXP_INFO',l_ass_med_tbl(i).resident_registration_no),
p_information8 => cnv_reg(l_ass_med_tbl(i).resident_registration_no),
p_information9 => l_ass_med_tbl(i).disabled_or_aged,
p_information10 => fnd_number.number_to_canonical(l_max_priority + c_priority_inc * (i + 1)),
p_information13 => l_ass_med_tbl(i).source_code,
p_assignment_yea_info_id => l_assignment_yea_info_id);
delete_kr_ass_yea_info(
p_assignment_id => p_assignment_id,
p_target_year => g_target_yyyy,
p_information15 => '1',
p_information_type => 'KR_YEA_DETAIL_DONATION_INFO');
insert_kr_ass_yea_info(
p_assignment_id => p_assignment_id,
p_target_year => g_target_yyyy,
p_information_type => 'KR_YEA_DETAIL_DONATION_INFO',
p_information1 => fnd_date.date_to_canonical(g_target_eoy),
p_information2 => l_ass_don_tbl(i).donated_date,
p_information3 => l_ass_don_tbl(i).donated_amount,
p_information5 => l_ass_don_tbl(i).donation_code,
p_information7 => cnv_reg(l_ass_don_tbl(i).recipient_registration_number),
p_information8 => l_ass_don_tbl(i).recipient_name,
p_information12 => get_relship(p_assignment_id,'KR_YEA_DETAIL_DONATION_INFO',l_ass_don_tbl(i).ni_num),
p_information13 => cnv_reg(l_ass_don_tbl(i).ni_num),
p_information14 => l_ass_don_tbl(i).donator_name,
p_information15 => l_ass_don_tbl(i).donation_detail_type,
p_assignment_yea_info_id => l_assignment_yea_info_id);
pay_kr_yea_sshr_utils_pkg.update_don_tot_data(
p_assignment_id => p_assignment_id,
p_target_year => g_target_yyyy ,
p_stat_self => p_stat_self ,
p_stat_spouse => p_stat_spouse ,
p_stat_child => p_stat_child ,
p_pol_self => p_pol_self ,
p_tax_self => p_tax_self ,
p_tax_spouse => p_tax_spouse ,
p_tax_child => p_tax_child ,
p_pledt_self => p_pledt_self ,
p_pledt_spouse => p_pledt_spouse,
p_pledt_child => p_pledt_child ,
p_esoa_self => p_esoa_self ,
p_spec_self => p_spec_self ,
p_spec_spouse => p_spec_spouse ,
p_spec_child => p_spec_child ,
p_rel_self => p_rel_self ,
p_rel_spouse => p_rel_spouse ,
p_rel_child => p_rel_child ,
p_oth_self => p_oth_self ,
p_oth_spouse => p_oth_spouse ,
p_oth_child => p_oth_child ,
p_stat_parent => p_stat_parent ,
p_stat_bro_sis => p_stat_bro_sis,
p_tax_parent => p_tax_parent ,
p_tax_bro_sis => p_tax_bro_sis ,
p_pledt_parent => p_pledt_parent ,
p_pledt_bro_sis => p_pledt_bro_sis ,
p_spec_parent => p_spec_parent ,
p_spec_bro_sis => p_spec_bro_sis ,
p_rel_parent => p_rel_parent ,
p_rel_bro_sis => p_rel_bro_sis,
p_oth_parent => p_oth_parent ,
p_oth_bro_sis => p_oth_bro_sis );
delete_kr_ass_yea_info(
p_assignment_id => p_assignment_id,
p_target_year => g_target_yyyy,
p_information6 => '1',
p_information_type => 'KR_YEA_DPNT_EDUC_TAX_EXEM_INFO');
insert_kr_ass_yea_info(
p_assignment_id => p_assignment_id,
p_target_year => g_target_yyyy,
p_information_type => 'KR_YEA_DPNT_EDUC_TAX_EXEM_INFO',
p_information1 => fnd_date.date_to_canonical(g_target_eoy),
p_information2 => get_relship(p_assignment_id,'KR_YEA_DPNT_EDUC_TAX_EXEM_INFO',l_ass_educ_tbl(i).ni_num),
p_information3 => l_ass_educ_tbl(i).school_type,
p_information4 => l_ass_educ_tbl(i).exp,
p_information5 => cnv_reg(l_ass_educ_tbl(i).ni_num),
p_information6 => l_ass_educ_tbl(i).nts_other,
p_information7 => l_ass_educ_tbl(i).dpnt_name,
p_assignment_yea_info_id => l_assignment_yea_info_id);
delete_kr_ass_yea_info(
p_assignment_id => p_assignment_id,
p_target_year => g_target_yyyy,
p_information6 => '1',
p_information_type => 'KR_YEA_DPNT_EDUC_TAX_EXEM_INFO');
insert_kr_ass_yea_info(
p_assignment_id => p_assignment_id,
p_target_year => g_target_yyyy,
p_information_type => 'KR_YEA_DPNT_EDUC_TAX_EXEM_INFO',
p_information1 => fnd_date.date_to_canonical(g_target_eoy),
p_information2 => get_relship(p_assignment_id,'KR_YEA_DPNT_EDUC_TAX_EXEM_INFO',l_ass_uniform_tbl(i).key),
p_information3 => 'H',
p_information4 => l_ass_uniform_tbl(i).val,
p_information5 => cnv_reg(l_ass_uniform_tbl(i).key),
p_information6 => '1',
p_information7 => l_ass_uniform_tbl(i).empname,
p_assignment_yea_info_id => l_assignment_yea_info_id);
delete_kr_ass_yea_info(
p_assignment_id => p_assignment_id,
p_target_year => g_target_yyyy,
p_information_type => 'KR_YEA_SEP_PEN_DETAILS');
insert_kr_ass_yea_info(
p_assignment_id => p_assignment_id,
p_target_year => g_target_yyyy,
p_information_type => 'KR_YEA_SEP_PEN_DETAILS',
p_information1 => fnd_date.date_to_canonical(g_target_eoy),
p_information2 => get_type('KR_YEA_SEP_PEN_DETAILS',l_ass_saving_tbl(i).type),
p_information3 => '110', --get_fin(l_ass_saving_tbl(i).financial_inst),
p_information4 => l_ass_saving_tbl(i).account_num,
p_information5 => l_ass_saving_tbl(i).contributed_amt,
p_information6 => l_ass_saving_tbl(i).priority,
p_assignment_yea_info_id => l_assignment_yea_info_id);
delete_kr_ass_yea_info(
p_assignment_id => p_assignment_id,
p_target_year => g_target_yyyy,
p_information_type => 'KR_YEA_PEN_SAVING_DETAILS');
insert_kr_ass_yea_info(
p_assignment_id => p_assignment_id,
p_target_year => g_target_yyyy,
p_information_type => 'KR_YEA_PEN_SAVING_DETAILS',
p_information1 => fnd_date.date_to_canonical(g_target_eoy),
p_information2 => l_ass_saving_tbl(i).type,
p_information3 => '110', --get_fin(l_ass_saving_tbl(i).financial_inst),
p_information4 => l_ass_saving_tbl(i).account_num,
p_information5 => l_ass_saving_tbl(i).contributed_amt,
p_information6 => l_ass_saving_tbl(i).priority,
p_assignment_yea_info_id => l_assignment_yea_info_id);
delete_kr_ass_yea_info(
p_assignment_id => p_assignment_id,
p_target_year => g_target_yyyy,
p_information_type => 'KR_YEA_LT_STOCK_SAVING_DETAILS');
insert_kr_ass_yea_info(
p_assignment_id => p_assignment_id,
p_target_year => g_target_yyyy,
p_information_type => 'KR_YEA_LT_STOCK_SAVING_DETAILS',
p_information1 => fnd_date.date_to_canonical(g_target_eoy),
p_information2 => l_ass_saving_tbl(i).type,
p_information3 => '110', --get_fin(l_ass_saving_tbl(i).financial_inst),
p_information4 => l_ass_saving_tbl(i).account_num,
p_information5 => l_ass_saving_tbl(i).contributed_amt,
p_information6 => l_ass_saving_tbl(i).priority,
p_assignment_yea_info_id => l_assignment_yea_info_id);
delete_kr_ass_yea_info(
p_assignment_id => p_assignment_id,
p_target_year => g_target_yyyy,
p_information_type => 'KR_YEA_HOU_SAVING_DETAILS');
insert_kr_ass_yea_info(
p_assignment_id => p_assignment_id,
p_target_year => g_target_yyyy,
p_information_type => 'KR_YEA_HOU_SAVING_DETAILS',
p_information1 => fnd_date.date_to_canonical(g_target_eoy),
p_information2 => get_type('KR_YEA_HOU_SAVING_DETAILS',l_ass_saving_tbl(i).type),
p_information3 => '110', --get_fin(l_ass_saving_tbl(i).financial_inst),
p_information4 => l_ass_saving_tbl(i).account_num,
p_information5 => l_ass_saving_tbl(i).contributed_amt,
p_information6 => l_ass_saving_tbl(i).priority,
p_assignment_yea_info_id => l_assignment_yea_info_id);
delete_pdf(p_file_id);
p_force_update => 'Y',
p_assignment_yea_info_id => l_assignment_yea_info_id);
update per_kr_assignment_yea_info
set ayi_information14 = decode(nvl(p_error_code,'X'),'X',decode(nvl(p_status,'X'),'C',c_upload_status,ayi_information14),c_error_status),
ayi_information16 = p_status,
ayi_information17 = p_error_code,
object_version_number = object_version_number + 1
where assignment_yea_info_id = p_assignment_yea_info_id;
delete_session;
select file_id,
file_name
from fnd_lobs
where file_id = p_file_id;
select pa.assignment_id,
pp.last_name||pp.first_name emp_name,
substrb(pp.national_identifier,1,6) res_reg_num,
pp.employee_number emp_num
from per_people_f pp,
per_periods_of_service ppos,
per_assignments_f pa,
hr_organization_information hoi,
per_business_groups_perf pbg
where pbg.legislation_code = c_legislation_code
and pbg.business_group_id = nvl(g_business_group_id,pbg.business_group_id)
and hoi.organization_id = pbg.business_group_id
and hoi.org_information_context = 'KR_YEA_PDF_SETUP_BG'
and hoi.org_information1 is not null
and pa.business_group_id = hoi.organization_id
and pa.establishment_id = nvl(g_business_place_id,pa.establishment_id)
and decode(to_char(nvl(g_assignment_set_id,-1)),
'-1','Y',
hr_assignment_set.assignment_in_set(g_assignment_set_id,pa.assignment_id)) = 'Y'
and pa.assignment_type = 'E'
and pa.effective_start_date <= g_target_eoy
and pa.effective_end_date >= g_target_soy
and ppos.period_of_service_id = pa.period_of_service_id
and nvl(ppos.final_process_date,g_target_eoy)
between g_target_soy and g_target_eoy
and pp.person_id = pa.person_id
and pp.effective_start_date <= g_target_eoy
and pp.effective_end_date >= g_target_soy
/* fetch latest emp information */
and not exists(
select null
from per_people_f pp2
where pp2.person_id = pa.person_id
and pp2.effective_start_date <= g_target_eoy
and pp2.effective_end_date >= g_target_soy
and pp2.effective_start_date < pp.effective_start_date)
and ((nvl(g_map_key,'FULL_NAME') = 'FULL_NAME'
and p_map_file_name like pp.last_name||pp.first_name||'('||substrb(pp.national_identifier,1,6)||')%')
or (g_map_key = 'EMPLOYEE_NUMBER'
and substrb(p_map_file_name,1,lengthb(p_map_file_name) - lengthb(c_file_extention)) = pp.employee_number));
fnd_gfm.delete_lob(l_csr_file.file_id);
select fl.file_id
from fnd_lobs fl
where fl.program_name = 'PAYKRYPIM'
and fl.program_tag = to_char(p_business_group_id)||':'||to_char(p_target_year)
and not exists(
select null
from per_kr_assignment_yea_info pkayi
where pkayi.information_type = 'KR_YEA_ENTRY_STATUS'
and pkayi.ayi_information_category = 'KR_YEA_ENTRY_STATUS'
and pkayi.target_year = p_target_year
and pkayi.ayi_information15 = fnd_number.number_to_canonical(fl.file_id));
procedure delete_pdf(
p_file_id in number,
p_with_status in varchar2 default null)
is
--
l_proc varchar2(80) := c_package||'delete_pdf';
select file_id,
to_number(substrb(program_tag,instrb(program_tag,':',-1)+1)) target_year
from fnd_lobs
where file_id = p_file_id;
fnd_gfm.delete_lob(l_csr_file.file_id);
update per_kr_assignment_yea_info
set ayi_information14 = decode(p_with_status,'Y',null,ayi_information14),
ayi_information15 = null,
ayi_information16 = decode(p_with_status,'Y',null,ayi_information16),
object_version_number = object_version_number + 1
where information_type = 'KR_YEA_ENTRY_STATUS'
and ayi_information_category = 'KR_YEA_ENTRY_STATUS'
and target_year = l_csr_file.target_year
and ayi_information15 = fnd_number.number_to_canonical(l_csr_file.file_id);
end delete_pdf;
procedure mass_delete_pdf(
p_business_group_id in number,
p_target_year in number,
p_with_status in varchar2 default null)
is
--
l_proc varchar2(80) := c_package||'mass_delete_pdf';
select file_id
from fnd_lobs
where program_name = 'PAYKRYPIM'
and to_number(substrb(program_tag,0,instrb(program_tag,':')-1)) = p_business_group_id
and substrb(program_tag,instrb(program_tag,':',-1)+1) = to_char(p_target_year);
delete_pdf(l_csr_mass_file.file_id,p_with_status);
end mass_delete_pdf;