public bool SAPLoadData(ClsSAPDataParameter p_para)
{
string dldate = p_para.Sap_AEDAT;
bool Result = true;
m_Conn = ClsUtility.GetConn();
strBuilder.Append(" Begin ");
strDetail.Append(" Begin ");
try
{
string sqlLQUA = @"select SUM(t.GESME) GESME,t.MATNR,t.WERKS,t.LGORT,substr(t.LGPLA,1,4) LGPLA,b.DW_NAME,c.KCDD_NAME
from LQUA t
join WZ_DW b ON b.DW_CODE=t.Werks
join WZ_KCDD c ON c.DWCODE=t.WERKS AND c.KCDD_CODE=t.LGORT
where regexp_like(lgpla,'^[0-9]+[0-9]$') and substr(t.LGTYP,1,1)<>'9' and matnr in (select matnr from ZC10MMDG085B)
GROUP BY t.MATNR,t.WERKS,t.LGORT,substr(t.LGPLA,1,4),b.DW_NAME,c.KCDD_NAME "; //查询lqua表库存，以上架后的(lgpla为数字的，并且matnr 在085b里有的)
//regexp_like(lgpla,'^[0-9]+[0-9]$')过滤不是数字的
DataTable dtLQUA = m_Conn.GetSqlResultToDt(sqlLQUA);
if (dtLQUA != null && dtLQUA.Rows.Count > 0)
{
string sqlWLZ = @" select d.MATNR,d.MATKL,d.MAKTX,e.PMNAME,e.JBJLDW ,e.DLNAME
FROM MARA d
join WZ_WLZ e on e.PMCODE=d.MATKL "; //查询物料组基本计量单位，物料描述
DataTable dtWLZ = m_Conn.GetSqlResultToDt(sqlWLZ);
string sql085B = @"select MATNR,WERKS,LGORT,substr(LGPLA,1,4)LGPLA,GESME,ERDAT,ZDHTZD ,ZITEM
from ZC10MMDG085B A
WHERE GESME>0 and ZDHTZD is not null
order by MATNR,WERKS,LGORT,substr(LGPLA,1,4) ASC,ERDAT DESC"; //查询085B 因为这个表有仓位//倒查是否有积压
DataTable dt085B = m_Conn.GetSqlResultToDt(sql085B);
int a = 0;
foreach (DataRow rowLQUA in dtLQUA.Rows)
{
double GESME = double.Parse(rowLQUA["GESME"].ToString());
//按工厂，库存地点，仓位前四位，物料编码 分组查询085b
DataRow[] arr085B = dt085B.Select("MATNR='" + rowLQUA["MATNR"] + "' and WERKS='" + rowLQUA["WERKS"] + "' and LGORT='" + rowLQUA["LGORT"] + "' and LGPLA='" + rowLQUA["LGPLA"] + "' ");
//按物料查询物料描述，基本计量单位等。
DataRow[] arrWLZ = dtWLZ.Select(" MATNR='" + rowLQUA["MATNR"] + "' ");
if (arr085B.Length > 0)
{
double totalGESME = 0;
foreach (var item in arr085B)
{
totalGESME = totalGESME + double.Parse(item["GESME"].ToString());
if (GESME > totalGESME)
{
if (arrWLZ.Length > 0)
{
strDetail.Append(" INSERT INTO CONVERT_SWKCDETAIL(WERKS,WERKS_NAME,LGORT,MATNR,GESME,ERDATE,DLNAME,PMNAME,DLDATE) VALUES( ");//插入明细表，为后面算账务金额做基础
strDetail.Append(" '" + rowLQUA["WERKS"].ToString() + "', ");
strDetail.Append(" '" + rowLQUA["DW_NAME"]?.ToString() + "', ");
strDetail.Append(" '" + rowLQUA["LGORT"]?.ToString() + "', ");
strDetail.Append(" '" + item["MATNR"]?.ToString() + "', ");
strDetail.Append(" '" + item["GESME"]?.ToString() + "', ");
strDetail.Append(" '" + item["ERDAT"]?.ToString() + "', ");
strDetail.Append(" '" + arrWLZ[0]["DLNAME"]?.ToString() + "', ");
strDetail.Append(" '" + arrWLZ[0]["PMNAME"]?.ToString() + "', ");
strDetail.Append(" '" + dldate + "'); ");
}
continue;//如果当前累计数量和小于库存，继续循环
}
else
{
//插入明细表，为后面算账务金额做基础
if (arrWLZ.Length > 0)
{
strDetail.Append(" INSERT INTO CONVERT_SWKCDETAIL(WERKS,WERKS_NAME,LGORT,MATNR,GESME,ERDATE,DLNAME,PMNAME,DLDATE) VALUES( ");//插入明细表，为后面算账务金额做基础
strDetail.Append(" '" + rowLQUA["WERKS"].ToString() + "', ");
strDetail.Append(" '" + rowLQUA["DW_NAME"]?.ToString() + "', ");
strDetail.Append(" '" + rowLQUA["LGORT"]?.ToString() + "', ");
strDetail.Append(" '" + item["MATNR"]?.ToString() + "', ");
strDetail.Append(" '" + item["GESME"]?.ToString() + "', ");
strDetail.Append(" '" + item["ERDAT"]?.ToString() + "', ");
strDetail.Append(" '" + arrWLZ[0]["DLNAME"]?.ToString() + "', ");
strDetail.Append(" '" + arrWLZ[0]["PMNAME"]?.ToString() + "', ");
strDetail.Append(" '" + dldate + "'); ");
}
//根据mard库存按工厂、物料码、库存地点，分组，回找入库通知单，直到入库通知单数量和大于或等于mard数量
//将近期入库通知单收货数量小于库存的都插入实物库存表CONVERT_SWKC
strBuilder.Append(" INSERT INTO CONVERT_SWKC (WERKS,ZDHTZD,ZITEM,MATKL,MATNR,MAKTX," +
"MEINS,GESME,LGORT,LGPLA,ERDAT,WERKS_NAME,LGORT_NAME,ZSTATUS,YXQ,DLDATE,KCTYPE) VALUES(");
strBuilder.Append(" '" + rowLQUA["WERKS"].ToString() + "',");
strBuilder.Append(" '" + item["ZDHTZD"] + "',");
strBuilder.Append(" '" + item["ZITEM"] + "',");
if (arrWLZ.Length > 0)
{
strBuilder.Append(" '" + arrWLZ[0]["MATKL"]?.ToString().Trim());
strBuilder.Append("',");
}
else
{
strBuilder.Append(" '',");
}
strBuilder.Append(" '" + item["MATNR"] + "',");
if (arrWLZ.Length > 0)
{
strBuilder.Append(" '" + arrWLZ[0]["MAKTX"]?.ToString().Trim());
strBuilder.Append("',");
strBuilder.Append(" '" + arrWLZ[0]["JBJLDW"]?.ToString().Trim().Replace('\'', ' ').Replace('(', ' ').Replace(')', ' ').Replace('）', ' ').Replace('（', ' '));
strBuilder.Append("',");
}
else
{
strBuilder.Append(" '','',");
}
strBuilder.Append(" '" + GESME.ToString() + "',");
strBuilder.Append(" '" + rowLQUA["LGORT"] + "',");
strBuilder.Append(" '" + rowLQUA["LGPLA"] + "',");//仓位
strBuilder.Append(" '" + item["ERDAT"]?.ToString() + "',");
strBuilder.Append(" '" + rowLQUA["DW_NAME"] + "',");
strBuilder.AppendLine(" '" + rowLQUA["KCDD_NAME"].ToString().Replace('(', ' ').Replace(')', ' ').Replace('）', ' ').Replace('（', ' ').Replace('\'', ' ') +
"','04','','" + dldate + "',0); ");
}
}
}
}
if (strBuilder.Length > 8)
{
strBuilder.Append(" COMMIT; end ; ");
strDetail.Append(" COMMIT; end ; ");
if (m_Conn.ExecuteSql(" begin delete from CONVERT_SWKC where KCTYPE=0;DELETE FROM CONVERT_SWKCDETAIL; commit; end; "))
{
Result = m_Conn.ExecuteSql(strDetail.ToString());//执行失败竟然也返回true
if (Result)
{
ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKCDETAIL", "ALL", p_para.Sap_AEDAT, "插入CONVERT_SWKCDETAIL失败,应该是sql有问题，可能含特殊字符了，");
}
else
{
ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKCDETAIL", "ALL", p_para.Sap_AEDAT, "插入CONVERT_SWKCDETAIL失败,应该是sql有问题，可能含特殊字符了，");
}
Result = ClsUtility.ExecuteSqlToDb(strBuilder.ToString());//执行失败竟然也返回true
if (Result)
{
DataTable dt = m_Conn.GetSqlResultToDt("select 1 from CONVERT_SWKC ");//要重新判断是否插入成功
if (dt == null || dt.Rows.Count == 0)
{
ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKC", "ALL", p_para.Sap_AEDAT, "插入CONVERT_SWKC表上架部分失败,应该是sql有问题，可能含特殊字符了，");
Result = false;
}
else
{
ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKC", "ALL", p_para.Sap_AEDAT, "插入CONVERT_SWKC表上架部分成功");
strBuilder.Length = 0;
strBuilder.Append(@" begin INSERT INTO CONVERT_SWKC (WERKS,ZDHTZD,ZITEM,MATKL,MATNR,MAKTX,
MEINS,GESME,LGORT,LGPLA,ERDAT,WERKS_NAME,LGORT_NAME,ZSTATUS,YXQ,DLDATE,KCTYPE)
select t.WERKS,t.ZDHTZD,t.ZITEM,d.MATKL, t.MATNR,d.MAKTX,
e.JBJLDW,k.VMENGE01,t.LGORT,'' LGPLA ,t.ZCJRQ, b.DW_NAME,c.KCDD_NAME,'03','','" + dldate + "',0 ");
strBuilder.Append(@" from ZC10MMDG072 t
join ZC10MMDG076 k on t.ZDHTZD =k.ZDHTZD and t.ZITEM=k.ZITEM
join WZ_DW b ON b.DW_CODE=t.Werks
join WZ_KCDD c ON c.DWCODE=t.WERKS AND c.KCDD_CODE=t.LGORT
join MARA d on d.matnr=t.matnr
join WZ_WLZ e on e.PMCODE=d.MATKL
where ZSTATUS ='03'; commit; end; ");//插入质检状态的入库单作为另一部分库存
Result = m_Conn.ExecuteSql(strBuilder.ToString());
if (Result)
{
ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKC", "ALL", p_para.Sap_AEDAT, "插入CONVERT_SWKC表质检部分成功");
}
else
{
ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKC", "ALL", p_para.Sap_AEDAT, "插入CONVERT_SWKC表质检部分失败");
}
}
string sqlupdateje = @"UPDATE CONVERT_SWKCDETAIL A SET A.SCJE=(select ROUND(B.SALK3/LBKUM*A.GESME,2)
FROM MBEW B
WHERE B.SALK3>0 AND A.MATNR=B.MATNR AND A.WERKS=B.BWKEY)";
if (!m_Conn.ExecuteSql(sqlupdateje))
{
ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKCDETAIL", "ALL", p_para.Sap_AEDAT, "更新CONVERT_SWKCDETAIL表金额失败");
}
}
else
{
ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKC", "ALL", p_para.Sap_AEDAT, "插入CONVERT_SWKC上架部分表失败");
}
}
}
}
ClsDataLoadBAOBIAO.SAPLoadData(p_para);//下架未过账库存金额模型
}
catch (Exception ex)
{
strBuilder.Length = 0;
ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKC", "ALL", p_para.Sap_AEDAT, "插入CONVERT_SWKC表发生异常:" + ex.Message);
return(false);
}
return(Result);
}
public bool SAPLoadData(ClsSAPDataParameter p_para)
{
string dldate = p_para.Sap_AEDAT;
bool Result = true;
m_Conn = ClsUtility.GetConn();
try
{
string week = DateTime.Today.DayOfWeek.ToString();
if (week == "Thursday")
{
string sql = " begin insert into CONVERT_SWKC_RECORD SELECT * FROM CONVERT_SWKC ;COMMIT; END; ";
m_Conn.ExecuteSql(sql);
}
//string sqlLQUA = @"begin delete from CONVERT_SWKC where KCTYPE=0;
// INSERT INTO CONVERT_SWKC (WERKS,MATKL,MATNR,MAKTX,
// MEINS,GESME,LGORT,LGPLA,ERDAT,WERKS_NAME,LGORT_NAME,ZSTATUS,YXQ,DLDATE,KCTYPE)
// SELECT A.WERKS,C.MATKL,A.MATNR,C.MAKTX,
// F.JBJLDW,A.GESME,A.LGORT,substr(A.LGPLA,0,4),case when SUBSTR(A.WDATU, 0, 8)='00000000' then A.BDATU ELSE A.WDATU END,D.DW_NAME,E.KCDD_NAME,'04','',to_char(sysdate,'yyyymmdd'),0
// FROM LQUA A JOIN(
// select WERKS,MATNR,substr(lgpla,0,2) DKCODE,LGORT,max(BDATU) BDATU,max(LQNUM) LQNUM
// from LQUA where regexp_like(lgpla,'^[0-9]+[0-9]$')
// group by werks,matnr,LGORT,substr(lgpla,0,2)) B ON A.WERKS=B.WERKS AND A.MATNR=B.MATNR AND substr(A.lgpla,0,2)=B.DKCODE AND A.LGORT=B.LGORT AND A.BDATU=B.BDATU AND A.LQNUM=B.LQNUM
// JOIN MARA C ON A.MATNR=C.MATNR
// join WZ_DW D ON D.DW_CODE=A.Werks
// LEFT join WZ_KCDD E ON E.DWCODE=A.WERKS AND E.KCDD_CODE=A.LGORT
// JOIN WZ_WLZ F ON F.PMCODE=C.MATKL ; COMMIT; end;"; //查询lqua表库存，lapla 为数字得，子查询是按物料编码取最新一条数据，主要取里面得BDATU,WDATU
//regexp_like(lgpla,'^[0-9]+[0-9]$')过滤lapla不是数字的
string sqlLQUA = @"
SELECT A.WERKS,C.MATKL,A.MATNR,trim(C.MAKTX)MAKTX,
F.JBJLDW MEINS,nvl(A.GESME,0)GESME,A.LGORT,substr(A.LGPLA,0,4)LGPLA2,A.LGPLA ,case when SUBSTR(A.WDATU, 0, 8)='00000000' then A.BDATU ELSE A.WDATU END ERDAT,
D.DW_NAME WERKS_NAME,E.KCDD_NAME,'04' ZSTATUS,''YXQ,to_char(sysdate,'yyyymmdd')DLDATE,0 KCTYPE
FROM LQUA A
JOIN MARA C ON A.MATNR=C.MATNR
join WZ_DW D ON D.DW_CODE=A.Werks
LEFT join WZ_KCDD E ON E.DWCODE=A.WERKS AND E.KCDD_CODE=A.LGORT
JOIN WZ_WLZ F ON F.PMCODE=C.MATKL where regexp_like(lgpla,'^0\d{9}$') AND substr(A.LGTYP,0,1)<>'9' "; //查询lqua表库存，lapla 为数字得，子查询是按物料编码取最新一条数据，主要取里面得BDATU,WDATU
//regexp_like(lgpla,'^[0-9]+[0-9]$')过滤lapla不是数字的
DataTable dt = m_Conn.GetSqlResultToDt(sqlLQUA);
if (dt != null && dt.Rows.Count > 0)
{
string sqlruku = @"select nvl(C.MENGE,0)MENGE,C.BUDAT_MKPF,A.MATNR,A.ZDHTZD, A.ZITEM,B.LGPLA,A.WERKS,A.LGORT
from ZC10MMDG072 A
JOIN ZC10MMDG085B B ON A.ZDHTZD=B.ZDHTZD AND A.ZITEM=B.ZITEM and regexp_like(B.lgpla,'^0\d{9}$')
JOIN MSEG C ON C.MBLNR=A.MBLNR AND C.ZEILE=A.ZEILE and C.BWART IN('105','101')
WHERE exists (SELECT 1 FROM LQUA d WHERE d.MATNR=A.MATNR and regexp_like(d.lgpla,'^0\d{9}$') )
order by C.BUDAT_MKPF desc,a.matnr,a.zdhtzd,a.zitem ";
DataTable dtruku = m_Conn.GetSqlResultToDt(sqlruku);
StringBuilder sb = new StringBuilder();
string sqlinsert = "INSERT INTO CONVERT_SWKC (WERKS,MATKL,MATNR,MAKTX,MEINS,GESME,LGORT,LGPLA,ERDAT,WERKS_NAME,LGORT_NAME,ZSTATUS,YXQ,DLDATE,KCTYPE)values ('";
foreach (DataRow row in dt.Rows)
{
DataRow[] rowsRK = dtruku.Select("MATNR='" + row["MATNR"] + "' and WERKS='" + row["WERKS"] + "' and LGORT='" + row["LGORT"] + "' and LGPLA='" + row["LGPLA"] + "' ");
if (rowsRK != null && rowsRK.Length > 0)
{
double sumSL = 0;
int flag = 0;
foreach (DataRow rowRK in rowsRK)
{
sumSL = sumSL + double.Parse(rowRK["MENGE"].ToString());
if (sumSL >= double.Parse(row["GESME"].ToString()))
{
sb.AppendLine("");
sb.Append(sqlinsert);
sb.Append(row["WERKS"].ToString() + "','");
sb.Append(row["MATKL"].ToString() + "','");
sb.Append(row["MATNR"].ToString() + "','");
sb.Append(row["MAKTX"].ToString().Replace("/*", "*").Replace("&", " ").Replace("'", "").Trim() + "','");
sb.Append(row["MEINS"].ToString() + "','");
sb.Append(row["GESME"].ToString() + "','");
sb.Append(row["LGORT"].ToString() + "','");
sb.Append(row["LGPLA2"].ToString() + "','");
sb.Append(rowRK["BUDAT_MKPF"].ToString() + "','");
sb.Append(row["WERKS_NAME"].ToString() + "','");
sb.Append(row["KCDD_NAME"].ToString() + "','");
sb.Append(row["ZSTATUS"].ToString() + "','");
sb.Append(row["YXQ"].ToString() + "','");
sb.Append(row["DLDATE"].ToString() + "',");
sb.Append(row["KCTYPE"].ToString() + ");");
flag = 1;
break;
}
}
if (flag == 0)
{
sb.AppendLine("");
sb.Append(sqlinsert);
sb.Append(row["WERKS"].ToString() + "','");
sb.Append(row["MATKL"].ToString() + "','");
sb.Append(row["MATNR"].ToString() + "','");
sb.Append(row["MAKTX"].ToString().Replace("/*", "*").Replace("&", " ").Replace("'", "").Trim() + "','");
sb.Append(row["MEINS"].ToString() + "','");
sb.Append(row["GESME"].ToString() + "','");
sb.Append(row["LGORT"].ToString() + "','");
sb.Append(row["LGPLA2"].ToString() + "','");
sb.Append(row["ERDAT"].ToString() + "','");
sb.Append(row["WERKS_NAME"].ToString() + "','");
sb.Append(row["KCDD_NAME"].ToString() + "','");
sb.Append(row["ZSTATUS"].ToString() + "','");
sb.Append(row["YXQ"].ToString() + "','");
sb.Append(row["DLDATE"].ToString() + "',");
sb.Append(row["KCTYPE"].ToString() + ");");
}
}
else
{
sb.AppendLine("");
sb.Append(sqlinsert);
sb.Append(row["WERKS"].ToString() + "','");
sb.Append(row["MATKL"].ToString() + "','");
sb.Append(row["MATNR"].ToString() + "','");
sb.Append(row["MAKTX"].ToString().Replace("/*", "*").Replace("&", " ").Replace("'", "").Trim() + "','");
sb.Append(row["MEINS"].ToString() + "','");
sb.Append(row["GESME"].ToString() + "','");
sb.Append(row["LGORT"].ToString() + "','");
sb.Append(row["LGPLA2"].ToString() + "','");
sb.Append(row["ERDAT"].ToString() + "','");
sb.Append(row["WERKS_NAME"].ToString() + "','");
sb.Append(row["KCDD_NAME"].ToString() + "','");
sb.Append(row["ZSTATUS"].ToString() + "','");
sb.Append(row["YXQ"].ToString() + "','");
sb.Append(row["DLDATE"].ToString() + "',");
sb.Append(row["KCTYPE"].ToString() + ");");
}
}
string insertSWKC = @"begin delete from CONVERT_SWKC where KCTYPE=0;";
insertSWKC += " COMMIT; end;";
Result = m_Conn.ExecuteSql(insertSWKC);
insertSWKC = " begin ";
insertSWKC += sb.ToString();
insertSWKC += " COMMIT; end;";
Result = m_Conn.ExecuteSql(insertSWKC);
if (Result)
{
ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKC", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "插入CONVERT_SWKC表上架部分成功");
}
else
{
ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKC", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "插入CONVERT_SWKC表上架部分失");
}
}
else
{
ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKC", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "插入CONVERT_SWKC表上架部分失败--没数据");
}
string sqlSWKCDetail = @" begin DELETE FROM CONVERT_SWKCDETAIL;
INSERT INTO CONVERT_SWKCDETAIL(WERKS,WERKS_NAME,LGORT,MATNR,GESME,ERDATE,DLNAME,PMNAME,DLDATE)
SELECT A.WERKS,D.DW_NAME,A.LGORT,A.MATNR,A.GESME
,case when SUBSTR(A.WDATU, 0, 8)='00000000' then A.BDATU ELSE A.WDATU END,F.DLNAME,F.PMNAME,to_char(sysdate,'yyyymmdd')
FROM LQUA A JOIN(
select WERKS,MATNR,substr(lgpla,0,2) DKCODE,LGORT,max(BDATU) BDATU,max(LQNUM) LQNUM
from LQUA where regexp_like(lgpla,'^[0-9]+[0-9]$') --AND matnr='000000011000683835'
group by werks,matnr,LGORT,substr(lgpla,0,2)) B ON A.WERKS=B.WERKS AND A.MATNR=B.MATNR AND substr(A.lgpla,0,2)=B.DKCODE AND A.LGORT=B.LGORT AND A.BDATU=B.BDATU AND A.LQNUM=B.LQNUM
JOIN MARA C ON A.MATNR=C.MATNR
join WZ_DW D ON D.DW_CODE=A.Werks
JOIN WZ_WLZ F ON F.PMCODE=C.MATKL ;COMMIT;end;";
Result = m_Conn.ExecuteSql(sqlSWKCDetail);
if (Result)
{
ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKCDETAIL", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "插入CONVERT_SWKCDETAIL表上架部分成功");
}
else
{
ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKCDETAIL", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "插入CONVERT_SWKCDETAIL表上架部分失败");
}
strBuilder.Length = 0;
strBuilder.Append(@" begin INSERT INTO CONVERT_SWKC (WERKS,ZDHTZD,ZITEM,MATKL,MATNR,MAKTX,
MEINS,GESME,LGORT,LGPLA,ERDAT,WERKS_NAME,LGORT_NAME,ZSTATUS,YXQ,DLDATE,KCTYPE)
select t.WERKS,t.ZDHTZD,t.ZITEM,d.MATKL, t.MATNR,trim(d.MAKTX)MAKTX,
e.JBJLDW,k.VMENGE01,t.LGORT,'' LGPLA ,t.ZCJRQ, b.DW_NAME,c.KCDD_NAME,'03','','" + dldate + "',0 ");
strBuilder.Append(@" from ZC10MMDG072 t
join ZC10MMDG076 k on t.ZDHTZD =k.ZDHTZD and t.ZITEM=k.ZITEM
join WZ_DW b ON b.DW_CODE=t.Werks
join WZ_KCDD c ON c.DWCODE=t.WERKS AND c.KCDD_CODE=t.LGORT
join MARA d on d.matnr=t.matnr
join WZ_WLZ e on e.PMCODE=d.MATKL
where ZSTATUS ='03' and k.ZJYRQ between TO_CHAR(trunc(sysdate)-14,'yyyymmdd') and TO_CHAR(sysdate,'yyyymmdd')
and k.ZJYRQ<>'00000000'
; commit; end; ");//插入质检状态的入库单作为另一部分库存
Result = m_Conn.ExecuteSql(strBuilder.ToString());
if (Result)
{
ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKC", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "插入CONVERT_SWKC表质检部分成功");
}
else
{
ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKC", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "插入CONVERT_SWKC表质检部分失败");
}
string sqlupdateje = @"UPDATE CONVERT_SWKCDETAIL A SET A.SCJE=(select ROUND(B.SALK3/LBKUM*A.GESME,2)
FROM MBEW B
WHERE B.SALK3>0 AND A.MATNR=B.MATNR AND A.WERKS=B.BWKEY)";
if (!m_Conn.ExecuteSql(sqlupdateje))
{
ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKCDETAIL", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "更新CONVERT_SWKCDETAIL表金额失败");
}
ClsDataLoadBAOBIAO.SAPLoadData(p_para);//下架未过账库存金额模型
}
catch (Exception ex)
{
strBuilder.Length = 0;
ClsErrorLogInfo.WriteSapLog("1", "CONVERT_SWKC", "ALL", DateTime.Now.ToString("yyyy-MM-dd"), "插入CONVERT_SWKC表发生异常:" + ex.Message);
return(false);
}
return(Result);
}
