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.EDR_RULE_TEMP SQL Statements

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

Line: 44


/* Obtain rule details for certain trasaction, and insert into temp tables */
PROCEDURE GET_DETAILS_TRANS ( p_trans_id  IN  VARCHAR2, p_input_var IN VARCHAR2
                          	) IS
  l_ssnid	NUMBER;

Line: 73


  select USERENV('SESSIONID') into l_ssnid from dual;

Line: 76


  select distinct application_id, application_name into l_apps_id, l_apps_name
  from ame_calling_apps
  where transaction_type_id = p_trans_id
  --Bug 4652277: Start
  --and end_Date is null;

Line: 88


  select distinct rule_id, start_date, end_date bulk collect
  into l_rule_ids, l_date_0s, l_date_1s   from ame_rule_usages
  --Bug 4652277: Start
  --where item_id = l_apps_id and (end_date is null OR end_date > sysdate);

Line: 102


    select count(*) into l_count_num from ame_rules where rule_id = l_rule_ids(ith)
    and description in ( select distinct rule_name from edr_amerule_input_var
    where ame_trans_name = l_apps_name and input_name = p_input_var )
    --Bug 4652277: Start
    -- and (end_date is null or end_date > sysdate);

Line: 125


    insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name,
	rule_type, appr_type, appr_desc, default_var, start_date, end_date ) values
	( l_ssnid, p_trans_id, l_rule_ids(ith), l_rule_desc, l_rule_type, l_appr_type,
	l_appr_desc, l_deft_use, l_date_0s(ith), l_date_1s(ith) );

Line: 131


	insert into edr_rule_condition_temp ( session_id, transaction_type_id, rule_id,
		condition_id, condition_desc ) values ( l_ssnid, p_trans_id, l_rule_ids(ith),
		l_cond_ids(jth), l_cond_desc(jth) );

Line: 138


      select distinct description into l_rule_name from ame_rules where rule_id = l_rule_ids(ith);

Line: 140


      insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name,
		default_var, start_date, end_date)	values ( l_ssnid, p_trans_id, l_rule_ids(ith),
		l_rule_name, l_deft_use, l_date_0s(ith), l_date_1s(ith) );

Line: 143


/* insert into edr_rule_condition_temp ( session_id, transaction_type_id, rule_id,
		condition_id, condition_desc )
		values ( l_ssnid, p_trans_id, l_rule_ids(ith), null, null ); */

Line: 180


  select USERENV('SESSIONID') into l_ssnid from dual;

Line: 183


  select transaction_type_id, application_id into l_trans_id, l_apps_id
  from ame_calling_apps
  where application_name = p_trans_name
  --Bug 4652277: Start
  --and end_Date is null;

Line: 194


  select distinct rule_id into l_rule_id from ame_rules where description = p_rule_name
  and rule_id in ( select distinct rule_id from ame_rule_usages where item_id = l_apps_id )
  --Bug 4652277: Start
  --and (end_date is null or end_date > sysdate);

Line: 203


  select start_date, end_date into l_date_frm, l_date_end
  from   ame_rule_usages
  where rule_id = l_rule_id
  --Bug 4652277: Start
  --and (end_date is null or end_date > sysdate);

Line: 223


    insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name,
	rule_type, appr_type, appr_desc, default_var, start_date, end_date ) values
	( l_ssnid, l_trans_id, l_rule_id, l_rule_desc, l_rule_type, l_appr_type,
	l_appr_desc, 'N', l_date_frm, l_date_end );

Line: 229


    insert into edr_rule_condition_temp ( session_id, transaction_type_id, rule_id, condition_id,
	condition_desc ) values ( l_ssnid, l_trans_id, l_rule_id, l_cond_ids(jth), l_cond_desc(jth) );

Line: 235


      insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name, default_var,
		start_date, end_date) values
		( l_ssnid, l_trans_id, l_rule_id, p_rule_name, 'N', l_date_frm, l_date_end );

Line: 253


  select USERENV('SESSIONID') into l_ssnid from dual;

