eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PAY_KR_SEP_LIAB_DETAIL_V

Source

View Text - Preformatted


SELECT paa.assignment_id assignment_id ,pet.reporting_name element_name ,pet.element_type_id element_type_id ,nvl(to_number(pay_kr_report_pkg.get_result_value(prr.run_result_id, piv.input_value_id)),0) result_value ,papf.full_name employee_name ,papf.employee_number employee_number ,ppa.business_group_id business_group_id ,ppa.time_period_id time_period_id ,paa.assignment_action_id assignment_action_id ,ppa.payroll_id payroll_id ,ppos.date_start date_start ,pgr.name grade_name ,hoi.org_information1 establishment_name ,hoi.organization_id establishment_id ,nvl(pcvf.proportion,1) proportion ,pay_kr_report_pkg.get_result_value_number(paa.assignment_action_id,ppa.business_group_id,'YBON_SUBJ_AVG','AMOUNT') * nvl(pcvf.proportion,1) yearly_bonus ,pay_kr_report_pkg.get_result_value_number(paa.assignment_action_id,ppa.business_group_id,'ALR_SUBJ_AVG','AMOUNT') * nvl(pcvf.proportion,1) alr ,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,'AVERAGE_SALARY_ME','_ASG_RUN') * nvl(pcvf.proportion,1) average_salary_me ,pay_kr_report_pkg.get_balance_value(paa.assignment_action_id,'AVERAGE_SALARY_YBON','_ASG_RUN') * nvl(pcvf.proportion,1) average_salary_ybon ,pay_kr_report_pkg.get_balance_value(paa.assignment_action_id,'AVERAGE_SALARY_ALR','_ASG_RUN')* nvl(pcvf.proportion,1) average_salary_alr ,pay_kr_report_pkg.get_balance_value(paa.assignment_action_id,'SEP_PAY','_ASG_RUN') * nvl(pcvf.proportion,1) separation_pay ,decode(pay_kr_report_pkg.get_result_value_date(paa.assignment_action_id,ppa.business_group_id,'WKPD','L_DATE'),null ,ppos.actual_termination_date ,pay_kr_report_pkg.get_result_value_date(paa.assignment_action_id,ppa.business_group_id,'WKPD','L_DATE')) Separation_date ,pay_kr_ff_functions_pkg.get_legislative_parameter(ppa.payroll_action_id, 'LIABILITY_RATE',0) liability_rate ,pay_kr_report_pkg.get_balance_value(paa.assignment_action_id,'PAYMENT_DAYS','_ASG_RUN') * nvl(pcvf.proportion,1) payment_days ,decode(pm.payroll_cost_segment, 'SEGMENT1' , pkv.segment1, 'SEGMENT2' , pkv.segment2, 'SEGMENT3' , pkv.segment3, 'SEGMENT4' , pkv.segment4, 'SEGMENT5' , pkv.segment5, 'SEGMENT6' , pkv.segment6, 'SEGMENT7' , pkv.segment7, 'SEGMENT8' , pkv.segment8, 'SEGMENT9' , pkv.segment9, 'SEGMENT10' , pkv.segment10, 'SEGMENT11' , pkv.segment11, 'SEGMENT12' , pkv.segment12, 'SEGMENT13' , pkv.segment13, 'SEGMENT14' , pkv.segment14, 'SEGMENT15' , pkv.segment15, 'SEGMENT16' , pkv.segment16, 'SEGMENT17' , pkv.segment17, 'SEGMENT18' , pkv.segment18, 'SEGMENT19' , pkv.segment19) cost_center FROM pay_balance_feeds_f pbf ,pay_input_values_f piv ,pay_element_types_f pet ,pay_run_results prr ,pay_payroll_actions ppa ,hr_organization_information hoi ,per_grades pgr ,pay_cost_allocation_keyflex pkv ,fnd_segment_attribute_values fsav ,pay_cost_allocations_f pcvf ,pay_payroll_gl_flex_maps pm ,pay_assignment_actions paa ,pay_run_types_f prt ,per_assignments_f paaf ,pay_payrolls_f ppf ,per_periods_of_service ppos ,per_people_f papf ,gl_sets_of_books gsb WHERE pbf.balance_type_id = (SELECT balance_type_id FROM pay_balance_types WHERE balance_name = 'EARNINGS_SUBJ_AVG' AND legislation_code ='KR') AND pbf.input_value_id = piv.input_value_id AND piv.element_type_id = pet.element_type_id AND prr.element_type_id = piv.element_type_id AND ppa.run_type_id = prt.run_type_id AND prt.run_type_id = paa.run_type_id AND prt.run_type_name = 'SEP_L' AND prt.legislation_code = 'KR' AND ppa.payroll_action_id = paa.payroll_action_id AND prr.assignment_action_id = paa.assignment_action_id AND ppa.business_group_id = paaf.business_group_id AND ppa.payroll_id = pm.payroll_id AND ppa.payroll_id = ppf.payroll_id AND ppa.payroll_action_id = paa.payroll_action_id AND papf.person_id = paaf.person_id AND ppos.period_of_service_id = paaf.period_of_service_id AND papf.person_id = ppos.person_id AND paa.tax_unit_id = hoi.organization_id AND hoi.organization_id = paaf.establishment_id AND hoi.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION' AND paaf.grade_id = pgr.grade_id(+) AND paaf.assignment_id = paa.assignment_id AND paaf.payroll_id = ppa.payroll_id AND ppf.payroll_id = paaf.payroll_id AND paa.assignment_id = pcvf.assignment_id(+) AND pcvf.cost_allocation_keyflex_id = pkv.cost_allocation_keyflex_id(+) AND fsav.application_column_name = pm.gl_account_segment AND fsav.application_id = 101 AND fsav.segment_attribute_type = 'FA_COST_CTR' AND fsav.attribute_value = 'Y' AND fsav.id_flex_code = 'GL#' AND (pkv.id_flex_num = fsav.id_flex_num OR pkv.id_flex_num is null) AND ppf.gl_set_of_books_id = pm.gl_set_of_books_id AND ppf.gl_set_of_books_id = gsb.set_of_books_id AND gsb.chart_of_accounts_id = fsav.id_flex_num AND (pay_kr_ff_functions_pkg.get_legislative_parameter(ppa.payroll_action_id, 'LIABILITY_RATE',0)) <> 0 AND ppa.effective_date between paaf.effective_start_date AND paaf.effective_end_date AND ppa.effective_date between prt.effective_start_date AND prt.effective_end_date AND ppa.effective_date between nvl(pcvf.effective_start_date,ppa.effective_date) AND nvl(pcvf.effective_end_date,ppa.effective_date)

