[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PAY_NO_PAYSLIP_ARCHIVE.GET_PARAMETER(legislative_parameters,'PAYROLL_ID')
,PAY_NO_PAYSLIP_ARCHIVE.GET_PARAMETER(legislative_parameters,'CONSOLIDATION_SET_ID')
,PAY_NO_PAYSLIP_ARCHIVE.GET_PARAMETER(legislative_parameters,'START_DATE')
,PAY_NO_PAYSLIP_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 ele_class
,piv.uom 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 hoi.org_information2 element_type_id
,hoi.org_information3 input_value_id
,hoi.org_information7 element_narrative
,pec.classification_name ele_class
,piv.uom uom
,piv.lookup_type lookup_type
,piv.value_set_id value_set_id
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 balance_type_id
FROM pay_balance_types
WHERE input_value_id = p_iv_id
AND ((business_group_id IS NULL AND legislation_code = 'NO')
OR (business_group_id = g_business_group_id AND legislation_code IS NULL))
AND rownum = 1 ;
SELECT defined_balance_id
FROM pay_defined_balances
WHERE balance_type_id = p_prim_bal_type_id
AND ((business_group_id IS NULL AND legislation_code = 'NO')
OR (business_group_id = g_business_group_id AND legislation_code IS NULL))
AND balance_dimension_id = ( select balance_dimension_id
from pay_balance_dimensions where legislation_code = 'NO'
and dimension_name = 'Assignment Calendar Year To Date' ) ;
SELECT eleclass.classification_name
,decode (eleclass.classification_name,'Taxable Pay _ Absence',1,'Taxable Pay _ Earnings',1
,'Taxable Pay _ Supplemental Earnings',1,'Taxable Pay _ Taxable Benefits',1,0) table_base
,decode (eleclass.classification_name,'Additional Taxable Pay _ Absence',2,'Additional Taxable Pay _ Earnings',2
,'Additional Taxable Pay _ Supplemental Earnings',2,'Additional Taxable Pay _ Taxable Benefits',2,0) percent_base
,decode (eleclass.classification_name,'Holiday Pay',3,0) holiday_pay
FROM pay_sub_classification_rules_f subclass
,pay_element_classifications eleclass
WHERE subclass.element_type_id = p_ele_type_id
AND p_date_earned BETWEEN subclass.effective_start_date AND subclass.effective_end_date
AND ((subclass.business_group_id IS NULL AND subclass.legislation_code = 'NO')
OR (subclass.business_group_id = g_business_group_id AND subclass.legislation_code IS NULL))
AND ((eleclass.business_group_id IS NULL AND eleclass.legislation_code = 'NO')
OR (eleclass.business_group_id = g_business_group_id AND eleclass.legislation_code IS NULL))
AND eleclass.classification_id = subclass.classification_id ;
SELECT 1
FROM pay_sub_classification_rules_f subclass
,pay_element_classifications eleclass
WHERE subclass.element_type_id = p_ele_type_id
AND eleclass.classification_name = p_ele_sub_class_name
AND p_date_earned BETWEEN subclass.effective_start_date AND subclass.effective_end_date
AND ((subclass.business_group_id IS NULL AND subclass.legislation_code = 'NO')
OR (subclass.business_group_id = g_business_group_id AND subclass.legislation_code IS NULL))
AND ((eleclass.business_group_id IS NULL AND eleclass.legislation_code = 'NO')
OR (eleclass.business_group_id = g_business_group_id AND eleclass.legislation_code IS NULL))
AND eleclass.classification_id = subclass.classification_id ;
SELECT 'YES'
FROM pay_sub_classification_rules_f subclass
,pay_element_classifications eleclass
WHERE subclass.element_type_id = p_ele_type_id
AND eleclass.classification_id = subclass.classification_id
AND eleclass.classification_name in ( 'Holiday Pay Base _ Absence',
'Holiday Pay Base _ Earnings',
'Holiday Pay Base _ Earnings Adjustment',
'Holiday Pay Base _ Holiday Pay Earnings Adjustment',
'Holiday Pay Base _ Supplementary Earnings')
AND p_date_earned BETWEEN subclass.effective_start_date AND subclass.effective_end_date
AND ((subclass.business_group_id IS NULL AND subclass.legislation_code = 'NO')
OR (subclass.business_group_id = g_business_group_id AND subclass.legislation_code IS NULL))
AND ((eleclass.business_group_id IS NULL AND eleclass.legislation_code = 'NO')
OR (eleclass.business_group_id = g_business_group_id AND eleclass.legislation_code IS NULL)) ;
SELECT 'YES'
FROM pay_sub_classification_rules_f subclass
,pay_element_classifications eleclass
WHERE subclass.element_type_id = p_ele_type_id
AND eleclass.classification_id = subclass.classification_id
AND eleclass.classification_name in ( 'Holiday Pay Base _ Absence',
'Holiday Pay Base _ Earnings',
'Holiday Pay Base _ Earnings Adjustment',
'Holiday Pay Base _ Holiday Pay Earnings Adjust',
'Holiday Pay Base _ Supplementary Earnings')
AND p_date_earned BETWEEN subclass.effective_start_date AND subclass.effective_end_date
AND ((subclass.business_group_id IS NULL AND subclass.legislation_code = 'NO')
OR (subclass.business_group_id = g_business_group_id AND subclass.legislation_code IS NULL))
AND ((eleclass.business_group_id IS NULL AND eleclass.legislation_code = 'NO')
OR (eleclass.business_group_id = g_business_group_id AND eleclass.legislation_code IS NULL)) ;
SELECT 'YES'
FROM pay_sub_classification_rules_f subclass
,pay_element_classifications eleclass
WHERE subclass.element_type_id = p_ele_type_id
AND eleclass.classification_id = subclass.classification_id
AND eleclass.classification_name in ( 'Holiday Pay Base _ Absence',
'Holiday Pay Base _ Earnings',
'Holiday Pay Base _ Earnings Adjustment',
'Holiday Pay Base _ Holiday Pay Earnings Adjust',
'Holiday Pay Base _ Supplementary Earnings',
'Holiday Pay Base During Absence _ Information')
AND p_date_earned BETWEEN subclass.effective_start_date AND subclass.effective_end_date
AND ((subclass.business_group_id IS NULL AND subclass.legislation_code = 'NO')
OR (subclass.business_group_id = g_business_group_id AND subclass.legislation_code IS NULL))
AND ((eleclass.business_group_id IS NULL AND eleclass.legislation_code = 'NO')
OR (eleclass.business_group_id = g_business_group_id AND eleclass.legislation_code IS NULL)) ;
SELECT 'YES'
FROM pay_sub_classification_rules_f subclass
,pay_element_classifications eleclass
WHERE subclass.element_type_id = p_ele_type_id
AND eleclass.classification_id = subclass.classification_id
AND eleclass.classification_name in ( 'Holiday Pay Base _ Absence',
'Holiday Pay Base _ Earnings',
'Holiday Pay Base _ Earnings Adjustment',
'Holiday Pay Base _ Supplementary Earnings',
'Holiday Pay Base During Absence _ Information')
AND p_date_earned BETWEEN subclass.effective_start_date AND subclass.effective_end_date
AND ((subclass.business_group_id IS NULL AND subclass.legislation_code = 'NO')
OR (subclass.business_group_id = g_business_group_id AND subclass.legislation_code IS NULL))
AND ((eleclass.business_group_id IS NULL AND eleclass.legislation_code = 'NO')
OR (eleclass.business_group_id = g_business_group_id AND eleclass.legislation_code IS NULL)) ;
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 = 'NO 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 = 'PYNOARCHIVE')
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,
paf.assignment_number asg_num
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 = 'NO_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id = hoi2.org_information1
AND hoi2.ORG_INFORMATION_CONTEXT='NO_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 ORG_INFORMATION1
from hr_organization_information
where organization_id = l_legal_employer_id
and ORG_INFORMATION_CONTEXT = 'NO_LEGAL_EMPLOYER_DETAILS' ;
SELECT name
FROM hr_organization_units
WHERE organization_id = p_org_name ;
SELECT asg2.assignment_id
FROM per_all_assignments_f asg1
,per_all_assignments_f asg2
WHERE asg1.assignment_id = p_asg_id
AND asg1.person_id = asg2.person_id
AND asg2.primary_flag = 'Y'
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 ;
/* 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 -- Legal Employer Org 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 => fnd_date.date_to_displaydate(l_hire_date) -- fnd_date.date_to_canonical(l_hire_date)
,p_action_information14 => rec_person_details.asg_num
,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 ('Absence','Direct Payments','Earnings','Supplementary Earnings')
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 = 'NO')
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 = 'NO')
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
,classification.classification_name ele_class
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 ('Earnings','Supplementary Earnings','Absence','Direct Payments','Pre-tax Deductions',
'Involuntary Deductions','Voluntary Deductions','Statutory Deductions','Reductions',
'Taxable Benefits','Benefits Not Taxed','Expenses Information','Taxable Expenses')
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 = 'NO')
OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL))
ORDER BY DECODE (classification.classification_name,
'Earnings',1,'Supplementary Earnings',2,'Absence',3,'Direct Payments',4,
'Pre-tax Deductions',5,'Involuntary Deductions',6,'Voluntary Deductions',7,
'Statutory Deductions',8,'Reductions',9,'Taxable Benefits',10,
'Benefits Not Taxed',11,'Expenses Information',12,'Taxable Expenses',13) ;
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
,classification.classification_name ele_class
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 = p_ele_class_name
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 = 'NO')
OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL)) ;
SELECT balance_type_id
FROM pay_balance_types
WHERE input_value_id = p_iv_id
AND ((business_group_id IS NULL AND legislation_code = 'NO')
OR (business_group_id = g_business_group_id AND legislation_code IS NULL))
AND rownum = 1 ;
SELECT defined_balance_id
FROM pay_defined_balances
WHERE balance_type_id = p_prim_bal_type_id
AND ((business_group_id IS NULL AND legislation_code = 'NO')
OR (business_group_id = g_business_group_id AND legislation_code IS NULL))
AND balance_dimension_id = ( select balance_dimension_id
from pay_balance_dimensions where legislation_code = 'NO'
and dimension_name = 'Assignment Calendar Year To Date' ) ;
SELECT eleclass.classification_name
,decode (eleclass.classification_name,'Taxable Pay _ Absence',1,'Taxable Pay _ Earnings',1
,'Taxable Pay _ Supplemental Earnings',1,'Taxable Pay _ Taxable Benefits',1,0) table_base
,decode (eleclass.classification_name,'Additional Taxable Pay _ Absence',2,'Additional Taxable Pay _ Earnings',2
,'Additional Taxable Pay _ Supplemental Earnings',2,'Additional Taxable Pay _ Taxable Benefits',2,0) percent_base
,decode (eleclass.classification_name,'Holiday Pay',3,0) holiday_pay
FROM pay_sub_classification_rules_f subclass
,pay_element_classifications eleclass
WHERE subclass.element_type_id = p_ele_type_id
AND p_date_earned BETWEEN subclass.effective_start_date AND subclass.effective_end_date
AND ((subclass.business_group_id IS NULL AND subclass.legislation_code = 'NO')
OR (subclass.business_group_id = g_business_group_id AND subclass.legislation_code IS NULL))
AND ((eleclass.business_group_id IS NULL AND eleclass.legislation_code = 'NO')
OR (eleclass.business_group_id = g_business_group_id AND eleclass.legislation_code IS NULL))
AND eleclass.classification_id = subclass.classification_id ;
SELECT 1
FROM pay_sub_classification_rules_f subclass
,pay_element_classifications eleclass
WHERE subclass.element_type_id = p_ele_type_id
AND eleclass.classification_name = p_ele_sub_class_name
AND p_date_earned BETWEEN subclass.effective_start_date AND subclass.effective_end_date
AND ((subclass.business_group_id IS NULL AND subclass.legislation_code = 'NO')
OR (subclass.business_group_id = g_business_group_id AND subclass.legislation_code IS NULL))
AND ((eleclass.business_group_id IS NULL AND eleclass.legislation_code = 'NO')
OR (eleclass.business_group_id = g_business_group_id AND eleclass.legislation_code IS NULL))
AND eleclass.classification_id = subclass.classification_id ;
SELECT 'YES'
FROM pay_sub_classification_rules_f subclass
,pay_element_classifications eleclass
WHERE subclass.element_type_id = p_ele_type_id
AND eleclass.classification_id = subclass.classification_id
AND eleclass.classification_name in ( 'Holiday Pay Base _ Absence',
'Holiday Pay Base _ Earnings',
'Holiday Pay Base _ Earnings Adjustment',
'Holiday Pay Base _ Holiday Pay Earnings Adjustment',
'Holiday Pay Base _ Supplementary Earnings')
AND p_date_earned BETWEEN subclass.effective_start_date AND subclass.effective_end_date
AND ((subclass.business_group_id IS NULL AND subclass.legislation_code = 'NO')
OR (subclass.business_group_id = g_business_group_id AND subclass.legislation_code IS NULL))
AND ((eleclass.business_group_id IS NULL AND eleclass.legislation_code = 'NO')
OR (eleclass.business_group_id = g_business_group_id AND eleclass.legislation_code IS NULL)) ;
SELECT 'YES'
FROM pay_sub_classification_rules_f subclass
,pay_element_classifications eleclass
WHERE subclass.element_type_id = p_ele_type_id
AND eleclass.classification_id = subclass.classification_id
AND eleclass.classification_name in ( 'Holiday Pay Base _ Absence',
'Holiday Pay Base _ Earnings',
'Holiday Pay Base _ Earnings Adjustment',
'Holiday Pay Base _ Holiday Pay Earnings Adjust',
'Holiday Pay Base _ Supplementary Earnings')
AND p_date_earned BETWEEN subclass.effective_start_date AND subclass.effective_end_date
AND ((subclass.business_group_id IS NULL AND subclass.legislation_code = 'NO')
OR (subclass.business_group_id = g_business_group_id AND subclass.legislation_code IS NULL))
AND ((eleclass.business_group_id IS NULL AND eleclass.legislation_code = 'NO')
OR (eleclass.business_group_id = g_business_group_id AND eleclass.legislation_code IS NULL)) ;
SELECT 'YES'
FROM pay_sub_classification_rules_f subclass
,pay_element_classifications eleclass
WHERE subclass.element_type_id = p_ele_type_id
AND eleclass.classification_id = subclass.classification_id
AND eleclass.classification_name in ( 'Holiday Pay Base _ Absence',
'Holiday Pay Base _ Earnings',
'Holiday Pay Base _ Earnings Adjustment',
'Holiday Pay Base _ Holiday Pay Earnings Adjust',
'Holiday Pay Base _ Supplementary Earnings',
'Holiday Pay Base During Absence _ Information')
AND p_date_earned BETWEEN subclass.effective_start_date AND subclass.effective_end_date
AND ((subclass.business_group_id IS NULL AND subclass.legislation_code = 'NO')
OR (subclass.business_group_id = g_business_group_id AND subclass.legislation_code IS NULL))
AND ((eleclass.business_group_id IS NULL AND eleclass.legislation_code = 'NO')
OR (eleclass.business_group_id = g_business_group_id AND eleclass.legislation_code IS NULL)) ;
SELECT 'YES'
FROM pay_sub_classification_rules_f subclass
,pay_element_classifications eleclass
WHERE subclass.element_type_id = p_ele_type_id
AND eleclass.classification_id = subclass.classification_id
AND eleclass.classification_name in ( 'Holiday Pay Base _ Absence',
'Holiday Pay Base _ Earnings',
'Holiday Pay Base _ Earnings Adjustment',
'Holiday Pay Base _ Supplementary Earnings',
'Holiday Pay Base During Absence _ Information')
AND p_date_earned BETWEEN subclass.effective_start_date AND subclass.effective_end_date
AND ((subclass.business_group_id IS NULL AND subclass.legislation_code = 'NO')
OR (subclass.business_group_id = g_business_group_id AND subclass.legislation_code IS NULL))
AND ((eleclass.business_group_id IS NULL AND eleclass.legislation_code = 'NO')
OR (eleclass.business_group_id = g_business_group_id AND eleclass.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 = 'NO ELEMENT DEFINITION'
AND action_context_type = 'PA'
AND action_information2 = rec_earnings.element_type_id
AND action_information3 = rec_earnings.input_value_id
AND action_information5 = ele_class_table(l_index).ele_context ;
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 = 'NO' )
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_name,
pea.segment2 branch,
pea.segment3 acct_name,
pea.segment4 acc_type,
pea.segment5 acc_curr,
pea.segment6 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 action_information2 element_type_id
,action_information3 input_value_id
,action_information4 element_narrative
,action_information5 element_context
,action_information6 uom
,action_information8 prim_def_bal_id
,action_information12 ele_class
FROM pay_action_information
WHERE action_context_id = p_payroll_action_id
AND action_information_category = 'NO ELEMENT DEFINITION'
AND action_context_type = 'PA'
AND action_information5 = 'F' ;
SELECT action_information2 element_type_id
,action_information3 input_value_id
,action_information4 element_narrative
,action_information5 element_context
,action_information6 uom
,action_information8 prim_def_bal_id
,action_information12 ele_class
,action_information13 lookup_type
,action_information14 value_set_id
FROM pay_action_information
WHERE action_context_id = p_payroll_action_id
AND action_information_category = 'NO ELEMENT DEFINITION'
AND action_context_type = 'PA'
AND action_information5 = 'F' ;
SELECT tax_unit_id
FROM pay_assignment_actions
WHERE assignment_action_id = p_assignment_action_id ;
select eei_information1
from pay_element_type_extra_info
where element_type_id = p_ele_type_id
and ( eei_information2 = p_le_org_id OR eei_information2 is null )
and information_type = 'NO_ELEMENT_CODES'
and eei_information_category = 'NO_ELEMENT_CODES'
and rownum = 1
order by eei_information2 , element_type_extra_info_id ;
select nvl((select eei_information1 from pay_element_type_extra_info petei
where petei.information_type='NO_ELEMENT_CODES'
and element_type_id = p_ele_type_id
and petei.eei_information2 = p_leg_emp_id
and rownum=1),
(select eei_information1 from pay_element_type_extra_info petei
where petei.information_type='NO_ELEMENT_CODES'
and element_type_id = p_ele_type_id
and eei_information2 is null
and rownum=1)) from dual;
SELECT ENTRY_INFORMATION1
FROM pay_element_entries_f
where ELEMENT_ENTRY_ID = p_ele_entry_id ;
SELECT rrv.result_value val
,rr.element_entry_id ele_entry_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 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 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 NOT EXISTS (SELECT NULL FROM pay_assignment_actions
WHERE payroll_action_id = run_assact.payroll_action_id
AND source_action_id = run_assact.ASSIGNMENT_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 = 'NO'
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 = 'NO'
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_NO_PAYSLIP_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 hsck.segment4
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 hr_de_general.get_uci(l_eff_date, t.user_table_id, r.user_row_id,'ZONE')
|| ' ' || hr_general.decode_lookup('NO_TAX_MUNICIPALITY',
hr_de_general.get_uci(l_eff_date, t.user_table_id, r.user_row_id, 'MAPPING_ID'))
FROM pay_user_tables t
,pay_user_rows_f r
WHERE t.user_table_name = 'NO_TAX_MUNICIPALITY'
AND t.legislation_code = 'NO'
AND r.user_table_id = t.user_table_id
AND r.row_low_range_or_name = p_municipal_no
AND l_eff_date BETWEEN r.effective_start_date AND r.effective_end_date;
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 ('Absence','Direct Payments','Earnings','Supplementary Earnings')
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 = 'NO')
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 = 'NO')
OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));
SELECT action_information2 element_type_id
,action_information3 input_value_id
,action_information4 element_narrative
,action_information5 element_context
,action_information6 uom
,action_information8 prim_def_bal_id
,action_information12 ele_class
FROM pay_action_information
WHERE action_context_id = p_payroll_action_id
AND action_information_category = 'NO ELEMENT DEFINITION'
AND action_context_type = 'PA'
AND action_information5 <> 'F';
SELECT tax_unit_id
FROM pay_assignment_actions
WHERE assignment_action_id = p_assignment_action_id ;
select eei_information1
from pay_element_type_extra_info
where element_type_id = p_ele_type_id
and ( eei_information2 = p_le_org_id OR eei_information2 is null )
and information_type = 'NO_ELEMENT_CODES'
and eei_information_category = 'NO_ELEMENT_CODES'
and rownum = 1
order by eei_information2 , element_type_extra_info_id ;
select nvl((select eei_information1 from pay_element_type_extra_info petei
where petei.information_type='NO_ELEMENT_CODES'
and element_type_id = p_ele_type_id
and petei.eei_information2 = p_leg_emp_id
and rownum=1),
(select eei_information1 from pay_element_type_extra_info petei
where petei.information_type='NO_ELEMENT_CODES'
and element_type_id = p_ele_type_id
and eei_information2 is null
and rownum=1)) from dual;
SELECT ENTRY_INFORMATION1
FROM pay_element_entries_f
where ELEMENT_ENTRY_ID = p_ele_entry_id ;
SELECT rrv.result_value val
,rr.element_entry_id ele_entry_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 element_type_id
FROM pay_element_types_f
WHERE element_name = 'Tax'
AND legislation_code = 'NO'
AND business_group_id IS NULL
AND p_date_earned BETWEEN effective_start_date AND effective_end_date ;
select element_name
from pay_element_types_f
where element_type_id = p_element_type_id
and p_date_earned between effective_start_date and effective_end_date ;
select input_value_id
from pay_input_values_f
where element_type_id = p_element_type_id
and name = p_inp_val_name
and business_group_id is null
and legislation_code = 'NO'
and p_date_earned between effective_start_date and effective_end_date ;
SELECT business_group_id
FROM hr_organization_units
WHERE organization_id = p_organization_id;
SELECT 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 Period Override Element'
AND et.legislation_code = 'NO'
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 ptp.prd_information1
FROM per_time_periods ptp , pay_payroll_actions ppa , pay_assignment_actions paa
WHERE paa.ASSIGNMENT_ACTION_ID = p_asg_act_id
AND ppa.payroll_action_id = paa.PAYROLL_ACTION_ID
AND ppa.time_period_id = ptp.time_period_id ;