The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Add_job_category inserts a row if it does not already exist, but updates */
/* the other lookup row if it exists already and is different. */
/******************************************************************************/
PROCEDURE add_job_category( p_job_cat_set IN NUMBER,
p_job_cat_lookup IN VARCHAR2 := null,
p_other_lookup IN VARCHAR2 := null )
IS
l_other_lookup VARCHAR2(30); -- Holds other lookup if it already exists
/* Selects the other lookup column from a row if it exists */
CURSOR row_exists_cur IS
SELECT other_lookup_code FROM hri_job_category_sets
WHERE job_category_set = p_job_cat_set
AND (member_lookup_code = p_job_cat_lookup
OR member_lookup_code IS NULL and p_job_cat_lookup IS NULL);
/* If row does not exist, insert it */
INSERT INTO hri_job_category_sets
( job_category_set
, member_lookup_code
, other_lookup_code )
VALUES
( p_job_cat_set
, p_job_cat_lookup
, p_other_lookup );
/* If the other row exists, update it if it is different */
UPDATE hri_job_category_sets
SET other_lookup_code = p_other_lookup
WHERE job_category_set = p_job_cat_set
AND member_lookup_code IS NULL;
/* Removes given job category by blanket delete */
/******************************************************************************/
PROCEDURE remove_job_category( p_job_cat_set IN NUMBER,
p_job_cat_lookup IN VARCHAR2 := null,
p_other_lookup IN VARCHAR2 := null )
IS
BEGIN
/* Remove row if it exists */
DELETE FROM hri_job_category_sets
WHERE job_category_set = p_job_cat_set
AND (member_lookup_code = p_job_cat_lookup
OR other_lookup_code = p_other_lookup);
/* Load row simply calls the update procedure */
/******************************************************************************/
PROCEDURE load_row( p_job_cat_set IN NUMBER,
p_job_cat_lookup IN VARCHAR2,
p_other_lookup IN VARCHAR2,
p_owner IN VARCHAR2 )
IS
BEGIN
/* Call to add_job_category includes the update functionality required */
add_job_category(p_job_cat_set, p_job_cat_lookup, p_other_lookup);
SELECT bfm.application_column_name
FROM bis_flex_mappings_v bfm
, bis_dimensions_vl bd
WHERE bfm.dimension_id = bd.dimension_id
AND bd.short_name = 'PRODUCT'
AND bfm.level_short_name = 'PRODUCT GROUP'
AND bfm.application_id = 800;
csr_sql_stmt := 'SELECT pct.value ' ||
'FROM bis_product_categories_v pct, per_jobs job ' ||
'WHERE job.job_id = :1 ' ||
'AND pct.id = job.' || g_prod_cat_segment;
SELECT fff.formula_id
FROM
ff_formulas_f fff
,ff_formula_types fft
WHERE fft.formula_type_name = 'QuickPaint'
AND fff.formula_type_id = fft.formula_type_id
AND fff.business_group_id = p_business_group_id
AND p_date BETWEEN fff.effective_start_date AND fff.effective_end_date
AND fff.formula_name = p_ff_name;
SELECT name
FROM per_business_groups
WHERE business_group_id = p_business_group_id;