APPS.MSC_WS_IO SQL Statements

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

Line: 53

     SELECT plans.compile_designator, plans.organization_id, plans.sr_instance_id,
            desig.inventory_atp_flag, desig.production
     INTO   l_plan_name, l_org_id, l_sr_instance_id, l_inventory_atp_flag, l_production
     FROM   msc_plans plans, msc_designators desig
     WHERE  plans.curr_plan_type = 4
     AND    plans.organization_id = desig.organization_id
     AND    plans.sr_instance_id = desig.sr_instance_id
     AND    plans.compile_designator = desig.designator
     AND    NVL(desig.disable_date, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE)
     AND    plans.organization_selection <> 1
     AND    plans.plan_id = planId;

Line: 78

    SELECT  'Y'
    INTO    l_result
    FROM    msc_calendar_dates dates,
            msc_trading_partners mtp
    WHERE   dates.calendar_code = mtp.calendar_code
    AND     dates.exception_set_id = mtp.calendar_exception_set_id
    AND     mtp.sr_instance_id = dates.sr_instance_id
    AND     mtp.sr_tp_id = l_org_id
    AND     mtp.sr_instance_id = l_sr_instance_id
    AND     dates.calendar_date <= TRUNC(SYSDATE)
    AND     dates.calendar_date = anchorDate;

Line: 100

       SELECT  decode(archiveCurrVersPlan, 'Y', msc_ws_common.sys_yes, msc_ws_common.sys_no)
       INTO   l_val_archivePlan
       FROM   dual;

Line: 287

        SELECT
            plans.organization_id,
            plans.sr_instance_id,
            plans.compile_designator
        INTO
            OrgId,
            InsId,
            PlanName
        FROM
            msc_plans       plans,
            msc_designators desig
        WHERE
            plans.organization_id = desig.organization_id AND
            plans.sr_instance_id = desig.sr_instance_id AND
            plans.compile_designator = desig.designator AND
            NVL(desig.disable_date, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE) AND
            plans.organization_selection <> 1 AND
            plans.curr_plan_type in (1,2,3,4,5,8,9) AND
            plans.plan_id <> -1 AND
            -- NVL(plans.copy_plan_id,-1) = -1 AND
            -- NVL(desig.copy_designator_id, -1) = -1 AND
            plans.plan_id = PlanId;

Line: 332

        SELECT
            1 INTO l_Dummy
        FROM
            msc_plans
        WHERE
            curr_plan_type = 4 AND
            plan_id = PlanId;

Line: 363

            SELECT
               1 INTO l_Dummy
            FROM
                msc_service_level_sets
            WHERE
                service_level_set_id = SetId;

Line: 397

        SELECT
            error_type INTO ErrorType
        FROM
            msd_dp_ascp_scenarios_v
        WHERE
            global_scenario_flag = 'Y' AND
            scenario_name <> PlanName AND
            scenario_id = SchId AND
            last_revision IS NOT NULL;

Line: 436

        SELECT level_id INTO l_scenario_lvl_geo
        FROM   msd_dp_scenario_output_levels
        WHERE  scenario_id = SchId AND level_id in (11,15,41,42,30);

Line: 447

        SELECT level_id INTO l_scenario_lvl_item
        FROM   msd_dp_scenario_output_levels
        WHERE  scenario_id = SchId AND level_id in (34,40);

Line: 647

SELECT
    sr_instance_id,
    input_schedule_id,
    demand_variability_type,
    probability
FROM
    msc_plan_schedules
WHERE
    plan_id = idPlan AND
    organization_id = -1;

Line: 664

    SchTbl.DELETE;

Line: 730

                                    l_SchTbl.delete(J);

Line: 734

                                    NULL; -- skip, this element is deleted.

Line: 749

                            NULL; -- skip, this element is deleted.

Line: 843

SELECT
    sr_instance_id,
    organization_id,
    input_schedule_id,
    demand_variability_type,
    probability
FROM
    msc_plan_schedules
WHERE
    plan_id = idPlan AND
    organization_id <> -1 AND
    scenario_set = idScenarioSet;

Line: 863

    SchTbl.DELETE;

Line: 905

l_Insert         NUMBER;

Line: 915

                l_Insert := MSC_UTIL.SYS_YES;

Line: 920

                                 l_Insert := MSC_UTIL.SYS_NO;

Line: 925

                IF l_Insert = MSC_UTIL.SYS_YES THEN
                    l_ScenarioTbl.extend;

Line: 959

                                            l_SchTbl.delete(K);

Line: 963

                                            NULL; -- skip, this element is deleted.