Line: 255


  delete edr_rule_detail_temp where transaction_type_id = p_trans_id and SESSION_ID = l_ssnid;

Line: 257


  delete edr_rule_condition_temp where transaction_type_id = p_trans_id and SESSION_ID = l_ssnid;

Line: 268


  select USERENV('SESSIONID') into l_ssnid from dual;

Line: 270


  delete edr_rule_detail_temp where transaction_type_id = p_trans_id
		and RULE_ID = p_rule_id and SESSION_ID = l_ssnid;

Line: 273


  delete edr_rule_condition_temp where transaction_type_id = p_trans_id
		and RULE_ID = p_rule_id and SESSION_ID = l_ssnid;

Line: 307


  select substr( p_trans_var, 1, instr(p_trans_var,'-')-1 ) into p_trans_id from dual;

Line: 308


  select substr( p_trans_var, 1+instr(p_trans_var,'-') ) into p_input_var from dual;

Line: 310


  select USERENV('SESSIONID') into l_ssnid from dual;

Line: 313


  select distinct application_id, application_name into l_apps_id, l_apps_name
  from ame_calling_apps
  where transaction_type_id = p_trans_id
  --Bug 4652277: Start
  --AND end_date is null ;

Line: 322


  select distinct rule_id, start_date, end_date bulk collect
  into l_rule_ids, l_date_0s, l_date_1s   from ame_rule_usages
  where item_id = l_apps_id
  --Bug 4652277: Start
  --and (end_date is null OR end_date > sysdate);

Line: 334


    select count(*) into l_count_num from ame_rules where rule_id = l_rule_ids(ith)
    and description in ( select distinct rule_name from edr_amerule_input_var
      where ame_trans_name = l_apps_name and input_name = p_input_var );

Line: 352


    insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name,
	rule_type, appr_type, appr_desc, default_var, start_date, end_date ) values
	( l_ssnid, p_trans_id, l_rule_ids(ith), l_rule_desc, l_rule_type, l_appr_type,
	l_appr_desc, l_deft_use, l_date_0s(ith), l_date_1s(ith) );

Line: 358


	insert into edr_rule_condition_temp ( session_id, transaction_type_id, rule_id, condition_id,
		condition_desc ) values
		( l_ssnid, p_trans_id, l_rule_ids(ith), l_cond_ids(jth), l_cond_desc(jth) );

Line: 365


      select distinct description into l_rule_name from ame_rules where rule_id = l_rule_ids(ith);

Line: 366


      insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name, default_var,
		start_date, end_date)	values ( l_ssnid, p_trans_id, l_rule_ids(ith),
		l_rule_name, l_deft_use, l_date_0s(ith), l_date_1s(ith) );

Line: 369


/* insert into edr_rule_condition_temp ( session_id, transaction_type_id, rule_id, condition_id,
		condition_desc ) values ( l_ssnid, p_trans_id, l_rule_ids(ith), null, null ); */

Line: 407


  select substr( p_trans_rule, 1, instr(p_trans_rule,'-')-1 ) into p_trans_name from dual;

Line: 408


  select instr(p_trans_rule, '-', 1+instr(p_trans_rule,'-')) into l_position from dual;

Line: 411


  select TO_NUMBER( substr( p_trans_rule, 1+instr(p_trans_rule,'-'),
		l_position - instr(p_trans_rule,'-') - 1 ),'999999999999.999999'  )  into p_rule_id from dual;

Line: 415


  select USERENV('SESSIONID') into l_ssnid from dual;

Line: 418


  select transaction_type_id, application_id into l_trans_id, l_apps_id
  from ame_calling_apps
  where application_name = p_trans_name
  --Bug 4652277: Start
  --and end_date is null;

Line: 430


  select distinct description into p_rule_name from ame_rules
  where  rule_id = l_rule_id
  --Bug 4652277: Start
  --and (end_date is null or end_date > sysdate);

Line: 439


  select start_date, end_date into l_date_frm, l_date_end
  from   ame_rule_usages where rule_id = l_rule_id and item_id = l_apps_id
  --Bug 4652277: Start
  --and    (end_date is null OR end_date > sysdate);

