APPS.EDW_TRD_PARTNER_M_C SQL Statements

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

Line: 26

l_rows_inserted    NUMBER := 0;

Line: 53

   Insert Into EDW_PO_VENDOR_HIERARCHIES (
	 hierarchy_level,
	 last_update_date,
	 vendor_id,
	 parent_vendor_id)
   select
	 level,
	 last_update_date,
	 vendor_id,
	 parent_vendor_id
   from po_vendors pov
   start with
   EXISTS (select 1
           from po_vendors pv_np
           where pv_np.parent_vendor_id is NULL
           and pv_np.vendor_id = pov.parent_vendor_id)
   connect by parent_vendor_id = PRIOR vendor_id;

Line: 71

   l_rows_inserted := sql%rowcount;

Line: 74

   edw_log.put_line('Inserted ' || to_char(nvl(l_rows_inserted, 0))||
         ' rows into the HIERARCHY table: EDW_PO_VENDOR_HIERARCHIES');

Line: 92

   Procedure update_hierarchy5 (p_from_date  IN  DATE,
                                p_to_date    IN  DATE) IS

/**********************************************************************
 *                                                                    *
 * This procedure updates the PL/SQL table t_hierarchyTable.          *
 * It will move all vendors with 5 or more levels parents to the      *
 * 4th level, their new parents will be the parents of their (grand)  *
 * parents who have 4 levels parents originally.                      *
 *                                                                    *
 * Author: phu         Date: Sep 23, 2000                             *
 **********************************************************************/

    updated_flag  BOOLEAN;

Line: 126

      SELECT hierarchy_level, vendor_id, parent_vendor_id
        FROM EDW_PO_VENDOR_HIERARCHIES_V
       WHERE hierarchy_level >= 4
         AND last_update_date between p_from_date and p_to_date
       ORDER BY hierarchy_level;

Line: 134

     updated_flag := FALSE;

Line: 158

        /* update this record in (PL/SQL) table v_hierarchyTable */

        v_hierarchyTable(id).parent_vendor_id := v_hierarchyTable(pid).parent_vendor_id;

Line: 164

        updated_flag := TRUE;

Line: 170

      /* Now, update the staging table */
      IF updated_flag THEN

       select instance_code into v_instance
         from edw_local_instance;

Line: 185

          UPDATE EDW_TPRT_TRADE_PARTNER_LSTG
             SET PARENT_TPARTNER_FK = pid ||'-'|| v_instance ||'-'|| 'SUPPLIER'
           WHERE TRADE_PARTNER_PK   = id  ||'-'|| v_instance ||'-'|| 'SUPPLIER';

Line: 200

      edw_log.put_line('***Exceptions in update_hierarchy5 : ' ||
                         sqlerrm || ' ***');

Line: 203

  END update_hierarchy5;

Line: 219

 l_rows_inserted        Number := 0;

