CREATE OR REPLACE PACKAGE Migrate_11_To_11i_Util AS
--==============================================================================
-- NAME: migrate_11_to_11i_util
-- PURPOSE: Global utility functions useed in migration routines
-- NOTES: Requires that a DBLINK be setuup for the release 11 database.
--==============================================================================
-- HISTORY
-- Who         When       Comments
-- ---------   ---------- ---------------------------------------------
-- Chet West   06/04/2003 Initial Versioon
--==============================================================================
--
-- Public Declarations
--

   FUNCTION get_field ( p_line         IN VARCHAR2
                      , p_delimiter    IN VARCHAR2
                      , p_field_no     IN NUMBER
                      , p_encapsulator IN VARCHAR2 DEFAULT '"'
                      )
      RETURN VARCHAR2;

   FUNCTION  get_user_name (p_user_id IN applsys.fnd_user.user_id%TYPE
                           ,p_dblink IN VARCHAR2 DEFAULT '') 
      RETURN applsys.fnd_user.user_name%TYPE;
   PRAGMA RESTRICT_REFERENCES (get_user_name, WNDS, WNPS);
   
   FUNCTION  get_user_id (p_user_name IN applsys.fnd_user.user_name%TYPE
                         ,p_dblink IN VARCHAR2 DEFAULT '') 
      RETURN applsys.fnd_user.user_id%TYPE;
   PRAGMA RESTRICT_REFERENCES (get_user_id, WNDS, WNPS);
   
   FUNCTION  get_employee_name (p_employee_id IN apps.hr_employees.employee_id%TYPE
                                   ,p_dblink IN VARCHAR2 DEFAULT '') 
      RETURN apps.hr_employees.full_name%TYPE;
   PRAGMA RESTRICT_REFERENCES (get_employee_name, WNDS, WNPS);
   
   FUNCTION  get_employee_id (p_full_name IN apps.hr_employees.full_name%TYPE
                                 ,p_dblink IN VARCHAR2 DEFAULT '') 
      RETURN apps.hr_employees.employee_id%TYPE;
   PRAGMA RESTRICT_REFERENCES (get_employee_id, WNDS, WNPS);
   
   FUNCTION  get_location_code (p_location_id IN apps.hr_locations.location_id%TYPE
                                   ,p_dblink IN VARCHAR2 DEFAULT '') 
      RETURN apps.hr_locations.location_code%TYPE;
   PRAGMA RESTRICT_REFERENCES (get_location_code, WNDS, WNPS);
   
   FUNCTION  get_location_id (p_location_code IN apps.hr_locations.location_code%TYPE
                                 ,p_dblink IN VARCHAR2 DEFAULT '') 
      RETURN apps.hr_locations.location_id%TYPE;
   PRAGMA RESTRICT_REFERENCES (get_location_id, WNDS, WNPS);
   
   FUNCTION  get_term_name (p_term_id IN apps.ap_terms_v.term_id%TYPE
                                   ,p_dblink IN VARCHAR2 DEFAULT '') 
      RETURN apps.ap_terms_v.name%TYPE;
   PRAGMA RESTRICT_REFERENCES (get_term_name, WNDS, WNPS);
   
   FUNCTION  get_term_id (p_term_name IN apps.ap_terms_v.name%TYPE
                                   ,p_dblink IN VARCHAR2 DEFAULT '') 
      RETURN apps.ap_terms_v.term_id%TYPE;
   PRAGMA RESTRICT_REFERENCES (get_term_id, WNDS, WNPS);

   -- need to add one or more routines to translate GL_CODE_COMBINATIONS

   FUNCTION get_gmi_item_number (p_item_id gmi.ic_item_mst_b.item_id%TYPE
                            ,p_dblink IN VARCHAR2 DEFAULT '') 
      RETURN gmi.ic_item_mst_b.item_no%TYPE;
   PRAGMA RESTRICT_REFERENCES (get_gmi_item_number, WNDS, WNPS);
      
