APPS.PQH_COMMITMENT_PKG SQL Statements

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

Line: 174

   Select bgt.budget_id,budget_name,period_set_name ,budgeted_entity_cd,
          budget_start_date,budget_end_date
    From pqh_budgets bgt
    Where bgt.budget_id in (Select bvr.budget_id
                    From pqh_budget_versions  bvr
                   Where bvr.budget_version_id = p_budget_version_id);

Line: 184

   Select pc.actual_period_type
     from pay_calendars pc
    Where pc.period_set_name = p_period_set_name;

Line: 267

   Select name
     From hr_all_positions_f_tl
    Where position_id = l_position_id
      and language = userenv('LANG');

Line: 273

    Select name
    From hr_all_organization_units -- Bug 2471864
   Where organization_id = l_org_id;

Line: 278

  Select name
  From per_jobs_vl
  Where job_id = l_job_id;

Line: 283

  Select name
  From per_grades_vl
  Where grade_id = l_grade_id;

Line: 292

   Select Position_id
     From pqh_budget_details bdt,pqh_budget_versions bvr
    Where bvr.budget_version_id  = p_budget_version_id
      AND bvr.budget_version_id  = bdt.budget_version_id
      AND bdt.position_id IS NOT NULL
      AND (bdt.position_id = l_position_id or l_position_id IS NULL);

Line: 300

   Select Organization_id
     From pqh_budget_details bdt,pqh_budget_versions bvr
    Where bvr.budget_version_id  = p_budget_version_id
      AND bvr.budget_version_id  = bdt.budget_version_id
      AND bdt.organization_id IS NOT NULL
      AND (bdt.organization_id = l_organization_id or l_organization_id IS NULL);

Line: 308

   Select Job_id
     From pqh_budget_details bdt,pqh_budget_versions bvr
    Where bvr.budget_version_id  = p_budget_version_id
      AND bvr.budget_version_id  = bdt.budget_version_id
      AND bdt.job_id IS NOT NULL
      AND (bdt.job_id = l_job_id or l_job_id IS NULL);

Line: 316

   Select Grade_id
     From pqh_budget_details bdt,pqh_budget_versions bvr
    Where bvr.budget_version_id  = p_budget_version_id
      AND bvr.budget_version_id  = bdt.budget_version_id
      AND bdt.grade_id IS NOT NULL
      AND (bdt.grade_id = l_grade_id or l_grade_id IS NULL);

Line: 650

  SELECT table_route_id
  FROM pqh_table_route
  WHERE table_alias =  p_table_alias;

Line: 687

  select tp.number_per_fiscal_year
  from per_time_period_types tp
  where tp.period_type = p_proc_period_type;

Line: 880

   Select start_date,end_date
     From per_time_periods
    Where period_set_name = p_period_set_name
      AND start_date between p_budget_start_date and p_budget_end_date
       order by start_date;

Line: 1138

       SELECT   PRL.period_type
         FROM	pay_payrolls_f 			PRL
        WHERE	PRL.payroll_id			= p_payroll_id
          AND	p_effective_dt	BETWEEN PRL.effective_start_date AND PRL.effective_end_date;

Line: 1166

    SELECT	PT.number_per_fiscal_year
    FROM	per_time_period_types 	PT
    WHERE	UPPER(PT.period_type) 	= UPPER(p_frequency);

Line: 1294

   * Insert row into fnd_sessions to allow use of global values
   */
   insert into fnd_sessions (session_id, effective_date) values (userenv('sessionid'),trunc(sysdate));

Line: 1534

       Select EE.element_entry_id, EE.creator_type,
              ee.effective_start_date, ee.effective_end_date
         from pay_element_entries_f EE, pay_element_links_f EL
        Where EL.element_type_id = p_element_type_id
          --AND p_commit_calculation_dt between EL.effective_start_date and EL.effective_end_date
          and EL.effective_start_date < p_commit_calculation_end_dt
          and EL.effective_end_date > p_commit_calculation_dt
          AND EL.element_link_id = EE.element_link_id
          AND EE.assignment_id   = p_assignment_id
          --AND p_commit_calculation_dt between EE.effective_start_date and EE.effective_end_date;

Line: 1555

      Select screen_entry_value
        from pay_element_entry_values_f
       Where input_value_id = p_input_value_id
         AND element_entry_id = p_element_entry_id
         --AND p_commit_calculation_dt between effective_start_date and effective_end_date;

Line: 1567

       select name from pay_input_values_f piv
       where piv.input_value_id = p_input_value_id
       --and p_commit_calculation_dt between piv.effective_start_date and piv.effective_start_date;

Line: 1574

      select pay_basis
      from per_pay_bases ppb
      where ppb.pay_basis_id = p_pay_basis_id;

