FPDS Migration Design Document

FPDS Migration Design Document

FPDS-NG

Migration Design Document

Prepared for

General Services Administration

Prepared by

Global Computer Enterprises, Inc.

10780 Parkridge Blvd., Suite 300

Reston, VA 20191

September 17, 2003February 19, 2004

FPDS Migration Design Document

Table of Contents

SectionPage

1Introduction

1.1Reference Data

1.2Transaction Data

2Pre-Migration Activities

2.1Backup

3Database Migration

3.1Reference Data Merge

3.1.1Reference Tables

3.1.2Create Reference Tables

3.1.2.1DOD_CLAIMANT

3.1.2.2PROGRAM_SYSTEM_EQUIPMENT

3.1.2.3MILITARY_DEPARTMENTS

3.1.2.4EPA_PRODS

3.1.2.5ETHNIC_OWNED_BUSINESS

3.1.2.6FAIR_OPPORTUNITY

3.1.2.7HUBZONE_PROGRAM

3.1.2.8SIC

3.1.2.9UNITED_STATES_REGIONS

3.1.3Import Reference Tables

3.1.3.1COUNTRY

3.1.3.2NAICS Tables

3.1.3.3DEPARTMENT

3.1.3.4PSC

3.1.3.5AGENCY

3.1.3.6SIC

3.1.3.7STATE_CODE

3.1.3.8PLACE_OF_PERFORMANCE

3.1.3.9GOVERNMENT_OFFICE

3.2Transaction Data Merge

3.2.1Transaction Tables

4Table Mappings

4.1Staging Table Structure

4.2Individual Contract Action Report (ICAR) tables 1998 – Present Mapping

4.3Individual Contract Action Report (ICAR) tables 1979 – 1989 Mapping

4.4Individual Contract Action Report (ICAR) tables 1990 – 1997 Mapping

4.5Staging Table to XML Component Mapping

4.5.1Award (original and reconstructed base)

4.5.2IDV (original and reconstructed)

5Data Analysis

5.1Data Issues and Resolutions

5.1.1Migration Data Issues all fiscal years

5.2Breakdown of documents by Fiscal Year

5.2.1Fiscal Year 1998-2003

5.2.2Fiscal Year 1997

5.2.3Fiscal Year 1996

5.2.4Fiscal Year 1995

5.2.5Fiscal Year 1994

5.2.6Fiscal Year 1993

5.2.7Fiscal Year 1992

5.2.8Fiscal Year 1991

5.2.9Fiscal Year 1990

6Migration

6.1Migration Overview

6.2Reference Data Migration

6.2.1Import

6.3ICAR Migration

6.3.1Initial Load to the Staging Table

6.3.2Migration Translator Service

7Implementation

7.1Class Diagrams

7.2Java Classes

7.2.1ContractLoader

7.2.2Package

7.2.3API’s

7.2.4MigrationLoader

7.2.5Package

7.2.6API’s

7.2.7MigrationAward

7.2.8Package

7.2.9API’s

7.2.10MigrationIDV

7.2.11Package

7.2.12API’s

7.2.13MigrationContractVendorInfo

7.2.14Package

7.2.15API’s

8XML Schema (XSD)

8.1Migration Mapping XML file

9FPDS-NG Web Services

9.1Create for Award

9.2Create for IDV

10Disposition

10.1Archive Database (1979-1997)

10.2Current Prouction Database (1978-2003)

1Introduction...... 1

1.1Reference Data...... 1

1.2Transaction Data...... 1

2Pre-Migration Activities...... 1

2.1Backup...... 1

3Database Migration...... 1

3.1Reference Data Merge...... 1

3.1.1Reference Tables...... 1

3.1.2Create Reference Tables...... 5

3.1.2.1DOD_CLAIMANT...... 5

3.1.2.2PROGRAM_SYSTEM_EQUIPMENT...... 6

3.1.2.3MILITARY_DEPARTMENTS...... 7

3.1.2.4EPA_PRODS...... 7

3.1.2.5ETHNIC_OWNED_BUSINESS...... 8

3.1.2.6FAIR_OPPORTUNITY...... 9

3.1.2.7HUBZONE_PROGRAM...... 10

3.1.2.8SIC...... 10

3.1.2.9UNITED_STATES_REGIONS...... 11

3.1.3Import Reference Tables...... 12

3.1.3.1COUNTRY...... 12

3.1.3.2NAICS Tables...... 13

3.1.3.3DEPARTMENT...... 13

3.1.3.4PSC...... 14

3.1.3.5AGENCY...... 15

3.1.3.6SIC...... 16

3.1.3.7STATE_CODE...... 17

3.1.3.8PLACE_OF_PERFORMANCE...... 17

3.1.3.9GOVERNMENT_OFFICE...... 18

3.2Transaction Data Merge...... 19

3.2.1Transaction Tables...... 19

4Table Mappings...... 20

4.1Staging Table Structure...... 20

4.2Individual Contract Action Report (ICAR) tables 1998 – Present Mapping...... 26

4.3Individual Contract Action Report (ICAR) tables 1979 – 1989 Mapping...... 35

4.4Individual Contract Action Report (ICAR) tables 1990 – 1997 Mapping...... 40

4.5Staging Table to XML Component Mapping...... 42

4.5.1Award (original and reconstructed base)...... 42

4.5.2IDV (original and reconstructed)...... 52

5Data Analysis...... 60

5.1Data Issues and Resolutions...... 60

5.1.1Migration Data Issues all fiscal years...... 60

5.2Breakdown of documents by Fiscal Year...... 73

5.2.1Fiscal Year 1998-2003...... 75

5.2.2Fiscal Year 1997...... 75

5.2.3Fiscal Year 1996...... 76

5.2.4Fiscal Year 1995...... 77

5.2.5Fiscal Year 1994...... 77

5.2.6Fiscal Year 1993...... 78

5.2.7Fiscal Year 1992...... 79

5.2.8Fiscal Year 1991...... 79

5.2.9Fiscal Year 1990...... 80

6Migration...... 80

6.1Migration Overview...... 80

6.2Reference data Migration...... 81

6.2.1Import...... 81

6.3ICAR Migration...... 82

6.3.1Initial Load to the Staging Table...... 82

6.3.2Migration Translator Service...... 82

7Implementation...... 82

7.1Class Diagrams...... 82

7.2Java Classes...... 82

7.2.1ContractLoader...... 82

7.2.2Package...... 83

7.2.3API’s...... 83

7.2.4MigrationLoader...... 84

7.2.5Package...... 84

7.2.6API’s...... 84

7.2.7MigrationAward...... 87

7.2.8Package...... 87

7.2.9API’s...... 87

7.2.10MigrationIDV...... 88

7.2.11Package...... 88

7.2.12API’s...... 89

7.2.13MigrationContractVendorInfo...... 90

7.2.14Package...... 90

7.2.15API’s...... 90

8XML Schema (XSD)...... 90

8.1Migration Mapping XML file...... 90

9FPDS-NG Web Services...... 194

9.1Create for Award...... 194

9.2Create for IDV...... 194

10Disposition...... 195

10.1Archive Database (1979-1997)...... 195

10.2Current Prouction Database (1978-2003)...... 195

1

FPDS Migration Design Document

1Introduction

The objective of FPDS Migration is to migrate existing data into the new FPDS-NG database. There are two types of data that will be migrated, Reference Data and Transaction Data.

1.1Reference Data

Reference data refers to data that is used to select and/or validate column input.

1.2Transaction Data

There are two specific types of transaction data that will be migrated:

• Individual Contract Action Report (ICAR) – will create Award Contract.
• Federal Schedule (FEDSCHED) – will create IDV Contract.

2Pre-Migration Activities

2.1Backup

A backup of databases that contain both current information and archive information will be performed.

3Database Migration

3.1Reference Data Merge

All necessary reference data will be migrated to the new FPDS-NG database schema.

3.1.1Reference Tables

The following table contains reference a list of reference tables in FPDS. These are used to populate drop-down data lists or show valid values for columns.