Line: 252

   Insert Into EDW_TPRT_TPARTNER_LOC_LSTG(
 	TPARTNER_LOC_PK,
	TRADE_PARTNER_FK,
	ADDRESS_LINE1,
	ADDRESS_LINE2,
	ADDRESS_LINE3,
	ADDRESS_LINE4,
	CITY,
	COUNTY,
	STATE,
	POSTAL_CODE,
	PROVINCE,
	COUNTRY,
	BUSINESS_TYPE,
	TPARTNER_LOC_DP,
	NAME,
	DATE_FROM,
	DATE_TO,
	VNDR_PURCH_SITE,
	VNDR_RFQ_ONLY,
	VNDR_PAY_SITE,
	VNDR_PAY_TERMS,
	CUST_SITE_USE,
	CUST_LOCATION,
	CUST_PRIMARY_FLAG,
	CUST_STATUS,
	CUST_ORIG_SYS_REF,
	CUST_SIC_CODE,
	CUST_PAY_TERMS,
	CUST_GSA_IND,
	CUST_SHIP_PARTIAL,
	CUST_SHIP_VIA,
	CUST_FOB_POINT,
	CUST_ORDER_TYPE,
	CUST_PRICE_LIST,
	CUST_FREIGHT,
	CUST_TERRITORY,
	CUST_TAX_REF,
	CUST_SORT_PRTY,
	CUST_TAX_CODE,
	CUST_DEMAND_CLASS,
	CUST_TAX_CLASSFN,
	CUST_TAX_HDR_FLAG,
	CUST_TAX_ROUND,
	CUST_SALES_REP,
	INSTANCE,
	USER_ATTRIBUTE1,
	USER_ATTRIBUTE2,
	USER_ATTRIBUTE3,
	USER_ATTRIBUTE4,
	USER_ATTRIBUTE5,
	OPERATION_CODE,
	COLLECTION_STATUS,
        LAST_UPDATE_DATE,
	LEVEL_NAME)
   select
        TPARTNER_LOC_PK,
	nvl(TRADE_PARTNER_FK, 'NA_EDW'),
	ADDRESS_LINE1,
	ADDRESS_LINE2,
	ADDRESS_LINE3,
	ADDRESS_LINE4,
	CITY,
	COUNTY,
	STATE,
	POSTAL_CODE,
	PROVINCE,
	COUNTRY,
	BUSINESS_TYPE,
        decode(UPPER(level_name),
          'TRADE PARTNER', l_tmp_str1 || ' (' || TPARTNER_LOC_DP || ')',
  	  TPARTNER_LOC_DP),
        decode(UPPER(level_name),
          'TRADE PARTNER', l_tmp_str1 || ' (' || NAME || ')',
          NAME),
	DATE_FROM,
	DATE_TO,
	VNDR_PURCH_SITE,
	VNDR_RFQ_ONLY,
	VNDR_PAY_SITE,
	VNDR_PAY_TERMS,
	CUST_SITE_USE,
	CUST_LOCATION,
	CUST_PRIMARY_FLAG,
	CUST_STATUS,
	CUST_ORIG_SYS_REF,
	CUST_SIC_CODE,
	CUST_PAY_TERMS,
	CUST_GSA_IND,
	CUST_SHIP_PARTIAL,
	CUST_SHIP_VIA,
	CUST_FOB_POINT,
	CUST_ORDER_TYPE,
	CUST_PRICE_LIST,
	CUST_FREIGHT,
	CUST_TERRITORY,
	CUST_TAX_REF,
	CUST_SORT_PRTY,
	CUST_TAX_CODE,
	CUST_DEMAND_CLASS,
	CUST_TAX_CLASSFN,
	CUST_TAX_HDR_FLAG,
	CUST_TAX_ROUND,
	CUST_SALES_REP,
	INSTANCE,
	USER_ATTRIBUTE1,
	USER_ATTRIBUTE2,
	USER_ATTRIBUTE3,
	USER_ATTRIBUTE4,
	USER_ATTRIBUTE5,
	NULL,
	'READY',
        LAST_UPDATE_DATE,
	LEVEL_NAME
   from EDW_TPRT_TPARTNER_LOC_LCV
   where last_update_date between l_push_date_range1 and l_push_date_range2;

Line: 369

   l_rows_inserted := sql%rowcount;

Line: 372

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

Line: 381

 EDW_TRD_PARTNER_M_C.g_row_count := EDW_TRD_PARTNER_M_C.g_row_count+l_rows_inserted;

Line: 383

 EDW_TRD_PARTNER_M_C.g_row_count_m := l_rows_inserted;

Line: 407

 l_rows_inserted        Number := 0;

