APPS.MSD_DEM_PUSH_SETUP_PARAMETERS SQL Statements

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

Line: 100

         x_sql := 'SELECT multi_org_flag FROM fnd_product_groups' || g_dblink ||
                  ' WHERE product_group_type = ''Standard''';

Line: 164

It is recommended to set the profile to selectively collect these level values.
To collect all the available geography dimension level values, please clear
out the dummy profile value. Until the profile value is set appropriately or
cleared out, only the dummy level value (other) will be collected into
Demand Management for geography dimension.';

Line: 182

         x_sql := 'SELECT count(*) FROM msd_dem_setup_parameters' || g_dblink;

Line: 438

	    msd_dem_common_utilities.log_message ('If this is not the master org, please update the MSD_DEM_MASTER_ORG profile on the Source');

Line: 470

            x_sql := 'DELETE FROM msd_dem_setup_parameters' || g_dblink;

Line: 474

            msd_dem_common_utilities.log_message ('Inserting profiles into source msd_dem_setup_parameters');

Line: 475

            x_sql := 'INSERT INTO msd_dem_setup_parameters' || g_dblink ||
                     ' (parameter_name, parameter_value, last_update_date, last_updated_by, creation_date, ' ||
                     '  created_by, last_update_login) values (:1, :2, sysdate, :3, sysdate, ' ||
                     '  :4, :5)';

Line: 491

               x_sql := 'DELETE FROM msd_dem_setup_parameters';

Line: 494

               msd_dem_common_utilities.log_message ('Inserting profiles into destination msd_dem_setup_parameters');

Line: 495

               x_sql := 'INSERT INTO msd_dem_setup_parameters' ||
                     ' (parameter_name, parameter_value, last_update_date, last_updated_by, creation_date, ' ||
                     '  created_by, last_update_login) values (:1, :2, sysdate, :3, sysdate, ' ||
                     '  :4, :5)';

Line: 561

         x_sql := 'DELETE FROM msd_dem_app_instance_orgs' || g_dblink;

Line: 565

         msd_dem_common_utilities.log_message ('Inserting Organizations into source msd_dem_app_instance_orgs');

Line: 566

         x_sql := 'INSERT INTO msd_dem_app_instance_orgs' || g_dblink ||
                  ' ( organization_id, organization_code, last_update_date, last_updated_by, creation_date, ' ||
                  '   created_by, last_update_login) ' ||
                  ' SELECT mtp.sr_tp_id, mtp.organization_code, sysdate, :a1, sysdate, ' ||
                  '  :a2, :a3 ' ||
                  ' FROM msc_instance_orgs mio, ' ||
                  '      msc_trading_partners mtp ' ||
                  ' WHERE mio.sr_instance_id = :1 ' ||
                  '   AND nvl(mio.org_group, ''-888'') = decode( :2, ''-999'', nvl(mio.org_group, ''-888''), :3) ' ||
                  '   AND nvl(mio.dp_enabled_flag, mio.enabled_flag) = 1 ' ||
                  '   AND mtp.sr_instance_id = mio.sr_instance_id ' ||
                  '   AND mtp.sr_tp_id = mio.organization_id ' ||
                  '   AND mtp.partner_type = 3';

Line: 585

            x_sql := 'DELETE FROM msd_dem_app_instance_orgs' || g_dblink || ' mdaio ' ||
                     ' WHERE NOT EXISTS (SELECT 1 from hr_organization_information' || g_dblink || ' hoi ' ||
                     '               WHERE  hoi.organization_id = mdaio.organization_id ' ||
                     '                  AND hoi.org_information_context = ''CLASS'' ' ||
                     '                  AND hoi.org_information1 = ''SPF'' ' ||
                     '                  AND hoi.org_information2 = ''Y'') ';

Line: 600

            x_sql := 'DELETE FROM msd_dem_app_instance_orgs' || g_dblink || ' mdaio ' ||
                     ' WHERE EXISTS (SELECT 1 from hr_organization_information' || g_dblink || ' hoi ' ||
                     '               WHERE  hoi.organization_id = mdaio.organization_id ' ||
                     '                  AND hoi.org_information_context = ''CLASS'' ' ||
                     '                  AND hoi.org_information1 = ''SPF'' ' ||
                     '                  AND hoi.org_information2 = ''Y'') ';

