dbo.MS_MS_ITEM_FINAL_AVG_REPORT_ALL_BACKUP

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

CREATE procedure dbo.MS_MS_ITEM_FINAL_AVG_REPORT_ALL_BACKUP
@PARAMETER xml='' 
---XencX---with encryption
as
 

SET TRANSACTION ISOLATION LEVEL  READ UNCOMMITTED

declare @pemisah varchar(255)
declare @pemisah_replace varchar(255)
set @pemisah_replace='<br />'
--set @pemisah=CHAR(13) + CHAR(10) + '
'
set @pemisah = '
'

declare @resultX varchar(max)
declare @resultX2 varchar(max)

declare @XmlSts xml
declare @raw xml
declare @xStsnoheader varchar(2),
  @xStsno varchar(2),
  @xStsdes varchar(max)
   
set @xStsnoheader='07'
set @xStsno='00';
set @xStsdes='sukses'
 
BEGIN TRY
SET NOCOUNT ON
DECLARE @NOW datetime
set @NOW =GETDATE()
   
DECLARE 
@data_by varchar(50)
,@business_date date  
,@header_id bigint
,@with_detail_daily bit

SELECT 
 @data_by=left(ltrim(rtrim(isnull(tempTable.item.value('data_by[1]', 'varchar(100)'),''))),50) 
,@header_id=isnull(tempTable.item.value('header_id[1]', 'bigint'),-1) 
,@business_date =dbo.str_to_date( isnull(tempTable.item.value('end_date[1]', 'varchar(305)'),'20170808'))
,@with_detail_daily = isnull(tempTable.item.value('with_detail_daily[1]', 'bit'),'0')
 FROM @PARAMETER.nodes('param/field')  tempTable(item)   
 
declare @data_in_from_retur xml
declare @data_in_from_bc xml 
declare @data_in_from_adjustment xml 
declare @data_in_from_sto xml
declare @data_out_carline xml
declare @data_out_memo xml
--declare @data_out_adjustment xml
--declare @data_out_sto xml
declare @data_category xml 
declare @raw_io_item xml  
declare @data_in_supplier_local xml  
declare @data_in_supplier_import xml  


declare @tp_ms_item_avg_pre as tp_ms_item_avg_pre       
declare @tp_ms_avg_to_sto tp_ms_avg_to_sto
declare @tp_ms_avg_to_adjustment tp_ms_avg_to_adjustment
declare @tp_ms_avg_stock_to_memo tp_ms_avg_stock_to_memo 
declare @tp_ms_item_out_to_carline tp_ms_item_out_to_carline 

declare @tp_ms_avg_in_from_sto tp_ms_avg_in_from_sto
declare @tp_ms_avg_in_from_adjustment tp_ms_avg_in_from_adjustment
declare @tp_ms_avg_in_from_retur tp_ms_avg_in_from_retur
declare @tp_ms_avg_in_from_bc tp_ms_avg_in_from_bc 

declare @tp_ms_in_by_supplier_local tp_ms_in_by_supplier_local 
declare @tp_ms_in_by_supplier_import tp_ms_in_by_supplier_import 
  
declare @tp_ms_avg_catergory_detail_in_out tp_ms_avg_catergory_detail_in_out

declare @REPORT_NAME varchar(255)
declare @REPORT_START date
declare @REPORT_STOP date=@business_date

declare @end_date_sta date 
declare @ms_date_business_id_sta bigint

if @header_id=-1
begin   
  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 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        (NOT (dt_ms_cost_final_avg.ms_cost_final_avg_id = 0)) AND (NOT (dt_ms_cost_final_avg_h_status.ms_cost_final_avg_status_id = - 1))
  ORDER BY dt_ms_cost_final_avg.end_date DESC
   
  if @end_date_sta is null
  begin  
   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) 
   ORDER BY dt_ms_cost_final_avg.end_date DESC  
  end

 set @REPORT_START= dateadd(day,1,@end_date_sta ) 
    set @REPORT_NAME='Draft ' + dbo.dateToIndStr(@REPORT_START) + ' until ' + dbo.dateToIndStr(@REPORT_STOP)
   
   
 --- LIST DETAIL IN DARI RETUR

 insert into @tp_ms_avg_in_from_retur (
   ms_in_item_detail_id   
   , category_id  
   , category_name  
   , ms_date_business_id  
   , ms_date_business_id_from  
   , ms_item_id  
   , part_no 
   , part_name  
   , qty_pakai   
   , UOM    
   ,retur_no   
   ,retur_date   
   ,retur_info   
   ,is_adjustment     
 )
  SELECT  
   dt_ms_item_in_detail.ms_in_item_detail_id 
   ,isnull( dt_ms_item_base_h_report_car_line_sum.ms_report_car_line_sum_id,-1) as category_id
   , isnull(dt_ms_report_car_line_sum.ms_report_car_line_sum_name     ,'') as  category_name  
   ,dt_ms_item_in_detail.ms_date_business_id
   ,dt_ms_item_in_detail.ms_date_business_id_from 
   ,dt_ms_item_in_detail.ms_item_id
   ,dt_ms_item.part_no
   ,dt_ms_item.part_name
   ,dt_ms_item_in_detail.quantity_pakai as  qty_pakai
   ,dt_ms_item.uom
   --,dt_ms_item_in_detail.total_amont_idr as ori_amount_idr
   --,dt_ms_item_in_detail.total_amont_usd as ori_amount_usd
    ,dt_ms_production_retur.retur_no 
   ,dt_ms_production_retur.retur_date  
   ,dt_ms_production_retur_detail.retur_info 
   ,dt_ms_item_in_detail.is_adjustment  
  FROM            dt_ms_item_base_h_report_car_line_sum INNER JOIN
                         dt_ms_report_car_line_sum ON dt_ms_item_base_h_report_car_line_sum.ms_report_car_line_sum_id = dt_ms_report_car_line_sum.ms_report_car_line_sum_id RIGHT OUTER JOIN
                         dt_ms_item_base ON dt_ms_item_base_h_report_car_line_sum.ms_item_base_h_report_car_line_sum_id = dt_ms_item_base.ms_item_base_h_report_car_line_sum_id RIGHT OUTER JOIN
                         dt_ms_production_retur_h_status INNER JOIN
                         dt_ms_production_retur ON dt_ms_production_retur_h_status.ms_production_retur_h_status_id = dt_ms_production_retur.ms_production_retur_h_status_id INNER JOIN
                         dt_ms_production_retur_detail ON dt_ms_production_retur.ms_production_retur_id = dt_ms_production_retur_detail.ms_production_retur_id INNER JOIN
                         dt_ms_item_daily_in_move_avg INNER JOIN
                         dt_ms_item_in_detail ON dt_ms_item_daily_in_move_avg.ms_item_in_move_avg_id = dt_ms_item_in_detail.ms_item_in_move_avg_id ON 
                         dt_ms_production_retur_detail.ms_production_retur_detail_id = dt_ms_item_in_detail.id_source 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
                         dt_ms_item ON dt_ms_item_in_detail.ms_item_id = dt_ms_item.ms_item_id ON dt_ms_item_base.ms_item_base_no = dt_ms_item.ms_item_base_no AND dt_ms_item_base.ms_item_base_no = dt_ms_item.ms_item_base_no
         WHERE        
  (dt_ms_production_retur_h_status.status_production_retur_id = 1) 
  AND ((dt_ms_item_in_detail.mode_source =5 )) 
  AND (dt_ms_item_in_detail.is_adjustment=1) 
  AND (dt_ms_item_daily_in_move_avg.ms_item_h_price_id = - 1 OR dt_ms_item_daily_in_move_avg.ms_item_h_price_id IS NULL)
  AND (dt_ms_date_business.ms_date_business <= @business_date and dt_ms_date_business.ms_date_business >@end_date_sta) 
  AND (( NULLIF (dt_ms_item_daily_in_move_avg.price_avg_move_idr, 0) IS NOT NULL AND dt_ms_item_daily_in_move_avg.ms_cost_final_avg_id <>0) )
  AND (( NULLIF (dt_ms_item_daily_in_move_avg.price_avg_move_usd, 0) IS NOT NULL AND dt_ms_item_daily_in_move_avg.ms_cost_final_avg_id <>0))
  AND (( NULLIF (dt_ms_item_daily_in_move_avg.total_qty_avg_move, 0) IS NOT NULL AND dt_ms_item_daily_in_move_avg.ms_cost_final_avg_id <>0)   )
  AND (dt_ms_item_daily_in_move_avg.ms_cost_final_avg_id=-1 or dt_ms_item_daily_in_move_avg.ms_cost_final_avg_id is null)

 ---DATA DATA OUTGOING
 
 ---- LIST ITEM OUT TO CAR LINE
 insert into @tp_ms_item_out_to_carline   (  
   category_id 
 , category_name   
 , doc_no 
 , doc_date  
 , doc_info  
 , ms_type_prosses_id  
  ,ms_type_prosses_name  
 , ms_car_line_no  
 , ms_car_line_name   
 , ms_item_id 
 , part_no   
 , part_name      
 , ms_car_line_group_id 
 , ms_car_line_group  
 , remark   
 , is_adjustment 
 , qty_total    
 , ms_date_business_id
 , ms_date_business_id_from
 )  


 ---- RUMUS PENGELUARAN KE CAR LINE
 SELECT         
      isnull(dt_ms_item_base_h_report_car_line_sum.ms_report_car_line_sum_id,-1) as category_id
 , isnull(dt_ms_report_car_line_sum.ms_report_car_line_sum_name,'')  as category_name 
  , dt_ms_car_line_out_header.doc_no
 , dt_ms_car_line_out_header.doc_date
 , dt_ms_car_line_out_header.doc_info
 , isnull(dt_ms_type_prosses.ms_type_prosses_id,-1) as ms_type_prosses_id
 , isnull(dt_ms_type_prosses.ms_type_prosses_name ,'OTHERS') as ms_type_prosses_name
 , dt_ms_car_line.ms_car_line_no
 , dt_ms_car_line.ms_car_line_name 
 , dt_ms_item.ms_item_id  
 , dt_ms_item.part_no 
 , dt_ms_item.part_name 
 , dt_ms_car_line.ms_car_line_group_id 
 , dt_ms_car_line_group.ms_car_line_group
 , dt_ms_car_line_out_detail.info 
 , isnull(dt_ms_car_line_out_header.is_move_business_date ,0) 
 , vw_ms_out_car_line_detail_sum_supply.total_supply as qty_total 
 , dt_ms_car_line_out_header.ms_date_business_id  
 , dt_ms_car_line_out_header.ms_date_business_id_from  
