· 9 years ago · Feb 01, 2017, 03:10 PM
1DELIMITER $$
2
3DROP PROCEDURE IF EXISTS usp_calculate_invoice_value_by_project $$
4
5/*
6 todo:
7 - include hold
8
9*/
10
11CREATE PROCEDURE usp_calculate_invoice_value_by_project
12(
13 in _tenant_id int,
14 in _session_user_id int,
15 in _project_id int,
16 in _invoicing_elements int,
17 in _include_hold int,
18 in _date_from date,
19 in _date_to date,
20 in _invoice_date date,
21 out _out_invoice_value decimal(10,2),
22 out _out_invoice_currency char(3),
23 out _out_invoice_value_rc decimal(10,2),
24 out _out_invoice_reporting_currency char(3),
25 out _debugger blob
26
27)
28 LANGUAGE SQL
29 DETERMINISTIC
30 CONTAINS SQL
31BEGIN
32
33 /* _InvoicingElements
34 0 = service only
35 1 = all
36 2 = expense only
37 */
38 declare _parent_id int;
39 declare _has_billing_rules int;
40 declare _calculation_project_id int;
41 declare _has_hourly_rates int;
42 declare _with_subscription int;
43 declare _final_service_value decimal(10,2);
44
45 declare _customer_id int;
46 declare _parent_project_id int;
47 declare _project_type varchar(100);
48
49 declare _legal_currency char(3);
50 declare _reporting_currency char(3);
51 declare _invoice_currency char(3);
52 declare _project_currency char(3);
53 declare _legal_currency_rate decimal(10, 4);
54 declare _project_currency_rate decimal(10, 4);
55 declare _reporting_currency_rate decimal(10, 4);
56 declare _default_discount_percent decimal(10,2);
57
58 declare _calculation_global_fee decimal(10,2);
59 declare _calculation_timesheet_value decimal(10,2);
60 declare _calculation_expense_value decimal(10,4);
61 declare _calculation_effort decimal(10,2);
62 declare _calculation_blb_effort decimal(10,2);
63 declare _cap_value decimal(10,2);
64 declare _prev_invoiced_value decimal(10,2);
65
66 declare _prev_advance_value decimal(10,2);
67 declare _prev_returned_advance_value decimal(10,2);
68 declare _prev_expense_advance_value decimal(10,2);
69 declare _prev_returned_expense_advance_value decimal(10,2);
70
71 declare _new_advance decimal(10,2);
72 declare _expense_value decimal(10,4);
73
74 declare _output_effort decimal(10,2);
75 declare _output_blb_effort decimal(10,2);
76 declare _output_service_value DECIMAL(10,2);
77 declare _output_discount_value decimal(10,2);
78 declare _output_discount_to_cap_value decimal(10,2);
79 declare _output_expense_value DECIMAL(10,2);
80 declare _output_total_invoice_value DECIMAL(10,2);
81 declare _output_advance_value decimal(10,2);
82 declare _output_returned_advance_value decimal(10,2);
83 declare _final_invoice_value decimal(10,2);
84 declare _subscription_amount decimal(10,2);
85
86 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
87
88 /*=====================================================
89 * DATA PREPARATION
90 *=====================================================*/
91
92 -- get tenant params
93 SELECT t.reporting_currency, t.legal_currency
94 INTO _reporting_currency, _legal_currency
95 FROM tenant t
96 WHERE t.tenant_id = _tenant_id;
97
98 /* get project type */
99 select
100 project_type, parent_id, has_billing_rules,
101 case when p.has_billing_rules = 0 then parent_id else project_id end as calculation_project_id
102 into _project_type, _parent_id, _has_billing_rules, _calculation_project_id
103 from project p
104 where project_id = _project_id and tenant_id = _tenant_id;
105
106
107 /* get customer data and currency */
108 if (_project_type = 'project' ) then
109 SELECT
110 p.customer_id, fe.invoice_currency, p.currency
111 INTO _customer_id, _invoice_currency, _project_currency
112 FROM project p
113 join customer cust on cust.customer_id = p.customer_id
114 join customer_fiscal_entity_ext fe on fe.customer_fiscal_entity_id = p.customer_fiscal_entity_id
115 WHERE 1 = 1
116 AND p.project_id = _project_id
117 AND p.tenant_id = _tenant_id;
118 else
119 SELECT
120 p.customer_id, fe.invoice_currency, p.currency
121 INTO _customer_id, _invoice_currency, _project_currency
122 FROM project p
123 join customer cust on cust.customer_id = p.customer_id
124 join customer_fiscal_entity_ext fe on fe.customer_fiscal_entity_id = p.customer_fiscal_entity_id
125 WHERE 1 = 1
126 AND p.project_id = _parent_id
127 AND p.tenant_id = _tenant_id;
128 end if;
129
130 -- calculate if there are any hourly rates
131 set _has_hourly_rates = (
132 select count(*)
133 from project p
134 join billing_rule br on br.project_id = p.project_id
135 where 1=1
136 and p.project_id = _calculation_project_id
137 and p.tenant_id = _tenant_id
138 and br.rule_type in ('HOURLY_BLENDED', 'HOURLY_SENIORITY', 'HOURLY_USER', 'HOURLY_ACTIVITY', 'HOURLY_THRESHOLD')
139 );
140
141 -- currency rates
142 SET _legal_currency_rate = (SELECT fn_get_rate(_tenant_id, _legal_currency, _invoice_currency, _invoice_date));
143 SET _project_currency_rate = (SELECT fn_get_rate(_tenant_id, _project_currency, _invoice_currency, _invoice_date));
144 SET _reporting_currency_rate = (SELECT fn_get_rate(_tenant_id, _reporting_currency, _invoice_currency, _invoice_date));
145
146 drop table if exists tmp_projectlist;
147 create temporary table tmp_projectlist (
148 project_id int,
149 reference_id int
150 ) ENGINE=MEMORY;
151
152 if(_project_type = 'project') then
153 /* calculate for all projects within the contract without special invoiving rule */
154 insert into tmp_projectlist (project_id, reference_id)
155 select _project_id, project_id as reference_id
156 from project p
157 where 1=1
158 AND p.tenant_id = _tenant_id
159 AND p.parent_id = _project_id
160 AND p.enabled = 1
161 AND p.has_billing_rules = 0
162 AND p.has_separate_invoice = 0;
163 else
164 /* use only selected project */
165 insert into tmp_projectlist (project_id, reference_id)
166 values (_parent_id, _project_id);
167 end if;
168
169 -- get CAP and discount from project /reference level
170 if ( _has_billing_rules = 1 ) then
171 begin
172 -- get cap, if any
173 select `value`
174 into _cap_value
175 from billing_rule
176 where 1=1
177 and project_id = _calculation_project_id and tenant_id = _tenant_id
178 and rule_type = 'CAP';
179
180 -- get default discount
181 select `value`
182 into _default_discount_percent
183 from billing_rule
184 where 1=1
185 and project_id = _calculation_project_id and tenant_id = _tenant_id
186 and rule_type = 'DISCOUNT';
187
188 end;
189 end if;
190 set _cap_value = coalesce(_cap_value, 0);
191 set _default_discount_percent = coalesce(_default_discount_percent, 0);
192
193 -- get subscription, if any
194 select case when coalesce(`value`, 0) > 0 then 1 else 0 end
195 into _with_subscription
196 from billing_rule
197 where 1=1
198 and project_id = _calculation_project_id and tenant_id = _tenant_id
199 and rule_type = 'SUBSCRIPTION';
200
201 set _with_subscription = coalesce(_with_subscription, 0);
202
203 SET _debugger = CONCAT(
204 "<br /><b>Customer info</b>" ,
205 "<br />Customer ID : ", COALESCE(_customer_id, 'NULL'),
206 "<br />Invoice currency : ", COALESCE(_invoice_currency, 'NULL'),
207 "<br />Elements : ", COALESCE(_invoicing_elements, 'NULL'),
208 "<br />",
209 "<br /><b>Project info</b>" ,
210 "<br />Project ID: ", COALESCE(_project_id, 'NULL'),
211 "<br />Type: ", COALESCE(_project_type, 'NULL'),
212 "<br />Invoice date: ", COALESCE(_invoice_date, 'NULL'),
213 "<br />",
214 "<br /><b>Exchange rates</b>" ,
215 "<br />Legal currency: ", COALESCE(_legal_currency, 'NULL'),
216 "<br />Legal currency rate: ", COALESCE(_legal_currency_rate, 'NULL'),
217 "<br />Project currency: ", COALESCE(_project_currency, 'NULL'),
218 "<br />Project currency rate: ", COALESCE(_project_currency_rate, 'NULL'),
219 "<br />Reporting currency: ", COALESCE(_reporting_currency, 'NULL'),
220 "<br />Reporting currency rate: ", COALESCE(_reporting_currency_rate, 'NULL'),
221 "<br />",
222 "<br /><b>Invoice calculation data</b>"
223 );
224
225
226 /*==============================================
227 * previous invoiced value
228 *==============================================*/
229 select sum(base_amount_rc)
230 into _prev_invoiced_value
231 from invoice_detail id
232 join invoice i on i.invoice_id = id.invoice_id
233 where 1=1
234 and i.invoice_status_id in (2,4,5)
235 -- and i.source_document_id is null
236 and id.item_id in (1,3) -- service, discount
237 and id.tenant_id = _tenant_id
238 and id.source_project_id = _project_id;
239 set _prev_invoiced_value = coalesce(_prev_invoiced_value, 0);
240
241
242
243 /***********************************************************************************
244 * calculate fee and expense advances and returns
245 * return output to the out variables, to be used further down
246 *
247 **********************************************************************************/
248 call usp_create_invoice_calculate_advance(
249 _tenant_id,
250 _session_user_id,
251 _project_id,
252 null,
253 _prev_advance_value,
254 _prev_returned_advance_value,
255 _prev_expense_advance_value,
256 _prev_returned_expense_advance_value);
257
258
259 /*==============================================
260 * timesheet values - only if _has_hourly_rates > 0
261 *==============================================*/
262 if ( _has_hourly_rates > 0 ) then
263 if ( coalesce(_include_hold, 0) > 0 ) then
264 -- all timesheets, with hold
265 select
266 -- round( sum(round(t.blb_effort /60, 4) * coalesce(ext.fee, 0)), 2 ),
267 round( sum(coalesce(ext.total_value, 0)), 2 ),
268 round( sum(t.total_effort) / 60, 4 ),
269 round( sum(t.blb_effort) / 60, 4 )
270 into _calculation_timesheet_value, _calculation_effort, _calculation_blb_effort
271 from timesheet t
272 left join timesheet_ext ext on t.timesheet_id = ext.timesheet_id
273 join tmp_projectlist prl on prl.reference_id = t.reference_id
274 where 1=1
275 and t.invoice_id is null
276 -- and t.project_id = _project_id
277 and t.date between _date_from and _date_to;
278 else
279 -- just timesheets without hold
280 select
281 -- round( sum(round(t.blb_effort /60, 4) * coalesce(ext.fee, 0)), 2 ),
282 round( sum(coalesce(ext.total_value, 0)), 2 ),
283 round( sum(t.total_effort) / 60, 4 ),
284 round( sum(t.blb_effort) / 60, 4 )
285 into _calculation_timesheet_value, _calculation_effort, _calculation_blb_effort
286 from timesheet t
287 left join timesheet_ext ext on t.timesheet_id = ext.timesheet_id
288 join tmp_projectlist prl on prl.reference_id = t.reference_id
289 where 1=1
290 and t.status_id <> 3 -- HOLD
291 and t.invoice_id is null
292 -- and t.project_id = _project_id
293 and t.date between _date_from and _date_to;
294 end if; -- coalesce(_include_hold, 0) > 0
295 end if; -- _has_hourly_rates > 0
296
297 set _calculation_timesheet_value = coalesce(_calculation_timesheet_value, 0);
298 set _calculation_effort = coalesce(_calculation_effort, 0);
299 set _calculation_blb_effort = coalesce(_calculation_blb_effort, 0);
300
301
302 /*==============================================
303 * value of global/success fees
304 *==============================================*/
305 if ( _has_billing_rules > 0 ) then
306 select sum( `value` )
307 into _calculation_global_fee
308 from billing_rule br
309 join project p on br.project_id = p.project_id
310 where 1=1
311 and br.project_id = _project_id and br.tenant_id = _tenant_id
312 and rule_type IN ('FLAT_FEE', 'SUCCESS_FEE')
313 and br.invoice_id is null
314 and br.value > 0
315 and br.date between _date_from and _date_to
316 and p.is_billable = 1
317 and p.enabled = 1;
318 end if;
319 set _calculation_global_fee = coalesce(_calculation_global_fee, 0);
320
321
322
323
324 /*==============================================
325 * payment in advance
326 *==============================================*/
327 if (_invoicing_elements in (6)) then
328 if ( _has_billing_rules > 0 ) then
329 select sum( `value` )
330 into _new_advance
331 from billing_rule br
332 join project p on br.project_id = p.project_id
333 where 1=1
334 and br.project_id = _project_id and br.tenant_id = _tenant_id
335 and rule_type IN ('FEE_ADVANCE', 'EXPENSE_ADVANCE')
336 and br.invoice_id is null
337 and br.value > 0
338 and br.date between _date_from and _date_to
339 and p.is_billable = 1
340 and p.enabled = 1;
341 end if;
342 end if;
343 set _new_advance = coalesce(_new_advance, 0);
344
345
346 /*==============================================
347 * subscription
348 *==============================================*/
349 if ( _with_subscription > 0 ) then
350 -- insert subscription line in table, if not existing
351 call usp_check_subscription( _tenant_id, _project_id, _date_from, _date_to );
352
353 -- get subscription value
354 select sum( rs.value )
355 into _subscription_amount
356 from billing_rule br
357 join project p on br.project_id = p.project_id
358 join rule_subscription rs on rs.billing_rule_id = br.billing_rule_id
359 where 1=1
360 and br.project_id = _project_id and br.tenant_id = _tenant_id
361 and rule_type IN ('SUBSCRIPTION')
362 and rs.invoice_id is null
363 and rs.value > 0
364 and rs.date between _date_from and _date_to
365 and p.is_billable = 1
366 and p.enabled = 1;
367 end if;
368 set _subscription_amount = coalesce(_subscription_amount, 0);
369
370 /*==============================================
371 * Expenses
372 *==============================================*/
373 if (_project_type = 'project' ) then
374 select
375 sum(round(e.base_amount * fn_get_rate(_tenant_id, e.currency, _project_currency, _invoice_date), 4))
376 into _calculation_expense_value
377 from expense e
378 join tmp_projectlist prl on prl.project_id = e.project_id
379 where 1=1
380 and e.is_billable = 1
381 and e.invoice_id is null
382 and e.date between _date_from and _date_to;
383 else
384 select
385 sum(round(e.base_amount * fn_get_rate(_tenant_id, e.currency, _project_currency, _invoice_date), 4))
386 into _calculation_expense_value
387 from expense e
388 join tmp_projectlist prl on prl.reference_id = e.reference_id
389 where 1=1
390 and e.is_billable = 1
391 and e.invoice_id is null
392 and e.date between _date_from and _date_to;
393 end if;
394
395
396
397 -- select _calculation_expense_value;
398
399 /*==============================================
400 * calculation of output values
401 *==============================================*/
402 IF ( _invoicing_elements = 1 ) THEN
403 -- only services
404 SET _output_effort = _calculation_effort, _output_blb_effort = _calculation_blb_effort;
405 SET _output_service_value = coalesce(_calculation_timesheet_value,0) + coalesce(_calculation_global_fee + coalesce(_subscription_amount, 0) ,0);
406 SET _output_discount_value =
407 CASE WHEN _default_discount_percent = 0
408 THEN 0
409 ELSE ROUND(_output_service_value * coalesce(_default_discount_percent, 0) / 100, 2)
410 END;
411 ELSEIF ( _invoicing_elements = 2 ) THEN
412 -- services and expenses
413 SET _output_effort = _calculation_effort, _output_blb_effort = _calculation_blb_effort;
414 SET _output_service_value = coalesce(_calculation_timesheet_value,0) + coalesce(_calculation_global_fee,0) + coalesce(_subscription_amount, 0);
415 SET _output_expense_value = _calculation_expense_value;
416 SET _output_discount_value =
417 CASE WHEN _default_discount_percent = 0
418 THEN 0
419 ELSE ROUND(_output_service_value * coalesce(_default_discount_percent, 0) / 100, 2)
420 END;
421 ELSEIF ( _invoicing_elements = 3 ) THEN
422 -- only expenses
423 SET _output_expense_value = _calculation_expense_value;
424 ELSEIF ( _invoicing_elements = 6 ) THEN
425 -- only expenses
426 SET _output_advance_value = _new_advance;
427 ELSE
428 -- else
429 SET _output_effort = _calculation_effort, _output_blb_effort = _calculation_blb_effort;
430 SET _output_service_value = coalesce(_calculation_timesheet_value,0) + coalesce(_calculation_global_fee,0);
431 SET _output_expense_value = _calculation_expense_value;
432 SET _output_discount_value =
433 CASE WHEN _default_discount_percent = 0
434 THEN 0
435 ELSE ROUND(_output_service_value * coalesce(_default_discount_percent, 0) / 100, 2)
436 END;
437 END IF;
438
439 SET _output_effort = coalesce(_output_effort, 0);
440 SET _output_blb_effort = coalesce(_output_blb_effort, 0);
441 SET _output_service_value = coalesce(_output_service_value, 0);
442 SET _output_expense_value = coalesce(_output_expense_value, 0);
443 SET _output_discount_value = coalesce(_output_discount_value, 0);
444
445 /*
446 * calculate actual service, considering the cap and previuously invoiced services
447 * if prev services + current service + discount > cap then add another line of discount to the cap value
448 */
449 IF (_cap_value > 0) AND (_cap_value - _prev_invoiced_value - _output_service_value + _output_discount_value < 0) THEN
450 SET _output_discount_to_cap_value = (-1) * (_cap_value - _prev_invoiced_value - _output_service_value + _output_discount_value);
451 ELSE
452 SET _output_discount_to_cap_value = 0;
453 END IF;
454
455 /*==============================================
456 * calculation of returned advance amount
457 * value is calculated as min amount of what was invoiced
458 * as advance and the current service amount on the invoice
459 *
460 *==============================================*/
461 IF (( _invoicing_elements = 1 ) OR ( _invoicing_elements = 2 )) THEN
462 IF (_prev_advance_value - _prev_returned_advance_value > 0) THEN
463 SET _output_returned_advance_value =
464 LEAST(
465 _prev_advance_value - _prev_returned_advance_value,
466 GREATEST(_output_service_value - _output_discount_value - _output_discount_to_cap_value, 0)
467 );
468 END IF;
469 END IF;
470 set _output_returned_advance_value = coalesce(_output_returned_advance_value, 0);
471
472 /* =======================================================
473 OUTPUT CALCULATIONS AND CURRENCY EXCHANGES
474 ========================================================*/
475
476 SET _final_invoice_value = GREATEST(_output_service_value + _output_expense_value - _output_discount_value - _output_discount_to_cap_value + _new_advance - _output_returned_advance_value, 0);
477 SET _final_service_value = GREATEST(_output_service_value - _output_discount_value - _output_discount_to_cap_value + _new_advance - _output_returned_advance_value, 0);
478
479
480 /* output invoice value in customer currency */
481 SET _out_invoice_currency = _invoice_currency;
482 SET _out_invoice_value = ROUND(_final_invoice_value * _project_currency_rate, 2);
483
484 /* output values RC are converted to reporting currency */
485 SET _out_invoice_reporting_currency = _reporting_currency;
486 SET _out_invoice_value_rc = ROUND(_final_invoice_value * _project_currency_rate / _reporting_currency_rate, 2);
487
488 SET _debugger = CONCAT(_debugger,
489 "<br />Invoicing elements : ", COALESCE(_invoicing_elements, 'null'),
490 "<br />Effort : ", COALESCE(_output_effort, 'null'),
491 "<br />Invoice effort : ", COALESCE(_output_blb_effort, 'null'),
492 "<br />Service value : ", COALESCE(_output_service_value, 'null'),
493 "<br />Expense value : ", COALESCE(_output_expense_value, 'null'),
494 "<br />Discount value : ", COALESCE(_output_discount_value, 'null'),
495 "<br />Cap : ", COALESCE(_cap_value, 'null'),
496 "<br />Previous effort value : ", COALESCE(_prev_invoiced_value, 'null'),
497 "<br />Additional discount to cap : ", COALESCE(_output_discount_to_cap_value, 'null'),
498 "<br />Prev advances : ", COALESCE(_prev_advance_value, 'null'),
499 "<br />Prev returned advances : ", COALESCE(_prev_returned_advance_value, 'null'),
500 "<br />New Advances : ", COALESCE(_new_advance, 'null'),
501 "<br />Returned advances : ", COALESCE(_output_returned_advance_value, 'null'),
502 "<br />Global fee : ", COALESCE(_calculation_global_fee, 'null'),
503 "<br />With subscription: ", COALESCE(_with_subscription, 'null'),
504 "<br />Subscription amount : ", COALESCE(_subscription_amount, 'null'),
505 "<br />Timesheet value : ", COALESCE(_calculation_timesheet_value, 'null'),
506 "<br />Additional discount to cap : ", COALESCE(_output_discount_to_cap_value, 'null'),
507 "<br />Final invoice value : ", COALESCE(_out_invoice_value, 'null'),
508 "<br />Customer currency : ", COALESCE(_out_invoice_currency, 'null'),
509 "<br />Reporting currency : ", COALESCE(_out_invoice_reporting_currency, 'null'),
510 "<br />Invoice in reporting currency : ", COALESCE(_out_invoice_value_rc, 'null')
511 );
512
513
514 -- select _project_type, _parent_id, _has_billing_rules, _calculation_project_id, _has_hourly_rates, _legal_currency, _project_currency, _reporting_currency, _invoice_currency, _reporting_currency_rate, _project_currency_rate, _legal_currency_rate, _calculation_timesheet_value, _calculation_effort, _calculation_blb_effort;
515 -- select * from tmp_projectlist;
516
517 drop table if exists tmp_projectlist;
518
519
520 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
521END;$$
522
523
524/*
525
526
527call usp_calculate_invoice_value_by_project (1, 1, 34652, 2, 0, '2017-01-01', '2017-01-31', '2017-02-01', @val, @curr, @val_rc, @curr_rc, @debug);
528select @val, @curr, @val_rc, @curr_rc, @debug;