
-- Load up PMPM processing messages.  2018-10-09


if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = '0 INSTRUCTIONS') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('0 INSTRUCTIONS', 'NEVER USE A COMMA. Colons OK.'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'BadF001') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('BadF001', 'Incorrect Referral Number format'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'BadF002') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('BadF002', 'Incorrect Referral Number format'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'BadF003') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('BadF003', 'Incorrect Invoice Date format'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'BadT101') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('BadT101', 'Bad PMPM Invoice Transmission'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'BadT102') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('BadT102', 'PMPM Invoice Report Total mismatch'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'BadT103') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('BadT103', 'PMPM Invoice Record Count transmission mismatch'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'LoadFail5') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('LoadFail5', 'Test Load failure: bad footer totals'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'BadL201') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('BadL201', 'Invoice Line Validation failure: Referral not found'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'CLMUF001') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('CLMUF001', 'Invoice Line Validation failure: Referral Number not found'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'CLMUF03I') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('CLMUF03I', 'Invoice Line Validation failure: ICN not found'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'CLMUF03P') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('CLMUF03P', 'Invoice Line Validation failure: PCN not found'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'CLMUF0607') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('CLMUF0607', 'Invoice Line Validation failure: Date Paid or Amount Paid not found'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'BadFLDCNT') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('BadFLDCNT', 'Invoice Line error: incorrect number of fields'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'BadF004') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('BadF004', 'Incorrect Invoice Line Service Date format'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'BadF005') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('BadF005', 'TBD'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'BadF006') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('BadF006', 'TBD'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'BadF007') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('BadF007', 'Incorrect Invoice Line Paid Amount format'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'BadF008') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('BadF008', 'Incorrect Invoice Line Billed Amount format'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'BadF009') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('BadF009', 'TBD'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'BadF010') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('BadF010', 'TBD'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'BadF011') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('BadF011', 'Incorrect Invoice Line CLIN format'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'LoadOK') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('LoadOK', 'Invoice lines loaded correctly'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'Verf1OK') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('Verf1OK', 'Invoice Line Verified OK -- Part 1'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'Loading') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('Loading', 'Invoice Lines intermediate processing'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'CLMLUR001') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('CLMLUR001', 'Invoice Line Validation failure: Referral Number only on rejected claims'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'CLMUR03I') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('CLMUR03I', 'Invoice Line Validation failure: ICN only on rejected claims'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'CLMUR03P') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('CLMUR03P', 'Invoice Line Validation failure: PCN only on rejected claims'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'CLMUF1011') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('CLMUF1011', 'Invoice Line Validation failure: Provider not found'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'CLMUR1011') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('CLMUR1011', 'Invoice Line Validation failure: Provider only on rejected claims'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'TEST001') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('TEST001', 'Test status'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'RptInvalid') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('RptInvalid', 'Report status Line Invalid: see other status for details'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'RptUnique') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('RptUnique', 'Report status: this ICN or PCN is first unique seen'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'RptDupICN') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('RptDupICN', 'Report status: this ICN seen before'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'RptDupPCN') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('RptDupPCN', 'Report status: this PCN seen before'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'BadT001') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('BadT001', 'Bad PMPM Invoice Transmission'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'BadT002') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('BadT002', 'PMPM Invoice Report Total mismatch'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'BadT003') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('BadT003', 'PMPM Invoice Record Count transmission mismatch'); 
if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = 'CLMUS04D') 
 insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('CLMUS04D', 'Invoice Line Validation failure: Date of Service outside of allowable range'); 


-- SELECT statement to generate some of the above statements. Uncomment and run!

-- select 'if not exists ( select code_letters from das.PMPM_MESSAGE_TEXT where code_letters = ''' + code_letters + ''') '
--   + CHAR(13) + CHAR(10) + ' insert into das.PMPM_MESSAGE_TEXT (code_letters, full_text) values ('''
--   + code_letters + ''', ''' + full_text + '''); ' from das.PMPM_MESSAGE_TEXT;

