DBA Data[Home] [Help]

APPS.ISC_EDW_BOOK_SUM1_F_C SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 35

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);
Line: 55

	SELECT instance_code
   	INTO   l_instance1
   	FROM   edw_local_instance;
Line: 59

   	SELECT instance_code
   	INTO   l_instance2
   	FROM   edw_local_instance@edw_apps_to_wh;
Line: 85

   	UPDATE ISC_EDW_BOOK_SUM1_FSTG
   	SET    COLLECTION_STATUS = 'READY'
   	WHERE  COLLECTION_STATUS = 'LOCAL READY'
   	AND    INSTANCE = (SELECT INSTANCE_CODE
                     	   FROM EDW_LOCAL_INSTANCE);
Line: 119

	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;
Line: 264

	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;
Line: 416

	SELECT isc_tmp_book_sum1_s.nextval
	INTO l_seq_id
	FROM dual;
Line: 425

	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;
Line: 534

	ELSE   	DELETE_STG;
Line: 574

      	EDW_LOG.Put_Line('Inserted '||nvl(g_row_count,0)||' rows into the local staging table');
Line: 584

      	DELETE isc_tmp_book_sum1
      	WHERE seq_id IN ( l_seq_id_line );
Line: 647

      	EDW_LOG.Put_Line('Inserted '||nvl(g_row_count,0)||' rows into the staging table');
Line: 668

      	ROLLBACK;   -- Rollback insert into local staging
Line: 669

      	EDW_LOG.Put_Line('Inserting into local staging has failed : '|| l_exception_msg);
Line: 682

      	ROLLBACK;      -- rollback any insert into remote site
Line: 698

      	DELETE_STG;    -- Delete records in staging with status 'LOCAL READY'
Line: 713

      	DELETE isc_tmp_book_sum1
      	WHERE seq_id IN ( l_seq_id_line);