dbo.sp_recalculate_part_number

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.sp_recalculate_part_number
 (@id_dtl as bigint, @inv_dtl as bigint, @inv_hdr_id as bigint, @id_hdr as bigint, @noinv as varchar(100), @part_number as varchar(100))
AS
BEGIN
 declare @count int
 
 set @count=(select COUNT(*) from dbo.tbl_bc_in_dtl_partnumber where id_bc_in_dtl_partnumber = @id_dtl and inv_dtl= @inv_dtl and part_number=@part_number) 
 IF @count = 0
 BEGIN
  select '01' as status, 'Data Tidak Ditemukan' as description
  return
 END
 -- cek part number dari table parths
 set @count = (select COUNT(*) from dbo.tbl_data_part_hs where part_no = @part_number)
 
 IF @count = 0
 BEGIN
  select '01' as status, 'Data Tidak Ditemukan di table part number.. Silahkan Isikan Terlebih dahulu' as description
  return
 END
 
 -- update data tbl_bc_in_dtl_partnumber where id_dtl = @id_dtl and inv_dtl= @inv_dtl and part_number=@part_number
 set @count = (SELECT COUNT(*) as countdata from (SELECT [id_bc_in_dtl_partnumber]
    ,[inv_dtl]
    ,[inv_hdr_id]
    ,[noinv]
    ,[car]
    ,[nobc]
    ,[tglbc]
    ,[seri_detil]
    ,[part_number]
    ,[part_name_customs]
    ,[bm]
    ,[qty]
    ,[unit]
    ,[harga]
    ,[kdval]
    ,[box]
    ,[pack]
  FROM 
  dbo.tbl_bc_in_dtl_partnumber where  id_bc_in_dtl_partnumber = @id_dtl and inv_dtl= @inv_dtl and part_number=@part_number
 )tblcount)
 
 IF @count = 0
 BEGIN
  select '01' as status, 'Data Tidak Ditemukan bc in detil part number' as description
  return
 END
 
 update tbl_bc_in_dtl_partnumber set 
 [part_number] = @part_number
 ,[part_name_customs] = (select top 1 tbl_data_part_hs.part_name_customs from dbo.tbl_data_part_hs where part_no = @part_number) 
 ,[hscode] =  (select top 1 tbl_data_part_hs.hs_code from dbo.tbl_data_part_hs where part_no = @part_number) 
 where  id_bc_in_dtl_partnumber = @id_dtl and inv_dtl= @inv_dtl and part_number=@part_number
 
 ---- insert ke detil dok
 

 DELETE
 from [tbl_bc_in_dtl] where id_bc_in_hdr = @id_hdr
 
 insert into dbo.tbl_bc_in_dtl (
  Serial
  ,CAR
  ,BrgUrai
  ,DNilInv
  ,NoHs
  ,id_bc_in_hdr
  ,JnsBarangDtl
  ,TujuanKirimDtl
  ,SeriTrp
  ,Merk
  ,Tipe
  ,SpfLain
  ,KdBrg
  ,JmlSat
  ,KdSat
  ) select 
    row_number() over(order by (dbo.tbl_bc_in_dtl_partnumber.part_name_customs) asc )
    ,car
    ,dbo.tbl_bc_in_dtl_partnumber.part_name_customs
    ,SUM(dbo.tbl_bc_in_dtl_partnumber.qty * dbo.tbl_bc_in_dtl_partnumber.harga)
    ,REPLACE(dbo.tbl_bc_in_dtl_partnumber.hscode,'.','')
    ,@id_hdr
    ,'01'
    ,'02'
    ,'0'
    ,'-'
    ,'-'
    ,'-'
    ,'-'
    ,sum(dbo.tbl_bc_in_dtl_partnumber.qty)
    ,dbo.tbl_bc_in_dtl_partnumber.unit
    
  FROM
    dbo.tbl_bc_in_dtl_partnumber
  WHERE
    noinv = @noinv and id_bc_in_hdr = @id_hdr
  GROUP BY
    dbo.tbl_bc_in_dtl_partnumber.noinv,
    dbo.tbl_bc_in_dtl_partnumber.hscode,
    dbo.tbl_bc_in_dtl_partnumber.unit,
    dbo.tbl_bc_in_dtl_partnumber.part_name_customs,
    dbo.tbl_bc_in_dtl_partnumber.car

 
 
  select '00' as status, 'Sukses Recalculate Data' as description
END


--GO

--DECLARE @return_value int
--EXEC @return_value = [dbo].[sp_recalculate_part_number]
-- @id_dtl = 23029,
-- @inv_dtl = 172744,
-- @inv_hdr_id = 1134,
-- @id_hdr = 180,
-- @noinv = N'BA01-4912',
-- @part_number = N'7223-2759'

--SELECT 'Return Value' = @return_value
--GO





GO