Is this to long to be a Sub? I'm only half done.... It's just a bunch of data manipulation. I would hate to get this all done and it would not work, I'd jump out the 13th floor window at my office....
Any Suggestions?
'On Error Resume Next 'Vb Runtime Error Object
On Error GoTo Handler
Dim strSQL As String
Dim rs As ADODB.Recordset
Dim rsCon As ADODB.Recordset
Dim objBkm As String
prgBar.Visible = True
Label5.Visible = True
Shape5.Visible = True
Set rs = New ADODB.Recordset
rs.Open "TMP_PD_DATA", ObjData.OraConn, adOpenForwardOnly, adLockReadOnly, adCmdTable
If rs.BOF Then
rs.MoveFirst
Else
rs.MoveFirst
End If
'fields for TMP_PD_DATA
Dim strEXT_AGT_CD
Dim strREP_FST_NAM
Dim strREP_MID_NAM
Dim strREP_LST_NAM
Dim strREP_MLG_ADR_LIN_0
Dim strREP_MLG_ADR_LIN_1
Dim strREP_MLG_ADR_LIN_2
Dim strREP_MLG_CTY
Dim strREP_MLG_STA_CD
Dim strREP_MLG_ZIP_CD
Dim strREP_MLG_ZP4_CD
Dim strREP_BUS_ADR_LIN_0
Dim strREP_BUS_ADR_LIN_1
Dim strREP_BUS_ADR_LIN_2
Dim strREP_BUS_CTY
Dim strREP_BUS_STA_CD
Dim strREP_BUS_ZIP_CD
Dim strREP_BUS_ZP4_CD
Dim strREP_BUS_PHN
Dim strREP_HME_PHN
Dim strREP_FAX_ADR
Dim strREP_EMAIL_ADR
Dim strINS_CTR_DATE
Dim strREP_TYP_CD
Dim strAGC_NAME
Dim strAGC_CD
Dim strSUB_AGC_CD
Dim strPAYEE_CD
Dim strNow 'date for all inserts
Dim strNowTemp 'convert strNow to Oracle PM or AM date format
Dim strOraDFormat 'convertion date format for PM OR AM
Dim sSQLAcc As String 'Account Insert Statement
Dim sSQLAccRef As String 'Account reference id's
Dim sSQLAExpan As String 'Account expantion table
Dim sSQLAExpanRef As String 'Account reference id's for expantion table
Dim sSQLAdds As String 'Shipping Address Insert Statement
Dim sSQLAddp As String 'Primary Address Insert Statement
Dim sSQLSelCon 'Select Contacts where the AGC_CD and SUB_AGC_CD = the account ref table
Dim strPostalCodes As String 'Format the 2 Oracle Shipping Postal codes into one
Dim strPostalCodep As String 'Format the 2 Oracle Primary Postal codes into one
Dim strAccount 'saleslogix account name string
Dim nErrorNum 'error number for error handler loop
Dim strAccountid As String 'Accountid as SalesLogix table id's
Dim strAddressidP As String 'AddressidP as SalesLogix Primary address table id
Dim strAddressidS As String 'AddressidS as SalesLogix Shipping address table id
Dim strShippingid As String 'Shipping as SalesLogix table id's
Dim strUserid As String 'Userid as SalesLogix table id's
Dim i As Long 'Contact For...Next Statment counter
Dim sContacid As String 'Contactid
strNow = Format$(Now, "mm/dd/yyyy hh:nn:ss AM/PM") 'nn may cause an error, will validate error
strNowTemp = Mid(strNow, 21, 23)
If strNowTemp = "PM" Then
strOraDFormat = "MM-DD-YYYY HH12:MI:SS PM"
Else
strOraDFormat = "MM-DD-YYYY HH12:MI:SS AM"
End If
sSQLSelCon = "SELECT * FROM TMP_PD_REF WHERE AGC_CD = '" & strAGC_CD & "' AND SUB_AGC_CD = '" & strSUB_AGC_CD & "'"
Do Until rs.EOF
If rs.Fields("REP_TYP_CD") = "NMO" Or rs.Fields("REP_TYP_CD") = "RMO" Or rs.Fields("REP_TYP_CD") = "EMO" Then
prgBar.Value = (rs.AbsolutePosition / rs.RecordCount) * 100
'strAccountid = slapi_DBCreateIDFor("ACCOUNT")
'strAddressidP = slapi_DBCreateIDFor("ADDRESS")
'strAddressidS = slapi_DBCreateIDFor("ADDRESS")
strShippingid = strAddressid
strUserid = "N82VV0000063"
strREP_FST_NAM = rs.Fields("REP_FST_NAM")
strREP_MID_NAM = rs.Fields("REP_MID_NAM")
strREP_LST_NAM = rs.Fields("REP_LST_NAM")
strREP_BUS_PHN = rs.Fields("REP_BUS_PHN")
strREP_HME_PHN = rs.Fields("REP_HME_PHN")
strREP_FAX_ADR = rs.Fields("REP_FAX_ADR")
strREP_EMAIL_ADR = rs.Fields("REP_EMAIL_ADR")
strREP_TYP_CD = rs.Fields("REP_TYP_CD")
If strREP_FST_NAM = "" And strREP_MID_NAM = "" Then
strAccount = UCase(strREP_MID_NAM)
Else
strAccount = UCase(strREP_FST_NAM & " " & strREP_MID_NAM & " " & strREP_LST_NAM)
End If
sSQLAcc = "INSERT INTO ACCOUNT (ACCOUNTID, SECCODEIED, ACCOUNTMANAGERID, ADDRESSID, SHIPPINGID, ACCOUNT, TYPE, " & _
"MAINPHONE, ALTERNATEPHONE, FAX, CREATEUSER, MODIFYUSER, CREATEDATE, MODIFYDATE) VALUES (" & _
"'" & strAccountid & "', 'F82VV0000042' ,'NN6BP0268763','" & strAddressidP & "', '" & strAddressidS & "'," & _
"'" & strAccount & "', '" & strREP_TYP_CD & "', '" & strREP_BUS_PHN & "', '" & strREP_HME_PHN & "'," & _
"'" & strREP_FAX_ADR & "', '" & strUserid & "', '" & strUserid & "', TO_DATE ('" & strNow & "', '" & strOraDFormat & "'), " & _
"TO_DATE ('" & strNow & "', '" & strOraDFormat & "')"
a.Write "ACCOUNT INSERT"
a.WriteBlankLines 1
a.Write sSQLAcc
a.WriteBlankLines 2
strAGC_CD = rs.Fields("AGC_CD")
strSUB_AGC_CD = rs.Fields("SUB_AGC_CD")
strPAYEE_CD = rs.Fields("PAYEE_CD")
strINS_CTR_DATE = rs.Fields("INS_CTR_DATE")
strEXT_AGT_CD = rs.Fields("EXT_AGT_CD")
sSQLExpan = "INSERT INTO FIXED_ACC_PD (ACCOUNTID, AGC_CD, SUB_AGC_CD, PAYEE_CD, INS_CTR_DATE, EXT_AGT_CD, CREATEUSER, " & _
"MODIFYUSER, CREATEDATE, MODIFYDATE) VALUES ('" & strAccountid & "', '" & strAGC_CD & "', '" & strSUB_AGC_CD & "'," & _
"'" & strPAYEE_CD & "', '" & strEXT_AGT_CD & "', '" & strINS_CTR_DATE & "','" & strUserid & "', '" & strUserid & "'," & _
"TO_DATE ('" & strNow & "', '" & strOraDFormat & "'), TO_DATE ('" & strNow & "', '" & strOraDFormat & "')"
a.Write "ACCOUNT.FIXED_ACC_PD INSERT"
a.WriteBlankLines 1
a.Write sSQLCon
a.WriteBlankLines 2
strREP_MLG_ADR_LIN_0 = rs.Fields("REP_MLG_ADR_LIN_0")
strREP_MLG_ADR_LIN_1 = rs.Fields("REP_MLG_ADR_LIN_1")
strREP_MLG_ADR_LIN_2 = rs.Fields("REP_MLG_ADR_LIN_2")
strREP_MLG_CTY = rs.Fields("REP_MLG_CTY")
strREP_MLG_STA_CD = rs.Fields("REP_MLG_STA_CD")
strREP_MLG_ZIP_CD = rs.Fields("REP_MLG_ZIP_CD")
strREP_MLG_ZP4_CD = rs.Fields("REP_MLG_ZP4_CD")
If strREP_MLG_ZP4_CD = "" Then
strPostalCodes = strREP_MLG_ZIP_CD
Else
strPostalCodes = strREP_MLG_ZIP_CD & "-" & strREP_MLG_ZP4_CD
End If
sSQLAdds = "INSERT INTO ADDRESS (ADDRESSID, ENTITYID, ADDRESS1, ADDRESS2, ADDRESS3, CITY, STATE, POSTALCODE, ISPRIMARY, ISMAILING, " & _
"DESCRIPTION, CREATEUSER, MODIFYUSER, CREATEDATE, MODIFYDATE) VALUES ('" & strAddressidS & "', '" & strAccountid & "'," & _
"'" & strREP_MLG_ADR_LIN_1 & "', '" & strREP_MLG_ADR_LIN_2 & "', '" & strREP_MLG_ADR_LIN_0 & "', '" & strREP_MLG_CTY & "', '" & strREP_MLG_STA_CD & "'," & _
"'" & strPostalCodes & "', 'F', 'T', 'Shipping', '" & strUserid & "', '" & strUserid & "'," & _
"TO_DATE('" & strNow & "', '" & strOraDFormat & "'), TO_DATE('" & strNow & "', '" & strOraDFormat & "')"
a.Write "ACCOUNT.ADDRESS (SHIPPING) INSERT"
a.WriteBlankLines 1
a.Write sSQLAdds
a.WriteBlankLines 2
strREP_BUS_ADR_LIN_0 = rs.Fields("REP_BUS_ADR_LIN_O")
strREP_BUS_ADR_LIN_1 = rs.Fields("REP_BUS_ADR_LIN_1")
strREP_BUS_ADR_LIN_2 = rs.Fields("REP_BUS_ADR_LIN_2")
strREP_BUS_CTY = rs.Fields("REP_BUS_CTY")
strREP_BUS_STA_CD = rs.Fields("REP_BUS_STA_CD")
strREP_BUS_ZIP_CD = rs.Fields("REP_BUS_ZIP_CD")
strREP_BUS_ZP4_CD = rs.Fields("REP_BUS_ZP4_CD")
If strREP_BUS_ZP4_CD = "" Then
strPostalCodep = strREP_BUS_ZIP_CD
Else
strPostalCodep = strREP_BUS_ZIP_CD & "-" & strREP_BUS_ZP4_CD
End If
sSQLAddp = "INSERT INTO ADDRESS (ADDRESSID, ENTITYID, ADDRESS1, ADDRESS2, ADDRESS3, CITY, STATE, POSTALCODE, ISPRIMARY, ISMAILING, " & _
"DESCRIPTION, CREATEUSER, MODIFYUSER, CREATEDATE, MODIFYDATE) VALUES ('" & strAddressidP & "', '" & strAccountid & "'," & _
"'" & strREP_BUS_ADR_LIN_1 & "', '" & strREP_BUS_ADR_LIN_2 & "', '" & strREP_BUS_ADR_LIN_0 & "', '" & strREP_BUS_CTY & "', '" & strREP_BUS_STA_CD & "'," & _
"'" & strPostalCodep & "', 'T', 'F', 'Primary', '" & strUserid & "', '" & strUserid & "'," & _
"TO_DATE('" & strNow & "', '" & strOraDFormat & "'), TO_DATE('" & strNow & "', '" & strOraDFormat & "')"
a.Write "ACCOUNT.ADDRESS (PRIMARY) INSERT"
a.WriteBlankLines 1
a.Write sSQLAddp
a.WriteBlankLines 2
rsCon.Open sSQLSelCon, ObjData.OraConn, adOpenForwardOnly, adLockReadOnly, adCmdText
'***************************Contact Records from TMP_PD_DATA
For i = 1 To rsCon.RecordCount Step 1
sSQLConIns = "INSERT INTO CONTACT (CONTACTID, TYPE, ACCOUNTID, ACCOUNT, ISPRIMARY, LASTNAME, FIRSTNAME, LASTNAME, " & _
"MIDDLENAME, ADDRESSID, SHIPPINGID, WORKPHONE, HOMEPHONE, FAX, EMAIL, ACCOUNTMANAGERID, CREATEUSER, MODIFYUSER, " * _
"CREATEDATE, MODIFYDATE) VALUES ('"
Next
rsCon.Close
'****************************Contact Records from TMP_PD_DATA
End If 'NMO,RMO,EMO if statement in 1st line of DO loop
rs.MoveNext
Loop
Handler:
If slapi_LogixErrors Then
strslapi_ErrCds = slapi_LogixErrorCode 'Public variable in SlgxApi.bas
strslapi_ErrTxt = slapi_LogixErrorText 'Public variable in SlgxApi.bas
frmErrors.Show
Exit Sub
ElseIf Err.Number <> 0 Then
nErr = Err.Number 'Error Number as public string
sErrSorc = Err.Source 'Error Source as public string
sErrDescr = Err.Description 'Error Description as public string
frmErrors.Show
Exit Sub
End If
a.Close
Set a = Nothing
Set fs = Nothing
End Sub
