Target Database Table
Script Description and Logic
Pulls order line id, fulfillment cost and shipping cost at the item fulfillment level. Fulfillments from all integrations are included in the primary source table, uos.order_item_fulfillments, and reported by unique order line id, shop id and integration id combination.
A lookup is performed against uos.order_item_fulfillments to return a list of order item fulfillments that have been updated in the last seven days. The order item fulfillments to update are then held in a staging table. From the uos.order_item_fulfillments table a join is made to the order_item_fulfillments_to_update table to lookup what order line fulfillments are pending update. Then a join is made to drp.fulfillment_cost to pull in fulfillment cost, number of items and shipping cost.
The fulfillment cost and shipping cost are calculated by dividing the cost by the number of items. The amounts are summed up to the order line level in case the order line was split shipped. The unique combinations of order line id, shop id, fulfillment cost, shipping cost and number of items are then placed in a staging table in preparation for incremental functionality. Incremental functionality is included such that only the records that require an update or are new get inserted into the final drp.order_item_fulfillment_cost table. The records in staging are compared to those in the final table.
Any fulfillments that match are removed from the final table and updated by way of insertion from staging. The records that appear in staging, but not in the final table are inserted into drp.order_item_fulfillment_cost.
Source Tables Used In Script
|Schema||Table (or Derived Table) Name||Table Type||Purpose|
|drp||fulfillment_cost||database||Obtain fulfillment and shipping cost for each fulfillment|
|N/A||last_sync_date||derived||Obtain last sync date for each record|
To View Larger Image, Right Click and Choose Open Image In New Tab.
Calculated and Derived Fields
|Target Column||Target Column Data Type||Source schema.table||Source Column||Transformation/Logic|
|fulfillment_cost||DECIMAL||drp.fulfillment_cost||fulfillment_cost||Sum(fulfillment cost/num_items) per order_line and __shop_id|
|shipping_cost||DECIMAL||drp.fulfillment_cost||shipping_cost||Sum(shipping cost/num_items) per order_line and __shop_id|
All content © Daasity 2021. Do not copy, share or distribute.