Line: 430

   Insert Into EDW_TPRT_TRADE_PARTNER_LSTG(
     ALTERNATE_NAME,
     CUST_ACCESS_TMPL,
     CUST_ANALYSIS_FY,
     CUST_CAT_CODE,
     CUST_CLASS,
     CUST_COMPETITOR,
     CUST_COTERM_DATE,
     CUST_DO_NOT_MAIL,
     CUST_FISCAL_END,
     CUST_FOB_POINT,
     CUST_FREIGHT,
     CUST_GSA_IND,
     CUST_KEY,
     CUST_NUMBER,
     CUST_NUM_EMP,
     CUST_ORDER_TYPE,
     CUST_ORIG_SYS,
     CUST_ORIG_SYS_REF,
     CUST_PRICE_LIST,
     CUST_PROSPECT,
     CUST_REF_USE_FLAG,
     CUST_REVENUE_CURR,
     CUST_REVENUE_NEXT,
     CUST_SALES_CHNL,
     CUST_SALES_REP,
     CUST_SHIP_PARTIAL,
     CUST_SHIP_VIA,
     CUST_STATUS,
     CUST_TAX_CODE,
     CUST_TAX_HDR_FLAG,
     CUST_TAX_ROUND,
     CUST_THIRD_PARTY,
     CUST_TYPE,
     CUST_YEAR_EST,
     END_ACTIVE_DATE,
     INSTANCE,
     LAST_UPDATE_DATE,
     NAME,
     PARENT_TPARTNER_FK,
     PAYMENT_TERMS,
     SIC_CODE,
     START_ACTIVE_DATE,
     TAXPAYER_ID,
     TAX_REG_NUM,
     TRADE_PARTNER_DP,
     TRADE_PARTNER_PK,
     USER_ATTRIBUTE1,
     USER_ATTRIBUTE2,
     USER_ATTRIBUTE3,
     USER_ATTRIBUTE4,
     USER_ATTRIBUTE5,
     VNDR_HOLD_FLAG,
     VNDR_INSPECT_REQ,
     VNDR_MINORITY_GRP,
     VNDR_NUMBER,
     VNDR_ONE_TIME,
     VNDR_RECEIPT_REQ,
     VNDR_SMALL_BUS,
     VNDR_SUB_RECEIPT,
     VNDR_TYPE,
     VNDR_UNORDER_RCV,
     VNDR_WOMEN_OWNED,
     OPERATION_CODE,
     COLLECTION_STATUS)
   select
     ALTERNATE_NAME,
     CUST_ACCESS_TMPL,
     CUST_ANALYSIS_FY,
     CUST_CAT_CODE,
     CUST_CLASS,
     CUST_COMPETITOR,
     CUST_COTERM_DATE,
     CUST_DO_NOT_MAIL,
     CUST_FISCAL_END,
     CUST_FOB_POINT,
     CUST_FREIGHT,
     CUST_GSA_IND,
     CUST_KEY,
     CUST_NUMBER,
     CUST_NUM_EMP,
     CUST_ORDER_TYPE,
     CUST_ORIG_SYS,
     CUST_ORIG_SYS_REF,
     CUST_PRICE_LIST,
     CUST_PROSPECT,
     CUST_REF_USE_FLAG,
     CUST_REVENUE_CURR,
     CUST_REVENUE_NEXT,
     CUST_SALES_CHNL,
     CUST_SALES_REP,
     CUST_SHIP_PARTIAL,
     CUST_SHIP_VIA,
     CUST_STATUS,
     CUST_TAX_CODE,
     CUST_TAX_HDR_FLAG,
     CUST_TAX_ROUND,
     CUST_THIRD_PARTY,
     CUST_TYPE,
     CUST_YEAR_EST,
     END_ACTIVE_DATE,
     INSTANCE,
     LAST_UPDATE_DATE,
     NAME,
     nvl(PARENT_TPARTNER_FK, 'NA_EDW'),
     PAYMENT_TERMS,
     SIC_CODE,
     START_ACTIVE_DATE,
     TAXPAYER_ID,
     TAX_REG_NUM,
     TRADE_PARTNER_DP,
     TRADE_PARTNER_PK,
     USER_ATTRIBUTE1,
     USER_ATTRIBUTE2,
     USER_ATTRIBUTE3,
     USER_ATTRIBUTE4,
     USER_ATTRIBUTE5,
     VNDR_HOLD_FLAG,
     VNDR_INSPECT_REQ,
     VNDR_MINORITY_GRP,
     VNDR_NUMBER,
     VNDR_ONE_TIME,
     VNDR_RECEIPT_REQ,
     VNDR_SMALL_BUS,
     VNDR_SUB_RECEIPT,
     VNDR_TYPE,
     VNDR_UNORDER_RCV,
     VNDR_WOMEN_OWNED,
     NULL, -- OPERATION_CODE
     'READY'
   from EDW_TPRT_TRADE_PARTNER_LCV
   where last_update_date between l_push_date_range1 and l_push_date_range2;

