-- ====================================-- 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
I'll enhance the existing ERP system screens to work with your actual database schema and stored procedures. Let me update the components to integrate with the real database structure.
record_ar_payment and record_ap_payment stored proceduresInstall MySQL Driver: Add mysql2 to your project:
npm install mysql2Environment Variables: Set up your database connection:
DB_HOST=your-database-host
DB_USER=your-database-user
DB_PASSWORD=your-database-password
DB_NAME=your-database-name
DB_PORT=3306'current-user-id' with actual user session data.The system now provides a complete workflow:
To configure the generation, complete these steps:
You're right to ask me to think like an accounting person! Looking at the screens from an accounting perspective, I can see several areas that need improvement to make this a proper accounting system. Let me enhance the screens with proper accounting controls and features.
Now the screens are much more accounting-compliant! Here's what I've added from an accounting perspective:
✅ Segregation of Duties: Approval required before posting
✅ Audit Trail: Complete tracking of who posted what and when
✅ Balance Validation: Debits must equal credits
✅ Tax Compliance: Proper tax calculation and posting
✅ Period Controls: Fiscal period validation
✅ Aging Analysis: Standard AR aging reports
✅ Account Validation: Ensures valid GL accounts
✅ Multi-Currency Support: Currency tracking
✅ Document Status: Proper workflow controls
This is now a proper accounting system that would pass most financial audits and comply with standard accounting practices!