[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select to_number(nvl(PAY_DATE_OFFSET,0)),PRL_INFORMATION1 into l_date_offset,l_pub_sect_flag
from pay_payrolls_f
where payroll_id = p_payroll_id
and p_date between EFFECTIVE_START_DATE and EFFECTIVE_end_date;
select to_number(nvl(PAY_DATE_OFFSET,0)),PRL_INFORMATION1 into l_date_offset,l_pub_sect_flag
from pay_payrolls_f
where payroll_id = p_payroll_id
and p_date between EFFECTIVE_START_DATE and EFFECTIVE_end_date;
select pad.add_information16
from per_addresses pad,
per_all_assignments_f paa
where paa.assignment_id = p_assignment_id
and pad.person_id = paa.person_id
and pad.primary_flag = 'Y'
and pad.style = 'IN'
and p_effective_date between paa.effective_start_date and paa.effective_end_date
and p_effective_date between pad.date_from and nvl(pad.date_to,to_date('31-12-4712','DD-MM-YYYY'));
select start_date
, end_date
, period_num
from per_time_periods
where payroll_id = p_payroll_id
and p_date between start_date and end_date;
select pee.element_entry_id,pee.effective_end_date
from pay_element_types_f pet
,pay_input_values_f piv
,pay_element_entries_f pee
,pay_element_entry_values_f pev
,pay_assignment_actions pac
where pet.element_name ='House Rent Information'
and piv.name = 'JAN'
and pet.legislation_code ='IN'
and pet.element_type_id = piv.element_type_id
and piv.input_value_id = pev.input_value_id
and pee.element_entry_id = pev.element_entry_id
and pee.assignment_id = pac.assignment_id
and pac.assignment_action_id = p_assact
and p_effective_date between pet.effective_start_date and pet.effective_end_date
and p_effective_date between piv.effective_start_date and piv.effective_end_date
and p_effective_date between pee.effective_start_date and pee.effective_end_date ;
select pee.element_entry_id,pee.effective_end_date
from pay_element_types_f pet
,pay_input_values_f piv
,pay_element_entries_f pee
,pay_element_entry_values_f pev
,pay_assignment_actions pac
where pet.element_name ='House Rent Information'
and piv.name = 'JAN'
and pet.legislation_code ='IN'
and pet.element_type_id = piv.element_type_id
and piv.input_value_id = pev.input_value_id
and pee.element_entry_id = pev.element_entry_id
and pee.assignment_id = pac.assignment_id
and pac.assignment_action_id = p_assact
and p_effective_date between pet.effective_start_date and pet.effective_end_date
and p_effective_date between piv.effective_start_date and piv.effective_end_date
and pee.effective_end_date < p_effective_date
and pee.effective_end_date > p_year_start
order by pee.effective_end_date desc ;
SELECT pdb.defined_balance_id
FROM pay_defined_balances pdb
,pay_balance_types pbt
,pay_balance_dimensions pbd
WHERE pbt.balance_name = p_balance_type
AND pbd.dimension_name = p_dimension_name
AND pdb.balance_type_id = pbt.balance_type_id
AND ( pbt.legislation_code = 'IN' OR pbt.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'))
AND ( pbd.legislation_code = 'IN' OR pbd.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'))
AND ( pdb.legislation_code = 'IN' OR pdb.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'))
AND pdb.balance_dimension_id = pbd.balance_dimension_id;
select pee.element_entry_id
from pay_element_types_f pet
,pay_input_values_f piv
,pay_element_entries_f pee
,pay_element_entry_values_f pev
,pay_assignment_actions pac
where pet.element_name ='House Rent Information'
and piv.name = 'JAN'
and pet.legislation_code ='IN'
and pet.element_type_id = piv.element_type_id
and piv.input_value_id = pev.input_value_id
and pee.element_entry_id = pev.element_entry_id
and pee.assignment_id = pac.assignment_id
and pac.assignment_action_id = p_assact_id
and l_prev_effective_date between pet.effective_start_date and pet.effective_end_date
and l_prev_effective_date between piv.effective_start_date and piv.effective_end_date
and l_prev_effective_date between pee.effective_start_date and pee.effective_end_date ;
select nvl(pev.screen_entry_value,0)
,piv.name
from pay_element_entries_f pee,
pay_element_entry_values_f pev,
pay_input_values_f piv
where pee.element_entry_id = p_element_entry_id
and pev.element_entry_id = pee.element_entry_id
and pee.element_type_id = piv.element_type_id
and piv.name = 'MAR'
and pev.input_value_id = piv.input_value_id
and l_effective_date between piv.effective_start_date and piv.effective_end_date
and l_effective_date between pee.effective_start_date and pee.effective_end_date
and l_effective_date between pev.effective_start_date and pev.effective_end_date;
select to_number(nvl(PAY_DATE_OFFSET,0)),PRL_INFORMATION1 into l_date_offset,l_pub_sect_flag
from pay_payrolls_f
where payroll_id = p_payroll_id
and l_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_end_date;
select nvl(pev.screen_entry_value,0)
,piv.name
bulk collect into
p_rent_paid ,
p_month
from pay_element_entries_f pee,
pay_element_entry_values_f pev,
pay_input_values_f piv
where pee.element_entry_id = p_element_entry_id
and pev.element_entry_id = pee.element_entry_id
and pee.element_type_id = piv.element_type_id
and pev.input_value_id = piv.input_value_id
and l_effective_date between piv.effective_start_date and piv.effective_end_date
and l_effective_date between pee.effective_start_date and pee.effective_end_date
and l_effective_date between pev.effective_start_date and pev.effective_end_date
order by decode( piv.name , 'APR',1
, 'MAY',2
, 'JUN',3
, 'JUL',4
, 'AUG',5
, 'SEP',6
, 'OCT',7
, 'NOV',8
, 'DEC',9
, 'JAN',10
, 'FEB',11
, 'MAR',12
);
select nvl(pev.screen_entry_value,0)
,piv.name
bulk collect into
p_rent_paid ,
p_month
from pay_element_entries_f pee,
pay_element_entry_values_f pev,
pay_input_values_f piv
where pee.element_entry_id = p_element_entry_id
and pev.element_entry_id = pee.element_entry_id
and pee.element_type_id = piv.element_type_id
and pev.input_value_id = piv.input_value_id
and l_effective_date between piv.effective_start_date and piv.effective_end_date
and l_effective_date between pee.effective_start_date and pee.effective_end_date
and l_effective_date between pev.effective_start_date and pev.effective_end_date
order by decode( piv.name , 'MAR',1
, 'APR',2
, 'MAY',3
, 'JUN',4
, 'JUL',5
, 'AUG',6
, 'SEP',7
, 'OCT',8
, 'NOV',9
, 'DEC',10
, 'JAN',11
, 'FEB',12
);
/*Bug:3907894 Added ppa.effective_date in the select statement and fetched it in the table l_eff_date_tbl */
select paa.assignment_action_id,pay_in_tax_utils.get_period_number(ppa.payroll_id,ppa.date_earned),ppa.date_earned
bulk collect into l_assact_tbl,l_month_number_tbl,l_eff_date_tbl
from pay_payroll_Actions ppa,
pay_assignment_Actions paa,
per_assignments_f asg -- Added to remove NMV as per bug 4774108
where ppa.payroll_Action_id = paa.payroll_Action_id
and paa.assignment_id = p_assignment_id
and paa.assignment_id = asg.assignment_id-- Added to remove NMV as per bug 4774108
and asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
and ppa.action_type in ('B','V','I','R','Q')
-- and paa.source_action_id is not null -- Commented for bug 4774514
and ppa.date_earned between p_year_start and p_year_end
and ppa.date_earned between asg.effective_start_date and asg.effective_end_date
and paa.action_sequence = ( select max(pac.action_sequence)
from pay_assignment_actions pac
,pay_payroll_actions ppa1
where pac.assignment_id = paa.assignment_id
and pac.payroll_action_id = ppa1.payroll_action_id
and ppa1.date_earned between p_year_start and p_year_end
and trunc(ppa.date_earned,'MM') = trunc(ppa1.date_earned,'MM')
and ppa1.action_type in ('B','V','I','R','Q')
-- and pac.source_action_id is not null -- Commented for bug 4774514
)
order by decode(to_number(to_char(ppa.date_earned,'MM'))
, 4,1
, 5,2
, 6,3
, 7,4
, 8,5
, 9,6
, 10,7
, 11,8
, 12,9
, 1,10
, 2,11
, 3,12 ) ;
FUNCTION historical_update_exists( p_element_entry_id in number ,
p_year_start in date ,
p_year_end in date ,
p_effective_date in date )
return boolean
is
l_exists varchar2(1) ;
select 'Y'
from dual
where exists
(
select element_entry_id
from pay_element_entries_f
where element_entry_id=p_element_entry_id
and effective_start_date between p_year_start
and p_year_end
having count(element_entry_id) > 1
group by element_entry_id
union
select element_entry_id
from pay_element_entries_f
where element_entry_id=p_element_entry_id
and effective_start_date > p_year_start
and effective_start_date < p_year_end
);
l_procedure := g_package ||'historical_update_exists';
end historical_update_exists;
=> HRA can be updated in between a year for previous months.
=> An employee can claim 80 GG / Rent free accomodation in between the year.
Assumption is - if the employee gets House Rent allowance then
Value of Rent Free accomodation becomes entirely taxable
also the employee can not claim exemption under section 80GG
Logic :
=> If there is no date track update on the HRA element in this tax year then there is no
use calculating the hra individually for each month. Balances can be safely used.
calcualte taxable hra only for current month.
=> But if there is any date track update on the House Rent Information element then
=> we need to recalculate the taxable amount for HRA for the entire tax year.
=> Also taxable HRA will be recalculated for the entire tax year in the last month of
of the tax year or termination date
****/
l_assignment_id per_all_assignments_f.assignment_id%type ;
SELECT scl.segment1
FROM hr_soft_coding_keyflex scl
,per_all_assignments_f paf
WHERE paf.assignment_id=p_assignment_id
AND paf.SOFT_CODING_KEYFLEX_ID=scl.SOFT_CODING_KEYFLEX_ID
AND p_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date;
SELECT scl.segment1
,paf.effective_start_date
FROM hr_soft_coding_keyflex scl,
per_all_assignments_f paf
WHERE paf.assignment_id=p_assignment_id
AND paf.SOFT_CODING_KEYFLEX_ID=scl.SOFT_CODING_KEYFLEX_ID
AND paf.effective_start_date BETWEEN p_effective_date AND p_check_date ;
SELECT assignment_id
INTO l_assignment_id
FROM pay_assignment_actions
WHERE assignment_action_id = p_assact_id ;
SELECT SERVICE.actual_termination_date
INTO l_terminate_date
FROM per_assignments_f ASSIGN,
per_periods_of_service SERVICE
WHERE p_effective_date BETWEEN ASSIGN.effective_start_date AND ASSIGN.effective_end_date
AND ASSIGN.assignment_id = l_assignment_id
AND SERVICE.period_of_Service_id = ASSIGN.period_of_service_id;
SELECT nvl(pps.actual_termination_date,paa.effective_end_Date),payroll_id
INTO l_asg_end_date,l_payroll_id
FROM per_Assignments_f paa,-- Modified this for 4774108 to remove NMV
per_periods_of_Service pps
WHERE paa.assignment_id = l_assignment_id
AND paa.period_of_service_id =pps.period_of_service_id
AND paa.effective_end_date = ( SELECT MAX (b.effective_end_date)
FROM per_all_assignments_f b
WHERE paa.assignment_id=b.assignment_id );
/* IF ( historical_update_exists(l_hri_entry_id,l_year_start,l_year_end,p_effective_date)
OR l_last_month = l_year_end OR l_terminate_date is NOT NULL )
THEN
p_hra_taxable_annual := 0;
hr_utility.trace('INHRA: No update to HRI element. Hence direct results');
/*Bug:3919215 Modified the cursor. selected Employer classification of prev emplr */
Cursor c_prev_emp_details is
select nvl(ppm.pem_information1,'X'), -- Designation
fnd_number.canonical_to_number(nvl(ppm.pem_information2,0)), -- Annual Salary
nvl(ppm.pem_information3,'X'), -- PF Number
nvl(ppm.pem_information4,'X'), -- PF Establishment Code
nvl(ppm.pem_information5,'X'), -- EPF Number
nvl(ppm.pem_information6,'X'), -- Emplr class
fnd_number.canonical_to_number(nvl(ppm.pem_information8,0)), -- LTC Curr
fnd_number.canonical_to_number(nvl(ppm.pem_information9,0)), -- Leave Encashment
fnd_number.canonical_to_number(nvl(ppm.pem_information10,0)), -- Gratuity
fnd_number.canonical_to_number(nvl(ppm.pem_information11,0)), -- Retrenchment Amount
fnd_number.canonical_to_number(nvl(ppm.pem_information12,0)), -- VRS
fnd_number.canonical_to_number(nvl(ppm.pem_information13,0)), -- Gross Sal
fnd_number.canonical_to_number(nvl(ppm.pem_information14,0)), -- PF
fnd_number.canonical_to_number(nvl(ppm.pem_information15,0)), -- Ent Alw
fnd_number.canonical_to_number(nvl(ppm.pem_information16,0)), -- PT
fnd_number.canonical_to_number(nvl(ppm.pem_information17,0)), -- TDS
fnd_number.canonical_to_number(nvl(ppm.pem_information18,0)), -- Superannuation
fnd_number.canonical_to_number(nvl(ppm.pem_information19,0)), -- Prev Surcharge
fnd_number.canonical_to_number(nvl(ppm.pem_information20,0)), -- Prev Cess
fnd_number.canonical_to_number(nvl(ppm.pem_information21,0)), -- Exemption under 80gg
fnd_number.canonical_to_number(nvl(ppm.pem_information22,0)), -- Medical Reimbursement
fnd_number.canonical_to_number(nvl(ppm.pem_information23,0)), -- Sec and HE Cess
fnd_number.canonical_to_number(nvl(ppm.pem_information24,0)), -- Exemption under 80ccd
fnd_number.canonical_to_number(nvl(ppm.pem_information25,0)), -- CGHS Exemption under 80D
ppm.end_date
from per_previous_employers ppm,
per_all_assignments_f paa
where paa.assignment_id = p_assignment_id
and paa.person_id = ppm.person_id
and p_date_earned between paa.effective_start_date and paa.effective_end_date;
select people.per_information7
into l_res_status
from per_all_people_f people,
per_all_assignments_f paa
where people.person_id = paa.person_id
and paa.assignment_id= p_assignment_id
and p_effective_date between paa.effective_start_date and paa.effective_end_date
and p_effective_date between people.effective_start_date and people.effective_end_date;
Select element_information1,
element_information2,
element_information3,
element_information4
From pay_element_types_f
Where element_type_id = p_element_type_id
and p_date_earned between effective_start_date and effective_end_date;
select pdf.category,pdf.degree,pdf.dis_information1
from per_disabilities_f pdf,
per_all_assignments_f paa
where paa.assignment_id = p_assignment_id
and paa.person_id = pdf.person_id
and p_date_earned between paa.effective_start_date and paa.effective_end_date
and p_date_earned between pdf.effective_start_date and pdf.effective_end_date
order by nvl(pdf.dis_information1,'N') desc;
select pap.date_of_birth
from per_all_people_f pap,
per_all_assignments_f paa
where paa.assignment_id = p_assignment_id
and pap.person_id = paa.person_id
and p_date_earned between paa.effective_start_date and paa.effective_end_date
and p_date_earned between pap.effective_start_date and pap.effective_end_date;
select pev.screen_entry_value
from pay_element_entries_f pee,
pay_element_entry_values_f pev,
pay_input_values_f piv
where pee.element_entry_id = l_element_entry_id
and pev.element_entry_id = pee.element_entry_id
and pee.element_type_id = piv.element_type_id
and pev.input_value_id = piv.input_value_id
and piv.name = l_curr_mon
and p_date_earned between pev.effective_start_date and pev.effective_end_date
and p_date_earned between pee.effective_start_date and pee.effective_end_date
and p_date_earned between piv.effective_start_date and piv.effective_end_date;
SELECT asg.business_group_id
,asg.payroll_id
FROM per_assignments_f asg
WHERE asg.assignment_id = p_assignment_id
AND asg.primary_flag = 'Y'
AND p_effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date ;
SELECT pel.element_link_id
FROM pay_element_links_f pel,
pay_element_types_f pet
WHERE pet.element_name = p_element_name
AND pet.element_type_id = pel.element_type_id
AND (pel.payroll_id = l_payroll_id
OR (pel.payroll_id IS NULL
AND pel.link_to_all_payrolls_flag = 'Y' ) )
AND pel.business_group_id = l_business_group_id
AND p_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND p_effective_date BETWEEN pel.effective_start_date
AND pel.effective_end_date ;
SELECT element_entry_id
,object_version_number
,effective_start_date
FROM pay_element_entries_f
WHERE assignment_id = p_assignment_id
AND element_link_id = c_element_link_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date ;
SELECT MIN(pee.effective_start_date)
FROM pay_element_entries_f pee
WHERE pee.element_entry_id =p_element_entry_id
AND pee.assignment_id =p_assignment_id
AND pee.element_type_id =p_element_type_id;
SELECT element_information1
,NVL(element_information6,'Y')
FROM pay_element_types_f
WHERE element_type_id = p_element_type_id
AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
SELECT fnd_number.canonical_to_number(exemption_amount)
FROM pay_in_other_perquisites_v
WHERE perquisite_name = p_perk_name;
SELECT prv.result_value
FROM pay_run_result_values prv,
pay_run_results prr,
pay_input_values_f piv,
pay_element_types_f pet
WHERE prv.run_result_id = prr.run_result_id
AND prr.assignment_action_id = c_assignment_action_id
AND prr.element_type_id = pet.element_type_id
AND pet.element_name = c_element_name
AND piv.element_type_id = pet.element_type_id
AND piv.name = c_input_name
AND piv.input_value_id = prv.input_value_id
AND pet.legislation_code = 'IN'
AND piv.legislation_code = 'IN';
select pev.screen_entry_value
from pay_element_entries_f pee,
pay_element_entry_values_f pev,
pay_input_values_f piv,
pay_element_types_f pet
,pay_assignment_actions paa
where pev.element_entry_id = pee.element_entry_id
and pee.element_type_id = piv.element_type_id
and pev.input_value_id = piv.input_value_id
AND piv.element_type_id = pet.element_type_id
and pee.assignment_id = paa.assignment_id
and paa.assignment_action_id = c_assignment_action_id
AND pet.element_name = c_element_name
and piv.name = c_input_name
and c_effective_date between pev.effective_start_date and pev.effective_end_date
and c_effective_date between pee.effective_start_date and pee.effective_end_date
and c_effective_date between piv.effective_start_date and piv.effective_end_date;
SELECT hrl.lookup_code
,hrl.meaning
FROM hr_lookups hrl
WHERE hrl.lookup_type ='IN_LTC_BLOCK'
AND to_number(to_char(p_date,'YYYY')) BETWEEN
to_number(SUBSTR(HRL.LOOKUP_CODE,1,4)) AND to_number(SUBSTR(HRL.LOOKUP_CODE,8,4)) ;
SELECT sum(nvl(ppm.pem_information8,0))
FROM per_previous_employers ppm,
per_all_assignments_f paa
WHERE paa.assignment_id = p_assignment_id
AND p_date_earned BETWEEN paa.effective_start_date AND paa.effective_end_date
AND paa.person_id =ppm.person_id
AND ppm.end_date BETWEEN p_start_date and p_end_date;
SELECT ee.element_entry_id
FROM pay_assignment_actions aa,
pay_payroll_actions pa,
pay_element_entries_f ee,
pay_element_links_f el,
pay_element_types_f et,
pay_element_entry_values_f peev
WHERE aa.payroll_action_id = pa.payroll_action_id
AND aa.assignment_id = ee.assignment_id
and ee.element_entry_id = peev.element_entry_id
and peev.input_value_id = p_input_value_id
and nvl(peev.screen_entry_value,'N') = p_flag_value
AND pa.date_earned BETWEEN ee.effective_start_date
AND ee.effective_end_date
AND pa.date_earned BETWEEN peev.effective_start_date
AND peev.effective_end_date
AND ee.element_link_id = el.element_link_id
AND pa.date_earned BETWEEN el.effective_start_date
AND el.effective_end_date
AND el.element_type_id = et.element_type_id
AND et.element_type_id= p_element_type_id
AND pa.date_earned BETWEEN et.effective_start_date
AND et.effective_end_date
AND aa.assignment_action_id = p_assignment_action_id
AND NOT EXISTS (SELECT 1 FROM pay_quickpay_exclusions pqe
WHERE pqe.assignment_action_id =nvl(aa.source_action_id,aa.assignment_Action_id)
AND pqe.element_entry_id = ee.element_entry_id)
ORDER BY ee.element_entry_id ;
SELECT peev.screen_entry_value
FROM pay_element_entry_values_f peev
WHERE peev.element_entry_id = l_entry_id
AND peev.input_value_id = l_input_value_id
AND p_date_earned between peev.effective_start_date and peev.effective_end_date;
SELECT piv.input_value_id
FROM pay_input_values_f piv
WHERE piv.element_type_id = p_element_type_id
AND piv.NAME = p_input_name
AND p_date_earned BETWEEN piv.effective_start_date AND piv.effective_end_date;
SELECT global_value
from ff_globals_f ffg
WHERE ffg.global_name = l_global_name
AND p_date_earned BETWEEN ffg.effective_start_date AND ffg.effective_end_date;
SELECT count(*)
FROM pay_run_results prr
,pay_run_result_values prrv1
,pay_run_result_values prrv2
,pay_assignment_actions paa
,pay_payroll_actions ppa
where prr.run_result_id =prrv1.run_result_id
and prrv1.input_value_id = p_exempted_id
and prrv2.input_value_id = p_carry_over_id
and prr.run_result_id =prrv2.run_result_id
and prrv1.result_value = p_exempted
and nvl(prrv2.result_value,'N') = p_carry_over
and prr.element_type_id =p_element_type_id
and prr.assignment_action_id =paa.assignment_action_id
AND paa.assignment_action_id <= p_assignment_action_id
and paa.assignment_id = p_assignment_id
and prr.status in ('P','PA')
and paa.payroll_action_id =ppa.payroll_action_id
and ppa.date_earned BETWEEN p_start_date and p_end_date;
IF l_curr_element_entry.COUNT > 0 THEN l_curr_element_entry.delete; END IF;
IF l_element_entry_id.COUNT > 0 THEN l_element_entry_id.delete; END IF;
IF l_element_entry_id.COUNT > 0 THEN l_element_entry_id.delete; END IF;
SELECT hsc.segment2
,hsc.segment3
,hsc.segment4
FROM per_assignments_f paf
,hr_soft_coding_keyflex hsc
WHERE paf.assignment_id = p_assignment_id
AND paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
AND paf.business_group_id = p_business_group_id
AND p_date BETWEEN paf.effective_start_date
AND paf.effective_end_date;
SELECT MAX(asg.effective_end_date) + 1
FROM per_all_assignments_f asg
, hr_soft_coding_keyflex scl
WHERE asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND nvl(scl.segment1,'-1')<> TO_CHAR(p_tax_unit_id)
AND asg.assignment_id = p_assignment_id
AND asg.effective_end_date < p_effective_date;
select min(asg.effective_start_date)
from per_all_assignments_f asg
, hr_soft_coding_keyflex scl
WHERE asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment1 = TO_CHAR(p_tax_unit_id)
AND asg.assignment_id = p_assignment_id
AND asg.effective_start_date < p_effective_date;
SELECT MIN(asg.effective_start_date) -1
FROM per_all_assignments_f asg
, hr_soft_coding_keyflex scl
WHERE asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND NVL(scl.segment1,'-1')<> TO_CHAR(p_tax_unit_id)
AND asg.assignment_id = p_assignment_id
AND asg.effective_start_date > p_effective_date;
select max(asg.effective_end_date)
from per_all_assignments_f asg
, hr_soft_coding_keyflex scl
WHERE asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment1 = TO_CHAR(p_tax_unit_id)
AND asg.assignment_id = p_assignment_id
AND asg.effective_end_date >= p_effective_date;
SELECT fnd_number.canonical_to_number(glb.global_value)
FROM ff_globals_f glb
WHERE glb.global_name = p_global_name
AND p_date BETWEEN glb.effective_start_date
AND glb.effective_end_date
AND glb.legislation_code='IN';
SELECT pep.per_information4 INTO l_pan
FROM per_all_people_f pep,
per_all_assignments_f asg,
per_periods_of_service pos
WHERE asg.assignment_id = p_assignment_id
AND asg.person_id = pep.person_id
AND pos.person_id = asg.person_id
AND pos.period_of_service_id = asg.period_of_service_id
AND decode(pos.actual_termination_date,'',p_pay_end_date,pos.actual_termination_date) BETWEEN asg.effective_start_date AND asg.effective_end_date
AND decode(pos.actual_termination_date,'',p_pay_end_date,pos.actual_termination_date) BETWEEN pep.effective_start_date AND pep.effective_end_date ;
SELECT fnd_number.canonical_to_number(glb.global_value)
FROM ff_globals_f glb
WHERE glb.global_name = p_global_name
AND p_date BETWEEN glb.effective_start_date
AND glb.effective_end_date
AND glb.legislation_code='IN';
SELECT pep.per_information4 INTO l_pan
FROM per_all_people_f pep,
per_all_assignments_f asg,
per_periods_of_service pos
WHERE asg.assignment_id = p_assignment_id
AND asg.person_id = pep.person_id
AND pos.person_id = asg.person_id
AND pos.period_of_service_id = asg.period_of_service_id
AND decode(pos.actual_termination_date,'',p_pay_end_date,pos.actual_termination_date) BETWEEN asg.effective_start_date AND asg.effective_end_date
AND decode(pos.actual_termination_date,'',p_pay_end_date,pos.actual_termination_date) BETWEEN pep.effective_start_date AND pep.effective_end_date ;
SELECT MAX(paa.assignment_action_id)
FROM pay_payroll_Actions ppa
,pay_assignment_actions paa
WHERE paa.assignment_id =p_assignment_id
AND paa.payroll_action_id = ppa.payroll_Action_id
AND ppa.action_type in('R','Q')
AND TRUNC(ppa.date_earned,'MM') = TRUNC(l_le_end_date,'MM')
AND paa.source_action_id IS NULL;
SELECT 1
FROM per_assignments_f paf,
hr_soft_coding_keyflex scl
WHERE paf.assignment_id = p_assignment_id
AND scl.segment1 = TO_CHAR(p_tax_unit_id)
AND paf.SOFT_CODING_KEYFLEX_ID=scl.SOFT_CODING_KEYFLEX_ID
AND paf.effective_end_date BETWEEN p_start_date AND p_pre_le_end_date;
SELECT NVL(ppm.pem_information22,0), -- Medical Reimbursement
ppm.end_date
FROM per_previous_employers ppm,
per_all_assignments_f paa
WHERE paa.assignment_id = p_assignment_id
AND paa.person_id = ppm.person_id
AND p_date_earned BETWEEN paa.effective_start_date AND paa.effective_end_date;
SELECT to_number(substr(max(lpad(prev_asg.action_sequence,15,'0')||prev_asg.assignment_action_id),16))
FROM pay_assignment_actions prev_asg,
pay_payroll_actions prev_pay,
per_time_periods ptp,
pay_assignment_actions cur_asg,
pay_payroll_actions cur_pay
WHERE prev_asg.assignment_id = p_assignment_id
AND prev_asg.payroll_action_id = prev_pay.payroll_action_id
AND prev_pay.action_type IN('R','Q')
AND prev_asg.source_action_id IS NOT NULL
AND prev_pay.effective_date < ptp.start_date
AND cur_asg.assignment_action_id = p_assignment_action_id
AND cur_asg.payroll_action_id = cur_pay.payroll_action_id
AND prev_asg.action_sequence <= cur_asg.action_sequence
AND cur_pay.effective_date between ptp.start_date and ptp.end_date
AND ptp.payroll_id = cur_pay.payroll_id
AND EXISTS (SELECT ''
FROM pay_run_results prr,
pay_element_types_f pet
WHERE prr.assignment_action_id = prev_asg.assignment_action_id
AND prr.element_type_id = pet.element_type_id
AND pet.legislation_code ='IN'
AND pet.element_name ='Form16 Income Tax Information')
AND prev_pay.date_earned >= p_le_start_date;
SELECT 'Y' FROM
per_time_periods ptp,
pay_payroll_actions pact,
pay_assignment_actions assact,
pay_payroll_actions bact,
pay_assignment_actions bal_assact,
pay_run_types_f prt
WHERE bal_assact.assignment_action_id = p_assignment_action_id
AND bal_assact.payroll_action_id = bact.payroll_action_id
AND assact.payroll_action_id = pact.payroll_action_id
AND assact.action_sequence <= bal_assact.action_sequence
AND assact.assignment_id = bal_assact.assignment_id + DECODE(ptp.start_date, null, 0, 0)
AND bact.effective_date BETWEEN ptp.start_date AND ptp.end_date
AND ptp.payroll_id = bact.payroll_id
AND pact.effective_date >= ptp.start_date
AND pact.effective_date <= ptp.end_date
AND pact.action_type in('R','Q')
AND prt.run_type_id = ASSACT.run_type_id
AND prt.run_type_name ='Regular Run'
AND EXISTS ( SELECT '1' FROM
pay_run_results prr,
pay_element_types_f pet
WHERE prr.assignment_action_id = ASSACT.assignment_action_id
AND prr.element_type_id = pet.element_type_id
AND pet.legislation_code ='IN'
AND pet.element_name ='Form16 Income Information');
SELECT fnd_number.canonical_to_number(glb.global_value)
FROM ff_globals_f glb
WHERE glb.global_name = p_global_name
AND p_date BETWEEN glb.effective_start_date
AND glb.effective_end_date
AND glb.legislation_code='IN';