APPS.OPI_DBI_WMS_UTILITY_PKG SQL Statements

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

Line: 163

    SELECT
        count (*)
      FROM  mtl_parameters mp,
            eni_oltp_item_star items
      WHERE mp.organization_id = items.organization_id
        AND mp.wms_enabled_flag = 'Y'
        AND mp.process_enabled_flag <> 'Y'
        AND (   items.weight_uom_code IS NULL
             OR items.volume_uom_code IS NULL
             OR items.unit_weight IS NULL
             OR items.unit_volume IS NULL);

Line: 182

    'SELECT
        (orgs.name || '' ('' || mp.organization_code || '')'') org,
        items.value item,
        CASE
            WHEN items.weight_uom_code IS NULL OR
                 items.unit_weight IS NULL THEN
                1 /*C_WT_MISSING*/
            ELSE
                0 /*C_NOTHING_MISSING*/
            END wt_missing_flag,
        CASE
            WHEN items.volume_uom_code IS NULL OR
                 items.unit_volume IS NULL THEN
                2 /*C_VOL_MISSING*/
            ELSE
                3 /*C_NOTHING_MISSING*/
            END vol_missing_flag
      FROM  mtl_parameters mp,
            eni_oltp_item_star items,
            hr_all_organization_units_vl orgs
      WHERE mp.organization_id = orgs.organization_id
        AND mp.organization_id = items.organization_id
        AND mp.wms_enabled_flag = ''Y''
        AND mp.process_enabled_flag <> ''Y''
        AND (   items.weight_uom_code IS NULL
             OR items.volume_uom_code IS NULL
             OR items.unit_weight IS NULL
             OR items.unit_volume IS NULL)
      ORDER BY
        (orgs.name || '' ('' || mp.organization_code || '')''),
        items.value';

Line: 475

    SELECT
        count (*)
      FROM  mtl_parameters mp,
            mtl_item_locations mil
      WHERE mp.organization_id = mil.organization_id
        AND mp.wms_enabled_flag = 'Y'
        AND mp.process_enabled_flag <> 'Y'
        AND (   mil.max_weight IS NULL
             OR mil.max_cubic_area IS NULL
             OR mil.location_weight_uom_code IS NULL
             OR mil.volume_uom_code IS NULL);

Line: 494

    'SELECT
        (orgs.name || '' ('' || mp.organization_code || '')'') org,
        mil.subinventory_code sub,
        INV_PROJECT.get_locator (mil.inventory_location_id,
                                 mil.organization_id) loc,
        CASE
            WHEN mil.max_weight IS NULL OR
                 mil.location_weight_uom_code IS NULL THEN
                1 /*C_WT_MISSING*/
            ELSE
                0 /*C_NOTHING_MISSING*/
            END wt_missing_flag,
        CASE
            WHEN mil.max_cubic_area IS NULL OR
                 mil.volume_uom_code IS NULL THEN
                2 /*C_VOL_MISSING*/
            ELSE
                0 /*C_NOTHING_MISSING*/
            END vol_missing_flag
      FROM  mtl_parameters mp,
            mtl_item_locations mil,
            hr_all_organization_units_vl orgs
      WHERE mp.organization_id = orgs.organization_id
        AND mp.organization_id = mil.organization_id
        AND mp.wms_enabled_flag = ''Y''
        AND mp.process_enabled_flag <> ''Y''
        AND (   mil.max_weight IS NULL
             OR mil.max_cubic_area IS NULL
             OR mil.location_weight_uom_code IS NULL
             OR mil.volume_uom_code IS NULL)
      ORDER BY
        (orgs.name || '' ('' || mp.organization_code || '')''),
        mil.subinventory_code,
        INV_PROJECT.get_locator (mil.inventory_location_id,
                                 mil.organization_id)';

