[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Added select to 'get_payroll_action'
for action_type 'P'.
Added 'get_element_type_name'.
40.5 17-FEB-94 GPayton-McDowall added get_ben_class_name
40.6 01-MAR-94 GPayton-McDowall added get_cobra_qualifying_event
get_cobra_status
40.7 23-Mar-94 MSwanson Added get_org_name, get_est_tax_unit and
get_org_hierarchy_name for EEO reporting.
40.8 25-Mar-94 MSwanson Added get_county_address for eeo and tax reps.
Added get_activity for eeo reps.
*********************************************************************************************************
40.0 18-May-94 M Gilmore Moved from US
40.1 03-Jul-94 A D Roussel Tidied up for 10G install.
40.2 03-Jul-94 hrdev Added Header
40.3 04-Jul-94 A D Roussel Fix symbol name message on load in sql+
40.x 12-Oct-94 MSwanson Add get_defined_balance_by_type.
Add get_employee_address.
Bug G1725 - remove use of index on
business_group_id.
Add get_person_name.
40.7 20-apr-95 MSwanson Add get_career_path_name.
40.8 29-Aug-95 MSwanson Tidy up balances for W2. Remove
many calls as we use new Bal API.
40.9 19-Oct-95 MSwanson Add get_state_name.
40.10 20-Oct-95 MSwanson Add get_new_hire_contact.
40.11 25-Oct-95 MSwanson Add get_salary.
40.12 01-Nov-95 Jthuring Removed error checking from end of script
40.13 29-Nov-95 mswanson Get normal_hours and work_schedule
in get_salary, so non-salaried emps
get calculated.
40.14 30-Apr-96 nbristow Now Caching defined_balance_id in
get_defined_balance_id to improve
performance (ChequeWriter).
40.15 21-May-96 nlee Bug 366087 Add new procedure
get_address_31.
Change 'Section 125' to
'Dependent Care'.
Add function get_location_code.
40.16 19-Aug-96 nlee Add a primary_flag = 'Y' condition to
get_employee_address procedure.
Add a new balance id and name called
'12' and 'W2 Fringe Benefit' respectively in get_defined_balance_by_type
40.17 06-Sep-96 ssdesai upgrade script py364888.sql creates a balance
called W2 Fringe Benefits (plural).
40.18 04-NOV-1996 hekim In get_person_name
-- changed l_person_name to VARCHAR2(240)
from VARCHAR2(60)
In get_address_31
-- take substr of line1,line2, line3 to
handle fields which are greater than 31 chars
40.19 05-NOV-1996 hekim Add function get_address_3lines
40.20 18-NOV-1996 hekim Added effective_date to get_address_3lines
40.21 04-DEC-1996 hekim Move state code on same line as city in get_address_31
40.22 09-JAN-1997 nlee Add a condition in get_person_name so that when it will get
the latest name in the effective_start_date and this will solve
the problem of fetching more than one row.
40.23 26-FEB-1997 hekim In get_address_3lines, take substring of city name
40.24 28-Jul-1997 nlee Change pay_state_rules to pay_us_states in get_state_name.
Change the selection of all rows from hr_locations and
per_addresses in get_address and get_employee_address
functions to selection of the specific rows that are
needed to increase performance and avoid overflow problem.
Change the sql statement to cursor statement in get_new_hire_contact
and add the exception handlers to the function.
40.25 08-DEC-1997 tlacey Added effective_date to get_employee_address.
115.1 04-MAR-1999 jmoyano MLS changes. Added references to _TL tables.
115.2 09-MAR-1999 sdoshi BUG 844582 - Ensure that all functions return a value,
whether it completes successfully or it fails via the
exception handler - default return value is NULL.
115.3 25-MAY-1999 mmukherj Added legislation code in the
get_defined_balance_id procedure.
115.4 18-APR-2000 mcpham Added function fnc_get_payee for report PAYRPTPP and bug 1063477
115.6 30-APR-2002 gperry Fixed WWBUG 2331831.
115.7 25-FEB-2003 vbanner Added function get_hr_est_tax_unit to
fix bug 2722353. (the new function will
return a tax id for the top org in a
hierarchy).
115.8 17-OCT-2003 ynegoro 3141907 Updated get_hr_est_tax_unit
Fixed GSCC warning, Added nocopy for out
parameters
115.9 23-OCT-2003 ynegoro 3182433 Added get_top_org_id function
115.10 09-APR-04 ynegoro 3545006 Updated get_top_org_id function
Added csr_get_max_child_id cursor;
115.16 11-OCT-2006 rpasumar 5577840 Selected hierarchy_node_id instead of entity_id in c_est_entity cursor of the function verify_state.
115.17 12-MAR-2008 psugumar 6774707 Added get_employee_address40
Consolidate Report
========================================================================================================
*/
-- Global declarations
type char_array is table of varchar(81) index by binary_integer;
SELECT SUBSTR(INITCAP(RTRIM(ppf.title)||' '||RTRIM(ppf.first_name)||' '||RTRIM(ppf.last_name)),1,60)
FROM per_addresses addr,
per_people_f ppf
WHERE ppf.person_id = IN_payee_id
AND ppf.business_group_id+0 = IN_business_group_id
AND IN_payment_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND addr.person_id(+) = ppf.person_id
AND addr.primary_flag(+) = 'Y'
AND IN_payment_date BETWEEN addr.date_from(+)
AND NVL(addr.date_to, IN_payment_date);
SELECT SUBSTR(hou.name,1,240)
FROM hr_locations loc,
hr_organization_units hou
WHERE hou.organization_id = IN_payee_id
AND hou.business_group_id = IN_business_group_id
AND IN_payment_date BETWEEN hou.date_from
AND NVL(hou.date_to, IN_payment_date)
AND loc.location_id(+) = hou.location_id;
Select
peev.effective_start_date,
hl.meaning,
asg.normal_hours,
hscf.segment4,
peev.screen_entry_value,
decode(asg.frequency,'W','WEEK', -- Bug 3669973
'M','MONTH',
'Y','YEAR',
null) frequency
Into
l_effective_start_date,
l_pay_basis,
l_normal_hours,
l_work_schedule,
l_salary,
l_frequency
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 ppf.full_name,
job.name,
ppf.work_telephone
From
per_people_f ppf,
per_assignments_f paf,
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
And paf.job_id = job.job_id(+);
select address_line_1, address_line_2, address_line_3,
town_or_city, region_2, postal_code
from hr_locations
where location_id = p_location_id;
select *
from hr_locations
where location_id = p_location_id;
select * from per_addresses
where person_id = p_person_id
and primary_flag='Y'
and nvl(date_to, p_effective_date) >= p_effective_date;
select address_line1, address_line2, address_line3,
town_or_city, region_2, postal_code
from per_addresses
where person_id = p_person_id
and primary_flag = 'Y'
and nvl(date_to, sysdate) >= sysdate;
insert into pay_us_rpt_totals(ATTRIBUTE30,attribute1) values(f_address,'test1');
select * from hr_locations
where location_id = p_location_id;
select * from hr_establishments_v
where establishment_id = p_establishment_id;
SELECT consolidation_set_name
INTO l_consolidation_set_name
FROM pay_consolidation_sets
WHERE consolidation_set_id = p_consolidation_set_id;
SELECT ppt_tl.payment_type_name
INTO l_payment_type_name
FROM pay_payment_types_tl ppt_tl,
pay_payment_types ppt
WHERE ppt_tl.payment_type_id = ppt.payment_type_id
and userenv('LANG') = ppt_tl.language
and ppt.payment_type_id = p_payment_type_id;
SELECT pet_tl.element_name
INTO l_element_type_name
FROM pay_element_classifications pec,
pay_element_types_f_tl pet_tl,
pay_element_types_f pet
WHERE pet_tl.element_type_id = pet.element_type_id
and userenv('LANG') = pet_tl.language
and pec.classification_id = pet.classification_id
AND pet.element_type_id = p_element_type_id;
SELECT name
INTO l_tax_unit_name
FROM hr_organization_units
WHERE organization_id = p_tax_unit_id;
SELECT distinct full_name
INTO l_person_name
FROM per_people_f ppf
WHERE person_id = p_person_id
AND ppf.effective_start_date =
(select max(effective_start_date)
from per_people_f ppf1
where ppf1.person_id = ppf.person_id);
SELECT action_type
INTO l_action_type
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT ppa.display_run_number || '-' || pcs.consolidation_set_name
|| '-' || ppa.effective_date || '-' || ppa.payroll_action_id
INTO l_payroll_action_name
FROM pay_consolidation_sets pcs,
pay_payroll_actions ppa
WHERE ppa.consolidation_set_id = pcs.consolidation_set_id
AND ppa.payroll_action_id = p_payroll_action_id;
SELECT ppa.display_run_number || '-' || has.assignment_set_name
|| '-' || pes.element_set_name
INTO l_payroll_action_name
FROM hr_assignment_sets has,
pay_element_sets pes,
pay_payroll_actions ppa
WHERE has.assignment_set_id(+) = ppa.assignment_set_id
AND pes.element_set_id(+) = ppa.element_set_id
AND ppa.payroll_action_id = p_payroll_action_id;
SELECT ppa.display_run_number || '-' || ppe.full_name
INTO l_payroll_action_name
FROM per_people_f ppe,
per_all_assignments_f pas,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE ppe.person_id = pas.person_id
AND pas.assignment_id = paa.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.payroll_action_id = p_payroll_action_id
AND ppa.effective_date between ppe.effective_start_date
and ppe.effective_end_date
AND ppa.effective_date between pas.effective_start_date
and pas.effective_end_date;
SELECT org_information9
INTO l_legislation_code
FROM hr_organization_information
WHERE organization_id = p_business_group_id
AND UPPER(org_information_context) = 'BUSINESS GROUP INFORMATION';
SELECT pdb.defined_balance_id
INTO l_defined_balance_id
FROM pay_defined_balances pdb
, pay_balance_dimensions pbd
, pay_balance_types pbt
WHERE pbt.balance_name = p_balance_name
AND ((pbt.business_group_id IS NULL
AND pbt.legislation_code = 'US')
OR pbt.business_group_id + 0 = p_business_group_id)
AND pbd.database_item_suffix = p_dimension_suffix
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND (pdb.business_group_id IS NULL
OR pdb.business_group_id + 0 = p_business_group_id);
SELECT pdb.defined_balance_id
INTO l_defined_balance_id
FROM pay_defined_balances pdb
, pay_balance_dimensions pbd
, pay_balance_types pbt
WHERE pbt.reporting_name = p_reporting_name
AND pbd.database_item_suffix = p_dimension_suffix
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id;
SELECT pbt.balance_type_id
INTO l_balance_type_id
FROM pay_balance_types pbt
WHERE pbt.balance_name = l_balance_type_name
AND pbt.business_group_id is null
AND pbt.legislation_code = 'US';
SELECT pdb.defined_balance_id
INTO l_defined_balance_id
FROM pay_defined_balances pdb
, pay_balance_dimensions pbd
, pay_balance_types pbt
WHERE pbt.balance_type_id = l_balance_type_id
AND pbd.database_item_suffix = p_dimension_suffix
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id;
select benefit_classification_name
into v_benefit_class_name
from ben_benefit_classifications
where benefit_classification_id = p_benefit_classification_id;
SELECT meaning
INTO v_qualifying_event_meaning
FROM hr_lookups
WHERE lookup_type = 'US_COBRA_EVENT'
AND lookup_code = p_qualifying_event;
SELECT meaning
INTO v_cobra_status_meaning
FROM hr_lookups
WHERE lookup_type = 'US_COBRA_STATUS'
AND lookup_code = p_cobra_status;
SELECT ose.organization_id_parent
FROM per_org_structure_elements ose
WHERE ose.org_structure_version_id = p_org_structure_version_id
START WITH ose.organization_id_child = p_starting_org_id
CONNECT BY PRIOR ose.organization_id_parent = ose.organization_id_child
AND ose.org_structure_version_id = p_org_structure_version_id;
SELECT 'Y'
INTO tax_unit_flag
FROM hr_organization_information hoi
WHERE hoi.organization_id = parent_tax_unit_id
AND hoi.ORG_INFORMATION1 = 'HR_LEGAL'
AND hoi.ORG_INFORMATION2 = 'Y' ;
SELECT decode(tax_unit_id,'','N','Y')
INTO tax_unit_flag
FROM hr_tax_units_v htuv,
hr_organization_units hou
WHERE htuv.tax_unit_id(+) = hou.organization_id
AND hou.organization_id = parent_tax_unit_id;
SELECT ose.organization_id_parent
FROM per_org_structure_elements ose
WHERE ose.org_structure_version_id = p_org_structure_version_id
START WITH ose.organization_id_child = p_starting_org_id
CONNECT BY PRIOR ose.organization_id_parent = ose.organization_id_child
AND ose.org_structure_version_id = p_org_structure_version_id;
SELECT hoi.organization_id
FROM hr_organization_information hoi
WHERE hoi.organization_id = p_starting_org_id
AND hoi.ORG_INFORMATION1 = 'HR_LEGAL'
AND hoi.ORG_INFORMATION2 = 'Y' ;
SELECT htuv.tax_unit_id
FROM hr_tax_units_v htuv
WHERE htuv.tax_unit_id = p_starting_org_id;
SELECT hoi.organization_id
FROM hr_organization_information hoi
WHERE hoi.organization_id = p_tax_unit_id
AND hoi.ORG_INFORMATION1 = 'HR_LEGAL'
AND hoi.ORG_INFORMATION2 = 'Y' ;
SELECT htuv.tax_unit_id
FROM hr_tax_units_v htuv,
hr_organization_units hou
WHERE htuv.tax_unit_id(+) = hou.organization_id
AND hou.organization_id = p_tax_unit_id;
SELECT decode(hr_tax_unit_id,'','N','Y')
INTO hr_tax_unit_flag
FROM hr_tax_units_v htuv,
hr_organization_units hou
WHERE htuv.tax_unit_id(+) = hou.organization_id
AND hou.organization_id = parent_hr_tax_unit_id;
SELECT pos.name
INTO l_org_hierarchy_name
FROM per_organization_structures pos,
per_org_structure_versions posv
WHERE pos.organization_structure_id = posv.organization_structure_id
AND posv.org_structure_version_id = p_org_structure_version_id;
SELECT state_name
INTO l_state_name
FROM pay_us_states
WHERE state_abbrev = p_state_code;
SELECT name
INTO l_org_name
FROM hr_organization_units
WHERE organization_id = p_organization_id
AND business_group_id + 0 = p_business_group_id;
SELECT location_code
INTO l_location_code
FROM hr_locations
WHERE location_id = p_location_id;
SELECT name
INTO l_career_path_name
FROM per_career_paths
WHERE career_path_id = p_career_path_id
AND business_group_id + 0 = p_business_group_id;
SELECT aap_organization_id
INTO l_aap_organization_id
FROM hr_aap_organizations_v
WHERE aap_name = p_aap_name
AND business_group_id + 0 = p_business_group_id;
select organization_id_parent
from per_org_structure_elements
where business_group_id = p_business_group_id
and org_structure_version_id = p_org_structure_version_id
and organization_id_child = l_organization_id_child;
select '1'
from per_org_structure_elements
where business_group_id = p_business_group_id
and org_structure_version_id = p_org_structure_version_id;
select max(organization_id_child)
from per_org_structure_elements
where business_group_id = p_business_group_id
and org_structure_version_id = p_org_structure_version_id;
select
pghn1.hierarchy_node_id
from
per_gen_hierarchy_nodes pghn1
,hr_location_extra_info hlei1
,hr_location_extra_info hlei2
,hr_locations_all eloc
where
(pghn1.hierarchy_version_id = P_HIERARCHY_VERSION_ID
and pghn1.node_type = 'EST'
and eloc.location_id = pghn1.entity_id
and hlei1.location_id = pghn1.entity_id
and hlei1.location_id = hlei2.location_id
and hlei1.information_type = 'VETS-100 Specific Information'
and hlei1.lei_information_category= 'VETS-100 Specific Information'
and hlei2.information_type = 'Establishment Information'
and hlei2.lei_information_category= 'Establishment Information'
and hlei2.lei_information10 = 'N'
and eloc.region_2 = P_STATE);
select count('ass')
from
per_all_assignments_f ass,
per_gen_hierarchy_nodes pgn
where
ass.business_group_id = P_BUSINESS_GROUP_ID
and ass.assignment_type = 'E'
and ass.primary_flag = 'Y'
-- Bug# 5577840
and P_DATE_END between ass.effective_start_date and ass.effective_end_date
-- Replaced the following conditions with the above query.
/*
and ass.effective_start_date <= P_DATE_END
and ass.effective_end_date >= P_DATE_START
*/
and ass.effective_start_date = (select max(paf2.effective_start_date)
from per_all_assignments_f paf2
where paf2.person_id = ass.person_id
and paf2.primary_flag = 'Y'
and paf2.assignment_type = 'E'
and paf2.effective_start_date
<= P_DATE_END)
AND EXISTS (
SELECT 'X'
FROM HR_ORGANIZATION_INFORMATION HOI1,
HR_ORGANIZATION_INFORMATION HOI2
WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
AND hoi1.org_information_context = 'Reporting Statuses'
AND hoi1.organization_id = P_BUSINESS_GROUP_ID
AND ass.employment_category = hoi2.org_information1
AND hoi2.organization_id = P_BUSINESS_GROUP_ID
AND hoi2.org_information_context = 'Reporting Categories'
AND hoi1.organization_id = hoi2.organization_id
)
---- Bug# 5577840
AND ass.location_id = pgn.entity_id
AND (pgn.hierarchy_node_id = l_est_node_id
or pgn.parent_hierarchy_node_id = l_est_node_id)
AND pgn.node_type in ('EST','LOC');
select address_line1, address_line2, address_line3,
town_or_city, region_2, postal_code
from per_addresses
where person_id = p_person_id
and primary_flag = 'Y'
and nvl(date_to, sysdate) >= sysdate;