APPS.MSC_RP_PUB SQL Statements

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

Line: 13

    l_update_sql_clob clob;  -- temporary

Line: 21

        dbms_lob.createtemporary(l_update_sql_clob,TRUE,DBMS_LOB.SESSION);

Line: 49

        clear_lob(l_update_sql_clob);

Line: 61

        dbms_lob.FREETEMPORARY(l_update_sql_clob);

Line: 119

 select nvl(criteria_changed,0),
 nvl(supply_inclusion_option,0)
 from msc_plans where plan_id=arg_plan_id;

Line: 124

 select
 criteria_id, table_name,criteria_condition,criteria_value,saved_query_keys from msc_rp_selection_criteria
 where
    plan_id=arg_plan_id and criteria_condition=2 and saved_query_keys is not null;

Line: 224

        APPEND(l_sql_clob, 'select ' || table2fields(l_table_name));

Line: 310

                '' ||
                '  ' || l_criteria_id || '' ||
                '  ' || l_table_xml_value || '' ||
                '  110' ||
                '  ' ||
                '    ' );

Line: 321

                '');

Line: 329

                        rp_Debug(l_counter || ' -- attempt to update ');

Line: 330

                        update msc_rp_selection_criteria
                               set key_values= l_key_values_xml_clob
                        where criteria_id= l_criteria_id;

Line: 333

                        rp_Debug(l_counter || ' -- after attempt to update ');

Line: 337

                        rp_Debug(l_counter || ' -- failed attempt to update ');

Line: 339

                            rp_Debug('Unable to update criteria table for criteria '||l_criteria_id);

Line: 385

            2)Insert plan rows with ASCP options
            3) Copy ASCP plan's msc_plans.ascp_plan_version to rp plan's  msc_plans.ascp_plan_version,  ascp msc_plans.plan_id to msc_plans.ascp_plan_id

    4. Return Success

*/

    Function COPY_ASCP_RP_OPTIONS(
        ASCP_PLAN_NAME IN varchar2,
        RP_PLAN_NAME IN varchar2,
        CATEGORY_SET_ID IN number,
        RP_HORIZON_DAYS IN number,
        RP_DESCRIPTION IN varchar2 default '',
        ERROR_DETAILS OUT NOCOPY varchar2,
        PROGRAM_TYPE OUT NOCOPY varchar2,
        REQUEST_ID OUT NOCOPY NUMBER
    ) return Number
    is

    --L_CALL_FROM_RP number:=1;--2;  --temp set the call from to 1

Line: 439

        select mp.plan_id,nvl(mp.ascp_plan_version,RP_NULL_VALUE), mp.plan_type, desig.designator_id, ascp_plan_id
        from msc_plans mp, msc_designators desig where mp.compile_designator=arg_plan_name
        and mp.compile_designator=desig.designator;

Line: 444

        select 1
        from msc_plans mp, msc_rp_plan_access mpa
        where mp.plan_id=mpa.plan_id(+)
            and
            (
                nvl(mp.private_plan,2) =2   --is public plan

            or

            (
                mp.private_plan =1 and mpa.user_id=l_user
            )
            )
                and mp.plan_id=arg_plan_id;

Line: 460

        select mp.description,mp.category_set_id,mp.daily_cutoff_bucket
        from msc_plans mp, msc_designators desig where mp.plan_id=arg_plan_id
        and mp.compile_designator=desig.designator;

Line: 466

        select msc_plan_organization_s.nextval from dual;

Line: 469

        Select
            Inventory_Atp_Flag, Production, launch_workflow_flag, Disable_Date, organization_id, sr_instance_id
        From Msc_Designators
        Where (Designator, Organization_Id, Sr_Instance_Id) =
            (Select
                Compile_Designator, Organization_Id, Sr_Instance_Id
                From
                    Msc_Plans
                Where
                    Plan_Id=p_plan_id);

Line: 593

                rp_debug('bef delete plan options for target rp');

Line: 596

                MSC_COPY_PLAN_OPTIONS.delete_plan_options
                (ERROR_DETAILS, l_retcode, rp_id);

Line: 599

                rp_debug('after delete retcode='||l_retcode);

Line: 690

            rp_debug(' bef executing update: rp_id='||rp_id);

