ABPN IT

Example - https://abpnit.freshdesk.com/a/tickets/5210 

For a HD ticket that mentions that articles are completed by person_Key, But they are not getting reflected in Oasis.

We need to check the ABPN_WHSE database

select * from [dbo].[moc_Oasis_Exam_Result_From_FTP] WHERE Personkey = <Person_Key> --167612

Here in our case we have an invalid diplomateKey of -1, It can also be a zero.

We need to check some tables in WebCAD database to find the diplomate keys for the articles

SELECT TOP (1000) [MOCArticleID]
,[ArticleID]
,[Status]
,[PilotPool]
,[SubTopic]
,[TopicNumber]
,[Category]
,[CoreCompetency]
,[ArticleAuthor]
,[ArticleTitle]
,[ArticleYear]
,[JournalName]
,[EditionDate]
,[Link]
,[PublisherLink]
,[Keywords]
,[PayStructure]
,[DateAdded]
,[DateUpdated]
  FROM [ABPN_WHSE].[dbo].[moc_Article]

  where articleid in
('A130048',
'A130031',
'A130120')

SELECT * from Diplomate WHERE Person_Key  = <Person_Key> --167612

These are CAP articles and hence we need to update the diplomate key to 136139 using the following query.

UPDATE [moc_Oasis_Exam_Result_From_FTP] SET Diplomatekey = 136139, ExpirationYear = 2026 WHERE Personkey = 167612 AND articleid in

  ('A130048',

'A130031',

'A130120')

The expiration year = the end date of the Oasis learning track.  To determine which oasis leanring track
1. Look at the physician page or query to understand the state of the physician.  Is the cert for which they are doing articles active and in CC OR is it inactive and they are reentering. 

2. If cert in question is inactive and the physician is taking article assessment to re-enter, the physician reentering has to apply, so has the standard applicant-candidate-testtaker-examinee tables.  The assigned oasis learning track id for an inactive cert is on examinee. 

select * from certificate where person_key = {}

select * from applicant where person_key = {} 

select * from examination where examination_key = {} 

select * from examinee where testtaker_key = {} 

3. If the cert in question is active and in CC/ABCC, then oasis leaning track id is on AbccAssessmentModule; look at module that the physician is currently in based on the module dates

select * from ABCCAssesssment where Certificateid = {}

select * from ABCCAssessmentModule where ABCCAssessmentId = {}

4. Look up the oasis learning track id to see it's end date year

select * from oasislearningtrack where OasisLearningTrackId = {}

We should see the articles being reflected the next day when the scheduled job runs