Oracle Procedure示例02


CREATE OR REPLACE 
PACKAGE XXDM_FORECAST_TO_ASCP_PKG
  IS
    /*---------------------------------------------------------
      Current Version : 1.00
      Create By       : 
      Create Date     : 2009-02-28
      Purpose         : push Demantra data to ASCP.
      Logicality      :
    ---------------------------------------------------------*/   
   C_USERNAME           CONSTANT VARCHAR2 (10)   := 'DM';
   C_TABLELABEL_1       CONSTANT VARCHAR2 (50)   := 'PART NUMBER (SP)';
   C_TABLELABEL_2       CONSTANT VARCHAR2 (50)   := 'RO ORG';
   C_COMPUTED_TITLE_1   CONSTANT VARCHAR2 (50)   := 'DEMAND PRIORITY';
   C_COMPUTED_TITLE_2   CONSTANT VARCHAR2 (50)   := 'FINAL FORECAST';
   C_COMPUTED_TITLE_3   CONSTANT VARCHAR2 (50)   := 'ITEM DESTINATION KEY';
   C_COMPUTED_TITLE_4   CONSTANT VARCHAR2 (50)
                                            := 'ORGANIZATION DESTINATION KEY';
   C_COMPUTED_TITLE_5   CONSTANT VARCHAR2 (50)   := 'LTB 1 YEAR AGG';
   C_COMPUTED_TITLE_6   CONSTANT VARCHAR2 (50)   := 'LTB 3 YEAR AGG';
   C_MONTHS_IRP         CONSTANT NUMBER          := 6;
   C_MONTHS_ROLLING     CONSTANT NUMBER          := 6;
   C_MONTHS_LTB         CONSTANT NUMBER          := 38; --36+2 Sean: Do EOL notice before 2 months
   C_SUCC_FLAG          CONSTANT VARCHAR2 (10)   := 'SUCC';
   g_err_position                  VARCHAR2 (10);
   g_err_desc                      VARCHAR2 (400);
   g_sql                           VARCHAR2 (8000);    
   g_ltb_b_exist                   BOOLEAN;
   g_additional_filter             VARCHAR2 (400);  
   g_user_id                       NUMBER;
   g_max_salles_date               DATE;
   g_min_forcast_date              DATE;
   g_eol_date_c      VARCHAR2(20); --Sean
   g_eol_date_format VARCHAR2(20) := 'YYYY/MM/DD';   --Sean    
   PROCEDURE xxdm_upload_forecast_to_ascp
                ( p_level_id IN VARCHAR2, p_member_id IN VARCHAR2);  
   PROCEDURE xxdm_upload_fc_by_noticeid
                                    ( p_level_id IN VARCHAR2, 
                                      p_member_id IN VARCHAR2,
                                      p_notice_id IN VARCHAR2,
                                      p_ro IN VARCHAR2,
                                      p_ppp IN VARCHAR2,
                                      p_ppp_new IN VARCHAR2,
                                      p_user_id IN NUMBER,
                                      p_additional_filter IN VARCHAR2,
                                      p_max_salles_date IN DATE);   
   
   PROCEDURE xxdm_insert_init_data (p_notice_id IN VARCHAR2,
                                    p_ro IN VARCHAR2, 
                                    p_ppp IN VARCHAR2,
                                    p_user_id IN NUMBER,
                                    p_transfer_id OUT VARCHAR2,
                                    p_transfer_q_id OUT VARCHAR2);
   
   PROCEDURE xxdm_create_int_comb_view
                ( p_ro in VARCHAR2, 
                  p_view_name in VARCHAR2,
                  p_additional_filter in VARCHAR2);
                  
   PROCEDURE xxdm_create_int_date_table (p_table_name IN VARCHAR2);
  
   PROCEDURE xxdm_insert_int_date_table (p_ppp IN VARCHAR2,p_table_name IN VARCHAR2);
   
   PROCEDURE xxdm_create_export_view (p_notice_id IN VARCHAR2,       
                                       p_ro IN VARCHAR2, 
                                       p_ppp_new IN VARCHAR2,                             
                                       p_int_comb_viewname IN VARCHAR2,
                                       p_int_date_tablename IN VARCHAR2,
                                       p_additional_filter IN VARCHAR2,
                                       p_max_salles_date IN DATE);
                                       
   FUNCTION xxdm_check_ltb_b_exist(p_additional_filter VARCHAR2) RETURN BOOLEAN;
END;
/


