2020-02-26

alter table  WAREHOUSE  add (CREATE_PEOPLE  VARCHAR2(255));
alter table  WAREHOUSE  add (CREATE_DATE  date);
2020-02-27

alter table  PRODUCT_RECORD  add (PRODUCT_VERSION  VARCHAR2(255));
alter table  PRODUCT_RECORD  add (PRODUCT_TYPE  VARCHAR2(255));
alter table  PRODUCT_RECORD  add (CREATE_PEOPLE  VARCHAR2(255));
alter table  PRODUCT_RECORD  add (CREATE_DATE  date);
alter table  PRODUCT_RECORD  add (CURVERSION  number(19));
alter table  PRODUCT_RECORD  add (UNIT  VARCHAR2(255));
alter table  PRODUCT_RECORD  add (UNIT_TYPE  VARCHAR2(255));
alter table  PRODUCT_RECORD  add (SPEC_NO  VARCHAR2(255));
alter table  PRODUCT_RECORD  add (PALLET_QTY  number(19));
alter table  PRODUCT_RECORD  add (PACKOIPATH  VARCHAR2(255));
alter table  PRODUCT_RECORD  add (BOX_QTY  number(19));
alter table  PRODUCT_RECORD  add (SHORT_NAME  VARCHAR2(255));
alter table  PRODUCT_RECORD  add (SERIALTYPE_LOT  VARCHAR2(255));
alter table  PRODUCT_RECORD  add (SERIALTYPE_CODE  VARCHAR2(255));
alter table  PRODUCT_RECORD  add (PICTURE_NAME  VARCHAR2(255));
alter table  PRODUCT_RECORD  add (LOTSTD_QTY  number(19));
alter table  PRODUCT_RECORD  add (ERP_NO  VARCHAR2(255));
alter table  PRODUCT_RECORD  add (GRAPH_NO  VARCHAR2(255));
alter table  PRODUCT_RECORD  add (QC_CATEGORY  VARCHAR2(255));

2020-02-28
alter table  WORKSTATION  add (EQUIPMENT_TYPE  VARCHAR2(255)) ;
alter table  WORKSTATION  add (CAPACITY  number(19)) ;
alter table  WORKSTATION  add (VENDOR_NO  VARCHAR2(255)) ;
alter table  WORKSTATION  add (MODEL_NO  VARCHAR2(255)) ;
alter table  WORKSTATION  add (CREATE_PEOPLE  VARCHAR2(255)) ;
alter table  WORKSTATION  add (CREATE_DATE  date) ;
alter table  WORKSTATION  add (ISSUESTATE  VARCHAR2(255)) ;
alter table  WORKSTATION  add (ENGINEERGROUP_NO  VARCHAR2(255)) ;
alter table  WORKSTATION  add (ASSET_NO  VARCHAR2(255)) ;
alter table  WORKSTATION  add (EQUIPMENT_CLASS  VARCHAR2(255)) ;
alter table  WORKSTATION  add (LOAD_PORT  number(1)) ;
alter table  WORKSTATION  add (AUTO_FLAG  number(1)) ;
alter table  WORKSTATION  add (EACONTROLLER  VARCHAR2(255)) ;
alter table  WORKSTATION  add (EQPRECIPE  number(19)) ;
alter table  WORKSTATION  add (QCLIST_NO  VARCHAR2(255)) ;
alter table  WORKSTATION  add (MAX_TIME  number(19)) ;
alter table  WORKSTATION  add (FIXEQP_TIME  number(19)) ;
alter table  WORKSTATION  add (VAREQP_TIME  number(19)) ;
alter table  WORKSTATION  add (COUNTEQPUNIT_QTY  number(19)) ;
alter table  WORKSTATION  add (COUNTER  number(19)) ;
alter table  WORKSTATION  add (ERP_NO  VARCHAR2(255)) ;
alter table  WORKSTATION  add (PRODUCTIONINF  number(19)) ;
alter table  WORKSTATION  add (ALLOWMULTIWORK  number(1)) ;

