APPS.MSC_WS_APS_DATA_SERVICES SQL Statements

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

Line: 76

PROCEDURE InsertOutputLevels(ScenarioId IN NUMBER, ItemOutputLevel IN VARCHAR2, OrganizationOutputLevel IN VARCHAR2, CustomerOutputLevel IN VARCHAR2, DemandClassOutputLevel IN VARCHAR2);

Line: 87

PROCEDURE InsertForecast(ScenarioId IN NUMBER, ForecastData IN MscForecastRec, OrganizationId IN NUMBER, SrItemId IN NUMBER);

Line: 115

PROCEDURE InsertSafetyStock(PlanId IN NUMBER, SafetyStockData IN MscSafetyStockRec);

Line: 116

PROCEDURE UpdateSafetyStock(PlanId IN NUMBER, SafetyStockData IN MscSafetyStockRec);

Line: 124

PROCEDURE InsertPlannedSupply(PlanId IN NUMBER, SrInstId IN NUMBER, PlannedSupplyData IN MscPlannedSupplyRec);

Line: 141

        SELECT MSD_DP_SCENARIOS_S.NEXTVAL INTO ScenarioId FROM DUAL;

Line: 143

        INSERT INTO msd_dp_scenarios
            (
            demand_plan_id, scenario_id, scenario_name, forecast_based_on,
            last_update_date, last_updated_by, creation_date, created_by
            )
        VALUES
            (
            g_DummyDemandPlanId, ScenarioId, ScenarioName, 'APS_DATA_SERVICE',
            sysdate, g_UserId, sysdate, g_UserId
            );

Line: 178

        SELECT distinct demand_plan_id INTO DemandPlanId
        FROM msd_dp_ascp_scenarios_v
        WHERE demand_plan_name = DemandPlanName;

Line: 212

        SELECT scenario_id
        INTO ScenarioId
        FROM msd_dp_scenarios
        WHERE
            demand_plan_id = DemandPlanId AND
            scenario_name = ScenarioName;

Line: 299

        DELETE FROM msd_dp_scn_entries_denorm
        WHERE scenario_id = ScenarioId;

Line: 303

        DELETE FROM msd_dp_scenario_output_levels
        WHERE
            demand_plan_id = g_DummyDemandPlanId AND
            scenario_id = ScenarioId;

Line: 331

PROCEDURE InsertOutputLevels(
        ScenarioId         IN         NUMBER,
        ItemOutputLevel    IN         VARCHAR2,
        OrganizationOutputLevel
                           IN         VARCHAR2,
        CustomerOutputLevel
                           IN         VARCHAR2,
        DemandClassOutputLevel
                           IN         VARCHAR2
) AS
l_OutputLevels      MscNumberArr;

Line: 347

        INSERT INTO msd_dp_scenario_output_levels
        (
            demand_plan_id, scenario_id, level_id,
            last_update_date, last_updated_by, creation_date, created_by
        )
        VALUES
        (
            g_DummyDemandPlanId, ScenarioId, l_OutputLevels(I),
            sysdate, g_UserId, sysdate, g_UserId
        );

Line: 362

END InsertOutputLevels;

Line: 400

        SELECT count(*) INTO l_Count FROM msd_dp_scenario_output_levels
        WHERE demand_plan_id = g_DummyDemandPlanId AND scenario_id = ScenarioId;

Line: 409

                    SELECT 1 INTO l_Count
                    FROM msd_dp_scenario_output_levels
                    WHERE
                        demand_plan_id = g_DummyDemandPlanId AND
                        scenario_id = ScenarioId AND
                        level_id = l_OutputLevels(I);

Line: 447

        SELECT count(*) INTO l_Count
        FROM fnd_lookup_values
        WHERE lookup_type = 'MSC_X_BUCKET_TYPE' AND lookup_code = BucketType;

Line: 502

                SELECT TRUNC(StartDate, 'MONTH') INTO l_Date FROM DUAL;

Line: 507

                    SELECT LAST_DAY(StartDate) INTO l_Date FROM DUAL;

