Time OUT

  • Hi,

    For a sp which is causing dead lock,I replaced cursors ,so now it is causing time out some times only.

    Fortunately this is happening in production only , not able to reproduce in local. I dont have access to prod.I have to send the sp to prod which is tuned locally. Not able to tackle this issue. Urgent help needed.

  • mandirkumar 18293 (4/29/2015)


    Hi,

    For a sp which is causing dead lock,I replaced cursors ,so now it is causing time out some times only.

    Fortunately this is happening in production only , not able to reproduce in local. I dont have access to prod.I have to send the sp to prod which is tuned locally. Not able to tackle this issue. Urgent help needed.

    Hi and welcome to SSC. It is impossible to offer any kind of assistance from what you posted. There is nowhere near enough information here. In order to help we would need to see the table and index definitions. We also need to see the procedure code. If possible an actual execution plan would help too.

    Take a look at this article for an explanation of how to post performance problems. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • the sp is using more than 20 tables and too many functions . It is difficult to provide the scripts

  • mandirkumar 18293 (4/29/2015)


    the sp is using more than 20 tables and too many functions . It is difficult to provide the scripts

    Too vague to be of any assistance. Can you post the stored procedure?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    This is the procedure code

    /*

    Spr_Insert_CR_Property_Seizure'<tbl_CR_Property_Seizure_Form Unit_ID="575" Property_Status="0" Seize_ID="0" Form_ID="3" IsClandestineDismantled="0" Arr_ID="0" Type="0"

    Fir_ID="0" FinalReport_No="0" PF_No="11" Drugs_Seized_Qty="0" Chemical_Seized_Qty="0" CD_NO="0" Seized_Date_Time="2012-05-17T12:23:00"

    Seized_By="57500033" Address1="555" City="457" State="16" Ident_Req="0" Seizure_Circumstance="" Dis_Perish_Property="" Keep_Value_Property=""

    Magistrate_Order="" Remarks="" Magistrate_Date="2012-05-17T00:00:00" RowID="e6e6c32a-5ffe-4b65-b9ac-60e96992a327" User_ID="57500033"

    DismantlingDate="0001-01-01T00:00:00" SeizedFromClandestine="1" UserID="57500033"><tbl_CR_Property_Details PD_ID="0" Unit_ID="575" FIR_ID="0"

    MID="0" Item_Desc="est" Sl_No="1" PropertyType="8" Att_Flag="0" Major_Type="0" Property_Status="2" Estimated_value="0" Prop_ID="0"

    RowID="1d6b5a9f-b1e6-4567-a852-b0d7445eeca4" UserID="57500033" Mode="I" />

    <tbl_CR_witness_Details Recording_Officer_Others=" " Relation="0" FIRID="0" Witness_ID="0" Witness_Statement="0" Mode="I"

    Person_No="1" Name="KIEO" Witness_Classification="7224" Eye_Witness="0" EvidenceTendered="0" Witness_type="2" Age="0" DOB="0001-01-01T00:00:00"

    Occupation_ID="2107" Religion="0" Cast_ID="0" Sex="1" Nationality_ID="80" Address1="LOE" City_ID="470" State_ID="16"

    Record_Date="2012-05-17T00:00:00" Recording_Officer="0" Officer_Rank="0" Unit_Id="575" RowID="4a262f81-3339-44ec-a25e-97b93700a753"

    UserID="57500033" FormID="0" Evidence_Type="0" Mahazar_No="0" Seizure_No="0" OtherWitnessStatement=" " />

    <Magistrate PD_ID="0" flag="1" RowID="1d6b5a9f-b1e6-4567-a852-b0d7445eeca4" Send_To_Court="0" Unit_ID="575" MID="0" Item_Desc="est"

    FIR_ID="0" PropertyType="0" Sl_No="0" Major_Type="0" Property_Status="2" Estimated_value="0" Prop_ID="0" UserID="57500033" Type="2" />

    </tbl_CR_Property_Seizure_Form>'

    */

    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

    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

  • 3 cursors inside a transaction. phew.

    Also, there is a select X where there is a select subquery Y inside select X which itself is a column in a larger select Z.

    Think about using outer apply instead of having subqueries as a subselect which is a subselect itself.

    16 @@error statements.

    It might be better and make the code readable and troubleshoot if you did a try catch instead of 16 separate bits of error if statements.

    Thing about try catch, you only need to have it once in this case.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • 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.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply