Description
Properties
| Name | Value |
|---|---|
| ANSI Nulls ON | True |
| Quoted Identifier ON | True |
| Encrypted | False |
| Execute As | |
| Assembly |
Parameters
| Name | Data Type | Length | Description |
|---|---|---|---|
| @PARAMETER | xml | -1 |
SQL Script
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE procedure dbo.MG_MS_ITEM_FINAL_AVG_CREATE_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
,@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' ;
goto f
end
if @final_avg_name =''
begin
set @xStsno='02';
set @xStsdes='Gagal,Isi Nama FINAL AVG' ;
goto f
end
if (
SELECT COUNT(*) AS Expr1
FROM vw_ms_item_final_avg
WHERE (final_avg_name = @final_avg_name) AND (NOT (ms_cost_final_avg_status_id = - 1))
)>0
begin
set @xStsno='02';
set @xStsdes='Gagal,Nama FINAL AVG sudah ada, ganti nama FINAL AVG' ;
goto f
end
--- PILIH 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
if @business_date <=@check_header_end_date
begin
set @xStsno='02';
set @xStsdes='Gagal,Pilih Tanggal Akhir FINAL AVG harus lebih besar dari tanggal akhir final AVG sebelum nya ( ' + dbo.date_to_str(@check_header_end_date)+ ' )' ;
goto f
end
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_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.ms_cost_final_avg_id 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.ms_cost_final_avg_id DESC
end
--- 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
declare @start_date date =dateadd(DAY ,1,@check_header_end_date)
if not exists(
SELECT ms_date_business_id
FROM dt_ms_date_business
WHERE (ms_date_business = @business_date)
)
begin
INSERT INTO dt_ms_date_business
(ms_date_business_id, ms_date_business, ms_cost_final_avg_id)
VALUES (cast(replace(convert(varchar(10),@business_date,121),'-','') as int),@business_date, - 1)
end
declare @ms_cost_final_avg_id bigint,@ms_cost_final_avg_h_status_id bigint
INSERT INTO dt_ms_cost_final_avg
(final_avg_name, start_date, end_date, info, create_date, create_by, modify_date, modify_by
,item_avg_pre
,avg_to_sto
,avg_to_adjustment
,avg_stock_to_memo
,avg_out_to_carline
,avg_in_from_sto
,avg_in_from_adjustment
,avg_in_from_retur
,avg_in_from_bc
,avg_catergory_detail_in_out
)
VALUES (@final_avg_name,@start_date ,@business_date ,@info,@now,@data_by ,@now,@data_by
,dbo.CompressXmlToByte((select * from @tp_ms_item_avg_pre for xml path('r'), root('root')),9)
,dbo.CompressXmlToByte((select * from @tp_ms_avg_to_sto for xml path('r'), root('root')),9)
,dbo.CompressXmlToByte((select * from @tp_ms_avg_to_adjustment for xml path('r'), root('root')),9)
,dbo.CompressXmlToByte((select * from @tp_ms_avg_stock_to_memo for xml path('r'), root('root')),9)
,dbo.CompressXmlToByte((select * from @tp_ms_item_out_to_carline for xml path('r'), root('root')),9)
,dbo.CompressXmlToByte((select * from @tp_ms_avg_in_from_sto for xml path('r'), root('root')),9)
,dbo.CompressXmlToByte((select * from @tp_ms_avg_in_from_adjustment for xml path('r'), root('root')),9)
,dbo.CompressXmlToByte((select * from @tp_ms_avg_in_from_retur for xml path('r'), root('root')),9)
,dbo.CompressXmlToByte((select * from @tp_ms_avg_in_from_bc for xml path('r'), root('root')),9)
,dbo.CompressXmlToByte((select * from @tp_ms_avg_catergory_detail_in_out for xml path('r'), root('root')) ,9)
)
set @ms_cost_final_avg_id=SCOPE_IDENTITY()
INSERT INTO dt_ms_cost_final_avg_h_status
(ms_cost_final_avg_id, ms_cost_final_avg_status_id, status_info, create_date, create_by)
VALUES (@ms_cost_final_avg_id, 1, '',@now,@data_by)
set @ms_cost_final_avg_h_status_id=SCOPE_IDENTITY()
update dt_ms_cost_final_avg
set ms_cost_final_avg_h_status_id =@ms_cost_final_avg_h_status_id
where ms_cost_final_avg_id=@ms_cost_final_avg_id
--select @ms_cost_final_avg_id,@start_date,@business_date
UPDATE dt_ms_date_business
SET ms_cost_final_avg_id =@ms_cost_final_avg_id
WHERE (ms_date_business >= @start_date) AND (ms_date_business <= @business_date)
declare @new_item_price table (
ms_item_id bigint primary key
,ms_item_list_price_id bigint
)
INSERT INTO dt_ms_item_list_price
(
ms_cost_final_avg_id
, ms_item_id
, end_qty
, end_price
, end_price_usd
, create_date
, create_by
, modify_date
, modify_by)
output inserted.ms_item_id ,inserted.ms_item_h_price_id into @new_item_price
SELECT
@ms_cost_final_avg_id
, ms_item_id
,(sta_qty + in_qty + adj_qty + sto_qty + retur_qty)-(out_carline_qty + out_memo_qty)
,final_avg_price* ((sta_qty + in_qty + adj_qty + sto_qty + retur_qty)-(out_carline_qty + out_memo_qty))
,final_avg_price_usd * ((sta_qty + in_qty + adj_qty + sto_qty + retur_qty)-(out_carline_qty + out_memo_qty))
,@now,@data_by ,@now,@data_by
FROM @tp_ms_item_avg_pre
UPDATE dt_ms_item_daily_in_move_avg
SET ms_item_h_price_id= data_price.ms_item_list_price_id
,ms_cost_final_avg_id=@ms_cost_final_avg_id
FROM dt_ms_item_daily_in_move_avg INNER JOIN
@new_item_price AS data_price ON dt_ms_item_daily_in_move_avg.ms_item_id = data_price.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
WHERE (dt_ms_date_business.ms_cost_final_avg_id = @ms_cost_final_avg_id)
UPDATE dt_ms_production_retur
SET approve_ms_cost_final_avg_id = @ms_cost_final_avg_id
FROM dt_ms_production_retur INNER JOIN
dt_ms_date_business ON dt_ms_production_retur.ms_date_business_id = dt_ms_date_business.ms_date_business_id
WHERE (dt_ms_date_business.ms_cost_final_avg_id = @ms_cost_final_avg_id)
UPDATE dt_ms_adj_sto
SET approve_ms_cost_final_avg_id = @ms_cost_final_avg_id
FROM dt_ms_adj_sto INNER JOIN
dt_ms_date_business ON dt_ms_adj_sto.ms_date_business_id = dt_ms_date_business.ms_date_business_id
WHERE (dt_ms_date_business.ms_cost_final_avg_id = @ms_cost_final_avg_id)
UPDATE dt_ms_tpb_out_header
SET approve_ms_cost_final_avg_id = @ms_cost_final_avg_id
FROM dt_ms_tpb_out_header INNER JOIN
dt_ms_date_business ON dt_ms_tpb_out_header.ms_date_business_id = dt_ms_date_business.ms_date_business_id
WHERE (dt_ms_date_business.ms_cost_final_avg_id = @ms_cost_final_avg_id)
UPDATE dt_ms_tpb_in_header
SET approve_ms_cost_final_avg_id = @ms_cost_final_avg_id
FROM dt_ms_tpb_in_header INNER JOIN
dt_ms_date_business ON dt_ms_tpb_in_header.ms_date_business_id = dt_ms_date_business.ms_date_business_id
WHERE (dt_ms_date_business.ms_cost_final_avg_id = @ms_cost_final_avg_id)
UPDATE dt_ms_memo_header
SET approve_ms_cost_final_avg_id = @ms_cost_final_avg_id
FROM dt_ms_memo_header INNER JOIN
dt_ms_date_business ON dt_ms_memo_header.ms_date_business_id = dt_ms_date_business.ms_date_business_id
WHERE (dt_ms_date_business.ms_cost_final_avg_id = @ms_cost_final_avg_id)
UPDATE dt_ms_car_line_out_header
SET approve_ms_cost_final_avg_id = @ms_cost_final_avg_id
FROM dt_ms_car_line_out_header INNER JOIN
dt_ms_date_business ON dt_ms_car_line_out_header.ms_date_business_id = dt_ms_date_business.ms_date_business_id
WHERE (dt_ms_date_business.ms_cost_final_avg_id = @ms_cost_final_avg_id)
COMMIT 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
GO
CREATE procedure dbo.MG_MS_ITEM_FINAL_AVG_CREATE_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
,@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' ;
goto f
end
if @final_avg_name =''
begin
set @xStsno='02';
set @xStsdes='Gagal,Isi Nama FINAL AVG' ;
goto f
end
if (
SELECT COUNT(*) AS Expr1
FROM vw_ms_item_final_avg
WHERE (final_avg_name = @final_avg_name) AND (NOT (ms_cost_final_avg_status_id = - 1))
)>0
begin
set @xStsno='02';
set @xStsdes='Gagal,Nama FINAL AVG sudah ada, ganti nama FINAL AVG' ;
goto f
end
--- PILIH 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
if @business_date <=@check_header_end_date
begin
set @xStsno='02';
set @xStsdes='Gagal,Pilih Tanggal Akhir FINAL AVG harus lebih besar dari tanggal akhir final AVG sebelum nya ( ' + dbo.date_to_str(@check_header_end_date)+ ' )' ;
goto f
end
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_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.ms_cost_final_avg_id 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.ms_cost_final_avg_id DESC
end
--- 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
declare @start_date date =dateadd(DAY ,1,@check_header_end_date)
if not exists(
SELECT ms_date_business_id
FROM dt_ms_date_business
WHERE (ms_date_business = @business_date)
)
begin
INSERT INTO dt_ms_date_business
(ms_date_business_id, ms_date_business, ms_cost_final_avg_id)
VALUES (cast(replace(convert(varchar(10),@business_date,121),'-','') as int),@business_date, - 1)
end
declare @ms_cost_final_avg_id bigint,@ms_cost_final_avg_h_status_id bigint
INSERT INTO dt_ms_cost_final_avg
(final_avg_name, start_date, end_date, info, create_date, create_by, modify_date, modify_by
,item_avg_pre
,avg_to_sto
,avg_to_adjustment
,avg_stock_to_memo
,avg_out_to_carline
,avg_in_from_sto
,avg_in_from_adjustment
,avg_in_from_retur
,avg_in_from_bc
,avg_catergory_detail_in_out
)
VALUES (@final_avg_name,@start_date ,@business_date ,@info,@now,@data_by ,@now,@data_by
,dbo.CompressXmlToByte((select * from @tp_ms_item_avg_pre for xml path('r'), root('root')),9)
,dbo.CompressXmlToByte((select * from @tp_ms_avg_to_sto for xml path('r'), root('root')),9)
,dbo.CompressXmlToByte((select * from @tp_ms_avg_to_adjustment for xml path('r'), root('root')),9)
,dbo.CompressXmlToByte((select * from @tp_ms_avg_stock_to_memo for xml path('r'), root('root')),9)
,dbo.CompressXmlToByte((select * from @tp_ms_item_out_to_carline for xml path('r'), root('root')),9)
,dbo.CompressXmlToByte((select * from @tp_ms_avg_in_from_sto for xml path('r'), root('root')),9)
,dbo.CompressXmlToByte((select * from @tp_ms_avg_in_from_adjustment for xml path('r'), root('root')),9)
,dbo.CompressXmlToByte((select * from @tp_ms_avg_in_from_retur for xml path('r'), root('root')),9)
,dbo.CompressXmlToByte((select * from @tp_ms_avg_in_from_bc for xml path('r'), root('root')),9)
,dbo.CompressXmlToByte((select * from @tp_ms_avg_catergory_detail_in_out for xml path('r'), root('root')) ,9)
)
set @ms_cost_final_avg_id=SCOPE_IDENTITY()
INSERT INTO dt_ms_cost_final_avg_h_status
(ms_cost_final_avg_id, ms_cost_final_avg_status_id, status_info, create_date, create_by)
VALUES (@ms_cost_final_avg_id, 1, '',@now,@data_by)
set @ms_cost_final_avg_h_status_id=SCOPE_IDENTITY()
update dt_ms_cost_final_avg
set ms_cost_final_avg_h_status_id =@ms_cost_final_avg_h_status_id
where ms_cost_final_avg_id=@ms_cost_final_avg_id
--select @ms_cost_final_avg_id,@start_date,@business_date
UPDATE dt_ms_date_business
SET ms_cost_final_avg_id =@ms_cost_final_avg_id
WHERE (ms_date_business >= @start_date) AND (ms_date_business <= @business_date)
declare @new_item_price table (
ms_item_id bigint primary key
,ms_item_list_price_id bigint
)
INSERT INTO dt_ms_item_list_price
(
ms_cost_final_avg_id
, ms_item_id
, end_qty
, end_price
, end_price_usd
, create_date
, create_by
, modify_date
, modify_by)
output inserted.ms_item_id ,inserted.ms_item_h_price_id into @new_item_price
SELECT
@ms_cost_final_avg_id
, ms_item_id
,(sta_qty + in_qty + adj_qty + sto_qty + retur_qty)-(out_carline_qty + out_memo_qty)
,final_avg_price* ((sta_qty + in_qty + adj_qty + sto_qty + retur_qty)-(out_carline_qty + out_memo_qty))
,final_avg_price_usd * ((sta_qty + in_qty + adj_qty + sto_qty + retur_qty)-(out_carline_qty + out_memo_qty))
,@now,@data_by ,@now,@data_by
FROM @tp_ms_item_avg_pre
UPDATE dt_ms_item_daily_in_move_avg
SET ms_item_h_price_id= data_price.ms_item_list_price_id
,ms_cost_final_avg_id=@ms_cost_final_avg_id
FROM dt_ms_item_daily_in_move_avg INNER JOIN
@new_item_price AS data_price ON dt_ms_item_daily_in_move_avg.ms_item_id = data_price.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
WHERE (dt_ms_date_business.ms_cost_final_avg_id = @ms_cost_final_avg_id)
UPDATE dt_ms_production_retur
SET approve_ms_cost_final_avg_id = @ms_cost_final_avg_id
FROM dt_ms_production_retur INNER JOIN
dt_ms_date_business ON dt_ms_production_retur.ms_date_business_id = dt_ms_date_business.ms_date_business_id
WHERE (dt_ms_date_business.ms_cost_final_avg_id = @ms_cost_final_avg_id)
UPDATE dt_ms_adj_sto
SET approve_ms_cost_final_avg_id = @ms_cost_final_avg_id
FROM dt_ms_adj_sto INNER JOIN
dt_ms_date_business ON dt_ms_adj_sto.ms_date_business_id = dt_ms_date_business.ms_date_business_id
WHERE (dt_ms_date_business.ms_cost_final_avg_id = @ms_cost_final_avg_id)
UPDATE dt_ms_tpb_out_header
SET approve_ms_cost_final_avg_id = @ms_cost_final_avg_id
FROM dt_ms_tpb_out_header INNER JOIN
dt_ms_date_business ON dt_ms_tpb_out_header.ms_date_business_id = dt_ms_date_business.ms_date_business_id
WHERE (dt_ms_date_business.ms_cost_final_avg_id = @ms_cost_final_avg_id)
UPDATE dt_ms_tpb_in_header
SET approve_ms_cost_final_avg_id = @ms_cost_final_avg_id
FROM dt_ms_tpb_in_header INNER JOIN
dt_ms_date_business ON dt_ms_tpb_in_header.ms_date_business_id = dt_ms_date_business.ms_date_business_id
WHERE (dt_ms_date_business.ms_cost_final_avg_id = @ms_cost_final_avg_id)
UPDATE dt_ms_memo_header
SET approve_ms_cost_final_avg_id = @ms_cost_final_avg_id
FROM dt_ms_memo_header INNER JOIN
dt_ms_date_business ON dt_ms_memo_header.ms_date_business_id = dt_ms_date_business.ms_date_business_id
WHERE (dt_ms_date_business.ms_cost_final_avg_id = @ms_cost_final_avg_id)
UPDATE dt_ms_car_line_out_header
SET approve_ms_cost_final_avg_id = @ms_cost_final_avg_id
FROM dt_ms_car_line_out_header INNER JOIN
dt_ms_date_business ON dt_ms_car_line_out_header.ms_date_business_id = dt_ms_date_business.ms_date_business_id
WHERE (dt_ms_date_business.ms_cost_final_avg_id = @ms_cost_final_avg_id)
COMMIT 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
Depends On
47- dbo.str_to_date
- dbo.vw_ms_item_final_avg
- dbo.dt_ms_cost_final_avg
- dbo.dt_ms_cost_final_avg_h_status
- dbo.date_to_str
- dbo.dt_ms_date_business
- dbo.dt_ms_item_base_h_report_car_line_sum
- dbo.dt_ms_report_car_line_sum
- dbo.dt_ms_item_base
- dbo.dt_ms_production_retur_h_status
- dbo.dt_ms_production_retur
- dbo.dt_ms_production_retur_detail
- dbo.dt_ms_item_daily_in_move_avg
- dbo.dt_ms_item_in_detail
- dbo.dt_ms_item
- dbo.dt_ms_car_line
- dbo.dt_ms_car_line_out_header
- dbo.dt_ms_car_line_out_header_h_status
- dbo.dt_ms_car_line_out_detail
- dbo.dt_ms_item_daily_out_move_avg
- dbo.vw_ms_out_car_line_detail_sum_supply
- dbo.dt_ms_car_line_group
- dbo.dt_ms_type_prosses
- dbo.enum_ms_memo_type
- dbo.dt_ms_memo_detail_supply
- dbo.dt_ms_memo_detail
- dbo.dt_ms_memo_header
- dbo.dt_ms_memo_header_h_status
- dbo.dt_ms_supplier_customer
- dbo.dt_ms_adj_sto
- dbo.dt_ms_adj_sto_h_status
- dbo.dt_ms_item_h_status
- dbo.dt_ms_item_custom
- dbo.vw_ms_item_final_cost_last
- dbo.vw_ms_item_beginning_balance
- dbo.vw_business_date_item_in_sum_bc_in
- dbo.dt_ms_tpb_in_dokumen
- dbo.dt_ms_molts_invoice_detail
- dbo.dt_ms_molts_invoice
- dbo.dt_ms_tpb_in_header_h_status
- dbo.dt_ms_tpb_in_header
- dbo.dt_ms_tpb_in_barang
- dbo.dt_ms_manual_invoice
- dbo.dt_ms_manual_invoice_detail
- dbo.CompressXmlToByte
- dbo.dt_ms_item_list_price
- dbo.dt_ms_tpb_out_header
Used By
No items found