END Migrate_11_To_11i_Util;
/

CREATE OR REPLACE PACKAGE BODY Migrate_11_To_11i_Util AS
--==============================================================================
-- NAME: migrate_11_to_11i_util
--==============================================================================
--
-- Private Declarations
--
--
-- Code Bodies
--
--==============================================================================

   FUNCTION Get_Field ( p_line         IN VARCHAR2
                      , p_delimiter    IN VARCHAR2
                      , p_field_no     IN NUMBER  
                      , p_encapsulator IN VARCHAR2 DEFAULT '"'
                      )
      RETURN VARCHAR2 IS
--==============================================================================
--  NAME: Get_Field                                                              
--                                                                            
--  PURPOSE: Get value of field n from aa delimited line of ascii data              
--                                                                            
--  DESCRIPTION                                                               
--     This utility function will returnn the value of a field from           
--     a delimited line of ascii text                                         
--                                                                            
--  PARAMETERS                                                                
--     p_line             IN VARCHAR2          - line of data                 
--     p_delimiter        IN VARCHAR2          - Delimiter character          
--     p_field_no         IN NUMBER            - Field occurance to be        
--                                               returned                     
--     p_encapsulator     IN VARCHAR2          - Character used to encapsulate text
--                                               Defaults to double quotes "
--  EXAMPLE
--          SELECT GET_FIELD( '"ABC",1233,"DE,FG",456'
--                          , ','
--                          , 3
--                          , '"'
--                          )
--            FROM dual;
--
--  RETURNS                                                                   
--     VARCHAR2                                - Value of field               
--                                                                            
--  HISTORY                                                                   
--  Who         WHEN       Comments                                           
--  ---------   ---------- ---------------------------------------------      
--  Chet West   06/18/2003 Initial Versiion                                    
--==============================================================================
--  Local variables 

      l_current       NUMBER  :=0;
      l_count         NUMBER  :=1;
      l_start         NUMBER  :=0;
      l_end           NUMBER  :=0;
      l_skip          BOOLEAN :=FALSE;  
      
   BEGIN
   
      -- Determine start position
      IF p_field_no = 1 THEN
         l_start := 0;
      ELSE
         l_current := 1;
         LOOP
            IF SUBSTR( p_line, l_current, 1) = p_delimiter AND NOT l_skip THEN
               l_count := l_count + 1;
               IF p_field_no = l_count THEN
                  IF SUBSTR( p_line, l_current+1, 1) = p_encapsulator THEN
                     l_start := l_current + 1;
                  ELSE 
                     l_start := l_current;
                  END IF;
                  EXIT;
               END IF;
            ELSIF SUBSTR(p_line, l_current, 1) = p_encapsulator AND NOT l_skip THEN
               l_skip := TRUE;
            ELSIF SUBSTR(p_line, l_current, 1) = p_encapsulator AND l_skip THEN
               l_skip := FALSE;
            END IF;
            l_current := l_current + 1;
            IF l_current > LENGTH(p_line) THEN
               RAISE NO_DATA_FOUND;
            END IF;
         END LOOP; 
      END IF;

      IF SUBSTR( p_line, l_current+1, 1) = p_encapsulator THEN
         l_start := l_current + 1;
      END IF;
      
      l_current := l_current + 1;
      l_skip := FALSE;
      
      -- Determine end position 
      LOOP
         IF SUBSTR( p_line, l_current, 1) = p_delimiter AND NOT l_skip THEN
            EXIT;
         ELSIF SUBSTR(p_line, l_current, 1) = p_encapsulator AND NOT l_skip THEN
            l_skip := TRUE;
         ELSIF SUBSTR(p_line, l_current, 1) = p_encapsulator AND l_skip THEN
            l_skip := FALSE;
         END IF;
         IF l_current = LENGTH(p_line) THEN
            l_current := l_current + 1;
            EXIT;
         END IF;
         l_current := l_current + 1;
      END LOOP;
      
      IF SUBSTR( p_line, l_current-1, 1) = p_encapsulator THEN
         l_end := l_current - 1;
      ELSE
         l_end := l_current;
      END IF;
            
      -- Extract the field data
      IF (l_end - l_start) = 1
      THEN
        RETURN NULL;
      ELSE
        RETURN SUBSTR(p_line,(l_start + 1),((l_end - l_start) - 1));
      END IF;
      
   EXCEPTION
     WHEN OTHERS THEN
       RETURN NULL;

   END Get_Field;
