[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT jqu.qual_usg_id, jqu.rule1
FROM jtf_qual_usgs_all jqu
, jtf_qual_type_usgs jqtu
, jtf_qual_type_denorm_v v
WHERE --jqu.enabled_flag = 'Y'
jqu.org_id = -3113
AND jqu.qual_type_usg_id = jqtu.qual_type_usg_id
AND jqtu.source_id = lp_source_id
AND jqtu.qual_type_id = v.related_id
AND jqu.rule1 IS NOT NULL
AND v.qual_type_id = lp_qual_type_id
AND EXISTS ( SELECT jtq.terr_id
FROM jtf_terr_qtype_usgs_all jtqu
, jtf_terr_all jt
, jtf_terr_qual_all jtq
, jtf_qual_type_usgs jqtu
WHERE NVL(jt.end_date_active, lp_sysdate + 1) > lp_sysdate
AND NVL(jt.start_date_active, lp_sysdate - 1) < lp_sysdate
AND jtqu.terr_id = jt.terr_id
AND jqtu.qual_type_usg_id = jtqu.qual_type_usg_id
AND jqtu.qual_type_id = lp_qual_type_id
AND jtqu.terr_id = jtq.terr_id
AND jtq.qual_usg_id = jqu.qual_usg_id )
UNION
SELECT jqu.qual_usg_id, jqu.rule2
FROM jtf_qual_usgs_all jqu
, jtf_qual_type_usgs_all jqtu
, jtf_qual_type_denorm_v v
WHERE --jqu.enabled_flag = 'Y'
jqu.org_id = -3113
AND jqu.qual_type_usg_id = jqtu.qual_type_usg_id
AND jqtu.source_id = lp_source_id
AND jqtu.qual_type_id = v.related_id
AND jqu.rule2 IS NOT NULL
AND v.qual_type_id = lp_qual_type_id
AND EXISTS ( SELECT jtq.terr_id
FROM jtf_terr_values_all jtv
, jtf_terr_qtype_usgs_all jtqu
, jtf_terr_all jt
, jtf_terr_qual_all jtq
, jtf_qual_type_usgs jqtu
WHERE NVL(jt.end_date_active, lp_sysdate + 1) > lp_sysdate
AND NVL(jt.start_date_active, lp_sysdate - 1) < lp_sysdate
AND jtqu.terr_id = jt.terr_id
AND jtv.terr_qual_id = jtq.terr_qual_id
AND jtv.comparison_operator IN ('<>', 'NOT LIKE', 'NOT BETWEEN')
AND jqtu.qual_type_usg_id = jtqu.qual_type_usg_id
AND jqtu.qual_type_id = lp_qual_type_id
AND jtqu.terr_id = jtq.terr_id
AND jtq.qual_usg_id = jqu.qual_usg_id ) ;
Add_To_PackageTable (' SELECT /*+ ORDERED ' );
Add_To_PackageTable (' DELETE FROM jtf_terr_results_GT_MT; ');
Add_To_PackageTable (' INSERT INTO jtf_terr_results_GT_MT jtr ');
Add_To_PackageTable (' SELECT /*+ ORDERED */');
Add_To_PackageTable (' SELECT ');
Add_To_PackageTable (' SELECT ');
Add_To_PackageTable (' '' SELECT /*+ ORDERED INDEX (jtra JTF_TERR_RSC_N1) '' || ' );
Add_To_PackageTable (' '' ( SELECT '' || ' );
Add_To_PackageTable (' '' ( SELECT '' || ' );
Add_To_PackageTable (' '' WHERE o.TERR_RANK <= (SELECT NVL(t.num_winners, 1) FROM jtf_terr_all t WHERE t.terr_id = o.top_level_terr_id) '' || ' );
Add_To_PackageTable (' '' SELECT column_value terr_id '' || ' );
Add_To_PackageTable (' '' , TABLE ( CAST ( MULTISET ( SELECT ilv1.terr_id '' || ' );
Add_To_PackageTable (' '' FROM ( SELECT /*+ INDEX (j JTF_TERR_RESULTS_U1) */ '' || ' );
Add_To_PackageTable (' '' (ROWNUM <= (SELECT NVL(t.num_winners, 1) FROM jtf_terr_all t WHERE t.terr_id = ilv1.top_level_terr_id) ) '' || ' );
SELECT j.name
INTO l_qualifier_type
FROM jtf_qual_types j
WHERE j.qual_type_id = p_qualifier_type_id;
SELECT COUNT(*)
INTO num_of_terr
FROM jtf_terr_qtype_usgs_all jtqu
, jtf_terr_usgs_all jtu
, jtf_terr_all jt1
, jtf_qual_type_usgs jqtu
WHERE jtqu.terr_id = jt1.terr_id
AND jqtu.qual_type_usg_id = jtqu.qual_type_usg_id
AND jqtu.qual_type_id = p_qualifier_type_id
AND jtu.source_id = p_source_id
AND jtu.terr_id = jt1.terr_id
AND NVL(jt1.end_date_active, lp_sysdate) >= lp_sysdate
AND jt1.start_date_active <= lp_sysdate
AND EXISTS (
SELECT jtrs.terr_rsc_id
FROM jtf_terr_rsc_all jtrs
WHERE NVL(jtrs.end_date_active, lp_sysdate) >= lp_sysdate
AND NVL(jtrs.start_date_active, lp_sysdate) <= lp_sysdate
AND jtrs.terr_id = jt1.terr_id )
AND NOT EXISTS (
SELECT jt.terr_id
FROM jtf_terr_all jt
WHERE NVL(jt.end_date_active, lp_sysdate + 1) < lp_sysdate
CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
START WITH jt.terr_id = jt1.terr_id)
AND jqtu.qual_type_id <> -1001
AND rownum < 2;
SELECT COUNT(*)
INTO num_of_terr
FROM jtf_terr_qtype_usgs_all jtqu
, jtf_terr_usgs_all jtu
, jtf_terr_all jt1
, jtf_qual_type_usgs jqtu
WHERE jtqu.terr_id = jt1.terr_id
AND jqtu.qual_type_usg_id = jtqu.qual_type_usg_id
AND jqtu.qual_type_id = p_qualifier_type_id
AND jtu.source_id = p_source_id
AND jtu.terr_id = jt1.terr_id
AND NVL(jt1.end_date_active, lp_sysdate) >= lp_sysdate
AND jt1.start_date_active <= lp_sysdate
AND EXISTS (
SELECT jtrs.terr_rsc_id
FROM jtf_terr_rsc_all jtrs
WHERE NVL(jtrs.end_date_active, lp_sysdate) >= lp_sysdate
AND NVL(jtrs.start_date_active, lp_sysdate) <= lp_sysdate
AND jtrs.terr_id = jt1.terr_id )
AND NOT EXISTS (
SELECT jt.terr_id
FROM jtf_terr_all jt
WHERE NVL(jt.end_date_active, lp_sysdate + 1) < lp_sysdate
CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1
START WITH jt.terr_id = jt1.terr_id)
AND jqtu.qual_type_id <> -1001;
' SELECT uo.object_name, uo.object_type, uo.created, uo.last_ddl_time, uo.timestamp, uo.status' ||
' FROM user_objects uo' ||
' WHERE uo.object_type = ''PACKAGE BODY'' AND uo.object_name = :b1 and rownum < 2';