[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Inserts a band given its minimum value. This function assumes the band */
/* type already has a complete set of bands. If there is already a band with */
/* the given minimum value then no action is taken. Otherwise, the existing */
/* band which the given minumum value falls into is split into two bands. */
/******************************************************************************/
PROCEDURE insert_time_band(p_type IN VARCHAR2,
p_band_min_day_comp IN NUMBER,
p_band_min_week_comp IN NUMBER,
p_band_min_month_comp IN NUMBER,
p_band_min_year_comp IN NUMBER) IS
l_band_min_value NUMBER; -- minimum value of band to insert
SELECT *
FROM hri_time_bands
WHERE type = p_type
AND v_band_min_value > band_min_value
AND (v_band_min_value < band_max_value
OR band_max_value IS NULL);
UPDATE hri_time_bands
SET band_sequence = band_sequence + 1
WHERE band_sequence > l_band_to_split.band_sequence
AND type = p_type;
/* Insert a new band with the new minimum and the existing maximum */
INSERT INTO hri_time_bands
(type
,band_min_value
,band_max_value
,band_sequence
,band_min_day_comp
,band_min_week_comp
,band_min_month_comp
,band_min_year_comp
,band_max_day_comp
,band_max_week_comp
,band_max_month_comp
,band_max_year_comp)
VALUES
(p_type
,l_band_min_value
,l_band_to_split.band_max_value
,l_band_to_split.band_sequence + 1
,NVL(p_band_min_day_comp,0)
,NVL(p_band_min_week_comp,0)
,NVL(p_band_min_month_comp,0)
,NVL(p_band_min_year_comp,0)
,l_band_to_split.band_max_day_comp
,l_band_to_split.band_max_week_comp
,l_band_to_split.band_max_month_comp
,l_band_to_split.band_max_year_comp);
/* Update the band with the existing minimum to end at the new minimum */
UPDATE hri_time_bands
SET band_max_value = l_band_min_value,
band_max_year_comp = p_band_min_year_comp,
band_max_month_comp = p_band_min_month_comp,
band_max_week_comp = p_band_min_week_comp,
band_max_day_comp = p_band_min_day_comp
WHERE band_min_value = l_band_to_split.band_min_value
AND type = p_type;
END insert_time_band;
/* type already has a complete set of bands. If the band to delete is found */
/* then it is removed and the previous band extended to cover the same values */
/* otherwise no action is taken. */
/******************************************************************************/
PROCEDURE remove_time_band(p_type IN VARCHAR2,
p_band_min_day_comp IN NUMBER,
p_band_min_week_comp IN NUMBER,
p_band_min_month_comp IN NUMBER,
p_band_min_year_comp IN NUMBER) IS
l_band_min_value NUMBER; -- minimum value of band to remove
l_band_to_delete hri_time_bands%rowtype; -- band to remove
/* Selects information from band to be deleted */
CURSOR delete_cur IS
SELECT *
FROM hri_time_bands
WHERE type = p_type
AND band_min_day_comp = p_band_min_day_comp
AND band_min_week_comp = p_band_min_week_comp
AND band_min_month_comp = p_band_min_month_comp
AND band_min_year_comp = p_band_min_year_comp;
/* Selects information from band to be extended */
CURSOR grow_cur IS
SELECT *
FROM hri_time_bands
WHERE type = p_type
AND band_max_day_comp = p_band_min_day_comp
AND band_max_week_comp = p_band_min_week_comp
AND band_max_month_comp = p_band_min_month_comp
AND band_max_year_comp = p_band_min_year_comp;
/* The band starting at 0 cannot be deleted as it is the first one */
IF (NVL(p_band_min_day_comp, 0) +
NVL(p_band_min_week_comp, 0) +
NVL(p_band_min_month_comp,0) +
NVL(p_band_min_year_comp, 0) > 0) THEN
/* Get the information about the band to be deleted */
OPEN delete_cur;
FETCH delete_cur INTO l_band_to_delete;
CLOSE delete_cur;
IF (l_band_to_delete.band_min_value = l_band_to_grow.band_max_value) THEN
/* Delete the given band */
DELETE FROM hri_time_bands
WHERE band_min_value = l_band_to_delete.band_min_value
AND type = p_type;
UPDATE hri_time_bands
SET band_sequence = band_sequence - 1
WHERE band_sequence > l_band_to_delete.band_sequence
AND type = p_type;
/* Set the maximum value of the band preceeding the deleted band */
/* to the maximum value of the deleted band */
UPDATE hri_time_bands
SET band_max_value = l_band_to_delete.band_max_value,
band_max_year_comp = l_band_to_delete.band_max_year_comp,
band_max_month_comp = l_band_to_delete.band_max_month_comp,
band_max_week_comp = l_band_to_delete.band_max_week_comp,
band_max_day_comp = l_band_to_delete.band_max_day_comp
WHERE band_max_value = l_band_to_grow.band_max_value
AND type = p_type;
SELECT
band_min_day_comp
,band_min_week_comp
,band_min_month_comp
,band_min_year_comp
FROM hri_time_bands
WHERE type = p_type
AND band_min_value > 0;
/* Inserts a row into the table. If the row already exists then the row is */
/* updated. Called from UPLOAD part of FNDLOAD. */
/******************************************************************************/
PROCEDURE load_time_band_row(p_type IN VARCHAR2,
p_band_min IN NUMBER,
p_band_max IN NUMBER,
p_band_sequence IN NUMBER,
p_band_min_day_comp IN NUMBER,
p_band_min_week_comp IN NUMBER,
p_band_min_month_comp IN NUMBER,
p_band_min_year_comp IN NUMBER,
p_band_max_day_comp IN NUMBER,
p_band_max_week_comp IN NUMBER,
p_band_max_month_comp IN NUMBER,
p_band_max_year_comp IN NUMBER,
p_owner IN VARCHAR2)
IS
l_rows_customized NUMBER; -- How many rows have been customized
l_last_update_date DATE := SYSDATE;
l_last_updated_by NUMBER := 0;
l_last_update_login NUMBER := 0;
/* Selects the number of bands for the given type that have been customized */
CURSOR customized_bands_csr IS
SELECT count(*)
FROM hri_time_bands
WHERE type = p_type
AND fnd_load_util.owner_name(last_updated_by) <> 'ORACLE';
l_last_updated_by := fnd_load_util.owner_id(p_name => p_owner);
/* Delete all overlapping bands */
DELETE FROM hri_time_bands
WHERE type = p_type
AND ((p_band_min <= band_min_value AND
(band_min_value < p_band_max OR p_band_max IS NULL))
OR (band_min_value <= p_band_min AND
(p_band_min < band_max_value OR band_max_value IS NULL))
OR band_sequence = p_band_sequence);
/* Insert seeded band */
INSERT INTO hri_time_bands
( type
, band_min_value
, band_max_value
, band_sequence
, band_min_day_comp
, band_min_week_comp
, band_min_month_comp
, band_min_year_comp
, band_max_day_comp
, band_max_week_comp
, band_max_month_comp
, band_max_year_comp
, last_update_date
, last_update_login
, last_updated_by
, created_by
, creation_date )
VALUES
( p_type
, p_band_min
, p_band_max
, p_band_sequence
, p_band_min_day_comp
, p_band_min_week_comp
, p_band_min_month_comp
, p_band_min_year_comp
, p_band_max_day_comp
, p_band_max_week_comp
, p_band_max_month_comp
, p_band_max_year_comp
, l_last_update_date
, l_last_update_login
, l_last_updated_by
, l_created_by
, l_creation_date );