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_OPPTY_SUMRY_PKG SQL Statements

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

Line: 20


PROCEDURE Delete_table( ERRBUF           IN OUT VARCHAR2
                       ,RETCODE          IN OUT VARCHAR2
                       ,p_date           IN     DATE);

Line: 37


PROCEDURE Insert_Data( ERRBUF           IN OUT VARCHAR2
                      ,RETCODE          IN OUT VARCHAR2
                      ,p_degree         IN     NUMBER
                      ,p_collect_date   IN     DATE);

Line: 69


  select max(collection_date)+1
    into l_collection_date_start
    from BIL_DO_L1_OPPTY_SUMRY;

Line: 197


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

Line: 239


/*Delete the existing records for the same collect_date from the table*/
IF (G_Truncate = 'N') THEN
BIL_DO_UTIL_PKG.Write_Log(p_msg=>'Delete Data for collection date: ' || TO_CHAR(l_collection_date, 'DD-MON-YYYY'), p_debug=>p_debug_mode);

Line: 242


    Delete_table( ERRBUF      => ERRBUF
                 ,RETCODE     => RETCODE
                 ,p_date      => l_collection_date);

Line: 247


BIL_DO_UTIL_PKG.Write_Log(p_msg=>'Insert Data for collection date: ' || TO_CHAR(l_collection_date, 'DD-MON-YYYY'), p_debug=>p_debug_mode);

Line: 248


 /*Calling the insert procedure for inserting data to the table.*/
Insert_Data( ERRBUF         => ERRBUF
            ,RETCODE        => RETCODE
            ,p_degree       => p_degree
            ,p_collect_date => l_collection_date);

Line: 289


*Insert_Data
***********************************************/
PROCEDURE Insert_Data( ERRBUF           IN OUT VARCHAR2
                      ,RETCODE          IN OUT VARCHAR2
                      ,p_degree         IN     NUMBER
                      ,p_collect_date   IN     DATE) IS

/*Strings for Dynamic sql*/
l_insert_string VARCHAR2(1000) := '';

Line: 298


l_select_string VARCHAR2(5000) := '';

Line: 305


l_delete_flag     VARCHAR2(1)  := 'N';

Line: 312


 l_insert_string := 'INSERT INTO BIL_DO_L1_OPPTY_SUMRY( COLLECTION_DATE';

Line: 313


 l_insert_string :=  l_insert_string|| ', SALES_GROUP_ID, PERIOD_NAME, PERIOD_TYPE, WON_AMOUNT, OPEN_AMOUNT';

Line: 314


 l_insert_string :=  l_insert_string|| ', WEIGHTED_OPEN_AMOUNT, FORECAST_AMOUNT, LAST_UPDATE_DATE, LAST_UPDATED_BY';

Line: 315


 l_insert_string :=  l_insert_string|| ', CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID';

Line: 316


 l_insert_string :=  l_insert_string||', PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) ';

Line: 318


l_select_string := '(SELECT /*+  use_hash(bdlbgt den) Parallel(den,'|| p_degree||')*/ :l_collection_date, bdlbgt.sales_group_id, pd.period_name, pd.period_type';

Line: 319


l_select_string := l_select_string||',sum(decode(nvl(den.WIN_LOSS_INDICATOR,'||l_quote||'N'||l_quote||')';

Line: 320


l_select_string := l_select_string||'||nvl(den.OPP_OPEN_STATUS_FLAG,'||l_quote||'Y'||l_quote||'),';

Line: 321


l_select_string := l_select_string||l_quote||'WN'||l_quote||',den.c1_WON_AMOUNT,0))';

Line: 322


l_select_string := l_select_string||',sum(decode(nvl(den.WIN_LOSS_INDICATOR,'||l_quote||'N'||l_quote||')';

Line: 324


l_select_string := l_select_string||'||nvl(den.OPP_OPEN_STATUS_FLAG,'||l_quote||'Y'||l_quote||'),';

Line: 325


