Produced by Araxis Merge on 6/12/2019 12:14:11 PM Eastern Daylight Time. See www.araxis.com for information about Merge. This report uses XHTML and CSS2, and is best viewed with a modern standards-compliant browser. For optimum results when printing this report, use landscape orientation and enable printing of background images and colours in your browser.
# | Location | File | Last Modified |
---|---|---|---|
1 | NonVA_PIT_SDD.zip\PITDI_PDC.zip\doc | Provider_Cleanup_Test_Cases.docx | Thu Jun 6 14:47:38 2019 UTC |
2 | NonVA_PIT_SDD.zip\PITDI_PDC.zip\doc | Provider_Cleanup_Test_Cases.docx | Wed Jun 12 15:43:22 2019 UTC |
Description | Between Files 1 and 2 |
|
---|---|---|
Text Blocks | Lines | |
Unchanged | 1 | 520 |
Changed | 0 | 0 |
Inserted | 0 | 0 |
Removed | 0 | 0 |
Whitespace | |
---|---|
Character case | Differences in character case are significant |
Line endings | Differences in line endings (CR and LF characters) are ignored |
CR/LF characters | Not shown in the comparison detail |
No regular expressions were active.
1 | #1 Happy P ath – All NPI and Ta xID Match, No New Pr oviders ar e Inserted | |
2 | ||
3 | All valid provider f ound NPI m atch. No new record s to dim_p rovider. F or all sam ple npis, check the last updat ed date an d the etl_ batch_id, the etl_ba tch_id mus t be diffe rent from the one we ’re loadin g (see the last quer y) | |
4 | ||
5 | Run Datast age stream HCFA_m_St ream - fi le CCNN-Te rminalStat us-HCFA-CC NNC-201905 16.txt | |
6 | Query to m ake sure e xisting bi lling prov iders were used. | |
7 | ||
8 | select bil ling_provi der_key, c laim_id, p .* from di m_va_claim c | |
9 | join dim_p rovider p on billing _provider_ key = prov ider_key | |
10 | where c.et l_batch_id in (selec t top 1 et l_batch_id from clai m_batch_lo g | |
11 | where file _name = 'C CNN-Termin alStatus-H CFA-CCNNC- 20190516.t xt' order by start_d ate_time d esc) | |
12 | ||
13 | Expected r esults | |
14 | Count: 672 | |
15 | Sample pro viders npi : 171098 0354, 1336 144617, 17 50386322 ( all 672 fo und match, no missin g on SF) | |
16 | ||
17 | Query to m ake sure e xisting re ferring pr oviders we re used. | |
18 | ||
19 | select ref erring_pro vider_key, claim_id, p.* from dim_va_cla im c | |
20 | join dim_p rovider p on referri ng_provide r_key = pr ovider_key | |
21 | where c.et l_batch_id in (selec t top 1 et l_batch_id from clai m_batch_lo g | |
22 | where file _name = 'C CNN-Termin alStatus-H CFA-CCNNC- 20190516.t xt' order by start_d ate_time d esc) and r eferring_p rovider_ke y != -1 | |
23 | ||
24 | Expected r esults | |
25 | Count: 412 | |
26 | Sample pro viders npi : 134624 5438,14876 59181,1689 679144 | |
27 | ||
28 | Query to make sure missing re ferring pr oviders we re used. | |
29 | ||
30 | select ref erring_pro vider_key, claim_id, p.* from dim_va_cla im c | |
31 | join dim_p rovider p on referri ng_provide r_key = pr ovider_key | |
32 | where c.et l_batch_id in (selec t top 1 et l_batch_id from clai m_batch_lo g | |
33 | where file _name = 'C CNN-Termin alStatus-H CFA-CCNNC- 20190516.t xt' order by start_d ate_time d esc) and r eferring_p rovider_ke y = -1 | |
34 | ||
35 | Expected r esults | |
36 | Count: 260 | |
37 | Sample pro viders cla im_id for missing re ferring pr ovider npi : 3019066 0000018000 0, 3019070 0000017200 0, 3019072 0000052700 0 | |
38 | Query to m ake sure e xisting re ndering pr oviders we re used. | |
39 | ||
40 | select ren dering_pro vider_key, p.* from F_PROFESSI ONAL_MEDIC AL_CLAIM_D ETAILS c | |
41 | join dim_p rovider p on renderi ng_provide r_key = pr ovider_key | |
42 | where c.et l_batch_id in (selec t top 1 et l_batch_id from clai m_batch_lo g | |
43 | where file _name = 'C CNN-Termin alStatus-H CFA-CCNNC- 20190516.t xt' order by start_d ate_time d esc) and r endering_p rovider_ke y != -1 | |
44 | ||
45 | Expected r esults | |
46 | Count: 812 | |
47 | Sample pro viders npi : 1689679 144, 17705 88303, 177 0588303 | |
48 | Query to m ake sure m issing ren dering pro viders wer e used. | |
49 | ||
50 | select ren dering_pro vider_key, source_cl aim_line_i d, p.* fro m F_PROFES SIONAL_MED ICAL_CLAIM _DETAILS c | |
51 | join dim_p rovider p on rende ring_provi der_key = provider_k ey | |
52 | where c.et l_batch_id in (selec t top 1 et l_batch_id from clai m_batch_lo g | |
53 | where file _name = 'C CNN-Termin alStatus-H CFA-CCNNC- 20190516.t xt' order by start_d ate_time d esc) and r endering_p rovider_ke y = -1 | |
54 | ||
55 | Expected r esults | |
56 | Count: 292 | |
57 | Sample pro viders sou rce_claim_ line_id fo r missing rendering provider n pi: 12085 0, 138261, 140346 | |
58 | Query to m ake sure m issing ref erring pro viders wer e used. | |
59 | ||
60 | select ren dering_fac ility_prov ider_key, p.* from d im_profess ional_clai m c | |
61 | join dim_p rovider p on renderi ng_facilit y_provider _key = pro vider_key | |
62 | where c.et l_batch_id in (selec t top 1 et l_batch_id from clai m_batch_lo g | |
63 | where file _name = 'C CNN-Termin alStatus-H CFA-CCNNC- 20190516.t xt' order by start_d ate_time d esc) and r endering_f acility_pr ovider_key != -1 | |
64 | ||
65 | Expected r esults | |
66 | Count: 316 | |
67 | Sample pro viders npi : 128562 1839, 1770 570202, 14 17946021 | |
68 | Query to m ake sure n o new prov iders were inserted | |
69 | ||
70 | select * f rom dim_pr ovider whe re etl_bat ch_id in ( select top 1 etl_bat ch_id from claim_bat ch_log | |
71 | where file _name = 'C CNN-Termin alStatus-H CFA-CCNNC- 20190516.t xt' order by start_d ate_time d esc) | |
72 | ||
73 | Expected r esults | |
74 | Count: 0 | |
75 | #2 New NPI , no Match with any Existing N PIs, New R ecords are Inserted into DIM_P ROVIDER an d Validate d Against NPPES | |
76 | Expect to take the N PI match a nd Ignore TIN. Inser t unmatche d NPI to d im_provide r | |
77 | Run Datast age stream HCFA_m_St ream - f ile CCNN-T erminalSta tus-HCFA-C CNNC-20190 525.txt | |
78 | Run nppes validator. Notice th at in prod uction it will be ru n continuo usly, here , we execu te it on d emand for the purpos e of testi ng. | |
79 | The valida tor will q uery NPPES and updat e DIM_PROV IDER. Expe cted resul ts: is_npi _valid=tru e, npi_che ck_date is not null, taxonomy is fully p opulated ( note new f ields in t he provide r_taxonomy table. | |
80 | ||
81 | Query to m ake sure e xisting bi lling prov iders were used, no TIN match. | |
82 | ||
83 | select bil ling_provi der_key, c laim_id, p .* from di m_va_claim c | |
84 | join dim_p rovider p on billing _provider_ key = prov ider_key | |
85 | where c.et l_batch_id in (selec t top 1 et l_batch_id from clai m_batch_lo g | |
86 | where file _name = 'C CNN-Termin alStatus-H CFA-CCNNC- 20190525.t xt' order by start_d ate_time d esc) and n pi in (199 2786784, 1 144226986, 189178178 7) | |
87 | ||
88 | Expected r esults | |
89 | Count: 7 | |
90 | Sample pro viders npi : 1992786 784, 11442 26986, 189 1781787 | |
91 | Query to m ake sure n ew provide rs were in serted | |
92 | ||
93 | select * f rom dim_pr ovider whe re etl_bat ch_id in | |
94 | (select to p 1 etl_ba tch_id fro m claim_ba tch_log wh ere file_n ame = 'CCN N-Terminal Status-HCF A-CCNNC-20 190525.txt ' order by start_dat e_time des c) | |
95 | and is_npi _valid=1 | |
96 | Expected r esults | |
97 | Count: 3 | |
98 | Sample pro viders npi : 1992786 784, 11442 26986, 189 1781787 | |
99 | #3 NPI is Blank, TIN is Presen t. Expect to match TIN. No Ne w Records in DIM_PRO VIDER | |
100 | Expect to take tin m atch for m issing npi . No new r ecord to d im_provide r | |
101 | Run Datast age stream UB92_m_St ream - file CCNN- TerminalSt atus-UB04- CCNNC-2019 0530.txt | |
102 | ||
103 | Query to make sure all billin g provider s were mat ched | |
104 | ||
105 | select bil ling_provi der_key, c laim_id, p .* from di m_va_claim c | |
106 | join dim_p rovider p on billing _provider_ key = prov ider_key | |
107 | where c.et l_batch_id in (selec t top 1 et l_batch_id from clai m_batch_lo g | |
108 | where file _name = 'C CNN-Termin alStatus-U B04-CCNNC- 20190530.t xt' order by start_d ate_time d esc) and b illing_pro vider_key = -1 | |
109 | ||
110 | Expected r esults | |
111 | Count: 0 | |
112 | Query to m ake sure s ome billin g provider s were mat ched by ti n | |
113 | ||
114 | select bil ling_provi der_key, c laim_id, p .* from di m_va_claim c | |
115 | join dim_p rovider p on billing _provider_ key = prov ider_key | |
116 | where c.e tl_batch_i d in | |
117 | (select to p 1 etl_ba tch_id fro m claim_ba tch_log wh ere file_n ame = 'CCN N-Terminal Status-UB0 4-CCNNC-20 190530.txt ') and tax _id is not null | |
118 | ||
119 | Expected r esults | |
120 | Count: 1 | |
121 | Sample pro viders tin : 0102115 01 | |
122 | Query to m ake sure n o new tin records to dim_provi der | |
123 | ||
124 | select * f rom dim_pr ovider whe re etl_bat ch_id in | |
125 | (select to p 1 etl_ba tch_id fro m claim_ba tch_log wh ere file_n ame = 'CCN N-Terminal Status-UB0 4-CCNNC-20 190530.txt ') | |
126 | and tax_id is not nu ll | |
127 | ||
128 | Expected r esults | |
129 | Count: 0 | |
130 | #4 No NPI, No TIN Ma tch. Shou ld create a new TIN provider r ecord. TIN is update d from cha mp_va_vend or table | |
131 | Run Datast age stream UB92_m_St ream fil e CCNN-Ter minalStatu s-UB04-CCN NC-2019053 1.txt | |
132 | ||
133 | Query to make sure some billi ng provide rs were ma tched by t in | |
134 | ||
135 | select bil ling_provi der_key, c laim_id, p .* from di m_va_claim c | |
136 | join dim_p rovider p on billing _provider_ key = prov ider_key | |
137 | where c.e tl_batch_i d in | |
138 | (select to p 1 etl_ba tch_id fro m claim_ba tch_log wh ere file_n ame = 'CCN N-Terminal Status-UB0 4-CCNNC-20 190531.txt ') and tax _id is not null | |
139 | ||
140 | Expected r esults | |
141 | Count: 66 | |
142 | Query to c heck new t in records to dim_pr ovider | |
143 | ||
144 | select * f rom dim_pr ovider whe re etl_bat ch_id in | |
145 | (select to p 1 etl_ba tch_id fro m claim_ba tch_log wh ere file_n ame = 'CCN N-Terminal Status-UB0 4-CCNNC-20 190531.txt ') | |
146 | and tax_id is not nu ll | |
147 | ||
148 | ||
149 | Expected r esults | |
150 | Count: 2 | |
151 | Sample pro viders tin : 0202603 34, 261739 498 | |
152 | ||
153 | Run the TI N validato r (same pr ocess as N PI validat or). The v alidator w ill query the champv a_vendor t able and u pdate dim_ provider f rom that t able. | |
154 | Expected r esults: is _tin_valid =true, the location is populat ed from ch ampva_vend or. | |
155 | ||
156 | #5 Provide r has DEA# and no NP I. New pr ovider rec ord should be create d with DEA # loaded. | |
157 | ||
158 | Run Datast age stream NCPDP_m_S tream - file PAID- ToScore-NC PDP-RX-201 905310012. dat | |
159 | ||
160 | Query to m ake sure e xisting pr escriber p roviders w ere used. | |
161 | ||
162 | select ref erring_pro vider_key, claim_id, p.* from dim_va_cla im c | |
163 | join dim_p rovider p on referri ng_provide r_key = pr ovider_key | |
164 | where c.et l_batch_id in (selec t top 1 et l_batch_id from clai m_batch_lo g | |
165 | where file _name = 'P AID-ToScor e-NCPDP-RX -201905310 012.dat' o rder by st art_date_t ime desc) and dea_nu mber is no t null | |
166 | ||
167 | Expected r esults | |
168 | Count: 85 | |
169 | ||
170 | Query to c heck new D EA# for pr escriber p roviders w ere insert ed | |
171 | ||
172 | select * f rom dim_pr ovider whe re etl_bat ch_id in | |
173 | (select to p 1 etl_ba tch_id fro m claim_ba tch_log wh ere file_n ame = 'PAI D-ToScore- NCPDP-RX-2 0190531001 2.dat') | |
174 | and dea_nu mber is no t null and npi is nu ll | |
175 | ||
176 | ||
177 | Expected r esults: | |
178 | Count: 48 | |
179 | Sample pro viders dea : MG01947 32, FS4549 599, MH122 5451 | |
180 | Expected r esults: NP I and TAX_ ID are nul l | |
181 | ||
182 | #6 TIN Val idation. Should cre ate a new TIN provid er record. TIN Valid ation Fail | |
183 | Run Datast age stream HCFA_m_St ream fil e CCNN-Ter minalStatu s-HCFA-CCN NC-2019051 2.txt | |
184 | ||
185 | Query to make sure some billi ng provide rs were ma tched by t in | |
186 | ||
187 | select bil ling_provi der_key, b illing_pro vider_TIN, claim_id, p.* from dim_va_cla im c | |
188 | join dim_p rovider p on billing _provider_ key = prov ider_key | |
189 | where c.et l_batch_id in (selec t top 1 et l_batch_id from clai m_batch_lo g | |
190 | where file _name = 'C CNN-Termin alStatus-H CFA-CCNNC- 20190512.t xt' order by start_d ate_time d esc) | |
191 | and tax_id is not nu ll and npi is null | |
192 | ||
193 | Expected r esults | |
194 | Count: 11 | |
195 | Query to c heck new t in records to dim_pr ovider and validatio n | |
196 | ||
197 | select * f rom dim_pr ovider whe re etl_bat ch_id in ( select top 1 etl_bat ch_id from claim_bat ch_log whe re file_na me = 'CCNN -TerminalS tatus-HCFA -CCNNC-201 90512.txt' order by start_date _time desc ) | |
198 | ||
199 | Expected r esults: is _tin_valid =false, ch va_vendor_ row_id=nul l | |
200 | Count: 8 N egative va lidation: 8 | |
201 | Sample pro viders tin : 7777770 01, 777777 003, 77777 7005, 7777 77007, 777 777002, 77 7777004, 7 77777006, 777777008 | |
202 | ||
203 | #7 Failed NPI Valida tion. Sho uld create a new NPI provider record. | |
204 | Run Datast age stream HCFA_m_St ream fil e CCNN-Ter minalStatu s-HCFA-CCN NC-2019032 0.txt | |
205 | ||
206 | Query to m ake sure s ome billin g provider s were mat ched by np i | |
207 | ||
208 | select bil ling_provi der_key, c laim_id, p .* from di m_va_claim c | |
209 | join dim_p rovider p on billing _provider_ key = prov ider_key | |
210 | where c.et l_batch_id in (selec t top 1 et l_batch_id from clai m_batch_lo g | |
211 | where file _name = 'C CNN-Termin alStatus-H CFA-CCNNC- 20190320.t xt' order by start_d ate_time d esc) | |
212 | ||
213 | ||
214 | ||
215 | Expected r esults. | |
216 | Count: 1 | |
217 | ||
218 | Query to check new npi record s to dim_p rovider an d validati on | |
219 | ||
220 | select * f rom dim_pr ovider whe re etl_bat ch_id in ( select top 1 etl_bat ch_id from claim_bat ch_log whe re file_na me = 'CCNN -TerminalS tatus-HCFA -CCNNC-201 90512.txt' order by start_date _time desc ) | |
221 | ||
222 | Expected r esults.: i s_npi_vali d=false | |
223 | Count: 1 N egative va lidation: 1 | |
224 | Sample pro viders npi : 6666666 008 | |
225 | ||
226 | #8 Refresh of the Ex isting Pro vider from NPPES | |
227 | NPPES Vali dator cont inuously c hecks all providers inserted f rom NPPES where the last check was more than 2 day s ago. If nppes_last _updated_d ate is old er than th e update d ate in NPP ES, the pr ovider wil l be updat ed with th e data fro m NPPES. | |
228 | update one of the pr oviders wi th the fak e name and old nppes update da te | |
229 | update dim _provider | |
230 | set provid er_name='f ake', npi_ check_date _time='201 0-01-01', nppes_last _updated_d ate='2010- 01-01' | |
231 | where npi= '136691987 0' and sou rce_system ='nppes' | |
232 | run valid ator, it w ill restor e the name | |
233 | select pro vider_key, tax_id, n pi, is_cur rent, sour ce_system, provider_ first_name , provider _last_name , provider _name, | |
234 | nppes_last _updated_d ate, npi_c heck_date_ time, last _updated_u ser, last_ updated_da te | |
235 | from dim_p rovider | |
236 | where npi= '136691987 0' and pro vider_name like 'kas di%' | |
237 | order by n pi_check_d ate_time d esc | |
238 | -- expecte d results: 1 | |
239 | #9 CCN or TPA Provid er | |
240 | Search for TriWest | |
241 | Is_ccn_tpa =true, is_ tax_id_val id=true | |
242 | Tax_id_che ck_time is not null | |
243 | Query: | |
244 | select pro vider_name , tax_id, npi, is_tp a_ccn, is_ tax_id_val id | |
245 | from dim_p rovider | |
246 | where is_t pa_ccn=1 | |
247 | -- expecte d results: 2 | |
248 | #10 Report with Prov iders that Failed Va lidation | |
249 | ||
250 | #11 TIN Va lidation. Should cr eate a new TIN provi der record . TIN Vali dation Suc cess | |
251 | Run Datast age stream CPE_m_Str eam file c pe-termina l-details- 2019052523 18.txt | |
252 | Inserted 2 9 valid TI Ns: | |
253 | select * f rom dim_pr ovider whe re etl_bat ch_id in ( select top 1 etl_bat ch_id from claim_bat ch_log | |
254 | where file _name = 'c pe-termina l-details- 2019052523 18.txt' or der by sta rt_date_ti me desc) | |
255 | and is_tax _id_valid= 1 and chva _row_id is not null | |
256 | Inserted 3 invalid T INS (inact ive in cha mpva): | |
257 | select * f rom dim_pr ovider whe re etl_bat ch_id in ( select top 1 etl_bat ch_id from claim_bat ch_log | |
258 | where file _name = 'c pe-termina l-details- 2019052523 18.txt' or der by sta rt_date_ti me desc) | |
259 | and is_tax _id_valid= 0 | |
260 | NPI on the file is i gnored, di m_va_payme nt.provide r_key is a lways -1 ( previously was match ed by NPI) |
Araxis Merge (but not the data content of this report) is Copyright © 1993-2016 Araxis Ltd (www.araxis.com). All rights reserved.