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_IE_LE_MIGRATE SQL Statements

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

Line: 17


SELECT hou.name,
       hou.date_from,
       hou.date_to,
       hou.internal_external_flag,
       hoi.*,
       decode(hoi.org_information11,'YY','YY'
                                   ,'Y','YN',
                                   'YYY','YYY'
                                   ,'NN') migrated_flag
FROM   hr_organization_units hou,
       hr_organization_information hoi
WHERE hou.organization_id = l_bg_id
AND   hoi.organization_id = hou.organization_id
AND   hoi.org_information_context = 'IE_ORG_INFORMATION'
AND   NVL(hoi.org_information11,'NN') <> 'NN';

Line: 34


SELECT hou.organization_id,hou.name
FROM   hr_organization_units hou
      ,hr_organization_information hoi1
      ,hr_organization_information hoi2
WHERE  hou.organization_id = hoi1.organization_id
AND    hou.organization_id = hoi2.organization_id
AND    hou.business_group_id = l_bg_id
AND    hoi1.org_information_context  = 'CLASS'
AND    hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND    hoi1.org_information2 = 'Y'
AND    hoi2.org_information_context  = 'IE_EMPLOYER_INFO'
AND    hou.name = l_name;

Line: 50


SELECT paa.*,ppa.action_type action_type,ppa.effective_date effective_date,
       ppa.report_type,ppa.report_qualifier
FROM   pay_assignment_actions paa,
       pay_payroll_actions ppa
WHERE  paa.payroll_action_id = ppa.payroll_action_id
AND    paa.action_status IN ('C','S') --10225372
AND    ppa.business_group_id = p_bg_id
AND    ppa.action_type in ('R','Q','P','U','I','B','V','X')
AND    (ppa.action_type <> 'X' OR ppa.report_type <> 'P45')
AND to_char(ppa.effective_date,'YYYY') = 2005
AND paa.tax_unit_id = p_tax_unit_id;

Line: 67


SELECT def_old.defined_balance_id old_id
      ,def_new.defined_balance_id new_id
  FROM pay_balance_types pbt
      ,pay_balance_dimensions dim_old
      ,pay_balance_dimensions dim_new
      ,pay_defined_balances def_old
      ,pay_defined_balances def_new
WHERE pbt.balance_name = p_balance_name
 AND  dim_old.database_item_suffix = '_PER_PAYE_REF_YTD'
 AND  dim_old.legislation_code = 'IE'
 AND  dim_new.legislation_code = 'IE'
 AND  dim_new.database_item_suffix = '_PER_YTD'
 AND  pbt.legislation_code = 'IE'
 AND  def_old.balance_type_id = pbt.balance_type_id
 AND  def_old.balance_dimension_id = dim_old.balance_dimension_id
 AND  def_new.balance_type_id = pbt.balance_type_id
 AND  def_new.balance_dimension_id = dim_new.balance_dimension_id;

Line: 87


SELECT pai.action_information_id,pai.object_version_number,pai.source_id,pai.action_information4
FROM  pay_action_information pai
WHERE pai.action_context_id = p_context_id
  AND pai.action_information1 = p_defbal_id
  AND pai.action_context_type = 'AAP'
  AND pai.action_information_category = 'EMEA BALANCES';

Line: 133


      	  UPDATE pay_assignment_actions paa
      	  SET tax_unit_id = NULL
      	  WHERE paa.assignment_action_id = v_assact.assignment_action_id;

Line: 155


			pay_action_information_api.update_action_information
					   (p_action_information_id => l_act_info_id
					    ,p_object_version_number => l_ovn
					    ,p_action_information1  => g_statutory_balance_table(l_index).new_defined_balance_id
					    ,p_action_information4  => fnd_number.number_to_canonical(l_value)
					   );

Line: 172


        HR_ORGanization_api.update_org_information
          (
            p_validate                => FALSE,
            p_effective_date          => sysdate,
            p_org_information_id      => v_er_bg.org_information_id,
            p_org_info_type_code      => 'IE_ORG_INFORMATION',
            p_org_information2        => v_er_bg.org_information2,
            p_org_information11       => 'Y',
            p_object_version_number   => l_object_version_number
          );

