ALTER TABLE IF3_POS_VIP_MAS add FAMILY_VIP_ID		 		VARCHAR2(64);
ALTER TABLE IF3_POS_VIP_MAS add WECHAT_ID				VARCHAR2(64);



ALTER TABLE IF3_POS_VIP_MAS add REC_KEY			VARCHAR2(64);



ALTER TABLE IF3_SOMAS add REC_KEY			NUMBER(20);

ALTER TABLE IF3_DOCMAS add PAY_REF			VARCHAR2(64);
ALTER TABLE IF3_DOCMAS add EMAIL_ADDR                VARCHAR2(128);
ALTER TABLE IF3_DOCMAS add DEMAIL_ADDR          	VARCHAR2(128);
ALTER TABLE IF3_DOCMAS add BATTN_TO			VARCHAR2(64);


DROP TABLE IF3_SOMAS	cascade constraint;
DROP TABLE IF3_SOLINE cascade constraint;

CREATE TABLE IF3_SOMAS
(
  TASK_KEY     			NUMBER(20)                      NOT NULL,
  TASK_ID			NUMBER(20)                      NOT NULL,
  ITEM_COUNT			NUMBER(8),	
  ORG_ID			VARCHAR2(8),
  LOC_ID			VARCHAR2(8),
  DOC_ID			VARCHAR2(64),
  DOC_DATE			VARCHAR2(32),
  STATUS_FLG			CHAR(1) 			DEFAULT 'A',
  USER_ID			VARCHAR2(32),
  EMP_ID			VARCHAR2(32),
  EMP_ID2			VARCHAR2(32),
  CUST_ID			VARCHAR2(16),
  NAME				VARCHAR2(256),
  ATTN_TO 			VARCHAR2(64),
  CC_TO				VARCHAR2(64),
  PROJ_ID			VARCHAR2(16),
  DEPT_ID		 	VARCHAR2(16),
  TAX_FLG			CHAR(1) 			DEFAULT 'N',
  TAX_ID			VARCHAR2(8),
  TAX_RATE			VARCHAR2(32),
  CURR_ID		 	VARCHAR2(8),
  CURR_RATE			VARCHAR2(32),
  CUST_REF			VARCHAR2(64),
  OUR_REF			VARCHAR2(64),
  TERM_ID			VARCHAR2(32),
  VALIDITY			VARCHAR2(32),
  ETD				VARCHAR2(32),
  ETA				VARCHAR2(32),
  DLY_DATE			VARCHAR2(32),
  DLY_TIME			VARCHAR2(8),
  CAMPAIGN_ID			VARCHAR2(32),
  TRADE_ID			VARCHAR2(16),
  TRANSPORT_ID			VARCHAR2(16),
  SALETYPE_ID			VARCHAR2(16),
  SALESCAT1_ID			VARCHAR2(16),
  SALESCAT2_ID			VARCHAR2(16),
  SALESCAT3_ID			VARCHAR2(16),
  CONSIGNMENT_FLG		CHAR(1) 			DEFAULT 'N',
  CONSIGNMENT_STORE_ID		VARCHAR2(16),
  BOOK_ID			VARCHAR2(16),
  VSL_ID			VARCHAR2(32),
  MARKING			VARCHAR2(32),
  DISC_CHR			VARCHAR2(32),
  DISC_NUM			VARCHAR2(32),
  LUMPSUM_DISC			VARCHAR2(32),
  ADDR_NAME			VARCHAR2(256),
  ADDRESS1			VARCHAR2(256),
  ADDRESS2			VARCHAR2(256),
  ADDRESS3			VARCHAR2(256),
  ADDRESS4			VARCHAR2(256),
  CITY_ID			VARCHAR2(32),
  STATE_ID			VARCHAR2(64),
  COUNTRY_ID			VARCHAR2(32),
  POSTALCODE			VARCHAR2(32),
  PHONE				VARCHAR2(32),
  FAX				VARCHAR2(32),
  ZONE_ID			VARCHAR2(32),
  DADDR_NAME			VARCHAR2(256),
  DADDRESS1			VARCHAR2(256),
  DADDRESS2			VARCHAR2(256),
  DADDRESS3			VARCHAR2(256),
  DADDRESS4			VARCHAR2(256),
  DCITY_ID			VARCHAR2(32),
  DSTATE_ID			VARCHAR2(64),
  DCOUNTRY_ID			VARCHAR2(32),
  DPOSTALCODE			VARCHAR2(32),
  DPHONE			VARCHAR2(32),
  DFAX				VARCHAR2(32),
  DZONE_ID			VARCHAR2(32),
  DATTN_TO			VARCHAR2(64),
  ANA_ID1			VARCHAR2(32),
  ANA_ID2			VARCHAR2(32),
  ANA_ID3			VARCHAR2(32),
  ANA_ID4			VARCHAR2(32),
  ANA_ID5			VARCHAR2(32),
  ANA_ID6			VARCHAR2(32),
  ANA_ID7			VARCHAR2(32),
  ANA_ID8			VARCHAR2(32),
  ANA_ID9			VARCHAR2(32),
  ANA_ID10			VARCHAR2(32),
  REF1				VARCHAR2(512),
  REF2				VARCHAR2(512),
  REF3				VARCHAR2(512),
  REF4				VARCHAR2(512),
  REMARK			VARCHAR2(2000),
  DEPOSIT_AMT			VARCHAR2(32),
  BEFORE_DISC			VARCHAR2(32),
  TOTAL_DISC			VARCHAR2(32),
  TOTAL_NET			VARCHAR2(32),
  TOTAL_TAX			VARCHAR2(32),
  GRANT_TOTAL			VARCHAR2(32),
  TOTAL_COST			VARCHAR2(32),
  TOTAL_QTY			VARCHAR2(32),
  TOTAL_WEIGHT			VARCHAR2(32),
  TOTAL_VOLUMN			VARCHAR2(32),
  TOTAL_RETAIL			VARCHAR2(32),
  TOTAL_PROFIT			VARCHAR2(32),
  GROSS_MARGIN			VARCHAR2(32),
  TOTAL_TRN_COST		VARCHAR2(32),
  TOTAL_TRN_PROFIT		VARCHAR2(32),
  GROSS_TRN_MARGIN		VARCHAR2(32),
  HOME_TOTAL_NET		VARCHAR2(32),
  HOME_TOTAL_TAX		VARCHAR2(32),
  HOME_GRAND_TOTAL		VARCHAR2(32),
  PAY_REF			VARCHAR2(64),
  EMAIL_ADDR               	VARCHAR2(128),
  DEMAIL_ADDR          	 	VARCHAR2(128),
  BATTN_TO			VARCHAR2(64),
  CREATE_DATE			DATE				DEFAULT SYSDATE,
  CONSTRAINT PK_IF3_SOMAS PRIMARY KEY(TASK_ID),
  CONSTRAINT FK_IF3_SOMAS FOREIGN KEY (TASK_KEY) REFERENCES IF3_TASK_BUf (TASK_KEY) ON DELETE CASCADE
);


