The following lines contain the word 'select', 'insert', 'update' or 'delete':
chars to enable inserts into
pay_input_values_f.
ccarter 03-Jun-99 115.6 Added get_user_status function
ccarter 30-Jun-99 115.7 Change made to get_user_status function
skekkar 23-AUG-99 115.8 Added decode_territory ,
decode_organization and
decode_availability_status functions
hsajja 25-AUG-99 115.9 Added DECODE_POSITION_CURRENT_NAME
function
hsajja 27-AUG-99 115.10 Change made to VERTICAL_APP_ID function
to add 8302(PQH) and 8303(PQP)
darora 27-Sep-99 115.11 Included functions - DECODE_POSITION_LATEST_NAME,
and DECODE_STEP
rraina 29-Sep-99 115.12 985430 Added functions decode_ar_lookup and
hr_lookup_locations
hsajja 01-OCT-99 115.13 Added DECODE_LATEST_POSITION_DEF_ID,
DECODE_AVAIL_STATUS_START_DATE functions
hsajja 04-OCT-99 115.14 Added GET_POSITION_DATE_END function
Changed per_all_positions to
hr_all_positions in decode_position
function
hsajja 07-OCT-99 115.15 Added functions DECODE_PERSON_NAME,
DECODE_GRADE_RULE.
hsajja 12-OCT-99 115.16 Modified DECODE_POSITION_LATEST_NAME,
and DECODE_LATEST_POSITION_DEF_ID
hsajja 16-OCT-99 115.17 Modified DECODE_AVAIL_STATUS_START_DATE
mmillmor 10-Nov-1999 115.18 Added check to init_forms to check that
the business group id matches that of
the security profile.
rraina 10-NOV-1999 115.19 added decode_fnd_comm_lookup for ota views opt
dkerr 10-NOV-99 115.20 Added p_hr_trace_dest parameter to
init_forms
cxsimpso 28-DEC-99 115.21 Added get_validation_name function.
smcmilla 29-DEC-99 115.22 Added product id 453 to
core_hr_app_id function (HRI)
mbocutt 03/01/2000 115.23 1125512 Change get_business_group_id. Replace
usage of fnd_profile.value_wnps to use
fnd_profile.value. This routine writes
the profile value to cache thus aiding
performance.
alogue 14/02/2000 115.24 Utf8 support.
hsajja 21/02/2000 115.25 Included function DECODE_SHARED_TYPE
alogue 01/03/2000 115.26 Support of change to hr_locations.
rvydyana 24/05/2000 115.27 Added new function get_xbg_profile
mbocutt 06/06/2000 115.29 Fixed error in function descriptions
for get_work/home_phone
tclewis 29-feb-2000 115.30 added function maintain_tax_Records.
arashid 13-OCT-00 115.22 Added a cover routine for:
DBMS_DESCRIBE.DESCRIBE_PROCEDURE to
compile an invalid package. The new
routine is called: DESCRIBE_PROCEDURE
pattwood 16-NOV-2000 115.32 Changed DESCRIBE_PROCEDURE so it
only attempts to compile the package
header, instead of the header and
body. Required by hr_api_user_hooks
package changes for deferred
compilation changes.
stlocke 23-JAN-2001 115.33 Added procedure init-fndload.
stlocke 08-FEB-2001 115.34 Procedure init-fndload removed.
cnholmes 28-NOV-2001 115.35 Add iRecruitment to HRSM_OBJECT.
hsajja 17-JAN-2002 115.36 NLS fix: Changed per_shared_types to
per_shared_types_vl in function
DECODE_AVAILABILITY_STATUS
gsayers 05-FEB-2002 115.37 Added check to prevent '01/01/0001'-1
in init_forms.
gperry 08-FEB-2002 115.38 Fixed WWBUG 2110218.
Added userenv('sessionid') to query.
dkerr 13-MAY-2002 115.38 2372279 Added check for null security profile
to get_xbg_profile
sgoyal 07-JUN-2002 115.41 2406408 Local variable defined as varchar2(60) changed
to %type of organization name
sgoyal 07-JUN-2002 115.42 Local variable defined as varchar2(60) changed
to %type of location,position name
hsajja 28-AUG-2002 115.43 Modified DECODE_POSITION_LATEST_NAME,
and DECODE_LATEST_POSITION_DEF_ID
adhunter 29-AUG-2002 115.46 2534838: cursors in get_user_status need to restrict
to current language.
GSCC warning: "exit when" clause in loop
in DECODE_AVAIL_START_DATE not liked, changed to "if"
clause with "exit".
adhunter 07-OCT-2002 115.47 2610865 init_forms: remove "+0" from SELECT
b.legislation_code clause.
dsaxby 04-DEC-2002 115.48 2692195 Nocopy changes.
joward 09-DEC-2002 115.49 MLS enabled grade name
pmfletch 10-DEC-2002 115.50 Pointed decode_position_latest_name to select
from MLS table hr_all_positions_f_tl
joward 23-DEC-2002 115.51 MLS enabled job name
kjagadee 26-JUN-2003 115.52 2519443 Modified the cursor csr_amend_user_status
in function get_user_status, so that the
amends in status types will affect only the
concerned business group
ynegoro 23-JUL-2003 115.53 Added DECODE_GRADE_LADDER
njaladi 28-AUG-2003 115.54 2555987 Modified the size of v_meaning in
procedure DECODE_ASS_STATUS_TYPE from
30 to 80.
njaladi 05-SEP-2003 115.55 2555987 Modified the size of v_meaning in
procedure DECODE_ASS_STATUS_TYPE from
80 to %type.
adudekul 29-JAN-2004 115.56 3355231 Modified cursor csr_get_us_city_names in
function chk_geocodes_installed.
kkoh 31-AUG-2004 115.57 3078158 Modified function CORE_HR_APP_ID to include
application_id range of 800 - 859
Modified function VERTICAL_APP_ID to include
3491930 check on application_id 8403 Oracle Labor Distribution
Modified function HRMS_OBJECT to include
checks on application_short_name AME and PSP
kkoh 01-SEP-2004 115.58 Typo in change history
adhunter 06-OCT-2004 115.59 3902208 hr_lookup_locations is erroring when concatenated
address segs exceed 600 chars. Added substr
to limit returned details.
hsajja 10-DEC-2004 115.60 3663875 Changed function
DECODE_POSITION_LATEST_NAME
hsajja 10-DEC-2004 115.61 3663875 Removed old/previous duplicate function
DECODE_POSITION_LATEST_NAME
========================================================================================
svittal 30-SEP-2005 120.1 Global Name Format from R12
changed decode_person_name
-------------------------------------------------------------------------------
DO NOT ADD ANY FUTHER PROCEDURES / FUNCTIONS TO THIS FILE!
IF REQUIRED PLEASE ADD TO HR_GENRAL2 (hrgenrl2.pkh/pkb)
--------------------------------------------------------------------------------
*/
--
-- Invalid package exception returned from DBMS_DESCRIBE.DESCRIBE_PROCEDURE.
--
invalid_package exception;
g_dummy number (1); -- dummy variable for select statements
select fa.application_short_name
from fnd_application fa
where fa.application_id = p_app_id ;
select 1
from hr_locations
where location_id = p_location_id
and location_use = 'HR'
and nvl (inactive_date, c_end_of_time) >= p_date;
select effective_date
from fnd_sessions
where session_id = userenv('sessionid');
select rule_mode
from pay_legislation_rules
where legislation_code = p_legislation_code
and rule_type = 'DC';
select currency_code
from fnd_currencies CURRENCY,
fnd_sessions SESH
where currency.enabled_flag = 'Y'
and currency.issuing_territory_code = p_legislation_code
and sesh.session_id = userenv ('sessionid')
and sesh.effective_date between nvl(currency.start_date_active,
sesh.effective_date)
and nvl(currency.end_date_active,
sesh.effective_date);
select currency_code
from per_business_groups_perf
where business_group_id = p_business_group_id;
select meaning
from hr_lookups
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code;
select meaning
from fnd_common_lookups
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code
and APPLICATION_ID = 800;
select name
from per_grades_vl
where grade_id = p_grade_id;
select name
from ben_pgm_f pgm
,fnd_sessions s
where pgm_id = p_grade_ladder_pgm_id
and s.effective_date between
pgm.effective_start_date and pgm.effective_end_date
and s.session_id = userenv ('sessionid');
select payroll_name
from pay_all_payrolls_f pay, fnd_sessions f
where payroll_id = p_payroll_id
and f.effective_date between
pay.effective_start_date and pay.effective_end_date
and f.session_id = userenv ('sessionid');
select eev.screen_entry_value
from pay_element_entry_values_f eev,
per_pay_bases ppb,
pay_element_entries_f pe,
fnd_sessions f
where ppb.pay_basis_id +0 = p_pay_basis_id
and pe.assignment_id = p_assignment_id
and eev.input_value_id = ppb.input_value_id
and eev.element_entry_id = pe.element_entry_id
and f.effective_date between
eev.effective_start_date and eev.effective_end_date
and f.effective_date between
pe.effective_start_date and pe.effective_end_date
and f.session_id = userenv ('sessionid');
select name
from per_jobs_vl
where job_id = p_job_id;
select name
from hr_all_positions
where position_id = p_position_id;
select location_code
from hr_locations
where location_id = p_location_id
and location_use = 'HR';
select name
from per_pay_bases
where pay_basis_id = p_pay_basis_id;
select user_status
from per_ass_status_type_amends
where assignment_status_type_id = p_assignment_status_type_id
and business_group_id = p_business_group_id;
select phn.phone_number
,phn.date_from
,phn.date_to
from per_phones phn
where phn.parent_id = p_person_id
and phn.parent_table = 'PER_ALL_PEOPLE_F'
and phn.phone_type = p_phone_type
and c_effective_date between phn.date_from and
nvl(phn.date_to,c_effective_date)
order by phn.date_from DESC -- This is not a mistake of not using
,phn.date_to DESC; -- nvl(date_to, c_effective_date) in the order
select phone_number
from per_phones phn
where phn.parent_id = p_person_id
and phn.parent_table = 'PER_ALL_PEOPLE_F'
and phn.phone_type = 'W1'
and c_effective_date between phn.date_from and
nvl(phn.date_to,c_effective_date);
select phone_number
from per_phones phn,
fnd_sessions f
where phn.parent_id = p_person_id
and phn.parent_table = 'PER_ALL_PEOPLE_F'
and phn.phone_type = 'H1'
and f.effective_date between phn.date_from and
nvl(phn.date_to,f.effective_date)
and f.session_id = userenv ('sessionid');
select telephone_number_1
from per_addresses adr,
fnd_sessions f
where adr.person_id = p_person_id
and adr.primary_flag = 'Y'
and f.effective_date between adr.date_from and
nvl(adr.date_to,f.effective_date)
and f.session_id = userenv ('sessionid');
select group_name
from pay_people_groups
where people_group_id = p_people_group_id;
select business_group_id
from per_security_profiles
where security_profile_id=l_security_profile_id;
select b.legislation_code
, b.short_name
, b.name
, b.currency_code
into p_legislation_code
, p_short_name
, p_bg_name
, p_bg_currency_code
from per_business_groups b
where b.business_group_id = p_business_group_id;
select 'Y' from dual
where exists(select null
from pay_us_city_names
where rownum =1);
select 'X'
from fnd_product_installations
where application_id = p_application_id
and status = 'I';
select tl.user_status
from per_ass_status_type_amends am,
per_ass_status_type_amends_tl tl
where am.assignment_status_type_id = p_assignment_status_type_id
and am.business_group_id = get_business_group_id -- Bug #2519443
and am.ass_status_type_amend_id =
tl.ass_status_type_amend_id
and tl.language=USERENV('LANG');
select tl.user_status
from per_assignment_status_types asg,
per_assignment_status_types_tl tl
where asg.assignment_status_type_id = p_assignment_status_type_id
and asg.assignment_status_type_id =
tl.assignment_status_type_id
and tl.language=USERENV('LANG');
select territory_short_name
from fnd_territories_vl
where territory_code = p_territory_code;
select name
from hr_all_organization_units_tl
where organization_id = p_organization_id
and language = userenv('LANG');
select shared_type_name
from per_shared_types_vl
where shared_type_id = p_availability_status_id;
select name
from hr_positions_x
where position_id = p_position_id;
select pft.name
from hr_all_positions_f_tl pft
where pft.position_id = p_position_id
and pft.language = userenv('LANG');
select psf.name
from hr_all_positions_f psf
where psf.position_id = p_position_id
and p_effective_date between
psf.effective_start_date and psf.effective_end_date;
select effective_date
from fnd_sessions
where session_id = userenv('sessionid');
select psp.spinal_point
from per_spinal_point_steps_f sps, per_spinal_points psp
where sps.step_id = p_step_id
and p_effective_date between sps.effective_start_date and sps.effective_end_date
and sps.spinal_point_id = psp.spinal_point_id;
select meaning
from ar_lookups
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code;
select
substrb(
LOC1.ADDRESS_LINE_1||
decode(LOC1.ADDRESS_LINE_1,null,'',', ')|| LOC1.ADDRESS_LINE_2||
decode(LOC1.ADDRESS_LINE_2,null,'',', ')|| LOC1.ADDRESS_LINE_3||
decode(LOC1.ADDRESS_LINE_3,null,'',', ')||
LOC1.TOWN_OR_CITY||decode(LOC1.TOWN_OR_CITY,null,'',', ')||
LOC1.REGION_1||decode(LOC1.REGION_1,null,'',', ')|| LOC1.REGION_2||
decode(LOC1.REGION_2,null,'',', ')||
LOC1.REGION_3||decode(LOC1.REGION_3,null,'',', ')|| LOC1.POSTAL_CODE||
decode(LOC1.POSTAL_CODE,null,'',', ')|| LOC1.COUNTRY||
decode(LOC1.COUNTRY,null,' ',', ')
,1,600)
from hr_locations LOC1
where location_id = p_location_id
and location_use = 'HR';
select position_definition_id
from hr_all_positions_f
where position_id = p_position_id
and effective_end_date = hr_general.end_of_time;
select psf.availability_status_id, psf.effective_start_date
from hr_all_positions_f psf
where psf.position_id = p_position_id
and psf.effective_start_date < p_effective_date
order by psf.effective_start_date desc;
select psf.effective_start_date - 1
from hr_all_positions_f psf, per_shared_types sht
where psf.position_id = p_position_id
and psf.availability_status_id = sht.shared_type_id
and sht.system_type_cd in ('DELETED','ELIMINATED');
select pr.name
from pay_grade_rules pgr, pay_rates pr
where pgr.rate_id = pr.rate_id
and pgr.grade_rule_id = p_grade_rule_id;
SELECT validation_name
FROM pay_legislative_field_info
WHERE UPPER(target_location) = UPPER(p_target_location)
AND UPPER(field_name) = UPPER(p_field_name)
AND UPPER(legislation_code) = UPPER(p_legislation_code)
AND UPPER(validation_type) = UPPER(p_validation_type);