|
Oracle® Business Intelligence Applications Installation and Configuration Guide > Oracle Business Analytics Warehouse for Life Sciences Data Considerations >
Syndicated Data Flat File Formats
This section includes descriptions of the following flat files used in the syndicated data load process:
Syndicated Data Flat File for Direct Sales Data
Table 130 provides information about the syndicated data flat file for Direct Sales data.
Table 130. Syndicated Data Flat File - Direct Sales Data
|
|
|
|
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 - S_AMT_M06 |
NUMBER(22,7) |
|
|
S_UNIT_M01 |
NUMBER(22,7) |
|
Stores sales units of current month data (for example, 20050301 data). |
S_UNIT_M02 |
NUMBER(22,7) |
|
Stores sales units of previous month data (for example, 20050201 data). |
S_UNIT_M03 |
NUMBER(22,7) |
|
Stores sales units of 20050101 data. |
S_UNIT_M04 - S_UNIT_M026 |
NUMBER(22,7) |
|
|
Syndicated Data Flat File for Indirect Sales Data
Table 131 provides information about the syndicated data flat file for Indirect Sales data.
Table 131. Syndicated Data Flat File - Indirect Sales Data
|
|
|
|
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 - S_AMT_M026 |
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 - S_UNIT_M26 |
NUMBER(22,7) |
|
|
Syndicated Data Flat File for Prescription Data
Table 132 provides information about the syndicated data flat file for Prescription data.
Table 132. Syndicated Data Flat File - Prescription Data
|
|
|
|
DATASOURCE_NUM_ID |
NUMBER(10) |
NOT NULL |
External data source number ID other than 1. |
DATA_SRC_CD |
VARCHAR2(30) |
NOT NULL |
RXPrf (Prescription data by Contact) RXZip (Prescription data by Zip Code) RXBrk (Prescription data by Brick) RXSMI (Sales Market - Incentives) RXSMM (Sales Market - Modified) |
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 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. |
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 New Rx of current month data. |
NRX_M02 |
NUMBER(22,7) |
|
Stores New Rx of previous month data. |
NRX_M03 |
NUMBER(22,7) |
|
Stores New Rx of 20050101 data. |
NRX_M04 - NRX_M26 |
NUMBER(22,7) |
|
|
TRX_M01 |
NUMBER(22,7) |
|
Stores Total Rx of current month data. |
TRX_M02 |
NUMBER(22,7) |
|
Stores Total Rx of previous month data. |
TRX_M03 |
NUMBER(22,7) |
|
Stores Total Rx of 20050101 data. |
TRX_M04 - TRX_M26 |
NUMBER(22,7) |
|
|
Syndicated Data Flat File for Plan Level Rx Data
Table 133 provides information about the syndicated data flat file for Plan Level Rx data.
Table 133. Syndicated Data Flat File - Plan Level Rx Data
|
|
|
|
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 New Rx of current month data. |
NRX_M02 |
NUMBER(22,7) |
|
Stores New Rx of previous month data. |
NRX_M03 |
NUMBER(22,7) |
|
Stores New Rx of 20050101 data. |
NRX_M04 - NRX_M26 |
NUMBER(22,7) |
|
|
TRX_M01 |
NUMBER(22,7) |
|
Stores Total Rx of current month data. |
TRX_M02 |
NUMBER(22,7) |
|
Stores Total Rx of previous month data. |
TRX_M03 |
NUMBER(22,7) |
|
Stores Total Rx of 20050101 data. |
TRX_M04 - TRX_M26 |
NUMBER(22,7) |
|
|
Syndicated Data Flat File for Physician Plan Level Rx Data
Table 134 provides information about the syndicated data flat file for Physician Plan Level Rx data.
Table 134. Syndicated Data Flat File - Physician Plan Level Rx Data
|
|
|
|
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 New Rx of current month data. |
NRX_M02 |
NUMBER(22,7) |
|
Stores New Rx of previous month data. |
NRX_M03 |
NUMBER(22,7) |
|
Stores New Rx of 20050101 data. |
NRX_M04 - NRX_M26 |
NUMBER(22,7) |
|
|
TRX_M01 |
NUMBER(22,7) |
|
Stores Total Rx of current month data. |
TRX_M02 |
NUMBER(22,7) |
|
Stores Total Rx s of previous month data. |
TRX_M03 |
NUMBER(22,7) |
|
Stores Total Rx of 20050101 data. |
TRX_M04 - TRX_M26 |
NUMBER(22,7) |
|
|
Syndicated Data Flat File for Weekly Early View Data
Table 135 provides information about the syndicated data flat file for Weekly Early View data.
Table 135. Syndicated Data Flat File - Weekly Early View Data
|
|
|
|
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 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. |
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 New Rx of current week data. |
NRX_W02 |
NUMBER(22,7) |
|
Stores New Rx of previous week data. |
NRX_W03 |
NUMBER(22,7) |
|
Stores New Rx of 20050101 data. |
NRX_W04 - NRX_W13 |
NUMBER(22,7) |
|
|
TRX_W01 |
NUMBER(22,7) |
|
Stores Total Rx of current week data. |
TRX_W02 |
NUMBER(22,7) |
|
Stores Total Rx of previous week data. |
TRX_W03 |
NUMBER(22,7) |
|
Stores Total Rx of 20050101 data. |
TRX_W04 - TRX_W13 |
NUMBER(22,7) |
|
|
Syndicated Market Flat File for Indirect Sales Data
Table 136 provides information about the syndicated market flat file for Indirect Sales data.
Table 136. Syndicated Market Flat File - Indirect Sales Data
|
|
|
|
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 |
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 - 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 - MKT_S_UNIT_M26 |
NUMBER(22,7) |
|
|
Syndicated Market Flat File for Prescription Data
Table 137 provides information about the syndicated market flat file for Prescription data.
Table 137. Syndicated Market Flat File - Prescription Data
|
|
|
|
DATASOURCE_NUM_ID |
NUMBER(10) |
NOT NULL |
External data source number ID other than 1. |
DATA_SRC_CD |
VARCHAR2(30) |
NOT NULL |
RXPrf (Prescription data by Contact) RXZip (Prescription data by Zip Code) RXBrk (Prescription data by Brick) RXSMI (Sales Market - Incentives) RXSMM (Sales Market - Modified) |
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 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 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 New Rx of current month data. |
MKT_NRX_M02 |
NUMBER(22,7) |
|
Stores New Rx of previous month data. |
MKT_NRX_M03 |
NUMBER(22,7) |
|
Stores New Rx of 20050101 data. |
MKT_NRX_M04 - MKT_NRX_M26 |
NUMBER(22,7) |
|
|
MKT_TRX_M01 |
NUMBER(22,7) |
|
Stores Total Rx of current month data. |
MKT_TRX_M02 |
NUMBER(22,7) |
|
Stores Total Rx of previous month data. |
MKT_TRX_M03 |
NUMBER(22,7) |
|
Stores Total Rx of 20050101 data. |
MKT_TRX_M04 - MKT_TRX_M26 |
NUMBER(22,7) |
|
|
Syndicated Market Flat File for Plan Level Rx Data
Table 138 provides information about the syndicated market flat file for Plan Level Rx data.
Table 138. Syndicated Market Flat File - Plan Level Rx Data
|
|
|
|
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 New Rx of current month data (for example, 20050301 data). |
MKT_NRX_M02 |
NUMBER(22,7) |
|
Stores New Rx of previous month data (for example, 20050201 data). |
MKT_NRX_M03 |
NUMBER(22,7) |
|
Stores New Rxof 20050101 data. |
MKT_NRX_M04 - MKT_NRX_M26 |
NUMBER(22,7) |
|
|
MKT_TRX_M01 |
NUMBER(22,7) |
|
Stores Total Rx of current month data (for example, 20050301 data). |
MKT_TRX_M02 |
NUMBER(22,7) |
|
Stores Total Rx of previous month data (for example, 20050301 data). |
MKT_TRX_M03 |
NUMBER(22,7) |
|
Stores Total Rx of 20050101 data. |
MKT_TRX_M04 - MKT_TRX_M26 |
NUMBER(22,7) |
|
|
Syndicated Market Flat File for Physician Plan Level Rx Data
Table 139 provides information about the syndicated market flat file for Physician Plan Level Rx data.
Table 139. Syndicated Market Flat File - Physician Plan Level Rx Data
|
|
|
|
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 New Rx of current month data (for example, 20050301 data). |
MKT_NRX_M02 |
NUMBER(22,7) |
|
Stores New Rx of previous month data (for example, 20050201 data). |
MKT_NRX_M03 |
NUMBER(22,7) |
|
Stores New Rx of 20050101 data (for example, 20050301 data). |
MKT_NRX_M04 - MKT_NRX_M26 |
NUMBER(22,7) |
|
|
MKT_TRX_M01 |
NUMBER(22,7) |
|
Stores Total Rx of current month data. |
MKT_TRX_M02 |
NUMBER(22,7) |
|
Stores Total Rx of previous month data. |
MKT_TRX_M03 |
NUMBER(22,7) |
|
Stores Total Rx of 20050101 data. |
MKT_TRX_M04 - MKT_TRX_M26 |
NUMBER(22,7) |
|
|
Syndicated Market Flat File for Weekly Early View Data
Table 140 provides information about the syndicated market flat file for Weekly Early View data.
Table 140. Syndicated Market Flat File - Weekly Early View Data
|
|
|
|
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 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. |
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 New Rx of current data. |
MKT_NRX_W02 |
NUMBER(22,7) |
|
Stores New Rx of previous data. |
MKT_NRX_W03 |
NUMBER(22,7) |
|
Stores New Rx of 20050101 data. |
MKT_NRX_W04 - MKT_NRX_W14 |
NUMBER(22,7) |
|
|
MKT_TRX_W01 |
NUMBER(22,7) |
|
Stores Total Rx of current week data. |
MKT_TRX_W02 |
NUMBER(22,7) |
|
Stores Total Rx of previous week data. |
MKT_TRX_W03 |
NUMBER(22,7) |
|
Stores Total Rx of 20050214 data. |
MKT_TRX_W04 - MKT_TRX_W13 |
NUMBER(22,7) |
|
|
Flat File Cross-Reference Data Population Rules
Table 141 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 141. Flat File Cross-Reference Data Population Rules
|
|
|
|
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. |
|
