v450.sql
来自「sql for setup the system. this is not th」· SQL 代码 · 共 975 行 · 第 1/3 页
SQL
975 行
---<NEW> MONTHLY COSTING
ALTER TABLE STK_IO_TYPE ADD MTH_FLG CHAR(1) DEFAULT 'N';
-----------------------------------------monthly costing------------------------------------
DROP TABLE STK_M_DETAIL CASCADE CONSTRAINT;
CREATE TABLE STK_M_DETAIL (
STK_BATCH_NO NUMBER(20),
PK_NO NUMBER(20),--STK_DETAIL PK_NO
ORG_NO NUMBER(8),
LOC_NO NUMBER(8),
F_YEAR NUMBER(4),
F_PERIOD NUMBER(2),
STK_TYPE CHAR(1),
ITEM_DATE DATE, -- TRANSACTION DATE (MAS_DATE)
WH_C VARCHAR2(16),
STK_C VARCHAR2(32),
MAT_C VARCHAR2(16) DEFAULT '*', -- * WHEN NOT USED, NULL VALUE NOT ALLOWED USE NVL(MAT_C,'*') TO INPUT
SIZE_C VARCHAR2(16) DEFAULT '*', -- * WHEN NOT USED, NULL VALUE NOT ALLOWED USE NVL(SIZE_C,'*') TO INPUT
COLOR VARCHAR2(16) DEFAULT '*', -- * WHEN NOT USED, NULL VALUE NOT ALLOWED USE NVL(COLOR,'*') TO INPUT
MADEIN VARCHAR2(16) DEFAULT '*', -- * WHEN NOT USED, NULL VALUE NOT ALLOWED USE NVL(MADEIN,'*') TO INPUT
VERSION VARCHAR2(16) DEFAULT '*', -- * WHEN NOT USED, NULL VALUE NOT ALLOWED USE NVL(MADEIN,'*') TO INPUT
BATCH_NO VARCHAR2(32) DEFAULT '*',
BRAND_C VARCHAR2(16) DEFAULT '*',
CAT_C VARCHAR2(16) DEFAULT '*',
CAT_C2 VARCHAR2(16) DEFAULT '*',
CAT_C3 VARCHAR2(16) DEFAULT '*',
IO_TYPE VARCHAR2(8),
IO_TYPE_FLG VARCHAR2(8),
STK_QTY NUMBER(20,6) DEFAULT 0,
MAT_VALUE NUMBER(20,6) DEFAULT 0,--MATERIAL
LB_VALUE NUMBER(20,6) DEFAULT 0,--LABOR
OH_VALUE NUMBER(20,6) DEFAULT 0,--OVERHEAD
OPT_VALUE NUMBER(20,6) DEFAULT 0,--OPERATION
OS_VALUE NUMBER(20,6) DEFAULT 0,--OUTSOURCE
EXP_VALUE NUMBER(20,6) DEFAULT 0,--EXPENSES
PRE_LB_VALUE NUMBER(20,6) DEFAULT 0,
PRE_OH_VALUE NUMBER(20,6) DEFAULT 0,
PRE_OPT_VALUE NUMBER(20,6) DEFAULT 0,
PRE_OS_VALUE NUMBER(20,6) DEFAULT 0,
PRE_EXP_VALUE NUMBER(20,6) DEFAULT 0,
DEPT_CODE VARCHAR2(16) DEFAULT '*',
WO_PK_NO NUMBER(20),
CAL_FLG CHAR(1) DEFAULT 'N',
-- N: WILL CHANGE TO Y IF CALCULATED
-- Y: CALCULATED
-- C: LB COST ETC
-- E: SINVE
-- R: FGR MANY TO ONE WILL CHANGE TO Y IF RELATED OUT ITEM CALCULATED
-- T: TRANSFER ONE TO ONE WILL CHANGE TO Y IF RELATED OUT ITEM CALCULATED
SRC_CODE VARCHAR(8),
SRC_PK_NO NUMBER(20),
SRC_ITEM_PK_NO NUMBER(20),
SRC_LOT_NO NUMBER(20),
CONSTRAINT PK_STK_M_DETAIL PRIMARY KEY (PK_NO),
CONSTRAINT FK_STK_M_DETAIL FOREIGN KEY (PK_NO) REFERENCES STK_DETAIL (PK_NO) ON DELETE CASCADE
);
CREATE INDEX I_STK_M_DETAIL1 ON STK_M_DETAIL(F_PERIOD,F_YEAR,ORG_NO,WH_C);
CREATE INDEX I_STK_M_DETAIL2 ON STK_M_DETAIL(STK_C,WH_C,F_YEAR,F_PERIOD,ORG_NO,MAT_C,SIZE_C,COLOR,MADEIN,VERSION,BATCH_NO);
DROP TABLE STK_M_DETAILR CASCADE CONSTRAINT;
CREATE TABLE STK_M_DETAILR (
PK_NO NUMBER(20),--STK_DETAIL PK_NO
REF_PK_NO NUMBER(20),--RELATED STK_DETAIL PK_NO
REF_RATIO NUMBER(20,9) DEFAULT 1,--RELATED STK_DETAIL RATIO
CONSTRAINT PK_STK_M_DETAILR PRIMARY KEY (PK_NO,REF_PK_NO),
CONSTRAINT FK_STK_M_DETAILR1 FOREIGN KEY (PK_NO) REFERENCES STK_M_DETAIL (PK_NO) ON DELETE CASCADE
);
CREATE INDEX I_STK_M_DETAILR ON STK_M_DETAILR(REF_PK_NO);
DROP TABLE STK_M_SUMP CASCADE CONSTRAINT;
CREATE TABLE STK_M_SUMP (
ORG_NO NUMBER(8),
F_YEAR NUMBER(4),
F_PERIOD NUMBER(2),
STK_TYPE CHAR(1),
READY_FLG CHAR(1) DEFAULT 'N',
WH_C VARCHAR2(16),
STK_C VARCHAR2(32),
MAT_C VARCHAR2(16) DEFAULT '*',
SIZE_C VARCHAR2(16) DEFAULT '*',
COLOR VARCHAR2(16) DEFAULT '*',
MADEIN VARCHAR2(16) DEFAULT '*',
VERSION VARCHAR2(16) DEFAULT '*',
BATCH_NO VARCHAR2(32) DEFAULT '*',
BRAND_C VARCHAR2(16) DEFAULT '*',
CAT_C VARCHAR2(16) DEFAULT '*',
CAT_C2 VARCHAR2(16) DEFAULT '*',
CAT_C3 VARCHAR2(16) DEFAULT '*',
O_STK_QTY NUMBER(20,6) DEFAULT 0,--OPEN QTY
O_MAT_VALUE NUMBER(20,6) DEFAULT 0,--MATERIAL
O_LB_VALUE NUMBER(20,6) DEFAULT 0,--LABOR
O_OH_VALUE NUMBER(20,6) DEFAULT 0,--OVERHEAD
O_OPT_VALUE NUMBER(20,6) DEFAULT 0,--OPERATION
O_OS_VALUE NUMBER(20,6) DEFAULT 0,--OUTSOURCE
O_EXP_VALUE NUMBER(20,6) DEFAULT 0,--EXPENSES
DR_QTY NUMBER(20,6) DEFAULT 0,--DR QTY
DR_MAT_VALUE NUMBER(20,6) DEFAULT 0,--MATERIAL
DR_LB_VALUE NUMBER(20,6) DEFAULT 0,--LABOR
DR_OH_VALUE NUMBER(20,6) DEFAULT 0,--OVERHEAD
DR_OPT_VALUE NUMBER(20,6) DEFAULT 0,--OPERATION
DR_OS_VALUE NUMBER(20,6) DEFAULT 0,--OUTSOURCE
DR_EXP_VALUE NUMBER(20,6) DEFAULT 0,--EXPENSES
CR_QTY NUMBER(20,6) DEFAULT 0,--CR QTY
CR_MAT_VALUE NUMBER(20,6) DEFAULT 0,--MATERIAL
CR_LB_VALUE NUMBER(20,6) DEFAULT 0,--LABOR
CR_OH_VALUE NUMBER(20,6) DEFAULT 0,--OVERHEAD
CR_OPT_VALUE NUMBER(20,6) DEFAULT 0,--OPERATION
CR_OS_VALUE NUMBER(20,6) DEFAULT 0,--OUTSOURCE
CR_EXP_VALUE NUMBER(20,6) DEFAULT 0,--EXPENSES
C_STK_QTY NUMBER(20,6) DEFAULT 0,--CLOSE QTY
C_MAT_VALUE NUMBER(20,6) DEFAULT 0,--MATERIAL
C_LB_VALUE NUMBER(20,6) DEFAULT 0,--LABOR
C_OH_VALUE NUMBER(20,6) DEFAULT 0,--OVERHEAD
C_OPT_VALUE NUMBER(20,6) DEFAULT 0,--OPERATION
C_OS_VALUE NUMBER(20,6) DEFAULT 0,--OUTSOURCE
C_EXP_VALUE NUMBER(20,6) DEFAULT 0,--EXPENSES
CONSTRAINT PK_STK_M_SUMP PRIMARY KEY (STK_C,WH_C,F_YEAR,F_PERIOD,ORG_NO,MAT_C,SIZE_C,COLOR,MADEIN,VERSION,BATCH_NO)
);
CREATE INDEX I_STK_M_SUMP1 ON STK_M_SUMP(F_PERIOD,F_YEAR,ORG_NO,WH_C);
CREATE INDEX I_STKWH_SUMP3 ON STKWH_SUMP (F_PERIOD,F_YEAR,ORG_NO);
CREATE INDEX I_STK_DETAIL7 ON STK_DETAIL (F_PERIOD,F_YEAR,ORG_NO);
alter table SPCHG_ITEM_PRICE add ( PUR_CURR_CODE VARCHAR2(8),
PUR_CURR_RATE NUMBER(20,9),
PUR_PRICE NUMBER(20,6));
ALTER TABLE STK_M_SUMP ADD (
UNIT_MAT_COST NUMBER(20,6) DEFAULT 0,--MATERIAL
UNIT_LB_COST NUMBER(20,6) DEFAULT 0,--LABOR
UNIT_OH_COST NUMBER(20,6) DEFAULT 0,--OVERHEAD
UNIT_OPT_COST NUMBER(20,6) DEFAULT 0,--OPERATION
UNIT_OS_COST NUMBER(20,6) DEFAULT 0,--OUTSOURCE
UNIT_EXP_COST NUMBER(20,6) DEFAULT 0);
--<gr> add function can import batch no
ALTER TABLE GELPKL_LOG ADD PLU_C VARCHAR2(32);
--<apchkout> add setting Consolidate payee regardless due date while transfer from AP
--<73310.Material Issue Note> ADD Batch Update BIN_C FUNCTION (SYSTEM GIVEOUT DEF VALUE BIN_C, USER CAN MODIFY)
--<70040.Warehouse Transfer> ADD Batch Update BIN_C FUNCTION (SYSTEM GIVEOUT DEF VALUE BIN_C, USER CAN MODIFY)
DELETE FROM SP_SETTING WHERE MAS_CODE = 'STKBATCH' AND ITEM_NO = 2;
-------SHINIK 2005/4/6---------------------------------------------
-- PASSWORD POLICY CONTROL
ALTER TABLE SCUSER ADD (
PWD_INIT_DATE DATE,
PWD_LOCK_FLG CHAR(1) DEFAULT 'N');
CREATE TABLE SCUSER_PWD (
USER_NO NUMBER(20),
USER_PASSWORD NUMBER(20) NOT NULL,
INIT_DATE DATE,
STOP_DATE DATE,
RESET_USER NUMBER(20),
CREATE_DATE DATE DEFAULT SYSDATE,
CONSTRAINT FK_SCUSER_PWD FOREIGN KEY (USER_NO) REFERENCES SCUSER (USER_NO) ON DELETE CASCADE
);
CREATE INDEX I_SCUSER_PWD ON SCUSER_PWD(USER_NO);
INSERT INTO GEO_EXP_ITEM (MAS_CODE,ITEM_NO,LEVEL_NO,EXP_TAB,EXP_COL,COL_TYPE,COL_LTH,EXP_TITLE,DB_ITEM)
VALUES ('STKMAS',910,1,'STK_MAS','DEF_BIN','Char',16,'Default BIN','Y');
ALTER TABLE STK_M_DETAIL ADD ORI_CAL_FLG CHAR(1) DEFAULT 'N';
ALTER TABLE STK_WH ADD MTH_COST NUMBER(20,6);
--Add setting
--Bypass 50260 Voucher post control
--In 132470,132472,132460,132462,
--30050,30060,30070,40040,40041,
--35030,35048,35060,
--45024,45030,45038,45040,
insert into SCAPP (APP_NO,APP_CODE,APP_NAME,APP_TYPE,GROUP_CODE,APP_ENABLE,MAS_CODE,VIEWER_FLG)
values (132112,'SRPPRT','Transfer to','O',132,'N','SRPPRT','Y');
ALTER TABLE PR_MAS ADD SRC_CODE VARCHAR2(8) DEFAULT 'SO';
delete from SYS_REF_TABLE where ref_code = 'DAD';
delete from SYS_REF_TABLE where ref_code = 'WHADJ';
ALTER TABLE SCUSER ADD PWD_LOCK_DATE DATE;
UPDATE sys_ref_table SET REF_STRING = '3' WHERE REF_CODE = 'GEOPWDUSAGE';
--ttn--2005/4/5
ALTER TABLE STK_ROB_DTL ADD SRC_WH_C VARCHAR2(16);
UPDATE STK_ROB_DTL SET SRC_WH_C = WH_C;
ALTER TABLE bom_mat_mas ADD CHK_FLG CHAR(1);
ALTER TABLE LOC_SP_GEN ADD REF_MAS_NO NUMBER(20);
INSERT INTO SYS_DEFINE_MAS (SYS_TYPE,MAS_CODE,TABLE_NAME) VALUES ('APPL','ASSETLOC','GASSET_MAS');
--v5
--geodec
ALTER TABLE WO_SIO_REG ADD DEPT_C VARCHAR2(16);
--TRUCK_BATCH_MAS
--NEW TABLE TRUCK_BATCH_CALENDAR
--NEW TABLE TRUCK_BATCH_FEE
ALTER TABLE TRUCK_BATCH_MAS ADD (
GEL_ORG_NO NUMBER(8),
START_KM NUMBER(20,6),
END_KM NUMBER(20,6),
START_TIME DATE,
END_TIME DATE,
TOTAL_WEIGHT NUMBER(20,6),
TOTAL_NUM NUMBER(20,6),
TRUCK_BATCH_NOTE VARCHAR2(2000)
);
CREATE TABLE TRUCK_BATCH_CALENDAR(
MAS_PK_NO NUMBER(20) NOT NULL,
PK_NO NUMBER(20),
ITEM_NO NUMBER(20,2),
START_ORG_NO NUMBER(8),
START_ACC_USER_NO NUMBER(20),
START_ACC_CODE VARCHAR2(16),
START_ACC_NAME VARCHAR2(128),
END_ORG_NO NUMBER(8),
END_ACC_USER_NO NUMBER(20),
END_ACC_CODE VARCHAR2(16),
END_ACC_NAME VARCHAR2(128),
START_TIME DATE,
END_TIME DATE,
OPERATE_TIMES NUMBER(20,6),
REMARK VARCHAR2(2000),
CONSTRAINT PK_TRUCK_BATCH_CALENDAR PRIMARY KEY (PK_NO),
CONSTRAINT FK_TRUCK_BATCH_CALENDAR FOREIGN KEY (MAS_PK_NO) REFERENCES TRUCK_BATCH_MAS (PK_NO) ON DELETE CASCADE
);
CREATE INDEX I_TRUCK_BATCH_CALENDAR1 ON TRUCK_BATCH_CALENDAR(MAS_PK_NO);
CREATE TABLE TRUCK_BATCH_FEE(
MAS_PK_NO NUMBER(20) NOT NULL,
PK_NO NUMBER(20),
ITEM_NO NUMBER(20,2),
STK_FLG CHAR(1) DEFAULT 'N',
BOM_C VARCHAR2(128),
STK_C VARCHAR2(32),
STK_NAME VARCHAR2(512) NOT NULL,
STK_MODEL VARCHAR2(128),
UOM VARCHAR2(8),
UOM_RATIO NUMBER(23,9) DEFAULT 1,
UOM_QTY NUMBER(20,6) NOT NULL,
SALES_PRICE NUMBER(20,6) DEFAULT 0 NOT NULL,
DISC_CHR VARCHAR2(32),
DISC_NUM NUMBER(20,6),
NET_PRICE NUMBER(20,6),
MAT_C VARCHAR2(16),
SIZE_C VARCHAR2(16),
COLOR VARCHAR2(16),
MADEIN VARCHAR2(16),
VERSION VARCHAR2(16),
REMARK VARCHAR2(2000),
CONSTRAINT PK_TRUCK_BATCH_FEE PRIMARY KEY (PK_NO),
CONSTRAINT FK_TRUCK_BATCH_FEE FOREIGN KEY (MAS_PK_NO) REFERENCES TRUCK_BATCH_MAS (PK_NO) ON DELETE CASCADE
);
CREATE INDEX I_TRUCK_BATCH_FEE1 ON TRUCK_BATCH_FEE(MAS_PK_NO);
ALTER TABLE ISP_MAS MODIFY MAS_MODE DEFAULT 'G';
--commision sales
CREATE TABLE COMS_MAS (
PK_NO NUMBER(20),
ORG_NO NUMBER(8),
LOC_NO NUMBER(8),
MAS_CODE VARCHAR2(8),
MAS_NO NUMBER(20) NOT NULL,
MAS_DATE DATE DEFAULT SYSDATE NOT NULL,
STATUS_FLG CHAR(1) DEFAULT 'A',
ACC_STATUS CHAR(1), --COMMISSION STATUS
SP_USER_NO NUMBER(20),
DEPT_C VARCHAR2(16),
MC_CODE VARCHAR2(16),--MARKETING COMPAING
ACC_USER_NO NUMBER(20) NOT NULL,
COM_USER_NO NUMBER(20),
ACC_CODE VARCHAR2(16),
ACC_NAME VARCHAR2(128),
ACC_CAT VARCHAR2(16),
ATTN_M VARCHAR2(64),
CC_M VARCHAR2(64),
TRADE VARCHAR2(16),
TRANSPOT VARCHAR2(16),
TYPE VARCHAR2(16),
SALES_CAT1 VARCHAR2(16),
SALES_CAT2 VARCHAR2(16),
SALES_CAT3 VARCHAR2(16),
SP_MODE CHAR(1),--T:TOP URGENT,U:URGENT,N:NORMAL
ADDRESS_NO NUMBER(20),
ALT_ADDRESS_NO NUMBER(20),
ZONE_CODE VARCHAR2(16),
ADDR_CODE VARCHAR2(16),
ADDR_NAME VARCHAR2(128),
ADDRESS1 VARCHAR2(64),
ADDRESS2 VARCHAR2(64),
ADDRESS3 VARCHAR2(64),
ADDRESS4 VARCHAR2(64),
CITY VARCHAR2(32),
STATE VARCHAR2(32),
ZIP VARCHAR2(32),
COUNTRY VARCHAR2(32),
TEL VARCHAR2(32),
FAX VARCHAR2(32),
DADDR_CODE VARCHAR2(16),
DADDR_NAME VARCHAR2(128),
DADDRESS1 VARCHAR2(64),
DADDRESS2 VARCHAR2(64),
DADDRESS3 VARCHAR2(64),
DADDRESS4 VARCHAR2(64),
DCITY VARCHAR2(32),
DSTATE VARCHAR2(32),
DZIP VARCHAR2(32),
DCOUNTRY VARCHAR2(32),
DTEL VARCHAR2(32),
DFAX VARCHAR2(32),
DAREA_C VARCHAR2(16),
PROJ_CODE VARCHAR2(16),
TAX_FLG CHAR(1) DEFAULT 'N',
TAX_CODE VARCHAR2(8),
TAX_RATE NUMBER(20,9),
⌨️ 快捷键说明
复制代码Ctrl + C
搜索代码Ctrl + F
全屏模式F11
增大字号Ctrl + =
减小字号Ctrl + -
显示快捷键?