eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PAY_GB_ROLLBACK SQL Statements

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

Line: 17


                                            when records are deleted from View
                                            Assignment and View Payroll Process Results forms.
  13-Sep-2012  krreddy     115.2  14553830  Removed the GB_ROLLBACK procedure and
                                            moved it into PAY_GB_ROLLBACK_PKG.
  02-Nov-2012  ssarap      115.3            For NI only aggregation deleting the orphan data
                                            in PAY_GB_FPS_DETAILS table.
  02-Nov-2012  kvinayku    115.4  14843731  Added one more condition to reset pensioner RTI sent flag.
  28-Dec-2012  sampamnd    115.5  14759083  Added procedures for rollback related cahnges for EAS and EAS 13.
  =============================================================================*/
  --
  --
FUNCTION get_version(
    p_assig_id IN NUMBER,
    p_type     IN VARCHAR2,
    p_aei_id OUT nocopy NUMBER)
  RETURN NUMBER
IS
  l_proc CONSTANT VARCHAR2(50):= 'get_version';

Line: 38


    SELECT object_version_number,
      assignment_extra_info_id
    FROM per_assignment_extra_info
    WHERE assignment_id  = p_assig_id
    AND information_type = p_type;

Line: 62


    SELECT paa.assignment_id,
      paa.assignment_action_id,
      pai.action_information6,  --EXPAT
      pai.action_information7,  --EXPAT
      pai.action_information8,  --EXPAT
      pai.action_information9,  --EXPAT
      pai.action_information10, --EXPAT
      pai.action_information12, --PENSIONER
	  pai.action_information26, --PENSIONER
      pai.action_information4,  --STARTER
      pai.action_information5,  --STARTER
      pai.action_information20  -- Number of Periods Covered
    FROM pay_assignment_actions paa,
      pay_action_information pai
    WHERE paa.payroll_action_id         = p_pactid
    AND paa.assignment_action_id        = pai.action_context_id
    AND pai.action_information_category = 'GB_RTI_FPS_ASG_DET1'
    AND pai.action_context_type         = 'AAP';

Line: 82


    SELECT paa.assignment_id,
      paa.assignment_action_id
    FROM pay_assignment_actions paa
    WHERE paa.payroll_action_id         = p_pactid;

Line: 89


    SELECT paa.assignment_id,
      paa.assignment_action_id,
      pai.action_information6,  --EXPAT
      pai.action_information7,  --EXPAT
      pai.action_information8,  --EXPAT
      pai.action_information9,  --EXPAT
      pai.action_information10, --EXPAT
      pai.action_information12, --PENSIONER
	  pai.action_information26, --PENSIONER
      pai.action_information4,  --STARTER
      pai.action_information5,  --STARTER
      pai.action_information20  -- Number of Periods Covered
    FROM pay_assignment_actions paa,
      pay_action_information pai
    WHERE paa.payroll_action_id         = p_pactid
    AND paa.assignment_action_id        = p_asgactid
    AND paa.assignment_action_id        = pai.action_context_id
    AND pai.action_information_category = 'GB_RTI_FPS_ASG_DET1'
    AND pai.action_context_type         = 'AAP';

Line: 111


    SELECT report_type
    FROM pay_payroll_actions
    WHERE payroll_action_id = p_pactid;

Line: 137


      hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_PAY_RTI',
      p_aei_information1 => archive_rec.action_information20);

Line: 144


        hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS',
        p_aei_information19 => 'N');

Line: 152


        hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS', p_aei_information9 => 'N');

Line: 159


        hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS', p_aei_information8 => 'N');

Line: 163


    DELETE
    FROM pay_gb_fps_details
    WHERE FPS_ASG_ACT_ID = archive_rec.ASSIGNMENT_ACTION_ID
    AND ASSIGNMENT_ID    = archive_rec.ASSIGNMENT_ID;

Line: 171


         DELETE
         FROM pay_gb_fps_details
         WHERE FPS_ASG_ACT_ID = del_rec.ASSIGNMENT_ACTION_ID
         AND ASSIGNMENT_ID    = del_rec.ASSIGNMENT_ID;

Line: 183


      hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_PAY_RTI',
      p_aei_information1 => l_asgact_rec.action_information20);

Line: 190


        hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS',
        p_aei_information19 => 'N');

Line: 198


        hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS', p_aei_information9 => 'N');

Line: 205


        hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS', p_aei_information8 => 'N');

Line: 209


    DELETE
    FROM pay_gb_fps_details
    WHERE FPS_ASG_ACT_ID = l_asgact_rec.ASSIGNMENT_ACTION_ID
    AND ASSIGNMENT_ID    = l_asgact_rec.ASSIGNMENT_ID;