Line: 1784

          Select ppb.pay_basis,ppb.input_value_id
          from   per_pay_bases ppb
          ,      pay_input_values_f piv  --To ensure that this input value id belongs to the passed element_type
          where  ppb.pay_basis_id = p_pay_basis_id
            and  piv.input_value_id = ppb.input_value_id
            and  piv.element_type_id = p_element_type_id
            and piv.effective_start_date <=  p_commit_calculation_end_dt
            and piv.effective_end_date >= p_commit_calculation_dt;

Line: 1799

          Select fnd_number.canonical_to_number(pev.screen_entry_value),
                 pev.effective_start_date, pev.effective_end_date
          from   pay_element_entry_values_f pev
          ,      pay_element_entries_f pee
          where  pee.assignment_id = p_assignment_id
   --         and  p_commit_calculation_dt
   --              between pee.effective_start_date and pee.effective_end_date
            and pee.effective_start_date <=  p_commit_calculation_end_dt
            and pee.effective_end_date >= p_commit_calculation_dt
            and  pev.element_entry_id = pee.element_entry_id
            and  pev.input_value_id = p_input_value_id
            and  pev.effective_start_date  <= p_commit_calculation_end_dt
            and  pev.effective_end_date    >= p_commit_calculation_dt;

Line: 1964

    pqh_process_batch_log.insert_log
    (
        p_message_type_cd    =>  'ERROR',
        p_message_text       =>  l_message_text_out
    );

Line: 1997

       Select Element_type_id,
              Formula_id,Salary_basis_flag,
              Element_input_value_id,
              dflt_elmnt_frequency,nvl(Overhead_percentage,0)
         From pqh_bdgt_cmmtmnt_elmnts
        Where budget_id = p_budget_id
        and actual_commitment_type in ('COMMITMENT','BOTH');

Line: 2009

    select budget_version_id from pqh_budget_versions
       where budget_id = p_budget_id;

Line: 2055

          Delete from  pqh_element_commitments
          where budget_version_id = l_budget_version_id;

Line: 2084

SELECT   1
  FROM  per_assignments_f asg, per_assignment_status_types ast
  WHERE asg.assignment_id = p_assignment_id
  AND   p_commit_calculation_dt between asg.effective_start_date and asg.effective_end_date
  AND   asg.assignment_status_type_id = ast.assignment_status_type_id
  AND   ast.per_system_status <> 'TERM_ASSIGN'
  AND   (ast.per_system_status <> 'SUSP_ASSIGN' OR (ast.per_system_status = 'SUSP_ASSIGN' AND   ast.pay_system_status = 'P') );

Line: 2135

Select 1
From   PQH_BUDGETS BGT
Where  BGT.BUDGET_ID =p_budget_id  And
       BGT.POSITION_CONTROL_FLAG ='Y';

Line: 2150

       Select assignment_id,pay_basis_id,
              business_group_id,payroll_id,
              normal_hours,frequency,
              effective_start_date,
              effective_end_date
         From per_all_assignments_f
        Where position_id = p_position_id
         And  p_commit_calculation_dt <= effective_end_date
         and p_commit_end_dt >= effective_start_date;

Line: 2162

       Select assignment_id,pay_basis_id,
              business_group_id,payroll_id,
              normal_hours,frequency,
              effective_start_date,
              effective_end_date
         From per_all_assignments_f
        Where assignment_id = p_assignment_id
         And  p_commit_calculation_dt <= effective_end_date
         and p_commit_end_dt >= effective_start_date;

Line: 2176

       Select assignment_id,pay_basis_id,
              business_group_id,payroll_id,
              normal_hours,frequency,
              effective_start_date,
              effective_end_date
         From per_all_assignments_f
        Where organization_id = p_organization_id
         And  p_commit_calculation_dt <= effective_end_date
         and p_commit_end_dt >= effective_start_date;

Line: 2189

       Select assignment_id,pay_basis_id,
              business_group_id,payroll_id,
              normal_hours,frequency,
              effective_start_date,
              effective_end_date
         From per_all_assignments_f
        Where job_id  = p_job_id
         And  p_commit_calculation_dt <= effective_end_date
         and p_commit_end_dt >= effective_start_date;

Line: 2203

       Select assignment_id,pay_basis_id,
              business_group_id,payroll_id,
              normal_hours,frequency,
              effective_start_date,
              effective_end_date
         From per_all_assignments_f
        Where grade_id = p_grade_id
         And  p_commit_calculation_dt <= effective_end_date
         and p_commit_end_dt >= effective_start_date;

Line: 2593

                                                        pqh_process_batch_log.insert_log ( p_message_type_cd => 'ERROR', p_message_text => l_message_text_out );

