[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT (get_parameter
(legislative_parameters
, 'LEGAL_EMPLOYER'
)
) LEGAL_EMPLOYER_ID
, (get_parameter
(legislative_parameters
, 'LU_REQUEST'
)
) LU_REQUEST
,(get_parameter
(legislative_parameters
, 'LOCAL_UNIT'
)
) LOCAL_UNIT_ID
,(get_parameter
(legislative_parameters
, 'YEAR'
)
) L_YEAR
, /*FND_DATE.canonical_to_date(effective_date)*/ effective_date, business_group_id bg_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
select /*o1.NAME LU_NAME,*/ hoi2.ORG_INFORMATION6 Insurance_Number
from HR_ORGANIZATION_UNITS o1
, HR_ORGANIZATION_INFORMATION hoi1
, HR_ORGANIZATION_INFORMATION hoi2
WHERE o1.business_group_id = g_business_group_id --3133
and hoi1.organization_id = o1.organization_id
and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER' --'SE_LOCAL_UNIT'
and hoi1.org_information_context = 'CLASS'
and o1.organization_id = hoi2.organization_id
and hoi2.ORG_INFORMATION_CONTEXT='SE_LEGAL_EMPLOYER_DETAILS' --'SE_LOCAL_UNIT_DETAILS'
and o1.organization_id = csr_v_legal_employer_id; --3134 --3135 --csr_local_unit_ID;
select o1.NAME LU_NAME,hoi2.ORG_INFORMATION2 CFAR
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.org_information1 = 'SE_LOCAL_UNIT'
and hoi1.org_information_context = 'CLASS'
and o1.organization_id = hoi2.organization_id
and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNIT_DETAILS'
and o1.organization_id = csr_local_unit_ID;
SELECT national_identifier, last_name || ' ' || first_name name ,DATE_OF_BIRTH
FROM
per_all_people_f WHERE
BUSINESS_GROUP_ID=g_business_group_id
AND person_id=csr_v_person_id
AND csr_v_end_date
BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
AND months_between (csr_v_end_date,DATE_OF_BIRTH) > 240;*/
SELECT national_identifier, last_name || ' ' || first_name name ,DATE_OF_BIRTH
FROM
per_all_people_f WHERE
BUSINESS_GROUP_ID=g_business_group_id
AND person_id=csr_v_person_id
/* AND csr_v_end_date
BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE*/
/* EFFECTIVE_END_DATE>=csr_v_start_date
AND EFFECTIVE_START_DATE <=csr_v_end_date
AND months_between (EFFECTIVE_END_DATE,DATE_OF_BIRTH) > 240;*/
SELECT national_identifier, last_name || ' ' || first_name name ,DATE_OF_BIRTH
FROM
per_all_people_f WHERE
BUSINESS_GROUP_ID=g_business_group_id
AND person_id=csr_v_person_id
AND csr_v_end_date
BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
AND months_between (csr_v_end_date,DATE_OF_BIRTH) > 240
ORDER BY last_name || ' ' || first_name;
SELECT effective_start_date FROM per_all_assignments_f
WHERE person_id=csr_v_person_id --21233
AND csr_v_end_date
BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
AND primary_flag='Y'
AND employee_category='WC' ;
SELECT MAX(effective_start_date) FROM per_all_people_f papf WHERE
CURRENT_EMPLOYEE_FLAG IS NULL
AND person_id=csr_v_person_id--21257
AND EFFECTIVE_START_DATE --'31-dec-2000'
BETWEEN csr_v_start_date AND csr_v_end_date /*'01-jan-2000' AND csr_v_end_date '31-dec-2000'*/
AND NOT EXISTS
(SELECT 1 FROM per_all_people_f papf1 WHERE
CURRENT_EMPLOYEE_FLAG='Y'
AND person_id=papf.person_id --21257
AND papf1.effective_start_date >papf.effective_start_date
);
/*SELECT effective_start_date,effective_end_date,pj.JOB_INFORMATION1,employee_category
FROM
per_all_people_f papf,
per_jobs pj
WHERE person_id=csr_v_person_id --21257 --21233
AND csr_v_start_date<=EFFECTIVE_END_DATE AND
csr_v_end_date>=EFFECTIVE_START_DATE
AND primary_flag='Y'
AND pj.job_id=papf.job_id
AND papf.job_id IS NOT NULL
AND papf.emloyee_category IS NOT NULL*/
SELECT paaf.effective_start_date,paaf.effective_end_date,
decode(pj.JOB_INFORMATION1,'Y','M',null) job,paaf.employee_category,
payroll_id,
hsck.segment2 local_unit_id
FROM
per_all_assignments_f paaf,
per_jobs pj , --new
hr_soft_coding_keyflex hsck
WHERE assignment_id=csr_v_assignment_id --21197 --21257 --21233
AND csr_v_start_date <=paaf.EFFECTIVE_END_DATE AND
csr_v_end_date >=paaf.EFFECTIVE_START_DATE
AND primary_flag='Y'
AND paaf.assignment_status_type_id <>3
AND pj.job_id(+)=paaf.job_id
-- AND paaf.employee_category IN ('BC','WC')
AND paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id --new
--AND hsck.segment2=to_char(csr_v_local_unit_id) --3268) --new
AND (paaf.job_id IS NOT NULL
OR paaf.employee_category IS NOT NULL);
/*SELECT JOB_INFORMATION1 FROM per_jobs pj, per_roles pr
WHERE pj.job_id=pr.job_id
AND pj.JOB_INFORMATION_CATEGORY='SE'
AND pr.person_id=csr_v_person_id; --21257 */
SELECT JOB_INFORMATION1,start_date, start_date+(e_date-start_date-1) end_date
FROM
(
SELECT JOB_INFORMATION1,start_date,lead( start_date, 1, to_date('31-12-4713','dd-mm-yyyy') )
over (order by start_date ASC) e_date
FROM per_jobs pj, per_roles pr
WHERE pj.job_id=pr.job_id
AND pj.JOB_INFORMATION_CATEGORY='SE'
AND pr.person_id=csr_v_person_id /*21197*/)
WHERE start_date<=csr_v_end_date --'31-dec-2005'
AND start_date+(e_date-start_date-1)>=csr_v_start_date; /*'01-jan-2005'*/
SELECT DISTINCT employee_category ,EFFECTIVE_START_DATE
FROM per_all_assignments_f
WHERE person_id=csr_v_person_id --21257 --21233
AND csr_v_start_date<=EFFECTIVE_END_DATE AND
csr_v_end_date>=EFFECTIVE_START_DATE
AND primary_flag='Y'
ORDER BY EFFECTIVE_START_DATE;
SELECT MAX(EFFECTIVE_end_DATE) FROM
per_all_assignments_f
WHERE person_id=csr_v_person_id --21257 --21233
AND csr_v_start_date<=EFFECTIVE_END_DATE AND
csr_v_end_date>=EFFECTIVE_START_DATE
AND employee_category='BC'
AND primary_flag='Y';
/*SELECT employee_category FROM per_all_assignments_f
WHERE person_id=csr_v_person_id --21233
AND csr_v_end_date
BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
AND primary_flag='Y';*/
SELECT o1.NAME legal_employer_name
-- , hoi2.org_information2 org_number
-- , hoi1.organization_id legal_id
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_employer_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 = 'SE_LEGAL_EMPLOYER_DETAILS';
SELECT DISTINCT papf.person_id ,paaf.assignment_id
FROM per_all_assignments_f paaf,
per_all_people_f papf,
hr_soft_coding_keyflex hsck
WHERE papf.business_group_id=csr_v_business_group_id -- 3133 --paaf.assignment_id = p_assignment_id
AND paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
AND papf.person_id=paaf.person_id
AND paaf.primary_flag='Y'
AND hsck.segment2=to_char(csr_v_local_unit_id) --3268)
-- AND csr_v_effective_date /*'01-jan-2006'*/ BETWEEN paaf.effective_start_date
--AND paaf.effective_end_date
--AND csr_v_effective_date /*'01-jan-2006'*/ BETWEEN papf.effective_start_date
--AND papf.effective_end_date
AND csr_v_end_date >= paaf.effective_start_date
AND csr_v_effective_date <= paaf.effective_end_date
AND csr_v_end_date >= papf.effective_start_date
AND csr_v_effective_date <= papf.effective_end_date
AND papf.CURRENT_EMPLOYEE_FLAG='Y'
AND paaf.employee_category IN ('WC','BC')
AND ADD_MONTHS(date_of_birth,252) <= /*'31-dec-2001'*/ csr_v_end_date
AND nvl(hsck.segment10,'N')='N' /* Person is not CEO */
AND nvl(hsck.segment11,'N')='N' /* Person is not Owner/Joint Owner */
ORDER BY papf.person_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 = 'SE'
AND ue.business_group_id is NULL
AND ue.creator_type = 'B';
SELECT MAX(assignment_action_id)
FROM pay_Assignment_actions WHERE
payroll_action_id=csr_v_payroll_action_id; --23
SELECT hoi2.ORG_INFORMATION1 local_unit_id
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_unit_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='SE_LOCAL_UNITS';
SELECT o1.NAME local_unit_name
-- , hoi2.org_information2 org_number
-- , hoi1.organization_id legal_id
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 = 'SE_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id = hoi2.organization_id
AND hoi2.org_information_context = 'SE_LOCAL_UNIT_DETAILS';
SELECT START_DATE,end_date FROM per_time_periods WHERE payroll_id=csr_v_payroll_id --3469
AND /*'15-jan-2005'*/ csr_v_effective_date BETWEEN START_DATE AND end_date;
SELECT min(effective_start_date)
FROM per_all_assignments_f
WHERE effective_start_date > csr_v_assignment_start_date --previous assignment start date
AND employee_category='WC'
AND assignment_id=csr_v_assignment_id;
SELECT final_process_date
FROM PER_PERIODS_OF_SERVICE
WHERE person_id=csr_v_person_id
AND actual_termination_date=csr_v_actual_termination;
SELECT hsck.segment2 FROM
per_all_assignments_f paaf,
hr_soft_coding_keyflex hsck
WHERE
paaf.assignment_id=csr_v_assignment_id
AND paaf.effective_start_date=csr_v_start_date
AND paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id;
SELECT hsck.segment2 FROM
per_all_assignments_f paaf,
hr_soft_coding_keyflex hsck
WHERE
paaf.assignment_id=csr_v_assignment_id
AND paaf.effective_start_date=
(SELECT min(effective_start_date)
FROM per_all_assignments_f
WHERE effective_start_date>csr_v_start_date
and assignment_id=paaf.assignment_id)
AND paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id;
'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';
-- Insert the report Parameters
pay_action_information_api.create_action_information
(p_action_information_id => l_action_info_id
, p_action_context_id => p_payroll_action_id
, p_action_context_type => 'PA'
, p_object_version_number => l_ovn
, p_effective_date => g_effective_date
, p_source_id => NULL
, p_source_text => NULL
, p_action_information_category => 'EMEA REPORT DETAILS'
, p_action_information1 => 'PYSEFORA'
, p_action_information2 => g_legal_employer_id
, p_action_information3 => L_LEGAL_EMPLOYER_NAME
, p_action_information4 => hr_general.decode_lookup('SE_REQUEST_LEVEL',g_LU_request)
, p_action_information5 => g_local_unit_id
, p_action_information6 => L_LOCAL_UNIT_NAME
, p_action_information7 => FND_NUMBER.NUMBER_TO_CANONICAL(g_year)
, p_action_information8 => to_char(l_insurance_number)
);
IF g_LU_request ='LU_SELECTED' THEN
/* THis is for Given LOCAL UNIT */
OPEN csr_CFAR_FROM_LU (g_local_unit_id);
/* no need to update the table, but calculate the balance values*/
ELSIF l_current_local_unit_id<>g_local_unit_id THEN
l_value:=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
P_ASSIGNMENT_ID =>l_assignment_id, --21348,
P_VIRTUAL_DATE=>l_asg_end_date);
emp_record.delete(csr_record);
/* no need to update the table, but calculate the balance values*/
ELSIF l_current_local_unit_id<>l_local_unit_id THEN
l_value:=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id,
P_ASSIGNMENT_ID =>l_assignment_id, --21348,
P_VIRTUAL_DATE=>l_asg_end_date);
emp_record.delete(csr_record);
'select 1 from dual where to_char(:payroll_action_id) = dummy';
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 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 action_information3, action_information4
, action_information5
FROM pay_action_information
WHERE action_context_type = 'PA'
AND action_context_id = csr_v_pa_id
AND action_information_category = 'EMEA REPORT INFORMATION'
AND action_information1 = 'PYSEHPDA'
AND action_information2 = 'LE';
SELECT TO_CHAR
(fnd_date.canonical_to_date (action_information5)
, 'YYYYMMDD'
) period_from
, TO_CHAR
(fnd_date.canonical_to_date (action_information6)
, 'YYYYMMDD'
) period_to
FROM pay_action_information
WHERE action_context_type = 'PA'
AND action_context_id = csr_v_pa_id
AND action_information_category = 'EMEA REPORT DETAILS'
AND action_information1 = 'PYSEHPDA';
SELECT *
FROM pay_action_information
WHERE action_context_type = 'AAP'
AND action_information_category = 'EMEA REPORT INFORMATION'
AND action_information1 = 'PYSEHPDA'
AND action_information3 = csr_v_pa_id
AND action_information2 = 'PER'
AND action_information15 = csr_v_le_id
ORDER BY action_information30;
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 distinct
pai.ACTION_INFORMATION7 Reporting_Year,
pai1.ACTION_INFORMATION3 Legal_Employer,
pai1.ACTION_INFORMATION4 Local_unit_id,
pai1.ACTION_INFORMATION5 Local_unit,
pai.ACTION_INFORMATION8 Insurance_Number--,
/*pai1.ACTION_INFORMATION6 Cfar_Number,
pai1.ACTION_INFORMATION7 Name,
pai1.ACTION_INFORMATION8 Employee_Category,
pai1.ACTION_INFORMATION9 White_Collar,
pai1.ACTION_INFORMATION10 Gross_Salary,
pai1.ACTION_INFORMATION11 Terminated,
pai1.ACTION_INFORMATION12 Painter*/
FROM
pay_action_information pai,
pay_payroll_actions ppa,
pay_action_information pai1
WHERE
pai.action_context_id = ppa.payroll_action_id
AND ppa.payroll_action_id =p_payroll_action_id --27021 --20162 --20264 --20165
AND pai.action_context_id = pai1.action_context_id
AND pai1.action_context_id= ppa.payroll_action_id
AND pai1.action_context_type='PA'
AND pai1.action_information2 = 'PER'
AND pai1.action_information1 = 'PYSEFORA'
AND pai1.action_information_category = 'EMEA REPORT INFORMATION'
AND pai1.ACTION_INFORMATION9=l_employee_category
AND pai.action_context_type = 'PA'
AND pai.action_information1 = 'PYSEFORA'
AND pai.action_information_category = 'EMEA REPORT DETAILS'
ORDER BY --pai1.ACTION_INFORMATION3,
pai1.ACTION_INFORMATION4 ;--pai1.ACTION_INFORMATION8 ;
SELECT distinct
pai.ACTION_INFORMATION7 Reporting_Year,
pai1.ACTION_INFORMATION3 Legal_Employer,
pai1.ACTION_INFORMATION4 Local_unit_id,
pai1.ACTION_INFORMATION5 Local_unit,
pai.ACTION_INFORMATION8 Insurance_Number,
pai1.ACTION_INFORMATION9 Employee_Category
/*pai1.ACTION_INFORMATION6 Cfar_Number,
pai1.ACTION_INFORMATION7 Name,
pai1.ACTION_INFORMATION8 Employee_Category,
pai1.ACTION_INFORMATION9 White_Collar,
pai1.ACTION_INFORMATION10 Gross_Salary,
pai1.ACTION_INFORMATION11 Terminated,
pai1.ACTION_INFORMATION12 Painter*/
FROM
pay_action_information pai,
pay_payroll_actions ppa,
pay_action_information pai1
WHERE
pai.action_context_id = ppa.payroll_action_id
AND ppa.payroll_action_id =p_payroll_action_id --27021 --20162 --20264 --20165
AND pai.action_context_id = pai1.action_context_id
AND pai1.action_context_id= ppa.payroll_action_id
AND pai1.action_context_type='PA'
AND pai1.action_information2 = 'PER'
AND pai1.action_information1 = 'PYSEFORA'
AND pai1.action_information_category = 'EMEA REPORT INFORMATION'
AND pai1.ACTION_INFORMATION9 =l_employee_category --IN ('BC','WC')
AND pai.action_context_type = 'PA'
AND pai.action_information1 = 'PYSEFORA'
AND pai.action_information_category = 'EMEA REPORT DETAILS'
ORDER BY --pai1.ACTION_INFORMATION3,
pai1.ACTION_INFORMATION9, pai1.ACTION_INFORMATION4 ;--pai1.ACTION_INFORMATION8 ;
SELECT
pai1.ACTION_INFORMATION6 Cfar_Number,
pai1.ACTION_INFORMATION7 Person_Number,
pai1.ACTION_INFORMATION8 Name,
pai1.ACTION_INFORMATION9 Employee_Category,
pai1.ACTION_INFORMATION10 White_Collar,
nvl(pai1.ACTION_INFORMATION11,0) Gross_Salary,
pai1.ACTION_INFORMATION12 Terminated,
pai1.ACTION_INFORMATION13 Painter
FROM
--pay_action_information pai,
pay_payroll_actions ppa,
pay_action_information pai1
WHERE
pai1.action_context_id = ppa.payroll_action_id
AND ppa.payroll_action_id =p_payroll_action_id --27021 --20162 --20264 --20165
/*AND pai.action_context_id = pai1.action_context_id*/
AND pai1.action_context_id= ppa.payroll_action_id
AND pai1.action_context_type='PA'
AND pai1.action_information2 = 'PER'
AND pai1.action_information1 = 'PYSEFORA'
AND pai1.action_information_category = 'EMEA REPORT INFORMATION'
AND pai1.ACTION_INFORMATION9=l_employee_category
AND pai1.ACTION_INFORMATION4=local_unit_id
/*AND pai.action_context_type = 'PA'
AND pai.action_information1 = 'PYSEFORA'
AND pai.action_information_category = 'EMEA REPORT DETAILS'*/
ORDER BY pai1.ACTION_INFORMATION8;
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;