Line: 553

        SELECT validation_org_id INTO l_ValidationOrgId
        FROM msc_apps_instances
        WHERE instance_id = SrInstanceId;

Line: 566

            SELECT 1 INTO l_Dummy
            FROM msc_trading_partners
            WHERE
                partner_type = 3 AND
                sr_instance_id = SrInstanceId AND
                sr_tp_id = SrOrgId;

Line: 666

            SELECT 1 INTO l_Count
            FROM msc_items
            WHERE
                inventory_item_id = ItemId;

Line: 675

        SELECT count(*) INTO l_Count
        FROM msc_system_items
        WHERE
            plan_id = -1 and
            inventory_item_id = ItemId and
            sr_instance_id = SrInstanceId and
            organization_id = OrgId and
            bom_item_type = 5;

Line: 688

        SELECT sr_inventory_item_id INTO SrItemId
        FROM msc_system_items
        WHERE
            plan_id = -1 and
            inventory_item_id = ItemId and
            sr_instance_id = SrInstanceId and
            organization_id = OrgId;

Line: 742

            SELECT 1 INTO l_Dummy
            FROM msc_tp_site_id_lid
            WHERE
                sr_tp_site_id = ShipToLocation AND
                partner_type = 2 AND
                sr_instance_id = SrInstanceId;

Line: 761

            SELECT 1 INTO l_Dummy
            FROM msc_tp_id_lid
            WHERE
                sr_tp_id = SrCustomerId AND
                partner_type = 2 AND
                sr_instance_id = SrInstanceId;

Line: 780

            SELECT 1 INTO l_Dummy
            FROM msc_tp_id_lid
            WHERE
                sr_tp_id = SrCustomerId AND
                partner_type = 2 AND
                sr_instance_id = SrInstanceId;

Line: 791

            SELECT 1 INTO l_Dummy
            FROM msc_regions
            WHERE
                region_id = SrZoneId AND
                sr_instance_id = SrInstanceId;

Line: 809

            SELECT 1 INTO l_Dummy
            FROM msc_regions
            WHERE
                region_id = SrZoneId AND
                sr_instance_id = SrInstanceId;

Line: 857

        SELECT uom_code INTO l_UomCode
        FROM msc_system_items
        WHERE
            plan_id = -1 and
            sr_instance_id = SrInstanceId and
            organization_id = OrgId and
            inventory_item_id = ItemId;

Line: 900

            SELECT 1 INTO l_Dummy
            FROM msc_demand_classes
            WHERE
                demand_class = DemandClass AND
                sr_instance_id = SrInstanceId;

Line: 934

            SELECT count(*) INTO l_Count
            FROM msd_dp_scn_entries_denorm
            WHERE scenario_id = ScenarioId;

Line: 941

                SELECT MAX(demand_id) INTO l_Count
                FROM msd_dp_scn_entries_denorm
                WHERE
                    demand_plan_id = g_DummyDemandPlanId AND
                    scenario_id = ScenarioId;

Line: 971

PROCEDURE InsertForecast(
        ScenarioId           IN         NUMBER,
        ForecastData         IN         MscForecastRec,
        OrganizationId       IN         NUMBER,
        SrItemId             IN         NUMBER
) AS
BEGIN
    -- generate an unique demand id
    GenerateDemandId(ScenarioId);

