VIEW: APPS.PA_CI_CRPRJ_LIST_V

Source

View Text - Preformatted

SELECT ci.project_id ,ppa.segment1 ,ppa.name ,ppa.long_name ,ppa.name || '(' || ppa.segment1 || ')' ,ppa.project_status_code ,ppa.start_date ,ci.ci_id ,ci.ci_number ,citypeb.ci_type_id ,ci.summary ,ci.description ,ci.status_code ,status.project_status_name ,status.project_system_status_code ,null ,ci.owner_id ,owner.party_name ,ci.object_type ,ci.object_id ,DECODE(ci.object_type, NULL, NULL, pa_proj_elements_utils.get_element_name(ci.object_id)) ,DECODE(ci.object_type, NULL, NULL, pa_proj_elements_utils.get_element_number(ci.object_id)) ,DECODE(ci.object_type, NULL, NULL, pa_proj_elements_utils.get_element_name_number(ci.object_id)) ,ci.date_required ,resolution.class_code ,ci.resolution ,resolution.class_category ,ci.status_overview ,classification.class_code ,reason.class_code ,ci.priority_code ,priority_lkp.meaning ,to_number(priority_lkp.predefined_flag) ,ci.price ,ci.price_currency_code ,null ,ci.source_type_code ,null ,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 ,ci.last_updated_by ,citypeb.ci_type_class_code ,citypetl.name ,citypetl.short_name ,citypeb.approval_required_flag ,null ,ci.date_closed ,ci.closed_by_id ,null ,ci.open_action_num ,ci.record_version_number ,sci.create_action_flag ,sci.update_flag ,citypetl.short_name || '(' || ci.ci_number || ')' ,ci.progress_status_code ,pro_status.project_status_name ,ci.progress_as_of_date ,pro_status.status_icon_active_ind ,ci.effort_level_code ,null ,decode(ci.status_code, 'CI_CLOSED', null, 'CI_CANCELED', null, TRUNC(ci.date_required) - TRUNC(sysdate)) ,decode(ci.status_code, 'CI_CLOSED', null, 'CI_CANCELED', null, TRUNC(sysdate) - TRUNC(ci.last_update_date)) ,fp.est_quantity ,fp.labor_quantity ,fp.est_project_raw_cost ,fp.total_project_raw_cost ,fp.est_project_burdened_cost ,fp.total_project_burdened_cost ,fp.est_project_revenue ,fp.total_project_revenue ,fp.derived_quantity ,fp.derived_cost ,fp.derived_revenue ,fp.derived_margin ,fp.derived_margin_percentage ,fp.est_margin ,fp.est_margin_percentage ,fp.planned_margin ,fp.planned_margin_percentage ,ppa.project_currency_code ,null ,ci.last_modified_by_id ,null ,ci.classification_code_id ,ci.reason_code_id ,ci.resolution_code_id ,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.highlighted_flag FROM pa_fp_ci_amounts_v fp ,pa_class_codes reason ,pa_class_codes classification ,pa_lookups priority_lkp ,hz_parties owner ,pa_project_statuses pro_status ,pa_project_statuses status ,pa_projects_all ppa ,pa_ci_types_tl citypetl ,pa_ci_types_b citypeb ,PA_CI_SECURED_V sci ,pa_control_items ci ,pa_class_codes resolution WHERE ci.ci_type_id = citypeb.ci_type_id AND sci.ci_id = ci.ci_id AND citypetl.ci_type_id = citypeb.ci_type_id AND citypetl.language = USERENV('LANG') AND ppa.project_id = ci.project_id AND status.project_status_code = ci.status_code AND status.status_type = 'CONTROL_ITEM' AND pro_status.project_status_code = ci.progress_status_code AND pro_status.status_type = 'PROGRESS' AND owner.party_id = ci.owner_id AND priority_lkp.lookup_code(+) = ci.priority_code AND priority_lkp.lookup_type(+) = 'PA_TASK_PRIORITY_CODE' AND reason.class_code_id(+) = ci.reason_code_id AND classification.class_code_id(+) = ci.classification_code_id AND fp.ci_id(+) = ci.ci_id AND fp.project_id(+) = ci.project_id AND resolution.class_code_id(+) = ci.resolution_code_id

View Text - HTML Formatted

