APPS.MSC_E1APS_UTIL SQL Statements

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

Line: 30

			sql_cnd := 'select INSTR('''||ws_prf||''','''||srch_str||''') from dual';

Line: 56

				SELECT instr(soap_respond,'')
				INTO   StartIndex
				FROM   dual;

Line: 60

				SELECT instr(soap_respond,'')
				INTO   EndIndex
				FROM   dual;

Line: 64

				SELECT SUBSTR(soap_respond,StartIndex+8,EndIndex-(StartIndex+8))
				INTO   returnStr
				FROM   dual;

Line: 102

					sql_cnd := 'select INSTR('''||ws_prf||''','''||srch_str||''') from dual';

Line: 128

							SELECT instr(soap_respond,'')
								INTO   StartIndex
								FROM   dual;

Line: 132

							SELECT instr(soap_respond,'')
								INTO   EndIndex
								FROM   dual;

Line: 136

							SELECT SUBSTR(soap_respond,StartIndex+8,EndIndex-(StartIndex+8))
								INTO   returnStr
								FROM   dual;

Line: 140

							SELECT instr(returnStr,'#')
								INTO   StartIndex
								FROM   dual;

Line: 144

							SELECT SUBSTR(returnStr,0,StartIndex-1)
								INTO   SessionNum
								FROM   dual;

Line: 148

							SELECT SUBSTR(returnStr,StartIndex+1,1800)
								INTO   ErrMessage
								FROM   dual;

Line: 182

						--****UPDATEVARIABLE****
						BEGIN
				           	--11g Web Logic  SOAP Request
							soap_request:= ''
										||'UPDATEVARIABLE001'
										||'E1TOAPSPROJECT.PVD_BASE_DATE='||BaseDate ||'/env:Body>';

Line: 200

							SELECT instr(soap_respond,'')
								INTO   StartIndex
								FROM   dual;

Line: 204

							SELECT instr(soap_respond,'')
								INTO   EndIndex
								FROM   dual;

Line: 208

							SELECT SUBSTR(soap_respond,StartIndex+8,EndIndex-(StartIndex+8))
								INTO   returnStr
								FROM   dual;

Line: 212

							SELECT instr(returnStr,'#')
								INTO   StartIndex
								FROM   dual;

Line: 216

							SELECT SUBSTR(returnStr,0,StartIndex-1)
								INTO   SessionNum
								FROM   dual;

Line: 220

							SELECT SUBSTR(returnStr,StartIndex+1,1800)
								INTO   ErrMessage
								FROM   dual;

Line: 226

				                ||'UPDATEVARIABLE'
				                ||'#ODI Session No.#Error Message='
				                ||returnStr;

Line: 234

				                ||'UPDATEVARIABLE'
				                ||'#ODI Session No.#Error Message='
				                ||returnStr;

Line: 244

									MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'UPDATE VARIABLE FAILED. Session #  ' || SessionNum);

Line: 253

								||'UPDATEVARIABLE'
								||'#ODI Session No.#Error Message=-1#Error in execution of ODI Scenario. Please check Application Server Log.';

Line: 276

							SELECT instr(soap_respond,'')
								INTO   StartIndex
								FROM   dual;

Line: 280

							SELECT instr(soap_respond,'')
								INTO   EndIndex
								FROM   dual;

Line: 284

							SELECT SUBSTR(soap_respond,StartIndex+8,EndIndex-(StartIndex+8))
								INTO   returnStr
								FROM   dual;

Line: 288

							SELECT instr(returnStr,'#')
								INTO   StartIndex
								FROM   dual;

Line: 292

							SELECT SUBSTR(returnStr,0,StartIndex-1)
								INTO   SessionNum
								FROM   dual;

Line: 296

							SELECT SUBSTR(returnStr,StartIndex+1,1800)
								INTO   ErrMessage
								FROM   dual;

Line: 363

                                l_sql := 'select user_name, password from '
                                ||dem_schema
                                ||'.user_id where user_id = '
                                ||l_user_id;

Line: 372

                                        l_sql := 'select user_name, password from '
                                        ||dem_schema
                                        ||'.user_id where user_id = '
                                        ||l_user_id;

Line: 390

                                l_sql         := 'SELECT
                                utl_http.request('''
                                ||l_url
                                ||'/WorkflowServer?action=run_proc&user='
                                ||l_user_name
                                ||'&password='
                                ||l_password
                                ||'&schema='
                                || REPLACE(l_schema_name, ' ', '%20')
                                ||'&sync=no'') FROM  dual';

