APPS.MSC_WS_COMMON SQL Statements

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

Line: 16

   SELECT USER_ID INTO V_USER_ID
   FROM FND_USER
   WHERE USER_ID = USERID;

Line: 27

       SELECT RESPONSIBILITY_ID  INTO V_RESPID
       FROM FND_USER_RESP_GROUPS
       WHERE USER_ID = V_USER_ID AND RESPONSIBILITY_ID = RESPID AND
      (sysdate BETWEEN nvl(start_date,sysdate) AND nvl(end_date,sysdate));

Line: 39

       SELECT APPLICATION_ID  INTO  V_APPID
       FROM FND_RESPONSIBILITY
       WHERE  RESPONSIBILITY_ID = V_RESPID;

Line: 93

        SELECT user_id INTO UserId
        FROM fnd_user
        WHERE user_name = upper(UserName);

Line: 107

        SELECT language_code INTO l_LanguageCode
        FROM fnd_languages
        WHERE nls_language = Language;

Line: 122

        SELECT application_id INTO l_application_id
        FROM fnd_application
        WHERE application_short_name = RespAppName;

Line: 137

        SELECT resp_tl.responsibility_id INTO ResponsibilityId
        FROM
            fnd_responsibility_tl resp_tl
        WHERE
            resp_tl.application_id       = l_application_id          AND
            resp_tl.language             = l_LanguageCode            AND
            resp_tl.responsibility_name  = RespName;

Line: 156

        SELECT security_group_id INTO SecurityGroupId
        FROM fnd_security_groups
        WHERE security_group_key = SecurityGroupName;

Line: 178

         SELECT COMPILE_DESIGNATOR INTO l_PlanName
         FROM MSC_PLANS
         WHERE PLAN_ID = PlanId;

Line: 206

   select curr_plan_type
   from msc_plans
   where plan_id = v_plan_id;

Line: 248

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

        SELECT
            sr_instance_id  INTO InsId
        FROM
            msc_plan_organizations
        WHERE
            plan_id = PlanId AND
            organization_id = OrgId;

Line: 330

            SELECT
               1 INTO l_Dummy
            FROM
                msc_item_simulation_sets
            WHERE
                simulation_set_id = SetId;

Line: 358

        DELETE FROM msc_plan_schedules
        WHERE
            plan_id = PlanId;

Line: 379

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

Line: 396

END UPDATE_PLAN_OPTIONS;

Line: 432

FUNCTION INSERT_G_DMD_SCH(
        PlanId              IN         NUMBER,
        InsId               IN         NUMBER,
        UserId              IN         NUMBER,
        SchRec              IN         MscIGlbDmdSchRec
) 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
            )
        VALUES
            (
            PlanId, -1, SchRec.DmdSchId, InsId,
            SchRec.input_type, sysdate, UserId,
            sysdate, UserId, SchRec.designator_type, SchRec.ShipToConsumptionLvl
            ) ;

Line: 459

END INSERT_G_DMD_SCH;

Line: 474

FUNCTION INSERT_L_DMD_SCH(
        PlanId              IN         NUMBER,
        InsId               IN         NUMBER,
        UserId              IN         NUMBER,
        SchRec              IN         MscILocDmdSchRec
) 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,
            include_target_demands,
            ship_to,
            interplant_demand_flag
            )
        VALUES
            (
            PlanId, SchRec.OrgId,
            SchRec.DmdSchId, InsId,
            SchRec.input_type, sysdate, UserId,
            sysdate, UserId, SchRec.designator_type,
            SchRec.IncludeTargetDmd,
            SchRec.ShipToConsumptionLvl,
            SchRec.InterPlantFlg
            ) ;

Line: 509

END INSERT_L_DMD_SCH;

Line: 524

FUNCTION INSERT_L_SUP_SCH(
        PlanId              IN         NUMBER,
        InsId               IN         NUMBER,
        UserId              IN         NUMBER,
        SchRec              IN         MscILocSupSchRec
) 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
            )
        VALUES
            (
            PlanId, SchRec.OrgId,
            SchRec.SupSchId, InsId,
            SchRec.input_type, sysdate, UserId,
            sysdate, UserId, SchRec.designator_type
            ) ;

Line: 553

END INSERT_L_SUP_SCH;

Line: 572

FUNCTION INSERT_ALL_SCHEDULES(
        PlanId              IN         NUMBER,
        InsId               IN         NUMBER,
        UserId              IN         NUMBER,
        GlbDmdSchs          IN         MscIGlbDmdSchTbl,
        LocDmdSchs          IN         MscILocDmdSchTbl,
        LocSupSchs          IN         MscILocSupSchTbl
) RETURN VARCHAR2 AS
l_String               VARCHAR2(100);

Line: 586

                l_String := INSERT_G_DMD_SCH(PlanId, InsId, UserId, GlbDmdSchs(I));

Line: 597

                l_String := INSERT_L_DMD_SCH(PlanId, InsId, UserId, LocDmdSchs(I));

Line: 608

                l_String := INSERT_L_SUP_SCH(PlanId, InsId, UserId, LocSupSchs(I));

Line: 616

END INSERT_ALL_SCHEDULES;

Line: 629

PROCEDURE UPDATE_G_DMD_SCH(
        PlanId              IN         NUMBER,
        InsId               IN         NUMBER,
        UserId              IN         NUMBER,
        SchRec              IN         MscIGlbDmdSchRec
) AS
BEGIN
    BEGIN
        UPDATE msc_plan_schedules
        SET
            ship_to = SchRec.ShipToConsumptionLvl
        WHERE
            plan_id = PlanId AND
            organization_id = -1 AND
            sr_instance_id = InsId AND
            input_schedule_id = SchRec.DmdSchId;

Line: 649

END UPDATE_G_DMD_SCH;

Line: 662

PROCEDURE UPDATE_L_DMD_SCH(
        PlanId              IN         NUMBER,
        InsId               IN         NUMBER,
        UserId              IN         NUMBER,
        SchRec              IN         MscILocDmdSchRec
) AS
BEGIN
    BEGIN
        UPDATE msc_plan_schedules
        SET
            ship_to = SchRec.ShipToConsumptionLvl,
            include_target_demands = SchRec.IncludeTargetDmd,
            interplant_demand_flag = SchRec.InterPlantFlg
        WHERE
            plan_id = PlanId AND
            organization_id = SchRec.OrgId AND
            sr_instance_id = InsId AND
            input_schedule_id = SchRec.DmdSchId;

Line: 684

END UPDATE_L_DMD_SCH;

Line: 703

FUNCTION INSERT_OR_UPDATE_ALL_SCHS(
        PlanId              IN         NUMBER,
        InsId               IN         NUMBER,
        UserId              IN         NUMBER,
        GlbDmdSchs          IN         MscIGlbDmdSchTbl,
        LocDmdSchs          IN         MscILocDmdSchTbl,
        LocSupSchs          IN         MscILocSupSchTbl
) RETURN VARCHAR2 AS
l_Dummy           NUMBER;

Line: 719

                    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  = GlbDmdSchs(I).DmdSchId;

Line: 730

                    l_String := INSERT_G_DMD_SCH(PlanId, InsId, UserId, GlbDmdSchs(I));

Line: 735

                    UPDATE_G_DMD_SCH(PlanId, InsId, UserId, GlbDmdSchs(I));

Line: 745

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

Line: 756

                    l_String := INSERT_L_DMD_SCH(PlanId, InsId, UserId, LocDmdSchs(I));

Line: 761

                    UPDATE_L_DMD_SCH(PlanId, InsId, UserId, LocDmdSchs(I));

Line: 771

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

Line: 782

                    l_String := INSERT_L_SUP_SCH(PlanId, InsId, UserId, LocSupSchs(I));

Line: 794

END INSERT_OR_UPDATE_ALL_SCHS;

Line: 815

        SELECT
            1 INTO l_Dummy
        FROM
            msd_dp_ascp_scenarios_v
        WHERE
            global_scenario_flag = 'Y' AND
            last_revision IS NOT NULL AND -- ASCP doesn't has this condition
            scenario_name <> PlanName AND
            scenario_id = SchId;

Line: 856

        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,40);

Line: 868

        SELECT level_id INTO l_scenario_lvl_item
        FROM msd_dp_scenario_output_levels
        WHERE
            scenario_id = SchId AND
            level_id = 34;

Line: 987

        SELECT
            designator_type INTO DesigType
        FROM
            msc_designators
        WHERE
            trunc(nvl(disable_date, trunc(sysdate) + 1)) > trunc(sysdate) AND
            designator <> PlanName AND
            organization_id = OrgId AND
            sr_instance_id = InsId AND
            designator_id = SchId AND
            designator_type not in (1,6)
        UNION
        SELECT
            desig.designator_type
        FROM
            msc_designators desig,
            msc_plan_organizations_v mpo
        WHERE
            desig.designator_type not in (1,6) AND
            trunc(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;