For anybody else wanting to look at this I formatted it and put it in a code window so it is legible.

CREATE PROCEDURE [Spr_Insert_CR_Property_Seizure] (@XmlDoc NTEXT)

AS

BEGIN

DECLARE @iDoc INT

DECLARE @Unit_ID INT

DECLARE @user-id INT

DECLARE @Seize_ID AS INT

DECLARE @PD_ID AS INT

DECLARE @Witness_ID AS INT

DECLARE @MID AS INT

DECLARE @FIR_MID AS INT

DECLARE @FIR_ID AS INT

DECLARE @Person_No AS INT

------------------- From Hear the transaction will begin------------------

BEGIN TRANSACTION

EXEC sp_xml_preparedocument @iDoc OUTPUT

,@XmlDoc

EXEC spr_GetIDValues @XmlDoc

,@Unit_ID OUTPUT

,@UserID OUTPUT

EXEC GenerateID 'tbl_CR_Property_Seizure_Form'

,@Seize_ID OUTPUT

,@Unit_ID

--FECHING ROWID'S FOR FETCHING

SELECT @FIR_ID = Fir_ID

FROM OPENXML(@iDoc, '/tbl_CR_Property_Seizure_Form', 1) WITH (Fir_ID INT)

DECLARE @Witness_temp TABLE (RowId UNIQUEIDENTIFIER)

INSERT @Witness_temp

SELECT RowID

FROM openxml(@iDoc, '/tbl_CR_Property_Seizure_Form/tbl_CR_witness_Details', 1) WITH (RowID UNIQUEIDENTIFIER)

SELECT PropertyType

,RowID

INTO #temp_prop

FROM OPENXML(@iDoc, '/tbl_CR_Property_Seizure_Form/tbl_CR_Property_Details', 1) WITH (

PropertyType INT

,RowID UNIQUEIDENTIFIER

)

SELECT RowID

INTO #person_temp

FROM OPENXML(@iDoc, '/tbl_CR_Property_Seizure_Form/tbl_CR_Property_Person_Details', 1) WITH (RowID UNIQUEIDENTIFIER)

SELECT @Person_No = max(person_no) + 1

FROM tbl_cr_witness_details

WHERE unit_id = @Unit_ID

AND fir_id = @FIR_ID

--Added as per the new functionailty in other sezuire.

IF (@Person_No IS NULL)

BEGIN

SELECT @Person_No = Person_No

FROM OPENXML(@iDoc, '/tbl_CR_Property_Seizure_Form/tbl_CR_witness_Details', 1) WITH (Person_No INT)

END

INSERT INTO tbl_CR_Person_Act_Section (

Unit_ID

,FIR_ID

,Crime_No

,Act_Code

,Section_Code

,RowID

,UserID

,Form_ID

,Type_ID

,Slno

)

SELECT Unit_ID

,FIR_ID

,Crime_No

,ActCode

,SectionCode

,RowID

,UserID

,Form_ID = 2

,Type_ID = @Seize_ID

,SlNo

FROM OPENXML(@iDoc, '/tbl_CR_Property_Seizure_Form/tbl_CR_Person_Act_Section', 1) WITH (

Unit_ID INT

,FIR_ID INT

,Crime_No VARCHAR(20)

,ActCode INT

,SectionCode INT

,Form_ID INT

,RowID UNIQUEIDENTIFIER

,UserID INT

,Mode VARCHAR(3)

,SlNo INT

) xtActSec

WHERE xtActSec.Mode = 'I'

IF @@error <> 0

BEGIN

EXEC sp_xml_removedocument @iDoc

ROLLBACK TRANSACTION

RETURN 0

END

-- END OF PERSON ACT SECTION

--- INSERION INTO PROPERY SEIZURE FORM

INSERT INTO tbl_CR_Property_Seizure_Form (

Seize_ID

,Unit_ID

,Form_ID

,Type

,FIR_ID

,PF_No

,Seizer_Recover_No

,TypeOf_Property

,Property_Status

,Seized_Date_Time

,Seized_Place_Desc

,Seized_By

,Address1

,Address2

,City

,STATE

,Pincode

,Ident_Req

,Seizure_Circumstance

,Disp_Perish_Property

,Keep_Value_Property

,Magistrate_Order

,Remarks

,Magistrate_Date

,RowID

,UserID

,perish_Prop_Copy

,value_Prop_Copy

,Circum_Ground_Copy

,NameandSign_Copy

,SeizedFromClandestine

,IsClandestineDismantled

,DismantlingDate

,GD_No

,DismantlingPlace

,Drugs_Seized

,Drugs_Seized_Qty

,Chemical_Seized

,Chemical_Seized_Qty

,Seized_Equipments

,DismantlingRemarks

,IOCode

,IOName

)

--,perish_Prop_Copy_Blob

--,value_Prop_Copy_Blob

--,Circum_Ground_Copy_Blob

--,NameandSign_Copy_Blob

SELECT Seize_ID = @Seize_ID

,Unit_ID

,Form_ID = 3

,Type

,Fir_ID

,PF_No

,Seizer_Recover_No

,Type_Of_Property

,Property_Status

,kpuser.XmlToDateTime(Seized_Date_Time)

,Seized_Place_Desc

,Seized_By

,Address1

,Address2

,City

,STATE

,PinCode

,Ident_Req

,Seizure_Circumstance

,Dis_Perish_Property

,Keep_Value_Property

,Magistrate_Order

,Remarks

,kpuser.XmlToDateTime(Magistrate_Date)

,RowID

,UserID

,Persish_Prop_Copy

,Value_Prop_Copy

,Circum_Ground_Copy

,NameandSign_Copy

,SeizedFromClandestine

,IsClandestineDismantled

,kpuser.XmlToDateTime(DismantlingDate)

,GD_No

,DismantlingPlace

,Drugs_Seized

,Drugs_Seized_Qty

,Chemical_Seized

,Chemical_Seized_Qty

,Seized_Equipments

,DismantlingRemarks

,(

SELECT TOP 1 Current_sho

FROM tbl_lo_sho_departure_arrival

WHERE Unit_Id = @Unit_ID

ORDER BY charge_date DESC

)

,(

SELECT First_Name + ' ' + Middle_Name + ' ' + Last_Name + ' ' + CHAR(13) + '(' + (

SELECT Designation_Name

FROM tbl_Designation_master DM

WHERE DM.Designation_ID = Designation

) + ')'

FROM tbl_Employee_Master

WHERE Employee_Id = (

SELECT TOP 1 Current_sho

FROM tbl_lo_sho_departure_arrival

WHERE Unit_Id = @Unit_ID

ORDER BY charge_date DESC

)

)

--,File_Binary1

--,File_Binary2

--,File_Binary3

--,File_Binary4

FROM OPENXML(@iDoc, '/tbl_CR_Property_Seizure_Form', 1) WITH (

Unit_ID INT

,Fir_ID INT

,Type TINYINT

,PF_No VARCHAR(50)

,Seizer_Recover_No VARCHAR(50)

,Type_Of_Property VARCHAR(5)

,Property_Status TINYINT

,Seized_Date_Time VARCHAR(50)

,Seized_Place_Desc NTEXT

,Seized_By VARCHAR(100)

,Address1 VARCHAR(200)

,Address2 VARCHAR(200)

,City INT

,STATE INT

,PinCode INT

,Ident_Req VARCHAR(10)

,Seizure_Circumstance NVARCHAR(1000)

,Dis_Perish_Property NTEXT

,Keep_Value_Property NTEXT

,Magistrate_Order NVARCHAR(1000)

,Remarks NVARCHAR(200)

,Magistrate_Date VARCHAR(50)

,RowID UNIQUEIDENTIFIER

,UserID VARCHAR(10)

,Persish_Prop_Copy VARCHAR(200)

,Value_Prop_Copy VARCHAR(200)

,Circum_Ground_Copy VARCHAR(200)

,NameandSign_Copy VARCHAR(200)

,SeizedFromClandestine INT

,IsClandestineDismantled INT

,DismantlingDate VARCHAR(50)

,GD_No VARCHAR(50)

,DismantlingPlace VARCHAR(50)

,Drugs_Seized VARCHAR(100)

,Drugs_Seized_Qty INT

,Chemical_Seized VARCHAR(100)

,Chemical_Seized_Qty INT

,Seized_Equipments VARCHAR(100)

,DismantlingRemarks VARCHAR(500)

--,File_Binary1 VARBINARY(max)

--,File_Binary2 VARBINARY(max)

--,File_Binary3 VARBINARY(max)

--,File_Binary4 VARBINARY(max)

) xtPropSeizure

