{"text_format":"3","workbook":"xlsx","n_sheets":7,"sheets":["Instructions","Identifying Information","Registrant","Registrant Licenses","Submitter","Opioid Sale","Glossary"],"read":"whole","date_system":"1900"}

{"sheet":"Instructions","index":0,"n_rows":27,"n_cols":1,"rows_kept":27}
["Version 1.3"]
["Introduction"]
["The purpose of this Excel workbook is to simplify the creation of the Opioid Excise Tax Submission XML file.  It is important to note that data entry cells are not immediately validated against the embedded XSD.  Elements are validated only after an export occurs."]
[]
["Summary"]
["This workbook contains seven worksheets.  Each worksheet contains a specific group of information.  There are five data entry worksheets: \"Identifying Information\", \"Registrant\", \"Registrant Licenses\", \"Submitter\", and \"Opioid Sale\".  In addition, there is worksheet named \"Glossary\" that defines the data entry elements.  Each column header on a data entry screen is linked to the corresponding glossary item.  The glossary item is linked back to the column header for convenience."]
[]
["Identifying Information worksheet"]
["Both TIN and reporting year are required to be entered.  Multiple entries are not allowed."]
[]
["Registrant worksheet"]
["All registrant information should be entered, if available.  Multiple entries are not allowed."]
[]
["Registrant Licenses worksheet"]
["At least one license needs to be entered per row.  Multiple rows are allowed.  All combinations of licenses should be entered for a registrant regardless of whether they had sales or not."]
[]
["Submitter worksheet"]
["All submitter information should be entered, if available.  Multiple entires are not allowed."]
[]
["Opioid Sale worksheet"]
["All opioid transaction information should be entered as applicable to the sale(including returns, credits, and cancellations).  Multiple entries are expected."]
[]
["Usage note on entering data in multiple rows"]
["Data entry must be consecutive.  Any row that is blank will cause all subsequent rows to be ignored when generating XML.  A small triangle in the lower right part of the cell will indicate the last row that will be processed."]
[]
["Generating XML"]
["Select the Developer file menu.  In the XML section, select Export.  This will allow you to choose a filename and location.  Select the Export button.  XML validation will be performed AFTER the XML has been generated.  Correct any validation errors before submitting the file."]

{"sheet":"Identifying Information","index":1,"n_rows":1,"n_cols":2,"rows_kept":1}
["TIN","Reporting Year"]

{"sheet":"Registrant","index":2,"n_rows":1,"n_cols":13,"rows_kept":1}
["Registrant Corporation Name","Address Line 1","Address Line 2","Address Line 3","City","State","Zip5","Zip4","Email","Phone Area Code","Phone Prefix","Phone Suffix","Phone Extenstion"]

{"sheet":"Registrant Licenses","index":3,"n_rows":2,"n_cols":4,"rows_kept":2}
["DEA number","CS License","SED License","Licensee Corporate Name"]
["DEANumber","CSLicense","SEDLicense","LicenseeCorporateName"]

{"sheet":"Submitter","index":4,"n_rows":1,"n_cols":14,"rows_kept":1}
["First Name","Last Name","Address Line 1","Address Line 2","Address Line 3","City","State","Zip5","Zip4","Email","Phone Area Code","Phone Prefix","Phone Suffix","Phone Extension"]

{"sheet":"Opioid Sale","index":5,"n_rows":2,"n_cols":31,"rows_kept":2}
["Seller Transaction ID","Invoice Number","Seller DEA number","Seller CS License","Seller SED License","Buyer DEA number","Buyer CS License","Buyer DEA Name","Buyer DEA Address Line 1","Buyer DEA Address Line 2","Buyer DEA Address Line 3","Buyer DEA City","Buyer DEA State","Buyer Zip Code","Buyer Zip Plus4","Buyer Type","Transaction Date","Transaction Type","Original Sale Date","Original Sale Invoice","Transaction Gross Receipt","First Sale Indicator","NDC Code","Drug Name","Strength per unit","Units per container","Number of Containers","Total Metric Quantity","MME Conversion Factor","Total NY MMEs","Wholesale Acquisition Cost is $0.50 or More"]
["SellerTransactionId","SellerInvoice","SellerDeaNumber","SellerCsLicenseNumber","SellerSedLicenseNumber","BuyerDeaNumber","BuyerCsLicenseNumber","BuyerDeaName","Line1","Line2","Line3","City","State","Zip5","Zip4","BuyerType","TransactionDate","Type","OriginalSaleDate","OriginalSaleInvoice","TransactionGrossReceipt","IsFirstSale","NdcCode","DrugName","StrengthPerUnit","UnitsPerContainer","Containers","TotalMetricQuantity","MmeConversionFactor","TotalNyMMEs","IsOverWacThreshold"]

