DBA Data[Home] [Help]

APPS.BOM_BULKLOAD_PVT_PKG SQL Statements

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

Line: 79

  SELECT
    COUNT(RESULTFMT_USAGE_ID) into l_unprocessed_rowcount
  FROM
    EGO_BULKLOAD_INTF E
  where
    E.RESULTFMT_USAGE_ID = p_resultfmt_usage_id
    and E.PROCESS_STATUS = 1;
Line: 110

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

  SELECT Bill_Sequence_Id
    INTO l_id
   FROM Bom_Bill_Of_Materials
  WHERE Assembly_Item_Id = p_assembly_item_id
    AND NVL(Alternate_Bom_Designator, 'NONE') =
             DECODE(p_alternate_bom_designator,NULL,'NONE',p_alternate_bom_designator)
      AND Organization_Id = p_organization_id;
Line: 200

  SELECT  Organization_Id
  INTO    l_id
  FROM    Mtl_Parameters
  WHERE   Organization_Code = p_organization;
Line: 261

  UPDATE EGO_BULKLOAD_INTF EBI
   SET  EBI.PROCESS_STATUS =
    (
      SELECT BMI.PROCESS_FLAG
      FROM   BOM_BILL_OF_MTLS_INTERFACE BMI
      WHERE  BMI.TRANSACTION_ID = EBI.TRANSACTION_ID
    )
  WHERE EXISTS
    (
      SELECT 'X'
      FROM   BOM_BILL_OF_MTLS_INTERFACE BMI
      WHERE  BMI.TRANSACTION_ID = EBI.TRANSACTION_ID
    )
    AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;
Line: 277

  UPDATE EGO_BULKLOAD_INTF EBI
   SET  EBI.PROCESS_STATUS =
    (
      SELECT BICI.PROCESS_FLAG
      FROM   BOM_INVENTORY_COMPS_INTERFACE BICI
      WHERE  BICI.TRANSACTION_ID = EBI.TRANSACTION_ID
    )
  WHERE EXISTS
    (
      SELECT 'X'
      FROM   BOM_INVENTORY_COMPS_INTERFACE BICI
      WHERE  BICI.TRANSACTION_ID = EBI.TRANSACTION_ID
    )
    AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;
Line: 541

  l_dyn_sql_select      VARCHAR2(10000);
Line: 542

  l_dyn_sql_insert      VARCHAR2(10000);
Line: 545

  l_cursor_select       INTEGER;
Line: 581

  G_TXN_UPDATE          VARCHAR2(10) := 'UPDATE';
Line: 582

  G_TXN_DELETE          VARCHAR2(10) := 'DELETE';
Line: 656

  G_DEL_GROUP_DESC    VARCHAR2(240) := 'Delete Group for EGO BOM Bulkload Structures';
Line: 683

    SELECT
      erf.Attribute_Code,
      erf.Intf_Column_Name,
      bcc.Bom_Intf_Column_Name ,
      bcc.Bom_Intf_Table_Name,
      bcc.Operation_Type,
      bcc.Lookup_Type
    FROM
      Ego_Results_Fmt_Usages erf,
      Bom_Component_Columns bcc
    WHERE
      (Region_Code = 'BOM_RESULT_DUMMY_REGION'
      OR -- Fix for import Region
      Region_Code = 'BOM_IMPORT_DUMMY_REGION' )
    AND
      Region_Application_Id = 702
    AND
      Customization_Application_Id = 431
    AND
      Resultfmt_Usage_Id = c_Resultfmt_Usage_Id
    AND
      bcc.Attribute_Code = erf.Attribute_Code
    AND
      bcc.OBJECT_TYPE = 'BOM_COMPONENTS'
    AND
      bcc.BOM_INTF_TABLE_NAME =  'BOM_INVENTORY_COMPS_INTERFACE'
    AND
      (   bcc.Parent_Entity IS NULL
      OR  BCC.Attribute_Code = 'ITEM_NUMBER'
      OR  BCC.Attribute_Code = 'PARENT_NAME')
    AND
      erf.Attribute_Code NOT LIKE '%$$%';
Line: 719

    SELECT
      ASSEMBLY_ITEM_ID,
      ORGANIZATION_ID,
      ASSEMBLY_TYPE,
      PROCESS_FLAG,
      ORGANIZATION_CODE,
      COMMON_ORG_CODE,
      ITEM_NUMBER,
      IMPLEMENTATION_DATE
    FROM
      BOM_BILL_OF_MTLS_INTERFACE BMI
    WHERE
      PROCESS_FLAG = 1
    AND
      REQUEST_ID = C_REQUEST_ID
    AND
      BMI.Alternate_Bom_Designator IS NOT NULL
    AND NOT EXISTS
        (SELECT NULL FROM Bom_structures_b bsb
            WHERE bsb.Assembly_Item_id IN (SELECT Inventory_item_id FROM mtl_system_items_vl WHERE concatenated_segments = bmi.Item_number)
           AND bsb.Organization_id = (SELECT ORGANIZATION_ID FROM mtl_parameters WHERE ORGANIZATION_code = bmi.ORGANIZATION_code)
             AND  bsb.Alternate_Bom_Designator IS NULL
        );
Line: 774

    DELETE FROM  EGO_BULKLOAD_INTF
    WHERE RESULTFMT_USAGE_ID = p_resultfmt_usage_id
    AND PROCESS_STATUS <> 1;
Line: 798

    Select INTF_COLUMN_NAME into G_INTF_ORG_CODE FROM Ego_Results_Fmt_Usages erf
    WHERE  Resultfmt_Usage_Id = p_Resultfmt_Usage_Id  AND ATTRIBUTE_CODE = 'ORGANIZATION_CODE';
Line: 802

        Select INTF_COLUMN_NAME into G_INTF_COMP_SEQ_ID FROM Ego_Results_Fmt_Usages erf
        WHERE  Resultfmt_Usage_Id = p_Resultfmt_Usage_Id  AND ATTRIBUTE_CODE = 'COMPONENT_SEQUENCE_ID';
Line: 810

      Select INTF_COLUMN_NAME into G_INTF_ASSEMBLY_TYPE FROM Ego_Results_Fmt_Usages erf
      WHERE  Resultfmt_Usage_Id = p_Resultfmt_Usage_Id  AND ATTRIBUTE_CODE = 'SUB_ASSEMBLY_TYPE';
Line: 818

      Select INTF_COLUMN_NAME into G_INTF_PARENT_REVISION FROM Ego_Results_Fmt_Usages erf
      WHERE  Resultfmt_Usage_Id = p_Resultfmt_Usage_Id  AND ATTRIBUTE_CODE = 'PARENT_REVISION_CODE';
Line: 826

      Select INTF_COLUMN_NAME into G_INTF_REVISION FROM Ego_Results_Fmt_Usages erf
      WHERE  Resultfmt_Usage_Id = p_Resultfmt_Usage_Id  AND ATTRIBUTE_CODE = 'REVISION';
Line: 844

    l_dyn_sql := l_dyn_sql || ' UPDATE EGO_BULKLOAD_INTF SET '||G_INTF_COMP_SEQ_ID||' = NULL ';
Line: 854

        Select INTF_COLUMN_NAME into G_INTF_REF_DESIG FROM Ego_Results_Fmt_Usages erf
        WHERE  Resultfmt_Usage_Id = p_Resultfmt_Usage_Id  AND ATTRIBUTE_CODE = 'COMPONENT_REFERENCE_DESIGNATOR';
Line: 864

    UPDATE EGO_BULKLOAD_INTF
     SET  Transaction_Id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
    WHERE  Resultfmt_Usage_Id = p_Resultfmt_Usage_Id AND PROCESS_STATUS = 1 ;
Line: 924

   l_dyn_sql :=              'UPDATE EGO_BULKLOAD_INTF EBI';
