eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BIM_RESPONSE_FACTS_PKG SQL Statements

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

Line: 28


    l_last_update_date        DATE;

Line: 73


              SELECT  MAX(end_date)
              FROM    bim_rep_history
              WHERE   object = 'RESPONSE';

Line: 212


/* This procedure will insert a HISTORY record whenever daily or first load is run */
--------------------------------------------------------------------------------------------------

PROCEDURE LOG_HISTORY
    (--p_api_version_number    IN   NUMBER
    --,p_init_msg_list         IN   VARCHAR2     := FND_API.G_FALSE
    --,x_msg_count             OUT  NUMBER
    --,x_msg_data              OUT  VARCHAR2
    --,x_return_status         OUT  VARCHAR2
    p_object                   IN   VARCHAR2,
    p_start_date               IN  DATE         DEFAULT NULL,
    p_end_date                 IN  DATE         DEFAULT NULL
    )
    IS
    l_user_id            	NUMBER := FND_GLOBAL.USER_ID();

Line: 256


    INSERT INTO bim_rep_history
     (CREATION_DATE,
      LAST_UPDATE_DATE,
      CREATED_BY,
      LAST_UPDATED_BY,
      OBJECT,
      OBJECT_LAST_UPDATED_DATE,
      START_DATE,
      END_DATE)
    VALUES
     (sysdate,
      sysdate,
      l_user_id,
      l_user_id,
      p_object,
      sysdate,
      p_start_date,
      p_end_date);

Line: 335


    l_last_update_date     	  DATE;

