dbo.vw_fn_ms_mutasi_mesin

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE FUNCTION dbo.vw_fn_ms_mutasi_mesin(
@date_start date,
@date_stop date ,
@scrap bit
) 
--ModeITEM 0=All , 1=MATERIAL, 2=FINISH GOODS, 3=MECHINE, 4=SPAREPART, 5=EQUIPMENT, 6=SCRAP
RETURNS @retTable TABLE 
 (
   kodebarang varchar(255)
 , namabarang varchar(255)
 , qty_saldoawal decimal(38,4) 
 , qty_in decimal(38,4) 
 , qty_out decimal(38,4) 
 , qty_penyesuaian decimal(38,4) 
 , saldo_akhir decimal(38,4) 
 , stock_opname decimal(38,4) 
 , selisih decimal(38,4) 
 , unit varchar(255) 
 , item_id bigint
 , is_consumable bit 
  )
WITH EXECUTE AS CALLER
AS 
BEGIN 


declare @cutoffData date  
SELECT   top (1)@cutoffData=     end_date
FROM            dt_ms_cost_final_avg
WHERE        (ms_cost_final_avg_id = 0)
set @cutoffData='2017-08-01';
--declare @data_item table(
-- ms_item_id bigint primary key
--,part_no varchar(255)
--,part_name varchar(255)
--,uom varchar(255)
--,qty_sta decimal(38,4)
--,qty_sta_range_in decimal(38,4)
--,qty_sta_range_out decimal(38,4)
--)

declare @data_item_consumable table(
ms_item_id bigint  primary key
)

insert into @data_item_consumable
SELECT        ms_item_id
FROM            dt_ms_item
WHERE        (consumable = 1)

declare @data_in table(
ms_item_id bigint primary key
,total  decimal(38,4)
)

insert into @data_in

SELECT        indetail.ms_item_id, SUM(indetail.quantity_pakai) AS total
FROM            vw_ms_item_in_detail_for_mutasi AS indetail INNER JOIN
                        dt_ms_item ON indetail.ms_item_id = dt_ms_item.ms_item_id

 WHERE        
 ((indetail.receive_date >=@date_start) and (indetail.receive_date <=@date_stop)  
 and (indetail.receive_date >@cutoffData))
 and (indetail.status_id=1)
 and not(indetail.mode_source =3) 
 and ((@scrap =1 and dt_ms_item.ms_type_id =3) or(@scrap =0 and not dt_ms_item.ms_type_id =3))
 GROUP BY indetail.ms_item_id  
  


  
insert into @data_in
SELECT        sto_detail.ms_item_id, SUM(sto_detail.qty) AS total
FROM            vw_ms_item_in_out_to_sto_detail  as sto_detail INNER JOIN
                         dt_ms_item ON sto_detail.ms_item_id = dt_ms_item.ms_item_id
WHERE       ( (sto_detail.doc_date >=@date_start) AND (sto_detail.doc_date <@date_stop) and (sto_detail.doc_date >@cutoffData))
and (sto_detail.status_id=1)
and ((@scrap =1 and dt_ms_item.ms_type_id =3) or(@scrap =0 and not dt_ms_item.ms_type_id =3))
and sto_detail.qty_ori>0
GROUP BY sto_detail.ms_item_id



declare @data_out table(
ms_item_id bigint primary key
,total  decimal(38,4)
)
 

