[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT (pay_se_income_statement.get_parameter
(legislative_parameters
,'INCOME_STATEMENT_PROVIDER'
)
) income_statement_provider
, (pay_se_income_statement.get_parameter
(legislative_parameters
,'REQUEST_FOR'
)
) request_for
, (pay_se_income_statement.get_parameter
(legislative_parameters
,'LEGAL_EMPLOYER'
)
) legal_employer
, (pay_se_income_statement.get_parameter
(legislative_parameters
,'INCOME_YEAR'
)
) income_year
, (pay_se_income_statement.get_parameter
(legislative_parameters
,'PERSON_REQUEST'
)
) person_for
, (pay_se_income_statement.get_parameter
(legislative_parameters
,'REQUESTING_PERSON'
)
) person_number
, (pay_se_income_statement.get_parameter
(legislative_parameters
,'TEST_PRODUCTION'
)
) test_production
, (pay_se_income_statement.get_parameter
(legislative_parameters
,'SORT_ORDER'
)
) sort_order
,effective_date
,business_group_id bg_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
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 IN ('C','S') -- 10229507
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 o.NAME "MP_NAME",o.location_id
,hoi1.org_information1 "MP_ORG_NUMBER"
,hoi1.org_information2 "MP_DEPARTMENT"
,hoi1.org_information3 "MEDIUM_IDENTITY"
,hoi1.org_information4 "PROGRAM"
FROM hr_all_organization_units o
,hr_organization_information hoi
,hr_organization_information hoi1
WHERE o.business_group_id = g_business_group_id
AND o.organization_id = hoi.organization_id
AND hoi.org_information_context = 'CLASS'
AND hoi.org_information1 = 'SE_INC_STMT_PROVIDER'
AND o.organization_id = hoi1.organization_id
AND hoi1.org_information_context = 'SE_INC_STMT_PROVIDER_DETAILS'
AND o.organization_id = csr_v_media_provider_id;
SELECT hoi22.org_information1
,hoi22.org_information2
,hoi22.org_information3
,hoi22.org_information_id
FROM hr_organization_information hoi11
,hr_organization_information hoi22
WHERE hoi11.organization_id = csr_v_media_provider_id
AND hoi11.org_information_context = 'CLASS'
AND hoi11.org_information1 = 'SE_INC_STMT_PROVIDER'
AND hoi22.organization_id = hoi11.organization_id
AND hoi22.org_information_context = 'SE_ORG_CONTACT_DETAILS'
AND hoi22.org_information1 = csr_v_type
AND ROWNUM < 2
ORDER BY hoi22.org_information_id ASC;
/* 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
, hr_organization_units o2
, hr_organization_information hoi3
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'
AND o2.organization_id = csr_v_media_provider_id
AND hoi3.org_information_context = 'SE_LEGAL_EMPLOYERS'
and hoi3.org_information1 = o1.organization_id;*/
SELECT o1.NAME legal_employer_name
,o1.location_id
,hoi2.org_information2 org_number
,hoi1.organization_id legal_id
FROM hr_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 =
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'
AND o1.organization_id = hoi3.organization_id
AND hoi3.org_information_context = 'SE_INC_STMT_PROVIDERS'
AND hoi3.org_information1 = csr_v_media_provider_id;
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 ORG_INFORMATION1,ORG_INFORMATION2,ORG_INFORMATION3
FROM hr_organization_information
WHERE organization_id = g_business_group_id
AND org_information_context = 'SE_POST_HEADER_INFO';
'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
,act.tax_unit_id legal_employer_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 IN ('C','S') -- 10229507
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
AND act.tax_unit_id IN (
SELECT o.organization_id
FROM hr_all_organization_units o
,hr_organization_information hoi1
,hr_organization_information hoi2
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 = hoi2.organization_id
AND hoi2.org_information_context =
'SE_INC_STMT_PROVIDERS'
AND hoi2.org_information1 =
g_income_statement_provider_id)
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 count('1') "VALID" from per_all_people_f pap
where pap.person_id=val_person_id
AND pap.EMPLOYEE_NUMBER=val_person_number
AND pap.EFFECTIVE_START_DATE <= g_income_end_date
AND pap.EFFECTIVE_END_DATE > = g_income_start_date
;
SELECT paa.assignment_id
,paa.effective_start_date
,paa.effective_end_date
,scl.segment2
FROM per_all_assignments_f paa
,hr_soft_coding_keyflex scl
WHERE person_id = csr_v_person_id
AND paa.effective_start_date <= g_income_end_date
AND paa.effective_end_date > = g_income_start_date
AND paa.primary_flag = 'Y'
AND paa.assignment_status_type_id IN (
SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'ACTIVE_ASSIGN'
AND active_flag = 'Y'
AND ( ( legislation_code IS NULL
AND business_group_id IS NULL
)
OR (business_group_id = g_business_group_id)
))
AND scl.soft_coding_keyflex_id = paa.soft_coding_keyflex_id;
SELECT pet.element_type_id
,piv.input_value_id
,pel.element_link_id
FROM pay_element_types_f pet
,pay_input_values_f piv
,pay_element_links_f pel
WHERE pet.element_name = 'Tax Card'
AND pet.legislation_code = 'SE'
AND piv.element_type_id = pet.element_type_id
AND piv.NAME = 'Tax Card Type'
AND pel.element_type_id = pet.element_type_id
AND pel.business_group_id = g_business_group_id
AND pet.effective_start_date <= g_income_end_date
AND pet.effective_end_date > = g_income_start_date
AND piv.effective_start_date <= g_income_end_date
AND piv.effective_end_date > = g_income_start_date
AND pel.effective_start_date <= g_income_end_date
AND pel.effective_end_date > = g_income_start_date;
SELECT COUNT ('Y') valid
FROM pay_element_entries_f pee
,pay_element_entry_values_f peev
WHERE peev.screen_entry_value = 'A'
AND peev.element_entry_id = pee.element_entry_id
AND peev.effective_start_date = pee.effective_start_date
AND peev.effective_end_date = pee.effective_end_date
AND peev.input_value_id = csr_v_input_value_id
--AND pee.element_link_id = csr_v_link_id
AND pee.element_type_id = csr_v_type_id
AND pee.assignment_id = csr_v_prim_assignment_id
AND pee.effective_start_date <= g_income_end_date
AND pee.effective_end_date > = g_income_start_date;
SELECT pei_information1
,pei_information2
,pei_information3
,pei_information4
,pei_information5
,pei_information6
,pei_information7
,pei_information8
,pei_information9
FROM per_people_extra_info
WHERE person_id = csr_v_person_id
AND information_type = csr_v_information_type;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM DUAL;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM DUAL;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM DUAL;
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
,hr_organization_information hoi2
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 = hoi2.organization_id
AND hoi2.org_information_context = 'SE_INC_STMT_PROVIDERS'
AND hoi2.org_information1 = g_income_statement_provider_id
AND o.organization_id =
DECODE (g_request_for
,'ALL_ORG', o.organization_id
,g_legal_employer_id
);
select o.organization_id,hoi3.ORG_INFORMATION1,hoi3.ORG_INFORMATION2
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_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
AND o.organization_id = hoi2.organization_id
AND hoi2.org_information_context = 'SE_INC_STMT_PROVIDERS'
and hoi2.org_information1 = g_income_statement_provider_id
and o.organization_id = decode(g_request_for,'ALL_ORG',o.organization_id,g_legal_employer_id)
AND o.organization_id = hoi3.organization_id
AND hoi3.org_information_context = 'SE_INC_STMT_KU10_INFORMATION';
select o.organization_id,hoi3.ORG_INFORMATION1,hoi3.ORG_INFORMATION2
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_id
AND o.organization_id = hoi3.organization_id
AND hoi3.org_information_context = 'SE_INC_STMT_KU10_INFORMATION';
SELECT o.organization_id
,hoi3.org_information1
,hoi3.org_information2
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_id
AND o.organization_id = hoi3.organization_id
-- AND hoi3.org_information_context = 'SE_INC_STMT_KU10_INFORMATION'
AND hoi3.org_information_context IN
('SE_INC_STMT_KU10_INFORMATION'
,'SE_INC_STMT_KU13_INFORMATION'
,'SE_INC_STMT_KU14_INFORMATION'
)
AND hoi3.org_information1 = csr_v_code;
SELECT h.lookup_code
FROM hr_lookups h
WHERE h.lookup_type = 'SE_INCOME_STATEMENT_FIELDS'
AND h.lookup_code LIKE 'KU%'
AND h.enabled_flag = 'Y'
ORDER BY h.meaning;
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 pd.defined_balance_id
FROM pay_defined_balances pd
,pay_balance_dimensions pbd
WHERE pd.balance_type_id = csr_v_balance_type_id
AND pbd.balance_dimension_id = pd.balance_dimension_id
AND pbd.legislation_code = 'SE'
AND pbd.database_item_suffix = '_PER_LE_YTD';
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 *
FROM (SELECT paa.effective_start_date,paa.effective_end_date
,paa.person_id
,paa.assignment_id
FROM per_all_assignments_f paa
,pay_assignment_actions pac
WHERE pac.assignment_action_id = p_asg_act_id
AND paa.assignment_id = pac.assignment_id
AND paa.effective_start_date <= g_income_end_date
AND paa.effective_end_date > = g_income_start_date
AND assignment_status_type_id IN (
SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'ACTIVE_ASSIGN'
AND active_flag = 'Y'
AND ( ( legislation_code IS NULL
AND business_group_id IS NULL
)
OR (business_group_id =
g_business_group_id
)
))
ORDER BY paa.effective_start_date DESC)
WHERE ROWNUM < 2;
SELECT pap.last_name
,pap.pre_name_adjunct
,pap.first_name
,pap.national_identifier
,pap.person_id
,pap.per_information1
,ft.territory_short_name
,ft.territory_code
,pap.effective_end_date
,pap.EMPLOYEE_NUMBER
,pap.date_of_birth -- EOY 2008/2009
FROM per_all_people_f pap
,fnd_territories_vl ft
WHERE pap.person_id = csr_v_person_id
AND pap.per_information_category = 'SE'
AND ft.obsolete_flag = 'N'
AND ft.territory_code = pap.per_information1
AND csr_v_effective_date BETWEEN pap.effective_start_date
AND pap.effective_end_date;
/* SELECT pap.last_name, pap.pre_name_adjunct, pap.first_name
, pap.national_identifier, pap.person_id
FROM
per_all_people_f pap
WHERE pap.person_id = csr_v_person_id
AND pap.per_information_category = 'SE'
AND csr_v_effective_date BETWEEN pap.effective_start_date
AND pap.effective_end_date;
SELECT address_line1
,address_line2
,address_line3
,postal_code
,country
,ft.territory_short_name
FROM per_addresses
,fnd_territories_vl ft
WHERE business_group_id = g_business_group_id
AND person_id = csr_v_person_id
AND country = ft.territory_code
AND csr_v_effective_date BETWEEN date_from
AND NVL (date_to
,TO_DATE ('31-12-4712'
,'DD-MM-YYYY'
)
);
SELECT MIN (paa.effective_start_date) effective_start_date
,MAX (paa.effective_end_date) effective_end_date
FROM per_all_assignments_f paa
,hr_soft_coding_keyflex scl
WHERE person_id = csr_v_person_id
AND paa.effective_start_date <= g_income_end_date
AND paa.effective_end_date > = g_income_start_date
AND assignment_status_type_id IN (
SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'ACTIVE_ASSIGN'
AND active_flag = 'Y'
AND ( ( legislation_code IS NULL
AND business_group_id IS NULL
)
OR (business_group_id = g_business_group_id)
))
AND scl.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
AND scl.segment2 IN (
SELECT o1.organization_id
FROM hr_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.org_information1 = 'SE_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND NVL (hoi1.org_information2, 'N') = 'Y'
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'
AND hoi3.organization_id = csr_v_legal_employer
AND NVL (hoi3.org_information2, 'N') = 'Y');
SELECT pei_information1
FROM per_people_extra_info
WHERE person_id = csr_v_person_id
AND information_type = 'SE_INC_STMT_DATA_CORRECTION';
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 csr_v_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_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_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 paa.assignment_id
,paa.effective_start_date
,paa.effective_end_date
,scl.segment2
FROM per_all_assignments_f paa
,hr_soft_coding_keyflex scl
WHERE paa.person_id = csr_v_person_id
AND paa.effective_start_date <= g_income_end_date
AND paa.effective_end_date > = g_income_start_date
AND paa.assignment_status_type_id IN (
SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'ACTIVE_ASSIGN'
AND active_flag = 'Y'
AND ( ( legislation_code IS NULL
AND business_group_id IS NULL
)
OR (business_group_id = g_business_group_id)
))
AND scl.soft_coding_keyflex_id = paa.soft_coding_keyflex_id;
SELECT paa.assignment_id
,paa.effective_start_date
,paa.effective_end_date
,scl.segment2
FROM per_all_assignments_f paa
,hr_soft_coding_keyflex scl
WHERE person_id = csr_v_person_id
AND paa.effective_start_date <= g_income_end_date
AND paa.effective_end_date > = g_income_start_date
AND paa.primary_flag = 'Y'
AND paa.assignment_status_type_id IN (
SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'ACTIVE_ASSIGN'
AND active_flag = 'Y'
AND ( ( legislation_code IS NULL
AND business_group_id IS NULL
)
OR (business_group_id = g_business_group_id)
))
AND scl.soft_coding_keyflex_id = paa.soft_coding_keyflex_id;
SELECT 'Y' "VALID"
FROM hr_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.org_information1 = 'SE_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND NVL (hoi1.org_information2, 'N') = 'Y'
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'
AND hoi3.organization_id = csr_v_legal_employer_id
AND NVL (hoi3.org_information2, 'N') = 'Y'
AND o1.organization_id = csr_v_local_unit_id;
SELECT pet.element_type_id
,piv.input_value_id
,pel.element_link_id
FROM pay_element_types_f pet
,pay_input_values_f piv
,pay_element_links_f pel
WHERE pet.element_name = 'Tax Card'
AND pet.legislation_code = 'SE'
AND piv.element_type_id = pet.element_type_id
AND piv.NAME = 'Tax Card Type'
AND pel.element_type_id = pet.element_type_id
AND pel.business_group_id = g_business_group_id
AND pet.effective_start_date <= g_income_end_date
AND pet.effective_end_date > = g_income_start_date
AND piv.effective_start_date <= g_income_end_date
AND piv.effective_end_date > = g_income_start_date
AND pel.effective_start_date <= g_income_end_date
AND pel.effective_end_date > = g_income_start_date;
SELECT pet.element_type_id
,pel.element_link_id
FROM pay_element_types_f pet
,pay_element_links_f pel
WHERE pet.element_name = csr_v_element_name
AND pet.legislation_code = 'SE'
AND pel.element_type_id = pet.element_type_id
AND pel.business_group_id = g_business_group_id
AND pet.effective_start_date <= g_income_end_date
AND pet.effective_end_date > = g_income_start_date
AND pel.effective_start_date <= g_income_end_date
AND pel.effective_end_date > = g_income_start_date;
SELECT count('Y') VALID
from pay_element_entries_f pee
, per_all_assignments_f paa
, pay_element_entry_values_f peev
where peev.screen_entry_value ='A'
AND peev.element_entry_id = pee.element_entry_id
AND peev.input_value_id = csr_v_input_value_id
AND peev.EFFECTIVE_START_DATE = pee.EFFECTIVE_START_DATE
AND peev.EFFECTIVE_END_DATE = pee.EFFECTIVE_END_DATE
and pee.ELEMENT_LINK_ID = csr_v_link_id
and paa.ASSIGNMENT_ID = pee.ASSIGNMENT_ID
and pee.ELEMENT_TYPE_ID = csr_v_type_id
and pee.ASSIGNMENT_ID = paa.assignment_id
AND PAA.PERSON_ID = csr_v_person_id
AND PAA.BUSINESS_GROUP_ID = g_business_group_id
AND PAA.PRIMARY_FLAG = 'Y'
AND pee.EFFECTIVE_START_DATE <= g_income_end_date AND pee.EFFECTIVE_END_DATE > = g_income_start_date
AND paa.EFFECTIVE_START_DATE <= g_income_end_date AND paa.EFFECTIVE_END_DATE > = g_income_start_date
AND paa.assignment_status_type_id IN
(select assignment_status_type_id
from per_assignment_status_types
where per_system_status = 'ACTIVE_ASSIGN'
and active_flag = 'Y'
and (
( legislation_code is null
and business_group_id is null
)
OR
( BUSINESS_GROUP_ID = g_business_group_id )
)
);
SELECT COUNT ('Y') valid
FROM pay_element_entries_f pee
,pay_element_entry_values_f peev
WHERE peev.screen_entry_value = 'A'
AND peev.element_entry_id = pee.element_entry_id
AND peev.effective_start_date = pee.effective_start_date
AND peev.effective_end_date = pee.effective_end_date
AND peev.input_value_id = csr_v_input_value_id
AND pee.element_link_id = csr_v_link_id
AND pee.element_type_id = csr_v_type_id
AND pee.assignment_id = csr_v_prim_assignment_id
AND pee.effective_start_date <= g_income_end_date
AND pee.effective_end_date > = g_income_start_date;
SELECT pei_information1
,pei_information2
,pei_information3
,pei_information4
,pei_information5
,pei_information6
,pei_information7
,pei_information8
,pei_information9
FROM per_people_extra_info
WHERE person_id = csr_v_person_id
AND information_type = csr_v_information_type;
SELECT pet.element_type_id
,pel.element_link_id
,pee.element_entry_id
FROM pay_element_types_f pet
,pay_element_links_f pel
,pay_element_entries_f pee
WHERE pel.element_type_id = pet.element_type_id
AND (pet.legislation_code = 'SE' OR pet.legislation_code IS NULL
)
AND pel.business_group_id = g_business_group_id
AND pet.effective_start_date <= g_income_end_date
AND pet.effective_end_date > = g_income_start_date
AND pel.effective_start_date <= g_income_end_date
AND pel.effective_end_date > = g_income_start_date
AND pee.effective_start_date <= g_income_end_date
AND pee.effective_end_date > = g_income_start_date
AND pet.element_information1 = csr_v_elem_code
AND pet.element_information_category = csr_v_category
AND pee.element_link_id = pel.element_link_id
AND pee.assignment_id = csr_v_assignment_id;
SELECT 'Y' "PROCESSED"
FROM pay_run_results prr
,pay_element_entries_f pee
WHERE pee.element_entry_id =
csr_v_element_entry_id
--p_p_element_entry_id
/*and p_effective_date* between pee.effective_start_date
and pee.effective_end_date*/
AND pee.effective_start_date BETWEEN g_income_start_date
AND g_income_end_date
AND pee.effective_end_date BETWEEN g_income_start_date
AND g_income_end_date
AND prr.source_id = pee.element_entry_id
AND prr.entry_type = pee.entry_type
AND prr.source_type = 'E'
AND prr.status <> 'U'
AND NOT EXISTS (
SELECT 1
FROM pay_run_results sub_rr
WHERE sub_rr.source_id = prr.run_result_id
AND sub_rr.source_type IN ('R', 'V'));
SELECT pee.element_entry_id
,pet.element_name
,pee.effective_start_date
,pee.effective_end_date
FROM pay_element_entries_f pee
,pay_element_types_f pet
WHERE pet.element_name = 'Car Benefit'
AND pet.legislation_code = 'SE'
AND pee.assignment_id = csr_v_assignment_id
AND pee.element_type_id = pet.element_type_id
AND pee.effective_start_date <= g_income_end_date
AND pee.effective_end_date >= g_income_start_date
AND pet.effective_start_date <= g_income_end_date
AND pet.effective_end_date >= g_income_start_date
ORDER BY pee.effective_end_date DESC;
SELECT pee.element_entry_id
,pee.effective_start_date
,pee.effective_end_date
,peev.screen_entry_value
FROM pay_element_entries_f pee
,pay_input_values_f piv
,pay_element_entry_values_f peev
WHERE pee.element_entry_id = csr_v_ee_id
AND piv.element_type_id = piv.element_type_id
AND piv.NAME = 'RSV Code'
AND peev.element_entry_id = pee.element_entry_id
AND peev.input_value_id = piv.input_value_id
-- AND pee.ASSIGNMENT_ID = csr_v_assignment_id Not Needed now.
AND pee.effective_start_date <= g_income_end_date
AND pee.effective_end_date > = g_income_start_date
AND piv.effective_start_date <= g_income_end_date
AND piv.effective_end_date > = g_income_start_date;
SELECT 1
INTO l_flag
FROM pay_action_information
WHERE action_information_category = 'EMEA REPORT INFORMATION'
AND action_information1 = 'PYSEINSA'
AND action_context_id = p_assignment_action_id;
SELECT *
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 = 'PYSEINSA'
AND action_information2 = 'LE';
SELECT *
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 = 'PYSEINSA'
AND action_information2 = 'MP';
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 = 'PYSEINSA';
SELECT *
FROM pay_action_information
WHERE action_context_type = 'AAP'
AND action_information_category = 'EMEA REPORT INFORMATION'
AND action_information1 = 'PYSEINSA'
AND action_information2 = 'PERSON'
AND action_information3 = csr_v_pa_id
AND action_information4 = csr_v_stmt_type
AND action_information29 = csr_v_le_id
ORDER BY decode(csr_v_sort_order
,'EMPNUM',action_information5
,'EMPPIN',action_information6
,'EMPLAN',action_information7
);
SELECT *
FROM pay_action_information
WHERE action_context_type = 'AAP'
AND action_information_category = 'EMEA REPORT INFORMATION'
AND action_information1 = 'PYSEINSA'
AND action_information2 = csr_v_record
AND action_information3 = csr_v_pa_id
AND action_information30 = csr_v_person_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 ft.territory_short_name
FROM fnd_territories_vl ft
WHERE ft.territory_code = p_code;