Line: 567

    update_hierarchy5 (l_push_date_range1, l_push_date_range2);

Line: 572

   l_rows_inserted := sql%rowcount;

Line: 575

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

Line: 584

EDW_TRD_PARTNER_M_C.G_row_count :=EDW_TRD_PARTNER_M_C.G_row_count+l_rows_inserted;

Line: 604

 l_rows_inserted        Number := 0;

Line: 628

   Insert Into EDW_TPRT_P1_TPARTNER_LSTG(
     ALLOW_SUB_RECEIPT,
     ALLOW_UNORDER_RCV,
     ALTERNATE_NAME,
     END_ACTIVE_DATE,
     HOLD_FLAG,
     INSPECT_REQUIRED,
     INSTANCE,
     LAST_UPDATE_DATE,
     MINORITY_GROUP,
     NAME,
     ONE_TIME_FLAG,
     PARENT_TPARTNER_FK,
     PAYMENT_TERMS,
     RECEIPT_REQUIRED,
     SIC_CODE,
     SMALL_BUSINESS,
     START_ACTIVE_DATE,
     TAXPAYER_ID,
     TAX_REG_NUM,
     USER_ATTRIBUTE1,
     USER_ATTRIBUTE2,
     USER_ATTRIBUTE3,
     USER_ATTRIBUTE4,
     USER_ATTRIBUTE5,
     TPARTNER_DP,
     VENDOR_NUMBER,
     TPARTNER_PK,
     VENDOR_TYPE,
     WOMEN_OWNED,
     OPERATION_CODE,
     COLLECTION_STATUS)
   select
     ALLOW_SUB_RECEIPT,
     ALLOW_UNORDER_RCV,
     ALTERNATE_NAME,
     END_ACTIVE_DATE,
     HOLD_FLAG,
     INSPECT_REQUIRED,
     INSTANCE,
     LAST_UPDATE_DATE,
     MINORITY_GROUP,
     NAME,
     ONE_TIME_FLAG,
     nvl(PARENT_TPARTNER_FK, 'NA_EDW'),
     PAYMENT_TERMS,
     RECEIPT_REQUIRED,
     SIC_CODE,
     SMALL_BUSINESS,
     START_ACTIVE_DATE,
     TAXPAYER_ID,
     TAX_REG_NUM,
     USER_ATTRIBUTE1,
     USER_ATTRIBUTE2,
     USER_ATTRIBUTE3,
     USER_ATTRIBUTE4,
     USER_ATTRIBUTE5,
     TPARTNER_DP,
     VENDOR_NUMBER,
     TPARTNER_PK,
     VENDOR_TYPE,
     WOMEN_OWNED,
     NULL, -- OPERATION_CODE
     'READY'
   from EDW_TPRT_P1_TPARTNER_LCV
   where last_update_date between l_push_date_range1 and l_push_date_range2;

Line: 695

   l_rows_inserted := sql%rowcount;

Line: 698

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

Line: 707

   EDW_TRD_PARTNER_M_C.G_row_count:=EDW_TRD_PARTNER_M_C.G_row_count+l_rows_inserted;

Line: 727

 l_rows_inserted        Number := 0;

