[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pdb.defined_balance_id
FROM pay_defined_balances pdb
,pay_balance_types pbt
,pay_balance_dimensions pbd
WHERE pbt.legislation_code='DK'
AND pbt.balance_name = p_balance_name
AND pbd.legislation_code = 'DK'
AND pbd.database_item_suffix = p_balance_dim
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id;
SELECT PAY_DK_ARCHIVE_EIN.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER_ID')
,PAY_DK_ARCHIVE_EIN.GET_PARAMETER(legislative_parameters,'PAYROLL')
,PAY_DK_ARCHIVE_EIN.GET_PARAMETER(legislative_parameters,'PAYROLL_PERIOD')
,PAY_DK_ARCHIVE_EIN.GET_PARAMETER(legislative_parameters,'TEST_SUBMISSION')
,PAY_DK_ARCHIVE_EIN.GET_PARAMETER(legislative_parameters,'COMPANY_TERMINATING')
,effective_date
,business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
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' ;
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);
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;
select payroll_name from pay_payrolls_f
where payroll_id=id;
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 ORDER BY ppf.person_id';
SELECT count(*)
INTO l_count
FROM pay_action_information
WHERE action_information_category = 'EMEA REPORT DETAILS'
AND action_information1 = 'PYDKEINA'
AND action_context_id = p_payroll_action_id;
SELECT to_char(sysdate,'yyyymmdd') INTO l_date FROM dual;
SELECT to_char(sysdate,'hhmiss') INTO l_time FROM dual;
SELECT as1.person_id person_id,
act.assignment_id assignment_id,
act.assignment_action_id run_action_id,
act1.assignment_action_id prepaid_action_id
FROM pay_payroll_actions ppa
,pay_payroll_actions appa
,pay_payroll_actions appa2
,pay_assignment_actions act
,pay_assignment_actions act1
,pay_action_interlocks pai
,per_all_assignments_f as1
WHERE ppa.payroll_action_id = p_payroll_action_id
AND appa.effective_date BETWEEN l_canonical_start_date
AND l_canonical_end_date
AND as1.person_id BETWEEN p_start_person
AND p_end_person
AND appa.action_type IN ('R','Q')
-- Payroll Run or Quickpay Run
AND act.payroll_action_id = appa.payroll_action_id
--aapa table add time period check
AND appa.time_period_id = p_payroll_period
AND act.source_action_id IS NULL -- Master Action
AND as1.assignment_id = act.assignment_id
-- Add payroll id
AND as1.payroll_id = p_payroll_id
-- Commenting Code to Include Terminated Assignments
-- AND ppa.effective_date BETWEEN as1.effective_start_date
-- AND as1.effective_end_date
AND act.action_status = 'C' -- Completed
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status = 'C' -- Completed
AND act1.payroll_action_id = appa2.payroll_action_id
AND appa2.action_type IN ('P','U')
AND appa2.effective_date BETWEEN l_canonical_start_date
AND l_canonical_end_date
-- Prepayments or Quickpay Prepayments
AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
AND act.TAX_UNIT_ID = p_legal_employer_id
ORDER BY as1.person_id , act.assignment_id;
select start_date, end_date from per_time_periods
where payroll_id = p_payroll_id
and time_period_id = p_payroll_period;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
SELECT pap.national_identifier cpr , pap.person_id , pac.assignment_id, pap.full_name,
pap.start_date, pap.business_group_id, assign.hourly_salaried_code HOURLY_SALARIED_CODE,
assign.assignment_number, assign.organization_id, assign.primary_flag
FROM
pay_assignment_actions pac,
per_all_assignments_f assign,
per_all_people_f pap
WHERE pac.assignment_action_id = p_asg_act_id
AND assign.assignment_id = pac.assignment_id
AND assign.person_id = pap.person_id
AND pap.per_information_category = 'DK'
AND p_asg_effective_date BETWEEN assign.effective_start_date
AND assign.effective_end_date
AND p_asg_effective_date BETWEEN pap.effective_start_date
AND pap.effective_end_date;
SELECT MAX(date_start) lhd FROM per_periods_of_service
WHERE person_id=pid;
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 =bg_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = hr_org_id
AND hoi1.org_information1 = 'HR_ORG'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='DK_EMPLOYMENT_DEFAULTS' ;
SELECT ptp.start_date, ptp.end_date,
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;
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 =g_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' ;
SELECT eev1.screen_entry_value screen_entry_value
FROM per_all_assignments_f asg1
,per_all_people_f per
,pay_element_links_f el
,pay_element_types_f et
,pay_input_values_f iv1
,pay_element_entries_f ee
,pay_element_entry_values_f eev1
WHERE asg1.assignment_id = p_assignment_id
AND per.person_id = asg1.person_id
AND et.element_name = 'Employee ATP'
AND et.legislation_code = 'DK'
AND iv1.element_type_id = et.element_type_id
AND iv1.name = 'ATP Table'
AND el.business_group_id = per.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.element_link_id = el.element_link_id
AND ee.assignment_id = asg1.assignment_id
AND eev1.element_entry_id = ee.element_entry_id
AND eev1.input_value_id = iv1.input_value_id
AND asg1.effective_end_date >= p_start_date
AND asg1.effective_start_date <= p_end_date
AND per.effective_end_date >= p_start_date
AND per.effective_start_date <= p_end_date
AND ee.effective_end_date >= p_start_date
AND ee.effective_start_date <= p_end_date
AND ((eev1.effective_start_date <= p_start_date
AND eev1.effective_end_date >= p_start_date )
OR (eev1.effective_start_date BETWEEN p_start_date AND p_end_date
AND eev1.effective_end_date >= p_end_date ));
SELECT ee.effective_start_date, eev1.screen_entry_value
FROM per_all_assignments_f asg1
,per_all_people_f per
,pay_element_links_f el
,pay_element_types_f et
,pay_input_values_f iv1
,pay_element_entries_f ee
,pay_element_entry_values_f eev1
WHERE asg1.assignment_id = p_assignment_id
AND per.person_id = asg1.person_id
AND et.element_name = 'Tax Card'
AND et.legislation_code = 'DK'
AND iv1.element_type_id = et.element_type_id
AND iv1.name = 'Tax Card Type'
AND el.business_group_id = per.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.element_link_id = el.element_link_id
AND ee.assignment_id = asg1.assignment_id
AND eev1.element_entry_id = ee.element_entry_id
AND eev1.input_value_id = iv1.input_value_id
AND asg1.effective_end_date >= p_start_date
AND asg1.effective_start_date <= p_end_date
AND per.effective_end_date >= p_start_date
AND per.effective_start_date <= p_end_date
AND ee.effective_end_date >= p_start_date
AND ee.effective_start_date <= p_end_date
AND ((eev1.effective_start_date <= p_start_date
AND eev1.effective_end_date >= p_start_date )
OR (eev1.effective_start_date BETWEEN p_start_date AND p_end_date
AND eev1.effective_end_date >= p_end_date ));
SELECT MAX( EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
FROM per_all_assignments_f paa
,pay_assignment_actions pac
WHERE pac.assignment_action_id = p_asg_act_id
AND paa.assignment_id = pac.assignment_id
AND paa.EFFECTIVE_START_DATE <= p_end_date
AND paa.EFFECTIVE_END_DATE > = p_start_date
AND assignment_status_type_id IN
(select assignment_status_type_id
from per_assignment_status_types
where per_system_status = 'ACTIVE_ASSIGN'
and active_flag = 'Y'
and (( legislation_code is null
and business_group_id is null)
OR (BUSINESS_GROUP_ID = p_business_group_id)));
SELECT ue.creator_id
FROM ff_user_entities ue, ff_database_items di
WHERE di.user_name = csr_v_Balance_Name
AND ue.user_entity_id = di.user_entity_id
AND ue.legislation_code = 'DK'
AND ue.business_group_id is NULL
AND ue.creator_type = 'B';
select pac.ASSIGNMENT_ACTION_ID id
from pay_assignment_actions pac, pay_payroll_actions ppa
where ppa.action_type IN ('R','Q')
and pac.payroll_action_id = ppa.payroll_action_id
and pac.assignment_id= assg_id
and ppa.payroll_id=pid
and ppa.time_period_id=tid
and pac.tax_unit_id=le_id;
SELECT payroll_action_id
INTO l_payroll_action_id
FROM pay_assignment_actions
WHERE assignment_action_id=p_assignment_action_id;
select to_char(HIRE_DATE) into l_hd from PER_PEOPLE_V where NATIONAL_IDENTIFIER=rg_csr_get_person_details.cpr ;
-- fnd_file.put_line(fnd_file.log,'Inserting terminators');
SELECT *
FROM pay_action_information pai
WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_information1 = 'PYDKEINA'
AND pai.action_information2 = to_char(p_payroll_action_id)
AND pai.action_information3 IN ('1000','2001','2101','5000','6000')
ORDER BY pai.action_information3,action_context_id DESC
FOR UPDATE;
SELECT *
FROM pay_action_information pai
WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_information1 = 'PYDKEINA'
AND pai.action_information2 = to_char(p_payroll_action_id)
AND pai.action_information4 = to_char(p_person_id)
AND pai.action_information3 IN ('6001','6002','6004','6005')
ORDER BY pai.action_information3,action_context_id,pai.action_information6 DESC
FOR UPDATE;
UPDATE pay_action_information pai
SET pai.action_information30 =LPAD(l_end_code,7,'0')
WHERE CURRENT OF csr_all_rec;
UPDATE pay_action_information pai
SET pai.action_information30 =LPAD(l_end_code,7,'0')
WHERE CURRENT OF csr_asg_all_rec;