CREATE TABLE IF3_SOLINE
(
  TASK_KEY     			NUMBER(20)                      NOT NULL,
  TASK_ID			NUMBER(20)                      NOT NULL,
  LINE_NO			VARCHAR2(32),
  LINE_REF			VARCHAR2(128),
  LINE_TYPE			CHAR(1) 		DEFAULT 'S',
-- S STOCK ITEM
-- N NON STOCK ITEM WITH CODE
-- T TITLE ITEM
-- M NON STOCK ITEM
-- C CHARGE ITEM
  PLU_ID			VARCHAR2(128),
  STK_ID			VARCHAR2(32),
  NAME				VARCHAR2(4000),
  MODEL				VARCHAR2(128),
  UOM_QTY			VARCHAR2(32),
  UOM       			VARCHAR2(16),
  UOM_RATIO			VARCHAR2(32),
  STK_QTY			VARCHAR2(32),
  UOM_ID			VARCHAR2(16),
  LIST_PRICE			VARCHAR2(32),
  DISC_CHR			VARCHAR2(32),
  DISC_NUM			VARCHAR2(32),
  NET_PRICE			VARCHAR2(32),
  UNIT_WEIGHT			VARCHAR2(32),
  UNIT_WEIGHT_UOM		VARCHAR2(8),
  -- G
  -- KG
  -- Ton
  VOLUMN			VARCHAR2(32),
  EXP_DLY_DATE			VARCHAR2(32),
  COM_DLY_DATE			VARCHAR2(32),
  REF_STK_ID			VARCHAR2(32),
  STORE_ID			VARCHAR2(16),
  STKATTR1_ID			VARCHAR2(16),
  STKATTR1			VARCHAR2(16),
  STKATTR2_ID			VARCHAR2(16),
  STKATTR2			VARCHAR2(16),
  STKATTR3_ID			VARCHAR2(16),
  STKATTR3			VARCHAR2(16),
  STKATTR4_ID			VARCHAR2(16),
  STKATTR4			VARCHAR2(16),
  STKATTR5_ID			VARCHAR2(16),
  STKATTR5			VARCHAR2(16),
  BATCH_ID1			VARCHAR2(32),
  BATCH_ID2			VARCHAR2(32),
  BATCH_ID3			VARCHAR2(32),
  BATCH_ID4			VARCHAR2(32),
  SRN_ID			VARCHAR2(64),
  ASSORTMENT_ID			VARCHAR2(16),
  TAX_ID			VARCHAR2(8),
  TAX_RATE			VARCHAR2(32),
  --ACC_ID			VARCHAR2(16),
  KIT_QTY_FLG			CHAR(1), -- DROPDOWN LIST
  KIT_PRICE_FLG			CHAR(1), -- DROPDOWN LIST
  MARGIN			VARCHAR2(32),
  TRN_MARGIN			VARCHAR2(32),
  HS_ID				VARCHAR2(32),
  DLYTYPE_ID		 	VARCHAR2(64),
  DLYCODE_ID			VARCHAR2(64),
  DLY_DESC			VARCHAR2(256),
  DLYZONE_ID			VARCHAR2(32),
  PROJ_ID			VARCHAR2(16),
  DEPT_ID		 	VARCHAR2(16),
  EMP_ID			VARCHAR2(32),
  ANA_ID1			VARCHAR2(32),
  ANA_ID2			VARCHAR2(32),
  ANA_ID3			VARCHAR2(32),
  ANA_ID4			VARCHAR2(32),
  ANA_ID5			VARCHAR2(32),
  ANA_ID6			VARCHAR2(32),
  ANA_ID7			VARCHAR2(32),
  ANA_ID8			VARCHAR2(32),
  ANA_ID9			VARCHAR2(32),
  ANA_ID10			VARCHAR2(32),
  REF1				VARCHAR2(512),
  REF2				VARCHAR2(512),
  REF3				VARCHAR2(512),
  REF4				VARCHAR2(512),
  REMARK			VARCHAR2(2000),
  LINE_TOTAL			VARCHAR2(32),
  LINE_TOTAL_AFTDISC		VARCHAR2(32),
  LINE_TAX			VARCHAR2(32),
  LINE_TOTAL_NET		VARCHAR2(32),
  LINE_TOTAL_WITH_TAX		VARCHAR2(32),
  CREATE_DATE			DATE			DEFAULT SYSDATE,
  CONSTRAINT PK_IF3_SOLINE PRIMARY KEY(TASK_ID),
  CONSTRAINT FK_IF3_SOLINE FOREIGN KEY (TASK_KEY) REFERENCES IF3_TASK_BUf (TASK_KEY) ON DELETE CASCADE
);

-------------------------------------------------------------------------------------------------



CREATE TABLE EPB_API_PLUMAS_LOC (
  PARA_KEY	NUMBER(20),
  LINE_NO	NUMBER(8),
  PLU_ID	VARCHAR2(32) NOT NULL,
  LOC_ID	VARCHAR2(8) NOT NULL,
  RETAIL_NET_PRICE	NUMBER(20,6) DEFAULT 0,
  RETAIL_VIP_PRICE	NUMBER(20,6) DEFAULT 0,
  REMARK	VARCHAR2(2000)
);



-------------------------------------------------------------------------------------------------
DROP TABLE IF3_STKMAS cascade constraint;
DROP TABLE IF3_STKMAS_PRICE cascade constraint;
DROP TABLE IF3_PLUMAS cascade constraint;


CREATE TABLE IF3_STKMAS (
  TASK_KEY 		NUMBER(20) 	NOT NULL,
  TASK_ID		NUMBER(20) 	NOT NULL,
  PARAM_ORG_ID		VARCHAR2(8),
  LINE_TYPE		CHAR(1) DEFAULT 'S' NOT NULL,
-- S:STOCK
-- N:NON-STOCK
-- C:SALES CHARGE
-- P_PURCHASE CHARGE
  ORG_ID		VARCHAR2(8),
  STK_ID		VARCHAR2(32) 	NOT NULL,
  NAME			VARCHAR2(512) 	NOT NULL,
  MODEL			VARCHAR2(128),
  NAME_LANG		VARCHAR2(512),
  UOM_ID		VARCHAR2(16) 	NOT NULL,
  STATUS_FLG		CHAR(1) 	DEFAULT 'A' 	NOT NULL, 
-- A:ACTIVE 
-- I:INACTIVE 
-- P:PHASE OUT
-- R:RUNING CHANGE
  TYPE			CHAR(1) 	DEFAULT 'F' 	NOT NULL,
-- F FINISHED STOCK ITEM
-- S SEMI-FINISHED STOCK ITEM
-- R RAW MATERIAL STOCK ITEM
-- M MERCHANDISE
-- O OTHERS
-- C CONSIGNMENT
-- T CUSTOMER SUPPLY
  SOURCE		CHAR(1) 	DEFAULT 'P' 	NOT NULL,
-- P PURCHASED
-- M MANUFACTURED
-- O OUTSOURCED
-- T OTHERS
  COST_TYPE		CHAR(1) 	DEFAULT 'A' 	NOT NULL,
-- A WEIGHTED AVERAGE
-- B SPECIFIC COSTING BASE ON PER BATCH, SELECTING OF BATCH DEFAULT TO FIFO UNLESS SPECIFIED. 
-- S STANDARD COSTING
-- F FIFO
  BRAND_ID		VARCHAR2(16),
  CAT1_ID		VARCHAR2(16),
  CAT2_ID		VARCHAR2(16),
  CAT3_ID		VARCHAR2(16),
  CAT4_ID		VARCHAR2(16),
  CAT5_ID		VARCHAR2(16),
  CAT6_ID		VARCHAR2(16),
  CAT7_ID		VARCHAR2(16),
  CAT8_ID		VARCHAR2(16),
  RETAIL_LIST_PRICE	NUMBER(20,6) DEFAULT 0 NOT NULL, -- RETAIL PRICE
  RETAIL_DISC_CHR	VARCHAR2(32),
  RETAIL_DISC_NUM	NUMBER(20,6) DEFAULT 0 NOT NULL,
  RETAIL_NET_PRICE	NUMBER(20,6) DEFAULT 0 NOT NULL,
  STD_COST		NUMBER(20,6) DEFAULT 0 NOT NULL,
  VIP_POINT_COEF        NUMBER(20,6) DEFAULT 1 NOT NULL,  -- FOR POS
  VIP_DISC_FLG        	CHAR(1) DEFAULT 'Y',  -- FOR POS
  HEAD_DISC_FLG        	CHAR(1) DEFAULT 'Y',  -- FOR POS
  PACK_QTY		NUMBER(20,6) DEFAULT 1,
  OUTER_PACK_QTY	NUMBER(20,6) DEFAULT 1,
  PALLET_CTN		NUMBER(20,6) DEFAULT 1,
  PALLET_QTY		NUMBER(20,6) DEFAULT 1,
  UNIT_WEIGHT		NUMBER(20,6) DEFAULT 0 NOT NULL,
  UNIT_WEIGHT_UOM	VARCHAR2(8),
-- G
-- KG
-- Ton
  GROSS_WEIGHT		NUMBER(20,6) DEFAULT 0,
  REMARK		VARCHAR2(2000),
  CONSTRAINT PK_IF3_STKMAS PRIMARY KEY(TASK_ID),
  CONSTRAINT FK_IF3_STKMAS FOREIGN KEY (TASK_KEY) REFERENCES IF3_TASK_BUf (TASK_KEY) ON DELETE CASCADE
);




