APPS.EGO_ITEM_BULKLOAD_PKG SQL Statements

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

Line: 128

   G_UPDATE             CONSTANT VARCHAR2(10) := 'UPDATE';

Line: 358

     SELECT VALUE
      FROM V$PARAMETER
      WHERE NAME = 'utl_file_dir';

Line: 485

PROCEDURE delete_records_from_MSII (p_set_process_id  IN NUMBER) IS

  TYPE char_tbl_type IS TABLE OF VARCHAR2(240);

Line: 501

   SELECT COLUMN_NAME
   BULK COLLECT INTO l_column_name
   FROM SYS.ALL_TAB_COLUMNS WHERE TABLE_NAME = 'MTL_SYSTEM_ITEMS_INTERFACE'
   AND COLUMN_NAME NOT IN ('SET_PROCESS_ID',
                           'TRANSACTION_ID',
                           'REQUEST_ID',
                           'PROGRAM_APPLICATION_ID',
                           'PROGRAM_ID',
                           'TRANSACTION_TYPE',
                           'ITEM_NUMBER',
                           'ORGANIZATION_CODE',
                           'PROCESS_FLAG',
                           'SOURCE_SYSTEM_ID',
                           'SOURCE_SYSTEM_REFERENCE',
                           'ITEM_CATALOG_GROUP_ID',
                           'INTERFACE_TABLE_UNIQUE_ID',
                           'INVENTORY_ITEM_ID',
                           'ORGANIZATION_ID',
                           'LAST_UPDATE_DATE',
                           'LAST_UPDATED_BY',
                           'CREATION_DATE',
                           'CREATED_BY',
                           'LAST_UPDATE_LOGIN')
   AND COLUMN_NAME NOT LIKE 'SEGMENT%'
   AND COLUMN_NAME NOT LIKE 'GLOBAL_ATTRIBUTE%'
   AND COLUMN_NAME NOT LIKE 'ATTRIBUTE%';

Line: 538

   l_dyn_sql := l_dyn_sql || ' DELETE MTL_SYSTEM_ITEMS_INTERFACE MSII ' ;

Line: 566

   l_dyn_sql := l_dyn_sql || '  EXISTS ( ';   -- there exists a row where item is being Created or updated in the same request

Line: 567

   l_dyn_sql := l_dyn_sql ||    ' SELECT ''X'' ';

Line: 575

   l_dyn_sql := l_dyn_sql ||    ' SELECT ''X'' ';

Line: 581

   Write_Debug(' DELETE MSII sql: '||l_dyn_sql);

Line: 585

   Write_Debug('delete_records_from_MSII : NEW Deleted redundant / unnecessary rows from MSII');

Line: 589

END delete_records_from_MSII;

Line: 597

 PROCEDURE Insert_Mtl_Intf_Err(  p_transaction_id       IN  VARCHAR2
                               , p_bo_identifier        IN  VARCHAR2
                               , p_error_entity_code    IN  VARCHAR2
                               , p_error_table_name     IN  VARCHAR2
                               , p_error_msg            IN  VARCHAR2
                               ) IS

 PRAGMA AUTONOMOUS_TRANSACTION;

Line: 609

   INSERT INTO MTL_INTERFACE_ERRORS
   ( ORGANIZATION_ID
     , UNIQUE_ID
     , LAST_UPDATE_DATE
     , LAST_UPDATED_BY
     , CREATION_DATE
     , CREATED_BY
     , LAST_UPDATE_LOGIN
     , TABLE_NAME
     , MESSAGE_NAME
     , COLUMN_NAME
     , REQUEST_ID
     , PROGRAM_APPLICATION_ID
     , PROGRAM_ID
     , PROGRAM_UPDATE_DATE
     , ERROR_MESSAGE
     , TRANSACTION_ID
     , ENTITY_IDENTIFIER
     , BO_IDENTIFIER)
   VALUES
   ( NULL
     , NULL
     , SYSDATE
     , G_USER_ID
     , SYSDATE
     , G_USER_ID
     , G_LOGIN_ID
     , p_error_table_name
     , NULL
     , NULL
     , G_REQUEST_ID
     , G_PROG_APPID
     , G_PROG_ID
     , SYSDATE
     , p_error_msg
     , p_transaction_id
     , p_error_entity_code
     , p_bo_identifier
   );

Line: 651

 END Insert_Mtl_Intf_Err;

Line: 662

      ' SELECT '|| p_item_num_colname
   || ' FROM   EGO_BULKLOAD_INTF '
   || ' WHERE  RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';

Line: 687

                      , position    => 1                  -- select position --
                      , c_tab       => l_item_num_table   -- table of chars --
                      , cnt         => 10000              -- rows requested --
                      , lower_bound => 1                  -- start at --
                       );

