-- 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;