Hi,
 
I think the error is correct if you compare the select statement and the returns list at position 6.  ATD.ACCT_ID probably does not match  R_FILING_COUNTY_ NAME VarChar(50),

--- On Sun, 26/4/09, Svein Erling Tysvaer <svein.erling.tysvaer@...> wrote:

From: Svein Erling Tysvaer <svein.erling.tysvaer@...>
Subject: Re: [firebird-support] FOR EXECUTE STATEMENT (Error when trying to run a stored procedure) Anyone?
To: firebird-support@yahoogroups.com
Date: Sunday, 26 April, 2009, 6:54 PM









Not really a revolutionary idea, but I'd try creating a new stored
procedure and only include only the part that I expected to cause the
problem (i.e. to the end of EXECUTE STATEMENT). If it still got an
error, I'd try to remove code until things worked to find the error. The
main thing that puzzles me with your error message, is ONTO (though I've
minimal knowledge of stored procedures and error messages). Do you get
the same error message if you hardcode the sql statement rather than
pass it as an input variable?

Set

SoftTech wrote:
> Anyone with any ideas at all on how I can get this to work?
>
> Thanks to all of you who have replied.
>
> I decided to build the SQL statement in the Delphi application interface and pass it to the stored procedure. I now have one input parameter of V_SQL_STATEMENT to which I pass the sql statement that was built in the app.
>
> SELECT DISTINCT
ATD.ACCT_TRAN_ ID,
ATD.ACCT_TRAN_ DETAIL_ID,
ATDT.ACCT_TASK_ ID,
CAST(ATDT.CREATE_ DATE AS DATE) AS CREATE_DATE,
CAST(ATDT.PROCESSED _DATE AS DATE) AS PROCESSED_DATE,
ATD.ACCT_ID,
ATD.CASE_ID, FC.FEE_CODE, FC.DESCRIPTION AS FEE_DESC, ATDTF.FEE_ID, ATDTF.FEE_DATE, ATDTF.FEE_AMOUNT, C.NAME AS FILING_COUNTY_ NAME, CS.SITE_REF_ NO, CS.SITE_NAME, CLT.GOVERNMENT_ CLIENT, T.TRAN_CODE, T.TRAN_TYPE_ ID FROM ACCT_TRAN_DETAIL_ TASK_FEE ATDTF JOIN FEE_CODE FC ON FC.FEE_ID = ATDTF.FEE_ID JOIN ACCT_TRAN_DETAIL_ TASK ATDT ON ATDT.ACCT_TRAN_ ID = ATDTF.ACCT_TRAN_ ID AND ATDT.ACCT_TRAN_ DETAIL_ID = ATDTF.ACCT_TRAN_ DETAIL_ID AND ATDT.ACCT_TASK_ ID = ATDTF.ACCT_TASK_ ID JOIN ACCT_TRAN_DETAIL_ TASK_DEBTS ATDTD ON ATDTD.ACCT_TRAN_ ID = ATDT.ACCT_TRAN_ ID AND ATDTD.ACCT_TRAN_ DETAIL_ID = ATDT.ACCT_TRAN_ DETAIL_ID AN
> D ATDTD.ACCT_TASK_ ID = ATDT.ACCT_TASK_ ID JOIN DEBT D ON D.ACCT_ID = ATDTD.ACCT_ID AND D.DEBT_NO = ATDTD.DEBT_NO JOIN ACCT_TRAN_DETAIL ATD ON ATD.ACCT_TRAN_ ID = ATDT.ACCT_TRAN_ ID AND ATD.ACCT_TRAN_ DETAIL_ID = ATDT.ACCT_TRAN_ DETAIL_ID JOIN ACCT_TRAN_MASTER ATM ON ATM.ACCT_TRAN_ ID = ATD.ACCT_TRAN_ ID JOIN TE_TRAN T ON T.TRAN_ID = ATM.TRAN_ID JOIN ACCT_CASE AC ON AC.ACCT_ID = ATD.ACCT_ID AND AC.CASE_ID = ATD.CASE_ID JOIN CLIENT_SITE CS ON CS.CLT_SITE_ ID = AC.CLT_SITE_ ID JOIN CLIENT CLT ON CLT.CLT_ID = CS.CLT_ID JOIN COUNTY C ON C.COUNTY_ID = AC.FILING_COUNTY_ ID WHERE (ATDT.QUE_STATUS_ CODE = 'C' AND D.ACCOUNT_EXPORT_ SETUP_ID IS NULL AND ATM.COMPANY_ ID = 3 AND (FC.FEE_CODE IN ('SOP','SC', 'SC')) AND (CAST(ATDTF. FEE_DATE AS DATE) BETWEEN '4/15/2009' AND '4/15/2009') ) AND ATM.TRAN_ID IN (221,242,253
> )
>
> This SQL I did test in Database Workbench and it returned the records I expected.
>
> This is my FOR EXECUTE STATEMENT now:
>
> FOR EXECUTE STATEMENT :V_SQL_STATEMENT
> INTO :iAcctTranID, :iAcctTranDetailID, :iAcctTaskID, :dFeeCreateDate,
> :dProcessedDate, :iAcctID, :iCaseID, :R_FEE_CODE, :R_FEE_DESC,
> :iFeeID, :R_FEE_DATE, :R_FEE_AMOUNT, :R_FILING_COUNTY_ NAME,
> :R_CLT_SITE_ REF_NO, :R_CLT_SITE_ NAME, :iGovernmentClient, :sTranCode,
> :iTranTypeID DO
> BEGIN
> /* Code */
> END
>
> But now I get an error:
>
> ISC ERROR CODE:335544829
>
> ISC ERROR MESSAGE:
> Variable type (position 6) in EXECUTE STATEMENT 'SELECT DISTINCT ATD.ACCT_TRAN_ ID, ATD.ACCT_TRAN_ DE' ONTO does not match return
>
> I have double checked that that my fields in the SELECT statement match those in the INTO section.
>
> Any ideas what is wrong or anther way to do this?
>
> Here if the complete DDL if anyone needs to see it:
> SET TERM ^^ ;
> CREATE PROCEDURE SPS_FEE_REPORT_ DATA (
> V_SQL_STATEMENT VarChar(2000) ,
> V_NOTICE_ID Integer,
> V_CLT_SITE_OPTION SmallInt,
> V_EXCLUDE_GOV_ CLTS SmallInt,
> V_FEE_TYPE_TO_ INCLUDE SmallInt)
> returns (
> R_ACCT_REF_NO VarChar(14),
> R_FEE_CODE VarChar(6),
> R_FEE_DESC VarChar(60),
> R_FEE_DATE Date,
> R_FEE_AMOUNT Numeric(15,2) ,
> R_FILING_COUNTY_ NAME VarChar(50),
> R_CLT_SITE_REF_ NO VarChar(10),
> R_CLT_SITE_NAME VarChar(50),
> R_DEBTOR1_NAME VarChar(50),
> R_DEBTOR2_NAME VarChar(50),
> R_DEBTOR3_NAME VarChar(50),
> R_NOTICE_DATE Date,
> R_CLT_REF_NOS VarChar(170) ,
> R_DEBTOR1_CASE_ NUMBER VarChar(12),
> R_DEBTOR2_CASE_ NUMBER VarChar(12),
> R_DEBTOR3_CASE_ NUMBER VarChar(12),
> R_ACTION_CODE VarChar(17),
> R_DEBTOR1_SERVICE_ TYPE_REQ_ CODE VarChar(6),
> R_DEBTOR2_SERVICE_ TYPE_REQ_ CODE VarChar(6),
> R_DEBTOR3_SERVICE_ TYPE_REQ_ CODE VarChar(6),
> R_DEBTOR1_SERVICE_ COUNTY VarChar(30),
> R_DEBTOR2_SERVICE_ COUNTY VarChar(30),
> R_DEBTOR3_SERVICE_ COUNTY VarChar(30),
> R_FEE_PAYABLE_ TO VarChar(10),
> R_IMPORTED_FEE Char(1),
> R_FEE_PAID_IN_ FULL Char(2))
> AS
> /*
> Author : Michael G. Tuttle
> Date : 3/18/2008 2:02:20 PM
> Purpose : This procedure will be used to generate data for the Court Cost, Surcharge
> and Service of Process Fee Reports
> */
> DECLARE VARIABLE iAcctTranID Integer;
> DECLARE VARIABLE iAcctTranDetailID Integer;
> DECLARE VARIABLE iAcctTaskID Integer;
> DECLARE VARIABLE sFirstName VarChar(15);
> DECLARE VARIABLE sMiddleName VarChar(15);
> DECLARE VARIABLE sLastName VarChar(25);
> DECLARE VARIABLE sSirName VarChar(15);
> DECLARE VARIABLE sDBAName VarChar(50);
> DECLARE VARIABLE sFullName VarChar(60);
>
> DECLARE VARIABLE iAcctID Integer;
> DECLARE VARIABLE iCaseID SmallInt;
> DECLARE VARIABLE sCltRefNo VarChar(20);
>
> DECLARE VARIABLE iPersonID Integer;
> DECLARE VARIABLE sCaseNumber VarChar(12);
>
> /* 04.23.08 */
> DECLARE VARIABLE iPersonCount SmallInt;
> DECLARE VARIABLE sTranCode VarChar(10);
> DECLARE VARIABLE sServTypeReqCode VarChar(6);
>
> /* 04.25.08 */
> DECLARE VARIABLE sAddress1 VarChar(50);
> DECLARE VARIABLE sAddress2 VarChar(50);
> DECLARE VARIABLE sCity VarChar(25);
> DECLARE VARIABLE sServiceCounty VarChar(30);
> DECLARE VARIABLE sStateCode Char(2);
> DECLARE VARIABLE sZipCode VarChar(10);
> DECLARE VARIABLE sZipCodeSuffix VarChar(4);
> DECLARE VARIABLE iCompSelectAddr SmallInt;
> DECLARE VARIABLE iNoticeSent SmallInt;
> DECLARE VARIABLE iServReqAcctTaskID Integer;
>
> DECLARE VARIABLE sFeePrePaidBy Char(1);
> DECLARE VARIABLE sCompanyAbbrev VarChar(6);
> DECLARE VARIABLE sComanyName VarChar(50);
> DECLARE VARIABLE sErrorMsg VarChar(500) ;
>
> DECLARE VARIABLE iFeeID Integer;
> DECLARE VARIABLE nAgencyPercent Numeric(3,2) ;
> DECLARE VARIABLE nFwdAgencyPercent Numeric(3,2) ;
> DECLARE VARIABLE iGovernmentClient SmallInt;
> DECLARE VARIABLE iOKToInclude SmallInt;
> DECLARE VARIABLE dFeeCreateDate Date;
> DECLARE VARIABLE iCaseFeeID Integer;
>
> /* 02.11.09 */
> DECLARE VARIABLE cCaseFeeAmount NUMERIC(15,2) ;
> DECLARE VARIABLE cCaseFeeBal NUMERIC(15,2) ;
> DECLARE VARIABLE cCaseFeePmt NUMERIC(15,2) ;
> DECLARE VARIABLE sChangeType CHAR(1);
> DECLARE VARIABLE iFeeBackedOut SmallInt;
> DECLARE VARIABLE dProcessedDate Date;
> DECLARE VARIABLE iTranTypeID SmallInt;
>
> begin
> FOR EXECUTE STATEMENT :V_SQL_STATEMENT
> INTO :iAcctTranID, :iAcctTranDetailID, :iAcctTaskID, :dFeeCreateDate,
> :dProcessedDate, :iAcctID, :iCaseID, :R_FEE_CODE, :R_FEE_DESC,
> :iFeeID, :R_FEE_DATE, :R_FEE_AMOUNT, :R_FILING_COUNTY_ NAME,
> :R_CLT_SITE_ REF_NO, :R_CLT_SITE_ NAME, :iGovernmentClient, :sTranCode,
> :iTranTypeID DO
> BEGIN
> cCaseFeeAmount = 0.00;
> cCaseFeeBal = 0.00;
> cCaseFeePmt = 0.00;
> iFeeBackedOut = 0;
>
> /* If imported Fee */
> IF (iTranTypeID = -9000) THEN
> BEGIN
> R_IMPORTED_FEE = '*';
>
> /* Uses dFeeCreateDate */
> SELECT DISTINCT
> CF.CASE_FEE_ ID,
> CP.AGENCY_PERCENT,
> CP.FWD_AGENCY_ PERCENT
> FROM CASE_FEE CF
> JOIN CASE_FEE_HISTORY CFH ON CFH.CASE_FEE_ ID = CF.CASE_FEE_ ID
> JOIN COLL_PLAN_REV_ DETAIL CP ON CP.COLL_PLAN_ ID = CF.COLL_PLAN_ ID
> AND CP.COLL_PLAN_ REV_NO = CF.COLL_PLAN_ REV_NO
> AND CP.FEE_ID = CF.FEE_ID
> WHERE CF.ACCT_ID = :iAcctID
> AND CF.CASE_ID = :iCaseID
> AND CF.FEE_ID = :iFeeID
> AND CFH.CHANGE_TYPE = 'C'
> AND CAST(CFH.CREATE_ DATE AS DATE) = :dFeeCreateDate
> INTO :iCaseFeeID, :nAgencyPercent, :nFwdAgencyPercent;
> END
> ELSE
> BEGIN
> R_IMPORTED_FEE = ' ';
>
> /* Uses dProcessedDate */
> SELECT DISTINCT
> CF.CASE_FEE_ ID,
> CP.AGENCY_PERCENT,
> CP.FWD_AGENCY_ PERCENT
> FROM CASE_FEE CF
> JOIN CASE_FEE_HISTORY CFH ON CFH.CASE_FEE_ ID = CF.CASE_FEE_ ID
> JOIN COLL_PLAN_REV_ DETAIL CP ON CP.COLL_PLAN_ ID = CF.COLL_PLAN_ ID
> AND CP.COLL_PLAN_ REV_NO = CF.COLL_PLAN_ REV_NO
> AND CP.FEE_ID = CF.FEE_ID
> WHERE CF.ACCT_ID = :iAcctID
> AND CF.CASE_ID = :iCaseID
> AND CF.FEE_ID = :iFeeID
> AND CFH.CHANGE_TYPE = 'C'
> AND CAST(CFH.CREATE_ DATE AS DATE) = :dProcessedDate
> INTO :iCaseFeeID, :nAgencyPercent, :nFwdAgencyPercent;
> END
>
> /* 02.11.09 Check for any fee adjustments or back out */
> FOR SELECT CFH.CASE_FEE_ AMT,
> CFH.CHANGE_TYPE
> FROM CASE_FEE_HISTORY CFH
> WHERE CFH.CASE_FEE_ ID = :iCaseFeeID
> INTO :cCaseFeeAmount, :sChangeType DO
> BEGIN
> IF (sChangeType in ('C')) THEN
> cCaseFeeBal = cCaseFeeAmount;
> ELSE IF (sChangeType in ('A')) THEN
> cCaseFeeBal = cCaseFeeBal + cCaseFeeAmount;
> ELSE IF (sChangeType in ('P')) THEN
> cCaseFeePmt = cCaseFeePmt + cCaseFeeAmount;
> ELSE IF (sChangeType in ('B')) THEN
> iFeeBackedOut = 1;
> END
> R_FEE_AMOUNT = cCaseFeeBal;
>
> /* 02.11.09 */
> IF ((iFeeBackedOut = 0) AND (R_FEE_AMOUNT > 0.00)) THEN
> BEGIN
> R_ACTION_CODE = R_FEE_CODE || '-' || sTranCode;
>
> R_DEBTOR1_NAME = NULL;
> R_DEBTOR2_NAME = NULL;
> R_DEBTOR3_NAME = NULL;
>
> R_ACCT_REF_NO = iAcctID || '-' || iCaseID;
>
> /* Build a list of client reference numbers */
> R_CLT_REF_NOS = '';
> FOR SELECT D.CLT_REF_NO
> FROM DEBT D
> WHERE D.ACCT_ID = :iAcctID
> AND D.CASE_ID = :iCaseID
> INTO :sCltRefNo DO
> BEGIN
> if (R_CLT_REF_NOS <> '') then
> R_CLT_REF_NOS = R_CLT_REF_NOS || ', ';
>
> R_CLT_REF_NOS = R_CLT_REF_NOS || :sCltRefNo;
> END
>
> iPersonCount = 0;
>
> FOR SELECT ATDTP.PERSON_ ID
> FROM ACCT_TRAN_DETAIL_ TASK_PERSON ATDTP
> WHERE ATDTP.ACCT_TRAN_ ID = :iAcctTranID
> AND ATDTP.ACCT_TRAN_ DETAIL_ID = :iAcctTranDetailID
> AND ATDTP.ACCT_TASK_ ID = :iAcctTaskID
> INTO :iPersonID DO
> BEGIN
> iPersonCount = iPersonCount + 1;
>
> SELECT P.FIRST_NAME,
> P.MIDDLE_NAME,
> P.LAST_NAME,
> P.SIR_NAME,
> P.DBA_NAME
> FROM PERSON P
> WHERE P.PERSON_ID = :iPersonID
> INTO :sFirstName, :sMiddleName, :sLastName, :sSirName, :sDBAName;
>
> sFullName = '';
>
> IF (sLastName IS NOT NULL) THEN
> sFullName = sLastName;
>
> IF (sSirName IS NOT NULL) THEN
> sFullName = sFullName || ' ' || sSirName;
>
> IF (sFirstName IS NOT NULL) THEN
> sFullName = sFullName || ', ' || sFirstName;
>
> IF (sMiddleName IS NOT NULL) THEN
> sFullName = sFullName || ' ' || sMiddleName;
>
> IF ((sFullName = '') and (sDBAName <> '')) then
> sFullName = sDBAName;
>
> /* 03.26.08 Fetch the Case number */
> sCaseNumber = NULL;
> SELECT ACC.CASE_NUMBER
> FROM ACCT_CASE_COURT_ PERSON ACCP
> JOIN ACCT_CASE_COURT ACC ON ACC.ACCT_CASE_ COURT_ID = ACCP.ACCT_CASE_ COURT_ID
> WHERE ACCP.ACCT_ID = :iAcctID
> AND ACCP.CASE_ID = :iCaseID
> AND ACCP.PERSON_ ID = :iPersonID
> INTO :sCaseNumber;
>
> iServReqAcctTaskID = NULL;
> sServTypeReqCode = NULL;
>
> FOR SELECT ATDTM.ACCT_TASK_ ID,
> STR.SERV_TYPE_ REQ_CODE
> FROM ACCT_TRAN_DETAIL_ TASK_MERGE ATDTM
> JOIN ACCT_TRAN_DETAIL_ TASK ATDT2 ON ATDT2.ACCT_TRAN_ ID = ATDTM.ACCT_TRAN_ ID
> AND ATDT2.ACCT_TRAN_ DETAIL_ID = ATDTM.ACCT_TRAN_ DETAIL_ID
> AND ATDT2.ACCT_TASK_ ID = ATDTM.ACCT_TASK_ ID
> JOIN SERVICE_TYPE_ REQ STR ON STR.SERV_TYPE_ REQ_ID = ATDTM.ROS_SERV_ TYPE_REQ_ ID
> WHERE ATDTM.ACCT_TRAN_ ID = :iAcctTranID
> AND ATDTM.ACCT_TRAN_ DETAIL_ID = :iAcctTranDetailID
> AND ATDTM.ROS_SERV_ TYPE_REQ_ ID IS NOT NULL
> AND ATDTM.PERSON_ ID = :iPersonID
>
> AND ATDT2.QUE_STATUS_ CODE IN ('S','C','W' ,'P')
>
> INTO :iServReqAcctTaskID , :sServTypeReqCode DO
> BEGIN
>
> END
>
> sServiceCounty = NULL;
>
> /* 04.25.08 If the notice has a Service Request, then grab a service address */
> if (iServReqAcctTaskID IS NOT NULL) then
> begin
> /* Code */
> EXECUTE PROCEDURE SPS_FETCH_SERVICE_ ADDRESS(: iServReqAcctTask ID)
> RETURNING_VALUES (:sAddress1, :sAddress2, :sCity, :sServiceCounty,
> :sStateCode, :sZipCode, :sZipCodeSuffix,
> :iCompSelectAddr, :iNoticeSent) ;
> end
>
> if (R_DEBTOR1_NAME IS NULL) then
> begin
> R_DEBTOR1_NAME = sFullName;
> R_DEBTOR1_CASE_ NUMBER = sCaseNumber;
> R_DEBTOR1_SERVICE_ TYPE_REQ_ CODE = :sServTypeReqCode;
> R_DEBTOR1_SERVICE_ COUNTY = :sServiceCounty;
> end
> else if (R_DEBTOR2_NAME IS NULL) then
> begin
> R_DEBTOR2_NAME = sFullName;
> R_DEBTOR2_CASE_ NUMBER = sCaseNumber;
> R_DEBTOR2_SERVICE_ TYPE_REQ_ CODE = :sServTypeReqCode;
> R_DEBTOR2_SERVICE_ COUNTY = :sServiceCounty;
> end
> else if (R_DEBTOR3_NAME IS NULL) then
> begin
> R_DEBTOR3_NAME = sFullName;
> R_DEBTOR3_CASE_ NUMBER = sCaseNumber;
> R_DEBTOR3_SERVICE_ TYPE_REQ_ CODE = :sServTypeReqCode;
> R_DEBTOR3_SERVICE_ COUNTY = :sServiceCounty;
> end
> END
>
> if (iPersonCount = 0) then
> BEGIN
> FOR SELECT P.PERSON_ID,
> P.FIRST_NAME,
> P.MIDDLE_NAME,
> P.LAST_NAME,
> P.SIR_NAME,
> P.DBA_NAME
> FROM ACCT_TRAN_DEBTOR ATD2
> JOIN PERSON P ON P.PERSON_ID = ATD2.PERSON_ ID
> WHERE ACCT_TRAN_ID = :iAcctTranID
> INTO :iPersonID, :sFirstName, :sMiddleName, :sLastName, :sSirName, :sDBAName DO
> BEGIN
> sFullName = '';
>
> IF (sLastName IS NOT NULL) THEN
> sFullName = sLastName;
>
> IF (sSirName IS NOT NULL) THEN
> sFullName = sFullName || ' ' || sSirName;
>
> IF (sFirstName IS NOT NULL) THEN
> sFullName = sFullName || ', ' || sFirstName;
>
> IF (sMiddleName IS NOT NULL) THEN
> sFullName = sFullName || ' ' || sMiddleName;
>
> IF ((sFullName = '') and (sDBAName <> '')) then
> sFullName = sDBAName;
>
> sCaseNumber = NULL;
> SELECT ACC.CASE_NUMBER
> FROM ACCT_CASE_COURT_ PERSON ACCP
> JOIN ACCT_CASE_COURT ACC ON ACC.ACCT_CASE_ COURT_ID = ACCP.ACCT_CASE_ COURT_ID
> WHERE ACCP.ACCT_ID = :iAcctID
> AND ACCP.CASE_ID = :iCaseID
> AND ACCP.PERSON_ ID = :iPersonID
> INTO :sCaseNumber;
>
> iServReqAcctTaskID = NULL;
> sServTypeReqCode = NULL;
>
> FOR SELECT ATDTM.ACCT_TASK_ ID,
> STR.SERV_TYPE_ REQ_CODE
> FROM ACCT_TRAN_DETAIL_ TASK_MERGE ATDTM
> JOIN ACCT_TRAN_DETAIL_ TASK ATDT2 ON ATDT2.ACCT_TRAN_ ID = ATDTM.ACCT_TRAN_ ID
> AND ATDT2.ACCT_TRAN_ DETAIL_ID = ATDTM.ACCT_TRAN_ DETAIL_ID
> AND ATDT2.ACCT_TASK_ ID = ATDTM.ACCT_TASK_ ID
> JOIN SERVICE_TYPE_ REQ STR ON STR.SERV_TYPE_ REQ_ID = ATDTM.ROS_SERV_ TYPE_REQ_ ID
> WHERE ATDTM.ACCT_TRAN_ ID = :iAcctTranID
> AND ATDTM.ACCT_TRAN_ DETAIL_ID = :iAcctTranDetailID
> AND ATDTM.ROS_SERV_ TYPE_REQ_ ID IS NOT NULL
> AND ATDTM.PERSON_ ID = :iPersonID
> AND ATDT2.QUE_STATUS_ CODE IN ('S','C','W' ,'P')
> INTO :iServReqAcctTaskID , :sServTypeReqCode DO
> BEGIN
>
> END
>
> sServiceCounty = NULL;
>
> /* 08.08.08 If the notice has a Service Request, then grab a service address */
> if (iServReqAcctTaskID IS NOT NULL) then
> begin
> EXECUTE PROCEDURE SPS_FETCH_SERVICE_ ADDRESS(: iServReqAcctTask ID)
> RETURNING_VALUES (:sAddress1, :sAddress2, :sCity, :sServiceCounty,
> :sStateCode, :sZipCode, :sZipCodeSuffix,
> :iCompSelectAddr, :iNoticeSent) ;
> end
>
> if (R_DEBTOR1_NAME IS NULL) then
> begin
> R_DEBTOR1_NAME = sFullName;
> R_DEBTOR1_CASE_ NUMBER = sCaseNumber;
> R_DEBTOR1_SERVICE_ TYPE_REQ_ CODE = :sServTypeReqCode;
> R_DEBTOR1_SERVICE_ COUNTY = :sServiceCounty;
> end
> else if (R_DEBTOR2_NAME IS NULL) then
> begin
> R_DEBTOR2_NAME = sFullName;
> R_DEBTOR2_CASE_ NUMBER = sCaseNumber;
> R_DEBTOR2_SERVICE_ TYPE_REQ_ CODE = :sServTypeReqCode;
> R_DEBTOR1_SERVICE_ COUNTY = :sServiceCounty;
> end
> else if (R_DEBTOR3_NAME IS NULL) then
> begin
> R_DEBTOR3_NAME = sFullName;
> R_DEBTOR3_CASE_ NUMBER = sCaseNumber;
> R_DEBTOR3_SERVICE_ TYPE_REQ_ CODE = :sServTypeReqCode;
> R_DEBTOR1_SERVICE_ COUNTY = :sServiceCounty;
> end
> END
> END
>
> /* If a V_NOTICE_ID was passed, then look for this NOTICE_ID inside of the same transaction */
> if (V_NOTICE_ID IS NOT NULL) then
> SELECT ATDT2.PROCESSED_ DATE
> FROM ACCT_TRAN_DETAIL_ TASK_MERGE ATDTM
> JOIN ACCT_TRAN_DETAIL_ TASK ATDT2 ON ATDT2.ACCT_TRAN_ ID = ATDTM.ACCT_TRAN_ ID
> AND ATDT2.ACCT_TRAN_ DETAIL_ID = ATDTM.ACCT_TRAN_ DETAIL_ID
> AND ATDT2.ACCT_TASK_ ID = ATDTM.ACCT_TASK_ ID
> WHERE ATDTM.ACCT_TRAN_ ID = :iAcctTranID
> AND ATDTM.ACCT_TRAN_ DETAIL_ID = :iAcctTranDetailID
> AND ATDTM.NOTICE_ ID = :V_NOTICE_ID
> INTO :R_NOTICE_DATE;
>
> IF (V_CLT_SITE_ OPTION = 1) THEN
> BEGIN
> /* 08.11.08 Determine if this is a pre-paid fee and if so change the R_CLT_SITE_REF_ NO and
> R_CLT_SITE_NAME variables to show the company */
> EXECUTE PROCEDURE SPS_FETCH_FEE_ PAID_BY(iAcctID, iCaseID, R_FEE_CODE)
> RETURNING_VALUES (:sFeePrePaidBy, :sCompanyAbbrev, :sComanyName, :sErrorMsg);
> IF (sFeePrePaidBy = 'A') THEN
> begin
> R_CLT_SITE_REF_ NO = :sCompanyAbbrev;
> R_CLT_SITE_NAME = :sComanyName;
> end
> END
>
> IF (iGovernmentClient = 1) THEN
> R_FEE_PAYABLE_ TO = 'The Courts';
> ELSE IF (nAgencyPercent = 100.00) THEN
> R_FEE_PAYABLE_ TO = 'Agency';
> ELSE IF (nAgencyPercent = 0.00) THEN
> R_FEE_PAYABLE_ TO = 'Client';
>
> /* 02.11.09 */
> IF (cCaseFeeBal - cCaseFeePmt = 0) THEN
> R_FEE_PAID_IN_ FULL = 'PD';
> ELSE
> R_FEE_PAID_IN_ FULL = ' ';
>
> /* 02.11.09 System Fee Only */
> IF ((V_FEE_TYPE_ TO_INCLUDE = 1) AND (R_IMPORTED_ FEE = '*')) THEN
> iOKToInclude = 0;
>
> /* 02.11.09 Imported Fee Only */
> ELSE IF ((V_FEE_TYPE_ TO_INCLUDE = 2) AND (R_IMPORTED_ FEE = ' ')) THEN
> iOKToInclude = 0;
>
> ELSE
> iOKToInclude = 1;
>
> IF ((iOKToInclude = 1) AND
> ((V_EXCLUDE_ GOV_CLTS = 0) OR
> ((V_EXCLUDE_ GOV_CLTS = 1) AND (iGovernmentClient = 0)))) THEN
> SUSPEND;
> END
> END
> end
> ^^
> SET TERM ; ^^















[Non-text portions of this message have been removed]