v450.sql

sql for setup the system. this is not that important.

 CURR_CODE 	VARCHAR2(8) NOT NULL,
 CURR_RATE	NUMBER(20,9) NOT NULL,
 DUE_DATE1  	DATE,
 TERMS		VARCHAR2(32),
 SP_NOTE	VARCHAR2(256),
 CS_REF		VARCHAR2(64),
 OUR_REF	VARCHAR2(64),
 NET_AMT	NUMBER(20,2) DEFAULT 0,
 SUPP_CODE     	VARCHAR2(16),  --SUPPLIER A/C
 COMS_IN_RATE   NUMBER(20,2) DEFAULT 0, --COMMISSION RATE
 COMS_IN	NUMBER(20,2) DEFAULT 0, 
 SUPP_CODE1    	VARCHAR2(16),  --SUPPLIER A/C
 COMS_OUT_RATE  NUMBER(20,2) DEFAULT 0, --COMMISSION RATE
 COMS_OUT	NUMBER(20,2) DEFAULT 0, 
 USER_NO	NUMBER(20),
 USER_NAME	VARCHAR2(128),
 SP_NAME	VARCHAR2(128),
 ATTACH_NO 	NUMBER(20),
 REMARK		VARCHAR2(2000),
 CREATE_DATE	DATE DEFAULT SYSDATE,
 CONSTRAINT PK_COMS_MAS PRIMARY KEY (PK_NO),
 CONSTRAINT UNQ_COMS_MAS UNIQUE (LOC_NO,MAS_CODE,MAS_NO),
 CONSTRAINT FK_COMS_MAS FOREIGN KEY (ORG_NO,DEPT_C) REFERENCES ACCOUNT_MAS (ORG_NO,ACC_CODE)
);

CREATE INDEX I_COMS_MAS1 ON COMS_MAS(LOC_NO,MAS_CODE,MAS_DATE);
CREATE INDEX I_COMS_MAS2 ON COMS_MAS(ORG_NO,ACC_CODE,MAS_CODE,STATUS_FLG);

CREATE TABLE COMS_ITEM (
 MAS_PK_NO	NUMBER(20) NOT NULL,
 ORG_NO		NUMBER(8) NOT NULL,
 LOC_NO		NUMBER(8) NOT NULL,
 MAS_CODE   	VARCHAR2(8),
 PK_NO		NUMBER(20),
 ITEM_NO	NUMBER(20,2),
 ITEM_REF	VARCHAR2(64),
 STK_FLG	CHAR(1) DEFAULT 'S',
-- S STOCK ITEM
-- C FOR SALES CHARGE ITEM
-- P FOR PURCHASE CHARGE ITEM
-- N NON STOCK ITEM WITH CODE
-- T TITLE ITEM
-- M NON STOCK ITEM
 BOM_C		VARCHAR2(128),
 STK_C		VARCHAR2(32),
 SP_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,
 STK_QTY	NUMBER(20,6) NOT NULL,
 UOM_WEIGHT	NUMBER(20,6) NOT NULL,
 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),
 COLOR		VARCHAR2(16),
 MADEIN		VARCHAR2(16),
 VERSION	VARCHAR2(16),
 ORI_PK_NO	NUMBER(20),
 REF_PK_NO1	NUMBER(20),
 REF_PK_NO2	NUMBER(20),
 STK_UOM	VARCHAR2(8),
 REMARK		VARCHAR2(256),
 CREATE_DATE	DATE DEFAULT SYSDATE,
 CONSTRAINT PK_COMS_ITEM PRIMARY KEY (PK_NO),
 CONSTRAINT FK_COMS_ITEM FOREIGN KEY (MAS_PK_NO) REFERENCES COMS_MAS (PK_NO) ON DELETE CASCADE
);

