Bookshelf v7.8.3: Loading Syndicated Data into the Siebel Data Warehouse

Bookshelf Home | Contents | Index | Search | PDF

Siebel Analytics Applications Installation and Administration Guide > Siebel Data Warehouse for Life Sciences Data Considerations > Importing Syndicated Data into Siebel Data Warehouse >

Loading Syndicated Data into the Siebel Data Warehouse

The following options are available for loading syndicated data into the Siebel Data Warehouse:

Loading From a Flat File Source Using Syndicated Data Staging Tables and Cross-Referencing Tables
Loading the Siebel Data Warehouse from the Siebel Transactional Database (OLTP) S_SYND_DATA Table
Loading From a Flat File Source Using Syndicated Market Staging Tables

Loading From a Flat File Source Using Syndicated Data Staging Tables and Cross-Referencing Tables

This option is supported in Siebel Analytics Applications Version 7.8.3. To load syndicated data with this option, you have to prepare external data files for syndicated data and cross-reference data as described by the requirements in the following tables: Table 96, Table 97, Table 98, Table 99, Table 100, Table 101, Table 107, Table 103, Table 104, Table 105, Table 106, and Table 107. The ETL process will load syndicated data source files as a full load each time, but cross-reference data files will be loaded incrementally. Cross-reference data files must contain only new and updated information each time to support proper incremental loading. When the cross-reference data files are prepared, the data must be already loaded into the transactional database so the proper Siebel row IDs and the corresponding external source keys are resolved and provided in the data files.

The alignment assignment rule is critical with this option. The external file AlignmentType.csv is installed during the Siebel Analytics Applications installation in the Informatica Server\SrcFiles folder. You have to select an alignment item type from the options defined below before running the ETL. You can change the alignment item type only when you choose to run the full ETL load process. The following alignment item types available:

Zipcode
Account/Contact
Brick

Once the syndicated data and cross-reference source files are prepared, then the data can be loaded directly into Siebel Data Warehouse staging tables. If any invalid data source type is used in the syndicated data files, then the ETL will load as it is. You have to prepare the correct data source type in the data files. Only the data that should not be nullable but is null in data files will be filtered out separately so you can review the rejected data for the next ETL run.

When the syndicated data is loaded from the Siebel Data Warehouse staging tables to target base tables, such as W_SYNDD_RX_F, the external source keys are replaced with Siebel Analytics keys and the data is loaded in the normalized format.

Then, based on what you defined as a primary alignment type above, the syndicated data in base target tables is replicated into the proper owner position and loaded into the main target tables at the territory level, such as W_SYND_RX_T_F, which contains all base table attributes plus ranking and position information.

Flat File Data Population Rules for Populating TRx Data

This section provides information about various major columns in the W_SYNDD_RX_F table related to populating TRx data.

INTEGRATION_ID
- Data type is varchar(30).
- Value can be any unique number or combination of values coming from the external file source with the appended postfix -##, such as -1, if the data is corresponding to the first month of bulk syndicated data, and -2 if the data is corresponding to the second month of bulk data, and so on.
- INTEGRATION_ID manipulation is done by LS_EXP_FLATFILE_GENERATE_INTEGRATION_ID transformation. It is required because the incoming INTEGRATION_ID in flat file represents 26 different bulk loads in denormalized format. Therefore, the format must be changed to normalized for the Siebel Data Warehouse target table.
DATASOURCE_NUM_ID
- ETL run specific (a numeric value that indicates the source for the data).
- Used in standard Siebel mappings to ensure each record carries a value indicating its source.
- Used in source qualifiers to ensure that lookups on dimension tables when populating fact tables area referencing data that was loaded from a uniform data source.
- Data source number 1 is reserved for the Siebel transactional database. The external source should use a number other than 1.
- For syndicated data flat file loads, the external source number (other than 1) is loaded into staging tables. When the data is loaded from the staging tables to target tables and external source keys are matched to Siebel IDs, the data source number is converted as 1 in the target table.
- Combination of INTEGRATION_ID and DATASOURCE_NUM_ID must be a unique value (mandated by a unique index on both columns).
  This means that the system will not accept the same record from the same source with identical unique IDs.
DATA_LEVEL_WID
- Stores a foreign key in the W_LOV_UD table for the record corresponding to that syndicated data source type for the data.
- Value comes from DATA_SRC_CD in the flat file. For example, in the table W_SYNDD_RX_F, the proper data source type defined in flat file must be one of the following:
  - RXPrf
  - RXZip
  - RXBrk
  - RXSMI
  - RXSMM
- The standard mapping uses the incoming DATA_SRC_CD code to do a lookup against the W_LOV_UD table to secure the ROW_WID of that data source type and inserts it into the W_SYNDD_RX_F table DATA_LEVEL_WID column.
PAYER_TYPE_WID
- Stores a foreign key in the W_INS_PLAN_D table for the record corresponding to that payer type of the data such as Cash, Total.
- Value comes from PAYER_TYPE_ID in the flat file.
PERIOD_TYPE_WID
- Stores a foreign key in the W_LOV_D table for the record corresponding to the period for the data.
- Value comes from PERIOD_CD in the flat file, such as Month, Week.
- The standard mapping uses the incoming PERIOD_CD code to do a lookup against the W_LOV_D table to secure the ROW_WID of that period type and inserts it into the W_SYNDD_RX_F table PERIOD_TYPE_WID column
GEO_WID
- GEO_WID is a foreign key to the W_GEO_D dimension, which contains a geographical hierarchy based on zip code as the lowest level of detail. It is preconfigured to allow users to roll up a TRx measure to levels such as city, state or province, county, country, and continent.
- If the flat file source of TRx data load is zip code level, then the ZIPCODE field in the flat file must be provided. Then, the value is performed using the following logic to get loaded in the target GEO_WID:
  - MPLT_GEO_WID_ZIPCODE takes zip code as the incoming value, and the literal NO_DUP_FLG = 'N' ports to do a lookup against W_GEO_D for any record where the ZIPCODE column matches the incoming zip code value and the DUP_ZIPCODE column = 'N'. (Sample data in the W_GEO_D table contains multiple city records per zip code, only one of which is marked as DUP_ZIPCODE = 'N'). The mapping also ensures that even with multiple matches only the first record is retrieved, and with no matches, the data comes back with the appropriate Unspecified code.
CON_GEO_WID
- CON_GEO_WID is a foreign key to the W_GEO_D dimension, which contains a geographical hierarchy based on zip code as the lowest level of detail.
- The following three attributes, city, country, and zip code, are the main keys to determine CON_GEO_WID, and are brought from flat file CON_CITY, CON_COUNTRY, and CON_ZIPCODE fields.
- If the flat file source of TRx data load is contact level, then CON_CITY, CON_COUNTRY, and CON_ZIPCODE fields in flat file must be provided. Then, the value is performed the following logic to get loaded in target CON_GEO_WID:
  - LS_EXP_FLATFILE_CITY_COUNTRY_ZIP_CHKNULL and LS_MPLT_FLATFILE_GEO_WID use the combination of city, country, and zip code to retrieve the first available record from W_GEO_D even if duplicates exist and regardless of the NO_DUP_FLG.
ALIGN_WID
- Depending on your selected alignment item type, the following transformation is performed:
  - When alignment item type is Zipcode:
IIF(DATA_SRC_CD = 'RXPrf', IN_CON_GEO_WID,

IIF(DATA_SRC_CD = 'RXBrk' OR DATA_SRC_CD = 'RXSMI' OR DATA_SRC_CD = 'RXSMM' OR DATA_SRC_CD = 'RXZip', IN_GEO_WID,

ETL_UNSPEC_NUM))
- When alignment item type is Account/Contact:
IIF(DATA_SRC_CD = 'RXBrk' OR DATA_SRC_CD = 'RXPrf' OR DATA_SRC_CD = 'RXSMI' OR DATA_SRC_CD = 'RXSMM' OR DATA_SRC_CD = 'RXZip', IN_CONTACT_WID, ETL_UNSPEC_NUM)
- When alignment item type is Brick:
IIF(DATA_SRC_CD = 'RXPrf', IN_CON_AREA_WID,

IIF(DATA_SRC_CD = 'RXBrk' OR DATA_SRC_CD = 'RXSMI' OR DATA_SRC_CD = 'RXSMM' OR DATA_SRC_CD = 'RXZip', IN_AREA_WID,

ETL_UNSPEC_NUM))

The resulting value is used to populate the ALIGN_WID column in the fact table, which should also be found in W_ALIGNMT_DH table ALIGN_WID column.

Based on this ALIGN_WID column, Siebel Data Warehouse replicates proper territory and populates W_SYND_RX_T_F table as well.

NOTE: This section discusses the W_SYNDD_RX_F table as an example only. In Siebel Analytics Applications Version7.8.3, the table W_SYND_DATA_F is split into the following six new tables due to performance enhancement: W_SYNDD_DS_F, W_SYNDD_IDS_F, W_SYNDD_RX_F, W_SYNDD_PT_F, W_SYNDD_XPT_F, and W_SYNDD_W_F tables.

Loading the Siebel Data Warehouse from the Siebel Transactional Database (OLTP) S_SYND_DATA Table

NOTE: This option is only available with Siebel Analytics Applications Version 7.8.2 and earlier.

Customers who are already using the EIM process or SQL Loader to load external syndicated data into the Siebel operational system can use data populated in S_SYND_DATA as the source to the Siebel Data Warehouse. Existing syndicated data from the Siebel transactional database, stored in the S_SYND_DATA table, is extracted, transformed, and loaded by prebuilt routines and populated into the W_SYND_DATA_F and W_SYND_MKT_F fact tables in the Siebel Data Warehouse. W_SYND_DATA_F stores Rx or sales measures by product. W_SYND_MKT_F stores Rx or sales measures by market and allows calculation of market share data.

The data is first copied from external data tapes (or other provided media) into the interface tables for the Siebel Industry Application (SIA), using a native database data loading utility, such as SQL* Loader, and following the rules in Table 108. Using EIM, data is transferred from the Siebel interface tables to predefined destination columns in the base tables of the Siebel Life Sciences database. The application administrator uses SQL*Loader to populate the ID field with a unique sequential value, a process that provides either full or partial table level extraction.

In order to correctly load the Siebel Data Warehouse, there is a set of population rules that are required to load S_SYND_DATA. This will guarantee minimal configuration required on the Analytics side and correct population of the Siebel Data Warehouse.

