APPS.JTF_TAE_SQL_LIB_PVT SQL Statements

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

Line: 57

   SELECT u.oracle_username
    INTO l_ora_username
    FROM fnd_product_installations i, fnd_application a, fnd_oracle_userid u
    WHERE a.application_short_name = 'JTF'
      AND a.application_id = i.application_id
      AND u.oracle_id = i.oracle_id;

Line: 131

** Function to return Static SELECT columns
*********************************************************/
FUNCTION add_SELECT_cols( p_new_mode_fetch  CHAR )
RETURN VARCHAR2 AS

   lx_SELECT_clause     VARCHAR2(2000) := NULL;

Line: 143

      RETURN lx_SELECT_CLAUSE ||
            G_INDENT || '       trans_object_id ' || g_newline ||
            G_INDENT || '     , trans_detail_object_id ' || g_newline ||
            -- eihsu: 06/19/2003 worker_id
            G_INDENT || '     , worker_id ' || g_newline ||
            G_INDENT || '     , header_id1 ' || g_newline ||
            G_INDENT || '     , header_id2 ' || g_newline ||
            G_INDENT || '     , P_SOURCE_ID ' || g_newline ||
            G_INDENT || '     , P_TRANS_OBJECT_TYPE_ID ' || g_newline ||
            G_INDENT || '     , L_SYSDATE ' || g_newline ||
            G_INDENT || '     , L_USER_ID ' || g_newline ||
            G_INDENT || '     , L_SYSDATE ' || g_newline ||
            G_INDENT || '     , L_USER_ID ' || g_newline ||
            G_INDENT || '     , L_USER_ID ' || g_newline ||
            G_INDENT || '     , L_REQUEST_ID ' || g_newline ||
            G_INDENT || '     , L_PROGRAM_APPL_ID ' || g_newline ||
            G_INDENT || '     , L_PROGRAM_ID ' || g_newline ||
            G_INDENT || '     , L_SYSDATE ' || g_newline ||
            G_INDENT || '     , ILV.terr_id ' || g_newline ||
            G_INDENT || '     , ILV.absolute_rank ' || g_newline ||
            G_INDENT || '     , ILV.top_level_terr_id '|| g_newline ||
            G_INDENT || '     , ILV.num_winners ' || g_newline ||
            G_INDENT || '     , ILV.org_id ' || g_newline;

Line: 169

      RETURN lx_SELECT_CLAUSE ||
            G_INDENT || '       A.TRANS_OBJECT_ID' || g_newline ||
            G_INDENT || '       , A.TRANS_DETAIL_OBJECT_ID' || g_newline ||
            G_INDENT || '       , A.HEADER_ID1' || g_newline ||
            G_INDENT || '       , A.HEADER_ID2' || g_newline ||
            G_INDENT || '       , p_source_id' || g_newline ||
            G_INDENT || '       , p_trans_object_type_id' || g_newline ||
            G_INDENT || '       , l_sysdate' || g_newline ||
            G_INDENT || '       , L_USER_ID' || g_newline ||
            G_INDENT || '       , l_sysdate' || g_newline ||
            G_INDENT || '       , L_USER_ID' || g_newline ||
            G_INDENT || '       , L_USER_ID' || g_newline ||
            G_INDENT || '       , L_REQUEST_ID' || g_newline ||
            G_INDENT || '       , L_PROGRAM_APPL_ID' || g_newline ||
            G_INDENT || '       , L_PROGRAM_ID' || g_newline ||
            G_INDENT || '       , l_sysdate' || g_newline ||
            G_INDENT || '       , A.SQUAL_FC01' || g_newline ||
            G_INDENT || '       , A.SQUAL_FC02' || g_newline ||
            G_INDENT || '       , A.SQUAL_FC03' || g_newline ||
            G_INDENT || '       , A.SQUAL_FC04' || g_newline ||
            G_INDENT || '       , A.SQUAL_FC05' || g_newline ||
            G_INDENT || '       , A.SQUAL_CURC01' || g_newline ||
            G_INDENT || '       , A.SQUAL_CURC02' || g_newline ||
            G_INDENT || '       , A.SQUAL_CURC03' || g_newline ||
            G_INDENT || '       , A.SQUAL_CURC04' || g_newline ||
            G_INDENT || '       , A.SQUAL_CURC05' || g_newline ||
            G_INDENT || '       , A.SQUAL_CURC06' || g_newline ||
            G_INDENT || '       , A.SQUAL_CURC07' || g_newline ||
            G_INDENT || '       , A.SQUAL_CURC08' || g_newline ||
            G_INDENT || '       , A.SQUAL_CURC09' || g_newline ||
            G_INDENT || '       , A.SQUAL_CURC10' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR01' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR02' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR03' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR04' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR05' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR06' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR07' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR08' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR09' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR10' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR11' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR12' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR13' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR14' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR15' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR16' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR17' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR18' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR19' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR20' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR21' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR22' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR23' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR24' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR25' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR26' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR27' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR28' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR30' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR31' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR32' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR33' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR34' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR35' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR36' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR37' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR38' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR39' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR40' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR41' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR42' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR43' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR44' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR45' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR46' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR47' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR48' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR49' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR50' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR51' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR52' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR53' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR54' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR55' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR56' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR57' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR58' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR59' || g_newline ||
            G_INDENT || '       , A.SQUAL_CHAR60' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM01' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM02' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM03' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM04' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM05' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM06' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM07' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM08' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM09' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM10' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM11' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM12' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM13' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM14' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM15' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM16' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM17' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM18' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM19' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM20' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM21' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM22' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM23' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM24' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM25' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM26' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM27' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM28' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM29' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM30' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM31' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM32' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM33' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM34' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM35' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM36' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM37' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM38' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM39' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM40' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM41' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM42' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM43' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM44' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM45' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM46' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM47' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM48' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM49' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM50' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM51' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM52' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM53' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM54' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM55' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM56' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM57' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM58' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM59' || g_newline ||
            G_INDENT || '       , A.SQUAL_NUM60' || g_newline ||
            G_INDENT || '       , A.ASSIGNED_FLAG' || g_newline ||
            G_INDENT || '       , A.PROCESSED_FLAG' || g_newline ||
            G_INDENT || '       , A.ORG_ID' || g_newline ||
            G_INDENT || '       , A.SECURITY_GROUP_ID' || g_newline ||
            G_INDENT || '       , A.OBJECT_VERSION_NUMBER' || g_newline ||
            G_INDENT || '       , A.WORKER_ID' || g_newline ;

Line: 330

         JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'ERROR: JTF_TAE_SQL_LIBRARY_PVT.add_SELECT_cols');

Line: 332

END add_SELECT_cols;

Line: 337

** Function to return Static SELECT clause SQL
*********************************************************/
FUNCTION add_SELECT_clause( p_new_mode_fetch  CHAR )
RETURN VARCHAR2 AS

   lx_SELECT_clause     VARCHAR2(2000) := NULL;

Line: 346

   RETURN lx_SELECT_CLAUSE ||
         g_newline || g_newline ||
         G_INDENT || ' SELECT DISTINCT  ' || g_newline ||
         -- dblee: 08/26/03 use call to add_SELECT_cols
         add_SELECT_cols(p_new_mode_fetch);

Line: 355

         JTF_TAE_CONTROL_PVT.WRITE_LOG(2, 'ERROR: JTF_TAE_SQL_LIBRARY_PVT.add_SELECT_clause');

Line: 357

END add_SELECT_clause;

Line: 374

          G_INDENT || '     ( SELECT         ' || g_newline ||
          G_INDENT || '              jtdr.terr_id                  ' || g_newline ||
          G_INDENT || '            , jtdr.source_id                ' || g_newline ||
          G_INDENT || '            , jtdr.qual_type_id             ' || g_newline ||
          G_INDENT || '            , jtdr.top_level_terr_id        ' || g_newline ||
          G_INDENT || '            , jtdr.absolute_rank            ' || g_newline ||
          G_INDENT || '            , jtdr.num_winners              ' || g_newline ||
          G_INDENT || '            , jtdr.org_id                   ' || g_newline ||
          G_INDENT || '       FROM  jtf_qual_type_usgs_all jqtu    ' || g_newline ||
          G_INDENT || '            ,jtf_terr_qtype_usgs_all jtqu   ' || g_newline ||
          G_INDENT || '            ,jtf_terr_denorm_rules_all jtdr  ' || g_newline ||
          G_INDENT || '       WHERE jtdr.terr_id = jtdr.related_terr_id ' || g_newline ||
          G_INDENT || '         AND jtdr.source_id = p_source_id   ' || g_newline ||
          G_INDENT || '         AND jqtu.source_id = jtdr.source_id    ' || g_newline ||
          G_INDENT || '         AND jqtu.qual_type_id = p_trans_object_type_id ' || g_newline ||
          G_INDENT || '         AND jtdr.terr_id = jtqu.terr_id ' || g_newline ||
          G_INDENT || '         AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id ' || g_newline ||
          G_INDENT || '         AND jtdr.resource_exists_flag = ''Y'' '|| g_newline ||
          G_INDENT || '         AND jtqu.qual_relation_product = lp_qual_combination_tbl(i) ' || g_newline ||
          G_INDENT || '     ) ILV '||g_newline;

Line: 399

          G_INDENT || '     ( SELECT DISTINCT        ' || g_newline ||
          G_INDENT || '              jtdr.terr_id                  ' || g_newline ||
          G_INDENT || '            , jtdr.source_id                ' || g_newline ||
          G_INDENT || '            , jtdr.qual_type_id             ' || g_newline ||
          G_INDENT || '            , jtdr.top_level_terr_id        ' || g_newline ||
          G_INDENT || '            , jtdr.absolute_rank            ' || g_newline ||
          G_INDENT || '            , jtdr.num_winners              ' || g_newline ||
          G_INDENT || '            , jtdr.org_id                   ' || g_newline ||
          G_INDENT || '       FROM  jtf_qual_type_usgs_all jqtu    ' || g_newline ||
          G_INDENT || '            ,jtf_terr_qtype_usgs_all jtqu   ' || g_newline ||
          G_INDENT || '            ,jtf_changed_terr_all jct ' || g_newline ||
          G_INDENT || '            ,jtf_terr_denorm_rules_all jtdr  ' || g_newline ||
          G_INDENT || '       WHERE jct.terr_id = jtdr.terr_id     ' || g_newline ||
          G_INDENT || '         AND jtdr.terr_id = jtdr.related_terr_id ' || g_newline ||
          G_INDENT || '         AND jqtu.source_id = p_source_id   ' || g_newline ||
          G_INDENT || '         AND jqtu.source_id = jtdr.source_id    ' || g_newline ||
          G_INDENT || '         AND jqtu.qual_type_id = p_trans_object_type_id ' || g_newline ||
          G_INDENT || '         AND jct.terr_id = jtqu.terr_id ' || g_newline ||
          G_INDENT || '         AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id ' || g_newline ||
          G_INDENT || '         AND jtdr.resource_exists_flag = ''Y'' '|| g_newline ||
          G_INDENT || '         AND jtqu.qual_relation_product = lp_qual_combination_tbl(i) ' || g_newline ||
          G_INDENT || '     ) ILV'||g_newline;

Line: 444

          G_INDENT || '     ( SELECT  /*+ NO_MERGE */              ' || g_newline ||
          G_INDENT || '              jtdr.terr_id                  ' || g_newline ||
          G_INDENT || '            , jtdr.source_id                ' || g_newline ||
          G_INDENT || '            , jtdr.qual_type_id             ' || g_newline ||
          G_INDENT || '            , jtdr.top_level_terr_id        ' || g_newline ||
          G_INDENT || '            , jtdr.absolute_rank            ' || g_newline ||
          G_INDENT || '            , jtdr.num_winners              ' || g_newline ||
          G_INDENT || '            , jtdr.org_id                   ' || g_newline ||
          G_INDENT || '       FROM jtf_terr_denorm_rules_all jtdr  ' || g_newline ||
          G_INDENT || '            ,jtf_terr_qtype_usgs_all jtqu   ' || g_newline ||
          G_INDENT || '            ,jtf_qual_type_usgs_all jqtu    ' || g_newline ||
          G_INDENT || '       WHERE jtdr.terr_id = jtdr.related_terr_id ' || g_newline ||
          G_INDENT || '         AND jtdr.source_id = p_source_id   ' || g_newline ||
          G_INDENT || '         AND jqtu.source_id = jtdr.source_id    ' || g_newline ||
          G_INDENT || '         AND jqtu.qual_type_id = p_trans_object_type_id ' || g_newline ||
          G_INDENT || '         AND jtdr.terr_id = jtqu.terr_id ' || g_newline ||
          G_INDENT || '         AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id ' || g_newline ||
          G_INDENT || '         AND jtdr.resource_exists_flag = ''Y'' '|| g_newline ||
          G_INDENT || '         AND jtqu.qual_relation_product = lp_qual_combination_tbl(i) ' || g_newline ||
          G_INDENT || '     ) ILV'||g_newline;

Line: 469

          G_INDENT || '     ( SELECT /*+ NO_MERGE */ DISTINCT      ' || g_newline ||
          G_INDENT || '              jtdr.terr_id                  ' || g_newline ||
          G_INDENT || '            , jtdr.source_id                ' || g_newline ||
          G_INDENT || '            , jtdr.qual_type_id             ' || g_newline ||
          G_INDENT || '            , jtdr.top_level_terr_id        ' || g_newline ||
          G_INDENT || '            , jtdr.absolute_rank            ' || g_newline ||
          G_INDENT || '            , jtdr.num_winners              ' || g_newline ||
          G_INDENT || '            , jtdr.org_id                   ' || g_newline ||
          G_INDENT || '       FROM jtf_changed_terr_all jct ' || g_newline ||
          G_INDENT || '          , jtf_terr_denorm_rules_all jtdr  ' || g_newline ||
          G_INDENT || '            ,jtf_terr_qtype_usgs_all jtqu   ' || g_newline ||
          G_INDENT || '            ,jtf_qual_type_usgs_all jqtu    ' || g_newline ||
          G_INDENT || '       WHERE jct.terr_id = jtdr.terr_id     ' || g_newline ||
          G_INDENT || '         AND jtdr.terr_id = jtdr.related_terr_id ' || g_newline ||
          G_INDENT || '         AND jqtu.source_id = p_source_id   ' || g_newline ||
          G_INDENT || '         AND jqtu.source_id = jtdr.source_id    ' || g_newline ||
          G_INDENT || '         AND jqtu.qual_type_id = p_trans_object_type_id ' || g_newline ||
          G_INDENT || '         AND jct.terr_id = jtqu.terr_id ' || g_newline ||
          G_INDENT || '         AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id ' || g_newline ||
          G_INDENT || '         AND jtdr.resource_exists_flag = ''Y'' '|| g_newline ||
          G_INDENT || '         AND jtqu.qual_relation_product = lp_qual_combination_tbl(i) ' || g_newline ||
          G_INDENT || '     ) ILV'||g_newline;

Line: 531

         '     SELECT COUNT(DISTINCT WORKER_ID) ' || g_newline ||
         '       INTO l_num_workers ' || g_newline ||
         '       FROM JTF_TAE_1001_ACCOUNT_TRANS; ' || g_newline || g_newline ||