Line: 617

            x_sql := 'DELETE FROM msd_dem_app_instance_orgs';

Line: 620

            msd_dem_common_utilities.log_message ('Inserting Organizations into destination msd_dem_app_instance_orgs');

Line: 621

            x_sql := 'INSERT INTO msd_dem_app_instance_orgs' ||
                     ' ( organization_id, organization_code, last_update_date, last_updated_by, creation_date, ' ||
                     '   created_by, last_update_login) ' ||
                     ' SELECT organization_id, organization_code, sysdate, :1, sysdate, :2, :3 ' ||
                     ' FROM msd_dem_app_instance_orgs' || g_dblink;

Line: 662

            SELECT meaning
            FROM fnd_lookup_values_vl
            WHERE lookup_type = 'MSD_DEM_TABLES'
              AND lookup_code = p_lookup_code;

Line: 694

         x_sql := 'DELETE FROM msd_dem_dates' || g_dblink;

Line: 700

         x_sql := 'SELECT time_bucket, first_day_of_week, aggregation_method ' ||
                  ' FROM ' || x_dm_table ||
                  ' WHERE dm_or_template = 2 ' ||
                  '   AND is_active = 1 ';

Line: 709

            msd_dem_common_utilities.log_message ('Lowest Time Bucket - Day : Time data not inserted into source msd_dem_dates');

Line: 741

             msd_dem_common_utilities.log_message ('Inserting time data into source msd_dem_dates');

Line: 742

             x_sql := 'INSERT INTO msd_dem_dates' || g_dblink ||
                      ' (datet, num_of_days, start_date, end_date, last_update_date, last_updated_by, creation_date, created_by, last_update_login) ' ||
                      ' SELECT datet, num_of_days, ' || x_start_date || x_end_date || ',' ||
                      ' sysdate, :1, sysdate, :2, :3 ' ||
                      ' FROM ' || x_source_time_table;

Line: 747

            msd_dem_common_utilities.log_debug('SQL for inserting data into source msd_dem_dates : ');

Line: 756

            x_sql := 'DELETE FROM msd_dem_dates';

Line: 760

                msd_dem_common_utilities.log_message ('Inserting time data into destination msd_dem_dates');

Line: 761

                x_sql := 'INSERT INTO msd_dem_dates' ||
                         ' (datet, num_of_days, start_date, end_date, last_update_date, last_updated_by, creation_date, created_by, last_update_login) ' ||
                         ' SELECT datet, num_of_days, ' || x_start_date || x_end_date || ',' ||
                         ' sysdate, :1, sysdate, :2, :3 ' ||
                         ' FROM ' || x_source_time_table;

Line: 766

                msd_dem_common_utilities.log_debug('SQL for inserting data into destination msd_dem_dates : ');

Line: 779

         x_sql := 'DELETE FROM msd_dem_day_dates';

Line: 782

         msd_dem_common_utilities.log_message ('Inserting time data into destination msd_dem_day_dates');

Line: 783

         x_sql := 'INSERT INTO msd_dem_day_dates' ||
                  ' (day_date, bucket_date, bucket_start_date, bucket_end_date, last_update_date, last_updated_by, creation_date, created_by, last_update_login) ' ||
                  ' with dem_dates as '||
                  ' (SELECT datet, num_of_days, ' || x_start_date || x_end_date ||
                  '  FROM ' || x_source_time_table || ') ' ||
                  ' select start_date + i day_date, datet, start_date, end_date, sysdate, :1, sysdate, :2, :3 ' ||
                  '  from dem_dates, xmltable(''for $i in 0 to xs:int(D)-1 return $i'' passing xmlelement(d, num_of_days)
                                                                    columns i integer path ''.'')' ;

Line: 791

         msd_dem_common_utilities.log_debug('SQL for inserting data into destination msd_dem_day_dates : ');

Line: 802

             x_sql := 'DELETE FROM msd_dem_day_dates' || g_dblink;

Line: 805

             msd_dem_common_utilities.log_message('Inserting data into source msd_dem_day_dates');