IF @@error <> 0

BEGIN

EXEC sp_xml_removedocument @iDoc

ROLLBACK TRANSACTION

RETURN 0

END

--END OF INSERTION

----below adde by anil for upload confess enclosures

SELECT

--RowID,

Unit_ID

,

--ParentRowID,

PF_No

,FIR_ID

INTO #temp_table

FROM OPENXML(@idoc, '/tbl_CR_Property_Seizure_Form/tbl_CR_Person_Confess_Enclosures', 1) WITH (

--RowID uniqueidentifier,

Unit_ID INT '/tbl_CR_Property_Seizure_Form/@Unit_ID'

,

--ParentRowID varchar(100),

FIR_ID INT

,PF_No VARCHAR(50)

)

IF @@rowcount <> 0

BEGIN

--DELETE

--FROM tbl_CR_Person_Confess_Enclosures

--WHERE Unit_ID IN (

-- SELECT Unit_ID

-- FROM #temp_table

-- )

-- AND Fir_ID IN (

-- SELECT FIR_ID

-- FROM #temp_table

-- )

-- AND PF_No IN (

-- SELECT PF_No

-- FROM #temp_table

-- )

DELETE pce

FROM tbl_CR_Person_Confess_Enclosures pce

INNER JOIN #temp_table t ON pce.unit_id = t.unit_id

AND pce.fir_id = t.fir_id

AND pce.pf_no = t.pf_no

DROP TABLE #temp_table

END

IF @@error <> 0

BEGIN

EXEC sp_xml_removedocument @idoc

ROLLBACK TRANSACTION

RETURN 0

END

INSERT INTO tbl_CR_Person_Confess_Enclosures (

RowID

,Unit_ID

,ParentRowID

,UserID

,Fir_ID

,PF_No

,Enclosures

,Seize_ID

,File_Binary

)

SELECT RowID

,Unit_ID

,ParentRowID

,UserID = @user-id

,Fir_ID

,PF_No

,Enclosures

,Seize_ID = @Seize_ID

,File_Binary

FROM OPENXML(@idoc, '/tbl_CR_Property_Seizure_Form/tbl_CR_Person_Confess_Enclosures', 1) WITH (

RowID UNIQUEIDENTIFIER

,Unit_ID INT '/tbl_CR_Property_Seizure_Form/@Unit_ID'

,ParentRowID VARCHAR(50)

,--uniqueidentifier '/tbl_CR_Property_Seizure_Form/@RowID',

UserID INT

,Fir_ID INT '/tbl_CR_Property_Seizure_Form/@Fir_ID'

,PF_No VARCHAR(50)

,Enclosures VARCHAR(500)

,Seize_ID INT

,File_Binary VARBINARY(max)

)

IF @@error <> 0

BEGIN

EXEC sp_xml_removedocument @idoc

ROLLBACK TRANSACTION

RETURN 0

END

-- INSERTION INTO WItNESS DETIALS

DECLARE @Witness_rowID AS UNIQUEIDENTIFIER

DECLARE @isStockWitness AS INT

DECLARE Witness_Details CURSOR

FOR

SELECT RowID

FROM @witness_temp

GROUP BY RowID

OPEN Witness_Details

FETCH NEXT

FROM Witness_Details

INTO @Witness_rowID

WHILE @@fetch_status = 0

BEGIN

EXEC GenerateID 'tbl_CR_witness_Details'

,@Witness_ID OUTPUT

,@Unit_ID

SELECT @isStockWitness = IsStockWitness

FROM OPENXML(@iDoc, '/tbl_CR_Property_Seizure_Form/tbl_CR_witness_Details', 1) WITH (IsStockWitness INT)

INSERT INTO tbl_CR_Witness_Details (

Witness_ID

,Unit_ID

,FIR_ID

,Person_No

,[Name]

,Relation_name

,Relation

,Witness_Classification

,Eye_Witness

,EvidenceTendered

,Witness_type

,Age

,DOB

,Occupation_ID

,Religion

,Cast_ID

,Sex

,Nationality_ID

,Address1

,Address2

,City_ID

,State_ID

,Pin_Code

,Phone

,Fax

,Email

,Record_Date

,Recording_Officer

,Officer_Rank

,Statement

,Other_Detail

,UserID

,Otherwitnessstatement

,Seizure_No

,Recording_Officer_Others

,RowID

,Witness_Statement

,IsStockWitness

)

SELECT Witness_ID = @Witness_ID

,Unit_Id

,FIRID

,Person_No = @Person_No

,[Name]

,Relation_name

,Relation

,Witness_Classification

,Eye_Witness

,EvidenceTendered

,Witness_type

,Age

,kpuser.XmlToDateTime(DOB)

,Occupation_ID

,Religion

,Cast_ID

,Sex

,Nationality_ID

,Address1

,Address2

,City_ID

,State_ID

,Pin_Code

,Phone

,Fax

,Email

,kpuser.XmlToDateTime(Record_Date)

,Recording_Officer

,Officer_Rank

,Statement

,Other_Detail

,UserID

,Otherwitnessstatement

,Seizure_No = @Seize_ID

,Recording_Officer_Others

,RowID

,Witness_Statement

,IsStockWitness = @isStockWitness

FROM OPENXML(@iDoc, '/tbl_CR_Property_Seizure_Form/tbl_CR_witness_Details', 1) WITH (

Unit_Id INT

,FIRID INT

,Person_No INT

,[Name] VARCHAR(100)

,Relation_name VARCHAR(50)

,Relation INT

,Witness_Classification INT

,Eye_Witness INT

,EvidenceTendered VARCHAR(100)

,Witness_type TINYINT

,Age INT

,DOB VARCHAR(50)

,Occupation_ID INT

,Religion INT

,Cast_ID INT

,Sex INT

,Nationality_ID INT

,Address1 VARCHAR(200)

,Address2 VARCHAR(200)

,City_ID INT

,State_ID INT

,Pin_Code VARCHAR(10)

,Phone VARCHAR(10)

,Fax VARCHAR(10)

,Email VARCHAR(50)

,Record_Date VARCHAR(50)

,Recording_Officer INT

,Officer_Rank INT

,Statement NTEXT

,Other_Detail NVARCHAR(1000)

,UserID INT

,Otherwitnessstatement VARCHAR(500)

,Recording_Officer_Others VARCHAR(50)

,RowID UNIQUEIDENTIFIER

,Witness_Statement INT

,Mode VARCHAR(1)

) xt

WHERE xt.RowID = @Witness_rowID

AND xt.Mode = 'I'

--Added by kareem

UPDATE tbl_CR_Witness_Details

SET Seizure_No = @Seize_ID

FROM OPENXML(@iDoc, '/tbl_CR_Property_Seizure_Form/tbl_CR_witness_Details', 1) WITH (

RowID UNIQUEIDENTIFIER

,Mode VARCHAR(1)

) xt

WHERE xt.RowID = tbl_CR_Witness_Details.Rowid

AND xt.Mode = 'O'

-- --Stockwitness

--Email Sending

--declare @isStockWitness as int

DECLARE @Crime_NO VARCHAR(50)

DECLARE @Unit_Name VARCHAR(20)

DECLARE @AddlText VARCHAR(max)

DECLARE @SHOEmail VARCHAR(200)

DECLARE @WitnessName VARCHAR(50)

DECLARE @FirType INT

SELECT @SHOEmail = (

SELECT Email

FROM tbl_unit_master

WHERE unit_id = @Unit_ID

)

