PROMPT 'Click OK and enter one of the Book Type Codes listed on the next window.'
ACCEPT btc PROMPT 'FA_CARBONDALE, FA_PRESIDENT, FA_UNIV_WIDE, FA_SPRINGFIELD, FA_EDWARDSVILLE'
ACCEPT unit PROMPT 'Enter the UNIT number (Format: #####)'





--WRITTEN BY AMY EATON 10/24/02 TO OBTAIN INVENTORY LISTINGS TO SUBMIT TO CMS
--MODIFIED BY BONNIE STOFFEL 1/14/03 TO INCLUDE ACQ ACCT
--MODIFIED BY BONNIE STOFFEL 8/20/04 TO INCLUDE SEARCH BY OLD TAG #
--MODIFIED BY BONNIE STOFFEL 11/03/06 TO CHANGE TABLE NAMES (FA_ADDITIONS TO FA_ADDITIONS_B AND AND FA_ADDITIONS_TL (DESCRIPTION COLUMN)FOR 11I UPGRADE
SELECT

		FAB.TAG_NUMBER AS "TAG NUMBER",
       	FAB.ATTRIBUTE10 AS "OLD TAG NMBER",
		LOC.SEGMENT1 AS "UNIT",
       	LOC.SEGMENT2 AS "USER_SEG",
		LOC.SEGMENT3 AS "BUILDING",
		LOC.SEGMENT4 AS "ROOM",
		FATL.DESCRIPTION AS "DESCRIPTION",
      	FAB.MANUFACTURER_NAME AS "MANUFACTURER",
       	FAB.MODEL_NUMBER AS "MODEL NUMBER",
       	FAB.SERIAL_NUMBER AS "SERIAL NUMBER",
       	BK.DATE_PLACED_IN_SERVICE AS "ACQ DATE",
       	BK.COST AS "COST"

		--CC.SEGMENT1 AS "CLASSIFICATION",
       	--CC.SEGMENT2 AS "EXPENSE",
       --LOC.SEGMENT7 AS "FUNCTION",
       --FAB.ATTRIBUTE1 AS "TRACT",
       --FAB.ATTRIBUTE15 AS "ACCOUNT_DESCRIPTION",
       --FAB.ATTRIBUTE2 AS "ACREAGE", -- for Land
       --FAB.ASSET_NUMBER AS "ASSET NUMBER",
 		--FAB.CURRENT_UNITS,
       --KW.SEGMENT10 AS "FAS_BUYING_ACCT",
       --KW.SEGMENT3 AS "AIS BUYING ACCT",
       --FAB.ASSET_TYPE AS "ASSET TYPE"


FROM FA.FA_ADDITIONS_B FAB,
     FA.FA_ADDITIONS_TL FATL,
     FA.FA_ASSET_KEYWORDS KW,
     FA.FA_LOCATIONS LOC,
     FA.FA_BOOKS BK,
     GL.GL_CODE_COMBINATIONS CC,
     FA.FA_DISTRIBUTION_HISTORY DH
-- ****TABLE LINKING SECTION DO NOT CHANGE ****
WHERE  FAB.ASSET_ID = BK.ASSET_ID
       AND FAB.ASSET_ID = DH.ASSET_ID
       AND FAB.ASSET_ID = FATL.ASSET_ID
       AND DH.DATE_INEFFECTIVE IS NULL
       AND BK.DATE_INEFFECTIVE IS NULL
       AND DH.LOCATION_ID = LOC.LOCATION_ID
       AND DH.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID
       AND BK.ADJUSTED_COST <> 0
       AND FAB.ASSET_KEY_CCID = KW.CODE_COMBINATION_ID

--SPECIFY BOOK TYPE CODE (OPTIONAL)
     AND UPPER(BK.BOOK_TYPE_CODE) = '&btc'

--SEARCH BY BUILDING (OPTIONAL)
   --  AND LOC.SEGMENT3 in('001231', '000079', '000482') --BUILDING MUST CONTAIN 6 CHARACTERS.  ENTER LEAD ZERO IF NECESSARY

--SEARCH BY ROOM NUMBER (OPTIONAL)
   --  AND LOC.SEGMENT4 LIKE '00315%' --ROOM IS TYPICALLY 5 CHARACTERS, POSSIBLY FOLLOWED BY AN ALPHA

--SEARCH BY BUYING ACCOUNT (OPTIONAL) -- ENTER THE FAS ACCOUNT IN SEGMENT10 AND CORRESPONDING AIS BP IN SEGMENT3
   --  AND (KW.SEGMENT10 IN ('621891','621669','622231','622275')
       --  OR KW.SEGMENT3 IN ('223399', '221476', '222181', '222606', '222880', '223121', '223201'))

--SEARCH BY UNIT (OPTIONAL)
    AND CC.SEGMENT2 LIKE '&unit'                      --FOR A SINGLE UNIT
     --AND CC.SEGMENT2 BETWEEN '#####'AND '#####'        --FOR A RANGE OF UNITS

--SEARCH BY CLASSIFICATION (OPTIONAL)
   --  AND CC.SEGMENT1 LIKE '2861802'            --CARBONDALE OWNED LAND
    -- AND CC.SEGMENT1 LIKE '2861803'            --CARBONDALE OWNED BUILDINGS
   --  AND CC.SEGMENT1 LIKE '2861804'            --CARBONDALE OWNED IMPROVEMENTS
    -- AND CC.SEGMENT1 LIKE '2861805'            --CARBONDALE OWNED EQUIPMENT
   --  AND CC.SEGMENT1 LIKE '2861810'            --CARBONDALE LEASED EQUIPMENT
   --  AND CC.SEGMENT1 LIKE '0869004'            --PRESIDENT OWNED EQUIPMENT
   --  AND CC.SEGMENT1 LIKE '1869004'            --UNIVERSITY WIDE OWNED EQUIPMENT
   --  AND CC.SEGMENT1 LIKE '5860451'            --SPRINGFIELD OWNED LAND
   --  AND CC.SEGMENT1 LIKE '5860452'            --SPRINGFIELD OWNED BUILDINGS
   --  AND CC.SEGMENT1 LIKE '5860453'            --SPRINGFIELD OWNED IMPROVMENTS
   --  AND CC.SEGMENT1 LIKE '5860454'            --SPRINGFIELD OWNED EQUIPMENT
   --  AND CC.SEGMENT1 LIKE '5860456'            --SPRINGFIELD LEASED EQUIPMENT
   --  AND CC.SEGMENT1 LIKE '7866010'            --EDWARDSVILLE OWNED LAND
   --  AND CC.SEGMENT1 LIKE '7866020'            --EDWARDSVILLE OWNED BUILDINGS
   --  AND CC.SEGMENT1 LIKE '7866030'            --EDWARDSVILLE OWNED IMPROVEMENTS
    -- AND CC.SEGMENT1 LIKE '7866040'            --EDWARDSVILLE OWNED EQUIPMENT
   --  AND CC.SEGMENT1 LIKE '7866054'            --EDWARDSVILLE LEASED EQUIPMENT
    --   AND CC.SEGMENT1 IN ('2861805','2861810', '0869004', '1869004','5860454', '7866040', '7866054')

--SEARCH BY TAG NUMBER (Remove the comment marks '--' from the beginning of the line and replace the # signs with an inventory tag number.)
   --  AND FAB.TAG_NUMBER LIKE 'C######'                      --SEARCH BY INDIVIDUAL TAG
   --  AND FAB.TAG_NUMBER BETWEEN 'C######' AND 'C######'     --SEARCH BY CONSECUTIVE SERIES OF TAGS
   --  AND FAB.TAG_NUMBER IN ('C######','C######','C######')  --SEARCH BY NON-CONSECTIVE SERIES OF TAGS

--SEARCH BY OLD TAG NUMBER (OPTIONAL)
  --AND FAB.ATTRIBUTE10 LIKE 'C######'

--CHOOSE SORTING ORDER
ORDER BY FAB.TAG_NUMBER 											--SORT IN TAG NUMBER ORDER
--ORDER BY LOC.SEGMENT3, LOC.SEGMENT4, FAB.TAG_NUMBER;              --SORT IN BUILDING, ROOM, TAG # ORDER