Line: 745

    SELECT
        (orgs.name || ' (' || mp.organization_code || ')') org,
        items.value item,
        errors.volume_uom_code
      FROM
        (SELECT
            organization_id,
            inventory_item_id || '-' || organization_id item_org_id,
            unit_volume_uom_code volume_uom_code
          FROM  opi_dbi_wms_stor_item_conv_stg
          WHERE volume_conv_rate < 0
        UNION
        SELECT
            organization_id,
            item_org_id,
            volume_uom_code
          FROM  opi_dbi_wms_curr_utz_item_f
          WHERE utilized_volume/volume_qty < 0
            AND aggregation_level_flag = C_ITEM_AGGR_LEVEL
        ) errors,
        eni_oltp_item_star items,
        hr_all_organization_units_vl orgs,
        mtl_parameters mp
      WHERE errors.organization_id = orgs.organization_id
        AND errors.organization_id = items.organization_id
        AND errors.item_org_id = items.id
        AND errors.organization_id = mp.organization_id
      ORDER BY
        (orgs.name || ' (' || mp.organization_code || ')'),
        items.value;

Line: 778

    SELECT
        (orgs.name || ' (' || mp.organization_code || ')') org,
        items.value item,
        errors.weight_uom_code
      FROM
        (SELECT
            organization_id,
            inventory_item_id || '-' || organization_id item_org_id,
            unit_weight_uom_code weight_uom_code
          FROM  opi_dbi_wms_stor_item_conv_stg
          WHERE weight_conv_rate < 0
        UNION
        SELECT
            organization_id,
            item_org_id,
            weight_uom_code
          FROM  opi_dbi_wms_curr_utz_item_f
          WHERE stored_weight/weight_qty < 0
            AND aggregation_level_flag = C_ITEM_AGGR_LEVEL
        ) errors,
        eni_oltp_item_star items,
        hr_all_organization_units_vl orgs,
        mtl_parameters mp
      WHERE errors.organization_id = orgs.organization_id
        AND errors.organization_id = items.organization_id
        AND errors.item_org_id = items.id
        AND errors.organization_id = mp.organization_id
      ORDER BY
        (orgs.name || ' (' || mp.organization_code || ')'),
        items.value;

Line: 812

    SELECT rep_uom_code
      FROM  opi_dbi_rep_uoms
      WHERE measure_code = p_measure_code;

Line: 1083

    SELECT
        (orgs.name || ' (' || mp.organization_code || ')') org,
        stg.subinventory_code sub,
        INV_PROJECT.get_locator (stg.locator_id, stg.organization_id)
            loc,
        stg.volume_uom_code
      FROM  opi_dbi_wms_curr_utz_sub_stg stg,
            mtl_parameters mp,
            hr_all_organization_units_vl orgs
      WHERE mp.organization_id = orgs.organization_id
        AND mp.wms_enabled_flag = 'Y'
        AND mp.process_enabled_flag <> 'Y'
        AND mp.organization_id = stg.organization_id
        AND stg.volume_capacity_rep < 0
      ORDER BY
        (orgs.name || ' (' || mp.organization_code || ')'),
        stg.subinventory_code,
        INV_PROJECT.get_locator (stg.locator_id, stg.organization_id);

Line: 1104

    SELECT
        (orgs.name || ' (' || mp.organization_code || ')') org,
        stg.subinventory_code sub,
        INV_PROJECT.get_locator (stg.locator_id, stg.organization_id)
            loc,
        stg.weight_uom_code
      FROM  opi_dbi_wms_curr_utz_sub_stg stg,
            mtl_parameters mp,
            hr_all_organization_units_vl orgs
      WHERE mp.organization_id = orgs.organization_id
        AND mp.wms_enabled_flag = 'Y'
        AND mp.process_enabled_flag <> 'Y'
        AND mp.organization_id = stg.organization_id
        AND stg.weight_capacity_rep < 0
      ORDER BY
        (orgs.name || ' (' || mp.organization_code || ')'),
        stg.subinventory_code,
        INV_PROJECT.get_locator (stg.locator_id, stg.organization_id);

Line: 1126

    SELECT rep_uom_code
      FROM  opi_dbi_rep_uoms
      WHERE measure_code = p_measure_code;

Line: 1521

    SELECT last_run_date
      FROM  opi_dbi_conc_prog_run_log
      WHERE ETL_TYPE = C_WDTH_CU1_DATE_TYPE;

