posted 6 years ago
Hi Team,
Some time failed middle of the executing SP(i.e. Insert 5 tables but it's not working after 3rd table) in production and calling SP through Hibernate and Spring MVC frame works.
Please provide solution and belowSP as well.
@RoId INT
AS BEGIN
SET NOCOUNT ON
DECLARE @BranchId INT,@Ronumber VARCHAR(100),@ReturnClaimNumber VARCHAR(MAX)=''
SELECT @BranchId= Branch_Id,
@Ronumber= RONumber
FROM SV_RO_HDR WHERE ro_id=@RoId
IF(@BranchId<>0 AND @Ronumber<>'')
BEGIN
CREATE TABLE #TempBillable(Id INT IDENTITY(1,1),BillableTypeId INT,ComplaintCode VARCHAR(50),LabourGroupCode VARCHAR(50))
INSERT INTO #TempBillable
SELECT SBT.billable_type_id,SRPD.ComplaintCode,SLGM.LabourGroupCode
FROM SV_RO_BILL_PRT_DTL SRBPD
INNER JOIN SV_RO_BILL_HDR SRBH ON SRBPD.ro_bill_id=SRBH.ro_bill_id
INNER JOIN SV_BILLABLE_TYPE_MST SBT ON SRBPD.billable_type_id=SBT.billable_type_id
INNER JOIN SV_RO_PART_DTL SRPD ON SRPD.partBranch_id=SRBPD.partBranch_id AND SRPD.ro_id=@RoId
AND SRPD.billable_type_id=SRBPD.billable_type_id AND SRPD.ComplaintCode=SRBPD.ComplaintCode
AND SRPD.GroupCode=SRBPD.GroupCode
INNER JOIN SV_LABOUR_GRP_MST SLGM ON SLGM.Labour_Group_Id=SRPD.GroupCode
WHERE SRBH.ro_id=@RoId AND SRBH.branch_id=@BranchId
AND SRBH.DocStatus='GENERATED' AND SBT.BillableTypeCode NOT IN ('BT1','BT9','BT10')
UNION
SELECT SBT.billable_type_id,SRLD.ComplaintCode,SLGM.LabourGroupCode
FROM SV_RO_BILL_LBR_DTL SRBLD
INNER JOIN SV_RO_BILL_HDR SRBH ON SRBLD.ro_bill_id=SRBH.ro_bill_id
INNER JOIN SV_BILLABLE_TYPE_MST SBT ON SRBLD.billable_type_id=SBT.billable_type_id
INNER JOIN SV_BILLABLE_Vs_CLAIM SBC ON SBC.billable_type_id=SRBLD.billable_type_id
INNER JOIN SV_LABOUR_MST SLM ON SLM.Labour_Id=SRBLD.labour_id
INNER JOIN SV_RO_LBR_DTL SRLD ON SRLD.Labour_id=SLM.labour_id AND SRLD.Ro_id=@RoId
INNER JOIN SV_LABOUR_GRP_MST SLGM on SRLD.LabourGroupCodeId=SLGM.Labour_Group_Id --change laboure group id from svld instead of slm 29/06/2016
WHERE SRBH.ro_id=@RoId AND SRBH.branch_id=@BranchId
AND SRBH.DocStatus='GENERATED' AND SBT.BillableTypeCode NOT IN ('BT1','BT9','BT10')
UNION
SELECT SBT.billable_type_id,SROD.ComplaintCode AS ComplaintCode,SROD.GroupCode AS LabourGroupCode
FROM SV_RO_BILL_OTHERCHARGES_DTL SRBOD
INNER JOIN SV_RO_BILL_HDR SRBH ON SRBOD.ro_bill_id=SRBH.ro_bill_id
INNER JOIN SV_BILLABLE_TYPE_MST SBT ON SRBOD.billable_type_id=SBT.billable_type_id
INNER JOIN SV_RO_OTHERCHARGES_DTL SROD ON SROD.ro_otherCharges_dtl_id=SRBOD.RO_Other_charge_DTL_Id
INNER JOIN PA_LBR_NON_INVENTORY_MST PLNI ON PLNI.partLabourId=SROD.NonInv_OtherChargsId
WHERE SRBH.ro_id=@RoId AND SRBH.branch_id=@BranchId
AND SRBH.DocStatus='GENERATED' AND SBT.BillableTypeCode NOT IN ('BT1','BT9','BT10')
DECLARE @VinId INT =0 , @RegistrationNumber VARCHAR(50)='',@Division VARCHAR(5)='',@DealerId INT=0
SELECT @Division= Division ,@VinId=CVM.Vin_Id,@RegistrationNumber=CVM.RegistrationNo,
@DealerId=AD.dealer_id
FROM CM_VIN_MST CVM
INNER JOIN SV_RO_HDR SRH ON SRH.Vin_Id=CVM.Vin_Id
INNER JOIN PA_PRODUCT_DIVISION PPD ON PPD.prod_divCd=CVM.Division
INNER JOIN ADM_BRANCH AB ON SRH.Branch_id=AB.Branch_id
INNER JOIN ADM_DEALER_BRANCH ADB ON ADB.Branch_id=AB.Branch_id
AND ADB.Product_Division_Id =(CASE WHEN CVM.Division='02' THEN 12 WHEN CVM.Division='06' THEN 17
ELSE ADB.Product_Division_Id END )
LEFT JOIN ADM_DEALER AD ON AD.dealer_id=ADB.SoldToParty_DC_id
WHERE SRH.ro_id=@RoId
DECLARE @Init INT =1 , @Length INT=0
SELECT @Length= COUNT(*) FROM #TempBillable
WHILE(@Init<=@Length)
BEGIN
DECLARE @ClaimTypeCode VARCHAR(10)='',@BillableTypeId INT=0,@ComplaintCode VARCHAR(50)='',
@LabourGroupCode VARCHAR(50)='',@RetroCount INT=0
SELECT @BillableTypeId= BillableTypeId ,@ComplaintCode=ComplaintCode,@LabourGroupCode=LabourGroupCode
FROM #TempBillable WHERE Id=@Init
--print @ComplaintCode print @LabourGroupCode
SELECT @ClaimTypeCode= WCM.ClaimTypeCode
FROM SV_BILLABLE_Vs_CLAIM SBVC
INNER JOIN WA_CLAIM_MASTER WCM ON WCM.claim_type_id=SBVC.claim_type_id
WHERE SBVC.billable_type_id=@BillableTypeId
DECLARE @BillTypeCode VARCHAR(10)=''
SELECT @BillTypeCode= BillableTypeCode FROM SV_BILLABLE_TYPE_MST WHERE billable_type_id=@BillableTypeId
SET @ClaimTypeCode =CASE WHEN @BillTypeCode='BT8' THEN 'YGT'
WHEN @BillTypeCode='BT7' AND @Division='02' THEN 'YE'---LCV
WHEN @BillTypeCode='BT7' AND @Division='06' THEN 'YM'---HCV
ELSE @ClaimTypeCode END
IF(@ClaimTypeCode<>'')
BEGIN
CREATE TABLE #TempPart(Id INT IDENTITY(1,1), partid INT,ropartdetailsids VARCHAR(MAX),cauorconparttype VARCHAR(100),partnumber VARCHAR(100),
partdesc VARCHAR(MAX),PartbranchID INT,Qty NUMERIC(18,2),warrantyCost NUMERIc(18,2),TotalAMT NUMERIc(18,2),
ChargeAmt NUMERIc(18,2),ActualPartAmt NUMERIc(18,2),claimAmt NUMERIc(18,2), ISOEMLocalPart VARCHAR(50),
HandlingCharge NUMERIc(18,2),MessagePopUp VARCHAR(MAX))
--print @ClaimTypeCode print @Ronumber print @BranchId
INSERT INTO #TempPart
EXEC GetPartWarrantyDTL @Ronumber,@ClaimTypeCode,@BranchId,@LabourGroupCode,@ComplaintCode,'1'
BillableTypeLabourExec:
CREATE TABLE #TempLabour(Labour_Id INT,RO_Labour_id INT,LabourCode VARCHAR(50),LabourDesc VARCHAR(MAX),
LabourGroupDesc VARCHAR(MAX),StandardHrs NUMERIC(18,2),WarrantyRatePerHour NUMERIC(18,2),
BasicAmt NUMERIC(18,2),ChargeAmt NUMERIC(18,2),TotalAmt NUMERIC(18,2),ActualAmt NUMERIC(18,2),
ClaimAmt NUMERIC(18,2),dealer_id INT,ISOEMLocalLabour VARCHAR(50))
IF(@BillTypeCode='BT5' AND @RetroCount=0)
BEGIN
SET @RetroCount=1
GOTO BillableTypePartExec
END
INSERT INTO #TempLabour
EXEC GetLabourWarrantyDTL @Ronumber,@ClaimTypeCode,@BranchId,@LabourGroupCode,@ComplaintCode
BillableTypePartExec:
--select * from #TempLabourOnly
/* comment bcz we will not check after ro open*/
Declare @AmcValidity int =0
Declare @AmcPortalValidity int =0
Set @AmcValidity=(select count(*) from SV_AMC_BILLING_DTLS ABD
inner join SV_AMC_REGISTRATION AR on ABD.amc_registration_id=AR.amc_registration_id
inner join SV_RO_HDR RH on RH.Vin_Id=AR.vin_id
where RH.RONumber=@Ronumber and rh.branch_id=@BranchId
and RH.OpeningDate>=AMCStartDate and RH.OpeningDate<=AMCExpiryDate
--and RH.OpeningDate>=BillingDateFrm and RH.OpeningDate<=BillingDateTo
--and InstallmentStatus='C'
)
set @AmcPortalValidity=(select count(*) from TTVehAMCDetails ta
inner join cm_vin_mst cm on ta.VehSrNo=cm.chassisno
inner join SV_RO_HDR RH on RH.Vin_Id=cm.vin_id
inner join TAMCBillingDetail tab on ta.PKRegistrationNo=tab.FKRegistrationNo
where RH.RONumber=@Ronumber
and Convert(varchar,RH.OpeningDate,112)>=Convert(varchar,ta.StartDate,112)
and Convert(varchar,RH.OpeningDate,112)<=Convert(varchar,ExpiryDate,112)
--and Convert(varchar,RH.OpeningDate,112)>=Convert(varchar,tab.BillingDateFrm,112)
--and Convert(varchar,RH.OpeningDate,112)<=Convert(varchar,tab.BillingDateTo,112)
--AND PolicyStatus='ACT'
--and tab.Status='C'
)
IF((@AmcValidity=0 and @AmcPortalValidity=0) AND @ClaimTypeCode='IA')
BEGIN
UPDATE #TempLabour SET BasicAmt=0.00 , ChargeAmt=0.00,TotalAmt=0.00,ActualAmt=0.00,ClaimAmt=0.00,WarrantyRatePerHour=0.00
UPDATE #TempPart SET ActualPartAmt=0.00 , ChargeAmt=0.00,TotalAmt=0.00,ClaimAmt=0.00,warrantyCost=0.00
END
--SELECT A.*
--INTO #TempLabour
--FROM #TempLabourOnly A
--INNER JOIN SV_RO_LBR_DTL SRLD ON SRLD.ro_id=@RoId AND SRLD.labour_id=A.Labour_Id
--INNER JOIN SV_LABOUR_MST SLM ON SLM.Labour_Id=SRLD.labour_id
--INNER JOIN SV_LABOUR_GRP_MST SLGM on SLM.Labour_Group_Id=SLGM.Labour_Group_Id
--WHERE SRLD.ComplaintCode=@ComplaintCode AND SLGM.LabourGroupCode=@LabourGroupCode
--select * from #TempLabour-- commment
--print @ClaimTypeCode
--DECLARE @IsAllow BIT =0
--SELECT @IsAllow = dbo.[FN_ISAllowWarentyClaim](@ClaimTypeCode,(SELECT COUNT(*) FROM #TempPart),
-- (SELECT COUNT(*) FROM #TempLabour))
--IF(@IsAllow=1)
--BEGIN
--print 'saurabh all condition satisfy'
DECLARE @ClaimNumber VARCHAR(50)='',@DocumentType VARCHAR(5)='',
@Date VARCHAR(20)=CONVERT(VARCHAR(20),GETDATE(),112),
@TClaimedPRTValue NUMERIC(18,2)=0.00,@TPRTChrgValue NUMERIC(18,2)=0.00,@TClaimedLBRValue NUMERIC(18,2)=0.00,
@TLBRChrgValue NUMERIC(18,2)=0.00,@TClaimedValue NUMERIC(18,2)=0.00,@partClaimT NUMERIC(18,2)=0.00,
@ChassisNo VARCHAR(20)=''
SELECT @TClaimedPRTValue=SUM(ISNULL(claimAmt,0)) - SUM(ISNULL(ChargeAmt,0)* ISNULL(Qty,0)),
@TPRTChrgValue=SUM(ISNULL(ChargeAmt,0)* ISNULL(Qty,0)),
@partClaimT=SUM(ISNULL(claimAmt,0))
FROM #TempPart
SELECT @TClaimedLBRValue=SUM(ISNULL(claimAmt,0)),
@TLBRChrgValue=SUM(ISNULL(ChargeAmt,0))
FROM #TempLabour
SET @TClaimedValue = ISNULL(@partClaimT,0)+ISNULL(@TClaimedLBRValue,0)
CREATE TABLE #TempClaimNumber (ClaimNumber VARCHAR(50))
IF(@Division='02')
SET @DocumentType='WAL'
ELSE IF(@Division='06')
SET @DocumentType='WAH'
SELECT @ChassisNo= VehSrNo FROM SV_RO_HDR WHERE ro_id=@RoId
IF @ClaimTypeCode='AC'
BEGIN
INSERT INTO #TempClaimNumber
EXEC [Get_WDoc_No_25AUG] @DocumentType,@BranchId,@Date,@ClaimTypeCode,@ChassisNo
END
ELSE
BEGIN
INSERT INTO #TempClaimNumber
EXEC [Get_WDoc_No_25AUG] @DocumentType,@BranchId,@Date,@ClaimTypeCode
END
SELECT TOP 1 @ClaimNumber=ClaimNumber FROM #TempClaimNumber
IF(@ClaimNumber<>'')
BEGIN
IF((SELECT COUNT(*) FROM #TempPart)>0 OR (SELECT COUNT(*) FROM #TempLabour)>0 OR (SELECT COUNT(*) FROM SV_RO_OTHERCHARGES_DTL WHERE ro_id=@RoId)>0)
BEGIN
BEGIN TRAN trans
IF @ClaimTypeCode IN ('ZA3')
BEGIN
UPDATE CM_DOC_BRANCH SET LastDocumentNo = RIGHT(@CLaimNumber,5)
WHERE DocumentPrefix=@DocumentType AND branch_id=@BranchId
AND finYear_id = (SELECT finYear_id FROM FI_FIN_YEAR WHERE CONVERT(DATE,GETDATE())
BETWEEN CONVERT(DATE,BeginDate) AND CONVERT(DATE,EndDate)
AND company_id=(SELECT TOP 1 company_id FROM ADM_DEALER_BRANCH WHERE branch_id=@BranchId AND IsActive='Y'))
END
ELSE
BEGIN
IF @ClaimTypeCode<>'AC'
BEGIN
UPDATE CM_DOC_BRANCH SET LastDocumentNo = SUBSTRING(@ClaimNumber,3,LEN(@CLaimNumber))
WHERE DocumentPrefix=@DocumentType AND branch_id=@BranchId
AND finYear_id = (SELECT finYear_id FROM FI_FIN_YEAR WHERE CONVERT(DATE,GETDATE())
BETWEEN CONVERT(DATE,BeginDate) AND CONVERT(DATE,EndDate)
AND company_id=(SELECT TOP 1 company_id FROM ADM_DEALER_BRANCH WHERE branch_id=@BranchId AND IsActive='Y'))
END
END
DECLARE @ClaimId INT=0
INSERT INTO SV_CLAIM_HDR
(
branch_id,
dealer_id,
ClaimNo,
ClaimDate,
ClaimTypeCode,
ClaimStatus,
T_ClaimedPRTValue,
T_PRTChrgValue,
T_ClaimedLBRValue,
T_LBRChrgValue,
T_ClaimedValue,
ro_id,
vin_id,
RegistrationNo,
IsXMLCreated,
CategoryCode,
PartyCode,
IsMaterialRequired,
IsDispatched,
IsSTaxClaimed,
STAX_XMLCreated,
CreatedDate,
CreatedBy,
SAP_ClaimTypeCode,
ComplaintCode,
GroupCode,
Claim_App_Status,
IsRejXmlCreated,
ClaimSettledDate
)
VALUES
(
@BranchId,
@DealerId ,--dealerid
@ClaimNumber,
GETDATE(),--claimdate
@ClaimTypeCode,
'Waiting For Submission Claim',
@TClaimedPRTValue,
@TPRTChrgValue,
@TClaimedLBRValue,
@TLBRChrgValue,
@TClaimedValue,
@RoId,
@VinId,
@RegistrationNumber,
'N',
'PRI',
'PRI000001',
'N',
'N',
'N',
'N',
GETDATE(),
'DB',
@ClaimTypeCode,
@ComplaintCode,
@LabourGroupCode,
'Waiting For Submission Claim',
'N',
NULL
)
SELECT @ClaimId = SCOPE_IDENTITY()
IF(@ClaimTypeCode<>'YL')
BEGIN
INSERT INTO SV_CLAIM_PRT_DTL(
claim_id,
partBranch_id,
OEMPartIndicator,
ClaimQty,
ClaimRate,
ChargeAmt,
TotalAmt,
IsCausalPart,
ActualPartAmt,
ClaimAmt,
MakeCode,
IsDispatched,
IsFOC,
CreatedDate,
CreatedBy
)
SELECT @ClaimId,PartbranchID,ISOEMLocalPart,Qty,warrantyCost,ChargeAmt,TotalAMT,cauorconparttype,
ActualPartAmt,claimAmt,'MNK' ,'N','N',GETDATE(),'DB'
FROM #TempPart
DECLARE @I INT =1, @L INT=0
SELECT @L=COUNT(*) FROM #TempPart
WHILE(@I<=@L)
BEGIN
DECLARE @RoBillPartDetailsIds VARCHAR(MAX)=''
SELECT @RoBillPartDetailsIds= ropartdetailsids FROM #TempPart WHERE Id=@I
UPDATE SV_RO_BILL_PRT_DTL SET IsClaimed='Y' ,claim_id=@ClaimId
WHERE ro_bill_prt_dtl_id IN (SELECT * FROM dbo.SplitComma(@RoBillPartDetailsIds,','))
SET @I=@I+1
END
END
INSERT INTO SV_CLAIM_LBR_DTL
(
claim_id,
labour_id,
StandardHrs,
Rate,
BasicAmt,
ChargeAmt,
TotalAmt,
ActualLabourAmt,
ClaimAmt,
MakeCode,
CreatedDate,
CreatedBy
)
SELECT @ClaimId,Labour_Id,StandardHrs,WarrantyRatePerHour,BasicAmt,ChargeAmt,TotalAmt,ActualAmt,
ClaimAmt,'MNK',GETDATE(),'DB'
FROM #TempLabour
UPDATE SV_RO_BILL_LBR_DTL SET IsClaimed='Y' ,claim_id=@ClaimId
WHERE ro_bill_lbr_dtl_id IN (SELECT RO_Labour_id FROM #TempLabour)
INSERT INTO SV_CLAIM_NON_INV_DTL
(claim_id,Oth_Part_type,Oth_PartNo,Oth_PartDesc,ClaimQty,ClaimRate,ChargeAmnt,Ref_Discount_Type,Ref_Discount_Rate,Ref_Discount_Amnt,TotalAmnt,
Actual_Oth_PartAmnt,ClaimAmnt,Version_No,NonInv_OtherChargsId)SELECT @ClaimId,'PART',NON.Oth_Part_Code,NON.Oth_Part_Desc,RBD.Qty,RBD.Rate,0 AS ChargeAmnt,RBD.DiscountType,RBD.DiscountRate,
RBD.DiscountValue,RBD.BillValue,NULL,RBD.BillValue,0,RBD.NonInv_OtherChargsId
FROM SV_RO_OTHERCHARGES_DTL ROD
INNER JOIN SV_RO_BILL_OTHERCHARGES_DTL RBD ON RBD.RO_Other_charge_DTL_Id=ROD.ro_otherCharges_dtl_id
INNER JOIN SV_RO_BILL_HDR RBH ON RBH.ro_bill_id=RBD.ro_bill_id
INNER JOIN PA_LBR_NON_INVENTORY_MST NON ON NON.partLabourId=ROD.NonInv_OtherChargsId
WHERE ROD.ro_id=@RoId AND RBH.DocStatus='GENERATED'
UPDATE SV_RO_BILL_OTHERCHARGES_DTL SET IsClaimed='Y' ,claim_id=@ClaimId
WHERE ro_bill_otherchargs_dtl_id IN (SELECT ro_bill_otherchargs_dtl_id FROM SV_RO_OTHERCHARGES_DTL ROD
INNER JOIN SV_RO_BILL_OTHERCHARGES_DTL RBD ON RBD.RO_Other_charge_DTL_Id=ROD.ro_otherCharges_dtl_id
INNER JOIN SV_RO_BILL_HDR RBH ON RBH.ro_bill_id=RBD.ro_bill_id
INNER JOIN PA_LBR_NON_INVENTORY_MST NON ON NON.partLabourId=ROD.NonInv_OtherChargsId
WHERE ROD.ro_id=@RoId AND RBH.DocStatus='GENERATED')
IF @@ERROR<>0
BEGIN
ROLLBACK TRANSACTION
INSERT INTO Auto_CLAIM_LOG
SELECT A.RONumber,B.BranchName,ERROR_PROCEDURE(),ERROR_LINE(),GETDATE()
FROM SV_RO_HDR(NOLOCK)A,ADM_BRANCH B
WHERE A.ro_id=@RoId AND A.branch_id=B.branch_id
END
ELSE
BEGIN
SET @ReturnClaimNumber=@ReturnClaimNumber+@ClaimNumber+','
COMMIT TRANSACTION
END
END
END
DROP TABLE #TempClaimNumber
--END
DROP TABLE #TempLabour
IF(@BillTypeCode='BT5' AND @RetroCount=1)
BEGIN
SET @RetroCount=2
TRUNCATE TABLE #TempPart
GOTO BillableTypeLabourExec
END
DROP TABLE #TempPart
END
SET @Init=@Init+1
END
DROP TABLE #TempBillable
SELECT ISNULL(@ReturnClaimNumber,'') AS ClaimNumber
END
END