Line: 541

      '         INSERT INTO  '|| p_match_table_name || ' i' || g_newline ||
      '         ('  || g_newline ||
      '            TRANS_OBJECT_ID' || g_newline ||
      '            , TRANS_DETAIL_OBJECT_ID' || g_newline ||
      '            , HEADER_ID1' || g_newline ||
      '            , HEADER_ID2' || g_newline ||
      '            , SOURCE_ID' || g_newline ||
      '            , TRANS_OBJECT_TYPE_ID' || g_newline ||
      '            , LAST_UPDATE_DATE' || g_newline ||
      '            , LAST_UPDATED_BY' || g_newline ||
      '            , CREATION_DATE' || g_newline ||
      '            , CREATED_BY' || g_newline ||
      '            , LAST_UPDATE_LOGIN' || g_newline ||
      '            , REQUEST_ID' || g_newline ||
      '            , PROGRAM_APPLICATION_ID' || g_newline ||
      '            , PROGRAM_ID' || g_newline ||
      '            , PROGRAM_UPDATE_DATE' || g_newline ||
      '            , SQUAL_FC01' || g_newline ||
      '            , SQUAL_FC02' || g_newline ||
      '            , SQUAL_FC03' || g_newline ||
      '            , SQUAL_FC04' || g_newline ||
      '            , SQUAL_FC05' || g_newline ||
      '            , SQUAL_CURC01' || g_newline ||
      '            , SQUAL_CURC02' || g_newline ||
      '            , SQUAL_CURC03' || g_newline ||
      '            , SQUAL_CURC04' || g_newline ||
      '            , SQUAL_CURC05' || g_newline ||
      '            , SQUAL_CURC06' || g_newline ||
      '            , SQUAL_CURC07' || g_newline ||
      '            , SQUAL_CURC08' || g_newline ||
      '            , SQUAL_CURC09' || g_newline ||
      '            , SQUAL_CURC10' || g_newline ||
      '            , SQUAL_CHAR01' || g_newline ||
      '            , SQUAL_CHAR02' || g_newline ||
      '            , SQUAL_CHAR03' || g_newline ||
      '            , SQUAL_CHAR04' || g_newline ||
      '            , SQUAL_CHAR05' || g_newline ||
      '            , SQUAL_CHAR06' || g_newline ||
      '            , SQUAL_CHAR07' || g_newline ||
      '            , SQUAL_CHAR08' || g_newline ||
      '            , SQUAL_CHAR09' || g_newline ||
      '            , SQUAL_CHAR10' || g_newline ||
      '            , SQUAL_CHAR11' || g_newline ||
      '            , SQUAL_CHAR12' || g_newline ||
      '            , SQUAL_CHAR13' || g_newline ||
      '            , SQUAL_CHAR14' || g_newline ||
      '            , SQUAL_CHAR15' || g_newline ||
      '            , SQUAL_CHAR16' || g_newline ||
      '            , SQUAL_CHAR17' || g_newline ||
      '            , SQUAL_CHAR18' || g_newline ||
      '            , SQUAL_CHAR19' || g_newline ||
      '            , SQUAL_CHAR20' || g_newline ||
      '            , SQUAL_CHAR21' || g_newline ||
      '            , SQUAL_CHAR22' || g_newline ||
      '            , SQUAL_CHAR23' || g_newline ||
      '            , SQUAL_CHAR24' || g_newline ||
      '            , SQUAL_CHAR25' || g_newline ||
      '            , SQUAL_CHAR26' || g_newline ||
      '            , SQUAL_CHAR27' || g_newline ||
      '            , SQUAL_CHAR28' || g_newline ||
      '            , SQUAL_CHAR30' || g_newline ||
      '            , SQUAL_CHAR31' || g_newline ||
      '            , SQUAL_CHAR32' || g_newline ||
      '            , SQUAL_CHAR33' || g_newline ||
      '            , SQUAL_CHAR34' || g_newline ||
      '            , SQUAL_CHAR35' || g_newline ||
      '            , SQUAL_CHAR36' || g_newline ||
      '            , SQUAL_CHAR37' || g_newline ||
      '            , SQUAL_CHAR38' || g_newline ||
      '            , SQUAL_CHAR39' || g_newline ||
      '            , SQUAL_CHAR40' || g_newline ||
      '            , SQUAL_CHAR41' || g_newline ||
      '            , SQUAL_CHAR42' || g_newline ||
      '            , SQUAL_CHAR43' || g_newline ||
      '            , SQUAL_CHAR44' || g_newline ||
      '            , SQUAL_CHAR45' || g_newline ||
      '            , SQUAL_CHAR46' || g_newline ||
      '            , SQUAL_CHAR47' || g_newline ||
      '            , SQUAL_CHAR48' || g_newline ||
      '            , SQUAL_CHAR49' || g_newline ||
      '            , SQUAL_CHAR50' || g_newline ||
      '            , SQUAL_CHAR51' || g_newline ||
      '            , SQUAL_CHAR52' || g_newline ||
      '            , SQUAL_CHAR53' || g_newline ||
      '            , SQUAL_CHAR54' || g_newline ||
      '            , SQUAL_CHAR55' || g_newline ||
      '            , SQUAL_CHAR56' || g_newline ||
      '            , SQUAL_CHAR57' || g_newline ||
      '            , SQUAL_CHAR58' || g_newline ||
      '            , SQUAL_CHAR59' || g_newline ||
      '            , SQUAL_CHAR60' || g_newline ||
      '            , SQUAL_NUM01' || g_newline ||
      '            , SQUAL_NUM02' || g_newline ||
      '            , SQUAL_NUM03' || g_newline ||
      '            , SQUAL_NUM04' || g_newline ||
      '            , SQUAL_NUM05' || g_newline ||
      '            , SQUAL_NUM06' || g_newline ||
      '            , SQUAL_NUM07' || g_newline ||
      '            , SQUAL_NUM08' || g_newline ||
      '            , SQUAL_NUM09' || g_newline ||
      '            , SQUAL_NUM10' || g_newline ||
      '            , SQUAL_NUM11' || g_newline ||
      '            , SQUAL_NUM12' || g_newline ||
      '            , SQUAL_NUM13' || g_newline ||
      '            , SQUAL_NUM14' || g_newline ||
      '            , SQUAL_NUM15' || g_newline ||
      '            , SQUAL_NUM16' || g_newline ||
      '            , SQUAL_NUM17' || g_newline ||
      '            , SQUAL_NUM18' || g_newline ||
      '            , SQUAL_NUM19' || g_newline ||
      '            , SQUAL_NUM20' || g_newline ||
      '            , SQUAL_NUM21' || g_newline ||
      '            , SQUAL_NUM22' || g_newline ||
      '            , SQUAL_NUM23' || g_newline ||
      '            , SQUAL_NUM24' || g_newline ||
      '            , SQUAL_NUM25' || g_newline ||
      '            , SQUAL_NUM26' || g_newline ||
      '            , SQUAL_NUM27' || g_newline ||
      '            , SQUAL_NUM28' || g_newline ||
      '            , SQUAL_NUM29' || g_newline ||
      '            , SQUAL_NUM30' || g_newline ||
      '            , SQUAL_NUM31' || g_newline ||
      '            , SQUAL_NUM32' || g_newline ||
      '            , SQUAL_NUM33' || g_newline ||
      '            , SQUAL_NUM34' || g_newline ||
      '            , SQUAL_NUM35' || g_newline ||
      '            , SQUAL_NUM36' || g_newline ||
      '            , SQUAL_NUM37' || g_newline ||
      '            , SQUAL_NUM38' || g_newline ||
      '            , SQUAL_NUM39' || g_newline ||
      '            , SQUAL_NUM40' || g_newline ||
      '            , SQUAL_NUM41' || g_newline ||
      '            , SQUAL_NUM42' || g_newline ||
      '            , SQUAL_NUM43' || g_newline ||
      '            , SQUAL_NUM44' || g_newline ||
      '            , SQUAL_NUM45' || g_newline ||
      '            , SQUAL_NUM46' || g_newline ||
      '            , SQUAL_NUM47' || g_newline ||
      '            , SQUAL_NUM48' || g_newline ||
      '            , SQUAL_NUM49' || g_newline ||
      '            , SQUAL_NUM50' || g_newline ||
      '            , SQUAL_NUM51' || g_newline ||
      '            , SQUAL_NUM52' || g_newline ||
      '            , SQUAL_NUM53' || g_newline ||
      '            , SQUAL_NUM54' || g_newline ||
      '            , SQUAL_NUM55' || g_newline ||
      '            , SQUAL_NUM56' || g_newline ||
      '            , SQUAL_NUM57' || g_newline ||
      '            , SQUAL_NUM58' || g_newline ||
      '            , SQUAL_NUM59' || g_newline ||
      '            , SQUAL_NUM60' || g_newline ||
      '            , ASSIGNED_FLAG' || g_newline ||
      '            , PROCESSED_FLAG' || g_newline ||
      '            , ORG_ID' || g_newline ||
      '            , SECURITY_GROUP_ID' || g_newline ||
      '            , OBJECT_VERSION_NUMBER' || g_newline ||
      '            , WORKER_ID' || g_newline ||
      '         )'  || g_newline ||
     /* Num of workers = 1 then USE_HASH */
       G_INDENT || 'SELECT /*+ USE_HASH(ILV4841 A) */ ' || g_newline;

Line: 704

         '         INSERT INTO  '||p_match_table_name || ' i' || g_newline ||
         '         (' || g_newline ||
         '            trans_object_id' || g_newline ||
         '          , trans_detail_object_id' || g_newline ||
         '          , worker_id' || g_newline ||
         '          , header_id1'|| g_newline ||
         '          , header_id2'|| g_newline ||
         '          , source_id'|| g_newline ||
         '          , trans_object_type_id'|| g_newline ||
         '          , last_update_date'|| g_newline ||
         '          , last_updated_by'|| g_newline ||
         '          , creation_date'|| g_newline ||
         '          , created_by'|| g_newline ||
         '          , last_update_login'|| g_newline ||
         '          , request_id'|| g_newline ||
         '          , program_application_id'|| g_newline ||
         '          , program_id'|| g_newline ||
         '          , program_update_date'|| g_newline ||
         '          , terr_id'|| g_newline ||
         '          , absolute_rank'|| g_newline ||
         '          , top_level_terr_id'|| g_newline ||
         '          , num_winners'|| g_newline ||
         '          , org_id'|| g_newline ||
         '         )' || g_newline ||

         /* Num of workers = 1 then USE_HASH */
         G_INDENT || 'SELECT /*+ USE_HASH(ILV4841 A) */ ' || g_newline;

Line: 735

      lx_4841_sql := lx_4841_sql || add_SELECT_cols(p_new_mode_fetch);

Line: 775

     '   , ( SELECT /*+ NO_MERGE */ ' || g_newline ||
     '              ILV4841.terr_id ' || g_newline ||
     '            , ILV4841.absolute_rank ' || g_newline ||
     '            , ILV4841.top_level_terr_id ' || g_newline ||
     '            , ILV4841.num_winners ' || g_newline ||
     '            , ILV4841.org_id ' || g_newline ||
     '            , Q1007R1.high_value_char q1007_high_value_char ' || g_newline ||
     '            , Q1007R1.low_value_char q1007_low_value_char ' || g_newline ||
     '            , Q1003R1.low_value_char q1003_low_value_char ' || g_newline ||
     '            , Q1007R1.comparison_operator q1007_cop ' || g_newline ||
     '            , Q1003R1.comparison_operator q1003_cop ' || g_newline ||
     '       FROM  jtf_terr_qual_rules_mv Q1003R1 ' || g_newline ||
     '           , jtf_terr_qual_rules_mv Q1007R1 ' || g_newline;

Line: 791

       '           , ( SELECT /*+ NO_MERGE */ DISTINCT ' || g_newline;

Line: 794

       '           , ( SELECT ' || g_newline;

Line: 879

      '         INSERT INTO  '|| p_match_table_name || ' i' || g_newline ||
      '         ('  || g_newline ||
      '            TRANS_OBJECT_ID' || g_newline ||
      '            , TRANS_DETAIL_OBJECT_ID' || g_newline ||
      '            , HEADER_ID1' || g_newline ||
      '            , HEADER_ID2' || g_newline ||
      '            , SOURCE_ID' || g_newline ||
      '            , TRANS_OBJECT_TYPE_ID' || g_newline ||
      '            , LAST_UPDATE_DATE' || g_newline ||
      '            , LAST_UPDATED_BY' || g_newline ||
      '            , CREATION_DATE' || g_newline ||
      '            , CREATED_BY' || g_newline ||
      '            , LAST_UPDATE_LOGIN' || g_newline ||
      '            , REQUEST_ID' || g_newline ||
      '            , PROGRAM_APPLICATION_ID' || g_newline ||
      '            , PROGRAM_ID' || g_newline ||
      '            , PROGRAM_UPDATE_DATE' || g_newline ||
      '            , SQUAL_FC01' || g_newline ||
      '            , SQUAL_FC02' || g_newline ||
      '            , SQUAL_FC03' || g_newline ||
      '            , SQUAL_FC04' || g_newline ||
      '            , SQUAL_FC05' || g_newline ||
      '            , SQUAL_CURC01' || g_newline ||
      '            , SQUAL_CURC02' || g_newline ||
      '            , SQUAL_CURC03' || g_newline ||
      '            , SQUAL_CURC04' || g_newline ||
      '            , SQUAL_CURC05' || g_newline ||
      '            , SQUAL_CURC06' || g_newline ||
      '            , SQUAL_CURC07' || g_newline ||
      '            , SQUAL_CURC08' || g_newline ||
      '            , SQUAL_CURC09' || g_newline ||
      '            , SQUAL_CURC10' || g_newline ||
      '            , SQUAL_CHAR01' || g_newline ||
      '            , SQUAL_CHAR02' || g_newline ||
      '            , SQUAL_CHAR03' || g_newline ||
      '            , SQUAL_CHAR04' || g_newline ||
      '            , SQUAL_CHAR05' || g_newline ||
      '            , SQUAL_CHAR06' || g_newline ||
      '            , SQUAL_CHAR07' || g_newline ||
      '            , SQUAL_CHAR08' || g_newline ||
      '            , SQUAL_CHAR09' || g_newline ||
      '            , SQUAL_CHAR10' || g_newline ||
      '            , SQUAL_CHAR11' || g_newline ||
      '            , SQUAL_CHAR12' || g_newline ||
      '            , SQUAL_CHAR13' || g_newline ||
      '            , SQUAL_CHAR14' || g_newline ||
      '            , SQUAL_CHAR15' || g_newline ||
      '            , SQUAL_CHAR16' || g_newline ||
      '            , SQUAL_CHAR17' || g_newline ||
      '            , SQUAL_CHAR18' || g_newline ||
      '            , SQUAL_CHAR19' || g_newline ||
      '            , SQUAL_CHAR20' || g_newline ||
      '            , SQUAL_CHAR21' || g_newline ||
      '            , SQUAL_CHAR22' || g_newline ||
      '            , SQUAL_CHAR23' || g_newline ||
      '            , SQUAL_CHAR24' || g_newline ||
      '            , SQUAL_CHAR25' || g_newline ||
      '            , SQUAL_CHAR26' || g_newline ||
      '            , SQUAL_CHAR27' || g_newline ||
      '            , SQUAL_CHAR28' || g_newline ||
      '            , SQUAL_CHAR30' || g_newline ||
      '            , SQUAL_CHAR31' || g_newline ||
      '            , SQUAL_CHAR32' || g_newline ||
      '            , SQUAL_CHAR33' || g_newline ||
      '            , SQUAL_CHAR34' || g_newline ||
      '            , SQUAL_CHAR35' || g_newline ||
      '            , SQUAL_CHAR36' || g_newline ||
      '            , SQUAL_CHAR37' || g_newline ||
      '            , SQUAL_CHAR38' || g_newline ||
      '            , SQUAL_CHAR39' || g_newline ||
      '            , SQUAL_CHAR40' || g_newline ||
      '            , SQUAL_CHAR41' || g_newline ||
      '            , SQUAL_CHAR42' || g_newline ||
      '            , SQUAL_CHAR43' || g_newline ||
      '            , SQUAL_CHAR44' || g_newline ||
      '            , SQUAL_CHAR45' || g_newline ||
      '            , SQUAL_CHAR46' || g_newline ||
      '            , SQUAL_CHAR47' || g_newline ||
      '            , SQUAL_CHAR48' || g_newline ||
      '            , SQUAL_CHAR49' || g_newline ||
      '            , SQUAL_CHAR50' || g_newline ||
      '            , SQUAL_CHAR51' || g_newline ||
      '            , SQUAL_CHAR52' || g_newline ||
      '            , SQUAL_CHAR53' || g_newline ||
      '            , SQUAL_CHAR54' || g_newline ||
      '            , SQUAL_CHAR55' || g_newline ||
      '            , SQUAL_CHAR56' || g_newline ||
      '            , SQUAL_CHAR57' || g_newline ||
      '            , SQUAL_CHAR58' || g_newline ||
      '            , SQUAL_CHAR59' || g_newline ||
      '            , SQUAL_CHAR60' || g_newline ||
      '            , SQUAL_NUM01' || g_newline ||
      '            , SQUAL_NUM02' || g_newline ||
      '            , SQUAL_NUM03' || g_newline ||
      '            , SQUAL_NUM04' || g_newline ||
      '            , SQUAL_NUM05' || g_newline ||
      '            , SQUAL_NUM06' || g_newline ||
      '            , SQUAL_NUM07' || g_newline ||
      '            , SQUAL_NUM08' || g_newline ||
      '            , SQUAL_NUM09' || g_newline ||
      '            , SQUAL_NUM10' || g_newline ||
      '            , SQUAL_NUM11' || g_newline ||
      '            , SQUAL_NUM12' || g_newline ||
      '            , SQUAL_NUM13' || g_newline ||
      '            , SQUAL_NUM14' || g_newline ||
      '            , SQUAL_NUM15' || g_newline ||
      '            , SQUAL_NUM16' || g_newline ||
      '            , SQUAL_NUM17' || g_newline ||
      '            , SQUAL_NUM18' || g_newline ||
      '            , SQUAL_NUM19' || g_newline ||
      '            , SQUAL_NUM20' || g_newline ||
      '            , SQUAL_NUM21' || g_newline ||
      '            , SQUAL_NUM22' || g_newline ||
      '            , SQUAL_NUM23' || g_newline ||
      '            , SQUAL_NUM24' || g_newline ||
      '            , SQUAL_NUM25' || g_newline ||
      '            , SQUAL_NUM26' || g_newline ||
      '            , SQUAL_NUM27' || g_newline ||
      '            , SQUAL_NUM28' || g_newline ||
      '            , SQUAL_NUM29' || g_newline ||
      '            , SQUAL_NUM30' || g_newline ||
      '            , SQUAL_NUM31' || g_newline ||
      '            , SQUAL_NUM32' || g_newline ||
      '            , SQUAL_NUM33' || g_newline ||
      '            , SQUAL_NUM34' || g_newline ||
      '            , SQUAL_NUM35' || g_newline ||
      '            , SQUAL_NUM36' || g_newline ||
      '            , SQUAL_NUM37' || g_newline ||
      '            , SQUAL_NUM38' || g_newline ||
      '            , SQUAL_NUM39' || g_newline ||
      '            , SQUAL_NUM40' || g_newline ||
      '            , SQUAL_NUM41' || g_newline ||
      '            , SQUAL_NUM42' || g_newline ||
      '            , SQUAL_NUM43' || g_newline ||
      '            , SQUAL_NUM44' || g_newline ||
      '            , SQUAL_NUM45' || g_newline ||
      '            , SQUAL_NUM46' || g_newline ||
      '            , SQUAL_NUM47' || g_newline ||
      '            , SQUAL_NUM48' || g_newline ||
      '            , SQUAL_NUM49' || g_newline ||
      '            , SQUAL_NUM50' || g_newline ||
      '            , SQUAL_NUM51' || g_newline ||
      '            , SQUAL_NUM52' || g_newline ||
      '            , SQUAL_NUM53' || g_newline ||
      '            , SQUAL_NUM54' || g_newline ||
      '            , SQUAL_NUM55' || g_newline ||
      '            , SQUAL_NUM56' || g_newline ||
      '            , SQUAL_NUM57' || g_newline ||
      '            , SQUAL_NUM58' || g_newline ||
      '            , SQUAL_NUM59' || g_newline ||
      '            , SQUAL_NUM60' || g_newline ||
      '            , ASSIGNED_FLAG' || g_newline ||
      '            , PROCESSED_FLAG' || g_newline ||
      '            , ORG_ID' || g_newline ||
      '            , SECURITY_GROUP_ID' || g_newline ||
      '            , OBJECT_VERSION_NUMBER' || g_newline ||
      '            , WORKER_ID' || g_newline ||
      '         )'  || g_newline ||
       G_INDENT || 'SELECT /*+ USE_CONCAT */ ' || g_newline;

