|
tracy5436
Yak Posting Veteran
50 Posts |
 Posted - 2010-07-10 : 22:48:05
|
Hi,
I have a macro below which uses SQL to extract data from an IV30300 table and inserts the data into a INVENTRY table.
There are various columns in the table, such as allocated quantity, allocated value, returned quantity, returned value.
My problem is that I can't get the correct values to be inserted for the allocqty and retqty values. These variables are totalled in the field Allocated Quantity in the INVENTRY table. However, it seems that once there are no values in some of the variables, it throws out the the values in others. I suspect my problem is with my join statements, but I have tried different things but I still can't get it right.
Can someone help please ?
Sub Inventory()
'
' Inventory Macro
'
' Keyboard Shortcut: Ctrl+i
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim sqlstring As String
Dim sqlstring2 As String
Dim sqlstring3 As String
Dim sqlstring4 As String
Dim sqlstring5 As String
Dim sqlstring6 As String
Dim sqlstring7 As String
Dim rstupdate As New ADODB.Recordset
Dim objConn
Dim cmdString
Dim objRec
Dim startdat As String
Dim enddate As String
Dim procdate As String
Dim exchangerate As Currency
ChDir "E:\KIRK DISTRIBUTORS - ITT\SALES"
Workbooks.Open Filename:="E:\KIRK DISTRIBUTORS - ITT\INVENTRY\INVENTRY4.XLS"
Rows("2:10658").Select
Selection.Delete Shift:=xlUp
Workbooks.Close
Set objRec = CreateObject("ADODB.Recordset")
Set objConn = CreateObject("ADODB.Connection")
With cn
.ConnectionString = "Provider=MSDASQL;DSN=GREATPLAINS;User Id=sa;Password=ramses2;database=KDLTD;"
.CursorLocation = adUseClient
.Open
End With
cmd.ActiveConnection = cn
cmd.CommandType = adCmdText
cn.CommandTimeout = 900000
cmd.CommandTimeout = 900000
startdat = InputBox("Starting Date: (MM/DD/YYYY")
enddate = InputBox("Ending Date: (MM/DD/YYYY)")
procdate = InputBox("Process Date: (YYYYMM)")
exchangerate = InputBox("Exchange Rate")
sqlstring2 = "DELETE INVENTRY"
sqlstring = "INSERT INTO INVENTRY([STOCKLIST CODE], [WAREHOUSE CODE], [PROCESS YYYYMM], [INVENTORY TYPE], [PRODUCT CODE], [OPENING BALANCE QTY.], [OPENING BAL. VALUE],[RECEIPT QUANTITY],[RECEIPT VALUE],[FREE RECEIPT QUANTITY],[FREE RECEIPT VALUE],[RETURN QUANTITY],[RETURN VALUE],[ADJUSTMENT QUANTITY],[ADJUSTMENT VALUE],[ALLOCATED QUANTITY],[ALLOCATED VALUE],[CLOSING QUANTITY],[CLOSING VALUE])"
sqlstring = sqlstring & " SELECT '5555550101','MGD',ProcessDate,'S',ProductCode,ISNULL(OpenQty,0.00),ISNULL(OpeningBalValue,0.00),ISNULL(RecQty,0.00),ISNULL(RecValue,0.00),0.00,0.00,0.00,0.00,ISNULL(AdjQtyb,0.00)+ISNULL(AdjQty,0.00),ISNULL(AdjValue,0.00)+ISNULL(AdjValueb,0.00),ISNULL(AllocQty,0.00)-ISNULL(RetQty,0.00),ISNULL(AllocValue,0.00)-ISNULL(RetValue,0.00),ISNULL(OpenQty,0.00)+ISNULL(RecQty,0.00)+ISNULL(RetQty,0.00)+ISNULL(AdjQty,0.00)+ISNULL(AdjQtyB,0.00)-ISNULL(AllocQty,0.00),ISNULL(OpeningBalValue,0.00)+ISNULL(RecValue,0.00)+ISNULL(RetValue,0.00)+ISNULL(AdjValue,0.00)-ISNULL(AllocValue,0.00) "
sqlstring = sqlstring & " FROM ( SELECT '" & procdate & "' As ProcessDate12,I11.[CP PRODUCT CODE] as ProductCode11,sum(I12.TRXQTY*I12.QTYBSUOM)*-1 as AllocQty,SUM(I12.TRXQTY*I12.QTYBSUOM*I11.[UNIT PRICE]) as AllocValue"
sqlstring = sqlstring & " FROM IV30300 I12"
sqlstring = sqlstring & " RIGHT OUTER JOIN [ITEM_MAPPING] I11"
sqlstring = sqlstring & " ON I11.[KIRK ITEM NUMBER] = I12.ITEMNMBR JOIN IV00101 I1 ON I11.[KIRK ITEM NUMBER] = I1.ITEMNMBR"
sqlstring = sqlstring & " and I12.DOCDATE BETWEEN '" & startdat & "' AND '" & enddate & "' "
sqlstring = sqlstring & " AND I12.DOCTYPE = '6' "
sqlstring = sqlstring & " AND I12.TRXLOCTN <> 'MGD_DAM'"
sqlstring = sqlstring & " GROUP BY I11.[CP PRODUCT CODE]"
sqlstring = sqlstring & " )D12"
sqlstring = sqlstring & " LEFT JOIN ("
sqlstring = sqlstring & " SELECT I70.[CP PRODUCT CODE] as ProductCode10,SUM(P10.TRXQTY * P10.QTYBSUOM) as RetQty,SUM(I70.[CASE PRICE]/P10.QTYBSUOM*(P10.TRXQTY * P10.QTYBSUOM)) as RetValue "
sqlstring = sqlstring & " FROM IV30300 P10 RIGHT OUTER JOIN [ITEM_MAPPING] I70 ON P10.ITEMNMBR = I70.[KIRK ITEM NUMBER] JOIN IV00101 I300 ON I70.[KIRK ITEM NUMBER] = I300.ITEMNMBR AND P10.DOCTYPE = '5' AND TRXLOCTN <> 'MGD_DAM' AND P10.DOCDATE BETWEEN '" & startdat & "' AND '" & enddate & "' GROUP BY I70.[CP PRODUCT CODE]"
sqlstring = sqlstring & " )D10"
sqlstring = sqlstring & " ON D12.ProductCode11 = D10.ProductCode10 "
sqlstring = sqlstring & " RIGHT OUTER JOIN"
sqlstring = sqlstring & " ( SELECT"
sqlstring = sqlstring & " '" & procdate & "' As ProcessDate1101,I201.[CP PRODUCT CODE] as ProductCode1101"
sqlstring = sqlstring & " FROM IV00101 I1101 JOIN [ITEM_MAPPING] I201 ON I1101.ITEMNMBR = I201.[KIRK ITEM NUMBER] "
sqlstring = sqlstring & " GROUP BY I201.[CP PRODUCT CODE]"
sqlstring = sqlstring & " )D1101"
sqlstring = sqlstring & " ON D10.ProductCode10 = D1101.ProductCode1101 "
sqlstring = sqlstring & " LEFT JOIN"
sqlstring = sqlstring & " ( SELECT"
sqlstring = sqlstring & " '" & procdate & "' As ProcessDate,I2.[CP PRODUCT CODE] as ProductCode,SUM(I3.QTYONHND*convert(money,I1.USCATVLS_2)) As ClosingQty1"
sqlstring = sqlstring & " FROM IV00101 I1 JOIN [ITEM_MAPPING] I2 ON I1.ITEMNMBR = I2.[KIRK ITEM NUMBER] JOIN IV00102 I3 ON I1.ITEMNMBR = I3.ITEMNMBR AND I3.LOCNCODE <> 'MGD_DAM'"
sqlstring = sqlstring & " GROUP BY I2.[CP PRODUCT CODE]"
sqlstring = sqlstring & " )D1"
sqlstring = sqlstring & " ON D1101.ProductCode1101 = D1.ProductCode "
sqlstring = sqlstring & " LEFT JOIN ("
sqlstring = sqlstring & " SELECT B2.[CP PRODUCT CODE] as Item,SUM(B1.[QTY PER STOCK]*convert(money,I3000.USCATVLS_2)) as ClosingQty,SUM(B1.[QTY PER STOCK]*convert(money,I3000.USCATVLS_2)*B2.[CASE PRICE]/convert(money,I3000.USCATVLS_2)) as ClosingValue,SUM(B1.[QTY PER STOCK]*convert(money,I3000.USCATVLS_2)) as OpenQty,SUM(B2.[CASE PRICE]/convert(money,I3000.USCATVLS_2)*(B1.[QTY PER STOCK])) As OpeningBalValue"
sqlstring = sqlstring & " FROM OPENINGBAL B1 INNER JOIN [ITEM_MAPPING] B2 ON B1.[ITEM NUMBER] = B2.[KIRK ITEM NUMBER] JOIN IV00101 I3000 ON B2.[KIRK ITEM NUMBER] = I3000.ITEMNMBR GROUP BY B2.[CP PRODUCT CODE]"
sqlstring = sqlstring & " )D13"
sqlstring = sqlstring & " ON D1.ProductCode = D13.Item"
sqlstring = sqlstring & " LEFT JOIN ("
sqlstring = sqlstring & " SELECT I6.[CP PRODUCT CODE] as ProductCode2,SUM(I5.TRXQTY*I5.QTYBSUOM) as AdjQty,SUM(I6.[CASE PRICE]/I5.QTYBSUOM*(I5.TRXQTY*I5.QTYBSUOM)) as AdjValue"
sqlstring = sqlstring & " FROM IV30300 I5 RIGHT OUTER JOIN [ITEM_MAPPING] I6 ON I6.[KIRK ITEM NUMBER] = I5.ITEMNMBR JOIN IV00101 I30 ON I6.[KIRK ITEM NUMBER] = I30.ITEMNMBR AND DOCDATE BETWEEN '" & startdat & "' AND '" & enddate & "' AND DOCTYPE = '1' "
sqlstring = sqlstring & " GROUP BY I6.[CP PRODUCT CODE]"
sqlstring = sqlstring & " )D2"
sqlstring = sqlstring & " ON D1.ProductCode = D2.ProductCode2"
sqlstring = sqlstring & " LEFT OUTER JOIN ("
sqlstring = sqlstring & " SELECT I9.[CP PRODUCT CODE] as ProdCode FROM [ITEM_MAPPING] I9 LEFT OUTER JOIN IV30300 I10 ON I9.[KIRK ITEM NUMBER] = I10.ITEMNMBR GROUP BY I9.[CP PRODUCT CODE]"
sqlstring = sqlstring & " )D4"
sqlstring = sqlstring & " ON D12.ProductCode11 = D4.ProdCode LEFT OUTER JOIN ("
sqlstring = sqlstring & " SELECT I7.[CP PRODUCT CODE] as ProductCode8,SUM(P1.TRXQTY * P1.QTYBSUOM) as RecQty,SUM(I7.[CASE PRICE]/P1.QTYBSUOM*(P1.TRXQTY * P1.QTYBSUOM)) as RecValue"
sqlstring = sqlstring & " FROM IV30300 P1 RIGHT OUTER JOIN [ITEM_MAPPING] I7 ON P1.ITEMNMBR = I7.[KIRK ITEM NUMBER] JOIN IV00101 I32 ON I7.[KIRK ITEM NUMBER] = I32.ITEMNMBR AND P1.DOCTYPE = '4' AND TRXLOCTN <> 'MGD_DAM' AND P1.DOCDATE BETWEEN '" & startdat & "' AND '" & enddate & "' GROUP BY I7.[CP PRODUCT CODE]"
sqlstring = sqlstring & " )D3"
sqlstring = sqlstring & " ON D4.ProdCode = D3.ProductCode8 "
sqlstring = sqlstring & " LEFT JOIN ("
sqlstring = sqlstring & " SELECT I6.[CP PRODUCT CODE] as ProductCode2B,SUM(I5.TRXQTY*I5.QTYBSUOM) as AdjQtyB,SUM(I6.[CASE PRICE]/I5.QTYBSUOM*(I5.TRXQTY*I5.QTYBSUOM)) as AdjValueB"
sqlstring = sqlstring & " FROM IV30300 I5 RIGHT OUTER JOIN [ITEM_MAPPING] I6 ON I6.[KIRK ITEM NUMBER] = I5.ITEMNMBR JOIN IV00101 I3 ON I6.[KIRK ITEM NUMBER] = I3.ITEMNMBR AND DOCDATE BETWEEN '" & startdat & "' AND '" & enddate & "' AND DOCTYPE = '2' "
sqlstring = sqlstring & " GROUP BY I6.[CP PRODUCT CODE]"
sqlstring = sqlstring & " )D2B"
sqlstring = sqlstring & " ON D2B.ProductCode2B = D3.ProductCode8"
'sqlstring = sqlstring & " LEFT JOIN ("
'sqlstring = sqlstring & " RIGHT OUTER JOIN ("
'sqlstring = sqlstring & " SELECT I90.[CP PRODUCT CODE] as ProdCode90 FROM [ITEM_MAPPING] I90 INNER JOIN IV00101 I100 ON I90.[KIRK ITEM NUMBER] = I100.ITEMNMBR GROUP BY I90.[CP PRODUCT CODE]"
'sqlstring = sqlstring & " )D40"
'sqlstring = sqlstring & " ON D3.ProductCode8 = D40.ProdCode90 LEFT OUTER JOIN ("
sqlstring3 = "UPDATE INVENTRY SET [ADJUSTMENT QUANTITY] = 1 WHERE [ADJUSTMENT VALUE] <> 0 and [adjustment quantity] = 0"
sqlstring4 = "UPDATE INVENTRY SET [ADJUSTMENT VALUE] = 1 WHERE [ADJUSTMENT QUANTITY] <> 0 and [adjustment value] = 0"
'sqlstring7 = "UPDATE INVENTRY SET [ALLOCATED QUANTITY] = -1 where [ALLOCATED value] < 0 and [ALLOCATED qUANtITy] = 0"
sqlstring5 = "UPDATE INVENTRY SET [CLOSING QUANTITY] = [OPENING BALANCE QTY.]+[RECEIPT QUANTITY]+[RETURN QUANTITY]+[ADJUSTMENT QUANTITY]-[ALLOCATED QUANTITY]"
sqlstring6 = "UPDATE INVENTRY SET [CLOSING VALUE] = [OPENING BAL. VALUE] + [RECEIPT VALUE] + [RETURN VALUE] + [ADJUSTMENT VALUE] - [ALLOCATED VALUE]"
rstupdate.Open sqlstring2, cn, adOpenDynamic, adLockOptimistic, adCmdText
rstupdate.Open sqlstring, cn, adOpenDynamic, adLockOptimistic, adCmdText
rstupdate.Open sqlstring3, cn, adOpenDynamic, adLockOptimistic, adCmdText
rstupdate.Open sqlstring4, cn, adOpenDynamic, adLockOptimistic, adCmdText
'rstupdate.Open sqlstring7, cn, adOpenDynamic, adLockOptimistic, adCmdText
rstupdate.Open sqlstring5, cn, adOpenDynamic, adLockOptimistic, adCmdText
rstupdate.Open sqlstring6, cn, adOpenDynamic, adLockOptimistic, adCmdText
MsgBox ("Process Complete")
cn.Close
End Sub
|
|
Joining tables