https://app.asana.com/0/1161712892239265/1202764423803060
Customer is saying that Distru update their LL product quantity to zero and listing status to unavailable, even though there is quantity in Distru.
Let's take one of the products mentioned in the ticket "Pharmicated - Pre-Rolls (Flower) - Alien OG - 1g" (ID: 620527) and look at what LL product it's associated to:
SELECT leaflink_product_id FROM products WHERE id = 620527;
-[ RECORD 1 ]-------+--------
leaflink_product_id | 1034472
Now lets take a look at our cache for LL product 1034472, we get 1 record back:
SELECT * FROM leaflink_product_caches WHERE leaflink_id = 1034472;
id | 127569
company_id | 373773
leaflink_id | 1034472
payload | {"id": 1034472, "sku": "Batch#: 220215GRPALOG", "name": "PhD 1g Pre-Roll (25 Count) - Alien OG", "brand": 2437, "parent": null, "seller": 9238, "dropped": null, "license": {"id": 10135, "type": "Medical", "number": "PAAA-4YD0-6CTM", "display_type": "Medical", "classification": "Processor"}, "strains": [17559], "tagline": null, "category": 14, "featured": false, "modified": "2022-07-22T11:38:10.033293-06:00", "quantity": "0.0000", "drop_date": null, "grow_type": null, "last_edit": "2022-08-09T07:48:25.217076-06:00", "created_on": "2022-05-13T12:18:23.380831-06:00", "sale_price": {"amount": "0.00", "currency": "USD"}, "description": "<p style=\"\"><span class=\"TextRun SCXW186380196 BCX0\" style=\"\">Pharmicated</span><span class=\"TextRun SCXW186380196 BCX0\" style=\"\"> pre-rolls are our flagship product and is widely lauded as the best quality at and above its price point throughout Oklahoma! In order to ensure the continuance of our reputation for providing consistently high-quality joints, we </span><span class=\"TextRun SCXW186380196 BCX0\" style=\"\">hand-pack triple-screened material into all our pre-rolls </span><span class=\"TextRun SCXW186380196 BCX0\" style=\"\">providing high value to our dispensary partners and consistent quality medicine to the patients who choose our products.</span><span class=\"EOP SCXW186380196 BCX0\" style=\"\"> </span><br style=\"\"></p><ul style=\"\"><li style=\"\">1 Unit = 25 pre-rolls</li><li style=\"\">Check back often as we release new strains daily!</li><li style=\"\">Don't forget our 5-packs!</li></ul><p><br></p><p><br></p><p> <br></p>", "image_count": 2, "display_name": "PhD 1g Pre-Roll (25 Count) - Alien OG", "external_ids": {}, "is_ancillary": false, "manufacturer": 9238, "product_line": 11483, "reserved_qty": "0.0000", "retail_price": {"amount": "5.00", "currency": "USD"}, "sub_category": 25, "listing_state": "Unavailable", "maximum_order": null, "minimum_order": "25.0000", "show_quantity": false, "children_count": 0, "extern_acct_id": null, "extern_sts_ids": [], "threshold_value": 0, "unit_multiplier": 25, "unit_of_measure": "Unit", "wholesale_price": {"amount": "62.50", "currency": "USD"}, "discount_percent": 0, "threshold_action": "unavailable", "featured_on_deals": false, "unit_denomination": {"id": 1, "name": "One", "label": "1", "value": "1.00000"}, "cost_of_goods_sold": null, "product_data_items": [{"spec_type": "thc", "text_value": "", "dosage_unit": "%", "decimal_value": "24.66", "max_decimal_value": "0.00"}, {"spec_type": "cannabinoids", "text_value": "", "dosage_unit": "%", "decimal_value": "28.65", "max_decimal_value": "0.00"}, {"spec_type": "terpenes", "text_value": "", "dosage_unit": "%", "decimal_value": "1.21", "max_decimal_value": "0.00"}], "available_inventory": "0", "base_units_per_unit": "1.000000000000000", "inventory_management": 1, "is_medical_line_item": false, "price_schedule_price": {"amount": "62.50", "currency": "USD"}, "AVAILABLE_FOR_SAMPLES": false, "display_listing_state": "Unavailable", "extern_income_acct_id": null, "s2s_conversion_amount": "1.0000", "strain_classification": "hybrid", "reverse_threshold_value": null, "sell_in_unit_of_measure": "Case", "reverse_threshold_action": "default", "allow_fractional_quantities": false, "has_deals_eligible_children": false, "track_batch_level_inventory": false, "low_inventory_alert_threshold": "100.0000", "should_alert_on_low_inventory": false, "strain_classification_display": "Hybrid"}
fetched_at | 2022-08-09 13:48:40.555288
inserted_at | 2022-06-08 21:21:58.22367
updated_at | 2022-08-09 13:48:40.555288
However, when I open the product form in the UI, I notice that we have a warning banner in the LL tab that says "The SKU has changed in LeafLink and is now different than the Distru product SKU." -- this leads me to think that the user created another version of this product. Let's see if we can find it using the same name:
SELECT * FROM leaflink_product_caches WHERE leaflink_id != 1034472 AND payload->>'name' = 'PhD 1g Pre-Roll (25 Count) - Alien OG' AND company_id = 373773;
We indeed do find a duplicate product:
id | 127570
company_id | 373773
leaflink_id | 938581
payload | {"id": 938581, "sku": "Batch#: 2202HTL0577.2432", "name": "PhD 1g Pre-Roll (25 Count) - Alien OG", "brand": 2437, "parent": null, "seller": 9238, "dropped": null, "license": {"id": 10135, "type": "Medical", "number": "PAAA-4YD0-6CTM", "display_type": "Medical", "classification": "Processor"}, "strains": [17559], "tagline": null, "category": 14, "featured": false, "modified": "2022-04-06T09:32:51.919447-06:00", "quantity": "0.0000", "drop_date": null, "grow_type": null, "last_edit": "2022-06-17T08:13:57.414455-06:00", "created_on": "2022-03-02T15:41:29.735940-07:00", "sale_price": {"amount": 0.0, "currency": "USD"}, "coming_soon": false, "description": "<p style=\"\"><span class=\"TextRun SCXW186380196 BCX0\" style=\"\">Pharmicated</span><span class=\"TextRun SCXW186380196 BCX0\" style=\"\"> pre-rolls are our flagship product and is widely lauded as the best quality at and above its price point throughout Oklahoma! In order to ensure the continuance of our reputation for providing consistently high-quality joints, we </span><span class=\"TextRun SCXW186380196 BCX0\" style=\"\">hand-pack triple-screened material into all our pre-rolls </span><span class=\"TextRun SCXW186380196 BCX0\" style=\"\">providing high value to our dispensary partners and consistent quality medicine to the patients who choose our products.</span><span class=\"EOP SCXW186380196 BCX0\" style=\"\"> </span><br style=\"\"></p><ul style=\"\"><li style=\"\">1 Unit = 25 pre-rolls</li><li style=\"\">Check back often as we release new strains daily!</li><li style=\"\">Don't forget our 5-packs!</li></ul><p><br></p><p><br></p><p> <br></p>", "image_count": 2, "display_name": "PhD 1g Pre-Roll (25 Count) - Alien OG", "external_ids": {}, "is_ancillary": false, "manufacturer": 9238, "product_line": 11483, "reserved_qty": "0.0000", "retail_price": {"amount": 5.0, "currency": "USD"}, "sub_category": 25, "listing_state": "Archived", "maximum_order": null, "minimum_order": "1.0000", "show_quantity": false, "children_count": 0, "extern_acct_id": null, "extern_sts_ids": [], "threshold_value": 0, "unit_multiplier": 25, "unit_of_measure": "Unit", "wholesale_price": {"amount": 62.5, "currency": "USD"}, "discount_percent": 0, "threshold_action": "unavailable", "featured_on_deals": false, "unit_denomination": {"id": 1, "name": "One", "label": "1", "value": "1.00000"}, "cost_of_goods_sold": null, "product_data_items": [{"spec_type": "thc", "text_value": "", "dosage_unit": "%", "decimal_value": "24.66", "max_decimal_value": "0.00"}, {"spec_type": "cannabinoids", "text_value": "", "dosage_unit": "%", "decimal_value": "28.65", "max_decimal_value": "0.00"}, {"spec_type": "terpenes", "text_value": "", "dosage_unit": "%", "decimal_value": "1.21", "max_decimal_value": "0.00"}], "available_inventory": 0.0, "base_units_per_unit": "1.000000000000000", "inventory_management": 1, "is_medical_line_item": false, "price_schedule_price": {"amount": 62.5, "currency": "USD"}, "AVAILABLE_FOR_SAMPLES": false, "display_listing_state": "Archived", "extern_income_acct_id": null, "s2s_conversion_amount": "1.0000", "strain_classification": "hybrid", "reverse_threshold_value": null, "sell_in_unit_of_measure": "Case", "reverse_threshold_action": "", "allow_fractional_quantities": false, "has_deals_eligible_children": false, "low_inventory_alert_threshold": "100.0000", "should_alert_on_low_inventory": false, "strain_classification_display": "Hybrid"}
fetched_at | 2022-06-17 14:21:28.621425
inserted_at | 2022-06-08 21:21:58.226228
updated_at | 2022-06-17 14:21:28.621492
However, this duplicated product seems to already be archived, so it shouldn't be the issue. Furthermore, we're seeing that warning of SKU mismatch above because the Distru product SKU doesn't line up with the LL product SKU (which should be fine since they're already linked by ID).
I'm now going to look at the module that syncs the inventory levels from Distru to LL: Distru.Workers.Leaflink.SyncProductsAvailableQuantityToLeaflink
Let's see how much available inventory the code thinks Distru product 620527 has first:
company = Distru.Repo.get!(Distru.Company, 373773)
query = Distru.Workers.Leaflink.SyncProductsAvailableQuantityToLeaflink.available_inventory_query(company, 620527)
result = Distru.Repo.all(query)
We get: [%{distru_available_quantity: #Decimal<-25.000000000>}]
Lets break down why this is -25:
First we need to scope by only LL synced locations:
SELECT id FROM locations WHERE sync_inventory_to_leaflink IS TRUE AND company_id = 373773;
id
--------
315226
SELECT SUM(reserved_quantity) FROM product_quantities WHERE product_id = 620527 AND location_id = 315226;
sum
--------------
50.000000000
SELECT SUM(quantity) FROM stocks WHERE product_id = 620527 AND status = 'active' AND location_id = 315226;
sum
---------------
75.000000000
SELECT SUM(oi.quantity) FROM order_items oi
JOIN orders o ON o.id = oi.order_id
WHERE o.status = 'Pending'
AND o.leaflink_id IS NOT NULL
AND oi.product_id = 620527;
sum
--------------
50.000000000
75 - 50 - 50 = -25
This data all makes sense, the active inventory the user is looking at contains inventory from both LL and Distru, but when we factor in just LL synced locations, the product has negative inventory, which we sync to LL as zero.