Line: 700

                    update MSC_PLANS
                    set
                        ascp_plan_id=ascp_id,
                        ascp_plan_version=l_ascp_plan_version,
                        category_set_id=l_category_set_id,
                        daily_cutoff_bucket=l_daily_cutoff_bucket,
                        has_run=1,
                        loaded_flag=null,
                        copy_plan_id=null,
                        status=1,
                        curr_overwrite_option=3,
                        curr_part_include_type=8,
                        part_include_type=8,
                        curr_cutoff_date= curr_start_date + l_daily_cutoff_bucket,
                        WEEKLY_CUTOFF_BUCKET=0,
                        PERIOD_CUTOFF_BUCKET=0,
                        compute_constraints=1 -- bug14750825
                        where plan_id = rp_id;

Line: 746

                    update MSC_plan_organizations
                    set
                    program_id= msc_plan_organization_s.nextval
                    where plan_id = rp_id;

Line: 775

                    update MSC_plan_schedules
                    set
                    program_id= msc_plan_organization_s.nextval
                    where plan_id = rp_id;

Line: 806

                    update MSC_DESIGNATORS
                    set
                    copy_Designator_id=null
                    where designator_id = rp_designator_id;

Line: 825

            rp_debug('done with update');

Line: 860

                    update MSC_PLANS
                        set
                            request_id=request_id1
                        where plan_id = rp_id;

Line: 868

                        rp_Debug('Unable to update request_id for target rp plan_id'||rp_id);

Line: 956

                        update MSC_PLANS
                        set
                        request_id=request_id2,
                        has_run=1,
                        status=1
                        where plan_id = rp_id;

Line: 967

                            rp_Debug('Unable to update request_id for target rp plan_id'||rp_id);

Line: 1042

        select rowid from msc_plans
        where base_plan_id=arg_plan_id
        for update of plan_completion_date,has_run,base_plan_id  nowait;

Line: 1053

        rp_debug('In process_child_rp_plans: Attempting to update Msc_Plans for child rp plans...');

Line: 1067

                UPDATE MSC_PLANS
                SET plan_completion_date=null,
                has_run=0,
                base_plan_id=null
                WHERE ROWID=l_rowlist(I);

Line: 1072

                rp_debug('Debug Only: Updated child plans...'||l_rowlist.count);

Line: 1087

                        rp_debug('Unable to lock/update msc_plans row after: ('||l_Counter||') Tries...');

Line: 1102

        select decode(sign(sum( decode ( nvl(plan_safety_stock,2),
                            2,0,
                            1,1)
                   )),
                   1,2,
                   0) ss_profile_value
        from msc_plan_organizations org where org.plan_id=arg_plan_id;

Line: 1112

    delete msc_plan_profiles where plan_id=p_plan_id;

