VIEW: APPS.PAY_KR_ASG_ELEMENTS_V

Source

View Text - Preformatted

SELECT assignment_action_id assignment_action_id, element_name element_reporting_name, processing_priority processing_priority, classification_name classification_name, sum(amount) amount, sum(hours) hours from (select ppac.assignment_action_id, prr.run_result_id, prr.element_type_id, pet.processing_priority, nvl(petl.reporting_name, petl.element_name) element_name, decode(instr(pec.classification_name,'EARNINGS'),0,null,'EARNINGS') ||Decode( pec.classification_name,'DIRECT_PAYMENTS','EARNINGS',null) ||Decode( pet.element_name,'KR Normal Hours','EARNINGS',null) ||Decode( pec.classification_name,'BASE_SALARY','EARNINGS',null) ||decode(instr(pec.classification_name,'DEDUCTIONS'),0,null,'DEDUCTIONS') classification_name, decode(decode(substr(piv.uom,1,2),'H_','H','P' ), 'P', prrv.result_value, null) amount, decode(decode(substr(piv.uom,1,2),'H_','H','P' ), 'H', prrv.result_value, null) hours from pay_assignment_actions ppac, per_assignments_f paa, pay_assignment_actions pac, pay_payroll_actions ppa, pay_payroll_actions pppa, pay_action_interlocks pai, pay_run_Results prr, pay_run_Result_values prrv, pay_input_values_f piv, per_business_groups_perf bg, pay_element_classifications pec, pay_element_types_f pet, pay_element_types_f_tl petl, per_people_f pap WHERE pac.assignment_id = paa.assignment_id and paa.person_id = pap.person_id and pap.business_group_id = bg.business_group_id and pac.payroll_action_id = ppa.payroll_action_id and nvl(pet.business_group_id,bg.business_group_id) = bg.business_group_id and bg.legislation_code = 'KR' and ppa.action_type in ('R','Q') and ppa.action_status = 'C' and pac.action_status = 'C' and ppac.payroll_action_id = pppa.payroll_action_id and pppa.action_type in ('P','U') and pppa.action_status = 'C' and ppac.action_status = 'C' and pac.assignment_action_id = pai.locked_action_id and ppac.assignment_action_id = pai.locking_action_id and pppa.effective_date between paa.effective_start_date and paa.effective_end_date and pppa.effective_date between pap.effective_start_date and pap.effective_end_date and prr.assignment_action_id = pac.assignment_action_id and prr.run_result_id = prrv.run_result_id and piv.input_value_id = prrv.input_value_id and (piv.name ='Pay Value' or substr(piv.uom,1,2)='H_') and ppa.effective_date between piv.effective_start_date and piv.effective_end_date and nvl(piv.legislation_code, 'KR') = 'KR' and nvl(piv.business_group_id,bg.business_group_id ) = bg.business_group_id and pec.classification_id = pet.classification_id and ( (pec.classification_name in ( 'BASE_SALARY' ,'TAXABLE_EARNINGS' ,'DIRECT_PAYMENTS' ,'NON_TAXABLE_EARNINGS_IN_KIND' ,'TAXABLE_EARNINGS_IN_KIND' ,'NON_TAXABLE_EARNINGS' ,'NON_TAXABLE_EARNINGS_SUBJ_HI' ,'NON_TAXABLE_EARNINGS_SUBJ_NP' ,'NON_TAXABLE_EARNINGS_SUBJ_EI' ,'TAXABLE_EARNINGS_IN_KIND_SUBJ_REG_TAX' ,'TAXABLE_EARNINGS_IN_KIND_SUBJ_EI' ,'TAXABLE_EARNINGS_IN_KIND_SUBJ_HI' ,'TAXABLE_EARNINGS_IN_KIND_SUBJ_NP' ,'NON_TAXABLE_EARNINGS_IN_KIND_SUBJ_HI' ,'NON_TAXABLE_EARNINGS_IN_KIND_SUBJ_NP' ,'NON_TAXABLE_EARNINGS_IN_KIND_SUBJ_EI' ,'TAXABLE_EARNINGS_SUBJ_REG_TAX' ,'TAXABLE_EARNINGS_SUBJ_HI' ,'TAXABLE_EARNINGS_SUBJ_NP' ,'TAXABLE_EARNINGS_SUBJ_EI' ,'INVOL_DEDUCTIONS' ,'VOL_DEDUCTIONS' ,'TAX_DEDUCTIONS' ,'COURT_ORDER_DEDUCTIONS' ,'PRE_TAX_DEDUCTIONS' ) and pec.legislation_code = 'KR' ) or ( pet.element_name = 'KR Normal Hours' ) ) and nvl(pet.legislation_code, 'KR') = 'KR' and nvl(pet.business_group_id,bg.business_group_id) = bg.business_group_id and ppa.effective_date between pet.effective_start_date and pet.effective_end_date and pet.element_type_id = prr.element_type_id and petl.element_type_id = pet.element_type_id and petl.language = userenv('LANG') ) group by assignment_action_id, element_name, processing_priority, classification_name