CREATE TABLE IF3_STKMAS_PRICE (
  TASK_KEY 		NUMBER(20) NOT NULL,
  TASK_ID		NUMBER(20) NOT NULL,
  PARAM_ORG_ID		VARCHAR2(8),
  STK_ID		VARCHAR2(32) NOT NULL,
  ORG_ID		VARCHAR2(8) NOT NULL,
  EFT_DATE		DATE NOT NULL,
  RETAIL_LIST_PRICE	NUMBER(20,6) DEFAULT 0 NOT NULL, -- RETAIL PRICE
  RETAIL_DISC_CHR	VARCHAR2(32),
  RETAIL_DISC_NUM	NUMBER(20,6) DEFAULT 0 NOT NULL,
  RETAIL_NET_PRICE	NUMBER(20,6) DEFAULT 0 NOT NULL,
  REMARK		VARCHAR2(2000),
  CONSTRAINT PK_IF3_STKMAS_PRICE PRIMARY KEY(TASK_ID),
  CONSTRAINT FK_IF3_STKMAS_PRICE FOREIGN KEY (TASK_KEY) REFERENCES IF3_TASK_BUf (TASK_KEY) ON DELETE CASCADE
);

CREATE TABLE IF3_PLUMAS (
  TASK_KEY 		NUMBER(20) NOT NULL,
  TASK_ID		NUMBER(20) NOT NULL,
  PARAM_ORG_ID		VARCHAR2(8),
  PLU_ID			VARCHAR2(128) NOT NULL,
  LINE_TYPE			CHAR(1) DEFAULT 'S',
-- S:STOCK, N:NON STOCK
  ORG_ID			VARCHAR2(8) NOT NULL,
  STK_ID			VARCHAR2(32),
  STK_QTY		NUMBER(20,6) DEFAULT 1,
  REMARK			VARCHAR2(256),
  VIP_POINT_COEF        NUMBER(20,6) DEFAULT 1 NOT NULL,  -- FOR POS
  VIP_DISC_FLG        	CHAR(1) DEFAULT 'Y',  -- FOR POS
  HEAD_DISC_FLG        	CHAR(1) DEFAULT 'Y',  -- FOR POS
  CONSTRAINT PK_IF3_PLUMAS PRIMARY KEY(TASK_ID),
  CONSTRAINT FK_IF3_PLUMAS FOREIGN KEY (TASK_KEY) REFERENCES IF3_TASK_BUf (TASK_KEY) ON DELETE CASCADE
);



-------------------------------------------------------------------------------------------------

DROP TABLE IF3_STKMAS cascade constraint;
DROP TABLE IF3_STKMAS_PRICE cascade constraint;
DROP TABLE IF3_PLUMAS cascade constraint;


CREATE TABLE IF3_STKMAS (
  TASK_KEY 		NUMBER(20) 	NOT NULL,
  TASK_ID		NUMBER(20) 	NOT NULL,
  PARAM_ORG_ID		VARCHAR2(8),
  LINE_TYPE		VARCHAR2(32),
-- S:STOCK
-- N:NON-STOCK
-- C:SALES CHARGE
-- P_PURCHASE CHARGE
  ORG_ID		VARCHAR2(32),
  STK_ID		VARCHAR2(32),
  NAME			VARCHAR2(512),
  MODEL			VARCHAR2(128),
  NAME_LANG		VARCHAR2(512),
  UOM_ID		VARCHAR2(32),
  STATUS_FLG		VARCHAR2(32),
-- A:ACTIVE 
-- I:INACTIVE 
-- P:PHASE OUT
-- R:RUNING CHANGE
  TYPE			VARCHAR2(32),
-- F FINISHED STOCK ITEM
-- S SEMI-FINISHED STOCK ITEM
-- R RAW MATERIAL STOCK ITEM
-- M MERCHANDISE
-- O OTHERS
-- C CONSIGNMENT
-- T CUSTOMER SUPPLY
  SOURCE		VARCHAR2(32),
-- P PURCHASED
-- M MANUFACTURED
-- O OUTSOURCED
-- T OTHERS
  COST_TYPE		VARCHAR2(32),
-- A WEIGHTED AVERAGE
-- B SPECIFIC COSTING BASE ON PER BATCH, SELECTING OF BATCH DEFAULT TO FIFO UNLESS SPECIFIED. 
-- S STANDARD COSTING
-- F FIFO
  BRAND_ID		VARCHAR2(32),
  CAT1_ID		VARCHAR2(32),
  CAT2_ID		VARCHAR2(32),
  CAT3_ID		VARCHAR2(32),
  CAT4_ID		VARCHAR2(32),
  CAT5_ID		VARCHAR2(32),
  CAT6_ID		VARCHAR2(32),
  CAT7_ID		VARCHAR2(32),
  CAT8_ID		VARCHAR2(32),
  RETAIL_LIST_PRICE	VARCHAR2(32),
  RETAIL_DISC_CHR	VARCHAR2(32),
  RETAIL_DISC_NUM	VARCHAR2(32),
  RETAIL_NET_PRICE	VARCHAR2(32),
  STD_COST		VARCHAR2(32),
  VIP_POINT_COEF        VARCHAR2(32),
  VIP_DISC_FLG        	VARCHAR2(32),
  HEAD_DISC_FLG        	VARCHAR2(32),
  PACK_QTY		VARCHAR2(32),
  OUTER_PACK_QTY	VARCHAR2(32),
  PALLET_CTN		VARCHAR2(32),
  PALLET_QTY		VARCHAR2(32),
  UNIT_WEIGHT		VARCHAR2(32),
  UNIT_WEIGHT_UOM	VARCHAR2(32),
-- G
-- KG
-- Ton
  GROSS_WEIGHT		VARCHAR2(32),
  REMARK		VARCHAR2(2000),
  CONSTRAINT PK_IF3_STKMAS PRIMARY KEY(TASK_ID),
  CONSTRAINT FK_IF3_STKMAS FOREIGN KEY (TASK_KEY) REFERENCES IF3_TASK_BUf (TASK_KEY) ON DELETE CASCADE
);

CREATE INDEX I_IF3_STKMAS ON IF3_STKMAS(TASK_KEY);



CREATE TABLE IF3_STKMAS_PRICE (
  TASK_KEY 		NUMBER(20) NOT NULL,
  TASK_ID		NUMBER(20) NOT NULL,
  PARAM_ORG_ID		VARCHAR2(8),
  STK_ID		VARCHAR2(32),
  ORG_ID		VARCHAR2(32),
  EFT_DATE		VARCHAR2(32),
  RETAIL_LIST_PRICE	VARCHAR2(32),
  RETAIL_DISC_CHR	VARCHAR2(32),
  RETAIL_DISC_NUM	VARCHAR2(32),
  RETAIL_NET_PRICE	VARCHAR2(32),
  REMARK		VARCHAR2(2000),
  CONSTRAINT PK_IF3_STKMAS_PRICE PRIMARY KEY(TASK_ID),
  CONSTRAINT FK_IF3_STKMAS_PRICE FOREIGN KEY (TASK_KEY) REFERENCES IF3_TASK_BUf (TASK_KEY) ON DELETE CASCADE
);

CREATE INDEX I_IF3_STKMAS_PRICE ON IF3_STKMAS_PRICE(TASK_KEY);


