eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.ISC_EDW_BOOKINGS_F_C SQL Statements

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

Line: 27


  INSERT INTO ISC_EDW_BOOKINGS_FSTG(
	 AGREEMENT_ID,
	 AGREEMENT_TYPE_FK,
	 BILL_TO_CUST_FK,
	 BILL_TO_LOC_FK,
	 BOOKED_DATE,
	 BOOKINGS_PK,
	 CAMPAIGN_ACTL_FK,
	 CAMPAIGN_INIT_FK,
	 CAMPAIGN_STATUS_ACTL_FK,
	 CAMPAIGN_STATUS_INIT_FK,
	 CANCEL_REASON_FK,
	 CONFIGURATION_ITEM_FLAG,
	 CONVERSION_DATE,
	 CONVERSION_RATE,
	 CONVERSION_TYPE,
	 CURRENCY_TRN_FK,
	 CUSTOMER_FK,
	 CUST_PO_NUMBER,
	 DATE_BOOKED_FK,
	 DATE_FULFILLED,
	 DATE_LATEST_PICK,
	 DATE_LATEST_SHIP,
	 DATE_PROMISED_FK,
	 DATE_REQUESTED_FK,
	 DATE_SCHEDULED_FK,
	 DEMAND_CLASS_FK,
	 EVENT_OFFER_ACTL_FK,
	 EVENT_OFFER_INIT_FK,
	 EVENT_OFFER_REG_FK,
	 FULFILLMENT_FLAG,
	 HEADER_ID,
	 INCLUDED_ITEM_FLAG,
	 INSTANCE,
	 INSTANCE_FK,
	 INV_ORG_FK,
	 ITEM_TYPE_CODE,
	 ITEM_ORG_FK,
	 LAST_UPDATE_DATE,
	 LINE_DETAIL_ID,
	 LINE_ID,
	 MARKET_SEGMENT_FK,
	 MEDCHN_ACTL_FK,
	 MEDCHN_INIT_FK,
	 OFFER_HDR_FK,
	 OFFER_LINE_FK,
	 OPERATING_UNIT_FK,
	 ORDER_CATEGORY_FK,
	 ORDER_NUMBER,
	 ORDER_SOURCE_FK,
	 ORDER_TYPE_FK,
	 ORDERED_DATE,
	 PRICE_LIST_ID,
	 PROMISED_DATE,
	 QTY_CANCELLED,
	 QTY_FULFILLED,
	 QTY_INVOICED,
	 QTY_ORDERED,
	 QTY_RESERVED,
	 QTY_RETURNED,
	 QTY_SHIPPED,
	 REQUESTED_DATE,
	 RETURN_REASON_FK,
	 SALES_CHANNEL_FK,
	 SALES_PERSON_FK,
	 SCHEDULED_DATE,
	 SET_OF_BOOKS_FK,
	 SHIPPABLE_FLAG,
	 SHIP_TO_CUST_FK,
	 SHIP_TO_LOC_FK,
	 SOURCE_LIST_FK,
	 TARGET_SEGMENT_ACTL_FK,
	 TARGET_SEGMENT_INIT_FK,
	 TASK_FK,
	 TOP_MODEL_FK,
	 TOTAL_NET_ORDER_VALUE,
	 TRANSACTABLE_FLAG,
	 UNIT_COST_G,
	 UNIT_COST_T,
	 UNIT_LIST_PRC_G,
	 UNIT_LIST_PRC_T,
	 UNIT_SELL_PRC_G,
	 UNIT_SELL_PRC_T,
	 UOM_UOM_FK,
	 USER_ATTRIBUTE1,
	 USER_ATTRIBUTE10,
	 USER_ATTRIBUTE11,
	 USER_ATTRIBUTE12,
	 USER_ATTRIBUTE13,
	 USER_ATTRIBUTE14,
	 USER_ATTRIBUTE15,
	 USER_ATTRIBUTE16,
	 USER_ATTRIBUTE17,
	 USER_ATTRIBUTE18,
	 USER_ATTRIBUTE19,
	 USER_ATTRIBUTE2,
	 USER_ATTRIBUTE20,
	 USER_ATTRIBUTE21,
	 USER_ATTRIBUTE22,
	 USER_ATTRIBUTE23,
	 USER_ATTRIBUTE24,
	 USER_ATTRIBUTE25,
	 USER_ATTRIBUTE3,
	 USER_ATTRIBUTE4,
	 USER_ATTRIBUTE5,
	 USER_ATTRIBUTE6,
	 USER_ATTRIBUTE7,
	 USER_ATTRIBUTE8,
	 USER_ATTRIBUTE9,
	 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 /*+ leading(ISC_EDW_BOOKINGS_F_FCV.ISCBV_BOOKINGS_FCV.ftp) */
         AGREEMENT_ID,
	 AGREEMENT_TYPE_FK,
	 BILL_TO_CUST_FK,
	 BILL_TO_LOC_FK,
	 BOOKED_DATE,
	 BOOKINGS_PK,
	 CAMPAIGN_ACTL_FK,
	 CAMPAIGN_INIT_FK,
	 CAMPAIGN_STATUS_ACTL_FK,
	 CAMPAIGN_STATUS_INIT_FK,
	 CANCEL_REASON_FK,
	 CONFIGURATION_ITEM_FLAG,
	 CONVERSION_DATE,
	 CONVERSION_RATE,
	 CONVERSION_TYPE,
	 CURRENCY_TRN_FK,
	 CUSTOMER_FK,
	 CUST_PO_NUMBER,
	 DATE_BOOKED_FK,
	 DATE_FULFILLED,
	 DATE_LATEST_PICK,
	 DATE_LATEST_SHIP,
	 DATE_PROMISED_FK,
	 DATE_REQUESTED_FK,
	 DATE_SCHEDULED_FK,
	 DEMAND_CLASS_FK,
	 EVENT_OFFER_ACTL_FK,
	 EVENT_OFFER_INIT_FK,
	 EVENT_OFFER_REG_FK,
	 FULFILLMENT_FLAG,
	 HEADER_ID,
	 INCLUDED_ITEM_FLAG,
	 INSTANCE,
	 INSTANCE_FK,
	 INV_ORG_FK,
	 ITEM_TYPE_CODE,
	 ITEM_ORG_FK,
	 LAST_UPDATE_DATE,
	 LINE_DETAIL_ID,
	 LINE_ID,
	 MARKET_SEGMENT_FK,
	 MEDCHN_ACTL_FK,
	 MEDCHN_INIT_FK,
	 OFFER_HDR_FK,
	 OFFER_LINE_FK,
	 OPERATING_UNIT_FK,
	 ORDER_CATEGORY_FK,
	 ORDER_NUMBER,
	 ORDER_SOURCE_FK,
	 ORDER_TYPE_FK,
	 ORDERED_DATE,
	 PRICE_LIST_ID,
	 PROMISED_DATE,
	 QTY_CANCELLED,
	 QTY_FULFILLED,
	 QTY_INVOICED,
	 QTY_ORDERED,
	 QTY_RESERVED,
	 QTY_RETURNED,
	 QTY_SHIPPED,
	 REQUESTED_DATE,
	 RETURN_REASON_FK,
	 SALES_CHANNEL_FK,
	 SALES_PERSON_FK,
	 SCHEDULED_DATE,
	 SET_OF_BOOKS_FK,
	 SHIPPABLE_FLAG,
	 SHIP_TO_CUST_FK,
	 SHIP_TO_LOC_FK,
	 SOURCE_LIST_FK,
	 TARGET_SEGMENT_ACTL_FK,
	 TARGET_SEGMENT_INIT_FK,
	 TASK_FK,
	 TOP_MODEL_FK,
	 TOTAL_NET_ORDER_VALUE,
	 TRANSACTABLE_FLAG,
	 UNIT_COST_G,
	 UNIT_COST_T,
	 UNIT_LIST_PRC_G,
	 UNIT_LIST_PRC_T,
	 UNIT_SELL_PRC_G,
	 UNIT_SELL_PRC_T,
	 UOM_UOM_FK,
	 USER_ATTRIBUTE1,
	 USER_ATTRIBUTE10,
	 USER_ATTRIBUTE11,
	 USER_ATTRIBUTE12,
	 USER_ATTRIBUTE13,
	 USER_ATTRIBUTE14,
	 USER_ATTRIBUTE15,
	 USER_ATTRIBUTE16,
	 USER_ATTRIBUTE17,
	 USER_ATTRIBUTE18,
	 USER_ATTRIBUTE19,
	 USER_ATTRIBUTE2,
	 USER_ATTRIBUTE20,
	 USER_ATTRIBUTE21,
	 USER_ATTRIBUTE22,
	 USER_ATTRIBUTE23,
	 USER_ATTRIBUTE24,
	 USER_ATTRIBUTE25,
	 USER_ATTRIBUTE3,
	 USER_ATTRIBUTE4,
	 USER_ATTRIBUTE5,
	 USER_ATTRIBUTE6,
	 USER_ATTRIBUTE7,
	 USER_ATTRIBUTE8,
	 USER_ATTRIBUTE9,
	 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_BOOKINGS_F_FCV
   WHERE view_type = p_view_type
     AND seq_id    = p_seq_id;

Line: 297


  SELECT isc_tmp_pk_s.nextval
    INTO l_seq_id
    FROM dual;

Line: 311


      INSERT INTO isc_tmp_pk(
	     SEQ_ID,
	     PK1,
	     PK2 )
      SELECT /*+ PARALLEL(h) */
	     l_seq_id,
             to_char(l.line_id),
             to_char(l.line_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 l.header_id = h.header_id
       UNION
      SELECT /*+ PARALLEL(l) */
  	     l_seq_id,
	     to_char(l.line_id),
  	     to_char(l.line_id)
	FROM oe_order_lines_all 	l
       WHERE l.last_update_date BETWEEN g_push_from_date AND g_push_to_date;

Line: 333


  UPDATE ISC_TMP_PK
     SET seq_id = l_seq_id
   WHERE seq_id = -10
     AND pk2 NOT IN (SELECT pk2 FROM ISC_TMP_PK WHERE seq_id = l_seq_id) ;

Line: 340


	  DELETE ISC_TMP_PK
	   WHERE seq_id = -10;

Line: 349


      INSERT INTO isc_tmp_pk(
	     SEQ_ID,
	     PK1,
	     PK2)
      SELECT l_seq_id,
	     mtl.reservation_id,
	     mtl.demand_source_line_id
	FROM isc_tmp_pk		isc,
	     mtl_reservations	mtl
       WHERE isc.PK1 = mtl.demand_source_line_id
	 AND isc.seq_ID = p_parent_seq_id
	 AND mtl.reservation_quantity IS NOT NULL
	 AND mtl.reservation_quantity <> 0
       UNION
      SELECT l_seq_id,
	     mtl.reservation_id,
	     mtl.demand_source_line_id
	FROM mtl_reservations mtl
       WHERE mtl.last_update_date BETWEEN g_push_from_date AND g_push_to_date
	 AND mtl.reservation_quantity IS NOT NULL
	 AND mtl.reservation_quantity <> 0;

Line: 373


  UPDATE ISC_TMP_PK
     SET seq_id = l_seq_id
   WHERE seq_id = -20
     AND pk2 NOT IN (SELECT pk2 FROM ISC_TMP_PK WHERE SEQ_ID = l_seq_id);

Line: 380


  DELETE ISC_TMP_PK
   WHERE seq_id = -20;

Line: 389


      INSERT INTO isc_tmp_pk(
	     SEQ_ID,
	     PK1,
	     PK2)
      SELECT l_seq_id,
	     hist.line_id||to_char(hist.hist_creation_date,'SSSSS'),
	     hist.line_id
	FROM isc_tmp_pk isc,
	     oe_order_lines_history hist
       WHERE isc.PK1 = hist.line_id
	 AND isc.seq_ID = p_parent_seq_id
	 AND hist.hist_type_code = 'CANCELLATION'
       UNION
      SELECT l_seq_id,
	     hist.line_id||to_char(hist.hist_creation_date,'SSSSS'),
	     hist.line_id
	FROM oe_order_lines_history hist
       WHERE hist.last_update_date BETWEEN g_push_from_date AND g_push_to_date
	 AND hist.hist_type_code = 'CANCELLATION';

Line: 411


  UPDATE ISC_TMP_PK
     SET seq_id = l_seq_id
   WHERE seq_id = -30
     AND pk2 NOT IN (SELECT pk2 FROM ISC_TMP_PK WHERE SEQ_ID = l_seq_id);

Line: 418


  DELETE ISC_TMP_PK
   WHERE seq_id = -30;

Line: 442


  INSERT INTO ISC_EDW_BOOK_MISSING_RATE
		( ID,
		  PK1,
		  PK2,
		  CURR_CONV_DATE,
		  FROM_CURRENCY,
		  TO_CURRENCY,
		  RATE_TYPE,
		  FROM_UOM_CODE,
		  TO_UOM_CODE,
		  INVENTORY_ITEM_ID,
		  ITEM_NAME,
		  STATUS)
  SELECT -- Reports Transaction to Base Conversion Currency Issue
	 g_seq_id_line_1			ID,
	 ftp.pk1				PK1,
	 l.line_id				PK2,
  	 decode( upper(h.conversion_type_code), 'USER',
 		 h.conversion_rate_date,
 		 h.booked_date)			CURR_CONV_DATE,
  	 h.transactional_curr_code		FROM_CURRENCY,
  	 gl.currency_code			TO_CURRENCY,
	 nvl(h.conversion_type_code,
	     edw_param.rate_type)		RATE_TYPE,
	 ''					FROM_UOM_CODE,
	 ''					TO_UOM_CODE,
	 ''					INVENTORY_ITEM_ID,
	 ''					ITEM_NAME,
  	 decode( decode( upper(h.conversion_type_code), 'USER',
 			 h.conversion_rate,
 			 decode( h.transactional_curr_code, gl.currency_code,
 				 1,
 				 GL_CURRENCY_API.get_rate_sql(
 					 h.transactional_curr_code,
 					 gl.currency_code,
 					 h.booked_date,
 					 nvl(h.conversion_type_code, edw_param.rate_type)))),
 		 -1,
 		 'RATE NOT AVAILABLE',
 		 -2,
 		 'INVALID CURRENCY')		STATUS
  FROM	 EDW_LOCAL_SYSTEM_PARAMETERS		edw_param,
  	 ISC_TMP_PK				ftp,
  	 OE_ORDER_LINES_ALL			l,
  	 OE_ORDER_HEADERS_ALL			h,
  	 FINANCIALS_SYSTEM_PARAMS_ALL		fspa,
  	 GL_SETS_OF_BOOKS			gl
   WHERE ftp.pk2 = l.line_id
     AND ftp.seq_id = g_seq_id_line_1
     AND l.org_id = fspa.org_id
     AND l.header_id = h.header_id
     AND fspa.set_of_books_id = gl.set_of_books_id
     AND h.booked_flag = 'Y'
     AND h.booked_date IS NOT NULL
     AND decode( upper(h.conversion_type_code), 'USER',
 		 h.conversion_rate,
 		 decode( h.transactional_curr_code, gl.currency_code,
 			 1,
 			 GL_CURRENCY_API.get_rate_sql(
 				 h.transactional_curr_code,
 				 gl.currency_code,
 				 h.booked_date,
 				 nvl(h.conversion_type_code, edw_param.rate_type)))) < 0
  UNION
  SELECT -- Reports Base to Global Conversion Currency Issue
	 g_seq_id_line_1			ID,
	 ftp.pk1				PK1,
  	 l.line_id				PK2,
  	 decode(upper(h.conversion_type_code), 'USER',
 		 h.conversion_rate_date,
 		 h.booked_date)			CURR_CONV_DATE,
  	 gl.currency_code			FROM_CURRENCY,
  	 edw_param.warehouse_currency_code	TO_CURRENCY,
	 nvl(h.conversion_type_code,
	     edw_param.rate_type)		RATE_TYPE,
	 ''					FROM_UOM_CODE,
	 ''					TO_UOM_CODE,
	 ''					INVENTORY_ITEM_ID,
	 ''					ITEM_NAME,
  	 decode( EDW_CURRENCY.Get_Rate (gl.currency_code,h.booked_date),
 		 -1,
 		 'RATE NOT AVAILABLE',
 		 -2,
 		 'INVALID CURRENCY')		STATUS
    FROM EDW_LOCAL_SYSTEM_PARAMETERS		edw_param,
  	 ISC_TMP_PK				ftp,
  	 OE_ORDER_LINES_ALL			l,
  	 OE_ORDER_HEADERS_ALL			h,
  	 FINANCIALS_SYSTEM_PARAMS_ALL		fspa,
  	 GL_SETS_OF_BOOKS			gl
   WHERE ftp.pk2 = l.line_id
     AND ftp.seq_id = g_seq_id_line_1
     AND l.org_id = fspa.org_id
     AND l.header_id = h.header_id
     AND fspa.set_of_books_id = gl.set_of_books_id
     AND h.booked_flag = 'Y'
     AND h.booked_date IS NOT NULL
     AND EDW_CURRENCY.Get_Rate (gl.currency_code,h.booked_date) < 0
  UNION
  SELECT -- Reports Base to Transaction Conversion Currency Issue
	 g_seq_id_line_1			ID,
	 ftp.pk1				PK1,
	 l.line_id				PK2,
  	 decode(upper(h.conversion_type_code), 'USER',
 		 h.conversion_rate_date,
 		 h.booked_date)			CURR_CONV_DATE,
  	 gl.currency_code			FROM_CURRENCY,
  	 h.transactional_curr_code		TO_CURRENCY,
	 nvl(h.conversion_type_code,
	     edw_param.rate_type)		RATE_TYPE,
	 ''					FROM_UOM_CODE,
	 ''					TO_UOM_CODE,
	 ''					INVENTORY_ITEM_ID,
	 ''					ITEM_NAME,
  	 decode( decode( upper(h.conversion_type_code),'USER',
 			 1/ h.conversion_rate,
 			 decode( h.transactional_curr_code, gl.currency_code,
 				 1,
 				 GL_CURRENCY_API.get_rate_sql (
 					     gl.currency_code,
 					     h.transactional_curr_code,
 					     h.booked_date,
 					     nvl(h.conversion_type_code, edw_param.rate_type)))),
 		 -1,
 		 'RATE NOT AVAILABLE',
 		 -2,
 		 'INVALID CURRENCY')		STATUS
    FROM EDW_LOCAL_SYSTEM_PARAMETERS		edw_param,
  	 ISC_TMP_PK				ftp,
  	 OE_ORDER_LINES_ALL			l,
  	 OE_ORDER_HEADERS_ALL			h,
  	 FINANCIALS_SYSTEM_PARAMS_ALL		fspa,
  	 GL_SETS_OF_BOOKS			gl
   WHERE ftp.pk2 = l.line_id
     AND ftp.seq_id = g_seq_id_line_1
     AND l.org_id = fspa.org_id
     AND l.header_id = h.header_id
     AND fspa.set_of_books_id = gl.set_of_books_id
     AND h.booked_flag = 'Y'
     AND h.booked_date IS NOT NULL
     AND decode( upper(h.conversion_type_code),'USER',
 		 1/ h.conversion_rate,
 		 decode( h.transactional_curr_code, gl.currency_code,
 			 1,
 			 GL_CURRENCY_API.get_rate_sql (
 					 gl.currency_code,
 					 h.transactional_curr_code,
 					 h.booked_date,
 					 nvl(h.conversion_type_code, edw_param.rate_type)))) < 0
  UNION
  SELECT -- Reports "Ship from Org Base" to "Header Org Base" Conversion Currency Issue
	 g_seq_id_line_1			ID,
	 ftp.pk1				PK1,
  	 l.line_id				PK2,
  	 h.booked_date				CURR_CONV_DATE,
  	 gl_cost.currency_code			FROM_CURRENCY,
  	 gl.currency_code			TO_CURRENCY,
	 edw_param.rate_type			RATE_TYPE,
	 ''					FROM_UOM_CODE,
	 ''					TO_UOM_CODE,
	 ''					INVENTORY_ITEM_ID,
	 ''					ITEM_NAME,
  	 decode(GL_CURRENCY_API.get_rate_sql (
 			     gl_cost.currency_code,
 			     gl.currency_code,
 			     h.booked_date,
 			     edw_param.rate_type),
 		 -1,
 		 'RATE NOT AVAILABLE',
 		 -2,
 		 'INVALID CURRENCY')		STATUS
    FROM EDW_LOCAL_SYSTEM_PARAMETERS		edw_param,
	 ISC_TMP_PK				ftp,
	 OE_ORDER_LINES_ALL			l,
	 OE_ORDER_HEADERS_ALL			h,
	 FINANCIALS_SYSTEM_PARAMS_ALL		fspa,
	 GL_SETS_OF_BOOKS			gl,
	 GL_SETS_OF_BOOKS			gl_cost,
	 HR_ORGANIZATION_INFORMATION		hoi
   WHERE ftp.pk2 = l.line_id
     AND ftp.seq_id = g_seq_id_line_1
     AND l.org_id = fspa.org_id
     AND l.header_id = h.header_id
     AND fspa.set_of_books_id = gl.set_of_books_id
     AND l.ship_from_org_id = hoi.organization_id -- if ship_from_org_id is null, don't include row in the missing rates
     AND hoi.org_information_context = 'Accounting Information'
     AND hoi.org_information1 = to_char(gl_cost.set_of_books_id)
     AND h.booked_flag = 'Y'
     AND h.booked_date IS NOT NULL
     AND GL_CURRENCY_API.get_rate_sql (
		gl_cost.currency_code,
		gl.currency_code,
		h.booked_date,
		edw_param.rate_type) < 0;

Line: 639


  INSERT INTO ISC_EDW_BOOK_MISSING_RATE(
	 ID,
	 PK1,
	 PK2,
	 CURR_CONV_DATE,
	 FROM_CURRENCY,
	 TO_CURRENCY,
	 RATE_TYPE,
	 FROM_UOM_CODE,
	 TO_UOM_CODE,
	 INVENTORY_ITEM_ID,
	 ITEM_NAME,
	 STATUS)
  SELECT -- Reports UOM Conversion Issue
	 g_seq_id_line_1			ID,
	 ftp.pk1				PK1,
	 l.line_id				PK2,
  	 to_date(NULL)				CURR_CONV_DATE,
  	 ''					FROM_CURRENCY,
  	 ''					TO_CURRENCY,
	 ''					RATE_TYPE,
	 l.order_quantity_uom			FROM_UOM_CODE,
	 EDW_UTIL.Get_Edw_Base_Uom(
		l.order_quantity_uom,
		l.inventory_item_id)		TO_UOM_CODE,
	 l.inventory_item_id			INVENTORY_ITEM_ID,
	 nvl(mtl.segment1,'Item number unavailable')
	   ||' : '||nvl(description,'Description unavailable')
						ITEM_NAME,
  	 'UOM ISSUE'				STATUS
    FROM ISC_TMP_PK				ftp,
  	 OE_ORDER_LINES_ALL			l,
  	 OE_ORDER_HEADERS_ALL			h,
	 MTL_SYSTEM_ITEMS_B			mtl
   WHERE ftp.pk2 = l.line_id
     AND ftp.seq_id = g_seq_id_line_1
     AND l.header_id = h.header_id
     AND h.booked_flag = 'Y'
     AND h.booked_date IS NOT NULL
     AND l.ship_from_org_id = mtl.organization_id
     AND l.inventory_item_id = mtl.inventory_item_id
     AND EDW_UTIL.Get_Uom_Conv_Rate(l.order_quantity_uom,l.inventory_item_id) IS NULL;

Line: 697


FUNCTION Insert_Isc_Tmp_Pk (p_count OUT NOCOPY NUMBER) RETURN NUMBER IS

BEGIN

  p_count := 0 ;

Line: 703


  INSERT INTO ISC_TMP_PK (
	 seq_id,
	 pk1,
	 pk2)
  SELECT -10,
	 ftp.pk1,
	 ftp.pk2
    FROM ISC_EDW_BOOK_MISSING_RATE	conv,
	 ISC_TMP_PK 			ftp
   WHERE conv.pk2 = ftp.pk2
     AND ftp.seq_id = g_seq_id_line_1
     AND conv.id = g_seq_id_line_1 ;

Line: 718


  INSERT INTO ISC_TMP_PK (
	 seq_id,
	 pk1,
	 pk2)
  SELECT -20,
	 ftp.pk1,
	 ftp.pk2
    FROM ISC_EDW_BOOK_MISSING_RATE	conv,
	 ISC_TMP_PK			ftp
   WHERE conv.pk2 = ftp.pk2
     AND ftp.seq_id = g_seq_id_line_2
     AND conv.id = g_seq_id_line_1;

Line: 733


  INSERT INTO ISC_TMP_PK (
	 seq_id,
	 pk1,
	 pk2)
  SELECT -30,
	 ftp.pk1,
	 ftp.pk2
    FROM ISC_EDW_BOOK_MISSING_RATE	conv,
	 ISC_TMP_PK			ftp
   WHERE conv.pk2 = ftp.pk2
     AND ftp.seq_id = g_seq_id_line_3
     AND conv.id = g_seq_id_line_1;

Line: 752


    g_errbuf  := 'Error in Function INSERT_ISC_TMP_PK : '||sqlerrm;

Line: 771


  l_stmt := 'SELECT instance_code FROM EDW_LOCAL_INSTANCE';

Line: 774


  l_stmt := 'INSERT  INTO ISC_EDW_BOOKINGS_FSTG (
	BOOKINGS_PK,
	COLLECTION_STATUS,
	OPERATION_CODE,
  	AGREEMENT_TYPE_FK,
	BILL_TO_CUST_FK,
	BILL_TO_LOC_FK,
	CAMPAIGN_ACTL_FK,
	CAMPAIGN_INIT_FK,
	CAMPAIGN_STATUS_ACTL_FK,
	CAMPAIGN_STATUS_INIT_FK,
	CANCEL_REASON_FK,
	CURRENCY_TRN_FK,
	CUSTOMER_FK,
	DATE_BOOKED_FK,
	DATE_PROMISED_FK,
	DATE_REQUESTED_FK,
	DATE_SCHEDULED_FK,
	DEMAND_CLASS_FK,
	EVENT_OFFER_ACTL_FK,
	EVENT_OFFER_INIT_FK,
	EVENT_OFFER_REG_FK,
	INSTANCE_FK,
	INV_ORG_FK,
	ITEM_ORG_FK,
	MARKET_SEGMENT_FK,
	MEDCHN_ACTL_FK,
	MEDCHN_INIT_FK,
	OFFER_HDR_FK,
	OFFER_LINE_FK,
	OPERATING_UNIT_FK,
	ORDER_CATEGORY_FK,
	ORDER_SOURCE_FK,
	ORDER_TYPE_FK,
	RETURN_REASON_FK,
	SALES_CHANNEL_FK,
	SALES_PERSON_FK,
	SET_OF_BOOKS_FK,
	SHIP_TO_CUST_FK,
	SHIP_TO_LOC_FK,
	SOURCE_LIST_FK,
	TARGET_SEGMENT_ACTL_FK,
	TARGET_SEGMENT_INIT_FK,
	TASK_FK,
	TOP_MODEL_FK,
	UOM_UOM_FK,
	USER_FK1,
	USER_FK2,
	USER_FK3,
	USER_FK4,
	USER_FK5)
SELECT  /*+ INDEX(del ISC_EDW_BOOK_DEL_N1)*/
	del.BOOKINGS_PK,
	''READY'',
	''DELETE'',
 	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW''
  FROM ISC_EDW_BOOK_DEL@'||l_edw_apps_to_wh||' del
 WHERE del.inst_name = '''||l_edw_local_instance|| '''
   AND NOT EXISTS ( SELECT l.line_id
		      FROM OE_ORDER_LINES_ALL l
		     WHERE l.line_id = del.line_id)
UNION ALL
SELECT  /*+ INDEX(del ISC_EDW_BOOK_DEL_N1)*/
	del.BOOKINGS_PK,
	''READY'',
	''DELETE'',
 	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW'',
	''NA_EDW''
  FROM ISC_EDW_BOOK_DEL@'||l_edw_apps_to_wh||' del
 WHERE del.inst_name = '''||l_edw_local_instance|| '''
     	 AND substr(del.bookings_pk,1,3) = ''RES''
   	 AND NOT EXISTS (SELECT demand_source_line_id
     	       	          FROM MTL_RESERVATIONS res
			  WHERE res.demand_source_line_id = del.line_id
			  and del.bookings_pk= ''RES-''||res.reservation_id||''-'||l_edw_local_instance||''')';

Line: 956


FUNCTION Delete_Isc_Tmp_Pk RETURN NUMBER IS

BEGIN

  DELETE FROM ISC_TMP_PK
  	WHERE pk2 IN ( SELECT pk2
			 FROM ISC_EDW_BOOK_MISSING_RATE
		        WHERE id = g_seq_id_line_1 )
	  AND seq_id >0 ;

Line: 970


    g_errbuf  := 'Error in Function Delete_Isc_Tmp_Pk : '||sqlerrm;

Line: 999


 SELECT DISTINCT trunc(curr_conv_date) curr_conv_date,
	from_currency,
 	to_currency,
	rate_type,
 	status
   FROM ISC_EDW_BOOK_MISSING_RATE
  WHERE status NOT IN ('UOM ISSUE')
  ORDER BY status,from_currency,trunc(curr_conv_date);

Line: 1009


 SELECT DISTINCT from_uom_code,
	to_uom_code,
	inventory_item_id item_id,
	substr(item_name,0,50) item_name
   FROM ISC_EDW_BOOK_MISSING_RATE
  WHERE status = 'UOM ISSUE'
  ORDER BY item_name,from_uom_code;

Line: 1122


  FII_UTIL.Print_Timer('Inserted '||g_miss_conv||' rows into the ISC_EDW_BOOK_MISSING_RATE table in ');

Line: 1131


  EDW_LOG.Put_Line('Inserting into ISC_TMP_PK with < 0 seq_id the rows having missing conversion rates (currency and UoM)');

Line: 1135


  g_row_count := Insert_Isc_Tmp_Pk (l_row_count);

Line: 1143


  FII_UTIL.Print_Timer('Inserted '||l_row_count||' rows into the ISC_TMP_PK table in ');

Line: 1156


  g_row_count := Delete_Isc_Tmp_Pk ;

Line: 1164


  FII_UTIL.Print_Timer('Deleted '||g_row_count||' rows from the ISC_TMP_PK table in ');

Line: 1195


	FII_UTIL.Print_Timer('Inserted '||g_row_count||' rows with view type = 1 into the local staging table in ');

Line: 1224


	FII_UTIL.Print_Timer('Inserted '||g_row_count||' rows with view type = 2 into the local staging table in ');

Line: 1248


	FII_UTIL.Print_Timer('Inserted '||g_row_count||' rows with view type = 3 into the local staging table in ');

Line: 1252


	EDW_LOG.Put_Line('Marking rows to be deleted from the Fact (rows having beeing deleted from the source instance table)');

Line: 1265


	FII_UTIL.Print_Timer('Marked '||g_row_count||' rows to be deleted from the Fact in ');

Line: 1276


  DELETE FROM isc_tmp_pk
	WHERE seq_id IN (g_seq_id_line_1, g_seq_id_line_2, g_seq_id_line_3);

Line: 1371


  DELETE FROM ISC_EDW_BOOK_MISSING_RATE
	WHERE id = g_seq_id_line_1;

Line: 1380


  EDW_LOG.Put_Line('Inserted '||g_rows_collected||' rows into the local staging table');