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

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

Line: 42


SELECT element_type_id
FROM   pay_element_types_f
WHERE  element_name = 'K27 Reimbursement File Details'
AND    nvl(business_group_id, p_business_group_id) = p_business_group_id
AND    nvl(legislation_code, 'NO') = 'NO'
AND    p_effective_date BETWEEN effective_start_date AND effective_end_date;

Line: 52


SELECT input_value_id
FROM   pay_input_values_f
WHERE  element_type_id = p_element_type_id
AND    name = p_name
AND    nvl(business_group_id, p_business_group_id) = p_business_group_id
AND    nvl(legislation_code, 'NO') = 'NO'
AND    p_effective_date BETWEEN effective_start_date AND effective_end_date;

Line: 68


SELECT links.element_link_id
FROM   pay_element_links_f links, per_all_assignments_f assign
WHERE  links.element_type_id = p_element_type_id
AND    links.business_group_id=p_business_group_id
AND    assign.assignment_id=p_assignment_id
AND   ((    links.payroll_id is not null
and links.payroll_id = assign.payroll_id)
OR     (    links.link_to_all_payrolls_flag='Y'
and assign.payroll_id is not null)
OR     (    links.payroll_id is null
and links.link_to_all_payrolls_flag='N')
OR     links.job_id=assign.job_id
OR     links.position_id=assign.position_id
OR     links.people_group_id=assign.people_group_id
OR     links.organization_id=assign.organization_id
OR     links.grade_id=assign.grade_id
OR     links.location_id=assign.location_id
OR     links.pay_basis_id=assign.pay_basis_id
OR     links.employment_category=assign.employment_category)
AND    p_effective_date BETWEEN links.effective_start_date
AND     links.effective_end_date;

Line: 100


select distinct ptp.start_date
from
per_all_assignments_f paaf,
pay_payrolls_f ppf,
per_time_periods ptp
where
paaf.assignment_id =  p_assignment_id
AND paaf.business_group_id = p_business_group_id
AND p_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paaf.payroll_id = ppf.payroll_id
AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND paaf.business_group_id = ppf.business_group_id
AND ptp.payroll_id = ppf.payroll_id
AND p_effective_date BETWEEN ptp.start_date AND ptp.end_date;

Line: 213


		SELECT  element_type_id
		FROM pay_element_types_f
		WHERE
		Element_name = p_element_name
		AND p_date BETWEEN  effective_start_date  AND effective_end_date ;

Line: 221


		SELECT links.element_link_id
		FROM   pay_element_links_f links, per_all_assignments_f assign
		WHERE  links.element_type_id = p_element_type_id
		AND    links.business_group_id= p_business_group_id
		AND    assign.assignment_id= p_assignment_id
		AND   ((    links.payroll_id is not null
		and links.payroll_id = assign.payroll_id)
		OR     (    links.link_to_all_payrolls_flag='Y'
		and assign.payroll_id is not null)
		OR     (    links.payroll_id is null
		and links.link_to_all_payrolls_flag='N')
		OR     links.job_id=assign.job_id
		OR     links.position_id=assign.position_id
		OR     links.people_group_id=assign.people_group_id
		OR     links.organization_id=assign.organization_id
		OR     links.grade_id=assign.grade_id
		OR     links.location_id=assign.location_id
		OR     links.pay_basis_id=assign.pay_basis_id
		OR     links.employment_category=assign.employment_category)
		AND    p_date BETWEEN links.effective_start_date
		AND     links.effective_end_date;

Line: 322


SELECT PAY_NO_K27_REIM_UPLD.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER_NUM')
,PAY_NO_K27_REIM_UPLD.GET_PARAMETER(legislative_parameters,'TEST_SUBMISSION')
,PAY_NO_K27_REIM_UPLD.GET_PARAMETER(legislative_parameters,'FILE_NAME')
,effective_date
,business_group_id
,PAY_NO_K27_REIM_UPLD.GET_PARAMETER(legislative_parameters,'PERIOD_DATE')
FROM  pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;

Line: 409