Line: 478

                        v_sql  := 'select status  from '
                                  || g_demantra_schema
                                  || '.wf_process_log'
                                  || ' where '
                                  || ' process_id '
                                  || '='
                                  || ret_process_id;

Line: 556

          x_sql := 'select sess_no,sess_status  from snp_session@'|| DbLink||
                   ' where scen_name= '''||scenario_name||''' and rownum=1 order by sess_no desc ';

Line: 560

          x_sql := 'select sess_no,sess_status  from snp_session
                    where scen_name= '''||scenario_name||'''
                    and rownum=1 order by sess_no desc ';

Line: 598

/* Procedure to Update Trading Partner Site Code bug#9757888*/
PROCEDURE UPDATE_TP_SITE_CODE(errbuf OUT NOCOPY     VARCHAR2,
                     retcode OUT NOCOPY    VARCHAR2,
                     parInstanceID IN VARCHAR2,
                     updateFlag   IN NUMBER )
  IS

   lv_sql_stmt VARCHAR2(2000);

Line: 614

    SELECT NVL(count(1),0) into x_table_presence
    FROM dba_tables
    WHERE owner = owner AND table_name = 'MDP_MATRIX';

Line: 618

    IF (updateFlag = MSC_UTIL.SYS_NO) THEN
      IF (fnd_profile.value('MSD_DEM_SCHEMA') IS NOT NULL AND x_table_presence > 0) THEN
        x_dem_schema := fnd_profile.value('MSD_DEM_SCHEMA');

Line: 640

          select attribute15 into e1_instance_name from msc_apps_instances
                where instance_id = parInstanceID;

Line: 648

       IF updateFlag = MSC_UTIL.SYS_YES THEN
          -- TO Update TP_SITE_CODE to BILL_TO
         lv_sql_stmt :=
           'UPDATE msc_trading_partner_sites '
          ||' SET TP_SITE_CODE               = ''BILL_TO'' '
          ||' WHERE  partner_type            =  ' ||  MSC_CL_PRE_PROCESS.G_CUSTOMER
          ||' AND    sr_instance_id          =  ' ||  parInstanceID
          ||' AND    partner_address         like ''E1_BILL_TO%''';

Line: 663

           'UPDATE msc_trading_partner_sites '
           ||' SET TP_SITE_CODE            = ''SHIP_TO'' '
           ||' WHERE  partner_type            = ' || MSC_CL_PRE_PROCESS.G_CUSTOMER
           ||' AND    sr_instance_id          = ' || parInstanceID
           ||' AND partner_address not like ''E1_BILL_TO%'''; -- Removed comment for bug# 12558193

Line: 675

          ELSIF updateFlag = MSC_UTIL.SYS_NO THEN
          -- To Update TP_SITE_CODE with Location

           lv_sql_stmt :=
           'UPDATE msc_trading_partner_sites '
           ||' SET    TP_SITE_CODE            = LOCATION '
           ||' WHERE  partner_type            =  ' || MSC_CL_PRE_PROCESS.G_CUSTOMER
           ||' AND    sr_instance_id          =  ' ||  parInstanceID  ;

Line: 693

                  errbuf  := ' TP_SITE_CODE Update failed ';

Line: 697

   END UPDATE_TP_SITE_CODE;

