--SET NOCOUNT OFF


BEGIN TRANSACTION Step1
  --CREATE TEMP TABLE #PCAMDL TO STORE PCA-TO-MODEL DATA

  CREATE TABLE #PCAMDL (
	  PCA		CHAR(5)		NOT NULL,
          MODEL		CHAR(5)		NOT NULL
        	)

IF @@ERROR <> 0 BEGIN
   RAISERROR 50000 'SQL Transaction error STEP 1 Aborting...'
   ROLLBACK TRANSACTION Step1
   GOTO ENDOFBATCH
END

COMMIT TRANSACTION -- step1


BEGIN TRANSACTION Step2
  --LOAD DATA INTO TABLE #PCAMDL

  --a whole crapload of values gets input here

IF @@ERROR <> 0 BEGIN
   RAISERROR 50000 'SQL Transaction error STEP 2 Aborting...'
   ROLLBACK TRANSACTION Step1
  GOTO ENDOFBATCH
END
COMMIT TRANSACTION -- step2

BEGIN TRANSACTION Step3
  --CREATE TEMP TABLE

  CREATE TABLE INVENTORY (
--FIELDS
	)
  

IF @@ERROR <> 0 BEGIN
   RAISERROR 50000 'SQL Transaction error STEP 3 Aborting...'
   ROLLBACK TRANSACTION Step1
     GOTO ENDOFBATCH
END
COMMIT TRANSACTION -- step3

BEGIN TRANSACTION Step4


  INSERT	INVENTORY (
-- FIELD1,2,3,ETC
	)
  SELECT	PARTNO,
--FIELDS
  FROM	AMAINFRAMESYSTEM

IF @@ERROR <> 0 BEGIN
   RAISERROR 50000 'SQL Transaction error STEP 4 Aborting...'
   ROLLBACK TRANSACTION Step1
     GOTO ENDOFBATCH
END
COMMIT TRANSACTION -- step4

BEGIN TRANSACTION Step5
  --DELETE RECORDS THAT DO NOT PERTAIN TO PHYSICAL PARTS

    DELETE FROM INVENTORY
    WHERE	PROCUREMENT IN ('TEST')

IF @@ERROR <> 0 BEGIN
   RAISERROR 50000 'SQL Transaction error STEP 5 Aborting...'
   ROLLBACK TRANSACTION Step1
     GOTO ENDOFBATCH
END
COMMIT TRANSACTION -- step5

BEGIN TRANSACTION Step6
--ASSIGN MODEL BASED ON PROCUREMENT CODE

  UPDATE	INVDATA
  SET	MODEL = '737'
  WHERE	MDL_CD = 'A'

  UPDATE	INVDATA
  SET	MODEL = '737'
  WHERE	MDL_CD = 'B'

  UPDATE	INVDATA
  SET	MODEL = '747'
  WHERE	MDL_CD = 'C'

  UPDATE	INVDATA
  SET	MODEL = '757'
  WHERE	MDL_CD = 'D'

  UPDATE	INVDATA
  SET	MODEL = '767'
  WHERE	MDL_CD = 'E'

  UPDATE	INVDATA
  SET	MODEL = '777'
  WHERE	MDL_CD = 'F'

  UPDATE	INVENTORY
  SET	MODEL = 'OTHER'
  WHERE	MODEL IS NULL

IF @@ERROR <> 0 BEGIN
   RAISERROR 50000 'SQL Transaction error STEP 6 Aborting...'
   ROLLBACK TRANSACTION Step1
     GOTO ENDOFBATCH
END
COMMIT TRANSACTION -- step6

ENDOFBATCH:

--DROP ALL TABLES