This query shows Items in a multi-org setup which have been created or modified within the past 30 days, or which will be
made effective at some future date. (One would of course change the ACCEPT prompt to reflect one's own Organiziation_ID numbers and Organization Names, as noted in the code comments below.)

One of the most interesting things about writing this query was using the techniques for NEW_VALUE to make a more interactive and informative TTITLE. (Since we have several Test instances along with our Production instance, on top of the multi-org setup, it's important to know what you are looking at !)
 

SET heading OFF verify OFF echo OFF feedback OFF
SET pagesize 66

COLUMN "Item" FORMAT A15
COLUMN "Description" FORMAT A25 TRUNCATE
COLUMN "Org" FORMAT 999
COLUMN "Rev" FORMAT 999
COLUMN "Effective" FORMAT A9
COLUMN "New ?" FORMAT A5

BREAK ON "Item" ON "Description" ON "Org" ON "Eff. Dt."

ACCEPT v_org PROMPT "(Default 'All Orgs' or '590' for MST, '591' for NL, '586' for US) Org? :"
--Modify to suit your Organization Name and Number setup.
--(The NVL is handled below as the default.)

COLUMN q_org NEW_VALUE this_org NOPRINT
COLUMN q_day NEW_VALUE this_day NOPRINT
COLUMN q_now NEW_VALUE right_now NOPRINT

SELECT TO_CHAR( sysdate, 'dd-Mon-yyyy') q_day
, TO_CHAR( sysdate, 'HH24:MI "(System Time)"') q_now
, 'Items and Revisions Created OR Made Effective in '
|| NVL( '&&v_org', 'All Orgs') || ' in ' || db.name q_org
FROM dual
, v$database db
/

TTITLE LEFT this_day -
CENTER right_now -
RIGHT 'Page ' FORMAT 999 sql.pno SKIP 2 -
CENTER this_org SKIP -
CENTER "Within the Past 30 Days OR To Be Made Effective in the Future" SKIP 2
-- Techniques from Oracle: A Beginner's Guide, Pages 294 - 295:
-- using NEW_VALUE to set 'new' columns, for use in TTITLE and BTITLE,
-- and using sql.pno to show page numbers when the default's not set.

BTITLE SKIP LEFT "Effective '-' means 'Same day as 'Created'.'" -
RIGHT "ITEM_REVS.SQL"
SET heading ON feedback ON

SELECT b.segment1 "Item"
, b.description "Description"
, b.organization_id "Org"
, a.revision "Rev"
, a.creation_date "Created"
, DECODE( TRUNC( a.effectivity_date - a.creation_date)
, 0, ' -'
, 1, ' Next day'
, TO_CHAR( a.effectivity_date)) "Effective"
, DECODE( a.revision
, 0, ' NEW'
, NULL) "New ?"
FROM inv.mtl_item_revisions a
, inv.mtl_system_items b
WHERE a.inventory_item_id = b.inventory_item_id
AND a.organization_id = b.organization_id
AND (a.effectivity_date > sysdate - 30
OR a.creation_date > sysdate - 30)
AND TO_CHAR( a.organization_id) LIKE NVL( '&v_org', '%')
ORDER BY "Item", "Org"
/

CLEAR COLUMN
TTITLE OFF
BTITLE OFF

--Review Items/Revisions created within the past 30 days,
--OR effective within the past 30 days,
--OR to be made effective in the future.

Return to : Oracle Database, SQL, Application, Programming Tips