VIEW: APPS.BOM_ROUTING_REVISIONS_VIEW

Source

View Text - Preformatted

SELECT rev1.organization_id, rev1.inventory_item_id, rev1.process_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) FROM MTL_RTG_ITEM_REVISIONS REV2,MTL_RTG_ITEM_REVISIONS REV1 where rev1.organization_id = rev2.organization_id(+) and rev1.inventory_item_id = rev2.inventory_item_id(+) and rev2.effectivity_date(+) > rev1.effectivity_date group by rev1.organization_id, rev1.inventory_item_id, rev1.process_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)

View Text - HTML Formatted

SELECT REV1.ORGANIZATION_ID
, REV1.INVENTORY_ITEM_ID
, REV1.PROCESS_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)
FROM MTL_RTG_ITEM_REVISIONS REV2
, MTL_RTG_ITEM_REVISIONS REV1
WHERE REV1.ORGANIZATION_ID = REV2.ORGANIZATION_ID(+)
AND REV1.INVENTORY_ITEM_ID = REV2.INVENTORY_ITEM_ID(+)
AND REV2.EFFECTIVITY_DATE(+) > REV1.EFFECTIVITY_DATE GROUP BY REV1.ORGANIZATION_ID
, REV1.INVENTORY_ITEM_ID
, REV1.PROCESS_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)