APPS.POA_EDW_SUP_PERF_F_C SQL Statements

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

Line: 41

PROCEDURE INSERT_MISSING_RATES
IS
 BEGIN
   INSERT INTO poa_edw_sup_perf_inc(primary_key)
   SELECT  TO_NUMBER(SUBSTR(sup_perf_pk, 1, INSTR(sup_perf_pk, '-' )-1))
   FROM  POA_EDW_SUP_PERF_FSTG
   where COLLECTION_STATUS = 'RATE NOT AVAILABLE' OR
         COLLECTION_STATUS = 'INVALID CURRENCY';

Line: 56

      edw_log.put_line('INSERTING ' || to_char(sql%rowcount) ||
           ' rows into poa_edw_sup_perf_inc table');

Line: 65

 PROCEDURE DELETE_STG_MISSING_RATES
 IS
 BEGIN
   DELETE FROM POA_EDW_SUP_PERF_FSTG
   WHERE  COLLECTION_STATUS = 'RATE NOT AVAILABLE'
      OR COLLECTION_STATUS = 'INVALID CURRENCY'
     AND    INSTANCE_FK = (SELECT INSTANCE_CODE
                        FROM   EDW_LOCAL_INSTANCE);

Line: 100

   When the value is 'Yes' (the first part of the IF statment), then we SELECT
   target_price from the source view which then calls the (expensive) API
   poa_edw_supperf.find_best_price.
   Otherwise, when the value is 'No' (the second part of the IF statment),
   we populate target_price by NULL, the API won't be called in the
   fact source view */


 IF(fnd_profile.value('POA_TARGET_PRICE_TXN') = 'Y') then

   edw_log.put_line('***The best price is calculated for target prices***');

