GP SOP or POP Transaction Example for Scribe Insight

Creating a Sales and Purchase Order in GP requires some special handing when using TIBCO Scribe® Insight.
The main reason is that detail/transaction lines need to be created prior to the header record in GP.

Sales Order or Purchase Order transactions consist of a single Header entry and one or more Detail line entries.
What is unique to GP is that Detailed lines are entered first and the Header entry is created last. This is a 2 stage process.

In the DTS, there are steps that are only triggered once per transaction/sales order (e.g. Obtaining GP Document Number, Header transaction) and steps that are executed for each record (Detail line entry). Because of the way GP handles these types of transactions, Flow Control within the DTS is crucial. For example:

• GP Document number must only be retrieved when the first source record is processed
• Detail lines must be entered first and have the proper sequence number associated with them
• You must know when the last detail line has been processed so the Header record can be written
• Everything must be rolled back if an error occurs otherwise there will be orphaned records in GP

 Any steps other than the Header step that is executed once per transaction should be at the beginning and use Skip if Repeated. The rules for Skip if Repeated are it must start at the first step and be contiguous (but does not need to be on every step).

In this example the first two steps only need to be executed once for each sales order and this is controlled by the OrderID field.


 
The DTS also uses the Commit Repeating Data Together option so that if there is a failure for the transaction, the entire sales order is rolled back. This works in conjunction with Skip if Repeated.
 


 
Finally the Header step needs to be executed when the last sales order line is processed. A Pre-Operation Step Flow formula is used to determine if the source row is the last row for the transaction. You must have a source field that indicates when you are on the last source row for the transaction.



If the source is SQL based you can add a column to calculate that as shown in the following example.



The same concept can be used with Text files (but does have limitations) when using the Scribe Text ODBC Driver.

Example Formula
SELECT *, (SELECT COUNT(StoreID) FROM GLData where StoreID = g.StoreID) AS ROWCOUNT FROM GLData g

This example is used when there are spaces in the field names. Need to use the ` character from the tilde key on the keyboard.
SELECT *, (SELECT COUNT(`ExpensAble Track#`) FROM ExpensableImportFile01 where `ExpensAble Track#` = g.`ExpensAble Track#`) AS ROWCOUNT FROM ExpensableImportFile01 g
 

This article discusses the basic steps for creating a DTS for GP SOP or POP transactions in TIBCO Scribe® Insight