Applied Quantity: A Key Metric for Inventory Transaction Optimization

Introduction: 

In the Life Sciences digital supply chain, specifically for managing inventory transactions, the logic of APPLIED QTY can be utilized to control and track the allocation of inventory quantities in real time. It handles returns, damaged goods, and negative transactions, ensuring that supply meets demand across various stages of the supply chain. 

Requirement:

Calculate the apl_qty based on the relationship between various cumulative and transactional quantity fields (ref_txn_qty, cumulative_txn_qty, cumulative_ref_ord_sched_qty, etc.). The logic is provided in the attached Excel file and use only the sample data provided in Excel file for calculation. 

Expected Output: Pyspark, Spark SQL.