AAA

create or replace procedure BC_HD_MAT
    (b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_ngayd number,b_ngayc number,
    b_kieu varchar2,b_loai_bp varchar2,b_ma_bp varchar2,b_ma varchar2,b_nhom varchar2,b_ten out nvarchar2,cs_kq out pht_type.cs_type)
AS
    b_loi varchar2(150);b_i1 number;b_c10 varchar2(10);b_c1 varchar2(1);
begin
--Lan--Bang ke hoa don mat,huy,tra lai--
--b_kieu=M-mat,K-huy,T-tra lai,''-mat,hong
--b_loi:=PHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'KT','023');
if b_ma_bp is null then b_loi:='loi:Nhap bo phan:loi'; raise PROGRAM_ERROR; end if;
select min(ma) into b_c10 from ht_ma_dvi where vp='C';
if b_ma_dvi<>b_c10 and b_loai_bp='D' and b_ma_dvi<>b_ma_bp then
   b_loi:='loi:Khong xem so lieu don vi khac:loi'; raise PROGRAM_ERROR;
end if;
---Lay ma dvi---
delete temp_1;
/*temp_1:c1=loai_bp,c2=ma_bp,c3=ma_dl*/
if b_loai_bp='D' then
    insert into temp_1(c1,n1) select a.ma_dvi,a.so_id from hd_1 a,hd_3 b where a.ma_dvi=b_ma_dvi
        and ngay_ht between b_ngayd and b_ngayc and l_ct='X' and a.ma_dvi=b.ma_dvi and a.so_id=b.so_id
        and b.loai_x='D' and a.ma_dvi=b_ma_bp and htoan='H';
    insert into temp_1(c1,n1) select a.ma_dvi,a.so_id from hd_1 a,hd_3 b where a.ma_dvi=b_ma_bp
        and ngay_ht between b_ngayd and b_ngayc and l_ct='X' and a.ma_dvi=b.ma_dvi and a.so_id=b.so_id
        and b.loai_x='B' and a.ma_dvi=b_ma_bp and ma_x in (select ma from ht_ma_phong where ma_dvi=b_ma_bp) and htoan='H';
    insert into temp_1(c1,n1) select a.ma_dvi,a.so_id from hd_1 a,hd_3 b where a.ma_dvi=b_ma_bp
        and ngay_ht between b_ngayd and b_ngayc and l_ct='X' and a.ma_dvi=b.ma_dvi and a.so_id=b.so_id
        and b.loai_x='C' and ma_x in (select ma from ht_ma_nsd where ma_dvi=b_ma_bp) and htoan='H';
    insert into temp_1(c1,n1) select a.ma_dvi,a.so_id from hd_1 a,hd_3 b where a.ma_dvi=b_ma_dvi
        and ngay_ht between b_ngayd and b_ngayc and l_ct='X' and a.ma_dvi=b.ma_dvi and a.so_id=b.so_id
        and b.loai_x='L' and b.ma_x in (select ma from bh_dl_ma_kh where ma_dvi=b_ma_dvi) and htoan='H';
elsif b_loai_bp='B' then
    insert into temp_1(c1,n1) select a.ma_dvi,a.so_id from hd_1 a,hd_3 b where a.ma_dvi=b_ma_dvi
        and ngay_ht between b_ngayd and b_ngayc and l_ct='X' and a.ma_dvi=b.ma_dvi and a.so_id=b.so_id
        and b.loai_x='B' and b.ma_x=b_ma_bp and htoan='H';
    insert into temp_1(c1,n1) select a.ma_dvi,a.so_id from hd_1 a,hd_3 b where a.ma_dvi=b_ma_dvi
        and ngay_ht between b_ngayd and b_ngayc and l_ct='X' and a.ma_dvi=b.ma_dvi and a.so_id=b.so_id
        and b.loai_x='C' and b.ma_x in (select ma from ht_ma_nsd where ma_dvi=b_ma_dvi and phong=b_ma_bp) and htoan='H';
    /*insert into temp_1(c1,n1) select a.ma_dvi,a.so_id from hd_1 a,hd_3 b where a.ma_dvi=b_ma_dvi
        and ngay_ht between b_ngayd and b_ngayc and l_ct='X' and a.ma_dvi=b.ma_dvi and a.so_id=b.so_id
        and b.loai_x='L' and b.ma_x in (select ma from bh_dl_ma_kh where ma_dvi=b_ma_dvi and phong=b_ma_bp) and htoan='H';*/
else
    insert into temp_1(c1,n1) select a.ma_dvi,a.so_id from hd_1 a,hd_3 b where a.ma_dvi=b_ma_dvi
        and ngay_ht between b_ngayd and b_ngayc and l_ct='X' and a.ma_dvi=b.ma_dvi and a.so_id=b.so_id
        and loai_x=b_loai_bp and b.ma_x=b_ma_bp and htoan='H';
