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(:iServReqAcctTaskID)
> 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(:iServReqAcctTaskID)
> 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 ; ^^