April 29, 2015 at 5:00 am
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.
April 29, 2015 at 7:43 am
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/
April 29, 2015 at 11:31 pm
the sp is using more than 20 tables and too many functions . It is difficult to provide the scripts
April 30, 2015 at 1:42 am
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?
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
April 30, 2015 at 2:25 am
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
,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
,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
April 30, 2015 at 3:47 am
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.
April 30, 2015 at 7:27 am
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
,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
,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