EOFFICE - GD4

--LẤY DỮ LIỆU SỐ LƯỢNG DÒNG CÁC BẢNG PHÂN HỆ KHO CHỨNG TỪ

SELECT COUNT(*),'1', 'CM_DOCUMENT' FROM CM_DOCUMENT

SELECT COUNT(*),'2', 'CM_WAREHOUSE' FROM CM_WAREHOUSE											

SELECT COUNT(*),'3', 'CM_WAREHOUSE_DT' FROM CM_WAREHOUSE_DT

SELECT COUNT(*),'4', 'BOX_DOCUMENT' FROM BOX_DOCUMENT

SELECT COUNT(*),'5', 'BOX_DOCUMENT_DT' FROM BOX_DOCUMENT_DT

SELECT COUNT(*),'6', 'BOX_DOCUMENT_HIST' FROM BOX_DOCUMENT_HIST

SELECT COUNT(*),'7', 'REQ_BOR_DOCUMENT' FROM REQ_BOR_DOCUMENT

SELECT COUNT(*),'8', 'REQ_BOR_DOCUMENT_DT' FROM REQ_BOR_DOCUMENT_DT

SELECT COUNT(*),'9', 'REQ_OUT_DOCUMENT' FROM REQ_OUT_DOCUMENT

SELECT COUNT(*),'10', 'REQ_OUT_DOCUMENT_DT' FROM REQ_OUT_DOCUMENT_DT

SELECT COUNT(*),'11', 'REQ_COL_DOCUMENT' FROM REQ_COL_DOCUMENT

SELECT COUNT(*),'12', 'REQ_COL_DOCUMENT_DT' FROM REQ_COL_DOCUMENT_DT

SELECT COUNT(*),'13', 'REQ_IN_DOCUMENT_GATHER' FROM REQ_IN_DOCUMENT_GATHER

SELECT COUNT(*),'14', 'REQ_IN_DOCUMENT_GATHER_DT' FROM REQ_IN_DOCUMENT_GATHER_DT

SELECT COUNT(*),'15', 'REQ_BOR_DOCUMENT_GATHER' FROM REQ_BOR_DOCUMENT_GATHER

SELECT COUNT(*),'16', 'REQ_BOR_DOCUMENT_GATHER_ID' FROM REQ_BOR_DOCUMENT_GATHER_DT

SELECT COUNT(*),'17', 'REQ_OUT_DOCUMENT_GATHER' FROM REQ_OUT_DOCUMENT_GATHER

SELECT COUNT(*),'18', 'REQ_OUT_DOCUMENT_GATHER_DT' FROM REQ_OUT_DOCUMENT_GATHER_DT

SELECT COUNT(*),'19', 'REQ_COL_DOCUMENT_GATHER' FROM REQ_COL_DOCUMENT_GATHER

SELECT COUNT(*),'20', 'REQ_COL_DOCUMENT_GATHER_DT' FROM REQ_COL_DOCUMENT_GATHER_DT

SELECT COUNT(*),'21', 'REQ_DES_DOCUMENT_GATHER' FROM REQ_DES_DOCUMENT_GATHER

SELECT COUNT(*),'22', 'REQ_DES_DOCUMENT_GATHER_DT' FROM REQ_DES_DOCUMENT_GATHER_DT

SELECT COUNT(*),'23', 'REQ_PROCESS_GATHER' FROM REQ_PROCESS_GATHER

--XÓA DỮ LIỆU PHÂN HỆ KHO CHỨNG TỪ

DELETE FROM CM_DOCUMENT

DELETE FROM CM_WAREHOUSE

DELETE FROM CM_WAREHOUSE_DT

DELETE FROM BOX_DOCUMENT

DELETE FROM BOX_DOCUMENT_DT

DELETE FROM BOX_DOCUMENT_HIST

DELETE FROM REQ_BOR_DOCUMENT

DELETE FROM REQ_BOR_DOCUMENT_DT