---__--__--__--__--__--__--__--__--__--___--__--__--__--__--__--__--__--__--__--
---__--__--__--__--__--__--__--__--__--___--__--__--__--__--__--__--__--__--__--
---__--__--__--__--__--__--__--__--__--___--__--__--__--__--__--__--__--__--__--

   FUNCTION  get_user_name (p_user_id IN applsys.fnd_user.user_id%TYPE
                           ,p_dblink IN VARCHAR2 DEFAULT '') 
      RETURN applsys.fnd_user.user_name%TYPE IS
--==============================================================================
-- NAME: get_user_name
-- PURPOSE: To return the user_name for  a specified user_id.
-- NOTES:
--==============================================================================
-- HISTORY
-- Who         When       Comments
-- ---------   ---------- ---------------------------------------------
-- Chet West   06/04/2003 Initial Versioon
--==============================================================================
      v_user_name applsys.fnd_user.user_name%TYPE := NULL;
      v_dblink  VARCHAR2(30) := '';
   BEGIN
      IF p_user_id IS NOT NULL THEN
         IF  p_dblink IS NOT NULL THEN
            v_dblink := LTRIM(p_dblink);
            IF SUBSTR(v_dblink,1,1) != '@' THEN
               v_dblink := '@'||v_dblink;
            END IF;
         END IF;
         EXECUTE IMMEDIATE 
            'SELECT user_name' 
            ||' FROM applsys.fnd_user'
            ||v_dblink
            ||' WHERE user_id = :1'
         INTO v_user_name
         USING p_user_id;
         END IF;
      RETURN v_user_name;

   END get_user_name;   
---__--__--__--__--__--__--__--__--__--___--__--__--__--__--__--__--__--__--__--
---__--__--__--__--__--__--__--__--__--___--__--__--__--__--__--__--__--__--__--
---__--__--__--__--__--__--__--__--__--___--__--__--__--__--__--__--__--__--__--

   FUNCTION  get_user_id (p_user_name IN applsys.fnd_user.user_name%TYPE
                         ,p_dblink IN VARCHAR2 DEFAULT '') 
      RETURN applsys.fnd_user.user_id%TYPE IS
--==============================================================================
-- NAME: get_user_id
-- PURPOSE: To return the user_id for a  specified user_name.
-- NOTES:
--==============================================================================
-- HISTORY
-- Who         When       Comments
-- ---------   ---------- ---------------------------------------------
-- Chet West   06/04/2003 Initial Versioon
--==============================================================================
      v_user_id applsys.fnd_user.user_id%TYPE := NULL;
      v_dblink  VARCHAR2(30) := '';
   BEGIN
      IF p_user_name IS NOT NULL THEN
         IF  p_dblink IS NOT NULL THEN
            v_dblink := LTRIM(p_dblink);
            IF SUBSTR(v_dblink,1,1) != '@' THEN
               v_dblink := '@'||v_dblink;
            END IF;
         END IF;
         EXECUTE IMMEDIATE 
            'SELECT user_id' 
            ||' FROM applsys.fnd_user'
            ||v_dblink
            ||' WHERE user_name = :1'
         INTO v_user_id
         USING p_user_name;
      END IF;
      RETURN v_user_id;

   END get_user_id;   
---__--__--__--__--__--__--__--__--__--___--__--__--__--__--__--__--__--__--__--
---__--__--__--__--__--__--__--__--__--___--__--__--__--__--__--__--__--__--__--
---__--__--__--__--__--__--__--__--__--___--__--__--__--__--__--__--__--__--__--


   FUNCTION  get_employee_name (p_employee_id IN apps.hr_employees.employee_id%TYPE
                               ,p_dblink IN VARCHAR2 DEFAULT '') 
      RETURN apps.hr_employees.full_name%TYPE IS
--==============================================================================
-- NAME: get_employee_name
-- PURPOSE: To return the full_name for  a specified employee_id.
-- NOTES:
--==============================================================================
-- HISTORY
-- Who         When       Comments
-- ---------   ---------- ---------------------------------------------
-- Chet West   06/04/2003 Initial Versioon
--==============================================================================
      v_full_name apps.hr_employees.full_name%TYPE :=  NULL;
      v_dblink  VARCHAR2(30) := '';
   BEGIN
      IF p_employee_id IS NOT NULL THEN
         IF  p_dblink IS NOT NULL THEN
            v_dblink := LTRIM(p_dblink);
            IF SUBSTR(v_dblink,1,1) != '@' THEN
               v_dblink := '@'||v_dblink;
            END IF;
         END IF;
         EXECUTE IMMEDIATE 
            'SELECT full_name' 
            ||' FROM apps.hr_employees'
            ||v_dblink
            ||' WHERE employee_id = :1'
         INTO v_full_name
         USING p_employee_id;
      END IF;
      RETURN v_full_name;

   END get_employee_name;   
---__--__--__--__--__--__--__--__--__--___--__--__--__--__--__--__--__--__--__--
---__--__--__--__--__--__--__--__--__--___--__--__--__--__--__--__--__--__--__--
---__--__--__--__--__--__--__--__--__--___--__--__--__--__--__--__--__--__--__--

   
   FUNCTION  get_employee_id (p_full_name IN apps.hr_employees.full_name%TYPE
                             ,p_dblink IN VARCHAR2 DEFAULT '') 
      RETURN apps.hr_employees.employee_id%TYPE IS
--==============================================================================
-- NAME: get_employee_id
-- PURPOSE: To return the employee_id foor a specified full_name.
-- NOTES:
--==============================================================================
-- HISTORY
-- Who         When       Comments
-- ---------   ---------- ---------------------------------------------
-- Chet West   06/04/2003 Initial Versioon
--==============================================================================
      v_employee_id apps.hr_employees.employee_id%TYPE := NULL;
      v_dblink  VARCHAR2(30) := '';
   BEGIN
      IF p_full_name IS NOT NULL THEN
         IF  p_dblink IS NOT NULL THEN
            v_dblink := LTRIM(p_dblink);
            IF SUBSTR(v_dblink,1,1) != '@' THEN
               v_dblink := '@'||v_dblink;
            END IF;
         END IF;
         EXECUTE IMMEDIATE 
            'SELECT employee_id' 
            ||' FROM apps.hr_employees'
            ||v_dblink
            ||' WHERE full_name = :1'
         INTO v_employee_id
         USING p_full_name;
      END IF;
      RETURN v_employee_id;

   END get_employee_id;   
---__--__--__--__--__--__--__--__--__--___--__--__--__--__--__--__--__--__--__--
---__--__--__--__--__--__--__--__--__--___--__--__--__--__--__--__--__--__--__--
---__--__--__--__--__--__--__--__--__--___--__--__--__--__--__--__--__--__--__--
   
   FUNCTION  get_location_code (p_location_id IN apps.hr_locations.location_id%TYPE
                                   ,p_dblink IN VARCHAR2 DEFAULT '') 
      RETURN apps.hr_locations.location_code%TYPE IS