insert into @data_out 
select ms_item_id ,sum(total) as total from (
SELECT        memo_out.ms_item_id, SUM(memo_out.qty) AS total
FROM            vw_ms_item_in_out_to_memo_detail AS memo_out INNER JOIN
                         dt_ms_item ON memo_out.ms_item_id = dt_ms_item.ms_item_id

WHERE        ((memo_out.doc_date >=@date_start) AND (memo_out.doc_date <=@date_stop) and (memo_out.doc_date >@cutoffData))
and (memo_out.status_id=1)
and ((@scrap =1 and dt_ms_item.ms_type_id =3) or(@scrap =0 and not dt_ms_item.ms_type_id =3))
GROUP BY memo_out.ms_item_id
union all 
        
SELECT        car_line_detail.ms_item_id, SUM(car_line_detail.qty) AS total
FROM            vw_ms_item_in_out_to_car_line_detail  as car_line_detail INNER JOIN
                         dt_ms_item ON car_line_detail.ms_item_id = dt_ms_item.ms_item_id
WHERE        ((car_line_detail.doc_date >=@date_start) AND (car_line_detail.doc_date <=@date_stop) 
and (car_line_detail.doc_date >@cutoffData))
and (car_line_detail.status_id=1)
and ((@scrap =1 and dt_ms_item.ms_type_id =3) or(@scrap =0 and not dt_ms_item.ms_type_id =3))
GROUP BY car_line_detail.ms_item_id

union all  
  
SELECT        sto_detail.ms_item_id, SUM(sto_detail.qty) AS total
FROM            vw_ms_item_in_out_to_sto_detail  as sto_detail INNER JOIN
                         dt_ms_item ON sto_detail.ms_item_id = dt_ms_item.ms_item_id
WHERE       ( (sto_detail.doc_date >=@date_start) AND (sto_detail.doc_date <@date_stop) and (sto_detail.doc_date >@cutoffData))
and (sto_detail.status_id=1)
and ((@scrap =1 and dt_ms_item.ms_type_id =3) or(@scrap =0 and not dt_ms_item.ms_type_id =3))
and sto_detail.qty_ori<0
GROUP BY sto_detail.ms_item_id

)xdata_out
group by ms_item_id
 
declare @data_ajustment_in table(
ms_item_id bigint primary key
,total  decimal(38,4)
)

insert into @data_ajustment_in
 SELECT        in_detail.ms_item_id, SUM(in_detail.quantity_pakai) AS total
 FROM            vw_ms_item_in_detail as in_detail INNER JOIN
                         dt_ms_item ON in_detail.ms_item_id = dt_ms_item.ms_item_id
 WHERE        ((in_detail.receive_date >=@date_start) and (in_detail.receive_date <=@date_stop) 
  and (in_detail.receive_date >@cutoffData))
 and (in_detail.status_id=1)
 and  (in_detail.mode_source =3) 
 and ((@scrap =1 and dt_ms_item.ms_type_id =3) or(@scrap =0 and not dt_ms_item.ms_type_id =3))
 GROUP BY in_detail.ms_item_id  

declare @data_ajustment_out table(
ms_item_id bigint primary key
,total  decimal(38,4)
)

insert into @data_ajustment_out
 SELECT        adjustment_detail.ms_item_id, SUM(abs(adjustment_detail.qty)) AS total
 FROM            vw_ms_item_in_out_to_adjustment_detail  as adjustment_detail INNER JOIN
                         dt_ms_item ON adjustment_detail.ms_item_id = dt_ms_item.ms_item_id
 WHERE        ((adjustment_detail.doc_date >=@date_start) AND (adjustment_detail.doc_date <=@date_stop)  and (adjustment_detail.doc_date >@cutoffData))
 and (adjustment_detail.status_id=1)
 and ((@scrap =1 and dt_ms_item.ms_type_id =3) or(@scrap =0 and not dt_ms_item.ms_type_id =3))
 GROUP BY adjustment_detail.ms_item_id 
 
declare @data_sto table(
ms_item_id bigint primary key
,total  decimal(38,4)
)
  
 
declare @ms_adj_sto_id bigint 
SELECT   top 1 @ms_adj_sto_id= ms_adj_sto_id
FROM            vw_ms_sto_header
WHERE        (doc_date = @date_stop)

 
insert into @data_sto

SELECT        sto_detail.ms_item_id, SUM(sto_detail.qty_ori) AS total
FROM            vw_ms_item_in_out_to_sto_detail  as sto_detail INNER JOIN
                         dt_ms_item ON sto_detail.ms_item_id = dt_ms_item.ms_item_id
WHERE       (  (sto_detail.doc_date =@date_stop) )
and (sto_detail.status_id=1)
and ((@scrap =1 and dt_ms_item.ms_type_id =3) or(@scrap =0 and not dt_ms_item.ms_type_id =3))
GROUP BY sto_detail.ms_item_id


declare @data_sta table(
        ms_item_id bigint primary key
  , end_qty decimal(38,5)) 

insert into @data_sta
SELECT        ms_item_id, end_qty
FROM            dt_ms_item_list_price
WHERE        (ms_cost_final_avg_id = 0)   

