[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
b.value
FROM pay_user_rows_f a
, pay_user_column_instances_f b
, pay_user_columns c
, pay_user_tables d
WHERE TRUNC(p_effective_date) BETWEEN b.effective_start_date AND b.effective_end_date
AND TRUNC(p_effective_date) BETWEEN a.effective_start_date AND a.effective_end_date
AND a.row_low_range_or_name = p_pay_plan ||'-'|| p_grade_or_level
AND c.user_column_name = p_step_or_rate
AND d.user_table_id = c.user_table_id
AND c.user_column_id = b.user_column_id
AND d.user_table_id = a.user_table_id
AND a.user_row_id = b.user_row_id
AND d.user_table_name = '0000 Oracle Federal Standard Pay Table (AL, ES, EX, GS, GG) No. 0000';
Default: Insert asterisks in pay basis and basic pay. */
if (
p_to_pay_plan = 'FG' and
(p_rate_determinant_code = '0' or
p_rate_determinant_code = '7')
) then
l_table_pay := get_pay_table_value (
p_effective_date => p_effective_date
, p_pay_plan => 'GS'
, p_grade_or_level => p_to_grade_or_level
, p_step_or_rate => p_to_step_or_rate
) ;
Default: Insert asterisks in pay basis and basic pay. */
IF p_to_pay_plan = 'EX' and
p_rate_determinant_code = '0' THEN
max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 5',
p_to_grade_or_level ,
'Maximum Basic Pay',
p_effective_date);
Default: Insert asterisks in pay basis and basic pay. */
-- Bug#4444609 Raju 28-May-2005 Added equal or exceed condition effective from 01-May-2005
-- UPD 43(Bug 4567571) Raju 09-Nov-2005 Delete PRD M effective date from 01-May-2005
IF p_effective_date < to_date('2005/05/01','YYYY/MM/DD') THEN
if ((p_to_grade_or_level is not null and p_to_step_or_rate is not null) and
(p_to_pay_plan = 'GG' or p_to_pay_plan = 'GS') and
(p_retained_pay_plan = 'GG' or p_retained_pay_plan = 'GS') and
p_rate_determinant_code in ('E','F','M') and
p_retained_grade >= p_to_grade_or_level
) then
l_table_pay := get_pay_table_value (
p_effective_date => p_effective_date
, p_pay_plan => p_retained_pay_plan
, p_grade_or_level => p_retained_grade
, p_step_or_rate => p_retained_step
) ;
Default: Insert asterisks in pay basis and basic pay.
if p_effective_date > fnd_date.canonical_to_date('1998/03/01') and
p_to_pay_plan = 'NH' and
p_rate_determinant_code in ('0','5','6','7') then -- added for bug 726125
if p_rate_determinant_code in ('0','7') then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 37',
p_to_grade_or_level ,
'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.*/
if p_to_pay_plan = 'NY' then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 26',
p_to_grade_or_level ,
'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.*/
if p_to_pay_plan = 'NX' then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 27',
p_to_grade_or_level ,
'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.
if p_effective_date > fnd_date.canonical_to_date('1998/03/01') and
p_to_pay_plan = 'NJ' and
p_rate_determinant_code in ('0','5','6','7') then -- added for bug 726125
if p_rate_determinant_code in ('0','7') then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 39',
p_to_grade_or_level ,
'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.
if p_effective_date > fnd_date.canonical_to_date('1998/03/01') and
p_to_pay_plan = 'NK' and
p_rate_determinant_code in ('0','5','6','7') then
if p_rate_determinant_code in ('0','7') then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 41',
p_to_grade_or_level ,
'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.*/
-- Modified EDIT for Student Loan Repayment
-- The to Basic Pay will be NULL for 817 action, so this edit will be fired
-- Checking for NOAC 817 before firing edit
if p_to_pay_plan = 'GS' and
p_rate_determinant_code in ('2','3','4','J','K','R') then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 2',
p_to_grade_or_level || '-' || p_to_step_or_rate,
'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.*/
/*
--
-- Commented the edit for bug 557188 on 7-aug-98
--
if p_rate_determinant_code in ('U','V') and
p_to_pay_plan in ('GG','GH','GS','GM') then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 2',
p_retained_grade || '-' || p_retained_step,
'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.
if p_to_pay_plan = 'GM'
and p_rate_determinant_code in ('0', '7') then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
p_to_grade_or_level || '-' || p_to_step_or_rate,
'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.*/
/*if p_to_pay_plan = 'GM'
and p_rate_determinant_code in ('5', '6', 'M') then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 20',
p_to_grade_or_level,
'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.
-- 17-Aug-00 vravikan 01-jan-2000 Delete retained step
if p_effective_date >= to_date('2000/01/01','yyyy/mm/dd') then
if p_to_pay_plan in ('GH','GM')
and p_rate_determinant_code in ('A','B')
and p_retained_grade >= p_to_grade_or_level
and p_retained_pay_plan in ('GH','GM') then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
p_retained_grade,
'Minimum Basic Pay',
p_effective_date);
Default: Insert asterisks in pay basis and basic pay.*/
if p_to_pay_plan in ('GH', 'GM')
and p_rate_determinant_code in ('2','3','4','J','K','R') then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 2',
p_to_grade_or_level || '-' || p_to_step_or_rate,
'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.*/
--
-- Madhuri 19-MAY-2004 End dated the Edit as of 10-JAN-04
--
IF p_effective_date < to_date('2004/01/11', 'yyyy/mm/dd') then
if p_to_pay_plan in ('FB', 'FJ', 'FX') then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 6',
'01',
'Maximum Basic Pay',
p_effective_date);
Default: Insert asterisks in pay basis and basic pay.*/
-- Update Date Updated By Effective Date Comments
-----------------------------------------------------------------------------------------------------------
-- 18/10/2004 Madhuri From the start of the edit Deleting the edit
-----------------------------------------------------------------------------------------------------------
/*if p_to_pay_plan in ('FL', 'FS', 'FW') then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 11',
'WG' || '-' || p_to_grade_or_level,
'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.*/
-- 17-Aug-00 vravikan 01-jan-2000 Delete step or rate
if p_effective_date >= to_date('2000/01/01','yyyy/mm/dd') then
if p_to_pay_plan = 'FM' then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
p_to_grade_or_level,
'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.
if p_to_pay_plan = 'DR' and
p_rate_determinant_code in ('0','7') then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 28',
p_to_grade_or_level ,
'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.
if p_to_pay_plan = 'DR' and
p_rate_determinant_code in ('5','6') then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 29',
p_to_grade_or_level ,
'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.*/
/* Commenting the edit for the bug 3147737
if p_to_pay_plan = 'GG' and
p_rate_determinant_code in ('5','6','M') then
max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 19',p_to_grade_or_level,
'Maximum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.*/
if p_to_pay_plan = 'GG' and
p_to_grade_or_level between '01' and '15' and
p_rate_determinant_code in ('2','3','4','J','K','R') then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 2',
p_to_grade_or_level || '-' || p_to_step_or_rate ,
'Minimum Basic Pay',
p_effective_date);
Default: Insert asterisks in pay basis and basic pay.*/
if p_to_pay_plan = 'GH'
and p_rate_determinant_code in ('0','7') then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
p_to_grade_or_level || '-' || p_to_step_or_rate,
'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.*/
/*if p_to_pay_plan = 'GH'
and p_rate_determinant_code in ('5', '6', 'M') then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 20',
p_to_grade_or_level,
'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.*/
-- Update Date By Comments
-- 20/2 27-Feb-2003 Madhuri Modified the requirement
-- from Then Basic Pay must equal the entry for the grade on Table 8 or be asterisks.
-- to Then Basic Pay must be within the range for the grade on Table 8 or be asterisks.
if p_to_pay_plan = 'FC' and p_rate_determinant_code = '0' then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 8',
p_to_grade_or_level || '-' || p_to_step_or_rate,
'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.*/
-- Update Date By Comments
-- 20/2 27-Feb-2003 Madhuri Modified the requirement
-- from Then Basic Pay must equal the entry for the grade on Table 8 or be asterisks.
-- to Then Basic Pay must be within the range for the grade on Table 8 or be asterisks.
if p_to_pay_plan = 'FC' and p_rate_determinant_code <> 'C' then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 8',
p_to_grade_or_level || '-' || p_to_step_or_rate,
'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.*/
if p_to_pay_plan = 'GG' and
p_to_grade_or_level = 'SL' and
p_rate_determinant_code = '0' then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 21',
p_to_pay_plan ,'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.
if p_to_pay_plan = 'ND' and
p_rate_determinant_code in ('0','5','6','7') then
if p_rate_determinant_code in ('0','7') then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 30',
p_to_grade_or_level ,'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.
if p_to_pay_plan = 'NG' and
p_rate_determinant_code in ('0','5','6','7') then -- added for bug 726125
if p_rate_determinant_code in ('0','7') then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 32',
p_to_grade_or_level ,'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.
if p_to_pay_plan = 'NT' and
p_rate_determinant_code in ('0','5','6','7') then -- added for bug 726125
if p_rate_determinant_code in ('0','7') then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 34',
p_to_grade_or_level ,'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.*/
if p_to_pay_plan in ('AF','FO','FP') and
p_rate_determinant_code = '0' then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 10',
p_to_grade_or_level || '-' || p_to_step_or_rate,
'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.*/
-- Update Date Updated By Effective Date Comments
-----------------------------------------------------------------------------------------------------------
-- 18/10/2004 Madhuri From the start of the edit Deleting the edit
-----------------------------------------------------------------------------------------------------------
/*if (substr(p_to_pay_plan,1,1) = 'W' or
p_to_pay_plan in ('XE','XF','XG','XH')) and
p_rate_determinant_code not in ('A','B','E','F','U','V') then
max_basic_pay := NULL;
Default: Insert asterisks in pay basis and basic pay.
Adds retained pay plan XE effective 1st of march 1998 */
/*
--
-- Commented the edit for bug 557188 on 7-aug-98
--
if p_effective_date < fnd_date.canonical_to_date('1998/03/01') then
if substr(p_retained_pay_plan,1,1) = 'W' or p_retained_pay_plan in ('XF','XG','XH') then
if substr(p_retained_pay_plan,1,1) = 'W' and
p_retained_pay_plan not in ('WG','WL','WM','WS') then
min_basic_pay := 0; -- no check in table
Default: Insert asterisks in pay basis and basic pay.*/
IF p_effective_date < fnd_date.canonical_to_date('2003/01/01') then --Bug# 5073313
if p_to_pay_plan = 'VM'
and p_rate_determinant_code = '0' then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 12',
p_to_grade_or_level, 'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.*/
-- NAME EFF DATE COMMENTS
-- Madhuri 19-MAY-2004 Deleting the edit by commenting
--
/*if p_to_pay_plan = 'VP'
and p_rate_determinant_code = '0' then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 13',
p_to_grade_or_level, 'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.
if p_to_pay_plan = 'VN'
and p_rate_determinant_code = '0' then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 14',
p_to_grade_or_level, 'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay plan, grade, step or
rate, pay basis, and basic pay.
Basis for Edit: E.O.12496*/
if p_to_pay_plan = 'SR' and
substr(p_agency_subelement,1,2) = 'VA' and
p_rate_determinant_code = '0' then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 16',
p_to_pay_plan , 'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.*/
-------------------------------------------------------------------------------
-- Modified by Date Comments
-------------------------------------------------------------------------------
-- Madhuri 01-MAR-05 Retroactively end dating as of 31-JAN-2002
-------------------------------------------------------------------------------
IF p_effective_date <= fnd_date.canonical_to_date('20'||'02/01/31') THEN
if p_to_pay_plan = 'KA' then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 17',
p_to_pay_plan ,
'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.*/
/*
--
-- Commented the edit for bug 557188 on 7-aug-98
--
if not(p_to_pay_plan in ('GS','GG'))
and p_retained_pay_plan in ('GS','GG')
and p_retained_step between '01' and '10'
and p_rate_determinant_code in ('E','F','M') then
max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 19',
p_retained_grade,
'Maximum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.*/
/*
--
-- Commented the edit for bug 557188 on 7-aug-98
--
if not(p_to_pay_plan in ('GH','GM'))
and p_retained_pay_plan in ('GH','GM')
and p_rate_determinant_code in ('A','B') then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 3',
p_retained_grade ||'-'|| p_retained_step,
'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.*/
-- Update Date By Effective Date Comment
-- 06-Feb-2003 Madhuri Modified condition as
-- p_to_basic_pay <= Max_basic_pay
--
/* Commenting the edit as per the bug 3147737
if p_to_pay_plan = 'GS'
and p_rate_determinant_code in ('5','6','M') then
max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 19',
p_to_grade_or_level,
'Maximum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.*/
/*
--
-- Commented the edit for bug 557188 on 7-aug-98
--
if not(p_to_pay_plan in ('GM','GH'))
and p_retained_pay_plan in ('GM','GH')
and p_rate_determinant_code in ('E','F','M') then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 20',
p_retained_grade,
'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.*/
/* Commenting the edit as per the bug 3147737
if p_to_pay_plan in ('GS','GG')
and p_retained_pay_plan in ('GS','GG')
and p_rate_determinant_code in ('E','F','M')
and p_retained_step between '01' and '10' then
max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 19',
p_retained_grade,
'Maximum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.
if p_to_pay_plan in ('GM','GH')
and p_retained_pay_plan in ('GM','GH')
and p_rate_determinant_code in ('E','F','M') then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 20',
p_retained_grade,
'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.
Basis for Edit: 5 U.S.C. 5372*/
if p_to_pay_plan = 'AL'
and p_rate_determinant_code <> 'C' then
if p_to_grade_or_level in ('01', '02') then
max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 22',
p_to_grade_or_level,
'Maximum Basic Pay',
p_effective_date);
Default: Insert asterisks in pay basis and basic pay.
Basis for Edit: 5 U.S.C. 5372a*/
if p_to_pay_plan = 'CA'
and p_rate_determinant_code <> 'C' then
max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 23',
p_to_grade_or_level,
'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.
Basis for Edit: 5 U.S.C. 5376*/
if p_to_pay_plan in ('ST', 'SL')
and p_rate_determinant_code <> 'C' then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 21',
p_to_pay_plan ,
'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay. */
if p_to_pay_plan = 'IJ' and
p_rate_determinant_code in ('0','7') then
max_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 36',
p_to_step_or_rate ,
'Maximum Basic Pay',
p_effective_date);
Default: Insert asterisks in pay basis and basic pay.*/
if p_to_pay_plan in ('AF','FO','FP')
and p_rate_determinant_code <> 'C' then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 10',
p_to_grade_or_level || '-' || p_to_step_or_rate,
'Minimum Basic Pay',p_effective_date);
Default: Insert asterisks in pay basis and basic pay.
if (substr(p_to_pay_plan,1,1) = 'W' or
p_to_pay_plan in ('XE','XF','XG','XH')) and
p_rate_determinant_code not in ('A','B','E','F','U','V') then
max_basic_pay := NULL;
Default: Insert asterisks in pay plan, grade, step or
rate, pay basis, and basic pay.
Basis for Edit: E.O. 12496*/
if p_to_pay_plan = 'SR' and
substr(p_agency_subelement,1,2) = 'VA' and
p_rate_determinant_code <> 'C' then
min_basic_pay := GHR_CPDF_CHECK.get_basic_pay('CPDF Oracle Federal Table 16',
p_to_pay_plan , 'Minimum Basic Pay',p_effective_date);