Config....
compiled v8 version 21.30903 (had updated to 11/2 back in Nov but it
resulted in massive I/O, Access Violations, and Lock ups so reverted back in
the meantime. Plan to update to current vrs in a few weeks.)
window network with new server
1 gig switches
1 gig network cards
home run cat6
workstation...
windows NT
1 gig network card
Dual processors
2 gig Ram
user...
120/min typer
no extra config, dll
This seems to happen primarly just before printing one of her reports or in
one form selecting records to record in live data. These errors will
continue to cycle threw till she ends the program. Often after going back
in the errors will not be there and the process works. the errors are...
I/O check for full disk
then
I/O database files out of sync
if someone has some idea for resolution here is the most consistant code
that errors pop up on...
RECALC VAR
SET VAR vSalesID INTEGER =(.vSalePayerSalesId)
CALL Pause3 ()
--RptCrtData4Rpt_TransBillingReport
--SET ERROR MESSAGES OFF
SET VAR RptTBD_DoARCalc TEXT='Y'
SET VAR vCheckForTbl INTEGER='0'
SELECT COUNT(*) INTO vCheckForTbl FROM SYS_TABLES WHERE SYS_TABLE_NAME
='att_SaleAndCustInfo4TB'
IF vCheckForTbl ='0' THEN
CREATE TEMPORARY TABLE `att_SaleAndCustInfo4TB` +
(`CustCID` INTEGER , +
`MedProCAID` INTEGER , +
`MedProCid` INTEGER , +
`SalesID` INTEGER , +
`SaleNotes` NOTE (1000) , +
`SalesPersonTxt` TEXT (40) , +
`CustName` TEXT (75) , +
`CustBirthdate` DATE , +
`CustGender` TEXT (1) , +
`CustHeight` TEXT (7) , +
`CustWeight` TEXT (3) , +
`CustNotes` NOTE (4000) , +
`CustAlert` TEXT (50) , +
`CustRefCAID` INTEGER , +
`CustAdd1` TEXT (50) , +
`CustAdd2` TEXT (50) , +
`CustCSZ` TEXT (75) , +
`CustFax` TEXT (20) , +
`CustPhone` TEXT (20) , +
`CustAddContactName` TEXT (30) , +
`CustAddDirections` NOTE (500) , +
`CustAddLookUpDirections` NOTE , +
`BusLocAddLn` TEXT (50) , +
`BusLocCSZ` TEXT (75) , +
`BusLocFaxFmt` TEXT (25) , +
`BusLocLogo` LONG VARBIT , +
`BusLocName` TEXT (30) , +
`BusLocPhoneFmt` TEXT (25) , +
`BusLocNPI` TEXT (10) , +
`DocRefCAID` INTEGER , +
`DocName` TEXT (75) , +
`DocAddContactName` TEXT (30) , +
`DocAdd1` TEXT (50) , +
`DocAdd2` TEXT (50) , +
`DocCSZ` TEXT (75) , +
`DocPhone` TEXT (25) , +
`DocFax` TEXT (75) , +
`DocContactNotes` TEXT (500), +
`DocNPI` TEXT (10) , +
`DocLic` TEXT (25) )
ELSE
DELETE FROM att_SaleAndCustInfo4TB
ENDIF
--removed to try and elim i/os APPEND atv_SaleAndCustInfo TO
att_SaleAndCustInfo4TB WHERE SalesID=.vSalesID
INSERT INTO att_SaleAndCustInfo4TB +
CustCID, +
MedProCAID, +
MedProCid, +
SalesID, +
SaleNotes, +
SalesPersonTxt, +
CustName, +
CustBirthdate, +
CustGender, +
CustHeight, +
CustWeight, +
CustNotes, +
CustAlert, +
CustRefCAID, +
CustAdd1, +
CustAdd2, +
CustCSZ, +
CustFax, +
CustPhone, +
CustAddContactName, +
CustAddDirections, +
CustAddLookUpDirections, +
BusLocAddLn, +
BusLocCSZ, +
BusLocFaxFmt, +
BusLocLogo, +
BusLocName, +
BusLocPhoneFmt, +
BusLocNPI +
SELECT +
T1.CID , +
T1.MedProCAID, +
T1.MedProCid, +
T1.SalesID, +
('Encounter Note: '+ T1.SaleNotes), +
T6.AppDisplayName, +
(IFNULL(T3.CMBusName,T3.CMFname&T3.CMLname,IFNULL(T3.CMLname,T3.CMBusName,T3.CMBusName&'attn:'&T3.CMFname&T3.CMLname))),
+
T4.CustBirthdate, +
T4.CustGender, +
T4.CustHeight, +
T4.CustWeight, +
('Customer Note: '+ T4.CustNotes), +
(IFNULL(T4.CustMajorAlert,T4.CustMajorAlert,'ALERT!!!')), +
T5.RefCAID, +
T5.CA_Address1, +
T5.CA_Address2, +
(T5.CA_City+','&T5.CA_State&T5.CA_Zip), +
(IFNULL(T5.CA_FaxNum ,T5.CA_FaxNum ,'Fax:
('+SGET(T5.CA_FaxNum,3,1)+')'+SGET(T5.CA_FaxNum
,3,4)+'-'+SGET(T5.CA_FaxNum,4,7))), +
(IFNULL(T5.CA_PhoneNum ,T5.CA_PhoneNum ,'Phone:
('+SGET(T5.CA_PhoneNum,3,1)+')'+SGET(T5.CA_PhoneNum
,3,4)+'-'+SGET(T5.CA_PhoneNum,4,7))), +
T5.CA_PrimContName, +
('Address Directions / Note: '+ T5.CA_Directions ), +
' ', +
T7.BusLocAddLn1, +
(T7.BusLocCity+', '+T7.BusLocState&T7.BusLocZip), +
(IFNULL(T7.BusLocFax ,T7.BusLocFax ,'Fax: ('+SGET(T7.BusLocFax
,3,1)+')'+SGET(T7.BusLocFax ,3,4)+'-'+SGET(T7.BusLocFax ,4,7))), +
T7.BusLocLogo, +
T7.BusLocName, +
(IFNULL(T7.BusLocPhone,T7.BusLocPhone,'Phone:
('+SGET(T7.BusLocPhone,3,1)+')'+SGET(T7.BusLocPhone,3,4)+'-'+SGET(T7.BusLocPhone,4,7))),
+
T7.BusLocElectSubNum +
FROM +
Sale T1, +
ContactMaster T3, +
Customer T4, +
ContactAddress T5, +
ZAppAccess T6, +
BusinessLocations T7 +
WHERE T1.SalesID=.vSalesID AND T1.CID=T3.CID AND T1.CID=T4.CID AND
T1.CustResidentialID=T5.CAID AND T1.SalesPerson =T6.AccessID AND
T1.SaleBusLocID=T7.BusLocID
--update customer address info if address is a look up
UPDATE att_SaleAndCustInfo4TB SET +
CustAddContactName =T2.CA_PrimContName, +
CustAdd1=T2.CA_Address1, +
CustAdd2=T2.CA_Address2, +
CustCSZ=(T2.CA_City+','&T2.CA_State&T2.CA_Zip), +
CustAddLookUpDirections=T2.CA_Directions,CustPhone=(IFNULL(T2.CA_PhoneNum,T2.CA_PhoneNum,'Phone:
('+SGET(T2.CA_PhoneNum,3,1)+')'+SGET(T2.CA_PhoneNum,3,4)+'-'+SGET(T2.CA_PhoneNum,4,7))),
+
CustFax =(IFNULL(T2.CA_FaxNum ,T2.CA_FaxNum ,'Fax: ('+SGET(T2.CA_FaxNum
,3,1)+')'+SGET(T2.CA_FaxNum ,3,4)+'-'+SGET(T2.CA_FaxNum ,4,7))) +
FROM att_SaleAndCustInfo4TB T1, ContactAddress T2 +
WHERE T1.CustRefCAID=T2.CAID
UPDATE att_SaleAndCustInfo4TB SET CustAddContactName=' ' WHERE
CustRefCAID='0'
UPDATE att_SaleAndCustInfo4TB SET +
CustAddContactName=(IFNULL(T3.CMBusName,T3.CMFname&T3.CMLname,IFNULL(T3.CMLname,T3.CMBusName,T3.CMBusName&'attn:'&T3.CMFname&T3.CMLname)))
+
FROM att_SaleAndCustInfo4TB T1, ContactAddress T2, ContactMaster T3 +
WHERE T1.CustRefCAID=T2.CAID AND T2.CID=T3.CID AND CustAddContactName IS
NULL
--update doc address info if address is a look up
UPDATE att_SaleAndCustInfo4TB SET +
DocRefCAID=T2.RefCAID , +
DocAddContactName=T2.CA_PrimContName, +
DocAdd1=T2.CA_Address1, +
DocAdd2=T2.CA_Address2, +
DocCSZ=(T2.CA_City+','&T2.CA_State&T2.CA_Zip), +
DocPhone=(IFNULL(T2.CA_PhoneNum,T2.CA_PhoneNum,'Phone:
('+SGET(T2.CA_PhoneNum,3,1)+')'+SGET(T2.CA_PhoneNum,3,4)+'-'+SGET(T2.CA_PhoneNum,4,7))),
+
DocFax =(IFNULL(T2.CA_FaxNum,T2.CA_FaxNum,'Fax:
('+SGET(T2.CA_FaxNum,3,1)+')'+SGET(T2.CA_FaxNum,3,4)+'-'+SGET(T2.CA_FaxNum,4,7))),
+
DocContactNotes=T2.CA_Directions +
FROM att_SaleAndCustInfo4TB T1, ContactAddress T2 +
WHERE T1.MedProCAID=T2.CAID
UPDATE att_SaleAndCustInfo4TB SET +
DocName
=(IFNULL(T3.CMBusName,T3.CMFname&T3.CMLname,IFNULL(T3.CMLname,T3.CMBusName,T3.CMBusName&'attn:'&T3.CMFname&T3.CMLname)))
+
FROM att_SaleAndCustInfo4TB T1, ContactMaster T3 +
WHERE T1.MedProCid =T3.CID
UPDATE att_SaleAndCustInfo4TB SET +
DocAddContactName=(IFNULL(T3.CMBusName,T3.CMFname&T3.CMLname,IFNULL(T3.CMLname,T3.CMBusName,T3.CMBusName&'attn:'&T3.CMFname&T3.CMLname))),
+
FROM att_SaleAndCustInfo4TB T1, ContactMaster T3 +
WHERE T1.MedProCid =T3.CID AND DocAddContactName IS NULL
UPDATE att_SaleAndCustInfo4TB SET +
DocNPI=T3.MedProNPIID,DocLic = T3.MedProDocLicNo +
FROM att_SaleAndCustInfo4TB T1, MedProfessionals T3 +
WHERE T1.MedProCid =T3.CID
UPDATE att_SaleAndCustInfo4TB SET +
DocAddContactName=T2.CA_PrimContName, +
DocAdd1=T2.CA_Address1, +
DocAdd2=T2.CA_Address2, +
DocCSZ=(T2.CA_City+','&T2.CA_State&T2.CA_Zip), +
DocPhone=(IFNULL(T2.CA_PhoneNum,T2.CA_PhoneNum,'Phone:
('+SGET(T2.CA_PhoneNum,3,1)+')'+SGET(T2.CA_PhoneNum,3,4)+'-'+SGET(T2.CA_PhoneNum,4,7))),
+
DocFax =(IFNULL(T2.CA_FaxNum,T2.CA_FaxNum,'Fax:
('+SGET(T2.CA_FaxNum,3,1)+')'+SGET(T2.CA_FaxNum,3,4)+'-'+SGET(T2.CA_FaxNum,4,7)))
+
FROM att_SaleAndCustInfo4TB T1, ContactAddress T2 +
WHERE T1.DocRefCAID=T2.CAID
UPDATE att_SaleAndCustInfo4TB SET DocAddContactName=' ' WHERE DocRefCAID='0'
UPDATE att_SaleAndCustInfo4TB SET +
DocAddContactName=(IFNULL(T3.CMBusName,T3.CMFname&T3.CMLname,IFNULL(T3.CMLname,T3.CMBusName,T3.CMBusName&'attn:'&T3.CMFname&T3.CMLname)))
+
FROM att_SaleAndCustInfo4TB T1, ContactAddress T2, ContactMaster T3 +
WHERE T1.DocRefCAID=T2.CAID AND T2.CID=T3.CID AND DocAddContactName IS NULL
-- temp table not always deleting data prior to insert
DELETE DUPLICATES FROM att_SaleAndCustInfo4TB USING SalesID
SET VAR vCheckForTbl INTEGER='0'
SELECT COUNT(*) INTO vCheckForTbl FROM SYS_TABLES WHERE SYS_TABLE_NAME
='att_TransPayer'
IF vCheckForTbl ='0' THEN
CREATE TEMPORARY TABLE `att_TransPayer` (`SalesID` INTEGER , +
`PayerBillID` TEXT (10), +
`PayorItem_DOS` DATE , +
`SalePayorID` INTEGER , +
`SaleItemPayorID` INTEGER , +
`CustInsVerID` INTEGER , +
`PolicyHolderRelCode` TEXT (2) , +
`SubscriberName` TEXT (30) , +
`SaleItemID` INTEGER , +
`PayerRefCAID` INTEGER , +
`CustInsuranceID` INTEGER , +
`PayerCAID` INTEGER , +
`PayerCID` INTEGER , +
`PayerOrder` INTEGER , +
`BDPayerStat` TEXT (2) , +
`BDBillLine` TEXT (1) , +
`BD2BillCnt` INTEGER , +
`BDBilledCnt` INTEGER , +
`PayerName` TEXT (70) , +
`PayerPAContactName` TEXT (30) , +
`PayerPAAdr1` TEXT (50) , +
`PayerPAAdr2` TEXT (50) , +
`PayerPACSZ` TEXT (70) , +
`PayerPAPhone` TEXT (25) , +
`PayerPAFax` TEXT (25) , +
`PayerNotes` NOTE (500) , +
`PayorItemName` TEXT (60) , +
`PayorLineItemRef` INTEGER , +
`BillingBilledCnt` INTEGER , +
`BilledQty` INTEGER , +
`BillingBilledAmt` CURRENCY , +
`BilledItemAmt` CURRENCY , +
`BillingHCPCS` TEXT (12) , +
`PANum` TEXT (12) , +
`BillingModifiers` TEXT (20) , +
`BillingDXCodes` TEXT (200) , +
`BillingNarative` TEXT (80) , +
`BDAdjCodes` TEXT (20) , +
`PayerAllowedAmt` CURRENCY , +
`PayerPaidAmt` CURRENCY DEFAULT 0, +
`BDHasPrevPayer` TEXT (1) DEFAULT 'N', +
`ClaimPaidInfo` CURRENCY , +
`AmtDue` CURRENCY , +
`AmtPaid` CURRENCY DEFAULT 0, +
`PolicyNo` TEXT (29) , +
`GroupNo` TEXT (29) , +
`CustInsNote` NOTE (4000), +
`BDACNum` TEXT (50) )
-- removed per christina 12/13 `ClaimPaidInfo`=
(IFEQ(BDHasPrevPayer,'Y',(BilledItemAmt-PayerAllowedAmt+PayerPaidAmt),0))
CURRENCY , +
ELSE
DELETE FROM att_TransPayer
ENDIF
INSERT INTO att_TransPayer +
(CustInsVerID,SalesID, PayerCAID, SalePayorID, PayerCID, CustInsuranceID,
PayerOrder, BDPayerStat, PayerNotes, BillingDXCodes, +
PayorItem_DOS, SaleItemPayorID, BDBilledCnt , BillingHCPCS,
BillingModifiers, BillingNarative, AmtDue , BilledQty, PayerAllowedAmt,
SaleItemID, BDACNum, +
BDAdjCodes, BilledItemAmt, PANum, +
BDBillLine, +
PayerPaidAmt) +
SELECT +
CustInsVerID,SalesID, CAID, SalePayorID, InsCID, CustInsuranceID,
PayerOrder, SalePayorStatus, ('Encounter Payer Note: '+ SalePayorNotes),
PayerDxCodes, +
PayorItem_DOS, SaleItemPayorID, BilledCount, BillingHCPCS, BillingModifiers,
BillingNarative, PLTotalCharged, BillingItemQty, AllowedItemTotal,
SaleItemID, ACNum, BillingStatGroup, BillingItemBilledTotal,
InsPAAuthNumber, +
BillThisLine, +
TotalExpectedPaidByPrevPayers +
FROM SalePayor T1,SalePayorItem2 T2 +
WHERE T1.SalePayorID=T2.SalePayorID AND SalesID=.vSalesID AND PayerCat='CI'
+
ORDER BY
PayerOrder,PayorItem_DOS,BillThisLineHow,InsPAAuthNumber,SaleItemPayorID
INSERT INTO att_TransPayer +
(SaleItemID,PayerOrder,BillingBilledAmt,SaleItemPayorID,BDPayerStat,BDBillLine,BDBilledCnt,
AmtDue, PayerPaidAmt, PayorItem_DOS, BillingBilledCnt, SalesID,
PayerName,SalePayorID, PayorItemName, BilledQty, BilledItemAmt,
BillingHCPCS, BillingDXCodes, PayerAllowedAmt, PayerNotes ) +
SELECT
SaleItemID,T1.PayerOrder,T2.BillingItemBilledTotal,T2.SaleItemPayorID,T1.SalePayorStatus,T2.BillThisLine,T2.BilledCount,T2.PLTotalCharged
, T2.TotalExpectedPaidByPrevPayers , T2.PayorItem_DOS , T2.BilledCount,
T1.SalesID, 'Customer/Customer Contact', T1.SalePayorID, T2.PayorItemName,
T2.BillingItemQty, T2.BillingItemBilledTotal, T2.BillingHCPCS,
T2.BillingDXCodes, T2.AllowedItemTotal, ('Payer Notes:'+T1.SalePayorNotes) +
FROM SalePayor T1,SalePayorItem2 T2 +
WHERE T1.SalePayorID=T2.SalePayorID AND SalesID=.vSalesID AND PayerCat='CC'
+
ORDER BY
PayerOrder,PayorItem_DOS,BillThisLineHow,InsPAAuthNumber,SaleItemPayorID
-- i/o seem start here...
UPDATE att_TransPayer SET +
PayerBillID=T3.BillingPayerID ,
PayerName=(IFNULL(T2.CMBusName,(T2.CMFname&T2.CMMname&T2.CMLname),(T2.CMBusName&(IFNULL(T2.CMFname,T2.CMFname,('attn:'&T2.CMFname&T2.CMMname&T2.CMLname)))))),
+
PayerPAContactName=(IFEQ(T3.CA_PrimContact,'S',(IFNULL(T2.CMBusName,(T2.CMFname&T2.CMMname&T2.CMLname),(T2.CMBusName&(IFNULL(T2.CMFname,T2.CMFname,('attn:'&T2.CMFname&T2.CMMname&T2.CMLname)))))),T3.CA_PrimContName)),
PayerPAAdr1=T3.CA_Address1, +
PayerPAAdr2=T3.CA_Address2, +
PayerPACSZ=(T3.CA_City+','&T3.CA_State&T3.CA_Zip), +
PayerPAPhone=(IFNULL(T3.CA_PhoneNum,T3.CA_PhoneNum,'Phone:
('+SGET(T3.CA_PhoneNum,3,1)+')'+SGET(T3.CA_PhoneNum,3,4)+'-'+SGET(T3.CA_PhoneNum,4,7))),
+
PayerPAFax=(IFNULL(T3.CA_FaxNum,T3.CA_FaxNum,'Fax:
('+SGET(T3.CA_FaxNum,3,1)+')'+SGET(T3.CA_FaxNum,3,4)+'-'+SGET(T3.CA_FaxNum,4,7)))
+
FROM att_TransPayer T1,ContactAddress T3, ContactMaster T2 +
WHERE T1.PayerCAID=T3.CAID AND T3.CID=T2.CID
UPDATE att_TransPayer SET +
PolicyNo=T2.PolicyNo,GroupNo=T2.GroupNo,CustInsNote=('Cust Ins Note: '+
T2.CustInsNote) +
FROM att_TransPayer T1,CustInsurance T2 +
WHERE T1.CustInsuranceID =T2.CustInsuranceID
UPDATE att_TransPayer SET +
PolicyholderRelCode=T2.PolicyholderRelCode,
SubscriberName=(T2.PolicyHolderFName&T2.PolicyHolderLName ) +
FROM att_TransPayer T1,CustInsVerify T2 +
WHERE T1.CustInsVerID=T2.CustInsVerID
UPDATE att_TransPayer SET +
SubscriberName=CustName +
FROM att_TransPayer T1,att_SaleAndCustInfo4TB T2 +
WHERE T1.SalesID=T2.SalesID AND PolicyholderRelCode='1'
SET VAR RptTBD_SaleItemPayorID INTEGER
SET VAR RptTBD_P CURRENCY='0'
SET VAR RptTBD_R CURRENCY='0'
SET VAR RptTBD_B CURRENCY='0'
DROP CURSOR GetPayerItems
DECLARE GetPayerItems CURSOR FOR SELECT SaleItemPayorID FROM att_TransPayer
OPEN GetPayerItems
FETCH GetPayerItems INTO RptTBD_SaleItemPayorID
WHILE SQLCODE<>100 AND SQLCODE>=0 THEN
SELECT SUM(PaymentAmt) INTO RptTBD_P FROM PayerPayments WHERE
SaleItemPayorID=.RptTBD_SaleItemPayorID AND PaymentAction IN ('w','-')
SELECT SUM(PaymentAmt) INTO RptTBD_R FROM PayerPayments WHERE
SaleItemPayorID=.RptTBD_SaleItemPayorID AND PaymentAction='+'
SET VAR RptTBD_B=(.RptTBD_P-.RptTBD_R)
UPDATE att_TransPayer SET +
AmtPaid=(.RptTBD_B) +
WHERE SaleItemPayorID=.RptTBD_SaleItemPayorID
SET VAR RptTBD_P CURRENCY='0'
SET VAR RptTBD_R CURRENCY='0'
SET VAR RptTBD_B CURRENCY='0'
FETCH GetPayerItems INTO RptTBD_SaleItemPayorID
ENDWHILE
DROP CURSOR GetPayerItems
--end of sp
PRINT TransBillingReport ARRANGE att_TransPayer BY PayerOrder WHERE
SalesID=.vSalesID OPTION SCREEN|ZOOM_TYPE 100_PERCENT|WINDOW_STATE MAXIMIZED
RETURN