DELETE FROM REQ_OUT_DOCUMENT

DELETE FROM REQ_OUT_DOCUMENT_DT

DELETE FROM REQ_COL_DOCUMENT

DELETE FROM REQ_COL_DOCUMENT_DT

DELETE FROM REQ_IN_DOCUMENT_GATHER

DELETE FROM REQ_IN_DOCUMENT_GATHER_DT

DELETE FROM REQ_BOR_DOCUMENT_GATHER

DELETE FROM REQ_BOR_DOCUMENT_GATHER_DT

DELETE FROM REQ_OUT_DOCUMENT_GATHER

DELETE FROM REQ_OUT_DOCUMENT_GATHER_DT

DELETE FROM REQ_COL_DOCUMENT_GATHER

DELETE FROM REQ_COL_DOCUMENT_GATHER_DT

DELETE FROM REQ_DES_DOCUMENT_GATHER

DELETE FROM REQ_DES_DOCUMENT_GATHER_DT

DELETE FROM REQ_PROCESS_GATHER

--LẤY DỮ LIỆU PREFIX PHÂN HỆ KHO CHỨNG TỪ

SELECT * FROM SYS_PREFIX WHERE ID IN ('CM_DOCUMENT'

,'CM_WAREHOUSE'

,'CM_WAREHOUSE_DT'

,'BOX_DOCUMENT'

,'BOX_DOCUMENT_DT'

,'BOX_DOCUMENT_HIST'

,'REQ_BOR_DOCUMENT'

,'REQ_BOR_DOCUMENT_DT'

,'REQ_OUT_DOCUMENT'

,'REQ_OUT_DOCUMENT_DT'

,'REQ_COL_DOCUMENT'

,'REQ_COL_DOCUMENT_DT'

,'REQ_IN_DOCUMENT_GATHER'

,'REQ_IN_DOCUMENT_GATHER_DT'

,'REQ_BOR_DOCUMENT_GATHER'

,'REQ_BOR_DOCUMENT_GATHER_DT'

,'REQ_OUT_DOCUMENT_GATHER'

,'REQ_OUT_DOCUMENT_GATHER_DT'

,'REQ_COL_DOCUMENT_GATHER'

,'REQ_COL_DOCUMENT_GATHER_DT'

,'REQ_DES_DOCUMENT_GATHER'

,'REQ_DES_DOCUMENT_GATHER_DT')

--LẤY DỮ LIỆU CODEMASTERS PHÂN HỆ KHO CHỨNG TỪ

SELECT * FROM SYS_CODEMASTERS WHERE PREFIX IN (SELECT PREFIX FROM SYS_PREFIX WHERE ID IN ('CM_DOCUMENT'

,'CM_WAREHOUSE'

,'CM_WAREHOUSE_DT'

,'BOX_DOCUMENT'

,'BOX_DOCUMENT_DT'

,'BOX_DOCUMENT_HIST'

,'REQ_BOR_DOCUMENT'

,'REQ_BOR_DOCUMENT_DT'

,'REQ_OUT_DOCUMENT'

,'REQ_OUT_DOCUMENT_DT'

,'REQ_COL_DOCUMENT'

,'REQ_COL_DOCUMENT_DT'

,'REQ_IN_DOCUMENT_GATHER'

,'REQ_IN_DOCUMENT_GATHER_DT'

,'REQ_BOR_DOCUMENT_GATHER'

,'REQ_BOR_DOCUMENT_GATHER_DT'

,'REQ_OUT_DOCUMENT_GATHER'

,'REQ_OUT_DOCUMENT_GATHER_DT'

,'REQ_COL_DOCUMENT_GATHER'

,'REQ_COL_DOCUMENT_GATHER_DT'

,'REQ_DES_DOCUMENT_GATHER'

,'REQ_DES_DOCUMENT_GATHER_DT'))

--XÓA DỮ LIỆU CODEMASTERS PHÂN HỆ KHO CHỨNG TỪ