alter table  ITEM  add (CREATE_PEOPLE  VARCHAR2(255)) ;
alter table  ITEM  add (CREATE_DATE  date) ;
alter table  ITEM  add (ISSUE_STATE  VARCHAR2(255)) ;

/**2020-03-05*/
alter table  LOGISTICS_GATE  add (LS1_ID  NUMBER(19)) ;
alter table  LOGISTICS_GATE  add (LS2_ID  NUMBER(19)) ;
alter table  ASN_DETAIL  add (PROP_N4  FLOAT) ;

alter table LOGISTICS_GATE
add constraint FK202003051120 foreign key (LS1_ID)
references LOCATION_STORAGE (ID);	

alter table LOGISTICS_GATE
add constraint FK202003051121 foreign key (LS2_ID)
references LOCATION_STORAGE (ID);

/**20200313*/
alter table  ASN_DETAIL  add (CREATE_PEOPLE  VARCHAR2(50))  ;
alter table  ASN_DETAIL  add (CREATE_DATE  date)  ;

/**2020-03-21*/
alter table  RECEIVED_RECORD  add (ftl_qty  float) ;
alter table  RECEIVED_RECORD  add (tlf_qty  float) ;
alter table  RECEIVED_RECORD  add (bf_qty  float) ;

/**2020-03-27*/
alter table  RECEIVED_RECORD  add (FINISH_DATE  DATE);
alter table  RECEIVED_RECORD  add (TARGET_AREA  VARCHAR2(255));

/**2020-03-30*/
alter table  ASN_DETAIL  add (WAREHOUSE_AREA_ID  number(19)) ;

alter table ASN_DETAIL
add constraint FK202003301418 foreign key (WAREHOUSE_AREA_ID)
references ZONE (ID);

alter table  ASN_DETAIL  add (LOCATION_STORAGE_ID  number(19));

alter table ASN_DETAIL
add constraint FK202003301419 foreign key (LOCATION_STORAGE_ID)
references LOCATION_STORAGE (ID);

--侯0402
--删除多余的单据类型 
 delete bill_type where id in (1023,1022,1021,1005,1001,1002);
  --重新建立约束
  alter table inventory drop constraint FM201912271731; 
  alter table inventory add constraint FM201912271731 unique (LOCATION_ID, SOI, ITEM_KEY_ID);
  --删除重复，重新建立约束
  delete asn_detail d where id in (
        select max(id) from asn_detail where po is not null group by po having count(po)>1
    )
  alter table ASN_DETAIL add constraint UK_AD_PO unique (PO);
  
  --侯0403 更改数量精度，保留4位小数
  update INVENTORY set CA_COEFFICIENT=ROUND(QUANTITY, 4);
