APPS.RRS_SITE_UDA_BULKLOAD_INTF SQL Statements

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

Line: 65

  select distinct process_status
  into l_status
  from rrs_site_ua_intf
  where data_set_id = 10000;

Line: 98

     SELECT VALUE
      FROM V$PARAMETER
      WHERE NAME = 'utl_file_dir';

Line: 220

                                    (SELECT 1
                                     FROM RRS_SITES_INTERFACE rsi_e
                                     WHERE rsi_e.TRANSACTION_TYPE  = ''CREATE''
                                       AND rsi_e.PROCESS_STATUS      = ''1''
                                       -- AND rsi_e.BATCH_ID    = UAI2.BATCH_ID
                                       AND rsi_e.SITE_ID = UAI2.SITE_ID
                                       )';

Line: 270

   select count(*) from RRS_SITE_UA_INTF
   WHERE PROCESS_STATUS = G_PS_TO_BE_PROCESSED
   AND data_set_id      = p_data_set_id;

Line: 274

   select count(*) from RRS_LOCATION_UA_INTF
   WHERE PROCESS_STATUS = G_PS_TO_BE_PROCESSED
   AND data_set_id      = p_data_set_id;

Line: 278

   SELECT count(*) FROM RRS_TRADEAREA_UA_INTF
   WHERE PROCESS_STATUS = G_PS_TO_BE_PROCESSED
   AND data_set_id      = p_data_set_id;

Line: 304

        SELECT 'HZ_PARTY:'||TO_CHAR(PERSON_ID)
          INTO G_HZ_PARTY_ID
        FROM EGO_PEOPLE_V
        WHERE USER_NAME = G_USER_NAME;

Line: 314

          UPDATE RRS_SITE_UA_INTF
          SET PROCESS_STATUS = G_PS_IN_PROCESS
          ,REQUEST_ID = G_REQUEST_ID
          ,PROGRAM_APPLICATION_ID = G_PROGAM_APPLICATION_ID
          ,PROGRAM_ID = G_PROGAM_ID
          ,PROGRAM_UPDATE_DATE = SYSDATE
          ,TRANSACTION_TYPE = UPPER(NVL(TRANSACTION_TYPE,EGO_USER_ATTRS_DATA_PVT.G_SYNC_MODE))
           WHERE DATA_SET_ID = p_data_set_id
           AND (PROCESS_STATUS IS NULL OR
            PROCESS_STATUS = G_PS_TO_BE_PROCESSED);

Line: 325

          l_rel_sql 		  := ' (SELECT RSU.SITE_USE_TYPE_CODE '||
					' FROM RRS_SITE_USES RSU' ||
					' WHERE RSU.SITE_ID =  UAI2.SITE_ID)'||
					' UNION ALL ' ||
					' (SELECT UAI2.SITE_USE_TYPE_CODE FROM DUAL)' ;

Line: 340

select 	distinct site_id , site_number, transaction_type
Bulk Collect
into 	l_site_id_list
from 	rrs_site_ua_intf
where 	data_set_id = p_data_set_id;

Line: 406

	        Update RRS_SITE_UA_INTF
        	Set     Process_status = '3'
        	Where   data_set_id = p_data_set_id
        	and     process_status = G_PS_IN_PROCESS
        	and     site_id = l_site_id_list(i).site_id
        	and     transaction_type = l_site_id_list(i).transaction_type;

Line: 505

                  DELETE FROM RRS_SITE_UA_INTF
                  WHERE BATCH_ID = p_batch_id
                  AND PROCESS_STATUS = G_PS_IN_PROCESS;

Line: 513

                 UPDATE RRS_SITE_UA_INTF
                 SET PROCESS_STATUS = G_PS_SUCCESS
                 WHERE batch_id = p_batch_id
                 AND PROCESS_STATUS = G_PS_IN_PROCESS;

Line: 528

           UPDATE RRS_LOCATION_UA_INTF
           SET PROCESS_STATUS = G_PS_IN_PROCESS
          ,REQUEST_ID = G_REQUEST_ID
          ,PROGRAM_APPLICATION_ID = G_PROGAM_APPLICATION_ID
          ,PROGRAM_ID = G_PROGAM_ID
          ,PROGRAM_UPDATE_DATE = SYSDATE
          ,CREATED_BY = DECODE(CREATED_BY, NULL, G_USER_ID, CREATED_BY)
          ,CREATION_DATE = DECODE(CREATION_DATE, NULL, SYSDATE, CREATION_DATE)
          ,LAST_UPDATED_BY = G_USER_ID
          ,LAST_UPDATE_DATE = SYSDATE
          ,LAST_UPDATE_LOGIN = G_LOGIN_ID
          ,TRANSACTION_TYPE = UPPER(NVL(TRANSACTION_TYPE,EGO_USER_ATTRS_DATA_PVT.G_SYNC_MODE))
           WHERE DATA_SET_ID = p_data_set_id
           AND (PROCESS_STATUS IS NULL OR
           PROCESS_STATUS = G_PS_TO_BE_PROCESSED);

