VIEW: APPS.PAY_KR_SEP_SLIP_V

Source

View Text - Preformatted

SELECT sep.business_group_id business_group_id, sep.legislation_code legislation_code, sep.payroll_id payroll_id, sep.assignment_id assignment_id, sep.assignment_action_id assignment_action_id, sep.effective_date effective_date, sep.date_earned date_earned, sep.run_type_id run_type_id, sep.run_type_name run_type_name, sep.tax_unit_id tax_unit_id, sep.xassignment_action_id xassignment_action_id, sep.last_name last_name, sep.first_name first_name, sep.national_identifier national_identifier, sep.employee_number employee_number, sep.organization_name organization_name, sep.kr_job_title kr_job_title, sep.grade_name grade_name, sep.hiring_date hiring_date, sep.leaving_date leaving_date, sep.working_period working_period, sep.me_1st_std me_1st_std, sep.me_1st_edd me_1st_edd, sep.me_2nd_std me_2nd_std, sep.me_2nd_edd me_2nd_edd, sep.me_3rd_std me_3rd_std, sep.me_3rd_edd me_3rd_edd, sep.me_4th_std me_4th_std, sep.me_4th_edd me_4th_edd, sep.bbalance_type_id bbalance_type_id, sep.vbalance_type_id vbalance_type_id, sep.ivbalance_type_id ivbalance_type_id, bon.bassignment_action_id bassignment_action_id, bon.beffective_date beffective_date, bon.brun_type_id brun_type_id, bon.brun_type_name brun_type_name, pay_kr_report_pkg.get_balance_value_asg_run(bon.bassignment_action_id,sep.bbalance_type_id) bon_amt, sep.person_id from (select bpaa.assignment_id bassignment_id, bppa.effective_date beffective_date, bpaa.assignment_action_id bassignment_action_id, bprt.run_type_id brun_type_id, bprt.run_type_name brun_type_name from pay_run_types_f bprt, pay_payroll_actions bppa, pay_assignment_actions bpaa WHERE bpaa.source_action_id is not null and bpaa.action_status = 'C' and bppa.payroll_action_id = bpaa.payroll_action_id and bprt.run_type_id = bpaa.run_type_id and bppa.effective_date between bprt.effective_start_date and bprt.effective_end_date and substr(bprt.run_type_name,1,3) = 'BON' and bprt.run_type_name <> 'BON_ALR') bon, (select ppa.business_group_id business_group_id, ppa.payroll_id payroll_id, pay_kr_report_pkg.legislation_code(ppa.business_group_id) legislation_code, paa.assignment_id assignment_id, paa.assignment_action_id assignment_action_id, ppa.effective_date effective_date, ppa.date_earned date_earned, prt.run_type_id run_type_id, prt.run_type_name run_type_name, paa.tax_unit_id tax_unit_id, xpaa.assignment_action_id xassignment_action_id, pp.last_name last_name, pp.first_name first_name, pp.national_identifier national_identifier, pp.employee_number employee_number, hou.name organization_name, hr_general.decode_lookup('KR_JOB_TITLE',hsck.segment2) kr_job_title, pgt.name grade_name, bpbt.balance_type_id bbalance_type_id, vpbt.balance_type_id vbalance_type_id, ivpbt.balance_type_id ivbalance_type_id, pay_kr_report_pkg.get_result_value_date(paa.assignment_action_id,ppa.business_group_id,'WKPD','H_DATE') hiring_date, pay_kr_report_pkg.get_result_value_date(paa.assignment_action_id,ppa.business_group_id,'WKPD','L_DATE') leaving_date, pay_kr_report_pkg.get_result_value_char(paa.assignment_action_id,ppa.business_group_id,'WKPD','WKPD') working_period, pay_kr_report_pkg.get_result_value_date(paa.assignment_action_id,ppa.business_group_id,'ME_SUBJ_AVG','ME_1ST_STD') me_1st_std, pay_kr_report_pkg.get_result_value_date(paa.assignment_action_id,ppa.business_group_id,'ME_SUBJ_AVG','ME_1ST_EDD') me_1st_edd, pay_kr_report_pkg.get_result_value_date(paa.assignment_action_id,ppa.business_group_id,'ME_SUBJ_AVG','ME_2ND_STD') me_2nd_std, pay_kr_report_pkg.get_result_value_date(paa.assignment_action_id,ppa.business_group_id,'ME_SUBJ_AVG','ME_2ND_EDD') me_2nd_edd, pay_kr_report_pkg.get_result_value_date(paa.assignment_action_id,ppa.business_group_id,'ME_SUBJ_AVG','ME_3RD_STD') me_3rd_std, pay_kr_report_pkg.get_result_value_date(paa.assignment_action_id,ppa.business_group_id,'ME_SUBJ_AVG','ME_3RD_EDD') me_3rd_edd, pay_kr_report_pkg.get_result_value_date(paa.assignment_action_id,ppa.business_group_id,'ME_SUBJ_AVG_ADD','ME_4TH_STD') me_4th_std, pay_kr_report_pkg.get_result_value_date(paa.assignment_action_id,ppa.business_group_id,'ME_SUBJ_AVG_ADD','ME_4TH_EDD') me_4th_edd, pp.person_id from pay_balance_types ivpbt, pay_balance_types vpbt, pay_balance_types bpbt, per_grades_tl pgt, hr_soft_coding_keyflex hsck, hr_organization_units hou, per_people_f pp, per_assignments_f pa, pay_payroll_actions xppa, pay_assignment_actions xpaa, pay_action_interlocks pai, pay_run_types_f prt, pay_payroll_actions ppa, pay_assignment_actions paa where paa.action_status = 'C' and ppa.payroll_action_id = paa.payroll_action_id and prt.run_type_id = paa.run_type_id and prt.run_type_name in ('SEP','SEP_I') and ppa.effective_date between prt.effective_start_date and prt.effective_end_date and pai.locked_action_id = paa.assignment_action_id and xpaa.assignment_action_id = pai.locking_action_id and xpaa.action_status = 'C' and xppa.payroll_action_id = xpaa.payroll_action_id and xppa.action_type = 'X' 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 and hou.organization_id = pa.organization_id and hsck.soft_coding_keyflex_id(+) = pa.soft_coding_keyflex_id and pgt.grade_id(+) = pa.grade_id and pgt.language (+) = userenv('LANG') and bpbt.balance_name = 'EARNINGS_SUBJ_AVG' and bpbt.legislation_code = 'KR' and vpbt.balance_name = 'VOL_DEDUCTIONS' and vpbt.legislation_code = 'KR' and ivpbt.balance_name = 'INVOL_DEDUCTIONS' and ivpbt.legislation_code = 'KR' and bpbt.legislation_code = vpbt.legislation_code and vpbt.legislation_code = ivpbt.legislation_code ) sep where bon.bassignment_id (+) = sep.assignment_id and bon.beffective_date (+) between add_months(sep.effective_date -1,-12) and (sep.effective_date -1)

