[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
07-DEC-07 vmkulkar 115.16 6644795 Updated get_contact_person_info to capture Title
6648007 using 'CS_PERSON'.
******************************************************************************/
-- Global Variable
g_number NUMBER;
SELECT ppf.first_name ||' '||ppf.middle_names||' '||ppf.last_name,
ppf.email_address,
ppf.first_name,
ppf.middle_names,
ppf.last_name,
ppf.title
FROM per_all_people_f ppf
WHERE ppf.person_id = c_person_id
AND c_date_earned BETWEEN ppf.effective_start_date
AND ppf.effective_end_date;
SELECT phone_number,
phone_type
FROM per_phones
WHERE parent_id = c_person_id
AND c_date BETWEEN date_from
AND nvl(date_to,end_Date)
AND parent_table = 'PER_ALL_PEOPLE_F';
select org_information2
from hr_organization_information hoi
WHERE hoi.organization_id = c_tax_unit_id
and hoi.org_information_context = 'Local Tax Rules'
AND hoi.org_information1 = '39-101-3000';
SELECT ppf.full_name ,
ppf.employee_number
from per_all_assignments_f assign
, per_all_people_f ppf
where ASSIGN.assignment_id = c_assignment_id
and c_effective_date BETWEEN ASSIGN.effective_start_date
AND ASSIGN.effective_end_date
and assign.person_id = ppf.person_id
and assign.assignment_type = 'E'
and c_effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date;
SELECT ppf.full_name ,
ppf.employee_number ,
addr.address_line1 ,
addr.address_line2 ,
addr.address_line3 ,
addr.town_or_city ,
decode(NVL(addr.country,'US'), 'CA', addr.region_1,
'US', addr.region_2, addr.region_1 )
Province_or_state,
addr.postal_code ,
substr(hrt.meaning,-2),
fttl.territory_short_name,
addr.region_1 ,
addr.region_2 ,
'Y' valid_address
from per_all_assignments_f assign
, per_addresses addr
, per_all_people_f ppf
, fnd_territories_tl fttl
, fnd_territories ftt
, hr_lookups hrt
where ASSIGN.assignment_id = c_assignment_id
and c_effective_date BETWEEN ASSIGN.effective_start_date
AND ASSIGN.effective_end_date
and assign.person_id = ppf.person_id
and assign.assignment_type = 'E'
and c_effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
and addr.person_id = ASSIGN.person_id
and addr.primary_flag = 'Y'
and NVL(addr.country,'US') = ftt.territory_code
and ftt.territory_code = fttl.territory_code
and fttl.language = USERENV('LANG')
and hrt.lookup_code = ftt.territory_code
and hrt.lookup_type = 'PQP_US_COUNTRY_TRANSLATE'
and c_effective_date BETWEEN ADDR.date_from
AND nvl(ADDR.date_to,end_date);
SELECT ppf.full_name ,
ppf.employee_number ,
addr.address_line1 ,
addr.address_line2 ,
addr.address_line3 ,
addr.town_or_city ,
decode(NVL(addr.country,'US'), 'CA', addr.region_1,
'US', addr.region_2, addr.region_1 )
Province_or_state,
addr.postal_code ,
substr(hrt.meaning,-2) ,
fttl.territory_short_name,
addr.region_1 ,
addr.region_2 ,
'Y' valid_address
FROM per_all_assignments_f ASSIGN
, per_addresses ADDR
, per_all_people_f PPF
, fnd_territories_tl fttl
, fnd_territories ftt
, hr_lookups hrt
where ASSIGN.assignment_id = c_assignment_id
and ASSIGN.person_id = ppf.person_id
and assign.assignment_type = 'E'
and ADDR.person_id = ASSIGN.person_id
and NVL(addr.country,'US') = ftt.territory_code
and ftt.territory_code = fttl.territory_code
and hrt.lookup_code = ftt.territory_code
and hrt.lookup_type = 'PQP_US_COUNTRY_TRANSLATE'
and fttl.language = USERENV('LANG')
and ADDR.primary_flag = 'Y'
and addr.date_to is NULL
order by addr.address_id desc;
SELECT ppf.full_name ,
ppf.employee_number ,
addr.address_line1 ,
addr.address_line2 ,
addr.address_line3 ,
addr.town_or_city ,
decode(NVL(addr.country,'US'), 'CA', addr.region_1,
'US', addr.region_2, addr.region_1 )
Province_or_state,
addr.postal_code ,
substr(hrt.meaning,-2),
fttl.territory_short_name,
addr.region_1 ,
addr.region_2 ,
'Y' valid_address
FROM per_addresses ADDR
, per_all_people_f PPF
, fnd_territories_tl fttl
, fnd_territories ftt
, hr_lookups hrt
where ppf.person_id = c_person_id
and c_effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
and ADDR.person_id = ppf.person_id
and ADDR.primary_flag = 'Y'
and NVL(addr.country,'US') = ftt.territory_code
and ftt.territory_code = fttl.territory_code
and hrt.lookup_code = ftt.territory_code
and hrt.lookup_type = 'PQP_US_COUNTRY_TRANSLATE'
and fttl.language = USERENV('LANG')
and c_effective_date BETWEEN ADDR.date_from
AND nvl(ADDR.date_to, end_date);
SELECT ppf.full_name ,
ppf.employee_number ,
addr.address_line1 ,
addr.address_line2 ,
addr.address_line3 ,
addr.town_or_city ,
decode(NVL(addr.country,'US'), 'CA', addr.region_1,
'US', addr.region_2, addr.region_1 )
Province_or_state,
addr.postal_code ,
substr(hrt.meaning,-2),
fttl.territory_short_name,
addr.region_1 ,
addr.region_2 ,
'Y' valid_address
FROM per_addresses addr
, per_all_people_f ppf
, fnd_territories_tl fttl
, fnd_territories ftt
, hr_lookups hrt
where ppf.person_id = c_person_id
and addr.person_id = ppf.person_id
and addr.primary_flag = 'Y'
and NVL(addr.country,'US') = ftt.territory_code
and ftt.territory_code = fttl.territory_code
and hrt.lookup_code = ftt.territory_code
and hrt.lookup_type = 'PQP_US_COUNTRY_TRANSLATE'
and fttl.language = USERENV('LANG')
and addr.date_to is NULL
order by addr.address_id desc;
SELECT ppf.full_name ,
ppf.employee_number
from per_all_people_f ppf
where ppf.person_id = c_person_id
and c_effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date;
SELECT hou.name org_name ,
hl.address_line_1,
hl.address_line_2,
hl.address_line_3,
hl.town_or_city,
decode(hl.country, 'CA', hl.region_1,
'US', hl.region_2, hl.region_1) Province_or_state,
hl.postal_code,
nvl(substr(hrl.meaning, -2),hl.country) country_code,
hl.region_1,
hl.region_2,
'Y' valid_address
FROM hr_locations_all hl,
hr_lookups hrl,
hr_all_organization_units hou
WHERE hou.organization_id = c_organization_id
AND hl.location_id = hou.location_id
AND nvl(hl.inactive_date,end_date)>= c_effective_date
AND hrl.lookup_code (+)=hl.country
AND lookup_type (+)='PQP_US_COUNTRY_TRANSLATE';
select legislative_parameters
from pay_payroll_Actions
where payroll_action_id = c_payroll_action_id;