Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION HIS_MEDICINE."NOT_CTTOATHUOC_DINHMUC_INS"(p_DVTT IN VARCHAR2,
- p_MA_TOA_THUOC IN VARCHAR2,
- p_MAKHOVATTU IN NUMBER,
- p_MAVATTU_DINMUC IN NUMBER,
- p_NGHIEP_VU IN VARCHAR2,
- P_SOLUONG IN NUMBER,
- p_SO_LUONG_THUC_LINH IN NUMBER,
- p_DONGIA_BAN_BV IN NUMBER,
- p_DONGIA_BAN_BH IN NUMBER,
- P_THANHTIEN IN NUMBER,
- p_SO_NGAY_UONG IN NUMBER,
- p_SANG_UONG IN NUMBER,
- p_TRUA_UONG IN NUMBER,
- p_CHIEU_UONG IN NUMBER,
- p_TOI_UONG IN NUMBER,
- p_NGAY_RA_TOA IN TIMESTAMP,
- p_GHI_CHU_CT_TOA_THUOC IN VARCHAR2,
- p_MA_BAC_SI_THEMTHUOC IN NUMBER,
- p_CACH_SU_DUNG IN VARCHAR2,
- p_stt_dieutri IN VARCHAR2,
- p_stt_benhan IN VARCHAR2,
- p_stt_dotdieutri IN VARCHAR2,
- p_TU_TU_THUOC IN NUMBER,
- p_CO_BHYT IN NUMBER,
- p_namhientai IN NUMBER,
- p_MABENHNHAN IN NUMBER,
- p_sophieuthanhtoan IN VARCHAR2,
- p_ngayhientai IN DATE,
- p_dathanhtoanroi IN NUMBER,
- p_maphongban IN VARCHAR2,
- p_sovaovien IN NUMBER,
- p_sovaovien_dt IN NUMBER,
- P_NGAYCHUYEN IN DATE,
- p_magoidichvu IN NUMBER DEFAULT NULL,
- P_ID_DINHMUC IN NUMBER,
- P_SOLUONG_DINHMUC IN NUMBER,
- p_sophieu IN VARCHAR2,
- p_macls IN NUMBER)
- -----
- -- P_NGHIEP_VU VARCHAR2,
- -- P_DVTT VARCHAR2,
- -- P_SOPHIEUCHUYEN VARCHAR2,
- -- P_MADONVIGIAO VARCHAR2,
- -- P_MADONVINHAN VARCHAR2,
- -- P_MAVATTU NUMBER,
- --P_TENVATTU VARCHAR2,
- --P_SOLUONG NUMBER,
- -- P_DONGIA NUMBER,
- -- P_THANHTIEN NUMBER,
- -- P_NGUOINHAP NUMBER,
- --P_GHICHU VARCHAR2
- RETURN NUMBER IS
- P_MAX_KHOVATTUCHITIET NUMBER(10);
- P_MAX_NHAPKHOCHITIET NUMBER(10);
- P_MNGAY DATE;
- P_SOLOSANXUAT VARCHAR2(100);
- P_NGAYSANXUAT DATE;
- P_NGAYHETHAN DATE;
- P_MAXHIENTAI NUMBER(18, 4);
- P_SAISOT NUMBER(11);
- P_DUYET NUMBER(11);
- P_MACHUYENKHOVATTU VARCHAR2(1000);
- P_TONTAI_DONVINHAN NUMBER(11) DEFAULT 0;
- V_THANHTIEN NUMBER(18, 4);
- V_SOLUONGCANTRU NUMBER(18, 4);
- V_KEY_QL VARCHAR2(100);
- V_SOLUONG_CK NUMBER(18, 4);
- V_SOLOSANXUAT VARCHAR2(50);
- V_DONGIA NUMBER(18, 4);
- V_FINISHED NUMBER(10) DEFAULT 0;
- V_NGAYSANXUAT DATE;
- V_SONHAPKHOCHITIET NUMBER(10) DEFAULT 0;
- V_SONHAPKHOCHITIET_CU NUMBER(10) DEFAULT 0;
- V_ERR VARCHAR2(500);
- V_KT_TONTAI_TRONNGAY NUMBER(11) DEFAULT 0;
- E_SL_XUAT_NV3_NGOAI NUMBER(11);
- V_NGAYBIENDONG DATE;
- V_NGAYBIENDONG2 DATE;
- V_I NUMBER(5) := 0;
- V_COUNT NUMBER(5) := 0;
- V_CK NUMBER(18, 4);
- V_XULYKHO NUMBER(11) := 0;
- V_MAX_NGAYBIENDONG DATE;
- V_CHK_SL_TON NUMBER(18, 4) := 0;
- V_CHK_SL_CHUYENDI NUMBER(18, 4) := 0;
- V_ROLLBACK NUMBER(1) := 0;
- v_stt_toathuoc NUMBER(11);
- v_KyHieuNhomBC VARCHAR2(100) DEFAULT '';
- v_HAM_LUONG VARCHAR2(4000) DEFAULT '';
- v_DUONG_DUNG VARCHAR2(150) DEFAULT '';
- v_SO_DANG_KY VARCHAR2(500) DEFAULT '';
- v_KEY_QLDM NUMBER(11);
- v_NGAY_RA_TOA TIMESTAMP;
- v_MaLoaiVatTu VARCHAR2(20);
- v_thanghientai NUMBER(10) DEFAULT 1;
- V_KIEMTRAKHO NUMBER(11);
- V_SUM NUMBER(18, 4);
- v_id_dieutri NUMBER(11);
- p_bant NUMBER(1);
- v_ngoaidanhmuc NUMBER(1) DEFAULT 0;
- v_laydongia VARCHAR2(50);
- v_dongiaban_taiquaythuoc NUMBER(18, 4);
- v_stt_order NUMBER(11) := 0;
- v_ngaychuyen DATE;
- v_xacnhan NUMBER(11);
- v_check_chot_duoc NUMBER(11);
- v_giochot NUMBER(11);
- v_thamsochotkhoduoc NUMBER(1);
- p_MAVATTU NUMBER(18);
- P_TENVATTU VARCHAR2(500);
- p_HOAT_CHAT VARCHAR2(500);
- p_DVT VARCHAR2(500);
- v_thamso91133 NUMBER(1);
- v_makhovattu NUMBER(11);
- BEGIN
- v_giochot := TO_NUMBER(TO_CHAR(SYSDATE, 'hh24'));
- --Lấy tham số sử dụng toa thuốc tổng hợp không phân kho
- BEGIN
- SELECT TO_NUMBER(mota_thamso)
- INTO v_thamso91133
- FROM his_fw.dm_thamso_donvi
- WHERE dvtt = p_dvtt
- AND ma_thamso = 91133;
- EXCEPTION
- WHEN NO_DATA_FOUND THEN
- v_thamso91133 := 0;
- END;
- --them bat exception
- BEGIN
- SELECT TO_NUMBER(mota_thamso)
- INTO v_thamsochotkhoduoc
- FROM his_fw.dm_thamso_donvi
- WHERE dvtt = p_dvtt
- AND ma_thamso = 313;
- EXCEPTION
- WHEN NO_DATA_FOUND THEN
- v_thamsochotkhoduoc := 0;
- END;
- --end
- IF p_TU_TU_THUOC = 0 AND v_thamsochotkhoduoc = 1 AND
- (v_giochot > 17 OR v_giochot < 7) THEN
- RETURN 200; --- chot kho kho
- END IF;
- P_SAISOT := 0;
- V_THANHTIEN := P_THANHTIEN;
- -- SET TRANSACTION READ WRITE NAME 'not_cttoathuoc_ins_svv';
- V_THANHTIEN := P_SOLUONG * p_DONGIA_BAN_BH;
- P_MAX_KHOVATTUCHITIET := 0;
- P_MAX_NHAPKHOCHITIET := 0;
- P_MNGAY := SYSTIMESTAMP;
- V_SOLUONGCANTRU := P_SOLUONG;
- SELECT COUNT(1)
- INTO v_check_chot_duoc
- FROM his_manager.DUOC_CHOTSOLIEU_BAOCAO
- WHERE dvtt = p_dvtt
- AND TRUNC(p_NGAY_RA_TOA) BETWEEN NGAY_BAT_DAU AND NGAY_KET_THUC
- AND TRANGTHAI = 1;
- IF v_check_chot_duoc > 0 THEN
- RETURN 100;
- END IF;
- SELECT id_dieutri
- INTO v_id_dieutri
- FROM his_manager.NOITRU_DIEUTRI
- WHERE DVTT = p_dvtt
- AND stt_dieutri = p_stt_dieutri
- AND stt_benhan = p_stt_benhan
- AND stt_dotdieutri = p_stt_dotdieutri
- AND sovaovien = p_sovaovien
- AND sovaovien_dt = p_sovaovien_dt;
- SELECT his_manager.SQ_KIEMTRAKHO.NEXTVAL INTO V_KIEMTRAKHO FROM DUAL;
- SELECT his_manager.SQ_XULYKHO.NEXTVAL INTO V_XULYKHO FROM DUAL;
- SELECT BANT, NGAY_RA_TOA
- INTO p_bant, v_ngaychuyen
- FROM his_manager.NOITRU_TOA_THUOC
- WHERE DVTT = p_dvtt
- AND stt_dieutri = p_stt_dieutri
- AND stt_benhan = p_stt_benhan
- AND stt_dotdieutri = p_stt_dotdieutri
- AND sovaovien = p_sovaovien
- AND sovaovien_dt = p_sovaovien_dt
- AND ID_DIEUTRI = v_id_dieutri;
- BEGIN
- SELECT XAC_NHAN
- INTO v_xacnhan
- FROM his_manager.NOITRU_CT_TOA_THUOC
- WHERE dvtt = p_DVTT
- AND MA_TOA_THUOC = p_MA_TOA_THUOC
- AND stt_dieutri = p_stt_dieutri
- AND stt_benhan = p_stt_benhan
- AND stt_dotdieutri = p_stt_dotdieutri
- AND ROWNUM = 1;
- EXCEPTION
- WHEN NO_DATA_FOUND THEN
- v_xacnhan := 0;
- END;
- IF p_bant = 0 THEN
- v_ngaychuyen := P_NGAYCHUYEN;
- END IF;
- IF v_xacnhan = 1 THEN
- P_SAISOT := 5;
- END IF;
- v_NGAY_RA_TOA := TO_DATE(TO_CHAR(v_ngaychuyen, 'yyyy-mm-dd') || ' ' ||
- TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS'),
- 'yyyy-mm-dd HH24:MI:SS');
- v_thanghientai := EXTRACT(MONTH FROM v_ngaychuyen);
- IF v_thamso91133=1 THEN
- --KIEM TRA TON KHO HIEN TAI
- INSERT INTO his_manager.KIEMTRA_TONKHO_CHUYEN
- (SONHAPKHOCHITIET,
- DVTT,
- MAKHOVATTU,
- SL_TON,
- NGAYBIENDONG,
- MINNGAYBIENDONG,
- MAXNGAYBIENDONG,
- ID_KIEMTRAKHO)
- SELECT SONHAPKHOCHITIET,
- a.DVTT,
- MAKHOVATTU,
- SL_TON,
- NGAYBIENDONG,
- MIN(NGAYBIENDONG) OVER(PARTITION BY SONHAPKHOCHITIET, a.DVTT, MAKHOVATTU) MINNGAYBIENDONG,
- MAX(NGAYBIENDONG) OVER(PARTITION BY SONHAPKHOCHITIET, a.DVTT, MAKHOVATTU) MAXNGAYBIENDONG,
- V_KIEMTRAKHO
- FROM his_manager.DC_TB_XNT_BIENDONG A
- JOIN HIS_MANAGER.DC_TB_VATTU B
- ON A.DVTT = B.DVTT
- AND A.MAVATTU = B.MAVATTU
- WHERE MAKHOVATTU IN
- (SELECT DISTINCT nks.makhovattu FROM HIS_MANAGER.NOITRU_KHOAPHONG_SUDUNGKHODUOC nks
- WHERE nks.dvtt=p_DVTT AND nks.maphongban=p_maphongban AND nks.nghiepvu=p_NGHIEP_VU)
- AND A.DVTT = P_DVTT
- AND DINHMUC_MA_NHOM_VATTU = p_MAVATTU_DINMUC
- AND KHOA_SONHAPKHOCHITIET = 0
- AND NGAYBIENDONG <= v_ngaychuyen
- AND HOAT_DONG = 1;
- --LAY TON KHO HIEN TAI
- SELECT SUM(SL_TON)
- INTO V_SUM
- FROM his_manager.KIEMTRA_TONKHO_CHUYEN
- WHERE NGAYBIENDONG = MAXNGAYBIENDONG
- AND DVTT = P_DVTT
- AND ID_KIEMTRAKHO = V_KIEMTRAKHO
- AND MAKHOVATTU IN
- (SELECT DISTINCT nks.makhovattu FROM HIS_MANAGER.NOITRU_KHOAPHONG_SUDUNGKHODUOC nks
- WHERE nks.dvtt=p_DVTT AND nks.maphongban=p_maphongban AND nks.nghiepvu=p_NGHIEP_VU);
- IF V_SUM >= P_SOLUONG THEN
- -----------------ID_TEMCHUYENKHO = V_XULYKHO
- INSERT INTO his_manager.TMP_CHUYENKHO
- (SONHAPKHOCHITIET,
- NGAYBIENDONG,
- DVTT,
- ID_TEMCHUYENKHO,
- MAKHOVATTU,
- MAVATTU)
- SELECT SONHAPKHOCHITIET,
- MAX(NGAYBIENDONG),
- P_DVTT,
- V_XULYKHO,
- a.makhovattu,
- a.MAVATTU
- FROM his_manager.DC_TB_XNT_BIENDONG A
- JOIN HIS_MANAGER.DC_TB_VATTU B
- ON A.DVTT = B.DVTT
- AND A.MAVATTU = B.MAVATTU
- WHERE MAKHOVATTU IN
- (SELECT DISTINCT nks.makhovattu FROM HIS_MANAGER.NOITRU_KHOAPHONG_SUDUNGKHODUOC nks
- WHERE nks.dvtt=p_DVTT AND nks.maphongban=p_maphongban AND nks.nghiepvu=p_NGHIEP_VU)
- AND a.DVTT = P_DVTT
- AND DINHMUC_MA_NHOM_VATTU = p_MAVATTU_DINMUC
- AND HOAT_DONG = 1
- AND KHOA_SONHAPKHOCHITIET = 0
- AND NGAYBIENDONG <= v_ngaychuyen
- -- AND DONGIA = P_DONGIA//Khong phan biet don gia
- AND EXISTS (SELECT NULL
- FROM his_manager.KIEMTRA_TONKHO_CHUYEN B
- WHERE A.SONHAPKHOCHITIET = B.SONHAPKHOCHITIET
- AND A.DVTT = B.DVTT
- AND A.NGAYBIENDONG = B.MAXNGAYBIENDONG
- AND B.NGAYBIENDONG = B.MAXNGAYBIENDONG
- AND B.SL_TON > 0
- AND b.dvtt = P_DVTT
- AND ID_KIEMTRAKHO = V_KIEMTRAKHO
- AND b.makhovattu = a.makhovattu)
- --AND NGAYNHAPKHO <= v_ngaychuyen
- GROUP BY SONHAPKHOCHITIET, a.MAVATTU, a.makhovattu
- ORDER BY SONHAPKHOCHITIET;
- SELECT SUM(SL_TON)
- INTO P_MAXHIENTAI
- FROM his_manager.DC_TB_XNT_BIENDONG A
- JOIN HIS_MANAGER.DC_TB_VATTU B
- ON A.DVTT = B.DVTT
- AND A.MAVATTU = B.MAVATTU
- WHERE A.MAKHOVATTU IN
- (SELECT DISTINCT nks.makhovattu FROM HIS_MANAGER.NOITRU_KHOAPHONG_SUDUNGKHODUOC nks
- WHERE nks.dvtt=p_DVTT AND nks.maphongban=p_maphongban AND nks.nghiepvu=p_NGHIEP_VU)
- AND A.DVTT = P_DVTT
- AND DINHMUC_MA_NHOM_VATTU = p_MAVATTU_DINMUC
- AND TT_MOINHAT = 1
- AND HOAT_DONG = 1
- -- AND SL_TON > 0
- AND KHOA_SONHAPKHOCHITIET = 0;
- -- AND DONGIA = p_DONGIA_BAN_BH;
- /*
- INSERT INTO TMP_TEST SELECT * FROM TMP_CHUYENKHO WHERE ID_TEMCHUYENKHO = V_XULYKHO;
- COMMIT;
- */
- --GET NGAY BIEN DONG KE CAN
- IF P_MAXHIENTAI >= P_SOLUONG AND P_SOLUONG > 0 THEN
- SELECT NVL(MAX(STT_order), 0)
- INTO v_stt_order
- FROM his_manager.NOITRU_CT_TOA_THUOC
- WHERE DVTT = p_dvtt
- AND MA_TOA_THUOC = p_MA_TOA_THUOC
- AND stt_dieutri = p_stt_dieutri
- AND stt_benhan = p_stt_benhan
- AND stt_dotdieutri = p_stt_dotdieutri
- AND sovaovien = p_sovaovien
- AND sovaovien_dt = p_sovaovien_dt;
- BEGIN
- DECLARE
- CURSOR XUATKHO_CURSOR IS
- -- KHAI BÁO CON TR? QUÉT QUA S? LU?NG T?N
- SELECT KEY_QL,
- SL_TON,
- SOLOSANXUAT,
- DONGIA,
- NGAYSANXUAT,
- SONHAPKHOCHITIET,
- MAVATTU, makhovattu
- FROM (SELECT KEY_QL,
- MIN(a.SL_TON) AS SL_TON,
- SOLOSANXUAT,
- DONGIA,
- NGAYSANXUAT,
- A.SONHAPKHOCHITIET,
- NGAYNHAPKHO,
- a.MAVATTU, a.makhovattu
- --SL_XUAT_NV3_NGOAI,
- --A.NGAYBIENDONG
- FROM his_manager.DC_TB_XNT_BIENDONG A
- INNER JOIN his_manager.TMP_CHUYENKHO B
- ON A.NGAYBIENDONG >= B.NGAYBIENDONG
- AND A.SONHAPKHOCHITIET = B.SONHAPKHOCHITIET
- AND a.dvtt = b.DVTT
- AND a.makhovattu = b.makhovattu
- WHERE A.DVTT = P_DVTT
- AND A.MAKHOVATTU IN
- (SELECT DISTINCT nks.makhovattu FROM HIS_MANAGER.NOITRU_KHOAPHONG_SUDUNGKHODUOC nks
- WHERE nks.dvtt=p_DVTT AND nks.maphongban=p_maphongban AND nks.nghiepvu=p_NGHIEP_VU)
- AND A.MAVATTU = B.MAVATTU
- --AND SONHAPKHOCHITIET IN (SELECT A.SONHAPKHOCHITIET FROM TMP_CHUYENKHO A)
- AND ID_TEMCHUYENKHO = V_XULYKHO
- AND a.hoat_dong = 1
- AND KHOA_SONHAPKHOCHITIET = 0
- GROUP BY KEY_QL,
- NGAYNHAPKHO,
- SOLOSANXUAT,
- DONGIA,
- NGAYSANXUAT,
- A.SONHAPKHOCHITIET,
- NGAYNHAPKHO,
- A.MAVATTU, a.makhovattu
- --SL_XUAT_NV3_NGOAI,
- --A.NGAYBIENDONG
- )
- WHERE SL_TON > 0
- -- FOR UPDATE OF A.SONHAPKHOCHITIET
- ORDER BY NGAYNHAPKHO, SONHAPKHOCHITIET ASC; --,NGAYBIENDONG
- BEGIN
- OPEN XUATKHO_CURSOR;
- LOOP
- FETCH XUATKHO_CURSOR
- INTO V_KEY_QL,
- V_SOLUONG_CK,
- V_SOLOSANXUAT,
- V_DONGIA,
- V_NGAYSANXUAT,
- V_SONHAPKHOCHITIET,
- p_MAVATTU, v_makhovattu; --,
- --E_SL_XUAT_NV3_NGOAI,
- --V_NGAYBIENDONG;
- EXIT WHEN XUATKHO_CURSOR%NOTFOUND;
- IF V_FINISHED = 1 THEN
- EXIT;
- END IF;
- SELECT NGAYBIENDONG
- INTO V_NGAYBIENDONG
- FROM his_manager.TMP_CHUYENKHO A
- WHERE A.DVTT = P_DVTT
- AND A.MAKHOVATTU = p_MAKHOVATTU
- AND A.MAVATTU = v_makhovattu
- AND SONHAPKHOCHITIET = V_SONHAPKHOCHITIET
- AND ID_TEMCHUYENKHO = V_XULYKHO;
- --KIEM TRA NGAY CHUYEN
- IF p_NGHIEP_VU = 'noitru_toaquaybanthuocbv' THEN
- BEGIN
- SELECT mota_thamso
- INTO v_laydongia
- FROM HIS_FW.DM_THAMSO_DONVI
- WHERE dvtt = p_dvtt
- AND MA_THAMSO = 210;
- EXCEPTION
- WHEN NO_DATA_FOUND THEN
- v_laydongia := '0';
- END;
- BEGIN
- SELECT DonGia_BV
- INTO v_dongiaban_taiquaythuoc
- FROM his_manager.dc_tb_vattu
- WHERE DVTT = p_DVTT
- AND MaVatTu = p_MAVATTU;
- EXCEPTION
- WHEN NO_DATA_FOUND THEN
- v_finished := 1;
- END;
- IF v_laydongia = '1' THEN
- v_dongiaban_taiquaythuoc := v_dongia;
- END IF;
- ELSE
- v_dongiaban_taiquaythuoc := v_dongia;
- END IF;
- SELECT vt.malienthong_dmdc,
- KyHieuNhomBC,
- vt.HamLuong,
- vt.maduongdung_dmdc,
- vt.so_dangky_dmdc,
- nhom.maloaivattu,
- VT.NGOAIDANHMUCBHYT,
- TENVATTU,
- HOATCHAT,
- DVT
- INTO v_KEY_QLDM,
- v_KyHieuNhomBC,
- v_HAM_LUONG,
- v_DUONG_DUNG,
- v_SO_DANG_KY,
- v_MaLoaiVatTu,
- v_ngoaidanhmuc,
- P_TENVATTU,
- p_HOAT_CHAT,
- p_DVT
- FROM his_manager.dc_tb_nhomvattu nhom,
- his_manager.dc_tb_vattu vt
- WHERE nhom.DVTT = p_DVTT
- AND vt.DVTT = p_DVTT
- AND nhom.TamNgung = 0
- AND vt.MaVatTu = p_MAVATTU
- AND vt.MaNhomVatTu = nhom.MaNhomVatTu
- AND ROWNUM = 1;
- IF V_SOLUONG_CK >= V_SOLUONGCANTRU AND V_SOLUONGCANTRU > 0 THEN
- -- -------------------------------- TH?C HI?N C?U L?NH
- --------------------// ??Y V?O C?C B?NG C?N LUU TUONG ?NG V?I NGHI?P V?
- SELECT NVL(MAX(CAST(NVL(his_manager.SUBSTRING_INDEX(STT_TOATHUOC,
- '_',
- -1),
- 0) AS NUMBER(10, 0))),
- 0)
- INTO v_stt_toathuoc
- FROM his_manager.NOITRU_CT_TOA_THUOC
- WHERE DVTT = p_dvtt
- AND MA_TOA_THUOC = p_MA_TOA_THUOC
- AND stt_dieutri = p_stt_dieutri
- AND stt_benhan = p_stt_benhan
- AND stt_dotdieutri = p_stt_dotdieutri
- AND sovaovien = p_sovaovien
- AND sovaovien_dt = p_sovaovien_dt;
- INSERT INTO his_manager.NOITRU_CT_TOA_THUOC
- (STT_TOATHUOC,
- STT_order,
- DVTT,
- MA_TOA_THUOC,
- MAKHOVATTU,
- MAVATTU,
- TEN_VAT_TU,
- HOAT_CHAT,
- DVT,
- NGHIEP_VU,
- SO_LUONG,
- SO_LUONG_THUC_LINH,
- DONGIA_BAN_BV,
- DONGIA_BAN_BH,
- THANHTIEN_THUOC,
- SO_NGAY_UONG,
- SANG_UONG,
- TRUA_UONG,
- CHIEU_UONG,
- TOI_UONG,
- NGAY_RA_TOA,
- GHI_CHU_CT_TOA_THUOC,
- MA_BAC_SI_THEMTHUOC,
- CACH_SU_DUNG,
- NAM,
- stt_dieutri,
- stt_benhan,
- stt_dotdieutri,
- TU_TU_THUOC,
- NGAY,
- THANG,
- mabenhnhan,
- khoalap,
- TRANG_THAI,
- KEY_QL,
- SOLOSANXUAT,
- NGAYSANXUAT,
- MaLoaiVatTu,
- KyHieuNhomBC,
- HAM_LUONG,
- DUONG_DUNG,
- SO_DANG_KY,
- sovaovien,
- sovaovien_dt,
- SONHAPKHOCHITIET,
- ID_DIEUTRI,
- BANT,
- NGOAI_DANH_MUC,
- MAGOIDICHVU,
- ID_DINHMUC,
- SOLUONG_DINHMUC,
- SO_PHIEU_XN,
- MA_XN,
- SO_PHIEU_CDHA,
- MA_CDHA,
- SO_PHIEU_DV,
- MA_DV)
- VALUES
- (p_MA_TOA_THUOC || '_' ||
- CAST((v_stt_toathuoc + 1) AS VARCHAR2(50)),
- v_stt_order + 1,
- p_DVTT,
- p_MA_TOA_THUOC,
- v_makhovattu,
- p_MAVATTU,
- P_TENVATTU,
- p_HOAT_CHAT,
- p_DVT,
- p_NGHIEP_VU,
- v_soluongcantru, -- p_SO_LUONG ,
- v_soluongcantru, -- pp_SO_LUONG_THUC_LINH ,
- v_dongiaban_taiquaythuoc, -- p_DONGIA_BAN_BV ,
- v_dongia, -- p_DONGIA_BAN_BH ,
- v_soluongcantru * v_dongiaban_taiquaythuoc, -- p_THANHTIEN_THUOC ,
- p_SO_NGAY_UONG,
- p_SANG_UONG,
- p_TRUA_UONG,
- p_CHIEU_UONG,
- p_TOI_UONG,
- v_NGAY_RA_TOA,
- p_GHI_CHU_CT_TOA_THUOC,
- p_MA_BAC_SI_THEMTHUOC,
- p_CACH_SU_DUNG,
- p_namhientai,
- p_stt_dieutri,
- p_stt_benhan,
- p_stt_dotdieutri,
- p_TU_TU_THUOC,
- v_ngaychuyen,
- v_thanghientai,
- p_MABENHNHAN,
- p_maphongban,
- 1,
- v_KEY_QL,
- v_SOLOSANXUAT,
- v_NGAYSANXUAT,
- v_MaLoaiVatTu,
- v_KyHieuNhomBC,
- v_HAM_LUONG,
- v_DUONG_DUNG,
- v_SO_DANG_KY,
- p_sovaovien,
- p_sovaovien_dt,
- v_SONHAPKHOCHITIET,
- v_id_dieutri,
- p_bant,
- v_ngoaidanhmuc,
- p_magoidichvu,
- P_ID_DINHMUC,
- P_SOLUONG_DINHMUC,
- CASE WHEN UPPER(SUBSTR(p_sophieu, 1, 2)) = 'XN' THEN
- p_sophieu ELSE NULL END,
- CASE WHEN UPPER(SUBSTR(p_sophieu, 1, 2)) = 'XN' THEN
- p_macls ELSE NULL END,
- CASE WHEN UPPER(SUBSTR(p_sophieu, 1, 2)) = 'CD' THEN
- p_sophieu ELSE NULL END,
- CASE WHEN UPPER(SUBSTR(p_sophieu, 1, 2)) = 'CD' THEN
- p_macls ELSE NULL END,
- CASE WHEN UPPER(SUBSTR(p_sophieu, 1, 2)) = 'DV' THEN
- p_sophieu ELSE NULL END,
- CASE WHEN UPPER(SUBSTR(p_sophieu, 1, 2)) = 'DV' THEN
- p_macls ELSE NULL END);
- IF SQL%ROWCOUNT <= 0 THEN
- v_rollback := 1;
- END IF;
- -- them vao tru kho benh vien --------------------------------------------------------
- INSERT INTO his_public_list.ls_sudungthuoc
- (MA_BENH_NHAN,
- DVTT,
- MA_TOA_THUOC,
- STT_TOATHUOC,
- MAVATTU,
- TEN_VAT_TU,
- HOAT_CHAT,
- SO_LUONG,
- DONGIA,
- THANHTIEN,
- SO_NGAY_UONG,
- SANG_UONG,
- TRUA_UONG,
- CHIEU_UONG,
- TOI_UONG,
- NGAY_RA_TOA,
- NGAY_HET_THUOC,
- MA_BAC_SI_THEMTHUOC,
- GHI_CHU_CT_TOA_THUOC,
- NGHIEP_VU,
- NOITRU,
- STT_DIEUTRI,
- STT_BENHAN,
- STT_DOTDIEUTRI,
- SOVAOVIEN_NOI,
- SOVAOVIEN_DT,
- ID_DIEUTRI)
- VALUES
- (p_MABENHNHAN,
- p_DVTT,
- p_MA_TOA_THUOC,
- p_MA_TOA_THUOC || '_' ||
- CAST((v_stt_toathuoc + 1) AS VARCHAR2(50)),
- p_MAVATTU,
- P_TENVATTU,
- p_HOAT_CHAT,
- V_soluongcantru, -- p_SO_LUONG,
- v_dongiaban_taiquaythuoc, -- p_DONGIA_BAN_BV,
- V_soluongcantru * v_dongiaban_taiquaythuoc, -- V_THANHTIEN_THUOC,
- p_SO_NGAY_UONG,
- p_SANG_UONG,
- p_TRUA_UONG,
- p_CHIEU_UONG,
- p_TOI_UONG,
- v_ngaychuyen,
- v_ngaychuyen + p_SO_NGAY_UONG,
- p_MA_BAC_SI_THEMTHUOC,
- p_GHI_CHU_CT_TOA_THUOC,
- p_NGHIEP_VU,
- 1,
- p_stt_dieutri,
- p_stt_benhan,
- p_stt_dotdieutri,
- p_sovaovien,
- p_sovaovien_dt,
- v_id_dieutri);
- --------------------END // ??Y V?O C?C B?NG C?N LUU TUONG ?NG V?I NGHI?P V?
- -- X? L? NGHI?P V? KHO
- V_KT_TONTAI_TRONNGAY := 0;
- SELECT COUNT(1)
- INTO V_KT_TONTAI_TRONNGAY
- FROM his_manager.DC_TB_XNT_BIENDONG
- WHERE --KEY_QL = V_KEY_QL
- DVTT = P_DVTT
- AND MAKHOVATTU =v_makhovattu
- AND MAVATTU = P_MAVATTU
- AND SONHAPKHOCHITIET = V_SONHAPKHOCHITIET
- AND NGAYBIENDONG = TRUNC(v_ngaychuyen);
- BEGIN
- IF V_KT_TONTAI_TRONNGAY > 0 THEN
- --IF V_I = 1 THEN
- -- NEU NGAY BIEN DONG LA NGUYEN CHUYEN THI CAP NHAT SO LUONG CHUYEN DI
- UPDATE his_manager.DC_TB_XNT_BIENDONG
- SET SL_TON = SL_TON - V_SOLUONGCANTRU,
- SL_XUAT_NV3_NOI = SL_XUAT_NV3_NOI + V_SOLUONGCANTRU
- WHERE --KEY_QL = V_KEY_QL
- DVTT = P_DVTT
- AND MAKHOVATTU = v_makhovattu
- AND MAVATTU = P_MAVATTU
- AND SONHAPKHOCHITIET = V_SONHAPKHOCHITIET
- AND NGAYBIENDONG = TRUNC(v_ngaychuyen);
- IF sql%rowcount = 0 THEN
- V_ROLLBACK := 1;
- END IF;
- ELSE
- INSERT INTO his_manager.DC_TB_XNT_BIENDONG
- (KEY_QL,
- DVTT,
- MAKHOVATTU,
- MAVATTU,
- TENVATTU,
- DONGIA,
- NGAY,
- SOLOSANXUAT,
- NGAYSANXUAT,
- NGAYHETHAN,
- HOATCHAT,
- HAMLUONG,
- DVT,
- CACHSUDUNG,
- NGOAIDANHMUCBHYT,
- HOAT_DONG,
- NGAYNHAPKHO,
- SONHAPKHOCHITIET,
- NGAYBIENDONG,
- SL_NHAP,
- SL_CHUYENDEN,
- SL_CHUYENDI,
- SL_HUY,
- SL_XUAT_KO_NV3,
- SL_XUAT_NV3_NGOAI,
- SL_XUAT_NV3_NOI,
- SL_HOANTRA_DI,
- SL_HOANTRA_DEN,
- SL_HOANTRA_NCC,
- SL_HOANTRA_NV3_NGOAI,
- SL_HOANTRA_NV3_NOI,
- SL_TON,
- TT_BIENDONG,
- TT_MOINHAT,
- THANGBIENDONG_SAUSO,
- NAMBIENDONG,
- DV_INT)
- SELECT KEY_QL,
- DVTT,
- MAKHOVATTU,
- MAVATTU,
- TENVATTU,
- DONGIA,
- NGAY,
- SOLOSANXUAT,
- NGAYSANXUAT,
- NGAYHETHAN,
- HOATCHAT,
- HAMLUONG,
- DVT,
- CACHSUDUNG,
- NGOAIDANHMUCBHYT,
- HOAT_DONG,
- NGAYNHAPKHO,
- SONHAPKHOCHITIET,
- TRUNC(v_ngaychuyen),
- 0, --SL_NHAP,
- 0, --SL_CHUYENDEN,
- 0, --SL_CHUYENDI,
- 0, --SL_HUY,
- 0, --SL_XUAT_KO_NV3,
- 0, -- SL_XUAT_NV3_NGOAI
- V_SOLUONGCANTRU, --SL_XUAT_NV3_NOI,
- 0, --SL_HOANTRA_DI,
- 0, --SL_HOANTRA_DEN,
- 0, --SL_HOANTRA_NCC,
- 0, --SL_HOANTRA_NV3_NGOAI,
- 0, --SL_HOANTRA_NV3_NOI,
- SL_TON - V_SOLUONGCANTRU,
- 0, --TT_BIENDONG,
- 1,
- --EXTRACT(MONTH FROM v_ngaychuyen) || EXTRACT(YEAR FROM P_NGAYCHUYEN)
- TO_NUMBER(TO_CHAR(v_ngaychuyen, 'MMYYYY')),
- EXTRACT(YEAR FROM v_ngaychuyen),
- P_DVTT
- FROM his_manager.DC_TB_XNT_BIENDONG
- WHERE DVTT = P_DVTT
- AND MAKHOVATTU = v_makhovattu
- AND MAVATTU = P_MAVATTU
- --AND DONGIA = V_DONGIA
- AND SONHAPKHOCHITIET = V_SONHAPKHOCHITIET
- AND NGAYBIENDONG = V_NGAYBIENDONG;
- IF sql%rowcount = 0 THEN
- V_ROLLBACK := 1;
- END IF;
- END IF;
- his_manager.CHECK_DONGBO_XNT(P_DVTT,
- v_makhovattu,
- V_SONHAPKHOCHITIET,
- v_ngaychuyen,
- 'TTNOI');
- CAPNHAT_XNT_SONHAPKHO(p_DVTT,
- v_makhovattu,
- v_SONHAPKHOCHITIET);
- SELECT MAX(NGAYBIENDONG)
- INTO V_MAX_NGAYBIENDONG
- FROM his_manager.DC_TB_XNT_BIENDONG
- WHERE DVTT = P_DVTT
- AND MAKHOVATTU = v_makhovattu
- AND MAVATTU = P_MAVATTU
- --AND DONGIA = V_DONGIA
- AND SONHAPKHOCHITIET = V_SONHAPKHOCHITIET;
- UPDATE his_manager.DC_TB_XNT_BIENDONG
- SET TT_MOINHAT = 0
- WHERE DVTT = P_DVTT
- AND MAKHOVATTU = v_makhovattu
- AND MAVATTU = P_MAVATTU
- --AND DONGIA = V_DONGIA
- AND SONHAPKHOCHITIET = V_SONHAPKHOCHITIET
- --AND NGAYSANXUAT = V_NGAYSANXUAT
- AND NGAYBIENDONG < V_MAX_NGAYBIENDONG;
- /*update NOITRU_PTT_THAMCHIEUTONGTIEN
- set tongtienthamchieu = tongtienthamchieu +
- v_dongia * V_SOLUONGCANTRU
- where SOPHIEUTHANHTOAN = p_sophieuthanhtoan
- and DVTT = p_DVTT
- and sovaovien = p_sovaovien
- and sovaovien_dt = p_sovaovien_dt;*/
- END;
- V_FINISHED := 1;
- --END// X? L? NGHI?P V? KHO
- -- ----------------------------------K?T TH?C TH?C HI?N C?U L?NH
- ELSIF V_SOLUONG_CK < V_SOLUONGCANTRU AND V_SOLUONGCANTRU > 0 THEN
- -- S? LU?NG CK NH? HON S? LU?NG C?N TR?, QU?T CON TR?
- --------------------// ??Y V?O C?C B?NG C?N LUU TUONG ?NG V?I NGHI?P V?
- SELECT NVL(MAX(CAST(NVL(his_manager.SUBSTRING_INDEX(STT_TOATHUOC,
- '_',
- -1),
- 0) AS NUMBER(10, 0))),
- 0)
- INTO v_stt_toathuoc
- FROM his_manager.NOITRU_CT_TOA_THUOC
- WHERE DVTT = p_dvtt
- AND MA_TOA_THUOC = p_MA_TOA_THUOC
- AND stt_dieutri = p_stt_dieutri
- AND stt_benhan = p_stt_benhan
- AND stt_dotdieutri = p_stt_dotdieutri
- AND sovaovien = p_sovaovien
- AND sovaovien_dt = p_sovaovien_dt;
- INSERT INTO his_manager.NOITRU_CT_TOA_THUOC
- (STT_TOATHUOC,
- STT_order,
- DVTT,
- MA_TOA_THUOC,
- MAKHOVATTU,
- MAVATTU,
- TEN_VAT_TU,
- HOAT_CHAT,
- DVT,
- NGHIEP_VU,
- SO_LUONG,
- SO_LUONG_THUC_LINH,
- DONGIA_BAN_BV,
- DONGIA_BAN_BH,
- THANHTIEN_THUOC,
- SO_NGAY_UONG,
- SANG_UONG,
- TRUA_UONG,
- CHIEU_UONG,
- TOI_UONG,
- NGAY_RA_TOA,
- GHI_CHU_CT_TOA_THUOC,
- MA_BAC_SI_THEMTHUOC,
- CACH_SU_DUNG,
- NAM,
- stt_dieutri,
- stt_benhan,
- stt_dotdieutri,
- TU_TU_THUOC,
- NGAY,
- THANG,
- mabenhnhan,
- khoalap,
- TRANG_THAI,
- KEY_QL,
- SOLOSANXUAT,
- NGAYSANXUAT,
- MaLoaiVatTu,
- KyHieuNhomBC,
- HAM_LUONG,
- DUONG_DUNG,
- SO_DANG_KY,
- sovaovien,
- sovaovien_dt,
- SONHAPKHOCHITIET,
- ID_DIEUTRI,
- BANT,
- NGOAI_DANH_MUC,
- MAGOIDICHVU,
- ID_DINHMUC,
- SOLUONG_DINHMUC,
- SO_PHIEU_XN,
- MA_XN,
- SO_PHIEU_CDHA,
- MA_CDHA,
- SO_PHIEU_DV,
- MA_DV)
- VALUES
- (p_MA_TOA_THUOC || '_' ||
- CAST((v_stt_toathuoc + 1) AS VARCHAR2(50)),
- v_stt_order + 1,
- p_DVTT,
- p_MA_TOA_THUOC,
- v_makhovattu,
- p_MAVATTU,
- P_TENVATTU,
- p_HOAT_CHAT,
- p_DVT,
- p_NGHIEP_VU,
- v_soluongcantru, -- p_SO_LUONG ,
- v_soluongcantru, -- pp_SO_LUONG_THUC_LINH ,
- v_dongiaban_taiquaythuoc, -- p_DONGIA_BAN_BV ,
- v_dongia, -- p_DONGIA_BAN_BH ,
- v_soluongcantru * v_dongiaban_taiquaythuoc, -- p_THANHTIEN_THUOC ,
- p_SO_NGAY_UONG,
- p_SANG_UONG,
- p_TRUA_UONG,
- p_CHIEU_UONG,
- p_TOI_UONG,
- v_NGAY_RA_TOA,
- p_GHI_CHU_CT_TOA_THUOC,
- p_MA_BAC_SI_THEMTHUOC,
- p_CACH_SU_DUNG,
- p_namhientai,
- p_stt_dieutri,
- p_stt_benhan,
- p_stt_dotdieutri,
- p_TU_TU_THUOC,
- v_ngaychuyen,
- v_thanghientai,
- p_MABENHNHAN,
- p_maphongban,
- 1,
- v_KEY_QL,
- v_SOLOSANXUAT,
- v_NGAYSANXUAT,
- v_MaLoaiVatTu,
- v_KyHieuNhomBC,
- v_HAM_LUONG,
- v_DUONG_DUNG,
- v_SO_DANG_KY,
- p_sovaovien,
- p_sovaovien_dt,
- v_SONHAPKHOCHITIET,
- v_id_dieutri,
- p_bant,
- v_ngoaidanhmuc,
- p_magoidichvu,
- P_ID_DINHMUC,
- P_SOLUONG_DINHMUC,
- CASE WHEN UPPER(SUBSTR(p_sophieu, 1, 2)) = 'XN' THEN
- p_sophieu ELSE NULL END,
- CASE WHEN UPPER(SUBSTR(p_sophieu, 1, 2)) = 'XN' THEN
- p_macls ELSE NULL END,
- CASE WHEN UPPER(SUBSTR(p_sophieu, 1, 2)) = 'CD' THEN
- p_sophieu ELSE NULL END,
- CASE WHEN UPPER(SUBSTR(p_sophieu, 1, 2)) = 'CD' THEN
- p_macls ELSE NULL END,
- CASE WHEN UPPER(SUBSTR(p_sophieu, 1, 2)) = 'DV' THEN
- p_sophieu ELSE NULL END,
- CASE WHEN UPPER(SUBSTR(p_sophieu, 1, 2)) = 'DV' THEN
- p_macls ELSE NULL END);
- IF SQL%ROWCOUNT <= 0 THEN
- v_rollback := 1;
- END IF;
- -- them vao tru kho benh vien --------------------------------------------------------
- INSERT INTO his_public_list.ls_sudungthuoc
- (MA_BENH_NHAN,
- DVTT,
- MA_TOA_THUOC,
- STT_TOATHUOC,
- MAVATTU,
- TEN_VAT_TU,
- HOAT_CHAT,
- SO_LUONG,
- DONGIA,
- THANHTIEN,
- SO_NGAY_UONG,
- SANG_UONG,
- TRUA_UONG,
- CHIEU_UONG,
- TOI_UONG,
- NGAY_RA_TOA,
- NGAY_HET_THUOC,
- MA_BAC_SI_THEMTHUOC,
- GHI_CHU_CT_TOA_THUOC,
- NGHIEP_VU,
- NOITRU,
- STT_DIEUTRI,
- STT_BENHAN,
- STT_DOTDIEUTRI,
- SOVAOVIEN_NOI,
- SOVAOVIEN_DT,
- ID_DIEUTRI)
- VALUES
- (p_MABENHNHAN,
- p_DVTT,
- p_MA_TOA_THUOC,
- p_MA_TOA_THUOC || '_' ||
- CAST((v_stt_toathuoc + 1) AS VARCHAR2(50)),
- p_MAVATTU,
- P_TENVATTU,
- p_HOAT_CHAT,
- v_SOLUONG_CK, -- p_SO_LUONG,
- v_dongiaban_taiquaythuoc, -- p_DONGIA_BAN_BV,
- v_SOLUONG_CK * v_dongiaban_taiquaythuoc, -- V_THANHTIEN_THUOC,
- p_SO_NGAY_UONG,
- p_SANG_UONG,
- p_TRUA_UONG,
- p_CHIEU_UONG,
- p_TOI_UONG,
- v_ngaychuyen,
- v_ngaychuyen + p_SO_NGAY_UONG,
- p_MA_BAC_SI_THEMTHUOC,
- p_GHI_CHU_CT_TOA_THUOC,
- p_NGHIEP_VU,
- 1,
- p_stt_dieutri,
- p_stt_benhan,
- p_stt_dotdieutri,
- p_sovaovien,
- p_sovaovien_dt,
- v_id_dieutri);
- --------------------END // ??Y V?O C?C B?NG C?N LUU TUONG ?NG V?I NGHI?P V?
- --// X? L? NGHI?P V? KHO
- V_KT_TONTAI_TRONNGAY := 0;
- SELECT COUNT(1)
- INTO V_KT_TONTAI_TRONNGAY
- FROM his_manager.DC_TB_XNT_BIENDONG
- WHERE --KEY_QL = V_KEY_QL
- DVTT = P_DVTT
- AND MAKHOVATTU = v_makhovattu
- AND MAVATTU = P_MAVATTU
- AND SONHAPKHOCHITIET = V_SONHAPKHOCHITIET
- AND NGAYBIENDONG = TRUNC(v_ngaychuyen); --V_NGAYBIENDONG;
- IF V_KT_TONTAI_TRONNGAY > 0 THEN
- --IF V_I = 1 THEN
- -- NEU NGAY BIEN DONG LA NGUYEN CHUYEN THI CAP NHAT SO LUONG CHUYEN DI
- UPDATE his_manager.DC_TB_XNT_BIENDONG
- SET SL_TON = 0,
- SL_XUAT_NV3_NOI = SL_XUAT_NV3_NOI + v_SOLUONG_CK
- WHERE --KEY_QL = V_KEY_QL
- DVTT = P_DVTT
- AND MAKHOVATTU = v_makhovattu
- AND MAVATTU = P_MAVATTU
- AND SONHAPKHOCHITIET = V_SONHAPKHOCHITIET
- AND NGAYBIENDONG = TRUNC(v_ngaychuyen);
- IF sql%rowcount = 0 THEN
- V_ROLLBACK := 1;
- END IF;
- ELSE
- INSERT INTO his_manager.DC_TB_XNT_BIENDONG
- (KEY_QL,
- DVTT,
- MAKHOVATTU,
- MAVATTU,
- TENVATTU,
- DONGIA,
- NGAY,
- SOLOSANXUAT,
- NGAYSANXUAT,
- NGAYHETHAN,
- HOATCHAT,
- HAMLUONG,
- DVT,
- CACHSUDUNG,
- NGOAIDANHMUCBHYT,
- HOAT_DONG,
- NGAYNHAPKHO,
- SONHAPKHOCHITIET,
- NGAYBIENDONG,
- SL_NHAP,
- SL_CHUYENDEN,
- SL_CHUYENDI,
- SL_HUY,
- SL_XUAT_KO_NV3,
- SL_XUAT_NV3_NGOAI,
- SL_XUAT_NV3_NOI,
- SL_HOANTRA_DI,
- SL_HOANTRA_DEN,
- SL_HOANTRA_NCC,
- SL_HOANTRA_NV3_NGOAI,
- SL_HOANTRA_NV3_NOI,
- SL_TON,
- TT_BIENDONG,
- TT_MOINHAT,
- THANGBIENDONG_SAUSO,
- NAMBIENDONG,
- DV_INT)
- SELECT KEY_QL,
- DVTT,
- MAKHOVATTU,
- MAVATTU,
- TENVATTU,
- DONGIA,
- NGAY,
- SOLOSANXUAT,
- NGAYSANXUAT,
- NGAYHETHAN,
- HOATCHAT,
- HAMLUONG,
- DVT,
- CACHSUDUNG,
- NGOAIDANHMUCBHYT,
- HOAT_DONG,
- NGAYNHAPKHO,
- SONHAPKHOCHITIET,
- TRUNC(v_ngaychuyen),
- 0, --SL_NHAP,
- 0, --SL_CHUYENDEN,
- 0, --SL_CHUYENDI,
- 0, --SL_HUY,
- 0, --SL_XUAT_KO_NV3,
- 0, -- SL_XUAT_NV3_NGOAI
- V_SOLUONG_CK, --SL_XUAT_NV3_NOI,
- 0, --SL_HOANTRA_DI,
- 0, --SL_HOANTRA_DEN,
- 0, --SL_HOANTRA_NCC,
- 0, --SL_HOANTRA_NV3_NGOAI,
- 0, --SL_HOANTRA_NV3_NOI,
- SL_TON - V_SOLUONG_CK,
- 0, --TT_BIENDONG,
- 1,
- TO_NUMBER(EXTRACT(MONTH FROM v_ngaychuyen) ||
- EXTRACT(YEAR FROM v_ngaychuyen)),
- EXTRACT(YEAR FROM v_ngaychuyen),
- TO_NUMBER(P_DVTT)
- FROM his_manager.DC_TB_XNT_BIENDONG
- WHERE DVTT = P_DVTT
- AND MAKHOVATTU = v_makhovattu
- AND MAVATTU = P_MAVATTU
- --AND DONGIA = V_DONGIA
- AND SONHAPKHOCHITIET = V_SONHAPKHOCHITIET
- --AND NGAYSANXUAT = V_NGAYSANXUAT
- AND NGAYBIENDONG = V_NGAYBIENDONG;
- --AND TT_MOINHAT = 1;
- IF sql%rowcount = 0 THEN
- V_ROLLBACK := 1;
- END IF;
- END IF;
- his_manager.CHECK_DONGBO_XNT(P_DVTT,
- v_makhovattu,
- V_SONHAPKHOCHITIET,
- v_ngaychuyen,
- 'TTNOI');
- CAPNHAT_XNT_SONHAPKHO(p_DVTT,
- v_makhovattu,
- v_SONHAPKHOCHITIET);
- SELECT MAX(NGAYBIENDONG)
- INTO V_MAX_NGAYBIENDONG
- FROM his_manager.DC_TB_XNT_BIENDONG
- WHERE DVTT = P_DVTT
- AND MAKHOVATTU = v_makhovattu
- AND MAVATTU = P_MAVATTU
- -- AND DONGIA = V_DONGIA
- AND SONHAPKHOCHITIET = V_SONHAPKHOCHITIET;
- UPDATE his_manager.DC_TB_XNT_BIENDONG
- SET TT_MOINHAT = 0
- WHERE DVTT = P_DVTT
- AND MAKHOVATTU = v_makhovattu
- AND MAVATTU = P_MAVATTU
- -- AND DONGIA = V_DONGIA
- AND SONHAPKHOCHITIET = V_SONHAPKHOCHITIET
- --AND NGAYSANXUAT = V_NGAYSANXUAT
- AND NGAYBIENDONG < V_MAX_NGAYBIENDONG; -- 13/06/2015
- V_SOLUONGCANTRU := V_SOLUONGCANTRU - V_SOLUONG_CK;
- /*update NOITRU_PTT_THAMCHIEUTONGTIEN
- set tongtienthamchieu = tongtienthamchieu +
- v_dongia * V_SOLUONGCANTRU
- where SOPHIEUTHANHTOAN = p_sophieuthanhtoan
- and DVTT = p_DVTT
- and sovaovien = p_sovaovien
- and sovaovien_dt = p_sovaovien_dt;*/
- --END// X? L? NGHI?P V? KHO
- ELSIF V_SOLUONGCANTRU < 0 THEN
- V_FINISHED := 1;
- END IF;
- SELECT COUNT(1)
- INTO V_CHK_SL_TON
- FROM his_manager.DC_TB_XNT_BIENDONG
- WHERE DVTT = P_DVTT
- AND MAKHOVATTU = v_makhovattu
- AND MAVATTU = P_MAVATTU
- AND NGAYBIENDONG >= TRUNC(v_ngaychuyen)
- AND SONHAPKHOCHITIET = V_SONHAPKHOCHITIET
- -- AND NGAYSANXUAT = V_NGAYSANXUAT
- AND SL_TON < 0;
- SELECT COUNT(1)
- INTO V_CHK_SL_CHUYENDI
- FROM his_manager.DC_TB_XNT_BIENDONG
- WHERE DVTT = P_DVTT
- AND MAKHOVATTU = v_makhovattu
- AND MAVATTU = P_MAVATTU
- AND NGAYBIENDONG >= TRUNC(v_ngaychuyen)
- AND SONHAPKHOCHITIET = V_SONHAPKHOCHITIET
- -- AND NGAYSANXUAT = V_NGAYSANXUAT
- AND SL_CHUYENDI < 0;
- CASE
- WHEN V_CHK_SL_TON > 0 THEN
- V_ROLLBACK := 1;
- WHEN V_CHK_SL_CHUYENDI > 0 THEN
- V_ROLLBACK := 1;
- ELSE
- V_ROLLBACK := 0;
- END CASE;
- IF V_ROLLBACK = 1 THEN
- V_FINISHED := 1;
- END IF;
- END LOOP;
- CLOSE XUATKHO_CURSOR;
- END;
- END;
- ELSE
- --P_MAXHIENTAI < P_SOLUONG
- P_SAISOT := 6; -- S? LU?NG KH?NG D?
- END IF;
- ELSE
- P_SAISOT := 6;
- END IF; --kiem tra sl ton cho phep cap thuoc
- IF V_ROLLBACK = 1 THEN
- ROLLBACK;
- END IF;
- RETURN P_SAISOT;
- ELSE --tham so 91133 = 0
- --KIEM TRA TON KHO HIEN TAI
- INSERT INTO his_manager.KIEMTRA_TONKHO_CHUYEN
- (SONHAPKHOCHITIET,
- DVTT,
- MAKHOVATTU,
- SL_TON,
- NGAYBIENDONG,
- MINNGAYBIENDONG,
- MAXNGAYBIENDONG,
- ID_KIEMTRAKHO)
- SELECT SONHAPKHOCHITIET,
- a.DVTT,
- MAKHOVATTU,
- SL_TON,
- NGAYBIENDONG,
- MIN(NGAYBIENDONG) OVER(PARTITION BY SONHAPKHOCHITIET, a.DVTT, MAKHOVATTU) MINNGAYBIENDONG,
- MAX(NGAYBIENDONG) OVER(PARTITION BY SONHAPKHOCHITIET, a.DVTT, MAKHOVATTU) MAXNGAYBIENDONG,
- V_KIEMTRAKHO
- FROM his_manager.DC_TB_XNT_BIENDONG A
- JOIN HIS_MANAGER.DC_TB_VATTU B
- ON A.DVTT = B.DVTT
- AND A.MAVATTU = B.MAVATTU
- WHERE MAKHOVATTU = p_MAKHOVATTU
- AND A.DVTT = P_DVTT
- AND DINHMUC_MA_NHOM_VATTU = p_MAVATTU_DINMUC
- AND KHOA_SONHAPKHOCHITIET = 0
- AND NGAYBIENDONG <= v_ngaychuyen
- AND HOAT_DONG = 1;
- --LAY TON KHO HIEN TAI
- SELECT SUM(SL_TON)
- INTO V_SUM
- FROM his_manager.KIEMTRA_TONKHO_CHUYEN
- WHERE NGAYBIENDONG = MAXNGAYBIENDONG
- AND DVTT = P_DVTT
- AND ID_KIEMTRAKHO = V_KIEMTRAKHO
- AND MAKHOVATTU = p_MAKHOVATTU;
- IF V_SUM >= P_SOLUONG THEN
- -----------------ID_TEMCHUYENKHO = V_XULYKHO
- INSERT INTO his_manager.TMP_CHUYENKHO
- (SONHAPKHOCHITIET,
- NGAYBIENDONG,
- DVTT,
- ID_TEMCHUYENKHO,
- MAKHOVATTU,
- MAVATTU)
- SELECT SONHAPKHOCHITIET,
- MAX(NGAYBIENDONG),
- P_DVTT,
- V_XULYKHO,
- p_MAKHOVATTU,
- a.MAVATTU
- FROM his_manager.DC_TB_XNT_BIENDONG A
- JOIN HIS_MANAGER.DC_TB_VATTU B
- ON A.DVTT = B.DVTT
- AND A.MAVATTU = B.MAVATTU
- WHERE MAKHOVATTU = p_MAKHOVATTU
- AND a.DVTT = P_DVTT
- AND DINHMUC_MA_NHOM_VATTU = p_MAVATTU_DINMUC
- AND HOAT_DONG = 1
- AND KHOA_SONHAPKHOCHITIET = 0
- AND NGAYBIENDONG <= v_ngaychuyen
- -- AND DONGIA = P_DONGIA//Khong phan biet don gia
- AND EXISTS (SELECT NULL
- FROM his_manager.KIEMTRA_TONKHO_CHUYEN B
- WHERE A.SONHAPKHOCHITIET = B.SONHAPKHOCHITIET
- AND A.DVTT = B.DVTT
- AND A.NGAYBIENDONG = B.MAXNGAYBIENDONG
- AND B.NGAYBIENDONG = B.MAXNGAYBIENDONG
- AND B.SL_TON > 0
- AND b.dvtt = P_DVTT
- AND ID_KIEMTRAKHO = V_KIEMTRAKHO
- AND b.makhovattu = p_MAKHOVATTU)
- --AND NGAYNHAPKHO <= v_ngaychuyen
- GROUP BY SONHAPKHOCHITIET, a.MAVATTU
- ORDER BY SONHAPKHOCHITIET;
- SELECT SUM(SL_TON)
- INTO P_MAXHIENTAI
- FROM his_manager.DC_TB_XNT_BIENDONG A
- JOIN HIS_MANAGER.DC_TB_VATTU B
- ON A.DVTT = B.DVTT
- AND A.MAVATTU = B.MAVATTU
- WHERE A.MAKHOVATTU = p_MAKHOVATTU
- AND A.DVTT = P_DVTT
- AND DINHMUC_MA_NHOM_VATTU = p_MAVATTU_DINMUC
- AND TT_MOINHAT = 1
- AND HOAT_DONG = 1
- -- AND SL_TON > 0
- AND KHOA_SONHAPKHOCHITIET = 0;
- -- AND DONGIA = p_DONGIA_BAN_BH;
- /*
- INSERT INTO TMP_TEST SELECT * FROM TMP_CHUYENKHO WHERE ID_TEMCHUYENKHO = V_XULYKHO;
- COMMIT;
- */
- --GET NGAY BIEN DONG KE CAN
- IF P_MAXHIENTAI >= P_SOLUONG AND P_SOLUONG > 0 THEN
- SELECT NVL(MAX(STT_order), 0)
- INTO v_stt_order
- FROM his_manager.NOITRU_CT_TOA_THUOC
- WHERE DVTT = p_dvtt
- AND MA_TOA_THUOC = p_MA_TOA_THUOC
- AND stt_dieutri = p_stt_dieutri
- AND stt_benhan = p_stt_benhan
- AND stt_dotdieutri = p_stt_dotdieutri
- AND sovaovien = p_sovaovien
- AND sovaovien_dt = p_sovaovien_dt;
- BEGIN
- DECLARE
- CURSOR XUATKHO_CURSOR IS
- -- KHAI BÁO CON TR? QUÉT QUA S? LU?NG T?N
- SELECT KEY_QL,
- SL_TON,
- SOLOSANXUAT,
- DONGIA,
- NGAYSANXUAT,
- SONHAPKHOCHITIET,
- MAVATTU
- FROM (SELECT KEY_QL,
- MIN(a.SL_TON) AS SL_TON,
- SOLOSANXUAT,
- DONGIA,
- NGAYSANXUAT,
- A.SONHAPKHOCHITIET,
- NGAYNHAPKHO,
- a.MAVATTU
- --SL_XUAT_NV3_NGOAI,
- --A.NGAYBIENDONG
- FROM his_manager.DC_TB_XNT_BIENDONG A
- INNER JOIN his_manager.TMP_CHUYENKHO B
- ON A.NGAYBIENDONG >= B.NGAYBIENDONG
- AND A.SONHAPKHOCHITIET = B.SONHAPKHOCHITIET
- AND a.dvtt = b.DVTT
- AND a.makhovattu = b.makhovattu
- WHERE A.DVTT = P_DVTT
- AND A.MAKHOVATTU = p_MAKHOVATTU
- AND A.MAVATTU = B.MAVATTU
- --AND SONHAPKHOCHITIET IN (SELECT A.SONHAPKHOCHITIET FROM TMP_CHUYENKHO A)
- AND ID_TEMCHUYENKHO = V_XULYKHO
- AND a.hoat_dong = 1
- AND KHOA_SONHAPKHOCHITIET = 0
- GROUP BY KEY_QL,
- NGAYNHAPKHO,
- SOLOSANXUAT,
- DONGIA,
- NGAYSANXUAT,
- A.SONHAPKHOCHITIET,
- NGAYNHAPKHO,
- A.MAVATTU
- --SL_XUAT_NV3_NGOAI,
- --A.NGAYBIENDONG
- )
- WHERE SL_TON > 0
- -- FOR UPDATE OF A.SONHAPKHOCHITIET
- ORDER BY NGAYNHAPKHO, SONHAPKHOCHITIET ASC; --,NGAYBIENDONG
- BEGIN
- OPEN XUATKHO_CURSOR;
- LOOP
- FETCH XUATKHO_CURSOR
- INTO V_KEY_QL,
- V_SOLUONG_CK,
- V_SOLOSANXUAT,
- V_DONGIA,
- V_NGAYSANXUAT,
- V_SONHAPKHOCHITIET,
- p_MAVATTU; --,
- --E_SL_XUAT_NV3_NGOAI,
- --V_NGAYBIENDONG;
- EXIT WHEN XUATKHO_CURSOR%NOTFOUND;
- IF V_FINISHED = 1 THEN
- EXIT;
- END IF;
- SELECT NGAYBIENDONG
- INTO V_NGAYBIENDONG
- FROM his_manager.TMP_CHUYENKHO A
- WHERE A.DVTT = P_DVTT
- AND A.MAKHOVATTU = p_MAKHOVATTU
- AND A.MAVATTU = P_MAVATTU
- AND SONHAPKHOCHITIET = V_SONHAPKHOCHITIET
- AND ID_TEMCHUYENKHO = V_XULYKHO;
- --KIEM TRA NGAY CHUYEN
- IF p_NGHIEP_VU = 'noitru_toaquaybanthuocbv' THEN
- BEGIN
- SELECT mota_thamso
- INTO v_laydongia
- FROM HIS_FW.DM_THAMSO_DONVI
- WHERE dvtt = p_dvtt
- AND MA_THAMSO = 210;
- EXCEPTION
- WHEN NO_DATA_FOUND THEN
- v_laydongia := '0';
- END;
- BEGIN
- SELECT DonGia_BV
- INTO v_dongiaban_taiquaythuoc
- FROM his_manager.dc_tb_vattu
- WHERE DVTT = p_DVTT
- AND MaVatTu = p_MAVATTU;
- EXCEPTION
- WHEN NO_DATA_FOUND THEN
- v_finished := 1;
- END;
- IF v_laydongia = '1' THEN
- v_dongiaban_taiquaythuoc := v_dongia;
- END IF;
- ELSE
- v_dongiaban_taiquaythuoc := v_dongia;
- END IF;
- SELECT vt.malienthong_dmdc,
- KyHieuNhomBC,
- vt.HamLuong,
- vt.maduongdung_dmdc,
- vt.so_dangky_dmdc,
- nhom.maloaivattu,
- VT.NGOAIDANHMUCBHYT,
- TENVATTU,
- HOATCHAT,
- DVT
- INTO v_KEY_QLDM,
- v_KyHieuNhomBC,
- v_HAM_LUONG,
- v_DUONG_DUNG,
- v_SO_DANG_KY,
- v_MaLoaiVatTu,
- v_ngoaidanhmuc,
- P_TENVATTU,
- p_HOAT_CHAT,
- p_DVT
- FROM his_manager.dc_tb_nhomvattu nhom,
- his_manager.dc_tb_vattu vt
- WHERE nhom.DVTT = p_DVTT
- AND vt.DVTT = p_DVTT
- AND nhom.TamNgung = 0
- AND vt.MaVatTu = p_MAVATTU
- AND vt.MaNhomVatTu = nhom.MaNhomVatTu
- AND ROWNUM = 1;
- IF V_SOLUONG_CK >= V_SOLUONGCANTRU AND V_SOLUONGCANTRU > 0 THEN
- -- -------------------------------- TH?C HI?N C?U L?NH
- --------------------// ??Y V?O C?C B?NG C?N LUU TUONG ?NG V?I NGHI?P V?
- SELECT NVL(MAX(CAST(NVL(his_manager.SUBSTRING_INDEX(STT_TOATHUOC,
- '_',
- -1),
- 0) AS NUMBER(10, 0))),
- 0)
- INTO v_stt_toathuoc
- FROM his_manager.NOITRU_CT_TOA_THUOC
- WHERE DVTT = p_dvtt
- AND MA_TOA_THUOC = p_MA_TOA_THUOC
- AND stt_dieutri = p_stt_dieutri
- AND stt_benhan = p_stt_benhan
- AND stt_dotdieutri = p_stt_dotdieutri
- AND sovaovien = p_sovaovien
- AND sovaovien_dt = p_sovaovien_dt;
- INSERT INTO his_manager.NOITRU_CT_TOA_THUOC
- (STT_TOATHUOC,
- STT_order,
- DVTT,
- MA_TOA_THUOC,
- MAKHOVATTU,
- MAVATTU,
- TEN_VAT_TU,
- HOAT_CHAT,
- DVT,
- NGHIEP_VU,
- SO_LUONG,
- SO_LUONG_THUC_LINH,
- DONGIA_BAN_BV,
- DONGIA_BAN_BH,
- THANHTIEN_THUOC,
- SO_NGAY_UONG,
- SANG_UONG,
- TRUA_UONG,
- CHIEU_UONG,
- TOI_UONG,
- NGAY_RA_TOA,
- GHI_CHU_CT_TOA_THUOC,
- MA_BAC_SI_THEMTHUOC,
- CACH_SU_DUNG,
- NAM,
- stt_dieutri,
- stt_benhan,
- stt_dotdieutri,
- TU_TU_THUOC,
- NGAY,
- THANG,
- mabenhnhan,
- khoalap,
- TRANG_THAI,
- KEY_QL,
- SOLOSANXUAT,
- NGAYSANXUAT,
- MaLoaiVatTu,
- KyHieuNhomBC,
- HAM_LUONG,
- DUONG_DUNG,
- SO_DANG_KY,
- sovaovien,
- sovaovien_dt,
- SONHAPKHOCHITIET,
- ID_DIEUTRI,
- BANT,
- NGOAI_DANH_MUC,
- MAGOIDICHVU,
- ID_DINHMUC,
- SOLUONG_DINHMUC,
- SO_PHIEU_XN,
- MA_XN,
- SO_PHIEU_CDHA,
- MA_CDHA,
- SO_PHIEU_DV,
- MA_DV)
- VALUES
- (p_MA_TOA_THUOC || '_' ||
- CAST((v_stt_toathuoc + 1) AS VARCHAR2(50)),
- v_stt_order + 1,
- p_DVTT,
- p_MA_TOA_THUOC,
- p_MAKHOVATTU,
- p_MAVATTU,
- P_TENVATTU,
- p_HOAT_CHAT,
- p_DVT,
- p_NGHIEP_VU,
- v_soluongcantru, -- p_SO_LUONG ,
- v_soluongcantru, -- pp_SO_LUONG_THUC_LINH ,
- v_dongiaban_taiquaythuoc, -- p_DONGIA_BAN_BV ,
- v_dongia, -- p_DONGIA_BAN_BH ,
- v_soluongcantru * v_dongiaban_taiquaythuoc, -- p_THANHTIEN_THUOC ,
- p_SO_NGAY_UONG,
- p_SANG_UONG,
- p_TRUA_UONG,
- p_CHIEU_UONG,
- p_TOI_UONG,
- v_NGAY_RA_TOA,
- p_GHI_CHU_CT_TOA_THUOC,
- p_MA_BAC_SI_THEMTHUOC,
- p_CACH_SU_DUNG,
- p_namhientai,
- p_stt_dieutri,
- p_stt_benhan,
- p_stt_dotdieutri,
- p_TU_TU_THUOC,
- v_ngaychuyen,
- v_thanghientai,
- p_MABENHNHAN,
- p_maphongban,
- 1,
- v_KEY_QL,
- v_SOLOSANXUAT,
- v_NGAYSANXUAT,
- v_MaLoaiVatTu,
- v_KyHieuNhomBC,
- v_HAM_LUONG,
- v_DUONG_DUNG,
- v_SO_DANG_KY,
- p_sovaovien,
- p_sovaovien_dt,
- v_SONHAPKHOCHITIET,
- v_id_dieutri,
- p_bant,
- v_ngoaidanhmuc,
- p_magoidichvu,
- P_ID_DINHMUC,
- P_SOLUONG_DINHMUC,
- CASE WHEN UPPER(SUBSTR(p_sophieu, 1, 2)) = 'XN' THEN
- p_sophieu ELSE NULL END,
- CASE WHEN UPPER(SUBSTR(p_sophieu, 1, 2)) = 'XN' THEN
- p_macls ELSE NULL END,
- CASE WHEN UPPER(SUBSTR(p_sophieu, 1, 2)) = 'CD' THEN
- p_sophieu ELSE NULL END,
- CASE WHEN UPPER(SUBSTR(p_sophieu, 1, 2)) = 'CD' THEN
- p_macls ELSE NULL END,
- CASE WHEN UPPER(SUBSTR(p_sophieu, 1, 2)) = 'DV' THEN
- p_sophieu ELSE NULL END,
- CASE WHEN UPPER(SUBSTR(p_sophieu, 1, 2)) = 'DV' THEN
- p_macls ELSE NULL END);
- IF SQL%ROWCOUNT <= 0 THEN
- v_rollback := 1;
- END IF;
- -- them vao tru kho benh vien --------------------------------------------------------
- INSERT INTO his_public_list.ls_sudungthuoc
- (MA_BENH_NHAN,
- DVTT,
- MA_TOA_THUOC,
- STT_TOATHUOC,
- MAVATTU,
- TEN_VAT_TU,
- HOAT_CHAT,
- SO_LUONG,
- DONGIA,
- THANHTIEN,
- SO_NGAY_UONG,
- SANG_UONG,
- TRUA_UONG,
- CHIEU_UONG,
- TOI_UONG,
- NGAY_RA_TOA,
- NGAY_HET_THUOC,
- MA_BAC_SI_THEMTHUOC,
- GHI_CHU_CT_TOA_THUOC,
- NGHIEP_VU,
- NOITRU,
- STT_DIEUTRI,
- STT_BENHAN,
- STT_DOTDIEUTRI,
- SOVAOVIEN_NOI,
- SOVAOVIEN_DT,
- ID_DIEUTRI)
- VALUES
- (p_MABENHNHAN,
- p_DVTT,
- p_MA_TOA_THUOC,
- p_MA_TOA_THUOC || '_' ||
- CAST((v_stt_toathuoc + 1) AS VARCHAR2(50)),
- p_MAVATTU,
- P_TENVATTU,
- p_HOAT_CHAT,
- V_soluongcantru, -- p_SO_LUONG,
- v_dongiaban_taiquaythuoc, -- p_DONGIA_BAN_BV,
- V_soluongcantru * v_dongiaban_taiquaythuoc, -- V_THANHTIEN_THUOC,
- p_SO_NGAY_UONG,
- p_SANG_UONG,
- p_TRUA_UONG,
- p_CHIEU_UONG,
- p_TOI_UONG,
- v_ngaychuyen,
- v_ngaychuyen + p_SO_NGAY_UONG,
- p_MA_BAC_SI_THEMTHUOC,
- p_GHI_CHU_CT_TOA_THUOC,
- p_NGHIEP_VU,
- 1,
- p_stt_dieutri,
- p_stt_benhan,
- p_stt_dotdieutri,
- p_sovaovien,
- p_sovaovien_dt,
- v_id_dieutri);
- --------------------END // ??Y V?O C?C B?NG C?N LUU TUONG ?NG V?I NGHI?P V?
- -- X? L? NGHI?P V? KHO
- V_KT_TONTAI_TRONNGAY := 0;
- SELECT COUNT(1)
- INTO V_KT_TONTAI_TRONNGAY
- FROM his_manager.DC_TB_XNT_BIENDONG
- WHERE --KEY_QL = V_KEY_QL
- DVTT = P_DVTT
- AND MAKHOVATTU = p_MAKHOVATTU
- AND MAVATTU = P_MAVATTU
- AND SONHAPKHOCHITIET = V_SONHAPKHOCHITIET
- AND NGAYBIENDONG = TRUNC(v_ngaychuyen);
- BEGIN
- IF V_KT_TONTAI_TRONNGAY > 0 THEN
- --IF V_I = 1 THEN
- -- NEU NGAY BIEN DONG LA NGUYEN CHUYEN THI CAP NHAT SO LUONG CHUYEN DI
- UPDATE his_manager.DC_TB_XNT_BIENDONG
- SET SL_TON = SL_TON - V_SOLUONGCANTRU,
- SL_XUAT_NV3_NOI = SL_XUAT_NV3_NOI + V_SOLUONGCANTRU
- WHERE --KEY_QL = V_KEY_QL
- DVTT = P_DVTT
- AND MAKHOVATTU = p_MAKHOVATTU
- AND MAVATTU = P_MAVATTU
- AND SONHAPKHOCHITIET = V_SONHAPKHOCHITIET
- AND NGAYBIENDONG = TRUNC(v_ngaychuyen);
- IF sql%rowcount = 0 THEN
- V_ROLLBACK := 1;
- END IF;
- ELSE
- INSERT INTO his_manager.DC_TB_XNT_BIENDONG
- (KEY_QL,
- DVTT,
- MAKHOVATTU,
- MAVATTU,
- TENVATTU,
- DONGIA,
- NGAY,
- SOLOSANXUAT,
- NGAYSANXUAT,
- NGAYHETHAN,
- HOATCHAT,
- HAMLUONG,
- DVT,
- CACHSUDUNG,
- NGOAIDANHMUCBHYT,
- HOAT_DONG,
- NGAYNHAPKHO,
- SONHAPKHOCHITIET,
- NGAYBIENDONG,
- SL_NHAP,
- SL_CHUYENDEN,
- SL_CHUYENDI,
- SL_HUY,
- SL_XUAT_KO_NV3,
- SL_XUAT_NV3_NGOAI,
- SL_XUAT_NV3_NOI,
- SL_HOANTRA_DI,
- SL_HOANTRA_DEN,
- SL_HOANTRA_NCC,
- SL_HOANTRA_NV3_NGOAI,
- SL_HOANTRA_NV3_NOI,
- SL_TON,
- TT_BIENDONG,
- TT_MOINHAT,
- THANGBIENDONG_SAUSO,
- NAMBIENDONG,
- DV_INT)
- SELECT KEY_QL,
- DVTT,
- MAKHOVATTU,
- MAVATTU,
- TENVATTU,
- DONGIA,
- NGAY,
- SOLOSANXUAT,
- NGAYSANXUAT,
- NGAYHETHAN,
- HOATCHAT,
- HAMLUONG,
- DVT,
- CACHSUDUNG,
- NGOAIDANHMUCBHYT,
- HOAT_DONG,
- NGAYNHAPKHO,
- SONHAPKHOCHITIET,
- TRUNC(v_ngaychuyen),
- 0, --SL_NHAP,
- 0, --SL_CHUYENDEN,
- 0, --SL_CHUYENDI,
- 0, --SL_HUY,
- 0, --SL_XUAT_KO_NV3,
- 0, -- SL_XUAT_NV3_NGOAI
- V_SOLUONGCANTRU, --SL_XUAT_NV3_NOI,
- 0, --SL_HOANTRA_DI,
- 0, --SL_HOANTRA_DEN,
- 0, --SL_HOANTRA_NCC,
- 0, --SL_HOANTRA_NV3_NGOAI,
- 0, --SL_HOANTRA_NV3_NOI,
- SL_TON - V_SOLUONGCANTRU,
- 0, --TT_BIENDONG,
- 1,
- --EXTRACT(MONTH FROM v_ngaychuyen) || EXTRACT(YEAR FROM P_NGAYCHUYEN)
- TO_NUMBER(TO_CHAR(v_ngaychuyen, 'MMYYYY')),
- EXTRACT(YEAR FROM v_ngaychuyen),
- P_DVTT
- FROM his_manager.DC_TB_XNT_BIENDONG
- WHERE DVTT = P_DVTT
- AND MAKHOVATTU = p_MAKHOVATTU
- AND MAVATTU = P_MAVATTU
- --AND DONGIA = V_DONGIA
- AND SONHAPKHOCHITIET = V_SONHAPKHOCHITIET
- AND NGAYBIENDONG = V_NGAYBIENDONG;
- IF sql%rowcount = 0 THEN
- V_ROLLBACK := 1;
- END IF;
- END IF;
- his_manager.CHECK_DONGBO_XNT(P_DVTT,
- p_MAKHOVATTU,
- V_SONHAPKHOCHITIET,
- v_ngaychuyen,
- 'TTNOI');
- CAPNHAT_XNT_SONHAPKHO(p_DVTT,
- p_MAKHOVATTU,
- v_SONHAPKHOCHITIET);
- SELECT MAX(NGAYBIENDONG)
- INTO V_MAX_NGAYBIENDONG
- FROM his_manager.DC_TB_XNT_BIENDONG
- WHERE DVTT = P_DVTT
- AND MAKHOVATTU = p_MAKHOVATTU
- AND MAVATTU = P_MAVATTU
- --AND DONGIA = V_DONGIA
- AND SONHAPKHOCHITIET = V_SONHAPKHOCHITIET;
- UPDATE his_manager.DC_TB_XNT_BIENDONG
- SET TT_MOINHAT = 0
- WHERE DVTT = P_DVTT
- AND MAKHOVATTU = p_MAKHOVATTU
- AND MAVATTU = P_MAVATTU
- --AND DONGIA = V_DONGIA
- AND SONHAPKHOCHITIET = V_SONHAPKHOCHITIET
- --AND NGAYSANXUAT = V_NGAYSANXUAT
- AND NGAYBIENDONG < V_MAX_NGAYBIENDONG;
- /*update NOITRU_PTT_THAMCHIEUTONGTIEN
- set tongtienthamchieu = tongtienthamchieu +
- v_dongia * V_SOLUONGCANTRU
- where SOPHIEUTHANHTOAN = p_sophieuthanhtoan
- and DVTT = p_DVTT
- and sovaovien = p_sovaovien
- and sovaovien_dt = p_sovaovien_dt;*/
- END;
- V_FINISHED := 1;
- --END// X? L? NGHI?P V? KHO
- -- ----------------------------------K?T TH?C TH?C HI?N C?U L?NH
- ELSIF V_SOLUONG_CK < V_SOLUONGCANTRU AND V_SOLUONGCANTRU > 0 THEN
- -- S? LU?NG CK NH? HON S? LU?NG C?N TR?, QU?T CON TR?
- --------------------// ??Y V?O C?C B?NG C?N LUU TUONG ?NG V?I NGHI?P V?
- SELECT NVL(MAX(CAST(NVL(his_manager.SUBSTRING_INDEX(STT_TOATHUOC,
- '_',
- -1),
- 0) AS NUMBER(10, 0))),
- 0)
- INTO v_stt_toathuoc
- FROM his_manager.NOITRU_CT_TOA_THUOC
- WHERE DVTT = p_dvtt
- AND MA_TOA_THUOC = p_MA_TOA_THUOC
- AND stt_dieutri = p_stt_dieutri
- AND stt_benhan = p_stt_benhan
- AND stt_dotdieutri = p_stt_dotdieutri
- AND sovaovien = p_sovaovien
- AND sovaovien_dt = p_sovaovien_dt;
- INSERT INTO his_manager.NOITRU_CT_TOA_THUOC
- (STT_TOATHUOC,
- STT_order,
- DVTT,
- MA_TOA_THUOC,
- MAKHOVATTU,
- MAVATTU,
- TEN_VAT_TU,
- HOAT_CHAT,
- DVT,
- NGHIEP_VU,
- SO_LUONG,
- SO_LUONG_THUC_LINH,
- DONGIA_BAN_BV,
- DONGIA_BAN_BH,
- THANHTIEN_THUOC,
- SO_NGAY_UONG,
- SANG_UONG,
- TRUA_UONG,
- CHIEU_UONG,
- TOI_UONG,
- NGAY_RA_TOA,
- GHI_CHU_CT_TOA_THUOC,
- MA_BAC_SI_THEMTHUOC,
- CACH_SU_DUNG,
- NAM,
- stt_dieutri,
- stt_benhan,
- stt_dotdieutri,
- TU_TU_THUOC,
- NGAY,
- THANG,
- mabenhnhan,
- khoalap,
- TRANG_THAI,
- KEY_QL,
- SOLOSANXUAT,
- NGAYSANXUAT,
- MaLoaiVatTu,
- KyHieuNhomBC,
- HAM_LUONG,
- DUONG_DUNG,
- SO_DANG_KY,
- sovaovien,
- sovaovien_dt,
- SONHAPKHOCHITIET,
- ID_DIEUTRI,
- BANT,
- NGOAI_DANH_MUC,
- MAGOIDICHVU,
- ID_DINHMUC,
- SOLUONG_DINHMUC,
- SO_PHIEU_XN,
- MA_XN,
- SO_PHIEU_CDHA,
- MA_CDHA,
- SO_PHIEU_DV,
- MA_DV)
- VALUES
- (p_MA_TOA_THUOC || '_' ||
- CAST((v_stt_toathuoc + 1) AS VARCHAR2(50)),
- v_stt_order + 1,
- p_DVTT,
- p_MA_TOA_THUOC,
- p_MAKHOVATTU,
- p_MAVATTU,
- P_TENVATTU,
- p_HOAT_CHAT,
- p_DVT,
- p_NGHIEP_VU,
- v_soluongcantru, -- p_SO_LUONG ,
- v_soluongcantru, -- pp_SO_LUONG_THUC_LINH ,
- v_dongiaban_taiquaythuoc, -- p_DONGIA_BAN_BV ,
- v_dongia, -- p_DONGIA_BAN_BH ,
- v_soluongcantru * v_dongiaban_taiquaythuoc, -- p_THANHTIEN_THUOC ,
- p_SO_NGAY_UONG,
- p_SANG_UONG,
- p_TRUA_UONG,
- p_CHIEU_UONG,
- p_TOI_UONG,
- v_NGAY_RA_TOA,
- p_GHI_CHU_CT_TOA_THUOC,
- p_MA_BAC_SI_THEMTHUOC,
- p_CACH_SU_DUNG,
- p_namhientai,
- p_stt_dieutri,
- p_stt_benhan,
- p_stt_dotdieutri,
- p_TU_TU_THUOC,
- v_ngaychuyen,
- v_thanghientai,
- p_MABENHNHAN,
- p_maphongban,
- 1,
- v_KEY_QL,
- v_SOLOSANXUAT,
- v_NGAYSANXUAT,
- v_MaLoaiVatTu,
- v_KyHieuNhomBC,
- v_HAM_LUONG,
- v_DUONG_DUNG,
- v_SO_DANG_KY,
- p_sovaovien,
- p_sovaovien_dt,
- v_SONHAPKHOCHITIET,
- v_id_dieutri,
- p_bant,
- v_ngoaidanhmuc,
- p_magoidichvu,
- P_ID_DINHMUC,
- P_SOLUONG_DINHMUC,
- CASE WHEN UPPER(SUBSTR(p_sophieu, 1, 2)) = 'XN' THEN
- p_sophieu ELSE NULL END,
- CASE WHEN UPPER(SUBSTR(p_sophieu, 1, 2)) = 'XN' THEN
- p_macls ELSE NULL END,
- CASE WHEN UPPER(SUBSTR(p_sophieu, 1, 2)) = 'CD' THEN
- p_sophieu ELSE NULL END,
- CASE WHEN UPPER(SUBSTR(p_sophieu, 1, 2)) = 'CD' THEN
- p_macls ELSE NULL END,
- CASE WHEN UPPER(SUBSTR(p_sophieu, 1, 2)) = 'DV' THEN
- p_sophieu ELSE NULL END,
- CASE WHEN UPPER(SUBSTR(p_sophieu, 1, 2)) = 'DV' THEN
- p_macls ELSE NULL END);
- IF SQL%ROWCOUNT <= 0 THEN
- v_rollback := 1;
- END IF;
- -- them vao tru kho benh vien --------------------------------------------------------
- INSERT INTO his_public_list.ls_sudungthuoc
- (MA_BENH_NHAN,
- DVTT,
- MA_TOA_THUOC,
- STT_TOATHUOC,
- MAVATTU,
- TEN_VAT_TU,
- HOAT_CHAT,
- SO_LUONG,
- DONGIA,
- THANHTIEN,
- SO_NGAY_UONG,
- SANG_UONG,
- TRUA_UONG,
- CHIEU_UONG,
- TOI_UONG,
- NGAY_RA_TOA,
- NGAY_HET_THUOC,
- MA_BAC_SI_THEMTHUOC,
- GHI_CHU_CT_TOA_THUOC,
- NGHIEP_VU,
- NOITRU,
- STT_DIEUTRI,
- STT_BENHAN,
- STT_DOTDIEUTRI,
- SOVAOVIEN_NOI,
- SOVAOVIEN_DT,
- ID_DIEUTRI)
- VALUES
- (p_MABENHNHAN,
- p_DVTT,
- p_MA_TOA_THUOC,
- p_MA_TOA_THUOC || '_' ||
- CAST((v_stt_toathuoc + 1) AS VARCHAR2(50)),
- p_MAVATTU,
- P_TENVATTU,
- p_HOAT_CHAT,
- v_SOLUONG_CK, -- p_SO_LUONG,
- v_dongiaban_taiquaythuoc, -- p_DONGIA_BAN_BV,
- v_SOLUONG_CK * v_dongiaban_taiquaythuoc, -- V_THANHTIEN_THUOC,
- p_SO_NGAY_UONG,
- p_SANG_UONG,
- p_TRUA_UONG,
- p_CHIEU_UONG,
- p_TOI_UONG,
- v_ngaychuyen,
- v_ngaychuyen + p_SO_NGAY_UONG,
- p_MA_BAC_SI_THEMTHUOC,
- p_GHI_CHU_CT_TOA_THUOC,
- p_NGHIEP_VU,
- 1,
- p_stt_dieutri,
- p_stt_benhan,
- p_stt_dotdieutri,
- p_sovaovien,
- p_sovaovien_dt,
- v_id_dieutri);
- --------------------END // ??Y V?O C?C B?NG C?N LUU TUONG ?NG V?I NGHI?P V?
- --// X? L? NGHI?P V? KHO
- V_KT_TONTAI_TRONNGAY := 0;
- SELECT COUNT(1)
- INTO V_KT_TONTAI_TRONNGAY
- FROM his_manager.DC_TB_XNT_BIENDONG
- WHERE --KEY_QL = V_KEY_QL
- DVTT = P_DVTT
- AND MAKHOVATTU = p_MAKHOVATTU
- AND MAVATTU = P_MAVATTU
- AND SONHAPKHOCHITIET = V_SONHAPKHOCHITIET
- AND NGAYBIENDONG = TRUNC(v_ngaychuyen); --V_NGAYBIENDONG;
- IF V_KT_TONTAI_TRONNGAY > 0 THEN
- --IF V_I = 1 THEN
- -- NEU NGAY BIEN DONG LA NGUYEN CHUYEN THI CAP NHAT SO LUONG CHUYEN DI
- UPDATE his_manager.DC_TB_XNT_BIENDONG
- SET SL_TON = 0,
- SL_XUAT_NV3_NOI = SL_XUAT_NV3_NOI + v_SOLUONG_CK
- WHERE --KEY_QL = V_KEY_QL
- DVTT = P_DVTT
- AND MAKHOVATTU = p_MAKHOVATTU
- AND MAVATTU = P_MAVATTU
- AND SONHAPKHOCHITIET = V_SONHAPKHOCHITIET
- AND NGAYBIENDONG = TRUNC(v_ngaychuyen);
- IF sql%rowcount = 0 THEN
- V_ROLLBACK := 1;
- END IF;
- ELSE
- INSERT INTO his_manager.DC_TB_XNT_BIENDONG
- (KEY_QL,
- DVTT,
- MAKHOVATTU,
- MAVATTU,
- TENVATTU,
- DONGIA,
- NGAY,
- SOLOSANXUAT,
- NGAYSANXUAT,
- NGAYHETHAN,
- HOATCHAT,
- HAMLUONG,
- DVT,
- CACHSUDUNG,
- NGOAIDANHMUCBHYT,
- HOAT_DONG,
- NGAYNHAPKHO,
- SONHAPKHOCHITIET,
- NGAYBIENDONG,
- SL_NHAP,
- SL_CHUYENDEN,
- SL_CHUYENDI,
- SL_HUY,
- SL_XUAT_KO_NV3,
- SL_XUAT_NV3_NGOAI,
- SL_XUAT_NV3_NOI,
- SL_HOANTRA_DI,
- SL_HOANTRA_DEN,
- SL_HOANTRA_NCC,
- SL_HOANTRA_NV3_NGOAI,
- SL_HOANTRA_NV3_NOI,
- SL_TON,
- TT_BIENDONG,
- TT_MOINHAT,
- THANGBIENDONG_SAUSO,
- NAMBIENDONG,
- DV_INT)
- SELECT KEY_QL,
- DVTT,
- MAKHOVATTU,
- MAVATTU,
- TENVATTU,
- DONGIA,
- NGAY,
- SOLOSANXUAT,
- NGAYSANXUAT,
- NGAYHETHAN,
- HOATCHAT,
- HAMLUONG,
- DVT,
- CACHSUDUNG,
- NGOAIDANHMUCBHYT,
- HOAT_DONG,
- NGAYNHAPKHO,
- SONHAPKHOCHITIET,
- TRUNC(v_ngaychuyen),
- 0, --SL_NHAP,
- 0, --SL_CHUYENDEN,
- 0, --SL_CHUYENDI,
- 0, --SL_HUY,
- 0, --SL_XUAT_KO_NV3,
- 0, -- SL_XUAT_NV3_NGOAI
- V_SOLUONG_CK, --SL_XUAT_NV3_NOI,
- 0, --SL_HOANTRA_DI,
- 0, --SL_HOANTRA_DEN,
- 0, --SL_HOANTRA_NCC,
- 0, --SL_HOANTRA_NV3_NGOAI,
- 0, --SL_HOANTRA_NV3_NOI,
- SL_TON - V_SOLUONG_CK,
- 0, --TT_BIENDONG,
- 1,
- TO_NUMBER(EXTRACT(MONTH FROM v_ngaychuyen) ||
- EXTRACT(YEAR FROM v_ngaychuyen)),
- EXTRACT(YEAR FROM v_ngaychuyen),
- TO_NUMBER(P_DVTT)
- FROM his_manager.DC_TB_XNT_BIENDONG
- WHERE DVTT = P_DVTT
- AND MAKHOVATTU = p_MAKHOVATTU
- AND MAVATTU = P_MAVATTU
- --AND DONGIA = V_DONGIA
- AND SONHAPKHOCHITIET = V_SONHAPKHOCHITIET
- --AND NGAYSANXUAT = V_NGAYSANXUAT
- AND NGAYBIENDONG = V_NGAYBIENDONG;
- --AND TT_MOINHAT = 1;
- IF sql%rowcount = 0 THEN
- V_ROLLBACK := 1;
- END IF;
- END IF;
- his_manager.CHECK_DONGBO_XNT(P_DVTT,
- p_MAKHOVATTU,
- V_SONHAPKHOCHITIET,
- v_ngaychuyen,
- 'TTNOI');
- CAPNHAT_XNT_SONHAPKHO(p_DVTT,
- p_MAKHOVATTU,
- v_SONHAPKHOCHITIET);
- SELECT MAX(NGAYBIENDONG)
- INTO V_MAX_NGAYBIENDONG
- FROM his_manager.DC_TB_XNT_BIENDONG
- WHERE DVTT = P_DVTT
- AND MAKHOVATTU = p_MAKHOVATTU
- AND MAVATTU = P_MAVATTU
- -- AND DONGIA = V_DONGIA
- AND SONHAPKHOCHITIET = V_SONHAPKHOCHITIET;
- UPDATE his_manager.DC_TB_XNT_BIENDONG
- SET TT_MOINHAT = 0
- WHERE DVTT = P_DVTT
- AND MAKHOVATTU = P_MAKHOVATTU
- AND MAVATTU = P_MAVATTU
- -- AND DONGIA = V_DONGIA
- AND SONHAPKHOCHITIET = V_SONHAPKHOCHITIET
- --AND NGAYSANXUAT = V_NGAYSANXUAT
- AND NGAYBIENDONG < V_MAX_NGAYBIENDONG; -- 13/06/2015
- V_SOLUONGCANTRU := V_SOLUONGCANTRU - V_SOLUONG_CK;
- /*update NOITRU_PTT_THAMCHIEUTONGTIEN
- set tongtienthamchieu = tongtienthamchieu +
- v_dongia * V_SOLUONGCANTRU
- where SOPHIEUTHANHTOAN = p_sophieuthanhtoan
- and DVTT = p_DVTT
- and sovaovien = p_sovaovien
- and sovaovien_dt = p_sovaovien_dt;*/
- --END// X? L? NGHI?P V? KHO
- ELSIF V_SOLUONGCANTRU < 0 THEN
- V_FINISHED := 1;
- END IF;
- SELECT COUNT(1)
- INTO V_CHK_SL_TON
- FROM his_manager.DC_TB_XNT_BIENDONG
- WHERE DVTT = P_DVTT
- AND MAKHOVATTU = p_MAKHOVATTU
- AND MAVATTU = P_MAVATTU
- AND NGAYBIENDONG >= TRUNC(v_ngaychuyen)
- AND SONHAPKHOCHITIET = V_SONHAPKHOCHITIET
- -- AND NGAYSANXUAT = V_NGAYSANXUAT
- AND SL_TON < 0;
- SELECT COUNT(1)
- INTO V_CHK_SL_CHUYENDI
- FROM his_manager.DC_TB_XNT_BIENDONG
- WHERE DVTT = P_DVTT
- AND MAKHOVATTU = p_MAKHOVATTU
- AND MAVATTU = P_MAVATTU
- AND NGAYBIENDONG >= TRUNC(v_ngaychuyen)
- AND SONHAPKHOCHITIET = V_SONHAPKHOCHITIET
- -- AND NGAYSANXUAT = V_NGAYSANXUAT
- AND SL_CHUYENDI < 0;
- CASE
- WHEN V_CHK_SL_TON > 0 THEN
- V_ROLLBACK := 1;
- WHEN V_CHK_SL_CHUYENDI > 0 THEN
- V_ROLLBACK := 1;
- ELSE
- V_ROLLBACK := 0;
- END CASE;
- IF V_ROLLBACK = 1 THEN
- V_FINISHED := 1;
- END IF;
- END LOOP;
- CLOSE XUATKHO_CURSOR;
- END;
- END;
- ELSE
- --P_MAXHIENTAI < P_SOLUONG
- P_SAISOT := 6; -- S? LU?NG KH?NG D?
- END IF;
- ELSE
- P_SAISOT := 6;
- END IF; --kiem tra sl ton cho phep cap thuoc
- IF V_ROLLBACK = 1 THEN
- ROLLBACK;
- END IF;
- RETURN P_SAISOT;
- END IF;
- END;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement