77 lines
3.3 KiB
Plaintext
77 lines
3.3 KiB
Plaintext
Function GetWinLineDocDeliveryNoteByUnsplittedProducts(ProductNumber, WinLineMandatorNr, WinLineYear)
|
|
|
|
'Version date: 15.09.2020
|
|
|
|
Dim Conn, Result
|
|
|
|
Set Conn = CWLStart.CurrentCompany.Connection
|
|
|
|
If (ProductNumber <> "") and (WinLineMandatorNr <> "") and (WinLineYear <> "") Then
|
|
|
|
'c999 = cOrdnerNr
|
|
'c998 = cInvoiceNr
|
|
'c997 = KeyValue
|
|
'c996 = KeyValue
|
|
'MESOPRIM = MESOPRIM
|
|
'c995 = Amount / Pos
|
|
'c994 = Amount / overall
|
|
|
|
SQL = ""
|
|
SQL = SQL & "SELECT t025.c045 as [c999], t025.c055 as [c998], T026.C000 as [c997], T025.C000 as [c996], T024.MESOPRIM, t026.c006 as [c995], "
|
|
|
|
SQL = SQL & "( "
|
|
SQL = SQL & "SELECT sum(t026.c006) "
|
|
SQL = SQL & "FROM T026 WITH (NOLOCK), T025 WITH (NOLOCK), T024 WITH (NOLOCK) "
|
|
SQL = SQL & "WHERE T026.MESOCOMP = '"& WinLineMandatorNr &"' AND T025.MESOCOMP = '"& WinLineMandatorNr &"' AND T024.MESOYEAR = '"& WinLineCurrentYear &"' AND T024.MESOCOMP = '"& WinLineMandatorNr &"' "
|
|
SQL = SQL & "AND (T025.C021 = T026.C044 AND T025.C022 = T026.C045 AND T025.C137 = 3 AND T026.C042 = N'1' AND T026.C055 < 10 AND T026.C074 < 10 AND T025.C186 = 0 AND T026.C003 = T024.C002 "
|
|
SQL = SQL & "AND (T025.C025 = N'D' OR T025.C025 =N'*' OR T025.C026 = N'D' OR T025.C026 =N'*') "
|
|
SQL = SQL & "AND (T026.C039 = N'D' OR T026.C039 =N'*' OR T026.C040 = N'D' OR T026.C040 =N'*') "
|
|
SQL = SQL & "AND T026.C006 <> 0.0 AND T026.C109 <= 0 AND T026.C003 >= '"& ProductNumber &"' AND T026.C003 <= N'TT1111001') "
|
|
SQL = SQL & ") as [c994] "
|
|
|
|
SQL = SQL & "FROM T026 WITH (NOLOCK), T025 WITH (NOLOCK), T024 WITH (NOLOCK) "
|
|
SQL = SQL & "WHERE T026.MESOCOMP = '"& WinLineMandatorNr &"' AND T025.MESOCOMP = '"& WinLineMandatorNr &"' AND T024.MESOYEAR = '"& WinLineCurrentYear &"' AND T024.MESOCOMP = '"& WinLineMandatorNr &"' "
|
|
SQL = SQL & "AND (T025.C021 = T026.C044 AND T025.C022 = T026.C045 AND T025.C137 = 3 AND T026.C042 = N'1' AND T026.C055 < 10 AND T026.C074 < 10 AND T025.C186 = 0 AND T026.C003 = T024.C002 "
|
|
SQL = SQL & "AND (T025.C025 = N'D' OR T025.C025 =N'*' OR T025.C026 = N'D' OR T025.C026 =N'*') "
|
|
SQL = SQL & "AND (T026.C039 = N'D' OR T026.C039 =N'*' OR T026.C040 = N'D' OR T026.C040 =N'*') "
|
|
SQL = SQL & "AND T026.C006 <> 0.0 AND T026.C109 <= 0 AND T026.C003 >= '"& ProductNumber &"' AND T026.C003 <= '"& ProductNumber &"' ) "
|
|
SQL = SQL & "ORDER BY T026.C003 ASC, T026.C025, T026.C044, T026.C045 "
|
|
|
|
'MsgBox "SQL (Part 1): " & Mid(SQL, 1, 750)
|
|
'MsgBox "SQL (Part 2): " & Mid(SQL, 750)
|
|
|
|
Set Result = Conn.Select(SQL)
|
|
|
|
'msgbox "egal was"
|
|
'test = result.rowcount
|
|
'msgbox "type: " & TypeName(result)
|
|
'msgbox "result: " & result
|
|
'msgbox "Rowcount: " & test
|
|
'msgbox "result: " & result.value("c999")
|
|
|
|
If DEBUG_ON = True Then
|
|
AddDebugLine "Querying for unsplitted delivery notes.. " & vbNewline
|
|
AddDebugLine "Result Columns: " & Result
|
|
AddDebugLine "Result Rows: " & Result.RowCount
|
|
AddDebugLine "SQL (Part 1): " & Mid(SQL, 1, 750)
|
|
AddDebugLine "SQL (Part 2): " & Mid(SQL, 750)
|
|
|
|
ShowDebugBox "GetWinLineInternalProductNumber"
|
|
End If
|
|
|
|
'Use the set order, because we want to return an object!
|
|
Set GetWinLineDocDeliveryNoteByUnsplittedProducts = Result
|
|
|
|
Else
|
|
|
|
If DEBUG_ON = True Then
|
|
AddDebugLine "Invalid argument call!" & vbNewline
|
|
|
|
ShowDebugBox "GetWinLineInternalProductNumber"
|
|
End If
|
|
|
|
GetWinLineDocDeliveryNoteByUnsplittedProducts = 0
|
|
|
|
End If
|
|
|
|
End Function |