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

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

Line: 26


/* Description: inserts qualifier data from staging table to          */
/*              jtf_tae_rpt_objs                                    */
/* Usage: public procedure , should be invoked when the staging table */        /*        is populated                                                */


PROCEDURE cleanup
as
v_statement varchar2(8000);

Line: 38


	select owner
	into l_ora_schemaname
	from all_synonyms
	where table_owner=
	(select owner from sys.all_objects where object_name='JTF_TAE_RPT_STAGING_OUT' and object_type='TABLE')
	and synonym_name='JTF_TAE_RPT_STAGING_OUT';

Line: 45


	/*delete from APPS.jtf_tae_rpt_staging_out
	where  session_id  in (select session_id from l_ora_schemaname ||'.icx_sessions
                        where disabled_flag <> 'N'); */

Line: 49


	v_statement:='delete from  '||l_ora_schemaname||'.jtf_tae_rpt_staging_out where session_id
	not in (select session_id from  '||l_ora_schemaname||'.icx_sessions)';

Line: 68


	select owner
	into l_ora_schemaname
	from all_synonyms
	where table_owner=
	(select owner from sys.all_objects where object_name='JTF_TAE_RPT_STAGING_OUT' and object_type='TABLE')
	and synonym_name='JTF_TAE_RPT_STAGING_OUT';

Line: 78


  /*delete from jtf_tae_rpt_staging_out
  where session_id = p_session_id */

  v_statement:='delete from  '||l_ora_schemaname||'.jtf_tae_rpt_staging_out where session_id = p_session_id';

