Inventory at Risk (IAR) Calculation in Drug Substance Not As Specified (DNSA)

Introduction: In the life sciences domain, manufacturing high-quality drug substances is critical to ensuring that products meet regulatory standards. When a DNSA event occurs, there is a risk that part of the inventory could be unusable, leading to financial and operational losses. The goal is to calculate the Inventory at Risk in real-time based on data from various stages of production, storage, and quality checks.

 

Requirement: Calculate the inventory_at_risk based on flag_active (Y/N). So if flag_active is 'Y' then sum of financial_qty of f_inv_movmnt table. After that calculate the Percentage of Inventory at risk of the total inventory that may be affected based on flag_active.

Formula - Percentage of Inventory at Risk = (inventory at risk / total inventory) * 100

 

Expected Codebase: Spark SQL.

 

Unity Catalog details: purgo_playground.f_inv_movmnt