Line: 112

   Insert Into POA_EDW_SUP_PERF_FSTG(
     DUNS_FK,
     UNSPSC_FK,
     SIC_CODE_FK,
     AMT_PURCHASED_G,
     AMT_PURCHASED_T,
     APPROVAL_DATE_FK,
     AP_TERMS_FK,
     BUYER_FK,
     CLOSED_CODE_FK,
     CONTRACT_NUM,
     CREATION_DATE_FK,
     DATE_DIM_FK,
     DAYS_EARLY_REC,
     DAYS_LATE_REC,
     EDW_BASE_UOM_FK,
     EDW_UOM_FK,
     FIRST_REC_DATE_FK,
     INSTANCE_FK,
     INVOICE_DATE_FK,
     IPV_G,
     IPV_T,
     ITEM_FK,
     LIST_PRICE_G,
     LIST_PRICE_T,
     LST_ACCPT_DATE_FK,
     MARKET_PRICE_G,
     MARKET_PRICE_T,
     NEED_BY_DATE_FK,
     NUM_DAYS_TO_INVOICE,
     NUM_EARLY_RECEIPT,
     NUM_LATE_RECEIPT,
     NUM_ONTIME_AFTDUE,
     NUM_ONTIME_BEFDUE,
     NUM_ONTIME_ONDUE,
     NUM_RECEIPT_LINES,
     NUM_SUBS_RECEIPT,
     PO_LINE_TYPE_FK,
     PO_NUMBER,
     PRICE_G,
     PRICE_T,
     PRICE_TYPE_FK,
     PROMISED_DATE_FK,
     PURCH_CLASS_FK,
     QTY_ACCEPTED_B,
     QTY_CANCELLED_B,
     QTY_DELIVERED_B,
     QTY_EARLY_RECEIPT_B,
     QTY_LATE_RECEIPT_B,
     QTY_ONTIME_AFTDUE_B,
     QTY_ONTIME_BEFDUE_B,
     QTY_ONTIME_ONDUE_B,
     QTY_ORDERED_B,
     QTY_PAST_DUE_B,
     QTY_RECEIVED_B,
     QTY_RECEIVED_TOL,
     QTY_REJECTED_B,
     QTY_SHIPPED_B,
     QTY_SUBS_RECEIPT_B,
     RCV_CLOSE_TOL,
     RELEASE_NUM,
     SHIP_LOCATION_FK,
     SHIP_TO_ORG_FK,
     SUPPLIER_ITEM_FK,
     SUPPLIER_SITE_FK,
     SUP_PERF_PK,
     SUP_SITE_GEOG_FK,
     TARGET_PRICE_G,
     TARGET_PRICE_T,
     TXN_CUR_CODE_FK,
     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_FK1,
     USER_FK2,
     USER_FK3,
     USER_FK4,
     USER_FK5,
     USER_MEASURE1,
     USER_MEASURE2,
     USER_MEASURE3,
     USER_MEASURE4,
     USER_MEASURE5,
     OPERATION_CODE,
     COLLECTION_STATUS)
   select
     NVL(DUNS_FK, 'NA_EDW'),
     NVL(UNSPSC_FK, 'NA_EDW'),
     NVL(SIC_CODE_FK, 'NA_EDW'),
     AMT_PURCHASED_G,
     AMT_PURCHASED_T,
     NVL(APPROVAL_DATE_FK,'NA_EDW'),
     NVL(AP_TERMS_FK,'NA_EDW'),
     NVL(BUYER_FK,'NA_EDW'),
     NVL(CLOSED_CODE_FK,'NA_EDW'),
     CONTRACT_NUM,
     NVL(CREATION_DATE_FK,'NA_EDW'),
     NVL(DATE_DIM_FK,'NA_EDW'),
     DAYS_EARLY_REC,
     DAYS_LATE_REC,
     NVL(EDW_BASE_UOM_FK,'NA_EDW'),
     NVL(EDW_UOM_FK,'NA_EDW'),
     NVL(FIRST_REC_DATE_FK,'NA_EDW'),
     NVL(INSTANCE_FK,'NA_EDW'),
     NVL(INVOICE_DATE_FK,'NA_EDW'),
     IPV_G,
     IPV_T,
     NVL(ITEM_FK,'NA_EDW'),
     LIST_PRICE_G,
     LIST_PRICE_T,
     NVL(LST_ACCPT_DATE_FK,'NA_EDW'),
     MARKET_PRICE_G,
     MARKET_PRICE_T,
     NVL(NEED_BY_DATE_FK,'NA_EDW'),
     NUM_DAYS_TO_INVOICE,
     NUM_EARLY_RECEIPT,
     NUM_LATE_RECEIPT,
     NUM_ONTIME_AFTDUE,
     NUM_ONTIME_BEFDUE,
     NUM_ONTIME_ONDUE,
     NUM_RECEIPT_LINES,
     NUM_SUBS_RECEIPT,
     NVL(PO_LINE_TYPE_FK,'NA_EDW'),
     PO_NUMBER,
     PRICE_G,
     PRICE_T,
     NVL(PRICE_TYPE_FK,'NA_EDW'),
     NVL(PROMISED_DATE_FK,'NA_EDW'),
     NVL(PURCH_CLASS_FK,'NA_EDW'),
     QTY_ACCEPTED_B,
     QTY_CANCELLED_B,
     QTY_DELIVERED_B,
     QTY_EARLY_RECEIPT_B,
     QTY_LATE_RECEIPT_B,
     QTY_ONTIME_AFTDUE_B,
     QTY_ONTIME_BEFDUE_B,
     QTY_ONTIME_ONDUE_B,
     QTY_ORDERED_B,
     QTY_PAST_DUE_B,
     QTY_RECEIVED_B,
     QTY_RECEIVED_TOL,
     QTY_REJECTED_B,
     QTY_SHIPPED_B,
     QTY_SUBS_RECEIPT_B,
     RCV_CLOSE_TOL,
     RELEASE_NUM,
     NVL(SHIP_LOCATION_FK,'NA_EDW'),
     NVL(SHIP_TO_ORG_FK,'NA_EDW'),
     NVL(SUPPLIER_ITEM_FK,'NA_EDW'),
     NVL(SUPPLIER_SITE_FK,'NA_EDW'),
     SUP_PERF_PK,
     NVL(SUP_SITE_GEOG_FK,'NA_EDW'),
     TARGET_PRICE_G,
     TARGET_PRICE_T,
     NVL(TXN_CUR_CODE_FK,'NA_EDW'),
     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,
     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
     COLLECTION_STATUS
   FROM POA_EDW_SUPPLIER_PERFORM_FCV
   WHERE view_id   = p_view_id
   AND   seq_id    = p_seq_id;

