?? v450.sql
字號:
-- Ver 4.5.0 sql modifications
update NBBS_LICENSE set VER = 'V4.5.0', VER_NUM = 32;
commit;
ALTER TABLE SCUSER ADD REF1 VARCHAR2(256);
declare
cursor c1 is select user_no,ref1 from account_mas where acc_index = 'C' and ref1 is not null;
begin
for c1rec in c1 loop
update scuser set ref1 = c1rec.ref1 where user_no = c1rec.user_no;
end loop;
end;
/
commit;
ALTER TABLE CAGE_DTL ADD TAX_REF VARCHAR2(16);
ALTER TABLE CAGE_DTLS ADD TAX_REF VARCHAR2(16);
CREATE OR REPLACE VIEW CAGE_DTLS_PERIOD (ORG_NO,USER_NO,ACC_USER_NO,ACC_CODE,TAX_REF,REF_ACC,
P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,
DESCRIPTION,ITEM_REF,REMARK)
AS SELECT ORG_NO,USER_NO,ACC_USER_NO,ACC_CODE,TAX_REF,REF_ACC,
DECODE(SEG,1,OPEN_AMOUNT,0),DECODE(SEG,2,OPEN_AMOUNT,0),DECODE(SEG,3,OPEN_AMOUNT,0),DECODE(SEG,4,OPEN_AMOUNT,0),
DECODE(SEG,5,OPEN_AMOUNT,0),DECODE(SEG,6,OPEN_AMOUNT,0),DECODE(SEG,7,OPEN_AMOUNT,0),DECODE(SEG,8,OPEN_AMOUNT,0),
DECODE(SEG,9,OPEN_AMOUNT,0),DECODE(SEG,10,OPEN_AMOUNT,0),DECODE(SEG,11,OPEN_AMOUNT,0),DECODE(SEG,12,OPEN_AMOUNT,0),
DECODE(SEG,1,0,2,0,3,0,4,0,5,0,6,0,7,0,8,0,9,0,10,0,11,0,12,0,OPEN_AMOUNT),
DESCRIPTION,ITEM_REF,REMARK
FROM CAGE_DTLS;
CREATE OR REPLACE VIEW CAGE_DTLS_DAY (ORG_NO,USER_NO,ACC_USER_NO,ACC_CODE,TAX_REF,REF_ACC,
P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,
DESCRIPTION,ITEM_REF,REMARK)
AS SELECT ORG_NO,USER_NO,ACC_USER_NO,ACC_CODE,TAX_REF,REF_ACC,
DECODE(SEG_1,1,OPEN_AMOUNT,0),DECODE(SEG_1,2,OPEN_AMOUNT,0),DECODE(SEG_1,3,OPEN_AMOUNT,0),DECODE(SEG_1,4,OPEN_AMOUNT,0),
DECODE(SEG_1,5,OPEN_AMOUNT,0),DECODE(SEG_1,6,OPEN_AMOUNT,0),DECODE(SEG_1,7,OPEN_AMOUNT,0),DECODE(SEG_1,8,OPEN_AMOUNT,0),
DECODE(SEG_1,9,OPEN_AMOUNT,0),DECODE(SEG_1,10,OPEN_AMOUNT,0),DECODE(SEG_1,11,OPEN_AMOUNT,0),DECODE(SEG_1,12,OPEN_AMOUNT,0),
DECODE(SEG_1,1,0,2,0,3,0,4,0,5,0,6,0,7,0,8,0,9,0,10,0,11,0,12,0,OPEN_AMOUNT),
DESCRIPTION,ITEM_REF,REMARK
FROM CAGE_DTLS;
CREATE TABLE ACC_AGE_CONT_ACC (
ORG_NO NUMBER (8),
TYPE CHAR(1),--'C' FOR CUSTOMER,'S' FOR SUPPLIER
ACC_CODE VARCHAR2(16),
CONSTRAINT PK_ACC_AGE_CONT_ACC PRIMARY KEY (ORG_NO,TYPE,ACC_CODE)
);
insert into ACC_AGE_CONT_ACC (ORG_NO,TYPE,ACC_CODE)
select org_no,'C',acc_code from account_mas
where CTL_ACC_FLG = 'Y';
update account_mas set budget_flg = 'N' where acc_index in ('D','Z');
alter table ibatch_item modify (
DR NUMBER(20,2),
CR NUMBER(20,2),
CURR_RATE NUMBER(20,9),
CURR_DR NUMBER(20,2),
CURR_CR NUMBER(20,2),
SOURCE_AMOUNT NUMBER(20,2),
CURR_SOURCE_AMOUNT NUMBER(20,2));
CREATE TABLE PLU_TYPE_MAS (
TYPE VARCHAR2(16),
REMARK VARCHAR2(2000),
-- EAN13 BAR CODE
-- ISBN
-- OTHER
CONSTRAINT PK_PLU_TYPE_MAS PRIMARY KEY (TYPE)
);
declare
cursor c1 is select distinct type from plu_mas where type is not null;
begin
delete from PLU_TYPE_MAS;
for c1rec in c1 loop
INSERT INTO PLU_TYPE_MAS (TYPE,REMARK)
VALUES (c1rec.type,c1rec.type);
end loop;
begin
INSERT INTO PLU_TYPE_MAS (TYPE,REMARK)
VALUES ('EAN13','EAN13');
exception
when others then
null;
end;
begin
INSERT INTO PLU_TYPE_MAS (TYPE,REMARK)
VALUES ('ISBN','ISBN');
exception
when others then
null;
end;
begin
INSERT INTO PLU_TYPE_MAS (TYPE,REMARK)
VALUES ('OTHER','OTHER');
exception
when others then
null;
end;
end;
/
commit;
--<NEW>
insert into SCAPP (APP_NO,APP_CODE,APP_NAME,APP_TYPE,GROUP_CODE,APP_ENABLE,MAS_CODE,VIEWER_FLG)
values (50105,'GLOPENEQ','GL Open Item Enquiry','O',50,'Y','GLOPENEQ','Y');
COMMIT;
CREATE TABLE SPCHG_ITEM_PRICE(
MAS_PK_NO NUMBER(20),
ITEM_PK_NO NUMBER(20),
PK_NO NUMBER(20),
ITEM_NO NUMBER(20,2),
CURR_CODE VARCHAR2(8) NOT NULL,
CURR_RATE NUMBER(20,9) NOT NULL,
STK_C VARCHAR2(32),
STK_NAME VARCHAR2(512) NOT NULL,
STK_MODEL VARCHAR2(128),
STK_QTY NUMBER(20,6) NOT NULL,
STK_UOM VARCHAR2(8),
SALES_PRICE NUMBER (20,6) DEFAULT 0 NOT NULL,
DISC_CHR VARCHAR2 (32),
DISC_NUM NUMBER (20,6) DEFAULT 0 NOT NULL,
NET_PRICE NUMBER(20,6) DEFAULT 0 NOT NULL,
MAT_C VARCHAR2 (16),
SIZE_C VARCHAR2 (16),
MADEIN VARCHAR2 (16),
VERSION VARCHAR2 (16),
COLOR VARCHAR2 (16),
GROUP_ID VARCHAR2 (16),
CONSTRAINT PK_SPCHG_ITEM_PRICE PRIMARY KEY (PK_NO),
CONSTRAINT FK_SPCHG_ITEM_PRICE1 FOREIGN KEY (ITEM_PK_NO) REFERENCES SPCHG_ITEM (PK_NO) ON DELETE CASCADE,
CONSTRAINT FK_SPCHG_ITEM_PRICE2 FOREIGN KEY (MAS_PK_NO) REFERENCES SPCHG_MAS (PK_NO) ON DELETE CASCADE
);
CREATE INDEX I_SPCHG_ITEM_PRICE ON SPCHG_ITEM_PRICE(ITEM_PK_NO);
---------------------------THC GEO99 3/15/2005 YD
insert into SCAPP (APP_NO,APP_CODE,APP_NAME,APP_TYPE,GROUP_CODE,APP_ENABLE,MAS_CODE,VIEWER_FLG)
values (3092,'SELSP','Select Sales Order','O',1,'N','SELSP','Y');
commit;
--<wo> add select so function
--<so create po add setting use purchase price book>
--<PR ADD TWO COLUMN SO_PK_NO,WO_PK_NO> IF GENERATED FROM W/O
ALTER TABLE PR_MAS ADD (
SO_PK_NO NUMBER(20),
WO_PK_NO NUMBER(20));
DROP TABLE SP_TOTAL_TMP;
CREATE TABLE SP_TOTAL_TMP (
MAS_PK_NO NUMBER(20),
ACC_CODE VARCHAR2(16),
TAX_CODE VARCHAR2(16),
TAX_RATE NUMBER(20,9),
STK_FLG CHAR(1),
AMOUNT NUMBER(20,6),
NET_AMT NUMBER(20,6),
TAX_AMT NUMBER(20,6),
TAX_AMT_INCL NUMBER(20,6),
CONSTRAINT FK_SP_TOTAL_TMP FOREIGN KEY (MAS_PK_NO) REFERENCES SP_MAS (PK_NO) ON DELETE CASCADE
);
CREATE INDEX I_SP_TOTAL_TMP ON SP_TOTAL_TMP(MAS_PK_NO);
ALTER TABLE SALES_BOM ADD (
CURR_CODE VARCHAR2(8),
SALE_PRICE NUMBER(20,6),
PUR_CURR_CODE VARCHAR2(8),
PUR_PRICE NUMBER(20,6));
insert into SCAPP (APP_NO,APP_CODE,APP_NAME,APP_TYPE,GROUP_CODE,APP_ENABLE,MAS_CODE,VIEWER_FLG)
values (3093,'TRNSBOM','Transfer Sales BOM','O',1,'N','TRNSBOM','Y');
alter table SALES_TMP_BOM add (
CURR_CODE VARCHAR2(8),
SALE_PRICE NUMBER(20,6),
PUR_CURR_CODE VARCHAR2(8),
PUR_PRICE NUMBER(20,6));
ALTER TABLE SP_ITEM_PRICE ADD (
PUR_CURR_CODE VARCHAR2(8),
PUR_PRICE NUMBER(20,6));
ALTER TABLE SP_ITEM_PRICE ADD PUR_CURR_RATE NUMBER(20,9);
CREATE TABLE ACC_ANA_CODE_DEF (
ITEM_NO NUMBER(2),
LOC_NO NUMBER(8),
CODE VARCHAR2(16),
DESCRIPTION VARCHAR2(256),
CONSTRAINT PK_ACC_ANA_CODE_DEF PRIMARY KEY (ITEM_NO,LOC_NO),
CONSTRAINT FK_ACC_ANA_CODE_DEF FOREIGN KEY (ITEM_NO) REFERENCES ACC_ANA_CODE (ITEM_NO) ON DELETE CASCADE
);
ALTER TABLE ACC_ANA_CODE ADD AUTO_FLG CHAR(1) DEFAULT 'Y' NOT NULL;
alter table stk_io_mas add sp_mode char(1);
CREATE INDEX I_STK_WH2 ON STK_WH(STK_C,WH_C);
----------------
ALTER TABLE BOM_MAT_ALT DROP CONSTRAINT FK_BOM_MAT_ALT1;
ALTER TABLE BOM_MAT_NOALT DROP CONSTRAINT FK_BOM_MAT_NOALT1;
ALTER TABLE BOM_MAT_POSITION DROP CONSTRAINT FK_BOM_MAT_POSITION;
ALTER TABLE BOM_MAT_MAS DROP PRIMARY KEY;
ALTER TABLE BOM_MAT_MAS DROP CONSTRAINT UNQ_BOM_MAT_MAS;
ALTER TABLE BOM_MAT_MAS ADD CONSTRAINT PK_BOM_MAT_MAS PRIMARY KEY (PK_NO);
ALTER TABLE BOM_MAT_MAS ADD CONSTRAINT UNQ_BOM_MAT_MAS UNIQUE (MAS_PK_NO,ORG_NO,MAT_STK_C,MAT_C,SIZE_C,COLOR,MADEIN,VERSION);
ALTER TABLE BOM_MAT_ALT ADD CONSTRAINT FK_BOM_MAT_ALT1 FOREIGN KEY (MAS_PK_NO) REFERENCES BOM_MAT_MAS (PK_NO) ON DELETE CASCADE;
ALTER TABLE BOM_MAT_NOALT ADD CONSTRAINT FK_BOM_MAT_NOALT1 FOREIGN KEY (MAS_PK_NO) REFERENCES BOM_MAT_MAS (PK_NO) ON DELETE CASCADE;
ALTER TABLE BOM_MAT_POSITION ADD CONSTRAINT FK_BOM_MAT_POSITION FOREIGN KEY (MAS_PK_NO) REFERENCES BOM_MAT_MAS (PK_NO) ON DELETE CASCADE;
ALTER TABLE WO_MAT_ITEM DROP CONSTRAINT UNQ_WO_MAT_ITEM;
ALTER TABLE WO_MAT_ITEM ADD CONSTRAINT UNQ_WO_MAT_ITEM UNIQUE (MAS_PK_NO,MAT_STK_C,MAT_C,SIZE_C,COLOR,MADEIN,VERSION);
ALTER TABLE LOC_SP_GEN DROP CONSTRAINT UNQ_LOC_SP_GEN;
DROP TABLE GEO_TRACE;
CREATE TABLE GEO_TRACE(
ORI_SRC_CODE VARCHAR2(8),
ORI_SRC_LOC_NO NUMBER(8),
ORI_SRC_PK_NO NUMBER(20),
SRC_TYPE CHAR(1), -- D:DOCUMENT, A:ACTION
SRC_CODE VARCHAR2(8),
SRC_LOC_NO NUMBER(8),
SRC_PK_NO NUMBER(20),
SRC_MAS_DATE DATE,
REMARK VARCHAR2(2000),
CREATE_DATE DATE DEFAULT SYSDATE
);
CREATE INDEX I_GEO_TRACE1 ON GEO_TRACE(ORI_SRC_PK_NO,SRC_CODE);
CREATE INDEX I_GEO_TRACE2 ON GEO_TRACE(SRC_PK_NO,SRC_CODE);
DROP TABLE GEO_TRACE_ITEM;
CREATE TABLE GEO_TRACE_ITEM(
ORI_SRC_CODE VARCHAR2(8),
ORI_SRC_LOC_NO NUMBER(8),
ORI_SRC_PK_NO NUMBER(20),
ORI_SRC_ITEM NUMBER(20),
SRC_CODE VARCHAR2(8),
SRC_LOC_NO NUMBER(8),
SRC_PK_NO NUMBER(20),
SRC_MAS_DATE DATE,
SRC_ITEM NUMBER(20),
SRC_QTY NUMBER(20,6),
CREATE_DATE DATE DEFAULT SYSDATE
);
CREATE INDEX I_GEO_TRACE_ITEM1 ON GEO_TRACE_ITEM(ORI_SRC_PK_NO,SRC_CODE);
CREATE INDEX I_GEO_TRACE_ITEM2 ON GEO_TRACE_ITEM(ORI_SRC_ITEM,SRC_CODE);
CREATE INDEX I_GEO_TRACE_ITEM3 ON GEO_TRACE_ITEM(SRC_PK_NO,SRC_CODE);
ALTER TABLE SP_ITEM ADD (
ORI_SRC_CODE VARCHAR2(8),
ORI_SRC_LOC_NO NUMBER(8),
ORI_SRC_PK_NO NUMBER(20),
ORI_SRC_ITEM NUMBER(20));
ALTER TABLE GEL_BATCH_DTL ADD (
ORI_SRC_CODE VARCHAR2(8),
ORI_SRC_LOC_NO NUMBER(8),
ORI_SRC_PK_NO NUMBER(20),
ORI_SRC_ITEM NUMBER(20));
insert into SCAPP (APP_NO,APP_CODE,APP_NAME,APP_TYPE,GROUP_CODE,APP_ENABLE,MAS_CODE,VIEWER_FLG)
values (90034,'GELSOENQ','Prove of Delivery(3PL)','O',90,'Y','GELSOENQ','Y');
commit;
ALTER TABLE SAGE_DTLS ADD
( DEPT_CODE VARCHAR2(16),
ANA_CODE1 VARCHAR2(16),
ANA_CODE2 VARCHAR2(16),
ANA_CODE3 VARCHAR2(16),
ANA_CODE4 VARCHAR2(16),
ANA_CODE5 VARCHAR2(16),
ANA_CODE6 VARCHAR2(16),
ANA_CODE7 VARCHAR2(16),
ANA_CODE8 VARCHAR2(16),
ANA_CODE9 VARCHAR2(16),
ANA_CODE10 VARCHAR2(16));
ALTER TABLE CAGE_DTLS ADD
( DEPT_CODE VARCHAR2(16),
ANA_CODE1 VARCHAR2(16),
ANA_CODE2 VARCHAR2(16),
ANA_CODE3 VARCHAR2(16),
ANA_CODE4 VARCHAR2(16),
ANA_CODE5 VARCHAR2(16),
ANA_CODE6 VARCHAR2(16),
ANA_CODE7 VARCHAR2(16),
ANA_CODE8 VARCHAR2(16),
ANA_CODE9 VARCHAR2(16),
ANA_CODE10 VARCHAR2(16));
insert into SCAPP (APP_NO,APP_CODE,APP_NAME,APP_TYPE,GROUP_CODE,APP_ENABLE,MAS_CODE,VIEWER_FLG)
values (73221,'WOREGBH','Work Order Reporting Batch Note','O',73,'Y','WOREGBH','Y');
--<NEW> WORK REPORTING BATCH NOTE
DROP TABLE WO_SIO_REG;
CREATE TABLE WO_SIO_REG (
MAS_PK_NO NUMBER(20),
PK_NO NUMBER(20),
WO_REG_PK_NO NUMBER(20),
OPT_PK_NO NUMBER(20),
DELETE_FLG CHAR(1),
OPT_NO NUMBER(20),
PRO_CODE VARCHAR2(16),
ACTION_DATE DATE NOT NULL,
ACT_QTY NUMBER(20,6) NOT NULL,
SCRAP_QTY NUMBER(20,6) NOT NULL,
HRS_USED NUMBER(20,6) NOT NULL,
QC_DOC_NO VARCHAR2(16),
REMARK VARCHAR2(2000),
CONSTRAINT PK_WO_SIO_REG PRIMARY KEY (PK_NO),
CONSTRAINT FK_WO_SIO_REG FOREIGN KEY (MAS_PK_NO) REFERENCES WO_SIO_MAS (PK_NO) ON DELETE CASCADE
);
CREATE INDEX I_WO_SIO_REG ON WO_SIO_REG (MAS_PK_NO);
ALTER TABLE WO_DETAIL ADD OPT_NO NUMBER(20);
CREATE OR REPLACE VIEW SRP_PROD_SCHEDULES (ORG_NO,PROD_CODE,CODE,LEAD_TIME,OPT_CODE)
AS SELECT DISTINCT ORG_NO,PROD_CODE,CODE,LEAD_TIME,OPT_CODE FROM SRP_PROD_SCHEDULE;
Update scapp set app_name = 'Proof of Delivery(3PL)' where app_no = 90034;
Commit;
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -