[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
,P_LAST_UPDATED_DATE IN DATE
,P_ASSIGNMENT_ID IN NUMBER
,P_EMPLOYEE_ID IN NUMBER
,P_ASSIGNMENT_EXTRA_INFO_ID IN NUMBER
,P_ENTRY_DATE IN DATE DEFAULT NULL
,p_warnings OUT NOCOPY VARCHAR2
)
IS
CURSOR c_element_name(p_business_group_id NUMBER)
IS
SELECT hoi.org_information3
FROM hr_organization_information hoi
WHERE hoi.organization_id = p_business_group_id
AND org_information_context='PER_IN_REIMBURSE_ELEMENTS';
SELECT types.element_type_id
,link.element_link_id
FROM per_assignments_f assgn
, pay_element_links_f link
, pay_element_types_f types
WHERE assgn.assignment_id = p_assignment_id
AND link.element_link_id = pay_in_utils.get_element_link_id(p_assignment_id
,P_ENTRY_DATE
,types.element_type_id
)
AND (types.processing_type = 'R' OR assgn.payroll_id IS NOT NULL)
AND link.business_group_id = assgn.business_group_id
AND link.element_type_id = types.element_type_id
AND types.element_type_id = p_element_name
AND p_effective_date BETWEEN assgn.effective_start_date AND assgn.effective_end_date
AND p_effective_date BETWEEN link.effective_start_date AND link.effective_end_date
AND p_effective_date BETWEEN types.effective_start_date AND types.effective_end_date;
SELECT inputs.name name
, inputs.input_value_id id
, inputs.default_value value
FROM pay_element_types_f types
, pay_input_values_f inputs
WHERE types.element_type_id = p_element_type_id
AND inputs.element_type_id = types.element_type_id
AND p_effective_date BETWEEN types.effective_start_date AND types.effective_end_date
AND p_effective_date BETWEEN inputs.effective_start_date AND inputs.effective_end_date
ORDER BY inputs.display_sequence;
SELECT object_version_number
,effective_start_date
FROM pay_element_entries_f
WHERE element_entry_id = p_element_entryid;
SELECT screen_entry_value
FROM pay_element_entry_values_f
WHERE element_entry_id = p_element_entryid
AND input_value_id = p_input;
SELECT pee.element_entry_id
FROM pay_element_entries_f pee
WHERE pee.element_type_id = p_element_type_id
AND pee.assignment_id = p_assignment_id
AND TO_CHAR(p_effective_date,'RRRR') = TO_CHAR(pee.effective_start_date,'RRRR') ;
SELECT element_type_id
FROM pay_element_entries_f
WHERE element_entry_id = p_element_entryid;
SELECT pae.aei_information9,
pae.aei_information10
FROM per_assignment_extra_info pae
WHERE pae.assignment_extra_info_id = p_assignment_extra_info_id;
SELECT element_name
FROM pay_element_types_f
WHERE element_type_id = p_element_type_id ;
IS SELECT nvl(peev.screen_entry_value,'N')
FROM pay_element_entry_values_f peev,
pay_input_values_f piv
WHERE peev.element_entry_id = p_element_entry_id
AND peev.input_value_id = piv.input_value_id
AND piv.name ='Carryover from Prev Block';
pay_in_utils.trace('P_LAST_UPDATED_DATE',TO_CHAR (P_LAST_UPDATED_DATE));
SELECT 1 INTO l_session FROM fnd_sessions WHERE SESSION_ID = USERENV('SESSIONID') AND ROWNUM=1;
INSERT INTO fnd_sessions(session_id,effective_date) VALUES (USERENV('SESSIONID'),P_ENTRY_DATE);
pay_element_entry_api.update_element_entry
(p_datetrack_update_mode => hr_api.g_correction
,p_effective_date => l_effective_start_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => p_element_entry_id
,p_object_version_number => l_object_version_no
,p_input_value_id1 => l_input_values_rec(1).input_value_id
,p_input_value_id2 => l_input_values_rec(2).input_value_id
,p_input_value_id3 => l_input_values_rec(3).input_value_id
,p_input_value_id4 => l_input_values_rec(4).input_value_id
,p_input_value_id5 => l_input_values_rec(5).input_value_id
,p_input_value_id6 => l_input_values_rec(6).input_value_id
,p_input_value_id7 => l_input_values_rec(7).input_value_id
,p_input_value_id8 => l_input_values_rec(8).input_value_id
,p_input_value_id9 => l_input_values_rec(9).input_value_id
,p_entry_value1 => l_input_values_rec(1).value
,p_entry_value2 => l_input_values_rec(2).value
,p_entry_value3 => l_submitted
,p_entry_value4 => P_LTCBLOCK
,p_entry_value5 => l_exempted_amount
,p_entry_value6 => P_CARRY_OVER
,p_entry_value7 => l_input_values_rec(7).value
,p_entry_value8 => l_input_values_rec(8).value
,p_entry_value9 => l_input_values_rec(9).value
,p_effective_start_date => l_start_date
,p_effective_end_date => l_end_date
,p_update_warning => l_warnings
);
pay_element_entry_api.update_element_entry
(p_datetrack_update_mode => hr_api.g_correction
,p_effective_date => l_effective_start_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_object_version_no
,p_input_value_id1 => l_input_values_rec(1).input_value_id
,p_input_value_id2 => l_input_values_rec(2).input_value_id
,p_input_value_id3 => l_input_values_rec(3).input_value_id
,p_input_value_id4 => l_input_values_rec(4).input_value_id
,p_input_value_id5 => l_input_values_rec(5).input_value_id
,p_input_value_id6 => l_input_values_rec(6).input_value_id
,p_input_value_id7 => l_input_values_rec(7).input_value_id
,p_input_value_id8 => l_input_values_rec(8).input_value_id
,p_input_value_id9 => l_input_values_rec(9).input_value_id
,p_entry_value1 => l_input_values_rec(1).value
,p_entry_value2 => l_input_values_rec(2).value
,p_entry_value3 => l_submitted
,p_entry_value4 => P_LTCBLOCK
,p_entry_value5 => l_exempted_amount
,p_entry_value6 => P_CARRY_OVER
,p_entry_value7 => l_input_values_rec(7).value
,p_entry_value8 => l_input_values_rec(8).value
,p_entry_value9 => l_input_values_rec(9).value
,p_effective_start_date => l_start_date
,p_effective_end_date => l_end_date
,p_update_warning => l_warnings
);
UPDATE per_assignment_extra_info
SET AEI_INFORMATION10 = P_EXEMPTED
,AEI_INFORMATION6 = P_ER_COMMENTS
,AEI_INFORMATION11 = nvl(l_element_entry_id,p_element_entry_id)
WHERE ASSIGNMENT_EXTRA_INFO_ID = p_assignment_extra_info_id ;
SELECT SUM(fnd_number.canonical_to_number(pae.aei_information6)) submitted
FROM per_assignment_extra_info pae
WHERE pae.assignment_id = p_assignment_id
AND pae.aei_information1 = p_tax_yr
AND pae.aei_information_category ='PER_IN_MEDICAL_BILLS'
AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
AND trunc(creation_date) <= nvl(p_created_to,to_date('31-12-4712','DD-MM-YYYY'))
AND fnd_date.canonical_to_date(pae.aei_information3) >=
(select min(effective_start_date)
from per_assignments_f
where assignment_id = p_assignment_id)
AND pae.aei_information7 IS NOT NULL;
SELECT SUM(fnd_number.canonical_to_number(pae.aei_information6)) submitted
FROM per_assignment_extra_info pae
WHERE pae.assignment_id = p_assignment_id
AND pae.aei_information1 = p_tax_yr
AND pae.aei_information_category ='PER_IN_MEDICAL_BILLS'
AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
AND trunc(creation_date) <= nvl(p_created_to,to_date('31-12-4712','DD-MM-YYYY'))
AND fnd_date.canonical_to_date(pae.aei_information3) >=
(select min(effective_start_date)
from per_assignments_f
where assignment_id = p_assignment_id)
AND pae.aei_information7 IS NULL;
SELECT SUM(fnd_number.canonical_to_number(pae.aei_information9)) submitted
FROM per_assignment_extra_info pae
WHERE pae.assignment_id = p_assignment_id
AND pae.aei_information1 = p_tax_yr
AND pae.aei_information_category ='PER_IN_LTC_BILLS'
AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
AND trunc(creation_date) <= nvl(p_created_to,to_date('31-12-4712','DD-MM-YYYY'))
AND pae.aei_information10 IS NOT NULL
AND fnd_date.canonical_to_date(pae.aei_information13) >=
(select min(effective_start_date)
from per_assignments_f
where assignment_id = p_assignment_id)
AND NVL(pae.aei_information18,'N') =nvl(p_carry_over,'N') ;
SELECT SUM(fnd_number.canonical_to_number(pae.aei_information9)) submitted
FROM per_assignment_extra_info pae
WHERE pae.assignment_id = p_assignment_id
AND pae.aei_information1 = p_tax_yr
AND pae.aei_information_category ='PER_IN_LTC_BILLS'
AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
AND trunc(creation_date) <= nvl(p_created_to,to_date('31-12-4712','DD-MM-YYYY'))
AND pae.aei_information10 IS NULL
AND fnd_date.canonical_to_date(pae.aei_information13) >=
(select min(effective_start_date)
from per_assignments_f
where assignment_id = p_assignment_id)
AND NVL(pae.aei_information18,'N') = nvl(p_carry_over,'N');
SELECT sum(nvl(fnd_number.canonical_to_number(nvl(aei_information7,0)),0)) approved
FROM per_assignment_extra_info pae
WHERE pae.assignment_id = p_assignment_id
AND pae.aei_information1 = p_tax_yr
AND pae.aei_information_category ='PER_IN_MEDICAL_BILLS'
AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
AND trunc(creation_date) <= nvl(p_created_to,to_date('31-12-4712','DD-MM-YYYY'))
AND fnd_date.canonical_to_date(pae.aei_information3) >=
(select min(effective_start_date)
from per_assignments_f
where assignment_id = p_assignment_id)
AND pae.aei_information7 IS NOT NULL;
SELECT sum(nvl(fnd_number.canonical_to_number(nvl(aei_information7,0)),0)) approved
FROM per_assignment_extra_info pae
WHERE pae.assignment_id = p_assignment_id
AND pae.aei_information1 = p_tax_yr
AND pae.aei_information_category ='PER_IN_MEDICAL_BILLS'
AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
AND trunc(creation_date) <= nvl(p_created_to,to_date('31-12-4712','DD-MM-YYYY'))
AND fnd_date.canonical_to_date(pae.aei_information3) >=
(select min(effective_start_date)
from per_assignments_f
where assignment_id = p_assignment_id)
AND pae.aei_information7 IS NULL;
SELECT SUM(fnd_number.canonical_to_number(pae.aei_information10)) submitted
FROM per_assignment_extra_info pae
WHERE pae.assignment_id = p_assignment_id
AND pae.aei_information1 = p_tax_yr
AND pae.aei_information_category ='PER_IN_LTC_BILLS'
AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
AND trunc(creation_date) <= nvl(p_created_to,to_date('31-12-4712','DD-MM-YYYY'))
AND fnd_date.canonical_to_date(pae.aei_information13) >=
(select min(effective_start_date)
from per_assignments_f
where assignment_id = p_assignment_id)
AND pae.aei_information10 IS NOT NULL
and NVL(pae.aei_information18,'N') = nvl(p_carry_over,'N');
SELECT 'Y'
FROM per_assignment_extra_info pae
WHERE pae.assignment_id = p_assignment_id
AND pae.aei_information1 = p_tax_yr
AND pae.aei_information_category ='PER_IN_MEDICAL_BILLS'
AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
AND trunc(creation_date) <= nvl(p_created_to,to_date('31-12-4712','DD-MM-YYYY'))
AND fnd_date.canonical_to_date(pae.aei_information3) >=
(select min(effective_start_date)
from per_assignments_f
where assignment_id = p_assignment_id)
AND pae.aei_information7 IS NOT NULL;
SELECT 'Y'
FROM per_assignment_extra_info pae
WHERE pae.assignment_id = p_assignment_id
AND pae.aei_information1 = p_tax_yr
AND pae.aei_information_category ='PER_IN_MEDICAL_BILLS'
AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
AND trunc(creation_date) <= nvl(p_created_to,to_date('31-12-4712','DD-MM-YYYY'))
AND fnd_date.canonical_to_date(pae.aei_information3) >=
(select min(effective_start_date)
from per_assignments_f
where assignment_id = p_assignment_id)
AND pae.aei_information7 IS NULL;
FUNCTION get_last_updated_date
(p_assignment_id IN NUMBER
,p_block IN VARCHAR2
,p_asg_info_type IN VARCHAR2
,p_created_from IN DATE DEFAULT NULL
,p_created_to IN DATE DEFAULT NULL
,p_approved IN VARCHAR2 DEFAULT NULL
,p_carry_over IN VARCHAR2 default null)
RETURN DATE
IS
/* CHANGE THIS CURSOR TO INCLUDE APPROVAL STATUS LATER */
CURSOR csr_get_med_date_appr
IS
SELECT MAX(extra.last_update_date)
FROM per_assignment_extra_info extra
WHERE extra.information_type = 'PER_IN_MEDICAL_BILLS'
and extra.aei_information1 = p_block
AND extra.assignment_id = p_assignment_id
AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
and trunc(creation_date) <= nvl(p_created_to,to_date('31-12-4712','DD-MM-YYYY'))
AND fnd_date.canonical_to_date(extra.aei_information3) >=
(select min(effective_start_date)
from per_assignments_f
where assignment_id = p_assignment_id)
AND extra.aei_information7 IS not NULL;
SELECT MAX(extra.last_update_date)
FROM per_assignment_extra_info extra
WHERE extra.information_type = 'PER_IN_LTC_BILLS'
and extra.aei_information1 = p_block
AND extra.assignment_id = p_assignment_id
AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
and trunc(creation_date) <= nvl(p_created_to,to_date('31-12-4712','DD-MM-YYYY'))
and NVL(extra.aei_information18,'N') = nvl(p_carry_over,'N')
AND fnd_date.canonical_to_date(extra.aei_information13) >=
(select min(effective_start_date)
from per_assignments_f
where assignment_id = p_assignment_id)
and extra.aei_information10 is not null;
SELECT MAX(extra.last_update_date)
FROM per_assignment_extra_info extra
WHERE extra.information_type = 'PER_IN_MEDICAL_BILLS'
and extra.aei_information1 = p_block
AND extra.assignment_id = p_assignment_id
AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
and trunc(creation_date) <= nvl(p_created_to,to_date('31-12-4712','DD-MM-YYYY'))
AND fnd_date.canonical_to_date(extra.aei_information3) >=
(select min(effective_start_date)
from per_assignments_f
where assignment_id = p_assignment_id)
AND extra.aei_information7 IS NULL;
SELECT MAX(extra.last_update_date)
FROM per_assignment_extra_info extra
WHERE extra.information_type = 'PER_IN_LTC_BILLS'
and extra.aei_information1 = p_block
AND extra.assignment_id = p_assignment_id
AND trunc(creation_date) >= nvl(p_created_from,to_date('01-01-1901','DD-MM-YYYY'))
and trunc(creation_date) <= nvl(p_created_to,to_date('31-12-4712','DD-MM-YYYY'))
and NVL(extra.aei_information18,'N') = nvl(p_carry_over,'N')
AND fnd_date.canonical_to_date(extra.aei_information13) >=
(select min(effective_start_date)
from per_assignments_f
where assignment_id = p_assignment_id)
and extra.aei_information10 is null;
l_updated_date DATE;
l_procedure := g_package || 'get_last_updated_date';
l_updated_date := l_upd_date_appr;
l_updated_date := l_upd_date_unappr;
l_updated_date := GREATEST(l_upd_date_appr,l_upd_date_unappr);
pay_in_utils.trace('l_updated_date',l_updated_date);
RETURN l_updated_date;
END get_last_updated_date;
SELECT screen_entry_value
FROM pay_element_entry_values_f peev,
pay_element_entries_f pev,
pay_element_types_f pet,
pay_input_values_f piv
WHERE pev.assignment_id = p_assignment_id
AND pev.element_type_id = pet.element_type_id
AND pev.element_entry_id = peev.element_entry_id
AND peev.element_entry_id = p_entry_id
AND piv.name = p_input_name
AND piv.input_value_id = peev.input_value_id
AND SYSDATE BETWEEN pet.effective_start_date AND pet.effective_end_date
AND SYSDATE BETWEEN piv.effective_start_date AND piv.effective_end_date
AND p_date BETWEEN pev.effective_start_date AND pev.effective_end_date
AND p_date BETWEEN peev.effective_start_date AND peev.effective_end_date;
SELECT hr_general.decode_lookup('CONTACT',relation.contact_type)
FROM per_contact_relationships relation,
per_all_people_f ppf
WHERE ppf.person_id = relation.contact_person_id
AND relation.contact_person_id = p_person_id
AND ppf.business_group_id = p_business_group_id;
SELECT DECODE(p_element_flag,'MEDBILL',org.org_information1,'MEDPAY',org.org_information2,'LTC_ELE',org.org_information3)
FROM hr_organization_information org,
hr_organization_units unit
WHERE org.org_information_context = 'PER_IN_REIMBURSE_ELEMENTS'
AND org.organization_id =unit.organization_id
AND unit.business_group_id = p_business_group_id;
PROCEDURE delete_medical_bill_entry(p_asg_extra_info_id IN NUMBER)
IS
CURSOR csr_exists
IS
SELECT '1'
FROM per_assignment_extra_info
WHERE assignment_extra_info_id = p_asg_extra_info_id;
DELETE FROM per_assignment_extra_info
WHERE assignment_extra_info_id = p_asg_extra_info_id;
END delete_medical_bill_entry;
SELECT object_version_number
FROM per_assignment_extra_info
WHERE assignment_extra_info_id = l_asg_extra_info_d;
select fnd_profile.value('PER_PERSON_ID') into l_person_profile_id from dual;
select fnd_profile.value('PER_BUSINESS_GROUP_ID') into l_bg_profile_id from dual;
hr_assignment_extra_info_api.update_assignment_extra_info(
p_aei_information1 => p_financial_yr,
p_aei_information2 => '',
p_aei_information3 => fnd_date.date_to_canonical(p_bill_date),
p_aei_information4 => p_con_person_id,
p_aei_information5 => p_bill_number,
p_aei_information6 => fnd_number.number_to_canonical(p_new_bill_amt) ,
p_aei_information8 => p_ee_comments,
p_aei_information9 => p_er_comments,
p_aei_information10 => l_element_entry_id,
p_assignment_extra_info_id => p_asg_extra_info_id,
p_object_version_number => l_ovn);
,p_last_updated_date => ''
,p_assignment_id => p_asg_id
,p_employee_id => 0
,p_assignment_extra_info_id => p_asg_extra_info_id
,p_entry_date => p_element_entry_date
,p_warnings => l_warnings
);
SELECT sum(nvl(target.result_value,0) )
FROM pay_run_result_values TARGET
,pay_balance_feeds_f FEED
,pay_run_results RR
,pay_assignment_actions ASSACT
,pay_assignment_actions BAL_ASSACT
,pay_payroll_actions PACT
,pay_payroll_actions BACT
,pay_input_values_f piv
,pay_run_result_values srcVal
,pay_input_values_f srcInp
where BAL_ASSACT.assignment_action_id = p_asg_action_id
AND BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
AND FEED.input_value_id = TARGET.input_value_id
AND nvl(TARGET.result_value, '0') <> '0'
AND TARGET.run_result_id = RR.run_result_id
AND RR.assignment_action_id = ASSACT.assignment_action_id
AND ASSACT.payroll_action_id = PACT.payroll_action_id
AND PACT.effective_date between
FEED.effective_start_date AND FEED.effective_end_date
AND RR.status in ('P','PA')
AND ASSACT.action_sequence <= BAL_ASSACT.action_sequence
AND ASSACT.assignment_id = BAL_ASSACT.assignment_id
AND feed.input_value_id = piv.input_value_id
AND feed.balance_type_id = p_balance_type_id
AND srcVal.run_result_id = RR.run_result_id
AND srcVal.result_value = p_ltc_block
AND srcVal.input_value_id = srcInp.input_value_id
AND srcInp.name = 'LTC Journey Block';
SELECT assignment_action_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_id = p_asg_id
AND paa.payroll_action_id = PPA.PAYROLL_ACTION_ID
AND ppa.action_type in('R','Q','B','I')
AND paa.source_action_id IS NOT NULL
ORDER BY paa.action_sequence DESC;
SELECT balance_type_id
FROM pay_balance_types pbt
WHERE pbt.balance_name = p_balance_name
AND legislation_code ='IN';
SELECT assignment_action_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_id = p_asg_id
AND paa.payroll_action_id = PPA.PAYROLL_ACTION_ID
AND ppa.action_type in('R','Q','B','I')
AND ppa.effective_date BETWEEN p_year_start AND p_year_end
AND paa.source_action_id IS NOT NULL
ORDER BY paa.action_sequence DESC;
SELECT assignment_action_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_id = p_asg_id
AND paa.payroll_action_id = PPA.PAYROLL_ACTION_ID
AND ppa.action_type in('R','Q','B','I')
AND ppa.effective_date BETWEEN p_year_start AND p_year_end
AND paa.source_action_id IS NOT NULL
and exists(select '1' from pay_run_results prr
where source_id in (select distinct(aei_information11)
from per_assignment_extra_info
where information_type ='PER_IN_MEDICAL_BILLS')
and prr.assignment_action_id = paa.assignment_action_id) ;
SELECT pdb.defined_balance_id
FROM pay_defined_balances pdb
,pay_balance_types pbt
,pay_balance_dimensions pbd
WHERE pbt.balance_name = p_balance_name
AND pbd.dimension_name = p_dimension_name
AND pdb.balance_type_id = pbt.balance_type_id
AND pbt.legislation_code = 'IN'
AND pbd.legislation_code = 'IN'
AND pdb.legislation_code = 'IN'
AND pdb.balance_dimension_id = pbd.balance_dimension_id;
SELECT NVL(DECODE(p_ltc_or_med,'LTC',org_information1,'MED',org_information2),'N') lock_flag
FROM
hr_organization_information org
,per_people_f person
WHERE
org.org_information_context = 'PER_IN_BENEFITS_DECL_INFO'
AND org.organization_id = person.business_group_id
AND person.person_id = p_person_id
AND SYSDATE BETWEEN person.effective_start_date
AND person.effective_end_date ;
PROCEDURE update_ltc_element
(
p_employee_number IN VARCHAR2
,p_full_name IN VARCHAR2
,p_start_date IN DATE
,p_effective_end_date IN DATE DEFAULT NULL
,p_fare IN NUMBER
,p_blockYr IN VARCHAR2
,p_carry IN VARCHAR2
,p_benefit IN NUMBER
,p_assignment_id IN NUMBER
,p_element_entry_id IN NUMBER DEFAULT NULL
,p_warnings OUT NOCOPY VARCHAR2
)
IS
CURSOR c_input_rec(p_element_type_id NUMBER
,p_effective_date DATE
)
IS
SELECT inputs.name name
, inputs.input_value_id id
, inputs.default_value value
FROM pay_element_types_f types
, pay_input_values_f inputs
WHERE types.element_type_id = p_element_type_id
AND inputs.element_type_id = types.element_type_id
--AND inputs.legislation_code = 'IN'
AND p_effective_date BETWEEN types.effective_start_date AND types.effective_end_date
AND p_effective_date BETWEEN inputs.effective_start_date AND inputs.effective_end_date
ORDER BY inputs.display_sequence;
SELECT object_version_number
FROM pay_element_entries_f
WHERE element_entry_id = p_element_entryid;
SELECT element_type_id
FROM pay_element_entries_f
WHERE element_entry_id = p_element_entryid;
l_procedure := g_package ||'update_ltc_element';
SELECT 1 INTO l_session FROM fnd_sessions WHERE SESSION_ID = USERENV('SESSIONID') AND ROWNUM=1;
INSERT INTO fnd_sessions(session_id,effective_date) VALUES (USERENV('SESSIONID'),p_start_date);
pay_element_entry_api.update_element_entry
(p_datetrack_update_mode => hr_api.g_correction
,p_effective_date => p_start_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => p_element_entry_id
,p_object_version_number => l_object_version_no
,p_input_value_id1 => l_input_values_rec(1).input_value_id
,p_input_value_id2 => l_input_values_rec(2).input_value_id
,p_input_value_id3 => l_input_values_rec(3).input_value_id
,p_input_value_id4 => l_input_values_rec(4).input_value_id
,p_input_value_id5 => l_input_values_rec(5).input_value_id
,p_input_value_id6 => l_input_values_rec(6).input_value_id
,p_input_value_id7 => l_input_values_rec(7).input_value_id
,p_input_value_id8 => l_input_values_rec(8).input_value_id
,p_input_value_id9 => l_input_values_rec(9).input_value_id
,p_entry_value1 => l_input_values_rec(1).value
,p_entry_value2 => l_input_values_rec(2).value
,p_entry_value3 => p_fare
,p_entry_value4 => p_blockYr
,p_entry_value5 => p_benefit
,p_entry_value6 => p_carry
,p_entry_value7 => l_input_values_rec(7).value
,p_entry_value8 => l_input_values_rec(8).value
,p_entry_value9 => l_input_values_rec(9).value
,p_effective_start_date => l_start_date
,p_effective_end_date => l_end_date
,p_update_warning => l_warnings
);
UPDATE per_assignment_extra_info
SET AEI_INFORMATION18 = p_carry
where AEI_INFORMATION11 = p_element_entry_id
and assignment_id = p_assignment_id;
,p_procedure_name => 'update_ltc_element'
,p_error_text => substr(sqlerrm, 1, 240)
);
END update_ltc_element;