Line: 1527

    SELECT /*+ parallel (wdth) */
        nvl (min (wdth.creation_date), sysdate)
      FROM  wms_dispatched_tasks_history wdth
      WHERE transaction_temp_id IS NOT NULL;

Line: 1580

            (SELECT
                C_WDTH_CU1_DATE_TYPE etl_type,
                l_wdth_cu1_date last_run_date,
                sysdate creation_date,
                sysdate last_update_date,
                l_user_id created_by,
                l_user_id last_updated_by,
                l_login_id last_update_login,
                l_program_application_id program_application_id,
                l_program_id program_id,
                l_program_login_id program_login_id,
                l_request_id request_id
              FROM  dual) new
        ON (base.etl_type = new.etl_type)
        WHEN MATCHED THEN UPDATE
        SET
            base.last_run_date = new.last_run_date,
            base.last_update_date = new.last_update_date,
            base.last_updated_by = new.last_updated_by,
            base.last_update_login = new.last_update_login,
            base.program_id = new.program_id,
            base.program_login_id = new.program_login_id,
            base.program_application_id = new.program_application_id,
            base.request_id = new.request_id
        WHEN NOT MATCHED THEN
        INSERT (
            base.etl_type,
            base.last_run_date,
            base.creation_date,
            base.last_update_date,
            base.created_by,
            base.last_updated_by,
            base.last_update_login,
            base.program_id,
            base.program_login_id,
            base.program_application_id,
            base.request_id
        )
        VALUES (
            new.etl_type,
            new.last_run_date,
            new.creation_date,
            new.last_update_date,
            new.created_by,
            new.last_updated_by,
            new.last_update_login,
            new.program_id,
            new.program_login_id,
            new.program_application_id,
            new.request_id);

Line: 1646

    As a side effect, populates/updates the WDTH CU1 date as needed.

    Also, we don't want to modify this date unless it is different,
    because otherwise MVs that depend on this might not fast refresh.

    Parameters:
    p_overwrite - if true, then function force updates the WDTH CU1 date.
                  if false, then WDTH CU1 date is not modified (if
                  it already exists).

    History:
    Date        Author              Action
    02/18/05    Dinkar Gupta        Wrote Function.

*/
PROCEDURE set_wms_pts_gsd (p_overwrite BOOLEAN)
IS

    -- WDTH CU1 date
    CURSOR wdth_cu1_date_csr IS
    SELECT
        last_run_date
      FROM opi_dbi_conc_prog_run_log
      WHERE etl_type = C_WDTH_CU1_DATE_TYPE;

Line: 1673

    SELECT
        last_run_date
      FROM opi_dbi_conc_prog_run_log
      WHERE etl_type = C_WMS_PTS_DATE_TYPE;

Line: 1736

            (SELECT
                C_WMS_PTS_DATE_TYPE etl_type,
                l_pts_start_date last_run_date,
                sysdate creation_date,
                sysdate last_update_date,
                l_user_id created_by,
                l_user_id last_updated_by,
                l_login_id last_update_login,
                l_program_application_id program_application_id,
                l_program_id program_id,
                l_program_login_id program_login_id,
                l_request_id request_id
              FROM  dual) new
        ON (base.etl_type = new.etl_type)
        WHEN MATCHED THEN UPDATE
        SET
            base.last_run_date = new.last_run_date,
            base.last_update_date = new.last_update_date,
            base.last_updated_by = new.last_updated_by,
            base.last_update_login = new.last_update_login,
            base.program_id = new.program_id,
            base.program_login_id = new.program_login_id,
            base.program_application_id = new.program_application_id,
            base.request_id = new.request_id
        WHEN NOT MATCHED THEN
        INSERT (
            base.etl_type,
            base.last_run_date,
            base.creation_date,
            base.last_update_date,
            base.created_by,
            base.last_updated_by,
            base.last_update_login,
            base.program_id,
            base.program_login_id,
            base.program_application_id,
            base.request_id
        )
        VALUES (
            new.etl_type,
            new.last_run_date,
            new.creation_date,
            new.last_update_date,
            new.created_by,
            new.last_updated_by,
            new.last_update_login,
            new.program_id,
            new.program_login_id,
            new.program_application_id,
            new.request_id);