AAA

CREATE OR REPLACE procedure PBH_HD_DO_TT_HD(
    b_ma_dvi varchar2,b_nsd varchar2,b_pas varchar2,b_oraIn clob,b_oraOut out clob)
AS
    b_loi varchar2(2000); b_nha_bh varchar2(20); cs_ton clob:='';
begin
-- Dan - Liet ke ton
delete temp_1; delete ket_qua; commit;
b_loi:=FHT_MA_NSD_KTRA(b_ma_dvi,b_nsd,b_pas,'BH','DO','NX');
if b_loi is not null then raise PROGRAM_ERROR; end if;
b_nha_bh:=FKH_JS_GTRI(b_oraIn,'nha_bh');
insert into temp_1(n1,n2,c1,c2,c3,c4,n3,n4) select so_id,so_id_ps,nhom,loai,nv,ma_nt,tien,thue
    from bh_hd_do_ps where ma_dvi=b_ma_dvi and nha_bh=b_nha_bh union
select so_id,so_id_ps,nhom,loai,nv,ma_nt,-tien,-thue
    from bh_hd_do_tt a,bh_hd_do_ct b where a.ma_dvi=b_ma_dvi and a.nha_bh=b_nha_bh and
    b.ma_dvi=b_ma_dvi and b.so_id_tt=a.so_id_tt;
insert into ket_qua(n1,n2,c1,c2,c3,c4,n3,n4) select n1,n2,c1,c2,c3,c4,sum(n3),sum(n4)
    from temp_1 group by n1,n2,c1,c2,c3,c4 having sum(n3)<>0;
select JSON_ARRAYAGG(json_object(
    'kieu' value kieu,'ngay_ht' value ngay_ht,'so_hd' value so_hd,
    'ma_nt' value ma_nt,'tien' value tien,'thue' value thue,'loai' value loai,'chon' value ' ',
    'nhom' value nhom,'nv' value nv,'so_id' value so_id,'so_id_ps' value so_id_ps)
    order by kieu,so_hd,ngay_ht,nhom,loai,ma_nt returning clob) into cs_ton from
    (select n1 so_id,n2 so_id_ps,c1 nhom,c2 loai,c3 nv,c4 ma_nt,n3 tien,n4 thue,
    FBH_DONG(b_ma_dvi,n1) kieu,FBH_HD_GOC_SO_HD(b_ma_dvi,n1) so_hd,
    FBH_HD_DO_TT_NGAY_HT(b_ma_dvi,n2) ngay_ht from ket_qua);
select json_object('cs_ton' value cs_ton returning clob) into b_oraOut from dual;
exception when others then if b_loi is null then raise PROGRAM_ERROR; else raise_application_error(-20105,b_loi); end if;
end;
/