Once the data is loaded into S_SYND_DATA, you can use the LS SYNDICATED FACT GROUP, in the Pharma subject area, to load the syndicated data into the Siebel Data Warehouse.

W_SYND_DATA_F Data Population Rules for Populating TRx Data

This section provides information about various columns in the W_SYND_DATA_F table related to populating TRx data. This option is only available with Siebel Analytics Applications Version 7.8.3 and earlier.

INTEGRATION_ID
- Data type is varchar(30).
- Value can be any unique number or combination of values coming in from the external file source. If the data is not already unique, the EXPTRANS transformation will manipulate the value to make it unique.
- Typically, contains the row ID of the source table record in the Siebel transactional database.
DATASOURCE_NUM_ID
- ETL run specific (a numeric value that indicates the source for the data).
- Used in standard Siebel mappings to ensure each record carries a value indicating its source.
- Used in source qualifiers to ensure that lookups on dimension tables when populating fact tables are referencing data that was loaded from a uniform data source.
- Data source number 1 is reserved for the Siebel transactional database. The external source should use a number other than 1.
- Combination of INTEGRATION_ID and DATASOURCE_NUM. Must be a unique value (mandated by a unique index on both columns).
  This means that the system will not accept the same record from the same source with identical unique IDs.
DATA_SRC_CD
Standard Siebel mapping transforms an incoming character data source code value into a numeric range between 1 and 8, using the following formula:

