Select t.Company_Cd,
t.Project_Cd,
t.Whs_Id,
t.Brand_Id,
t.Item_Cd,
t.Item_Spec_Id,
t.Unit,
t.Lot_No,
t.Location_Id,
t.Quantity Po_In,
0 Po_Out,
0 Pd_In,
0 r_In,
0 r_Out,
0 l_In,
0 s_Out,
0 Di_In,
0 Di_Out,
0 p_In,
0 p_Out,
0 Onhand,
t.inv_operate_date,
t.dely_org_cd org_cd
From Scm_Wms_Inv_Transactions_Tbl t
Where t.Inv_Operate_Type = 'P' --采购入库
Union All
Select t.Source_Company_Cd Company_Cd,
t.Source_Project_Cd Project_Cd,
t.Source_Whs_Id Whs_Id,
t.Brand_Id,
t.Item_Cd,
t.Item_Spec_Id,
t.Unit,
t.Lot_No,
t.Source_Location_Id Location_Id,
0 Po_In,
t.Quantity Po_Out,
0 Pd_In,
0 r_In,
0 r_Out,
0 l_In,
0 s_Out,
0 Di_In,
0 Di_Out,
0 p_In,
0 p_Out,
0 Onhand,
t.inv_operate_date,
t.rcpt_org_cd org_cd
From Scm_Wms_Inv_Transactions_Tbl t
Where t.Inv_Operate_Type = 'R' --采购退货
Union All
Select t.Company_Cd,
t.Project_Cd,
t.Whs_Id,
t.Brand_Id,
t.Item_Cd,
t.Item_Spec_Id,
t.Unit,
t.Lot_No,
t.Location_Id,
t.Quantity Po_In,
0 Po_Out,
0 Pd_In,
0 r_In,
0 r_Out,
0 l_In,
0 s_Out,
0 Di_In,
0 Di_Out,
0 p_In,
0 p_Out,
0 Onhand,
t.inv_operate_date,
t.rcpt_org_cd org_cd
From Scm_Wms_Inv_Transactions_Tbl t
Where t.Inv_Operate_Type = 'PR' --现场接收
Union All
Select t.Company_Cd,
t.Project_Cd,
t.Whs_Id,
t.Brand_Id,
t.Item_Cd,
t.Item_Spec_Id,
t.Unit,
t.Lot_No,
t.Location_Id,
t.Quantity Po_In,
0 Po_Out,
0 Pd_In,
0 r_In,
0 r_Out,
0 l_In,
0 s_Out,
0 Di_In,
0 Di_Out,
0 p_In,
0 p_Out,
0 Onhand,
t.inv_operate_date,
t.rcpt_org_cd org_cd
From Scm_Wms_Inv_Transactions_Tbl t
Where t.Inv_Operate_Type = 'PS' --采购入库
Union All
Select t.Company_Cd,
t.Project_Cd,
t.Whs_Id,
t.Brand_Id,
t.Item_Cd,
t.Item_Spec_Id,
t.Unit,
t.Lot_No,
t.Location_Id,
t.Quantity Po_In,
0 Po_Out,
0 Pd_In,
0 r_In,
0 r_Out,
0 l_In,
0 s_Out,
0 Di_In,
0 Di_Out,
0 p_In,
0 p_Out,
0 Onhand,
t.inv_operate_date,
t.dely_org_cd org_cd
From Scm_Wms_Inv_Transactions_Tbl t
Where t.Inv_Operate_Type = 'RR' --现场退货
Union All
Select t.Company_Cd,
t.Project_Cd,
t.Whs_Id,
t.Brand_Id,
t.Item_Cd,
t.Item_Spec_Id,
t.Unit,
t.Lot_No,
t.Location_Id,
0 Po_In,
0 Po_Out,
t.Quantity Pd_In,
0 r_In,
0 r_Out,
0 l_In,
0 s_Out,
0 Di_In,
0 Di_Out,
0 p_In,
0 p_Out,
0 Onhand,
t.inv_operate_date,
t.dely_org_cd org_cd
From Scm_Wms_Inv_Transactions_Tbl t
Where t.Inv_Operate_Type = 'PD' --铺货提前到货
Union All
Select t.Company_Cd,
t.Project_Cd,
t.Whs_Id,
t.Brand_Id,
t.Item_Cd,
t.Item_Spec_Id,
t.Unit,
t.Lot_No,
t.Location_Id,
0 Po_In,
0 Po_Out,
0 Pd_In,
t.Quantity r_In,
0 r_Out,
0 l_In,
0 s_Out,
0 Di_In,
0 Di_Out,
0 p_In,
0 p_Out,
0 Onhand,
t.inv_operate_date,
t.dely_org_cd org_cd
From Scm_Wms_Inv_Transactions_Tbl t
Where t.Inv_Operate_Type = 'OI' --杂项入库
Union All
Select t.Source_Company_Cd Company_Cd,
t.Source_Project_Cd Project_Cd,
t.Source_Whs_Id Whs_Id,
t.Brand_Id,
t.Item_Cd,
t.Item_Spec_Id,
t.Unit,
t.Lot_No,
t.Source_Location_Id Location_Id,
0 Po_In,
0 Po_Out,
0 Pd_In,
0 r_In,
t.Quantity r_Out,
0 l_In,
0 s_Out,
0 Di_In,
0 Di_Out,
0 p_In,
0 p_Out,
0 Onhand,
t.inv_operate_date,
t.rcpt_org_cd org_cd
From Scm_Wms_Inv_Transactions_Tbl t
Where t.Inv_Operate_Type = 'O' --领用出库
Union All
Select t.Company_Cd,
t.Project_Cd,
t.Whs_Id,
t.Brand_Id,
t.Item_Cd,
t.Item_Spec_Id,
t.Unit,
t.Lot_No,
t.Location_Id,
0 Po_In,
0 Po_Out,
0 Pd_In,
0 r_In,
0 r_Out,
t.Quantity l_In,
0 s_Out,
0 Di_In,
0 Di_Out,
0 p_In,
0 p_Out,
0 Onhand,
t.inv_operate_date,
t.dely_org_cd org_cd
From Scm_Wms_Inv_Transactions_Tbl t
Where t.Inv_Operate_Type = 'I' --余料退库
Union All
Select t.Source_Company_Cd Company_Cd,
t.Source_Project_Cd Project_Cd,
t.Source_Whs_Id Whs_Id,
t.Brand_Id,
t.Item_Cd,
t.Item_Spec_Id,
t.Unit,
t.Lot_No,
t.Source_Location_Id Location_Id,
0 Po_In,
0 Po_Out,
0 Pd_In,
0 r_In,
0 r_Out,
0 l_In,
t.Quantity s_Out,
0 Di_In,
0 Di_Out,
0 p_In,
0 p_Out,
0 Onhand,
t.inv_operate_date,
t.rcpt_org_cd org_cd
From Scm_Wms_Inv_Transactions_Tbl t
Where t.Inv_Operate_Type = 'S' --报废出库
Union All
Select t.Company_Cd,
t.Project_Cd,
t.Whs_Id,
t.Brand_Id,
t.Item_Cd,
t.Item_Spec_Id,
t.Unit,
t.Lot_No,
t.Location_Id,
0 Po_In,
0 Po_Out,
0 Pd_In,
0 r_In,
0 r_Out,
0 l_In,
0 s_Out,
t.Quantity Di_In,
0 Di_Out,
0 p_In,
0 p_Out,
0 Onhand,
t.inv_operate_date,
t.rcpt_org_cd org_cd
From Scm_Wms_Inv_Transactions_Tbl t
Where t.Inv_Operate_Type = 'DI' --调拨入库
Union All
Select t.Source_Company_Cd Company_Cd,
t.Source_Project_Cd Project_Cd,
t.Source_Whs_Id Whs_Id,
t.Brand_Id,
t.Item_Cd,
t.Item_Spec_Id,
t.Unit,
t.Lot_No,
t.Source_Location_Id Location_Id,
0 Po_In,
0 Po_Out,
0 Pd_In,
0 r_In,
0 r_Out,
0 l_In,
0 s_Out,
0 Di_In,
t.Quantity Di_Out,
0 p_In,
0 p_Out,
0 Onhand,
t.inv_operate_date,
t.dely_org_cd org_cd
From Scm_Wms_Inv_Transactions_Tbl t
Where t.Inv_Operate_Type = 'DI' --调拨出库
Union All
Select t.Company_Cd,
t.Project_Cd,
t.Whs_Id,
t.Brand_Id,
t.Item_Cd,
t.Item_Spec_Id,
t.Unit,
t.Lot_No,
t.Location_Id,
0 Po_In,
0 Po_Out,
0 Pd_In,
0 r_In,
0 r_Out,
0 l_In,
0 s_Out,
0 Di_In,
0 Di_Out,
t.Quantity p_In,
0 p_Out,
0 Onhand,
t.inv_operate_date,
t.rcpt_org_cd org_cd
From Scm_Wms_Inv_Transactions_Tbl t
Where t.Inv_Operate_Type = 'PC' --项目转入
Union All
Select t.Source_Company_Cd Company_Cd,
t.Source_Project_Cd Project_Cd,
t.Source_Whs_Id Whs_Id,
t.Brand_Id,
t.Item_Cd,
t.Item_Spec_Id,
t.Unit,
t.Lot_No,
t.Source_Location_Id Location_Id,
0 Po_In,
0 Po_Out,
0 Pd_In,
0 r_In,
0 r_Out,
0 l_In,
0 s_Out,
0 Di_In,
0 Di_Out,
0 p_In,
t.Quantity p_Out,
0 Onhand,
t.inv_operate_date,
t.dely_org_cd org_cd
From Scm_Wms_Inv_Transactions_Tbl t
Where t.Inv_Operate_Type = 'PC' --项目转出
感觉这个SQL是不是能用with as替换掉
但是用with as怎么构造呢,构造一个,构造多个?