On Error GoTo ERR_Allocate
Dim rs_ALLOC, rs_FCST_BATCH As DAO.Recordset
Dim str_ALLOC, str_FCST_BATCH, str_ADD_ALLOC As String
Dim OF, WF, CS, AC, RF, RS As Double
Dim dbl_ALLOC, dbl_rStock, dbl_rFCST
Dim a, i, TMP_STK_ID, fb As Integer
Dim str_TMP_STOCK, str_ADD_UN As String
Dim str_Update_STOCK, str_REM_REQ, str_Update_Alloc, str_CLOSE As String
lstSplash.AddItem ("MODULE: ALLOCATE")
bln_FULFILL = False
bln_WEND = False
'get records to allocate
str_FCST_BATCH = "SELECT TMP_FCST_BATCH.FCST_ID, TMP_FCST_BATCH.ID, TMP_FCST_BATCH.ABC, " & _
"TMP_FCST_BATCH.FCST_QTY, TMP_FCST_BATCH.BASIC_MAT " & _
"FROM TMP_FCST_BATCH " & _
"WHERE TMP_FCST_BATCH.PASS = True " & _
"ORDER BY TMP_FCST_BATCH.CALEN_PER, TMP_FCST_BATCH.BASIC_MAT, TMP_FCST_BATCH.ABC DESC , " & _
"TMP_FCST_BATCH.FCST_QTY "
Set rs_FCST_BATCH = CurrentDb.OpenRecordset(str_FCST_BATCH)
If rs_FCST_BATCH.RecordCount <> 0 Then
rs_FCST_BATCH.MoveLast
fb = rs_FCST_BATCH.RecordCount
'MsgBox fb
rs_FCST_BATCH.MoveFirst
End If
'MsgBox "records to match locally:" & fb
For a = 1 To fb
int_ID = rs_FCST_BATCH.Fields(1)
int_FCST_ID = rs_FCST_BATCH.Fields(0)
dbl_FCST_QTY = rs_FCST_BATCH.Fields(3)
str_ABC = rs_FCST_BATCH.Fields(2)
str_BAS_MAT = rs_FCST_BATCH.Fields(4)
'MsgBox int_ID & " and " & int_FCST_ID
str_ALLOC = "SELECT YSOC_CONSOLIDATE.ID, TMP_FCST_BATCH.ID, YSOC_CONSOLIDATE.DIRTY_MAT, " & _
"YSOC_CONSOLIDATE.PLANT, YAV_SUPPLYLOC.SLOC_PR, YSOC_CONSOLIDATE.ABC_RATE, " & _
"YSOC_CONSOLIDATE.HARVEST_DATE, IIf(IsNull([REM_QTY]),[QTY],[REM_QTY]) AS AVAIL_QTY " & _
"FROM TMP_STOCK " & _
"INNER JOIN (TMP_FCST_BATCH " & _
"INNER JOIN (YAV_SUPPLYLOC " & _
"INNER JOIN YSOC_CONSOLIDATE " & _
"ON (YSOC_CONSOLIDATE.MPG = YAV_SUPPLYLOC.MPG) " & _
"AND (YAV_SUPPLYLOC.Plant = YSOC_CONSOLIDATE.PLANT)) " & _
"ON (TMP_FCST_BATCH.BASIC_MAT = YSOC_CONSOLIDATE.DIRTY_MAT) " & _
"AND (TMP_FCST_BATCH.MPG = YAV_SUPPLYLOC.MPG) " & _
"AND (TMP_FCST_BATCH.COUNTRY = YAV_SUPPLYLOC.CO)) " & _
"ON (TMP_STOCK.BATCH_ID = TMP_FCST_BATCH.ID) " & _
"AND (TMP_STOCK.STOCK_ID = YSOC_CONSOLIDATE.ID) " & _
"WHERE (((YSOC_CONSOLIDATE.bln_IN_STOCK) = True)) " & _
"GROUP BY YSOC_CONSOLIDATE.ID, TMP_FCST_BATCH.ID, YSOC_CONSOLIDATE.DIRTY_MAT, YSOC_CONSOLIDATE.PLANT, " & _
"YAV_SUPPLYLOC.SLOC_PR, YSOC_CONSOLIDATE.ABC_RATE, YSOC_CONSOLIDATE.HARVEST_DATE, " & _
"IIf(IsNull([REM_QTY]),[QTY],[REM_QTY]) " & _
"HAVING (((TMP_FCST_BATCH.ID) = " & rs_FCST_BATCH.Fields(1) & ") " & _
"And ((YSOC_CONSOLIDATE.ABC_RATE) = 'A' " & _
"Or (YSOC_CONSOLIDATE.ABC_RATE) = 'B' " & _
"Or (YSOC_CONSOLIDATE.ABC_RATE) = 'C') " & _
"And ((IIf(IsNull([REM_QTY]), [QTY], [REM_QTY])) Is Not Null)) " & _
"ORDER BY YSOC_CONSOLIDATE.DIRTY_MAT, YAV_SUPPLYLOC.SLOC_PR, YSOC_CONSOLIDATE.ABC_RATE DESC , " & _
"YSOC_CONSOLIDATE.HARVEST_DATE, IIf(IsNull([REM_QTY]),[QTY],[REM_QTY]) "
'MsgBox str_ALLOC
Set rs_ALLOC = CurrentDb.OpenRecordset(str_ALLOC)
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
TRUSTED BY
ASKER