CREATE INDEX I_COMS_ITEM1 ON COMS_ITEM(ORI_PK_NO);
CREATE INDEX I_COMS_ITEM3 ON COMS_ITEM(LOC_NO,MAS_CODE,STK_C);
CREATE INDEX I_COMS_ITEM4 ON COMS_ITEM(LOC_NO,MAS_CODE,STK_NAME);
CREATE INDEX I_COMS_ITEM5 ON COMS_ITEM(MAS_PK_NO);
CREATE INDEX I_COMS_ITEM6 ON COMS_ITEM(REF_PK_NO1);

INSERT INTO SCAPP (APP_NO,APP_CODE,APP_NAME,APP_TYPE,GROUP_CODE,APP_ENABLE,MAS_CODE,VIEWER_FLG)
       VALUES (90070,'COMSSO','Commision Order','O',90,'Y','COMSSO','Y');
INSERT INTO SCAPP (APP_NO,APP_CODE,APP_NAME,APP_TYPE,GROUP_CODE,APP_ENABLE,MAS_CODE,VIEWER_FLG)
       VALUES (90071,'CMSOENQ','Outstanding Commision Order Enquiry','O',90,'Y','CMSOENQ','Y');
       
CREATE TABLE COMMISSION_STATUS
(
  ORG_NO       NUMBER(8),
  CODE         CHAR(1),
  DESCRIPTION  VARCHAR2(64)
)

INSERT INTO SCAPP (APP_NO,APP_CODE,APP_NAME,APP_TYPE,GROUP_CODE,APP_ENABLE,MAS_CODE,VIEWER_FLG)
       VALUES (1607,'TRANSCOI','Transfer from Outstanding C/O','O',1,'N','TRANSCOI','N');
ALTER TABLE COMS_MAS ADD INV_NO NUMBER(20);
ALTER TABLE COMS_MAS ADD SRC_LOC_NO NUMBER(8);
ALTER TABLE COMS_MAS ADD SRC_ORG_NO NUMBER(8);
ALTER TABLE COMS_MAS ADD SINV_NO NUMBER(20);
ALTER TABLE COMS_MAS ADD SSRC_LOC_NO NUMBER(8);
ALTER TABLE COMS_MAS ADD SSRC_ORG_NO NUMBER(8);
INSERT INTO SCAPP (APP_NO,APP_CODE,APP_NAME,APP_TYPE,GROUP_CODE,APP_ENABLE,MAS_CODE,VIEWER_FLG)
       VALUES (1608,'TRANSCO','Transfer from Outstanding C/O','O',1,'N','TRANSCO','N');
INSERT INTO SYS_DEFINE_MAS (SYS_TYPE,MAS_CODE,TABLE_NAME) VALUES ('APPL','COMSSO','COMS_MAS');

--Outsource	A	73350.Outsource Return Note Issue	CAN ALLOW USER MODIFY LIST PRICE
--Outsource	A	73360.Outsouce Return Note Receive	CAN ALLOW USER MODIFY LIST PRICE	

--bin master add log
CREATE TABLE BIN_MAS_LOG (
  ORG_NO        NUMBER(8),
  BIN_C         VARCHAR2(32),
  ACTION_TYPE   VARCHAR2(16),
  ACTION_DATE   DATE,
  USER_NO       NUMBER(20),
  STATUS        VARCHAR2(20),
  SEND_USER_NO  NUMBER(20),
  SEND_DATE     DATE,
  CHK_FLG       CHAR(1)
);

CREATE INDEX I_BIN_MAS_LOG ON BIN_MAS_LOG (BIN_C,ORG_NO,STATUS,ACTION_TYPE);