DECODE(DATA_SRC_CD

RXPrf',1,
RXEVM',0,
RXSMI',3,
RXSMM',4,
RXXPT',5,
RXPT',6,
RXZip',7,
RXBrk',8

The resulting numeric value is used to populate the DATA_TYPE column in the fact table, which is used in the Analytics business models to logically partition data when running different types of reports against the W_SYND_DATA table.
PREFIX
The following transformation is performed on DATA_SRC_CD to populate the PREFIX column:

DECODE(DATA_SRC_CD,

RXPrf','Prf',
RXEVM','EVM',
RXSMI','SMI',
RXSMM','SMM',
RXXPT','XPT',
RXPT','PT',
RXZip','Zip',
RXBrk','Brk')

The resulting three-character code is used to prefix the ROW_WID of the W_SYND_DATA table. Instead of 1, the first record is populated with Prf1 when loading prescriptions by professional. This is done in order to facilitate parallel load streams in Informatica so that ROW_WIDs of records for different data types do not conflict with each other.
PERIOD_TYPE_WID
- Stores a foreign key in the W_LOV_D table for the record corresponding to the period for the data.
- PERIOD_CD comes from the S_SYND_DATA as Month or Week, and so on.
- The standard mapping uses the code to do a lookup against the W_LOV_D table to secure the ROW_WID of that period type and inserts it into the W_SYND_DATA_F table PERIOD_TYPE_WID column.
OWNER_POSTN_WID
- W_SYND_DATA maps each record to a Siebel position using OWNER_POSTN_ID. Typically, when the source of the ETL is S_SYND_DATA in the transactional database, the field is already populated, but this is not the case when the source is the syndicated data file.
- The W_ALIGNMT_DH table in the data warehouse contains information that maps Siebel positions to zip codes, bricks, and accounts. ALIGN_ITEM_TYPE contains literal type values for this data, such as zip code, brick or account.
- Since the alignment process could assign a territory to multiple zip codes, the W_ALIGNMT_DH table could contain multiple records for the same zip code to position and territory combinations. In such cases, the ALIGN_TYPE_FLG column will designate whether the relationship between the territory and the zip code is primary (P), mirror (M), share (S), and so on.
- The Source qualifier in the custom mapping that populates W_SYND_DATA_F then retrieves the correct data warehouse position ID based on the following join criteria:
SELECT
SYND.IMS_NUM,
SYND.CLIENT_NUM,
SYND.START_DT,
SYND.IMS_SPEC_CD,
SYND.PROD_GRP,
SYND.ME_NUM,
SYND.PR_CITY,
SYND.PR_COUNTRY,
SYND.ZIPCODE,
ALIGN.LVL1ANC_POSTN_ID as OWNER_POSTN_ID,
PER.ROW_WID as MAP_CON_ROW_WID,
PROD_MKT.ROW_WID as MAP_MKT_ROW_WID,
PROD.ROW_WID as PROD_ROW_WID,
PROD.RX_AVG_PRICE,
POSTN.ROW_WID as POSTN_ROW_WID,
POSTN.INTEGRATION_ID as OWNER_POSTN_ID,
SYND.TRX01,
SYND.TRX02,
SYND.TRX03,
SYND.TRX04,
SYND.TRX05,
SYND.TRX06,
SYND.TRX07,
SYND.TRX08,
SYND.TRX09,
SYND.TRX10,
SYND.TRX11,
SYND.TRX12,
SYND.TRX13,
ALIGN.ALIGN_WID,
ALIGN.ALIGN_TYPE_FLG,
PER.INTEGRATION_ID,
PROD_MKT.PROD_NAME,
PROD.PROD_NAME
from WC_SYND_DATA_FS as SYND,

W_ALIGNMT_DH as ALIGN,
W_PERSON_D as PER,
W_PRODUCT_D as PROD_MKT,
W_PRODUCT_D as PROD,
W_POSITION_D as POST

where
(SYND.ZIPCODE = ALIGN.ALIGN_ITEM and ALIGN.ALIGN_ITEM_TYPE = 'Zipcode' and ALIGN.ALIGN_TYPE_FLG = 'P') and
SYND.ME_NUM = PER.CSN and
SYND.PHYNTYPE = PROD_MKT.PROD_NAME and
SYND.PROD_GRP = PROD.PROD_NAME and
ALIGN.LVL1ANC_POSTN_ID = POSTN.INTEGRATION_ID
GEO_WID
- In the Siebel Data Warehouse, the GEO_WID is a foreign key to the W_GEO_D dimension, which contains a geographical hierarchy based on zip code as the lowest level of detail. It is preconfigured to allow users to roll up a TRx measure to levels such as city, state or province, county, country, and continent.
- In the standard Siebel mappings that populate W_SYND_DATA_F, the following three attributes are brought directly from the S_SYND_DATA table: city, country, and zip code.
- A complex lookup is then performed utilizing two possible logic paths:
  - MPLT_GEO_WID_ZIPCODE (returns IN_ZIPCODE_GEO_WID): Takes in the zip code value, and a literal NO_DUP_FLG = 'N' ports do a lookup against W_GEO_D for any record where the ZIPCODE column matches the incoming zip code value and the DUP_ZIPCODE column = 'N'. (Sample data in the W_GEO_D table contains multiple city records per zip code, only one of which is marked as DUP_ZIPCODE = 'N'). The mapping also ensures that even with multiple matches only the first record is retrieved, and with no matches, the data comes back with the appropriate Unspecified code.
  - EXP_CITY_COUNTRY_ZIP_CHKNULL & MPLT_GEO_WID1 (returns IN_GEO_WID): Uses the combination of city, country, and zip code to retrieve the first available record from W_GEO_D even if duplicates exists and regardless of the NO_DUP_FLG.
- Once the two values are brought into the EXPTRANS transformation, the following formula is used:
IIF(DATA_SRC_CD = 'RXZip' OR DATA_SRC_CD = 'RXBrk' OR DATA_SRC_CD = 'SlsDirBrk' OR DATA_SRC_CD = 'SlsDirZip' OR DATA_SRC_CD = 'SlsIndBrk' OR DATA_SRC_CD = 'SlsIndZip',IN_ZIPCODE_GEO_WID,IN_GEO_WID)

Loading From a Flat File Source Using Syndicated Market Staging Tables

Siebel Analytics Applications Version 7.8.3 supports loading syndicated market data using flat files. You have to prepare flat files of syndicated market data source and cross-referencing data source files as described rules in the following tables: Table 96, Table 97, Table 98, Table 99, Table 100, Table 101, Table 107, Table 103, Table 104, Table 105, Table 106, and Table 107.

By default this option is disabled. To use this feature, you must to turn on the option manually in the DAC client. Turning on this option allows the flat file syndicated market data to be loaded directly into the Siebel Data Warehouse staging table W_SYNDM_RX_FS, and then loaded into the target table W_SYNDM_RX_F.

To load syndicated market data using flat files

Open the DAC client, and navigate to Design > Task Definitions.
Enter the following query:
LS Load into SyndicatedMarket Fact for*

Eleven tasks are returned.
Inactivate the following tasks, which, by default, are turned on:
- LS Load into SyndicatedMarket Fact for Calculated Exponent Plantrak
- LS Load into SyndicatedMarket Fact for Calculated Indirect Sales
- LS Load into SyndicatedMarket Fact for Calculated Plantrak
- LS Load into SyndicatedMarket Fact for Calculated Rx
- LS Load into SyndicatedMarket Fact for Calculated Weekly Early View
  NOTE: Do not inactivate LS Load into SyndicatedMarket Fact for Calculated Direct Sales.
Activate the following tasks:
- LS Load into SyndicatedMarket Fact for Exponent Plantrak
- LS Load into SyndicatedMarket Fact for Indirect Sales
- LS Load into SyndicatedMarket Fact for Plantrak
- LS Load into SyndicatedMarket Fact for Rx
- LS Load into SyndicatedMarket Fact for Weekly Early View
Save your work.

Syndicated Data Flat File for Direct Sales Data

Table 96 provides information about the syndicated data flat file for Direct Sales data.

Table 96. Syndicated Data Flat File - Direct Sales Data
Field Name	Data Type	Field Required	Field Description
DATASOURCE_NUM_ID	NUMBER(10)	NOT NULL	External data source number ID other than 1.
DATA_SRC_CD	VARCHAR2(30)	NOT NULL	Enter the following Siebel data source type code: SlsDirAct
INTEGRATION_ID	VARCHAR2(25)	NOT NULL	External integration ID.
START_DT	VARCHAR2(15)	NOT NULL	Syndicate data date format YYYYMMDD.
AMT_DT	VARCHAR2(15)		Exchange date for currency code. Date format YYYYMMDD.
ACCNT_ID	VARCHAR2(30)		External account ID. Note: Corresponding Siebel S_ORG_EXT.ROW_ID should be provided in cross-reference file.
ACCNT_AREA_ID	VARCHAR2(30)		External brick ID of account's primary address for account level. Note: Corresponding Siebel S_REGION_EXT.ROW_ID (S_CON_ADDR.BRICK_ID) should be provided in cross-reference file.
ACCNT_CITY	VARCHAR2(50)		Account city. Initial capital format; for example, Short Hills.
ACCNT_COUNTRY	VARCHAR2(30)		Account country. Initial capital format; for example, France; or abbreviate format, such as USA.
ACCNT_ZIPCODE	VARCHAR2(30)		Account zip code.
AMT_CURCY_CD	VARCHAR2(30)		Currency code for Amount.
PAYER_TYPE_ID	VARCHAR2(30)		External payer type ID. This column is used to define payment type, such as Cash, Government, Third Party, Total, and so on. Note: Corresponding Siebel S_INS_PLAN.ROW_ID should be provided in cross-reference file.
PERIOD_CD	VARCHAR2(30)		Enter the following Siebel period code: Month
PROD_ID	VARCHAR2(30)		External product ID. Note: Corresponding Siebel S_PROD_INT.ROW_ID should be provided in cross-reference file.
S_AMT_M01	NUMBER(22,7)		Stores sales amounts of current month data.
S_AMT_M02	NUMBER(22,7)		Stores sales amounts of previous month data.
S_AMT_M03	NUMBER(22,7)		Stores sales amounts of 20050101 data.
S_AMT_M04	NUMBER(22,7)
S_AMT_M05	NUMBER(22,7)
S_AMT_M06	NUMBER(22,7)
S_AMT_M07	NUMBER(22,7)
S_AMT_M08	NUMBER(22,7)
S_AMT_M09	NUMBER(22,7)
S_AMT_M10	NUMBER(22,7)
S_AMT_M11	NUMBER(22,7)
S_AMT_M12	NUMBER(22,7)
S_AMT_M13	NUMBER(22,7)
S_AMT_M14	NUMBER(22,7)
S_AMT_M15	NUMBER(22,7)
S_AMT_M16	NUMBER(22,7)
S_AMT_M17	NUMBER(22,7)
S_AMT_M18	NUMBER(22,7)
S_AMT_M19	NUMBER(22,7)
S_AMT_M20	NUMBER(22,7)
S_AMT_M21	NUMBER(22,7)
S_AMT_M22	NUMBER(22,7)
S_AMT_M23	NUMBER(22,7)
S_AMT_M24	NUMBER(22,7)
S_AMT_M25	NUMBER(22,7)
S_AMT_M26	NUMBER(22,7)
S_UNIT_M01	NUMBER(22,7)		Stores sales units of current month data.
S_UNIT_M02	NUMBER(22,7)		Stores sales units of previous month data.
S_UNIT_M03	NUMBER(22,7)		Stores sales units of 20050101 data.
S_UNIT_M04	NUMBER(22,7)		Stores sales units of current month data.
S_UNIT_M05	NUMBER(22,7)
S_UNIT_M06	NUMBER(22,7)
S_UNIT_M07	NUMBER(22,7)
S_UNIT_M08	NUMBER(22,7)
S_UNIT_M09	NUMBER(22,7)
S_UNIT_M10	NUMBER(22,7)
S_UNIT_M11	NUMBER(22,7)
S_UNIT_M12	NUMBER(22,7)
S_UNIT_M13	NUMBER(22,7)
S_UNIT_M14	NUMBER(22,7)
S_UNIT_M15	NUMBER(22,7)
S_UNIT_M16	NUMBER(22,7)
S_UNIT_M17	NUMBER(22,7)
S_UNIT_M18	NUMBER(22,7)
S_UNIT_M19	NUMBER(22,7)
S_UNIT_M20	NUMBER(22,7)
S_UNIT_M21	NUMBER(22,7)
S_UNIT_M22	NUMBER(22,7)
S_UNIT_M23	NUMBER(22,7)
S_UNIT_M24	NUMBER(22,7)
S_UNIT_M25	NUMBER(22,7)
S_UNIT_M26	NUMBER(22,7)

Syndicated Data Flat File for Indirect Sales Data

Table 97 provides information about the syndicated data flat file for Indirect Sales data.

Table 97. Syndicated Data Flat File - Indirect Sales Data
Field Name	Data Type	Field Required	Field Description
DATASOURCE_NUM_ID	NUMBER(10)	NOT NULL	External data source number ID other than 1.
DATA_SRC_CD	VARCHAR2(30)	NOT NULL	Enter the following Siebel data source type code: SlsIndAct (Indirect sales account level) SlsIndZip (Indirect sales zip code level) SlsIndBrk (Indirect sales brick level)
INTEGRATION_ID	VARCHAR2(25)	NOT NULL	External integration ID.
START_DT	VARCHAR2(15)	NOT NULL	Syndicate data date format YYYYMMDD.
AMT_DT	VARCHAR2(15)		Exchange date for currency code. Date format YYYYMMDD.
ACCNT_ID	VARCHAR2(30)		External account ID. Note: Corresponding Siebel S_ORG_EXT.ROW_ID should be provided in cross-reference file.
ACCNT_AREA_ID	VARCHAR2(30)		External brick ID of account's primary address for account level. Note: Corresponding Siebel S_REGION_EXT.ROW_ID (S_CON_ADDR.BRICK_ID) should be provided in cross-reference file.
ACCNT_CITY	VARCHAR2(50)		Account city. Enter value if DATA_SRC_CD is account level. Initial capital format; for example, Short Hills.
ACCNT_COUNTRY	VARCHAR2(30)		Account country. Enter value if DATA_SRC_CD is account level. Initial capital format; for example, France; or abbreviate format, such as USA.
ACCNT_ZIPCODE	VARCHAR2(30)		Account zip code. Enter value if DATA_SRC_CD is account level.
AMT_CURCY_CD	VARCHAR2(30)		Applies to syndicated data only. Currency code for Amount.
AREA_ID	VARCHAR2(30)		External syndicate brick ID. Enter value if DATA_SRC_CD is brick level. Note: Corresponding Siebel S_REGION.ROW_ID should be provided in cross-reference file.
PAYER_TYPE_ID	VARCHAR2(30)		External payer type ID. This column is used to define payment type, such as Cash, Government, Third Party, Total, and so on. Note: Corresponding Siebel S_INS_PLAN.ROW_ID should be provided in cross-reference file.
PERIOD_CD	VARCHAR2(30)		Enter the following Siebel period code: Month
PROD_ID	VARCHAR2(30)		Applies to syndicated data only. External product ID. Note: Corresponding Siebel S_PROD_INT.ROW_ID should be provided in cross-reference file.
ZIPCODE	VARCHAR2(30)		Syndicate zip code. Enter value if DATA_SRC_CD is zip code level.
S_AMT_M01	NUMBER(22,7)		Stores sales amounts of current month data.
S_AMT_M02	NUMBER(22,7)		Stores sales amounts of previous month data.
S_AMT_M03	NUMBER(22,7)		Stores sales amounts of 20050101 data.
S_AMT_M04	NUMBER(22,7)
S_AMT_M05	NUMBER(22,7)
S_AMT_M06	NUMBER(22,7)
S_AMT_M07	NUMBER(22,7)
S_AMT_M08	NUMBER(22,7)
S_AMT_M09	NUMBER(22,7)
S_AMT_M10	NUMBER(22,7)
S_AMT_M11	NUMBER(22,7)
S_AMT_M12	NUMBER(22,7)
S_AMT_M13	NUMBER(22,7)
S_AMT_M14	NUMBER(22,7)
S_AMT_M15	NUMBER(22,7)
S_AMT_M16	NUMBER(22,7)
S_AMT_M17	NUMBER(22,7)
S_AMT_M18	NUMBER(22,7)
S_AMT_M19	NUMBER(22,7)
S_AMT_M20	NUMBER(22,7)
S_AMT_M21	NUMBER(22,7)
S_AMT_M22	NUMBER(22,7)
S_AMT_M23	NUMBER(22,7)
S_AMT_M24	NUMBER(22,7)
S_AMT_M25	NUMBER(22,7)
S_AMT_M26	NUMBER(22,7)
S_UNIT_M01	NUMBER(22,7)		Stores sales units of current month data.
S_UNIT_M02	NUMBER(22,7)		Stores sales units of previous month data.
S_UNIT_M03	NUMBER(22,7)		Stores sales units of 20050101 data.
S_UNIT_M04	NUMBER(22,7)		Stores sales units of current month data.
S_UNIT_M05	NUMBER(22,7)
S_UNIT_M06	NUMBER(22,7)
S_UNIT_M07	NUMBER(22,7)
S_UNIT_M08	NUMBER(22,7)
S_UNIT_M09	NUMBER(22,7)
S_UNIT_M10	NUMBER(22,7)
S_UNIT_M11	NUMBER(22,7)
S_UNIT_M12	NUMBER(22,7)
S_UNIT_M13	NUMBER(22,7)
S_UNIT_M14	NUMBER(22,7)
S_UNIT_M15	NUMBER(22,7)
S_UNIT_M16	NUMBER(22,7)
S_UNIT_M17	NUMBER(22,7)
S_UNIT_M18	NUMBER(22,7)
S_UNIT_M19	NUMBER(22,7)
S_UNIT_M20	NUMBER(22,7)
S_UNIT_M21	NUMBER(22,7)
S_UNIT_M22	NUMBER(22,7)
S_UNIT_M23	NUMBER(22,7)
S_UNIT_M24	NUMBER(22,7)
S_UNIT_M25	NUMBER(22,7)
S_UNIT_M26	NUMBER(22,7)

Syndicated Data Flat File for Prescription Data

Table 98 provides information about the syndicated data flat file for Prescription data.

Table 98. Syndicated Data Flat File - Prescription Data
Field Name	Data Type	Field Required	Field Description
DATASOURCE_NUM_ID	NUMBER(10)	NOT NULL	External data source number ID other than 1.
DATA_SRC_CD	VARCHAR2(30)	NOT NULL
INTEGRATION_ID	VARCHAR2(25)	NOT NULL	External integration ID.
START_DT	VARCHAR2(15)	NOT NULL	Syndicate data date format YYYYMMDD.
AREA_ID	VARCHAR2(30)		External syndicate brick ID. Enter value if DATA_SRC_CD is brick level. Note: Corresponding Siebel S_REGION.ROW_ID should be provided in cross-reference file.
CONTACT_ID	VARCHAR2(30)		External contact ID. Note: Corresponding Siebel S_CONTACT.ROW_ID should be provided in cross-reference file.
CON_AREA_ID	VARCHAR2(30)		External syndicate brick ID of contact's primary address. Enter value if DATA_SRC_CD is brick level. Note: Corresponding Siebel S_REGION.ROW_ID should be provided in cross-reference file.
CON_CITY	VARCHAR2(50)		Contact city. Enter value if DATA_SRC_CD is account level. Initial capital format; for example, Short Hills
CON_COUNTRY	VARCHAR2(30)		Contact country. Enter value if DATA_SRC_CD is account level. Initial capital format; for example, France; or abbreviate format, such as USA.
CON_ZIPCODE	VARCHAR2(30)		Contact zip code. Enter value if DATA_SRC_CD is account level.
PAYER_TYPE_ID	VARCHAR2(30)		External payer type ID. This column is used to define payment type, such as Cash, Government, Third Party, Total, and so on. Note: Corresponding Siebel S_INS_PLAN.ROW_ID should be provided in cross-reference file.
PERIOD_CD	VARCHAR2(30)		Enter the following Siebel period code: Month
PROD_ID	VARCHAR2(30)		External product ID. Note: Corresponding Siebel S_PROD_INT.ROW_ID should be provided in cross-reference file.
ZIPCODE	VARCHAR2(30)		Syndicate zip code. Enter value if DATA_SRC_CD is zip code level.
CONV_FACTOR	NUMBER(22,7)		Applies to Rx data only. Used to convert Rx volume to measurable units which will be used to dollarize the Rx volume.
NRX_M01	NUMBER(22,7)		Stores sales amounts of current month data.
NRX_M02	NUMBER(22,7)		Stores sales amounts of previous month data.
NRX_M03	NUMBER(22,7)		Stores sales amounts of 20050101 data.
NRX_M04	NUMBER(22,7)
NRX_M05	NUMBER(22,7)
NRX_M06	NUMBER(22,7)
NRX_M07	NUMBER(22,7)
NRX_M08	NUMBER(22,7)
NRX_M09	NUMBER(22,7)
NRX_M10	NUMBER(22,7)
NRX_M11	NUMBER(22,7)
NRX_M12	NUMBER(22,7)
NRX_M13	NUMBER(22,7)
NRX_M14	NUMBER(22,7)
NRX_M15	NUMBER(22,7)
NRX_M16	NUMBER(22,7)
NRX_M17	NUMBER(22,7)
NRX_M18	NUMBER(22,7)
NRX_M19	NUMBER(22,7)
NRX_M20	NUMBER(22,7)
NRX_M21	NUMBER(22,7)
NRX_M22	NUMBER(22,7)
NRX_M23	NUMBER(22,7)
NRX_M24	NUMBER(22,7)
NRX_M25	NUMBER(22,7)
NRX_M26	NUMBER(22,7)
TRX_M01	NUMBER(22,7)		Stores sales units of current month data.
TRX_M02	NUMBER(22,7)		Stores sales units of previous month data.
TRX_M03	NUMBER(22,7)		Stores sales units of 20050101 data.
TRX_M04	NUMBER(22,7)		Stores sales units of current month data.
TRX_M05	NUMBER(22,7)
TRX_M06	NUMBER(22,7)
TRX_M07	NUMBER(22,7)
TRX_M08	NUMBER(22,7)
TRX_M09	NUMBER(22,7)
TRX_M10	NUMBER(22,7)
TRX_M11	NUMBER(22,7)
TRX_M12	NUMBER(22,7)
TRX_M13	NUMBER(22,7)
TRX_M14	NUMBER(22,7)
TRX_M15	NUMBER(22,7)
TRX_M16	NUMBER(22,7)
TRX_M17	NUMBER(22,7)
TRX_M18	NUMBER(22,7)
TRX_M19	NUMBER(22,7)
TRX_M20	NUMBER(22,7)
TRX_M21	NUMBER(22,7)
TRX_M22	NUMBER(22,7)
TRX_M23	NUMBER(22,7)
TRX_M24	NUMBER(22,7)
TRX_M25	NUMBER(22,7)
TRX_M26	NUMBER(22,7)

Syndicated Data Flat File for Plan Level Rx Data

Table 99 provides information about the syndicated data flat file for Plan Level Rx data.

Table 99. Syndicated Data Flat File - Plan Level Rx Data
Field Name	Data Type	Field Required	Field Description
DATASOURCE_NUM_ID	NUMBER(10)	NOT NULL	External data source number ID other than 1.
DATA_SRC_CD	VARCHAR2(30)	NOT NULL	Enter the following Siebel data source type code: RXPT
INTEGRATION_ID	VARCHAR2(25)	NOT NULL	External integration ID.
START_DT	VARCHAR2(15)	NOT NULL	Syndicate data date format YYYYMMDD.
ACCNT_ID	VARCHAR2(30)		External account and plan ID. Note: Corresponding Siebel S_ORG_EXT.ROW_ID should be provided in cross-reference file.
ACCNT_AREA_ID	VARCHAR2(30)		External brick ID of account's primary address. Enter value if DATA_SRC_CD is account level. Note: Corresponding Siebel S_REGION.ROW_ID should be provided in cross-reference file.
ACCNT_CITY	VARCHAR2(50)		Account city. Enter value if DATA_SRC_CD is account level. Initial capital format; for example, Short Hills
ACCNT_COUNTRY	VARCHAR2(30)		Account country. Enter value if DATA_SRC_CD is account level. Initial capital format; for example, France; or abbreviate format, such as USA.
ACCNT_ZIPCODE	VARCHAR2(30)		Account zip code. Enter value if DATA_SRC_CD is account level.
PERIOD_CD	VARCHAR2(30)		Enter the following Siebel period code: Month
PROD_ID	VARCHAR2(30)		Applies to syndicated data only. External product ID. Note: Corresponding Siebel S_PROD_INT.ROW_ID should be provided in cross-reference file.
CONV_FACTOR	NUMBER(22,7)		Applies to Rx data only. Used to convert Rx volume to measurable units which will be used to dollarize the Rx volume.
NRX_M01	NUMBER(22,7)		Stores sales amounts of current month data.
NRX_M02	NUMBER(22,7)		Stores sales amounts of previous month data.
NRX_M03	NUMBER(22,7)		Stores sales amounts of 20050101 data.
NRX_M04	NUMBER(22,7)
NRX_M05	NUMBER(22,7)
NRX_M06	NUMBER(22,7)
NRX_M07	NUMBER(22,7)
NRX_M08	NUMBER(22,7)
NRX_M09	NUMBER(22,7)
NRX_M10	NUMBER(22,7)
NRX_M11	NUMBER(22,7)
NRX_M12	NUMBER(22,7)
NRX_M13	NUMBER(22,7)
NRX_M14	NUMBER(22,7)
NRX_M15	NUMBER(22,7)
NRX_M16	NUMBER(22,7)
NRX_M17	NUMBER(22,7)
NRX_M18	NUMBER(22,7)
NRX_M19	NUMBER(22,7)
NRX_M20	NUMBER(22,7)
NRX_M21	NUMBER(22,7)
NRX_M22	NUMBER(22,7)
NRX_M23	NUMBER(22,7)
NRX_M24	NUMBER(22,7)
NRX_M25	NUMBER(22,7)
NRX_M26	NUMBER(22,7)
TRX_M01	NUMBER(22,7)		Stores sales units of current month data.
TRX_M02	NUMBER(22,7)		Stores sales units of previous month data.
TRX_M03	NUMBER(22,7)		Stores sales units of 20050101 data.
TRX_M04	NUMBER(22,7)		Stores sales units of current month data.
TRX_M05	NUMBER(22,7)
TRX_M06	NUMBER(22,7)
TRX_M07	NUMBER(22,7)
TRX_M08	NUMBER(22,7)
TRX_M09	NUMBER(22,7)
TRX_M10	NUMBER(22,7)
TRX_M11	NUMBER(22,7)
TRX_M12	NUMBER(22,7)
TRX_M13	NUMBER(22,7)
TRX_M14	NUMBER(22,7)
TRX_M15	NUMBER(22,7)
TRX_M16	NUMBER(22,7)
TRX_M17	NUMBER(22,7)
TRX_M18	NUMBER(22,7)
TRX_M19	NUMBER(22,7)
TRX_M20	NUMBER(22,7)
TRX_M21	NUMBER(22,7)
TRX_M22	NUMBER(22,7)
TRX_M23	NUMBER(22,7)
TRX_M24	NUMBER(22,7)
TRX_M25	NUMBER(22,7)
TRX_M26	NUMBER(22,7)

Syndicated Data Flat File for Physician Plan Level Rx Data

Table 100 provides information about the syndicated data flat file for Physician Plan Level Rx data.

Table 100. Syndicated Data Flat File - Physician Plan Level Rx Data
Field Name	Data Type	Field Required	Field Description
DATASOURCE_NUM_ID	NUMBER(10)	NOT NULL	External data source number ID other than 1.
DATA_SRC_CD	VARCHAR2(30)	NOT NULL	Enter the following Siebel data source type code: RXXPT
INTEGRATION_ID	VARCHAR2(25)	NOT NULL	External integration ID.
START_DT	VARCHAR2(15)	NOT NULL	Syndicate data date format YYYYMMDD.
ACCNT_ID	VARCHAR2(30)		External account ID. Note: Corresponding Siebel S_ORG_EXT.ROW_ID should be provided in cross-reference file.
ACCNT_AREA_ID	VARCHAR2(30)		External brick ID of account's primary address. Enter value if DATA_SRC_CD is brick level. Note: Corresponding Siebel S_REGION.ROW_ID should be provided in cross-reference file.
ACCNT_CITY	VARCHAR2(50)		Account city. Enter a value if DATA_SRC_CD is account level. Initial capital format; for example, Short Hills
ACCNT_COUNTRY	VARCHAR2(30)		Account country. Enter value if DATA_SRC_CD is account level. Initial capital format; for example, France; or abbreviate format, such as USA.
ACCNT_ZIPCODE	VARCHAR2(30)		Account zip code. Enter value if DATA_SRC_CD is account level.
CONTACT_ID	VARCHAR2(30)		External contact ID. Note: Corresponding Siebel S_CONTACT.ROW_ID should be provided in cross-reference file.
CON_AREA_ID	VARCHAR2(30)		External brick ID of contact's primary address. Enter value if DATA_SRC_CD is contact level. Note: Corresponding Siebel S_REGION.ROW_ID should be provided in cross-reference file.
CON_CITY	VARCHAR2(50)		Contact city. Enter value if DATA_SRC_CD is contact level. Initial capital format; for example, Short Hills
CON_COUNTRY	VARCHAR2(30)		Contact country. Enter value if DATA_SRC_CD is contact level. Initial capital format; for example, France; or abbreviate format, such as USA.
CON_ZIPCODE	VARCHAR2(30)		Contact zip code. Enter value if DATA_SRC_CD is contact level.
PERIOD_CD	VARCHAR2(30)		Enter the following Siebel period code: Month
PROD_ID	VARCHAR2(30)		Applies to syndicated data only. External product ID. Note: Corresponding Siebel S_PROD_INT.ROW_ID should be provided in cross-reference file.
CONV_FACTOR	NUMBER(22,7)		Applies to Rx data only. Used to convert Rx volume to measurable units which will be used to dollarize the Rx volume.
NRX_M01	NUMBER(22,7)		Stores sales amounts of current month data.
NRX_M02	NUMBER(22,7)		Stores sales amounts of previous month data.
NRX_M03	NUMBER(22,7)		Stores sales amounts of 20050101 data.
NRX_M04	NUMBER(22,7)
NRX_M05	NUMBER(22,7)
NRX_M06	NUMBER(22,7)
NRX_M07	NUMBER(22,7)
NRX_M08	NUMBER(22,7)
NRX_M09	NUMBER(22,7)
NRX_M10	NUMBER(22,7)
NRX_M11	NUMBER(22,7)
NRX_M12	NUMBER(22,7)
NRX_M13	NUMBER(22,7)
NRX_M14	NUMBER(22,7)
NRX_M15	NUMBER(22,7)
NRX_M16	NUMBER(22,7)
NRX_M17	NUMBER(22,7)
NRX_M18	NUMBER(22,7)
NRX_M19	NUMBER(22,7)
NRX_M20	NUMBER(22,7)
NRX_M21	NUMBER(22,7)
NRX_M22	NUMBER(22,7)
NRX_M23	NUMBER(22,7)
NRX_M24	NUMBER(22,7)
NRX_M25	NUMBER(22,7)
NRX_M26	NUMBER(22,7)
TRX_M01	NUMBER(22,7)		Stores sales units of current month data.
TRX_M02	NUMBER(22,7)		Stores sales units of previous month data.
TRX_M03	NUMBER(22,7)		Stores sales units of 20050101 data.
TRX_M04	NUMBER(22,7)		Stores sales units of current month data.
TRX_M05	NUMBER(22,7)
TRX_M06	NUMBER(22,7)
TRX_M07	NUMBER(22,7)
TRX_M08	NUMBER(22,7)
TRX_M09	NUMBER(22,7)
TRX_M10	NUMBER(22,7)
TRX_M11	NUMBER(22,7)
TRX_M12	NUMBER(22,7)
TRX_M13	NUMBER(22,7)
TRX_M14	NUMBER(22,7)
TRX_M15	NUMBER(22,7)
TRX_M16	NUMBER(22,7)
TRX_M17	NUMBER(22,7)
TRX_M18	NUMBER(22,7)
TRX_M19	NUMBER(22,7)
TRX_M20	NUMBER(22,7)
TRX_M21	NUMBER(22,7)
TRX_M22	NUMBER(22,7)
TRX_M23	NUMBER(22,7)
TRX_M24	NUMBER(22,7)
TRX_M25	NUMBER(22,7)
TRX_M26	NUMBER(22,7)

Syndicated Data Flat File for Weekly Early View Data

Table 101 provides information about the syndicated data flat file for Weekly Early View data.

Table 101. Syndicated Data Flat File - Weekly Early View Data
Field Name	Data Type	Field Required	Field Description
DATASOURCE_NUM_ID	NUMBER(10)	NOT NULL	External data source number ID other than 1.
DATA_SRC_CD	VARCHAR2(30)	NOT NULL	Enter the following Siebel data source type code: RXEVM
INTEGRATION_ID	VARCHAR2(25)	NOT NULL	External integration ID.
START_DT	VARCHAR2(15)	NOT NULL	Syndicate data date format YYYYMMDD.
CONTACT_ID	VARCHAR2(30)		External contact ID. Note: Corresponding Siebel S_CONTACT.ROW_ID should be provided in cross-reference file.
CON_AREA_ID	VARCHAR2(30)		External brick ID of contact's primary address. Enter value if DATA_SRC_CD is brick level. Note: Corresponding Siebel S_REGION.ROW_ID should be provided in cross-reference file.
CON_CITY	VARCHAR2(50)		Contact city. Enter a value if DATA_SRC_CD is account level. Initial capital format; for example, Short Hills
CON_COUNTRY	VARCHAR2(30)		Contact country. Enter value if DATA_SRC_CD is account level. Initial capital format; for example, France; or abbreviate format, such as USA.
CON_ZIPCODE	VARCHAR2(30)		Contact zip code. Enter value if DATA_SRC_CD is account level.
MARKET_ID	VARCHAR2(30)		Applies to syndicated market only. External market ID for product. Note: Corresponding Siebel S_PROD_INT.ROW_ID should be provided in cross-reference file.
PAYER_TYPE_ID	VARCHAR2(30)		External payer type ID. This column is used to define payment type, such as Cash, Government, Third Party, Total, and so on. Note: Corresponding Siebel S_INS_PLAN.ROW_ID should be provided in cross-reference file.
PERIOD_CD	VARCHAR2(30)		Enter the following Siebel period code: Week
PROD_ID	VARCHAR2(30)		Applies to syndicated data only. External product ID. Note: Corresponding Siebel S_PROD_INT.ROW_ID should be provided in cross-reference file.
CONV_FACTOR	NUMBER(22,7)		Applies to Rx data only. Used to convert Rx volume to measurable units which will be used to dollarize the Rx volume.
NRX_W01	NUMBER(22,7)		Stores sales amounts of current month data.
NRX_W02	NUMBER(22,7)		Stores sales amounts of previous month data.
NRX_W03	NUMBER(22,7)		Stores sales amounts of 20050101 data.
NRX_W04	NUMBER(22,7)
NRX_W05	NUMBER(22,7)
NRX_W06	NUMBER(22,7)
NRX_W07	NUMBER(22,7)
NRX_WM08	NUMBER(22,7)
NRX_W09	NUMBER(22,7)
NRX_W10	NUMBER(22,7)
NRX_W11	NUMBER(22,7)
NRX_W12	NUMBER(22,7)
NRX_W13	NUMBER(22,7)
NRX_W14	NUMBER(22,7)
TRX_W01	NUMBER(22,7)		Stores sales units of current month data.
TRX_W02	NUMBER(22,7)		Stores sales units of previous month data.
TRX_W03	NUMBER(22,7)		Stores sales units of 20050101 data.
TRX_W04	NUMBER(22,7)		Stores sales units of current month data.
TRX_W05	NUMBER(22,7)
TRX_WM06	NUMBER(22,7)
TRX_W07	NUMBER(22,7)
TRX_W08	NUMBER(22,7)
TRX_W09	NUMBER(22,7)
TRX_W10	NUMBER(22,7)
TRX_W11	NUMBER(22,7)
TRX_W12	NUMBER(22,7)
TRX_W13	NUMBER(22,7)

Syndicated Market Flat File for Indirect Sales Data

Table 102 provides information about the syndicated market flat file for Indirect Sales data.

Table 102. Syndicated Market Flat File - Indirect Sales Data
Field Name	Data Type	Field Required	Field Description
DATASOURCE_NUM_ID	NUMBER(10)	NOT NULL	External data source number ID other than 1.
DATA_SRC_CD	VARCHAR2(30)	NOT NULL	Enter the following Siebel data source type code: SlsIndAct (Indirect sales account level) SlsIndZip (Indirect sales zip code level) SlsIndBrk (Indirect sales brick level)
INTEGRATION_ID	VARCHAR2(25)	NOT NULL	External integration ID.
START_DT	VARCHAR2(15)	NOT NULL	Syndicate data date format YYYYMMDD.
MKT_AMT_DT	VARCHAR2(15)		Exchange date for currency code. Date format YYYYMMDD.
ACCNT_ID	VARCHAR2(30)		External account ID. Note: Corresponding Siebel S_ORG_EXT.ROW_ID should be provided in cross-reference file.
ACCNT_AREA_ID	VARCHAR2(30)		External brick ID of account's primary address for account level. Note: Corresponding Siebel S_REGION_EXT.ROW_ID (S_CON_ADDR.BRICK_ID) should be provided in cross-reference file.
ACCNT_CITY	VARCHAR2(50)		Account city. Enter value if DATA_SRC_CD is account level. Initial capital format; for example, Short Hills.
ACCNT_COUNTRY	VARCHAR2(30)		Account country. Enter value if DATA_SRC_CD is account level. Initial capital format; for example, France; or abbreviate format, such as USA.
ACCNT_ZIPCODE	VARCHAR2(30)		Account zip code. Enter value if DATA_SRC_CD is account level.
AREA_ID	VARCHAR2(30)		External syndicate brick ID. Enter value if DATA_SRC_CD is brick level. Note: Corresponding Siebel S_REGION.ROW_ID should be provided in cross-reference file.
MARKET_ID	VARCHAR2(30)		External market ID for the product. Note: Corresponding Siebel S_PROD_INT.ROW_ID should be provided in cross-reference file.
MKT_AMT_CURCY_CD	VARCHAR2(30)		Currency code for Amount.
PAYER_TYPE_ID	VARCHAR2(30)		External payer type ID. This column is used to define payment type, such as Cash, Government, Third Party, Total, and so on. Note: Corresponding Siebel S_INS_PLAN.ROW_ID should be provided in cross-reference file.
PERIOD_CD	VARCHAR2(30)		Enter the following Siebel period code: Month
PROD_ID	VARCHAR2(30)		Applies to syndicated data only. External product ID. Note: Corresponding Siebel S_PROD_INT.ROW_ID should be provided in cross-reference file.
ZIPCODE	VARCHAR2(30)		Syndicate zip code. Enter value if DATA_SRC_CD is zip code level.
MKT_S_AMT_M01	NUMBER(22,7)		Stores sales amounts of current month data.
MKT_S_AMT_M02	NUMBER(22,7)		Stores sales amounts of previous month data.
MKT_S_AMT_M03	NUMBER(22,7)		Stores sales amounts of 20050101 data.
MKT_S_AMT_M04	NUMBER(22,7)
MKT_S_AMT_M05	NUMBER(22,7)
MKT_S_AMT_M06	NUMBER(22,7)
MKT_S_AMT_M07	NUMBER(22,7)
MKT_S_AMT_M08	NUMBER(22,7)
MKT_S_AMT_M09	NUMBER(22,7)
MKT_S_AMT_M10	NUMBER(22,7)
MKT_S_AMT_M11	NUMBER(22,7)
MKT_S_AMT_M12	NUMBER(22,7)
MKT_S_AMT_M13	NUMBER(22,7)
MKT_S_AMT_M14	NUMBER(22,7)
MKT_S_AMT_M15	NUMBER(22,7)
MKT_S_AMT_M16	NUMBER(22,7)
MKT_S_AMT_M17	NUMBER(22,7)
MKT_S_AMT_M18	NUMBER(22,7)
MKT_S_AMT_M19	NUMBER(22,7)
MKT_S_AMT_M20	NUMBER(22,7)
MKT_S_AMT_M21	NUMBER(22,7)
MKT_S_AMT_M22	NUMBER(22,7)
MKT_S_AMT_M23	NUMBER(22,7)
MKT_S_AMT_M24	NUMBER(22,7)
MKT_S_AMT_M25	NUMBER(22,7)
MKT_S_AMT_M26	NUMBER(22,7)
MKT_S_UNIT_M01	NUMBER(22,7)		Stores sales units of current month data.
MKT_S_UNIT_M02	NUMBER(22,7)		Stores sales units of previous month data.
MKT_S_UNIT_M03	NUMBER(22,7)		Stores sales units of 20050101 data.
MKT_S_UNIT_M04	NUMBER(22,7)		Stores sales units of current month data.
MKT_S_UNIT_M05	NUMBER(22,7)
MKT_S_UNIT_M06	NUMBER(22,7)
MKT_S_UNIT_M07	NUMBER(22,7)
MKT_S_UNIT_M08	NUMBER(22,7)
MKT_S_UNIT_M09	NUMBER(22,7)
MKT_S_UNIT_M10	NUMBER(22,7)
MKT_S_UNIT_M11	NUMBER(22,7)
MKT_S_UNIT_M12	NUMBER(22,7)
MKT_S_UNIT_M13	NUMBER(22,7)
MKT_S_UNIT_M14	NUMBER(22,7)
MKT_S_UNIT_M15	NUMBER(22,7)
MKT_S_UNIT_M16	NUMBER(22,7)
MKT_S_UNIT_M17	NUMBER(22,7)
MKT_S_UNIT_M18	NUMBER(22,7)
MKT_S_UNIT_M19	NUMBER(22,7)
MKT_S_UNIT_M20	NUMBER(22,7)
MKT_S_UNIT_M21	NUMBER(22,7)
MKT_S_UNIT_M22	NUMBER(22,7)
MKT_S_UNIT_M23	NUMBER(22,7)
MKT_S_UNIT_M24	NUMBER(22,7)
MKT_S_UNIT_M25	NUMBER(22,7)
MKT_S_UNIT_M26	NUMBER(22,7)

Syndicated Market Flat File for Prescription Data

Table 103 provides information about the syndicated market flat file for Prescription data.

Table 103. Syndicated Market Flat File - Prescription Data
Field Name	Data Type	Field Required	Field Description
DATASOURCE_NUM_ID	NUMBER(10)	NOT NULL	External data source number ID other than 1.
DATA_SRC_CD	VARCHAR2(30)	NOT NULL
INTEGRATION_ID	VARCHAR2(25)	NOT NULL	External integration ID.
START_DT	VARCHAR2(15)	NOT NULL	Syndicate data date format YYYYMMDD.
AREA_ID	VARCHAR2(30)		External syndicate brick ID. Enter value if DATA_SRC_CD is brick level. Note: Corresponding Siebel S_REGION.ROW_ID should be provided in cross-reference file.
CONTACT_ID	VARCHAR2(30)		External contact ID. Note: Corresponding Siebel S_CONTACT.ROW_ID should be provided in cross-reference file.
CON_AREA_ID	VARCHAR2(30)		External syndicate brick ID of contact's primary address. Enter value if DATA_SRC_CD is brick level. Note: Corresponding Siebel S_REGION.ROW_ID should be provided in cross-reference file.
CON_CITY	VARCHAR2(50)		Contact city. Enter value if DATA_SRC_CD is account level. Initial capital format; for example, Short Hills
CON_COUNTRY	VARCHAR2(30)		Contact country. Enter value if DATA_SRC_CD is account level. Initial capital format; for example, France; or abbreviate format, such as USA.
CON_ZIPCODE	VARCHAR2(30)		Contact zip code. Enter value if DATA_SRC_CD is account level.
MARKET_ID	VARCHAR2(30)		External market ID for the product. Note: Corresponding Siebel S_PROD_INT.ROW_ID should be provided in cross-reference file.
PAYER_TYPE_ID	VARCHAR2(30)		External payer type ID. This column is used to define payment type, such as Cash, Government, Third Party, Total, and so on. Note: Corresponding Siebel S_INS_PLAN.ROW_ID should be provided in cross-reference file.
PERIOD_CD	VARCHAR2(30)		Enter the following Siebel period code: Month
ZIPCODE	VARCHAR2(30)		Syndicate zip code. Enter value if DATA_SRC_CD is zip code level.
CONV_FACTOR	NUMBER(22,7)		Applies to Rx data only. Used to convert Rx volume to measurable units which will be used to dollarize the Rx volume.
MKT_NRX_M01	NUMBER(22,7)		Stores sales amounts of current month data.
MKT_NRX_M02	NUMBER(22,7)		Stores sales amounts of previous month data.
MKT_NRX_M03	NUMBER(22,7)		Stores sales amounts of 20050101 data.
MKT_NRX_M04	NUMBER(22,7)
MKT_NRX_M05	NUMBER(22,7)
MKT_NRX_M06	NUMBER(22,7)
MKT_NRX_M07	NUMBER(22,7)
MKT_NRX_M08	NUMBER(22,7)
MKT_NRX_M09	NUMBER(22,7)
MKT_NRX_M10	NUMBER(22,7)
MKT_NRX_M11	NUMBER(22,7)
MKT_NRX_M12	NUMBER(22,7)
MKT_NRX_M13	NUMBER(22,7)
MKT_NRX_M14	NUMBER(22,7)
MKT_NRX_M15	NUMBER(22,7)
MKT_NRX_M16	NUMBER(22,7)
MKT_NRX_M17	NUMBER(22,7)
MKT_NRX_M18	NUMBER(22,7)
MKT_NRX_M19	NUMBER(22,7)
MKT_NRX_M20	NUMBER(22,7)
MKT_NRX_M21	NUMBER(22,7)
MKT_NRX_M22	NUMBER(22,7)
MKT_NRX_M23	NUMBER(22,7)
MKT_NRX_M24	NUMBER(22,7)
MKT_NRX_M25	NUMBER(22,7)
MKT_NRX_M26	NUMBER(22,7)
MKT_TRX_M01	NUMBER(22,7)		Stores sales units of current month data.
MKT_TRX_M02	NUMBER(22,7)		Stores sales units of previous month data.
MKT_TRX_M03	NUMBER(22,7)		Stores sales units of 20050101 data.
MKT_TRX_M04	NUMBER(22,7)		Stores sales units of current month data.
MKT_TRX_M05	NUMBER(22,7)
MKT_TRX_M06	NUMBER(22,7)
MKT_TRX_M07	NUMBER(22,7)
MKT_TRX_M08	NUMBER(22,7)
MKT_TRX_M09	NUMBER(22,7)
MKT_TRX_M10	NUMBER(22,7)
MKT_TRX_M11	NUMBER(22,7)
MKT_TRX_M12	NUMBER(22,7)
MKT_TRX_M13	NUMBER(22,7)
MKT_TRX_M14	NUMBER(22,7)
MKT_TRX_M15	NUMBER(22,7)
MKT_TRX_M16	NUMBER(22,7)
MKT_TRX_M17	NUMBER(22,7)
MKT_TRX_M18	NUMBER(22,7)
MKT_TRX_M19	NUMBER(22,7)
MKT_TRX_M20	NUMBER(22,7)
MKT_TRX_M21	NUMBER(22,7)
MKT_TRX_M22	NUMBER(22,7)
MKT_TRX_M23	NUMBER(22,7)
MKT_TRX_M24	NUMBER(22,7)
MKT_TRX_M25	NUMBER(22,7)
MKT_TRX_M26	NUMBER(22,7)

Syndicated Market Flat File for Plan Level Rx Data

Table 104 provides information about the syndicated market flat file for Plan Level Rx data.

Table 104. Syndicated Market Flat File - Plan Level Rx Data
Field Name	Data Type	Field Required	Field Description
DATASOURCE_NUM_ID	NUMBER(10)	NOT NULL	External data source number ID other than 1.
DATA_SRC_CD	VARCHAR2(30)	NOT NULL	Enter the following Siebel data source type code: RXPT
INTEGRATION_ID	VARCHAR2(25)	NOT NULL	External integration ID.
START_DT	VARCHAR2(15)	NOT NULL	Syndicate data date format YYYYMMDD.
ACCNT_ID	VARCHAR2(30)		External account and plan ID. Note: Corresponding Siebel S_ORG_EXT.ROW_ID should be provided in cross-reference file.
ACCNT_AREA_ID	VARCHAR2(30)		External brick ID of account's primary address. Enter value if DATA_SRC_CD is account level. Note: Corresponding Siebel S_REGION.ROW_ID should be provided in cross-reference file.
ACCNT_CITY	VARCHAR2(50)		Account city. Enter value if DATA_SRC_CD is account level. Initial capital format; for example, Short Hills
ACCNT_COUNTRY	VARCHAR2(30)		Account country. Enter value if DATA_SRC_CD is account level. Initial capital format; for example, France; or abbreviate format, such as USA.
ACCNT_ZIPCODE	VARCHAR2(30)		Account zip code. Enter value if DATA_SRC_CD is account level.
MARKET_ID	VARCHAR2(30)		External market ID for the product. Note: Corresponding Siebel S_PROD_INT.ROW_ID should be provided in cross-reference file.
PERIOD_CD	VARCHAR2(30)		Enter the following Siebel period code: Month
CONV_FACTOR	NUMBER(22,7)		Applies to Rx data only. Used to convert Rx volume to measurable units which will be used to dollarize the Rx volume.
MKT_NRX_M01	NUMBER(22,7)		Stores sales amounts of current month data.
MKT_NRX_M02	NUMBER(22,7)		Stores sales amounts of previous month data.
MKT_NRX_M03	NUMBER(22,7)		Stores sales amounts of 20050101 data.
MKT_NRX_M04	NUMBER(22,7)
MKT_NRX_M05	NUMBER(22,7)
MKT_NRX_M06	NUMBER(22,7)
MKT_NRX_M07	NUMBER(22,7)
MKT_NRX_M08	NUMBER(22,7)
MKT_NRX_M09	NUMBER(22,7)
MKT_NRX_M10	NUMBER(22,7)
MKT_NRX_M11	NUMBER(22,7)
MKT_NRX_M12	NUMBER(22,7)
MKT_NRX_M13	NUMBER(22,7)
MKT_NRX_M14	NUMBER(22,7)
MKT_NRX_M15	NUMBER(22,7)
MKT_NRX_M16	NUMBER(22,7)
MKT_NRX_M17	NUMBER(22,7)
MKT_NRX_M18	NUMBER(22,7)
MKT_NRX_M19	NUMBER(22,7)
MKT_NRX_M20	NUMBER(22,7)
MKT_NRX_M21	NUMBER(22,7)
MKT_NRX_M22	NUMBER(22,7)
MKT_NRX_M23	NUMBER(22,7)
MKT_NRX_M24	NUMBER(22,7)
MKT_NRX_M25	NUMBER(22,7)
MKT_NRX_M26	NUMBER(22,7)
MKT_TRX_M01	NUMBER(22,7)		Stores sales units of current month data.
MKT_TRX_M02	NUMBER(22,7)		Stores sales units of previous month data.
MKT_TRX_M03	NUMBER(22,7)		Stores sales units of 20050101 data.
MKT_TRX_M04	NUMBER(22,7)		Stores sales units of current month data.
MKT_TRX_M05	NUMBER(22,7)
MKT_TRX_M06	NUMBER(22,7)
MKT_TRX_M07	NUMBER(22,7)
MKT_TRX_M08	NUMBER(22,7)
MKT_TRX_M09	NUMBER(22,7)
MKT_TRX_M10	NUMBER(22,7)
MKT_TRX_M11	NUMBER(22,7)
MKT_TRX_M12	NUMBER(22,7)
MKT_TRX_M13	NUMBER(22,7)
MKT_TRX_M14	NUMBER(22,7)
MKT_TRX_M15	NUMBER(22,7)
MKT_TRX_M16	NUMBER(22,7)
MKT_TRX_M17	NUMBER(22,7)
MKT_TRX_M18	NUMBER(22,7)
MKT_TRX_M19	NUMBER(22,7)
MKT_TRX_M20	NUMBER(22,7)
MKT_TRX_M21	NUMBER(22,7)
MKT_TRX_M22	NUMBER(22,7)
MKT_TRX_M23	NUMBER(22,7)
MKT_TRX_M24	NUMBER(22,7)
MKT_TRX_M25	NUMBER(22,7)
MKT_TRX_M26	NUMBER(22,7)

Syndicated Market Flat File for Physician Plan Level Rx Data

Table 105 provides information about the syndicated market flat file for Physician Plan Level Rx data.

Table 105. Syndicated Market Flat File - Physician Plan Level Rx Data
Field Name	Data Type	Field Required	Field Description
DATASOURCE_NUM_ID	NUMBER(10)	NOT NULL	External data source number ID other than 1.
DATA_SRC_CD	VARCHAR2(30)	NOT NULL	Enter the following Siebel data source type code: RXXPT
INTEGRATION_ID	VARCHAR2(25)	NOT NULL	External integration ID.
START_DT	VARCHAR2(15)	NOT NULL	Syndicate data date format YYYYMMDD.
ACCNT_ID	VARCHAR2(30)		External account ID. Note: Corresponding Siebel S_ORG_EXT.ROW_ID should be provided in cross-reference file.
ACCNT_AREA_ID	VARCHAR2(30)		External brick ID of account's primary address. Enter value if DATA_SRC_CD is brick level. Note: Corresponding Siebel S_REGION.ROW_ID should be provided in cross-reference file.
ACCNT_CITY	VARCHAR2(50)		Account city. Enter a value if DATA_SRC_CD is account level. Initial capital format; for example, Short Hills
ACCNT_COUNTRY	VARCHAR2(30)		Account country. Enter value if DATA_SRC_CD is account level. Initial capital format; for example, France; or abbreviate format, such as USA.
ACCNT_ZIPCODE	VARCHAR2(30)		Account zip code. Enter value if DATA_SRC_CD is account level.
CONTACT_ID	VARCHAR2(30)		External contact ID. Note: Corresponding Siebel S_CONTACT.ROW_ID should be provided in cross-reference file.
CON_AREA_ID	VARCHAR2(30)		External brick ID of contact's primary address. Enter value if DATA_SRC_CD is contact level. Note: Corresponding Siebel S_REGION.ROW_ID should be provided in cross-reference file.
CON_CITY	VARCHAR2(50)		Contact city. Enter value if DATA_SRC_CD is contact level. Initial capital format; for example, Short Hills
CON_COUNTRY	VARCHAR2(30)		Contact country. Enter value if DATA_SRC_CD is contact level. Initial capital format; for example, France; or abbreviate format, such as USA.
CON_ZIPCODE	VARCHAR2(30)		Contact zip code. Enter value if DATA_SRC_CD is contact level.
MARKET_ID	VARCHAR2(30)		External market ID for product. Note: Corresponding Siebel S_PROD_INT.ROW_ID should be provided in cross-reference file.
PERIOD_CD	VARCHAR2(30)		Enter the following Siebel period code: Month
CONV_FACTOR	NUMBER(22,7)		Applies to Rx data only. Used to convert Rx volume to measurable units which will be used to dollarize the Rx volume.
MKT_NRX_M01	NUMBER(22,7)		Stores sales amounts of current month data.
MKT_NRX_M02	NUMBER(22,7)		Stores sales amounts of previous month data.
MKT_NRX_M03	NUMBER(22,7)		Stores sales amounts of 20050101 data.
MKT_NRX_M04	NUMBER(22,7)
MKT_NRX_M05	NUMBER(22,7)
MKT_NRX_M06	NUMBER(22,7)
MKT_NRX_M07	NUMBER(22,7)
MKT_NRX_M08	NUMBER(22,7)
MKT_NRX_M09	NUMBER(22,7)
MKT_NRX_M10	NUMBER(22,7)
MKT_NRX_M11	NUMBER(22,7)
MKT_NRX_M12	NUMBER(22,7)
MKT_NRX_M13	NUMBER(22,7)
MKT_NRX_M14	NUMBER(22,7)
MKT_NRX_M15	NUMBER(22,7)
MKT_NRX_M16	NUMBER(22,7)
MKT_NRX_M17	NUMBER(22,7)
MKT_NRX_M18	NUMBER(22,7)
MKT_NRX_M19	NUMBER(22,7)
MKT_NRX_M20	NUMBER(22,7)
MKT_NRX_M21	NUMBER(22,7)
MKT_NRX_M22	NUMBER(22,7)
MKT_NRX_M23	NUMBER(22,7)
MKT_NRX_M24	NUMBER(22,7)
MKT_NRX_M25	NUMBER(22,7)
MKT_NRX_M26	NUMBER(22,7)
MKT_TRX_M01	NUMBER(22,7)		Stores sales units of current month data.
MKT_TRX_M02	NUMBER(22,7)		Stores sales units of previous month data.
MKT_TRX_M03	NUMBER(22,7)		Stores sales units of 20050101 data.
MKT_TRX_M04	NUMBER(22,7)		Stores sales units of current month data.
MKT_TRX_M05	NUMBER(22,7)
MKT_TRX_M06	NUMBER(22,7)
MKT_TRX_M07	NUMBER(22,7)
MKT_TRX_M08	NUMBER(22,7)
MKT_TRX_M09	NUMBER(22,7)
MKT_TRX_M10	NUMBER(22,7)
MKT_TRX_M11	NUMBER(22,7)
MKT_TRX_M12	NUMBER(22,7)
MKT_TRX_M13	NUMBER(22,7)
MKT_TRX_M14	NUMBER(22,7)
MKT_TRX_M15	NUMBER(22,7)
MKT_TRX_M16	NUMBER(22,7)
MKT_TRX_M17	NUMBER(22,7)
MKT_TRX_M18	NUMBER(22,7)
MKT_TRX_M19	NUMBER(22,7)
MKT_TRX_M20	NUMBER(22,7)
MKT_TRX_M21	NUMBER(22,7)
MKT_TRX_M22	NUMBER(22,7)
MKT_TRX_M23	NUMBER(22,7)
MKT_TRX_M24	NUMBER(22,7)
MKT_TRX_M25	NUMBER(22,7)
MKT_TRX_M26	NUMBER(22,7)

Syndicated Market Flat File for Weekly Early View Data

Table 106 provides information about the syndicated market flat file for Weekly Early View data.

Table 106. Syndicated Market Flat File - Weekly Early View Data
Field Name	Data Type	Field Required	Field Description
DATASOURCE_NUM_ID	NUMBER(10)	NOT NULL	External data source number ID other than 1.
DATA_SRC_CD	VARCHAR2(30)	NOT NULL	Enter the following Siebel data source type code: RXEVM
INTEGRATION_ID	VARCHAR2(25)	NOT NULL	External integration ID.
START_DT	VARCHAR2(15)	NOT NULL	Syndicate data date format YYYYMMDD.
CONTACT_ID	VARCHAR2(30)		External contact ID. Note: Corresponding Siebel S_CONTACT.ROW_ID should be provided in cross-reference file.
CON_AREA_ID	VARCHAR2(30)		External brick ID of contact's primary address. Enter value if DATA_SRC_CD is brick level. Note: Corresponding Siebel S_REGION.ROW_ID should be provided in cross-reference file.
CON_CITY	VARCHAR2(50)		Contact city. Enter a value if DATA_SRC_CD is account level. Initial capital format; for example, Short Hills
CON_COUNTRY	VARCHAR2(30)		Contact country. Enter value if DATA_SRC_CD is account level. Initial capital format; for example, France; or abbreviate format, such as USA.
CON_ZIPCODE	VARCHAR2(30)		Contact zip code. Enter value if DATA_SRC_CD is account level.
MARKET_ID	VARCHAR2(30)		External market ID for product. Note: Corresponding Siebel S_PROD_INT.ROW_ID should be provided in cross-reference file.
PAYER_TYPE_ID	VARCHAR2(30)		External payer type ID. This column is used to define payment type, such as Cash, Government, Third Party, Total, and so on. Note: Corresponding Siebel S_INS_PLAN.ROW_ID should be provided in cross-reference file.
PERIOD_CD	VARCHAR2(30)		Enter the following Siebel period code: Week
CONV_FACTOR	NUMBER(22,7)		Applies to Rx data only. Used to convert Rx volume to measurable units which will be used to dollarize the Rx volume.
MKT_NRX_W01	NUMBER(22,7)		Stores sales amounts of current month data.
MKT_NRX_W02	NUMBER(22,7)		Stores sales amounts of previous month data.
MKT_NRX_W03	NUMBER(22,7)		Stores sales amounts of 20050101 data.
MKT_NRX_W04	NUMBER(22,7)
MKT_NRX_W05	NUMBER(22,7)
MKT_NRX_W06	NUMBER(22,7)
MKT_NRX_W07	NUMBER(22,7)
MKT_NRX_WM08	NUMBER(22,7)
MKT_NRX_W09	NUMBER(22,7)
MKT_NRX_W10	NUMBER(22,7)
MKT_NRX_W11	NUMBER(22,7)
MKT_NRX_W12	NUMBER(22,7)
MKT_NRX_W13	NUMBER(22,7)
MKT_NRX_W14	NUMBER(22,7)
MKT_TRX_W01	NUMBER(22,7)		Stores sales units of current month data.
MKT_TRX_W02	NUMBER(22,7)		Stores sales units of previous month data.
MKT_TRX_W03	NUMBER(22,7)		Stores sales units of 20050101 data.
MKT_TRX_W04	NUMBER(22,7)		Stores sales units of current month data.
MKT_TRX_W05	NUMBER(22,7)
MKT_TRX_WM06	NUMBER(22,7)
MKT_TRX_W07	NUMBER(22,7)
MKT_TRX_W08	NUMBER(22,7)
MKT_TRX_W09	NUMBER(22,7)
MKT_TRX_W10	NUMBER(22,7)
MKT_TRX_W11	NUMBER(22,7)
MKT_TRX_W12	NUMBER(22,7)
MKT_TRX_W13	NUMBER(22,7)

Flat File Cross-Reference Data Population Rules

Table 107 provides information about flat file cross-reference data population rules for the following data types: Area (Brick), Account and Plan, Payer Type, and Contact.

Table 107. Flat File Cross-Reference Data Population Rules
Field Name	Data Type	Field Required	Field Description
Area (Brick) Data
EXT_DATASRC_NUM_ID	NUMBER(10)	NOT NULL	External data source number ID other than 1.
EXT_INTEGRATION_ID	VARCHAR2(30)	NOT NULL	External area (brick) ID. Note: This same ID should be used in ACCNT_AREA_ID, AREA_ID, AND CON_AREA_ID syndicated data and syndicated market flat files.
EXT_AREA_NAME	VARCHAR2(50)		Area name. Note: The same value should already be loaded in Siebel S_REGION.NAME.
EXT_PROVINCE	VARCHAR2(50)		Area province. Note: The same value should already be loaded in Siebel S_REGION.PROVINCE.
EXT_STATE	VARCHAR2(50)		Area state. Note: The same value should already be loaded in Siebel S_REGION.STATE. Use the format all capitals and abbreviated, such as NJ.
EXT_COUNTRY	VARCHAR2(30)		Area country. Note: The same value should already be loaded in Siebel S_REGION.COUNTRY. Use the initial capital format; for example, France.
INTEGRATION_ID	VARCHAR2(30)	NOT NULL	Siebel area (brick) ID. Note: This value should come from Siebel S_REGION.ROW_ID.
Account and Plan Data
EXT_DATASRC_NUM_ID	NUMBER(10)	NOT NULL	External data source number ID other than 1. Note: The same value should be used in the DATASOURCE_NUM_ID field of the syndicated data and syndicated market flat files.
EXT_INTEGRATION_ID	VARCHAR2(30)	NOT NULL	External account and plan ID. Note: The same ID should be used in ACCNT_AREA_ID, AREA_ID, AND CON_AREA_ID syndicated data and syndicated market flat files.
EXT_ACCNT_NAME	VARCHAR2(100)		Account and plan name. Note: The same value should already be loaded in Siebel S_ORG_EXT.NAME.
EXT_ACCNT_NUM	VARCHAR2(30)		Account outlet number.
INTEGRATION_ID	VARCHAR2(30)	NOT NULL	Siebel account ID. Note: This value should come from Siebel S_REGION.ROW_ID.
Payer Type Data
EXT_DATASRC_NUM_ID	NUMBER(10)	NOT NULL	External data source number ID other than 1. Note: The same value should be used in the DATASOURCE_NUM_ID field of the syndicated data and syndicated market flat files.
EXT_INTEGRATION_ID	VARCHAR2(30)	NOT NULL	External payer ID. Note: The same value should be used in the PAYER_TYPE_ID field of the syndicated data and syndicated market flat files.
EXT_PLAN_TYPE	VARCHAR2(30)		Plan type, such as Payer, Plan, and so on. Note: The same value should already be loaded in Siebel S_INS_PLAN.PLAN_TYPE.
EXT_PAYER_NAME	VARCHAR2(100)		Payer type, such as Cash, Government, Third Party, Total, and so on. Note: The same value should already be loaded in Siebel S_INS_PLAN.NAME.
INTEGRATION_ID	VARCHAR2(30)	NOT NULL	Siebel payer ID. Note: This value should come from Siebel S_REGION.ROW_ID.
Contact Data
EXT_DATASRC_NUM_ID	NUMBER(10)	NOT NULL	External data source number ID other than 1. Note: The same value should be used in the DATASOURCE_NUM_ID field of the syndicated data and syndicated market flat files.
EXT_INTEGRATION_ID	VARCHAR2(30)	NOT NULL	External contact ID. Note: The same value should be used in the CONTACT_ID field of the syndicated data and syndicated market flat files.
EXT_CON_FULL_NAME	VARCHAR2(102)		Contact name. Note: The same value should already be loaded in Siebel S_CONTACT.LAST_NAME and S_CONTACT.FST_NAME.
EXT_ME_NUM	VARCHAR2(50)		Contact ME number. Note: The same value should already be loaded in Siebel S_CONTACT.CSN.
INTEGRATION_ID	VARCHAR2(30)	NOT NULL	Siebel contact ID. Note: This value should come from Siebel S_CONTACT.ROW_ID.
Product and Market Data
EXT_DATASRC_NUM_ID	NUMBER(10)	NOT NULL	External data source number ID other than 1. Note: The same value should be used in the DATASOURCE_NUM_ID field of the syndicated data and syndicated market flat files.
EXT_INTEGRATION_ID	VARCHAR2(30)	NOT NULL	External product and market ID. Note: The same value should be used in the PAYER_TYPE_ID field of the syndicated data and syndicated market flat files.
EXT_PROD_TYPE	VARCHAR2(30)		Product and market type. Note: The same value should already be loaded in Siebel S_PROD_INT.
EXT_PROD_NAME	VARCHAR2(50)		Product and market name. Note: The same value should already be loaded in Siebel PROD_INT.NAME.
INTEGRATION_ID	VARCHAR2(30)	NOT NULL	Siebel product ID. Note: This value should come from Siebel S_PROD_INT.ROW_ID.

S_SYND_DATA Data Population Rules

Table 108 provides the data population rules for S_SYND_DATA.

Table 108. S_SYND_DATA Data Population Rules
Required Codes	Data Types	Required fields (x) and data population rules for correct rollup in Siebel Analytics
Syndicated Data mandatory codes to be populated in DATA_SRC_CD	Data Type supported with DATA_SRC_CODE	Territory ID TERR_ID	Period ID PERIOD_ID	Position ID POSITION_ID	Postal Code ZIPCODE	Account ID OU_EXT_ID	Contact ID CON_ID	Brick ID AREA_ID	Payor Level PLAN_ID Foreign Key to S_INS_PLAN	Product Level PRDINT_ID (always try to populate data at the lowest level - form strength)	Market Level MARKET_ID	Attribute 1 (Attrib_01)	Attribute 1 (Attrib_02)	Attribute 1 (Attrib_03)	Attribute 1 (Attrib_04)	Siebel Pharma Analytics Business Model Data Source Codes
RXPrf	Prescription data by Contact		x	x			x		Total	Form Strength	x	NRx	TRx	Market NRx	Market TRx	1
SlsIndBrk	Indirect Sales Brick Level		x	x				x	Total	Form Strength	x	Product Sales $	Product Sales Units	Market Sales $	Market Sales Units	22
SlsIndAct	Indirect Sales Account Level		x	x		x			Total	Form Strength	x	Product Sales $	Product Sales Units	Market Sales $	Market Sales Units	21
SlsIndZip	Indirect Sales Zip Level		x	x	x				Total	Form Strength	x	Product Sales $	Product Sales Units	Market Sales $	Market Sales Units	23
SlsDirAct	Direct Sales Account Level		x	x		x			Total	Form Strength	x	Product Sales $	Product Sales Units			11
SlsDirBrk	Direct Sales Brick Level		x	x				x	Total	Form Strength	x	Product Sales $	Product Sales Units			12
SlsDirZip	Direct Sales Zip Level		x	x	x				Total	Form Strength	x	Product Sales $	Product Sales Units			13
RXEVM	Weekly RX Data by Contact		x	x			x		Total	Form Strength	x	Product NRx	Product TRx	Market NRx	Market TRx	0
RXPT	Plan Level Rx Data		x	x					Plan	Form Strength	x	Product NRx	Product TRx	Market NRx	Market TRx	6
RXSMI	Sales Market - Incentives		x	x	x				Total	Form Strength	x	Product NRx	Product TRx	Market NRx	Market TRx	3
RXSMM	Sales Market - Modified		x	x	x				Total	Form Strength	x	Product NRx	Product TRx	Market NRx	Market TRx	4
RXXPT	Plan Level Physician Rx Data		x	x			x		Plan	Form Strength	x	Product NRx	Product TRx	Market NRx	Market TRx	5
RXZip	Prescription data by Zipcode		x	x	x				Total	Form Strength	x	Product NRx	Product TRx	Market NRx	Market TRx	7
RXBrk	Prescription data by Brick		x	x				x	Total	Form Strength	x	Product NRx	Product TRx	Market NRx	Market TRx	8

Siebel Analytics Applications Installation and Administration Guide