7,732
社区成员




Function AllocateCommMaterial(Threshold As Double)
Dim rstQry As Recordset
Dim Part As String
Dim WO As String
Dim WOShortage As Long
Dim ETA As Long
Dim i As Integer
Dim Wi As Long
PartFlag = 0
WOFlag = 0
Set rstQry = CurrentDb.OpenRecordset("0403_Comm_WO_Parts_Status")
rstQry.MoveLast
rstQry.MoveFirst
WOPre = "0000"
DoCmd.SetWarnings (False)
While Not rstQry.EOF
WO = rstQry![WO No]
WOShortage = rstQry![WO Demand Qty]
Part = rstQry![Part No]
'pg.Run Count, RowCount, "正在运行:" & Part
PartOHNet = IIf(IsNull(rstQry![OH Net]), 0, rstQry![OH Net])
PartOHAllocated = IIf(IsNull(rstQry![OH Allocated Qty]), 0, rstQry![OH Allocated Qty])
PartReceNet = IIf(IsNull(rstQry![Receiving Net]), 0, rstQry![Receiving Net])
PartReceAllocated = IIf(IsNull(rstQry![Receiving Allocated Qty]), 0, rstQry![Receiving Allocated Qty])
PartTransitNet = IIf(IsNull(rstQry![Transit Net]), 0, rstQry![Transit Net])
PartTransitAllocated = IIf(IsNull(rstQry![Transit Allocated Qty]), 0, rstQry![Transit Allocated Qty])
PartWONet = IIf(IsNull(rstQry![WO Net]), 0, rstQry![WO Net])
PartWOAllocated = IIf(IsNull(rstQry![WO Allocated Qty]), 0, rstQry![WO Allocated Qty])
PartOSPNet = IIf(IsNull(rstQry![OSP Net]), 0, rstQry![OSP Net])
PartOSPAllocated = IIf(IsNull(rstQry![OSP Allocated Qty]), 0, rstQry![OSP Allocated Qty])
If WOShortage <= PartOHNet Then 'No shortage
WOSQL2 = "UPDATE [41_1_Comm_WO_Analysis_Temp] SET [Result] = 'No Shortage', [OH Allocate] = " & WOShortage & _
" WHERE [WO No] = '" & WO & "' AND [Part No] = '" & Part & "'"
PartSQL = "UPDATE [28_1_Avaiable_For_Comm_WO] SET [OH Allocated Qty] = " & PartOHAllocated + WOShortage & _
",[OH Net] = " & PartOHNet - WOShortage & " WHERE [Part No] = '" & Part & "'"
ElseIf WOShortage <= PartOHNet + PartWONet Then 'No shortage - WO
WOSQL2 = "UPDATE [41_1_Comm_WO_Analysis_Temp] SET [Result] = 'No shortage - WO', [OH Allocate] = " & PartOHNet & _
",[WO Allocate]= " & WOShortage - PartOHNet & " WHERE [WO No] = '" & WO & "' AND [Part No] = '" & Part & "'"
PartSQL = "UPDATE [28_1_Avaiable_For_Comm_WO] SET [OH Net] = 0" & _
",[WO Net] = " & PartWONet - WOShortage + PartOHNet & _
",[WO Allocated Qty] = " & WOShortage - PartOHNet + PartWOAllocated & _
",[OH Allocated Qty]= " & PartOHAllocated + PartOHNet & " WHERE [Part No] = '" & Part & "'"
ElseIf WOShortage <= PartOHNet + PartWONet + PartOSPNet Then 'OSP'
WOSQL2 = "UPDATE [41_1_Comm_WO_Analysis_Temp] SET [Result] = 'OSP', [OH Allocate] = " & PartOHNet & _
",[WO Allocate] = " & PartWONet & _
",[OSP Allocate]= " & WOShortage - PartOHNet - PartWONet & " WHERE [WO No] = '" & WO & "' AND [Part No] = '" & Part & "'"
PartSQL = "UPDATE [28_1_Avaiable_For_Comm_WO] SET [OH Net] = 0" & _
",[WO Net] = 0" & _
",[OSP Net] = " & PartOSPNet - WOShortage + PartOHNet + PartWONet & _
",[OSP Allocated Qty] = " & WOShortage - PartWONet - PartOHNet + PartOSPAllocated & _
",[WO Allocated Qty]= " & PartWOAllocated + PartWONet & _
",[OH Allocated Qty]= " & PartOHAllocated + PartOHNet & " WHERE [Part No] = '" & Part & "'"
ElseIf WOShortage <= PartOHNet + PartWONet + PartReceNet Then 'Receiving
WOSQL2 = "UPDATE [41_1_Comm_WO_Analysis_Temp] SET [Result] = 'Receiving', [OH Allocate] = " & PartOHNet & _
",[WO Allocate] = " & PartWONet & _
",[OSP Allocate] = " & PartOSPNet & _
",[Receiving Allocate]= " & WOShortage - PartOHNet - PartWONet - PartOSPNet & " WHERE [WO No] = '" & WO & "' AND [Part No] = '" & Part & "'"
PartSQL = "UPDATE [28_1_Avaiable_For_Comm_WO] SET [OH Net] = 0" & _
",[WO Net] = 0" & _
",[OSP Net] = 0" & _
",[Receiving Net] = " & PartReceNet - WOShortage + PartOHNet + PartWONet + PartOSPNet & _
",[Receiving Allocated Qty] = " & WOShortage - PartOHNet - PartWONet - PartOSPNet + PartReceAllocated & _
",[OSP Allocated Qty]= " & PartWOAllocated + PartOSPNet & _
",[WO Allocated Qty]= " & PartWOAllocated + PartWONet & _
",[OH Allocated Qty]= " & PartOHAllocated + PartOHNet & " WHERE [Part No] = '" & Part & "'"
ElseIf WOShortage <= PartOHNet + PartWONet + PartReceNet + PartTransitNet Then 'Transit
WOSQL2 = "UPDATE [41_1_Comm_WO_Analysis_Temp] SET [Result] = 'Transit', [OH Allocate] = " & PartOHNet & _
",[WO Allocate] = " & PartWONet & _
",[OSP Allocate] = " & PartOSPNet & _
",[Receiving Allocate] = " & PartReceNet & _
",[Transit Allocate]= " & WOShortage - PartOHNet - PartWONet - PartOSPNet - PartReceNet & " WHERE [WO No] = '" & WO & "' AND [Part No] = '" & Part & "'"
PartSQL = "UPDATE [28_1_Avaiable_For_Comm_WO] SET [OH Net] = 0" & _
",[WO Net] = 0" & _
",[OSP Net] = 0" & _
",[Receiving Net] = 0" & _
",[Transit Net] = " & PartTransitNet - WOShortage + PartReceNet + PartOSPNet + PartWONet + PartOHNet & _
",[Transit Allocated Qty] = " & WOShortage - PartReceNet - PartOHNet - PartWONet - PartOSPNet + PartTransitAllocated & _
",[Receiving Allocated Qty]= " & PartReceAllocated + PartReceNet & _
",[OSP Allocated Qty]= " & PartOSPAllocated + PartOSPNet & _
",[WO Allocated Qty]= " & PartWOAllocated + PartWONet & _
",[OH Allocated Qty]= " & PartOHAllocated + PartOHNet & " WHERE [Part No] = '" & Part & "'"
Else 'if Parts are Shortage = WOShortage > PartOHNet + PartReceNet , then update W1, W2, W3......
WOSQL2 = "UPDATE [41_1_Comm_WO_Analysis_Temp] SET [OH Allocate] = " & PartOHNet & ", [WO Allocate] = " & PartWONet & ", [Receiving Allocate]= " & PartReceNet & ", [Transit Allocate]= " & PartTransitNet & ", [OSP Allocate]= " & PartOSPNet
PartSQL = "UPDATE [28_1_Avaiable_For_Comm_WO] SET [OH Net] = 0, [WO Net] = 0, [Receiving Net] = 0, [Transit Net] = 0, [OSP Net] = 0, [OSP Allocated Qty]= " & PartOSPAllocated + PartOSPNet & ", [Transit Allocated Qty]= " & PartTransitAllocated + PartTransitNet & ", [Receiving Allocated Qty]= " & PartReceAllocated + PartReceNet & ", [WO Allocated Qty] = " & PartWONet + PartWOAllocated & ", [OH Allocated Qty] = " & PartOHNet + PartOHAllocated
ETA = PartOHNet + PartWONet + PartReceNet + PartTransitNet + PartOSPNet
i = 1
While i <= 54
Wi = IIf(IsNull(rstQry.Fields("W" & i & " Net")), 0, rstQry.Fields("W" & i & " Net"))
If Wi > 0 Then
ETA = ETA + Wi
If ETA < WOShortage Then 'ETA not enough for WOShortage
WOSQL2 = WOSQL2 & ",[W" & i & " Allocate] = " & Wi
PartSQL = PartSQL & ",[W" & i & " Net] = 0"
Else 'ETA is enough for WOShortage, end the loop
If i < 10 Then
WOSQL2 = WOSQL2 & ",[W" & i & " Allocate] = " & Wi + WOShortage - ETA & ",[Result] = 'W0" & i & "'" & _
" WHERE [WO No] = '" & WO & "' AND [Part No] = '" & Part & "'"
Else
WOSQL2 = WOSQL2 & ",[W" & i & " Allocate] = " & Wi + WOShortage - ETA & ",[Result] = 'W" & i & "'" & _
" WHERE [WO No] = '" & WO & "' AND [Part No] = '" & Part & "'"
End If
PartSQL = PartSQL & ",[W" & i & " Net] = " & ETA - WOShortage & _
" WHERE [Part No] = '" & Part & "'"
GoTo LabelA
End If
End If
i = i + 1
Wend
LabelA:
If i = 55 Then
WOSQL2 = WOSQL2 & ",[Result] = 'W55...'" & _
" WHERE [WO No] = '" & WO & "' AND [Part No] = '" & Part & "'"
PartSQL = PartSQL & " WHERE [Part No] = '" & Part & "'"
End If
End If
DoCmd.RunSQL WOSQL2
DoCmd.RunSQL PartSQL
WOPre = WO
rstQry.MoveNext
Wend 'Allocate material
End Function