SELECT  distinct PAA.ASSIGNMENT_ID, PAA.PAYROLL_ID
FROM per_all_assignments_f PAA
, per_all_people_f PAP
WHERE PAA.BUSINESS_GROUP_ID      = p_business_group_id
AND PAP.per_information_category ='NO'
AND (substr(PAP.NATIONAL_IDENTIFIER,1,6)||substr(PAP.NATIONAL_IDENTIFIER,8,5) = lpad(p_ni_number,11,'0')
OR PAP.NATIONAL_IDENTIFIER = lpad(p_ni_number,11,'0'))
AND PAA.PERSON_ID = PAP.PERSON_ID
AND PAA.PRIMARY_FLAG = 'Y'
AND PAA.payroll_id is not null --14363238
AND p_date between  PAA.EFFECTIVE_START_DATE  and PAA.EFFECTIVE_END_DATE
AND p_date between  PAP.EFFECTIVE_START_DATE  and PAP.EFFECTIVE_END_DATE ;

Line: 429


select distinct SEGMENT2
from
per_all_assignments_f paaf,
hr_soft_coding_keyflex hsck
where
paaf.assignment_id = p_asg_id
and paaf.business_group_id = p_business_group_id
and paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND p_date between  paaf.EFFECTIVE_START_DATE  and paaf.EFFECTIVE_END_DATE;

Line: 445


select hoi.ORG_INFORMATION1
FROM
HR_ORGANIZATION_INFORMATION hoi,
HR_ORGANIZATION_UNITS hou,
HR_ORGANIZATION_INFORMATION hoi1
where hoi.org_information_context = 'NO_LOCAL_UNIT_DETAILS'
and  HOi1.ORG_INFORMATION_CONTEXT = 'CLASS'
and  HOI1.org_information2 ='Y'
and  HOI1.org_information1 = 'NO_LOCAL_UNIT'
and  HOI.organization_id = HOI1.organization_id
and hou.organization_id = hoi.organization_id
and hou.business_group_id= p_business_group_id
and hoi.organization_id = p_asg_lu_org_id;

Line: 474


SELECT 'Y'
FROM
PAY_ACTION_INFORMATION
WHERE
action_information_category  = 'EMEA REPORT INFORMATION'
and action_information1      = 'K27_REIMB_UPLOAD'
--and action_information2      = p_mun_sso_num
and action_information3      = p_le_org_num
and action_information4      = p_lu_org_num
--and action_information5      = p_ref_code
and action_information6      = p_emp_ni_num
and action_information7      = p_reimb_start_dt
and action_information10     = p_reimb_end_dt
and action_information11     = p_reimb_amount
and nvl(action_information13,1)     = nvl(p_max_reimb_dt,1); -- added nvl for bug 16318591

Line: 494


select 'Y' from
pay_payroll_actions ppa,
pay_assignment_actions paa
where
ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = p_assignment_id
and ppa.action_type in ('R','Q')
and ppa.business_group_id = p_bg_id
and ppa.action_status = 'C'
and ppa.payroll_id = p_payroll_id
and exists
(select 1
from per_time_periods ptp
where
ptp.payroll_id = ppa.payroll_id
and ptp.time_period_id = ppa.time_period_id
and p_period_date BETWEEN ptp.start_date AND ptp.end_date
);

Line: 978


SELECT o1.name ,hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION2, hoi2.ORG_INFORMATION3, hoi2.ORG_INFORMATION4, hoi2.ORG_INFORMATION5, hoi2.ORG_INFORMATION6, hoi2.ORG_INFORMATION13
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE  o1.business_group_id =l_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id =  csr_v_legal_emp_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='DK_LEGAL_ENTITY_DETAILS' ;

Line: 995


SELECT
hoi2.ORG_INFORMATION2,
hoi2.ORG_INFORMATION3,
hoi2.ORG_INFORMATION4,
hoi2.ORG_INFORMATION5,
hoi2.ORG_INFORMATION6
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE  o1.business_group_id =l_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id =  csr_v_legal_emp_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='DK_DA_OFFICE_CODE';

Line: 1018


select * from hr_organization_information
where org_information_context = 'DK_SERVICE_PROVIDER_DETAILS'
and organization_id in (
select organization_id from hr_organization_units
where business_group_id= l_business_group_id);

Line: 1029


SELECT ptp.start_date, ptp.end_date, ptp.period_name,
default_dd_date,
decode(PERIOD_TYPE
  ,'Calendar Month','1'
,'Bi-Week'       ,'2'
    ,'Week'          ,'3'
  ,'Lunar Month'   ,'4')  PAYROLL_PERIOD
