[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT (pay_se_work_time_certificate.get_parameter
(legislative_parameters
,'PERSON_ID'
)
) person_id
, (pay_se_work_time_certificate.get_parameter
(legislative_parameters
,'ASSIGNMENT_ID'
)
) assignment_id
, (pay_se_work_time_certificate.get_parameter
(legislative_parameters
,'STILL_EMPLOYED'
)
) still_employed
, (pay_se_work_time_certificate.get_parameter
(legislative_parameters
,'SALARY_YEAR'
)
) income_salary_year
,effective_date effective_date
,business_group_id bg_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT paa.absence_attendance_id
-- ,paa.date_start
, GREATEST (paa.date_start, csr_v_start_date) startdate
,paa.time_start
-- ,paa.date_end
, LEAST (paa.date_end, csr_v_end_date) enddate
,paa.time_end
FROM per_absence_attendances paa
,per_absence_attendance_types pat
WHERE paa.person_id = csr_v_person_id
AND ( paa.date_start BETWEEN csr_v_start_date AND csr_v_end_date
OR paa.date_end BETWEEN csr_v_start_date AND csr_v_end_date
)
/* (
(paa.date_start >=CSR_V_start_date AND ( nvl(paa.date_end,CSR_V_end_date)<=CSR_V_end_date) or (nvl(paa.date_end,CSR_V_end_date)>=CSR_V_end_date) )
or
( (paa.date_start < CSR_V_start_date ) and nvl(paa.date_end,CSR_V_end_date)<=CSR_V_end_date)
)
*/
AND paa.absence_attendance_type_id =
pat.absence_attendance_type_id
AND pat.absence_category NOT IN ('V')
ORDER BY paa.date_start;
SELECT hl.location_code
,hl.description
,hl.address_line_1
,hl.address_line_2
,hl.address_line_3
,hl.postal_code
,hl.town_or_city
,hl.region_1
,hl.region_2
,ft.territory_short_name
FROM hr_organization_units hou
,hr_locations hl
,fnd_territories_vl ft
WHERE hl.location_id = csr_v_location_id
AND hl.country = ft.territory_code;
SELECT o.NAME
,hoi3.org_information2 "ORG_NUMBER"
,o.location_id
,o.organization_id
FROM hr_all_organization_units o
,hr_organization_information hoi1
,hr_organization_information hoi2
,hr_organization_information hoi3
WHERE o.business_group_id = g_business_group_id
AND hoi1.organization_id = o.organization_id
AND hoi1.org_information_context = 'CLASS'
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi2.organization_id = hoi1.organization_id
AND hoi2.org_information_context = 'SE_LOCAL_UNITS'
AND hoi2.org_information1 = csr_v_local_unit_id
AND o.organization_id = hoi3.organization_id
AND hoi3.org_information_context = 'SE_LEGAL_EMPLOYER_DETAILS';
SELECT hoi4.org_information3
FROM hr_organization_information hoi4
WHERE hoi4.organization_id = csr_v_legal_employer_id
AND hoi4.org_information_context = 'SE_ORG_CONTACT_DETAILS'
AND hoi4.org_information_id =
(SELECT MIN (org_information_id)
FROM hr_organization_information
WHERE organization_id = csr_v_legal_employer_id
AND org_information_context = 'SE_ORG_CONTACT_DETAILS'
AND org_information1 = 'PHONE');
SELECT past.per_system_status
,p.effective_start_date
,p.effective_end_date
FROM per_all_assignments_f p
,per_assignment_status_types past
WHERE p.business_group_id = g_business_group_id
AND p.effective_start_date =
(SELECT MAX (p1.effective_start_date)
FROM per_all_assignments_f p1
,per_assignment_status_types past1
WHERE p1.assignment_id = p.assignment_id
AND past1.assignment_status_type_id = p1.assignment_status_type_id
AND past1.per_system_status IN ('ACTIVE_ASSIGN' )
)
AND p.person_id = csr_v_person_id
AND p.assignment_id = csr_v_assignment_id
AND past.assignment_status_type_id = p.assignment_status_type_id
AND past.per_system_status IN ('ACTIVE_ASSIGN', 'TERM_ASSIGN');
SELECT MIN (p.effective_start_date) min_date
FROM per_all_assignments_f p
WHERE p.business_group_id = g_business_group_id
AND p.person_id = csr_v_person_id
AND p.assignment_id = csr_v_assignment_id;
SELECT *
FROM per_all_people_f p
WHERE p.business_group_id = g_business_group_id
AND p.person_id = csr_v_person_id
AND csr_v_effective_date BETWEEN p.effective_start_date
AND p.effective_end_date;
SELECT *
FROM per_all_assignments_f p
WHERE p.business_group_id = g_business_group_id
AND p.assignment_id = csr_v_assignment_id
AND p.person_id = csr_v_person_id
AND csr_v_effective_date BETWEEN p.effective_start_date
AND p.effective_end_date;
SELECT *
FROM per_assignment_extra_info
WHERE assignment_id = csr_v_assignment_id
AND information_type = csr_v_information_type;
SELECT *
FROM per_assignment_extra_info
WHERE assignment_id = csr_v_assignment_id
AND information_type = 'SE_WTC_TIME_WORKED_INFO'
AND aei_information1 = csr_v_year;
SELECT *
FROM hr_soft_coding_keyflex
WHERE soft_coding_keyflex_id = csr_v_soft_coding_keyflex_id;
SELECT REPLACE(UPPER(pbt.balance_name),' ' ,'_') balance_name , pbt.balance_name bname
FROM pay_balance_types pbt , pay_balance_categories_f pbc
WHERE pbc.legislation_code='SE'
AND pbt.business_group_id =p_business_group_id
AND pbt.balance_category_id = pbc.balance_category_id
AND pbc.category_name = p_balance_category_name ;
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 is NULL
AND ue.business_group_id = p_business_group_id
AND ue.creator_type = 'B';
SELECT last_day(l_report_start_date)
INTO l_reporting_date
FROM DUAL;
SELECT last_day(l_report_start_date)
INTO l_reporting_date
FROM DUAL;
'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
AND ppf.person_id = '''
|| g_person_id
|| '''
ORDER BY ppf.person_id';
'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 mod(l_number,10) INTO l_digit(l_count) from dual;
SELECT floor(l_number) INTO l_digit(l_number) from dual;
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 = 'PYSEWTCR'
AND action_information2 = 'LE';
SELECT action_information3 person_number
,action_information4 emp_last_name
,action_information5 emp_first_name
-- ,ACTION_INFORMATION6 HIRED_FROM
-- ,ACTION_INFORMATION7 HIRED_TO
--,TO_CHAR (fnd_date.canonical_to_date (action_information6)
--,'YYYY-MM-DD'
-- ) hired_from
,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(action_information6),'YYYYMMDD')) hired_from
,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(action_information7),'YYYYMMDD')) hired_to
-- ,TO_CHAR (fnd_date.canonical_to_date (action_information7)
-- ,'YYYY-MM-DD'
-- ) hired_to
,action_information8 still_employed
--,TO_CHAR (fnd_date.canonical_to_date (action_information9)
-- ,'YYYY-MM-DD'
--) absence_from
-- ,TO_CHAR (fnd_date.canonical_to_date (action_information10)
--,'YYYY-MM-DD'
--) absence_to
,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(action_information9),'YYYYMMDD')) absence_from
,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(action_information10),'YYYYMMDD')) absence_to
,action_information11 form_of_employment
,action_information12 work_taks
,action_information13 employed_temp_agency
,action_information14 employed_temp_work
,action_information15 reason
--,ACTION_INFORMATION16 NOTIFICATION_DATE
--,fnd_date.canonical_to_date(ACTION_INFORMATION16) NOTIFICATION_DATE
--,TO_CHAR (fnd_date.canonical_to_date (action_information16)
--,'YYYY-MM-DD'
--) notification_date
,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(action_information16),'YYYYMMDD')) notification_date
,action_information17 employees_request
,action_information18 termination_reason
,action_information19 absence_percentage
,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(action_information20),'YYYYMMDD'))employment_end_date
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 = 'PYSEWTCA'
AND action_information2 = 'WTC_PERSON1';
SELECT action_information3 continuous_offer
,action_information4 permanent_check
-- ,ACTION_INFORMATION5 PERMANENT_DATE
--,TO_CHAR (fnd_date.canonical_to_date (action_information5)
--,'YYYY-MM-DD'
--) permanent_date
,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(action_information5),'YYYYMMDD')) permanent_date_from
,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(action_information25),'YYYYMMDD')) permanent_date_to
,action_information6 time_limited_check
-- ,ACTION_INFORMATION7 TIME_LIMITED_FROM
-- ,ACTION_INFORMATION8 TIME_LIMITED_TO
--,TO_CHAR (fnd_date.canonical_to_date (action_information7)
--,'YYYY-MM-DD'
--) time_limited_from
--,TO_CHAR (fnd_date.canonical_to_date (action_information8)
--,'YYYY-MM-DD'
--) time_limited_to
,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(action_information7),'YYYYMMDD')) time_limited_from
,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(action_information8),'YYYYMMDD')) time_limited_to
,action_information9 other_check
,action_information10 other
,action_information11 full_time_check
,action_information12 full_time
,action_information13 part_time_check
,action_information14 part_time
,action_information15 working_percentage
,action_information16 various_working_time
,action_information17 offer_accepted
-- ,ACTION_INFORMATION18 DECLINE_DATE
-- ,ACTION_INFORMATION19 TIME_WORKED_FROM
-- ,ACTION_INFORMATION20 TIME_WORKED_TO
--, TO_CHAR (fnd_date.canonical_to_date (NVL(action_information18,NULL))
--,'YYYY-MM-DD'
--) decline_date
--,TO_CHAR (fnd_date.canonical_to_date (NVL(action_information19,NULL))
--,'YYYY-MM-DD'
--) time_worked_from
--,TO_CHAR (fnd_date.canonical_to_date (NVL(action_information20,NULL))
--,'YYYY-MM-DD'
--) time_worked_to
,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(action_information18),'YYYYMMDD')) decline_date
,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(action_information19),'YYYYMMDD')) time_worked_from
,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(action_information20),'YYYYMMDD')) time_worked_to
,action_information21 total_worked_hours
,action_information22 paid_sick_leave_days
,action_information23 teaching_load
,action_information24 aggrmnt_of_compn_signed --EOY 2008
,action_information26 teaching_load_check_box -- EOY 2008
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 = 'PYSEWTCA'
AND action_information2 = 'WTC_PERSON2';
SELECT action_information3 assign_full_time
,action_information4 assign_hours_week
,action_information5 assign_frequency
,action_information6 assign_part_time
,action_information7 assign_working_percentage
,action_information8 assign_various_work_time
,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(action_information9)) salary_year
,action_information10 assign_salary_paid_out
,action_information11 salary_amount
,action_information12 school_holiday_pay_amount
,action_information13 school_holiday_pay_box
,action_information14 no_of_paid_holiday_days
,action_information15 emp_with_holiday_pay
,action_information16 paid_days_off_duty_time
,action_information17 employed_educational_assoc
,action_information18 holiday_pay_amount
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 = 'PYSEWTCA'
AND action_information2 = 'WTC_PERSON3';
SELECT action_information3 holiday_duty
,action_information4 lay_off_period_paid_days
,action_information5 holiday_laid_off
--,TO_CHAR (fnd_date.canonical_to_date (action_information6)
--,'YYYY-MM-DD'
--) lay_off_from
--,TO_CHAR (fnd_date.canonical_to_date (action_information7)
--,'YYYY-MM-DD'
--) lay_off_to
-- ,ACTION_INFORMATION6 LAY_OFF_FROM
-- ,ACTION_INFORMATION7 LAY_OFF_TO
,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(action_information6),'YYYYMMDD')) lay_off_from
,FND_NUMBER.CANONICAL_TO_NUMBER(TO_CHAR(fnd_date.canonical_to_date(action_information7),'YYYYMMDD')) lay_off_to
,action_information8 other_information
,action_information9 legal_employer_name
,action_information10 org_number
,action_information11 phone_number
,action_information12 location_code
,action_information13 address_line_1
,action_information14 address_line_2
,action_information15 address_line_3
,action_information16 postal_code
,action_information17 town_or_city
,action_information18 region_1
,action_information19 region_2
,action_information20 territory_short_name
,action_information21 hourly_pay_variable
,action_information22 hourly_overtime_rate
,action_information23 hourly_addl_suppl_time
,action_information24 other_taxable_compensation
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 = 'PYSEWTCA'
AND action_information2 = 'WTC_PERSON4';
SELECT action_information28 GROUP_ID
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 = 'PYSEWTCA'
AND action_information2 = 'WTC_PERSON5'
GROUP BY action_information28;
SELECT ROWNUM
,action_information3 jan_days
,action_information4 feb_days
,action_information5 mar_days
,action_information6 apr_days
,action_information7 may_days
,action_information8 jun_days
,action_information9 jul_days
,action_information10 aug_days
,action_information11 sep_days
,action_information12 oct_days
,action_information13 nov_days
,action_information14 dec_days
,action_information15 YEAR
,action_information16 jan_hours
,action_information17 feb_hours
,action_information18 mar_hours
,action_information19 apr_hours
,action_information20 may_hours
,action_information21 jun_hours
,action_information22 jul_hours
,action_information23 aug_hours
,action_information24 sep_hours
,action_information25 oct_hours
,action_information26 nov_hours
,action_information27 dec_hours
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 = 'PYSEWTCA'
AND action_information2 = 'WTC_PERSON5'
AND action_information28 = csr_v_group_id;
SELECT action_information2 rpt_person_id
,action_information3 rpt_assignment_id
,action_information4 rpt_still_employed
,action_information5 rpt_business_group_id
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 = 'PYSEWTCA';
SELECT *
FROM pay_action_information
WHERE action_context_type = 'AAP'
AND action_information_category = 'EMEA REPORT INFORMATION'
AND action_information1 = 'PYSEWTCR'
AND action_information3 = csr_v_pa_id
AND action_information2 = 'PER'
AND action_information15 = csr_v_le_id
ORDER BY action_information30;
SELECT ROWNUM
,action_information3 jan_addl_time
,action_information4 feb_addl_time
,action_information5 mar_addl_time
,action_information6 apr_addl_time
,action_information7 may_addl_time
,action_information8 jun_addl_time
,action_information9 jul_addl_time
,action_information10 aug_addl_time
,action_information11 sep_addl_time
,action_information12 oct_addl_time
,action_information13 nov_addl_time
,action_information14 dec_addl_time
,action_information15 YEAR
,action_information16 jan_overtime
,action_information17 feb_overtime
,action_information18 mar_overtime
,action_information19 apr_overtime
,action_information20 may_overtime
,action_information21 jun_overtime
,action_information22 jul_overtime
,action_information23 aug_overtime
,action_information24 sep_overtime
,action_information25 oct_overtime
,action_information26 nov_overtime
,action_information27 dec_overtime
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 = 'PYSEWTCA'
AND action_information2 = 'WTC_PERSON6'
AND action_information28 = csr_v_group_id;
SELECT ROWNUM
,action_information3 jan_absence_time
,action_information4 feb_absence_time
,action_information5 mar_absence_time
,action_information6 apr_absence_time
,action_information7 may_absence_time
,action_information8 jun_absence_time
,action_information9 jul_absence_time
,action_information10 aug_absence_time
,action_information11 sep_absence_time
,action_information12 oct_absence_time
,action_information13 nov_absence_time
,action_information14 dec_absence_time
,action_information15 YEAR
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 = 'PYSEWTCA'
AND action_information2 = 'WTC_PERSON7'
AND action_information28 = csr_v_group_id;
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;