NDC Code Source Batch Update

Edit

Convert to:

Notes

---------------------------------------------
-- 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

Back

Checklist 1 item

☰ https://jira3.cerner.com/browse/MPM-5757

Links

Link

Role

+ New Link

No links