CREATE OR REPLACE procedure getall
(
i_sales_pnt_id 	sales_pnt.pk_sales_pnt_id%type,
i_tran_date		date,
i_volt_tp_id   	cust.volt_tp_id%type,
o_db_cur 		OUT BSAReport_pkg.ref_db_cur
)
as
v_start_date 			 date;
v_end_date 				 date;
v_inputted_date 		 date;
v_prev_month_consumption number;
v_curr_month_consumption number;
v_count 				 number;
begin
v_start_date := ADD_MONTHS(TO_DATE('01-'||TO_CHAR(i_tran_date,'MON-YYYY'),'DD-MON-YYYY'),-2);
v_end_date := LAST_DAY(TO_DATE('01-'||TO_CHAR(i_tran_date,'MON-YYYY')));
v_inputted_date := TO_DATE('01-'||TO_CHAR(i_tran_date,'MON-YYYY'),'DD-MON-YYYY');
select count(1) into v_count from billing_statistics where inputted_date = v_inputted_date
and sales_pnt_id = i_sales_pnt_id
and volt_tp_id = i_volt_tp_id;
If v_count = 0 Then
	insert into bill_gen_temp
	(pk_bill_Generated_id,
	tp,
	id,
	bill_tp_id,
	bill_sts_id,
	TOT_WITHOUT_TAX)
select
	pk_bill_Generated_id,
	tp,
	id,
	bill_tp_id,
	bill_sts_id,
	TOT_WITHOUT_TAX
        where c.fk_sales_pnt_id = i_sales_pnt_id
	union all
	select
	pk_bill_Generated_id,
	tp,
	id,
	bill_tp_id,
	bill_sts_id,
	TOT_WITHOUT_TAX    
where and e.fk_sales_pnt_id = i_sales_pnt_id;
insert into meter_reading_temp
	(
	  PK_METER_READING_ID,
	  FK_CUST_ID,
	  FK_SALES_PNT_ID)

	select
	  mr.PK_METER_READING_ID,
	  mr.FK_CUST_ID,
	  mr.FK_SALES_PNT_ID
          and c.fk_sales_pnt_id = i_sales_pnt_id;    

commit;
while (v_start_date <= v_end_date)
loop
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Nombre d''abonnements payés et PL non pris en facturation' as section,
	v_start_date transaction_date,
	count(1) tot_consmptn
	from cust c where subscrptn_sts_id = 12	and c.volt_tp_id = 1
	and not exists (select 1 from bill_gen_temp where
					to_dt <= to_date(TO_CHAR(LAST_DAY(v_start_date),'DD-MON-YYYY')||' 23:59:59','dd-mon-yyyy hh24:mi:ss')
					and from_dt >= to_date(v_start_date||' 00:00:00','dd-mon-yyyy hh24:mi:ss')
					and id = c.pk_cust_id
					and bill_tp_id = 14
					and tp = 'CUSTOMER'
				   )
	and exists (select 1 from bill_gen_temp where
					bill_sts_id = 1
					and to_dt <= to_date(TO_CHAR(LAST_DAY(ADD_MONTHS(v_start_date,-1)),'DD-MON-YYYY')||' 23:59:59','dd-mon-yyyy hh24:mi:ss')
					and from_dt >= to_date(ADD_MONTHS(v_start_date,-1)||' 00:00:00','dd-mon-yyyy hh24:mi:ss')
					and id = c.pk_cust_id
					and bill_tp_id = 14
					and tp = 'CUSTOMER'
				   )
	and	commit_ts between to_date(v_start_date||' 00:00:00','dd-mon-yyyy hh24:mi:ss')
				and to_date(TO_CHAR(LAST_DAY(v_start_date),'DD-MON-YYYY')||' 23:59:59','dd-mon-yyyy hh24:mi:ss')
	and c.fk_sales_pnt_id = i_sales_pnt_id;
end loop;
End If; --If v_count = 0 Then
	If i_volt_tp_id = 1 Then
		open o_db_cur for
		select t.section,
		(select result_value from billing_statistics where TO_CHAR(TRANSACTION_DATE,'MON') = 'JAN'
					 and section = t.section
					 and volt_tp_id = i_volt_tp_id
					 and inputted_date = v_inputted_date
					 and sales_pnt_id = i_sales_pnt_id
		) as JAN,
		(select result_value from billing_statistics where TO_CHAR(TRANSACTION_DATE,'MON') = 'FEB'
					 and section = t.section
					 and volt_tp_id = i_volt_tp_id
					 and inputted_date = v_inputted_date
		) as FEB;

End If;
update 	billing_statistics
	set 	volt_tp_id = 1,
			inputted_date = v_inputted_date,
			sales_pnt_id = i_sales_pnt_id
	where 	inputted_date IS NULL;
	commit;
end getall;
/