FROM            dt_ms_car_line INNER JOIN
                         dt_ms_date_business INNER JOIN
                         dt_ms_car_line_out_header INNER JOIN
                         dt_ms_car_line_out_header_h_status ON dt_ms_car_line_out_header.ms_car_line_out_header_h_status_id = dt_ms_car_line_out_header_h_status.ms_car_line_out_header_h_status_id INNER JOIN
                         dt_ms_car_line_out_detail ON dt_ms_car_line_out_header.ms_car_line_out_header_id = dt_ms_car_line_out_detail.ms_car_line_out_header_id INNER JOIN
                         dt_ms_item_daily_out_move_avg ON dt_ms_car_line_out_detail.ms_item_out_move_avg_id = dt_ms_item_daily_out_move_avg.ms_item_out_move_avg_id ON 
                         dt_ms_date_business.ms_date_business_id = dt_ms_item_daily_out_move_avg.ms_date_business_id INNER JOIN
                         vw_ms_out_car_line_detail_sum_supply ON dt_ms_car_line_out_detail.ms_car_line_out_detail_id = vw_ms_out_car_line_detail_sum_supply.ms_car_line_out_detail_id INNER JOIN
                         dt_ms_item ON dt_ms_car_line_out_detail.ms_item_id = dt_ms_item.ms_item_id ON dt_ms_car_line.ms_car_line_no = dt_ms_car_line_out_header.ms_car_line_no INNER JOIN
                         dt_ms_car_line_group ON dt_ms_car_line.ms_car_line_group_id = dt_ms_car_line_group.ms_car_line_group_id LEFT OUTER JOIN
                         dt_ms_item_base_h_report_car_line_sum INNER JOIN
                         dt_ms_item_base ON dt_ms_item_base_h_report_car_line_sum.ms_item_base_h_report_car_line_sum_id = dt_ms_item_base.ms_item_base_h_report_car_line_sum_id INNER JOIN
                         dt_ms_report_car_line_sum ON dt_ms_item_base_h_report_car_line_sum.ms_report_car_line_sum_id = dt_ms_report_car_line_sum.ms_report_car_line_sum_id 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_type_prosses ON dt_ms_car_line_out_header.ms_type_prosses_id = dt_ms_type_prosses.ms_type_prosses_id
       
           
  WHERE        
 (dt_ms_car_line_out_header_h_status.ms_car_line_out_header_status_id = 1) 
 AND (dt_ms_date_business.ms_date_business <= @business_date) and dt_ms_date_business.ms_date_business >@end_date_sta
 AND (dt_ms_item_daily_out_move_avg.ms_cost_final_avg_id=-1)
 ORDER BY 
 dt_ms_item_base_h_report_car_line_sum.ms_report_car_line_sum_id
 ,  dt_ms_car_line.ms_car_line_group_id
   
 ---- RUMUS PENGELUARAN KE MEMO

 insert into @tp_ms_avg_stock_to_memo (
   ms_memo_type_id 
  ,ms_memo_type_name 
  ,memo_code 
  ,memo_date 
  ,category_id 
  ,category_name 
  ,ms_item_id  
  ,part_no
  ,part_name 
  ,total_out 
  ,supplier_customer_id
  ,supplier_customer_code
  ,supplier_customer_name 
  ,is_affilete
  ,is_adjustment
 , ms_date_business_id
 , ms_date_business_id_from
  )

 SELECT  
   dt_ms_memo_header.ms_memo_type_id
 , enum_ms_memo_type.ms_memo_type_name
 , dt_ms_memo_header.doc_no 
 , dt_ms_memo_header.doc_date   
 , isnull(dt_ms_item_base_h_report_car_line_sum.ms_report_car_line_sum_id,-1) as category_id
 , isnull(dt_ms_report_car_line_sum.ms_report_car_line_sum_name,'')  as category_name 
 , dt_ms_item_in_detail.ms_item_id
 , dt_ms_item.part_no
 , dt_ms_item.part_name
 , SUM(dt_ms_memo_detail_supply.quantity) AS total_supply 
 , isnull(dt_ms_memo_header.ms_supplier_customer_id,-1) as ms_supplier_customer_id
 , isnull(dt_ms_supplier_customer.supplier_customer_code,'') as supplier_customer_code
 , isnull(dt_ms_supplier_customer.supplier_customer_name,'') as supplier_customer_name
 , isnull(dt_ms_supplier_customer.is_affilete,0) as is_affilete
 , isnull(dt_ms_memo_header.is_move_business_date ,0)
 , dt_ms_memo_header.ms_date_business_id  
 , dt_ms_memo_header.ms_date_business_id_from    

     FROM            enum_ms_memo_type INNER JOIN
                         dt_ms_item INNER JOIN
                         dt_ms_memo_detail_supply INNER JOIN
                         dt_ms_memo_detail ON dt_ms_memo_detail_supply.ms_memo_detail_id = dt_ms_memo_detail.ms_memo_detail_id INNER JOIN
                         dt_ms_memo_header ON dt_ms_memo_detail.ms_memo_header_id = dt_ms_memo_header.ms_memo_header_id INNER JOIN
                         dt_ms_memo_header_h_status ON dt_ms_memo_header.ms_memo_header_h_status_id = dt_ms_memo_header_h_status.ms_memo_header_h_status_id INNER JOIN
                         dt_ms_item_in_detail ON dt_ms_memo_detail_supply.supply_id_source = dt_ms_item_in_detail.ms_in_item_detail_id ON dt_ms_item.ms_item_id = dt_ms_item_in_detail.ms_item_id ON 
                         enum_ms_memo_type.ms_memo_type_id = dt_ms_memo_header.ms_memo_type_id INNER JOIN
                         dt_ms_item_daily_out_move_avg ON dt_ms_memo_detail.ms_item_out_move_avg_id = dt_ms_item_daily_out_move_avg.ms_item_out_move_avg_id INNER JOIN
                         dt_ms_date_business ON dt_ms_item_daily_out_move_avg.ms_date_business_id = dt_ms_date_business.ms_date_business_id INNER 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 INNER JOIN
                         dt_ms_item_base_h_report_car_line_sum ON dt_ms_item_base.ms_item_base_h_report_car_line_sum_id = dt_ms_item_base_h_report_car_line_sum.ms_item_base_h_report_car_line_sum_id INNER JOIN
                         dt_ms_report_car_line_sum ON dt_ms_item_base_h_report_car_line_sum.ms_report_car_line_sum_id = dt_ms_report_car_line_sum.ms_report_car_line_sum_id LEFT OUTER JOIN
                         dt_ms_supplier_customer ON dt_ms_memo_header.ms_supplier_customer_id = dt_ms_supplier_customer.ms_supplier_customer_id
        WHERE        
  (dt_ms_memo_detail_supply.supply_mode_source = 0) 
  AND (dt_ms_memo_header_h_status.ms_memo_header_status_id = 1)
  AND (dt_ms_date_business.ms_date_business <= @business_date) and dt_ms_date_business.ms_date_business >@end_date_sta
     AND (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) 
  AND (dt_ms_item_daily_out_move_avg.ms_cost_final_avg_id IS NULL OR dt_ms_item_daily_out_move_avg.ms_cost_final_avg_id = - 1) 
 
 GROUP BY    dt_ms_memo_header.ms_memo_type_id
 , enum_ms_memo_type.ms_memo_type_name
 , dt_ms_memo_header.doc_no 
 , dt_ms_memo_header.doc_date   
 , isnull(dt_ms_item_base_h_report_car_line_sum.ms_report_car_line_sum_id,-1)  
 , isnull(dt_ms_report_car_line_sum.ms_report_car_line_sum_name,'')  
 , dt_ms_item_in_detail.ms_item_id
 , dt_ms_item.part_no
 , dt_ms_item.part_name 
 , isnull(dt_ms_memo_header.ms_supplier_customer_id,-1) 
 , isnull(dt_ms_supplier_customer.supplier_customer_code,'')  
 , isnull(dt_ms_supplier_customer.supplier_customer_name,'') 
 , isnull(dt_ms_supplier_customer.is_affilete,0)
 , isnull(dt_ms_memo_header.is_move_business_date ,0)
 , dt_ms_memo_header.ms_date_business_id  
 , dt_ms_memo_header.ms_date_business_id_from    
  
   
 --- AJUSTMENT
 insert into @tp_ms_avg_to_adjustment(
 ms_item_id   
 ,part_no 
 ,part_name  
 ,category_id  
 ,category_name  
 ,total
 ) 

 SELECT        
   dt_ms_item_in_detail.ms_item_id
 , dt_ms_item.part_no
 , dt_ms_item.part_name 
 , isnull(dt_ms_item_base_h_report_car_line_sum.ms_report_car_line_sum_id,-1) as category_id
 , dt_ms_report_car_line_sum.ms_report_car_line_sum_name   as category_name
 , SUM(dt_ms_item_in_detail.quantity_pakai) AS total_supply 
        
 FROM            dt_ms_item_base_h_report_car_line_sum INNER JOIN
                         dt_ms_report_car_line_sum ON dt_ms_item_base_h_report_car_line_sum.ms_report_car_line_sum_id = dt_ms_report_car_line_sum.ms_report_car_line_sum_id RIGHT OUTER JOIN
                         dt_ms_item_base ON dt_ms_item_base_h_report_car_line_sum.ms_item_base_h_report_car_line_sum_id = dt_ms_item_base.ms_item_base_h_report_car_line_sum_id RIGHT OUTER JOIN
                         dt_ms_item_in_detail INNER JOIN
                         dt_ms_adj_sto ON dt_ms_item_in_detail.id_source_header = dt_ms_adj_sto.ms_adj_sto_id INNER JOIN
                         dt_ms_adj_sto_h_status ON dt_ms_adj_sto.ms_adj_sto_h_status_id = dt_ms_adj_sto_h_status.ms_adj_sto_h_status_id INNER JOIN
                         dt_ms_item ON dt_ms_item_in_detail.ms_item_id = dt_ms_item.ms_item_id INNER JOIN
                         dt_ms_item_daily_out_move_avg ON dt_ms_item_in_detail.ms_item_out_move_avg_id = dt_ms_item_daily_out_move_avg.ms_item_out_move_avg_id INNER JOIN
                         dt_ms_date_business ON dt_ms_item_daily_out_move_avg.ms_date_business_id = dt_ms_date_business.ms_date_business_id ON dt_ms_item_base.ms_item_base_no = dt_ms_item.ms_item_base_no AND 
                         dt_ms_item_base.ms_item_base_no = dt_ms_item.ms_item_base_no
  WHERE        
 (dt_ms_adj_sto_h_status.status_adj_sto_id = 1) 
 --AND (dt_ms_item_in_detail.quantity_pakai < 0)
 AND (dt_ms_item_in_detail.mode_source = 3) 
 AND (dt_ms_adj_sto.adj_sto_mode_id = 0)
  AND (dt_ms_date_business.ms_date_business <= @business_date) and dt_ms_date_business.ms_date_business >@end_date_sta
   AND (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) 
 AND (dt_ms_item_daily_out_move_avg.ms_cost_final_avg_id IS NULL OR dt_ms_item_daily_out_move_avg.ms_cost_final_avg_id = - 1) 
 group by dt_ms_item_in_detail.ms_item_id
 , dt_ms_item.part_no
 , dt_ms_item.part_name 
 , isnull(dt_ms_item_base_h_report_car_line_sum.ms_report_car_line_sum_id,-1) 
 , dt_ms_report_car_line_sum.ms_report_car_line_sum_name  


 --- STO
 insert into @tp_ms_avg_to_sto(
  ms_item_id   
 ,part_no 
 ,part_name  
 ,category_id  
 ,category_name  
 ,total
 )

 SELECT        
 dt_ms_item_in_detail.ms_item_id
 , dt_ms_item.part_no
 , dt_ms_item.part_name 
 , isnull(dt_ms_item_base_h_report_car_line_sum.ms_report_car_line_sum_id,-1) as category_id
 , dt_ms_report_car_line_sum.ms_report_car_line_sum_name   as category_name
 , SUM(abs(dt_ms_item_in_detail.quantity_pakai) ) AS total_supply 
 FROM            dt_ms_item_base_h_report_car_line_sum INNER JOIN
                         dt_ms_report_car_line_sum ON dt_ms_item_base_h_report_car_line_sum.ms_report_car_line_sum_id = dt_ms_report_car_line_sum.ms_report_car_line_sum_id RIGHT OUTER JOIN
                         dt_ms_item_base ON dt_ms_item_base_h_report_car_line_sum.ms_item_base_h_report_car_line_sum_id = dt_ms_item_base.ms_item_base_h_report_car_line_sum_id RIGHT OUTER JOIN
                         dt_ms_item_in_detail INNER JOIN
                         dt_ms_adj_sto ON dt_ms_item_in_detail.id_source_header = dt_ms_adj_sto.ms_adj_sto_id INNER JOIN
                         dt_ms_adj_sto_h_status ON dt_ms_adj_sto.ms_adj_sto_h_status_id = dt_ms_adj_sto_h_status.ms_adj_sto_h_status_id INNER JOIN
                         dt_ms_item ON dt_ms_item_in_detail.ms_item_id = dt_ms_item.ms_item_id INNER JOIN
                         dt_ms_item_daily_out_move_avg ON dt_ms_item_in_detail.ms_item_out_move_avg_id = dt_ms_item_daily_out_move_avg.ms_item_out_move_avg_id INNER JOIN
                         dt_ms_date_business ON dt_ms_item_daily_out_move_avg.ms_date_business_id = dt_ms_date_business.ms_date_business_id ON dt_ms_item_base.ms_item_base_no = dt_ms_item.ms_item_base_no AND 
                         dt_ms_item_base.ms_item_base_no = dt_ms_item.ms_item_base_no
  WHERE        
 (dt_ms_adj_sto_h_status.status_adj_sto_id = 1) 
 --AND (dt_ms_item_in_detail.quantity_pakai < 0)
 AND (dt_ms_item_in_detail.mode_source = 4) 
 AND (dt_ms_adj_sto.adj_sto_mode_id = 1)
 AND (dt_ms_date_business.ms_date_business <= @business_date) and dt_ms_date_business.ms_date_business >@end_date_sta
 AND (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) 
 AND (dt_ms_item_daily_out_move_avg.ms_cost_final_avg_id IS NULL OR dt_ms_item_daily_out_move_avg.ms_cost_final_avg_id = - 1) 
 group by dt_ms_item_in_detail.ms_item_id
 , dt_ms_item.part_no
 , dt_ms_item.part_name 
 , isnull(dt_ms_item_base_h_report_car_line_sum.ms_report_car_line_sum_id,-1)  
 , dt_ms_report_car_line_sum.ms_report_car_line_sum_name    


 ;WITH data_item (ms_item_id, part_no, part_name,uom,ms_item_status_id,ms_item_custom_no
     ,ms_item_custom_name,base_no,base_name,category_id,category_name
     ,sta_qty,sta_price_idr,sta_price_usd)  
 AS  
 -- Define the CTE query.
 (  
  SELECT        
  dt_ms_item.ms_item_id
  , dt_ms_item.part_no
  , dt_ms_item.part_name
  , dt_ms_item.uom
  , dt_ms_item_h_status.ms_item_status_id
  , dt_ms_item.ms_item_custom_no
  , dt_ms_item_custom.ms_item_custom_name
  , dt_ms_item.ms_item_base_no
  , dt_ms_item_base.ms_item_base_name
 , isnull(dt_ms_item_base_h_report_car_line_sum.ms_report_car_line_sum_id,-1) as category_id
 , isnull(dt_ms_report_car_line_sum.ms_report_car_line_sum_name,'')  as category_name 
  , COALESCE(vw_ms_item_final_cost_last.end_qty,vw_ms_item_beginning_balance.end_qty ,0) as sta_qty
  ----, COALESCE(vw_ms_item_final_cost_last.end_price,vw_ms_item_beginning_balance.end_price ,0) as sta_price_idr-- as sta_price_idr
  ----,COALESCE(vw_ms_item_final_cost_last.end_price_usd,vw_ms_item_beginning_balance.end_price_usd ,0) as sta_price_usd--sta_price_usd
  ,case when COALESCE(vw_ms_item_final_cost_last.end_qty,vw_ms_item_beginning_balance.end_qty ,0) =0 then 0 else  COALESCE(vw_ms_item_final_cost_last.end_price,vw_ms_item_beginning_balance.end_price ,0)end  as sta_price_idr-- as sta_price_idr
  ,case when COALESCE(vw_ms_item_final_cost_last.end_qty,vw_ms_item_beginning_balance.end_qty ,0) =0 then 0 else  COALESCE(vw_ms_item_final_cost_last.end_price_usd,vw_ms_item_beginning_balance.end_price_usd ,0) end as sta_price_usd--sta_price_usd
 FROM            dt_ms_item_h_status INNER JOIN
                         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 ON dt_ms_item_h_status.ms_item_h_status_id = dt_ms_item.ms_item_h_status_id LEFT OUTER JOIN
                         vw_ms_item_final_cost_last ON dt_ms_item.ms_item_id = vw_ms_item_final_cost_last.ms_item_id LEFT OUTER JOIN
                         vw_ms_item_beginning_balance ON dt_ms_item.ms_item_id = vw_ms_item_beginning_balance.ms_item_id 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_report_car_line_sum INNER JOIN
                         dt_ms_item_base_h_report_car_line_sum ON dt_ms_report_car_line_sum.ms_report_car_line_sum_id = dt_ms_item_base_h_report_car_line_sum.ms_report_car_line_sum_id ON 
                         dt_ms_item_base.ms_item_base_h_report_car_line_sum_id = dt_ms_item_base_h_report_car_line_sum.ms_item_base_h_report_car_line_sum_id

 )  , data_in (ms_item_id, in_qty, in_amount_idr,in_amount_usd)  
 AS   
 (   
  SELECT        vw_business_date_item_in_sum_bc_in.ms_item_id
  , SUM(vw_business_date_item_in_sum_bc_in.total_qty_pakai) AS total_qty_avg_move
  , SUM(vw_business_date_item_in_sum_bc_in.total_amont_idr) AS price_avg_move_idr
  , SUM(vw_business_date_item_in_sum_bc_in.total_amont_usd) AS price_avg_move_usd
  FROM            vw_business_date_item_in_sum_bc_in INNER JOIN
       dt_ms_date_business ON vw_business_date_item_in_sum_bc_in.ms_date_business_id = dt_ms_date_business.ms_date_business_id
  WHERE        (vw_business_date_item_in_sum_bc_in.ms_item_h_price_id = - 1 OR vw_business_date_item_in_sum_bc_in.ms_item_h_price_id IS NULL)
   AND (dt_ms_date_business.ms_date_business <= @business_date) and dt_ms_date_business.ms_date_business >@end_date_sta
       --AND (NULLIF (vw_business_date_item_in_sum_bc_in.total_amont_usd, 0) IS NOT NULL)
  GROUP BY vw_business_date_item_in_sum_bc_in.ms_item_id  
 )  
 , out_carline (ms_item_id, total)  
 AS   
 (  select ms_item_id, sum(total_out) as total  from  @tp_ms_avg_stock_to_memo
  group by ms_item_id
 )  
 , out_memo (ms_item_id, total)  
 AS  
 -- Define the CTE query.
 (   
  select ms_item_id, sum(qty_total) as total  from  @tp_ms_item_out_to_carline 
  group by ms_item_id
 )  

 , ajustment (ms_item_id, total)  
 AS  
 -- Define the CTE query.
 (   
  select ms_item_id, sum(total) as total  from  @tp_ms_avg_to_adjustment 
  group by ms_item_id
 )  
 , sto (ms_item_id, total)  
 AS  
 -- Define the CTE query.
 (   
  select ms_item_id, sum(total) as total  from  @tp_ms_avg_to_sto 
  group by ms_item_id
 )  
 , retur (ms_item_id, total)  
 AS  
 -- Define the CTE query.
 (   
  select ms_item_id, sum(qty_pakai) as total  from  @tp_ms_avg_in_from_retur 
  group by ms_item_id
 )  
   
 insert into  @tp_ms_item_avg_pre (
   ms_item_id 
 , part_no  
 , part_name 
 , uom  
 , base_no  
 , base_name
 , category_id  
 , category_name 
 , sta_qty  
 , sta_amount_idr   
 , sta_amount_usd   
 , in_qty  
 , in_amount_idr   
 , in_amount_usd   
 , final_avg_price   
 , final_avg_price_usd  
 , out_carline_qty 
 , out_carline_amount      
 , out_memo_qty  
 , out_memo_amount     
 , adj_qty  
 , adj_amount     
 , sto_qty  
 , sto_amount 
 , retur_qty  
 , retur_amount 
 )

 SELECT 
   data_item.ms_item_id 
 , data_item.part_no  
 , data_item.part_name 
 , data_item.uom  
 , data_item.base_no  
 , data_item.base_name 
 , data_item.category_id  
 , data_item.category_name 
 , data_item.sta_qty  
 , data_item.sta_price_idr   
 , data_item.sta_price_usd   
 , isnull(data_in.in_qty,0)
 , isnull(data_in.in_amount_idr,0)
 , isnull(data_in.in_amount_usd ,0)
 , case when data_item.sta_qty  + isnull(data_in.in_qty,0) =0 then 0 else (data_item.sta_price_idr + isnull(data_in.in_amount_idr,0))/(data_item.sta_qty  + isnull(data_in.in_qty,0))  end    final_avg_price   
 , case when data_item.sta_qty  + isnull(data_in.in_qty,0) =0 then 0 else (data_item.sta_price_usd + isnull(data_in.in_amount_usd,0))/(data_item.sta_qty  + isnull(data_in.in_qty,0))  end    final_avg_price_usd   
   
  ,  isnull(out_carline.total,0)  as out_carline_qty
 , case when isnull(out_carline.total,0) =0 then 0 else isnull(out_carline.total,0)    * (case when data_item.sta_qty  + isnull(data_in.in_qty,0) =0 then 0 else (data_item.sta_price_usd + isnull(data_in.in_amount_usd,0))/(data_item.sta_qty  + isnull(data_in.in_qty,0))  end) end as out_carline_amount  
 
 ,  isnull(out_memo.total,0)  as out_memo_qty
 , case when isnull(out_memo.total,0) =0 then 0 else isnull(out_memo.total,0)    * (case when data_item.sta_qty  + isnull(data_in.in_qty,0) =0 then 0 else (data_item.sta_price_usd + isnull(data_in.in_amount_usd,0))/(data_item.sta_qty  + isnull(data_in.in_qty,0))  end) end as out_memo_amount  
 
 ,  isnull(ajustment.total,0)  as adj_qty
 , case when isnull(ajustment.total,0) =0 then 0 else isnull(ajustment.total,0)    * (case when data_item.sta_qty  + isnull(data_in.in_qty,0) =0 then 0 else (data_item.sta_price_usd + isnull(data_in.in_amount_usd,0))/(data_item.sta_qty  + isnull(data_in.in_qty,0))  end) end as adj_amount  
 
 ,  isnull(sto.total,0)   as sto_qty
 , case when isnull(sto.total,0) =0 then 0 else isnull(sto.total,0)    * (case when data_item.sta_qty  + isnull(data_in.in_qty,0) =0 then 0 else (data_item.sta_price_usd + isnull(data_in.in_amount_usd,0))/(data_item.sta_qty  + isnull(data_in.in_qty,0))  end) end as sto_amount  
 
 ,  isnull(retur.total,0)   as retur_qty
 , case when isnull(retur.total,0) =0 then 0 else isnull(retur.total,0)    * (case when data_item.sta_qty  + isnull(data_in.in_qty,0) =0 then 0 else (data_item.sta_price_usd + isnull(data_in.in_amount_usd,0))/(data_item.sta_qty  + isnull(data_in.in_qty,0))  end) end as retur_amount  
    FROM            data_item LEFT OUTER JOIN
        sto ON data_item.ms_item_id = sto.ms_item_id LEFT OUTER JOIN
        ajustment ON data_item.ms_item_id = ajustment.ms_item_id LEFT OUTER JOIN
        out_carline ON data_item.ms_item_id = out_carline.ms_item_id LEFT OUTER JOIN
        out_memo ON data_item.ms_item_id = out_memo.ms_item_id LEFT OUTER JOIN
        retur ON data_item.ms_item_id = retur.ms_item_id LEFT OUTER JOIN
        data_in ON data_item.ms_item_id = data_in.ms_item_id
  

  
 --- LIST DETAIL IN DARI BC
 insert into @tp_ms_avg_in_from_bc (
    ms_in_item_detail_id
    ,category_id  
    ,category_name 
    ,ms_date_business_id  
    ,ms_date_business_id_from  
    ,ms_item_id  
    ,part_no 
    ,part_name  
    ,qty_pakai  
    ,uom  
    ,ori_amount_idr  
    ,ori_amount_usd  
    ,bc_type
    ,car 
    ,bc_no 
    ,bc_date 
    ,receive_no  
    ,receive_date   
    ,po_no  
    ,po_date   
    ,supplier_customer_id  
    ,supplier_customer_code  
    ,supplier_customer_name  
    ,is_affilete  
    ,invoice_no
    ,invoice_date
    ,seri  
    ,is_adjustment 
    ,is_molts  
    ,is_import  
    ,is_40   
    ) 
  SELECT  
   dt_ms_item_in_detail.ms_in_item_detail_id       
   ,data_price_item.category_id
   ,data_price_item.category_name
   ,dt_ms_item_in_detail.ms_date_business_id
   ,dt_ms_item_in_detail.ms_date_business_id_from
   ,dt_ms_item_daily_in_move_avg.ms_item_id
   ,data_price_item.part_no
   ,data_price_item.part_name
   ,dt_ms_item_in_detail.quantity_pakai as  qty_pakai
   ,data_price_item.uom
   ,dt_ms_item_in_detail.total_amont_idr as  ori_amount_idr
   ,dt_ms_item_in_detail.total_amont_usd as ori_amount_usd
    ,dt_ms_tpb_in_header.KODE_DOKUMEN_PABEAN 
   ,dt_ms_tpb_in_header.NOMOR_AJU  
   ,dt_ms_tpb_in_header.NOMOR_DAFTAR 
   ,dt_ms_tpb_in_header.TANGGAL_DAFTAR 
   ,dt_ms_tpb_in_header.receive_no
   ,dt_ms_tpb_in_header.receive_date  
   , case when  dt_ms_tpb_in_header.KODE_DOKUMEN_PABEAN='40' then  
        (SELECT        TOP (1) NOMOR_DOKUMEN
      FROM            dt_ms_tpb_in_dokumen
      WHERE        (KODE_JENIS_DOKUMEN = '380') AND (dt_ms_tpb_in_dokumen.ID_HEADER =dt_ms_tpb_in_header.ID_HEADER )
      ORDER BY ID
      )
    else  dt_ms_tpb_in_barang.po_no end  as po_no
   , case when  dt_ms_tpb_in_header.KODE_DOKUMEN_PABEAN='40' then  
        (SELECT        TOP (1) TANGGAL_DOKUMEN
      FROM            dt_ms_tpb_in_dokumen
      WHERE        (KODE_JENIS_DOKUMEN = '380') AND (dt_ms_tpb_in_dokumen.ID_HEADER =dt_ms_tpb_in_header.ID_HEADER )
      ORDER BY ID
      )
    else  dt_ms_tpb_in_barang.po_date end as po_date 
   
   ,dt_ms_tpb_in_header.mapping_supplier_id 
   ,dt_ms_supplier_customer.supplier_customer_code
   ,dt_ms_supplier_customer.supplier_customer_name
   ,dt_ms_supplier_customer.is_affilete
   ,      case when dt_ms_tpb_in_header.mode_source =5 then  -- manual invoice
       dt_ms_manual_invoice.invoice_no
     when dt_ms_tpb_in_header.mode_source =3 then -- molts
       dt_ms_molts_invoice.invoice_no 
     --when dt_ms_tpb_in_header.mode_source =1 then -- ceisa
     --''
     else (SELECT        TOP (1) NOMOR_DOKUMEN
      FROM            dt_ms_tpb_in_dokumen
      WHERE        (KODE_JENIS_DOKUMEN = '380') AND (dt_ms_tpb_in_dokumen.ID_HEADER =dt_ms_tpb_in_header.ID_HEADER )
      ORDER BY ID
      )
   
     end  as invoice_no

   ,      case when dt_ms_tpb_in_header.mode_source =5 then  -- manual invoice
       dt_ms_manual_invoice.invoice_date
     when dt_ms_tpb_in_header.mode_source =3 then -- molts
       dt_ms_molts_invoice.invoice_date 
     --when dt_ms_tpb_in_header.mode_source =1 then -- ceisa
     --null
     else 
     (SELECT        TOP (1) TANGGAL_DOKUMEN
     FROM            dt_ms_tpb_in_dokumen
     WHERE        (KODE_JENIS_DOKUMEN = '380') AND (dt_ms_tpb_in_dokumen.ID_HEADER =dt_ms_tpb_in_header.ID_HEADER )
     ORDER BY ID
     )
     end  as invoice_date
     ,dt_ms_tpb_in_barang.SERI_BARANG 
     ,dt_ms_item_in_detail.is_adjustment 
     
   ,      case  when not(dt_ms_tpb_in_header.mode_source =3) and  (dt_ms_tpb_in_header.KODE_DOKUMEN_PABEAN='23' or  dt_ms_tpb_in_header.KODE_DOKUMEN_PABEAN='27'  or  dt_ms_tpb_in_header.KODE_DOKUMEN_PABEAN='271') then 0
     else  1
     end  as is_molts
  
     
   ,      case  when dt_ms_tpb_in_header.KODE_DOKUMEN_PABEAN='23' or  dt_ms_tpb_in_header.KODE_DOKUMEN_PABEAN='27'  or  dt_ms_tpb_in_header.KODE_DOKUMEN_PABEAN='271'  then 1 
     else  0
     end  as is_import
     
   ,      case  when dt_ms_tpb_in_header.KODE_DOKUMEN_PABEAN='40' then 1 
     else  0
     end  as is_40
      
   FROM            dt_ms_molts_invoice_detail INNER JOIN
                         dt_ms_molts_invoice ON dt_ms_molts_invoice_detail.ms_molts_invoice_id = dt_ms_molts_invoice.ms_molts_invoice_id RIGHT OUTER JOIN
                         dt_ms_tpb_in_header_h_status INNER JOIN
                         dt_ms_tpb_in_header ON dt_ms_tpb_in_header_h_status.dt_ms_tpb_in_header_h_status_id = dt_ms_tpb_in_header.dt_ms_tpb_in_header_h_status_id INNER JOIN
                         dt_ms_tpb_in_barang ON dt_ms_tpb_in_header.ID_HEADER = dt_ms_tpb_in_barang.ID_HEADER INNER JOIN
                         dt_ms_item_daily_in_move_avg INNER JOIN
                         dt_ms_item_in_detail ON dt_ms_item_daily_in_move_avg.ms_item_in_move_avg_id = dt_ms_item_in_detail.ms_item_in_move_avg_id ON dt_ms_tpb_in_barang.ID = dt_ms_item_in_detail.id_source 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
                         dt_ms_supplier_customer ON dt_ms_tpb_in_header.mapping_supplier_id = dt_ms_supplier_customer.ms_supplier_customer_id INNER JOIN
                         @tp_ms_item_avg_pre AS data_price_item ON dt_ms_item_in_detail.ms_item_id = data_price_item.ms_item_id ON 
                         dt_ms_molts_invoice_detail.ms_molts_invoice_detail_id  = dt_ms_tpb_in_barang.id_source  LEFT OUTER JOIN
                         dt_ms_manual_invoice INNER JOIN
                         dt_ms_manual_invoice_detail ON dt_ms_manual_invoice.ms_manual_invoice_id = dt_ms_manual_invoice_detail.ms_manual_invoice_id ON dt_ms_tpb_in_barang.id_source = dt_ms_manual_invoice_detail.ms_manual_invoice_detail_id
        WHERE        
      (dt_ms_tpb_in_header_h_status.status_tpb_in_id = 3) 
      AND ((dt_ms_item_in_detail.mode_source = 2)) 
      AND (dt_ms_item_daily_in_move_avg.ms_item_h_price_id = - 1 OR dt_ms_item_daily_in_move_avg.ms_item_h_price_id IS NULL)
       AND (dt_ms_date_business.ms_date_business <= @business_date) and dt_ms_date_business.ms_date_business >@end_date_sta
        --AND (( NULLIF (dt_ms_item_daily_in_move_avg.price_avg_move_idr, 0) IS NOT NULL AND dt_ms_item_daily_in_move_avg.ms_cost_final_avg_id <>0) )
      -- AND (( NULLIF (dt_ms_item_daily_in_move_avg.price_avg_move_usd, 0) IS NOT NULL AND dt_ms_item_daily_in_move_avg.ms_cost_final_avg_id <>0))
      --AND (( NULLIF (dt_ms_item_daily_in_move_avg.total_qty_avg_move, 0) IS NOT NULL AND dt_ms_item_daily_in_move_avg.ms_cost_final_avg_id <>0) )
      AND (dt_ms_item_daily_in_move_avg.ms_cost_final_avg_id=-1 or dt_ms_item_daily_in_move_avg.ms_cost_final_avg_id is null)
      order by dt_ms_tpb_in_header.ID_HEADER ,dt_ms_tpb_in_header.TANGGAL_DAFTAR ,dt_ms_tpb_in_header.NOMOR_DAFTAR ,dt_ms_tpb_in_barang.SERI_BARANG 
 
  
 

  insert into @tp_ms_avg_catergory_detail_in_out
  (
    category_id  
  , category_name   
  , category_initial  
  , category_color 
  , show   

  , order_by   
  , sta_qty  
  , sta_amount_usd   
  , in_qty  
  , in_amount_usd  
    
  , out_qty      
  , out_amount    
  , adj_qty    
  , adj_amount    
  , end_qty  

  , end_amount  
 )
  select 
  xsource.category_id 
    ,xsource.category_name  
    ,dt_ms_report_car_line_sum.code_sheet 
    ,dt_ms_report_car_line_sum.ms_report_car_line_sum_color
 ,dt_ms_report_car_line_sum.show_on_report

 ,dt_ms_report_car_line_sum.order_column  
 ,sum(isnull(xsource.sta_qty ,0))
 ,sum(isnull(xsource.sta_amount_usd ,0))

 ,sum(isnull(xsource.in_qty  ,0) )
 ,sum(isnull(xsource.in_amount_usd  ,0)) 


 ,sum(isnull(xsource.out_carline_qty ,0) + isnull(xsource.out_memo_qty ,0))
 ,sum(isnull(xsource.out_carline_amount ,0) + isnull(xsource.out_memo_amount ,0)) 

 ,sum(isnull(xsource.adj_qty  ,0)+isnull(xsource.sto_qty  ,0)+isnull(xsource.retur_qty  ,0) )
 ,sum(isnull(xsource.adj_amount  ,0)+isnull(xsource.sto_amount  ,0)+isnull(xsource.retur_amount  ,0) ) 
   
 ,sum( (isnull(xsource.sta_qty  ,0)  + isnull(xsource.in_qty ,0)  + isnull(xsource.adj_qty  ,0)+isnull(xsource.sto_qty  ,0)+isnull(xsource.retur_qty  ,0) )
    - (isnull(xsource.out_carline_qty ,0) + isnull(xsource.out_memo_qty ,0)) ) 

 ,sum( ((isnull(xsource.sta_qty  ,0)  + isnull(xsource.in_qty ,0)  + isnull(xsource.adj_qty  ,0)+isnull(xsource.sto_qty  ,0)+isnull(xsource.retur_qty  ,0) )
    - (isnull(xsource.out_carline_qty ,0) + isnull(xsource.out_memo_qty ,0))) * isnull(final_avg_price_usd,0)) 

 from @tp_ms_item_avg_pre as xsource  INNER JOIN
     dt_ms_report_car_line_sum ON xsource.category_id = dt_ms_report_car_line_sum.ms_report_car_line_sum_id 
 where not(xsource.category_id =-1) 
 and dt_ms_report_car_line_sum.show_on_report =1  
 group by  
  xsource.category_id 
    ,xsource.category_name  
     ,dt_ms_report_car_line_sum.code_sheet 
    ,dt_ms_report_car_line_sum.ms_report_car_line_sum_color
 ,dt_ms_report_car_line_sum.show_on_report
 ,dt_ms_report_car_line_sum.order_column  
 order by xsource.category_id
    
