APPS.POA_EDW_CONTRACT_F_C SQL Statements

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

Line: 39

PROCEDURE INSERT_MISSING_RATES
IS
 BEGIN
   INSERT INTO poa_edw_contract_inc(primary_key)
   SELECT po_header_id
   FROM POA_EDW_CONTRACT_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_contract_inc table');

Line: 65

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

Line: 84

  l_rows_inserted        Number:=0;

Line: 102

   Insert Into POA_EDW_CONTRACT_FSTG(
     DUNS_FK,
     SIC_CODE_FK,
     CONTRACT_NUM,
     ACCPT_DUE_DATE_FK,
     ACCPT_REQUIRED_FK,
     AMT_AGREED_G,
     AMT_AGREED_T,
     AMT_LIMIT_G,
     AMT_LIMIT_T,
     AMT_MIN_RELEASE_G,
     AMT_MIN_RELEASE_T,
     AMT_RELEASED_G,
     AMT_RELEASED_T,
     APPROVED_DATE_FK,
     APPROVED_FK,
     APPROVER_FK,
     AP_TERMS_FK,
     BILL_LOCATION_FK,
     BUYER_FK,
     CANCELLED_FK,
     CLOSED_FK,
     COMMENTS,
     CONFIRM_ORDER_FK,
     CONTRACT_EFFECTIVE_FK,
     CONTRACT_PK,
     CREATION_DATE_FK,
     EDI_PROCESSED_FK,
     END_DATE_FK,
     FOB_FK,
     FREIGHT_TERMS_FK,
     FROZEN_FK,
     INSTANCE_FK,
     NUM_DAYS_APP_SEND_TO_ACCPT,
     NUM_DAYS_APP_TO_SEND,
     NUM_DAYS_CREATE_TO_APP,
     OPERATING_UNIT_FK,
     PO_HEADER_ID,
     PO_TYPE_FK,
     PRINTED_DATE_FK,
     RECEIVER_NOTE,
     REVISED_DATE_FK,
     REVISION_NUM,
     SHIP_LOCATION_FK,
     SHIP_VIA_FK,
     START_DATE_FK,
     SUPPLIER_NOTE,
     SUPPLIER_SITE_FK,
     SUP_SITE_GEOG_FK,
     TXN_CUR_CODE_FK,
     TXN_CUR_DATE_FK,
     TXN_CUR_RATE,
     TXN_CUR_RATE_TYPE,
     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_HOLD_FK,
     USER_MEASURE1,
     USER_MEASURE2,
     USER_MEASURE3,
     USER_MEASURE4,
     USER_MEASURE5,
     OPERATION_CODE,
     COLLECTION_STATUS)
   select
     NVL(DUNS_FK, 'NA_EDW'),
     NVL(SIC_CODE_FK, 'NA_EDW'),
     CONTRACT_NUM,
     NVL(ACCPT_DUE_DATE_FK,'NA_EDW'),
     NVL(ACCPT_REQUIRED_FK,'NA_EDW'),
     AMT_AGREED_G,
     AMT_AGREED_T,
     AMT_LIMIT_G,
     AMT_LIMIT_T,
     AMT_MIN_RELEASE_G,
     AMT_MIN_RELEASE_T,
     AMT_RELEASED_G,
     AMT_RELEASED_T,
     NVL(APPROVED_DATE_FK,'NA_EDW'),
     NVL(APPROVED_FK,'NA_EDW'),
     NVL(APPROVER_FK,'NA_EDW'),
     NVL(AP_TERMS_FK,'NA_EDW'),
     NVL(BILL_LOCATION_FK,'NA_EDW'),
     NVL(BUYER_FK,'NA_EDW'),
     NVL(CANCELLED_FK,'NA_EDW'),
     NVL(CLOSED_FK,'NA_EDW'),
     COMMENTS,
     NVL(CONFIRM_ORDER_FK,'NA_EDW'),
     NVL(CONTRACT_EFFECTIVE_FK,'NA_EDW'),
     CONTRACT_PK,
     NVL(CREATION_DATE_FK,'NA_EDW'),
     NVL(EDI_PROCESSED_FK,'NA_EDW'),
     NVL(END_DATE_FK,'NA_EDW'),
     NVL(FOB_FK,'NA_EDW'),
     NVL(FREIGHT_TERMS_FK,'NA_EDW'),
     NVL(FROZEN_FK,'NA_EDW'),
     NVL(INSTANCE_FK,'NA_EDW'),
     NUM_DAYS_APP_SEND_TO_ACCPT,
     NUM_DAYS_APP_TO_SEND,
     NUM_DAYS_CREATE_TO_APP,
     NVL(OPERATING_UNIT_FK,'NA_EDW'),
     PO_HEADER_ID,
     NVL(PO_TYPE_FK,'NA_EDW'),
     NVL(PRINTED_DATE_FK,'NA_EDW'),
     RECEIVER_NOTE,
     NVL(REVISED_DATE_FK,'NA_EDW'),
     REVISION_NUM,
     NVL(SHIP_LOCATION_FK,'NA_EDW'),
     NVL(SHIP_VIA_FK,'NA_EDW'),
     NVL(START_DATE_FK,'NA_EDW'),
     SUPPLIER_NOTE,
     NVL(SUPPLIER_SITE_FK,'NA_EDW'),
     NVL(SUP_SITE_GEOG_FK,'NA_EDW'),
     NVL(TXN_CUR_CODE_FK,'NA_EDW'),
     NVL(TXN_CUR_DATE_FK,'NA_EDW'),
     TXN_CUR_RATE,
     TXN_CUR_RATE_TYPE,
     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'),
     NVL(USER_HOLD_FK,'NA_EDW'),
     USER_MEASURE1,
     USER_MEASURE2,
     USER_MEASURE3,
     USER_MEASURE4,
     USER_MEASURE5,
     NULL, -- OPERATION_CODE
     collection_status
   from POA_EDW_CONTRACT_AGRMNTS_FCV
   WHERE view_id   = p_view_id
   AND   seq_id    = p_seq_id;