Line: 198


SELECT distinct paa.*,ppa.action_type action_type,ppa.effective_date effective_date,
       ppa.report_type,ppa.report_qualifier,asg.person_id person_id
FROM   pay_assignment_actions paa,
       pay_payroll_actions ppa,
       per_all_assignments_f asg
WHERE  paa.payroll_action_id = ppa.payroll_action_id
AND    paa.action_status IN ('C','S') --10225372
AND    ppa.effective_date between p_start_date and p_end_date
AND    paa.assignment_id = asg.assignment_id
--AND    ppa.effective_date between asg.effective_start_date and asg.effective_end_date
AND    asg.payroll_id = p_payroll_id
AND    ppa.business_group_id = p_bg_id
AND    (
         (p_migrate_archive_data = 'N' and ppa.action_type in ('R','Q','P','U','I','B','V')) or
         (p_migrate_archive_data = 'Y' and ppa.action_type in ('R','Q','P','U','I','B','V','X'))
       )
-- AND    ppa.action_type in ('R','Q','P','U','I','B','V','X')
AND    (ppa.action_type <> 'X' OR ppa.report_type <> 'P45' OR p_p45_migrated = 1)
AND to_char(ppa.effective_date,'YYYY') = 2005
AND paa.tax_unit_id IS NULL
ORDER BY ppa.action_type ASC,
         ppa.report_qualifier,
         ppa.report_type,
         paa.assignment_id,
         paa.assignment_action_id DESC;

Line: 226


SELECT pai.action_information_id,pai.object_version_number,pai.source_id,pai.action_information4
FROM  pay_action_information pai
WHERE pai.action_context_id = p_context_id
  AND pai.action_information1 = p_defbal_id
  AND pai.action_context_type = 'AAP'
  AND pai.action_information_category = 'EMEA BALANCES';

Line: 235


SELECT pai.action_information_id,pai.object_version_number
 FROM  pay_action_information pai
WHERE  pai.action_context_id = p_asg_action_id
  AND  pai.action_information_category = p_act_info_cat;

Line: 242


SELECT act_inf.action_information11
FROM   pay_action_information act_inf
WHERE  act_inf.action_context_id = p_asg_action_id
AND    act_inf.action_information_category = 'EMPLOYEE DETAILS'
AND    act_inf.action_context_type = 'AAP';

Line: 274


          l_stage := 'Update Asg Action' || v_assact.assignment_action_id;

Line: 277


      	  UPDATE pay_assignment_actions paa
      	  SET tax_unit_id = p_org_id
      	  WHERE paa.assignment_action_id = v_assact.assignment_action_id;

Line: 293


          l_stage := 'Update Employee Details' || v_assact.assignment_action_id || ' date '|| l_comm_date;

Line: 295


			pay_action_information_api.update_action_information
					   (p_action_information_id => l_act_info_id
					    ,p_object_version_number => l_object_version_number
					    ,p_action_information30  => l_comm_date
					   );

Line: 307


	              l_stage := 'Update P45 Information' || v_assact.assignment_action_id || ' person '|| v_assact.person_id;

Line: 309


			pay_action_information_api.update_action_information
					   (p_action_information_id => l_act_info_id
					    ,p_object_version_number => l_object_version_number
					    ,p_action_information8  => v_assact.person_id
					   );

Line: 322


             l_stage := 'Update Action Information' || v_assact.assignment_action_id;

Line: 338


			pay_action_information_api.update_action_information
					   (p_action_information_id => l_act_info_id
					    ,p_object_version_number => l_ovn
					    ,p_action_information1  => g_statutory_balance_table(l_index).new_defined_balance_id
					    ,p_action_information4  => fnd_number.number_to_canonical(l_value)
					   );

Line: 368


SELECT hou.name,
       hou.date_from,
       hou.date_to,
       hou.internal_external_flag,
       hoi.*,
       decode(hoi.org_information11,'YY','YY'
                                   ,'Y','YN',
                                   'YYY','YYY'
                                   ,'NN') migrated_flag
FROM   hr_organization_units hou,
       hr_organization_information hoi