Line: 545

          l_rel_sql 		  := ' (SELECT HL.COUNTRY '||
					' FROM HZ_LOCATIONS HL' ||
					' WHERE HL.LOCATION_ID = UAI2.LOCATION_ID)'||
					' UNION ALL ' ||
					' (SELECT UAI2.COUNTRY FROM DUAL)' ;

Line: 601

                  DELETE FROM RRS_LOCATION_UA_INTF
                  WHERE BATCH_ID = p_batch_id
                  AND PROCESS_STATUS = G_PS_IN_PROCESS;

Line: 609

                 UPDATE RRS_LOCATION_UA_INTF
                 SET PROCESS_STATUS = G_PS_SUCCESS
                 WHERE batch_id = p_batch_id
                 AND PROCESS_STATUS = G_PS_IN_PROCESS;

Line: 622

           UPDATE RRS_TRADEAREA_UA_INTF
           SET PROCESS_STATUS = G_PS_IN_PROCESS
          ,REQUEST_ID = G_REQUEST_ID
          ,PROGRAM_APPLICATION_ID = G_PROGAM_APPLICATION_ID
          ,PROGRAM_ID = G_PROGAM_ID
          ,PROGRAM_UPDATE_DATE = SYSDATE
          ,CREATED_BY = DECODE(CREATED_BY, NULL, G_USER_ID, CREATED_BY)
          ,CREATION_DATE = DECODE(CREATION_DATE, NULL, SYSDATE, CREATION_DATE)
          ,LAST_UPDATED_BY = G_USER_ID
          ,LAST_UPDATE_DATE = SYSDATE
          ,LAST_UPDATE_LOGIN = G_LOGIN_ID
          ,TRANSACTION_TYPE = UPPER(NVL(TRANSACTION_TYPE,EGO_USER_ATTRS_DATA_PVT.G_SYNC_MODE))
           WHERE DATA_SET_ID = p_data_set_id
           AND (PROCESS_STATUS IS NULL OR
           PROCESS_STATUS = G_PS_TO_BE_PROCESSED);

Line: 640

          l_rel_sql 		  := ' (SELECT to_char(RTA.GROUP_ID) '||
					' FROM RRS_TRADE_AREAS RTA' ||
					' WHERE RTA.TRADE_AREA_ID = UAI2.TRADE_AREA_ID)'||
					' UNION ALL ' ||
					' (SELECT UAI2.GROUP_ID FROM DUAL)' ;

Line: 695

                  DELETE FROM RRS_TRADEAREA_UA_INTF
                  WHERE BATCH_ID = p_batch_id
                  AND PROCESS_STATUS = G_PS_IN_PROCESS;

Line: 703

                 UPDATE RRS_TRADEAREA_UA_INTF
                 SET PROCESS_STATUS = G_PS_SUCCESS
                 WHERE batch_id = p_batch_id
                 AND PROCESS_STATUS = G_PS_IN_PROCESS;

Line: 737

      INSERT INTO rrs_interface_errors
         	(SITE_ID
			,SITE_IDENTIFICATION_NUMBER
			,COLUMN_NAME
			,MESSAGE_NAME
			,MESSAGE_TYPE
			,MESSAGE_TEXT
			,SOURCE_TABLE_NAME
			,DESTINATION_TABLE_NAME
			,CREATED_BY
			,CREATION_DATE
			,LAST_UPDATED_BY
			,LAST_UPDATE_DATE
			,LAST_UPDATE_LOGIN
			,REQUEST_ID
			,PROGRAM_APPLICATION_ID
			,PROGRAM_ID
			,PROGRAM_UPDATE_DATE
			,PROCESS_STATUS
			,TRANSACTION_TYPE
			,BATCH_ID
			)
	  SELECT NULL
	        ,NULL
	        ,COLUMN_NAME
			,MESSAGE_NAME
			,MESSAGE_TYPE
			,ERROR_MESSAGE
			,'RRS_SITE_UA_INTF'
			,'RRS_INTERFACE_ERRORS'
			,CREATED_BY
			,CREATION_DATE
			,LAST_UPDATED_BY
			,LAST_UPDATE_DATE
			,LAST_UPDATE_LOGIN
			,REQUEST_ID
			,PROGRAM_APPLICATION_ID
			,PROGRAM_ID
			,PROGRAM_UPDATE_DATE
            ,'3'
            ,NULL
            ,p_batch_id
      FROM   mtl_interface_errors
      WHERE  request_id = fnd_global.conc_request_id
      AND    program_application_id = FND_GLOBAL.PROG_APPL_ID;