VIEW: APPS.PA_ADVERTISED_OPEN_REQ_V

Source

View Text - Preformatted

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 )

View Text - HTML Formatted

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 )