There were other related-QC procedures that had to be developed because of these incidents. For example, if you had quantitative reference(s) disappear from a multi-output process, I found that, upon discovering and re-instating the quantitative reference, you then have to check that the default allocation factors also get re-instated correctly. Because the product flows don't disappear (just the quantitative reference designation at the process exchange-level), you can also identify 'disappearing quantitative references' by cross-checking the flows table with the exchange tables to look for product type flows that are not also an exchange of the output type (IS_INPUT = 0 in exchange table); that is, if you know which ones should have providing processes in your database, i.e., which ones are CUTOFFs and used only as inputs.
# Extracts product output flows by providing process with exchange details
SELECT pr.id AS Process_ID, pr.name AS Process_Name, f.ID AS FLOW_ID, f.REF_ID AS Flow_UUID, f.NAME AS Flow_Name, f.FLOW_TYPE AS Flow_Type, c.name AS Subcategory, (SELECT c.name FROM tbl_categories AS c WHERE c.f_category = c.ID) AS Category, f.f_location AS Geography, u.id AS Unit_ID, e.f_unit AS Exchange_Unit_ID, u.name AS Unit, pf.ID AS Flow_Property_Factor_ID, e.F_OWNER AS Exchange_Process, e.IS_INPUT AS Input_To, e.id AS Exchange_ID, e.F_DEFAULT_PROVIDER AS Default_Provider, e.AVOIDED_PRODUCT AS Avoided_Product
FROM tbl_flows AS f
LEFT JOIN tbl_exchanges AS e
ON e.F_FLOW = f.ID
LEFT JOIN tbl_processes AS pr
ON pr.ID = e.F_OWNER
INNER JOIN tbl_categories AS c
ON f.f_category = c.ID
INNER JOIN tbl_flow_properties AS p
ON f.F_REFERENCE_FLOW_PROPERTY = p.ID
INNER JOIN tbl_unit_groups AS ug
ON ug.ID = p.F_UNIT_GROUP
INNER JOIN tbl_units AS u
ON u.id = ug.F_REFERENCE_UNIT
LEFT OUTER JOIN tbl_flow_property_factors AS pf
ON pf.f_flow = e.f_flow
WHERE e.IS_INPUT = 0
AND f.FLOW_TYPE = 'PRODUCT_FLOW';
*Sort by process name and conditionally format duplicates to examine multi-output processes' outputs
You can also extract all product flows and cross-check these results with the list generated above to see if there are product flows not being listed in the outputs that should be.
