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

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

Line: 9


    SELECT org_information10
    FROM   hr_organization_information
    WHERE  organization_id = p_business_group_id
    AND    org_information_context = 'Business Group Information';

Line: 136


/* SELECTS BUSINESS GROUP ID FOR ORGANIZATION SPECIFIED */
CURSOR csr_get_bg_id_org (l_org_id number) IS
select business_group_id
from hr_all_organization_units
where  ORGANIZATION_ID = l_org_id;

Line: 141


/* SELECTS BUSINESS GROUP ID FROM THE PAYROLL SPECIFIED */
CURSOR csr_get_bg_id_pay (l_payroll_id number , l_effective_date date) IS
select business_group_id
from pay_all_payrolls_f
where  payroll_id = l_payroll_id
AND trunc(l_effective_date,'MM') between trunc(effective_start_date,'MM') and effective_end_date;

Line: 147


/* SELECTS PAYROLL NAME FOR HEADER */
CURSOR csr_get_payroll_name(l_pyrl_id number , l_eff_date date) IS
select payroll_name
from pay_all_payrolls_f
where PAYROLL_ID = l_pyrl_id
and l_eff_date between effective_start_date and effective_end_date;

Line: 153


/* SELECTS ORGANIZATION NAME FOR HEADER */
CURSOR csr_get_organization_name (l_org_id number) IS
select name
from hr_all_organization_units
where  ORGANIZATION_ID = l_org_id;

Line: 158


/* SELECTS ORGANIZATIONS COMING UNDER A PAYROLL */
/* Modifyig the cursor for performance issue for Bug 7632337 */
/*CURSOR csr_get_orgs_for_payroll (l_payroll_id number , l_effective_date date) is
SELECT        distinct pai_emp.action_information15 organization
		  ,ppf.payroll_id
FROM         per_time_periods ptp
            ,pay_action_information pai_emp
            ,pay_assignment_actions paa1
            ,pay_action_interlocks lck
            ,pay_payroll_actions ppa1
		,pay_all_payrolls_f ppf
WHERE  ptp.payroll_id = l_payroll_id
AND    ptp.time_period_id = pai_emp.action_information16
AND    pai_emp.action_context_type = 'AAP'
AND    pai_emp.action_information_category = 'EMPLOYEE DETAILS'
AND    lck.locking_action_id = pai_emp.action_context_id
AND    lck.locked_action_id = paa1.assignment_action_id
AND    paa1.payroll_action_id = ppa1.payroll_action_id
AND    ppa1.action_type in ('R','Q')
AND    ppa1.action_status = 'C'
AND    paa1.action_status = 'C'
AND    ptp.end_date = l_effective_date
AND    ppf.payroll_id = ptp.payroll_id
AND    l_effective_date BETWEEN ppf.effective_start_date and ppf.effective_end_date; */

Line: 184


SELECT        distinct pai_emp.action_information15 organization
		        ,ppf.payroll_id
FROM         per_time_periods ptp
            ,pay_action_information pai_emp
            ,pay_assignment_actions paa1
            ,pay_payroll_actions ppa1
		,pay_all_payrolls_f ppf
WHERE  ptp.payroll_id = l_payroll_id
AND    ptp.time_period_id = pai_emp.action_information16
AND    pai_emp.action_context_type = 'AAP'
AND    pai_emp.action_information_category = 'EMPLOYEE DETAILS'
AND    pai_emp.action_context_id = paa1.assignment_action_id
AND    paa1.payroll_action_id = ppa1.payroll_action_id
AND    ppa1.action_type = 'X'
and    ppa1.report_type = 'KW_ARCHIVE'
AND    ppa1.action_status = 'C'
AND    paa1.action_status = 'C'
AND    ptp.end_date = l_effective_date
AND    ppf.payroll_id = ptp.payroll_id
AND    l_effective_date BETWEEN ppf.effective_start_date and ppf.effective_end_date;

Line: 207


SELECT  distinct pai_emp.action_information15 organization
					,ppf.payroll_id
				FROM	 per_time_periods ptp
			            ,pay_action_information pai_emp
			            ,pay_assignment_actions paa1
		      	      ,pay_action_interlocks lck
		            	,pay_payroll_actions ppa1
					,pay_all_payrolls_f ppf
 				WHERE  ptp.time_period_id = pai_emp.action_information16
					AND    pai_emp.action_context_type = 'AAP'
					AND    pai_emp.action_information_category = 'EMPLOYEE DETAILS'
					AND    lck.locking_action_id = pai_emp.action_context_id
					AND    lck.locked_action_id = paa1.assignment_action_id
					AND    paa1.payroll_action_id = ppa1.payroll_action_id
					AND    ppa1.action_type in ('R','Q')
					AND    ppa1.action_status = 'C'
					AND    paa1.action_status = 'C'
					AND    ptp.end_date = l_effective_date
					AND    ppf.payroll_id = ptp.payroll_id
					AND    l_effective_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
					AND 	 pai_emp.action_information2  in (select to_char(pose.organization_id_child)
											   from per_org_structure_elements pose
											   connect by pose.organization_id_parent =
											   prior pose.organization_id_child
											   and pose.org_structure_version_id =
											   to_char (l_org_structure_version_id)
											   start with pose.organization_id_parent =
											   to_char(nvl(l_organization_id,l_parent_id))
											   and pose.org_structure_version_id =
												 to_char(l_org_structure_version_id)
											   union select  to_char(nvl(l_organization_id,l_parent_id))
												   from sys.dual) ; */

