dbo.MG_CALCULATE_AVG_MOVE_BY_BUSINESS_DATE

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.MG_CALCULATE_AVG_MOVE_BY_BUSINESS_DATE 
@ms_item xml
,@business_date date
,@last_final_avg bigint
---XencX---with encryption
as

SET TRANSACTION ISOLATION LEVEL  READ UNCOMMITTED 
declare @list_item tp_ms_avg_item

declare @tmp_ms_cost_final_avg table (
ms_cost_final_avg_id bigint primary key
)


declare @end_date_sta date 
declare @ms_date_business_id_sta bigint
SELECT  top 1 @end_date_sta= end_date
,@ms_date_business_id_sta=        dt_ms_date_business.ms_date_business_id
FROM            dt_ms_cost_final_avg INNER JOIN
                         dt_ms_date_business ON dt_ms_cost_final_avg.end_date = dt_ms_date_business.ms_date_business
WHERE        (dt_ms_cost_final_avg.ms_cost_final_avg_id = 0)



insert into @tmp_ms_cost_final_avg 
SELECT        dt_ms_cost_final_avg.ms_cost_final_avg_id
FROM            dt_ms_cost_final_avg INNER JOIN
       dt_ms_cost_final_avg_h_status ON dt_ms_cost_final_avg.ms_cost_final_avg_h_status_id = dt_ms_cost_final_avg_h_status.ms_cost_final_avg_h_status_id
WHERE        (dt_ms_cost_final_avg_h_status.ms_cost_final_avg_status_id = 1)


declare @item table(
ms_item_id  bigint primary key)

insert into @item
SELECT isnull(tempTable.item.value('@id[1]', 'bigint'),-1)  as ms_item_id 
 FROM @ms_item.nodes('item/row')  tempTable(item)

insert into @list_item (ms_item_id,business_date_start_recalculate)  
select xdata.ms_item_id 
,isnull( (SELECT       top 1(dt_ms_date_business.ms_date_business)
       FROM            dt_ms_item_daily_in_move_avg INNER JOIN
          dt_ms_date_business ON dt_ms_item_daily_in_move_avg.ms_date_business_id = dt_ms_date_business.ms_date_business_id INNER JOIN
           @tmp_ms_cost_final_avg AS header_final_avg ON dt_ms_date_business.ms_cost_final_avg_id = header_final_avg.ms_cost_final_avg_id
       WHERE        (dt_ms_item_daily_in_move_avg.ms_item_id = xdata.ms_item_id) 
        AND (dt_ms_date_business.ms_date_business < @business_date and dt_ms_date_business.ms_date_business>@end_date_sta)
        AND (header_final_avg.ms_cost_final_avg_id = - 1)
       order by dt_ms_date_business.ms_date_business desc 
   ),@business_date) 
from @item xdata
group by xdata.ms_item_id  


UPDATE       @list_item
 SET  qty_avg_final=COALESCE(vw_ms_item_final_cost_last.end_qty, vw_ms_item_beginning_balance.end_qty,0)
  ,price_avg_idr_final=COALESCE(vw_ms_item_final_cost_last.end_price, vw_ms_item_beginning_balance.end_price,0) 
  ,price_avg_usd_final=COALESCE(vw_ms_item_final_cost_last.end_price_usd, vw_ms_item_beginning_balance.end_price_usd,0) 
  ,business_date_start_recalculate=COALESCE(vw_ms_item_final_cost_last.end_date,@end_date_sta  )      
 FROM            @list_item LEFT OUTER JOIN
        vw_ms_item_beginning_balance ON [@list_item].ms_item_id = vw_ms_item_beginning_balance.ms_item_id  
      LEFT OUTER JOIN
       vw_ms_item_final_cost_last ON [@list_item].ms_item_id = vw_ms_item_final_cost_last.ms_item_id  
  

UPDATE       dt_ms_item_daily_in_move_avg
SET 
 total_qty_avg_move=  (isnull(vw_business_date_item_in_sum_bc_in.total_qty_pakai,0) + isnull(avg_final.qty_avg_final,0))