Line: 455


    insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name,
	rule_type, appr_type, appr_desc, default_var, start_date, end_date ) values
	( l_ssnid, l_trans_id, l_rule_id, l_rule_desc, l_rule_type, l_appr_type,
	l_appr_desc, 'N', l_date_frm, l_date_end );

Line: 461


    insert into edr_rule_condition_temp ( session_id, transaction_type_id, rule_id, condition_id,
	condition_desc ) values ( l_ssnid, l_trans_id, l_rule_id, l_cond_ids(jth), l_cond_desc(jth) );

Line: 467


      insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name,
		default_var, start_date, end_date) values
		( l_ssnid, l_trans_id, l_rule_id, p_rule_name, 'N', l_date_frm, l_date_end );

Line: 512


  select distinct ame_trans_id, input_name into p_trans_id, p_input_var
  from edr_ametran_input_var where tran_config_id = TO_NUMBER(p_trans_config_id,'999999999999.999999');

Line: 516


  select USERENV('SESSIONID') into l_ssnid from dual;

Line: 518


  select distinct application_id, application_name into l_apps_id, l_apps_name
  from ame_calling_apps
  where transaction_type_id = p_trans_id
  --Bug 4652277: Start
  --and end_date is null;

Line: 526


  select distinct rule_id, start_date, end_date bulk collect
  into l_rule_ids, l_date_0s, l_date_1s   from ame_rule_usages
  where item_id = l_apps_id
  --Bug 4652277: Start
  --and (end_date is null OR end_date > sysdate);

Line: 540


    select count(*) into l_count_num from ame_rules where rule_id = l_rule_ids(ith)
    and description in ( select distinct rule_name from edr_amerule_input_var
      where ame_trans_name in (select distinct application_name from ame_calling_apps
        where transaction_type_id = p_trans_id) and input_name = p_input_var );

Line: 560


    insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name,
	rule_type, appr_type, appr_desc, default_var, start_date, end_date ) values
	( l_ssnid, p_trans_id, l_rule_ids(ith), l_rule_desc, l_rule_type, l_appr_type,
	l_appr_desc, l_deft_use, l_date_0s(ith), l_date_1s(ith) );

Line: 566


	insert into edr_rule_condition_temp ( session_id, transaction_type_id, rule_id, condition_id,
		condition_desc ) values
		( l_ssnid, p_trans_id, l_rule_ids(ith), l_cond_ids(jth), l_cond_desc(jth) );

Line: 574


      select distinct description into l_rule_name from ame_rules
      where  rule_id = l_rule_ids(ith) and (end_date is null or end_date > sysdate);

Line: 577


      insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name, default_var,
		start_date, end_date)	values ( l_ssnid, p_trans_id, l_rule_ids(ith),
		l_rule_name, l_deft_use, l_date_0s(ith), l_date_1s(ith) );

Line: 580


/* insert into edr_rule_condition_temp ( session_id, transaction_type_id, rule_id, condition_id,
		condition_desc ) values ( l_ssnid, p_trans_id, l_rule_ids(ith), null, null ); */

Line: 621


  select distinct ame_trans_name, rule_id into p_trans_name, p_rule_id
  from edr_amerule_input_var where rule_config_id = TO_NUMBER(p_rule_config_id,'999999999999.999999');

Line: 625


  select USERENV('SESSIONID') into l_ssnid from dual;

Line: 628


  select transaction_type_id, application_id into l_trans_id, l_apps_id
  from ame_calling_apps
  where application_name = p_trans_name
  --Bug 4652277: Start
  --and end_date is null;

Line: 638


  select distinct description into p_rule_name from ame_rules
  where  rule_id = l_rule_id
  --Bug 4652277: Start
  --and (end_date is null or end_date > sysdate);

Line: 647


  select distinct start_date, end_date into l_date_frm, l_date_end
  from   ame_rule_usages where rule_id = l_rule_id and item_id = l_apps_id
  --Bug 4652277: Start
  --and    (end_date is null OR end_date > sysdate);

