Modelling Order Fulfillment Business Process with BPMN 2.0
For my company ZS Trading I had modeled my own Business Process for Order Fulfillment with the Fulfillment by Amazon (FBA) service, which I had conceived and specified along the enabling possiblities of Amazon Market Web Service (MWS) a few years ago.
For this purpose I applied my BPMN 2.0 skills in the business process modelling, which is short for the Business Process Model and Notation standard which is an ISO standard for nearly 10 years already.
The Process starts with the compliation of customer orders from the ERP system that were earmarked by a meta process to be fulfilled with the FBA service in form of an XML file.
In a second step those order positions part numbers are collected and queried against existing SKUs in a MySQL database, which in return will be matched with live inventory data that is available for fulfillment right away along with inventory health data (storage durations).
As a last step shown in more detail is the process step Decide on SKUs for fulfillment which list all thta data and enables the user to take an informed decision.
Sample Screen from Step two of the process Query SKUs towards Decide on SKUs for fulfillment:
Below is the process implemented in PHP 7 code which shows the relevant functions and processes with code omitted for better readability. The part of the code is shown where SKUs get queried for part number to be fulfilled in a two step drilled down process:
First part is the SQL query which skus belong to a part number which are to be collected. Second part is the query of SKUs with Amazon’s Market Web Service (MWS) and Fulfillment by Amazon (FBA) live inventory data via REST API.
In the screen above the user has the choice between all the SKUs listed, where as only SKU X10-2018-DE has got stock ready to be shipped. This corresponds to the process step Decide on SKUs for fulfillment.
from IPython.display import HTML
HTML(filename="/blog/modelling-business-process/Fulfillment-Code.html")
<?php
// code omitted
// ............
// code omitted
$serviceUrl2 =
"https://mws-eu.amazonservices.com/FulfillmentOutboundShipment/2010-10-01";
$serviceUrl1 =
"https://mws-eu.amazonservices.com/FulfillmentInventory/2010-10-01";
$config2 = [
"ServiceURL" => $serviceUrl2,
"ProxyHost" => null,
"ProxyPort" => -1,
"ProxyUsername" => null,
"ProxyPassword" => null,
"MaxErrorRetry" => 3,
];
$config1 = [
"ServiceURL" => $serviceUrl1,
"ProxyHost" => null,
"ProxyPort" => -1,
"ProxyUsername" => null,
"ProxyPassword" => null,
"MaxErrorRetry" => 3,
];
$service2 = new FBAOutboundServiceMWS_Client(
AWS_ACCESS_KEY_ID,
AWS_SECRET_ACCESS_KEY,
$config2,
APPLICATION_NAME,
APPLICATION_VERSION
);
$service1 = new FBAInventoryServiceMWS_Client(
AWS_ACCESS_KEY_ID,
AWS_SECRET_ACCESS_KEY,
$config1,
APPLICATION_NAME,
APPLICATION_VERSION
);
// code omitted
// ............
// code omitted
echo "<h4>ERP order no # " .
$_SESSION[$sessionstring]["SellerFulfillmentOrderId"] .
"</h4>";
// code omitted
// ............
// code omitted
for ($z = 0; $z < count($xml["tBestellung"][$x]["twarenkorbpos"]); $z++) {
echo "pos count: " . count($xml["tBestellung"][$x]["twarenkorbpos"]);
echo "<div id='expand'>";
echo "<pre>";
print_r($xml);
echo "</pre>";
echo "</div>";
$ignoredartnos = ["0001", "00001", "0002", "00002", "1162"];
$tobecheckedartno = $xml["tBestellung"][$x]["twarenkorbpos"][$z]["cArtNr"];
if (
in_array($tobecheckedartno, $ignoredartnos) ||
mb_substr($tobecheckedartno, 0, 2) == "90"
) {
// only real items!!
echo "Order line item ID: " .
($z + 1) .
" with artno " .
$tobecheckedartno .
" is discarded.<br>########################
<br>";
continue;
}
if (
isset(
$xml["tBestellung"][$x]["twarenkorbpos"][$z][
"twarenkorbposeigenschaft"
]
)
) {
$_SESSION[$sessionstring]["Items.member"][$z]["artno"] =
$xml["tBestellung"][$x]["twarenkorbpos"][$z][
"twarenkorbposeigenschaft"
]["cArtNr"];
$sql =
"SELECT sku FROM " .
$tname .
" WHERE vanr = '" .
$_SESSION[$sessionstring]["Items.member"][$z]["artno"] .
"'";
//echo $sql;
} else {
$_SESSION[$sessionstring]["Items.member"][$z]["artno"] =
$xml["tBestellung"][$x]["twarenkorbpos"][$z]["cArtNr"];
$sql =
"SELECT sku FROM " .
$tname .
" WHERE artnr = '" .
$_SESSION[$sessionstring]["Items.member"][$z]["artno"] .
"'";
}
echo "Order line item ID: " . ($z + 1) . "<br> ";
//query fba-sku for the artno (reverse mapping)
$q = $pdo->query($sql);
$q->setFetchMode(PDO::FETCH_ASSOC);
$skuresult = $q->fetchAll();
// no FBA sku found
if ($q->rowCount() == 0) {
echo "<span style='color:red;'><b>No FBA SKU for " .
$_SESSION[$sessionstring]["Items.member"][$z]["artno"] .
" found! A complete fulfillment order is acc. to mapping data not possible. If a FBA SKU for this article exists, it needs to be maintained in the mapping Database.</b></span><br>########################
<br>";
continue;
}
$skuresultflat = array_column($skuresult, "sku");
$skuresultflat = array_flatten($skuresultflat);
$skuresultflat = array_values($skuresultflat);
// code omitted
// ............
// code omitted
$_SESSION[$sessionstring]["Items.member"][$z]["allskus"] = $skuresult;
// get fba stock for each sku through MWS REST API
$request = new FBAInventoryServiceMWS_Model_ListInventorySupplyRequest();
$request->setSellerId(MERCHANT_ID);
$request->setMarketplace(MARKETPLACE_ID);
$skus = new FBAInventoryServiceMWS_Model_SellerSkuList();
$skus->setmember($skuresultflat);
$request->setSellerSkus($skus);
// object or array of parameters
invokeListInventorySupply($service1, $request);
echo "inventory call finsihed";
// qty
$_SESSION[$sessionstring]["Items.member"][$z]["qty"] = round(
$xml["tBestellung"][$x]["twarenkorbpos"][$z]["fAnzahl"],
0
);
echo "<br><b>Ordered Quantity: " .
$_SESSION[$sessionstring]["Items.member"][$z]["qty"] .
"</b><br>";
//SellerFulfillmentOrderItemId
$_SESSION[$sessionstring]["Items.member"][$z][
"SellerFulfillmentOrderItemId"
] = $erporder . "-" . $z;
//RNo check
$RNo = $order["@attributes"]["cRechnungsNr"];
if ($RNo == "") {
echo "<h3 style='color:red;'>Warning: No Invoice Number detected! Invoice created at all for " .
$auftragsno .
"? <b>Strictly</b> no Fufillment-Order allowed without created ERP invoice!</h3>";
}
// Form 2 mainpart- SKU Selection
//echo "ERP Auftrag #:". $_SESSION[$sessionstring]['SellerFulfillmentOrderId']."<br>";
echo "Ext bestell #:" .
$_SESSION[$sessionstring]["DisplayableOrderId"] .
"<br>";
echo "Lieferadresse Name #:" .
$_SESSION[$sessionstring]["DestinationAddress.Name"] .
"<br>";
echo "artno/vanr: <b>" .
$_SESSION[$sessionstring]["Items.member"][$z]["artno"] .
"</b>: <br>";
echo " Choose FBA sku. LIVE FBA DATA[sku | total | instock | availability]:<select name='" .
$_SESSION[$sessionstring]["Items.member"][$z][
"SellerFulfillmentOrderItemId"
] .
"' id='" .
$_SESSION[$sessionstring]["Items.member"][$z][
"SellerFulfillmentOrderItemId"
] .
"'>";
sort_array_of_array(
$_SESSION[$sessionstring]["Items.member"][$z]["allskus"],
"InStockSupplyQuantity"
);
// skus iteration
for (
$y = 0;
$y < count($_SESSION[$sessionstring]["Items.member"][$z]["allskus"]);
$y++
) {
echo "<option>" .
$_SESSION[$sessionstring]["Items.member"][$z]["allskus"][$y][
"sku"
] .
" | " .
$_SESSION[$sessionstring]["Items.member"][$z]["allskus"][$y][
"TotalSupplyQuantity"
] .
" | " .
$_SESSION[$sessionstring]["Items.member"][$z]["allskus"][$y][
"InStockSupplyQuantity"
] .
" | " .
$_SESSION[$sessionstring]["Items.member"][$z]["allskus"][$y][
"EarliestAvailability"
] .
"</option>";
}
echo "</select><br>";
// screen output for user
// print Inventory health information for each sku for optimized choice of fulfillment sku
// inventory health report array
$IHreport = [];
$fp = fopen("DE-invent-health.txt", "r");
if (($headers = fgetcsv($fp, 0, "\t")) !== false) {
if ($headers) {
while (($line = fgetcsv($fp, 0, "\t")) !== false) {
if ($line) {
if (sizeof($line) == sizeof($headers)) {
$IHreport[] = array_combine($headers, $line);
}
}
};
}
}
fclose($fp);
//echo '<pre>' . var_export($IHreport, true) . '</pre>';die;
for (
$y = 0;
$y < count($_SESSION[$sessionstring]["Items.member"][$z]["allskus"]);
$y++
) {
$ltf12feeqty = "";
$ltf6feeqty = "";
$age0 = "";
$age91 = "";
$age181 = "";
$age271 = "";
$age365 = "";
for ($i = 0; $i < count($IHreport); $i++) {
if (
$IHreport[$i]["sku"] ==
$_SESSION[$sessionstring]["Items.member"][$z]["allskus"][$y][
"sku"
]
) {
$ltf12feeqty = $IHreport[$i]["qty-to-be-charged-ltsf-12-mo"];
$ltf6feeqty = $IHreport[$i]["qty-to-be-charged-ltsf-6-mo"];
$age0 = $IHreport[$i]["inv-age-0-to-90-days"];
$age91 = $IHreport[$i]["inv-age-91-to-180-days"];
$age181 = $IHreport[$i]["inv-age-181-to-270-days"];
$age271 = $IHreport[$i]["inv-age-271-to-365-days"];
$age365 = $IHreport[$i]["inv-age-365-plus-days"];
}
}
if (
in_array(
$_SESSION[$sessionstring]["Items.member"][$z]["allskus"][$y][
"sku"
],
$excessSKU
)
) {
echo "<br><span style='color:red;'>" .
$_SESSION[$sessionstring]["Items.member"][$z]["allskus"][$y][
"sku"
] .
" | " .
$_SESSION[$sessionstring]["Items.member"][$z]["allskus"][$y][
"TotalSupplyQuantity"
] .
" | " .
$_SESSION[$sessionstring]["Items.member"][$z]["allskus"][$y][
"InStockSupplyQuantity"
] .
" | " .
$_SESSION[$sessionstring]["Items.member"][$z]["allskus"][$y][
"EarliestAvailability"
] .
" | Excess SKU!</span>" .
" | LTF 12 months fee qty: " .
$ltf12feeqty .
" | LTF 6 months fee qty: " .
$ltf6feeqty .
" | Age (90|180|270|365|365+) qty: " .
$age0 .
" | " .
$age91 .
" | " .
$age181 .
" | " .
$age271 .
" | " .
$age365;
} elseif (
$_SESSION[$sessionstring]["Items.member"][$z]["allskus"][$y][
"sku"
] == "DE-WP444-FBA"
) {
echo "<br><span style='color:blue;'>" .
$_SESSION[$sessionstring]["Items.member"][$z]["allskus"][$y][
"sku"
] .
" | " .
$_SESSION[$sessionstring]["Items.member"][$z]["allskus"][$y][
"TotalSupplyQuantity"
] .
" | " .
$_SESSION[$sessionstring]["Items.member"][$z]["allskus"][$y][
"InStockSupplyQuantity"
] .
" | " .
$_SESSION[$sessionstring]["Items.member"][$z]["allskus"][$y][
"EarliestAvailability"
] .
" | Sonderfall SKU</span>" .
" | LTF 12 months fee qty: " .
$ltf12feeqty .
" | LTF 6 months fee qty: " .
$ltf6feeqty .
" | Age (90|180|270|365|365+) qty: " .
$age0 .
" | " .
$age91 .
" | " .
$age181 .
" | " .
$age271 .
" | " .
$age365;
} else {
// regular sku
echo "<br>" .
$_SESSION[$sessionstring]["Items.member"][$z]["allskus"][$y][
"sku"
] .
" | " .
$_SESSION[$sessionstring]["Items.member"][$z]["allskus"][$y][
"TotalSupplyQuantity"
] .
" | " .
$_SESSION[$sessionstring]["Items.member"][$z]["allskus"][$y][
"InStockSupplyQuantity"
] .
" | " .
$_SESSION[$sessionstring]["Items.member"][$z]["allskus"][$y][
"EarliestAvailability"
] .
" | LTF 12 months fee qty: " .
$ltf12feeqty .
" | LTF 6 months fee qty: " .
$ltf6feeqty .
" | Age (90|180|270|365|365+) qty: " .
$age0 .
" | " .
$age91 .
" | " .
$age181 .
" | " .
$age271 .
" | " .
$age365;
}
}
echo "<br>" .
count($_SESSION[$sessionstring]["Items.member"][$z]["allskus"]) .
" distinct FBA SKUs for " .
$_SESSION[$sessionstring]["Items.member"][$z]["artno"];
echo "<br>########################<br>";
}
// code omitted
// ............
// code omitted
// MWS SDK functions
// code omitted
// ............
// code omitted
function invokeListInventorySupply(
FBAInventoryServiceMWS_Interface $service,
$request
) {
//$x lineitemid
try {
$response = $service->ListInventorySupply($request);
// use global values outside this function
global $z, $erporder, $sessionstring;
//echo "x: ".$x." ERPorder variable: ".$erporder."<br>";
echo "sessionstring: " . $sessionstring . "<br>";
//echo ("Service Response\n");
//echo ("=============================================================================\n");
$xmlstring = $response->toXML();
//Three line xml2array:
$xml = simplexml_load_string($xmlstring);
$json = json_encode($xml);
$array = json_decode($json, true);
$inventory = $array["ListInventorySupplyResult"]["InventorySupplyList"];
// normalize amzn api array
if (!isset($inventory["member"][0])) {
// list has only single sku
$rebuild = [];
//Check to see if 'properties' is only one, if it
//is then wrap it in an array of its own.
if (
is_array($inventory["member"]) &&
!isset($inventory["member"][0])
) {
//Only one propery found, wrap it in an array
$rebuild["member"] = [$inventory["member"]];
}
//echo '################################################';
$inventory["member"] = $rebuild["member"];
echo "sku list member normalized <br>";
}
//foreach($inventory as $member){
for ($y = 0; $y < count($inventory["member"]); $y++) {
// add new array for line item ['Items.member'][]
$_SESSION[$sessionstring]["Items.member"][$z]["allskus"][$y][
"TotalSupplyQuantity"
] = $inventory["member"][$y]["TotalSupplyQuantity"];
$_SESSION[$sessionstring]["Items.member"][$z]["allskus"][$y][
"InStockSupplyQuantity"
] = $inventory["member"][$y]["InStockSupplyQuantity"];
$_SESSION[$sessionstring]["Items.member"][$z]["allskus"][$y][
"EarliestAvailability"
] =
$inventory["member"][$y]["EarliestAvailability"][
"TimepointType"
];
}
echo "ResponseHeaderMetadata: " .
$response->getResponseHeaderMetadata() .
"\n";
} catch (FBAInventoryServiceMWS_Exception $ex) {
echo "Caught Exception: " . $ex->getMessage() . "\n";
echo "Response Status Code: " . $ex->getStatusCode() . "\n";
echo "Error Code: " . $ex->getErrorCode() . "\n";
echo "Error Type: " . $ex->getErrorType() . "\n";
echo "Request ID: " . $ex->getRequestId() . "\n";
echo "XML: " . $ex->getXML() . "\n";
echo "ResponseHeaderMetadata: " .
$ex->getResponseHeaderMetadata() .
"\n";
}
return;
}
/// #######################
// code omitted
// ............
// code omitted
function array_flatten($array = null)
{
$result = [];
if (!is_array($array)) {
$array = func_get_args();
}
foreach ($array as $key => $value) {
if (is_array($value)) {
$result = array_merge($result, array_flatten($value));
} else {
$result = array_merge($result, [$key => $value]);
}
}
return $result;
}
function sort_array_of_array(&$array, $subfield)
{
$sortarray = [];
foreach ($array as $key => $row) {
$sortarray[$key] = $row[$subfield];
}
array_multisort($sortarray, SORT_DESC, $array);
}
// code omitted
// ............
// code omitted
?>