APPS.PON_FIELDS_PVT SQL Statements

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

Line: 9

   PROCEDURE : INSERT_FIELD
   PARAMETERS: 1. p_code- The field Code
               2. p_name - The name for the field
               3. p_description - The description for the field
               4. p_result - The result from this procedure
                              0 -> success 1 -> failure
               5. p_err_code - The error code if any
               6. p_err_msg - The error message if any
   COMMENT   : When a new field is created by the user, the name and
               description for the field are stored in the pon_fields_tl
               table. The pon_fields_tl will have 'n' new rows
               corresponding to this new field, here 'n' is the total number
               of installed and base languages. The name and description
               will remain the same for all the language rows when the
               field is first created.
======================================================================*/
PROCEDURE  insert_field(p_code          IN        VARCHAR2,
                        p_name          IN        VARCHAR2,
                        p_description   IN        VARCHAR2,
                        p_result        OUT        NOCOPY        NUMBER,
                        p_err_code      OUT        NOCOPY        VARCHAR2,
                        p_err_msg       OUT        NOCOPY        VARCHAR2) IS

l_api_name                CONSTANT VARCHAR2(30) := 'INSERT_FIELD';

Line: 41

      insert into pon_fields_tl (FIELD_CODE,
                                FIELD_NAME,
                                DESCRIPTION,
                                LANGUAGE,
                                SOURCE_LANG,
                                CREATION_DATE,
                                CREATED_BY,
                                LAST_UPDATE_DATE,
                                LAST_UPDATED_BY,
                                LAST_UPDATE_LOGIN)
                            select  p_code,
                                    p_name,
                                    p_description,
                                    a.language_code,
                                    USERENV ('LANG'),
                                    sysdate,
                                    fnd_global.user_id,
                                    sysdate,
                                    fnd_global.user_id,
                                    fnd_global.login_id
                            from         fnd_languages a
                            where         a.installed_flag in ('I', 'B');

Line: 68

        PON_FORMS_UTIL_PVT.print_error_log (l_api_name, 'Exception in inserting rows into pon_fields_tl');

Line: 73

        RAISE_APPLICATION_ERROR(-20101, 'Exception at PON_FIELDS_PVT.insert_field: ' || p_err_code || ' : ' || p_err_msg);

Line: 78

   PROCEDURE : DELETE_FIELD
   PARAMETERS: 1. p_code: the field code of the field to be deleted.
               2. p_result: 0-> indicates success 1->failure
               3. p_err_code: The error code if any
               4. p_err_msg: The error message if any
   COMMENT   : Given a field code this procedure will delete
               all the entries in the PON_FIELDS_TL table
               corresponding to this field.
======================================================================*/
PROCEDURE delete_field (p_code     IN VARCHAR2,
                        p_result   OUT NOCOPY NUMBER,
                        p_err_code OUT NOCOPY VARCHAR2,
                        p_err_msg  OUT NOCOPY VARCHAR2) IS

l_api_name                CONSTANT VARCHAR2(30) := 'DELETE_FIELD';

Line: 99

      delete from pon_fields_tl where field_code=p_code;

Line: 111

        RAISE_APPLICATION_ERROR(-20102, 'Exception at PON_FIELDS_PVT.delete_field:' || p_err_code || ' : ' || p_err_msg);

Line: 116

   PROCEDURE : UPDATE_FIELD
   PARAMETERS: 1. p_code: The new field Code
               2. p_name: The name of the field
               3. p_description: The description of the field
               4. p_lastUpdate: the lastUpdate date of the field in the
                           pon_fields table
               5. p_old_code: The old field code.
               6. p_result: 0->success 1-> failure
               7. p_err_code: The error code if any
               8. p_err_msg: The error message if any
   COMMENT   : This procedure will update the field_code, name and
               description of all the row that have field_code as
               p_old_code
======================================================================*/
PROCEDURE  update_field(p_code           IN        VARCHAR2,
                        p_name           IN        VARCHAR2,
                        p_description    IN        VARCHAR2,
                        p_lastUpdate     IN        DATE,
                        p_old_code       IN        VARCHAR2,
                        p_result         OUT       NOCOPY        NUMBER,
                        p_err_code       OUT       NOCOPY        VARCHAR2,
                        p_err_msg        OUT       NOCOPY        VARCHAR2) IS

x_updated               varchar2(1);

Line: 140

l_api_name                CONSTANT VARCHAR2(30) := 'UPDATE_FIELD';

Line: 150

      update pon_fields_tl
      set
            field_code = p_code
      where
            field_code = p_old_code;

Line: 156

      update pon_fields_tl
      set
            field_name        = p_name,
            description       = p_description,
            source_lang       = USERENV ('LANG'),
            last_updated_by   = fnd_global.user_id,
            last_update_date  = sysdate,
            last_update_login = fnd_global.login_id
      where
            field_code        = p_code and
            language          = USERENV ('LANG');

Line: 178

        RAISE_APPLICATION_ERROR(-20103, 'Exception at PON_FIELDS_PVT.update_field: ' || p_err_code || ' : ' || p_err_msg);

Line: 179

END UPDATE_FIELD; --}

Line: 190

    INSERT INTO PON_FIELDS_TL (
      FIELD_CODE,
      FIELD_NAME,
      DESCRIPTION,
      LANGUAGE,
      SOURCE_LANG,
      CREATED_BY,
      CREATION_DATE,
      LAST_UPDATED_BY,
      LAST_UPDATE_DATE,
      LAST_UPDATE_LOGIN
    )
    SELECT
      pf.FIELD_CODE,
      pf.FIELD_NAME,
      pf.DESCRIPTION,
      lang.language_code,
      pf.SOURCE_LANG,
      pf.CREATED_BY,
      sysdate,
      pf.LAST_UPDATED_BY,
      sysdate,
      pf.LAST_UPDATE_LOGIN
    FROM PON_FIELDS_TL pf, FND_LANGUAGES lang
    WHERE pf.language = USERENV('LANG')
    AND lang.INSTALLED_FLAG in ('I','B')
    AND NOT EXISTS (SELECT 'x' FROM PON_FIELDS_TL pf2
                    WHERE pf2.FIELD_CODE = pf.FIELD_CODE
                    AND   pf2.language = lang.language_code);