Line: 434


      INSERT /*+ append parallel(RDF,1) */
      INTO bim_r_rgrd_daily_facts RDF
      (
       Grade_daily_transaction_id
      ,creation_date
      ,last_update_date
      ,created_by
      ,last_updated_by
      ,last_update_login
      ,Object_Id
      ,Object_type
      ,Object_status
      ,Source_Code
      ,Source_Code_Id
      ,Response_Region
      ,Response_Country
      ,Business_Unit_Id
      ,Response_Grade
      ,Response_Grade_Count
      ,landing_pad_hits
      ,survey_completed
      ,transaction_Create_Date
      ,weekend_date
      )
      SELECT
         bim_r_rgrd_daily_facts_s.nextval
        ,sysdate
        ,sysdate
        ,-1
        ,-1
        ,-1
      	,d.parent_object_id     object_id
      	,d.parent_object_type	object_type
      	,d.status		object_status
      	,a.source_code         	source_code
      	,a.source_code_id      	source_code_id
      	,a.region              	region
      	,a.country             	country
      	,d.business_unit_id	business_unit_id
      	,b.response_grade	response_grade
      	,b.response_grade_count	response_grade_count
      	,a.landing_pad_hits	Landing_pad_hits
      	,a.survey_completed	survey_completed
      	,a.response_create_date response_create_date
        ,trunc((decode(decode( to_char(response_create_date,'MM') , to_char(next_day(response_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
      	        ,'TRUE'
      	        ,decode(decode(response_create_date , (next_day(response_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
       	        ,'TRUE'
      	        ,response_create_date
      	        ,'FALSE'
      	        ,next_day(response_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
      	        ,'FALSE'
      	        ,decode(decode(to_char(response_create_date,'MM'),to_char(next_day(response_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
      	        ,'FALSE'
      	        ,last_day(response_create_date)))))   weekend_date
      FROM
      	   	bim_r_resp_int_header	a,
      	   	bim_r_resp_int_grades	b,
      	   	bim_r_source_codes	d
      WHERE  	a.response_create_date >=  p_start_date
      AND	a.response_create_date <=  p_end_date
      AND	a.object_id		=  decode(d.object_type,'CAMP',d.parent_object_id,'EVEH',d.parent_object_id,
						'CSCH',d.object_id,'EVEO',d.object_id)
      AND	a.object_type 		=  d.object_type
      AND	a.interface_header_id	= b.interface_header_id;

Line: 511


      INSERT /*+ append parallel(RDF,1) */
      INTO bim_r_rrsn_daily_facts RDF
      (
       Reason_daily_transaction_id
      ,creation_date
      ,last_update_date
      ,created_by
      ,last_updated_by
      ,last_update_login
      ,Object_Id
      ,Object_type
      ,Object_status
      ,Source_Code
      ,Source_Code_Id
      ,Response_Region
      ,Response_Country
      ,Business_Unit_Id
      ,Invalid_Reason
      ,Invalid_Responses
      ,landing_pad_hits
      ,survey_completed
      ,transaction_Create_Date
      ,weekend_date
      )
      SELECT
         bim_r_rrsn_daily_facts_s.nextval
        ,sysdate
        ,sysdate
        ,-1
        ,-1
        ,-1
      	,d.parent_object_id    	object_id
      	,d.parent_object_type	object_type
      	,d.status		object_status
      	,a.source_code         	source_code
      	,a.source_code_id      	source_code_id
      	,a.region              	region
      	,a.country             	country
      	,d.business_unit_id	business_unit_id
      	,b.invalid_reason	invalid_reason
      	,b.invalid_responses	invalid_responses
      	,a.landing_pad_hits	Landing_pad_hits
      	,a.survey_completed	survey_completed
      	,a.response_create_date response_create_date
        ,trunc((decode(decode( to_char(response_create_date,'MM') , to_char(next_day(response_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
      	        ,'TRUE'
      	        ,decode(decode(response_create_date , (next_day(response_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
       	        ,'TRUE'
      	        ,response_create_date
      	        ,'FALSE'
      	        ,next_day(response_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
      	        ,'FALSE'
      	        ,decode(decode(to_char(response_create_date,'MM'),to_char(next_day(response_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
      	        ,'FALSE'
      	        ,last_day(response_create_date)))))   weekend_date
      FROM
      	   	bim_r_resp_int_header	a,
      	   	bim_r_resp_int_reason	b,
      	   	bim_r_source_codes	d
      WHERE  	a.response_create_date >=  p_start_date
      AND	a.response_create_date <=  p_end_date
      AND	a.object_type		=  d.object_type
      AND	a.object_id		=  decode(d.object_type,'CAMP',d.parent_object_id,'EVEH',d.parent_object_id,
						'CSCH',d.object_id,'EVEO',d.object_id)
      AND	a.interface_header_id	= b.interface_header_id;

Line: 609


   /*  INSERT INTO WEEKLY SUMMARY TABLE */

   l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');

Line: 621


   /*BEGIN BLOCK FOR THE WEEKLY INSERT */
     INSERT /*+ append parallel(RWF,1) */
     INTO bim_r_rgrd_weekly_facts  RWF
     (
      Grade_Weekly_transaction_id
     ,creation_date
     ,last_update_date
     ,created_by
     ,last_updated_by
     ,last_update_login
     ,Object_Id
     ,Object_type
     ,Object_status
     ,Source_Code
     ,Source_Code_Id
     ,Response_Region
     ,Response_Country
     ,Business_Unit_Id
     ,Response_Grade
     ,weekend_Date
     ,Response_Grade_Count
     ,landing_pad_hits
     ,survey_completed
     )
     SELECT
      bim_r_rgrd_weekly_facts_s.nextval
     ,sysdate
     ,sysdate
     ,-1
     ,-1
     ,-1
     ,Object_Id
     ,Object_type
     ,Object_status
     ,Source_Code
     ,Source_Code_Id
     ,Response_Region
     ,Response_Country
     ,Business_Unit_Id
     ,Response_Grade
     ,weekend_date
     ,Response_Grade_Count
     ,landing_pad_hits
     ,survey_completed
     FROM  (
     SELECT
      Object_Id
     ,Object_type
     ,Object_status
     ,Source_Code
     ,Source_Code_Id
     ,Response_Region
     ,Response_Country
     ,Business_Unit_Id
     ,Response_Grade
     ,weekend_date
     ,sum(Response_Grade_Count) Response_Grade_Count
     ,sum(landing_pad_hits)	Landing_pad_hits
     ,sum(survey_completed)	Survey_Completed
     FROM	bim_r_rgrd_daily_facts
     GROUP BY
      	Object_Id
     	,Object_type
     ,Object_status
     ,Source_Code
     ,Source_Code_Id
     ,Response_Region
     ,Response_Country
     ,Business_Unit_Id
     ,Response_Grade
     ,weekend_date
     );

Line: 707


     INSERT /*+ append parallel(RWF,1) */
     INTO bim_r_rrsn_weekly_facts RWF
     (
      Reason_Weekly_transaction_id
     ,creation_date
     ,last_update_date
     ,created_by
     ,last_updated_by
     ,last_update_login
     ,Object_Id
     ,Object_type
     ,Object_status
     ,Source_Code
     ,Source_Code_Id
     ,Response_Region
     ,Response_Country
     ,Business_Unit_Id
     ,Invalid_Reason
     ,weekend_Date
     ,Invalid_Responses
     ,landing_pad_hits
     ,survey_completed
     )
     SELECT
      bim_r_rrsn_weekly_facts_s.nextval
     ,sysdate
     ,sysdate
     ,-1
     ,-1
     ,-1
     ,Object_Id
     ,Object_type
     ,Object_status
     ,Source_Code
     ,Source_Code_Id
     ,Response_Region
     ,Response_Country
     ,Business_Unit_Id
     ,Invalid_Reason
     ,weekend_date
     ,Invalid_Responses
     ,Landing_Pad_hits
     ,Survey_Completed
     FROM  (
     SELECT
      Object_Id
     ,Object_type
     ,Object_status
     ,Source_Code
     ,Source_Code_Id
     ,Response_Region
     ,Response_Country
     ,Business_Unit_Id
     ,Invalid_Reason
     ,weekend_date
     ,sum(Invalid_Responses) 	Invalid_Responses
     ,sum(landing_pad_hits)	Landing_Pad_hits
     ,sum(survey_completed)	Survey_Completed
     FROM	bim_r_rrsn_daily_facts
     GROUP BY
      	Object_Id
     	,Object_type
     ,Object_status
     ,Source_Code
     ,Source_Code_Id
     ,Response_Region
     ,Response_Country
     ,Business_Unit_Id
     ,Invalid_Reason
     ,weekend_date
     );