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