VIEW: APPS.PAY_KR_SEP_RESULT_EE_V

Source

View Text - Preformatted

SELECT paa.assignment_action_id assignment_action_id, paa.assignment_id assignment_id, ppa.effective_date effective_date, ppa.date_earned date_earned, paa.run_type_id run_type_id, pel.element_link_id element_link_id, pee.element_entry_id element_entry_id, pee.effective_start_date effective_start_date, pee.effective_end_date effective_end_date, pee.entry_type entry_type, pee.creator_type creator_type, pee.creator_id creator_id, pee.source_id source_id, pet.element_type_id element_type_id, pet.element_name element_name, pet.processing_priority processing_priority, petl.element_name uelement_name, pec.classification_id classification_id, pec.classification_name classification_name, pectl.classification_name uclassification_name from pay_element_classifications_tl pectl, pay_element_classifications pec, pay_element_types_f_tl petl, pay_element_types_f pet, pay_element_links_f pel, pay_element_entries_f pee, pay_payroll_actions ppa, pay_assignment_actions paa where not exists( select null from pay_run_types_f prt, pay_payroll_actions sppa, pay_assignment_actions spaa, pay_action_interlocks pai where pai.locking_action_id = paa.assignment_action_id and spaa.assignment_action_id = pai.locked_action_id and sppa.payroll_action_id = spaa.payroll_action_id and prt.run_type_id = spaa.run_type_id and sppa.effective_date between prt.effective_start_date and prt.effective_end_date and prt.run_type_name like 'SEP%') and ppa.payroll_action_id = paa.payroll_action_id and pee.assignment_id = paa.assignment_id and ppa.date_earned between pee.effective_start_date and pee.effective_end_date and pee.creator_type in ('B','EE','F','H','R','RR') and pel.element_link_id = pee.element_link_id and ppa.date_earned between pel.effective_start_date and pel.effective_end_date and pet.element_type_id = pel.element_type_id and ppa.date_earned between pet.effective_start_date and pet.effective_end_date and petl.element_type_id = pet.element_type_id and petl.language = userenv('LANG') and pec.classification_id = pet.classification_id and pectl.classification_id = pec.classification_id and pectl.language = userenv('LANG') and not exists( select null from pay_run_types_f prt, pay_element_type_usages_f petu where petu.element_type_id = pet.element_type_id and ppa.effective_date between petu.effective_start_date and petu.effective_end_date and petu.inclusion_flag = 'N' and prt.run_type_id = petu.run_type_id and ppa.effective_date between prt.effective_start_date and prt.effective_end_date and prt.run_type_name like 'SEP%') and not exists( select null from pay_element_type_rules petr where petr.element_type_id = pet.element_type_id and petr.element_set_id = ppa.element_set_id and petr.include_or_exclude = 'E')

View Text - HTML Formatted

SELECT PAA.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE EFFECTIVE_DATE
, PPA.DATE_EARNED DATE_EARNED
, PAA.RUN_TYPE_ID RUN_TYPE_ID
, PEL.ELEMENT_LINK_ID ELEMENT_LINK_ID
, PEE.ELEMENT_ENTRY_ID ELEMENT_ENTRY_ID
, PEE.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, PEE.ENTRY_TYPE ENTRY_TYPE
, PEE.CREATOR_TYPE CREATOR_TYPE
, PEE.CREATOR_ID CREATOR_ID
, PEE.SOURCE_ID SOURCE_ID
, PET.ELEMENT_TYPE_ID ELEMENT_TYPE_ID
, PET.ELEMENT_NAME ELEMENT_NAME
, PET.PROCESSING_PRIORITY PROCESSING_PRIORITY
, PETL.ELEMENT_NAME UELEMENT_NAME
, PEC.CLASSIFICATION_ID CLASSIFICATION_ID
, PEC.CLASSIFICATION_NAME CLASSIFICATION_NAME
, PECTL.CLASSIFICATION_NAME UCLASSIFICATION_NAME
FROM PAY_ELEMENT_CLASSIFICATIONS_TL PECTL
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_ELEMENT_TYPES_F_TL PETL
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_LINKS_F PEL
, PAY_ELEMENT_ENTRIES_F PEE
, PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
WHERE NOT EXISTS( SELECT NULL
FROM PAY_RUN_TYPES_F PRT
, PAY_PAYROLL_ACTIONS SPPA
, PAY_ASSIGNMENT_ACTIONS SPAA
, PAY_ACTION_INTERLOCKS PAI
WHERE PAI.LOCKING_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND SPAA.ASSIGNMENT_ACTION_ID = PAI.LOCKED_ACTION_ID
AND SPPA.PAYROLL_ACTION_ID = SPAA.PAYROLL_ACTION_ID
AND PRT.RUN_TYPE_ID = SPAA.RUN_TYPE_ID
AND SPPA.EFFECTIVE_DATE BETWEEN PRT.EFFECTIVE_START_DATE
AND PRT.EFFECTIVE_END_DATE
AND PRT.RUN_TYPE_NAME LIKE 'SEP%')
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PEE.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PPA.DATE_EARNED BETWEEN PEE.EFFECTIVE_START_DATE
AND PEE.EFFECTIVE_END_DATE
AND PEE.CREATOR_TYPE IN ('B'
, 'EE'
, 'F'
, 'H'
, 'R'
, 'RR')
AND PEL.ELEMENT_LINK_ID = PEE.ELEMENT_LINK_ID
AND PPA.DATE_EARNED BETWEEN PEL.EFFECTIVE_START_DATE
AND PEL.EFFECTIVE_END_DATE
AND PET.ELEMENT_TYPE_ID = PEL.ELEMENT_TYPE_ID
AND PPA.DATE_EARNED BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND PETL.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PETL.LANGUAGE = USERENV('LANG')
AND PEC.CLASSIFICATION_ID = PET.CLASSIFICATION_ID
AND PECTL.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID
AND PECTL.LANGUAGE = USERENV('LANG')
AND NOT EXISTS( SELECT NULL
FROM PAY_RUN_TYPES_F PRT
, PAY_ELEMENT_TYPE_USAGES_F PETU
WHERE PETU.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PPA.EFFECTIVE_DATE BETWEEN PETU.EFFECTIVE_START_DATE
AND PETU.EFFECTIVE_END_DATE
AND PETU.INCLUSION_FLAG = 'N'
AND PRT.RUN_TYPE_ID = PETU.RUN_TYPE_ID
AND PPA.EFFECTIVE_DATE BETWEEN PRT.EFFECTIVE_START_DATE
AND PRT.EFFECTIVE_END_DATE
AND PRT.RUN_TYPE_NAME LIKE 'SEP%')
AND NOT EXISTS( SELECT NULL
FROM PAY_ELEMENT_TYPE_RULES PETR
WHERE PETR.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PETR.ELEMENT_SET_ID = PPA.ELEMENT_SET_ID
AND PETR.INCLUDE_OR_EXCLUDE = 'E')