CREATE TABLE IF3_PLUMAS (
  TASK_KEY 		NUMBER(20) NOT NULL,
  TASK_ID		NUMBER(20) NOT NULL,
  PARAM_ORG_ID		VARCHAR2(8),
  PLU_ID		VARCHAR2(128),
  LINE_TYPE		VARCHAR2(32),
-- S:STOCK, N:NON STOCK
  ORG_ID		VARCHAR2(32),
  STK_ID		VARCHAR2(32),
  STK_QTY		VARCHAR2(32),
  REMARK		VARCHAR2(256),
  VIP_POINT_COEF        VARCHAR2(32),
  VIP_DISC_FLG        	VARCHAR2(32),
  HEAD_DISC_FLG        	VARCHAR2(32),
  CONSTRAINT PK_IF3_PLUMAS PRIMARY KEY(TASK_ID),
  CONSTRAINT FK_IF3_PLUMAS FOREIGN KEY (TASK_KEY) REFERENCES IF3_TASK_BUf (TASK_KEY) ON DELETE CASCADE
);

CREATE INDEX I_IF3_PLUMAS ON IF3_PLUMAS(TASK_KEY);

-------------------------------------------------------------------------------------------------


-------------------------------------------------------------------------------------------------



-------------------------------------------------------------------------------------------------


-------------------------------------------------------------------------------------------------

CREATE TABLE IF3_POSPOINTADJMAS
(
  TASK_KEY NUMBER(20) NOT NULL,
  TASK_ID	NUMBER(20) NOT NULL,
  DOC_DATE	DATE NOT NULL,
  SHOP_ID VARCHAR2(32) NOT NULL,
  CREATE_DATE	DATE	DEFAULT SYSDATE,
  CONSTRAINT PK_IF3_POSPOINTADJMAS PRIMARY KEY(TASK_ID),
  CONSTRAINT FK_IF3_POSPOINTADJMAS FOREIGN KEY (TASK_KEY) REFERENCES IF3_TASK_BUf (TASK_KEY) ON DELETE CASCADE
);


CREATE TABLE IF3_POSPOINTADJLINE
(
  TASK_KEY NUMBER(20) NOT NULL,
  TASK_ID	NUMBER(20) NOT NULL,
  VIP_ID	VARCHAR2(64),
  ADJ_PTS NUMBER(20,6),
  REMARK VARCHAR2(2000),
  CREATE_DATE	DATE	DEFAULT SYSDATE,
  CONSTRAINT PK_IF3_POSPOINTADJLINE PRIMARY KEY(TASK_ID),
  CONSTRAINT FK_IF3_POSPOINTADJLINE FOREIGN KEY (TASK_KEY) REFERENCES IF3_TASK_BUf (TASK_KEY) ON DELETE CASCADE
);




-------------------------------------------------------------------

alter TABLE ECSHOP add  ORDER_TYPE			CHAR(1) DEFAULT 'A';

---------------------------------------------------------------


CREATE TABLE IF3_IMPORT_DATA
(
  TASK_KEY     			NUMBER(20)                      NOT NULL,
  TASK_ID			NUMBER(20)                      NOT NULL,
  LINE_NO			VARCHAR2(32),
  DATA				VARCHAR2(4000),
  CREATE_DATE			DATE			DEFAULT SYSDATE,
  CONSTRAINT PK_IF3_IMPORT_DATA PRIMARY KEY(TASK_ID),
  CONSTRAINT FK_IF3_IMPORT_DATA FOREIGN KEY (TASK_KEY) REFERENCES IF3_TASK_BUf (TASK_KEY) ON DELETE CASCADE
);

alter table IF3_TASK_BUF add  ORG_ID            VARCHAR2(8);
alter table IF3_TASK_BUF add  LOC_ID            VARCHAR2(8);



---------------------------------------------------------------

drop table IF3_ECORDERMAS cascade constraint;
drop table IF3_ECORDERLINE cascade constraint;
drop table IF3_ECORDERLINE_KIT cascade constraint;
drop table IF3_ECORDERTAX cascade constraint;
drop table IF3_ECORDERMAS_PAYMENT cascade constraint;


CREATE TABLE IF3_ECORDERMAS (
  TASK_KEY     		NUMBER(20)                      NOT NULL,
  TASK_ID			NUMBER(20)                      NOT NULL,
  ITEM_COUNT			NUMBER(8),
  ORG_ID			VARCHAR2(8),
  LOC_ID			VARCHAR2(8),
  DOC_ID			VARCHAR2(64),
  DOC_DATE			VARCHAR2(32),
  STATUS_FLG	 	CHAR(1) DEFAULT 'A',
-- A - ACTIVE
-- B - CANCELLED
-- C - APPROVING
-- D - POSTING
-- E - POSTED
-- F - INACTIVE
-- G - UNDOPOSTING
-- H - APPROVED
  ORDER_TYPE	CHAR(1) DEFAULT 'A',
-- A: OUTSOURCE
-- B: VENDOR DELIVER
-- C: SELF DELIVER
  TRANS_TYPE   		CHAR(1) DEFAULT 'A',
 ---'A' SALES
 ---'E' RETURN
 ---'G' DEPOSIT
 ---'H' COLLECTION
 ---'J' REFUND
  ECSHOP_ID 			VARCHAR2(32),
  ECSHOP_NAME 			VARCHAR2(128),
  ECLOG_ID  			VARCHAR2(16),
  ECLOG_NAME  			VARCHAR2(256),
  EC_ID  				VARCHAR2(16),
  DELIVERY_STATUS		CHAR(1) DEFAULT 'A',
-- A:NOT DELIVER
-- B:PARTIAL DELIVER
-- C:FULL DELIVER
  PAYMENT_STATUS		CHAR(1) DEFAULT 'A',
-- A:NOT PAY
-- B:PARTIAL PAY
-- C:FULL PAY
  DAYCLOSE_FLG			CHAR(1) DEFAULT 'N',
  USER_ID			VARCHAR2(32),
  EMP_ID			VARCHAR2(32),
  VIP_ID			VARCHAR2(64),
  VIP_NAME			VARCHAR2(128),
  DEPT_ID		 	VARCHAR2(16),
  TAX_FLG			CHAR(1) DEFAULT 'N',
  TAX_ID			VARCHAR2(8),
  TAX_RATE			VARCHAR2(32),
  CURR_ID		 	VARCHAR2(8),
  CURR_RATE			VARCHAR2(32),
  CUST_REF			VARCHAR2(64),
  INVOICE_FLG		CHAR(1) DEFAULT 'N', -- NEED ISSUE TAX INVOICE
  OUR_REF			VARCHAR2(64),
  DLY_DATE			VARCHAR2(32),
  RETURN_ID			VARCHAR2(16),
  TIMESLOT_ID			VARCHAR2(16),
  CAMPAIGN_ID			VARCHAR2(32),
  DADDR_NAME			VARCHAR2(256),
  DADDRESS1			VARCHAR2(256),
  DADDRESS2			VARCHAR2(256),
  DADDRESS3			VARCHAR2(256),
  DADDRESS4			VARCHAR2(256),
  DCITY_ID			VARCHAR2(32),
  DSTATE_ID			VARCHAR2(64),
  DCOUNTRY_ID			VARCHAR2(32),
  DPOSTALCODE			VARCHAR2(32),
  DZONE_ID			VARCHAR2(32),
  DPHONE			VARCHAR2(32),
  DFIXEDPHONE			VARCHAR2(32),
  DMAIL				VARCHAR2(128),
  DATTN_TO			VARCHAR2(64),
  REF1				VARCHAR2(512),
  REF2				VARCHAR2(512),
  REF3				VARCHAR2(512),
  REF4				VARCHAR2(512),
  REMARK			VARCHAR2(2000),
  BEFORE_DISC			VARCHAR2(32),
  TOTAL_DISC			VARCHAR2(32),
  TOTAL_NET			VARCHAR2(32),
  TOTAL_TAX			VARCHAR2(32),
  GRANT_TOTAL			VARCHAR2(32),
  TOTAL_QTY			VARCHAR2(32),
  TOTAL_WEIGHT			VARCHAR2(32),
  TOTAL_VOLUMN			VARCHAR2(32),
  TOTAL_RETAIL			VARCHAR2(32),
  TOTAL_COST			VARCHAR2(32),
  TOTAL_PROFIT			VARCHAR2(32),
  GROSS_MARGIN			VARCHAR2(32),
  TOTAL_TRN_COST		VARCHAR2(32),
  TOTAL_TRN_PROFIT		VARCHAR2(32),
  GROSS_TRN_MARGIN		VARCHAR2(32),
  HOME_TOTAL_NET		VARCHAR2(32),
  HOME_TOTAL_TAX		VARCHAR2(32),
  HOME_GRAND_TOTAL		VARCHAR2(32),
  AMT_SPELL        		VARCHAR2(256),
  PRINT_FLG				CHAR(1) DEFAULT 'N',
  DLY_ADDR_KEY			VARCHAR2(32),
  ORDER_NO			VARCHAR2(64), -- FROM OMS
  SALE_NO			VARCHAR2(64), -- FROM OMS
  ORDER_CREATE_TIME		VARCHAR2(32), -- FROM OMS
  ORDER_PAY_TIME		VARCHAR2(32), -- FROM OMS
  ORDER_CHECK_TIME		VARCHAR2(32), -- FROM OMS
  ORDER_SHIP_TIME		VARCHAR2(32), -- FROM OMS
  DELIVERY_NO			VARCHAR2(64), -- FROM OMS
  PM_ID				VARCHAR2(16), -- FROM OMS
  SALE_AMOUNT			VARCHAR2(32), -- FROM OMS
  GOODS_AMOUNT			VARCHAR2(32), -- FROM OMS
  DISCOUNT_AMOUNT		VARCHAR2(32), -- FROM OMS
  FREIGHT_AMOUNT		VARCHAR2(32), -- FROM OMS
  ADDITIONAL_AMOUNT		VARCHAR2(32), -- FROM OMS
  CREATE_DATE			DATE		DEFAULT SYSDATE, 
  CONSTRAINT PK_IF3_ECORDERMAS PRIMARY KEY(TASK_ID),
  CONSTRAINT FK_IF3_ECORDERMAS FOREIGN KEY (TASK_KEY) REFERENCES IF3_TASK_BUf (TASK_KEY) ON DELETE CASCADE
);



