[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
PAY_NO_PACCR_PKG.GET_PARAMETER(legislative_parameters,'PAYROLL')
,PAY_NO_PACCR_PKG.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER')
,PAY_NO_PACCR_PKG.GET_PARAMETER(legislative_parameters,'RESTRICT_EMPLR_CONTR')
,PAY_NO_PACCR_PKG.GET_PARAMETER(legislative_parameters,'ELEMENT_NAME')
,PAY_NO_PACCR_PKG.GET_PARAMETER(legislative_parameters,'EOY_CODE')
,PAY_NO_PACCR_PKG.GET_PARAMETER(legislative_parameters,'COSTING_SEG')
,effective_date
,fnd_date.canonical_to_date(PAY_NO_PACCR_PKG.GET_PARAMETER(legislative_parameters,'REPORT_START_DATE'))
,fnd_date.canonical_to_date(PAY_NO_PACCR_PKG.GET_PARAMETER(legislative_parameters,'REPORT_END_DATE'))
,PAY_NO_PACCR_PKG.GET_PARAMETER(legislative_parameters,'ARCHIVE')
,business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT etei.eei_information3
FROM pay_element_type_extra_info etei
WHERE etei.element_type_id = l_element_type_id
AND etei.eei_information_category = 'NO_EOY_REPORTING_CODE_MAPPING'
AND to_number(to_char(l_effective_date,'YYYY'))
between to_number(etei.eei_information1) and to_number(nvl(etei.eei_information2,'4712'));
SELECT count(*)
FROM pay_action_information
WHERE action_information_category = 'EMEA REPORT DETAILS'
AND action_information1 = 'PYNOPACCA'
AND action_context_id = pactid;
SELECT hou.name NAME
, hoi.org_information1 ORGANIZATION_NO
FROM hr_organization_units hou
,hr_organization_information hoi
WHERE hou.business_group_id = p_business_group_id
and hoi.organization_id = hou.organization_id
and hoi.org_information_context = 'NO_LEGAL_EMPLOYER_DETAILS'
and hou.organization_id = p_le_id
and p_effective_date BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, p_effective_date) ;
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';
SELECT ppf.payroll_name INTO l_payroll_name
FROM
pay_all_payrolls_f ppf
WHERE ppf.payroll_id=l_payroll_id;
SELECT nvl(petf.reporting_name,petf.element_name) INTO l_ele_name
FROM
pay_element_types_f petf
WHERE petf.element_type_id=l_ele_type_id;
SELECT effective_date
,fnd_date.CANONICAL_TO_DATE(action_information5) from_date
,fnd_date.CANONICAL_TO_DATE(action_information6) to_date
,to_number(action_information2) business_group_id
,to_number(action_information3) payroll_id
,action_information10 eoy_code
,tax_unit_id
,to_number(action_information11) ele_type_id
,action_information14 restr_econtr
,action_information9 cost_seg
FROM pay_action_information pai
WHERE action_context_type = 'PA'
AND action_context_id = p_payroll_action_id
AND action_information_category = 'EMEA REPORT DETAILS'
AND action_information1 = 'PYNOPACCA'
AND action_information4 = 'HDR';
SELECT to_number(hoi.ORG_INFORMATION1) lu_id
FROM HR_ORGANIZATION_UNITS hou
, HR_ORGANIZATION_INFORMATION hoi
WHERE hou.business_group_id = p_bg_id
AND hou.organization_id = p_le_id
AND hoi.ORG_INFORMATION_CONTEXT='NO_LOCAL_UNITS'
AND hoi.organization_id = hou.organization_id;
SELECT distinct pet.element_type_id ELE_TYPE_ID
-- Modified for bug fix 5239796
,nvl(pet.reporting_name,pet.element_name) ELE_NAME
-- ,pet.element_information1 ELE_EOY_CODE
,paaf.assignment_id ASG_ID
,paaf.assignment_number ASSIGNMENT_NUMBER
,ppf.payroll_name PAYROLL_NAME
,pcak.concatenated_segments ELE_COST_SEG
,pc.cost_allocation_keyflex_id COST_FLEX_ID
,pc.debit_or_credit DEBIT_CREDIT
,pc.costed_value COSTED_VALUE
,ppa.effective_date EFFECTIVE_DATE
,pc.balance_or_cost COST_OR_BAL
,papf.full_name EMP_NAME
,prr.run_result_id RR_ID -- not in use
FROM
pay_payroll_actions ppa
, pay_payrolls_f ppf
, per_all_people_f papf
, per_all_assignments_f paaf
, pay_assignment_actions paa
, hr_soft_coding_keyflex hsck
, pay_element_types_f pet
, pay_input_values_f piv
, pay_run_results prr
, pay_costs pc
, pay_cost_allocation_keyflex pcak
WHERE paaf.person_id BETWEEN p_start_person AND p_end_person
AND papf.person_id = paaf.person_id
-- Added for bug 5242754 - Start
AND paaf.effective_start_date <= p_end_date
AND paaf.effective_end_date >= p_start_date
AND papf.current_employee_flag = 'Y'
-- Added for bug 5242754 - End
AND ppf.payroll_id = nvl(p_payroll_id,ppf.payroll_id)
AND paaf.payroll_id = ppf.payroll_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_id = paaf.assignment_id
AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND hsck.enabled_flag = 'Y'
AND hsck.segment2 = nvl(to_char(p_local_unit),hsck.segment2)
AND pet.element_type_id = nvl(p_element_type_id,pet.element_type_id)
-- AND nvl(pet.element_information1,0) = nvl(p_eoy_code,nvl(pet.element_information1,0))
AND (pet.business_group_id = p_business_group_id OR pet.legislation_code='NO')
AND pet.element_type_id = piv.element_type_id
-- Modified for bug fix 5242486
AND pet.classification_id IN
(select pec2.classification_id
from pay_element_classifications pec1
, pay_element_classifications pec2
-- Added for bug fix 5242486
, pay_sub_classification_rules_f pscrf
where pec2.classification_id = pec1.parent_classification_id (+)
and nvl(pec1.classification_name,'0') like decode(p_restr_econtr
, 'Y','%Subject%to%Employer%Contributions%'
, '%')
and pec2.classification_id = pet.classification_id
-- Added for bug fix 5242486
-- Modified for bug fix 6069852
and pscrf.element_type_id = pet.element_type_id
--and pscrf.classification_id = pec1.classification_id
and ppa.date_earned between pscrf.effective_start_date and pscrf.effective_end_date
-- Added for bug fix 6069852
UNION ALL
select pec2.classification_id
from pay_element_classifications pec1
, pay_element_classifications pec2
where pec2.classification_id = pec1.parent_classification_id (+)
and nvl(pec1.classification_name,'0') like decode(p_restr_econtr
, 'N','%')
and pec2.classification_id = pet.classification_id
)
AND piv.name ='Pay Value'
AND nvl(pc.distributed_input_value_id, pc.input_value_id) = piv.input_value_id
AND prr.element_type_id = pet.element_type_id
AND prr.run_result_id = pc.run_result_id
AND pc.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id
AND pcak.concatenated_segments like replace(nvl(p_cost_seg,'%'),'.','%.')||'%'
AND prr.assignment_action_id = paa.assignment_action_id
AND ppa.date_earned between pet.effective_start_date and pet.effective_end_date
AND ppa.date_earned between p_start_date and p_end_date
AND ppa.date_earned between paaf.effective_start_date and paaf.effective_end_date
AND ppa.date_earned between papf.effective_start_date and papf.effective_end_date
AND ppa.date_earned between piv.effective_start_date and piv.effective_end_date
AND ppa.date_earned between ppf.effective_start_date and ppf.effective_end_date;
/*SELECT eei_information1 ele_code
FROM pay_element_type_extra_info petei
WHERE information_type='NO_ELEMENT_CODES'
AND nvl(element_type_id,0) = p_ele_type_id
AND nvl(eei_information2,0) = nvl(p_leg_emp_id, 0)
AND rownum <=1; */
SELECT nvl((select eei_information1 from pay_element_type_extra_info petei
where petei.information_type='NO_ELEMENT_CODES'
and element_type_id = p_ele_type_id
and petei.eei_information2 = p_leg_emp_id
and rownum=1),
(select eei_information1 from pay_element_type_extra_info petei
where petei.information_type='NO_ELEMENT_CODES'
and element_type_id = p_ele_type_id
and eei_information2 is null
and rownum=1)) ele_code from dual;
SELECT pay_assignment_actions_s.NEXTVAL INTO l_actid FROM dual;
SELECT to_number(action_information7) element_type_id
,action_information8 element_name
,action_information9 eoy_code
,action_information10 concatenated_segments
,to_number(action_information11) cost_allocation_keyflex_id
,action_information12 debit_or_credit
,sum(fnd_number.canonical_to_number(action_information14)) costed_value
,action_information15 balance_or_cost
,tax_unit_id leg_emp_id
,to_number(action_information6) local_unit_id
,effective_date
FROM pay_action_information pai
WHERE action_context_type = 'AAP'
AND action_information3 = to_char(p_payroll_action_id)
AND action_information_category = 'EMEA REPORT INFORMATION'
AND action_information1 = 'PYNOPACCA'
AND action_information4 = 'DETL'
GROUP BY action_information10
,action_information11
,action_information9
,action_information7
,action_information8
,action_information15
,action_information12
,tax_unit_id
,action_information6
,effective_date;
/*SELECT eei_information1 ele_code
FROM pay_element_type_extra_info petei
WHERE information_type='NO_ELEMENT_CODES'
AND nvl(element_type_id,0) = p_ele_type_id
AND nvl(eei_information2,0) = nvl(p_leg_emp_id, 0)
AND rownum <=1; */
SELECT nvl((select eei_information1 from pay_element_type_extra_info petei
where petei.information_type='NO_ELEMENT_CODES'
and element_type_id = p_ele_type_id
and petei.eei_information2 = p_leg_emp_id
and rownum=1),
(select eei_information1 from pay_element_type_extra_info petei
where petei.information_type='NO_ELEMENT_CODES'
and element_type_id = p_ele_type_id
and eei_information2 is null
and rownum=1)) ele_code from dual;
SELECT *
FROM pay_action_information pai
WHERE action_context_type = 'PA'
AND action_context_id = p_payroll_action_id
AND action_information_category = 'EMEA REPORT DETAILS'
AND action_information1 = 'PYNOPACCA'
AND action_information4 = 'HDR';
SELECT *
FROM pay_action_information pai
WHERE action_context_type = 'PA'
AND action_context_id = p_payroll_action_id
AND action_information_category = 'EMEA REPORT DETAILS'
AND action_information1 = 'PYNOPACCA'
AND tax_unit_id = p_tax_unit_id
AND action_information4 = 'SUMM';
SELECT payroll_action_id
INTO l_payroll_action_id
FROM pay_payroll_actions ppa,
fnd_conc_req_summary_v fcrs,
fnd_conc_req_summary_v fcrs1
WHERE fcrs.request_id = fnd_global.conc_request_id
AND fcrs.priority_request_id = fcrs1.priority_request_id
AND ppa.request_id between fcrs1.request_id and fcrs.request_id
AND ppa.request_id = fcrs1.request_id;
SELECT *
FROM pay_action_information pai
WHERE action_context_type = 'PA'
AND action_context_id = p_payroll_action_id
AND action_information_category = 'EMEA REPORT DETAILS'
AND action_information1 = 'PYNOPACCA'
AND action_information4 = 'HDR';
SELECT action_information7 --ELE_TYPE_ID
,action_information8 --ELE_NAME
,action_information9 --ELE_EOY_CODE
,assignment_id --ASSIGNMENT_ID
,action_information5 --ASSIGNMENT_NUMBER
,action_information2 --PAYROLL_NAME
,action_information10 --ELE_COST_SEG
,action_information11 --COST_FLEX_ID
,action_information12 --DEBIT_CREDIT
,action_information14 --COSTED_VALUE
,effective_date --EFFECTIVE_DATE
,action_information15 --COST_OR_BAL
,action_information13 --ELE_CODE
,action_information16 --COST_EFFECTIVE_DATE
,action_information17 --EMP_NAME
FROM pay_action_information pai
WHERE action_context_type = 'AAP'
AND action_information3 = to_char(p_payroll_action_id)
AND action_information_category = 'EMEA REPORT INFORMATION'
AND action_information1 = 'PYNOPACCA'
AND tax_unit_id = p_tax_unit_id
AND action_information4 = 'DETL'
ORDER BY action_information10
,action_information9
,action_information8
,action_information15
,action_information12
,action_information5
,fnd_date.date_to_canonical(action_information16)
,fnd_number.canonical_to_number(action_information14)
,effective_date;
SELECT payroll_action_id
INTO l_payroll_action_id
FROM pay_payroll_actions ppa,
fnd_conc_req_summary_v fcrs,
fnd_conc_req_summary_v fcrs1
WHERE fcrs.request_id = fnd_global.conc_request_id
AND fcrs.priority_request_id = fcrs1.priority_request_id
AND ppa.request_id between fcrs1.request_id and fcrs.request_id
AND ppa.request_id = fcrs1.request_id;