Line: 240


SELECT  distinct pai_emp.action_information15 organization
					,ppf.payroll_id
				FROM	 per_time_periods ptp
			            ,pay_action_information pai_emp
			            ,pay_assignment_actions paa1
		            	,pay_payroll_actions ppa1
					,pay_all_payrolls_f ppf
 				WHERE  ptp.time_period_id = pai_emp.action_information16
					AND    pai_emp.action_context_type = 'AAP'
					AND    pai_emp.action_information_category = 'EMPLOYEE DETAILS'
					AND    pai_emp.action_context_id = paa1.assignment_action_id
					AND    paa1.payroll_action_id = ppa1.payroll_action_id
					AND    ppa1.action_type = 'X'
					AND    ppa1.report_type = 'KW_ARCHIVE'
					AND    ppa1.action_status = 'C'
					AND    paa1.action_status = 'C'
					AND    ptp.end_date = l_effective_date
					AND    ppf.payroll_id = ptp.payroll_id
					AND    l_effective_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
					AND 	 pai_emp.action_information2  in (select to_char(pose.organization_id_child)
											   from per_org_structure_elements pose
											   connect by pose.organization_id_parent =
											   prior pose.organization_id_child
											   and pose.org_structure_version_id =
											   to_char (l_org_structure_version_id)
											   start with pose.organization_id_parent =
											   to_char(nvl(l_organization_id,l_parent_id))
											   and pose.org_structure_version_id =
												 to_char(l_org_structure_version_id)
											   union select  to_char(nvl(l_organization_id,l_parent_id))
												   from sys.dual) ;

Line: 273


SELECT  distinct pai_emp.action_information15 organization
					,ppf.payroll_id
				FROM	 per_time_periods ptp
			            ,pay_action_information pai_emp
			            ,pay_assignment_actions paa1
		      	      ,pay_action_interlocks lck
		            	,pay_payroll_actions ppa1
					,pay_all_payrolls_f ppf
 				WHERE  ptp.time_period_id = pai_emp.action_information16
					AND    pai_emp.action_context_type = 'AAP'
					AND    pai_emp.action_information_category = 'EMPLOYEE DETAILS'
					AND    lck.locking_action_id = pai_emp.action_context_id
					AND    lck.locked_action_id = paa1.assignment_action_id
					AND    paa1.payroll_action_id = ppa1.payroll_action_id
					AND    ppa1.action_type in ('R','Q')
					AND    ppa1.action_status = 'C'
					AND    paa1.action_status = 'C'
					AND    ptp.end_date = l_effective_date
					AND    ppf.payroll_id = ptp.payroll_id
					AND    l_effective_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
					AND 	 pai_emp.action_information2 = l_organization_id; */

Line: 295


SELECT  distinct pai_emp.action_information15 organization
					,ppf.payroll_id
				FROM	 per_time_periods ptp
			            ,pay_action_information pai_emp
			            ,pay_assignment_actions paa1
		            	,pay_payroll_actions ppa1
					,pay_all_payrolls_f ppf
 				WHERE  ptp.time_period_id = pai_emp.action_information16
					AND    pai_emp.action_context_type = 'AAP'
					AND    pai_emp.action_information_category = 'EMPLOYEE DETAILS'
                                        AND    pai_emp.action_context_id = paa1.assignment_action_id
					AND    paa1.payroll_action_id = ppa1.payroll_action_id
					AND    ppa1.action_type = 'X'
					AND    ppa1.report_type = 'KW_ARCHIVE'
					AND    ppa1.action_status = 'C'
					AND    paa1.action_status = 'C'
					AND    ptp.end_date = l_effective_date
					AND    ppf.payroll_id = ptp.payroll_id
					AND    l_effective_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
					AND 	 pai_emp.action_information2 = l_organization_id;

Line: 318


SELECT  distinct pai_emp.action_information15 organization
					,ppf.payroll_id
				FROM	 per_time_periods ptp
			            ,pay_action_information pai_emp
			            ,pay_assignment_actions paa1
		      	      ,pay_action_interlocks lck
		            	,pay_payroll_actions ppa1
					,pay_all_payrolls_f ppf
 				WHERE  ptp.time_period_id = pai_emp.action_information16
					AND    pai_emp.action_context_type = 'AAP'
					AND    pai_emp.action_information_category = 'EMPLOYEE DETAILS'
					AND    lck.locking_action_id = pai_emp.action_context_id
					AND    lck.locked_action_id = paa1.assignment_action_id
					AND    paa1.payroll_action_id = ppa1.payroll_action_id
					AND    ppa1.action_type in ('R','Q')
					AND    ppa1.action_status = 'C'
					AND    paa1.action_status = 'C'
					AND    ptp.end_date = l_effective_date
					AND    ppf.payroll_id = ptp.payroll_id
					AND    l_effective_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
					AND	 1 = 2; */

