Hello,

Bear in mind I know very little about Transport Tasks, and I find the way they are implemented a little odd (as I often do with certain IFS aspects.  *smile*).

It looks like when a TT is created in IEE it creates an empty header record to start with, then detail lines are added to it. If a single line exists for a TT (a single part moving to and from locations), that part and location information are stored right on the TT header line. If multiple detail lines exist, then "..." starts to appear on the header line for fields such as part number and to/from location numbers.

I verified that just passing the single field "FIXED_DB" will successfully create the TT header line (as you probably already know). And the TT header line has no freeform field that you could try to use to place some sort of unique key to later retrieve the record. I tried adding a non-existent PART_NO and the procedure still succeeded in creating a TT header line, but PART_NO remained null.

(I am just thinking out loud here to wrap my head around data is structured.)

So, you want to add a new TT header record and get back the auto-incremented ID, and there is nothing on the record that you can use to make the record unique (other than the system-generated Transport_Task_Id). I see a few options here, but am not sure what will work best for you.

First, you could try to get output from the anonymous PL/SQL block, as outlined here: https://stackoverflow.com/questions/27613046/bind-in-and-out-parameters-to-anonymous-pl-sql. I am not sure if that will work, as anonymous blocks are not really meant to return output. You can explore that thread, though, and see if anything jumps out as useful.

Second, you could try just grabbing the highest Transport_Task_ID in the table after a successful insert. Since the ID auto-generates, the one you just created should have the highest number. I do not know if this is suitable for you, though, if for example you are going to have a LOT of TTs getting created at the same time.

Third, and similar the second option, you could try getting the last-used value of the Transport Task ID sequence right after you run the PL/SQL block to create the new row:

SELECT Last_Number - 1 AS Last_Used_ID
    FROM User_Sequences
    WHERE Sequence_Name = 'TRANSPORT_TASK_ID'

However, I am not sure how this will work across user sessions, so it could suffer from the same contention issue if a lot of folks are adding these rows at the same time.

Fourth and finally, you could write the SQL that is now an anonymous block as a packaged method in a custom API package under the IFSAPP schema. That would allow output parameters that I believe the access provider should let you to get back (I know output parameters work with standard ODBC calls from .NET, as that is what I leverage via the Oracle Instant Client iOIC] layer.)

The last option is probably the most direct and controllable. But the third option could work as well if you can test it out in a multi-user, high-volume environment and be sure last-used sequence IDs aren't getting crossed.

Let us know what you figure out! Good luck, and Happy New Year!

Thanks,
Joe Kaufman

Hi Joe,

Thanks for your reply and detailed description.

I am aware that my initial message conatins only part of the code that creates TT header.

My key requirement was to get newly created TT id directly from attribute (p3_) out of the current execution.
I wanted to avoid running additional select query to fetch this number mainly due to the fact that max id can be generated by another user. I assumed that many users may work at the same time.
Once I have TT id I launch instruction that assignes lines to TT.

Nevertheless I approached this issue as described in IFS’ documentation.

Here is the link: https://docs.ifs.com/techdocs/Foundation1/050_development/040_core_server/010_access_providers_dev/020_ap_dotnet/050_examples.htm
1. Go to paragraph: PLSQL Examples 
2. Click on “Commands” - this is where you can find sample code.

Many thanks,

Piotr

Piotr,

If you want to avoid another query (and since getting the TT ID back that way is not reliable in a multi-user environment), that leaves the fourth option as the only one that will probably work for you. I do not know of a way to get an output variable back from an anonymous block of code.

That being said, I am not sure why you can’t call Transport_Task_API.New__() directly and get output back from that? The documentation you link to describes how to bind to the input and output parameters, and you shouldn’t really have to use an anonymous block at all. From the link you posted, a little deeper in are examples:

http://ifstechdocs10.azurewebsites.net/Foundation1/050_development/040_core_server/010_access_providers_dev/020_ap_dotnet/code_examples/plsqlcommands.cs.txt

Have you tried something like that, with a direct call to the TT API’s “New” method? The TRANSPORT_TASK_ID comes back in the fourth parameter after a successful insert. That is the only way I can see of you getting the ID back without needing to do an additional query…

Good luck!

Joe Kaufman

Joe,

Thank you for your effort and engagement.

I have this issue sorted, therefore I shared link to documentation with you.

Kind regards,

Piotr

Piotr,

So you were able to get the bound variables working? I have been playing around and have code that works by using the “base method” scenario, as well as calling the New__ method directly. I am seeing the TT ID in the returned attr string.

