[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PAY_SE_PAYSLIP_ARCHIVE.GET_PARAMETER(legislative_parameters,'PAYROLL_ID')
,PAY_SE_PAYSLIP_ARCHIVE.GET_PARAMETER(legislative_parameters,'CONSOLIDATION_SET_ID')
,PAY_SE_PAYSLIP_ARCHIVE.GET_PARAMETER(legislative_parameters,'START_DATE')
,PAY_SE_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
,code.org_information2||','||hoi.org_information7 element_narrative
,pec.classification_name
,piv.uom
--,code.org_information2 element_code
FROM hr_organization_information hoi
,hr_organization_information code
,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
and code.organization_id (+)= p_bus_grp_id
and code.org_information_context (+)='SE_SOE_ELEMENT_ADD_DETAILS'
and pet.element_type_id = code.org_information1(+);
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') -- 10229507
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') -- 10229507
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 = 'SE_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.assignment_number assignment_num,
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 = 'SE_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id = hoi2.org_information1
AND hoi2.ORG_INFORMATION_CONTEXT='SE_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 => fnd_date.date_to_displaydate(l_hire_date)
,p_action_information14 => rec_person_details.assignment_num -- Bug 6625393
,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 code.org_information2||','||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
,hr_organization_information code
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 ( 'Salary in Money'
,'Lumpsum'
,'Other Payments Subject to Tax'
,'Retrospective Payments'
,'Direct Payments'
)
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 = 'SE')
OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL))
and code.organization_id(+) = g_business_group_id
and code.org_information_context (+)='SE_SOE_ELEMENT_ADD_DETAILS'
and et.element_type_id = code.org_information1 (+);
/*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'
,'Salary in Money'
,'Lumpsum'
,'Benefits in Kind'
,'Taxable Expenses'
,'Other Payments Subject to Tax'
,'Retrospective Payments'
,'Direct Payments'
,'Employer Charges'
,'External 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 = 'SE')
OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));*/
SELECT code.org_information2||','||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
,hr_organization_information code
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'
,'Pre-Tax 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 = 'SE')
OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL))
and code.organization_id(+) = g_business_group_id
and code.org_information_context (+)='SE_SOE_ELEMENT_ADD_DETAILS'
and et.element_type_id = code.org_information1 (+);
/*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
('Pre-Tax Deductions'
,'Involuntary Deductions'
,'Voluntary Deductions'
,'Statutory 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 = 'SE')
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 = 'SE' )
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 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 hoi.org_information2 element_type_id
,hoi.org_information3 input_value_id
,hoi.org_information7 element_narrative
,pec.classification_name
,piv.uom
,code.org_information2 element_code
FROM hr_organization_information hoi
,pay_element_classifications pec
,pay_element_types_f pet
,pay_input_values_f piv
,hr_organization_information code
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
and code.organization_id(+) = 75235
and code.org_information_context (+)='SE_SOE_ELEMENT_ADD_DETAILS'
and pet.element_type_id = code.org_information1 (+);*/
SELECT rrv.result_value ,rr.element_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 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
AND rr.element_entry_id = p_EE_ID;
select ORG_INFORMATION3,ORG_INFORMATION6,ORG_INFORMATION7,ORG_INFORMATION8,ORG_INFORMATION9,ORG_INFORMATION10
from hr_organization_information code
where code.organization_id = g_business_group_id
and code.org_information_context ='SE_SOE_ELEMENT_ADD_DETAILS'
and code.org_information1 =p_ele_type_id;
SELECT sum(rrv.result_value) result_value
,count(rrv.RUN_RESULT_ID) record_count
,rrv.result_value UNIT_PRICE
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
group by rrv.result_value;
SELECT sum(rrv.result_value) result_value
,count(rrv.RUN_RESULT_ID) record_count
,rrv.result_value UNIT_PRICE
FROM pay_run_result_values pr
,pay_run_result_values rrv
,pay_run_results rr
,pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE pr.input_value_id(+) = p_group_by
AND 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.run_result_id = pr.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
--AND pr.result_value IS NOT NULL
group by pr.result_value,rrv.result_value;*/
SELECT sum(rrv.result_value) result_value
,count(rrv.RUN_RESULT_ID) record_count
,rrv.result_value UNIT_PRICE
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
group by pr.result_value,rrv.result_value;*/
SELECT rrv3.result_value UNIT_PRICE , sum(rrv1.result_value) UNIT, sum(rrv2.result_value) AMOUNT
FROM pay_run_result_values rrv1
,pay_run_results rr1
,pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_run_result_values rrv2
,pay_run_results rr2
,pay_run_result_values rrv3
,pay_run_results rr3
WHERE rrv1.input_value_id = p_iv_id_UNIT
AND rr1.element_type_id = p_ele_type_id
AND rr1.run_result_id = rrv1.run_result_id
AND rr1.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 rrv2.input_value_id = p_iv_id_AMOUNT
AND rr2.run_result_id = rrv2.run_result_id
AND rr2.element_entry_id = rr1.element_entry_id
AND rr2.assignment_action_id = paa.assignment_action_id
and rrv3.input_value_id = p_iv_id_UNIT_PRICE
AND rr3.run_result_id = rrv3.run_result_id
AND rr3.element_entry_id = rr1.element_entry_id
AND rr3.assignment_action_id = paa.assignment_action_id
group by rrv3.result_value;
SELECT rrv3.result_value UNIT_PRICE , rrv1.result_value UNIT, rrv2.result_value AMOUNT
FROM pay_run_result_values rrv1
,pay_run_results rr1
,pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_run_result_values rrv2
,pay_run_results rr2
,pay_run_result_values rrv3
,pay_run_results rr3
WHERE rrv1.input_value_id = p_iv_id_UNIT
AND rr1.element_type_id = p_ele_type_id
AND rr1.run_result_id = rrv1.run_result_id
AND rr1.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 rrv2.input_value_id = p_iv_id_AMOUNT
AND rr2.run_result_id = rrv2.run_result_id
AND rr2.element_entry_id = rr1.element_entry_id
AND rr2.assignment_action_id = paa.assignment_action_id
and rrv3.input_value_id = p_iv_id_UNIT_PRICE
AND rr3.run_result_id = rrv3.run_result_id
AND rr3.element_entry_id = rr1.element_entry_id
AND rr3.assignment_action_id = paa.assignment_action_id;
-- All three are selected, we can group by three in single query
IF rec_group_by.ORG_INFORMATION10 = 'Y'
THEN
FOR csr_result_rec IN csr_all_sum_result_values(rec_group_by.ORG_INFORMATION7
,rec_group_by.ORG_INFORMATION9
,rec_group_by.ORG_INFORMATION8
,g_element_table(l_index).element_type_id
,p_assignment_action_id )
LOOP
IF csr_result_rec.AMOUNT is not null THEN
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 => 'EMEA ELEMENT INFO'
,p_action_information1 => g_element_table(l_index).element_type_id
,p_action_information2 => g_element_table(l_index).input_value_id
,p_action_information3 => g_element_table(l_index).element_type
,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.AMOUNT) --l_formatted_value
,p_action_information8 => csr_result_rec.UNIT
,p_action_information9 => 'Additional Element unit per price:'||csr_result_rec.UNIT_PRICE
,p_assignment_id => p_assignment_id);
ELSE -- Three inputs are not selected.
-- have to get the each input value id and find value for each
-- and archive it if the amount is not null
-- Case for UNIT,PRICE,AMOUNT
-- Segment 7,8,9 is allowed
-- Segemnt 6 is not allowed here, as it doesn't makes sense.
-- segment 7 = > Input ID UNIT
-- segment 8 = > Input ID UNIT PRICE
-- segment 9 = > Input ID Amount
IF rec_group_by.ORG_INFORMATION9 IS NOT NULL
THEN
-- amount should not be null
-- find the amount value and element entry id of this element
-- for this element entry find the the other selected columns like UNIT , UNIT PRICE if selected
--
FOR csr_result_rec IN csr_result_value(rec_group_by.ORG_INFORMATION9 ,g_element_table(l_index).element_type_id ,p_assignment_action_id)
LOOP
-- we have EE id
l_amount := csr_result_rec.result_value;
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 = 'SE'
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 = 'SE'
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='SE'
AND attribute_name= p_attribute_name;
SELECT defined_balance_id
FROM pay_balance_attributes
WHERE attribute_id= p_attribute_id;
SELECT prepay_assact.assignment_action_id prepay_assact_id
,prepay_assact.assignment_id prepay_assgt_id
,prepay_payact.payroll_action_id prepay_payact_id
,prepay_payact.effective_date prepay_effective_date
,run_assact.assignment_id run_assgt_id
,run_assact.assignment_action_id run_assact_id
,run_payact.payroll_action_id run_payact_id
,run_payact.payroll_id payroll_id
FROM pay_action_interlocks archive_intlck
,pay_assignment_actions prepay_assact
,pay_payroll_actions prepay_payact
,pay_action_interlocks prepay_intlck
,pay_assignment_actions run_assact
,pay_payroll_actions run_payact
WHERE archive_intlck.locking_action_id = p_locking_action_id
AND prepay_assact.assignment_action_id = archive_intlck.locked_action_id
AND prepay_payact.payroll_action_id = prepay_assact.payroll_action_id
AND prepay_payact.action_type IN ('U','P')
AND prepay_intlck.locking_action_id = prepay_assact.assignment_action_id
AND run_assact.assignment_action_id = prepay_intlck.locked_action_id
AND run_payact.payroll_action_id = run_assact.payroll_action_id
AND run_payact.action_type IN ('Q', 'R')
ORDER BY prepay_intlck.locking_action_id,prepay_intlck.locked_action_id desc;
SELECT ptp.end_date end_date,
ptp.regular_payment_date regular_payment_date,
ptp.time_period_id time_period_id,
ppa.date_earned date_earned,
ppa.effective_date effective_date,
ptp.start_date start_date
FROM per_time_periods ptp
,pay_payroll_actions ppa
,pay_assignment_actions paa
WHERE ptp.payroll_id =ppa.payroll_id
AND ppa.payroll_action_id =paa.payroll_action_id
AND paa.assignment_action_id =p_assact_id
AND ppa.payroll_action_id =p_pay_act_id
AND ppa.date_earned BETWEEN ptp.start_date
AND ptp.end_date;
SELECT payroll_action_id
FROM pay_assignment_actions
WHERE assignment_Action_id = p_assignment_action_id;
SELECT 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
( 'Salary in Money'
,'Lumpsum'
,'Other Payments Subject to Tax'
,'Retrospective Payments'
,'Direct Payments'
)
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 = 'SE')
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
,code.org_information2 element_code
FROM pay_element_types_f et
, pay_element_types_f_tl pettl
, pay_input_values_f iv
, pay_element_classifications classification
,hr_organization_information code
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'
,'Salary in Money'
,'Lumpsum'
,'Benefits in Kind'
,'Taxable Expenses'
,'Other Payments Subject to Tax'
,'Retrospective Payments'
,'Direct Payments'
,'Employer Charges'
,'External 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 = 'SE')
OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL))
and code.organization_id(+) = g_business_group_id
and code.org_information_context (+)='SE_SOE_ELEMENT_ADD_DETAILS'
and et.element_type_id = code.org_information1 (+);*/
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'
,'Pre-Tax 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 = 'SE')
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
,code.org_information2 element_code
FROM pay_element_types_f et
, pay_element_types_f_tl pettl
, pay_input_values_f iv
, pay_element_classifications classification
,hr_organization_information code
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')
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 = 'SE')
OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL))
and code.organization_id(+) = g_business_group_id
and code.org_information_context (+)='SE_SOE_ELEMENT_ADD_DETAILS'
and et.element_type_id = code.org_information1 (+);*/
SELECT rrv.result_value,rr.element_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 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
AND rr.element_entry_id = p_EE_ID;
select ORG_INFORMATION3,ORG_INFORMATION6,ORG_INFORMATION7,ORG_INFORMATION8,ORG_INFORMATION9,ORG_INFORMATION10
from hr_organization_information code
where code.organization_id = g_business_group_id
and code.org_information_context ='SE_SOE_ELEMENT_ADD_DETAILS'
and code.org_information1 =p_ele_type_id;
SELECT sum(rrv.result_value) result_value
,count(rrv.RUN_RESULT_ID) record_count
,rrv.result_value UNIT_PRICE
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
group by rrv.result_value;
SELECT sum(rrv.result_value) result_value
,count(rrv.RUN_RESULT_ID) record_count
,rrv.result_value UNIT_PRICE
FROM pay_run_result_values pr
,pay_run_result_values rrv
,pay_run_results rr
,pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE pr.input_value_id(+) = p_group_by
AND 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.run_result_id = pr.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
--AND pr.result_value IS NOT NULL
group by pr.result_value,rrv.result_value;*/
SELECT sum(rrv.result_value) 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
group by rrv.result_value;
SELECT rrv3.result_value UNIT_PRICE , sum(rrv1.result_value) UNIT, sum(rrv2.result_value) AMOUNT
FROM pay_run_result_values rrv1
,pay_run_results rr1
,pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_run_result_values rrv2
,pay_run_results rr2
,pay_run_result_values rrv3
,pay_run_results rr3
WHERE rrv1.input_value_id = p_iv_id_UNIT
AND rr1.element_type_id = p_ele_type_id
AND rr1.run_result_id = rrv1.run_result_id
AND rr1.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 rrv2.input_value_id = p_iv_id_AMOUNT
AND rr2.run_result_id = rrv2.run_result_id
AND rr2.element_entry_id = rr1.element_entry_id
AND rr2.assignment_action_id = paa.assignment_action_id
and rrv3.input_value_id = p_iv_id_UNIT_PRICE
AND rr3.run_result_id = rrv3.run_result_id
AND rr3.element_entry_id = rr1.element_entry_id
AND rr3.assignment_action_id = paa.assignment_action_id
group by rrv3.result_value;
SELECT rrv3.result_value UNIT_PRICE , rrv1.result_value UNIT, rrv2.result_value AMOUNT
FROM pay_run_result_values rrv1
,pay_run_results rr1
,pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_run_result_values rrv2
,pay_run_results rr2
,pay_run_result_values rrv3
,pay_run_results rr3
WHERE rrv1.input_value_id = p_iv_id_UNIT
AND rr1.element_type_id = p_ele_type_id
AND rr1.run_result_id = rrv1.run_result_id
AND rr1.assignment_action_id = paa.assignment_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_action_id = p_assignment_action_id
AND ppa.action_type IN ('Q','R')
and rrv2.input_value_id = p_iv_id_AMOUNT
AND rr2.run_result_id = rrv2.run_result_id
AND rr2.element_entry_id = rr1.element_entry_id
AND rr2.assignment_action_id = paa.assignment_action_id
and rrv3.input_value_id = p_iv_id_UNIT_PRICE
AND rr3.run_result_id = rrv3.run_result_id
AND rr3.element_entry_id = rr1.element_entry_id
AND rr3.assignment_action_id = paa.assignment_action_id;
-- All three are selected, we can group by three in single query
IF rec_group_by.ORG_INFORMATION10 = 'Y'
THEN
FOR csr_result_rec IN csr_all_sum_result_values(rec_group_by.ORG_INFORMATION7
,rec_group_by.ORG_INFORMATION9
,rec_group_by.ORG_INFORMATION8
,csr_rec.element_type_id
,p_assignment_action_id )
LOOP
IF csr_result_rec.AMOUNT is not null THEN
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 => 'EMEA ELEMENT INFO'
,p_action_information1 => csr_rec.element_type_id
,p_action_information2 => csr_rec.input_value_id
,p_action_information3 => 'E'
,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.AMOUNT) --l_formatted_value
,p_action_information8 => csr_result_rec.UNIT
,p_action_information9 => 'Earning Element unit per price:'||csr_result_rec.UNIT_PRICE
,p_assignment_id => p_assignment_id);
ELSE -- Three inputs are not selected.
-- have to get the each input value id and find value for each
-- and archive it if the amount is not null
-- Case for UNIT,PRICE,AMOUNT
-- Segment 7,8,9 is allowed
-- Segemnt 6 is not allowed here, as it doesn't makes sense.
-- segment 7 = > Input ID UNIT
-- segment 8 = > Input ID UNIT PRICE
-- segment 9 = > Input ID Amount
IF rec_group_by.ORG_INFORMATION9 IS NOT NULL
THEN
-- amount should not be null
-- find the amount value and element entry id of this element
-- for this element entry find the the other selected columns like UNIT , UNIT PRICE if selected
--
FOR csr_result_rec IN csr_result_value(rec_group_by.ORG_INFORMATION9 ,csr_rec.element_type_id ,p_assignment_action_id)
LOOP
-- we have EE id
l_amount := csr_result_rec.result_value;
-- All three are selected, we can group by three in single query
IF rec_group_by.ORG_INFORMATION10 = 'Y'
THEN
FOR csr_result_rec IN csr_all_sum_result_values(rec_group_by.ORG_INFORMATION7
,rec_group_by.ORG_INFORMATION9
,rec_group_by.ORG_INFORMATION8
,csr_rec.element_type_id
,p_assignment_action_id )
LOOP
IF csr_result_rec.AMOUNT is not null THEN
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 => 'EMEA ELEMENT INFO'
,p_action_information1 => csr_rec.element_type_id
,p_action_information2 => csr_rec.input_value_id
,p_action_information3 => 'D'
,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.AMOUNT) --l_formatted_value
,p_action_information8 => csr_result_rec.UNIT
,p_action_information9 => 'Deduction Element unit per price:'||csr_result_rec.UNIT_PRICE
,p_assignment_id => p_assignment_id);
ELSE -- Three inputs are not selected.
-- have to get the each input value id and find value for each
-- and archive it if the amount is not null
-- Case for UNIT,PRICE,AMOUNT
-- Segment 7,8,9 is allowed
-- Segemnt 6 is not allowed here, as it doesn't makes sense.
-- segment 7 = > Input ID UNIT
-- segment 8 = > Input ID UNIT PRICE
-- segment 9 = > Input ID Amount
IF rec_group_by.ORG_INFORMATION9 IS NOT NULL
THEN
-- amount should not be null
-- find the amount value and element entry id of this element
-- for this element entry find the the other selected columns like UNIT , UNIT PRICE if selected
--
FOR csr_result_rec IN csr_result_value(rec_group_by.ORG_INFORMATION9 ,csr_rec.element_type_id ,p_assignment_action_id)
LOOP
-- we have EE id
l_amount := csr_result_rec.result_value;
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 = 'SE_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id = hoi2.org_information1
AND hoi2.ORG_INFORMATION_CONTEXT='SE_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';