I have a stored procedure in Oracle that needs to be converted to
MSSQL and do not understand the sytax enough to tackle this. Is there
anyone that can assist? Heres the procedure.
CREATE PROCEDURE UPD_ACTIVITY IS
CURSOR ACT_cur1
IS
SELECT DISTINCT A.ACCT_NUM, A.DUE_DATE
FROM TBLCACSDAILYACTIVITIES A ; /* this picks the unique
combination of acct and date*/
tot_pay number ;
rec_upd varchar2(1);
todays_date date;
BEGIN
dbms_output.put_line('inside upd_activity ');
SELECT SYSDATE INTO todays_date FROM DUAL;
FOR ACT_CUR_REC1 IN ACT_CUR1
LOOP
rec_upd := 'N';
DECLARE
CURSOR ACT_CUR2 IS
SELECT B.ACCT_NUM,
B.ACTIVITY_DATE,B.PROMISE_AMT_1,B.PROMISE_STATUS,
B.TOTAL_DOLLARS_COLL
FROM TBLCACSDAILYACTIVITIES B
WHERE B.ACCT_NUM = ACT_CUR_REC1.ACCT_NUM
AND B.DUE_DATE = ACT_CUR_REC1.DUE_DATE
ORDER BY B.ACTIVITY_DATE
FOR UPDATE OF B.PROMISE_STATUS,B.TOTAL_DOLLARS_COLL
NOWAIT;
BEGIN
dbms_output.put_line('inside upd_activity1111 ');
FOR ACT_CUR_REC2 IN ACT_CUR2
LOOP
BEGIN
SELECT SUM(C.PAY_AMT) into tot_pay
FROM TBLCACSDAILYPAYMENTS C
WHERE C.ACCT_NUM = ACT_CUR_REC2.ACCT_NUM
AND C.ACTIVITY_DATE >= ACT_CUR_REC2.ACTIVITY_DATE
AND C.ACTIVITY_DATE < ACT_CUR_REC2.ACTIVITY_DATE+10;
dbms_output.put_line('tot_pay =');
IF tot_pay >= .9 * ACT_CUR_REC2.PROMISE_AMT_1 THEN
IF rec_upd = 'N' THEN /* recs have not bee updated for promise
status*/
UPDATE TBLCACSDAILYACTIVITIES
SET PROMISE_STATUS = 'PK',
TOTAL_DOLLARS_COLL = tot_pay
WHERE CURRENT OF ACT_CUR2;
rec_upd := 'Y';
ELSIF rec_upd = 'Y' THEN
UPDATE TBLCACSDAILYACTIVITIES
SET PROMISE_STATUS = 'IP'
WHERE CURRENT OF ACT_CUR2;
END IF;
ELSIF tot_pay < .9 * ACT_CUR_REC2.PROMISE_AMT_1 THEN
IF (ACT_CUR_REC2.ACTIVITY_DATE+10) > todays_date THEN
UPDATE TBLCACSDAILYACTIVITIES
SET PROMISE_STATUS = 'OP'
WHERE CURRENT OF ACT_CUR2;
BREAK;
ELSE
UPDATE TBLCACSDAILYACTIVITIES
SET PROMISE_STATUS = 'PB'
WHERE CURRENT OF ACT_CUR2;
END IF;
END IF;
END;
END LOOP; /* end of ACT_CUR_REC2 loop */
END;
END LOOP; /*end of ACT_CUR_REC1 */
END ; /* end of proc */
/
Received on Fri Aug 27 2004 - 09:07:44 CDT