Line: 2662

                                          hr_utility.set_location('Already existing value is updated cnt is:'||element_cnt,40);

Line: 2690

                        hr_utility.set_location('Delete LD encumbered commitments'||to_char(t_entity_assignments(assign_cnt).assignment_id),55);

Line: 2692

                        DELETE
                        FROM    pqh_element_commitments
                        WHERE   budget_version_id = p_budget_version_id
                            AND ASSIGNMENT_ID     = t_entity_assignments(assign_cnt).assignment_id
                            AND (COMMITMENT_START_DATE BETWEEN p_actual_cmmtmnt_start_dt AND p_actual_cmmtmnt_end_dt
                             OR p_actual_cmmtmnt_start_dt BETWEEN COMMITMENT_START_DATE AND COMMITMENT_END_DATE);

Line: 2703

hr_utility.set_location('Consolidated the values now proceeding to insert',48);

Line: 2710

          hr_utility.set_location('Insert commitment'||cnt,48);

Line: 2714

          INSERT
          INTO    pqh_element_commitments
                  (
                          ELEMENT_COMMITMENT_ID ,
                          BUDGET_VERSION_ID,
                          ASSIGNMENT_ID,
                          ELEMENT_TYPE_ID,
                          COMMITMENT_START_DATE,
                          COMMITMENT_END_DATE,
                          COMMITMENT_CALC_FREQUENCY,
                          COMMITMENT_AMOUNT,
                          CREATION_DATE,
                          CREATED_BY
                  )
                  VALUES
                  (
                          pqh_element_commitments_s.nextval ,
                          p_budget_version_id,
                          t_element_commitment(cnt).assignment_id,
                          t_element_commitment(cnt).element_type_id,
                          p_actual_cmmtmnt_start_dt,
                          p_actual_cmmtmnt_end_dt,
                          p_commitment_calc_frequency,
                          t_element_commitment(cnt).commitment,
                          sysdate,
                          -1
                  )
                  ;

Line: 2742

          hr_utility.set_location('Insert commitment2',48);

Line: 2755

        hr_utility.set_location('Exception raised when inserting record into elements table',1);

Line: 2756

        pqh_process_batch_log.insert_log ( p_message_type_cd => 'ERROR', p_message_text => SQLERRM );

Line: 2761

        hr_utility.set_location('Exception raised when inserting record into elements table',2);

Line: 2762

        pqh_process_batch_log.insert_log ( p_message_type_cd => 'ERROR', p_message_text => SQLERRM );

Line: 2818

  delete from pqh_element_commitments where budget_version_id = p_budget_version_id ;

Line: 2866

Select budget_name
From pqh_budgets
Where budget_id = l_bdgt_id;

Line: 3049

     Update pqh_budget_details
     set commitment_gl_status = g_budget_detail_status
     Where budget_version_id = p_budget_version_id
     and position_id = g_budget_entities(pos_cnt).entity_id;

Line: 3056

     Update pqh_budget_details
     set commitment_gl_status = g_budget_detail_status
     Where budget_version_id = p_budget_version_id
     and job_id = g_budget_entities(pos_cnt).entity_id;

Line: 3063

     Update pqh_budget_details
     set commitment_gl_status = g_budget_detail_status
     Where budget_version_id = p_budget_version_id
     and organization_id = g_budget_entities(pos_cnt).entity_id;

Line: 3069

     Update pqh_budget_details
     set commitment_gl_status = g_budget_detail_status
     Where budget_version_id = p_budget_version_id
     and grade_id = g_budget_entities(pos_cnt).entity_id;

Line: 3082

 Update pqh_budget_versions
    set commitment_gl_status = g_budget_version_status
  where budget_version_id = p_budget_version_id;

Line: 3148

SELECT	DECODE(COUNT(session_id), 0, 'N', 'Y')
INTO	v_fnd_sess_row
FROM	fnd_sessions
WHERE	session_id	= userenv('sessionid');

Line: 3154

   insert into fnd_sessions (session_id, effective_date) values(userenv('sessionid'),trunc(sysdate));

Line: 3165

   select 'Y'
   into   l_exists
   from   pay_user_columns PUC
   where  PUC.USER_COLUMN_NAME 		= p_ws_name
   and    NVL(business_group_id, p_bg_id)  = p_bg_id
   and    NVL(legislation_code,'US')       = 'US';

Line: 3179

   select PUC.USER_COLUMN_NAME
   into v_ws_name
   from pay_user_columns PUC
   where PUC.USER_COLUMN_ID = p_ws_name
   and    NVL(business_group_id, p_bg_id)       = p_bg_id
   and    NVL(legislation_code,'US')            = 'US';