Existing Table Name / Table Information Description / New Table Name Or Comments / FPDS-NG Action
AASA / Assistance and Advisory / Contains only Y or N not longer needed. / Remove
BUNDLING / Consolidation of Contracts / Contains only Y or N not longer needed. / Remove
CAUTH / Contracting Authority / REASON_NOT_COMPETED
Create using new FPDS-NG values / Create
CIA / Commercial Item Acquisition / Contains only Y or N not longer needed. / Remove
CICA / CICA Applicability / REASON_NOT_COMPETED
COMPETITIVE_PROCEDURES
Create using new FPDS-NG values. / Create
DEMTEST / Demonstration Program / Contains only Y or N not longer needed. / Remove
DODCMNT / DoOD Claimant / DOD_CLAIMANT / Create
DODSE / DOoD Weapons or Systems / PROGRAM_SYSTEM_EQUIPMENT / Create
DOD_SERVICE / Department of the Military / MILITARY_DEPARTMENTS / Create
EMSMALL / Emerging Small Business / Contains only Y or N not longer needed. / Remove
SMSMALLAWARD / Emerging Small Business Reserve Award / Contains only Y or N not longer needed. / Remove
EPA_CLAUSES / EPP Recovered Material Clauses / RECOVERED_MATERIAL_CLAUSES
Create using new FPDS-NG values. / Create
EPA_PRODUCTS / Use of EPA Designated Product / EPA_PRODS / Create
ETHNIC / Ethnic Owned Business / ETHNIC_OWNED_BUSINESS / Create
EXCOMP / Extent Competed / EXTENT_COMPETED
Create using new FPDS-NG values. / Create
FAIR_OP / Multiple Award Contract Fair Opportunity / FAIR_OPPORTUNITY / Create
FRGNGOV / Foreign Government Contract / Contains only Y or N not longer needed. / Remove
FUND_AG_ACT / Funding Agency, Clinger-Cohen Act / Contains only Y or N not longer needed. / Remove
FUND_AG_COM / Funding Agency Commercial Item Category / IT_COMMERCIAL_ITEM_CATEGORY
Create using new FPDS-NG values. / Create
FUND_AG_REASON / Funding Reason / REASON_FOR_PURCHASE
Create using new FPDS-NG values. / Create
HUB_PROG / Hubzone Program / HUBZONE_PROGRAM / Create
HUB_SBC / Hubzone Small Business Concerns / Values being used from CCR / Old
KCA / Kind of Contract Action / CONTRACT_ACTION
Create using new FPDS-NG values. / Create
LABSTAT / Subject to Labor Statues / Not needed in FPDS-NG, this is now mapped to as separate Yes or No columns. / Remove
MYC / Multi Year Contract / Contains only Y or N not longer needed. / Remove
NUMOFFR / Number of Offers / Not needed in FPDS-NG, we now store the number of offers as a number. / Remove
PBSC / Performance Based Service Contract / Contains only Y or N not longer needed. / Remove
PPROG / Other Programs / REASON_NOT_COMPETED
COMPETITIVE_PROCEDURES
Create using new FPDS-NG values / Create
SDB_BREAKOUT / Not needed in FPDS-NG / Remove
SDB_PROG / Small Business Concerns / Values being used from CCR / Old
SIZESMALL / Size of the Small Business / Values being used from CCR / Old
SMALLDIS / Small Disadvantaged / Values being used from CCR / Old
SPA / Synopsis / FPDS-NG values for Synopsis are Y and N. / Remove
SPLAN / Subcontracting Plan / SUBCONTRACING_PLAN
Create using new FPDS-NG values. / Create
SPROC / Solicitation Procedure / COMPETITIVE_PROCEDURES.
Create using new FPDS-NG values. / Create
TAREG / Tariff or Regulated / Contains only Y or N not longer needed. / Remove
TYPECNTRCTR / Contractor Type - Prime or Subcontractor / Not referenced in FPDS-NG / Remove
TYPECONTRACT / Type of Contract / TYPE_OF_CONTRACT_PRICING
Create using new FPDS-NG values. / Create
TYPECONTRACTOR / Type of Contractor / Values being used from CCR / Old
TYPENT / Type of Entry / STATUS_CODE
Create using new FPDS-NG values / Exists
TYPOBL / Type of Obligation / Not referenced in FPDS-NG / Remove
TYPPLAN / Not referenced in FPDS-NG / Remove
TYPRPT / Type of Report / Not referenced in FPDS-NG / Remove
VET_OWNED / Veteran Owned Business / Values being used from CCR / Old
WOB / Women Owned Business / Values being used from CCR / Old
CO / Contracting Office / GOVERNMENT_OFFICE / Exists
COUNTRY / Country Code and Names / COUNTRY / Exists
FUND_AG_DODAAC / Funding Agency DODAAC / FUNDING_DODAAC / Create
NAICS / NAICS Codes / NAICS / Import
PSC / Product or Service Code / PSC / Import
REGIONS / Regions of the USA / UNITED_STATES_REGIONS / Create
RPTAG / Agency / AGENCY / DEPARTMENT / Import
RSTATUS / Dun and Bradstreet Status / Not needed in FPDS-NG / Remove
SIC / SIC Codes / SIC / Create
STATE / States and State Codes / STATE_CODE / Import
STATE_ZIP / State and Zip Code Combinations / ZIP_CODE
Create using new Zip Code Values from the US Postal Service. / Create
STPLACE / State and City Combinations with Location Codes / PLACE_OF_PERFORMANCE / Import
WORLD_REGION / Regions of the World / Not needed in FPDS-NG / Remove
CIF_BASE / Contractor Information File / Not needed in FPDS-NG as the new CCR database will be used. / Remove
CIF_CHANGES / Contractor Information File Changes / Not needed in FPDS-NG as the new CCR database will be used. / Remove
CIF_HISTORY / Contractor Information File History – This file contains the CEC to DUNS number mapping. / Not needed in FPDS-NG, as the new CCR database will be used. / Remove
CIF_NEW / Latest Contractor Information File – This file a DUNS number mapping to a new DUNS number. / Not needed in FPDS-NG as the new CCR database will be used. / Remove

3.1.2Create Reference Tables

3.1.2.1DOD_CLAIMANT

• The migration will create a new table reference called DOD_CLAIMANT.
• Records from the DODCMNT table will be migrated into the new table.

The table mapping for the new reference table is as follows:

Old Column Name / New Column Name / Data Type / Description
DODCMNT_CODE / DOD_CLAIMANT (primary key) / Varchar2(3) / DoOD Claimant Code
Old Column Name / New Column Name / Data Type / Description
DODCMNT_NAME / DESCRIPTION / Varchar2(255) / DoOD Claimant Description
DOE / CREATED_DATE / Date / Date Created
CREATED_BY / Varchar2(20) / Created by User ID
DOC / LAST_UPDATED_DATE / Date / Date Last Updated
LAST_UPDATED_BY / Varchar2(20) / Last Updated by User ID
START_DATE / START_DATE / Date / Start Date
END_DATE / END_DATE / Date / End Date
STATUS / Status
NEXT
PREV

3.1.2.2PROGRAM_SYSTEM_EQUIPMENT

• The migration will create a new table reference called PROGRAM_SYSTEM_EQUIPMENT.
• Records from the DODSE table will be migrated into the new table.

The table mapping for the new reference table is as follows:

Old Column Name / New Column Name / Data Type / Description
DODSE_CODE / PROGRAM_SYSTEM_EQUIPMENT_CODE (primary key) / Varchar2(3) / DoD Weapons or System Code
DOSSE_DESC / DESCRIPTION / Varchar2(1000) / DoD Weapons or System Description
DOD_SERVICE / SERVICE_CODE
(foreign key military_departments) / Varchar2(1) / Military Service Code
DOE / CREATED_DATE / Date / Date Created
CREATED_BY / Varchar2(20) / Created by User ID
DOC / LAST_UPDATED_DATE / Date / Date Last Updated
LAST_UPDATED_BY / Varchar2(20) / Last Updated by User ID
START_DATE / START_DATE / Date / Start Date
Old Column Name / New Column Name / Data Type / Description
END_DATE / END_DATE / Date / End Date
STATUS / Status
NEXT
PREV

3.1.2.3MILITARY_DEPARTMENTS

• The migration will create a new table reference called MILITARY_DEPARTMENTS.
• Records from the DOD_SERVICE table will be migrated into the new table.

The table mapping for the new reference table is as follows:

Old Column Name / New Column Name / Data Type / Description
DOD_SERVICE / SERVICE_CODE (Primary Key) / Varchar2(1) / DoD Service Code
NAME / SERVICE_NAME / Varchar2(50) / DoD Service Name
DOE / CREATED_DATE / Date / Date Created
CREATED_BY / Varchar2(20) / Created by User ID
DOC / LAST_UPDATED_DATE / Date / Date Last Updated
LAST_UPDATED_BY / Varchar2(20) / Last Updated by User ID

3.1.2.4EPA_PRODS

• The migration will create a new table reference called EPA_PRODS.
• Records from the EPA_PRODUCTS table will be migrated into the new table.

