[Home] [Help]
PACKAGE BODY: APPS.AMS_CPAG_QUERY_USAGES_PKG
Source
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,
6 X_QUERY_USED_BY_TYPE in VARCHAR2,
7 X_QUERY_USED_BY_ID in NUMBER,
8 X_QUERY_ID in NUMBER,
9 X_CREATION_DATE in DATE,
10 X_CREATED_BY in NUMBER,
11 X_LAST_UPDATE_DATE in DATE,
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 (
21 QUERY_USAGE_ID,
22 QUERY_USED_BY_TYPE,
23 QUERY_USED_BY_ID,
24 QUERY_ID,
25 CREATION_DATE,
26 CREATED_BY,
27 LAST_UPDATE_DATE,
28 LAST_UPDATED_BY,
29 LAST_UPDATE_LOGIN,
30 OBJECT_VERSION_NUMBER
31 ) values (
32 X_QUERY_USAGE_ID,
33 X_QUERY_USED_BY_TYPE,
34 X_QUERY_USED_BY_ID,
35 X_QUERY_ID,
36 X_CREATION_DATE,
37 X_CREATED_BY,
38 X_LAST_UPDATE_DATE,
39 X_LAST_UPDATED_BY,
40 X_LAST_UPDATE_LOGIN,
41 X_OBJECT_VERSION_NUMBER
42 );
43
44 open c;
45 fetch c into X_ROWID;
46 if (c%notfound) then
47 close c;
48 raise no_data_found;
49 end if;
50 close c;
51
52 end INSERT_ROW;
53
54 procedure LOCK_ROW (
55 X_QUERY_USAGE_ID in NUMBER,
56 X_OBJECT_VERSION_NUMBER in NUMBER,
57 X_QUERY_USED_BY_TYPE in VARCHAR2,
58 X_QUERY_USED_BY_ID in NUMBER,
59 X_QUERY_ID in NUMBER
60 ) is
61 cursor c is select
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
71 begin
72 open c;
73 fetch c into recinfo;
74 if (c%notfound) then
75 close c;
76 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
77 app_exception.raise_exception;
78 end if;
79 close c;
80 if ( ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
81 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
82 AND ((recinfo.QUERY_USED_BY_TYPE = X_QUERY_USED_BY_TYPE)
83 OR ((recinfo.QUERY_USED_BY_TYPE is null) AND (X_QUERY_USED_BY_TYPE is null)))
84 AND ((recinfo.QUERY_USED_BY_ID = X_QUERY_USED_BY_ID)
85 OR ((recinfo.QUERY_USED_BY_ID is null) AND (X_QUERY_USED_BY_ID is null)))
86 AND ((recinfo.QUERY_ID = X_QUERY_ID)
87 OR ((recinfo.QUERY_ID is null) AND (X_QUERY_ID is null)))
88 ) then
89 null;
90 else
91 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
92 app_exception.raise_exception;
93 end if;
94
95 return;
96 end LOCK_ROW;
97
98 procedure UPDATE_ROW (
99 X_QUERY_USAGE_ID in NUMBER,
100 X_OBJECT_VERSION_NUMBER in NUMBER,
101 X_QUERY_USED_BY_TYPE in VARCHAR2,
102 X_QUERY_USED_BY_ID in NUMBER,
103 X_QUERY_ID in NUMBER,
104 X_LAST_UPDATE_DATE in DATE,
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,
114 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
115 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
116 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
117 where QUERY_USAGE_ID = X_QUERY_USAGE_ID;
118
119 if (sql%notfound) then
120 raise no_data_found;
121 end if;
122 end UPDATE_ROW;
123
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;
133 end if;
134
135 end DELETE_ROW;
136
137 procedure LOAD_ROW(
138 X_QUERY_USAGE_ID in NUMBER,
139 X_QUERY_USED_BY_TYPE in VARCHAR2,
140 X_QUERY_USED_BY_ID in NUMBER,
141 X_QUERY_ID in NUMBER,
142 X_OWNER in VARCHAR2
143 ) is
144
145 l_user_id number := 0;
146 l_obj_verno number;
147 l_dummy_char varchar2(1);
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'
157 from AMS_CPAG_QUERY_USAGES
158 where QUERY_USAGE_ID = X_QUERY_USAGE_ID;
159
160 BEGIN
161
162 if X_OWNER = 'SEED' then
163 l_user_id := 1;
164 end if;
165
166 open c_chk_qry_exists;
167 fetch c_chk_qry_exists into l_dummy_char;
168 if c_chk_qry_exists%notfound
169 then
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,
179 X_QUERY_ID => X_QUERY_ID,
180 X_CREATION_DATE => SYSDATE,
181 X_CREATED_BY => l_user_id,
182 X_LAST_UPDATE_DATE => SYSDATE,
183 X_LAST_UPDATED_BY => l_user_id,
184 X_LAST_UPDATE_LOGIN => 0,
185 X_OBJECT_VERSION_NUMBER => l_obj_verno
186 );
187 else
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,
197 X_LAST_UPDATE_DATE => SYSDATE,
198 X_LAST_UPDATED_BY => l_user_id,
199 X_LAST_UPDATE_LOGIN => 0,
200 X_OBJECT_VERSION_NUMBER => l_obj_verno + 1
201 );
202 end if;
203
204 END LOAD_ROW;
205
206 end AMS_CPAG_QUERY_USAGES_PKG;