[Home] [Help]
PACKAGE BODY: APPS.HZ_PARTY_SITES_PKG
Source
1 PACKAGE BODY HZ_PARTY_SITES_PKG AS
2 /*$Header: ARHPSTTB.pls 120.4 2005/07/29 01:26:55 jhuang ship $ */
3
4 G_MISS_CONTENT_SOURCE_TYPE CONSTANT VARCHAR2(30) := 'USER_ENTERED';
5
6 PROCEDURE Insert_Row (
7 X_PARTY_SITE_ID IN OUT NOCOPY NUMBER,
8 X_PARTY_ID IN NUMBER,
9 X_LOCATION_ID IN NUMBER,
10 X_PARTY_SITE_NUMBER IN OUT NOCOPY VARCHAR2,
11 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
12 X_ATTRIBUTE1 IN VARCHAR2,
13 X_ATTRIBUTE2 IN VARCHAR2,
14 X_ATTRIBUTE3 IN VARCHAR2,
15 X_ATTRIBUTE4 IN VARCHAR2,
16 X_ATTRIBUTE5 IN VARCHAR2,
17 X_ATTRIBUTE6 IN VARCHAR2,
18 X_ATTRIBUTE7 IN VARCHAR2,
19 X_ATTRIBUTE8 IN VARCHAR2,
20 X_ATTRIBUTE9 IN VARCHAR2,
21 X_ATTRIBUTE10 IN VARCHAR2,
22 X_ATTRIBUTE11 IN VARCHAR2,
23 X_ATTRIBUTE12 IN VARCHAR2,
24 X_ATTRIBUTE13 IN VARCHAR2,
25 X_ATTRIBUTE14 IN VARCHAR2,
26 X_ATTRIBUTE15 IN VARCHAR2,
27 X_ATTRIBUTE16 IN VARCHAR2,
28 X_ATTRIBUTE17 IN VARCHAR2,
29 X_ATTRIBUTE18 IN VARCHAR2,
30 X_ATTRIBUTE19 IN VARCHAR2,
31 X_ATTRIBUTE20 IN VARCHAR2,
32 X_ORIG_SYSTEM_REFERENCE IN VARCHAR2,
33 X_LANGUAGE IN VARCHAR2,
34 X_MAILSTOP IN VARCHAR2,
35 X_IDENTIFYING_ADDRESS_FLAG IN VARCHAR2,
36 X_STATUS IN VARCHAR2,
37 X_PARTY_SITE_NAME IN VARCHAR2,
38 X_ADDRESSEE IN VARCHAR2,
39 X_OBJECT_VERSION_NUMBER IN NUMBER,
40 X_CREATED_BY_MODULE IN VARCHAR2,
41 X_APPLICATION_ID IN NUMBER,
42 X_ACTUAL_CONTENT_SOURCE IN VARCHAR2,
43 X_GLOBAL_LOCATION_NUMBER IN VARCHAR2,
44 X_DUNS_NUMBER_C IN VARCHAR2 DEFAULT NULL
45 ) IS
46
47 l_success VARCHAR2(1) := 'N';
48
49 BEGIN
50
51 WHILE l_success = 'N' LOOP
52 BEGIN
53 INSERT INTO HZ_PARTY_SITES (
54 PARTY_SITE_ID,
55 PARTY_SITE_NUMBER,
56 PARTY_ID,
57 LOCATION_ID,
58 LAST_UPDATE_DATE,
59 LAST_UPDATED_BY,
60 CREATION_DATE,
61 CREATED_BY,
62 LAST_UPDATE_LOGIN,
63 REQUEST_ID,
64 PROGRAM_APPLICATION_ID,
65 PROGRAM_ID,
66 PROGRAM_UPDATE_DATE,
67 ATTRIBUTE_CATEGORY,
68 ATTRIBUTE1,
69 ATTRIBUTE2,
70 ATTRIBUTE3,
71 ATTRIBUTE4,
72 ATTRIBUTE5,
73 ATTRIBUTE6,
74 ATTRIBUTE7,
75 ATTRIBUTE8,
76 ATTRIBUTE9,
77 ATTRIBUTE10,
78 ATTRIBUTE11,
79 ATTRIBUTE12,
80 ATTRIBUTE13,
81 ATTRIBUTE14,
82 ATTRIBUTE15,
83 ATTRIBUTE16,
84 ATTRIBUTE17,
85 ATTRIBUTE18,
86 ATTRIBUTE19,
87 ATTRIBUTE20,
88 ORIG_SYSTEM_REFERENCE,
89 MAILSTOP,
90 IDENTIFYING_ADDRESS_FLAG,
91 STATUS,
92 PARTY_SITE_NAME,
93 ADDRESSEE,
94 OBJECT_VERSION_NUMBER,
95 CREATED_BY_MODULE,
96 APPLICATION_ID,
97 ACTUAL_CONTENT_SOURCE,
98 GLOBAL_LOCATION_NUMBER,
99 DUNS_NUMBER_C
100 )
101 VALUES (
102 DECODE( X_PARTY_SITE_ID, FND_API.G_MISS_NUM, HZ_PARTY_SITES_S.NEXTVAL, NULL, HZ_PARTY_SITES_S.NEXTVAL, X_PARTY_SITE_ID ),
103 DECODE( X_PARTY_SITE_NUMBER, FND_API.G_MISS_CHAR, TO_CHAR( HZ_PARTY_SITE_NUMBER_S.NEXTVAL ), NULL, TO_CHAR( HZ_PARTY_SITE_NUMBER_S.NEXTVAL ), X_PARTY_SITE_NUMBER ),
104 DECODE( X_PARTY_ID, FND_API.G_MISS_NUM, NULL, X_PARTY_ID ),
105 DECODE( X_LOCATION_ID, FND_API.G_MISS_NUM, NULL, X_LOCATION_ID ),
106 HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
107 HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
108 HZ_UTILITY_V2PUB.CREATION_DATE,
109 HZ_UTILITY_V2PUB.CREATED_BY,
110 HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
111 HZ_UTILITY_V2PUB.REQUEST_ID,
112 HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
113 HZ_UTILITY_V2PUB.PROGRAM_ID,
114 HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
115 DECODE( X_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE_CATEGORY ),
116 DECODE( X_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE1 ),
117 DECODE( X_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE2 ),
118 DECODE( X_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE3 ),
119 DECODE( X_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE4 ),
120 DECODE( X_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE5 ),
121 DECODE( X_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE6 ),
122 DECODE( X_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE7 ),
123 DECODE( X_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE8 ),
124 DECODE( X_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE9 ),
125 DECODE( X_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE10 ),
126 DECODE( X_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE11 ),
127 DECODE( X_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE12 ),
128 DECODE( X_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE13 ),
129 DECODE( X_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE14 ),
130 DECODE( X_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE15 ),
131 DECODE( X_ATTRIBUTE16, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE16 ),
132 DECODE( X_ATTRIBUTE17, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE17 ),
133 DECODE( X_ATTRIBUTE18, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE18 ),
134 DECODE( X_ATTRIBUTE19, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE19 ),
135 DECODE( X_ATTRIBUTE20, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE20 ),
136 DECODE( X_ORIG_SYSTEM_REFERENCE, FND_API.G_MISS_CHAR, TO_CHAR(NVL(X_PARTY_SITE_ID,HZ_PARTY_SITES_S.CURRVAL)), NULL, TO_CHAR(NVL(X_PARTY_SITE_ID,HZ_PARTY_SITES_S.CURRVAL)), X_ORIG_SYSTEM_REFERENCE ),
137 DECODE( X_MAILSTOP, FND_API.G_MISS_CHAR, NULL, X_MAILSTOP ),
138 DECODE( X_IDENTIFYING_ADDRESS_FLAG, FND_API.G_MISS_CHAR, 'N', NULL, 'N', X_IDENTIFYING_ADDRESS_FLAG ),
139 DECODE( X_STATUS, FND_API.G_MISS_CHAR, 'A', NULL, 'A', X_STATUS ),
140 DECODE( X_PARTY_SITE_NAME, FND_API.G_MISS_CHAR, NULL, X_PARTY_SITE_NAME ),
141 DECODE( X_ADDRESSEE, FND_API.G_MISS_CHAR, NULL, X_ADDRESSEE ),
142 DECODE( X_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER ),
143 DECODE( X_CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE ),
144 DECODE( X_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, X_APPLICATION_ID ),
145 decode( X_ACTUAL_CONTENT_SOURCE,
146 FND_API.G_MISS_CHAR, G_MISS_CONTENT_SOURCE_TYPE,
147 NULL, G_MISS_CONTENT_SOURCE_TYPE, X_ACTUAL_CONTENT_SOURCE ),
148 DECODE( X_GLOBAL_LOCATION_NUMBER, FND_API.G_MISS_CHAR, NULL, X_GLOBAL_LOCATION_NUMBER),
149 DECODE( X_DUNS_NUMBER_C, FND_API.G_MISS_CHAR, NULL, X_DUNS_NUMBER_C )
150 ) RETURNING
151 PARTY_SITE_ID,
152 PARTY_SITE_NUMBER
153 INTO
154 X_PARTY_SITE_ID,
155 X_PARTY_SITE_NUMBER;
156
157 l_success := 'Y';
158
159 EXCEPTION
160 WHEN DUP_VAL_ON_INDEX THEN
161 IF INSTRB( SQLERRM, 'HZ_PARTY_SITES_U1' ) <> 0 OR
162 INSTRB( SQLERRM, 'HZ_PARTY_SITES_PK' ) <> 0
163 THEN
164 DECLARE
165 l_count NUMBER;
166 l_dummy VARCHAR2(1);
167 BEGIN
168 l_count := 1;
169 WHILE l_count > 0 LOOP
170 SELECT HZ_PARTY_SITES_S.NEXTVAL
171 INTO X_PARTY_SITE_ID FROM dual;
172 BEGIN
173 SELECT 'Y' INTO l_dummy
174 FROM HZ_PARTY_SITES
175 WHERE PARTY_SITE_ID = X_PARTY_SITE_ID;
176 l_count := 1;
177 EXCEPTION
178 WHEN NO_DATA_FOUND THEN
179 l_count := 0;
180 END;
181 END LOOP;
182 END;
183 ELSIF INSTRB( SQLERRM, 'HZ_PARTY_SITES_U2' ) <> 0 THEN
184 DECLARE
185 l_count NUMBER;
186 l_dummy VARCHAR2(1);
187 BEGIN
188 l_count := 1;
189 WHILE l_count > 0 LOOP
190 SELECT TO_CHAR( HZ_PARTY_SITE_NUMBER_S.NEXTVAL )
191 INTO X_PARTY_SITE_NUMBER FROM dual;
192 BEGIN
193 SELECT 'Y' INTO l_dummy
194 FROM HZ_PARTY_SITES
195 WHERE PARTY_SITE_NUMBER = X_PARTY_SITE_NUMBER;
196 l_count := 1;
197 EXCEPTION
198 WHEN NO_DATA_FOUND THEN
199 l_count := 0;
200 END;
201 END LOOP;
202 END;
203 ELSE
204 RAISE;
205 END IF;
206
207 END;
208 END LOOP;
209
210 END Insert_Row;
211
212 PROCEDURE Update_Row (
213 X_Rowid IN OUT NOCOPY VARCHAR2,
214 X_PARTY_SITE_ID IN NUMBER,
215 X_PARTY_ID IN NUMBER,
216 X_LOCATION_ID IN NUMBER,
217 X_PARTY_SITE_NUMBER IN VARCHAR2,
218 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
219 X_ATTRIBUTE1 IN VARCHAR2,
220 X_ATTRIBUTE2 IN VARCHAR2,
221 X_ATTRIBUTE3 IN VARCHAR2,
222 X_ATTRIBUTE4 IN VARCHAR2,
223 X_ATTRIBUTE5 IN VARCHAR2,
224 X_ATTRIBUTE6 IN VARCHAR2,
225 X_ATTRIBUTE7 IN VARCHAR2,
226 X_ATTRIBUTE8 IN VARCHAR2,
227 X_ATTRIBUTE9 IN VARCHAR2,
228 X_ATTRIBUTE10 IN VARCHAR2,
229 X_ATTRIBUTE11 IN VARCHAR2,
230 X_ATTRIBUTE12 IN VARCHAR2,
231 X_ATTRIBUTE13 IN VARCHAR2,
232 X_ATTRIBUTE14 IN VARCHAR2,
233 X_ATTRIBUTE15 IN VARCHAR2,
234 X_ATTRIBUTE16 IN VARCHAR2,
235 X_ATTRIBUTE17 IN VARCHAR2,
236 X_ATTRIBUTE18 IN VARCHAR2,
237 X_ATTRIBUTE19 IN VARCHAR2,
238 X_ATTRIBUTE20 IN VARCHAR2,
239 X_ORIG_SYSTEM_REFERENCE IN VARCHAR2,
240 X_LANGUAGE IN VARCHAR2,
241 X_MAILSTOP IN VARCHAR2,
242 X_IDENTIFYING_ADDRESS_FLAG IN VARCHAR2,
243 X_STATUS IN VARCHAR2,
244 X_PARTY_SITE_NAME IN VARCHAR2,
245 X_ADDRESSEE IN VARCHAR2,
246 X_OBJECT_VERSION_NUMBER IN NUMBER,
247 X_CREATED_BY_MODULE IN VARCHAR2,
248 X_APPLICATION_ID IN NUMBER,
249 X_ACTUAL_CONTENT_SOURCE IN VARCHAR2 DEFAULT NULL,
250 X_GLOBAL_LOCATION_NUMBER IN VARCHAR2,
251 X_DUNS_NUMBER_C IN VARCHAR2 DEFAULT NULL
252 ) IS
253
254 BEGIN
255
256 UPDATE HZ_PARTY_SITES SET
257 PARTY_SITE_ID = DECODE( X_PARTY_SITE_ID, NULL, PARTY_SITE_ID, FND_API.G_MISS_NUM, NULL, X_PARTY_SITE_ID ),
258 PARTY_ID = DECODE( X_PARTY_ID, NULL, PARTY_ID, FND_API.G_MISS_NUM, NULL, X_PARTY_ID ),
259 LOCATION_ID = DECODE( X_LOCATION_ID, NULL, LOCATION_ID, FND_API.G_MISS_NUM, NULL, X_LOCATION_ID ),
260 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
261 PARTY_SITE_NUMBER = DECODE( X_PARTY_SITE_NUMBER, NULL, PARTY_SITE_NUMBER, FND_API.G_MISS_CHAR, NULL, X_PARTY_SITE_NUMBER ),
262 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
263 CREATION_DATE = CREATION_DATE,
264 CREATED_BY = CREATED_BY,
265 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
266 REQUEST_ID = HZ_UTILITY_V2PUB.REQUEST_ID,
267 PROGRAM_APPLICATION_ID = HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
268 PROGRAM_ID = HZ_UTILITY_V2PUB.PROGRAM_ID,
269 PROGRAM_UPDATE_DATE = HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
270 ATTRIBUTE_CATEGORY = DECODE( X_ATTRIBUTE_CATEGORY, NULL, ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE_CATEGORY ),
271 ATTRIBUTE1 = DECODE( X_ATTRIBUTE1, NULL, ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE1 ),
272 ATTRIBUTE2 = DECODE( X_ATTRIBUTE2, NULL, ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE2 ),
273 ATTRIBUTE3 = DECODE( X_ATTRIBUTE3, NULL, ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE3 ),
274 ATTRIBUTE4 = DECODE( X_ATTRIBUTE4, NULL, ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE4 ),
275 ATTRIBUTE5 = DECODE( X_ATTRIBUTE5, NULL, ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE5 ),
276 ATTRIBUTE6 = DECODE( X_ATTRIBUTE6, NULL, ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE6 ),
277 ATTRIBUTE7 = DECODE( X_ATTRIBUTE7, NULL, ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE7 ),
278 ATTRIBUTE8 = DECODE( X_ATTRIBUTE8, NULL, ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE8 ),
279 ATTRIBUTE9 = DECODE( X_ATTRIBUTE9, NULL, ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE9 ),
280 ATTRIBUTE10 = DECODE( X_ATTRIBUTE10, NULL, ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE10 ),
281 ATTRIBUTE11 = DECODE( X_ATTRIBUTE11, NULL, ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE11 ),
282 ATTRIBUTE12 = DECODE( X_ATTRIBUTE12, NULL, ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE12 ),
283 ATTRIBUTE13 = DECODE( X_ATTRIBUTE13, NULL, ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE13 ),
284 ATTRIBUTE14 = DECODE( X_ATTRIBUTE14, NULL, ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE14 ),
285 ATTRIBUTE15 = DECODE( X_ATTRIBUTE15, NULL, ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE15 ),
286 ATTRIBUTE16 = DECODE( X_ATTRIBUTE16, NULL, ATTRIBUTE16, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE16 ),
287 ATTRIBUTE17 = DECODE( X_ATTRIBUTE17, NULL, ATTRIBUTE17, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE17 ),
288 ATTRIBUTE18 = DECODE( X_ATTRIBUTE18, NULL, ATTRIBUTE18, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE18 ),
289 ATTRIBUTE19 = DECODE( X_ATTRIBUTE19, NULL, ATTRIBUTE19, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE19 ),
290 ATTRIBUTE20 = DECODE( X_ATTRIBUTE20, NULL, ATTRIBUTE20, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE20 ),
291 ORIG_SYSTEM_REFERENCE = DECODE( X_ORIG_SYSTEM_REFERENCE, NULL, ORIG_SYSTEM_REFERENCE, FND_API.G_MISS_CHAR, ORIG_SYSTEM_REFERENCE, X_ORIG_SYSTEM_REFERENCE ),
292 MAILSTOP = DECODE( X_MAILSTOP, NULL, MAILSTOP, FND_API.G_MISS_CHAR, NULL, X_MAILSTOP ),
293 IDENTIFYING_ADDRESS_FLAG = DECODE( X_IDENTIFYING_ADDRESS_FLAG, NULL, IDENTIFYING_ADDRESS_FLAG, FND_API.G_MISS_CHAR, 'N', X_IDENTIFYING_ADDRESS_FLAG ),
294 STATUS = DECODE( X_STATUS, NULL, STATUS, FND_API.G_MISS_CHAR, 'A', X_STATUS ),
295 PARTY_SITE_NAME = DECODE( X_PARTY_SITE_NAME, NULL, PARTY_SITE_NAME, FND_API.G_MISS_CHAR, NULL, X_PARTY_SITE_NAME ),
299 APPLICATION_ID = DECODE( X_APPLICATION_ID, NULL, APPLICATION_ID, FND_API.G_MISS_NUM, NULL, X_APPLICATION_ID ),
296 ADDRESSEE = DECODE( X_ADDRESSEE, NULL, ADDRESSEE, FND_API.G_MISS_CHAR, NULL, X_ADDRESSEE ),
297 OBJECT_VERSION_NUMBER = DECODE( X_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER ),
298 CREATED_BY_MODULE = DECODE( X_CREATED_BY_MODULE, NULL, CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE ),
300 ACTUAL_CONTENT_SOURCE = DECODE( x_ACTUAL_CONTENT_SOURCE, NULL, ACTUAL_CONTENT_SOURCE,
301 FND_API.G_MISS_CHAR, NULL, X_ACTUAL_CONTENT_SOURCE),
302 GLOBAL_LOCATION_NUMBER = DECODE ( X_GLOBAL_LOCATION_NUMBER, NULL, GLOBAL_LOCATION_NUMBER, FND_API.G_MISS_CHAR, NULL, X_GLOBAL_LOCATION_NUMBER),
303 DUNS_NUMBER_C = DECODE( X_DUNS_NUMBER_C, NULL, DUNS_NUMBER_C, FND_API.G_MISS_CHAR, NULL, X_DUNS_NUMBER_C )
304 WHERE ROWID = X_RowId;
305
306 IF ( SQL%NOTFOUND ) THEN
307 RAISE NO_DATA_FOUND;
308 END IF;
309
310 END Update_Row;
311
312 PROCEDURE Lock_Row (
313 X_Rowid IN OUT NOCOPY VARCHAR2,
314 X_PARTY_SITE_ID IN NUMBER,
315 X_PARTY_ID IN NUMBER,
316 X_LOCATION_ID IN NUMBER,
317 X_LAST_UPDATE_DATE IN DATE,
318 X_PARTY_SITE_NUMBER IN VARCHAR2,
319 X_LAST_UPDATED_BY IN NUMBER,
320 X_CREATION_DATE IN DATE,
321 X_CREATED_BY IN NUMBER,
322 X_LAST_UPDATE_LOGIN IN NUMBER,
323 X_REQUEST_ID IN NUMBER,
324 X_PROGRAM_APPLICATION_ID IN NUMBER,
325 X_PROGRAM_ID IN NUMBER,
326 X_PROGRAM_UPDATE_DATE IN DATE,
327 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
328 X_ATTRIBUTE1 IN VARCHAR2,
329 X_ATTRIBUTE2 IN VARCHAR2,
330 X_ATTRIBUTE3 IN VARCHAR2,
331 X_ATTRIBUTE4 IN VARCHAR2,
332 X_ATTRIBUTE5 IN VARCHAR2,
333 X_ATTRIBUTE6 IN VARCHAR2,
334 X_ATTRIBUTE7 IN VARCHAR2,
335 X_ATTRIBUTE8 IN VARCHAR2,
336 X_ATTRIBUTE9 IN VARCHAR2,
337 X_ATTRIBUTE10 IN VARCHAR2,
338 X_ATTRIBUTE11 IN VARCHAR2,
339 X_ATTRIBUTE12 IN VARCHAR2,
340 X_ATTRIBUTE13 IN VARCHAR2,
341 X_ATTRIBUTE14 IN VARCHAR2,
342 X_ATTRIBUTE15 IN VARCHAR2,
343 X_ATTRIBUTE16 IN VARCHAR2,
344 X_ATTRIBUTE17 IN VARCHAR2,
345 X_ATTRIBUTE18 IN VARCHAR2,
346 X_ATTRIBUTE19 IN VARCHAR2,
347 X_ATTRIBUTE20 IN VARCHAR2,
348 X_ORIG_SYSTEM_REFERENCE IN VARCHAR2,
349 X_LANGUAGE IN VARCHAR2,
350 X_MAILSTOP IN VARCHAR2,
351 X_IDENTIFYING_ADDRESS_FLAG IN VARCHAR2,
352 X_STATUS IN VARCHAR2,
353 X_PARTY_SITE_NAME IN VARCHAR2,
354 X_ADDRESSEE IN VARCHAR2,
355 X_OBJECT_VERSION_NUMBER IN NUMBER,
356 X_CREATED_BY_MODULE IN VARCHAR2,
357 X_APPLICATION_ID IN NUMBER,
358 X_ACTUAL_CONTENT_SOURCE IN VARCHAR2 DEFAULT NULL,
359 X_GLOBAL_LOCATION_NUMBER IN VARCHAR2
360 ) IS
361
362 CURSOR C IS
363 SELECT * FROM HZ_PARTY_SITES
364 WHERE ROWID = x_Rowid
365 FOR UPDATE NOWAIT;
366 Recinfo C%ROWTYPE;
367
368 BEGIN
369
370 OPEN C;
371 FETCH C INTO Recinfo;
372 IF ( C%NOTFOUND ) THEN
373 CLOSE C;
374 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
375 APP_EXCEPTION.RAISE_EXCEPTION;
376 END IF;
377 CLOSE C;
378
379 IF (
380 ( ( Recinfo.PARTY_SITE_ID = X_PARTY_SITE_ID )
381 OR ( ( Recinfo.PARTY_SITE_ID IS NULL )
382 AND ( X_PARTY_SITE_ID IS NULL ) ) )
383 AND ( ( Recinfo.PARTY_ID = X_PARTY_ID )
384 OR ( ( Recinfo.PARTY_ID IS NULL )
385 AND ( X_PARTY_ID IS NULL ) ) )
386 AND ( ( Recinfo.LOCATION_ID = X_LOCATION_ID )
387 OR ( ( Recinfo.LOCATION_ID IS NULL )
388 AND ( X_LOCATION_ID IS NULL ) ) )
389 AND ( ( Recinfo.LAST_UPDATE_DATE = X_LAST_UPDATE_DATE )
390 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
391 AND ( X_LAST_UPDATE_DATE IS NULL ) ) )
392 AND ( ( Recinfo.PARTY_SITE_NUMBER = X_PARTY_SITE_NUMBER )
393 OR ( ( Recinfo.PARTY_SITE_NUMBER IS NULL )
394 AND ( X_PARTY_SITE_NUMBER IS NULL ) ) )
395 AND ( ( Recinfo.LAST_UPDATED_BY = X_LAST_UPDATED_BY )
396 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
397 AND ( X_LAST_UPDATED_BY IS NULL ) ) )
398 AND ( ( Recinfo.CREATION_DATE = X_CREATION_DATE )
399 OR ( ( Recinfo.CREATION_DATE IS NULL )
400 AND ( X_CREATION_DATE IS NULL ) ) )
404 AND ( ( Recinfo.LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN )
401 AND ( ( Recinfo.CREATED_BY = X_CREATED_BY )
402 OR ( ( Recinfo.CREATED_BY IS NULL )
403 AND ( X_CREATED_BY IS NULL ) ) )
405 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
406 AND ( X_LAST_UPDATE_LOGIN IS NULL ) ) )
407 AND ( ( Recinfo.REQUEST_ID = X_REQUEST_ID )
408 OR ( ( Recinfo.REQUEST_ID IS NULL )
409 AND ( X_REQUEST_ID IS NULL ) ) )
410 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID )
411 OR ( ( Recinfo.PROGRAM_APPLICATION_ID IS NULL )
412 AND ( X_PROGRAM_APPLICATION_ID IS NULL ) ) )
413 AND ( ( Recinfo.PROGRAM_ID = X_PROGRAM_ID )
414 OR ( ( Recinfo.PROGRAM_ID IS NULL )
415 AND ( X_PROGRAM_ID IS NULL ) ) )
416 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE )
417 OR ( ( Recinfo.PROGRAM_UPDATE_DATE IS NULL )
418 AND ( X_PROGRAM_UPDATE_DATE IS NULL ) ) )
419 AND ( ( Recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY )
420 OR ( ( Recinfo.ATTRIBUTE_CATEGORY IS NULL )
421 AND ( X_ATTRIBUTE_CATEGORY IS NULL ) ) )
422 AND ( ( Recinfo.ATTRIBUTE1 = X_ATTRIBUTE1 )
423 OR ( ( Recinfo.ATTRIBUTE1 IS NULL )
424 AND ( X_ATTRIBUTE1 IS NULL ) ) )
425 AND ( ( Recinfo.ATTRIBUTE2 = X_ATTRIBUTE2 )
426 OR ( ( Recinfo.ATTRIBUTE2 IS NULL )
427 AND ( X_ATTRIBUTE2 IS NULL ) ) )
428 AND ( ( Recinfo.ATTRIBUTE3 = X_ATTRIBUTE3 )
429 OR ( ( Recinfo.ATTRIBUTE3 IS NULL )
430 AND ( X_ATTRIBUTE3 IS NULL ) ) )
431 AND ( ( Recinfo.ATTRIBUTE4 = X_ATTRIBUTE4 )
432 OR ( ( Recinfo.ATTRIBUTE4 IS NULL )
433 AND ( X_ATTRIBUTE4 IS NULL ) ) )
434 AND ( ( Recinfo.ATTRIBUTE5 = X_ATTRIBUTE5 )
435 OR ( ( Recinfo.ATTRIBUTE5 IS NULL )
436 AND ( X_ATTRIBUTE5 IS NULL ) ) )
437 AND ( ( Recinfo.ATTRIBUTE6 = X_ATTRIBUTE6 )
438 OR ( ( Recinfo.ATTRIBUTE6 IS NULL )
439 AND ( X_ATTRIBUTE6 IS NULL ) ) )
440 AND ( ( Recinfo.ATTRIBUTE7 = X_ATTRIBUTE7 )
441 OR ( ( Recinfo.ATTRIBUTE7 IS NULL )
442 AND ( X_ATTRIBUTE7 IS NULL ) ) )
443 AND ( ( Recinfo.ATTRIBUTE8 = X_ATTRIBUTE8 )
444 OR ( ( Recinfo.ATTRIBUTE8 IS NULL )
445 AND ( X_ATTRIBUTE8 IS NULL ) ) )
446 AND ( ( Recinfo.ATTRIBUTE9 = X_ATTRIBUTE9 )
447 OR ( ( Recinfo.ATTRIBUTE9 IS NULL )
448 AND ( X_ATTRIBUTE9 IS NULL ) ) )
449 AND ( ( Recinfo.ATTRIBUTE10 = X_ATTRIBUTE10 )
450 OR ( ( Recinfo.ATTRIBUTE10 IS NULL )
451 AND ( X_ATTRIBUTE10 IS NULL ) ) )
452 AND ( ( Recinfo.ATTRIBUTE11 = X_ATTRIBUTE11 )
453 OR ( ( Recinfo.ATTRIBUTE11 IS NULL )
454 AND ( X_ATTRIBUTE11 IS NULL ) ) )
455 AND ( ( Recinfo.ATTRIBUTE12 = X_ATTRIBUTE12 )
456 OR ( ( Recinfo.ATTRIBUTE12 IS NULL )
457 AND ( X_ATTRIBUTE12 IS NULL ) ) )
458 AND ( ( Recinfo.ATTRIBUTE13 = X_ATTRIBUTE13 )
459 OR ( ( Recinfo.ATTRIBUTE13 IS NULL )
460 AND ( X_ATTRIBUTE13 IS NULL ) ) )
461 AND ( ( Recinfo.ATTRIBUTE14 = X_ATTRIBUTE14 )
462 OR ( ( Recinfo.ATTRIBUTE14 IS NULL )
463 AND ( X_ATTRIBUTE14 IS NULL ) ) )
464 AND ( ( Recinfo.ATTRIBUTE15 = X_ATTRIBUTE15 )
465 OR ( ( Recinfo.ATTRIBUTE15 IS NULL )
466 AND ( X_ATTRIBUTE15 IS NULL ) ) )
467 AND ( ( Recinfo.ATTRIBUTE16 = X_ATTRIBUTE16 )
468 OR ( ( Recinfo.ATTRIBUTE16 IS NULL )
469 AND ( X_ATTRIBUTE16 IS NULL ) ) )
470 AND ( ( Recinfo.ATTRIBUTE17 = X_ATTRIBUTE17 )
471 OR ( ( Recinfo.ATTRIBUTE17 IS NULL )
472 AND ( X_ATTRIBUTE17 IS NULL ) ) )
473 AND ( ( Recinfo.ATTRIBUTE18 = X_ATTRIBUTE18 )
474 OR ( ( Recinfo.ATTRIBUTE18 IS NULL )
475 AND ( X_ATTRIBUTE18 IS NULL ) ) )
476 AND ( ( Recinfo.ATTRIBUTE19 = X_ATTRIBUTE19 )
477 OR ( ( Recinfo.ATTRIBUTE19 IS NULL )
478 AND ( X_ATTRIBUTE19 IS NULL ) ) )
479 AND ( ( Recinfo.ATTRIBUTE20 = X_ATTRIBUTE20 )
480 OR ( ( Recinfo.ATTRIBUTE20 IS NULL )
481 AND ( X_ATTRIBUTE20 IS NULL ) ) )
482 AND ( ( Recinfo.ORIG_SYSTEM_REFERENCE = X_ORIG_SYSTEM_REFERENCE )
483 OR ( ( Recinfo.ORIG_SYSTEM_REFERENCE IS NULL )
484 AND ( X_ORIG_SYSTEM_REFERENCE IS NULL ) ) )
485 AND ( ( Recinfo.MAILSTOP = X_MAILSTOP )
486 OR ( ( Recinfo.MAILSTOP IS NULL )
487 AND ( X_MAILSTOP IS NULL ) ) )
488 AND ( ( Recinfo.IDENTIFYING_ADDRESS_FLAG = X_IDENTIFYING_ADDRESS_FLAG )
489 OR ( ( Recinfo.IDENTIFYING_ADDRESS_FLAG IS NULL )
490 AND ( X_IDENTIFYING_ADDRESS_FLAG IS NULL ) ) )
491 AND ( ( Recinfo.STATUS = X_STATUS )
492 OR ( ( Recinfo.STATUS IS NULL )
493 AND ( X_STATUS IS NULL ) ) )
494 AND ( ( Recinfo.PARTY_SITE_NAME = X_PARTY_SITE_NAME )
495 OR ( ( Recinfo.PARTY_SITE_NAME IS NULL )
496 AND ( X_PARTY_SITE_NAME IS NULL ) ) )
497 AND ( ( Recinfo.ADDRESSEE = X_ADDRESSEE )
498 OR ( ( Recinfo.ADDRESSEE IS NULL )
499 AND ( X_ADDRESSEE IS NULL ) ) )
500 AND ( ( Recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER )
501 OR ( ( Recinfo.OBJECT_VERSION_NUMBER IS NULL )
502 AND ( X_OBJECT_VERSION_NUMBER IS NULL ) ) )
503 AND ( ( Recinfo.CREATED_BY_MODULE = X_CREATED_BY_MODULE )
504 OR ( ( Recinfo.CREATED_BY_MODULE IS NULL )
505 AND ( X_CREATED_BY_MODULE IS NULL ) ) )
506 AND ( ( Recinfo.APPLICATION_ID = X_APPLICATION_ID )
510 OR ( ( Recinfo.ACTUAL_CONTENT_SOURCE IS NULL )
507 OR ( ( Recinfo.APPLICATION_ID IS NULL )
508 AND ( X_APPLICATION_ID IS NULL ) ) )
509 AND ( ( Recinfo.ACTUAL_CONTENT_SOURCE = X_ACTUAL_CONTENT_SOURCE )
511 AND ( X_ACTUAL_CONTENT_SOURCE IS NULL ) ) )
512 AND ( ( Recinfo.GLOBAL_LOCATION_NUMBER = X_GLOBAL_LOCATION_NUMBER )
513 OR ( ( Recinfo.GLOBAL_LOCATION_NUMBER IS NULL )
514 AND ( X_GLOBAL_LOCATION_NUMBER IS NULL ) ) )
515 ) THEN
516 RETURN;
517 ELSE
518 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
519 APP_EXCEPTION.RAISE_EXCEPTION;
520 END IF;
521
522 END Lock_Row;
523
524 PROCEDURE Select_Row (
525 X_PARTY_SITE_ID IN OUT NOCOPY NUMBER,
526 X_PARTY_ID OUT NOCOPY NUMBER,
527 X_LOCATION_ID OUT NOCOPY NUMBER,
528 X_PARTY_SITE_NUMBER OUT NOCOPY VARCHAR2,
529 X_ATTRIBUTE_CATEGORY OUT NOCOPY VARCHAR2,
530 X_ATTRIBUTE1 OUT NOCOPY VARCHAR2,
531 X_ATTRIBUTE2 OUT NOCOPY VARCHAR2,
532 X_ATTRIBUTE3 OUT NOCOPY VARCHAR2,
533 X_ATTRIBUTE4 OUT NOCOPY VARCHAR2,
534 X_ATTRIBUTE5 OUT NOCOPY VARCHAR2,
535 X_ATTRIBUTE6 OUT NOCOPY VARCHAR2,
536 X_ATTRIBUTE7 OUT NOCOPY VARCHAR2,
537 X_ATTRIBUTE8 OUT NOCOPY VARCHAR2,
538 X_ATTRIBUTE9 OUT NOCOPY VARCHAR2,
539 X_ATTRIBUTE10 OUT NOCOPY VARCHAR2,
540 X_ATTRIBUTE11 OUT NOCOPY VARCHAR2,
541 X_ATTRIBUTE12 OUT NOCOPY VARCHAR2,
542 X_ATTRIBUTE13 OUT NOCOPY VARCHAR2,
543 X_ATTRIBUTE14 OUT NOCOPY VARCHAR2,
544 X_ATTRIBUTE15 OUT NOCOPY VARCHAR2,
545 X_ATTRIBUTE16 OUT NOCOPY VARCHAR2,
546 X_ATTRIBUTE17 OUT NOCOPY VARCHAR2,
547 X_ATTRIBUTE18 OUT NOCOPY VARCHAR2,
548 X_ATTRIBUTE19 OUT NOCOPY VARCHAR2,
549 X_ATTRIBUTE20 OUT NOCOPY VARCHAR2,
550 X_ORIG_SYSTEM_REFERENCE OUT NOCOPY VARCHAR2,
551 X_LANGUAGE OUT NOCOPY VARCHAR2,
552 X_MAILSTOP OUT NOCOPY VARCHAR2,
553 X_IDENTIFYING_ADDRESS_FLAG OUT NOCOPY VARCHAR2,
554 X_STATUS OUT NOCOPY VARCHAR2,
555 X_PARTY_SITE_NAME OUT NOCOPY VARCHAR2,
556 X_ADDRESSEE OUT NOCOPY VARCHAR2,
557 X_CREATED_BY_MODULE OUT NOCOPY VARCHAR2,
558 X_APPLICATION_ID OUT NOCOPY NUMBER,
559 X_ACTUAL_CONTENT_SOURCE OUT NOCOPY VARCHAR2,
560 X_GLOBAL_LOCATION_NUMBER OUT NOCOPY VARCHAR2,
561 X_DUNS_NUMBER_C OUT NOCOPY VARCHAR2
562 ) IS
563
564 BEGIN
565
566 SELECT
567 NVL( PARTY_SITE_ID, FND_API.G_MISS_NUM ),
568 NVL( PARTY_ID, FND_API.G_MISS_NUM ),
569 NVL( LOCATION_ID, FND_API.G_MISS_NUM ),
570 NVL( PARTY_SITE_NUMBER, FND_API.G_MISS_CHAR ),
571 NVL( ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR ),
572 NVL( ATTRIBUTE1, FND_API.G_MISS_CHAR ),
573 NVL( ATTRIBUTE2, FND_API.G_MISS_CHAR ),
574 NVL( ATTRIBUTE3, FND_API.G_MISS_CHAR ),
575 NVL( ATTRIBUTE4, FND_API.G_MISS_CHAR ),
576 NVL( ATTRIBUTE5, FND_API.G_MISS_CHAR ),
577 NVL( ATTRIBUTE6, FND_API.G_MISS_CHAR ),
578 NVL( ATTRIBUTE7, FND_API.G_MISS_CHAR ),
579 NVL( ATTRIBUTE8, FND_API.G_MISS_CHAR ),
580 NVL( ATTRIBUTE9, FND_API.G_MISS_CHAR ),
581 NVL( ATTRIBUTE10, FND_API.G_MISS_CHAR ),
582 NVL( ATTRIBUTE11, FND_API.G_MISS_CHAR ),
583 NVL( ATTRIBUTE12, FND_API.G_MISS_CHAR ),
584 NVL( ATTRIBUTE13, FND_API.G_MISS_CHAR ),
585 NVL( ATTRIBUTE14, FND_API.G_MISS_CHAR ),
586 NVL( ATTRIBUTE15, FND_API.G_MISS_CHAR ),
587 NVL( ATTRIBUTE16, FND_API.G_MISS_CHAR ),
588 NVL( ATTRIBUTE17, FND_API.G_MISS_CHAR ),
589 NVL( ATTRIBUTE18, FND_API.G_MISS_CHAR ),
590 NVL( ATTRIBUTE19, FND_API.G_MISS_CHAR ),
591 NVL( ATTRIBUTE20, FND_API.G_MISS_CHAR ),
592 NVL( ORIG_SYSTEM_REFERENCE, FND_API.G_MISS_CHAR ),
593 NVL( LANGUAGE, FND_API.G_MISS_CHAR ),
594 NVL( MAILSTOP, FND_API.G_MISS_CHAR ),
595 NVL( IDENTIFYING_ADDRESS_FLAG, FND_API.G_MISS_CHAR ),
596 NVL( STATUS, FND_API.G_MISS_CHAR ),
597 NVL( PARTY_SITE_NAME, FND_API.G_MISS_CHAR ),
598 NVL( ADDRESSEE, FND_API.G_MISS_CHAR ),
599 NVL( CREATED_BY_MODULE, FND_API.G_MISS_CHAR ),
600 NVL( APPLICATION_ID, FND_API.G_MISS_NUM ),
601 NVL( ACTUAL_CONTENT_SOURCE, FND_API.G_MISS_CHAR ),
602 NVL( GLOBAL_LOCATION_NUMBER, FND_API.G_MISS_CHAR ),
603 NVL( DUNS_NUMBER_C, FND_API.G_MISS_CHAR )
604 INTO
605 X_PARTY_SITE_ID,
606 X_PARTY_ID,
607 X_LOCATION_ID,
608 X_PARTY_SITE_NUMBER,
609 X_ATTRIBUTE_CATEGORY,
610 X_ATTRIBUTE1,
611 X_ATTRIBUTE2,
612 X_ATTRIBUTE3,
613 X_ATTRIBUTE4,
614 X_ATTRIBUTE5,
615 X_ATTRIBUTE6,
616 X_ATTRIBUTE7,
617 X_ATTRIBUTE8,
618 X_ATTRIBUTE9,
619 X_ATTRIBUTE10,
620 X_ATTRIBUTE11,
621 X_ATTRIBUTE12,
622 X_ATTRIBUTE13,
623 X_ATTRIBUTE14,
624 X_ATTRIBUTE15,
625 X_ATTRIBUTE16,
626 X_ATTRIBUTE17,
627 X_ATTRIBUTE18,
628 X_ATTRIBUTE19,
629 X_ATTRIBUTE20,
630 X_ORIG_SYSTEM_REFERENCE,
631 X_LANGUAGE,
632 X_MAILSTOP,
633 X_IDENTIFYING_ADDRESS_FLAG,
634 X_STATUS,
635 X_PARTY_SITE_NAME,
636 X_ADDRESSEE,
637 X_CREATED_BY_MODULE,
638 X_APPLICATION_ID,
639 X_ACTUAL_CONTENT_SOURCE,
640 X_GLOBAL_LOCATION_NUMBER,
641 X_DUNS_NUMBER_C
642 FROM HZ_PARTY_SITES
643 WHERE PARTY_SITE_ID = X_PARTY_SITE_ID;
644
645 EXCEPTION
646 WHEN NO_DATA_FOUND THEN
647 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
648 FND_MESSAGE.SET_TOKEN( 'RECORD', 'party_site_rec');
649 FND_MESSAGE.SET_TOKEN( 'VALUE', TO_CHAR( X_PARTY_SITE_ID ) );
650 FND_MSG_PUB.ADD;
651 RAISE FND_API.G_EXC_ERROR;
652
653 END Select_Row;
654
655 PROCEDURE Delete_Row (
656 X_PARTY_SITE_ID IN NUMBER
657 ) IS
658
659 BEGIN
660
661 DELETE FROM HZ_PARTY_SITES
662 WHERE PARTY_SITE_ID = X_PARTY_SITE_ID;
663
664 IF ( SQL%NOTFOUND ) THEN
665 RAISE NO_DATA_FOUND;
666 END IF;
667
668 END Delete_Row;
669
670 END HZ_PARTY_SITES_PKG;