Hi all,
I have a requirement to use Excel(2010) to validate GL Code Combinations(In Oracle eBS R12.1.3) that are entered on a spreadsheet.
I have setup the ADODB macro (another requirement) and can pass simple queries, and it all works correctly, however I am trouble converting a script for use in the macro.
Script I am trying to convert:
DECLARE
l_return boolean;
l_message varchar2(240);
BEGIN
l_return := fnd_flex_keyval.validate_segs(operation => 'CHECK_COMBINATION'
,appl_short_name => 'SQLGL'
,key_flex_code => 'GL#'
,structure_number => 50268
,concat_segments => '6701.24600.14003.50.08.091'
,validation_date => sysdate);
l_message := FND_FLEX_KEYVAL.ERROR_MESSAGE;
If l_return THEN
dbms_output.put_line('Valid');
Else
dbms_output.put_line(l_message);
End if;
END;
Basically, i am having trouble converting this to something to pass through to Oracle, and have a message returned saying either Valid, or the reason why it's not a valid combination.
Once i work out how to pass it all through correctly, I can then update the macro to loop through the spreadsheet and update accordingly. I'm just having problems converting and passing back and forth. I know the RETURN from that package and function is BOOLEAN.
If anyone could help, that would be greatly appreciated.
Macro in Excel so far:
Sub QueryGLComb()
Dim Oracon As ADODB.Connection
Dim recset As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim param1 As New ADODB.Parameter
Dim param2 As New ADODB.Parameter
Dim param3 As New ADODB.Parameter
Dim param4 As New ADODB.Parameter
Dim param5 As New ADODB.Parameter
Dim param6 As New ADODB.Parameter
Dim param7 As New ADODB.Parameter
Dim objErr As ADODB.Error
Dim lngRow As Long
On Error GoTo err_test
DeptValue = InputBox(Message, Title, Default)
If DeptValue = "" Then Exit Sub
If DeptValue < 10 Or DeptValue > 30 Then DeptValue = 30
Set Oracon = CreateObject("ADODB.Connection")
Oracon.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
"Data Source=DEMO;" & _
"User ID=scott;" & _
"Password=tiger;"
Oracon.Open
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = Oracon
Set param1 = cmd.CreateParameter("param1", adVariant, adParamInput, , "CHECK_COMBINATION")
Set param2 = cmd.CreateParameter("param2", adVariant, adParamInput, , "SQLGL")
Set param3 = cmd.CreateParameter("param3", adVariant, adParamInput, , "GL#")
Set param4 = cmd.CreateParameter("param4", adVariant, adParamInput, , "50268")
Set param5 = cmd.CreateParameter("param5", adVariant, adParamInput, , "6701.24600.14003.50.08.091")
Set param6 = cmd.CreateParameter("param6", adVariant, adParamInput, , "sysdate")
cmd.Parameters.Append param1
cmd.Parameters.Append param2
cmd.Parameters.Append param3
cmd.Parameters.Append param4
cmd.Parameters.Append param5
cmd.Parameters.Append param6
Set param7 = cmd.CreateParameter("param7", adBoolean, adParamOutput)
cmd.Parameters.Append param7
' Enable PLSQLRSet property
cmd.Properties("PLSQLRSet") = True
cmd.CommandText = "{exec fnd_flex_keyval.validate_segs(?,?,?,?,?,?,?)}"
Set recset = cmd.Execute
' Disable PLSQLRSet property
cmd.Properties("PLSQLRSet") = False
Do While Not recset.EOF
MsgBox recset.Fields(1).Value '--Not sure on this bit also, but will work out later
recset.MoveNext
Loop
Exit Sub
err_test:
MsgBox Error$
For Each objErr In Oracon.Errors
MsgBox objErr.Description
Next
Oracon.Errors.Clear
Resume Next
End Sub
Many thanks.
Russell H.