Line: 1041

        '         INSERT INTO  '|| p_match_table_name || ' i' || g_newline ||
         '         (' || g_newline ||
         '            trans_object_id' || g_newline ||
         '          , trans_detail_object_id' || g_newline ||
         '          , worker_id' || g_newline ||
         '          , header_id1'|| g_newline ||
         '          , header_id2'|| g_newline ||
         '          , source_id'|| g_newline ||
         '          , trans_object_type_id'|| g_newline ||
         '          , last_update_date'|| g_newline ||
         '          , last_updated_by'|| g_newline ||
         '          , creation_date'|| g_newline ||
         '          , created_by'|| g_newline ||
         '          , last_update_login'|| g_newline ||
         '          , request_id'|| g_newline ||
         '          , program_application_id'|| g_newline ||
         '          , program_id'|| g_newline ||
         '          , program_update_date'|| g_newline ||
         '          , terr_id'|| g_newline ||
         '          , absolute_rank'|| g_newline ||
         '          , top_level_terr_id'|| g_newline ||
         '          , num_winners'|| g_newline ||
         '          , org_id'|| g_newline ||
         '         )' || g_newline ||
         G_INDENT || 'SELECT /*+ USE_CONCAT INDEX(A ' || l_trans_idx_name || ') */ ' || g_newline;

Line: 1071

      lx_4841_sql := lx_4841_sql || add_SELECT_cols(p_new_mode_fetch);

Line: 1111

     '   , ( SELECT /*+ NO_MERGE */ ' || g_newline ||
     '              ILV4841.terr_id ' || g_newline ||
     '            , ILV4841.absolute_rank ' || g_newline ||
     '            , ILV4841.top_level_terr_id ' || g_newline ||
     '            , ILV4841.num_winners ' || g_newline ||
     '            , ILV4841.org_id ' || g_newline ||
     '            , Q1007R1.high_value_char q1007_high_value_char ' || g_newline ||
     '            , Q1007R1.low_value_char q1007_low_value_char ' || g_newline ||
     '            , Q1003R1.low_value_char q1003_low_value_char ' || g_newline ||
     '            , Q1007R1.comparison_operator q1007_cop ' || g_newline ||
     '            , Q1003R1.comparison_operator q1003_cop ' || g_newline ||
     '       FROM  jtf_terr_qual_rules_mv Q1003R1 ' || g_newline ||
     '           , jtf_terr_qual_rules_mv Q1007R1 ' || g_newline;

Line: 1127

       '           , ( SELECT /*+ NO_MERGE */ DISTINCT ' || g_newline;

Line: 1130

       '           , ( SELECT ' || g_newline;

Line: 1234

   lp_SELECT_cols              VARCHAR2(32767) := NULL;

Line: 1245

       lp_SELECT_cols :=
          /* ARPATEL: 01/15/2004 bug#3373462 */
          --G_INDENT5 || add_SELECT_cols(p_new_mode_fetch) || g_newline;

Line: 1395

       lp_SELECT_cols :=
          G_INDENT5 || 'A.trans_object_id, A.trans_detail_object_id, ' || g_newline ||
          -- eihsu: 06/19/2003 worker_id
          G_INDENT5 || 'A.worker_id, ' || g_newline ||
          G_INDENT5 || 'A.header_id1, A.header_id2, ' || g_newline ||
          G_INDENT5 || 'ILV.terr_id, ILV.absolute_rank, ' || g_newline ||
          G_INDENT5 || 'ILV.top_level_terr_id, ILV.num_winners, ILV.org_id ';

Line: 1474

      l_qual_rules => G_INDENT || add_SELECT_clause(p_new_mode_fetch) || g_newline
   );

Line: 1489

         G_INDENT5 || 'SELECT /*+ ' || g_newline ||
         G_INDENT5 || '           ORDERED ' || g_newline ||
         G_INDENT5 || '           USE_CONCAT ' || g_newline ||
         G_INDENT5 || '           USE_NL(ILV Q1003R1 Q1007R1 Q1012R1 A) ' || g_newline ||
         G_INDENT5 || '           INDEX(ILV JTF_TERR_DENORM_RULES_N4) ' || g_newline ||
                  /* DYNAMIC HINT BASED ON TRANSACTION TYPE */
         G_INDENT5 || '           INDEX(A JTF_TAE_TN' || ABS(p_trans_object_type_id) ||
                                                        '_324347_ND)' || g_newline ||

         G_INDENT5 || '           INDEX(Q1003R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1007R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1012R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '       */ ' || g_newline;

Line: 1504

         G_INDENT5 || 'SELECT /*+ ' || g_newline ||
         G_INDENT5 || '           ORDERED ' || g_newline ||
         G_INDENT5 || '           USE_CONCAT ' || g_newline ||
         G_INDENT5 || '           NO_MERGE ' || g_newline ||
         G_INDENT5 || '       */ ' || g_newline;

Line: 1512

         /* Add SELECT columns */
         lp_SELECT_cols || g_newline ||

         G_INDENT5 || 'FROM ';

Line: 1556

         G_INDENT5 || 'SELECT /*+ ' || g_newline ||
         G_INDENT5 || '           ORDERED ' || g_newline ||
         G_INDENT5 || '           USE_CONCAT ' || g_newline ||
         G_INDENT5 || '           USE_NL(ILV Q1003R1 Q1007R1 Q1012LK A)  ' || g_newline ||
         G_INDENT5 || '           INDEX(ILV JTF_TERR_DENORM_RULES_N4) ' || g_newline ||

         /* DYNAMIC HINT BASED ON TRANSACTION TYPE */
         G_INDENT5 || '           INDEX(A JTF_TAE_TN' || ABS(p_trans_object_type_id) ||
                                                      '_324347_ND)' || g_newline ||
         G_INDENT5 || '           INDEX(Q1003R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1007R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1012LK JTF_TERR_CNR_QUAL_LIKE_MV_N10) ' || g_newline ||
         G_INDENT5 || '       */ ' || g_newline;

Line: 1571

         G_INDENT5 || 'SELECT /*+ ' || g_newline ||
         G_INDENT5 || '           ORDERED ' || g_newline ||
         G_INDENT5 || '           USE_CONCAT ' || g_newline ||
         G_INDENT5 || '           NO_MERGE ' || g_newline ||
         G_INDENT5 || '       */ ' || g_newline;

Line: 1579

         /* Add SELECT columns */
         lp_SELECT_cols || g_newline ||

         G_INDENT5 || 'FROM    ';

Line: 1624

         G_INDENT5 || 'SELECT /*+ ' || g_newline ||
         G_INDENT5 || '           ORDERED ' || g_newline ||
         G_INDENT5 || '           USE_CONCAT ' || g_newline ||
         G_INDENT5 || '           USE_NL(ILV Q1003R1 Q1007R1 Q1012LK A)  ' || g_newline ||
         G_INDENT5 || '           INDEX(ILV JTF_TERR_DENORM_RULES_N4) ' || g_newline ||
         /* DYNAMIC HINT BASED ON TRANSACTION TYPE */
         G_INDENT5 || '           INDEX(A JTF_TAE_TN' || ABS(p_trans_object_type_id) ||
                                                        '_324347X_ND)' || g_newline ||
         G_INDENT5 || '           INDEX(Q1003R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1007R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1012LK JTF_TERR_CNR_QUAL_LIKE_MV_N10) ' || g_newline ||
         G_INDENT5 || '       */ ' || g_newline;

Line: 1638

         G_INDENT5 || 'SELECT /*+ ' || g_newline ||
         G_INDENT5 || '           ORDERED ' || g_newline ||
         G_INDENT5 || '           USE_CONCAT ' || g_newline ||
         G_INDENT5 || '           NO_MERGE ' || g_newline ||
         G_INDENT5 || '       */ ' || g_newline;

Line: 1646

         /* Add SELECT columns */
         lp_SELECT_cols || g_newline ||

         G_INDENT5 || 'FROM   ';

Line: 1682

         G_INDENT5 || 'SELECT /*+ ' || g_newline ||
         G_INDENT5 || '           ORDERED ' || g_newline ||
         G_INDENT5 || '           USE_CONCAT ' || g_newline ||
         G_INDENT5 || '           USE_NL(ILV Q1003R1 Q1007R1 Q1012LK A)  ' || g_newline ||
         G_INDENT5 || '           INDEX(ILV JTF_TERR_DENORM_RULES_N4) ' || g_newline ||
         /* DYNAMIC HINT BASED ON TRANSACTION TYPE */
         G_INDENT5 || '           INDEX(A JTF_TAE_TN' || ABS(p_trans_object_type_id) ||
                                                        '_324347X_ND)' || g_newline ||
         G_INDENT5 || '           INDEX(Q1003R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1007R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1012LK JTF_TERR_CNR_QUAL_LIKE_MV_N10) ' || g_newline ||
         G_INDENT5 || '       */ ' || g_newline;

Line: 1696

         G_INDENT5 || 'SELECT /*+ ' || g_newline ||
         G_INDENT5 || '           ORDERED ' || g_newline ||
         G_INDENT5 || '           USE_CONCAT ' || g_newline ||
         G_INDENT5 || '           NO_MERGE ' || g_newline ||
         G_INDENT5 || '       */ ' || g_newline;

Line: 1704

         /* Add SELECT columns */
         lp_SELECT_cols || g_newline ||

         G_INDENT5 || 'FROM   ';

Line: 1741

         G_INDENT5 || 'SELECT /*+ ' || g_newline ||
         G_INDENT5 || '           ORDERED ' || g_newline ||
         G_INDENT5 || '           USE_CONCAT ' || g_newline ||
         G_INDENT5 || '           USE_NL(ILV Q1003R1 Q1007R1 Q1012BT A) ' || g_newline ||
         G_INDENT5 || '           INDEX(ILV JTF_TERR_DENORM_RULES_N4) ' || g_newline ||
         /* DYNAMIC HINT BASED ON TRANSACTION TYPE */
         G_INDENT5 || '           INDEX(A JTF_TAE_TN' || ABS(p_trans_object_type_id) ||
                                                        '_324347_ND)' || g_newline ||

         G_INDENT5 || '           INDEX(Q1003R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1007R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1012BT JTF_TERR_CNR_QUAL_BTWN_MV_N10) ' || g_newline ||
         G_INDENT5 || '       */ ' || g_newline;

Line: 1756

         G_INDENT5 || 'SELECT /*+ ' || g_newline ||
         G_INDENT5 || '           ORDERED ' || g_newline ||
         G_INDENT5 || '           USE_CONCAT ' || g_newline ||
         G_INDENT5 || '           NO_MERGE ' || g_newline ||
         G_INDENT5 || '       */ ' || g_newline;

Line: 1764

         /* Add SELECT columns */
         lp_SELECT_cols || g_newline ||

         G_INDENT5 || 'FROM   ';

Line: 1810

FUNCTION EXP_PURCHASE_UNION_SELECT (p_new_mode_fetch  IN   CHAR)
RETURN VARCHAR2 AS
BEGIN
  --bug#3373462 ARPATEL: 01/30/2004
    IF p_new_mode_fetch = 'Y'
    THEN
      RETURN
            --G_INDENT || 'FROM ( ' || g_newline ||
            G_INDENT || 'SELECT /*+ NO_MERGE(ILV1) NO_MERGE(ILV2) */ ' || g_newline ||
            --G_INDENT || '                 USE_HASH(ILV1 ILV2) */' || g_newline ||
            G_INDENT || '         ILV2.TRANS_OBJECT_ID' || g_newline ||
            G_INDENT || '       , ILV2.TRANS_DETAIL_OBJECT_ID' || g_newline ||
            G_INDENT || '       , ILV2.HEADER_ID1' || g_newline ||
            G_INDENT || '       , ILV2.HEADER_ID2' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_FC01' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_FC02' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_FC03' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_FC04' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_FC05' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CURC01' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CURC02' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CURC03' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CURC04' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CURC05' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CURC06' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CURC07' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CURC08' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CURC09' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CURC10' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR01' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR02' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR03' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR04' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR05' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR06' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR07' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR08' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR09' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR10' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR11' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR12' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR13' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR14' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR15' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR16' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR17' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR18' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR19' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR20' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR21' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR22' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR23' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR24' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR25' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR26' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR27' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR28' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR30' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR31' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR32' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR33' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR34' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR35' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR36' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR37' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR38' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR39' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR40' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR41' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR42' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR43' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR44' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR45' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR46' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR47' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR48' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR49' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR50' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR51' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR52' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR53' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR54' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR55' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR56' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR57' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR58' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR59' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR60' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM01' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM02' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM03' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM04' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM05' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM06' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM07' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM08' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM09' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM10' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM11' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM12' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM13' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM14' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM15' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM16' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM17' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM18' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM19' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM20' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM21' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM22' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM23' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM24' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM25' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM26' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM27' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM28' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM29' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM30' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM31' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM32' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM33' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM34' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM35' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM36' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM37' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM38' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM39' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM40' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM41' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM42' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM43' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM44' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM45' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM46' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM47' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM48' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM49' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM50' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM51' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM52' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM53' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM54' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM55' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM56' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM57' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM58' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM59' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM60' || g_newline ||
            G_INDENT || '       , ILV2.ASSIGNED_FLAG' || g_newline ||
            G_INDENT || '       , ILV2.PROCESSED_FLAG' || g_newline ||
            G_INDENT || '       , ILV2.ORG_ID' || g_newline ||
            G_INDENT || '       , ILV2.SECURITY_GROUP_ID' || g_newline ||
            G_INDENT || '       , ILV2.OBJECT_VERSION_NUMBER' || g_newline ||
            G_INDENT || '       , ILV2.WORKER_ID' || g_newline ||
            G_INDENT || '       , ILV2.TERR_ID' || g_newline ||
            G_INDENT || '       , ILV2.ABSOLUTE_RANK' || g_newline ||
            G_INDENT || '       , ILV2.TOP_LEVEL_TERR_ID' || g_newline ||
            G_INDENT || '       , ILV2.NUM_WINNERS' || g_newline ||
            G_INDENT || '   FROM '|| g_newline;