declare @data_sta_range_in table(
        ms_item_id bigint primary key
  , total decimal(38,5)) 
insert into @data_sta_range_in


SELECT        detail_for_mutasi.ms_item_id, SUM(detail_for_mutasi.quantity_pakai) AS total
FROM            vw_ms_item_in_detail_for_mutasi as detail_for_mutasi INNER JOIN
                         dt_ms_item ON detail_for_mutasi.ms_item_id = dt_ms_item.ms_item_id

WHERE        ((detail_for_mutasi.receive_date >=dateadd(day,1,@cutoffData)) and (detail_for_mutasi.receive_date <@date_start))
and (detail_for_mutasi.status_id=1) 
 and ((@scrap =1 and dt_ms_item.ms_type_id =3) or(@scrap =0 and not dt_ms_item.ms_type_id =3))
GROUP BY detail_for_mutasi.ms_item_id 



declare @data_sta_range_out table (
        ms_item_id bigint primary key
  , total decimal(38,5))

insert into @data_sta_range_out
 select ms_item_id ,sum(total) as total from (
        SELECT        to_memo_detail.ms_item_id, SUM(to_memo_detail.qty) AS total
        FROM            vw_ms_item_in_out_to_memo_detail as to_memo_detail INNER JOIN
                         dt_ms_item ON to_memo_detail.ms_item_id = dt_ms_item.ms_item_id

        WHERE       ( (to_memo_detail.doc_date >=dateadd(day,1,@cutoffData)) AND (to_memo_detail.doc_date <=@date_start))
        and (to_memo_detail.status_id=1)
        and ((@scrap =1 and dt_ms_item.ms_type_id =3) or(@scrap =0 and not dt_ms_item.ms_type_id =3))
        GROUP BY to_memo_detail.ms_item_id
 union all 
        
        SELECT        to_car_line_detail.ms_item_id, SUM(to_car_line_detail.qty) AS total
        FROM            vw_ms_item_in_out_to_car_line_detail as to_car_line_detail INNER JOIN
                         dt_ms_item ON to_car_line_detail.ms_item_id = dt_ms_item.ms_item_id

        WHERE        ((to_car_line_detail.doc_date >=dateadd(day,1,@cutoffData)) AND (to_car_line_detail.doc_date <=@date_start))
        and (to_car_line_detail.status_id=1)
        and ((@scrap =1 and dt_ms_item.ms_type_id =3) or(@scrap =0 and not dt_ms_item.ms_type_id =3))
        GROUP BY to_car_line_detail.ms_item_id
 union all 
        
        SELECT        to_adjustment_detail.ms_item_id, SUM(abs(to_adjustment_detail.qty)) AS total
        FROM            vw_ms_item_in_out_to_adjustment_detail as to_adjustment_detail INNER JOIN
                                dt_ms_item ON to_adjustment_detail.ms_item_id = dt_ms_item.ms_item_id

        WHERE        ((to_adjustment_detail.doc_date >=dateadd(day,1,@cutoffData)) AND (to_adjustment_detail.doc_date <=@date_start))
        and (to_adjustment_detail.status_id=1)
        and ((@scrap =1 and dt_ms_item.ms_type_id =3) or(@scrap =0 and not dt_ms_item.ms_type_id =3))
        GROUP BY to_adjustment_detail.ms_item_id
 union all 
        
        SELECT        to_sto_detail.ms_item_id, SUM(-1* to_sto_detail.qty_ori) AS total
        FROM            vw_ms_item_in_out_to_sto_detail  as to_sto_detail INNER JOIN
                                dt_ms_item ON to_sto_detail.ms_item_id = dt_ms_item.ms_item_id

        WHERE        ((to_sto_detail.doc_date >=dateadd(day,1,@cutoffData))
         AND (to_sto_detail.doc_date <@date_start))
        and (to_sto_detail.status_id=1)
        and ((@scrap =1 and dt_ms_item.ms_type_id =3) or(@scrap =0 and not dt_ms_item.ms_type_id =3))
        GROUP BY to_sto_detail.ms_item_id

         )xdata_sta_range_out
         group by ms_item_id

        


