[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PAY_DK_ARCHIVE.GET_PARAMETER(legislative_parameters,'PAYROLL_ID')
,PAY_DK_ARCHIVE.GET_PARAMETER(legislative_parameters,'CONSOLIDATION_SET_ID')
,PAY_DK_ARCHIVE.GET_PARAMETER(legislative_parameters,'START_DATE')
,PAY_DK_ARCHIVE.GET_PARAMETER(legislative_parameters,'END_DATE')
,effective_date
,business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT org_information6 message
FROM hr_organization_information
WHERE organization_id = p_bus_grp_id
AND org_information_context = 'Business Group:Payslip Info'
AND org_information1 = 'MESG';
SELECT org_information4 balance_type_id
,org_information5 balance_dim_id
,org_information7 narrative
FROM hr_organization_information
WHERE organization_id = p_bus_grp_id
AND org_information_context = 'Business Group:Payslip Info'
AND org_information1 = 'BALANCE';
SELECT defined_balance_id
FROM pay_defined_balances
WHERE balance_type_id = bal_type_id
AND balance_dimension_id = bal_dim_id;
SELECT ptp.end_date end_date,
ptp.start_date start_date,
ptp.period_name period_name,
ppf.payroll_name payroll_name
FROM per_time_periods ptp
,pay_payroll_actions ppa
,pay_payrolls_f ppf
WHERE ptp.payroll_id = ppa.payroll_id
AND ppa.payroll_action_id = p_run_payact_id
AND ppa.payroll_id = ppf.payroll_id
AND ppf.payroll_id = NVL(p_payroll_id , ppf.payroll_id)
AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date
AND ppa.date_earned BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
SELECT hoi.org_information2 element_type_id
,hoi.org_information3 input_value_id
,hoi.org_information7 element_narrative
,pec.classification_name
,piv.uom
FROM hr_organization_information hoi
,pay_element_classifications pec
,pay_element_types_f pet
,pay_input_values_f piv
WHERE hoi.organization_id = p_bus_grp_id
AND hoi.org_information_context = 'Business Group:Payslip Info'
AND hoi.org_information1 = 'ELEMENT'
AND hoi.org_information2 = pet.element_type_id
AND pec.classification_id = pet.classification_id
AND piv.input_value_id = hoi.org_information3
AND p_date_earned BETWEEN piv.effective_start_date AND piv.effective_end_date;
SELECT 1 INTO l_flag
FROM pay_action_information
WHERE action_information_category = 'EMEA BALANCE DEFINITION'
AND action_context_id = p_payroll_action_id
AND action_information2 = l_defined_balance_id
AND action_information6 = 'OBAL'
AND action_information4 = rec_get_balance.narrative;
SELECT 1 INTO l_flag
FROM pay_action_information
WHERE action_context_id = p_payroll_action_id
AND action_information_category = 'EMEA ELEMENT DEFINITION'
AND action_information2 = rec_get_element.element_type_id
AND action_information3 = rec_get_element.input_value_id
AND action_information5 = l_element_context;
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';
-- Return cursor that selects no rows
p_sql := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
SELECT 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.consolidation_set_id = p_consolidation_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
AND act.source_action_id IS NULL -- Master Action
AND as1.assignment_id = act.assignment_id
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') -- Prepayments or Quickpay Prepayments
AND appa2.effective_date BETWEEN l_canonical_start_date AND l_canonical_end_date
AND (as1.payroll_id = p_payroll_id OR p_payroll_id IS NULL)
AND NOT EXISTS (SELECT /* + ORDERED */ NULL
FROM pay_action_interlocks pai1,
pay_assignment_actions act2,
pay_payroll_actions appa3
WHERE pai1.locked_action_id = act.assignment_action_id
AND act2.assignment_action_id= pai1.locking_action_id
AND act2.payroll_action_id = appa3.payroll_action_id
AND appa3.action_type = 'X'
AND appa3.action_status = 'C'
AND appa3.report_type = 'PYDKARCHIVE')
AND NOT EXISTS ( SELECT /* + ORDERED */ NULL
FROM pay_action_interlocks pai1,
pay_assignment_actions act2,
pay_payroll_actions appa3
WHERE pai1.locked_action_id = act.assignment_action_id
AND act2.assignment_action_id= pai1.locking_action_id
AND act2.payroll_action_id = appa3.payroll_action_id
AND appa3.action_type = 'V'
AND appa3.action_status = 'C')
ORDER BY act.assignment_id;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
SELECT distinct prepay_payact.payroll_action_id prepay_payact_id
,run_payact.date_earned date_earned
FROM pay_action_interlocks archive_intlck
,pay_assignment_actions prepay_assact
,pay_payroll_actions prepay_payact
,pay_action_interlocks prepay_intlck
,pay_assignment_actions run_assact
,pay_payroll_actions run_payact
,pay_assignment_actions archive_assact
WHERE archive_intlck.locking_action_id = archive_assact.assignment_action_id
and archive_assact.payroll_action_id = p_payroll_action_id
AND prepay_assact.assignment_action_id = archive_intlck.locked_action_id
AND prepay_payact.payroll_action_id = prepay_assact.payroll_action_id
AND prepay_payact.action_type IN ('U','P')
AND prepay_intlck.locking_action_id = prepay_assact.assignment_action_id
AND run_assact.assignment_action_id = prepay_intlck.locked_action_id
AND run_payact.payroll_action_id = run_assact.payroll_action_id
AND run_payact.action_type IN ('Q', 'R')
ORDER BY prepay_payact.payroll_action_id;
SELECT distinct prepay_payact.payroll_action_id prepay_payact_id
,run_payact.date_earned date_earned
,run_payact.payroll_action_id run_payact_id
FROM pay_action_interlocks archive_intlck
,pay_assignment_actions prepay_assact
,pay_payroll_actions prepay_payact
,pay_action_interlocks prepay_intlck
,pay_assignment_actions run_assact
,pay_payroll_actions run_payact
,pay_assignment_actions archive_assact
WHERE archive_intlck.locking_action_id = archive_assact.assignment_action_id
and archive_assact.payroll_action_id = p_payroll_action_id
AND prepay_assact.assignment_action_id = archive_intlck.locked_action_id
AND prepay_payact.payroll_action_id = prepay_assact.payroll_action_id
AND prepay_payact.action_type IN ('U','P')
AND prepay_intlck.locking_action_id = prepay_assact.assignment_action_id
AND run_assact.assignment_action_id = prepay_intlck.locked_action_id
AND run_payact.payroll_action_id = run_assact.payroll_action_id
AND run_payact.action_type IN ('Q', 'R')
ORDER BY prepay_payact.payroll_action_id;
SELECT territory_short_name
FROM fnd_territories_vl
WHERE territory_code = p_territory_code;
SELECT ppf.person_id person_id,
ppf.full_name full_name,
ppf.national_identifier ni_number,
ppf.nationality nationality,
pps.date_start start_date,
ppf.employee_number emp_num,
ppf.first_name first_name,
ppf.last_name last_name,
ppf.title title,
paf.location_id loc_id,
paf.organization_id org_id, -- HR Org at Asg level
paf.job_id job_id,
paf.position_id pos_id,
paf.grade_id grade_id,
paf.business_group_id bus_grp_id
FROM per_assignments_f paf,
per_all_people_f ppf,
per_periods_of_service pps
WHERE paf.person_id = ppf.person_id
AND paf.assignment_id = p_assignment_id
AND pps.person_id = ppf.person_id
AND p_date_earned BETWEEN paf.effective_start_date AND paf.effective_end_date
AND p_date_earned BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
SELECT pa.person_id person_id,
pa.style style,
pa.address_type ad_type,
pa.country country,
pa.region_1 R1,
pa.region_2 R2,
pa.region_3 R3,
pa.town_or_city city,
pa.address_line1 AL1,
pa.address_line2 AL2,
pa.address_line3 AL3,
pa.postal_code postal_code
FROM per_addresses pa
WHERE pa.primary_flag = 'Y'
AND pa.person_id = p_person_id
AND p_effective_date BETWEEN pa.date_from AND NVL(pa.date_to,to_date('31-12-4712','DD-MM-YYYY'));
SELECT hla.style style
,hla.country country
,hla.address_line_1 AL1
,hla.address_line_2 AL2
,hla.address_line_3 AL3
,hla.postal_code postal_code
FROM hr_locations_all hla
,hr_organization_units hou
WHERE hou.organization_id = p_organization_id
AND hou.location_id = hla.location_id;
SELECT hla.style style
,hla.address_line_1 AL1
,hla.address_line_2 AL2
,hla.address_line_3 AL3
,hla.country country
,hla.postal_code postal_code
FROM hr_locations_all hla,
hr_organization_units hoa
WHERE hla.location_id = hoa.location_id
AND hoa.organization_id = p_organization_id
AND p_effective_date BETWEEN hoa.date_from AND NVL(hoa.date_to,to_date('31-12-4712','DD-MM-YYYY'));
SELECT business_group_id
FROM hr_organization_units
WHERE organization_id = p_organization_id;
SELECT org_information10
FROM hr_organization_information
WHERE organization_id = p_bg_id
AND org_information_context = 'Business Group Information';
SELECT hoi3.organization_id
FROM HR_ORGANIZATION_UNITS o1
, HR_ORGANIZATION_INFORMATION hoi1
, HR_ORGANIZATION_INFORMATION hoi2
, HR_ORGANIZATION_INFORMATION hoi3
WHERE o1.business_group_id =l_bg_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = p_organization_id
AND hoi1.org_information1 = 'DK_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id = hoi2.org_information1
AND hoi2.ORG_INFORMATION_CONTEXT='DK_LOCAL_UNITS'
AND hoi2.organization_id = hoi3.organization_id
AND hoi3.ORG_INFORMATION_CONTEXT='CLASS'
AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER';
SELECT pg.name
FROM per_grades pg
WHERE pg.grade_id = p_grade_id;
SELECT pap.name
FROM per_all_positions pap
WHERE pap.position_id = p_position_id;
SELECT name
FROM per_jobs
WHERE job_id = p_job_id;
SELECT concatenated_segments
FROM pay_cost_allocations_v
WHERE assignment_id=p_assignment_id
AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
SELECT location_code location
FROM hr_locations_all
WHERE location_id = p_location_id;
SELECT date_start
FROM per_periods_of_service pps,
per_all_assignments_f paa
WHERE pps.period_of_service_id = paa.period_of_service_id
AND p_date_earned between paa.effective_start_date and paa.effective_end_date
AND paa.assignment_id = p_assignment_id;
SELECT segment2
from per_all_assignments_f paaf
,HR_SOFT_CODING_KEYFLEX hsck
where paaf.assignment_id= p_assignment_id
and p_date_earned BETWEEN paaf.effective_start_date and paaf.effective_end_date
and paaf.SOFT_CODING_KEYFLEX_ID = hsck.SOFT_CODING_KEYFLEX_ID;
SELECT segment1
from per_all_assignments_f paaf
,HR_SOFT_CODING_KEYFLEX hsck
where paaf.assignment_id= p_assignment_id
and p_date_earned BETWEEN paaf.effective_start_date and paaf.effective_end_date
and paaf.SOFT_CODING_KEYFLEX_ID = hsck.SOFT_CODING_KEYFLEX_ID;
SELECT org_information3 le_phone_num
FROM hr_organization_information hoi
WHERE organization_id = l_legal_employer_id
AND org_information_context = 'ORG_CONTACT_DETAILS'
AND org_information1 = 'PHONE' ;
SELECT ORG_INFORMATION1
FROM hr_organization_information hoi
WHERE organization_id = l_legal_employer_id
AND org_information_context = 'DK_LEGAL_ENTITY_DETAILS' ;
SELECT name
FROM hr_organization_units
WHERE organization_id = org_id ;
SELECT name INTO l_organization
FROM hr_organization_units
WHERE organization_id = rec_person_details.org_id;
SELECT name INTO l_employer_name
FROM hr_organization_units
WHERE organization_id = l_legal_employer_id;
/* INSERT PERSON DETAILS */
pay_action_information_api.create_action_information (
p_action_information_id => l_action_info_id
,p_action_context_id => p_archive_assact_id
,p_action_context_type => 'AAP'
,p_object_version_number => l_ovn
,p_effective_date => p_effective_date
,p_source_id => NULL
,p_source_text => NULL
,p_action_information_category => 'EMPLOYEE DETAILS'
,p_action_information1 => rec_person_details.full_name
,p_action_information2 => l_legal_employer_id -- rec_person_details.org_id -- org_id of HR Org at asg level
,p_action_information4 => rec_person_details.ni_number
,p_action_information7 => l_grade
,p_action_information10 => rec_person_details.emp_num
,p_action_information12 => fnd_date.date_to_displaydate(l_hire_date) -- fnd_date.date_to_canonical(l_hire_date)
,p_action_information15 => l_organization -- name of HR Org at asg level
,p_action_information16 => p_time_period_id
,p_action_information17 => l_job
,p_action_information18 => l_employer_name -- Legal Employer Name
,p_action_information19 => l_position
,p_action_information25 => le_phone_num_str
,p_action_information30 => l_pay_location
,p_assignment_id => p_assignment_id);
/* INSERT ADDRESS DETAILS */
IF rec_primary_address.AL1 IS NOT NULL THEN /* CHECK IF EMPLOYEE HAS BEEN GIVEN A PRIMARY ADDRESS */
pay_action_information_api.create_action_information (
p_action_information_id => l_action_info_id
,p_action_context_id => p_archive_assact_id
,p_action_context_type => 'AAP'
,p_object_version_number => l_ovn
,p_effective_date => p_effective_date
,p_source_id => NULL
,p_source_text => NULL
,p_action_information_category => 'ADDRESS DETAILS'
,p_action_information1 => rec_primary_address.person_id
,p_action_information5 => rec_primary_address.AL1
,p_action_information6 => rec_primary_address.AL2
,p_action_information7 => rec_primary_address.AL3
,p_action_information12 => l_postal_code
,p_action_information13 => l_country
,p_action_information14 => 'Employee Address'
,p_assignment_id => p_assignment_id);
/* INSERT EMPLOYER ADDRESS AS EMPLOYEE'S PRIMARY ADDRESS */
pay_action_information_api.create_action_information (
p_action_information_id => l_action_info_id
,p_action_context_id => p_archive_assact_id
,p_action_context_type => 'AAP'
,p_object_version_number => l_ovn
,p_effective_date => p_effective_date
,p_source_id => NULL
,p_source_text => NULL
,p_action_information_category => 'ADDRESS DETAILS'
,p_action_information1 => rec_primary_address.person_id
,p_action_information5 => NULL
,p_action_information6 => NULL
,p_action_information7 => NULL
,p_action_information8 => NULL
,p_action_information9 => NULL
,p_action_information10 => NULL
,p_action_information11 => NULL
,p_action_information12 => NULL
,p_action_information13 => NULL
,p_action_information14 => 'Employee Address'
,p_assignment_id => p_assignment_id);
/* INSERT EMPLOYER'S ADDRESS (ORGANIZATION ADDRESS)*/
BEGIN
l_org_exists := 0;
SELECT 1
INTO l_org_exists
FROM pay_action_information
WHERE action_context_id = p_payroll_action_id
AND action_information1 = l_legal_employer_id -- rec_person_details.org_id
AND effective_date = p_effective_date
AND action_information_category = 'ADDRESS DETAILS';
SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
,et.element_type_id element_type_id
,iv.input_value_id input_value_id
,iv.uom uom
FROM pay_element_types_f et
, pay_element_types_f_tl pettl
, pay_input_values_f iv
, pay_element_classifications classification
WHERE et.element_type_id = iv.element_type_id
AND et.element_type_id = pettl.element_type_id
AND pettl.language = USERENV('LANG')
AND iv.name = 'Pay Value'
AND classification.classification_id = et.classification_id
AND classification.classification_name
IN ('Direct Payments','Income','Special Pay')
AND p_date_earned BETWEEN et.effective_start_date AND et.effective_end_date
AND p_date_earned BETWEEN iv.effective_start_date AND iv.effective_end_date
AND ((et.business_group_id IS NULL AND et.legislation_code = 'DK')
OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));
SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
,et.element_type_id element_type_id
,iv.input_value_id input_value_id
,iv.uom uom
FROM pay_element_types_f et
, pay_element_types_f_tl pettl
, pay_input_values_f iv
, pay_element_classifications classification
WHERE et.element_type_id = iv.element_type_id
AND et.element_type_id = pettl.element_type_id
AND pettl.language = USERENV('LANG')
AND iv.name = 'Pay Value'
AND classification.classification_id = et.classification_id
AND classification.classification_name
IN ('Involuntary Deductions','Pre-Tax Deductions','Statutory Deductions','Voluntary Deductions')
AND p_date_earned BETWEEN et.effective_start_date AND et.effective_end_date
AND p_date_earned BETWEEN iv.effective_start_date AND iv.effective_end_date
AND ((et.business_group_id IS NULL AND et.legislation_code = 'DK')
OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));
SELECT 1 INTO l_flag
FROM pay_action_information
WHERE action_context_id = p_payroll_action_id
AND action_information_category = 'EMEA ELEMENT DEFINITION'
AND action_information2 = rec_earnings.element_type_id
AND action_information3 = rec_earnings.input_value_id
AND action_information5 = 'E';
SELECT 1 INTO l_flag
FROM pay_action_information
WHERE action_context_id = p_payroll_action_id
AND action_information_category = 'EMEA ELEMENT DEFINITION'
AND action_information2 = rec_deduction.element_type_id
AND action_information3 = rec_deduction.input_value_id
AND action_information5 = 'D';
SELECT u.creator_id
FROM ff_user_entities u,
ff_database_items d
WHERE d.user_name = p_user_name
AND u.user_entity_id = d.user_entity_id
AND (u.legislation_code = 'DK' )
AND (u.business_group_id IS NULL )
AND u.creator_type = 'B';
SELECT personal_payment_method_id ppm_id,
org_payment_method_id opm_id
FROM pay_pre_payments
WHERE assignment_action_id = p_prepay_assact_id;
SELECT ppm.external_account_id
FROM pay_personal_payment_methods_f ppm
WHERE ppm.personal_payment_method_id = p_ppm_id
AND p_date_earned BETWEEN ppm.effective_start_date AND ppm.effective_end_date;
SELECT pop.org_payment_method_id opm_id,
pop.org_payment_method_name opm_name,
ppttl.payment_type_name pay_type,
ppp.value value
FROM pay_org_payment_methods_f pop,
pay_assignment_actions paa,
pay_payment_types ppt,
pay_payment_types_tl ppttl,
pay_pre_payments ppp
WHERE paa.assignment_action_id = p_prepay_assact_id
AND ppt.payment_type_id = pop.payment_type_id
AND ppt.payment_type_id = ppttl.payment_type_id
AND ppttl.language = userenv('LANG')
AND ppp.org_payment_method_id = pop.org_payment_method_id
AND pop.org_payment_method_id = opm_id
AND ppp.assignment_action_id = paa.assignment_action_id
AND p_date_earned BETWEEN pop.effective_start_date AND pop.effective_end_date;
SELECT pea.segment1 bank_reg_num,
pea.segment2 branch,
pea.segment3 acct_num,
ppm.org_payment_method_id opm_id,
pop.external_account_id,
pop.org_payment_method_name opm_name,
ppm.personal_payment_method_id ppm_id,
ppttl.payment_type_name pay_type,
ppp.value value
FROM pay_external_accounts pea,
pay_org_payment_methods_f pop,
pay_personal_payment_methods_f ppm,
pay_assignment_actions paa,
pay_payment_types ppt,
pay_payment_types_tl ppttl,
pay_pre_payments ppp
WHERE
-- pea.id_flex_num=20 AND
pea.external_account_id = NVL(ppm.external_account_id,pop.external_account_id)
AND paa.assignment_action_id = p_prepay_assact_id
AND paa.assignment_id = ppm.assignment_id
AND ppm.org_payment_method_id = pop.org_payment_method_id
AND ppm.personal_payment_method_id = ppm_id
AND ppt.payment_type_id = pop.payment_type_id
AND ppt.payment_type_id = ppttl.payment_type_id
AND ppttl.language = userenv('LANG')
AND ppp.assignment_action_id = paa.assignment_action_id
AND ppp.personal_payment_method_id = ppm.personal_payment_method_id
AND p_date_earned BETWEEN pop.effective_start_date AND pop.effective_end_date
AND p_date_earned BETWEEN ppm.effective_start_date AND ppm.effective_end_date;
SELECT pap.accrual_plan_name
,hr_general_utilities.get_lookup_meaning('US_PTO_ACCRUAL',pap.accrual_category)
,pap.accrual_units_of_measure
,ppa.payroll_id
,pap.business_group_id
,pap.accrual_plan_id
FROM pay_accrual_plans pap
,pay_element_types_f pet
,pay_element_links_f pel
,pay_element_entries_f pee
,pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE pet.element_type_id = pap.accrual_plan_element_type_id
AND pel.element_type_id = pet.element_type_id
AND pee.element_link_id = pel.element_link_id
AND paa.assignment_id = pee.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ('R','Q')
AND ppa.action_status = 'C'
AND ppa.date_earned BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND ppa.date_earned BETWEEN pel.effective_start_date
AND pel.effective_end_date
AND ppa.date_earned BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND paa.assignment_id = p_assignment_id
AND paa.assignment_action_id = p_run_assignment_action_id;
SELECT hoi.org_information2 element_type_id
,hoi.org_information3 input_value_id
,hoi.org_information7 element_narrative
,pec.classification_name
,piv.uom
FROM hr_organization_information hoi
,pay_element_classifications pec
,pay_element_types_f pet
,pay_input_values_f piv
WHERE hoi.organization_id = p_bus_grp_id
AND hoi.org_information_context = 'Business Group:Payslip Info'
AND hoi.org_information1 = 'ELEMENT'
AND hoi.org_information2 = pet.element_type_id
AND pec.classification_id = pet.classification_id
AND piv.input_value_id = hoi.org_information3
AND p_date_earned BETWEEN piv.effective_start_date AND piv.effective_end_date;
SELECT rrv.result_value
FROM pay_run_result_values rrv
,pay_run_results rr
,pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE rrv.input_value_id = p_iv_id
AND rr.element_type_id = p_ele_type_id
AND rr.run_result_id = rrv.run_result_id
AND rr.assignment_action_id = paa.assignment_action_id
AND paa.assignment_action_id = p_assignment_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ('Q','R')
AND rrv.result_value IS NOT NULL;
SELECT org_information4 balance_type_id
,org_information5 balance_dim_id
,org_information7 narrative
FROM hr_organization_information
WHERE organization_id = p_bus_grp_id
AND org_information_context = 'Business Group:Payslip Info'
AND org_information1 = 'BALANCE';
SELECT paa.tax_unit_id
FROM pay_assignment_actions paa
WHERE paa.assignment_action_id = p_run_assact_id;
SELECT defined_balance_id
FROM pay_defined_balances
WHERE balance_type_id = bal_type_id
AND balance_dimension_id = bal_dim_id;
SELECT prepay_assact.assignment_action_id prepay_assact_id
,prepay_assact.assignment_id prepay_assgt_id
,prepay_payact.payroll_action_id prepay_payact_id
,prepay_payact.effective_date prepay_effective_date
,run_assact.assignment_id run_assgt_id
,run_assact.assignment_action_id run_assact_id
,run_payact.payroll_action_id run_payact_id
,run_payact.payroll_id payroll_id
FROM pay_action_interlocks archive_intlck
,pay_assignment_actions prepay_assact
,pay_payroll_actions prepay_payact
,pay_action_interlocks prepay_intlck
,pay_assignment_actions run_assact
,pay_payroll_actions run_payact
WHERE archive_intlck.locking_action_id = p_locking_action_id
AND prepay_assact.assignment_action_id = archive_intlck.locked_action_id
AND prepay_payact.payroll_action_id = prepay_assact.payroll_action_id
AND prepay_payact.action_type IN ('U','P')
AND prepay_intlck.locking_action_id = prepay_assact.assignment_action_id
AND run_assact.assignment_action_id = prepay_intlck.locked_action_id
AND run_payact.payroll_action_id = run_assact.payroll_action_id
AND run_payact.action_type IN ('Q', 'R')
ORDER BY prepay_intlck.locking_action_id,prepay_intlck.locked_action_id desc;
SELECT ptp.end_date end_date,
ptp.regular_payment_date regular_payment_date,
ptp.time_period_id time_period_id,
ppa.date_earned date_earned,
ppa.effective_date effective_date,
ptp.start_date start_date
FROM per_time_periods ptp
,pay_payroll_actions ppa
,pay_assignment_actions paa
WHERE ptp.payroll_id =ppa.payroll_id
AND ppa.payroll_action_id =paa.payroll_action_id
AND paa.assignment_action_id =p_assact_id
AND ppa.payroll_action_id =p_pay_act_id
AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date;
SELECT payroll_action_id
FROM pay_assignment_actions
WHERE assignment_Action_id = p_assignment_action_id;
SELECT actual_termination_date
FROM per_periods_of_service pps,
per_all_assignments_f paa
WHERE pps.period_of_service_id = paa.period_of_service_id
AND p_date_earned between paa.effective_start_date and paa.effective_end_date
AND paa.assignment_id = p_assignment_id;
SELECT ee.effective_start_date effective_start_date
,eev1.screen_entry_value screen_entry_value
FROM per_all_assignments_f asg1
,per_all_assignments_f asg2
,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 p_date_earned BETWEEN asg1.effective_start_date AND asg1.effective_end_date
AND p_date_earned BETWEEN asg2.effective_start_date AND asg2.effective_end_date
AND per.person_id = asg1.person_id
AND asg2.person_id = per.person_id
AND asg2.primary_flag = 'Y'
AND et.element_name = 'Tax Card'
AND et.legislation_code = 'DK'
AND iv1.element_type_id = et.element_type_id
AND iv1.name = p_input_value
AND el.business_group_id = per.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = asg2.assignment_id
AND ee.element_link_id = el.element_link_id
AND eev1.element_entry_id = ee.element_entry_id
AND eev1.input_value_id = iv1.input_value_id
AND p_date_earned BETWEEN ee.effective_start_date AND ee.effective_end_date
AND p_date_earned BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
SELECT ee.effective_start_date
,eev1.screen_entry_value screen_entry_value
FROM per_all_assignments_f asg1
,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 p_date_earned BETWEEN asg1.effective_start_date AND asg1.effective_end_date
AND et.element_name = 'Tax'
AND et.legislation_code = 'DK'
AND iv1.element_type_id = et.element_type_id
AND iv1.name = p_input_value
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = asg1.assignment_id
AND ee.element_link_id = el.element_link_id
AND eev1.element_entry_id = ee.element_entry_id
AND eev1.input_value_id = iv1.input_value_id
AND p_date_earned BETWEEN ee.effective_start_date AND ee.effective_end_date
AND p_date_earned BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
SELECT segment13
FROM per_all_assignments_f paa,
hr_soft_coding_keyflex hsc
WHERE paa.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
AND p_date_earned BETWEEN paa.effective_start_date AND paa.effective_end_date
AND paa.assignment_id = p_assignment_id;
SELECT global_value
FROM ff_globals_f
WHERE global_name = p_global_name
AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
SELECT PAY_DK_ARCHIVE.GET_PARAMETER(legislative_parameters,'PAYROLL_ID')
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id ;
SELECT payroll_name , period_type
FROM pay_all_payrolls_f
WHERE payroll_id = l_payroll_id ;
/*SELECT decode (l_period_type, 'Calendar Month', l_income_from_hol_Pay_sal, l_income_from_hol_Pay_hr)
INTO l_income_from_hol_Pay
FROM dual ;*/
SELECT decode (l_hourly_salaried, 'S', l_income_from_hol_Pay_sal, l_income_from_hol_Pay_hr)
INTO l_income_from_hol_Pay
FROM dual ;
SELECT decode (l_period_type
,'Calendar Month',g_tax_card_tab(5).screen_entry_val
,'Bi-Week',g_tax_card_tab(6).screen_entry_val
,'Week',g_tax_card_tab(7).screen_entry_val
,'Lunar Month',g_tax_card_tab(5).screen_entry_val) /* Changes for Lunar Payroll*/
INTO l_tax_deduction
FROM dual ;
/*SELECT decode (l_period_type ,
'Calendar Month',
g_bal_val(9).bal_val ,
g_bal_val(18).bal_val)
INTO l_calc_holiday_pay_asg_ytd
FROM dual ;*/
SELECT decode (l_hourly_salaried, 'S',
g_bal_val(9).bal_val ,
g_bal_val(18).bal_val)
INTO l_calc_holiday_pay_asg_ytd
FROM dual ;
SELECT decode (l_period_type ,
'Calendar Month',
707 ,
808 )
INTO l_calc_holiday_pay_asg_ytd
FROM dual ;
SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
,et.element_type_id element_type_id
,iv.input_value_id input_value_id
,iv.uom uom
,et.processing_priority processing_priority
FROM pay_element_types_f et
, pay_element_types_f_tl pettl
, pay_input_values_f iv
, pay_element_classifications classification
WHERE et.element_type_id = iv.element_type_id
AND et.element_type_id = pettl.element_type_id
AND pettl.language = USERENV('LANG')
AND iv.name = 'Pay Value'
AND classification.classification_id = et.classification_id
AND classification.classification_name
IN ('Direct Payments','Income','Special Pay')
AND p_date_earned BETWEEN et.effective_start_date AND et.effective_end_date
AND p_date_earned BETWEEN iv.effective_start_date AND iv.effective_end_date
AND ((et.business_group_id IS NULL AND et.legislation_code = 'DK')
OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));
SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
,et.element_type_id element_type_id
,iv.input_value_id input_value_id
,iv.uom uom
,et.processing_priority processing_priority
FROM pay_element_types_f et
, pay_element_types_f_tl pettl
, pay_input_values_f iv
, pay_element_classifications classification
WHERE et.element_type_id = iv.element_type_id
AND et.element_type_id = pettl.element_type_id
AND pettl.language = USERENV('LANG')
AND iv.name = 'Pay Value'
AND classification.classification_id = et.classification_id
AND classification.classification_name
IN ('Involuntary Deductions','Pre-Tax Deductions','Statutory Deductions','Voluntary Deductions')
AND p_date_earned BETWEEN et.effective_start_date AND et.effective_end_date
AND p_date_earned BETWEEN iv.effective_start_date AND iv.effective_end_date
AND ((et.business_group_id IS NULL AND et.legislation_code = 'DK')
OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));
SELECT rrv.result_value
/* Added for Pension changes */
, rr.run_result_id
FROM pay_run_result_values rrv
,pay_run_results rr
,pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE rrv.input_value_id = p_iv_id
AND rr.element_type_id = p_ele_type_id
AND rr.run_result_id = rrv.run_result_id
AND rr.assignment_action_id = paa.assignment_action_id
AND paa.assignment_action_id = p_assignment_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ('Q','R')
AND rrv.result_value IS NOT NULL;
SELECT pai.action_information_id
FROM pay_action_information pai
WHERE pai.action_context_type = 'PA'
AND pai.effective_date = p_effective_date
AND pai.action_information_category = 'EMEA ELEMENT DEFINITION'
AND pai.action_information2 = to_char(p_element_type_id)
AND pai.action_information3 = to_char(p_input_value_id)
AND pai.action_information5 = 'D'
AND pai.action_information7 = 'D';
SELECT piv.input_value_id
FROM pay_input_values_f piv
WHERE piv.element_type_id = p_element_type_id
AND piv.name= 'Third Party Payee'
AND p_effective_date between piv.effective_start_date and piv.effective_end_date;
SELECT hou.name
FROM
pay_run_result_values rrv
, pay_input_values_f iv
, hr_organization_units hou
WHERE rrv.run_result_id = p_run_result_id
AND rrv.input_value_id = iv.input_value_id
AND iv.name = 'Third Party Payee'
AND p_effective_date between
iv.effective_start_date and iv.effective_end_date
AND hou.organization_id = fnd_number.canonical_to_number(rrv.result_value)
AND p_effective_date between hou.date_from and nvl(hou.date_to, p_effective_date);
pay_action_information_api.update_action_information(
p_action_information_id => l_action_info_id_pen
,p_object_version_number => l_ovn_pen
,p_action_information4 => l_new_rep_name); */