CREATE OR REPLACE procedure sales_pnt
(
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,
	tot_with_tax,
	TOT_CONSMPTN,
	exec_dt,
	from_dt,
	to_dt,
	CURR_MAX_LOAD_IND,
	BILL_CERT_DT,
	CUST_CAT_ID,
	CUST_ACTV_ID,
	VOLT_TP_ID
	)
	select
	pk_bill_Generated_id,
	tp,
	id,
	bill_tp_id,
	bill_sts_id,
	TOT_WITHOUT_TAX,
	tot_with_tax,
	TOT_CONSMPTN,
	exec_dt,
	from_dt,
	to_dt,
	null CURR_MAX_LOAD_IND,
	BG.BILL_CERT_DT,
	C.FK_CUST_CAT_ID,
	C.FK_CUST_ACTV_ID,
	C.VOLT_TP_ID
	from cust c, bill_generated bg
	where bg.actv_rec_in = 'Y'
	and bg.to_dt <= v_end_date
	and bg.from_dt >= v_start_date
	and bg.id = c.pk_cust_id
	and bg.tp IN ('CUSTOMER','REQUEST')
	and c.volt_tp_id = i_volt_tp_id
	and c.subscrptn_sts_id = 12
	and 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,
	tot_with_tax,
	TOT_CONSMPTN,
	exec_dt,
	from_dt,
	to_dt,
	null CURR_MAX_LOAD_IND,
	BG.BILL_CERT_DT,
	e.FK_CUST_CAT_ID,
	e.FK_CUST_ACTV_ID,
	e.VOLT_TP_ID
	from estimate e, bill_generated bg
	where bg.actv_rec_in = 'Y'
	and bg.to_dt <= v_end_date
	and bg.from_dt >= v_start_date
	and bg.id = e.pk_estimate_id
	and bg.tp = 'ESTIMATE'
	and e.fk_sales_pnt_id = i_sales_pnt_id;
	If i_volt_tp_id = 1 Then
		insert into bill_gen_temp
		(pk_bill_Generated_id,
		tp,
		id,
		bill_tp_id,
		bill_sts_id,
		TOT_WITHOUT_TAX,
		tot_with_tax,
		TOT_CONSMPTN,
		exec_dt,
		from_dt,
		to_dt,
		CURR_MAX_LOAD_IND,
		BILL_CERT_DT,
		CUST_CAT_ID,
		CUST_ACTV_ID,
		VOLT_TP_ID
		)
		select
		pk_bill_Generated_id,
		tp,
		id,
		bill_tp_id,
		bill_sts_id,
		TOT_WITHOUT_TAX,
		tot_with_tax,
		TOT_CONSMPTN,
		exec_dt,
		from_dt,
		to_dt,
		null CURR_MAX_LOAD_IND,
		BG.BILL_CERT_DT,
		null,
		null,
		i_volt_tp_id
		from preestimate e, bill_generated bg
		where bg.actv_rec_in = 'Y'
		and bg.to_dt <= v_end_date
		and bg.from_dt >= v_start_date
		and bg.id = e.pk_preestimate_id
		and bg.tp = 'PREESTIMATE'
		and e.fk_sales_pnt_id = i_sales_pnt_id;
	End If;
	commit;
	insert into bill_other_det_temp
	(
	fk_bill_Generated_id,
	chrg_tp,
	amt,
	tax,
	qty_or_unit
	)
	select
	fk_bill_Generated_id,
	chrg_tp,
	amt,
	tax,
	qty_or_unit
	from bill_other_det bod
	where exists (select 1 from bill_gen_temp where pk_bill_generated_id = bod.fk_bill_generated_id);
	insert into bill_coll_temp
	(
	fk_bill_Generated_id,
	fk_cust_id,
	cashing_mode,
	cashing_mode_nr,
	amt_paid,
	amt,
	BILL_COLLCTD_DT,
	vat
	)
	select
	fk_bill_Generated_id,
	fk_cust_id,
	cashing_mode,
	cashing_mode_nr,
	amt_paid,
	amt,
	BILL_COLLCTD_DT,
	vat
	from bill_collected bc
	where exists (select 1 from bill_gen_temp where pk_bill_generated_id = bc.fk_bill_generated_id);
	insert into meter_reading_temp
	(
	  PK_METER_READING_ID,
	  FK_CUST_ID,
	  FK_SALES_PNT_ID,
	  FK_DISTRICT_ID,
	  FK_ZONE_ID,
	  FK_BLOCK_ID,
	  FROM_DT,
	  TO_DT,
	  CURR_METER_READING,
	  PREV_METER_READING,
	  ACTV_REC_IN,
	  PREV_ACTV_IMP_OFF_PEAK,
	  CURR_ACTV_IMP_OFF_PEAK,
	  PREV_ACTV_IMP_PEAK,
	  CURR_ACTV_IMP_PEAK,
	  PREV_ACTV_EXP_OFF_PEAK,
	  CURR_ACTV_EXP_OFF_PEAK,
	  PREV_ACTV_EXP_PEAK,
	  CURR_ACTV_EXP_PEAK,
	  PREV_REACTV_IMP_OFF_PEAK,
	  CURR_REACTV_IMP_OFF_PEAK,
	  PREV_REACTV_IMP_PEAK,
	  CURR_REACTV_IMP_PEAK
	)
	select
	  mr.PK_METER_READING_ID,
	  mr.FK_CUST_ID,
	  mr.FK_SALES_PNT_ID,
	  mr.FK_DISTRICT_ID,
	  mr.FK_ZONE_ID,
	  mr.FK_BLOCK_ID,
	  mr.FROM_DT,
	  mr.TO_DT,
	  mr.CURR_METER_READING,
	  mr.PREV_METER_READING,
	  mr.ACTV_REC_IN,
	  mr.PREV_ACTV_IMP_OFF_PEAK,
	  mr.CURR_ACTV_IMP_OFF_PEAK,
	  mr.PREV_ACTV_IMP_PEAK,
	  mr.CURR_ACTV_IMP_PEAK,
	  mr.PREV_ACTV_EXP_OFF_PEAK,
	  mr.CURR_ACTV_EXP_OFF_PEAK,
	  mr.PREV_ACTV_EXP_PEAK,
	  mr.CURR_ACTV_EXP_PEAK,
	  mr.PREV_REACTV_IMP_OFF_PEAK,
	  mr.CURR_REACTV_IMP_OFF_PEAK,
	  mr.PREV_REACTV_IMP_PEAK,
	  mr.CURR_REACTV_IMP_PEAK
	from cust c, meter_reading mr
	where mr.to_dt <= v_end_date
	and mr.from_dt >= v_start_date
	and mr.fk_cust_id = c.pk_cust_id
	and c.volt_tp_id = i_volt_tp_id
	and c.subscrptn_sts_id = 12
	and c.fk_sales_pnt_id = i_sales_pnt_id;
	commit;