Line: 663


    insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name,
	rule_type, appr_type, appr_desc, default_var, start_date, end_date ) values
	( l_ssnid, l_trans_id, l_rule_id, l_rule_desc, l_rule_type, l_appr_type,
	l_appr_desc, 'N', l_date_frm, l_date_end );

Line: 669


    insert into edr_rule_condition_temp ( session_id, transaction_type_id, rule_id, condition_id,
	condition_desc ) values ( l_ssnid, l_trans_id, l_rule_id, l_cond_ids(jth), l_cond_desc(jth) );

Line: 675


      insert into edr_rule_detail_temp ( session_id, transaction_type_id, rule_id, rule_name,
		default_var, start_date, end_date) values
		( l_ssnid, l_trans_id, l_rule_id, p_rule_name, 'N', l_date_frm, l_date_end );

Line: 723


  SELECT USERENV('SESSIONID') INTO L_SSNID FROM DUAL;

Line: 726


  SELECT DISTINCT APPLICATION_ID, APPLICATION_NAME INTO L_APPS_ID, L_APPS_NAME
  FROM  AME_CALLING_APPS_VL
  WHERE TRANSACTION_TYPE_ID = P_TRANSACTION_ID
  AND   SYSDATE BETWEEN START_DATE AND NVL(END_DATE, SYSDATE);

Line: 732


  SELECT DISTINCT RULE_ID, START_DATE, END_DATE BULK COLLECT
  INTO   L_RULE_IDS, L_DATE_0S, L_DATE_1S   FROM AME_RULE_USAGES
  WHERE  ITEM_ID = L_APPS_ID
  AND    SYSDATE <= NVL(END_DATE,SYSDATE);

Line: 743


    SELECT COUNT(*)
    INTO   L_COUNT_NUM
    FROM   AME_RULES_VL AME_RULES,
           EDR_AMERULE_INPUT_VAR RULE_VAR
    WHERE  AME_RULES.RULE_ID = L_RULE_IDS(ith)
    AND    AME_RULES.RULE_ID = RULE_VAR.RULE_ID
    AND    RULE_VAR.AME_TRANS_ID = P_TRANSACTION_ID
    AND    RULE_VAR.INPUT_NAME = P_VARIABLE_NAME
    AND    SYSDATE <= NVL(AME_RULES.END_DATE,SYSDATE);

Line: 770


    INSERT INTO EDR_RULE_DETAIL_TEMP (SESSION_ID, TRANSACTION_TYPE_ID, RULE_ID, RULE_NAME,
	                              RULE_TYPE, APPR_TYPE, APPR_DESC, DEFAULT_VAR, START_DATE, END_DATE )
	   VALUES (L_SSNID, P_TRANSACTION_ID, L_RULE_IDS(ith), L_RULE_DESC, L_RULE_TYPE, L_APPR_TYPE,
	           L_APPR_DESC, L_DEFT_USE, L_DATE_0S(ith), L_DATE_1S(ith) );

Line: 776


      INSERT INTO EDR_RULE_CONDITION_TEMP( SESSION_ID, TRANSACTION_TYPE_ID, RULE_ID, CONDITION_ID,CONDITION_DESC )
      VALUES ( L_SSNID, P_TRANSACTION_ID, L_RULE_IDS(ith), L_COND_IDS(jth), L_COND_DESC(jth) );

Line: 783


      SELECT DISTINCT DESCRIPTION
      INTO   L_RULE_NAME FROM AME_RULES_VL
      WHERE  RULE_ID = L_RULE_IDS(ith) AND (END_DATE IS NULL OR END_DATE > SYSDATE);

Line: 787


      INSERT INTO EDR_RULE_DETAIL_TEMP ( SESSION_ID, TRANSACTION_TYPE_ID, RULE_ID, RULE_NAME,
                                         DEFAULT_VAR, START_DATE, END_DATE)
      VALUES ( L_SSNID, P_TRANSACTION_ID, L_RULE_IDS(ith),L_RULE_NAME, L_DEFT_USE, L_DATE_0S(ith), L_DATE_1S(ith));