end
else
begin
  
declare  @check_ms_cost_final_avg_id bigint
, @check_final_avg_name varchar(255)
, @check_start_date date 
, @check_end_date date
, @check_info varchar(1000)
, @bin_item_avg_pre varbinary(MAX)  
,@bin_avg_to_sto varbinary(MAX)  
,@bin_avg_to_adjustment varbinary(MAX)  
,@bin_avg_stock_to_memo varbinary(MAX)  
,@bin_avg_out_to_carline varbinary(MAX)  
,@bin_avg_in_from_sto varbinary(MAX)  
,@bin_avg_in_from_adjustment varbinary(MAX)  
,@bin_avg_in_from_retur varbinary(MAX)  
,@bin_avg_in_from_bc varbinary(MAX)  
,@bin_avg_catergory_detail_in_out varbinary(MAX)  
    
--declare @rsx varbinary(max)=dbo.CompressXmlToByte(@kk,9)
--select @rsx
--declare @kk2 xml =dbo.DecompressToString(@rsx,9)
--select @kk2


SELECT        TOP (1) 
 @check_ms_cost_final_avg_id=ms_cost_final_avg_id
, @check_final_avg_name=final_avg_name
, @check_start_date=start_date
, @check_end_date=end_date
, @check_info=info
, @bin_item_avg_pre=item_avg_pre 
, @bin_avg_to_sto=avg_to_sto 
, @bin_avg_to_adjustment=avg_to_adjustment 
, @bin_avg_stock_to_memo=avg_stock_to_memo 
, @bin_avg_out_to_carline=avg_out_to_carline 
, @bin_avg_in_from_sto=avg_in_from_sto 
, @bin_avg_in_from_adjustment=avg_in_from_adjustment 
, @bin_avg_in_from_retur=avg_in_from_retur 
, @bin_avg_in_from_bc=avg_in_from_bc
, @bin_avg_catergory_detail_in_out=avg_catergory_detail_in_out
FROM            dt_ms_cost_final_avg
WHERE        (ms_cost_final_avg_id = @header_id)
 