I will post some sample code in a bit, but I have a problem with my code… Even though no errors are thrown and a new Transport_Task_Id is returned, no data is being written to IFS! Can you post your code that works so I can see what I am doing wrong? Is there some sort of COMMIT call I am missing? (Like I said, some code in a bit to provide examples for anyone else who might read this thread since Access Provider examples are fairly sparse...).

Thanks,

Joe Kaufman

Here is example code to add a new Transport_Task using a straight method call with bound parameters. It gets back the attr string and pulls out the value of TRANSPORT_TASK_ID:

try
{
	// It all starts with a connection string. We connect to the IFS Application server, not directly to the Oracle database!
	string connString = "https://<server>:<port>/";
	string userId = <username>;
	string password = <password>;
	// Establish connection. Set CatchExceptions = false because we are handling any errors here with the try..catch.
	FndConnection conn = new FndConnection(connString, userId, password);
	conn.CatchExceptions = false;
	// Set up bound variables.
	FndBindVariable p0Info = new FndBindVariable(FndBindVariableDirection.Out, "INFO", new FndTextAttribute());
	FndBindVariable p1ObjId = new FndBindVariable(FndBindVariableDirection.Out, "OBJID", new FndTextAttribute());
	FndBindVariable p2ObjVersion = new FndBindVariable(FndBindVariableDirection.Out, "OBJVERSION", new FndTextAttribute());
	FndBindVariable p3Attr = new FndBindVariable(FndBindVariableDirection.InOut, "ATTR", new FndTextAttribute());
	FndBindVariable p4Action = new FndBindVariable(FndBindVariableDirection.In, "ACTION", new FndTextAttribute("DO"));
	// Set up command to execute with bound variables embedded in the method call to add a new Transport Task.
	string runCommand = "Transport_Task_API.New__(:INFO, :OBJID, :OBJVERSION, :ATTR, :ACTION)";
	FndPLSQLCommand cmdNewTransportTask = new FndPLSQLCommand(conn, runCommand);
	// Bind the variables to the command.
	cmdNewTransportTask.BindVariables.Add(p0Info);
	cmdNewTransportTask.BindVariables.Add(p1ObjId);
	cmdNewTransportTask.BindVariables.Add(p2ObjVersion);
	cmdNewTransportTask.BindVariables.Add(p3Attr);
	cmdNewTransportTask.BindVariables.Add(p4Action);
	// Execute the command.
	cmdNewTransportTask.ExecuteNonQuery();
	// Evaluate the bound output variable to get Transport_Task_Id.
	FndAttributeString attr = new FndAttributeString(p3Attr.Value.ToString());
	MessageBox.Show(attr.GetValue("TRANSPORT_TASK_ID"));
}
catch (FndException err)
{
	err.Show();
}

Everything works fine, but as I said further up on this thread, no new data is being added to IFS. The Transport_Task_Id continues to increment, but I assume that is because it is auto-generated based on a Sequence that always increases even if data is not saved. Is there a COMMIT I am missing or property that needs to be set?

Here is a more compact version of creating a new Transport_Task that leverages FndPLSQLBaseMethodCall:

try
{
	// It all starts with a connection string. We connect to the IFS Application server, not directly to the Oracle database!
	string connString = "https://<server>:<port>/";
	string userId = <username>;
	string password = <password>;
	// Establish connection. Set CatchExceptions = false because we are handling any errors here with the try..catch.
	FndConnection conn = new FndConnection(connString, userId, password);
	conn.CatchExceptions = false;
	// Use a "base method" call to add a Transport Task (based on example code).
	// FndDataRow must have OBJID and OBJVERSION defined since those values are returned by the base method call.
	FndDataRow newTransportTask = new FndDataRow("TRANSPORT_TASK");
	newTransportTask.Columns.Add("OBJID", FndAttributeType.Text);
	newTransportTask.Columns.Add("OBJVERSION", FndAttributeType.Text);
	// Also add TRANSPORT_TASK_ID so we can get that back.
	newTransportTask.Columns.Add("TRANSPORT_TASK_ID", FndAttributeType.Number);
	// We can now call the NEW method without anything else in the data row because IFS allows an "empty" TT header record.
	FndPLSQLBaseMethodCommand cmd = new FndPLSQLBaseMethodCommand(conn, FndBaseMethodType.New, "TRANSPORT_TASK_API", "New__", newTransportTask, FndBaseMethodAction.Do);
	cmd.ExecuteNonQuery();
	// Get value of TRANSPORT_TASK_ID for the row just added.
	FndNumberAttribute newTransportTaskId = (FndNumberAttribute)newTransportTaska"TRANSPORT_TASK_ID"];
	int newId = Convert.ToInt32(newTransportTaskId.GetValue(-1));
	MessageBox.Show("New Transport Task ID: " + newId.ToString());
}
catch (FndException err)
{
	err.Show();
}