Line: 85


   ' insert into '||l_ora_schemaname||'.jtf_tae_rpt_staging_out ' ||
   '   (TRANS_OBJECT_ID, ' ||
   '    TRANS_DETAIL_OBJECT_ID, ' ||
   '    SOURCE_ID,' ||
   '    TRANS_OBJECT_TYPE_ID, ' ||
   '    TERR_ID,' ||
   '    session_id, ' ||
   '    at_char01, ' ||
   '    at_char02, ' ||
   '    low_value_char ' ||
   '    ) ' ||
   'select ' ||
   '     ROW_NUMBER() OVER (ORDER BY ilv.low_value_char, ilv.org_name, ilv.org_address) ' ||
   '     AS PSEUDO_ROWNUM,         ' ||
   '     -999, ' ||
   '     -999, ' ||
   '     -999, ' ||
   '     :p_terr_id1, ' ||
   '     :p_session_id, ' ||
   '     ilv.org_name, ' ||
   '     ilv.org_address, ' ||
   '     ilv.low_value_char         ' ||
   ' FROM ( ' ||
   '       SELECT ' ||
   '         Q1012.low_value_char, Q1012.high_value_char ' ||
   '       , hzp.party_name org_name ' ||
   '       , hzl.address1 || '', '' || hzl.address2 || '', '' || ' ||
   '         hzl.city || '', '' || hzl.state ' ||
   '         || '', '' || hzl.postal_code org_address ' ||
   '       FROM  ' || l_ora_schemaname ||'.jtf_terr_qual_rules_mv Q1007R1 ' ||' , '||
				l_ora_schemaname ||'.jtf_terr_qual_rules_mv Q1012 ' || ' , '||
				l_ora_schemaname ||'.hz_locations hzl ' || ' , '||
				l_ora_schemaname ||'.hz_party_sites hzps ' || ' , '||
				l_ora_schemaname ||'.hz_parties hzp ' ||' , '||
				l_ora_schemaname ||'.as_accesses_all aaa ' || ' , '||
				l_ora_schemaname ||'.jtf_terr_rsc_all jtr ' ||
   '       WHERE ' ||
   '          ( ( hzl.postal_code = Q1007R1.low_value_char AND ' ||
   '               Q1007R1.comparison_operator = ''='' ) ' ||
   '               OR ' ||
   '             ( hzl.postal_code <= Q1007R1.high_value_char AND ' ||
   '               hzl.postal_code >= Q1007R1.low_value_char AND ' ||
   '               Q1007R1.comparison_operator = ''BETWEEN'' ) ' ||
   '           ) ' ||
   '       AND Q1007R1.qual_usg_id = -1007 ' ||
   '       AND Q1007R1.terr_id = jtr.terr_id ' ||
   '       AND UPPER(hzp.party_name) = Q1012.low_value_char ' ||
   '       AND Q1012.COMPARISON_OPERATOR = ''='' ' ||
   '       AND Q1012.qual_usg_id = -1012 ' ||
   '       AND Q1012.terr_id = jtr.terr_id ' ||
   '       AND hzl.location_id = hzps.location_id ' ||
   '       AND (hzps.status IN (''A'',''I'') OR hzps.status IS NULL ) ' ||
   '       AND hzps.party_id = hzp.party_id ' ||
   '       AND hzp.status = ''A'' ' ||
   '       AND hzp.party_id = aaa.customer_id ' ||
   '       AND aaa.salesforce_id = jtr.resource_id ' ||
   '       AND jtr.terr_id = :p_terr_id2 ' ||

   '       UNION  ' ||

   '       SELECT ' ||
   '         Q1012.low_value_char, NULL ' ||
   '       , hzp.party_name org_name ' ||
   '       , hzl.address1 || '', '' || hzl.address2 || '', '' || ' ||
   '         hzl.city || '', '' || hzl.state ' ||
   '         || '', '' || hzl.postal_code org_address ' ||
   '       FROM  ' || l_ora_schemaname ||'.jtf_terr_qual_rules_mv Q1007R1 ' || ' , '||
				l_ora_schemaname ||'.jtf_terr_cnr_qual_like_mv Q1012 ' || ' , ' ||
				l_ora_schemaname ||'.hz_locations hzl ' || ' , '||
				l_ora_schemaname ||'.hz_party_sites hzps ' || ' , '||
				l_ora_schemaname ||'.hz_parties hzp ' || ' , '||
				l_ora_schemaname ||'.as_accesses_all aaa ' || ' , '||
				l_ora_schemaname ||'.jtf_terr_rsc_all jtr ' ||
   '       WHERE ' ||
   '          ( ( hzl.postal_code = Q1007R1.low_value_char AND ' ||
   '               Q1007R1.comparison_operator = ''='' ) ' ||
   '               OR ' ||
   '             ( hzl.postal_code <= Q1007R1.high_value_char AND ' ||
   '               hzl.postal_code >= Q1007R1.low_value_char AND ' ||
   '               Q1007R1.comparison_operator = ''BETWEEN'' ) ' ||
   '           ) ' ||
   '       AND Q1007R1.qual_usg_id = -1007 ' ||
   '       AND Q1007R1.terr_id = jtr.terr_id ' ||
   '       AND UPPER(hzp.party_name) LIKE Q1012.low_value_char ' ||
   '       AND UPPER(SUBSTR(hzp.party_name, 1, 1)) = Q1012.first_char ' ||
   '       AND Q1012.qual_usg_id = -1012 ' ||
   '       AND Q1012.terr_id = jtr.terr_id ' ||
   '       AND hzl.location_id = hzps.location_id ' ||
   '       AND (hzps.status IN (''A'',''I'') OR hzps.status IS NULL ) ' ||
   '       AND hzps.party_id = hzp.party_id ' ||
   '       AND hzp.status = ''A'' ' ||
   '       AND hzp.party_id = aaa.customer_id ' ||
   '       AND aaa.salesforce_id = jtr.resource_id ' ||
   '       AND jtr.terr_id = :p_terr_id3 ' ||

   '       UNION  ' ||

  '       SELECT ' ||
   '         Q1012.low_value_char, NULL ' ||
   '       , hzp.party_name org_name ' ||
   '       , hzl.address1 || '', '' || hzl.address2 || '', '' || ' ||
   '         hzl.city || '', '' || hzl.state ' ||
   '         || '', '' || hzl.postal_code org_address ' ||
   '       FROM  ' || l_ora_schemaname ||'.jtf_terr_qual_rules_mv Q1007R1 ' || ' , '||
				l_ora_schemaname ||'.jtf_terr_cnr_qual_like_mv Q1012 ' || ' , '||
				l_ora_schemaname ||'.hz_locations hzl ' || ' , '||
				l_ora_schemaname ||'.hz_party_sites hzps ' || ' , '||
				l_ora_schemaname ||'.hz_parties hzp ' || ' , '||
				l_ora_schemaname ||'.as_accesses_all aaa ' || ' , '||
				l_ora_schemaname ||'.jtf_terr_rsc_all jtr ' ||
  '       WHERE ' ||
   '          ( ( hzl.postal_code = Q1007R1.low_value_char AND ' ||
   '               Q1007R1.comparison_operator = ''='' ) ' ||
   '               OR ' ||
   '             ( hzl.postal_code <= Q1007R1.high_value_char AND ' ||
   '               hzl.postal_code >= Q1007R1.low_value_char AND ' ||
   '               Q1007R1.comparison_operator = ''BETWEEN'' ) ' ||
   '           ) ' ||
   '       AND Q1007R1.qual_usg_id = -1007 ' ||
   '       AND Q1007R1.terr_id = jtr.terr_id ' ||
   '       AND UPPER(hzp.party_name) LIKE Q1012.low_value_char ' ||
   '       AND ''%'' = Q1012.first_char ' ||
   '       AND Q1012.qual_usg_id = -1012 ' ||
   '       AND Q1012.terr_id = jtr.terr_id ' ||
   '       AND hzl.location_id = hzps.location_id ' ||
   '       AND (hzps.status IN (''A'',''I'') OR hzps.status IS NULL ) ' ||
   '       AND hzps.party_id = hzp.party_id ' ||
   '       AND hzp.status = ''A'' ' ||
   '       AND hzp.party_id = aaa.customer_id ' ||
   '       AND aaa.salesforce_id = jtr.resource_id ' ||
   '       AND jtr.terr_id = :p_terr_id4 ' ||

   '       UNION  ' ||

   '       SELECT ' ||
   '         Q1012.low_value_char, Q1012.high_value_char ' ||
   '       , hzp.party_name org_name ' ||
   '       , hzl.address1 || '', '' || hzl.address2 || '', '' || ' ||
   '         hzl.city || '', '' || hzl.state ' ||
   '         || '', '' || hzl.postal_code org_address ' ||
   '       FROM  ' || l_ora_schemaname ||'.jtf_terr_qual_rules_mv Q1007R1 ' || ' , '||
				l_ora_schemaname ||'.jtf_terr_cnr_qual_btwn_mv Q1012 ' || ' , '||
				l_ora_schemaname ||'.hz_locations hzl ' || ' , '||
				l_ora_schemaname ||'.hz_party_sites hzps ' || ' , '||
				l_ora_schemaname ||'.hz_parties hzp ' || ' , '||
				l_ora_schemaname ||'.as_accesses_all aaa ' || ' , '||
				l_ora_schemaname ||'.jtf_terr_rsc_all jtr ' ||
   '       WHERE ' ||
   '          ( ( hzl.postal_code = Q1007R1.low_value_char AND ' ||
   '               Q1007R1.comparison_operator = ''='' ) ' ||
   '               OR ' ||
   '             ( hzl.postal_code <= Q1007R1.high_value_char AND ' ||
   '               hzl.postal_code >= Q1007R1.low_value_char AND ' ||
   '               Q1007R1.comparison_operator = ''BETWEEN'' ) ' ||
   '           ) ' ||
   '       AND Q1007R1.qual_usg_id = -1007 ' ||
   '       AND Q1007R1.terr_id = jtr.terr_id ' ||
   '       AND UPPER(hzp.party_name) BETWEEN Q1012.low_value_char AND Q1012.high_value_char ' ||
   '       AND Q1012.qual_usg_id = -1012  ' ||
   '       AND Q1012.terr_id = jtr.terr_id ' ||
   '       AND hzl.location_id = hzps.location_id ' ||
   '       AND (hzps.status IN (''A'',''I'') OR hzps.status IS NULL ) ' ||
   '       AND hzps.party_id = hzp.party_id ' ||
   '       AND hzp.status = ''A'' ' ||
   '       AND hzp.party_id = aaa.customer_id ' ||
   '       AND aaa.salesforce_id = jtr.resource_id ' ||
   '       AND jtr.terr_id = :p_terr_id5 ' ||
   '     ) ilv ';

