The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* 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;
/* 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;
/* 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;
/* 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 */';
END insert_gbl_var_for_debug;
/* 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';
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;
insert_debug('BEGIN', NULL);
SELECT 'Y' into l_exists
FROM dual
WHERE exists
( SELECT api_owner_id
FROM zx_api_owner_statuses
WHERE
status_code in ('DELETED','NEW')
);
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;
print_debug('-- CG: Inserting a record in zx_api_owner_statuses for API owners');
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);
/* IF all the statuses for the provider are DELETED then drop the */
/* package */
/*------------------------------------------------------------------------*/
IF (t_prv.status_code(l_prvidx) = 'DELETED') THEN
ad_ddl.do_ddl(
'APPS','ZX','AD_DDL.DROP_TABLE','DROP PACKAGE '||l_pack_name,l_pack_name );
print_debug('-- CG: Before insert_gbl_val_for_debug');
insert_gbl_var_for_debug(l_pack_name);
insert_debug('BEGIN', NULL);
UPDATE ZX_API_OWNER_STATUSES SET STATUS_CODE = 'GENERATED'
WHERE api_owner_id = t_prv.api_owner_id(l_prvidx);
insert_gbl_var_for_debug(l_pack_name);
insert_debug('BEGIN', NULL);