The table mapping for the new reference table is as follows:

Old Column Name / New Column Name / Data Type / Description
EPA_CODE / EPA_PRODS (Primary Key) / Varchar2(1) / Designated Products Code
SHORT_DESC / SHORT_DESCRIPTION / Varchar2(35) / Short Description
LONG_DESC / DESCRIPTION / Varchar2(1000) / Description
DOE / CREATED_DATE / Date / Date Created
Old Column Name / New Column Name / Data Type / Description
CREATED_BY / Varchar2(20) / Created by User ID
DOC / LAST_UPDATED_DATE / Date / Date Last Updated
LAST_UPDATED_BY / Varchar2(20) / Last Updated by User ID
START_DATE / START_DATE / Date / Start Date
END_DATE / END_DATE / Date / End Date
STATUS / Status

3.1.2.5ETHNIC_OWNED_BUSINESS

• The migration will create a new table reference called ETHNIC_OWNED_BUSINESS.
• Records from the ETHNIC table will be migrated into the new table.

The table mapping for the new reference table is as follows:

Old Column Name / New Column Name / Data Type / Description
ETHNIC / ETHNIC_CODE (Primary Key) / Varchar2(1) / Ethnic Code
SHORT_DESC / SHORT_DESCRIPTION / Varchar2(35) / Short Description
LONG_DESC / DESCRIPTION / Varchar2(1000) / Description
DOE / CREATED_DATE / Date / Date Created
CREATED_BY / Varchar2(20) / Created by User ID
DOC / LAST_UPDATED_DATE / Date / Date Last Updated
LAST_UPDATED_BY / Varchar2(20) / Last Updated by User ID
START_DATE / START_DATE / Date / Start Date
END_DATE / END_DATE / Date / End Date
Old Column Name / New Column Name / Data Type / Description
STATUS / Status
NEXT
PREV

3.1.2.6FAIR_OPPORTUNITY

• The migration will create a new table reference called FAIR_OPPORTUNITY.
• Records from the FAIR_OP table will be migrated into the new table.

The table mapping for the new reference table is as follows:

Old Column Name / New Column Name / Data Type / Description
FAIR_OP / FAIR_OPPORTUNITY_CODE (Primary Key) / Varchar2(1) / Fair Opportunity Code
SHORT_DESC / SHORT_DESCRIPTION / Varchar2(35) / Short Description
LONG_DESC / DESCRIPTION / Varchar2(1000) / Description
DOE / CREATED_DATE / Date / Date Created
CREATED_BY / Varchar2(20) / Created by User ID
DOC / LAST_UPDATED_DATE / Date / Date Last Updated
LAST_UPDATED_BY / Varchar2(20) / Last Updated by User ID
START_DATE / START_DATE / Date / Start Date
END_DATE / END_DATE / Date / End Date
STATUS / Status

3.1.2.7HUBZONE_PROGRAM

• The migration will create a new table reference called HUBZONE_PROGRAM.
• Records from the HUB_PROG table will be migrated into the new table.

The table mapping for the new reference table is as follows:

Old Column Name / New Column Name / Data Type / Description
HUB_PROG / HUBZONE_PROGRAM_CODE (Primary Key) / Varchar2(1) / HUBZONE Program Code
SHORT_DESC / SHORT_DESCRIPTION / Varchar2(35) / Short Description
LONG_DESC / DESCRIPTION / Varchar2(1000) / Description
DOE / CREATED_DATE / Date / Date Created
CREATED_BY / Varchar2(20) / Created by User ID
DOC / LAST_UPDATED_DATE / Date / Date Last Updated
LAST_UPDATED_BY / Varchar2(20) / Last Updated by User ID
START_DATE / START_DATE / Date / Start Date
END_DATE / END_DATE / Date / End Date
STATUS / Status

3.1.2.8SIC

• The migration will create a new table reference called SIC.
• Records from the SIC table will be migrated into the new table.

The table mapping for the new reference table is as follows:

