APPS.HRI_OPL_CMNTS SQL Statements

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

Line: 152

  INSERT INTO HRI_MB_CMNTS_CT (
     HRI_MB_CMNTS_CT_ID
    ,EFFECTIVE_START_DATE
    ,EFFECTIVE_END_DATE
    ,ASSIGNMENT_ID
    ,ORGANIZATION_ID
    ,JOB_ID
    ,POSITION_ID
    ,GRADE_ID
    ,ELEMENT_TYPE_ID
    ,INPUT_VALUE_ID
    ,COST_ALLOCATION_KEYFLEX_ID
    ,COMMITMENT_VALUE
    ,CURRENCY_CODE
    --
    -- WHO Columns
    --
    ,last_update_date
    ,last_update_login
    ,last_updated_by
    ,created_by
    ,creation_date)
   SELECT hri_mb_cmnts_ct_s.nextval
         ,pec.commitment_start_date
         ,pec.commitment_end_date
         ,pec.assignment_id
         ,paaf.organization_id
         ,paaf.job_id
         ,paaf.position_id
         ,paaf.grade_id
         ,pec.element_type_id
         ,CASE WHEN nvl(pbce.salary_basis_flag,'N') = 'N' THEN pbce.element_input_value_id
               WHEN nvl(pbce.salary_basis_flag,'N') = 'Y' THEN ppb.input_value_id
           END  input_value_id
         ,NULL cost_allocation_keyflex_id
         ,pec.commitment_amount
         ,petf.output_currency_code
         ,SYSDATE
         ,l_user_id
         ,l_user_id
         ,l_user_id
         ,SYSDATE
     FROM pqh_element_commitments pec,
          pqh_bdgt_cmmtmnt_elmnts pbce,
          per_all_assignments_f   paaf,
          per_pay_bases           ppb,
          pay_element_types_f     petf
    WHERE pec.assignment_id           = paaf.assignment_id
      AND pec.element_type_id         = petf.element_type_id
      AND paaf.pay_basis_id           = ppb.pay_basis_id
      AND pbce.element_type_id        = pec.element_type_id
      AND pbce.budget_id              = (SELECT budget_id FROM pqh_budget_versions WHERE budget_version_id = pec.budget_version_id)
      AND pbce.actual_commitment_type IN ('COMMITMENT','BOTH')
      AND pec.commitment_start_date   BETWEEN petf.effective_start_date AND petf.effective_end_date
      AND pec.commitment_start_date   BETWEEN paaf.effective_start_date AND paaf.effective_end_date
      AND pec.assignment_id           BETWEEN p_start_object_id AND p_end_object_id
      AND pec.commitment_start_date   BETWEEN g_refresh_start_date AND g_refresh_end_date;

Line: 211

  dbg(SQL%ROWCOUNT||' commitment records inserted into HRI_MB_CMNTS_CT');

Line: 280

          'SELECT  /*+ PARALLEL(asgn, DEFAULT, DEFAULT) */
                   DISTINCT
                   asgn.assignment_id object_id
          FROM     per_all_assignments_f   asgn
          WHERE    asgn.assignment_type in (''E'',''C'')
          AND      asgn.effective_end_date >= to_date(''' ||
                       to_char(g_refresh_start_date, 'DD-MM-YYYY') ||
                       ''',''DD-MM-YYYY'') - 1
          ORDER BY asgn.assignment_id';

Line: 397

      dbg('Full Refresh selected - Creating indexes');

Line: 408

        dbg('Full Refresh selected - gathering stats');

Line: 449

  SELECT mthd_range_id,
         min(object_id) start_object_id,
         max(object_id) end_object_id
  FROM   (SELECT  hri_opl_multi_thread.get_next_mthd_range_id(rownum,200) mthd_range_id
                  ,object_id
          FROM    (SELECT DISTINCT asgn.assignment_id object_id
                     FROM per_all_assignments_f   asgn
                    WHERE asgn.assignment_type in ('E','C')
                      AND asgn.effective_end_date >= g_refresh_start_date - 1
                    ORDER BY asgn.assignment_id)
          )
  GROUP BY mthd_range_id;