I have few of those in one of the project I am responsible to maintain... and sometimes something goes wrong with the system and I have oh no .. not again moment....I am not saying stored proc are bad but at times it takes you on a wild ride when figuring out what went wrong and itis absolute nightmare to debug...
ALTER PROCEDURE [dbo].[uspUpdateMfgOrdNo]
AS
BEGIN
DECLARE @Machine_Id varchar(8)
DECLARE @Flt_Locn VARCHAR(8)
DECLARE @Prod_Date datetime
DECLARE @Shift_Id VARCHAR(1)
DECLARE @Item_No varchar(15)
DECLARE @Prod_Qty numeric(9,0)
DECLARE @Tot_Prod_Qty NUMERIC(9,0)
DECLARE @Mh_Mfno varchar(8)
DECLARE @Seq_Id NUMERIC(18,0)
DECLARE @WorkCenter_Id VARCHAR(8)
DECLARE @Facility_Id VARCHAR(8)
DECLARE @Matched_MfNo VARCHAR(8)
DECLARE @Available_Qty NUMERIC(9, 0)
DECLARE @CurrentQty NUMERIC(9, 0)
DECLARE @FilledQty NUMERIC(9, 0)
DECLARE @Cono NUMERIC(9, 0)
DECLARE @Scrap_Qty NUMERIC(9, 0)
DECLARE @RowCount INT
DECLARE @blFlagMultiMO CHAR(1)
DECLARE @MachHistTemp TABLE(Id INT,Machine_Id VARCHAR(8),Flt_Locn VARCHAR(8),Prod_Date DATETIME,
Shift_Id VARCHAR(1),Item_No VARCHAR(15),Prod_Qty NUMERIC(9,0),Mh_Mfno VARCHAR(8),
Seq_Id NUMERIC(18,0))
DECLARE @ROWCNT INT
DECLARE @CNT INT
SET @blFlagMultiMO = 'F'
BEGIN TRAN
Branch_One:
TRUNCATE TABLE MachHistTemp
INSERT INTO MachHistTemp(Machine_Id,Flt_Locn,Prod_Date,Shift_Id,Item_No,Prod_Qty,Mh_Mfno,Seq_Id)
SELECT Machine_Id,Flt_Locn,Prod_Date,Shift_Id,Item_No,Prod_Qty,Mh_Mfno,Seq_Id FROM MachHist WITh(NOLOCK)
WHERE mh_mfno=-1 or mh_mfno=-2 order by mh_mfno DESC
SET @CNT=1
SELECT @ROWCNT=MAX(Id) FROM MachHistTemp
WHILE(@CNT <= @ROWCNT)
BEGIN
SELECT @Machine_Id=Machine_Id,@Flt_Locn=Flt_Locn,@Prod_Date=Prod_Date,
@Shift_Id=Shift_Id,@Item_No=Item_No,@Prod_Qty=Prod_Qty, @Mh_Mfno=Mh_Mfno,
@Seq_Id=Seq_Id FROM MachHistTemp WHERE Id=@CNT
SET @CurrentQty = @Prod_Qty
SET @Scrap_Qty = 0
IF (@Mh_Mfno = -2)
BEGIN
SET @Scrap_Qty = @Prod_Qty
END
SELECT @WorkCenter_Id=[Wc_Id] FROM [dbo].[Machine] WITh(NOLOCK) WHERE [Machine_Id] = @Machine_Id
SELECT @Facility_Id = [Faci_Id] FROM dbo.WorkCenter WITh(NOLOCK) WHERE Wc_ID = @WorkCenter_Id
WHILE (@CurrentQty != 0)
BEGIN
IF (@Facility_Id = 'C29')
BEGIN
SELECT TOP 1 @Matched_MfNo=[Mhd_MfNo], @Available_Qty=ISNULL([Mhd_Orqt], 0) - ISNULL([Mhd_Maqa], 0),
@Cono = [Mhd_Cono]
FROM [dbo].[MOrdHdr] WITh(NOLOCK)
WHERE ( (ISNULL([Mhd_Maqa], 0)) < ISNULL([Mhd_Orqt], 0) )
AND [Mhd_Prno]= @Item_No AND [Mhd_Faci]= @Facility_Id
AND [Mhd_Whst] < 90
ORDER BY [Mhd_Stdt] ASC, [Mhd_Msti] ASC ,[Mhd_MfNo] ASC
END
ELSE IF (@Facility_Id = 'C59')
BEGIN
SELECT TOP 1 @Matched_MfNo=[Mhd_MfNo],
@Available_Qty=ISNULL([Mhd_Orqt], 0) - ISNULL([Mhd_Maqa], 0),
@Cono = [Mhd_Cono]
FROM [dbo].[MOrdHdr] WITh(NOLOCK)
WHERE ( (ISNULL([Mhd_Maqa], 0)) < ISNULL([Mhd_Orqt], 0) )
AND [Mhd_Prno]= @Item_No AND [Mhd_Faci]= @Facility_Id
AND [Mhd_machine] = @Machine_Id
AND [Mhd_Whst] < 90
ORDER BY [Mhd_Stdt] ASC, [Mhd_Msti] ASC,[Mhd_MfNo] ASC
Print @@ROWCOUNT
IF(@@ROWCOUNT = 0)
BEGIN
SELECT TOP 1 @Matched_MfNo=[Mhd_MfNo],
@Available_Qty=ISNULL([Mhd_Orqt], 0) - ISNULL([Mhd_Maqa], 0),
@Cono = [Mhd_Cono]
FROM [dbo].[MOrdHdr] WITh(NOLOCK)
WHERE ( (ISNULL([Mhd_Maqa], 0)) < ISNULL([Mhd_Orqt], 0) )
AND [Mhd_Prno]= @Item_No AND [Mhd_Faci]= @Facility_Id
AND [Mhd_Whst] < 90
ORDER BY [Mhd_Stdt] ASC, [Mhd_Msti] ASC ,[Mhd_MfNo] ASC
END
END
IF (@@ROWCOUNT = 0)
BEGIN
SET @CurrentQty = 0
END
ELSE
BEGIN
SET @CurrentQty = @Available_Qty - @CurrentQty
IF (@CurrentQty < 0)
BEGIN
SET @FilledQty = @Available_Qty
SET @CurrentQty = ABS(@CurrentQty)
SET @blFlagMultiMO = 'T'
UPDATE [dbo].[MachHist]
SET Mh_Mfno=@Matched_MfNo,
Prod_Qty=@FilledQty
WHERE [Seq_Id]=@Seq_Id
INSERT INTO [dbo].[MachHist](Machine_Id, Flt_Locn, Prod_Date, Shift_Id,
Item_No, Prod_Qty, Flt_Code, Mh_Mfno, Crt_date, Crt_time)
VALUES (@Machine_Id, @Flt_Locn, @Prod_Date, @Shift_Id, @Item_No, @CurrentQty,
'', NULL, Getdate(), Getdate())
UPDATE [dbo].[MOrdHdr]
SET [Mhd_Maqa]=ISNULL([Mhd_Maqa],0) + @FilledQty
,[Mhd_Scqa] = ISNULL([Mhd_Scqa],0) + @Scrap_Qty
WHERE [Mhd_Mfno]=@Matched_MfNo
AND [Mhd_Prno]= @Item_No
AND [Mhd_Faci]= @Facility_Id
IF EXISTS (SELECT 1 FROM [dbo].[TOrdHdr] WITh(NOLOCK)
WHERE [Mhd_cono]=@Cono
AND [Faci_id]=@Facility_Id
AND [Mhd_mfno]=@Matched_MfNo
AND [Mhd_prno]=@Item_No
AND [Sfdc_Sts] = 'O')
BEGIN
UPDATE [dbo].[TOrdHdr]
SET [Mhd_Maqa] = ISNULL([Mhd_Maqa],0) + @FilledQty
,[Mhd_Scqa] = ISNULL([Mhd_Scqa],0) + @Scrap_Qty
WHERE [Mhd_cono]=@Cono
AND [Faci_id]=@Facility_Id
AND [Mhd_mfno]=@Matched_MfNo
AND [Mhd_prno]=@Item_No
AND [Sfdc_Sts] = 'O'
END
ELSE
BEGIN
INSERT INTO [dbo].[TOrdHdr](Mhd_cono, Faci_id, Mhd_mfno, Mhd_prno,
Mhd_whst, Mhd_orqt, Mhd_prio, Mhd_maqa,
Mhd_scqa, Mhd_stdt, Mhd_msti, Mhd_fidt, Mhd_mfti,
Mhd_type, Mhd_locn, Mhd_rptno, Mhd_machine,Sfdc_Sts,
Mhd_Seqs , Mhd_Prap, Mhd_Schs,
Mhd_Plgf )
SELECT Mhd_cono, Mhd_Faci, Mhd_mfno, Mhd_prno, Mhd_whst,
Mhd_orqt, Mhd_prio, @FilledQty,
@Scrap_Qty, Mhd_stdt, Mhd_msti, Mhd_fidt, Mhd_mfti,
Mhd_type, Mhd_locn, Mhd_rptno, Mhd_machine,'O',
Mhd_Seqs , Mhd_Prap, Mhd_Schs,Mhd_Plgf
FROM [dbo].[MOrdHdr] WITh(NOLOCK)
WHERE [Mhd_Mfno] = @Matched_MfNo
AND [Mhd_Prno]= @Item_No
AND [Mhd_Faci]= @Facility_Id
END
IF EXISTS (SELECT 1 FROM [dbo].[MOrdOpr] WITh(NOLOCK)
WHERE [Opr_Cono] = @Cono
AND [Opr_Faci] = @Facility_Id
AND [Opr_Mfno] = @Matched_MfNo
AND [Opr_Prno] = @Item_No)
BEGIN
UPDATE [dbo].[MOrdOpr]
SET [Opr_Maqa] = ISNULL([Opr_Maqa],0) + @FilledQty
,[Opr_Scqa] = ISNULL([Opr_Scqa],0) + @Scrap_Qty
WHERE [Opr_Cono] = @Cono
AND [Opr_Faci] = @Facility_Id
AND [Opr_Mfno] = @Matched_MfNo
AND [Opr_Prno] = @Item_No
END
IF EXISTS (SELECT 1 FROM [dbo].[TOrdOpr] WITh(NOLOCK)
WHERE [Opr_Cono] = @Cono
AND [Faci_id] = @Facility_Id
AND [Opr_Mfno] = @Matched_MfNo
AND [Opr_Prno] = @Item_No
AND [Sfdc_Sts] = 'O')
BEGIN
UPDATE [dbo].[TOrdOpr]
SET [Opr_Maqa] = ISNULL([Opr_Maqa],0) + @FilledQty
,[Opr_Scqa] = ISNULL([Opr_Scqa],0) + @Scrap_Qty
WHERE [Opr_Cono] = @Cono
AND [Faci_id] = @Facility_Id
AND [Opr_Mfno] = @Matched_MfNo
AND [Opr_Prno] = @Item_No
AND [Sfdc_Sts] = 'O'
END
ELSE
BEGIN
INSERT INTO [dbo].[TOrdOpr](Opr_cono, Faci_id,
Opr_mfno, Opr_prno, Opr_wost, Opr_opno, Opr_opds, Opr_orqt,
Opr_maqa, Opr_scqa, Opr_stdt, Opr_msti, Opr_wcetre,
Opr_subc_cf, Opr_Conto, Opr_rptno,Sfdc_Sts)
SELECT Opr_cono, Opr_Faci, Opr_mfno, Opr_prno, Opr_wost, Opr_opno,
Opr_opds, Opr_orqt, @FilledQty, @Scrap_Qty, Opr_stdt, Opr_msti,
Opr_wcentre, Opr_subc_cf, Opr_Conto, Opr_rptno,'O'
FROM [dbo].[MOrdOpr] WITh(NOLOCK)
WHERE [Opr_Cono] = @Cono
AND [Opr_Faci] = @Facility_Id
AND [Opr_Mfno] = @Matched_MfNo
AND [Opr_Prno] = @Item_No
END
SET @CurrentQty = 0
GOTO Branch_One
END
ELSE
BEGIN
IF @blFlagMultiMO = 'T'
BEGIN
SET @FilledQty = ABS(@Available_Qty - @CurrentQty)
SET @blFlagMultiMO = 'F'
END
ELSE
SET @FilledQty = ABS(@Prod_Qty)
IF (@Mh_Mfno = -2)
BEGIN
SET @Scrap_Qty = @Prod_Qty
END
ELSE
BEGIN
SET @Scrap_Qty = 0
END
UPDATE [dbo].[MachHist]
SET Mh_Mfno=@Matched_MfNo
WHERE [Seq_Id]=@Seq_Id
BEGIN
IF @Scrap_Qty > @FilledQty
declare @currDate varchar(10)
declare @errDesc varchar(100)
Set @currDate= Convert(VARCHAR(10),GetDate(),103)
Set @errDesc = 'MO: ' + CONVERT(varchar,@Matched_MfNo) + ' - ' + CONVERT(varchar,@FilledQty) + ' - ' + CONVERT(varchar,@Scrap_Qty)
exec uspSaveAPIErr @Facility_Id, @currDate, 'uspUpdateMfgOrdNo', 'err', 'qty error', @errDesc , '10'
END
UPDATE [dbo].[MOrdHdr]
SET [Mhd_Maqa]=ISNULL([Mhd_Maqa],0) + @FilledQty
,[Mhd_Scqa] = ISNULL([Mhd_Scqa],0) + @Scrap_Qty
WHERE [Mhd_Mfno]=@Matched_MfNo
AND [Mhd_Prno]= @Item_No
AND [Mhd_Faci]= @Facility_Id
IF EXISTS ( SELECT 1 FROM [dbo].[TOrdHdr] WITh(NOLOCK)
WHERE [Mhd_cono]=@Cono
AND [Faci_id]=@Facility_Id
AND [Mhd_mfno]=@Matched_MfNo
AND [Mhd_prno]=@Item_No
AND [Sfdc_Sts] = 'O')
BEGIN
UPDATE [dbo].[TOrdHdr]
SET [Mhd_Maqa] = ISNULL([Mhd_Maqa],0) + @FilledQty
,[Mhd_Scqa] = ISNULL([Mhd_Scqa],0) + @Scrap_Qty
WHERE [Mhd_cono]=@Cono
AND [Faci_id]=@Facility_Id
AND [Mhd_mfno]=@Matched_MfNo
AND [Mhd_prno]=@Item_No
AND [Sfdc_Sts] = 'O'
END
ELSE
BEGIN
INSERT INTO [dbo].[TOrdHdr](Mhd_cono, Faci_id, Mhd_mfno,
Mhd_prno, Mhd_whst, Mhd_orqt, Mhd_prio, Mhd_maqa,
Mhd_scqa, Mhd_stdt, Mhd_msti, Mhd_fidt, Mhd_mfti, Mhd_type,
Mhd_locn, Mhd_rptno, Mhd_machine,Sfdc_Sts,
Mhd_Seqs , Mhd_Prap, Mhd_Schs,
Mhd_Plgf)
SELECT Mhd_cono, Mhd_Faci, Mhd_mfno, Mhd_prno, Mhd_whst,
Mhd_orqt, Mhd_prio, @FilledQty,
@Scrap_Qty, Mhd_stdt, Mhd_msti, Mhd_fidt, Mhd_mfti,
Mhd_type, Mhd_locn, Mhd_rptno, Mhd_machine,'O',
Mhd_Seqs , Mhd_Prap, Mhd_Schs,Mhd_Plgf
FROM [dbo].[MOrdHdr] WITh(NOLOCK)
WHERE [Mhd_Mfno] = @Matched_MfNo
AND [Mhd_Prno]= @Item_No
AND [Mhd_Faci]= @Facility_Id
END
IF EXISTS ( SELECT 1 FROM [dbo].[MOrdOpr] WITh(NOLOCK)
WHERE [Opr_Cono] = @Cono
AND [Opr_Faci] = @Facility_Id
AND [Opr_Mfno] = @Matched_MfNo
AND [Opr_Prno] = @Item_No)
BEGIN
UPDATE [dbo].[MOrdOpr]
SET [Opr_Maqa] = ISNULL([Opr_Maqa],0) + @FilledQty
,[Opr_Scqa] = ISNULL([Opr_Scqa],0) + @Scrap_Qty
WHERE [Opr_Cono] = @Cono
AND [Opr_Faci] = @Facility_Id
AND [Opr_Mfno] = @Matched_MfNo
AND [Opr_Prno] = @Item_No
END
IF EXISTS (SELECT 1 FROM [dbo].[TOrdOpr] WITh(NOLOCK)
WHERE [Opr_Cono] = @Cono
AND [Faci_id] = @Facility_Id
AND [Opr_Mfno] = @Matched_MfNo
AND [Opr_Prno] = @Item_No
AND [Sfdc_Sts] = 'O')
BEGIN
UPDATE [dbo].[TOrdOpr]
SET [Opr_Maqa] = ISNULL([Opr_Maqa],0) + @FilledQty
,[Opr_Scqa] = ISNULL([Opr_Scqa],0) + @Scrap_Qty
WHERE [Opr_Cono] = @Cono
AND [Faci_id] = @Facility_Id
AND [Opr_Mfno] = @Matched_MfNo
AND [Opr_Prno] = @Item_No
AND [Sfdc_Sts] = 'O'
END
ELSE
BEGIN
INSERT INTO [dbo].[TOrdOpr](Opr_cono, Faci_id, Opr_mfno,
Opr_prno, Opr_wost, Opr_opno, Opr_opds, Opr_orqt,
Opr_maqa, Opr_scqa, Opr_stdt, Opr_msti, Opr_wcetre, Opr_subc_cf,
Opr_Conto, Opr_rptno,Sfdc_Sts)
SELECT Opr_cono, Opr_Faci, Opr_mfno, Opr_prno, Opr_wost, Opr_opno,
Opr_opds, Opr_orqt,@FilledQty, @Scrap_Qty, Opr_stdt, Opr_msti,
Opr_wcentre, Opr_subc_cf, Opr_Conto, Opr_rptno,'O'
FROM [dbo].[MOrdOpr] WITh(NOLOCK)
WHERE [Opr_Cono] = @Cono
AND [Opr_Faci] = @Facility_Id
AND [Opr_Mfno] = @Matched_MfNo
AND [Opr_Prno] = @Item_No
END
SET @CurrentQty = 0
END
END
END
SET @CNT=@CNT + 1
END
COMMIT TRAN
END
Zen and the art of software maintenance : rm -rf *
Math is like love : a simple idea but it can get complicated.
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
65 = A, 97 = a