It works exactly the same as the code above it, and I get a new TT ID back, but, again, no data is actually being saved to IFS.

If anyone knows why the data would not be saving, please let me know! I would like to have a full understanding of how the Access Provider works to both read and write data so I can use it more in the future. The Access Provider is pretty cool, especially since it does not require an additional Oracle client layer to be installed (e.g. Oracle Instant Client).

Thanks,

Joe Kaufman

OK, got the data to commit via an explicit command after the New__ call:

FndPLSQLCommand cmdCommit = new FndPLSQLCommand(conn, "COMMIT");
cmdCommit.ExecuteNonQuery();

I assume that commits all pending changes for the connection. I am just not used to having to do an explicit commit, as it is not required when I do my ODBC-based calls. Though, I do wrap those method calls in a BEGIN..END;, so perhaps there is some sort of implicit committing going on that I just don’t understand fully.

@T2S , I am still interested in seeing your working code so that I have learned all I can about the Access Provider in this scenario. This has been a great discussion, so thanks for posting!

Thanks,

Joe Kaufman

Here is the final solution. Crucial rows (also in red) are between commented lines.

        Dim queryStr As String
        Dim IFSconn As New Global.Ifs.Fnd.AccessProvider.FndConnection
        Dim query As New Global.Ifs.Fnd.AccessProvider.PLSQL.FndPLSQLCommand 
        Dim rep_response As New Global.Ifs.Fnd.Data.FndDataTable
        Dim dumy As New Global.Ifs.Fnd.Buffer.FndBuffer
        Dim dumyRows As New Global.Ifs.Fnd.Data.FndDataRowCollection
        Dim dumyRow As New Global.Ifs.Fnd.Data.FndDataRow

        Dim TTid As String

        '----------------------------------------------------------------------------------------------
        Dim OUTPUT As New Global.Ifs.Fnd.Data.FndTextAttribute()
        '----------------------------------------------------------------------------------------------

        IFSconn.ConnectionString = (IfsConStr)
        IFSconn.SetCredentials(userId_, psd_)
        IFSconn.CatchExceptions = False

        queryStr = "DECLARE "
        queryStr = queryStr & "   p0_ VARCHAR2(32000) := NULL; "
        queryStr = queryStr & "   p1_ VARCHAR2(32000) := NULL; "
        queryStr = queryStr & "   p2_ VARCHAR2(32000) := NULL; "
        queryStr = queryStr & "   p3_ VARCHAR2(32000) := 'FIXED_DB'||chr(31)||'TRUE'||chr(30); "
        queryStr = queryStr & "   p4_ VARCHAR2(32000) := 'DO'; "
        queryStr = queryStr & "BEGIN "
        queryStr = queryStr & "IFSAPP.Log_SYS.Init_Debug_Session_('pl'); "
        queryStr = queryStr & "IFSAPP.Transport_Task_API.NEW__( p0_ , p1_ , p2_ , p3_ , p4_ ); "
        queryStr = queryStr & "commit; "
        '----------------------------------------------------------------------------------------------
        queryStr = queryStr & ":OUTPUT := ifsapp.client_sys.get_item_value('TRANSPORT_TASK_ID', p3_); "
        '----------------------------------------------------------------------------------------------
        queryStr = queryStr & "END; "

        'On Error Resume Next
        query.Connection = IFSconn
        query.CommandText = queryStr
        '----------------------------------------------------------------------------------------------
        query.BindVariables.Add(New Ifs.Fnd.AccessProvider.PLSQL.FndBindVariable(Ifs.Fnd.AccessProvider.PLSQL.FndBindVariableDirection.Out, "OUTPUT", OUTPUT))
        '----------------------------------------------------------------------------------------------
        query.ExecuteNonQuery()

TTid = query.BindVariables("OUTPUT").Value.ToString

Piotr,

Thanks for posting the final solution! You should definitely mark it as the answer so others are all the more likely to find it. You got bound variables working with an anonymous PL/SQL block -- that’s fantastic! I had no idea it could work that way, embedded inside a block of code. This seems really powerful, and allows the COMMIT to be added more easily (as opposed to my methodology where a COMMIT needs to be explicitly called against the connection/session -- that still feels wonky to me).

Great work! Now we just need to get you coding in C#…  (Just kidding -- VB.NET works just as well!)

I will definitely know what to search for on this forum the next time I need to run a block of code such as this!

Thanks,

Joe Kaufman