[Home] [Help]
View: BOM_ITEM_REVISIONS_VIEW
Product: | BOM - Bills of Material |
Description: | View of item revisions with start and high dates |
Implementation/DBA Data: |
APPS.BOM_ITEM_REVISIONS_VIEW
|
View Text
SELECT REV1.ORGANIZATION_ID
, REV1.INVENTORY_ITEM_ID
, REV1.REVISION
, SUBSTR(TO_CHAR(REV1.EFFECTIVITY_DATE
, 'YYYY/MM/DD HH24:MI:SS')
, 1
, 25)
, SUBSTR(TO_CHAR(MIN(DECODE( REV2.EFFECTIVITY_DATE
, NULL
, GREATEST(SYSDATE
, REV1.EFFECTIVITY_DATE)
, (REV2.EFFECTIVITY_DATE - 60 / (60 * 60 * 24) ) ) )
, 'YYYY/MM/DD HH24:MI:SS' )
, 1
, 25)
, SUBSTR(TO_CHAR(REV1.IMPLEMENTATION_DATE
, 'YYYY/MM/DD HH24:MI:SS')
, 1
, 25)
, RI.CHANGE_NOTICE
, RI.REVISED_ITEM_ID
, RI.STATUS_TYPE
FROM ENG_REVISED_ITEMS RI
, MTL_ITEM_REVISIONS_B REV2
, MTL_ITEM_REVISIONS_B REV1
WHERE REV1.ORGANIZATION_ID = REV2.ORGANIZATION_ID(+)
AND REV1.INVENTORY_ITEM_ID = REV2.INVENTORY_ITEM_ID(+)
AND REV2.EFFECTIVITY_DATE(+) > REV1.EFFECTIVITY_DATE
AND REV1.REVISED_ITEM_SEQUENCE_ID = RI.REVISED_ITEM_SEQUENCE_ID(+) GROUP BY REV1.ORGANIZATION_ID
, REV1.INVENTORY_ITEM_ID
, REV1.REVISION
, SUBSTR(TO_CHAR(REV1.EFFECTIVITY_DATE
, 'YYYY/MM/DD HH24:MI:SS')
, 1
, 25)
, REV1.EFFECTIVITY_DATE
, SUBSTR(TO_CHAR(REV1.IMPLEMENTATION_DATE
, 'YYYY/MM/DD HH24:MI:SS')
, 1
, 25)
, RI.CHANGE_NOTICE
, RI.REVISED_ITEM_ID
, RI.STATUS_TYPE
Columns
Name |
ORGANIZATION_ID |
INVENTORY_ITEM_ID |
REVISION |
EFFECTIVITY_DATE |
HIGH_DATE |
IMPLEMENTATION_DATE |
CHANGE_NOTICE |
REVISED_ITEM_ID |
REVISED_ITEM_STATUS |