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

APPS.BIL_DO_L1_BASE_GRP_PKG SQL Statements

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

Line: 27


PROCEDURE Insert_Parent_Data( ERRBUF           IN OUT VARCHAR2
                             ,RETCODE          IN OUT VARCHAR2
                             ,p_degree         IN     NUMBER);

Line: 31


PROCEDURE Insert_Hirarchial_Data( ERRBUF           IN OUT VARCHAR2
                                 ,RETCODE          IN OUT VARCHAR2
                                 ,p_group_id       IN     NUMBER
                                 ,p_level          IN     NUMBER
                                 ,p_degree         IN     NUMBER);

Line: 37


PROCEDURE Insert_From_Denorm(  ERRBUF           IN OUT VARCHAR2
                              ,RETCODE          IN OUT VARCHAR2
                              ,p_level          IN     NUMBER
                              ,p_degree         IN     NUMBER);

Line: 47


*Inserts data to the level equal to profile option value of
*BIL_DO_L1_GRP_AGGR_LVL.
*ERRBUFF:    error message returned by the proc
*RETCODE:    completion status of the procedure
*p_degree:   parallel degree
*p_debug:    debug mode (yes or no)
*p_trace:    trace mode (yes or no)
********************************************************************/
procedure collect_temp_data( ERRBUF        OUT VARCHAR2
                           , RETCODE       OUT VARCHAR2
                           , p_degree      IN  NUMBER   DEFAULT 4
                           , p_debug_mode  IN  VARCHAR2 DEFAULT 'N'
                           , p_trace_mode  IN  VARCHAR2 DEFAULT 'N'
                           ) is

  CURSOR lvl_cur(l_level number) is
    select child_sales_group_id, Hier_level
      from bil_do_l1_base_grp_temp
     where Hier_level = l_level;

Line: 100


/*insert all the parents*/
Insert_Parent_Data( ERRBUF    => ERRBUF
                   ,RETCODE   => RETCODE
                   ,p_degree  => p_degree);

Line: 110


     Insert_Hirarchial_Data( ERRBUF      => ERRBUF
                            ,RETCODE     => RETCODE
                            ,p_group_id  => j.child_sales_group_id
                            ,p_level     => l_counter
                            ,p_degree    => p_degree); -- inserting values for level (l_Counter+1)

Line: 122


  /*Inserting the last level of data into the table*/
 insert_from_denorm(  ERRBUF      => ERRBUF
                     ,RETCODE     => RETCODE
                     ,p_level     => l_profile_option
                     ,p_degree    => p_degree);

Line: 128


 /*Analyze the table after insertion*/
 BIL_DO_UTIL_PKG.Write_Log('Analyze table BIL_DO_L1_BASE_GRP_TEMP',p_debug=>G_Debug);

Line: 175


 * Insert_parent_data inserts all the top level
 *salesgroups(parents) as hier_level=1 in the table
 ***************************************************************/

 PROCEDURE Insert_Parent_Data( ERRBUF           IN OUT VARCHAR2
                             ,RETCODE          IN OUT VARCHAR2
                             ,p_degree         IN     NUMBER)IS
 l_sysdate DATE := sysdate;

Line: 185


 l_insert_statement VARCHAR2(1000);

Line: 186


 l_select_statement VARCHAR2(5000);

Line: 192


 l_insert_statement := 'INSERT INTO  /*+ APPEND PARALLEL(bgt,'||p_degree||') */ bil_do_l1_base_grp_temp bgt';

Line: 193


 l_insert_statement := l_insert_statement||'( child_sales_group_id,sales_group_id,Hier_level,creation_date,created_by';

Line: 194


 l_insert_statement := l_insert_statement||',last_update_date,last_updated_by,last_update_login,request_id';

Line: 195


 l_insert_statement := l_insert_statement||',program_application_id,program_id,program_update_date)   ';

Line: 197


 l_select_statement :=' (SELECT  group_id,group_id, :l_level, :l_sysdate, :G_user_id, :l_sysdate, :G_user_id, :G_login_id';

Line: 198


 l_select_statement :=l_select_statement||' , :G_request_id, :G_appl_id, :G_program_id, :l_sysdate  FROM  ';

Line: 200


 l_select_statement :=l_select_statement||' ( SELECT /*+ Parallel(REL,'||p_degree||') */  distinct REL.related_grouP_id group_id ';

Line: 201


 l_select_statement :=l_select_statement||'     FROM jtf_rs_grp_relations REL, jtf_rs_group_usages usg ';

Line: 202


 l_select_statement :=l_select_statement||'    WHERE relation_type = :l_parent_group AND related_group_id not in ';

Line: 203


 l_select_statement :=l_select_statement||'         (SELECT group_id FROM apps.jtf_rs_grp_relations) ';

Line: 204


 l_select_statement :=l_select_statement||'      AND (start_date_active <= :l_sysdate OR start_date_active IS NULL) ';

