--**************创建表*****************************
create TABLE CUX.CUX_MOVE_ITEM_TABLE2--创建CUX用户下的表（默认用户是APPS, 此表不能用）
( INVENTORY_ITEM_ID VARCHAR2(40) ,
QUANTITY_moved NUMBER,
ORGANIZATION_ID NUMBER not null,
LAST_UPDATE_DATE DATE not null,--------------一般表中必有字段
LAST_UPDATED_BY NUMBER not null,--------
CREATION_DATE DATE not null,----------
CREATED_BY NUMBER not null,--------
LAST_UPDATE_LOGIN NUMBER,---------------------一般表中必有字段
ATTRIBUTE_CATEGORY VARCHAR2(30),---------预留字段
ATTRIBUTE1 VARCHAR2(150),------
ATTRIBUTE2 VARCHAR2(150),------
ATTRIBUTE3 VARCHAR2(150),------
ATTRIBUTE4 VARCHAR2(150),------
ATTRIBUTE5 VARCHAR2(150),------
ATTRIBUTE6 VARCHAR2(150),------
ATTRIBUTE7 VARCHAR2(150),------
ATTRIBUTE8 VARCHAR2(150),------
ATTRIBUTE9 VARCHAR2(150),------
ATTRIBUTE10 VARCHAR2(150),------
ATTRIBUTE11 VARCHAR2(150),------
ATTRIBUTE12 VARCHAR2(150),------
ATTRIBUTE13 VARCHAR2(150),------
ATTRIBUTE14 VARCHAR2(150),------
ATTRIBUTE15 VARCHAR2(150))---------预留字段
CREATE SYNONYM CUX_MOVE_ITEM_TABLE2 FOR CUX.CUX_MOVE_ITEM_TABLE2;--创建同义词表 将其放到默认用户APPS下
SELECT * FROM CUX_MOVE_ITEM_TABLE2
DROP SYNONYM CUX_MOVE_ITEM_TABLE2
DROP TABLE CUX.CUX_MOVE_ITEM_TABLE2
--**********新建序列***************
CREATE SEQUENCE CUX_COPY_QUO
CREATE SYNONYM CUX_COPY_QUO FOR CUX.CUX_COPY_QUO
SELECT CUX_COPY_QUO.NEXTVAL FROM DUAL
DROP SEQUENCE CUX.CUX_COPY_QUO
DROP SYNONYM CUX_COPY_QUO
--************新建视图**************
CREATE OR REPLACE VIEW CUX_MOVE_ITEM_V2 AS
SELECT MSI.SEGMENT1 ITEM_CODE, --物料编码
MSI.DESCRIPTION ITEM_DESC, --物料描述
MSI.PRIMARY_UOM_CODE ITEM_UOM_CODE, --单位
MSI.LOT_CONTROL_CODE, --批次控制编码（ 1 or 2 ）
WRO.ORGANIZATION_ID,
WRO.INVENTORY_ITEM_ID, --物料ID
WRO.QUANTITY_PER_ASSEMBLY,
WRO.QUANTITY_ISSUED,
WRO.WIP_ENTITY_ID, --任务ID
WRO.OPERATION_SEQ_NUM, --工序号
WRO.REQUIRED_QUANTITY, --必需
WRO.COMPONENT_YIELD_FACTOR --产出率
FROM MTL_SYSTEM_ITEMS_VL MSI,
WIP_REQUIREMENT_OPERATIONS WRO
WHERE MSI.ORGANIZATION_ID = WRO.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
AND WRO.QUANTITY_ISSUED > =0 --只写>0会漏掉有相同物料但是发料为0的情况，导致‘11005’查不出数据
create table SCF.CUX_FLODER_DEMO
(
FLODER_DEMO_ID NUMBER not null,
NUMBER_FIELD1 NUMBER not null,
NUMBER_FIELD2 NUMBER,
NUMBER_FIELD3 NUMBER,
NUMBER_FIELD4 NUMBER,
DATE_FIELD1 DATE NOT NULL,
DATE_FIELD2 DATE,
VARCHAR2_FIELD1 VARCHAR2(100) NOT NULL,
VARCHAR2_FIELD2 VARCHAR2(100),
VARCHAR2_FIELD3 VARCHAR2(100),
VARCHAR2_FIELD4 VARCHAR2(100),
VARCHAR2_FIELD5 VARCHAR2(100),
VARCHAR2_FIELD6 VARCHAR2(100),
CREATION_DATE DATE not null,
CREATED_BY NUMBER not null,
LAST_UPDATED_BY NUMBER not null,
LAST_UPDATE_DATE DATE not null,
LAST_UPDATE_LOGIN NUMBER,
ATTRIBUTE_CATEGORY VARCHAR2(30),
ATTRIBUTE1 VARCHAR2(240),
ATTRIBUTE2 VARCHAR2(240),
ATTRIBUTE3 VARCHAR2(240),
ATTRIBUTE4 VARCHAR2(240),
ATTRIBUTE5 VARCHAR2(240),
ATTRIBUTE6 VARCHAR2(240),
ATTRIBUTE7 VARCHAR2(240),
ATTRIBUTE8 VARCHAR2(240),
ATTRIBUTE9 VARCHAR2(240),
ATTRIBUTE10 VARCHAR2(240),
ATTRIBUTE11 VARCHAR2(240),
ATTRIBUTE12 VARCHAR2(240),
ATTRIBUTE13 VARCHAR2(240),
ATTRIBUTE14 VARCHAR2(240),
ATTRIBUTE15 VARCHAR2(240)
)
tablespace SCF;
-- Create/Recreate indexes
create unique index SCF.CUX_FLODER_DEMO_U1 on SCF.CUX_FLODER_DEMO (FLODER_DEMO_ID)
tablespace SCF;
-- Create/Recreate sequence
CREATE SEQUENCE SCF.CUX_FLODER_DEMO_S;
-- Create/Recreate synonum
CREATE SYNONYM CUX_FLODER_DEMO_S FOR scf.CUX_FLODER_DEMO_S;
CREATE SYNONYM CUX_FLODER_DEMO FOR scf.CUX_FLODER_DEMO;
-- Create table
create table WIP.WIP_DISCRETE_JOBS
(
WIP_ENTITY_ID NUMBER not null,
ORGANIZATION_ID NUMBER not null,
LAST_UPDATE_DATE DATE not null,
LAST_UPDATED_BY NUMBER not null,
CREATION_DATE DATE not null,
CREATED_BY NUMBER not null,
LAST_UPDATE_LOGIN NUMBER,
REQUEST_ID NUMBER,
PROGRAM_APPLICATION_ID NUMBER,
PROGRAM_ID NUMBER,
PROGRAM_UPDATE_DATE DATE,
SOURCE_LINE_ID NUMBER,
SOURCE_CODE VARCHAR2(30),
DESCRIPTION VARCHAR2(240),
STATUS_TYPE NUMBER not null,
PRIMARY_ITEM_ID NUMBER,
FIRM_PLANNED_FLAG NUMBER not null,
JOB_TYPE NUMBER not null,
WIP_SUPPLY_TYPE NUMBER not null,
CLASS_CODE VARCHAR2(10) not null,
MATERIAL_ACCOUNT NUMBER,
MATERIAL_OVERHEAD_ACCOUNT NUMBER,
RESOURCE_ACCOUNT NUMBER,
OUTSIDE_PROCESSING_ACCOUNT NUMBER,
MATERIAL_VARIANCE_ACCOUNT NUMBER,
RESOURCE_VARIANCE_ACCOUNT NUMBER,
OUTSIDE_PROC_VARIANCE_ACCOUNT NUMBER,
STD_COST_ADJUSTMENT_ACCOUNT NUMBER,
OVERHEAD_ACCOUNT NUMBER,
OVERHEAD_VARIANCE_ACCOUNT NUMBER,
SCHEDULED_START_DATE DATE not null,
DATE_RELEASED DATE,
SCHEDULED_COMPLETION_DATE DATE not null,
DATE_COMPLETED DATE,
DATE_CLOSED DATE,
START_QUANTITY NUMBER not null,
QUANTITY_COMPLETED NUMBER not null,
QUANTITY_SCRAPPED NUMBER not null,
NET_QUANTITY NUMBER not null,
BOM_REFERENCE_ID NUMBER,
ROUTING_REFERENCE_ID NUMBER,
COMMON_BOM_SEQUENCE_ID NUMBER,
COMMON_ROUTING_SEQUENCE_ID NUMBER,
BOM_REVISION VARCHAR2(3),
ROUTING_REVISION VARCHAR2(3),
BOM_REVISION_DATE DATE,
ROUTING_REVISION_DATE DATE,
LOT_NUMBER VARCHAR2(80),
ALTERNATE_BOM_DESIGNATOR VARCHAR2(10),
ALTERNATE_ROUTING_DESIGNATOR VARCHAR2(10),
COMPLETION_SUBINVENTORY VARCHAR2(10),
COMPLETION_LOCATOR_ID NUMBER,
MPS_SCHEDULED_COMPLETION_DATE DATE,
MPS_NET_QUANTITY NUMBER,
DEMAND_CLASS VARCHAR2(30),
ATTRIBUTE_CATEGORY VARCHAR2(30),
ATTRIBUTE1 VARCHAR2(150),
ATTRIBUTE2 VARCHAR2(150),
ATTRIBUTE3 VARCHAR2(150),
ATTRIBUTE4 VARCHAR2(150),
ATTRIBUTE5 VARCHAR2(150),
ATTRIBUTE6 VARCHAR2(150),
ATTRIBUTE7 VARCHAR2(150),
ATTRIBUTE8 VARCHAR2(150),
ATTRIBUTE9 VARCHAR2(150),
ATTRIBUTE10 VARCHAR2(150),
ATTRIBUTE11 VARCHAR2(150),
ATTRIBUTE12 VARCHAR2(150),
ATTRIBUTE13 VARCHAR2(150),
ATTRIBUTE14 VARCHAR2(150),
ATTRIBUTE15 VARCHAR2(150),
SCHEDULE_GROUP_ID NUMBER,
BUILD_SEQUENCE NUMBER,
LINE_ID NUMBER,
PROJECT_ID NUMBER,
TASK_ID NUMBER,
KANBAN_CARD_ID NUMBER,
OVERCOMPLETION_TOLERANCE_TYPE NUMBER,
OVERCOMPLETION_TOLERANCE_VALUE NUMBER,
END_ITEM_UNIT_NUMBER VARCHAR2(30),
PO_CREATION_TIME NUMBER,
PRIORITY NUMBER,
DUE_DATE DATE,
EST_SCRAP_ACCOUNT NUMBER,
EST_SCRAP_VAR_ACCOUNT NUMBER,
EST_SCRAP_PRIOR_QTY NUMBER,
DUE_DATE_PENALTY NUMBER,
DUE_DATE_TOLERANCE NUMBER,
COPRODUCTS_SUPPLY NUMBER,
PARENT_WIP_ENTITY_ID NUMBER,
ASSET_NUMBER VARCHAR2(30),
ASSET_GROUP_ID NUMBER,
REBUILD_ITEM_ID NUMBER,
REBUILD_SERIAL_NUMBER VARCHAR2(30),
MANUAL_REBUILD_FLAG VARCHAR2(1),
SHUTDOWN_TYPE VARCHAR2(30),
ESTIMATION_STATUS NUMBER,
REQUESTED_START_DATE DATE,
NOTIFICATION_REQUIRED VARCHAR2(1),
WORK_ORDER_TYPE VARCHAR2(30),
OWNING_DEPARTMENT NUMBER,
ACTIVITY_TYPE VARCHAR2(30),
ACTIVITY_CAUSE VARCHAR2(30),
TAGOUT_REQUIRED VARCHAR2(1),
PLAN_MAINTENANCE VARCHAR2(1),
PM_SCHEDULE_ID NUMBER,
LAST_ESTIMATION_DATE DATE,
LAST_ESTIMATION_REQ_ID NUMBER,
ACTIVITY_SOURCE VARCHAR2(30),
SERIALIZATION_START_OP NUMBER,
MAINTENANCE_OBJECT_ID NUMBER,
MAINTENANCE_OBJECT_TYPE NUMBER,
MAINTENANCE_OBJECT_SOURCE NUMBER,
MATERIAL_ISSUE_BY_MO VARCHAR2(1),
SCHEDULING_REQUEST_ID NUMBER,
ISSUE_ZERO_COST_FLAG VARCHAR2(1),
EAM_LINEAR_LOCATION_ID NUMBER,
ACTUAL_START_DATE DATE,
EXPECTED_HOLD_RELEASE_DATE DATE,
EXPEDITED VARCHAR2(1),
JOB_NOTE CLOB
)
tablespace APPS_TS_TX_DATA
pctfree 20
initrans 10
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
-- Create/Recreate indexes
create index WIP.WIP_DISCRETE_JOBS_N1 on WIP.WIP_DISCRETE_JOBS (PRIMARY_ITEM_ID)
tablespace APPS_TS_TX_IDX
pctfree 10
initrans 11
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
create index WIP.WIP_DISCRETE_JOBS_N10 on WIP.WIP_DISCRETE_JOBS (PARENT_WIP_ENTITY_ID, MANUAL_REBUILD_FLAG)
tablespace APPS_TS_TX_IDX
pctfree 10
initrans 11
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
create index WIP.WIP_DISCRETE_JOBS_N11 on WIP.WIP_DISCRETE_JOBS (REBUILD_ITEM_ID)
tablespace APPS_TS_TX_IDX
pctfree 10
initrans 11
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
create index WIP.WIP_DISCRETE_JOBS_N12 on WIP.WIP_DISCRETE_JOBS (ORGANIZATION_ID, PROJECT_ID)
tablespace APPS_TS_TX_IDX
pctfree 10
initrans 11
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
create index WIP.WIP_DISCRETE_JOBS_N13 on WIP.WIP_DISCRETE_JOBS (PROJECT_ID, TASK_ID)
tablespace APPS_TS_TX_IDX
pctfree 10
initrans 11
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
create index WIP.WIP_DISCRETE_JOBS_N14 on WIP.WIP_DISCRETE_JOBS (DATE_CLOSED, ORGANIZATION_ID)
tablespace APPS_TS_TX_IDX
pctfree 10
initrans 11
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
create index WIP.WIP_DISCRETE_JOBS_N2 on WIP.WIP_DISCRETE_JOBS (BOM_REFERENCE_ID, ORGANIZATION_ID)
tablespace APPS_TS_TX_IDX
pctfree 10
initrans 11
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
create index WIP.WIP_DISCRETE_JOBS_N3 on WIP.WIP_DISCRETE_JOBS (ROUTING_REFERENCE_ID, ORGANIZATION_ID)
tablespace APPS_TS_TX_IDX
pctfree 10
initrans 11
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
create index WIP.WIP_DISCRETE_JOBS_N4 on WIP.WIP_DISCRETE_JOBS (CLASS_CODE, ORGANIZATION_ID, ATTRIBUTE1, ATTRIBUTE2)
tablespace APPS_TS_TX_IDX
pctfree 10
initrans 11
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
create index WIP.WIP_DISCRETE_JOBS_N5 on WIP.WIP_DISCRETE_JOBS (FIRM_PLANNED_FLAG, STATUS_TYPE)
tablespace APPS_TS_TX_IDX
pctfree 10
initrans 11
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
create index WIP.WIP_DISCRETE_JOBS_N6 on WIP.WIP_DISCRETE_JOBS (SCHEDULE_GROUP_ID, BUILD_SEQUENCE)
tablespace APPS_TS_TX_IDX
pctfree 10
initrans 11
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
create index WIP.WIP_DISCRETE_JOBS_N7 on WIP.WIP_DISCRETE_JOBS (STATUS_TYPE, ORGANIZATION_ID, SCHEDULED_START_DATE, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3)
tablespace APPS_TS_TX_IDX
pctfree 10
initrans 11
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
create index WIP.WIP_DISCRETE_JOBS_N8 on WIP.WIP_DISCRETE_JOBS (LINE_ID, ORGANIZATION_ID)
tablespace APPS_TS_TX_IDX
pctfree 10
initrans 11
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
create index WIP.WIP_DISCRETE_JOBS_N9 on WIP.WIP_DISCRETE_JOBS (MAINTENANCE_OBJECT_TYPE, MAINTENANCE_OBJECT_ID, MAINTENANCE_OBJECT_SOURCE, ORGANIZATION_ID)
tablespace APPS_TS_TX_IDX
pctfree 10
initrans 11
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
create unique index WIP.WIP_DISCRETE_JOBS_U1 on WIP.WIP_DISCRETE_JOBS (WIP_ENTITY_ID, ORGANIZATION_ID)
tablespace APPS_TS_TX_IDX
pctfree 10
initrans 11
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
-- Grant/Revoke object privileges
grant select, insert, update, delete, references, alter, index on WIP.WIP_DISCRETE_JOBS to APPS with grant option;
扫一扫
4262
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
