test

Sql code posted
created at 24 Mar 06:59

Edit | Back 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
 
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 $$
10.5 KB in 6 ms with coderay