[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PAY_FI_ARCHIVE_PYSA.GET_PARAMETER(legislative_parameters,'PAYROLL_ID')
,PAY_FI_ARCHIVE_PYSA.GET_PARAMETER(legislative_parameters,'CONSOLIDATION_SET_ID')
,PAY_FI_ARCHIVE_PYSA.GET_PARAMETER(legislative_parameters,'START_DATE')
,PAY_FI_ARCHIVE_PYSA.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';
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 IN ('C','S') -- 10229501
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') -- 10229501
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 (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 = 'FI_ARCHIVE')
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,
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 = 'FI_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id = hoi2.org_information1
AND hoi2.ORG_INFORMATION_CONTEXT='FI_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 trunc(date_start) 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 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
,p_action_information4 => rec_person_details.ni_number
,p_action_information7 => l_grade
,p_action_information10 => rec_person_details.emp_num
,p_action_information12 => to_char(trunc(l_hire_date))
,p_action_information15 => l_organization
,p_action_information16 => p_time_period_id
,p_action_information17 => l_job
,p_action_information18 => l_employer_name
,p_action_information19 => l_position
,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_person_details.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 = 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 ('Capital Income'
,'Compensation for Use of Item'
,'Compensation for Work'
,'Deductions Before Tax'
,'Direct Payment'
,'Holiday Bonus Pay'
,'Holiday Compensation'
,'Holiday Pay'
,'Other Payments Subject to Tax'
,'Salary in Money'
)
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 = 'FI')
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'
,'Voluntary Deductions'
,'Statutory Deductions'
,'VAT')
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 = 'FI')
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 = 'FI' )
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 name_id,
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.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 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
,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 p_date_earned BETWEEN per.effective_start_date AND per.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 = 'FI'
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 = 'FI'
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 attribute_id
FROM pay_bal_attribute_definitions
WHERE legislation_code='FI'
AND attribute_name= p_attribute_name;
SELECT defined_balance_id
FROM pay_balance_attributes
WHERE attribute_id= p_attribute_id;
/*Unemployment code to be inserted here*/
l_defined_balance_id := GET_DEFINED_BALANCE_ID('UNEMPLOYMENT_INSURANCE_ASG_PTD');
/* Holiday Bonus Pay to be inserted here*/
l_defined_balance_id := GET_DEFINED_BALANCE_ID('HOLIDAY_BONUS_PAY_ASG_PTD');
/* Holiday Compensation to be inserted here*/
l_defined_balance_id := GET_DEFINED_BALANCE_ID('HOLIDAY_COMPENSATION_ASG_PTD');
/* Holiday Pay Details to be inserted here*/
BEGIN
l_defined_balance_id := GET_DEFINED_BALANCE_ID('SUMMER_HOLIDAY_DAYS_ENTITLEMENT_ASG_BD_HOL_YTD');
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 paa.tax_unit_id
FROM pay_assignment_actions paa
WHERE paa.assignment_action_id = p_run_assact_id;
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 ('Capital Income'
,'Compensation for Use of Item'
,'Compensation for Work'
,'Deductions Before Tax'
,'Direct Payment'
,'Holiday Bonus Pay'
,'Holiday Compensation'
,'Holiday Pay'
,'Other Payments Subject to Tax'
,'Salary in Money'
)
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 = 'FI')
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'
,'Voluntary Deductions'
,'Statutory Deductions'
,'VAT')
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 = 'FI')
OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));
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 DISTINCT segment2 local_unit , paaf.business_group_id
FROM per_all_assignments_f paaf
,HR_SOFT_CODING_KEYFLEX hsck
WHERE p_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paaf.SOFT_CODING_KEYFLEX_ID = hsck.SOFT_CODING_KEYFLEX_ID
AND paaf.assignment_id IN
(SELECT DISTINCT assignment_id
FROM pay_assignment_actions
WHERE payroll_action_id= p_payroll_action_id );
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 =p_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = p_organization_id
AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id = hoi2.org_information1
AND hoi2.ORG_INFORMATION_CONTEXT='FI_LOCAL_UNITS'
AND hoi2.organization_id = hoi3.organization_id
AND hoi3.ORG_INFORMATION_CONTEXT='CLASS'
AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER';
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 effective_date
FROM pay_payroll_actions
WHERE payroll_action_id= p_payroll_action_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 = csr_legal_emp_rec.organization_id
AND effective_date = l_effective_date
AND action_information_category = 'ADDRESS DETAILS';