update INVENTORY set QUANTITY=null,QUEUED_QUANTITY=null,MASTER_PACK_QUANTITY=null;
alter table INVENTORY modify (QUANTITY number(10,4),QUEUED_QUANTITY number(10,4),MASTER_PACK_QUANTITY number(10,4));
update INVENTORY set QUANTITY=CA_COEFFICIENT,QUEUED_QUANTITY=0,MASTER_PACK_QUANTITY=CA_COEFFICIENT;
update INVENTORY set CA_COEFFICIENT=0;
update RECEIVED_RECORD rd set rd.WEIGHT=rd.RECEIVED_QUANTITY;
update RECEIVED_RECORD rd set rd.RECEIVED_QUANTITY=null,rd.RECEIVED_QUANTITY_MU=null,rd.BF_QTY=null,rd.FTL_QTY=null,rd.TLF_QTY=null;
alter table RECEIVED_RECORD modify (RECEIVED_QUANTITY number(10,4),RECEIVED_QUANTITY_MU number(10,4),BF_QTY number(10,4),FTL_QTY number(10,4),TLF_QTY number(10,4));
update RECEIVED_RECORD rd set rd.RECEIVED_QUANTITY=WEIGHT,rd.RECEIVED_QUANTITY_MU=WEIGHT,rd.BF_QTY=0,rd.FTL_QTY=0,rd.TLF_QTY=0;
update RECEIVED_RECORD rd set WEIGHT=0;
update PICK_TICKET pt set pt.WEIGHT=pt.ORDER_QUANTITY;
update PICK_TICKET pt set pt.ORDER_QUANTITY=null,pt.PRE_ALLOCATED_QTY=null,pt.ALLOCATED_QUANTITY=null,pt.PICKED_QUANTITY=null,pt.SHIPPED_QUANTITY=null,pt.BOL_QUANTITY=null;
alter table PICK_TICKET modify (ORDER_QUANTITY number(10,4),PRE_ALLOCATED_QTY number(10,4),ALLOCATED_QUANTITY number(10,4),PICKED_QUANTITY number(10,4),SHIPPED_QUANTITY number(10,4),BOL_QUANTITY number(10,4));
update PICK_TICKET pt set pt.ORDER_QUANTITY=WEIGHT,pt.PRE_ALLOCATED_QTY=0,pt.ALLOCATED_QUANTITY=0,pt.PICKED_QUANTITY=0,pt.SHIPPED_QUANTITY=0,pt.BOL_QUANTITY=0;
update PICK_TICKET pt set WEIGHT=0;
update PICK_TICKET_DETAIL ptd set ptd.WEIGHT=ptd.ORDER_QUANTITY;
update PICK_TICKET_DETAIL ptd set ptd.ORDER_QUANTITY=null,ptd.ORDER_QUANTITY_MU=null,ptd.PRE_ALLOCATED_QTY=null,ptd.ALLOCATED_QUANTITY=null,ptd.PICKED_QUANTITY=null,ptd.SHIPPED_QUANTITY=null,ptd.BOL_QUANTITY=null;
alter table PICK_TICKET_DETAIL modify (ORDER_QUANTITY number(10,4),ORDER_QUANTITY_MU number(10,4),PRE_ALLOCATED_QTY number(10,4),ALLOCATED_QUANTITY number(10,4),PICKED_QUANTITY number(10,4),SHIPPED_QUANTITY number(10,4),BOL_QUANTITY number(10,4));
update PICK_TICKET_DETAIL ptd set ptd.ORDER_QUANTITY=WEIGHT,ptd.ORDER_QUANTITY_MU=weight,ptd.PRE_ALLOCATED_QTY=0,ptd.ALLOCATED_QUANTITY=0,ptd.PICKED_QUANTITY=0,ptd.SHIPPED_QUANTITY=0,ptd.BOL_QUANTITY=0;
update PICK_TICKET_DETAIL ptd set WEIGHT=0;
update TASK t set t.MOVED_QUANTITY_MU=null,t.PLAN_QUANTITY=null,t.PLAN_QUANTITY_MU=null;
alter table TASK modify (MOVED_QUANTITY_MU number(10,4),PLAN_QUANTITY number(10,4),PLAN_QUANTITY_MU number(10,4));
update TASK t set t.MOVED_QUANTITY_MU=0,t.PLAN_QUANTITY=0,t.PLAN_QUANTITY_MU=0;
update TASK_LOG log set log.MOVED_QUANTITY=null,log.SHIP_QUANTITY=null,log.BOL_QUANTITY=null;
alter table TASK_LOG modify (MOVED_QUANTITY number(10,4),SHIP_QUANTITY number(10,4),BOL_QUANTITY number(10,4));
update TASK_LOG t set t.MOVED_QUANTITY=0,t.SHIP_QUANTITY=0,t.BOL_QUANTITY=0;

alter table task drop constraint SYS_C0011572; 
alter table task drop INDEX SYS_C0011572; 
alter table task add constraint SYS_C0011572 unique (id,ITEM_KEY_id,PICK_TICKET_DETAIL_ID,soi, SRC_LOC_ID, PLAN_QUANTITY);

alter table task_log modify PACKAGE_UNIT_ID null;
alter table task_log modify dst_loc_id null;

/**2020-04-13*/
alter table  ASN  add (ftl_qty  float);
alter table  ASN  add (tlf_qty  float);
alter table  ASN  add (bf_qty  float);

update  ASN set ftl_qty=0,tlf_qty=0,bf_qty=0 
where
ftl_qty is null 
and tlf_qty is null 
and  bf_qty is null