[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NULL
FROM PAY_GRADE_RULES_F GR
WHERE (GR.GRADE_RULE_ID <> P_GRADE_RULE_ID_2
OR P_GRADE_RULE_ID_2 IS NULL)
AND GR.GRADE_OR_SPINAL_POINT_ID = P_GRADE_OR_SPINAL_POINT_ID
AND GR.RATE_TYPE = P_RATE_TYPE
AND GR.RATE_ID = P_RATE_ID
AND GR.business_group_id + 0 = P_BUSINESS_GROUP_ID;
SELECT PAY_GRADE_RULES_S.NEXTVAL
FROM SYS.DUAL;
select r.name,
u.meaning
from hr_lookups u,
pay_rates r
where u.lookup_type = 'UNITS'
and u.lookup_code = r.rate_uom
and r.rate_id = p_rt_id
and r.business_group_id + 0 = p_bgroup_id;
PROCEDURE INSERT_ROW(P_ROWID IN OUT NOCOPY VARCHAR2,
P_GRADE_RULE_ID NUMBER,
P_EFFECTIVE_START_DATE DATE,
P_EFFECTIVE_END_DATE DATE,
P_BUSINESS_GROUP_ID NUMBER,
P_RATE_TYPE VARCHAR2,
P_GRADE_OR_SPINAL_POINT_ID NUMBER,
P_RATE_ID NUMBER,
P_MAXIMUM VARCHAR2,
P_MID_VALUE VARCHAR2,
P_MINIMUM VARCHAR2,
P_SEQUENCE NUMBER,
P_VALUE VARCHAR2,
P_REQUEST_ID NUMBER,
P_PROGRAM_APPLICATION_ID NUMBER,
P_PROGRAM_ID NUMBER,
P_PROGRAM_UPDATE_DATE DATE,
P_CURRENCY_CODE VARCHAR2)
IS
-- Fix for bug 2400465
P_DATE_TO DATE;
SELECT ROWID
FROM PAY_GRADE_RULES_F
WHERE GRADE_RULE_ID = P_GRADE_RULE_ID;
SELECT DATE_TO
FROM PER_GRADES P
WHERE P.GRADE_ID = P_GRADE_OR_SPINAL_POINT_ID;
INSERT INTO PAY_GRADE_RULES_F(GRADE_RULE_ID, EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE, BUSINESS_GROUP_ID,
RATE_ID, GRADE_OR_SPINAL_POINT_ID, RATE_TYPE,
MAXIMUM, MID_VALUE, MINIMUM, SEQUENCE, VALUE,
REQUEST_ID, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE, CURRENCY_CODE)
VALUES (P_GRADE_RULE_ID, P_EFFECTIVE_START_DATE, P_END_DATE,
P_BUSINESS_GROUP_ID, P_RATE_ID, P_GRADE_OR_SPINAL_POINT_ID,
P_RATE_TYPE, P_MAXIMUM, P_MID_VALUE, P_MINIMUM, P_SEQUENCE,
P_VALUE, P_REQUEST_ID, P_PROGRAM_APPLICATION_ID, P_PROGRAM_ID,
P_PROGRAM_UPDATE_DATE, P_CURRENCY_CODE);
END INSERT_ROW;
PROCEDURE UPDATE_ROW( P_ROWID VARCHAR2,
P_GRADE_RULE_ID NUMBER,
P_EFFECTIVE_START_DATE DATE,
P_EFFECTIVE_END_DATE DATE,
P_BUSINESS_GROUP_ID NUMBER,
P_RATE_TYPE VARCHAR2,
P_GRADE_OR_SPINAL_POINT_ID NUMBER,
P_RATE_ID NUMBER,
P_MAXIMUM VARCHAR2,
P_MID_VALUE VARCHAR2,
P_MINIMUM VARCHAR2,
P_SEQUENCE NUMBER,
P_VALUE VARCHAR2,
P_REQUEST_ID NUMBER,
P_PROGRAM_APPLICATION_ID NUMBER,
P_PROGRAM_ID NUMBER,
P_PROGRAM_UPDATE_DATE DATE,
P_CURRENCY_CODE VARCHAR2)
IS
BEGIN
UPDATE PAY_GRADE_RULES_F
SET GRADE_RULE_ID = P_GRADE_RULE_ID,
EFFECTIVE_START_DATE = P_EFFECTIVE_START_DATE, EFFECTIVE_END_DATE = P_EFFECTIVE_END_DATE,
BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID,
RATE_TYPE = P_RATE_TYPE,
GRADE_OR_SPINAL_POINT_ID = P_GRADE_OR_SPINAL_POINT_ID,
RATE_ID = P_RATE_ID,
MAXIMUM = P_MAXIMUM,
MID_VALUE = P_MID_VALUE,
MINIMUM = P_MINIMUM,
SEQUENCE = P_SEQUENCE,
VALUE = P_VALUE,
REQUEST_ID = P_REQUEST_ID,
PROGRAM_APPLICATION_ID = P_PROGRAM_APPLICATION_ID,
PROGRAM_ID = P_PROGRAM_ID,
PROGRAM_UPDATE_DATE = P_PROGRAM_UPDATE_DATE,
CURRENCY_CODE = P_CURRENCY_CODE
WHERE ROWID = chartorowid(P_ROWID);
END UPDATE_ROW;
PROCEDURE DELETE_ROW(P_ROWID VARCHAR2) IS
BEGIN
DELETE FROM PAY_GRADE_RULES_F
WHERE PAY_GRADE_RULES_F.ROWID = chartorowid(P_ROWID);
END DELETE_ROW;
P_PROGRAM_UPDATE_DATE DATE,
P_CURRENCY_CODE VARCHAR2)
IS
CURSOR C IS SELECT * FROM PAY_GRADE_RULES_F
WHERE ROWID = chartorowid(P_ROWID)
FOR UPDATE OF GRADE_RULE_ID NOWAIT;
AND((RECINFO.PROGRAM_UPDATE_DATE = P_PROGRAM_UPDATE_DATE)
OR(RECINFO.PROGRAM_UPDATE_DATE IS NULL AND P_PROGRAM_UPDATE_DATE IS NULL))
AND((RECINFO.CURRENCY_CODE = P_CURRENCY_CODE)
OR(RECINFO.CURRENCY_CODE IS NULL AND P_CURRENCY_CODE IS NULL)))
THEN
RETURN;
select pr.name
into l_name
from pay_rates pr, pay_grade_rules_f g
where pr.rate_id = g.rate_id
and g.grade_or_spinal_point_id = l_spinal_point_id
and p_effective_date between g.effective_start_date and g.effective_end_date
and g.rate_type = 'SP'; -- Fix 3401079
select g.value, r.rate_uom, g.currency_code
into l_value, l_rate_uom, l_currency_code
from pay_grade_rules_f g, pay_rates r
where g.rate_id = r.rate_id
and g.grade_or_spinal_point_id = l_spinal_point_id
and p_effective_date between g.effective_start_date and g.effective_end_date
and g.rate_type = 'SP'; -- Fix 3401079
select r.rate_id, r.business_group_id
into l_rate_id, l_bg_id
from pay_grade_rules_f g, pay_rates r
where r.rate_id = g.rate_id
and GRADE_OR_SPINAL_POINT_ID = l_spinal_point_id
and p_effective_date between g.effective_start_date and g.effective_end_date
and g.rate_type = 'SP'; -- Fix 3401079
select u.meaning
into l_meaning
from hr_lookups u,
pay_rates r
where u.lookup_type = 'UNITS'
and u.lookup_code = r.rate_uom
and r.rate_id = l_rate_id
and r.business_group_id + 0 = l_bg_id;
select 'x'
from per_spinal_points psp,
pay_grade_rules_f pgr,
per_spinal_point_steps_f psps,
per_spinal_point_placements_f pspp
where psp.spinal_point_id = pgr.grade_or_spinal_point_id
and psp.parent_spine_id = pspp.parent_spine_id
and psp.spinal_point_id = psps.spinal_point_id
and psps.step_id = pspp.step_id
and pgr.rate_type = 'SP'
and nvl(p_effective_date, hr_api.g_sot)
between pspp.effective_start_date
and pspp.effective_end_date
and psp.spinal_point_id = p_spinal_point_id
and psp.parent_spine_id = p_parent_spine_id;
select 'x'
from per_spinal_points psp,
pay_grade_rules_f pgr,
per_spinal_point_steps_f psps,
per_spinal_point_placements_f pspp
where psp.spinal_point_id = pgr.grade_or_spinal_point_id
and psp.parent_spine_id = pspp.parent_spine_id
and psp.spinal_point_id = psps.spinal_point_id
and psps.step_id = pspp.step_id
and pgr.rate_type = 'SP'
and psp.spinal_point_id = p_spinal_point_id
and psp.parent_spine_id = p_parent_spine_id;