SELECT @Crime_NO = crime_no

FROM tbl_cr_fir_details

WHERE unit_id = @Unit_ID

AND Fir_id = @FIR_ID --@fir_id

SELECT @Unit_Name = unit_name

FROM tbl_unit_master

WHERE unit_id = @Unit_ID

SET @FirType = (

SELECT FIR_Entry_Type

FROM tbl_cr_fir_details

WHERE FIR_ID = @FIR_ID

AND Unit_Id = @Unit_ID

)

--select

-- @isStockWitness=IsStockWitness

-- FROM OPENXML(@iDoc,'/tbl_CR_Property_Seizure_Form/tbl_CR_witness_Details',1)

-- WITH

-- (

-- IsStockWitness int

-- )

SELECT @WitnessName = NAME

FROM OPENXML(@iDoc, '/tbl_CR_Property_Seizure_Form/tbl_CR_witness_Details', 1) WITH (NAME VARCHAR(50))

SET @AddlText = 'Witness=' + @WitnessName + ',' + 'Type=' + cast(@FirType AS VARCHAR(3))

IF (@isStockWitness = 1)

BEGIN

INSERT INTO tbl_Email_Request (

TemplateCode

,EmailAddress

,CreatedDate

,CreatedBy

,IsEmailSent

,IsEmailDelivered

,FIRNumber

,UnitId

,PSName

,AddlText

,ActsSections

)

VALUES (

'E115'

,@SHOEmail

,getdate()

,@UserID

,0

,0

,(

SELECT Substring(@Crime_NO, 14, 4) + '/' + Substring(@Crime_NO, 10, 4)

)

,@unit_id

,@Unit_Name

,@AddlText

,''

)

END

-- --Stockwitness

IF @@error <> 0

BEGIN

EXEC sp_xml_removedocument @iDoc

ROLLBACK TRANSACTION

RETURN 0

END

FETCH NEXT

FROM Witness_Details

INTO @Witness_rowID

END

CLOSE Witness_Details

DEALLOCATE Witness_Details

---END OF INSERION

---INSERION OF PERSON PROPERY

INSERT INTO tbl_CR_Property_Person_Details (

Seize_ID

,SL_No

,FIR_ID

,Person_Name

,Relation_Name

,Relation

,Age

,DOB

,OccupationID

,Sex

,Address1

,Address2

,CityID

,StateID

,PinCode

,Professional_Receiver

,Unit_ID

,RowID

,UserID

,[Months]

,[Days]

)

SELECT Seize_ID = @Seize_ID

,SL_No

,FIR_ID

,Person_Name

,Relation_Name

,Relation

,Age

,kpuser.XmlToDateTime(DOB)

,OccupationID

,Sex

,Address1

,Address2

,CityID

,StateID

,PinCode

,Professional_Receiver

,Unit_ID

,RowID

,UserID

,[Month]

,[Day]

FROM OPENXML(@iDoc, '/tbl_CR_Property_Seizure_Form/tbl_CR_Property_Person_Details', 1) WITH (

SL_No INT

,FIR_ID INT

,Person_Name VARCHAR(50)

,Relation_Name VARCHAR(50)

,Relation INT

,Age INT

,DOB VARCHAR(50)

,OccupationID INT

,Sex CHAR

,Address1 VARCHAR(200)

,Address2 VARCHAR(200)

,CityID INT

,StateID INT

,PinCode INT

,Professional_Receiver CHAR

,Unit_ID INT

,RowID UNIQUEIDENTIFIER

,UserID INT

,[Month] INT

,[Day] INT

) xt

IF @@error <> 0

BEGIN

EXEC sp_xml_removedocument @iDoc

ROLLBACK TRANSACTION

RETURN 0

END

--END OF INSERTION

----INSERTION OF Property_Details

DECLARE @rowID AS UNIQUEIDENTIFIER

DECLARE Prop_Details CURSOR

FOR

SELECT RowID

FROM #temp_prop

GROUP BY RowID

OPEN Prop_Details

FETCH NEXT

FROM Prop_Details

INTO @rowID

WHILE @@fetch_status = 0

BEGIN

EXEC GenerateID 'tbl_CR_Property_Details'

,@PD_ID OUTPUT

,@Unit_ID

INSERT INTO tbl_CR_Property_Details (

Unit_ID

,PD_ID

,MID

,Item_Desc

,Seize_ID

,FIR_ID

,PropertyType

,Sl_No

,Major_Type

,Type

,Property_Status

,Estimated_value

,RowID

,UserID

)

SELECT Unit_ID

,PD_ID = @PD_ID

,MID

,Item_Desc

,Seize_ID = @Seize_ID

,FIR_ID

,PropertyType

,Sl_No

,Major_Type

,Property_Status

,Property_Status

,Estimated_value

,RowID

,UserID

FROM OPENXML(@iDoc, '/tbl_CR_Property_Seizure_Form/tbl_CR_Property_Details', 1) WITH (

Unit_ID INT

,MID INT

,Item_Desc VARCHAR(500)

,---changed by Triveni from size 50 to 500

FIR_ID INT

,PropertyType TINYINT

,Sl_No INT

,Major_Type INT

,Property_Status INT

,Estimated_value INT

,RowID UNIQUEIDENTIFIER

,UserID INT

)

WHERE RowID = @rowID

IF @@error <> 0

BEGIN

EXEC sp_xml_removedocument @iDoc

ROLLBACK TRANSACTION

RETURN 0

END

/*INSERT INTO tbl_CR_Property_Movement

(

PD_ID,

Seize_ID,

Unit_ID,

Property_Type,

Action_date,

Action_TYpe,

UserID,

RowID

)

Select

PD_ID=@PD_ID,

Seize_ID=@Seize_ID,

Unit_ID,

PropertyType as Property_Type,

Action_date=getdate(),

Action_TYpe=9,

UserID=@UserID,

newid() as RowID

FROM OPENXML (@iDoc, '/tbl_CR_Property_Seizure_Form/Magistrate',1)

WITH

(

Unit_ID int,

MID int,

Item_Desc varchar(50),

FIR_ID int,

PropertyType int,

Type int,

Sl_No int,

Major_Type int,

Estimated_value int,

UserID int,

RowID uniqueidentifier,Send_To_Court int

)xt

where xt.RowID=@rowID and Send_To_Court=1

IF @@ERROR <> 0

BEGIN

EXEC sp_xml_removedocument @idoc

ROLLBACK TRANSACTION

RETURN 0

END */

DECLARE @PropertyType AS INT

SELECT @PropertyType = PropertyType

FROM #temp_prop

WHERE RowID = @rowID

IF @PropertyType = 5

OR @PropertyType = 6

OR @PropertyType = 7

BEGIN

---- ISNERTING VALUES INTO TABLE tbl_cr_Arms_NU_Prop

INSERT INTO tbl_CR_Arms_NU_Prop (

Unit_ID

,PD_ID

,Quantity

,Unit

,Make

,Model

,Manufacturer_No

,Country_of_Origin

,Special_Marks

,Belongs_To

,Insurance_Company

,Insurance_Cert_no

,RowID

,UserID

,Property_Type

,Licensed

)

SELECT Unit_ID

,PD_ID = @PD_ID

,Quantity

,Unit

,Make

,Model

,Manufacturer_No

,Country_of_Origin

,Special_Marks

,Belongs_To

,Insurance_Company

,Insurance_Cert_no

,RowID

,UserID

,Property_Type

,Licensed

FROM OPENXML(@iDoc, '/tbl_CR_Property_Seizure_Form/tbl_CR_Arms_NU_Prop', 1) WITH (

Unit_ID INT

,Quantity VARCHAR(50)

,Unit INT

,Make VARCHAR(100)

,Model VARCHAR(100)

,Manufacturer_No VARCHAR(50)

,Country_of_Origin INT

,Special_Marks VARCHAR(100)

,Belongs_To INT

,Insurance_Company VARCHAR(100)

,Insurance_Cert_no VARCHAR(100)

,RowID UNIQUEIDENTIFIER

,UserID INT

,Property_Type INT

,Licensed TINYINT

)

