PAY_PROCESS_RESULTS

Details

Schema: FUSION
Object owner: PAY
Object type: VIEW

Columns

Name
OBJECT_TYPE OBJECT_ID OBJECT_ACTION_ID ACTION_CODE STATUS ACTION_STATUS_CODE ACTION_TYPE ACTION_TYPE_CODE PAYROLL_ACTION_ID PROCESS_DATE LOCKINGFLAG LOCKINGSTATUS TASK_NAME OBJECT_NAME OBJECT_REFERENCE PERSON_NUMBER RELATIONSHIP_NUMBER PAYROLL PAYROLL_PERIOD_NAME PAYROLL_PERIOD_NUMBER DATE_EARNED ASSIGNMENT_NUMBER RELATIONSHIP_TYPE KEYWORDS LEGISLATIVE_DATA_GROUP_ID LEGISLATIVE_DATA_GROUP_NAME ASSIGNMENT_STATUS

Name

OBJECT_TYPE

OBJECT_ID

OBJECT_ACTION_ID

ACTION_CODE

STATUS

ACTION_STATUS_CODE

ACTION_TYPE

ACTION_TYPE_CODE

PAYROLL_ACTION_ID

PROCESS_DATE

LOCKINGFLAG

LOCKINGSTATUS

TASK_NAME

OBJECT_NAME

OBJECT_REFERENCE

PERSON_NUMBER

RELATIONSHIP_NUMBER

PAYROLL

PAYROLL_PERIOD_NAME

PAYROLL_PERIOD_NUMBER

DATE_EARNED

ASSIGNMENT_NUMBER

RELATIONSHIP_TYPE

KEYWORDS

LEGISLATIVE_DATA_GROUP_ID

LEGISLATIVE_DATA_GROUP_NAME

ASSIGNMENT_STATUS

Query