,price_avg_move_idr=  case when   (isnull(vw_business_date_item_in_sum_bc_in.total_qty_pakai,0) + isnull(avg_final.qty_avg_final,0)) =0 then 0
    else   (isnull(vw_business_date_item_in_sum_bc_in.total_amont_idr,0) + isnull(avg_final.price_avg_idr_final,0)) 
    / (isnull(vw_business_date_item_in_sum_bc_in.total_qty_pakai,0) + isnull(avg_final.qty_avg_final,0)) 
     end  
,price_avg_move_usd=  case when   (isnull(vw_business_date_item_in_sum_bc_in.total_qty_pakai,0) + isnull(avg_final.qty_avg_final,0)) =0 then 0
    else   (isnull(vw_business_date_item_in_sum_bc_in.total_amont_usd,0) + isnull(avg_final.price_avg_usd_final,0)) 
    / (isnull(vw_business_date_item_in_sum_bc_in.total_qty_pakai,0) + isnull(avg_final.qty_avg_final,0)) 
     end  
FROM            dt_ms_item_daily_in_move_avg INNER JOIN
                         vw_business_date_item_in_sum_bc_in ON dt_ms_item_daily_in_move_avg.ms_date_business_id = vw_business_date_item_in_sum_bc_in.ms_date_business_id AND 
                         dt_ms_item_daily_in_move_avg.ms_item_id = vw_business_date_item_in_sum_bc_in.ms_item_id INNER JOIN
                         dt_ms_date_business ON dt_ms_item_daily_in_move_avg.ms_date_business_id = dt_ms_date_business.ms_date_business_id INNER JOIN
                            -- (SELECT dt_ms_item_daily_in_move_avg.ms_item_id, business_date_start_recalculate, qty_avg_final, price_avg_final, price_avg_usd_final)
       @list_item AS avg_final ON 
                           vw_business_date_item_in_sum_bc_in.ms_item_id = avg_final.ms_item_id INNER JOIN
                           -- (SELECT dt_ms_item_daily_in_move_avg.ms_item_id, business_date_start_recalculate, qty_avg_final, price_avg_final, price_avg_usd_final)
       @list_item AS filter_date ON 
                         dt_ms_item_daily_in_move_avg.ms_item_id = filter_date.ms_item_id AND dt_ms_date_business.ms_date_business >= filter_date.business_date_start_recalculate and dt_ms_date_business.ms_date_business >@end_date_sta
WHERE        (dt_ms_item_daily_in_move_avg.ms_item_h_price_id IS NULL) OR (dt_ms_item_daily_in_move_avg.ms_item_h_price_id = - 1) 

---UPDATE AVG MOVE DI PENGELUARAN DATE OUT>= @business_date YANG BELUM TERKAIT POSTING
       
UPDATE       dt_ms_item_daily_out_move_avg
SET                ms_item_in_move_avg_id=( 
  SELECT    top (1)    dt_ms_item_daily_in_move_avg.ms_item_in_move_avg_id
  FROM            dt_ms_item_daily_in_move_avg INNER JOIN
         dt_ms_date_business AS date_business_in ON dt_ms_item_daily_in_move_avg.ms_date_business_id = date_business_in.ms_date_business_id
  WHERE        (date_business_in.ms_date_business <=date_business_out.ms_date_business) AND (dt_ms_item_daily_in_move_avg.ms_item_id = dt_ms_item_daily_out_move_avg.ms_item_id)  
  and dt_ms_item_daily_in_move_avg.total_qty_avg_move>0
  order by date_business_in.ms_date_business desc
)
FROM            dt_ms_item_daily_out_move_avg INNER JOIN
                         dt_ms_date_business AS date_business_out ON dt_ms_item_daily_out_move_avg.ms_date_business_id = date_business_out.ms_date_business_id INNER JOIN
                             --(SELECT dt_ms_item_daily_out_move_avg.ms_item_id, business_date_start_recalculate, qty_avg_final, price_avg_final, price_avg_usd_final)
        @list_item AS data_filter_out ON 
                         dt_ms_item_daily_out_move_avg.ms_item_id = data_filter_out.ms_item_id AND date_business_out.ms_date_business >= data_filter_out.business_date_start_recalculate and date_business_out.ms_date_business >@end_date_sta
WHERE        (dt_ms_item_daily_out_move_avg.ms_item_h_price_id IS NULL) OR
                         (dt_ms_item_daily_out_move_avg.ms_item_h_price_id = - 1)



GO