CREATE OR REPLACE 
PACKAGE BODY XXDM_FORECAST_TO_ASCP_PKG
IS
    /*------------------------------------------------------------------------------
     Created by 
     Function:
                Create view for tranfering demantra data to ASCP.
     Logicality:
                this procedure use to create view
                
     Input:     p_level_id		-- demantra data model items level id
				p_member_id		-- demantra data model items member id
     Output:
		        null
   ------------------------------------------------------------------------------*/
   PROCEDURE xxdm_upload_forecast_to_ascp
    ( p_level_id IN VARCHAR2, p_member_id IN VARCHAR2)
    IS       
       v_level_id                      NUMBER;
       v_member_id                     NUMBER;
       v_transfer_id                   NUMBER;
       v_transfer_q_id                 NUMBER;      
       v_count                         NUMBER;
       v_notice_id                     VARCHAR2 (100);      
       v_sysdate                       DATE;       
       v_id_field                      VARCHAR2 (100);
       v_yyyy_mm_dd                    VARCHAR2 (10);
       v_product_line                  VARCHAR2 (10);
       v_ro                            VARCHAR2 (10);
       v_ppp                           VARCHAR2 (10);
       v_ppp_new                       VARCHAR2 (10);
       v_tempname                      VARCHAR2 (100);
       v_left_notice_id                VARCHAR2 (100);
       v_int_comb_viewname             VARCHAR2 (100);
       v_int_date_tablename            VARCHAR2 (100); 
    BEGIN

       apps.xxascp_util_pkg.dbms_output_lines('p_level_id='||p_level_id);      --for test   
       apps.xxascp_util_pkg.dbms_output_lines('p_member_id='||p_member_id);      --for test        

       --check parameter is correct 
       g_err_position:='E0001-01';
       g_err_desc:='level_id and member_id must be number type. level_id='||p_level_id||',member_id='||p_member_id;
       v_level_id:=TO_NUMBER(p_level_id);
       v_member_id:=TO_NUMBER(p_member_id);
       
       --get additional filter
       g_err_position:='E0001-02';
       g_err_desc:='error occurs in invoking function [xxdm_util_pkg.xxdm_Get_Additional_Filter]';
       g_additional_filter :=
          xxdm_util_pkg.xxdm_get_additional_filter ('mdp_matrix',
                                                    v_level_id,
                                                    v_member_id
                                                    );
       apps.xxascp_util_pkg.dbms_output_lines('g_additional_filter=' || g_additional_filter);
       
       --check LTB-B exist
       g_err_position:='E0001-03';
       g_err_desc:='error occurs in invoking function [xxdm_check_ltb_b_exist]';
       g_ltb_b_exist := xxdm_check_ltb_b_exist(g_additional_filter); 
       
       --get user_id by user_name
       g_err_position:='E0001-04';
       g_err_desc:='data not exist in table [user_id],column UPPER(user_name)='||C_USERNAME;
       SELECT user_id
         INTO g_user_id
         FROM demantra.user_id
        WHERE UPPER(user_name) = C_USERNAME;
       apps.xxascp_util_pkg.dbms_output_lines('g_user_id='||g_user_id);      --for test       
       
       
       g_err_position:='E0001-05';
       g_err_desc:='parameter max_sales_date not exits.';
       SELECT TO_DATE (s.pval, d.pval)
         INTO g_max_salles_date
         FROM db_params d, sys_params s
        WHERE d.pname = 'nls_date_format' AND s.pname = 'max_sales_date';
    
       g_err_position:='E0001-06';
       g_err_desc:='parameter min_fore_sales_date not exits.';
       SELECT TO_DATE (s.pval, d.pval)
         INTO g_min_forcast_date
         FROM demantra.db_params d, demantra.sys_params s
        WHERE d.pname = 'nls_date_format' AND s.pname = 'min_fore_sales_date';
       apps.xxascp_util_pkg.dbms_output_lines('g_max_salles_date=' || g_max_salles_date);
       apps.xxascp_util_pkg.dbms_output_lines('g_min_forcast_date=' || g_min_forcast_date);      
    
       --get notice_id by level_id and member_id
       g_err_position:='E0002';
       g_err_desc:='error occurs in invoking function [xxdm_util_pkg.xxdm_get_notice_id] when getting notice_id,parameter level_id='||p_level_id||',member_id='||p_member_id;
       v_notice_id := demantra.xxdm_util_pkg.xxdm_get_notice_id (v_level_id, v_member_id);
       apps.xxascp_util_pkg.dbms_output_lines('v_notice_id=' || v_notice_id); --for test
       
       --v_notice_id :='20090525-PRJ-BQP-LTB-006';    -- for test
       --g_ltb_b_exist:=TRUE;   -- for test
       IF g_ltb_b_exist THEN
           g_err_position:='E0001-07';
           g_err_desc:='eol_date is null in table [t_ro_ltb].t_ro_ltb_desc='||v_notice_id;
           SELECT TO_CHAR (TRUNC (eol_date, 'MM'), g_eol_date_format)
            INTO g_eol_date_c
           FROM demantra.t_ro_ltb
            WHERE t_ro_ltb_desc = v_notice_id;
            
           apps.xxascp_util_pkg.dbms_output_lines('g_eol_date_c=' || g_eol_date_c); --for test
       END IF;
       
       
       --get yyyy-mm-dd of notice id
       g_err_position:='E0002-01'; 
       g_err_desc:='error occurs when get yyyy-mm-dd of notice id ';
       v_left_notice_id:=v_notice_id;       
       v_yyyy_mm_dd:=demantra.xxdm_util_pkg.xxdm_get_substr_before_split(v_left_notice_id,'-');
       v_left_notice_id:=demantra.xxdm_util_pkg.xxdm_get_substr_after_split(v_left_notice_id,'-');
       apps.xxascp_util_pkg.dbms_output_lines('v_yyyy_mm_dd=' || v_yyyy_mm_dd);  -- for test   
       
       --get product line of notice id
       g_err_position:='E0002-02'; 
       g_err_desc:='error occurs when get product line of notice id ';
       v_product_line:=demantra.xxdm_util_pkg.xxdm_get_substr_before_split(v_left_notice_id,'-');
       v_left_notice_id:=demantra.xxdm_util_pkg.xxdm_get_substr_after_split(v_left_notice_id,'-');       
       apps.xxascp_util_pkg.dbms_output_lines('v_product_line=' || v_product_line);  -- for test
       
       --get ro of notice id
       g_err_position:='E0002-03'; 
       g_err_desc:='error occurs when get ro of notice id ';       
       v_ro:=UPPER(demantra.xxdm_util_pkg.xxdm_get_substr_before_split(v_left_notice_id,'-'));
       v_left_notice_id:=demantra.xxdm_util_pkg.xxdm_get_substr_after_split(v_left_notice_id,'-');
       apps.xxascp_util_pkg.dbms_output_lines('v_ro=' || v_ro);  -- for test
       
       --get ppp of notice id
       g_err_position:='E0002-04'; 
       g_err_desc:='error occurs when get ppp of notice id ';
       v_ppp:=UPPER(demantra.xxdm_util_pkg.xxdm_get_substr_before_split(v_left_notice_id,'-'));
       v_left_notice_id:=demantra.xxdm_util_pkg.xxdm_get_substr_after_split(v_left_notice_id,'-');       
       apps.xxascp_util_pkg.dbms_output_lines('v_ppp=' || v_ppp);  -- for test
       v_ppp_new := v_ppp;
       
       apps.xxascp_util_pkg.dbms_output_lines('v_left_notice_id=' || v_left_notice_id);  -- for test       
      
       IF g_ltb_b_exist THEN       
           --execute notice id of LB1        
           v_ppp_new :='LB1';     
           v_notice_id := v_yyyy_mm_dd || '_' || v_product_line || '_' || v_ro || '_' || v_ppp_new || '_' || v_left_notice_id;
           apps.xxascp_util_pkg.dbms_output_lines('v_notice_id=' || v_notice_id);  -- for test
           xxdm_upload_fc_by_noticeid(v_level_id,v_member_id,v_notice_id,v_ro,v_ppp,v_ppp_new,g_user_id,g_additional_filter,g_max_salles_date);          --upload forcast by notice id
           
           --execute notice id of LB3
           v_ppp_new :='LB3';  
           v_notice_id := v_yyyy_mm_dd || '_' || v_product_line || '_' || v_ro || '_' || v_ppp_new || '_' || v_left_notice_id;
           apps.xxascp_util_pkg.dbms_output_lines('v_notice_id=' || v_notice_id);  -- for test
           xxdm_upload_fc_by_noticeid(v_level_id,v_member_id,v_notice_id,v_ro,v_ppp,v_ppp_new,g_user_id,g_additional_filter,g_max_salles_date);          --upload forcast by notice id
       ELSE
           v_notice_id := REPLACE (v_notice_id, '-', '_');
           apps.xxascp_util_pkg.dbms_output_lines('v_notice_id=' || v_notice_id);  -- for test          
           xxdm_upload_fc_by_noticeid(v_level_id,v_member_id,v_notice_id,v_ro,v_ppp,v_ppp_new,g_user_id,g_additional_filter,g_max_salles_date);          --upload forcast by notice id
       END IF;                    
    
       g_err_position:=C_SUCC_FLAG;   
       g_err_desc:='program end.'; 
       
       apps.xxascp_util_pkg.dbms_output_lines('g_err_position=' || g_err_position);  -- for test
       apps.xxascp_util_pkg.dbms_output_lines('g_err_desc=' || g_err_desc);          -- for test */
     
     
    EXCEPTION 
       WHEN OTHERS THEN
            ROLLBACK;
            g_err_desc := g_err_desc || 'SQLERRM:' || substr(SQLERRM,1,3000);
            apps.xxascp_util_pkg.dbms_output_lines('g_err_position=' || g_err_position);  -- for test
            apps.xxascp_util_pkg.dbms_output_lines('g_err_desc=' || g_err_desc);          -- for test   
            apps.xxascp_util_pkg.insert_log('Demantra',NULL,NULL,NULL,g_err_position,
                                          g_err_desc,0,NULL,NULL,SYSDATE,
                                          NULL,NULL,NULL,NULL,NULL);
    END;


  
   
   /*------------------------------------------------------------------------------
     Created by 
     Function:
                Create view for tranfering demantra data to ASCP.
     Logicality:
                this procedure use to create view
                
     Input:     p_level_id		        -- demantra data model items level id
				p_member_id		        -- demantra data model items member id
				p_notice_id		        -- notice id
				p_ro		            -- ro in notice id
				p_ppp		            -- ppp in notice id
				p_ppp_new		        -- replace ppp with LB1 or LB3 in notice id
				p_user_id		        -- user id
				p_additional_filter		-- addtional filter
				p_max_salles_date		-- max sales date
				
     Output:
		        null
   ------------------------------------------------------------------------------*/
   PROCEDURE xxdm_upload_fc_by_noticeid
                                    ( p_level_id IN VARCHAR2, 
                                      p_member_id IN VARCHAR2,
                                      p_notice_id IN VARCHAR2,
                                      p_ro IN VARCHAR2,
                                      p_ppp IN VARCHAR2,
                                      p_ppp_new IN VARCHAR2,
                                      p_user_id IN NUMBER,
                                      p_additional_filter IN VARCHAR2,
                                      p_max_salles_date IN DATE)
    IS              
       v_transfer_id                   NUMBER;
       v_transfer_q_id                 NUMBER;      
       v_count                         NUMBER;
       v_sysdate                       DATE;
       v_id_field                      VARCHAR2 (100);
       v_yyyy_mm_dd                    VARCHAR2 (10);
       v_product_line                  VARCHAR2 (10);       
       v_tempname                      VARCHAR2 (100);
       v_left_notice_id                VARCHAR2 (100);
       v_int_comb_viewname             VARCHAR2 (100);
       v_int_date_tablename            VARCHAR2 (100); 
       --Add by ken bu 20090616
       v_error_code NUMBER;
       v_error_msg  VARCHAR2(200);
    BEGIN             
    
       SELECT count(*) 
         INTO v_count
         FROM demantra.transfer_list
        WHERE NAME = p_notice_id;
    
       g_err_position:='E0004';
       g_err_desc:='E0004';
       
       IF NOT v_count > 0
       THEN
          -- begin: INSERT DATA and get v_transfer_id and v_transfer_q_id
          xxdm_insert_init_data(p_notice_id,p_ro,p_ppp,p_user_id,v_transfer_id,v_transfer_q_id);
          -- end: INSERT DATA 
       ELSE          
           SELECT ID
            INTO v_transfer_id
            FROM demantra.transfer_list
           WHERE NAME = p_notice_id;
        
          g_err_position:='E0021-000';
          g_err_desc:='data not exist in table [transfer_query],column transfer_id='||v_transfer_id;
          SELECT MAX(ID)
            INTO v_transfer_q_id
            FROM transfer_query
           WHERE transfer_id = v_transfer_id;
           
          g_err_position:='E0021-001';
          g_err_desc:='error ocurrs when update transfer_query collumn query_name.';
          UPDATE transfer_query
              SET query_name = REPLACE (p_notice_id, '_', '-')
             WHERE ID = v_transfer_q_id;
          commit;
          
           
       END IF;
    
       apps.xxascp_util_pkg.dbms_output_lines('v_transfer_id=' || v_transfer_id);
       apps.xxascp_util_pkg.dbms_output_lines('v_transfer_q_id=' || v_transfer_q_id);    
       
       
       --begin: Create view int_comb_
       g_err_position:='E0024';
       g_err_desc:='E0024';
       
       v_int_comb_viewname :=
                              'INT_COMB_' || v_transfer_q_id || '_' || p_user_id;
       
       
       xxdm_create_int_comb_view(p_ro,v_int_comb_viewname,p_additional_filter);
       --end: Create view int_comb_
    
       --begin: Create table int_date_
       g_err_position:='E0028';
       g_err_desc:='E0028';
       v_int_date_tablename :=
                              'INT_DATE_' || v_transfer_q_id || '_' || p_user_id;
       apps.xxascp_util_pkg.dbms_output_lines('v_int_date_tablename=' || v_int_date_tablename);
    
       xxdm_create_int_date_table(v_int_date_tablename);
       --end: Create table int_date_
       
       --begin: insert into int_date_ table
       xxdm_insert_int_date_table(p_ppp,v_int_date_tablename);       
       --end: insert into int_date_ table
    
    
       --begin: Create export view
       xxdm_create_export_view(p_notice_id,p_ro,p_ppp_new,v_int_comb_viewname,v_int_date_tablename,
                               p_additional_filter,p_max_salles_date);       
       --end: Create export view
       
       g_err_position:='E0040';   
       g_err_desc:='error occurs when calling r12 function[apps.msd_dem_upload_forecast.upload_forecast('||p_notice_id||')] upload forcast';   
       
       --call r12 function upload forcast
       --apps.msd_dem_upload_fc_test.upload_forecast(p_notice_id);  --for test
       apps.msd_dem_upload_forecast.upload_forecast(REPLACE(p_notice_id, '_', '-'));
       
       --Begin Add by ken bu 20090616
       apps.xxascp_cns_item_pkg.insert_base_data
                (
                 p_notice_id  => p_notice_id,
                 o_error_code => v_error_code,
                 o_error_msg  => v_error_msg
                );
       IF v_error_code = 2 THEN 
           g_err_position:='E0050';        
           g_err_desc:='error occurs when execute xxascp_cns_item_pkg:'||v_error_msg;
           RAISE_APPLICATION_ERROR(-20001,g_err_desc );
           
       ELSE
       
       --End Add by ken bu 20090616
       
           g_err_position:=C_SUCC_FLAG;
           g_err_desc:='execute xxdm_upload_fc_by_noticeid successfully,notice id='||p_notice_id;
           
           apps.xxascp_util_pkg.dbms_output_lines('g_err_position=' || g_err_position);  -- for test
           apps.xxascp_util_pkg.dbms_output_lines('g_err_desc=' || g_err_desc);          -- for test
       END IF;
       -- remove the mark by Terry Lee on 2009/6/17 from Steven's request
       --insert step log
       xxdm_util_pkg.insert_step_log_common(p_notice_id,'O','','','','','006'); 
    
    END;
   

    /*---------------------------------------------------------
    Created by 
    PROCEDURE:
               insert init data
    LOGICALITY:
               <_____Optional_____Internal_Logic_Description>
    INPUT:
               p_notice_id                  notice_id
               p_ppp                        ppp of notice id
               p_user_id                    user id
    OUTPUT:
               p_transfer_id                transfer_id
               p_transfer_q_id              transfer_q_id
    ---------------------------------------------------------*/
    PROCEDURE xxdm_insert_init_data (p_notice_id IN VARCHAR2,
                                     p_ro IN VARCHAR2, 
                                     p_ppp IN VARCHAR2,
                                     p_user_id IN NUMBER,
                                     p_transfer_id OUT VARCHAR2,
                                     p_transfer_q_id OUT VARCHAR2
    )
    IS
       v_transfer_id                   NUMBER;
       v_transfer_q_id                 NUMBER;      
       v_level1_id                     NUMBER;
       v_level2_id                     NUMBER;
       v_series_id_1                   NUMBER;
       v_series_id_2                   NUMBER;
       v_series_id_3                   NUMBER;
       v_series_id_4                   NUMBER;
       v_series_id_5                   NUMBER;
       v_series_id_6                   NUMBER;
    BEGIN
    
      --INSERT INTO  transfer_list
      g_err_position:='E0005';
      g_err_desc:='error occurs in invoking function [get_seq_nextval],column transfer_id='||v_transfer_id;
      demantra.get_seq_nextval ('TRANSFER_LIST',
                       'ID',
                       'TRANSFER_ID_SEQ',
                       v_transfer_id
                      );
      apps.xxascp_util_pkg.dbms_output_lines('v_transfer_id=' || v_transfer_id);  -- for test
      
      g_err_position:='E0006';
      g_err_desc:='error occurs when inserting data to table [transfer_list],column transfer_id='||v_transfer_id;
      INSERT INTO demantra.transfer_list
                  (ID, NAME, description, user_id, use_external_schema
                  )
           VALUES (v_transfer_id, p_notice_id, p_notice_id, p_user_id, 0
                  );
                  
      
      --INSERT INTO transfer_query
      g_err_position:='E0007';
      g_err_desc:='error occurs in invoking function [get_seq_nextval],column transfer_q_id='||v_transfer_q_id;
      demantra.get_seq_nextval ('TRANSFER_QUERY',
                       'ID',
                       'TRANSFER_QUERY_SEQ',
                       v_transfer_q_id
                      );                      
      
      apps.xxascp_util_pkg.dbms_output_lines('v_transfer_q_id=' || v_transfer_q_id); -- for test
      g_err_position:='E0008';
      g_err_desc:='error occurs when inserting data to table [transfer_query],column transfer_q_id='||v_transfer_q_id;
      g_sql :=
            'INSERT INTO demantra.transfer_query(id,transfer_id,presentation_type,view_name,table_name,
                           file_name,delimiter,is_fixed_width,view_type,use_real_proportion,
                           insertnewcombinations,insertforecasthorizon,query_name,description,time_res_id,
                           from_date,until_date,relative_date,relative_from_date,relative_until_date,
                           unit_id,index_id,data_scale,dm_id,schema_id,
                           query_id,integration_type,export_type,batch_file,import_from_file,
                           last_export_date,filter_sd_by_gl)
                   VALUES('
         || v_transfer_q_id
         || ','
         || v_transfer_id
         || ',1,''BIEO_'
         || p_notice_id
         || ''',''BIIO_'
         || p_notice_id
         || ''',
                        Null,Null,Null,0,0,
                        0,0,'''
         || replace(p_notice_id, '_', '-')
         || ''',Null,5,
                        SYSDATE,SYSDATE,1,1,1,
                        1,Null,1,Null,Null,
                        Null,2,1,Null,0,
                        Null,0)';
      apps.xxascp_util_pkg.dbms_output_lines('g_sql=' || g_sql);  -- for test
      EXECUTE IMMEDIATE g_sql;
      
      g_err_position:='E0009';
      g_err_desc:='data not exist in table [group_tables],column UPPER(table_label)='||C_TABLELABEL_1;
      SELECT group_table_id
        INTO v_level1_id
        FROM demantra.group_tables
       WHERE UPPER(table_label) = C_TABLELABEL_1;               --Part Number (SP)
       
      g_err_position:='E0010';
      g_err_desc:='data not exist in table [group_tables],column UPPER(table_label)='||C_TABLELABEL_2;
      SELECT group_table_id
        INTO v_level2_id
        FROM demantra.group_tables
       WHERE UPPER(table_label) = C_TABLELABEL_2;                         --RO Org
      apps.xxascp_util_pkg.dbms_output_lines('v_level1_id=' || v_level1_id);  -- for test
      apps.xxascp_util_pkg.dbms_output_lines('v_level2_id=' || v_level2_id);   -- for test

      --Insert into transfer_query_levels
      -- Level 1 Level_id of 'Part Number (SP)' --"Primary Item"
      g_err_position:='E0011';
      g_err_desc:='error occurs when insert data to table [transfer_query_levels],column [ID]='||v_transfer_q_id||',level_id='||v_level1_id;
      INSERT INTO demantra.transfer_query_levels
                  (ID, level_id, lorder
                  )
           VALUES (v_transfer_q_id, v_level1_id, 1
                  );

      -- Level 2 Level_id of "RO Org"
      g_err_position:='E0012';
      g_err_desc:='error occurs when insert data to table [transfer_query_levels],column [ID]='||v_transfer_q_id||',level_id='||v_level2_id;
      INSERT INTO demantra.transfer_query_levels
                  (ID, level_id, lorder
                  )
           VALUES (v_transfer_q_id, v_level2_id, 2
                  );

      --Get series_id
      g_err_position:='E0013';
      g_err_desc:='data not exist in table [computed_fields],column UPPER(computed_title)='||C_COMPUTED_TITLE_1;
      v_series_id_1:=demantra.xxdm_util_pkg.xxdm_get_series_id_by_title(C_COMPUTED_TITLE_1);  --Demand Priority
      
      g_err_position:='E0014';
      g_err_desc:='data not exist in table [computed_fields],column UPPER(computed_title)='||C_COMPUTED_TITLE_2;
      v_series_id_2:=demantra.xxdm_util_pkg.xxdm_get_series_id_by_title(C_COMPUTED_TITLE_2);  --Final Forecast
          
      g_err_position:='E0015';
      g_err_desc:='data not exist in table [computed_fields],column UPPER(computed_title)='||C_COMPUTED_TITLE_3;
      v_series_id_3:=demantra.xxdm_util_pkg.xxdm_get_series_id_by_title(C_COMPUTED_TITLE_3);  --Item Destination Key  

      g_err_position:='E0016';
      g_err_desc:='data not exist in table [computed_fields],column UPPER(computed_title)='||C_COMPUTED_TITLE_4;
      v_series_id_4:=demantra.xxdm_util_pkg.xxdm_get_series_id_by_title(C_COMPUTED_TITLE_4);  --Organization Destination Key
      
      g_err_position:='E0016-01';
      g_err_desc:='data not exist in table [computed_fields],column UPPER(computed_title)='||C_COMPUTED_TITLE_5;
      v_series_id_5:=demantra.xxdm_util_pkg.xxdm_get_series_id_by_title(C_COMPUTED_TITLE_5);  --LTB 1 year Agg 
                
      g_err_position:='E0016-02';
      g_err_desc:='data not exist in table [computed_fields],column UPPER(computed_title)='||C_COMPUTED_TITLE_6;
      v_series_id_6:=demantra.xxdm_util_pkg.xxdm_get_series_id_by_title(C_COMPUTED_TITLE_6);  --LTB 3 year Agg
       
      apps.xxascp_util_pkg.dbms_output_lines('v_series_id_1=' || v_series_id_1);
      apps.xxascp_util_pkg.dbms_output_lines('v_series_id_2=' || v_series_id_2);
      apps.xxascp_util_pkg.dbms_output_lines('v_series_id_3=' || v_series_id_3);
      apps.xxascp_util_pkg.dbms_output_lines('v_series_id_4=' || v_series_id_4);
      apps.xxascp_util_pkg.dbms_output_lines('v_series_id_5=' || v_series_id_5);
      apps.xxascp_util_pkg.dbms_output_lines('v_series_id_6=' || v_series_id_6);

      --Insert into transfer_query_series
      g_err_position:='E0017';
      g_err_desc:='error occurs when insert data to table [transfer_query_series],column [ID]='||v_transfer_q_id||',series_id='||v_series_id_1;
      INSERT INTO demantra.transfer_query_series
                  (ID, series_id, load_option, purge_option
                  )
           VALUES (v_transfer_q_id, v_series_id_1, 0, 0
                  );             -- series_id of "Demand Priority"

        g_err_position:='E0018';
          g_err_desc:='error occurs when insert data to table [transfer_query_series],column ID='||v_transfer_q_id||',series_id='||v_series_id_2;
          INSERT INTO demantra.transfer_query_series
                      (ID, series_id, load_option, purge_option
                      )
               VALUES (v_transfer_q_id, v_series_id_2, 0, 0
                      );                          -- series_id of "Final Forecast"

      /*            
      IF g_ltb_b_exist THEN
        g_err_position:='E0017-01';
        g_err_desc:='error occurs when insert data to table [transfer_query_series],column [ID]='||v_transfer_q_id||',series_id='||v_series_id_5;
        INSERT INTO demantra.transfer_query_series
               (ID, series_id, load_option, purge_option
               )
               VALUES (v_transfer_q_id, v_series_id_5, 0, 0
               );         -- series_id of "LTB 1 year Agg"
        g_err_position:='E0017-02';
        g_err_desc:='error occurs when insert data to table [transfer_query_series],column [ID]='||v_transfer_q_id||',series_id='||v_series_id_6;
        INSERT INTO demantra.transfer_query_series
               (ID, series_id, load_option, purge_option
               )
               VALUES (v_transfer_q_id, v_series_id_6, 0, 0
               );         -- series_id of "LTB 3 year Agg"        
      ELSE
      
        g_err_position:='E0018';
          g_err_desc:='error occurs when insert data to table [transfer_query_series],column ID='||v_transfer_q_id||',series_id='||v_series_id_2;
          INSERT INTO demantra.transfer_query_series
                      (ID, series_id, load_option, purge_option
                      )
               VALUES (v_transfer_q_id, v_series_id_2, 0, 0
                      );                          -- series_id of "Final Forecast"
      END IF;
      */
      g_err_position:='E0019';
      g_err_desc:='error occurs when insert data to table [transfer_query_series],column ID='||v_transfer_q_id||',series_id='||v_series_id_3;
      INSERT INTO demantra.transfer_query_series
                  (ID, series_id, load_option, purge_option
                  )
           VALUES (v_transfer_q_id, v_series_id_3, 0, 0
                  );                    -- series_id of "Item Destination Key"

      g_err_position:='E0020';
      g_err_desc:='error occurs when insert data to table [transfer_query_series],column ID='||v_transfer_q_id||',series_id='||v_series_id_4;
      INSERT INTO demantra.transfer_query_series
                  (ID, series_id, load_option, purge_option
                  )
           VALUES (v_transfer_q_id, v_series_id_4, 0, 0
                  );            -- series_id of "Organization Destination Key"        
      
      p_transfer_id:=v_transfer_id;
      p_transfer_q_id:=v_transfer_q_id;                                 
      COMMIT;
    END;    


    /*---------------------------------------------------------
    Created by 
    PROCEDURE:
               Create init comb view
    LOGICALITY:
               <_____Optional_____Internal_Logic_Description>
    INPUT:
               p_ro                   ro value
               p_view_name            view name
               p_additional_filter    additional filter string
    OUTPUT:
               null
    ---------------------------------------------------------*/
   /* Formatted on 2009/03/11 13:05 (Formatter Plus v4.8.7) */
    PROCEDURE xxdm_create_int_comb_view (
       p_ro                  IN   VARCHAR2,
       p_view_name           IN   VARCHAR2,
       p_additional_filter   IN   VARCHAR2
    )
    IS
       v_viewname   VARCHAR2 (100);
    BEGIN
       v_viewname := 'demantra.' || p_view_name;
    
       /*IF p_ro = 'TPC'
       THEN
          g_err_position := 'E0026';
          g_err_desc :=
                      'TPC error occurs in create view,view name=' || v_viewname;
          g_sql :=
                'CREATE OR REPLACE VIEW '
             || v_viewname
             || ' AS
                            SELECT (nvl(rownum, 0)) AS comb_id,
                                   t_comb_list.level1,
                                   t_comb_list.level_id1
                              FROM (SELECT DISTINCT t_ep_item.item                AS level1,
                                                    t_ep_item.t_ep_item_ep_id     AS level_id1
                                      FROM mdp_matrix, t_ep_item
                                     WHERE mdp_matrix.t_ep_item_ep_id = t_ep_item.t_ep_item_ep_id
                                       AND '
             || p_additional_filter
             || ') t_comb_list';
       ELSE
          g_err_position := 'E0027';
          g_err_desc := 'error occurs in create view,view name=' || v_viewname;
          g_sql :=
                'CREATE OR REPLACE VIEW '
             || v_viewname
             || ' AS
                            SELECT (nvl(rownum, 0)) AS comb_id,
                                   t_comb_list.level1,
                                   t_comb_list.level_id1,
                                   t_comb_list.level2,
                                   t_comb_list.level_id2
                              FROM (SELECT DISTINCT t_ep_item.item                AS level1,
                                                    t_ep_item.t_ep_item_ep_id     AS level_id1,
                                                    t_ep_ro_org.ro_org            AS level2,
                                                    t_ep_ro_org.t_ep_ro_org_ep_id AS level_id2
                                      FROM mdp_matrix, t_ep_item, t_ep_ro_org
                                     WHERE mdp_matrix.t_ep_item_ep_id = t_ep_item.t_ep_item_ep_id
                                       AND mdp_matrix.t_ep_ro_org_ep_id = t_ep_ro_org.t_ep_ro_org_ep_id
                                       AND '
             || p_additional_filter
             || ') t_comb_list';
       END IF;*/
       
      g_err_position := 'E0027';
      g_err_desc := 'error occurs in create view,view name=' || v_viewname;
      g_sql :=
            'CREATE OR REPLACE VIEW '
         || v_viewname
         || ' AS
                        SELECT (nvl(rownum, 0)) AS comb_id,
                               t_comb_list.level1,
                               t_comb_list.level_id1,
                               t_comb_list.level2,
                               t_comb_list.level_id2
                          FROM (SELECT DISTINCT t_ep_item.item                AS level1,
                                                t_ep_item.t_ep_item_ep_id     AS level_id1,
                                                t_ep_ro_org.ro_org            AS level2,
                                                t_ep_ro_org.t_ep_ro_org_ep_id AS level_id2
                                  FROM mdp_matrix, t_ep_item, t_ep_ro_org
                                 WHERE mdp_matrix.t_ep_item_ep_id = t_ep_item.t_ep_item_ep_id
                                   AND mdp_matrix.t_ep_ro_org_ep_id = t_ep_ro_org.t_ep_ro_org_ep_id
                                   AND '
         || p_additional_filter
         || ') t_comb_list';
    
       apps.xxascp_util_pkg.dbms_output_lines ('g_sql=' || g_sql);
    
       EXECUTE IMMEDIATE g_sql;
    END;    
    
    
    /*---------------------------------------------------------
    Created by 
    PPROCEDURE:
               Create init date table
    LOGICALITY:
               <_____Optional_____Internal_Logic_Description>
    INPUT:
               p_table_name           table name
    OUTPUT:
               null
    ---------------------------------------------------------*/
    /* Formatted on 2009/03/11 13:04 (Formatter Plus v4.8.7) */
    PROCEDURE xxdm_create_int_date_table (p_table_name IN VARCHAR2)
    IS
       v_tablename   VARCHAR2 (100);
       v_tempname    VARCHAR2 (100);
       v_count       NUMBER;
    BEGIN
       v_tablename := 'demantra.' || p_table_name;
    
       SELECT COUNT (*)
         INTO v_count
         FROM user_tables
        WHERE table_name = p_table_name;
    
       g_err_position := 'E0029';
       g_err_desc := 'table ' || p_table_name || ' not exists.';
    
       IF v_count > 0
       THEN
          g_err_position := 'E0030';
          g_err_desc :=
                    'error occurs when deleteing data,table name=' || v_tablename;
          g_sql := 'DELETE FROM ' || v_tablename;
    
          apps.xxascp_util_pkg.dbms_output_lines ('g_sql=' || g_sql);
          EXECUTE IMMEDIATE g_sql;
    
          COMMIT;
       ELSE
          g_err_position := 'E0031';
          g_err_desc := 'error occurs when creating table ' || v_tablename;
          g_sql :=
                'CREATE TABLE '
             || v_tablename
             || '
                        (
                          FROM_SALES_DATE DATE NOT NULL,
                          TO_SALES_DATE   DATE,
                          MADAD           NUMBER
                        )';
          apps.xxascp_util_pkg.dbms_output_lines ('g_sql=' || g_sql);
    
          EXECUTE IMMEDIATE g_sql;
       END IF;
    
       --end: Create table int_date_
    
       --begin: Create/Recreate indexes
       v_tempname := p_table_name || '_X';
       g_err_position := 'E0032';
       g_err_desc :=
             'index data not exists in table [user_indexes],column [index_name]='
          || v_tempname;
    
       SELECT COUNT (*)
         INTO v_count
         FROM user_indexes
        WHERE index_name = v_tempname;
    
       apps.xxascp_util_pkg.dbms_output_lines ('v_count=' || v_count);
    
       v_tempname:='demantra.'||v_tempname;
       IF NOT v_count > 0
       THEN
          g_err_position := 'E0033';
          g_err_desc :=
                'error occurs when creating index ['
             || v_tempname
             || '] on table ['
             || v_tablename
             || ']';
          g_sql :=
                'CREATE UNIQUE INDEX '
             || v_tempname
             || ' on '
             || v_tablename
             || ' (FROM_SALES_DATE,TO_SALES_DATE)';
          apps.xxascp_util_pkg.dbms_output_lines ('g_sql=' || g_sql);
    
          EXECUTE IMMEDIATE g_sql;
       END IF;
    END;
    
    
    /*---------------------------------------------------------
    Created by 
    PPROCEDURE:
               insert data to int date table
    LOGICALITY:
               <_____Optional_____Internal_Logic_Description>
    INPUT:
               p_ppp                  ppp value
               p_table_name           table name
    OUTPUT:
               null
    ---------------------------------------------------------*/
    PROCEDURE xxdm_insert_int_date_table (p_ppp IN VARCHAR2,
                                          p_table_name IN VARCHAR2)
    IS
       v_tablename                     VARCHAR2 (100);
       v_count                         NUMBER;
       v_month_first_day               DATE;
       v_month_last_day                DATE;
       v_sysdate                       DATE;
       exp_common_raise exception;
    BEGIN
       v_tablename := 'demantra.' || p_table_name;
       IF p_ppp = 'IRP'
       THEN
          v_count := C_MONTHS_IRP;
       ELSIF p_ppp = 'LTB'
       THEN
          v_count := C_MONTHS_LTB;
       ELSIF p_ppp = 'ROL'
       THEN
          v_count := C_MONTHS_ROLLING;
       ELSE
          v_count :=0; --occurs error
          g_err_position:='E0034';
          g_err_desc:='ppp not in(LTB,ROL,IRP)v_ppp='||p_ppp;
          raise exp_common_raise;
       END IF;
    
       g_err_position:='E0035';
       g_err_desc:='E0035';
       --v_sysdate :=sysdate;
       --v_sysdate :=sysdate; crp2 remark 
       select to_date(pval,'mm-dd-yyyy hh24:mi:ss') into v_sysdate from demantra.SYS_PARAMS
       where pname='max_sales_date';


          FOR i IN 0 .. v_count - 1
       LOOP
          /*IF p_ppp = 'IRP' OR p_ppp = 'LTB' 
          THEN
            v_month_first_day := TRUNC(ADD_MONTHS (LAST_DAY (v_sysdate), i),'mm'); -- first day of month  
            v_month_last_day := TRUNC(ADD_MONTHS (LAST_DAY (v_sysdate), i),'mm');  -- last day of month 
          ELSIF p_ppp = 'MP'
          THEN
            v_month_first_day := TRUNC(ADD_MONTHS (LAST_DAY (v_sysdate), i+1),'mm'); -- first day of month  
            v_month_last_day := TRUNC(ADD_MONTHS (LAST_DAY (v_sysdate), i+1),'mm');  -- last day of month 
          END IF;*/
          
          v_month_first_day := TRUNC(ADD_MONTHS (LAST_DAY (v_sysdate), i+1),'mm'); -- first day of month  
          v_month_last_day := TRUNC(ADD_MONTHS (LAST_DAY (v_sysdate), i+1),'mm');  -- last day of month 
    
          g_err_position:='E0036';     
          g_err_desc:='E0036';                                                       
          g_sql :=
                'INSERT INTO '
             || v_tablename
             || ' (FROM_SALES_DATE,TO_SALES_DATE,MADAD)
                        VALUES(to_date('''
             || TO_CHAR (v_month_first_day, 'yyyy-MM-dd')
             || ''',''yyyy-MM-dd HH24:mi:ss''),
                               to_date('''
             || TO_CHAR (v_month_last_day, 'yyyy-MM-dd')
             || ''',''yyyy-MM-dd HH24:mi:ss''),1)';
          apps.xxascp_util_pkg.dbms_output_lines('g_sql=' || g_sql);
    
          g_err_position:='E0037';    
          g_err_desc:='error occurs when inserting data to table ['||v_tablename||']'; 
          EXECUTE IMMEDIATE g_sql;
       END LOOP;
    
       IF v_count > 0
       THEN
          g_err_position:='E0038';        
          g_err_desc:='error occurs when commit data';
          COMMIT;
       END IF;
      
    END;
    
    
    /*---------------------------------------------------------
    Created by 
    PPROCEDURE:
               Create export view
    LOGICALITY:
               <_____Optional_____Internal_Logic_Description>
    INPUT:
               p_notice_id               notice id
               p_ro                      ro of notiec id
               p_ppp                     ppp of notice id
               p_int_comb_viewname       int comb view name
               p_int_date_tablename      int date table name
               p_additional_filter       additional fiter
               p_max_salles_date         max sales date
    OUTPUT:
               null
    ---------------------------------------------------------*/
    PROCEDURE xxdm_create_export_view (p_notice_id IN VARCHAR2,            
                                       p_ro IN VARCHAR2, 
                                       p_ppp_new IN VARCHAR2,                    
                                       p_int_comb_viewname IN VARCHAR2,
                                       p_int_date_tablename IN VARCHAR2,
                                       p_additional_filter IN VARCHAR2,
                                       p_max_salles_date IN DATE)
    IS
        v_view_name                     VARCHAR2(100); 
        v_int_comb_viewname             VARCHAR2(100);
        v_int_date_tablename            VARCHAR2(100);        
        v_replace_string_1              VARCHAR2 (2000);
        v_replace_string_2              VARCHAR2 (2000);  
        v_replace_string_3              VARCHAR2 (2000);
    BEGIN
    
       v_int_comb_viewname:='demantra.'||p_int_comb_viewname;
       v_int_date_tablename:='demantra.'||p_int_date_tablename;
       v_view_name:='demantra.BIEO_'||p_notice_id;
       
       g_err_position:='E0038-01';        
       g_err_desc:='E0038-01';       
       
       /*IF p_ppp_new='LB1' THEN
           v_replace_string_1:='SUM(branch_data.FCST_LTB_1Y_AGG) AS c_pred';
       ELSIF p_ppp_new='LB3' THEN
           v_replace_string_1:='SUM(branch_data.FCST_LTB_3Y_AGG) AS c_pred';
       ELSE */
           g_err_position:='E0038-02';        
           g_err_desc:='E0038-02';      
           SELECT replace(replace(exp_template, '#FORE@0#', 'REPLACE_STRING_2'), '#UNIT#', '1') || 'AS c_pred ' INTO v_replace_string_1
           FROM computed_fields WHERE forecast_type_id=1;
           
           g_err_position:='E0038-02';        
           g_err_desc:='E0038-02';      
           apps.xxascp_util_pkg.dbms_output_lines('v_replace_string_1=' || v_replace_string_1);
           SELECT 'branch_data.'||FORE_COLUMN_NAME INTO v_replace_string_2 FROM forecast_history WHERE TIME_SIG = (SELECT MAX(TIME_SIG)  FROM forecast_history);
           apps.xxascp_util_pkg.dbms_output_lines('v_replace_string_2=' || v_replace_string_2);
           v_replace_string_1:=REPLACE(v_replace_string_1,'REPLACE_STRING_2',v_replace_string_2);           
       -- END IF;
       
           g_err_position:='E0038-03';        
           g_err_desc:='E0038-03';

       IF p_ppp_new='LB1' THEN
           v_replace_string_3
             :=' AND branch_data.sales_date >= to_date('''||g_eol_date_c||''','''||g_eol_date_format||''')'||
               ' AND branch_data.sales_date <= add_months(to_date('''||g_eol_date_c||''','''||g_eol_date_format||''')'||',11)';
       ELSIF p_ppp_new='LB3' THEN
           v_replace_string_3
             :=' AND branch_data.sales_date >= add_months(to_date('''||g_eol_date_c||''','''||g_eol_date_format||''')'||',12)' ||
               ' AND branch_data.sales_date <= add_months(to_date('''||g_eol_date_c||''','''||g_eol_date_format||''')'||',35)';
       ELSE 
           v_replace_string_3
             :='';
       END IF;
       
       apps.xxascp_util_pkg.dbms_output_lines('v_replace_string_3=' || v_replace_string_3);
       -- apps.xxascp_util_pkg.dbms_output_lines('v_replace_string_1=' || v_replace_string_1);
       
       g_sql := 'CREATE OR REPLACE VIEW '
                || v_view_name
                ||' AS
                    SELECT sdate,
                           level1,
                           level2,
                           c_pred,
                           prty_demand,
                           dkey_item,
                           dkey_org,
                           record_type
                      FROM (SELECT MIN(t_dates_list.from_sales_date) AS sdate,
                                   (CASE
                                     WHEN (MIN(t_dates_list.from_sales_date) - to_date('''
                || TO_CHAR (p_max_salles_date, 'yyyy-MM-dd HH24:mi:ss')
                || ''',''yyyy-MM-dd HH24:mi:ss'') <= 0) THEN
                                      (CASE
                                     WHEN (MAX(t_dates_list.from_sales_date) - to_date('''
                || TO_CHAR (p_max_salles_date, 'yyyy-MM-dd HH24:mi:ss')
                || ''',''yyyy-MM-dd HH24:mi:ss'') <= 0) THEN
                                      1
                                     ELSE
                                      3
                                   END) ELSE 2 END) AS record_type,
                                   MAX(comb_id) AS comb_id,
                                   t_comb_list.level_id1,
                                   t_comb_list.level_id2,
                                   REPLACE_STRING_1,
                                   MIN(branch_data.demand_priority) AS prty_demand,
                                   MIN(t_ep_item.ebs_item_dest_key) AS dkey_item,
                                   MIN(t_ep_ro_org.org_dest_key) AS dkey_org,
                                   (CASE
                                     WHEN MAX(branch_data.note_id) > 0 THEN
                                      1
                                     ELSE
                                      0
                                   END) AS note_id,
                                   (CASE
                                     WHEN MAX(branch_data.is_promotion) > 0 THEN
                                      1
                                     ELSE
                                      0
                                   END) AS is_promotion,
                                   (CASE
                                     WHEN MAX(branch_data.is_supply_plan) > 0 THEN
                                      1
                                     ELSE
                                      0
                                   END) AS is_supply_plan,
                                   (CASE
                                     WHEN MAX(branch_data.is_scenario_resource) > 0 THEN
                                      1
                                     ELSE
                                      0
                                   END) AS is_scenario_resource,
                                   1 AS is_base_0
                              FROM sales_data branch_data,
                                   mdp_matrix,
                                   t_ep_item,
                                   t_ep_ro_org,
                                   '|| v_int_comb_viewname||' t_comb_list,
                                   '||v_int_date_tablename||' t_dates_list
                             WHERE branch_data.sales_date >= t_dates_list.from_sales_date
                             AND branch_data.sales_date <= t_dates_list.to_sales_date' ||
                               v_replace_string_3||
                               ' AND branch_data.item_id = mdp_matrix.item_id
                               AND branch_data.location_id = mdp_matrix.location_id
                               AND mdp_matrix.t_ep_item_ep_id = t_ep_item.t_ep_item_ep_id
                               AND mdp_matrix.t_ep_ro_org_ep_id = t_ep_ro_org.t_ep_ro_org_ep_id
                               AND '||p_additional_filter||'
                               AND t_comb_list.level_id1 = mdp_matrix.t_ep_item_ep_id
                               AND t_comb_list.level_id2 = mdp_matrix.t_ep_ro_org_ep_id
                             GROUP BY t_comb_list.level_id1,
                                      t_comb_list.level_id2,
                                      t_dates_list.from_sales_date) int_table,
                           '||v_int_comb_viewname||'  
                     WHERE '||v_int_comb_viewname||'.level_id1 = int_table.level_id1
                       AND '||v_int_comb_viewname||'.level_id2 = int_table.level_id2
                    ';
       
       
       g_sql:=REPLACE(g_sql,'REPLACE_STRING_1',v_replace_string_1);      
       apps.xxascp_util_pkg.dbms_output_lines('g_sql=' || g_sql);
       
       g_err_position:='E0039';   
       g_err_desc:='error occurs when creating export view';   
       EXECUTE IMMEDIATE g_sql;
       
    END;   
    
    
    /*---------------------------------------------------------
    Created by 
    FUNCTION :
               Check LTB-B exists
    LOGICALITY:
               <_____Optional_____Internal_Logic_Description>
    INPUT:
               p_additional_filter      additional_filter
    OUTPUT:
               True or False
    ---------------------------------------------------------*/
    FUNCTION xxdm_check_ltb_b_exist(p_additional_filter VARCHAR2)
      RETURN  BOOLEAN IS
      RESULT        BOOLEAN;
      v_count       NUMBER;
    BEGIN 
         g_sql :='SELECT COUNT (*) 
                 FROM mdp_matrix, t_ep_item, t_ep_ro_org, t_ep_i_att_6
                 WHERE mdp_matrix.t_ep_item_ep_id = t_ep_item.t_ep_item_ep_id
                   AND mdp_matrix.t_ep_ro_org_ep_id = t_ep_ro_org.t_ep_ro_org_ep_id
                   AND mdp_matrix.t_ep_i_att_6_ep_id = t_ep_i_att_6.t_ep_i_att_6_ep_id
                   AND t_ep_i_att_6.i_att_6 = ''EOL-B'' 
                   AND '|| p_additional_filter;
        apps.xxascp_util_pkg.dbms_output_lines('g_sql=' || g_sql);        
        EXECUTE IMMEDIATE g_sql INTO v_count;
        IF v_count>0 THEN
            RESULT := TRUE;
        ELSE
            RESULT := FALSE;
        END IF;
        
        RETURN RESULT;
    END;       
END;
/







评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值