set  @REPORT_NAME=@check_final_avg_name
set  @REPORT_START =@check_start_date
set @REPORT_STOP =@check_end_date

if @check_ms_cost_final_avg_id is not null
begin

    
DECLARE @DocHandle int; 
declare @xml_tmp xml
if @bin_item_avg_pre is not null and DATALENGTH(@bin_item_avg_pre)>0
begin 
set @xml_tmp=dbo.DecompressToString(@bin_item_avg_pre,9)

EXEC sp_xml_preparedocument @DocHandle OUTPUT, @xml_tmp;

 INSERT into @tp_ms_item_avg_pre 
 SELECT  *
 FROM OPENXML (@DocHandle, '//root/r'  )
 WITH (    
   ms_item_id bigint 'ms_item_id'    
 , part_no varchar(255) 'part_no'
 , part_name varchar(255) 'part_name'
 , uom varchar(255) 'uom'
 , base_no varchar(255) 'base_no'
 , base_name varchar(255)  'base_name'
 , category_id bigint 'category_id'
 , category_name varchar(255)  'category_name'
 , sta_qty decimal(38,5) 'sta_qty'
 , sta_amount_idr decimal(38,5) 'sta_amount_idr'
 , sta_amount_usd decimal(38,5) 'sta_amount_usd'
 , in_qty decimal(38,5) 'in_qty'
 , in_amount_idr decimal(38,5) 'in_amount_idr'
 , in_amount_usd decimal(38,5) 'in_amount_usd'
 , final_avg_price decimal(38,5) 'final_avg_price'
 , final_avg_price_usd decimal(38,5) 'final_avg_price_usd'
 , out_carline_qty decimal(38,5) 'out_carline_qty'  
 , out_carline_amount decimal(38,5) 'out_carline_amount'   
 , out_memo_qty decimal(38,5) 'out_memo_qty'  
 , out_memo_amount decimal(38,5) 'out_memo_amount' 
 , adj_qty decimal(38,5)'adj_qty'
 , adj_amount decimal(38,5) 'adj_amount'   
 , sto_qty decimal(38,5) 'sto_qty'  
 , sto_amount decimal(38,5) 'sto_amount'    
 , retur_qty decimal(38,5) 'retur_qty'  
 , retur_amount decimal(38,5) 'retur_amount'  
 );

 EXEC sp_xml_removedocument @DocHandle; 

