dbo.MG_MS_ITEM_FINAL_AVG_PABRIKASI_CREATE

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

CREATE procedure dbo.MG_MS_ITEM_FINAL_AVG_PABRIKASI_CREATE
@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 @DocHandle int; 
declare @xml_tmp xml

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  
,@final_avg_name varchar(255)
,@info varchar(255)

SELECT 
 @data_by=left(ltrim(rtrim(isnull(tempTable.item.value('data_by[1]', 'varchar(100)'),''))),50) 
,@final_avg_name=left(ltrim(rtrim(isnull(tempTable.item.value('final_avg_name[1]', 'varchar(300)'),''))),255) 
,@info=left(ltrim(rtrim(isnull(tempTable.item.value('info[1]', 'varchar(300)'),''))),255) 
,@business_date =dbo.str_to_date( isnull(tempTable.item.value('business_date[1]', 'varchar(305)'),'20170808'))
FROM @PARAMETER.nodes('param/field')  tempTable(item)   
  
if @business_date is null
begin
 set @xStsno='02';
 set @xStsdes='Gagal,Pilih Tanggal Akhir FINAL AVG PABRIKASI' ;
goto f
end
  
if @final_avg_name =''
begin
 set @xStsno='02';
 set @xStsdes='Gagal,Isi Nama FINAL AVG PABRIKASI' ;
goto f
end

if (
SELECT        COUNT(*) AS Expr1
FROM            vw_ms_item_final_avg_pabrikasi
WHERE        (final_avg_pabrikasi_name = @final_avg_name) AND (NOT (ms_cost_final_avg_pabrikasi_status_id = - 1))
)>0
begin
 set @xStsno='02';
 set @xStsdes='Gagal,Nama FINAL AVG PABRIKASI sudah ada, ganti nama FINAL AVG PABRIKASI' ;
goto f
end

--- CEK AVG TERAKHIR

DECLARE 
 @check_header_ms_cost_final_avg_id bigint
,@check_header_final_avg_name varchar(255)
,@check_header_start_date date
,@check_header_end_date date

SELECT top (1)
  @check_header_ms_cost_final_avg_id=dt_ms_cost_final_avg.ms_cost_final_avg_id
