CREATE OR REPLACE FUNCTION WITHIN4SOL(vACCOUNT VARCHAR2, STATEABBV VARCHAR2,
vYEARS INTEGER) RETURN INTEGER IS
–KJP 05-16-2005
–Calculate Statute Of Limitations, the result is a 1
–if the account will still be in statute in 4 months, 0 If not…
SOLDAYS INTEGER := vYears * 365;
–Multiply N years times days to use the days variable
D_DAY DATE := SYSDATE + 120;
–Roughly 4 months from today is the latest it TW USEFUL_DATE DATE;
–Running through the possible values to use for dates.
–This is the value that ends up being the one
–to compare against for SOL calculation
CURSOR C1_CURSOR(vACCOUNT varchar2) IS
SELECT NVL(R.CHGOFF_DATE, SYSDATE – 7500) AS COD,
NVL(R.LAST_PAY_DATE, SYSDATE – 7500) AS LPD, NVL(R.CONTRACT_DATE, SYSDATE – 7500)
as CD, NVL(R.DTE_LST_CR_CHG,SYSDATE – 7500) as LCC FROM
RACCOUNT R WHERE ACCOUNT = vACCOUNT and DIVISION_ID=’PORT’;
C1_RECORD C1_CURSOR%ROWTYPE;
BEGIN
OPEN C1_CURSOR(vACCOUNT);
FETCH C1_CURSOR INTO C1_RECORD;
CASE STATEABBV
–Texas has its own Rules
When ‘TX’ THEN
IF (C1_RECORD.COD + SOLDAYS) > D_DAY then
RETURN 1;
ELSE
RETURN 0;
END IF;
–Every other State but TX
ELSE
– Find the best date
IF C1_RECORD.LCC > C1_RECORD.LPD then
USEFUL_DATE := (C1_RECORD.LCC + SOLDAYS);
END IF;
IF C1_RECORD.LPD > (SYSDATE – 7400) then
USEFUL_DATE := (C1_RECORD.LPD + SOLDAYS);
END IF;
IF C1_RECORD.CD > (SYSDATE – 7400) AND USEFUL_DATE IS NULL then
USEFUL_DATE := (C1_RECORD.CD + SOLDAYS);
END IF;
IF C1_RECORD.COD > (SYSDATE – 7400) AND USEFUL_DATE IS NULL then
USEFUL_DATE := (C1_RECORD.COD + SOLDAYS);
End IF;
IF USEFUL_DATE > D_DAY THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END CASE;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END;
/