View Text - HTML Formatted

SELECT SEP.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, SEP.LEGISLATION_CODE LEGISLATION_CODE
, SEP.PAYROLL_ID PAYROLL_ID
, SEP.ASSIGNMENT_ID ASSIGNMENT_ID
, SEP.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, SEP.EFFECTIVE_DATE EFFECTIVE_DATE
, SEP.DATE_EARNED DATE_EARNED
, SEP.RUN_TYPE_ID RUN_TYPE_ID
, SEP.RUN_TYPE_NAME RUN_TYPE_NAME
, SEP.TAX_UNIT_ID TAX_UNIT_ID
, SEP.XASSIGNMENT_ACTION_ID XASSIGNMENT_ACTION_ID
, SEP.LAST_NAME LAST_NAME
, SEP.FIRST_NAME FIRST_NAME
, SEP.NATIONAL_IDENTIFIER NATIONAL_IDENTIFIER
, SEP.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, SEP.ORGANIZATION_NAME ORGANIZATION_NAME
, SEP.KR_JOB_TITLE KR_JOB_TITLE
, SEP.GRADE_NAME GRADE_NAME
, SEP.HIRING_DATE HIRING_DATE
, SEP.LEAVING_DATE LEAVING_DATE
, SEP.WORKING_PERIOD WORKING_PERIOD
, SEP.ME_1ST_STD ME_1ST_STD
, SEP.ME_1ST_EDD ME_1ST_EDD
, SEP.ME_2ND_STD ME_2ND_STD
, SEP.ME_2ND_EDD ME_2ND_EDD
, SEP.ME_3RD_STD ME_3RD_STD
, SEP.ME_3RD_EDD ME_3RD_EDD
, SEP.ME_4TH_STD ME_4TH_STD
, SEP.ME_4TH_EDD ME_4TH_EDD
, SEP.BBALANCE_TYPE_ID BBALANCE_TYPE_ID
, SEP.VBALANCE_TYPE_ID VBALANCE_TYPE_ID
, SEP.IVBALANCE_TYPE_ID IVBALANCE_TYPE_ID
, BON.BASSIGNMENT_ACTION_ID BASSIGNMENT_ACTION_ID
, BON.BEFFECTIVE_DATE BEFFECTIVE_DATE
, BON.BRUN_TYPE_ID BRUN_TYPE_ID
, BON.BRUN_TYPE_NAME BRUN_TYPE_NAME
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE_ASG_RUN(BON.BASSIGNMENT_ACTION_ID
, SEP.BBALANCE_TYPE_ID) BON_AMT
, SEP.PERSON_ID
FROM (SELECT BPAA.ASSIGNMENT_ID BASSIGNMENT_ID
, BPPA.EFFECTIVE_DATE BEFFECTIVE_DATE
, BPAA.ASSIGNMENT_ACTION_ID BASSIGNMENT_ACTION_ID
, BPRT.RUN_TYPE_ID BRUN_TYPE_ID
, BPRT.RUN_TYPE_NAME BRUN_TYPE_NAME
FROM PAY_RUN_TYPES_F BPRT
, PAY_PAYROLL_ACTIONS BPPA
, PAY_ASSIGNMENT_ACTIONS BPAA
WHERE BPAA.SOURCE_ACTION_ID IS NOT NULL
AND BPAA.ACTION_STATUS = 'C'
AND BPPA.PAYROLL_ACTION_ID = BPAA.PAYROLL_ACTION_ID
AND BPRT.RUN_TYPE_ID = BPAA.RUN_TYPE_ID
AND BPPA.EFFECTIVE_DATE BETWEEN BPRT.EFFECTIVE_START_DATE
AND BPRT.EFFECTIVE_END_DATE
AND SUBSTR(BPRT.RUN_TYPE_NAME
, 1
, 3) = 'BON'
AND BPRT.RUN_TYPE_NAME <> 'BON_ALR') BON
, (SELECT PPA.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PPA.PAYROLL_ID PAYROLL_ID
, PAY_KR_REPORT_PKG.LEGISLATION_CODE(PPA.BUSINESS_GROUP_ID) LEGISLATION_CODE
, PAA.ASSIGNMENT_ID ASSIGNMENT_ID
, PAA.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, PPA.EFFECTIVE_DATE EFFECTIVE_DATE
, PPA.DATE_EARNED DATE_EARNED
, PRT.RUN_TYPE_ID RUN_TYPE_ID
, PRT.RUN_TYPE_NAME RUN_TYPE_NAME
, PAA.TAX_UNIT_ID TAX_UNIT_ID
, XPAA.ASSIGNMENT_ACTION_ID XASSIGNMENT_ACTION_ID
, PP.LAST_NAME LAST_NAME
, PP.FIRST_NAME FIRST_NAME
, PP.NATIONAL_IDENTIFIER NATIONAL_IDENTIFIER
, PP.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, HOU.NAME ORGANIZATION_NAME
, HR_GENERAL.DECODE_LOOKUP('KR_JOB_TITLE'
, HSCK.SEGMENT2) KR_JOB_TITLE
, PGT.NAME GRADE_NAME
, BPBT.BALANCE_TYPE_ID BBALANCE_TYPE_ID
, VPBT.BALANCE_TYPE_ID VBALANCE_TYPE_ID
, IVPBT.BALANCE_TYPE_ID IVBALANCE_TYPE_ID
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'WKPD'
, 'H_DATE') HIRING_DATE
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'WKPD'
, 'L_DATE') LEAVING_DATE
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_CHAR(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'WKPD'
, 'WKPD') WORKING_PERIOD
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'ME_SUBJ_AVG'
, 'ME_1ST_STD') ME_1ST_STD
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'ME_SUBJ_AVG'
, 'ME_1ST_EDD') ME_1ST_EDD
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'ME_SUBJ_AVG'
, 'ME_2ND_STD') ME_2ND_STD
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'ME_SUBJ_AVG'
, 'ME_2ND_EDD') ME_2ND_EDD
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'ME_SUBJ_AVG'
, 'ME_3RD_STD') ME_3RD_STD
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'ME_SUBJ_AVG'
, 'ME_3RD_EDD') ME_3RD_EDD
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'ME_SUBJ_AVG_ADD'
, 'ME_4TH_STD') ME_4TH_STD
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'ME_SUBJ_AVG_ADD'
, 'ME_4TH_EDD') ME_4TH_EDD
, PP.PERSON_ID
FROM PAY_BALANCE_TYPES IVPBT
, PAY_BALANCE_TYPES VPBT
, PAY_BALANCE_TYPES BPBT
, PER_GRADES_TL PGT
, HR_SOFT_CODING_KEYFLEX HSCK
, HR_ORGANIZATION_UNITS HOU
, PER_PEOPLE_F PP
, PER_ASSIGNMENTS_F PA
, PAY_PAYROLL_ACTIONS XPPA
, PAY_ASSIGNMENT_ACTIONS XPAA
, PAY_ACTION_INTERLOCKS PAI
, PAY_RUN_TYPES_F PRT
, PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
WHERE PAA.ACTION_STATUS = 'C'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PRT.RUN_TYPE_ID = PAA.RUN_TYPE_ID
AND PRT.RUN_TYPE_NAME IN ('SEP'
, 'SEP_I')
AND PPA.EFFECTIVE_DATE BETWEEN PRT.EFFECTIVE_START_DATE
AND PRT.EFFECTIVE_END_DATE
AND PAI.LOCKED_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND XPAA.ASSIGNMENT_ACTION_ID = PAI.LOCKING_ACTION_ID
AND XPAA.ACTION_STATUS = 'C'
AND XPPA.PAYROLL_ACTION_ID = XPAA.PAYROLL_ACTION_ID
AND XPPA.ACTION_TYPE = 'X'
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
AND HOU.ORGANIZATION_ID = PA.ORGANIZATION_ID
AND HSCK.SOFT_CODING_KEYFLEX_ID(+) = PA.SOFT_CODING_KEYFLEX_ID
AND PGT.GRADE_ID(+) = PA.GRADE_ID
AND PGT.LANGUAGE (+) = USERENV('LANG')
AND BPBT.BALANCE_NAME = 'EARNINGS_SUBJ_AVG'
AND BPBT.LEGISLATION_CODE = 'KR'
AND VPBT.BALANCE_NAME = 'VOL_DEDUCTIONS'
AND VPBT.LEGISLATION_CODE = 'KR'
AND IVPBT.BALANCE_NAME = 'INVOL_DEDUCTIONS'
AND IVPBT.LEGISLATION_CODE = 'KR'
AND BPBT.LEGISLATION_CODE = VPBT.LEGISLATION_CODE
AND VPBT.LEGISLATION_CODE = IVPBT.LEGISLATION_CODE ) SEP
WHERE BON.BASSIGNMENT_ID (+) = SEP.ASSIGNMENT_ID
AND BON.BEFFECTIVE_DATE (+) BETWEEN ADD_MONTHS(SEP.EFFECTIVE_DATE -1
, -12)
AND (SEP.EFFECTIVE_DATE -1)