Public Function UpdateSAPConfirmedQuants(xTable)
Static M As Recordset, UpdateSAPConfirmedQuants_SQL
Static Ret As Variant, z As Long, TotalRecs, xText
Static curKG, curST, curWCtxt, preKG, preST, preWCtxt
Static x, i, itxt, isPrev, LastPostingDate, pi, xPrevWCstr As vPrevWCstring
xText = "Update SAP confirmations to " & xTable
[COLOR=red]UpdateSAPConfirmedQuants_SQL = "SELECT " & xTable & ".ID, " & xTable & ".Component_Flag, " & xTable & ".MRP_Controller, " & xTable & ".Operation_No, " & xTable & ".Work_Center, " & xTable & ".Work_CenterConfirm, " & xTable & ".Work_CenterPrevConfirm, " & xTable & ".SAPWCConfirmText, " & xTable & ".SAPConfirmQuant_kg, " & xTable & ".SAPConfirmQuant_Pieces, " & xTable & ".LastSAP_PostingDate AS LastSAP_PostDate, " & xTable & ".WCConfirmText, " & xTable & ".ConfirmQuant_kg, " & xTable & ".ConfirmQuant_Pieces, " & xTable & ".SAPPrevWCConfirmText, " & xTable & ".SAPPrevConfirmQuant_kg, " & xTable & ".SAPPrevConfirmQuant_Pieces, " & xTable & ".PrevWCConfirmText, " & xTable & ".PrevConfirmQuant_kg, " & xTable & ".PrevConfirmQuant_Pieces, " & xTable & ".PrevCompleted, ProductionMatrixSAP.* FROM " & xTable & " INNER JOIN ProductionMatrixSAP ON " & xTable & ".SD_key = ProductionMatrixSAP.SD_key ORDER BY " & xTable & ".ID;"[/COLOR]
x = SaveSQL(xText, "UpdateSAPConfirmedQuants", "UpdateSAPConfirmedQuants_SQL", UpdateSAPConfirmedQuants_SQL, GetAddSQL())
Set M = CurrentDb.OpenRecordset(UpdateSAPConfirmedQuants_SQL, dbOpenDynaset)
Debug.Print UpdateSAPConfirmedQuants_SQL
If M.RecordCount > 0 Then
M.MoveLast
TotalRecs = M.RecordCount
Ret = SysCmd(SYSCMD_INITMETER, xText, TotalRecs)
z = 1
M.MoveFirst
Do Until M.EOF
Ret = SysCmd(SYSCMD_UPDATEMETER, z)
curKG = 0
curST = 0
curWCtxt = Null
preKG = 0
preST = 0
preWCtxt = Null
LastPostingDate = CDate("01/01/1999")
xPrevWCstr = GetPrevWorkCenter_Var(M("Work_Center"), M("MRP_Controller"))
For i = M("maxOP") To 1 Step -1
itxt = Format(i, "00")
'check to find proper prv_string
If xPrevWCstr.IsComplex And (IsNull(xPrevWCstr.SinglePrevWCstr) Or IsEmpty(xPrevWCstr.SinglePrevWCstr)) Then
pi = 1
Do While (IsNull(xPrevWCstr.SinglePrevWCstr) Or IsEmpty(xPrevWCstr.SinglePrevWCstr)) And _
pi <= xPrevWCstr.ComplexPrevWCstrNo
If InStr(1, xPrevWCstr.ComplexPrevWCstr(pi), M("PG" + itxt)) > 0 And _
M("OP" + itxt) < M("Operation_No") Then
xPrevWCstr.SinglePrevWCstr = xPrevWCstr.ComplexPrevWCstr(pi)
End If
pi = pi + 1
Loop
End If
Next i
For i = 1 To M("maxOP")
itxt = Format(i, "00")
If M("OP" + itxt) < M("Operation_No") Then
isPrev = IIf(InStr(1, xPrevWCstr.SinglePrevWCstr, M("PG" + itxt)) > 0, True, False)
If isPrev Then
preKG = preKG + M("KG" + itxt)
preST = preST + M("ST" + itxt)
preWCtxt = AddToList(preWCtxt, Mid(M("WC" + itxt), IIf(Left(M("WC" + itxt), 2) = "S_", 3, 1), 8))
End If
End If
If M("OP" + itxt) = M("Operation_No") Then
If M("WG" + itxt) = M("Work_CenterConfirm") Then
curKG = curKG + M("KG" + itxt)
curST = curST + M("ST" + itxt)
LastPostingDate = IIf(LastPostingDate < M("LD" + itxt), M("LD" + itxt), LastPostingDate)
curWCtxt = AddToList(curWCtxt, Mid(M("WC" + itxt), IIf(Left(M("WC" + itxt), 2) = "S_", 3, 1), 8))
End If
End If
Next i
M.Edit
M("LastSAP_PostDate") = LastPostingDate
'Current Machine Confirmed Quant in SAP
'======================================
M("SAPWCConfirmText") = curWCtxt 'string with all WC produced the quantity in SAP
M("SAPConfirmQuant_kg") = curKG
M("SAPConfirmQuant_Pieces") = curST
'Current Machine Confirmed Quant in SAP + Deltia (Dif)
'=====================================================
M("WCConfirmText") = curWCtxt 'string with all WC produced the quantity in SAP + Deltia
M("ConfirmQuant_kg") = curKG
M("ConfirmQuant_Pieces") = curST
'Previous Machine Confirmed Quant in SAP
'=======================================
M("SAPPrevWCConfirmText") = preWCtxt 'string with all previous WC produced the quantity in SAP
M("SAPPrevConfirmQuant_kg") = preKG
M("SAPPrevConfirmQuant_Pieces") = preST
'Previous Machine Confirmed Quant in SAP + Deltia (Dif)
'======================================================
M("PrevWCConfirmText") = preWCtxt 'string with all previous WC produced the quantity in SAP + Deltia
M("PrevConfirmQuant_kg") = preKG
M("PrevConfirmQuant_Pieces") = preST
M.Update
M.MoveNext
z = z + 1
Loop
Ret = SysCmd(SYSCMD_REMOVEMETER)
End If
M.Close
End Function