while (v_start_date <= v_end_date)
loop
	If i_volt_tp_id = 1 Then
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Ventes totales kWh' as section,
	v_start_date transaction_date,
	sum(tot_consmptn)
	from bill_Gen_temp bg
	where (
	bg.to_dt 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 bg.from_dt 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 bg.bill_tp_id in (10,11,13,14,19)
	and bg.tp='CUSTOMER';
	--- Residentiels < = 50 KWh
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Ventes totales KWh par tranches :  Résidentiels < = 50 KWh' SECTION,
	v_start_date transaction_date,
	sum(bod.qty_or_unit)
	from bill_gen_temp bg,bill_other_det_temp bod
	where
	bod.chrg_tp = 'Slab - 1'
	and bod.fk_bill_generated_id = bg.pk_bill_generated_id
	and bg.cust_actv_id in (100, 14, 15, 16, 31, 32, 33, 34, 37)
	and bg.bill_tp_id in (10,11,13,14,19)
	and bg.tp = 'CUSTOMER'
	and exec_dt 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');
	-- Residentiels > 50 KWh et <=200KWh
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Ventes totales KWh par tranches :  Résidentiels > 50 KWh et <=200KWh' SECTION,
	v_start_date transaction_date,
	sum(bod.qty_or_unit)
	from bill_gen_temp bg,bill_other_det_temp bod
	where
	bod.chrg_tp = 'Slab - 2'
	and bod.fk_bill_generated_id = bg.pk_bill_generated_id
	and bg.cust_actv_id in (100, 14, 15, 16, 31, 32, 33, 34, 37)
	and bg.bill_tp_id in (10,11,13,14,19)
	and bg.tp = 'CUSTOMER'
	and exec_dt 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');
	-- Residentiels > 200KWh
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Ventes totales KWh par tranches :  Résidentiels > 200KWh' SECTION,
	v_start_date transaction_date,
	sum(bod.qty_or_unit)
	from bill_gen_temp bg,bill_other_det_temp bod
	where
	bod.chrg_tp = 'Slab - 3'
	and bod.fk_bill_generated_id = bg.pk_bill_generated_id
	and bg.cust_actv_id in (100, 14, 15, 16, 31, 32, 33, 34, 37)
	and bg.bill_tp_id in (10,11,13,14,19)
	and bg.tp = 'CUSTOMER'
	and exec_dt 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');
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Ventes totales KWh par tranches :  Professionnels' SECTION,
	v_start_date transaction_date,
	sum(bod.qty_or_unit)
	from bill_gen_temp bg,bill_other_det_temp bod
	where
	upper(bod.chrg_tp) = 'FIXED PREMIUM'
	and bod.fk_bill_generated_id = bg.pk_bill_generated_id
	and bg.cust_actv_id in (100, 14, 15, 16, 31, 32, 33, 34, 37)
	and bg.cust_cat_id not in (29,30)
	and bg.bill_tp_id in (10,11,13,14,19)
	and bg.tp = 'CUSTOMER'
	and exec_dt 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');
	-- Eclairage public
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Ventes totales KWh par tranches :  Eclairage public' SECTION,
	v_start_date transaction_date,
	sum(bg.TOT_CONSMPTN)
	from bill_gen_temp bg
	where
	bg.cust_actv_id not in (100, 14, 15, 16, 31, 32, 33, 34, 37)
	and bg.cust_cat_id in (29,30)
	and bg.bill_tp_id in (10,11,13,14,19)
	and bg.tp='CUSTOMER'
	and bg.exec_dt 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');
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Ventes totale Agents AES SONEL en KWH' as section,
	v_start_date transaction_date,
	sum(tot_consmptn)
	from bill_Gen_temp bg
	where
	bg.cust_cat_id in (14,15)
	and (
	bg.to_dt 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 bg.from_dt 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 bg.bill_tp_id in (10,11,13,14,19)
	and bg.tp='CUSTOMER';
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Ventes totales Administrations en KWH' as section,
	v_start_date transaction_date,
	sum(tot_consmptn)
	from bill_Gen_temp bg
	where
	(
	bg.to_dt 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 bg.from_dt 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 bg.cust_cat_id in (22,23,24,26,52,53,54,70,71)
	and bg.bill_tp_id in (10,11,13,14,19)
	and bg.tp='CUSTOMER';
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Ventes totales Commerçants (Regroupés) en KWH' as section,
	v_start_date transaction_date,
	sum(tot_consmptn)
	from bill_Gen_temp bg
	where (
	bg.to_dt 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 bg.from_dt 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 bg.cust_cat_id in (12, 13, 17, 18, 32, 33, 34, 35, 36)
	and bg.bill_tp_id in (10,11,13,14,19)
	and bg.tp='CUSTOMER';
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Ventes totales Gros consommateurs (A définir et m à j listes multicritères) en KWH' as section,
	v_start_date transaction_date,
	0 tot_consmptn
	from
	dual;
	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;
	--Nombre de compteurs posés non facturés
	-- all customers with new meters but not billed in the month
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select 'Nombre de compteurs posés non facturés' as section,
	v_start_date transaction_date,
	count(1) tot_consmptn
	from cust c
	where 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 commit_ts between to_date(ADD_MONTHS(v_start_date,-1)||' 00:00:00','dd-mon-yyyy hh24:mi:ss')
				and 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 c.volt_tp_id = 1 and subscrptn_sts_id = 12
	and c.fk_sales_pnt_id = i_sales_pnt_id;
	select
	sum(tot_consmptn) into v_prev_month_consumption
	from bill_Gen_temp bg
	where (
	bg.to_dt between to_date(ADD_MONTHS(v_start_date,-1)||' 00:00:00','dd-mon-yyyy hh24:mi:ss')
				and to_date(LAST_DAY(ADD_MONTHS(v_start_date,-1))||' 23:59:59','dd-mon-yyyy hh24:mi:ss')
	and bg.from_dt between to_date(ADD_MONTHS(v_start_date,-1)||' 00:00:00','dd-mon-yyyy hh24:mi:ss')
				and to_date(LAST_DAY(ADD_MONTHS(v_start_date,-1))||' 23:59:59','dd-mon-yyyy hh24:mi:ss')
	)
	and bg.bill_tp_id in (10,11,13,14,19)
	and bg.tp='CUSTOMER';
	select
	sum(tot_consmptn) into v_curr_month_consumption
	from bill_Gen_temp bg
	where (
	bg.to_dt 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 bg.from_dt 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 bg.bill_tp_id in (10,11,13,14,19)
	and bg.tp='CUSTOMER';
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Variation kWh M vs M-1' as section,
	v_start_date transaction_date,
	v_curr_month_consumption - v_prev_month_consumption as tot_consmptn
	from dual;
	select
	sum(tot_consmptn) into v_prev_month_consumption
	from bill_Gen_temp bg
	where (
	bg.to_dt between to_date(ADD_MONTHS(v_start_date,-12)||' 00:00:00','dd-mon-yyyy hh24:mi:ss')
	and to_date(LAST_DAY(ADD_MONTHS(v_start_date,-12))||' 23:59:59','dd-mon-yyyy hh24:mi:ss')
	and bg.from_dt between to_date(ADD_MONTHS(v_start_date,-12)||' 00:00:00','dd-mon-yyyy hh24:mi:ss')
	and to_date(LAST_DAY(ADD_MONTHS(v_start_date,-12))||' 23:59:59','dd-mon-yyyy hh24:mi:ss')
	)
	and bg.bill_tp_id in (10,11,13,14,19)
	and bg.tp='CUSTOMER';
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Variation kWh M vs M (A-1)' as section,
	v_start_date transaction_date,
	v_curr_month_consumption - v_prev_month_consumption as tot_consmptn
	from dual;
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Nombre coupures (manuel)' as section,
	v_start_date transaction_date,
	0 as tot_consmptn
	from dual;
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select 'Nombre remises (manuel)' as section,
	v_start_date transaction_date,
	0 as tot_consmptn
	from dual;
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Nombre branchements payés' as section,
	v_start_date transaction_date,
	count(1) as tot_consmptn
	from bill_Gen_temp bg, bill_coll_temp bc
	where
	bc.fk_bill_generated_id = bg.pk_bill_Generated_id
	and bc.bill_collctd_dt between add_months(to_date('26-'||to_char(v_start_date,'MON-YYYY')||' 00:00:00','dd-mon-yyyy hh24:mi:ss'),-1)
		and to_date('25-'||to_char(v_start_date,'MON-YYYY')||' 23:59:59','dd-mon-yyyy hh24:mi:ss')
	and bg.bill_sts_id in (1,3)
	and bg.bill_tp_id = 2
	and bg.tp='CUSTOMER';
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select 'Nombre branchements réalisés' as section,
	v_start_date transaction_date,
	count(1) as tot_consmptn
	from estimate where
	ESTMT_STS_ID = 6 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 volt_tp_id = 1
	and fk_sales_pnt_id = i_sales_pnt_id;
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Nombre branchements en instance' SECTION,
	v_start_date transaction_date,
	count(1) as tot_consmptn
	from estimate e where
	exists (select 1 from bill_gen_temp where
			bill_sts_id in (1,6) and
			id = e.pk_estimate_id and
			bill_tp_id = 2
			and tp = 'ESTIMATE'
		    ) and
	e.ESTMT_STS_ID = 5 and
	e.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 e.volt_tp_id = 1
	and e.fk_sales_pnt_id = i_sales_pnt_id;
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Nombres PL existants' as section,
	v_start_date transaction_date,
	count(1) as tot_consmptn
	from supply_pnt sp
	where 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 volt_tp_id = 1
	and fk_sales_pnt_id = i_sales_pnt_id;
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Nombres PL actifs (avec abonnement)' as section,
	v_start_date transaction_date,
	count(1) as tot_consmptn
	from cust
	where 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 volt_tp_id = 1
	and subscrptn_sts_id = 12
	and fk_sales_pnt_id = i_sales_pnt_id;
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Nombre de PL facturés' as section,
	v_start_date transaction_date,
	count(bg.pk_bill_generated_id) as tot_consmptn
	from bill_Gen_temp bg
	where (
	bg.to_dt 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 bg.from_dt 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 bg.bill_tp_id = 14
	and bg.tp='CUSTOMER';
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Nombre PL sans consommations' as section,
	v_start_date transaction_date,
	count(1) as tot_consmptn
	from meter_reading_temp mr
	where mr.actv_rec_in = 'Y'
	and (
		(mr.CURR_METER_READING = mr.PREV_METER_READING)
		OR mr.CURR_METER_READING is null
		)
	and (
	mr.to_dt 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 mr.from_dt 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')
	);
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	SELECT
	'Nombre de PL actif non facturés' as section,
	v_start_date transaction_date,
	count(1) as tot_consmptn
	FROM
	METER_READING_temp mr
	WHERE
	NOT exists(
	SELECT BILL_GENERATED.FK_METER_READING_ID FROM BILL_GENERATED
	WHERE BILL_GENERATED.FK_METER_READING_ID
	IS NOT NULL and BILL_GENERATED.FK_METER_READING_ID = mr.PK_METER_READING_ID
	) AND
	mr.to_dt 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 mr.from_dt 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');
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Nombre de PL avec variation négative de plus de 25% (M vs M-1) en KWH' as section,
	v_start_date transaction_date,
	count(c.pk_cust_id) as tot_consmptn
	from cust c,
		(
			select
			id,
			sum(tot_consmptn) tot_consmptn
			from bill_Gen_temp bg
			where (
			bg.to_dt 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 bg.from_dt 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 bg.bill_tp_id in (10,11,13,14,19)
			and bg.tp='CUSTOMER'
			group by id
		) curr_year,
		(
			select
			id,
			sum(tot_consmptn) tot_consmptn
			from bill_Gen_temp bg
			where (
			bg.to_dt between to_date(ADD_MONTHS(v_start_date,-12)||' 00:00:00','dd-mon-yyyy hh24:mi:ss')
						and to_date(LAST_DAY(ADD_MONTHS(v_start_date,-12))||' 23:59:59','dd-mon-yyyy hh24:mi:ss')
			and bg.from_dt between to_date(ADD_MONTHS(v_start_date,-12)||' 00:00:00','dd-mon-yyyy hh24:mi:ss')
						and to_date(LAST_DAY(ADD_MONTHS(v_start_date,-12))||' 23:59:59','dd-mon-yyyy hh24:mi:ss')
			)
			and bg.bill_tp_id in (10,11,13,14,19)
			and bg.tp='CUSTOMER'
			group by id
		) prev_year
	where
	(
	(((curr_year.tot_consmptn - prev_year.tot_consmptn) / prev_year.tot_consmptn) * 100) < 0
	and ABS((((curr_year.tot_consmptn - prev_year.tot_consmptn) / prev_year.tot_consmptn) * 100)) >= 25
	)
	and curr_year.id = prev_year.id
	and prev_year.id = c.pk_cust_id
	and c.subscrptn_sts_id = 12
	and c.volt_tp_id = 1
	and c.fk_sales_pnt_id = i_sales_pnt_id;
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select 'Ventes totales Fcfa TTC' as section,
	v_start_date transaction_date,
	sum (tot_amount) as tot_consmptn
	from
	(
	select
	sum(tot_with_tax) tot_amount
	from bill_Gen_temp bg
	where (
			bg.to_dt 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 bg.from_dt 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 bg.tp in ('CUSTOMER','REQUEST')
	UNION ALL
	select
	sum(tot_with_tax) tot_amount
	from bill_Gen_temp bg,estimate e
	where (
			bg.to_dt 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 bg.from_dt 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 bg.id = e.pk_estimate_id
	and bg.tp = 'ESTIMATE'
	and e.estmt_sts_id = 4
	and e.volt_tp_id = 1
	and e.fk_sales_pnt_id = i_sales_pnt_id
	UNION ALL
	select
	sum(tot_with_tax) tot_amount
	from bill_Gen_temp bg
	where (
			bg.to_dt 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 bg.from_dt 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 bg.tp = 'PREESTIMATE'
	);
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select 'Encaissements Fcfa TTC' as section,
	v_start_date transaction_date,
	sum (amt_paid) as tot_consmptn
	from
	(
	select
	sum(bc.amt_paid) amt_paid
	from bill_Gen_temp bg,bill_coll_temp bc
	where bc.fk_bill_generated_id = bg.pk_bill_Generated_id
	and bc.bill_collctd_dt between add_months(to_date('26-'||to_char(v_start_date,'MON-YYYY')||' 00:00:00','dd-mon-yyyy hh24:mi:ss'),-1)
		and to_date('25-'||to_char(v_start_date,'MON-YYYY')||' 23:59:59','dd-mon-yyyy hh24:mi:ss')
	and bg.tp in ('CUSTOMER','REQUEST')
	UNION ALL
	select
	sum(bc.amt_paid) amt_paid
	from bill_Gen_temp bg,bill_coll_temp bc,estimate e
	where bc.fk_bill_generated_id = bg.pk_bill_Generated_id
	and bc.bill_collctd_dt between add_months(to_date('26-'||to_char(v_start_date,'MON-YYYY')||' 00:00:00','dd-mon-yyyy hh24:mi:ss'),-1)
		and to_date('25-'||to_char(v_start_date,'MON-YYYY')||' 23:59:59','dd-mon-yyyy hh24:mi:ss')
	and bg.id = e.pk_estimate_id
	and bg.tp = 'ESTIMATE'
	and e.estmt_sts_id = 4
	and e.volt_tp_id = 1
	and e.fk_sales_pnt_id = i_sales_pnt_id
	UNION ALL
	select
	sum(bc.amt_paid) amt_paid
	from bill_Gen_temp bg,bill_coll_temp bc
	where bc.fk_bill_generated_id = bg.pk_bill_Generated_id
	and bc.bill_collctd_dt between add_months(to_date('26-'||to_char(v_start_date,'MON-YYYY')||' 00:00:00','dd-mon-yyyy hh24:mi:ss'),-1)
		and to_date('25-'||to_char(v_start_date,'MON-YYYY')||' 23:59:59','dd-mon-yyyy hh24:mi:ss')
	and bg.tp = 'PREESTIMATE'
	);
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select 'Impayés Fcfa TTC' as section,
	v_start_date transaction_date,
	sum (tot_amount) as tot_consmptn
	from
	(
	select
	sum(tot_with_tax) tot_amount
	from bill_Gen_temp bg
	where
	bg.exec_dt <= to_date('25-'||TO_CHAR(v_start_date,'MON-YYYY')||' 23:59:59','dd-mon-yyyy hh24:mi:ss')
	and bg.bill_sts_id = 2
	and bg.tp in ('CUSTOMER','REQUEST')
	UNION ALL
	select
	sum(tot_with_tax) tot_amount
	from bill_Gen_temp bg
	where bg.exec_dt <= to_date('25-'||TO_CHAR(v_start_date,'MON-YYYY')||' 23:59:59','dd-mon-yyyy hh24:mi:ss')
	and bg.bill_sts_id = 2
	and bg.tp = 'ESTIMATE'
	UNION ALL
	select
	sum(tot_with_tax) tot_amount
	from bill_Gen_temp bg,preestimate e
	where bg.exec_dt <= to_date('25-'||TO_CHAR(v_start_date,'MON-YYYY')||' 23:59:59','dd-mon-yyyy hh24:mi:ss')
	and bg.bill_sts_id = 2
	and bg.tp = 'PREESTIMATE'
	);
	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 If;
	If i_volt_tp_id = 2 Then
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Ventes totales kWh' as section,
	v_start_date transaction_date,
	sum(tot_consmptn)
	from bill_Gen_temp bg
	where (
	bg.to_dt 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 bg.from_dt 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 bg.bill_tp_id in (10,11,13,14,19)
	and bg.tp='CUSTOMER';
	--- Residentiels < = 50 KWh
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Ventes totales KWh par tranches :  Résidentiels < = 50 KWh' SECTION,
	v_start_date transaction_date,
	0
	from dual;
	-- Residentiels > 50 KWh et <=200KWh
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Ventes totales KWh par tranches :  Résidentiels > 50 KWh et <=200KWh' SECTION,
	v_start_date transaction_date,
	0
	from dual;
	-- Residentiels > 200KWh
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Ventes totales KWh par tranches :  Résidentiels > 200KWh' SECTION,
	v_start_date transaction_date,
	0
	from dual;
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Ventes totales KWh par tranches :  Professionnels' SECTION,
	v_start_date transaction_date,
	0
	from dual;
	-- Eclairage public
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Ventes totales KWh par tranches :  Eclairage public' SECTION,
	v_start_date transaction_date,
	0
	from dual;
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Ventes totale Agents AES SONEL en KWH' as section,
	v_start_date transaction_date,
	sum(tot_consmptn)
	from bill_Gen_temp bg
	where
	bg.cust_cat_id in (14,15)
	and (
	bg.to_dt 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 bg.from_dt 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 bg.bill_tp_id in (10,11,13,14,19)
	and bg.tp='CUSTOMER';
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Ventes totales Administrations en KWH' as section,
	v_start_date transaction_date,
	sum(tot_consmptn)
	from bill_Gen_temp bg
	where
	bg.cust_cat_id in (22,23,24,26,52,53,54,70,71)
	and (
	bg.to_dt 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 bg.from_dt 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 bg.bill_tp_id in (10,11,13,14,19)
	and bg.tp='CUSTOMER';
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Ventes totales Commerçants (Regroupés) en KWH' as section,
	v_start_date transaction_date,
	sum(tot_consmptn)
	from bill_Gen_temp bg
	where
	bg.cust_cat_id not in (50,22,23,24,26,52,53,54,70,71)
	and (
	bg.to_dt 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 bg.from_dt 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 bg.bill_tp_id in (10,11,13,14,19)
	and bg.tp='CUSTOMER';
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Ventes totales Gros consommateurs (A définir et m à j listes multicritères) en KWH' as section,
	v_start_date transaction_date,
	0 tot_consmptn
	from
	dual;
	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 = 2
	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;
	--Nombre de compteurs posés non facturés
	-- all customers with new meters but not billed in the month
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select 'Nombre de compteurs posés non facturés' as section,
	v_start_date transaction_date,
	count(1) tot_consmptn
	from cust c
	where 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 commit_ts between to_date(ADD_MONTHS(v_start_date,-1)||' 00:00:00','dd-mon-yyyy hh24:mi:ss')
				and 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 c.volt_tp_id = 2 and subscrptn_sts_id = 12
	and c.fk_sales_pnt_id = i_sales_pnt_id;
	select
	sum(tot_consmptn) into v_prev_month_consumption
	from bill_Gen_temp bg
	where (
	bg.to_dt between to_date(ADD_MONTHS(v_start_date,-1)||' 00:00:00','dd-mon-yyyy hh24:mi:ss')
				and to_date(LAST_DAY(ADD_MONTHS(v_start_date,-1))||' 23:59:59','dd-mon-yyyy hh24:mi:ss')
	and bg.from_dt between to_date(ADD_MONTHS(v_start_date,-1)||' 00:00:00','dd-mon-yyyy hh24:mi:ss')
				and to_date(LAST_DAY(ADD_MONTHS(v_start_date,-1))||' 23:59:59','dd-mon-yyyy hh24:mi:ss')
	)
	and bg.bill_tp_id in (10,11,13,14,19)
	and bg.tp='CUSTOMER';
	select
	sum(tot_consmptn) into v_curr_month_consumption
	from bill_Gen_temp bg
	where (
	bg.to_dt 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 bg.from_dt 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 bg.bill_tp_id in (10,11,13,14,19)
	and bg.tp='CUSTOMER';
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Variation kWh M vs M-1' as section,
	v_start_date transaction_date,
	v_curr_month_consumption - v_prev_month_consumption as tot_consmptn
	from dual;
	select
	sum(tot_consmptn) into v_prev_month_consumption
	from bill_Gen_temp bg
	where (
	bg.to_dt between to_date(ADD_MONTHS(v_start_date,-12)||' 00:00:00','dd-mon-yyyy hh24:mi:ss')
				and to_date(LAST_DAY(ADD_MONTHS(v_start_date,-12))||' 23:59:59','dd-mon-yyyy hh24:mi:ss')
	and bg.from_dt between to_date(ADD_MONTHS(v_start_date,-12)||' 00:00:00','dd-mon-yyyy hh24:mi:ss')
				and to_date(LAST_DAY(ADD_MONTHS(v_start_date,-12))||' 23:59:59','dd-mon-yyyy hh24:mi:ss')
	)
	and bg.bill_tp_id in (10,11,13,14,19)
	and bg.tp='CUSTOMER';
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Variation kWh M vs M (A-1)' as section,
	v_start_date transaction_date,
	v_curr_month_consumption - v_prev_month_consumption as tot_consmptn
	from dual;
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Nombre coupures (manuel)' as section,
	v_start_date transaction_date,
	0 as tot_consmptn
	from dual;
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select 'Nombre remises (manuel)' as section,
	v_start_date transaction_date,
	0 as tot_consmptn
	from dual;
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Nombre branchements payés' as section,
	v_start_date transaction_date,
	count(1) as tot_consmptn
	from bill_Gen_temp bg, bill_coll_temp bc
	where
	bc.fk_bill_generated_id = bg.pk_bill_Generated_id
	and bc.bill_collctd_dt between add_months(to_date('26-'||to_char(v_start_date,'MON-YYYY')||' 00:00:00','dd-mon-yyyy hh24:mi:ss'),-1)
		and to_date('25-'||to_char(v_start_date,'MON-YYYY')||' 23:59:59','dd-mon-yyyy hh24:mi:ss')
	and bg.bill_sts_id = 1
	and bg.bill_tp_id = 2
	and bg.tp='CUSTOMER';
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select 'Nombre branchements réalisés' as section,
	v_start_date transaction_date,
	count(1) as tot_consmptn
	from estimate where
	ESTMT_STS_ID = 6 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 volt_tp_id = 2
	and fk_sales_pnt_id = i_sales_pnt_id;
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Nombre branchements en instance' SECTION,
	v_start_date transaction_date,
	count(1) as tot_consmptn
	from estimate e where
	exists (select 1 from bill_gen_temp where
			bill_sts_id in (1,6) and
			id = e.pk_estimate_id and
			bill_tp_id = 2
			and tp = 'ESTIMATE'
		    ) and
	e.ESTMT_STS_ID = 5 and
	e.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 e.volt_tp_id = 2
	and e.fk_sales_pnt_id = i_sales_pnt_id;
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Nombres PL existants' as section,
	v_start_date transaction_date,
	count(1) as tot_consmptn
	from supply_pnt sp
	where 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 volt_tp_id = 2
	and fk_sales_pnt_id = i_sales_pnt_id;
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Nombres PL actifs (avec abonnement)' as section,
	v_start_date transaction_date,
	count(1) as tot_consmptn
	from cust
	where 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 volt_tp_id = 2
	and subscrptn_sts_id = 12
	and fk_sales_pnt_id = i_sales_pnt_id;
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Nombre de PL facturés' as section,
	v_start_date transaction_date,
	count(bg.pk_bill_generated_id) as tot_consmptn
	from bill_Gen_temp bg
	where (
	bg.to_dt 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 bg.from_dt 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 bg.bill_tp_id = 14
	and bg.tp='CUSTOMER';
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Nombre PL sans consommations' as section,
	v_start_date transaction_date,
	count(1) as tot_consmptn
	from meter_reading_temp mr
	where mr.actv_rec_in = 'Y'
	and (
		(
		mr.PREV_ACTV_IMP_OFF_PEAK=mr.CURR_ACTV_IMP_OFF_PEAK and
		mr.PREV_ACTV_IMP_PEAK=mr.CURR_ACTV_IMP_PEAK and
		mr.PREV_ACTV_EXP_OFF_PEAK=mr.CURR_ACTV_EXP_OFF_PEAK and
		mr.PREV_ACTV_EXP_PEAK=mr.CURR_ACTV_EXP_PEAK and
		mr.PREV_REACTV_IMP_OFF_PEAK=mr.CURR_REACTV_IMP_OFF_PEAK and
		mr.PREV_REACTV_IMP_PEAK=mr.CURR_REACTV_IMP_PEAK
		)
		OR
		(
		mr.CURR_ACTV_IMP_OFF_PEAK IS NULL and
		mr.CURR_ACTV_IMP_PEAK  IS NULL and
		mr.CURR_ACTV_EXP_OFF_PEAK  IS NULL and
		mr.CURR_ACTV_EXP_PEAK  IS NULL and
		mr.CURR_REACTV_IMP_OFF_PEAK  IS NULL and
		mr.CURR_REACTV_IMP_PEAK IS NULL )
		)
	and (
	mr.to_dt 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 mr.from_dt 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')
	);
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	SELECT
	'Nombre de PL actif non facturés' as section,
	v_start_date transaction_date,
	count(1) as tot_consmptn
	FROM
	METER_READING_temp mr
	WHERE
	NOT exists(
	SELECT BILL_GENERATED.FK_METER_READING_ID FROM BILL_GENERATED
	WHERE BILL_GENERATED.FK_METER_READING_ID
	IS NOT NULL and BILL_GENERATED.FK_METER_READING_ID = mr.PK_METER_READING_ID
	) AND
	mr.to_dt 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 mr.from_dt 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');
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select
	'Nombre de PL avec variation négative de plus de 25% (M vs M-1) en KWH' as section,
	v_start_date transaction_date,
	count(c.pk_cust_id) as tot_consmptn
	from cust c,
		(
			select
			id,
			sum(tot_consmptn) tot_consmptn
			from bill_Gen_temp bg
			where (
			bg.to_dt 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 bg.from_dt 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 bg.bill_tp_id in (10,11,13,14,19)
			and bg.tp='CUSTOMER'
			group by id
		) curr_year,
		(
			select
			id,
			sum(tot_consmptn) tot_consmptn
			from bill_Gen_temp bg
			where (
			bg.to_dt between to_date(ADD_MONTHS(v_start_date,-12)||' 00:00:00','dd-mon-yyyy hh24:mi:ss')
						and to_date(LAST_DAY(ADD_MONTHS(v_start_date,-12))||' 23:59:59','dd-mon-yyyy hh24:mi:ss')
			and bg.from_dt between to_date(ADD_MONTHS(v_start_date,-12)||' 00:00:00','dd-mon-yyyy hh24:mi:ss')
						and to_date(LAST_DAY(ADD_MONTHS(v_start_date,-12))||' 23:59:59','dd-mon-yyyy hh24:mi:ss')
			)
			and bg.bill_tp_id in (10,11,13,14,19)
			and bg.tp='CUSTOMER'
			group by id
		) prev_year
	where
	(
	(((curr_year.tot_consmptn - prev_year.tot_consmptn) / prev_year.tot_consmptn) * 100) < 0
	and ABS((((curr_year.tot_consmptn - prev_year.tot_consmptn) / prev_year.tot_consmptn) * 100)) >= 25
	)
	and curr_year.id = prev_year.id
	and prev_year.id = c.pk_cust_id
	and c.subscrptn_sts_id = 12
	and c.volt_tp_id = 2
	and c.fk_sales_pnt_id = i_sales_pnt_id;
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select 'Ventes totales Fcfa TTC' as section,
	v_start_date transaction_date,
	sum (tot_amount) as tot_consmptn
	from
	(
	select
	sum(tot_with_tax) tot_amount
	from bill_Gen_temp bg
	where (
			bg.to_dt 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 bg.from_dt 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 bg.tp in ('CUSTOMER','REQUEST')
	UNION ALL
	select
	sum(tot_with_tax) tot_amount
	from bill_Gen_temp bg,estimate e
	where (
			bg.to_dt 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 bg.from_dt 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 bg.id = e.pk_estimate_id
	and bg.tp = 'ESTIMATE'
	and e.estmt_sts_id = 4
	and e.volt_tp_id = 2
	and e.fk_sales_pnt_id = i_sales_pnt_id
	);
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select 'Encaissements Fcfa TTC' as section,
	v_start_date transaction_date,
	sum (amt_paid) as tot_consmptn
	from
	(
	select
	sum(bc.amt_paid) amt_paid
	from bill_Gen_temp bg,bill_coll_temp bc
	where bc.fk_bill_generated_id = bg.pk_bill_Generated_id
	and bc.bill_collctd_dt between add_months(to_date('26-'||to_char(v_start_date,'MON-YYYY')||' 00:00:00','dd-mon-yyyy hh24:mi:ss'),-1)
		and to_date('25-'||to_char(v_start_date,'MON-YYYY')||' 23:59:59','dd-mon-yyyy hh24:mi:ss')
	and bg.tp in ('CUSTOMER','REQUEST')
	UNION ALL
	select
	sum(bc.amt_paid) amt_paid
	from bill_Gen_temp bg,bill_coll_temp bc,estimate e
	where bc.fk_bill_generated_id = bg.pk_bill_Generated_id
	and bc.bill_collctd_dt between add_months(to_date('26-'||to_char(v_start_date,'MON-YYYY')||' 00:00:00','dd-mon-yyyy hh24:mi:ss'),-1)
		and to_date('25-'||to_char(v_start_date,'MON-YYYY')||' 23:59:59','dd-mon-yyyy hh24:mi:ss')
	and bg.id = e.pk_estimate_id
	and bg.tp = 'ESTIMATE'
	and e.estmt_sts_id = 4
	and e.volt_tp_id = 2
	and e.fk_sales_pnt_id = i_sales_pnt_id
	);
	insert into billing_statistics
	(
	section,transaction_date,result_value
	)
	select 'Impayés Fcfa TTC' as section,
	v_start_date transaction_date,
	sum (tot_amount) as tot_consmptn
	from
	(
	select
	sum(tot_with_tax) tot_amount
	from bill_Gen_temp bg
	where bg.exec_dt <= to_date('25-'||TO_CHAR(v_start_date,'MON-YYYY')||' 23:59:59','dd-mon-yyyy hh24:mi:ss')
	and bg.bill_sts_id = 2
	and bg.tp in ('CUSTOMER','REQUEST')
	UNION ALL
	select
	sum(tot_with_tax) tot_amount
	from bill_Gen_temp bg
	where bg.exec_dt <= to_date('25-'||TO_CHAR(v_start_date,'MON-YYYY')||' 23:59:59','dd-mon-yyyy hh24:mi:ss')
	and bg.bill_sts_id = 2
	and bg.tp = 'ESTIMATE'
	);
	update 	billing_statistics
	set 	volt_tp_id = 2,
			inputted_date=v_inputted_date,
			sales_pnt_id = i_sales_pnt_id
	where 	volt_tp_id is null;
	commit;
	End If;
v_start_date := ADD_MONTHS(v_start_date,1);
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,
		(select result_value from billing_statistics where TO_CHAR(TRANSACTION_DATE,'MON') = 'MAR'
					 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 MAR,
		(select result_value from billing_statistics where TO_CHAR(TRANSACTION_DATE,'MON') = 'APR'
					 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 APR,
		(select result_value from billing_statistics where TO_CHAR(TRANSACTION_DATE,'MON') = 'MAY'
					 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 MAY,
		(select result_value from billing_statistics where TO_CHAR(TRANSACTION_DATE,'MON') = 'JUN'
					 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 JUN,
		(select result_value from billing_statistics where TO_CHAR(TRANSACTION_DATE,'MON') = 'JUL'
					 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 JUL,
		(select result_value from billing_statistics where TO_CHAR(TRANSACTION_DATE,'MON') = 'AUG'
					 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 AUG,
		(select result_value from billing_statistics where TO_CHAR(TRANSACTION_DATE,'MON') = 'SEP'
					 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 SEP,
		(select result_value from billing_statistics where TO_CHAR(TRANSACTION_DATE,'MON') = 'OCT'
					 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 OCT,
		(select result_value from billing_statistics where TO_CHAR(TRANSACTION_DATE,'MON') = 'NOV'
					 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 NOV,
		(select result_value from billing_statistics where TO_CHAR(TRANSACTION_DATE,'MON') = 'DEC'
					 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 DEC
		from (
		select distinct section from billing_statistics) t;
	ElsIf i_volt_tp_id = 2 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,
		(select result_value from billing_statistics where TO_CHAR(TRANSACTION_DATE,'MON') = 'MAR'
					 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 MAR,
		(select result_value from billing_statistics where TO_CHAR(TRANSACTION_DATE,'MON') = 'APR'
					 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 APR,
		(select result_value from billing_statistics where TO_CHAR(TRANSACTION_DATE,'MON') = 'MAY'
					 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 MAY,
		(select result_value from billing_statistics where TO_CHAR(TRANSACTION_DATE,'MON') = 'JUN'
					 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 JUN,
		(select result_value from billing_statistics where TO_CHAR(TRANSACTION_DATE,'MON') = 'JUL'
					 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 JUL,
		(select result_value from billing_statistics where TO_CHAR(TRANSACTION_DATE,'MON') = 'AUG'
					 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 AUG,
		(select result_value from billing_statistics where TO_CHAR(TRANSACTION_DATE,'MON') = 'SEP'
					 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 SEP,
		(select result_value from billing_statistics where TO_CHAR(TRANSACTION_DATE,'MON') = 'OCT'
					 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 OCT,
		(select result_value from billing_statistics where TO_CHAR(TRANSACTION_DATE,'MON') = 'NOV'
					 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 NOV,
		(select result_value from billing_statistics where TO_CHAR(TRANSACTION_DATE,'MON') = 'DEC'
					 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 DEC
		from (
		select distinct section from billing_statistics) t;
	End If;
end sales_pnt;
/