SQL_Statement
SELECT OBJECT_TYPE, OBJECT_ID , OBJECT_ACTION_ID , ACTION_CODE, STATUS , ACTION_STATUS_CODE , ACTION_TYPE , ACTION_TYPE_CODE , PAYROLL_ACTION_ID , PROCESS_DATE, LOCKINGFLAG , LOCKINGSTATUS , TASK_NAME, object_name, object_reference, person_number, relationship_number, payroll, payroll_period_name, payroll_period_number, date_earned, assignment_number, Relationship_type, keywords, legislative_data_group_id, legislative_data_group_name, assignment_status FROM ( SELECT pay_obj_act.OBJECT_TYPE, pay_obj_act.OBJECT_ID, pay_obj_act.TEMP_OBJECT_ACTION_ID as OBJECT_ACTION_ID , 'PTOA' AS ACTION_CODE, actionstatuslookup.meaning status, actionstatuslookup.lookup_code action_status_code, actiontypelookup.meaning action_type, actiontypelookup.lookup_code action_type_code, pay_obj_act.PAYROLL_ACTION_ID AS payroll_action_id, payrollactioneo.effective_date AS process_date, NVL( (SELECT MAX(l.locking_action_id) AS lockingId FROM pay_action_interlocks l WHERE l.locked_action_id=pay_obj_act.TEMP_OBJECT_ACTION_ID ),-1) AS LockingFlag, (SELECT NVL(rel.action_status,'N/A') FROM pay_payroll_rel_actions rel WHERE rel.payroll_rel_action_id = NVL( (SELECT MAX(l.locking_action_id) AS lockingId FROM pay_action_interlocks l WHERE l.locked_action_id=pay_obj_act.TEMP_OBJECT_ACTION_ID ),-1) ) AS LockingStatus , NVL(pt.task_name,actiontypelookup.meaning) AS task_name, null object_name, null object_reference, null person_number, null relationship_number, null payroll, null payroll_period_name, null payroll_period_number, null date_earned, null assignment_number, null Relationship_type, null keywords, null legislative_data_group_id, null legislative_data_group_name, null assignment_status FROM hcm_lookups actiontypelookup, hcm_lookups actionstatuslookup, pay_temp_object_actions pay_obj_act, pay_payroll_actions payrollactioneo, pay_requests prq, pay_task_actions pta, pay_tasks_vl pt, per_legislative_data_groups ldg WHERE actiontypelookup.lookup_type = 'ACTION_TYPE' and 1=2 AND actiontypelookup.lookup_code ='X' AND actionstatuslookup.lookup_type = 'PAY_ACTION_STATUS' AND actionstatuslookup.lookup_code = pay_obj_act.ACTION_STATUS AND payrollactioneo.payroll_action_id =pay_obj_act.payroll_action_id AND prq.pay_request_id (+) = payrollactioneo.pay_request_id AND prq.pay_task_action_id = pta.task_action_id (+) AND pta.base_task_id = pt.task_id (+) AND payrollactioneo.legislative_data_group_id = ldg.legislative_data_group_id (+) AND ((pt.legislative_data_group_id IS NOT NULL AND pt.legislation_code IS NULL AND pt.legislative_data_group_id = ldg.legislative_data_group_id) OR (pt.legislation_code IS NOT NULL AND pt.legislative_data_group_id IS NULL AND pt.legislation_code =ldg.legislation_code AND ( NOT EXISTS (SELECT TASK_ID FROM PAY_TASK_ACTIONS_VL c1 WHERE pt.BASE_TASK_ID = c1.BASE_TASK_ID AND (c1.legislative_data_group_id IS NOT NULL AND c1.legislation_code IS NULL AND c1.legislative_data_group_id = ldg.legislative_data_group_id ) ))) OR ( pt.legislative_data_group_id IS NULL AND pt.legislation_code IS NULL AND ( NOT EXISTS (SELECT TASK_ID FROM PAY_TASK_ACTIONS_VL c2 WHERE pt.BASE_TASK_ID = c2.BASE_TASK_ID AND (( c2.legislative_data_group_id IS NOT NULL AND c2.legislation_code IS NULL AND c2.legislative_data_group_id = ldg.legislative_data_group_id) OR ( c2.legislation_code IS NOT NULL AND c2.legislative_data_group_id IS NULL AND c2.legislation_code = ldg.legislation_code )) )))) union all SELECT pay_obj_act.OBJECT_TYPE, null as OBJECT_ID, pay_obj_act.OBJECT_ACTION_ID, 'POA' AS ACTION_CODE, actionstatuslookup.meaning status, actionstatuslookup.lookup_code action_status_code, actiontypelookup.meaning action_type, actiontypelookup.lookup_code action_type_code, pay_obj_act.PAYROLL_ACTION_ID AS payroll_action_id, payrollactioneo.effective_date AS process_date, NVL( (SELECT MAX(l.locking_action_id) AS lockingId FROM pay_action_interlocks l WHERE l.locked_action_id=pay_obj_act.OBJECT_ACTION_ID ),-1) AS LockingFlag, (SELECT NVL(rel.action_status,'N/A') FROM pay_payroll_rel_actions rel WHERE rel.payroll_rel_action_id = NVL( (SELECT MAX(l.locking_action_id) AS lockingId FROM pay_action_interlocks l WHERE l.locked_action_id=pay_obj_act.OBJECT_ACTION_ID ),-1) ) AS LockingStatus , NVL(pt.task_name,actiontypelookup.meaning) AS task_name, null object_name, null object_reference, null person_number, null relationship_number, null payroll, null payroll_period_name, null payroll_period_number, null date_earned, null assignment_number, null Relationship_type, null keywords, null legislative_data_group_id, null legislative_data_group_name, null assignment_status FROM hcm_lookups actiontypelookup, hcm_lookups actionstatuslookup, pay_object_actions pay_obj_act, pay_payroll_actions payrollactioneo, pay_requests prq, pay_task_actions pta, pay_tasks_vl pt, per_legislative_data_groups ldg WHERE actiontypelookup.lookup_type = 'ACTION_TYPE' AND actiontypelookup.lookup_code ='X' and 1=2 AND actionstatuslookup.lookup_type = 'PAY_ACTION_STATUS' AND actionstatuslookup.lookup_code = pay_obj_act.ACTION_STATUS AND payrollactioneo.payroll_action_id =pay_obj_act.payroll_action_id AND prq.pay_request_id (+) = payrollactioneo.pay_request_id AND prq.pay_task_action_id = pta.task_action_id (+) AND pta.base_task_id = pt.task_id (+) AND payrollactioneo.legislative_data_group_id = ldg.legislative_data_group_id (+) AND ((pt.legislative_data_group_id IS NOT NULL AND pt.legislation_code IS NULL AND pt.legislative_data_group_id = ldg.legislative_data_group_id) OR (pt.legislation_code IS NOT NULL AND pt.legislative_data_group_id IS NULL AND pt.legislation_code =ldg.legislation_code AND ( NOT EXISTS (SELECT TASK_ID FROM PAY_TASK_ACTIONS_VL c1 WHERE pt.BASE_TASK_ID = c1.BASE_TASK_ID AND (c1.legislative_data_group_id IS NOT NULL AND c1.legislation_code IS NULL AND c1.legislative_data_group_id = ldg.legislative_data_group_id ) ))) OR ( pt.legislative_data_group_id IS NULL AND pt.legislation_code IS NULL AND ( NOT EXISTS (SELECT TASK_ID FROM PAY_TASK_ACTIONS_VL c2 WHERE pt.BASE_TASK_ID = c2.BASE_TASK_ID AND (( c2.legislative_data_group_id IS NOT NULL AND c2.legislation_code IS NULL AND c2.legislative_data_group_id = ldg.legislative_data_group_id) OR ( c2.legislation_code IS NOT NULL AND c2.legislative_data_group_id IS NULL AND c2.legislation_code = ldg.legislation_code )) )))) union all SELECT null OBJECT_TYPE, null as OBJECT_ID, null OBJECT_ACTION_ID, 'PREL' AS ACTION_CODE, actionstatuslookup.meaning status, actionstatuslookup.lookup_code action_status_code, actiontypelookup.meaning action_type, actiontypelookup.lookup_code action_type_code, pay_obj_act.PAYROLL_ACTION_ID AS payroll_action_id, payrollactioneo.effective_date AS process_date, NVL( (SELECT MAX(l.locking_action_id) AS lockingId FROM pay_action_interlocks l WHERE l.locked_action_id=pay_obj_act.payroll_rel_ACTION_ID ),-1) AS LockingFlag, (SELECT NVL(rel.action_status,'N/A') FROM pay_payroll_rel_actions rel WHERE rel.payroll_rel_action_id = NVL( (SELECT MAX(l.locking_action_id) AS lockingId FROM pay_action_interlocks l WHERE l.locked_action_id=pay_obj_act.payroll_rel_ACTION_ID ),-1) ) AS LockingStatus , NVL(pt.task_name,actiontypelookup.meaning) AS task_name, null object_name, null object_reference, peo.person_number person_number, prl.payroll_relationship_number relationship_number, ppp.payroll_name payroll, null payroll_period_name, null payroll_period_number, null date_earned, null assignment_number, null Relationship_type, null keywords, null legislative_data_group_id, null legislative_data_group_name, null assignment_status FROM hcm_lookups actiontypelookup, hcm_lookups actionstatuslookup, pay_payroll_rel_actions pay_obj_act, pay_payroll_actions payrollactioneo, pay_requests prq, pay_task_actions pta, pay_tasks_vl pt, per_legislative_data_groups ldg, pay_pay_relationships_dn prl, per_all_people_f peo, pay_all_payrolls_f ppp WHERE actiontypelookup.lookup_type = 'ACTION_TYPE' AND actiontypelookup.lookup_code = payrollactioneo.action_type AND actionstatuslookup.lookup_type = 'PAY_ACTION_STATUS' AND actionstatuslookup.lookup_code = pay_obj_act.ACTION_STATUS AND payrollactioneo.payroll_action_id =pay_obj_act.payroll_action_id AND prq.pay_request_id (+) = payrollactioneo.pay_request_id AND prq.pay_task_action_id = pta.task_action_id (+) AND pta.base_task_id = pt.task_id (+) AND payrollactioneo.legislative_data_group_id = ldg.legislative_data_group_id (+) AND ((pt.legislative_data_group_id IS NOT NULL AND pt.legislation_code IS NULL AND pt.legislative_data_group_id = ldg.legislative_data_group_id) OR (pt.legislation_code IS NOT NULL AND pt.legislative_data_group_id IS NULL AND pt.legislation_code =ldg.legislation_code AND ( NOT EXISTS (SELECT TASK_ID FROM PAY_TASK_ACTIONS_VL c1 WHERE pt.BASE_TASK_ID = c1.BASE_TASK_ID AND (c1.legislative_data_group_id IS NOT NULL AND c1.legislation_code IS NULL AND c1.legislative_data_group_id = ldg.legislative_data_group_id ) ))) OR ( pt.legislative_data_group_id IS NULL AND pt.legislation_code IS NULL AND ( NOT EXISTS (SELECT TASK_ID FROM PAY_TASK_ACTIONS_VL c2 WHERE pt.BASE_TASK_ID = c2.BASE_TASK_ID AND (( c2.legislative_data_group_id IS NOT NULL AND c2.legislation_code IS NULL AND c2.legislative_data_group_id = ldg.legislative_data_group_id) OR ( c2.legislation_code IS NOT NULL AND c2.legislative_data_group_id IS NULL AND c2.legislation_code = ldg.legislation_code )) )))) and prl.payroll_relationship_id = pay_obj_act.payroll_relationship_id and prl.person_id=peo.person_id and payrollactioneo.effective_date between peo.effective_start_Date and peo.effective_end_date and payrollactioneo.payroll_id=ppp.payroll_id (+) and payrollactioneo.effective_date between ppp.effective_start_Date (+) and ppp.effective_end_date (+) ) order by OBJECT_ACTION_ID desc

