dbo.spi_incoming

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.spi_incoming (@id_hdr as bigint, @doc_in_from_id as int )
AS
BEGIN
 declare @count int
 declare @idx_hdr bigint
 declare @doc_in_no varchar(50)
    declare @hasil varchar(50)
    declare @stsbrg 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
  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
 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
 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())
  
  select '00' as status, ' DATA PROCESSED ' + cast(@idx_hdr as varchar ) 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