Check Accounts – need to fail if not existing
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!
SC Version 22.214.171.124
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
If you would like to submit an answer or comment, please sign in to the eOne portal.