SELECT CI.PROJECT_ID
, PPA.SEGMENT1
, PPA.NAME
, PPA.LONG_NAME
, PPA.NAME || '(' || PPA.SEGMENT1 || ')'
, PPA.PROJECT_STATUS_CODE
, PPA.START_DATE
, CI.CI_ID
, CI.CI_NUMBER
, CITYPEB.CI_TYPE_ID
, CI.SUMMARY
, CI.DESCRIPTION
, CI.STATUS_CODE
, STATUS.PROJECT_STATUS_NAME
, STATUS.PROJECT_SYSTEM_STATUS_CODE
, NULL
, CI.OWNER_ID
, OWNER.PARTY_NAME
, CI.OBJECT_TYPE
, CI.OBJECT_ID
, DECODE(CI.OBJECT_TYPE
, NULL
, NULL
, PA_PROJ_ELEMENTS_UTILS.GET_ELEMENT_NAME(CI.OBJECT_ID))
, DECODE(CI.OBJECT_TYPE
, NULL
, NULL
, PA_PROJ_ELEMENTS_UTILS.GET_ELEMENT_NUMBER(CI.OBJECT_ID))
, DECODE(CI.OBJECT_TYPE
, NULL
, NULL
, PA_PROJ_ELEMENTS_UTILS.GET_ELEMENT_NAME_NUMBER(CI.OBJECT_ID))
, CI.DATE_REQUIRED
, RESOLUTION.CLASS_CODE
, CI.RESOLUTION
, RESOLUTION.CLASS_CATEGORY
, CI.STATUS_OVERVIEW
, CLASSIFICATION.CLASS_CODE
, REASON.CLASS_CODE
, CI.PRIORITY_CODE
, PRIORITY_LKP.MEANING
, TO_NUMBER(PRIORITY_LKP.PREDEFINED_FLAG)
, CI.PRICE
, CI.PRICE_CURRENCY_CODE
, NULL
, CI.SOURCE_TYPE_CODE
, NULL
, 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
, CI.LAST_UPDATED_BY
, CITYPEB.CI_TYPE_CLASS_CODE
, CITYPETL.NAME
, CITYPETL.SHORT_NAME
, CITYPEB.APPROVAL_REQUIRED_FLAG
, NULL
, CI.DATE_CLOSED
, CI.CLOSED_BY_ID
, NULL
, CI.OPEN_ACTION_NUM
, CI.RECORD_VERSION_NUMBER
, SCI.CREATE_ACTION_FLAG
, SCI.UPDATE_FLAG
, CITYPETL.SHORT_NAME || '(' || CI.CI_NUMBER || ')'
, CI.PROGRESS_STATUS_CODE
, PRO_STATUS.PROJECT_STATUS_NAME
, CI.PROGRESS_AS_OF_DATE
, PRO_STATUS.STATUS_ICON_ACTIVE_IND
, CI.EFFORT_LEVEL_CODE
, NULL
, DECODE(CI.STATUS_CODE
, 'CI_CLOSED'
, NULL
, 'CI_CANCELED'
, NULL
, TRUNC(CI.DATE_REQUIRED) - TRUNC(SYSDATE))
, DECODE(CI.STATUS_CODE
, 'CI_CLOSED'
, NULL
, 'CI_CANCELED'
, NULL
, TRUNC(SYSDATE) - TRUNC(CI.LAST_UPDATE_DATE))
, FP.EST_QUANTITY
, FP.LABOR_QUANTITY
, FP.EST_PROJECT_RAW_COST
, FP.TOTAL_PROJECT_RAW_COST
, FP.EST_PROJECT_BURDENED_COST
, FP.TOTAL_PROJECT_BURDENED_COST
, FP.EST_PROJECT_REVENUE
, FP.TOTAL_PROJECT_REVENUE
, FP.DERIVED_QUANTITY
, FP.DERIVED_COST
, FP.DERIVED_REVENUE
, FP.DERIVED_MARGIN
, FP.DERIVED_MARGIN_PERCENTAGE
, FP.EST_MARGIN
, FP.EST_MARGIN_PERCENTAGE
, FP.PLANNED_MARGIN
, FP.PLANNED_MARGIN_PERCENTAGE
, PPA.PROJECT_CURRENCY_CODE
, NULL
, CI.LAST_MODIFIED_BY_ID
, NULL
, CI.CLASSIFICATION_CODE_ID
, CI.REASON_CODE_ID
, CI.RESOLUTION_CODE_ID
, 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.HIGHLIGHTED_FLAG
FROM PA_FP_CI_AMOUNTS_V FP
, PA_CLASS_CODES REASON
, PA_CLASS_CODES CLASSIFICATION
, PA_LOOKUPS PRIORITY_LKP
, HZ_PARTIES OWNER
, PA_PROJECT_STATUSES PRO_STATUS
, PA_PROJECT_STATUSES STATUS
, PA_PROJECTS_ALL PPA
, PA_CI_TYPES_TL CITYPETL
, PA_CI_TYPES_B CITYPEB
, PA_CI_SECURED_V SCI
, PA_CONTROL_ITEMS CI
, PA_CLASS_CODES RESOLUTION
WHERE CI.CI_TYPE_ID = CITYPEB.CI_TYPE_ID
AND SCI.CI_ID = CI.CI_ID
AND CITYPETL.CI_TYPE_ID = CITYPEB.CI_TYPE_ID
AND CITYPETL.LANGUAGE = USERENV('LANG')
AND PPA.PROJECT_ID = CI.PROJECT_ID
AND STATUS.PROJECT_STATUS_CODE = CI.STATUS_CODE
AND STATUS.STATUS_TYPE = 'CONTROL_ITEM'
AND PRO_STATUS.PROJECT_STATUS_CODE = CI.PROGRESS_STATUS_CODE
AND PRO_STATUS.STATUS_TYPE = 'PROGRESS'
AND OWNER.PARTY_ID = CI.OWNER_ID
AND PRIORITY_LKP.LOOKUP_CODE(+) = CI.PRIORITY_CODE
AND PRIORITY_LKP.LOOKUP_TYPE(+) = 'PA_TASK_PRIORITY_CODE'
AND REASON.CLASS_CODE_ID(+) = CI.REASON_CODE_ID
AND CLASSIFICATION.CLASS_CODE_ID(+) = CI.CLASSIFICATION_CODE_ID
AND FP.CI_ID(+) = CI.CI_ID
AND FP.PROJECT_ID(+) = CI.PROJECT_ID
AND RESOLUTION.CLASS_CODE_ID(+) = CI.RESOLUTION_CODE_ID