Line: 340


SELECT  '123' organization
					,'213' payroll_id
				FROM	 dual
				where  1 = 2;

Line: 347


SELECT  distinct ppf.payroll_id
				FROM	 per_time_periods ptp
			            ,pay_action_information pai_emp
			            ,pay_assignment_actions paa1
		      	      ,pay_action_interlocks lck
		            	,pay_payroll_actions ppa1
					,pay_all_payrolls_f ppf
 				WHERE  ptp.time_period_id = pai_emp.action_information16
					AND    pai_emp.action_context_type = 'AAP'
					AND    pai_emp.action_information_category = 'EMPLOYEE DETAILS'
					AND    lck.locking_action_id = pai_emp.action_context_id
					AND    lck.locked_action_id = paa1.assignment_action_id
					AND    paa1.payroll_action_id = ppa1.payroll_action_id
					AND    ppa1.action_type in ('R','Q')
					AND    ppa1.action_status = 'C'
					AND    paa1.action_status = 'C'
					AND    ptp.end_date = l_effective_date
					AND    ppf.payroll_id = ptp.payroll_id
					AND    l_effective_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
					AND 	 pai_emp.action_information2  in (select to_char(pose.organization_id_child)
											   from per_org_structure_elements pose
											   connect by pose.organization_id_parent =
											   prior pose.organization_id_child
											   and pose.org_structure_version_id =
											   to_char (l_org_structure_version_id)
											   start with pose.organization_id_parent =
											   to_char(nvl(l_organization_id,l_parent_id))
											   and pose.org_structure_version_id =
												 to_char(l_org_structure_version_id)
											   union select  to_char(nvl(l_organization_id,l_parent_id))
												   from sys.dual) ; */

Line: 379


     SELECT  distinct ppf.payroll_id
				FROM	 per_time_periods ptp
			            ,pay_action_information pai_emp
			            ,pay_assignment_actions paa1
		            	,pay_payroll_actions ppa1
					,pay_all_payrolls_f ppf
 				WHERE  ptp.time_period_id = pai_emp.action_information16
					AND    pai_emp.action_context_type = 'AAP'
					AND    pai_emp.action_information_category = 'EMPLOYEE DETAILS'
					AND    pai_emp.action_context_id = paa1.assignment_action_id
					AND    paa1.payroll_action_id = ppa1.payroll_action_id
					AND    ppa1.action_type = 'X'
					AND    ppa1.report_type = 'KW_ARCHIVE'
					AND    ppa1.action_status = 'C'
					AND    paa1.action_status = 'C'
					AND    ptp.end_date = l_effective_date
					AND    ppf.payroll_id = ptp.payroll_id
					AND    l_effective_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
					AND 	 pai_emp.action_information2  in (select to_char(pose.organization_id_child)
											   from per_org_structure_elements pose
											   connect by pose.organization_id_parent =
											   prior pose.organization_id_child
											   and pose.org_structure_version_id =
											   to_char (l_org_structure_version_id)
											   start with pose.organization_id_parent =
											   to_char(nvl(l_organization_id,l_parent_id))
											   and pose.org_structure_version_id =
												 to_char(l_org_structure_version_id)
											   union select  to_char(nvl(l_organization_id,l_parent_id))
												   from sys.dual) ;

Line: 411


SELECT  distinct ppf.payroll_id
				FROM	 per_time_periods ptp
			            ,pay_action_information pai_emp
  			            ,pay_assignment_actions paa1
		      	      ,pay_action_interlocks lck
		            	,pay_payroll_actions ppa1
					,pay_all_payrolls_f ppf
 				WHERE  ptp.time_period_id = pai_emp.action_information16
					AND    pai_emp.action_context_type = 'AAP'
					AND    pai_emp.action_information_category = 'EMPLOYEE DETAILS'
					AND    lck.locking_action_id = pai_emp.action_context_id
					AND    lck.locked_action_id = paa1.assignment_action_id
					AND    paa1.payroll_action_id = ppa1.payroll_action_id
					AND    ppa1.action_type in ('R','Q')
					AND    ppa1.action_status = 'C'
					AND    paa1.action_status IN ('C','S')
					AND    ptp.end_date = l_effective_date
					AND    ppf.payroll_id = ptp.payroll_id
					AND    l_effective_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
					AND 	 pai_emp.action_information2 = l_organization_id;

Line: 433


select to_char(pose.organization_id_child)
from per_org_structure_elements pose
connect by pose.organization_id_parent =
prior pose.organization_id_child
and pose.org_structure_version_id = to_char (l_org_structure_version_id)
start with pose.organization_id_parent = to_char(nvl(l_organization_id,l_parent_id))
and pose.org_structure_version_id = to_char(l_org_structure_version_id)
union select to_char(nvl(l_organization_id,l_parent_id)) from sys.dual;

