[Home] [Help]
PACKAGE BODY: APPS.AMS_CTD_USAGES_PKG
Source
1 package body AMS_CTD_USAGES_PKG as
2 /* $Header: amslctub.pls 115.1 2003/11/18 13:49:51 mayjain noship $ */
3 procedure INSERT_ROW (
4 X_ROWID IN OUT NOCOPY VARCHAR2,
5 X_USAGE_ID IN NUMBER,
6 X_ACTION_ID IN NUMBER,
7 X_APPLICABLE_FOR IN VARCHAR2,
8 X_LAST_UPDATE_DATE in DATE,
9 X_LAST_UPDATED_BY in NUMBER,
10 X_CREATION_DATE in DATE,
11 X_CREATED_BY in NUMBER,
12 X_LAST_UPDATE_LOGIN in NUMBER,
13 X_OBJECT_VERSION_NUMBER in NUMBER
14 ) is
15 cursor c is select ROWID from AMS_CLIK_THRU_USAGES
16 where USAGE_ID = X_USAGE_ID
17 ;
18
19 begin
20 insert into AMS_CLIK_THRU_USAGES (
21 USAGE_ID,
22 ACTION_ID,
23 APPLICABLE_FOR,
24 LAST_UPDATE_DATE,
25 LAST_UPDATED_BY,
26 CREATION_DATE,
27 CREATED_BY,
28 LAST_UPDATE_LOGIN,
29 OBJECT_VERSION_NUMBER
30 ) values (
31 X_USAGE_ID,
32 X_ACTION_ID,
33 X_APPLICABLE_FOR,
34 DECODE(X_LAST_UPDATE_DATE,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,X_LAST_UPDATE_DATE),
35 DECODE(X_LAST_UPDATED_BY,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,X_LAST_UPDATED_BY),
36 DECODE(X_CREATION_DATE,FND_API.G_MISS_DATE,SYSDATE,NULL,SYSDATE,X_CREATION_DATE),
37 DECODE(X_CREATED_BY,FND_API.G_MISS_NUM,FND_GLOBAL.user_id,NULL,FND_GLOBAL.user_id,X_CREATED_BY),
38 DECODE(X_LAST_UPDATE_LOGIN,FND_API.G_MISS_NUM,FND_GLOBAL.login_id,NULL,FND_GLOBAL.user_id,X_LAST_UPDATE_LOGIN),
39 X_OBJECT_VERSION_NUMBER
40 );
41
42 open c;
43 fetch c into X_ROWID;
44 if (c%notfound) then
45 close c;
46 raise no_data_found;
47 end if;
48 close c;
49
50 end INSERT_ROW;
51
52
53
54
55 procedure LOCK_ROW (
56 X_USAGE_ID IN NUMBER,
57 X_OBJECT_VERSION_NUMBER in NUMBER,
58 X_ACTION_ID IN NUMBER,
59 X_APPLICABLE_FOR IN VARCHAR2
60 ) is
61 cursor c is select
62 OBJECT_VERSION_NUMBER,
63 ACTION_ID,
64 APPLICABLE_FOR,
65 LAST_UPDATE_DATE,
66 LAST_UPDATED_BY,
67 CREATION_DATE,
68 CREATED_BY,
69 LAST_UPDATE_LOGIN
70 from AMS_CLIK_THRU_USAGES
71 where USAGE_ID = X_USAGE_ID
72 for update of USAGE_ID nowait;
73 recinfo c%rowtype;
74
75
76 begin
77
78 open c;
79 fetch c into recinfo;
80 if (c%notfound) then
81 close c;
82 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
83 app_exception.raise_exception;
84 end if;
85 close c;
86 if ( ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
87 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
88 AND ((recinfo.ACTION_ID = X_ACTION_ID)
89 OR ((recinfo.ACTION_ID is null) AND (X_ACTION_ID is null)))
90 AND ((recinfo.APPLICABLE_FOR = X_APPLICABLE_FOR)
91 OR ((recinfo.APPLICABLE_FOR is null) AND (X_APPLICABLE_FOR is null)))
92 ) then
93 null;
94 else
95 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
96 app_exception.raise_exception;
97 end if;
98
99 return;
100 end LOCK_ROW;
101
102
103
104 procedure UPDATE_ROW (
105 X_USAGE_ID IN NUMBER,
106 X_OBJECT_VERSION_NUMBER in NUMBER,
107 X_ACTION_ID IN NUMBER,
108 X_APPLICABLE_FOR IN VARCHAR2,
109 X_LAST_UPDATE_DATE in DATE,
110 X_LAST_UPDATED_BY in NUMBER,
111 X_LAST_UPDATE_LOGIN in NUMBER
112 ) is
113 begin
114 update AMS_CLIK_THRU_USAGES set
115 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
116 ,ACTION_ID = X_ACTION_ID
117 ,APPLICABLE_FOR = X_APPLICABLE_FOR
118 ,LAST_UPDATE_DATE = X_LAST_UPDATE_DATE
119 ,LAST_UPDATED_BY = X_LAST_UPDATED_BY
120 ,LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
121 where USAGE_ID = X_USAGE_ID;
122
123 if (sql%notfound) then
124 raise no_data_found;
125 end if;
126
127 end UPDATE_ROW;
128
129
130 procedure DELETE_ROW (
131 X_USAGE_ID IN NUMBER
132 ) is
133 begin
134
135 delete from AMS_CLIK_THRU_USAGES
136 where USAGE_ID = X_USAGE_ID;
137
138 if (sql%notfound) then
139 raise no_data_found;
140 end if;
141
142 end DELETE_ROW;
143
144
145
146 procedure LOAD_ROW(
147 X_USAGE_ID IN NUMBER,
148 X_ACTION_ID IN NUMBER,
149 X_APPLICABLE_FOR IN VARCHAR2,
150 X_OWNER in VARCHAR2,
151 X_CUSTOM_MODE in VARCHAR2
152 ) is
153
154 l_user_id number := 0;
155 l_last_updated_by number;
156 l_obj_verno number;
157 l_dummy_char varchar2(1);
158 l_row_id varchar2(100);
159
160 cursor c_obj_verno is
161 select OBJECT_VERSION_NUMBER,
162 last_updated_by
163 from AMS_CLIK_THRU_USAGES
164 where USAGE_ID = X_USAGE_ID;
165
166 cursor c_chk_prd_exists is
167 select 'x'
168 from AMS_CLIK_THRU_USAGES
169 where USAGE_ID = X_USAGE_ID;
170
171 BEGIN
172
173 if X_OWNER = 'SEED' then
174 l_user_id := 1;
175 elsif X_OWNER = 'ORACLE' then
176 l_user_id := 2;
177 elsif X_OWNER = 'SYSADMIN' THEN
178 l_user_id := 0;
179 end if;
180
181 open c_chk_prd_exists;
182 fetch c_chk_prd_exists into l_dummy_char;
183 if c_chk_prd_exists%notfound
184 then
185 close c_chk_prd_exists;
186
187 l_obj_verno := 1;
188
189 AMS_CTD_USAGES_PKG.INSERT_ROW (
190 X_ROWID => l_row_id,
191 X_USAGE_ID => X_USAGE_ID,
192 X_ACTION_ID => X_ACTION_ID,
193 X_APPLICABLE_FOR => X_APPLICABLE_FOR,
194 X_LAST_UPDATE_DATE => sysdate,
195 X_LAST_UPDATED_BY => l_user_id,
196 X_CREATION_DATE => sysdate,
197 X_CREATED_BY => l_user_id,
198 X_LAST_UPDATE_LOGIN => 0,
199 X_OBJECT_VERSION_NUMBER => l_obj_verno
200 );
201 else
202 close c_chk_prd_exists;
203
204 open c_obj_verno;
205 fetch c_obj_verno into l_obj_verno,l_last_updated_by;
206 close c_obj_verno;
207
208 if (l_last_updated_by in (1,2,0) OR
209 NVL(x_custom_mode,'PRESERVE')='FORCE') THEN
210
211 AMS_CTD_USAGES_PKG.UPDATE_ROW(
212 X_USAGE_ID => X_USAGE_ID,
213 X_ACTION_ID => X_ACTION_ID,
214 X_APPLICABLE_FOR => X_APPLICABLE_FOR,
215 X_LAST_UPDATE_DATE => SYSDATE,
216 X_LAST_UPDATED_BY => l_user_id,
217 X_LAST_UPDATE_LOGIN => 0,
218 X_OBJECT_VERSION_NUMBER => l_obj_verno + 1
219 );
220 end if;
221 end if;
222
223 END LOAD_ROW;
224
225 PROCEDURE TRANSLATE_ROW (
226 X_USAGE_ID IN NUMBER,
227 X_OWNER IN VARCHAR2,
228 X_CUSTOM_MODE IN VARCHAR2
229 )
230 is
231 l_date DATE;
232 BEGIN
233 select sysdate into l_date from dual;
234 END TRANSLATE_ROW;
235
236
237 end AMS_CTD_USAGES_PKG;