Line: 268

   l_rows_inserted := sql%rowcount;

Line: 271

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

Line: 276

   RETURN (l_rows_inserted);

Line: 291

 PROCEDURE DELETE_DUPLICATES IS

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

Line: 312

          DELETE FROM poa_edw_contract_inc
          WHERE CURRENT OF Dup_Rec;

Line: 343

   select poa_edw_contract_inc_s.nextval into l_seq_id from dual;

Line: 345

   /** 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_contract_inc
        SET seq_id = l_seq_id
        WHERE seq_id IS NULL;

Line: 354

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

Line: 356

	INSERT INTO poa_edw_contract_inc(primary_key, seq_id)
	SELECT  po_header_id, l_seq_id
	  FROM  PO_HEADERS_ALL
	 WHERE  type_lookup_code            in ('CONTRACT', 'BLANKET')
           AND  approved_flag               = 'Y'
           AND  last_update_date between g_push_date_range1 and g_push_date_range2;

Line: 414

         SELECT DISTINCT NVL(poh.rate_date, poh.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 POA_EDW_CONTRACT_FSTG        fstg,
              PO_HEADERS_ALL               POH,
              GL_SETS_OF_BOOKS             GSOB,
              FINANCIALS_SYSTEM_PARAMS_ALL FSP
         where (fstg.COLLECTION_STATUS = 'RATE NOT AVAILABLE' OR
                fstg.COLLECTION_STATUS = 'INVALID CURRENCY')
          AND fstg.PO_HEADER_ID = POH.PO_HEADER_ID
          AND nvl(POH.ORG_ID, -999) = nvl(FSP.ORG_ID, -999)
          AND FSP.set_of_books_id   = GSOB.set_of_books_id;

Line: 469

   DELETE_DUPLICATES;

Line: 470

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

Line: 477

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

Line: 482

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

Line: 500

    INSERT_MISSING_RATES;

Line: 527

    DELETE_STG_MISSING_RATES;

Line: 535

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

Line: 557

      rollback;   -- Rollback insert into local staging

Line: 558

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