Line: 830


  SELECT USERENV('SESSIONID') INTO L_SSNID FROM DUAL;

Line: 832


  SELECT APPLICATION_ID INTO L_APPS_ID
  FROM   AME_CALLING_APPS_VL
  WHERE  TRANSACTION_TYPE_ID = P_TRANSACTION_ID
  AND    SYSDATE BETWEEN START_DATE AND NVL(END_DATE, SYSDATE);

Line: 838


  SELECT DISTINCT DESCRIPTION
  INTO   L_RULE_NAME FROM AME_RULES_VL
  WHERE  RULE_ID = P_RULE_ID
  AND    SYSDATE <= NVL(END_DATE,SYSDATE);

Line: 844


  SELECT DISTINCT START_DATE, END_DATE INTO L_DATE_FRM, L_DATE_END
  FROM   AME_RULE_USAGES
  WHERE  RULE_ID = P_RULE_ID
  AND    ITEM_ID = L_APPS_ID
  AND    SYSDATE <= NVL(END_DATE,SYSDATE);

Line: 863


    INSERT INTO EDR_RULE_DETAIL_TEMP (SESSION_ID, TRANSACTION_TYPE_ID, RULE_ID, RULE_NAME, RULE_TYPE, APPR_TYPE,
                                      APPR_DESC, DEFAULT_VAR, START_DATE, END_DATE )
    VALUES (L_SSNID, P_TRANSACTION_ID, P_RULE_ID, L_RULE_DESC, L_RULE_TYPE, L_APPR_TYPE,
	    L_APPR_DESC, 'N', L_DATE_FRM, L_DATE_END );

Line: 869


      INSERT INTO EDR_RULE_CONDITION_TEMP ( SESSION_ID, TRANSACTION_TYPE_ID,
                                            RULE_ID, CONDITION_ID,CONDITION_DESC)
      VALUES (L_SSNID, P_TRANSACTION_ID, P_RULE_ID, L_COND_IDS(JTH), L_COND_DESC(JTH) );

Line: 877


      INSERT INTO EDR_RULE_DETAIL_TEMP (SESSION_ID, TRANSACTION_TYPE_ID, RULE_ID, RULE_NAME,
	 	                        DEFAULT_VAR, START_DATE, END_DATE)
      VALUES(L_SSNID, P_TRANSACTION_ID, P_RULE_ID, L_RULE_NAME, 'N', L_DATE_FRM, L_DATE_END );

Line: 917


  DELETE FROM EDR_AMERULE_INPUT_VAR RULE_VAR
  WHERE  RULE_VAR.AME_TRANS_ID = P_TRANSACTION_ID
  AND   RULE_VAR.RULE_ID NOT IN (SELECT AME_USAGES.RULE_ID
                                 FROM   AME_CALLING_APPS_VL AME_APPS,
                                     AME_RULE_USAGES AME_USAGES
                                 WHERE  AME_APPS.TRANSACTION_TYPE_ID = P_TRANSACTION_ID
                                 AND    AME_APPS.APPLICATION_ID = AME_USAGES.ITEM_ID
			         AND    SYSDATE <= NVL(AME_USAGES.END_DATE,SYSDATE));

Line: 953


  SELECT COUNT(*) INTO L_COUNT FROM EDR_AMERULE_INPUT_VAR RULE_VAR
  WHERE  RULE_VAR.AME_TRANS_ID = P_TRANSACTION_ID
  AND   RULE_VAR.RULE_ID NOT IN (SELECT AME_USAGES.RULE_ID
                                 FROM   AME_CALLING_APPS_VL AME_APPS,
                                     AME_RULE_USAGES AME_USAGES
                                 WHERE  AME_APPS.TRANSACTION_TYPE_ID = P_TRANSACTION_ID
                                 AND    AME_APPS.APPLICATION_ID = AME_USAGES.ITEM_ID
			         AND    SYSDATE <= NVL(AME_USAGES.END_DATE,SYSDATE));