dbo.spi_incoming_bc27pasi

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.spi_incoming_bc27pasi (@id_hdr as bigint, @doc_in_from_id as int, @username varchar(100) )
AS
BEGIN
 declare @count int
 declare @idx_hdr bigint
 declare @doc_in_no varchar(50)
    declare @hasil varchar(50)
    exec [dbo].[spu_seq] @nomor_untuk = N'receiving_number', @hasil = @hasil OUTPUT
    set @doc_in_no = @hasil 
    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 HDR NOT FOUND' as description
  return
 end
 --set @count = (select COUNT(*) from dbo.tbl_bc_in_dtl_partnumber where id_bc_in_hdr = @id_hdr)
 --if @count = 0
 --begin
 -- select '01' as status, ' DATA PART NOT FOUND' as description
 -- return
 --end
 set @count = (select COUNT(*) from dbo.tbl_data_in_hdr where id_terkait = @id_hdr and doc_in_from_id=@doc_in_from_id)
 if @count <> 0
 begin
  select '01' as status, ' DATA ALREADY EXIST ' as description
  return
 end
BEGIN TRY  
 BEGIN TRANSACTION
  --select '07' as status, CAST(@doc_in_from_id as varchar) as description
  --return
  insert into dbo.tbl_data_in_hdr (doc_in_no, doc_in_from_id, id_terkait, status) values(@doc_in_no, @doc_in_from_id, @id_hdr, 'DATA PROCESSED')
  
  --set @idx_hdr = (select top 1 id_data_in_hdr from dbo.tbl_data_in_hdr where id_terkait = @id_hdr )
  set @idx_hdr=@@IDENTITY
  
  insert into dbo.tbl_data_in_dtl (
   id_data_in_hdr
   ,doc_in_from_id
   ,part_name_customs
   ,part_number
   ,part_no
   ,qty_in
   ,unit_in
   ,box
   ,pack
   ,id_part_number
   ,harga
   ,kdval
   ,type_brg
   ,part_name
  ) select 
    @idx_hdr
    ,@doc_in_from_id
    ,SpfLain
    ,BrgUrai
    ,KdBrg   --replace(BrgUrai, char(32), '')
    ,JmlSat
    ,'MTR'
    ,KemasJm
    ,KemasJn
          ,0
          ,DNilInv
          ,tbl_bc_in_hdr.KdVal
          ,1  -- partmaterial
          ,replace(BrgUrai, char(32), '')
  FROM         tbl_bc_in_hdr INNER JOIN
                      tbl_bc_in_dtl ON tbl_bc_in_hdr.id_bc_in_hdr = tbl_bc_in_dtl.id_bc_in_hdr
  where tbl_bc_in_hdr.id_bc_in_hdr = @id_hdr
  
  update dbo.tbl_bc_in_hdr set statusdok = 'DATA PROCESSED INVENTORY' 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, 'DATA PROCESSED INVENTORY', GETDATE())
  
  INSERT INTO tbl_data_in_hdr_h_status
                      (id_data_in_hdr, status, status_date)
  VALUES     (@idx_hdr, 'DATA PROCESSED', GETDATE())
  
  declare @doc_in_nox varchar(100)
  declare @doc_in_date date

  set @doc_in_no = (select [BC23No] from [tbl_bc_in_hdr] where id_bc_in_hdr=@id_hdr)
  set @doc_in_date = (select [BC23Tg] from [tbl_bc_in_hdr] where id_bc_in_hdr=@id_hdr)
    
  exec  [dbo].[spi_incoming_approved]  @id_data_in_hdr=@idx_hdr, @doc_in_from_id=@doc_in_from_id, @doc_in_no=@doc_in_no, @doc_in_date=@doc_in_date, @id_terkait=@id_hdr, @user=@username 
  --select '00' as status, ' DATA PROCESSED' 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