Line: 1971

         G_INDENT || 'SELECT /*+ NO_MERGE(ILV1) NO_MERGE(ILV2) ' || g_newline ||
         G_INDENT || '                 USE_HASH(ILV1 ILV2) */' || g_newline ||
         G_INDENT || '             ILV2.trans_object_id' || g_newline ||
         G_INDENT || '           , ILV2.trans_detail_object_id' || g_newline ||
             -- eihsu 06/19/2003 worker_id
         G_INDENT || '           , ILV2.worker_id' || g_newline ||
         G_INDENT || '           , ILV2.header_id1' || g_newline ||
         G_INDENT || '           , ILV2.header_id2' || g_newline ||
         G_INDENT || '           , ILV2.terr_id' || g_newline ||
         G_INDENT || '           , ILV2.absolute_rank' || g_newline ||
         G_INDENT || '           , ILV2.top_level_terr_id' || g_newline ||
         G_INDENT || '           , ILV2.num_winners' || g_newline ||
         G_INDENT || '           , ILV2.org_id' || g_newline ||
         G_INDENT || '      FROM '|| g_newline; --||

Line: 1986

END EXP_PURCHASE_UNION_SELECT;

Line: 1997

   l_select_ilv2               VARCHAR2(32767) := NULL;

Line: 1998

   lp_SELECT_cols              VARCHAR2(32767) := NULL;

Line: 2014

       l_select_ilv2 := add_SELECT_clause(p_new_mode_fetch);

Line: 2016

       lp_SELECT_cols :=
          --G_INDENT5 || add_SELECT_cols(p_new_mode_fetch) || g_newline;

Line: 2171

       l_select_ilv2 :=
          G_INDENT5 || 'SELECT DISTINCT ' || g_newline ||
          G_INDENT5 ||'ILV2.trans_object_id' || g_newline ||
          G_INDENT5 ||', ILV2.trans_detail_object_id' || g_newline ||
          -- eihsu 06/19/2003 worker_id
          G_INDENT5 ||', ILV2.worker_id' || g_newline ||
          G_INDENT5 ||',ILV2.header_id1' || g_newline ||
          G_INDENT5 ||',ILV2.header_id2' || g_newline ||
          G_INDENT5 ||', p_source_id' || g_newline ||
          G_INDENT5 ||', p_trans_object_type_id' || g_newline ||
          G_INDENT5 ||', l_sysdate' || g_newline ||
          G_INDENT5 ||', L_USER_ID' || g_newline ||
          G_INDENT5 ||', l_sysdate' || g_newline ||
          G_INDENT5 ||', L_USER_ID' || g_newline ||
          G_INDENT5 ||', L_USER_ID' || g_newline ||
          G_INDENT5 ||', L_REQUEST_ID' || g_newline ||
          G_INDENT5 ||', L_PROGRAM_APPL_ID' || g_newline ||
          G_INDENT5 ||', L_PROGRAM_ID' || g_newline ||
          G_INDENT5 ||', l_sysdate' || g_newline ||
          G_INDENT5 ||', ILV2.terr_id' || g_newline ||
          G_INDENT5 ||', ILV2.absolute_rank' || g_newline ||
          G_INDENT5 ||', ILV2.top_level_terr_id' || g_newline ||
          G_INDENT5 ||', ILV2.num_winners' || g_newline ||
          G_INDENT5 ||', ILV2.org_id' || g_newline ;

Line: 2196

       lp_SELECT_cols :=
          G_INDENT5 || 'A.trans_object_id, A.trans_detail_object_id, ' || g_newline ||
          -- eihsu 06/19/2003 worker_id
          G_INDENT5 || 'A.worker_id, ' || g_newline ||
          G_INDENT5 || 'A.header_id1, A.header_id2, ' || g_newline ||
          G_INDENT5 || 'ILV.terr_id, ILV.absolute_rank, ' || g_newline ||
          G_INDENT5 || 'ILV.top_level_terr_id, ILV.num_winners, ILV.org_id ';

Line: 2232

      l_qual_rules => l_select_ilv2
       );

Line: 2237

    G_INDENT5 || 'FROM ( ' || g_newline || EXP_PURCHASE_UNION_SELECT(p_new_mode_fetch)
    );

Line: 2261

         G_INDENT5 || 'SELECT /*+ ' || g_newline ||
         G_INDENT5 || '           USE_NL(ILV Q1142R1 ALLP) ' || g_newline ||
         G_INDENT5 || '           INDEX(ALLP AS_LEAD_LINES_N2)' || g_newline ||
         G_INDENT5 || '           INDEX(ILV JTF_TERR_DENORM_RULES_N4) ' || g_newline ||
         G_INDENT5 || '       */ ' || g_newline ||
         G_INDENT5 || '                      ALLP.lead_id' || g_newline ||
         G_INDENT5 || '                    , ALLP.lead_line_id' || g_newline ||
         G_INDENT5 || '                    , ILV.terr_id ' || g_newline ||
         G_INDENT5 || '                     , ILV.top_level_terr_id ' || g_newline ||
         G_INDENT5 || '                     , ILV.absolute_rank  ' || g_newline ||
         G_INDENT5 || '                     , ILV.num_winners ' || g_newline ||
         G_INDENT5 || '                     , ILV.org_id ' || g_newline ||
         G_INDENT5 || '                 FROM AS_LEAD_LINES ALLP, eni_prod_denorm_hrchy_v prd, ' || g_newline ||
         G_INDENT5 || '                      jtf_terr_qual_rules_mv Q1142R1, ' || g_newline ||

         /* STATIC INLINE VIEW */
         add_ILV_with_NOMERGE_hint(l_sql, p_new_mode_fetch) ||

         G_INDENT5 || '                WHERE  (  Q1142R1.qual_usg_id = -1142 AND Q1142R1.terr_id = ILV.terr_id )' || g_newline ||
         G_INDENT5 || '                AND   Q1142R1.value1_id = PRD.child_id ' || g_newline ||
         G_INDENT5 || '                AND   Q1142R1.value2_id = PRD.category_set_id ' || g_newline ||
         G_INDENT5 || '                AND   PRD.parent_id = ALLP.product_category_id ' || g_newline ||
         G_INDENT5 || '                AND   PRD.category_set_id = ALLP.product_cat_set_id ' || g_newline ||
         G_INDENT5 || '                AND   Q1142R1.comparison_operator = ''=''' || g_newline ||
         G_INDENT5 || '   ) ILV1,' || g_newline ||

          /************************/
         /*  = XYZ  ILV2             */
         /************************/
         G_INDENT5 || '   ( ' || g_newline ||
         G_INDENT5 || 'SELECT /*+ ' || g_newline ||
         G_INDENT5 || '           NO_MERGE ' || g_newline ||
         G_INDENT5 || '           ORDERED ' || g_newline ||
         G_INDENT5 || '           USE_CONCAT ' || g_newline;

Line: 2311

         /* Add SELECT columns */
         JTF_TAE_GEN_PVT.write_buffer_content(
      l_qual_rules => lx_934313_sql
       );

Line: 2318

      l_qual_rules => lp_SELECT_cols || g_newline
       );

Line: 2367

   l_select_ilv2               VARCHAR2(32767) := NULL;

Line: 2368

   lp_SELECT_cols              VARCHAR2(32767) := NULL;

Line: 2387

       l_select_ilv2 := add_SELECT_clause(p_new_mode_fetch);

Line: 2389

       lp_SELECT_cols :=
          --G_INDENT5 || add_SELECT_cols(p_new_mode_fetch) || g_newline;

Line: 2544

       l_select_ilv2 :=
          G_INDENT5 || 'SELECT DISTINCT ' || g_newline ||
          G_INDENT5 ||'ILV2.trans_object_id' || g_newline ||
          G_INDENT5 ||', ILV2.trans_detail_object_id' || g_newline ||
          -- eihsu 06/19/2003 worker_id
          G_INDENT5 ||', ILV2.worker_id' || g_newline ||
          G_INDENT5 ||',ILV2.header_id1' || g_newline ||
          G_INDENT5 ||',ILV2.header_id2' || g_newline ||
          G_INDENT5 ||', p_source_id' || g_newline ||
          G_INDENT5 ||', p_trans_object_type_id' || g_newline ||
          G_INDENT5 ||', l_sysdate' || g_newline ||
          G_INDENT5 ||', L_USER_ID' || g_newline ||
          G_INDENT5 ||', l_sysdate' || g_newline ||
          G_INDENT5 ||', L_USER_ID' || g_newline ||
          G_INDENT5 ||', L_USER_ID' || g_newline ||
          G_INDENT5 ||', L_REQUEST_ID' || g_newline ||
          G_INDENT5 ||', L_PROGRAM_APPL_ID' || g_newline ||
          G_INDENT5 ||', L_PROGRAM_ID' || g_newline ||
          G_INDENT5 ||', l_sysdate' || g_newline ||
          G_INDENT5 ||', ILV2.terr_id' || g_newline ||
          G_INDENT5 ||', ILV2.absolute_rank' || g_newline ||
          G_INDENT5 ||', ILV2.top_level_terr_id' || g_newline ||
          G_INDENT5 ||', ILV2.num_winners' || g_newline ||
          G_INDENT5 ||', ILV2.org_id' || g_newline ;

Line: 2569

       lp_SELECT_cols :=
          G_INDENT5 || 'A.trans_object_id, A.trans_detail_object_id, ' || g_newline ||
          -- eihsu 06/19/2003 worker_id
          G_INDENT5 || 'A.worker_id, ' || g_newline ||
          G_INDENT5 || 'A.header_id1, A.header_id2, ' || g_newline ||
          G_INDENT5 || 'ILV.terr_id, ILV.absolute_rank, ' || g_newline ||
          G_INDENT5 || 'ILV.top_level_terr_id, ILV.num_winners, ILV.org_id ';

Line: 2642

      l_qual_rules => l_select_ilv2
       );

Line: 2647

    G_INDENT5 || 'FROM ( ' || g_newline || EXP_PURCHASE_UNION_SELECT(p_new_mode_fetch)
    );

Line: 2671

         G_INDENT5 || 'SELECT /*+ ' || g_newline;

Line: 2719

         G_INDENT5 || 'SELECT /*+ ' || g_newline;

Line: 2743

         /* Add SELECT columns */
         JTF_TAE_GEN_PVT.write_buffer_content(
      l_qual_rules => lx_62598971_sql
       );

Line: 2750

      l_qual_rules => lp_SELECT_cols || g_newline
       );

Line: 2799

    G_INDENT || EXP_PURCHASE_UNION_SELECT(p_new_mode_fetch)
    );

Line: 2822

         G_INDENT5 || 'SELECT /*+ ' || g_newline;

Line: 2870

          G_INDENT5 || 'SELECT /*+ ' || g_newline;

Line: 2898

         /* Add SELECT columns */
         JTF_TAE_GEN_PVT.write_buffer_content(
           l_qual_rules => lp_SELECT_cols || g_newline
         );

Line: 2947

            G_INDENT || EXP_PURCHASE_UNION_SELECT(p_new_mode_fetch)
          );

Line: 2969

         G_INDENT5 || 'SELECT /*+ ' || g_newline;

Line: 3015

         G_INDENT5 || 'SELECT /*+ ' || g_newline;

Line: 3039

         /* Add SELECT columns */
         lp_SELECT_cols || g_newline ||

         G_INDENT5 || 'FROM   ';

Line: 3072

         G_INDENT5 || 'SELECT /*+ ' || g_newline;

Line: 3096

         /* Add SELECT columns */
         lp_SELECT_cols || g_newline ||

         G_INDENT5 || 'FROM   ';

Line: 3142

            G_INDENT || EXP_PURCHASE_UNION_SELECT(p_new_mode_fetch)
          );

Line: 3164

         G_INDENT5 || 'SELECT /*+ ' || g_newline;

Line: 3207

         G_INDENT5 || 'SELECT /*+ ' || g_newline;

Line: 3233

         /* Add SELECT columns */
         lp_SELECT_cols || g_newline ||

         G_INDENT5 || 'FROM   ';

Line: 3290

   l_select_ilv2               VARCHAR2(32767) := NULL;

Line: 3291

   lp_SELECT_cols              VARCHAR2(32767) := NULL;

Line: 3310

       l_select_ilv2 := add_SELECT_clause(p_new_mode_fetch);

Line: 3312

       lp_SELECT_cols :=
          --G_INDENT5 || add_SELECT_cols(p_new_mode_fetch) || g_newline;

Line: 3467

       l_select_ilv2 :=
          G_INDENT5 || 'SELECT DISTINCT ' || g_newline ||
          G_INDENT5 ||'ILV2.trans_object_id' || g_newline ||
          G_INDENT5 ||', ILV2.trans_detail_object_id' || g_newline ||
          -- eihsu 06/19/2003 worker_id
          G_INDENT5 ||', ILV2.worker_id' || g_newline ||
          G_INDENT5 ||',ILV2.header_id1' || g_newline ||
          G_INDENT5 ||',ILV2.header_id2' || g_newline ||
          G_INDENT5 ||', p_source_id' || g_newline ||
          G_INDENT5 ||', p_trans_object_type_id' || g_newline ||
          G_INDENT5 ||', l_sysdate' || g_newline ||
          G_INDENT5 ||', L_USER_ID' || g_newline ||
          G_INDENT5 ||', l_sysdate' || g_newline ||
          G_INDENT5 ||', L_USER_ID' || g_newline ||
          G_INDENT5 ||', L_USER_ID' || g_newline ||
          G_INDENT5 ||', L_REQUEST_ID' || g_newline ||
          G_INDENT5 ||', L_PROGRAM_APPL_ID' || g_newline ||
          G_INDENT5 ||', L_PROGRAM_ID' || g_newline ||
          G_INDENT5 ||', l_sysdate' || g_newline ||
          G_INDENT5 ||', ILV2.terr_id' || g_newline ||
          G_INDENT5 ||', ILV2.absolute_rank' || g_newline ||
          G_INDENT5 ||', ILV2.top_level_terr_id' || g_newline ||
          G_INDENT5 ||', ILV2.num_winners' || g_newline ||
          G_INDENT5 ||', ILV2.org_id' || g_newline ;

Line: 3492

       lp_SELECT_cols :=
          G_INDENT5 || 'A.trans_object_id, A.trans_detail_object_id, ' || g_newline ||
          -- eihsu 06/19/2003 worker_id
          G_INDENT5 || 'A.worker_id, ' || g_newline ||
          G_INDENT5 || 'A.header_id1, A.header_id2, ' || g_newline ||
          G_INDENT5 || 'ILV.terr_id, ILV.absolute_rank, ' || g_newline ||
          G_INDENT5 || 'ILV.top_level_terr_id, ILV.num_winners, ILV.org_id ';

Line: 3565

      l_qual_rules => l_select_ilv2
       );

Line: 3570

    G_INDENT5 || 'FROM ( ' || g_newline || EXP_PURCHASE_UNION_SELECT(p_new_mode_fetch)
    );