View Text - HTML Formatted

SELECT PAA.ASSIGNMENT_ID ASSIGNMENT_ID
, PET.REPORTING_NAME ELEMENT_NAME
, PET.ELEMENT_TYPE_ID ELEMENT_TYPE_ID
, NVL(TO_NUMBER(PAY_KR_REPORT_PKG.GET_RESULT_VALUE(PRR.RUN_RESULT_ID
, PIV.INPUT_VALUE_ID))
, 0) RESULT_VALUE
, PAPF.FULL_NAME EMPLOYEE_NAME
, PAPF.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PPA.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PPA.TIME_PERIOD_ID TIME_PERIOD_ID
, PAA.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, PPA.PAYROLL_ID PAYROLL_ID
, PPOS.DATE_START DATE_START
, PGR.NAME GRADE_NAME
, HOI.ORG_INFORMATION1 ESTABLISHMENT_NAME
, HOI.ORGANIZATION_ID ESTABLISHMENT_ID
, NVL(PCVF.PROPORTION
, 1) PROPORTION
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_NUMBER(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'YBON_SUBJ_AVG'
, 'AMOUNT') * NVL(PCVF.PROPORTION
, 1) YEARLY_BONUS
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_NUMBER(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'ALR_SUBJ_AVG'
, 'AMOUNT') * NVL(PCVF.PROPORTION
, 1) ALR
, 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
, 'AVERAGE_SALARY_ME'
, '_ASG_RUN') * NVL(PCVF.PROPORTION
, 1) AVERAGE_SALARY_ME
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'AVERAGE_SALARY_YBON'
, '_ASG_RUN') * NVL(PCVF.PROPORTION
, 1) AVERAGE_SALARY_YBON
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'AVERAGE_SALARY_ALR'
, '_ASG_RUN')* NVL(PCVF.PROPORTION
, 1) AVERAGE_SALARY_ALR
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'SEP_PAY'
, '_ASG_RUN') * NVL(PCVF.PROPORTION
, 1) SEPARATION_PAY
, DECODE(PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'WKPD'
, 'L_DATE')
, NULL
, PPOS.ACTUAL_TERMINATION_DATE
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'WKPD'
, 'L_DATE')) SEPARATION_DATE
, PAY_KR_FF_FUNCTIONS_PKG.GET_LEGISLATIVE_PARAMETER(PPA.PAYROLL_ACTION_ID
, 'LIABILITY_RATE'
, 0) LIABILITY_RATE
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'PAYMENT_DAYS'
, '_ASG_RUN') * NVL(PCVF.PROPORTION
, 1) PAYMENT_DAYS
, DECODE(PM.PAYROLL_COST_SEGMENT
, 'SEGMENT1'
, PKV.SEGMENT1
, 'SEGMENT2'
, PKV.SEGMENT2
, 'SEGMENT3'
, PKV.SEGMENT3
, 'SEGMENT4'
, PKV.SEGMENT4
, 'SEGMENT5'
, PKV.SEGMENT5
, 'SEGMENT6'
, PKV.SEGMENT6
, 'SEGMENT7'
, PKV.SEGMENT7
, 'SEGMENT8'
, PKV.SEGMENT8
, 'SEGMENT9'
, PKV.SEGMENT9
, 'SEGMENT10'
, PKV.SEGMENT10
, 'SEGMENT11'
, PKV.SEGMENT11
, 'SEGMENT12'
, PKV.SEGMENT12
, 'SEGMENT13'
, PKV.SEGMENT13
, 'SEGMENT14'
, PKV.SEGMENT14
, 'SEGMENT15'
, PKV.SEGMENT15
, 'SEGMENT16'
, PKV.SEGMENT16
, 'SEGMENT17'
, PKV.SEGMENT17
, 'SEGMENT18'
, PKV.SEGMENT18
, 'SEGMENT19'
, PKV.SEGMENT19) COST_CENTER
FROM PAY_BALANCE_FEEDS_F PBF
, PAY_INPUT_VALUES_F PIV
, PAY_ELEMENT_TYPES_F PET
, PAY_RUN_RESULTS PRR
, PAY_PAYROLL_ACTIONS PPA
, HR_ORGANIZATION_INFORMATION HOI
, PER_GRADES PGR
, PAY_COST_ALLOCATION_KEYFLEX PKV
, FND_SEGMENT_ATTRIBUTE_VALUES FSAV
, PAY_COST_ALLOCATIONS_F PCVF
, PAY_PAYROLL_GL_FLEX_MAPS PM
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_RUN_TYPES_F PRT
, PER_ASSIGNMENTS_F PAAF
, PAY_PAYROLLS_F PPF
, PER_PERIODS_OF_SERVICE PPOS
, PER_PEOPLE_F PAPF
, GL_SETS_OF_BOOKS GSB
WHERE PBF.BALANCE_TYPE_ID = (SELECT BALANCE_TYPE_ID
FROM PAY_BALANCE_TYPES
WHERE BALANCE_NAME = 'EARNINGS_SUBJ_AVG'
AND LEGISLATION_CODE ='KR')
AND PBF.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PRR.ELEMENT_TYPE_ID = PIV.ELEMENT_TYPE_ID
AND PPA.RUN_TYPE_ID = PRT.RUN_TYPE_ID
AND PRT.RUN_TYPE_ID = PAA.RUN_TYPE_ID
AND PRT.RUN_TYPE_NAME = 'SEP_L'
AND PRT.LEGISLATION_CODE = 'KR'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PPA.BUSINESS_GROUP_ID = PAAF.BUSINESS_GROUP_ID
AND PPA.PAYROLL_ID = PM.PAYROLL_ID
AND PPA.PAYROLL_ID = PPF.PAYROLL_ID
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAPF.PERSON_ID = PAAF.PERSON_ID
AND PPOS.PERIOD_OF_SERVICE_ID = PAAF.PERIOD_OF_SERVICE_ID
AND PAPF.PERSON_ID = PPOS.PERSON_ID
AND PAA.TAX_UNIT_ID = HOI.ORGANIZATION_ID
AND HOI.ORGANIZATION_ID = PAAF.ESTABLISHMENT_ID
AND HOI.ORG_INFORMATION_CONTEXT = 'KR_BUSINESS_PLACE_REGISTRATION'
AND PAAF.GRADE_ID = PGR.GRADE_ID(+)
AND PAAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PAAF.PAYROLL_ID = PPA.PAYROLL_ID
AND PPF.PAYROLL_ID = PAAF.PAYROLL_ID
AND PAA.ASSIGNMENT_ID = PCVF.ASSIGNMENT_ID(+)
AND PCVF.COST_ALLOCATION_KEYFLEX_ID = PKV.COST_ALLOCATION_KEYFLEX_ID(+)
AND FSAV.APPLICATION_COLUMN_NAME = PM.GL_ACCOUNT_SEGMENT
AND FSAV.APPLICATION_ID = 101
AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'FA_COST_CTR'
AND FSAV.ATTRIBUTE_VALUE = 'Y'
AND FSAV.ID_FLEX_CODE = 'GL#'
AND (PKV.ID_FLEX_NUM = FSAV.ID_FLEX_NUM OR PKV.ID_FLEX_NUM IS NULL)
AND PPF.GL_SET_OF_BOOKS_ID = PM.GL_SET_OF_BOOKS_ID
AND PPF.GL_SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID
AND GSB.CHART_OF_ACCOUNTS_ID = FSAV.ID_FLEX_NUM
AND (PAY_KR_FF_FUNCTIONS_PKG.GET_LEGISLATIVE_PARAMETER(PPA.PAYROLL_ACTION_ID
, 'LIABILITY_RATE'
, 0)) <> 0
AND PPA.EFFECTIVE_DATE BETWEEN PAAF.EFFECTIVE_START_DATE
AND PAAF.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN PRT.EFFECTIVE_START_DATE
AND PRT.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN NVL(PCVF.EFFECTIVE_START_DATE
, PPA.EFFECTIVE_DATE)
AND NVL(PCVF.EFFECTIVE_END_DATE
, PPA.EFFECTIVE_DATE)