[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT TO_NUMBER
(pay_se_alecta.get_parameter (legislative_parameters
,'LEGAL_EMPLOYER'
)
) legal
, (pay_se_alecta.get_parameter (legislative_parameters
,'REQUEST_FOR'
)
) request_for
, (pay_se_alecta.get_parameter (legislative_parameters, 'YEAR')
) report_year
, (pay_se_alecta.get_parameter (legislative_parameters
,'MONTH')
) report_month
, (pay_se_alecta.get_parameter (legislative_parameters
,'SENT_FROM'
)
) sent_from
, (pay_se_alecta.get_parameter (legislative_parameters
,'SENT_TO'
)
) sent_to
, (pay_se_alecta.get_parameter (legislative_parameters
,'PRODUCTION'
)
) production
,effective_date effective_date
,business_group_id bg_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
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 =
NVL (csr_v_legal_employer_id, hoi1.organization_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 '1'
FROM pay_payroll_actions appa
,pay_assignment_actions act
,per_all_assignments_f as1
,pay_payroll_actions ppa
WHERE ppa.payroll_action_id = p_payroll_action_id
AND appa.effective_date BETWEEN csr_v_canonical_start_date
AND csr_v_canonical_end_date
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.business_group_id = g_business_group_id
AND act.action_status = 'C' -- Completed
AND act.tax_unit_id = csr_v_legal_employer_id
AND appa.effective_date BETWEEN as1.effective_start_date
AND as1.effective_end_date
AND ppa.effective_date BETWEEN as1.effective_start_date
AND as1.effective_end_date
ORDER BY as1.person_id
,act.assignment_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';
'select 1 from dual where to_char(:payroll_action_id) = dummy';
SELECT as1.person_id person_id
,act.assignment_id assignment_id
,act.assignment_action_id run_action_id
FROM pay_payroll_actions appa
,pay_assignment_actions act
,per_all_assignments_f as1
,pay_payroll_actions ppa
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.business_group_id = g_business_group_id
AND act.action_status = 'C' -- Completed
AND act.tax_unit_id = NVL (p_legal_employer_id, act.tax_unit_id)
AND appa.effective_date BETWEEN as1.effective_start_date
AND as1.effective_end_date
-- AND ppa.effective_date BETWEEN as1.effective_start_date AND as1.effective_end_date
ORDER BY as1.person_id
,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 = 'SE'
AND ue.business_group_id IS NULL
AND ue.creator_type = 'B';
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM DUAL;
SELECT o.organization_id
FROM hr_all_organization_units o
,hr_organization_information hoi1
WHERE o.business_group_id = g_business_group_id
AND hoi1.organization_id = o.organization_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
/* AND o.organization_id =
DECODE (g_request_for
,'ALL_ORG', o.organization_id
,g_legal_employer_id
) */
;
SELECT h.lookup_code
FROM hr_lookups h
WHERE h.lookup_type = 'HR_SE_ALECTA_FIELDS'
AND h.enabled_flag = 'Y'
ORDER BY h.meaning;
SELECT h.lookup_code
FROM hr_lookups h
WHERE h.lookup_type = 'HR_SE_EVENT'
AND h.enabled_flag = 'Y'
AND ( ( SUBSTR (csr_v_fields, 1, 2) = 'ET'
AND (lookup_code <> 'FK')
)
OR ( SUBSTR (csr_v_fields, 1, 2) = 'MS'
AND lookup_code IN ('IN', 'FK', 'LO')
)
OR ( SUBSTR (csr_v_fields, 1, 2) = 'TR'
AND lookup_code IN ('AV2', 'AV3', 'AV4')
)
OR ( SUBSTR (csr_v_fields, 1, 2) = 'PL'
AND (lookup_code = 'AV2')
)
)
ORDER BY h.meaning;
SELECT o.organization_id
,hoi3.org_information1
,hoi3.org_information2
,hoi3.org_information3
,hoi3.org_information4
,hoi3.org_information5
,hoi3.org_information6
,hoi3.org_information7
,hoi3.org_information8
FROM hr_all_organization_units o
,hr_organization_information hoi1
,hr_organization_information hoi3
WHERE o.business_group_id = g_business_group_id
AND hoi1.organization_id = o.organization_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
AND o.organization_id = csr_v_le
AND o.organization_id = hoi3.organization_id
AND hoi3.org_information_context = 'SE_ALECTA_MAPPING'
AND hoi3.org_information1 = csr_v_name
AND hoi3.org_information2 = csr_v_event;
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 db.defined_balance_id
FROM pay_defined_balances db
,pay_balance_dimensions bd
WHERE db.balance_type_id = p_balance_type_id
AND db.balance_dimension_id = bd.balance_dimension_id
AND bd.database_item_suffix = p_dimension
AND bd.legislation_code = 'SE';
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 pac.assignment_id
FROM pay_assignment_actions pac
WHERE pac.assignment_action_id = p_asg_act_id;
SELECT pap.last_name
,pap.pre_name_adjunct
,pap.first_name
,pap.national_identifier
,pap.person_id
,pac.assignment_id
,paa.assignment_number
,paa.employee_category
,paa.effective_start_date
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 = 'SE'
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 hoi3.organization_id legal_id
FROM hr_all_organization_units o1
,hr_organization_information hoi1
,hr_organization_information hoi2
,hr_organization_information hoi3
WHERE o1.business_group_id = g_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_organization_id
AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id = hoi2.org_information1
AND hoi2.org_information_context = 'SE_LOCAL_UNITS'
AND hoi2.organization_id = hoi3.organization_id
AND hoi3.org_information_context = 'CLASS'
AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER';
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 = '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 COUNT ('1') "RECORD_FOUND"
FROM per_all_assignments_f
WHERE assignment_id = csr_v_assignment_id
AND business_group_id = g_business_group_id
AND effective_start_date < g_start_date;
SELECT hr_general.decode_lookup ('SE_AGREEMENT_PLAN'
,aei_information1
) "AGREEMENT"
FROM per_assignment_extra_info
WHERE assignment_id = csr_v_assignment_id
AND information_type = 'SE_ALECTA_DETAILS';
SELECT effective_start_date
,effective_end_date
FROM per_all_assignments_f
WHERE assignment_id = csr_v_assignment_id
AND business_group_id = g_business_group_id
AND effective_start_date >= g_start_date
AND effective_start_date <= g_end_date
--AND rownum =1
ORDER BY effective_start_date ASC;
SELECT paaf.person_id
FROM per_all_assignments_f paaf
WHERE paaf.assignment_id = csr_v_assignment_id
AND paaf.effective_start_date <= g_end_date;
SELECT GREATEST (effective_start_date, g_start_date)
effective_start_date
,LEAST (effective_end_date, g_end_date) effective_end_date
FROM per_all_assignments_f
WHERE assignment_id = csr_v_assignment_id
AND business_group_id = g_business_group_id
AND effective_start_date BETWEEN g_start_date AND g_end_date
ORDER BY effective_end_date DESC;
SELECT *
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 = 'PYSEALEA';
SELECT *
FROM pay_action_information
WHERE action_context_type = 'AAP'
AND action_information_category = 'EMEA REPORT INFORMATION'
AND action_information1 = 'PYSEALEA'
AND action_information3 = csr_v_pa_id
AND action_information2 = 'PER'
AND action_information4 = csr_v_event
ORDER BY action_information30;
SELECT COUNT ('1')
FROM pay_action_information
WHERE action_context_type = 'AAP'
AND action_information_category = 'EMEA REPORT INFORMATION'
AND action_information1 = 'PYSEALEA'
AND action_information3 = csr_v_pa_id
AND action_information2 = 'PER'
AND action_information4 = csr_v_event;
SELECT *
FROM pay_action_information
WHERE action_context_type = 'AAP'
AND action_information_category = 'EMEA REPORT INFORMATION'
AND action_information1 = 'PYSEALEA'
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 scl.segment2
,paa.location_id
FROM per_all_assignments_f paa
,hr_soft_coding_keyflex scl
WHERE paa.assignment_id = p_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 hoi3.organization_id legal_id
FROM hr_all_organization_units o1
,hr_organization_information hoi1
,hr_organization_information hoi2
,hr_organization_information hoi3
WHERE o1.business_group_id = g_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_organization_id
AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id = hoi2.org_information1
AND hoi2.org_information_context = 'SE_LOCAL_UNITS'
AND hoi2.organization_id = hoi3.organization_id
AND hoi3.org_information_context = 'CLASS'
AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER';
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 hr_general.decode_lookup ('SE_ALECTA_COST_CENTER'
,lei_information1
) "COST_CENTER"
FROM hr_location_extra_info
WHERE location_id = csr_v_location_id
AND information_type = 'SE_ALECTA_DETAILS';
SELECT pap.last_name
,pap.first_name
,pap.national_identifier
,pap.person_id
,paa.assignment_id
,paa.assignment_number
,paa.effective_start_date
,pap.date_of_birth
FROM per_all_assignments_f paa
,per_all_people_f pap
WHERE paa.assignment_id = p_assignment_id
AND paa.person_id = pap.person_id
AND pap.per_information_category = 'SE'
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 MONTHS_BETWEEN (g_start_date, pap.date_of_birth) / 12 "YEAR"
,pap.date_of_birth
FROM per_all_assignments_f paa
,per_all_people_f pap
WHERE paa.assignment_id = p_assignment_id
AND paa.person_id = pap.person_id
AND pap.per_information_category = 'SE'
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 paa.person_id
FROM per_all_assignments_f paa
WHERE paa.assignment_id = p_assignment_id
AND p_effective_date BETWEEN paa.effective_start_date
AND paa.effective_end_date;
SELECT paat.absence_category
,paa.date_start
,paa.date_end
,paa.abs_information3
FROM per_absence_attendances paa
,per_absence_attendance_types paat
WHERE paa.person_id = csr_v_person_id
AND paa.absence_attendance_type_id =
paat.absence_attendance_type_id
AND paat.absence_category = 'S'
AND paa.date_start >= g_start_date
AND paa.date_start <= g_end_date
ORDER BY paa.date_start desc;
SELECT scl.segment2
,paa.location_id
FROM per_all_assignments_f paa
,hr_soft_coding_keyflex scl
WHERE paa.assignment_id = p_assignment_id
AND scl.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
AND p_effective_date BETWEEN paa.effective_start_date
AND paa.effective_end_date;
SELECT hoi3.organization_id legal_id
FROM hr_all_organization_units o1
,hr_organization_information hoi1
,hr_organization_information hoi2
,hr_organization_information hoi3
WHERE o1.business_group_id = g_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_organization_id
AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id = hoi2.org_information1
AND hoi2.org_information_context = 'SE_LOCAL_UNITS'
AND hoi2.organization_id = hoi3.organization_id
AND hoi3.org_information_context = 'CLASS'
AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER';
SELECT db.defined_balance_id
FROM pay_defined_balances db
,pay_balance_dimensions bd
WHERE db.balance_type_id = csr_v_balance_type_id
AND db.balance_dimension_id = bd.balance_dimension_id
AND bd.database_item_suffix = csr_v_dimesion
AND bd.legislation_code = 'SE';
SELECT effective_start_date
FROM pay_element_entries_f
WHERE assignment_id = p_assignment_id
AND element_type_id = csr_v_element_type_id
AND effective_start_date BETWEEN g_start_date AND g_end_date;
SELECT min(effective_start_date)
FROM per_all_assignments_f
WHERE assignment_id=csr_v_assignment_id;
SELECT scl.segment2
,scl.segment5
,scl.segment6
,scl.segment7
,GREATEST (effective_start_date, g_start_date)
"EFF_START_DATE"
,LEAST (effective_end_date, g_end_date) "EFF_END_DATE"
FROM per_all_assignments_f paa
,hr_soft_coding_keyflex scl
WHERE paa.assignment_id = p_assignment_id
AND paa.business_group_id = g_business_group_id
AND scl.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
AND paa.effective_start_date BETWEEN g_start_date AND g_end_date
ORDER BY paa.effective_end_date DESC;
SELECT peef.effective_start_date
,peevf.screen_entry_value
FROM pay_element_entries_f peef
,pay_element_entry_values_f peevf
WHERE peef.assignment_id = p_assignment_id
AND peef.element_type_id = csr_v_element_type_id
AND peef.effective_start_date BETWEEN g_start_date AND g_end_date
AND peevf.effective_start_date BETWEEN g_start_date AND g_end_date
AND peef.element_entry_id=peevf.element_entry_id
AND peevf.input_value_id = csr_v_input_value_id;