DBA Data[Home] [Help]

APPS.EGO_ORCHESTRATION_UTIL_PUB SQL Statements

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

Line: 33

      SELECT gpc_code, bundle_id, source_system_id, source_system_reference
      FROM mtl_system_items_interface
      WHERE bundle_id = l_b_Id ;
Line: 49

        UPDATE mtl_system_items_interface
          SET Item_catalog_group_id = l_icc_code
          WHERE  bundle_id = item_data.bundle_id
                AND source_system_id = item_data.source_system_id
                AND source_system_reference = item_data.source_system_reference;
Line: 80

  IS SELECT gpc_code ,bundle_id, global_trade_item_number,source_system_reference
      FROM mtl_system_items_interface
      WHERE bundle_id = l_b_Id ;
Line: 99

      UPDATE MTL_ITEM_CATEGORIES_INTERFACE
      SET CATEGORY_id = l_acc_code ,CATEGORY_SET_ID = l_acc_catalog
        WHERE  bundle_id = item_data.bundle_id
          AND source_system_id = item_data.global_trade_item_number
          AND source_system_reference = item_data.source_system_reference;
Line: 125

  SELECT Value(xml_tab) bundle
  FROM TABLE(XMLSEQUENCE(EXTRACT(p_bundles_xml, '/Bundles/Bundle'))) xml_tab;
Line: 130

  SELECT Value(xml_tab) bundle
  FROM TABLE(XMLSEQUENCE(EXTRACT(p_bundles_xml, '//ItemBundle'))) xml_tab;
Line: 146

    SELECT MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
    INTO l_bundle_collection_id
    FROM dual;
Line: 165

        INSERT INTO MTL_ITEM_BULKLOAD_RECS (request_id,
                                          creation_date,
                                          last_update_date,
                                          created_by,
                                          last_updated_by,
                                          bundle_collection_id,
                                          bundle_id,
                                          source_system_id,
                                          source_system_reference
                                          ) values
                                          (-1,
                                          sysdate,
                                          sysdate,
                                          1,
                                          1,
                                          l_bundle_collection_id,
                                          l_bundle_id,
                                          l_source_system_id,
                                          l_source_system_ref
                                          );
Line: 192

      INSERT INTO MTL_ITEM_BULKLOAD_RECS (request_id,
                                          creation_date,
                                          last_update_date,
                                          created_by,
                                          last_updated_by,
                                          bundle_collection_id,
                                          bundle_id,
                                          message_type,
                                          message_code
                                          ) values
                                          (-1,
                                          sysdate,
                                          sysdate,
                                          1,
                                          1,
                                          l_bundle_collection_id,
                                          l_bundle_id,
                                          c_bls.bundle.extract('/Bundle/Message/@type').getStringVal(),
                                          substr(c_bls.bundle.extract('/Bundle/Message/text()').getStringVal(), 1, 80));
Line: 234

  IS  SELECT bundle_id
        FROM MTL_ITEM_BULKLOAD_RECS
        WHERE bundle_collection_id = p_rcb_id;
Line: 316

      SELECT bundle_id
      FROM MTL_ITEM_BULKLOAD_RECS
      WHERE bundle_collection_id = p_rcb_id
            AND entity_type LIKE 'ITEM';
Line: 408

  SELECT batch_id
  FROM EGO_IMPORT_BATCHES_tl
  WHERE name = l_batch_name
        and language = USERENV('LANG');
Line: 414

  SELECT batch_type
  from ego_import_batches_b
  WHERE batch_id =  l_batch_id;
Line: 420

    SELECT 1
    FROM DUAL
    WHERE EXISTS (SELECT NULL
                  FROM EGO_IMPORT_OPTION_SETS
                  WHERE BATCH_ID = l_batch_id
                  AND ENABLED_FOR_DATA_POOL = 'Y'
                );