Line: 283


	select owner
	into l_ora_schemaname
	from all_synonyms
	where table_owner=
	(select owner from sys.all_objects where object_name='JTF_TAE_RPT_STAGING_OUT' and object_type='TABLE')
	and synonym_name='JTF_TAE_RPT_STAGING_OUT';

Line: 290


	/*delete from APPS.jtf_tae_rpt_staging_out
	where session_id = p_session_id; */

Line: 293


	v_statement:='delete from  '||l_ora_schemaname||'.jtf_tae_rpt_staging_out where session_id = p_session_id';

Line: 297


  insert into l_ora_schemaname ||'.jtf_tae_rpt_staging_out
      (TRANS_OBJECT_ID,
       TRANS_DETAIL_OBJECT_ID,
       SOURCE_ID,
       TRANS_OBJECT_TYPE_ID,
       TERR_ID,
       terr_name,
       terr_rank,
       session_id)
  values(-999,
        -999,
        -999,
        -999,
        -999,
        p_resource_id,
        p_group_id,
        p_session_id);

Line: 318


  l_dyn_str:='insert into '||l_ora_schemaname||'.jtf_tae_rpt_staging_out
      (TRANS_OBJECT_ID,
       TRANS_DETAIL_OBJECT_ID,
       SOURCE_ID,
       TRANS_OBJECT_TYPE_ID,
       TERR_ID,
       terr_name,
       terr_rank,
       session_id)
  select distinct -999,
        -999,
        -999,
        -999,
        jta.terr_id,
        jta.name,
        jta.rank,
        p_session_id
  from jtf_terr_all jta,
       jtf_terr_rsc_all jtra
  where jta.terr_id = jtra.terr_id
  and (jtra.resource_id = p_resource_id or p_resource_id = -999)
  and (jtra.group_id = p_group_id or p_group_id =-999)
  AND NOT EXISTS (
       SELECT jt.terr_id
       FROM jtf_terr_all jt
       WHERE ( ( NVL(jt.end_date_active, ' || lp_sysdate ||') <= NVL(' ||lp_active_date ||', '|| lp_sysdate ||'
	   ) ) OR ( NVL(jt.start_date_active, ' ||lp_sysdate ||') > NVL(' ||lp_active_date ||', '|| lp_sysdate ||' ) ) )
       CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
       START WITH jt.terr_id = jta.terr_id )';