WHERE RowID = @rowID

DROP TABLE #temp_prop

IF @@error <> 0

BEGIN

EXEC sp_xml_removedocument @iDoc

ROLLBACK TRANSACTION

RETURN 0

END

END

IF @PropertyType = 1

----------------Email -------------------------Added by Amar--

BEGIN

DECLARE @Registration_No VARCHAR(50)

DECLARE @Chassis_No VARCHAR(50)

DECLARE @Engine_No VARCHAR(50)

DECLARE @make VARCHAR(100)

DECLARE @Model VARCHAR(100)

DECLARE @Property_Category INT

DECLARE @FIRNumber VARCHAR(20)

DECLARE @UnitName VARCHAR(50)

DECLARE @Vehiclecount INT

DECLARE @FIRUnit_ID INT

DECLARE @sho VARCHAR(50)

DECLARE @FirSHO VARCHAR(50)

DECLARE @SHOList VARCHAR(100)

SELECT @make = Make

,@Model = Model

,@Registration_No = Registration_No

,@Chassis_No = Chassis_No

,@Engine_No = Engine_No

,@Property_Category = Property_Category

,@rowID = RowID

FROM OPENXML(@iDoc, '/tbl_CR_Property_Seizure_Form/tbl_CR_Automobile_Prop', 1) WITH (

Make VARCHAR(100)

,Model VARCHAR(100)

,Registration_No VARCHAR(50)

,Chassis_No VARCHAR(50)

,Engine_No VARCHAR(50)

,Property_Category INT

,RowID UNIQUEIDENTIFIER

)

WHERE RowID = @rowID

BEGIN

SELECT @Registration_No = ap.Registration_No

,@FIRNumber = pd.FIR_ID

,@FIRUnit_ID = pd.Unit_id

FROM tbl_CR_Property_Details pd

INNER JOIN tbl_CR_Automobile_Prop ap ON pd.Rowid = ap.rowid

AND pd.unit_id = ap.unit_id

INNER JOIN tbl_CR_FIR_DETAILS fd ON fd.FIR_ID = pd.FIR_ID

AND fd.unit_id = pd.unit_id

WHERE pd.propertytype = 1

AND [kpuser].[fn_Remove_Special_Chars](ap.Registration_No) = [kpuser].[fn_Remove_Special_Chars](@Registration_No)

AND ap.make = @make

AND fd.Draft = 'NO'

ORDER BY pd.DEDT DESC

SELECT @UnitName = unit_name

FROM tbl_unit_master

WHERE unit_id = @Unit_ID

IF @Registration_No <> ''

BEGIN

SELECT @sho = (

(

SELECT isnull((

SELECT TOP 1 Emp.Email_ID

FROM tbl_Employee_Master Emp

WHERE Emp.WorkPlace_UnitID = @Unit_ID

AND Rank_ID = 8

), (

SELECT TOP 1 Emp.First_Name

FROM tbl_Employee_Master Emp

WHERE Emp.WorkPlace_UnitID = @Unit_ID

AND Rank_ID = 9

)) AS SHO

)

)

SELECT @FirSHO = (

(

SELECT isnull((

SELECT TOP 1 Emp.Email_ID

FROM tbl_Employee_Master Emp

WHERE Emp.WorkPlace_UnitID = @FIRUnit_ID

AND Rank_ID = 8

), (

SELECT TOP 1 Emp.First_Name

FROM tbl_Employee_Master Emp

WHERE Emp.WorkPlace_UnitID = @FIRUnit_ID

AND Rank_ID = 9

)) AS SHO

)

)

SET @SHOList = @sho + ',' + @FirSHO

INSERT INTO tbl_Email_Request (

TemplateCode

,EmailAddress

,CreatedDate

,CreatedBy

,IsEmailSent

,IsEmailDelivered

,FIRNumber

,UnitId

,PSName

,AddlText

)

VALUES (

'E118'

,@SHOList

,getdate()

,@UserID

,0

,0

,@FIRNumber

,@Unit_ID

,@UnitName

,@Registration_No

)

END

END

END

-------------------end email-------------------------------------------------

BEGIN

---- INSERTING VALUES INTO Automobiles tbl_CR_Automobile_Prop

---vin

INSERT INTO tbl_CR_Automobile_Prop (

Unit_ID

,PD_ID

,Make

,Model

,Color

,Color_Peculiarity

,Registration_No

,Chassis_No

,Engine_No

,Belongs_To

,Insurance_Cert_No

,Insurance_Company

,RowID

,UserID

,Property_Category

)

SELECT Unit_ID

,PD_ID = @PD_ID

,Make

,Model

,Color

,Color_Peculiarity

,Registration_No

,Chassis_No

,Engine_No

,Belongs_To

,Insurance_Cert_No

,Insurance_Company

,RowID

,UserID

,Property_Category

FROM OPENXML(@iDoc, '/tbl_CR_Property_Seizure_Form/tbl_CR_Automobile_Prop', 1) WITH (

Unit_ID INT

,Make VARCHAR(100)

,Model VARCHAR(100)

,Color INT

,Color_Peculiarity VARCHAR(50)

,Registration_No VARCHAR(50)

,Chassis_No VARCHAR(50)

,Engine_No VARCHAR(50)

,Belongs_To INT

,Insurance_Cert_No VARCHAR(50)

,Insurance_Company VARCHAR(50)

,RowID UNIQUEIDENTIFIER

,UserID INT

,Property_Category INT

)

WHERE RowID = @rowID

IF @@error <> 0

BEGIN

EXEC sp_xml_removedocument @iDoc

ROLLBACK TRANSACTION

RETURN 0

END

END

IF @PropertyType = 2

BEGIN

----INSERTING VALUESINTO tbl_CR_Currency_Prop

INSERT INTO tbl_CR_Currency_Prop (

Unit_ID

,PD_ID

,Series

,From_Serial_No

,Denomination

,Number_of_Pieces

,Identification_Marks

,Currency_Design

,Security_Thread

,Florosent_Present

,RowID

,UserID

)

SELECT Unit_ID

,PD_ID = @PD_ID

,Series

,From_Serial_No

,Denomination

,Number_Of_Pieces

,Identification_Marks

,Currency_Design

,Security_Thread

,Floresent_Present

,RowID

,UserID

FROM OPENXML(@iDoc, '/tbl_CR_Property_Seizure_Form/tbl_CR_Currency_Prop', 1) WITH (

Unit_ID INT

,Series VARCHAR(50)

,From_Serial_No VARCHAR(50)

,Denomination INT

,Number_Of_Pieces INT

,Identification_Marks VARCHAR(500)

,Currency_Design TINYINT

,Security_Thread TINYINT

,Floresent_Present TINYINT

,RowID UNIQUEIDENTIFIER

,UserID INT

)

WHERE RowID = @rowID

IF @@error <> 0

BEGIN

EXEC sp_xml_removedocument @iDoc

ROLLBACK TRANSACTION

RETURN 0

END

END

IF @PropertyType = 3

BEGIN

---INSERT VALUE INTO tbl_CR_Cultural_Prop

INSERT INTO tbl_CR_Cultural_Prop (

Unit_ID

,PD_ID

,Quantity

,Material_Used

,Nomenclature

,[AD/BC]

,Age

,Height

,Height_text

,Breadth

,Breadth_Text

,Depth

,Depth_text

,Weight

,Weight_Text

,ASI_cert_No

,Insurance_Company

,[Insurance_Cert_no.]

,Photo_Taken

,RowID

,UserID

)

SELECT Unit_ID

,PD_ID = @PD_ID

,Quantity

,Material_Used

,Nomenclature

,ADBC

,Age

,Height

,Height_text

,Breadth

,Breadth_Text

,Depth

,Depth_text

,Weight

,Weight_Text

,ASI_cert_No

,Insurance_Company

,Insurance_Cert_no

,Photo_Taken

,RowID

,UserID