Line: 523

  SELECT set_process_id
  FROM MTL_SYSTEM_ITEMS_INTERFACE
    WHERE BUNDLE_ID IN
      (SELECT BUNDLE_ID
       FROM MTL_ITEM_BULKLOAD_RECS
       WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
Line: 538

    UPDATE MTL_SYSTEM_ITEMS_INTERFACE ISTI
    SET TRANSACTION_TYPE = C_TRANSACTION_SYNC,
        PROCESS_FLAG = C_INIT_PROCESS_FLAG,
        --CONFIRM_STATUS = 'UN',
        SOURCE_SYSTEM_ID = (SELECT SOURCE_SYSTEM_ID
                            FROM EGO_IMPORT_BATCHES_B
                            WHERE BATCH_ID = ISTI.SET_PROCESS_ID)
    WHERE BUNDLE_ID IN
      (SELECT BUNDLE_ID
       FROM MTL_ITEM_BULKLOAD_RECS
       WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID)
      AND ITEM_NUMBER IS NULL;
Line: 551

    UPDATE MTL_SYSTEM_ITEMS_INTERFACE ISTI
    SET TRANSACTION_TYPE = C_TRANSACTION_SYNC,
        PROCESS_FLAG = C_INIT_PROCESS_FLAG,
        CONFIRM_STATUS = 'CN',
        SOURCE_SYSTEM_ID = (SELECT SOURCE_SYSTEM_ID
                            FROM EGO_IMPORT_BATCHES_B
                            WHERE BATCH_ID = ISTI.SET_PROCESS_ID)
    WHERE BUNDLE_ID IN
      (SELECT BUNDLE_ID
       FROM MTL_ITEM_BULKLOAD_RECS
       WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID)
      AND ITEM_NUMBER IS NOT NULL;
Line: 565

    UPDATE EGO_ITM_USR_ATTR_INTRFC EIUAI
    SET TRANSACTION_TYPE = C_TRANSACTION_SYNC,
        PROCESS_STATUS = C_INIT_PROCESS_FLAG,
        SOURCE_SYSTEM_ID = (SELECT SOURCE_SYSTEM_ID
                            FROM EGO_IMPORT_BATCHES_B
                            WHERE BATCH_ID = EIUAI.DATA_SET_ID)
    WHERE BUNDLE_ID IN
      (SELECT BUNDLE_ID
       FROM MTL_ITEM_BULKLOAD_RECS
       WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
Line: 577

    UPDATE EGO_ITEM_ASSOCIATIONS_INTF EIAI
    SET TRANSACTION_TYPE = C_TRANSACTION_SYNC,
        PROCESS_FLAG = C_INIT_PROCESS_FLAG,
        SOURCE_SYSTEM_ID = (SELECT SOURCE_SYSTEM_ID
                            FROM EGO_IMPORT_BATCHES_B
                            WHERE BATCH_ID = EIAI.BATCH_ID)
    WHERE BUNDLE_ID IN
      (SELECT BUNDLE_ID
       FROM MTL_ITEM_BULKLOAD_RECS
       WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
Line: 589

    UPDATE MTL_ITEM_CATEGORIES_INTERFACE MICI
    SET TRANSACTION_TYPE = C_TRANSACTION_SYNC,
        PROCESS_FLAG = C_INIT_PROCESS_FLAG,
        SOURCE_SYSTEM_ID = (SELECT SOURCE_SYSTEM_ID
                            FROM EGO_IMPORT_BATCHES_B
                            WHERE BATCH_ID = MICI.SET_PROCESS_ID)
    WHERE BUNDLE_ID IN
      (SELECT BUNDLE_ID
       FROM MTL_ITEM_BULKLOAD_RECS
       WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
Line: 601

    UPDATE BOM_BILL_OF_MTLS_INTERFACE BBOMI
    SET TRANSACTION_TYPE = C_TRANSACTION_SYNC,
        PROCESS_FLAG = 1
    WHERE BUNDLE_ID IN
      (SELECT BUNDLE_ID
       FROM MTL_ITEM_BULKLOAD_RECS
       WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
Line: 610

    UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
    SET TRANSACTION_TYPE = C_TRANSACTION_SYNC,
        PROCESS_FLAG = 1
    WHERE BUNDLE_ID IN
      (SELECT BUNDLE_ID
       FROM MTL_ITEM_BULKLOAD_RECS
       WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
Line: 619

    UPDATE EGO_INTERFACE_TL
    SET PROCESS_STATUS = 1
    WHERE BUNDLE_ID IN
      (SELECT BUNDLE_ID
       FROM MTL_ITEM_BULKLOAD_RECS
       WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
Line: 627

    UPDATE EGO_UCCNET_EVENTS EUE
    SET SOURCE_SYSTEM_ID = (SELECT SOURCE_SYSTEM_ID
                            FROM EGO_IMPORT_BATCHES_B
                            WHERE BATCH_ID = EUE.import_batch_id)
    WHERE CLN_ID IN
      (SELECT BUNDLE_ID
       FROM MTL_ITEM_BULKLOAD_RECS
       WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
Line: 642

    UPDATE BOM_BILL_OF_MTLS_INTERFACE BBOMI
    SET ORGANIZATION_ID = (SELECT ORGANIZATION_ID
                           FROM MTL_SYSTEM_ITEMS_INTERFACE
                           WHERE BUNDLE_ID = BBOMI.BUNDLE_ID
                           AND ROWNUM = 1),
        ORGANIZATION_CODE = (SELECT mp.organization_code
                             FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
                                  MTL_PARAMETERS mp
                             WHERE mp.organization_id = msii.organization_id
                             AND msii.BUNDLE_ID = BBOMI.BUNDLE_ID
                             AND ROWNUM = 1)
    WHERE BUNDLE_ID IN
      (SELECT BUNDLE_ID
       FROM MTL_ITEM_BULKLOAD_RECS
       WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
Line: 658

    UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
    SET ORGANIZATION_ID = (SELECT ORGANIZATION_ID
                           FROM MTL_SYSTEM_ITEMS_INTERFACE
                           WHERE BUNDLE_ID = BICI.BUNDLE_ID
                           AND ROWNUM = 1),
        ORGANIZATION_CODE = (SELECT mp.organization_code
                             FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
                                  MTL_PARAMETERS mp
                             WHERE mp.organization_id = msii.organization_id
                             AND msii.BUNDLE_ID = BICI.BUNDLE_ID
                             AND ROWNUM = 1)
    WHERE BUNDLE_ID IN
      (SELECT BUNDLE_ID
       FROM MTL_ITEM_BULKLOAD_RECS
       WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
Line: 674

    UPDATE EGO_ITM_USR_ATTR_INTRFC EIUAI
    SET pk2_value = (SELECT asa.VENDOR_SITE_ID
                       FROM ap_supplier_sites_all asa,
                            ap_supplier_sites_all asa2,
                            mtl_system_items_interface msii
                       WHERE asa.party_site_id = asa2.party_site_id
                         AND asa.vendor_id = EIUAI.pk1_value
                         AND asa2.vendor_site_id = EIUAI.pk2_value
                         AND asa.org_id = msii.organization_id
                         AND msii.BUNDLE_ID = EIUAI.BUNDLE_ID
                         AND ROWNUM =1)
    WHERE data_level_name = 'ITEM_SUP_SITE'
      AND pk2_value IS NOT NULL
      AND BUNDLE_ID IN
          (SELECT BUNDLE_ID
           FROM MTL_ITEM_BULKLOAD_RECS
           WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
Line: 692

    UPDATE EGO_ITEM_ASSOCIATIONS_INTF EIAI
    SET pk2_value = (SELECT asa.VENDOR_SITE_ID
                       FROM ap_supplier_sites_all asa,
                            ap_supplier_sites_all asa2,
                            mtl_system_items_interface msii
                       WHERE asa.party_site_id = asa2.party_site_id
                         AND asa.vendor_id = EIAI.pk1_value
                         AND asa2.vendor_site_id = EIAI.pk2_value
                         AND asa.org_id = msii.organization_id
                         AND msii.BUNDLE_ID = EIAI.BUNDLE_ID
                         AND ROWNUM =1)
     WHERE data_level_name = 'ITEM_SUP_SITE'
      AND pk2_value IS NOT NULL
      AND BUNDLE_ID IN
          (SELECT BUNDLE_ID
           FROM MTL_ITEM_BULKLOAD_RECS
           WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
Line: 710

    UPDATE EGO_ITM_USR_ATTR_INTRFC EIUAI
    SET pk2_value = (SELECT asa.VENDOR_SITE_ID
                       FROM ap_supplier_sites_all asa,
                            ap_supplier_sites_all asa2,
                            mtl_system_items_interface msii
                       WHERE asa.party_site_id = asa2.party_site_id
                         AND asa.vendor_id = EIUAI.pk1_value
                         AND asa2.vendor_site_id = EIUAI.pk2_value
                         AND asa.org_id = msii.organization_id
                         AND msii.BUNDLE_ID = EIUAI.BUNDLE_ID
                         AND ROWNUM =1)
    WHERE data_level_name = 'ITEM_SUP_SITE_ORG'
      AND pk2_value IS NOT NULL
      AND BUNDLE_ID IN
          (SELECT BUNDLE_ID
           FROM MTL_ITEM_BULKLOAD_RECS
           WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
Line: 728

    UPDATE EGO_ITEM_ASSOCIATIONS_INTF EIAI
    SET pk2_value = (SELECT asa.VENDOR_SITE_ID
                       FROM ap_supplier_sites_all asa,
                            ap_supplier_sites_all asa2,
                            mtl_system_items_interface msii
                       WHERE asa.party_site_id = asa2.party_site_id
                         AND asa.vendor_id = EIAI.pk1_value
                         AND asa2.vendor_site_id = EIAI.pk2_value
                         AND asa.org_id = msii.organization_id
                         AND msii.BUNDLE_ID = EIAI.BUNDLE_ID
                         AND ROWNUM =1)
     WHERE data_level_name = 'ITEM_SUP_SITE_ORG'
      AND pk2_value IS NOT NULL
      AND BUNDLE_ID IN
          (SELECT BUNDLE_ID
           FROM MTL_ITEM_BULKLOAD_RECS
           WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
Line: 762

  SELECT   XMLELEMENT("BundleCollections",
             XMLELEMENT("BundleCollection",
               XMLELEMENT("BundleCollectionId", bundle_collection_id),
               XMLELEMENT("Bundles",
                 XMLAGG(XMLELEMENT("Bundle",
                          XMLELEMENT("BundleId", bundle_id)))))) XML_DOC
  INTO l_xml_doc
  FROM MTL_ITEM_BULKLOAD_RECS
  WHERE bundle_collection_id = p_bundle_collection_id
    AND bundle_id > Nvl(p_prior_bundle_id, 0)
    AND ROWNUM < p_max_elements
    GROUP BY  bundle_collection_id;
Line: 796

    SELECT asa.VENDOR_ID, aas.VENDOR_NAME
    INTO X_SUPPLIER_ID, X_SUPPLIER_NAME
    FROM ap_suppliers aas,
         ap_supplier_sites_all asa,
         hz_party_sites hps
    WHERE hps.GLOBAL_LOCATION_NUMBER = X_EXT_SUP_ID
      AND hps.party_site_id = asa.party_site_id
      AND aas.vendor_id = asa.vendor_id
      AND rownum = 1;
Line: 808

      SELECT asa.VENDOR_SITE_ID, asa.VENDOR_SITE_CODE
      INTO X_SUPPLIER_ID, X_SUPPLIER_NAME
      FROM ap_suppliers aas,
           ap_supplier_sites_all asa,
           hz_party_sites hps
      WHERE hps.GLOBAL_LOCATION_NUMBER = X_EXT_SUP_ID
        AND hps.party_site_id = asa.party_site_id
        AND aas.vendor_id = asa.vendor_id
        AND rownum = 1;
Line: 831

  SELECT MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
  INTO l_id
  FROM dual;
Line: 849

  SELECT Value(xml_tab) entry
  FROM TABLE(XMLSEQUENCE(EXTRACT(p_collection_xml, '/TL/'||p_entity_name))) xml_tab;
Line: 855

    INSERT INTO EGO_INTERFACE_TL (
      set_process_id,
      unique_id,
      bundle_id,
      table_name,
      LANGUAGE,
      column_name,
      column_value,
      created_by,
      creation_date,
      last_updated_by,
      last_update_date,
      last_update_login
    ) VALUES(
      p_batch_id,
      p_unique_id,
      p_bundle_id,
      p_table_name,
      trans_entry.ENTRY.extract('/'||p_entity_name||'/@languageID').getStringVal(),
      p_column_name,
      trans_entry.ENTRY.extract('/'||p_entity_name||'/text()').getStringVal(),
      1,
      SYSDATE,
      1,
      SYSDATE,
      1
    );
Line: 899

                          p_last_updated_by         IN NUMBER,
                          p_last_update_date        IN DATE,
                          p_last_update_login       IN NUMBER)
IS

  -- Attribute Groups
  CURSOR c_AttributeGrps(p_entity_xml XMLTYPE, p_entity_name VARCHAR2)
  IS
  SELECT Value(xml_tab) attributeGroups
  FROM TABLE(XMLSEQUENCE(EXTRACT(p_entity_xml, '/'||p_entity_name||'/AttributeGroup'))) xml_tab;
Line: 913

  SELECT Value(xml_tab) attributes
  FROM TABLE(XMLSEQUENCE(EXTRACT(p_attrGrp_xml, '/AttributeGroup/Attribute'))) xml_tab;
Line: 989

        INSERT INTO EGO_ITM_USR_ATTR_INTRFC(
                transaction_id,
                bundle_id,
                source_system_id,
                source_system_reference,
                data_set_id,
                row_identifier,
                organization_code,

                attr_group_type,
                attr_group_int_name,
                attr_int_name,
                attr_value_str,
                attr_value_num,
                attr_value_date,
                attr_value_uom,
                data_level_name,
                pk1_value,
                pk2_value,

                CREATED_BY,
                CREATION_DATE,
                LAST_UPDATED_BY,
                LAST_UPDATE_DATE,
                LAST_UPDATE_LOGIN
        ) VALUES (
                p_transaction_id,
                p_bundle_id,
                p_source_system_id,
                p_source_system_reference,
                p_data_set_id,
                l_row_identifier,
                p_organization_code,

                'EGO_ITEMMGMT_GROUP', -- bug:6525204 Passing EGO_ITEMMGMT_GROUP as UDA type always
                l_attr_group_int_name,
                l_attr_int_name,
                l_attr_text_value,
                l_attr_numeric_value,
                l_date_value,
                l_attr_quant_unit_code,
                p_data_level_name,
                p_pk1_value,
                p_pk2_value,

                p_created_by,
                p_creation_date,
                p_last_updated_by,
                p_last_update_date,
                p_last_update_login
        );
Line: 1043

          SELECT XMLELEMENT("TL", l_xml_Attrs.attributes.extract('/Attribute/ValueText'))
          INTO l_xml_trans
          FROM DUAL;
Line: 1118

  l_last_updated_by               NUMBER;
Line: 1119

  l_last_update_date              DATE;
Line: 1120

  l_last_update_login             NUMBER;
Line: 1134

  EGO_ORC_DELETE_LINE             EXCEPTION;
Line: 1139

  SELECT Value(xml_tab) bundles
  FROM TABLE(XMLSEQUENCE(EXTRACT(p_collection_xml, '/XMLEntries/SyncItemPublication/ItemPublicationLine'))) xml_tab;
Line: 1145

  SELECT Value(xml_tab) items
  FROM TABLE(XMLSEQUENCE(EXTRACT(p_bundles_xml, '/ItemPublicationLine/Item'))) xml_tab;
Line: 1151

  SELECT Value(xml_tab) classifications
  FROM TABLE(XMLSEQUENCE(EXTRACT(p_items_xml, '/Item/ItemCatalog'))) xml_tab;
Line: 1157

  SELECT Value(xml_tab) suppliers
  FROM TABLE(XMLSEQUENCE(EXTRACT(p_items_xml, '/Item/ItemSupplier'))) xml_tab;
Line: 1163

  SELECT Value(xml_tab) supplierLocations
  FROM TABLE(XMLSEQUENCE(EXTRACT(p_suppliers_xml, '/ItemSupplier/ItemSupplierLocation'))) xml_tab;
Line: 1169

  SELECT Value(xml_tab) structures
  FROM TABLE(XMLSEQUENCE(EXTRACT(p_bundles_xml, '/ItemPublicationLine/ItemStructure'))) xml_tab;
Line: 1175

  SELECT Value(xml_tab) components
  FROM TABLE(XMLSEQUENCE(EXTRACT(p_structures_xml, '/ItemStructure/ComponentItem'))) xml_tab;
Line: 1181

  SELECT CONCATENATED_SEGMENTS
  FROM MTL_CATEGORIES_KFV
  WHERE SEGMENT2 = c_alt_cat_code
  AND   ROWNUM = 1;
Line: 1196

  l_last_updated_by := 0;
Line: 1197

  l_last_update_date := SYSDATE;
Line: 1198

  l_last_update_login := 0;
Line: 1235

        IF (l_xml_null_chk IS NOT NULL AND l_xml_null_chk.getStringVal() = 'PUBLICATION_DELETE') --bug:6500128
        THEN
          raise EGO_ORC_DELETE_LINE;
Line: 1289

            INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE(
              message_timestamp,
              transaction_id,
              top_item_flag,
              bundle_id,
              set_process_id,
              source_system_id,
              source_system_reference,

              item_number,
              organization_code,

              serial_status_enabled,
              lot_status_enabled,
              service_item_flag,
              --type_code
              dual_uom_control,
              primary_uom_code,
              --storage_uom_code,
              --shipping_uom_code,
              --UOM_conversion_usage_code
              secondary_uom_code,
              description,


              unit_volume,
              volume_uom_code,
              unit_weight,
              weight_uom_code,
              dimension_uom_code,
              unit_length,
              unit_width,
              unit_height,

              cycle_count_enabled_flag,
              --lot_expiration_on_receipt,
              lot_merge_enabled,
              lot_split_enabled,
              --reservation_allowed_flag,
              --serialization_event_code,
              shelf_life_days,
              --revision_control_flag,
              stock_enabled_flag,
              auto_lot_alpha_prefix,
              --auto_lot_suffix,
              auto_serial_alpha_prefix,
              --auto_serial_suffix,

              --debit_gl_account_code,
              asset_creation_code,
              purchasing_enabled_flag,
              receipt_required_flag,
              must_use_approved_vendor_flag,
              allow_substitute_receipts_flag,
              allow_unordered_receipts_flag,
              rfq_required_flag,
              taxable_flag,
              hazard_class_id,
              tax_code,
              --issue_uom_code,
              --list_price_per_unit_amount,
              list_price_per_unit,
              under_shipment_tolerance,
              over_shipment_tolerance,
              --receipt_duration_tolerance,

              --manufactured_item_indicator,
              consigned_flag,
              inventory_planning_code,
              --reorder_max_inv_duration,
              --reorder_min_inv_duration,
              --reorder_max_inv_quantity,
              --reorder_min_inv_quantity,
              --reorder_quantity,
              min_minmax_quantity,
              max_minmax_quantity,
              minimum_order_quantity,
              shrinkage_rate,

              bom_item_type,
              config_model_type,
              effectivity_control,
              wip_supply_type,
              eng_item_flag,
              bom_enabled_flag,
              costing_enabled_flag,
              inventory_asset_flag,
              std_lot_size,
              back_orderable_flag,
              returnable_flag,
              --assemble_to_order_flag,

              gpc_code,
              trade_item_descriptor,

              CREATED_BY,
              CREATION_DATE,
              LAST_UPDATED_BY,
              LAST_UPDATE_DATE,
              LAST_UPDATE_LOGIN
            ) VALUES(
              l_message_date,
              l_transaction_id,
              NVL(l_xml_ItemEBO.items.extract('/Item/AttributeGroup[ID = "EGO_ORCH_INT"]/Attribute[ID ="TopItem"]/Value/text()').getStringVal(), 'N'),
              l_bundle_id,
              l_batch_id,
              p_source_sys_id,
              l_source_sys_reference,

              l_xml_ItemEBO.items.extract('/Item/ItemIdentification/Identification/text()').getStringVal(),
              l_org_code,

              Decode(l_xml_ItemEBO.items.extract('/Item/ItemBase/SerialControlIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
              Decode(l_xml_ItemEBO.items.extract('/Item/ItemBase/LotControlIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
              Decode(l_xml_ItemEBO.items.extract('/Item/ItemBase/ServiceIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
              --l_xml_ItemEBO.items.extract('/Item/ItemBase/TypeCode/text()').getStringVal(),
              Decode(l_xml_ItemEBO.items.extract('/Item/ItemBase/DualUOMTrackingIndicator/text()').getStringVal(), 'true', 1, 'false', 0, null),
              -- UOM to be set during import
              null,--l_xml_ItemEBO.items.extract('/Item/ItemBase/BaseUOMCode/text()').getStringVal(),
              --l_xml_ItemEBO.items.extract('/Item/ItemBase/StorageUOMCode/text()').getStringVal(),
              --l_xml_ItemEBO.items.extract('/Item/ItemBase/ShippingUOMCode/text()').getStringVal(),
              --l_xml_ItemEBO.items.extract('/Item/ItemBase/UOMConversionUsageCode/text()').getStringVal(),
              null,--l_xml_ItemEBO.items.extract('/Item/ItemBase/SecondaryUOMCode/text()').getStringVal(),
              l_xml_ItemEBO.items.extract('/Item/ItemBase/Description[position() = 1]/text()').getStringVal(),

              l_xml_ItemEBO.items.extract('/Item/PhysicalCharacteristics/UnitVolumeMeasure/text()').getStringVal(),
              l_xml_ItemEBO.items.extract('/Item/PhysicalCharacteristics/UnitVolumeMeasure/@unitCode').getStringVal(),
              l_xml_ItemEBO.items.extract('/Item/PhysicalCharacteristics/UnitWeightMeasure/text()').getStringVal(),
              l_xml_ItemEBO.items.extract('/Item/PhysicalCharacteristics/UnitWeightMeasure/@unitCode').getStringVal(),
              l_xml_ItemEBO.items.extract('/Item/PhysicalCharacteristics/LengthMeasure/@unitCode').getStringVal(),
              l_xml_ItemEBO.items.extract('/Item/PhysicalCharacteristics/LengthMeasure/text()').getStringVal(),
              l_xml_ItemEBO.items.extract('/Item/PhysicalCharacteristics/WidthMeasure/text()').getStringVal(),
              l_xml_ItemEBO.items.extract('/Item/PhysicalCharacteristics/HeightMeasure/text()').getStringVal(),

              Decode(l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/CycleCountEnabledIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
              --Decode(l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/LotExpirationOnReceiptIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
              Decode(l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/LotMergeEnabledIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
              Decode(l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/LotSplitEnabledIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
              --Decode(l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/ReservationAllowedIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
              --l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/SerializationEventCode/text()').getStringVal(),
              l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/ShelfLifeDuration/text()').getStringVal(),
              --Decode(l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/RevisionControlIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
              Decode(l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/StockingAllowedIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
              l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/InitialLotNumberPrefix/text()').getStringVal(),
              --l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/InitialLotNumberSuffix/text()').getStringVal(),
              l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/InitialSerialNumberPrefix/text()').getStringVal(),
              --l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/InitialSerialNumberSuffix/text()').getStringVal(),

              --l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/DebitGLAccountCode/text()').getStringVal(),
              l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/AssetClassificationCode/text()').getStringVal(),
              Decode(l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/PurchasableIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
              Decode(l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/ReceiptRequiredIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
              Decode(l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/UseApprovedSupplierIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
              Decode(l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/AllowReceiptSubstitutionIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
              Decode(l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/AllowUnorderedReceiptIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
              Decode(l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/RFQRequiredIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
              Decode(l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/TaxableIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
              l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/HazardClassificationCode/text()').getStringVal(),
              l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/TaxCode/text()').getStringVal(),
              --l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/IssueUOMCode/text()').getStringVal(),
              --l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/UnitListPrice/Amount/text()').getStringVal(),
              l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/UnitListPrice/PerQuantity/text()').getStringVal(),
              l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/OverReceiptTolerancePercent/UnderDuration/text()').getStringVal(),
              l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/OverReceiptTolerancePercent/OverDuration/text()').getStringVal(),
              --l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/ReceiptDurationTolerance/text()').getStringVal(),

              --Decode(l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/ManufacturedItemIndicator/text()').getStringVal(), 'true', 1, 'false', 0, null),
              Decode(l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/ConsignmentItemIndicator/text()').getStringVal(), 'true', 1, 'false', 0, null),
              l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/InventoryPlanningCode/text()').getStringVal(),
              --l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/ReorderSetup/MaximumInventoryDuration/text()').getStringVal(),
              --l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/ReorderSetup/MinimumInventoryDuration/text()').getStringVal(),
              --l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/ReorderSetup/MaximumReorderQuantity/text()').getStringVal(),
              --l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/ReorderSetup/MinimumReorderQuantity/text()').getStringVal(),
              --l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/ReorderSetup/ReorderQuantity/text()').getStringVal(),
              l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/MinMaxSetup/MinimumInventoryQuantity/text()').getStringVal(),
              l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/MinMaxSetup/MaximumInventoryQuantity/text()').getStringVal(),
              l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/MinimumProductionOrderQuantity/text()').getStringVal(),
              l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/ShrinkageRate/text()').getStringVal(),

              l_xml_ItemEBO.items.extract('/Item/ManufacturingConfiguration/BOMItemTypeCode/text()').getStringVal(),
              l_xml_ItemEBO.items.extract('/Item/ManufacturingConfiguration/ConfiguratorModelTypeCode/text()').getStringVal(),
              l_xml_ItemEBO.items.extract('/Item/ManufacturingConfiguration/EffectivityControlCode/text()').getStringVal(),
              l_xml_ItemEBO.items.extract('/Item/ManufacturingConfiguration/WIPSupplyTypeCode/text()').getStringVal(),
              Decode(l_xml_ItemEBO.items.extract('/Item/ManufacturingConfiguration/EngineeringItemIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
              Decode(l_xml_ItemEBO.items.extract('/Item/ManufacturingConfiguration/AllowStructureIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
              Decode(l_xml_ItemEBO.items.extract('/Item/ManufacturingConfiguration/CostingEnabledIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
              Decode(l_xml_ItemEBO.items.extract('/Item/ManufacturingConfiguration/InventoryAssetIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
              l_xml_ItemEBO.items.extract('/Item/ManufacturingConfiguration/StandardLotSizeQuantity/text()').getStringVal(),

              Decode(l_xml_ItemEBO.items.extract('/Item/OrderManagementConfiguration/BackOrderEnabledIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
              Decode(l_xml_ItemEBO.items.extract('/Item/OrderManagementConfiguration/ReturnableIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
              --Decode(l_xml_ItemEBO.items.extract('/Item/OrderManagementConfiguration/AssembleToOrderIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),

              l_xml_ItemEBO.items.extract('/Item/ItemCatalog[PrimaryIndicator = "true"]/CatalogReference/CatalogIdentification/Identification/ID[@schemeName = "GPC"]/text()').getStringVal(),
              -- Expecting the trade_item_descriptor as the BaseUOM
              l_xml_ItemEBO.items.extract('/Item/ItemBase/BaseUOMCode/text()').getStringVal(),

              l_created_by,
              l_creation_date,
              l_last_updated_by,
              l_last_update_date,
              l_last_update_login
            );
Line: 1495

            SELECT XMLELEMENT("TL", l_xml_ItemEBO.items.extract('/Item/ItemIdentification/Name'))
            INTO l_xml_trans
            FROM DUAL;
Line: 1510

            SELECT XMLELEMENT("TL", l_xml_ItemEBO.items.extract('/Item/ItemBase/Description'))
            INTO l_xml_trans
            FROM DUAL;
Line: 1525

            INSERT INTO EGO_UCCNET_EVENTS (
              source_system_id,
              source_system_reference,
              message_id,
              import_batch_id,
              ext_complex_item_reference,
              batch_id,
              event_row_id,
              event_type,
              event_action,
              gtin,
              supplier_gln,
              target_market,
              cln_id,
              disposition_code,
              disposition_date,

              CREATED_BY,
              CREATION_DATE,
              LAST_UPDATED_BY,
              LAST_UPDATE_DATE,
              LAST_UPDATE_LOGIN

            ) VALUES (
              p_source_sys_id,
              l_source_sys_reference,
              l_message_id,
              l_batch_id,
              l_external_bundle_id, -- External Bundle Id
              -1,
              l_transaction_id, -- PDUTTA:IDentify seq
              'PUBLICATION_INBOUND',
              'NEW_ITEM', -- Action or NEW_ITEM
              l_source_sys_reference,
              '-1', -- Supplier GLN
              '-1', -- Tgt Mgt
              l_bundle_id,
              EGO_POST_PROCESS_MESSAGE_PVT.G_CIC_ACCEPTED_MESSAGE_TYPE,
              sysdate,

              l_created_by,
              l_creation_date,
              l_last_updated_by,
              l_last_update_date,
              l_last_update_login

            );
Line: 1582

              INSERT INTO MTL_ITEM_CATEGORIES_INTERFACE(
                transaction_id,
                category_set_name,
                category_name,
                source_system_id,
                source_system_reference,
                bundle_id,
                set_process_id,

                CREATED_BY,
                CREATION_DATE,
                LAST_UPDATED_BY,
                LAST_UPDATE_DATE,
                LAST_UPDATE_LOGIN
              ) VALUES (
                l_transaction_id,
                l_xml_Classification.classifications.extract('/ItemCatalog/CatalogReference/CatalogIdentification/Identification/ID/@schemeName').getStringVal(),
                l_alt_cat_concat_seg,
                p_source_sys_id,
                l_source_sys_reference,
                l_bundle_id,
                l_batch_id,

                l_created_by,
                l_creation_date,
                l_last_updated_by,
                l_last_update_date,
                l_last_update_login
              );
Line: 1635

                  INSERT INTO EGO_ITEM_ASSOCIATIONS_INTF(
                    transaction_id,
                    batch_id,
                    source_system_id,
                    source_system_reference,
                    bundle_id,

                    pk1_value,
                    supplier_name,
                    supplier_number,
                    --supplier_site_name,
                    data_level_name,

                    CREATED_BY,
                    CREATION_DATE,
                    LAST_UPDATED_BY,
                    LAST_UPDATE_DATE,
                    LAST_UPDATE_LOGIN,
                    transaction_type
                  ) VALUES(
                    l_transaction_id,
                    l_batch_id,
                    p_source_sys_id,
                    l_source_sys_reference,
                    l_bundle_id,

                    l_supplier_id,
                    l_supplier_name,
                    l_supplier_id,
                    --l_supplier_site_name,
                    'ITEM_SUP',

                    l_created_by,
                    l_creation_date,
                    l_last_updated_by,
                    l_last_update_date,
                    l_last_update_login,
                    'SYNC'
                  );
Line: 1688

                                p_last_updated_by         => l_last_updated_by,
                                p_last_update_date        => l_last_update_date,
                                p_last_update_login       => l_last_update_login);
Line: 1714

                        INSERT INTO EGO_ITEM_ASSOCIATIONS_INTF(
                          transaction_id,
                          batch_id,
                          source_system_id,
                          source_system_reference,
                          bundle_id,

                          pk1_value,
                          pk2_value,
                          supplier_name,
                          supplier_number,
                          supplier_site_name,
                          data_level_name,

                          CREATED_BY,
                          CREATION_DATE,
                          LAST_UPDATED_BY,
                          LAST_UPDATE_DATE,
                          LAST_UPDATE_LOGIN,
                          transaction_type
                        ) VALUES(
                          l_transaction_id,
                          l_batch_id,
                          p_source_sys_id,
                          l_source_sys_reference,
                          l_bundle_id,

                          l_supplier_id,
                          l_supplier_site_id,
                          l_supplier_name,
                          l_supplier_site_id,
                          l_xml_SupplierSite.supplierLocations.extract('/ItemSupplierLocation/LocationReference/Name/text()').getStringVal(),
                          'ITEM_SUP_SITE',

                          l_created_by,
                          l_creation_date,
                          l_last_updated_by,
                          l_last_update_date,
                          l_last_update_login,
                          'SYNC'
                      );
Line: 1758

                      INSERT INTO EGO_ITEM_ASSOCIATIONS_INTF(
                        transaction_id,
                        batch_id,
                        source_system_id,
                        source_system_reference,
                        bundle_id,
                        organization_code,

                        pk1_value,
                        pk2_value,
                        supplier_name,
                        supplier_number,
                        supplier_site_name,
                        data_level_name,

                        CREATED_BY,
                        CREATION_DATE,
                        LAST_UPDATED_BY,
                        LAST_UPDATE_DATE,
                        LAST_UPDATE_LOGIN,
                        transaction_type
                      ) VALUES(
                        l_transaction_id,
                        l_batch_id,
                        p_source_sys_id,
                        l_source_sys_reference,
                        l_bundle_id,
                        l_org_code,

                        l_supplier_id,
                        l_supplier_site_id,
                        l_supplier_name,
                        l_supplier_site_id,
                        l_xml_SupplierSite.supplierLocations.extract('/ItemSupplierLocation/LocationReference/Name/text()').getStringVal(),
                        l_supplier_attr_level,

                        l_created_by,
                        l_creation_date,
                        l_last_updated_by,
                        l_last_update_date,
                        l_last_update_login,
                        'SYNC'
                      );
Line: 1815

                                      p_last_updated_by         => l_last_updated_by,
                                      p_last_update_date        => l_last_update_date,
                                      p_last_update_login       => l_last_update_login);
Line: 1844

                            p_last_updated_by         => l_last_updated_by,
                            p_last_update_date        => l_last_update_date,
                            p_last_update_login       => l_last_update_login);
Line: 1859

              INSERT INTO BOM_BILL_OF_MTLS_INTERFACE(
                transaction_id,
                batch_id,
                --source_system_id,
                source_system_reference,
                bundle_id,
                alternate_bom_designator,
                organization_code,

                CREATED_BY,
                CREATION_DATE,
                LAST_UPDATED_BY,
                LAST_UPDATE_DATE,
                LAST_UPDATE_LOGIN
              ) VALUES(
                NULL,--l_transaction_id,
                l_batch_id,
                --p_source_sys_id,
                l_hdr_source_sys_reference,
                l_bundle_id,
                'PIM_PBOM_S',
                l_org_code,

                l_created_by,
                l_creation_date,
                l_last_updated_by,
                l_last_update_date,
                l_last_update_login
              );
Line: 1891

                INSERT INTO BOM_INVENTORY_COMPS_INTERFACE(
                  transaction_id,
                  batch_id,
                  --source_system_id,
                  parent_source_system_reference,
                  comp_source_system_reference,
                  bundle_id,
                  alternate_bom_designator,
                  organization_code,
                  component_quantity,
                  primary_unit_of_measure,

                  CREATED_BY,
                  CREATION_DATE,
                  LAST_UPDATED_BY,
                  LAST_UPDATE_DATE,
                  LAST_UPDATE_LOGIN
                ) VALUES(
                  NULL,--l_transaction_id,
                  l_batch_id,
                  --p_source_sys_id,
                  l_hdr_source_sys_reference,
                  l_xml_component.components.extract('/ComponentItem/ItemReference/ItemIdentification/GTIN/text()').getStringVal(),
                  l_bundle_id,
                  'PIM_PBOM_S',
                  l_org_code,
                  l_xml_component.components.extract('/ComponentItem/ComponentItemBase/Quantity/text()').getStringVal(),
                  l_xml_component.components.extract('/ComponentItem/ComponentItemBase/Quantity/@unitCode').getStringVal(),

                  l_created_by,
                  l_creation_date,
                  l_last_updated_by,
                  l_last_update_date,
                  l_last_update_login
              );
Line: 1964

            WHEN EGO_ORC_DELETE_LINE --Ignore Delete messages
            THEN
              NULL;