end

 
if @bin_avg_to_sto is not null and DATALENGTH(@bin_avg_to_sto)>0
begin 
set @xml_tmp=dbo.DecompressToString(@bin_avg_to_sto,9)

EXEC sp_xml_preparedocument @DocHandle OUTPUT, @xml_tmp; ; 
 INSERT into @tp_ms_avg_to_sto ( 
   ms_item_id 
  ,part_no 
  ,part_name 
  ,category_id
  ,category_name
  ,total 
 )
 SELECT  *
 FROM OPENXML (@DocHandle, '//root/r'  )
 WITH (     
   ms_item_id bigint 'ms_item_id'
  ,part_no varchar(255) 'part_no'
  ,part_name varchar(255) 'part_name'
  ,category_id int 'category_id'
  ,category_name varchar(255)'category_name'
  ,total decimal(38,5)  'total'
 ); 
 EXEC sp_xml_removedocument @DocHandle; 
end
 
 
if @bin_avg_to_adjustment is not null and DATALENGTH(@bin_avg_to_adjustment)>0
begin 
set @xml_tmp=dbo.DecompressToString(@bin_avg_to_adjustment,9)
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @xml_tmp; ;
 
  INSERT into @tp_ms_avg_to_adjustment ( 
  ms_item_id 
  ,part_no 
  ,part_name 
  ,category_id
  ,category_name
  ,total 
 )
 SELECT  *
 FROM OPENXML (@DocHandle, '//root/r'  )
 WITH (     ms_item_id bigint 'ms_item_id'
  ,part_no varchar(255) 'part_no'
  ,part_name varchar(255) 'part_name'
  ,category_id int 'category_id'
  ,category_name varchar(255)'category_name'
  ,total decimal(38,5)  'total'
 );
  EXEC sp_xml_removedocument @DocHandle; 
end
   
if @bin_avg_stock_to_memo is not null and DATALENGTH(@bin_avg_stock_to_memo)>0
begin 

set @xml_tmp=dbo.DecompressToString(@bin_avg_stock_to_memo,9)
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @xml_tmp; 
 INSERT into @tp_ms_avg_stock_to_memo ( 
  ms_memo_type_id  
  ,ms_memo_type_name  
  ,memo_code  
  ,memo_date  
  ,ms_item_id  
  ,part_no  
  ,part_name  
  , uom  
  ,category_id  
  ,category_name  
  ,total_out  
  ,supplier_customer_id  
  ,supplier_customer_code 
  ,supplier_customer_name  
  ,is_affilete  
  ,is_adjustment 
  , ms_date_business_id  
  , ms_date_business_id_from  
 )
 SELECT  *
 FROM OPENXML (@DocHandle, '//root/r'  )
 WITH (    
   ms_memo_type_id int 'ms_memo_type_id'
  ,ms_memo_type_name varchar(255) 'ms_memo_type_name'
  ,memo_code varchar(255) 'memo_code'
  ,memo_date date 'memo_date'
  ,ms_item_id bigint  'ms_item_id'
  ,part_no varchar(255) 'part_no'
  ,part_name varchar(255)  'part_name'
  , uom varchar(255) 'uom'
  ,category_id int 'category_id'
  ,category_name varchar(255) 'category_name'
  ,total_out decimal(38,5)  'total_out'
  ,supplier_customer_id varchar(255)  'supplier_customer_id'
  ,supplier_customer_code varchar(255)  'supplier_customer_code'
  ,supplier_customer_name varchar(255)  'supplier_customer_name'
  ,is_affilete bit 'is_affilete'
  ,is_adjustment bit 'is_adjustment'
  , ms_date_business_id bigint 'ms_date_business_id'
  , ms_date_business_id_from bigint 'ms_date_business_id_from'
 ); 
 EXEC sp_xml_removedocument @DocHandle; 
end


if @bin_avg_out_to_carline is not null and DATALENGTH(@bin_avg_out_to_carline)>0
begin 

set @xml_tmp=dbo.DecompressToString(@bin_avg_out_to_carline,9)
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @xml_tmp ;

 INSERT into @tp_ms_item_out_to_carline ( 
    category_id  
  , category_name  
  , doc_no  
  , doc_date 
  , doc_info  
  , ms_type_prosses_id  
  ,ms_type_prosses_name  
  , ms_car_line_no   
  , ms_car_line_name   
  , part_no    
  , part_name    
  , qty_total   
  , ms_car_line_group_id  
  , ms_car_line_group   
  , remark    
  ,is_adjustment  
  , ms_date_business_id  
  , ms_date_business_id_from  
  , ms_item_id  
  , uom  
 )
 SELECT  *
 FROM OPENXML (@DocHandle, '//root/r'  )
 WITH (    
    category_id bigint 'category_id'
  , category_name varchar(255) 'category_name'
  , doc_no varchar(255) 'doc_no'
  , doc_date date  'doc_date'
  , doc_info  varchar(1000) 'doc_info'
  , ms_type_prosses_id int  'ms_type_prosses_id'
  , ms_type_prosses_name  varchar(255) 'ms_type_prosses_name'
  , ms_car_line_no  varchar(255) 'ms_car_line_no'
  , ms_car_line_name   varchar(255) 'ms_car_line_name'
  , part_no   varchar(255) 'part_no'
  , part_name   varchar(255) 'part_name'
  , qty_total  decimal(38,5)    'qty_total'
  , ms_car_line_group_id bigint 'ms_car_line_group_id'
  , ms_car_line_group  varchar(255)  'ms_car_line_group'
  , remark   varchar(1000) 'remark'
  ,is_adjustment bit 'is_adjustment'
  , ms_date_business_id bigint 'ms_date_business_id'
  , ms_date_business_id_from bigint 'ms_date_business_id_from'
  , ms_item_id bigint 'ms_item_id'
  , uom varchar(255) 'uom'
 ); 
 EXEC sp_xml_removedocument @DocHandle; 
end 

if @bin_avg_in_from_sto is not null and DATALENGTH(@bin_avg_in_from_sto)>0
begin 

set @xml_tmp=dbo.DecompressToString(@bin_avg_in_from_sto,9)
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @xml_tmp;  

 INSERT into @tp_ms_avg_in_from_sto 
 SELECT  *
 FROM OPENXML (@DocHandle, '//root/r'  )
 WITH (    
   ms_in_item_detail_id  bigint 'ms_in_item_detail_id'
  , category_id bigint 'category_id'
  , category_name varchar(255) 'category_name'
  , ms_date_business_id bigint 'ms_date_business_id'
  , ms_date_business_id_from bigint 'ms_date_business_id_from'
  , ms_item_id bigint 'ms_item_id'
  , part_no varchar(255) 'part_no'
  , part_name varchar(255)  'part_name'
  , qty_pakai decimal(38,5) 'qty_pakai'
  , uom  varchar(255) 'uom'
  , ori_amount_idr decimal(38,5) 'ori_amount_idr'
  , ori_amount_usd decimal(38,5)  'ori_amount_usd'
  , doc_code  varchar(255) 'doc_code'
  , doc_date  date 'doc_date'
  , info   varchar(1000) 'info'
  , bc_type  varchar(255) 'bc_type'
  , car   varchar(255) 'car'
  , bc_no  varchar(255) 'bc_no'
  , bc_date date 'bc_date'
  , seri int 'seri'
  , is_adjustment bit 'is_adjustment'
 ); 
 EXEC sp_xml_removedocument @DocHandle; 
end 


if @bin_avg_in_from_adjustment is not null and DATALENGTH(@bin_avg_in_from_adjustment)>0
begin 

set @xml_tmp=dbo.DecompressToString(@bin_avg_in_from_adjustment,9)
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @xml_tmp;   

 INSERT into @tp_ms_avg_in_from_adjustment 
 SELECT  *
 FROM OPENXML (@DocHandle, '//root/r'  )
 WITH (    
   ms_in_item_detail_id        bigint 'ms_in_item_detail_id'
  , category_id bigint 'category_id'
  , category_name varchar(255) 'category_name'
  , ms_date_business_id bigint 'ms_date_business_id'
  , ms_date_business_id_from bigint 'ms_date_business_id_from'
  , ms_item_id bigint 'ms_item_id'
  , part_no varchar(255) 'part_no'
  , part_name varchar(255)  'part_name'
  , qty_pakai decimal(38,5) 'qty_pakai'
  , uom  varchar(255) 'uom'
  , ori_amount_idr decimal(38,5) 'ori_amount_idr'
  , ori_amount_usd decimal(38,5)  'ori_amount_usd'
  , doc_code  varchar(255) 'doc_code'
  , doc_date  date 'doc_date'
  , info   varchar(1000) 'info'
  , bc_type  varchar(255) 'bc_type'
  , car   varchar(255) 'car'
  , bc_no  varchar(255) 'bc_no'
  , bc_date date 'bc_date'
  , seri int 'seri'
  , is_adjustment bit    'is_adjustment'
 ); 
 EXEC sp_xml_removedocument @DocHandle; 
end 


 
if @bin_avg_in_from_retur is not null and DATALENGTH(@bin_avg_in_from_retur)>0
begin 

set @xml_tmp=dbo.DecompressToString(@bin_avg_in_from_retur,9)
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @xml_tmp;   
 
 INSERT into @tp_ms_avg_in_from_retur 
 SELECT  *
 FROM OPENXML (@DocHandle, '//root/r'  )
 WITH (    
   ms_in_item_detail_id       bigint 'ms_in_item_detail_id'
  ,category_id bigint 'category_id'
  ,category_name varchar(255) 'category_name'
  ,ms_date_business_id bigint 'ms_date_business_id'
  ,ms_date_business_id_from bigint 'ms_date_business_id_from'
  ,ms_item_id bigint 'ms_item_id'
  ,part_no varchar(255) 'part_no'
  ,part_name varchar(255)  'part_name'
  ,uom  varchar(255) 'uom'
  ,qty_pakai  decimal(38,5) 'qty_pakai'
  ,retur_no  varchar(255) 'retur_no'
  ,retur_date  date 'retur_date'
  ,retur_info  varchar(1000) 'retur_info'
  ,is_adjustment bit    'is_adjustment'
 ); 
 EXEC sp_xml_removedocument @DocHandle; 