FROM OPENXML(@iDoc, '/tbl_CR_Property_Seizure_Form/tbl_CR_Cultural_Prop', 1) WITH (

Unit_ID INT

,Quantity INT

,Material_Used INT

,Nomenclature INT

,ADBC TINYINT

,Age INT

,Height INT

,Height_text VARCHAR(50)

,Breadth INT

,Breadth_Text VARCHAR(50)

,Depth INT

,Depth_text VARCHAR(50)

,Weight INT

,Weight_Text VARCHAR(50)

,ASI_cert_No VARCHAR(50)

,Insurance_Company VARCHAR(50)

,Insurance_Cert_no VARCHAR(50)

,Photo_Taken INT

,RowID UNIQUEIDENTIFIER

,UserID INT

)

WHERE RowID = @rowID

INSERT INTO tbl_CR_Cult_Prop_Spl_Features (

Seizure_ID

,PD_ID

,Cult_Major_ID

,Cult_Minor_ID

,ParentRowID

,RowID

,Unit_ID

,UserID

)

SELECT Seizure_ID = @Seize_ID

,PD_ID = @PD_ID

,Cult_Major_ID

,Cult_Minor_ID

,ParentRowID

,RowID

,Unit_ID

,UserID

FROM OPENXML(@iDoc, '/tbl_CR_Property_Seizure_Form/tbl_CR_Cultural_Spl_Prop', 1) WITH (

Cult_Major_ID INT

,Cult_Minor_ID INT

,ParentRowID UNIQUEIDENTIFIER

,RowID UNIQUEIDENTIFIER

,Unit_ID INT

,UserID INT

) xt

WHERE xt.ParentRowID = @rowID

IF @@error <> 0

BEGIN

EXEC sp_xml_removedocument @iDoc

ROLLBACK TRANSACTION

RETURN 0

END

END

IF @PropertyType = 4

BEGIN

---- INSERTING VALUES into tbl_CR_Drug_Prop

INSERT INTO tbl_CR_Drugs_Prop (

Unit_ID

,PD_ID

,Quantity

,UOM_Desc

,Conv_Factor

,Conv_UOM_Desc

,Drug_Weight

,Method_Desc

,Drug_Meant_For

,SeizedClandestine

,DrugSource

,DrugDest

,Drug_Remarks

,Drug_Area

,Drug_Hectar

,Drug_N0_Of_Fields

,Drug_PotentialYields

,RowID

,UserID

)

SELECT UnitID

,PD_ID = @PD_ID

,Quantity

,UOM_Desc

,Conv_Factor

,Conv_UOM_Desc

,Weight

,Method_Desc

,Drug_Meant_For

,Clandestine

,Drug_Source

,Drug_Dest

,Drug_Remarks

,Drug_Area

,Hectars

,No_Of_Plants

,Potential_Yields

,RowID

,UserID

FROM OPENXML(@iDoc, '/tbl_CR_Property_Seizure_Form/tbl_CR_Drugs_Prop', 1) WITH (

UnitID INT

,Quantity VARCHAR(50)

,UOM_Desc INT

,Conv_Factor INT

,Conv_UOM_Desc INT

,Weight INT

,Drug_Meant_For TINYINT

,Clandestine TINYINT

,Drug_Source INT

,Drug_Dest INT

,Drug_Remarks VARCHAR(1000)

,Drug_Area VARCHAR(500)

,Hectars VARCHAR(50)

,No_Of_Plants VARCHAR(50)

,Potential_Yields VARCHAR(50)

,Method_Desc INT

,RowID UNIQUEIDENTIFIER

,UserID INT

)

WHERE RowID = @rowID

IF @@error <> 0

BEGIN

EXEC sp_xml_removedocument @iDoc

ROLLBACK TRANSACTION

RETURN 0

END

END

FETCH NEXT

FROM Prop_Details

INTO @rowID

END

CLOSE Prop_Details

DEALLOCATE Prop_Details

--Attached Proeprty

DECLARE @P_MID AS INT

DECLARE @PersonAct_rowID AS UNIQUEIDENTIFIER

SELECT RowID

,PD_ID

,PropertyType

INTO #Attached_Property

FROM openxml(@iDoc, '/tbl_CR_Property_Seizure_Form/AttachedProperty', 1) WITH (

PropertyType INT

,RowID UNIQUEIDENTIFIER

,PD_ID INT

)

DECLARE @OldPDID AS INT

DECLARE @PType AS INT

DECLARE @Attached_rowID AS UNIQUEIDENTIFIER

DECLARE AttachedProp_Details CURSOR

FOR

SELECT RowID

,PD_ID

,PropertyType

FROM #Attached_Property --order by RowID,PD_ID,PropertyType

UPDATE tbl_CR_Property_Details

SET tbl_CR_Property_Details.Att_Flag = 1

,tbl_CR_Property_Details.Prop_ID = #Attached_Property.PD_ID

--, tbl_CR_Property_Details.Property_Status=2

FROM #Attached_Property

WHERE tbl_CR_Property_Details.RowID = #Attached_Property.RowID

AND tbl_CR_Property_Details.PD_ID = #Attached_Property.PD_ID

OPEN AttachedProp_Details

FETCH NEXT

FROM AttachedProp_Details

INTO @Attached_rowID

,@OldPDID

,@PType

WHILE @@fetch_status = 0

BEGIN

EXEC GenerateID 'tbl_CR_Property_Details'

,@PD_ID OUTPUT

,@Unit_ID

INSERT INTO tbl_CR_Property_Details (

Unit_ID

,PD_ID

,MID

,Item_Desc

,Seize_ID

,FIR_ID

,PropertyType

,Sl_No

,Att_Flag

,Major_Type

,Type

,Property_Status

,Estimated_value

,UserID

,RowID

)

SELECT Unit_ID

,PD_ID = @PD_ID

,MID

,Item_Desc

,Seize_ID = @Seize_ID

,FIR_ID

,PropertyType

,Sl_No

,Att_Flag = 0

,Major_Type

,Type

,Property_Status = 2

,Estimated_value

,UserID

,RowID

FROM OPENXML(@iDoc, '/tbl_CR_Property_Seizure_Form/AttachedProperty', 1) WITH (

Unit_ID INT

,MID INT

,Item_Desc VARCHAR(500)

,---changed by Triveni from size 50 to 500

FIR_ID INT

,PropertyType INT

,Type INT

,Sl_No INT

,Major_Type INT

,Estimated_value INT

,UserID INT

,RowID UNIQUEIDENTIFIER

) xt

WHERE xt.RowID = @Attached_rowID

IF @@error <> 0

BEGIN

EXEC sp_xml_removedocument @iDoc

ROLLBACK TRANSACTION

RETURN 0

END

--Property Movement

/*INSERT INTO tbl_CR_Property_Movement

(

PD_ID,

Seize_ID,

Unit_ID,

Property_Type,

Action_date,

Action_TYpe,

UserID,

RowID

)

Select

PD_ID=@PD_ID,

Seize_ID=@Seize_ID,

Unit_ID,

PropertyType as Property_Type,

Action_date=getdate(),

Action_TYpe=9,

UserID=@UserID,

newid() as RowID

FROM OPENXML (@iDoc, '/tbl_CR_Property_Seizure_Form/Magistrate',1)

WITH

(

Unit_ID int,

MID int,

Item_Desc varchar(50),

FIR_ID int,

PropertyType int,

Type int,

Sl_No int,

Major_Type int,

Estimated_value int,

UserID int,

RowID uniqueidentifier,Send_To_Court int

)xt

where xt.RowID=@Attached_rowID and Send_To_Court=1

IF @@ERROR <> 0

BEGIN

EXEC sp_xml_removedocument @idoc

ROLLBACK TRANSACTION

RETURN 0

END */

--select model,* from tbl_CR_Automobile_Prop

IF @PType = 1

BEGIN

INSERT INTO tbl_CR_Automobile_Prop (

Unit_ID

,PD_ID

,Color

,Color_Peculiarity

,Registration_No

,Chassis_No

,Engine_No

,Belongs_To

,Insurance_Cert_No

,Insurance_Company

,RowID

,UserID

,Property_Category

,model

,make --- make is added by venkata Bushireddy

)

