Check Accounts – need to fail if not existing
Sherry asked 10 years ago
I am using the sample KB script for Check Accounts restriction as a model for a different task. I need to take the functionality one step further. I want to ONLY update existing Deferral Profiles in PP000010 table; that table is my Destination. If a profile in my source file does NOT already exist, I do not want to import it. Example: I have a source file with two profiles – TEST and TEST1. TEST already exists; I just need to update the start date and number of periods. TEST1 does not exist, and I do NOT want to create it; I want it to fail. When it fails, I would like an error message box to be displayed like the Check Accounts restriction and an error email to be sent, telling the user that the profile does not exist and cannot be updated. The Check Accounts script only returns true; it won't ever fail. So TEST1 gets integrated. How can I add a true restriction to this node so it only includes source records where the profile exists and returns an error for the others? Thanks in advance!
Answers
Best Answer
Sherry answered 10 years ago
Sherry answered 10 years ago
Well, I have code that works. Not sure it’s pretty; I’m not a code writer. But I thought it might help somebody else trying to do something similar. This is writing to a PP (Revenue and Expense Deferrals) table. This code is on the Restriction for the node – in place of what would be there from the sample – Check Accounts. I am also using the Display Accounts to show the box with the errors.
Dim server As String = “SERVER”
Dim database As String = “DB”
Dim user As String = “USER”
Dim password As String = “password”
Dim myProfile As String = _PROFILE
Dim mapInterface As Integer = 1
‘—————————————————————————————
Try
‘Declare the connection string based on the configuration variables
Dim conString As New String(“Data Source=” & server & “;Initial Catalog=” & database & “;User=” & user & “;Password=” & password & “;”)
‘Define Profile for testing if exists
dim Test as string = “”
‘Define SQL Query to return bad value if not exists
dim SQLQuery = “if not exists (select a.PP_Profile_name from pp000010 a where PP_Profile_Name = ‘” & myProfile & “‘) begin select ‘FAILED’ as [Profile] end else begin select a.PP_Profile_name as [Profile] from pp000010 a where PP_Profile_Name = ‘” & myProfile & “‘ end”
‘Define the SQL connection and open it
Dim myConn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(conString)
myConn.Open()
‘Declare the SQL command as the query string
Dim myCmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(SQLQuery, myConn)
‘Define the SQL Reader and execute it
Dim sqlReader As System.Data.SqlClient.SqlDataReader = myCmd.ExecuteReader()
‘read the line
sqlreader.Read()
Test = sqlreader(“Profile”).ToString()
‘This captures all the profiles that don’t exist and concatenates them in GBL-EONEPROFILES variable for later display/email.
if Test = “FAILED”
GBL_EONEPROFILES = GBL_EONEPROFILES & myProfile & Convert.ToChar(9) & ” Does not exist; not updated.” & Environment.NewLine
end if
‘This Message Box can be used to show the non existent profiles one at a time if needed.
‘if Test = “FAILED”
‘MessageBox.Show(Test & “-” & myProfile & ” Does Not Exist”)
‘end if
myConn.Close()
‘This is the actual Restriction that determines whether or not the source file line is selected for integration.
if Test = “FAILED”
Return False
else
Return True
end if
Catch ex As Exception
If (mapInterface = 1) Then
Messagebox.Show(ex.Message, “CheckAccountNumbers Script Error”)
End If
Return False
end try
SC Version 20.12.1.16