CREATE TABLE IF3_ECORDERLINE (
  TASK_KEY     		NUMBER(20)                      NOT NULL,
  TASK_ID			NUMBER(20)                      NOT NULL,
  LINE_NO			NUMBER(20,10),
  LINE_REF			VARCHAR2(128),
  LINE_TYPE			CHAR(1) DEFAULT 'S',
-- S STOCK ITEM
-- N NON STOCK ITEM WITH CODE
-- T TITLE ITEM
-- M NON STOCK ITEM
-- C CHARGE ITEM
  CASHCARRY			CHAR(1),
  PLU_ID			VARCHAR2(128),
  STK_ID			VARCHAR2(32),
  NAME				VARCHAR2(512),
  MODEL				VARCHAR2(128),
  UOM_QTY			VARCHAR2(32),
  UOM       			VARCHAR2(16),
  UOM_RATIO			VARCHAR2(32),
  STK_QTY			VARCHAR2(32),
  UOM_ID			VARCHAR2(16),
  LIST_PRICE			VARCHAR2(32),
  DISC_CHR			VARCHAR2(32),
  DISC_NUM			VARCHAR2(32),
  NET_PRICE			VARCHAR2(32),
  COST_PRICE			VARCHAR2(32),
  TRN_COST_PRICE		VARCHAR2(32),
  STD_COST			VARCHAR2(32),
  UNIT_WEIGHT			VARCHAR2(32),
  UNIT_WEIGHT_UOM		VARCHAR2(8),
-- G
-- KG
-- Ton
  VOLUMN			VARCHAR2(32),
  EXP_DLY_DATE			VARCHAR2(32),
  COM_DLY_DATE			VARCHAR2(32),
  REF_STK_ID			VARCHAR2(32),
  STORE_ID			VARCHAR2(16),
  STKATTR1_ID			VARCHAR2(16),
  STKATTR1			VARCHAR2(16),
  STKATTR2_ID			VARCHAR2(16),
  STKATTR2			VARCHAR2(16),
  STKATTR3_ID			VARCHAR2(16),
  STKATTR3			VARCHAR2(16),
  STKATTR4_ID			VARCHAR2(16),
  STKATTR4			VARCHAR2(16),
  STKATTR5_ID			VARCHAR2(16),
  STKATTR5			VARCHAR2(16),
  BATCH_ID1			VARCHAR2(32),
  BATCH_ID2			VARCHAR2(32),
  BATCH_ID3			VARCHAR2(32),
  BATCH_ID4			VARCHAR2(32),
  SRN_ID			VARCHAR2(32),
  COMPLETE_FLG			CHAR(1) DEFAULT 'N',
  MARGIN			VARCHAR2(32),
  TRN_MARGIN			VARCHAR2(32),
  REF1				VARCHAR2(512),
  REF2				VARCHAR2(512),
  REF3				VARCHAR2(512),
  REF4				VARCHAR2(512),
  REMARK			VARCHAR2(2000),
  ORI_REC_KEY			VARCHAR2(32),
  LINE_TOTAL			VARCHAR2(32),
  LINE_TOTAL_AFTDISC		VARCHAR2(32),
  LINE_TAX			VARCHAR2(32),
  LINE_TOTAL_NET		VARCHAR2(32),
  LINE_TOTAL_WITH_TAX		VARCHAR2(32),
  PB_CODE			VARCHAR2(64),
  PB_PRICE			VARCHAR2(32),
  PB_REMARK			VARCHAR2(2000),
  RETAIL_NET_PRICE		VARCHAR2(32),
  B2B_FLG			CHAR(1) DEFAULT 'N',
  PUR_ACC_ID			VARCHAR2(16),
  PUR_CURR_ID		 	VARCHAR2(8),
  PUR_CURR_RATE			VARCHAR2(32),
  PUR_PRICE			VARCHAR2(32),
  PUR_DISC_CHR			VARCHAR2(32),
  PUR_DISC_NUM			VARCHAR2(32),
  PUR_NET_PRICE			VARCHAR2(32),
  PUR_UOM_QTY			VARCHAR2(32),
  PUR_UOM       		VARCHAR2(16),
  PUR_UOM_RATIO			VARCHAR2(32),
  PUR_STK_QTY			VARCHAR2(32),
  PUR_UOM_ID			VARCHAR2(16),
  LINE_PUR_TOTAL		VARCHAR2(32),
  KIT_QTY_FLG			CHAR(1), -- DROPDOWN LIST
  KIT_PRICE_FLG			CHAR(1), -- DROPDOWN LIST
  RETURN_ID			VARCHAR2(16),
  SRC_CODE			VARCHAR2(32),
  SRC_LOC_ID			VARCHAR2(8),
  SRC_REC_KEY			VARCHAR2(32),
  SRC_LINE_REC_KEY		VARCHAR2(32),
  SRC_DOC_ID			VARCHAR2(64),
  CREATE_DATE		DATE			DEFAULT SYSDATE,  
  CONSTRAINT PK_IF3_ECORDERLINE PRIMARY KEY(TASK_ID),
  CONSTRAINT FK_IF3_ECORDERLINE FOREIGN KEY (TASK_KEY) REFERENCES IF3_TASK_BUf (TASK_KEY) ON DELETE CASCADE
);


