DM_TABLE_RELATIONSHIPS



Column Section Top of Page


Table-level Detail

Description: DM_TABLE_RELATIONSHIPS
Definition: Reference table to store table relationships metadata
Table Type: REFERENCE





Column Detail - DM_TABLE_RELATIONSHIPS


Column Name Type Null? Definition
ALT_RE_PK_NAME VARCHAR(50) Y ALTERNATE ROOT ENTITY PK NAME
ALT_RE_PK_POSITION DOUBLE Y ALTERNATE ROOT ENTITY PK COLUMN POSITION
ALT_ROOT_ENTITY_ATTR VARCHAR(30) Y ALTERNATE ROOT ENTITY TABLE COLUMN NAME
ALT_ROOT_ENTITY_NAME VARCHAR(30) Y ALTERNATE ROOT ENTITY TABLE NAME
COLUMN_NAME VARCHAR(30) N COLUMN NAME PARTICIPATING IN THE RELATIONSHIP
DM_TABLE_RELATIONSHIPS_ID DOUBLE N PRIMARY KEY
DRR_FLAG DOUBLE Y FLAG to indicate if the row participates ( 1 ) or not ( -1 )
DRR_RELTN_TYPE VARCHAR(15) Y Typically used to note of an ALTERNATIVE relationship is needed/used
DRR_TABLE_NAME VARCHAR(30) Y the name of the DRR shadow table
DRR_TEXT VARCHAR(500) Y store note on this relationship (comment)
DRR_ZERO_PERSON_COL VARCHAR(30) Y When DRR_RELTN_TYPE is ZERO, this column is populated with the other person column name that will be processed by standard method.
DRR_ZERO_RESTORE_IND DOUBLE Y When DRR_RELTN_TYPE is ZERO, this column will be populated 1 or 0. 1 - Unrestrict must restore original column value.
EHI_FLAG DOUBLE Y FLAG value for indicating able/column is processed for EHI ( -1 = does not participate, 1 = activity table participant 2 = = reference table participant )
FK_NAME VARCHAR(30) Y for column in Foreign Key, this column will be populated with the Foreign Key name
FK_POSITION DOUBLE Y for column in Foreign Key, this column will be populated with the Foreign Key column position
OWNER VARCHAR(20) N TABLE OWNER
PE_NAME_COLUMN VARCHAR(50) Y for column has parent entity relationship, this column will be populated with column name that store parent table name. eg, PARENT_ENTITY_NAME
PE_NAME_COL_VALUE VARCHAR(50) Y for column has parent entity relationship, this column will be populated with parent table name. eg, PERSON
PK_NAME VARCHAR(30) Y for column in Primary Key, this column will be populated with the Primary Key name
PK_POSITION DOUBLE Y for column in Foreign Key, this column will be populated with the Foreign Key column position
P_COLUMN_NAME VARCHAR(30) Y store parent column name that has relationships with this column
P_PK_NAME VARCHAR(30) Y store parent table Primary Key name
P_PK_POSITION DOUBLE Y store parent table Primary Key column position
P_TABLE_NAME VARCHAR(30) Y store parent table name
RE_PK_NAME VARCHAR(50) Y store root parent's Primary Key name
RE_PK_POSITION DOUBLE Y store root parent's Primary Key position
ROOT_ENTITY_ATTR VARCHAR(30) Y store root parent column name
ROOT_ENTITY_NAME VARCHAR(30) Y Store root parent table name
TABLE_INSTANCE DOUBLE N table instance use for versioning. Should only have one max instance per table.
TABLE_NAME VARCHAR(30) N THE TABLE NAME
UPDT_APPLCTX DOUBLE N The application context number from the record info block. The UPDT family of columns are typically used for housekeeping and external system process and should never be depended on for solution specific logic.
UPDT_CNT DOUBLE N Set to 0 on insert. Incremented by 1 on update. Used to recognize update conflict where data in a row updated by one application is at risk of being lost by a second application attempting to update the row. The UPDT family of columns are typically used for housekeeping and external system process and should never be depended on for solution specific logic.
UPDT_DT_TM DATETIME N The date and time the row was last inserted or updated. The UPDT family of columns are typically used for housekeeping and external system process and should never be depended on for solution specific logic.
UPDT_ID DOUBLE N The person_id of the person from the personnel table (prsnl) that caused the last insert or update of the row in the table. The UPDT family of columns are typically used for housekeeping and external system process and should never be depended on for solution specific logic.
UPDT_TASK DOUBLE N The registered (assigned) task number for the process that inserted or updated the row. The UPDT family of columns are typically used for housekeeping and external system process and should never be depended on for solution specific logic.