Line: 1122

        insert into msc_plan_profiles (plan_id,
        profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
        values(p_plan_id, 'COMP_SUBSTITUTION_OPTION' , fnd_profile.value('COMP_SUBSTITUTION_OPTION'),  sysdate,
        p_user_id, sysdate, p_user_id,p_user_id);

Line: 1127

        insert into msc_plan_profiles (plan_id,
        profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
        values(p_plan_id, 'LATENESS_THRESHOLD' , fnd_profile.value('LATENESS_THRESHOLD'),  sysdate, p_user_id,
        sysdate, p_user_id,p_user_id);

Line: 1132

        insert into msc_plan_profiles (plan_id,
        profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
        values(p_plan_id, 'MRP_FIRM_OE_XFERRED_INTERNAL_REQ' ,
        fnd_profile.value('MRP_FIRM_OE_XFERRED_INTERNAL_REQ'),  sysdate, p_user_id, sysdate, p_user_id,p_user_id);

Line: 1137

        insert into msc_plan_profiles (plan_id,
        profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
        values(p_plan_id, 'MRP_FIRM_ORDER_TF' , fnd_profile.value('MRP_FIRM_ORDER_TF'),  sysdate, p_user_id,
        sysdate, p_user_id,p_user_id);

Line: 1142

        insert into msc_plan_profiles (plan_id,
        profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
        values(p_plan_id, 'MRP_FIRM_PO_TF' , fnd_profile.value('MRP_FIRM_PO_TF'),  sysdate, p_user_id, sysdate,
        p_user_id,p_user_id);

Line: 1147

        insert into msc_plan_profiles (plan_id,
        profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
        values(p_plan_id, 'MRP_LOAD_REQ_GROUP_BY' , fnd_profile.value('MRP_LOAD_REQ_GROUP_BY'),  sysdate, p_user_id,
        sysdate, p_user_id,p_user_id);

Line: 1152

        insert into msc_plan_profiles (plan_id,
        profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
        values(p_plan_id, 'MSC_AUTO_REL_COMP_TOLERANCE' , fnd_profile.value('MSC_AUTO_REL_COMP_TOLERANCE'),
        sysdate, p_user_id, sysdate, p_user_id,p_user_id);

Line: 1157

        insert into msc_plan_profiles (plan_id,
        profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
        values(p_plan_id, 'MSC_CONSUME_FORECAST_DTF' , fnd_profile.value('MSC_CONSUME_FORECAST_DTF'),  sysdate,
        p_user_id, sysdate, p_user_id,p_user_id);

Line: 1162

        insert into msc_plan_profiles (plan_id,
        profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
        values(p_plan_id, 'MSC_CONSUME_INT_SALES_ORDERS' , fnd_profile.value('MSC_CONSUME_INT_SALES_ORDERS'),
        sysdate, p_user_id, sysdate, p_user_id,p_user_id);

Line: 1167

        insert into msc_plan_profiles (plan_id,
        profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
        values(p_plan_id, 'MSC_CONSUME_NULL_DC' , fnd_profile.value('MSC_CONSUME_NULL_DC'),  sysdate, p_user_id,
        sysdate, p_user_id,p_user_id);

Line: 1172

        insert into msc_plan_profiles (plan_id,
        profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
        values(p_plan_id, 'MSC_FIRM_PURCH_REQ_TF' , fnd_profile.value('MSC_FIRM_PURCH_REQ_TF'),  sysdate, p_user_id,
        sysdate, p_user_id,p_user_id);

Line: 1177

        insert into msc_plan_profiles (plan_id,
        profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
        values(p_plan_id, 'MSC_INFLATE_WIP' , fnd_profile.value('MSC_INFLATE_WIP'),  sysdate, p_user_id, sysdate,
        p_user_id,p_user_id);

Line: 1182

        insert into msc_plan_profiles (plan_id,
        profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
        values(p_plan_id, 'MSC_RELEASED_BY_USER_ONLY' , fnd_profile.value('MSC_RELEASED_BY_USER_ONLY'),  sysdate,
        p_user_id, sysdate, p_user_id,p_user_id);

Line: 1188

        insert into msc_plan_profiles (plan_id,
        profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
        values(p_plan_id, 'MSC_RELEASE_FLAG' , 'Y' ,  sysdate, p_user_id, sysdate,
        p_user_id,p_user_id);

Line: 1193

        insert into msc_plan_profiles (plan_id,
        profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
        values(p_plan_id, 'MSC_RP_GROUP_PLANNED_ORDERS' , fnd_profile.value('MSC_RP_GROUP_PLANNED_ORDERS'),
        sysdate, p_user_id, sysdate, p_user_id,p_user_id);

Line: 1198

        insert into msc_plan_profiles (plan_id,
        profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
        values(p_plan_id, 'MSC_RP_SAFETY_STOCK_METHOD' ,l_ss_profile_value,
        sysdate, p_user_id, sysdate, p_user_id,p_user_id);

Line: 1203

        insert into msc_plan_profiles (plan_id,
        profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
        values(p_plan_id, 'MSC_SS_BKT_START_OFFSET_DAYS' , fnd_profile.value('MSC_SS_BKT_START_OFFSET_DAYS'),
        sysdate, p_user_id, sysdate, p_user_id,p_user_id);

Line: 1208

        insert into msc_plan_profiles (plan_id,
        profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
        values(p_plan_id, 'MSO_DEFAULT_FORECAST_PRIORITY' , fnd_profile.value('MSO_DEFAULT_FORECAST_PRIORITY'),
        sysdate, p_user_id, sysdate, p_user_id,p_user_id);

Line: 1213

        insert into msc_plan_profiles (plan_id,
        profile_code,profile_value,last_update_date,last_updated_by,creation_date,created_by,last_update_login)
        values(p_plan_id, 'MSO_DEFAULT_SO_PRIORITY' , fnd_profile.value('MSO_DEFAULT_SO_PRIORITY'),  sysdate,
        p_user_id, sysdate, p_user_id,p_user_id);