Reporting on item country of origin (COO)

[dalers]

How could a small electronics product manufacturing company record and report on purchased stock item country of origin (COO)? My employer has "asked" for a report as soon as possible and wIth global supply chains and increasingly complicated compliance reporting, I am guessing we are not alone. Effectively managing purchased item COO could be a feature of significant interest to many manufacturing companies.

Per Gemini, the typical implementation is to add a country of origin field to the stock item master record which is essentially a requirement to the purchaser (who will also obtain a certificate of origin from the supplier). Although COO is arguably more directly related to the supplier (or manufacturer), using a stock item master field seems to be the more pragmatic solution based on typical ERP features. My concern is that associating the COO with the stock master implies multiple stockids for what is essentially the same item except manufactured in a different countries, which I fear would lead to very complicated BOM and purchasing management.

Is anyone managing country of origin using webERP now? How?

Thanks for any and all suggestions.

Cheers,
Dale

[dalers]

No opinons? Surely someone must have an opinion @timschofield ? @pakricard ? @andrewgaluski ? :-)

I am considering adding a "Country of Origin" stock category property to each stock category following Tim's explanation of stock item properties. I was also planning to use stock categories to represent lifecycle state following the pattern described by @pakricard.

While this seems technically feasible, the combined result seems overly complicated and I'm not sure how I would create a report that calculates the summarized item cost by country of origin for a complete product (a product being a multi-level hierarchical BOM with possible re-use of parts and sub-assemblies within the BOM). Any suggestions?

P.S. It was brought to my attention that some of my recent posts could have been less kind than one would expect. I admit I can react with more emotion than consideration and then edit if needed. This is obviously not good for those get new posts emailed to them (and only ever see the first post unless they read the edited version on GitHub) and I promise to think more and type/edit less. Please accept my apologies for any offences I may have caused.

[dalers]

I should emphasise that using a stock category property for "Country of Origin" and stock categories for lifecycle states is the best solution currently available but not my preferred solution - which would be to add a field specifically for Country of Origin (most likely to the stockmaster table) and implement a generalized lifecycle managemement system as discussed in #813. However, that would take time.....

[andrewgaluski]

Dale,
I think this is an important piece of data for a lot of manufacturers for various reasons. Having an item level flag is good for planning. With supply chain complexity, specifically around tariffs being all over in the US recently and for some time moving forward. I can look at an MRP, cross reference with what parts I expect 'with' tariffs in the next x time frame and help me with my spend planning or use of substitutes or setting a preferred vendor in another country. For compliancy reporting, that is a whole different perspective. It is usually more a 'look back' meaning in xzy time frame, what percentage was from Country abc or what percentage of a FG over a time did we use foreign components in. That to me is a combination of the Item and Inbound Receipt or P?O (Where did it ship from). Consider I place orders with a supplier and they have plants in Korea and Vietnam and they set ship from in their side based on their capacity/demand. We would need to query the stock for those that have foreign COO and then the "Ship From" country on the order product was received on although that only tracks inbound receipts, not usage.
Admittedly I am not well versed in the actual rules/calculations of these things - that is why I would love to know the final 'end purpose' or 'use' of this data AFTER it has been setup and the business has been running.
What is the use case you are being asked for and do you have any example of how they are 'cobbling' this together now?
For goods under tariffs the additional field/property would be the HS Code/Tariff code. Having that also allows companies to use the current rates matched by HS Code to what they can expect in fees and potentially (down the road) add an additional line to the P/O (or depending on the terms of Shipment) create another P/O to a different agency to pay the tariffs.

[dalers]

Thanks @andrewgaluski for your thoughts.

My initial thought was for reporting, either to my government or a foreign government which may levy a tax or tarrif on my product based on content by country (even disallowing certain countries entirely). I hadn't considered planning processes but see the benefit to on-going operations could be even more valuable.

I was considering "country of origin" as the country the item was manufactured in, not the country it was shipped from (although the two could be the same in some cases). This was based on my experience several years ago when I was tasked (as the product engineer) to create a report listing each component in our product (a high-tech music industry digital effects processor), the component purchase cost and country of (manufacturing) origin. However, I am also not as well versed in the details of the businesss processes as I would like to be and I'll take your question about use to the team to discuss and see if we are all aligned (I expect we probably aren't entirely) .