WHERE hou.organization_id = l_bg_id
AND   hoi.organization_id = hou.organization_id
AND   hoi.org_information_context = 'IE_ORG_INFORMATION'
AND  (
      ( nvl(hoi.org_information11,'NN') <> 'YY' AND l_p45_migrated = 0) OR
      ( nvl(hoi.org_information11,'NN') <> 'YYY' AND l_p45_migrated = 1)
     );

Line: 389


 SELECT scl.id_flex_num id_flex_num,scl.segment2 segment2,
        scl.segment1 segment1,scl.segment3 segment3,
        pap.*
 FROM   pay_all_payrolls_f pap,
        hr_soft_coding_keyflex scl
 WHERE  pap.business_group_id = p_bg_id
 AND    pap.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
 AND    scl.segment1 = p_tax_ref
 AND    scl.segment3 = p_paye_ref
 AND    scl.segment4 IS NULL;

Line: 402


SELECT hou.organization_id,hou.name
FROM   hr_organization_units hou
      ,hr_organization_information hoi1
      ,hr_organization_information hoi2
WHERE  hou.organization_id = hoi1.organization_id
AND    hou.organization_id = hoi2.organization_id
AND    hou.business_group_id = l_bg_id
AND    hoi1.org_information_context  = 'CLASS'
AND    hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND    hoi1.org_information2 = 'Y'
AND    hoi2.org_information_context  = 'IE_EMPLOYER_INFO'
AND    hou.name = l_name;

Line: 417


SELECT  papf.payroll_id,papf.payroll_name,papf.effective_start_date,papf.effective_end_date
FROM pay_all_payrolls_f papf
    ,hr_soft_coding_keyflex hsck
WHERE papf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
  AND papf.business_group_id = p_bg_id
  AND hsck.segment4 = to_char(p_tax_unit_id);

Line: 429


SELECT def_old.defined_balance_id old_id
      ,def_new.defined_balance_id new_id
  FROM pay_balance_types pbt
      ,pay_balance_dimensions dim_old
      ,pay_balance_dimensions dim_new
      ,pay_defined_balances def_old
      ,pay_defined_balances def_new
WHERE pbt.balance_name = p_balance_name
 AND  dim_old.database_item_suffix = '_PER_YTD'
 AND  dim_old.legislation_code = 'IE'
 AND  dim_new.legislation_code = 'IE'
 AND  dim_new.database_item_suffix = '_PER_PAYE_REF_YTD'
 AND  pbt.legislation_code = 'IE'
 AND  def_old.balance_type_id = pbt.balance_type_id
 AND  def_old.balance_dimension_id = dim_old.balance_dimension_id
 AND  def_new.balance_type_id = pbt.balance_type_id
 AND  def_new.balance_dimension_id = dim_new.balance_dimension_id;

Line: 448


 SELECT 1
 FROM FND_DESCR_FLEX_COLUMN_USAGES
 WHERE DESCRIPTIVE_FLEXFIELD_NAME = 'Action Information DF'
 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = 'IE P45 INFORMATION'
 AND APPLICATION_COLUMN_NAME = 'ACTION_INFORMATION8'
 AND END_USER_COLUMN_NAME = 'Person ID';

Line: 632


	                  l_stage := 'Update Payroll '||v_payroll.payroll_name;

Line: 633


	                    -- Update the payroll
				pay_payroll_api.update_payroll
					(
					  p_validate                 => FALSE,
					  p_effective_date           => v_payroll.effective_start_date,
					  p_datetrack_mode           => 'CORRECTION',
					  p_payroll_id               => l_payroll_id,
					  p_object_version_number    => l_object_version_number,
					  p_soft_coding_keyflex_id   => l_scl_keyflex_id,
					  p_prl_effective_start_date => l_start_date,
					  p_prl_effective_end_date   => l_end_date,
					  p_comment_id               => l_comment_id
					);

Line: 688


      HR_ORGanization_api.update_org_information
        (
          p_validate                => FALSE,
          p_effective_date          => sysdate,
          p_org_information_id      => v_org.org_information_id,
          p_org_info_type_code      => 'IE_ORG_INFORMATION',
          p_org_information2        => v_org.org_information2,
          p_org_information11       => 'YYY',
          p_object_version_number   => l_object_version_number
        );