Line: 3594

         G_INDENT5 || 'SELECT /*+ ' || g_newline ||
         G_INDENT5 || '           USE_NL(ILV Q1023R1 ALLP) ' || g_newline ||
         G_INDENT5 || '           INDEX(ALLP AS_LEAD_LINES_N2)' || g_newline ||
         G_INDENT5 || '           INDEX(ILV JTF_TERR_DENORM_RULES_N4) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1023R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '       */ ' || g_newline ||
         G_INDENT5 || '                      ALLP.lead_id' || g_newline ||
         G_INDENT5 || '                    , ALLP.lead_line_id' || g_newline ||
         G_INDENT5 || '                    , ILV.terr_id ' || g_newline ||
         G_INDENT5 || '                     , ILV.top_level_terr_id ' || g_newline ||
         G_INDENT5 || '                     , ILV.absolute_rank  ' || g_newline ||
         G_INDENT5 || '                     , ILV.num_winners ' || g_newline ||
         G_INDENT5 || '                     , ILV.org_id ' || g_newline ||
         G_INDENT5 || '                 FROM AS_LEAD_LINES ALLP, jtf_terr_qual_rules_mv Q1023R1' || g_newline ||
         G_INDENT5 || '                     , /* INLINE VIEW */' || g_newline ||
         G_INDENT5 || '                     ( SELECT /*+ NO_MERGE */' || g_newline ||
         G_INDENT5 || '                             jtdr.terr_id ' || g_newline ||
         G_INDENT5 || '                           , jtdr.source_id' || g_newline ||
         G_INDENT5 || '                             , jtdr.qual_type_id' || g_newline ||
         G_INDENT5 || '                            , jtdr.top_level_terr_id' || g_newline ||
         G_INDENT5 || '                             , jtdr.absolute_rank ' || g_newline ||
         G_INDENT5 || '                            , jtdr.num_winners ' || g_newline ||
         G_INDENT5 || '                            , jtdr.org_id' || g_newline ||
         G_INDENT5 || '                        FROM  jtf_terr_denorm_rules_all jtdr' || g_newline ||
         G_INDENT5 || '                             ,jtf_terr_qtype_usgs_all jtqu   ' || g_newline ||
         G_INDENT5 || '                             ,jtf_qual_type_usgs_all jqtu    ' || g_newline ||
         G_INDENT5 || '                        WHERE jtdr.source_id = p_source_id' || g_newline ||
         G_INDENT5 || '                         AND jtdr.terr_id= jtdr.related_terr_id' || g_newline ||
         G_INDENT5 || '                         AND jqtu.source_id = jtdr.source_id    ' || g_newline ||
         G_INDENT5 || '                         AND jqtu.qual_type_id = p_trans_object_type_id ' || g_newline ||
         G_INDENT5 || '                         AND jtdr.terr_id = jtqu.terr_id ' || g_newline ||
         G_INDENT5 || '                         AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id ' || g_newline ||
         G_INDENT5 || '                         AND jtdr.resource_exists_flag = ''Y''' || g_newline ||
         G_INDENT5 || '                         AND jtqu.qual_relation_product = lp_qual_combination_tbl(i)' || g_newline ||
         G_INDENT5 || '                    ) ILV' || g_newline ||
         G_INDENT5 || '                WHERE  (  Q1023R1.qual_usg_id = -1023 AND Q1023R1.terr_id = ILV.terr_id )' || g_newline ||
         G_INDENT5 || '    AND ( Q1023R1.secondary_interest_code_id IS NULL' || g_newline ||
         G_INDENT5 || '    OR (ALLP.secondary_interest_code_id = Q1023R1.secondary_interest_code_id))' || g_newline ||
         G_INDENT5 || '   AND ( Q1023R1.primary_interest_code_id IS NULL' || g_newline ||
         G_INDENT5 || '   OR ( ALLP.primary_interest_code_id = Q1023R1.primary_interest_code_id ))' || g_newline ||
         G_INDENT5 || '   AND ALLP.interest_type_id =  Q1023R1.interest_type_id' || g_newline ||
         G_INDENT5 || '    AND Q1023R1.comparison_operator = ''=''' || g_newline ||
         G_INDENT5 || '   ) ILV1,' || g_newline ||

          /************************/
         /*  = XYZ  ILV2             */
         /************************/
         G_INDENT5 || '   ( ' || g_newline ||
         G_INDENT5 || 'SELECT /*+ ' || g_newline ||
         G_INDENT5 || '           ORDERED ' || g_newline ||
         G_INDENT5 || '           USE_CONCAT ' || g_newline ||
         G_INDENT5 || '           USE_NL(ILV Q1003R1 Q1007R1 Q1012R1 A) ' || g_newline ||
         G_INDENT5 || '           INDEX(ILV JTF_TERR_DENORM_RULES_N4) ' || g_newline ||
                  /* DYNAMIC HINT BASED ON TRANSACTION TYPE */
         G_INDENT5 || '           INDEX(A JTF_TAE_TN' || ABS(p_trans_object_type_id) ||
                                                        '_324347_ND)' || g_newline ||

         G_INDENT5 || '           INDEX(Q1003R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1007R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1012R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '       */ ' || g_newline ; --||

Line: 3656

         /* Add SELECT columns */
         JTF_TAE_GEN_PVT.write_buffer_content(
      l_qual_rules => lx_45084233_sql
       );

Line: 3663

      l_qual_rules => lp_SELECT_cols || g_newline
       );

Line: 3704

    G_INDENT || EXP_PURCHASE_UNION_SELECT(p_new_mode_fetch)
    );

Line: 3727

         G_INDENT5 || 'SELECT /*+ ' || g_newline ||
         G_INDENT5 || '           USE_NL(ILV Q1023R1 ALLP) ' || g_newline ||
         G_INDENT5 || '           INDEX(ALLP AS_LEAD_LINES_N2)' || g_newline ||
         G_INDENT5 || '           INDEX(ILV JTF_TERR_DENORM_RULES_N4) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1023R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '       */ ' || g_newline ||
         G_INDENT5 || '                      ALLP.lead_id' || g_newline ||
         G_INDENT5 || '                    , ALLP.lead_line_id' || g_newline ||
         G_INDENT5 || '                    , ILV.terr_id ' || g_newline ||
         G_INDENT5 || '                     , ILV.top_level_terr_id ' || g_newline ||
         G_INDENT5 || '                     , ILV.absolute_rank  ' || g_newline ||
         G_INDENT5 || '                     , ILV.num_winners ' || g_newline ||
         G_INDENT5 || '                     , ILV.org_id ' || g_newline ||
         G_INDENT5 || '                 FROM AS_LEAD_LINES ALLP, jtf_terr_qual_rules_mv Q1023R1' || g_newline ||
         G_INDENT5 || '                     , /* INLINE VIEW */' || g_newline ||
         G_INDENT5 || '                     ( SELECT /*+ NO_MERGE */' || g_newline ||
         G_INDENT5 || '                             jtdr.terr_id ' || g_newline ||
         G_INDENT5 || '                           , jtdr.source_id' || g_newline ||
         G_INDENT5 || '                             , jtdr.qual_type_id' || g_newline ||
         G_INDENT5 || '                            , jtdr.top_level_terr_id' || g_newline ||
         G_INDENT5 || '                             , jtdr.absolute_rank ' || g_newline ||
         G_INDENT5 || '                            , jtdr.num_winners ' || g_newline ||
         G_INDENT5 || '                            , jtdr.org_id' || g_newline ||
         G_INDENT5 || '                        FROM  jtf_terr_denorm_rules_all jtdr' || g_newline ||
         G_INDENT5 || '                             ,jtf_terr_qtype_usgs_all jtqu   ' || g_newline ||
         G_INDENT5 || '                             ,jtf_qual_type_usgs_all jqtu    ' || g_newline ||
         G_INDENT5 || '                        WHERE jtdr.source_id = p_source_id' || g_newline ||
         G_INDENT5 || '                         AND jtdr.terr_id= jtdr.related_terr_id' || g_newline ||
         G_INDENT5 || '                         AND jqtu.source_id = jtdr.source_id    ' || g_newline ||
         G_INDENT5 || '                         AND jqtu.qual_type_id = p_trans_object_type_id ' || g_newline ||
         G_INDENT5 || '                         AND jtdr.terr_id = jtqu.terr_id ' || g_newline ||
         G_INDENT5 || '                         AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id ' || g_newline ||
         G_INDENT5 || '                         AND jtdr.resource_exists_flag = ''Y''' || g_newline ||
         G_INDENT5 || '                         AND jtqu.qual_relation_product = lp_qual_combination_tbl(i)' || g_newline ||
         G_INDENT5 || '                    ) ILV' || g_newline ||
         G_INDENT5 || '                WHERE  (  Q1023R1.qual_usg_id = -1023 AND Q1023R1.terr_id = ILV.terr_id )' || g_newline ||
         G_INDENT5 || '    AND ( Q1023R1.secondary_interest_code_id IS NULL' || g_newline ||
         G_INDENT5 || '    OR (ALLP.secondary_interest_code_id = Q1023R1.secondary_interest_code_id))' || g_newline ||
         G_INDENT5 || '   AND ( Q1023R1.primary_interest_code_id IS NULL' || g_newline ||
         G_INDENT5 || '   OR ( ALLP.primary_interest_code_id = Q1023R1.primary_interest_code_id ))' || g_newline ||
         G_INDENT5 || '   AND ALLP.interest_type_id =  Q1023R1.interest_type_id' || g_newline ||
         G_INDENT5 || '    AND Q1023R1.comparison_operator = ''=''' || g_newline ||
         G_INDENT5 || '   ) ILV1,' || g_newline ||


         /************************/
         /*  LIKE XYZ%           */
         /************************/
         G_INDENT5 || '   ( ' || g_newline ||
          G_INDENT5 || 'SELECT /*+ ' || g_newline ||

         G_INDENT5 || '           ORDERED ' || g_newline ||
         G_INDENT5 || '           USE_CONCAT ' || g_newline ||
         G_INDENT5 || '           USE_NL(ILV Q1003R1 Q1007R1 Q1012LK A)  ' || g_newline ||
         G_INDENT5 || '           INDEX(ILV JTF_TERR_DENORM_RULES_N4) ' || g_newline ||

         /* DYNAMIC HINT BASED ON TRANSACTION TYPE */
         G_INDENT5 || '           INDEX(A JTF_TAE_TN' || ABS(p_trans_object_type_id) ||
                                                      '_324347_ND)' || g_newline ||
         G_INDENT5 || '           INDEX(Q1003R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1007R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1012LK JTF_TERR_CNR_QUAL_LIKE_MV_N10) ' || g_newline ||
         G_INDENT5 || '       */ ' || g_newline ; -- ||

Line: 3795

         /* Add SELECT columns */
         JTF_TAE_GEN_PVT.write_buffer_content(
           l_qual_rules => lp_SELECT_cols || g_newline
         );

Line: 3836

            G_INDENT || EXP_PURCHASE_UNION_SELECT(p_new_mode_fetch)
          );

Line: 3858

         G_INDENT5 || 'SELECT /*+ ' || g_newline ||
         G_INDENT5 || '           USE_NL(ILV Q1023R1 ALLP) ' || g_newline ||
         G_INDENT5 || '           INDEX(ALLP AS_LEAD_LINES_N2)' || g_newline ||
         G_INDENT5 || '           INDEX(ILV JTF_TERR_DENORM_RULES_N4) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1023R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '       */ ' || g_newline ||
         G_INDENT5 || '                      ALLP.lead_id' || g_newline ||
         G_INDENT5 || '                    , ALLP.lead_line_id' || g_newline ||
         G_INDENT5 || '                    , ILV.terr_id ' || g_newline ||
         G_INDENT5 || '                     , ILV.top_level_terr_id ' || g_newline ||
         G_INDENT5 || '                     , ILV.absolute_rank  ' || g_newline ||
         G_INDENT5 || '                     , ILV.num_winners ' || g_newline ||
         G_INDENT5 || '                     , ILV.org_id ' || g_newline ||
         G_INDENT5 || '                 FROM AS_LEAD_LINES ALLP, jtf_terr_qual_rules_mv Q1023R1' || g_newline ||
         G_INDENT5 || '                     , /* INLINE VIEW */' || g_newline ||
         G_INDENT5 || '                     ( SELECT /*+ NO_MERGE */' || g_newline ||
         G_INDENT5 || '                             jtdr.terr_id ' || g_newline ||
         G_INDENT5 || '                           , jtdr.source_id' || g_newline ||
         G_INDENT5 || '                             , jtdr.qual_type_id' || g_newline ||
         G_INDENT5 || '                            , jtdr.top_level_terr_id' || g_newline ||
         G_INDENT5 || '                             , jtdr.absolute_rank ' || g_newline ||
         G_INDENT5 || '                            , jtdr.num_winners ' || g_newline ||
         G_INDENT5 || '                            , jtdr.org_id' || g_newline ||
         G_INDENT5 || '                        FROM  jtf_terr_denorm_rules_all jtdr' || g_newline ||
         G_INDENT5 || '                             ,jtf_terr_qtype_usgs_all jtqu   ' || g_newline ||
         G_INDENT5 || '                             ,jtf_qual_type_usgs_all jqtu    ' || g_newline ||
         G_INDENT5 || '                        WHERE jtdr.source_id = p_source_id' || g_newline ||
         G_INDENT5 || '                         AND jtdr.terr_id= jtdr.related_terr_id' || g_newline ||
         G_INDENT5 || '                         AND jqtu.source_id = jtdr.source_id    ' || g_newline ||
         G_INDENT5 || '                         AND jqtu.qual_type_id = p_trans_object_type_id ' || g_newline ||
         G_INDENT5 || '                         AND jtdr.terr_id = jtqu.terr_id ' || g_newline ||
         G_INDENT5 || '                         AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id ' || g_newline ||
         G_INDENT5 || '                         AND jtdr.resource_exists_flag = ''Y''' || g_newline ||
         G_INDENT5 || '                         AND jtqu.qual_relation_product = lp_qual_combination_tbl(i)' || g_newline ||
         G_INDENT5 || '                    ) ILV' || g_newline ||
         G_INDENT5 || '                WHERE  (  Q1023R1.qual_usg_id = -1023 AND Q1023R1.terr_id = ILV.terr_id )' || g_newline ||
         G_INDENT5 || '    AND ( Q1023R1.secondary_interest_code_id IS NULL' || g_newline ||
         G_INDENT5 || '    OR (ALLP.secondary_interest_code_id = Q1023R1.secondary_interest_code_id))' || g_newline ||
         G_INDENT5 || '   AND ( Q1023R1.primary_interest_code_id IS NULL' || g_newline ||
         G_INDENT5 || '   OR ( ALLP.primary_interest_code_id = Q1023R1.primary_interest_code_id ))' || g_newline ||
         G_INDENT5 || '   AND ALLP.interest_type_id =  Q1023R1.interest_type_id' || g_newline ||
         G_INDENT5 || '    AND Q1023R1.comparison_operator = ''=''' || g_newline ||
         G_INDENT5 || '   ) ILV1,' || g_newline ||

         /************************/
         /*  LIKE %XYZ           */
         /************************/
         G_INDENT5 || '   ( ' || g_newline ||
         G_INDENT5 || 'SELECT /*+ ' || g_newline ||

         G_INDENT5 || '           ORDERED ' || g_newline ||
         G_INDENT5 || '           USE_CONCAT ' || g_newline ||
         G_INDENT5 || '           USE_NL(ILV Q1003R1 Q1007R1 Q1012LK A)  ' || g_newline ||
         G_INDENT5 || '           INDEX(ILV JTF_TERR_DENORM_RULES_N4) ' || g_newline ||
         /* DYNAMIC HINT BASED ON TRANSACTION TYPE */
         G_INDENT5 || '           INDEX(A JTF_TAE_TN' || ABS(p_trans_object_type_id) ||
                                                        '_324347X_ND)' || g_newline ||
         G_INDENT5 || '           INDEX(Q1003R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1007R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1012LK JTF_TERR_CNR_QUAL_LIKE_MV_N10) ' || g_newline ||
         G_INDENT5 || '       */ ' || g_newline ||

         /* Add SELECT columns */
         lp_SELECT_cols || g_newline ||

         G_INDENT5 || 'FROM   '||

         /* STATIC INLINE VIEW */
         add_ILV(l_sql, p_new_mode_fetch) ||

         G_INDENT5 || '   ,  jtf_terr_cnr_qual_like_mv Q1012LK ' || g_newline ||
         G_INDENT5 || '   ,  jtf_terr_qual_rules_mv Q1007R1 ' || g_newline ||
         G_INDENT5 || '   ,  jtf_terr_qual_rules_mv Q1003R1 ' || g_newline ||

         /* DYNAMIC BASED ON TRANSACTION TYPE */
         G_INDENT || '    ,' || P_TABLE_NAME || ' A ' || g_newline ||

         G_INDENT5 || 'WHERE ' || g_newline ||

         lp_pc_cntry_predicate_eq || g_newline ||

         G_INDENT5 || '  AND Q1007R1.terr_id = Q1012LK.terr_id ' || g_newline ||
         G_INDENT5 || '  AND      a.squal_char01 LIKE Q1012LK.low_value_char AND ' || g_newline ||
         G_INDENT5 || '        Q1012LK.first_char = ''%'' ' || g_newline ||
         G_INDENT5 || '  AND Q1012LK.qual_usg_id = -1012 ' || g_newline ||
         G_INDENT5 || '  AND Q1012LK.terr_id = ILV.terr_id ' || g_newline ||
         G_INDENT5 || 'UNION ALL ' || g_newline ||
         G_INDENT5 || 'SELECT /*+ ' || g_newline ||

         G_INDENT5 || '           ORDERED ' || g_newline ||
         G_INDENT5 || '           USE_CONCAT ' || g_newline ||
         G_INDENT5 || '           USE_NL(ILV Q1003R1 Q1007R1 Q1012LK A)  ' || g_newline ||
         G_INDENT5 || '           INDEX(ILV JTF_TERR_DENORM_RULES_N4) ' || g_newline ||
         /* DYNAMIC HINT BASED ON TRANSACTION TYPE */
         G_INDENT5 || '           INDEX(A JTF_TAE_TN' || ABS(p_trans_object_type_id) ||
                                                        '_324347X_ND)' || g_newline ||
         G_INDENT5 || '           INDEX(Q1003R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1007R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1012LK JTF_TERR_CNR_QUAL_LIKE_MV_N10) ' || g_newline ||
         G_INDENT5 || '       */ ' || g_newline ||

         /* Add SELECT columns */
         lp_SELECT_cols || g_newline ||

         G_INDENT5 || 'FROM   '||

         /* STATIC INLINE VIEW */
         add_ILV(l_sql, p_new_mode_fetch) ||

         G_INDENT5 || '   ,  jtf_terr_cnr_qual_like_mv Q1012LK ' || g_newline ||
         G_INDENT5 || '   ,  jtf_terr_qual_rules_mv Q1007R1 ' || g_newline ||
         G_INDENT5 || '   ,  jtf_terr_qual_rules_mv Q1003R1 ' || g_newline ||

         /* DYNAMIC BASED ON TRANSACTION TYPE */
         G_INDENT || '    ,' || P_TABLE_NAME || ' A ' || g_newline ||

         G_INDENT5 || 'WHERE ' || g_newline ||

         lp_pc_cntry_predicate_btwn || g_newline ||

         G_INDENT5 || '  AND Q1007R1.terr_id = Q1012LK.terr_id ' || g_newline ||
         G_INDENT5 || '  AND      a.squal_char01 LIKE Q1012LK.low_value_char AND ' || g_newline ||
         G_INDENT5 || '        Q1012LK.first_char = ''%'' ' || g_newline ||
         G_INDENT5 || '  AND Q1012LK.qual_usg_id = -1012 ' || g_newline ||
         G_INDENT5 || '  AND Q1012LK.terr_id = ILV.terr_id ' || g_newline ||

         G_INDENT5 || ') ILV2' || g_newline ||
         G_INDENT5 || 'WHERE ILV1.terr_id = ILV2.terr_id' || g_newline ||
         G_INDENT5 || 'AND ILV1.lead_id = ILV2.trans_object_id' || g_newline ||

         G_INDENT5 || 'UNION ALL ' || g_newline ;--||

