View: PA_ADVERTISED_OPEN_REQ_V

Product:	PA - Projects
Description:
Implementation/DBA Data:	APPS.PA_ADVERTISED_OPEN_REQ_V

View Text

SELECT ASETS.OBJECT_ID
FROM PA_ACTION_SETS ASETS
WHERE ASETS.OBJECT_TYPE='OPEN_ASSIGNMENT'
AND ASETS.ACTION_SET_TYPE_CODE = 'ADVERTISEMENT'
AND ASETS.STATUS_CODE IN ('RESUMED'
, 'PAUSED'
, 'STARTED'
, 'NOT_STARTED')
AND EXISTS ( SELECT ASLA.OBJECT_ID
FROM PA_ACTION_SET_LINE_AUD ASLA
, PER_ALL_ASSIGNMENTS_F AAF
, FND_USER FU
WHERE ASLA.OBJECT_ID = ASETS.OBJECT_ID
AND ASLA.OBJECT_TYPE = 'OPEN_ASSIGNMENT'
AND ASLA.ACTION_SET_TYPE_CODE = 'ADVERTISEMENT'
AND ASLA.ACTIVE_FLAG = 'Y'
AND (ASLA.ACTION_CODE = 'ADVERTISEMENT_PUB_TO_ALL' OR (ASLA.ACTION_CODE = 'ADVERTISEMENT_PUB_TO_SM'
AND ASLA.AUDIT_ATTRIBUTE = FU.EMPLOYEE_ID) OR (ASLA.ACTION_CODE = 'ADVERTISEMENT_PUB_TO_START_ORG'
AND ASLA.AUDIT_ATTRIBUTE = AAF.ORGANIZATION_ID) OR (ASLA.ACTION_CODE = 'ADVERTISEMENT_ESC_TO_NEXT_LVL'
AND ASLA.AUDIT_ATTRIBUTE = AAF.ORGANIZATION_ID))
AND AAF.PERSON_ID = FU.EMPLOYEE_ID
AND AAF.PRIMARY_FLAG = 'Y'
AND AAF.ASSIGNMENT_TYPE IN ('C'
, 'E')
AND TRUNC(SYSDATE) BETWEEN AAF.EFFECTIVE_START_DATE
AND AAF.EFFECTIVE_END_DATE
AND FU.USER_ID = FND_GLOBAL.USER_ID )

Columns