eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PA_CI_BASIC_CTL_V

Source

View Text - Preformatted


SELECT ci.project_id ,ppa.segment1 ,ppa.name || '(' || ppa.segment1 || ')' ,ppa.project_status_code ,ppa.start_date ,ci.ci_id ,ci.ci_number ,ci.ci_type_id ,ci.summary ,ci.description ,ci.status_code ,status.project_status_name ,status.project_system_status_code ,ci.owner_id ,owner.party_name ,ci.object_type ,ci.object_id ,DECODE(ci.object_id, NULL, NULL, PA_CONTROL_ITEMS_UTILS.get_object_name(ci.project_id,ci.object_id, ci.object_type)) ,ci.date_required ,ci.resolution_code_id ,resolution.class_code ,ci.resolution ,ci.status_overview ,ci.classification_code_id ,classification.class_code ,ci.reason_code_id ,reason.class_code ,ci.priority_code ,priority_lkp.meaning ,ci.price ,ci.price_currency_code ,currency.description ,ci.source_type_code ,source_lkp.meaning ,ci.source_comment ,ci.source_number ,ci.source_date_received ,ci.source_organization ,ci.source_person ,ci.creation_date ,ci.last_update_date ,ci.created_by ,PA_CONTROL_ITEMS_UTILS.GetUserName(ci.created_by) ,ci.last_updated_by ,PA_CONTROL_ITEMS_UTILS.GetUserName(ci.last_updated_by) ,citype.ci_type_class_code ,citype.name ,citype.short_name ,citype.approval_required_flag ,citype.auto_number_flag ,citype.resolution_required_flag ,citype.source_attrs_enabled_flag ,ci.highlighted_flag ,ci.date_closed ,ci.closed_by_id ,closed_by.party_name ,ci.record_version_number ,ci.open_action_num ,ci.effort_level_code ,effort_lkp.meaning ,ci.last_modification_date ,ci.last_modified_by_id ,modified_by.party_name ,pps.project_status_code ,pps.project_status_name ,pps.status_icon_ind ,pps.status_icon_active_ind ,ci.progress_as_of_date ,PA_CONTROL_ITEMS_UTILS.closeAllowed(ci.ci_id,ci.owner_id,ci.created_by,status.project_system_status_code) ,PA_CONTROL_ITEMS_UTILS.deleteAllowed(ci.ci_id,ci.owner_id,ci.created_by,status.project_system_status_code) ,PA_CONTROL_ITEMS_UTILS.submitAllowed(ci.ci_id,ci.owner_id,ci.created_by,status.project_system_status_code) ,ci.attribute_category ,ci.attribute1 ,ci.attribute2 ,ci.attribute3 ,ci.attribute4 ,ci.attribute5 ,ci.attribute6 ,ci.attribute7 ,ci.attribute8 ,ci.attribute9 ,ci.attribute10 ,ci.attribute11 ,ci.attribute12 ,ci.attribute13 ,ci.attribute14 ,ci.attribute15 ,ci.rowid , null pco_number ,ci.version_comments ,ci.PCO_STATUS_CODE ,pco_lkp.meaning PCO_STATUS ,citype.APPROVAL_TYPE_CODE ,approval_lkp.meaning APPROVAL_TYPE ,NVL(ci.LOCKED_FLAG, 'N') LOCKED_FLAG ,locked_lkp.meaning CHANGE_DOC_STATUS , citype.impact_budget_type_code ,ci.change_approver ,ci.original_ci_id ,ci.version_number FROM pa_control_items ci ,pa_ci_types_vl citype ,pa_projects_all ppa ,pa_project_statuses status ,hz_parties owner ,hz_parties closed_by ,hz_parties modified_by ,pa_lookups priority_lkp ,pa_lookups source_lkp ,pa_lookups effort_lkp ,fnd_currencies_tl currency ,PA_CLASS_CODES resolution ,PA_CLASS_CODES reason ,PA_CLASS_CODES classification ,pa_project_statuses pps ,pa_lookups pco_lkp ,pa_lookups approval_lkp ,pa_lookups locked_lkp WHERE ci.ci_type_id = citype.ci_type_id AND ppa.project_Id = ci.project_id AND status.project_status_code(+) = ci.status_code AND owner.party_id(+) = ci.owner_id AND closed_by.party_id(+) = ci.closed_by_id AND modified_by.party_id(+) = ci.last_modified_by_id AND priority_lkp.lookup_type(+) = 'PA_TASK_PRIORITY_CODE' AND priority_lkp.lookup_code(+) = ci.priority_code AND currency.currency_code(+) = ci.price_currency_code AND currency.language(+) = USERENV('LANG') AND source_lkp.lookup_type(+) = 'PA_CI_SOURCE_TYPES' AND source_lkp.lookup_code(+) = ci.source_type_code AND effort_lkp.lookup_type(+) = 'PA_CI_EFFORT_LEVELS' AND effort_lkp.lookup_code(+) = ci.effort_level_code AND pps.project_status_code(+) = ci.progress_status_code AND resolution.class_code_id(+) = ci.resolution_code_id AND reason.class_code_id(+) = ci.reason_code_id AND classification.class_code_id(+) = ci.classification_code_id AND pco_lkp.lookup_type(+) = 'PA_CI_PCO_STATUSES' AND pco_lkp.lookup_code(+) = ci.PCO_STATUS_CODE AND approval_lkp.lookup_type(+) = 'PA_CI_APPROVAL_TYPES' AND approval_lkp.lookup_code(+) = citype.APPROVAL_TYPE_CODE AND locked_lkp.lookup_type(+) = 'PA_CI_LOCKED_STATUSES' AND locked_lkp.lookup_code(+) = decode(ci.LOCKED_FLAG, 'Y', 'LOCKED', 'N', 'UNLOCKED', 'UNLOCKED')

