[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT FF.FORMULA_ID
FROM per_all_assignments_f asg1
,pay_element_links_f el
,pay_element_types_f et
,pay_input_values_f iv1
,pay_element_entries_f ee
,pay_element_entry_values_f eev1
, ff_formulas_f ff
WHERE asg1.assignment_id = p_assignment_id
AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
AND et.element_name = 'Holiday Pay Information'
AND et.legislation_code = 'FI'
AND iv1.element_type_id = et.element_type_id
AND iv1.name = p_input_value
AND el.business_group_id = asg1.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = asg1.assignment_id
AND ee.element_link_id = el.element_link_id
AND eev1.element_entry_id = ee.element_entry_id
AND eev1.input_value_id = iv1.input_value_id
AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date
AND p_effective_date BETWEEN el.effective_start_date AND el.effective_end_date
AND p_effective_date BETWEEN et.effective_start_date AND et.effective_end_date
AND p_effective_date BETWEEN iv1.effective_start_date AND iv1.effective_end_date
AND FF.FORMULA_NAME=eev1.SCREEN_ENTRY_VALUE;
/* SELECT PRL_INFORMATION1
FROM PAY_PAYROLLS PP
WHERE pp.business_group_id +0 = p_business_group_id
and pp.payroll_id = p_payroll_id
and trunc(p_date_earned) between pp.effective_start_date and nvl(pp.effective_end_date,p_date_earned)
and PRL_INFORMATION_CATEGORY = 'FI';
SELECT end_date
FROM per_time_periods
WHERE time_definition_id = p_time_definition_id
AND end_date BETWEEN p_payroll_start_date
AND p_payroll_end_date ;
SELECT 1
FROM per_all_assignments_f asg
,pay_element_links_f el
,pay_element_types_f et
,pay_element_entries_f ee
WHERE asg.assignment_id = p_assignment_id
AND et.element_name = p_element_name
AND et.legislation_code = 'FI'
AND el.business_group_id = asg.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = asg.assignment_id
AND ee.element_link_id = el.element_link_id
AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND p_effective_date BETWEEN et.effective_start_date AND et.effective_end_date
AND p_effective_date BETWEEN el.effective_start_date AND el.effective_end_date ;
SELECT FF.FORMULA_ID
FROM per_all_assignments_f asg1
,pay_element_links_f el
,pay_element_types_f et
,pay_input_values_f iv1
,pay_element_entries_f ee
,pay_element_entry_values_f eev1
, ff_formulas_f ff
WHERE asg1.assignment_id = p_assignment_id
AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
AND et.element_name = 'Holiday Pay Information'
AND et.legislation_code = 'FI'
AND iv1.element_type_id = et.element_type_id
AND iv1.name = p_input_value
AND el.business_group_id = asg1.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = asg1.assignment_id
AND ee.element_link_id = el.element_link_id
AND eev1.element_entry_id = ee.element_entry_id
AND eev1.input_value_id = iv1.input_value_id
AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date
AND p_effective_date BETWEEN el.effective_start_date AND el.effective_end_date
AND p_effective_date BETWEEN et.effective_start_date AND et.effective_end_date
AND p_effective_date BETWEEN iv1.effective_start_date AND iv1.effective_end_date
AND FF.FORMULA_NAME=eev1.SCREEN_ENTRY_VALUE;
/* SELECT PRL_INFORMATION1
FROM PAY_PAYROLLS PP
WHERE pp.business_group_id +0 = p_business_group_id
and pp.payroll_id = p_payroll_id
and trunc(p_date_earned) between pp.effective_start_date and nvl(pp.effective_end_date,p_date_earned)
and PRL_INFORMATION_CATEGORY = 'FI';
SELECT eev1.SCREEN_ENTRY_VALUE
FROM per_all_assignments_f asg1
,pay_element_links_f el
,pay_element_types_f et
,pay_input_values_f iv1
,pay_element_entries_f ee
,pay_element_entry_values_f eev1
WHERE asg1.assignment_id = p_assignment_id
AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
AND et.element_name = 'Holiday Pay Accrual Override'
AND et.legislation_code = 'FI'
AND iv1.element_type_id = et.element_type_id
AND iv1.name = p_input_value
AND el.business_group_id = asg1.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = asg1.assignment_id
AND ee.element_link_id = el.element_link_id
AND eev1.element_entry_id = ee.element_entry_id
AND eev1.input_value_id = iv1.input_value_id
AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
SELECT eev1.SCREEN_ENTRY_VALUE
FROM per_all_assignments_f asg1
,pay_element_links_f el
,pay_element_types_f et
,pay_input_values_f iv1
,pay_element_entries_f ee
,pay_element_entry_values_f eev1
WHERE asg1.assignment_id = p_assignment_id
AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
AND et.element_name = 'Holiday Pay Entitlement Override'
AND et.legislation_code = 'FI'
AND iv1.element_type_id = et.element_type_id
AND iv1.name = p_input_value
AND el.business_group_id = asg1.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = asg1.assignment_id
AND ee.element_link_id = el.element_link_id
AND eev1.element_entry_id = ee.element_entry_id
AND eev1.input_value_id = iv1.input_value_id
AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
SELECT sck.segment2
FROM per_all_assignments_f asg1
, hr_soft_coding_keyflex sck
WHERE asg1.assignment_id = p_assignment_id
AND asg1.soft_coding_keyflex_id=sck.soft_coding_keyflex_id
AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date;
SELECT TO_NUMBER(TO_CHAR(ee.effective_start_date, 'J')) julian_effective_date
,eev1.screen_entry_value screen_entry_value
FROM per_all_assignments_f asg1
,per_all_assignments_f asg2
,per_all_people_f per
,pay_element_links_f el
,pay_element_types_f et
,pay_input_values_f iv1
,pay_element_entries_f ee
,pay_element_entry_values_f eev1
WHERE asg1.assignment_id = p_assignment_id
AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date
AND p_effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
AND per.person_id = asg1.person_id
AND asg2.person_id = per.person_id
AND asg2.primary_flag = 'Y'
AND et.element_name = 'Tax Card'
AND et.legislation_code = 'FI'
AND iv1.element_type_id = et.element_type_id
AND iv1.name = p_input_value
AND el.business_group_id = per.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = asg2.assignment_id
AND ee.element_link_id = el.element_link_id
AND eev1.element_entry_id = ee.element_entry_id
AND eev1.input_value_id = iv1.input_value_id
AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
SELECT eev1.screen_entry_value tax_days
FROM per_all_assignments_f asg1
,per_all_assignments_f asg2
,per_all_people_f per
,pay_element_links_f el
,pay_element_types_f et
,pay_input_values_f iv1
,pay_element_entries_f ee
,pay_element_entry_values_f eev1
WHERE asg1.assignment_id = p_assignment_id
AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date
AND per.person_id = asg1.person_id
AND asg2.person_id = per.person_id
AND asg2.primary_flag = 'Y'
AND et.element_name = 'Tax Days Override'
AND et.legislation_code = 'FI'
AND iv1.element_type_id = et.element_type_id
AND iv1.name = p_input_value
AND el.business_group_id = per.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = asg2.assignment_id
AND ee.element_link_id = el.element_link_id
AND eev1.element_entry_id = ee.element_entry_id
AND eev1.input_value_id = iv1.input_value_id
AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
SELECT PRL_INFORMATION1
FROM PAY_PAYROLLS PP
WHERE pp.business_group_id +0 = p_business_group_id
and pp.payroll_id = p_payroll_id
and trunc(p_date_earned) between pp.effective_start_date and nvl(pp.effective_end_date,p_date_earned)
and PRL_INFORMATION_CATEGORY = 'FI';
SELECT TO_NUMBER(TO_CHAR(ee.effective_start_date, 'J')) julian_effective_date
FROM per_all_assignments_f asg1
,per_all_people_f per
,pay_element_links_f el
,pay_element_types_f et
,pay_element_entries_f ee
WHERE asg1.assignment_id = p_assignment_id
AND per.person_id = asg1.person_id
AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date
AND et.element_name = 'Tax'
AND et.legislation_code = 'FI'
AND el.business_group_id = per.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = asg1.assignment_id
AND ee.element_link_id = el.element_link_id
AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date;
SELECT NVL(hoi2.org_information10,'364') org_information10
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id =p_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = p_tax_unit_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='FI_LEGAL_EMPLOYER_DETAILS';
SELECT NVL(hoi2.org_information3,0 ) org_information3 ,
NVL(hoi2.org_information12,'N') org_information11
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id =p_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = p_tax_unit_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='FI_LEGAL_EMPLOYER_DETAILS';
SELECT hoi2.org_information3
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id =p_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = p_tax_unit_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='FI_ACCIDENT_PROVIDERS'
AND p_effective_date between fnd_date.canonical_to_date(hoi2.org_information1) AND
nvl(fnd_date.canonical_to_date(hoi2.org_information2),to_date('31/12/4712','DD/MM/YYYY')) ;
SELECT hoi2.org_information3 org_information3 ,
NVL(hoi2.org_information5,0 ) org_information5 ,
NVL(hoi2.org_information6,0 ) org_information6
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id =p_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = p_tax_unit_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='FI_ACCIDENT_PROVIDERS'
AND p_effective_date between fnd_date.canonical_to_date(hoi2.org_information1) AND
nvl(fnd_date.canonical_to_date(hoi2.org_information2),to_date('31/12/4712','DD/MM/YYYY')) ;
SELECT hoi2.org_information4 org_information4
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id =p_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = p_local_unit_id
AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='FI_LU_ACCIDENT_PROVIDERS'
AND p_effective_date between fnd_date.canonical_to_date(hoi2.org_information1) AND
nvl(fnd_date.canonical_to_date(hoi2.org_information2),to_date('31/12/4712','DD/MM/YYYY')) ;
SELECT PER_INFORMATION15, PER_INFORMATION16, PER_INFORMATION24
FROM per_all_assignments_f asg1
,per_all_people_f per
WHERE asg1.assignment_id = p_assignment_id
AND per.person_id = asg1.person_id
AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date
AND p_effective_date BETWEEN nvl(fnd_date.canonical_to_date(per.per_information14),to_date('01/01/0001','DD/MM/YYYY'))
AND nvl(fnd_date.canonical_to_date(per.per_information20),to_date('31/12/4712','DD/MM/YYYY'));
SELECT NVL(hoi2.org_information4,0 ) org_information4 , NVL(hoi2.org_information7,0 ) org_information7
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id =p_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = p_tax_unit_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='FI_PENSION_PROVIDERS'
AND hoi2.org_information6 = p_pension_num
AND p_effective_date between fnd_date.canonical_to_date(hoi2.org_information1) AND
nvl(fnd_date.canonical_to_date(hoi2.org_information2),to_date('31/12/4712','DD/MM/YYYY'))
AND hoi2.org_information6 IN
(
SELECT NVL(hoi2.org_information1,0 )
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id = p_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = l_local_unit
AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.org_information1 = p_pension_num
AND hoi2.ORG_INFORMATION_CONTEXT='FI_LU_PENSION_PROVIDERS' );
SELECT PER_INFORMATION8
FROM per_all_assignments_f asg1
,per_all_people_f per
WHERE asg1.assignment_id = p_assignment_id
AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date
AND per.person_id = asg1.person_id;
,p_dt_update_mode in varchar2
)
IS
CURSOR CSR_PERSON_EIT
IS
select PERSON_EXTRA_INFO_ID,
object_version_number,
person_id,
information_type,
pei_information_category,
pei_information1,
pei_information2,
pei_information3,
pei_information4,
pei_information5,
pei_information6,
pei_information7
from per_people_extra_info
where information_type='FI_PENSION'
AND PEI_INFORMATION_CATEGORY='FI_PENSION'
AND PEI_INFORMATION3=p_COLUMN_NAME
AND PERSON_ID = P_PERSON_ID;
IF p_dt_update_mode ='UPDATE' or p_dt_update_mode='UPDATE_CHANGE'
THEN
l_Action :='U';
IF p_dt_update_mode ='INSERT_CHANGE' or p_dt_update_mode='UPDATE_CHANGE'
THEN
-- as the changes update mode
-- is called, we need to pass the new value,
-- and the action as insert, and reported as No
-- along with the session date
hr_person_extra_info_api.update_person_extra_info
(
p_person_extra_info_id => lr_PERSON_EIT.person_extra_info_id,
p_object_version_number => L_OVN,
-- p_pei_information_category => lr_PERSON_EIT.pei_information_category,
-- p_pei_information1 => lr_PERSON_EIT.pei_information1,
p_pei_information2 => null,
-- p_pei_information3 => lr_PERSON_EIT.pei_information3,
p_pei_information4 => p_new_value,
p_pei_information5 => 'N',
p_pei_information6 => l_Action,
p_pei_information7 =>FND_DATE.DATE_TO_CANONICAL(p_Session_Date)
);
hr_person_extra_info_api.update_person_extra_info
(
p_person_extra_info_id => lr_PERSON_EIT.person_extra_info_id,
p_object_version_number => L_OVN,
-- p_pei_information_category => lr_PERSON_EIT.pei_information_category,
-- p_pei_information1 => lr_PERSON_EIT.pei_information1,
p_pei_information2 => null,
-- p_pei_information3 => lr_PERSON_EIT.pei_information3,
-- p_pei_information4 => p_new_value,
p_pei_information5 => 'N',
p_pei_information6 => l_Action,
p_pei_information7 =>FND_DATE.DATE_TO_CANONICAL(p_Session_Date)
);
hr_person_extra_info_api.update_person_extra_info
(
p_person_extra_info_id => lr_PERSON_EIT.person_extra_info_id,
p_object_version_number => L_OVN,
--p_pei_information_category => lr_PERSON_EIT.pei_information_category,
--p_pei_information1 => lr_PERSON_EIT.pei_information1,
--p_pei_information2 => lr_PERSON_EIT.pei_information2,
--p_pei_information3 => lr_PERSON_EIT.pei_information3,
--p_pei_information4 => p_new_value,
p_pei_information5 => 'N',
p_pei_information6 => l_Action
);
--hr_utility.trace('Updated Record ==> ' ||lr_PERSON_EIT.person_extra_info_id);
END IF; -- END if of INSERT_CHANGE
PROCEDURE INSERT_OR_UPDATE_PERSON_EIT
(p_person_id IN NUMBER,
p_new_PENSION_JOINING_DATE IN VARCHAR2,
p_old_PENSION_JOINING_DATE in VARCHAR2,
p_new_PENSION_TYPES IN VARCHAR2,
p_old_PENSION_TYPES in VARCHAR2,
p_new_PENSION_INS_NUM IN VARCHAR2,
p_old_PENSION_INS_NUM in VARCHAR2,
p_new_PENSION_GROUP IN VARCHAR2,
p_old_PENSION_GROUP in VARCHAR2,
p_new_LOCAL_UNIT IN VARCHAR2,
p_old_LOCAL_UNIT in VARCHAR2,
p_Session_Date in VARCHAR2,
p_dt_update_mode in varchar2,
p_where IN VARCHAR2 default NULL
)
is
BEGIN
-- if any of the 14,15,16 ,24 has been changed then call the API to insert or update acc
-- PER_INFORMATION14 Pension Joining Date PERSON.LOC_DATE04
-- PER_INFORMATION15 Pension Types PERSON.LOC_ITEM18
-- PER_INFORMATION16 Pension Group PERSON.LOC_INFORMATION_C01
-- PER_INFORMATION24 Pension Insurance Number PERSON.LOC_INFORMATION_C06
-- PER_INFORMATION17 Planned Retirement age PERSON.LOC_NUM03
--hr_utility.trace_on(NULL,'TELL');
INS_OR_UPD_PERSON_EIT_COLUMN(p_person_id,p_new_LOCAL_UNIT,p_Session_Date,'Local Unit',p_dt_update_mode);
INS_OR_UPD_PERSON_EIT_COLUMN(p_person_id,p_new_PENSION_JOINING_DATE,p_Session_Date,'Pension Joining Date','INSERT');
hr_utility.trace('Calling the Column update ');
INS_OR_UPD_PERSON_EIT_COLUMN(p_person_id,p_new_PENSION_JOINING_DATE,p_Session_Date,'Pension Joining Date',p_dt_update_mode);
INS_OR_UPD_PERSON_EIT_COLUMN(p_person_id,p_new_PENSION_GROUP,p_Session_Date,'Pension Group','INSERT');
INS_OR_UPD_PERSON_EIT_COLUMN(p_person_id,p_new_PENSION_GROUP,p_Session_Date,'Pension Group',p_dt_update_mode);
INS_OR_UPD_PERSON_EIT_COLUMN(p_person_id,p_new_PENSION_INS_NUM,p_Session_Date,'Insurance Number','INSERT');
INS_OR_UPD_PERSON_EIT_COLUMN(p_person_id,p_new_PENSION_INS_NUM,p_Session_Date,'Insurance Number',p_dt_update_mode);
INS_OR_UPD_PERSON_EIT_COLUMN(p_person_id,p_new_PENSION_TYPES,p_Session_Date,'Pension Types','INSERT');
INS_OR_UPD_PERSON_EIT_COLUMN(p_person_id,p_new_PENSION_TYPES,p_Session_Date,'Pension Types',p_dt_update_mode);
INS_OR_UPD_PERSON_EIT_COLUMN(p_person_id,p_new_PENSION_JOINING_DATE,p_Session_Date,'Pension Joining Date','INSERT_CHANGE');
-- if the Old was TYEL then change the joining date value and insert and Not reported
-- then dont change the group value but, make insert and Not reported
INS_OR_UPD_PERSON_EIT_COLUMN(p_person_id,p_new_PENSION_GROUP,p_Session_Date,'Pension Group','INSERT');
-- then dont change the Type value but, make update and Not reported
END IF;
END INSERT_OR_UPDATE_PERSON_EIT;
SELECT replace(trim(to_char(to_number(l_time),'00.00')),'.',':') FROM dual;
SELECT p_duration + (((SUBSTR(l_day_end_time,1,2)*60 + SUBSTR(l_day_end_time,4,2)) -
(SUBSTR(l_start_time,1,2)*60 + SUBSTR(l_start_time,4,2)))/60)
INTO p_duration
FROM DUAL;
SELECT p_duration + (((SUBSTR(l_end_time,1,2)*60 + SUBSTR(l_end_time,4,2)) -
(SUBSTR(l_day_start_time,1,2)*60 + SUBSTR(l_day_start_time,4,2)) + 1)/60)
INTO p_duration
FROM DUAL;
SELECT p_duration + ((TRUNC(l_schedule(l_idx).END_DATE_TIME) - TRUNC(l_schedule(l_idx).START_DATE_TIME) - 1) * 24)
INTO p_duration
FROM DUAL;
SELECT p_duration + (((SUBSTR(l_end_time,1,2)*60 + SUBSTR(l_end_time,4,2)) -
(SUBSTR(l_start_time,1,2)*60 + SUBSTR(l_start_time,4,2)))/60)
INTO p_duration
FROM DUAL;
g_fi_cache_table.delete;
l_updated boolean;
l_updated:= FALSE;
l_updated:= true;
if (not l_updated) then
If (g_fi_cache_table.count > 0) then
l_cache_index := g_fi_cache_table.last + 1;
FUNCTION delete_cache_table_row(p_cache_code in varchar2)RETURN NUMBER
is
l_cache_index number;
g_fi_cache_table.delete(l_cache_index);
SELECT eev1.SCREEN_ENTRY_VALUE
FROM per_all_assignments_f asg1
,pay_element_links_f el
,pay_element_types_f et
,pay_input_values_f iv1
,pay_element_entries_f ee
,pay_element_entry_values_f eev1
WHERE asg1.assignment_id = p_assignment_id
AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
AND et.element_name =p_element_name
AND et.legislation_code = 'FI'
AND iv1.element_type_id = et.element_type_id
AND iv1.name = p_input_value_name
AND el.business_group_id = asg1.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = asg1.assignment_id
AND ee.element_link_id = el.element_link_id
AND eev1.element_entry_id = ee.element_entry_id
AND eev1.input_value_id = iv1.input_value_id
AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
SELECT HOURLY_SALARIED_CODE FROM PER_ALL_ASSIGNMENTS_F WHERE ASSIGNMENT_ID=p_assignment_id and
p_date_earned between effective_start_date and effective_end_Date;
SELECT PRD_INFORMATION1
,PRD_INFORMATION2
,PRD_INFORMATION3
,PRD_INFORMATION4
,PRD_INFORMATION5
FROM per_time_periods
WHERE payroll_id = p_payroll_id
AND start_date = p_payroll_start_date
AND end_date = p_payroll_end_date;