Line: 982

    INSERT INTO msd_dp_scn_entries_denorm
    (
        demand_plan_id, scenario_id, demand_id, bucket_type, start_time, end_time, quantity,
        sr_organization_id, sr_instance_id, sr_inventory_item_id, error_type, forecast_error,
        inventory_item_id, sr_ship_to_loc_id, sr_customer_id, sr_zone_id, priority,
        dp_uom_code, ascp_uom_code, demand_class, unit_price,
        creation_date, created_by, last_update_login
    )
    VALUES
    (
        g_DummyDemandPlanId ,          -- demand_plan_id
        ScenarioId,                    -- scenario_id
        g_DemandId,                    -- demand_id
        ForecastData.BucketType,       -- bucket_type
        ForecastData.StartDate,        -- start_time
        ForecastData.EndDate,          -- end_time
        ForecastData.Quantity,         -- quantity
        ForecastData.SrOrganizationId, -- sr_organization_id
        ForecastData.SrInstanceId,     -- sr_instance_id
        SrItemId,                      -- sr_inventory_item_id
        ForecastData.ErrorType,        -- error_type
        ForecastData.ForecastError,    -- forecast_error
        ForecastData.ItemId,           -- inventory_item_id
        ForecastData.ShipToLocation,   -- sr_ship_to_loc_id
        ForecastData.SrCustomerId,     -- sr_customer_id
        ForecastData.SrZoneId,         -- sr_zone_id
        ForecastData.Priority,         -- priority
        ForecastData.Uom,              -- dp_uom_code
        ForecastData.Uom,              -- ascp_uom_code
        ForecastData.DemandClass,      -- demand_class
        ForecastData.UnitPrice,        -- unit_price
        sysdate,                       -- creation_date
        g_UserId,                      -- created_by
        g_UserId                       -- last_update_login
    ) ;

Line: 1021

END InsertForecast;

Line: 1139

    InsertForecast(ScenarioId, ForecastData, l_OrgId, l_SrItemId);