CREATE TABLE IF3_ECORDERLINE_KIT (
  TASK_KEY             NUMBER(20)                      NOT NULL,
  MAS_TASK_ID            NUMBER(20)                      NOT NULL,
  TASK_ID            NUMBER(20)                      NOT NULL,
  LINE_NO			VARCHAR2(32),
  LINE_REF			VARCHAR2(128),
  LINE_TYPE			CHAR(1) DEFAULT 'S',
-- S STOCK ITEM
-- N NON STOCK ITEM WITH CODE
-- T TITLE ITEM
-- M NON STOCK ITEM
-- C CHARGE ITEM
  PLU_ID			VARCHAR2(128),
  STK_ID			VARCHAR2(32),
  NAME				VARCHAR2(512),
  MODEL				VARCHAR2(128),
  UOM_QTY			VARCHAR2(32),
  UOM       			VARCHAR2(16),
  UOM_RATIO			NUMBER(20,9) DEFAULT 1,
  STK_QTY			VARCHAR2(32),
  UOM_ID			VARCHAR2(16),
  LIST_PRICE			VARCHAR2(32),
  DISC_CHR			VARCHAR2(32),
  DISC_NUM			VARCHAR2(32),
  NET_PRICE			VARCHAR2(32),
  COST_PRICE			VARCHAR2(32),
  TRN_COST_PRICE		NUMBER(20,6) DEFAULT 0,
  STD_COST			VARCHAR2(32),
  UNIT_WEIGHT			VARCHAR2(32),
  UNIT_WEIGHT_UOM		VARCHAR2(8),
-- G
-- KG
-- Ton
  VOLUMN			VARCHAR2(32),
  REF_STK_ID			VARCHAR2(32),
  STORE_ID			VARCHAR2(16),
  STKATTR1_ID			VARCHAR2(16),
  STKATTR1			VARCHAR2(16),
  STKATTR2_ID			VARCHAR2(16),
  STKATTR2			VARCHAR2(16),
  STKATTR3_ID			VARCHAR2(16),
  STKATTR3			VARCHAR2(16),
  STKATTR4_ID			VARCHAR2(16),
  STKATTR4			VARCHAR2(16),
  STKATTR5_ID			VARCHAR2(16),
  STKATTR5			VARCHAR2(16),
  BATCH_ID1			VARCHAR2(32),
  BATCH_ID2			VARCHAR2(32),
  BATCH_ID3			VARCHAR2(32),
  BATCH_ID4			VARCHAR2(32),
  SRN_ID			VARCHAR2(32),
  PUR_ACC_ID			VARCHAR2(16),
  PUR_CURR_ID		 	VARCHAR2(8),
  PUR_CURR_RATE			NUMBER(20,9),
  PUR_PRICE			VARCHAR2(32),
  PUR_DISC_CHR			VARCHAR2(32),
  PUR_DISC_NUM			VARCHAR2(32),
  PUR_NET_PRICE			VARCHAR2(32),
  PUR_UOM_QTY			VARCHAR2(32),
  PUR_UOM       		VARCHAR2(16),
  PUR_UOM_RATIO			VARCHAR2(32),
  PUR_STK_QTY			VARCHAR2(32),
  PUR_UOM_ID			VARCHAR2(16),
  LINE_PUR_TOTAL		VARCHAR2(32),
  REF1				VARCHAR2(512),
  REF2				VARCHAR2(512),
  REF3				VARCHAR2(512),
  REF4				VARCHAR2(512),
  REMARK			VARCHAR2(2000),
  ORI_REC_KEY			VARCHAR2(32),
  LINE_TOTAL			VARCHAR2(32),
  LINE_TOTAL_AFTDISC		VARCHAR2(32),
  LINE_TAX			VARCHAR2(32),
  LINE_TOTAL_NET		VARCHAR2(32),
  LINE_TOTAL_WITH_TAX		VARCHAR2(32),
  PB_CODE			VARCHAR2(64),
  PB_PRICE			VARCHAR2(32),
  PB_REMARK			VARCHAR2(2000),
  RETAIL_NET_PRICE		VARCHAR2(32),
  CREATE_DATE		DATE			DEFAULT SYSDATE,  
  CONSTRAINT PK_IF3_ECORDERLINE_KIT PRIMARY KEY(TASK_ID),
  CONSTRAINT FK_IF3_ECORDERLINE_KIT1 FOREIGN KEY (TASK_KEY) REFERENCES IF3_TASK_BUf (TASK_KEY) ON DELETE CASCADE,
  CONSTRAINT FK_IF3_ECORDERLINE_KIT2 FOREIGN KEY (MAS_TASK_ID) REFERENCES IF3_ECORDERLINE (TASK_ID) ON DELETE CASCADE
);

 

CREATE TABLE IF3_ECORDERMAS_PAYMENT (
  TASK_KEY     		NUMBER(20)                      NOT NULL,
  TASK_ID			NUMBER(20)                      NOT NULL,
  LINE_NO	 		VARCHAR2(32),
  PAY_DATE	 		VARCHAR2(32),
  PAY_USER_ID		VARCHAR2(32),
  PAY_STATUS		CHAR(1) DEFAULT 'N',
-- NOT PAY
-- PAY
  CURR_ID	 		VARCHAR2(8),
  CURR_RATE	 		VARCHAR2(32),
  CURR_AMT	 		VARCHAR2(32),
  HOME_AMT	 		VARCHAR2(32),
  PM_ID 	 		VARCHAR2(16),
  PAY_REF	 		VARCHAR2(256),
  PAY_RECEIVE		VARCHAR2(32),
  PAY_CHANGE		VARCHAR2(32),
  REF1				VARCHAR2(512),
  REF2				VARCHAR2(512),
  REF3				VARCHAR2(512),
  REF4				VARCHAR2(512),
  REMARK	 		VARCHAR2(2000),
  ACC_REC_KEY     	VARCHAR2(32),
  CREATE_DATE		DATE			DEFAULT SYSDATE,
  CONSTRAINT PK_IF3_ECORDERMAS_PAYMENT PRIMARY KEY(TASK_ID),
  CONSTRAINT FK_IF3_ECORDERMAS_PAYMENT FOREIGN KEY (TASK_KEY) REFERENCES IF3_TASK_BUf (TASK_KEY) ON DELETE CASCADE
);

 
CREATE TABLE IF3_ECORDERTAX (
  TASK_KEY     		NUMBER(20)                      NOT NULL,
  TASK_ID			NUMBER(20)                      NOT NULL,
  ACC_ID			VARCHAR2(16),
  ACC_NAME			VARCHAR2(256),
  TAX_REF			VARCHAR2(32),
  TAX_SIGN			CHAR(1),
  TAX_ID			VARCHAR2(16),
  TAX_RATE			VARCHAR2(32),
  REF_DATE			VARCHAR2(32),
  TAX_INV_NO		VARCHAR2(128),
  DESCRIPTION		VARCHAR2(512),
  CURR_ID			VARCHAR2(8),
  CURR_RATE			VARCHAR2(32),
  CURR_AMT			VARCHAR2(32),
  AMT				VARCHAR2(32),
  SRC_CURR_AMT		VARCHAR2(32),
  SRC_AMT			VARCHAR2(32),
  TAX_REF1			VARCHAR2(64),
  TAX_REF2			VARCHAR2(64),
  TAX_REF3			VARCHAR2(64),
  TAX_REF4			VARCHAR2(64),
  REMARK			VARCHAR2(2000),  
  OINV_REC_KEY		VARCHAR2(32),
  CREATE_DATE		DATE			DEFAULT SYSDATE,
  CONSTRAINT PK_IF3_ECORDERTAX PRIMARY KEY(TASK_ID),
  CONSTRAINT FK_IF3_ECORDERTAX FOREIGN KEY (TASK_KEY) REFERENCES IF3_TASK_BUf (TASK_KEY) ON DELETE CASCADE
);

----------------------------------------------------------------------------------------


drop table IF3_DOCMAS cascade constraint;
drop table IF3_DOCLINE cascade constraint;


