dbo.spu_bc23_to_inventory_cancel

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.spu_bc23_to_inventory_cancel(@id_hdr as bigint )
AS
BEGIN
  declare @count int
  
  set @count = (select COUNT(*) from dbo.tbl_bc_in_hdr where id_bc_in_hdr = @id_hdr)
  if @count = 0
  begin
 select '01' as status, ' DATA NOT FOUND' as description
 return
  end
  
  --set @count = (select COUNT(*) from dbo.tbl_data_in_hdr where tbl_data_in_hdr.id_terkait = @id_hdr and tbl_data_in_hdr.status = 'APPROVED')
  --if @count <> 0
  --begin
-- select '01' as status, ' DATA SUDAH APPROVED DI RECEIVING' as description
-- return
 -- end
  
  if (select COUNT(*) from tbl_bc_in_hdr_h_status where statusdok = 'DATA SENT TO INVENTORY' and id_bc_in_hdr=@id_hdr) = 0
  begin
 select '05' as status, ' DATA DATA BELUM PERNAH DIKIRIM TIDAK BISA DICANCEL' as description
 return
  end
  
BEGIN TRY  
 BEGIN TRANSACTION
  
  UPDATE    tbl_data_in_hdr_h_status
  SET              status_date =GETDATE(), status = 'DATA INVENTORY CANCELED' 
  WHERE     (id_data_in_hdr = (select top 1 dbo.tbl_data_in_hdr.id_data_in_hdr from dbo.tbl_data_in_hdr where id_terkait = @id_hdr))
  DELETE from  dbo.tbl_data_in_dtl where [id_data_in_hdr] = (select top 1 dbo.tbl_data_in_hdr.id_data_in_hdr from dbo.tbl_data_in_hdr where id_terkait = @id_hdr)
  DELETE from dbo.tbl_data_in_hdr where id_terkait = @id_hdr
  
  UPDATE dbo.tbl_bc_in_hdr set statusdok = 'SENT TO INVENTORY CANCELED' where id_bc_in_hdr = @id_hdr
  
  INSERT INTO tbl_bc_in_hdr_h_status
                      (id_bc_in_hdr, statusdok, status_date)
  VALUES     (@id_hdr, (select top 1 statusdok from tbl_bc_in_hdr where id_bc_in_hdr = @id_hdr), GETDATE())
  
  select '01' as status, ' SUKSES UPDATED' as description
 COMMIT
END TRY
 
BEGIN CATCH
  -- Whoops, there was an error
  IF @@TRANCOUNT > 0
     ROLLBACK

  -- Raise an error with the details of the exception
  DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
  Set @ErrMsg = ERROR_MESSAGE()
  set @ErrSeverity = ERROR_SEVERITY()
  select @ErrMsg as description,@ErrSeverity as status
  RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
  
  
END
GO