APPS.MSC_WS_SRP SQL Statements

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

Line: 61

       SELECT plans.compile_designator, plans.organization_id, plans.sr_instance_id,
              desig.inventory_atp_flag, desig.production
       INTO l_val_planname,l_val_orgid,l_val_instanceid,l_val_inventory_atp_flag,
            l_val_production
       FROM  msc_plans plans, msc_designators desig
       WHERE plans.curr_plan_type in (8)
       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   NVL(plans.copy_plan_id,-1) = -1
       AND   NVL(desig.copy_designator_id, -1) = -1
       AND   plans.plan_id = planId;

Line: 85

       SELECT calendar_date
       INTO l_val_anchordate
       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_val_orgid
        AND mtp.sr_instance_id = l_val_instanceid
        AND dates.calendar_date <= TRUNC(sysdate)
        AND dates.calendar_date = anchordate;

Line: 107

       SELECT lookup_code
       INTO   l_val_launchsnapshot
       FROM   mfg_lookups
       WHERE  lookup_type = 'MSC_LAUNCH_SNAPSHOT'
       AND    lookup_code in decode(lookup_code,1,1,2,2,3,
                       decode((select count(*) from msc_plan_schedules
                               where plan_id = planId
                               and rownum = 1
                               and designator_type = 7
                               and input_type = 1),0,1,3 ) )
       AND    lookup_code = decode(launchsnapshot,'FULL',1,'NO',2,'DP_ONLY',3,-1);

Line: 129

       SELECT lookup_code
       INTO   l_val_launchplanner
       FROM   MFG_LOOKUPS
       WHERE  lookup_type = 'SYS_YES_NO'
       AND    ((lookup_code = 1 AND l_val_launchsnapshot in (1,3,4)) OR
               (l_val_launchsnapshot = 2))
       AND    lookup_code = decode(launchplanner, 'Y', msc_ws_common.sys_yes, msc_ws_common.sys_no);

Line: 147

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

Line: 165

       SELECT lookup_code
       INTO   l_val_enable24x7atp
       FROM   MFG_LOOKUPS
       WHERE  LOOKUP_TYPE = 'MSC_24X7_PURGE'
       AND    (( LOOKUP_CODE IN (1,2,3) and NVL(l_val_inventory_atp_flag,2) = 1 )
               OR LOOKUP_CODE=2)
       AND    LOOKUP_CODE = decode(enable24x7atp,'YES_PURGE',1,'NO',2,'YES_NO_PURGE',3,-1);

Line: 192

       SELECT lookup_code
       INTO   l_val_releasereschedules
       FROM   MFG_LOOKUPS
       WHERE  lookup_type='SYS_YES_NO'
       AND    (NVL(l_val_production,2)=1  or lookup_code=2)
       AND    lookup_code = decode(releasereschedules,'Y',msc_ws_common.sys_yes, msc_ws_common.sys_no);

Line: 206

        SELECT lookup_code
        INTO   l_val_generateForecast
        FROM   MFG_LOOKUPS
        where lookup_type = 'SYS_YES_NO'
        AND ( (l_val_launchplanner = 1 and lookup_code in (1,2)  )  OR
                  (l_val_launchplanner = 2 and lookup_code in   (1)     ) )
        AND    lookup_code = decode(generateForecast, 'Y', msc_ws_common.sys_yes, msc_ws_common.sys_no);

Line: 222

       SELECT to_number(decode(snapstaticentities,   'Y',   msc_ws_common.sys_yes,   msc_ws_common.sys_no))
       INTO l_val_snapstaticentities
       FROM dual;

Line: 226

       SELECT to_number(decode(netchange,   'Y',   msc_ws_common.sys_yes,   msc_ws_common.sys_no))
       INTO l_val_netchange
       FROM dual;

Line: 368

           SELECT plans.compile_designator
         INTO l_val_planname
         FROM  msc_plans plans, msc_designators desig
         WHERE plans.curr_plan_type in (8)
         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   NVL(plans.copy_plan_id,-1) = -1
         AND   NVL(desig.copy_designator_id, -1) = -1
         AND   plans.plan_id = planId;

Line: 518

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

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

Line: 655

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

Line: 737

        SELECT
            desig.designator_type,
            decode(desig.designator_type,  6, desig.update_type, -1)
        INTO
            DesigType,
            FcstShipTo
        FROM
            msc_designators desig,
            fnd_lookups lu
        WHERE
            ( (desig.designator_type = 6 AND desig.forecast_set_id is null) OR
              (desig.designator_type IN (5,8)) ) AND
            trunc(nvl(desig.disable_date, trunc(sysdate) + 1)) > trunc(sysdate) AND
            ( desig.designator <> PlanName OR desig.designator_type = 1 ) AND
            desig.organization_id = OrgId AND
            desig.sr_instance_id = InsId AND
            desig.designator_id = SchId AND
            lu.lookup_code(+) = desig.update_type AND
            lu.lookup_type(+) = 'MSC_SHIP_TO'
        UNION
        SELECT
            7, -1
        FROM
            msd_dp_ascp_scenarios_v
        WHERE
            scenario_name <> PlanName AND
            (sr_instance_id = InsId or sr_instance_id = -23453) AND
            scenario_id = SchId AND
            global_scenario_flag = 'N' AND
            last_revision IS NOT NULL
        UNION
        SELECT
            desig.designator_type,
            decode(desig.designator_type,  6, desig.update_type, -1)
        FROM
            msc_designators desig,
            msc_plan_organizations_v mpo,
            fnd_lookups lu
        WHERE
            ( (desig.designator_type = 6 AND desig.forecast_set_id IS NULL) OR
              (desig.designator_type in (5,8)) ) 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 AND
            lu.lookup_code(+) = desig.update_type AND
            lu.lookup_type(+) = 'MSC_SHIP_TO'
        UNION
        SELECT
            desig.designator_type,
            decode(desig.designator_type,  6, desig.update_type, -1)
        FROM
            msc_designators desig,
            msc_item_sourcing mis,
            msc_plans mp,
            fnd_lookups lu
        WHERE
            ( (desig.designator_type = 6 AND desig.forecast_set_id IS NULL) OR
              (desig.designator_type IN (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 AND
            lu.lookup_code(+) = desig.update_type AND
            lu.lookup_type(+) = 'MSC_SHIP_TO';

Line: 927

FUNCTION UPDATE_PLAN_OPTIONS(
        PlanId              IN         NUMBER,
        ItemSimulationSetId IN         NUMBER,
        OverwriteSup        IN         NUMBER,
        OverwriteManualFcst IN         NUMBER
) RETURN VARCHAR2 AS
BEGIN
    BEGIN
        UPDATE msc_plans
        SET
            item_simulation_set_id = ItemSimulationSetId,
            curr_overwrite_option = OverwriteSup,
            manual_fcst_overwrite_option =OverwriteManualFcst
        WHERE
            plan_id = PlanId;

Line: 948

END UPDATE_PLAN_OPTIONS;

Line: 960

FUNCTION INSERT_G_MAN_FCST(
        PlanId              IN         NUMBER,
        UserId              IN         NUMBER,
        FcstRec             IN         MscIGlbRetFcstRec
) 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, -1, FcstRec.FcstId, FcstRec.InsId,
            FcstRec.input_type, sysdate, UserId,
            sysdate, UserId, FcstRec.designator_type
            ) ;

Line: 987

END INSERT_G_MAN_FCST;

Line: 1006

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

Line: 1020

        l_ReturnString:= MSC_WS_COMMON.INSERT_ALL_SCHEDULES(
                             PlanId, InsId, UserId,
                             g_IGlbDmdSchTbl, g_ILocDmdSchTbl, g_ILocSupSchTbl);

Line: 1035

                l_ReturnString:= INSERT_G_MAN_FCST(PlanId, UserId, GlbManFcsts(I));

Line: 1043

END INSERT_ALL_SCHEDULES;

Line: 1062

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

Line: 1077

        l_ReturnString := MSC_WS_COMMON.INSERT_OR_UPDATE_ALL_SCHS(
                             PlanId, InsId, UserId,
                             g_IGlbDmdSchTbl, g_ILocDmdSchTbl, g_ILocSupSchTbl);

Line: 1093

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

Line: 1104

                    l_ReturnString := INSERT_G_MAN_FCST(PlanId, UserId, GlbManFcsts(I));

Line: 1116

END INSERT_OR_UPDATE_ALL_SCHS;

Line: 1298

    l_String := UPDATE_PLAN_OPTIONS(PlanId, ItemSimulationSetId, l_OverwriteSup, l_OverwriteManualFcst);

Line: 1306

        l_String := INSERT_ALL_SCHEDULES(
                             PlanId, l_InsId, UserId,
                             g_IGlbDmdSchTbl, g_IGlbRetFcstTbl, g_ILocDmdSchTbl, g_ILocSupSchTbl);

Line: 1310

        l_String := INSERT_OR_UPDATE_ALL_SCHS(
                             PlanId, l_InsId, UserId,
                             g_IGlbDmdSchTbl, g_IGlbRetFcstTbl, g_ILocDmdSchTbl, g_ILocSupSchTbl);