Line: 441


/* SELECTS DATA FOR A GIVEN ORGANIZATION AND A PAYROLL  COMBINATION */
/* Modifyig the cursor for performance issue for Bug 7632337 */
/*CURSOR csr_get_details (l_payroll_id number , l_effective_date date,  l_org_name varchar2,p_order_1 varchar2,p_order_2 varchar2,p_order_3 varchar2) Is
SELECT       distinct pai_emp.action_context_id arch_assact
             ,to_char(ptp.payroll_id) org_pay
             ,pai_emp.action_information1 full_name
             ,pai_emp.action_information10
             ,pai_emp.action_information15 organization
             ,pai_emp.action_information19 position
             ,pai_emp.action_information5 cost_center
             ,pai_emp.action_information9 nationality
             ,pai_emp.action_information17 job
             ,pai_emp1.action_information9 title
             ,pai_emp1.action_information10
             ,pai_emp1.action_information11
             ,ppf.payroll_name
             ,nvl(pai_emp1.action_information13,0) ytd_earning
             ,nvl(pai_emp1.action_information4,0) ytd_deduction
FROM        per_time_periods ptp
            ,pay_action_information pai_emp
            ,pay_action_information pai_emp1
            ,pay_assignment_actions paa1
            ,pay_action_interlocks lck
            ,pay_payroll_actions ppa1
            ,pay_all_payrolls_f ppf
WHERE  ptp.payroll_id = l_payroll_id
AND    ptp.time_period_id = pai_emp.action_information16
AND    pai_emp.action_context_id = pai_emp1.action_context_id
AND    pai_emp.action_context_type = 'AAP'
AND    pai_emp.action_information_category = 'EMPLOYEE DETAILS'
AND    pai_emp.action_information15 = l_org_name
AND    pai_emp1.action_context_type = 'AAP'
AND    pai_emp1.action_information_category(+) = 'ADDL EMPLOYEE DETAILS'
AND    lck.locking_action_id = pai_emp.action_context_id
AND    lck.locked_action_id = paa1.assignment_action_id
AND    paa1.payroll_action_id = ppa1.payroll_action_id
AND    ppa1.action_type in ('R','Q')
AND    ppa1.action_status = 'C'
AND    paa1.action_status = 'C'
AND    ptp.end_date = l_effective_date
AND    ppf.payroll_id = ptp.payroll_id
AND    l_effective_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
ORDER BY decode(p_order_1,'first_name',pai_emp1.action_information10,'employee_number',pai_emp.action_information10,'family_name',pai_emp1.action_information11),
	 decode(p_order_2,'first_name',pai_emp1.action_information10,'employee_number',pai_emp.action_information10,'family_name',pai_emp1.action_information11,null,1),
	 decode(p_order_3,'first_name',pai_emp1.action_information10,'employee_number',pai_emp.action_information10,'family_name',pai_emp1.action_information11,null,1); */

Line: 488


SELECT       distinct pai_emp.action_context_id arch_assact
             ,to_char(ptp.payroll_id) org_pay
             ,pai_emp.action_information1 full_name
             ,pai_emp.action_information10
             ,pai_emp.action_information15 organization
             ,pai_emp.action_information19 position
             ,pai_emp1.action_information7 cost_center
             ,pai_emp.action_information9 nationality
             ,pai_emp.action_information17 job
             ,pai_emp1.action_information9 title
             ,pai_emp1.action_information6
             ,pai_emp1.action_information11
             ,ppf.payroll_name
             ,nvl(pai_emp1.action_information13,0) ytd_earning
             ,nvl(pai_emp1.action_information4,0) ytd_deduction
FROM         per_time_periods ptp
            ,pay_action_information pai_emp
            ,pay_action_information pai_emp1
            ,pay_assignment_actions paa1
            ,pay_payroll_actions ppa1
            ,pay_all_payrolls_f ppf
WHERE  ptp.payroll_id = l_payroll_id
AND    ptp.time_period_id = pai_emp.action_information16
AND    pai_emp.action_context_id = pai_emp1.action_context_id
AND    pai_emp.action_context_type = 'AAP'
AND    pai_emp.action_information_category = 'EMPLOYEE DETAILS'
AND    pai_emp.action_information15 = l_org_name
AND    pai_emp1.action_context_type = 'AAP'
AND    pai_emp1.action_information_category(+) = 'ADDL EMPLOYEE DETAILS'
AND    ptp.end_date = l_effective_date
AND    pai_emp.action_context_id = paa1.assignment_action_id
AND    paa1.payroll_action_id = ppa1.payroll_action_id
AND    ppa1.action_type = 'X'
and    ppa1.report_type = 'KW_ARCHIVE'
AND    ppa1.action_status = 'C'
AND    paa1.action_status = 'C'
AND    ppf.payroll_id = ptp.payroll_id
AND    l_effective_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
ORDER BY decode(p_order_1,'first_name',pai_emp1.action_information6,'employee_number',pai_emp.action_information10,'family_name',pai_emp1.action_information11),
	 decode(p_order_2,'first_name',pai_emp1.action_information6,'employee_number',pai_emp.action_information10,'family_name',pai_emp1.action_information11,null,1),
	 decode(p_order_3,'first_name',pai_emp1.action_information6,'employee_number',pai_emp.action_information10,'family_name',pai_emp1.action_information11,null,1);

