The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'X'
FROM ghr_pay_plans
WHERE equivalent_pay_plan = 'FW'
AND pay_plan = p_pay_plan;
Default: Insert asterisks in prior pay basis and prior
basic pay.*/
-- UPDATE_DATE UPDATED_BY EFFECTIVE_DATE COMMENTS
-----------------------------------------------------------------------------
-- 18-oct-04 Madhuri from start of edit Terminating the edit.
--
/*
if p_prior_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_prior_basic_pay) > max_per_diem then
hr_utility.set_message(8301, 'GHR_37519_ALL_PROCEDURE_FAIL');
Default: Insert asterisks in prior pay basis and prior
basic pay.*/
-- 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.
-- 29-DEC-04 Madhuri Modified rule of checking basic pay against range. No need to divide by 2087.
--- Modified for bug 4089960
-----------------------------------------------------------------------------------------------------------------
IF ( p_prior_pay_plan <> 'AD' ) THEN -- Sundar Bug 4307246
IF ( p_effective_date < to_date('2004/01/11','yyyy/mm/dd') ) then
max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 18',
p_prior_pay_basis,
'Maximum Basic Pay',
p_prior_effective_date);
Default: Insert asterisks in prior pay basis and prior
basic pay.*/
-- UPDATE_DATE UPDATED_BY EFFECTIVE_DATE COMMENTS
-----------------------------------------------------------------------------
-- 18-oct-04 Madhuri from start of edit Terminating the edit.
--
/* if p_prior_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_prior_basic_pay) > max_basic_pay then
hr_utility.set_message(8301, 'GHR_37520_ALL_PROCEDURE_FAIL');
-- Update/Change Date By Effective Date Comment
-- 13-Jun-06 Raju 01-Jan-03 Terminate the edit
IF p_effective_date < fnd_date.canonical_to_date('2003/01/01') then --Bug# 5073313
if p_prior_pay_plan = 'VM' and
p_prior_grade not in ('11','12','13','14','15','96','97') and
p_prior_grade is not null
then
hr_utility.set_message(8301, 'GHR_37532_ALL_PROCEDURE_FAIL');
-- Update/Change Date By Effective Date Comment
-- Upd 46 13-Jun-06 Raju 01-Jan-2006 New Edit
If p_effective_date >= to_date('2006/01/01', 'yyyy/mm/dd') then
if p_prior_pay_plan in ('GL') and
p_prior_grade not in ('03','04','05','06','07','08','09','10') and
p_prior_grade is not null
then
hr_utility.set_message(8301, 'GHR_37432_ALL_PROCEDURE_FAIL');
/* This selects the counties that make up Boston CMSA */
(
substr(p_prior_duty_stn,1,2) = '25' and
substr(p_prior_duty_stn,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_prior_duty_stn 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_prior_duty_stn <> '250489013'
and
/* part of Worcester County */
p_prior_duty_stn 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_prior_duty_stn 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_prior_duty_stn <> '330236013' and
/* part of Rockingham County */
p_prior_duty_stn 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_prior_duty_stn not in ('330029017','330090017','330100017','330140017','330281017',
'330311017','330342017','330345017','330440017','330443017','330470017')
and
/* Maine */
/* part of York County */
p_prior_duty_stn not in ('230450031','231445031','232450031','234250031','234300031',
'237450031','239800031','239900031','239950031')
and
/* Connecticut */
/* part of Windham County */
p_prior_duty_stn not in ('090231015','090259015','090373015','090500015','090603015',
'090749015')
and
/* Chiacago */
/* Illinois */
(
substr(p_prior_duty_stn,1,2) = '17' and
substr(p_prior_duty_stn,7,3) not in ('031','037','043','063','089','091','093','097',
'111','197')
) and
/* Indiana */
(
substr(p_prior_duty_stn,1,2) = '18' and
substr(p_prior_duty_stn,7,3) not in ('089','027')
) and
/* Wisconsin */
(
substr(p_prior_duty_stn,1,2) = '55' and
substr(p_prior_duty_stn,7,3) <> '059'
) and
/* Los Angeles */
(
(substr(p_prior_duty_stn,1,2) = '06' and
substr(p_prior_duty_stn,7,3) not in ('037','059','065','071','083','111')) and
p_prior_duty_stn <> '061077029'
) and
/* New York */
(
substr(p_prior_duty_stn,1,2) = '36' and
substr(p_prior_duty_stn,7,3) not in ('005','027','047','059','061','071','079','081',
'085','087','103','119')
) and
/* New Jersey */
(
substr(p_prior_duty_stn,1,2) = '34' and
substr(p_prior_duty_stn,7,3) not in ('003','013','017','019','021','023','025','027',
'029','031','035','037','039','041')
) and
/* Connecticut */
(
substr(p_prior_duty_stn,1,2) = '09' and
substr(p_prior_duty_stn,7,3) not in ('001','0009')
) and
/* part of Litchfield County */
p_prior_duty_stn not in ('090051005','090083005','090247005','090629005','090740005',
'090802005','090805005','090450005','090454005','090535005','090817005','090857005')
and
/* part of Middlesex County */
p_prior_duty_stn not in ('090130007','090332007')
and
/* Pennsylvania */
(
substr(p_prior_duty_stn,1,2) = '42' and
substr(p_prior_duty_stn,7,3) <> '103'
) and
/* Philadelphia */
/* Pennsylvania */
(
substr(p_prior_duty_stn,1,2) = '42' and
substr(p_prior_duty_stn,7,3) not in ('017','029','045','091','101')
) and
/* New Jersey */
(
substr(p_prior_duty_stn,1,2) = '34' and
substr(p_prior_duty_stn,7,3) not in ('001','005','007','009','011','015','033')
) and
/* Delaware */
(
substr(p_prior_duty_stn,1,2) = '10' and
substr(p_prior_duty_stn,7,3) <> '015'
) and
/* San Diego */
(
substr(p_prior_duty_stn,1,2) = '06' and
substr(p_prior_duty_stn,7,3) <> '073'
) and
/* San Francisco */
(
substr(p_prior_duty_stn,1,2) = '06' and
substr(p_prior_duty_stn,7,3) not in ('001','013','041','055','075','081','085',
'087','095','097')
) and
/* Washington DC */
/* Maryland */
(
substr(p_prior_duty_stn,1,2) = '24' and
substr(p_prior_duty_stn,7,3) not in ('003','005','009','013','017','021','025',
'027','031','033','035','037','043','510')
) and
/* Virginia */
(
substr(p_prior_duty_stn,1,2) = '51' and
substr(p_prior_duty_stn,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_prior_duty_stn,1,2) = '54' and
substr(p_prior_duty_stn,7,3) not in ('003','037')
)
) then
hr_utility.set_message(8301, 'GHR_37266_ALL_PROCEDURE_FAIL');