DM_TABLE_RELATIONSHIPS_GTTP



Column Section Top of Page


Table-level Detail

Description: Global Temp table to facilitate load of DM_TABLE_RELATIONSHIPS
Definition: We will use this table as a temp table whose contents will later be loaded into DM_TABLE_RELATIONSHIPS table. So, this table needs to be identical to DM_TABLE_RELATIONSHIPS table.
Table Type: ACTIVITY





Column Detail - DM_TABLE_RELATIONSHIPS_GTTP


Column Name Type Null? Definition
ALT_RE_PK_NAME VARCHAR(30) Y See column doc content in DM_TABLE_RELATIONSHIPS
ALT_RE_PK_POSITION DOUBLE Y See column doc content in DM_TABLE_RELATIONSHIPS
ALT_ROOT_ENTITY_ATTR VARCHAR(30) Y See column doc content in DM_TABLE_RELATIONSHIPS
ALT_ROOT_ENTITY_NAME VARCHAR(30) Y See column doc content in DM_TABLE_RELATIONSHIPS
COLUMN_NAME VARCHAR(30) Y See column doc content in DM_TABLE_RELATIONSHIPS
DM_TABLE_RELATIONSHIPS_ID DOUBLE Y PRIMARY KEY. Does not use sequence. ID value shipped from ADM table in RVADM1
DRR_FLAG DOUBLE Y See column doc content in DM_TABLE_RELATIONSHIPS
DRR_RELTN_TYPE VARCHAR(15) Y See column doc content in DM_TABLE_RELATIONSHIPS
DRR_TABLE_NAME VARCHAR(30) Y See column doc content in DM_TABLE_RELATIONSHIPS
DRR_TEXT VARCHAR(500) Y See column doc content in DM_TABLE_RELATIONSHIPS
DRR_ZERO_PERSON_COL VARCHAR(30) Y See column doc content in DM_TABLE_RELATIONSHIPS
DRR_ZERO_RESTORE_IND DOUBLE Y See column doc content in DM_TABLE_RELATIONSHIPS
EHI_FLAG DOUBLE Y FLAG value for indicating able/column is processed for EHI ( -1 = does not participate, 1 = activity table participant 2 = = reference table participant )
FK_NAME VARCHAR(30) Y See column doc content in DM_TABLE_RELATIONSHIPS
FK_POSITION DOUBLE Y See column doc content in DM_TABLE_RELATIONSHIPS
OWNER VARCHAR(20) Y TABLE OWNER
PE_NAME_COLUMN VARCHAR(50) Y See column doc content in DM_TABLE_RELATIONSHIPS
PE_NAME_COL_VALUE VARCHAR(50) Y See column doc content in DM_TABLE_RELATIONSHIPS
PK_NAME VARCHAR(30) Y See column doc content in DM_TABLE_RELATIONSHIPS
PK_POSITION DOUBLE Y See column doc content in DM_TABLE_RELATIONSHIPS
P_COLUMN_NAME VARCHAR(30) Y See column doc content in DM_TABLE_RELATIONSHIPS
P_PK_NAME VARCHAR(30) Y See column doc content in DM_TABLE_RELATIONSHIPS
P_PK_POSITION DOUBLE Y See column doc content in DM_TABLE_RELATIONSHIPS
P_TABLE_NAME VARCHAR(30) Y See column doc content in DM_TABLE_RELATIONSHIPS
RE_PK_NAME VARCHAR(30) Y See column doc content in DM_TABLE_RELATIONSHIPS
RE_PK_POSITION DOUBLE Y See column doc content in DM_TABLE_RELATIONSHIPS
ROOT_ENTITY_ATTR VARCHAR(30) Y See column doc content in DM_TABLE_RELATIONSHIPS
ROOT_ENTITY_NAME VARCHAR(30) Y See column doc content in DM_TABLE_RELATIONSHIPS
TABLE_INSTANCE DOUBLE Y See column doc content in DM_TABLE_RELATIONSHIPS
TABLE_NAME VARCHAR(30) Y See column doc content in DM_TABLE_RELATIONSHIPS
UPDT_APPLCTX DOUBLE Y The application context number from the record info block. The UPDT family of columns are typically used for housekeeping and external system process and should never be depended on for solution specific logic.
UPDT_CNT DOUBLE Y Set to 0 on insert. Incremented by 1 on update. Used to recognize update conflict where data in a row updated by one application is at risk of being lost by a second application attempting to update the row. The UPDT family of columns are typically used for housekeeping and external system process and should never be depended on for solution specific logic.
UPDT_DT_TM DATETIME Y The date and time the row was last inserted or updated. The UPDT family of columns are typically used for housekeeping and external system process and should never be depended on for solution specific logic.
UPDT_ID DOUBLE Y The person_id of the person from the personnel table (prsnl) that caused the last insert or update of the row in the table. The UPDT family of columns are typically used for housekeeping and external system process and should never be depended on for solution specific logic.
UPDT_TASK DOUBLE Y The registered (assigned) task number for the process that inserted or updated the row. The UPDT family of columns are typically used for housekeeping and external system process and should never be depended on for solution specific logic.


