SCH_REC_EXT_HEADERS
This table stores the product recall header information provided by the external sources such as manufacturers, distributors, and the regulatory authorities, who initiated or issued the recall. It also stores the header level attributes that are transformed by generative artificial intelligence, the attributes that are enriched post transformation, and the attributes that are proposed by the end user.
Details
-
Schema: FUSION
-
Object owner: SCH
-
Object type: TABLE
-
Tablespace: Default
Primary Key
| Name | Columns |
|---|---|
|
SCH_REC_EXT_HEADERS_PK |
EXT_SOURCE_HEADER_ID |
Columns
| Name | Datatype | Length | Precision | Not-null | Comments |
|---|---|---|---|---|---|
| EXT_SOURCE_HEADER_ID | NUMBER | 18 | Yes | Unique identifier of the source header in the external recall notification. | |
| RECALL_SOURCE_REFERENCE_ID | NUMBER | 18 | A Unique identifier assigned by External Source to a specific recall event used for tracking purposes. | ||
| STATUS | VARCHAR2 | 80 | Indicates the status of external recall notification. | ||
| RECALL_REFERENCE_NUMBER | VARCHAR2 | 60 | Recall reference number provided by the source who initiated the recall. | ||
| RECALL_REPORT_DATE | TIMESTAMP | Date that the External Source issued the enforcement report for the product recall. | |||
| RECALL_INITIATION_DATE | TIMESTAMP | Date on which recall notice is initiated by the source. | |||
| TRACK_START_DATE | DATE | Indicates the start date of distribution of the recalled product in the market. | |||
| TRACK_START_DATE_SUGG | DATE | Indicates the earliest start date of distribution among all the recalled products captured in the external recall notification. This value is derived by the enrichment process while capturing the recall notice. | |||
| TRACK_START_DATE_PROP | DATE | Indicates the user proposed start date of distribution of the recalled product in the market. | |||
| TRACK_END_DATE | DATE | Indicates the end date of distribution of the recalled product in the market. | |||
| TRACK_END_DATE_SUGG | DATE | Indicates the latest end date of distribution among all the recalled products captured in the external recall notification. This value is derived by the enrichment process while capturing the recall notice. | |||
| TRACK_END_DATE_PROP | DATE | Indicates the user proposed end date of distribution of the recalled product in the market. | |||
| RECALL_RECEIVED_FROM | VARCHAR2 | 360 | Indicates the firm name from where the recall notification is received. It can be a regulatory authority or a distributor or a manufacturer. | ||
| RECALLING_FIRM | VARCHAR2 | 360 | Indicates the firm name who has initiated the recall. It is a mandatory attribute to publish a recall notice. | ||
| RECALLING_FIRM_SUGG | VARCHAR2 | 360 | Indicates the name of the recalling firm derived by the enrichment process. | ||
| RECALLING_FIRM_PROP | VARCHAR2 | 360 | Indicates the proposed final value of the firm who has initiated the recall. | ||
| RECALL_SOURCE_TYPE | VARCHAR2 | 30 | Indicates the type of source who initiated the recall such as Manufacturer,Regulatory Authority etc. Values are retrieved from the seeded lookup type ORA_SCH_SOURCE_TYPE. | ||
| RECALL_REASON | VARCHAR2 | 4000 | Indicates the reasons for the recall notice. | ||
| RECALL_REASON_PROP | VARCHAR2 | 4000 | Indicates the proposed final value of the reason for the recall of the product. | ||
| RECALL_NOTICE_TYPE | VARCHAR2 | 360 | Indicates the classification of recall notice with values retrieved from the seeded lookup type ORA_SCH_NOTICE_TYPE. | ||
| RECALL_NOTICE_TYPE_PROP | VARCHAR2 | 360 | Indicates the proposed final value of the type of recall notice. | ||
| RECALL_CLSF_TYPE | VARCHAR2 | 80 | Indicates the classification of recall notice based on severity of the impact it has on consumers. These values are retrieved from the seeded lookup type ORA_SCH_CLASSIFICATION. | ||
| RECALL_CLSF_TYPE_PROP | VARCHAR2 | 80 | Indicates the proposed final value of the type of classification of the recall notice based on severity of the impact it has on consumers. | ||
| POTENTIAL_RISK | VARCHAR2 | 2000 | Free text where user can provide the details about the risk factors of the recalled product. | ||
| REPLACEMENT_TYPE | VARCHAR2 | 30 | Indicates the method by which the recalled parts can be disposed off. These values are retrieved from the seeded lookup type ORA_SCH_REPLACEMENT_TYPE. | ||
| RECALL_INSTRUCTION | VARCHAR2 | 4000 | Free text where user can provide some special instructions against replacement type. It is mandatory for Correction replacement type. | ||
| RECALL_INSTRUCTION_PROP | VARCHAR2 | 4000 | User enterable text where user can provide some special instructions against replacement type. It is mandatory for Correction replacement type. | ||
| BUSINESS_UNIT_ID | NUMBER | 18 | Business Unit unique identifier in which the recall notice is created. | ||
| MODE_OF_CAPTURE | VARCHAR2 | 30 | Indicates the method by which the recall notice is captured. The possible values are Regulatory authority API, E-mail, and Manually created. | ||
| RECALL_INTERNAL_API_ID | NUMBER | 18 | Unique Identifier of the registered external source's rest end point. | ||
| OBJECT_VERSION_NUMBER | NUMBER | 9 | Yes | Used to implement optimistic locking. This number is incremented every time that the row is updated. The number is compared at the start and end of a transaction to detect whether another session has updated the row since it was queried. | |
| REQUEST_ID | NUMBER | 18 | Enterprise Service Scheduler: indicates the request ID of the job that created or last updated the row. | ||
| JOB_DEFINITION_PACKAGE | VARCHAR2 | 900 | Enterprise Service Scheduler: indicates the package name of the job that created or last updated the row. | ||
| JOB_DEFINITION_NAME | VARCHAR2 | 100 | Enterprise Service Scheduler: indicates the name of the job that created or last updated the row. | ||
| CREATION_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the creation of the row. | ||
| CREATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who created the row. | |
| LAST_UPDATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who last updated the row. | |
| LAST_UPDATE_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the last update of the row. | ||
| LAST_UPDATE_LOGIN | VARCHAR2 | 32 | Who column: indicates the session login associated to the user who last updated the row. | ||
| POTENTIAL_RISK_PROP | VARCHAR2 | 2000 | User enterable text indicating the proposed final value of the risk factors associated with the recalled product. |
Foreign Keys
| Table | Foreign Table | Foreign Key Column |
|---|---|---|
| sch_rec_ext_lines | sch_rec_ext_headers | EXT_SOURCE_HEADER_ID |
| sch_rec_ext_serial_numbers | sch_rec_ext_headers | EXT_SOURCE_HEADER_ID |
| sch_rec_ext_lot_numbers | sch_rec_ext_headers | EXT_SOURCE_HEADER_ID |
Indexes
| Index | Uniqueness | Tablespace | Columns |
|---|---|---|---|
| SCH_REC_EXT_HEADERS_N1 | Non Unique | Default | RECALL_REFERENCE_NUMBER, RECALL_INTERNAL_API_ID |
| SCH_REC_EXT_HEADERS_U1 | Unique | Default | EXT_SOURCE_HEADER_ID |