Line: 314

   Insert Into POA_EDW_SUP_PERF_FSTG(
     DUNS_FK,
     UNSPSC_FK,
     SIC_CODE_FK,
     AMT_PURCHASED_G,
     AMT_PURCHASED_T,
     APPROVAL_DATE_FK,
     AP_TERMS_FK,
     BUYER_FK,
     CLOSED_CODE_FK,
     CONTRACT_NUM,
     CREATION_DATE_FK,
     DATE_DIM_FK,
     DAYS_EARLY_REC,
     DAYS_LATE_REC,
     EDW_BASE_UOM_FK,
     EDW_UOM_FK,
     FIRST_REC_DATE_FK,
     INSTANCE_FK,
     INVOICE_DATE_FK,
     IPV_G,
     IPV_T,
     ITEM_FK,
     LIST_PRICE_G,
     LIST_PRICE_T,
     LST_ACCPT_DATE_FK,
     MARKET_PRICE_G,
     MARKET_PRICE_T,
     NEED_BY_DATE_FK,
     NUM_DAYS_TO_INVOICE,
     NUM_EARLY_RECEIPT,
     NUM_LATE_RECEIPT,
     NUM_ONTIME_AFTDUE,
     NUM_ONTIME_BEFDUE,
     NUM_ONTIME_ONDUE,
     NUM_RECEIPT_LINES,
     NUM_SUBS_RECEIPT,
     PO_LINE_TYPE_FK,
     PO_NUMBER,
     PRICE_G,
     PRICE_T,
     PRICE_TYPE_FK,
     PROMISED_DATE_FK,
     PURCH_CLASS_FK,
     QTY_ACCEPTED_B,
     QTY_CANCELLED_B,
     QTY_DELIVERED_B,
     QTY_EARLY_RECEIPT_B,
     QTY_LATE_RECEIPT_B,
     QTY_ONTIME_AFTDUE_B,
     QTY_ONTIME_BEFDUE_B,
     QTY_ONTIME_ONDUE_B,
     QTY_ORDERED_B,
     QTY_PAST_DUE_B,
     QTY_RECEIVED_B,
     QTY_RECEIVED_TOL,
     QTY_REJECTED_B,
     QTY_SHIPPED_B,
     QTY_SUBS_RECEIPT_B,
     RCV_CLOSE_TOL,
     RELEASE_NUM,
     SHIP_LOCATION_FK,
     SHIP_TO_ORG_FK,
     SUPPLIER_ITEM_FK,
     SUPPLIER_SITE_FK,
     SUP_PERF_PK,
     SUP_SITE_GEOG_FK,
     TARGET_PRICE_G,
     TARGET_PRICE_T,
     TXN_CUR_CODE_FK,
     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_FK1,
     USER_FK2,
     USER_FK3,
     USER_FK4,
     USER_FK5,
     USER_MEASURE1,
     USER_MEASURE2,
     USER_MEASURE3,
     USER_MEASURE4,
     USER_MEASURE5,
     OPERATION_CODE,
     COLLECTION_STATUS)
   select
     NVL(DUNS_FK, 'NA_EDW'),
     NVL(UNSPSC_FK, 'NA_EDW'),
     NVL(SIC_CODE_FK, 'NA_EDW'),
     AMT_PURCHASED_G,
     AMT_PURCHASED_T,
     NVL(APPROVAL_DATE_FK,'NA_EDW'),
     NVL(AP_TERMS_FK,'NA_EDW'),
     NVL(BUYER_FK,'NA_EDW'),
     NVL(CLOSED_CODE_FK,'NA_EDW'),
     CONTRACT_NUM,
     NVL(CREATION_DATE_FK,'NA_EDW'),
     NVL(DATE_DIM_FK,'NA_EDW'),
     DAYS_EARLY_REC,
     DAYS_LATE_REC,
     NVL(EDW_BASE_UOM_FK,'NA_EDW'),
     NVL(EDW_UOM_FK,'NA_EDW'),
     NVL(FIRST_REC_DATE_FK,'NA_EDW'),
     NVL(INSTANCE_FK,'NA_EDW'),
     NVL(INVOICE_DATE_FK,'NA_EDW'),
     IPV_G,
     IPV_T,
     NVL(ITEM_FK,'NA_EDW'),
     LIST_PRICE_G,
     LIST_PRICE_T,
     NVL(LST_ACCPT_DATE_FK,'NA_EDW'),
     MARKET_PRICE_G,
     MARKET_PRICE_T,
     NVL(NEED_BY_DATE_FK,'NA_EDW'),
     NUM_DAYS_TO_INVOICE,
     NUM_EARLY_RECEIPT,
     NUM_LATE_RECEIPT,
     NUM_ONTIME_AFTDUE,
     NUM_ONTIME_BEFDUE,
     NUM_ONTIME_ONDUE,
     NUM_RECEIPT_LINES,
     NUM_SUBS_RECEIPT,
     NVL(PO_LINE_TYPE_FK,'NA_EDW'),
     PO_NUMBER,
     PRICE_G,
     PRICE_T,
     NVL(PRICE_TYPE_FK,'NA_EDW'),
     NVL(PROMISED_DATE_FK,'NA_EDW'),
     NVL(PURCH_CLASS_FK,'NA_EDW'),
     QTY_ACCEPTED_B,
     QTY_CANCELLED_B,
     QTY_DELIVERED_B,
     QTY_EARLY_RECEIPT_B,
     QTY_LATE_RECEIPT_B,
     QTY_ONTIME_AFTDUE_B,
     QTY_ONTIME_BEFDUE_B,
     QTY_ONTIME_ONDUE_B,
     QTY_ORDERED_B,
     QTY_PAST_DUE_B,
     QTY_RECEIVED_B,
     QTY_RECEIVED_TOL,
     QTY_REJECTED_B,
     QTY_SHIPPED_B,
     QTY_SUBS_RECEIPT_B,
     RCV_CLOSE_TOL,
     RELEASE_NUM,
     NVL(SHIP_LOCATION_FK,'NA_EDW'),
     NVL(SHIP_TO_ORG_FK,'NA_EDW'),
     NVL(SUPPLIER_ITEM_FK,'NA_EDW'),
     NVL(SUPPLIER_SITE_FK,'NA_EDW'),
     SUP_PERF_PK,
     NVL(SUP_SITE_GEOG_FK,'NA_EDW'),
     to_number(NULL), --TARGET_PRICE_G ,
     to_number(NULL), --TARGET_PRICE_T,
     NVL(TXN_CUR_CODE_FK,'NA_EDW'),
     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,
     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
     COLLECTION_STATUS
   FROM POA_EDW_SUPPLIER_PERFORM_FCV
   WHERE view_id   = p_view_id
   AND   seq_id    = p_seq_id;