View Text - HTML Formatted

SELECT CI.PROJECT_ID
, PPA.SEGMENT1
, PPA.NAME || '(' || PPA.SEGMENT1 || ')'
, PPA.PROJECT_STATUS_CODE
, PPA.START_DATE
, CI.CI_ID
, CI.CI_NUMBER
, CI.CI_TYPE_ID
, CI.SUMMARY
, CI.DESCRIPTION
, CI.STATUS_CODE
, STATUS.PROJECT_STATUS_NAME
, STATUS.PROJECT_SYSTEM_STATUS_CODE
, CI.OWNER_ID
, OWNER.PARTY_NAME
, CI.OBJECT_TYPE
, CI.OBJECT_ID
, DECODE(CI.OBJECT_ID
, NULL
, NULL
, PA_CONTROL_ITEMS_UTILS.GET_OBJECT_NAME(CI.PROJECT_ID
, CI.OBJECT_ID
, CI.OBJECT_TYPE))
, CI.DATE_REQUIRED
, CI.RESOLUTION_CODE_ID
, RESOLUTION.CLASS_CODE
, CI.RESOLUTION
, CI.STATUS_OVERVIEW
, CI.CLASSIFICATION_CODE_ID
, CLASSIFICATION.CLASS_CODE
, CI.REASON_CODE_ID
, REASON.CLASS_CODE
, CI.PRIORITY_CODE
, PRIORITY_LKP.MEANING
, CI.PRICE
, CI.PRICE_CURRENCY_CODE
, CURRENCY.DESCRIPTION
, CI.SOURCE_TYPE_CODE
, SOURCE_LKP.MEANING
, CI.SOURCE_COMMENT
, CI.SOURCE_NUMBER
, CI.SOURCE_DATE_RECEIVED
, CI.SOURCE_ORGANIZATION
, CI.SOURCE_PERSON
, CI.CREATION_DATE
, CI.LAST_UPDATE_DATE
, CI.CREATED_BY
, PA_CONTROL_ITEMS_UTILS.GETUSERNAME(CI.CREATED_BY)
, CI.LAST_UPDATED_BY
, PA_CONTROL_ITEMS_UTILS.GETUSERNAME(CI.LAST_UPDATED_BY)
, CITYPE.CI_TYPE_CLASS_CODE
, CITYPE.NAME
, CITYPE.SHORT_NAME
, CITYPE.APPROVAL_REQUIRED_FLAG
, CITYPE.AUTO_NUMBER_FLAG
, CITYPE.RESOLUTION_REQUIRED_FLAG
, CITYPE.SOURCE_ATTRS_ENABLED_FLAG
, CI.HIGHLIGHTED_FLAG
, CI.DATE_CLOSED
, CI.CLOSED_BY_ID
, CLOSED_BY.PARTY_NAME
, CI.RECORD_VERSION_NUMBER
, CI.OPEN_ACTION_NUM
, CI.EFFORT_LEVEL_CODE
, EFFORT_LKP.MEANING
, CI.LAST_MODIFICATION_DATE
, CI.LAST_MODIFIED_BY_ID
, MODIFIED_BY.PARTY_NAME
, PPS.PROJECT_STATUS_CODE
, PPS.PROJECT_STATUS_NAME
, PPS.STATUS_ICON_IND
, PPS.STATUS_ICON_ACTIVE_IND
, CI.PROGRESS_AS_OF_DATE
, PA_CONTROL_ITEMS_UTILS.CLOSEALLOWED(CI.CI_ID
, CI.OWNER_ID
, CI.CREATED_BY
, STATUS.PROJECT_SYSTEM_STATUS_CODE)
, PA_CONTROL_ITEMS_UTILS.DELETEALLOWED(CI.CI_ID
, CI.OWNER_ID
, CI.CREATED_BY
, STATUS.PROJECT_SYSTEM_STATUS_CODE)
, PA_CONTROL_ITEMS_UTILS.SUBMITALLOWED(CI.CI_ID
, CI.OWNER_ID
, CI.CREATED_BY
, STATUS.PROJECT_SYSTEM_STATUS_CODE)
, CI.ATTRIBUTE_CATEGORY
, CI.ATTRIBUTE1
, CI.ATTRIBUTE2
, CI.ATTRIBUTE3
, CI.ATTRIBUTE4
, CI.ATTRIBUTE5
, CI.ATTRIBUTE6
, CI.ATTRIBUTE7
, CI.ATTRIBUTE8
, CI.ATTRIBUTE9
, CI.ATTRIBUTE10
, CI.ATTRIBUTE11
, CI.ATTRIBUTE12
, CI.ATTRIBUTE13
, CI.ATTRIBUTE14
, CI.ATTRIBUTE15
, CI.ROWID
, NULL PCO_NUMBER
, CI.VERSION_COMMENTS
, CI.PCO_STATUS_CODE
, PCO_LKP.MEANING PCO_STATUS
, CITYPE.APPROVAL_TYPE_CODE
, APPROVAL_LKP.MEANING APPROVAL_TYPE
, NVL(CI.LOCKED_FLAG
, 'N') LOCKED_FLAG
, LOCKED_LKP.MEANING CHANGE_DOC_STATUS
, CITYPE.IMPACT_BUDGET_TYPE_CODE
, CI.CHANGE_APPROVER
, CI.ORIGINAL_CI_ID
, CI.VERSION_NUMBER
FROM PA_CONTROL_ITEMS CI
, PA_CI_TYPES_VL CITYPE
, PA_PROJECTS_ALL PPA
, PA_PROJECT_STATUSES STATUS
, HZ_PARTIES OWNER
, HZ_PARTIES CLOSED_BY
, HZ_PARTIES MODIFIED_BY
, PA_LOOKUPS PRIORITY_LKP
, PA_LOOKUPS SOURCE_LKP
, PA_LOOKUPS EFFORT_LKP
, FND_CURRENCIES_TL CURRENCY
, PA_CLASS_CODES RESOLUTION
, PA_CLASS_CODES REASON
, PA_CLASS_CODES CLASSIFICATION
, PA_PROJECT_STATUSES PPS
, PA_LOOKUPS PCO_LKP
, PA_LOOKUPS APPROVAL_LKP
, PA_LOOKUPS LOCKED_LKP
WHERE CI.CI_TYPE_ID = CITYPE.CI_TYPE_ID
AND PPA.PROJECT_ID = CI.PROJECT_ID
AND STATUS.PROJECT_STATUS_CODE(+) = CI.STATUS_CODE
AND OWNER.PARTY_ID(+) = CI.OWNER_ID
AND CLOSED_BY.PARTY_ID(+) = CI.CLOSED_BY_ID
AND MODIFIED_BY.PARTY_ID(+) = CI.LAST_MODIFIED_BY_ID
AND PRIORITY_LKP.LOOKUP_TYPE(+) = 'PA_TASK_PRIORITY_CODE'
AND PRIORITY_LKP.LOOKUP_CODE(+) = CI.PRIORITY_CODE
AND CURRENCY.CURRENCY_CODE(+) = CI.PRICE_CURRENCY_CODE
AND CURRENCY.LANGUAGE(+) = USERENV('LANG')
AND SOURCE_LKP.LOOKUP_TYPE(+) = 'PA_CI_SOURCE_TYPES'
AND SOURCE_LKP.LOOKUP_CODE(+) = CI.SOURCE_TYPE_CODE
AND EFFORT_LKP.LOOKUP_TYPE(+) = 'PA_CI_EFFORT_LEVELS'
AND EFFORT_LKP.LOOKUP_CODE(+) = CI.EFFORT_LEVEL_CODE
AND PPS.PROJECT_STATUS_CODE(+) = CI.PROGRESS_STATUS_CODE
AND RESOLUTION.CLASS_CODE_ID(+) = CI.RESOLUTION_CODE_ID
AND REASON.CLASS_CODE_ID(+) = CI.REASON_CODE_ID
AND CLASSIFICATION.CLASS_CODE_ID(+) = CI.CLASSIFICATION_CODE_ID
AND PCO_LKP.LOOKUP_TYPE(+) = 'PA_CI_PCO_STATUSES'
AND PCO_LKP.LOOKUP_CODE(+) = CI.PCO_STATUS_CODE
AND APPROVAL_LKP.LOOKUP_TYPE(+) = 'PA_CI_APPROVAL_TYPES'
AND APPROVAL_LKP.LOOKUP_CODE(+) = CITYPE.APPROVAL_TYPE_CODE
AND LOCKED_LKP.LOOKUP_TYPE(+) = 'PA_CI_LOCKED_STATUSES'
AND LOCKED_LKP.LOOKUP_CODE(+) = DECODE(CI.LOCKED_FLAG
, 'Y'
, 'LOCKED'
, 'N'
, 'UNLOCKED'
, 'UNLOCKED')