[Home] [Help]
PACKAGE BODY: APPS.HZ_PARTY_SITE_USES_PKG
Source
1 PACKAGE BODY HZ_PARTY_SITE_USES_PKG AS
2 /*$Header: ARHPSUTB.pls 115.9 2002/11/21 19:43:08 sponnamb ship $ */
3
4 PROCEDURE Insert_Row (
5 X_PARTY_SITE_USE_ID IN OUT NOCOPY NUMBER,
6 X_COMMENTS IN VARCHAR2,
7 X_PARTY_SITE_ID IN NUMBER,
8 X_SITE_USE_TYPE IN VARCHAR2,
9 X_PRIMARY_PER_TYPE IN VARCHAR2,
10 X_STATUS IN VARCHAR2,
11 X_OBJECT_VERSION_NUMBER IN NUMBER,
12 X_CREATED_BY_MODULE IN VARCHAR2,
13 X_APPLICATION_ID IN NUMBER
14 ) IS
15
16 l_success VARCHAR2(1) := 'N';
17
18 BEGIN
19
20 WHILE l_success = 'N' LOOP
21 BEGIN
22 INSERT INTO HZ_PARTY_SITE_USES (
23 PARTY_SITE_USE_ID,
24 COMMENTS,
25 PARTY_SITE_ID,
26 LAST_UPDATE_DATE,
27 REQUEST_ID,
28 LAST_UPDATED_BY,
29 PROGRAM_APPLICATION_ID,
30 CREATION_DATE,
31 PROGRAM_ID,
32 CREATED_BY,
33 PROGRAM_UPDATE_DATE,
34 LAST_UPDATE_LOGIN,
35 SITE_USE_TYPE,
36 PRIMARY_PER_TYPE,
37 STATUS,
38 OBJECT_VERSION_NUMBER,
39 CREATED_BY_MODULE,
40 APPLICATION_ID
41 )
42 VALUES (
43 DECODE( X_PARTY_SITE_USE_ID, FND_API.G_MISS_NUM, HZ_PARTY_SITE_USES_S.NEXTVAL, NULL, HZ_PARTY_SITE_USES_S.NEXTVAL, X_PARTY_SITE_USE_ID ),
44 DECODE( X_COMMENTS, FND_API.G_MISS_CHAR, NULL, X_COMMENTS ),
45 DECODE( X_PARTY_SITE_ID, FND_API.G_MISS_NUM, NULL, X_PARTY_SITE_ID ),
46 HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
47 HZ_UTILITY_V2PUB.REQUEST_ID,
48 HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
49 HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
50 HZ_UTILITY_V2PUB.CREATION_DATE,
51 HZ_UTILITY_V2PUB.PROGRAM_ID,
52 HZ_UTILITY_V2PUB.CREATED_BY,
53 HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
54 HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
55 DECODE( X_SITE_USE_TYPE, FND_API.G_MISS_CHAR, NULL, X_SITE_USE_TYPE ),
56 DECODE( X_PRIMARY_PER_TYPE, FND_API.G_MISS_CHAR, 'N', NULL, 'N', X_PRIMARY_PER_TYPE ),
57 DECODE( X_STATUS, FND_API.G_MISS_CHAR, 'A', NULL, 'A', X_STATUS ),
58 DECODE( X_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER ),
59 DECODE( X_CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE ),
60 DECODE( X_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, X_APPLICATION_ID )
61 ) RETURNING
62 PARTY_SITE_USE_ID
63 INTO
64 X_PARTY_SITE_USE_ID;
65
66 l_success := 'Y';
67
68 EXCEPTION
69 WHEN DUP_VAL_ON_INDEX THEN
70 IF INSTRB( SQLERRM, 'HZ_PARTY_SITE_USES_U1' ) <> 0 OR
71 INSTRB( SQLERRM, 'HZ_PARTY_SITE_USES_PK' ) <> 0
72 THEN
73 DECLARE
74 l_count NUMBER;
75 l_dummy VARCHAR2(1);
76 BEGIN
77 l_count := 1;
78 WHILE l_count > 0 LOOP
79 SELECT HZ_PARTY_SITE_USES_S.NEXTVAL
80 INTO X_PARTY_SITE_USE_ID FROM dual;
81 BEGIN
82 SELECT 'Y' INTO l_dummy
83 FROM HZ_PARTY_SITE_USES
84 WHERE PARTY_SITE_USE_ID = X_PARTY_SITE_USE_ID;
85 l_count := 1;
86 EXCEPTION
87 WHEN NO_DATA_FOUND THEN
88 l_count := 0;
89 END;
90 END LOOP;
91 END;
92 ELSE
93 RAISE;
94 END IF;
95
96 END;
97 END LOOP;
98
99 END Insert_Row;
100
101 PROCEDURE Update_Row (
102 X_Rowid IN OUT NOCOPY VARCHAR2,
103 X_PARTY_SITE_USE_ID IN NUMBER,
104 X_COMMENTS IN VARCHAR2,
105 X_PARTY_SITE_ID IN NUMBER,
106 X_SITE_USE_TYPE IN VARCHAR2,
107 X_PRIMARY_PER_TYPE IN VARCHAR2,
108 X_STATUS IN VARCHAR2,
109 X_OBJECT_VERSION_NUMBER IN NUMBER,
110 X_CREATED_BY_MODULE IN VARCHAR2,
111 X_APPLICATION_ID IN NUMBER
112 ) IS
113
114 BEGIN
115
116 UPDATE HZ_PARTY_SITE_USES SET
117 PARTY_SITE_USE_ID = DECODE( X_PARTY_SITE_USE_ID, NULL, PARTY_SITE_USE_ID, FND_API.G_MISS_NUM, NULL, X_PARTY_SITE_USE_ID ),
118 COMMENTS = DECODE( X_COMMENTS, NULL, COMMENTS, FND_API.G_MISS_CHAR, NULL, X_COMMENTS ),
119 PARTY_SITE_ID = DECODE( X_PARTY_SITE_ID, NULL, PARTY_SITE_ID, FND_API.G_MISS_NUM, NULL, X_PARTY_SITE_ID ),
120 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
121 REQUEST_ID = HZ_UTILITY_V2PUB.REQUEST_ID,
122 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
123 PROGRAM_APPLICATION_ID = HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
124 CREATION_DATE = CREATION_DATE,
125 PROGRAM_ID = HZ_UTILITY_V2PUB.PROGRAM_ID,
126 CREATED_BY = CREATED_BY,
127 PROGRAM_UPDATE_DATE = HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
128 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
129 SITE_USE_TYPE = DECODE( X_SITE_USE_TYPE, NULL, SITE_USE_TYPE, FND_API.G_MISS_CHAR, NULL, X_SITE_USE_TYPE ),
130 PRIMARY_PER_TYPE = DECODE( X_PRIMARY_PER_TYPE, NULL, PRIMARY_PER_TYPE, FND_API.G_MISS_CHAR, 'N', X_PRIMARY_PER_TYPE ),
131 STATUS = DECODE( X_STATUS, NULL, STATUS, FND_API.G_MISS_CHAR, 'A', X_STATUS ),
132 OBJECT_VERSION_NUMBER = DECODE( X_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER ),
133 CREATED_BY_MODULE = DECODE( X_CREATED_BY_MODULE, NULL, CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE ),
134 APPLICATION_ID = DECODE( X_APPLICATION_ID, NULL, APPLICATION_ID, FND_API.G_MISS_NUM, NULL, X_APPLICATION_ID )
135 WHERE ROWID = X_RowId;
136
137 IF ( SQL%NOTFOUND ) THEN
138 RAISE NO_DATA_FOUND;
139 END IF;
140
141 END Update_Row;
142
143 PROCEDURE Lock_Row (
144 X_Rowid IN OUT NOCOPY VARCHAR2,
145 X_PARTY_SITE_USE_ID IN NUMBER,
146 X_COMMENTS IN VARCHAR2,
147 X_PARTY_SITE_ID IN NUMBER,
148 X_LAST_UPDATE_DATE IN DATE,
149 X_REQUEST_ID IN NUMBER,
150 X_LAST_UPDATED_BY IN NUMBER,
151 X_PROGRAM_APPLICATION_ID IN NUMBER,
152 X_CREATION_DATE IN DATE,
153 X_PROGRAM_ID IN NUMBER,
154 X_CREATED_BY IN NUMBER,
155 X_PROGRAM_UPDATE_DATE IN DATE,
156 X_LAST_UPDATE_LOGIN IN NUMBER,
157 X_SITE_USE_TYPE IN VARCHAR2,
158 X_PRIMARY_PER_TYPE IN VARCHAR2,
159 X_STATUS IN VARCHAR2,
160 X_OBJECT_VERSION_NUMBER IN NUMBER,
161 X_CREATED_BY_MODULE IN VARCHAR2,
162 X_APPLICATION_ID IN NUMBER
163 ) IS
164
165 CURSOR C IS
166 SELECT * FROM HZ_PARTY_SITE_USES
167 WHERE ROWID = x_Rowid
168 FOR UPDATE NOWAIT;
169 Recinfo C%ROWTYPE;
170
171 BEGIN
172
173 OPEN C;
174 FETCH C INTO Recinfo;
175 IF ( C%NOTFOUND ) THEN
176 CLOSE C;
177 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
178 APP_EXCEPTION.RAISE_EXCEPTION;
179 END IF;
180 CLOSE C;
181
182 IF (
183 ( ( Recinfo.PARTY_SITE_USE_ID = X_PARTY_SITE_USE_ID )
184 OR ( ( Recinfo.PARTY_SITE_USE_ID IS NULL )
185 AND ( X_PARTY_SITE_USE_ID IS NULL ) ) )
186 AND ( ( Recinfo.COMMENTS = X_COMMENTS )
187 OR ( ( Recinfo.COMMENTS IS NULL )
188 AND ( X_COMMENTS IS NULL ) ) )
189 AND ( ( Recinfo.PARTY_SITE_ID = X_PARTY_SITE_ID )
190 OR ( ( Recinfo.PARTY_SITE_ID IS NULL )
191 AND ( X_PARTY_SITE_ID IS NULL ) ) )
192 AND ( ( Recinfo.LAST_UPDATE_DATE = X_LAST_UPDATE_DATE )
193 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
194 AND ( X_LAST_UPDATE_DATE IS NULL ) ) )
195 AND ( ( Recinfo.REQUEST_ID = X_REQUEST_ID )
196 OR ( ( Recinfo.REQUEST_ID IS NULL )
197 AND ( X_REQUEST_ID IS NULL ) ) )
198 AND ( ( Recinfo.LAST_UPDATED_BY = X_LAST_UPDATED_BY )
199 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
200 AND ( X_LAST_UPDATED_BY IS NULL ) ) )
201 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID )
202 OR ( ( Recinfo.PROGRAM_APPLICATION_ID IS NULL )
203 AND ( X_PROGRAM_APPLICATION_ID IS NULL ) ) )
204 AND ( ( Recinfo.CREATION_DATE = X_CREATION_DATE )
205 OR ( ( Recinfo.CREATION_DATE IS NULL )
206 AND ( X_CREATION_DATE IS NULL ) ) )
207 AND ( ( Recinfo.PROGRAM_ID = X_PROGRAM_ID )
208 OR ( ( Recinfo.PROGRAM_ID IS NULL )
209 AND ( X_PROGRAM_ID IS NULL ) ) )
210 AND ( ( Recinfo.CREATED_BY = X_CREATED_BY )
211 OR ( ( Recinfo.CREATED_BY IS NULL )
212 AND ( X_CREATED_BY IS NULL ) ) )
213 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE )
214 OR ( ( Recinfo.PROGRAM_UPDATE_DATE IS NULL )
215 AND ( X_PROGRAM_UPDATE_DATE IS NULL ) ) )
216 AND ( ( Recinfo.LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN )
217 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
218 AND ( X_LAST_UPDATE_LOGIN IS NULL ) ) )
219 AND ( ( Recinfo.SITE_USE_TYPE = X_SITE_USE_TYPE )
220 OR ( ( Recinfo.SITE_USE_TYPE IS NULL )
221 AND ( X_SITE_USE_TYPE IS NULL ) ) )
222 AND ( ( Recinfo.PRIMARY_PER_TYPE = X_PRIMARY_PER_TYPE )
223 OR ( ( Recinfo.PRIMARY_PER_TYPE IS NULL )
224 AND ( X_PRIMARY_PER_TYPE IS NULL ) ) )
225 AND ( ( Recinfo.STATUS = X_STATUS )
226 OR ( ( Recinfo.STATUS IS NULL )
227 AND ( X_STATUS IS NULL ) ) )
228 AND ( ( Recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER )
229 OR ( ( Recinfo.OBJECT_VERSION_NUMBER IS NULL )
230 AND ( X_OBJECT_VERSION_NUMBER IS NULL ) ) )
231 AND ( ( Recinfo.CREATED_BY_MODULE = X_CREATED_BY_MODULE )
232 OR ( ( Recinfo.CREATED_BY_MODULE IS NULL )
233 AND ( X_CREATED_BY_MODULE IS NULL ) ) )
234 AND ( ( Recinfo.APPLICATION_ID = X_APPLICATION_ID )
235 OR ( ( Recinfo.APPLICATION_ID IS NULL )
236 AND ( X_APPLICATION_ID IS NULL ) ) )
237 ) THEN
238 RETURN;
239 ELSE
240 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
241 APP_EXCEPTION.RAISE_EXCEPTION;
242 END IF;
243
244 END Lock_Row;
245
246 PROCEDURE Select_Row (
247 X_PARTY_SITE_USE_ID IN OUT NOCOPY NUMBER,
248 X_COMMENTS OUT NOCOPY VARCHAR2,
249 X_PARTY_SITE_ID OUT NOCOPY NUMBER,
250 X_SITE_USE_TYPE OUT NOCOPY VARCHAR2,
251 X_PRIMARY_PER_TYPE OUT NOCOPY VARCHAR2,
252 X_STATUS OUT NOCOPY VARCHAR2,
253 X_CREATED_BY_MODULE OUT NOCOPY VARCHAR2,
254 X_APPLICATION_ID OUT NOCOPY NUMBER
255 ) IS
256
257 BEGIN
258
259 SELECT
260 NVL( PARTY_SITE_USE_ID, FND_API.G_MISS_NUM ),
261 NVL( COMMENTS, FND_API.G_MISS_CHAR ),
262 NVL( PARTY_SITE_ID, FND_API.G_MISS_NUM ),
263 NVL( SITE_USE_TYPE, FND_API.G_MISS_CHAR ),
264 NVL( PRIMARY_PER_TYPE, FND_API.G_MISS_CHAR ),
265 NVL( STATUS, FND_API.G_MISS_CHAR ),
266 NVL( CREATED_BY_MODULE, FND_API.G_MISS_CHAR ),
267 NVL( APPLICATION_ID, FND_API.G_MISS_NUM )
268 INTO
269 X_PARTY_SITE_USE_ID,
270 X_COMMENTS,
271 X_PARTY_SITE_ID,
272 X_SITE_USE_TYPE,
273 X_PRIMARY_PER_TYPE,
274 X_STATUS,
275 X_CREATED_BY_MODULE,
276 X_APPLICATION_ID
277 FROM HZ_PARTY_SITE_USES
278 WHERE PARTY_SITE_USE_ID = X_PARTY_SITE_USE_ID;
279
280 EXCEPTION
281 WHEN NO_DATA_FOUND THEN
282 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
283 FND_MESSAGE.SET_TOKEN( 'RECORD', 'party_site_use_rec');
284 FND_MESSAGE.SET_TOKEN( 'VALUE', TO_CHAR( X_PARTY_SITE_USE_ID ) );
285 FND_MSG_PUB.ADD;
286 RAISE FND_API.G_EXC_ERROR;
287
288 END Select_Row;
289
290 PROCEDURE Delete_Row (
291 X_PARTY_SITE_USE_ID IN NUMBER
292 ) IS
293
294 BEGIN
295
296 DELETE FROM HZ_PARTY_SITE_USES
297 WHERE PARTY_SITE_USE_ID = X_PARTY_SITE_USE_ID;
298
299 IF ( SQL%NOTFOUND ) THEN
300 RAISE NO_DATA_FOUND;
301 END IF;
302
303 END Delete_Row;
304
305 END HZ_PARTY_SITE_USES_PKG;