SELECT Unit_ID

,PD_ID = @PD_ID

,Color

,Color_Peculiarity

,Registration_No

,Chassis_No

,Engine_No

,Belongs_To

,Insurance_Cert_No

,Insurance_Company

,RowID

,UserID

,Property_Category

,model

,make --- Make is added by Venkata Bushireddy

FROM tbl_CR_Automobile_Prop

WHERE PD_ID = @OldPDID

AND Unit_ID = @Unit_ID

END

IF @PType = 2

BEGIN

INSERT INTO tbl_CR_Currency_Prop (

Unit_ID

,PD_ID

,Series

,From_Serial_No

,Denomination

,Number_of_Pieces

,Identification_Marks

,Currency_Design

,Security_Thread

,Florosent_Present

,RowID

,UserID

)

SELECT Unit_ID

,PD_ID = @PD_ID

,Series

,From_Serial_No

,Denomination

,Number_Of_Pieces

,Identification_Marks

,Currency_Design

,Security_Thread

,Florosent_Present

,RowID

,UserID

FROM tbl_CR_Currency_Prop

WHERE PD_ID = @OldPDID

AND Unit_ID = @Unit_ID

END

IF @PType = 3

BEGIN

INSERT INTO tbl_CR_Cultural_Prop (

Unit_ID

,PD_ID

,Quantity

,Material_Used

,Nomenclature

,[AD/BC]

,Age

,Height

,Height_text

,Breadth

,Breadth_Text

,Depth

,Depth_text

,Weight

,Weight_Text

,ASI_cert_No

,Insurance_Company

,[Insurance_Cert_no.]

,Photo_Taken

,RowID

,UserID

)

SELECT Unit_ID

,PD_ID = @PD_ID

,Quantity

,Material_Used

,Nomenclature

,[AD/BC]

,Age

,Height

,Height_text

,Breadth

,Breadth_Text

,Depth

,Depth_text

,Weight

,Weight_Text

,ASI_cert_No

,Insurance_Company

,[Insurance_Cert_no.]

,Photo_Taken

,RowID

,UserID

FROM tbl_CR_Cultural_Prop

WHERE PD_ID = @OldPDID

AND Unit_ID = @Unit_ID

END

IF @PType = 4

BEGIN

INSERT INTO tbl_CR_Drugs_Prop (

Unit_ID

,PD_ID

,Quantity

,UOM_Desc

,Conv_Factor

,Conv_UOM_Desc

,Drug_Weight

,Method_Desc

,Drug_Meant_For

,SeizedClandestine

,DrugSource

,DrugDest

,Drug_Remarks

,Drug_Area

,Drug_Hectar

,Drug_N0_Of_Fields

,Drug_PotentialYields

,RowID

,UserID

)

SELECT Unit_ID

,PD_ID = @PD_ID

,Quantity

,UOM_Desc

,Conv_Factor

,Conv_UOM_Desc

,Drug_Weight

,Method_Desc

,Drug_Meant_For

,SeizedClandestine

,DrugSource

,DrugDest

,Drug_Remarks

,Drug_Area

,Drug_Hectar

,Drug_N0_Of_Fields

,Drug_PotentialYields

,RowID

,USErID

FROM tbl_CR_Drugs_Prop

WHERE PD_ID = @OldPDID

AND Unit_ID = @Unit_ID

END

IF @PType = 5

OR @PType = 6

OR @PType = 7

BEGIN

INSERT INTO tbl_CR_Arms_NU_Prop (

Unit_ID

,PD_ID

,Quantity

,Unit

,Manufacturer_No

,Country_of_Origin

,Special_Marks

,Belongs_To

,Insurance_Company

,Insurance_Cert_no

,RowID

,UserID

,Property_Type

,Licensed

)

SELECT Unit_ID

,PD_ID = @PD_ID

,Quantity

,Unit

,Manufacturer_No

,Country_of_Origin

,Special_Marks

,Belongs_To

,Insurance_Company

,Insurance_Cert_no

,RowID

,UserID

,Property_Type

,Licensed

FROM tbl_CR_Arms_NU_Prop

WHERE PD_ID = @OldPDID

AND Unit_ID = @Unit_ID

END

IF @@error <> 0

BEGIN

EXEC sp_xml_removedocument @iDoc

ROLLBACK TRANSACTION

RETURN 0

END

FETCH NEXT

FROM AttachedProp_Details

INTO @Attached_rowID

,@OldPDID

,@PType

END

CLOSE AttachedProp_Details

DEALLOCATE AttachedProp_Details

UPDATE tbl_CR_Property_Details

SET Property_Status = 9

,Send_To_Court = 1

FROM OPENXML(@iDoc, '/tbl_CR_Property_Seizure_Form/Magistrate', 1) WITH (

RowID UNIQUEIDENTIFIER

,Send_To_Court INT

) xt

WHERE tbl_CR_Property_Details.RowID = xt.RowID

AND xt.Send_To_Court = 1

IF @@error <> 0

BEGIN

EXEC sp_xml_removedocument @iDoc

ROLLBACK TRANSACTION

RETURN 0

END

--below added by anil

DECLARE @Sl_no AS INT

DECLARE @NewSHDID VARCHAR(30)

DECLARE @Item_Desc VARCHAR(30)

EXEC GenerateID 'tbl_LO_SHD'

,@NewSHDID OUTPUT

,@Unit_ID

--EXEC GenerateID 'tbl_CR_Property_Seizure_Form', @Seize_ID OUTPUT ,@Unit_ID

DECLARE @Remarks NVARCHAR(4000)

--declare @Seize_ID int

--declare @unit_id int

--select @Item_Desc = Item_Desc

--FROM OPENXML (@iDoc, '/tbl_CR_Property_Seizure_Form/tbl_CR_Property_Details',1)

-- WITH

-- (

-- Item_Desc varchar(500)

-- )

--set @Item_Desc =(select Item_Desc FROM tbl_CR_Property_Details WHERE Pd_ID=@PD_ID and seize_id=@seize_id)

--set @Seize_ID =(select Item_Desc FROM tbl_CR_Property_Details prop WHERE prop.Seize_ID =@Seize_ID and prop.Unit_ID =@Unit_ID )

SET @Item_Desc = (

SELECT Item_Desc

FROM tbl_CR_Property_Details

WHERE Unit_ID = @Unit_ID

AND seize_id = @seize_id

AND PD_ID = @PD_ID

) --PD_ID=@PD_ID added by anil

SET @Remarks = (

'The Property ' + (@Item_Desc) + ' seized by ' + (

SELECT First_Name + ' ' + Middle_Name + ' ' + Last_Name + ' ' + CHAR(13) + '(' + (

SELECT Designation_Name

FROM tbl_Designation_master DM

WHERE DM.Designation_ID = Designation

) + ')'

FROM tbl_Employee_Master

WHERE Employee_Id = @userid

)

)

SET @Sl_no = (

SELECT isnull(max(sl_no), 0) + 1

FROM tbl_LO_SHD

WHERE convert(VARCHAR, Reporting_Date_Time, 103) = convert(VARCHAR, getdate(), 103)

AND Unit_ID = @Unit_ID

)

INSERT INTO tbl_LO_SHD (

SHD_ID

,Unit_Id

,Reporting_Date_Time

,Event_Type

,SHO

,Report_Substance

,Reference_No

,RowID

,UserID

,Sl_no

)

SELECT SHD_ID = @NewSHDID

,Unit_ID

,kpuser.xmltodatetime(Seized_Date_Time) AS Reporting_Date_Time

,Event_Type = 3905

,SHO = @user-id

,Report_Substance = @Remarks

,Seize_ID AS Reference_No

,RowID

,UserId = @user-id

,

--@Sl_no,

Sl_no = @Sl_no

FROM OPENXML(@iDoc, '/tbl_CR_Property_Seizure_Form', 1) WITH (

SHD_ID VARCHAR(30)

,Unit_ID INT

,Seized_Date_Time VARCHAR(50)

,SHO INT

,--'@SHOEmp_Id',

Report_Substance NVARCHAR(4000)

,Seize_ID INT

,RowID UNIQUEIDENTIFIER

,UserId INT

,--'@UserID' ,

Sl_no INT

)

