View: ENG_ENGINEERING_CHANGES_ERV

Product:	ENG - Engineering
Description:
Implementation/DBA Data:	APPS.ENG_ENGINEERING_CHANGES_ERV

View Text

SELECT DISTINCT EEC.ROWID ROW_ID
, EEC.CHANGE_NOTICE
, EEC.ORGANIZATION_ID
, MP1.ORGANIZATION_CODE
, HOU1.NAME ORGANIZATION_NAME
, EEC.LAST_UPDATE_DATE
, EEC.LAST_UPDATED_BY
, EEC.CREATION_DATE
, EEC.CREATED_BY
, EEC.LAST_UPDATE_LOGIN
, EEC.DESCRIPTION
, EEC.STATUS_TYPE
, ECSVL.STATUS_NAME ECO_STATUS
, MLU2.MEANING APPROVAL_STATUS
, EEC.INITIATION_DATE
, EEC.IMPLEMENTATION_DATE
, EEC.CANCELLATION_DATE
, EEC.CANCELLATION_COMMENTS
, EEC.PRIORITY_CODE
, ECP.DESCRIPTION PRIORITY_CODE_DESCRIPTION
, EEC.REASON_CODE
, ECR.DESCRIPTION REASON_CODE_DESCRIPTION
, EEC.ESTIMATED_ENG_COST
, EEC.ESTIMATED_MFG_COST
, EEC.REQUESTOR_ID
, HZP.PARTY_NAME REQUESTOR_NAME
, EEC.CHANGE_ORDER_TYPE_ID
, ECOT.CHANGE_ORDER_TYPE CHANGE_ORDER_TYPE
, ECOT.DESCRIPTION CHANGE_ORDER_TYPE_DESCRIPTION
, ECOT.ASSEMBLY_TYPE ASSEMBLY_TYPE
, EEC.RESPONSIBLE_ORGANIZATION_ID
, HOU.NAME ECO_DEPARTMENT
, ECTP.PROCESS_NAME
, EWF.DISPLAY_NAME APPROVAL_PROCESS
, EEC.APPROVAL_STATUS_TYPE
, EEC.APPROVAL_DATE
, EEC.APPROVAL_LIST_ID
, EEAL.APPROVAL_LIST_NAME APPROVAL_LIST_NAME
, EEAL.DESCRIPTION APPROVAL_LIST_NAME_DESCRIPTION
, EEC.APPROVAL_REQUEST_DATE
, (EEC.APPROVAL_DATE - EEC.APPROVAL_REQUEST_DATE) DAYS_TO_APPROVE
, EEC.ATTRIBUTE_CATEGORY
, EEC.ATTRIBUTE1
, EEC.ATTRIBUTE2
, EEC.ATTRIBUTE3
, EEC.ATTRIBUTE4
, EEC.ATTRIBUTE5
, EEC.ATTRIBUTE6
, EEC.ATTRIBUTE7
, EEC.ATTRIBUTE8
, EEC.ATTRIBUTE9
, EEC.ATTRIBUTE10
, EEC.ATTRIBUTE11
, EEC.ATTRIBUTE12
, EEC.ATTRIBUTE13
, EEC.ATTRIBUTE14
, EEC.ATTRIBUTE15
, EEC.REQUEST_ID
, EEC.PROGRAM_APPLICATION_ID
, EEC.PROGRAM_ID
, EEC.PROGRAM_UPDATE_DATE
, PPA.NAME PROJECT_NAME
, PPA.SEGMENT1 PROJECT_NUMBER
, EEC.PROJECT_ID
, PT.TASK_NAME
, PT.TASK_NUMBER
, EEC.TASK_ID
, NVL(EEC.HIERARCHY_FLAG
, 2)
, EEC.HIERARCHY_ID
, POS.NAME
, EEC.CHANGE_ID
, EEC.CHANGE_MGMT_TYPE_CODE
, EEC.ASSIGNEE_ID
, COR.REVISION CURRENT_REVISION
, CSD.SCHEDULED_DATE CURRENT_SCHEDULED_DATE
, CSD.EMPLOYEE_ID SCHEDULE_REQUESTOR_ID
, HZP1.PARTY_NAME SCHEDULE_REQUESTOR_NAME
, CSD.COMMENTS SCHEDULE_COMMENTS
FROM ENG_CHANGE_ORDER_TYPES_V ECOT
, ENG_CHANGE_REASONS ECR
, ENG_CHANGE_PRIORITIES ECP
, ENG_ECN_APPROVAL_LISTS EEAL
, HR_ALL_ORGANIZATION_UNITS_TL HOU
, MFG_LOOKUPS MLU2
, ENG_ENGINEERING_CHANGES EEC
, PA_PROJECTS_ALL PPA
, PA_TASKS PT
, PER_ORGANIZATION_STRUCTURES POS
, ENG_CHANGE_STATUSES_VL ECSVL
, ENG_CHANGE_MGMT_TYPES ECMT
, HZ_PARTIES HZP
, HZ_PARTIES HZP1
, HR_ALL_ORGANIZATION_UNITS_TL HOU1
, MTL_PARAMETERS MP1
, ENG_CHANGE_ORDER_REVISIONS COR
, ENG_CURRENT_SCHEDULED_DATES CSD
, ENG_WF_PROCESS_V EWF
, ENG_CHANGE_TYPE_PROCESSES ECTP
WHERE ECOT.CHANGE_ORDER_TYPE_ID = EEC.CHANGE_ORDER_TYPE_ID
AND EEC.REASON_CODE = ECR.ENG_CHANGE_REASON_CODE(+)
AND EWF.PROCESS_NAME(+) = ECTP.PROCESS_NAME
AND ECTP.CHANGE_ORDER_TYPE_ID(+) = EEC.CHANGE_ORDER_TYPE_ID
AND EEC.PRIORITY_CODE = ECTP.ENG_CHANGE_PRIORITY_CODE(+)
AND (EEC.PRIORITY_CODE IS NULL OR (EEC.PRIORITY_CODE IS NOT NULL
AND (EXISTS (SELECT MAX(X1.ORGANIZATION_ID)
FROM ENG_CHANGE_TYPE_PROCESSES X1
WHERE X1.CHANGE_ORDER_TYPE_ID = EEC.CHANGE_ORDER_TYPE_ID
AND X1.ENG_CHANGE_PRIORITY_CODE = EEC.PRIORITY_CODE
AND (X1.ORGANIZATION_ID = EEC.ORGANIZATION_ID OR X1.ORGANIZATION_ID = -1)) )) )
AND COR.ORGANIZATION_ID(+) = EEC.ORGANIZATION_ID
AND COR.CHANGE_NOTICE(+) = EEC.CHANGE_NOTICE
AND ((COR.REVISION_ID IS NULL) OR COR.REVISION_ID IN (SELECT MAX(COR1.REVISION_ID)
FROM ENG_CHANGE_ORDER_REVISIONS COR1
WHERE COR1.ORGANIZATION_ID = EEC.ORGANIZATION_ID
AND COR1.CHANGE_NOTICE = EEC.CHANGE_NOTICE) )
AND HZP1.PARTY_ID(+) = CSD.EMPLOYEE_ID
AND CSD.ORGANIZATION_ID(+) = EEC.ORGANIZATION_ID
AND CSD.CHANGE_NOTICE(+) = EEC.CHANGE_NOTICE
AND (CSD.CREATION_DATE IS NULL OR CSD.CREATION_DATE IN (SELECT MAX(CSD1.CREATION_DATE)
FROM ENG_CURRENT_SCHEDULED_DATES CSD1
WHERE CSD1.ORGANIZATION_ID = EEC.ORGANIZATION_ID
AND CSD1.CHANGE_NOTICE = EEC.CHANGE_NOTICE) )
AND ECR.ORGANIZATION_ID(+) = -1
AND EEC.PRIORITY_CODE = ECP.ENG_CHANGE_PRIORITY_CODE(+)
AND ECP.ORGANIZATION_ID(+) = -1
AND EEC.APPROVAL_LIST_ID = EEAL.APPROVAL_LIST_ID(+)
AND HOU.ORGANIZATION_ID(+) = EEC.RESPONSIBLE_ORGANIZATION_ID
AND HOU.LANGUAGE(+) = USERENV('LANG')
AND EEC.PROJECT_ID = PPA.PROJECT_ID(+)
AND EEC.PROJECT_ID = PT.PROJECT_ID(+)
AND EEC.TASK_ID = PT.TASK_ID(+)
AND HOU1.ORGANIZATION_ID = EEC.ORGANIZATION_ID
AND HOU1.LANGUAGE = USERENV('LANG')
AND MP1.ORGANIZATION_ID = EEC.ORGANIZATION_ID
AND HZP.PARTY_ID(+) = EEC.REQUESTOR_ID
AND EEC.STATUS_TYPE = ECSVL.STATUS_CODE(+)
AND EEC.APPROVAL_STATUS_TYPE = MLU2.LOOKUP_CODE(+)
AND MLU2.LOOKUP_TYPE = 'ENG_ECN_APPROVAL_STATUS'
AND HIERARCHY_ID = POS.ORGANIZATION_STRUCTURE_ID(+)
AND ECMT.CHANGE_MGMT_TYPE_CODE =EEC.CHANGE_MGMT_TYPE_CODE
AND (EEC.CHANGE_MGMT_TYPE_CODE='CHANGE_ORDER' OR ECMT.ENABLE_REV_ITEMS_FLAG='Y' )

Columns