Line: 236


  SELECT DISTINCT paa.assignment_id
  FROM pay_assignment_actions paa,
       pay_payroll_actions ppa,
       pay_action_information pai
 WHERE paa.payroll_action_id = ppa.payroll_action_id
   AND ppa.payroll_action_id = p_pactid --1688760
   AND paa.assignment_action_id = pai.action_context_id;

Line: 246


  SELECT DISTINCT paa.assignment_id
  FROM pay_assignment_actions paa,
       pay_payroll_actions ppa,
       pay_action_information pai
 WHERE paa.payroll_action_id = ppa.payroll_action_id
   AND ppa.payroll_action_id = p_pactid --1688760
   AND paa.assignment_action_id = pai.action_context_id
   and paa.assignment_action_id = p_asgactid;

Line: 256


select  		fnd_date.canonical_to_date(substr(legislative_parameters,instr(legislative_parameters,'START=') + 6,10)) effective_date,
						substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref
     from   pay_payroll_actions
     where  payroll_action_id = p_pactid;

Line: 263


select pai.ACTION_INFORMATION10
from pay_assignment_Actions paa, pay_action_information pai
where paa.payroll_action_id = p_pactid and paa.assignment_id = p_asg_id
	and pai.action_context_id = paa.assignment_Action_id
	and pai.ACTION_INFORMATION_CATEGORY = 'GB RTI EMPLOYEE DETAILS';

Line: 272


	select paaf.assignment_id
	from per_all_assignments_f paaf,
    	 pay_payrolls_f pay,
	     hr_soft_coding_keyflex sck
	where paaf.person_id     =  (select distinct person_id from per_all_assignments_f where ASSIGNMENT_ID =p_asg_id)
	and   paaf.assignment_id <> p_asg_id
	and   pay.payroll_id     = paaf.payroll_id
	and   pay.SOFT_CODING_KEYFLEX_ID = sck.SOFT_CODING_KEYFLEX_ID
	and   sck.SEGMENT1 = l_tax_ref
	and   paaf.EFFECTIVE_START_DATE =
				( select max(paaf1.effective_start_date)
					from per_all_assignments_f paaf1
					where paaf1.assignment_id = paaf.assignment_id
					and paaf1.assignment_type       = 'E'
					and paaf1.effective_start_date <= l_effective_date
				)
	and l_effective_date between pay.EFFECTIVE_START_DATE and pay.EFFECTIVE_END_DATE;

Line: 307


        hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS', p_aei_information8 => 'N');

Line: 322


						        hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS', p_aei_information8 => 'N');

Line: 337


        hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS', p_aei_information8 => 'N');

Line: 352


						        hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS', p_aei_information8 => 'N');

Line: 379


  SELECT DISTINCT paa.assignment_id
  FROM pay_assignment_actions paa,
       pay_payroll_actions ppa
 WHERE paa.payroll_action_id = ppa.payroll_action_id
   AND ppa.payroll_action_id = p_pactid; --1688760

Line: 387


  SELECT DISTINCT paa.assignment_id
  FROM pay_assignment_actions paa,
       pay_payroll_actions ppa
 WHERE paa.payroll_action_id = ppa.payroll_action_id
   AND ppa.payroll_action_id = p_pactid
   and paa.assignment_action_id = p_asgactid ;

Line: 396


	select 'Y' from dual where exists(select *
	from   pay_payroll_actions prev,
	pay_assignment_actions paa
    where paa.assignment_id = p_asg_id
	and paa.payroll_action_id = prev.payroll_action_id
	and prev.payroll_action_id <> p_pactid
	and prev.ACTION_STATUS='C'
	and prev.REPORT_TYPE = 'RTI_EAS_REP');

Line: 423


        hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS', p_aei_information8 => 'N');

Line: 437


        hr_assignment_extra_info_api.update_assignment_extra_info (p_validate => false, p_object_version_number => l_ovn, p_assignment_extra_info_id => l_aei_id, p_aei_information_category => 'GB_RTI_ASG_DETAILS', p_aei_information8 => 'N');

Line: 447


deleted from View Payroll Process Results forms.*/
PROCEDURE gb_pay_rollback(p_pactid IN NUMBER)

IS
  l_proc CONSTANT VARCHAR2(50):= 'gb_pay_rollback';

Line: 456


    SELECT report_type
    FROM pay_payroll_actions
    WHERE payroll_action_id = p_pactid;

Line: 495


deleted from View Assignment Process Results forms.*/
PROCEDURE gb_asg_rollback(p_pactid IN NUMBER,
                          p_asgactid IN NUMBER)

IS
  l_proc CONSTANT VARCHAR2(50):= 'gb_asg_rollback';

Line: 505


    SELECT report_type
    FROM pay_payroll_actions
    WHERE payroll_action_id = p_pactid;