Erp screen generation - v0 by Vercel

-- ====================================-- 1. CREATE MISSING GL/AR/AP TABLES-- ====================================-- GL Posting Rules TableDROP TABLE IF EXISTS gl_posting_rules;CREATE TABLE gl_posting_rules ( posting_rule_id CHAR(36) NOT NULL, account_id CHAR(36) DEFAULT NULL, workspace_id CHAR(36) DEFAULT NULL, control_unit_id CHAR(36) DEFAULT NULL, rule_name VARCHAR(100) NOT NULL, rule_description TEXT, document_type VARCHAR(50) DEFAULT NULL, transaction_type VARCHAR(50) DEFAULT NULL, rule_priority ENUM('LOW','MEDIUM','HIGH') DEFAULT 'MEDIUM', is_active TINYINT(1) DEFAULT '1', -- Rule Conditions job_cost_item_id CHAR(36) DEFAULT NULL, customer_org_type_id INT DEFAULT NULL, supplier_org_type_id INT DEFAULT NULL, source_entity_type VARCHAR(50) DEFAULT NULL, tax_jurisdiction_id CHAR(36) DEFAULT NULL, tax_type_id CHAR(36) DEFAULT NULL, cost_center_pattern VARCHAR(100) DEFAULT NULL, profit_center_pattern VARCHAR(100) DEFAULT NULL, rule_conditions JSON DEFAULT NULL, -- Account Mappings default_debit_account_id CHAR(36) DEFAULT NULL, default_credit_account_id CHAR(36) DEFAULT NULL, default_tax_account_id CHAR(36) DEFAULT NULL, default_wip_account_id CHAR(36) DEFAULT NULL, default_ar_account_id CHAR(36) DEFAULT NULL, default_ap_account_id CHAR(36) DEFAULT NULL, -- Account Derivation Logic account_derivation_method ENUM('FIXED','ITEM_MASTER_MAPPING','TAX_JURISDICTION_MAPPING','RULE_BASED') DEFAULT 'FIXED', account_mapping_config JSON DEFAULT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, created_by CHAR(36) DEFAULT NULL, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, updated_by CHAR(36) DEFAULT NULL, PRIMARY KEY (posting_rule_id), KEY idx_gl_posting_rules_document_type (document_type), KEY idx_gl_posting_rules_job_cost_item (job_cost_item_id), KEY idx_gl_posting_rules_tax_jurisdiction (tax_jurisdiction_id), KEY idx_gl_posting_rules_control_unit (control_unit_id), CONSTRAINT fk_gl_posting_rules_control_unit FOREIGN KEY (control_unit_id) REFERENCES control_unit (control_unit_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_gl_posting_rules_job_cost_item FOREIGN KEY (job_cost_item_id) REFERENCES cs_job_cost_item (job_cost_item_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_gl_posting_rules_tax_jurisdiction FOREIGN KEY (tax_jurisdiction_id) REFERENCES tax_jurisdictions (tax_jurisdiction_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_gl_posting_rules_tax_type FOREIGN KEY (tax_type_id) REFERENCES tax_types (tax_type_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_gl_posting_rules_debit_account FOREIGN KEY (default_debit_account_id) REFERENCES gl06_chart_of_account (chart_of_account_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_gl_posting_rules_credit_account FOREIGN KEY (default_credit_account_id) REFERENCES gl06_chart_of_account (chart_of_account_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_gl_posting_rules_ar_account FOREIGN KEY (default_ar_account_id) REFERENCES gl06_chart_of_account (chart_of_account_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_gl_posting_rules_ap_account FOREIGN KEY (default_ap_account_id) REFERENCES gl06_chart_of_account (chart_of_account_id) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;-- GL Posting Rule Conditions TableDROP TABLE IF EXISTS gl_posting_rule_conditions;CREATE TABLE gl_posting_rule_conditions ( condition_id CHAR(36) NOT NULL, posting_rule_id CHAR(36) NOT NULL, condition_type VARCHAR(50) DEFAULT NULL, condition_field VARCHAR(100) DEFAULT NULL, condition_operator VARCHAR(20) DEFAULT NULL, condition_value VARCHAR(255) DEFAULT NULL, condition_sequence INT DEFAULT NULL, is_active TINYINT(1) DEFAULT '1', PRIMARY KEY (condition_id), KEY idx_gl_posting_rule_conditions_rule (posting_rule_id), CONSTRAINT fk_gl_posting_rule_conditions_rule FOREIGN KEY (posting_rule_id) REFERENCES gl_posting_rules (posting_rule_id) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;-- GL Transaction Header TableDROP TABLE IF EXISTS gl_transaction_header;CREATE TABLE gl_transaction_header ( gl_transaction_header_id CHAR(36) NOT NULL, account_id CHAR(36) DEFAULT NULL, workspace_id CHAR(36) DEFAULT NULL, control_unit_id CHAR(36) DEFAULT NULL, journal_entry_id VARCHAR(50) DEFAULT NULL, document_number VARCHAR(100) DEFAULT NULL, document_type VARCHAR(50) DEFAULT NULL, transaction_source VARCHAR(50) DEFAULT NULL, transaction_type VARCHAR(50) DEFAULT NULL, transaction_date DATE DEFAULT NULL, posting_date DATE DEFAULT NULL, gl_date DATE DEFAULT NULL, -- Fiscal Calendar Integration fiscal_year_id CHAR(36) DEFAULT NULL, fiscal_period_id CHAR(36) DEFAULT NULL, period_sequence_number INT DEFAULT NULL, description TEXT, reference_number VARCHAR(100) DEFAULT NULL, total_debit_amount DECIMAL(15,2) DEFAULT '0.00', total_credit_amount DECIMAL(15,2) DEFAULT '0.00', functional_currency_id INT DEFAULT NULL, status VARCHAR(20) DEFAULT 'DRAFT', approval_status VARCHAR(20) DEFAULT 'PENDING', created_by CHAR(36) DEFAULT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, updated_by CHAR(36) DEFAULT NULL, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, posted_by CHAR(36) DEFAULT NULL, posted_at TIMESTAMP NULL DEFAULT NULL, PRIMARY KEY (gl_transaction_header_id), UNIQUE KEY uk_gl_transaction_header_journal_entry (journal_entry_id), KEY idx_gl_transaction_header_document_number (document_number), KEY idx_gl_transaction_header_transaction_date (transaction_date), KEY idx_gl_transaction_header_fiscal_period (fiscal_period_id), KEY idx_gl_transaction_header_control_unit (control_unit_id), KEY idx_gl_transaction_header_status (status), CONSTRAINT fk_gl_transaction_header_control_unit FOREIGN KEY (control_unit_id) REFERENCES control_unit (control_unit_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_gl_transaction_header_fiscal_year FOREIGN KEY (fiscal_year_id) REFERENCES fiscal_year (fiscal_year_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_gl_transaction_header_fiscal_period FOREIGN KEY (fiscal_period_id) REFERENCES fiscal_periods (period_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_gl_transaction_header_currency FOREIGN KEY (functional_currency_id) REFERENCES currency_master (currency_id) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;-- GL Transaction Lines TableDROP TABLE IF EXISTS gl_transaction_lines;CREATE TABLE gl_transaction_lines ( gl_transaction_line_id CHAR(36) NOT NULL, gl_transaction_header_id CHAR(36) NOT NULL, account_id CHAR(36) DEFAULT NULL, workspace_id CHAR(36) DEFAULT NULL, control_unit_id CHAR(36) DEFAULT NULL, chart_of_account_id CHAR(36) DEFAULT NULL, -- Account Classification finance_type_id CHAR(36) DEFAULT NULL, elements_of_financial_statement_id CHAR(36) DEFAULT NULL, account_group_id CHAR(36) DEFAULT NULL, account_group_sub_id CHAR(36) DEFAULT NULL, natural_account_id CHAR(36) DEFAULT NULL, account_code VARCHAR(50) DEFAULT NULL, account_name VARCHAR(255) DEFAULT NULL, -- Transaction Details line_number INT DEFAULT NULL, transaction_type ENUM('DEBIT','CREDIT') DEFAULT NULL, debit_amount DECIMAL(15,2) DEFAULT '0.00', credit_amount DECIMAL(15,2) DEFAULT '0.00', net_amount DECIMAL(15,2) DEFAULT '0.00', -- Source Document Traceability source_entity_type VARCHAR(50) DEFAULT NULL, source_entity_id CHAR(36) DEFAULT NULL, source_document_type VARCHAR(50) DEFAULT NULL, source_document_number VARCHAR(100) DEFAULT NULL, source_line_id CHAR(36) DEFAULT NULL, -- Tax Integration tax_item_id CHAR(36) DEFAULT NULL, tax_jurisdiction_id CHAR(36) DEFAULT NULL, tax_rate DECIMAL(8,4) DEFAULT '0.0000', tax_base_amount DECIMAL(15,2) DEFAULT '0.00', tax_amount DECIMAL(15,2) DEFAULT '0.00', -- Organizational Dimensions branch_code VARCHAR(20) DEFAULT NULL, region_code VARCHAR(20) DEFAULT NULL, country_code VARCHAR(10) DEFAULT NULL, zone_code VARCHAR(20) DEFAULT NULL, company_code VARCHAR(20) DEFAULT NULL, cost_center_code VARCHAR(50) DEFAULT NULL, profit_center_code VARCHAR(50) DEFAULT NULL, -- Trading Partners customer_vendor_id CHAR(36) DEFAULT NULL, customer_vendor_code VARCHAR(50) DEFAULT NULL, customer_vendor_name VARCHAR(255) DEFAULT NULL, customer_vendor_type VARCHAR(20) DEFAULT NULL, -- Multi-Currency transaction_currency_id INT DEFAULT NULL, functional_currency_id INT DEFAULT NULL, exchange_rate DECIMAL(10,6) DEFAULT '1.000000', functional_debit_amount DECIMAL(15,2) DEFAULT '0.00', functional_credit_amount DECIMAL(15,2) DEFAULT '0.00', -- Fiscal Period Integration fiscal_year_id CHAR(36) DEFAULT NULL, fiscal_period_id CHAR(36) DEFAULT NULL, period_sequence_number INT DEFAULT NULL, transaction_date DATE DEFAULT NULL, posting_date DATE DEFAULT NULL, description TEXT, reference_1 VARCHAR(100) DEFAULT NULL, reference_2 VARCHAR(100) DEFAULT NULL, status VARCHAR(20) DEFAULT 'POSTED', created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, created_by CHAR(36) DEFAULT NULL, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, updated_by CHAR(36) DEFAULT NULL, posted_at TIMESTAMP NULL DEFAULT NULL, posted_by CHAR(36) DEFAULT NULL, PRIMARY KEY (gl_transaction_line_id), KEY idx_gl_transaction_lines_header (gl_transaction_header_id), KEY idx_gl_transaction_lines_chart_account (chart_of_account_id), KEY idx_gl_transaction_lines_source_entity (source_entity_type,source_entity_id), KEY idx_gl_transaction_lines_customer_vendor (customer_vendor_id), KEY idx_gl_transaction_lines_fiscal_period (fiscal_period_id), KEY idx_gl_transaction_lines_transaction_date (transaction_date), KEY idx_gl_transaction_lines_control_unit (control_unit_id), CONSTRAINT fk_gl_transaction_lines_header FOREIGN KEY (gl_transaction_header_id) REFERENCES gl_transaction_header (gl_transaction_header_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_gl_transaction_lines_control_unit FOREIGN KEY (control_unit_id) REFERENCES control_unit (control_unit_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_gl_transaction_lines_chart_account FOREIGN KEY (chart_of_account_id) REFERENCES gl06_chart_of_account (chart_of_account_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_gl_transaction_lines_finance_type FOREIGN KEY (finance_type_id) REFERENCES gl01_finance_type (finance_type_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_gl_transaction_lines_elements FOREIGN KEY (elements_of_financial_statement_id) REFERENCES gl02_elements_of_financial_statements (elements_of_financial_statement_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_gl_transaction_lines_account_group FOREIGN KEY (account_group_id) REFERENCES gl03_account_group (account_group_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_gl_transaction_lines_account_sub_group FOREIGN KEY (account_group_sub_id) REFERENCES gl04_account_sub_group (account_group_sub_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_gl_transaction_lines_natural_account FOREIGN KEY (natural_account_id) REFERENCES gl05_natural_account (natural_account_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_gl_transaction_lines_tax_item FOREIGN KEY (tax_item_id) REFERENCES tax_items (tax_item_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_gl_transaction_lines_tax_jurisdiction FOREIGN KEY (tax_jurisdiction_id) REFERENCES tax_jurisdictions (tax_jurisdiction_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_gl_transaction_lines_customer_vendor FOREIGN KEY (customer_vendor_id) REFERENCES crm_organization (org_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_gl_transaction_lines_fiscal_year FOREIGN KEY (fiscal_year_id) REFERENCES fiscal_year (fiscal_year_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_gl_transaction_lines_fiscal_period FOREIGN KEY (fiscal_period_id) REFERENCES fiscal_periods (period_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_gl_transaction_lines_transaction_currency FOREIGN KEY (transaction_currency_id) REFERENCES currency_master (currency_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_gl_transaction_lines_functional_currency FOREIGN KEY (functional_currency_id) REFERENCES currency_master (currency_id) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;-- AR Transactions TableDROP TABLE IF EXISTS ar_transactions;CREATE TABLE ar_transactions ( ar_transaction_id CHAR(36) NOT NULL, account_id CHAR(36) DEFAULT NULL, workspace_id CHAR(36) DEFAULT NULL, control_unit_id CHAR(36) DEFAULT NULL, transaction_number VARCHAR(50) DEFAULT NULL, transaction_type VARCHAR(50) DEFAULT NULL, customer_id CHAR(36) DEFAULT NULL, invoice_id CHAR(36) DEFAULT NULL, transaction_date DATE DEFAULT NULL, due_date DATE DEFAULT NULL, currency_id INT DEFAULT NULL, transaction_amount DECIMAL(15,2) DEFAULT '0.00', outstanding_amount DECIMAL(15,2) DEFAULT '0.00', paid_amount DECIMAL(15,2) DEFAULT '0.00', payment_terms INT DEFAULT NULL, status VARCHAR(20) DEFAULT 'OPEN', -- GL Integration gl_transaction_header_id CHAR(36) DEFAULT NULL, ar_account_id CHAR(36) DEFAULT NULL, revenue_account_id CHAR(36) DEFAULT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, created_by CHAR(36) DEFAULT NULL, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, updated_by CHAR(36) DEFAULT NULL, PRIMARY KEY (ar_transaction_id), UNIQUE KEY uk_ar_transactions_number (transaction_number), KEY idx_ar_transactions_customer (customer_id), KEY idx_ar_transactions_due_date (due_date), KEY idx_ar_transactions_status (status), KEY idx_ar_transactions_transaction_date (transaction_date), KEY idx_ar_transactions_control_unit (control_unit_id), KEY idx_ar_transactions_invoice (invoice_id), CONSTRAINT fk_ar_transactions_control_unit FOREIGN KEY (control_unit_id) REFERENCES control_unit (control_unit_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_ar_transactions_customer FOREIGN KEY (customer_id) REFERENCES crm_organization (org_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_ar_transactions_invoice FOREIGN KEY (invoice_id) REFERENCES invoice (invoice_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_ar_transactions_currency FOREIGN KEY (currency_id) REFERENCES currency_master (currency_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_ar_transactions_gl_header FOREIGN KEY (gl_transaction_header_id) REFERENCES gl_transaction_header (gl_transaction_header_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_ar_transactions_ar_account FOREIGN KEY (ar_account_id) REFERENCES gl06_chart_of_account (chart_of_account_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_ar_transactions_revenue_account FOREIGN KEY (revenue_account_id) REFERENCES gl06_chart_of_account (chart_of_account_id) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;-- AR Payments TableDROP TABLE IF EXISTS ar_payments;CREATE TABLE ar_payments ( ar_payment_id CHAR(36) NOT NULL, account_id CHAR(36) DEFAULT NULL, workspace_id CHAR(36) DEFAULT NULL, control_unit_id CHAR(36) DEFAULT NULL, payment_number VARCHAR(50) DEFAULT NULL, ar_transaction_id CHAR(36) NOT NULL, customer_id CHAR(36) DEFAULT NULL, payment_date DATE DEFAULT NULL, payment_amount DECIMAL(15,2) DEFAULT '0.00', payment_method VARCHAR(50) DEFAULT NULL, reference_number VARCHAR(100) DEFAULT NULL, -- GL Integration gl_transaction_header_id CHAR(36) DEFAULT NULL, cash_account_id CHAR(36) DEFAULT NULL, ar_account_id CHAR(36) DEFAULT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, created_by CHAR(36) DEFAULT NULL, PRIMARY KEY (ar_payment_id), UNIQUE KEY uk_ar_payments_number (payment_number), KEY idx_ar_payments_transaction (ar_transaction_id), KEY idx_ar_payments_date (payment_date), KEY idx_ar_payments_customer (customer_id), CONSTRAINT fk_ar_payments_ar_transaction FOREIGN KEY (ar_transaction_id) REFERENCES ar_transactions (ar_transaction_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_ar_payments_customer FOREIGN KEY (customer_id) REFERENCES crm_organization (org_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_ar_payments_gl_header FOREIGN KEY (gl_transaction_header_id) REFERENCES gl_transaction_header (gl_transaction_header_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_ar_payments_cash_account FOREIGN KEY (cash_account_id) REFERENCES gl06_chart_of_account (chart_of_account_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_ar_payments_ar_account FOREIGN KEY (ar_account_id) REFERENCES gl06_chart_of_account (chart_of_account_id) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;-- AP Transactions TableDROP TABLE IF EXISTS ap_transactions;CREATE TABLE ap_transactions ( ap_transaction_id CHAR(36) NOT NULL, account_id CHAR(36) DEFAULT NULL, workspace_id CHAR(36) DEFAULT NULL, control_unit_id CHAR(36) DEFAULT NULL, transaction_number VARCHAR(50) DEFAULT NULL, transaction_type VARCHAR(50) DEFAULT NULL, supplier_id CHAR(36) DEFAULT NULL, purchase_order_id CHAR(36) DEFAULT NULL, transaction_date DATE DEFAULT NULL, due_date DATE DEFAULT NULL, currency_id INT DEFAULT NULL, transaction_amount DECIMAL(15,2) DEFAULT '0.00', outstanding_amount DECIMAL(15,2) DEFAULT '0.00', paid_amount DECIMAL(15,2) DEFAULT '0.00', payment_terms INT DEFAULT NULL, status VARCHAR(20) DEFAULT 'OPEN', -- GL Integration gl_transaction_header_id CHAR(36) DEFAULT NULL, ap_account_id CHAR(36) DEFAULT NULL, expense_account_id CHAR(36) DEFAULT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, created_by CHAR(36) DEFAULT NULL, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, updated_by CHAR(36) DEFAULT NULL, PRIMARY KEY (ap_transaction_id), UNIQUE KEY uk_ap_transactions_number (transaction_number), KEY idx_ap_transactions_supplier (supplier_id), KEY idx_ap_transactions_due_date (due_date), KEY idx_ap_transactions_status (status), KEY idx_ap_transactions_transaction_date (transaction_date), KEY idx_ap_transactions_control_unit (control_unit_id), KEY idx_ap_transactions_purchase_order (purchase_order_id), CONSTRAINT fk_ap_transactions_control_unit FOREIGN KEY (control_unit_id) REFERENCES control_unit (control_unit_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_ap_transactions_supplier FOREIGN KEY (supplier_id) REFERENCES crm_organization (org_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_ap_transactions_purchase_order FOREIGN KEY (purchase_order_id) REFERENCES purchase_order (purchase_order_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_ap_transactions_currency FOREIGN KEY (currency_id) REFERENCES currency_master (currency_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_ap_transactions_gl_header FOREIGN KEY (gl_transaction_header_id) REFERENCES gl_transaction_header (gl_transaction_header_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_ap_transactions_ap_account FOREIGN KEY (ap_account_id) REFERENCES gl06_chart_of_account (chart_of_account_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_ap_transactions_expense_account FOREIGN KEY (expense_account_id) REFERENCES gl06_chart_of_account (chart_of_account_id) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;-- AP Payments TableDROP TABLE IF EXISTS ap_payments;CREATE TABLE ap_payments ( ap_payment_id CHAR(36) NOT NULL, account_id CHAR(36) DEFAULT NULL, workspace_id CHAR(36) DEFAULT NULL, control_unit_id CHAR(36) DEFAULT NULL, payment_number VARCHAR(50) DEFAULT NULL, ap_transaction_id CHAR(36) NOT NULL, supplier_id CHAR(36) DEFAULT NULL, payment_date DATE DEFAULT NULL, payment_amount DECIMAL(15,2) DEFAULT '0.00', payment_method VARCHAR(50) DEFAULT NULL, reference_number VARCHAR(100) DEFAULT NULL, -- GL Integration gl_transaction_header_id CHAR(36) DEFAULT NULL, cash_account_id CHAR(36) DEFAULT NULL, ap_account_id CHAR(36) DEFAULT NULL, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, created_by CHAR(36) DEFAULT NULL, PRIMARY KEY (ap_payment_id), UNIQUE KEY uk_ap_payments_number (payment_number), KEY idx_ap_payments_transaction (ap_transaction_id), KEY idx_ap_payments_date (payment_date), KEY idx_ap_payments_supplier (supplier_id), CONSTRAINT fk_ap_payments_ap_transaction FOREIGN KEY (ap_transaction_id) REFERENCES ap_transactions (ap_transaction_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_ap_payments_supplier FOREIGN KEY (supplier_id) REFERENCES crm_organization (org_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_ap_payments_gl_header FOREIGN KEY (gl_transaction_header_id) REFERENCES gl_transaction_header (gl_transaction_header_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_ap_payments_cash_account FOREIGN KEY (cash_account_id) REFERENCES gl06_chart_of_account (chart_of_account_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_ap_payments_ap_account FOREIGN KEY (ap_account_id) REFERENCES gl06_chart_of_account (chart_of_account_id) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;-- ====================================-- 2. ALTER EXISTING TABLES FOR GL INTEGRATION-- ====================================-- Add GL Integration to Invoice TableALTER TABLE invoice ADD COLUMN gl_transaction_header_id CHAR(36) DEFAULT NULL AFTER workflow_status_id,ADD COLUMN is_posted_to_gl TINYINT(1) DEFAULT '0' AFTER gl_transaction_header_id,ADD COLUMN gl_posted_date TIMESTAMP NULL DEFAULT NULL AFTER is_posted_to_gl,ADD COLUMN gl_posted_by CHAR(36) DEFAULT NULL AFTER gl_posted_date,ADD INDEX idx_invoice_gl_posted (is_posted_to_gl),ADD CONSTRAINT fk_invoice_gl_header FOREIGN KEY (gl_transaction_header_id) REFERENCES gl_transaction_header (gl_transaction_header_id) ON DELETE CASCADE ON UPDATE CASCADE;-- Add Primary Key and GL Integration to Invoice Items TableALTER TABLE invoice_itemsADD COLUMN invoice_item_id CHAR(36) NOT NULL FIRST,ADD PRIMARY KEY (invoice_item_id);ALTER TABLE invoice_itemsADD COLUMN posting_rule_id CHAR(36) DEFAULT NULL AFTER item_sequence,ADD COLUMN gl_income_account_id CHAR(36) DEFAULT NULL AFTER posting_rule_id,ADD COLUMN tax_item_id CHAR(36) DEFAULT NULL AFTER gl_income_account_id,ADD COLUMN tax_jurisdiction_id CHAR(36) DEFAULT NULL AFTER tax_item_id,ADD COLUMN tax_rate DECIMAL(8,4) DEFAULT '0.0000' AFTER tax_jurisdiction_id,ADD COLUMN tax_amount DECIMAL(15,2) DEFAULT '0.00' AFTER tax_rate,ADD COLUMN net_amount DECIMAL(15,2) DEFAULT '0.00' AFTER tax_amount,ADD COLUMN is_posted_to_gl TINYINT(1) DEFAULT '0' AFTER net_amount,ADD CONSTRAINT fk_invoice_items_posting_rule FOREIGN KEY (posting_rule_id) REFERENCES gl_posting_rules (posting_rule_id) ON DELETE CASCADE ON UPDATE CASCADE,ADD CONSTRAINT fk_invoice_items_tax_item FOREIGN KEY (tax_item_id) REFERENCES tax_items (tax_item_id) ON DELETE CASCADE ON UPDATE CASCADE,ADD CONSTRAINT fk_invoice_items_tax_jurisdiction FOREIGN KEY (tax_jurisdiction_id) REFERENCES tax_jurisdictions (tax_jurisdiction_id) ON DELETE CASCADE ON UPDATE CASCADE,ADD CONSTRAINT fk_invoice_items_gl_account FOREIGN KEY (gl_income_account_id) REFERENCES gl06_chart_of_account (chart_of_account_id) ON DELETE CASCADE ON UPDATE CASCADE;-- Add GL Integration to Purchase Order TableALTER TABLE purchase_order ADD COLUMN gl_transaction_header_id CHAR(36) DEFAULT NULL AFTER workflow_status_id,ADD COLUMN is_posted_to_gl TINYINT(1) DEFAULT '0' AFTER gl_transaction_header_id,ADD COLUMN gl_posted_date TIMESTAMP NULL DEFAULT NULL AFTER is_posted_to_gl,ADD COLUMN gl_posted_by CHAR(36) DEFAULT NULL AFTER gl_posted_date,ADD INDEX idx_purchase_order_gl_posted (is_posted_to_gl),ADD CONSTRAINT fk_purchase_order_gl_header FOREIGN KEY (gl_transaction_header_id) REFERENCES gl_transaction_header (gl_transaction_header_id) ON DELETE CASCADE ON UPDATE CASCADE;-- Add Primary Key and GL Integration to Purchase Order Items TableALTER TABLE purchase_order_itemsADD COLUMN po_item_id CHAR(36) NOT NULL FIRST,ADD PRIMARY KEY (po_item_id);ALTER TABLE purchase_order_itemsADD COLUMN posting_rule_id CHAR(36) DEFAULT NULL AFTER item_sequence,ADD COLUMN gl_expense_account_id CHAR(36) DEFAULT NULL AFTER posting_rule_id,ADD COLUMN tax_item_id CHAR(36) DEFAULT NULL AFTER gl_expense_account_id,ADD COLUMN tax_jurisdiction_id CHAR(36) DEFAULT NULL AFTER tax_item_id,ADD COLUMN tax_rate DECIMAL(8,4) DEFAULT '0.0000' AFTER tax_jurisdiction_id,ADD COLUMN tax_amount DECIMAL(15,2) DEFAULT '0.00' AFTER tax_rate,ADD COLUMN net_amount DECIMAL(15,2) DEFAULT '0.00' AFTER tax_amount,ADD COLUMN is_posted_to_gl TINYINT(1) DEFAULT '0' AFTER net_amount,ADD CONSTRAINT fk_purchase_order_items_posting_rule FOREIGN KEY (posting_rule_id) REFERENCES gl_posting_rules (posting_rule_id) ON DELETE CASCADE ON UPDATE CASCADE,ADD CONSTRAINT fk_purchase_order_items_tax_item FOREIGN KEY (tax_item_id) REFERENCES tax_items (tax_item_id) ON DELETE CASCADE ON UPDATE CASCADE,ADD CONSTRAINT fk_purchase_order_items_tax_jurisdiction FOREIGN KEY (tax_jurisdiction_id) REFERENCES tax_jurisdictions (tax_jurisdiction_id) ON DELETE CASCADE ON UPDATE CASCADE,ADD CONSTRAINT fk_purchase_order_items_gl_account FOREIGN KEY (gl_expense_account_id) REFERENCES gl06_chart_of_account (chart_of_account_id) ON DELETE CASCADE ON UPDATE CASCADE;-- ====================================-- 3. STORED PROCEDURES FOR GL/AR/AP POSTING-- ====================================-- Procedure 1: Post Invoice to AR and GLDELIMITER $DROP PROCEDURE IF EXISTS post_invoice_to_ar_gl$CREATE PROCEDURE post_invoice_to_ar_gl( IN p_invoice_id CHAR(36), IN p_posted_by CHAR(36))BEGIN DECLARE v_gl_header_id CHAR(36); DECLARE v_ar_transaction_id CHAR(36); DECLARE v_journal_entry_id VARCHAR(50); DECLARE v_invoice_amount DECIMAL(15,2) DEFAULT 0; DECLARE v_customer_id CHAR(36); DECLARE v_invoice_date DATE; DECLARE v_invoice_number VARCHAR(100); DECLARE v_currency_id INT; DECLARE v_account_id CHAR(36); DECLARE v_workspace_id CHAR(36); DECLARE v_control_unit_id CHAR(36); DECLARE v_ar_account_id CHAR(36) DEFAULT '550e8400-e29b-41d4-a716-446655440001'; -- Default AR Account DECLARE v_revenue_account_id CHAR(36) DEFAULT '550e8400-e29b-41d4-a716-446655440002'; -- Default Revenue Account DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END; START TRANSACTION; -- Get invoice details SELECT customer_id, invoice_date, invoice_number, currency_id, account_id, workspace_id, control_unit_id INTO v_customer_id, v_invoice_date, v_invoice_number, v_currency_id, v_account_id, v_workspace_id, v_control_unit_id FROM invoice WHERE invoice_id = p_invoice_id; -- Calculate total invoice amount SELECT COALESCE(SUM(total), 0) INTO v_invoice_amount FROM invoice_items WHERE invoice_id = p_invoice_id; -- Generate IDs SET v_gl_header_id = UUID(); SET v_ar_transaction_id = UUID(); SET v_journal_entry_id = CONCAT('JE-', v_invoice_number); -- 1. Create GL Transaction Header INSERT INTO gl_transaction_header ( gl_transaction_header_id, account_id, workspace_id, control_unit_id, journal_entry_id, document_number, document_type, transaction_source, transaction_type, transaction_date, posting_date, gl_date, description, total_debit_amount, total_credit_amount, functional_currency_id, status, created_by, posted_by, posted_at ) VALUES ( v_gl_header_id, v_account_id, v_workspace_id, v_control_unit_id, v_journal_entry_id, v_invoice_number, 'Sales Invoice', 'AR_MODULE', 'Revenue', v_invoice_date, v_invoice_date, v_invoice_date, CONCAT('Invoice posting for ', v_invoice_number), v_invoice_amount, v_invoice_amount, v_currency_id, 'POSTED', p_posted_by, p_posted_by, NOW() ); -- 2. Create GL Transaction Lines - Debit AR INSERT INTO gl_transaction_lines ( gl_transaction_line_id, gl_transaction_header_id, account_id, workspace_id, control_unit_id, chart_of_account_id, line_number, transaction_type, debit_amount, credit_amount, source_entity_type, source_entity_id, customer_vendor_id, transaction_date, posting_date, description, created_by, posted_by, posted_at ) VALUES ( UUID(), v_gl_header_id, v_account_id, v_workspace_id, v_control_unit_id, v_ar_account_id, 1, 'DEBIT', v_invoice_amount, 0, 'INVOICE', p_invoice_id, v_customer_id, v_invoice_date, v_invoice_date, CONCAT('AR for invoice ', v_invoice_number), p_posted_by, p_posted_by, NOW() ); -- 3. Create GL Transaction Lines - Credit Revenue (for each invoice item) INSERT INTO gl_transaction_lines ( gl_transaction_line_id, gl_transaction_header_id, account_id, workspace_id, control_unit_id, chart_of_account_id, line_number, transaction_type, debit_amount, credit_amount, source_entity_type, source_entity_id, source_line_id, transaction_date, posting_date, description, created_by, posted_by, posted_at ) SELECT UUID(), v_gl_header_id, v_account_id, v_workspace_id, v_control_unit_id, COALESCE(ii.gl_income_account_id, v_revenue_account_id), ROW_NUMBER() OVER (ORDER BY ii.item_sequence) + 1, 'CREDIT', 0, ii.total, 'INVOICE_ITEM', p_invoice_id, ii.invoice_item_id, v_invoice_date, v_invoice_date, CONCAT('Revenue for ', ii.job_cost_item_name), p_posted_by, p_posted_by, NOW() FROM invoice_items ii WHERE ii.invoice_id = p_invoice_id; -- 4. Create AR Transaction INSERT INTO ar_transactions ( ar_transaction_id, account_id, workspace_id, control_unit_id, transaction_number, transaction_type, customer_id, invoice_id, transaction_date, due_date, currency_id, transaction_amount, outstanding_amount, paid_amount, status, gl_transaction_header_id, ar_account_id, revenue_account_id, created_by ) VALUES ( v_ar_transaction_id, v_account_id, v_workspace_id, v_control_unit_id, v_invoice_number, 'INVOICE', v_customer_id, p_invoice_id, v_invoice_date, DATE_ADD(v_invoice_date, INTERVAL 30 DAY), v_currency_id, v_invoice_amount, v_invoice_amount, 0, 'OPEN', v_gl_header_id, v_ar_account_id, v_revenue_account_id, p_posted_by ); -- 5. Update Invoice with GL reference UPDATE invoice SET gl_transaction_header_id = v_gl_header_id, is_posted_to_gl = 1, gl_posted_date = NOW(), gl_posted_by = p_posted_by WHERE invoice_id = p_invoice_id; -- 6. Update Invoice Items posting status UPDATE invoice_items SET is_posted_to_gl = 1 WHERE invoice_id = p_invoice_id; COMMIT; SELECT 'SUCCESS' as status, v_gl_header_id as gl_header_id, v_ar_transaction_id as ar_transaction_id; END$DELIMITER ;-- Procedure 2: Post Purchase Order to AP and GLDELIMITER $DROP PROCEDURE IF EXISTS post_purchase_order_to_ap_gl$CREATE PROCEDURE post_purchase_order_to_ap_gl( IN p_purchase_order_id CHAR(36), IN p_posted_by CHAR(36))BEGIN DECLARE v_gl_header_id CHAR(36); DECLARE v_ap_transaction_id CHAR(36); DECLARE v_journal_entry_id VARCHAR(50); DECLARE v_po_amount DECIMAL(15,2) DEFAULT 0; DECLARE v_supplier_id CHAR(36); DECLARE v_po_date DATE; DECLARE v_po_number VARCHAR(100); DECLARE v_currency_id INT; DECLARE v_account_id CHAR(36); DECLARE v_workspace_id CHAR(36); DECLARE v_control_unit_id CHAR(36); DECLARE v_ap_account_id CHAR(36) DEFAULT '550e8400-e29b-41d4-a716-446655440003'; -- Default AP Account DECLARE v_expense_account_id CHAR(36) DEFAULT '550e8400-e29b-41d4-a716-446655440004'; -- Default Expense Account DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END; START TRANSACTION; -- Get purchase order details SELECT supplier_id, purchase_order_date, purchase_order_number, currency_id, account_id, workspace_id, control_unit_id INTO v_supplier_id, v_po_date, v_po_number, v_currency_id, v_account_id, v_workspace_id, v_control_unit_id FROM purchase_order WHERE purchase_order_id = p_purchase_order_id; -- Calculate total PO amount SELECT COALESCE(SUM(total), 0) INTO v_po_amount FROM purchase_order_items WHERE purchase_order_id = p_purchase_order_id; -- Generate IDs SET v_gl_header_id = UUID(); SET v_ap_transaction_id = UUID(); SET v_journal_entry_id = CONCAT('JE-', v_po_number); -- 1. Create GL Transaction Header INSERT INTO gl_transaction_header ( gl_transaction_header_id, account_id, workspace_id, control_unit_id, journal_entry_id, document_number, document_type, transaction_source, transaction_type, transaction_date, posting_date, gl_date, description, total_debit_amount, total_credit_amount, functional_currency_id, status, created_by, posted_by, posted_at ) VALUES ( v_gl_header_id, v_account_id, v_workspace_id, v_control_unit_id, v_journal_entry_id, v_po_number, 'Purchase Order', 'AP_MODULE', 'Expense', v_po_date, v_po_date, v_po_date, CONCAT('Purchase Order posting for ', v_po_number), v_po_amount, v_po_amount, v_currency_id, 'POSTED', p_posted_by, p_posted_by, NOW() ); -- 2. Create GL Transaction Lines - Debit Expense (for each PO item) INSERT INTO gl_transaction_lines ( gl_transaction_line_id, gl_transaction_header_id, account_id, workspace_id, control_unit_id, chart_of_account_id, line_number, transaction_type, debit_amount, credit_amount, source_entity_type, source_entity_id, source_line_id, transaction_date, posting_date, description, created_by, posted_by, posted_at ) SELECT UUID(), v_gl_header_id, v_account_id, v_workspace_id, v_control_unit_id, COALESCE(poi.gl_expense_account_id, v_expense_account_id), ROW_NUMBER() OVER (ORDER BY poi.item_sequence), 'DEBIT', poi.total, 0, 'PO_ITEM', p_purchase_order_id, poi.po_item_id, v_po_date, v_po_date, CONCAT('Expense for ', poi.job_cost_item_name), p_posted_by, p_posted_by, NOW() FROM purchase_order_items poi WHERE poi.purchase_order_id = p_purchase_order_id; -- 3. Create GL Transaction Lines - Credit AP INSERT INTO gl_transaction_lines ( gl_transaction_line_id, gl_transaction_header_id, account_id, workspace_id, control_unit_id, chart_of_account_id, line_number, transaction_type, debit_amount, credit_amount, source_entity_type, source_entity_id, customer_vendor_id, transaction_date, posting_date, description, created_by, posted_by, posted_at ) VALUES ( UUID(), v_gl_header_id, v_account_id, v_workspace_id, v_control_unit_id, v_ap_account_id, (SELECT COUNT(*) FROM purchase_order_items WHERE purchase_order_id = p_purchase_order_id) + 1, 'CREDIT', 0, v_po_amount, 'PURCHASE_ORDER', p_purchase_order_id, v_supplier_id, v_po_date, v_po_date, CONCAT('AP for PO ', v_po_number), p_posted_by, p_posted_by, NOW() ); -- 4. Create AP Transaction INSERT INTO ap_transactions ( ap_transaction_id, account_id, workspace_id, control_unit_id, transaction_number, transaction_type, supplier_id, purchase_order_id, transaction_date, due_date, currency_id, transaction_amount, outstanding_amount, paid_amount, status, gl_transaction_header_id, ap_account_id, expense_account_id, created_by ) VALUES ( v_ap_transaction_id, v_account_id, v_workspace_id, v_control_unit_id, v_po_number, 'PURCHASE_ORDER', v_supplier_id, p_purchase_order_id, v_po_date, DATE_ADD(v_po_date, INTERVAL 30 DAY), v_currency_id, v_po_amount, v_po_amount, 0, 'OPEN', v_gl_header_id, v_ap_account_id, v_expense_account_id, p_posted_by ); -- 5. Update Purchase Order with GL reference UPDATE purchase_order SET gl_transaction_header_id = v_gl_header_id, is_posted_to_gl = 1, gl_posted_date = NOW(), gl_posted_by = p_posted_by WHERE purchase_order_id = p_purchase_order_id; -- 6. Update Purchase Order Items posting status UPDATE purchase_order_items SET is_posted_to_gl = 1 WHERE purchase_order_id = p_purchase_order_id; COMMIT; SELECT 'SUCCESS' as status, v_gl_header_id as gl_header_id, v_ap_transaction_id as ap_transaction_id; END$DELIMITER ;-- Procedure 3: Record AR PaymentDELIMITER $DROP PROCEDURE IF EXISTS record_ar_payment$CREATE PROCEDURE record_ar_payment( IN p_ar_transaction_id CHAR(36), IN p_payment_amount DECIMAL(15,2), IN p_payment_date DATE, IN p_payment_method VARCHAR(50), IN p_reference_number VARCHAR(100), IN p_cash_account_id CHAR(36), IN p_created_by CHAR(36))BEGIN DECLARE v_ar_payment_id CHAR(36); DECLARE v_gl_header_id CHAR(36); DECLARE v_payment_number VARCHAR(50); DECLARE v_customer_id CHAR(36); DECLARE v_ar_account_id CHAR(36); DECLARE v_account_id CHAR(36); DECLARE v_workspace_id CHAR(36); DECLARE v_control_unit_id CHAR(36); DECLARE v_currency_id INT; DECLARE v_current_outstanding DECIMAL(15,2); DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END; START TRANSACTION; -- Get AR transaction details SELECT customer_id, ar_account_id, account_id, workspace_id, control_unit_id, currency_id, outstanding_amount INTO v_customer_id, v_ar_account_id, v_account_id, v_workspace_id, v_control_unit_id, v_currency_id, v_current_outstanding FROM ar_transactions WHERE ar_transaction_id = p_ar_transaction_id; -- Generate IDs SET v_ar_payment_id = UUID(); SET v_gl_header_id = UUID(); SET v_payment_number = CONCAT('PAY-AR-', DATE_FORMAT(p_payment_date, '%Y%m%d'), '-', SUBSTRING(v_ar_payment_id, 1, 8)); -- 1. Create GL Transaction Header for Payment INSERT INTO gl_transaction_header ( gl_transaction_header_id, account_id, workspace_id, control_unit_id, journal_entry_id, document_number, document_type, transaction_source, transaction_type, transaction_date, posting_date, gl_date, description, total_debit_amount, total_credit_amount, functional_currency_id, status, created_by, posted_by, posted_at ) VALUES ( v_gl_header_id, v_account_id, v_workspace_id, v_control_unit_id, v_payment_number, v_payment_number, 'AR Payment', 'AR_MODULE', 'Receipt', p_payment_date, p_payment_date, p_payment_date, CONCAT('Customer payment received - ', p_reference_number), p_payment_amount, p_payment_amount, v_currency_id, 'POSTED', p_created_by, p_created_by, NOW() ); -- 2. Create GL Lines - Debit Cash INSERT INTO gl_transaction_lines ( gl_transaction_line_id, gl_transaction_header_id, account_id, workspace_id, control_unit_id, chart_of_account_id, line_number, transaction_type, debit_amount, credit_amount, source_entity_type, source_entity_id, customer_vendor_id, transaction_date, posting_date, description, created_by, posted_by, posted_at ) VALUES ( UUID(), v_gl_header_id, v_account_id, v_workspace_id, v_control_unit_id, p_cash_account_id, 1, 'DEBIT', p_payment_amount, 0, 'AR_PAYMENT', v_ar_payment_id, v_customer_id, p_payment_date, p_payment_date, CONCAT('Cash received - ', p_payment_method), p_created_by, p_created_by, NOW() ); -- 3. Create GL Lines - Credit AR INSERT INTO gl_transaction_lines ( gl_transaction_line_id, gl_transaction_header_id, account_id, workspace_id, control_unit_id, chart_of_account_id, line_number, transaction_type, debit_amount, credit_amount, source_entity_type, source_entity_id, customer_vendor_id, transaction_date, posting_date, description, created_by, posted_by, posted_at ) VALUES ( UUID(), v_gl_header_id, v_account_id, v_workspace_id, v_control_unit_id, v_ar_account_id, 2, 'CREDIT', 0, p_payment_amount, 'AR_PAYMENT', v_ar_payment_id, v_customer_id, p_payment_date, p_payment_date, 'AR payment received', p_created_by, p_created_by, NOW() ); -- 4. Create AR Payment Record INSERT INTO ar_payments ( ar_payment_id, account_id, workspace_id, control_unit_id, payment_number, ar_transaction_id, customer_id, payment_date, payment_amount, payment_method, reference_number, gl_transaction_header_id, cash_account_id, ar_account_id, created_by ) VALUES ( v_ar_payment_id, v_account_id, v_workspace_id, v_control_unit_id, v_payment_number, p_ar_transaction_id, v_customer_id, p_payment_date, p_payment_amount, p_payment_method, p_reference_number, v_gl_header_id, p_cash_account_id, v_ar_account_id, p_created_by ); -- 5. Update AR Transaction UPDATE ar_transactions SET paid_amount = paid_amount + p_payment_amount, outstanding_amount = outstanding_amount - p_payment_amount, status = CASE WHEN outstanding_amount - p_payment_amount <= 0 THEN 'PAID' ELSE 'PARTIAL_PAID' END, updated_at = NOW() WHERE ar_transaction_id = p_ar_transaction_id; COMMIT; SELECT 'SUCCESS' as status, v_ar_payment_id as payment_id, v_gl_header_id as gl_header_id; END$DELIMITER ;-- Procedure 4: Record AP PaymentDELIMITER $DROP PROCEDURE IF EXISTS record_ap_payment$CREATE PROCEDURE record_ap_payment( IN p_ap_transaction_id CHAR(36), IN p_payment_amount DECIMAL(15,2), IN p_payment_date DATE, IN p_payment_method VARCHAR(50), IN p_reference_number VARCHAR(100), IN p_cash_account_id CHAR(36), IN p_created_by CHAR(36))BEGIN DECLARE v_ap_payment_id CHAR(36); DECLARE v_gl_header_id CHAR(36); DECLARE v_payment_number VARCHAR(50); DECLARE v_supplier_id CHAR(36); DECLARE v_ap_account_id CHAR(36); DECLARE v_account_id CHAR(36); DECLARE v_workspace_id CHAR(36); DECLARE v_control_unit_id CHAR(36); DECLARE v_currency_id INT; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END; START TRANSACTION; -- Get AP transaction details SELECT supplier_id, ap_account_id, account_id, workspace_id, control_unit_id, currency_id INTO v_supplier_id, v_ap_account_id, v_account_id, v_workspace_id, v_control_unit_id, v_currency_id FROM ap_transactions WHERE ap_transaction_id = p_ap_transaction_id; -- Generate IDs SET v_ap_payment_id = UUID(); SET v_gl_header_id = UUID(); SET v_payment_number = CONCAT('PAY-AP-', DATE_FORMAT(p_payment_date, '%Y%m%d'), '-', SUBSTRING(v_ap_payment_id, 1, 8)); -- 1. Create GL Transaction Header for Payment INSERT INTO gl_transaction_header ( gl_transaction_header_id, account_id, workspace_id, control_unit_id, journal_entry_id, document_number, document_type, transaction_source, transaction_type, transaction_date, posting_date, gl_date, description, total_debit_amount, total_credit_amount, functional_currency_id, status, created_by, posted_by, posted_at ) VALUES ( v_gl_header_id, v_account_id, v_workspace_id, v_control_unit_id, v_payment_number, v_payment_number, 'AP Payment', 'AP_MODULE', 'Payment', p_payment_date, p_payment_date, p_payment_date, CONCAT('Supplier payment made - ', p_reference_number), p_payment_amount, p_payment_amount, v_currency_id, 'POSTED', p_created_by, p_created_by, NOW() ); -- 2. Create GL Lines - Debit AP INSERT INTO gl_transaction_lines ( gl_transaction_line_id, gl_transaction_header_id, account_id, workspace_id, control_unit_id, chart_of_account_id, line_number, transaction_type, debit_amount, credit_amount, source_entity_type, source_entity_id, customer_vendor_id, transaction_date, posting_date, description, created_by, posted_by, posted_at ) VALUES ( UUID(), v_gl_header_id, v_account_id, v_workspace_id, v_control_unit_id, v_ap_account_id, 1, 'DEBIT', p_payment_amount, 0, 'AP_PAYMENT', v_ap_payment_id, v_supplier_id, p_payment_date, p_payment_date, 'AP payment made', p_created_by, p_created_by, NOW() ); -- 3. Create GL Lines - Credit Cash INSERT INTO gl_transaction_lines ( gl_transaction_line_id, gl_transaction_header_id, account_id, workspace_id, control_unit_id, chart_of_account_id, line_number, transaction_type, debit_amount, credit_amount, source_entity_type, source_entity_id, customer_vendor_id, transaction_date, posting_date, description, created_by, posted_by, posted_at ) VALUES ( UUID(), v_gl_header_id, v_account_id, v_workspace_id, v_control_unit_id, p_cash_account_id, 2, 'CREDIT', 0, p_payment_amount, 'AP_PAYMENT', v_ap_payment_id, v_supplier_id, p_payment_date, p_payment_date, CONCAT('Cash paid - ', p_payment_method), p_created_by, p_created_by, NOW() ); -- 4. Create AP Payment Record INSERT INTO ap_payments ( ap_payment_id, account_id, workspace_id, control_unit_id, payment_number, ap_transaction_id, supplier_id, payment_date, payment_amount, payment_method, reference_number, gl_transaction_header_id, cash_account_id, ap_account_id, created_by ) VALUES ( v_ap_payment_id, v_account_id, v_workspace_id, v_control_unit_id, v_payment_number, p_ap_transaction_id, v_supplier_id, p_payment_date, p_payment_amount, p_payment_method, p_reference_number, v_gl_header_id, p_cash_account_id, v_ap_account_id, p_created_by ); -- 5. Update AP Transaction UPDATE ap_transactions SET paid_amount = paid_amount + p_payment_amount, outstanding_amount = outstanding_amount - p_payment_amount, status = CASE WHEN outstanding_amount - p_payment_amount <= 0 THEN 'PAID' ELSE 'PARTIAL_PAID' END, updated_at = NOW() WHERE ap_transaction_id = p_ap_transaction_id; COMMIT; SELECT 'SUCCESS' as status, v_ap_payment_id as payment_id, v_gl_header_id as gl_header_id; END$DELIMITER ;-- Example 1: Post an Invoice to AR/GLCALL post_invoice_to_ar_gl('your-invoice-id-here', 'user-id-who-posted');-- Example 2: Post a Purchase Order to AP/GL CALL post_purchase_order_to_ap_gl('your-po-id-here', 'user-id-who-posted');-- Example 3: Record AR PaymentCALL record_ar_payment( 'ar-transaction-id', 1500.00, '2025-02-01', 'Bank Transfer', 'REF123456', 'cash-account-id', 'user-id');-- Example 4: Record AP PaymentCALL record_ap_payment( 'ap-transaction-id', 2500.00, '2025-02-01', 'Check', 'CHK789012', 'cash-account-id', 'user-id');here iam sharing script for that i need to generate screens erp sytem already generate please fo enhance make it work screens

Key Enhancements Made:

1. API Integration

2. Real Database Integration

3. Payment Recording

4. Enhanced GL Posting

5. Database Schema Compliance

To Complete the Integration:

Key Accounting Improvements:

1. Pre-Posting Validation

2. Tax Handling

3. AR Aging Report

4. Enhanced Invoice Details

5. Accounting Controls

What Makes This Accounting-Compliant: