Skip to content

3. Shipment to Receipt

Inter-company Processing: SAMINC O/E Shipment to SAMLTD P/O Receipt (SAMACCINTCOIMP)

The second of the two intercompany integrations makes the return journey. It reads shipments out of SAMINC, creates the matching P/O Receipt in SAMLTD, and then flags the shipment so it is not exported again.

It is the same seven-step shape as step 2, with the two companies the other way round: SAMINC is now the source and SAMLTD the target.

Both companies must exist in Sage 300 first

Set up both companies in Sage 300 before you run this integration. See Company setup if you have not done so.

Seven nodes joined left to right on the design surface: a database reader, a hierarchy, a map, a Sage 300 connector, a filter, a second map and a database writer

This integration has:

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

Raise the shipment in SAMINC

Before running it, create a shipment in SAMINC against the intercompany order that step 2 created.

Ship less than was ordered on at least one line. The receipt takes its quantities from the shipment, not the purchase order. If you ship everything, the receipt looks the same either way and you cannot see the difference.

Sage 300's O/E Shipment Entry window in SAMINC, showing a shipment against customer SAMINT with the originating order and PO numbers and two item lines

Read

A SQL query reads the shipments straight from the SAMINC database.

The database reader's Setup tab: Database Connection set to Sample Sage300 SAMINC Connection, a SQL Statement box holding the query, and a Query Timeout of 30 seconds

The SQL statement

select H.SHIUNIQ, H.ORDNUMBER, H.SHINUMBER, H.PONUMBER, D.ITEM, D.QTYSHIPPED, OD.VALUE as 'POLINENUM'
from OESHIH H inner join OESHID D on H.SHIUNIQ = D.SHIUNIQ
inner join OESHIHO OH on H.SHIUNIQ = OH.SHIUNIQ and OH.OPTFIELD = 'EXPORTED'
inner join OESHIDO OD on D.SHIUNIQ = OD.SHIUNIQ and D.LINENUM = OD.LINENUM and OD.OPTFIELD = 'POLINE'
where LTRIM(OH.VALUE) = '0' and CUSTOMER = 'SAMINT'

It mirrors step 2's query, and selects an outstanding intercompany shipment the same way:

  • CUSTOMER = 'SAMINT' — intercompany sales are raised against a particular customer, which distinguishes them from ordinary ones.
  • LTRIM(OH.VALUE) = '0' — the EXPORTED optional field on the shipment is still No. The join onto OESHIHO brings that optional field into the query.

This query has a second optional-field join, which step 2's query does not. It joins OESHIDO per line, on OPTFIELD = 'POLINE', to put the purchase order line sequence back on each row:
SHIUNIQ shipment sequence number — the key the flag is written back against
ORDNUMBER the SAMINC order number
SHINUMBER the SAMINC shipment number
PONUMBER the SAMLTD purchase order number — how the receipt finds its order
ITEM item number
QTYSHIPPED the quantity actually shipped
POLINENUM the POLINE optional field, written onto the order line by step 2

Press Refresh to run the query. One shipment of two lines, shipped short on both, returns this:

SHIUNIQ ORDNUMBER SHINUMBER PONUMBER ITEM QTYSHIPPED POLINENUM
3073 ORD000000000086 SH00000000000000000077 PO000000242 A1-103/0 8.0000 37536
3073 ORD000000000086 SH00000000000000000077 PO000000242 A1-401/0 5.0000 37664

POLINENUM is the value step 2 wrote onto the O/E Order line. It has travelled order → shipment → here, and it lets the receipt put the right quantity on the right line.

Hierarchy

The query returns one row per shipment line. The Hierarchy transform turns those flat rows into the header-and-lines shape the P/O Receipt import expects.

The Hierarchy transform's Field Mapping tab: Transaction Id to Hierarchise set to Shipments, a Hierarchy tree showing Shipments marked root with ShipLines beneath it, and a field grid where SHIUNIQ, ORDNUMBER, SHINUMBER and PONUMBER are ticked for import, SHIUNIQ carries Key 1, and ITEM, QTYSHIPPED and POLINENUM are unticked

SHIUNIQ is Key 1, so the transform builds one Shipments record per shipment. The four header fields are ticked for import. The three line-level fields are unticked and stay on the ShipLines child.

Map

The Map transform adds a single empty field.

The Map transform's Field Mapping tab on the Shipments transaction: SHIUNIQ, ORDNUMBER, SHINUMBER and PONUMBER carried through, and RECEIPTNBR added with no value

RECEIPTNBR is added empty. It gives the connector a field to write back the receipt number Sage 300 generates. The filter below tests it, and after a run it tells you which receipt each shipment produced. ORDNUMBER plays the same role in step 2.

Sage 300 connector

The connector defines how the dataset maps to the SAMLTD P/O Receipt.

The Sage 300 connector's Setup tab: Sage 300 Connector set to Sage 300 SAMLTD training company, Sage 300 Import Type set to P/O Receipt, and Update Operation set to Insert

The connector named here is the SAMLTD System Connector. Everything before it read from SAMINC, and the database writer at the end goes back to SAMINC.

Field Mapping