Line: 1477

    l_Sql := 'SELECT '                                                         ||
                  'nvl(bucket_type, -23453), '                                 ||
                  'nvl(start_time, to_date(''1970-01-01'', ''YYYY-MM-DD'')), ' ||
                  'nvl(end_time, to_date(''1970-01-01'', ''YYYY-MM-DD'')), '   ||
                  'nvl(quantity, -23453), '                                    ||
                  'sr_organization_id, '                                       ||
                  'nvl(sr_instance_id, -23453), '                              ||
                  'error_type, '                                               ||
                  'forecast_error, '                                           ||
                  'nvl(inventory_item_id, -23453), '                           ||
                  'sr_ship_to_loc_id, '                                        ||
                  'sr_customer_id, '                                           ||
                  'sr_zone_id, '                                               ||
                  'nvl(priority, -23453), '                                    ||
                  'nvl(ascp_uom_code, ''''), '                                 ||
                  'demand_class, '                                             ||
                  'nvl(unit_price, -23453) '                                   ||
             'FROM msd_dp_scn_entries_denorm '                                 ||
             'WHERE '                                                          ||
                 WhereClause;

Line: 1566

        SELECT plan_id
        INTO PlanId
        FROM msc_plans
        WHERE
            organization_id = OwningOrgId AND
            compile_designator = PlanName AND
            sr_instance_id = SrInstanceId;

Line: 1604

        SELECT 1 INTO l_Dummy
        FROM
            msc_plan_organizations
        WHERE
            plan_id = PlanId AND
            organization_id = OrgId AND
            sr_instance_id = SrInstId;

Line: 1642

        SELECT 1 INTO l_Dummy
        FROM msc_system_items
        WHERE
            plan_id = planId AND
            sr_instance_id = SrInstId AND
            organization_id = OrgId AND
            inventory_item_id = ItemId;

Line: 1681

        SELECT 1 INTO l_Dummy
        FROM msc_plan_buckets
        WHERE
            plan_id = PlanId AND
            organization_id = OrgId AND
            sr_instance_id = SrInstId AND
            curr_flag = 1 AND
            trunc(bkt_start_date) = PeriodStartDate; -- PeriodStartDate is date only in xsd

Line: 1735

            SELECT 1 INTO l_Dummy
            FROM msc_projects
            WHERE
                plan_id = PlanId AND
                sr_instance_id = SrInstId AND
                organization_id = OrgId AND
                project_id = ProjectId;

Line: 1748

                SELECT 1 INTO l_Dummy
                FROM msc_project_tasks
                WHERE
                    plan_id = PlanId AND
                    sr_instance_id = SrInstId AND
                    organization_id = OrgId AND
                    project_id = ProjectId AND
                    task_id = TaskId;

Line: 1763

                SELECT 1 INTO l_Dummy
                FROM pjm_project_parameters
                WHERE
                    organization_id = OrgId AND
                    project_id = ProjectId AND
                    planning_group = PlanningGroup;

Line: 1793

PROCEDURE InsertSafetyStock(
        PlanId             IN         NUMBER,
        SafetyStockData    IN         MscSafetyStockRec
) AS
BEGIN
    BEGIN
        INSERT INTO msc_safety_stocks
            (
            plan_id, organization_id, sr_instance_id, inventory_item_id,
            period_start_date, safety_stock_quantity,
            last_update_date, last_updated_by, creation_date, created_by,
            target_safety_stock,
            project_id, task_id, planning_group,
            user_defined_safety_stocks, user_defined_dos,
            target_days_of_supply, achieved_days_of_supply,
            demand_var_ss_percent, mfg_ltvar_ss_percent,
            transit_ltvar_ss_percent, sup_ltvar_ss_percent,
            total_unpooled_safety_stock
            )
        VALUES
            (
            PlanId, SafetyStockData.OrganizationId, SafetyStockData.SrInstanceId, SafetyStockData.ItemId,
            SafetyStockData.PeriodStartDate, SafetyStockData.SafetyStockQty,
            sysdate, g_UserId, sysdate, g_UserId,
            SafetyStockData.TargetSafetyStock,
            SafetyStockData.ProjectId, SafetyStockData.TaskId, SafetyStockData.PlanningGroup,
            SafetyStockData.UserDefinedSafetyStock, SafetyStockData.UserDefinedDOS,
            SafetyStockData.TargetDOS, SafetyStockData.AchievedDOS,
            SafetyStockData.DemandVarSSPct, SafetyStockData.MfgLTVarSSPct,
            SafetyStockData.TransitLTVarSSPct, SafetyStockData.SupLTVarSSPct,
            SafetyStockData.TotalUnpooledSS
            ) ;

Line: 1827

                UpdateSafetyStock(PlanId, SafetyStockData);

Line: 1833

END InsertSafetyStock;

Line: 1844

PROCEDURE UpdateSafetyStock(
        PlanId             IN         NUMBER,
        SafetyStockData    IN         MscSafetyStockRec
) AS
BEGIN
    UPDATE msc_safety_stocks
    SET
        safety_stock_quantity       = SafetyStockData.SafetyStockQty,
        last_update_date            = sysdate,
        last_updated_by             = g_UserId,
        target_safety_stock         = SafetyStockData.TargetSafetyStock,
        user_defined_safety_stocks  = SafetyStockData.UserDefinedSafetyStock,
        user_defined_dos            = SafetyStockData.UserDefinedDOS,
        target_days_of_supply       = SafetyStockData.TargetDOS,
        achieved_days_of_supply     = SafetyStockData.AchievedDOS,
        demand_var_ss_percent       = SafetyStockData.DemandVarSSPct,
        mfg_ltvar_ss_percent        = SafetyStockData.MfgLTVarSSPct,
        transit_ltvar_ss_percent    = SafetyStockData.TransitLTVarSSPct,
        sup_ltvar_ss_percent        = SafetyStockData.SupLTVarSSPct,
        total_unpooled_safety_stock = SafetyStockData.TotalUnpooledSS
    WHERE
        plan_id                     = PlanId                                 AND
        organization_id             = SafetyStockData.OrganizationId         AND
        sr_instance_id              = SafetyStockData.SrInstanceId           AND
        inventory_item_id           = SafetyStockData.ItemId                 AND
        period_start_date           = SafetyStockData.PeriodStartDate        AND
        nvl(project_id, -1)         = nvl(SafetyStockData.ProjectId, -1)     AND
        nvl(task_id, -1)            = nvl(SafetyStockData.TaskId, -1)        AND
        nvl(planning_group, -1)     = nvl(SafetyStockData.PlanningGroup, -1) AND
        unit_number IS NULL;

Line: 1878

END UpdateSafetyStock;

Line: 2004

    InsertSafetyStock(PlanId, SafetyStockData);

Line: 2050

    l_Sql := 'SELECT '                            ||
                  'organization_id, '             ||
                  'sr_instance_id, '              ||
                  'inventory_item_id, '           ||
                  'period_start_date, '           ||
                  'safety_stock_quantity, '       ||
                  'target_safety_stock, '         ||
                  'project_id, '                  ||
                  'task_id, '                     ||
                  'planning_group, '              ||
                  'user_defined_safety_stocks, '  ||
                  'user_defined_dos, '            ||
                  'target_days_of_supply, '       ||
                  'achieved_days_of_supply, '     ||
                  'demand_var_ss_percent, '       ||
                  'mfg_ltvar_ss_percent, '        ||
                  'transit_ltvar_ss_percent, '    ||
                  'sup_ltvar_ss_percent, '        ||
                  'total_unpooled_safety_stock '  ||
             'FROM msc_safety_stocks '            ||
             'WHERE '                             ||
                 WhereClause;

Line: 2142

        SELECT 1 INTO l_Dummy
        FROM msc_plans
        WHERE
            curr_plan_type IN (1, 2, 3) AND
            plan_id = PlanId;

Line: 2168

PROCEDURE InsertPlannedSupply(
        PlanId              IN         NUMBER,
        SrInstId            IN         NUMBER,
        PlannedSupplyData   IN         MscPlannedSupplyRec
) AS
l_TransactionId     NUMBER;

Line: 2176

    SELECT msc_supplies_s.nextval INTO l_TransactionId FROM DUAL;

Line: 2179

    INSERT INTO msc_supplies
        (
        plan_id, transaction_id, organization_id,
        sr_instance_id, inventory_item_id, new_schedule_date,
        disposition_status_type, order_type, new_order_quantity,
        quantity_in_process, firm_planned_type, firm_quantity,
        firm_date, implement_firm, new_dock_date,
        status, applied,
        last_update_date, last_updated_by, creation_date, created_by
        )
    VALUES
        (
        PlanId, l_TransactionId, PlannedSupplyData.OrganizationId,
        SrInstId, PlannedSupplyData.ItemId, PlannedSupplyData.FirmDate,
        1, 5, 0,
        0, 1, PlannedSupplyData.Quantity,
        PlannedSupplyData.FirmDate, 2, PlannedSupplyData.FirmDate,
        0, 2,
        sysdate, g_UserId, sysdate, g_UserId
        );

Line: 2202

END InsertPlannedSupply;

Line: 2255

    InsertPlannedSupply(PlanId, SrInstanceId, PlannedSupplyData);

Line: 2408

        InsertOutputLevels(l_ScenarioId, ItemOutputLevel, OrganizationOutputLevel, CustomerOutputLevel, DemandClassOutputLevel);

Line: 2412

            InsertOutputLevels(l_ScenarioId, ItemOutputLevel, OrganizationOutputLevel, CustomerOutputLevel, DemandClassOutputLevel);

Line: 2461

        INSERT INTO msd_demand_plans
            (
            demand_plan_id, organization_id, sr_instance_id, demand_plan_name, use_org_specific_bom_flag,
            last_update_date, last_updated_by, creation_date, created_by
            )
        VALUES
            (
            g_DummyDemandPlanId, -23453, -23453, g_DummyDemandPlanName, 'N',
            sysdate, g_UserId, sysdate, g_UserId
            ) ;

Line: 2768

        SELECT plan_completion_date INTO l_Date
        FROM msc_plans
        WHERE plan_id = l_PlanId;

Line: 2789

            DELETE FROM msc_safety_stocks
            WHERE
                plan_id = l_PlanId;

Line: 3037

        SELECT plan_completion_date INTO l_CompletionDate
        FROM msc_plans
        WHERE plan_id = l_PlanId;

Line: 3060

        DELETE FROM msc_supplies
        WHERE
            plan_id = l_PlanId AND
            order_type = 5 AND
            firm_planned_type = 1;