{"sheet":"Glossary","index":6,"n_rows":65,"n_cols":2,"rows_kept":65}
["Term","Definition"]
["TIN","Taxpayer Identification Number (FEIN or EIN assigned by the Internal Revenue Service)"]
["Reporting Year","The year in which the transaction occurred (e.g. 2019)"]
["Registrant Corporation Name","Corporate Name Associated with Taxpayer Identification Number (TIN)"]
["Address Line 1","Corporate Address associated with TIN"]
["Address Line 2","Corporate Address associated with TIN"]
["Address Line 3","Corporate Address associated with TIN"]
["City","Corporate City associated with TIN"]
["State","Corporate State associated with TIN"]
["Zip5","Corporate First 5 of the Zip Code  associated with TIN"]
["Zip4","Corporate Zip 4 associated with TIN"]
["Email","Corporate Email associated with TIN"]
["Phone Area Code","Corporate Phone Number area code associated with TIN"]
["Phone Prefix","Corporate Phone Number (first 3 digits) associated with TIN"]
["Phone Suffix","Corporate Phone Number (last 4 digits) associated with TIN"]
["Phone Extenstion","Corporate Phone Number (extension) associated with TIN"]
["DEA number","DEA registration number, if applicable, for each registrant included in the annual report "]
["CS License","NYS DOH controlled substance license number, if applicable, for each registrant included in the annual report"]
["SED License","NYS Education Department registration number, if applicable, for each registrant included in the annual report"]
["Licensee Corporate Name","DEA registration name if available, otherwise, corporate name as displayed on license/registration certificate(CS or SED)"]
["First Name","First name of person to contact for questions related to the annual report"]
["Last Name","Last name of person to contact for questions related to the annual report"]
["Address Line 1","Address of person to contact regarding the annual report"]
["Address Line 2","Address of person to contact regarding the annual report"]
["Address Line 3","Address of person to contact regarding the annual report"]
["City","City of person to contact regarding the annual report"]
["State","State of person to contact regarding the annual report"]
["Zip5","First 5 of the Zip Code of person to contact regarding the annual report"]
["Zip4","Zip 4 of person to contact regarding the annual report"]
["Email","Email of person to contact regarding the annual report"]
["Phone Area Code","Phone number area code of person to contact regarding the annual report"]
["Phone Prefix","Phone number (first 3 digits) of person to contact regarding the annual report"]
["Phone Suffix","Phone number (last 4 digits) of person to contact regarding the annual report"]
["Phone Extension","Phone number (extension)of person to contact regarding the annual report"]
["Seller Transaction ID","Transaction ID assigned to each opioid transaction(must be a unique value per Reporting Year)"]
["Invoice Number","Invoice number on sales transaction"]
["Seller DEA number","DEA registration number, if applicable, for the registrant that sold the opioid"]
["Seller CS License","NYS DOH controlled substance license number, if applicable, for the registrant that sold the opioid"]
["Seller SED License","State Education Department registration number, if applicable, for the registrant that sold the opioid"]
["Buyer DEA number","DEA registration number of the buyer"]
["Buyer CS License","NYS DOH controlled substance license number, if applicable, of the buyer"]
["Buyer DEA Name","The name of the buyer as it appears on the DEA Registration"]
["Buyer DEA Address Line 1","The address of the buyer as it appears on the DEA Registration"]
["Buyer DEA Address Line 2","The address of the buyer as it appears on the DEA Registration"]
["Buyer DEA Address Line 3","The address of the buyer as it appears on the DEA Registration"]
["Buyer DEA City","The city of the buyer as it appears on the DEA Registration"]
["Buyer DEA State","The state of the buyer as it appears on the DEA Registration"]
["Buyer Zip Code","The first five digits of the zip code of the buyer as it appears on the DEA Registration"]
["Buyer Zip Plus4","The zip plus 4 of the buyer as it appears on the DEA Registration"]
["Buyer Type","Buyer Classification: \n'32' - buyers operating pursuant to Article 32 of Mental Hygiene Law, \n'40' - buyers operating pursuant to Article 40 of Public Health Law,\n'99' - buyers located out of NYS, \n'49' - all other buyers"]
["Transaction Date","Date transaction occurred"]
["Transaction Type","S for Sale, R for Return/Credit/Cancellation  (Use uppercase letters)"]
["Original Sale Date","If Transaction Type is a return, reference the transaction date of the original sale of opioid\n\nIf Transaction Type is a sale, leave this field blank"]
["Original Sale Invoice","If Transaction Type is a return, reference the invoice number of the original sale of opioid\n\nIf Transaction Type is a sale, leave this field blank"]
["Transaction Gross Receipt","Gross receipt total for the opioid transaction (in US Dollars).  Ensure there is a decimal point used, even if the reported value is a whole number."]
["First Sale Indicator","Was this the first sale within or into New York, 'Y' for yes, 'N' for no (Use uppercase letters)\n\n(A first sale is any transfer of title to an opioid unit for consideration where actual or constructive possession of such opioid unit is transferred by a registrant holding title to such opioid unit to a purchaser or its designee in this state for the first time.)"]
["NDC Code","11-digit National Drug Code (NDC) identifying the opioid sold (include leading zeros’s)"]
["Drug Name","Name of the drug as registered with the Food and Drug Administration"]
["Strength per unit","Amount of opioid in a unit, as measured by weight, volume, concentration or other measure"]
["Units per container","Number of units in the saleable container as identified by the 11-digit NDC Code. Ensure there is a decimal point used, even if the reported value is a whole number."]
["Number of Containers","Number of containers in this transaction. Ensure there is a decimal point used, even if the reported value is a whole number."]
["Total Metric Quantity","Units per container multiplied by the number of containers sold.  Ensure there is a decimal point used, even if the reported value is a whole number."]
["MME Conversion Factor","Reference standard of a particular opioid as it relates in potency to morphine as determined by the Commissioner of Health. Ensure there is a decimal point used, even if the reported value is a whole number."]
["Total NY MMEs","Total Morphine Milligram Equivalent's in transaction. Ensure there is a decimal point used, even if the reported value is a whole number. "]
["Wholesale Acquisition Cost is $0.50 or More","Is the wholesale acquisition cost equal to or greater than $0.50?   'Y' for yes, 'N' for no  (Use uppercase letters)"]