Notes: NDC Code Source Batch Update

--------------------------------------------- -- NDC Code Source Population --------------------------------------------- JIRA: MPM-5757 | https://jira3.cerner.com/browse/MPM-5757 Estimate <1 Day Request: Update product_code_source_id in mdp_product_code for NDCs where source is determinable via FDA match. Logic: The LEFT5 of an NDC is the labeler code (manufacturer identifier). NDCs sharing the same LEFT5 share the same manufacturer, and therefore the same product_code_source_id. Artifact: Gerry Hobson to provide the query Required changes: INSERT new row into mdp_product_code_source: product_code_source_id = 4, description = "UDI" UPDATE statement on mdp_product_code: setting product_code_source_id = x for qualifying NDCs (same LEFT5 = same source) SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select distinct a.ndc_code, left (a.ndc_code,5) + substring (a.ndc_code, 7,5) as 'ndc_10' from V_mdp_product_code a where left(a.ndc_code,5) in ( '10006','10019','10038','10119','10122','10135','10139','10148','10158','10181','10207','10310','10331','10337','10356','10361','10370','10518','10530','10542','10544','10565','10572','10599','10625','10631','10641','10702','10733','10738','10812','10858','10885','10914','10922','10939','10956','10960','10961','10974','11017','11042','11086','11169','11370','11383','11399','11444', '11528','11530','11534','11673','11694','11701','11704','11743','11763','11788','11808','11845','11868','11917','11926','11980','11994','12162','12258','12277','12280','12463','12539','12547','12634','12745','12758','12772','12830','12843','12939','13107','13273','13279','13310','13341','13411','13517','13533','13548','13551','13553','13613','13632','13668','13811','13913','13925','14629','14789','14832','15014', '15127','15187','15310','15330','15338','15370','15456','15718','15749','16103','16110','16241','16252','16477','16500','16571','16590','16714','16729','16781','16837','16864','16881','16887','17156','17236','17238','17271','17350','17478','17518','17714','17772','17808','18657','18754','18837','18860','19515','19650','19810','20091','20254','20482','20536','20555','21245','21695','21724','22510','22537','22840', '23155','23317','23359','23360','23589','23594','23635','23710','23900','24090','24108','24201','24208','24236','24330','24338','24357','24385','24470','24477','24478','24658','24839','24856','24979','24987','25021','25077','25121','25208','25382','25682','26608','27241','27437','27495','27505','27808','27854','28105','28595','29033','29300','29336', '30014','30698','30727','30768','31064','31248','31357','31382','31722','31928','32909','33261','33332','33342','33358','33674','33739','33753','34674','35046','35356','35573','35787','36000','36602','36652','36769','36800','37000','37205','37341','37937','38137','38245','38341','38396','38485','38739','39686','39769','40032','40042','41250','42002', '42023','42043','42192','42195','42291','42292','42367','42515','42543','42546','42549','42571','42582','42658','42702','42747','42794','42799','42806','42808','42826','42847','42858','42865','42874','42998','43063','43066','43068','43199','43353','43376','43386','43393','43469','43478','43538','43547','43595','43598','43825','44001','44183','44206', '44523','44567','45043','45802','45861','45945','45963','46026','46028','46122','46287','46581','47682','47781','47783','47918','48102','48433','48818','49035','49158','49281','49348','49349','49401','49411','49471','49483','49502','49609','49643','49663','49685','49702','49708','49771','49781','49884','49938','49999','50102','50111','50192','50222', '50224','50242','50383','50419','50458','50474','50484','50505','50532','50564','50580','50742','50804','50816','50844','50991','51013','51021','51079','51097','51138','51144','51167','51224','51248','51285','51292','51293','51407','51477','51479','51655','51660','51759','51772','51803','51817','51862','51991','52015','52054','52118','52152','52183', '52244','52246','52268','52276','52343','52376','52427','52536','52544','52565','52584','52605','52682','52959','53014','53097','53150','53329','53436','53489','53746','54092','54162','54348','54396','54436','54458','54482','54505','54531','54738','54746','54766','54799','54838','54879','55111','55150','55289','55292','55390','55513','55515','55648', '55741','55806','55910','57237','57278','57344','57664','57665','57844','57893','57894','57896','57902','57962','57970','58060','58063','58160','58177','58178','58281','58394','58406','58407','58463','58487','58657','58716','58768','58809','58914','59011','59075','59088','59137','59148','59212','59310','59338','59353','59385','59390','59467','59572', '59584','59627','59630','59640','59676','59702','59726','59741','59746','59767','59779','59883','59922','59923','60258','60267','60429','60432','60598','60635','60687','60758','60760','60763','60793','60809','60842','60846','60951','60977','61168','61314','61364','61442','61570','61703','61744','61748','61755','61874','61894','61938','61971','62037', '62107','62175','62225','62541','62559','62577','62584','62592','62756','62794','62847','62856','62935','63020','63029','63032','63256','63304','63307','63323','63395','63402','63459','63481','63646','63704','63713','63717','63736','63739','63824','63833','63851','63857','63868','63874','64011','64067','64116','64125','64193','64281','64370','64376', '64380','64406','64455','64543','64597','64678','64679','64682','64693','64720','64764','64875','64896','64950','64980','65086','65162','65174','65197','65219','65224','65250','65293','65483','65580','65597','65628','65649','65726','65757','65862','65880','65974','66019','66213','66215','66220','66267','66302','66336','66424','66435','66479','66490', '66500','66521','66657','66658','66663','66689','66758','66780','66869','66887','66992','66993','67066','67159','67172','67213','67253','67308','67386','67402','67405','67425','67457','67467','67544','67618','67767','67781','67857','67871','67877','67919','67979','68001','68012','68016','68032','68047','68084','68134','68135','68152','68180','68209', '68220','68308','68330','68382','68387','68453','68462','68546','68611','68645','68669','68682','68712','68727','68752','68774','68782','68791','68803','68820','68850','68982','69007','69067','69076','69097','69101','69166','69171','69176','69230','69251','69315','69329','69339','69367','69387','69401','69442','69468','69543','69547','69618','69654', '69656','69668','69911','69918','70010','70030','70069','70362','70436','70460','70461','70564','70651','70655','70860','71090','71104','71351','71469','71525','71717','72124','72205','72426','72493','72511','72512','72572','72694','72789','72893','73063','73141','73362','73462','75834','75840','75854','75987','76045','76075','76125','76179','76204', '76234','76282','76299','76310','76325','76346','76385','76388','76420','76431','76439','76478','78206','80159','82584','82959','89141','99207') and a.country_id = 13 and a.ndc_code_10 is null and a.ndc_code not in ( select ndc_code from mdp_product_code_us where product_code_id in ( select product_code_id from V_mdp_product_code where ndc_code_10 is null and left (ndc_code,1) = '0') union SELECT a.ndc_code from mdp_product_code_us a where a.product_code_id in ( select product_code_id from V_mdp_product_code where ndc_code_10 is null and substring (ndc_code, 6,1) = '0' and substring (ndc_code, 10,1) > 0 and left (ndc_code,1) > '0' ) union SELECT a.ndc_code from mdp_product_code_us a where a.product_code_id in ( select product_code_id from V_mdp_product_code where a.ndc_code_10 is null and substring (a.ndc_code, 6,1) > 0 and substring (a.ndc_code, 10,1) = 0 and left (ndc_code,1) > '0')) union select a.ndc_code, left (a.ndc_code,9) + substring (a.ndc_code, 11,1) from V_mdp_product_code a where left(a.ndc_code,5) in ( '10106','10147','10223','10267','10481','10742','10802','10866','11509','11523','11716','11822','11940','12090','12496','12870','14362','15054', '15256','15455','15584','17205','17270','17314','17317','17474','17856','23490','23558','25201','25332','35781','38779','39822','41167','44087','44178','45567','48878','50090', '50332','50488','51525','51672','51754','52380','52609','53270','53451','53808','54569','54643','54868','55056','55154','55566','58232','58468','59365','59366','59617','59730', '59762','59772','60492','60505','61953','61958','62011','62484','63653','63672','66621','66685','66715','67296','68071','68599','68788','68875','68968','69968','70000','71399', '71858') and a.country_id = 13 and a.ndc_code_10 is null and a.ndc_code not in ( select ndc_code from mdp_product_code_us where product_code_id in ( select product_code_id from V_mdp_product_code where ndc_code_10 is null and left (ndc_code,1) = '0') union SELECT a.ndc_code from mdp_product_code_us a where a.product_code_id in ( select product_code_id from V_mdp_product_code where ndc_code_10 is null and substring (ndc_code, 6,1) = '0' and substring (ndc_code, 10,1) > 0 and left (ndc_code,1) > '0' ) union SELECT a.ndc_code from mdp_product_code_us a where a.product_code_id in ( select product_code_id from V_mdp_product_code where a.ndc_code_10 is null and substring (a.ndc_code, 6,1) > 0 and substring (a.ndc_code, 10,1) = 0 and left (ndc_code,1) > '0')) union select ndc_code, right (ndc_code, 10) from mdp_product_code_us where product_code_id in ( select product_code_id from V_mdp_product_code where ndc_code_10 is null and left (ndc_code,1) = '0') union SELECT a.ndc_code, left (ndc_code, 5) + right (ndc_code,5) from mdp_product_code_us a where a.product_code_id in ( select product_code_id from V_mdp_product_code where ndc_code_10 is null and substring (ndc_code, 6,1) = '0' and substring (ndc_code, 10,1) > 0 and left (ndc_code,1) > '0' ) union SELECT a.ndc_code, left (ndc_code, 9) + right (ndc_code,1) from mdp_product_code_us a where a.product_code_id in ( select product_code_id from V_mdp_product_code where a.ndc_code_10 is null and substring (a.ndc_code, 6,1) > 0 and substring (a.ndc_code, 10,1) = 0 and left (ndc_code,1) > '0') order by a.ndc_code