[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(org.org_information1, 'N') locking_period
, nvl(org.org_information3, 0) window_period
, fnd_date.canonical_to_date(org.org_information2) locking_period_start
, TRUNC(SYSDATE - start_date) hire_duration
FROM hr_organization_information org
, per_people_f person
, per_assignments_f assign
, hr_soft_coding_keyflex scl
WHERE org.org_information_context = 'PER_IN_TAX_DECL_DETAILS'
AND person.person_id = c_person_id
AND assign.person_id = person.person_id
AND assign.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND assign.primary_flag = 'Y'
AND org.organization_id = nvl(scl.segment1, person.business_group_id)
AND SYSDATE BETWEEN person.effective_start_date
AND person.effective_end_date
AND SYSDATE BETWEEN assign.effective_start_date
AND assign.effective_end_date;
SELECT add_information16
FROM per_addresses
WHERE person_id = p_person_id
AND primary_flag = 'Y'
AND style = 'IN'
AND p_effective_date BETWEEN date_from
AND NVL(date_to, hr_general.end_of_time);
SELECT assignment_extra_info_id
, aei_information2 flag
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND information_type = g_approval_info_type
AND aei_information1 = p_tax_year
AND aei_information_category = g_approval_info_type;
SELECT cinstances.value
FROM pay_user_tables utab
, pay_user_columns ucols
, pay_user_rows_f urows
, pay_user_column_instances_f cinstances
WHERE utab.user_table_id = ucols.user_table_id
AND utab.user_table_id = urows.user_table_id
AND ucols.user_column_id = cinstances.user_column_id
AND urows.user_row_id = cinstances.user_row_id
AND utab.user_table_name = 'PER_IN_DONATION_DETAILS'
AND utab.legislation_code = 'IN'
AND ucols.user_column_name = 'Donation Type'
AND urows.row_low_range_or_name = p_lookup_code;
SELECT screen_entry_value
FROM pay_element_entry_values_f
WHERE element_entry_id = p_ele_entry_id
AND input_value_id = p_input_value_id
AND c_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT types.element_name
, inputs.name name
, value.screen_entry_value planned
, value.screen_entry_value actual
FROM per_assignments_f assgn
, pay_element_links_f link
, pay_element_types_f types
, pay_element_entries_f entries
, pay_element_entry_values_f value
, pay_input_values_f inputs
WHERE assgn.assignment_id = p_assignment_id
AND link.element_link_id = pay_in_utils.get_element_link_id(p_assignment_id
,c_effective_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_name in ( 'House Rent Information'
, 'Rebates under Section 88'
, 'Tuition Fee'
, 'Deduction under Section 80GG'
, 'Deduction under Section 80D'
, 'Deduction under Section 80DDB'
, 'Deduction under Section 80GGA'
, 'Deduction under Section 80E'
, 'Deduction under Section 80U' -- Check if it is really required
, 'Other Income')
AND entries.element_type_id = types.element_type_id
AND entries.element_link_id = link.element_link_id
AND entries.assignment_id = assgn.assignment_id
AND entries.element_entry_id = value.element_entry_id
AND inputs.input_value_id = value.input_value_id
AND inputs.element_type_id = types.element_type_id
AND c_effective_date BETWEEN assgn.effective_start_date
AND assgn.effective_end_date
AND c_effective_date BETWEEN link.effective_start_date
AND link.effective_end_date
AND c_effective_date BETWEEN types.effective_start_date
AND types.effective_end_date
AND c_effective_date BETWEEN entries.effective_start_date
AND entries.effective_end_date
AND c_effective_date BETWEEN inputs.effective_start_date
AND inputs.effective_end_date
AND c_effective_date BETWEEN value.effective_start_date
AND value.effective_end_date
order by types.element_name, inputs.name;
FUNCTION get_last_updated_date
(p_person_id IN NUMBER
,p_effective_date IN DATE
,p_element_type IN VARCHAR2)
RETURN DATE
IS
CURSOR csr_get_date(c_element_name1 IN VARCHAR2
,c_element_name2 IN VARCHAR2
,c_element_name3 IN VARCHAR2
,c_element_name4 IN VARCHAR2
,c_element_name5 IN VARCHAR2
,c_element_name6 IN VARCHAR2
,c_element_name7 IN VARCHAR2
,c_element_name8 IN VARCHAR2
,c_element_name9 IN VARCHAR2
,c_element_name10 IN VARCHAR2
,c_element_name11 IN VARCHAR2
,c_element_name12 IN VARCHAR2
,c_element_name13 IN VARCHAR2)
IS
SELECT MAX(entries.last_update_date)
FROM pay_element_types_f ele
, pay_element_entries_f entries
, per_assignments_f assgn
WHERE ele.element_name in (c_element_name1
,c_element_name2
,c_element_name3
,c_element_name4
,c_element_name5
,c_element_name6
,c_element_name7
,c_element_name8
,c_element_name9
,c_element_name10
,c_element_name11
,c_element_name12
,c_element_name13)
AND ele.legislation_code = 'IN'
AND assgn.person_id = p_person_id
AND entries.assignment_id = assgn.assignment_id
AND entries.element_type_id = ele.element_type_id
AND p_effective_date BETWEEN assgn.effective_start_date
AND assgn.effective_end_date
AND p_effective_date BETWEEN ele.effective_start_date
AND ele.effective_end_date
AND p_effective_date BETWEEN entries.effective_start_date
AND entries.effective_end_date;
l_updated_date DATE;
l_procedure := g_package || 'get_last_updated_date';
FETCH csr_get_date INTO l_updated_date;
pay_in_utils.trace('l_updated_date',l_updated_date);
RETURN l_updated_date;
END get_last_updated_date;
SELECT inputs.name
, inputs.input_value_id
FROM pay_element_types_f types
, pay_input_values_f inputs
WHERE types.element_type_id = c_element_type_id
AND inputs.element_type_id = types.element_type_id
AND inputs.legislation_code = g_legislation_code
AND sysdate BETWEEN types.effective_start_date
AND types.effective_end_date
AND sysdate BETWEEN inputs.effective_start_date
AND inputs.effective_end_date
ORDER BY inputs.display_sequence;
SELECT entries.element_entry_id
, links.element_type_id
, links.business_group_id
, entries.object_version_number
FROM pay_element_entries_f entries
, pay_element_links_f links
WHERE entries.element_link_id = links.element_link_id
AND entries.assignment_id = c_assignment_id
AND links.element_link_id = c_element_link_id
AND links.element_type_id = entries.element_type_id
AND p_effective_date BETWEEN entries.effective_start_date
AND entries.effective_end_date
AND p_effective_date BETWEEN links.effective_start_date
AND links.effective_end_date;
SELECT links.element_type_id
, links.business_group_id
FROM pay_element_links_f links
WHERE links.element_link_id = c_element_link_id
AND p_effective_date BETWEEN links.effective_start_date
AND links.effective_end_date;
FUNCTION get_update_mode
(p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE
,p_effective_date IN DATE)
RETURN VARCHAR2
IS
--
l_procedure VARCHAR(100);
l_update BOOLEAN;
l_update_override BOOLEAN;
l_update_change_insert BOOLEAN;
l_update_mode VARCHAR2(30);
l_procedure := g_package || 'get_update_mode';
,p_update => l_update
,p_update_override => l_update_override
,p_update_change_insert=> l_update_change_insert);
IF l_update THEN
l_update_mode := hr_api.g_update;
ELSIF l_update_override THEN
l_update_mode := hr_api.g_update_override;
l_update_mode := hr_api.g_correction;
l_update_mode := hr_api.g_correction;
pay_in_utils.trace('l_update_mode : ',l_update_mode);
RETURN l_update_mode;
END get_update_mode;
delete_declaration
(p_element_entry_id => l_element_entry_id
,p_effective_date => pay_in_utils.next_tax_year(l_effective_date)-1
,p_warnings => l_warnings);
pay_element_entry_api.update_element_entry
(p_datetrack_update_mode => get_update_mode(l_element_entry_id
,l_effective_date)
,p_effective_date => l_effective_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_object_version_number
-- April
,p_input_value_id1 => l_input_values(0).input_value_id
-- May
,p_input_value_id2 => l_input_values(1).input_value_id
-- June
,p_input_value_id3 => l_input_values(2).input_value_id
-- July
,p_input_value_id4 => l_input_values(3).input_value_id
-- August
,p_input_value_id5 => l_input_values(4).input_value_id
-- September
,p_input_value_id6 => l_input_values(5).input_value_id
-- October
,p_input_value_id7 => l_input_values(6).input_value_id
-- November
,p_input_value_id8 => l_input_values(7).input_value_id
-- December
,p_input_value_id9 => l_input_values(8).input_value_id
-- January
,p_input_value_id10 => l_input_values(9).input_value_id
-- February
,p_input_value_id11 => l_input_values(10).input_value_id
-- March
,p_input_value_id12 => l_input_values(11).input_value_id
,p_entry_value1 => p_apr
,p_entry_value2 => p_may
,p_entry_value3 => p_jun
,p_entry_value4 => p_jul
,p_entry_value5 => p_aug
,p_entry_value6 => p_sep
,p_entry_value7 => p_oct
,p_entry_value8 => p_nov
,p_entry_value9 => p_dec
,p_entry_value10 => p_jan
,p_entry_value11 => p_feb
,p_entry_value12 => p_mar
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_update_warning => p_warnings
);
delete_declaration
(p_element_entry_id =>l_element_entry_id
,p_effective_date =>pay_in_utils.next_tax_year(l_effective_date)-1
,p_warnings =>l_warnings);
SELECT global_value
FROM ff_globals_f
WHERE global_name = 'IN_PERMANENT_PHYSICAL_DISABILITY_80U'
AND legislation_code = g_legislation_code;
delete_declaration
(p_element_entry_id => l_element_entry_id
,p_effective_date => pay_in_utils.next_tax_year(l_effective_date)-1
,p_warnings => l_warnings);
pay_element_entry_api.update_element_entry
(p_datetrack_update_mode => get_update_mode(l_element_entry_id
,l_effective_date)
,p_effective_date => l_effective_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_object_version_number
--Pension Fund 80CCC
,p_input_value_id1 => l_input_values(0).input_value_id
--Medical Insurance Premium 80D
,p_input_value_id2 => l_input_values(1).input_value_id
--Sec 80DDB Cover Senior Citizen
,p_input_value_id3 => l_input_values(2).input_value_id
--Disease Treatment 80DDB
,p_input_value_id4 => l_input_values(3).input_value_id
--Sec 80D Cover Senior Citizen
,p_input_value_id5 => l_input_values(4).input_value_id
--Higher Education Loan 80E
,p_input_value_id6 => l_input_values(5).input_value_id
--Claim Exemption under Sec 80GG
,p_input_value_id7 => l_input_values(6).input_value_id
--Donation for Research 80GGA
,p_input_value_id8 => l_input_values(7).input_value_id
--Int on Gen Investment 80L
,p_input_value_id9 => l_input_values(8).input_value_id
--Int on Securities 80L
,p_input_value_id10 => l_input_values(9).input_value_id
,p_entry_value1 => p_pension_fund_80ccc
,p_entry_value2 => p_medical_insurance_prem_80d
,p_entry_value3 => p_sec_80ddb_senior_citizen
,p_entry_value4 => p_disease_treatment_80ddb
,p_entry_value5 => p_sec_80d_senior_citizen
,p_entry_value6 => p_higher_education_loan_80e
,p_entry_value7 => p_claim_exemp_under_sec_80gg
,p_entry_value8 => p_donation_for_research_80gga
,p_entry_value9 => p_int_on_gen_investment_80L
,p_entry_value10 => p_int_on_securities_80L
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_update_warning => p_warnings
);
pay_element_entry_api.update_element_entry
(p_datetrack_update_mode => get_update_mode(l_element_entry_id
,l_endation_date)
,p_effective_date => l_endation_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_object_version_number
--Pension Fund 80CCC
,p_input_value_id1 => l_input_values(0).input_value_id
--Medical Insurance Premium 80D
,p_input_value_id2 => l_input_values(1).input_value_id
--Sec 80DDB Cover Senior Citizen
,p_input_value_id3 => l_input_values(2).input_value_id
--Disease Treatment 80DDB
,p_input_value_id4 => l_input_values(3).input_value_id
--Sec 80D Cover Senior Citizen
,p_input_value_id5 => l_input_values(4).input_value_id
--Higher Education Loan 80E
,p_input_value_id6 => l_input_values(5).input_value_id
--Claim Exemption under Sec 80GG
,p_input_value_id7 => l_input_values(6).input_value_id
--Donation for Research 80GGA
,p_input_value_id8 => l_input_values(7).input_value_id
--Int on Gen Investment 80L
,p_input_value_id9 => l_input_values(8).input_value_id
--Int on Securities 80L
,p_input_value_id10 => l_input_values(9).input_value_id
,p_entry_value1 => 0
,p_entry_value2 => 0
,p_entry_value3 => 'N'
,p_entry_value4 => 0
,p_entry_value5 => 'N'
,p_entry_value6 => 0
,p_entry_value7 => 'N'
,p_entry_value8 => 0
,p_entry_value9 => 0
,p_entry_value10 => 0
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_update_warning => p_warnings
);
delete_declaration
(p_element_entry_id => l_element_entry_id
,p_effective_date => l_endation_date-1
,p_warnings => l_warnings);
delete_declaration
(p_element_entry_id => l_element_entry_id
,p_effective_date => pay_in_utils.next_tax_year(l_effective_date)-1
,p_warnings => l_warnings);
pay_element_entry_api.update_element_entry
(p_datetrack_update_mode => get_update_mode(l_element_entry_id
,l_effective_date)
,p_effective_date => l_effective_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_object_version_number
-- Income from House Property
,p_input_value_id1 => l_input_values(0).input_value_id
-- Profit and Gain from Business
,p_input_value_id2 => l_input_values(1).input_value_id
-- Long Term Capital Gain
,p_input_value_id3 => l_input_values(2).input_value_id
-- Short Term Capital Gain
,p_input_value_id4 => l_input_values(3).input_value_id
-- Income from any other sources
,p_input_value_id5 => l_input_values(4).input_value_id
-- TDS Paid on Other Income
,p_input_value_id6 => l_input_values(5).input_value_id
,p_entry_value1 => p_income_from_house_property
,p_entry_value2 => p_profit_and_gain_from_busines
,p_entry_value3 => p_long_term_capital_gain
,p_entry_value4 => p_short_term_capital_gain
,p_entry_value5 => p_income_from_any_other_source
,p_entry_value6 => p_tds_paid_on_other_income
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_update_warning => p_warnings
);
delete_declaration
(p_element_entry_id =>l_element_entry_id
,p_effective_date =>pay_in_utils.next_tax_year(l_effective_date)-1
,p_warnings =>l_warnings);
SELECT element_type_id
FROM pay_element_types_f
WHERE (legislation_code = 'IN' OR business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'))
AND element_name = p_element_name
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT types.element_type_id
, links.element_link_id
, assgn.business_group_id
FROM per_assignments_f assgn
, pay_element_links_f links
, pay_element_types_f types
WHERE assgn.assignment_id = c_assignment_id
AND links.element_link_id = c_element_link_id
AND (types.processing_type = 'R' OR assgn.payroll_id IS NOT NULL)
AND links.business_group_id = assgn.business_group_id
AND links.element_type_id = types.element_type_id
AND types.element_name = 'Deduction under Section 80DD'
AND c_effective_date BETWEEN assgn.effective_start_date
AND assgn.effective_end_date
AND c_effective_date BETWEEN links.effective_start_date
AND links.effective_end_date
AND c_effective_date BETWEEN types.effective_start_date
AND types.effective_end_date;
SELECT entries.element_type_id
, entries.object_version_number
, assgn.business_group_id
FROM pay_element_entries_f entries
, per_assignments_f assgn
WHERE entries.element_entry_id = c_element_entry_id
AND entries.assignment_id = assgn.assignment_id
AND c_effective_date BETWEEN entries.effective_start_date
AND entries.effective_end_date
AND c_effective_date BETWEEN assgn.effective_start_date
AND assgn.effective_end_date;
hr_session_utilities.insert_session_row(l_effective_date);
delete_declaration
(p_element_entry_id => l_element_entry_id
,p_effective_date => pay_in_utils.next_tax_year(l_effective_date)-1
,p_warnings => p_warnings);
pay_element_entry_api.update_element_entry
(p_datetrack_update_mode => get_update_mode(p_element_entry_id
,l_effective_date)
,p_effective_date => l_effective_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => p_element_entry_id
,p_object_version_number => l_object_version_number
-- Disability Type
,p_input_value_id1 => l_input_values(0).input_value_id
-- Disability Percentage
,p_input_value_id2 => l_input_values(1).input_value_id
-- Treatment Amount
,p_input_value_id3 => l_input_values(2).input_value_id
,p_entry_value1 => p_disability_type
,p_entry_value2 => p_disability_percentage
,p_entry_value3 => p_treatment_amount
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_update_warning => l_warnings
);
delete_declaration
(p_element_entry_id =>l_element_entry_id
,p_effective_date =>pay_in_utils.next_tax_year(l_effective_date)-1
,p_warnings =>p_warnings);
SELECT element_type_id
FROM pay_element_types_f
WHERE legislation_code = 'IN'
AND element_name = p_element_name
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT types.element_type_id
, link.element_link_id
, assgn.business_group_id
FROM per_assignments_f assgn
, pay_element_links_f link
, pay_element_types_f types
WHERE assgn.assignment_id = c_assignment_id
AND link.element_link_id = c_element_link_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_name = 'Deduction under Section 80G'
AND c_effective_date BETWEEN assgn.effective_start_date
AND assgn.effective_end_date
AND c_effective_date BETWEEN link.effective_start_date
AND link.effective_end_date
AND c_effective_date BETWEEN types.effective_start_date
AND types.effective_end_date;
SELECT entries.element_type_id
, entries.object_version_number
, assgn.business_group_id
FROM pay_element_entries_f entries
, per_assignments_f assgn
WHERE entries.element_entry_id = c_element_entry_id
AND entries.assignment_id = assgn.assignment_id
AND c_effective_date BETWEEN entries.effective_start_date
AND entries.effective_end_date
AND c_effective_date BETWEEN assgn.effective_start_date
AND assgn.effective_end_date;
hr_session_utilities.insert_session_row(l_effective_date);
delete_declaration
(p_element_entry_id => l_element_entry_id
,p_effective_date => pay_in_utils.next_tax_year(l_effective_date)-1
,p_warnings => p_warnings);
pay_element_entry_api.update_element_entry
(p_datetrack_update_mode => get_update_mode(p_element_entry_id
,l_effective_date)
,p_effective_date => l_effective_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => p_element_entry_id
,p_object_version_number => l_object_version_number
-- Donation Type
,p_input_value_id1 => l_input_values(0).input_value_id
-- Donation Amount
,p_input_value_id2 => l_input_values(1).input_value_id
,p_entry_value1 => p_donation_type
,p_entry_value2 => p_donation_amount
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_update_warning => l_warnings
);
delete_declaration
(p_element_entry_id =>l_element_entry_id
,p_effective_date =>pay_in_utils.next_tax_year(l_effective_date)-1
,p_warnings =>p_warnings);
SELECT types.element_type_id
, link.element_link_id
, assgn.business_group_id
FROM per_assignments_f assgn
, pay_element_links_f link
, pay_element_types_f types
WHERE assgn.assignment_id = c_assignment_id
AND link.element_link_id = c_element_link_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_name = 'Life Insurance Premium'
AND c_effective_date BETWEEN assgn.effective_start_date
AND assgn.effective_end_date
AND c_effective_date BETWEEN link.effective_start_date
AND link.effective_end_date
AND c_effective_date BETWEEN types.effective_start_date
AND types.effective_end_date;
SELECT entries.element_type_id
, entries.object_version_number
, assgn.business_group_id
FROM pay_element_entries_f entries
, per_assignments_f assgn
WHERE entries.element_entry_id = c_element_entry_id
AND entries.assignment_id = assgn.assignment_id
AND c_effective_date BETWEEN entries.effective_start_date
AND entries.effective_end_date
AND c_effective_date BETWEEN assgn.effective_start_date
AND assgn.effective_end_date;
SELECT element_type_id
FROM pay_element_types_f
WHERE legislation_code = 'IN'
AND element_name = p_element_name
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
delete_declaration
(p_element_entry_id => l_element_entry_id
,p_effective_date => pay_in_utils.next_tax_year(l_effective_date)-1
,p_warnings => p_warnings);
pay_element_entry_api.update_element_entry
(p_datetrack_update_mode => get_update_mode(p_element_entry_id
,l_effective_date)
,p_effective_date => l_effective_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => p_element_entry_id
,p_object_version_number => l_object_version_number
-- Premium Paid
,p_input_value_id1 => l_input_values(0).input_value_id
-- Sum Assured
,p_input_value_id2 => l_input_values(1).input_value_id
,p_entry_value1 => p_premium_paid
,p_entry_value2 => p_sum_assured
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_update_warning => l_warnings
);
delete_declaration
(p_element_entry_id =>l_element_entry_id
,p_effective_date =>pay_in_utils.next_tax_year(l_effective_date)-1
,p_warnings =>p_warnings);
/* BUG 4251141: STAT UPDATE 2005 CHANGES START HERE */
--------------------------------------------------------------------------
-- --
-- Name : DECLARE_SECTION80CCE --
-- Type : PROCEDURE --
-- Access : Public --
-- Description : The procedure is responsible for storing the --
-- detials in 'Deduction under Section 80CCE' element. --
-- --
-- Parameters : --
-- IN : p_assignment_id per_assignments_f.assignment_id%TYPE--
-- p_investment_type VARCHAR2 --
-- p_investment_amount NUMBER --
-- p_effective_date DATE --
-- p_element_entry_id element_entry_id%TYPE --
-- OUT : p_warnings BOOLEAN --
-- --
-- Change History : --
--------------------------------------------------------------------------
-- Rev# Date Userid Description --
--------------------------------------------------------------------------
-- 1.0 14-Apr-2005 VGSRINIV Initial Version --
--------------------------------------------------------------------------
PROCEDURE declare_section80cce
(p_assignment_id IN per_assignments_f.assignment_id%TYPE
,p_investment_type IN VARCHAR2
,p_investment_amount IN NUMBER
,p_effective_date IN DATE default null
,p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE DEFAULT NULL
,p_warnings OUT NOCOPY VARCHAR2)
IS
--
l_procedure VARCHAR(100);
SELECT types.element_type_id
, link.element_link_id
, assgn.business_group_id
FROM per_assignments_f assgn
, pay_element_links_f link
, pay_element_types_f types
WHERE assgn.assignment_id = c_assignment_id
AND link.element_link_id = pay_in_utils.get_element_link_id(c_assignment_id
,c_effective_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_name = c_element_name--'Deduction under Section 80CCE'
AND c_effective_date BETWEEN assgn.effective_start_date
AND assgn.effective_end_date
AND c_effective_date BETWEEN link.effective_start_date
AND link.effective_end_date
AND c_effective_date BETWEEN types.effective_start_date
AND types.effective_end_date;
SELECT entries.element_type_id
, entries.object_version_number
, assgn.business_group_id
FROM pay_element_entries_f entries
, per_assignments_f assgn
WHERE entries.element_entry_id = c_element_entry_id
AND entries.assignment_id = assgn.assignment_id
AND c_effective_date BETWEEN entries.effective_start_date
AND entries.effective_end_date
AND c_effective_date BETWEEN assgn.effective_start_date
AND assgn.effective_end_date;
SELECT entries.element_entry_id entry_id
,entries.object_version_number
,value2.screen_entry_value
FROM per_assignments_f assgn
, pay_element_links_f link
, pay_element_types_f types
, pay_element_entries_f entries
, pay_element_entry_values_f value1
, pay_input_values_f inputs1
, pay_element_entry_values_f value2
, pay_input_values_f inputs2
WHERE assgn.assignment_id = p_assignment_id
AND link.element_link_id = pay_in_utils.get_element_link_id(p_assignment_id
,c_effective_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_name = c_element_name--'Deduction under Section 80CCE'
AND entries.element_type_id = types.element_type_id
AND entries.element_link_id = link.element_link_id
AND entries.assignment_id = assgn.assignment_id
AND value1.element_entry_id = entries.element_entry_id
AND inputs1.input_value_id = value1.input_value_id
AND inputs1.element_type_id = types.element_type_id
AND inputs1.name = 'Component Name'
AND value2.element_entry_id = entries.element_entry_id
AND inputs2.input_value_id = value2.input_value_id
AND inputs2.element_type_id = types.element_type_id
AND inputs2.name = 'Investment Amount'
AND c_effective_date BETWEEN assgn.effective_start_date AND assgn.effective_end_date
AND c_effective_date BETWEEN link.effective_start_date AND link.effective_end_date
AND c_effective_date BETWEEN types.effective_start_date AND types.effective_end_date
AND c_effective_date BETWEEN entries.effective_start_date AND entries.effective_end_date
AND c_effective_date BETWEEN inputs1.effective_start_date AND inputs1.effective_end_date
AND c_effective_date BETWEEN value1.effective_start_date AND value1.effective_end_date
AND c_effective_date BETWEEN inputs2.effective_start_date AND inputs2.effective_end_date
AND c_effective_date BETWEEN value2.effective_start_date AND value2.effective_end_date
AND value1.screen_entry_value = p_investment_type;
SELECT peev.screen_entry_value
FROM pay_element_entries_f peef,
pay_input_values_f piv,
pay_element_entry_values_f peev
WHERE peef.element_entry_id = p_element_entry_id
AND piv.element_type_id = peef.element_type_id
AND piv.name = 'Component Name'
AND peev.input_value_id = piv.input_value_id
AND peev.element_entry_id = peef.element_entry_id
AND piv.legislation_code = 'IN'
AND p_effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
AND p_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND p_effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date;
hr_session_utilities.insert_session_row(l_effective_date);
SELECT DECODE(p_investment_type
,'Pension Fund 80CCC','Pension Fund 80CCC'
,'Deferred Annuity','Deferred Annuity'
,'Senior Citizens Savings Scheme','Senior Citizens Savings Scheme'
,'Deduction under Section 80CCE'
)
INTO l_element_name
FROM dual;
pay_element_entry_api.update_element_entry
(p_datetrack_update_mode => get_update_mode(l_ele_entry_id
,l_effective_date)
,p_effective_date => l_effective_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => l_ele_entry_id
,p_object_version_number => l_ovn
-- Investment Amount
,p_input_value_id1 => l_input_values(0).input_value_id
-- Investment Type
,p_input_value_id2 => l_input_values(1).input_value_id
,p_entry_value1 => p_investment_amount+l_entry_value
,p_entry_value2 => p_investment_type
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_update_warning => l_warnings
);
delete_declaration
(p_element_entry_id =>l_element_entry_id
,p_effective_date =>pay_in_utils.next_tax_year(l_effective_date)-1
,p_warnings =>p_warnings);
delete_declaration
(p_element_entry_id => l_element_entry_id
,p_effective_date => pay_in_utils.next_tax_year(l_effective_date)-1
,p_warnings => p_warnings);
pay_element_entry_api.update_element_entry
(p_datetrack_update_mode => get_update_mode(p_element_entry_id
,l_effective_date)
,p_effective_date => l_effective_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => p_element_entry_id
,p_object_version_number => l_object_version_number
-- Investment Amount
,p_input_value_id1 => l_input_values(0).input_value_id
-- Investment Type
,p_input_value_id2 => l_input_values(1).input_value_id
,p_entry_value1 => p_investment_amount
,p_entry_value2 => p_investment_type
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_update_warning => l_warnings
);
delete_declaration
(p_element_entry_id =>l_element_entry_id
,p_effective_date =>pay_in_utils.next_tax_year(l_effective_date)-1
,p_warnings =>p_warnings);
delete_declaration
(p_element_entry_id => l_element_entry_id
,p_effective_date => pay_in_utils.next_tax_year(l_effective_date)-1
,p_warnings => l_warnings);
pay_element_entry_api.update_element_entry
(p_datetrack_update_mode => get_update_mode(l_element_entry_id
,l_effective_date)
,p_effective_date => l_effective_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_object_version_number
--Claim Exemption under Sec 80GG
,p_input_value_id1 => l_input_values(0).input_value_id
,p_entry_value1 => p_claim_exemp_under_sec_80gg
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_update_warning => p_warnings
);
delete_declaration
(p_element_entry_id =>l_element_entry_id
,p_effective_date =>l_endation_date-1
,p_warnings =>l_warnings);
delete_declaration
(p_element_entry_id => l_element_entry_id
,p_effective_date => pay_in_utils.next_tax_year(l_effective_date)-1
,p_warnings => l_warnings);
pay_element_entry_api.update_element_entry
(p_datetrack_update_mode => get_update_mode(l_element_entry_id
,l_effective_date)
,p_effective_date => l_effective_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_object_version_number
--Claim Exemption under Sec 80E
,p_input_value_id1 => l_input_values(0).input_value_id
,p_entry_value1 => p_higher_education_loan_80e
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_update_warning => p_warnings
);
delete_declaration
(p_element_entry_id =>l_element_entry_id
,p_effective_date =>l_endation_date-1
,p_warnings =>l_warnings);
delete_declaration
(p_element_entry_id =>l_element_entry_id
,p_effective_date =>l_effective_date
,p_warnings =>l_warnings);
delete_declaration
(p_element_entry_id => l_element_entry_id
,p_effective_date => pay_in_utils.next_tax_year(l_effective_date)-1
,p_warnings => l_warnings);
pay_element_entry_api.update_element_entry
(p_datetrack_update_mode => get_update_mode(l_element_entry_id
,l_effective_date)
,p_effective_date => l_effective_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_object_version_number
--Deduction under Sec 80GGA
,p_input_value_id1 => l_input_values(0).input_value_id
,p_entry_value1 => p_donation_for_research_80gga
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_update_warning => p_warnings
);
delete_declaration
(p_element_entry_id =>l_element_entry_id
,p_effective_date =>l_endation_date-1
,p_warnings =>l_warnings);
delete_declaration
(p_element_entry_id =>l_element_entry_id
,p_effective_date =>l_effective_date
,p_warnings =>l_warnings);
delete_declaration
(p_element_entry_id => l_element_entry_id
,p_effective_date => pay_in_utils.next_tax_year(l_effective_date)-1
,p_warnings => l_warnings);
pay_element_entry_api.update_element_entry
(p_datetrack_update_mode => get_update_mode(l_element_entry_id
,l_effective_date)
,p_effective_date => l_effective_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_object_version_number
--Deduction under Sec 80D
,p_input_value_id1 => l_input_values(0).input_value_id
,p_entry_value1 => p_medical_insurance_prem_80d
,p_input_value_id2 => l_input_values(2).input_value_id
,p_entry_value2 => p_sec_80d_senior_citizen
,p_input_value_id3 => l_input_values(4).input_value_id
,p_entry_value3 => p_med_par_insurance_prem_80d
,p_input_value_id4 => l_input_values(5).input_value_id
,p_entry_value4 => p_sec_80d_par_senior_citizen
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_update_warning => p_warnings
);
delete_declaration
(p_element_entry_id =>l_element_entry_id
,p_effective_date =>l_endation_date-1
,p_warnings =>l_warnings);
delete_declaration
(p_element_entry_id => l_element_entry_id
,p_effective_date => pay_in_utils.next_tax_year(l_effective_date)-1
,p_warnings => l_warnings);
pay_element_entry_api.update_element_entry
(p_datetrack_update_mode => get_update_mode(l_element_entry_id
,l_effective_date)
,p_effective_date => l_effective_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_object_version_number
--Deduction under Sec 80DDB
,p_input_value_id1 => l_input_values(0).input_value_id
,p_entry_value1 => p_disease_treatment_80ddb
,p_input_value_id2 => l_input_values(2).input_value_id
,p_entry_value2 => p_sec_80ddb_senior_citizen
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_update_warning => p_warnings
);
delete_declaration
(p_element_entry_id =>l_element_entry_id
,p_effective_date =>l_endation_date-1
,p_warnings =>l_warnings);
delete_declaration
(p_element_entry_id =>l_element_entry_id
,p_effective_date =>l_effective_date
,p_warnings =>l_warnings);
/* BUG 4251141: STAT UPDATE 2005 CHANGES END HERE */
PROCEDURE declare_tax
(p_assignment_id IN per_assignments_f.assignment_id%TYPE
,p_is_monthly_rent_changed IN VARCHAR2
,p_apr IN NUMBER default null
,p_may IN NUMBER default null
,p_jun IN NUMBER default null
,p_jul IN NUMBER default null
,p_aug IN NUMBER default null
,p_sep IN NUMBER default null
,p_oct IN NUMBER default null
,p_nov IN NUMBER default null
,p_dec IN NUMBER default null
,p_jan IN NUMBER default null
,p_feb IN NUMBER default null
,p_mar IN NUMBER default null
,p_is_chapter6a_changed IN VARCHAR2
,p_pension_fund_80ccc IN NUMBER default null
,p_medical_insurance_prem_80d IN NUMBER default null
,p_med_par_insurance_prem_80d IN NUMBER default NULL
,p_80d_par_prem_changed IN VARCHAR2 DEFAULT NULL
,p_sec_80d_par_senior_citizen IN VARCHAR2 default null
,p_80d_par_snr_changed IN VARCHAR2 DEFAULT NULL
,p_sec_80ddb_senior_citizen IN VARCHAR2 default null
,p_disease_treatment_80ddb IN NUMBER default null
,p_sec_80d_senior_citizen IN VARCHAR2 default null
,p_higher_education_loan_80e IN NUMBER default null
,p_claim_exemp_under_sec_80gg IN VARCHAR2 default null
,p_donation_for_research_80gga IN NUMBER default null
,p_80gg_changed IN VARCHAR2 DEFAULT NULL
,p_80e_changed IN VARCHAR2 DEFAULT NULL
,p_80gga_changed IN VARCHAR2 DEFAULT NULL
,p_80d_changed IN VARCHAR2 DEFAULT NULL
,p_80dsc_planned_value IN VARCHAR2 DEFAULT NULL
,p_80ddb_changed IN VARCHAR2 DEFAULT NULL
,p_80ddbsc_planned_value IN VARCHAR2 DEFAULT NULL
,p_int_on_gen_investment_80L IN NUMBER default null
,p_int_on_securities_80L IN NUMBER default null
,p_is_section88_changed IN VARCHAR2 DEFAULT NULL
,p_deferred_annuity IN NUMBER default null
,p_senior_citizen_sav_scheme IN NUMBER default null
,p_public_provident_fund IN NUMBER default null
,p_post_office_savings_scheme IN NUMBER default null
,p_deposit_in_nsc_vi_issue IN NUMBER default null
,p_deposit_in_nsc_viii_issue IN NUMBER default null
,p_interest_on_nsc_reinvested IN NUMBER default null
,p_house_loan_repayment IN NUMBER default null
,p_notified_mutual_fund_or_uti IN NUMBER default null
,p_national_housing_bank_scheme IN NUMBER default null
,p_unit_linked_insurance_plan IN NUMBER default null
,p_notified_annuity_plan IN NUMBER default null
,p_notified_pension_fund IN NUMBER default null
,p_public_sector_company_scheme IN NUMBER default null
,p_approved_superannuation_fund IN NUMBER default null
,p_infrastructure_bond IN NUMBER default null
,p_tuition_fee_for_child_1 IN NUMBER default null
,p_tuition_fee_for_child_2 IN NUMBER default null
,p_is_other_income_changed IN VARCHAR2
,p_income_from_house_property IN NUMBER default null
,p_profit_and_gain_from_busines IN NUMBER default null
,p_long_term_capital_gain IN NUMBER default null
,p_short_term_capital_gain IN NUMBER default null
,p_income_from_any_other_source IN NUMBER default null
,p_tds_paid_on_other_income IN NUMBER default null
,p_approved_flag IN VARCHAR2 default null
,p_comment_text IN VARCHAR2 default null
,p_effective_date IN DATE default null
,p_warnings OUT NOCOPY VARCHAR2)
IS
--
l_effective_date DATE;
SELECT global_value
FROM ff_globals_f
WHERE global_name = 'IN_PERMANENT_PHYSICAL_DISABILITY_80U'
AND legislation_code = g_legislation_code;
hr_session_utilities.insert_session_row(l_effective_date);
PROCEDURE delete_declaration
(p_element_entry_id IN NUMBER
,p_effective_date IN DATE DEFAULT NULL
,p_warnings OUT NOCOPY VARCHAR2)
IS
--
l_effective_date DATE;
SELECT object_version_number
,effective_end_date
FROM pay_element_entries_f
WHERE element_entry_id = p_element_entry_id
AND c_effective_date BETWEEN effective_start_date
AND effective_end_date;
l_procedure := g_package || 'delete_declaration';
pay_element_entry_api.delete_element_entry
(p_validate => FALSE
,p_datetrack_delete_mode => hr_api.g_delete
,p_effective_date => l_effective_date
,p_element_entry_id => p_element_entry_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_delete_warning => l_warnings
);
,p_procedure_name => 'delete_declaration'
,p_error_text => substr(sqlerrm, 1, 240)
);
END delete_declaration;
SELECT object_version_number
FROM per_assignment_extra_info
WHERE assignment_extra_info_id = c_extra_info_id;
hr_assignment_extra_info_api.update_assignment_extra_info
(p_assignment_extra_info_id => l_extra_info_id
,p_object_version_number => l_object_version_number
,p_aei_information_category => g_approval_info_type
,p_aei_information1 => get_tax_year(p_effective_date)
,p_aei_information2 => p_approval_flag
,p_aei_information3 => substr(p_comment_text, 0, 150));
,P_LAST_UPDATED_DATE IN date default null
,P_ORGANIZATION_ID IN number default null
,P_BUSINESS_GROUP_ID IN number default null
,P_START_DATE IN date default null
,P_GRADE_ID IN number default null
,P_JOB_ID IN number default null
,P_POSITION_ID IN number default null
,P_TAX_AREA_NUMBER IN varchar2 default NULL
,P_APPROVAL_STATUS IN varchar2 default NULL
,P_TAX_YEAR IN varchar2 default NULL
,p_parent_premium IN number default null
,p_parent_sc IN varchar2 default null
)
IS
--
l_effective_date DATE;
SELECT element_type_id
FROM pay_element_types_f
WHERE legislation_code = 'IN'
AND element_name = p_element_name
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT count(entries.element_entry_id)
FROM per_assignments_f assgn
, pay_element_links_f link
, pay_element_types_f types
, pay_element_entries_f entries
WHERE assgn.assignment_id = c_assignment_id
AND link.element_link_id = c_element_link_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_name = c_element_name
AND entries.element_type_id = types.element_type_id
AND entries.element_link_id = link.element_link_id
AND entries.assignment_id = assgn.assignment_id
AND c_effective_date BETWEEN assgn.effective_start_date
AND assgn.effective_end_date
AND c_effective_date BETWEEN link.effective_start_date
AND link.effective_end_date
AND c_effective_date BETWEEN types.effective_start_date
AND types.effective_end_date
AND c_effective_date BETWEEN entries.effective_start_date
AND entries.effective_end_date;
SELECT entries.element_entry_id entry_id
, value1.screen_entry_value Disability_Type
, value2.screen_entry_value Treatment_Amount
, value3.screen_entry_value Disability_Percentage
FROM per_assignments_f assgn
, pay_element_links_f link
, pay_element_types_f types
, pay_element_entries_f entries
, pay_element_entry_values_f value1
, pay_input_values_f inputs1
, pay_element_entry_values_f value2
, pay_input_values_f inputs2
, pay_element_entry_values_f value3
, pay_input_values_f inputs3
WHERE assgn.assignment_id = p_assignment_id
AND link.element_link_id = p_element_link_id-- Changed for bug 4774108
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_name = 'Deduction under Section 80DD'
AND entries.element_type_id = types.element_type_id
AND entries.element_link_id = link.element_link_id
AND entries.assignment_id = assgn.assignment_id
AND value1.element_entry_id = entries.element_entry_id
AND inputs1.input_value_id = value1.input_value_id
AND inputs1.element_type_id = types.element_type_id
AND inputs1.name = 'Disability Type'
AND value2.element_entry_id = entries.element_entry_id
AND inputs2.input_value_id = value2.input_value_id
AND inputs2.element_type_id = types.element_type_id
AND inputs2.name = 'Treatment Amount'
AND value3.element_entry_id = entries.element_entry_id
AND inputs3.input_value_id = value3.input_value_id
AND inputs3.element_type_id = types.element_type_id
AND inputs3.name = 'Disability Percentage'
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
AND p_effective_date BETWEEN entries.effective_start_date
AND entries.effective_end_date
AND p_effective_date BETWEEN inputs1.effective_start_date
AND inputs1.effective_end_date
AND p_effective_date BETWEEN value1.effective_start_date
AND value1.effective_end_date
AND p_effective_date BETWEEN inputs2.effective_start_date
AND inputs2.effective_end_date
AND p_effective_date BETWEEN value2.effective_start_date
AND value2.effective_end_date
AND p_effective_date BETWEEN inputs3.effective_start_date
AND inputs3.effective_end_date
AND p_effective_date BETWEEN value3.effective_start_date
AND value3.effective_end_date;
SELECT entries.element_entry_id entry_id
, value1.screen_entry_value Donation_Type
, value2.screen_entry_value Donation_Amount
FROM per_assignments_f assgn
, pay_element_links_f link
, pay_element_types_f types
, pay_element_entries_f entries
, pay_element_entry_values_f value1
, pay_input_values_f inputs1
, pay_element_entry_values_f value2
, pay_input_values_f inputs2
WHERE assgn.assignment_id = p_assignment_id
AND link.element_link_id = p_element_link_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_name = 'Deduction under Section 80G'
AND entries.element_type_id = types.element_type_id
AND entries.element_link_id = link.element_link_id
AND entries.assignment_id = assgn.assignment_id
AND value1.element_entry_id = entries.element_entry_id
AND inputs1.input_value_id = value1.input_value_id
AND inputs1.element_type_id = types.element_type_id
AND inputs1.name = 'Donation Type'
AND value2.element_entry_id = entries.element_entry_id
AND inputs2.input_value_id = value2.input_value_id
AND inputs2.element_type_id = types.element_type_id
AND inputs2.name = 'Donation Amount'
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
AND p_effective_date BETWEEN entries.effective_start_date
AND entries.effective_end_date
AND p_effective_date BETWEEN inputs1.effective_start_date
AND inputs1.effective_end_date
AND p_effective_date BETWEEN value1.effective_start_date
AND value1.effective_end_date
AND p_effective_date BETWEEN inputs2.effective_start_date
AND inputs2.effective_end_date
AND p_effective_date BETWEEN value2.effective_start_date
AND value2.effective_end_date;
SELECT entries.element_entry_id entry_id
, value1.screen_entry_value Premium_Paid
, value2.screen_entry_value Sum_Assured
FROM per_assignments_f assgn
, pay_element_links_f link
, pay_element_types_f types
, pay_element_entries_f entries
, pay_element_entry_values_f value1
, pay_input_values_f inputs1
, pay_element_entry_values_f value2
, pay_input_values_f inputs2
WHERE assgn.assignment_id = p_assignment_id
AND link.element_link_id = p_element_link_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_name = 'Life Insurance Premium'
AND entries.element_type_id = types.element_type_id
AND entries.element_link_id = link.element_link_id
AND entries.assignment_id = assgn.assignment_id
AND value1.element_entry_id = entries.element_entry_id
AND inputs1.input_value_id = value1.input_value_id
AND inputs1.element_type_id = types.element_type_id
AND inputs1.name = 'Premium Paid'
AND value2.element_entry_id = entries.element_entry_id
AND inputs2.input_value_id = value2.input_value_id
AND inputs2.element_type_id = types.element_type_id
AND inputs2.name = 'Sum Assured'
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
AND p_effective_date BETWEEN entries.effective_start_date
AND entries.effective_end_date
AND p_effective_date BETWEEN inputs1.effective_start_date
AND inputs1.effective_end_date
AND p_effective_date BETWEEN value1.effective_start_date
AND value1.effective_end_date
AND p_effective_date BETWEEN inputs2.effective_start_date
AND inputs2.effective_end_date
AND p_effective_date BETWEEN value2.effective_start_date
AND value2.effective_end_date;
SELECT entries.element_entry_id entry_id
, value1.screen_entry_value Investment_Amount
, value2.screen_entry_value Component_Name
FROM per_assignments_f assgn
, pay_element_links_f link
, pay_element_types_f types
, pay_element_entries_f entries
, pay_element_entry_values_f value1
, pay_input_values_f inputs1
, pay_element_entry_values_f value2
, pay_input_values_f inputs2
WHERE assgn.assignment_id = p_assignment_id
AND link.element_link_id = p_element_link_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_name = p_element_name
AND entries.element_type_id = types.element_type_id
AND entries.element_link_id = link.element_link_id
AND entries.assignment_id = assgn.assignment_id
AND value1.element_entry_id = entries.element_entry_id
AND inputs1.input_value_id = value1.input_value_id
AND inputs1.element_type_id = types.element_type_id
AND inputs1.name = 'Investment Amount'
AND value2.element_entry_id = entries.element_entry_id
AND inputs2.input_value_id = value2.input_value_id
AND inputs2.element_type_id = types.element_type_id
AND inputs2.name = 'Component 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
AND p_effective_date BETWEEN entries.effective_start_date
AND entries.effective_end_date
AND p_effective_date BETWEEN inputs1.effective_start_date
AND inputs1.effective_end_date
AND p_effective_date BETWEEN value1.effective_start_date
AND value1.effective_end_date
AND p_effective_date BETWEEN inputs2.effective_start_date
AND inputs2.effective_end_date
AND p_effective_date BETWEEN value2.effective_start_date
AND value2.effective_end_date;
SELECT element_type_id
FROM pay_element_types_f
WHERE legislation_code = 'IN'
AND element_name = p_element_name
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
g_80dd_values.DELETE;
g_80g_values.DELETE;
g_insurace_values.DELETE;
g_80cce_values.DELETE;