ALTER TABLE SRP_PROD_SCHEDULE ADD CHK_FLG CHAR(1);
alter table GASSET_ITEM add CAPEX_CODE		VARCHAR2(16);
alter table ASSET_MAS add CAPEX_CODE		VARCHAR2(16);
INSERT INTO SYS_DEFINE_MAS (SYS_TYPE,MAS_CODE,TABLE_NAME) VALUES ('APPL','WOREGBH','WO_SIO_MAS');
COMMIT;
ALTER TABLE WO_SIO_REG ADD  CREW_CODE     VARCHAR2(16);
ALTER TABLE WO_SIO_REG ADD  NUM_MANPOWER	NUMBER(8);
CREATE OR REPLACE VIEW CREW_OPERATOR (ORG_NO,LOC_NO,CODE,DESCRIPTION,NO_OF_OPT)
 AS SELECT A.ORG_NO,A.LOC_NO,A.CREW_CODE,A.DESCRIPTION,COUNT(*)
 FROM CREW_MAS A,CREW_ITEM B WHERE A.ORG_NO = B.ORG_NO(+) AND A.LOC_NO = B.LOC_NO(+) AND A.CREW_CODE = B.CREW_CODE(+)
 GROUP BY A.ORG_NO,A.LOC_NO,A.CREW_CODE,A.DESCRIPTION
UNION ALL
 SELECT ORG_NO,LOC_NO,OPT_CODE,DESCRIPTION,1 FROM OPERATOR_MAS;

ALTER TABLE WO_DETAIL ADD  OPT_TYPE      CHAR(1) DEFAULT 'I' NOT NULL;
ALTER TABLE WOWIP_SUMP ADD  OPT_TYPE      CHAR(1) DEFAULT 'I' NOT NULL;


-- <PM> ADD GEN_FLG, ALLOW USER NO NEED DELETE THOSE ITEM DO NOT WANT TO BUY
ALTER TABLE PR_ITEM ADD GEN_FLG CHAR(1);
UPDATE PR_ITEM SET GEN_FLG = 'Y';
COMMIT;

--<WOREGBH>
alter table WO_SIO_REG add OPT_TYPE CHAR(1) default 'I' not null;

alter table stk_mas_log add  CHK_FLG CHAR(1);

CREATE OR REPLACE FORCE VIEW SP_MAS_ITEM
(PK_NO, ORG_NO, LOC_NO, MAS_CODE, MAS_NO, 
 MAS_DATE, STATUS_FLG, ACC_USER_NO, COM_USER_NO, ACC_CODE, 
 ACC_NAME, PROJ_CODE, TAX_FLG, TAX_CODE, TAX_RATE, 
 RN_TYPE, CC_M, TRADE, TRANSPOT, TYPE, CAPEX_CODE, MAS_MODE,
 SALES_CAT1, SALES_CAT2, SALES_CAT3, DEPT_C, CURR_CODE, 
 CURR_RATE, MAS_DISC_CHR, MAS_DISC_NUM, CS_REF, OUR_REF, 
 USER_NO, SP_USER_NO, USER_NAME, SP_NAME, ZONE_CODE, 
 ADDRESS_NO, ALT_ADDRESS_NO, ROOT_NO, WORK_NO,
 ACC_CAT,MC_CODE,MAS_TERMS,
 ITEM_PK_NO,ITEM_REF_NO, ITEM_NO, ITEM_REF, STK_FLG, STK_C, 
 SP_STK_C, BOM_C, STK_NAME, STK_MODEL, UOM, 
 UOM_RATIO, UOM_QTY, STK_QTY, MAT_C, SIZE_C, 
 COLOR, MADEIN, VERSION, PALLET_NO, BATCH_NO, 
 EXP_DATE, BIN_C, POSITION, TRANS_POSITION, IO_TYPE, 
 ITEM_ACC_CODE, ITEM_TAX_CODE, ITEM_TAX_RATE, SALES_PRICE, DISC_CHR, 
 DISC_NUM, NET_PRICE, COST_PRICE, MIN_PRICE, STK_UOM, 
 QTY1, QTY2, DS_C, QC_FLG, MAS_DUE_DATE1, 
 MAS_DUE_DATE2, ITEM_DUE_DATE1, ITEM_DUE_DATE2, INCL_PRICE_FLG, WH_C, 
 UOM_WEIGHT, CASH_CARRY_FLG, COMPLETE_FLG, REF_PK_NO1, COMED_FLG, 
 DISC_TERMS, ORI_PK_NO, PUR_ACC_CODE, PUR_CURR_CODE, PUR_CURR_RATE, 
 PUR_PRICE, REMARK, VOLUMN, QC_DOC_NO, QC_PK_NO, 
 QC_QTY,ORI_SRC_CODE,ORI_SRC_LOC_NO,ORI_SRC_PK_NO,ORI_SRC_ITEM)
