Customer Sales Repository (CSR)
The Customer Sales Repository table (csr_gbl_ods__v) provides a conformed table with sales information from ICS 867 and Symphony Non-Retail. Mappings are delivered enabled by default.
| Global ODS Field | Type |
Source Data Connector: Symphony Non-Retail Table: symphony_nonretail_data_ods__v |
Source Data Connector: ICS Table: ics_867_layout_ods__v |
|---|---|---|---|
| composite_key__v | varchar(510) | |connector_name__v|market_id__v|product_id__v|rel_id__v|week_ending_date__v|month_ending_date__v| | |connector_name__v|detail_report_group_key__v|sales_inv_date__v| |
| source_system_id__v | varchar(18) | Extracted from Internal Admin Console (Source ID) | Extracted from Internal Admin Console (Source ID) |
| native_id__v | varchar(500) | |market_id__v|product_id__v|rel_id__v|week_ending_date__v|month_ending_date__v| | detail_report_group_key__v |
| source_connector_name__v | varchar(100) | Extracted from Internal Admin Console (Connector Name) | Extracted from Internal Admin Console (Connector Name) |
| source_table_name__v | varchar(100) | Extracted from MDS | Extracted from MDS |
| native_demographic_id__v | varchar(10) | rel_id__v | |
| native_data_agent_code__v | varchar(2) | ||
| native_writer_type__v | varchar(1) | ||
| native_first_name__v | varchar(25) | ||
| native_middle_name__v | varchar(25) | ||
| native_last_name__v | varchar(60) | ||
| native_title__v | varchar(3) | ||
| native_specialty_code__v | varchar(60) | ||
| native_specialty_description__v | varchar(75) | ||
| native_address__v | varchar(100) | symphony_nonretail_demographic_ods__v.address__v | |
| native_city__v | varchar(28) | symphony_nonretail_demographic_ods__v.city__v | |
| native_state__v | varchar(2) | symphony_nonretail_demographic_ods__v.state__v | |
| native_postal_code__v | varchar(20) | symphony_nonretail_demographic_ods__v.zip_code__v | |
| native_ama_no_contact__v | varchar(1) | ||
| native_pdrp_indicator__v | varchar(1) | ||
| native_pdrp_date__v | varchar(8) | ||
| native_practitioner_status_indicator__v | varchar(1) | ||
| native_type_of_practice_code__v | varchar(3) | ||
| native_dea__v | varchar(9) | symphony_nonretail_demographic_ods__v.dea_number__v | |
| native_cs_provider_ama_id__v | varchar(10) | ||
| native_cs_provider_ama_check_digit__v | varchar(1) | ||
| native_npi__v | varchar(10) | symphony_nonretail_demographic_ods__v.npi__v | |
| native_territory_id__v | varchar(20) | symphony_nonretail_demographic_ods__v.territory_id__v | |
| native_call_status_code__v | varchar(1) | ||
| native_hin__v | varchar(9) | symphony_nonretail_demographic_ods__v.hin__v | |
| native_outlet_name__v | varchar(100) | symphony_nonretail_demographic_ods__v.outlet_name__v | |
| native_outlet_class_of_trade__v | varchar(2) | symphony_nonretail_demographic_ods__v.outlet_class_of_trade__v | |
| native_outlet_sub_class_of_trade__v | varchar(2) | symphony_nonretail_demographic_ods__v.outlet_sub_class_of_trade__v | |
| plan_id__v | varchar(6) | ||
| market_id__v | varchar(10) | market_id__v | |
| product_id__v | varchar(20) | product_id__v | |
| data_type__v | varchar(2) | ||
| sha_id__v | varchar(10) | ||
| week_ending_date__v | date | week_ending_date__v | |
| month_ending_date__v | varchar(6) | month_ending_date__v | |
| sales_type__v | varchar(17) | Non_Retail_Sales | 867 |
| vendor__v | varchar(9) | Symphony | ICS |
| wac_dollars__v | numeric(15,2) | wac_dollars__v | |
| volume_units__v | numeric(15,2) | volume_units__v | |
| pack_units__v | numeric(15,2) | pack_units__v | |
| awp__v | numeric(15,2) | awp__v | |
| detail_invoice_date__v | date | detail_invoice_date__v | |
| detail_group_id__v | varchar(50) | detail_group_id__v | |
| detail_report_group_key__v | varchar(500) | detail_report_group_key__v | |
| detail_report_group_address_1__v | varchar(150) | detail_report_group_addr_info_1_txt__v | |
| detail_report_group_address_2__v | varchar(150) | detail_report_group_addr_info_2_txt__v | |
| detail_report_group_city_name__v | varchar(50) | detail_report_group_city_nm__v | |
| detail_report_group_state_name__v | varchar(50) | detail_report_group_state_nm__v | |
| detail_report_group_postal_code__v | varchar(50) | detail_report_group_postal_cd__v | |
| detail_report_group_country_code__v | varchar(50) | detail_report_group_country_cd__v | |
| detail_report_group_dea_number__v | varchar(50) | detail_report_group_dea_nbr__v | |
| detail_report_group_hin_number__v | varchar(50) | detail_report_group_hin_nbr__v | |
| sales_inv_date__v | date | sales_inv_date__v | |
| invoice_date__v | date | detail_invoice_date__v if populated. If detail_invoice_date__v is null, then use sales_inv_date__v | |
| prod_dsc__v | varchar(50) | prod_dsc__v | |
| ind_prod_id__v | varchar(50) | ind_prod_id__v | |
| sales_qty__v | numeric(10) | Set to negative value when prod_transfer_tcd__v = RU OR RV and sales_qty_qcd__v = 32 OR 76Set to negative value when prod_transfer_tcd__v = SS and sales_qty_qcd__v=76Value = sales_qty__v | |
| extended_amt__v | numeric(14,2) | extended_amt__v | |
| sales_qty_qcd__v | varchar(10) | sales_qty_qcd__v | |
| prod_transfer_tcd__v | varchar(10) | prod_transfer_tcd__v | |
| contract_unit_price_amt__v | numeric(14,2) | contract_unit_price_amt__v | |
| contract_uom_code__v | varchar(10) | contract_uom_cd__v | |
| sales_inv_id__v | varchar(50) | sales_inv_id__v | |
| reference_id__v | varchar(50) | reference_id__v | |
| invoice_id__v | varchar(50) | sales_inv_id__v or if sales_inv_id__v is null then populated with reference_id__v | |
| prim_support_group_id__v | varchar(50) | prim_support_group_id__v | |
| prim_report_group_nm__v | varchar(50) | prim_report_group_nm__v | |
| new_rx_count__v | numeric(14,3) | ||
| refill_rx_count__v | numeric(14,3) | ||
| total_rx_count__v | numeric(14,3) | ||
| new_rx_quantity__v | numeric(14,3) | ||
| refill_rx_quantity__v | numeric(14,3) | ||
| total_rx_quantity__v | numeric(14,3) | ||
| new_rx_cost__v | numeric(14,3) | ||
| refill_rx_cost__v | numeric(14,3) | ||
| total_rx_cost__v | numeric(14,3) | ||
| data_created_date__v | timestamp |
