[Home] [Help]
PACKAGE BODY: APPS.HZ_ORIG_SYSTEM_REF_PKG
Source
1 PACKAGE BODY HZ_ORIG_SYSTEM_REF_PKG AS
2 /*$Header: ARHOSRTB.pls 120.6 2006/12/14 22:16:27 awu noship $ */
3
4 PROCEDURE Insert_Row (
5 x_orig_system_ref_id IN OUT NOCOPY NUMBER,
6 x_orig_system IN VARCHAR2,
7 x_orig_system_reference IN VARCHAR2,
8 x_owner_table_name IN VARCHAR2,
9 x_owner_table_id IN NUMBER,
10 --raji
11 x_party_id IN NUMBER,
12 x_status IN VARCHAR2,
13 x_reason_code IN VARCHAR2,
14 x_old_orig_system_reference IN VARCHAR2,
15 x_start_date_active IN DATE,
16 x_end_date_active IN DATE,
17 x_object_version_number IN NUMBER,
18 x_created_by_module IN VARCHAR2,
19 x_application_id IN NUMBER,
20 x_attribute_category IN VARCHAR2,
21 x_attribute1 IN VARCHAR2,
22 x_attribute2 IN VARCHAR2,
23 x_attribute3 IN VARCHAR2,
24 x_attribute4 IN VARCHAR2,
25 x_attribute5 IN VARCHAR2,
26 x_attribute6 IN VARCHAR2,
27 x_attribute7 IN VARCHAR2,
28 x_attribute8 IN VARCHAR2,
29 x_attribute9 IN VARCHAR2,
30 x_attribute10 IN VARCHAR2,
31 x_attribute11 IN VARCHAR2,
32 x_attribute12 IN VARCHAR2,
33 x_attribute13 IN VARCHAR2,
34 x_attribute14 IN VARCHAR2,
35 x_attribute15 IN VARCHAR2,
36 x_attribute16 IN VARCHAR2,
37 x_attribute17 IN VARCHAR2,
38 x_attribute18 IN VARCHAR2,
39 x_attribute19 IN VARCHAR2,
40 x_attribute20 IN VARCHAR2
41 ) IS
42
43 BEGIN
44
45 IF x_orig_system_ref_id = FND_API.G_MISS_NUM THEN
46 x_orig_system_ref_id := NULL;
47 END IF;
48
49 INSERT INTO HZ_ORIG_SYS_REFERENCES (
50 orig_system_ref_id,
51 orig_system,
52 orig_system_reference,
53 owner_table_name,
54 owner_table_id,
55 --raji
56 party_id,
57 status,
58 reason_code,
59 old_orig_system_reference,
60 start_date_active,
61 end_date_active,
62 created_by,
63 creation_date,
64 last_updated_by,
65 last_update_date,
66 last_update_login,
67 object_version_number,
68 created_by_module,
69 application_id,
70 attribute_category,
71 attribute1,
72 attribute2,
73 attribute3,
74 attribute4,
75 attribute5,
76 attribute6,
77 attribute7,
78 attribute8,
79 attribute9,
80 attribute10,
81 attribute11,
82 attribute12,
83 attribute13,
84 attribute14,
85 attribute15,
86 attribute16,
87 attribute17,
88 attribute18,
89 attribute19,
90 attribute20
91 )
92 VALUES (
93 DECODE(x_orig_system_ref_id,
94 FND_API.G_MISS_NUM, HZ_ORIG_SYSTEM_REF_S.NEXTVAL,
95 NULL, HZ_ORIG_SYSTEM_REF_S.NEXTVAL,
96 x_orig_system_ref_id),
97 DECODE(x_orig_system,
98 FND_API.G_MISS_CHAR, NULL,
99 x_orig_system),
100 DECODE(x_orig_system_reference,
101 FND_API.G_MISS_CHAR, NULL,
102 x_orig_system_reference),
103 DECODE(x_owner_table_name,
104 FND_API.G_MISS_CHAR, NULL,
105 x_owner_table_name),
106 DECODE(x_owner_table_id,
107 FND_API.G_MISS_NUM, NULL,
108 x_owner_table_id),
109 --raji
110 DECODE(x_party_id,
111 FND_API.G_MISS_NUM,NULL,
112 x_party_id),
113 DECODE(x_status,
114 FND_API.G_MISS_CHAR, 'A',
115 NULL, 'A',
116 x_status),
117 DECODE(x_reason_code,
118 FND_API.G_MISS_CHAR, NULL,
119 x_reason_code),
120 DECODE(x_old_orig_system_reference,
121 FND_API.G_MISS_CHAR, NULL,
122 x_old_orig_system_reference),
123 DECODE(x_start_date_active,
124 FND_API.G_MISS_DATE, SYSDATE,TO_DATE(NULL), SYSDATE,
125 x_start_date_active),
126 DECODE(x_end_date_active,
127 FND_API.G_MISS_DATE, TO_DATE(NULL),
128 x_end_date_active),
129 hz_utility_v2pub.created_by,
130 hz_utility_v2pub.creation_date,
131 hz_utility_v2pub.last_updated_by,
132 hz_utility_v2pub.last_update_date,
133 hz_utility_v2pub.last_update_login,
134 DECODE(x_object_version_number,
135 FND_API.G_MISS_NUM, NULL,
136 x_object_version_number),
137 DECODE(x_created_by_module,
138 FND_API.G_MISS_CHAR, 'MOSR',NULL, 'MOSR',
139 x_created_by_module),
140 hz_utility_v2pub.application_id,
141 DECODE(x_attribute_category,
142 FND_API.G_MISS_CHAR, NULL,
143 x_attribute_category),
144 DECODE(x_attribute1,
145 FND_API.G_MISS_CHAR, NULL,
146 x_attribute1),
147 DECODE(x_attribute2,
148 FND_API.G_MISS_CHAR, NULL,
149 x_attribute2),
150 DECODE(x_attribute3,
151 FND_API.G_MISS_CHAR, NULL,
152 x_attribute3),
153 DECODE(x_attribute4,
154 FND_API.G_MISS_CHAR, NULL,
155 x_attribute4),
156 DECODE(x_attribute5,
157 FND_API.G_MISS_CHAR, NULL,
158 x_attribute5),
159 DECODE(x_attribute6,
160 FND_API.G_MISS_CHAR, NULL,
161 x_attribute6),
162 DECODE(x_attribute7,
163 FND_API.G_MISS_CHAR, NULL,
164 x_attribute7),
165 DECODE(x_attribute8,
166 FND_API.G_MISS_CHAR, NULL,
167 x_attribute8),
168 DECODE(x_attribute9,
169 FND_API.G_MISS_CHAR, NULL,
170 x_attribute9),
171 DECODE(x_attribute10,
172 FND_API.G_MISS_CHAR, NULL,
173 x_attribute10),
174 DECODE(x_attribute11,
175 FND_API.G_MISS_CHAR, NULL,
176 x_attribute11),
177 DECODE(x_attribute12,
178 FND_API.G_MISS_CHAR, NULL,
179 x_attribute12),
180 DECODE(x_attribute13,
181 FND_API.G_MISS_CHAR, NULL,
182 x_attribute13),
183 DECODE(x_attribute14,
184 FND_API.G_MISS_CHAR, NULL,
185 x_attribute14),
186 DECODE(x_attribute15,
187 FND_API.G_MISS_CHAR, NULL,
188 x_attribute15),
189 DECODE(x_attribute16,
190 FND_API.G_MISS_CHAR, NULL,
191 x_attribute16),
192 DECODE(x_attribute17,
193 FND_API.G_MISS_CHAR, NULL,
194 x_attribute17),
195 DECODE(x_attribute18,
196 FND_API.G_MISS_CHAR, NULL,
197 x_attribute18),
198 DECODE(x_attribute19,
199 FND_API.G_MISS_CHAR, NULL,
200 x_attribute19),
201 DECODE(x_attribute20,
202 FND_API.G_MISS_CHAR, NULL,
203 x_attribute20)
204 ) returning orig_system_ref_id into x_orig_system_ref_id;
205
206 END Insert_Row;
207
208 PROCEDURE Update_Row (
209 x_orig_system_ref_id IN NUMBER,
210 x_orig_system IN VARCHAR2,
211 x_orig_system_reference IN VARCHAR2,
212 x_owner_table_name IN VARCHAR2,
213 x_owner_table_id IN NUMBER,
214 --raji
215 x_party_id IN NUMBER,
216 x_status IN VARCHAR2,
217 x_reason_code IN VARCHAR2,
218 x_old_orig_system_reference IN VARCHAR2,
219 x_start_date_active IN DATE,
220 x_end_date_active IN DATE,
221 x_object_version_number IN NUMBER,
222 x_created_by_module IN VARCHAR2,
223 x_application_id IN NUMBER,
224 x_attribute_category IN VARCHAR2,
225 x_attribute1 IN VARCHAR2,
226 x_attribute2 IN VARCHAR2,
227 x_attribute3 IN VARCHAR2,
228 x_attribute4 IN VARCHAR2,
229 x_attribute5 IN VARCHAR2,
230 x_attribute6 IN VARCHAR2,
231 x_attribute7 IN VARCHAR2,
232 x_attribute8 IN VARCHAR2,
233 x_attribute9 IN VARCHAR2,
234 x_attribute10 IN VARCHAR2,
235 x_attribute11 IN VARCHAR2,
236 x_attribute12 IN VARCHAR2,
237 x_attribute13 IN VARCHAR2,
238 x_attribute14 IN VARCHAR2,
239 x_attribute15 IN VARCHAR2,
240 x_attribute16 IN VARCHAR2,
241 x_attribute17 IN VARCHAR2,
242 x_attribute18 IN VARCHAR2,
243 x_attribute19 IN VARCHAR2,
244 x_attribute20 IN VARCHAR2
245 ) IS
246 BEGIN
247
248 UPDATE HZ_ORIG_SYS_REFERENCES
249 SET
250 orig_system_ref_id =
251 DECODE(x_orig_system_ref_id,
252 NULL, orig_system_ref_id,
253 FND_API.G_MISS_NUM, NULL,
254 x_orig_system_ref_id),
255 orig_system =
256 DECODE(x_orig_system,
257 NULL, orig_system,
258 FND_API.G_MISS_CHAR, NULL,
259 x_orig_system),
260 orig_system_reference =
261 DECODE(x_orig_system_reference,
262 NULL, orig_system_reference,
263 FND_API.G_MISS_CHAR, NULL,
264 x_orig_system_reference),
265 owner_table_name =
266 DECODE(x_owner_table_name,
267 NULL, owner_table_name,
268 FND_API.G_MISS_CHAR, NULL,
269 x_owner_table_name),
270 owner_table_id =
271 DECODE(x_owner_table_id,
272 NULL, owner_table_id,
273 FND_API.G_MISS_NUM, NULL,
274 x_owner_table_id),
275 --raji
276 party_id =
277 DECODE(x_party_id,
278 NULL,party_id,
279 FND_API.G_MISS_NUM, NULL,
280 x_party_id),
281 status =
282 DECODE(x_status,
283 NULL, status,
284 FND_API.G_MISS_CHAR, NULL,
285 x_status),
286 reason_code =
287 DECODE(x_reason_code,
288 NULL, reason_code,
289 FND_API.G_MISS_CHAR, NULL,
290 x_reason_code),
291 old_orig_system_reference =
292 DECODE(x_old_orig_system_reference,
293 NULL, old_orig_system_reference,
294 FND_API.G_MISS_CHAR, NULL,
295 x_old_orig_system_reference),
296 start_date_active =
297 DECODE(x_start_date_active,
298 NULL, start_date_active,
299 FND_API.G_MISS_DATE, NULL,
300 x_start_date_active),
301 end_date_active =
302 DECODE(x_end_date_active,
303 NULL, end_date_active,
304 FND_API.G_MISS_DATE, NULL,
305 x_end_date_active),
306 created_by = created_by,
307 creation_date = creation_date,
308 last_updated_by = hz_utility_v2pub.last_updated_by,
309 last_update_date = hz_utility_v2pub.last_update_date,
310 last_update_login = hz_utility_v2pub.last_update_login,
311 object_version_number =
312 DECODE(x_object_version_number,
313 NULL, object_version_number,
314 FND_API.G_MISS_NUM, NULL,
315 x_object_version_number),
316 created_by_module =
317 DECODE(x_created_by_module,
318 NULL, created_by_module,
319 FND_API.G_MISS_CHAR, NULL,
320 x_created_by_module),
321 application_id = hz_utility_v2pub.application_id,
322 attribute_category =
323 DECODE(x_attribute_category,
324 NULL, attribute_category,
325 FND_API.G_MISS_CHAR, NULL,
329 NULL, attribute1,
326 x_attribute_category),
327 attribute1 =
328 DECODE(x_attribute1,
330 FND_API.G_MISS_CHAR, NULL,
331 x_attribute1),
332 attribute2 =
333 DECODE(x_attribute2,
334 NULL, attribute2,
335 FND_API.G_MISS_CHAR, NULL,
336 x_attribute2),
337 attribute3 =
338 DECODE(x_attribute3,
339 NULL, attribute3,
340 FND_API.G_MISS_CHAR, NULL,
341 x_attribute3),
342 attribute4 =
343 DECODE(x_attribute4,
344 NULL, attribute4,
345 FND_API.G_MISS_CHAR, NULL,
346 x_attribute4),
347 attribute5 =
348 DECODE(x_attribute5,
349 NULL, attribute5,
350 FND_API.G_MISS_CHAR, NULL,
351 x_attribute5),
352 attribute6 =
353 DECODE(x_attribute6,
354 NULL, attribute6,
355 FND_API.G_MISS_CHAR, NULL,
356 x_attribute6),
357 attribute7 =
358 DECODE(x_attribute7,
359 NULL, attribute7,
360 FND_API.G_MISS_CHAR, NULL,
361 x_attribute7),
362 attribute8 =
363 DECODE(x_attribute8,
364 NULL, attribute8,
365 FND_API.G_MISS_CHAR, NULL,
366 x_attribute8),
367 attribute9 =
368 DECODE(x_attribute9,
369 NULL, attribute9,
370 FND_API.G_MISS_CHAR, NULL,
371 x_attribute9),
372 attribute10 =
373 DECODE(x_attribute10,
374 NULL, attribute10,
375 FND_API.G_MISS_CHAR, NULL,
376 x_attribute10),
377 attribute11 =
378 DECODE(x_attribute11,
379 NULL, attribute11,
380 FND_API.G_MISS_CHAR, NULL,
381 x_attribute11),
382 attribute12 =
383 DECODE(x_attribute12,
384 NULL, attribute12,
385 FND_API.G_MISS_CHAR, NULL,
386 x_attribute12),
387 attribute13 =
388 DECODE(x_attribute13,
389 NULL, attribute13,
390 FND_API.G_MISS_CHAR, NULL,
391 x_attribute13),
392 attribute14 =
393 DECODE(x_attribute14,
394 NULL, attribute14,
395 FND_API.G_MISS_CHAR, NULL,
396 x_attribute14),
397 attribute15 =
398 DECODE(x_attribute15,
399 NULL, attribute15,
400 FND_API.G_MISS_CHAR, NULL,
401 x_attribute15),
402 attribute16 =
403 DECODE(x_attribute16,
404 NULL, attribute16,
405 FND_API.G_MISS_CHAR, NULL,
406 x_attribute16),
407 attribute17 =
408 DECODE(x_attribute17,
409 NULL, attribute17,
410 FND_API.G_MISS_CHAR, NULL,
411 x_attribute17),
412 attribute18 =
413 DECODE(x_attribute18,
414 NULL, attribute18,
415 FND_API.G_MISS_CHAR, NULL,
416 x_attribute18),
417 attribute19 =
418 DECODE(x_attribute19,
419 NULL, attribute19,
420 FND_API.G_MISS_CHAR, NULL,
421 x_attribute19),
422 attribute20 =
423 DECODE(x_attribute20,
424 NULL, attribute20,
425 FND_API.G_MISS_CHAR, NULL,
426 x_attribute20)
427 WHERE ORIG_SYSTEM_REF_ID = X_ORIG_SYSTEM_REF_ID;
428
429 IF ( SQL%NOTFOUND ) THEN
430 RAISE NO_DATA_FOUND;
431 END IF;
432
433 END Update_Row;
434
435 PROCEDURE Lock_Row (
436 x_rowid IN OUT NOCOPY VARCHAR2,
437 x_orig_system_ref_id IN NUMBER,
438 x_orig_system IN VARCHAR2,
439 x_orig_system_reference IN VARCHAR2,
440 x_owner_table_name IN VARCHAR2,
441 x_owner_table_id IN NUMBER,
442 --raji
443 x_party_id IN NUMBER,
444 x_status IN VARCHAR2,
445 x_reason_code IN VARCHAR2,
446 x_old_orig_system_reference IN VARCHAR2,
447 x_start_date_active IN DATE,
448 x_end_date_active IN DATE,
449 x_created_by IN NUMBER,
450 x_creation_date IN DATE,
451 x_last_updated_by IN NUMBER,
452 x_last_update_date IN DATE,
453 x_last_update_login IN NUMBER,
454 x_object_version_number IN NUMBER,
455 x_created_by_module IN VARCHAR2,
456 x_application_id IN NUMBER,
457 x_attribute_category IN VARCHAR2,
458 x_attribute1 IN VARCHAR2,
459 x_attribute2 IN VARCHAR2,
460 x_attribute3 IN VARCHAR2,
461 x_attribute4 IN VARCHAR2,
462 x_attribute5 IN VARCHAR2,
463 x_attribute6 IN VARCHAR2,
464 x_attribute7 IN VARCHAR2,
465 x_attribute8 IN VARCHAR2,
466 x_attribute9 IN VARCHAR2,
470 x_attribute13 IN VARCHAR2,
467 x_attribute10 IN VARCHAR2,
468 x_attribute11 IN VARCHAR2,
469 x_attribute12 IN VARCHAR2,
471 x_attribute14 IN VARCHAR2,
472 x_attribute15 IN VARCHAR2,
473 x_attribute16 IN VARCHAR2,
474 x_attribute17 IN VARCHAR2,
475 x_attribute18 IN VARCHAR2,
476 x_attribute19 IN VARCHAR2,
477 x_attribute20 IN VARCHAR2
478 ) IS
479
480 CURSOR c IS
481 SELECT * FROM HZ_ORIG_SYS_REFERENCES
482 WHERE rowid = x_rowid
483 FOR UPDATE NOWAIT;
484 Recinfo c%ROWTYPE;
485
486 BEGIN
487
488 OPEN c;
489 FETCH c INTO Recinfo;
490 IF ( c%NOTFOUND ) THEN
491 CLOSE c;
492 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
493 APP_EXCEPTION.RAISE_EXCEPTION;
494 END IF;
495 CLOSE C;
496
497 IF (
498 ( ( Recinfo.orig_system_ref_id = x_orig_system_ref_id )
499 OR ( ( Recinfo.orig_system_ref_id IS NULL )
500 AND ( x_orig_system_ref_id IS NULL ) ) )
501 AND ( ( Recinfo.orig_system = x_orig_system )
502 OR ( ( Recinfo.orig_system IS NULL )
503 AND ( x_orig_system IS NULL ) ) )
504 AND ( ( Recinfo.orig_system_reference = x_orig_system_reference )
505 OR ( ( Recinfo.orig_system_reference IS NULL )
506 AND ( x_orig_system_reference IS NULL ) ) )
507 AND ( ( Recinfo.owner_table_name = x_owner_table_name )
508 OR ( ( Recinfo.owner_table_name IS NULL )
509 AND ( x_owner_table_name IS NULL ) ) )
510 AND ( ( Recinfo.owner_table_id = x_owner_table_id )
511 OR ( ( Recinfo.owner_table_id IS NULL )
512 AND ( x_owner_table_id IS NULL ) ) )
513 --raji
514 AND ( ( Recinfo.party_id = x_party_id )
515 OR ( ( Recinfo.party_id IS NULL )
516 AND ( x_party_id IS NULL ) ) )
517 AND ( ( Recinfo.status = x_status )
518 OR ( ( Recinfo.status IS NULL )
519 AND ( x_status IS NULL ) ) )
520 AND ( ( Recinfo.reason_code = x_reason_code )
521 OR ( ( Recinfo.reason_code IS NULL )
522 AND ( x_reason_code IS NULL ) ) )
523 AND ( ( Recinfo.old_orig_system_reference = x_old_orig_system_reference )
524 OR ( ( Recinfo.old_orig_system_reference IS NULL )
525 AND ( x_old_orig_system_reference IS NULL ) ) )
526 AND ( ( Recinfo.start_date_active = x_start_date_active )
527 OR ( ( Recinfo.start_date_active IS NULL )
528 AND ( x_start_date_active IS NULL ) ) )
529 AND ( ( Recinfo.end_date_active = x_end_date_active )
530 OR ( ( Recinfo.end_date_active IS NULL )
531 AND ( x_end_date_active IS NULL ) ) )
532 AND ( ( Recinfo.created_by = x_created_by )
533 OR ( ( Recinfo.created_by IS NULL )
534 AND ( x_created_by IS NULL ) ) )
535 AND ( ( Recinfo.creation_date = x_creation_date )
536 OR ( ( Recinfo.creation_date IS NULL )
537 AND ( x_creation_date IS NULL ) ) )
538 AND ( ( Recinfo.last_updated_by = x_last_updated_by )
539 OR ( ( Recinfo.last_updated_by IS NULL )
540 AND ( x_last_updated_by IS NULL ) ) )
541 AND ( ( Recinfo.last_update_date = x_last_update_date )
542 OR ( ( Recinfo.last_update_date IS NULL )
543 AND ( x_last_update_date IS NULL ) ) )
544 AND ( ( Recinfo.last_update_login = x_last_update_login )
545 OR ( ( Recinfo.last_update_login IS NULL )
546 AND ( x_last_update_login IS NULL ) ) )
547 AND ( ( Recinfo.object_version_number = x_object_version_number )
548 OR ( ( Recinfo.object_version_number IS NULL )
549 AND ( x_object_version_number IS NULL ) ) )
550 AND ( ( Recinfo.created_by_module = x_created_by_module )
551 OR ( ( Recinfo.created_by_module IS NULL )
552 AND ( x_created_by_module IS NULL ) ) )
553 AND ( ( Recinfo.application_id = x_application_id )
554 OR ( ( Recinfo.application_id IS NULL )
555 AND ( x_application_id IS NULL ) ) )
556 AND ( ( Recinfo.attribute_category = x_attribute_category )
557 OR ( ( Recinfo.attribute_category IS NULL )
558 AND ( x_attribute_category IS NULL ) ) )
559 AND ( ( Recinfo.attribute1 = x_attribute1 )
560 OR ( ( Recinfo.attribute1 IS NULL )
561 AND ( x_attribute1 IS NULL ) ) )
562 AND ( ( Recinfo.attribute2 = x_attribute2 )
563 OR ( ( Recinfo.attribute2 IS NULL )
564 AND ( x_attribute2 IS NULL ) ) )
565 AND ( ( Recinfo.attribute3 = x_attribute3 )
566 OR ( ( Recinfo.attribute3 IS NULL )
567 AND ( x_attribute3 IS NULL ) ) )
568 AND ( ( Recinfo.attribute4 = x_attribute4 )
569 OR ( ( Recinfo.attribute4 IS NULL )
570 AND ( x_attribute4 IS NULL ) ) )
571 AND ( ( Recinfo.attribute5 = x_attribute5 )
572 OR ( ( Recinfo.attribute5 IS NULL )
573 AND ( x_attribute5 IS NULL ) ) )
574 AND ( ( Recinfo.attribute6 = x_attribute6 )
575 OR ( ( Recinfo.attribute6 IS NULL )
576 AND ( x_attribute6 IS NULL ) ) )
577 AND ( ( Recinfo.attribute7 = x_attribute7 )
578 OR ( ( Recinfo.attribute7 IS NULL )
579 AND ( x_attribute7 IS NULL ) ) )
580 AND ( ( Recinfo.attribute8 = x_attribute8 )
581 OR ( ( Recinfo.attribute8 IS NULL )
582 AND ( x_attribute8 IS NULL ) ) )
583 AND ( ( Recinfo.attribute9 = x_attribute9 )
584 OR ( ( Recinfo.attribute9 IS NULL )
585 AND ( x_attribute9 IS NULL ) ) )
586 AND ( ( Recinfo.attribute10 = x_attribute10 )
590 OR ( ( Recinfo.attribute11 IS NULL )
587 OR ( ( Recinfo.attribute10 IS NULL )
588 AND ( x_attribute10 IS NULL ) ) )
589 AND ( ( Recinfo.attribute11 = x_attribute11 )
591 AND ( x_attribute11 IS NULL ) ) )
592 AND ( ( Recinfo.attribute12 = x_attribute12 )
593 OR ( ( Recinfo.attribute12 IS NULL )
594 AND ( x_attribute12 IS NULL ) ) )
595 AND ( ( Recinfo.attribute13 = x_attribute13 )
596 OR ( ( Recinfo.attribute13 IS NULL )
597 AND ( x_attribute13 IS NULL ) ) )
598 AND ( ( Recinfo.attribute14 = x_attribute14 )
599 OR ( ( Recinfo.attribute14 IS NULL )
600 AND ( x_attribute14 IS NULL ) ) )
601 AND ( ( Recinfo.attribute15 = x_attribute15 )
602 OR ( ( Recinfo.attribute15 IS NULL )
603 AND ( x_attribute15 IS NULL ) ) )
604 AND ( ( Recinfo.attribute16 = x_attribute16 )
605 OR ( ( Recinfo.attribute16 IS NULL )
606 AND ( x_attribute16 IS NULL ) ) )
607 AND ( ( Recinfo.attribute17 = x_attribute17 )
608 OR ( ( Recinfo.attribute17 IS NULL )
609 AND ( x_attribute17 IS NULL ) ) )
610 AND ( ( Recinfo.attribute18 = x_attribute18 )
611 OR ( ( Recinfo.attribute18 IS NULL )
612 AND ( x_attribute18 IS NULL ) ) )
613 AND ( ( Recinfo.attribute19 = x_attribute19 )
614 OR ( ( Recinfo.attribute19 IS NULL )
615 AND ( x_attribute19 IS NULL ) ) )
616 AND ( ( Recinfo.attribute20 = x_attribute20 )
617 OR ( ( Recinfo.attribute20 IS NULL )
618 AND ( x_attribute20 IS NULL ) ) )
619 ) THEN
620 RETURN;
621 ELSE
622 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
623 APP_EXCEPTION.RAISE_EXCEPTION;
624 END IF;
625
626 END Lock_Row;
627
628 PROCEDURE Select_Row (
629 x_orig_system_ref_id IN OUT NOCOPY NUMBER,
630 x_orig_system OUT NOCOPY VARCHAR2,
631 x_orig_system_reference OUT NOCOPY VARCHAR2,
632 x_owner_table_name OUT NOCOPY VARCHAR2,
633 x_owner_table_id OUT NOCOPY NUMBER,
634 --raji
635 x_party_id OUT NOCOPY NUMBER,
636 x_status OUT NOCOPY VARCHAR2,
637 x_reason_code OUT NOCOPY VARCHAR2,
638 x_old_orig_system_reference OUT NOCOPY VARCHAR2,
639 x_start_date_active OUT NOCOPY DATE,
640 x_end_date_active OUT NOCOPY DATE,
641 x_object_version_number OUT NOCOPY NUMBER,
642 x_created_by_module OUT NOCOPY VARCHAR2,
643 x_application_id OUT NOCOPY NUMBER,
644 x_attribute_category OUT NOCOPY VARCHAR2,
645 x_attribute1 OUT NOCOPY VARCHAR2,
646 x_attribute2 OUT NOCOPY VARCHAR2,
647 x_attribute3 OUT NOCOPY VARCHAR2,
648 x_attribute4 OUT NOCOPY VARCHAR2,
649 x_attribute5 OUT NOCOPY VARCHAR2,
650 x_attribute6 OUT NOCOPY VARCHAR2,
651 x_attribute7 OUT NOCOPY VARCHAR2,
652 x_attribute8 OUT NOCOPY VARCHAR2,
653 x_attribute9 OUT NOCOPY VARCHAR2,
654 x_attribute10 OUT NOCOPY VARCHAR2,
655 x_attribute11 OUT NOCOPY VARCHAR2,
656 x_attribute12 OUT NOCOPY VARCHAR2,
657 x_attribute13 OUT NOCOPY VARCHAR2,
658 x_attribute14 OUT NOCOPY VARCHAR2,
659 x_attribute15 OUT NOCOPY VARCHAR2,
660 x_attribute16 OUT NOCOPY VARCHAR2,
661 x_attribute17 OUT NOCOPY VARCHAR2,
662 x_attribute18 OUT NOCOPY VARCHAR2,
663 x_attribute19 OUT NOCOPY VARCHAR2,
664 x_attribute20 OUT NOCOPY VARCHAR2
665 ) IS
666 BEGIN
667
668 SELECT
669 NVL(orig_system_ref_id, FND_API.G_MISS_NUM),
670 NVL(orig_system, FND_API.G_MISS_CHAR),
671 NVL(orig_system_reference, FND_API.G_MISS_CHAR),
672 NVL(owner_table_name, FND_API.G_MISS_CHAR),
673 NVL(owner_table_id, FND_API.G_MISS_NUM),
674 --raji
675 NVL(party_id,FND_API.G_MISS_NUM),
676 NVL(status, FND_API.G_MISS_CHAR),
677 NVL(reason_code, FND_API.G_MISS_CHAR),
678 NVL(old_orig_system_reference, FND_API.G_MISS_CHAR),
679 NVL(start_date_active, FND_API.G_MISS_DATE),
680 NVL(end_date_active, FND_API.G_MISS_DATE),
681 NVL(created_by_module, FND_API.G_MISS_CHAR),
682 NVL(application_id, FND_API.G_MISS_NUM),
683 NVL(attribute_category, FND_API.G_MISS_CHAR),
684 NVL(attribute1, FND_API.G_MISS_CHAR),
685 NVL(attribute2, FND_API.G_MISS_CHAR),
686 NVL(attribute3, FND_API.G_MISS_CHAR),
687 NVL(attribute4, FND_API.G_MISS_CHAR),
688 NVL(attribute5, FND_API.G_MISS_CHAR),
689 NVL(attribute6, FND_API.G_MISS_CHAR),
690 NVL(attribute7, FND_API.G_MISS_CHAR),
691 NVL(attribute8, FND_API.G_MISS_CHAR),
692 NVL(attribute9, FND_API.G_MISS_CHAR),
693 NVL(attribute10, FND_API.G_MISS_CHAR),
694 NVL(attribute11, FND_API.G_MISS_CHAR),
695 NVL(attribute12, FND_API.G_MISS_CHAR),
696 NVL(attribute13, FND_API.G_MISS_CHAR),
697 NVL(attribute14, FND_API.G_MISS_CHAR),
698 NVL(attribute15, FND_API.G_MISS_CHAR),
699 NVL(attribute16, FND_API.G_MISS_CHAR),
700 NVL(attribute17, FND_API.G_MISS_CHAR),
701 NVL(attribute18, FND_API.G_MISS_CHAR),
702 NVL(attribute19, FND_API.G_MISS_CHAR),
703 NVL(attribute20, FND_API.G_MISS_CHAR)
704 INTO
705 x_orig_system_ref_id,
706 x_orig_system,
707 x_orig_system_reference,
708 x_owner_table_name,
709 x_owner_table_id,
710 --raji
711 x_party_id,
712 x_status,
713 x_reason_code,
714 x_old_orig_system_reference,
715 x_start_date_active,
716 x_end_date_active,
717 x_created_by_module,
718 x_application_id,
719 x_attribute_category,
720 x_attribute1,
721 x_attribute2,
722 x_attribute3,
723 x_attribute4,
724 x_attribute5,
725 x_attribute6,
726 x_attribute7,
727 x_attribute8,
728 x_attribute9,
729 x_attribute10,
730 x_attribute11,
731 x_attribute12,
732 x_attribute13,
733 x_attribute14,
734 x_attribute15,
735 x_attribute16,
736 x_attribute17,
737 x_attribute18,
738 x_attribute19,
739 x_attribute20
740 FROM HZ_ORIG_SYS_REFERENCES
741 WHERE orig_system_ref_id = x_orig_system_ref_id;
742
743 EXCEPTION
744 WHEN NO_DATA_FOUND THEN
745 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
746 FND_MESSAGE.SET_TOKEN('RECORD', 'orig_sys_reference_rec');
747 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(x_orig_system_ref_id));
748 FND_MSG_PUB.ADD;
749 RAISE FND_API.G_EXC_ERROR;
750
751 END Select_Row;
752
753 PROCEDURE Delete_Row (
754 x_orig_system_ref_id IN NUMBER
755 ) IS
756 BEGIN
757
758 DELETE FROM HZ_ORIG_SYS_REFERENCES
759 WHERE orig_system_ref_id = x_orig_system_ref_id;
760
761 IF ( SQL%NOTFOUND ) THEN
762 RAISE NO_DATA_FOUND;
763 END IF;
764
765 END Delete_Row;
766
767 END HZ_ORIG_SYSTEM_REF_PKG;