APPS.ZX_TPI_PLUGIN_PKG SQL Statements

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

Line: 22

/*  Cursors to select api owners for that service category                    */
/* -------------------------------------------------------------------------- */

    Cursor c_apiowner(p_srvc_cat IN VARCHAR2,p_api_owner_id IN NUMBER) is
      SELECT distinct api_owner_id
           , status_code
      FROM zx_api_owner_statuses
      WHERE upper(service_category_code) = upper(p_srvc_cat)
      AND api_owner_id = nvl(p_api_owner_id, api_owner_id)
      ORDER BY api_owner_id asc;

Line: 34

/*  Cursors to select api owners for invoke                                   */
/* -------------------------------------------------------------------------- */

    Cursor c_gen_apiowner(p_srvc_cat IN VARCHAR2) is
      SELECT distinct api_owner_id
           , status_code
      FROM zx_api_owner_statuses
      WHERE upper(service_category_code) = upper(p_srvc_cat)
      AND status_code = 'GENERATED'
      ORDER BY api_owner_id asc;

Line: 46

/*  Cursor to select distinct service types for that service category         */
/* -------------------------------------------------------------------------- */

    Cursor c_srvctyp(p_srvc_cat IN VARCHAR2,p_api_owner_id IN NUMBER) is
      SELECT distinct a.api_owner_id
           , b.service_type_id
           , b.service_type_code
           , b.data_transfer_code
      FROM  zx_api_registrations a
          , zx_service_types b
          , zx_api_owner_statuses c
      WHERE a.service_type_id = b.service_type_id
      AND   a.api_owner_id = c.api_owner_id
      AND   upper(c.service_category_code) = upper(p_srvc_cat)
      AND   a.api_owner_id = nvl(p_api_owner_id,a.api_owner_id)
      ORDER BY a.api_owner_id asc
             , b.data_transfer_code desc
             , b.service_type_id asc;

Line: 66

/*  Cursor to select distinct context ids for that service category           */
/* -------------------------------------------------------------------------- */

    Cursor c_api(p_srvc_cat IN VARCHAR2,p_api_owner_id IN NUMBER) is
      SELECT distinct a.api_owner_id
           , a.service_type_id
           , a.context_ccid
           , a.package_name
           , a.procedure_name
           , b.service_type_code
      FROM   zx_api_registrations a
           , zx_service_types b
           , zx_api_owner_statuses c
      WHERE  a.service_type_id = b.service_type_id
      and    a.api_owner_id = c.api_owner_id
      and    upper(c.service_category_code) = upper(p_srvc_cat)
      and    a.api_owner_id = nvl(p_api_owner_id, a.api_owner_id)
      ORDER BY a.api_owner_id asc
             , b.service_type_code asc
             , a.context_ccid asc;

Line: 89

/*     Cursor to select oracle_user_name , bug#13077326                       */
/* -------------------------------------------------------------------------- */
     Cursor c_oracle_username IS
        SELECT oracle_username
        FROM fnd_oracle_userid
        WHERE read_only_flag = 'U'
        AND ROWNUM=1;

Line: 133

/*      Procedure to insert global variables for debug                        */
/*      Bug # 4769082                                                         */
/* -------------------------------------------------------------------------- */

     PROCEDURE insert_gbl_var_for_debug(p_string IN VARCHAR2) IS
       BEGIN
          l_string := '/* Global Data Types */';

Line: 171

       END insert_gbl_var_for_debug;

Line: 174

/*      Procedure to insert debug statement                                   */
/*      Bug # 4769082                                                         */
/* -------------------------------------------------------------------------- */

     PROCEDURE insert_debug(  p_stmt_type IN VARCHAR2
                            , p_str IN VARCHAR2) IS
       BEGIN
          l_string := 'IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN';

Line: 219

        SELECT service_type_id,
               parameter_name,
               position,
               param_usage_code,
               map_global_var_code,
               map_gbl_var_data_type
        FROM   zx_srvc_typ_params
        WHERE   service_type_id = p_srvc_typ_id;

Line: 367

        insert_debug('BEGIN', NULL);

Line: 556

    SELECT 'Y' into l_exists
    FROM dual
    WHERE exists
    ( SELECT api_owner_id
      FROM zx_api_owner_statuses
      WHERE
      status_code in ('DELETED','NEW')
    );

Line: 580

         SELECT 'Y'
           INTO l_exists_in_owner_statuses
           FROM zx_api_owner_statuses
          WHERE upper(service_category_code) = upper(p_srvc_category)
          AND   api_owner_id = p_api_owner_id;

Line: 591

         print_debug('--  CG: Inserting a record in zx_api_owner_statuses for API owners');

Line: 592

         INSERT INTO zx_api_owner_statuses(api_owner_id
                                        , service_category_code
                                        , status_code
                                        , creation_date
                                        , created_by
                                        , last_update_date
                                        , last_updated_by
                                        , last_update_login)
                                   values(p_api_owner_id
                                        , p_srvc_category
                                        , 'NEW'
                                        , sysdate
                                        , fnd_global.user_id
                                        , sysdate
                                        , fnd_global.user_id
                                        , fnd_global.user_id);

Line: 701

/*   IF all the statuses for the provider are DELETED then drop the       */
/*   package                                                              */
/*------------------------------------------------------------------------*/

      IF (t_prv.status_code(l_prvidx) = 'DELETED') THEN
      Open C_Oracle_Username;

Line: 733

     print_debug('--  CG: Before insert_gbl_val_for_debug');

Line: 734

        insert_gbl_var_for_debug(l_pack_name);

Line: 779

            insert_debug('BEGIN', NULL);

Line: 863

              UPDATE ZX_API_OWNER_STATUSES SET STATUS_CODE = 'GENERATED'
              WHERE api_owner_id = t_prv.api_owner_id(l_prvidx);

Line: 910

          insert_gbl_var_for_debug(l_pack_name);

Line: 932

          insert_debug('BEGIN', NULL);