, @check_header_final_avg_name=dt_ms_cost_final_avg.final_avg_name
, @check_header_start_date=dt_ms_cost_final_avg.start_date
, @check_header_end_date=dt_ms_cost_final_avg.end_date 
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        (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

--select @business_date as business_date, @check_header_end_date as check_header_end_date
 
IF @business_date > @check_header_end_date
begin
 set @xStsno='02';
 set @xStsdes='Gagal, Create FINAL AVG terlebih dahulu, tanggal akhir final AVG sebelum nya ( ' + dbo.date_to_str(@check_header_end_date)+ ' )' ;
goto f
end

--- PILIH AVG PABRIKASI TERAKHIR

DECLARE 
 @check_header_ms_cost_final_avg_pabrikasi_id bigint
,@check_header_final_avg_pabrikasi_name varchar(255)
,@check_header_pabrikasi_start_date date
,@check_header_pabrikasi_end_date date

SELECT TOP (1)
  @check_header_ms_cost_final_avg_pabrikasi_id=dt_ms_cost_final_avg_pabrikasi.ms_cost_final_avg_pabrikasi_id
, @check_header_final_avg_pabrikasi_name=dt_ms_cost_final_avg_pabrikasi.final_avg_pabrikasi_name
, @check_header_pabrikasi_start_date=dt_ms_cost_final_avg_pabrikasi.start_date
, @check_header_pabrikasi_end_date=dt_ms_cost_final_avg_pabrikasi.end_date 
FROM            dt_ms_cost_final_avg_pabrikasi INNER JOIN
                         dt_ms_cost_final_avg_pabrikasi_h_status ON dt_ms_cost_final_avg_pabrikasi.ms_cost_final_avg_pabrikasi_h_status_id = dt_ms_cost_final_avg_pabrikasi_h_status.ms_cost_final_avg_pabrikasi_h_status_id
WHERE        (NOT (dt_ms_cost_final_avg_pabrikasi_h_status.ms_cost_final_avg_pabrikasi_status_id = - 1))
ORDER BY dt_ms_cost_final_avg_pabrikasi.end_date desc
 
IF @business_date <=@check_header_pabrikasi_end_date
begin
 set @xStsno='02';
 set @xStsdes='Gagal,Pilih Tanggal Akhir FINAL AVG harus lebih besar dari tanggal akhir final AVG PABRIKASI sebelum nya ( ' + dbo.date_to_str(@check_header_pabrikasi_end_date)+ ' )' ;
goto f
end

declare @start_date date =dateadd(DAY ,1,COALESCE(@check_header_pabrikasi_end_date,@check_header_end_date))

DECLARE @tp_ms_item_avg_pre as tp_ms_item_avg_pre     
         
DECLARE @tp_ms_item_out_new_part table (
 ms_part_list_production_no varchar(255) ,
 ms_part_list_production_date datetime ,
 status_info varchar(255) ,
 ms_process_id bigint ,
 ms_process_name varchar(50) ,
 ms_car_line_no bigint ,
 ms_car_line_name varchar(255) ,
 ms_item_id bigint ,
 part_no varchar(255) ,
 part_name varchar(255) ,
 qty_total decimal(18,4)
)

DECLARE @tp_ms_item_out_recycle table (
 ms_part_list_production_no varchar(255) ,
 ms_part_list_production_date datetime ,
 status_info varchar(255) ,
 ms_process_id bigint ,
 ms_process_name varchar(50) ,
 ms_car_line_no bigint ,
 ms_car_line_name varchar(255) ,
 ms_item_id bigint ,
 part_no varchar(255) ,
 part_name varchar(255) ,
 qty_total decimal(18,4)
)

DECLARE @tp_ms_avg_catergory_detail_in_out tp_ms_avg_catergory_detail_in_out 

BEGIN TRANSACTION;  

 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 
 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

--- DATA DATA OUTGOING

 -- PABRIKASI NEW PART
 INSERT INTO @tp_ms_item_out_new_part(
  ms_part_list_production_no ,
  ms_part_list_production_date ,
  status_info ,
  ms_process_id ,
  ms_process_name ,
  ms_car_line_no ,
  ms_car_line_name ,
  ms_item_id ,
  part_no ,
  part_name ,
  qty_total
 )
 SELECT 
  ms_part_list_production_no ,
  ms_part_list_production_date ,
  status_info ,
  ms_process_id ,
  ms_process_name ,
  ms_car_line_no ,
  ms_car_line_name ,
  ms_item_id ,
  part_no ,
  part_name ,
  qty_total
 FROM vw_ms_out_part_list_sum_new_part
 WHERE ms_part_list_production_date <= @business_date and ms_part_list_production_date >@end_date_sta
 
 -- PABRIKASI RECYCLE
 INSERT INTO @tp_ms_item_out_recycle(
  ms_part_list_production_no ,
  ms_part_list_production_date ,
  status_info ,
  ms_process_id ,
  ms_process_name ,
  ms_car_line_no ,
  ms_car_line_name ,
  ms_item_id ,
  part_no ,
  part_name ,
  qty_total
 )
 SELECT 
  ms_part_list_production_no ,
  ms_part_list_production_date ,
  status_info ,
  ms_process_id ,
  ms_process_name ,
  ms_car_line_no ,
  ms_car_line_name ,
  ms_item_id ,
  part_no ,
  part_name ,
  qty_total
 FROM vw_ms_out_part_list_sum_recycle
 WHERE ms_part_list_production_date <= @business_date and ms_part_list_production_date >@end_date_sta

--- END DATA DATA OUTGOING

 ;WITH data_in_1(
  ms_item_id
  ,part_no
  ,part_name
  ,uom
  ,ms_item_status_id
  ,ms_item_custom_no
  ,ms_item_custom_name
  ,base_no
  ,base_name
  ,in_qty
  ,in_price_idr
  ,in_price_usd
 )  
 AS (
  SELECT 
     ms_item_id
   , part_no
   , part_name
   , uom
   , status_id
   , ms_item_custom_no
   , ms_item_custom_name
   , ms_item_base_no
   , ms_item_base_name
   , quantity_sisa as in_qty
   , price as sta_price_idr-- as sta_price_idr
   , price_usd as sta_price_usd--sta_price_usd
  FROM vw_ms_production_in_detail
  WHERE doc_date <= @start_date and doc_date > = @end_date_sta
 ), out_new_part(ms_item_id, total)
 AS
 (
  select ms_item_id, sum(qty_total) from vw_ms_out_part_list_sum_new_part
  group by ms_item_id
 ), out_recycle(ms_item_id, total)
 AS
 (
  select ms_item_id, sum(qty_total) from vw_ms_out_part_list_sum_recycle
  group by ms_item_id
 )

 select base_no, base_name, sum(in_qty), sum(in_price_idr), sum(in_price_usd) from data_in_1
 where base_no = 'CF-REAL' OR base_no = 'AS-REAL'
 group by base_no, base_name

--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)

--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 (end_date = @end_date_sta)

--IF @check_ms_cost_final_avg_id is not null
-- BEGIN
-- 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) 'in_qty'
-- , out_amount decimal(38,5) 'in_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

 --select * from @tp_ms_avg_catergory_detail_in_out
 --where category_id = 2 or category_id = 3

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 and (category_id = 2 or category_id = 3)
ORDER BY order_by
for xml path ('xformat'), type 
) 

ROLLBACK TRANSACTION;
 --ROLLBACK TRAN;
f:

set nocount off

END TRY  
BEGIN CATCH
 IF (@@TRANCOUNT > 0)  ROLLBACK TRAN;
 
 set @xStsno='88'
 set @xStsdes= ERROR_MESSAGE() 
END CATCH 
g:
set @XmlSts=(select @xStsnoheader + @xStsno as 'no', @xStsdes as 'des' for xml path('sts'))
select  CAST( (select @XmlSts,isnull(@raw ,'')
 --,isnull(@format_col ,'')
 --,isnull(@data_out_carline ,'')
 --,isnull(@data_out_memo ,'')
     for xml path ('result')
)  as XML)  as dt 

GO