from per_time_periods ptp
WHERE payroll_id = p_payroll_id
AND time_period_id = p_payroll_period;

Line: 1045


  select payroll_name from pay_payrolls_f
  where payroll_id=id;

Line: 1052


SELECT DISTINCT
ppf.payroll_name,
ppf.payroll_id,
ptp.start_date,
ptp.end_date,
ptp.period_name,
ptp.default_dd_date,
 decode(ptp.PERIOD_TYPE
			,'Calendar Month','1'
			,'Bi-Week'       ,'2'
			,'Week'          ,'3'
			,'Lunar Month'   ,'4')  PAYROLL_PERIOD,
ptp.TIME_PERIOD_ID
FROM
pay_payrolls_f ppf,
per_assignments_f paf,
hr_organization_units hou,
hr_organization_information hoi,
hr_soft_coding_keyflex scl,
per_time_periods ptp
WHERE hou.business_group_id = l_business_group_id
AND hou.organization_id = g_legal_employer_id
AND hou.organization_id = hoi.organization_id
AND hoi.org_information_context = 'CLASS'
AND hoi.org_information1 = 'HR_LEGAL_EMPLOYER'
AND paf.payroll_id = ppf.payroll_id
AND ppf.payroll_id=NVL(g_payroll_id,ppf.payroll_id)
AND ppf.effective_start_date <= l_canonical_start_date and ppf.effective_end_date >= l_canonical_end_date
AND ptp.payroll_id=ppf.payroll_id
AND ptp.end_date BETWEEN l_canonical_start_date AND l_canonical_end_date
AND paf.SOFT_CODING_KEYFLEX_ID = scl.SOFT_CODING_KEYFLEX_ID
AND scl.segment1 = to_char(hou.organization_id)
AND ppf.business_group_id = l_business_group_id;

Line: 1090


select
report_type
from
pay_payroll_actions
where payroll_action_id = p_payroll_action_id;

Line: 1099


SELECT NVL(org_information5,'N')
FROM hr_organization_information
WHERE organization_id = p_org_id
and org_information_context = 'DK_HOLIDAY_ENTITLEMENT_INFO';

Line: 1105


SELECT PRL_INFORMATION1, Payroll_name
FROM pay_payrolls_f ppf
WHERE PAYROLL_ID =  p_payroll_id
AND p_date_earned BETWEEN ppf.EFFECTIVE_START_DATE AND ppf.EFFECTIVE_END_DATE;

Line: 1118


p_sql := 'SELECT DISTINCT person_id FROM  per_people_f ppf,pay_payroll_actions ppa WHERE ppa.payroll_action_id = :payroll_action_id
AND ppa.business_group_id = ppf.business_group_id and 1=2 ORDER BY ppf.person_id ';

Line: 1259


SELECT substr(action_information6,1,6)||'-'||substr(action_information6,7,5) NI_number,
to_char(to_date(action_information13,'RRRRMMDD'),'DD-MON-RRRR') Reimb_date
FROM
PAY_ACTION_INFORMATION
WHERE
action_information_category  = 'EMEA REPORT INFORMATION'
and action_information1      = 'K27_REIMB_UPLOAD'
and action_information3      = p_legal_employer
and to_date(action_information13,'RRRRMMDD') BETWEEN p_from_dt AND p_to_dt; */

Line: 1271


SELECT substr(pai.action_information6,1,6)||'-'||substr(pai.action_information6,7,5) NI_number,
	to_char(to_date(pai.action_information13,'RRRRMMDD'),'DD-MON-RRRR') Reimb_date,
      papf.employee_number emp_number,
	pai.action_information11 reimburse_amount FROM
	pay_action_information pai,
	per_all_assignments_f paaf ,
	per_all_people_f papf
	WHERE pai.action_information_category  = 'EMEA REPORT INFORMATION'
	  and pai.action_information1      = 'K27_REIMB_UPLOAD'
	  and pai.action_information3      = p_legal_employer
	  and to_date(pai.action_information13,'RRRRMMDD') BETWEEN p_from_dt AND p_to_dt
	  and paaf.assignment_id = pai.assignment_id
	  and to_date(pai.action_information13,'RRRRMMDD') between paaf.effective_start_date and paaf.effective_end_date
	  and paaf.person_id = papf.person_id
	  and to_date(pai.action_information13,'RRRRMMDD') between papf.effective_start_date and papf.effective_end_date;