Line: 927

   l_dyn_sql := l_dyn_sql || '    SELECT ORGANIZATION_ID                    ';
Line: 942

   l_dyn_sql :=              'UPDATE EGO_BULKLOAD_INTF EBI';
Line: 945

   l_dyn_sql := l_dyn_sql || '    SELECT inventory_item_id                  ';
Line: 961

   l_dyn_sql :=              'UPDATE EGO_BULKLOAD_INTF EBI';
Line: 964

   l_dyn_sql := l_dyn_sql || '    SELECT inventory_item_id                  ';
Line: 980

   l_dyn_sql :=              'UPDATE EGO_BULKLOAD_INTF EBI';
Line: 983

   l_dyn_sql := l_dyn_sql || '    SELECT bill_sequence_id                  ';
Line: 1003

     l_dyn_sql :=              'UPDATE EGO_BULKLOAD_INTF EBI SET';
Line: 1046

     l_dyn_sql :=              'UPDATE EGO_BULKLOAD_INTF EBI';
Line: 1049

     l_dyn_sql := l_dyn_sql || '    SELECT COMPONENT_SEQUENCE_ID                  ';
Line: 1066

    l_dyn_sql_insert := '';
Line: 1067

    l_dyn_sql_insert := l_dyn_sql_insert || 'INSERT INTO BOM_BILL_OF_MTLS_INTERFACE (BATCH_ID';
Line: 1068

    l_dyn_sql_insert := l_dyn_sql_insert || ' , SOURCE_SYSTEM_REFERENCE ';
Line: 1069

    l_dyn_sql_insert := l_dyn_sql_insert || ' , SOURCE_SYSTEM_REFERENCE_DESC ';
Line: 1070

    l_dyn_sql_insert := l_dyn_sql_insert || ' , REQUEST_ID, Transaction_Type ';
Line: 1071

    l_dyn_sql_insert := l_dyn_sql_insert || ' , Transaction_Id, Process_Flag, Item_Number ';
Line: 1072

    l_dyn_sql_insert := l_dyn_sql_insert || ' , Organization_Code, Alternate_Bom_Designator, Structure_Type_Name, Effectivity_Control, Is_Preferred, assembly_type, Revision) ';
Line: 1074

    l_dyn_sql_select := '';
Line: 1075

    l_dyn_sql_select := l_dyn_sql_select || ' SELECT ' || P_BATCH_ID || ', C_FIX_COLUMN12,  C_FIX_COLUMN13 ';
Line: 1076

    l_dyn_sql_select := l_dyn_sql_select || ' , REQUEST_ID, Transaction_Type, Transaction_Id, 1, ' || l_item_col_name;
Line: 1077

    l_dyn_sql_select := l_dyn_sql_select || ' , ' || G_INTF_ORG_CODE;
Line: 1078

    l_dyn_sql_select := l_dyn_sql_select || ' , DECODE(' || 'DECODE(' || G_INTF_STRUCT_NAME
                                         || ',null,(select decode(structure_name,BOM_GLOBALS.GET_PRIMARY_UI,null,structure_name) from ego_import_option_sets where batch_id = '
                                         ||' :1 ),'|| G_INTF_STRUCT_NAME|| ')'
                                         || ',Bom_Globals.Retrieve_Message(''BOM'', ''BOM_PRIMARY''),NULL,'
                                         || 'DECODE(' || G_INTF_STRUCT_NAME || ',null,(select decode(structure_name,BOM_GLOBALS.GET_PRIMARY_UI,null,structure_name) from ego_import_option_sets where batch_id = '
                                         ||' :2 ),'|| G_INTF_STRUCT_NAME|| ')' || ')';
Line: 1084

    l_dyn_sql_select := l_dyn_sql_select || ' , ' || 'DECODE(' || G_INTF_STR_TYPE_NAME
                                         ||',null,(SELECT BSTV.structure_type_name from bom_structure_types_vl BSTV,ego_import_option_sets EIOS WHERE EIOS.batch_id =  '
                                         || ' :3 AND BSTV.structure_type_id = EIOS.structure_type_id ),' || G_INTF_STR_TYPE_NAME || ' )';
Line: 1087

    l_dyn_sql_select := l_dyn_sql_select || ' , (SELECT Lookup_Code FROM Fnd_Lookup_Values  WHERE Lookup_Type = ''BOM_EFFECTIVITY_CONTROL'' AND LANGUAGE=USERENV(''LANG'') AND Meaning=' || G_INTF_EFFEC_CONTROL || ')';
Line: 1088

    l_dyn_sql_select := l_dyn_sql_select || ' , (SELECT Lookup_Code FROM Fnd_Lookup_Values  WHERE Lookup_Type = ''EGO_YES_NO'' AND LANGUAGE=USERENV(''LANG'') AND Meaning = ' || G_INTF_IS_PREFERRED ||  ')';
Line: 1089

    l_dyn_sql_select := l_dyn_sql_select || ' , (SELECT Lookup_Code FROM Mfg_Lookups  WHERE Lookup_Type = ''BOM_ASSEMBLY_TYPE'' AND Meaning=' || G_INTF_ASSEMBLY_TYPE || ')';
Line: 1090

    l_dyn_sql_select := l_dyn_sql_select || ' , ' || G_INTF_REVISION || ' ';
Line: 1091

    l_dyn_sql_select := l_dyn_sql_select || ' FROM EGO_BULKLOAD_INTF WHERE RESULTFMT_USAGE_ID = :4 ';
Line: 1094

      l_dyn_sql_select := l_dyn_sql_select || ' AND  PROCESS_STATUS = 1 AND ' || l_parent_column || ' IS NULL ';
Line: 1096

       l_dyn_sql_select := l_dyn_sql_select || ' AND  PROCESS_STATUS = 1 AND ' ||   G_INTF_SRCSYS_PARENT || ' IS NULL ';
Line: 1099

    l_dyn_sql := l_dyn_sql_insert || ' ' || l_dyn_sql_select;
Line: 1113

    l_dyn_sql_cursor := l_dyn_sql_cursor || ' SELECT Distinct ' ;
Line: 1117

                                         || ',null,(select decode(structure_name,BOM_GLOBALS.GET_PRIMARY_UI,null,structure_name) from ego_import_option_sets where batch_id =  '
                                         || ' :1 ),'|| G_INTF_STRUCT_NAME|| ')'  || ', Bom_Globals.Retrieve_Message(''BOM'', ''BOM_PRIMARY''),NULL,'
                                         || 'DECODE(' || G_INTF_STRUCT_NAME || ',null,(select decode(structure_name,BOM_GLOBALS.GET_PRIMARY_UI,null,structure_name) from ego_import_option_sets where batch_id = '
                                         ||' :2 ),'|| G_INTF_STRUCT_NAME|| ')'
                                         || ')';
Line: 1123

                                         ||',null,(SELECT BSTV.structure_type_name from bom_structure_types_vl BSTV,ego_import_option_sets EIOS WHERE EIOS.batch_id = '
                                         || ' :3  AND BSTV.structure_type_id = EIOS.structure_type_id ),' || G_INTF_STR_TYPE_NAME || ' )';
Line: 1149

    l_cursor_select := Dbms_Sql.Open_Cursor;
Line: 1150

    Dbms_Sql.Parse(l_cursor_select, l_dyn_sql_cursor, Dbms_Sql.NATIVE);
Line: 1151

    Dbms_Sql.Define_Column(l_cursor_select, 1, L_SRCSYS_PARENT,3000);
Line: 1152

    Dbms_Sql.Define_Column(l_cursor_select, 2, L_PARENT_NAME, 3000);
Line: 1153

    Dbms_Sql.Define_Column(l_cursor_select, 3, L_ORGANIZATION_CODE, 10);
Line: 1154

    Dbms_Sql.Define_Column(l_cursor_select, 4, L_STRUCTURE_NAME, 240);