Line: 749

   Insert Into EDW_TPRT_P2_TPARTNER_LSTG(
     ALLOW_SUB_RECEIPT,
     ALLOW_UNORDER_RCV,
     ALTERNATE_NAME,
     END_ACTIVE_DATE,
     HOLD_FLAG,
     INSPECT_REQUIRED,
     INSTANCE,
     LAST_UPDATE_DATE,
     MINORITY_GROUP,
     NAME,
     ONE_TIME_FLAG,
     PARENT_TPARTNER_FK,
     PAYMENT_TERMS,
     RECEIPT_REQUIRED,
     SIC_CODE,
     SMALL_BUSINESS,
     START_ACTIVE_DATE,
     TAXPAYER_ID,
     TAX_REG_NUM,
     USER_ATTRIBUTE1,
     USER_ATTRIBUTE2,
     USER_ATTRIBUTE3,
     USER_ATTRIBUTE4,
     USER_ATTRIBUTE5,
     TPARTNER_DP,
     VENDOR_NUMBER,
     TPARTNER_PK,
     VENDOR_TYPE,
     WOMEN_OWNED,
     OPERATION_CODE,
     COLLECTION_STATUS)
   select
     ALLOW_SUB_RECEIPT,
     ALLOW_UNORDER_RCV,
     ALTERNATE_NAME,
     END_ACTIVE_DATE,
     HOLD_FLAG,
     INSPECT_REQUIRED,
     INSTANCE,
     LAST_UPDATE_DATE,
     MINORITY_GROUP,
     NAME,
     ONE_TIME_FLAG,
     nvl(PARENT_TPARTNER_FK, 'NA_EDW'),
     PAYMENT_TERMS,
     RECEIPT_REQUIRED,
     SIC_CODE,
     SMALL_BUSINESS,
     START_ACTIVE_DATE,
     TAXPAYER_ID,
     TAX_REG_NUM,
     USER_ATTRIBUTE1,
     USER_ATTRIBUTE2,
     USER_ATTRIBUTE3,
     USER_ATTRIBUTE4,
     USER_ATTRIBUTE5,
     TPARTNER_DP,
     VENDOR_NUMBER,
     TPARTNER_PK,
     VENDOR_TYPE,
     WOMEN_OWNED,
     NULL, -- OPERATION_CODE
     'READY'
   from EDW_TPRT_P2_TPARTNER_LCV
   where last_update_date between l_push_date_range1 and l_push_date_range2;

Line: 816

   l_rows_inserted := sql%rowcount;

Line: 819

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

Line: 827

   EDW_TRD_PARTNER_M_C.G_row_count:=EDW_TRD_PARTNER_M_C.G_row_count+l_rows_inserted;

Line: 848

 l_rows_inserted        Number := 0;

Line: 870

   Insert Into EDW_TPRT_P3_TPARTNER_LSTG(
     ALLOW_SUB_RECEIPT,
     ALLOW_UNORDER_RCV,
     ALTERNATE_NAME,
     END_ACTIVE_DATE,
     HOLD_FLAG,
     INSPECT_REQUIRED,
     INSTANCE,
     LAST_UPDATE_DATE,
     MINORITY_GROUP,
     NAME,
     ONE_TIME_FLAG,
     PARENT_TPARTNER_FK,
     PAYMENT_TERMS,
     RECEIPT_REQUIRED,
     SIC_CODE,
     SMALL_BUSINESS,
     START_ACTIVE_DATE,
     TAXPAYER_ID,
     TAX_REG_NUM,
     USER_ATTRIBUTE1,
     USER_ATTRIBUTE2,
     USER_ATTRIBUTE3,
     USER_ATTRIBUTE4,
     USER_ATTRIBUTE5,
     TPARTNER_DP,
     VENDOR_NUMBER,
     TPARTNER_PK,
     VENDOR_TYPE,
     WOMEN_OWNED,
     OPERATION_CODE,
     COLLECTION_STATUS)
   select
     ALLOW_SUB_RECEIPT,
     ALLOW_UNORDER_RCV,
     ALTERNATE_NAME,
     END_ACTIVE_DATE,
     HOLD_FLAG,
     INSPECT_REQUIRED,
     INSTANCE,
     LAST_UPDATE_DATE,
     MINORITY_GROUP,
     NAME,
     ONE_TIME_FLAG,
     nvl(PARENT_TPARTNER_FK, 'NA_EDW'),
     PAYMENT_TERMS,
     RECEIPT_REQUIRED,
     SIC_CODE,
     SMALL_BUSINESS,
     START_ACTIVE_DATE,
     TAXPAYER_ID,
     TAX_REG_NUM,
     USER_ATTRIBUTE1,
     USER_ATTRIBUTE2,
     USER_ATTRIBUTE3,
     USER_ATTRIBUTE4,
     USER_ATTRIBUTE5,
     TPARTNER_DP,
     VENDOR_NUMBER,
     TPARTNER_PK,
     VENDOR_TYPE,
     WOMEN_OWNED,
     NULL, -- OPERATION_CODE
     'READY'
   from EDW_TPRT_P3_TPARTNER_LCV
   where last_update_date between l_push_date_range1 and l_push_date_range2;