Line: 712

			chk_sql := 'select err_msg from wr_execution_status@' ||DbLink|| ' where session_id = '''|| session_no || '''';

Line: 714

			chk_sql := 'select err_msg from wr_execution_status where session_id = ''' || session_no || '''';

Line: 771

                        SELECT instr(soap_respond,'')
                        INTO   StartIndex
                        FROM   dual;

Line: 775

                        SELECT instr(soap_respond,'')
                        INTO   EndIndex
                        FROM   dual;

Line: 779

                        SELECT SUBSTR(soap_respond,StartIndex+12,EndIndex-(StartIndex+12))
                        INTO   returnStr
                        FROM   dual;

Line: 832

                                SELECT instr(soap_respond,'')
                                INTO   StartIndex
                                FROM   dual;

Line: 836

                                SELECT instr(soap_respond,'')
                                INTO   EndIndex
                                FROM   dual;

Line: 840

                                SELECT SUBSTR(soap_respond,StartIndex+12,EndIndex-(StartIndex+12))
                                INTO   returnStr
                                FROM   dual;

Line: 844

                                SELECT instr(returnStr,'#')
                                INTO   StartIndex
                                FROM   dual;

Line: 848

                                SELECT SUBSTR(returnStr,0,StartIndex-1)
                                INTO   SessionNum
                                FROM   dual;

Line: 852

                                SELECT SUBSTR(returnStr,StartIndex+1,1800)
                                INTO   ErrMessage
                                FROM   dual;

Line: 878

                        /*UPDATEVARIABLE*/
                        BEGIN
                                soap_request:= ''
                                || ''
                                || ''
                                || 'UPDATEVARIABLE'
                                || '001'
                                || 'E1TOAPSPROJECT.PVD_BASE_DATE='||BaseDate ||''
                                || ''
                                || ''
                                ||'';

Line: 899

                                SELECT instr(soap_respond,'')
                                INTO   StartIndex
                                FROM   dual;

Line: 903

                                SELECT instr(soap_respond,'')
                                INTO   EndIndex
                                FROM   dual;

Line: 907

                                SELECT SUBSTR(soap_respond,StartIndex+12,EndIndex-(StartIndex+12))
                                INTO   returnStr
                                FROM   dual;

Line: 911

                                SELECT instr(returnStr,'#')
                                INTO   StartIndex
                                FROM   dual;

Line: 915

                                SELECT SUBSTR(returnStr,0,StartIndex-1)
                                INTO   SessionNum
                                FROM   dual;

Line: 919

                                SELECT SUBSTR(returnStr,StartIndex+1,1800)
                                INTO   ErrMessage
                                FROM   dual;

Line: 925

                                        ||'UPDATEVARIABLE'
                                        ||'#ODI Session No.#Error Message='
                                        ||returnStr;

Line: 932

                                        ||'UPDATEVARIABLE'
                                        ||'#ODI Session No.#Error Message='
                                        ||returnStr;

Line: 940

                                ||'UPDATEVARIABLE'
                                ||'#ODI Session No.#Error Message=-1#Error in execution of ODI Scenario. Please check Application Server Log.';

Line: 968

                                SELECT instr(soap_respond,'')
                                INTO   StartIndex
                                FROM   dual;

Line: 972

                                SELECT instr(soap_respond,'')
                                INTO   EndIndex
                                FROM   dual;

Line: 976

                                SELECT SUBSTR(soap_respond,StartIndex+12,EndIndex-(StartIndex+12))
                                INTO   returnStr
                                FROM   dual;

Line: 980

                                SELECT instr(returnStr,'#')
                                INTO   StartIndex
                                FROM   dual;

Line: 984

                                SELECT SUBSTR(returnStr,0,StartIndex-1)
                                INTO   SessionNum
                                FROM   dual;

Line: 988

                                SELECT SUBSTR(returnStr,StartIndex+1,1800)
                                INTO   ErrMessage
                                FROM   dual;