VIEW: APPS.PAY_KR_SEP_HISTORY_V

Source

View Text - Preformatted

SELECT ppa.business_group_id BUSINESS_GROUP_ID, pay_kr_report_pkg.legislation_code(ppa.business_group_id) LEGISLATION_CODE, pp.person_id PERSON_ID, pp.full_name FULL_NAME, pp.employee_number EMPLOYEE_NUMBER, pp.national_identifier NATIONAL_IDENTIFIER, pa.assignment_id ASSIGNMENT_ID, pa.assignment_number ASSIGNMENT_NUMBER, paa.assignment_action_id ASSIGNMENT_ACTION_ID, decode(ppa.action_type, 'X', paa.assignment_action_id, nvl(paa.source_action_id, paa.assignment_action_id)) SOURCE_ACTION_ID, nvl(paa.run_type_id, ppa.run_type_id) RUN_TYPE_ID, pay_kr_sep_form_pkg.get_run_type_name(nvl(paa.run_type_id, ppa.run_type_id), ppa.effective_date) RUN_TYPE_NAME, paa.tax_unit_id TAX_UNIT_ID, paa.action_status ACTION_STATUS_CODE, pay_assignment_actions_pkg.get_action_status(paa.assignment_action_id, ppa.action_type, paa.action_status) ACTION_STATUS, paa.action_sequence ACTION_SEQUENCE, ppa.payroll_action_id PAYROLL_ACTION_ID, ppa.effective_date EFFECTIVE_DATE, ppa.date_earned DATE_EARNED, hr_general.decode_lookup('ACTION_TYPE', ppa.action_type) ACTION_TYPE, hr_payrolls.display_period_name(paa.payroll_action_id) PERIOD_NAME, pay_kr_report_pkg.get_result_value_date(paa.assignment_action_id, ppa.business_group_id, 'WKPD', 'H_DATE') H_DATE, pay_kr_report_pkg.get_result_value_date(paa.assignment_action_id,ppa.business_group_id, 'WKPD', 'L_DATE') L_DATE FROM per_people_f pp, per_assignments_f pa, pay_payroll_actions ppa, pay_assignment_actions paa WHERE ppa.payroll_action_id = paa.payroll_action_id and ( ( ppa.action_type in ('R','Q') and exists ( select null from pay_run_types_f sprt where sprt.run_type_id = nvl(paa.run_type_id,ppa.run_type_id) and ppa.effective_date between sprt.effective_start_date and sprt.effective_end_date and sprt.run_type_name like 'SEP%' ) and paa.source_action_id is not null ) or ( ppa.action_type = 'X' and exists ( select null from pay_run_types_f xsprt, pay_assignment_actions xspaa, pay_action_interlocks xpai where xpai.locking_action_id = paa.assignment_action_id and xspaa.assignment_action_id = xpai.locked_action_id and xsprt.run_type_id = xspaa.run_type_id and ppa.effective_date between xsprt.effective_start_date and xsprt.effective_end_date and xsprt.run_type_name like 'SEP%' ) ) ) and pa.assignment_id = paa.assignment_id and ppa.effective_date between pa.effective_start_date and pa.effective_end_date and pp.person_id = pa.person_id and ppa.effective_date between pp.effective_start_date and pp.effective_end_date

View Text - HTML Formatted

SELECT PPA.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PAY_KR_REPORT_PKG.LEGISLATION_CODE(PPA.BUSINESS_GROUP_ID) LEGISLATION_CODE
, PP.PERSON_ID PERSON_ID
, PP.FULL_NAME FULL_NAME
, PP.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PP.NATIONAL_IDENTIFIER NATIONAL_IDENTIFIER
, PA.ASSIGNMENT_ID ASSIGNMENT_ID
, PA.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, PAA.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, DECODE(PPA.ACTION_TYPE
, 'X'
, PAA.ASSIGNMENT_ACTION_ID
, NVL(PAA.SOURCE_ACTION_ID
, PAA.ASSIGNMENT_ACTION_ID)) SOURCE_ACTION_ID
, NVL(PAA.RUN_TYPE_ID
, PPA.RUN_TYPE_ID) RUN_TYPE_ID
, PAY_KR_SEP_FORM_PKG.GET_RUN_TYPE_NAME(NVL(PAA.RUN_TYPE_ID
, PPA.RUN_TYPE_ID)
, PPA.EFFECTIVE_DATE) RUN_TYPE_NAME
, PAA.TAX_UNIT_ID TAX_UNIT_ID
, PAA.ACTION_STATUS ACTION_STATUS_CODE
, PAY_ASSIGNMENT_ACTIONS_PKG.GET_ACTION_STATUS(PAA.ASSIGNMENT_ACTION_ID
, PPA.ACTION_TYPE
, PAA.ACTION_STATUS) ACTION_STATUS
, PAA.ACTION_SEQUENCE ACTION_SEQUENCE
, PPA.PAYROLL_ACTION_ID PAYROLL_ACTION_ID
, PPA.EFFECTIVE_DATE EFFECTIVE_DATE
, PPA.DATE_EARNED DATE_EARNED
, HR_GENERAL.DECODE_LOOKUP('ACTION_TYPE'
, PPA.ACTION_TYPE) ACTION_TYPE
, HR_PAYROLLS.DISPLAY_PERIOD_NAME(PAA.PAYROLL_ACTION_ID) PERIOD_NAME
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'WKPD'
, 'H_DATE') H_DATE
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'WKPD'
, 'L_DATE') L_DATE
FROM PER_PEOPLE_F PP
, PER_ASSIGNMENTS_F PA
, PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
WHERE PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND ( ( PPA.ACTION_TYPE IN ('R'
, 'Q')
AND EXISTS ( SELECT NULL
FROM PAY_RUN_TYPES_F SPRT
WHERE SPRT.RUN_TYPE_ID = NVL(PAA.RUN_TYPE_ID
, PPA.RUN_TYPE_ID)
AND PPA.EFFECTIVE_DATE BETWEEN SPRT.EFFECTIVE_START_DATE
AND SPRT.EFFECTIVE_END_DATE
AND SPRT.RUN_TYPE_NAME LIKE 'SEP%' )
AND PAA.SOURCE_ACTION_ID IS NOT NULL ) OR ( PPA.ACTION_TYPE = 'X'
AND EXISTS ( SELECT NULL
FROM PAY_RUN_TYPES_F XSPRT
, PAY_ASSIGNMENT_ACTIONS XSPAA
, PAY_ACTION_INTERLOCKS XPAI
WHERE XPAI.LOCKING_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND XSPAA.ASSIGNMENT_ACTION_ID = XPAI.LOCKED_ACTION_ID
AND XSPRT.RUN_TYPE_ID = XSPAA.RUN_TYPE_ID
AND PPA.EFFECTIVE_DATE BETWEEN XSPRT.EFFECTIVE_START_DATE
AND XSPRT.EFFECTIVE_END_DATE
AND XSPRT.RUN_TYPE_NAME LIKE 'SEP%' ) ) )
AND PA.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PPA.EFFECTIVE_DATE BETWEEN PA.EFFECTIVE_START_DATE
AND PA.EFFECTIVE_END_DATE
AND PP.PERSON_ID = PA.PERSON_ID
AND PPA.EFFECTIVE_DATE BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE