Cursor UNA is throwing this error .. ORA-01001: invalid cursor Error
Can you please please help me find the issue.
CREATE OR REPLACE TRIGGER LOT_DBA.CR_FIELD_CUT_AFTER_INSERT
AFTER INSERT
ON LOT_DBA.CR_FIELD_CUT_STAGING
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
stmt_var VARCHAR2(50);
err_msg VARCHAR2(250);
conn UTL_SMTP.CONNECTION;
crlf VARCHAR2(2) := CHR(13) || CHR(10);
mesg VARCHAR2(2000);
--cc_recipient VARCHAR2(50) DEFAULT 'qwewerhg@XYZ.com';
cc_recipient VARCHAR2(50) DEFAULT 'qwewewr@XYZ.com';--6/24
msg_body VARCHAR2(200) := 'Please check CR_FIELD_ERROR Table for Details. ';
systime DATE;
V_CCI VARCHAR2(50) DEFAULT 'qwewewr@XYZ.com';-- 07/27/2010 Added Contractor Name for CT 2010-4071
CURSOR CUT (pl_no VARCHAR2) IS
SELECT layout_no
FROM CR_FIELD_CUT_SHEET
WHERE LAYOUT_NO = pl_no;
cut_cur CUT%ROWTYPE;
CURSOR LOT (pl_no VARCHAR2) IS
SELECT layout_no,contractor_name -- 07/27/2010 Added Contractor Name for CT 2010-4071
FROM CR_LAYOUT_TRACKING
WHERE LAYOUT_NO = pl_no;
lot_cur LOT%ROWTYPE;
CURSOR DET (pl_no VARCHAR2) IS
SELECT layout_no, cut_no
FROM CR_FIELD_CUT_DETAIL
WHERE LAYOUT_NO = pl_no;
det_cur DET%ROWTYPE;
CURSOR DTN (pl_no VARCHAR2,
pl_ct NUMBER,
pl_nt VARCHAR2) IS
SELECT layout_no, cut_no
FROM CR_FIELD_CUT_DETAIL
WHERE LAYOUT_NO = pl_no
AND CUT_NO = pl_ct
AND NVL(NOTE,'XXXXXXXXX') = pl_nt;
dtn_cur DET%ROWTYPE;
CURSOR ITM (pl_no VARCHAR2) IS
SELECT layout_no, cut_no, seq_no
FROM CR_FIELD_CUT_ITEM
WHERE LAYOUT_NO = pl_no;
itm_cur ITM%ROWTYPE;
CURSOR STAT (pl_no CHAR) IS
SELECT MAX(create_date) cdate
FROM CR_FIELD_LAYOUT_STATUS
WHERE LAYOUT_NO = pl_no
AND STATUS = 'AWAITING_COMPLETION';
stat_cur STAT%ROWTYPE;
CURSOR UNA (pl_no VARCHAR2,
pa_no VARCHAR2) IS
SELECT layout_no, account_no
FROM CR_ACCOUNTS
WHERE LAYOUT_NO = pl_no
AND ACCOUNT_NO = pa_no;--added for cha 2011-3172 6/14/2011
una_cur UNA%ROWTYPE;--added for cha 2011-3172 6/14/2011
BEGIN
systime := SYSDATE;
/* If the record being inserted in the staging table does not
exist in the core table, the record is inserted in the core
table. However, if it exists, then the record in the core
table is deleted and then inserted. */
/* Delete items, then cut details, and finally cut sheet
respectively so as not to raise the foreign key constraint */
---------------
-- Cut Items --
---------------
OPEN ITM(:NEW.LAYOUT_NO);
FETCH ITM
INTO itm_cur;
IF ITM%FOUND THEN
DELETE FROM CR_FIELD_CUT_ITEM
WHERE LAYOUT_NO = :NEW.LAYOUT_NO
AND NVL(NOTE,'XXXXXXXXX') <> :NEW.NOTE;
stmt_var := 'Delete Cut Items';
END IF;
-----------------
-- Cut Details --
-----------------
OPEN DET(:NEW.LAYOUT_NO);
FETCH DET
INTO det_cur;
IF DET%FOUND THEN
DELETE FROM CR_FIELD_CUT_DETAIL
WHERE LAYOUT_NO = :NEW.LAYOUT_NO
AND NVL(NOTE,'XXXXXXXXX') <> :NEW.NOTE;
stmt_var := 'Delete Cut Detail';
END IF;
---------------
-- Cut Sheet --
---------------
OPEN CUT(:NEW.LAYOUT_NO);
FETCH CUT
INTO cut_cur;
IF CUT%FOUND THEN
DELETE FROM CR_FIELD_CUT_SHEET
WHERE LAYOUT_NO = :NEW.LAYOUT_NO ;
stmt_var := 'Delete Cut Sheet';
END IF;
---------------
-- Layout Tracking -- Moved it from below for Change Track 2010-4071 (OpenedByContractor).
---------------
OPEN LOT(:NEW.LAYOUT_NO);
FETCH LOT
INTO lot_cur;
/* This updates the table CR_LAYOUT_TRACKING's columns
House Number into the new value set by the staging table. */
IF LOT%FOUND THEN
UPDATE CR_LAYOUT_TRACKING
SET HOUSE_NUMBER = :NEW.HOUSE_NUMBER
WHERE LAYOUT_NO = lot_cur.LAYOUT_NO;
stmt_var := 'Update Layout Tracking';
END IF;
/* Insert record from staging table to Cut Sheet Table
the complete_status is set to No. ('N')*/
BEGIN
INSERT INTO CR_FIELD_CUT_SHEET
(LAYOUT_NO , PERMIT_TYPE ,
SAWCUT , PLATED ,
COUNTY , STATE ,
WEST_MAP_ID , OPENED_BY_CONTR ,
PROTECTED_ST , COMPASS_POINT ,
STREET_NAME , ARTERY_TYPE ,
LEFT_CROSS_STREET , RIGHT_CROSS_STREET ,
SPECIFIC_LOC , EMER_PERMIT_NO ,
EMER_ISSUE_DATE , SERVICE_TYPE ,
PARKING_RESTR , RESTORE_REQD ,
CREATE_DATE , CREATE_BY ,
NOTE , COMPLETE_STATUS ,
EDIT_FINAL)
VALUES
(:NEW.LAYOUT_NO , :NEW.PERMIT_TYPE ,
:NEW.SAWCUT , :NEW.PLATED ,
:NEW.COUNTY , :NEW.STATE ,
:NEW.WEST_MAP_ID , lot_cur.contractor_name , -- Get contractor Name from cr_layout_tracking
:NEW.PROTECTED_ST , :NEW.COMPASS_POINT ,
:NEW.STREET_NAME , :NEW.ARTERY_TYPE ,
:NEW.LEFT_CROSS_STREET , :NEW.RIGHT_CROSS_STREET ,
:NEW.SPECIFIC_LOC , :NEW.EMER_PERMIT_NO ,
:NEW.EMER_ISSUE_DATE , :NEW.SERVICE_TYPE ,
:NEW.PARKING_RESTR , :NEW.RESTORE_REQD ,
systime , :NEW.CREATE_BY ,
:NEW.NOTE , 'N' ,
:NEW.STATUS );
stmt_var := 'Insert Cut Sheet';
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
NULL;
END;
---------------------
-- Cut Details New --
---------------------
OPEN DTN(:NEW.LAYOUT_NO, :NEW.CUT_NO, :NEW.NOTE);
FETCH DTN
INTO dtn_cur;
IF DTN%NOTFOUND THEN
/** start --added for cha 2011-3172 6/14/2011 **/
OPEN UNA(:NEW.LAYOUT_NO, :NEW.ACCOUNT_NO);
FETCH UNA
INTO una_cur;
---------------------------------
-- CAPTURE UNPROCESSED ACCOUNT --
---------------------------------
IF UNA%NOTFOUND THEN
INSERT INTO UNPROCESSED_ACCOUNTS
(LAYOUT_NO , EMPL_NO ,
ACCOUNT_NO , LOG_DATE ,
COMPLETE_TODAY , NEW_ACCOUNT_NO ,
STATUS , CREATED_BY ,
MODIFIED_BY , CUT_NO ,
CREATE_DATE , MODIFIED_DATE )
VALUES
(:NEW.LAYOUT_NO , NULL ,
:NEW.ACCOUNT_NO , NULL ,
NULL , NULL ,
:NEW.STATUS , :NEW.CREATE_BY ,
:NEW.CREATE_BY , :NEW.CUT_NO ,
:NEW.CREATE_DATE , :NEW.CREATE_DATE );
stmt_var := 'Insert Unprocessed Accounts';
SELECT EMAIL INTO V_CCI FROM C_LOV_CCI WHERE UPPER(NAME) = (SELECT UPPER(CCI)
FROM CR_LAYOUT_TRACKING WHERE layout_no = :NEW.LAYOUT_NO);
IF SQL%NOTFOUND THEN
V_CCI := 'qwewewr@XYZ.com';
END IF;
V_CCI := 'qwewewr@XYZ.com';--6/24
conn:= utl_smtp.open_connection( 'EXCHSMTP.coned.com');
utl_smtp.helo(conn, 'coned.com');
utl_smtp.mail(conn, 'qwewewr@XYZ.com' );
utl_smtp.rcpt(conn, 'qwewewr@XYZ.com' );
utl_smtp.rcpt(conn, V_CCI);
mesg:= 'From: Cut Staging After Insert <qwewewr@XYZ.com>' || crlf ||
'Subject: Unprocessed Account (Testing)' || crlf ||
'To: XYZ <qwewewr@XYZ.com>' || crlf ||
'Cc: ' || V_CCI || crlf ;
mesg:= mesg || '' || crlf || 'Unprocessed account found: '||' Layout No: '|| :NEW.LAYOUT_NO ||
' , Account No: '|| :NEW.ACCOUNT_NO ||' , Status: '|| :NEW.STATUS ||
' , Created By: '|| :NEW.CREATE_BY ||' , Cut No.: '|| :NEW.CUT_NO ||
' , Create Date: '|| :NEW.CREATE_DATE;
utl_smtp.data( conn, mesg );
utl_smtp.quit( conn );
END IF;
/** end --added for cha 2011-3172 6/14/2011 **/
INSERT INTO CR_FIELD_CUT_DETAIL
(LAYOUT_NO , CUT_NO ,
SHALLOW_FACILITIES , CUT_LENGTH ,
CUT_WIDTH , CUT_DEPTH ,
BASE_DEPTH , BASE_MATERIAL ,
SURFACE_DEPTH , SURFACE_MATERIAL ,
LANE , START_POINT ,
LINEAR_START_CUT , LINEAR_CURB_CUT ,
SHAPE , PAVING_REQUIRED ,
OPENED_DATE , EXCAVATION_DATE ,
BACKFILL_DATE , BASE_DATE ,
TRENCHING_DATE , BASE_MAT_REPLACED ,
COMBINED_CUT , CASTING_DEDUCTION ,
ACCOUNT_NO , CREATE_DATE ,
CREATE_BY , NOTE ,
STATUS , REMARK ,
PERMIT_NO)
VALUES
(:NEW.LAYOUT_NO , :NEW.CUT_NO ,
:NEW.SHALLOW_FACILITIES , :NEW.CUT_LENGTH ,
:NEW.CUT_WIDTH , :NEW.CUT_DEPTH ,
:NEW.BASE_DEPTH , :NEW.BASE_MATERIAL ,
:NEW.SURFACE_DEPTH , :NEW.SURFACE_MATERIAL ,
:NEW.LANE , :NEW.START_POINT ,
:NEW.LINEAR_START_CUT , :NEW.LINEAR_CURB_CUT ,
:NEW.SHAPE , :NEW.PAVING_REQUIRED ,
TRUNC(:NEW.OPENED_DATE) , TRUNC(:NEW.EXCAVATION_DATE) ,
TRUNC(:NEW.BACKFILL_DATE) , TRUNC(:NEW.BASE_DATE) ,
TRUNC(:NEW.TRENCHING_DATE) , :NEW.BASE_MAT_REPLACED ,
:NEW.COMBINED_CUT , :NEW.CASTING_DEDUCTION ,
:NEW.ACCOUNT_NO , systime ,
:NEW.CREATE_BY , :NEW.NOTE ,
:NEW.STATUS , :NEW.REMARK ,
:NEW.PERMIT_NO);
stmt_var := 'Insert Cut Detail';
END IF;
/* Insert record from staging table to Cut Item Table */
INSERT INTO CR_FIELD_CUT_ITEM
(LAYOUT_NO , CUT_NO ,
SEQ_NO , CODE ,
LENGTH , WIDTH ,
DEPTH , DATE_WORKED ,
CREATE_DATE , CREATE_BY ,
NOTE , STIP_FACTOR )
VALUES
(:NEW.LAYOUT_NO , :NEW.CUT_NO ,
:NEW.SEQ_NO , :NEW.CODE ,
:NEW.LENGTH , :NEW.WIDTH ,
:NEW.DEPTH , TRUNC(:NEW.DATE_WORKED) ,
systime , :NEW.CREATE_BY ,
:NEW.NOTE , :NEW.STIP_FACTOR );
stmt_var := 'Insert Cut Items';
/* This inserts record from staging to history table. */
BEGIN
INSERT INTO CR_FIELD_CUT_HISTORY
(LAYOUT_NO , PERMIT_TYPE ,
PO_NUMBER , HOUSE_NUMBER ,
SAWCUT , PLATED ,
COUNTY , STATE ,
WEST_MAP_ID , OPENED_BY_CONTR ,
PROTECTED_ST , COMPASS_POINT ,
STREET_NAME , ARTERY_TYPE ,
LEFT_CROSS_STREET , RIGHT_CROSS_STREET ,
SPECIFIC_LOC , EMER_PERMIT_NO ,
EMER_ISSUE_DATE , SERVICE_TYPE ,
PARKING_RESTR , RESTORE_REQD ,
CUT_NO , SHALLOW_FACILITIES ,
CUT_LENGTH , CUT_WIDTH ,
CUT_DEPTH , BASE_DEPTH ,
BASE_MATERIAL , SURFACE_DEPTH ,
SURFACE_MATERIAL , LANE ,
START_POINT , LINEAR_START_CUT ,
LINEAR_CURB_CUT , SHAPE ,
PAVING_REQUIRED , OPENED_DATE ,
EXCAVATION_DATE , BACKFILL_DATE ,
BASE_DATE , TRENCHING_DATE ,
BASE_MAT_REPLACED , COMBINED_CUT ,
CASTING_DEDUCTION , ACCOUNT_NO ,
SEQ_NO , CODE ,
LENGTH , WIDTH ,
DEPTH , DATE_WORKED ,
CREATE_DATE , CREATE_BY ,
NOTE , STATUS ,
PERMIT_NO , STIP_FACTOR )
VALUES
(:NEW.LAYOUT_NO , :NEW.PERMIT_TYPE ,
:NEW.PO_NUMBER , :NEW.HOUSE_NUMBER ,
:NEW.SAWCUT , :NEW.PLATED ,
:NEW.COUNTY , :NEW.STATE ,
:NEW.WEST_MAP_ID , lot_cur.contractor_name , -- Get it from LOT Change Track 2010-4071
:NEW.PROTECTED_ST , :NEW.COMPASS_POINT ,
:NEW.STREET_NAME , :NEW.ARTERY_TYPE ,
:NEW.LEFT_CROSS_STREET , :NEW.RIGHT_CROSS_STREET ,
:NEW.SPECIFIC_LOC , :NEW.EMER_PERMIT_NO ,
TRUNC(:NEW.EMER_ISSUE_DATE) , :NEW.SERVICE_TYPE ,
:NEW.PARKING_RESTR , :NEW.RESTORE_REQD ,
:NEW.CUT_NO , :NEW.SHALLOW_FACILITIES ,
:NEW.CUT_LENGTH , :NEW.CUT_WIDTH ,
:NEW.CUT_DEPTH , :NEW.BASE_DEPTH ,
:NEW.BASE_MATERIAL , :NEW.SURFACE_DEPTH ,
:NEW.SURFACE_MATERIAL , :NEW.LANE ,
:NEW.START_POINT , :NEW.LINEAR_START_CUT ,
:NEW.LINEAR_CURB_CUT , :NEW.SHAPE ,
:NEW.PAVING_REQUIRED , TRUNC(:NEW.OPENED_DATE) ,
TRUNC(:NEW.EXCAVATION_DATE) , TRUNC(:NEW.BACKFILL_DATE) ,
TRUNC(:NEW.BASE_DATE) , TRUNC(:NEW.TRENCHING_DATE) ,
:NEW.BASE_MAT_REPLACED , :NEW.COMBINED_CUT ,
:NEW.CASTING_DEDUCTION , :NEW.ACCOUNT_NO ,
:NEW.SEQ_NO , :NEW.CODE ,
:NEW.LENGTH , :NEW.WIDTH ,
:NEW.DEPTH , TRUNC(:NEW.DATE_WORKED) ,
systime , :NEW.CREATE_BY ,
'PROCESSED '||TO_CHAR(systime , 'MM/DD/YYYY HH:MI:SS'),
:NEW.STATUS , :NEW.PERMIT_NO ,
:NEW.STIP_FACTOR );
stmt_var := 'Insert Cut History';
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
NULL;
END;
/* This will insert/update the records in CR_FIELD_LAYOUT_STATUS */
OPEN STAT(:NEW.LAYOUT_NO);
FETCH STAT INTO STAT_CUR;
IF stat_cur.cdate IS NULL THEN
/* Offset system date inserted by 1 second (SYSDATE+.00001) to prevent PK_LOT_STAT
constraint from firing when the cuts are entered simultaneously with logs. */
INSERT INTO CR_FIELD_LAYOUT_STATUS
(LAYOUT_NO, STATUS, CREATE_BY, CREATE_DATE, NOTE)
VALUES (:NEW.LAYOUT_NO, 'AWAITING_COMPLETION', :NEW.CREATE_BY, SYSDATE+.00001,
'Cut Inserted by Field Personnel '|| to_char(SYSDATE+.00001,'MM/DD/YYYY HH:MI:SS AM'));
stmt_var := 'Insert Layout Status';
ELSE
UPDATE CR_FIELD_LAYOUT_STATUS
SET CREATE_DATE = SYSDATE
, NOTE = 'Cuts Updated: '|| to_char(SYSDATE+.00001,'MM/DD/YYYY HH:MI:SS AM') -- CT 2009-5511 09/10/09 Removed concatenation
WHERE LAYOUT_NO = :NEW.LAYOUT_NO
AND STATUS = 'AWAITING_COMPLETION'
AND CREATE_DATE = stat_cur.cdate;
stmt_var := 'Update Layout Status';
END IF;
CLOSE CUT;
CLOSE DET;
CLOSE DTN;
CLOSE ITM;
CLOSE LOT;
CLOSE STAT;
CLOSE UNA;--added for cha 2011-3172 6/14/2011
/* When exceptions are raised, the users will be notified for
the failure. */
EXCEPTION WHEN OTHERS THEN
err_msg := SQLERRM;
INSERT INTO CR_FIELD_ERROR VALUES(:NEW.LAYOUT_NO, ' Cut No.: '||:NEW.CUT_NO
||' Item No.: '||:NEW.SEQ_NO, SYSDATE, err_msg);
/* conn:= utl_smtp.open_connection( 'EXCHSMTP.coned.com');
utl_smtp.helo(conn, 'coned.com');
utl_smtp.mail(conn, 'qwewerhg@XYZ.com' );
utl_smtp.rcpt(conn, 'qwewerhg@XYZ.com' );
utl_smtp.rcpt(conn, cc_recipient);
mesg:= 'From: Cut Staging After Insert <qwewerhg@XYZ.com>' || crlf ||
'Subject: CR_FIELD_CUT_AFTER_INSERT Trigger Failed (Testing)' || crlf ||
'To: Harsha <qwewerhg@XYZ.com>' || crlf ||
'Cc: ' || cc_recipient || crlf ;
mesg:= mesg || '' || crlf || msg_body || ' Layout No.: '|| :NEW.LAYOUT_NO ||
' Cut No.: ' || :NEW.CUT_NO || ' Item No.: ' || :NEW.SEQ_NO ||
' **ErrorMessage** '||err_msg||' Error found after this statement: '||stmt_var;
utl_smtp.data( conn, mesg );
utl_smtp.quit( conn );*/ --6/24
conn:= utl_smtp.open_connection( 'EXCHSMTP.coned.com');
utl_smtp.helo(conn, 'coned.com');
utl_smtp.mail(conn, 'qwewewr@XYZ.com' );
utl_smtp.rcpt(conn, 'qwewewr@XYZ.com' );
utl_smtp.rcpt(conn, cc_recipient);
mesg:= 'From: Cut Staging After Insert <qwewewr@XYZ.com>' || crlf ||
'Subject: CR_FIELD_CUT_AFTER_INSERT Trigger Failed (Testing)' || crlf ||
'To: XYZ <qwewewr@XYZ.com>' || crlf ||
'Cc: ' || cc_recipient || crlf ;
mesg:= mesg || '' || crlf || msg_body || ' Layout No.: '|| :NEW.LAYOUT_NO ||
' Cut No.: ' || :NEW.CUT_NO || ' Item No.: ' || :NEW.SEQ_NO ||
' **ErrorMessage** '||err_msg||' Error found after this statement: '||stmt_var;
utl_smtp.data( conn, mesg );
utl_smtp.quit( conn );
END;
Edited by: 831050 on Jul 20, 2011 6:50 AM