|
|
CREATE TABLE HISTORYMAINLINE
(
HISTORYMAINLINEID CHAR(16 BYTE) DEFAULT '0' NOT NULL,
HISTORYID CHAR(16 BYTE),
TXNID CHAR(16 BYTE),
APPLICATION NUMBER(10),
BASETXNTYPE NUMBER(10),
BINNINGINCLUDED NUMBER(10),
BONUSINCLUDED NUMBER(10),
CHANGECOUNT NUMBER(10),
CLIENT NUMBER(10),
COMMENTS VARCHAR2(255 BYTE),
COMPOUNDTXNTYPE NUMBER(10),
CONTAINERID CHAR(16 BYTE),
DEFECTINCLUDED NUMBER(10),
EMPLOYEEID CHAR(16 BYTE),
FACTORYID CHAR(16 BYTE),
ICONID NUMBER(10),
IMPLICIT NUMBER(10),
INREWORK NUMBER(10),
LOCALREWORKINCLUDED NUMBER(10),
LOGINID CHAR(16 BYTE),
LOSSINCLUDED NUMBER(10),
MFGDATE DATE,
OPERATIONID CHAR(16 BYTE),
OWNERID CHAR(16 BYTE),
PARAMETRICDETAILID CHAR(16 BYTE),
PRODUCTID CHAR(16 BYTE),
RESOURCEID CHAR(16 BYTE),
REVERSALSTATUS NUMBER(10),
SERVER NUMBER(10),
CALENDARSHIFTID CHAR(16 BYTE),
SPECID CHAR(16 BYTE),
STATUS NUMBER(10),
STEPENTRYTXNID CHAR(16 BYTE),
STEPPASS NUMBER(10),
SYSTEMDATE DATE,
SYSTEMDATEGMT DATE,
TXNDATE DATE,
TXNDATEGMT DATE,
TXNTYPE NUMBER(10),
USERID CHAR(16 BYTE),
WORKFLOWSTEPID CHAR(16 BYTE),
SHIFTNAME VARCHAR2(40 BYTE),
CDOTYPEID NUMBER(10),
WIPTRACKINGGROUPKEYID CHAR(16 BYTE),
CALLBYCDONAME VARCHAR2(40 BYTE),
CDONAME VARCHAR2(40 BYTE),
CDOTXNSEQUENCE NUMBER(10),
CARRIERID CHAR(16 BYTE),
CARRIERNAME VARCHAR2(40 BYTE),
HISTORYSUMMARYID CHAR(16 BYTE),
SPECPASS NUMBER(10),
SPECNAME VARCHAR2(40 BYTE),
PRODUCTNAME VARCHAR2(40 BYTE),
STEPLOGICNAME VARCHAR2(40 BYTE),
USERNAME VARCHAR2(40 BYTE),
OWNERNAME VARCHAR2(40 BYTE),
QTY NUMBER(10),
QTY2 NUMBER(10),
MOVEINQTY NUMBER(10),
MOVEINQTY2 NUMBER(10),
MOVEINTIMESTAMP DATE,
RESOURCENAME VARCHAR2(40 BYTE),
FROMSPECNAME VARCHAR2(40 BYTE),
FROMQTY NUMBER(10),
FROMQTY2 NUMBER(10),
FROMSPECPASS NUMBER(10),
CONTAINERNAME VARCHAR2(40 BYTE),
EMPLOYEENAME VARCHAR2(40 BYTE),
FROMSPECID CHAR(16 BYTE),
FROMUOMNAME VARCHAR2(40 BYTE),
UOMNAME VARCHAR2(40 BYTE),
CALENDARJULIANDAY VARCHAR2(40 BYTE),
CALENDARMONTH VARCHAR2(40 BYTE),
CALENDARWORKWEEK VARCHAR2(40 BYTE),
CALENDARYEAR VARCHAR2(40 BYTE),
FROMSTATUS NUMBER(10),
FROMWORKFLOWNAME VARCHAR2(40 BYTE),
WORKFLOWNAME VARCHAR2(40 BYTE),
INTEGRATIONTXNID CHAR(16 BYTE),
CONTAINERCHANGECOUNT NUMBER(10),
COMPUTERNAME VARCHAR2(40 BYTE)
)
TABLESPACE INSITE_SUZ
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL
MONITORING;
CREATE INDEX HISTORYMAINLINE1 ON HISTORYMAINLINE
(HISTORYID, TXNID, REVERSALSTATUS, TXNDATEGMT, HISTORYMAINLINEID)
NOLOGGING
TABLESPACE INSITE_SUZ
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE INDEX HISTORYMAINLINE2 ON HISTORYMAINLINE
(HISTORYID)
NOLOGGING
TABLESPACE INSITE_SUZ
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE INDEX HISTORYMAINLINE3 ON HISTORYMAINLINE
(HISTORYID, TXNID, TXNDATEGMT)
NOLOGGING
TABLESPACE INSITE_SUZ
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE INDEX HISTORYMAINLINE4 ON HISTORYMAINLINE
(TXNID)
NOLOGGING
TABLESPACE INSITE_SUZ
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE INDEX HISTORYMAINLINE5 ON HISTORYMAINLINE
(INTEGRATIONTXNID)
NOLOGGING
TABLESPACE INSITE_SUZ
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE UNIQUE INDEX HISTORYMAINL483 ON HISTORYMAINLINE
(HISTORYMAINLINEID)
LOGGING
TABLESPACE INSITE_SUZ
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE OR REPLACE TRIGGER INTTRIGHML AFTER INSERT ON HISTORYMAINLINE FOR EACH ROW
DECLARE
/* ---------------------------------------------------------------------------
Description : Integration with HistoryMainline. Will enqueue message
into the respective queues.
Author : Benny.Chia
Date : 10 July 2006
Compile in : INSITEDEV
Called By : Nil
Call : INSITEQUE.IntHML.HMLEnQ()
Release Date :
Release Version : 3.4.0001
Release Notes for 3.4.0001:
1) First release version.
------------------------------------------------------------------------------- */
vErrorTag varchar2(255); -- for tracing at which point the program stop should an error occurs
vErrMsg VARCHAR2(255);
bEnQueue BOOLEAN:=FALSE;
-- AND NVL(IM.CallByCDOName, '$$$') = NVL(:New.CallByCDOName, '$$$')
-- AND NVL(IM.FromSpecName, '$$$') = NVL(:New.FromSpecName, '$$$')
-- AND NVL(IM.SpecName, '$$$') = NVL(:New.SpecName, '$$$')
-- AND NVL(IM.StepLogicName, '$$$') = NVL(:New.StepLogicName, '$$$');
BEGIN
vErrorTag := 'START';
--=====================
-- Check on corresponding Integration Point
--=====================
--dbms_output.put_line (':New.HistoryMainlineID=' || :New.HistoryMainlineID);
--dbms_output.put_line (':New.CDOName=' || :New.CDOName);
--dbms_output.put_line (':New.CDOTxnSequence=' || :New.CDOTxnSequence);
--dbms_output.put_line (':New.TxnDate=' || :New.TxnDate);
--dbms_output.put_line (':New.CallByCDOName=' || :New.CallByCDOName);
--dbms_output.put_line (':New.FromSpecName=' || :New.FromSpecName);
--dbms_output.put_line (':New.SpecName=' || :New.SpecName);
--dbms_output.put_line (':New.StepLogicName=' || :New.StepLogicName);
FOR c IN
(SELECT IP.IntegrationPointName,
IM.CallByCDOName,
IM.SpecName,
IM.FromSpecName,
IM.StepLogicName
FROM I_IntegrationMap IM,
I_IntegrationMapDetails IMD,
I_IntegrationPoint IP
WHERE IM.IntegrationMapID = IMD.IntegrationMapID
AND IMD.IntegrationPointID = IP.IntegrationPointID
AND IM.CDOName = :New.CDOName
ORDER BY IP.IntegrationPointName
)
LOOP
vErrorTag := 'Checking IntegrationPoint';
bEnQueue := FALSE;
--dbms_output.put ('IntegrationPointName=' || c.IntegrationPointName);
--dbms_output.put ('c.SpecName=' || c.SpecName);
--dbms_output.put (':new.SpecName=' || :new.SpecName);
-- Check CallByCDOName
IF (:New.CallByCDOName IS NULL OR c.CallByCDOName IS NULL) THEN
bEnQueue := TRUE;
ELSE
IF c.CallByCDOName = :New.CallByCDOName THEN
bEnQueue := TRUE;
END IF;
END IF;
-- Check SpecName
vErrorTag := 'Checking SpecName';
IF bEnQueue THEN
bEnQueue := FALSE;
IF (:New.SpecName IS NULL OR c.SpecName IS NULL) THEN
bEnQueue := TRUE;
ELSE
IF c.SpecName = :New.SpecName THEN
bEnQueue := TRUE;
END IF;
END IF;
END IF;
-- Check FromSpecName
vErrorTag := 'Checking FromSpecName';
IF bEnQueue THEN
bEnQueue := FALSE;
IF (:New.FromSpecName IS NULL OR c.FromSpecName IS NULL) THEN
bEnQueue := TRUE;
ELSE
IF c.FromSpecName = :New.FromSpecName THEN
bEnQueue := TRUE;
END IF;
END IF;
END IF;
-- Check StepLogicName
vErrorTag := 'Checking StepLogicName';
IF bEnQueue THEN
bEnQueue := FALSE;
IF (:New.StepLogicName IS NULL OR c.StepLogicName IS NULL) THEN
bEnQueue := TRUE;
ELSE
IF c.StepLogicName = :New.StepLogicName THEN
bEnQueue := TRUE;
END IF;
END IF;
END IF;
vErrorTag := 'Checking if need to enqueue';
IF bEnQueue THEN
--dbms_output.put ('=>Queue');
vErrorTag := 'Enqueuing into ' || c.IntegrationPointName;
IntHML.HMLEnQ
(pvIntPoint => c.IntegrationPointName,
pcHMLID => :New.HistoryMainlineID,
pvCallByCDOName => :New.CallByCDOName,
pvCDOName => :New.CDOName,
pnCDOTxnSequence => :New.CDOTxnSequence,
pvSpecName => :New.SpecName,
pvFromSpecName => :New.FromSpecName,
pvStepLogicName => :New.StepLogicName,
ptTxnDate => :New.TxnDate);
END IF;
--dbms_output.put_line ('');
END LOOP;
vErrorTag := 'SUCCESSFUL';
RETURN;
EXCEPTION
WHEN OTHERS THEN
vErrMsg := chr(10) || 'IntTrigHML Trigger error (' || vErrorTag || ') for ' || :New.HistoryMainlineID
|| ' - Original ORA' || TO_CHAR(SQLCODE, '999999')
|| chr(10) || SQLERRM;
IntGlobal.logTab ('IntTrigHML', SQLERRM, vErrMsg);
RAISE_APPLICATION_ERROR(-20008, vErrMsg);
END;
/
SHOW ERRORS;
CREATE SYNONYM INSITEINT_SUZ.HISTORYMAINLINE FOR HISTORYMAINLINE;
ALTER TABLE HISTORYMAINLINE ADD (
CONSTRAINT HISTORYMAINL483 PRIMARY KEY (HISTORYMAINLINEID)
USING INDEX
TABLESPACE INSITE_SUZ
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
));
GRANT SELECT ON HISTORYMAINLINE TO INSITEINT_SUZ;
这个表数据 一百多万条 |
|
楼主: Terry916
IP卡
狗仔卡
楼主
显身卡
发表于 2008-3-25 13:56