IF @@error <> 0

BEGIN

EXEC sp_xml_removedocument @iDoc

ROLLBACK TRANSACTION

RETURN 0

END

UPDATE tbl_CR_Property_Seizure_Form

SET GD_No = @Sl_no

WHERE tbl_CR_Property_Seizure_Form.FIR_ID = @FIR_ID

AND tbl_CR_Property_Seizure_Form.Unit_ID = @Unit_ID

-- select * from tbl_CR_Property_Seizure_Form

--where tbl_CR_Property_Seizure_Form.FIR_ID=2014000005 and tbl_CR_Property_Seizure_Form.Unit_ID=49

COMMIT TRANSACTION

EXEC sp_xml_removedocument @iDoc

INSERT INTO tbl_CR_Application_Alert_details (

RowId

,Unit_ID

,FIR_ID

,AlertType

,Message

,From_Date

,To_Date

,UserId

)

SELECT Id

,unit.str

,FIR_Id

,AlertType

,Message

,From_Date

,To_Date

,em.Employee_ID

FROM (

SELECT NEWID() AS Id

,psf.Unit_ID AS Unit_Id

,psf.FIR_ID AS FIR_Id

,'Spirits seized' AS AlertType

,'Spirits seized at ' + dm.District_Name + ' exceeds 5000 litres' AS Message

,GETDATE() AS From_Date

,GETDATE() + 7 AS To_Date

FROM tbl_CR_Property_Seizure_Form psf

INNER JOIN tbl_CR_Property_Details pd ON (

psf.Seize_ID = pd.Seize_ID

AND pd.Unit_ID = psf.Unit_ID

)

INNER JOIN tbl_CR_Drugs_Prop dp ON (

pd.PD_ID = dp.PD_ID

AND pd.Unit_ID = dp.Unit_ID

)

INNER JOIN tbl_District_Master dm ON (dm.District_ID = psf.City)

INNER JOIN tbl_CR_FIR_Act_Sections firAct ON (

firAct.Unit_ID = psf.Unit_ID

AND firAct.FIR_ID = psf.FIR_ID

)

WHERE (

dp.Conv_UOM_Desc = 3762

AND (Quantity * Conv_Factor) >= 5000

)

AND Item_Desc IN (

'LIQUOR BOTTLE'

,'LIQUOR PACKETS'

)

AND firAct.Act_Code IN (

107

,206

,295

,335

,342

,350

,388

,438

,577

,658

,708

,731

,807

,1097

,1098

,1234

,1289

,1355

,1564

,1565

,1566

,1567

,1586

,1627

,1634

,1728

,1810

,2080

,2081

,2568

,2642

,2873

,3320

)

AND psf.Seize_ID = @Seize_ID

UNION

SELECT NEWID() AS Id

,psf.Unit_ID AS Unit_Id

,psf.FIR_ID AS FIR_Id

,'Spirits seized' AS AlertType

,'Spirits seized at ' + dm.District_Name + ' exceeds 5000 litres' AS Message

,GETDATE() AS From_Date

,GETDATE() + 7 AS To_Date

FROM tbl_CR_Property_Seizure_Form psf

INNER JOIN tbl_CR_Property_Details pd ON (

psf.Seize_ID = pd.Seize_ID

AND pd.Unit_ID = psf.Unit_ID

)

INNER JOIN tbl_CR_Drugs_Prop dp ON (

pd.PD_ID = dp.PD_ID

AND pd.Unit_ID = dp.Unit_ID

)

INNER JOIN tbl_District_Master dm ON (dm.District_ID = psf.City)

INNER JOIN tbl_CR_FIR_Act_Sections firAct ON (

firAct.Unit_ID = psf.Unit_ID

AND firAct.FIR_ID = psf.FIR_ID

)

WHERE (

dp.UOM_Desc = 3762

AND Quantity >= 5000

)

AND Item_Desc IN (

'LIQUOR BOTTLE'

,'LIQUOR PACKETS'

)

AND firAct.Act_Code IN (

107

,206

,295

,335

,342

,350

,388

,438

,577

,658

,708

,731

,807

,1097

,1098

,1234

,1289

,1355

,1564

,1565

,1566

,1567

,1586

,1627

,1634

,1728

,1810

,2080

,2081

,2568

,2642

,2873

,3320

)

AND psf.Seize_ID = @Seize_ID

) A

INNER JOIN UnitAboveInTable(@Unit_ID, ',') unit ON (unit.str = unit.str)

INNER JOIN tbl_Employee_Master em ON (em.WorkPlace_UnitID = unit.str)

INNER JOIN tbl_Unit_Master um ON (um.Unit_ID = unit.str)

INNER JOIN tbl_Unit_Type_Master ut ON (ut.Type_ID = um.Type_ID)

INNER JOIN tbl_Designation_Master dm ON (em.Designation = dm.Designation_ID)

WHERE em.Rank_ID = 5

AND em.Designation = 5

INSERT INTO tbl_CR_Application_Alert_details (

RowId

,Unit_ID

,FIR_ID

,AlertType

,Message

,From_Date

,To_Date

,UserId

)

SELECT Id

,unit.str

,FIR_Id

,AlertType

,Message

,From_Date

,To_Date

,em.Employee_ID

FROM (

SELECT NEWID() AS Id

,psf.Unit_ID AS Unit_Id

,psf.FIR_ID AS FIR_Id

,' Narcotic seized' AS AlertType

,'Narcotic/Psychotropic substances (other then ganja) was seized at ' + dm.District_Name + ' (place). It did not exceed the prescribed "minimum for pers

onal consumption or small quantity' AS Message

,GETDATE() AS From_Date

,GETDATE() + 7 AS To_Date

FROM tbl_CR_Property_Seizure_Form psf

INNER JOIN tbl_CR_Property_Details pd ON (

psf.Seize_ID = pd.Seize_ID

AND pd.Unit_ID = psf.Unit_ID

)

INNER JOIN tbl_CR_Drugs_Prop dp ON (

pd.PD_ID = dp.PD_ID

AND pd.Unit_ID = dp.Unit_ID

)

INNER JOIN tbl_District_Master dm ON (dm.District_ID = psf.City)

INNER JOIN tbl_CR_FIR_Act_Sections firAct ON (

firAct.Unit_ID = psf.Unit_ID

AND firAct.FIR_ID = psf.FIR_ID

)

WHERE NOT Item_Desc LIKE '%Ganja%'

AND firAct.Act_Code IN (

48

,51

,1710

,2172

,2518

,2581

,3323

)

AND psf.Seize_ID = @Seize_ID

) A

INNER JOIN UnitAboveInTable(@Unit_ID, ',') unit ON (unit.str = unit.str)

INNER JOIN tbl_Employee_Master em ON (em.WorkPlace_UnitID = unit.str)

INNER JOIN tbl_Unit_Master um ON (um.Unit_ID = unit.str)

INNER JOIN tbl_Unit_Type_Master ut ON (ut.Type_ID = um.Type_ID)

INNER JOIN tbl_Designation_Master dm ON (em.Designation = dm.Designation_ID)

WHERE em.Rank_ID > 1

AND em.Designation > 1

AND em.Rank_ID < 6

AND em.Designation < 6

INSERT INTO tbl_CR_Application_Alert_details (

RowId

,Unit_ID

,FIR_ID

,AlertType

,Message

,From_Date

,To_Date

,UserId

)

SELECT NewId() AS RowId

,unit.str

,act.FIR_ID

,'ArmsAmmunitionsAlert' AS AlertType

,'Theft of Government Arms and Ammunitions or theft / loss of Government property over rs. 10 lakhs was registered (FIR no. xxxx) ' + kpuser.CR_CrimeNoFormat(act.Crime_No) AS Message

,GETDATE() AS FromDate

,GETDATE() + 1 AS ToDate

,Employee_ID

