APPS.EDW_UNSPSC_M_C SQL Statements

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

Line: 15

 l_rows_inserted            Number:=0;

Line: 99

  Update POA_UNSPSC_INTERFACE
  set Segment = UNSPSC,
      Segment_Description = UNSPSC_DESCRIPTION
  where (Segment IS NULL);

Line: 104

  Update POA_UNSPSC_INTERFACE
  set Family = UNSPSC,
      Family_Description = UNSPSC_DESCRIPTION
  where (Family IS NULL);

Line: 109

  Update POA_UNSPSC_INTERFACE
  set Class = UNSPSC,
      Class_Description = UNSPSC_DESCRIPTION
  where (Class IS NULL);

Line: 114

  Update POA_UNSPSC_INTERFACE
  set Commodity = UNSPSC,
      Commodity_Description = UNSPSC_DESCRIPTION
  where (Commodity IS NULL);

Line: 119

  Update POA_DNB_ITEMS poa
  set (Item_PK, Item_Name, Function, DNB_Update_Date) =
      (select Item_PK, Item_Name, UNSPSC || '-' || Function,
       sysdate from POA_UNSPSC_INTERFACE dnb
       where poa.Item_PK = dnb.Item_PK)
  where Item_PK IN
        (select Item_PK from POA_UNSPSC_INTERFACE dnb
         where ((poa.Item_PK = dnb.Item_PK) and
                 (poa.Function <> dnb.Function)));

Line: 129

  insert into POA_DNB_ITEMS poa
 (Item_PK, Item_Name, Function, DNB_Update_Date)
  (select Item_PK, Item_Name, UNSPSC || '-' || Function,
   sysdate from POA_UNSPSC_INTERFACE dnb
   where dnb.Item_PK NOT IN (select Item_PK
                             from POA_DNB_ITEMS));

Line: 141

    l_rows_inserted NUMBER :=0;

Line: 146

   Insert Into
   EDW_SPSC_CLASS_LSTG(
       NAME,
       CLASS_PK,
       CLASS_DP,
       CLASS_CODE,
       FAMILY_FK,
       INSTANCE,
       LAST_UPDATE_DATE,
       COLLECTION_STATUS)
   select
       distinct dnb.Class_Description,
       dnb.Class,
       dnb.Class_Description,
       dnb.Class,
       NVL(dnb.Family, 'NA_EDW'),
       NULL,
       sysdate,
       'READY'
   from POA_UNSPSC_INTERFACE dnb;

Line: 167

   l_rows_inserted := sql%rowcount;

Line: 168

   EDW_UNSPSC_M_C.g_row_count := EDW_UNSPSC_M_C.g_row_count + l_rows_inserted ;

Line: 184

    l_rows_inserted NUMBER :=0;

Line: 189

   Insert Into
   EDW_SPSC_COMMODITY_LSTG(
      NAME,
      COMMODITY_PK,
      COMMODITY_CODE,
      COMMODITY_DP,
      UNSPSC,
      UNSPSC_DESCRIPTION,
      CLASS_FK,
      INSTANCE,
      LAST_UPDATE_DATE,
      COLLECTION_STATUS)
   select
       distinct Commodity_Description,
       dnb.Commodity,
       dnb.Commodity,
       dnb.Commodity_Description,
       dnb.UNSPSC,
       dnb.UNSPSC_Description,
       NVL(dnb.Class, 'NA_EDW'),
       NULL,
       sysdate,
       'READY'
   from POA_UNSPSC_INTERFACE dnb;

Line: 214

   l_rows_inserted := sql%rowcount;

Line: 215

   EDW_UNSPSC_M_C.g_row_count := EDW_UNSPSC_M_C.g_row_count + l_rows_inserted ;

Line: 233

    l_rows_inserted NUMBER :=0;

Line: 238

   Insert Into
   EDW_SPSC_FAMILY_LSTG(
       NAME,
       FAMILY_PK,
       FAMILY_DP,
       FAMILY_CODE,
       SEGMENT_FK,
       INSTANCE,
       LAST_UPDATE_DATE,
       COLLECTION_STATUS)
   select
       distinct Family_Description,
       dnb.Family,
       dnb.Family_Description,
       dnb.Family,
       NVL(Segment, 'NA_EDW'),
       NULL,
       sysdate,
       'READY'
   from POA_UNSPSC_INTERFACE dnb;

Line: 259

   l_rows_inserted := sql%rowcount;

Line: 260

   EDW_UNSPSC_M_C.g_row_count := EDW_UNSPSC_M_C.g_row_count + l_rows_inserted ;

Line: 275

    l_rows_inserted NUMBER :=0;

Line: 282

   Update POA_UNSPSC_INTERFACE
   set Update_Fact_Flag = 'N';

Line: 285

   Update POA_UNSPSC_INTERFACE
   set Update_Fact_Flag = 'Y'
   where Item_PK IN (select Item_PK
                     from  POA_DNB_ITEMS poa
                     where poa.DNB_Update_Date between
                     l_date1 and l_date2);

Line: 293

   Insert Into
   EDW_SPSC_FUNCTION_LSTG(
       NAME,
       FUNCTION_PK,
       FUNCTION_DP,
       FUNCTION_CODE,
       COMMODITY_FK,
       INSTANCE,
       LAST_UPDATE_DATE,
       COLLECTION_STATUS,
       UPDATE_FACT_FLAG)
   select
       distinct NVL(ltrim(dnb.Function_Description), dnb.UNSPSC_Description),
       dnb.UNSPSC || '-' || dnb.Function,
       NVL(ltrim(dnb.Function_Description), dnb.UNSPSC_Description),
       dnb.UNSPSC || '-' || dnb.Function,
       NVL(Commodity, 'NA_EDW'),
       NULL,
       sysdate,
       'READY',
       dnb.Update_Fact_Flag
   from POA_UNSPSC_INTERFACE dnb,
        POA_DNB_ITEMS poa
   where (dnb.Item_PK = poa.Item_PK);

Line: 318

   l_rows_inserted := sql%rowcount;

Line: 319

   EDW_UNSPSC_M_C.g_row_count := EDW_UNSPSC_M_C.g_row_count + l_rows_inserted ;

Line: 336

    l_rows_inserted NUMBER :=0;

Line: 342

   Insert Into
   EDW_SPSC_SEGMENT_LSTG(
       NAME,
       SEGMENT_PK,
       SEGMENT_DP,
       SEGMENT_CODE,
       ALL_FK,
       INSTANCE,
       LAST_UPDATE_DATE,
       COLLECTION_STATUS)
   select
       distinct dnb.Segment_Description,
       dnb.Segment,
       dnb.Segment_Description,
       dnb.Segment,
       'ALL',
       NULL,
       sysdate,
       'READY'
   from POA_UNSPSC_INTERFACE dnb;

Line: 363

   l_rows_inserted := sql%rowcount;

Line: 364

   EDW_UNSPSC_M_C.g_row_count := EDW_UNSPSC_M_C.g_row_count + l_rows_inserted ;