VIEW: APPS.PAY_KR_SEP_RESULT_SLIP_YB_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, bpaa.assignment_action_id bassignment_action_id, bpaa.action_sequence baction_sequence, bppa.effective_date beffective_date, bppa.date_earned bdate_earned, bprt.run_type_id brun_type_id, bprt.run_type_name brun_type_name, bprttl.run_type_name ubrun_type_name, pay_kr_report_pkg.get_balance_value_asg_run(bpaa.assignment_action_id,bpbt.balance_type_id) bearnings_subj_avg from pay_run_types_f_tl bprttl, pay_run_types_f bprt, pay_payroll_actions bppa, pay_assignment_actions bpaa, pay_balance_types bpbt, pay_payroll_actions ppa, pay_assignment_actions paa where ppa.payroll_action_id = paa.payroll_action_id and bpbt.balance_name = 'EARNINGS_SUBJ_AVG' and bpbt.legislation_code = 'KR' and bpaa.assignment_id = paa.assignment_id and bpaa.action_sequence < paa.action_sequence and bpaa.action_status = 'C' and bppa.payroll_action_id = bpaa.payroll_action_id and bppa.effective_date between add_months(ppa.effective_date -1,-12) and (ppa.effective_date) and bprt.run_type_id = bpaa.run_type_id and substr(bprt.run_type_name,1,3) = 'BON' and bprt.run_type_name <> 'BON_ALR' and bppa.effective_date between bprt.effective_start_date and bprt.effective_end_date and bprttl.run_type_id = bprt.run_type_id and bprttl.language = userenv('LANG') and exists( select null from pay_run_result_values bprrv, pay_run_results bprr, pay_balance_feeds_f bpbf where bpbf.balance_type_id = bpbt.balance_type_id and bppa.effective_date between bpbf.effective_start_date and bpbf.effective_end_date and bprr.assignment_action_id = bpaa.assignment_action_id and bprrv.run_result_id = bprr.run_result_id and bprrv.input_value_id = bpbf.input_value_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
, BPAA.ASSIGNMENT_ACTION_ID BASSIGNMENT_ACTION_ID
, BPAA.ACTION_SEQUENCE BACTION_SEQUENCE
, BPPA.EFFECTIVE_DATE BEFFECTIVE_DATE
, BPPA.DATE_EARNED BDATE_EARNED
, BPRT.RUN_TYPE_ID BRUN_TYPE_ID
, BPRT.RUN_TYPE_NAME BRUN_TYPE_NAME
, BPRTTL.RUN_TYPE_NAME UBRUN_TYPE_NAME
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE_ASG_RUN(BPAA.ASSIGNMENT_ACTION_ID
, BPBT.BALANCE_TYPE_ID) BEARNINGS_SUBJ_AVG
FROM PAY_RUN_TYPES_F_TL BPRTTL
, PAY_RUN_TYPES_F BPRT
, PAY_PAYROLL_ACTIONS BPPA
, PAY_ASSIGNMENT_ACTIONS BPAA
, PAY_BALANCE_TYPES BPBT
, PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
WHERE PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND BPBT.BALANCE_NAME = 'EARNINGS_SUBJ_AVG'
AND BPBT.LEGISLATION_CODE = 'KR'
AND BPAA.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND BPAA.ACTION_SEQUENCE < PAA.ACTION_SEQUENCE
AND BPAA.ACTION_STATUS = 'C'
AND BPPA.PAYROLL_ACTION_ID = BPAA.PAYROLL_ACTION_ID
AND BPPA.EFFECTIVE_DATE BETWEEN ADD_MONTHS(PPA.EFFECTIVE_DATE -1
, -12)
AND (PPA.EFFECTIVE_DATE)
AND BPRT.RUN_TYPE_ID = BPAA.RUN_TYPE_ID
AND SUBSTR(BPRT.RUN_TYPE_NAME
, 1
, 3) = 'BON'
AND BPRT.RUN_TYPE_NAME <> 'BON_ALR'
AND BPPA.EFFECTIVE_DATE BETWEEN BPRT.EFFECTIVE_START_DATE
AND BPRT.EFFECTIVE_END_DATE
AND BPRTTL.RUN_TYPE_ID = BPRT.RUN_TYPE_ID
AND BPRTTL.LANGUAGE = USERENV('LANG')
AND EXISTS( SELECT NULL
FROM PAY_RUN_RESULT_VALUES BPRRV
, PAY_RUN_RESULTS BPRR
, PAY_BALANCE_FEEDS_F BPBF
WHERE BPBF.BALANCE_TYPE_ID = BPBT.BALANCE_TYPE_ID
AND BPPA.EFFECTIVE_DATE BETWEEN BPBF.EFFECTIVE_START_DATE
AND BPBF.EFFECTIVE_END_DATE
AND BPRR.ASSIGNMENT_ACTION_ID = BPAA.ASSIGNMENT_ACTION_ID
AND BPRRV.RUN_RESULT_ID = BPRR.RUN_RESULT_ID
AND BPRRV.INPUT_VALUE_ID = BPBF.INPUT_VALUE_ID)