--==============================================================================
-- NAME: get_location_code
-- PURPOSE: To return the location_code  for a specified location_id.
-- NOTES:
--==============================================================================
-- HISTORY
-- Who         When       Comments
-- ---------   ---------- ---------------------------------------------
-- Chet West   06/05/2003 Initial Versioon
--==============================================================================
      v_location_code apps.hr_locations.location_code%TYPE := NULL;
      v_dblink  VARCHAR2(30) := '';
   BEGIN
      IF p_location_id IS NOT NULL THEN
         IF  p_dblink IS NOT NULL THEN
            v_dblink := LTRIM(p_dblink);
            IF SUBSTR(v_dblink,1,1) != '@' THEN
               v_dblink := '@'||v_dblink;
            END IF;
         END IF;
         EXECUTE IMMEDIATE 
            'SELECT location_code' 
            ||' FROM apps.hr_locations'
            ||v_dblink
            ||' WHERE location_id = :1'
         INTO v_location_code
         USING p_location_id;
      END IF;
      RETURN v_location_code;

   END get_location_code;   
---__--__--__--__--__--__--__--__--__--___--__--__--__--__--__--__--__--__--__--
---__--__--__--__--__--__--__--__--__--___--__--__--__--__--__--__--__--__--__--
---__--__--__--__--__--__--__--__--__--___--__--__--__--__--__--__--__--__--__--
   

   FUNCTION  get_location_id (p_location_code IN apps.hr_locations.location_code%TYPE
                                 ,p_dblink IN VARCHAR2 DEFAULT '') 
      RETURN apps.hr_locations.location_id%TYPE IS
--==============================================================================
-- NAME: get_location_id
-- PURPOSE: To return the location_id foor a specified location_code.
-- NOTES:
--==============================================================================
-- HISTORY
-- Who         When       Comments
-- ---------   ---------- ---------------------------------------------
-- Chet West   06/05/2003 Initial Versioon
--==============================================================================
      v_location_id apps.hr_locations.location_id%TYPE := NULL;
      v_dblink  VARCHAR2(30) := '';
   BEGIN
      IF p_location_code IS NOT NULL THEN
         IF  p_dblink IS NOT NULL THEN
            v_dblink := LTRIM(p_dblink);
            IF SUBSTR(v_dblink,1,1) != '@' THEN
               v_dblink := '@'||v_dblink;
            END IF;
         END IF;
         EXECUTE IMMEDIATE 
            'SELECT location_id' 
            ||' FROM apps.hr_locations'
            ||v_dblink
            ||' WHERE location_code = :1'
         INTO v_location_id
         USING p_location_code;
      END IF;
      RETURN v_location_id;

   END get_location_id;
---__--__--__--__--__--__--__--__--__--___--__--__--__--__--__--__--__--__--__--
---__--__--__--__--__--__--__--__--__--___--__--__--__--__--__--__--__--__--__--
---__--__--__--__--__--__--__--__--__--___--__--__--__--__--__--__--__--__--__--

   FUNCTION  get_term_name (p_term_id IN apps.ap_terms_v.term_id%TYPE
                                   ,p_dblink IN VARCHAR2 DEFAULT '') 
      RETURN apps.ap_terms_v.name%TYPE IS
--==============================================================================
-- NAME: get_term_name
-- PURPOSE: To return the term name for  a specified term_id.
-- NOTES:
--==============================================================================
-- HISTORY
-- Who         When       Comments
-- ---------   ---------- ---------------------------------------------
-- Chet West   06/05/2003 Initial Versioon
--==============================================================================
      v_term_name apps.ap_terms_v.name%TYPE := NULL;
      v_dblink  VARCHAR2(30) := '';
   BEGIN
      IF p_term_id IS NOT NULL THEN
         IF  p_dblink IS NOT NULL THEN
            v_dblink := LTRIM(p_dblink);
            IF SUBSTR(v_dblink,1,1) != '@' THEN
               v_dblink := '@'||v_dblink;
            END IF;
         END IF;
         EXECUTE IMMEDIATE 
            'SELECT name' 
            ||' FROM apps.ap_terms_v'
            ||v_dblink
            ||' WHERE term_id = :1'
         INTO v_term_name
         USING p_term_id;
      END IF;
      RETURN v_term_name;

   END get_term_name;