CREATE TABLE IF3_DOCMAS
(
  TASK_KEY     			NUMBER(20)                      NOT NULL,
  TASK_ID			NUMBER(20)                      NOT NULL,
  ITEM_COUNT			NUMBER(8),	
  ORG_ID			VARCHAR2(8),
  LOC_ID			VARCHAR2(8),
  DOC_ID			VARCHAR2(64),
  DOC_DATE			VARCHAR2(32),
  STATUS_FLG			CHAR(1) 			DEFAULT 'A',
  USER_ID			VARCHAR2(32),
  EMP_ID			VARCHAR2(32),
  EMP_ID2			VARCHAR2(32),
  CUST_ID			VARCHAR2(16),
  NAME				VARCHAR2(256),
  ATTN_TO 			VARCHAR2(64),
  CC_TO				VARCHAR2(64),
  PROJ_ID			VARCHAR2(16),
  DEPT_ID		 	VARCHAR2(16),
  TAX_FLG			CHAR(1) 			DEFAULT 'N',
  TAX_ID			VARCHAR2(8),
  TAX_RATE			VARCHAR2(32),
  CURR_ID		 	VARCHAR2(8),
  CURR_RATE			VARCHAR2(32),
  CUST_REF			VARCHAR2(64),
  OUR_REF			VARCHAR2(64),
  TERM_ID			VARCHAR2(32),
  VALIDITY			VARCHAR2(32),
  ETD				VARCHAR2(32),
  ETA				VARCHAR2(32),
  DLY_DATE			VARCHAR2(32),
  DLY_TIME			VARCHAR2(8),
  CAMPAIGN_ID			VARCHAR2(32),
  TRADE_ID			VARCHAR2(16),
  TRANSPORT_ID			VARCHAR2(16),
  SALETYPE_ID			VARCHAR2(16),
  SALESCAT1_ID			VARCHAR2(16),
  SALESCAT2_ID			VARCHAR2(16),
  SALESCAT3_ID			VARCHAR2(16),
  CONSIGNMENT_FLG		CHAR(1) 			DEFAULT 'N',
  CONSIGNMENT_STORE_ID		VARCHAR2(16),
  BOOK_ID			VARCHAR2(16),
  VSL_ID			VARCHAR2(32),
  MARKING			VARCHAR2(32),
  DISC_CHR			VARCHAR2(32),
  DISC_NUM			NUMBER(20,6) 			DEFAULT 0 NOT NULL,
  LUMPSUM_DISC			NUMBER(20,2),
  ADDR_NAME			VARCHAR2(256),
  ADDRESS1			VARCHAR2(256),
  ADDRESS2			VARCHAR2(256),
  ADDRESS3			VARCHAR2(256),
  ADDRESS4			VARCHAR2(256),
  CITY_ID			VARCHAR2(32),
  STATE_ID			VARCHAR2(64),
  COUNTRY_ID			VARCHAR2(32),
  POSTALCODE			VARCHAR2(32),
  PHONE				VARCHAR2(32),
  FAX				VARCHAR2(32),
  ZONE_ID			VARCHAR2(32),
  DADDR_NAME			VARCHAR2(256),
  DADDRESS1			VARCHAR2(256),
  DADDRESS2			VARCHAR2(256),
  DADDRESS3			VARCHAR2(256),
  DADDRESS4			VARCHAR2(256),
  DCITY_ID			VARCHAR2(32),
  DSTATE_ID			VARCHAR2(64),
  DCOUNTRY_ID			VARCHAR2(32),
  DPOSTALCODE			VARCHAR2(32),
  DPHONE			VARCHAR2(32),
  DFAX				VARCHAR2(32),
  DZONE_ID			VARCHAR2(32),
  DATTN_TO			VARCHAR2(64),
  ANA_ID1			VARCHAR2(32),
  ANA_ID2			VARCHAR2(32),
  ANA_ID3			VARCHAR2(32),
  ANA_ID4			VARCHAR2(32),
  ANA_ID5			VARCHAR2(32),
  ANA_ID6			VARCHAR2(32),
  ANA_ID7			VARCHAR2(32),
  ANA_ID8			VARCHAR2(32),
  ANA_ID9			VARCHAR2(32),
  ANA_ID10			VARCHAR2(32),
  REF1				VARCHAR2(512),
  REF2				VARCHAR2(512),
  REF3				VARCHAR2(512),
  REF4				VARCHAR2(512),
  REMARK			VARCHAR2(2000),
  DEPOSIT_AMT			VARCHAR2(32),
  BEFORE_DISC			VARCHAR2(32),
  TOTAL_DISC			VARCHAR2(32),
  TOTAL_NET			VARCHAR2(32),
  TOTAL_TAX			VARCHAR2(32),
  GRANT_TOTAL			VARCHAR2(32),
  TOTAL_COST			VARCHAR2(32),
  TOTAL_QTY			VARCHAR2(32),
  TOTAL_WEIGHT			VARCHAR2(32),
  TOTAL_VOLUMN			VARCHAR2(32),
  TOTAL_RETAIL			VARCHAR2(32),
  TOTAL_PROFIT			VARCHAR2(32),
  GROSS_MARGIN			VARCHAR2(32),
  TOTAL_TRN_COST		VARCHAR2(32),
  TOTAL_TRN_PROFIT		VARCHAR2(32),
  GROSS_TRN_MARGIN		VARCHAR2(32),
  HOME_TOTAL_NET		VARCHAR2(32),
  HOME_TOTAL_TAX		VARCHAR2(32),
  HOME_GRAND_TOTAL		VARCHAR2(32),
  CREATE_DATE			DATE				DEFAULT SYSDATE,
  CONSTRAINT PK_IF3_DOCMAS PRIMARY KEY(TASK_ID),
  CONSTRAINT FK_IF3_DOCMAS FOREIGN KEY (TASK_KEY) REFERENCES IF3_TASK_BUf (TASK_KEY) ON DELETE CASCADE
);


CREATE TABLE IF3_DOCLINE
(
  TASK_KEY     			NUMBER(20)                      NOT NULL,
  TASK_ID			NUMBER(20)                      NOT NULL,
  LINE_NO			VARCHAR2(32),
  LINE_REF			VARCHAR2(128),
  LINE_TYPE			CHAR(1) 		DEFAULT 'S',
-- S STOCK ITEM
-- N NON STOCK ITEM WITH CODE
-- T TITLE ITEM
-- M NON STOCK ITEM
-- C CHARGE ITEM
  PLU_ID			VARCHAR2(128),
  STK_ID			VARCHAR2(32),
  NAME				VARCHAR2(4000),
  MODEL				VARCHAR2(128),
  UOM_QTY			VARCHAR2(32),
  UOM       			VARCHAR2(16),
  UOM_RATIO			VARCHAR2(32),
  STK_QTY			VARCHAR2(32),
  UOM_ID			VARCHAR2(16),
  LIST_PRICE			VARCHAR2(32),
  DISC_CHR			VARCHAR2(32),
  DISC_NUM			VARCHAR2(32),
  NET_PRICE			VARCHAR2(32),
  UNIT_WEIGHT			VARCHAR2(32),
  UNIT_WEIGHT_UOM		VARCHAR2(8),
  -- G
  -- KG
  -- Ton
  VOLUMN			VARCHAR2(32),
  EXP_DLY_DATE			VARCHAR2(32),
  COM_DLY_DATE			VARCHAR2(32),
  REF_STK_ID			VARCHAR2(32),
  STORE_ID			VARCHAR2(16),
  STKATTR1_ID			VARCHAR2(16),
  STKATTR1			VARCHAR2(16),
  STKATTR2_ID			VARCHAR2(16),
  STKATTR2			VARCHAR2(16),
  STKATTR3_ID			VARCHAR2(16),
  STKATTR3			VARCHAR2(16),
  STKATTR4_ID			VARCHAR2(16),
  STKATTR4			VARCHAR2(16),
  STKATTR5_ID			VARCHAR2(16),
  STKATTR5			VARCHAR2(16),
  BATCH_ID1			VARCHAR2(32),
  BATCH_ID2			VARCHAR2(32),
  BATCH_ID3			VARCHAR2(32),
  BATCH_ID4			VARCHAR2(32),
  SRN_ID			VARCHAR2(32),
  --COMPLETE_FLG		CHAR(1) DEFAULT 'N',
  ASSORTMENT_ID			VARCHAR2(16),
  TAX_ID			VARCHAR2(8),
  TAX_RATE			VARCHAR2(32),
  --ACC_ID			VARCHAR2(16),
  KIT_QTY_FLG			CHAR(1), -- DROPDOWN LIST
  KIT_PRICE_FLG			CHAR(1), -- DROPDOWN LIST
  MARGIN			VARCHAR2(32),
  TRN_MARGIN			VARCHAR2(32),
  HS_ID				VARCHAR2(32),
  DLYTYPE_ID		 	VARCHAR2(64),
  DLYCODE_ID			VARCHAR2(64),
  DLY_DESC			VARCHAR2(256),
  DLYZONE_ID			VARCHAR2(32),
  PROJ_ID			VARCHAR2(16),
  DEPT_ID		 	VARCHAR2(16),
  EMP_ID			VARCHAR2(32),
  ANA_ID1			VARCHAR2(32),
  ANA_ID2			VARCHAR2(32),
  ANA_ID3			VARCHAR2(32),
  ANA_ID4			VARCHAR2(32),
  ANA_ID5			VARCHAR2(32),
  ANA_ID6			VARCHAR2(32),
  ANA_ID7			VARCHAR2(32),
  ANA_ID8			VARCHAR2(32),
  ANA_ID9			VARCHAR2(32),
  ANA_ID10			VARCHAR2(32),
  REF1				VARCHAR2(512),
  REF2				VARCHAR2(512),
  REF3				VARCHAR2(512),
  REF4				VARCHAR2(512),
  REMARK			VARCHAR2(2000),
  LINE_TOTAL			VARCHAR2(32),
  LINE_TOTAL_AFTDISC		VARCHAR2(32),
  LINE_TAX			VARCHAR2(32),
  LINE_TOTAL_NET		VARCHAR2(32),
  LINE_TOTAL_WITH_TAX		VARCHAR2(32),
  CREATE_DATE			DATE			DEFAULT SYSDATE,
  CONSTRAINT PK_IF3_DOCLINE PRIMARY KEY(TASK_ID),
  CONSTRAINT FK_IF3_DOCLINE FOREIGN KEY (TASK_KEY) REFERENCES IF3_TASK_BUf (TASK_KEY) ON DELETE CASCADE
);


