[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
REM insert a line feed.
REM swamukhe 26-MAY-2008 115.25 Modified the date format for TX from 'YYYYMMDD' to
REM 'MMDDYYYY'
REM lbodired 21-AUG-2009 115.26 Modified the 'fl_new_hire_record' function to
REM insert carriage return
REM nkjaladi 26-JUL-2011 115.27 10014495 Modified 'ny_1h_record' and
REM 'a03_ny_new_hire_record' functions as per
REM requirement of New York dependent health
REM benefits available.
REM (Made changes to package header also)
REM nkjaladi 26-JUL-2011 115.28 10014495 Modified 'a03_ny_new_hire_header' and
REM 'a03_ny_new_hire_record' functions as per
REM requirement of New York dependent health
REM benefits available.
REM (Made changes to package header also)
REM ybudamal 11-NOV-2011 115.29 13082528 Modified 'fl_new_hire_record', 'a03_fl_new_hire_header'
REM and 'a03_fl_new_hire_record' functions to add the parameter
REM 'p_medical_insr_avail' and modified output as per the
REM Florida New Hires Electronic Reporting Specifications
REM nkjaladi 18-MAY-2012 115.30 10387522 Added new functions for SC, MI, PA,
REM OH, AR, MD and LA states.
REM agarai 31-MAY-2012 115.31 10387522 Added nocopy to OUT parameters
REM to remove GSCC errors.
REM agarai 01-JUN-2012 115.32 10387522 Changed substr parameters in
REM a03_ny_new_hire_record to remove
REM errors.
REM nkjaladi 07-JUN-2012 115.33 10387522 Corrected typos to resolve the
REM issue of columns wrongly getting
REM ordered.
REM nkjaladi 07-JUN-2012 115.34 10387522 In A03 PA record corrected the
REM date format
REM agarai 31-AUG-2012 115.35 14532612 Modified 'a03_ny_new_hire_record'
REM 14470601 and 'ny_1h_record' to add
REM 'p_new_hire_indicator' parameter,
REM to correct date formats of hire
REM date and Dep. health insurance
REM start date and to display the
REM eligibility indicator properly when
REM p_medical_avail is 'E'.
REM ========================================================================
*/
--
-- Global variables and Constants.
--
g_character_set varchar2(80);
select address_line_1, address_line_2, address_line_3,
town_or_city, region_1, region_2, postal_code ,style
from hr_locations
where location_id = p_location_id;
select address_line_1, address_line_2, address_line_3,
town_or_city, region_1, region_2, postal_code
,country,style
from hr_locations
where location_id = p_location_id;
select address_line1, address_line2, address_line3,
town_or_city, region_1, region_2, postal_code
,style
from per_addresses
where person_id = p_person_id
and primary_flag = 'Y'
and nvl(date_to, sysdate) >= sysdate;
select address_line1, address_line2, address_line3,
town_or_city, region_1, region_2
, postal_code ,country ,style
from per_addresses
where person_id = p_person_id
and primary_flag = 'Y'
and nvl(date_to, sysdate) >= sysdate;
Select ppf.first_name || ' ' || ppf.last_name,
job.name,
ppf.work_telephone
From
--per_people_f ppf, --view bug 4912696
per_all_people_f ppf, --table
--per_assignments_f paf, --view bug 4912696
per_all_assignments_f paf, --table
per_jobs job
Where
ppf.person_id = p_person_id
And ppf.business_group_id + 0 = p_business_group_id
And p_report_date between paf.effective_start_date
and paf.effective_end_date
And ppf.person_id = paf.person_id
And paf.assignment_type = 'E'
And paf.primary_flag = 'Y'
And p_report_date between paf.effective_start_date
and paf.effective_end_date
-- BUG2919553
And ppf.effective_start_date =
(select max(ppf1.effective_start_date)
from per_all_people_f ppf1 --table bug 4912696
where ppf1.person_id = ppf.person_id
and ppf1.effective_start_date <= p_report_date
)
-- End of BUG2919553
And paf.job_id = job.job_id(+);
Select ppf.first_name
, ppf.last_name
From per_all_people_f ppf
Where ppf.person_id = p_person_id
And ppf.business_group_id + 0 = p_business_group_id
And ppf.effective_start_date =
(select max(ppf1.effective_start_date)
from per_all_people_f ppf1
where ppf1.person_id = ppf.person_id
and ppf1.effective_start_date <= p_report_date
);
Select
peev.effective_start_date,
hl.meaning,
asg.normal_hours,
hscf.segment4,
peev.screen_entry_value,
asg.payroll_id
Into
l_effective_start_date,
l_pay_basis,
l_normal_hours,
l_work_schedule,
l_salary,
l_payroll_id
From
pay_element_entry_values_f peev,
pay_element_entries_f pee,
per_pay_bases ppb,
hr_soft_coding_keyflex hscf,
per_assignments_f asg,
hr_lookups hl
Where
hl.application_id = 800
And hl.lookup_type = 'PAY_BASIS'
And hl.lookup_code = ppb.pay_basis
And peev.element_entry_id = pee.element_entry_id
And peev.effective_start_date = pee.effective_start_date
And peev.input_value_id+0 = ppb.input_value_id
And asg.pay_basis_id = ppb.pay_basis_id
And pee.assignment_id = asg.assignment_id
And hscf.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
And asg.assignment_id = p_assignment_id
And asg.business_group_id = p_business_group_id
And pee.effective_start_date between asg.effective_start_date
and asg.effective_end_date
And p_report_date between pee.effective_start_date
and pee.effective_end_date;
select decode(ppb.pay_basis,'MONTHLY','M',
'ANNUAL','Y',
'HOURLY','H',
'PERIOD','P'
)
into p_frequency
from per_pay_bases ppb ,
per_all_assignments_f paf
where paf.assignment_id = p_assignment_id
and ppb.pay_basis_id = paf.pay_basis_id
and ppb.BUSINESS_GROUP_ID=p_business_group_id ;
SELECT ptp.number_per_fiscal_year,
decode(ptp.number_per_fiscal_year
,26 ,'B'
,12 ,'M'
,24 ,'S'
,52 ,'W'
,1 ,'Y'
,'R')
INTO l_number_per_fiscal_year ,
p_frequency
FROM pay_payrolls_f prl,
per_time_period_types ptp,
per_all_assignments_f paf
WHERE ptp.period_type = prl.period_type
and paf.assignment_id = p_assignment_id
and prl.payroll_id = paf.payroll_id
and prl.business_group_id + 0 = p_business_group_id
and p_report_date between paf.effective_start_date
and paf.effective_end_date
and p_report_date between prl.effective_start_date
and prl.effective_end_date;