Transaction Level Mapping pairs the two transactions the hierarchy built with the two Sage 300 views the import type expects, PO0700 for the receipt header and PO0710 for the lines.

The connector's Field Mapping tab on the Shipments transaction: a Transaction Level Mapping tree reading Shipments to PO0700 with ShipLines to PO0710 beneath it, and a field grid pairing ORDNUMBER with Description (DESCRIPTIO), SHINUMBER with Reference (REFERENCE), PONUMBER with Purchase Order Number (PONUMBER), RECEIPTNBR with Receipt Number (RCPNUMBER), and SHIUNIQ with (not mapped)

PONUMBER identifies the purchase order to receipt against. It is mapped to the receipt's Purchase Order Number field, the same field you fill in when you enter a receipt by hand in Sage 300.

ORDNUMBER and SHINUMBER are mapped to Description and Reference, so the receipt in SAMLTD records which SAMINC order and shipment produced it.

SHIUNIQ is (not mapped), because SAMLTD does not need SAMINC's internal sequence number. The database writer at the end of the integration does need it, and only mapped fields flow through a connector. Its Log Key of 20 carries it through. A non-zero Log Key makes an unmapped field available to downstream transforms, and a non-sequential one keeps it out of the audit report's Source column. See Making unmapped fields flow.

On the ShipLines transaction:

The connector's Field Mapping tab on the ShipLines transaction: POLINENUM mapped to Purchase Order Line Sequence (PORLSEQ), QTYSHIPPED to Quantity Received (RQRECEIVED), PONUMBER to Purchase Order Number (PONUMBER), and ITEM and SHIUNIQ not mapped

Two mappings matter here:

  • POLINENUM → Purchase Order Line Sequence (PORLSEQ). This mapping matches each shipment line to the right line of the purchase order.
  • QTYSHIPPED → Quantity Received (RQRECEIVED). The receipt takes the quantity that was shipped, not the quantity that was ordered.

ITEM is (not mapped) too, for a different reason. The purchase order line sequence already identifies the line, so the item number would be redundant on the receipt. ITEM carries Log Key 2 and PONUMBER carries 1. Because the pair is sequential, the two name the failing line in the audit report's Source column if the import fails.

Running it

Refresh does not simulate the import. It runs it. When it finishes, RCPNUMBER holds the receipt number Sage 300 generated:

The connector's preview after a refresh: one PO0700 row with SHIUNIQ 3073, the order number in DESCRIPTIO, the shipment number in REFERENCE, PONUMBER PO000000242 and RCPNUMBER RCP00000113

The receipt is in SAMLTD. Description and Reference hold the O/E Order and O/E Shipment numbers from SAMINC, and each line is receipted for the quantity shipped, not the quantity ordered:

Sage 300's P/O Receipt Entry window in SAMLTD, showing a receipt against vendor SAMINT with the originating PO number, the order number as its Description, the shipment number as its Reference, and two lines with their received quantities

Filter, Second Map and Database Write

The last three steps flag the SAMINC shipment as exported so it is not receipted twice. They are the same three steps as in step 2, pointed at the other company. This page shows only what differs.

The Filter keeps only the shipments that produced a receipt:

The Filter transform's Field Mapping tab: Current Transaction Id set to PO0700 and a Record Evaluation of %RCPNUMBER <> "", with the syntax check reporting No Errors Found

The second Map adds VALUE, set to 1, the Yes of a Sage 300 Yes/No optional field:

The Map transform's Field Mapping tab on the PO0700 transaction: the fields carried through from the connector, and VALUE added as Text with the value 1

The transactions are now called PO0700 and PO0710. The connector renamed them to the Sage 300 views it mapped them to, and every transform downstream of a connector uses the new names.

The database writer runs the update. Its connection is back to SAMINC, because this is the return leg. The operation is Update, because the EXPORTED optional field already exists on the shipment and the writer changes its value from No to Yes.

The database writer's Setup tab: Database Connection set to Sample Sage300 SAMINC Connection, SQL Operation set to Update, Commit Transaction set to (none) and a Batch Size of 0

The writer's Field Mapping tab: Map To Table set to OESHIHO, a Where Clause of SHIUNIQ = %SHIUNIQ and OPTFIELD = 'EXPORTED', an Output tree reading PO0700 to OESHIHO with PO0710 not mapped, and a field grid in which only VALUE is given a column

  • Map To Table — OESHIHO, the O/E shipment header optional fields table.
  • Where Clause — SHIUNIQ = %SHIUNIQ and OPTFIELD = 'EXPORTED'. Together those two columns are the primary key of OESHIHO, so the writer updates exactly one row. %SHIUNIQ is a merge field that takes its value from the record being written. The connector carried SHIUNIQ along unmapped for this reason.
  • Column — only VALUE is given one, so VALUE is the only column the update sets.

The advisory under the Where Clause, and why the update still runs

Version 6.1 shows an advisory beneath the Where Clause. It says that %SHIUNIQ is an unqualified field reference and that merge fields are written %[Record.Field]. The clause still resolves and the update still runs. Use the qualified form in anything you write now.

After the update, the EXPORTED optional field on the shipment reads Yes, and the next run of the integration will not pick it up. The round trip, from purchase order to sales order and from shipment to receipt, is complete.