42:
43: Cursor For_Cancelled_Closed_MO_lines (p_line_id VARCHAR2) IS
44: SELECT icl.line_id,
45: icl.header_id
46: FROM ic_txn_request_lines icl
47: where icl.line_status = 5
48: and icl.txn_source_line_id = p_line_id;
49:
50: Cursor See_if_inv_interface_is_done (p_line_id VARCHAR2) IS
107: --
108: -- Create the table. If table exists handle the exception.
109: --
110: BEGIN
111: v_sql_stmt := 'CREATE TABLE IC_TXN_REQUEST_LINES_BAK AS SELECT * FROM IC_TXN_REQUEST_LINES WHERE 1=2';
112: EXECUTE IMMEDIATE v_sql_stmt;
113: EXCEPTION
114: WHEN table_already_exists THEN
115: NULL;
153: --
154: -- Archive first.
155: --
156: v_sql_stmt :=
157: 'INSERT INTO IC_TXN_REQUEST_LINES_BAK ' ||
158: '(SELECT * FROM IC_TXN_REQUEST_LINES ' ||
159: ' WHERE line_id = :1) ';
160:
161: EXECUTE IMMEDIATE v_sql_stmt using l_line_id;
154: -- Archive first.
155: --
156: v_sql_stmt :=
157: 'INSERT INTO IC_TXN_REQUEST_LINES_BAK ' ||
158: '(SELECT * FROM IC_TXN_REQUEST_LINES ' ||
159: ' WHERE line_id = :1) ';
160:
161: EXECUTE IMMEDIATE v_sql_stmt using l_line_id;
162:
169:
170: --
171: -- Physically delete.
172: --
173: DELETE FROM ic_txn_request_lines
174: WHERE line_id=l_line_id;
175:
176: DELETE FROM ic_txn_request_headers ich
177: WHERE header_id=l_header_id
174: WHERE line_id=l_line_id;
175:
176: DELETE FROM ic_txn_request_headers ich
177: WHERE header_id=l_header_id
178: AND NOT EXISTS (select 1 from ic_txn_request_lines icl
179: where icl.header_id=ich.header_id);
180: EXCEPTION
181: WHEN others THEN
182: raise continue;