DRR_CORE_PLSQL_LOG



Column Section Top of Page


Table-level Detail

Description: DRR_CORE_PLSQL_LOG
Definition: stores execution log for DRR core process
Table Type: ACTIVITY





Column Detail - DRR_CORE_PLSQL_LOG


Column Name Type Null? Definition
ACTION VARCHAR(100) Y store PLSQL ACTION START/COMPLETE/FAILED sys_context('userenv','action')
BACK_TRACE VARCHAR(4000) Y store dbms_utility.format_error_backtrace
CALL_STACK VARCHAR(4000) Y store dbms_utility.format_call_stack
CLIENT_INFO VARCHAR(64) Y
DRR_CORE_PLSQL_LOG_ID DOUBLE N PRIMARY KEY
ERROR_STACK VARCHAR(4000) Y store error code from dbms_utility.format_error_stack
LINE_NO VARCHAR(100) Y line_no where error occured
MODULE VARCHAR(100) N store PLSQL MODULE sys_context('userenv','module') PROC/FUNC NAME
PROCESS_INFO VARCHAR(255) Y
SESSIONID DOUBLE Y store process sessionid to_number(sys_context('USERENV','SESSIONID'))
SID DOUBLE Y store process SID to_number(sys_context('userenv','sid'))
TIME_STAMP_TS DATETIME(6) Y store logging timestamp
UPDT_APPLCTX DOUBLE N The application context number from the record info block. The UPDT family of columns are typically used for housekeeping and external system process and should never be depended on for solution specific logic.
UPDT_CNT DOUBLE N Set to 0 on insert. Incremented by 1 on update. Used to recognize update conflict where data in a row updated by one application is at risk of being lost by a second application attempting to update the row. The UPDT family of columns are typically used for housekeeping and external system process and should never be depended on for solution specific logic.
UPDT_DT_TM DATETIME N The date and time the row was last inserted or updated. The UPDT family of columns are typically used for housekeeping and external system process and should never be depended on for solution specific logic.
UPDT_ID DOUBLE N The person_id of the person from the personnel table (prsnl) that caused the last insert or update of the row in the table. The UPDT family of columns are typically used for housekeeping and external system process and should never be depended on for solution specific logic.
UPDT_TASK DOUBLE N The registered (assigned) task number for the process that inserted or updated the row. The UPDT family of columns are typically used for housekeeping and external system process and should never be depended on for solution specific logic.


DRR_CUSTOM_PLSQL



Column Section Top of Page


Table-level Detail

Description: DRR_CUSTOM_PLSQL
Definition: Reference table store custom PLSQL function
Table Type: REFERENCE





Column Detail - DRR_CUSTOM_PLSQL


Column Name Type Null? Definition
CHILD_TABLE VARCHAR(30) Y child table name
DRR_CUSTOM_PLSQL_ID DOUBLE N PRIMARY KEY.
EXEC_ORDER DOUBLE N execution order 0 -- execute before core process, 99 - execute after core process
EXEC_STMT VARCHAR(200) N statement execute for custom process
PARENT_TABLE VARCHAR(30) Y parent table name
PROCESS_NAME VARCHAR(50) N e.g. RESTRICT, DELETE_RESTRICT, UNRESTRICT, DELETE. use code_set
UPDT_APPLCTX DOUBLE N The application context number from the record info block. The UPDT family of columns are typically used for housekeeping and external system process and should never be depended on for solution specific logic.
UPDT_CNT DOUBLE N Set to 0 on insert. Incremented by 1 on update. Used to recognize update conflict where data in a row updated by one application is at risk of being lost by a second application attempting to update the row. The UPDT family of columns are typically used for housekeeping and external system process and should never be depended on for solution specific logic.
UPDT_DT_TM DATETIME N The date and time the row was last inserted or updated. The UPDT family of columns are typically used for housekeeping and external system process and should never be depended on for solution specific logic.
UPDT_ID DOUBLE N The person_id of the person from the personnel table (prsnl) that caused the last insert or update of the row in the table. The UPDT family of columns are typically used for housekeeping and external system process and should never be depended on for solution specific logic.
UPDT_TASK DOUBLE N The registered (assigned) task number for the process that inserted or updated the row. The UPDT family of columns are typically used for housekeeping and external system process and should never be depended on for solution specific logic.


