APPS.HRI_BPL_JOB SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 40

/* 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

Line: 50

/* 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);

Line: 73

  /* 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 );

Line: 83

  /* 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;

Line: 94

/* 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);

Line: 113

/* 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);

Line: 135

  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;

Line: 180

      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;

Line: 284

  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;

Line: 383

  SELECT name
  FROM   per_business_groups
  WHERE  business_group_id = p_business_group_id;