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: 488

PROCEDURE delete_records_from_MSII (p_set_process_id  IN NUMBER) IS

  TYPE char_tbl_type IS TABLE OF VARCHAR2(240);

Line: 506

   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: 535

   SELECT col.column_name
   BULK COLLECT INTO l_column_name
   from user_synonyms syn, dba_tab_columns col
   where syn.synonym_name = 'MTL_SYSTEM_ITEMS_INTERFACE'
   and col.owner = syn.table_owner
   and col.table_name = syn.table_name
   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: 575

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

Line: 603

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

Line: 604

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

Line: 612

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

Line: 618

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

Line: 622

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

Line: 626

END delete_records_from_MSII;

Line: 634

 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: 646

   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: 688

 END Insert_Mtl_Intf_Err;

Line: 699

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

Line: 724

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

Line: 779

      ' 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: 849

      ' 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: 881

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

Line: 928

     SELECT  attr_group_disp_name
     FROM    ego_attr_groups_v
     WHERE   attr_group_id = p_attr_group_id;

Line: 933

     SELECT  attr_display_name
     FROM    ego_attrs_v
     WHERE   attr_id = p_attr_id;

Line: 988

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

Line: 998

   l_dyn_sql :=              ' UPDATE EGO_BULKLOAD_INTF EBI';

Line: 1030

    SELECT source_system_id into l_source_system_id
    FROM EGO_IMPORT_BATCHES_B
    WHERE batch_id = G_MSII_SET_PROCESS_ID;

Line: 1059

    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: 1072

    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: 1131

    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 <> 'MASTER_ORGANIZATION_ID' -- bug: 8347241
     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: 1154

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

Line: 1164

    SELECT 'x'
    FROM mtl_system_items_interface
    WHERE set_process_id = c_set_process_id;

Line: 1213

  l_cursor_select     INTEGER;

Line: 1254

   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: 1303

       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: 1401

   l_dyn_sql :=              ' UPDATE EGO_BULKLOAD_INTF ';

Line: 1463

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

Line: 1470

   l_dyn_sql :=              'UPDATE EGO_BULKLOAD_INTF EBI';

Line: 1473

   l_dyn_sql := l_dyn_sql || '    SELECT ORGANIZATION_ID                    ';

Line: 1513

    l_dyn_sql :=              'SELECT ';

Line: 1528

    l_cursor_select := DBMS_SQL.OPEN_CURSOR;

Line: 1529

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

Line: 1530

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

Line: 1531

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

Line: 1532

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

Line: 1533

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

Line: 1535

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

Line: 1536

    l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);

Line: 1550

      l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);

Line: 1551

      DBMS_SQL.COLUMN_VALUE(l_cursor_select, 1, l_org_id_table);

Line: 1552

      DBMS_SQL.COLUMN_VALUE(l_cursor_select, 2, l_item_number_table);

Line: 1553

      DBMS_SQL.COLUMN_VALUE(l_cursor_select, 3, l_trans_type_table);

Line: 1554

      DBMS_SQL.COLUMN_VALUE(l_cursor_select, 4, l_trans_id_table);

Line: 1582

		Select ITEM_CATALOG_GROUP_ID into l_catalog_group_id
		FROM MTL_SYSTEM_ITEMS_B
		WHERE INVENTORY_ITEM_ID=l_inventory_item_id
			AND ORGANIZATION_ID=l_org_id_table(i);

Line: 1599

            l_dyn_sql :=              ' UPDATE EGO_BULKLOAD_INTF ';

Line: 1635

                l_dyn_sql :=              ' UPDATE EGO_BULKLOAD_INTF EBI';

Line: 1638

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

Line: 1655

              l_dyn_sql :=              ' UPDATE EGO_BULKLOAD_INTF ';

Line: 1667

              l_dyn_sql :=              ' UPDATE EGO_BULKLOAD_INTF ';

Line: 1713

      l_org_id_table.DELETE;

Line: 1714

      l_item_number_table.DELETE;

Line: 1715

      l_trans_type_table.DELETE;

Line: 1716

      l_trans_id_table.DELETE;

Line: 1725

    DBMS_SQL.CLOSE_CURSOR(l_cursor_select);

Line: 1732

      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: 1755

      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: 1786

      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: 1818

      SELECT mtl_system_items_intf_sets_s.NEXTVAL
        INTO l_msii_set_process_id
      FROM dual;

Line: 1831

    l_dyn_sql :=              'INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE MSII';

Line: 1850

    l_dyn_sql := l_dyn_sql || ' SELECT ';

Line: 1883

    l_dyn_sql :=              ' UPDATE EGO_BULKLOAD_INTF ';

Line: 1953

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 1957

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

Line: 1969

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

Line: 1982

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 1988

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