Line: 1155

    Dbms_Sql.Define_Column(l_cursor_select, 5, L_STR_TYPE_NAME, 240);
Line: 1156

    Dbms_Sql.Define_Column(l_cursor_select, 6, L_EFFEC_CONTROL, 240);
Line: 1157

    Dbms_Sql.Define_Column(l_cursor_select, 7, L_IS_PREF_MEANING, 80);
Line: 1158

    Dbms_Sql.Define_Column(l_cursor_select, 8, L_ASSTYPE_MEANING, 80);
Line: 1159

    Dbms_Sql.Define_Column(l_cursor_select, 9, L_PARENT_REVISION, 80);
Line: 1160

    Dbms_Sql.Define_Column(l_cursor_select, 10, L_TRANSACTION_ID);
Line: 1162

    Dbms_Sql.Bind_Variable(l_cursor_select,':1', p_batch_id);
Line: 1163

    Dbms_Sql.Bind_Variable(l_cursor_select,':2', p_batch_id);
Line: 1164

    Dbms_Sql.Bind_Variable(l_cursor_select,':3', p_batch_id);
Line: 1165

    Dbms_Sql.Bind_Variable(l_cursor_select,':4', p_resultfmt_usage_id);
Line: 1171

    l_cursor_execute := Dbms_Sql.EXECUTE(l_cursor_select);
Line: 1178

      IF (Dbms_Sql.Fetch_Rows(l_cursor_select) > 0) THEN
        Dbms_Sql.Column_Value(l_cursor_select,1,L_SRCSYS_PARENT);
Line: 1180

        Dbms_Sql.Column_Value(l_cursor_select,2,L_PARENT_NAME);
Line: 1181

        Dbms_Sql.Column_Value(l_cursor_select,3,L_ORGANIZATION_CODE);
Line: 1182

        Dbms_Sql.Column_Value(l_cursor_select,4,L_STRUCTURE_NAME);
Line: 1183

        Dbms_Sql.Column_Value(l_cursor_select,5,L_STR_TYPE_NAME);
Line: 1184

        Dbms_Sql.Column_Value(l_cursor_select,6,L_EFFEC_CONTROL);
Line: 1185

        Dbms_Sql.Column_Value(l_cursor_select,7,L_IS_PREF_MEANING);
Line: 1186

        Dbms_Sql.Column_Value(l_cursor_select,8,L_ASSTYPE_MEANING);
Line: 1187

        Dbms_Sql.Column_Value(l_cursor_select,9,L_PARENT_REVISION);
Line: 1188

        Dbms_Sql.Column_Value(l_cursor_select,10,L_TRANSACTION_ID);
Line: 1192

          SELECT
            Lookup_Code
          INTO
            l_eff_ctrl
          FROM
            Fnd_Lookup_Values
          WHERE
            Lookup_Type = 'BOM_EFFECTIVITY_CONTROL' AND LANGUAGE=USERENV('LANG') AND Meaning=L_EFFEC_CONTROL;
Line: 1205

        SELECT
          Lookup_Code
        INTO
          l_is_preferred
        FROM
          Fnd_Lookup_Values
        WHERE
          Lookup_Type = 'EGO_YES_NO' AND LANGUAGE=USERENV('LANG') AND Meaning=L_IS_PREF_MEANING;
Line: 1219

        SELECT
          Lookup_Code
        INTO
          l_assemblytype
        FROM
          Mfg_Lookups
        WHERE
          Lookup_Type = 'BOM_ASSEMBLY_TYPE' AND Meaning=L_ASSTYPE_MEANING;
Line: 1238

          INSERT INTO BOM_BILL_OF_MTLS_INTERFACE (
            BATCH_ID,
            REQUEST_ID,
            TRANSACTION_TYPE,
            TRANSACTION_ID,
            PROCESS_FLAG,
            ITEM_NUMBER,
            ORGANIZATION_CODE,
            ALTERNATE_BOM_DESIGNATOR,
            STRUCTURE_TYPE_NAME,
            EFFECTIVITY_CONTROL,
            IS_PREFERRED ,
            assembly_type,
            REVISION,
            SOURCE_SYSTEM_REFERENCE )
        select
            p_batch_id,
            G_REQUEST_ID,
            G_TXN_SYNC,
            L_TRANSACTION_ID,
            1,
            L_PARENT_NAME,
            L_ORGANIZATION_CODE,
            L_STRUCTURE_NAME,
            L_STR_TYPE_NAME,
            l_eff_ctrl,
            l_is_preferred,
            l_assemblytype,
            L_PARENT_REVISION,
            L_SRCSYS_PARENT from dual
         where not exists
         (select 'X' from bom_bill_of_mtls_interface
          where
              batch_id    = p_batch_id
          and request_id  = g_request_id
          and process_flag = 1
          and ( (ITEM_NUMBER IS NOT NULL AND ITEM_NUMBER = L_PARENT_NAME) OR  (SOURCE_SYSTEM_REFERENCE IS NOT NULL AND SOURCE_SYSTEM_REFERENCE=L_SRCSYS_PARENT))
          and ORGANIZATION_CODE = L_ORGANIZATION_CODE
          and nvl(ALTERNATE_BOM_DESIGNATOR,'000') = nvl(L_STRUCTURE_NAME,'000')
          and nvl(STRUCTURE_TYPE_NAME,'000') = nvl(L_STR_TYPE_NAME,'000')
          and nvl(EFFECTIVITY_CONTROL,'000') = nvl(l_eff_ctrl,'000') );
Line: 1281

          INSERT INTO BOM_BILL_OF_MTLS_INTERFACE (
                    BATCH_ID,
                    REQUEST_ID,
                    TRANSACTION_TYPE,
                    TRANSACTION_ID,
                    PROCESS_FLAG,
                    ITEM_NUMBER,
                    ORGANIZATION_CODE,
                    ALTERNATE_BOM_DESIGNATOR,
                    STRUCTURE_TYPE_NAME,
                    EFFECTIVITY_CONTROL,
                    IS_PREFERRED ,
                    assembly_type,
                    REVISION,
                    SOURCE_SYSTEM_REFERENCE )
                select
                    p_batch_id,
                    G_REQUEST_ID,
                    G_TXN_NO_OP,
                    L_TRANSACTION_ID,
                    1,
                    L_PARENT_NAME,
                    L_ORGANIZATION_CODE,
                    L_STRUCTURE_NAME,
                    L_STR_TYPE_NAME,
                    l_eff_ctrl,
                    l_is_preferred,
                    l_assemblytype,
                    L_PARENT_REVISION,
                    L_SRCSYS_PARENT from dual
         where not exists
         (select 'X' from bom_bill_of_mtls_interface
          where
              batch_id    = p_batch_id
          and request_id  = g_request_id
          and process_flag = 1
          and ( (ITEM_NUMBER IS NOT NULL AND ITEM_NUMBER = L_PARENT_NAME) OR  (SOURCE_SYSTEM_REFERENCE IS NOT NULL AND SOURCE_SYSTEM_REFERENCE=L_SRCSYS_PARENT))
          and ORGANIZATION_CODE = L_ORGANIZATION_CODE
          and nvl(ALTERNATE_BOM_DESIGNATOR,'000') = nvl(L_STRUCTURE_NAME,'000')
          and nvl(STRUCTURE_TYPE_NAME,'000') = nvl(L_STR_TYPE_NAME,'000')
          and nvl(EFFECTIVITY_CONTROL,'000') = nvl(l_eff_ctrl,'000') );
Line: 1332

    l_dyn_sql_insert := '';
Line: 1333

    l_dyn_sql_insert := l_dyn_sql_insert || 'INSERT INTO BOM_INVENTORY_COMPS_INTERFACE ( BATCH_ID, ';