Line: 205


 l_select_statement :=l_select_statement||'      AND (end_date_active > :l_sysdate OR end_date_active IS NULL) ';

Line: 206


 l_select_statement :=l_select_statement||'      AND usg.group_id = REL.related_group_id    AND usg.Usage = :l_sales))';

Line: 209


 EXECUTE IMMEDIATE l_insert_statement||l_select_statement
 USING
    l_level
   ,l_sysdate
   ,G_user_id
   ,l_sysdate
   ,G_user_id
   ,G_login_id
   ,G_request_id
   ,G_appl_id
   ,G_program_id
   ,l_sysdate
   ,l_parent_group
   ,l_sysdate
   ,l_sysdate
   ,l_sales;

Line: 228


      BIL_DO_UTIL_PKG.Write_Log(p_msg=>'No Rows are inserted from Insert_parent_data',p_stime=>l_stime,p_etime=>SYSDATE, p_force=>'Y',p_debug=>G_Debug);

Line: 230


 BIL_DO_UTIL_PKG.Write_Log(p_msg=>'Rows Inserted from Insert_parent_data:'||SQL%ROWCOUNT,p_stime=>l_stime,p_etime=>SYSDATE, p_debug=>G_Debug);

Line: 233


        ERRBUF := ERRBUF ||' Insert_Parent_Data:'||sqlcode||' '|| sqlerrm;

Line: 236


        BIL_DO_UTIL_PKG.Write_Log(p_msg=>' Insert_Parent_Data:'||sqlcode||' '|| sqlerrm
           , p_force=> 'Y', p_debug => G_Debug);

Line: 239


END Insert_Parent_Data;

Line: 242


*insert_hirarchial_data is used to insert hirarchial
*data up to the level specified in profile_option
*starting from top.
*@p_level - Determines which level of data is being inserted.
*@Group_id - Sales_group_id
***********************************************/
procedure Insert_Hirarchial_Data( ERRBUF           IN OUT VARCHAR2
                                 ,RETCODE          IN OUT VARCHAR2
                                 ,p_group_id       IN     NUMBER
                                 ,p_level          IN     NUMBER
                                 ,p_degree         IN     NUMBER) is
l_level NUMBER := 0;

Line: 256


l_insert_statement VARCHAR2(1000);

Line: 257


l_select_statement VARCHAR2(5000);

Line: 260


l_delete_flag_y    VARCHAR2(1) := 'Y';

Line: 261


l_delete_flag_n    VARCHAR2(1) := 'N';

Line: 265


 /* inserting data for the given level */
l_level := p_level + 1;

Line: 267


l_insert_statement := 'INSERT INTO  /*+ APPEND PARALLEL(bgt,'||p_degree||') */ bil_do_l1_base_grp_temp bgt';

Line: 268


l_insert_statement := l_insert_statement||' ( child_sales_group_id, sales_group_id, Hier_level, creation_date';

Line: 269


l_insert_statement := l_insert_statement||', created_by, last_update_date, last_updated_by, last_update_login';

Line: 270


l_insert_statement := l_insert_statement||', request_id, program_application_id, program_id, program_update_date)';

Line: 272


l_select_statement := '(SELECT /*+ PARALLEL(REL,'||p_degree||') */ REL.group_id';

Line: 273


l_select_statement := l_select_statement||'       ,REL.group_id, :l_level, :l_sysdate, :G_user_id';

Line: 274


l_select_statement := l_select_statement||'       ,:l_sysdate,:G_user_id,:G_login_id,:G_request_id';

Line: 275


l_select_statement := l_select_statement||'       ,:G_appl_id,:G_program_id,:l_sysdate';

Line: 276


l_select_statement := l_select_statement||'   FROM jtf_rs_grp_relations REL, jtf_rs_group_usages usg ';

Line: 277


l_select_statement := l_select_statement||'  WHERE REL.relation_type = :l_relation_type';

Line: 278


l_select_statement := l_select_statement||'    AND (REL.start_date_active <= :l_sysdate OR REL.start_date_active is null)';

Line: 279


l_select_statement := l_select_statement||'    AND (REL.end_date_active >= :l_sysdate OR REL.end_date_active is null)';

Line: 280


l_select_statement := l_select_statement||'    AND NVL(rel.delete_flag, :l_delete_flag_n) <> :l_delete_flag_y';

Line: 281


l_select_statement := l_select_statement||'    AND rel.group_id <> :p_group_id AND rel.related_group_id = :p_group_id ';

Line: 282


l_select_statement := l_select_statement||'    AND rel.group_id = usg.group_id AND usg.usage = :l_sales) ';

Line: 284


EXECUTE IMMEDIATE l_insert_statement||l_select_statement
USING
     l_level
    ,l_sysdate
    ,G_user_id
    ,l_sysdate
    ,G_user_id
    ,G_login_id
    ,G_request_id
    ,G_appl_id
    ,G_program_id
    ,l_sysdate
    ,l_relation_type
    ,l_sysdate
    ,l_sysdate
    ,l_delete_flag_n
    ,l_delete_flag_y
    ,p_group_id
    ,p_group_id
    ,l_sales;