----------------------------------------------------------------------------------------------------

--FOR SERVER ONLY
ALTER TABLE BIPOS ADD  CREATE_DATE			date DEFAULT SYSDATE;
--END FOR SERVER ONLY





drop table IF3_TASK_BUF cascade constraint;
drop table IF3_POS_VIP_MAS cascade constraint;


CREATE TABLE IF3_TASK_BUF
(
  TASK_KEY     		NUMBER(20)                      NOT NULL,
  APP_CODE     		VARCHAR2(64)                	NOT NULL,
  EPB_ID        	VARCHAR2(32),
  IF_TYPE       	VARCHAR2(32),
  IF_KEY		VARCHAR2(32),
  STATUS       		CHAR(1)                     	DEFAULT 'A'                   NOT NULL,
  EPB_VERSION		VARCHAR2(32),
  --A: Active  S: Success   F: Fail
  FILE_NAME		VARCHAR2(256),  
  MSG_CODE		VARCHAR2(256),  
  MSG          		VARCHAR2(4000),
  REC_KEY_RETURN 	NUMBER(20),
  MODIFY_TIME  		DATE                             DEFAULT SYSDATE,
  CREATE_TIME  		DATE                             DEFAULT SYSDATE,
  CONSTRAINT PK_IF3_TASK_BUF PRIMARY KEY(TASK_KEY)
);






CREATE TABLE IF3_POS_VIP_MAS
(
  TASK_KEY     		NUMBER(20)                      NOT NULL,
  TASK_ID		NUMBER(20)                      NOT NULL,
  PARAM_ORG_ID		VARCHAR2(256),
  REC_KEY             	VARCHAR2(256),
  TIME_STAMP            VARCHAR2(256),
  ORG_ID             	VARCHAR2(256),
  VIP_ID             	VARCHAR2(256),
  REF_VIP_ID          	VARCHAR2(256),
  NAME             	VARCHAR2(256),
  CHRISTIAN_NAME    	VARCHAR2(256),
  CARD_NO             	VARCHAR2(256),
  CLASS_ID             	VARCHAR2(256),
  VIP_PHONE1          	VARCHAR2(256),
  VIP_PHONE2          	VARCHAR2(256),
  GENDER             	VARCHAR2(256),
  ADDRESS1             	VARCHAR2(256),
  ADDRESS2             	VARCHAR2(256),
  ADDRESS3             	VARCHAR2(256),
  ADDRESS4             	VARCHAR2(256),
  CITY_ID             	VARCHAR2(256),
  STATE_ID             	VARCHAR2(256),
  COUNTRY_ID          	VARCHAR2(256),
  POSTALCODE          	VARCHAR2(256),
  PHONE             	VARCHAR2(256),
  FAX             	VARCHAR2(256),
  EMAIL_ADDR         	VARCHAR2(256),
  ZONE_ID             	VARCHAR2(256),
  TITLE_ID             	VARCHAR2(256),
  DOB_DAY             	VARCHAR2(256),
  DOB_MONTH             VARCHAR2(256),
  DOB_YEAR             	VARCHAR2(256),
  VALID_DATE          	VARCHAR2(256),
  EXPIRE_DATE          	VARCHAR2(256),
  REF1             	VARCHAR2(256),
  REF2             	VARCHAR2(256),
  REF3             	VARCHAR2(256),
  REF4             	VARCHAR2(256),
  REF5             	VARCHAR2(256),
  REF6             	VARCHAR2(256),
  REF7             	VARCHAR2(256),
  REF8             	VARCHAR2(256),
  REF9             	VARCHAR2(256),
  REF10             	VARCHAR2(256),
  REF11             	VARCHAR2(256),
  REF12             	VARCHAR2(256),
  CREATE_DATE          	VARCHAR2(256),
  CREATE_USER_ID       	VARCHAR2(256),
  LASTUPDATE           	VARCHAR2(256),
  LASTUPDATE_USER_ID   	VARCHAR2(256),
  EDUCATION_ID         	VARCHAR2(256),
  INCOME_ID             VARCHAR2(256),
  MARRIAGE_ID          	VARCHAR2(256),
  PROFESSION_ID        	VARCHAR2(256),
  OCCUPATION_ID       	VARCHAR2(256),
  SOURCE_ID             VARCHAR2(256),
  SHOP_ID             	VARCHAR2(256),
  EMP_ID             	VARCHAR2(256),
  REMARK1             	VARCHAR2(2000),
  REMARK2             	VARCHAR2(2000),
  REMARK3             	VARCHAR2(2000),
  REMARK4             	VARCHAR2(2000),
  CHILDNUM             	VARCHAR2(256),
  SELF1_ID             	VARCHAR2(256),
  SELF2_ID             	VARCHAR2(256),
  SELF3_ID             	VARCHAR2(256),
  SELF4_ID             	VARCHAR2(256),
  SELF5_ID             	VARCHAR2(256),
  SELF6_ID             	VARCHAR2(256),
  SELF7_ID             	VARCHAR2(256),
  SELF8_ID             	VARCHAR2(256),
  STATUS_FLG         	VARCHAR2(256),
  CUST_ID             	VARCHAR2(256),
  REF_DATE1             VARCHAR2(256),
  REF_DATE2             VARCHAR2(256),
  REF_ORG_ID         	VARCHAR2(256),
  LOC_ID             	VARCHAR2(256),
  ENCRYPT_FLG         	VARCHAR2(256),
  LAST_DOC_ID        	VARCHAR2(256),
  SMS_FLG             	VARCHAR2(256),
  BIRTH_DATE          	VARCHAR2(256),
  REF13             	VARCHAR2(256),
  REF14             	VARCHAR2(256),
  REF15             	VARCHAR2(256),
  REF16             	VARCHAR2(256),
  REF17             	VARCHAR2(256),
  REF18             	VARCHAR2(256),
  REF19             	VARCHAR2(256),
  REF20             	VARCHAR2(256),
  REF21             	VARCHAR2(256),
  REF22             	VARCHAR2(256),
  REF23             	VARCHAR2(256),
  REF24             	VARCHAR2(256),
  VIP_PWD             	VARCHAR2(256),
  CONSTRAINT PK_IF3_POS_VIP_MAS PRIMARY KEY(TASK_ID),
  CONSTRAINT FK_IF3_POS_VIP_MAS FOREIGN KEY (TASK_KEY) REFERENCES IF3_TASK_BUf (TASK_KEY) ON DELETE CASCADE
);