Line: 1334

    l_dyn_sql_insert := l_dyn_sql_insert || ' COMP_SOURCE_SYSTEM_REFERENCE, COMP_SOURCE_SYSTEM_REFER_DESC,';
Line: 1335

    l_dyn_sql_insert := l_dyn_sql_insert || ' PARENT_SOURCE_SYSTEM_REFERENCE, REQUEST_ID, Transaction_Type,';
Line: 1336

    l_dyn_sql_insert := l_dyn_sql_insert || ' Transaction_Id, Process_Flag, ';
Line: 1337

    l_dyn_sql_insert := l_dyn_sql_insert || 'ORGANIZATION_CODE, ALTERNATE_BOM_DESIGNATOR, COMPONENT_SEQUENCE_ID, PARENT_REVISION_CODE, ';
Line: 1338

    l_dyn_sql_select := '';
Line: 1339

    l_dyn_sql_select := l_dyn_sql_select || 'SELECT ' || P_BATCH_ID || ' , ' || G_INTF_SRCSYS_COMPONENT ;
Line: 1340

    l_dyn_sql_select := l_dyn_sql_select || ', ' || G_INTF_SRCSYS_DESCRIPTION || ' , ' || G_INTF_SRCSYS_PARENT ;
Line: 1341

    l_dyn_sql_select := l_dyn_sql_select || ' , REQUEST_ID, TRANSACTION_TYPE, Transaction_Id, 1, ' || G_INTF_ORG_CODE || ', ';
Line: 1342

    l_dyn_sql_select := l_dyn_sql_select || ' DECODE(' || 'DECODE(' || G_INTF_STRUCT_NAME
                                         || ',null,(select decode(structure_name,BOM_GLOBALS.GET_PRIMARY_UI,null,structure_name) from ego_import_option_sets where batch_id = '
                                         || ' :1 ),'|| G_INTF_STRUCT_NAME|| ')'  || ', Bom_Globals.Retrieve_Message(''BOM'', ''BOM_PRIMARY''),NULL,'
                                         || 'DECODE(' || G_INTF_STRUCT_NAME || ',null,(select decode(structure_name,BOM_GLOBALS.GET_PRIMARY_UI,null,structure_name) from ego_import_option_sets where batch_id = '
                                         ||' :2 ),'|| G_INTF_STRUCT_NAME|| ')) ,';
Line: 1347

    l_dyn_sql_select := l_dyn_sql_select || G_INTF_COMP_SEQ_ID || ', ' || G_INTF_PARENT_REVISION || ', ';
Line: 1357

          l_dyn_sql_insert := l_dyn_sql_insert || 'NEW_EFFECTIVITY_DATE,';
Line: 1371

          l_str := l_str || ', ( SELECT Effectivity_Date FROM Bom_inventory_Components WHERE Component_Sequence_Id = ' || G_INTF_COMP_SEQ_ID || ')),';
Line: 1373

          l_dyn_sql_select := l_dyn_sql_select || l_str;
Line: 1376

              l_dyn_sql_insert := l_dyn_sql_insert || 'NEW_OPERATION_SEQ_NUM,';
Line: 1378

              l_dyn_sql_insert := l_dyn_sql_insert || 'NEW_FROM_END_ITEM_UNIT_NUMBER,';
Line: 1380

              l_dyn_sql_insert := l_dyn_sql_insert || l_bom_col_name(i) || ',';
Line: 1384

              l_dyn_sql_select := l_dyn_sql_select || 'TO_DATE(' || l_intf_col_name_tbl(i) || ',''DD-MON-YYYY HH24:MI:SS''),';
Line: 1386

              l_str := '(SELECT LOOKUP_CODE FROM FND_LOOKUP_VALUES WHERE ';
Line: 1388

              l_dyn_sql_select := l_dyn_sql_select || l_str;
Line: 1390

              l_dyn_sql_select := l_dyn_sql_select || l_intf_col_name_tbl(i) || ',';
Line: 1397

    l_dyn_sql_insert := SUBSTR(l_dyn_sql_insert,0,LENGTH(l_dyn_sql_insert) - 1);
Line: 1398

    l_dyn_sql_select := SUBSTR(l_dyn_sql_select,0,LENGTH(l_dyn_sql_select) - 1);
Line: 1399

    l_dyn_sql_insert := l_dyn_sql_insert || ' ) ';
Line: 1400

    l_dyn_sql_select := l_dyn_sql_select || ' FROM EGO_BULKLOAD_INTF ';
Line: 1401

    l_dyn_sql_select := l_dyn_sql_select || ' WHERE RESULTFMT_USAGE_ID = :3 ';
Line: 1402

    l_dyn_sql_select := l_dyn_sql_select || ' AND PROCESS_STATUS = 1 AND ';
Line: 1405

     l_dyn_sql_select := l_dyn_sql_select || l_parent_column || ' IS NOT NULL ';
Line: 1407

       l_dyn_sql_select := l_dyn_sql_select || G_INTF_SRCSYS_PARENT || ' IS NOT NULL ';