Line: 307


      BIL_DO_UTIL_PKG.Write_Log(p_msg=>'No Rows are inserted from Insert_Hirarchial_Data',p_stime=>l_stime,p_etime=>SYSDATE, p_force=>'Y',p_debug=>G_Debug);

Line: 310


 BIL_DO_UTIL_PKG.Write_Log(p_msg=>'Rows Inserted from Insert_Hirarchial_Data:'||SQL%ROWCOUNT,p_stime=>l_stime,p_etime=>SYSDATE, p_debug=>G_Debug);

Line: 314


        ERRBUF := ERRBUF ||' Insert_Hirarchial_Data:'||sqlcode||' '|| sqlerrm;

Line: 317


      BIL_DO_UTIL_PKG.Write_Log(p_msg=>' Insert_Hirarchial_Data:'||sqlcode||' '|| sqlerrm
           , p_force=> 'Y', p_debug => G_Debug);

Line: 320


end insert_hirarchial_data;

Line: 324


*insert_from_denorm is used insert data from jtf_rs_groups_denorm table from hirarchial level=p_level.
*@p_group_id - Group_id which needs to be inserted along with all its children.
*
******************************************************************************************************/
procedure Insert_From_Denorm(  ERRBUF           IN OUT VARCHAR2
                              ,RETCODE          IN OUT VARCHAR2
                              ,p_level          IN     NUMBER
                              ,p_degree         IN     NUMBER) is
l_level number := 0;

Line: 335


l_insert_statement VARCHAR2(1000);

Line: 336


l_select_statement VARCHAR2(5000);

Line: 343


l_insert_statement := 'INSERT INTO  /*+ APPEND PARALLEL(bgt,'||p_degree||') */ bil_do_l1_base_grp_temp bgt';

Line: 344


l_insert_statement := l_insert_statement||' ( child_sales_group_id, sales_group_id, Hier_level, creation_date';

Line: 345


l_insert_statement := l_insert_statement||', created_by, last_update_date, last_updated_by, last_update_login';

Line: 346


l_insert_statement := l_insert_statement||', request_id, program_application_id, program_id, program_update_date)';

Line: 348


l_select_statement :=' (select /*+ PARALLEL(den,'||p_degree||') */  distinct den.group_id';

Line: 349


l_select_statement :=l_select_statement||' , den.parent_group_id, :l_level, :l_sysdate, :G_user_id, :l_sysdate';

Line: 350


l_select_statement :=l_select_statement||' , :G_user_id, :G_login_id, :G_request_id, :G_appl_id';

Line: 351


l_select_statement :=l_select_statement||' , :G_program_id, :l_sysdate';

Line: 352


l_select_statement :=l_select_statement||' from jtf_rs_groups_denorm den, jtf_rs_group_usages usg ';

Line: 353


l_select_statement :=l_select_statement||' where den.parent_group_id in ';

Line: 354


l_select_statement :=l_select_statement||'     (select child_sales_group_id from bil_do_l1_base_grp_temp';

Line: 355


l_select_statement :=l_select_statement||'       where Hier_level = :p_level)';

Line: 356


l_select_statement :=l_select_statement||'  and (den.start_date_active <= :l_sysdate OR den.start_date_active is null)';

Line: 357


l_select_statement :=l_select_statement||'  and (den.end_date_active >= :l_sysdate OR den.End_date_active is null)';

Line: 358


l_select_statement :=l_select_statement||'  and den.parent_group_id <> den.group_id AND usg.group_id = den.group_id  AND usg.usage = :l_sales)';

Line: 360


EXECUTE IMMEDIATE l_insert_statement||l_select_statement
  USING
     l_level
    ,l_sysdate
    ,G_user_id
    ,l_sysdate
    ,G_user_id
    ,G_login_id
    ,G_request_id
    ,G_appl_id
    ,G_program_id
    ,l_sysdate
    ,p_level
    ,l_sysdate
    ,l_sysdate
    ,l_sales;

Line: 379


      BIL_DO_UTIL_PKG.Write_Log(p_msg=>'No Rows are inserted from Insert_From_Denorm',p_stime=>l_stime,p_etime=>SYSDATE, p_force=>'Y',p_debug=>G_Debug);

Line: 382


 BIL_DO_UTIL_PKG.Write_Log(p_msg=>'Rows Inserted from Insert_From_Denorm:'||SQL%ROWCOUNT,p_stime=>l_stime,p_etime=>SYSDATE, p_debug=>G_Debug);

Line: 385


        ERRBUF := ERRBUF ||' Insert_From_Denorm:'||sqlcode||' '|| sqlerrm;

Line: 388


        BIL_DO_UTIL_PKG.Write_Log(p_msg=>' Insert_From_Denorm:'||sqlcode||' '|| sqlerrm
           , p_force=> 'Y',p_debug => G_Debug);

Line: 391


END Insert_From_Denorm;