Line: 806

             x_sql := 'INSERT INTO msd_dem_day_dates' || g_dblink ||
                     ' (day_date, bucket_date, bucket_start_date, bucket_end_date, last_update_date, last_updated_by, creation_date, created_by, last_update_login) ' ||
                     ' SELECT day_date, bucket_date, bucket_start_date, bucket_end_date, ' ||
                     ' sysdate, :1, sysdate, :2, :3 ' ||
                     ' FROM msd_dem_day_dates' ;

Line: 811

             msd_dem_common_utilities.log_debug('SQL for inserting data into source msd_dem_day_dates : ');

Line: 858

         SELECT owner
         FROM dba_objects
         WHERE  owner = owner
            AND object_type = 'TABLE'
            AND object_name = 'MDP_MATRIX'
         ORDER BY created desc;

Line: 989

     * This procedure updates profiles values configure for a particular legacy instance
     * to the legacy profiles table - MSD_DEM_LEGACY_SETUP_PARAMS
     */
    PROCEDURE CONFIGURE_LEGACY_PROFILES (
      			errbuf         		OUT  NOCOPY VARCHAR2,
      			retcode        		OUT  NOCOPY VARCHAR2,
      			p_legacy_instance_id 	IN	    NUMBER,
      			p_master_org            IN      NUMBER,
                p_sr_category_set_id    IN      NUMBER)
    IS
        /*** LOCAL VARIABLES ***/
        x_sql		VARCHAR2(500)	:= NULL;

Line: 1015

        /* Before inserting parameter values for a legacy instance to the MSD_DEM_LEGACY_SETUP_PARAMS table
         * delete any rows which already exist for this instance.
         */
        msd_dem_common_utilities.log_message ('Deleting records from msd_dem_legacy_setup_params, for instance_id : ' || p_legacy_instance_id );

Line: 1019

        x_sql := 'DELETE FROM msd_dem_legacy_setup_params where instance_id = ' || p_legacy_instance_id;

Line: 1025

        /* Insert values for the two parameters
         * MSD_DEM_MASTER_ORG and MSD_DEM_CATEGORY_SET_NAME
         * into MSD_DEM_LEGACY_SETUP_PARAMS table
         */
        x_sql := 'INSERT INTO msd_dem_legacy_setup_params' ||
                     ' (instance_id, parameter_name, parameter_value, last_update_date, last_updated_by, creation_date, ' ||
                     '  created_by, last_update_login) values (:1, :2, :3, sysdate, :4, sysdate, ' ||
                     '  :5, :6)';

Line: 1034

        msd_dem_common_utilities.log_message ('Inserting profile ' || g_master_org || ' for legacy instance ' || p_legacy_instance_id || ' into msd_dem_legacy_setup_params');

Line: 1037

        msd_dem_common_utilities.log_message ('Inserting profile ' || g_sr_category_set || ' for legacy instance ' || p_legacy_instance_id || ' into msd_dem_legacy_setup_params');

Line: 1095

        x_sql := 'SELECT parameter_value FROM msd_dem_legacy_setup_params where instance_id = :1 and parameter_name = :2';

Line: 1123

        x_sql := 'DELETE FROM msd_dem_setup_parameters where parameter_name in (''' || g_master_org || ''', ''' || g_sr_category_set || ''')';

Line: 1129

        /* Insert values for the two profiles
         * MSD_DEM_MASTER_ORG and MSD_DEM_CATEGORY_SET_NAME
         * into MSD_DEM_SETUP_PARAMETERS table
         */
        x_sql := 'INSERT INTO msd_dem_setup_parameters' ||
                     ' (parameter_name, parameter_value, last_update_date, last_updated_by, creation_date, ' ||
                     '  created_by, last_update_login) values (:1, :2, sysdate, :3, sysdate, ' ||
                     '  :4, :5)';

Line: 1138

        msd_dem_common_utilities.log_message ('Inserting profile ' || g_master_org || ' into msd_dem_setup_parameters');

Line: 1141

        msd_dem_common_utilities.log_message ('Inserting profile ' || g_sr_category_set || ' into msd_dem_setup_parameters');