VIEW: APPS.PAY_KR_SEP_RESULT_SLIP_V

Source

View Text - Preformatted

SELECT /*+RULE*/ 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, 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_balance_value(paa.assignment_action_id,'WKPD_SEP_PAY','_ASG_RUN') wkpd_sep_pay, 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_balance_value(paa.assignment_action_id,'ME_1ST_WKD','_ASG_RUN') me_1st_wkd, pay_kr_report_pkg.get_balance_value(paa.assignment_action_id,'ME_1ST_SUBJ_AVG','_ASG_RUN') me_1st_subj_avg, 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_balance_value(paa.assignment_action_id,'ME_2ND_WKD','_ASG_RUN') me_2nd_wkd, pay_kr_report_pkg.get_balance_value(paa.assignment_action_id,'ME_2ND_SUBJ_AVG','_ASG_RUN') me_2nd_subj_avg, 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_balance_value(paa.assignment_action_id,'ME_3RD_WKD','_ASG_RUN') me_3rd_wkd, pay_kr_report_pkg.get_balance_value(paa.assignment_action_id,'ME_3RD_SUBJ_AVG','_ASG_RUN') me_3rd_subj_avg, 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, pay_kr_report_pkg.get_balance_value(paa.assignment_action_id,'ME_4TH_WKD','_ASG_RUN') me_4th_wkd, pay_kr_report_pkg.get_balance_value(paa.assignment_action_id,'ME_4TH_SUBJ_AVG','_ASG_RUN') me_4th_subj_avg, pay_kr_report_pkg.get_balance_value(paa.assignment_action_id,'ALR_SUBJ_AVG','_ASG_RUN') alr_subj_avg, pay_kr_report_pkg.get_balance_value(paa.assignment_action_id,'YBON_SUBJ_AVG','_ASG_RUN') ybon_subj_avg, pay_kr_report_pkg.get_balance_value(paa.assignment_action_id,'AVERAGE_SALARY_ME','_ASG_RUN') avg_sal_me, pay_kr_report_pkg.get_balance_value(paa.assignment_action_id,'AVERAGE_SALARY_ALR','_ASG_RUN') avg_sal_alr, pay_kr_report_pkg.get_balance_value(paa.assignment_action_id,'AVERAGE_SALARY_YBON','_ASG_RUN') avg_sal_ybon, pay_kr_report_pkg.get_balance_value(paa.assignment_action_id,'AVERAGE_SALARY','_ASG_RUN') avg_sal, pay_kr_report_pkg.get_balance_value(paa.assignment_action_id,'SEP_PAY','_ASG_RUN') sep_pay, pay_kr_report_pkg.get_dbitem_value(paa.assignment_action_id,'KR_LMTH_REGULAR_SALARY') last_mth_regular_salary from 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

View Text - HTML Formatted

SELECT /*+RULE*/ 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
, 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_BALANCE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'WKPD_SEP_PAY'
, '_ASG_RUN') WKPD_SEP_PAY
, 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_BALANCE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ME_1ST_WKD'
, '_ASG_RUN') ME_1ST_WKD
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ME_1ST_SUBJ_AVG'
, '_ASG_RUN') ME_1ST_SUBJ_AVG
, 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_BALANCE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ME_2ND_WKD'
, '_ASG_RUN') ME_2ND_WKD
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ME_2ND_SUBJ_AVG'
, '_ASG_RUN') ME_2ND_SUBJ_AVG
, 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_BALANCE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ME_3RD_WKD'
, '_ASG_RUN') ME_3RD_WKD
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ME_3RD_SUBJ_AVG'
, '_ASG_RUN') ME_3RD_SUBJ_AVG
, 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
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ME_4TH_WKD'
, '_ASG_RUN') ME_4TH_WKD
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ME_4TH_SUBJ_AVG'
, '_ASG_RUN') ME_4TH_SUBJ_AVG
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ALR_SUBJ_AVG'
, '_ASG_RUN') ALR_SUBJ_AVG
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'YBON_SUBJ_AVG'
, '_ASG_RUN') YBON_SUBJ_AVG
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'AVERAGE_SALARY_ME'
, '_ASG_RUN') AVG_SAL_ME
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'AVERAGE_SALARY_ALR'
, '_ASG_RUN') AVG_SAL_ALR
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'AVERAGE_SALARY_YBON'
, '_ASG_RUN') AVG_SAL_YBON
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'AVERAGE_SALARY'
, '_ASG_RUN') AVG_SAL
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'SEP_PAY'
, '_ASG_RUN') SEP_PAY
, PAY_KR_REPORT_PKG.GET_DBITEM_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'KR_LMTH_REGULAR_SALARY') LAST_MTH_REGULAR_SALARY
FROM 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