Line: 529


/* SELECT ELEMENTS AND DEDUCTIONS DETAILS */
CURSOR csr_get_earn_det (l_assact_id number) IS
/*The select statement is changed for fixing bug 6081731
SELECT   pai_ele.action_context_id arch_payact
              ,pay_v.action_context_id arch_assact
              ,pay_v.narrative earn_element
              ,pay_v.numeric_value earn_value
             ,pai_ele.action_information7
FROM    pay_action_information pai_ele
             ,pay_emea_paymnts_action_info_v pay_v
             ,pay_assignment_actions paa
WHERE    paa.assignment_action_id = l_assact_id
AND	 paa.payroll_action_id = pai_ele.action_context_id
AND      pai_ele.action_context_type = 'PA'
AND      pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
AND      pai_ele.action_information7 IN ('E')
AND      pay_v.action_context_id = paa.assignment_action_id
AND      pay_v.narrative = pai_ele.action_information4
AND      pay_v.payment_type NOT IN ('F');*/

Line: 548


SELECT   ppa.payroll_action_id arch_payact
         ,paa2.assignment_action_id arch_assact
         ,pai.action_information4 earn_element
         ,pet.result_value earn_value
         ,pai.action_information7
FROM
  pay_action_interlocks lck,
  pay_assignment_actions paa1,
  pay_assignment_actions paa2,
  pay_payroll_actions ppa,
  pay_action_information pai,
  pay_emea_payment_values_v pet
WHERE
  lck.locked_action_id = paa1.assignment_action_id AND
  paa1.source_action_id IS NULL AND
  paa1.payroll_action_id = ppa.payroll_action_id AND
  ppa.action_type IN ('P','U') AND
  ppa.payroll_action_id = NVL (pai.action_information1,ppa.payroll_action_id) AND
  pai.action_context_type = 'PA' AND
  pai.action_information_category = 'EMEA ELEMENT DEFINITION' AND
  paa1.assignment_action_id = pet.assignment_action_id AND
  pet.element_type_id = pai.action_information2 AND
  pet.input_value_id = pai.action_information3 AND
  lck.locking_action_id = paa2.assignment_action_id AND
  paa2.payroll_action_id = pai.action_context_id AND
  pai.action_information5 NOT IN ('F') AND
  pai.action_information7 IN ('E') AND
  paa2.assignment_action_id = l_assact_id;

Line: 578


/*SELECT   pai_ele.action_context_id arch_payact
              ,pay_v.action_context_id arch_assact
              ,pay_v.narrative ded_element
             ,pay_v.numeric_value ded_value
             ,pai_ele.action_information7
FROM    pay_action_information pai_ele
             ,pay_emea_paymnts_action_info_v pay_v
             ,pay_assignment_actions paa
WHERE  	 paa.assignment_action_id= l_assact_id
AND      paa.payroll_action_id = pai_ele.action_context_id
AND      pai_ele.action_context_type = 'PA'
AND      pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
AND      pai_ele.action_information7 IN ('D')
AND      pay_v.action_context_id = paa.assignment_action_id
AND      pay_v.narrative = pai_ele.action_information4
AND      pay_v.payment_type NOT IN ('F');*/

Line: 594


SELECT   ppa.payroll_action_id arch_payact
         ,paa2.assignment_action_id arch_assact
         ,pai.action_information4 ded_element
         ,pet.result_value ded_value
         ,pai.action_information7
FROM
  pay_action_interlocks lck,
  pay_assignment_actions paa1,
  pay_assignment_actions paa2,
  pay_payroll_actions ppa,
  pay_action_information pai,
  pay_emea_payment_values_v pet
WHERE
  lck.locked_action_id = paa1.assignment_action_id AND
  paa1.source_action_id IS NULL AND
  paa1.payroll_action_id = ppa.payroll_action_id AND
  ppa.action_type IN ('P','U') AND
  ppa.payroll_action_id = NVL (pai.action_information1,ppa.payroll_action_id) AND
  pai.action_context_type = 'PA' AND
  pai.action_information_category = 'EMEA ELEMENT DEFINITION' AND
  paa1.assignment_action_id = pet.assignment_action_id AND
  pet.element_type_id = pai.action_information2 AND
  pet.input_value_id = pai.action_information3 AND
  lck.locking_action_id = paa2.assignment_action_id AND
  paa2.payroll_action_id = pai.action_context_id AND
  pai.action_information5 NOT IN ('F') AND
  pai.action_information7 IN ('D') AND
  paa2.assignment_action_id = l_assact_id;

Line: 625


