rRLLRDWs

1DELIMITER$$
2
3CREATE procedure income_statement_consolidated_proc(IN company_name_in varchar(100))
4BEGIN
5
6	DECLARE record_not_found INTEGER DEFAULT 0;
7	
8	DECLARE company_name_val varchar(100);
9	DECLARE revenue_sales_val decimal(15,2);
10	DECLARE service_revenue_val  decimal(15,2);
11	DECLARE neg_excise_duty_sales_srv_tax_val decimal(15,2);
12	DECLARE work_in_progress_finished_goods_val  decimal(15,2);
13	DECLARE cost_of_revenue_val decimal(15,2);
14	DECLARE cost_of_trading_units_val	decimal(15,2);
15	DECLARE engineering_exp_val decimal(15,2);
16	DECLARE other_exp_of_revenue_val decimal(15,2);
17	
18	DECLARE Revenue_val decimal(15,2);
19	DECLARE Expenses_val decimal(15,2);
20	DECLARE Gross_Profit_val decimal(15,2);
21	
22	DECLARE income_statement_consolidated_cur CURSOR for
23	SELECT * FROM income_statement_consolidated
24	WHERE company_name = company_name_in;
25	
26	DECLARE CONTINUE HANDLER FOR NOT FOUND SET record_not_found = 1;
27	
28	DROP TEMPORARY TABLE IF EXISTS income_statement_consolidated_report;
29	
30	CREATE TEMPORARY TABLE income_statement_consolidated_report
31	(
32		revenue_sales	decimal(15,2),    
33		service_revenue  decimal(15,2),    
34		neg_excise_duty_sales_srv_tax decimal(15,2),  
35		work_in_progress_finished_goods  decimal(15,2),	
36		revenue decimal(15,2),
37		cost_of_revenue decimal(15,2),   			
38		cost_of_trading_units	decimal(15,2),		
39		engineering_exp decimal(15,2),				
40		other_exp_of_revenue decimal(15,2),	
41		expenses decimal(15,2),
42		gross_profit decimal(15,2)
43	);
44	
45	OPEN income_statement_consolidated_cur;
46	
47	report: LOOP
48		
49		FETCH income_statement_consolidated_cur
50		INTO company_name_val, 
51			 revenue_sales_val,
52			 service_revenue_val,
53			 neg_excise_duty_sales_srv_tax_val,
54			 work_in_progress_finished_goods_val,
55			 cost_of_revenue_val,
56			 cost_of_trading_units_val,
57			 engineering_exp_val,
58			 other_exp_of_revenue_val;
59	
60	IF record_not_found
61	THEN 
62		LEAVE report; 
63	END IF; 
64		
65		SET Revenue_val := revenue_sales_val + service_revenue_val + neg_excise_duty_sales_srv_tax_val + work_in_progress_finished_goods_val;
66		SET Expenses_val := cost_of_revenue_val + cost_of_trading_units_val + engineering_exp_val + other_exp_of_revenue_val;
67		SET Gross_Profit_val := Revenue_val - Expenses_val;
68		
69		INSERT INTO income_statement_consolidated_report VALUES (revenue_sales_val,service_revenue_val,neg_excise_duty_sales_srv_tax_val,work_in_progress_finished_goods_val,Revenue_val,cost_of_revenue_val,cost_of_trading_units_val,engineering_exp_val,other_exp_of_revenue_val,Expenses_val,Gross_Profit_val);
70		
71	END LOOP report;    
72        CLOSE income_statement_consolidated_cur; 
73		
74	SELECT * FROM income_statement_consolidated_report;
75	DROP TABLE income_statement_consolidated_report;
76	
77END
78$$
79
80DELIMITER;