l_select_string := l_select_string||l_quote||'NY'||l_quote||',den.c1_SALES_CREDIT_AMOUNT, 0))';

Line: 327


l_select_string := l_select_string||',sum(decode(nvl(den.WIN_LOSS_INDICATOR,'||l_quote||'N'||l_quote||')';

Line: 328


l_select_string := l_select_string||'||nvl(den.OPP_OPEN_STATUS_FLAG,'||l_quote||'Y'||l_quote||'),';

Line: 330


l_select_string := l_select_string||l_quote||'NY'||l_quote||',den.c1_SALES_CREDIT_AMOUNT*den.WIN_PROBABILITY/100.00, 0))';

Line: 331


l_select_string := l_select_string||',null,:l_sysdate,:G_user_id,:l_sysdate,:G_user_id,:G_login_id,:G_request_id';

Line: 332


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

Line: 334


l_select_string := l_select_string||' FROM  as_sales_credits_denorm den, bil_do_l1_base_grp_temp bdlbgt,as_period_days pd';

Line: 336


l_select_string := l_select_string||' WHERE pd.start_date <= den.decision_date  AND pd.end_date >= den.decision_date';

Line: 337


l_select_string := l_select_string||'   AND pd.period_set_name = :l_period_type AND den.sales_group_id = bdlbgt.child_sales_group_id';

Line: 339


l_select_string := l_select_string||'   AND pd.period_day = :l_collection_date ';

Line: 341


l_select_string := l_select_string||'   AND den.credit_type_id = :l_credit_type  AND den.OPP_DELETED_FLAG = :l_delete_flag';

Line: 342


l_select_string := l_select_string||'   AND den.status_code in (SELECT  STATUS_CODE FROM as_statuses_b ';

Line: 343


l_select_string := l_select_string||'       WHERE  enabled_flag = :l_enabled_flag and opp_flag = :l_enabled_flag) ';

Line: 344


l_select_string := l_select_string||'   AND den.sales_stage_id in (SELECT sales_stage_id FROM as_sales_stages_all_b ';

Line: 345


l_select_string := l_select_string||'       WHERE enabled_flag = :l_enabled_flag ';

Line: 346


l_select_string := l_select_string||'         AND sysdate between start_date_active and nvl(end_date_active,sysdate))';

Line: 347


l_select_string := l_select_string||'   AND den.interest_type_id in (SELECT interest_type_id FROM as_interest_types_b ';

Line: 348


l_select_string := l_select_string||'       WHERE enabled_flag = :l_enabled_flag AND expected_purchase_flag = :l_enabled_flag)';

Line: 349


l_select_string := l_select_string||' GROUP BY  bdlbgt.sales_group_id,pd.period_name,pd.period_type)';

Line: 351


 EXECUTE IMMEDIATE l_insert_string||l_select_string
 USING
     l_collection_date
    ,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_period_type
    ,l_collection_date
    ,l_credit_type
    ,l_delete_flag
    ,l_enabled_flag
    ,l_enabled_flag
    ,l_enabled_flag
    ,l_enabled_flag
    ,l_enabled_flag;

Line: 375


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

Line: 379


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

Line: 383


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

Line: 385


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

Line: 388


end Insert_Data;

Line: 391


*Delete_table is used to Delete existing records
*from BIL Table BIL_DO_L1_OPPTY_SUMRY for the collection_date.
**************************************************************/
PROCEDURE Delete_table( ERRBUF           IN OUT VARCHAR2
                       ,RETCODE          IN OUT VARCHAR2
                       ,p_date           IN     DATE) IS
 l_date DATE := p_date;

Line: 399


 DELETE FROM BIL_DO_L1_OPPTY_SUMRY
  WHERE collection_date = l_date;

Line: 401


 BIL_DO_UTIL_PKG.Write_Log(p_msg=>'Deleted records from BIL Table BIL_DO_L1_OPPTY_SUMRY',p_debug=>G_Debug);

Line: 406


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