[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_service_ff_update DATE; -- Last update date of Fast Formula
SELECT days_to_month
FROM hri_service_bands
WHERE days_to_month IS NOT NULL;
UPDATE hri_service_bands
SET days_to_month = p_days_to_month
WHERE days_to_month IS NOT NULL;
/* This procedure inserts a service band into the hri_service_bands table. */
/* The PK is the minimum year, month, week and day for the service band. */
/* There will always be a row with all of these zero since this cannot be */
/* removed by the remove_service_band API) and there will always be (possibly */
/* the same row) a row with null maximum year, month, week and day values */
/* since inserting a row always works by picking the band that the new */
/* service length falls into, and splitting it into two */
/* */
/* If a service length is given that already exists, nothing will happen. */
/* */
/* E.g. if the following bands exist (Years, Months, Weeks, Days): */
/* (0,0,0,0) - (0,3,0,0) */
/* (0,3,0,0) - (0,6,0,0) */
/* (0,6,0,0) - (0,9,0,0) */
/* (0,9,0,0) - (,,,) */
/* */
/* Then insert_service_band(1,0,0,0) would give the new set of bands as: */
/* (0,0,0,0) - (0,3,0,0) */
/* (0,3,0,0) - (0,6,0,0) */
/* (0,6,0,0) - (0,9,0,0) */
/* (0,9,0,0) - (1,0,0,0) */
/* (1,0,0,0) - (,,,) */
/******************************************************************************/
PROCEDURE insert_service_band( p_service_min_years NUMBER,
p_service_min_months NUMBER,
p_service_min_weeks NUMBER,
p_service_min_days NUMBER)
IS
l_total_min_service_months NUMBER; -- Holds the service length in months
SELECT normalize_band( band_min_total_years
, band_min_total_months
, band_min_total_weeks
, band_min_total_days
, v_days_to_month) band_months
, band_max_total_years
, band_max_total_months
, band_max_total_weeks
, band_max_total_days
FROM hri_service_bands
WHERE (normalize_band( band_max_total_years
, band_max_total_months
, band_max_total_weeks
, band_max_total_days
, v_days_to_month) > v_total_min_service_months
OR ( band_max_total_years IS NULL AND band_max_total_months IS NULL
AND band_max_total_weeks IS NULL AND band_max_total_days IS NULL))
AND normalize_band( band_min_total_years
, band_min_total_months
, band_min_total_weeks
, band_min_total_days
, v_days_to_month) < v_total_min_service_months;
INSERT INTO hri_service_bands
(band_min_total_years,
band_min_total_months,
band_min_total_weeks,
band_min_total_days,
band_max_total_years,
band_max_total_months,
band_max_total_weeks,
band_max_total_days)
VALUES
( p_service_min_years,
p_service_min_months,
p_service_min_weeks,
p_service_min_days,
l_band_to_split_max_years,
l_band_to_split_max_months,
l_band_to_split_max_weeks,
l_band_to_split_max_days );
/* Update the maximum of band to split with the input */
UPDATE hri_service_bands
SET band_max_total_years = p_service_min_years,
band_max_total_months = p_service_min_months,
band_max_total_weeks = p_service_min_weeks,
band_max_total_days = p_service_min_days
WHERE normalize_band( band_min_total_years,
band_min_total_months,
band_min_total_weeks,
band_min_total_days,
l_days_to_month ) = l_band_to_split_min;
END insert_service_band;
/* the removed band is updated with the maximim of the removed band. */
/******************************************************************************/
PROCEDURE remove_service_band( p_service_min_years NUMBER,
p_service_min_months NUMBER,
p_service_min_weeks NUMBER,
p_service_min_days NUMBER)
IS
l_total_min_service_months NUMBER; -- Service length of band to remove
SELECT
band_max_total_years
,band_max_total_months
,band_max_total_weeks
,band_max_total_days
FROM hri_service_bands
WHERE band_min_total_years = p_service_min_years
AND band_min_total_months = p_service_min_months
AND band_min_total_weeks = p_service_min_weeks
AND band_min_total_days = p_service_min_days;
DELETE FROM hri_service_bands
WHERE band_min_total_years = p_service_min_years
AND band_min_total_months = p_service_min_months
AND band_min_total_weeks = p_service_min_weeks
AND band_min_total_days = p_service_min_days;
/* Update the previous band, which can be identified by its maximum */
/* being the minimum of the band removed */
UPDATE hri_service_bands
SET band_max_total_years = l_band_to_grow_max_years,
band_max_total_months = l_band_to_grow_max_months,
band_max_total_weeks = l_band_to_grow_max_weeks,
band_max_total_days = l_band_to_grow_max_days
WHERE band_max_total_years = p_service_min_years
AND band_max_total_months = p_service_min_months
AND band_max_total_weeks = p_service_min_weeks
AND band_max_total_days = p_service_min_days;
/* Inserts row into table, or updates it if the row already exists. Called */
/* from the UPLOAD section of FNDLOAD. */
/******************************************************************************/
PROCEDURE load_row( p_band_min_yrs IN NUMBER,
p_band_min_mths IN NUMBER,
p_band_min_wks IN NUMBER,
p_band_min_days IN NUMBER,
p_band_max_yrs IN NUMBER,
p_band_max_mths IN NUMBER,
p_band_max_wks IN NUMBER,
p_band_max_days IN NUMBER,
p_days_to_month IN NUMBER,
p_owner IN VARCHAR2 )
IS
l_row_exists NUMBER;
l_last_update_date DATE;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
SELECT 1
FROM hri_service_bands
WHERE (band_min_total_years = p_band_min_yrs
AND band_min_total_months = p_band_min_mths
AND band_min_total_weeks = p_band_min_wks
AND band_min_total_days = p_band_min_days)
OR (band_min_total_years IS NULL AND p_band_min_yrs IS NULL
AND band_min_total_months IS NULL AND p_band_min_mths IS NULL
AND band_min_total_weeks IS NULL AND p_band_min_wks IS NULL
AND band_min_total_days IS NULL AND p_band_min_days IS NULL);
l_last_update_date := SYSDATE;
l_last_updated_by := 0;
l_last_update_login := 0;
INSERT INTO hri_service_bands
( band_min_total_years
, band_min_total_months
, band_min_total_weeks
, band_min_total_days
, band_max_total_years
, band_max_total_months
, band_max_total_weeks
, band_max_total_days
, days_to_month
, last_update_date
, last_update_login
, last_updated_by
, created_by
, creation_date )
VALUES
( p_band_min_yrs
, p_band_min_mths
, p_band_min_wks
, p_band_min_days
, p_band_max_yrs
, p_band_max_mths
, p_band_max_wks
, p_band_max_days
, p_days_to_month
, l_last_update_date
, l_last_update_login
, l_last_updated_by
, l_created_by
, l_creation_date );
UPDATE hri_service_bands
SET
band_max_total_years = p_band_max_yrs
,band_max_total_months = p_band_max_mths
,band_max_total_weeks = p_band_max_wks
,band_max_total_days = p_band_max_days
,days_to_month = p_days_to_month
,last_update_date = l_last_update_date
,last_update_login = l_last_update_login
,last_updated_by = l_last_updated_by
WHERE (band_min_total_years = p_band_min_yrs
AND band_min_total_months = p_band_min_mths
AND band_min_total_weeks = p_band_min_wks
AND band_min_total_days = p_band_min_days)
OR (band_min_total_years IS NULL AND p_band_min_yrs IS NULL
AND band_min_total_months IS NULL AND p_band_min_mths IS NULL
AND band_min_total_weeks IS NULL AND p_band_min_wks IS NULL
AND band_min_total_days IS NULL AND p_band_min_days IS NULL);
SELECT
SUM(months_between(least(nvl(actual_termination_date + 1,
p_effective_date + 1),
p_effective_date + 1),
date_start)) total_months
,MAX(date_start) latest_hire_date
FROM per_periods_of_service
WHERE person_id = p_person_id
AND date_start <= p_effective_date;
SELECT bb.*
FROM bis_bucket_customizations bb,
bis_bucket b
WHERE b.short_name = c_bucket
AND b.bucket_id = bb.bucket_id;
SELECT bb.*
FROM bis_bucket_customizations bb,
bis_bucket b
WHERE b.short_name = c_bucket
AND b.bucket_id = bb.bucket_id;
SELECT pow_band_sk_pk
FROM hri_cs_pow_band_ct powb
WHERE powb.wkth_wktyp_sk_fk = decode(p_assignment_type,'E','EMP','CWK')
AND powb.band_sequence = p_band_number;