Editing ray

Title / Description

Code
DELIMITER $$ DROP PROCEDURE IF EXISTS report_6213$$ CREATE PROCEDURE report_6213 ( IN p_org_id INT, IN p_beg_date DATE, IN p_end_date DATE ) BEGIN SELECT CASE WHEN t100.region_name IS NULL THEN 'Total' ELSE t100.region_name END AS 'Team', t100.invoices AS 'Invoices', t100.collected AS 'Collected ($)', t100.est_profit AS 'Est Profit ($)', t100.est_margin AS 'Est Margin' FROM ( SELECT t100.region_name, COUNT(*) AS invoices, ROUND(SUM(t100.billing_amt), 2) AS collected, ROUND(SUM(t100.billing_profit_amt) * .98) AS est_profit, CONCAT(ROUND(SUM(t100.billing_profit_amt) * .98 / SUM(t100.billing_amt) * 100, 1), '%') AS est_margin # *.98 profit to account for any items not yet costed which would inflate the profit amount in the report FROM ( SELECT DATE(t100.paid_on) AS paid_on, t100.gl_transaction_id AS so_transaction_id, #t100.extended_amt, #t100.inventory_amt, #t100.labor_amt, #t100.extended_amt - (t100.inventory_amt + t100.labor_amt) AS profit_amt, #ROUND(((t100.extended_amt - (t100.inventory_amt + t100.labor_amt)) / t100.extended_amt) * 100,1) AS profit_percent, t130.invoice_amt - t130.sales_tax_amt AS billing_amt, (t130.invoice_amt - t130.sales_tax_amt) - (t100.inventory_amt + t100.labor_amt) AS billing_profit_amt, ROUND((((t130.invoice_amt - t130.sales_tax_amt) - (t100.inventory_amt + t100.labor_amt)) / (t130.invoice_amt - t130.sales_tax_amt)) * 100, 1) AS billing_profit_percent, t120.region_name FROM ( SELECT t120.gl_transaction_id, SUM(t120.extended_amt) AS extended_amt, SUM(t100.inventory_amt) AS inventory_amt, SUM(t100.labor_amt) AS labor_amt, t100.paid_on FROM ( SELECT t100.gl_transaction_item_id, t100.total_amt AS inventory_amt, 0.00 AS labor_amt, t100.paid_on FROM ( SELECT t110.gl_transaction_item_id, SUM(t170.units) AS units, SUM(t170.total_amt) AS total_amt, t100.paid_on FROM ( SELECT t130.sales_order_ordered_id, t100.paid_on FROM ( SELECT t120.gl_transaction_total_id, t100.activity_date AS paid_on FROM gl_transaction t100 JOIN ar_payment_item t110 ON (t110.gl_transaction_id = t100.gl_transaction_id) JOIN gl_apply_item t120 ON (t120.gl_apply_item_id = t110.gl_apply_item_id) WHERE t100.activity_date BETWEEN p_beg_date AND p_end_date AND (is_last_applied(t120.gl_apply_item_id, t120.gl_transaction_total_id)) ) t100 JOIN ar_invoice t110 ON (t110.gl_transaction_id = t100.gl_transaction_total_id) JOIN ar_invoice_item t120 ON (t120.gl_transaction_id = t110.gl_transaction_id) JOIN sales_order_invoiced t130 ON (t130.gl_transaction_item_id = t120.gl_transaction_item_id) ) t100 JOIN sales_order_ordered t110 ON (t110.sales_order_ordered_id = t100.sales_order_ordered_id) JOIN sales_order_item t120 ON (t120.gl_transaction_item_id = t110.gl_transaction_item_id) JOIN sales_order_reserved t130 ON (t130.sales_order_ordered_id = t110.sales_order_ordered_id) JOIN sales_order_staged t140 ON (t140.sales_order_reserved_id = t130.sales_order_reserved_id) JOIN sales_order_delivered t150 ON (t150.sales_order_staged_id = t140.sales_order_staged_id) JOIN inventory_activity t160 ON (t160.activity_source_id = 300) AND (t160.activity_item_id = t150.sales_order_delivered_id) JOIN inventory_decrease t170 ON (t170.inventory_activity_id = t160.inventory_activity_id) GROUP BY t110.gl_transaction_item_id ) t100 UNION ALL SELECT t110.gl_transaction_item_id, 0.00 AS inventory_amt, t300.total_amt AS labor_amt, t100.paid_on FROM ( SELECT t130.sales_order_ordered_id, t100.paid_on FROM ( SELECT t120.gl_transaction_total_id, t100.activity_date AS paid_on FROM gl_transaction t100 JOIN ar_payment_item t110 ON (t110.gl_transaction_id = t100.gl_transaction_id) JOIN gl_apply_item t120 ON (t120.gl_apply_item_id = t110.gl_apply_item_id) WHERE t100.activity_date BETWEEN p_beg_date AND p_end_date AND (is_last_applied(t120.gl_apply_item_id, t120.gl_transaction_total_id)) ) t100 JOIN ar_invoice t110 ON (t110.gl_transaction_id = t100.gl_transaction_total_id) JOIN ar_invoice_item t120 ON (t120.gl_transaction_id = t110.gl_transaction_id) JOIN sales_order_invoiced t130 ON (t130.gl_transaction_item_id = t120.gl_transaction_item_id) ) t100 JOIN sales_order_ordered t110 ON (t110.sales_order_ordered_id = t100.sales_order_ordered_id) JOIN sales_order_item t120 ON (t120.gl_transaction_item_id = t110.gl_transaction_item_id) JOIN work_order_item t130 ON (t130.gl_transaction_item_id = t110.gl_transaction_item_id) JOIN product_supplier_cost t200 ON (t200.product_supplier_cost_id = t130.product_supplier_cost_id) JOIN unit_of_measure t210 ON (t210.unit_of_measure_id = t200.unit_of_measure_id) JOIN product t220 ON (t220.product_id = t210.product_id) JOIN product_type t230 ON (t230.product_type_id = t220.product_type_id) JOIN activity_type t240 ON (t240.activity_type_id = t230.activity_type_id) AND (NOT (t240.is_inventory)) JOIN work_order_completed_item t300 ON (t300.gl_transaction_item_id = t110.gl_transaction_item_id) ) t100 JOIN sales_order_item t120 ON (t120.gl_transaction_item_id = t100.gl_transaction_item_id) GROUP BY t120.gl_transaction_id ) t100 JOIN drf_view_sales_order_llc_team_region t110 ON (t110.gl_transaction_id = t100.gl_transaction_id) JOIN region t120 ON (t120.region_id = t110.llc_team_region_id) JOIN gl_transaction_total t130 ON (t130.gl_transaction_id = t100.gl_transaction_id) JOIN sales_order t140 ON (t140.gl_transaction_id = t100.gl_transaction_id) JOIN warehouse t150 ON (t150.warehouse_id = t140.warehouse_id) JOIN corporate t160 ON (t160.company_id = t150.company_id) WHERE t160.organization_id = p_org_id AND t160.company_id != 25074 #25074 RMIS ) t100 GROUP BY t100.region_name WITH ROLLUP )t100 ; END $$

Author

Highlight as C C++ CSS Clojure Delphi ERb Groovy (beta) HAML HTML JSON Java JavaScript PHP Plain text Python Ruby SQL XML YAML diff code