Line: 3997

            G_INDENT || EXP_PURCHASE_UNION_SELECT(p_new_mode_fetch)
          );

Line: 4019

         G_INDENT5 || 'SELECT /*+ ' || g_newline ||
         G_INDENT5 || '           USE_NL(ILV Q1023R1 ALLP) ' || g_newline ||
         G_INDENT5 || '           INDEX(ALLP AS_LEAD_LINES_N2)' || g_newline ||
         G_INDENT5 || '           INDEX(ILV JTF_TERR_DENORM_RULES_N4) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1023R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '       */ ' || g_newline ||
         G_INDENT5 || '                      ALLP.lead_id' || g_newline ||
         G_INDENT5 || '                    , ALLP.lead_line_id' || g_newline ||
         G_INDENT5 || '                    , ILV.terr_id ' || g_newline ||
         G_INDENT5 || '                     , ILV.top_level_terr_id ' || g_newline ||
         G_INDENT5 || '                     , ILV.absolute_rank  ' || g_newline ||
         G_INDENT5 || '                     , ILV.num_winners ' || g_newline ||
         G_INDENT5 || '                     , ILV.org_id ' || g_newline ||
         G_INDENT5 || '                 FROM AS_LEAD_LINES ALLP, jtf_terr_qual_rules_mv Q1023R1' || g_newline ||
         G_INDENT5 || '                     , /* INLINE VIEW */' || g_newline ||
         G_INDENT5 || '                     ( SELECT /*+ NO_MERGE */' || g_newline ||
         G_INDENT5 || '                             jtdr.terr_id ' || g_newline ||
         G_INDENT5 || '                           , jtdr.source_id' || g_newline ||
         G_INDENT5 || '                             , jtdr.qual_type_id' || g_newline ||
         G_INDENT5 || '                            , jtdr.top_level_terr_id' || g_newline ||
         G_INDENT5 || '                             , jtdr.absolute_rank ' || g_newline ||
         G_INDENT5 || '                            , jtdr.num_winners ' || g_newline ||
         G_INDENT5 || '                            , jtdr.org_id' || g_newline ||
         G_INDENT5 || '                        FROM  jtf_terr_denorm_rules_all jtdr' || g_newline ||
         G_INDENT5 || '                             ,jtf_terr_qtype_usgs_all jtqu   ' || g_newline ||
         G_INDENT5 || '                             ,jtf_qual_type_usgs_all jqtu    ' || g_newline ||
         G_INDENT5 || '                        WHERE jtdr.source_id = p_source_id' || g_newline ||
         G_INDENT5 || '                         AND jtdr.terr_id= jtdr.related_terr_id' || g_newline ||
         G_INDENT5 || '                         AND jqtu.source_id = jtdr.source_id    ' || g_newline ||
         G_INDENT5 || '                         AND jqtu.qual_type_id = p_trans_object_type_id ' || g_newline ||
         G_INDENT5 || '                         AND jtdr.terr_id = jtqu.terr_id ' || g_newline ||
         G_INDENT5 || '                         AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id ' || g_newline ||
         G_INDENT5 || '                         AND jtdr.resource_exists_flag = ''Y''' || g_newline ||
         G_INDENT5 || '                         AND jtqu.qual_relation_product = lp_qual_combination_tbl(i)' || g_newline ||
         G_INDENT5 || '                    ) ILV' || g_newline ||
         G_INDENT5 || '                WHERE  (  Q1023R1.qual_usg_id = -1023 AND Q1023R1.terr_id = ILV.terr_id )' || g_newline ||
         G_INDENT5 || '    AND ( Q1023R1.secondary_interest_code_id IS NULL' || g_newline ||
         G_INDENT5 || '    OR (ALLP.secondary_interest_code_id = Q1023R1.secondary_interest_code_id))' || g_newline ||
         G_INDENT5 || '   AND ( Q1023R1.primary_interest_code_id IS NULL' || g_newline ||
         G_INDENT5 || '   OR ( ALLP.primary_interest_code_id = Q1023R1.primary_interest_code_id ))' || g_newline ||
         G_INDENT5 || '   AND ALLP.interest_type_id =  Q1023R1.interest_type_id' || g_newline ||
         G_INDENT5 || '    AND Q1023R1.comparison_operator = ''=''' || g_newline ||
         G_INDENT5 || '   ) ILV1,' || g_newline ||

         G_INDENT5 || '   ( ' || g_newline ||
         G_INDENT5 || 'SELECT /*+ ' || g_newline ||
         G_INDENT5 || '           ORDERED ' || g_newline ||
         G_INDENT5 || '           USE_CONCAT ' || g_newline ||
         G_INDENT5 || '           USE_NL(ILV Q1003R1 Q1007R1 Q1012BT A) ' || g_newline ||
         G_INDENT5 || '           INDEX(ILV JTF_TERR_DENORM_RULES_N4) ' || g_newline ||
         /* DYNAMIC HINT BASED ON TRANSACTION TYPE */
         G_INDENT5 || '           INDEX(A JTF_TAE_TN' || ABS(p_trans_object_type_id) ||
                                                        '_324347_ND)' || g_newline ||

         G_INDENT5 || '           INDEX(Q1003R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1007R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1012BT JTF_TERR_CNR_QUAL_BTWN_MV_N10) ' || g_newline ||
         G_INDENT5 || '       */ ' || g_newline ||

         /* Add SELECT columns */
         lp_SELECT_cols || g_newline ||

         G_INDENT5 || 'FROM   '||

         /* STATIC INLINE VIEW */
         add_ILV(l_sql, p_new_mode_fetch) ||

         G_INDENT5 || '   ,  jtf_terr_cnr_qual_btwn_mv Q1012BT ' || g_newline ||
         G_INDENT5 || '   ,  jtf_terr_qual_rules_mv Q1007R1 ' || g_newline ||
         G_INDENT5 || '   ,  jtf_terr_qual_rules_mv Q1003R1 ' || g_newline ||

         /* DYNAMIC BASED ON TRANSACTION TYPE */
         G_INDENT || '    ,' || P_TABLE_NAME || ' A ' || g_newline ||

         G_INDENT5 || 'WHERE ' || g_newline ||

         lp_pc_cntry_predicate || g_newline ||

         G_INDENT5 || '  AND Q1007R1.terr_id = Q1012BT.terr_id ' || g_newline ||
         G_INDENT5 || '  AND      a.squal_char01 <= Q1012BT.high_value_char AND ' || g_newline ||
         G_INDENT5 || '        a.squal_char01 >= Q1012BT.low_value_char AND ' || g_newline ||
         G_INDENT5 || '        a.squal_fc01 >= SUBSTR(Q1012BT.low_value_char, 1, 1) ' || g_newline ||
         G_INDENT5 || '  AND Q1012BT.qual_usg_id = -1012 ' || g_newline ||
         G_INDENT5 || '  AND Q1012BT.terr_id = ILV.terr_id ' || g_newline ||
         G_INDENT5 || '  AND Q1012BT.terr_id = Q1003R1.terr_id ' || g_newline ||
         G_INDENT5 || ') ILV2' || g_newline ||
         G_INDENT5 || 'WHERE ILV1.terr_id = ILV2.terr_id' || g_newline ||
         G_INDENT5 || 'AND ILV1.lead_id = ILV2.trans_object_id' || g_newline ||
         G_INDENT || lp_close_outermost_ILV || g_newline;

Line: 4127

   l_select_ilv2               VARCHAR2(32767) := NULL;

Line: 4128

   lp_SELECT_cols              VARCHAR2(32767) := NULL;

Line: 4145

       l_select_ilv2 := add_SELECT_clause(p_new_mode_fetch);

Line: 4148

       lp_SELECT_cols :=
          --G_INDENT5 || add_SELECT_cols(p_new_mode_fetch) || g_newline;

Line: 4304

       l_select_ilv2 :=
          G_INDENT5 || 'SELECT DISTINCT ' || g_newline ||
          G_INDENT5 ||'ILV2.trans_object_id' || g_newline ||
          G_INDENT5 ||', ILV2.trans_detail_object_id' || g_newline ||
         -- eihsu 06/19/2003 worker_id
          G_INDENT5 ||',ILV2.worker_id' || g_newline ||
          G_INDENT5 ||',ILV2.header_id1' || g_newline ||
          G_INDENT5 ||',ILV2.header_id2' || g_newline ||
          G_INDENT5 ||', p_source_id' || g_newline ||
          G_INDENT5 ||', p_trans_object_type_id' || g_newline ||
          G_INDENT5 ||', l_sysdate' || g_newline ||
          G_INDENT5 ||', L_USER_ID' || g_newline ||
          G_INDENT5 ||', l_sysdate' || g_newline ||
          G_INDENT5 ||', L_USER_ID' || g_newline ||
          G_INDENT5 ||', L_USER_ID' || g_newline ||
          G_INDENT5 ||', L_REQUEST_ID' || g_newline ||
          G_INDENT5 ||', L_PROGRAM_APPL_ID' || g_newline ||
          G_INDENT5 ||', L_PROGRAM_ID' || g_newline ||
          G_INDENT5 ||', l_sysdate' || g_newline ||
          G_INDENT5 ||', ILV2.terr_id' || g_newline ||
          G_INDENT5 ||', ILV2.absolute_rank' || g_newline ||
          G_INDENT5 ||', ILV2.top_level_terr_id' || g_newline ||
          G_INDENT5 ||', ILV2.num_winners' || g_newline ||
          G_INDENT5 ||', ILV2.org_id' || g_newline ;

Line: 4331

       lp_SELECT_cols :=
          G_INDENT5 || 'A.trans_object_id, A.trans_detail_object_id, ' || g_newline ||
          -- eihsu 06/19/2003 worker_id
          G_INDENT5 || 'A.worker_id, ' || g_newline ||
          G_INDENT5 || 'A.header_id1, A.header_id2, ' || g_newline ||
          G_INDENT5 || 'ILV.terr_id, ILV.absolute_rank, ' || g_newline ||
          G_INDENT5 || 'ILV.top_level_terr_id, ILV.num_winners, ILV.org_id ';

Line: 4365

      l_qual_rules => l_select_ilv2
       );

Line: 4372

    G_INDENT5 || 'FROM ( ' || g_newline || EXP_PURCHASE_UNION_SELECT(p_new_mode_fetch)
    );

Line: 4386

         G_INDENT5 || 'SELECT /*+ ' || g_newline;

Line: 4415

         /* Add SELECT columns */
         --ARPATEL 10/14 bug#3207518
      JTF_TAE_GEN_PVT.write_buffer_content(
      l_qual_rules => lp_SELECT_cols || g_newline
       );

Line: 4452

         G_INDENT5 || 'SELECT /*+ ' || g_newline;

Line: 4515

   l_select_ilv2               VARCHAR2(32767) := NULL;

Line: 4516

   lp_SELECT_cols              VARCHAR2(32767) := NULL;

Line: 4536

       l_select_ilv2 := add_SELECT_clause(p_new_mode_fetch);

Line: 4539

       lp_SELECT_cols :=
          --G_INDENT5 || add_SELECT_cols(p_new_mode_fetch) || g_newline;

Line: 4695

       l_select_ilv2 :=
          G_INDENT5 || 'SELECT DISTINCT ' || g_newline ||
          G_INDENT5 ||'ILV2.trans_object_id' || g_newline ||
          G_INDENT5 ||', ILV2.trans_detail_object_id' || g_newline ||
         -- eihsu 06/19/2003 worker_id
          G_INDENT5 ||',ILV2.worker_id' || g_newline ||
          G_INDENT5 ||',ILV2.header_id1' || g_newline ||
          G_INDENT5 ||',ILV2.header_id2' || g_newline ||
          G_INDENT5 ||', p_source_id' || g_newline ||
          G_INDENT5 ||', p_trans_object_type_id' || g_newline ||
          G_INDENT5 ||', l_sysdate' || g_newline ||
          G_INDENT5 ||', L_USER_ID' || g_newline ||
          G_INDENT5 ||', l_sysdate' || g_newline ||
          G_INDENT5 ||', L_USER_ID' || g_newline ||
          G_INDENT5 ||', L_USER_ID' || g_newline ||
          G_INDENT5 ||', L_REQUEST_ID' || g_newline ||
          G_INDENT5 ||', L_PROGRAM_APPL_ID' || g_newline ||
          G_INDENT5 ||', L_PROGRAM_ID' || g_newline ||
          G_INDENT5 ||', l_sysdate' || g_newline ||
          G_INDENT5 ||', ILV2.terr_id' || g_newline ||
          G_INDENT5 ||', ILV2.absolute_rank' || g_newline ||
          G_INDENT5 ||', ILV2.top_level_terr_id' || g_newline ||
          G_INDENT5 ||', ILV2.num_winners' || g_newline ||
          G_INDENT5 ||', ILV2.org_id' || g_newline ;

