ABPN IT

Sometimes CCArticles that show up in Oasis as being completed will not appear complete in the Physician Portal.  This is likely because there was an error when populating the moc_Oasis_Exam_Result_From_FTP table in the ABPN_WHSE database.  The steps for resolving this are as follows:

1. In the ABPN_WHSE database, run the following query

SELECT * FROM moc_Oasis_Exam_Result_From_FTP WHERE PersonKey = [PersonKey]

2. Take note of the DiplomateKey and ExpirationYear fields in the result set.  Records that have not been updated for Physician Portal will have a DiplomateKey value of -1 and an ExpirationYear value of 1111

3. Run the query from step 1 again, and this time append the following: " AND DiplomateKey = -1"  Copy the full list of values under EsoArticleId

4. Run a second query: in ABPN_WHSE:  SELECT * FROM moc_Article WHERE ArticleId IN ([EsoArticleId values])

5. The PilotPool field will indicate the certification the CCArticles are for.  Make sure to view the whole result list in case there are different results for PilotPool, you will need to make a note of these.

6. In WebCAD, run the following query:  SELECT * FROM Diplomate WHERE Person_Key = [PersonKey]

7. Make a note of the Certification Keys and Diplomate_IDs in step 6, and run the following query:  SELECT * FROM Certification WHERE Certification_Key IN ([CertificationKeys])

8. Run the following query:  SELECT * FROM Certificate WHERE Person_Key = [PersonKey] AND CertificateNumber IN [Diplomate_IDs from step 6] 

NOTE:  you may need to have Kevin give you update permissions on the table

9.  You should now have the correct Diplomate Key to use for updating the moc_Oasis_Exam_Result_From_FTP table.  Run the following query:  UPDATE moc_Oasis_Exam_Result_From_FTP SET DiplomateKey = [Diplomate Key to update to], ExpirationYear = [Last year of CCBlock currently being worked on] WHERE PersonKey = [PersonKey] AND DiplomateKey = -1