declare bill_where boolean; area_where boolean; AppID PLS_INTEGER; cursor cur_0(section number,branch number,grop number,billingrun number ,bill_1 number,bill_2 number,area_1 number,area_2 number) is select C.section_name,E.branch_name, D.area_name,A.billingrun_id,a.id, a.issue_month,a.issue_year,a.file_no,a.customer_name,a.customer_address,a.customer_account_id,a.no_of_units,a.no_of_room, decode(a.meter_status_code,1,a.current_reading,null)current_reading, decode(a.meter_status_code,1,a.previous_reading,null)previous_reading, nvl( a.usage,0)usage,nvl(to_char(a.usage_value,'999999990.99'),0)usage_value,nvl(to_char(a.sanitary,'999999990.99'),0)sanitary,nvl(to_char(a.maintenance,'999999990.99'),0)maintenance ,nvl(to_char(revenue_stamp,'0.99'),0)revenue_stamp,nvl(to_char(a.receipt_stamp,'0.99')+to_char(a.contract_stamp,'990.99'),0) stamp,nvl(to_char(a.gov_stamp,'0.99'),0)gov_stamp,nvl(to_char(a.add_val,'999999990.99'),0)add_val,nvl(to_char(a.deduct_val,'999999990.99'),0)deduct_val,nvl(to_char(a.total_due,'999999990.99'),0)total_due,a.figures_in_words ,b.meter_status_name from bl_billing a, bl_d_meter_status B , BL_D_SECTION C , BL_D_AREA D, BL_D_BRANCH E where a.meter_status_code = b.code and A.section_code = section and A.branch_code =branch AND A.AREA_CODE IN (SELECT CODE FROM BL_D_AREA WHERE GROUP_ID =grop) AND A.billingrun_id = billingrun AND A.SECTION_CODE = C.CODE and a.section_code = e.section_code AND A.BRANCH_CODE = E.CODE and a.section_code = d.section_code and a.branch_code = d.branch_code AND A.AREA_CODE = D.CODE and a.AREA_CODE between area_1 and area_2 and a.id between bill_1 and bill_2 -- and (a.id between bill_1 and bill_2 or bill_where) -- and (a.AREA_CODE between area_1 and area_2 or area_where) --and a.id =12963 order by a.id; cursor cur_1(cust number,bill number ,billrun number) is SELECT H.account_type_name,nvl(G.usage_val,0)usage_val FROM BL_USAGE_TX G, BL_D_ACCOUNT_TYPE H WHERE G.account_type_code = H.code AND G.customer_account_id =cust AND G.bill_id = bill AND G.billrun_id = billrun ; cursor cur_2(bill number ,billrun number) is SELECT sum(I.install_value) install_value,decode(sum(I.install_value),null,null,'تقسيط فاتورة') i_type FROM BL_INSTALLMENT_DETAIL I WHERE I.bill_id= bill AND I.billrun_id = billrun having sum(I.install_value) is not null UNION SELECT sum(S.install_value) install_value,F.name i_type FROM PAYED_SCHEDUAL S, BL_SCHEDUAL V ,BL_D_SCHEDUAL_TYPE F WHERE S.bill_id = bill AND S.billrun_id = billrun AND S.schedual_code = V.id AND V.schedule_type = F.code group by f.name; cursor cur_3(bill number ,billrun number) is SELECT SUM(M.service_value) service FROM SERVICE_DETAIL M WHERE M.bill_id = bill AND M.billrun_id = billrun; cursor cur_4(cust number) is select sum(to_char(a.amount,'999999999.99') - nvl(to_char(a.cut_value,'9999999999.99'),0)) remfrontpayment from upfrontpayment a where a.customer_account_id = cust; cursor cur_5(cust number) is select sum(g1.remm) remmm from ( select sum(install_rem*install_value) remm from bl_schedual where install_rem >0 and customer_account_id = cust union select sum(install_rem*install_value) remm from bl_installment where install_rem >0 and cusomer_account_id = cust )g1; counter number; pillRecord varchar(4000); filename VARCHAR2(255); l_tempfile text_io.file_type; --1 account_type_name_0 varchar(50); usage_val_0 varchar(50); account_type_name_1 varchar(50); usage_val_1 varchar(50); account_type_name_2 varchar(50); usage_val_2 varchar(50); account_type_name_other varchar(50); usage_val_other number; --2 i_type_0 varchar(50); install_value_0 varchar(50); i_type_1 varchar(50); install_value_1 varchar(50); i_type_2 varchar(50); install_value_2 varchar(50); i_type_other varchar(50); install_value_other number; --3 service varchar(50); --4 remfrontpayment varchar(50); --5 remmm varchar(50); ii number; cursor count_cur(section number,branch number,grop number,billingrun number ,bill_1 number,bill_2 number,area_1 number,area_2 number) is select count('x') from bl_billing a, bl_d_meter_status B , BL_D_SECTION C , BL_D_AREA D, BL_D_BRANCH E where a.meter_status_code = b.code and A.section_code = section and A.branch_code =branch AND A.AREA_CODE IN (SELECT CODE FROM BL_D_AREA WHERE GROUP_ID =grop) AND A.billingrun_id = billingrun AND A.SECTION_CODE = C.CODE and a.section_code = e.section_code AND A.BRANCH_CODE = E.CODE and a.section_code = d.section_code and a.branch_code = d.branch_code AND A.AREA_CODE = D.CODE and a.id between bill_1 and bill_2 and a.AREA_CODE between area_1 and area_2 -- and (a.id between bill_1 and bill_2 or bill_where) -- and (a.AREA_CODE between area_1 and area_2 or area_where) order by a.id; v_per number; -- dec variable v_usage_value_dec varchar2(2); v_maintenance_dec varchar2(2); v_revenue_stamp_dec varchar2(2); v_stamp_dec varchar2(2); v_gov_stamp_dec varchar2(2); v_sanitary_dec varchar2(2); v_add_val_dec varchar2(2); v_deduct_val_dec varchar2(2); v_total_due_dec varchar2(2); begin open count_cur(:SECTION_CODE,:BRANCH_CODE,:GROUP_ID,:BILLINGRUN_ID,:FROM_inv,:TO_inv,:FROM_AREA,:TO_AREA); fetch count_cur into :bar.count; close count_cur; counter :=0; filename := 'c:\outfile\bill.dat'; if client_text_io.is_open(l_tempfile) then client_text_io.fclose(l_tempfile); end if; l_tempfile := text_io.fopen(filename, 'W'); area_where := false; bill_where := false; if(:FROM_AREA is null or :TO_AREA is null) then area_where := true; end if; if(:FROM_inv is null or :TO_inv is null) then bill_where := true; end if; for rec_0 in cur_0(:SECTION_CODE,:BRANCH_CODE,:GROUP_ID,:BILLINGRUN_ID,:FROM_inv,:TO_inv,:FROM_AREA,:TO_AREA) loop service :='0'; remfrontpayment :='0.00'; remmm :='0.00'; ii :=0; install_value_other :=0; usage_val_other :=0; for rec_1 in cur_1(rec_0.customer_account_id,rec_0.id,rec_0.billingrun_id) loop if (ii = 0) then account_type_name_0 := rec_1.account_type_name; usage_val_0 := rec_1.usage_val; elsif (ii = 1) then account_type_name_1 := rec_1.account_type_name; usage_val_1 := rec_1.usage_val; elsif (ii = 2) then account_type_name_2 := rec_1.account_type_name; usage_val_2 := rec_1.usage_val; else account_type_name_other := rec_1.account_type_name; usage_val_other :=usage_val_other+ to_number(rec_1.usage_val); end if; ii :=ii+1; end loop; account_type_name_other := ''; if (ii> 3) then account_type_name_other := 'أخرى'; end if; ii :=0; for rec_2 in cur_2(rec_0.id,rec_0.billingrun_id) loop if (ii = 0) then i_type_0 :=rec_2.i_type; install_value_0 := rec_2.install_value; elsif (ii = 1) then i_type_1 := rec_2.i_type; install_value_1 := rec_2.install_value; elsif (ii = 2) then i_type_2 := rec_2.i_type; install_value_2 := rec_2.install_value; else i_type_other := rec_2.i_type; install_value_other :=install_value_other + to_number(rec_2.install_value); end if; ii :=ii+1; end loop; i_type_other :=''; if (ii > 3) then i_type_other := 'أخرى'; end if; for rec_3 in cur_3(rec_0.id,rec_0.billingrun_id) loop service := rec_3.service; end loop; for rec_4 in cur_4(rec_0.customer_account_id) loop remfrontpayment :=rec_4.remfrontpayment; end loop; for rec_5 in cur_5(rec_0.customer_account_id) loop remmm :=rec_5.remmm; end loop; v_usage_value_dec:=0; v_maintenance_dec :=0; v_revenue_stamp_dec :=0; v_stamp_dec :=0; v_gov_stamp_dec :=0; v_sanitary_dec :=0; v_add_val_dec :=0; v_deduct_val_dec :=0; v_total_due_dec :=0; --- decemil sprerat -- hany v_usage_value_dec := substr(rec_0.usage_value,instr(rec_0.usage_value,'.')+1,length(rec_0.usage_value)); rec_0.usage_value:= trunc(rec_0.usage_value,0); v_maintenance_dec:=substr(rec_0.maintenance,instr(rec_0.maintenance,'.')+1,length(rec_0.maintenance)); rec_0.maintenance:= trunc( rec_0.maintenance,0); v_revenue_stamp_dec:=substr(rec_0.revenue_stamp,instr(rec_0.revenue_stamp,'.')+1,length(rec_0.revenue_stamp)); if length(v_revenue_stamp_dec)=1 then v_revenue_stamp_dec:=rpad(v_revenue_stamp_dec,2,0);end if; rec_0.revenue_stamp:= trunc( rec_0.revenue_stamp,0); v_stamp_dec:=substr(rec_0.stamp,instr(rec_0.stamp,'.')+1,length(rec_0.stamp)); if length(v_stamp_dec)=1 then v_stamp_dec:=rpad(v_stamp_dec,2,0);end if; rec_0.stamp:= trunc( rec_0.stamp,0); v_gov_stamp_dec:=substr(rec_0.gov_stamp,instr(rec_0.gov_stamp,'.')+1,length(rec_0.gov_stamp)); if length(v_gov_stamp_dec)=1 then v_gov_stamp_dec:=rpad(v_gov_stamp_dec,2,0);end if; rec_0.gov_stamp:= trunc( rec_0.gov_stamp,0); v_sanitary_dec:=substr(rec_0.sanitary,instr(rec_0.sanitary,'.')+1,length(rec_0.sanitary)); rec_0.sanitary:= trunc( rec_0.sanitary,0); v_add_val_dec:=substr(rec_0.add_val,instr(rec_0.add_val,'.')+1,length(rec_0.add_val)); rec_0.add_val:= trunc( rec_0.add_val,0); v_deduct_val_dec:=substr(rec_0.deduct_val,instr(rec_0.deduct_val,'.')+1,length(rec_0.deduct_val)); rec_0.deduct_val:= trunc( rec_0.deduct_val,0); v_total_due_dec:=substr(rec_0.total_due,instr(rec_0.total_due,'.')+1,length(rec_0.total_due)); rec_0.total_due:= trunc( rec_0.total_due,0); client_text_io.put_line(l_tempfile,''); text_io.put_line(l_tempfile,''); client_text_io.put_line(l_tempfile,' '||Word(rec_0.section_name,20)||' '||Word(rec_0.area_name,6)||' '||Word(rec_0.no_of_units,3)||' '||Word(rec_0.branch_name,20)||' '||Word(rec_0.branch_name,20)); client_text_io.put_line(l_tempfile,' '|| Word(rec_0.branch_name,20) || ' ' || Word(rec_0.file_no,7) || ' ' || Word(rec_0.no_of_room,3) || ' ' || Word(rec_0.area_name,5) || ' ' || Word(rec_0.area_name,5)); client_text_io.put_line(l_tempfile,' ' || Word(rec_0.customer_name,40) || ' ' || Word(rec_0.file_no,7) || ' ' || Word(rec_0.file_no,7)); client_text_io.put_line(l_tempfile,' ' || Word(rec_0.customer_address,34) || ' ' || Word1(service,7) || ' ' || Word(rec_0.id,9) || ' ' || Word(rec_0.customer_name,23) || ' ' || Word(rec_0.customer_name,23)); client_text_io.put_line(l_tempfile,''); client_text_io.put_line(l_tempfile,' ' || Word(rec_0.issue_month,2) || ' ' || Word(rec_0.issue_year,4)); client_text_io.put_line(l_tempfile,' ' || Word(rec_0.current_reading,6) || ' ' || Word4(usage_val_0,6) || ' ' || Word(account_type_name_0,12) || ' ' || Word4(install_value_0,7) || ' ' || Word(i_type_0,7) ||' ' || Word(rec_0.issue_month,2) || ' ' || Word(rec_0.issue_year,4) || ' ' || Word(rec_0.issue_month,2) || ' ' || Word(rec_0.issue_year,4)); client_text_io.put_line(l_tempfile,' ' || Word(rec_0.previous_reading,6) || ' ' || Word4(usage_val_1,6) || ' ' || Word(account_type_name_1,12) || ' ' || Word4(install_value_1,7) || ' ' || Word(i_type_1,7)); client_text_io.put_line(l_tempfile,' ' || Word(rec_0.usage,6) || ' ' || Word4(usage_val_2,6) || ' ' || Word(account_type_name_2,12) || ' ' || Word4(install_value_2,7) || ' ' || Word(i_type_2,7) || ' ' || Word2(remmm,6)); client_text_io.put_line(l_tempfile,' ' || Word(rec_0.meter_status_name,6) || ' ' || Word4(usage_val_other,6) || ' ' || Word(account_type_name_other,12) || ' ' || Word4(install_value_other,7) || ' ' || Word(i_type_other,7) || ' ' || rpad(remfrontpayment,8,' ') || ' ' || Word(rec_0.id,9) || ' ' || Word(rec_0.id,9)); client_text_io.put_line(l_tempfile,''); client_text_io.put_line(l_tempfile,' ' ||v_total_due_dec ||' '|| Word2(rec_0.total_due,9) || ' ' ||v_total_due_dec ||' '|| Word2(rec_0.total_due,9)); client_text_io.put_line(l_tempfile,''); client_text_io.put_line(l_tempfile, v_usage_value_dec||' '||Word2(rec_0.usage_value,6) ||' ' ||v_maintenance_dec||' '|| Word2(rec_0.maintenance,2) ||' '||v_revenue_stamp_dec||' '|| Word2(rec_0.revenue_stamp,1) ||' ' || v_stamp_dec||' '||Word2(rec_0.stamp,2)||' ' ||v_gov_stamp_dec||' '||Word2(rec_0.gov_stamp,1) ||' ' ||v_sanitary_dec||' '||Word2(rec_0.sanitary,6) ||' '||v_add_val_dec||' '|| Word2(rec_0.add_val,6) ||' '|| v_deduct_val_dec||' '||Word2(rec_0.deduct_val,6)||' '||v_total_due_dec ||' '||Word2(rec_0.total_due,7)); client_text_io.put_line(l_tempfile,''); client_text_io.put_line(l_tempfile,' ' || Word(rec_0.figures_in_words,46)); client_text_io.put_line(l_tempfile,'{a2 |};cC;x' || Word3(rec_0.id,9) || 'x;H06;D{a0'); client_text_io.put_line(l_tempfile,''); client_text_io.put_line(l_tempfile,''); client_text_io.put_line(l_tempfile,''); client_text_io.put_line(l_tempfile,''); client_text_io.put_line(l_tempfile,''); counter := counter + 1; ---- install_value_0:=0; install_value_1:=0; install_value_2:=0; install_value_other:=0; ---- /* if (counter mod 3 = 0) then client_text_io.put_line(l_tempfile,' '); client_text_io.put_line(l_tempfile,' '); client_text_io.put_line(l_tempfile,' '); end if;*/ -- bar :rem :=cur_0%rowcount; V_PER:=ROUND(((:REM/:COUNT)*100),0); :BAR:=V_PER||'%'; if v_per/10 in (1,2,3,4,5,6,7,8,9,10)then synchronize; SET_ITEM_PROPERTY('BAR',WIDTH,V_PER*3); end if; end loop; if (counter mod 3 = 1) then for i in 1 .. 49 loop client_text_io.put_line(l_tempfile,''); end loop; end if; if (counter mod 3 = 2) then for i in 1 .. 26 loop client_text_io.put_line(l_tempfile,''); end loop; end if; message('تم تجهيز ' || counter ||' '|| 'فاتورة'); message('تم تجهيز ' || counter ||' '|| 'فاتورة'); client_text_io.Fclose(l_tempfile); AppID := DDE.App_Begin(get_app_path||'outfile\p.bat',DDE.APP_MODE_MINIMIZED); update working_done set step_date = to_char(sysdate,'dd-mon-yyyy') where section_code = :section_code and branch_code = :branch_code and group_id= :group_id and user_id=:global.userid and billing_run_id = :billingrun_id and step_number =8; if sql%notfound then insert into working_done(section_code,step_number,step_date,user_id,large_cust,branch_code, group_id,billing_run_id,area_code) values(:section_code,8,to_char(sysdate,'dd-mon-yyyy'),:global.userid,0,:branch_code, :group_id,:billingrun_id,null); commit; end if; exception when others then message (SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255)); client_text_io.Fclose(l_tempfile); end;