2. Purchase Order

Inter-company Processing: SAMLTD P/O to SAMINC O/E (SAMACCINTCOEXP)

The first integration required for this process extracts the P/O from SAMLTD to export a corresponding O/E Order in SAMINC, with a final step that flags the exported orders to prevent them from being re-exported.

Some setup is required in order to see this process.

This integration has:

1. Read
2. Hierarchy
3. Map
4. Connector
5. Filter
6. Second Map
7. Database Write

Read Transform

This extracts purchase orders from SAMLTD using SQL Query.

The SQL Statement

To denote inter-company orders, a specific vendor is used and must be specified in the query to the database. In these examples, this vendor Id is 'SAMINT'.

The where clause in the query limits the result set to only purchase orders where the EXPORTED optional field is False (i.e. the order has not been exported!).

The SQL query extracts the very basic fields required for the integration:

1. Purchase Order Sequence Number
2. Purchase Order Number
3. Purchase Order Date
4. Purchase Order Line Sequence (below)
5. Item Number
6. Quantity Ordered

When the refresh button is pressed, the records that are imported from SAMLTD will appear in Preview.

The 'Purchase Order Line Sequence' is used to record on which line an item belongs. It will be stored with the line on the O/E Order and will be used in the return transaction to uniquely identify the item to receipt.

Hierarchy Transform

Transforms the flat dataset to a hierarchical dataset consisting of a header and details.

Map Transform

The Map transforms adds two fields to the dataset and modifies the value of another so the order can be imported into SAMINC.

1. DATE
   1. Uses the NumberToDate function to convert the numeric Date value (yyyymmdd format) to a date format which can be imported into Sage300 (dd/mm/yyyy or mm/dd/yyyy depending on your region).
2. CUSTOMER
   1. Set to a static value ‘SAMINT’.
3. ORDNUMBER
   1. An empty field to capture the generated order number used on the audit report.

Sage300 Connector

The Sage300 Connector defines the mapping of the data into the SAMINC O/E Order.

1. In the Field Mapping tab, we map each of the fields in our data set.

2. The Order Details transaction's PO Line Sequence is mapped to the POLINE optional field.
   1. As stated previously, this value will be used in the return transaction to identify the record in the receipt.

3. The resulting preview will look something like:

4. See the result! The O/E Order appears in SAMINC:

5. With the POLINE optional field:

Filter Transform

The next three steps flag the SAMLTD Purchase Order to indicate it has been processed and prevent it from being exported again.

This Filter step is optional and would depend on the logic desired, but in this example we want to prevent orders which cannot be imported (for whatever reason) from flagging the EXPORTED. I.e. The order will continue to be exported from SAMLTD until it can be imported into SAMINC or until the EXPORTED optional field on the Purchase Order is manually set to True.

Second Map Transform

In this transform a field (VALUE) is added to the dataset which will enable the EXPORTED optional field to be set to True (1).

Database Writer

This step performs the update to the EXPORTED optional field.

The Options tab specifies the database to update and the type of SQL Operation to perform.

1. The Field Mapping tab is where the Update operation is setup:
   1. Map To Table
      1. Updating the Purchase Order Header Optional Fields Table.
   2. Where Clause
      1. This property specifies the Where clause used in the SQL Update command.
      2. Here we limit the records being updated to a single record by specifying the OPTFIELD field and then parameterising clause with the Purchase Order Header Sequence field (PORHSEQ), forming the primary key on the POPORHO table.
   3. Value
      1. Tells the operation to update the Value field to true (i.e. the item has been exported).

2. After the update is run, the EXPORTED optional field on the Purchase Order should be set to True.