Old Column Name / New Column Name / Data Type / Description
SIC / SIC_CODE (Primary Key) / Varchar2(4) / SIC Code
SIC_NAME / SHORT_DESCRIPTION / Varchar2(35) / Short Description
LONG_DESC / DESCRIPTION / Varchar2(1000) / Description
DOE / CREATED_DATE / Date / Date Created
CREATED_BY / Varchar2(20) / Created by User ID
Old Column Name / New Column Name / Data Type / Description
DOC / LAST_UPDATED_DATE / Date / Date Last Updated
LAST_UPDATED_BY / Varchar2(20) / Last Updated by User ID
START_DATE / START_DATE / Date / Start Date
END_DATE / END_DATE / Date / End Date
STATUS / Status
NEXT
PREV

3.1.2.9UNITED_STATES_REGIONS

• The migration will create a new table reference called UNITED_STATES_REGIONS.
• Records from the REGIONS table will be migrated into the new table.

The table mapping for the new reference table is as follows:

Old Column Name / New Column Name / Data Type / Description
REGION_CODE / REGION (Primary Key) / Varchar2(4) / Region Code
REGION_NAME / REGION_NAME / Varchar2(100) / Region Name
CITY / CITY / Varchar2(35) / City
STATE / STATE / Varchar2(2) / State
CREATED_DATE / Date / Date Created
CREATED_BY / Varchar2(20) / Created by User ID
LAST_UPDATED_DATE / Date / Date Last Updated
LAST_UPDATED_BY / Varchar2(20) / Last Updated by User ID
START_DATE / START_DATE / Date / Start Date
END_DATE / END_DATE / Date / End Date
STATUS / Status

3.1.3Import Reference Tables

3.1.3.1COUNTRY