Line: 3238

select information3
from per_shared_types
where lookup_type ='FREQUENCY'
and system_type_cd = p_std_freq;

Line: 3381

  SELECT	lookup_code
  INTO		v_pay_basis
  FROM		hr_lookups	 	lkp
  WHERE 	lkp.application_id	= 800
  AND		lkp.lookup_type		= 'PAY_BASIS'
  AND		lkp.lookup_code		= p_freq;

Line: 3557

    SELECT	PT.number_per_fiscal_year
    INTO	v_annualizing_factor
    FROM	per_time_period_types 	PT
    WHERE	UPPER(PT.period_type) 	= UPPER(p_freq);

Line: 3683

Select bdgts.budget_id,budget_name,period_set_name ,
        budget_start_date,budget_end_date
From   PQH_BUDGETS bdgts,per_shared_types shtyps
where p_effective_date between budget_start_date and budget_end_date
and position_control_flag ='Y'
and budgeted_entity_cd ='POSITION'
and shtyps.shared_type_id = bdgts.budget_unit1_id
and shtyps.system_type_cd ='MONEY'
and bdgts.business_group_id = hr_general.get_business_group_id;

Line: 3696

   Select pc.actual_period_type
     from pay_calendars pc
    Where pc.period_set_name = p_period_set_name;

Line: 3703

Select position_id
from per_all_assignments_f
where assignment_id = p_assignment_id
and p_effective_date between effective_start_date and effective_end_date;

Line: 3710

Select budget_version_id
from pqh_budget_versions
where budget_id = p_budget_id
and p_effective_date between date_from and date_to;

Line: 3717

Select period_type
from PAY_PAYROLLS_f
where payroll_id = (Select payroll_id
                    from per_all_assignments_f
                    where assignment_id =   p_assignment_id);

Line: 3724

              Select  Formula_id,
              Salary_basis_flag,
              dflt_elmnt_frequency,
              nvl(Overhead_percentage,0)
         From pqh_bdgt_cmmtmnt_elmnts
        Where budget_id = p_budget_id
        and element_type_id = p_element_type_id
        and element_input_value_id  = p_input_value_id
        and actual_commitment_type in ('COMMITMENT','BOTH');

Line: 3735

       Select pay_basis_id,
              business_group_id,payroll_id,
              normal_hours,frequency
         From per_all_assignments_f
        Where assignment_id = p_assignment_id
         And  p_effective_date between effective_start_date
                                          AND effective_end_date;

Line: 3743

Select ppb.input_value_id
from   per_pay_bases ppb
   ,      pay_input_values_f piv  --To ensure that this input value id belongs to the passed element_type
where  ppb.pay_basis_id = p_pay_basis_id
            and  piv.input_value_id = ppb.input_value_id
            and  piv.element_type_id = p_element_type_id
            and  p_effective_date between piv.effective_start_date and piv.effective_end_date;

Line: 3752

Select budget_name
From pqh_budgets
Where budget_id = l_bdgt_id;

Line: 4021

         hr_utility.set_location('Delete commitment',46);

Line: 4023

         Delete from  pqh_element_commitments
          Where budget_version_id  = l_budget_version_id
            AND ASSIGNMENT_ID      = p_assignment_id
            AND ELEMENT_TYPE_ID    = p_element_type_id
            AND (COMMITMENT_START_DATE  between g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_start_dt
                                           and g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_end_dt OR
                 g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_start_dt between COMMITMENT_START_DATE
                                               and COMMITMENT_END_DATE);

Line: 4033

         hr_utility.set_location('Insert commitment',48);

Line: 4036

         Insert into pqh_element_commitments(
             ELEMENT_COMMITMENT_ID ,
             BUDGET_VERSION_ID,
             ASSIGNMENT_ID,
             ELEMENT_TYPE_ID,
             COMMITMENT_START_DATE,
             COMMITMENT_END_DATE,
             COMMITMENT_CALC_FREQUENCY,
             COMMITMENT_AMOUNT,
             CREATION_DATE,
             CREATED_BY)
           Values(
             pqh_element_commitments_s.nextval ,
             l_budget_version_id,
             p_assignment_id,
             p_element_type_id,
             g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_start_dt,
             g_cmmtmnt_calc_dates(cnt).actual_cmmtmnt_end_dt,
             l_budget_cal_freq,
             l_entry_commitment+nvl(l_element_overhead,0),
             sysdate,
             -1);

Line: 4059

             hr_utility.set_location('Insert commitment2'||(l_entry_commitment+l_element_overhead),48);

Line: 4070

 pqh_process_batch_log.insert_log
 (
   p_message_type_cd    =>  'ERROR',
   p_message_text       =>  SQLERRM
 );