Line: 1997

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

Line: 2025

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2029

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

Line: 2042

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

Line: 2054

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2060

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

Line: 2069

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

Line: 2077

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2081

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

Line: 2094

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

Line: 2106

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2112

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

Line: 2121

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

Line: 2124

            l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2128

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

Line: 2140

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

Line: 2152

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2158

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

Line: 2167

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

Line: 2175

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2179

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

Line: 2192

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

Line: 2204

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2210

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

Line: 2220

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

Line: 2229

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2233

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

Line: 2246

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

Line: 2258

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2264

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

Line: 2273

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

Line: 2281

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2285

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

Line: 2298

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

Line: 2310

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2316

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

Line: 2325

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

Line: 2333

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2337

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

Line: 2350

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

Line: 2362

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2368

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

Line: 2377

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

Line: 2385

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2389

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

Line: 2401

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

Line: 2413

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2419

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

Line: 2428

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

Line: 2436

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2440

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

Line: 2453

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

Line: 2465

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2471

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

Line: 2480

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

Line: 2489

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2493

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

Line: 2504

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

Line: 2516

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2522

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

Line: 2531

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

Line: 2549

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2564

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

Line: 2567

              l_dyn_sql := l_dyn_sql || ' SELECT LOOKUP_CODE ';

Line: 2572

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

Line: 2600

           		l_dyn_sql := l_dyn_sql || '    SELECT MICG.ITEM_CATALOG_GROUP_ID ';

Line: 2611

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

Line: 2621

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

Line: 2634

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2640

           l_dyn_sql := l_dyn_sql || '    SELECT NULL ';

Line: 2655

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

Line: 2676

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2680

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

Line: 2691

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

Line: 2703

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2709

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

Line: 2720

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

Line: 2729

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2733

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

Line: 2745

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

Line: 2757

           l_dyn_sql :=              'UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII ';

Line: 2763

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

Line: 2772

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

Line: 2780

    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: 2933

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

Line: 2937

   l_dyn_sql := l_dyn_sql || ', LAST_UPDATE_DATE   ';

Line: 2938

   l_dyn_sql := l_dyn_sql || ', LAST_UPDATED_BY  ';

Line: 2941

   l_dyn_sql := l_dyn_sql || ', LAST_UPDATE_LOGIN  ';

Line: 2948

   l_dyn_sql := l_dyn_sql || ', PROGRAM_UPDATE_DATE  ';

Line: 2954

   l_dyn_sql := l_dyn_sql || 'SELECT ';

Line: 3051

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

Line: 3058

   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: 3085

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

Line: 3273

   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: 3315

   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: 3326

   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: 3342

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

Line: 3385

    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: 3428

    SELECT 'x'
    FROM mtl_system_items_interface
    WHERE set_process_id = c_set_process_id;

Line: 3460

  l_cursor_select     INTEGER;

Line: 3488

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

Line: 3592

     l_dyn_sql :=              ' UPDATE EGO_BULKLOAD_INTF EBI ';

Line: 3595

     l_dyn_sql := l_dyn_sql || '      SELECT ORGANIZATION_ID      ';

Line: 3604

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

Line: 3610

       l_dyn_sql :=              ' UPDATE EGO_BULKLOAD_INTF ';

Line: 3618

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

Line: 3624

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

Line: 3631

     l_cursor_select := DBMS_SQL.OPEN_CURSOR;

Line: 3632

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

Line: 3633

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

Line: 3634

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

Line: 3635

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

Line: 3636

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

Line: 3637

     l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);

Line: 3641

       l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);

Line: 3642

       DBMS_SQL.COLUMN_VALUE(l_cursor_select, 1, l_org_id_table);

Line: 3643

       DBMS_SQL.COLUMN_VALUE(l_cursor_select, 2, l_item_number_table);

Line: 3644

       DBMS_SQL.COLUMN_VALUE(l_cursor_select, 3, l_trans_id_table);

Line: 3673

           l_dyn_sql :=              ' UPDATE EGO_BULKLOAD_INTF ';

Line: 3692

             l_dyn_sql :=              ' UPDATE EGO_BULKLOAD_INTF EBI';

Line: 3695

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

Line: 3710

           l_dyn_sql := ' UPDATE EGO_BULKLOAD_INTF ';

Line: 3727

       l_org_id_table.DELETE;

Line: 3728

       l_item_number_table.DELETE;

Line: 3735

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

Line: 3737

     DBMS_SQL.CLOSE_CURSOR(l_cursor_select);

Line: 3745

       SELECT mtl_system_items_intf_sets_s.NEXTVAL
         INTO l_msii_set_process_id
       FROM dual;

Line: 3781

   l_dyn_sql :=              'INSERT INTO MTL_ITEM_REVISIONS_INTERFACE ';

