I am in a crunch and need to covert this Oracle statement to MSSQL. Is
there any Oracle/MSSQL experts out there that can help me? I do not
understand the syntax enough to modify this.
Thanks so much for any assistance. Here is 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:05:59 CDT