---__--__--__--__--__--__--__--__--__--___--__--__--__--__--__--__--__--__--__--
---__--__--__--__--__--__--__--__--__--___--__--__--__--__--__--__--__--__--__--
---__--__--__--__--__--__--__--__--__--___--__--__--__--__--__--__--__--__--__--

   
   FUNCTION  get_term_id (p_term_name IN apps.ap_terms_v.name%TYPE
                                   ,p_dblink IN VARCHAR2 DEFAULT '') 
      RETURN apps.ap_terms_v.term_id%TYPE IS
--==============================================================================
-- NAME: get_term_id
-- PURPOSE: To return the term_id for a  specified term name.
-- NOTES:
--==============================================================================
-- HISTORY
-- Who         When       Comments
-- ---------   ---------- ---------------------------------------------
-- Chet West   06/05/2003 Initial Versioon
--==============================================================================
      v_term_id apps.ap_terms_v.term_id%TYPE := NULL;
      v_dblink  VARCHAR2(30) := '';
   BEGIN
      IF p_term_name IS NOT NULL THEN
         IF  p_dblink IS NOT NULL THEN
            v_dblink := LTRIM(p_dblink);
            IF SUBSTR(v_dblink,1,1) != '@' THEN
               v_dblink := '@'||v_dblink;
            END IF;
         END IF;
         EXECUTE IMMEDIATE 
            'SELECT term_id' 
            ||' FROM apps.ap_terms_v'
            ||v_dblink
            ||' WHERE name = :1'
         INTO v_term_id
         USING p_term_name;
      END IF;
      RETURN v_term_id;

   END get_term_id;
---__--__--__--__--__--__--__--__--__--___--__--__--__--__--__--__--__--__--__--
---__--__--__--__--__--__--__--__--__--___--__--__--__--__--__--__--__--__--__--
---__--__--__--__--__--__--__--__--__--___--__--__--__--__--__--__--__--__--__--

   FUNCTION get_gmi_item_number (p_item_id gmi.ic_item_mst_b.item_id%TYPE
                            ,p_dblink IN VARCHAR2 DEFAULT '') 
      RETURN gmi.ic_item_mst_b.item_no%TYPE IS
--==============================================================================
-- NAME: get_gmi_item_number
-- PURPOSE: To return the item_no for a  specified item_id.
-- NOTES:
--==============================================================================
-- HISTORY
-- Who         When       Comments
-- ---------   ---------- ---------------------------------------------
-- Chet West   06/11/2003 Initial Versioon
--==============================================================================
       v_retval gmi.ic_item_mst_b.item_no%TYPE;
       v_dblink  VARCHAR2(30) := '';
   BEGIN
      IF p_item_id IS NOT NULL
      AND p_item_id != 0 THEN
         IF  p_dblink IS NOT NULL THEN
            v_dblink := LTRIM(p_dblink);
            IF SUBSTR(v_dblink,1,1) != '@' THEN
               v_dblink := '@'||v_dblink;
            END IF;
         END IF;
         EXECUTE IMMEDIATE 
            'SELECT item_no' 
            ||' FROM ic_item_mst'
            ||v_dblink
            ||' WHERE item_id = :1'
         INTO v_retval
         USING p_item_id;
      ELSE
         v_retval := NULL;
      END IF;

      RETURN v_retval;
   EXCEPTION
      WHEN OTHERS THEN
         RETURN NULL;
   END get_gmi_item_number;
---__--__--__--__--__--__--__--__--__--___--__--__--__--__--__--__--__--__--__--
---__--__--__--__--__--__--__--__--__--___--__--__--__--__--__--__--__--__--__--
---__--__--__--__--__--__--__--__--__--___--__--__--__--__--__--__--__--__--__--
--
-- Package Initialization
BEGIN
   NULL;
END Migrate_11_To_11i_Util;
/