DELETE FROM SYS_CODEMASTERS WHERE PREFIX IN (SELECT PREFIX FROM SYS_PREFIX WHERE ID IN ('CM_DOCUMENT'

,'CM_WAREHOUSE'

,'CM_WAREHOUSE_DT'

,'BOX_DOCUMENT'

,'BOX_DOCUMENT_DT'

,'BOX_DOCUMENT_HIST'

,'REQ_BOR_DOCUMENT'

,'REQ_BOR_DOCUMENT_DT'

,'REQ_OUT_DOCUMENT'

,'REQ_OUT_DOCUMENT_DT'

,'REQ_COL_DOCUMENT'

,'REQ_COL_DOCUMENT_DT'

,'REQ_IN_DOCUMENT_GATHER'

,'REQ_IN_DOCUMENT_GATHER_DT'

,'REQ_BOR_DOCUMENT_GATHER'

,'REQ_BOR_DOCUMENT_GATHER_DT'

,'REQ_OUT_DOCUMENT_GATHER'

,'REQ_OUT_DOCUMENT_GATHER_DT'

,'REQ_COL_DOCUMENT_GATHER'

,'REQ_COL_DOCUMENT_GATHER_DT'

,'REQ_DES_DOCUMENT_GATHER'

,'REQ_DES_DOCUMENT_GATHER_DT'))

DBCC CHECKIDENT(REQ_PROCESS_GATHER, RESEED, 0)--BẢNG NÀY INDENTITY NÊN KHÔNG CÓ TRONG SYS_CODEMASTERS

--XỬ LÝ REVERT CÁC MÃ PHIẾU PHÂN HỆ KHO CHỨNG TỪ

SELECT * FROM SYS_CODEMASTERS WHERE Prefix LIKE '2025%' -- LẤY THÔNG TIN MÃ PHIẾU GEN TỰ ĐỘNG CỦA CHỨC NĂNG (NHẬP CHỨNG TỪ)

-- LẤY THÔNG TIN MÃ PHIẾU GEN TỰ ĐỘNG CỦA CHỨC NĂNG (XUẤT MƯỢN/TRÍCH LỤC CHỨNG TỪ)(XUẤT KHO CHỨNG TỪ)(HOÀN NHẬP CHỨNG TỪ)

*CHỨNG TỪ TẬP TRUNG *(PYC NHẬP KHO CHỨNG TỪ)(XUẤT MƯỢN/TRÍCH LỤC CHỨNG TỪ)(XUẤT KHO CHỨNG TỪ)(HOÀN NHẬP CHỨNG TỪ)(PYC HỦY THÙNG CHỨNG TỪ)

SELECT * FROM BOR_CODEMASTERS 

SELECT * FROM SYS_CODEMASTERS WHERE Prefix LIKE 'whcode%' -- LẤY THÔNG TIN MÃ PHIẾU GEN TỰ ĐỘNG CỦA CHỨC NĂNG (DANH MỤC KHO)

SELECT * FROM SYS_CODEMASTERS WHERE Prefix LIKE 'CT.%' -- LẤY THÔNG TIN MÃ PHIẾU GEN TỰ ĐỘNG CỦA CHỨC NĂNG (DANH MỤC LOẠI CHỨNG TỪ)

--LẤY LỊCH SỬ XỬ LÝ PHIẾU PHÂN HỆ KHO CHỨNG TỪ

SELECT * FROM PL_PROCESS WHERE REQ_ID LIKE 'PNT%'

SELECT * FROM PL_PROCESS WHERE REQ_ID LIKE 'BDDT%'

SELECT * FROM PL_PROCESS WHERE REQ_ID LIKE 'LST%'

SELECT * FROM PL_PROCESS WHERE REQ_ID LIKE 'CDOC%'

SELECT * FROM PL_PROCESS WHERE REQ_ID LIKE 'K%'

SELECT * FROM PL_PROCESS WHERE REQ_ID LIKE 'CWDT%'