DRR_DELETE_LOG



Column Section Top of Page


Table-level Detail

Description: DRR_DELETE_LOG
Definition: Activity table that stores DRR delete DML statement for persons. It will use drr_table_stmt/drr_custom_plsql to gernate dml statements for persons in dm_process_queue
Table Type: ACTIVITY





Column Detail - DRR_DELETE_LOG


Column Name Type Null? Definition
AUDIT_COUNT DOUBLE Y store number of rows found during audit
AUDIT_DURATION DOUBLE Y store how long delete takes from start to complete or fail in seconds
AUDIT_END_DT_TM DATETIME Y ending audit datetime
AUDIT_START_DT_TM DATETIME Y starting audit datetime
AUDIT_STATUS VARCHAR(50) Y store audit status like IN PROCESS, SUCCESS, FAILED
CHILD_COL_STR VARCHAR(200) Y child column names
CHILD_TABLE VARCHAR(30) Y child table name
CUSTOM_PLSQL_IND DOUBLE N indicator for custom delete type. 1 - zero type. 2 - custom pl/sql inserted by solution team.
CUST_DELETE_STMT VARCHAR(100) Y statement execute for custom delete
DELETE_COUNT DOUBLE Y store number of rows being deleted
DELETE_DURATION DOUBLE Y store how long delete takes from start to complete or fail in seconds
DELETE_END_DT_TM DATETIME Y ending delete datetime
DELETE_ORDER DOUBLE Y delete execution order
DELETE_START_DT_TM DATETIME Y starting delete datetime
DELETE_STATUS VARCHAR(50) Y store delete status like IN PROCESS, SUCCESS, FAILED
DELETE_STMT VARCHAR(4000) Y statement execute for delete
DRR_CHILD_TABLE VARCHAR(30) Y drr shadow child table name
DRR_DELETE_LOG_ID DOUBLE N PRIMARY KEY.
DRR_IDENT_ID DOUBLE Y Unique sequence generated to identify each person being restricted. In combine scenario, one unique value is used for both combined away person and the combined to person.
DRR_PARENT_TABLE VARCHAR(30) Y drr shadow parent table name
DRR_PROCESS_PLAN_ID DOUBLE N drr_process_plan_id generated for delete on DRR_PROCESS_PLAN table.
DRR_RESTRICT_PLAN_ID DOUBLE N drr_process_plan_id generated for restrict on DRR_PROCESS_PLAN table.
ERROR_MSG VARCHAR(4000) Y store error message
PARENT_TABLE VARCHAR(30) Y parent table name
PAR_COL_STR VARCHAR(200) Y parent column names
PERSON_ID DOUBLE N person_id being delete.
PRIMARY_PERSON_ID DOUBLE Y When person_id column stores combined away person, this column will store the final combined to person_id.
RETRY_COUNT DOUBLE Y number of times retried
SELECT_STMT VARCHAR(4000) Y statement execute for audit
SESSIONID DOUBLE Y store process sessionid to_number(sys_context('USERENV','SESSIONID'))
UPDT_APPLCTX DOUBLE N The application context number from the record info block. The UPDT family of columns are typically used for housekeeping and external system process and should never be depended on for solution specific logic.
UPDT_CNT DOUBLE N Set to 0 on insert. Incremented by 1 on update. Used to recognize update conflict where data in a row updated by one application is at risk of being lost by a second application attempting to update the row. The UPDT family of columns are typically used for housekeeping and external system process and should never be depended on for solution specific logic.
UPDT_DT_TM DATETIME N The date and time the row was last inserted or updated. The UPDT family of columns are typically used for housekeeping and external system process and should never be depended on for solution specific logic.
UPDT_ID DOUBLE N The person_id of the person from the personnel table (prsnl) that caused the last insert or update of the row in the table. The UPDT family of columns are typically used for housekeeping and external system process and should never be depended on for solution specific logic.
UPDT_TASK DOUBLE N The registered (assigned) task number for the process that inserted or updated the row. The UPDT family of columns are typically used for housekeeping and external system process and should never be depended on for solution specific logic.