VIEW: APPS.PAY_ASSIGNMENT_ACTIONS_V3

Source

View Text - Preformatted

SELECT /*+ LEADING(aac)*/ AAC.ASSIGNMENT_ID ASSIGNMENT_ID , PET.ELEMENT_TYPE_ID ELEMENT_TYPE_ID , PAC.EFFECTIVE_DATE EFFECTIVE_DATE , HR_PAYROLLS.DISPLAY_PERIOD_NAME(PAC.PAYROLL_ACTION_ID) PERIOD_NAME , HR_GENERAL.DECODE_LOOKUP('ACTION_TYPE', PAC.ACTION_TYPE) ACTION_TYPE_MEANING , AAC.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID , PET.OUTPUT_CURRENCY_CODE OUTPUT_CURRENCY_CODE , HR_GENERAL.DECODE_LOOKUP('UNITS', PIV.UOM) PAY_VALUE_UOM_MEANING , PRR.RUN_RESULT_ID RUN_RESULT_ID , HR_CHKFMT.CHANGEFORMAT (PRRV.RESULT_VALUE, PIV.UOM, PET.OUTPUT_CURRENCY_CODE) PAY_VALUE_RESULT , PAC.DATE_EARNED from pay_element_types_f pet, pay_input_values_f piv, per_all_assignments_f asg, pay_run_result_values prrv, pay_run_results prr, pay_payroll_actions pac, pay_assignment_actions aac where aac.assignment_id = asg.assignment_id /* Only select the following payroll actions: Action Type Meaning ----------- ------- B Balance adjustment Q QuickPay Run V Reversal R Run */ and (pac.action_type = 'B' or pac.action_type = 'Q' or pac.action_type = 'V' or pac.action_type = 'R') /* Only select assignment actions which are completed */ and aac.action_status = 'C' /* For the employee assignment join to the time periods restricting by the payroll action date */ and aac.payroll_action_id = pac.payroll_action_id /* For the assignment action select the run results which will contribute to the balances: Status Meaning ------ ------- PA Processed and Adjusted P Processed */ and prr.assignment_action_id = aac.assignment_action_id and (prr.status = 'PA' or prr.status = 'P') /* For the run result select the 'Pay Value' if it exists */ and prrv.run_result_id = prr.run_result_id and piv.input_value_id = prrv.input_value_id and piv.name = 'Pay Value' and pet.element_type_id (+) = piv.element_type_id /* Date effective joins */ and pac.effective_date between asg.effective_start_date and asg.effective_end_date and pac.effective_date between piv.effective_start_date and piv.effective_end_date and pac.effective_date between pet.effective_start_date and pet.effective_end_date union all select /*+ LEADING (aac)*/ aac.assignment_id assignment_id, pet.element_type_id element_type_id, pac.effective_date effective_date, hr_payrolls.display_period_name(pac.payroll_action_id) period_name, hr_general.decode_lookup('ACTION_TYPE', pac.action_type) action_type_meaning, aac.assignment_action_id assignment_action_id, pet.output_currency_code output_currency_code, NULL, /* no Pay Value = no meaning */ prr.run_result_id run_result_id, NULL, /* no Pay Value = no meaning */ PAC.DATE_EARNED from pay_element_types_f pet, per_all_assignments_f asg, pay_run_results prr, pay_payroll_actions pac, pay_assignment_actions aac where aac.assignment_id = asg.assignment_id and (pac.action_type = 'B' or pac.action_type = 'Q' or pac.action_type = 'V' or pac.action_type = 'R') and aac.action_status = 'C' and aac.payroll_action_id = pac.payroll_action_id and prr.assignment_action_id = aac.assignment_action_id and (prr.status = 'PA' or prr.status = 'P') /* Select elements without a Pay Value */ and not exists (select 'X' from pay_input_values_f piv1, pay_run_result_values prrv1 where prrv1.run_result_id = prr.run_result_id and prrv1.input_value_id = piv1.input_value_id and piv1.name = 'Pay Value') and pet.element_type_id = prr.element_type_id /* Date effective joins */ and pac.effective_date between asg.effective_start_date and asg.effective_end_date and pac.effective_date between pet.effective_start_date and pet.effective_end_date

View Text - HTML Formatted

