Hi,
The stored procedure below absolutely crushes my system and my own lack of expertise keeps me from explaining exactly why. It was running today against about 80,000 records and once the lights started dimming I figured I had to kill it.
In my limited experience, I have found informix to be smoking fast compared to our MS-SQL db's, so I am wondering if this SP is to blame. Can anyone take a quick eyeball at it for any obvious issues the programmers approach has created?
Its hard to read in a browser page and might retain a stepped format better if you paste it into a text tool.
I throw myself on your mercy
oh wise and gracious gurus.
Runnning IDS 9.4, raw disk with a gig of mem if it matters and informix is really the only thing on this box and aty the time this process was the only thing running.
-----------------------------------------------------------
drop procedure "informix".sp_client_hist_ins;
create procedure "informix".sp_client_hist_ins(strBatchNo char(8))
DEFINE intRowCount integer;
DEFINE intGoodReqRecieved integer;
DEFINE intTotalReqReceived integer;
DEFINE intTotalMemberErr integer;
DEFINE intTotalSocErr integer;
DEFINE intTotalClmErr integer;
DEFINE intTotalRecords integer;
DEFINE dteLastUpDate date;
DEFINE intBatch_id integer;
DEFINE intErrorCode integer;
DEFINE intMemberCount integer;
DEFINE intFirstReqCount integer;
DEFINE intMarketCount integer;
--
-- Setup debug info
--
IF (SELECT dbinfo('dbhostname')
FROM systables
WHERE tabid = 1 ) = 'test' THEN
set debug file to '/u/informix/trace/sp_client_hist_ins.dbg';
trace on;
END IF
-- Check and see if any client history requests exist for
-- the specified strBatchNo and if no error create batch statistics
SELECT count(*)
INTO intRowCount
FROM client_history
WHERE batch_no = strBatchNo;
SELECT batch_id
INTO intBatch_id
FROM batch_stats
WHERE batch_no = strBatchNo;
INSERT INTO batch_cnt(batch_cnt_id,batch_id,cnt_id,value)
VALUES(0,intBatch_id,"6",intRowCount);
IF intRowCount = 0 then
raise exception -746,0,
'sp_client_hist_ins: No data found for batch ' || intBatch_id;
END IF
UPDATE client_history
SET error_code = "G"
WHERE error_code is null
OR trim(error_code) = "";
--total requests recieved
--
SELECT count(distinct request_id)
INTO intTotalReqReceived
FROM client_history;
INSERT INTO batch_cnt(batch_cnt_id,batch_id,cnt_id,value)
VALUES(0,intBatch_id,"2",intTotalReqReceived);
--total claims recieved
SELECT count(distinct request_id)
INTO intGoodReqRecieved
FROM client_history
WHERE error_code = "G";
IF intGoodReqRecieved <> 0 THEN
INSERT INTO batch_cnt(batch_cnt_id,batch_id,cnt_id,value)
VALUES(0,intBatch_id,"3",intGoodReqRecieved);
END IF
--total err "Member Not Found"
SELECT count(*)
INTO intTotalMemberErr
FROM client_history
WHERE error_code = "M";
IF intTotalMemberErr <> 0 THEN
INSERT INTO batch_cnt(batch_cnt_id,batch_id,cnt_id,value)
VALUES(0,intBatch_id,"4",intTotalMemberErr);
END IF
SELECT count(*)
INTO intTotalSocErr
FROM client_history
WHERE error_code = "S";
IF intTotalSocErr <> 0 THEN
INSERT INTO batch_cnt(batch_cnt_id,batch_id,cnt_id,value)
VALUES(0,intBatch_id,"8",intTotalSocErr);
END IF
--determine Last Update Date of Data Warehouse
SELECT distinct(last_update_date)
INTO dteLastUpDate
FROM client_history
WHERE last_update_date is not null;
begin
define intReqID integer;
define strMemberId varchar(23);
define strMemberDataSrc varchar(2);
define strPNamePref varchar(4);
define strP_FirstName varchar(30);
define strP_MiddleName varchar(10);
define strP_LastName varchar(30);
define strP_NameSuf varchar(5);
define strP_RelCode varchar(14);
define dteP_Dob date;
define strS_Ssn varchar(10);
define strS_NamePref varchar(5);
define strS_FName varchar(30);
define strS_LName varchar(30);
define strS_NameSuf varchar(5);
define strS_Addr1 varchar(35);
define strS_Addr2 varchar(35);
define strS_City varchar(30);
define strS_Sate varchar(2);
define strS_Zip5 varchar(5);
define strS_Phone1 varchar(3);
define strS_Phone2 varchar(3);
define strS_Phone3 varchar(4);
define strGrpNo varchar(8);
define strAccount varchar(5);
define strGrpSuf varchar(8);
define strGrpName varchar(40);
define strMarket varchar(5);
define dteD_Injury date;
define strBusSeg varchar(5);
define strFundCat varchar(3);
define strLangInd varchar(6);
define strProvNo varchar(14);
define strProvName varchar(30);
define strProvAddr1 varchar(35);
define strProvCity varchar(30);
define strProvState varchar(2);
define strProvZip varchar(5);
define strClaimNo varchar(19);
define strFormatClaimNo varchar(19);
define dteD_ServBegin date;
define dteD_ServEnd date;
define dteD_Paid date;
define strFirstCATCODE varchar(6);
define strSecondCATCODE varchar(6);
define strThirdCATCODE varchar(6);
define strFourthCATCODE varchar(6);
define strFifthCATCODE varchar(6);
define strFrmtCATCODE_1 varchar(6);
define strCATCODEMatch_1 varchar(1);
define strFrmtCATCODE_2 varchar(6);
define strCATCODEMatch_2 varchar(1);
define strFrmtCATCODE_3 varchar(6);
define strCATCODEMatch_3 varchar(1);
define strFrmtCATCODE_4 varchar(6);
define strCATCODEMatch_4 varchar(1);
define strFrmtCATCODE_5 varchar(6);
define strCATCODEMatch_5 varchar(1);
define strCptCode varchar(5);
define strPaidAmount varchar(12);
define strBilledAmount varchar(12);
define strPaidStatus varchar(5);
define strLineItemStat varchar(5);
define strReversalCode varchar(1);
define strSumCode varchar(1);
define strClmTypeCode varchar(1);
define strClmStatCode varchar(1);
define strOrigClmCode varchar(1);
define strErrorCode varchar(2);
define strHT_Flag char(1);
define intFileKey integer;
define intParentId integer;
define strClmLineId varchar(20);
-- define blnFirstRecord integer;
define intChildId integer;
define m_member_id varchar(23);
define m_group_no varchar(6);
define str char(1024);
define strFundCat_prev varchar(3);
define strGrpNo_prev varchar(6);
define strFormatMemberId varchar(11);
----- Begin Variable init test ---
let intFileKey = Null;
let strMemberId = Null;
let strMemberDataSrc = Null;
let strFormatMemberId = Null;
let strPNamePref = Null;
let strP_FirstName = Null;
let strP_MiddleName = Null;
let strP_LastName = Null;
let strP_NameSuf = Null;
let strP_RelCode = Null;
let dteP_Dob = Null;
let strS_Ssn = Null;
let strS_NamePref = Null;
let strS_FName = Null;
let strS_LName = Null;
let strS_NameSuf = Null;
let strS_Addr1 = Null;
let strS_Addr2 = Null;
let strS_City = Null;
let strS_Sate = Null;
let strS_Zip5 = Null;
let strS_Phone1 = Null;
let strS_Phone2 = Null;
let strS_Phone3 = Null;
let strGrpNo = Null;
let strAccount = Null;
let strGrpSuf = Null;
let strGrpName = Null;
let strMarket = Null;
let dteD_Injury = Null;
let strBusSeg = Null;
let strFundCat = Null;
let strLangInd = Null;
let strProvNo = Null;
let strProvName = Null;
let strProvAddr1 = Null;
let strProvCity = Null;
let strProvState = Null;
let strProvZip = Null;
let strClaimNo = Null;
let strFormatClaimNo = Null;
let dteD_ServBegin = Null;
let dteD_ServEnd = Null;
let dteD_Paid = Null;
let strFirstCATCODE = Null;
let strSecondCATCODE = Null;
let strThirdCATCODE = Null;
let strFourthCATCODE = Null;
let strFifthCATCODE = Null;
let strFrmtCATCODE_1 = Null;
let strCATCODEMatch_1 = Null;
let strFrmtCATCODE_2 = Null;
let strCATCODEMatch_2 = Null;
let strFrmtCATCODE_3 = Null;
let strCATCODEMatch_3 = Null;
let strFrmtCATCODE_4 = Null;
let strCATCODEMatch_4 = Null;
let strFrmtCATCODE_5 = Null;
let strCATCODEMatch_5 = Null;
let strCptCode = Null;
let strPaidAmount = Null;
let strBilledAmount= Null;
let strLineItemStat= Null;
let strReversalCode= Null;
let strSumCode = Null;
let strClmTypeCode = Null;
let strClmStatCode = Null;
let strClmLineId = Null;
let strOrigClmCode = Null;
let intReqID = Null;
let strErrorCode = Null;
Let intParentId = Null;
--- End Variable init test
FOREACH curMain FOR
SELECT distinct(request_id)
INTO intReqID
FROM client_history
ORDER BY request_id
LET strErrorCode = "-";
SELECT file_key
INTO intFileKey
FROM support_requests
WHERE sup_req_id = intReqID;
SELECT is LOB_trad_flg
INTO strHT_Flag
FROM client_hist_req
WHERE req_id = intReqID;
--Check if there is at least one row with the error code "G"
--then update all error codes for this request to "G"
SELECT count (error_code)
INTO intErrorCode
FROM client_history
WHERE error_code = "G"
AND request_id = intReqID;
IF intErrorCode <> 0 THEN
UPDATE client_history
SET error_code = "G"
WHERE request_id = intReqID;
END IF
SELECT distinct(error_code)
INTO strErrorCode
FROM client_history
WHERE request_id = intReqID;
LET strFundCat_prev = "";
LET strGrpNo_prev = "";
FOREACH curRequest FOR
SELECT
source_member_id, --1
member_data_source, --2
p_name_prefix, --3
p_fname, --4
p_middle_name, --5
p_lname, --6
p_name_suffix, --7
p_rel_code, --8
p_dob date, --9
s_ssn[3,11], --10
s_name_prefix, --11
s_first_name, --12
s_last_name, --13
s_name_suffix, --14
s_addr1, --15
s_addr2, --16
s_city, --17
s_state, --18
s_zip, --19
s_phone1, --20
s_phone2, --21
s_phone3, --22
prov_no, --23
prov_name, --24
prov_addr1, --25
prov_city, --26
prov_state, --27
prov_zip, --28
planId_grpNo, --29
account, --30
grp_suffix[6,8], --31
grp_name, --32
clm_no, --33
market, --34
bus_seg, --35
d_serv_end, --36
d_serv_begin, --37
paid_date, --38
CATCODE_1, --39
CATCODE_2, --40
CATCODE_3, --41
CATCODE_4, --42
CATCODE_5, --43
cpt_code, --44
bill_amount, --45
paid_amount, --46
fund_cat, --47
plan_id, --48
clm_status_code, --49
sum_code, --50
orig_clm_code, --51
reversal_code, --52
clm_line_id --53
-- error_code --54
INTO
strMemberId, --1
strMemberDataSrc, --2
strPNamePref, --3
strP_FirstName, --4
strP_MiddleName, --5
strP_LastName, --6
strP_NameSuf, --7
strP_RelCode, --8
dteP_Dob, --9
strS_Ssn, --10
strS_NamePref, --11
strS_FName, --12
strS_LName, --13
strS_NameSuf, --14
strS_Addr1, --15
strS_Addr2, --16
strS_City, --17
strS_Sate, --18
strS_Zip5, --19
strS_Phone1, --20
strS_Phone2, --21
strS_Phone3, --22
strProvNo, --23
strProvName, --24
strProvAddr1, --25
strProvCity, --26
strProvState, --27
strProvZip, --28
strGrpNo, --29
strAccount, --30
strGrpSuf, --31
strGrpName, --32
strClaimNo, --33
strMarket, --34
strBusSeg, --35
dteD_ServEnd, --36
dteD_ServBegin, --37
dteD_Paid, --38
strFirstCATCODE, --39
strSecondCATCODE, --40
strThirdCATCODE, --41
strFourthCATCODE, --42
strFifthCATCODE, --43
strCptCode, --44
strBilledAmount, --45
strPaidAmount, --46
strFundCat, --47
strLangInd, --48
strClmStatCode, --49
strSumCode, --50
strClmTypeCode, --51
strReversalCode, --52
strClmLineId --53
--strErrorCode --54
FROM client_history
WHERE request_id = intReqID
ORDER BY fund_cat, planid_grpno
IF length(strMemberId) > 8 THEN
IF strGrpNo in ("351630","351631","351632","351633" THEN
LET strFormatMemberId = strMemberId[2,11];
ELSE
LET strFormatMemberId = strMemberId[3,11];
END IF
LET strMarket = Null;
SELECT count(distinct off_key)
INTO intMarketCount
FROM client_history
WHERE request_id = intReqID
AND (trim(off_key) <> ""
OR off_key is null);
IF intMarketCount = 1 THEN
SELECT distinct(off_key)
INTO strMarket
FROM client_history
WHERE request_id = intReqID
AND (trim(off_key)<> ""
or off_key is null);
END IF
ELSE
LET strFormatMemberId = strMemberId;
END IF
--on the IFSP client system the length of the claim number
--is 15 characters long and first 3 indicates market,
--because our system can hold only 14 character for claim no,
--we cut off first three characters (requested by Product support cdm and pkh)
IF length(strClaimNo) = 15 THEN
LET strFormatClaimNo = strClaimNo[4,15];
ELSE
LET strFormatClaimNo = strClaimNo;
END IF
--
-- we are populating the strMarket from Market field from client_history
-- then we check if this is LOB or Traditional. In case if this is Traditional we are initializing strMarket and
-- selecting into it the data from off_key field of client_history table which keeps data
-- for service area for Traditional
--##########################################################
--BUILDING STRING FOR MULTI-MEMBER_IF
--checking if we have multiple member_id, and if so then we build the string of member_id and group_no
--to add to the diary note
SELECT count (distinct source_member_id)
INTO intMemberCount
FROM client_history
WHERE request_id = intReqId;
IF intMemberCount > 1 THEN
LET str = "";
FOREACH curMemberID_Group FOR
SELECT distinct source_member_id,planid_grpno
INTO m_member_id, m_group_no
FROM client_history
WHERE request_id = intReqID
IF length(m_member_id) > 8 THEN
IF strGrpNo in ("351630","351631","351632","351633" THEN
LET m_member_id = m_member_id[2,11];
ELSE
LET m_member_id = m_member_id[3,11];
END IF
END IF
IF m_group_no is NULL OR TRIM(m_group_no) = "" THEN
LET str = TRIM(str) || ", " || TRIM(m_member_id) || " - " || "No PD Claims";
ELSE
LET str = TRIM(str) || ", " || TRIM(m_member_id) || " - " || TRIM(m_group_no);
END IF
END FOREACH
IF LENGTH(str) > 0 THEN
LET str =SUBSTR(str, 3);
END IF
END IF
--#######################
--
--checking if error_message then inserting data into sup_req_error, diary, and tickle
--
IF strErrorCode <> "G" THEN
IF strErrorCode = "C" THEN
INSERT INTO sup_req_error
VALUES (intReqID,"No Claims Found",current);
ELIF strErrorCode = "M" THEN
INSERT INTO sup_req_error
VALUES (intReqID,"Member Not Found",current);
ELIF strErrorCode = "S" THEN
INSERT INTO sup_req_error
VALUES (intReqID,"Social Not Found",current);
ELSE --strErrorCode = "D" or anything else
INSERT INTO sup_req_error
VALUES (intReqID,"Unknown Reason.Research.",current);
END IF
INSERT INTO diary (diary_key,file_key,d_event,t_event,subject, user_name)
VALUES(0,intFileKey,current, current,'Automated History Request ' || intReqId || ' Failed; PS Researching',
'auto');
UPDATE req_hist_par
SET message_id = 1
WHERE sup_req_id = intReqId;
INSERT INTO tickle
(tickle_key,
file_key,
d_tickle,
subject,
tickler,
ticklee,
priority,
tickle_type)
VALUES(0,
intFileKey,
current,
intReqId,
'auto',
(SELECT rep_name
FROM fversion
WHERE file_key = intFileKey
AND file_ver = (SELECT max(file_ver)
FROM fversion
WHERE file_key = intFileKey)), 0,11);
--check if request(file_key) do not exists
-- in the table client_first_req then insert record into
--and insert error message "client Purged" into sup_req_error
SELECT count(file_key)
INTO intFirstReqCount
FROM client_first_req
WHERE sup_req_id = intReqID;
IF intFirstReqCount = 0 THEN
INSERT INTO client_first_req
VALUES(intReqID, intFileKey, today);
INSERT INTO sup_req_error
VALUES (intReqID, "First Time Request", current);
END IF
UPDATE support_requests
SET proc_user = "auto",priority = "FAIL"
WHERE sup_req_id = intReqID;
ELSE
SELECT d_injury
INTO dteD_Injury
FROM files
WHERE file_key = intFileKey;
--checking if any parent records for this request
IF (NVL(strFundCat,'') <> NVL(strFundCat_prev,''))
OR (NVL(strGrpNo,'') <> NVL(strGrpNo_prev,'')) THEN
SELECT max(req_hist_par_id)
INTO intParentId
FROM req_hist_par
WHERE sup_req_id = intReqID
AND fund_cat = strFundCat
AND grp_no = strGrpNo;
IF intParentId is NULL THEN
INSERT INTO req_hist_par
(req_hist_par_id, --01
file_key, --02
member_id, --03
member_data_src, --04
p_name_prefix, --05
p_fname, --06
p_middle_name, --07
p_lname, --08
p_name_suffix, --09
p_rel_code, --10
p_dob, --11
s_ssn, --12
s_name_prefix, --13
s_fname, --14
s_lname, --15
s_name_suffix, --16
s_addr1, --17
s_addr2, --18
s_city, --19
s_state, --20
s_zip5, --21
s_phone, --22
grp_no, --23
account, --24
grp_suffix, --25
grp_name, --26
market, --27
d_injury, --28
bus_seg, --29
fund_cat, --30
lang_ind, --31
batch_id, --32
error_code, --33
sup_req_id) --34
VALUES
(0, --01
intFileKey, --02
strFormatMemberId, --03
strMemberDataSrc,--04
strPNamePref, --05
strP_FirstName, --06
strP_MiddleName,--07
strP_LastName, --08
strP_NameSuf, --09
strP_RelCode, --10
dteP_Dob, --11
strS_Ssn, --12
strS_NamePref, --13
strS_FName, --14
strS_LName, --15
strS_NameSuf, --16
strS_Addr1, --17
strS_Addr2, --18
strS_City, --19
strS_Sate, --20
strS_Zip5, --21
strS_Phone1 || strS_Phone2 || strS_Phone3, --22
strGrpNo, --23
strAccount, --24
strGrpSuf, --25
strGrpName, --26
strMarket, --27
dteD_Injury, --28
strBusSeg, --29
strFundCat,--30
strLangInd, --31
intBatch_id, --32
strErrorCode, --33
intReqID); --34)
-- Get the serial value used by the
-- insert statement above
LET intParentId = (select dbinfo('sqlca.sqlerrd1')
from systables
where tabid = 1);
END IF -- this parent id not exist
END IF -- new fund_gat, grpno
LET strFundCat_prev = strFundCat;
LET strGrpNo_prev = strGrpNo;
-- Insert a new child record
LET strFrmtCATCODE_1 = sp_format_CATCODE(strFirstCATCODE);
IF strFrmtCATCODE_1 is not null then
select max(capture)
into strCATCODEMatch_1
from CATCODE
where code = strFrmtCATCODE_1;
ELSE
LET strCATCODEMatch_1 = null;
END IF
LET strFrmtCATCODE_2 = sp_format_CATCODE(strSecondCATCODE);
IF strFrmtCATCODE_2 is not null then
select max(capture)
into strCATCODEMatch_2
from CATCODE
where code = strFrmtCATCODE_2;
ELSE
LET strCATCODEMatch_2 = null;
END IF
LET strFrmtCATCODE_3 = sp_format_CATCODE(strThirdCATCODE);
IF strFrmtCATCODE_3 is not null then
select max(capture)
into strCATCODEMatch_3
from CATCODE
where code = strFrmtCATCODE_3;
ELSE
LET strCATCODEMatch_3 = null;
END IF
LET strFrmtCATCODE_4 = sp_format_CATCODE(strFourthCATCODE);
IF strFrmtCATCODE_4 is not null then
select max(capture)
into strCATCODEMatch_4
from CATCODE
where code = strFrmtCATCODE_4;
ELSE
LET strCATCODEMatch_4 = null;
END IF
LET strFrmtCATCODE_5 = sp_format_CATCODE(strFifthCATCODE);
IF strFrmtCATCODE_5 is not null then
select max(capture)
into strCATCODEMatch_5
from CATCODE
where code = strFrmtCATCODE_5;
ELSE
LET strCATCODEMatch_5 = null;
END IF
--
-- Check to see if this child already exsists
-- if so skip inserting the redundant data
--
SELECT count(*)
INTO intRowCount
FROM req_hist_child
WHERE ((strFormatClaimNo is null and claim_no is null)
or (strFormatClaimNo is not null and claim_no = strClaimNo))
AND ((dteD_ServBegin is null and d_serv_begin is null)
or (dteD_ServBegin is not null and d_serv_begin = dteD_ServBegin))
AND ((dteD_ServEnd is null and d_serv_end is null)
or (dteD_ServEnd is not null and d_serv_end = dteD_ServEnd))
AND ((dteD_Paid is null and d_paid is null)
or (dteD_Paid is not null and d_paid = dteD_Paid))
AND ((strFrmtCATCODE_1 is null and CATCODE_1 is null)
or (strFrmtCATCODE_1 is not null and CATCODE_1 = strFrmtCATCODE_1))
AND ((strFrmtCATCODE_2 is null and CATCODE_2 is null)
or (strFrmtCATCODE_2 is not null and CATCODE_2 = strFrmtCATCODE_2))
AND ((strFrmtCATCODE_3 is null and CATCODE_3 is null)
or (strFrmtCATCODE_3 is not null and CATCODE_3 = strFrmtCATCODE_3))
AND ((strFrmtCATCODE_4 is null and CATCODE_4 is null)
or (strFrmtCATCODE_4 is not null and CATCODE_4 = strFrmtCATCODE_4))
AND ((strFrmtCATCODE_5 is null and CATCODE_5 is null)
or (strFrmtCATCODE_5 is not null and CATCODE_5 = strFrmtCATCODE_5))
AND ((strCATCODEMatch_1 is null and CATCODE_1_match is null)
or (strCATCODEMatch_1 is not null and CATCODE_1_match = strCATCODEMatch_1))
AND ((strCATCODEMatch_2 is null and CATCODE_2_match is null)
or (strCATCODEMatch_2 is not null and CATCODE_2_match = strCATCODEMatch_2))
AND ((strCATCODEMatch_3 is null and CATCODE_3_match is null)
or (strCATCODEMatch_3 is not null and CATCODE_3_match = strCATCODEMatch_3))
AND ((strCATCODEMatch_4 is null and CATCODE_4_match is null)
or (strCATCODEMatch_4 is not null and CATCODE_4_match = strCATCODEMatch_4))
AND ((strCATCODEMatch_5 is null and CATCODE_5_match is null)
or (strCATCODEMatch_5 is not null and CATCODE_5_match = strCATCODEMatch_5))
AND ((strBilledAmount is null and bill_amount is null)
or (strBilledAmount is not null and bill_amount = strBilledAmount))
AND ((strPaidAmount is null and paid_amount is null )
or (strPaidAmount is not null and paid_amount = strPaidAmount))
AND ((strClmStatCode is null and paid_status is null )
or (strClmStatCode is not null and paid_status = strClmStatCode))
AND ((intFileKey is null and file_key is null)
or (intFileKey is not null and file_key = intFileKey ))
AND((strCptCode is null and cpt_code is null)
or (strCptCode is not null and cpt_code = strCptCode))
AND ((strReversalCode is null and reversal_code is null)
or (strReversalCode is not null and reversal_code = strReversalCode))
AND ((strClmLineId is null and orig_clm_code is null)
or (strClmLineId is not null and orig_clm_code = strClmLineId));
SELECT req_hist_par_id
INTO intParentId
FROM req_hist_par
WHERE sup_req_id = intReqID
AND fund_cat = strFundCat
AND grp_no = strGrpNo;
IF intRowCount = 0 THEN
INSERT INTO client_x_hist
VALUES (intParentId,
intReqID,
intFileKey,
strProvNo,
strProvName,
strProvAddr1,
strProvCity,
strProvState,
strProvZip,
strFormatClaimNo,
strClmLineId,
dteD_ServBegin,
dteD_ServEnd,
dteD_Paid,
strFrmtCATCODE_1,
strCATCODEMatch_1,
strFrmtCATCODE_2,
strCATCODEMatch_2,
strFrmtCATCODE_3,
strCATCODEMatch_3,
strFrmtCATCODE_4,
strCATCODEMatch_4,
strFrmtCATCODE_5,
strCATCODEMatch_5,
strBilledAmount,
strPaidAmount,
strCptCode,
strClmStatCode,
strSumCode,
strReversalCode,
strClmTypeCode,
today);
END IF
END IF
END FOREACH --end curRequest
IF (strErrorCode = "G" OR strErrorCode IS NULL) THEN
INSERT INTO req_hist_child
(req_hist_child_id, --01
req_hist_par_id, --02
prov_no, --03
prov_name, --04
prov_addr1, --05
prov_city, --06
prov_state, --07
prov_zip5, --08
claim_no, --09
d_serv_begin, --10
d_serv_end, --11
d_paid, --12
CATCODE_1, --13
CATCODE_1_match, --14
CATCODE_2, --15
CATCODE_2_match, --16
CATCODE_3, --17
CATCODE_3_match, --18
CATCODE_4, --19
CATCODE_4_match, --20
CATCODE_5, --21
CATCODE_5_match, --22
bill_amount, --23
paid_amount, --24
paid_status, --25
clm_status_code, --26
line_item_status, --27
reversal_code, --28
cpt_code, --29
file_key, --30
sum_code, --31
clm_type_code, --32
orig_clm_code, --33
clm_file_id, --34
client data system_status) --35
SELECT "0", --01
req_par_id, --02
prov_no, --03
prov_name, --04
prov_addr1, --05
prov_city, --06
prov_state, --07
prov_zip, --08
clm_no, --09
d_serv_begin, --10
d_serv_end, --11
d_paid, --12
CATCODE_1, --13
CATCODE_1_match, --14
CATCODE_2, --15
CATCODE_2_match, --16
CATCODE_3, --17
CATCODE_3_match, --18
CATCODE_4, --19
CATCODE_4_match, --20
CATCODE_5, --21
CATCODE_5_match, --22
bill_amount, --23
paid_amount, --24
clm_status_code, --25
clm_status_code, --26
reversal_code, --27
reversal_code, --28
cpt_code, --29
file_key, --30
sum_code, --31
orig_clm_code, --32
clm_line_id, --33
orig_clm_code, --34
'N' --35
FROM client_x_hist
WHERE request_id = intReqId
AND req_par_id is not null;
IF intMemberCount >1 THEN
INSERT INTO diary (diary_key,file_key,d_event,t_event,subject,user_name)
VALUES(0,intFileKey,current,current,'Automated History Request ' || intReqId || ' Completed, Multiple member id: ' || TRIM(str) ,'auto');
END IF
-- Insert the tickle
SELECT count(*)
INTO intRowCount
FROM tickle
WHERE file_key = intFileKey
AND subject = intReqId
AND tickle_type = 10;
IF intRowCount = 0 then
INSERT into tickle (tickle_key,file_key,d_tickle,subject,
tickler,ticklee,priority,tickle_type)
VALUES(0,intFileKey,current,intReqId,'auto',
(SELECT rep_name
FROM fversion
WHERE file_key = intFileKey
AND file_ver = (SELECT max(file_ver)
FROM fversion
WHERE file_key = intFileKey)),
0,
10);
END IF;
-- Mark the original support request as completed only if no purged error message
SELECT count(file_key)
INTO intFirstReqCount
FROM client_first_req
WHERE file_key = intFileKey;
IF intFirstReqCount = 0 THEN
INSERT INTO client_first_req
VALUES(intReqID, intFileKey, today);
INSERT INTO sup_req_error
VALUES (intReqID, "First Time Request", current);
UPDATE support_requests
SET priority = "1ST"
WHERE sup_req_id = intReqID;
INSERT INTO diary
(diary_key,
file_key,
d_event,
t_event,
subject,
user_name)
VALUES(0,intFileKey,
current,
current,
'Automated History Request ' || intReqId || ' Completed; Manual Claims Possible, Current thru ' || dteLastUpDate || '; PS Researching', 'auto');
UPDATE req_hist_par
SET message_id = 1
WHERE sup_req_id = intReqId;
ELSE
INSERT INTO diary (diary_key,
file_key,
d_event,
t_event,
subject,
user_name)
VALUES(0,intFileKey,
current,current,
'Automated History Request ' || intReqId ||' Completed, Current thru ' || dteLastUpDate ,'auto');
UPDATE support_requests
SET d_complete = today
WHERE sup_req_id = intReqId;
END IF
END IF --if strErrorCode = "G"
END FOREACH --end curMain
END
--total err "No Claims Found"
SELECT count(*)
INTO intTotalClmErr
FROM client_history
WHERE error_code = "C";
IF intTotalClmErr <> 0 THEN
INSERT INTO batch_cnt(batch_cnt_id,batch_id,cnt_id,value)
VALUES(0,intBatch_id,"5",intTotalClmErr);
END IF
UPDATE batch_stats
SET d_loaded = current
WHERE batch_no = strBatchNo;
--
--purge off data from client_hist_req after when data has already transfered into client data system tables
--data from client_history (staging) table will be purged every time when we start the new load of the
--data into staging table(weekly).
--
--DELETE from client_x_hist;
--DELETE from client_hist_req
--WHERE batch_no = (select batch_no
-- from batch_stats
-- where d_loaded = current);
END PROCEDURE;
The stored procedure below absolutely crushes my system and my own lack of expertise keeps me from explaining exactly why. It was running today against about 80,000 records and once the lights started dimming I figured I had to kill it.
In my limited experience, I have found informix to be smoking fast compared to our MS-SQL db's, so I am wondering if this SP is to blame. Can anyone take a quick eyeball at it for any obvious issues the programmers approach has created?
Its hard to read in a browser page and might retain a stepped format better if you paste it into a text tool.
I throw myself on your mercy
oh wise and gracious gurus.Runnning IDS 9.4, raw disk with a gig of mem if it matters and informix is really the only thing on this box and aty the time this process was the only thing running.
-----------------------------------------------------------
drop procedure "informix".sp_client_hist_ins;
create procedure "informix".sp_client_hist_ins(strBatchNo char(8))
DEFINE intRowCount integer;
DEFINE intGoodReqRecieved integer;
DEFINE intTotalReqReceived integer;
DEFINE intTotalMemberErr integer;
DEFINE intTotalSocErr integer;
DEFINE intTotalClmErr integer;
DEFINE intTotalRecords integer;
DEFINE dteLastUpDate date;
DEFINE intBatch_id integer;
DEFINE intErrorCode integer;
DEFINE intMemberCount integer;
DEFINE intFirstReqCount integer;
DEFINE intMarketCount integer;
--
-- Setup debug info
--
IF (SELECT dbinfo('dbhostname')
FROM systables
WHERE tabid = 1 ) = 'test' THEN
set debug file to '/u/informix/trace/sp_client_hist_ins.dbg';
trace on;
END IF
-- Check and see if any client history requests exist for
-- the specified strBatchNo and if no error create batch statistics
SELECT count(*)
INTO intRowCount
FROM client_history
WHERE batch_no = strBatchNo;
SELECT batch_id
INTO intBatch_id
FROM batch_stats
WHERE batch_no = strBatchNo;
INSERT INTO batch_cnt(batch_cnt_id,batch_id,cnt_id,value)
VALUES(0,intBatch_id,"6",intRowCount);
IF intRowCount = 0 then
raise exception -746,0,
'sp_client_hist_ins: No data found for batch ' || intBatch_id;
END IF
UPDATE client_history
SET error_code = "G"
WHERE error_code is null
OR trim(error_code) = "";
--total requests recieved
--
SELECT count(distinct request_id)
INTO intTotalReqReceived
FROM client_history;
INSERT INTO batch_cnt(batch_cnt_id,batch_id,cnt_id,value)
VALUES(0,intBatch_id,"2",intTotalReqReceived);
--total claims recieved
SELECT count(distinct request_id)
INTO intGoodReqRecieved
FROM client_history
WHERE error_code = "G";
IF intGoodReqRecieved <> 0 THEN
INSERT INTO batch_cnt(batch_cnt_id,batch_id,cnt_id,value)
VALUES(0,intBatch_id,"3",intGoodReqRecieved);
END IF
--total err "Member Not Found"
SELECT count(*)
INTO intTotalMemberErr
FROM client_history
WHERE error_code = "M";
IF intTotalMemberErr <> 0 THEN
INSERT INTO batch_cnt(batch_cnt_id,batch_id,cnt_id,value)
VALUES(0,intBatch_id,"4",intTotalMemberErr);
END IF
SELECT count(*)
INTO intTotalSocErr
FROM client_history
WHERE error_code = "S";
IF intTotalSocErr <> 0 THEN
INSERT INTO batch_cnt(batch_cnt_id,batch_id,cnt_id,value)
VALUES(0,intBatch_id,"8",intTotalSocErr);
END IF
--determine Last Update Date of Data Warehouse
SELECT distinct(last_update_date)
INTO dteLastUpDate
FROM client_history
WHERE last_update_date is not null;
begin
define intReqID integer;
define strMemberId varchar(23);
define strMemberDataSrc varchar(2);
define strPNamePref varchar(4);
define strP_FirstName varchar(30);
define strP_MiddleName varchar(10);
define strP_LastName varchar(30);
define strP_NameSuf varchar(5);
define strP_RelCode varchar(14);
define dteP_Dob date;
define strS_Ssn varchar(10);
define strS_NamePref varchar(5);
define strS_FName varchar(30);
define strS_LName varchar(30);
define strS_NameSuf varchar(5);
define strS_Addr1 varchar(35);
define strS_Addr2 varchar(35);
define strS_City varchar(30);
define strS_Sate varchar(2);
define strS_Zip5 varchar(5);
define strS_Phone1 varchar(3);
define strS_Phone2 varchar(3);
define strS_Phone3 varchar(4);
define strGrpNo varchar(8);
define strAccount varchar(5);
define strGrpSuf varchar(8);
define strGrpName varchar(40);
define strMarket varchar(5);
define dteD_Injury date;
define strBusSeg varchar(5);
define strFundCat varchar(3);
define strLangInd varchar(6);
define strProvNo varchar(14);
define strProvName varchar(30);
define strProvAddr1 varchar(35);
define strProvCity varchar(30);
define strProvState varchar(2);
define strProvZip varchar(5);
define strClaimNo varchar(19);
define strFormatClaimNo varchar(19);
define dteD_ServBegin date;
define dteD_ServEnd date;
define dteD_Paid date;
define strFirstCATCODE varchar(6);
define strSecondCATCODE varchar(6);
define strThirdCATCODE varchar(6);
define strFourthCATCODE varchar(6);
define strFifthCATCODE varchar(6);
define strFrmtCATCODE_1 varchar(6);
define strCATCODEMatch_1 varchar(1);
define strFrmtCATCODE_2 varchar(6);
define strCATCODEMatch_2 varchar(1);
define strFrmtCATCODE_3 varchar(6);
define strCATCODEMatch_3 varchar(1);
define strFrmtCATCODE_4 varchar(6);
define strCATCODEMatch_4 varchar(1);
define strFrmtCATCODE_5 varchar(6);
define strCATCODEMatch_5 varchar(1);
define strCptCode varchar(5);
define strPaidAmount varchar(12);
define strBilledAmount varchar(12);
define strPaidStatus varchar(5);
define strLineItemStat varchar(5);
define strReversalCode varchar(1);
define strSumCode varchar(1);
define strClmTypeCode varchar(1);
define strClmStatCode varchar(1);
define strOrigClmCode varchar(1);
define strErrorCode varchar(2);
define strHT_Flag char(1);
define intFileKey integer;
define intParentId integer;
define strClmLineId varchar(20);
-- define blnFirstRecord integer;
define intChildId integer;
define m_member_id varchar(23);
define m_group_no varchar(6);
define str char(1024);
define strFundCat_prev varchar(3);
define strGrpNo_prev varchar(6);
define strFormatMemberId varchar(11);
----- Begin Variable init test ---
let intFileKey = Null;
let strMemberId = Null;
let strMemberDataSrc = Null;
let strFormatMemberId = Null;
let strPNamePref = Null;
let strP_FirstName = Null;
let strP_MiddleName = Null;
let strP_LastName = Null;
let strP_NameSuf = Null;
let strP_RelCode = Null;
let dteP_Dob = Null;
let strS_Ssn = Null;
let strS_NamePref = Null;
let strS_FName = Null;
let strS_LName = Null;
let strS_NameSuf = Null;
let strS_Addr1 = Null;
let strS_Addr2 = Null;
let strS_City = Null;
let strS_Sate = Null;
let strS_Zip5 = Null;
let strS_Phone1 = Null;
let strS_Phone2 = Null;
let strS_Phone3 = Null;
let strGrpNo = Null;
let strAccount = Null;
let strGrpSuf = Null;
let strGrpName = Null;
let strMarket = Null;
let dteD_Injury = Null;
let strBusSeg = Null;
let strFundCat = Null;
let strLangInd = Null;
let strProvNo = Null;
let strProvName = Null;
let strProvAddr1 = Null;
let strProvCity = Null;
let strProvState = Null;
let strProvZip = Null;
let strClaimNo = Null;
let strFormatClaimNo = Null;
let dteD_ServBegin = Null;
let dteD_ServEnd = Null;
let dteD_Paid = Null;
let strFirstCATCODE = Null;
let strSecondCATCODE = Null;
let strThirdCATCODE = Null;
let strFourthCATCODE = Null;
let strFifthCATCODE = Null;
let strFrmtCATCODE_1 = Null;
let strCATCODEMatch_1 = Null;
let strFrmtCATCODE_2 = Null;
let strCATCODEMatch_2 = Null;
let strFrmtCATCODE_3 = Null;
let strCATCODEMatch_3 = Null;
let strFrmtCATCODE_4 = Null;
let strCATCODEMatch_4 = Null;
let strFrmtCATCODE_5 = Null;
let strCATCODEMatch_5 = Null;
let strCptCode = Null;
let strPaidAmount = Null;
let strBilledAmount= Null;
let strLineItemStat= Null;
let strReversalCode= Null;
let strSumCode = Null;
let strClmTypeCode = Null;
let strClmStatCode = Null;
let strClmLineId = Null;
let strOrigClmCode = Null;
let intReqID = Null;
let strErrorCode = Null;
Let intParentId = Null;
--- End Variable init test
FOREACH curMain FOR
SELECT distinct(request_id)
INTO intReqID
FROM client_history
ORDER BY request_id
LET strErrorCode = "-";
SELECT file_key
INTO intFileKey
FROM support_requests
WHERE sup_req_id = intReqID;
SELECT is LOB_trad_flg
INTO strHT_Flag
FROM client_hist_req
WHERE req_id = intReqID;
--Check if there is at least one row with the error code "G"
--then update all error codes for this request to "G"
SELECT count (error_code)
INTO intErrorCode
FROM client_history
WHERE error_code = "G"
AND request_id = intReqID;
IF intErrorCode <> 0 THEN
UPDATE client_history
SET error_code = "G"
WHERE request_id = intReqID;
END IF
SELECT distinct(error_code)
INTO strErrorCode
FROM client_history
WHERE request_id = intReqID;
LET strFundCat_prev = "";
LET strGrpNo_prev = "";
FOREACH curRequest FOR
SELECT
source_member_id, --1
member_data_source, --2
p_name_prefix, --3
p_fname, --4
p_middle_name, --5
p_lname, --6
p_name_suffix, --7
p_rel_code, --8
p_dob date, --9
s_ssn[3,11], --10
s_name_prefix, --11
s_first_name, --12
s_last_name, --13
s_name_suffix, --14
s_addr1, --15
s_addr2, --16
s_city, --17
s_state, --18
s_zip, --19
s_phone1, --20
s_phone2, --21
s_phone3, --22
prov_no, --23
prov_name, --24
prov_addr1, --25
prov_city, --26
prov_state, --27
prov_zip, --28
planId_grpNo, --29
account, --30
grp_suffix[6,8], --31
grp_name, --32
clm_no, --33
market, --34
bus_seg, --35
d_serv_end, --36
d_serv_begin, --37
paid_date, --38
CATCODE_1, --39
CATCODE_2, --40
CATCODE_3, --41
CATCODE_4, --42
CATCODE_5, --43
cpt_code, --44
bill_amount, --45
paid_amount, --46
fund_cat, --47
plan_id, --48
clm_status_code, --49
sum_code, --50
orig_clm_code, --51
reversal_code, --52
clm_line_id --53
-- error_code --54
INTO
strMemberId, --1
strMemberDataSrc, --2
strPNamePref, --3
strP_FirstName, --4
strP_MiddleName, --5
strP_LastName, --6
strP_NameSuf, --7
strP_RelCode, --8
dteP_Dob, --9
strS_Ssn, --10
strS_NamePref, --11
strS_FName, --12
strS_LName, --13
strS_NameSuf, --14
strS_Addr1, --15
strS_Addr2, --16
strS_City, --17
strS_Sate, --18
strS_Zip5, --19
strS_Phone1, --20
strS_Phone2, --21
strS_Phone3, --22
strProvNo, --23
strProvName, --24
strProvAddr1, --25
strProvCity, --26
strProvState, --27
strProvZip, --28
strGrpNo, --29
strAccount, --30
strGrpSuf, --31
strGrpName, --32
strClaimNo, --33
strMarket, --34
strBusSeg, --35
dteD_ServEnd, --36
dteD_ServBegin, --37
dteD_Paid, --38
strFirstCATCODE, --39
strSecondCATCODE, --40
strThirdCATCODE, --41
strFourthCATCODE, --42
strFifthCATCODE, --43
strCptCode, --44
strBilledAmount, --45
strPaidAmount, --46
strFundCat, --47
strLangInd, --48
strClmStatCode, --49
strSumCode, --50
strClmTypeCode, --51
strReversalCode, --52
strClmLineId --53
--strErrorCode --54
FROM client_history
WHERE request_id = intReqID
ORDER BY fund_cat, planid_grpno
IF length(strMemberId) > 8 THEN
IF strGrpNo in ("351630","351631","351632","351633"
THENLET strFormatMemberId = strMemberId[2,11];
ELSE
LET strFormatMemberId = strMemberId[3,11];
END IF
LET strMarket = Null;
SELECT count(distinct off_key)
INTO intMarketCount
FROM client_history
WHERE request_id = intReqID
AND (trim(off_key) <> ""
OR off_key is null);
IF intMarketCount = 1 THEN
SELECT distinct(off_key)
INTO strMarket
FROM client_history
WHERE request_id = intReqID
AND (trim(off_key)<> ""
or off_key is null);
END IF
ELSE
LET strFormatMemberId = strMemberId;
END IF
--on the IFSP client system the length of the claim number
--is 15 characters long and first 3 indicates market,
--because our system can hold only 14 character for claim no,
--we cut off first three characters (requested by Product support cdm and pkh)
IF length(strClaimNo) = 15 THEN
LET strFormatClaimNo = strClaimNo[4,15];
ELSE
LET strFormatClaimNo = strClaimNo;
END IF
--
-- we are populating the strMarket from Market field from client_history
-- then we check if this is LOB or Traditional. In case if this is Traditional we are initializing strMarket and
-- selecting into it the data from off_key field of client_history table which keeps data
-- for service area for Traditional
--##########################################################
--BUILDING STRING FOR MULTI-MEMBER_IF
--checking if we have multiple member_id, and if so then we build the string of member_id and group_no
--to add to the diary note
SELECT count (distinct source_member_id)
INTO intMemberCount
FROM client_history
WHERE request_id = intReqId;
IF intMemberCount > 1 THEN
LET str = "";
FOREACH curMemberID_Group FOR
SELECT distinct source_member_id,planid_grpno
INTO m_member_id, m_group_no
FROM client_history
WHERE request_id = intReqID
IF length(m_member_id) > 8 THEN
IF strGrpNo in ("351630","351631","351632","351633"
THENLET m_member_id = m_member_id[2,11];
ELSE
LET m_member_id = m_member_id[3,11];
END IF
END IF
IF m_group_no is NULL OR TRIM(m_group_no) = "" THEN
LET str = TRIM(str) || ", " || TRIM(m_member_id) || " - " || "No PD Claims";
ELSE
LET str = TRIM(str) || ", " || TRIM(m_member_id) || " - " || TRIM(m_group_no);
END IF
END FOREACH
IF LENGTH(str) > 0 THEN
LET str =SUBSTR(str, 3);
END IF
END IF
--#######################
--
--checking if error_message then inserting data into sup_req_error, diary, and tickle
--
IF strErrorCode <> "G" THEN
IF strErrorCode = "C" THEN
INSERT INTO sup_req_error
VALUES (intReqID,"No Claims Found",current);
ELIF strErrorCode = "M" THEN
INSERT INTO sup_req_error
VALUES (intReqID,"Member Not Found",current);
ELIF strErrorCode = "S" THEN
INSERT INTO sup_req_error
VALUES (intReqID,"Social Not Found",current);
ELSE --strErrorCode = "D" or anything else
INSERT INTO sup_req_error
VALUES (intReqID,"Unknown Reason.Research.",current);
END IF
INSERT INTO diary (diary_key,file_key,d_event,t_event,subject, user_name)
VALUES(0,intFileKey,current, current,'Automated History Request ' || intReqId || ' Failed; PS Researching',
'auto');
UPDATE req_hist_par
SET message_id = 1
WHERE sup_req_id = intReqId;
INSERT INTO tickle
(tickle_key,
file_key,
d_tickle,
subject,
tickler,
ticklee,
priority,
tickle_type)
VALUES(0,
intFileKey,
current,
intReqId,
'auto',
(SELECT rep_name
FROM fversion
WHERE file_key = intFileKey
AND file_ver = (SELECT max(file_ver)
FROM fversion
WHERE file_key = intFileKey)), 0,11);
--check if request(file_key) do not exists
-- in the table client_first_req then insert record into
--and insert error message "client Purged" into sup_req_error
SELECT count(file_key)
INTO intFirstReqCount
FROM client_first_req
WHERE sup_req_id = intReqID;
IF intFirstReqCount = 0 THEN
INSERT INTO client_first_req
VALUES(intReqID, intFileKey, today);
INSERT INTO sup_req_error
VALUES (intReqID, "First Time Request", current);
END IF
UPDATE support_requests
SET proc_user = "auto",priority = "FAIL"
WHERE sup_req_id = intReqID;
ELSE
SELECT d_injury
INTO dteD_Injury
FROM files
WHERE file_key = intFileKey;
--checking if any parent records for this request
IF (NVL(strFundCat,'') <> NVL(strFundCat_prev,''))
OR (NVL(strGrpNo,'') <> NVL(strGrpNo_prev,'')) THEN
SELECT max(req_hist_par_id)
INTO intParentId
FROM req_hist_par
WHERE sup_req_id = intReqID
AND fund_cat = strFundCat
AND grp_no = strGrpNo;
IF intParentId is NULL THEN
INSERT INTO req_hist_par
(req_hist_par_id, --01
file_key, --02
member_id, --03
member_data_src, --04
p_name_prefix, --05
p_fname, --06
p_middle_name, --07
p_lname, --08
p_name_suffix, --09
p_rel_code, --10
p_dob, --11
s_ssn, --12
s_name_prefix, --13
s_fname, --14
s_lname, --15
s_name_suffix, --16
s_addr1, --17
s_addr2, --18
s_city, --19
s_state, --20
s_zip5, --21
s_phone, --22
grp_no, --23
account, --24
grp_suffix, --25
grp_name, --26
market, --27
d_injury, --28
bus_seg, --29
fund_cat, --30
lang_ind, --31
batch_id, --32
error_code, --33
sup_req_id) --34
VALUES
(0, --01
intFileKey, --02
strFormatMemberId, --03
strMemberDataSrc,--04
strPNamePref, --05
strP_FirstName, --06
strP_MiddleName,--07
strP_LastName, --08
strP_NameSuf, --09
strP_RelCode, --10
dteP_Dob, --11
strS_Ssn, --12
strS_NamePref, --13
strS_FName, --14
strS_LName, --15
strS_NameSuf, --16
strS_Addr1, --17
strS_Addr2, --18
strS_City, --19
strS_Sate, --20
strS_Zip5, --21
strS_Phone1 || strS_Phone2 || strS_Phone3, --22
strGrpNo, --23
strAccount, --24
strGrpSuf, --25
strGrpName, --26
strMarket, --27
dteD_Injury, --28
strBusSeg, --29
strFundCat,--30
strLangInd, --31
intBatch_id, --32
strErrorCode, --33
intReqID); --34)
-- Get the serial value used by the
-- insert statement above
LET intParentId = (select dbinfo('sqlca.sqlerrd1')
from systables
where tabid = 1);
END IF -- this parent id not exist
END IF -- new fund_gat, grpno
LET strFundCat_prev = strFundCat;
LET strGrpNo_prev = strGrpNo;
-- Insert a new child record
LET strFrmtCATCODE_1 = sp_format_CATCODE(strFirstCATCODE);
IF strFrmtCATCODE_1 is not null then
select max(capture)
into strCATCODEMatch_1
from CATCODE
where code = strFrmtCATCODE_1;
ELSE
LET strCATCODEMatch_1 = null;
END IF
LET strFrmtCATCODE_2 = sp_format_CATCODE(strSecondCATCODE);
IF strFrmtCATCODE_2 is not null then
select max(capture)
into strCATCODEMatch_2
from CATCODE
where code = strFrmtCATCODE_2;
ELSE
LET strCATCODEMatch_2 = null;
END IF
LET strFrmtCATCODE_3 = sp_format_CATCODE(strThirdCATCODE);
IF strFrmtCATCODE_3 is not null then
select max(capture)
into strCATCODEMatch_3
from CATCODE
where code = strFrmtCATCODE_3;
ELSE
LET strCATCODEMatch_3 = null;
END IF
LET strFrmtCATCODE_4 = sp_format_CATCODE(strFourthCATCODE);
IF strFrmtCATCODE_4 is not null then
select max(capture)
into strCATCODEMatch_4
from CATCODE
where code = strFrmtCATCODE_4;
ELSE
LET strCATCODEMatch_4 = null;
END IF
LET strFrmtCATCODE_5 = sp_format_CATCODE(strFifthCATCODE);
IF strFrmtCATCODE_5 is not null then
select max(capture)
into strCATCODEMatch_5
from CATCODE
where code = strFrmtCATCODE_5;
ELSE
LET strCATCODEMatch_5 = null;
END IF
--
-- Check to see if this child already exsists
-- if so skip inserting the redundant data
--
SELECT count(*)
INTO intRowCount
FROM req_hist_child
WHERE ((strFormatClaimNo is null and claim_no is null)
or (strFormatClaimNo is not null and claim_no = strClaimNo))
AND ((dteD_ServBegin is null and d_serv_begin is null)
or (dteD_ServBegin is not null and d_serv_begin = dteD_ServBegin))
AND ((dteD_ServEnd is null and d_serv_end is null)
or (dteD_ServEnd is not null and d_serv_end = dteD_ServEnd))
AND ((dteD_Paid is null and d_paid is null)
or (dteD_Paid is not null and d_paid = dteD_Paid))
AND ((strFrmtCATCODE_1 is null and CATCODE_1 is null)
or (strFrmtCATCODE_1 is not null and CATCODE_1 = strFrmtCATCODE_1))
AND ((strFrmtCATCODE_2 is null and CATCODE_2 is null)
or (strFrmtCATCODE_2 is not null and CATCODE_2 = strFrmtCATCODE_2))
AND ((strFrmtCATCODE_3 is null and CATCODE_3 is null)
or (strFrmtCATCODE_3 is not null and CATCODE_3 = strFrmtCATCODE_3))
AND ((strFrmtCATCODE_4 is null and CATCODE_4 is null)
or (strFrmtCATCODE_4 is not null and CATCODE_4 = strFrmtCATCODE_4))
AND ((strFrmtCATCODE_5 is null and CATCODE_5 is null)
or (strFrmtCATCODE_5 is not null and CATCODE_5 = strFrmtCATCODE_5))
AND ((strCATCODEMatch_1 is null and CATCODE_1_match is null)
or (strCATCODEMatch_1 is not null and CATCODE_1_match = strCATCODEMatch_1))
AND ((strCATCODEMatch_2 is null and CATCODE_2_match is null)
or (strCATCODEMatch_2 is not null and CATCODE_2_match = strCATCODEMatch_2))
AND ((strCATCODEMatch_3 is null and CATCODE_3_match is null)
or (strCATCODEMatch_3 is not null and CATCODE_3_match = strCATCODEMatch_3))
AND ((strCATCODEMatch_4 is null and CATCODE_4_match is null)
or (strCATCODEMatch_4 is not null and CATCODE_4_match = strCATCODEMatch_4))
AND ((strCATCODEMatch_5 is null and CATCODE_5_match is null)
or (strCATCODEMatch_5 is not null and CATCODE_5_match = strCATCODEMatch_5))
AND ((strBilledAmount is null and bill_amount is null)
or (strBilledAmount is not null and bill_amount = strBilledAmount))
AND ((strPaidAmount is null and paid_amount is null )
or (strPaidAmount is not null and paid_amount = strPaidAmount))
AND ((strClmStatCode is null and paid_status is null )
or (strClmStatCode is not null and paid_status = strClmStatCode))
AND ((intFileKey is null and file_key is null)
or (intFileKey is not null and file_key = intFileKey ))
AND((strCptCode is null and cpt_code is null)
or (strCptCode is not null and cpt_code = strCptCode))
AND ((strReversalCode is null and reversal_code is null)
or (strReversalCode is not null and reversal_code = strReversalCode))
AND ((strClmLineId is null and orig_clm_code is null)
or (strClmLineId is not null and orig_clm_code = strClmLineId));
SELECT req_hist_par_id
INTO intParentId
FROM req_hist_par
WHERE sup_req_id = intReqID
AND fund_cat = strFundCat
AND grp_no = strGrpNo;
IF intRowCount = 0 THEN
INSERT INTO client_x_hist
VALUES (intParentId,
intReqID,
intFileKey,
strProvNo,
strProvName,
strProvAddr1,
strProvCity,
strProvState,
strProvZip,
strFormatClaimNo,
strClmLineId,
dteD_ServBegin,
dteD_ServEnd,
dteD_Paid,
strFrmtCATCODE_1,
strCATCODEMatch_1,
strFrmtCATCODE_2,
strCATCODEMatch_2,
strFrmtCATCODE_3,
strCATCODEMatch_3,
strFrmtCATCODE_4,
strCATCODEMatch_4,
strFrmtCATCODE_5,
strCATCODEMatch_5,
strBilledAmount,
strPaidAmount,
strCptCode,
strClmStatCode,
strSumCode,
strReversalCode,
strClmTypeCode,
today);
END IF
END IF
END FOREACH --end curRequest
IF (strErrorCode = "G" OR strErrorCode IS NULL) THEN
INSERT INTO req_hist_child
(req_hist_child_id, --01
req_hist_par_id, --02
prov_no, --03
prov_name, --04
prov_addr1, --05
prov_city, --06
prov_state, --07
prov_zip5, --08
claim_no, --09
d_serv_begin, --10
d_serv_end, --11
d_paid, --12
CATCODE_1, --13
CATCODE_1_match, --14
CATCODE_2, --15
CATCODE_2_match, --16
CATCODE_3, --17
CATCODE_3_match, --18
CATCODE_4, --19
CATCODE_4_match, --20
CATCODE_5, --21
CATCODE_5_match, --22
bill_amount, --23
paid_amount, --24
paid_status, --25
clm_status_code, --26
line_item_status, --27
reversal_code, --28
cpt_code, --29
file_key, --30
sum_code, --31
clm_type_code, --32
orig_clm_code, --33
clm_file_id, --34
client data system_status) --35
SELECT "0", --01
req_par_id, --02
prov_no, --03
prov_name, --04
prov_addr1, --05
prov_city, --06
prov_state, --07
prov_zip, --08
clm_no, --09
d_serv_begin, --10
d_serv_end, --11
d_paid, --12
CATCODE_1, --13
CATCODE_1_match, --14
CATCODE_2, --15
CATCODE_2_match, --16
CATCODE_3, --17
CATCODE_3_match, --18
CATCODE_4, --19
CATCODE_4_match, --20
CATCODE_5, --21
CATCODE_5_match, --22
bill_amount, --23
paid_amount, --24
clm_status_code, --25
clm_status_code, --26
reversal_code, --27
reversal_code, --28
cpt_code, --29
file_key, --30
sum_code, --31
orig_clm_code, --32
clm_line_id, --33
orig_clm_code, --34
'N' --35
FROM client_x_hist
WHERE request_id = intReqId
AND req_par_id is not null;
IF intMemberCount >1 THEN
INSERT INTO diary (diary_key,file_key,d_event,t_event,subject,user_name)
VALUES(0,intFileKey,current,current,'Automated History Request ' || intReqId || ' Completed, Multiple member id: ' || TRIM(str) ,'auto');
END IF
-- Insert the tickle
SELECT count(*)
INTO intRowCount
FROM tickle
WHERE file_key = intFileKey
AND subject = intReqId
AND tickle_type = 10;
IF intRowCount = 0 then
INSERT into tickle (tickle_key,file_key,d_tickle,subject,
tickler,ticklee,priority,tickle_type)
VALUES(0,intFileKey,current,intReqId,'auto',
(SELECT rep_name
FROM fversion
WHERE file_key = intFileKey
AND file_ver = (SELECT max(file_ver)
FROM fversion
WHERE file_key = intFileKey)),
0,
10);
END IF;
-- Mark the original support request as completed only if no purged error message
SELECT count(file_key)
INTO intFirstReqCount
FROM client_first_req
WHERE file_key = intFileKey;
IF intFirstReqCount = 0 THEN
INSERT INTO client_first_req
VALUES(intReqID, intFileKey, today);
INSERT INTO sup_req_error
VALUES (intReqID, "First Time Request", current);
UPDATE support_requests
SET priority = "1ST"
WHERE sup_req_id = intReqID;
INSERT INTO diary
(diary_key,
file_key,
d_event,
t_event,
subject,
user_name)
VALUES(0,intFileKey,
current,
current,
'Automated History Request ' || intReqId || ' Completed; Manual Claims Possible, Current thru ' || dteLastUpDate || '; PS Researching', 'auto');
UPDATE req_hist_par
SET message_id = 1
WHERE sup_req_id = intReqId;
ELSE
INSERT INTO diary (diary_key,
file_key,
d_event,
t_event,
subject,
user_name)
VALUES(0,intFileKey,
current,current,
'Automated History Request ' || intReqId ||' Completed, Current thru ' || dteLastUpDate ,'auto');
UPDATE support_requests
SET d_complete = today
WHERE sup_req_id = intReqId;
END IF
END IF --if strErrorCode = "G"
END FOREACH --end curMain
END
--total err "No Claims Found"
SELECT count(*)
INTO intTotalClmErr
FROM client_history
WHERE error_code = "C";
IF intTotalClmErr <> 0 THEN
INSERT INTO batch_cnt(batch_cnt_id,batch_id,cnt_id,value)
VALUES(0,intBatch_id,"5",intTotalClmErr);
END IF
UPDATE batch_stats
SET d_loaded = current
WHERE batch_no = strBatchNo;
--
--purge off data from client_hist_req after when data has already transfered into client data system tables
--data from client_history (staging) table will be purged every time when we start the new load of the
--data into staging table(weekly).
--
--DELETE from client_x_hist;
--DELETE from client_hist_req
--WHERE batch_no = (select batch_no
-- from batch_stats
-- where d_loaded = current);
END PROCEDURE;