dbo.spi_incoming_approved_1

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
create PROCEDURE dbo.spi_incoming_approved_1(@id_hdr as bigint, @doc_in_from_id int, @doc_in_date as varchar(50), @user varchar(70) )
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
  
  if (select COUNT(*) from dbo.tbl_data_in_hdr where tbl_data_in_hdr.id_terkait = @id_hdr and tbl_data_in_hdr.status='APPROVED' and doc_in_from_id=@doc_in_from_id) > 0
  begin
 select '02' as status, ' DATA SUDAH APPROVED DI RECEIVING' as description
 return
  end
  
  if (select COUNT(*) from dbo.tbl_data_in_hdr where id_terkait = @id_hdr and doc_in_from_id=@doc_in_from_id) > 0
  begin
 select '03' as status, ' DATA SEDANG DIPROSES DI INVENTORY SILAHKAN CANCEL DAHULU' as description
 return
  end

 declare @idx_hdr bigint
 declare @doc_in_no varchar(50)
 declare @hasil varchar(50)
 declare @stsbrg varchar(50)

 set @doc_in_no = (select (SELECT [doc_in_from_name] FROM [dbo].[enum_data_in_from] where doc_in_from_id= @doc_in_from_id) + '-' + car from tbl_bc_in_hdr where id_bc_in_hdr = @id_hdr) 

 if (select COUNT(*) from dbo.tbl_bc_in_dtl_partnumber where id_bc_in_hdr = @id_hdr) = 0
 begin
  set @stsbrg='NOPART'
  -- no part tbl_bc_in_dtl_partnumber
  --select '01' as status, ' DATA PART NOT FOUND' as description
  --return
 end
 else
 begin
 
  set @stsbrg='PART' 
 end

 
 if  (select COUNT(*) from dbo.tbl_data_in_hdr where id_terkait = @id_hdr and doc_in_from_id=@doc_in_from_id) <> 0
 begin
  select '01' as status, ' DATA ALREADY EXIST ' as description
  return
 end

  
BEGIN TRY  
 BEGIN TRANSACTION
  UPDATE dbo.tbl_bc_in_hdr set statusdok = 'DATA SENT TO 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, (select top 1 statusdok from tbl_bc_in_hdr where id_bc_in_hdr = @id_hdr), GETDATE())
 
  
  --select '00' as status, ' SUKSES UPDATED' as description
  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=SCOPE_IDENTITY()

  
  if @stsbrg = 'PART'
  begin  
   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
     ,part_name_customs
     ,part_number
     ,part_no2
     ,qty
     ,unit
     ,box
     ,pack
     ,id_partnumber
     ,harga
     ,kdval
     ,1  -- partmaterial
     ,part_name
   from dbo.tbl_bc_in_dtl_partnumber where id_bc_in_hdr = @id_hdr
  end
  else
  begin
   if(select top 1 JnsBarang from dbo.tbl_bc_in_hdr   where id_bc_in_hdr = @id_hdr) = '03'
   begin
    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
      ,[BrgUrai]
      ,[KdBrg]
      ,[KdBrg]
      ,[JmlSat]
      ,[KdSat]
      ,1
      ,1
      ,0
      ,[DNilInv]
      ,(select [KdVal] from tbl_bc_in_hdr where id_bc_in_hdr = @id_hdr )
      ,5 --mutasi mesin dan peralatan kantor
      ,[KdBrg]
    from dbo.tbl_bc_in_dtl where id_bc_in_hdr = @id_hdr
   end 

   if(select top 1 JnsBarang from dbo.tbl_bc_in_hdr   where id_bc_in_hdr = @id_hdr) = '07'
   begin
    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
      ,[BrgUrai]
      ,[KdBrg]
      ,[KdBrg]
      ,[JmlSat]
      ,[KdSat]
      ,1
      ,1
      ,0
      ,[DNilInv]
      ,(select [KdVal] from tbl_bc_in_hdr where id_bc_in_hdr = @id_hdr )
      ,7 --mutasi barang reimpor
      ,[KdBrg]
    from dbo.tbl_bc_in_dtl where id_bc_in_hdr = @id_hdr
   end

   if(select top 1 JnsBarang from dbo.tbl_bc_in_hdr   where id_bc_in_hdr = @id_hdr) = '02'
   begin
    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
      ,[BrgUrai]
      ,[KdBrg]
      ,[KdBrg]
      ,[JmlSat]
      ,[KdSat]
      ,1
      ,1
      ,0
      ,[DNilInv]
      ,(select [KdVal] from tbl_bc_in_hdr where id_bc_in_hdr = @id_hdr )
      ,10 --mutasi bahan penolong
      ,[KdBrg]
    from dbo.tbl_bc_in_dtl where id_bc_in_hdr = @id_hdr
   end
   if(select top 1 JnsBarang from dbo.tbl_bc_in_hdr   where id_bc_in_hdr = @id_hdr) is null
   begin
    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
      ,[BrgUrai]
      ,[KdBrg]
      ,[KdBrg]
      ,[JmlSat]
      ,[KdSat]
      ,1
      ,1
      ,0
      ,[DNilInv]
      ,(select [KdVal] from tbl_bc_in_hdr where id_bc_in_hdr = @id_hdr )
      ,10 --mutasi bahan penolong
      ,[KdBrg]
    from dbo.tbl_bc_in_dtl where id_bc_in_hdr = @id_hdr
   end
  end 
  
  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())


  
  update dbo.tbl_bc_in_hdr set statusdok = 'APPROVED INVENTORY' where id_bc_in_hdr = @id_hdr
  
  INSERT INTO tbl_bc_in_hdr_h_status
      (id_bc_in_hdr, statusdok, status_date, create_by, create_date, update_by, update_date)
  VALUES     (@idx_hdr, (select top 1 statusdok from tbl_bc_in_hdr where id_bc_in_hdr = @idx_hdr), GETDATE(), @user, GETDATE(), @user, GETDATE())
  
  update dbo.tbl_data_in_hdr set status = 'APPROVED', doc_in_date=@doc_in_date where id_data_in_hdr = @idx_hdr
  
  INSERT INTO tbl_data_in_hdr_h_status
      (id_data_in_hdr, status, status_date, create_by, create_date, update_by, update_date)
  VALUES     ((select top 1 [tbl_data_in_hdr].id_data_in_hdr from dbo.[tbl_data_in_hdr] where id_data_in_hdr = @idx_hdr), 'APPROVED', GETDATE(), @user, GETDATE(), @user, GETDATE())
  

  select '00' as status, ' DATA 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
--BEGIN TRY
   
-- EXEC [spi_incoming_approved_1] @id_hdr = 1169911, @doc_in_from_id=5, @doc_in_date = '12/24/2019', @user = 0
   
-- END TRY
-- BEGIN CATCH
-- SELECT
-- '06' AS status
-- ,ERROR_MESSAGE() AS description;
    
-- END CATCH;
GO