end 

 
if @bin_avg_in_from_bc is not null and DATALENGTH(@bin_avg_in_from_bc)>0
begin 
set @xml_tmp=dbo.DecompressToString(@bin_avg_in_from_bc,9)
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @xml_tmp;   
  
 INSERT into @tp_ms_avg_in_from_bc 
 SELECT  *
 FROM OPENXML (@DocHandle, '//root/r'  )
 WITH (    
   ms_in_item_detail_id bigint  'ms_in_item_detail_id'
  ,category_id bigint 'category_id'
  ,category_name varchar(255) 'category_name'
  ,ms_date_business_id bigint 'ms_date_business_id'
  ,ms_date_business_id_from bigint 'ms_date_business_id_from'
  ,ms_item_id bigint 'ms_item_id'
  ,part_no varchar(255) 'part_no'
  ,part_name varchar(255)  'part_name'
  ,uom  varchar(255) 'uom'
  ,qty_pakai decimal(38,5) 'qty_pakai'
  ,ori_amount_idr decimal(38,5) 'ori_amount_idr'
  ,ori_amount_usd decimal(38,5) 'ori_amount_usd'
  ,bc_type  varchar(255) 'bc_type'
  ,car   varchar(255) 'car'
  ,bc_no  varchar(255) 'bc_no'
  ,bc_date date 'bc_date'
  ,receive_no varchar(255) 'receive_no'
  ,receive_date  date 'receive_date'
  ,po_no varchar(255) 'po_no'
  ,po_date  date 'po_date'
  ,supplier_customer_id bigint 'supplier_customer_id'
  ,supplier_customer_code varchar(255) 'supplier_customer_code'
  ,supplier_customer_name varchar(255) 'supplier_customer_name'
  ,is_affilete int  'is_affilete'
  ,invoice_no varchar(1000) 'invoice_no'
  ,invoice_date date 'invoice_date'
  ,seri int 'seri'
  ,is_adjustment bit   'is_adjustment'
  ,is_molts bit 'is_molts'
  ,is_import bit 'is_import'
  ,is_40 bit 'is_40'
 ); 
 EXEC sp_xml_removedocument @DocHandle; 
end 

if @bin_avg_catergory_detail_in_out is not null and DATALENGTH(@bin_avg_catergory_detail_in_out)>0
begin 
set @xml_tmp=dbo.DecompressToString(@bin_avg_catergory_detail_in_out,9)
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @xml_tmp;   
  
   INSERT into @tp_ms_avg_catergory_detail_in_out 
 SELECT  *
 FROM OPENXML (@DocHandle, '//root/r'  )
 WITH (    
  category_id bigint 'category_id'
  , category_name  varchar(255)  'category_name'
  , category_initial  varchar(255)  'category_initial'
  , category_color  varchar(255)  'category_color'
  , show bit  'show'
  , order_by int  'order_by'
  , sta_qty decimal(38,5)    'sta_qty'
  , sta_amount_usd decimal(38,5)   'sta_amount_usd'
  , in_qty decimal(38,5) 'in_qty'   
  , in_amount_usd decimal(38,5)  'in_amount_usd' 
  , out_qty decimal(38,5)   'out_qty'  
  , out_amount decimal(38,5)  'out_amount'   
  , adj_qty decimal(38,5)   'adj_qty' 
  , adj_amount decimal(38,5)  'adj_amount'   
  , end_qty decimal(38,5) 'end_qty'
  , end_amount decimal(38,5) 'end_amount'
 ); 
 EXEC sp_xml_removedocument @DocHandle; 
end 
 
     
end 

end

  --- GROUPING BY SUPPLIER
 insert into @tp_ms_in_by_supplier_local(
  supplier_customer_id
    ,supplier_customer_code
    ,supplier_customer_name 
 ,category_id 
 ,in_qty
 ,in_amount
 ) 

 select   
   xsource.supplier_customer_id  
  ,xsource.supplier_customer_code  
  ,xsource.supplier_customer_name   
  ,isnull(xsource.category_id,-1)  
  ,sum(isnull(xsource.qty_pakai,0)) 
  ,sum(isnull(xsource.ori_amount_usd,0))  
 from @tp_ms_avg_in_from_bc as xsource   
 where  
   xsource.is_import=0 and isnull(xsource.is_affilete,0) =0
  and exists (
  select category_id from(select * FROM   @tp_ms_avg_catergory_detail_in_out
  where show =1
 )xfillcategory where xsource.category_id=xfillcategory.category_id)
 and not isnull(xsource.category_id,-1) =-1 
 group by xsource.supplier_customer_id  
  ,xsource.supplier_customer_code  
  ,xsource.supplier_customer_name   
  ,isnull(xsource.category_id,-1)  
 order by isnull(xsource.category_id,-1) , xsource.supplier_customer_code
  
  
insert into @tp_ms_in_by_supplier_import(
  supplier_customer_id
    ,supplier_customer_code
    ,supplier_customer_name  
 ,is_affilete 
 ,in_qty
 ,in_amount
 )  

select   
  xsource.supplier_customer_id  
 ,xsource.supplier_customer_code  
 ,xsource.supplier_customer_name     
 ,isnull(xsource.is_affilete,0)  
 ,sum(isnull(xsource.qty_pakai,0)) 
 ,sum(isnull(xsource.ori_amount_usd,0)) 
from @tp_ms_avg_in_from_bc  as xsource
where   ((xsource.is_import)=1 or (xsource.is_import=0 and isnull(xsource.is_affilete,0) =1))
 and exists (
  select category_id from(select * FROM   @tp_ms_avg_catergory_detail_in_out
  where show =1
 )xfillcategory where xsource.category_id=xfillcategory.category_id)
 and not isnull(xsource.category_id,-1) =-1 
group by 
  xsource.supplier_customer_id  
 ,xsource.supplier_customer_code  
 ,xsource.supplier_customer_name     
 ,isnull(xsource.is_affilete,0)  
