VIEW: APPS.PA_CI_ACTIONS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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