View: PA_CI_ACTIONS_V

Product:	PA - Projects
Description:	This view provides the atrribute values for Action Lists
Implementation/DBA Data:	APPS.PA_CI_ACTIONS_V

View Text

SELECT CITYPE.SHORT_NAME||' ('||PCI.CI_NUMBER||')' CONTROL_ITEM
, PAC.CI_ACTION_ID
, PAC.CI_ID
, PAC.CI_ACTION_NUMBER
, PCI.PROJECT_ID
, PPA.NAME||'('||PPA.SEGMENT1||')' PROJECT_NAME
, PCI.SUMMARY SUBJECT
, PCI.CI_NUMBER
, CITYPE.NAME CI_TYPE_NAME
, PCI.STATUS_CODE
, PRIORITY_LKP.MEANING PRIORITY_DESCRIPTION
, PRIORITY_LKP.PREDEFINED_FLAG
, PA_CONTROL_ITEMS_UTILS.GET_OBJECT_NAME(PCI.PROJECT_ID
, PCI.OBJECT_ID
, PCI.OBJECT_TYPE) OBJECT_NAME
, PAC.CREATION_DATE
, DECODE(PAC.STATUS_CODE
, 'CI_ACTION_CLOSED'
, PAC.DATE_CLOSED
, NULL) DATE_CLOSED
, DECODE(PAC.STATUS_CODE
, 'CI_ACTION_CANCELED'
, PAC.DATE_CLOSED
, NULL) DATE_CLOSED
, PAC.DATE_REQUIRED
, LOOK_UP_TYPE.MEANING ACTION_TYPE
, PAC.TYPE_CODE ACTION_TYPE_CODE
, PAC.ASSIGNED_TO ASSIGN_PARTY_ID
, HZ_ASSIGN.PARTY_NAME ASSIGN_NAME
, HZ_CREATE.PARTY_ID CREATE_PARTY_ID
, HZ_CREATE.PARTY_NAME CREATE_NAME
, PCC.COMMENT_TEXT
, PAC.RECORD_VERSION_NUMBER
, PAC.STATUS_CODE
, PPS.MEANING
, PAC.SIGN_OFF_REQUIRED_FLAG
, YES_NO.MEANING
, PAC.SIGN_OFF_FLAG
, YES_NO2.MEANING
, PAC.LAST_UPDATE_DATE
, HZ_ASSIGN2.PARTY_NAME
, PAC2.CI_ACTION_ID
, PAC2.CI_ACTION_NUMBER
, PAC2.ASSIGNED_TO
, TRUNC(PAC.DATE_REQUIRED-SYSDATE)
, TRUNC(SYSDATE-PAC.LAST_UPDATE_DATE)
, PAC.SOURCE_CI_ACTION_ID
, PAC3.CI_ACTION_NUMBER
, CI_LOOK_UP_TYPE.MEANING
, PCC2.COMMENT_TEXT
, CITYPE.CI_TYPE_CLASS_CODE
FROM PA_LOOKUPS PRIORITY_LKP
, PA_LOOKUPS PPS
, PA_LOOKUPS CI_LOOK_UP_TYPE
, PA_LOOKUPS LOOK_UP_TYPE
, HZ_PARTIES HZ_ASSIGN2
, HZ_PARTIES HZ_CREATE
, HZ_PARTIES HZ_ASSIGN
, FND_LOOKUPS YES_NO2
, FND_LOOKUPS YES_NO
, PA_PROJECTS_ALL PPA
, PA_CI_TYPES_VL CITYPE
, PA_CONTROL_ITEMS PCI
, PA_CI_COMMENTS PCC
, PA_CI_COMMENTS PCC2
, PA_CI_ACTIONS PAC3
, PA_CI_ACTIONS PAC2
, PA_CI_ACTIONS PAC
WHERE PAC.CI_ID = PCI.CI_ID
AND PPA.PROJECT_ID = PCI.PROJECT_ID
AND PCI.CI_TYPE_ID = CITYPE.CI_TYPE_ID
AND PAC.ASSIGNED_TO = HZ_ASSIGN.PARTY_ID
AND HZ_ASSIGN.PARTY_TYPE = 'PERSON'
AND PA_UTILS.GET_PARTY_ID(PAC.CREATED_BY) = HZ_CREATE.PARTY_ID
AND HZ_CREATE.PARTY_TYPE = 'PERSON'
AND PAC.CI_ACTION_ID = PCC.CI_ACTION_ID
AND PAC.CI_ID = PCC.CI_ID
AND PCC.TYPE_CODE = 'REQUESTOR'
AND PAC.CI_ID = PCC2.CI_ID (+)
AND PAC.CI_ACTION_ID = PCC2.CI_ACTION_ID(+)
AND PCC2.TYPE_CODE(+) = 'CLOSURE'
AND PPS.LOOKUP_TYPE = 'CI_ACTION_SYSTEM_STATUS'
AND PPS.LOOKUP_CODE = PAC.STATUS_CODE
AND PAC2.SOURCE_CI_ACTION_ID(+) = PAC.CI_ACTION_ID
AND PAC2.CI_ID(+) = PAC.CI_ID
AND PAC.SOURCE_CI_ACTION_ID = PAC3.CI_ACTION_ID(+)
AND PAC.CI_ID = PAC3.CI_ID(+)
AND HZ_ASSIGN2.PARTY_ID(+) = PAC2.ASSIGNED_TO
AND HZ_ASSIGN2.PARTY_TYPE(+) = 'PERSON'
AND LOOK_UP_TYPE.LOOKUP_TYPE = 'PA_CI_ACTION_TYPES'
AND LOOK_UP_TYPE.LOOKUP_CODE = PAC.TYPE_CODE
AND CI_LOOK_UP_TYPE.LOOKUP_TYPE = 'PA_CI_TYPE_CLASSES'
AND CI_LOOK_UP_TYPE.LOOKUP_CODE = CITYPE.CI_TYPE_CLASS_CODE
AND PRIORITY_LKP.LOOKUP_TYPE(+) = 'PA_TASK_PRIORITY_CODE'
AND PRIORITY_LKP.LOOKUP_CODE(+) = PCI.PRIORITY_CODE
AND YES_NO.LOOKUP_TYPE = 'YES_NO'
AND YES_NO.LOOKUP_CODE = PAC.SIGN_OFF_REQUIRED_FLAG
AND YES_NO2.LOOKUP_TYPE = 'YES_NO'
AND YES_NO2.LOOKUP_CODE = PAC.SIGN_OFF_FLAG

Columns