[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pdb.defined_balance_id
FROM pay_balance_dimensions pbd
,pay_balance_types pbt
,pay_defined_balances pdb
WHERE pbd.database_item_suffix = p_dimension_name
AND pbd.business_group_id is null
AND pbd.legislation_code='NL'
AND pbt.balance_name = p_balance_name
AND pbt.business_group_id is null
AND pbt.legislation_code='NL'
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id= pbd.balance_dimension_id
AND pdb.business_group_id is null
AND pdb.legislation_code='NL';
SELECT 1
FROM pay_user_tables put
WHERE put.legislation_code IS NULL
AND put.business_group_id = p_bg_id
AND put.user_table_name = 'NL_USER_STATUTORY_BALANCES';
SELECT TRIM(user_column_name) bal_dimension
,TRIM(pur.row_low_range_or_name) bal_name
FROM pay_user_columns puc
,pay_user_rows_f pur
,pay_user_tables put
,pay_user_column_instances_f puci
WHERE put.legislation_code IS NULL
AND put.business_group_id = p_bg_id
AND pur.user_table_id = put.user_table_id
AND puc.user_table_id = put.user_table_id
AND puci.user_row_id = pur.user_row_id
AND puci.user_column_id = puc.user_column_id
AND put.user_table_name = 'NL_USER_STATUTORY_BALANCES'
AND p_effecitve_date BETWEEN puci.effective_start_date
AND puci.effective_end_date
AND p_effecitve_date BETWEEN pur.effective_start_date
AND pur.effective_end_date;
SELECT TRIM(user_column_name) bal_dimension
,TRIM(pur.row_low_range_or_name) bal_name
FROM pay_user_columns puc
,pay_user_rows_f pur
,pay_user_tables put
,pay_user_column_instances_f puci
WHERE put.legislation_code = 'NL'
AND pur.user_table_id = put.user_table_id
AND puc.user_table_id = put.user_table_id
AND puci.user_row_id = pur.user_row_id
AND puci.user_column_id = puc.user_column_id
AND put.user_table_name = 'NL_STATUTORY_BALANCES'
AND p_effecitve_date BETWEEN puci.effective_start_date
AND puci.effective_end_date
AND p_effecitve_date BETWEEN pur.effective_start_date
AND pur.effective_end_date;
SELECT PAY_NL_PAYSLIP_ARCHIVE.get_parameter(legislative_parameters,'PAYROLL_ID')
,PAY_NL_PAYSLIP_ARCHIVE.get_parameter(legislative_parameters,'CONSOLIDATION_SET_ID')
,PAY_NL_PAYSLIP_ARCHIVE.get_parameter(legislative_parameters,'START_DATE')
,PAY_NL_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.org_information1,
org.org_information2,
org.org_information3,
org.org_information4
FROM hr_organization_information_v org
WHERE org.org_information_context = p_context
AND org.organization_id = p_bg_id;
SELECT pbt.balance_name,
pbd.database_item_suffix,
pbt.legislation_code,
pdb.defined_balance_id
FROM pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pbt.balance_type_id = p_balance_type_id
AND pbd.balance_dimension_id = p_balance_dimension_id;
SELECT pet.iterative_formula_id
FROM pay_element_types_f pet
WHERE pet.element_type_id = p_element_type_id
AND p_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date;
SELECT piv.uom
FROM pay_input_values_f piv
WHERE piv.input_value_id = p_input_value_id
AND p_effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date;
|Description : This procedure returns a sql string to select a range of |
| assignments eligible for archival |
-------------------------------------------------------------------------------*/
Procedure RANGE_CODE (pactid IN NUMBER
,sqlstr OUT NOCOPY VARCHAR2) is
--
--
CURSOR csr_payrolls (p_payroll_id NUMBER
,p_consolidation_set_id NUMBER
,p_effective_date DATE) IS
SELECT ppf.payroll_id
FROM pay_all_payrolls_f ppf
WHERE ppf.consolidation_set_id = p_consolidation_set_id
AND ppf.payroll_id = NVL(p_payroll_id,ppf.payroll_id)
AND p_effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date;
SELECT hoi_tax.organization_id org_id
,hou1.name tax_office_name
,hoi_tax.org_information4 reg_no
FROM hr_all_organization_units hou
,hr_all_organization_units hou1
,hr_organization_information hoi
,hr_organization_information hoi_tax
WHERE hoi.org_information_context = 'CLASS'
AND hoi.org_information1 = 'HR_ORG'
AND hoi.organization_id = HOU.organization_id
AND hou.business_group_id = p_bus_group_id
AND hoi_tax.organization_id = hoi.organization_id
AND hoi_tax.org_information_context = 'NL_ORG_INFORMATION'
AND hoi_tax.org_information4 IS NOT NULL
AND hou1.business_group_id = p_bus_group_id
AND hou1.organization_id = hoi_tax.org_information3
AND hoi_tax.org_information3 IS NOT NULL
AND NOT EXISTS (SELECT NULL
FROM pay_action_information pai
WHERE pai.action_context_id = p_pact_id
AND pai.action_context_type = 'PA'
AND pai.action_information_category ='EMEA PAYROLL INFO'
AND pai.action_information1 = hoi_tax.organization_id);
SELECT pact.payroll_action_id payroll_action_id
,pact.effective_date effective_date
,pact.date_earned date_earned
,pact.pay_advice_message payroll_message
FROM pay_payrolls_f ppf,
pay_payroll_actions pact
WHERE pact.payroll_id = ppf.payroll_id
AND pact.effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND pact.payroll_id = p_payroll_id
AND pact.effective_date BETWEEN p_start_date
AND p_end_date
AND pact.action_type IN ('R','Q')
AND pact.action_status = 'C'
AND NOT EXISTS (
SELECT NULL
FROM pay_action_information pai
WHERE pai.action_context_id = pactid
AND pai.action_context_type = 'PA'
AND pai.action_information_category ='EMPLOYEE OTHER INFORMATION');
sqlstr := '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
sqlstr := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
SELECT /*+ ORDERED
INDEX(ppa PAY_PAYROLL_ACTIONS_PK)
INDEX(as1 PER_ASSIGNMENTS_F_N12)
INDEX(ppf PAY_PAYROLLS_F_PK)
INDEX(act PAY_ASSIGNMENT_ACTIONS_N51)
INDEX(appa PAY_PAYROLL_ACTIONS_PK)
INDEX(pai PAY_ACTION_INTERLOCKS_FK2)
INDEX(act1 PAY_ASSIGNMENT_ACTIONS_PK)
INDEX(appa2 PAY_PAYROLL_ACTIONS_PK)
USE_NL(ppa as1 ppf act appa pai act1 appa2) */
act.assignment_id assignment_id,
act.assignment_action_id run_action_id,
act1.assignment_action_id prepaid_action_id
FROM pay_payroll_actions ppa,
per_all_assignments_f as1,
pay_assignment_actions act,
pay_payroll_actions appa,
pay_action_interlocks pai,
pay_assignment_actions act1,
pay_payroll_actions appa2,
pay_payrolls_f ppf
WHERE ppa.payroll_action_id = p_pact_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 stperson AND endperson
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.business_group_id = as1.business_group_id
AND ppa.effective_date BETWEEN as1.effective_start_date AND as1.effective_end_date
AND act.action_status IN ('C','S') -- 10228241
AND act.assignment_action_id = pai.locked_action_id
AND appa2.payroll_id = ppf.payroll_id
AND ppf.business_group_id = as1.business_group_id
AND ppa.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status IN ('C','S') -- 10228241
AND ((ppf.Multi_Assignments_Flag='Y' AND act1.source_action_id IS NOT NULL)
OR (ppf.Multi_Assignments_Flag='N' AND act1.source_action_id IS NULL))
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 = 'NL_PS_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')
--group by act.assignment_id,act1.assignment_action_id
ORDER BY act.assignment_id,act1.assignment_Action_id;
select paa.assignment_action_id
from pay_assignment_Actions paa
,pay_assignment_Actions paa1
where paa.source_action_id=p_source_action_id
and paa1.assignment_id <> p_assignment_id
and paa.assignment_id=p_assignment_id
and paa1.assignment_action_id=p_source_action_id;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
select pai.action_information_id, pai.action_information1, pa.add_information13,
pa.add_information14, pai.object_version_number
from pay_action_information pai,
per_addresses pa
where pai.action_context_id = p_assactid
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'ADDRESS DETAILS'
and pa.person_id = pai.action_information1
and pa.primary_flag = 'Y'
and p_archive_effective_date between pa.date_from
and nvl(pa.date_to, p_archive_effective_date);
pay_action_information_api.update_action_information(p_action_information_id => cntr.action_information_id
,p_object_version_number => cntr.object_version_number
,p_action_information26 => cntr.add_information13
,p_action_information27 => cntr.add_information14
);
SELECT trim(scl.segment11) tax_code
,FND_NUMBER.Canonical_To_Number(scl.segment12) prev_year_sal
,scl.segment4 tax_reduction
,scl.segment7 labour_tax_reduction
,scl.segment9 add_senior_tax_reduction
,FND_NUMBER.Canonical_To_Number(scl.segment28) individual_working_hours
FROM per_assignments_f paf
,hr_soft_coding_keyflex scl
WHERE paf.assignment_id = p_assignment_id
AND p_date_earned BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id;
SELECT organization_id
FROM per_all_assignments_f
WHERE assignment_id=p_assignment_id
AND p_date_earned BETWEEN effective_start_date
AND effective_end_date;
SELECT paf.period_type period_type
,paf.business_group_id bug_group_id
FROM pay_all_payrolls_f paf
WHERE payroll_id = p_payroll_id
AND p_date_earned BETWEEN paf.effective_start_date
AND paf.effective_end_date;
SELECT ff.context_id context_id
,pact.context_value context_value
, decode(context_value,'ZVW',0,'ZW',1,
'WEWE',2,'WEWA',3,'WAOD',4,'WAOB',5,6) seq
FROM ff_contexts ff
,pay_action_contexts pact
WHERE ff.context_name = p_context_name
AND pact.context_id = ff.context_id
AND pact.assignment_action_id=p_assignment_action_id
ORDER BY decode(context_value,'ZVW',0,'ZW',1,
'WEWE',2,'WEWA',3,'WAOD',4,'WAOB',5,6);
SELECT org_structure_version_id
FROM per_org_structure_versions posv
WHERE organization_structure_id =p_org_struct_id
AND p_date_earned BETWEEN posv.date_from
AND NVL(posv.date_to,hr_general.end_of_time);
SELECT hr_org_tl.NAME name
,hr_loc.style style
,hr_loc.region_1 street_name
,hr_loc.address_line_1 add_line1
,hr_loc.address_line_2 add_line2
,hr_loc.address_line_3 add_line3
-- ,hr_loc.postal_code postal_code
,pay_nl_general.get_postal_code_new(hr_loc.postal_code) postal_code
,hr_loc.town_or_city city
,hr_loc.region_2 province
,hr_loc.country country
,hr_loc.region_3 po_box_no
FROM HR_ALL_ORGANIZATION_UNITS hr_org
,HR_ALL_ORGANIZATION_UNITS_TL hr_org_tl
,hr_locations_all hr_loc
WHERE hr_org.organization_id = p_organization_id
AND hr_org.organization_id = hr_org_tl.organization_id
AND hr_org.location_id = hr_loc.location_id (+)
AND hr_org_tl.LANGUAGE = USERENV('LANG');
SELECT TERRITORY_SHORT_NAME
FROM FND_TERRITORIES_VL
WHERE TERRITORY_CODE = p_territory_code;*/
SELECT paf.normal_hours normal_hours
FROM per_all_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND p_date_earned BETWEEN paf.effective_start_date
AND paf.effective_end_date;
SELECT prv.result_value value
,prv1.result_value si_type
FROM pay_run_result_values prv
,pay_run_results prr
,pay_input_values_f piv
,pay_input_values_f piv1
,pay_run_result_values prv1
,pay_element_types_f pet
WHERE prr.status IN ('P','PA')
AND prv.run_result_id = prr.run_result_id
AND prr.assignment_action_id = p_assignment_action_id
AND prr.element_type_id = pet.element_type_id
AND pet.legislation_code = 'NL'
AND prv.input_value_id = piv.input_value_id
AND prv1.input_value_id = piv1.input_value_id
AND piv.name = 'SI Type Name'
AND p_effective_date BETWEEN piv1.effective_start_date
AND piv1.effective_end_date
AND p_effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND piv.element_type_id = prr.element_type_id
AND prv1.run_result_id = prv.run_result_id
AND piv1.name = 'SI Type'
AND prv.result_value IS NOT NULL
AND pet.element_name IN
('Net Employee SI Contribution', 'Employee SI Contribution Standard Tax',
'Employee SI Contribution Special Tax', 'Employee SI Contribution');
SELECT 1
FROM pay_run_results prr
,pay_element_types_f pet
WHERE
pet.element_name = 'Private Health Insurance'
AND prr.assignment_action_id=p_assgn_action_id
AND prr.element_type_id=pet.element_type_id
AND p_date_earned BETWEEN pet.effective_start_date
AND pet.effective_end_date;
select result_value
from pay_run_result_values prrv
, pay_run_results prr
,pay_element_types_f pet
,pay_input_values_f piv
where prrv.run_result_id=prr.run_result_id
and prr.assignment_action_id=p_asg_act_id
and prr.element_type_id=pet.element_type_id
and pet.element_name = 'Special Tax Deduction'
and prrv.input_value_id=piv.input_value_id
and piv.legislation_code = 'NL'
and piv.name = 'Previous Year Taxable Income'
and p_date_earned BETWEEN pet.effective_start_date
AND pet.effective_end_date
and p_date_earned BETWEEN piv.effective_start_date
AND piv.effective_end_date;
SELECT TERRITORY_SHORT_NAME
FROM FND_TERRITORIES_VL
WHERE TERRITORY_CODE = p_territory_code;
SELECT 1
FROM pay_input_values_f piv
WHERE piv.element_type_id = p_element_type_id
AND piv.NAME = 'SI Type'
AND p_effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date;
SELECT prv.result_value
FROM pay_run_result_values prv
,pay_run_results prr
WHERE prr.status IN ('P','PA')
AND prv.run_result_id = prr.run_result_id
AND prr.assignment_action_id = p_assignment_action_id
AND prr.element_type_id = p_element_type_id
AND prv.input_value_id = p_input_value_id
AND prv.result_value IS NOT NULL;
SELECT prv.result_value value
,prv1.result_value si_type
FROM pay_run_result_values prv
,pay_run_results prr
,pay_input_values_f piv
,pay_run_result_values prv1
WHERE prr.status IN ('P','PA')
AND prv.run_result_id = prr.run_result_id
AND prr.assignment_action_id = p_assignment_action_id
AND prr.element_type_id = p_element_type_id
AND prv.input_value_id = p_input_value_id
AND piv.name = 'SI Type'
AND p_effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND piv.element_type_id = prr.element_type_id
AND prv1.run_result_id = prv.run_result_id
AND prv1.input_value_id = piv.input_value_id
AND prv1. result_value = NVL(p_si_type,prv1. result_value)
AND prv.result_value IS NOT NULL;
select piv.input_value_id
from pay_input_values_f piv
,pay_element_types_f pet
where
pet.element_type_id=p_element_type_id
and pet.element_type_id=piv.element_type_id
and piv.name = 'SI Type Name'
and p_effective_date between piv.effective_start_date
and piv.effective_end_date;
Select prv1.result_value si_type_name
From
pay_run_result_values prv
,pay_run_results prr
,pay_input_values_f piv
,pay_run_result_values prv1
,pay_input_values_f piv1
where prr.status in ('P','PA')
AND prr.run_result_id = prv.run_result_id
AND prr.assignment_action_id = p_assignment_action_id
AND prr.element_type_id = p_element_type_id
AND piv.name = 'SI Type'
AND prv.input_value_id = piv.input_value_id
AND p_effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND piv1.name = 'SI Type Name'
AND prv1.input_value_id = piv1.input_value_id
AND p_effective_date BETWEEN piv1.effective_start_date
AND piv1.effective_end_date
AND prv1.run_result_id = prv.run_result_id
AND prv.result_value = p_si_type;
SELECT pac.context_id context_id
,pac.context_value value
FROM ff_contexts ff
,pay_action_contexts pac
WHERE ff.context_name = p_context_name
AND pac.context_id = ff.context_id
AND pac.assignment_Action_id = p_assig_action_id;
SELECT ff.context_id context_id
FROM ff_contexts ff
WHERE ff.context_name = p_context_name;
SELECT prrv1.result_value si_type_name
FROM pay_balance_feeds_f pbf
,pay_balance_types pbt
,pay_input_values_f piv
,pay_input_values_f piv1
,pay_input_values_f piv2
,pay_element_types_f pet
,pay_run_results prr
,pay_run_result_values prrv
,pay_run_result_values prrv1
WHERE pbf.balance_type_id = pbt.balance_type_id
AND pbt.balance_name = p_balance_name
AND piv.input_value_id = pbf.input_value_id
AND (piv.name ='Pay Value'
OR piv.name ='Days')
AND pet.element_type_id = piv.element_type_id
AND pet.classification_id <> (SELECT classification_id
from pay_element_classifications
where classification_name ='Balance Initialization'
and business_group_id is null
and legislation_code is null)
AND piv1.element_type_id = pet.element_type_id
AND piv1.name = 'SI Type Name'
AND piv2.element_type_id = pet.element_type_id
AND piv2.name = 'SI Type'
AND prr.element_type_id = pet.element_type_id
AND prr.assignment_action_id = p_assgn_action_id
AND prrv.run_result_id = prr.run_result_id
AND prrv.input_value_id = piv2.input_value_id
AND prrv.result_value = p_si_type
AND prrv1.run_result_id = prrv.run_result_id
AND prrv1.input_value_id = piv1.input_value_id
AND p_date_earned BETWEEN pbf.effective_start_date
AND pbf.effective_end_date
AND p_date_earned BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND p_date_earned BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND p_date_earned BETWEEN piv1.effective_start_date
AND piv1.effective_end_date
AND p_date_earned BETWEEN piv2.effective_start_date
AND piv2.effective_end_date;
SELECT pdb.defined_balance_id defined_balance_id
FROM pay_balance_dimensions pbd
,pay_balance_types pbt
,pay_defined_balances pdb
WHERE pbd.database_item_suffix = p_dimension
AND pbt.balance_name = p_balance_name
AND (pbt.business_group_id = p_bus_group_id
OR pbt.legislation_code = 'NL')
AND (pbd.business_group_id = p_bus_group_id
OR pbd.legislation_code = 'NL')
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND (pdb.business_group_id = p_bus_group_id
OR pdb.legislation_code = 'NL');
SELECT prv.result_value value
,pet.element_type_id element_type_id
,pet.element_name element_name
,nvl(pettl.reporting_name,pettl.element_name) reporting_name
,pet.processing_priority priority
,pet.business_group_id business_group_id
,prv.run_result_id run_result_id
FROM pay_run_result_values prv
,pay_run_results prr
,pay_element_types_f pet
,pay_element_types_f_tl pettl
,pay_input_values_f piv
,pay_element_classifications pec
,pay_element_entries_f pee
WHERE prr.status IN ('P','PA')
AND prr.source_id = pee.element_entry_id
AND pee.CREATOR_TYPE = p_creator_type
AND prv.run_result_id = prr.run_result_id
AND prr.assignment_action_id = p_run_assign_action_id
AND prr.element_type_id = pet.element_type_id
AND prv.input_value_id = piv.input_value_id
AND piv.name = p_input_value_name
AND pet.classification_id = pec.classification_id
AND pec.classification_name = p_class_name
AND pet.element_name = NVL(p_element_name,pet.element_name)
AND pec.legislation_code = 'NL'
AND pet.element_type_id = pettl.element_type_id
AND pettl.language = USERENV('LANG')
AND p_retro_period = nvl( prr.start_date /*pay_nl_general.get_retro_period(prr.source_id,p_date_earned)*/,p_retro_period) --Bug 5107780
AND p_date_earned BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND p_date_earned BETWEEN piv.effective_start_date
AND piv.effective_end_date;
SELECT unique prr.start_date --(pay_nl_general.get_retro_period(prr.source_id,p_date_earned)) Bug 5107780
FROM pay_run_result_values prv
,pay_run_results prr
,pay_element_types_f pet
,pay_element_types_f_tl pettl
,pay_input_values_f piv
,pay_element_classifications pec
WHERE prr.status IN ('P','PA')
AND prv.run_result_id = prr.run_result_id
AND prr.assignment_action_id = p_run_assign_action_id
AND prr.element_type_id = pet.element_type_id
AND prv.input_value_id = piv.input_value_id
AND piv.name = p_input_value_name
AND pet.classification_id = pec.classification_id
AND pec.classification_name = p_class_name
AND pet.element_name = NVL(p_element_name,pet.element_name)
AND piv.element_type_id = pet.element_type_id
AND pec.legislation_code = 'NL'
AND pet.element_type_id = pettl.element_type_id
AND pettl.language = USERENV('LANG')
AND p_date_earned BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND p_date_earned BETWEEN piv.effective_start_date
AND piv.effective_end_date
ORDER BY prr.start_date ; --pay_nl_general.get_retro_period(prr.source_id,p_date_earned); Bug 5107780
SELECT prv.result_value value
,fnd_date.date_to_canonical( prr.start_date /*pay_nl_general.get_retro_period(prr.source_id,p_date_earned)*/ ) RDate
,pet.element_type_id element_type_id
,pet.element_name element_name
,nvl(pettl.reporting_name,pettl.element_name) reporting_name
,pet.processing_priority priority
,pet.business_group_id business_group_id
,prv.run_result_id run_result_id /* 4389520*/
FROM pay_run_result_values prv
,pay_run_results prr
,pay_element_types_f pet
,pay_element_types_f_tl pettl
,pay_input_values_f piv
,pay_element_classifications pec
WHERE prr.status IN ('P','PA')
AND prv.run_result_id = prr.run_result_id
AND prr.assignment_action_id = p_run_assign_action_id
AND prr.element_type_id = pet.element_type_id
AND prv.input_value_id = piv.input_value_id
AND piv.name = p_input_value_name
AND pet.classification_id = pec.classification_id
AND pec.classification_name = p_class_name
AND pet.element_name = NVL(p_element_name,pet.element_name)
AND pec.legislation_code = 'NL'
AND pet.element_type_id = pettl.element_type_id
AND pet.element_type_id = piv.element_type_id
AND pettl.language = USERENV('LANG')
AND p_date_earned BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND p_date_earned BETWEEN piv.effective_start_date
AND piv.effective_end_date
ORDER BY pet.element_name,prr.start_date ;
SELECT 1
FROM pay_sub_classification_rules_f pscf
,pay_element_classifications pec
WHERE pscf.element_type_id = p_element_type_id
AND pscf.classification_id = pec.classification_id
AND pec.classification_name = p_sub_class_name
AND pec.legislation_code = 'NL'
AND p_date_earned BETWEEN pscf.effective_start_date
AND pscf.effective_end_date;
SELECT pec.classification_name
FROM pay_sub_classification_rules_f pscf
,pay_element_classifications pec
WHERE pscf.element_type_id = p_element_type_id
AND pscf.classification_id = pec.classification_id
AND pec.classification_name
IN
('Pension Standard Tax : Pre-SI and Pre-Tax Deductions'
,'Pension Special Tax : Pre-SI and Pre-Tax Deductions'
,'Pension Standard Tax : Retro Pre SI Pre Tax Deductions'
,'Pension Special Tax : Retro Pre SI Pre Tax Deductions'
)
AND pec.legislation_code = 'NL'
AND p_date_earned BETWEEN pscf.effective_start_date
AND pscf.effective_end_date;
SELECT prv.result_value value
FROM pay_run_result_values prv
,pay_run_results prr
,pay_element_types_f pet
,pay_input_values_f piv
,pay_element_classifications pec
WHERE prr.status IN ('P','PA')
AND prv.run_result_id = prr.run_result_id
AND prr.assignment_action_id = p_run_assign_action_id
AND prr.element_type_id = pet.element_type_id
AND prv.input_value_id = piv.input_value_id
AND piv.name = p_input_value_name
AND pet.classification_id = pec.classification_id
AND pec.classification_name = p_class_name
AND pet.element_name = NVL(p_element_name,pet.element_name)
AND pet.element_type_id = piv.element_type_id
AND pec.legislation_code = 'NL'
AND p_date_earned BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND p_date_earned BETWEEN piv.effective_start_date
AND piv.effective_end_date;
SELECT paa.assignment_action_id np_assignment_action_id
FROM pay_assignment_actions paa
,pay_run_types_f prt
WHERE paa.source_action_id = p_assignment_action_id
AND paa.payroll_action_id = p_payroll_action_id
AND paa.assignment_id = p_assignment_id
AND paa.run_type_id = prt.run_type_id
AND prt.run_method IN ('N','P')
AND p_effective_date BETWEEN prt.effective_start_date
AND prt.effective_end_date;
SELECT pbt.balance_name
FROM pay_balance_types pbt,
pay_balance_feeds_f pbf,
pay_input_values_f piv
WHERE pbf.balance_type_id = pbt.balance_type_id
AND piv.input_value_id = pbf.input_value_id
AND piv.element_type_id = p_element_type_id
AND piv.name = 'Pay Value'
AND pbt.balance_name like '%Tax Correction'
AND p_effective_date BETWEEN pbf.effective_start_date AND pbf.effective_end_date;
SELECT pac.context_id context_id
,pac.context_value value
FROM ff_contexts ff
,pay_action_contexts pac
WHERE ff.context_name = p_context_name
AND pac.context_id = ff.context_id
AND pac.assignment_Action_id = p_run_assign_action_id;
SELECT NVL(pbt_tl.reporting_name,pbt_tl.balance_name) bal_name
FROM pay_balance_types pbt
,pay_balance_types_tl pbt_tl
WHERE pbt.balance_name = p_balance_name
AND pbt.balance_type_id = pbt_tl.balance_type_id
AND pbt_tl.language = USERENV('LANG');
SELECT prrv1.result_value si_type_name
FROM pay_balance_feeds_f pbf
,pay_balance_types pbt
,pay_input_values_f piv
,pay_input_values_f piv1
,pay_input_values_f piv2
,pay_element_types_f pet
,pay_run_results prr
,pay_run_result_values prrv
,pay_run_result_values prrv1
WHERE pbf.balance_type_id = pbt.balance_type_id
AND pbt.balance_name like p_balance_name||'%'
AND piv.input_value_id = pbf.input_value_id
AND piv.name ='Pay Value'
AND pet.element_type_id = piv.element_type_id
AND pet.classification_id <>(SELECT classification_id
from pay_element_classifications
where classification_name ='Balance Initialization'
and business_group_id is null
and legislation_code is null)
AND piv1.element_type_id = pet.element_type_id
AND piv1.name = 'SI Type Name'
AND piv2.element_type_id = pet.element_type_id
AND piv2.name = 'SI Type'
AND prr.element_type_id = pet.element_type_id
AND prr.assignment_action_id = p_assgn_action_id
AND prrv.run_result_id = prr.run_result_id
AND prrv.input_value_id = piv2.input_value_id
AND prrv.result_value = p_si_type
AND prrv1.run_result_id = prrv.run_result_id
AND prrv1.input_value_id = piv1.input_value_id
AND p_date_earned BETWEEN pbf.effective_start_date
AND pbf.effective_end_date
AND p_date_earned BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND p_date_earned BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND p_date_earned BETWEEN piv1.effective_start_date
AND piv1.effective_end_date
AND p_date_earned BETWEEN piv2.effective_start_date
AND piv2.effective_end_date;
SELECT 1
FROM pay_run_results prr
,pay_element_types_f pet
WHERE
pet.element_name = 'Private Health Insurance'
AND prr.assignment_action_id=p_assgn_action_id
AND prr.element_type_id=pet.element_type_id
AND p_date_earned BETWEEN pet.effective_start_date
AND pet.effective_end_date;
SELECT paa.assignment_action_id np_assignment_action_id
FROM pay_assignment_actions paa
,pay_run_types_f prt
WHERE paa.source_action_id = p_assignment_action_id
AND paa.payroll_action_id = p_payroll_action_id
AND paa.assignment_id = p_assignment_id
AND paa.run_type_id = prt.run_type_id
AND prt.run_method IN ('N','P')
AND p_effective_date BETWEEN prt.effective_start_date
AND prt.effective_end_date
order by np_assignment_action_id;
SELECT pre.locked_action_id pre_assignment_action_id,
pay.locked_action_id master_assignment_action_id,
assact.assignment_id assignment_id,
assact.payroll_action_id pay_payroll_action_id,
paa.effective_date effective_date,
ppaa.effective_date pre_effective_date,
paa.date_earned date_earned,
paa.time_period_id time_period_id,
paa.payroll_id payroll_id
FROM pay_action_interlocks pre,
pay_action_interlocks pay,
pay_payroll_actions paa,
pay_payroll_actions ppaa,
pay_assignment_actions assact,
pay_assignment_actions passact
WHERE pre.locked_action_id = pay.locking_action_id
AND pre.locking_action_id = lp_locking_action_id
AND pre.locked_action_id = passact.assignment_action_id
AND passact.assignment_id =assact.assignment_id
AND passact.payroll_action_id = ppaa.payroll_action_id
AND ppaa.action_type IN ('P','U')
AND pay.locked_action_id = assact.assignment_action_id
AND assact.payroll_action_id = paa.payroll_action_id
AND assact.source_action_id IS NULL
ORDER BY pay.locked_action_id;
SELECT paa.assignment_action_id np_assignment_action_id
FROM pay_assignment_actions paa
,pay_run_types_f prt
WHERE paa.source_action_id = p_assignment_action_id
AND paa.payroll_action_id = p_payroll_action_id
AND paa.assignment_id = p_assignment_id
AND paa.run_type_id = prt.run_type_id
AND prt.run_method IN ('N','P')
AND p_effective_date BETWEEN prt.effective_start_date
AND prt.effective_end_date
order by np_assignment_action_id;
SELECT unique prr.start_date /*pay_nl_general.get_retro_period(prr.source_id,p_date_earned)*/ RDate -- Bug 5107780
FROM pay_balance_feeds_f pbf
,pay_balance_types pbt
,pay_input_values_f piv
,pay_element_types_f pet
,pay_run_results prr
WHERE pbf.balance_type_id = pbt.balance_type_id
AND (pbt.balance_name like p_balance_name||'%'
OR pbt.balance_name like nvl(p_standard_bal_name,p_balance_name)||'%'
OR pbt.balance_name like nvl(p_special_bal_name,p_balance_name)||'%')
AND piv.input_value_id = pbf.input_value_id
AND piv.name ='Pay Value'
AND pet.element_type_id = piv.element_type_id
AND pet.classification_id <> p_classification_id /*(SELECT classification_id
from pay_element_classifications
where classification_name ='Balance Initialization'
and business_group_id is null
and legislation_code is null) */ -- Bug 5107780
AND prr.element_type_id = pet.element_type_id
AND prr.assignment_action_id = p_assignment_action_id
AND p_date_earned BETWEEN pbf.effective_start_date
AND pbf.effective_end_date
AND p_date_earned BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND p_date_earned BETWEEN piv.effective_start_date
AND piv.effective_end_date;
SELECT pac.context_id context_id
,pac.context_value value
FROM ff_contexts ff
,pay_action_contexts pac
WHERE ff.context_name = p_context_name
AND pac.context_id = ff.context_id
AND pac.assignment_Action_id = p_run_assign_action_id;
SELECT sum(fnd_number.canonical_to_number(prrv.result_value)) result_value
,pet.processing_priority priority
FROM pay_balance_feeds_f pbf
,pay_balance_types pbt
,pay_input_values_f piv
,pay_element_types_f pet
-- ,pay_element_types_f_tl pettl Bug 5107780
,pay_run_results prr
,pay_run_result_values prrv
,pay_element_entries_f pee
WHERE pbf.balance_type_id = pbt.balance_type_id
AND prr.source_id = pee.element_entry_id
AND pee.CREATOR_TYPE = p_creator_type
AND pbt.balance_name like p_balance_name|| '%'
AND piv.input_value_id = pbf.input_value_id
AND piv.name ='Pay Value'
AND pet.element_type_id = piv.element_type_id
AND pet.classification_id <> p_classification_id /*(SELECT classification_id
from pay_element_classifications
where classification_name ='Balance Initialization'
and business_group_id is null
and legislation_code is null) */ -- Bug 5107780
AND prr.element_type_id = pet.element_type_id
AND prr.assignment_action_id = p_assignment_action_id
AND prrv.run_result_id = prr.run_result_id
-- AND pet.element_type_id = pettl.element_type_id Bug 5107780
-- AND pettl.language = USERENV('LANG') Bug 5107780
AND p_date_earned BETWEEN pbf.effective_start_date
AND pbf.effective_end_date
AND p_date_earned BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND p_date_earned BETWEEN piv.effective_start_date
AND piv.effective_end_date
and /*pay_nl_general.get_retro_period(prr.source_id,p_date_earned)*/ prr.start_date = p_retro_date -- Bug 5107780
group by pet.processing_priority;
SELECT sum(fnd_number.canonical_to_number(prrv2.result_value))
,prrv1.result_value
,pet.processing_priority
FROM pay_balance_feeds_f pbf
,pay_balance_types pbt
,pay_input_values_f piv
,pay_input_values_f piv1
,pay_input_values_f piv2
,pay_element_types_f pet
-- ,pay_element_types_f_tl pettl Bug 5107780
,pay_run_results prr
,pay_run_result_values prrv
,pay_run_result_values prrv1
,pay_run_result_values prrv2
,pay_element_entries_f pee
WHERE pbf.balance_type_id = pbt.balance_type_id
AND prr.source_id = pee.element_entry_id
AND pee.CREATOR_TYPE = p_creator_type
AND pbt.balance_name like p_balance_name
AND piv.input_value_id = pbf.input_value_id
AND piv.name ='Pay Value'
AND pet.element_type_id = piv.element_type_id
AND pet.classification_id <> p_classification_id /*(SELECT classification_id
from pay_element_classifications
where classification_name ='Balance Initialization'
and business_group_id is null
and legislation_code is null) */ -- Bug 5107780
AND piv1.element_type_id = pet.element_type_id
AND piv1.name = 'SI Type Name'
AND piv2.element_type_id = pet.element_type_id
AND piv2.name = 'SI Type'
AND prr.element_type_id = pet.element_type_id
AND prr.assignment_action_id = p_assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND prrv.input_value_id = piv2.input_value_id
AND prrv.result_value = p_si_type
AND prrv1.run_result_id = prrv.run_result_id
AND prrv1.input_value_id = piv1.input_value_id
AND prrv2.run_result_id = prrv.run_result_id
AND prrv2.input_value_id = piv.input_value_id
-- AND pet.element_type_id = pettl.element_type_id Bug 5107780
-- AND pettl.language = USERENV('LANG') Bug 5107780
AND p_date_earned BETWEEN pbf.effective_start_date
AND pbf.effective_end_date
AND p_date_earned BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND p_date_earned BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND p_date_earned BETWEEN piv1.effective_start_date
AND piv1.effective_end_date
AND p_date_earned BETWEEN piv2.effective_start_date
AND piv2.effective_end_date
and /*pay_nl_general.get_retro_period(prr.source_id,p_date_earned)*/ prr.start_date = p_retro_date
group by prrv1.result_value,pet.processing_priority; -- Bug 5107780
SELECT NVL(pbt_tl.reporting_name,pbt_tl.balance_name) bal_name
FROM pay_balance_types pbt
,pay_balance_types_tl pbt_tl
WHERE pbt.balance_name = p_balance_name
AND pbt.balance_type_id = pbt_tl.balance_type_id
AND pbt_tl.language = USERENV('LANG');
SELECT classification_id
from pay_element_classifications
where classification_name ='Balance Initialization'
and business_group_id is null
and legislation_code is null;
SELECT pre.locked_action_id pre_assignment_action_id,
passact.source_action_id master_pre_asg_action_id,
pay.locked_action_id master_assignment_action_id,
assact.assignment_id assignment_id,
assact.payroll_action_id pay_payroll_action_id,
paa.effective_date effective_date,
ppaa.effective_date pre_effective_date,
paa.date_earned date_earned,
paa.time_period_id time_period_id,
paa.payroll_id payroll_id
FROM pay_action_interlocks pre,
pay_action_interlocks pay,
pay_payroll_actions paa,
pay_payroll_actions ppaa,
pay_assignment_actions assact,
pay_assignment_actions passact
WHERE pre.locked_action_id = pay.locking_action_id
AND pre.locking_action_id = p_locking_action_id
AND pre.locked_action_id = passact.assignment_action_id
AND passact.assignment_id =assact.assignment_id
AND passact.payroll_action_id = ppaa.payroll_action_id
AND ppaa.action_type IN ('P','U')
AND pay.locked_action_id = assact.assignment_action_id
AND assact.payroll_action_id = paa.payroll_action_id
AND assact.source_action_id IS NULL
ORDER BY pay.locked_action_id DESC;
select max(paa.assignment_Action_id) max_assact from
pay_payroll_actions ppa,
pay_assignment_Actions paa,
pay_assignment_Actions paa1
where paa1.assignment_Action_id = p_assignment_action_id
and paa1.payroll_action_id = ppa.payroll_action_id
and paa.assignment_id = paa1.assignment_id
and paa.payroll_action_id = ppa.payroll_action_id;
SELECT paa.assignment_action_id child_assignment_action_id,
'S' run_type
FROM pay_assignment_actions paa,
pay_run_types_f prt
WHERE paa.source_action_id = p_master_assignment_action
AND paa.payroll_action_id = p_payroll_action_id
AND paa.assignment_id = p_assignment_id
AND paa.run_type_id = prt.run_type_id
AND prt.run_method = 'S'
AND p_effective_date BETWEEN prt.effective_start_date
AND prt.effective_end_date
UNION
SELECT paa.assignment_action_id child_assignment_action_id,
'NP' run_type --Standard Run, Process Separate Run
FROM pay_assignment_actions paa
WHERE paa.payroll_action_id = p_payroll_action_id
AND paa.assignment_id = p_assignment_id
AND paa.action_sequence = (
SELECT MAX(paa1.action_sequence)
FROM pay_assignment_actions paa1,
pay_run_types_f prt1
WHERE prt1.run_type_id = paa1.run_type_id
AND prt1.run_method IN ('N','P')
AND paa1.payroll_action_id = p_payroll_action_id
AND paa1.assignment_id = p_assignment_id
AND paa1.source_action_id = p_master_assignment_action
AND p_effective_date BETWEEN prt1.effective_start_date
AND prt1.effective_end_date);
SELECT paa.assignment_action_id np_assignment_action_id,
prt.run_method run_method
FROM pay_assignment_actions paa
,pay_run_types_f prt
WHERE paa.source_action_id = p_assignment_action_id
AND paa.payroll_action_id = p_payroll_action_id
AND paa.assignment_id = p_assignment_id
AND paa.run_type_id = prt.run_type_id
AND prt.run_method IN ('N','P')
AND p_effective_date BETWEEN prt.effective_start_date
AND prt.effective_end_date;
SELECT ptp.end_date end_date,
ptp.regular_payment_date regular_payment_date,
ptp.time_period_id time_period_id
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_assignment_action_id
AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date;
SELECT paa.chunk_number
INTO l_chunk_number
FROM pay_assignment_actions paa
WHERE paa.assignment_action_id = p_assignment_action_id;