dbo.MG_MS_FIND_ITEM_TO_PRODUCTION

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE procedure dbo.MG_MS_FIND_ITEM_TO_PRODUCTION
@PARAMETER xml='' 
---XencX---with encryption
as
SET NOCOUNT ON 
declare @xStsnoheader varchar(2),
  @xStsno varchar(2),
  @xStsdes varchar(100)
  
set @xStsnoheader='05'
set @xStsno='00';
set @xStsdes='sukses'

  
declare @XmlSts xml
declare @raw xml 

declare  @findtext varchar(255), 
   @rows int ,
   @allow_item varchar(255) ,
   @allow_consumable bit

 SELECT
  @findtext=ltrim(rtrim(isnull(tempTable.item.value('findtext[1]', 'varchar(255)'),''))) ,
   @rows=isnull(tempTable.item.value('rows[1]', 'int'),0)  ,
   @allow_item=isnull(tempTable.item.value('allow_item[1]', 'varchar(255)'),'') , 
   @allow_consumable=isnull(tempTable.item.value('allow_consumable[1]', 'bit'),'0')  
   FROM @PARAMETER.nodes('param/field')  tempTable(item)
 -- CHECK supp_code_old
 
DECLARE @SQLString nvarchar(2000);
DECLARE @ParmDefinition nvarchar(1000);

 SET @findtext= replace(@findtext ,'[', '[[]')
 SET @findtext= replace(@findtext ,'''', '''''')
 SET @findtext= replace(@findtext ,'%','[%]')
 SET @findtext= replace(@findtext ,'_','[_]')
 SET @findtext= replace(@findtext ,'*','%')

 declare @TOP varchar(100);
 declare @xcari varchar(60)
 declare @where varchar(1000)
 
 declare @allow_type table(
 ms_type_id int primary key
 )
 insert into @allow_type(ms_type_id)
 select ltrim(ltrim(txt_value )) from  dbo.SplitStr(@allow_item,',')
 where ltrim(ltrim(txt_value ))<>'' and ISNUMERIC(ltrim(ltrim(txt_value )))=1
 group by ltrim(ltrim(txt_value )) 

 declare @tp_item table(
 urut bigint identity(1,1)
    ,ms_item_id bigint primary key
 ,part_no varchar(255)
 ,part_name varchar(255)
 ,ms_item_custom_no varchar(255)
 ,ms_item_base_no varchar(255)
 ,ms_item_base_name varchar(255)
 ,uom varchar(255)
 ,rack_no  varchar(255)
 ,nomor_hs varchar(255)
 ,tarif_bm decimal(18,2)
 ,tarif_pph decimal(18,2)
 ,tarif_ppn decimal(18,2)
 ,tarif_ppnbm decimal(18,2)
 ) 
  
  if @rows>0 
   begin 
  if (select count(*) from @tp_item)<@rows
  begin 
   insert into @tp_item( ms_item_id ,part_no ,part_name ,ms_item_custom_no ,ms_item_base_no,ms_item_base_name,uom,rack_no ,nomor_hs,tarif_bm,tarif_pph,tarif_ppn,tarif_ppnbm )
   SELECT top (@rows-(select count(*) from @tp_item)) dt_ms_item.ms_item_id , dt_ms_item.part_no, dt_ms_item.part_name, dt_ms_item.ms_item_custom_no, dt_ms_item.ms_item_base_no,dt_ms_item_base.ms_item_base_name, dt_ms_item.uom, dt_ms_item.rack_no, dt_ms_item_custom_h_tarif.nomor_hs, dt_ms_item_custom_h_tarif.tarif_bm, dt_ms_item_custom_h_tarif.tarif_pph, dt_ms_item_custom_h_tarif.tarif_ppn, dt_ms_item_custom_h_tarif.tarif_ppnbm
           
   FROM            dt_ms_item INNER JOIN
                         dt_ms_item_custom ON dt_ms_item.ms_item_custom_no = dt_ms_item_custom.ms_item_custom_no LEFT OUTER JOIN
                         dt_ms_item_base ON dt_ms_item.ms_item_base_no = dt_ms_item_base.ms_item_base_no AND dt_ms_item.ms_item_base_no = dt_ms_item_base.ms_item_base_no LEFT OUTER JOIN
                         dt_ms_item_custom_h_tarif ON dt_ms_item_custom.ms_item_custom_h_tarif_id = dt_ms_item_custom_h_tarif.ms_item_custom_h_tarif_id

   where (dt_ms_item.part_no =   @findtext )  
   and len(isnull(dt_ms_item.part_no,''))>0
   and (isnull(dt_ms_item.consumable,0) = case when @allow_consumable=0 then 0 else isnull(dt_ms_item.consumable,0) end )
   and not exists (
    select * from @tp_item as xfilter
    where dt_ms_item.ms_item_id=xfilter.ms_item_id
   ) 
   and(
   ((select count(*) from @allow_type)>0 and exists(select * from @allow_type as xtype where dt_ms_item.ms_type_id =xtype.ms_type_id ))
    or (select count(*) from @allow_type)=0
     )
   order by   part_no desc, len(isnull(dt_ms_item.part_no,''))
  end

  if (select count(*) from @tp_item)<@rows
  begin 
   insert into @tp_item( ms_item_id ,part_no ,part_name ,ms_item_custom_no ,ms_item_base_no,ms_item_base_name,uom,rack_no,nomor_hs,tarif_bm,tarif_pph,tarif_ppn,tarif_ppnbm )
   SELECT top (@rows-(select count(*) from @tp_item)) dt_ms_item.ms_item_id , dt_ms_item.part_no, dt_ms_item.part_name, dt_ms_item.ms_item_custom_no, dt_ms_item.ms_item_base_no,dt_ms_item_base.ms_item_base_name, dt_ms_item.uom, dt_ms_item.rack_no, dt_ms_item_custom_h_tarif.nomor_hs, dt_ms_item_custom_h_tarif.tarif_bm, dt_ms_item_custom_h_tarif.tarif_pph, dt_ms_item_custom_h_tarif.tarif_ppn, dt_ms_item_custom_h_tarif.tarif_ppnbm
           
  FROM            dt_ms_item INNER JOIN
                         dt_ms_item_custom ON dt_ms_item.ms_item_custom_no = dt_ms_item_custom.ms_item_custom_no LEFT OUTER JOIN
                         dt_ms_item_base ON dt_ms_item.ms_item_base_no = dt_ms_item_base.ms_item_base_no AND dt_ms_item.ms_item_base_no = dt_ms_item_base.ms_item_base_no LEFT OUTER JOIN
                         dt_ms_item_custom_h_tarif ON dt_ms_item_custom.ms_item_custom_h_tarif_id = dt_ms_item_custom_h_tarif.ms_item_custom_h_tarif_id

   where (dt_ms_item.part_no like '' +   @findtext + '%')  
  and len(isnull(dt_ms_item.part_no,''))>0
  and (isnull(dt_ms_item.consumable,0) = case when @allow_consumable=0 then 0 else isnull(dt_ms_item.consumable,0) end )
  and not exists (
   select * from @tp_item as xfilter
   where dt_ms_item.ms_item_id=xfilter.ms_item_id
  ) 
  and(
  ((select count(*) from @allow_type)>0 and exists(select * from @allow_type as xtype where dt_ms_item.ms_type_id =xtype.ms_type_id ))
   or (select count(*) from @allow_type)=0
    )
  order by   part_no desc, len(isnull(dt_ms_item.part_no,''))
  end

  if (select count(*) from @tp_item)<@rows
  begin 

   insert into @tp_item( ms_item_id ,part_no ,part_name ,ms_item_custom_no ,ms_item_base_no,ms_item_base_name,uom,rack_no,nomor_hs,tarif_bm,tarif_pph,tarif_ppn,tarif_ppnbm )
   SELECT top (@rows-(select count(*) from @tp_item)) dt_ms_item.ms_item_id , dt_ms_item.part_no, dt_ms_item.part_name, dt_ms_item.ms_item_custom_no, dt_ms_item.ms_item_base_no,dt_ms_item_base.ms_item_base_name, dt_ms_item.uom, dt_ms_item.rack_no, dt_ms_item_custom_h_tarif.nomor_hs, dt_ms_item_custom_h_tarif.tarif_bm, dt_ms_item_custom_h_tarif.tarif_pph, dt_ms_item_custom_h_tarif.tarif_ppn, dt_ms_item_custom_h_tarif.tarif_ppnbm
           
  FROM            dt_ms_item INNER JOIN
                         dt_ms_item_custom ON dt_ms_item.ms_item_custom_no = dt_ms_item_custom.ms_item_custom_no LEFT OUTER JOIN
                         dt_ms_item_base ON dt_ms_item.ms_item_base_no = dt_ms_item_base.ms_item_base_no AND dt_ms_item.ms_item_base_no = dt_ms_item_base.ms_item_base_no LEFT OUTER JOIN
                         dt_ms_item_custom_h_tarif ON dt_ms_item_custom.ms_item_custom_h_tarif_id = dt_ms_item_custom_h_tarif.ms_item_custom_h_tarif_id
    where (dt_ms_item.part_name like  '' + @findtext + '%')  
   and len(isnull(dt_ms_item.part_name,''))>0
   and (isnull(dt_ms_item.consumable,0) = case when @allow_consumable=0 then 0 else isnull(dt_ms_item.consumable,0) end )
   and not exists (
   select * from @tp_item as xfilter
   where dt_ms_item.ms_item_id=xfilter.ms_item_id
   ) 
   and(
   ((select count(*) from @allow_type)>0 and exists(select * from @allow_type as xtype where dt_ms_item.ms_type_id =xtype.ms_type_id ))
   or (select count(*) from @allow_type)=0
     )
    order by   dt_ms_item.part_name  asc, len(isnull(dt_ms_item.part_name,'')) 
  end
  
  
  if (select count(*) from @tp_item)<@rows
  begin 
   insert into @tp_item( ms_item_id ,part_no ,part_name ,ms_item_custom_no ,ms_item_base_no,ms_item_base_name,uom,rack_no ,nomor_hs,tarif_bm,tarif_pph,tarif_ppn,tarif_ppnbm )
   SELECT top (@rows-(select count(*) from @tp_item)) dt_ms_item.ms_item_id , dt_ms_item.part_no, dt_ms_item.part_name, dt_ms_item.ms_item_custom_no, dt_ms_item.ms_item_base_no,dt_ms_item_base.ms_item_base_name, dt_ms_item.uom, dt_ms_item.rack_no, dt_ms_item_custom_h_tarif.nomor_hs, dt_ms_item_custom_h_tarif.tarif_bm, dt_ms_item_custom_h_tarif.tarif_pph, dt_ms_item_custom_h_tarif.tarif_ppn, dt_ms_item_custom_h_tarif.tarif_ppnbm
           
  FROM            dt_ms_item INNER JOIN
                         dt_ms_item_custom ON dt_ms_item.ms_item_custom_no = dt_ms_item_custom.ms_item_custom_no LEFT OUTER JOIN
                         dt_ms_item_base ON dt_ms_item.ms_item_base_no = dt_ms_item_base.ms_item_base_no AND dt_ms_item.ms_item_base_no = dt_ms_item_base.ms_item_base_no LEFT OUTER JOIN
                         dt_ms_item_custom_h_tarif ON dt_ms_item_custom.ms_item_custom_h_tarif_id = dt_ms_item_custom_h_tarif.ms_item_custom_h_tarif_id
   where (dt_ms_item.part_no like '%' + @findtext + '%')  
  and len(isnull(dt_ms_item.part_no,''))>0
  and (isnull(dt_ms_item.consumable,0) = case when @allow_consumable=0 then 0 else isnull(dt_ms_item.consumable,0) end )
  and not exists (
   select * from @tp_item as xfilter
   where dt_ms_item.ms_item_id=xfilter.ms_item_id
  ) 
   and(
   ((select count(*) from @allow_type)>0 and exists(select * from @allow_type as xtype where dt_ms_item.ms_type_id =xtype.ms_type_id ))
   or (select count(*) from @allow_type)=0
     )
  order by   part_no desc, len(isnull(dt_ms_item.part_no,''))
  end

 
  if (select count(*) from @tp_item)<@rows 
  begin 

   insert into @tp_item( ms_item_id ,part_no ,part_name ,ms_item_custom_no ,ms_item_base_no,ms_item_base_name,uom,rack_no ,nomor_hs,tarif_bm,tarif_pph,tarif_ppn,tarif_ppnbm )
   SELECT top (@rows-(select count(*) from @tp_item)) dt_ms_item.ms_item_id , dt_ms_item.part_no, dt_ms_item.part_name, dt_ms_item.ms_item_custom_no, dt_ms_item.ms_item_base_no,dt_ms_item_base.ms_item_base_name, dt_ms_item.uom, dt_ms_item.rack_no, dt_ms_item_custom_h_tarif.nomor_hs, dt_ms_item_custom_h_tarif.tarif_bm, dt_ms_item_custom_h_tarif.tarif_pph, dt_ms_item_custom_h_tarif.tarif_ppn, dt_ms_item_custom_h_tarif.tarif_ppnbm
           
  FROM            dt_ms_item INNER JOIN
                         dt_ms_item_custom ON dt_ms_item.ms_item_custom_no = dt_ms_item_custom.ms_item_custom_no LEFT OUTER JOIN
                         dt_ms_item_base ON dt_ms_item.ms_item_base_no = dt_ms_item_base.ms_item_base_no AND dt_ms_item.ms_item_base_no = dt_ms_item_base.ms_item_base_no LEFT OUTER JOIN
                         dt_ms_item_custom_h_tarif ON dt_ms_item_custom.ms_item_custom_h_tarif_id = dt_ms_item_custom_h_tarif.ms_item_custom_h_tarif_id
    where (dt_ms_item.part_name like '%' +  @findtext + '%')  
   and len(isnull(dt_ms_item.part_name,''))>0
   and (isnull(dt_ms_item.consumable,0) = case when @allow_consumable=0 then 0 else isnull(dt_ms_item.consumable,0) end )
   and not exists (
   select * from @tp_item as xfilter
   where dt_ms_item.ms_item_id=xfilter.ms_item_id
   ) 
   and(
   ((select count(*) from @allow_type)>0 and exists(select * from @allow_type as xtype where dt_ms_item.ms_type_id =xtype.ms_type_id ))
    or (select count(*) from @allow_type)=0
    )
    order by   dt_ms_item.part_name  asc, len(isnull(dt_ms_item.part_name,'')) 
  end
    
    
 
  if (select count(*) from @tp_item)<@rows and @findtext=''
  begin 

   insert into @tp_item( ms_item_id ,part_no ,part_name ,ms_item_custom_no ,ms_item_base_no,ms_item_base_name,uom,rack_no,nomor_hs,tarif_bm,tarif_pph,tarif_ppn,tarif_ppnbm )
   SELECT top (@rows-(select count(*) from @tp_item)) dt_ms_item.ms_item_id , dt_ms_item.part_no, dt_ms_item.part_name, dt_ms_item.ms_item_custom_no, dt_ms_item.ms_item_base_no,dt_ms_item_base.ms_item_base_name, dt_ms_item.uom, dt_ms_item.rack_no, dt_ms_item_custom_h_tarif.nomor_hs, dt_ms_item_custom_h_tarif.tarif_bm, dt_ms_item_custom_h_tarif.tarif_pph, dt_ms_item_custom_h_tarif.tarif_ppn, dt_ms_item_custom_h_tarif.tarif_ppnbm
           
   FROM            dt_ms_item INNER JOIN
                         dt_ms_item_custom ON dt_ms_item.ms_item_custom_no = dt_ms_item_custom.ms_item_custom_no LEFT OUTER JOIN
                         dt_ms_item_base ON dt_ms_item.ms_item_base_no = dt_ms_item_base.ms_item_base_no AND dt_ms_item.ms_item_base_no = dt_ms_item_base.ms_item_base_no LEFT OUTER JOIN
                         dt_ms_item_custom_h_tarif ON dt_ms_item_custom.ms_item_custom_h_tarif_id = dt_ms_item_custom_h_tarif.ms_item_custom_h_tarif_id
   where  len(isnull(dt_ms_item.part_no,''))>0
   and (isnull(dt_ms_item.consumable,0) = case when @allow_consumable=0 then 0 else isnull(dt_ms_item.consumable,0) end )
   and not exists (
   select * from @tp_item as xfilter
   where dt_ms_item.ms_item_id=xfilter.ms_item_id
   ) 
   and(
   ((select count(*) from @allow_type)>0 and exists(select * from @allow_type as xtype where dt_ms_item.ms_type_id =xtype.ms_type_id ))
    or (select count(*) from @allow_type)=0
    
    )
    order by   dt_ms_item.part_no  asc, len(isnull(dt_ms_item.part_no,'')) 
  end
end
 set @raw=(
 select 
  ms_item_id  as rID
 ,part_no as r1
 ,part_name  as r2
 ,ms_item_custom_no  as r3
 ,ms_item_base_no as r4
 ,ms_item_base_name as r4n
 ,uom  as r5
 ,rack_no  as r6
 ,nomor_hs  as hs1
 ,tarif_bm  as hs2
 ,tarif_pph  as hs3
 ,tarif_ppn  as hs4
 ,tarif_ppnbm  as hs5
 from @tp_item 
 order by urut
 for xml path ('xrow'))  

SET NOCOUNT OFF
f:

set @XmlSts=(select @xStsnoheader + @xStsno as 'no', @xStsdes as 'des' for xml path('sts'))
   select  CAST( (select @XmlSts,@raw    for xml path ('result')
)  as XML)  as dt
 

GO