Document Version
Obtaining data from elsewhere in the NZULM
Download 196.51 Kb.
|
Obtaining data from elsewhere in the NZULMFrom these two indexes it is possible to go to other tables within the NZULM to extract information that you may be interested in. There is a table that maps NZULM MP records to SNOMED-CT product concepts. The NZMT sctid in this table is the MP sctid. The generic_concept_id in the prescribing and charting term indexes is not the MP sctid, however. It is either the MPUU sctid or the MPP sctid. Currently this is a trial release of the snomed_mapping table and contains approximately 1,000 records, out of a possible 2,400 MP records. Even when the mapping exercise is complete, there is no certainity that all NZMT MP records will have a matching SNOMED-CT product concept. For records in the indexes where the generic_concept_table is ‘MPUU’, read table MPUU where MPUU.id = generic_concept_id. You can then read the mapping table where MPUU.MP = mapping table MP sctid. The mapping table will give you concept and description ids for SNOMED-CT product concepts. Here’s sample SQL code for this: SELECT Prescribing_term_index.*, MP.PreferredTerm, snomed_mapping.snomed_term, snomed_mapping.description_id, snomed_mapping.conceptId FROM ((Prescribing_term_index INNER JOIN MPUU ON Prescribing_term_index.generic_concept_id = MPUU.id) INNER JOIN MP ON MPUU.MP = MP.id) INNER JOIN snomed_mapping ON MP.id = snomed_mapping.ID WHERE (((Prescribing_term_index.generic_concept_table)='MPUU')); For records where the generic_concept_table is ‘MPP’, read table MHM where MHM.MPP = generic_concept_id. Next read table MPUU where MPUU.id = MHM.MPUU. You can then read the mapping table where MPUU.MP = mapping table MP sctid. Note that this may return multiple records as the MPP will be linked to more than MPUU record. In addition, not all MPs have been mapped to SNOMED-CT and so you may find a map to SNOMED for one MPUU related to a specific MPP but not another. Here’s sample SQL code for this: SELECT distinct Prescribing_term_index.*, MP.PreferredTerm, snomed_mapping.snomed_term, snomed_mapping.description_id, snomed_mapping.conceptId FROM ((((Prescribing_term_index INNER JOIN MPP ON Prescribing_term_index.generic_concept_id = MPP.id) INNER JOIN MHM ON MPP.id = MHM.MPP) INNER JOIN MPUU ON MHM.MPUU = MPUU.id) INNER JOIN MP ON MPUU.MP = MP.id) INNER JOIN snomed_mapping ON MP.id = snomed_mapping.ID WHERE (((Prescribing_term_index.generic_concept_table)='MPP')); For records where there is no generic term (prescribe_by_brand = 1), there are two methods, depending on the value of the trade_concept_table. For records where the trade_concept_table is ‘TPUU’, read table TPUU where TPUU.id = trade_concept_id. Next read MPUU where MPUU.id = TPUU.MPUU. You can then read the mapping table where MPUU.MP = mapping table MP sctid. Here’s sample SQL code for this: SELECT Prescribing_term_index.*, MP.PreferredTerm, snomed_mapping.snomed_term, snomed_mapping.description_id, snomed_mapping.conceptId FROM (((TPUU INNER JOIN MPUU ON TPUU.MPUU = MPUU.id) INNER JOIN MP ON MPUU.MP = MP.id) INNER JOIN snomed_mapping ON MP.id = snomed_mapping.ID) INNER JOIN Prescribing_term_index ON TPUU.id = Prescribing_term_index.trade_concept_id WHERE (((Prescribing_term_index.prescribe_by_brand)="1") AND ((Prescribing_term_index.trade_concept_table)='TPUU')); For records where the trade_concept_table is ‘TPP’, read table THT where THT.TPP = trade_concept_id. Next read table TPUU where TPUU.id = THT.TPUU. Next read MPUU where MPUU.id = TPUU.MPUU. You can then read the mapping table where MPUU.MP = mapping table MP sctid. As with the MPP based query, this may return multiple records as a TPP will be linked to more than one TPUU records. Here’s sample SQL for this. At present none of the pack-level prescribe_by_brand medicines are matched to SNOMED-CT. SELECT distinct Prescribing_term_index.*, MP.PreferredTerm, snomed_mapping.snomed_term, snomed_mapping.description_id, snomed_mapping.conceptId FROM (((((Prescribing_term_index INNER JOIN TPP ON Prescribing_term_index.trade_concept_id = TPP.id) INNER JOIN THT ON TPP.id = THT.TPP) INNER JOIN TPUU ON THT.TPUU = TPUU.id) INNER JOIN MPUU ON TPUU.MPUU = MPUU.id) INNER JOIN MP ON MPUU.MP = MP.id) INNER JOIN snomed_mapping ON MP.id = snomed_mapping.ID WHERE (((Prescribing_term_index.trade_concept_table)='TPP') AND ((Prescribing_term_index.prescribe_by_brand)='1')); Linking to Medsafe DataThere are a number of data elements that may be required that are found specifically in the Medsafe registration data. All the tables of Medsafe data are prefixed ‘ms_’. When retrieving information from the Medsafe tables, you need to remember that not all products in the NZMT are in the Medsafe data. Products that are flagged in the NZMT as Section 29 medicines (CTPP.is_Section29 = 1) will not necessarily be found in Medsafe. Other products subsidised by PHARMAC and present in the NZMT such as devices and special foods will not be in Medsafe because they are not medicines in terms of the Medicines Act and aren’t included in the Medsafe database. Retrieving Route of Administration dataWhile Route of Administration data is not stored in the NZMT, it is stored in the Medsafe data. The Route of Admin data (ms_route_of_admin) is held at component level which is roughly equivalent to the Unit of Use level in the NZMT. To access the Medsafe ms_route_of_admin table, you read ms_package where ms_package.nzmt_sctid = ctpp_id. Next you read ms_component where ms_component.product_id = ms_package.product_id. Then you read ms_route_of_admin where ms_route_of_admin.component_id = ms_component.component_id. As there may be more than one component for a package, this may return more than one route of administration. Here’s sample SQL code for this: SELECT Prescribing_term_index.*, ms_route_of_admin.route FROM ((Prescribing_term_index INNER JOIN ms_package ON Prescribing_term_index.ctpp_id = ms_package.nzmt_sctid) INNER JOIN ms_component ON ms_package.product_id = ms_component.product_id) INNER JOIN ms_route_of_admin ON ms_component.component_id = ms_route_of_admin.component_id;
Retrieving Availability DataThe only product availability data in the NZULM is that held by Medsafe. Medsafe’s data reflects overall long-term availability of products and so may not reflect stock shortages or products recently removed from the market. In addition, all products listed in the PHARMAC Community Schedule are available, regardless of their registration status with Medsafe. Similarly, products flagged as Section 29 medicines (is_Section29 = 1) may also be available. To retrieve the products and packages that Medsafe considered to be available, read ms_package where ms_package.nzmt_sctid = ctpp_id. You then read ms_product where ms_product.product_id = ms_package.product_id. All records where the ms_package.pkg_status = ‘Active’ and the ms_product.reg_situation = ‘Consent Given’ can be considered available. Here’s sample SQL code for this: SELECT Prescribing_term_index.*, ms_package.pkg_status, ms_product.reg_situation FROM (Prescribing_term_index INNER JOIN ms_package ON Prescribing_term_index.ctpp_id = ms_package.nzmt_sctid) INNER JOIN ms_product ON ms_package.product_id = ms_product.product_id WHERE (((ms_package.pkg_status)='Active') AND ((ms_product.reg_situation)='Consent Given')); Retrieving Legal ClassificationThe legal classification of a medicine is found in the Medsafe data. Read ms_package where ms_package.nzmt_sctid = ctpp_id. You then read ms_product where ms_product.product_id = ms_package.product_id. The field ms_product.classification holds the legal classification of the medicine. Here’s sample SQL code for this SELECT Prescribing_term_index.*, ms_product.classification, ms_product.reg_situation, ms_package.pkg_status FROM (Prescribing_term_index INNER JOIN ms_package ON Prescribing_term_index.ctpp_id = ms_package.nzmt_sctid) INNER JOIN ms_product ON ms_package.product_id = ms_product.product_id; This will return the classification for a substantial number of products that are not actually available, so you may want to combine this query with the previous query, as follows: SELECT Prescribing_term_index.*, ms_product.classification, ms_product.reg_situation, ms_package.pkg_status FROM (Prescribing_term_index INNER JOIN ms_package ON Prescribing_term_index.ctpp_id = ms_package.nzmt_sctid) INNER JOIN ms_product ON ms_package.product_id = ms_product.product_id WHERE (((ms_product.reg_situation)='Consent Given') AND ((ms_package.pkg_status)='Active'));
Linking to PHARMAC Subsidy DataThere are a number of data elements that may be required that are found specifically in the PHARMAC schedule data. There two sets of data provided by PHARMAC, the Community Schedule the Hospital Medicines List (HML). All the tables of Community Schedule data are prefixed ‘ps_’ and all the tables of HML data are prefixed ‘hml_’. The key into the Community Schedule data is the pharmacode. At any point in time, however, only a small proportion of the NZULM products will be subsidised. Currently the NZULM contains approximately 15,600 products and at any given time PHARMAC subsidise something in the order of 2,200 products. It is best, therefore, to only look for subsidy data when the index is_subsidised flag = 1. To retrieve the subsidy values from the Community Schedule data, read ps_pack where ps_pack.pharmacode = ctpp_pharmacode. The field ps_pack.subsidy contains the current subsidy and the field ps_pack.price contains the current manufacturer’s price. Where these two values are the same, even in instances where they are both zero, the product is fully subsidised. Where these two values are different the product is partially subsidised. Here's sample SQL code for this: SELECT Prescribing_term_index.*, ps_pack.subsidy, ps_pack.price FROM Prescribing_term_index INNER JOIN ps_pack ON Prescribing_term_index.ctpp_pharmacode = ps_pack.pharmacode; Items not coveredThe following data retrieval areas have deliberately been excluded from this guide due to their complexity. Within the NZMTRetrieving atomised strength data (see section 3.4.5.6 of the Data Overview, which is included in the full NZULM download) Retrieving pack item and volume quantity (see sections 3.3.5.6 and 3.4.7.6 of the Data Overview) Within the PHARMAC dataRetrieving PHARMAC’s product rules and restrictions Retrieving Special Authority requirements The NZMT Primary ConceptsThe Data Overview contains full details of all the tables in the NZMT and their relationships. In this document I have reproduced the descriptions of the key tables (CTPP, TPP, TPUU, TP, MPP, MPUU, MP and substance), focusing on the sample of terms held at each level to assist in understanding what each table contains. The New Zealand Medicines Terminology (NZMT) database has been designed from the SNOMED design model, and is intended to provide a single, consistent database of names for medicines and trade products containing these medicines. OverviewThe SNOMED design model means that almost all the tables have an id column (unique within the NZMT), a PreferredTerm column and a FullySpecifiedName column. Most reference tables have only these three columns, while the main tables (CTPP, TP, TPP, TPUU, MP, MPP, MPUU) have a number of other columns. Tables that are an addition to the original SNOMED model such as the table linking ATC codes to NZMT core concepts (nzmt_atc_links) do not necessarily conform to this design model. CTPP – Containered Trade Product PackDescriptionThe Containered Trade Product Pack (CTPP) is the packaged, branded product that is dispensed in some kind of container (e.g.: a bottle, an ampoule, a blister pack for a tablet, or a sachet for a patch). This table holds details of specific instances of trade product packs. It is important to understand that this is the only table that represents an actual product that you can physically handle and dispense. This is why it is the link to all the other sector datasets. Eventually they all have to deal with physical products. The other tables in the NZMT refer to “virtual” or “abstract” products.
Sample Contents – Preferred Terms“Panadol 500 mg tablet: film-coated, 50 tablets, blister pack” – CTPP.id = ‘50032691000117103’ “Accuretic 10/12.5 tablet: film-coated, 30 tablets, blister pack” – CTPP.id = ‘50004651000117106’ “Ventolin inhaler CFC-Free 100 microgram/actuation inhalation: pressurised, 200 actuations, metered dose aerosol can” – CTPP.id = ‘50005221000117100’ “Augmentin 500/125 tablet: film-coated, 20 tablets, blister pack” – CTPP.id = ‘50019081000117105’ “Triphasil, 84 tablets [3 x 28 tablets], blister pack” – CTPP.id = ‘50053051000117101’ “Pegasys-RBV (4 x 135 microgram prefilled syringes, 112 x 200 mg tablets), 1 pack, composite pack” – CTPP.id = ‘50030791000117109’ Sample Contents – Prescribing TermsWe do not provide prescribing terms at CTPP level, as it is not expected that prescribers will be seeking to prescribe specific packs. The base assumption is that a prescriber will prescribe a medicine to be taken so many times a day for so many days. The dispenser will then determine if a standard pack meets that prescription. Sample Contents – Charting TermsWe do not provide charting terms at CTPP level, again because it not expected that medications will be charted by pack. Other Alternate TermsWe provide sort names at CTPP level, where required to assist in correctly sorting actual products. “Panadol 500 mg tablet: film-coated, 00050 tablets, blister pack” TPP – Trade Product PackDescriptionA Trade Product Pack (TPP) is the parent of the CTPP. It represents a branded product with a certain formulation, a specific dose form, and a specific pack size. e.g.: “Panadol 500mg tablets, pack of 20”. It may be the parent of many CTPP records, each of which represents the TPP in a specific container type such as bottle or blister pack.
Sample Contents – Preferred Terms“Panadol 500 mg tablet: film-coated, 50 tablets” – TPP.id = ‘10510741000116108’ “Accuretic 10/12.5 tablet: film-coated, 30 tablets” – TPP.id = ‘10008921000116101’ “Ventolin inhaler CFC-Free 100 microgram/actuation inhalation: pressurised, 200 actuations” – TPP.id = ‘10697641000116109’ “Augmentin 500/125 tablet: film-coated, 20 tablets” – TPP.id = ‘10083061000116103’ “Triphasil, 84 tablets [3 x 28 tablets]” – TPP.id = ‘10688991000116109’ “Pegasys-RBV (4 x 135 microgram prefilled syringes, 112 x 200 mg tablets), 1 pack” – TPP.id = ‘10531661000116103’ Sample Contents – Prescribing TermsOnly some products have Trade Prescribing Terms at TPP level, where those products are typically prescribed by pack rather than by unit of use. “Triphasil – levonorgestrel 50mcg+ethinylestradiol 30mcg [18] & levonorgestrel 75mcg+ethinylstradiol 40mcg[15] & levonorgestrel 125mcg+ethinylestradiol 30mcg [30] & inert tab [21], [3 x 28] tablets” “Pegasys-RBV – peginterferon alfa 2a 135 microgram/0.5 mL injection [4 x 0.5 mL prefilled syringes] (&) ribavirin 200 mg tablet [112 tablets], pack” Sample Contents – Charting TermsAs with Prescribing Terms, only some products have Trade Charting Terms at TPP level. “Triphasil 28 tablets” “Pegasys-RBV (4 x 135 microgram prefilled syringes, 112 x 200 mg tablets)” Other Alternate TermsWhere products are likely to be dispensed as whole packs we provide Label Names in 80 and 160 character lengths at TPP level. In certain instances, we provide alternative terms in TallMan Lettering format. “Triphasil 28 tablets” – Label Name 160 “Triphasil tablet [3 x 28] 84 tablets” – Label Name 80 “Pegasys-RBV – pegINTERFERON alfa 2a 135 microgram/0.5 mL injection [4 x 0.5 mL prefilled syringes] (&) ribavirin 200 mg tablet [112 tablets], pack” – Prescribing Term TallMan TPUU – Trade Product Unit of UseDescriptionA Trade Product Unit of Use (TPUU) represents a single dose of a specific formulation of a branded product (unless the product is presented as a continuous dosage form, e.g. liquid or cream). It specifies the strength of one or more active ingredients, and the Dose Form. It belongs to a particular Trade Product (brand) and to a specific MPUU (its generic equivalent). Sample Contents – Preferred Terms“Panadol (paracetamol 500 mg) tablet: film-coated, 1 tablet” – TPUU.id = ‘10510501000116104’ “Accuretic 10/12.5 (quinapril (as hydrochloride) 10 mg + hydrochlorothiazide 12.5 mg) tablet: film-coated, 1 tablet” – TPUU.id = ‘10008891000116107’ “Ventolin inhaler CFC-Free (salbutamol (as sulfate) 100 microgram/actuation) inhalation: pressurised” – TPUU.id = ‘10697621000116104’ “Augmentin (amoxicillin (as trihydrate) 500 mg + clavulanic acid (as potassium) 125 mg) tablet: film-coated, 1 tablet” – TPUU.id – ‘10082571000116109’ “Triphasll (levonorgestrel 75 microgram + ethinyloestradiol 40 microgram) tablet: sugar-coated, 1 tablet” – TPUU.id = ‘20052131000116109’ “Triphasll (levonorgestrel 125 microgram + ethinyloestradiol 30 microgram) tablet: sugar-coated, 1 tablet” – TPUU.id = ‘20052111000116103’ “Triphasll (levonorgestrel 50 microgram + ethinyloestradiol 30 microgram) tablet: sugar-coated, 1 tablet” – TPUU.id = ‘20052151000116104’ “Triphasll (inert substance) tablet: sugar-coated, 1 tablet” – TPUU.id = ‘10688881000116102’ “Copegus (ribavirin 200 mg) tablet: film-coated, 1 tablet” – TPUU.id = ‘20022741000116100’ “Pegasys (peginterferon alfa-2a 135 microgram/0.5 mL) injection: solution, syringe” – TPUU.id = ‘10532011000116104’ Sample Contents – Prescribing TermsMost trade prescribing terms are held at TPUU level. There are some multi-component packs where the individual components are marketed separately as well as together in a pack. Such cases will have a trade prescribing term at TPP level for the pack and trade prescribing terms at TPUU level for the individual components. There are no terms for Triphasil at this level because the individual tablet strengths are not sold individually. “Panadol – paracetamol 500 mg tablet” “Accuretic – quinapril 10 mg + hydrochlorothiazide 12.5 mg tablet” “Ventolin – salbutamol 100 microgram/actuation inhalation: pressurised” “Augmentin - amoxicillin 500 mg + clavulanic acid 125 mg tablet” “Copegus – ribavirin 200 mg tablet” “Pegasys – peginterferon alfa-2a 135 microgram/0.5 mL injection” Sample Contents – Charting TermsMost trade charting terms are held at TPUU level. Hospital practice in NZ normally separate packs into their individual components for use, so we have charting terms of the individual components of a multi-component pack at TPUU level. We may also have a pack level charting term for those cases where a pack is administered. “Panadol 500 mg tablet” “Accuretic 10/12.5 tablet” “Ventolin inhaler CFC-Free 100 microgram/actuation inhalation: pressurised” “Augmentin 500 tablet” “Copegus 200 mg tablet” “Pegasys 135 microgram/0.5 mL injection” Other Alternate TermsWe provide Label Names in 80 and 160 character lengths at TPUU level. For selected products there are also TallMan Lettering alternatives. “Pegasys (pegINTERFERON alfa-2a 135 microgram/0.5 mL) injection: solution, syringe” – Preferred Term TallMan “Pegasys – pegINTERFERON alfa-2a 135 microgram/0.5 mL injection” – Prescribing Term TallMan TP – Trade ProductDescriptionThe Trade Product (TP) table essentially holds brand names for products. There are three TPs for Pegasys-RBV as each component has its own brand name, while the composite pack has a third brand name of its own. Sample Contents – Preferred Terms“Panadol” – TP.id = ‘10510491000116108’ “Accuretic” – TP.id = ‘10008881000116105’ “Ventolin” – TP.id = ‘10697451000116109’ “Augmentin” – TP.id = ‘10082321000116103’ “Triphasil” – TP.id = ‘1068871000116104’ “Pegasys-RBV” – TP.id = ‘46354271000116104’ “Pegasys” – TP.id = ‘10531821000116105’ “Copegus” – TP.id = ‘20022751000116102’ Sample Contents – Prescribing TermsBrand names are not sufficiently specific to be safe for use in prescribing so we do not provide prescribing terms at TP level. Sample Contents – Charting TermsBrand Names are not sufficiently specific for purposes of charting medicines either so we do not provide charting terms at TP level. Other Alternate TermsThere are no other alternate terms specified at TP level. MPP – Medicinal Product PackDescriptionA Medicinal Product Pack (MPP) is the medical equivalent of the Trade Product Pack (TPP). It represents a generic substance with a certain formulation, a specific dose form, and a specific pack size. e.g.: “Paracetamol 500mg tablets, pack of 20”. It includes strength, dose form and pack size information but is based on the active medicinal ingredient rather than the brand name. Note that for every MPP, there may be many TPPs linked to it, each representing a different brand that offers the same pack size and dose strength. Sample Contents – Preferred Terms“paracetamol 500 mg tablet, 50” – MPP.id = ‘10037621000116102’ “quinapril 10 mg + hydrochlorothiazide 12.5 mg tablet, 30” – MPP.id = ‘10008861000116103’ “salbutamol 100 microgram/actuation inhalation, 200 actuations” – MPP.id = ‘10023871000116105’ “amoxicillin 500 mg + clavulanic acid 125 mg tablet, 20” – MPP.id = ‘10083041000116104’ “levonorgestrel 50 microgram + ethinyloestradiol 30 microgram tablet [18 tablets] (&) levonorgestrel 75 microgram + ethinyloestradiol 40 microgram tablet [15 tablets] (&) levonorgestrel 125 microgram + ethinyloestradiol 30 microgram tablet [30 tablets] (&) inert substance tablet [21 tablets], 84 [3 x 28 tablets]” – MPP.id = ‘20014211000116109’ “peginterferon alfa-2a 135 microgram/0.5 mL injection [4 x 0.5 mL prefilled syringes] (&) ribavirin 200 mg tablet [112 tablets], 1 pack” – MPP.id = ‘20037791000116108’ Sample Contents – Prescribing TermsOnly some products have Generic Prescribing Terms at MPP level, where those products are typically prescribed by pack rather than by unit of use. “levonorgestrel 50mcg + ethinylestradiol 30mcg [18] & levonorgestrel 75mcg + ethinylestradiol 40mcg [15] & levonorgestrel 125mcg + ethinyloestradiol 30mcg [30] & inert substance [21] tablets” “peginterferon alfa-2a 135 microgram/0.5 mL injection [4] (&) ribavirin 200 mg tablet [112] Sample Contents – Charting TermsAs with Prescribing Terms, only some products have Generic Charting Terms at MPP level. “levonorgestrel + ethinyloestradiol 50|30microgram & levonorgestrel + ethinyloestradiol 75|40microgram & levonorgestrel + ethinyloestradiol 125|30microgram tablets” “peginterferon alfa-2a 135 microgram/0.5 mL injection [4 x 0.5 mL prefilled syringes] (&) ribavirin 200 mg tablet [112 tablets], pack Other Alternate TermsWhere products are likely to be dispensed as whole packs we provide Label Names in 80 and 160 character lengths at TPP level. Peginterferon alfa-2a has TallMan Lettering alternate terms also. “levonorgestrel + ethinyloestradiol 50|30microgram & levonorgestrel + ethinyloestradiol 75|40microgram & levonorgestrel + ethinyloestradiol 125|30microgram tablets” – Label Name 160 “pegINTERFERON alfa-2a 135 microgram/0.5 mL injection [4 x 0.5 mL prefilled syringes] (&) ribavirin 200 mg tablet [112 tablets], 1 pack” – Preferred Term TallMan “pegINTERFERON alfa-2a 135 microgram/0.5 mL injection [4] (&) ribavirin 200 mg tablet [112] – Prescribing Term TallMan “pegINTERFERON alfa-2a 135 microgram/0.5 mL injection [4 x 0.5 mL prefilled syringes] (&) ribavirin 200 mg tablet [112 tablets], pack – Charting Term Tallman MPUU – Medicinal Product Unit of UseDescriptionA Medicinal Product Unit of Use (MPUU) is the medicinal equivalent of the TPUU. It holds the details of an individual dose of a medicine described using the generic substance name rather than the brand name. It includes the active ingredient(s), dose form and dose strength. Sample Contents – Preferred Terms“paracetamol 500 mg tablet” – MPUU.id = ‘10037311000116101’ “quinapril 10 mg + hydrochlorothiazide 12.5 mg tablet” – MPUU.id = ‘10008831000116106’ “salbutamol 100 microgram/actuation inhalation” – MPUU.id = ‘10619771000116103’ “amoxicillin 500 mg + clavulanic acid 125 mg tablet” – MPUU.id = ‘10033131000116100’ “levonorgestrel 125 microgram + ethinyloestradiol 30 microgram tablet” – MPUU.id = ‘20014261000116106’ “levonorgestrel 75 microgram + ethinyloestradiol 40 microgram tablet” – MPUU.id = ‘20014281000116104’ “levonorgestrel 50 microgram + ethinyloestradiol 30 microgram tablet” – MPUU.id = ‘20014301000116103’ “inert substance tablet” – MPUU.id = ‘20013921000116106’ “ribarvirin 200 mg tablet” – MPUU.id = 20022711000116101’ “peginterferon alfa-2a 135 microgram/0.5 mL, injection, syringe” – MPUU.id = ‘20037811000116109’ Sample Contents – Prescribing TermsMost generic prescribing terms are held at MPUU level. There are some multi-component packs where the individual components are marketed separately as well as together in a pack. Such cases will have a generic prescribing term at MPP level for the pack and generic prescribing terms at MPUU level for the individual components. There are no prescribing terms for the individual strengths of the levonorgestrel + ethinylestradiol tablets as these are not available individually. Often the PreferredTerm is sufficient and so no specific Prescribing Term exists. The prescribing_term_index defaults to the MPUU PreferredTerm “paracetamol 500 mg tablet” – MPUU PreferredTerm default “quinapril 10 mg + hydrochlorothiazide 12.5 tablet” – MPUU PreferredTerm default “salbutamol 100 microgram/actuation inhalation: pressurised” – MPUU PreferredTerm default “amoxicillin 500 mg + clavulanic acid 125 mg tablet” – MPUU PreferredTerm default “ribarvirin 200 mg tablet” – MPUU PreferredTerm default “peginterferon alfa-2a 135 microgram/0.5 mL, injection” Sample Contents – Charting TermsMost generic charting terms are held at MPUU level. Hospital practice in NZ normally separate packs into their individual components for use, so we have generic charting terms of the individual components of a multi-component pack at MPUU level. We may also have a pack level charting term for those cases where a pack is administered. Often the PreferredTerm is sufficient and so no specific Charting Term exists. The charting_term_index defaults to the MPUU PreferredTerm. “paracetamol 500 mg tablet” – MPUU PreferredTerm default “quinapril 10 mg + hydrochlorothiazide 12.5 mg tablet” – MPUU PreferredTerm default “salbutamol 100 microgram/actuation inhalation: pressurised” – MPUU PreferredTerm default “amoxicillin 500 mg + clavulanic acid 125 mg tablet” “ribarvirin 200 mg tablet” – MPUU PreferredTerm default “peginterferon alfa-2a 135 microgram/0.5 mL, injection” Other Alternate TermsWe provide Label Names in 80 and 160 character lengths at MPUU level. In some instance we also provide sort names at MPUU level, to ensure that generics appear before brands and in the correct sequence when displayed on the NZULM website. Peginterferon alfa-2a also has alternate TallMan Lettering terms. “levonorgestrel 075 microgram + ethinyloestradiol 40 microgram tablet” – sort name “levonorgestrel 050 microgram + ethinyloestradiol 30 microgram tablet” – sort name “0inert substance tablet” – sort name “pegINTERFERON alfa-2a 135 microgram/0.5 mL, injection, syringe” – Preferred Term TallMan “pegINTERFERON alfa-2a 135 microgram/0.5 mL, injection” – Prescribing Term TallMan “pegINTERFERON alfa-2a 135 microgram/0.5 mL, injection” – Charting Term TallMan MP – Medicinal ProductDescriptionThe Medicinal Product (MP) table holds the generic names of medicinal substances. An MP can be a single ingredient (e.g. paracetamol) or it can be multi-ingredient (e.g. paracetamol + codeine). Sample Contents – Preferred Terms“paracetamol” – MP.id = ‘10037191000116105’ “quinapril + hydrochlorothiazide” – MP.id = ‘10008801000116102’ “salbutamol” – MP.id = ‘10023831000116108’ “amoxicillin + clavulanic acid” – MP.id = ‘20038721000116109’ “levonorgestrel + ethinyloestradiol” – MP.id = ‘10399161000116101’ “inert substance” – MP.id = ‘20006751000116107’ “ribavirin” – MP.id = ‘20055461000116103’ “peginterferon alfa-2a” – MP.id = ‘20037821000116100’ Sample Contents – Prescribing TermsWe do not provide prescribing terms at MP level as this level, on its own, is not considered specific enough to be safe for prescribing. Sample Contents – Charting TermsWe do not provide charting terms at MP level for the same safety reasons that we do not provide prescribing terms at this level. Other Alternate TermsWe provide synonyms for PreferredTerms at MP level. We also provide a TallMan alternative for the PreferredTerm for peginterferon alfa-2a. “pegINTERFERON alfa-2a” – Preferred Term TallMan substanceDescriptionThis is the list of substances, specifically linked as ingredients in the MPUUSAI table and to the MP table. Sample Contents – Preferred Terms“paracetamol” – substance.id = ‘2442011000036104’ “quinapril” – substance.id = ‘2539011000036102’ “hydrochlorothiazide” – substance.id = ‘2194011000036100’ “salbutamol” – substance.id = ‘2571011000036104’ “amoxicillin” – substance.id = ‘29422001000116105’ “clavulanic acid” – substance.id = ‘1962011000036108’ “levonorgestrel” – substance.id = ‘227601000036102’ “ethinyloestradiol” – substance.id = ‘44277261000116101’ “ribavirin” – substance.id = ‘2556011000036107’ “peginterferon alfa-2a” – substance.id = ‘2448011000036101’ Sample Contents – Prescribing TermsWe do not provide prescribing terms at substance level as this level, on its own, is not considered specific enough to be safe for prescribing. Sample Contents – Charting TermsWe do not provide charting terms at substance level for the same safety reasons that we do not provide prescribing terms at this level. Other Alternate TermsWe provide synonyms for PreferredTerms at substance level. There is also a TallMan Lettering alternate PreferredTerm. “pegINTERFERON alfa-2a” Preferred Term TallMan DescriptionsDescriptionA description is a text expression related to a Concept ID. Each of these descriptions has a SNOMED CT description ID of its own, in addition to the SNOMED CT Concept ID to which it is related. The primary descriptions are the FullySpecifiedName and PreferredTerm but this table includes other variant Terms or names for a substance or product. There are number of types of descriptions including Label Names, Pharmac-specific names and Prescribing Terms (see 3.5.7 for more details). These may exist for a number of reasons. For example, some substances have very long names (Preferred Terms) but a label on a bottle only has room for 40 characters, so an alternate name of type “label” may need to be created. This would be done by a pharmacist, and would involve sensibly condensing a long name down to something that it still precise and meaningful in 40 characters or less. This will usually involve the use of common abbreviations. There are number of possible descriptions in addition to the FullySpecifiedName and PreferredTerm including:
Label names (up to 40, 80 and 160 characters long) PHARMAC-specific names Prescribing Terms (up to 200 characters long) Charting Terms (up to 100 characters long) Synonyms (up to 1000 characters long) The descriptions table is an extension of existing tables. It exists to provide a SNOMED CT ID for each PreferredTerm and, in addition, to cater for those situations where the accepted PreferredTerm is not satisfactory. For example, a CTPP PreferredTerm may be too long to conveniently fit onto a medicine container. In this case a label name may be defined and this name would be stored in the descriptions table. The 40 and 80 character lengths correspond to lengths of labels currently in community practice. The 160-character length is specifically intended for use in hospital pharmacy systems. Similarly, there may be instances where Pharmac require a CTPP to have a specific name for the purposes of the Pharmaceutical Schedule. This name would be displayed in the Pharmac pop-up on the NZULM website in preference to the PreferredTerm of the NZMT, and again would be stored in the descriptions table. The Prescribing Term equally exists to provide a standard abbreviated alternative to a Preferred Term that is in excess of 200 characters in length. The Charting Term exists to provide a standard abbreviated alternative to both Preferred Terms and Prescribing Terms that are in excess of 100 characters in length, and was designed initially for use in hospital electronic charting systems. When using these descriptions, we would expect that text searches would continue to be based on the primary Preferred Term with the description used as an alternative for display or reporting purposes. All descriptions have their own unique sctids so that, if necessary, what the user actually saw on the screen can be recorded as well as the primary term. The table includes a ‘table_name’ column to allow this facility to be used as flexibly as possible – although both examples are at CTPP level, descriptions are not restricted to this table. When importing .csv files containing these fields into Excel you need to specify the fields as ‘text’. If you don’t Excel will recognise them as numeric but will not have a sufficiently large numeric field to enter them into and so truncate them. The truncation is somewhat insidious as all 18 digits will be displayed but the right-hand-most digit will be 0. As this is the checkdigit it is normally not 0. Prescribing TermsThe Prescribing Terms description now has entries at the MPUU, MPP, TPUU and TPP level. These entries are designed to provide a standard shorter abbreviation (200 characters instead of 1000 characters) for lengthy Preferred Terms. While the field is 200 characters in length, in practice we have reduced the length of Prescribing Terms to approximately 120 characters. Where a Prescribing Term exists we expect it to be used as a display term in preference to the Preferred Term in prescribing systems. In the absence of a generic Prescribing Term, where a shortened name is needed, the MPUU PT is to be used. Note that an MPUU or MPP record that has the ‘prescribe_by_brand’ flag set to 1 does not have a generic Prescribing Term and the MPUU or MPP PreferredTerm should not be used as a generic Prescribing Term. For these generics, the Trade Prescribing Term is the only Prescribing Term to be used. Sort NamesThe Sort Names description now has entries at the CTPP and MPUU level. These entries are designed to support sorting the text Preferred Terms into ascending order by strength and unit dose. They have only been added where required, and where they do not exist the Preferred Term is used to sort for text-based sorting of the records. Note that the sort we use includes the dose form as a separate element and so the Sort Name is intended only to provide the correct ascending sequence for a given dose form. SynonymsThe Synonyms description now has entries at the MP and substance level. These entries are designed to support searching for alternate names for medicines and so are currently only found at MP and substance level. They have been included in the preferredterm_index as synonyms. Charting TermsThe Charting Terms description now has entries at the MPUU, MPP, TPUU and TPP level. These entries are designed to provide a standard shorter abbreviation (100 characters instead of 1000 characters) for lengthy Preferred Terms. These terms were specifically intended for use in hospital electronic charting systems. Where a Charting Term exists we expect it to be used as a display term in preference to the Preferred Term in such systems. In the absence of a generic Prescribing Term, where a shortened name is needed, the MPUU PT is to be used. Note that unlike the Prescribing Term an MPUU or MPP record that has the ‘prescribe_by_brand’ flag set to 1 does have a generic Charting Term and the MPUU or MPP PreferredTerm should be used as a generic Charting Term where required. Another variation from the Preferred Term and the Prescribing is that the trade Charting Terms do not include details of the generic ingredients of the product. LBL160 TermsHospital clinicians were concerned about the level of abbreviation in the LBL80 label terms where these were used for internal hospital labels. Accordingly, we now provide an LBL160 label which has dose forms (for example) no longer abbreviated as a matter of course. These label terms are designed to provide a standard shorter abbreviation (160 characters instead of 1000 characters) for lengthy Preferred Terms for use in hospital pharmacy systems. Note that an MPUU or MPP record that has the ‘prescribe_by_brand’ flag set to 1 still has a generic LBL160 Term. Download 196.51 Kb. Share with your friends:
|