+-----+----------------------------------------------------------------+
| #   | **Action**                                                     |
| # S |                                                                |
| tep |                                                                |
+-----+----------------------------------------------------------------+
| **1 | **Common Data Element (CDE) Librarian creates comma delimited  |
| .** | flat file.**                                                   |
+-----+----------------------------------------------------------------+
|     | A.  Use the caDSR browser and the "Download Data Elements to   |
|     |     Excel" option to create an Excel file.                     |
|     |                                                                |
|     | B.  Add or remove columns to meet the required format.         |
|     |                                                                |
|     | C.  Save the file with the file name "NCI_CDE.DAT" using the   |
|     |     CVS (comma delimited) option.                              |
+-----+----------------------------------------------------------------+
| **2 | **CDE Loader Technician prepares staging tables.**             |
| .** |                                                                |
+-----+----------------------------------------------------------------+
|     | A.  Reopen the file using a text editor to ensure that         |
|     |     extraneous carriage returns are not embedded in the file   |
|     |     and that blank records are removed.                        |
|     |                                                                |
|     | B.  Use the script "Archive_Stg_Tables.sql" to copy any        |
|     |     existing records from the staging tables to the archive    |
|     |     tables.                                                    |
|     |                                                                |
|     | C.  Use the script "Truncate_Stg_Tables.sql" to clean the      |
|     |     staging tables.                                            |
+-----+----------------------------------------------------------------+
| **3 | # CDE Loader Technician loads staging tables.                  |
| .** |                                                                |
+-----+----------------------------------------------------------------+
|     | A.  The CDE Loading Technician will load the staging table     |
|     |     "NCI_CDE" using SQL\*Loader and the control file           |
|     |     "load_nci_cde.ctl".                                        |
|     |                                                                |
|     | B.  Use normal SQL\*Loader procedures to populate the NCI_CDE  |
|     |     table.                                                     |
|     |                                                                |
|     | C.  Check the files "nci_cde.bad", "nci_cde.dsc" and           |
|     |     "nci_cde.log" for errors (a successful load will only      |
|     |     produce the log file "nci_cde.log" and contain no errors). |
+-----+----------------------------------------------------------------+
| **4 | # CDE Loader Technician processes CDE file.                    |
| .** |                                                                |
+-----+----------------------------------------------------------------+
|     | A.  Check the Question Names in the NCI_CDE table to ensure    |
|     |     that they meet the 20-character limit set by the clinical  |
|     |     data management application. Use the SQL script            |
|     |     "Derive_CDE.sql" to perform this check.                    |
|     |                                                                |
|     | B.  Remove all duplicates within the staging tables. Use the   |
|     |     function "Filter_NCI_CDE_Dupes.Filter_NCI_CDE_Dupes_Main"  |
|     |     to perform this check and generate a report.               |
+-----+----------------------------------------------------------------+
| **5 | # CDE Lo                                                       |
| .** | ader Technician loads discrete value groups (DVGs) and values. |
+-----+----------------------------------------------------------------+
|     | A.  Use the package "CDE2DVGS" and the Get_Data function to    |
|     |     extract data from the primary staging tables into the DVG  |
|     |     specific staging tables.                                   |
|     |                                                                |
|     | B.  Use the "MIG_DATA" function to migrate the DVG to the      |
|     |     Global Library.                                            |
|     |                                                                |
|     | C.  Use the package "CDE2DVG_VALUES" to migrate the values.    |
|     |     (This package is identical to "CDE2DVG" in that the data   |
|     |     is first migrated to the DVG Values specific staging       |
|     |     tables by use of the "Get_Data" function, and finally      |
|     |     migrated to the GLIB by use of the "MIG_DATA" function).   |
+-----+----------------------------------------------------------------+
| **6 | # CDE Librarian activates DVGs and load questions.             |
| .** |                                                                |
+-----+----------------------------------------------------------------+
|     | A.  Set the DVGs and their values to "Active" in Oracle        |
|     |     Clinical.                                                  |
|     |                                                                |
|     | B.  Use the "CDE2QUESTIONS" package and the "Get_Data"         |
|     |     function to migrate the question data from the primary     |
|     |     staging tables to the specific staging tables.             |
|     |                                                                |
|     | C.  Migrate the questions to the GLIB by using the procedure   |
|     |     "Mig_Data".                                                |
+-----+----------------------------------------------------------------+
| **7 | # CDE Loader Technician generates data load report.            |
| .** |                                                                |
+-----+----------------------------------------------------------------+
|     | A.  Run the script "Check_Batch.sql" to produce a report of    |
|     |     the CDEs that were loaded.                                 |
+-----+----------------------------------------------------------------+
| **8 | # CDE Loader Technician resolves partial CDE load.             |
| .** |                                                                |
+-----+----------------------------------------------------------------+
|     | A.  Identify the error that caused the partial load and        |
|     |     correct the problem.                                       |
|     |                                                                |
|     | B.  Verify correct data files are in the staging tables.       |
|     |                                                                |
|     | C.  Repeat steps 4 through 7.                                  |
+-----+----------------------------------------------------------------+
| **9 | # CDE Loader Technician resolves errors and reloads CDE file.  |
| .** |                                                                |
+-----+----------------------------------------------------------------+
|     | A.  Identify the error that caused the failed data load and    |
|     |     correct the problem.                                       |
|     |                                                                |
|     | B.  Verify correct data files are in the staging tables.       |
|     |                                                                |
|     | C.  Repeat steps 4 through 7.                                  |
+-----+----------------------------------------------------------------+
| *   | # CDE Loader Technician backs out erroneous CDE data.          |
| *10 |                                                                |
| .** |                                                                |
+-----+----------------------------------------------------------------+
|     | A.  Protocol builder or CDE Loader technician will inform Help |
|     |     Desk or CDE Loader Technician that incorrect data was      |
|     |     loaded.                                                    |
|     |                                                                |
|     | B.  The Technician will receive authorization from NCIB        |
|     |     Project Director to "Obsolete" the records from C3Dl via   |
|     |     e-mail.                                                    |
|     |                                                                |
|     | C.  The technician will mark the records as "Obsolete" in the  |
|     |     clinical data management application.                      |
|     |                                                                |
|     | D.  The technician will correct the data and reload per steps  |
|     |     1 through 7.                                               |
+-----+----------------------------------------------------------------+