/* SELECT PAYMENT METHOD DETAILS */
CURSOR csr_get_paymeth_det (l_assact_id number) IS
SELECT        pen.org_payment_method_name
             ,pen.segment1 bank_name
             ,pen.segment2 branch_name
             ,pen.segment4 account_number
             ,pen.value pay_amount
             ,pen.action_context_id
             ,pay_assignment_actions_pkg.get_payment_status(paa.assignment_action_id,ppp.pre_payment_id) status
FROM          pay_emp_net_dist_action_info_v pen
             ,pay_action_interlocks pai
             ,pay_assignment_actions paa
             ,pay_payroll_actions ppa
             ,pay_pre_payments ppp
WHERE    pen.action_context_id = l_assact_id
AND      pen.action_context_id = pai.locking_action_id
AND      pai.locked_action_id =  paa.assignment_action_id
AND      paa.payroll_action_id = ppa.payroll_action_id
AND      ppa.action_type in ('P','U')
AND      ppa.action_status = 'C'
AND      paa.assignment_action_id = ppp.assignment_action_id
AND    (ppp.personal_payment_method_id = pen.personal_payment_method_id
            OR ppp.org_payment_method_id = pen.org_payment_method_id )
ORDER BY status, pay_amount;

Line: 651


SELECT   sum(pay_v.numeric_value)
FROM    pay_action_information pai_ele
             ,pay_emea_paymnts_action_info_v pay_v
             ,pay_assignment_actions paa
WHERE    paa.payroll_action_id = pai_ele.action_context_id
AND      pai_ele.action_context_type = 'PA'
AND      pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
AND      pai_ele.action_information7 IN ('E')
AND      pay_v.action_context_id = paa.assignment_action_id
AND      pay_v.narrative = pai_ele.action_information4
AND      pay_v.payment_type NOT IN ('F')
AND    paa.assignment_action_id in (SELECT        pai_emp.action_context_id arch_assact
FROM          per_time_periods ptp
            ,pay_action_information pai_emp
            ,pay_assignment_actions paa1
            ,pay_action_interlocks lck
            ,pay_payroll_actions ppa1
WHERE  ptp.payroll_id = l_payroll_id
AND    ptp.time_period_id = pai_emp.action_information16
AND    pai_emp.action_context_type = 'AAP'
AND    pai_emp.action_information_category = 'EMPLOYEE DETAILS'
AND    pai_emp.action_information15 = l_org_name
AND    lck.locking_action_id = pai_emp.action_context_id
AND    lck.locked_action_id = paa1.assignment_action_id
AND    paa1.payroll_action_id = ppa1.payroll_action_id
AND    ppa1.action_type in ('R','Q')
AND    ppa1.action_status = 'C'
AND    paa1.action_status = 'C'
AND    ptp.end_date = l_effective_date);*/

Line: 682


SELECT   sum(pay_v.numeric_value)
FROM    pay_action_information pai_ele
             ,pay_emea_paymnts_action_info_v pay_v
             ,pay_assignment_actions paa
WHERE    paa.payroll_action_id = pai_ele.action_context_id
AND      pai_ele.action_context_type = 'PA'
AND      pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
AND      pai_ele.action_information7 IN ('E')
AND      pay_v.action_context_id = paa.assignment_action_id
AND      pay_v.narrative = pai_ele.action_information4
AND      pay_v.payment_type NOT IN ('F')
AND    paa.assignment_action_id in (SELECT        pai_emp.action_context_id arch_assact
FROM          per_time_periods ptp
            ,pay_action_information pai_emp
            ,pay_assignment_actions paa1
            ,pay_action_interlocks lck
            ,pay_payroll_actions ppa1
WHERE  ptp.payroll_id = l_payroll_id
AND    ptp.time_period_id = pai_emp.action_information16
AND    pai_emp.action_context_type = 'AAP'
AND    pai_emp.action_information_category = 'EMPLOYEE DETAILS'
AND 	 pai_emp.action_information2  in (select to_char(pose.organization_id_child)
					   from per_org_structure_elements pose
					   connect by pose.organization_id_parent =
					   prior pose.organization_id_child
					   and pose.org_structure_version_id =
					   to_char (l_org_structure_version_id)
					   start with pose.organization_id_parent =  to_char(l_organization_id)
					   and pose.org_structure_version_id = to_char(l_org_structure_version_id)
					   union select  to_char(l_organization_id) from sys.dual)
/*AND    pai_emp.action_information15 = l_org_name*/
AND    lck.locking_action_id = pai_emp.action_context_id
AND    lck.locked_action_id = paa1.assignment_action_id
AND    paa1.payroll_action_id = ppa1.payroll_action_id
AND    ppa1.action_type in ('R','Q')
AND    ppa1.action_status = 'C'
AND    paa1.action_status IN ('C','S')
AND    ptp.end_date = l_effective_date);

Line: 723


