ئhGLF g .TStringList<*cdBdeZkcdDBTablesZkfTQuery-gTQuery.SetQuerygTQuery.ExecSQLg.TQueryg TQuery.CreategTDBDataSet.SetDatabaseName !~cdSysUtilsZkgIntToStrJ 7g FloatToStrmgTrim~ cdSysInitZkcdSystemZkfLongint|ofBooleanNcf AnsiString8fString8g @LStrLAsgX,Tdg @LStrCat3X,Tdg @LStrCatNX,Tdg@LStrClrg @LStrArrayClrX,Tdg@HandleFinallyX,TdgTObject.Create݋RgTrue1ogFalse1og TObject.Free݋RfInteger|ogRoundgStrg@Str2ExtX,Tdg@ROUNDX,Tdg@LStrFromString|g @VarToRealX,Tdg@VarClrX,TdceControlsZkgTControl.SetTextc_ceFormsZkgTCustomForm.Createj-gTCustomForm.Showj-gTCustomForm.Closej-ceDbZkg TDataSet.Open4gTDataSet.Close4gTDataSet.FieldByName4g TDataSet.Next4ce MyMessage f TMessagesFormcO<*g.TMessagesForm8g TMessagesForm.ProcessWinMessagesV*NceGenFns gUpdateBatchStatusz3>ce DateFunctions ceProgress ceDataMod ceMain g s_User_IDgMainForm.|c .1f qTNf< qStringListf>(UpdateDubiousTestList ! nThisBatch!nThisDubiousTypeb! nThisServiceb! nThisCustomer !nThisSupplierServiceCodeID" sBatchf sServicef sCustomerf sDubiousTypef lCustomerOk sSupplierCodef lSupplierCodeOkfc( DubiousTest_1pta! nThisBatch" lSuccessfulf nBatch nDubiousf qryTN qryStringListc( DubiousTest_2pta! nThisBatch" lSuccessfulf nBatch nDubiousf qryTN qryStringListc( DubiousTest_3pta=! nThisBatch" lSuccessfulf nBatchf sBatchf sBatchTypeIDf nValue sVariancef nDubiousf qryTN qryStringListc( DubiousTest_4pta7! nThisBatch" lSuccessfulf nBatch nDubiousf qryTN qryStringList sVariancef c( DubiousTest_5pta! nThisBatch" lSuccessfulf nBatch nDubiousf qryTN qryStringListc(DubiousErrorTestpta! nThisBatch" lSuccessfulf DubiousMsg nBatchf sBatchf qryTN qryBatchType qryStringListf lResultIsGoodfc( FinalizationX,Td\! c( DubiousTests!cD@lYUQjjIuMSVWMU؋} 3UhMd0d UUEUEÄt U Eh}Et UE EhR@xQ4E܋MU܋Q4E܋M躼U܋Q4E܋MU܋Q4tE܋MU܋Q4 Q4}tE܋M4U܋Q4 \Q4R@Q4Q4huhuhuhuhuh0h$Eܺ U܋Q43ZYYdhTEE_^[]NULLSELECT * FROM tDubiousResultWHERE BatchID = AND DubiousTypeID = AND ServiceID = AND CustomerID = AND CustomerID IS NULLAND SupplierServiceCodeID = !AND SupplierServiceCodeID IS NULLINSERT INTO tDubiousResultN(BatchID, ServiceID, CustomerID, SupplierServiceCodeID, DubiousTypeID, UserID) VALUES ( ,  )USVW3ɉMMU3Uhd2d"3ɲغ  Q4DQ4t ExRTEƋR@ƋQ4ƋQ4ƋQ4pƋQ4ƋQ4ƋQ4UMEPUƋQ4xƋQ4ƋQ4 ƋQ4TƋQ4ƋQ4֋ttEIE8RTPjRTȋUt3ZYYdhE_^[]dbPPdataSELECT ID FROM tDubiousType+WHERE Description LIKE 'Rental Variations%'id SELECT TN2.*YFROM (SELECT tTransaction.ServiceID, tTransaction.CustomerID, tTransaction.ServiceTypeID,e Sum(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.AmountExGST ELSE 0 END) AS RENTFROM (tTransactionT LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID)Z LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.IDWHERE tTransaction.BatchID = AND tTransaction.RevenueFlag = 1\GROUP BY tTransaction.ServiceID, tTransaction.CustomerID, tTransaction.ServiceTypeID) AS TN2> LEFT JOIN tServiceType ON TN2.ServiceTypeID = tServiceType.ID+WHERE tServiceType.ExpectedRent IS NOT NULL=AND ROUND(TN2.Rent, 2) <> ROUND(tServiceType.ExpectedRent, 2) CustomerID ServiceIDUSVW3ɉMMU3Uhd2d"3ɲغ0DQ4hQ4t ERTEƋR@ƋQ4ƋQ4$ƋQ4ƋQ4ƋQ4 ƋQ4ƋQ4UMEUƋQ4 ƋQ48ƋQ4ƋQ4ƋQ4(ƋQ4DƋQ4dƋQ4֋ttEIE8RTPjRTȋUt3ZYYdhE_^[]dbPPdataSELECT ID FROM tDubiousType)WHERE Description LIKE 'Calls less than%'id SELECT TN2.*ZFROM ((SELECT tTransaction.ServiceID, tTransaction.CustomerID, tTransaction.ServiceTypeID,g Sum(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.AmountIncGST ELSE 0 END) AS RENT,f Sum(CASE tTransactionGroup.Description WHEN 'CALL' THEN tTransaction.AmountIncGST ELSE 0 END) AS CALLFROM (tTransactionT LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID)Z LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.IDWHERE tTransaction.BatchID = AND tTransaction.RevenueFlag = 1\GROUP BY tTransaction.ServiceID, tTransaction.CustomerID, tTransaction.ServiceTypeID) AS TN27 LEFT JOIN tServiceID ON TN2.ServiceID = tServiceID.ID)> LEFT JOIN tServiceType ON TN2.ServiceTypeID = tServiceType.IDWHERE TN2.Call <> 0AND TN2.Call < TN2.RentAND tServiceType.NoCalls = 0 CustomerID ServiceIDUSVW3ɉMMMMU3Uhd2d"EUE3ɲغQ4Q4t ERTER@Q48Q4EMLU䋃Q4tE`lRTUR@Q4Q4Q4RPu |d~ R@ۅ5<$EƋR@ƋQ4`ƋQ4ƋQ48ƋQ4ƋQ4ƋQ4DƋQ4\ƋQ4xƋQ4ƋQ4h ƋQ4 ƋQ40 ƋQ4t ƋQ4 ƋQ4 ƋQ4D ƋQ4 ƋQ4 ƋQ4 ƋQ4, ƋQ4L ƋQ4EMh UƋQ4 ƋQ4 ƋQ4 ƋQ4\ ƋQ4EM UƋQ4 ƋQ4ƋQ4$ƋQ4`ƋQ4ƋQ4huhEUƋQ4֋ttEZEI,RTP@RTP`RTȋUEt3ZYYdhEE_^[]dbPPdataSELECT ID FROM tDubiousType WHERE Description LIKE 'Tariff%'idSELECT BatchTypeID FROM tBatch WHERE ID = 0 BatchTypeID%SELECT PARMNUMBER AS "TariffVariance"FROM tParameter WHERE PARM LIKE 'TariffVariance'TariffVarianceBESELECT DISTINCT TN.ServiceID, TN.CustomerID, TN.SupplierServiceCodeIDWFROM (SELECT TN3.ServiceID, TN3.CustomerID, TN3.AmountExGST, TN3.SupplierServiceCodeID,l (CASE WHEN TN3.CalcAmtExGST < TN3.MinCharge THEN TN3.MinCharge ELSE TN3.CalcAmtExGST END) AS "CalcAmtExGST"WFROM (SELECT TN2.ServiceID, TN2.CustomerID, TN2.AmountExGST, TN2.SupplierServiceCodeID,D ((TN2.NumCalls * TN2.FlagFall) + (TN2.NumCalls * TN2.FixedCharge) +Q (CEILING(TN2.Duration / ChargeInterval) * TN2.InitialCharge)) AS "CalcAmtExGST", TN2.MinChargeFROM (SELECT TN1.*,r (CASE WHEN tAgreementTariffs.FixedCharge IS NULL THEN 0 ELSE tAgreementTariffs.FixedCharge END) AS "FixedCharge",i (CASE WHEN tAgreementTariffs.FlagFall IS NULL THEN 0 ELSE tAgreementTariffs.FlagFall END) AS "FlagFall",v (CASE WHEN tAgreementTariffs.Stage1Charge IS NULL THEN 0 ELSE tAgreementTariffs.Stage1Charge END) AS "InitialCharge",? (CASE WHEN tAgreementTariffs.ChargeIntervalSecs IS NULL THEN 1; WHEN tAgreementTariffs.ChargeIntervalSecs = 0 THEN 1J ELSE tAgreementTariffs.ChargeIntervalSecs END) AS "ChargeInterval",6 (CASE WHEN tAgreementTariffs.MinCharge IS NULL THEN 02 WHEN tAgreementTariffs.MinCharge < 0 THEN 0; ELSE tAgreementTariffs.MinCharge END) AS "MinCharge";FROM (SELECT ServiceID, CustomerID, Count(*) AS "NumCalls",* Sum(CAST(Duration AS int)) AS "Duration",# Sum(AmountExGST) AS "AmountExGST", SupplierServiceCodeIDFROM tTransactionWHERE BatchID =  AND Duration IS NOT NULL=GROUP BY ServiceID, CustomerID, SupplierServiceCodeID) AS TN1bLEFT JOIN tAgreementTariffs ON TN1.SupplierServiceCodeID = tAgreementTariffs.SupplierServiceCodeIDELEFT JOIN tAgreement ON tAgreementTariffs.AgreementID = tAgreement.IDWHERE tAgreement.BatchTypeID = AND tAgreementTariffs.Active = 1AND tAgreement.Active = 10AND ((tAgreementTariffs.FixedCharge IS NOT NULL)- OR (tAgreementTariffs.FlagFall IS NOT NULL)I OR (tAgreementTariffs.Stage1Charge IS NOT NULL))) AS TN2) AS TN3) AS TNWHERE TN.CalcAmtExGST > ( * TN.AmountExGST) CustomerIDSupplierServiceCodeID ServiceIDUjjIuQSVWU3Uhd2d"3ɲQ4@Q4t ElRTER@xQ4Q4tE+UQ\E<$EËR@ËQ4(ËQ4|ËQ4ËQ40ËQ4|ËQ4ËQ4ËQ4ËQ4ËQ4DËQ4XËQ4|ËQ4|ËQ4ËQ4ËQ4 ËQ4H ËQ4|ËQ4 ËQ4 ËQ4 ËQ4 ËQ4X ËQ4l ËQ4|ËQ4 ËQ4 ËQ4 ËQ4( ËQ4x ËQ4U܋M܍Eດ UËQ4 ËQ4 ËQ4EM8 UËQ4EM` UËQ4EM UËQ4EM UËQ4ӋttEIE8 RTPj RTȋUt3ZYYdhEܺEE_^[]dbPPdataSELECT ID FROM tDubiousType"WHERE Description LIKE 'Activity%'id!SELECT ParmNumber FROM tParameter)WHERE Parm LIKE 'ActivityVariancePerCent'10 ParmNumber%SELECT SID.ServiceID, SID.CustomerID,J SID.CurrentRent, SID.PreviousRent, SID.RentChange, SID.RentChangePerCent,G SID.CurrentCalls, SID.PreviousCalls, SID.CallChange, CallChangePerCentZFROM (SELECT TN.ServiceID, TN.CustomerID, tServiceID.CurrentRent, tServiceID.PreviousRent,B (tServiceID.CurrentRent - tServiceID.PreviousRent) AS RentChange, CASEz WHEN tServiceID.PreviousRent > 0 THEN (tServiceID.CurrentRent - tServiceID.PreviousRent) / tServiceID.PreviousRent * 100z WHEN tServiceID.PreviousRent < 0 THEN (tServiceID.PreviousRent - tServiceID.CurrentRent) / tServiceID.PreviousRent * 100M WHEN tServiceID.PreviousRent = 0 AND tServiceID.CurrentRent > 0 THEN 100.00N WHEN tServiceID.PreviousRent = 0 AND tServiceID.CurrentRent < 0 THEN -100.00 ELSE 0.00 END AS RentChangePerCent,? WHEN tServiceID.PreviousRent > 0 THEN tServiceID.PreviousRent# ELSE tServiceID.PreviousRent * -1 END AS CalcRent,3 tServiceID.CurrentCalls, tServiceID.PreviousCalls,D (tServiceID.CurrentCalls - tServiceID.PreviousCalls) AS CallChange,~ WHEN tServiceID.PreviousCalls > 0 THEN (tServiceID.CurrentCalls - tServiceID.PreviousCalls) / tServiceID.PreviousCalls * 100~ WHEN tServiceID.PreviousCalls < 0 THEN (tServiceID.PreviousCalls - tServiceID.CurrentCalls) / tServiceID.PreviousCalls * 100L WHEN tServiceID.PreviousCalls = 0 AND tServiceID.CurrentCalls > 0 THEN 100M WHEN tServiceID.PreviousCalls = 0 AND tServiceID.CurrentCalls < 0 THEN -100 ELSE 0 END AS CallChangePerCent,A WHEN tServiceID.PreviousCalls > 0 THEN tServiceID.PreviousCalls$ ELSE tServiceID.PreviousCalls * -1 END AS CalcCallsEFROM (SELECT DISTINCT tTransaction.ServiceID, tTransaction.CustomerID FROM tTransaction WHERE tTransaction.BatchID = ) AND tTransaction.RevenueFlag <> 0) AS TN<LEFT JOIN tServiceID ON TN.ServiceID = tServiceID.ID) AS SIDWHERE SID.RentChangePerCent > OR SID.RentChangePerCent < -OR SID.CallChangePerCent > OR SID.CallChangePerCent < - CustomerID ServiceIDUSVW3ɉMMU3Uhd2d"3ɲغQ48Q4t EhRTEƋR@tƋQ4ƋQ4ƋQ44ƋQ4UMEUƋQ4ƋQ4ƋQ44ƋQ4hƋQ4ƋQ4ƋQ4֋ttEIE8RTPj0RTȋUt3ZYYdhE_^[]dbPPdataSELECT ID FROM tDubiousType%WHERE Description LIKE 'Value Range%'idQSELECT tTransaction.ServiceID, tTransaction.CustomerID, tTransaction.AmountIncGSTFROM (tTransaction?LEFT JOIN tServiceID ON tTransaction.ServiceID = tServiceID.ID)ULEFT JOIN tCostRange ON tTransaction.TransactionTypeID = tCostRange.TransactionTypeIDWHERE tTransaction.BatchID = !AND tTransaction.RevenueFlag <> 0BAND tCostRange.ValidAccountNumberID = tServiceID.SupplierAccountID(AND tCostRange.CostRangeFrom IS NOT NULL&AND tCostRange.CostRangeTo IS NOT NULL9AND (tTransaction.AmountIncGST < tCostRange.CostRangeFrom6OR tTransaction.AmountIncGST > tCostRange.CostRangeTo) CustomerID ServiceIDUSVW3ɉMMU3Uhd0d 3ɲغ3ҋuUEEUEEE3ɲ<3ɲ<R@PQ4`Q4EM|U苆Q4Q4R@3ɲ<ER@EQ4EQ4EQ4h4uhPEUEQ4U\R@t$t3ҋUEE8tEUR@t$3ҋUEE8tEUR@t$3ҋUEE8tEU,R@t$D3ҋUEE8tEUtR@t$3ҋUEE8tEUE8t< E8 EE3ZYYdhEE_^[]Dubious Error ChecksClearing previous results ...dbPPdataDELETEFROM tDubiousResultWHERE BatchID = AND ResolutionID IS NULLSELECT tBatchType.*!FROM tBatchType INNER JOIN tBatch& ON tBatchType.ID = tBatch.BatchTypeIDWHERE (tBatch.ID = )DubiousCheck01"Checking for Rental Variations ...DubiousCheck02Checking for Calls < Rent ...DubiousCheck03*Checking for Tariff & Rates Compliance ...DubiousCheck04$Checking for Changes in Activity ...DubiousCheck053Checking Splits are only between valid Services ...U3Uh%d0d 3ZYYdh,]Ã-m=D & ,. ,$ .* .* .2 ."0 ."   ^ $2 z0 `     `24 6i0 8   $^.b`" . ^Nbb"d `>>4 60 8   $^.b`" . ^Nbb"d `>>4 6MB( 8   $^.b`  , .*^" ,b`   $^ bH@`: 8FJ$ .$ .0&^Nbbb$d `>>24 6a+4 8   $^.b`  $^" ,b L`" .$ .* .* .* .&^Nbb"d `>>4N2 6(0 8   $^.b`" . ^Nbb"d `>>4 6M 4j VR RXl" 8  8        , .&$( 0*^ bRl*bRl*bRl*bRl*bRl:| p| p  >>`>  `  >`>Z>22 6 ~ 6 ~  Zh 4448 8HhQD   $(H$bRD   $(H$bR5V   $(  <02   0.44H$bI+H   $(  0VH4444$br'D   $(H$bR5 H  , "&   <  J*  *  *  *  *  .*X-  &v,)&0=&6Md&z&m"& Hph h| pDH Hd I pDH Hd]} }pV\Ύ̮e ̥iHpHL Lhڈօ ֥  pDH Hd 0pHhUq 9   a