Line: 742

      ' SELECT SEGMENT1 , TRANSACTION_ID '
   || '   FROM MTL_SYSTEM_ITEMS_INTERFACE '
   || '  WHERE REQUEST_ID = '||REQUEST_ID
   || '    AND PROCESS_FLAG = '||G_INTF_STATUS_SUCCESS
   || '    AND TRANSACTION_TYPE = '''||G_CREATE||'''';

Line: 812

      ' SELECT '
   || ' INSTANCE_PK2_VALUE     '
   || ' FROM   EGO_BULKLOAD_INTF '
   || ' WHERE  RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID'
   || '  AND   '|| p_item_num_colname ||' IS NULL '
   || '  AND   PROCESS_STATUS = 1';

Line: 844

                      , position    => 1                -- select position --
                      , c_tab       => l_org_id_table   -- table of chars --
                      , cnt         => 10000            -- rows requested --
                      , lower_bound => 1                -- start at --
                       );

Line: 891

     SELECT  attr_group_disp_name
     FROM    ego_attr_groups_v
     WHERE   attr_group_id = p_attr_group_id;

Line: 896

     SELECT  attr_display_name
     FROM    ego_attrs_v
     WHERE   attr_id = p_attr_id;

Line: 951

   DELETE EGO_BULKLOAD_INTF
     WHERE RESULTFMT_USAGE_ID = p_resultfmt_usage_id
     AND   PROCESS_STATUS <> 1;

Line: 961

   l_dyn_sql :=              ' UPDATE EGO_BULKLOAD_INTF EBI';

Line: 993

    SELECT source_system_id into l_source_system_id
    FROM EGO_IMPORT_BATCHES_B
    WHERE batch_id = G_MSII_SET_PROCESS_ID;

Line: 1022

    SELECT ITEM_DESC_GEN_METHOD, ITEM_CATALOG_GROUP_ID, LEVEL
    FROM MTL_ITEM_CATALOG_GROUPS_B
    WHERE LEVEL > 1
    CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID = ITEM_CATALOG_GROUP_ID
    START WITH ITEM_CATALOG_GROUP_ID = c_catalog_group_id
    ORDER BY LEVEL;

Line: 1035

    SELECT ITEM_DESC_GEN_METHOD into l_desc_gen_method
    FROM MTL_ITEM_CATALOG_GROUPS_B
    WHERE ITEM_CATALOG_GROUP_ID = p_catalog_group_id;

Line: 1094

    SELECT attribute_code, intf_column_name
    FROM   ego_results_fmt_usages
    WHERE  resultfmt_usage_id = c_resultfmt_usage_id
     AND attribute_code NOT LIKE '%$$%'
     AND attribute_code <> 'APPROVAL_STATUS' -- bug: 3433942
     AND attribute_code NOT IN ('SUPPLIER_NAME','SUPPLIER_SITE','SUPPLIER_PRIMARY','SUPPLIER_STATUS','SUPPLIERSITE_STATUS'
                                ,'SUPPLIERSITE_PRIMARY','SUPPLIERSITE_STORE_STATUS','SUPPLIER_NUMBER')
     AND attribute_code NOT IN --Segregating Item Base Attributes using this clause
     (
        select LOOKUP_CODE CODE
        from   FND_LOOKUP_VALUES
        where  LOOKUP_TYPE = 'EGO_ITEM_REV_HDR_ATTR_GRP'
        AND    LANGUAGE = USERENV('LANG')
        AND    ENABLED_FLAG = 'Y'
     )
     AND attribute_code <> G_REV_EFF_DATE_ATTR_CODE --Bug 6139409
     ORDER BY intf_column_name;-- Bug: 3340808

Line: 1116

     SELECT 'x'
     FROM   bom_component_columns
     WHERE  attribute_code = c_attribute_code
      AND   parent_entity = 'ITEM';

Line: 1126

    SELECT 'x'
    FROM mtl_system_items_interface
    WHERE set_process_id = c_set_process_id;

Line: 1175

  l_cursor_select     INTEGER;

Line: 1212

   UPDATE ego_bulkload_intf
     --The Transaction ID sequence that is used in INVPOPIF package to
     --auto-populate Transaction ID in MSII.
     SET  transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
   WHERE  resultfmt_usage_id = p_resultfmt_usage_id;

Line: 1261

       SELECT BOM_INTF_COLUMN_NAME INTO l_bom_prod_col_name
        FROM BOM_COMPONENT_COLUMNS
       WHERE Attribute_Code = l_prod_col_name AND Parent_Entity = 'ITEM';

Line: 1359

   l_dyn_sql :=              ' UPDATE EGO_BULKLOAD_INTF ';

Line: 1421

     Write_Debug('Updated EBI with Value-to-ID Conversion Cols');

Line: 1428

   l_dyn_sql :=              'UPDATE EGO_BULKLOAD_INTF EBI';

Line: 1431

   l_dyn_sql := l_dyn_sql || '    SELECT ORGANIZATION_ID                    ';

Line: 1471

    l_dyn_sql :=              'SELECT ';

Line: 1486

    l_cursor_select := DBMS_SQL.OPEN_CURSOR;

Line: 1487

    DBMS_SQL.PARSE(l_cursor_select, l_dyn_sql, DBMS_SQL.NATIVE);

Line: 1488

    DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 1,l_org_id_table,2500, l_temp);

Line: 1489

    DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_item_number_table,2500, l_temp);

Line: 1490

    DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_trans_type_table,2500, l_temp);

Line: 1491

    DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 4,l_trans_id_table,2500, l_temp);

Line: 1493

    DBMS_SQL.BIND_VARIABLE(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);

Line: 1494

    l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);

Line: 1508

      l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);

Line: 1509

      DBMS_SQL.COLUMN_VALUE(l_cursor_select, 1, l_org_id_table);

Line: 1510

      DBMS_SQL.COLUMN_VALUE(l_cursor_select, 2, l_item_number_table);

Line: 1511

      DBMS_SQL.COLUMN_VALUE(l_cursor_select, 3, l_trans_type_table);

Line: 1512

      DBMS_SQL.COLUMN_VALUE(l_cursor_select, 4, l_trans_id_table);

Line: 1541

          l_dyn_sql :=              ' UPDATE EGO_BULKLOAD_INTF ';

Line: 1571

                l_dyn_sql :=              ' UPDATE EGO_BULKLOAD_INTF EBI';

Line: 1574

                l_dyn_sql := l_dyn_sql || '    SELECT TO_CHAR(MICG.ITEM_CATALOG_GROUP_ID) ';

Line: 1591

              l_dyn_sql :=              ' UPDATE EGO_BULKLOAD_INTF ';

Line: 1603

              l_dyn_sql :=              ' UPDATE EGO_BULKLOAD_INTF ';

Line: 1648

      l_org_id_table.DELETE;

Line: 1649

      l_item_number_table.DELETE;

Line: 1650

      l_trans_type_table.DELETE;

Line: 1651

      l_trans_id_table.DELETE;

Line: 1660

    DBMS_SQL.CLOSE_CURSOR(l_cursor_select);

Line: 1667

      l_dyn_sql := ' UPDATE ego_bulkload_intf ebi' ||
                   ' SET '||l_item_number_col||' = ''$$FG-''||TO_CHAR(transaction_id)'||
                   ' ,c_fix_column12 = TO_CHAR(transaction_id)'||
                   ' WHERE resultfmt_usage_id = :RESULTFMT_USAGE_ID'||
                   ' AND '||G_ITEM_CATALOG_EBI_COL||' IS NOT NULL'||
                   ' AND process_status = 1'||
                   ' AND 10 < '||
                   ' ( SELECT LENGTH ( MIN ('||
                         ' CASE WHEN ITEM_NUM_GEN_METHOD = ''F'' '||
                                   ' AND (PRIOR ITEM_NUM_GEN_METHOD IS NULL OR PRIOR ITEM_NUM_GEN_METHOD = ''I'') ' ||
                              ' THEN LPAD(LEVEL, 8, ''0'')||''XX''||TO_CHAR(item_num_action_id) '||
                              ' WHEN item_num_gen_method IN (''U'', ''S'') '||
                              ' THEN LPAD(LEVEL, 8, ''0'')||''XX'' '||
                              ' ELSE NULL ' ||
                              ' END        )'||
                                   ' )'||
                      ' FROM MTL_ITEM_CATALOG_GROUPS_B '||
                      ' CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID = ITEM_CATALOG_GROUP_ID ';

Line: 1690

      l_dyn_sql := ' UPDATE ego_bulkload_intf ebi' ||
                   ' SET '||l_item_number_col||' = NVL( '||l_item_number_col||', ''$$FG-''||TO_CHAR(transaction_id) )'||
                   ' ,c_fix_column12 = TO_CHAR(transaction_id)'||
                   ' WHERE resultfmt_usage_id = :RESULTFMT_USAGE_ID'||
                   ' AND '||G_ITEM_CATALOG_EBI_COL||' IS NOT NULL'||
                   ' AND UPPER(ebi.transaction_type) IN (''CREATE'',''SYNC'') '||
                   ' AND process_status = 1'||
                   ' AND ''F'' = '||
                   ' ( SELECT item_num_gen_method'||
                      ' FROM mtl_item_catalog_groups_b '||
                      ' WHERE NVL(item_num_gen_method,''I'') IN (''U'',''S'', ''F'') '||
                      ' AND ROWNUM = 1 '||
                      ' CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID = ITEM_CATALOG_GROUP_ID';

Line: 1721

      l_dyn_sql := ' UPDATE ego_bulkload_intf ebi' ||
                   ' SET '||l_item_number_col||' = NVL( '||l_item_number_col||', ''$$SG-''||TO_CHAR(transaction_id) )'||
                   ' ,c_fix_column12 = TO_CHAR(transaction_id)'||
                   ' WHERE resultfmt_usage_id = :RESULTFMT_USAGE_ID'||
                   ' AND '||G_ITEM_CATALOG_EBI_COL||' IS NOT NULL'||
                   ' AND UPPER(ebi.transaction_type) IN (''CREATE'',''SYNC'') '||
                   ' AND process_status = 1'||
                   ' AND ''S'' = '||
                   ' ( SELECT item_num_gen_method'||
                      ' FROM mtl_item_catalog_groups_b '||
                      ' WHERE NVL(item_num_gen_method,''I'') IN (''U'',''S'', ''F'') '||
                      ' AND ROWNUM = 1 '||
                      ' CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID = ITEM_CATALOG_GROUP_ID';

Line: 1753

      SELECT mtl_system_items_intf_sets_s.NEXTVAL
        INTO l_msii_set_process_id
      FROM dual;

Line: 1766

    l_dyn_sql :=              'INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE MSII';

Line: 1785

    l_dyn_sql := l_dyn_sql || ' SELECT ';

Line: 1818

    l_dyn_sql :=              ' UPDATE EGO_BULKLOAD_INTF ';

Line: 1888

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 1892

           l_dyn_sql := l_dyn_sql || '    SELECT MUOM.UOM_CODE   ';

Line: 1904

           l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 1917

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 1923

           l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 1932

           Write_Debug('MSII: Updated the Primary UOMs.');

Line: 1960

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 1964

           l_dyn_sql := l_dyn_sql || '    SELECT IT.LOOKUP_CODE ';

Line: 1977

           l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 1989

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 1995

           l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 2004

           Write_Debug('MSII: Updated the User Item Types.');

Line: 2012

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2016

           l_dyn_sql := l_dyn_sql || '    SELECT IT.LOOKUP_CODE ';

Line: 2029

           l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 2041

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2047

           l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 2056

           Write_Debug('MSII: Updated the BOM Item Types.');

Line: 2059

            l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2063

           l_dyn_sql := l_dyn_sql || '    SELECT IT.INTERNAL_NAME ';

Line: 2075

           l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 2087

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2093

           l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 2102

           Write_Debug('MSII: Updated the Trade Item Descriptor.');

Line: 2110

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2114

           l_dyn_sql := l_dyn_sql || '    SELECT IT.LOOKUP_CODE ';

Line: 2127

           l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 2139

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2145

           l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 2155

           Write_Debug('MSII: Updated the Eng Item Flags.');

Line: 2164

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2168

           l_dyn_sql := l_dyn_sql || '    SELECT IT.LOOKUP_CODE ';

Line: 2181

           l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 2193

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2199

           l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 2208

           Write_Debug('MSII: Updated the Conversions.');

Line: 2216

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2220

           l_dyn_sql := l_dyn_sql || '    SELECT IT.LOOKUP_CODE ';

Line: 2233

           l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 2245

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2251

           l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 2260

           Write_Debug('MSII: Updated the Conversions.');

Line: 2268

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2272

           l_dyn_sql := l_dyn_sql || '    SELECT IT.LOOKUP_CODE ';

Line: 2285

           l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 2297

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2303

           l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 2312

           Write_Debug('MSII: Updated the Conversions.');

Line: 2320

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2324

           l_dyn_sql := l_dyn_sql || '    SELECT MUOM.UOM_CODE   ';

Line: 2336

           l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 2348

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2354

           l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 2363

           Write_Debug('MSII: Updated the Conversions.');

Line: 2371

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2375

           l_dyn_sql := l_dyn_sql || '    SELECT IT.LOOKUP_CODE ';

Line: 2388

           l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 2400

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2406

           l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 2415

           Write_Debug('MSII: Updated the Conversions.');

Line: 2424

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2428

           l_dyn_sql := l_dyn_sql || '    SELECT IT.INVENTORY_ITEM_STATUS_CODE ';

Line: 2439

           l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 2451

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2457

           l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 2466

           Write_Debug('MSII: Updated the Item Status.*');

Line: 2481

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2496

             l_dyn_sql := l_dyn_sql || ' SELECT SUBSTR(EBI.'||l_intf_col_name_tbl(i)|| ',1,30)';

Line: 2499

              l_dyn_sql := l_dyn_sql || ' SELECT LOOKUP_CODE ';

Line: 2504

              l_dyn_sql := l_dyn_sql || ' SELECT EBI.'||l_intf_col_name_tbl(i);

Line: 2526

           l_dyn_sql := l_dyn_sql || '    SELECT EBI.TRANSACTION_ID ';

Line: 2536

           Write_Debug('MSII: Updated the '||l_prod_col_name_tbl(i)||' column values.');

Line: 2549

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2555

           l_dyn_sql := l_dyn_sql || '    SELECT NULL ';

Line: 2570

           EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id, 'F', 'UPDATE', 'SYNC';

Line: 2591

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2595

           l_dyn_sql := l_dyn_sql || '    SELECT LC.PROJ_ELEMENT_ID ';

Line: 2606

           l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 2618

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2624

           l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 2635

           Write_Debug('MSII: Updated the Lifecycle IDs.');

Line: 2644

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2648

           l_dyn_sql := l_dyn_sql || '    SELECT LCP.PROJ_ELEMENT_ID ';

Line: 2660

           l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 2672

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2678

           l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 2687

           Write_Debug('MSII: Updated the Lifecycle Phase IDs.');

Line: 2695

    UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
    SET  start_date_active = DECODE(start_date_active,G_EXCEL_NULL_DATE,EGO_ITEM_PUB.G_INTF_NULL_DATE,start_date_active),
         end_date_active = DECODE(end_date_active,G_EXCEL_NULL_DATE,EGO_ITEM_PUB.G_INTF_NULL_DATE,end_date_active),
         engineering_date = DECODE(engineering_date,G_EXCEL_NULL_DATE,EGO_ITEM_PUB.G_INTF_NULL_DATE,engineering_date)
    WHERE MSII.SET_PROCESS_ID  = l_msii_set_process_id
      AND MSII.PROCESS_FLAG  = G_PROCESS_STATUS
      AND (MSII.start_date_active IS NOT NULL
           OR
           MSII.end_date_active IS NOT NULL
           OR
           MSII.engineering_date IS NOT NULL
          )
      AND EXISTS
       ( SELECT 'X'
         FROM   EGO_BULKLOAD_INTF EBI
         WHERE  EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id
         AND    EBI.TRANSACTION_ID = MSII.TRANSACTION_ID
         AND    EBI.PROCESS_STATUS = 1
       );

Line: 2848

   l_dyn_sql := l_dyn_sql || 'INSERT INTO MTL_INTERFACE_ERRORS ';

Line: 2852

   l_dyn_sql := l_dyn_sql || ', LAST_UPDATE_DATE   ';

Line: 2853

   l_dyn_sql := l_dyn_sql || ', LAST_UPDATED_BY  ';

Line: 2856

   l_dyn_sql := l_dyn_sql || ', LAST_UPDATE_LOGIN  ';

Line: 2863

   l_dyn_sql := l_dyn_sql || ', PROGRAM_UPDATE_DATE  ';

Line: 2869

   l_dyn_sql := l_dyn_sql || 'SELECT ';

Line: 2966

   Write_Debug('MIERR: Inserted Pre-processed error messages in MTL_INTERFACE_ERRORS');

Line: 2973

   UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
    SET   MSII.PROCESS_FLAG = G_INTF_STATUS_ERROR
   WHERE  MSII.SET_PROCESS_ID = p_set_process_id
     AND  MSII.PROCESS_FLAG IN
    (
       G_ITEM_CATALOG_NAME_ERR_STS
     , G_PRIMARY_UOM_ERR_STS
     , G_LIFECYCLE_ERR_STS
     , G_LIFECYCLE_PHASE_ERR_STS
     , G_USER_ITEM_TYPE_ERR_STS
     , G_BOM_ITEM_TYPE_ERR_STS
     , G_ENG_ITEM_FLAG_ERR_STS
     , G_DESCRIPTION_ERR_STS -- Bug: 3804572
     , G_CONVERSIONS_ERR_STS --Bug: 3969593 Begin
     , G_SECONDARY_DEF_IND_ERR_STS
     , G_ONT_PRICING_QTY_SRC_ERR_STS
     , G_SECONDARY_UOM_CODE_ERR_STS
     , G_TRACKING_QTY_IND_ERR_STS --Bug: 3969593 End
     , G_INV_ITEM_STATUS_ERR_STS--Rathna MLS Status
     )
     AND  MSII.TRANSACTION_ID IN
     (
      SELECT TRANSACTION_ID
      FROM   EGO_BULKLOAD_INTF
      WHERE  RESULTFMT_USAGE_ID = p_resultfmt_usage_id
      );

Line: 3000

   Write_Debug('MSII: Updated all the line statuses to Error for Pre-processing validation errors');

Line: 3188

   UPDATE EGO_BULKLOAD_INTF EBI
     SET  EBI.PROCESS_STATUS =
     (
      SELECT MSII.PROCESS_FLAG
      FROM   MTL_SYSTEM_ITEMS_INTERFACE MSII
      WHERE  MSII.TRANSACTION_ID = EBI.TRANSACTION_ID
      )
     WHERE EXISTS
     (
      SELECT 'X'
      FROM   MTL_SYSTEM_ITEMS_INTERFACE MSII
      WHERE  MSII.TRANSACTION_ID = EBI.TRANSACTION_ID
      )
     AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;

Line: 3230

   UPDATE EGO_BULKLOAD_INTF EBI
     SET  EBI.PROCESS_STATUS = G_INTF_STATUS_SUCCESS
     WHERE EBI.PROCESS_STATUS = G_INTF_STATUS_TOBE_PROCESS
     AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;

Line: 3241

   UPDATE EGO_BULKLOAD_INTF EBI
     SET  EBI.INSTANCE_PK1_VALUE =
     (
      SELECT MSII.INVENTORY_ITEM_ID
      FROM   MTL_SYSTEM_ITEMS_INTERFACE MSII
      WHERE  MSII.TRANSACTION_ID = EBI.TRANSACTION_ID
      )
     WHERE EXISTS
     (
      SELECT 'X'
      FROM   MTL_SYSTEM_ITEMS_INTERFACE MSII
      WHERE  MSII.TRANSACTION_ID = EBI.TRANSACTION_ID
      )
     AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id
     AND EBI.PROCESS_STATUS = G_INTF_STATUS_SUCCESS;

Line: 3257

   Write_Debug('EBI: Updated the Process_Status to Indicate Succssful/Unsucessful completion.');

Line: 3300

    SELECT attribute_code, intf_column_name
    FROM   ego_results_fmt_usages
    WHERE  resultfmt_usage_id = c_resultfmt_usage_id
     AND   attribute_code NOT LIKE '%$$%'
     ---------------------------------------------------------------------------
     -- Added NOT LIKE 'GTIN_%' to filter out Dummy Attrs for Attr Group: "GTIN"
     ---------------------------------------------------------------------------
     AND   attribute_code NOT LIKE 'GTIN_%'
     AND   p_data_level = G_ITEM_REV_DATA_LEVEL
   UNION
    -------------------
    --Item Data Level
    -------------------
    SELECT attribute_code, intf_column_name
    FROM   ego_results_fmt_usages
    WHERE  resultfmt_usage_id = c_resultfmt_usage_id
     AND   attribute_code NOT LIKE '%$$%'
     ---------------------------------------------------------------------------
     -- Added NOT LIKE 'GTIN_%' to filter out Dummy Attrs for Attr Group: "GTIN"
     ---------------------------------------------------------------------------
     AND   attribute_code NOT LIKE 'GTIN_%'
     AND   p_data_level = G_ITEM_DATA_LEVEL
     AND (
     attribute_code IN -- Segregating Item Revision Base Attributes using this clause
      (
        select LOOKUP_CODE CODE
        from   FND_LOOKUP_VALUES
        where  LOOKUP_TYPE = 'EGO_ITEM_REV_HDR_ATTR_GRP'
        AND    LANGUAGE = USERENV('LANG')
        AND    ENABLED_FLAG = 'Y'
        and LOOKUP_CODE not in ('REVISION_CREATION_DATE', 'REVISION_CREATED_BY')
     )
     OR
     attribute_code = G_REV_EFF_DATE_ATTR_CODE
     -- Bug 6186037
     --accomodate for revision effective date which doest exist in EGO_ITEM_REV_HDR_ATTR_GRP and cannot be included

     );

Line: 3343

    SELECT 'x'
    FROM mtl_system_items_interface
    WHERE set_process_id = c_set_process_id;

Line: 3375

  l_cursor_select     INTEGER;

Line: 3403

     UPDATE ego_bulkload_intf
       SET  transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
     WHERE  resultfmt_usage_id = p_resultfmt_usage_id;

Line: 3507

     l_dyn_sql :=              ' UPDATE EGO_BULKLOAD_INTF EBI ';

Line: 3510

     l_dyn_sql := l_dyn_sql || '      SELECT ORGANIZATION_ID      ';

Line: 3519

     Write_Debug('Selecting Org IDs, Item Numbers');

Line: 3525

       l_dyn_sql :=              ' UPDATE EGO_BULKLOAD_INTF ';

Line: 3533

       Write_Debug('Updated EBI with Catalog Group Name for Item Revisions');

Line: 3539

     l_dyn_sql :=              ' SELECT INSTANCE_PK2_VALUE, '||l_item_number_col || ', ';

Line: 3546

     l_cursor_select := DBMS_SQL.OPEN_CURSOR;

Line: 3547

     DBMS_SQL.PARSE(l_cursor_select, l_dyn_sql, DBMS_SQL.NATIVE);

Line: 3548

     DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 1,l_org_id_table,2500, l_temp);

Line: 3549

     DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_item_number_table,2500, l_temp);

Line: 3550

     DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_trans_id_table,2500, l_temp);

Line: 3551

     DBMS_SQL.BIND_VARIABLE(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);

Line: 3552

     l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);

Line: 3556

       l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);

Line: 3557

       DBMS_SQL.COLUMN_VALUE(l_cursor_select, 1, l_org_id_table);

Line: 3558

       DBMS_SQL.COLUMN_VALUE(l_cursor_select, 2, l_item_number_table);

Line: 3559

       DBMS_SQL.COLUMN_VALUE(l_cursor_select, 3, l_trans_id_table);

Line: 3588

           l_dyn_sql :=              ' UPDATE EGO_BULKLOAD_INTF ';

Line: 3607

             l_dyn_sql :=              ' UPDATE EGO_BULKLOAD_INTF EBI';

Line: 3610

             l_dyn_sql := l_dyn_sql || '    SELECT TO_CHAR(MICG.ITEM_CATALOG_GROUP_ID) ';

Line: 3625

           l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';

Line: 3642

       l_org_id_table.DELETE;

Line: 3643

       l_item_number_table.DELETE;

Line: 3650

     END LOOP; --l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);

Line: 3652

     DBMS_SQL.CLOSE_CURSOR(l_cursor_select);

Line: 3660

       SELECT mtl_system_items_intf_sets_s.NEXTVAL
         INTO l_msii_set_process_id
       FROM dual;

Line: 3696

   l_dyn_sql :=              'INSERT INTO MTL_ITEM_REVISIONS_INTERFACE ';

Line: 3712

   l_dyn_sql := l_dyn_sql || 'SELECT ';

Line: 3737

   l_dyn_sql :=              ' UPDATE EGO_BULKLOAD_INTF ';

Line: 3772

          l_dyn_sql := l_dyn_sql || 'UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI ';

Line: 3775

          l_dyn_sql := l_dyn_sql || '    SELECT IT.LOOKUP_CODE ';

Line: 3789

          l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 3801

          Write_Debug('MIRI: Updated the Revision Reason Codes.');

Line: 3830

          l_dyn_sql := l_dyn_sql || 'UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI ';

Line: 3833

          l_dyn_sql := l_dyn_sql || '    SELECT EBI.'||l_intf_col_name_tbl(i);

Line: 3841

          l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 3851

          Write_Debug('MIRI: Updated the '||l_prod_col_name_tbl(i)||' column values.');

Line: 3881

           l_dyn_sql := l_dyn_sql || 'UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI ';

Line: 3884

           l_dyn_sql := l_dyn_sql || '    SELECT LC.PROJ_ELEMENT_ID ';

Line: 3894

           l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 3905

           Write_Debug('MIRI: Updated the Lifecycle IDs.');

Line: 3921

           l_dyn_sql := l_dyn_sql || 'UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI ';

Line: 3924

           l_dyn_sql := l_dyn_sql || '    SELECT MSI.LIFECYCLE_ID ';

Line: 3934

           l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 3948

           Write_Debug('MIRI: Updated the Lifecycle IDs from MTL_SYSTEM_ITEMS_B.');

Line: 3955

           l_dyn_sql := l_dyn_sql || 'UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI ';

Line: 3958

           l_dyn_sql := l_dyn_sql || '    SELECT LCP.PROJ_ELEMENT_ID ';

Line: 3968

           l_dyn_sql := l_dyn_sql || '    SELECT ''X'' ';

Line: 3979

           Write_Debug('MIRI: Updated the Lifecycle Phase IDs.');

Line: 3990

   /* Bug 7578350. Moving this DELETE statement to the function process_item_interface_lines(), after the function call
      load_itm_or_rev_usrattr_intf() that updates UDAs, so that we delete the rows from MTL_ITEM_REVISIONS_INTERFACE
      only if there are no Revision Level Attributes provided.

    DELETE MTL_ITEM_REVISIONS_INTERFACE MIRI
    WHERE revision IS NULL
      AND revision_id IS NULL
      AND implementation_date IS NULL
      AND effectivity_date IS NULL
      AND description IS NULL
      AND revision_label IS NULL
      AND revision_reason IS NULL
      AND current_phase_id IS NULL
      AND EXISTS (SELECT 'X'
                    FROM  EGO_BULKLOAD_INTF EBI
                   WHERE  EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id
                     AND  EBI.TRANSACTION_ID = MIRI.TRANSACTION_ID
                     AND  EBI.PROCESS_STATUS = 1
                 ); */

Line: 4013

   UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI
   SET  ecn_initiation_date = DECODE(ecn_initiation_date,G_EXCEL_NULL_DATE,EGO_ITEM_PUB.G_INTF_NULL_DATE,ecn_initiation_date),
        implementation_date = DECODE(implementation_date,G_EXCEL_NULL_DATE,EGO_ITEM_PUB.G_INTF_NULL_DATE,implementation_date),
        effectivity_date = DECODE(effectivity_date,G_EXCEL_NULL_DATE,EGO_ITEM_PUB.G_INTF_NULL_DATE,effectivity_date)
   WHERE MIRI.TRANSACTION_ID IN
      ( SELECT EBI.TRANSACTION_ID
        FROM   EGO_BULKLOAD_INTF EBI
        WHERE  EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id
        AND    EBI.PROCESS_STATUS = 1
      )
   AND    (MIRI.ecn_initiation_date IS NOT NULL
                OR
                MIRI.implementation_date IS NOT NULL
                OR
                MIRI.effectivity_date IS NOT NULL
               );

Line: 4066

   DELETE MTL_ITEM_REVISIONS_INTERFACE
     WHERE
     (
      (
       ITEM_NUMBER                   IS NOT NULL    OR
       INVENTORY_ITEM_ID             IS NOT NULL
       )
       AND
      (
       ORGANIZATION_CODE             IS NOT NULL    OR
       ORGANIZATION_ID               IS NOT NULL
       )
       AND
      (
       REVISION                      IS NOT NULL    OR
       REVISION_ID                   IS NOT NULL
       )
      )
     AND
     (
       DESCRIPTION                   IS NULL AND
       LIFECYCLE_ID                  IS NULL AND
       CURRENT_PHASE_ID              IS NULL AND
       REVISION_LABEL                IS NULL AND
       REVISION_REASON               IS NULL AND
       LIFECYCLE_ID                  IS NULL AND
       CURRENT_PHASE_ID              IS NULL AND
       EFFECTIVITY_DATE              IS NULL
      )
       AND SET_PROCESS_ID = p_set_process_id;

Line: 4097

   Write_Debug('Preprocess_ItemRev_Interface : Deleted redundant / unnecessary rows from MIRI');

Line: 4185

   UPDATE EGO_BULKLOAD_INTF EBI
     SET  EBI.PROCESS_STATUS =
     (
      SELECT MIRI.PROCESS_FLAG
      FROM   MTL_ITEM_REVISIONS_INTERFACE MIRI
      WHERE  MIRI.TRANSACTION_ID = EBI.TRANSACTION_ID
      )
     WHERE EXISTS
     (
      SELECT 'X'
      FROM   MTL_ITEM_REVISIONS_INTERFACE MIRI
      WHERE  MIRI.TRANSACTION_ID = EBI.TRANSACTION_ID
      )
     AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;

Line: 4208

   UPDATE EGO_BULKLOAD_INTF EBI
     SET  EBI.PROCESS_STATUS = G_INTF_STATUS_ERROR
     WHERE EXISTS
     (
      SELECT 'X'
      FROM   MTL_ITEM_REVISIONS_INTERFACE MIRI
      WHERE  MIRI.TRANSACTION_ID = EBI.TRANSACTION_ID
       AND   MIRI.PROCESS_FLAG   = G_INTF_STATUS_SUCCESS
       AND   MIRI.REVISION_ID IS NULL
      )
     AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;

Line: 4225

   UPDATE EGO_BULKLOAD_INTF EBI
     SET  EBI.PROCESS_STATUS = G_INTF_STATUS_SUCCESS
     WHERE EBI.PROCESS_STATUS = G_INTF_STATUS_TOBE_PROCESS
     AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;

Line: 4236

   UPDATE EGO_BULKLOAD_INTF EBI
     SET  EBI.INSTANCE_PK1_VALUE =
     (
      SELECT MSII.INVENTORY_ITEM_ID
      FROM   MTL_SYSTEM_ITEMS_INTERFACE MSII
      WHERE  MSII.TRANSACTION_ID = EBI.TRANSACTION_ID
      )
     WHERE EXISTS
     (
      SELECT 'X'
      FROM   MTL_SYSTEM_ITEMS_INTERFACE MSII
      WHERE  MSII.TRANSACTION_ID = EBI.TRANSACTION_ID
      )
     AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id
     AND EBI.PROCESS_STATUS = G_INTF_STATUS_SUCCESS;

Line: 4258

   UPDATE EGO_BULKLOAD_INTF EBI
     SET  EBI.INSTANCE_PK3_VALUE =
     (
      SELECT MIRI.REVISION_ID
      FROM   MTL_ITEM_REVISIONS_INTERFACE MIRI
      WHERE  MIRI.TRANSACTION_ID = EBI.TRANSACTION_ID
      )
     WHERE EXISTS
     (
      SELECT 'X'
      FROM   MTL_ITEM_REVISIONS_INTERFACE MIRI
      WHERE  MIRI.TRANSACTION_ID = EBI.transaction_id
      )
     AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id
     AND EBI.PROCESS_STATUS = G_INTF_STATUS_SUCCESS;

Line: 4274

   Write_Debug('EBI: Updated the Process_Status to Indicate Succssful/Unsucessful completion.');

Line: 4312

    SELECT DISTINCT To_Number(SUBSTR(attribute_code, 1, INSTR(attribute_code, '$$') - 1)) attr_group_id
    FROM   ego_results_fmt_usages
    WHERE  resultfmt_usage_id = c_resultfmt_usage_id
     AND   attribute_code LIKE '%$$%'
     AND   To_Number(SUBSTR(attribute_code, 1, INSTR(attribute_code, '$$') - 1)) IN --attr_group_id
      ------------------------------------------------------------------------------
      -- Fixed in 11.5.10. Ensuring only the Item User-Defined Attrs are processed.
      ------------------------------------------------------------------------------
      (
        SELECT attr_group_id
        FROM   ego_attr_groups_v
        WHERE  attr_group_type in (G_IUD_ATTR_GROUP_TYPE, G_GTN_SNG_ATTR_GROUP_TYPE, G_GTN_MUL_ATTR_GROUP_TYPE)
        AND    application_id = G_APPLICATION_ID
      )
      ;

Line: 4334

     SELECT  attr_group_name, attr_name, attr_group_type,
             DECODE(data_type_code,'A','C'
                                  ,'X','D'
                                  ,'Y','D'
                   ,data_type_code) data_type_code,
                   uom_class -- R12C UOM Change
     FROM    ego_attrs_v
     WHERE   attr_id = p_attr_id
      AND    attr_group_type in (G_IUD_ATTR_GROUP_TYPE, G_GTN_SNG_ATTR_GROUP_TYPE, G_GTN_MUL_ATTR_GROUP_TYPE)
      AND    application_id = G_APPLICATION_ID;

Line: 4445

  l_cursor_select          INTEGER;

Line: 4506

    SELECT NVL(MAX(ROW_IDENTIFIER),0)
      INTO L_ATTR_GRP_ROW_IDENT
      FROM EGO_ITM_USR_ATTR_INTRFC
     WHERE DATA_SET_ID = p_data_set_id;

Line: 4521

    SELECT INTF_COLUMN_NAME
      INTO l_supplier_name_col
      FROM EGO_RESULTS_FMT_USAGES
     WHERE RESULTFMT_USAGE_ID = p_resultfmt_usage_id
       AND ATTRIBUTE_CODE NOT LIKE '%$$%'
       AND CUSTOMIZATION_APPLICATION_ID = 431
       AND REGION_APPLICATION_ID = 431
       AND ATTRIBUTE_CODE = 'SUPPLIER_NAME';

Line: 4535

    SELECT INTF_COLUMN_NAME
      INTO l_supplier_number_col
      FROM EGO_RESULTS_FMT_USAGES
     WHERE RESULTFMT_USAGE_ID = p_resultfmt_usage_id
       AND ATTRIBUTE_CODE NOT LIKE '%$$%'
       AND CUSTOMIZATION_APPLICATION_ID = 431
       AND REGION_APPLICATION_ID = 431
       AND ATTRIBUTE_CODE = 'SUPPLIER_NUMBER';

Line: 4549

    SELECT INTF_COLUMN_NAME
      INTO l_supplier_site_name_col
      FROM EGO_RESULTS_FMT_USAGES
     WHERE RESULTFMT_USAGE_ID = p_resultfmt_usage_id
       AND ATTRIBUTE_CODE NOT LIKE '%$$%'
       AND CUSTOMIZATION_APPLICATION_ID = 431
       AND REGION_APPLICATION_ID = 431
       AND ATTRIBUTE_CODE = 'SUPPLIER_SITE';

Line: 4576

    SELECT DATA_LEVEL_INT_NAME INTO l_attr_group_data_level
      FROM EGO_OBJ_ATTR_GRP_ASSOCS_V
     WHERE ATTR_GROUP_TYPE in (G_IUD_ATTR_GROUP_TYPE, G_GTN_SNG_ATTR_GROUP_TYPE, G_GTN_MUL_ATTR_GROUP_TYPE)
       AND ATTR_GROUP_ID = c_attr_grp_rec.attr_group_id
       AND OBJECT_NAME = G_EGO_ITEM_OBJ_NAME
       AND ROWNUM = 1;-- The AG cannot have associations at Item level and Revision Level for different Catalogs.

Line: 4589

    l_dyn_sql := ' SELECT SUBSTR(attribute_code, INSTR(attribute_code, ''$$'')+2) attr_id, intf_column_name, DATA_LEVEL_ID ';

Line: 4597

    l_cursor_select := DBMS_SQL.OPEN_CURSOR;

Line: 4598

    DBMS_SQL.PARSE(l_cursor_select, l_dyn_sql, DBMS_SQL.NATIVE);

Line: 4599

    DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 1,l_attr_id_table,2500, l_temp);

Line: 4600

    DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_intf_col_name_table,2500, l_temp);

Line: 4601

    DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_data_level_id_table,2500, l_temp);

Line: 4603

    DBMS_SQL.BIND_VARIABLE(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);

Line: 4604

    DBMS_SQL.BIND_VARIABLE(l_cursor_select,':ATTRIBUTE_CODE', c_attr_grp_rec.attr_group_id||'$$%');

Line: 4605

    l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);

Line: 4607

    l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);

Line: 4608

    DBMS_SQL.COLUMN_VALUE(l_cursor_select, 1, l_attr_id_table);

Line: 4609

    DBMS_SQL.COLUMN_VALUE(l_cursor_select, 2, l_intf_col_name_table);

Line: 4610

    DBMS_SQL.COLUMN_VALUE(l_cursor_select, 3, l_data_level_id_table);

Line: 4613

    DBMS_SQL.CLOSE_CURSOR(l_cursor_select);

Line: 4619

    l_dyn_attr_id_val_sql := ' SELECT ';

Line: 4655

    <>
    FOR i IN 1..l_attr_id_table.COUNT LOOP
      Write_Debug(l_api_name || 'LIORUI_update_item_ids_loop - '||i);

Line: 4665

    END LOOP LIORUI_update_item_ids_loop; --end: FOR i IN 1..l_attr_id_table.COUNT LOOP

Line: 4828

              SELECT VENDOR_ID
                INTO l_supplier_id
                FROM AP_SUPPLIERS
               WHERE VENDOR_NAME = l_supplier_name;

Line: 4844

               SELECT VENDOR_ID
                 INTO l_supplier_id
                 FROM AP_SUPPLIERS
                WHERE SEGMENT1 = l_supplier_number;

Line: 4887

                  SELECT VENDOR_SITE_ID
                    INTO l_supplier_site_id
                    FROM AP_SUPPLIER_SITES_ALL
                   WHERE VENDOR_SITE_CODE = l_supplier_site_name
                     AND ORG_ID = l_site_org_id
                     AND VENDOR_ID = l_supplier_id;

Line: 5012

              l_usr_attr_data_tbl.DELETE(l_rows_per_attr_grp_indx);

Line: 5028

             SELECT UOM_CODE
               INTO l_varchar_data
               FROM MTL_UNITS_OF_MEASURE_VL
              WHERE UOM_CLASS = l_uom_class
                AND UNIT_OF_MEASURE_TL = l_uom_meaning; -- Bug	6397849

Line: 5051

    l_attr_id_table.DELETE;

Line: 5052

    l_intf_col_name_table.DELETE;

Line: 5053

    l_data_level_id_table.DELETE;

Line: 5134

        IF (l_transaction_type = G_CREATE OR l_transaction_type = G_UPDATE) THEN
           l_transaction_type := G_SYNC;

Line: 5153

        INSERT INTO EGO_ITM_USR_ATTR_INTRFC
        (
         DATA_SET_ID          ,
         TRANSACTION_ID       ,
         TRANSACTION_TYPE     ,
         INVENTORY_ITEM_ID    ,
         ORGANIZATION_ID      ,
         REVISION_ID          ,
         ITEM_NUMBER          ,
         ORGANIZATION_CODE    ,
         REVISION             ,
         ROW_IDENTIFIER       ,
         ATTR_GROUP_INT_NAME  ,
         ATTR_INT_NAME        ,
         ATTR_DISP_VALUE      ,
         PROCESS_STATUS       ,
         SOURCE_SYSTEM_ID     ,
         SOURCE_SYSTEM_REFERENCE,
         ATTR_GROUP_TYPE,         --Bug Fix 4630163(ISSUE2)
         ITEM_CATALOG_GROUP_ID,    --Bug Fix 5179741
         DATA_LEVEL_ID       ,
         PK1_VALUE           ,
         PK2_VALUE           ,
         ATTR_VALUE_UOM          --R12C UOM Changes
        )
        VALUES
        (
         l_usr_attr_data_tbl(i).DATA_SET_ID,
         l_usr_attr_data_tbl(i).TRANSACTION_ID,
         l_transaction_type,                  --l_usr_attr_data_tbl(i).TRANSACTION_TYPE,
         l_usr_attr_data_tbl(i).INVENTORY_ITEM_ID,
         l_usr_attr_data_tbl(i).ORGANIZATION_ID,
         l_usr_attr_data_tbl(i).REVISION_ID,
         l_usr_attr_data_tbl(i).ITEM_NUMBER,
         l_usr_attr_data_tbl(i).ORGANIZATION_CODE,
         l_usr_attr_data_tbl(i).REVISION,
         l_usr_attr_data_tbl(i).ROW_IDENTIFIER,
         l_usr_attr_data_tbl(i).ATTR_GROUP_NAME,
         l_usr_attr_data_tbl(i).ATTR_NAME,
         l_varchar_data,                                     -- ATTR_DISP_VALUE
         G_PROCESS_STATUS,
         l_usr_attr_data_tbl(i).SOURCE_SYSTEM_ID,
         l_usr_attr_data_tbl(i).SOURCE_SYSTEM_REFERENCE,
         l_usr_attr_data_tbl(i).ATTR_GROUP_TYPE,        --Bug Fix 4630163(ISSUE2)
         G_CATALOG_GROUP_ID,                             --Bug Fix 5179741
         l_usr_attr_data_tbl(i).DATA_LEVEL_ID,
         l_usr_attr_data_tbl(i).PK1_VALUE,
         l_usr_attr_data_tbl(i).PK2_VALUE,
         l_usr_attr_data_tbl(i).ATTR_VALUE_UOM  --R12C UOM Changes
        );

Line: 5270

    SELECT count(distinct(To_Number(SUBSTR(attribute_code, 1, INSTR(attribute_code, '$$') - 1))))
    FROM   ego_results_fmt_usages
    WHERE  resultfmt_usage_id = c_resultfmt_usage_id
     AND attribute_code LIKE '%$$%'
     -- Following statement fetches the Attribute Group Id --
     AND To_Number(SUBSTR(attribute_code, 1, INSTR(attribute_code, '$$') - 1)) IN
     (
        SELECT ATTR_GROUP_ID
        FROM   EGO_ATTR_GROUPS_V
        WHERE  ATTR_GROUP_TYPE = G_ERP_ATTR_GROUP_TYPE
         AND   APPLICATION_ID = G_APPLICATION_ID
     );

Line: 5287

    SELECT distinct(To_Number(SUBSTR(attribute_code, 1, INSTR(attribute_code, '$$') - 1))) OPER_ATTR_GRP_ID
    FROM   ego_results_fmt_usages
    WHERE  resultfmt_usage_id = c_resultfmt_usage_id
     AND attribute_code LIKE '%$$%'
     -- Following statement fetches the Attribute Group Id --
     AND To_Number(SUBSTR(attribute_code, 1, INSTR(attribute_code, '$$') - 1)) IN
     (
        SELECT ATTR_GROUP_ID
        FROM   EGO_ATTR_GROUPS_V
        WHERE  ATTR_GROUP_TYPE = G_ERP_ATTR_GROUP_TYPE
         AND   APPLICATION_ID = G_APPLICATION_ID
     );

Line: 5307

    SELECT   To_Number(SUBSTR(attribute_code, INSTR(attribute_code, '$$')+2))
           , intf_column_name
    FROM   ego_results_fmt_usages
    WHERE  resultfmt_usage_id = c_resultfmt_usage_id
     AND   INSTR(attribute_code, '$$UOM') = 0  -- R12C UOM Change : ignoring uom columns in this case:
     AND   To_Number(SUBSTR(attribute_code, 1, INSTR(attribute_code, '$$') - 1)) = c_attr_group_id;

Line: 5315

       ' SELECT   To_Number(SUBSTR(attribute_code, INSTR(attribute_code, ''$$'')+2))   '
    || '        , intf_column_name '
    || ' FROM   ego_results_fmt_usages '
    || ' WHERE  resultfmt_usage_id = :RESULTFMT_USAGE_ID '
    || ' AND   INSTR(attribute_code, ''$$UOM'') = 0 '  -- R12C UOM Change : ignoring uom columns in this case:
    || ' AND    To_Number(SUBSTR(attribute_code, 1, INSTR(attribute_code, ''$$'') - 1)) = :ATTR_GROUP_ID ';

Line: 5326

    SELECT ext.application_column_name , TL.FORM_LEFT_PROMPT
    FROM   ego_fnd_df_col_usgs_ext ext, FND_DESCR_FLEX_COL_USAGE_TL TL
    WHERE  ext.attr_id = c_attr_id
    AND    TL.LANGUAGE = USERENV('LANG')
    AND    EXT.DESCRIPTIVE_FLEXFIELD_NAME  = TL.DESCRIPTIVE_FLEXFIELD_NAME
    AND    EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE  = TL.DESCRIPTIVE_FLEX_CONTEXT_CODE
    AND    EXT.APPLICATION_COLUMN_NAME  = TL.APPLICATION_COLUMN_NAME
    AND    EXT.APPLICATION_ID = TL.APPLICATION_ID; -- Bug 6531938

Line: 5395

   SELECT OBJECT_ID
     INTO l_object_id
   FROM   FND_OBJECTS
   WHERE  OBJ_NAME = G_EGO_ITEM_OBJ_NAME;

Line: 5429

          l_attr_id_table.DELETE;

Line: 5430

          l_intf_col_table.DELETE;

Line: 5461

                         , position    => 1                  -- select position --
                         , n_tab       => l_attr_id_table    -- table of numbers --
                         , cnt         => 2500               -- rows requested --
                         , lower_bound => 1                  -- start at --
                             );

Line: 5468

                         , position    => 2                  -- select position --
                         , c_tab       => l_intf_col_table   -- table of varchar --
                         , cnt         => 2500               -- rows requested --
                         , lower_bound => 1                  -- start at --
                             );

Line: 5492

        l_dyn_sql := ' SELECT ';

Line: 5566

              l_msii_col_table.DELETE;

Line: 5719

            l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE ';

Line: 5731

                l_dyn_sql_ebi := ' UPDATE ego_bulkload_intf ' ||
                                 ' SET '|| G_VAL_SET_CONV_ERR_COL ||' = '''||l_ebi_err_msg||''''||
                                 ' WHERE TRANSACTION_ID = ' || l_transaction_id;

Line: 5767

      l_user_attr_data_table.DELETE;

Line: 5777

   l_dyn_sql := l_dyn_sql || 'INSERT INTO MTL_INTERFACE_ERRORS ';

Line: 5781

   l_dyn_sql := l_dyn_sql || ', LAST_UPDATE_DATE   ';

Line: 5782

   l_dyn_sql := l_dyn_sql || ', LAST_UPDATED_BY  ';

Line: 5785

   l_dyn_sql := l_dyn_sql || ', LAST_UPDATE_LOGIN  ';

Line: 5792

   l_dyn_sql := l_dyn_sql || ', PROGRAM_UPDATE_DATE  ';

Line: 5798

   l_dyn_sql := l_dyn_sql || 'SELECT ';

Line: 5834

   UPDATE MTL_SYSTEM_ITEMS_INTERFACE
      SET PROCESS_FLAG = G_INTF_STATUS_ERROR
    WHERE PROCESS_FLAG IN
            (
      G_VS_INVALID_ERR_STS
            )
      AND TRANSACTION_ID IN
            (
              SELECT TRANSACTION_ID
              FROM   EGO_BULKLOAD_INTF
              WHERE  RESULTFMT_USAGE_ID = p_resultfmt_usage_id
            );

Line: 5891

 SELECT C_INTF_ATTR239
 FROM   EGO_BULKLOAD_INTF
 WHERE  RESULTFMT_USAGE_ID = c_resultfmt_usage_id
   AND  C_INTF_ATTR239 IS NOT NULL;

Line: 5944

  ' UPDATE MTL_SYSTEM_ITEMS_B MSIB ' ||
  '  SET   (MSIB.UNIT_WEIGHT, MSIB.WEIGHT_UOM_CODE) = ' ||
  '  ( ' ||
  '        DECODE(MSIB.TRADE_ITEM_DESCRIPTOR, ''BASE_UNIT_OR_EACH'', MSIB.UNIT_WEIGHT, NULL), ' ||
  '        DECODE(MSIB.TRADE_ITEM_DESCRIPTOR, ''BASE_UNIT_OR_EACH'', MSIB.WEIGHT_UOM_CODE, NULL) ' ||
  '  )  ' ||
  '  WHERE EXISTS  ' ||
  ' (  ' ||
  '     SELECT ''X''  ' ||
  '     FROM   EGO_BULKLOAD_INTF EBI   ' ||
  '     WHERE  FND_NUMBER.CANONICAL_TO_NUMBER(EBI.INSTANCE_PK1_VALUE) = MSIB.INVENTORY_ITEM_ID  ' ||
  '     AND    FND_NUMBER.CANONICAL_TO_NUMBER(EBI.INSTANCE_PK2_VALUE) = MSIB.ORGANIZATION_ID  ' ||
  '     AND    EBI.PROCESS_STATUS = 7 ' || -- Successful Rows Only
  '     AND    EBI.RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID_2 ' ||
  ' ) ';

Line: 5962

  Write_Debug('MSIB: Updated the Net Weights.');

Line: 6025

    SELECT   Nvl(fmt.data_level, G_ITEM_DATA_LEVEL)
        ---------------------------------------------------------------------------------
        --Fix for Bug# 3681711. (JCGEORGE)
        -- CLASSIFICATION_CODE in EGO_RESULTS_FMT_USAGES, now stores the **current**
        -- Item Catalog Group ID for the selected Import Format.
        ---------------------------------------------------------------------------------
        --, Decode(fmt.classification1, -1, NULL, fmt.classification1)
        , Decode(fmt_usg.classification_code, -1, NULL, fmt_usg.classification_code)
    FROM   ego_results_fmt_usages fmt_usg, ego_results_format_v fmt
    WHERE  fmt_usg.resultfmt_usage_id = c_resultfmt_usage_id
     AND   fmt.customization_application_id = fmt_usg.customization_application_id
     AND   fmt.customization_code = fmt_usg.customization_code
     AND   fmt.region_application_id  = fmt_usg.region_application_id
     AND   fmt.region_code = fmt_usg.region_code;

Line: 6041

    SELECT 'x'
    FROM   ego_results_fmt_usages
    WHERE  resultfmt_usage_id = c_resultfmt_usage_id
     AND   attribute_code = G_REV_CODE_ATTR_CODE;

Line: 6047

    SELECT 'x'
    FROM   mtl_interface_errors
    WHERE  request_id = FND_GLOBAL.conc_request_id;

Line: 6197

          delete_records_from_MSII(p_data_set_id);

Line: 6206

          SELECT count(*)
            INTO l_rev_base_attrs_count
          FROM  ego_results_fmt_usages
          WHERE  resultfmt_usage_id = p_resultfmt_usage_id
          AND attribute_code NOT LIKE '%$$%'
          ---------------------------------------------------------------------------
          -- Added NOT LIKE 'GTIN_%' to filter out Dummy Attrs for Attr Group: "GTIN"
          ---------------------------------------------------------------------------
          AND attribute_code NOT LIKE 'GTIN_%'
          AND attribute_code IN --Segregating Item Revision Base Attrs using this clause
          (
              select LOOKUP_CODE CODE
              from  FND_LOOKUP_VALUES
              where  LOOKUP_TYPE = 'EGO_ITEM_REV_HDR_ATTR_GRP'
              AND    LANGUAGE = USERENV('LANG')
              AND    ENABLED_FLAG = 'Y'
          );

Line: 6369

   /* Bug 7578350. Moved this DELETE statement from load_item_revs_interface() function,
      by addubg NOT EXITS condition in WHERE caluse, so that we delete the rows from MTL_ITEM_REVISIONS_INTERFACE
      only if there are no Revision Level Attributes provided. */

   DELETE MTL_ITEM_REVISIONS_INTERFACE MIRI
    WHERE revision IS NULL
      AND revision_id IS NULL
      AND implementation_date IS NULL
      AND effectivity_date IS NULL
      AND description IS NULL
      AND revision_label IS NULL
      AND revision_reason IS NULL
      AND current_phase_id IS NULL
      AND EXISTS (SELECT 'X'
                    FROM  EGO_BULKLOAD_INTF EBI
                   WHERE  EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id
                     AND  EBI.TRANSACTION_ID = MIRI.TRANSACTION_ID
                     AND  EBI.PROCESS_STATUS = 1
                 )
      AND NOT EXISTS (SELECT 'X'
                        FROM EGO_ITM_USR_ATTR_INTRFC EIUAT
                      WHERE EIUAT.TRANSACTION_ID = MIRI.TRANSACTION_ID
                        AND EIUAT.PROCESS_STATUS = 1
                        AND EIUAT.DATA_LEVEL_ID=43106);

Line: 6407

   l_dyn_sql :=              'SELECT MIERR.REQUEST_ID REQUEST_ID, '||G_NEWLINE;

Line: 6484

      UPDATE EGO_BULKLOAD_INTF EBI
        SET  EBI.PROCESS_STATUS = G_INTF_STATUS_ERROR
        WHERE EBI.PROCESS_STATUS = G_INTF_STATUS_TOBE_PROCESS
        AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;

Line: 6517

  SELECT TRADE_ITEM_DESCRIPTOR INTO x_tradeItemDescriptor
  FROM MTL_SYSTEM_ITEMS_B
  WHERE INVENTORY_ITEM_ID = to_number(p_inventory_item_id)
    AND ORGANIZATION_ID  = to_number(p_organization_id);

Line: 6546

   SELECT C_INTF_ATTR239
 FROM   EGO_BULKLOAD_INTF
 WHERE C_INTF_ATTR239 IS NOT NULL
   AND RESULTFMT_USAGE_ID = c_resultfmt_usage_id
   AND ROWNUM < 2;

Line: 6553

    SELECT INSTANCE_PK1_VALUE , INSTANCE_PK2_VALUE
      FROM EGO_BULKLOAD_INTF
      WHERE RESULTFMT_USAGE_ID  = c_resultfmt_usage_id
        AND PROCESS_STATUS = 7;

Line: 6559

     SELECT TRADE_ITEM_DESCRIPTOR
       FROM MTL_SYSTEM_ITEMS_B
      WHERE INVENTORY_ITEM_ID = c_inventory_itemId
        AND ORGANIZATION_ID = c_organization_Id ;

Line: 6569

     SELECT DATABASE_COLUMN ,
            EDIT_IN_HIERARCHY_CODE ,
            DATA_TYPE_CODE
       FROM EGO_ATTRS_V
      WHERE ATTR_GROUP_TYPE = c_attr_group_type  -- 'EGO_ITEM_GTIN_ATTRS'
        AND EDIT_IN_HIERARCHY_CODE IN  ( c_edit_in_hcR  )  -- ( 'L' , 'LP', 'A' , 'AP')
        AND DATA_TYPE_CODE not in ( c_data_type_codeA  );

Line: 6582

     SELECT DATABASE_COLUMN ,
            EDIT_IN_HIERARCHY_CODE ,
            DATA_TYPE_CODE
       FROM EGO_ATTRS_V
      WHERE ATTR_GROUP_TYPE = c_attr_group_type  -- 'EGO_ITEM_GTIN_ATTRS'
        AND EDIT_IN_HIERARCHY_CODE IN  ( c_edit_in_hcL , c_edit_in_hcLP )  -- ( 'L' , 'LP', 'A' , 'AP')
        AND DATA_TYPE_CODE not in ( c_data_type_codeA ) ;

Line: 6595

     SELECT DATABASE_COLUMN ,
            EDIT_IN_HIERARCHY_CODE ,
            DATA_TYPE_CODE
       FROM EGO_ATTRS_V
      WHERE ATTR_GROUP_TYPE = c_attr_group_type  -- 'EGO_ITEM_GTIN_ATTRS'
        AND EDIT_IN_HIERARCHY_CODE IN  ( c_edit_in_hcR  )  -- ( 'L' , 'LP', 'A' , 'AP')
        AND DATA_TYPE_CODE in ( c_data_type_codeA ) ;

Line: 6608

     SELECT DATABASE_COLUMN ,
            EDIT_IN_HIERARCHY_CODE ,
            DATA_TYPE_CODE
       FROM EGO_ATTRS_V
      WHERE ATTR_GROUP_TYPE = c_attr_group_type  -- 'EGO_ITEM_GTIN_ATTRS'
        AND EDIT_IN_HIERARCHY_CODE IN  ( c_edit_in_hcL , c_edit_in_hcLP  )  -- ( 'L' , 'LP', 'A' , 'AP')
        AND DATA_TYPE_CODE in ( c_data_type_codeA ) ;

Line: 6835

                  EXECUTE IMMEDIATE ' UPDATE  EGO_ITEM_GTN_ATTRS_B SET  ( '|| l_leafAttrs_bTable_dbCol ||
                                   ' ) = ( SELECT '|| l_leafAttrs_bTable_value ||
                                   ' FROM DUAL ) WHERE INVENTORY_ITEM_ID  = :inventory_item_id '||
                                   ' AND ORGANIZATION_ID = :organization_id '  USING l_inventory_item_id , l_organization_id;

Line: 6844

                  EXECUTE IMMEDIATE ' UPDATE  EGO_ITEM_GTN_ATTRS_TL SET ( '|| l_leafAttrs_tlTable_dbCol ||
                                  ' ) = ( SELECT '|| l_leafAttrs_tlTable_value ||
                                  ' FROM DUAL ) WHERE INVENTORY_ITEM_ID  = :inventory_item_id ' ||
                                  ' AND ORGANIZATION_ID = :organization_id '  USING l_inventory_item_id , l_organization_id;

Line: 6852

                  EXECUTE IMMEDIATE ' UPDATE  EGO_ITM_GTN_MUL_ATTRS_B SET ( ' || l_leafAttrs_MulBTable_dbCol||
                                  ' ) = ( SELECT ' || l_leafAttrs_MulBTable_value ||
                                  ' FROM DUAL ) WHERE INVENTORY_ITEM_ID  = :inventory_item_id ' ||
                                  ' AND ORGANIZATION_ID = :organization_id '  USING l_inventory_item_id , l_organization_id;

Line: 6858

                  EXECUTE IMMEDIATE ' UPDATE  EGO_ITM_GTN_MUL_ATTRS_TL SET ( ' || l_leafAttrs_MultlTable_dbCol||
                                  ' ) = ( SELECT ' || l_leafAttrs_MultlTable_value ||
                                  ' FROM DUAL ) WHERE INVENTORY_ITEM_ID  = :inventory_item_id' ||
                                  ' AND ORGANIZATION_ID = :organization_id '  USING l_inventory_item_id , l_organization_id;

Line: 6864

                EGO_GTIN_PVT.UPDATE_REG_PUB_UPDATE_DATES (p_inventory_item_id  => l_inventory_item_id ,
                                                          p_organization_id    => l_organization_id ,
                                                          p_update_reg         => 'Y' ,
                                                          x_return_status      => l_return_status ,
                                                          x_msg_count          => l_msg_count,
                                                          x_msg_data           => l_msg_data );

Line: 6871

                   Write_Debug('GTIN : Clear_Gtin_Attrs. :: Exception from EGO_GTIN_PVT.UPDATE_REG_PUB_UPDATE_DATES ');

Line: 6879

                  EXECUTE IMMEDIATE ' UPDATE EGO_ITEM_GTN_ATTRS_B SET ( '|| l_rootAttrs_bTable_dbCol ||
                                  ' ) = ( SELECT'|| l_rootAttrs_bTable_value ||
                                  ' FROM DUAL ) WHERE INVENTORY_ITEM_ID  =  :inventory_item_id '||
                                  ' AND ORGANIZATION_ID = :organization_id  ' USING l_inventory_item_id , l_organization_id;

Line: 6886

                  EXECUTE IMMEDIATE ' UPDATE EGO_ITEM_GTN_ATTRS_TL SET ( '|| l_rootAttrs_tlTable_dbCol ||
                                  ' ) = ( SELECT'|| l_rootAttrs_tlTable_value ||
                                  ' FROM DUAL ) WHERE INVENTORY_ITEM_ID  = :inventory_item_id ' ||
                                  ' AND ORGANIZATION_ID =  :organization_id ' USING l_inventory_item_id , l_organization_id;

Line: 6893

                  EXECUTE IMMEDIATE ' UPDATE EGO_ITM_GTN_MUL_ATTRS_B SET ( '|| l_rootAttrs_MulBTable_dbCol ||
                                  ' ) = ( SELECT '|| l_rootAttrs_MulBTable_value ||
                                  ' FROM DUAL ) WHERE INVENTORY_ITEM_ID  = :inventory_item_id ' ||
                                  ' AND ORGANIZATION_ID = :organization_id '  USING l_inventory_item_id , l_organization_id;

Line: 6900

                  EXECUTE IMMEDIATE ' UPDATE EGO_ITM_GTN_MUL_ATTRS_TL SET ( '||l_rootAttrs_MultlTable_dbCol ||
                                  ' ) = ( SELECT '|| l_rootAttrs_MultlTable_value ||
                                  ' FROM DUAL ) WHERE INVENTORY_ITEM_ID  = :inventory_item_id ' ||
                                  ' AND ORGANIZATION_ID = :organization_id '  USING l_inventory_item_id , l_organization_id;

Line: 6906

                EGO_GTIN_PVT.UPDATE_REG_PUB_UPDATE_DATES (p_inventory_item_id  => l_inventory_item_id ,
                                                          p_organization_id    => l_organization_id ,
                                                          p_update_reg         => 'N' ,
                                                          x_return_status      => l_return_status,
                                                          x_msg_count          => l_msg_count,
                                                          x_msg_data           => l_msg_data );

Line: 6913

                   Write_Debug('GTIN : Clear_Gtin_Attrs. :: Exception from EGO_GTIN_PVT.UPDATE_REG_PUB_UPDATE_DATES ');

Line: 7018

  SELECT DISTINCT item_catalog_group_id
  BULK COLLECT INTO l_cc_id_table
  FROM  MTL_SYSTEM_ITEMS_INTERFACE
  WHERE set_process_id = p_set_id
    AND (organization_id = p_org_id OR p_all_org = 1)
    AND process_flag = p_rec_status
    AND transaction_type = 'CREATE';

Line: 7035

    SELECT TO_CHAR(organization_id), item_number,
           transaction_id, source_system_id,
           source_system_reference
    BULK COLLECT INTO l_org_id_table, l_old_item_num_table,
                      l_trans_id_table, l_ss_id_table,
                      l_ss_ref_table
    FROM  MTL_SYSTEM_ITEMS_INTERFACE
    WHERE set_process_id = p_set_id
      AND (organization_id = p_org_id OR p_all_org = 1)
      AND item_catalog_group_id = l_cc_id_table(cc_row_index)
      AND process_flag = p_rec_status
      AND transaction_type = 'CREATE';

Line: 7059

        UPDATE mtl_system_items_interface
           SET item_number = l_item_num_table(item_num_row_index),
               SEGMENT1 = NULL,
               SEGMENT2 = NULL,
               SEGMENT3 = NULL,
               SEGMENT4 = NULL,
               SEGMENT5 = NULL,
               SEGMENT6 = NULL,
               SEGMENT7 = NULL,
               SEGMENT8 = NULL,
               SEGMENT9 = NULL,
               SEGMENT10 = NULL,
               SEGMENT11 = NULL,
               SEGMENT12 = NULL,
               SEGMENT13 = NULL,
               SEGMENT14 = NULL,
               SEGMENT15 = NULL,
               SEGMENT16 = NULL,
               SEGMENT17 = NULL,
               SEGMENT18 = NULL,
               SEGMENT19 = NULL,
               SEGMENT20 = NULL
         WHERE set_process_id IN (p_set_id, l_xset_id) /*bug 6158936 child records are in l_xset_id*/
           AND process_flag IN (p_rec_status, 60001)   /*bug 6158936 child records are in process_flag + 60000*/
           AND source_system_id = l_ss_id_table(item_num_row_index)
           AND ( item_number = l_old_item_num_table(item_num_row_index) OR
                 source_system_reference = l_ss_ref_table(item_num_row_index));

Line: 7089

        UPDATE mtl_item_revisions_interface
           SET item_number = l_item_num_table(rev_row_index)
         WHERE set_process_id = l_xset_id
           AND source_system_id = l_ss_id_table(rev_row_index)
           AND process_flag = p_rec_status
           AND ( item_number = l_old_item_num_table(rev_row_index) OR
                 source_system_reference = l_ss_ref_table(rev_row_index));

Line: 7099

        UPDATE mtl_item_categories_interface
           SET item_number = l_item_num_table(item_cat_row_index)
         WHERE set_process_id = l_xset_id
           AND source_system_id = l_ss_id_table(item_cat_row_index)
           AND process_flag = p_rec_status
           AND ( item_number = l_old_item_num_table(item_cat_row_index) OR
                 source_system_reference = l_ss_ref_table(item_cat_row_index));

Line: 7109

        UPDATE ego_itm_usr_attr_intrfc
           SET item_number = l_item_num_table(usr_attr_row_index)
         WHERE data_set_id = l_xset_id
           AND source_system_id = l_ss_id_table(usr_attr_row_index)
           AND process_status = p_rec_status
           AND ( item_number = l_old_item_num_table(usr_attr_row_index) OR
                 source_system_reference = l_ss_ref_table(usr_attr_row_index));

Line: 7119

        UPDATE ego_item_people_intf
           SET item_number = l_item_num_table(ss_id_row_index)
         WHERE data_set_id = l_xset_id
           AND source_system_id = l_ss_id_table(ss_id_row_index)
           AND process_status = p_rec_status
           AND ( item_number = l_old_item_num_table(ss_id_row_index) OR
                 source_system_reference = l_ss_ref_table(ss_id_row_index));

Line: 7129

        UPDATE ego_aml_intf
           SET item_number = l_item_num_table(ss_id_row_index)
         WHERE data_set_id = l_xset_id
           AND source_system_id = l_ss_id_table(ss_id_row_index)
           AND process_flag = p_rec_status
           AND ( item_number = l_old_item_num_table(ss_id_row_index) OR
                 source_system_reference = l_ss_ref_table(ss_id_row_index));

Line: 7139

        UPDATE bom_bill_of_mtls_interface
           SET item_number = l_item_num_table(bill_id_row_index)
         WHERE process_flag = p_rec_status
           AND batch_id = l_xset_id
           AND ( item_number = l_old_item_num_table(bill_id_row_index) OR
                 source_system_reference = l_ss_ref_table(bill_id_row_index));

Line: 7147

        UPDATE BOM_INVENTORY_COMPS_INTERFACE
           SET component_item_number = l_item_num_table(bom_inv_id_row_index)
         WHERE process_flag = p_rec_status
           AND batch_id = l_xset_id
           AND ( component_item_number = l_old_item_num_table(bom_inv_id_row_index) OR
                 comp_source_system_reference = l_ss_ref_table(bom_inv_id_row_index));

Line: 7155

        UPDATE BOM_INVENTORY_COMPS_INTERFACE
           SET assembly_item_number = l_item_num_table(bom_par_id_row_index)
         WHERE process_flag = p_rec_status
           AND batch_id = l_xset_id
           AND ( assembly_item_number = l_old_item_num_table(bom_par_id_row_index) OR
                 parent_source_system_reference = l_ss_ref_table(bom_par_id_row_index));

Line: 7163

        UPDATE BOM_SUB_COMPS_INTERFACE
           SET assembly_item_number = l_item_num_table(bom_sub_id_row_index)
         WHERE process_flag = p_rec_status
           AND batch_id = l_xset_id
           AND ( assembly_item_number = l_old_item_num_table(bom_sub_id_row_index) OR
                 parent_source_system_reference = l_ss_ref_table(bom_sub_id_row_index));

Line: 7171

        UPDATE BOM_SUB_COMPS_INTERFACE
           SET component_item_number = l_item_num_table(bom_sub_id_row_index)
         WHERE process_flag = p_rec_status
           AND batch_id = l_xset_id
           AND ( component_item_number = l_old_item_num_table(bom_sub_id_row_index) OR
                 comp_source_system_reference = l_ss_ref_table(bom_sub_id_row_index));

Line: 7179

        UPDATE BOM_SUB_COMPS_INTERFACE
           SET substitute_comp_number = l_item_num_table(bom_sub_id_row_index)
         WHERE process_flag = p_rec_status
           AND batch_id = l_xset_id
           AND ( substitute_comp_number = l_old_item_num_table(bom_sub_id_row_index) OR
                 subcom_source_system_reference = l_ss_ref_table(bom_sub_id_row_index));

Line: 7187

        UPDATE BOM_REF_DESGS_INTERFACE
           SET assembly_item_number = l_item_num_table(bom_ref_id_row_index)
         WHERE process_flag = p_rec_status
           AND batch_id = l_xset_id
           AND ( assembly_item_number = l_old_item_num_table(bom_ref_id_row_index) OR
                 parent_source_system_reference = l_ss_ref_table(bom_ref_id_row_index));

Line: 7195

        UPDATE BOM_REF_DESGS_INTERFACE
           SET component_item_number = l_item_num_table(bom_ref_id_row_index)
         WHERE process_flag = p_rec_status
           AND batch_id = l_xset_id
           AND ( component_item_number = l_old_item_num_table(bom_ref_id_row_index) OR
                 comp_source_system_reference = l_ss_ref_table(bom_ref_id_row_index));

Line: 7203

        UPDATE BOM_COMPONENT_OPS_INTERFACE
           SET assembly_item_number = l_item_num_table(bom_comp_id_row_index)
         WHERE process_flag = p_rec_status
           AND batch_id = l_xset_id
           AND ( assembly_item_number = l_old_item_num_table(bom_comp_id_row_index) OR
                 parent_source_system_reference = l_ss_ref_table(bom_comp_id_row_index));

Line: 7211

        UPDATE BOM_COMPONENT_OPS_INTERFACE
           SET component_item_number = l_item_num_table(bom_comp_id_row_index)
         WHERE process_flag = p_rec_status
           AND batch_id = l_xset_id
           AND ( component_item_number = l_old_item_num_table(bom_comp_id_row_index) OR
                 comp_source_system_reference = l_ss_ref_table(bom_comp_id_row_index));

Line: 7255

    SELECT attribute_code, intf_column_name, data_level_id
    FROM   ego_results_fmt_usages
    WHERE  resultfmt_usage_id = c_resultfmt_usage_id
     /* AND DATA_LEVEL_ID in (SELECT DATA_LEVEL_ID
                              FROM EGO_DATA_LEVEL_B
                             WHERE DATA_LEVEL_NAME IN ('ITEM_SUP','ITEM_ORG','ITEM_SUP_SITE')
                               AND APPLICATION_ID = 431
                               AND ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP'
                            )*/;

Line: 7315

     SELECT mtl_system_items_intf_sets_s.NEXTVAL
       INTO l_msii_set_process_id
     FROM dual;

Line: 7414

   SELECT MEANING
     INTO l_yes_meaning
    FROM  FND_LOOKUP_VALUES_VL
    WHERE  LOOKUP_TYPE = 'EGO_YES_NO'
      AND  LOOKUP_CODE = 'Y';

Line: 7420

   SELECT MEANING
     INTO l_no_meaning
    FROM  FND_LOOKUP_VALUES_VL
    WHERE  LOOKUP_TYPE = 'EGO_YES_NO'
      AND  LOOKUP_CODE = 'N';

Line: 7426

   SELECT MEANING
     INTO l_active_meaning
    FROM  FND_LOOKUP_VALUES_VL
    WHERE  LOOKUP_TYPE = 'EGO_ASSOCIATION_STATUS'
      AND  LOOKUP_CODE = '1';

Line: 7432

   SELECT MEANING
     INTO l_inactive_meaning
    FROM  FND_LOOKUP_VALUES_VL
    WHERE  LOOKUP_TYPE = 'EGO_ASSOCIATION_STATUS'
      AND  LOOKUP_CODE = '2';

Line: 7445

     SELECT DATA_LEVEL_ID
       INTO l_data_level_id
       FROM EGO_DATA_LEVEL_B
      WHERE DATA_LEVEL_NAME = 'ITEM_SUP'
        AND APPLICATION_ID = 431
        AND ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP';

Line: 7452

     l_dyn_sql :=              'INSERT INTO EGO_ITEM_ASSOCIATIONS_INTF ';

Line: 7469

     l_dyn_sql := l_dyn_sql || ' LAST_UPDATED_BY          ,   ';

Line: 7470

     l_dyn_sql := l_dyn_sql || ' LAST_UPDATE_DATE         ,   ';

Line: 7471

     l_dyn_sql := l_dyn_sql || ' LAST_UPDATE_LOGIN        ,   ';

Line: 7475

     l_dyn_sql := l_dyn_sql || ' PROGRAM_UPDATE_DATE      ,   ';

Line: 7481

     l_dyn_sql := l_dyn_sql || 'SELECT ';

Line: 7518

     l_dyn_sql := l_dyn_sql || 'EBI.'||'LAST_UPDATED_BY         , ';

Line: 7519

     l_dyn_sql := l_dyn_sql || 'EBI.'||'LAST_UPDATE_DATE        , ';

Line: 7520

     l_dyn_sql := l_dyn_sql || 'EBI.'||'LAST_UPDATE_LOGIN       , ';

Line: 7524

     l_dyn_sql := l_dyn_sql || 'EBI.'||'PROGRAM_UPDATE_DATE     , ';

Line: 7564

      SELECT DATA_LEVEL_ID
        INTO l_data_level_id
        FROM EGO_DATA_LEVEL_B
       WHERE DATA_LEVEL_NAME = 'ITEM_SUP_SITE'
         AND APPLICATION_ID = 431
         AND ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP';

Line: 7571

      SELECT DATA_LEVEL_ID
        INTO l_data_level_id_1
        FROM EGO_DATA_LEVEL_B
       WHERE DATA_LEVEL_NAME = 'ITEM_SUP_SITE_ORG'
         AND APPLICATION_ID = 431
         AND ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP';

Line: 7578

      l_dyn_sql :=              'INSERT INTO EGO_ITEM_ASSOCIATIONS_INTF ';

Line: 7597

      l_dyn_sql := l_dyn_sql || ' LAST_UPDATED_BY          ,   ';

Line: 7598

      l_dyn_sql := l_dyn_sql || ' LAST_UPDATE_DATE         ,   ';

Line: 7599

      l_dyn_sql := l_dyn_sql || ' LAST_UPDATE_LOGIN        ,   ';

Line: 7603

      l_dyn_sql := l_dyn_sql || ' PROGRAM_UPDATE_DATE      ,   ';

Line: 7609

      l_dyn_sql := l_dyn_sql || 'SELECT ';

Line: 7614

      l_dyn_sql := l_dyn_sql || 'SELECT ';

Line: 7652

      l_dyn_sql := l_dyn_sql || 'EBI.'||'LAST_UPDATED_BY         , ';

Line: 7653

      l_dyn_sql := l_dyn_sql || 'EBI.'||'LAST_UPDATE_DATE        , ';

Line: 7654

      l_dyn_sql := l_dyn_sql || 'EBI.'||'LAST_UPDATE_LOGIN       , ';

Line: 7658

      l_dyn_sql := l_dyn_sql || 'EBI.'||'PROGRAM_UPDATE_DATE     , ';

Line: 7676

      l_dyn_sql := l_dyn_sql || ' SELECT ';

Line: 7714

      l_dyn_sql := l_dyn_sql || 'EBI.'||'LAST_UPDATED_BY         , ';

Line: 7715

      l_dyn_sql := l_dyn_sql || 'EBI.'||'LAST_UPDATE_DATE        , ';

Line: 7716

      l_dyn_sql := l_dyn_sql || 'EBI.'||'LAST_UPDATE_LOGIN       , ';

Line: 7720

      l_dyn_sql := l_dyn_sql || 'EBI.'||'PROGRAM_UPDATE_DATE     , ';