SELECT   sum(pay_v.numeric_value)
FROM    pay_assignment_actions paa
        ,per_time_periods ptp
        ,pay_assignment_actions paa1
        ,pay_action_interlocks lck
        ,pay_payroll_actions ppa1
        ,pay_action_information pai_ele
        ,pay_action_information pai_emp
        ,pay_emea_paymnts_action_info_v pay_v
WHERE    paa.payroll_action_id = pai_ele.action_context_id
AND      pai_ele.action_context_type = 'PA'
AND      pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
AND      pai_ele.action_information7 IN ('E')
AND      pay_v.action_context_id = paa.assignment_action_id
AND      pay_v.narrative = pai_ele.action_information4
AND      pay_v.payment_type NOT IN ('F')
AND    paa.assignment_action_id = pai_emp.action_context_id
AND    ptp.payroll_id = l_payroll_id
AND    ptp.time_period_id = pai_emp.action_information16
AND    pai_emp.action_context_type = 'AAP'
AND    pai_emp.action_information_category = 'EMPLOYEE DETAILS'
AND      pai_emp.action_information2  = l_organization_id
--AND    pai_emp.action_information15 = l_org_name
AND    lck.locking_action_id = pai_emp.action_context_id
AND    lck.locked_action_id = paa1.assignment_action_id
AND    paa1.payroll_action_id = ppa1.payroll_action_id
AND    ppa1.action_type in ('R','Q')
AND    ppa1.action_status = 'C'
AND    paa1.action_status = 'C'
AND    ptp.end_date = l_effective_date;

Line: 757


select pai_emp.action_context_id arch_assact
from per_time_periods ptp
,pay_action_information pai_emp
,pay_assignment_actions paa1
,pay_action_interlocks lck
,pay_payroll_actions ppa1
where ptp.payroll_id = l_payroll_id
and ptp.time_period_id = pai_emp.action_information16
and pai_emp.action_context_type = 'AAP'
and pai_emp.action_information_category = 'EMPLOYEE DETAILS'
and pai_emp.action_information2 = l_organization_id
/*and pai_emp.action_information15 = l_org_name*/
and lck.locking_action_id = pai_emp.action_context_id
and lck.locked_action_id = paa1.assignment_action_id
and paa1.payroll_action_id = ppa1.payroll_action_id
and ptp.payroll_id = ppa1.payroll_id
and ppa1.action_type in ('R','Q')
and ppa1.action_status = 'C'
and paa1.action_status IN ('C','S')
and ptp.end_date = l_effective_date;

Line: 778


select SUM(pay_v.numeric_value)
from pay_action_information pai_ele
,pay_emea_paymnts_action_info_v pay_v
,pay_assignment_actions paa
where paa.payroll_action_id = pai_ele.action_context_id
and pai_ele.action_context_type = 'PA'
and pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
and pai_ele.action_information7 in ('E')
and pay_v.action_context_id = paa.assignment_action_id
and pay_v.narrative = pai_ele.action_information4
and pay_v.payment_type not in ('F')
and paa.assignment_action_id = l_arch_assact;

Line: 797


SELECT   sum(pay_v.numeric_value)
FROM    pay_action_information pai_ele
             ,pay_emea_paymnts_action_info_v pay_v
             ,pay_assignment_actions paa
WHERE    paa.payroll_action_id = pai_ele.action_context_id
AND      pai_ele.action_context_type = 'PA'
AND      pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
AND      pai_ele.action_information7 IN ('D')
AND      pay_v.action_context_id = paa.assignment_action_id
AND      pay_v.narrative = pai_ele.action_information4
AND      pay_v.payment_type NOT IN ('F')
AND    paa.assignment_action_id in (SELECT        pai_emp.action_context_id arch_assact
FROM          per_time_periods ptp
            ,pay_action_information pai_emp
            ,pay_assignment_actions paa1
            ,pay_action_interlocks lck
            ,pay_payroll_actions ppa1
WHERE  ptp.payroll_id = l_payroll_id
AND    ptp.time_period_id = pai_emp.action_information16
AND    pai_emp.action_context_type = 'AAP'
AND    pai_emp.action_information_category = 'EMPLOYEE DETAILS'
AND 	 pai_emp.action_information2  in (select to_char(pose.organization_id_child)
					   from per_org_structure_elements pose
					   connect by pose.organization_id_parent =
					   prior pose.organization_id_child
					   and pose.org_structure_version_id =
					   to_char (l_org_structure_version_id)
					   start with pose.organization_id_parent =  to_char(l_organization_id)
					   and pose.org_structure_version_id = to_char(l_org_structure_version_id)
					   union select  to_char(l_organization_id) from sys.dual)
/*AND    pai_emp.action_information15 = l_org_name*/
AND    lck.locking_action_id = pai_emp.action_context_id
AND    lck.locked_action_id = paa1.assignment_action_id
AND    paa1.payroll_action_id = ppa1.payroll_action_id
AND    ppa1.action_type in ('R','Q')
AND    ppa1.action_status = 'C'
AND    paa1.action_status IN ('C','S')
AND    ptp.end_date = l_effective_date);

Line: 838


