SELECT PACT.BUSINESS_GROUP_ID , PACT.PAYROLL_ID , ASSACT.ASSIGNMENT_ID , ASSACT.ACTION_SEQUENCE MAX_ACTION_SEQUENCE , PACT.EFFECTIVE_DATE MAX_EFFECTIVE_DATE FROM PAY_ASSIGNMENT_ACTIONS ASSACT , PAY_PAYROLL_ACTIONS PACT /* IF ONES DOES NOT HAVE ACTIONS FROM 1999/06/01 TO 1999/12/31 , HIS ACTIONS ARE SELECTED. */ /* IT MEANS IT IS POSSIBLE TO GET HIS ASSIGNMENT INFO. */ /* WHEN THE RESULT OF SELECTION ARE DISPLAYED , THE ACTIONS FROM 1999/01/01 TO 1999/05/31 ARE NOT DISPLAYED. */ WHERE PACT.EFFECTIVE_DATE BETWEEN TO_DATE('1999/01/01' , 'YYYY/MM/DD') AND TO_DATE('1999/12/31' , 'YYYY/MM/DD') /* AND PACT.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID */ /* AND PACT.PAYROLL_ID = P_PAYROLL_ID */ AND PACT.ACTION_TYPE IN ('R' , 'Q' , 'B' , 'I') AND ASSACT.PAYROLL_ACTION_ID = PACT.PAYROLL_ACTION_ID /* TARGET THE PERSON , WHOSE ACTION EXISTS FROM 1999/01/01 TO 1999/03/31 (IGNORE FRESH MAN. */ ) AND EXISTS( SELECT NULL FROM PAY_PAYROLL_ACTIONS PPA , PAY_ASSIGNMENT_ACTIONS PAA WHERE PAA.ASSIGNMENT_ID = ASSACT.ASSIGNMENT_ID AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID AND PPA.EFFECTIVE_DATE BETWEEN TO_DATE('1999/01/01' , 'YYYY/MM/DD') AND TO_DATE('1999/03/31 ' , 'YYYY/MM/DD') AND PPA.ACTION_TYPE IN ('R' , 'Q' , 'B' , 'I') AND NOT EXISTS( SELECT NULL FROM PAY_PAYROLL_ACTIONS PPA2 , PAY_ASSIGNMENT_ACTIONS PAA2 , PAY_ACTION_INTERLOCKS PAI2 WHERE PAI2.LOCKED_ACTION_ID=PAA.ASSIGNMENT_ACTION_ID AND PAA2.ASSIGNMENT_ACTION_ID=PAI2.LOCKING_ACTION_ID AND PPA2.PAYROLL_ACTION_ID=PAA2.PAYROLL_ACTION_ID AND PPA2.ACTION_TYPE = 'V')) /* TARGET LAST ACTION */ AND NOT EXISTS( SELECT NULL FROM PAY_PAYROLL_ACTIONS PPA , PAY_ASSIGNMENT_ACTIONS PAA WHERE PAA.ASSIGNMENT_ID = ASSACT.ASSIGNMENT_ID AND PAA.ACTION_SEQUENCE > ASSACT.ACTION_SEQUENCE AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID AND PPA.EFFECTIVE_DATE BETWEEN TO_DATE('1999/01/01' , 'YYYY/MM/DD') AND TO_DATE('1999/12/31 ' , 'YYYY/MM/DD') AND PPA.ACTION_TYPE IN ('R' , 'Q' , 'B' , 'I') AND NOT EXISTS( SELECT NULL FROM PAY_PAYROLL_ACTIONS PPA2 , PAY_ASSIGNMENT_ACTIONS PAA2 , PAY_ACTION_INTERLOCKS PAI2 WHERE PAI2.LOCKED_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID AND PAA2.ASSIGNMENT_ACTION_ID = PAI2.LOCKING_ACTION_ID AND PPA2.PAYROLL_ACTION_ID = PAA2.PAYROLL_ACTION_ID AND PPA2.ACTION_TYPE = 'V')) /* TARGET NOT LOCKED. (IGNORE REVERSED ACTION) */ AND NOT EXISTS( SELECT NULL FROM PAY_PAYROLL_ACTIONS PPA , PAY_ASSIGNMENT_ACTIONS PAA , PAY_ACTION_INTERLOCKS PAI WHERE PAI.LOCKED_ACTION_ID = ASSACT.ASSIGNMENT_ACTION_ID AND PAA.ASSIGNMENT_ACTION_ID = PAI.LOCKING_ACTION_ID AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID AND PPA.ACTION_TYPE = 'V')
[Home] [Help]