1: package body AMS_CPAG_QUERY_USAGES_PKG as
2: /* $Header: amslcqub.pls 115.0 2002/06/13 19:02:15 gdeodhar noship $ */
3: procedure INSERT_ROW (
4: X_ROWID in out VARCHAR2,
5: X_QUERY_USAGE_ID in NUMBER,
12: X_LAST_UPDATED_BY in NUMBER,
13: X_LAST_UPDATE_LOGIN in NUMBER,
14: X_OBJECT_VERSION_NUMBER in NUMBER
15: ) is
16: cursor C is select ROWID from AMS_CPAG_QUERY_USAGES
17: where QUERY_USAGE_ID = X_QUERY_USAGE_ID
18: ;
19: begin
20: insert into AMS_CPAG_QUERY_USAGES (
16: cursor C is select ROWID from AMS_CPAG_QUERY_USAGES
17: where QUERY_USAGE_ID = X_QUERY_USAGE_ID
18: ;
19: begin
20: insert into AMS_CPAG_QUERY_USAGES (
21: QUERY_USAGE_ID,
22: QUERY_USED_BY_TYPE,
23: QUERY_USED_BY_ID,
24: QUERY_ID,
62: OBJECT_VERSION_NUMBER
63: ,QUERY_USED_BY_TYPE
64: ,QUERY_USED_BY_ID
65: ,QUERY_ID
66: from AMS_CPAG_QUERY_USAGES
67: where QUERY_USAGE_ID = X_QUERY_USAGE_ID
68: for update of QUERY_USAGE_ID nowait;
69: recinfo c%rowtype;
70:
105: X_LAST_UPDATED_BY in NUMBER,
106: X_LAST_UPDATE_LOGIN in NUMBER
107: ) is
108: begin
109: update AMS_CPAG_QUERY_USAGES set
110: OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
111: QUERY_USED_BY_TYPE = X_QUERY_USED_BY_TYPE,
112: QUERY_USED_BY_ID = X_QUERY_USED_BY_ID,
113: QUERY_ID = X_QUERY_ID,
124: procedure DELETE_ROW (
125: X_QUERY_USAGE_ID in NUMBER
126: ) is
127: begin
128: delete from AMS_CPAG_QUERY_USAGES
129: where QUERY_USAGE_ID = X_QUERY_USAGE_ID;
130:
131: if (sql%notfound) then
132: raise no_data_found;
148: l_row_id varchar2(100);
149:
150: cursor c_obj_verno is
151: select object_version_number
152: from AMS_CPAG_QUERY_USAGES
153: where query_usage_id = X_QUERY_USAGE_ID;
154:
155: cursor c_chk_qry_exists is
156: select 'x'
153: where query_usage_id = X_QUERY_USAGE_ID;
154:
155: cursor c_chk_qry_exists is
156: select 'x'
157: from AMS_CPAG_QUERY_USAGES
158: where QUERY_USAGE_ID = X_QUERY_USAGE_ID;
159:
160: BEGIN
161:
170: close c_chk_qry_exists;
171:
172: l_obj_verno := 1;
173:
174: AMS_CPAG_QUERY_USAGES_PKG.INSERT_ROW(
175: X_ROWID => l_row_id,
176: X_QUERY_USAGE_ID => X_QUERY_USAGE_ID,
177: X_QUERY_USED_BY_TYPE => X_QUERY_USED_BY_TYPE,
178: X_QUERY_USED_BY_ID => X_QUERY_USED_BY_ID,
188: close c_chk_qry_exists;
189: open c_obj_verno;
190: fetch c_obj_verno into l_obj_verno;
191: close c_obj_verno;
192: AMS_CPAG_QUERY_USAGES_PKG.UPDATE_ROW(
193: X_QUERY_USAGE_ID => X_QUERY_USAGE_ID,
194: X_QUERY_USED_BY_TYPE => X_QUERY_USED_BY_TYPE,
195: X_QUERY_USED_BY_ID => X_QUERY_USED_BY_ID,
196: X_QUERY_ID => X_QUERY_ID,
202: end if;
203:
204: END LOAD_ROW;
205:
206: end AMS_CPAG_QUERY_USAGES_PKG;