[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
PAY_FI_ARCHIVE_TEL.GET_PARAMETER(legislative_parameters,'PENSION_INS_NUM') PPID
,TO_NUMBER ( PAY_FI_ARCHIVE_TEL.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER_ID') ) Legal
,TO_NUMBER (PAY_FI_ARCHIVE_TEL.GET_PARAMETER(legislative_parameters,'LOCAL_UNIT_ID') ) Local_unit
,PAY_FI_ARCHIVE_TEL.GET_PARAMETER(legislative_parameters,'REPORT') Annual_Report
,FND_DATE.CANONICAL_TO_DATE(PAY_FI_ARCHIVE_TEL.GET_PARAMETER(legislative_parameters,'REF_DATE'))
,PAY_FI_ARCHIVE_TEL.GET_PARAMETER(legislative_parameters,'ARCHIVE') ARCHIVE_OR_NOT
-- ,PAY_FI_ARCHIVE_TEL.GET_PARAMETER(legislative_parameters,'TEST') TEST_OR_NOT
,effective_date Effective_date
,business_group_id BG_ID FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT o1.name,
hoi2.ORG_INFORMATION3,
hoi2.ORG_INFORMATION4,
hoi2.ORG_INFORMATION5,
hoi2.ORG_INFORMATION8
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id =g_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_legal_emp_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='FI_PENSION_PROVIDERS'
AND hoi2.org_information6=csr_v_pension_ins_num;
SELECT o1.NAME
FROM hr_organization_units o1
, hr_organization_information hoi1
WHERE o1.business_group_id =g_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_pension_provider_id
AND hoi1.org_information_context = 'CLASS'
AND hoi1.org_information1 = 'FR_PENSION' ;
SELECT o1.NAME,hoi2.ORG_INFORMATION2
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id =g_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_pension_provider_id
AND hoi1.org_information_context = 'CLASS'
AND hoi1.org_information1 = 'FR_PENSION'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='FI_PENSION_TYPES'
AND hoi2.org_information1='TEL';
SELECT hoi2.ORG_INFORMATION3
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id =g_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_pension_provider_id
AND hoi1.org_information_context = 'CLASS'
AND hoi1.org_information1 = 'FR_PENSION'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='FI_PENSION_DEPARTMENT_CODES';
SELECT o1.name , hoi2.ORG_INFORMATION1
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id =g_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_local_unit_id
AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='FI_LOCAL_UNIT_DETAILS';
SELECT hoi_le.org_information1 local_unit_id,
hou_lu.NAME local_unit_name,
hoi_lu.org_information1
FROM hr_organization_units hou_le,
hr_organization_information hoi_le,
hr_organization_units hou_lu,
hr_organization_information hoi_lu
WHERE hoi_le.organization_id = hou_le.organization_id
AND hou_le.organization_id = csr_v_legal_employer_id
AND hoi_le.org_information_context = 'FI_LOCAL_UNITS'
AND hou_lu.organization_id = hoi_le.org_information1
AND hou_lu.organization_id = hoi_lu.organization_id
AND hoi_lu.org_information_context = 'FI_LOCAL_UNIT_DETAILS';
SELECT hoi2.ORG_INFORMATION2
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id =l_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_local_unit_id
AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='FI_LU_PENSION_PROVIDERS'
AND hoi2.org_information1=csr_v_pension_ins_num ;
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
,hr_soft_coding_keyflex hsck
, per_all_people_f pap
WHERE ppa.payroll_action_id = p_payroll_action_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 as1.person_id = pap.person_id
AND pap.per_information24 = p_pension_ins_num
AND ppa.effective_date BETWEEN as1.effective_start_date
AND as1.effective_end_date
AND ppa.effective_date BETWEEN pap.effective_start_date
AND pap.effective_end_date
AND act.action_status = 'C' -- Completed
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status = 'C' -- Completed
AND act1.payroll_action_id = appa2.payroll_action_id
AND appa2.action_type IN ('P','U')
AND appa2.effective_date BETWEEN l_canonical_start_date
AND l_canonical_end_date
-- Prepayments or Quickpay Prepayments
AND hsck.SOFT_CODING_KEYFLEX_ID=as1.SOFT_CODING_KEYFLEX_ID
AND hsck.segment2 = to_char(p_local_unit_id)
AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
AND act.TAX_UNIT_ID = p_legal_employer_id
ORDER BY act.assignment_id;
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
, per_all_people_f pap
WHERE ppa.payroll_action_id = p_payroll_action_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 as1.person_id = pap.person_id
AND pap.per_information24 = p_pension_ins_num
AND ppa.effective_date BETWEEN as1.effective_start_date
AND as1.effective_end_date
AND ppa.effective_date BETWEEN pap.effective_start_date
AND pap.effective_end_date
AND act.action_status = 'C' -- Completed
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status = 'C' -- Completed
AND act1.payroll_action_id = appa2.payroll_action_id
AND appa2.action_type IN ('P','U')
AND appa2.effective_date BETWEEN l_canonical_start_date
AND l_canonical_end_date
-- Prepayments or Quickpay Prepayments
AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
AND act.TAX_UNIT_ID = p_legal_employer_id
ORDER BY act.assignment_id;
SELECT ue.creator_id
FROM ff_user_entities ue,
ff_database_items di
WHERE di.user_name = csr_v_Balance_Name
AND ue.user_entity_id = di.user_entity_id
AND ue.legislation_code = 'FI'
AND ue.business_group_id is NULL
AND ue.creator_type = 'B';
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
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 = 'FI' )
AND (u.business_group_id IS NULL )
AND u.creator_type = 'B';
SELECT ue.creator_id
FROM ff_user_entities ue,
ff_database_items di
WHERE di.user_name = csr_v_Balance_Name
AND ue.user_entity_id = di.user_entity_id
AND ue.legislation_code = 'FI'
AND ue.business_group_id is NULL
AND ue.creator_type = 'B';
l_EIT_Insert_or_Update per_people_extra_info.PEI_INFORMATION6%TYPE;
SELECT hoi2.ORG_INFORMATION4, hoi2.ORG_INFORMATION5, hoi2.ORG_INFORMATION8, hoi2.ORG_INFORMATION10
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id =g_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_legal_emp_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='FI_PENSION_PROVIDERS'
AND hoi2.org_information6=csr_v_pension_ins_num
AND csr_v_effective_date BETWEEN fnd_date.canonical_to_date(hoi2.org_information1) AND
nvl(fnd_date.canonical_to_date(hoi2.org_information2),to_date('31/12/4712','DD/MM/YYYY')) ;
SELECT pap.LAST_NAME,pap.PRE_NAME_ADJUNCT,pap.FIRST_NAME,
pap.national_identifier ,
pap.person_id ,
pap.PER_INFORMATION14,
pap.PER_INFORMATION15,
pap.PER_INFORMATION16,
pap.PER_INFORMATION17,
pap.PER_INFORMATION20,
pap.PER_INFORMATION21,
pap.PER_INFORMATION24,
pac.assignment_id
FROM
pay_assignment_actions pac,
per_all_assignments_f paa,
per_all_people_f pap
WHERE pac.assignment_action_id = p_asg_act_id
AND paa.assignment_id = pac.assignment_id
AND paa.person_id = pap.person_id
AND pap.per_information_category = 'FI'
and p_effective_date between pap.effective_Start_date AND pap.effective_end_date
and p_effective_date between paa.effective_Start_date AND paa.effective_end_date;
SELECT scl.segment2,scl.segment8
FROM PER_ALL_ASSIGNMENTS_F paa
,HR_SOFT_CODING_KEYFLEX scl
,pay_assignment_actions pasa
WHERE pasa.ASSIGNMENT_ACTION_ID = p_assignment_action_id
AND pasa.ASSIGNMENT_ID = paa.ASSIGNMENT_ID
AND scl.soft_coding_keyflex_id=paa.soft_coding_keyflex_id
AND paa.primary_flag='Y'
AND p_effective_date between paa.effective_Start_date AND paa.effective_end_date;
SELECT o1.name , hoi2.ORG_INFORMATION1
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id =g_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_local_unit_id
AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='FI_LOCAL_UNIT_DETAILS';
select PERSON_EXTRA_INFO_ID,
object_version_number,
person_id,
information_type,
pei_information_category,
pei_information1,
pei_information2,
pei_information3,
pei_information4,
pei_information5,
pei_information6,
pei_information7
from per_people_extra_info
where information_type='FI_PENSION'
AND PEI_INFORMATION_CATEGORY='FI_PENSION'
AND PEI_INFORMATION3=CSR_V_COLUMN_NAME
AND PERSON_ID = CSR_V_PERSON_ID;
select PERSON_EXTRA_INFO_ID,
object_version_number,
person_id,
information_type,
pei_information_category,
pei_information1,
pei_information2,
pei_information3,
pei_information4,
pei_information5,
pei_information6,
pei_information7
from per_people_extra_info
where information_type='FI_PENSION'
AND PEI_INFORMATION_CATEGORY='FI_PENSION'
AND PERSON_ID = CSR_V_PERSON_ID;
SELECT hoi2.ORG_INFORMATION3
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id =g_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_pension_provider_id
AND hoi1.org_information_context = 'CLASS'
AND hoi1.org_information1 = 'FR_PENSION'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='FI_PENSION_DEPARTMENT_CODES'
AND hoi2.ORG_INFORMATION1 = g_legal_employer_id
AND hoi2.ORG_INFORMATION2 = csr_v_Local_unit_id;
SELECT * FROM (
SELECT PAA.DATE_START,PAA.DATE_END,PAAT.INFORMATION1
FROM PER_ABSENCE_ATTENDANCES PAA,PER_ABSENCE_ATTENDANCE_TYPES PAAT
WHERE PAA.BUSINESS_GROUP_ID = g_business_group_id
AND PAA.PERSON_ID = CSR_V_PERSON_ID
AND PAAT.BUSINESS_GROUP_ID = g_business_group_id
AND PAAT.INFORMATION_CATEGORY = 'FI'
AND PAAT.ABSENCE_ATTENDANCE_TYPE_ID = PAA.ABSENCE_ATTENDANCE_TYPE_ID
AND PAAT.DATE_EFFECTIVE <= csr_v_start_effective_date
AND PAA.DATE_START BETWEEN csr_v_start_effective_date
AND csr_v_end_effective_date
ORDER BY PAA.DATE_START DESC )
WHERE ROWNUM <4;
SELECT 1 INTO l_flag
FROM pay_action_information
WHERE action_information_category = 'EMEA REPORT DETAILS'
AND action_information1 = 'PYFITELA'
AND action_context_id = p_assignment_action_id;