APPS.PAY_SA_UPGRADE SQL Statements

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

Line: 20

      SELECT context_id
      FROM   ff_contexts
      WHERE  context_name = p_context_name;

Line: 56

      SELECT /*+ INDEX(prr PAY_RUN_RESULTS_N50,PAY_RUN_RESULTS_N1) */ et.element_type_id
            ,et.element_name
            ,iv.input_value_id
            ,iv.name input_value_name
            ,rr.run_result_id
            ,aa.assignment_action_id
            ,aa.assignment_id
            ,DECODE(et.element_name, 'GOSI', rr.element_entry_id, rr.source_id) element_entry_id
      FROM   pay_element_types_f    et
            ,pay_input_values_f     iv
            ,pay_run_results        rr
            ,pay_assignment_actions aa
      WHERE  et.element_name         IN ('Employer GOSI Hazards'
                                        ,'GOSI'
                                        ,'Employee GOSI Annuities'
                                        ,'Employee GOSI Arrears'
                                        ,'Employer GOSI Annuities'
                                        ,'Employer GOSI Hazards'
                                        ,'Employer GOSI Subsidy'
                                        ,'GOSI Reference Salary')
        AND  et.legislation_code     = 'SA'
        AND  iv.element_type_id      = et.element_type_id
        AND  iv.name                 = 'Joiner'
        AND  rr.element_type_id      = et.element_type_id
        AND  aa.assignment_action_id = rr.assignment_action_id
        AND  NOT EXISTS (SELECT NULL
                         FROM   pay_run_result_values rrv
                         WHERE  rrv.run_result_id   = rr.run_result_id
                           AND  rrv.input_value_id  = iv.input_value_id)
      ORDER BY aa.assignment_action_id;

Line: 164

        INSERT INTO pay_action_contexts
        (assignment_action_id
        ,assignment_id
        ,context_id
        ,context_value)
        VALUES
        (l_result_rec.assignment_action_id
        ,l_result_rec.assignment_id
        ,l_joiner_context_id
        ,l_joiner);

Line: 175

        INSERT INTO pay_action_contexts
        (assignment_action_id
        ,assignment_id
        ,context_id
        ,context_value)
        VALUES
        (l_result_rec.assignment_action_id
        ,l_result_rec.assignment_id
        ,l_leaver_context_id
        ,l_leaver);

Line: 186

        INSERT INTO pay_action_contexts
        (assignment_action_id
        ,assignment_id
        ,context_id
        ,context_value)
        VALUES
        (l_result_rec.assignment_action_id
        ,l_result_rec.assignment_id
        ,l_nationality_context_id
        ,l_nationality);

Line: 201

      INSERT INTO pay_run_result_values
      (run_result_id
      ,input_value_id
      ,result_value)
      SELECT l_result_rec.run_result_id
            ,iv.input_value_id
            ,DECODE(iv.name, 'Joiner'     , l_joiner
                           , 'Leaver'     , l_leaver
                           , 'Nationality', l_nationality)
      FROM  pay_input_values_f iv
      WHERE iv.element_type_id = l_result_rec.element_type_id
        AND iv.name IN ('Joiner', 'Leaver', 'Nationality');