Line: 938

   l_rows_inserted := sql%rowcount;

Line: 941

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

Line: 949

   EDW_TRD_PARTNER_M_C.G_row_count:=EDW_TRD_PARTNER_M_C.G_row_count+l_rows_inserted;

Line: 970

 l_rows_inserted        Number := 0;

Line: 992

   Insert Into EDW_TPRT_P4_TPARTNER_LSTG(
     ALLOW_SUB_RECEIPT,
     ALLOW_UNORDER_RCV,
     PARENT_TPARTNER_FK,
     ALTERNATE_NAME,
     END_ACTIVE_DATE,
     HOLD_FLAG,
     INSPECT_REQUIRED,
     INSTANCE,
     LAST_UPDATE_DATE,
     MINORITY_GROUP,
     NAME,
     ONE_TIME_FLAG,
     PAYMENT_TERMS,
     RECEIPT_REQUIRED,
     SIC_CODE,
     SMALL_BUSINESS,
     START_ACTIVE_DATE,
     TAXPAYER_ID,
     TAX_REG_NUM,
     USER_ATTRIBUTE1,
     USER_ATTRIBUTE2,
     USER_ATTRIBUTE3,
     USER_ATTRIBUTE4,
     USER_ATTRIBUTE5,
     TPARTNER_DP,
     VENDOR_NUMBER,
     TPARTNER_PK,
     VENDOR_TYPE,
     WOMEN_OWNED,
     OPERATION_CODE,
     COLLECTION_STATUS)
   select
     ALLOW_SUB_RECEIPT,
     ALLOW_UNORDER_RCV,
     nvl(PARENT_TPARTNER_FK, 'NA_EDW'),
     ALTERNATE_NAME,
     END_ACTIVE_DATE,
     HOLD_FLAG,
     INSPECT_REQUIRED,
     INSTANCE,
     LAST_UPDATE_DATE,
     MINORITY_GROUP,
     NAME,
     ONE_TIME_FLAG,
     PAYMENT_TERMS,
     RECEIPT_REQUIRED,
     SIC_CODE,
     SMALL_BUSINESS,
     START_ACTIVE_DATE,
     TAXPAYER_ID,
     TAX_REG_NUM,
     USER_ATTRIBUTE1,
     USER_ATTRIBUTE2,
     USER_ATTRIBUTE3,
     USER_ATTRIBUTE4,
     USER_ATTRIBUTE5,
     TPARTNER_DP,
     VENDOR_NUMBER,
     TPARTNER_PK,
     VENDOR_TYPE,
     WOMEN_OWNED,
     NULL, -- OPERATION_CODE
     'READY'
   from EDW_TPRT_P4_TPARTNER_LCV
   where last_update_date between l_push_date_range1 and l_push_date_range2;

Line: 1059

   l_rows_inserted := sql%rowcount;

Line: 1062

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

Line: 1070

   EDW_TRD_PARTNER_M_C.G_row_count:=EDW_TRD_PARTNER_M_C.G_row_count+l_rows_inserted;