SQL_Statement

SELECT OBJECT_TYPE,

OBJECT_ID ,

OBJECT_ACTION_ID ,

ACTION_CODE,

STATUS ,

ACTION_STATUS_CODE ,

ACTION_TYPE ,

ACTION_TYPE_CODE ,

PAYROLL_ACTION_ID ,

PROCESS_DATE,

LOCKINGFLAG ,

LOCKINGSTATUS ,

TASK_NAME,

object_name,

object_reference,

person_number,

relationship_number,

payroll,

payroll_period_name,

payroll_period_number,

date_earned,

assignment_number,

Relationship_type,

keywords,

legislative_data_group_id,

legislative_data_group_name,

assignment_status

FROM

(

SELECT pay_obj_act.OBJECT_TYPE,

pay_obj_act.OBJECT_ID,

pay_obj_act.TEMP_OBJECT_ACTION_ID as OBJECT_ACTION_ID ,

'PTOA' AS ACTION_CODE,

actionstatuslookup.meaning status,

actionstatuslookup.lookup_code action_status_code,

actiontypelookup.meaning action_type,

actiontypelookup.lookup_code action_type_code,

pay_obj_act.PAYROLL_ACTION_ID AS payroll_action_id,

payrollactioneo.effective_date AS process_date,

NVL(

(SELECT MAX(l.locking_action_id) AS lockingId

FROM pay_action_interlocks l

WHERE l.locked_action_id=pay_obj_act.TEMP_OBJECT_ACTION_ID

),-1) AS LockingFlag,

(SELECT NVL(rel.action_status,'N/A')

FROM pay_payroll_rel_actions rel

WHERE rel.payroll_rel_action_id = NVL(

(SELECT MAX(l.locking_action_id) AS lockingId

FROM pay_action_interlocks l

WHERE l.locked_action_id=pay_obj_act.TEMP_OBJECT_ACTION_ID

),-1)

) AS LockingStatus ,

NVL(pt.task_name,actiontypelookup.meaning) AS task_name,

null object_name,

null object_reference,

null person_number,

null relationship_number,

null payroll,

null payroll_period_name,

null payroll_period_number,

null date_earned,

null assignment_number,

null Relationship_type,

null keywords,

null legislative_data_group_id,

null legislative_data_group_name,

null assignment_status

FROM hcm_lookups actiontypelookup,

hcm_lookups actionstatuslookup,

pay_temp_object_actions pay_obj_act,

pay_payroll_actions payrollactioneo,

pay_requests prq,

pay_task_actions pta,

pay_tasks_vl pt,

per_legislative_data_groups ldg

WHERE actiontypelookup.lookup_type = 'ACTION_TYPE'

and 1=2

AND actiontypelookup.lookup_code ='X'

AND actionstatuslookup.lookup_type = 'PAY_ACTION_STATUS'

AND actionstatuslookup.lookup_code = pay_obj_act.ACTION_STATUS

AND payrollactioneo.payroll_action_id =pay_obj_act.payroll_action_id

AND prq.pay_request_id (+) = payrollactioneo.pay_request_id

AND prq.pay_task_action_id = pta.task_action_id (+)

AND pta.base_task_id = pt.task_id (+)

AND payrollactioneo.legislative_data_group_id = ldg.legislative_data_group_id (+)

AND ((pt.legislative_data_group_id IS NOT NULL

AND pt.legislation_code IS NULL

AND pt.legislative_data_group_id = ldg.legislative_data_group_id)

OR (pt.legislation_code IS NOT NULL

AND pt.legislative_data_group_id IS NULL

AND pt.legislation_code =ldg.legislation_code

AND ( NOT EXISTS

(SELECT TASK_ID

FROM PAY_TASK_ACTIONS_VL c1

WHERE pt.BASE_TASK_ID = c1.BASE_TASK_ID

AND (c1.legislative_data_group_id IS NOT NULL

AND c1.legislation_code IS NULL

AND c1.legislative_data_group_id = ldg.legislative_data_group_id )

)))

OR ( pt.legislative_data_group_id IS NULL

AND pt.legislation_code IS NULL

AND ( NOT EXISTS

(SELECT TASK_ID

FROM PAY_TASK_ACTIONS_VL c2

WHERE pt.BASE_TASK_ID = c2.BASE_TASK_ID

AND (( c2.legislative_data_group_id IS NOT NULL

AND c2.legislation_code IS NULL

AND c2.legislative_data_group_id = ldg.legislative_data_group_id)

OR ( c2.legislation_code IS NOT NULL

AND c2.legislative_data_group_id IS NULL

AND c2.legislation_code = ldg.legislation_code ))

))))

union all

SELECT pay_obj_act.OBJECT_TYPE,

null as OBJECT_ID,

pay_obj_act.OBJECT_ACTION_ID,

'POA' AS ACTION_CODE,

actionstatuslookup.meaning status,

actionstatuslookup.lookup_code action_status_code,

actiontypelookup.meaning action_type,

actiontypelookup.lookup_code action_type_code,

pay_obj_act.PAYROLL_ACTION_ID AS payroll_action_id,

payrollactioneo.effective_date AS process_date,

NVL(

(SELECT MAX(l.locking_action_id) AS lockingId

FROM pay_action_interlocks l

WHERE l.locked_action_id=pay_obj_act.OBJECT_ACTION_ID

),-1) AS LockingFlag,

(SELECT NVL(rel.action_status,'N/A')

FROM pay_payroll_rel_actions rel

WHERE rel.payroll_rel_action_id = NVL(

(SELECT MAX(l.locking_action_id) AS lockingId

FROM pay_action_interlocks l

WHERE l.locked_action_id=pay_obj_act.OBJECT_ACTION_ID

),-1)

) AS LockingStatus ,

NVL(pt.task_name,actiontypelookup.meaning) AS task_name,

null object_name,

null object_reference,

null person_number,

null relationship_number,

null payroll,

null payroll_period_name,

null payroll_period_number,

null date_earned,

null assignment_number,

null Relationship_type,

null keywords,

null legislative_data_group_id,

null legislative_data_group_name,

null assignment_status

FROM hcm_lookups actiontypelookup,

hcm_lookups actionstatuslookup,

pay_object_actions pay_obj_act,

pay_payroll_actions payrollactioneo,

pay_requests prq,

pay_task_actions pta,

pay_tasks_vl pt,

per_legislative_data_groups ldg

WHERE actiontypelookup.lookup_type = 'ACTION_TYPE'

AND actiontypelookup.lookup_code ='X'

and 1=2

AND actionstatuslookup.lookup_type = 'PAY_ACTION_STATUS'

AND actionstatuslookup.lookup_code = pay_obj_act.ACTION_STATUS

AND payrollactioneo.payroll_action_id =pay_obj_act.payroll_action_id

AND prq.pay_request_id (+) = payrollactioneo.pay_request_id

AND prq.pay_task_action_id = pta.task_action_id (+)

AND pta.base_task_id = pt.task_id (+)

AND payrollactioneo.legislative_data_group_id = ldg.legislative_data_group_id (+)

AND ((pt.legislative_data_group_id IS NOT NULL

AND pt.legislation_code IS NULL

AND pt.legislative_data_group_id = ldg.legislative_data_group_id)

OR (pt.legislation_code IS NOT NULL

AND pt.legislative_data_group_id IS NULL

AND pt.legislation_code =ldg.legislation_code

AND ( NOT EXISTS

(SELECT TASK_ID

FROM PAY_TASK_ACTIONS_VL c1

WHERE pt.BASE_TASK_ID = c1.BASE_TASK_ID

AND (c1.legislative_data_group_id IS NOT NULL

AND c1.legislation_code IS NULL

AND c1.legislative_data_group_id = ldg.legislative_data_group_id )

)))

OR ( pt.legislative_data_group_id IS NULL

AND pt.legislation_code IS NULL

AND ( NOT EXISTS

(SELECT TASK_ID

FROM PAY_TASK_ACTIONS_VL c2

WHERE pt.BASE_TASK_ID = c2.BASE_TASK_ID

AND (( c2.legislative_data_group_id IS NOT NULL

AND c2.legislation_code IS NULL

AND c2.legislative_data_group_id = ldg.legislative_data_group_id)

OR ( c2.legislation_code IS NOT NULL

AND c2.legislative_data_group_id IS NULL

AND c2.legislation_code = ldg.legislation_code ))

))))

union all

SELECT null OBJECT_TYPE,

null as OBJECT_ID,

null OBJECT_ACTION_ID,

'PREL' AS ACTION_CODE,

actionstatuslookup.meaning status,

actionstatuslookup.lookup_code action_status_code,

actiontypelookup.meaning action_type,

actiontypelookup.lookup_code action_type_code,

pay_obj_act.PAYROLL_ACTION_ID AS payroll_action_id,

payrollactioneo.effective_date AS process_date,

NVL(

(SELECT MAX(l.locking_action_id) AS lockingId

FROM pay_action_interlocks l

WHERE l.locked_action_id=pay_obj_act.payroll_rel_ACTION_ID

),-1) AS LockingFlag,

(SELECT NVL(rel.action_status,'N/A')

FROM pay_payroll_rel_actions rel

WHERE rel.payroll_rel_action_id = NVL(

(SELECT MAX(l.locking_action_id) AS lockingId

FROM pay_action_interlocks l

WHERE l.locked_action_id=pay_obj_act.payroll_rel_ACTION_ID

),-1)

) AS LockingStatus ,

NVL(pt.task_name,actiontypelookup.meaning) AS task_name,

null object_name,

null object_reference,

peo.person_number person_number,

prl.payroll_relationship_number relationship_number,

ppp.payroll_name payroll,

null payroll_period_name,

null payroll_period_number,

null date_earned,

null assignment_number,

null Relationship_type,

null keywords,

null legislative_data_group_id,

null legislative_data_group_name,

null assignment_status

FROM hcm_lookups actiontypelookup,

hcm_lookups actionstatuslookup,

pay_payroll_rel_actions pay_obj_act,

pay_payroll_actions payrollactioneo,

pay_requests prq,

pay_task_actions pta,

pay_tasks_vl pt,

per_legislative_data_groups ldg,

pay_pay_relationships_dn prl,

per_all_people_f peo,

pay_all_payrolls_f ppp

WHERE actiontypelookup.lookup_type = 'ACTION_TYPE'

AND actiontypelookup.lookup_code = payrollactioneo.action_type

AND actionstatuslookup.lookup_type = 'PAY_ACTION_STATUS'

AND actionstatuslookup.lookup_code = pay_obj_act.ACTION_STATUS

AND payrollactioneo.payroll_action_id =pay_obj_act.payroll_action_id

AND prq.pay_request_id (+) = payrollactioneo.pay_request_id

AND prq.pay_task_action_id = pta.task_action_id (+)

AND pta.base_task_id = pt.task_id (+)

AND payrollactioneo.legislative_data_group_id = ldg.legislative_data_group_id (+)

AND ((pt.legislative_data_group_id IS NOT NULL

AND pt.legislation_code IS NULL

AND pt.legislative_data_group_id = ldg.legislative_data_group_id)

OR (pt.legislation_code IS NOT NULL

AND pt.legislative_data_group_id IS NULL

AND pt.legislation_code =ldg.legislation_code

AND ( NOT EXISTS

(SELECT TASK_ID

FROM PAY_TASK_ACTIONS_VL c1

WHERE pt.BASE_TASK_ID = c1.BASE_TASK_ID

AND (c1.legislative_data_group_id IS NOT NULL

AND c1.legislation_code IS NULL

AND c1.legislative_data_group_id = ldg.legislative_data_group_id )

)))

OR ( pt.legislative_data_group_id IS NULL

AND pt.legislation_code IS NULL

AND ( NOT EXISTS

(SELECT TASK_ID

FROM PAY_TASK_ACTIONS_VL c2

WHERE pt.BASE_TASK_ID = c2.BASE_TASK_ID

AND (( c2.legislative_data_group_id IS NOT NULL

AND c2.legislation_code IS NULL

AND c2.legislative_data_group_id = ldg.legislative_data_group_id)

OR ( c2.legislation_code IS NOT NULL

AND c2.legislative_data_group_id IS NULL

AND c2.legislation_code = ldg.legislation_code ))

))))

and prl.payroll_relationship_id = pay_obj_act.payroll_relationship_id

and prl.person_id=peo.person_id

and payrollactioneo.effective_date between peo.effective_start_Date and peo.effective_end_date

and payrollactioneo.payroll_id=ppp.payroll_id (+)

and payrollactioneo.effective_date between ppp.effective_start_Date (+) and ppp.effective_end_date (+)

)

order by OBJECT_ACTION_ID desc