AS SELECT A.PK_NO,B.ORG_NO,B.LOC_NO,B.MAS_CODE,A.MAS_NO,A.MAS_DATE,A.STATUS_FLG,A.ACC_USER_NO,A.COM_USER_NO,
 A.ACC_CODE,A.ACC_NAME,A.PROJ_CODE,A.TAX_FLG,A.TAX_CODE,A.TAX_RATE,A.RN_TYPE,A.CC_M,
 A.TRADE,A.TRANSPOT,A.TYPE,A.CAPEX_CODE,A.MAS_MODE,A.SALES_CAT1,A.SALES_CAT2,A.SALES_CAT3,DEPT_C,
 A.CURR_CODE,A.CURR_RATE,A.DISC_CHR,A.DISC_NUM,A.CS_REF,A.OUR_REF,A.USER_NO,A.SP_USER_NO,A.USER_NAME,A.SP_NAME,A.ZONE_CODE,A.ADDRESS_NO,A.ALT_ADDRESS_NO,A.ROOT_NO,A.WORK_NO,
 A.ACC_CAT,A.MC_CODE,A.TERMS,
 B.PK_NO,B.ITEM_REF_NO,B.ITEM_NO,B.ITEM_REF,B.STK_FLG,B.STK_C,B.SP_STK_C,B.BOM_C,B.STK_NAME,B.STK_MODEL,B.UOM,B.UOM_RATIO,B.UOM_QTY,B.STK_QTY,
 B.MAT_C,B.SIZE_C,B.COLOR,B.MADEIN,B.VERSION,B.PALLET_NO,B.BATCH_NO,B.EXP_DATE,B.BIN_C,B.POSITION,B.TRANS_POSITION,B.IO_TYPE,
 B.ACC_CODE,B.TAX_CODE,B.TAX_RATE,
 B.SALES_PRICE,B.DISC_CHR,B.DISC_NUM,B.NET_PRICE,B.COST_PRICE,B.MIN_PRICE,B.STK_UOM,B.QTY1,B.QTY2,A.DS_C,A.QC_FLG,
 A.DUE_DATE1,A.DUE_DATE2,NVL(B.DUE_DATE1,A.DUE_DATE1),NVL(B.DUE_DATE2,A.DUE_DATE2),B.INCL_PRICE_FLG,
 B.WH_C,B.UOM_WEIGHT,B.CASH_CARRY_FLG,B.COMPLETE_FLG,B.REF_PK_NO1,B.COMED_FLG,B.DISC_TERMS,
 B.ORI_PK_NO,B.PUR_ACC_CODE,B.PUR_CURR_CODE,B.PUR_CURR_RATE,B.PUR_PRICE,B.REMARK,B.VOLUMN,B.QC_DOC_NO,B.QC_PK_NO,B.QC_QTY,
 B.ORI_SRC_CODE,B.ORI_SRC_LOC_NO,B.ORI_SRC_PK_NO,B.ORI_SRC_ITEM
 FROM SP_ITEM B,SP_MAS A WHERE A.PK_NO = B.MAS_PK_NO;

--<transfer> add checking if tax_code ot tax flag not same stop transfer