Line: 1008

        SELECT designator_type
        INTO   DesigType
        FROM   msc_designators
        WHERE
            ((designator_type = 6 AND forecast_set_id IS NULL) OR
             (designator_type in  (1,2,3,4,5,8)) ) AND
            trunc(nvl(disable_date, trunc(sysdate) + 1)) > trunc(sysdate) AND
            (designator <> PlanName OR designator_type = 1) AND
            organization_id = OrgId AND
            sr_instance_id = InsId AND
            designator_id = SchId
        UNION
        SELECT 7
        FROM   msd_dp_ascp_scenarios_v
        WHERE
            global_scenario_flag = 'N' AND
            scenario_name <> PlanName AND
            sr_instance_id = InsId AND
            (sr_instance_id = -23453 OR sr_instance_id = InsId) AND
            scenario_id = SchId
        UNION
        SELECT designator_type
        FROM
            msc_designators desig,
            msc_plan_organizations_v mpo
        WHERE
            ((desig.designator_type = 6 and desig.forecast_set_id IS NULL) OR
             (desig.designator_type IN (2,3,4,5,8) )) AND
            NVL(desig.disable_date, trunc(sysdate) + 1) > trunc(sysdate) AND
            mpo.organization_id  = desig.organization_id AND
            mpo.sr_instance_id  = desig.sr_instance_id AND
            mpo.compile_designator = desig.designator AND
            mpo.planned_organization = OrgId AND
            mpo.sr_instance_id = InsId AND
            desig.designator <> PlanName AND
            desig.designator_id = SchId
        UNION
        SELECT desig.designator_type
        FROM
            msc_designators desig,
            msc_item_sourcing mis,
            msc_plans mp
        WHERE
            ((desig.designator_type = 6 AND desig.forecast_set_id IS NULL) OR
             (desig.designator_type IN (2,3,4,5,8)) ) AND
            trunc(nvl(desig.disable_date, trunc(sysdate) + 1)) > trunc(sysdate) AND
            mis.plan_id = mp.plan_id AND
            mp.organization_id  = desig.organization_id AND
            mp.sr_instance_id  = desig.sr_instance_id AND
            mp.compile_designator = desig.designator AND
            mis.source_organization_id = OrgId AND
            mis.sr_instance_id2 = InsId AND
            desig.designator <> PlanName AND
            desig.designator_id = SchId;

Line: 1252

PROCEDURE UPDATE_PLAN_OPTIONS(
        PlanId              IN         NUMBER,
        ItemSimulationSetId IN         NUMBER,
        SvcLvlSetId         IN         NUMBER
) AS
BEGIN
    BEGIN
        UPDATE msc_plans
        SET
            item_simulation_set_id = ItemSimulationSetId,
            curr_service_level_set_id = SvcLvlSetId
        WHERE
            plan_id = PlanId;

Line: 1270

END UPDATE_PLAN_OPTIONS;

Line: 1285

FUNCTION INSERT_GLB_DMD_SCHEDULE(
        PlanId              IN         NUMBER,
        InsId               IN         NUMBER,
        UserId              IN         NUMBER,
        SchRec              IN         MscIGlbIODmdSchRec
) RETURN VARCHAR2 AS
BEGIN
    BEGIN
        INSERT INTO msc_plan_schedules
            (
            plan_id, organization_id, input_schedule_id, sr_instance_id,
            input_type, last_update_date, last_updated_by,
            creation_date, created_by, designator_type, ship_to,
            demand_variability_type, probability, mape_value
            )
        VALUES
            (
            PlanId, -1, SchRec.DmdSchId, InsId,
            SchRec.input_type, sysdate, UserId,
            sysdate, UserId, SchRec.designator_type, SchRec.ShipToConsumptionLvl,
            SchRec.DmdVariabilityType, SchRec.Probability, SchRec.MeanAbsPctError
            );

Line: 1315

END INSERT_GLB_DMD_SCHEDULE;

Line: 1331

FUNCTION INSERT_LOC_DMD_SCHEDULE(
        PlanId              IN         NUMBER,
        InsId               IN         NUMBER,
        UserId              IN         NUMBER,
        SchRec              IN         MscILocIODmdSchRec
) RETURN VARCHAR2 AS
BEGIN
    BEGIN
        INSERT INTO msc_plan_schedules
            (
            plan_id, organization_id, input_schedule_id, sr_instance_id,
            input_type, last_update_date, last_updated_by,
            creation_date, created_by, designator_type, ship_to,
            demand_variability_type, probability, mape_value,
            scenario_set, include_target_demands -- include_target_demands is hard coded to 2
            )
        VALUES
            (
            PlanId, SchRec.OrgId, SchRec.DmdSchId, InsId,
            SchRec.input_type, sysdate, UserId,
            sysdate, UserId, SchRec.designator_type, SchRec.ShipToConsumptionLvl,
            SchRec.DmdVariabilityType, SchRec.Probability, SchRec.MeanAbsPctError,
            SchRec.ScenarioSet, 2
            );

Line: 1363

END INSERT_LOC_DMD_SCHEDULE;

Line: 1376

FUNCTION INSERT_ALL_SCHEDULES(
        PlanId              IN         NUMBER,
        InsId               IN         NUMBER,
        UserId              IN         NUMBER
) RETURN VARCHAR2 AS
l_ReturnString    VARCHAR2(100);

Line: 1387

                l_ReturnString:= INSERT_GLB_DMD_SCHEDULE(PlanId, InsId, UserId, g_IGlbDmdSchTbl(I));

Line: 1398

                l_ReturnString:= INSERT_LOC_DMD_SCHEDULE(PlanId, InsId, UserId, g_ILocDmdSchTbl(I));

Line: 1406

END INSERT_ALL_SCHEDULES;

Line: 1419

PROCEDURE UPDATE_GLB_DMD_SCHEDULE(
        PlanId              IN         NUMBER,
        InsId               IN         NUMBER,
        UserId              IN         NUMBER,
        SchRec              IN         MscIGlbIODmdSchRec
) AS
BEGIN
    BEGIN
        UPDATE msc_plan_schedules
        SET
            ship_to                 = SchRec.ShipToConsumptionLvl,
            demand_variability_type = SchRec.DmdVariabilityType,
            probability             = SchRec.Probability,
            mape_value              = SchRec.MeanAbsPctError
        WHERE
            plan_id = PlanId AND
            organization_id = -1 AND
            sr_instance_id = InsId AND
            input_schedule_id = SchRec.DmdSchId;

Line: 1443

END UPDATE_GLB_DMD_SCHEDULE;

Line: 1456

PROCEDURE UPDATE_LOC_DMD_SCHEDULE(
        PlanId              IN         NUMBER,
        InsId               IN         NUMBER,
        UserId              IN         NUMBER,
        SchRec              IN         MscILocIODmdSchRec
) AS
BEGIN
    BEGIN
        UPDATE msc_plan_schedules
        SET
            ship_to                 = SchRec.ShipToConsumptionLvl,
            demand_variability_type = SchRec.DmdVariabilityType,
            probability             = SchRec.Probability,
            mape_value              = SchRec.MeanAbsPctError,
            scenario_set            = SchRec.ScenarioSet
        WHERE
            plan_id = PlanId AND
            organization_id = SchRec.OrgId AND
            sr_instance_id = InsId AND
            input_schedule_id = SchRec.DmdSchId;

Line: 1481

END UPDATE_LOC_DMD_SCHEDULE;

Line: 1494

FUNCTION INSERT_OR_UPDATE_ALL_SCHS(
        PlanId              IN         NUMBER,
        InsId               IN         NUMBER,
        UserId              IN         NUMBER
) RETURN VARCHAR2 AS
l_ReturnString    VARCHAR2(100);

Line: 1507

                    Select count(*) INTO l_Dummy
                    FROM   msc_plan_schedules
                    WHERE
                        plan_id = PlanId AND
                        organization_id = -1 AND
                        sr_instance_id = InsId AND
                        input_schedule_id  = g_IGlbDmdSchTbl(I).DmdSchId;

Line: 1519

                    l_ReturnString := INSERT_GLB_DMD_SCHEDULE(PlanId, InsId, UserId, g_IGlbDmdSchTbl(I));

Line: 1521

                    UPDATE_GLB_DMD_SCHEDULE(PlanId, InsId, UserId, g_IGlbDmdSchTbl(I));

Line: 1531

                    Select count(*) INTO l_Dummy
                    FROM   msc_plan_schedules
                    WHERE
                        plan_id = PlanId AND
                        organization_id = g_ILocDmdSchTbl(I).OrgId AND
                        sr_instance_id = InsId AND
                        input_schedule_id  = g_ILocDmdSchTbl(I).DmdSchId;

Line: 1543

                    l_ReturnString := INSERT_LOC_DMD_SCHEDULE(PlanId, InsId, UserId, g_ILocDmdSchTbl(I));

Line: 1548

                    UPDATE_LOC_DMD_SCHEDULE(PlanId, InsId, UserId, g_ILocDmdSchTbl(I));

Line: 1558

END INSERT_OR_UPDATE_ALL_SCHS;

Line: 1707

    UPDATE_PLAN_OPTIONS(PlanId, ItemSimulationSetId, ServiceLvlSetId);

Line: 1711

        l_String := INSERT_ALL_SCHEDULES(PlanId, l_InsId, UserId);

Line: 1713

        l_String := INSERT_OR_UPDATE_ALL_SCHS(PlanId, l_InsId, UserId);