An additional stock category property could be used for a stock items HS code (in addition to its COO) and besides printing the HS code on shipping documents or using it to add the expected tarrif to the sales order for the customer to pay (if that's the business model), it could be used for planning purposes as you describe. The details of exactly how one might do all that are starting to cause my brain to fog but it all needs to start with recording the HS code and everything else can wait until it's needed.

We don't currently have a process for cobbling together such a report. It was suggestion we manually create a stand-alone spreadsheet with columns for internal part number, country of origin and all "alternate" supplier part numbers (and keep it updated to always be accurate).

[andrewgaluski]

Yes, it does have to be where the component was manufactured I believe and in my example of Korea/Vietnam, that is one vendor/supplier, 2 manufacturing facilities. I agree, just getting the fields there is a good start. except for some edge cases you can answer questions like "How many of our raws are manufactured in country xyz" or "What FGs are impacted if country xyz suddenly can't ship or becomes 30% more expensive" Because they just went to war or got hit with tariffs. I believe the tariff code is determined by the manufacturer (how it is classified) and based on that some companies decide to setup multiple parts when they have multiple manufacturers instead of single code, multiple purchasing data scenario most of us are familiar with operating on.

[timschofield]

I would need to think deeper on this, but I would have thought that the purchdata table is the place to store this information. It is entered in the PurchData.php script, accessed from the SelectProduct.php script. The primary purpose of this table is to tie the stock item and the supplier together.

We cannot guarantee that there will always be a 1:1 correlation between stock items and suppliers, so the stock item may be purchased from multiple countries.

Just my quick thoughts.

Tim

[dalers]

I would have thought that the purchdata table is the place to store this information

I also thought so but Gemini says the best solution is the stockmaster table unless "OEM Records" are used. Coincidently I am planning to add OEM Records but hadn't considered COO as a reason to at the time (my motivation was only to specify the required OEM part number, regardless of the supplier it can be or is being purchased from).

This is Gemini's response to "why is the COO stored as a mandatory field in the Item Master record instead of with the supplier or OEM record?"

Monitoring the Country of Origin (COO) in the Item Master record rather than the Supplier record is a fundamental principle of effective inventory and compliance management. While it might seem intuitive to link a location to a vendor, doing so creates significant data integrity risks.

Here is why the Item Master is the correct home for this data:

1. The "Many-to-One" Supplier Relationship

A single supplier or distributor (like Digi-Key or Mouser) often sells thousands of different components manufactured in dozens of different countries.

• If you store the COO on the Supplier Record, you are forced to assume every part from that vendor comes from the same country.
• By storing it in the Item Master, you can specify that Part A (Resistor) is from Japan and Part B (Capacitor) is from Malaysia, even if both are purchased from the same Canadian distributor.

2. Multi-Sourcing and Interchangeability

In electronics manufacturing, you often have one internal Part Number (IPN) that can be fulfilled by multiple approved manufacturers (the "Approved Vendor List" or AVL).

• If you buy a generic 10k resistor, you might source it from Vendor A (Taiwan) this month and Vendor B (USA) next month.
• The Item Master acts as the "source of truth" for what that part is. When you run a Bill of Materials (BOM) report to calculate the total originating value of a product, the system looks at the parts on the list, not the address of the person who sent the invoice.

3. Granular Traceability for "Substantial Transformation"

Customs authorities care about where the part was made, not where the vendor is headquartered.

• A German company might have a warehouse in Mexico, but the silicon wafer inside the component was fabricated in South Korea.
• Storing COO at the item level allows you to capture the specific manufacturing origin required for Tariff Shift rules and Trade Agreement (like USMCA) certifications.

Comparison: Item Master vs. Supplier Record

Feature	Stored in Item Master	Stored in Supplier Record
Accuracy	High: Specific to the individual component.	Low: Generalizes across all goods from that vendor.
BOM Roll-up	Automatic and precise.	Impossible or requires manual overrides.
Compliance	Meets Customs/Audit requirements.	Often leads to "Non-Compliance" flags during audits.
Multi-Sourcing	Handles multiple origins for the same part type.	Fails if the vendor sources from multiple regions.

4. The Role of the OEM Record

While the Item Master holds your internal data, the OEM (Original Equipment Manufacturer) Record is often linked to the item. The COO is technically a property of the OEM's specific factory output.

In a sophisticated setup, the relationship looks like this:

1. Item Master: Your internal Part Number.

2. Manufacturer Part Number (MPN): The OEM's specific part ID.

3. COO: The specific country associated with that MPN.

If your ERP allows for "Batch" or "Lot" tracking, the most advanced way to handle COO is at the Lot level. This allows you to track a scenario where you have the exact same part number in stock, but half the bin is from Japan and the other half is from China due to a mid-year supplier shift.

[timschofield]

I'm not sure what gemini is saying here. If item A can be sourced from either Canada or Australia, then which country would appear in the items COO field?

The purchdata table is not just a supplier table it contains info on the relationship between stockiid and supplierid. There are still problems with using it, but I would say they are smaller problems than just putting it in the stock record.

[pakricard]

As we use it, COO is a certificate issued by the exporter of an item, stating the “Country of last significant transformation”. Because this was created by lawyers and politicians and not IT nerds, they included the word “significant”, so there's grey area.

So, in most cases, COO belongs to the stockid (stockmaster).
But, in stockid's supplied by more than one supplier, we can have the same stockid with 2 different COOs (Bolt ABCD, with supplier A in China and supplier B in Vietnam).
Moreover, if the same stockid is sold by distributor D (which is our supplier), the COO of the item could be anywhere, as supplier can change suppliers.

So, doing it exactly right can be complicated, and we'll always have exceptions.

I would prefer it linked to supplier data: Supplier A sells bolt ABCD stating COO Country X. We could default it to the country of the supplier. Then we can assume the COO of the item itself is the COO of its preferred supplier (we have a flag for it).

R

[timschofield]

Thinking about this some more, the only way to track this with 100% reliability, is to make each part you want to track the COO a batch controlled item, even if it is only an internal batch number. Then you can store the COO in the batch record. This would entail a physical separation in the warehouse.

[dalers]

Fyi, I will be creating a wiki page to document a COO management process and any changes needed to webERP and take into account everyone's concerns/needs (so keep posting if you have more).