四家公司数据同步
目录
- 四家公司数据同步
- MaterialTransactionsView需要的表
- 已同步的表
- 未同步的表
- （transfer_mst）转移单同步存储过程
- （prodvar_mst）产品码同步存储过程
- （projmatl_mst）项目物料表同步存储过程
- （matltran_amt_mst）物料处理金额表同步存储过程
- （citemh_mst）客户订单物料历史记录表同步存储过程
- （pitemh_mst）采购单物料历史表同步存储过程
- 创建MaterialTransactionsView视图
MaterialTransactionsView需要的表
item |
itemloc |
matltran |
transfer |
prodvar |
prodcode |
matltran_amt |
reason |
non_inventory_item |
jobmatl |
projmatl |
rmaitem |
coitem |
poitem |
citemh |
pitemh |
parms |
已同步的表
item |
poitem |
itemloc |
matltran |
prodcode |
reason |
non_inventory_item |
jobmatl |
rmaitem |
coitem |
parms |
未同步的表
transfer |
prodvar |
matltran_amt |
projmatl |
citemh |
pitemh |
（transfer_mst）转移单同步存储过程
/***********************************************************
创 建 者：姚铖霖
创建日期：2023-05-15
系统用途：同步转移单表
修改描述：
修改日期：
执行过程：
EXEC BI_projmatl_mst
*************** 修改记录 ************************************
修改人 修改日期 修改原因
*************************************************************/
--select count(*) from syscolumns where id = object_id('transfer');
create proc BI_transfer_mst as --从ERP里取（transfer）转移单表
begin
declare @sql nvarchar(max) --sql语句变量
,
@select nvarchar(max) --sql语句中的select
--,@select1 nvarchar(max) --sql语句中的select
,
@table1 nvarchar(100) = '[192.168.0.12\slsqlserver].nydt_app10.dbo.transfer_mst with(nolock)' --南阳转移单表
,
@table2 nvarchar(100) = '[192.168.0.62\slsqlserver].gddtgk_app.dbo.transfer_mst with(nolock)' --广东转移单表
,
@table3 nvarchar(100) = '[192.168.0.12\slsqlserver].dtcz_app.dbo.transfer_mst with(nolock)' --超智转移单表
,
@table4 nvarchar(100) = '[192.168.0.12\slsqlserver].dtx_app.dbo.transfer_mst with(nolock)' --鼎泰鑫转移单表
,
@record int --记录数
,
@dateS datetime = dateadd(month, -3, getdate()),
@where nvarchar(max) = ' where ' if object_id('tempdb..##transfer_mst') is not null drop table ##transfer_mst
if object_id('tempdb..#transfer_mst_all') is not null drop table #transfer_mst_all
--设置获取的列
set @select = 'select site_ref
,trn_num
,from_whse
,to_whse
,stat
,ship_code
,weight
,qty_packages
,from_site
,to_site
,entered_site
,fob_site
,exch_rate
,freight_vendor
,duty_vendor
,brokerage_vendor
,frt_alloc_percent
,duty_alloc_percent
,brk_alloc_percent
,est_freight_amt
,act_freight_amt
,est_brokerage_amt
,act_brokerage_amt
,est_duty_amt
,act_duty_amt
,freight_amt_t
,brokerage_amt_t
,duty_amt_t
,duty_alloc_meth
,frt_alloc_meth
,brk_alloc_meth
,duty_alloc_type
,frt_alloc_type
,brk_alloc_type
,trans_nat
,process_ind
,delterm
,NoteExistsFlag
,RecordDate
,RowPointer
,CreatedBy
,UpdatedBy
,CreateDate
,InWorkflow
,ins_vendor
,ins_alloc_percent
,ins_alloc_type
,est_insurance_amt
,ins_alloc_meth
,act_insurance_amt
,insurance_amt_t
,loc_frt_vendor
,loc_frt_alloc_percent
,loc_frt_alloc_type
,est_local_freight_amt
,loc_frt_alloc_meth
,act_local_freight_amt
,local_freight_amt_t
,trans_nat_2
,export_type
,order_date
,fs_mobile_transfer_match
,fs_mobile_transfer_lot
,fs_mobile_transfer_ser_num
,empnum
from ' --把4个库的表合并
--把4个库的表合并
-- select * into 表b from 表a where 1=2
--这样实现了拷贝一个表结构的目的；
--如果条件是“1=1”就是选择所有记录。
SET @sql = 'select * into ##transfer_mst
from (' + @select + @table + ' where 1=2 )a
insert into ##transfer_mst
select * from(' + @select + @table1 + '''' + @where + ' CreateDate>''' + convert(nvarchar(10), @dateS, 20) + ''''
+ ' union all' + @select + @table2 + '''' + @where + ' CreateDate>''' + convert(nvarchar(10), @dateS, 20) + ''''
+ ' union all' + @select + @table3 + '''' + @where + ' CreateDate>''' + convert(nvarchar(10), @dateS, 20) + ''''
+ ' union all' + @select + @table4 + '''' + @where + ' CreateDate>''' + convert(nvarchar(10), @dateS, 20) + '''' + ')a' exec (@sql) --源表s 与目标表t 完全外联，条件：站点、单号、修改时间
select s.site_ref,
s.trn_num,
s.recordDate,
t.site_ref as site_ref_t,
t.trn_num as trn_num_t,
t.RecordDate as recordDate_t into #transfer_mst_all
from (
select *
from transfer_mst
where CreateDate > convert(nvarchar(10), @dateS, 20)
) t
full outer join ##transfer_mst s on s.site_ref =t.site_ref
and s.trn_num = t.trn_num
and s.RecordDate = t.RecordDate --源表s 为空的删除目标表对应数据
select @record = count(*)
from #transfer_mst_all where site_ref is null
if @record > 0 begin delete t
from transfer_mst t,
#transfer_mst_all a
where t.site_ref = a.site_ref_t
and t.trn_num = a.trn_num_t
and a.site_ref is null
insert into ERPToBIDataUupdateLog (table_name, qty_record, note, record_date)
values('transfer_mst', @record, '删除数据', getdate())
end --源表s 为空的删除目标表对应数据
select @record = count(*)
from #transfer_mst_all where site_ref_t is null
if @record > 0 begin
insert into transfer_mst
select s.*
from ##transfer_mst s
join #transfer_mst_all a on s.site_ref=a.site_ref and s.trn_num=a.trn_num and a.site_ref_t is null
insert into ERPToBIDataUupdateLog (table_name, qty_record, note, record_date)
values('transfer_mst', @record, '插入数据', getdate())
end if object_id('tempdb..#transfer_mst') is not null drop table #transfer_mst
if object_id('tempdb..#transfer_mst_all') is not null drop table #transfer_mst_all
end（prodvar_mst）产品码同步存储过程
/***********************************************************
创 建 者：姚铖霖
创建日期：2023-05-15
系统用途：同步产品码表
修改描述：
修改日期：
执行过程：
EXEC BI_projmatl_mst
*************** 修改记录 ************************************
修改人 修改日期 修改原因
*************************************************************/
--select count(*) from syscolumns where id = object_id('prodvar');
create proc BI_prodvar_mst as --从ERP里取（prodvar）产品码
begin
declare @sql nvarchar(max) --sql语句变量
,
@select nvarchar(max) --sql语句中的select
--,@select1 nvarchar(max) --sql语句中的select
,
@table1 nvarchar(100) = '192.168.0.12\slsqlserver.nydt_app10.dbo.prodvar_mst with(nolock)' --南阳产品码表
,
@table2 nvarchar(100) = '192.168.0.62\slsqlserver.gddtgk_app.dbo.prodvar_mst with(nolock)' --广东产品码表
,
@table3 nvarchar(100) = '192.168.0.12\slsqlserver.dtcz_app.dbo.prodvar_mst with(nolock)' --超智产品码表
,
@table4 nvarchar(100) = '192.168.0.12\slsqlserver.dtx_app.dbo.prodvar_mst with(nolock)' --鼎泰鑫产品码表
,
@record int --记录数
,
@dateS datetime = dateadd(month, -3, getdate()),
@where nvarchar(max) = ' where ' if object_id('tempdb..##prodvar_mst') is not null drop table ##prodvar_mst
if object_id('tempdb..#prodvar_mst_all') is not null drop table #prodvar_mst_all
--设置获取的列
set @select = 'select site_ref
,product_code
,pcv_acct
,muv_acct
,lrv_acct
,luv_acct
,flouv_acct
,vlouv_acct
,fmouv_acct
,vmouv_acct
,srv_acct
,slr_acct
,dcv_acct
,fcv_acct
,bcv_acct
,fmcouv_acct
,vmcouv_acct
,pcv_acct_unit1
,pcv_acct_unit2
,pcv_acct_unit3
,pcv_acct_unit4
,muv_acct_unit1
,muv_acct_unit2
,muv_acct_unit3
,muv_acct_unit4
,lrv_acct_unit1
,lrv_acct_unit2
,lrv_acct_unit3
,lrv_acct_unit4
,luv_acct_unit1
,luv_acct_unit2
,luv_acct_unit3
,luv_acct_unit4
,flouv_acct_unit1
,flouv_acct_unit2
,flouv_acct_unit3
,flouv_acct_unit4
,vlouv_acct_unit1
,vlouv_acct_unit2
,vlouv_acct_unit3
,vlouv_acct_unit4
,fmouv_acct_unit1
,fmouv_acct_unit2
,fmouv_acct_unit3
,fmouv_acct_unit4
,vmouv_acct_unit1
,vmouv_acct_unit2
,vmouv_acct_unit3
,vmouv_acct_unit4
,srv_acct_unit1
,srv_acct_unit2
,srv_acct_unit3
,srv_acct_unit4
,slr_acct_unit1
,slr_acct_unit2
,slr_acct_unit3
,slr_acct_unit4
,dcv_acct_unit1
,dcv_acct_unit2
,dcv_acct_unit3
,dcv_acct_unit4
,fcv_acct_unit1
,fcv_acct_unit2
,fcv_acct_unit3
,fcv_acct_unit4
,bcv_acct_unit1
,bcv_acct_unit2
,bcv_acct_unit3
,bcv_acct_unit4
,fmcouv_acct_unit1
,fmcouv_acct_unit2
,fmcouv_acct_unit3
,fmcouv_acct_unit4
,vmcouv_acct_unit1
,vmcouv_acct_unit2
,vmcouv_acct_unit3
,vmcouv_acct_unit4
,NoteExistsFlag
,RecordDate
,RowPointer
,CreatedBy
,UpdatedBy
,CreateDate
,InWorkflow
,icv_acct
,icv_acct_unit1
,icv_acct_unit2
,icv_acct_unit3
,icv_acct_unit4
,lfcv_acct
,lfcv_acct_unit1
,lfcv_acct_unit2
,lfcv_acct_unit3
,lfcv_acct_unit4
,pouv_acct
,pouv_acct_unit1
,pouv_acct_unit2
,pouv_acct_unit3
,pouv_acct_unit4
from ' --把4个库的表合并
--把4个库的表合并
-- select * into 表b from 表a where 1=2
--这样实现了拷贝一个表结构的目的；
--如果条件是“1=1”就是选择所有记录。
SET @sql = 'select * into ##prodvar_mst
from (' + @select + @table + ' where 1=2 )a
insert into ##prodvar_mst
select * from(' + @select + @table1
+ ' union all' + @select + @table2
+ ' union all' + @select + @table3
+ ' union all' + @select + @table4
+ ')a' exec (@sql) --源表s 与目标表t 完全外联，条件：站点、产品码、修改时间
select s.site_ref,
s.product_code,
s.RecordDate,
t.site_ref as site_ref_t,
t.product_code as trn_num_t,
t.RecordDate as recordDate_t into #prodvar_mst_all
from prodvar_mst t
full outer join ##prodvar_mst s on s.site_ref=t.site_ref and s.product_code=t.product_code and s.RecordDate=t.RecordDate
--源表s 为空的删除目标表对应数据
select @record = count(*)
from #prodvar_mst_all where site_ref is null
if @record > 0 begin delete t
from prodvar_mst t,
#prodvar_mst_all a
where t.site_ref = a.site_ref_t
and t.product_code = a.product_code
and a.site_ref is null
insert into ERPToBIDataUupdateLog (table_name, qty_record, note, record_date)
values('prodvar_mst', @record, '删除数据', getdate())
end --源表s 为空的删除目标表对应数据
select @record = count(*)
from #prodvar_mst_all where site_ref_t is null
if @record > 0 begin
insert into prodvar_mst
select s.*
from ##prodvar_mst s
join #prodvar_mst_all a on s.site_ref=a.site_ref and s.product_code=a.product_code and a.site_ref_t is null
insert into ERPToBIDataUupdateLog (table_name, qty_record, note, record_date)
values('prodvar_mst', @record, '插入数据', getdate())
end if object_id('tempdb..##prodvar_mst') is not null drop table ##prodvar_mst
if object_id('tempdb..#prodvar_mst_all') is not null drop table #prodvar_mst_all
end（projmatl_mst）项目物料表同步存储过程
/***********************************************************
创 建 者：姚铖霖
创建日期：2023-05-15
系统用途：同步项目物料表
修改描述：
修改日期：
执行过程：
EXEC BI_projmatl_mst
*************** 修改记录 ************************************
修改人 修改日期 修改原因
*************************************************************/
--select count(*) from syscolumns where id = object_id('projmatl');
create proc BI_projmatl_mst as --从ERP里取（projmatl）项目物料表
begin
declare @sql nvarchar(max) --sql语句变量
,
@select nvarchar(max) --sql语句中的select
--,@select1 nvarchar(max) --sql语句中的select
,
@table1 nvarchar(100) = '192.168.0.12\slsqlserver.nydt_app10.dbo.projmatl_mst with(nolock)' --南阳项目物料表
,
@table2 nvarchar(100) = '192.168.0.62\slsqlserver.gddtgk_app.dbo.projmatl_mst with(nolock)' --广东项目物料表
,
@table3 nvarchar(100) = '192.168.0.12\slsqlserver.dtcz_app.dbo.projmatl_mst with(nolock)' --超智项目物料表
,
@table4 nvarchar(100) = '192.168.0.12\slsqlserver.dtx_app.dbo.projmatl_mst with(nolock)' --鼎泰鑫项目物料表
,
@record int --记录数
,
@dateS datetime = dateadd(month, -3, getdate()),
@where nvarchar(max) = ' where ' if object_id('tempdb..##projmatl_mst') is not null drop table ##projmatl_mst
if object_id('tempdb..#projmatl_mst_all') is not null drop table #projmatl_mst_all
--设置获取的列
set @select = 'select site_ref
,proj_num
,task_num
,seq
,item
,item_desc
,cost_code
,matl_type
,matl_qty
,matl_qty_conv
,u_m
,cost
,cost_conv
,matl_cost
,lbr_cost
,fovhd_cost
,vovhd_cost
,out_cost
,ref_type
,ref_num
,ref_line_suf
,ref_release
,qty_issued
,a_cost
,matl_a_cost
,lbr_a_cost
,fovhd_a_cost
,vovhd_a_cost
,out_a_cost
,matl_cost_conv
,lbr_cost_conv
,fovhd_cost_conv
,vovhd_cost_conv
,out_cost_conv
,whse
,charfld1
,charfld2
,charfld3
,datefld
,decifld1
,decifld2
,decifld3
,logifld
,qty_packed
,qty_shipped
,projected_date
,NoteExistsFlag
,RecordDate
,RowPointer
,CreatedBy
,UpdatedBy
,CreateDate
,InWorkflow
,trans_nat
,trans_nat_2
,delterm
,process_ind
,comm_code
,origin
,unit_weight
,suppl_qty_conv_factor
,ec_code
,export_value
,transport
,due_date
from ' --把4个库的表合并
--把4个库的表合并
-- select * into 表b from 表a where 1=2
--这样实现了拷贝一个表结构的目的；
--如果条件是“1=1”就是选择所有记录。
SET @sql = 'select * into ##projmatl_mst
from (' + @select + @table + ' where 1=2 )a
insert into ##projmatl_mst
select * from(' + @select + @table1 + '''' + @where + ' CreateDate>''' + convert(nvarchar(10), @dateS, 20) + ''''
+ ' union all' + @select + @table2 + '''' + @where + ' CreateDate>''' + convert(nvarchar(10), @dateS, 20) + ''''
+ ' union all' + @select + @table3 + '''' + @where + ' CreateDate>''' + convert(nvarchar(10), @dateS, 20) + ''''
+ ' union all' + @select + @table4 + '''' + @where + ' CreateDate>''' + convert(nvarchar(10), @dateS, 20) + '''' + ')a' exec (@sql) --使用 FULL OUTER JOIN 全连接新表和旧表,将两张表的指定字段存入，条件：站点、项目单号、任务单号、seq、修改时间
select s.site_ref,
s.proj_num,
s.task_num,
s.seq,
s.RecordDate,
t.site_ref as site_ref_t,
t.proj_num as proj_num_t,
t.task_num as task_num_t,
t.seq as seq_t,
t.RecordDate as RecordDate_t into #projmatl_mst_all
from (
select *
from projmatl_mst
where CreateDate > convert(nvarchar(10), @dateS, 20)
) t
full outer join ##projmatl_mst s on s.site_ref = t.site_ref_t and
s.proj_num = t.proj_num_t
and s.task_num = t.task_num_t
and s.seq = t.seq_t
and s.RecordDate = t.RecordDate_t --检测是否存在 site_ref 为 null 的记录 (检测是否有新表已删除的记录)
select @record = count(*)
from #projmatl_mst_all where site_ref is null
if @record > 0 begin delete t
from projmatl_mst t,
#projmatl_mst_all a
where s.site_ref = a.site_ref_t
and s.proj_num = a.proj_num_t
and s.task_num = a.task_num_t
and s.seq = a.seq_t
and a.site_ref_t is null --在 ERPToBIDataUupdateLog 表中,存入对数据的删除操作
insert into ERPToBIDataUupdateLog (table_name, qty_record, note, record_date)
values('projmatl_mst', @record, '删除数据', getdate())
end --检测是否存在 site_ref_t 为 null 的记录 (检测是否有旧表未存入的记录)
select @record = count(*)
from #projmatl_mst_all where site_ref_t is null
if @record > 0 begin
insert into projmatl_mst
select s.*
from ##projmatl_mst s
join #projmatl_mst_all a on s.site_ref = a.site_ref_t and
s.proj_num = a.proj_num_t
and s.task_num = a.task_num_t
and s.seq = a.seq_t
and a.site_ref_t is null --在 ERPToBIDataUupdateLog 表中,存入对数据的添加操作
insert into ERPToBIDataUupdateLog (table_name, qty_record, note, record_date)
values('projmatl_mst', @record, '插入数据', getdate())
end if object_id('tempdb..##projmatl_mst') is not null drop table ##projmatl_mst
if object_id('tempdb..#projmatl_mst_all') is not null drop table #projmatl_mst_all
end（matltran_amt_mst）物料处理金额表同步存储过程
/***********************************************************
创 建 者：姚铖霖
创建日期：2023-05-15
系统用途：同步物料处理金额表
修改描述：
修改日期：
执行过程：
EXEC BI_matltran_amt_mst
*************** 修改记录 ************************************
修改人 修改日期 修改原因
*************************************************************/
create PROCEDURE dbo.BI_matltran_amt_mst AS BEGIN --return
DECLARE @sql NVARCHAR(MAX);
--sql语句变量
DECLARE @select NVARCHAR(MAX);
--sql语句中的select
DECLARE @table1 NVARCHAR(100) = '192.168.0.12\slsqlserver.dtcz_app.dbo.matltran_amt_mst with(nolock)';
--超智物料处理金额表
DECLARE @table2 NVARCHAR(100) = '192.168.0.12\slsqlserver.dtx_app.dbo.matltran_amt_mst with(nolock)';
--鼎泰鑫物料处理金额表
DECLARE @table3 NVARCHAR(100) = '192.168.0.12\slsqlserver.nydt_app10.dbo.matltran_amt_mst with(nolock)';
--南阳物料处理金额表
DECLARE @table4 NVARCHAR(100) = '192.168.0.62\slsqlserver.gddtgk_app.dbo.matltran_amt_mst with(nolock)';
--广东物料处理金额表
DECLARE @record INT --记录数
,
@dateS datetime = dateadd(day, -3, getdate()) --,@dateS datetime=dateadd(month,-1,getdate())
--set @dateS = '2021-01-01'
set @dateS = convert(nvarchar(10), @dateS, 20)
DECLARE @where NVARCHAR(MAX) = ' where CreateDate>=' + '''' + convert(nvarchar(100), @dateS, 120) + ''''
IF OBJECT_ID('tempdb..##matltran_amt_mst') IS NOT NULL BEGIN DROP TABLE ##matltran_amt_mst
END --删除旧临时表(#matltran_amt_mstall)
IF OBJECT_ID('tempdb..#matltran_amt_mstall') IS NOT NULL BEGIN DROP TABLE #matltran_amt_mstall
END --从指定表中提出共同的需求的表字段,填充select语句
SET @select = 'select site_ref
,trans_num
,trans_seq
,amt
,acct
,acct_unit1
,acct_unit2
,acct_unit3
,acct_unit4
,matl_amt
,matl_acct
,matl_acct_unit1
,matl_acct_unit2
,matl_acct_unit3
,matl_acct_unit4
,lbr_amt
,lbr_acct
,lbr_acct_unit1
,lbr_acct_unit2
,lbr_acct_unit3
,lbr_acct_unit4
,fovhd_amt
,fovhd_acct
,fovhd_acct_unit1
,fovhd_acct_unit2
,fovhd_acct_unit3
,fovhd_acct_unit4
,vovhd_amt
,vovhd_acct
,vovhd_acct_unit1
,vovhd_acct_unit2
,vovhd_acct_unit3
,vovhd_acct_unit4
,out_amt
,out_acct
,out_acct_unit1
,out_acct_unit2
,out_acct_unit3
,out_acct_unit4
,NoteExistsFlag
,RecordDate
,RowPointer
,CreatedBy
,UpdatedBy
,CreateDate
,InWorkflow
,include_in_inventory_bal_calc from ' --拼接sql语句 功能: 将指定表中共同的需求的字段填充入 ##matltran_amt_mst 临时表内
SET @sql = 'select * into ##matltran_amt_mst
from (' + @select + @table + ' where 1=2 )a
insert into ##matltran_amt_mst
select * from(' + @select + @table1 + @where + ' union all ' + @select + @table2 + @where + ' union all ' + @select + @table3 + @where + ' union all ' + @select + @table4 + @where + ') a' --执行sql
--print @sql
EXEC (@sql) --使用 FULL OUTER JOIN 全连接新表和旧表,将两张表的指定字段存入 #matltran_amt_mstall
SELECT s.site_ref,
s.trans_num,
s.trans_seq,
s.RecordDate,
t.site_ref AS site_ref_t,
t.trans_num AS trans_num_t,
t.trans_seq AS trans_seq_t,
t.RecordDate AS recordDate_t INTO #matltran_amt_mstall
FROM (
select *
from matltran_amt_mst with(nolock)
where CreateDate >= @dateS
) t
FULL OUTER JOIN ##matltran_amt_mst s
ON s.site_ref = t.site_ref
AND s.trans_num = t.trans_num_t
AND s.trans_seq = t.trans_seq_t
AND s.RecordDate = t.recordDate_t --检测是否存在 site_ref 为 null 的记录 (检测是否有新表已删除的记录)
SELECT @record = COUNT(*)
FROM #matltran_amt_mstall WHERE site_ref IS NULL
--如果有,进行筛选,并在 matltran_amt_mst 表中删除
IF @record > 0 BEGIN DELETE t
FROM matltran_amt_mst t
join #matltran_amt_mstall a
on t.site_ref = a.site_ref_t
AND t.trans_num = a.trans_num_t
AND t.trans_seq = a.trans_seq_t
AND a.site_ref IS NULL --在 ERPToBIDataUupdateLog 表中,存入对数据的删除操作
INSERT INTO ERPToBIDataUupdateLog (table_name, qty_record, note, record_date)
VALUES('matltran_amt_mst', @record, '删除数据', GETDATE())
END --检测是否存在 site_ref_t 为 null 的记录 (检测是否有旧表未存入的记录)
SELECT @record = COUNT(*)
FROM #matltran_amt_mstall WHERE site_ref_t IS NULL
--如果有,进行筛选,并在 matltran_amt_mst 表中添加
IF @record > 0 BEGIN
INSERT INTO matltran_amt_mst
SELECT s.*
FROM ##matltran_amt_mst s
JOIN #matltran_amt_mstall a ON s.site_ref = a.site_ref AND t.trans_num = a.trans_num_t AND t.trans_seq = a.trans_seq_t AND a.site_ref_t IS NULL
--在 ERPToBIDataUupdateLog 表中,存入对数据的添加操作
INSERT INTO ERPToBIDataUupdateLog (table_name, qty_record, note, record_date)
VALUES('matltran_amt_mst', @record, '插入数据', GETDATE())
END --删除旧临时表(##matltran_amt_mst)
IF OBJECT_ID('tempdb..##matltran_amt_mst') IS NOT NULL BEGIN DROP TABLE ##matltran_amt_mst
END --删除旧临时表(#matltran_amt_mstall)
IF OBJECT_ID('tempdb..#matltran_amt_mstall') IS NOT NULL BEGIN DROP TABLE #matltran_amt_mstall
END
END（citemh_mst）客户订单物料历史记录表同步存储过程
/***********************************************************
创 建 者：姚铖霖
创建日期：2023-05-15
系统用途：同步客户订单物料历史记录表
修改描述：
修改日期：
执行过程：
EXEC BI_citemh_mst
*************** 修改记录 ************************************
修改人 修改日期 修改原因
*************************************************************/
create PROCEDURE dbo.BI_citemh_mst AS BEGIN --return
DECLARE @sql NVARCHAR(MAX);
--sql语句变量
DECLARE @select NVARCHAR(MAX);
--sql语句中的select
DECLARE @table1 NVARCHAR(100) = '192.168.0.12\slsqlserver.dtcz_app.dbo.citemh_mst with(nolock)';
--超智客户订单物料历史记录表
DECLARE @table2 NVARCHAR(100) = '192.168.0.12\slsqlserver.dtx_app.dbo.citemh_mst with(nolock)';
--鼎泰鑫客户订单物料历史记录表
DECLARE @table3 NVARCHAR(100) = '192.168.0.12\slsqlserver.nydt_app10.dbo.citemh_mst with(nolock)';
--南阳客户订单物料历史记录表
DECLARE @table4 NVARCHAR(100) = '192.168.0.62\slsqlserver.gddtgk_app.dbo.citemh_mst with(nolock)';
--广东客户订单物料历史记录表
DECLARE @record INT --记录数
,
@dateS datetime = dateadd(day, -3, getdate()) --前三个月的时间
--,@dateS datetime=dateadd(month,-1,getdate())
--set @dateS = '2021-01-01'
set @dateS = convert(nvarchar(10), @dateS, 20)
DECLARE @where NVARCHAR(MAX) = ' where CreateDate>=' + '''' + convert(nvarchar(100), @dateS, 120) + '''' --删除旧临时表(##citemh_mst)
IF OBJECT_ID('tempdb..##citemh_mst') IS NOT NULL BEGIN DROP TABLE ##citemh_mst
END --删除旧临时表(#citemh_mstall)
IF OBJECT_ID('tempdb..#citemh_mstall') IS NOT NULL BEGIN DROP TABLE #citemh_mstall
END --从指定表中提出共同的需求的表字段,填充select语句
SET @select = 'select site_ref
,co_num
,co_line
,co_release
,item
,qty_ordered
,qty_ready
,qty_shipped
,qty_packed
,disc
,cost
,price
,ref_type
,ref_num
,ref_line_suf
,ref_release
,due_date
,ship_date
,reprice
,cust_item
,qty_invoiced
,qty_returned
,cgs_total
,feat_str
,stat
,cust_num
,cust_seq
,prg_bill_tot
,prg_bill_app
,release_date
,promise_date
,whse
,comm_code
,trans_nat
,process_ind
,unit_weight
,delterm
,origin
,cons_num
,tax_code1
,tax_code2
,transport
,ec_code
,export_value
,pick_date
,pricecode
,u_m
,qty_ordered_conv
,price_conv
,matl_cost
,lbr_cost
,fovhd_cost
,vovhd_cost
,out_cost
,cgs_total_matl
,cgs_total_lbr
,cgs_total_fovhd
,cgs_total_vovhd
,cgs_total_out
,cost_conv
,matl_cost_conv
,lbr_cost_conv
,fovhd_cost_conv
,vovhd_cost_conv
,out_cost_conv
,ship_site
,sync_reqd
,co_orig_site
,cust_po
,rma_num
,rma_line
,projected_date
,NoteExistsFlag
,RecordDate
,RowPointer
,description
,config_id
,CreatedBy
,UpdatedBy
,CreateDate
,InWorkflow
,trans_nat_2
,suppl_qty_conv_factor
,print_kit_components
,external_reservation_ref
,non_inv_acct
,non_inv_acct_unit1
,non_inv_acct_unit2
,non_inv_acct_unit3
,non_inv_acct_unit4
,days_shipped_before_due_date_tolerance
,days_shipped_after_due_date_tolerance
,shipped_over_ordered_qty_tolerance
,shipped_under_ordered_qty_tolerance
,manufacturer_id
,manufacturer_item
,co_cust_num
,wks_basis
,wks_value
,qty_picked
,promotion_code
from ' --拼接sql语句 功能: 将指定表中共同的需求的字段填充入 ##citemh_mst 临时表内
SET @sql = 'select * into ##citemh_mst
from (' + @select + @table + ' where 1=2 )a
insert into ##citemh_mst
select * from(' + @select + @table1 + @where +
' union all ' + @select + @table2 + @where +
' union all ' + @select + @table3 + @where +
' union all ' + @select + @table4 + @where + ') a' --执行sql
--print @sql
EXEC (@sql) --使用 FULL OUTER JOIN 全连接新表和旧表,将两张表的指定字段存入 #citemh_mstall
SELECT s.site_ref,
s.co_num,
s.co_line,
s.co_release,
s.RecordDate,
t.site_ref AS site_ref_t,
t.co_num AS co_num_t,
t.co_line AS co_line_t,
t.co_release AS co_release_t,
t.RecordDate AS recordDate_t INTO #citemh_mstall
FROM (
select *
from citemh_mst with(nolock)
where CreateDate >= @dateS
) t
FULL OUTER JOIN ##citemh_mst s
ON s.site_ref = t.site_ref
AND s.co_num = t.co_num_t
AND s.co_line = t.co_line_t
AND s.co_release = t.co_release_t
AND s.RecordDate = t.recordDate_t --检测是否存在 site_ref 为 null 的记录 (检测是否有新表已删除的记录)
SELECT @record = COUNT(*)
FROM #citemh_mstall WHERE site_ref IS NULL
--如果有,进行筛选,并在 citemh_mst 表中删除
IF @record > 0 BEGIN DELETE t
FROM citemh_mst t
join #citemh_mstall a
on t.site_ref = a.site_ref_t
AND t.co_num = a.co_num_t
AND t.co_line = a.co_line_t
AND t.co_release = a.co_release_t
AND a.site_ref IS NULL --在 ERPToBIDataUupdateLog 表中,存入对数据的删除操作
INSERT INTO ERPToBIDataUupdateLog (table_name, qty_record, note, record_date)
VALUES('citemh_mst', @record, '删除数据', GETDATE())
END --检测是否存在 site_ref_t 为 null 的记录 (检测是否有旧表未存入的记录)
SELECT @record = COUNT(*)
FROM #citemh_mstall WHERE site_ref_t IS NULL
--如果有,进行筛选,并在 citemh_mst 表中添加
IF @record > 0 BEGIN
INSERT INTO citemh_mst
SELECT s.*
FROM ##citemh_mst s
JOIN #citemh_mstall a ON s.site_ref = a.site_ref AND t.co_num = a.co_num_t AND t.co_line = a.co_line_t AND t.co_release = a.co_release_t AND a.site_ref_t IS NULL
--在 ERPToBIDataUupdateLog 表中,存入对数据的添加操作
INSERT INTO ERPToBIDataUupdateLog (table_name, qty_record, note, record_date)
VALUES('citemh_mst', @record, '插入数据', GETDATE())
END --删除旧临时表(##citemh_mst)
IF OBJECT_ID('tempdb..##citemh_mst') IS NOT NULL BEGIN DROP TABLE ##citemh_mst
END --删除旧临时表(#citemh_mstall)
IF OBJECT_ID('tempdb..#citemh_mstall') IS NOT NULL BEGIN DROP TABLE #citemh_mstall
END
END（pitemh_mst）采购单物料历史表同步存储过程
/***********************************************************
创 建 者：姚铖霖
创建日期：2023-05-15
系统用途：同步采购单物料历史表
修改描述：
修改日期：
执行过程：
EXEC BI_pitemh_mst
*************** 修改记录 ************************************
修改人 修改日期 修改原因
*************************************************************/
create PROCEDURE dbo.BI_pitemh_mst AS BEGIN --return
DECLARE @sql NVARCHAR(MAX);
--sql语句变量
DECLARE @select NVARCHAR(MAX);
--sql语句中的select
DECLARE @table1 NVARCHAR(100) = '192.168.0.12\slsqlserver.dtcz_app.dbo.pitemh_mst with(nolock)';
--超智采购单物料历史表
DECLARE @table2 NVARCHAR(100) = '192.168.0.12\slsqlserver.dtx_app.dbo.pitemh_mst with(nolock)';
--鼎泰鑫采购单物料历史表
DECLARE @table3 NVARCHAR(100) = '192.168.0.12\slsqlserver.nydt_app10.dbo.pitemh_mst with(nolock)';
--南阳采购单物料历史表
DECLARE @table4 NVARCHAR(100) = '192.168.0.62\slsqlserver.gddtgk_app.dbo.pitemh_mst with(nolock)';
--广东采购单物料历史表
DECLARE @record INT --记录数
,
@dateS datetime = dateadd(day, -3, getdate()) --前三月的记录数
--,@dateS datetime=dateadd(month,-1,getdate())
--set @dateS = '2021-01-01'
set @dateS = convert(nvarchar(10), @dateS, 20)
DECLARE @where NVARCHAR(MAX) = ' where CreateDate>=' + '''' + convert(nvarchar(100), @dateS, 120) + '''' --删除旧临时表(##pitemh_mst)
IF OBJECT_ID('tempdb..##pitemh_mst') IS NOT NULL BEGIN DROP TABLE ##pitemh_mst
END --删除旧临时表(#pitemh_mstall)
IF OBJECT_ID('tempdb..#pitemh_mstall') IS NOT NULL BEGIN DROP TABLE #pitemh_mstall
END --从指定表中提出共同的需求的表字段,填充select语句
SET @select = 'select site_ref
,po_num
,po_line
,po_release
,item
,stat
,qty_ordered
,qty_received
,qty_rejected
,qty_voucher
,qty_returned
,item_cost
,ref_type
,ref_num
,ref_line_suf
,ref_release
,due_date
,rcvd_date
,vend_item
,root_job
,root_suf
,plan_cost
,voucher_cost
,non_inv_acct
,drop_ship_no
,drop_seq
,ship_addr
,promise_date
,release_date
,whse
,item_type
,cost_type
,comm_code
,trans_nat
,process_ind
,unit_weight
,delterm
,origin
,cons_num
,unit_mat_cost
,unit_duty_cost
,unit_freight_cost
,unit_brokerage_cost
,tax_code1
,tax_code2
,transport
,ec_code
,export_value
,req_num
,req_line
,u_m
,qty_ordered_conv
,item_cost_conv
,plan_cost_conv
,unit_mat_cost_conv
,unit_duty_cost_conv
,unit_freight_cost_conv
,unit_brokerage_cost_conv
,non_inv_acct_unit1
,non_inv_acct_unit2
,non_inv_acct_unit3
,non_inv_acct_unit4
,revision
,drawing_nbr
,NoteExistsFlag
,RecordDate
,RowPointer
,description
,CreatedBy
,UpdatedBy
,CreateDate
,InWorkflow
,expedited
,unit_insurance_cost
,unit_insurance_cost_conv
,unit_loc_frt_cost
,unit_loc_frt_cost_conv
,trans_nat_2
,suppl_qty_conv_factor
,preassign_lots
,preassign_serials
,ipr_id
,ipr_seq
,manufacturer_id
,manufacturer_item
,fa_num
,fa_class
,dept from ' --拼接sql语句 功能: 将指定表中共同的需求的字段填充入 ##pitemh_mst 临时表内
SET @sql = 'select * into ##pitemh_mst
from (' + @select + @table + ' where 1=2 )a
insert into ##pitemh_mst
select * from(' + @select + @table1 + @where + ' union all ' + @select + @table2 + @where + ' union all ' + @select + @table3 + @where + ' union all ' + @select + @table4 + @where + ') a' --执行sql
--print @sql
EXEC (@sql) --使用 FULL OUTER JOIN 全连接新表和旧表,将两张表的指定字段存入 #pitemh_mstall
SELECT s.site_ref,
s.po_num,
s.po_line,
s.po_release,
s.RecordDate,
t.site_ref AS site_ref_t,
t.po_num AS po_num_t,
t.po_line AS po_line_t,
t.po_release AS po_release_t,
t.RecordDate AS recordDate_t INTO #pitemh_mstall
FROM (
select *
from pitemh_mst with(nolock)
where CreateDate >= @dateS
) t
FULL OUTER JOIN ##pitemh_mst s
ON s.site_ref = t.site_ref
AND s.po_num = t.po_num_t
AND s.po_line = t.po_line_t
AND s.po_release = t.po_release_t
AND s.RecordDate = t.recordDate_t --检测是否存在 site_ref 为 null 的记录 (检测是否有新表已删除的记录)
SELECT @record = COUNT(*)
FROM #pitemh_mstall WHERE site_ref IS NULL
--如果有,进行筛选,并在 pitemh_mst 表中删除
IF @record > 0 BEGIN DELETE t
FROM pitemh_mst t
join #pitemh_mstall a
on t.site_ref = a.site_ref_t
AND t.po_num = a.po_num_t
AND t.po_line = a.po_line_t
AND t.po_release = a.po_release_t
AND a.site_ref IS NULL --在 ERPToBIDataUupdateLog 表中,存入对数据的删除操作
INSERT INTO ERPToBIDataUupdateLog (table_name, qty_record, note, record_date)
VALUES('pitemh_mst', @record, '删除数据', GETDATE())
END --检测是否存在 site_ref_t 为 null 的记录 (检测是否有旧表未存入的记录)
SELECT @record = COUNT(*)
FROM #pitemh_mstall WHERE site_ref_t IS NULL
--如果有,进行筛选,并在 pitemh_mst 表中添加
IF @record > 0 BEGIN
INSERT INTO pitemh_mst
SELECT s.*
FROM ##pitemh_mst s
JOIN #pitemh_mstall a ON s.site_ref = a.site_ref AND t.po_num = a.po_num_t AND t.po_line = a.po_line_t AND t.po_release = a.po_release_t AND a.site_ref_t IS NULL
--在 ERPToBIDataUupdateLog 表中,存入对数据的添加操作
INSERT INTO ERPToBIDataUupdateLog (table_name, qty_record, note, record_date)
VALUES('pitemh_mst', @record, '插入数据', GETDATE())
END --删除旧临时表(##pitemh_mst)
IF OBJECT_ID('tempdb..##pitemh_mst') IS NOT NULL BEGIN DROP TABLE ##pitemh_mst
END --删除旧临时表(#pitemh_mstall)
IF OBJECT_ID('tempdb..#pitemh_mstall') IS NOT NULL BEGIN DROP TABLE #pitemh_mstall
END
END创建MaterialTransactionsView视图
/***********************************************************
创 建 者：姚铖霖
创建日期：2023-05-15
系统用途：同步创建MaterialTransactionsView 视图
修改描述：
修改日期：
执行过程：
EXEC BI_pitemh_mst
*************** 修改记录 ************************************
修改人 修改日期 修改原因
*************************************************************/
CREATE VIEW dbo.MaterialTransactionsView (
Trans_Num,
TransDate,
TransType,
Item,
ItemDesc,
ItemUM,
Whse,
Qty,
Cost,
TotalCost,
TotalPost,
Loc,
Job,
Ref,
From,
To,
Type,
RefDesc,
LocCode,
UserCode,
DocumentNum,
Backflush,
WC,
ReasonCode,
ReasonDesc,
RefType,
RefNum,
RefLineSuf,
RefRelease,
DerivedMatltranCode
) AS
SELECT
mt.site_ref
mt.trans_num -- AS TransNum
,
mt.trans_date -- AS TransDate
,
mt.trans_type -- AS TransType
,
mt.item -- AS Item
,
ISNULL(
tt_MTCode.InvItemDesc,
ISNULL(
tt_MTCode.NonInvItemDesc,
ISNULL(
tt_MTCode.LineItemDesc,
tt_MTCode.HistLineItemDesc
)
)
) -- AS ItemDesc
,
item.u_m -- AS ItemUM
,
mt.whse -- AS Whse
,
mt.qty -- AS Qty
,
CASE
WHEN ISNULL(mt.cost, 0) <> 0 THEN (mt.cost)
ELSE (
mt.matl_cost + mt.lbr_cost + mt.fovhd_cost + mt.vovhd_cost + mt.out_cost
)
END -- AS Cost
,
(mt.qty * mt.cost) -- AS TotalCost
,
(
CASE
WHEN tt_MTCode.mtcode IN ('A', 'NL', 'NK', 'NS', 'NW', 'DS') THEN CASE
WHEN (
SELECT mtamt.amt
FROM matltran_amt_mst mtamt with (readuncommitted)
WHERE mtamt.trans_num = mt.trans_num
AND mtamt.trans_seq = 1
and mtamt.site_ref=mt.site_ref
) <> 0 THEN (
SELECT mtamt.amt
FROM matltran_amt_mst mtamt with (readuncommitted)
WHERE mtamt.trans_num = mt.trans_num
AND mtamt.trans_seq = 1
and mtamt.site_ref=mt.site_ref
)
END
WHEN tt_MTCode.mtcode = 'CF' THEN 0
WHEN tt_MTCode.mtcode = 'AC' THEN (
SELECT SUM(mtamt.amt)
FROM matltran_amt_mst mtamt with (readuncommitted)
WHERE mtamt.trans_num = mt.trans_num
AND mtamt.acct IS NULL
and mtamt.site_ref=mt.site_ref
)
WHEN tt_MTCode.mtcode = 'V' THEN (
SELECT SUM(mtamt.amt)
FROM matltran_amt_mst mtamt with (readuncommitted)
WHERE mtamt.trans_num = mt.trans_num
and mtamt.site_ref=mt.site_ref
AND mtamt.acct = CASE
WHEN item.cost_type = 'S' THEN (
SELECT prodvar.pcv_acct
FROM prodvar_mst prodvar with (readuncommitted)
INNER JOIN item_mst itempv with (readuncommitted) ON itempv.product_code = prodvar.product_code
AND itempv.item = mt.item
and prodvar.site_ref = itempv.site_ref
)
ELSE (
SELECT prodcode.inv_adj_acct
FROM prodcode_mst prodcode with (readuncommitted)
INNER JOIN item_mst itempc with (readuncommitted) ON itempc.product_code = prodcode.product_code
AND itempc.item = mt.item
and prodcode.site_ref = itempc.site_ref
)
END
/* CASE WHEN item.cost_type */
)
WHEN tt_MTCode.mtcode IN ('L', 'TT', 'T') THEN ISNULL(
(
SELECT SUM(mtamt.amt)
FROM matltran_amt_mst mtamt with (readuncommitted)
WHERE mtamt.trans_num = mt.trans_num
and mtamt.site_ref=mt.site_ref
AND (
(
mt.qty >= 0
and mtamt.amt >= 0
)
OR (
mt.qty < 0
and mtamt.amt < 0
)
)
AND mtamt.include_in_inventory_bal_calc = 1
),
0
)
ELSE CASE
WHEN EXISTS(
SELECT 1
FROM matltran_amt_mst matltran_amt with (readuncommitted)
WHERE matltran_amt.trans_num = mt.trans_num and
matltran_amt.site_ref=mt.site_ref
AND (
(
mt.qty >= 0
and matltran_amt.amt >= 0
)
OR (
mt.qty < 0
and matltran_amt.amt < 0
)
)
) THEN (
SELECT SUM(mtamt.amt)
FROM matltran_amt_mst mtamt with (readuncommitted)
WHERE mtamt.trans_num = mt.trans_num
and mtamt.site_ref=mt.site_ref
AND (
(
mt.qty >= 0
and mtamt.amt >= 0
)
OR (
mt.qty < 0
and mtamt.amt < 0
)
)
)
ELSE 0
END
END
) -- AS TotalPost
,
tt_MTCode.loc -- AS Loc
,
tt_MTCode.job -- AS Job
-- @Ref
,
(
CASE
WHEN mt.trans_type = 'A' THEN (tt_MTCode.Ref)
WHEN mt.trans_type = 'B' THEN (tt_MTCode.Ref)
WHEN tt_MTCode.mtcode = 'CS' THEN (tt_MTCode.job)
WHEN tt_MTCode.mtcode = 'CM' THEN (tt_MTCode.job)
WHEN tt_MTCode.mtcode = 'CA' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.ref_num
)
WHEN tt_MTCode.mtcode = 'CB' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.ref_num + ' ' + (
CASE
WHEN mt.ref_release > 0 THEN CAST(mt.ref_release AS NVARCHAR(4))
ELSE ''
END
)
)
WHEN tt_MTCode.mtcode = 'C' THEN (tt_MTCode.job)
WHEN mt.trans_type = 'D' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.ref_num + ' ' + + (
CASE
WHEN mt.ref_release > 0 THEN CAST(mt.ref_release AS NVARCHAR(4))
ELSE ''
END
)
)
WHEN tt_MTCode.mtcode = 'FK' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode)
)
WHEN tt_MTCode.mtcode = 'FS' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.ref_num + ' ' + (
CASE
WHEN mt.ref_release > 0 THEN CAST(mt.ref_release AS NVARCHAR(4))
ELSE ''
END
)
)
WHEN tt_MTCode.mtcode = 'F' THEN (tt_MTCode.job)
WHEN mt.trans_type = 'G' THEN (tt_MTCode.Ref)
WHEN mt.trans_type = 'H' THEN (tt_MTCode.Ref)
WHEN tt_MTCode.mtcode = 'IC' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + tt_MTCode.Ref
)
WHEN tt_MTCode.mtcode = 'CK' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.ref_num
)
WHEN tt_MTCode.mtcode = 'IK' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + (
CASE
WHEN mt.ref_release > 0 THEN CAST(mt.ref_release AS NVARCHAR(4))
ELSE ''
END
)
)
WHEN tt_MTCode.mtcode = 'CP' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.ref_num + ' ' + (
CASE
WHEN mt.ref_release > 0 THEN CAST(mt.ref_release AS NVARCHAR(4))
ELSE ''
END
)
)
WHEN tt_MTCode.mtcode = 'IS' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.ref_num + ' ' + (
CASE
WHEN mt.ref_release > 0 THEN CAST(mt.ref_release AS NVARCHAR(4))
ELSE ''
END
)
)
WHEN tt_MTCode.mtcode = 'IW' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.wc
)
WHEN tt_MTCode.mtcode = 'CF' THEN (tt_MTCode.job)
WHEN tt_MTCode.mtcode = 'I' THEN (tt_MTCode.job)
WHEN tt_MTCode.mtcode = 'L' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + tt_MTCode.Ref
)
WHEN mt.trans_type = 'M' THEN (tt_MTCode.Ref)
WHEN tt_MTCode.mtcode = 'NK' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + (
CASE
WHEN mt.ref_release > 0 THEN CAST(mt.ref_release AS NVARCHAR(4))
ELSE ''
END
)
)
WHEN tt_MTCode.mtcode = 'NA' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.ref_num
)
WHEN tt_MTCode.mtcode = 'NS' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.ref_num + ' ' + (
CASE
WHEN mt.ref_release > 0 THEN CAST(mt.ref_release AS NVARCHAR(4))
ELSE ''
END
)
)
WHEN tt_MTCode.mtcode = 'NB' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.ref_num
)
WHEN tt_MTCode.mtcode = 'NW' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.wc
)
WHEN tt_MTCode.mtcode = 'NL' THEN (tt_MTCode.job)
WHEN tt_MTCode.mtcode = 'N' THEN (tt_MTCode.job)
WHEN tt_MTCode.mtcode = 'OW' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.ref_num
)
WHEN tt_MTCode.mtcode = 'O' THEN (tt_MTCode.job)
WHEN mt.trans_type = 'P' THEN (tt_MTCode.Ref)
WHEN tt_MTCode.mtcode = 'RK' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode)
)
WHEN tt_MTCode.mtcode = 'RS' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.ref_num + ' ' + (
CASE
WHEN mt.ref_release > 0 THEN CAST(mt.ref_release AS NVARCHAR(4))
ELSE ''
END
)
)
WHEN tt_MTCode.mtcode = 'RA' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + tt_MTCode.Ref
)
WHEN tt_MTCode.mtcode = 'R' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + tt_MTCode.Ref
)
WHEN tt_MTCode.mtcode = 'V' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + tt_MTCode.Ref
)
WHEN tt_MTCode.mtcode = 'SA' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + tt_MTCode.Ref
)
WHEN tt_MTCode.mtcode = 'S' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + tt_MTCode.Ref
)
/* for matltran.type = T and matltran.qty > 0, MTCode = 'TT', -- Transfer To; and
* for matltran.type = T and matltran.qty <= 0, MTCode = 'T', -- Transfer From
*/
/* FOR MULTI-SITE TRANSFER ORDERS WE WANT TO DISPLAY THE SITE IDENTIFIER
* ALONG WITH THE TRANSFER ORDER NUMBER AND LINE IF THE OTHER HALF OF THE
* BALANCING TRANSACTION IS LOCATION IN THE OTHER SITES DATABASE. THIS
* CAN BE DETERMINED AS FOLLOWS:
*
* SCENARIO 1
* ----------
* SITE A SHIPS TO SITE B'S TRANSIT LOCATION AND THEN SITE B RECEIVES THE
* ITEM FROM SITE B'S TRANSIT LOCATION INTO SITE B'S INVENTORY. FOB =
* SITE A.
*
* SCENARIO 2
* ----------
* SITE A SHIPS TO SITE A'S TRANSIT LOCATION AND THEN SITE B RECEIVES THE
* ITEM FROM SITE A'S TRANSIT LOCATION INTO SITE B'S INVENTORY. FOB =
* SITE B.
*
* SELECTED FIELD VALUES WOULD BE AS FOLLOWS:
* ------------------------------------------
* SCENARIO 1
* matltran from-site to-site fob-site local-site itemloc FRM/TO TRX
* 1 A B A A T from
* 2 A B A B T to
* 3 A B A B <>T from
* 4 A B A B <>T to
*
* SCENARIO 2
* matltran from-site to-site fob-site local-site itemloc FRM/TO TRX
* 5 A B B A T from
* 6 A B B A T to
* 7 A B B A <>T from
* 8 A B B B <>T to
*
* WE WANT THE SITE IDENTIFIER TO PRINT ONLY WITH matltran NUMBERS 1, 2,
* 7 and 8.
*/
WHEN tt_MTCode.mtcode = 'TT' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + tt_MTCode.Ref
) + (
CASE
WHEN trn1.RowPointer IS NOT NULL
AND iloc.RowPointer IS NOT NULL
AND (
trn2.RowPointer IS NOT NULL
OR (
trn3.RowPointer IS NOT NULL
AND ilocTypeT.RowPointer IS NOT NULL
)
) THEN ' ' + (trn1.from_site)
ELSE ''
END
)
WHEN tt_MTCode.mtcode = 'T' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + tt_MTCode.Ref
) + (
CASE
WHEN trn1.RowPointer IS NOT NULL
AND iloc.RowPointer IS NOT NULL
AND (
trn2.RowPointer IS NOT NULL
OR (
trn3.RowPointer IS NOT NULL
AND ilocTypeNotT.RowPointer IS NOT NULL
)
) THEN ' ' + (trn1.from_site)
ELSE ''
END
)
WHEN tt_MTCode.mtcode = 'WC' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.ref_num + ' ' + CAST(mt.ref_line_suf AS NVARCHAR) + ' ' + (
CASE
WHEN mt.ref_release > 0 THEN CAST(mt.ref_release AS NVARCHAR(4))
ELSE ''
END
)
)
WHEN tt_MTCode.mtcode = 'W' THEN (tt_MTCode.job)
WHEN tt_MTCode.mtcode = 'WK' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + (
CASE
WHEN mt.ref_release > 0 THEN CAST(mt.ref_release AS NVARCHAR(4))
ELSE ''
END
)
)
WHEN tt_MTCode.mtcode = 'WO' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + tt_MTCode.Ref
)
WHEN tt_MTCode.mtcode = 'WA' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + tt_MTCode.Ref
)
WHEN tt_MTCode.mtcode = 'WM' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + tt_MTCode.Ref
)
WHEN tt_MTCode.mtcode = 'WR' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + tt_MTCode.Ref
)
WHEN tt_MTCode.mtcode = 'WP' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + tt_MTCode.Ref
)
WHEN tt_MTCode.mtcode = 'WS' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.ref_num + ' ' + (
CASE
WHEN mt.ref_release > 0 THEN CAST(mt.ref_release AS NVARCHAR(4))
ELSE ''
END
)
)
WHEN tt_MTCode.mtcode = 'WW' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.wc
)
ELSE (mt.ref_type + ' ' + tt_MTCode.Ref)
END
) -- AS Ref
-- @From
,
(
CASE
WHEN mt.trans_type = 'A' THEN (tt_MTCode.loc)
WHEN mt.trans_type = 'B' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'CS' THEN (tt_MTCode.job)
WHEN tt_MTCode.mtcode = 'CM' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.loc
)
WHEN tt_MTCode.mtcode = 'CA' THEN ''
WHEN tt_MTCode.mtcode = 'CB' THEN ''
WHEN tt_MTCode.mtcode = 'C' THEN ''
WHEN mt.trans_type = 'D' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.ref_num + ' ' + (
CASE
WHEN mt.ref_release > 0 THEN CAST(mt.ref_release AS NVARCHAR(4))
ELSE ''
END
)
)
WHEN tt_MTCode.mtcode = 'FK' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode)
)
WHEN tt_MTCode.mtcode = 'FS' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.ref_num + ' ' + (
CASE
WHEN mt.ref_release > 0 THEN CAST(mt.ref_release AS NVARCHAR(4))
ELSE ''
END
)
)
WHEN tt_MTCode.mtcode = 'F' THEN (tt_MTCode.job)
WHEN tt_MTCode.mtcode = 'G' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'H' THEN (
CASE
WHEN mt.ref_type = 'F' THEN (
dbo.GetLabel('@fs_pck_hdr.sro_num') + ' ' + tt_MTCode.Ref
)
ELSE (tt_MTCode.loc)
END
)
WHEN tt_MTCode.mtcode = 'IC' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'CK' THEN ''
WHEN tt_MTCode.mtcode = 'IK' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'CP' THEN ''
WHEN tt_MTCode.mtcode = 'IS' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'IW' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'CF' THEN ''
WHEN tt_MTCode.mtcode = 'I' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'L' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'MT' THEN ''
WHEN tt_MTCode.mtcode = 'M' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'NK' THEN ''
WHEN tt_MTCode.mtcode = 'NA' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.ref_num
)
WHEN tt_MTCode.mtcode = 'NS' THEN ''
WHEN tt_MTCode.mtcode = 'NB' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.ref_num
)
WHEN tt_MTCode.mtcode = 'NW' THEN ''
WHEN tt_MTCode.mtcode = 'NL' THEN ''
WHEN tt_MTCode.mtcode = 'N' THEN (tt_MTCode.job)
WHEN tt_MTCode.mtcode = 'OW' THEN ''
WHEN tt_MTCode.mtcode = 'O' THEN ''
WHEN tt_MTCode.mtcode = 'P' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'RK' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'RS' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'RA' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'R' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + tt_MTCode.Ref
)
WHEN tt_MTCode.mtcode = 'V' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + tt_MTCode.Ref
)
WHEN tt_MTCode.mtcode = 'SA' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + tt_MTCode.Ref
)
WHEN tt_MTCode.mtcode = 'S' THEN (tt_MTCode.loc)
/* for matltran.type = T and matltran.qty > 0, MTCode = 'TT', -- Transfer To; and
* for matltran.type = T and matltran.qty <= 0, MTCode = 'T', -- Transfer From
* see comments above
*/
WHEN tt_MTCode.mtcode = 'TT' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + tt_MTCode.Ref
) + (
CASE
WHEN trn1.RowPointer IS NOT NULL
AND iloc.RowPointer IS NOT NULL
AND (
trn2.RowPointer IS NOT NULL
OR (
trn3.RowPointer IS NOT NULL
AND ilocTypeT.RowPointer IS NOT NULL
)
) THEN ' ' + (trn1.from_site)
ELSE ''
END
)
WHEN tt_MTCode.mtcode = 'T' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'WC' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'W' THEN (tt_MTCode.job)
WHEN tt_MTCode.mtcode = 'WK' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + (
CASE
WHEN mt.ref_release > 0 THEN CAST(mt.ref_release AS NVARCHAR(4))
ELSE ''
END
)
)
WHEN tt_MTCode.mtcode = 'WO' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + tt_MTCode.Ref
)
WHEN tt_MTCode.mtcode = 'WA' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'WM' THEN (
CASE
WHEN mt.qty > 0 THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + tt_MTCode.Ref
)
ELSE (tt_MTCode.loc)
END
)
WHEN tt_MTCode.mtcode = 'WR' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'WP' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + tt_MTCode.Ref
)
WHEN tt_MTCode.mtcode = 'WS' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.ref_num + ' ' + (
CASE
WHEN mt.ref_release > 0 THEN CAST(mt.ref_release AS NVARCHAR(4))
ELSE ''
END
)
)
WHEN tt_MTCode.mtcode = 'WW' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.wc
)
ELSE (tt_MTCode.Ref)
END
) -- AS tFrom
-- @To
,
(
CASE
WHEN mt.trans_type = 'A' THEN (tt_MTCode.loc)
WHEN mt.trans_type = 'B' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'CS' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.loc
)
WHEN tt_MTCode.mtcode = 'CM' THEN (tt_MTCode.job)
WHEN tt_MTCode.mtcode = 'CA' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.ref_num
)
WHEN tt_MTCode.mtcode = 'CB' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.ref_num + ' ' + (
CASE
WHEN mt.ref_release > 0 THEN CAST(mt.ref_release AS NVARCHAR(4))
ELSE ''
END
)
)
WHEN tt_MTCode.mtcode = 'C' THEN (tt_MTCode.job)
WHEN mt.trans_type = 'D' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'FK' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'FS' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'F' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'G' THEN (
CASE
WHEN mt.ref_type = 'F' THEN (
dbo.GetLabel('@fs_pck_hdr.sro_num') + ' ' + tt_MTCode.Ref
)
ELSE ''
END
)
WHEN tt_MTCode.mtcode = 'H' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'IC' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + tt_MTCode.Ref
)
WHEN tt_MTCode.mtcode = 'CK' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.ref_num
)
WHEN tt_MTCode.mtcode = 'IK' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + (
CASE
WHEN mt.ref_release > 0 THEN CAST(mt.ref_release AS NVARCHAR(4))
ELSE ''
END
)
)
WHEN tt_MTCode.mtcode = 'CP' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.ref_num + ' ' + (
CASE
WHEN mt.ref_release > 0 THEN CAST(mt.ref_release AS NVARCHAR(4))
ELSE ''
END
)
)
WHEN tt_MTCode.mtcode = 'IS' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.ref_num + ' ' + (
CASE
WHEN mt.ref_release > 0 THEN CAST(mt.ref_release AS NVARCHAR(4))
ELSE ''
END
)
)
WHEN tt_MTCode.mtcode = 'IW' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.wc
)
WHEN tt_MTCode.mtcode = 'CF' THEN (tt_MTCode.job)
WHEN tt_MTCode.mtcode = 'I' THEN (tt_MTCode.job)
WHEN tt_MTCode.mtcode = 'L' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + tt_MTCode.Ref
)
WHEN tt_MTCode.mtcode = 'MT' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'M' THEN ''
WHEN tt_MTCode.mtcode = 'NK' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + (
CASE
WHEN mt.ref_release > 0 THEN CAST(mt.ref_release AS NVARCHAR(4))
ELSE ''
END
)
)
WHEN tt_MTCode.mtcode = 'NA' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.ref_num + ' ' + mt.loc
)
WHEN tt_MTCode.mtcode = 'NS' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.ref_num + ' ' + (
CASE
WHEN mt.ref_release > 0 THEN CAST(mt.ref_release AS NVARCHAR(4))
ELSE ''
END
)
)
WHEN tt_MTCode.mtcode = 'NB' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.ref_num + ' ' + mt.loc
)
WHEN tt_MTCode.mtcode = 'NW' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.wc
)
WHEN tt_MTCode.mtcode = 'NL' THEN (tt_MTCode.job)
WHEN tt_MTCode.mtcode = 'N' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.ref_num + '-' + REPLICATE('0', 4 - LEN(CAST(ref_line_suf AS NVARCHAR))) + CAST(ref_line_suf AS NVARCHAR) + ' ' + LTRIM(mt.loc)
)
WHEN tt_MTCode.mtcode = 'OW' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.ref_num
)
WHEN tt_MTCode.mtcode = 'O' THEN (tt_MTCode.job)
WHEN tt_MTCode.mtcode = 'P' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'RK' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode)
)
WHEN tt_MTCode.mtcode = 'RS' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + mt.ref_num + ' ' + (
CASE
WHEN mt.ref_release > 0 THEN CAST(mt.ref_release AS NVARCHAR(4))
ELSE ''
END
)
)
WHEN tt_MTCode.mtcode = 'RA' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + tt_MTCode.Ref
)
WHEN tt_MTCode.mtcode = 'R' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'V' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'SA' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'S' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + tt_MTCode.Ref
)
WHEN tt_MTCode.mtcode = 'TT' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'T' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + tt_MTCode.Ref
) + (
CASE
WHEN trn1.RowPointer IS NOT NULL
AND iloc.RowPointer IS NOT NULL
AND (
trn2.RowPointer IS NOT NULL
OR (
trn3.RowPointer IS NOT NULL
AND ilocTypeNotT.RowPointer IS NOT NULL
)
) THEN ' ' + (trn1.from_site)
ELSE ''
END
)
WHEN tt_MTCode.mtcode = 'WC' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + tt_MTCode.Ref
)
WHEN tt_MTCode.mtcode = 'W' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'WK' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'WO' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'WA' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + tt_MTCode.Ref
)
WHEN tt_MTCode.mtcode = 'WM' THEN (
CASE
WHEN mt.qty > 0 THEN (tt_MTCode.loc)
ELSE (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + tt_MTCode.Ref
)
END
)
WHEN tt_MTCode.mtcode = 'WR' THEN (
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) + ' ' + tt_MTCode.Ref
)
WHEN tt_MTCode.mtcode = 'WP' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'WS' THEN (tt_MTCode.loc)
WHEN tt_MTCode.mtcode = 'WW' THEN (tt_MTCode.loc)
ELSE (tt_MTCode.loc)
END
) -- AS tTo
,
(
CASE
WHEN tt_MTCode.mtcode = 'CF' THEN 'I' + ' ' + dbo.GetLabel('@:MatlTransType:' + tt_MTCode.mtcode)
WHEN tt_MTCode.mtcode = 'V' THEN 'R' + ' ' + dbo.GetLabel('@:MatlTransType:' + tt_MTCode.mtcode)
WHEN tt_MTCode.mtcode = 'G'
AND mt.ref_type = 'F' THEN tt_MTCode.mtcode + ' ' + dbo.GetLabel('@SSSFSSROIssue')
WHEN tt_MTCode.mtcode = 'H'
AND mt.ref_type = 'F' THEN tt_MTCode.mtcode + ' ' + dbo.GetLabel('@SSSFSSROReturn')
ELSE tt_MTCode.mtcode + ' ' + dbo.GetLabel('@:MatlTransType:' + tt_MTCode.mtcode)
END
) -- AS Type
,
dbo.GetLabel('@:MatltranRefDesc:' + tt_MTCode.mtcode) -- AS RefDesc
,
dbo.GetLabel('@:MatltranLocalCode:' + tt_MTCode.mtcode) -- AS LocCode
,
mt.user_code -- AS UserCode
,
mt.document_num -- AS DocumentNum
,
mt.backflush -- AS Backflush
,
mt.wc -- AS WC
,
mt.reason_code AS ReasonCode,
(
SELECT reason.description
FROM reason_mst reason with (readuncommitted)
WHERE reason.reason_class = CASE
tt_MTCode.mtcode
WHEN 'A' THEN 'INV ADJUST'
WHEN 'G' THEN 'MISC ISSUE'
WHEN 'H' THEN 'MISC RCPT'
WHEN 'L' THEN 'TRN LOSS'
WHEN 'T' then 'TRANSFER RETURN'
WHEN 'TT' then 'TRANSFER RETURN'
WHEN 'WA' THEN 'CO RETURN'
WHEN 'WC' THEN 'CO RETURN'
WHEN 'WO' THEN 'CO RETURN'
WHEN 'SA' THEN 'CO RETURN'
WHEN 'WM' THEN 'RMA RETURN'
WHEN 'RA' THEN 'PO RETURN'
WHEN 'R' THEN 'PO RETURN'
WHEN 'WP' THEN 'PO RETURN'
WHEN 'WR' THEN 'PO RETURN'
ELSE 'MFG SCRAP'
/* when 'D' or 'WR' or 'WS' or 'WW' then */
END
AND reason.reason_code = mt.reason_code
and reason.site_ref=mt.site_ref
) -- AS ReasonDesc
,
mt.ref_type,
mt.ref_num,
mt.ref_line_suf,
mt.ref_release,
tt_MTCode.mtcode -- AS DerivedMatltranCode
FROM (
SELECT trans_num,
(
ISNULL(
ref_num + ' ' + CAST(ref_line_suf AS NVARCHAR) + CASE
WHEN ref_release > 0 THEN '-' + CAST(ref_release AS NVARCHAR(4))
ELSE ''
END,
''
)
) AS Ref,
(
dbo.GetLabel('@:MatltranRefDesc:F') + ' ' + ref_num + '-' + + REPLICATE('0', 4 - LEN(CAST(ref_line_suf AS NVARCHAR))) + CAST(ref_line_suf AS NVARCHAR) + CASE
WHEN ref_release > 0 THEN ' ' + CAST(ref_release AS NVARCHAR(4))
ELSE ''
END
) AS job,
(
loc + (
CASE
WHEN lot IS NOT NULL THEN '/' + lot
ELSE ''
END
)
) AS loc -- ITEM DESCRIPTION
,
(
SELECT item.description
FROM item_mst item with (readuncommitted)
WHERE item.item = matltran.item
and item.site_ref=matltran.site_ref
) AS InvItemDesc,
(
SELECT non_inventory_item.description
FROM non_inventory_item_mst non_inventory_item with (readuncommitted)
WHERE non_inventory_item.item = matltran.item
and non_inventory_item.site_ref = matltran.site_ref
) AS NonInvItemDesc,
(
CASE
WHEN ref_type = 'J' THEN (
SELECT TOP 1 jobmatl.description
FROM jobmatl_mst jobmatl with (readuncommitted)
WHERE jobmatl.job = matltran.ref_num
AND jobmatl.suffix = matltran.ref_line_suf
AND jobmatl.oper_num = matltran.ref_release
AND jobmatl.item = matltran.item
and jobmatl.site_ref = matltran.site_ref
)
WHEN ref_type = 'C' THEN (
SELECT projmatl.item_desc
FROM projmatl_mst projmatl with (readuncommitted)
WHERE projmatl.proj_num = matltran.ref_num
AND projmatl.task_num = matltran.ref_line_suf
AND projmatl.seq = matltran.ref_release
and projmatl.site_ref = matltran.site_ref
)
WHEN ref_type = 'R' THEN (
SELECT rmaitem.description
FROM rmaitem_mst rmaitem with (readuncommitted)
WHERE rmaitem.rma_num = matltran.ref_num
AND rmaitem.rma_line = matltran.ref_line_suf
and rmaitem.site_ref = matltran.site_ref
)
WHEN ref_type = 'O' THEN (
SELECT coitem.description
FROM coitem_mst coitem with (readuncommitted)
WHERE coitem.co_num = matltran.ref_num
AND coitem.co_line = matltran.ref_line_suf
AND coitem.co_release = matltran.ref_release
and coitem.site_ref = matltran.site_ref
)
WHEN ref_type = 'P' THEN (
SELECT poitem.description
FROM poitem_mst poitem with (readuncommitted)
WHERE poitem.po_num = matltran.ref_num
AND poitem.po_line = matltran.ref_line_suf
AND poitem.po_release = matltran.ref_release
and poitem.site_ref = matltran.site_ref
)
ELSE NULL
END
) AS LineItemDesc,
(
CASE
WHEN ref_type = 'O' THEN (
SELECT citemh.description
FROM citemh_mst citemh with (readuncommitted)
WHERE citemh.co_num = matltran.ref_num
AND citemh.co_line = matltran.ref_line_suf
AND citemh.co_release = matltran.ref_release
and citemh.site_ref = matltran.site_ref
)
WHEN ref_type = 'P' THEN (
SELECT pitemh.description
FROM pitemh_mst pitemh with (readuncommitted)
WHERE pitemh.po_num = matltran.ref_num
AND pitemh.po_line = matltran.ref_line_suf
AND pitemh.po_release = matltran.ref_release
and pitemh.site_ref = matltran.site_ref
)
ELSE NULL
END
) AS HistLineItemDesc -- ITEM DESCRIPTION
,
(
CASE
WHEN trans_type = 'A' THEN CASE
WHEN qty = 0 THEN 'AC'
/* Cost Adjust */
ELSE 'A'
/* Stock Adjust */
END
WHEN trans_type = 'B' THEN 'B'
/* Cycle Count */
WHEN trans_type = 'C' THEN CASE
WHEN loc IS NOT NULL THEN CASE
WHEN qty < 0 THEN 'CS'
/* Job Split */
ELSE 'CM'
/* Job Merge */
END
WHEN ref_type = 'K' THEN 'CA'
/* JIT WIP Create */
WHEN ref_type = 'S' THEN 'CB'
/* PS WIP Create */
ELSE 'C'
/* Job WIP Create */
END
WHEN trans_type = 'D' THEN CASE
WHEN qty < 0 THEN 'DA'
/* PS Scrap Return */
ELSE 'DS'
/* PS Scrap Issue */
END
WHEN trans_type = 'F' THEN CASE
WHEN ref_type = 'K' THEN 'FK'
/* JIT Finish */
WHEN ref_type = 'S' THEN 'FS'
/* PS Finish */
ELSE 'F'
/* Job Finish */
END
WHEN trans_type = 'G' THEN 'G'
/* Misc Issue */
WHEN trans_type = 'H' THEN 'H'
/* Misc Rcpt */
WHEN trans_type = 'I' THEN CASE
WHEN ref_type = 'C' THEN 'IC'
/* Project Resource Issue */
WHEN ref_type = 'K' THEN CASE
WHEN qty = 0 THEN 'CK'
/* JIT WIP Change */
ELSE 'IK'
/* JIT Issue */
END
WHEN ref_type = 'S' THEN CASE
WHEN qty = 0 THEN 'CP'
/* PS WIP Change */
ELSE 'IS'
/* PS Issue */
END
WHEN ref_type = 'W' THEN 'IW'
/* WC Matl Issue */
ELSE CASE
WHEN qty = 0 THEN 'CF'
/* Job WIP Change */
ELSE 'I'
/* Job Issue */
END
END
WHEN trans_type = 'L' THEN 'L'
/* Transfer Loss */
WHEN trans_type = 'M' THEN CASE
WHEN qty > 0 THEN 'MT'
/* Stock Move To */
ELSE 'M'
/* Stock Move From */
END
WHEN trans_type = 'N' THEN CASE
WHEN ref_type = 'K' THEN CASE
WHEN qty = 0 THEN 'NK'
/* JIT Labor */
ELSE 'NA'
/* JIT Next Oper */
END
WHEN ref_type = 'S' THEN CASE
WHEN qty = 0 THEN 'NS'
/* PS Labor */
ELSE 'NB'
/* PS Next Oper */
END
WHEN ref_type = 'W' THEN 'NW'
/* WC Labor */
ELSE CASE
WHEN qty = 0 THEN 'NL'
/* Job Labor */
ELSE 'N'
/* Job Next Oper */
END
END
WHEN trans_type = 'O' THEN CASE
WHEN ref_type = 'W' THEN 'OW'
/* WC Other Cost */
ELSE 'O'
/* Job Other Cost */
END
WHEN trans_type = 'P' THEN 'P'
/* Physical Inventory */
WHEN trans_type = 'R' THEN CASE
WHEN ref_type = 'K' THEN 'RK'
/* JIT Receipt */
WHEN ref_type = 'S' THEN 'RS'
/* PS Receipt */
ELSE CASE
WHEN qty < 0 THEN 'RA'
/* PO Receipt Adj */
WHEN qty > 0 THEN 'R'
/* PO Receipt */
ELSE 'V'
/* PO Cost Variance */
END
END
WHEN trans_type = 'S' THEN CASE
WHEN qty > 0 THEN 'SA'
/* CO Ship Adj */
ELSE 'S'
/* CO Ship */
END
WHEN trans_type = 'T' THEN CASE
WHEN qty > 0 THEN 'TT'
/* Transfer To */
ELSE 'T'
/* Transfer From */
END
WHEN trans_type = 'W' THEN CASE
WHEN ref_type = 'C' THEN 'WC'
/* Project Resource Return */
WHEN ref_type = 'J' THEN 'W'
/* Job Withdrawal */
WHEN ref_type = 'K' THEN 'WK'
/* JIT Return */
WHEN ref_type = 'O' THEN CASE
WHEN qty > 0 THEN 'WO'
/* CO Return */
ELSE 'WA'
/* CO Return Adj */
END
WHEN ref_type = 'R' THEN 'WM'
/* RMA Return */
WHEN ref_type = 'P' THEN CASE
WHEN qty < 0 THEN 'WR'
/* PO Return Adj */
ELSE 'WP'
/* PO Return */
END
WHEN ref_type = 'S' THEN 'WS'
/* PS Return */
WHEN ref_type = 'W' THEN 'WW'
/* WC Matl Return */
ELSE ''
END
ELSE ''
END
) AS MTCode
FROM matltran_mst matltran
) tt_MTCode
JOIN matltran_mst mt with (readuncommitted) ON mt.trans_num = tt_MTCode.trans_num and mt.site_ref= tt_MTCode.site_ref
LEFT JOIN item_mst item with (readuncommitted) ON item.item = mt.item And item.site_ref = mt.site_ref
LEFT JOIN itemloc_mst iloc with (readuncommitted) ON iloc.whse = mt.whse
AND iloc.item = mt.item
AND iloc.loc = mt.loc
and iloc.site_ref=mt.site_ref
LEFT JOIN itemloc_mst ilocTypeT with (readuncommitted) ON ilocTypeT.whse = mt.whse
AND ilocTypeT.item = mt.item
AND ilocTypeT.loc = mt.loc
and ilocTypeT.loc_type = 'T'
and ilocTypeT.site_ref=mt.site_ref
LEFT JOIN itemloc_mst ilocTypeNotT with (readuncommitted) ON ilocTypeNotT.whse = mt.whse
AND ilocTypeNotT.item = mt.item
AND ilocTypeNotT.loc = mt.loc
and ilocTypeNotT.loc_type <> 'T'
and ilocTypeNotT.site_ref=mt.site_ref
LEFT JOIN transfer_mst trn1 with (readuncommitted) ON trn1.trn_num = mt.ref_num
LEFT JOIN transfer_mst trn2 with (readuncommitted) ON trn2.trn_num = mt.ref_num
AND trn2.fob_site = mt.site_ref
LEFT JOIN transfer_mst trn3 with (readuncommitted) ON trn3.trn_num = mt.ref_num
AND trn3.from_site = trn3.fob_site
AND trn3.to_site = mt.site_ref
GO