FROM (

SELECT SUM(Pd.Estimated_value) AS Estimated_value

,PD.FIR_ID

,Pd.Unit_ID

,fd.Crime_No

FROM tbl_CR_Property_Details Pd

LEFT JOIN tbl_CR_FIR_Details fd ON (

fd.Unit_ID = Pd.Unit_ID

AND Pd.FIR_ID = fd.FIR_ID

)

LEFT JOIN tbl_CR_Automobile_Prop AP ON (

AP.PD_ID = pd.PD_ID

AND AP.Unit_ID = Pd.Unit_ID

)

LEFT JOIN tbl_CR_Arms_NU_Prop NP ON (

Np.PD_ID = pd.PD_ID

AND NP.Unit_ID = Pd.Unit_ID

)

WHERE (

NP.Belongs_To = 3801

OR AP.Belongs_To = 3801

)

AND Pd.Unit_ID = @Unit_ID

AND Pd.FIR_ID = @FIR_ID

GROUP BY Pd.FIR_ID

,Pd.Unit_ID

,fd.Crime_No

HAVING SUM(Pd.Estimated_value) >= 1000000

) A

INNER JOIN tbl_CR_FIR_Act_Sections Act ON (

A.FIR_ID = Act.FIR_ID

AND A.Unit_ID = Act.Unit_ID

)

INNER JOIN tbl_CR_Act_Master actmast ON (Act.Act_Code = actmast.Act_Code)

INNER JOIN UnitAboveInTable(@Unit_ID, ',') unit ON (unit.str = unit.str)

INNER JOIN tbl_Employee_Master em ON (em.WorkPlace_UnitID = unit.str)

INNER JOIN tbl_Unit_Master um ON (um.Unit_ID = unit.str)

INNER JOIN tbl_Unit_Type_Master ut ON (ut.Type_ID = um.Type_ID)

INNER JOIN tbl_Designation_Master dm ON (em.Designation = dm.Designation_ID)

WHERE Act_Description LIKE '%Arms%'

AND ShortName LIKE '%LKS%'

AND Rank_ID BETWEEN 3

AND 5

AND (

Rank_ID > 3

OR ut.Type_ID = 11

OR dm.Designation_Name LIKE '%CP%'

)

INSERT INTO tbl_CR_Application_Alert_details (

RowId

,Unit_ID

,FIR_ID

,AlertType

,Message

,From_Date

,To_Date

,UserId

)

SELECT NewId() AS RowId

,unit.str

,act.FIR_ID

,'FinancialFraudAlert' AS AlertType

,'Cases of Financial fraud (particular case or other cases of a similar nature involving the same accused) by private financial institutions which exceed rs. 2 Crores, was

registered.) (FIR No. ' + kpuser.CR_CrimeNoFormat(act.Crime_No) + ')' AS Message

,GETDATE() AS FromDate

,GETDATE() + 1 AS ToDate

,Employee_ID

FROM tbl_CR_FIR_Act_Sections act

INNER JOIN tbl_CR_Property_Seizure_Form pf ON (

Act.FIR_ID = pf.FIR_ID

AND act.Unit_ID = pf.Unit_ID

)

INNER JOIN tbl_CR_Property_Details pdet ON (

pdet.FIR_ID = act.FIR_ID

AND act.Unit_ID = pdet.Unit_ID

)

LEFT JOIN tbl_CR_Arms_NU_Prop ap ON (

ap.PD_ID = pdet.PD_ID

AND ap.Unit_ID = pdet.Unit_ID

)

INNER JOIN UnitAboveInTable(@Unit_ID, ',') unit ON (unit.str = unit.str)

INNER JOIN tbl_Employee_Master em ON (em.WorkPlace_UnitID = unit.str)

INNER JOIN tbl_Unit_Master um ON (um.Unit_ID = unit.str)

INNER JOIN tbl_Unit_Type_Master ut ON (ut.Type_ID = um.Type_ID)

INNER JOIN tbl_Designation_Master dm ON (em.Designation = dm.Designation_ID)

WHERE Section_Code IN (

SELECT Section_Code

FROM tbl_CR_Section_master

WHERE Sec_Description IN (

'415'

,'416'

,'417'

,'418'

,'419'

,'420'

,'421'

,'422'

,'423'

,'424'

,'425'

,'426'

,'427'

,'428'

,'429'

,'430'

,'431'

,'432'

,'433'

,'434'

,'435'

,'436'

,'437'

,'438'

,'439'

,'440'

)

)

AND Rank_ID BETWEEN 3

AND 5

AND (

Rank_ID > 3

OR ut.Type_ID = 11

OR dm.Designation_Name LIKE '%CP%'

)

AND act.Unit_ID = @Unit_ID

AND pf.FIR_ID = @FIR_ID

AND pdet.Item_Desc LIKE '%CASH%'

GROUP BY act.Crime_No

,act.FIR_ID

,unit.str

,pdet.Estimated_value

,Employee_ID

HAVING SUM(pdet.Estimated_value) >= 20000000

INSERT INTO tbl_CR_Application_Alert_details (

RowId

,Unit_ID

,FIR_ID

,AlertType

,Message

,From_Date

,To_Date

,UserId

)

SELECT NewId() AS RowId

,unit.str

,act.FIR_ID

,'counterfeit Alert' AS AlertType

,'Counterfeit currency case was registered (FIR no.' + kpuser.CR_CrimeNoFormat(act.Crime_No) + ')' AS Message

,GETDATE() AS FromDate

,GETDATE() + 1 AS ToDate

,Employee_ID

FROM tbl_CR_FIR_Act_Sections act

INNER JOIN tbl_CR_Property_Seizure_Form pf ON (

Act.FIR_ID = pf.FIR_ID

AND act.Unit_ID = pf.Unit_ID

)

INNER JOIN tbl_CR_Property_Details pdet ON (

pf.Seize_ID = pdet.Seize_ID

AND pdet.Unit_ID = pf.Unit_ID

)

INNER JOIN UnitAboveInTable(@Unit_ID, ',') unit ON (unit.str = unit.str)

INNER JOIN tbl_Employee_Master em ON (em.WorkPlace_UnitID = unit.str)

INNER JOIN tbl_Unit_Master um ON (um.Unit_ID = unit.str)

INNER JOIN tbl_Unit_Type_Master ut ON (ut.Type_ID = um.Type_ID)

INNER JOIN tbl_Designation_Master dm ON (em.Designation = dm.Designation_ID)

WHERE EXISTS (

SELECT *

FROM tbl_CR_Currency_Prop cp

WHERE cp.PD_ID = pdet.PD_ID

AND cp.Unit_ID = pdet.Unit_ID

)

AND EXISTS (

SELECT *

FROM tbl_CR_Section_master section

WHERE act.Section_Code = section.Section_Code

AND Sec_Description IN (

'489A'

,'489B'

,'489C'

,'489D'

,'489E'

)

)

AND pf.FIR_ID = @FIR_ID

AND pf.Unit_ID = @Unit_ID

AND Rank_ID BETWEEN 3

AND 5

AND (

Rank_ID > 3

OR ut.Type_ID = 11

OR dm.Designation_Name LIKE '%CP%'

)

GROUP BY act.FIR_ID

,act.Crime_No

,unit.str

,em.Employee_ID

ORDER BY act.FIR_ID DESC

RETURN 1

END

That being said it is no surprise that the performance of this is far less than acceptable. Fixing this is WAY beyond the scope of an online forum. If I were doing this as a consulting job I would put the estimate at a minimum of two weeks and that depends on what we find inside the various scalar and table functions.

I will highlight some of the low hanging fruit of problems. This procedure has a single inbound parameter and it is a datatype that has been deprecated for nearly a decade. If it is is XML pass the parameter as XML.

Three cursors!!! The scalar function kpuser.XmlToDateTime. So many functions... There are plenty of other code smells in here. Things that look like it is doing a roll your own style identity, procs named GenerateID, other pieces getting IDs using MAX(Col) + 1

Honestly the only way you are going to get this thing tuned is to hire somebody who has experience doing this sort of thing. It is no small amount of effort to make this better.