• The migration will import the records from the COUNTRY table into the new FPDS-NG existing COUNTRY table.
• The existing FPDS-NG COUNTRY table was populated from the latest country listing as supplied by the FPDS website (
• Only records that do not exist will be migrated to the new COUNTRY table.
• Migration Issue(s) for this reference table are as follows:

The current system does not have the 3-character country code that is used as the primary key for the FPDS-NG COUNTRY table.

The country code table will be manual updated with the proper values.

The table mapping for the new reference table is as follows:

Old Column Name / New Column Name / Data Type / Description
COUNTRY_CODE (Primary Key) / Varchar2(3) / 3 Character Country Code
COUNTRY_NAME / COUNTRY_NAME / Varchar2(100) / Country Name
COUNTRY / COUNTRY_SHORT_CODE / Varchar2(2) / 2 Character Country Code
REGION / REGION / Varchar2(5) / Region Code associated to the WORLD_REGIONS table.
DOE / CREATED_DATE / Date / Date Created
DOC / LAST_UPDATED_DATE / Date / Date Last Modified
CREATED_BY / Varchar2(20) / Created by User ID
LAST_UPDATED_BY / Varchar2(20) / Last Updated by User ID
START_DATE / START_DATE / Date / Start Date
END_DATE / END_DATE / Date / End Date
STATUS / Status

3.1.3.2NAICS Tables

• The migration will import the records from the NAICS table into the new FPDS-NG existing NAICS tables.
• The existing FPDS-NG NAICS tables were populated from the latest NAICS listing as supplied by the FPDS website (
• Only records that do not exist will be migrated to the new NAICS tables.
• Migration Issue(s) for this reference table are as follows:
• None.

The table mapping for the NAICS table is as follows:

Old Column Name / New Column Name / Data Type / Description
NAICS / NAICS_CODE (Primary Key) / Varchar2(6) / NAICS Code
PARENT_NAICS_CODE / Varchar2(5) / NAICS Parent Code
NAICS_NAME / NAICS_DESCRIPTION / Varchar2(150) / NAICS Description
DOE / CREATED_DATE / Date / Date Created
CREATED_BY / Varchar2(20) / Created by User ID
DOC / LAST_UPDATED_DATE / Date / Date Last Updated
LAST_UPDATED_BY / Varchar2(20) / Last Updated by User ID
START_DATE / START_DATE / Date / Start Date
END_DATE / END_DATE / Date / End Date
SERVICE_SECTOR

3.1.3.3DEPARTMENT

• The migration will import the records from the PARENT_AGENCY table into the new FPDS-NG existing DEPARTMENT table.
• The existing FPDS-NG DEPARTMENT table was populated from the latest department listing as supplied by the FPDS website (
• Only records that do not exist will be migrated to the new DEPARTMENT table.
• Migration Issue(s) for this reference table are as follows:

None

The table mapping for the PSC table is as follows:

Old Column Name / New Column Name / Data Type / Description
RPTAG / DEPARTMENT_ID (Primary Key) / Varchar2(4) / Department ID
AGENCY / DEPARTMENT_NAME / Varchar2(100) / Department Name
DEPARTMENT_SHORT_NAME / Varchar2(3) / Department Short Name
START_DATE / Date / Start Date
END_DATE / Date / End Date
CREATED_DATE / Date / Date Created
CREATED_BY / Varchar2(20) / Created by User ID
LAST_UPDATED_DATE / Date / Date Last Updated
LAST_UPDATED_BY / Varchar2(20) / Last Updated by User ID

3.1.3.4PSC

• The migration will import the records from the PSC table into the new FPDS-NG existing PSC table.
• The existing FPDS-NG PSC table was populated from the latest PSC listing as supplied by the FPDS website (
• Only records that do not exist will be migrated to the new PSC table.
• Migration Issue(s) for this reference table are as follows:

None

The PSC table mappings for the new reference table ismappings for the new reference table are as follows:

Old Column Name / New Column Name / Data Type / Description
PSC / PRODUCT_OR_SERVICE_CODE (Primary Key) / Varchar2(4) / Product or Service Code
PSC_NAME / PRODUCT_OR_SERVICE_DESCRIPTION / Varchar2(100) / Product or Service Description
PARENT_PRODUCT_OR_SERVICE_CODE / Varchar2(4) / Parent Product or Service character code
DOE / CREATED_DATE / Date / Date Created
CREATED_BY / Varchar2(20) / Created by User ID
Old Column Name / New Column Name / Data Type / Description
DOC / LAST_UPDATED_DATE / Date / Date Last Updated
LAST_UPDATED_BY / Varchar2(20) / Last Updated by User ID
START_DATE / START_DATE / Date / Start Date
END_DATE / END_DATE / Date / End Date
FULL_NAME / COMPLETE_DESCRIPTION / Varchar2(2000) / Description
INCLUDES / PRODUCT_OR_SERVICE_INCLUDES / Varchar2(2000) / Product or Service Includes
EXCLUDES / PRODUCT_OR_SERVICE_EXCLUDES / Varchar2(2000) / Product or Service Excludes
NOTES / PRODUCT_OR_SERVICE_COMMENTS / Varchar2(4000) / Product or Service Comments or Notes

3.1.3.5AGENCY

• The migration will import the records from the RPTAG table into the new FPDS-NG existing AGENCY table.
• The existing FPDS-NG AGENCY table was populated from the latest agency listing as supplied by the FPDS website (
• Only records that do not exist will be migrated to the new AGENCY table.
• Migration Issue(s) for this reference table are as follows:

None

The table mapping for the new reference table is as follows:

Old Column Name / New Column Name / Data Type / Description
RPTAG / AGENCY_CODE (Primary Key) / Varchar2(4) / Agency Code
PARENT / DEPARTMENT_ID / Varchar2(4) / Department ID associated to the DEPARTMENT table
NAME / AGENCY_NAME / Varchar2(100) / Department Name
Old Column Name / New Column Name / Data Type / Description
N/A / AGENCY_SHORT_CODE / Varchar2(20) / Department Short Name
N/A / PARENT_AGENCY_CODE / Varchar2(4) / Parent Agency Code
DOE / CREATED_DATE / Date / Date Created
CREATED_BY / Varchar2(20) / Created by User ID
DOC / LAST_UPDATED_DATE / Date / Date Last Updated
LAST_UPDATED_BY / Varchar2(20) / Last Updated by User ID
START_DATE / START_DATE / Date / Start Date
END_DATE / END_DATE / Date / End Date

3.1.3.6SIC

• The migration will create a new table reference called SIC.
• Records from the SIC table will be migrated into the new table.

The table mapping for the new reference table is as follows:

Old Column Name / New Column Name / Data Type / Description
SIC / SIC_CODE / Varchar2(4) / SIC Code
SIC_NAME / DESCRIPTION / Varchar2(1000) / SIC Description
DOE / CREATED_DATE / Date / Date Created
CREATED_BY / Varchar2(20) / Created by User ID
DOC / LAST_UPDATED_DATE / Date / Date Last Updated
LAST_UPDATED_BY / Varchar2(20) / Last Updated by User ID
START_DATE / START_DATE / Date / Start Date
END_DATE / END_DATE / Date / End Date

3.1.3.7STATE_CODE