Line: 1411

    l_dyn_sql_select := l_dyn_sql_select || ' AND ( UPPER(Transaction_Type) = ''' || G_TXN_UPDATE || ''' OR UPPER(Transaction_Type) = ''' || G_TXN_SYNC || ''') ';
Line: 1414

    l_dyn_sql := l_dyn_sql_insert || ' ' || l_dyn_sql_select;
Line: 1432

    Dbms_Sql.Close_Cursor(l_cursor_select);
Line: 1435

    l_dyn_sql_insert := '';
Line: 1436

    l_dyn_sql_insert := l_dyn_sql_insert || 'INSERT INTO BOM_INVENTORY_COMPS_INTERFACE ( BATCH_ID, ';
Line: 1437

    l_dyn_sql_insert := l_dyn_sql_insert || ' COMP_SOURCE_SYSTEM_REFERENCE, COMP_SOURCE_SYSTEM_REFER_DESC,';
Line: 1438

    l_dyn_sql_insert := l_dyn_sql_insert || ' PARENT_SOURCE_SYSTEM_REFERENCE, REQUEST_ID, Transaction_Type,';
Line: 1439

    l_dyn_sql_insert := l_dyn_sql_insert || ' Transaction_Id, Process_Flag, ';
Line: 1440

    l_dyn_sql_insert := l_dyn_sql_insert || 'ORGANIZATION_CODE, ALTERNATE_BOM_DESIGNATOR, COMPONENT_SEQUENCE_ID, PARENT_REVISION_CODE, ';
Line: 1441

    l_dyn_sql_select := '';
Line: 1442

    l_dyn_sql_select := l_dyn_sql_select || 'SELECT ' || P_BATCH_ID || ' , ' || G_INTF_SRCSYS_COMPONENT ;
Line: 1443

    l_dyn_sql_select := l_dyn_sql_select || ', ' || G_INTF_SRCSYS_DESCRIPTION || ' , ' || G_INTF_SRCSYS_PARENT ;
Line: 1444

    l_dyn_sql_select := l_dyn_sql_select || ' , REQUEST_ID, ''' || G_TXN_CREATE || ''' , Transaction_Id, 1, ' || G_INTF_ORG_CODE || ', ';
Line: 1445

    l_dyn_sql_select := l_dyn_sql_select || ' DECODE(' || 'DECODE(' || G_INTF_STRUCT_NAME
                                         || ',null,(select decode(structure_name,BOM_GLOBALS.GET_PRIMARY_UI,null,structure_name) from ego_import_option_sets where batch_id = '
                                         || ' :1 ),'|| G_INTF_STRUCT_NAME|| ')'  || ', Bom_Globals.Retrieve_Message(''BOM'', ''BOM_PRIMARY''),NULL,'
                                         || 'DECODE(' || G_INTF_STRUCT_NAME || ',null,(select decode(structure_name,BOM_GLOBALS.GET_PRIMARY_UI,null,structure_name) from ego_import_option_sets where batch_id = '
                                         ||' :2 ),'|| G_INTF_STRUCT_NAME|| ')) ,';
Line: 1450

    l_dyn_sql_select := l_dyn_sql_select || G_INTF_COMP_SEQ_ID || ', ' || G_INTF_PARENT_REVISION || ', ';
Line: 1454

        l_dyn_sql_insert := l_dyn_sql_insert || l_bom_col_name(i) || ',';
Line: 1458

          l_dyn_sql_select := l_dyn_sql_select || 'TO_DATE(' || l_intf_col_name_tbl(i) || ',''DD-MON-YYYY HH24:MI:SS''),';
Line: 1460

          l_str := '(SELECT LOOKUP_CODE FROM FND_LOOKUP_VALUES WHERE ';
Line: 1462

          l_dyn_sql_select := l_dyn_sql_select || l_str;
Line: 1464

          l_dyn_sql_select := l_dyn_sql_select || l_intf_col_name_tbl(i) || ',';
Line: 1470

         l_dyn_sql_insert := l_dyn_sql_insert || 'NEW_EFFECTIVITY_DATE,';
Line: 1471

         l_dyn_sql_select := l_dyn_sql_select || 'TO_DATE(' || l_intf_col_name_tbl(i) || ',''DD-MON-YYYY HH24:MI:SS''),';
Line: 1478

    l_dyn_sql_insert := SUBSTR(l_dyn_sql_insert,0,LENGTH(l_dyn_sql_insert) - 1);
Line: 1479

    l_dyn_sql_select := SUBSTR(l_dyn_sql_select,0,LENGTH(l_dyn_sql_select) - 1);
Line: 1480

    l_dyn_sql_insert := l_dyn_sql_insert || ' ) ';
Line: 1481

    l_dyn_sql_select := l_dyn_sql_select || ' FROM EGO_BULKLOAD_INTF ';
Line: 1482

    l_dyn_sql_select := l_dyn_sql_select || ' WHERE RESULTFMT_USAGE_ID = :3 ';
Line: 1483

    l_dyn_sql_select := l_dyn_sql_select || ' AND PROCESS_STATUS = 1 AND ';
Line: 1486

     l_dyn_sql_select := l_dyn_sql_select || l_parent_column || ' IS NOT NULL ';
Line: 1488

       l_dyn_sql_select := l_dyn_sql_select || G_INTF_SRCSYS_PARENT || ' IS NOT NULL ';
Line: 1492

    l_dyn_sql_select := l_dyn_sql_select || ' AND ( UPPER(Transaction_Type) = ''' || G_TXN_CREATE || ''' OR  UPPER(Transaction_Type) = ''' || G_TXN_ADD || ''') ';
Line: 1498

    l_dyn_sql := l_dyn_sql_insert || ' ' || l_dyn_sql_select;
Line: 1512

    l_dyn_sql_insert := '';
Line: 1513

    l_dyn_sql_insert := l_dyn_sql_insert || 'INSERT INTO BOM_INVENTORY_COMPS_INTERFACE ( BATCH_ID, ';
Line: 1514

    l_dyn_sql_insert := l_dyn_sql_insert || ' COMP_SOURCE_SYSTEM_REFERENCE, COMP_SOURCE_SYSTEM_REFER_DESC,';
Line: 1515

    l_dyn_sql_insert := l_dyn_sql_insert || ' PARENT_SOURCE_SYSTEM_REFERENCE, REQUEST_ID, Transaction_Type,';
Line: 1516

    l_dyn_sql_insert := l_dyn_sql_insert || ' Transaction_Id, Process_Flag, ';
Line: 1517

    l_dyn_sql_insert := l_dyn_sql_insert || 'ORGANIZATION_CODE, ALTERNATE_BOM_DESIGNATOR, COMPONENT_SEQUENCE_ID, ';
Line: 1518

    l_dyn_sql_insert := l_dyn_sql_insert || 'DELETE_GROUP_NAME, DG_DESCRIPTION, ';
Line: 1519

    l_dyn_sql_select := '';
Line: 1520

    l_dyn_sql_select := l_dyn_sql_select || 'SELECT ' || P_BATCH_ID || ' , ' || G_INTF_SRCSYS_COMPONENT ;
Line: 1521

    l_dyn_sql_select := l_dyn_sql_select || ', ' || G_INTF_SRCSYS_DESCRIPTION || ' , ' || G_INTF_SRCSYS_PARENT ;
Line: 1522

    l_dyn_sql_select := l_dyn_sql_select || ' , REQUEST_ID, TRANSACTION_TYPE, Transaction_Id, 1, ' || G_INTF_ORG_CODE || ', ';
Line: 1523

    l_dyn_sql_select := l_dyn_sql_select || ' DECODE(' || 'DECODE(' || G_INTF_STRUCT_NAME
                                         || ',null,(select decode(structure_name,BOM_GLOBALS.GET_PRIMARY_UI,null,structure_name) from ego_import_option_sets where batch_id = '
                                         || ' :1 ),'|| G_INTF_STRUCT_NAME|| ')'  || ', Bom_Globals.Retrieve_Message(''BOM'', ''BOM_PRIMARY''),NULL,'
                                         || 'DECODE(' || G_INTF_STRUCT_NAME || ',null,(select decode(structure_name,BOM_GLOBALS.GET_PRIMARY_UI,null,structure_name) from ego_import_option_sets where batch_id = '
                                         ||' :2 ),'|| G_INTF_STRUCT_NAME|| ')) ,';
Line: 1528

    l_dyn_sql_select := l_dyn_sql_select || G_INTF_COMP_SEQ_ID || ', ';
Line: 1529

    l_dyn_sql_select := l_dyn_sql_select || '''' || G_DEL_GROUP_NAME || ''', ''' || G_DEL_GROUP_DESC || ''', ' ;
Line: 1533

        l_dyn_sql_insert := l_dyn_sql_insert || l_bom_col_name(i) || ',';
Line: 1537

          l_dyn_sql_select := l_dyn_sql_select || 'TO_DATE(' || l_intf_col_name_tbl(i) || ',''DD-MON-YYYY HH24:MI:SS''),';
Line: 1539

          l_str := '(SELECT LOOKUP_CODE FROM FND_LOOKUP_VALUES WHERE ';
Line: 1541

          l_dyn_sql_select := l_dyn_sql_select || l_str;
Line: 1543

          l_dyn_sql_select := l_dyn_sql_select || l_intf_col_name_tbl(i) || ',';
Line: 1549

    l_dyn_sql_insert := SUBSTR(l_dyn_sql_insert,0,LENGTH(l_dyn_sql_insert) - 1);
Line: 1550

    l_dyn_sql_select := SUBSTR(l_dyn_sql_select,0,LENGTH(l_dyn_sql_select) - 1);
Line: 1551

    l_dyn_sql_insert := l_dyn_sql_insert || ' ) ';
Line: 1552

    l_dyn_sql_select := l_dyn_sql_select || ' FROM EGO_BULKLOAD_INTF ';
Line: 1553

    l_dyn_sql_select := l_dyn_sql_select || ' WHERE RESULTFMT_USAGE_ID = :3 ';
Line: 1554

    l_dyn_sql_select := l_dyn_sql_select || ' AND PROCESS_STATUS = 1 AND ';
Line: 1557

     l_dyn_sql_select := l_dyn_sql_select || l_parent_column || ' IS NOT NULL ';
Line: 1559

       l_dyn_sql_select := l_dyn_sql_select || G_INTF_SRCSYS_PARENT || ' IS NOT NULL ';