order by isnull(xsource.is_affilete,0) , xsource.supplier_customer_code
 
   
declare @format_col xml=(
SELECT         
category_id as '@cid'
, replace(category_name ,' ', '_') as '@rName'
,category_name  as '@rNameOri'
, category_color as '@rColor'
, isnull(nullif(category_initial,''''),lower(dbo.CombineFirstWord( category_name,4))) as '@ri'
, isnull(sta_qty,0) as '@sta_qty'
, isnull(sta_amount_usd,0) as '@sta_amount'
, isnull(in_qty,0) as '@in_qty'
, isnull(in_amount_usd ,0) as '@in_amount'
, isnull(out_qty,0) as '@out_qty'
, isnull(out_amount ,0) as '@out_amount'
, isnull(adj_qty,0) as '@adj_qty'
, isnull(adj_amount ,0) as '@adj_amount' 
, isnull(end_qty,0) as '@end_qty'
, isnull(end_amount ,0) as '@end_amount'
FROM            @tp_ms_avg_catergory_detail_in_out  
where show =1
order by order_by
for xml path ('xformat'), type 
) 

set  @data_in_from_bc  =(
SELECT       
 category_id as '@cid'   
,is_molts   as '@is_molt'  
,is_import   as '@is_import'  
,is_40     as '@is_40'  
,ms_item_id as 'id'
,part_no   as 'p_no'
,part_name   as 'p_na'
,uom    as 'p_u'
,isnull(qty_pakai,0) as 'qty' 
,isnull(ori_amount_usd,0)  as 'amt' 
,bc_type as 'bc_i'
,car   as 'bc_a'
,bc_no   as 'bc_d'
,bc_date  as 'bc_t'
,receive_no   as 'rc_n'
,receive_date    as 'rc_d'
,po_no   as 'po_n'
,po_date   as 'po_d'
,supplier_customer_id as 's_id'
,supplier_customer_code  as 's_n'
,supplier_customer_name  as 's_c' 
,invoice_no as 'inv_n'
,invoice_date   as 'inv_d'  
FROM            @tp_ms_avg_in_from_bc as dt    
where    exists (
  select category_id from(select * FROM   @tp_ms_avg_catergory_detail_in_out
  where show =1
 )xfillcategory where dt.category_id=xfillcategory.category_id) 
order by dt.receive_date ,dt.receive_no ,dt.part_no
for xml path ('i_bc'), type 
) 

set  @data_in_supplier_local  =(
SELECT       
 category_id as '@cid'    
,supplier_customer_id as 's_id'
,supplier_customer_code  as 's_n'
,supplier_customer_name  as 's_c' 
,is_affilete   as 'aff' 
,isnull(in_qty,0)  as 'qty'
,isnull(in_amount,0)   as 'amt'
FROM            @tp_ms_in_by_supplier_local as dt   
order by dt.category_id 
for xml path ('i_sl'), type 
) 

set  @data_in_supplier_import  =(
SELECT       
 is_affilete as '@af' 
,supplier_customer_id as 's_id'
,supplier_customer_code  as 's_n'
,supplier_customer_name  as 's_c'  
, isnull(in_qty,0)  as 'qty'
, isnull(in_amount,0)  as 'amt'
FROM            @tp_ms_in_by_supplier_import as dt    
order by dt.is_affilete 
for xml path ('i_si'), type 
)  

set @data_in_from_retur=(
SELECT       
 dt.category_id as '@cid' 
,dt.ms_item_id as 'id'
,dt.part_no   as 'p_no'
,dt.part_name   as 'p_na'
,dt.uom    as 'p_u'
,dt.qty_pakai as 'qty' 
,dt.retur_no  as 'rt_n'
,dt.retur_date  as 'rt_d'
,dt.retur_info  as 'rt_i'
FROM            @tp_ms_avg_in_from_retur  as dt    
where    exists (
  select category_id from(select * FROM   @tp_ms_avg_catergory_detail_in_out
  where show =1
 )xfillcategory where dt.category_id=xfillcategory.category_id) 

order by dt.category_id
for xml path ('i_r'), type 
)

set  @data_in_from_adjustment  =(
SELECT       
 dt.category_id as '@cid'   
, dt.ms_item_id as 'id'
, dt.part_no   as 'p_no'
, dt.part_name   as 'p_na'
, dt.uom    as 'p_u'
, isnull(dt.qty_pakai,0) as 'qty'  
, isnull(dt.ori_amount_usd,0) as 'amt'
, dt.doc_code  'd_c'
, dt.doc_date  'd_d'
, dt.info    'd_i'
, dt.bc_type as 'bc_i'
, dt.car   as 'bc_a'
, dt.bc_no   as 'bc_d'
, dt.bc_date  as 'bc_t'  
FROM            @tp_ms_avg_in_from_adjustment   as dt 
where    exists (
  select category_id from(select * FROM   @tp_ms_avg_catergory_detail_in_out
  where show =1
 )xfillcategory where dt.category_id=xfillcategory.category_id) 

order by dt.category_id
for xml path ('i_adj'), type 
)


set  @data_in_from_sto  =(
SELECT       
 dt.category_id as '@cid'   
, dt.ms_item_id as 'id'
, dt.part_no   as 'p_no'
, dt.part_name   as 'p_na'
, dt.uom    as 'p_u'
, isnull(dt.qty_pakai,0) as 'qty'  
, isnull(dt.ori_amount_usd,0) as 'amt'
, dt.doc_code  'd_c'
, dt.doc_date  'd_d'
, dt.info    'd_i'
, dt.bc_type as 'bc_i'
, dt.car   as 'bc_a'
, dt.bc_no   as 'bc_d'
, dt.bc_date  as 'bc_t'  
FROM            @tp_ms_avg_in_from_sto   as dt    
where    exists (
  select category_id from(select * FROM   @tp_ms_avg_catergory_detail_in_out
  where show =1
 )xfillcategory where dt.category_id=xfillcategory.category_id) 

order by dt.category_id
for xml path ('i_sto'), type 
) 

set  @data_out_carline  =(
SELECT       
  xdata.category_id as '@cid'   
, xdata.doc_no as 'd_n'
, xdata.doc_date as 'd_d' 
, xdata.doc_info  as 'd_i'
, xdata.ms_type_prosses_id as 'cl_no'
, xdata.ms_type_prosses_name   as 'cl_name'
, xdata.ms_car_line_no  as 'p_id' 
, xdata.ms_car_line_name   as 'p_n'
--, xdata.ms_type_prosses_id as 'p_id'
--, xdata.ms_type_prosses_name as 'p_n'
--, xdata.ms_car_line_no as 'cl_no'
--, xdata.ms_car_line_name as 'cl_name'
, xdata.ms_car_line_group_id as 'cl_gid'
, xdata.ms_car_line_group  as 'cl_gn'
, xdata.ms_item_id as 'id'
, xdata.part_no   as 'p_no'
, xdata.part_name   as 'p_na'
, xdata.uom    as 'p_u' 
, isnull(xdata.qty_total,0)  as 'qty'
, isnull(data_item.final_avg_price_usd,0) as 'price'
, isnull(xdata.qty_total * data_item.final_avg_price_usd,0) as 'amt'
, xdata.remark    as 'rm' 
FROM            @tp_ms_item_out_to_carline   as xdata INNER JOIN 
@tp_ms_item_avg_pre as data_item  ON xdata.ms_item_id = data_item.ms_item_id 
where    exists (
  select category_id from(select * FROM   @tp_ms_avg_catergory_detail_in_out
  where show =1
 )xfillcategory where data_item.category_id=xfillcategory.category_id) 

order by xdata.category_id,xdata.ms_car_line_group_id
for xml path ('o_cl'), type 
) 

set  @data_out_memo  =(
SELECT         
  xdata.category_id as '@cid'  
 ,xdata.ms_memo_type_id as 'mt_id'
 ,xdata.ms_memo_type_name  as 'm_tn'
 ,xdata.memo_code  as 'm_c'
 ,xdata.memo_date  as 'm_d'
, xdata.ms_item_id as 'id'
, xdata.part_no   as 'p_no'
, xdata.part_name   as 'p_na'
, xdata.uom    as 'p_u'  
, isnull(xdata.total_out,0) as 'qty'
, isnull(xdata.total_out * data_item.final_avg_price_usd,0) as 'amt'
, xdata.supplier_customer_id as 's_id'
, xdata.supplier_customer_code  as 's_c'
, xdata.supplier_customer_name  as 's_n' 
 ,xdata.is_affilete 'aff' 
FROM            @tp_ms_avg_stock_to_memo  as xdata INNER JOIN 
@tp_ms_item_avg_pre as data_item  ON xdata.ms_item_id = data_item.ms_item_id 
where    exists (
  select category_id from(select * FROM   @tp_ms_avg_catergory_detail_in_out
  where show =1
 )xfillcategory where data_item.category_id=xfillcategory.category_id) 
order by xdata.category_id,xdata.memo_date,xdata.memo_code
for xml path ('o_m'), type
) 
 
/* set @data_out_adjustment =( SELECT category_id as '@cid' ,* FROM @tp_ms_avg_to_adjustment order by category_id for xml path ('o_adj'), type ) set @data_out_sto =( SELECT category_id as '@cid' ,* FROM @tp_ms_avg_to_sto --where exists ( --select category_id from(select * FROM @tp_ms_avg_catergory_detail_in_out --where show =1 --)xdata where [@tp_ms_avg_to_sto].category_id=xdata.category_id) order by category_id for xml path ('o_sto'), type ) */
declare @item_in_pivot xml
declare @item_out_pivot xml  
declare @raw_pivot_out_hdr xml  

declare @item_data xml
if @with_detail_daily=1
begin 
 
 declare @format_bc_in table( ms_date_business_id int)
 insert into @format_bc_in
 SELECT    xsource.ms_date_business_id '@dt' 
 FROM            @tp_ms_avg_in_from_bc as xsource 
 where    exists (
  select category_id from(select * FROM   @tp_ms_avg_catergory_detail_in_out
  where show =1
 )xfillcategory where xsource.category_id=xfillcategory.category_id) 
 group by xsource.ms_date_business_id
 order by xsource.ms_date_business_id

 declare @raw_pivot_bc_in_hdr xml  
 set  @raw_pivot_bc_in_hdr =(
 SELECT    ms_date_business_id '@dt' 
 from @format_bc_in
 for xml path('format_bc_in')
 )
 
 declare @format_bc_out table( ms_date_business_id int)
 insert into @format_bc_out
 SELECT    ms_date_business_id '@dt' from ( 
  SELECT xsource.ms_date_business_id
  FROM            @tp_ms_item_out_to_carline  as xsource 
  where    exists (
   select category_id from(select * FROM   @tp_ms_avg_catergory_detail_in_out
   where show =1
  )xfillcategory where xsource.category_id=xfillcategory.category_id) 

 union all 
  SELECT xsource.ms_date_business_id
  FROM            @tp_ms_avg_stock_to_memo  as xsource 
  where    exists (
   select category_id from(select * FROM   @tp_ms_avg_catergory_detail_in_out
   where show =1
  )xfillcategory where xsource.category_id=xfillcategory.category_id) 

 )xgab
 group by ms_date_business_id
 order by ms_date_business_id
  
  

 set  @raw_pivot_out_hdr =(
  SELECT    ms_date_business_id '@dt' 
  from @format_bc_out
  for xml path('format_out')
 )

 set @item_in_pivot=(   
      select 
       xsource.ms_item_id   as '@id'
      ,xsource.ms_date_business_id   as '@bid'
      ,sum(isnull(xsource.qty_pakai,0))    as '@qty'
      ,sum(isnull(xsource.ori_amount_usd,0)) as '@usd'
       ,(STUFF(( 
      SELECT     ','+  isnull(xinvoice.invoice_no,'') 
      FROM           @tp_ms_avg_in_from_bc as xinvoice   
      where    exists (
       select category_id from(select * FROM   @tp_ms_avg_catergory_detail_in_out
       where show =1
      )xfillcategory where xinvoice.category_id=xfillcategory.category_id)  
      and   xinvoice.ms_date_business_id=xinvoice.ms_date_business_id
      and xinvoice.ms_item_id=xsource.ms_item_id  
      for xml  PATH(''), type 
      ).value('.', 'varchar(max)'), 1, 1, '')  
      ) as  '@inv'
      from @tp_ms_avg_in_from_bc as xsource  
      GROUP BY  
      ms_item_id, ms_date_business_id
      for xml path ('i_in') , root ('list_in')
      ) 

 set @item_out_pivot=(   
 select 
  ms_item_id   as '@id'
 ,ms_date_business_id   as '@bid'
 ,sum(isnull(qty_total,0))    as '@qty' 
 from ( 
  SELECT
   xsource.ms_date_business_id
  ,xsource.ms_item_id
  ,xsource.qty_total 
  FROM            @tp_ms_item_out_to_carline as xsource    
   where    exists (
       select category_id from(select * FROM   @tp_ms_avg_catergory_detail_in_out
       where show =1
      )xfillcategory where xsource.category_id=xfillcategory.category_id)  
      

  union all 
  SELECT ms_date_business_id
  ,xsource.ms_item_id
  ,xsource.total_out as qty_total
  FROM            @tp_ms_avg_stock_to_memo as xsource   
  where    exists (
       select category_id from(select * FROM   @tp_ms_avg_catergory_detail_in_out
       where show =1
      )xfillcategory where xsource.category_id=xfillcategory.category_id)  
      
 
 ) xdata_out_pre 
 group by ms_item_id 
 ,ms_date_business_id 
 
      for xml path ('i_out') , root ('list_out')
 )
  
 
declare @RUMUS_SELECT_PIVOT_BC_IN_2 nvarchar(max) =
cast(STUFF(( 

SELECT  N', isnull( data_in.inv' + cast(ms_date_business_id as nvarchar(max)) + ','''') as inv' + cast(ms_date_business_id as nvarchar(max))  + char(13) +
 N', isnull( data_in.it' + cast(ms_date_business_id as nvarchar(max)) + ',0) as it' + cast(ms_date_business_id as nvarchar(max))  + char(13) +
 N', isnull( data_in.ip' + cast(ms_date_business_id as nvarchar(max)) + ',0) as ip' + cast(ms_date_business_id as nvarchar(max))  + char(13)  
from @format_bc_in

 
 for xml  PATH(''), type
).value('.', 'nvarchar(max)'), 1, 0, '') 
 as  nvarchar(max)) 
 



 
declare @RUMUS_VIPOT_BC_IN_2 nvarchar(max) =
cast(STUFF(( 
SELECT
N', sum(case when xdata_in_pre.ms_date_business_id='+    cast(xsource.ms_date_business_id  as nvarchar(max)) + N' then isnull(xdata_in_pre.qty_pakai ,0) else 0 end ) as it' +  cast(ms_date_business_id  as nvarchar(max))  + char(13) +
N', sum(case when xdata_in_pre.ms_date_business_id='+    cast(xsource.ms_date_business_id  as nvarchar(max)) + N' then isnull(xdata_in_pre.ori_price_total_usd,0) else 0 end ) as ip' +  cast(ms_date_business_id  as nvarchar(max))  + char(13) 
   AS [text()]   
FROM            @tp_ms_avg_in_from_bc as  xsource
where    exists (
       select category_id from(select * FROM   @tp_ms_avg_catergory_detail_in_out
       where show =1
      )xfillcategory where xsource.category_id=xfillcategory.category_id)  
    
group by xsource.ms_date_business_id
order by xsource.ms_date_business_id
 for xml  PATH(''), type
).value('.', 'nvarchar(max)'), 1, 0, '') 
 as  nvarchar(max)) 
  