SELECT  sum(pay_v.numeric_value)
FROM    pay_assignment_actions paa
        ,per_time_periods ptp
        ,pay_assignment_actions paa1
        ,pay_action_interlocks lck
        ,pay_payroll_actions ppa1
        ,pay_action_information pai_ele
        ,pay_action_information pai_emp
        ,pay_emea_paymnts_action_info_v pay_v
WHERE    paa.payroll_action_id = pai_ele.action_context_id
AND      pai_ele.action_context_type = 'PA'
AND      pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
AND      pai_ele.action_information7 IN ('D')
AND      pay_v.action_context_id = paa.assignment_action_id
AND      pay_v.narrative = pai_ele.action_information4
AND      pay_v.payment_type NOT IN ('F')
AND    paa.assignment_action_id = pai_emp.action_context_id
AND    ptp.payroll_id = l_payroll_id
AND    ptp.time_period_id = pai_emp.action_information16
AND    pai_emp.action_context_type = 'AAP'
AND    pai_emp.action_information_category = 'EMPLOYEE DETAILS'
AND      pai_emp.action_information2  = l_organization_id
--AND    pai_emp.action_information15 = l_org_name
AND    lck.locking_action_id = pai_emp.action_context_id
AND    lck.locked_action_id = paa1.assignment_action_id
AND    paa1.payroll_action_id = ppa1.payroll_action_id
AND    ppa1.action_type in ('R','Q')
AND    ppa1.action_status = 'C'
AND    paa1.action_status = 'C'
AND    ptp.end_date = l_effective_date;

Line: 872


select pai_emp.action_context_id arch_assact
from per_time_periods ptp
,pay_action_information pai_emp
,pay_assignment_actions paa1
,pay_action_interlocks lck
,pay_payroll_actions ppa1
where ptp.payroll_id = l_payroll_id
and ptp.time_period_id = pai_emp.action_information16
and pai_emp.action_context_type = 'AAP'
and pai_emp.action_information_category = 'EMPLOYEE DETAILS'
and pai_emp.action_information2 = l_organization_id
/*and pai_emp.action_information15 = l_org_name*/
and lck.locking_action_id = pai_emp.action_context_id
and lck.locked_action_id = paa1.assignment_action_id
and paa1.payroll_action_id = ppa1.payroll_action_id
and ptp.payroll_id = ppa1.payroll_id
and ppa1.action_type in ('R','Q')
and ppa1.action_status = 'C'
and paa1.action_status IN ('C','S')
and ptp.end_date = l_effective_date;

Line: 893


select SUM(pay_v.numeric_value)
from pay_action_information pai_ele
,pay_emea_paymnts_action_info_v pay_v
,pay_assignment_actions paa
where paa.payroll_action_id = pai_ele.action_context_id
and pai_ele.action_context_type = 'PA'
and pai_ele.action_information_category = 'EMEA ELEMENT DEFINITION'
and pai_ele.action_information7 in ('D')
and pay_v.action_context_id = paa.assignment_action_id
and pay_v.narrative = pai_ele.action_information4
and pay_v.payment_type not in ('F')
and paa.assignment_action_id = l_arch_assact;

Line: 909


vXMLtable.DELETE;

Line: 910


vXMLTable_summary.DELETE;

Line: 958


			select distinct pose.organization_id_parent
			into   l_parent_id
			from   per_org_structure_elements pose
			where  pose.org_structure_version_id = p_org_structure_version_id
			and pose.organization_id_parent not in (select pose1.organization_id_child
            							from per_org_structure_elements pose1
									where pose1.org_structure_version_id = p_org_structure_version_id);

Line: 1611


				tab_earn_data.delete;

Line: 1612


				tab_ded_data .delete;

Line: 1962


        select userenv('LANGUAGE') into g_nls_db_char from dual;

Line: 1997


			/*SELECT file_data
			INTO   p_pdf_blob
			FROM   fnd_lobs
			WHERE  file_id = (SELECT MAX(file_id)
			                  FROM    fnd_lobs
                	                         WHERE   file_name like '%PAY_PRG_ar_KW.pdf'); */

Line: 2003


			SELECT file_data
			INTO   p_pdf_blob
			FROM   fnd_lobs
			WHERE  file_id =
			  ( SELECT MAX(file_id)
			  from FND_LOBS
			  WHERE PROGRAM_NAME = 'PAY_PRG_ar_KW.pdf'
			  and   program_tag= 'TMP:XDO:XDOTMPLATE1:SEED'
			  and   nvl(EXPIRATION_DATE ,trunc(sysdate)) = trunc(sysdate)
			  );

Line: 2014


        		SELECT file_data
			INTO   p_pdf_blob
			FROM   fnd_lobs
			WHERE  file_id = (SELECT MAX(file_id)
			                  FROM    fnd_lobs
                	                         WHERE   file_name like '%PAY_PRG_SUMMARY_ar_KW.pdf');*/

Line: 2031


    select meaning
    from   hr_lookups
    where  lookup_type = p_lookup_type
    and    lookup_code = p_lookup_code;