[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select fam.noa_family_code
from ghr_noa_families nfa,
ghr_families fam
where nfa.nature_of_action_id in ( select nature_of_action_id from
ghr_nature_of_actions where code = p_First_NOAC_Lookup_Code )
and nfa.noa_family_code = fam.noa_family_code
and fam.update_hr_flag = 'Y';
SELECT 'X'
FROM ghr_pay_plans
WHERE equivalent_pay_plan = 'FW'
AND pay_plan = p_to_pay_plan;
Default: Insert asterisks in pay basis and basic pay.*/
-- UPDATE_DATE UPDATED_BY EFFECTIVE_DATE COMMENTS
-----------------------------------------------------------------------------
-- 18-oct-04 Madhuri from start of edit Terminating the edit.
--
/* if p_pay_basis ='PD' and
(
substr(p_agency_subelement,1,2) in ('CU','FD','FL','FY') or
p_agency_subelement in ('TRAJ','TR35')
) and
to_number(p_basic_pay) >max_per_diem
then
hr_utility.set_message(8301, 'GHR_37219_ALL_PROCEDURE_FAIL');
Default: Insert asterisks in pay basis and basic pay.
Basis for Edit: CPDF processing requirement*/
-- UPDATE DATE UPDATE BY BUG NO COMMENTS
-------------------------------------------------------------------------------------------------------------
-- 18-Oct-04 Madhuri Modifying the existing edit as under from 11-JAN-2004.
-- If pay basis is BW, PA, PD, PH, or WC,
-- Then basic pay must be within the range for the pay basis shown in Table 56.
-- Splitting the error message for if and else part also.
-- 19-NOV-04 Madhuri Not splitting message. new message 38918 is being used now.
--- Modified for bug 4089960
--- 22-Apr-2005 Madhuri Modified the edit per Bug # 4307246, as under:
--- If Pay Basis is BW, PA, PD, PH, or WC AND Pay Plan is NOT AD,
--- then Basic Pay must be within the range for the Pay Basis shown on Table 56.
-- UPD 50(Bug 5745356) Raju 01-Oct-2006 Occupation code is added
-- 05-Aug-07 Raju 5132113 Added pay plans GP, GR to edits
--- ----------------------------------------------------------------------------------------------------------
IF ( p_effective_date < to_date('2004/01/11','yyyy/mm/dd') ) then
if p_pay_basis in ('PA', 'PH', 'PM', 'BW', 'WC') then
max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 18',
p_pay_basis,
'Maximum Basic Pay',
p_effective_date);
Default: Insert asterisks in pay basis and basic pay.*/
-- UPDATE_DATE UPDATED_BY EFFECTIVE_DATE COMMENTS
-----------------------------------------------------------------------------
-- 18-oct-04 Madhuri from start of edit Terminating the edit.
--
/*
if p_pay_basis = 'PD' and
(substr(p_agency_subelement,1,2) in ('CU','FD','FL','FY') or
p_agency_subelement in ('TRAJ','TR35')) then
max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 18',
p_pay_basis,
'Maximum Basic Pay',
p_effective_date);
/* This selects the counties that make up Boston CMSA */
(
substr(p_duty_station_lookup_code,1,2) = '25' and
substr(p_duty_station_lookup_code,7,3) not in ('009','017','021','023','025')
) and
/* This selects the parts of other counties that make up Boston CMSA */
/* part of Bristol County */
p_duty_station_lookup_code not in ('250007005','250039005','250096005','250188005','250251005',
'250254005','250281005','250299005','250315005','250385005','250670005','250850005','250911005',
'250912005','250913005','250924005','251064005','251062005','251135005','251219005','251225005',
'251280005')
and
/* part of Hampden County */
p_duty_station_lookup_code <> '250489013'
and
/* part of Worcester County */
p_duty_station_lookup_code not in ('250032027','250055027','250079027','250080027','250098027',
'250110027','250902027','250910027','250916027','250918027','250927027','250944027','250117027',
'250123027','250150027','250185027','250186027','250189027','250220027','250252027','250263027',
'250272027','250280027','250332027','250350027','250390027','250436027','250467027','250480027',
'250510027','250555027','250565027','250585027','250610027','250619027','250640027','250664027',
'250745027','250780027','250785027','250820027','250834027','250900027','250943027','250980027',
'250999027','251450027','251800027','251100027','251172027','251200027','251203027','251204027',
'251210027','251228027','251240027','251260027','251273027','251266027','251271027','251278027',
'251269027','251283027','251310027','251320027','251376027','251380027','251390027','251395027',
'251410027','251439027','251455027','251470027','251500027','251520027')
and
/* New Hampshire */
/* part of Hillsborough County */
p_duty_station_lookup_code not in ('330011011','330018011','330031011','330160011','330180011',
'330234011','330240011','330299011','330310011','330324011','330334011','330340011','330344011',
'330350011','330357011','330401011','330434011','330509011','330540011')
and
/* part of Merrimack County */
p_duty_station_lookup_code <> '330236013' and
/* part of Rockingham County */
p_duty_station_lookup_code not in ('330012015','330013015','330025015','330032015','330045015',
'330087015','330085015','330105015','330108015','330112015','330123015','330130015','330153015',
'330176015','330195015','330200015','330199015','330201015','330252015','330355015','330354015',
'330356015','330370015','330381015','330382015','330391015','330384015','330417015','330430015',
'330435015','330445015','330447015','330448015','330462015','330466015','330474015','330475015',
'330478015','330255015','330305015','330533015','330527015','330551015')
and
/* part of Strafford County */
p_duty_station_lookup_code not in ('330029017','330090017','330100017','330140017','330281017',
'330311017','330342017','330345017','330440017','330443017','330470017')
and
/* Maine */
/* part of York County */
p_duty_station_lookup_code not in ('230450031','231445031','232450031','234250031','234300031',
'237450031','239800031','239900031','239950031')
and
/* Connecticut */
/* part of Windham County */
p_duty_station_lookup_code not in ('090231015','090259015','090373015','090500015','090603015',
'090749015')
and
/* Chiacago */
/* Illinois */
(
substr(p_duty_station_lookup_code,1,2) = '17' and
substr(p_duty_station_lookup_code,7,3) not in ('031','037','043','063','089','091','093','097',
'111','197')
) and
/* Indiana */
(
substr(p_duty_station_lookup_code,1,2) = '18' and
substr(p_duty_station_lookup_code,7,3) not in ('089','027')
) and
/* Wisconsin */
(
substr(p_duty_station_lookup_code,1,2) = '55' and
substr(p_duty_station_lookup_code,7,3) <> '059'
) and
/* Los Angeles */
(
(substr(p_duty_station_lookup_code,1,2) = '06' and
substr(p_duty_station_lookup_code,7,3) not in ('037','059','065','071','083','111')) and
p_duty_station_lookup_code <> '061077029'
) and
/* New York */
(
substr(p_duty_station_lookup_code,1,2) = '36' and
substr(p_duty_station_lookup_code,7,3) not in ('005','027','047','059','061','071','079','081',
'085','087','103','119')
) and
/* New Jersey */
(
substr(p_duty_station_lookup_code,1,2) = '34' and
substr(p_duty_station_lookup_code,7,3) not in ('003','013','017','019','021','023','025','027',
'029','031','035','037','039','041')
) and
/* Connecticut */
(
substr(p_duty_station_lookup_code,1,2) = '09' and
substr(p_duty_station_lookup_code,7,3) not in ('001','0009')
) and
/* part of Litchfield County */
p_duty_station_lookup_code not in ('090051005','090083005','090247005','090629005','090740005',
'090802005','090805005','090450005','090454005','090535005','090817005','090857005')
and
/* part of Middlesex County */
p_duty_station_lookup_code not in ('090130007','090332007')
and
/* Pennsylvania */
(
substr(p_duty_station_lookup_code,1,2) = '42' and
substr(p_duty_station_lookup_code,7,3) <> '103'
) and
/* Philadelphia */
/* Pennsylvania */
(
substr(p_duty_station_lookup_code,1,2) = '42' and
substr(p_duty_station_lookup_code,7,3) not in ('017','029','045','091','101')
) and
/* New Jersey */
(
substr(p_duty_station_lookup_code,1,2) = '34' and
substr(p_duty_station_lookup_code,7,3) not in ('001','005','007','009','011','015','033')
) and
/* Delaware */
(
substr(p_duty_station_lookup_code,1,2) = '10' and
substr(p_duty_station_lookup_code,7,3) <> '015'
) and
/* San Diego */
(
substr(p_duty_station_lookup_code,1,2) = '06' and
substr(p_duty_station_lookup_code,7,3) <> '073'
) and
/* San Francisco */
(
substr(p_duty_station_lookup_code,1,2) = '06' and
substr(p_duty_station_lookup_code,7,3) not in ('001','013','041','055','075','081','085',
'087','095','097')
) and
/* Washington DC */
/* Maryland */
(
substr(p_duty_station_lookup_code,1,2) = '24' and
substr(p_duty_station_lookup_code,7,3) not in ('003','005','009','013','017','021','025',
'027','031','033','035','037','043','510')
) and
/* Virginia */
(
substr(p_duty_station_lookup_code,1,2) = '51' and
substr(p_duty_station_lookup_code,7,3) not in ('013','043','047','059','061','099','107',
'153','177','179','187','510','600','610','630','683','685')
) and
/* West Virginia */
(
substr(p_duty_station_lookup_code,1,2) = '54' and
substr(p_duty_station_lookup_code,7,3) not in ('003','037')
)
) then
hr_utility.set_message(8301, 'GHR_37266_ALL_PROCEDURE_FAIL');