Line: 530

 PROCEDURE DELETE_DUPLICATES IS

  -- Cursor to delete duplicates
  CURSOR Dup_Rec IS
        SELECT primary_key
         FROM poa_edw_sup_perf_inc
        ORDER BY primary_key
  FOR UPDATE;

Line: 552

          DELETE FROM poa_edw_sup_perf_inc
          WHERE CURRENT OF Dup_Rec;

Line: 571

 FUNCTION INSERT_RCPT(p_seq_id       IN NUMBER)
 RETURN NUMBER
 IS

  l_count  NUMBER;

Line: 578

  insert into poa_edw_sup_perf_inc(primary_key, seq_id)
   select rcv.po_line_location_id, p_seq_id
   from rcv_transactions rcv, po_line_locations_all pll
   where rcv.po_line_location_id = pll.line_location_id
   and rcv.last_update_date between g_push_from_date and
       g_push_to_date
   group by rcv.po_line_location_id
   having max(rcv.last_update_date) between g_push_from_date
       and g_push_to_date;

Line: 615

   select poa_edw_sup_perf_inc_s.nextval into l_seq_id from dual;

Line: 617

   /** Update the seq_id for records that had missing currency rates in
       the earlier PUSH. We need to repush these records again
    **/

        UPDATE poa_edw_sup_perf_inc
        SET seq_id = l_seq_id
        WHERE seq_id IS NULL;