end if;
delete ket_qua;
/*ket_qua:c1=ma_dvi,c2=ma,c3=ten,c4=dvt,n1=do_dai,c5=seri,n2=dau,
    n3=cuoi,n4=so_luong,n5=q_dau,n7=so_to,n8=so_quyen,c12=dau_c,c13=cuoi_c
*/
insert into ket_qua(c1,c2,c3,n1,c5,n2,n3,n4,n5,n7,n8,c6)
    select ma_dvi,ma,'',0,seri,dau,cuoi,cuoi-dau+1,0,0,0,ma_tke
    from hd_2 where (ma_dvi,so_id) in (select c1,n1 from temp_1) and (b_ma is null or ma=b_ma) and ma_tke in ('M','H');
update ket_qua set (c3,n7,n1)=(select ten,so_to,do_dai from hd_ma_hd where ma_dvi=c1 and ma=c2);
update ket_qua set c12=lpad(rtrim(to_char(n2)),n1-length(c5),'0');
update ket_qua set c13=lpad(rtrim(to_char(n3)),n1-length(c5),'0');
if b_loai_bp='D' then select ten into b_ten from ht_ma_dvi where ma=b_ma_bp;
elsif b_loai_bp='B' then select ten into b_ten from ht_ma_phong where ma_dvi=b_ma_dvi and ma=b_ma_bp;
elsif b_loai_bp='C' then select ten into b_ten from ht_ma_nsd where ma_dvi=b_ma_dvi and ma=b_ma_bp;
end if;
delete ket_qua where c2 not in (select nv||'>'||ma from hd_ma_hd where ma_dvi=b_ma_dvi and (b_nhom is null or ma_nhom=b_nhom));
open cs_kq for select c6 loai,c2 ma,c3 ten,c5 seri,c12 dau,c13 cuoi,n4 so_luong
    from ket_qua order by c6,c2,c5,n2,n3;
exception when others then raise_application_error(-20105,b_loi);
end;
/

create or replace procedure BC_HD_LKE

    (b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_ngayd number,b_ngayc number,

    b_loai_bp varchar2,b_ma_bp varchar2,b_ma varchar2,b_nhom varchar2,b_ten out nvarchar2,cs_kq out pht_type.cs_type)

AS

    b_loi varchar2(150);b_i1 number;b_c10 varchar2(10);b_c1 varchar2(1);b_c2 varchar2(1);

begin

--Lan--Liet ke chung tu theo ngay

--b_loi:=PHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'KT','023');

--if b_loi is not null then raise PROGRAM_ERROR; end if;

---Lay ma dvi---

delete temp_1;

if b_ma_bp is null then b_loi:='loi:Nhap bo phan:loi'; raise PROGRAM_ERROR; end if;

insert into temp_1(n15,c1,c2,c3,c4,n1,c6,n2,n3,n5,n10) select ngay_ht,PKH_SO_CNG(ngay_ht),

       l_ct,so_ct,ma,0,seri,dau,cuoi,gia,a.so_id from hd_1 a,hd_2 b where a.ma_dvi=b_ma_dvi and a.ma_dvi=b.ma_dvi and a.ngay_ht between b_ngayd and b_ngayc

       and a.so_id=b.so_id and (b_loai_bp is null or loai_n=b_loai_bp) and (b_ma_bp is null or ma_n=b_ma_bp) and (b_ma is null or ma=b_ma);

update temp_1 set (c5,n1)=(select ten,do_dai from hd_ma_hd where ma_dvi=b_ma_dvi and ma=c4);

/*

update temp_1 set n4=n3-n2+1;

update temp_1 set n6=n4*n5;

update temp_1 set c7=lpad(rtrim(to_char(n2)),n1-length(c6),'0');

update temp_1 set c8=lpad(rtrim(to_char(n3)),n1-length(c6),'0');

*/

-- Hung sua thanh 01 dong

update temp_1 set n4=n3-n2+1, n6=(n3-n2+1)*n5, c7=lpad(rtrim(to_char(n2)),n1-length(c6),'0'),c8=lpad(rtrim(to_char(n3)),n1-length(c6),'0');

--

if b_loai_bp='D' then select nvl(min(ten),' ') into b_ten from ht_ma_dvi where ma=b_ma_bp;

elsif b_loai_bp='B' then select nvl(min(ten),' ') into b_ten from ht_ma_phong where ma_dvi=b_ma_dvi and ma=b_ma_bp;

elsif b_loai_bp='C' then select nvl(min(ten),' ') into b_ten from ht_ma_cb where ma_dvi=b_ma_dvi and ma=b_ma_bp;

elsif b_loai_bp='L' then select nvl(min(ten),' ') into b_ten from bh_dl_ma_kh where ma_dvi=b_ma_dvi and ma=b_ma_bp;

end if;

delete temp_1 where c4 not in (select nv || '>' ||ma from hd_ma_hd where ma_dvi=b_ma_dvi and (b_nhom is null or ma_nhom=b_nhom));

open cs_kq for select c1 ngay,c2 l_ct,c3 so_ct,c4 ma,c5 ten,c6 seri,c7 dau,c8 cuoi,n4 so_luong,n5 gia,n6 tien

    from temp_1 order by n15,n10,c2,c3,c4;

exception when others then raise_application_error(-20105,b_loi);

end;