--<APPROVAL>
ALTER TABLE APPR_ITEM ADD  ( 
 REJ_USER_NAME	VARCHAR2(64),
 REF_PK_NO	NUMBER(20),
 PRIOR_FLG CHAR(1) DEFAULT 'N'
);

ALTER TABLE APPL_MAS ADD  (  
 PRIOR_FLG CHAR(1) DEFAULT 'N'
);

--<commision sales>
alter table coms_mas add Qty1 number(20,2);
alter table coms_mas add Qty2 number(20,2);
ALTER TABLE COMS_MAS ADD (
 ORI_SRC_CODE	VARCHAR2(8),
 ORI_SRC_LOC_NO	NUMBER(8),
 ORI_SRC_PK_NO	NUMBER(20));

INSERT INTO SCAPP (APP_NO,APP_CODE,APP_NAME,APP_TYPE,GROUP_CODE,APP_ENABLE,MAS_CODE,VIEWER_FLG)
       VALUES (1431,'GEOTRACE','Trace','O',1,'N','GEOTRACE','N');

--<>PO ADD APPROVAL NET_PTICE <> PURCHASE PRICE BOOK

--<>BOM MASTER ADD REPORT CONDITION FROM/TO BOM STATUS

--<>BOM ADD REF 1..4

--<>BOM ADD INSERT/UPDATE/DELETE PRIVILEGE

--V5
ALTER TABLE SCORG ADD (
 MAS_ORG_NO		NUMBER(8),
 STK_MAS		CHAR(1) DEFAULT 'N' NOT NULL,
 BOM_MAS		CHAR(1) DEFAULT 'N' NOT NULL,
 PBSALE_MAS		CHAR(1) DEFAULT 'N' NOT NULL,
 PBPUR_MAS		CHAR(1) DEFAULT 'N' NOT NULL,
 CUST_MAS		CHAR(1) DEFAULT 'N' NOT NULL,
 SUPP_MAS		CHAR(1) DEFAULT 'N' NOT NULL,
 ACC_MAS		CHAR(1) DEFAULT 'N' NOT NULL,
 USER_MAS		CHAR(1) DEFAULT 'N' NOT NULL);

ALTER TABLE SCORG DROP COLUMN PRICE_MAS;

ALTER TABLE SCORG ADD (
 PBSALE_MAS		CHAR(1) DEFAULT 'N' NOT NULL,
 PBPUR_MAS		CHAR(1) DEFAULT 'N' NOT NULL);

--<tw tax expport>
alter table TW_TAX_EXPORT add t12 		char(1);

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',718,1,'STK_MAS','SUPP_DISC_CHR1','Char',32,'Supplier 1 Discount(Char)','Y');

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',738,1,'STK_MAS','SUPP_DISC_CHR2','Char',32,'Supplier 2 Discount(Char)','Y');

--<DS> DS ADD FUNCTION CAN VIEW RELATED RESERVATION INFO

--<WH> WH MAS ADD NO OF DAYS NO RESERVE
ALTER TABLE WH_MAS ADD (
 RES_CONT_FLG	CHAR(1) DEFAULT 'N',
 RES_DEFER_DAY	NUMBER(2) DEFAULT 0);

--<PR TRACE>
ALTER TABLE PR_ITEM ADD (
 ORI_SRC_CODE	VARCHAR2(8),
 ORI_SRC_LOC_NO	NUMBER(8),
 ORI_SRC_PK_NO	NUMBER(20),
 ORI_SRC_ITEM	NUMBER(20));

