SELECT PAI.ACTION_CONTEXT_ID , PAI.ASSIGNMENT_ID , PAI.EFFECTIVE_DATE , PAI.ACTION_INFORMATION2 INFO_TYPE , /*(GIVES TYPE ..BALANCE/ELEMENT/MESSAGE)*/ PAI.ACTION_INFORMATION4 NAME , /* (BALANCE NAME , ELEMENT NAME , MESSAGE)*/ PAI.ACTION_INFORMATION5 VALUE_TYPE , /*INPUT VALUE FOR ELEMENT , OR DIMENSION FOR BALANCE)*/ /*PAY_US_EMPLOYEE_PAYSLIP_WEB.GET_FORMAT_VALUE (PAF.BUSINESS_GROUP_ID , PAI.ACTION_INFORMATION6) VALUE */ PAI.ACTION_INFORMATION6 VALUE FROM PAY_ACTION_INFORMATION PAI , PER_ASSIGNMENTS_F PAF WHERE PAI.ACTION_INFORMATION_CATEGORY = 'EMPLOYEE OTHER INFORMATION' AND PAI.ACTION_INFORMATION2 = 'BALANCE' AND PAF.ASSIGNMENT_ID = PAI.ASSIGNMENT_ID AND PAI.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE AND PAF.EFFECTIVE_END_DATE UNION ALL SELECT PAI.ACTION_CONTEXT_ID , PAI.ASSIGNMENT_ID , PAI.EFFECTIVE_DATE , PAI.ACTION_INFORMATION2 INFO_TYPE , PAI.ACTION_INFORMATION4 NAME , PAI.ACTION_INFORMATION5 VALUE_TYPE , /*INPUT VALUE FOR ELEMENT*/ DECODE(PIV.UOM , 'M' , PAY_US_EMPLOYEE_PAYSLIP_WEB.GET_FORMAT_VALUE (PAF.BUSINESS_GROUP_ID , PAI.ACTION_INFORMATION6) , 'N' , PAY_US_EMPLOYEE_PAYSLIP_WEB.GET_FORMAT_VALUE (PAF.BUSINESS_GROUP_ID , PAI.ACTION_INFORMATION6) , PAI.ACTION_INFORMATION6) VALUE FROM PAY_ACTION_INFORMATION PAI , PAY_INPUT_VALUES_F PIV , PER_ASSIGNMENTS_F PAF WHERE PAI.ACTION_INFORMATION_CATEGORY = 'EMPLOYEE OTHER INFORMATION' AND PAI.ACTION_INFORMATION2 = 'ELEMENT' AND PAI.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE AND PIV.EFFECTIVE_END_DATE AND PIV.INPUT_VALUE_ID = PAI.ACTION_INFORMATION8 AND PAF.ASSIGNMENT_ID = PAI.ASSIGNMENT_ID AND PAI.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE AND PAF.EFFECTIVE_END_DATE UNION ALL SELECT PAA.ASSIGNMENT_ACTION_ID , PAI.ASSIGNMENT_ID , PAI.EFFECTIVE_DATE , PAI.ACTION_INFORMATION2 INFO_TYPE , /*(GIVES TYPE ..BALANCE/ELEMENT/MESSAGE)*/ PAI.ACTION_INFORMATION4 NAME , /* (BALANCE NAME , ELEMENT NAME , MESSAGE)*/ PAI.ACTION_INFORMATION5 VALUE_TYPE , /*INPUT VALUE FOR ELEMENT , OR DIMENSION FOR BALANCE)*/ PAI.ACTION_INFORMATION6 VALUE FROM PAY_ACTION_INFORMATION PAI , PAY_ASSIGNMENT_ACTIONS PAA , PER_ALL_ASSIGNMENTS_F PAF WHERE PAI.ACTION_INFORMATION_CATEGORY = 'EMPLOYEE OTHER INFORMATION' AND PAI.ACTION_INFORMATION2 = 'MESG' AND PAA.PAYROLL_ACTION_ID = PAI.ACTION_CONTEXT_ID AND PAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID AND PAF.ORGANIZATION_ID = PAI.ACTION_INFORMATION1 AND PAI.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE AND PAF.EFFECTIVE_END_DATE UNION ALL SELECT PAA.ASSIGNMENT_ACTION_ID , PAI.ASSIGNMENT_ID , PAI.EFFECTIVE_DATE , PAI.ACTION_INFORMATION2 INFO_TYPE , /*(GIVES TYPE ..BALANCE/ELEMENT/MESSAGE)*/ PAI.ACTION_INFORMATION4 NAME , /* (BALANCE NAME , ELEMENT NAME , MESSAGE)*/ PAI.ACTION_INFORMATION5 VALUE_TYPE , /*INPUT VALUE FOR ELEMENT , OR DIMENSION FOR BA LANCE)*/ PAI.ACTION_INFORMATION6 VALUE FROM PAY_ACTION_INFORMATION PAI , PAY_ASSIGNMENT_ACTIONS PAA , PAY_PAYROLL_ACTIONS PPA WHERE PAI.ACTION_INFORMATION_CATEGORY = 'EMPLOYEE OTHER INFORMATION' AND PAI.ACTION_INFORMATION2 = 'MESG' AND PAA.PAYROLL_ACTION_ID = PAI.ACTION_CONTEXT_ID AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID AND PPA.BUSINESS_GROUP_ID = PAI.ACTION_INFORMATION1 UNION ALL SELECT PAA.ASSIGNMENT_ACTION_ID ACTION_CONTEXT_ID , PAI.ASSIGNMENT_ID ASSIGNMENT_ID , PAI.EFFECTIVE_DATE EFFECTIVE_DATE , PAI.ACTION_INFORMATION2 INFO_TYPE , PAI.ACTION_INFORMATION4 NAME , PAI.ACTION_INFORMATION5 VALUE_TYPE , PAI.ACTION_INFORMATION6 VALUE FROM PAY_PAYROLL_ACTIONS PPA , PAY_ASSIGNMENT_ACTIONS PAA , PAY_ACTION_INTERLOCKS LCK , PAY_ASSIGNMENT_ACTIONS PAA2 , PAY_PAYROLL_ACTIONS PPA2 , PAY_ACTION_INFORMATION PAI WHERE PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID AND PAA.ASSIGNMENT_ACTION_ID = LCK.LOCKING_ACTION_ID AND LCK.LOCKED_ACTION_ID = PAA2.ASSIGNMENT_ACTION_ID AND PAA2.PAYROLL_ACTION_ID = PPA2.PAYROLL_ACTION_ID AND PPA2.ACTION_TYPE IN ('R' , 'Q') AND PAA.PAYROLL_ACTION_ID = PAI.ACTION_CONTEXT_ID AND PAI.ACTION_INFORMATION_CATEGORY = 'EMPLOYEE OTHER INFORMATION' AND PAI.ACTION_INFORMATION2 = 'MESG' AND PAA2.PAYROLL_ACTION_ID = PAI.ACTION_INFORMATION1