Supply Chain (Pharma) Demo

/*
  Trace demand from a distributor back to raw material quantity requirements.
*/
MATCH (d:Distributor)<-[db:DISTRIBUTED_BY]-(prod:Product)
WHERE prod.productSKU = '9a6b431f-3a38-4b45-9451-fbf39b2e2fd0'  // Start with a specific product

MATCH (api)<-[pf2:PRODUCT_FLOW]-(rm:RM)
WHERE pf2.globalBrand = prod.globalBrand
  AND pf2.strength = prod.strength
  AND pf2.form = prod.form
  AND pf2.generation = prod.generation                 // Match equivalent raw materials by product attributes

WITH d, db.demandQty AS demandQty, db, prod, COLLECT(DISTINCT rm) AS RMList
UNWIND RMList AS curRM                                 // Process each matching raw material

MATCH p = shortestPath((prod)<-[pf1:PRODUCT_FLOW*]-(curRM))  // Trace shortest path from RM to product

MATCH p3 = (myProd:Product)<-[pf:PRODUCT_FLOW]-(curRM)<-[:SUPPLIES_RM]-(sup:Suppliers)
WHERE pf.globalBrand = prod.globalBrand
  AND pf.strength = prod.strength
  AND pf.form = prod.form
  AND pf.generation = prod.generation
  AND pf.market = d.market                              // Match supplier relationships in the same market

RETURN
  sup.companyName AS supplierName,
  curRM.productSKU AS rawMaterialSKU,
  apoc.coll.disjunction(["Product"], labels(myProd))[0] AS usedBy, // Identify final product stage (API, DP, etc.)
  demandQty,
  REDUCE(rmQty = demandQty, rel IN relationships(p) |
    TOINTEGER(ROUND(rmQty / (COALESCE(rel.conversionRatio, 1.0)), 0))
  ) AS rawMaterialQty

ORDER BY usedBy, supplierName, rawMaterialSKU

/*
  Detects either Cross-Border or Cyclic shipment patterns
  in Finished Goods (FG) to Distributor (DIST) product flows.
*/
MATCH (fg:FG:Product WHERE fg.globalBrand = "Calciiarottecarin")   // Start with FG products for a specific brand
WHERE NOT EXISTS {
  MATCH (fg)-[pf:PRODUCT_FLOW WHERE pf.globalBrand = "Calciiarottecarin"
    AND pf.generation = "g2"
    AND pf.form = "Caplet"                                // Filter: generation, form and strength of the drug
    AND pf.strength = "50mg"
  ]->(:FG)                                                // Ensure it's not part of a chained FG → FG flow
}

WITH fg, count(*) AS num

MATCH p = (fg)-[pf:PRODUCT_FLOW WHERE pf.globalBrand = "Calciiarottecarin"
  AND pf.generation = "g2"
  AND pf.form = "Caplet"
  AND pf.strength = "50mg"
]->+ (dist:DIST:Product WHERE dist.globalBrand = fg.globalBrand
  AND dist.generation = fg.generation
  AND dist.strength = fg.strength
  AND dist.form = fg.form)
WHERE EXISTS {
  MATCH (dist)-[:DISTRIBUTED_BY]->(:Distributor)                // Confirm product ends with a Distributor
}

WITH fg,
  REDUCE(loc = [], x IN nodes(p)[0..-1] | loc + [split(x.location, "/")[1]]) AS countryList,
  REDUCE(loc = [], x IN nodes(p)[0..-1] | loc + [x.location]) AS locationList,
  REDUCE(loc = [], x IN nodes(p) | loc + [x.location]) AS fullLocList

WITH fg, countryList, locationList, fullLocList,
  apoc.coll.dropDuplicateNeighbors(apoc.coll.sort(countryList)) AS dedupCountryList,
  apoc.coll.dropDuplicateNeighbors(apoc.coll.sort(locationList)) AS dedupLocationList

WHERE
  (countryList[0] = countryList[-1] AND size(dedupCountryList) > 1)   // Cross-Border condition
  OR
  (locationList[0] = locationList[-1] AND size(dedupLocationList) > 1) // Cyclic condition

WITH fg, fullLocList,
  CASE
    WHEN countryList[0] = countryList[-1] AND size(dedupCountryList) > 1
      THEN "Cross Border Shipment"
    WHEN locationList[0] = locationList[-1] AND size(dedupLocationList) > 1
      THEN "Cyclic Movement"
    ELSE null
  END AS costType

RETURN DISTINCT
  costType,
  fg.form AS form,
  fg.generation AS gen,
  fg.strength AS strength,
  fullLocList AS LocationPath

ORDER BY fg.generation, fg.strength