Line: 1563

    l_dyn_sql_select := l_dyn_sql_select || ' AND UPPER(Transaction_Type) = ''' || G_TXN_DELETE || ''' ';
Line: 1565

    l_dyn_sql := l_dyn_sql_insert || ' ' || l_dyn_sql_select;
Line: 1575

      FND_FILE.PUT_LINE( FND_FILE.LOG,'Before UPdate of AssemblyType');
Line: 1579

    UPDATE BOM_BILL_OF_MTLS_INTERFACE SET assembly_type = 2
    WHERE assembly_type IS NULL
    AND batch_id = p_batch_id;
Line: 1584

      FND_FILE.PUT_LINE( FND_FILE.LOG,'After UPdate of AssemblyType');
Line: 1595

      INSERT INTO BOM_BILL_OF_MTLS_INTERFACE
      (
        ASSEMBLY_ITEM_ID,
        ORGANIZATION_ID,
        ASSEMBLY_TYPE,
        PROCESS_FLAG,
        ORGANIZATION_CODE,
        COMMON_ORG_CODE,
        ITEM_NUMBER,
        IMPLEMENTATION_DATE,
        ALTERNATE_BOM_DESIGNATOR,
        TRANSACTION_TYPE,
        REQUEST_ID)
      VALUES
      (
        C_BOM_BILL_PRIMARY_REC.ASSEMBLY_ITEM_ID,
        C_BOM_BILL_PRIMARY_REC.ORGANIZATION_ID,
        C_BOM_BILL_PRIMARY_REC.ASSEMBLY_TYPE,
        C_BOM_BILL_PRIMARY_REC.PROCESS_FLAG,
        C_BOM_BILL_PRIMARY_REC.ORGANIZATION_CODE,
        C_BOM_BILL_PRIMARY_REC.COMMON_ORG_CODE,
        C_BOM_BILL_PRIMARY_REC.ITEM_NUMBER,
        C_BOM_BILL_PRIMARY_REC.IMPLEMENTATION_DATE,
        NULL,
        G_TXN_CREATE,
        G_REQUEST_ID);
Line: 1624

    UPDATE BOM_BILL_OF_MTLS_INTERFACE
     SET  Transaction_Id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
    WHERE  REQUEST_ID = G_REQUEST_ID AND PROCESS_FLAG = 1
    AND Transaction_Id IS NULL;
Line: 1634

  l_upd_sql := ' UPDATE EGO_BULKLOAD_INTF EBI1 ' ||
               ' SET EBI1.transaction_id = ( SELECT EBI2.transaction_id ' ||
                                           ' FROM EGO_BULKLOAD_INTF EBI2 ' ||
                                           ' WHERE EBI2.resultfmt_usage_id =  EBI1.resultfmt_usage_id AND EBI2.process_status = 1 ' ||
                                           ' AND EBI2.' || l_item_seq_col_name || ' IS NOT NULL' ;
Line: 1655

               ' AND EBI1.' || l_parent_column || ' = (SELECT EBI3.' || l_parent_column ||
                                                  ' FROM EGO_BULKLOAD_INTF EBI3 ' ||
                                                  ' WHERE EBI3.resultfmt_usage_id =  EBI1.resultfmt_usage_id AND EBI3.process_status = 1 ' ||
                                                  ' AND EBI3.' || l_item_seq_col_name || ' IS NOT NULL' ||
                                                  ' AND EBI3.' || l_parent_column || ' = EBI1.' || l_parent_column ||
                                                  ' AND EBI3.' || l_item_col_name || ' = EBI1.' || l_item_col_name ||
                                                  ' ) ' ||
               ' AND EBI1.' || l_item_col_name || ' IS NOT NULL ' ||
               ' AND EBI1.' || l_item_col_name || ' = (SELECT EBI4.' || l_item_col_name ||
                                                    ' FROM EGO_BULKLOAD_INTF EBI4 ' ||
                                                    ' WHERE EBI4.resultfmt_usage_id =  EBI1.resultfmt_usage_id AND EBI4.process_status = 1 ' ||
                                                    ' AND EBI4.' || l_item_seq_col_name || ' IS NOT NULL' ||
                                                    ' AND EBI4.' || l_item_col_name || ' = EBI1.' || l_item_col_name ||
                                                    ' AND EBI4.' || l_parent_column || ' = EBI1.' || l_parent_column ||
                                                    ' ) ';
Line: 1672

             ' AND EBI1.' || G_INTF_SRCSYS_PARENT || ' = (SELECT EBI3.' || G_INTF_SRCSYS_PARENT ||
                                                  ' FROM EGO_BULKLOAD_INTF EBI3 ' ||
                                                  ' WHERE EBI3.resultfmt_usage_id =  EBI1.resultfmt_usage_id AND EBI3.process_status = 1 ' ||
                                                  ' AND EBI3.' || l_item_seq_col_name || ' IS NOT NULL' ||
                                                  ' AND EBI3.' || G_INTF_SRCSYS_PARENT || ' = EBI1.' || G_INTF_SRCSYS_PARENT ||
                                                  ' AND EBI3.' || G_INTF_SRCSYS_COMPONENT || ' = EBI1.' || G_INTF_SRCSYS_COMPONENT ||
                                                  ' ) ' ||
             ' AND EBI1.' || G_INTF_SRCSYS_COMPONENT || ' IS NOT NULL ' ||
             ' AND EBI1.' || G_INTF_SRCSYS_COMPONENT || ' = (SELECT EBI4.' || G_INTF_SRCSYS_COMPONENT ||
                                                    ' FROM EGO_BULKLOAD_INTF EBI4 ' ||
                                                    ' WHERE EBI4.resultfmt_usage_id =  EBI1.resultfmt_usage_id  AND EBI4.process_status = 1 ' ||
                                                    ' AND EBI4.' || l_item_seq_col_name || ' IS NOT NULL' ||
                                                    ' AND EBI4.' || G_INTF_SRCSYS_COMPONENT || ' = EBI1.' || G_INTF_SRCSYS_COMPONENT ||
                                                    ' AND EBI4.' || G_INTF_SRCSYS_PARENT || ' = EBI1.' ||  G_INTF_SRCSYS_PARENT ||
                                                    ' ) ';
Line: 1692

      FND_FILE.PUT_LINE( FND_FILE.LOG,'Update Sql for ego bulkload for Multi Row-->' || l_upd_sql);
Line: 1711

      DELETE BOM_INVENTORY_COMPS_INTERFACE
      WHERE PROCESS_FLAG = 1
      AND batch_id = p_batch_id
      AND (OPERATION_SEQ_NUM IS NOT NULL OR NEW_OPERATION_SEQ_NUM IS NOT NULL)
      AND (EFFECTIVITY_DATE IS NOT NULL OR NEW_EFFECTIVITY_DATE IS NOT NULL)
      AND ITEM_NUM IS NULL;
Line: 1722

      l_dyn_sql_cursor := l_dyn_sql_cursor || ' SELECT ' || l_item_col_name  || ',' || G_INTF_SRCSYS_COMPONENT ;
Line: 1731

      l_dyn_sql_cursor := l_dyn_sql_cursor || ' OR UPPER(Transaction_Type) = ''' || G_TXN_UPDATE || ''' OR UPPER(Transaction_Type) = ''' || G_TXN_SYNC || ''' )';
Line: 1735

      l_cursor_select := Dbms_Sql.Open_Cursor;
Line: 1736

      Dbms_Sql.Parse(l_cursor_select, l_dyn_sql_cursor, Dbms_Sql.NATIVE);
Line: 1737

      Dbms_Sql.Define_Column(l_cursor_select, 1, L_ITEM_NAME, 3000);
Line: 1738

      Dbms_Sql.Define_Column(l_cursor_select, 2, L_SRCSYS_ITEM, 3000);
Line: 1739

      Dbms_Sql.Define_Column(l_cursor_select, 3, L_PARENT_NAME, 3000);
Line: 1740

      Dbms_Sql.Define_Column(l_cursor_select, 4, L_SRCSYS_PARENT,3000);
Line: 1741

      Dbms_Sql.Define_Column(l_cursor_select, 5, L_ORGANIZATION_CODE, 10);
Line: 1742

      Dbms_Sql.Define_Column(l_cursor_select, 6, L_STRUCTURE_NAME, 240);
Line: 1743

      Dbms_Sql.Define_Column(l_cursor_select, 7, L_COMP_REF_DESIG, 3000);
Line: 1744

      Dbms_Sql.Define_Column(l_cursor_select, 8, L_TRANSACTION_ID);
Line: 1745

      Dbms_Sql.Define_Column(l_cursor_select, 9, L_TRNSACTION_TYPE, 30);
Line: 1747

      Dbms_Sql.Bind_Variable(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
Line: 1751

      l_cursor_execute := Dbms_Sql.EXECUTE(l_cursor_select);
Line: 1756

        IF (Dbms_Sql.Fetch_Rows(l_cursor_select) > 0) THEN
          Dbms_Sql.Column_Value(l_cursor_select,1,L_ITEM_NAME);
Line: 1758

          Dbms_Sql.Column_Value(l_cursor_select,2,L_SRCSYS_ITEM);
Line: 1759

          Dbms_Sql.Column_Value(l_cursor_select,3,L_PARENT_NAME);
Line: 1760

          Dbms_Sql.Column_Value(l_cursor_select,4,L_SRCSYS_PARENT);
Line: 1761

          Dbms_Sql.Column_Value(l_cursor_select,5,L_ORGANIZATION_CODE);
Line: 1762

          Dbms_Sql.Column_Value(l_cursor_select,6,L_STRUCTURE_NAME);
Line: 1763

          Dbms_Sql.Column_Value(l_cursor_select,7,L_COMP_REF_DESIG);
Line: 1764

          Dbms_Sql.Column_Value(l_cursor_select,8,L_TRANSACTION_ID);
Line: 1765

          Dbms_Sql.Column_Value(l_cursor_select,9,L_TRNSACTION_TYPE);
Line: 1770

            INSERT INTO BOM_REF_DESGS_INTERFACE (
              BATCH_ID,
              REQUEST_ID,
              TRANSACTION_TYPE,
              TRANSACTION_ID,
              PROCESS_FLAG,
              ASSEMBLY_ITEM_NUMBER,
              ORGANIZATION_CODE,
              ALTERNATE_BOM_DESIGNATOR,
              COMPONENT_ITEM_NUMBER,
              COMP_SOURCE_SYSTEM_REFERENCE,
              PARENT_SOURCE_SYSTEM_REFERENCE,
              COMPONENT_REFERENCE_DESIGNATOR)
          VALUES (
              p_batch_id,
              G_REQUEST_ID,
              Decode(L_TRNSACTION_TYPE,G_TXN_SYNC,G_TXN_SYNC,G_TXN_UPDATE,G_TXN_SYNC,G_TXN_CREATE,G_TXN_CREATE,G_TXN_ADD,G_TXN_CREATE,L_TRNSACTION_TYPE),
              L_TRANSACTION_ID,
              1,
              L_PARENT_NAME,
              L_ORGANIZATION_CODE,
              L_STRUCTURE_NAME,
              L_ITEM_NAME,
              L_SRCSYS_ITEM,
              L_SRCSYS_PARENT,
              l_comp_ref_desig_tbl(I));
Line: 1802

      Dbms_Sql.Close_Cursor(l_cursor_select);
Line: 1805

    UPDATE BOM_REF_DESGS_INTERFACE BRDI
       SET(effectivity_date, operation_seq_num,  from_end_item_unit_number, component_sequence_id, assembly_item_revision_code)
       = (SELECT Decode(effectivity_date,NULL,new_effectivity_date,effectivity_date),
                 Decode(operation_seq_num,NULL,new_operation_seq_num,operation_seq_num),
                 Decode(from_end_item_unit_number,NULL,new_from_end_item_unit_number,from_end_item_unit_number),
                 component_sequence_id, parent_revision_code
           FROM BOM_INVENTORY_COMPS_INTERFACE BIC1
           WHERE BIC1.TRANSACTION_ID = BRDI.TRANSACTION_ID )
       WHERE
        BRDI.batch_id = p_batch_id
        AND EXISTS( Select 'X' FROM BOM_INVENTORY_COMPS_INTERFACE BIC12
           WHERE BIC12.TRANSACTION_ID = BRDI.TRANSACTION_ID);
Line: 1829

    UPDATE EGO_BULKLOAD_INTF EBI
      SET  EBI.PROCESS_STATUS = 7
    WHERE EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id AND EBI.PROCESS_STATUS = 1;
Line: 1932

  G_TXN_UPDATE          VARCHAR2(10) := 'UPDATE';
Line: 1933

  G_TXN_DELETE          VARCHAR2(10) := 'DELETE';
Line: 1938

    SELECT Bill_Sequence_id, Common_Bill_Sequence_id, Source_Bill_Sequence_id
    INTO l_Bill_Sequence_id, l_Common_Bill_Sequence_Id, l_Source_Bill_Sequence_Id
    FROM BOM_STRUCTURES_B
    WHERE assembly_item_id = p_assembly_item_id
    AND organization_id = p_organization_id
    AND NVL(alternate_bom_designator , Fnd_Api.G_MISS_CHAR) = NVL(p_alternate_bom_designator, Fnd_Api.G_MISS_CHAR);
Line: 1949

      SELECT COUNT(COMPONENT_ITEM_ID) INTO l_Component_Count
      FROM BOM_INVENTORY_COMPS_INTERFACE
      WHERE process_flag = 1
      AND UPPER(transaction_type) = G_TXN_UPDATE
      AND Request_Id = p_request_id
      AND (p_batch_id IS NULL OR batch_id = p_batch_id)
      AND assembly_item_id = p_assembly_item_id
      AND organization_id = p_organization_id
      AND NVL(alternate_bom_designator , Fnd_Api.G_MISS_CHAR) = NVL(p_alternate_bom_designator, Fnd_Api.G_MISS_CHAR);
Line: 1965

        UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
          SET COMPONENT_SEQUENCE_ID
                               = (SELECT COMPONENT_SEQUENCE_ID
                                 FROM BOM_INVENTORY_COMPONENTS BIC, bom_structures_b bsb
                                 WHERE BIC.bill_sequence_id = bsb.bill_Sequence_id
                                 AND bsb.assembly_item_id = p_assembly_item_id
                                 AND bsb.organization_id = p_organization_id
                                 AND NVL(bsb.alternate_bom_designator , Fnd_Api.G_MISS_CHAR) = NVL(p_alternate_bom_designator, Fnd_Api.G_MISS_CHAR)
                                 AND BIC.common_component_sequence_id = BICI.component_sequence_id)
          WHERE process_flag = 1
          AND UPPER(transaction_type) = G_TXN_UPDATE
          AND Request_Id = p_request_id
          AND (p_batch_id IS NULL OR batch_id = p_batch_id)
          AND assembly_item_id = p_assembly_item_id
          AND organization_id = p_organization_id
          AND NVL(alternate_bom_designator , Fnd_Api.G_MISS_CHAR) = NVL(p_alternate_bom_designator, Fnd_Api.G_MISS_CHAR);
Line: 2010

    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 = G_COMP_ATTR_GROUP_TYPE
        AND    application_id = G_BOM_APPLICATION_ID
      );
Line: 2032

     SELECT  attr_group_name, attr_name
     FROM    ego_attrs_v
     WHERE   attr_id = p_attr_id
      AND    attr_group_type = G_COMP_ATTR_GROUP_TYPE
      AND    application_id = G_BOM_APPLICATION_ID;
Line: 2126

  l_cursor_select          INTEGER;
Line: 2169

     SELECT mtl_system_items_intf_sets_s.NEXTVAL
       INTO l_msii_set_process_id
     FROM dual;
Line: 2189

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

    l_cursor_select := DBMS_SQL.OPEN_CURSOR;
Line: 2200

    DBMS_SQL.PARSE(l_cursor_select, l_dyn_sql, DBMS_SQL.NATIVE);
Line: 2201

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

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

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

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

    DBMS_SQL.BIND_VARIABLE(l_cursor_select,':ATTRIBUTE_CODE_O', c_attr_grp_rec.attr_group_id||'$$%_O');
Line: 2209

    l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);
Line: 2218

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

    l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);
Line: 2229

    DBMS_SQL.COLUMN_VALUE(l_cursor_select, 1, l_attr_id_table);
Line: 2230

    DBMS_SQL.COLUMN_VALUE(l_cursor_select, 2, l_intf_col_name_table);
Line: 2232

    DBMS_SQL.CLOSE_CURSOR(l_cursor_select);
Line: 2239

    l_dyn_attr_id_val_sql := ' SELECT ';
Line: 2461

              l_usr_attr_data_tbl.DELETE(l_rows_per_attr_grp_indx);
Line: 2483

      l_attr_id_table.DELETE;
Line: 2484

      l_intf_col_name_table.DELETE;
Line: 2550

        SELECT structure_type_id
        INTO l_str_type_id
        FROM ego_import_option_sets
        where batch_id = p_data_set_id;
Line: 2573

        SELECT COUNT(*) INTO l_comp_usr_attr_flag
        FROM BOM_CMP_USR_ATTR_INTERFACE
        WHERE DATA_SET_ID = l_usr_attr_data_tbl(i).DATA_SET_ID
         AND ROW_IDENTIFIER = l_usr_attr_data_tbl(i).ROW_IDENTIFIER
         AND ATTR_GROUP_INT_NAME = l_usr_attr_data_tbl(i).ATTR_GROUP_NAME
         AND ATTR_INT_NAME = l_usr_attr_data_tbl(i).ATTR_NAME
	 --Bug 13580598
	 AND (     ( ITEM_NUMBER = l_usr_attr_data_tbl(i).COMPONENT_ITEM_NUMBER
                    AND ASSEMBLY_ITEM_NUMBER = l_usr_attr_data_tbl(i).ASSEMBLY_ITEM_NUMBER
                    AND ORGANIZATION_CODE = l_usr_attr_data_tbl(i).ORGANIZATION_CODE )
             );
Line: 2588

          INSERT INTO BOM_CMP_USR_ATTR_INTERFACE
          (
           DATA_SET_ID          ,
           TRANSACTION_ID       ,
           BILL_SEQUENCE_ID    ,
           COMPONENT_SEQUENCE_ID,
           ROW_IDENTIFIER       ,
           ATTR_GROUP_INT_NAME  ,
           ATTR_INT_NAME        ,
           ATTR_DISP_VALUE      ,
           PROCESS_STATUS       ,
           SOURCE_SYSTEM_ID     ,
           COMP_SOURCE_SYSTEM_REFERENCE,
           BATCH_ID,
           STRUCTURE_TYPE_ID,
           TRANSACTION_TYPE,
           ITEM_NUMBER,
           ORGANIZATION_CODE,
           ORGANIZATION_ID,
           ASSEMBLY_ITEM_NUMBER,
           PARENT_SOURCE_SYSTEM_REFERENCE,
	   DATA_LEVEL_ID   -- Added for PIMTELCO Bug-7645265
          )
          VALUES
          (
           l_usr_attr_data_tbl(i).DATA_SET_ID,
           l_usr_attr_data_tbl(i).TRANSACTION_ID,
           l_usr_attr_data_tbl(i).BILL_SEQUENCE_ID,
           l_usr_attr_data_tbl(i).COMPONENT_SEQUENCE_ID,
           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,
           0,-- G_PROCESS_STATUS,
           l_usr_attr_data_tbl(i).SOURCE_SYSTEM_ID,
           l_usr_attr_data_tbl(i).SOURCE_SYSTEM_REFERENCE,
           p_data_set_id,
           l_str_type_id,
           'SYNC',
           l_usr_attr_data_tbl(i).COMPONENT_ITEM_NUMBER,
           l_usr_attr_data_tbl(i).ORGANIZATION_CODE,
           (SELECT ORGANIZATION_ID FROM mtl_parameters where organization_code = l_usr_attr_data_tbl(i).ORGANIZATION_CODE),
           l_usr_attr_data_tbl(i).ASSEMBLY_ITEM_NUMBER,
           l_usr_attr_data_tbl(i).PARENT_SOURCE_SYSTEM_REFERENCE,
           70201    -- Added for PIMTELCO Bug-7645265 Hardcoded Value for COMPONENTS_LEVEL data level
          );
Line: 2635

         UPDATE BOM_CMP_USR_ATTR_INTERFACE
         SET TRANSACTION_ID = l_usr_attr_data_tbl(i).TRANSACTION_ID,
          BILL_SEQUENCE_ID = l_usr_attr_data_tbl(i).BILL_SEQUENCE_ID,
          COMPONENT_SEQUENCE_ID = l_usr_attr_data_tbl(i).COMPONENT_SEQUENCE_ID,
          ATTR_DISP_VALUE = l_varchar_data,
          PROCESS_STATUS = 0,
          SOURCE_SYSTEM_ID = l_usr_attr_data_tbl(i).SOURCE_SYSTEM_ID,
          COMP_SOURCE_SYSTEM_REFERENCE =  l_usr_attr_data_tbl(i).SOURCE_SYSTEM_REFERENCE,
          BATCH_ID = p_data_set_id,
          STRUCTURE_TYPE_ID = l_str_type_id,
          TRANSACTION_TYPE = 'SYNC',
          ITEM_NUMBER = l_usr_attr_data_tbl(i).COMPONENT_ITEM_NUMBER,
          ORGANIZATION_CODE = l_usr_attr_data_tbl(i).ORGANIZATION_CODE,
          ORGANIZATION_ID = (SELECT ORGANIZATION_ID FROM mtl_parameters where organization_code = l_usr_attr_data_tbl(i).ORGANIZATION_CODE),
          ASSEMBLY_ITEM_NUMBER = l_usr_attr_data_tbl(i).ASSEMBLY_ITEM_NUMBER,
          PARENT_SOURCE_SYSTEM_REFERENCE = l_usr_attr_data_tbl(i).PARENT_SOURCE_SYSTEM_REFERENCE,
          DATA_LEVEL_ID = 70201,
	   --pnagasur:Added for Bug 13085172
          ATTR_VALUE_STR =l_usr_attr_data_tbl(i).ATTR_VALUE_STR,
	  ATTR_VALUE_NUM  =l_usr_attr_data_tbl(i).ATTR_VALUE_NUM ,
          ATTR_VALUE_DATE =l_usr_attr_data_tbl(i).ATTR_VALUE_DATE
         WHERE DATA_SET_ID = l_usr_attr_data_tbl(i).DATA_SET_ID
          AND ROW_IDENTIFIER = l_usr_attr_data_tbl(i).ROW_IDENTIFIER
          AND ATTR_GROUP_INT_NAME = l_usr_attr_data_tbl(i).ATTR_GROUP_NAME
          AND ATTR_INT_NAME = l_usr_attr_data_tbl(i).ATTR_NAME
	  --Bug 13580598
          AND (     ( ITEM_NUMBER = l_usr_attr_data_tbl(i).COMPONENT_ITEM_NUMBER
                    AND ASSEMBLY_ITEM_NUMBER = l_usr_attr_data_tbl(i).ASSEMBLY_ITEM_NUMBER
                    AND ORGANIZATION_CODE = l_usr_attr_data_tbl(i).ORGANIZATION_CODE )
             );