[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PAY_SA_ARCHIVE.GET_PARAMETER(legislative_parameters,'PAYROLL_ID')
,PAY_SA_ARCHIVE.GET_PARAMETER(legislative_parameters,'CONSOLIDATION_SET_ID')
,PAY_SA_ARCHIVE.GET_PARAMETER(legislative_parameters,'START_DATE')
,PAY_SA_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_information1 balance_type_id
,org_information2 balance_dim_id
,org_information3 narrative
FROM hr_organization_information
WHERE organization_id = p_bus_grp_id
AND org_information_context = 'SA_PAYSLIP_BALANCES';
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_information1 element_type_id
,hoi.org_information2 input_value_id
,hoi.org_information3 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 = 'SA_PAYSLIP_ELEMENTS'
AND hoi.org_information1 = pet.element_type_id
AND pec.classification_id = pet.classification_id
AND piv.input_value_id = hoi.org_information2
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')
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 = 'SA_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.per_information1 father_name,
ppf.per_information2 g_father_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 paa.person_id person_id
,hscl.segment1 org_id
,hla.style style
,hla.country country
,hla.region_1 R1
,hla.region_2 R2
,hla.region_3 R3
,hla.town_or_city city
,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
,per_all_assignments_f paa
,hr_soft_coding_keyflex hscl
WHERE hscl.segment1 = hou.organization_id
AND hscl.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
AND hou.location_id = hla.location_id
AND paa.assignment_id = p_assignment_id
AND paa.business_group_id = hou.business_group_id
AND p_date_earned BETWEEN paa.effective_start_date
AND paa.effective_end_date;
SELECT hla.style style
,hla.address_line_1 AL1
,hla.address_line_2 AL2
,hla.address_line_3 AL3
,hla.town_or_city city
,hla.region_1 R1
,hla.region_2 R2
,hla.region_3 R3
,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 DISTINCT pose.organization_id_parent
FROM per_org_structure_elements pose
WHERE pose.org_structure_version_id = p_org_structure_version_id
AND pose.organization_id_parent NOT IN (SELECT pose1.organization_id_child
FROM per_org_structure_elements pose1
WHERE pose1.org_structure_version_id = p_org_structure_version_id);
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 org_information1
FROM hr_organization_information
WHERE organization_id = p_bg_id
AND org_information_context = 'SA_HR_BG_INFO';
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 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 global_value
FROM ff_globals_f
WHERE global_name = 'SA_GOSI_BASE_LOWER_LIMIT'
AND legislation_code = 'SA'
AND business_group_id IS NULL
AND p_pay_date_earned BETWEEN effective_start_date
AND effective_end_date;
SELECT global_value
FROM ff_globals_f
WHERE global_name = 'SA_GOSI_BASE_UPPER_LIMIT'
AND legislation_code = 'SA'
AND business_group_id IS NULL
AND p_pay_date_earned BETWEEN effective_start_date
AND effective_end_date;
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 = rec_employer_address.org_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 => rec_person_details.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_canonical(rec_person_details.start_date)
,p_action_information15 => l_organization
,p_action_information16 => p_time_period_id
,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 => NULL
,p_action_information8 => l_city
,p_action_information9 => rec_primary_address.R1
,p_action_information10 => rec_primary_address.R2
,p_action_information11 => rec_primary_address.R3
,p_action_information12 => rec_primary_address.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_employer_address.person_id
,p_action_information5 => NULL --rec_employer_address.AL1
,p_action_information6 => NULL --rec_employer_address.AL2
,p_action_information7 => NULL --rec_employer_address.AL3
,p_action_information8 => NULL --l_emp_city
,p_action_information9 => NULL --rec_employer_address.R1
,p_action_information10 => NULL --rec_employer_address.R2
,p_action_information11 => NULL --rec_employer_address.R3
,p_action_information12 => NULL --rec_employer_address.postal_code
,p_action_information13 => NULL --l_emp_country
,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 ('Absence'
,'Direct Payment'
,'Earnings'
,'Supplemental 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 = 'SA')
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-Statutory Deductions'
,'Statutory Deductions'
,'Voluntary Deductions')
AND nvl(et.reporting_name,et.element_name) <> 'GOSI'
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 = 'SA')
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 = 'SA' )
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,
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.segment4 acct_num,
ppm.org_payment_method_id opm_id,
pop.external_account_id,
pop.org_payment_method_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) accrual_catgry
,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_information1 element_type_id
,hoi.org_information2 input_value_id
,hoi.org_information3 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 = 'SA_PAYSLIP_ELEMENTS'
AND hoi.org_information1 = pet.element_type_id
AND pec.classification_id = pet.classification_id
AND piv.input_value_id = hoi.org_information2
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_information1 balance_type_id
,org_information2 balance_dim_id
,org_information3 narrative
FROM hr_organization_information
WHERE organization_id = p_bus_grp_id
AND org_information_context = 'SA_PAYSLIP_BALANCES';
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;