Thanks, I will create your table in my db and try it locally : Here it is: CREATE TABLE CLIENT_ASSET_ACCT : ( : CLIENT_ID_K NUMBER(9) NOT NULL, : EFF_DTE DATE NOT NULL, : CREATE_TS TIMESTAMP(6) NOT NULL, : SET_ID NUMBER(9) NOT NULL, : ID NUMBER(9), : ASSET_ID NUMBER(9), : END_DTE DATE, : EXISTS_FLG VARCHAR2(1 CHAR) NOT NULL, : ASSET_ACCT_TYP_CD_CODE_K VARCHAR2(6 CHAR), : LAST_UPDT_UID_NAM VARCHAR2(8 CHAR) NOT NULL, : LAST_UPDT_TS TIMESTAMP(6) NOT NULL : ) : TABLESPACE HM_CORE_TBL_TB : PCTUSED 0 : PCTFREE 10 : INITRANS 1 : MAXTRANS 255 : STORAGE ( : INITIAL 72K : MINEXTENTS 1 : MAXEXTENTS 2147483645 : PCTINCREASE 0 : BUFFER_POOL DEFAULT : ) : LOGGING : NOCOMPRESS : NOCACHE : PARALLEL ( DEGREE 2 INSTANCES 3 ) : MONITORING; : COMMENT ON TABLE CLIENT_ASSET_ACCT IS 'Parent table that stores client asset : account data, e.g., data on accounts for checking, savings, etc. Table : employs a "date series using sets" pattern as a client can have : multiple concurrent asset accounts. To support joint ownership, i.e. : assigning multiple clients to a given asset, the same asset can exist for : multiple clients, i.e. have the same ASSET_ID. On the Account workflow : step window, when an asset is added for a client, then that asset is added : for the client and any other client that was tagged as a joint owner. To : make a client no longer a joint owner of an asset, the EXISTS_FLG is set : to "N ". Child table of CLIENT.'; : COMMENT ON COLUMN CLIENT_ASSET_ACCT.CREATE_TS IS 'System timestamp of when : the row was inserted. Included to support version the data on this : table.'; : COMMENT ON COLUMN CLIENT_ASSET_ACCT.SET_ID IS 'Set identifier. Used on tables : that store concurrent multiple instance data. This ID ties together all : versions of a particular data instance, i.e. initial row and change : history rows.'; : COMMENT ON COLUMN CLIENT_ASSET_ACCT.ID IS 'Client asset account ID. System : generated unique identifier. This is propagated to this tables child : tables to serve as a parent - child link.'; : COMMENT ON COLUMN CLIENT_ASSET_ACCT.ASSET_ID IS 'System generated identifier : to support joint ownership of security assets. Multiple clients can own a : given asset. That asset data is duplicated for each client owner. This ID : ties this relationship together.'; : COMMENT ON COLUMN CLIENT_ASSET_ACCT.END_DTE IS 'Date upon which client : stopped having the account.'; : COMMENT ON COLUMN CLIENT_ASSET_ACCT.EXISTS_FLG IS 'Flag indicating if the : asset account instance exists for the client..'; : COMMENT ON COLUMN CLIENT_ASSET_ACCT.ASSET_ACCT_TYP_CD_CODE_K IS 'Asset : account type - pointer to the Reference Data table ASSET_ACCT_TYP_CD.'; : COMMENT ON COLUMN CLIENT_ASSET_ACCT.LAST_UPDT_UID_NAM IS 'User ID of the user : who last updated the row.'; : COMMENT ON COLUMN CLIENT_ASSET_ACCT.LAST_UPDT_TS IS 'Timestamp of when the : row was last updated.'; : COMMENT ON COLUMN CLIENT_ASSET_ACCT.CLIENT_ID_K IS 'Associated client ID. : Foreign key to CLIENT.ID.'; : COMMENT ON COLUMN CLIENT_ASSET_ACCT.EFF_DTE IS 'Date upon which the system : recognizes the data to be effective. This date is set by the application : framework.'; : CREATE UNIQUE INDEX PK_CLIENT_ASSET_ACCT ON CLIENT_ASSET_ACCT : (CLIENT_ID_K, EFF_DTE, CREATE_TS, SET_ID) : LOGGING : TABLESPACE USERS : PCTFREE 10 : INITRANS 2 : MAXTRANS 255 : STORAGE ( : INITIAL 72K : MINEXTENTS 1 : MAXEXTENTS 2147483645 : PCTINCREASE 0 : BUFFER_POOL DEFAULT : ) : NOPARALLEL; : CREATE UNIQUE INDEX UK_CLIENT_ASSET_ACCT ON CLIENT_ASSET_ACCT : (ID, CLIENT_ID_K) : LOGGING : TABLESPACE USERS : PCTFREE 10 : INITRANS 2 : MAXTRANS 255 : STORAGE ( : INITIAL 72K : MINEXTENTS 1 : MAXEXTENTS 2147483645 : PCTINCREASE 0 : BUFFER_POOL DEFAULT : ) : NOPARALLEL;
|