[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT territory_short_name
FROM fnd_territories_vl
WHERE territory_code = p_territory_code;
SELECT pdb.defined_balance_id
FROM pay_balance_dimensions pbd,
pay_balance_types pbt,
pay_defined_balances pdb
WHERE pbd.database_item_suffix = p_dimension_suffix
AND pbd.business_group_id IS NULL
AND pbd.legislation_code = 'DK'
AND pbt.balance_name = p_balance_name
AND pbt.business_group_id IS NULL
AND pbt.legislation_code = 'DK'
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pdb.business_group_id IS NULL
AND pdb.legislation_code = 'DK';
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_EHOL.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER_ID')
,PAY_DK_ARCHIVE_EHOL.GET_PARAMETER(legislative_parameters,'PAYROLL')
,PAY_DK_ARCHIVE_EHOL.GET_PARAMETER(legislative_parameters,'YEAR')
,PAY_DK_ARCHIVE_EHOL.GET_PARAMETER(legislative_parameters,'PAYROLL_TYPE')
,PAY_DK_ARCHIVE_EHOL.GET_PARAMETER(legislative_parameters,'START_DATE')
,PAY_DK_ARCHIVE_EHOL.GET_PARAMETER(legislative_parameters,'END_DATE')
,PAY_DK_ARCHIVE_EHOL.GET_PARAMETER(legislative_parameters,'TEST_SUBMISSION')
,PAY_DK_ARCHIVE_EHOL.GET_PARAMETER(legislative_parameters,'COMPANY_TERMINATING')
,PAY_DK_ARCHIVE_EHOL.GET_PARAMETER(legislative_parameters,'MESSAGE_TEXT')
,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
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';
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;
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;
select
report_type
from
pay_payroll_actions
where payroll_action_id = p_payroll_action_id;
SELECT NVL(org_information5,'N')
FROM hr_organization_information
WHERE organization_id = p_org_id
and org_information_context = 'DK_HOLIDAY_ENTITLEMENT_INFO';
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;
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 IN ('PYDKEHOLCA' , 'PYDKEHOLCA_ANN', 'PYDKEHOLCA_MIA')
AND action_context_id = p_payroll_action_id;
SELECT to_char(sysdate,'yyyy-mm-dd') INTO l_date FROM dual;
SELECT to_char(sysdate,'hh:mi:ss') INTO l_time FROM dual;
SELECT to_char(sysdate,'yyyymmdd') INTO l_date_id FROM dual;
SELECT to_char(sysdate,'hhmiss') INTO l_time_id FROM dual;
p_sql := 'SELECT DISTINCT person_id FROM per_people_f ppf,pay_payroll_actions ppa WHERE 1=2';
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,
as1.hourly_salaried_code
,as1.payroll_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 appa.effective_date between l_canonical_start_date and l_canonical_end_date
AND act.source_action_id IS NULL -- Master Action
AND as1.assignment_id = act.assignment_id
-- Add payroll id
AND as1.payroll_id = nvl(p_payroll_id,as1.payroll_id) /* 9489806 */
-- Commenting Code to Include Terminated Assignments
-- AND ppa.effective_date BETWEEN as1.effective_start_date
-- AND as1.effective_end_date
AND act.action_status IN ('C','S') -- 10229494
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status IN ('C','S') -- 10229494
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.payroll_id, 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
report_type
from
pay_payroll_actions
where payroll_action_id = p_payroll_action_id;
SELECT MAX( EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
FROM per_all_assignments_f
WHERE
assignment_id = p_asg_id
AND 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_bg_id)));
SELECT decode(PERIOD_TYPE
,'Calendar Month','1'
,'Bi-Week' ,'2'
,'Week' ,'3'
,'Lunar Month' ,'4') PERIOD_TYPE
from per_time_periods ptp
WHERE payroll_id = p_payroll_id
and rownum=1;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
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, to_char(pap.date_of_birth,'yyyymmdd') dob,
pap.first_name||' '||pap.middle_names||' '||pap.last_name pname, pap.sex, pap.start_date, pap.business_group_id,
assign.hourly_salaried_code HOURLY_SALARIED_CODE, assign.assignment_number, assign.organization_id, assign.primary_flag,
assign.location_id
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 distinct
paaf.assignment_id ASG_ID
,ppf.payroll_name PAYROLL_NAME
,ppf.payroll_id
,paaf.assignment_number ASSIGNMENT_NUMBER
,to_char(paaf.effective_start_date,'YYYYMMDD') ASG_START_DATE
,paaf.effective_end_date ASG_END_DATE
,substr(to_char(papf.national_identifier),1,instr(to_char(papf.national_identifier),'-')-1)||substr(to_char(papf.national_identifier),instr(to_char(papf.national_identifier),'-')+1) CPR_NO
,scl.SEGMENT3 COND_OF_EMP
,scl.SEGMENT4 EMP_GRP
,scl.SEGMENT14 JOB_OCC_MKODE
,scl.SEGMENT15 JOB_STATUS_MKODE
,paaf.NORMAL_HOURS NORMAL_HOURS
,paaf.FREQUENCY FREQ
,scl.SEGMENT10 DEFAULT_WORK_PATT
,scl.SEGMENT11 HOURLY_ACCR_RATE
,scl.SEGMENT13 SAL_ALLOW_RATE
,decode(ppf.PERIOD_TYPE
,'Calendar Month','1'
,'Bi-Week' ,'2'
,'Week' ,'3'
,'Lunar Month' ,'4') PAYROLL_PERIOD
,scl.SEGMENT16 SAL_BASIS_MKODE
,scl.SEGMENT17 TIME_OFF_LIEU
,paaf.hourly_salaried_code HOURLY_SALARIED_CODE
,paaf.organization_id HR_ORG_ID
,paaf.location_id LOC_ID
,scl.SEGMENT21 MO_CODE
,scl.SEGMENT22 FO_CODE
,NVL(scl.SEGMENT23,'FH1') FH_CODE
FROM
per_all_people_f papf
,per_all_assignments_f paaf
,pay_payrolls_f ppf
,hr_soft_coding_keyflex scl
WHERE paaf.person_id = p_person_id
AND papf.PERSON_ID = paaf.PERSON_ID
AND ppf.payroll_id = p_payroll_id
AND paaf.payroll_id = ppf.payroll_id
AND paaf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.enabled_flag = 'Y'
AND paaf.assignment_id = p_assignment_id
AND p_asg_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND papf.current_employee_flag = 'Y'
AND scl.segment1 = to_char(p_le_id)
ORDER BY asg_id, ASG_START_DATE;
SELECT fnd_number.canonical_to_number(AEI_INFORMATION3)
FROM PER_ASSIGNMENT_EXTRA_INFO
WHERE
ASSIGNMENT_ID = p_asg_id
AND INFORMATION_TYPE = 'DK_EHOLC_HPAY_RATE'
AND p_effective_date between fnd_date.canonical_to_date(AEI_INFORMATION1)
AND NVL(fnd_date.canonical_to_date(AEI_INFORMATION2),to_date('31-12-4712','dd-mm-rrrr'));
SELECT hoi2.ORG_INFORMATION1 DEFAULT_WORK_PATT
,fnd_number.canonical_to_number(hoi2.ORG_INFORMATION3) HOURLY_ACCR_RATE
,fnd_number.canonical_to_number(hoi2.ORG_INFORMATION4) SAL_ALLOW_RATE
FROM HR_ORGANIZATION_UNITS hou
, HR_ORGANIZATION_INFORMATION hoi1
, HR_ORGANIZATION_INFORMATION hoi2
WHERE hou.business_group_id = p_business_group_id
and hoi1.organization_id = hou.organization_id
and hoi1.organization_id = p_le_id
and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
and hoi1.ORG_INFORMATION2 = 'Y'
and hoi2.ORG_INFORMATION_CONTEXT='DK_HOLIDAY_ENTITLEMENT_INFO'
and hoi2.organization_id = hoi1.organization_id
and p_effective_date BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, p_effective_date);
SELECT hou.organization_id ORG_ID
,hoi2.ORG_INFORMATION1 CVR_NO
,hoi2.ORG_INFORMATION2 DS_WPCODE
,hoi2.ORG_INFORMATION6 PUNIT
,hou.name NAME
,SUBSTR(loc.ADDRESS_LINE_1,1,100) ad_line1
,SUBSTR(loc.ADDRESS_LINE_2,1,100) ad_line2
,SUBSTR(loc.ADDRESS_LINE_3,1,100) ad_line3
,loc.postal_code
,loc.TOWN_OR_CITY
,loc.COUNTRY
,loc.REGION_1
,loc.REGION_2
,loc.REGION_3
,loc.style
FROM HR_ORGANIZATION_UNITS hou
, HR_ORGANIZATION_INFORMATION hoi1
, HR_ORGANIZATION_INFORMATION hoi2
, HR_LOCATIONS loc
WHERE hou.business_group_id = p_business_group_id
and hou.organization_id = p_le_id
and hoi1.organization_id = hou.organization_id
and hou.location_id = loc.LOCATION_ID(+)
and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
and hoi1.ORG_INFORMATION2 = 'Y'
and hoi2.ORG_INFORMATION_CONTEXT='DK_LEGAL_ENTITY_DETAILS'
and hoi2.organization_id = hoi1.organization_id
and p_effective_date BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, p_effective_date);
SELECT
pad.address_line1
,pad.address_line2
,pad.address_line3
,pad.postal_code
,pad.TOWN_OR_CITY
,pad.COUNTRY
,pad.REGION_1
,pad.REGION_2
,pad.REGION_3
,pad.style
FROM per_addresses pad
, per_all_people_f pap
WHERE pad.person_id = pap.person_id
AND pad.primary_flag = 'Y'
AND pap.person_id = p_person_id
AND pad.business_group_id = pap.business_group_id
AND pad.business_group_id = p_business_group_id
AND p_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date
AND p_effective_date BETWEEN pad.date_from AND NVL(pad.date_to,to_date('31-12-4712','DD-MM-YYYY'));
select
pea.SEGMENT1 Bank_Registration,
pea.SEGMENT2 Bank_Branch,
pea.SEGMENT3 Account_Number
from
pay_personal_payment_methods_f pppm
,pay_external_accounts pea
where
pppm.ASSIGNMENT_ID = p_assignment_id
and pppm.BUSINESS_GROUP_ID = p_business_group_id
and pppm.EXTERNAL_ACCOUNT_ID = pea.EXTERNAL_ACCOUNT_ID
and p_effective_date between EFFECTIVE_START_DATE and nvl(EFFECTIVE_END_DATE,p_effective_date);
SELECT MAX(date_start) lhd FROM per_periods_of_service
WHERE person_id=pid
AND date_start <= p_end_date;
SELECT PEI_INFORMATION1 yes_no FROM per_people_extra_info
WHERE person_id=pid
AND information_type='DK_EINCOME_FORIEGN_IND';
SELECT *
FROM per_addresses_v
WHERE person_id =pid
and primary_flag='Y';
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 lei_information1
FROM hr_location_extra_info
WHERE location_id = p_location_id
AND information_type='DK_LOCATION_INFO';
SELECT --DISTINCT
max(ppf.payroll_id) payroll_id,
max(ptp.time_period_id) time_period_id
--ppf.payroll_name,
--ptp.period_name
FROM
pay_payrolls_f ppf,
per_assignments_f paf,
per_time_periods ptp
WHERE paf.payroll_id = ppf.payroll_id
AND paf.assignment_id=c_assignment_id
AND ppf.payroll_id=NVL(g_payroll_id,ppf.payroll_id)
AND ppf.effective_start_date <= c_canonical_start_date and ppf.effective_end_date >= c_canonical_end_date
AND ptp.payroll_id=ppf.payroll_id
AND ptp.end_date BETWEEN c_canonical_start_date AND c_canonical_end_date
AND ppf.business_group_id = g_business_group_id
AND ptp.end_date between paf.effective_start_date and paf.effective_end_date
and exists
(select 1 from pay_payroll_actions ppa, pay_assignment_actions paa
where ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type IN ('R','Q')
AND ppa.effective_date between c_canonical_start_date and c_canonical_end_date
AND paa.assignment_id = paf.assignment_id
AND paa.assignment_id = c_assignment_id
AND ppa.time_period_id = ptp.time_period_id
);
l_select_str VARCHAR2(200) := ' SELECT max(ppa.payroll_id) payroll_id, max(ppa.time_period_id) time_period_id ';
l_select_qry VARCHAR2(3000);
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 10322958
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 MAX(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 *
FROM per_assignment_extra_info
WHERE information_type = 'DK_EINCOME_INFO'
AND assignment_id = p_assgt_id;
SELECT 1
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 appa.action_type IN ('R','Q')
AND act.payroll_action_id = appa.payroll_action_id
AND appa.time_period_id = p_payroll_period
AND appa.effective_date between l_canonical_start_date and l_canonical_end_date
AND act.source_action_id IS NULL -- Master Action
AND as1.assignment_id = act.assignment_id
AND as1.payroll_id = p_payroll_id
AND act.action_status IN ('C','S') -- 10229494
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status IN ('C','S') -- 10229494
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
AND as1.assignment_id = l_assignment_id;
SELECT NVL(org_information5,'N')
FROM hr_organization_information
WHERE organization_id = p_org_id
and org_information_context = 'DK_HOLIDAY_ENTITLEMENT_INFO';
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;
SELECT fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) asg_action_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = p_assignment_id
and ppa.action_type in ('R','Q','I','B','V')
and paa.source_action_id is null
AND ppa.effective_date between p_start_date AND p_end_date;
SELECT 'Y'
FROM pay_payroll_actions ppa
, pay_user_tables put
, pay_user_columns puc
, pay_user_column_instances_f puci
, pay_user_rows_f pur
, pay_user_columns puc1
, pay_user_column_instances_f puci1
, pay_user_rows_f pur1
WHERE ppa.payroll_action_id = p_payroll_action_id
AND put.user_table_name = 'DK_PBS_DATA'
AND put.LEGISLATION_CODE ='DK'
AND puc.user_table_id = put.user_table_id
AND puc.user_column_name = 'Information Type'
AND puci.user_column_id = puc.user_column_id
AND ppa.effective_date between puci.effective_start_date and puci.effective_end_date
AND pur.user_row_id = puci.user_row_id
AND ppa.effective_date between pur.effective_start_date and pur.effective_end_date
and puc1.user_table_id = put.user_table_id
and puc1.user_column_name = 'PBS Number'
and puci1.user_column_id = puc1.user_column_id
and puci1.business_group_id =ppa.business_group_id
and ppa.effective_date between puci1.effective_start_date and puci1.effective_end_date
and pur1.user_row_id = puci1.user_row_id
and pur1.user_row_id = pur.user_row_id
and ppa.effective_date between pur1.effective_start_date and pur1.effective_end_date
and puci.value = '400';
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 = p_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = p_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';
select
report_type
from
pay_payroll_actions
where payroll_action_id = p_payroll_action_id;
SELECT payroll_action_id, assignment_id
INTO l_payroll_action_id,l_ass_id /* 9489806 */
FROM pay_assignment_actions
WHERE assignment_action_id=p_assignment_action_id;
l_select_str := 'SELECT DISTINCT ppa.payroll_id, ppa.time_period_id ';
l_select_qry := l_select_str
||' FROM
pay_payrolls_f ppf,
per_assignments_f paf,
per_time_periods ptp
WHERE paf.payroll_id = ppf.payroll_id
AND paf.assignment_id= ' || l_ass_id
|| l_where_clause
||' 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 ('||''''|| l_canonical_start_date ||''''|| ' BETWEEN ptp.start_date and ptp.end_date OR '||''''|| l_canonical_end_date ||''''|| ' BETWEEN
ptp.start_date and ptp.end_date)'
||' AND ppf.business_group_id = ' || g_business_group_id ||
' AND (ptp.end_date between paf.effective_start_date and paf.effective_end_date OR ptp.start_date between paf.effective_start_date and paf.effective_end_date )
and exists
(select 1 from pay_payroll_actions ppa, pay_assignment_actions paa
where ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type IN ('||''''||'R'||''''||','||''''||'Q'||''''|| ') '
||' AND ppa.effective_date between '||''''|| l_canonical_start_date ||''''||' and ' ||''''|| l_canonical_end_date ||''''
||' AND paa.assignment_id = paf.assignment_id
AND paa.assignment_id = ' || l_ass_id
||' AND ppa.time_period_id = ptp.time_period_id
)';
l_select_qry := l_select_str
||' FROM
pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type IN ('||''''||'R'||''''||','||''''||'Q'||''''|| ') '
||' AND ppa.effective_date between '||''''|| l_canonical_start_date ||''''||' and ' ||''''|| l_canonical_end_date ||''''
|| l_where_clause
||' AND paa.assignment_id = ' || l_ass_id;
Fnd_file.put_line(FND_FILE.LOG,'Dynamic Query:'||l_select_qry );
OPEN csr_asg_payroll FOR l_select_qry; --Ref Cursor
fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 19-1: Inserted PYDKEHOLCA - 1');
fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 20: Inserted PYDKEHOLCA - 2');
fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 20: Inserted PYDKEHOLCA - 3');
fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 20: Inserted PYDKEHOLCA - 4');
SELECT *
FROM
PAY_ACTION_INFORMATION
WHERE
ACTION_CONTEXT_ID = p_payroll_action_id
AND ACTION_CONTEXT_TYPE = 'PA'
AND ACTION_INFORMATION_CATEGORY = 'EMEA REPORT INFORMATION'
AND ACTION_INFORMATION1 = p_report_type; --'PYDKEHOLCA';
SELECT distinct PAI.ACTION_INFORMATION30 Payroll_id
FROM
PAY_ASSIGNMENT_ACTIONS PAA,
PAY_ACTION_INFORMATION PAI
WHERE
PAA.PAYROLL_ACTION_ID = p_payroll_action_id
AND PAI.ACTION_CONTEXT_ID = PAA.ASSIGNMENT_ACTION_ID
AND PAI.ACTION_CONTEXT_TYPE = 'AAP'
AND PAI.ACTION_INFORMATION_CATEGORY = 'EMEA REPORT INFORMATION'
AND PAI.ACTION_INFORMATION1 = p_report_type --'PYDKEHOLCA'
AND PAI.ACTION_INFORMATION3 = 'DK EMPLOYEE DETAILS';
SELECT PAI.*
FROM
PAY_ASSIGNMENT_ACTIONS PAA,
PAY_ACTION_INFORMATION PAI
WHERE
PAA.PAYROLL_ACTION_ID = p_payroll_action_id
AND PAI.ACTION_CONTEXT_ID = PAA.ASSIGNMENT_ACTION_ID
AND PAI.ACTION_CONTEXT_TYPE = 'AAP'
AND PAI.ACTION_INFORMATION_CATEGORY = 'EMEA REPORT INFORMATION'
AND PAI.ACTION_INFORMATION1 = p_report_type --'PYDKEHOLCA'
AND PAI.ACTION_INFORMATION3 = 'DK EMPLOYEE DETAILS'
AND PAI.ACTION_INFORMATION30 = p_payroll_id
ORDER BY PAI.ACTION_INFORMATION4, PAI.ACTION_INFORMATION5;
SELECT PAI.*
FROM
PAY_ASSIGNMENT_ACTIONS PAA,
PAY_ACTION_INFORMATION PAI
WHERE
PAA.PAYROLL_ACTION_ID = p_payroll_action_id
AND PAI.ACTION_CONTEXT_ID = PAA.ASSIGNMENT_ACTION_ID
AND PAI.ACTION_CONTEXT_TYPE = 'AAP'
AND PAI.ACTION_INFORMATION_CATEGORY = 'EMEA REPORT INFORMATION'
AND PAI.ACTION_INFORMATION1 = p_report_type --'PYDKEHOLCA'
AND PAI.ACTION_INFORMATION3 = p_hcard_info --'DK HCARD INFO' / 'DK HCARD INFO ADD'
AND PAI.ACTION_INFORMATION30 = p_payroll_id
AND PAI.ACTION_INFORMATION4 = p_person_id
AND PAI.ACTION_INFORMATION5 = p_asg_id
ORDER BY ACTION_INFORMATION6 DESC;
SELECT PAI.*
FROM
PAY_ASSIGNMENT_ACTIONS PAA,
PAY_ACTION_INFORMATION PAI
WHERE
PAA.PAYROLL_ACTION_ID = p_payroll_action_id
AND PAI.ACTION_CONTEXT_ID = PAA.ASSIGNMENT_ACTION_ID
AND PAI.ACTION_CONTEXT_TYPE = 'AAP'
AND PAI.ACTION_INFORMATION_CATEGORY = 'EMEA REPORT INFORMATION'
AND PAI.ACTION_INFORMATION1 = p_report_type --'PYDKEHOLCA'
AND PAI.ACTION_INFORMATION3 = p_hcard_info --'DK HCARD INFO' / 'DK HCARD INFO ADD'
AND PAI.ACTION_INFORMATION30 = p_payroll_id
AND PAI.ACTION_INFORMATION4 = p_person_id
AND PAI.ACTION_INFORMATION5 = p_asg_id
AND PAI.ACTION_INFORMATION6 = p_year
ORDER BY ACTION_INFORMATION6 DESC;
SELECT
ACTION_INFORMATION29 Period_id,
ACTION_INFORMATION27 period_from, --14078879
ACTION_INFORMATION28 period_to, --14078879
ACTION_INFORMATION30 payroll_id
FROM
PAY_ACTION_INFORMATION
WHERE
ACTION_INFORMATION1 = 'PYDKEHOLCA_MIA'
AND ACTION_INFORMATION2 = p_payroll_action_id
AND ACTION_INFORMATION3 = 'DK EMPLOYEE DETAILS'
--AND ACTION_INFORMATION30 = p_payroll_id
AND ACTION_INFORMATION4 IS NOT NULL --14078879
AND ACTION_INFORMATION5 IS NOT NULL --14078879
GROUP BY ACTION_INFORMATION30, ACTION_INFORMATION29, ACTION_INFORMATION28, ACTION_INFORMATION27;
SELECT PAI.*
FROM
PAY_ASSIGNMENT_ACTIONS PAA,
PAY_ACTION_INFORMATION PAI
WHERE
PAA.PAYROLL_ACTION_ID = p_payroll_action_id
AND PAI.ACTION_CONTEXT_ID = PAA.ASSIGNMENT_ACTION_ID
AND PAI.ACTION_CONTEXT_TYPE = 'AAP'
AND PAI.ACTION_INFORMATION_CATEGORY = 'EMEA REPORT INFORMATION'
AND PAI.ACTION_INFORMATION1 = 'PYDKEHOLCA_MIA'
AND PAI.ACTION_INFORMATION3 = 'DK EMPLOYEE DETAILS'
AND PAI.ACTION_INFORMATION30 = p_payroll_id
AND PAI.ACTION_INFORMATION29 = p_period_id
AND PAI.ACTION_INFORMATION4 IS NOT NULL --14078879
AND PAI.ACTION_INFORMATION5 IS NOT NULL --14078879
ORDER BY PAI.ACTION_INFORMATION4, PAI.ACTION_INFORMATION5;
SELECT PAI.*
FROM
PAY_ASSIGNMENT_ACTIONS PAA,
PAY_ACTION_INFORMATION PAI
WHERE
PAA.PAYROLL_ACTION_ID = p_payroll_action_id
AND PAI.ACTION_CONTEXT_ID = PAA.ASSIGNMENT_ACTION_ID
AND PAI.ACTION_CONTEXT_TYPE = 'AAP'
AND PAI.ACTION_INFORMATION_CATEGORY = 'EMEA REPORT INFORMATION'
AND PAI.ACTION_INFORMATION1 = 'PYDKEHOLCA_MIA'
AND PAI.ACTION_INFORMATION3 = p_hcard_info --'DK HCARD ACCRUAL' / DK HCARD PAYMENTS
AND PAI.ACTION_INFORMATION30 = p_payroll_id
AND PAI.ACTION_INFORMATION4 = p_person_id
AND PAI.ACTION_INFORMATION5 = p_asg_id
AND PAI.ACTION_INFORMATION28 = p_period_id;
select
report_type
from
pay_payroll_actions
where payroll_action_id = p_payroll_act_id;