declare @RUMUS_VIPOT_BC_IN_3 nvarchar(max) =
cast(STUFF(( 
SELECT  
N', (STUFF(( SELECT '',''+ isnull(xinvoice.invoice_no,'''') FROM @tp_ms_avg_in_from_bc as xinvoice where xinvoice.ms_date_business_id=' +  cast(ms_date_business_id  as nvarchar(max)) +
' and exists ( select category_id from(select * FROM @tp_ms_avg_catergory_detail_in_out where show =1 )xfillcategory where xinvoice.category_id=xfillcategory.category_id) and xinvoice.ms_item_id=xgab1.ms_item_id for xml PATH(''''), type ).value(''.'', ''varchar(max)''), 1, 1, '''') ) as inv' +  cast(xsource.ms_date_business_id  as nvarchar(max))   + char(13) +
N', sum(it'+    cast(xsource.ms_date_business_id  as nvarchar(max)) + N') as it' +  cast(xsource.ms_date_business_id  as nvarchar(max))  + char(13) +
N', sum(ip'+    cast(xsource.ms_date_business_id  as nvarchar(max)) + N') as ip' +  cast(xsource.ms_date_business_id  as nvarchar(max))  + char(13) 
   AS [text()]   
FROM            @tp_ms_avg_in_from_bc as  xsource
 where    exists (
       select category_id from(select * FROM   @tp_ms_avg_catergory_detail_in_out
       where show =1
      )xfillcategory where xsource.category_id=xfillcategory.category_id)  
    
group by xsource.ms_date_business_id
order by xsource.ms_date_business_id
 for xml  PATH(''), type
).value('.', 'nvarchar(max)'), 1,0, '') 
 as  nvarchar(max)) 
  

declare @raw_pivot_item xml 

declare @RUMUS_SELECT_PIVOT_OUT_2 nvarchar(max) =
cast(STUFF(( 

SELECT  N', isnull( data_out.ot' + cast(ms_date_business_id as nvarchar(max)) + ',0) as ot' + cast(ms_date_business_id as nvarchar(max))  + char(13)  
from @format_bc_out
 for xml  PATH(''), type
).value('.', 'nvarchar(max)'), 1, 0, '') 
 as  nvarchar(max)) 
 
declare @RUMUS_VIPOT_OUT_2 nvarchar(max) =
cast(STUFF(( 
SELECT      N', sum(case when ms_date_business_id='+    cast(ms_date_business_id  as nvarchar(max)) + N' then isnull(qty_total ,0) else 0 end ) as ot' +  cast(ms_date_business_id  as nvarchar(max))   + char(13) 
   AS [text()] 
 from ( 
 SELECT xsource.ms_date_business_id
 FROM            @tp_ms_item_out_to_carline  as  xsource 
 where    exists (
       select category_id from(select * FROM   @tp_ms_avg_catergory_detail_in_out
       where show =1
      )xfillcategory where xsource.category_id=xfillcategory.category_id)  
    
union all 
 SELECT xsource.ms_date_business_id
 FROM            @tp_ms_avg_stock_to_memo as xsource
 where    exists (
       select category_id from(select * FROM   @tp_ms_avg_catergory_detail_in_out
       where show =1
      )xfillcategory where xsource.category_id=xfillcategory.category_id)  
    

)xgab
group by ms_date_business_id
order by ms_date_business_id
    
 for xml  PATH(''), type
).value('.', 'nvarchar(max)'), 1, 0, '') 
 as  nvarchar(max))
  
   
declare @RUMUS_VIPOT_OUT_3 nvarchar(max) =
cast(STUFF(( 
SELECT      N', sum(ot'+    cast(ms_date_business_id  as nvarchar(max)) + N') as ot' +  cast(ms_date_business_id  as nvarchar(max))   + char(13) 
   AS [text()] 
 from ( 
 SELECT xsource.ms_date_business_id
 FROM            @tp_ms_item_out_to_carline as xsource
 where    exists (
       select category_id from(select * FROM   @tp_ms_avg_catergory_detail_in_out
       where show =1
      )xfillcategory where xsource.category_id=xfillcategory.category_id)  
    
union all 
 SELECT xsource.ms_date_business_id
 FROM            @tp_ms_avg_stock_to_memo as xsource 
 where    exists (
       select category_id from(select * FROM   @tp_ms_avg_catergory_detail_in_out
       where show =1
      )xfillcategory where xsource.category_id=xfillcategory.category_id)  
    

)xgab
group by ms_date_business_id
order by ms_date_business_id
    
 for xml  PATH(''), type
).value('.', 'nvarchar(max)'), 1,0, '') 
 as  nvarchar(max))
   
declare @RUMUS_SELECT_ITEM_IN_OUT_VIPOT nvarchar(max)= 
 
N' set @item_data=( select category_id as ''@cid'' , category_id as ''cid2'' , data_item.ms_item_id as ''id'' , data_item.part_no as ''p_no'' , data_item.part_name as ''p_na'' , data_item.uom as ''p_u'' , data_item.sta_qty as ''a_q'' , data_item.sta_amount_usd as ''a_a'' , data_item.in_qty as ''i_q'' , data_item.in_amount_usd as ''i_a'' , data_item.out_carline_qty + data_item.out_memo_qty as ''o_q'' , data_item.adj_qty + data_item.sto_qty + data_item.retur_qty as ''j_q'' , data_item.final_avg_price_usd as ''f_p'' , (data_item.sta_qty + data_item.in_qty + data_item.adj_qty + data_item.sto_qty + data_item.retur_qty)-(data_item.out_carline_qty + data_item.out_memo_qty) as ''e_q'' ' +
 case when @RUMUS_SELECT_PIVOT_BC_IN_2 is null then '' else   @RUMUS_SELECT_PIVOT_BC_IN_2  end +
 case when @RUMUS_SELECT_PIVOT_OUT_2 is null then '' else   @RUMUS_SELECT_PIVOT_OUT_2  end +
' FROM @tp_ms_item_avg_pre AS data_item '
 +
 case when @RUMUS_VIPOT_OUT_2 is null then '' else  
' LEFT OUTER JOIN ( SELECT ms_item_id '+ cast(@RUMUS_VIPOT_OUT_3  as  nvarchar(max)) +  
N'FROM (SELECT xdata_out_pre.ms_item_id '+ cast(@RUMUS_VIPOT_OUT_2  as  nvarchar(max)) + 
N' from ( SELECT ms_date_business_id ,ms_item_id ,qty_total FROM @tp_ms_item_out_to_carline as xsource where exists ( select category_id from(select * FROM @tp_ms_avg_catergory_detail_in_out where show =1 )xfillcategory where xsource.category_id=xfillcategory.category_id) union all SELECT ms_date_business_id ,ms_item_id ,total_out as qty_total FROM @tp_ms_avg_stock_to_memo as xsource where exists ( select category_id from(select * FROM @tp_ms_avg_catergory_detail_in_out where show =1 )xfillcategory where xsource.category_id=xfillcategory.category_id) ) xdata_out_pre group by ms_item_id ,ms_date_business_id )xgabout group by ms_item_id ) AS data_out ON data_item.ms_item_id = data_out.ms_item_id ' 
end 
+
case when @RUMUS_VIPOT_BC_IN_2 is null then '' else  

' LEFT OUTER JOIN ( select ms_item_id ' + cast(@RUMUS_VIPOT_BC_IN_3  as  nvarchar(max)) + 
' FROM ( SELECT xdata_in_pre.ms_item_id ' + cast(@RUMUS_VIPOT_BC_IN_2  as  nvarchar(max)) + 
  
N' from ( select ms_date_business_id ,ms_item_id ,sum(isnull(qty_pakai,0)) as qty_pakai ,sum(isnull(ori_amount_usd,0)) as ori_price_total_usd from @tp_ms_avg_in_from_bc as xsource where exists ( select category_id from(select * FROM @tp_ms_avg_catergory_detail_in_out where show =1 )xfillcategory where xsource.category_id=xfillcategory.category_id) GROUP BY ms_date_business_id ,ms_item_id ) as xdata_in_pre GROUP BY ms_item_id,ms_date_business_id )xgab1 group by ms_item_id ) AS data_in ON data_item.ms_item_id = data_in.ms_item_id '
end 
+
' where exists ( select category_id from(select * FROM @tp_ms_avg_catergory_detail_in_out where show =1 )xfillcategory where data_item.category_id=xfillcategory.category_id) order by data_item.part_no for xml path (''r''),root(''d_i'')) ' 
declare  @ParmDefinition_out nvarchar(max) = N'@tp_ms_item_avg_pre tp_ms_item_avg_pre READONLY, @tp_ms_avg_in_from_bc tp_ms_avg_in_from_bc READONLY ,@tp_ms_item_out_to_carline tp_ms_item_out_to_carline READONLY ,@tp_ms_avg_stock_to_memo tp_ms_avg_stock_to_memo READONLY ,@tp_ms_avg_catergory_detail_in_out tp_ms_avg_catergory_detail_in_out READONLY , @item_data xml output';
EXECUTE sp_executesql  @RUMUS_SELECT_ITEM_IN_OUT_VIPOT, @ParmDefinition_out
 ,@tp_ms_item_avg_pre = @tp_ms_item_avg_pre
 ,@tp_ms_avg_in_from_bc = @tp_ms_avg_in_from_bc
 ,@tp_ms_avg_catergory_detail_in_out=@tp_ms_avg_catergory_detail_in_out
 ,@tp_ms_item_out_to_carline = @tp_ms_item_out_to_carline
 ,@tp_ms_avg_stock_to_memo = @tp_ms_avg_stock_to_memo
 ,@item_data=@item_data output 
 




end
else
begin
set @item_data=(   
select  
  xsource.category_id  as '@cid'
,  xsource.category_id  as 'cid2'
, xsource.ms_item_id as 'id'
, xsource.part_no   as 'p_no'
, xsource.part_name   as 'p_na'
, xsource.uom    as 'p_u'
, xsource.sta_qty as 'a_q' 
, xsource.sta_amount_usd as 'a_a'
, xsource.in_qty  as 'i_q' 
, xsource.in_amount_usd as 'i_a'
, xsource.out_carline_qty + xsource.out_memo_qty as 'o_q'   
, xsource.adj_qty + xsource.sto_qty + xsource.retur_qty as 'j_q'  
, xsource.final_avg_price_usd as 'f_p'
, (xsource.sta_qty + xsource.in_qty + xsource.adj_qty + xsource.sto_qty + xsource.retur_qty)-(xsource.out_carline_qty + xsource.out_memo_qty) as 'e_q'
from @tp_ms_item_avg_pre xsource  

where    exists (
       select category_id from(select * FROM   @tp_ms_avg_catergory_detail_in_out
       where show =1
      )xfillcategory where xsource.category_id=xfillcategory.category_id)  
    
order by xsource.ms_item_id
for xml path ('r'),root('d_i')
)

end

  

 
---REPORT SUM
declare @RUMUS_VIPOT varchar(max)= (
STUFF(( 
SELECT      ', [HDR_' +    cast( category_id as varchar(255)) + ']' 
FROM            @tp_ms_avg_catergory_detail_in_out
where show =1
 for xml  PATH(''), type
).value('.', 'varchar(max)'), 1, 1, '') 
) 


declare @RUMUS_SELECT nvarchar(max)= 
'set @raw=( SELECT group_id as ''@gid'' ,mid as ''@mid'' , PROCESS ,CAR_LINE_NAME, '''' as MH, ' +
(
STUFF(( 
SELECT      ', ISNULL(HDR_' +    cast( ms_report_car_line_sum_id as varchar(255)) + ',0) AS '  + replace(ms_report_car_line_sum_name ,' ', '_')
FROM            dt_ms_report_car_line_sum
where show_on_report =1
order by order_column asc
 for xml  PATH(''), type
).value('.', 'varchar(max)'), 1, 1, '') 
)  + + 
' FROM ( select 1 as group_id , 1 as mid , data_carline.ms_type_prosses_name as PROCESS , data_carline.ms_car_line_no as CAR_LINE_NO , data_carline.ms_car_line_name as CAR_LINE_NAME , ''HDR_'' + cast(data_carline.category_id as varchar(255)) as HDR , sum(data_carline.qty_total * data_item.final_avg_price_usd) as price_total_usd from @tp_ms_item_out_to_carline as data_carline INNER JOIN @tp_ms_item_avg_pre as data_item ON data_carline.ms_item_id = data_item.ms_item_id where exists ( select category_id from(select * FROM @tp_ms_avg_catergory_detail_in_out where show =1 )xdata where data_carline.category_id=xdata.category_id ) GROUP BY data_carline.ms_type_prosses_name , data_carline.ms_car_line_no , data_carline.ms_car_line_name , ''HDR_'' + cast(data_carline.category_id as varchar(255)) union all select 2 as group_id , data_memo.ms_memo_type_id as mid , isnull(data_memo.ms_memo_type_name,'''') as PROCESS , isnull(data_memo.supplier_customer_code,'''') as CAR_LINE_NO , isnull(data_memo.supplier_customer_code,'''') as CAR_LINE_NAME , ''HDR_'' + cast(data_memo.category_id as varchar(255)) as HDR , sum(data_memo.total_out * data_item.final_avg_price_usd) as price_total_usd from @tp_ms_avg_stock_to_memo as data_memo INNER JOIN @tp_ms_item_avg_pre as data_item ON data_memo.ms_item_id = data_item.ms_item_id where exists ( select category_id from(select * FROM @tp_ms_avg_catergory_detail_in_out where show =1 )xdata where data_memo.category_id=xdata.category_id ) GROUP BY data_memo.ms_memo_type_id , isnull(data_memo.ms_memo_type_name,'''') , isnull(data_memo.supplier_customer_code,'''') , isnull(data_memo.supplier_customer_code,'''') , ''HDR_'' + cast(data_memo.category_id as varchar(255)) )XDATA PIVOT ( SUM([price_total_usd]) FOR HDR IN (' + @RUMUS_VIPOT +
') ) AS pv1 order by group_id,mid desc ,PROCESS for xml path(''xrow''),TYPE) --'
 
declare @ParmDefinition nvarchar(max)
SET @ParmDefinition = N'@tp_ms_item_avg_pre tp_ms_item_avg_pre readonly, @tp_ms_item_out_to_carline tp_ms_item_out_to_carline READONLY ,@tp_ms_avg_stock_to_memo tp_ms_avg_stock_to_memo READONLY ,@tp_ms_avg_catergory_detail_in_out tp_ms_avg_catergory_detail_in_out READONLY , @raw xml output';
EXECUTE sp_executesql  @RUMUS_SELECT, @ParmDefinition
 ,@tp_ms_item_avg_pre=@tp_ms_item_avg_pre
 ,@tp_ms_item_out_to_carline = @tp_ms_item_out_to_carline
 ,@tp_ms_avg_stock_to_memo = @tp_ms_avg_stock_to_memo
 ,@tp_ms_avg_catergory_detail_in_out=@tp_ms_avg_catergory_detail_in_out
 ,@raw=@raw output 
 

f:

set nocount off

END TRY  
BEGIN CATCH
 IF (@@TRANCOUNT > 0)  ROLLBACK TRAN;
 
 set @xStsno='88'
 set @xStsdes= ERROR_MESSAGE() 
END CATCH 
g:

 --select
 -- isnull(@data_in_supplier_local ,'')
 -- ,isnull(@data_in_supplier_import ,'')

set @XmlSts=(select @xStsnoheader + @xStsno as 'no', @xStsdes as 'des' for xml path('sts'))
 select  CAST( (select @XmlSts,isnull(@raw ,'')
, @REPORT_NAME as report_name
, @REPORT_START as report_start
, @REPORT_STOP as report_end
, @with_detail_daily as 'with_daily'
,isnull(@format_col ,'')   
,isnull(@raw_pivot_out_hdr ,'')  
,isnull(@raw_pivot_bc_in_hdr ,'')  
,isnull(@item_data ,'')   

,isnull(@data_in_supplier_local ,'')   
,isnull(@data_in_supplier_import ,'') 

,isnull(@item_in_pivot ,'')  
,isnull(@item_out_pivot ,'')  
   
,isnull(@data_out_memo ,'') 
,isnull(@data_out_carline ,'') 

,isnull(@data_in_from_bc ,'') 
,isnull(@data_in_from_retur ,'') 
,isnull(@data_in_from_adjustment ,'') 
,isnull(@data_in_from_sto ,'')    

    for xml path ('result')
)  as XML)  as dt
 

GO