INV Item Revisions (Oracle Fusion SQL)

Supply Chain & Manufacturing (SCM) Inventory (INV-Fusion-SQL's)
1

This INV item revisions query provides details of item revisions including the revision code, description, and flags indicating whether the revision is current or pending implementation. It joins item and revision data with inventory organization context to support item version tracking and lifecycle management in inventory systems.

#sqlquery

SELECT
  --SQL4Fusion (An Orbit Analytics Project)
  --This query displays item and revision details by organization, helping users identify current and pending revisions for inventory management decisions.
        -- Item Information
		HAOU.NAME ORGANIZATION_NAME,
        ESIB.INVENTORY_ITEM_ID    INVENTORY_ITEM_ID,
        ESIB.ITEM_NUMBER          ITEM_NUMBER,
        -- Revision Information
        --EIRB.REVISION_ID          REVISION_ID,
		EIRB.REVISION,
        EIRB.REVISION_REASON,
		EIRB.DESCRIPTION          ITEM_REVISION_DESCRIPTION,
        EIRB.EFFECTIVITY_DATE     REVISION_START_DATE,
        EIRB.END_EFFECTIVITY_DATE REVISION_END_DATE,
        EIRB.CREATION_DATE        REVISION_CREATION_DATE,
        EIRB.LAST_UPDATE_DATE     REVISION_LAST_UPDATE_DATE,
        -- Flags
        CASE 
            WHEN RANK() OVER (
                     PARTITION BY ESIB.INVENTORY_ITEM_ID 
                     ORDER BY EIRB.EFFECTIVITY_DATE DESC
                 ) = 1
                 AND TRUNC(SYSDATE) BETWEEN TRUNC(EIRB.EFFECTIVITY_DATE) 
                                        AND NVL(TRUNC(EIRB.END_EFFECTIVITY_DATE), DATE '4712-12-31')
            THEN 'Y' 
            ELSE 'N'
        END AS CURRENT_REVISION_FLAG,
        CASE 
            WHEN TRUNC(EIRB.EFFECTIVITY_DATE) > TRUNC(SYSDATE)
            THEN 'Y'
            ELSE 'N'
        END AS PENDING_REVISION_FLAG
    FROM
        EGP_ITEM_REVISIONS_B    EIRB,
        EGP_SYSTEM_ITEMS_B      ESIB,
	HR_ALL_ORGANIZATION_UNITS HAOU
    WHERE
        EIRB.INVENTORY_ITEM_ID = ESIB.INVENTORY_ITEM_ID
        AND EIRB.ORGANIZATION_ID = ESIB.ORGANIZATION_ID
	    AND HAOU.ORGANIZATION_ID = EIRB.ORGANIZATION_ID