SELECT /*+ LEADING(AAC)*/ AAC.ASSIGNMENT_ID ASSIGNMENT_ID
, PET.ELEMENT_TYPE_ID ELEMENT_TYPE_ID
, PAC.EFFECTIVE_DATE EFFECTIVE_DATE
, HR_PAYROLLS.DISPLAY_PERIOD_NAME(PAC.PAYROLL_ACTION_ID) PERIOD_NAME
, HR_GENERAL.DECODE_LOOKUP('ACTION_TYPE'
, PAC.ACTION_TYPE) ACTION_TYPE_MEANING
, AAC.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, PET.OUTPUT_CURRENCY_CODE OUTPUT_CURRENCY_CODE
, HR_GENERAL.DECODE_LOOKUP('UNITS'
, PIV.UOM) PAY_VALUE_UOM_MEANING
, PRR.RUN_RESULT_ID RUN_RESULT_ID
, HR_CHKFMT.CHANGEFORMAT (PRRV.RESULT_VALUE
, PIV.UOM
, PET.OUTPUT_CURRENCY_CODE) PAY_VALUE_RESULT
, PAC.DATE_EARNED
FROM PAY_ELEMENT_TYPES_F PET
, PAY_INPUT_VALUES_F PIV
, PER_ALL_ASSIGNMENTS_F ASG
, PAY_RUN_RESULT_VALUES PRRV
, PAY_RUN_RESULTS PRR
, PAY_PAYROLL_ACTIONS PAC
, PAY_ASSIGNMENT_ACTIONS AAC
WHERE AAC.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID /* ONLY SELECT THE FOLLOWING PAYROLL ACTIONS: ACTION TYPE MEANING ----------- ------- B BALANCE ADJUSTMENT Q QUICKPAY RUN V REVERSAL R RUN */
AND (PAC.ACTION_TYPE = 'B' OR PAC.ACTION_TYPE = 'Q' OR PAC.ACTION_TYPE = 'V' OR PAC.ACTION_TYPE = 'R') /* ONLY SELECT ASSIGNMENT ACTIONS WHICH ARE COMPLETED */
AND AAC.ACTION_STATUS = 'C' /* FOR THE EMPLOYEE ASSIGNMENT JOIN TO THE TIME PERIODS RESTRICTING BY THE PAYROLL ACTION DATE */
AND AAC.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID /* FOR THE ASSIGNMENT ACTION SELECT THE RUN RESULTS WHICH WILL CONTRIBUTE TO THE BALANCES: STATUS MEANING ------ ------- PA PROCESSED
AND ADJUSTED P PROCESSED */
AND PRR.ASSIGNMENT_ACTION_ID = AAC.ASSIGNMENT_ACTION_ID
AND (PRR.STATUS = 'PA' OR PRR.STATUS = 'P') /* FOR THE RUN RESULT SELECT THE 'PAY VALUE' IF IT EXISTS */
AND PRRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND PIV.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PIV.NAME = 'PAY VALUE'
AND PET.ELEMENT_TYPE_ID (+) = PIV.ELEMENT_TYPE_ID /* DATE EFFECTIVE JOINS */
AND PAC.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND PAC.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND PAC.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE UNION ALL SELECT /*+ LEADING (AAC)*/ AAC.ASSIGNMENT_ID ASSIGNMENT_ID
, PET.ELEMENT_TYPE_ID ELEMENT_TYPE_ID
, PAC.EFFECTIVE_DATE EFFECTIVE_DATE
, HR_PAYROLLS.DISPLAY_PERIOD_NAME(PAC.PAYROLL_ACTION_ID) PERIOD_NAME
, HR_GENERAL.DECODE_LOOKUP('ACTION_TYPE'
, PAC.ACTION_TYPE) ACTION_TYPE_MEANING
, AAC.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, PET.OUTPUT_CURRENCY_CODE OUTPUT_CURRENCY_CODE
, NULL
, /* NO PAY VALUE = NO MEANING */ PRR.RUN_RESULT_ID RUN_RESULT_ID
, NULL
, /* NO PAY VALUE = NO MEANING */ PAC.DATE_EARNED
FROM PAY_ELEMENT_TYPES_F PET
, PER_ALL_ASSIGNMENTS_F ASG
, PAY_RUN_RESULTS PRR
, PAY_PAYROLL_ACTIONS PAC
, PAY_ASSIGNMENT_ACTIONS AAC
WHERE AAC.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND (PAC.ACTION_TYPE = 'B' OR PAC.ACTION_TYPE = 'Q' OR PAC.ACTION_TYPE = 'V' OR PAC.ACTION_TYPE = 'R')
AND AAC.ACTION_STATUS = 'C'
AND AAC.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID
AND PRR.ASSIGNMENT_ACTION_ID = AAC.ASSIGNMENT_ACTION_ID
AND (PRR.STATUS = 'PA' OR PRR.STATUS = 'P') /* SELECT ELEMENTS WITHOUT A PAY VALUE */
AND NOT EXISTS (SELECT 'X'
FROM PAY_INPUT_VALUES_F PIV1
, PAY_RUN_RESULT_VALUES PRRV1
WHERE PRRV1.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND PRRV1.INPUT_VALUE_ID = PIV1.INPUT_VALUE_ID
AND PIV1.NAME = 'PAY VALUE')
AND PET.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID /* DATE EFFECTIVE JOINS */
AND PAC.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND PAC.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE