APPS.IEB_SERVICEPLAN_PVT SQL Statements

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

Line: 39

    fnd_msg_pub.delete_msg();

Line: 49

    select IEB_SVC_PLAN_S1.nextval into l_svc_plan_id from dual;

Line: 51

    EXECUTE immediate 'INSERT INTO  IEB_SERVICE_PLANS '||
    '(   SVCPLN_ID, ' ||
    '    CREATED_BY, ' ||
    '    CREATION_DATE, ' ||
    '    LAST_UPDATED_BY,' ||
    '    LAST_UPDATE_DATE, ' ||
    '    LAST_UPDATE_LOGIN, ' ||
    '    SERVICE_PLAN_NAME, ' ||
    '    DIRECTION, ' ||
    '    TREATMENT, ' ||
    '    DESCRIPTION, ' ||
    '    MEDIA_TYPE_ID, ' ||
    '  OBJECT_VERSION_NUMBER,' ||
    '  SECURITY_GROUP_ID ' ||
    ' )VALUES ' ||
    '(:1 ,' ||
    ' :2, '||
    ' :3, '||
    ' :4, '||
    ' :5, '||
    ' :6, '||
    ' :7, '||
    ' :8, '||
    ' :9, '||
    ' :10, '||
    ' :11, '||
    ' :12, '||
    ' :13 '||
    ') '
     USING  l_svc_plan_id,
       FND_GLOBAL.USER_ID,
        SYSDATE,
        FND_GLOBAL.USER_ID,
        SYSDATE,
        FND_GLOBAL.LOGIN_ID,
        LTRIM(RTRIM(p_name)),
        p_direction,
        l_temp_str,
        LTRIM(RTRIM(p_desc)),
        p_media_type_id,
        0,
        0;

Line: 96

    insert into IEB_SERVICE_PLANS_TL (
        CREATION_DATE,
        LAST_UPDATED_BY,
        LAST_UPDATE_DATE,
        CREATED_BY,
        SERVICE_PLAN_ID,
        OBJECT_VERSION_NUMBER,
        DESCRIPTION,
        LAST_UPDATE_LOGIN,
        PLAN_NAME,
        LANGUAGE,
        SOURCE_LANG
    ) select
        SYSDATE,
        FND_GLOBAL.USER_ID,
        SYSDATE,
        FND_GLOBAL.USER_ID,
        l_svc_plan_id,
        0,
        LTRIM(RTRIM(p_desc)),
        FND_GLOBAL.LOGIN_ID,
        LTRIM(RTRIM(p_name)),
        L.LANGUAGE_CODE,
        userenv('LANG')
        FROM FND_LANGUAGES L
        where L.INSTALLED_FLAG in ('I', 'B')
        and not exists
        (select NULL
        from IEB_SERVICE_PLANS_TL T
        where T.SERVICE_PLAN_ID = l_svc_plan_id
        and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 214

    fnd_msg_pub.delete_msg();

Line: 225

                select b.HOURLY_QUOTA, b.MIN_AGENTS
             into l_quota, l_minagent from IEB_SERVICE_LEVELS_B b, IEB_SERVICE_LEVELS_tl tl
             where b.SERVICE_LEVEL_ID = tl.SERVICE_LEVEL_ID
             and tl.LANGUAGE = l_language
             and b.SERVICE_LEVEL_ID = rec_obj.slevel_id;

Line: 231

        select IEB_SVC_COV_S2.nextval into l_osvccov_id from dual;

Line: 242

        EXECUTE immediate 'INSERT into IEB_OUTB_SVC_COVERAGES '||
        ' ( OSVCCOV_ID, '||
        ' CREATED_BY,  '||
        ' CREATION_DATE, '||
        ' LAST_UPDATED_BY, '||
        ' LAST_UPDATE_DATE, '||
        ' LAST_UPDATE_LOGIN,'||
        ' SCHEDULE_TYPE, '||
        ' REGULAR_SCHD_DAY, '||
        ' SPEC_SCHD_DATE, '||
        ' BEGIN_TIME_HHMM, '||
        ' END_TIME_HHMM, '||
        ' MIN_AGENT, '||
        ' QUOTA, '||
        ' SVCPLN_SVCPLN_ID,'||
        ' OBJECT_VERSION_NUMBER, '||
        ' SECURITY_GROUP_ID, '||
        ' SERVICE_LEVEL_ID'||
        ' ) values ( '||
        ' :1, '||
        ' :2, '||
        ' :3, '||
        ' :4, '||
        ' :5, '||
        ' :6, '||
        ' :7, '||
        ' :8, '||
        ' :9, '||
        ' :10, '||
        ' :11, '||
        ' :12, '||
        ' :13, '||
        ' :14, '||
        ' :15, '||
        ' :16, '||
        ' :17 '||
        ' ) '
        USING   l_osvccov_id,
                FND_GLOBAL.USER_ID,
                SYSDATE,
                FND_GLOBAL.USER_ID,
                SYSDATE,
                FND_GLOBAL.LOGIN_ID,
                rec_obj.schedule_type,
                rec_obj.regular_schd_day,
                rec_obj.spec_schd_date,
                rec_obj.start_time,
                rec_obj.end_time,
                l_minagent,
                l_quota,
                rec_obj.splan_id,
                0,
                0,
                rec_obj.slevel_id;

Line: 299

    select  b.MIN_AGENTS,b.GOAL_PERCENT, b.GOAL_TIME,
            b.MAX_WAIT_TIME,b.REROUTE_TIME, b.REROUTE_WARNING_TIME
             into l_minagent, l_percentage,
            l_time_threshold, l_max_wait_time, l_reroute_time,
            l_reroute_war_time
    from IEB_SERVICE_LEVELS_B b, IEB_SERVICE_LEVELS_tl tl
    where b.SERVICE_LEVEL_ID = tl.SERVICE_LEVEL_ID
        and tl.LANGUAGE = l_language
        and b.SERVICE_LEVEL_ID = rec_obj.slevel_id;

Line: 310

    select IEB_SVC_COV_S1.nextval into l_isvccov_id from dual;

Line: 333

    EXECUTE immediate ' INSERT INTO IEB_INB_SVC_COVERAGES '||
    ' ( ISVCCOV_ID, '||
    '    CREATED_BY, '||
    '    CREATION_DATE, '||
    '    LAST_UPDATED_BY, '||
    '    LAST_UPDATE_DATE, '||
    '    LAST_UPDATE_LOGIN, '||
    '    SCHEDULE_TYPE, '||
    '    REGULAR_SCHD_DAY, '||
    '    SPEC_SCHD_DATE, '||
    '    BEGIN_TIME_HHMM, '||
    '    END_TIME_HHMM, '||
    '    MIN_AGENT, '||
    '    PERCENTAGE, '||
    '    TIME_THRESHOLD, '||
    '    MAX_WAIT_TIME, '||
    '    REROUTE_TIME, '||
    '    REROUTE_WARNING_TIME, '||
    '    SVCPLN_SVCPLN_ID, '||
    '    OBJECT_VERSION_NUMBER, '||
    '    SECURITY_GROUP_ID, '||
    '    SERVICE_LEVEL_ID '||
    '    ) values ( '||
    ' :1, '||
    ' :2, '||
    ' :3, '||
    ' :4, '||
    ' :5, '||
    ' :6, '||
    ' :7, '||
    ' :8, '||
    ' :9, '||
    ' :10, '||
    ' :11, '||
    ' :12, '||
    ' :13, '||
    ' :14, '||
    ' :15, '||
    ' :16, '||
    ' :17, '||
    ' :18, '||
    ' :19, '||
    ' :20, '||
    ' :21 '||
    ' ) '
    USING   l_isvccov_id,
            FND_GLOBAL.USER_ID,
            SYSDATE,
            FND_GLOBAL.USER_ID,
            SYSDATE,
            FND_GLOBAL.LOGIN_ID,
            rec_obj.schedule_type,
            rec_obj.regular_schd_day,
            rec_obj.spec_schd_date,
            rec_obj.start_time,
            rec_obj.end_time,
            l_minagent,
            l_percentage,
            l_time_threshold,
            l_max_wait_time,
            l_reroute_time,
            l_reroute_war_time,
            rec_obj.splan_id,
            0,
            0,
            rec_obj.slevel_id;

Line: 437

PROCEDURE Update_IOCoverages (   x_return_status OUT NOCOPY VARCHAR2,
                                x_msg_count OUT NOCOPY NUMBER,
                                x_msg_data OUT NOCOPY VARCHAR2,
                                rec_obj IN SYSTEM.IEB_SERVICE_COVERAGES_OBJ
                                )as


    l_language             VARCHAR2(4);

Line: 483

    fnd_msg_pub.delete_msg();

Line: 493

        select b.HOURLY_QUOTA, b.MIN_AGENTS
        into l_quota, l_minagent from IEB_SERVICE_LEVELS_B b, IEB_SERVICE_LEVELS_tl tl
        where b.SERVICE_LEVEL_ID = tl.SERVICE_LEVEL_ID
        and tl.LANGUAGE = l_language
        and b.SERVICE_LEVEL_ID = rec_obj.slevel_id;

Line: 504

            update IEB_OUTB_SVC_COVERAGES
            set BEGIN_TIME_HHMM = rec_obj.start_time,
                END_TIME_HHMM = rec_obj.end_time,
                MIN_AGENT = l_minagent,
                QUOTA = l_quota,
                SERVICE_LEVEL_ID = rec_obj.slevel_id,
                LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
                LAST_UPDATE_DATE = SYSDATE,
                LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
            where SVCPLN_SVCPLN_ID = rec_obj.splan_id
                and SCHEDULE_TYPE = rec_obj.schedule_type
                and REGULAR_SCHD_DAY = rec_obj.regular_schd_day;

Line: 519

            update IEB_OUTB_SVC_COVERAGES
            set BEGIN_TIME_HHMM = rec_obj.start_time,
                END_TIME_HHMM = rec_obj.end_time,
                MIN_AGENT = l_minagent,
                QUOTA = l_quota,
                SERVICE_LEVEL_ID = rec_obj.slevel_id,
                LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
                LAST_UPDATE_DATE = SYSDATE,
                LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
            where SVCPLN_SVCPLN_ID = rec_obj.splan_id
                and SCHEDULE_TYPE = rec_obj.schedule_type
                and SPEC_SCHD_DATE = rec_obj.spec_schd_date;

Line: 535

        select  b.MIN_AGENTS,b.GOAL_PERCENT, b.GOAL_TIME,
                b.MAX_WAIT_TIME,b.REROUTE_TIME, b.REROUTE_WARNING_TIME
                 into l_minagent, l_percentage,
                l_time_threshold, l_max_wait_time, l_reroute_time,
                l_reroute_war_time
        from IEB_SERVICE_LEVELS_B b, IEB_SERVICE_LEVELS_tl tl
        where b.SERVICE_LEVEL_ID = tl.SERVICE_LEVEL_ID
            and tl.LANGUAGE = l_language
            and b.SERVICE_LEVEL_ID = rec_obj.slevel_id;

Line: 554

            update IEB_INB_SVC_COVERAGES
            set BEGIN_TIME_HHMM = rec_obj.start_time,
                END_TIME_HHMM = rec_obj.end_time,
                MIN_AGENT = l_minagent,
                PERCENTAGE = l_percentage,
                TIME_THRESHOLD = l_time_threshold,
                MAX_WAIT_TIME = l_max_wait_time,
                REROUTE_TIME = l_reroute_time,
                REROUTE_WARNING_TIME = l_reroute_war_time,
                SERVICE_LEVEL_ID = rec_obj.slevel_id,
                LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
                LAST_UPDATE_DATE = SYSDATE,
                LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
            where SVCPLN_SVCPLN_ID = rec_obj.splan_id
                and SCHEDULE_TYPE = rec_obj.schedule_type
                and REGULAR_SCHD_DAY = rec_obj.regular_schd_day;

Line: 573

            update IEB_INB_SVC_COVERAGES
            set BEGIN_TIME_HHMM = rec_obj.start_time,
                END_TIME_HHMM = rec_obj.end_time,
                MIN_AGENT = l_minagent,
                PERCENTAGE = l_percentage,
                TIME_THRESHOLD = l_time_threshold,
                MAX_WAIT_TIME = l_max_wait_time,
                REROUTE_TIME = l_reroute_time,
                REROUTE_WARNING_TIME = l_reroute_war_time,
                SERVICE_LEVEL_ID = rec_obj.slevel_id,
                LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
                LAST_UPDATE_DATE = SYSDATE,
                LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
            where SVCPLN_SVCPLN_ID = rec_obj.splan_id
                and SCHEDULE_TYPE = rec_obj.schedule_type
                and SPEC_SCHD_DATE = rec_obj.spec_schd_date;

Line: 611

END Update_IOCoverages;

Line: 649

    fnd_msg_pub.delete_msg();

Line: 657

    select IEB_SVC_CAT_RULES_S1.nextval into l_wbscrule_id from dual;

Line: 659

    EXECUTE immediate 'INSERT into IEB_WB_SVC_CAT_RULES '||
        '(WBSCRULE_ID, '||
        ' RULE_TYPE, '||
        ' CREATED_BY, '||
        ' CREATION_DATE, '||
        ' LAST_UPDATED_BY, '||
        ' LAST_UPDATE_DATE, '||
        ' LAST_UPDATE_LOGIN, '||
        ' CLASSIFICATION, '||
        ' SKILL_INCLUDED_Y_N, '||
        ' DESCRIPTION, '||
        ' WBSC_WBSC_ID, '||
        ' OBJECT_VERSION_NUMBER, '||
        ' SECURITY_GROUP_ID '||
        ' ) VALUES '||
        ' ( :1,' ||
        ' :2, '||
        ' :3, '||
        ' :4, '||
        ' :5, '||
        ' :6, '||
        ' :7, '||
        ' :8, '||
        ' :9, '||
        ' :10, '||
        ' :11, '||
        ' :12, '||
        ' :13 '||
        ' ) '
        USING   l_wbscrule_id,
                'C',
                FND_GLOBAL.USER_ID,
                SYSDATE,
                FND_GLOBAL.USER_ID,
                SYSDATE,
                FND_GLOBAL.LOGIN_ID,
                p_classfn_name,
                'N',
                p_classfn_name,
                p_svc_cat_id,
                0,
                0;

Line: 769

          select s.WBSVR_ID, s.WB_SERVER_NAME, s.IEO_SERVER_ID  from IEB_WB_SERVERS s;

Line: 774

    fnd_msg_pub.delete_msg();

Line: 782

    select  media_type into l_media_type
     from IEB_SVC_CAT_TEMPS_B where media_type_id = p_media_type_id;

Line: 787

        select parent_id into l_parent_id
        from IEB_WB_SVC_CATS where media_type_id= p_media_type_id
        and wbsvr_wbsvr_id= c_rec.WBSVR_ID
        and default_flag= 'Y';

Line: 792

        SELECT DBMS_UTILITY.GET_TIME INTO l_time_stamp FROM dual;

Line: 797

        select IEB_SVC_CATS_S1.nextval into l_wbsc_id from dual;

Line: 799

        EXECUTE immediate 'INSERT into IEB_WB_SVC_CATS '||
        ' (WBSC_ID,  '||
        ' CREATED_BY,  '||
        ' CREATION_DATE, '||
        ' LAST_UPDATED_BY,  '||
        ' LAST_UPDATE_DATE, '||
        ' LAST_UPDATE_LOGIN, '||
        ' SERVICE_CATEGORY_NAME, '||
        ' CAMPAIGN_SERVER_NAME, '||
        ' ACTIVE_Y_N, '||
        ' MEDIA_TYPE, '||
        ' PRIORITY, '||
        ' DEPTH, '||
        ' WBSVR_WBSVR_ID, '||
        ' PARENT_ID, '||
        ' SVCPLN_SVCPLN_ID, '||
        ' OBJECT_VERSION_NUMBER, '||
        ' SECURITY_GROUP_ID, '||
        ' MEDIA_TYPE_ID'||
        ' ) values ('||
        ' :1, '||
        ' :2, '||
        ' :3, '||
        ' :4, '||
        ' :5, '||
        ' :6, '||
        ' :7, '||
        ' :8, '||
        ' :9, '||
        ' :10, '||
        ' :11, '||
        ' :12, '||
        ' :13, '||
        ' :14, '||
        ' :15, '||
        ' :16, '||
        ' :17, '||
        ' :18 '||
        ' ) '
        USING   l_wbsc_id,
                FND_GLOBAL.USER_ID,
                SYSDATE,
                FND_GLOBAL.USER_ID,
                SYSDATE,
                FND_GLOBAL.LOGIN_ID,
                l_svr_cat_name,
                p_name,
                'Y',
                l_media_type,
                0,
                3,
                c_rec.WBSVR_ID,
                l_parent_id,
                p_svc_plan_id,
                0,
                0,
                p_media_type_id;

Line: 932

    fnd_msg_pub.delete_msg();

Line: 941

        select parent_id into l_parent_id
        from IEB_WB_SVC_CATS where media_type_id= p_media_type_id
        and wbsvr_wbsvr_id= p_server_id
        and default_flag= 'Y';

Line: 946

        select  media_type into l_media_type
        from IEB_SVC_CAT_TEMPS_B where media_type_id = p_media_type_id;

Line: 950

        SELECT DBMS_UTILITY.GET_TIME INTO l_time_stamp FROM dual;

Line: 954

        select IEB_SVC_CATS_S1.nextval into l_wbsc_id from dual;

Line: 956

        EXECUTE immediate 'INSERT into IEB_WB_SVC_CATS '||
        ' (WBSC_ID,  '||
        ' CREATED_BY,  '||
        ' CREATION_DATE, '||
        ' LAST_UPDATED_BY,  '||
        ' LAST_UPDATE_DATE, '||
        ' LAST_UPDATE_LOGIN, '||
        ' SERVICE_CATEGORY_NAME, '||
        ' ACTIVE_Y_N, '||
        ' MEDIA_TYPE, '||
        ' PRIORITY, '||
        ' DEPTH, '||
        ' WBSVR_WBSVR_ID, '||
        ' PARENT_ID, '||
        ' SVCPLN_SVCPLN_ID, '||
        ' OBJECT_VERSION_NUMBER, '||
        ' SECURITY_GROUP_ID, '||
        ' MEDIA_TYPE_ID'||
        ' ) values ('||
        ' :1, '||
        ' :2, '||
        ' :3, '||
        ' :4, '||
        ' :5, '||
        ' :6, '||
        ' :7, '||
        ' :8, '||
        ' :9, '||
        ' :10, '||
        ' :11, '||
        ' :12, '||
        ' :13, '||
        ' :14, '||
        ' :15, '||
        ' :16, '||
        ' :17 '||
        ' ) '
        USING   l_wbsc_id,
                FND_GLOBAL.USER_ID,
                SYSDATE,
                FND_GLOBAL.USER_ID,
                SYSDATE,
                FND_GLOBAL.LOGIN_ID,
                l_svr_cat_name,
                'Y',
                l_media_type,
                0,
                3,
                p_server_id,
                l_parent_id,
                p_svc_plan_id,
                0,
                0,
                p_media_type_id;

Line: 1077

    fnd_msg_pub.delete_msg();

Line: 1096

    EXECUTE immediate 'INSERT into IEB_REGIONAL_PLANS '||
    '(SERVICE_PLAN_ID, '||
    ' CREATED_BY, '||
    ' CREATION_DATE, '||
    ' LAST_UPDATED_BY, '||
    ' LAST_UPDATE_DATE, '||
    ' LAST_UPDATE_LOGIN, '||
    ' BASE_PLAN_ID, '||
    ' OBJECT_VERSION_NUMBER, '||
    ' SECURITY_GROUP_ID '||
    ' ) VALUES '||
    ' (:1, '||
    ' :2, '||
    ' :3, '||
    ' :4, '||
    ' :5, '||
    ' :6, '||
    ' :7, '||
    ' :8, '||
    ' :9'||
    ') '
    USING l_svc_plan_id,
        FND_GLOBAL.USER_ID,
        SYSDATE,
        FND_GLOBAL.USER_ID,
        SYSDATE,
        FND_GLOBAL.LOGIN_ID,
        p_base_plan_id,
        0,
        0;

Line: 1189

    fnd_msg_pub.delete_msg();

Line: 1198

    select IEB_GROUP_PLAN_MAPS_S1.nextval into l_map_id from dual;

Line: 1199

    EXECUTE immediate 'INSERT INTO IEB_GROUP_PLAN_MAPS '||
    '(MAP_ID, '||
    ' SERVICE_PLAN_ID, '||
    ' CREATED_BY, '||
    ' CREATION_DATE, '||
    ' LAST_UPDATED_BY, '||
    ' LAST_UPDATE_DATE, '||
    ' LAST_UPDATE_LOGIN, '||
    ' SERVER_GROUP_ID, '||
    ' OBJECT_VERSION_NUMBER, '||
    ' SECURITY_GROUP_ID '||
    ' ) VALUES '||
    ' ( :1, '||
    ' :2, '||
    ' :3, '||
    ' :4, '||
    ' :5, '||
    ' :6, '||
    ' :7, '||
    ' :8, '||
    ' :9, '||
    ' :10  '||
    ') '
    USING l_map_id,
        p_plan_id,
        FND_GLOBAL.USER_ID,
        SYSDATE,
        FND_GLOBAL.USER_ID,
        SYSDATE,
        FND_GLOBAL.LOGIN_ID,
        p_server_group_id,
        0,
        0 ;

Line: 1274

PROCEDURE Update_Category (   x_return_status OUT NOCOPY VARCHAR2,
                                x_msg_count OUT NOCOPY NUMBER,
                                x_msg_data OUT NOCOPY VARCHAR2,
                                p_base_plan_id in  NUMBER,
                                p_media_type_id in NUMBER,
                                p_reg_plan_id in  NUMBER
                                )
as
    l_language             VARCHAR2(4);

Line: 1294

    fnd_msg_pub.delete_msg();

Line: 1302

    update IEB_WB_SVC_CATS set
        svcpln_svcpln_id =  p_reg_plan_id,
        LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
        LAST_UPDATE_DATE = SYSDATE,
        LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
    where MEDIA_TYPE_ID  = p_media_type_id
        and svcpln_svcpln_id =  p_base_plan_id;

Line: 1330

END  Update_Category;

Line: 1368

    fnd_msg_pub.delete_msg();

Line: 1378

    select IEB_SVC_LEVEL_S1.nextval into l_service_lvl_id from dual;

Line: 1380

    EXECUTE immediate  'insert into IEB_SERVICE_LEVELS_B '||
    '(SERVICE_LEVEL_ID, '||
    ' CREATED_BY, '||
    ' CREATION_DATE, '||
    ' LAST_UPDATED_BY, '||
    ' LAST_UPDATE_DATE, '||
    ' LAST_UPDATE_LOGIN, '||
    ' DIRECTION, '||
    ' MANDATORY_FLAG, '||
    ' HOURLY_QUOTA, '||
    ' MIN_AGENTS, '||
    ' GOAL_PERCENT, '||
    ' GOAL_TIME, '||
    ' MAX_WAIT_TIME, '||
    ' REROUTE_TIME, '||
    ' REROUTE_WARNING_TIME, '||
    ' OBJECT_VERSION_NUMBER, '||
    ' SECURITY_GROUP_ID '||
    ' ) values '||
    ' (:1, '||
    ' :2, '||
    ' :3, '||
    ' :4, '||
    ' :5, '||
    ' :6, '||
    ' :7, '||
    ' :8, '||
    ' :9, '||
    ' :10, '||
    ' :11, '||
    ' :12, '||
    ' :13, '||
    ' :14, '||
    ' :15, '||
    ' :16, '||
    ' :17 '||
    ') '
    USING l_service_lvl_id,
        FND_GLOBAL.USER_ID,
        SYSDATE,
        FND_GLOBAL.USER_ID,
        SYSDATE,
        FND_GLOBAL.LOGIN_ID,
        rec_obj.direction,
        l_temp_str,
        rec_obj.hourly_quota,
        rec_obj.min_agents,
        rec_obj.goal_percent,
        rec_obj.goal_time,
        rec_obj.max_wait_time,
        rec_obj.reroute_time,
        rec_obj.reroute_warning_time,
        0,
        0;

Line: 1439

 insert into IEB_SERVICE_LEVELS_TL (
    SERVICE_LEVEL_ID,
    CREATED_BY,
    CREATION_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATE_LOGIN,
    LEVEL_NAME,
    DESCRIPTION,
    OBJECT_VERSION_NUMBER,
    SECURITY_GROUP_ID,
    LANGUAGE,
    SOURCE_LANG
  ) select
    l_service_lvl_id,
    FND_GLOBAL.USER_ID,
    SYSDATE,
    FND_GLOBAL.USER_ID,
    SYSDATE,
    FND_GLOBAL.LOGIN_ID,
    LTRIM(RTRIM(rec_obj.level_name)),
    LTRIM(RTRIM(rec_obj.level_description)),
    0,
    0,
    L.LANGUAGE_CODE,
    userenv('LANG')
  from FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and not exists
    (select NULL
    from IEB_SERVICE_LEVELS_TL T
    where T.SERVICE_LEVEL_ID = l_service_lvl_id
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 1511

PROCEDURE Update_ServiceLevel (    x_return_status  OUT NOCOPY VARCHAR2,
                        x_msg_count OUT  NOCOPY NUMBER,
                       x_msg_data  OUT  NOCOPY VARCHAR2,
                       rec_obj IN SYSTEM.IEB_SERVICE_LEVELS_OBJ
                       )

as
    l_language             VARCHAR2(4);

Line: 1531

    fnd_msg_pub.delete_msg();

Line: 1540

        update IEB_SERVICE_LEVELS_B set
            LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
            LAST_UPDATE_DATE = SYSDATE,
            LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
            GOAL_PERCENT = rec_obj.goal_percent,
            GOAL_TIME = rec_obj.goal_time,
            MAX_WAIT_TIME = rec_obj.max_wait_time,
            MIN_AGENTS= rec_obj.min_agents
         where
          SERVICE_LEVEL_ID = rec_obj.service_level_id;

Line: 1553

        update IEB_SERVICE_LEVELS_B set
            LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
            LAST_UPDATE_DATE = SYSDATE,
            LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
            HOURLY_QUOTA = rec_obj.hourly_quota,
            MIN_AGENTS= rec_obj.min_agents
         where
          SERVICE_LEVEL_ID = rec_obj.service_level_id;

Line: 1585

END Update_ServiceLevel;

Line: 1606

PROCEDURE Delete_Service_Level(   x_return_status  OUT NOCOPY VARCHAR2,
                        x_msg_count OUT  NOCOPY NUMBER,
                       x_msg_data  OUT  NOCOPY VARCHAR2,
    x_service_level_id IN NUMBER
    )
    is

    l_language  VARCHAR2(4);

Line: 1626

    'delete from IEB_SERVICE_LEVELS_TL '||
    ' where  SERVICE_LEVEL_ID = :1  and language= :2'
    USING x_service_level_id, l_language;

Line: 1635

    ' delete from IEB_SERVICE_LEVELS_B '||
    ' where  SERVICE_LEVEL_ID =  :1'
    USING x_service_level_id;

Line: 1653

END Delete_Service_Level;

Line: 1671

PROCEDURE Delete_Service_Plan (   x_return_status  OUT NOCOPY VARCHAR2,
                        x_msg_count OUT  NOCOPY NUMBER,
                       x_msg_data  OUT  NOCOPY VARCHAR2,
    x_service_plan_id IN NUMBER
)
 as


    l_service_plan_b_id  IEB_SERVICE_PLANS.SVCPLN_ID%type;

Line: 1693

   ' delete from IEB_SERVICE_PLANS where  SVCPLN_ID = :1 or SVCPLN_ID in ( select SERVICE_PLAN_ID  from ieb_regional_plans where base_plan_id = :2)'
    USING x_service_plan_id, x_service_plan_id;

Line: 1700

    ' delete from IEB_SERVICE_PLANS_TL where  SERVICE_PLAN_ID = :1 or SERVICE_PLAN_ID in ( select SERVICE_PLAN_ID  from ieb_regional_plans where base_plan_id =:2)'
    USING x_service_plan_id, x_service_plan_id;

Line: 1710

    ' delete from ieb_group_plan_maps where service_plan_id = :1 or  service_plan_id in (select SERVICE_PLAN_ID  from ieb_regional_plans where base_plan_id =:2)'
    USING x_service_plan_id, x_service_plan_id ;

Line: 1719

    ' delete from IEB_INB_SVC_COVERAGES where  SVCPLN_SVCPLN_ID = :1 or SVCPLN_SVCPLN_ID in (select SERVICE_PLAN_ID  from ieb_regional_plans where base_plan_id =:2)'
    USING x_service_plan_id, x_service_plan_id;

Line: 1727

    ' delete from IEB_OUTB_SVC_COVERAGES where  SVCPLN_SVCPLN_ID = :1 or SVCPLN_SVCPLN_ID in (select SERVICE_PLAN_ID  from ieb_regional_plans where base_plan_id =:2)'
    USING x_service_plan_id, x_service_plan_id;

Line: 1735

    ' delete from ieb_regional_plans where base_plan_id = :1'
    USING x_service_plan_id;

Line: 1752

END Delete_Service_Plan;

Line: 1769

PROCEDURE Delete_Classification(    x_return_status  OUT NOCOPY VARCHAR2,
                        x_msg_count OUT  NOCOPY NUMBER,
                       x_msg_data  OUT  NOCOPY VARCHAR2,
                       p_svc_plan_id in  NUMBER,
                       p_media_type_id in NUMBER
                       )
 as


    l_service_plan_b_id  IEB_SERVICE_PLANS.SVCPLN_ID%type;

Line: 1792

    ' delete from   IEB_WB_SVC_CAT_RULES '||
    ' where WBSC_WBSC_ID in (select distinct c.WBSC_ID from '||
    ' IEB_WB_SVC_CATS c,IEB_WB_SVC_CAT_RULES r , IEB_REGIONAL_PLANS p '||
    ' where c.WBSC_ID = r.WBSC_WBSC_ID '||
    ' and (c.SVCPLN_SVCPLN_ID = :1 or p.BASE_PLAN_ID = :2) '||
    ' and c.MEDIA_TYPE_ID = :3  '||
    ' and p.SERVICE_PLAN_ID (+)= c.SVCPLN_SVCPLN_ID  ) '
    USING  p_svc_plan_id,p_svc_plan_id, p_media_type_id;

Line: 1806

    ' delete from IEB_WB_SVC_CATS s '||
    ' where s.WBSC_ID in ( select c.WBSC_ID from '||
    ' IEB_WB_SVC_CATS c,  IEB_REGIONAL_PLANS p '||
    ' where  c.MEDIA_TYPE_ID  = :1'||
    ' and (c.svcpln_svcpln_id =  :2 or p.BASE_PLAN_ID = :3) '||
    ' and p.SERVICE_PLAN_ID (+)= c.SVCPLN_SVCPLN_ID )  '
    USING p_media_type_id, p_svc_plan_id, p_svc_plan_id;

Line: 1826

END Delete_Classification;

Line: 1843

PROCEDURE Delete_Category(    x_return_status  OUT NOCOPY VARCHAR2,
                        x_msg_count OUT  NOCOPY NUMBER,
                       x_msg_data  OUT  NOCOPY VARCHAR2,
                       p_svc_plan_id in  NUMBER,
                       p_media_type_id in NUMBER
                       ) as


    l_service_plan_b_id  IEB_SERVICE_PLANS.SVCPLN_ID%type;

Line: 1865

    ' delete from IEB_WB_SVC_CATS s '||
    ' where s.WBSC_ID in ( select c.WBSC_ID from '||
    ' IEB_WB_SVC_CATS c,  IEB_REGIONAL_PLANS p '||
    ' where  c.MEDIA_TYPE_ID  = :1'||
    ' and (c.svcpln_svcpln_id =  :2 or p.BASE_PLAN_ID = :3) '||
    ' and p.SERVICE_PLAN_ID (+)= c.SVCPLN_SVCPLN_ID )  '
    USING p_media_type_id, p_svc_plan_id, p_svc_plan_id;

Line: 1886

END Delete_Category;

Line: 1902

PROCEDURE Delete_IOCoverages (   x_return_status OUT NOCOPY VARCHAR2,
                                x_msg_count OUT NOCOPY NUMBER,
                                x_msg_data OUT NOCOPY VARCHAR2,
                                p_direction IN VARCHAR2,
                                p_plan_id IN VARCHAR2
                                )as


    l_service_plan_b_id  IEB_SERVICE_PLANS.SVCPLN_ID%type;

Line: 1926

        ' delete from IEB_INB_SVC_COVERAGES where svcpln_svcpln_id =:1 '
        USING p_plan_id;

Line: 1935

        ' delete from IEB_OUTB_SVC_COVERAGES where svcpln_svcpln_id =:1 '
        USING p_plan_id;

Line: 1951

END Delete_IOCoverages;

Line: 1954

PROCEDURE Delete_Regional_Plan (   x_return_status  OUT NOCOPY VARCHAR2,
                        x_msg_count OUT  NOCOPY NUMBER,
                       x_msg_data  OUT  NOCOPY VARCHAR2,
                       p_base_plan_id IN NUMBER,
                       p_reg_plan_id IN NUMBER )as


    l_media_type_id  IEB_SERVICE_PLANS.MEDIA_TYPE_ID%type;

Line: 1977

    select MEDIA_TYPE_ID into l_media_type_id from IEB_SERVICE_PLANS where SVCPLN_ID = p_base_plan_id;

Line: 1979

    update IEB_WB_SVC_CATS set
        svcpln_svcpln_id =  p_base_plan_id,
        LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
        LAST_UPDATE_DATE = SYSDATE,
        LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
    where MEDIA_TYPE_ID  = l_media_type_id
        and svcpln_svcpln_id =  p_reg_plan_id;

Line: 1990

    ' delete from ieb_regional_plans where base_plan_id = :1 and SERVICE_PLAN_ID = :2'
    USING p_base_plan_id, p_reg_plan_id ;

Line: 1998

    ' delete from ieb_group_plan_maps where service_plan_id = :1 '
    USING p_reg_plan_id;

Line: 2006

    ' delete from IEB_INB_SVC_COVERAGES where  SVCPLN_SVCPLN_ID = :1 '
    USING p_reg_plan_id;

Line: 2014

    ' delete from IEB_OUTB_SVC_COVERAGES where  SVCPLN_SVCPLN_ID = :1'
    USING p_reg_plan_id;

Line: 2021

    ' delete from IEB_SERVICE_PLANS where  SVCPLN_ID = :1'
    USING p_reg_plan_id;

Line: 2029

    ' delete from IEB_SERVICE_PLANS_TL where  SERVICE_PLAN_ID = :1'
    USING p_reg_plan_id;

Line: 2045

END Delete_Regional_Plan;

Line: 2048

PROCEDURE Delete_Regional_PlanMaps (   x_return_status  OUT NOCOPY VARCHAR2,
                        x_msg_count OUT  NOCOPY NUMBER,
                       x_msg_data  OUT  NOCOPY VARCHAR2,
                       p_reg_plan_id IN NUMBER ,
                       p_base_plan_id IN NUMBER,
                       p_media_type_id IN NUMBER)as

    l_language  VARCHAR2(4);

Line: 2070

    ' delete from ieb_group_plan_maps where service_plan_id = :1 '
    USING p_reg_plan_id;

Line: 2078

    update IEB_WB_SVC_CATS set
        svcpln_svcpln_id =  p_base_plan_id,
        LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
        LAST_UPDATE_DATE = SYSDATE,
        LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
    where MEDIA_TYPE_ID  = p_media_type_id
        and svcpln_svcpln_id =  p_reg_plan_id;

Line: 2096

END Delete_Regional_PlanMaps;

Line: 2099

PROCEDURE Delete_SpecDateCoverages (   x_return_status OUT NOCOPY VARCHAR2,
                                x_msg_count OUT NOCOPY NUMBER,
                                x_msg_data OUT NOCOPY VARCHAR2,
                                p_direction IN VARCHAR2,
                                p_plan_id IN VARCHAR2,
                                p_spec_date IN VARCHAR2
                                )as


    l_media_type_id  IEB_SERVICE_PLANS.MEDIA_TYPE_ID%type;

Line: 2130

        ' delete from IEB_INB_SVC_COVERAGES where svcpln_svcpln_id =:1 and SPEC_SCHD_DATE =:2 and SCHEDULE_TYPE = :3'
        USING p_plan_id, p_spec_date, 'S';

Line: 2139

        ' delete from IEB_OUTB_SVC_COVERAGES where svcpln_svcpln_id =:1 and SPEC_SCHD_DATE=:2 and SCHEDULE_TYPE = :3'
        USING p_plan_id, p_spec_date, 'S';

Line: 2156

END Delete_SpecDateCoverages;