The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE DELETE_STG IS
BEGIN
DELETE ISC_EDW_BOOK_SUM1_FSTG
WHERE COLLECTION_STATUS = 'LOCAL READY'
AND INSTANCE = (SELECT INSTANCE_CODE
FROM EDW_LOCAL_INSTANCE);
SELECT instance_code
INTO l_instance1
FROM edw_local_instance;
SELECT instance_code
INTO l_instance2
FROM edw_local_instance@edw_apps_to_wh;
UPDATE ISC_EDW_BOOK_SUM1_FSTG
SET COLLECTION_STATUS = 'READY'
WHERE COLLECTION_STATUS = 'LOCAL READY'
AND INSTANCE = (SELECT INSTANCE_CODE
FROM EDW_LOCAL_INSTANCE);
INSERT INTO ISC_EDW_BOOK_SUM1_FSTG(
BOOKINGS_PK,
BILL_TO_CUST_FK,
CURRENCY_BASE_FK,
DATE_BOOKED_FK,
INSTANCE_FK,
OPERATING_UNIT_FK,
SET_OF_BOOKS_FK,
BOOKED_AMT_B,
BOOKED_AMT_G,
BOOKED_LIST_AMT_B,
BOOKED_LIST_AMT_G,
FULFILLED_AMT_B,
FULFILLED_AMT_G,
INVOICED_AMT_B,
INVOICED_AMT_G,
SHIPPED_AMT_B,
SHIPPED_AMT_G,
DATE_BOOKED,
DATE_LATEST_FULFILLED,
DATE_LATEST_SHIP,
DATE_ORDERED,
HEADER_ID,
INSTANCE,
ORDER_NUMBER,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
USER_ATTRIBUTE6,
USER_ATTRIBUTE7,
USER_ATTRIBUTE8,
USER_ATTRIBUTE9,
USER_ATTRIBUTE10,
USER_ATTRIBUTE11,
USER_ATTRIBUTE12,
USER_ATTRIBUTE13,
USER_ATTRIBUTE14,
USER_ATTRIBUTE15,
USER_ATTRIBUTE16,
USER_ATTRIBUTE17,
USER_ATTRIBUTE18,
USER_ATTRIBUTE19,
USER_ATTRIBUTE20,
USER_ATTRIBUTE21,
USER_ATTRIBUTE22,
USER_ATTRIBUTE23,
USER_ATTRIBUTE24,
USER_ATTRIBUTE25,
USER_FK1,
USER_FK2,
USER_FK3,
USER_FK4,
USER_FK5,
USER_MEASURE1,
USER_MEASURE2,
USER_MEASURE3,
USER_MEASURE4,
USER_MEASURE5,
OPERATION_CODE,
COLLECTION_STATUS)
SELECT
BOOKINGS_PK,
BILL_TO_CUST_FK,
CURRENCY_BASE_FK,
DATE_BOOKED_FK,
INSTANCE_FK,
OPERATING_UNIT_FK,
SET_OF_BOOKS_FK,
BOOKED_AMT_B,
BOOKED_AMT_G,
BOOKED_LIST_AMT_B,
BOOKED_LIST_AMT_G,
FULFILLED_AMT_B,
FULFILLED_AMT_G,
INVOICED_AMT_B,
INVOICED_AMT_G,
SHIPPED_AMT_B,
SHIPPED_AMT_G,
DATE_BOOKED,
DATE_LATEST_FULFILLED,
DATE_LATEST_SHIP,
DATE_ORDERED,
HEADER_ID,
INSTANCE,
ORDER_NUMBER,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
USER_ATTRIBUTE6,
USER_ATTRIBUTE7,
USER_ATTRIBUTE8,
USER_ATTRIBUTE9,
USER_ATTRIBUTE10,
USER_ATTRIBUTE11,
USER_ATTRIBUTE12,
USER_ATTRIBUTE13,
USER_ATTRIBUTE14,
USER_ATTRIBUTE15,
USER_ATTRIBUTE16,
USER_ATTRIBUTE17,
USER_ATTRIBUTE18,
USER_ATTRIBUTE19,
USER_ATTRIBUTE20,
USER_ATTRIBUTE21,
USER_ATTRIBUTE22,
USER_ATTRIBUTE23,
USER_ATTRIBUTE24,
USER_ATTRIBUTE25,
USER_FK1,
USER_FK2,
USER_FK3,
USER_FK4,
USER_FK5,
USER_MEASURE1,
USER_MEASURE2,
USER_MEASURE3,
USER_MEASURE4,
USER_MEASURE5,
NULL, -- OPERATION_CODE
'LOCAL READY'
FROM ISC_EDW_BOOK_SUM1_F_FCV
WHERE seq_id = p_seq_id;
INSERT INTO ISC_EDW_BOOK_SUM1_FSTG@EDW_APPS_TO_WH(
BOOKINGS_PK,
BILL_TO_CUST_FK,
CURRENCY_BASE_FK,
DATE_BOOKED_FK,
INSTANCE_FK,
OPERATING_UNIT_FK,
SET_OF_BOOKS_FK,
BOOKED_AMT_B,
BOOKED_AMT_G,
BOOKED_LIST_AMT_B,
BOOKED_LIST_AMT_G,
FULFILLED_AMT_B,
FULFILLED_AMT_G,
INVOICED_AMT_B,
INVOICED_AMT_G,
SHIPPED_AMT_B,
SHIPPED_AMT_G,
DATE_BOOKED,
DATE_LATEST_FULFILLED,
DATE_LATEST_SHIP,
DATE_ORDERED,
HEADER_ID,
INSTANCE,
ORDER_NUMBER,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
USER_ATTRIBUTE6,
USER_ATTRIBUTE7,
USER_ATTRIBUTE8,
USER_ATTRIBUTE9,
USER_ATTRIBUTE10,
USER_ATTRIBUTE11,
USER_ATTRIBUTE12,
USER_ATTRIBUTE13,
USER_ATTRIBUTE14,
USER_ATTRIBUTE15,
USER_ATTRIBUTE16,
USER_ATTRIBUTE17,
USER_ATTRIBUTE18,
USER_ATTRIBUTE19,
USER_ATTRIBUTE20,
USER_ATTRIBUTE21,
USER_ATTRIBUTE22,
USER_ATTRIBUTE23,
USER_ATTRIBUTE24,
USER_ATTRIBUTE25,
USER_FK1,
USER_FK2,
USER_FK3,
USER_FK4,
USER_FK5,
USER_MEASURE1,
USER_MEASURE2,
USER_MEASURE3,
USER_MEASURE4,
USER_MEASURE5,
OPERATION_CODE,
COLLECTION_STATUS)
SELECT
BOOKINGS_PK,
BILL_TO_CUST_FK,
CURRENCY_BASE_FK,
DATE_BOOKED_FK,
INSTANCE_FK,
OPERATING_UNIT_FK,
SET_OF_BOOKS_FK,
BOOKED_AMT_B,
BOOKED_AMT_G,
BOOKED_LIST_AMT_B,
BOOKED_LIST_AMT_G,
FULFILLED_AMT_B,
FULFILLED_AMT_G,
INVOICED_AMT_B,
INVOICED_AMT_G,
SHIPPED_AMT_B,
SHIPPED_AMT_G,
DATE_BOOKED,
DATE_LATEST_FULFILLED,
DATE_LATEST_SHIP,
DATE_ORDERED,
HEADER_ID,
INSTANCE,
ORDER_NUMBER,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
USER_ATTRIBUTE6,
USER_ATTRIBUTE7,
USER_ATTRIBUTE8,
USER_ATTRIBUTE9,
USER_ATTRIBUTE10,
USER_ATTRIBUTE11,
USER_ATTRIBUTE12,
USER_ATTRIBUTE13,
USER_ATTRIBUTE14,
USER_ATTRIBUTE15,
USER_ATTRIBUTE16,
USER_ATTRIBUTE17,
USER_ATTRIBUTE18,
USER_ATTRIBUTE19,
USER_ATTRIBUTE20,
USER_ATTRIBUTE21,
USER_ATTRIBUTE22,
USER_ATTRIBUTE23,
USER_ATTRIBUTE24,
USER_ATTRIBUTE25,
NVL(USER_FK1,'NA_EDW'),
NVL(USER_FK2,'NA_EDW'),
NVL(USER_FK3,'NA_EDW'),
NVL(USER_FK4,'NA_EDW'),
NVL(USER_FK5,'NA_EDW'),
USER_MEASURE1,
USER_MEASURE2,
USER_MEASURE3,
USER_MEASURE4,
USER_MEASURE5,
NULL, -- OPERATION_CODE
'READY'
FROM ISC_EDW_BOOK_SUM1_FSTG;
SELECT isc_tmp_book_sum1_s.nextval
INTO l_seq_id
FROM dual;
INSERT
INTO isc_tmp_book_sum1(
PK1,
SEQ_ID)
SELECT /*+ PARALLEL(h) */
distinct to_char(h.header_id),
l_seq_id
FROM oe_order_headers_all h,
oe_order_lines_all l
WHERE h.last_update_date between g_push_from_date AND g_push_to_date
AND h.booked_date between g_push_from_booked_date AND g_push_to_booked_date
AND l.header_id = h.header_id
UNION
SELECT /*+ PARALLEL(l) */
distinct to_char(l.header_id),
l_seq_id
FROM oe_order_lines_all l,
oe_order_headers_all h
WHERE l.last_update_date between g_push_from_date AND g_push_to_date
AND h.booked_date between g_push_from_booked_date AND g_push_to_booked_date
AND l.header_id = h.header_id;
ELSE DELETE_STG;
EDW_LOG.Put_Line('Inserted '||nvl(g_row_count,0)||' rows into the local staging table');
DELETE isc_tmp_book_sum1
WHERE seq_id IN ( l_seq_id_line );
EDW_LOG.Put_Line('Inserted '||nvl(g_row_count,0)||' rows into the staging table');
ROLLBACK; -- Rollback insert into local staging
EDW_LOG.Put_Line('Inserting into local staging has failed : '|| l_exception_msg);
ROLLBACK; -- rollback any insert into remote site
DELETE_STG; -- Delete records in staging with status 'LOCAL READY'
DELETE isc_tmp_book_sum1
WHERE seq_id IN ( l_seq_id_line);