Line: 4722

       lp_SELECT_cols :=
          G_INDENT5 || 'A.trans_object_id, A.trans_detail_object_id, ' || g_newline ||
          -- eihsu 06/19/2003 worker_id
          G_INDENT5 || 'A.worker_id, ' || g_newline ||
          G_INDENT5 || 'A.header_id1, A.header_id2, ' || g_newline ||
          G_INDENT5 || 'ILV.terr_id, ILV.absolute_rank, ' || g_newline ||
          G_INDENT5 || 'ILV.top_level_terr_id, ILV.num_winners, ILV.org_id ';

Line: 4793

      l_qual_rules => l_select_ilv2
       );

Line: 4800

    G_INDENT5 || 'FROM ( ' || g_newline || EXP_PURCHASE_UNION_SELECT(p_new_mode_fetch)
    );

Line: 4817

         G_INDENT5 || 'SELECT /*+ ' || g_newline ||
         G_INDENT5 || '           ORDERED ' || g_newline ||
         G_INDENT5 || '           USE_CONCAT ' || g_newline ||
         G_INDENT5 || '           NO_MERGE ' || g_newline ||
         G_INDENT5 || '       */ ' || g_newline ; --||

Line: 4824

         G_INDENT5 || 'SELECT /*+ ' || g_newline ||
         G_INDENT5 || '           ORDERED ' || g_newline ||
         G_INDENT5 || '           USE_CONCAT ' || g_newline ||
         G_INDENT5 || '           USE_NL(ILV Q1003R1 Q1007R1 Q1012R1 A) ' || g_newline ||
         G_INDENT5 || '           INDEX(ILV JTF_TERR_DENORM_RULES_N4) ' || g_newline ||
                  /* DYNAMIC HINT BASED ON TRANSACTION TYPE */
         G_INDENT5 || '           INDEX(A JTF_TAE_TN' || ABS(p_trans_object_type_id) ||
                                                        '_324347_ND)' || g_newline ||

         G_INDENT5 || '           INDEX(Q1003R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1007R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1012R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '       */ ' || g_newline ; --||

Line: 4845

         /* Add SELECT columns */
         --ARPATEL 10/14 bug#3207518
      JTF_TAE_GEN_PVT.write_buffer_content(
      l_qual_rules => lp_SELECT_cols || g_newline
       );

Line: 4890

         G_INDENT5 || 'SELECT /*+ ' || g_newline;

Line: 4919

         /* Add SELECT columns */
         --ARPATEL 10/14 bug#3207518
      JTF_TAE_GEN_PVT.write_buffer_content(
      l_qual_rules => lp_SELECT_cols || g_newline
       );

Line: 4962

         G_INDENT5 || 'SELECT /*+ ' || g_newline;

Line: 4990

         /* Add SELECT columns */
         --ARPATEL 10/14 bug#3207518
      JTF_TAE_GEN_PVT.write_buffer_content(
      l_qual_rules => lp_SELECT_cols || g_newline
       );

Line: 5029

         G_INDENT5 || 'SELECT /*+ ' || g_newline;

Line: 5056

         /* Add SELECT columns */
         --ARPATEL 10/14 bug#3207518
      JTF_TAE_GEN_PVT.write_buffer_content(
      l_qual_rules => lp_SELECT_cols || g_newline
       );

Line: 5097

         G_INDENT5 || 'SELECT /*+ ' || g_newline;

Line: 5126

         /* Add SELECT columns */
         --ARPATEL 10/14 bug#3207518
      JTF_TAE_GEN_PVT.write_buffer_content(
      l_qual_rules => lp_SELECT_cols || g_newline
       );

Line: 5131

         /* Add SELECT columns */
         --lp_SELECT_cols || g_newline ||

         lx_61950277_sql_5 :=
         G_INDENT5 || 'FROM   ';

Line: 5171

         G_INDENT5 || 'SELECT /*+ ' || g_newline;

Line: 5236

   l_select_ilv2               VARCHAR2(32767) := NULL;

Line: 5237

   lp_SELECT_cols              VARCHAR2(32767) := NULL;

Line: 5257

       l_select_ilv2 := add_SELECT_clause(p_new_mode_fetch);

Line: 5260

       lp_SELECT_cols :=
          --G_INDENT5 || add_SELECT_cols(p_new_mode_fetch) || g_newline;

Line: 5416

       l_select_ilv2 :=
          G_INDENT5 || 'SELECT DISTINCT ' || g_newline ||
          G_INDENT5 ||'ILV2.trans_object_id' || g_newline ||
          G_INDENT5 ||', ILV2.trans_detail_object_id' || g_newline ||
         -- eihsu 06/19/2003 worker_id
          G_INDENT5 ||',ILV2.worker_id' || g_newline ||
          G_INDENT5 ||',ILV2.header_id1' || g_newline ||
          G_INDENT5 ||',ILV2.header_id2' || g_newline ||
          G_INDENT5 ||', p_source_id' || g_newline ||
          G_INDENT5 ||', p_trans_object_type_id' || g_newline ||
          G_INDENT5 ||', l_sysdate' || g_newline ||
          G_INDENT5 ||', L_USER_ID' || g_newline ||
          G_INDENT5 ||', l_sysdate' || g_newline ||
          G_INDENT5 ||', L_USER_ID' || g_newline ||
          G_INDENT5 ||', L_USER_ID' || g_newline ||
          G_INDENT5 ||', L_REQUEST_ID' || g_newline ||
          G_INDENT5 ||', L_PROGRAM_APPL_ID' || g_newline ||
          G_INDENT5 ||', L_PROGRAM_ID' || g_newline ||
          G_INDENT5 ||', l_sysdate' || g_newline ||
          G_INDENT5 ||', ILV2.terr_id' || g_newline ||
          G_INDENT5 ||', ILV2.absolute_rank' || g_newline ||
          G_INDENT5 ||', ILV2.top_level_terr_id' || g_newline ||
          G_INDENT5 ||', ILV2.num_winners' || g_newline ||
          G_INDENT5 ||', ILV2.org_id' || g_newline ;

Line: 5443

       lp_SELECT_cols :=
          G_INDENT5 || 'A.trans_object_id, A.trans_detail_object_id, ' || g_newline ||
          -- eihsu 06/19/2003 worker_id
          G_INDENT5 || 'A.worker_id, ' || g_newline ||
          G_INDENT5 || 'A.header_id1, A.header_id2, ' || g_newline ||
          G_INDENT5 || 'ILV.terr_id, ILV.absolute_rank, ' || g_newline ||
          G_INDENT5 || 'ILV.top_level_terr_id, ILV.num_winners, ILV.org_id ';

Line: 5514

      l_qual_rules => l_select_ilv2
       );

Line: 5521

    G_INDENT5 || 'FROM ( ' || g_newline || EXP_PURCHASE_UNION_SELECT(p_new_mode_fetch)
    );

Line: 5535

         G_INDENT5 || 'SELECT /*+ ' || g_newline ||
         G_INDENT5 || '           ORDERED ' || g_newline ||
         G_INDENT5 || '           USE_CONCAT ' || g_newline ||
         G_INDENT5 || '           USE_NL(ILV Q1003R1 Q1007R1 Q1012R1 A) ' || g_newline ||
         G_INDENT5 || '           INDEX(ILV JTF_TERR_DENORM_RULES_N4) ' || g_newline ||
                  /* DYNAMIC HINT BASED ON TRANSACTION TYPE */
         G_INDENT5 || '           INDEX(A JTF_TAE_TN' || ABS(p_trans_object_type_id) ||
                                                        '_324347_ND)' || g_newline ||

         G_INDENT5 || '           INDEX(Q1003R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1007R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1012R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '       */ ' || g_newline ; --||

Line: 5555

         /* Add SELECT columns */
         --ARPATEL 10/14 bug#3207518
      JTF_TAE_GEN_PVT.write_buffer_content(
      l_qual_rules => lp_SELECT_cols || g_newline
       );

Line: 5592

         G_INDENT5 || 'SELECT /*+ ' || g_newline ||

         G_INDENT5 || '           ORDERED ' || g_newline ||
         G_INDENT5 || '           USE_CONCAT ' || g_newline ||
         G_INDENT5 || '           USE_NL(ILV Q1003R1 Q1007R1 Q1012LK A)  ' || g_newline ||
         G_INDENT5 || '           INDEX(ILV JTF_TERR_DENORM_RULES_N4) ' || g_newline ||

         /* DYNAMIC HINT BASED ON TRANSACTION TYPE */
         G_INDENT5 || '           INDEX(A JTF_TAE_TN' || ABS(p_trans_object_type_id) ||
                                                      '_324347_ND)' || g_newline ||
         G_INDENT5 || '           INDEX(Q1003R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1007R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1012LK JTF_TERR_CNR_QUAL_LIKE_MV_N10) ' || g_newline ||
         G_INDENT5 || '       */ ' || g_newline ; --||

Line: 5612

         /* Add SELECT columns */
         --ARPATEL 10/14 bug#3207518
      JTF_TAE_GEN_PVT.write_buffer_content(
      l_qual_rules => lp_SELECT_cols || g_newline
       );

Line: 5647

         G_INDENT5 || 'SELECT /*+ ' || g_newline ||

         G_INDENT5 || '           ORDERED ' || g_newline ||
         G_INDENT5 || '           USE_NL(ILV Q1003R1 Q1007R1 Q1012LK A)  ' || g_newline ||

         /* DYNAMIC HINT BASED ON TRANSACTION TYPE */
         G_INDENT5 || '           INDEX(A JTF_TAE_TN' || ABS(p_trans_object_type_id) ||
                                                        '_324347X_ND)' || g_newline ||
         G_INDENT5 || '           INDEX(ILV JTF_TERR_DENORM_RULES_N4) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1003R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1007R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1012LK JTF_TERR_CNR_QUAL_LIKE_MV_N10) ' || g_newline ||
         G_INDENT5 || '       */ ' || g_newline ; --||

Line: 5667

         /* Add SELECT columns */
         --ARPATEL 10/14 bug#3207518
      JTF_TAE_GEN_PVT.write_buffer_content(
      l_qual_rules => lp_SELECT_cols || g_newline
       );

Line: 5698

         G_INDENT5 || 'SELECT /*+ ' || g_newline ||

         G_INDENT5 || '           ORDERED ' || g_newline ||
         G_INDENT5 || '           USE_NL(ILV Q1003R1 Q1007R1 Q1012LK A)  ' || g_newline ||

         /* DYNAMIC HINT BASED ON TRANSACTION TYPE */
         G_INDENT5 || '           INDEX(A JTF_TAE_TN' || ABS(p_trans_object_type_id) ||
                                                        '_324347X_ND)' || g_newline ||
         G_INDENT5 || '           INDEX(ILV JTF_TERR_DENORM_RULES_N4) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1003R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1007R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1012LK JTF_TERR_CNR_QUAL_LIKE_MV_N10) ' || g_newline ||
         G_INDENT5 || '       */ ' || g_newline ; --||

Line: 5717

         /* Add SELECT columns */
         --ARPATEL 10/14 bug#3207518
      JTF_TAE_GEN_PVT.write_buffer_content(
      l_qual_rules => lp_SELECT_cols || g_newline
       );

Line: 5750

         G_INDENT5 || 'SELECT /*+ ' || g_newline ||
         G_INDENT5 || '           ORDERED ' || g_newline ||
         G_INDENT5 || '           USE_CONCAT ' || g_newline ||
         G_INDENT5 || '           USE_NL(ILV Q1003R1 Q1007R1 Q1012BT A) ' || g_newline ||
         G_INDENT5 || '           INDEX(ILV JTF_TERR_DENORM_RULES_N4) ' || g_newline ||
         /* DYNAMIC HINT BASED ON TRANSACTION TYPE */
         G_INDENT5 || '           INDEX(A JTF_TAE_TN' || ABS(p_trans_object_type_id) ||
                                                        '_324347_ND)' || g_newline ||

         G_INDENT5 || '           INDEX(Q1003R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1007R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1012BT JTF_TERR_CNR_QUAL_BTWN_MV_N10) ' || g_newline ||
         G_INDENT5 || '       */ ' || g_newline ; --||

Line: 5769

         /* Add SELECT columns */
         --ARPATEL 10/14 bug#3207518
      JTF_TAE_GEN_PVT.write_buffer_content(
      l_qual_rules => lp_SELECT_cols || g_newline
       );

Line: 5774

         /* Add SELECT columns */
         --lp_SELECT_cols || g_newline ||

         lx_44435539_sql_5 :=
         G_INDENT5 || 'FROM   '||

         /* STATIC INLINE VIEW */
         add_ILV(l_sql, p_new_mode_fetch) ||

         G_INDENT5 || '   ,  jtf_terr_cnr_qual_btwn_mv Q1012BT ' || g_newline ||
         G_INDENT5 || '   ,  jtf_terr_qual_rules_mv Q1007R1 ' || g_newline ||
         G_INDENT5 || '   ,  jtf_terr_qual_rules_mv Q1003R1 ' || g_newline ||

         /* DYNAMIC BASED ON TRANSACTION TYPE */
         G_INDENT || '    ,' || P_TABLE_NAME || ' A ' || g_newline ||

         G_INDENT5 || 'WHERE ' || g_newline ||

         lp_pc_cntry_predicate || g_newline ||

         G_INDENT5 || '  AND Q1007R1.terr_id = Q1012BT.terr_id ' || g_newline ||
         G_INDENT5 || '  AND      a.squal_char01 <= Q1012BT.high_value_char AND ' || g_newline ||
         G_INDENT5 || '        a.squal_char01 >= Q1012BT.low_value_char AND ' || g_newline ||
         G_INDENT5 || '        a.squal_fc01 >= SUBSTR(Q1012BT.low_value_char, 1, 1) ' || g_newline ||
         G_INDENT5 || '  AND Q1012BT.qual_usg_id = -1012 ' || g_newline ||
         G_INDENT5 || '  AND Q1012BT.terr_id = ILV.terr_id ' || g_newline ||
         G_INDENT5 || '  AND Q1012BT.terr_id = Q1003R1.terr_id ) ILV2,' || g_newline ||

         /************************/
         /*  = ILV1               */
         /************************/
         G_INDENT5 || '( ' || g_newline ||
         G_INDENT5 || 'SELECT /*+ ' || g_newline ||
         G_INDENT5 || '           USE_NL(ILV Q1018R1 ASLLP) ' || g_newline ||
         G_INDENT5 || '           USE_HASH(Q1018R1 ASLLP) ' || g_newline ||
         G_INDENT5 || '           INDEX(ILV JTF_TERR_DENORM_RULES_N4) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1018R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '       */ ' || g_newline ||

         G_INDENT5 || '                      ASLLP.sales_lead_id' || g_newline ||
         G_INDENT5 || '                    , ASLLP.sales_lead_line_id' || g_newline ||
         G_INDENT5 || '                    , ILV.terr_id ' || g_newline ||
         G_INDENT5 || '                     , ILV.top_level_terr_id ' || g_newline ||
         G_INDENT5 || '                     , ILV.absolute_rank  ' || g_newline ||
         G_INDENT5 || '                     , ILV.num_winners ' || g_newline ||
         G_INDENT5 || '                     , ILV.org_id ' || g_newline ||
         G_INDENT5 || '                 FROM AS_SALES_LEAD_LINES ASLLP, jtf_terr_qual_rules_mv Q1018R1' || g_newline ||
         G_INDENT5 || '                     , /* INLINE VIEW */' || g_newline ||
         G_INDENT5 || '                     ( SELECT /*+ NO_MERGE */' || g_newline ||
         G_INDENT5 || '                             jtdr.terr_id ' || g_newline ||
         G_INDENT5 || '                           , jtdr.source_id' || g_newline ||
         G_INDENT5 || '                             , jtdr.qual_type_id' || g_newline ||
         G_INDENT5 || '                            , jtdr.top_level_terr_id' || g_newline ||
         G_INDENT5 || '                             , jtdr.absolute_rank ' || g_newline ||
         G_INDENT5 || '                            , jtdr.num_winners ' || g_newline ||
         G_INDENT5 || '                            , jtdr.org_id' || g_newline ||
         G_INDENT5 || '                        FROM  jtf_terr_denorm_rules_all jtdr' || g_newline ||
         G_INDENT5 || '                             ,jtf_terr_qtype_usgs_all jtqu   ' || g_newline ||
         G_INDENT5 || '                             ,jtf_qual_type_usgs_all jqtu    ' || g_newline ||
         G_INDENT5 || '                        WHERE jtdr.source_id = p_source_id' || g_newline ||
         G_INDENT5 || '                         AND jtdr.terr_id= jtdr.related_terr_id' || g_newline ||
         G_INDENT5 || '                         AND jqtu.source_id = jtdr.source_id    ' || g_newline ||
         G_INDENT5 || '                         AND jqtu.qual_type_id = p_trans_object_type_id ' || g_newline ||
         G_INDENT5 || '                         AND jtdr.terr_id = jtqu.terr_id ' || g_newline ||
         G_INDENT5 || '                         AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id ' || g_newline ||
         G_INDENT5 || '                         AND jtdr.resource_exists_flag = ''Y''' || g_newline ||
         G_INDENT5 || '                         AND jtqu.qual_relation_product = lp_qual_combination_tbl(i)' || g_newline ||
         G_INDENT5 || '                    ) ILV' || g_newline ||
	 /* ARPATEL BUG#3531955 03/24/2004 change 1023 to 1018 */
         G_INDENT5 || '                WHERE  (  Q1018R1.qual_usg_id = -1018 AND Q1018R1.terr_id = ILV.terr_id )' || g_newline ||
         G_INDENT5 || '    AND ( Q1018R1.secondary_interest_code_id IS NULL' || g_newline ||
         G_INDENT5 || '    OR (ASLLP.secondary_interest_code_id = Q1018R1.secondary_interest_code_id))' || g_newline ||
         G_INDENT5 || '   AND ( Q1018R1.primary_interest_code_id IS NULL' || g_newline ||
         G_INDENT5 || '   OR ( ASLLP.primary_interest_code_id = Q1018R1.primary_interest_code_id ))' || g_newline ||
         G_INDENT5 || '   AND ASLLP.interest_type_id =  Q1018R1.interest_type_id' || g_newline ||
         G_INDENT5 || '    AND Q1018R1.comparison_operator = ''=''' || g_newline ||
         G_INDENT5 || '   ) ILV1 ' || g_newline ||
         G_INDENT5 || '  WHERE ILV1.terr_id = ILV2.terr_id ' || g_newline ||
         G_INDENT5 || '    AND ILV1.sales_lead_id = ILV2.trans_object_id ' || g_newline ||
         G_INDENT5 || lp_close_outermost_ILV || g_newline;