SELECT * FROM PL_PROCESS WHERE REQ_ID LIKE 'PXMTL%'

SELECT * FROM PL_PROCESS WHERE REQ_ID LIKE 'PXMCT%'

SELECT * FROM PL_PROCESS WHERE REQ_ID LIKE 'PXMKTT%'

SELECT * FROM PL_PROCESS WHERE REQ_ID LIKE 'PXMKTTCT%'

SELECT * FROM PL_PROCESS WHERE REQ_ID LIKE 'PHN%'

SELECT * FROM PL_PROCESS WHERE REQ_ID LIKE 'PHNCT%'

SELECT * FROM PL_PROCESS WHERE REQ_ID LIKE 'PHNKTT%'

SELECT * FROM PL_PROCESS WHERE REQ_ID LIKE 'PHNKTTCT%'

SELECT * FROM PL_PROCESS WHERE REQ_ID LIKE 'PHTKTT%'

SELECT * FROM PL_PROCESS WHERE REQ_ID LIKE 'PHTKTTCT%'

SELECT * FROM PL_PROCESS WHERE REQ_ID LIKE 'PNKTT%'

SELECT * FROM PL_PROCESS WHERE REQ_ID LIKE 'PNKTTCT%'

SELECT * FROM PL_PROCESS WHERE REQ_ID LIKE 'PXK%'

SELECT * FROM PL_PROCESS WHERE REQ_ID LIKE 'PXKCT%'

SELECT * FROM PL_PROCESS WHERE REQ_ID LIKE 'PXKKTT%'

SELECT * FROM PL_PROCESS WHERE REQ_ID LIKE 'PXKKTTCT%'

--XÓA LỊCH SỬ XỬ LÝ PHIẾU PHÂN HỆ KHO CHỨNG TỪ

DELETE FROM PL_PROCESS WHERE REQ_ID LIKE 'PNT%'

DELETE FROM PL_PROCESS WHERE REQ_ID LIKE 'BDDT%'

DELETE FROM PL_PROCESS WHERE REQ_ID LIKE 'LST%'

DELETE FROM PL_PROCESS WHERE REQ_ID LIKE 'CDOC%'

DELETE FROM PL_PROCESS WHERE REQ_ID LIKE 'K%'

DELETE FROM PL_PROCESS WHERE REQ_ID LIKE 'CWDT%'

DELETE FROM PL_PROCESS WHERE REQ_ID LIKE 'PXMTL%'

DELETE FROM PL_PROCESS WHERE REQ_ID LIKE 'PXMCT%'

DELETE FROM PL_PROCESS WHERE REQ_ID LIKE 'PXMKTT%'

DELETE FROM PL_PROCESS WHERE REQ_ID LIKE 'PXMKTTCT%'

DELETE FROM PL_PROCESS WHERE REQ_ID LIKE 'PHN%'

DELETE FROM PL_PROCESS WHERE REQ_ID LIKE 'PHNCT%'

DELETE FROM PL_PROCESS WHERE REQ_ID LIKE 'PHNKTT%'

DELETE FROM PL_PROCESS WHERE REQ_ID LIKE 'PHNKTTCT%'

DELETE FROM PL_PROCESS WHERE REQ_ID LIKE 'PHTKTT%'

DELETE FROM PL_PROCESS WHERE REQ_ID LIKE 'PHTKTTCT%'

DELETE FROM PL_PROCESS WHERE REQ_ID LIKE 'PNKTT%'

DELETE FROM PL_PROCESS WHERE REQ_ID LIKE 'PNKTTCT%'

DELETE FROM PL_PROCESS WHERE REQ_ID LIKE 'PXK%'

DELETE FROM PL_PROCESS WHERE REQ_ID LIKE 'PXKCT%'

DELETE FROM PL_PROCESS WHERE REQ_ID LIKE 'PXKKTT%'

DELETE FROM PL_PROCESS WHERE REQ_ID LIKE 'PXKKTTCT%'