Line: 3797

   l_dyn_sql := l_dyn_sql || 'SELECT ';

Line: 3822

   l_dyn_sql :=              ' UPDATE EGO_BULKLOAD_INTF ';

Line: 3857

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

Line: 3860

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

Line: 3874

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

Line: 3886

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

Line: 3915

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

Line: 3918

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

Line: 3926

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

Line: 3936

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

Line: 3966

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

Line: 3969

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

Line: 3979

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

Line: 3990

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

Line: 4006

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

Line: 4009

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

Line: 4019

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

Line: 4033

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

Line: 4040

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

Line: 4043

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

Line: 4053

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

Line: 4064

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

Line: 4075

   /* 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: 4098

   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: 4151

   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: 4182

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

Line: 4270

   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: 4293

   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: 4310

   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: 4321

   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: 4343

   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: 4359

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

Line: 4397

    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: 4419

     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: 4530

  l_cursor_select          INTEGER;

Line: 4591

    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: 4606

    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: 4620

    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: 4634

    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: 4661

    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: 4674

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

Line: 4682

    l_cursor_select := DBMS_SQL.OPEN_CURSOR;

Line: 4683

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

Line: 4684

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

Line: 4685

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

Line: 4686

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

Line: 4688

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

Line: 4689

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

Line: 4690

    l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);

Line: 4692

    l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);

Line: 4693

    DBMS_SQL.COLUMN_VALUE(l_cursor_select, 1, l_attr_id_table);

Line: 4694

    DBMS_SQL.COLUMN_VALUE(l_cursor_select, 2, l_intf_col_name_table);

Line: 4695

    DBMS_SQL.COLUMN_VALUE(l_cursor_select, 3, l_data_level_id_table);

Line: 4698

    DBMS_SQL.CLOSE_CURSOR(l_cursor_select);

Line: 4704

    l_dyn_attr_id_val_sql := ' SELECT ';

Line: 4740

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

Line: 4750

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

Line: 4913

              SELECT VENDOR_ID
                INTO l_supplier_id
                FROM AP_SUPPLIERS
               WHERE VENDOR_NAME = l_supplier_name;

Line: 4929

               SELECT VENDOR_ID
                 INTO l_supplier_id
                 FROM AP_SUPPLIERS
                WHERE SEGMENT1 = l_supplier_number;

Line: 4972

                  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: 5097

              l_usr_attr_data_tbl.DELETE(l_rows_per_attr_grp_indx);

Line: 5113

             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: 5138

    l_attr_id_table.DELETE;

Line: 5139

    l_intf_col_name_table.DELETE;

Line: 5140

    l_data_level_id_table.DELETE;

Line: 5221

        /* Bug 12656687 - Start. Will change the transaction type to SYNC only if user has given UPDATE. */
        -- IF (l_transaction_type = G_CREATE OR l_transaction_type = G_UPDATE) THEN
        IF (l_transaction_type = G_UPDATE) THEN
        -- Bug 12656687 : End
           l_transaction_type := G_SYNC;

Line: 5243

        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: 5360

    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: 5377

    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: 5397

    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: 5405

       ' 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: 5416

    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: 5475

     l_dyn_sql_update_msii             VARCHAR2(10000);

Line: 5476

     l_cursor_update_msii              INTEGER;             -- use for update MSII by dynamic sql

Line: 5477

     l_error_indicator                 BOOLEAN := FALSE;    -- indicate if int value is incorrect when update MSII: TRUE for incorrect

Line: 5490

   SELECT OBJECT_ID
     INTO l_object_id
   FROM   FND_OBJECTS
   WHERE  OBJ_NAME = G_EGO_ITEM_OBJ_NAME;

Line: 5524

          l_attr_id_table.DELETE;

Line: 5525

          l_intf_col_table.DELETE;

Line: 5556

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

Line: 5563

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

Line: 5593

          l_msii_col_table.DELETE;

Line: 5627

        l_dyn_sql_update_msii := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE SET ';

Line: 5628

        l_cursor_update_msii := DBMS_SQL.OPEN_CURSOR;

Line: 5633

            l_dyn_sql_update_msii := l_dyn_sql_update_msii || l_msii_col_table(i) || ' = :' || l_msii_col_table(i) || ', ';

Line: 5635

            l_dyn_sql_update_msii := l_dyn_sql_update_msii || l_msii_col_table(i) || ' = :' || l_msii_col_table(i);

Line: 5638

        l_dyn_sql_update_msii := l_dyn_sql_update_msii || ' WHERE TRANSACTION_ID = :TRANSACTION_ID';

Line: 5641

        DBMS_SQL.PARSE(l_cursor_update_msii, l_dyn_sql_update_msii, DBMS_SQL.NATIVE);