Line: 5875

l_select_ilv2             VARCHAR2(32767) := NULL;

Line: 5876

lp_SELECT_cols            VARCHAR2(32767) := NULL;

Line: 5881

       l_select_ilv2 := --add_SELECT_clause(p_new_mode_fetch);

Line: 5882

                       G_INDENT || ' SELECT DISTINCT  ' || g_newline ||
            G_INDENT || '       ILV2.TRANS_OBJECT_ID' || g_newline ||
            G_INDENT || '       , ILV2.TRANS_DETAIL_OBJECT_ID' || g_newline ||
            G_INDENT || '       , ILV2.HEADER_ID1' || g_newline ||
            G_INDENT || '       , ILV2.HEADER_ID2' || g_newline ||
            G_INDENT || '       , p_source_id' || g_newline ||
            G_INDENT || '       , p_trans_object_type_id' || g_newline ||
            G_INDENT || '       , l_sysdate' || g_newline ||
            G_INDENT || '       , L_USER_ID' || g_newline ||
            G_INDENT || '       , l_sysdate' || g_newline ||
            G_INDENT || '       , L_USER_ID' || g_newline ||
            G_INDENT || '       , L_USER_ID' || g_newline ||
            G_INDENT || '       , L_REQUEST_ID' || g_newline ||
            G_INDENT || '       , L_PROGRAM_APPL_ID' || g_newline ||
            G_INDENT || '       , L_PROGRAM_ID' || g_newline ||
            G_INDENT || '       , l_sysdate' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_FC01' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_FC02' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_FC03' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_FC04' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_FC05' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CURC01' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CURC02' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CURC03' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CURC04' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CURC05' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CURC06' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CURC07' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CURC08' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CURC09' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CURC10' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR01' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR02' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR03' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR04' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR05' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR06' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR07' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR08' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR09' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR10' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR11' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR12' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR13' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR14' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR15' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR16' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR17' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR18' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR19' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR20' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR21' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR22' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR23' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR24' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR25' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR26' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR27' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR28' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR30' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR31' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR32' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR33' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR34' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR35' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR36' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR37' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR38' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR39' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR40' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR41' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR42' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR43' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR44' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR45' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR46' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR47' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR48' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR49' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR50' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR51' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR52' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR53' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR54' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR55' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR56' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR57' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR58' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR59' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_CHAR60' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM01' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM02' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM03' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM04' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM05' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM06' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM07' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM08' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM09' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM10' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM11' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM12' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM13' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM14' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM15' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM16' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM17' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM18' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM19' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM20' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM21' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM22' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM23' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM24' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM25' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM26' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM27' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM28' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM29' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM30' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM31' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM32' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM33' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM34' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM35' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM36' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM37' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM38' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM39' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM40' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM41' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM42' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM43' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM44' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM45' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM46' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM47' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM48' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM49' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM50' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM51' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM52' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM53' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM54' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM55' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM56' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM57' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM58' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM59' || g_newline ||
            G_INDENT || '       , ILV2.SQUAL_NUM60' || g_newline ||
            G_INDENT || '       , ILV2.ASSIGNED_FLAG' || g_newline ||
            G_INDENT || '       , ILV2.PROCESSED_FLAG' || g_newline ||
            G_INDENT || '       , ILV2.ORG_ID' || g_newline ||
            G_INDENT || '       , ILV2.SECURITY_GROUP_ID' || g_newline ||
            G_INDENT || '       , ILV2.OBJECT_VERSION_NUMBER' || g_newline ||
            G_INDENT || '       , ILV2.WORKER_ID' || g_newline ;

Line: 6039

       lp_SELECT_cols :=
          --G_INDENT5 || add_SELECT_cols(p_new_mode_fetch) || g_newline;

Line: 6195

       l_select_ilv2 :=
          G_INDENT5 || 'SELECT /*+ USE_HASH(ILV2 ASLLP) */ ' || g_newline ||
	  G_INDENT5 || '       DISTINCT ' || g_newline ||
          G_INDENT5 ||'ILV2.trans_object_id' || g_newline ||
          G_INDENT5 ||', ILV2.trans_detail_object_id' || g_newline ||
         -- eihsu 06/19/2003 worker_id
          G_INDENT5 ||',ILV2.worker_id' || g_newline ||
          G_INDENT5 ||',ILV2.header_id1' || g_newline ||
          G_INDENT5 ||',ILV2.header_id2' || g_newline ||
          G_INDENT5 ||', p_source_id' || g_newline ||
          G_INDENT5 ||', p_trans_object_type_id' || g_newline ||
          G_INDENT5 ||', l_sysdate' || g_newline ||
          G_INDENT5 ||', L_USER_ID' || g_newline ||
          G_INDENT5 ||', l_sysdate' || g_newline ||
          G_INDENT5 ||', L_USER_ID' || g_newline ||
          G_INDENT5 ||', L_USER_ID' || g_newline ||
          G_INDENT5 ||', L_REQUEST_ID' || g_newline ||
          G_INDENT5 ||', L_PROGRAM_APPL_ID' || g_newline ||
          G_INDENT5 ||', L_PROGRAM_ID' || g_newline ||
          G_INDENT5 ||', l_sysdate' || g_newline ||
          G_INDENT5 ||', ILV2.terr_id' || g_newline ||
          G_INDENT5 ||', ILV2.absolute_rank' || g_newline ||
          G_INDENT5 ||', ILV2.top_level_terr_id' || g_newline ||
          G_INDENT5 ||', ILV2.num_winners' || g_newline ||
          G_INDENT5 ||', ILV2.org_id' || g_newline ;

Line: 6221

       lp_SELECT_cols :=
          G_INDENT5 || 'A.trans_object_id, A.trans_detail_object_id, ' || g_newline ||
          -- eihsu 06/19/2003 worker_id
          G_INDENT5 || 'A.worker_id, ' || g_newline ||
          G_INDENT5 || 'A.header_id1, A.header_id2, ' || g_newline ||
          G_INDENT5 || 'ILV.terr_id, ILV.absolute_rank, ' || g_newline ||
          G_INDENT5 || 'ILV.top_level_terr_id, ILV.num_winners, ILV.org_id ';

Line: 6232

        l_qual_rules => l_select_ilv2 || ' FROM ' ||g_newline ||
        G_INDENT5 || '( SELECT /*+ NO_MERGE USE_CONCAT */ ' || g_newline
       );

Line: 6236

      lx_663217_sql := lp_SELECT_cols || g_newline ||
        G_INDENT5 || '    FROM  JTF_TAE_1001_LEAD_TRANS  A, ' || g_newline ||
        G_INDENT5 || '   jtf_terr_qual_rules_mv Q1007R1, ' || g_newline ||
        G_INDENT5 || '   jtf_terr_qual_rules_mv Q1003R1, ' || g_newline ||

         /* STATIC INLINE VIEW */
         add_ILV_with_NOMERGE_hint(l_sql, p_new_mode_fetch);

Line: 6275

         G_INDENT5 || '   (SELECT /*+ NO_MERGE */' || g_newline ||
         G_INDENT5 || '           Q1018R1.secondary_interest_code_id,' || g_newline ||
         G_INDENT5 || '                      Q1018R1.primary_interest_code_id,' || g_newline ||
         G_INDENT5 || '                      Q1018R1.interest_type_id' || g_newline ||
         G_INDENT5 || '                    , ILV.terr_id' || g_newline ||
         G_INDENT5 || '                    , ILV.top_level_terr_id' || g_newline ||
         G_INDENT5 || '                    , ILV.absolute_rank' || g_newline ||
         G_INDENT5 || '                    , ILV.num_winners' || g_newline ||
         G_INDENT5 || '                    , ILV.org_id' || g_newline ||
         G_INDENT5 || '               FROM  jtf_terr_qual_rules_mv Q1018R1, ' || g_newline;

Line: 6332

   lp_SELECT_cols              VARCHAR2(32767) := NULL;

Line: 6345

       lp_SELECT_cols :=
          /* ARPATEL: 01/15/2004 bug#3373462 */
           G_INDENT5 || '       A.TRANS_OBJECT_ID' || g_newline ||
            G_INDENT5 || '       , A.TRANS_DETAIL_OBJECT_ID' || g_newline ||
            G_INDENT5 || '       , A.HEADER_ID1' || g_newline ||
            G_INDENT5 || '       , A.HEADER_ID2' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_FC01' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_FC02' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_FC03' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_FC04' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_FC05' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CURC01' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CURC02' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CURC03' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CURC04' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CURC05' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CURC06' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CURC07' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CURC08' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CURC09' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CURC10' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR01' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR02' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR03' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR04' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR05' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR06' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR07' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR08' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR09' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR10' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR11' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR12' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR13' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR14' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR15' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR16' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR17' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR18' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR19' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR20' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR21' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR22' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR23' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR24' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR25' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR26' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR27' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR28' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR30' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR31' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR32' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR33' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR34' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR35' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR36' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR37' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR38' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR39' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR40' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR41' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR42' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR43' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR44' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR45' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR46' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR47' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR48' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR49' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR50' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR51' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR52' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR53' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR54' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR55' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR56' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR57' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR58' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR59' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_CHAR60' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM01' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM02' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM03' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM04' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM05' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM06' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM07' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM08' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM09' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM10' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM11' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM12' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM13' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM14' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM15' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM16' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM17' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM18' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM19' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM20' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM21' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM22' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM23' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM24' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM25' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM26' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM27' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM28' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM29' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM30' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM31' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM32' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM33' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM34' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM35' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM36' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM37' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM38' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM39' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM40' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM41' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM42' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM43' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM44' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM45' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM46' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM47' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM48' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM49' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM50' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM51' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM52' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM53' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM54' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM55' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM56' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM57' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM58' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM59' || g_newline ||
            G_INDENT5 || '       , A.SQUAL_NUM60' || g_newline ||
            G_INDENT5 || '       , A.ASSIGNED_FLAG' || g_newline ||
            G_INDENT5 || '       , A.PROCESSED_FLAG' || g_newline ||
            G_INDENT5 || '       , A.ORG_ID' || g_newline ||
            G_INDENT5 || '       , A.SECURITY_GROUP_ID' || g_newline ||
            G_INDENT5 || '       , A.OBJECT_VERSION_NUMBER' || g_newline ||
            G_INDENT5 || '       , A.WORKER_ID' || g_newline ;

Line: 6494

       lp_SELECT_cols :=
          G_INDENT5 || 'A.trans_object_id, A.trans_detail_object_id, ' || g_newline ||
          -- eihsu: 06/19/2003 worker_id
          G_INDENT5 || 'A.worker_id, ' || g_newline ||
          G_INDENT5 || 'A.header_id1, A.header_id2, ' || g_newline ||
          G_INDENT5 || 'ILV.terr_id, ILV.absolute_rank, ' || g_newline ||
          G_INDENT5 || 'ILV.top_level_terr_id, ILV.num_winners, ILV.org_id ';

Line: 6570

      l_qual_rules => G_INDENT || add_SELECT_clause(p_new_mode_fetch) || g_newline
   );

Line: 6585

         G_INDENT5 || 'SELECT /*+ ' || g_newline ||
         G_INDENT5 || '           ORDERED ' || g_newline ||
         G_INDENT5 || '           USE_CONCAT ' || g_newline ||
         G_INDENT5 || '           USE_NL(ILV Q1003R1 Q1007R1 Q1102R1 A) ' || g_newline ||
         G_INDENT5 || '           INDEX(ILV JTF_TERR_DENORM_RULES_N4) ' || g_newline ||
                  /* DYNAMIC HINT BASED ON TRANSACTION TYPE */
         G_INDENT5 || '           INDEX(A JTF_TAE_TN' || ABS(p_trans_object_type_id) ||
                                                        '_324347_ND)' || g_newline ||

         G_INDENT5 || '           INDEX(Q1003R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1007R1 JTF_TERR_QUAL_RULES_MV_N10) ' || g_newline ||
         G_INDENT5 || '           INDEX(Q1102R1 JTF_TERR_CNRG_EQUAL_MV_N10) ' || g_newline ||
         G_INDENT5 || '       */ ' || g_newline;

Line: 6600

         G_INDENT5 || 'SELECT /*+ ' || g_newline ||
         G_INDENT5 || '           ORDERED ' || g_newline ||
         G_INDENT5 || '           USE_CONCAT ' || g_newline ||
         G_INDENT5 || '           NO_MERGE ' || g_newline ||
         G_INDENT5 || '       */ ' || g_newline;

Line: 6608

         /* Add SELECT columns */
         lp_SELECT_cols || g_newline ||

         G_INDENT5 || 'FROM ';

Line: 6650

         G_INDENT5 || 'SELECT /*+ ' || g_newline;

Line: 6675

         /* Add SELECT columns */
         lp_SELECT_cols || g_newline ||

         G_INDENT5 || 'FROM    ';

Line: 6720

         G_INDENT5 || 'SELECT /*+ ' || g_newline;

Line: 6744

         /* Add SELECT columns */
         lp_SELECT_cols || g_newline ||

         G_INDENT5 || 'FROM   ';

Line: 6783

         G_INDENT5 || 'SELECT /*+ ' || g_newline;

Line: 6807

         /* Add SELECT columns */
         lp_SELECT_cols || g_newline ||

         G_INDENT5 || 'FROM   ';

Line: 6847

         G_INDENT5 || 'SELECT /*+ ' || g_newline;

Line: 6872

         /* Add SELECT columns */
         lp_SELECT_cols || g_newline ||

         G_INDENT5 || 'FROM   ';