CREATE OR REPLACE FORCE VIEW PR_MAS_ITEM
(PK_NO,ORG_NO,LOC_NO,MAS_CODE,MAS_NO, 
 MAS_DATE,STATUS_FLG,SP_USER_NO,DEPT_C,PROJ_CODE, 
 DUE_DATE1,DUE_DATE2,CS_REF,USER_NO,REMARK, 
 ITEM_PK_NO,SOURCE_CODE,SOURCE_ITEM_NO,COMED_FLG,ITEM_REF_NO, 
 ITEM_NO,ITEM_REF,INCL_PRICE_FLG,STK_FLG,STK_C, 
 STK_MODEL,MAT_C,SIZE_C,COLOR,MADEIN, 
 VERSION,STK_NAME,UOM,UOM_RATIO,UOM_QTY, 
 STK_QTY,SALES_PRICE,DISC_CHR,DISC_NUM,NET_PRICE, 
 QTY1,QTY2,ITEM_DUE_DATE1,ITEM_DUE_DATE2,LEAD_TIME, 
 STK_UOM,CAT_C,CAT_C2,CAT_C3,BRAND, 
 ITEM_REMARK,BO_PK_NO,WH_C,CAPEX_CODE,MAS_MODE,
 ORI_SRC_CODE,ORI_SRC_LOC_NO,ORI_SRC_PK_NO,ORI_SRC_ITEM)
AS 
SELECT A.PK_NO,A.ORG_NO,A.LOC_NO,A.MAS_CODE,A.MAS_NO,A.MAS_DATE,A.STATUS_FLG,A.SP_USER_NO,
 A.DEPT_C,A.PROJ_CODE,A.DUE_DATE1,A.DUE_DATE2,A.CS_REF,A.USER_NO,A.REMARK,
 B.PK_NO,B.SOURCE_CODE,B.SOURCE_ITEM_NO,B.COMED_FLG,
 B.ITEM_REF_NO,B.ITEM_NO,B.ITEM_REF,B.INCL_PRICE_FLG,B.STK_FLG,B.STK_C,B.STK_MODEL,B.MAT_C,B.SIZE_C,B.COLOR,B.MADEIN,B.VERSION,
 B.STK_NAME,B.UOM,B.UOM_RATIO,B.UOM_QTY,
 B.STK_QTY,B.SALES_PRICE,B.DISC_CHR,B.DISC_NUM,B.NET_PRICE,B.QTY1,B.QTY2,B.DUE_DATE1,B.DUE_DATE2,B.LEAD_TIME,
 B.STK_UOM,B.CAT_C,B.CAT_C2,B.CAT_C3,B.BRAND,B.REMARK,B.BO_PK_NO,B.WH_C,A.CAPEX_CODE,A.MAS_MODE,
 B.ORI_SRC_CODE,B.ORI_SRC_LOC_NO,B.ORI_SRC_PK_NO,B.ORI_SRC_ITEM
 FROM PR_MAS A,PR_ITEM B WHERE A.PK_NO = B.MAS_PK_NO;

ALTER TABLE PR_ITEM MODIFY  GEN_FLG 	CHAR(1) DEFAULT 'Y';

--<70065/70070/70080> ADD EXPOPRT FUNCTION
@@SAMPLEIR.EXP
@@SAMPLEI.EXP
@@SAMPLER.EXP

--<rfq> add can be transfer from rfq

alter table GASSET_ITEM_LOT add (
 PIC_CODE         	VARCHAR2(16),
 ASSET_LOC        	VARCHAR2(16),
 ASSET_LOCS	 	VARCHAR2(128));


--<WH REFRESH> ADD FLAG  RELEASE_FLG	CHAR(1) DEFAULT 'N'
-- DO NOT RELEASE RESERVED ITEM WHILE REFRESHING
ALTER TABLE WH_MAS ADD  RELEASE_FLG	CHAR(1) DEFAULT 'N';

--<s/o> add err_code in sp_mas
alter table sp_mas add  ERR_CODE	VARCHAR2(16);

--<prodmas>
alter table bom_stk_mas
add(
reference1 varchar2(256),
reference2 varchar2(256),
reference3 varchar2(256),
reference4 varchar2(256)
);

COMMIT;
@@I1_PLSQL.sql

@@NBBSTERM.TXT

@@GEOMSG.LST

COMMIT;