Line: 5651

        l_dyn_sql := ' SELECT ';

Line: 5858

            l_dyn_sql := 'UPDATE MTL_SYSTEM_ITEMS_INTERFACE ';

Line: 5871

                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: 5888

                  DBMS_SQL.BIND_VARIABLE(l_cursor_update_msii, ':' || l_msii_col_table(i), l_attr_int_val_table(i));

Line: 5890

              DBMS_SQL.BIND_VARIABLE(l_cursor_update_msii, ':TRANSACTION_ID', l_transaction_id);

Line: 5891

              l_execute := DBMS_SQL.EXECUTE(l_cursor_update_msii);

Line: 5895

              Write_Debug('load_item_oper_attr_values: Internal value errors while update msii ' || l_dyn_sql);

Line: 5910

        DBMS_SQL.CLOSE_CURSOR(l_cursor_update_msii);

Line: 5920

      l_user_attr_data_table.DELETE;

Line: 5930

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

Line: 5934

   l_dyn_sql := l_dyn_sql || ', LAST_UPDATE_DATE   ';

Line: 5935

   l_dyn_sql := l_dyn_sql || ', LAST_UPDATED_BY  ';

Line: 5938

   l_dyn_sql := l_dyn_sql || ', LAST_UPDATE_LOGIN  ';

Line: 5945

   l_dyn_sql := l_dyn_sql || ', PROGRAM_UPDATE_DATE  ';

Line: 5951

   l_dyn_sql := l_dyn_sql || 'SELECT ';

Line: 5987

   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: 6044

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

Line: 6097

  ' 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: 6115

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

Line: 6178

    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: 6194

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

Line: 6200

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

Line: 6350

          delete_records_from_MSII(p_data_set_id);

Line: 6359

          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: 6522

   /* 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: 6560

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

Line: 6637

      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: 6670

  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: 6699

   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: 6706

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

Line: 6712

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

Line: 6722

     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: 6735

     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: 6748

     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: 6761

     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: 6988

                  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: 6997

                  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: 7005

                  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: 7011

                  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: 7017

                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: 7024

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

Line: 7032

                  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: 7039

                  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: 7046

                  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: 7053

                  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: 7059

                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: 7066

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

Line: 7171

  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: 7188

    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: 7212

        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: 7242

        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: 7252

        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: 7262

        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: 7272

        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: 7282

        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: 7292

        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: 7300

        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: 7308

        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: 7316

        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: 7324

        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: 7332

        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: 7340

        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: 7348

        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: 7356

        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: 7364

        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: 7408

    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: 7468

     SELECT mtl_system_items_intf_sets_s.NEXTVAL
       INTO l_msii_set_process_id
     FROM dual;

Line: 7567

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

Line: 7573

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

Line: 7579

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

Line: 7585

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

Line: 7598

     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: 7605

     l_dyn_sql :=              'INSERT INTO EGO_ITEM_ASSOCIATIONS_INTF ';

Line: 7622

     l_dyn_sql := l_dyn_sql || ' LAST_UPDATED_BY          ,   ';

Line: 7623

     l_dyn_sql := l_dyn_sql || ' LAST_UPDATE_DATE         ,   ';

Line: 7624

     l_dyn_sql := l_dyn_sql || ' LAST_UPDATE_LOGIN        ,   ';

Line: 7628

     l_dyn_sql := l_dyn_sql || ' PROGRAM_UPDATE_DATE      ,   ';

Line: 7634

     l_dyn_sql := l_dyn_sql || 'SELECT ';

Line: 7671

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

Line: 7672

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

Line: 7673

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

Line: 7677

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

Line: 7717

      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: 7724

      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: 7731

      l_dyn_sql :=              'INSERT INTO EGO_ITEM_ASSOCIATIONS_INTF ';

Line: 7750

      l_dyn_sql := l_dyn_sql || ' LAST_UPDATED_BY          ,   ';

Line: 7751

      l_dyn_sql := l_dyn_sql || ' LAST_UPDATE_DATE         ,   ';

Line: 7752

      l_dyn_sql := l_dyn_sql || ' LAST_UPDATE_LOGIN        ,   ';

Line: 7756

      l_dyn_sql := l_dyn_sql || ' PROGRAM_UPDATE_DATE      ,   ';

Line: 7762

      l_dyn_sql := l_dyn_sql || 'SELECT ';

Line: 7767

      l_dyn_sql := l_dyn_sql || 'SELECT ';

Line: 7805

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

Line: 7806

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

Line: 7807

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

Line: 7811

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

Line: 7829

      l_dyn_sql := l_dyn_sql || ' SELECT ';

Line: 7867

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

Line: 7868

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

Line: 7869

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

Line: 7873

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