EGP_DOCUMENTS_LATEST_REV

Details

Schema: FUSION
Object owner: EGP
Object type: VIEW

Columns

Name
INVENTORY_ITEM_ID ITEM_NUMBER ORGANIZATION_ID ENGINEERED_ITEM_FLAG SERIAL_NUMBER_CONTROL_CODE LOT_CONTROL_CODE REVISION_QTY_CONTROL_CODE MASTER_ORG_ID APPROVAL_STATUS ITEM_CLASS_ID ITEM_CLASS_CODE PARENT_ITEM_CLASS_ID PARENT_ITEM_CLASS_CODE REVISION REVISION_ID EFFECTIVITY_DATE IMPLEMENTATION_DATE END_EFFECTIVITY_DATE

Name

INVENTORY_ITEM_ID

ITEM_NUMBER

ORGANIZATION_ID

ENGINEERED_ITEM_FLAG

SERIAL_NUMBER_CONTROL_CODE

LOT_CONTROL_CODE

REVISION_QTY_CONTROL_CODE

MASTER_ORG_ID

APPROVAL_STATUS

ITEM_CLASS_ID

ITEM_CLASS_CODE

PARENT_ITEM_CLASS_ID

PARENT_ITEM_CLASS_CODE

REVISION

REVISION_ID

EFFECTIVITY_DATE

IMPLEMENTATION_DATE

END_EFFECTIVITY_DATE

Query

SQL_Statement
SELECT /+INDEX (REV EGP_ITEM_REVISIONS_B_U1) / ITEMS.INVENTORY_ITEM_ID, ITEMS.ITEM_NUMBER, ITEMS.ORGANIZATION_ID, ITEMS.ENGINEERED_ITEM_FLAG, ITEMS.SERIAL_NUMBER_CONTROL_CODE, ITEMS.LOT_CONTROL_CODE, ITEMS.REVISION_QTY_CONTROL_CODE, ITEMS.MASTER_ORG_ID, ITEMS.APPROVAL_STATUS, ICH.ITEM_CLASS_ID, ICH.ITEM_CLASS_CODE, ICH.PARENT_ITEM_CLASS_ID, ICH.PARENT_ITEM_CLASS_CODE, REV.REVISION, REV.REVISION_ID, REV.EFFECTIVITY_DATE, REV.IMPLEMENTATION_DATE, REV.END_EFFECTIVITY_DATE FROM EGP_SYSTEM_ITEMS_B ITEMS, EGP_IC_HIERARCHY ICH, ACA_PC_COMMON_CONFIG_B CONF, EGP_ITEM_REVISIONS_B REV WHERE REV.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND REV.ORGANIZATION_ID = ITEMS.INVENTORY_ORGANIZATION_ID AND REV.IMPLEMENTATION_DATE IS NOT NULL AND rev.effectivity_date <= SYSDATE AND (REV.END_EFFECTIVITY_DATE IS NULL OR REV.END_EFFECTIVITY_DATE > SYSDATE) AND ITEMS.ITEM_CATALOG_GROUP_ID = ICH.ITEM_CLASS_ID AND ICH.PARENT_ITEM_CLASS_CODE = CONF.CONFIG_VALUE AND CONF.CONFIG_TYPE = 'ORA_ITEM_SETTINGS' AND CONF.CONFIG_CODE = 'ROOT_DOCUMENT_CLASS' AND ITEMS.TEMPLATE_ITEM_FLAG = 'N' AND ITEMS.APPROVAL_STATUS = 'A' AND ITEMS.ACD_TYPE = 'PROD' AND ITEMS.CHANGE_LINE_ID = -1 AND ITEMS.VERSION_ID = -1 AND NVL(ITEMS.ITEM_OBJECT_TYPE, 'ITEM') in ('ITEM', 'DOCUMENT')

SQL_Statement

SELECT /*+INDEX (REV EGP_ITEM_REVISIONS_B_U1) */

ITEMS.INVENTORY_ITEM_ID, ITEMS.ITEM_NUMBER, ITEMS.ORGANIZATION_ID, ITEMS.ENGINEERED_ITEM_FLAG, ITEMS.SERIAL_NUMBER_CONTROL_CODE, ITEMS.LOT_CONTROL_CODE, ITEMS.REVISION_QTY_CONTROL_CODE, ITEMS.MASTER_ORG_ID, ITEMS.APPROVAL_STATUS,

ICH.ITEM_CLASS_ID, ICH.ITEM_CLASS_CODE, ICH.PARENT_ITEM_CLASS_ID, ICH.PARENT_ITEM_CLASS_CODE,

REV.REVISION, REV.REVISION_ID, REV.EFFECTIVITY_DATE, REV.IMPLEMENTATION_DATE, REV.END_EFFECTIVITY_DATE

FROM

EGP_SYSTEM_ITEMS_B ITEMS,

EGP_IC_HIERARCHY ICH,

ACA_PC_COMMON_CONFIG_B CONF,

EGP_ITEM_REVISIONS_B REV

WHERE

REV.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID

AND REV.ORGANIZATION_ID = ITEMS.INVENTORY_ORGANIZATION_ID

AND REV.IMPLEMENTATION_DATE IS NOT NULL

AND rev.effectivity_date <= SYSDATE

AND (REV.END_EFFECTIVITY_DATE IS NULL OR REV.END_EFFECTIVITY_DATE > SYSDATE)

AND ITEMS.ITEM_CATALOG_GROUP_ID = ICH.ITEM_CLASS_ID

AND ICH.PARENT_ITEM_CLASS_CODE = CONF.CONFIG_VALUE

AND CONF.CONFIG_TYPE = 'ORA_ITEM_SETTINGS'

AND CONF.CONFIG_CODE = 'ROOT_DOCUMENT_CLASS'

AND ITEMS.TEMPLATE_ITEM_FLAG = 'N'

AND ITEMS.APPROVAL_STATUS = 'A'

AND ITEMS.ACD_TYPE = 'PROD'

AND ITEMS.CHANGE_LINE_ID = -1

AND ITEMS.VERSION_ID = -1

AND NVL(ITEMS.ITEM_OBJECT_TYPE, 'ITEM') in ('ITEM', 'DOCUMENT')