View Text - HTML Formatted

SELECT ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, ELEMENT_NAME ELEMENT_REPORTING_NAME
, PROCESSING_PRIORITY PROCESSING_PRIORITY
, CLASSIFICATION_NAME CLASSIFICATION_NAME
, SUM(AMOUNT) AMOUNT
, SUM(HOURS) HOURS
FROM (SELECT PPAC.ASSIGNMENT_ACTION_ID
, PRR.RUN_RESULT_ID
, PRR.ELEMENT_TYPE_ID
, PET.PROCESSING_PRIORITY
, NVL(PETL.REPORTING_NAME
, PETL.ELEMENT_NAME) ELEMENT_NAME
, DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'EARNINGS')
, 0
, NULL
, 'EARNINGS') ||DECODE( PEC.CLASSIFICATION_NAME
, 'DIRECT_PAYMENTS'
, 'EARNINGS'
, NULL) ||DECODE( PET.ELEMENT_NAME
, 'KR NORMAL HOURS'
, 'EARNINGS'
, NULL) ||DECODE( PEC.CLASSIFICATION_NAME
, 'BASE_SALARY'
, 'EARNINGS'
, NULL) ||DECODE(INSTR(PEC.CLASSIFICATION_NAME
, 'DEDUCTIONS')
, 0
, NULL
, 'DEDUCTIONS') CLASSIFICATION_NAME
, DECODE(DECODE(SUBSTR(PIV.UOM
, 1
, 2)
, 'H_'
, 'H'
, 'P' )
, 'P'
, PRRV.RESULT_VALUE
, NULL) AMOUNT
, DECODE(DECODE(SUBSTR(PIV.UOM
, 1
, 2)
, 'H_'
, 'H'
, 'P' )
, 'H'
, PRRV.RESULT_VALUE
, NULL) HOURS
FROM PAY_ASSIGNMENT_ACTIONS PPAC
, PER_ASSIGNMENTS_F PAA
, PAY_ASSIGNMENT_ACTIONS PAC
, PAY_PAYROLL_ACTIONS PPA
, PAY_PAYROLL_ACTIONS PPPA
, PAY_ACTION_INTERLOCKS PAI
, PAY_RUN_RESULTS PRR
, PAY_RUN_RESULT_VALUES PRRV
, PAY_INPUT_VALUES_F PIV
, PER_BUSINESS_GROUPS_PERF BG
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_TYPES_F_TL PETL
, PER_PEOPLE_F PAP
WHERE PAC.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PAA.PERSON_ID = PAP.PERSON_ID
AND PAP.BUSINESS_GROUP_ID = BG.BUSINESS_GROUP_ID
AND PAC.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND NVL(PET.BUSINESS_GROUP_ID
, BG.BUSINESS_GROUP_ID) = BG.BUSINESS_GROUP_ID
AND BG.LEGISLATION_CODE = 'KR'
AND PPA.ACTION_TYPE IN ('R'
, 'Q')
AND PPA.ACTION_STATUS = 'C'
AND PAC.ACTION_STATUS = 'C'
AND PPAC.PAYROLL_ACTION_ID = PPPA.PAYROLL_ACTION_ID
AND PPPA.ACTION_TYPE IN ('P'
, 'U')
AND PPPA.ACTION_STATUS = 'C'
AND PPAC.ACTION_STATUS = 'C'
AND PAC.ASSIGNMENT_ACTION_ID = PAI.LOCKED_ACTION_ID
AND PPAC.ASSIGNMENT_ACTION_ID = PAI.LOCKING_ACTION_ID
AND PPPA.EFFECTIVE_DATE BETWEEN PAA.EFFECTIVE_START_DATE
AND PAA.EFFECTIVE_END_DATE
AND PPPA.EFFECTIVE_DATE BETWEEN PAP.EFFECTIVE_START_DATE
AND PAP.EFFECTIVE_END_DATE
AND PRR.ASSIGNMENT_ACTION_ID = PAC.ASSIGNMENT_ACTION_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PIV.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND (PIV.NAME ='PAY VALUE' OR SUBSTR(PIV.UOM
, 1
, 2)='H_')
AND PPA.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND NVL(PIV.LEGISLATION_CODE
, 'KR') = 'KR'
AND NVL(PIV.BUSINESS_GROUP_ID
, BG.BUSINESS_GROUP_ID ) = BG.BUSINESS_GROUP_ID
AND PEC.CLASSIFICATION_ID = PET.CLASSIFICATION_ID
AND ( (PEC.CLASSIFICATION_NAME IN ( 'BASE_SALARY'
, 'TAXABLE_EARNINGS'
, 'DIRECT_PAYMENTS'
, 'NON_TAXABLE_EARNINGS_IN_KIND'
, 'TAXABLE_EARNINGS_IN_KIND'
, 'NON_TAXABLE_EARNINGS'
, 'NON_TAXABLE_EARNINGS_SUBJ_HI'
, 'NON_TAXABLE_EARNINGS_SUBJ_NP'
, 'NON_TAXABLE_EARNINGS_SUBJ_EI'
, 'TAXABLE_EARNINGS_IN_KIND_SUBJ_REG_TAX'
, 'TAXABLE_EARNINGS_IN_KIND_SUBJ_EI'
, 'TAXABLE_EARNINGS_IN_KIND_SUBJ_HI'
, 'TAXABLE_EARNINGS_IN_KIND_SUBJ_NP'
, 'NON_TAXABLE_EARNINGS_IN_KIND_SUBJ_HI'
, 'NON_TAXABLE_EARNINGS_IN_KIND_SUBJ_NP'
, 'NON_TAXABLE_EARNINGS_IN_KIND_SUBJ_EI'
, 'TAXABLE_EARNINGS_SUBJ_REG_TAX'
, 'TAXABLE_EARNINGS_SUBJ_HI'
, 'TAXABLE_EARNINGS_SUBJ_NP'
, 'TAXABLE_EARNINGS_SUBJ_EI'
, 'INVOL_DEDUCTIONS'
, 'VOL_DEDUCTIONS'
, 'TAX_DEDUCTIONS'
, 'COURT_ORDER_DEDUCTIONS'
, 'PRE_TAX_DEDUCTIONS' )
AND PEC.LEGISLATION_CODE = 'KR' ) OR ( PET.ELEMENT_NAME = 'KR NORMAL HOURS' ) )
AND NVL(PET.LEGISLATION_CODE
, 'KR') = 'KR'
AND NVL(PET.BUSINESS_GROUP_ID
, BG.BUSINESS_GROUP_ID) = BG.BUSINESS_GROUP_ID
AND PPA.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND PET.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PETL.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PETL.LANGUAGE = USERENV('LANG') ) GROUP BY ASSIGNMENT_ACTION_ID
, ELEMENT_NAME
, PROCESSING_PRIORITY
, CLASSIFICATION_NAME