This document outlines the logic for the automated Invoice-to-Quota synchronization task. The system ensures that every paid service is translated into a usable member entitlement while respecting complex “Package” (BOM) structures and recurring billing cycles.

The goal is to automate the creation of member “quotas” (entry entitlements) based on successful invoice generation. For every invoice issued to a member for a specific service, the system must generate a corresponding quota record that defines what they can do, how many times, and for how long.

The logic draws from five distinct functional areas of your database:

• Transactions (dok, dok_items): The source of truth for what was purchased and when.
• Membership & Services (sif_clan, sif_art): The master files for who the members are and what services/packages exist.
• Recurring Logic (osn_zad): The “engine” that defines the billing frequency and the duration of the service (Days vs. Months).
• Recipes/Packages (osn_sastavnice, osn_sastavke): The breakdown logic for “bundled” services (e.g., a “VIP Package” that consists of 10 Gym entries and 2 Saunas).
• Entitlements (gym_quota, gym_quotad): The destination tables that control the member’s actual access rights.

To ensure data integrity and business accuracy, the extraction process follows a strict hierarchy of filters:

Data is only extracted if:

1. Company Level: The company (sif_pod) is active and has purchased the specific Module 50.
2. Department Level: The department (sif_oj) is active.
3. Config Level: The specific setting OJ_CLAN = 'F' exists in osn_setoj for that department.

The system distinguishes between standalone services and packages using the sif_art.ru flag:

• Simple Service (ru != 'Y'): The item is moved directly to the quota.
• Package Service (ru = 'Y'): The system “explodes” the item into its individual components via the osn_sastavke table. The final quota quantity is calculated as:
  • Total_Qty=Invoice_Qty×Recipe_Ingredient_Qty

Instead of using a fixed duration, the system calculates the expiry date (edate) dynamically based on the subscription master (osn_zad):

• Daily Frequency (dm = 'D'): edate = sdate + frek
• Monthly Frequency (dm = 'M'): edate = sdate + (frek * months)
• Cycle Alignment: The sdate is anchored to the membership start date (poc), moving forward in cycles to align with the current invoice period.

The task is designed to be idempotent, meaning it can run multiple times without creating duplicate records.

1. Identification: A LEFT JOIN between dok and gym_quota identifies invoices that do not yet have an assigned quota.
2. Transformation: The SQL handles the complex joining of configuration settings and package recipes in a single pass.
3. Insertion: The Python script performs a two-stage transactional insert:
   • Stage 1: Insert into gym_quota (The Header).
   • Stage 2: Use the RETURNING id from Stage 1 to populate gym_quotad (The Details) with the exploded list of services.