Line: 626

        edw_log.put_line( 'Updated ' ||  p_count  || ' records');

Line: 628

	INSERT
  	into    poa_edw_sup_perf_inc(primary_key, seq_id)
	SELECT  pll.line_location_id, l_seq_id
	FROM 	po_lines_all			pol,
		po_line_locations_all		pll,
		po_headers_all			poh
	WHERE	poh.po_header_id	    	= pll.po_header_id
	AND	pol.po_line_id		    	= pll.po_line_id
 	AND 	(greatest(pol.last_update_date,pll.last_update_date,
			poh.last_update_date)
    		        between  g_push_from_date and g_push_to_date
                 OR nvl(pll.promised_date, pll.need_by_date) +
                    nvl(pll.days_late_receipt_allowed, 0)
                    between g_push_from_date and g_push_to_date);

Line: 684

 l_insert_rcpt_failure      EXCEPTION;

Line: 695

         SELECT DISTINCT NVL(poh.rate_date, pll.creation_date) Rate_Date,
                         decode(poh.rate_type,
                                'User',gsob.currency_code,
                                NVL(poh.currency_code,
                                    gsob.currency_code)) From_Currency,
                         fstg.Collection_Status
         FROM (select TO_NUMBER(SUBSTR(sup_perf_pk, 1,
                                       INSTR(sup_perf_pk, '-' )-1))
                          Line_location_id,
                      Collection_Status
               from POA_EDW_SUP_PERF_FSTG
               where COLLECTION_STATUS = 'RATE NOT AVAILABLE' OR
                     COLLECTION_STATUS = 'INVALID CURRENCY') fstg,
              po_line_locations_all        pll,
              PO_HEADERS_ALL               POH,
              GL_SETS_OF_BOOKS             GSOB,
              FINANCIALS_SYSTEM_PARAMS_ALL FSP
        WHERE fstg.Line_location_id = pll.line_location_id
          AND PLL.PO_HEADER_ID = POH.PO_HEADER_ID
          AND NVL(fsp.org_id, -999)       = NVL(pll.org_id, -999)
          AND FSP.set_of_books_id   = GSOB.set_of_books_id;

Line: 763

      edw_log.put_line('Calling insert_rcpt...');

Line: 764

      l_no_rcpt := INSERT_RCPT(l_seq_id1);

Line: 767

        RAISE l_insert_rcpt_failure;

Line: 769

      edw_log.put_line('Inserted ' || l_no_rcpt || ' records');

Line: 774

   DELETE_DUPLICATES;

Line: 775

   edw_log.put_line('Duplicate records deleted in Inc Table');

Line: 782

      edw_log.put_line('Inserting into local staging table for view type 1');

Line: 787

      edw_log.put_line('Inserted '||nvl(l_row_count1,0)||
         ' rows into the local staging table for view type 1');

Line: 803

    INSERT_MISSING_RATES;

Line: 826

    DELETE_STG_MISSING_RATES;

Line: 833

      edw_log.put_line('Inserted '||nvl(g_row_count,0)||
         ' rows into the staging table');

Line: 856

      rollback;   -- Rollback insert into local staging

Line: 857

      edw_log.put_line('Inserting into local staging have failed');

Line: 877

   WHEN L_INSERT_RCPT_FAILURE THEN
      Errbuf:=g_errbuf;

Line: 887

      edw_log.put_line('Insert_rcpt has failed');