insert @retTable   
 (
   kodebarang  
 , namabarang  
 , qty_saldoawal  
 , qty_in  
 , qty_out  
 , qty_penyesuaian   
 , saldo_akhir  
 , stock_opname  
 , selisih  
 , unit   
 , item_id 
 , is_consumable 
 )
SELECT    

dt_ms_item.ms_item_custom_no
, dt_ms_item_custom.ms_item_custom_name
    
-- dt_ms_item.part_no
--, dt_ms_item.part_name
, sum(case when dt_ms_item.consumable =1 then 0 else (isnull(data_sta.end_qty ,0) + isnull(data_sta_range_in.total ,0))-isnull(data_sta_range_out.total ,0) end ) as stock_awal 
, sum(isnull(data_in.total  ,0))  as pemasukan
, sum(case when dt_ms_item.consumable =1 then isnull(data_in.total  ,0) else  isnull(data_out.total  ,0)   end )as pengeluaran
, sum(case when dt_ms_item.consumable =1 then 0 else isnull(data_ajustment_in.total  ,0)- isnull(data_ajustment_out.total  ,0)  end  )as  adjustment
, sum(case when dt_ms_item.consumable =1 then 0 else(( (isnull(data_sta.end_qty ,0) + isnull(data_sta_range_in.total ,0))-isnull(data_sta_range_out.total ,0) ) + isnull(data_in.total  ,0)  + ( isnull(data_ajustment_in.total  ,0)- isnull(data_ajustment_out.total  ,0))) -isnull(data_out.total  ,0)  end )   as stock_akhir
, sum(case when dt_ms_item.consumable =1 then 0 else case when @ms_adj_sto_id is not null
then ((( (isnull(data_sta.end_qty ,0) + isnull(data_sta_range_in.total ,0))-isnull(data_sta_range_out.total ,0) ) + isnull(data_in.total  ,0)  + ( isnull(data_ajustment_in.total  ,0)- isnull(data_ajustment_out.total  ,0))) -isnull(data_out.total  ,0))  + isnull(data_sto.total  ,0)  
else ((( (isnull(data_sta.end_qty ,0) + isnull(data_sta_range_in.total ,0))-isnull(data_sta_range_out.total ,0) ) + isnull(data_in.total  ,0)  + ( isnull(data_ajustment_in.total  ,0)- isnull(data_ajustment_out.total  ,0))) -isnull(data_out.total  ,0)) 
end  end ) as stock_opname
, sum(case when dt_ms_item.consumable =1 then 0 else  case when @ms_adj_sto_id is not null
then isnull(data_sto.total  ,0)  
else 0
end end ) as selisih
, dt_ms_item.uom
,-1--,dt_ms_item.ms_item_id
,-1--dt_ms_item.consumable
FROM            dt_ms_item LEFT OUTER JOIN
                          @data_sta
AS data_sta ON dt_ms_item.ms_item_id = data_sta.ms_item_id  
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
                          @data_sta_range_in
AS data_sta_range_in ON dt_ms_item.ms_item_id = data_sta_range_in.ms_item_id  

LEFT OUTER JOIN @data_sta_range_out
AS data_sta_range_out ON dt_ms_item.ms_item_id = data_sta_range_out.ms_item_id  
 
LEFT OUTER JOIN
                          @data_in 
AS data_in ON dt_ms_item.ms_item_id = data_in.ms_item_id  


LEFT OUTER JOIN
                           @data_out
AS data_out ON dt_ms_item.ms_item_id = data_out.ms_item_id    

LEFT OUTER JOIN
                         @data_ajustment_in
AS data_ajustment_in ON dt_ms_item.ms_item_id = data_ajustment_in.ms_item_id    

LEFT OUTER JOIN
 @data_ajustment_out
AS data_ajustment_out ON dt_ms_item.ms_item_id = data_ajustment_out.ms_item_id   
 
LEFT OUTER JOIN
 @data_sto
AS data_sto ON dt_ms_item.ms_item_id = data_sto.ms_item_id  
where  
 ((@scrap =1 and dt_ms_item.ms_type_id =3) or(@scrap =0 and not dt_ms_item.ms_type_id =3))
  
 group by  
dt_ms_item.ms_item_custom_no
, dt_ms_item_custom.ms_item_custom_name 
, dt_ms_item.uom

--,dt_ms_item.consumable
RETURN
END 


GO