[Home] [Help]
PACKAGE BODY: APPS.HZ_CONTACT_POINTS_PKG
Source
1 PACKAGE BODY hz_contact_points_pkg AS
2 /*$Header: ARHCPTTB.pls 120.5 2006/05/05 09:19:20 pkasturi ship $ */
3
4 G_MISS_CONTENT_SOURCE_TYPE CONSTANT VARCHAR2(30) := 'USER_ENTERED';
5
6 PROCEDURE insert_row (
7 x_contact_point_id IN OUT NOCOPY NUMBER,
8 x_contact_point_type IN VARCHAR2,
9 x_status IN VARCHAR2,
10 x_owner_table_name IN VARCHAR2,
11 x_owner_table_id IN NUMBER,
12 x_primary_flag IN VARCHAR2,
13 x_orig_system_reference IN VARCHAR2,
14 x_attribute_category IN VARCHAR2,
15 x_attribute1 IN VARCHAR2,
16 x_attribute2 IN VARCHAR2,
17 x_attribute3 IN VARCHAR2,
18 x_attribute4 IN VARCHAR2,
19 x_attribute5 IN VARCHAR2,
20 x_attribute6 IN VARCHAR2,
21 x_attribute7 IN VARCHAR2,
22 x_attribute8 IN VARCHAR2,
23 x_attribute9 IN VARCHAR2,
24 x_attribute10 IN VARCHAR2,
25 x_attribute11 IN VARCHAR2,
26 x_attribute12 IN VARCHAR2,
27 x_attribute13 IN VARCHAR2,
28 x_attribute14 IN VARCHAR2,
29 x_attribute15 IN VARCHAR2,
30 x_attribute16 IN VARCHAR2,
31 x_attribute17 IN VARCHAR2,
32 x_attribute18 IN VARCHAR2,
33 x_attribute19 IN VARCHAR2,
34 x_attribute20 IN VARCHAR2,
35 x_edi_transaction_handling IN VARCHAR2,
36 x_edi_id_number IN VARCHAR2,
37 x_edi_payment_method IN VARCHAR2,
38 x_edi_payment_format IN VARCHAR2,
39 x_edi_remittance_method IN VARCHAR2,
40 x_edi_remittance_instruction IN VARCHAR2,
41 x_edi_tp_header_id IN NUMBER,
42 x_edi_ece_tp_location_code IN VARCHAR2,
43 x_eft_transmission_program_id IN NUMBER,
44 x_eft_printing_program_id IN NUMBER,
45 x_eft_user_number IN VARCHAR2,
46 x_eft_swift_code IN VARCHAR2,
47 x_email_format IN VARCHAR2,
48 x_email_address IN VARCHAR2,
49 x_phone_calling_calendar IN VARCHAR2,
50 x_last_contact_dt_time IN DATE,
51 x_timezone_id IN NUMBER,
52 x_phone_area_code IN VARCHAR2,
53 x_phone_country_code IN VARCHAR2,
54 x_phone_number IN VARCHAR2,
55 x_phone_extension IN VARCHAR2,
56 x_phone_line_type IN VARCHAR2,
57 x_telex_number IN VARCHAR2,
58 x_web_type IN VARCHAR2,
59 x_url IN VARCHAR2,
60 x_content_source_type IN VARCHAR2,
61 x_raw_phone_number IN VARCHAR2,
62 x_object_version_number IN NUMBER,
63 x_contact_point_purpose IN VARCHAR2,
64 x_primary_by_purpose IN VARCHAR2,
65 x_created_by_module IN VARCHAR2,
66 x_application_id IN NUMBER,
67 x_transposed_phone_number IN VARCHAR2,
68 x_actual_content_source IN VARCHAR2
69 ) IS
70
71 l_success VARCHAR2(1) := 'N';
72
73 l_primary_key_passed BOOLEAN := FALSE;
74
75 BEGIN
76
77 -- The following lines are used to take care of the situation
78 -- when content_source_type IS not USER_ENTERED, because of
79 -- policy funcation, when we do unique validation, we cannot
80 -- see all of the records. Thus, we have to double check here
81 -- and raise corresponding exception. We donot need to do anything
82 -- for those tables without policy functions.
83
84 IF x_contact_point_id IS NOT NULL AND
85 x_contact_point_id <> fnd_api.g_miss_num
86 THEN
87 l_primary_key_passed := TRUE;
88 END IF;
89
90 IF x_contact_point_id = fnd_api.g_miss_num THEN
91 x_contact_point_id := NULL;
92 END IF;
93
94 WHILE l_success = 'N' LOOP
95 BEGIN
96 INSERT INTO HZ_CONTACT_POINTS (
97 contact_point_id,
98 contact_point_type,
99 status,
100 owner_table_name,
101 owner_table_id,
102 primary_flag,
103 orig_system_reference,
104 last_update_date,
105 last_updated_by,
106 creation_date,
107 created_by,
108 last_update_login,
109 request_id,
110 program_application_id,
111 program_id,
112 program_update_date,
113 attribute_category,
114 attribute1,
115 attribute2,
116 attribute3,
117 attribute4,
118 attribute5,
119 attribute6,
120 attribute7,
121 attribute8,
122 attribute9,
123 attribute10,
124 attribute11,
125 attribute12,
126 attribute13,
127 attribute14,
128 attribute15,
129 attribute16,
130 attribute17,
131 attribute18,
132 attribute19,
133 attribute20,
134 edi_transaction_handling,
135 edi_id_number,
136 edi_payment_method,
137 edi_payment_format,
138 edi_remittance_method,
139 edi_remittance_instruction,
140 edi_tp_header_id,
141 edi_ece_tp_location_code,
142 eft_transmission_program_id,
143 eft_printing_program_id,
144 eft_user_number,
145 eft_swift_code,
146 email_format,
147 email_address,
148 phone_calling_calendar,
149 last_contact_dt_time,
150 timezone_id,
151 phone_area_code,
152 phone_country_code,
153 phone_number,
154 phone_extension,
155 phone_line_type,
156 telex_number,
157 web_type,
158 url,
159 content_source_type,
160 raw_phone_number,
161 object_version_number,
162 contact_point_purpose,
163 primary_by_purpose,
164 created_by_module,
165 application_id,
166 transposed_phone_number,
167 ACTUAL_CONTENT_SOURCE
168 )
169 VALUES (
170 DECODE(x_contact_point_id,
171 fnd_api.g_miss_num, hz_contact_points_s.NEXTVAL,
172 NULL, hz_contact_points_s.nextval,
173 x_contact_point_id),
174 DECODE(x_contact_point_type,
175 fnd_api.g_miss_char, NULL, x_contact_point_type),
176 DECODE(x_status, fnd_api.g_miss_char, 'A', NULL, 'A', x_status),
177 DECODE(x_owner_table_name,
178 fnd_api.g_miss_char, NULL, x_owner_table_name),
179 DECODE(x_owner_table_id,
180 fnd_api.g_miss_num, NULL, x_owner_table_id),
181 DECODE(x_primary_flag,
182 fnd_api.g_miss_char, 'N', NULL, 'N', x_primary_flag),
183 DECODE(x_orig_system_reference,
184 fnd_api.g_miss_char, TO_CHAR(NVL(x_contact_point_id,
185 hz_contact_points_s.CURRVAL)),
186 NULL, TO_CHAR(NVL(x_contact_point_id,
187 hz_contact_points_s.CURRVAL)),
188 x_orig_system_reference),
189 hz_utility_v2pub.last_update_date,
190 hz_utility_v2pub.last_updated_by,
191 hz_utility_v2pub.creation_date,
192 hz_utility_v2pub.created_by,
193 hz_utility_v2pub.last_update_login,
194 hz_utility_v2pub.request_id,
195 hz_utility_v2pub.program_application_id,
196 hz_utility_v2pub.program_id,
197 hz_utility_v2pub.program_update_date,
198 DECODE(x_attribute_category,
199 fnd_api.g_miss_char, NULL, x_attribute_category),
200 DECODE(x_attribute1, fnd_api.g_miss_char, NULL, x_attribute1),
201 DECODE(x_attribute2, fnd_api.g_miss_char, NULL, x_attribute2),
202 DECODE(x_attribute3, fnd_api.g_miss_char, NULL, x_attribute3),
203 DECODE(x_attribute4, fnd_api.g_miss_char, NULL, x_attribute4),
204 DECODE(x_attribute5, fnd_api.g_miss_char, NULL, x_attribute5),
205 DECODE(x_attribute6, fnd_api.g_miss_char, NULL, x_attribute6),
206 DECODE(x_attribute7, fnd_api.g_miss_char, NULL, x_attribute7),
207 DECODE(x_attribute8, fnd_api.g_miss_char, NULL, x_attribute8),
208 DECODE(x_attribute9, fnd_api.g_miss_char, NULL, x_attribute9),
209 DECODE(x_attribute10, fnd_api.g_miss_char, NULL, x_attribute10),
210 DECODE(x_attribute11, fnd_api.g_miss_char, NULL, x_attribute11),
211 DECODE(x_attribute12, fnd_api.g_miss_char, NULL, x_attribute12),
212 DECODE(x_attribute13, fnd_api.g_miss_char, NULL, x_attribute13),
213 DECODE(x_attribute14, fnd_api.g_miss_char, NULL, x_attribute14),
214 DECODE(x_attribute15, fnd_api.g_miss_char, NULL, x_attribute15),
215 DECODE(x_attribute16, fnd_api.g_miss_char, NULL, x_attribute16),
216 DECODE(x_attribute17, fnd_api.g_miss_char, NULL, x_attribute17),
217 DECODE(x_attribute18, fnd_api.g_miss_char, NULL, x_attribute18),
218 DECODE(x_attribute19, fnd_api.g_miss_char, NULL, x_attribute19),
219 DECODE(x_attribute20, fnd_api.g_miss_char, NULL, x_attribute20),
220 DECODE(x_edi_transaction_handling,
221 fnd_api.g_miss_char, NULL, x_edi_transaction_handling),
222 DECODE(x_edi_id_number, fnd_api.g_miss_char, NULL, x_edi_id_number),
223 DECODE(x_edi_payment_method,
224 fnd_api.g_miss_char, NULL, x_edi_payment_method),
225 DECODE(x_edi_payment_format,
226 fnd_api.g_miss_char, NULL, x_edi_payment_format),
227 DECODE(x_edi_remittance_method,
228 fnd_api.g_miss_char, NULL, x_edi_remittance_method),
229 DECODE(x_edi_remittance_instruction,
230 fnd_api.g_miss_char, NULL, x_edi_remittance_instruction),
231 DECODE(x_edi_tp_header_id,
232 fnd_api.g_miss_num, NULL, x_edi_tp_header_id),
233 DECODE(x_edi_ece_tp_location_code,
234 fnd_api.g_miss_char, NULL, x_edi_ece_tp_location_code),
235 DECODE(x_eft_transmission_program_id,
236 fnd_api.g_miss_num, NULL, x_eft_transmission_program_id),
237 DECODE(x_eft_printing_program_id,
238 fnd_api.g_miss_num, NULL, x_eft_printing_program_id),
239 DECODE(x_eft_user_number,
240 fnd_api.g_miss_char, NULL, x_eft_user_number),
241 DECODE(x_eft_swift_code,
242 fnd_api.g_miss_char, NULL, x_eft_swift_code),
243 ---Bug No. 4359226
244 DECODE(x_email_format,
245 fnd_api.g_miss_char, DECODE(x_contact_point_type,
246 'EMAIL', 'MAILTEXT', NULL),
247 NULL, DECODE(x_contact_point_type, 'EMAIL', 'MAILTEXT', NULL),
248 x_email_format),
249 ----Bug No. 4359226
250 --Bug 4355133
251 SUBSTRB(DECODE(x_email_address, fnd_api.g_miss_char, NULL,
252 x_email_address),1,320),
253 DECODE(x_phone_calling_calendar,
254 fnd_api.g_miss_char, NULL, x_phone_calling_calendar),
255 DECODE(x_last_contact_dt_time,
256 fnd_api.g_miss_date, to_date(NULL), x_last_contact_dt_time),
257 DECODE(x_timezone_id, fnd_api.g_miss_num, NULL, x_timezone_id),
261 fnd_api.g_miss_char, NULL, x_phone_country_code),
258 DECODE(x_phone_area_code,
259 fnd_api.g_miss_char, NULL, x_phone_area_code),
260 DECODE(x_phone_country_code,
262 DECODE(x_phone_number, fnd_api.g_miss_char, NULL, x_phone_number),
263 DECODE(x_phone_extension,
264 fnd_api.g_miss_char, NULL, x_phone_extension),
265 DECODE(x_phone_line_type,
266 fnd_api.g_miss_char, NULL, x_phone_line_type),
267 DECODE(x_telex_number, fnd_api.g_miss_char, NULL, x_telex_number),
268 DECODE(x_web_type, fnd_api.g_miss_char, NULL, x_web_type),
269 DECODE(x_url, fnd_api.g_miss_char, NULL, x_url),
270 DECODE(x_content_source_type,
271 fnd_api.g_miss_char, g_miss_content_source_type,
272 NULL, g_miss_content_source_type,
273 x_content_source_type),
274 DECODE(x_raw_phone_number,
275 fnd_api.g_miss_char, NULL, x_raw_phone_number),
276 DECODE(x_object_version_number,
277 fnd_api.g_miss_num, NULL, x_object_version_number),
278 DECODE(x_contact_point_purpose,
279 fnd_api.g_miss_char, NULL, x_contact_point_purpose),
280 DECODE(x_primary_by_purpose,
281 fnd_api.g_miss_char, 'N', NULL, 'N', x_primary_by_purpose),
282 DECODE(x_created_by_module,
283 fnd_api.g_miss_char, NULL, x_created_by_module),
284 DECODE(x_application_id,
285 fnd_api.g_miss_num, NULL, x_application_id),
286 DECODE(x_transposed_phone_number,
287 fnd_api.g_miss_char, NULL, x_transposed_phone_number),
288 DECODE(x_actual_content_source,
289 fnd_api.g_miss_char, g_miss_content_source_type,
290 NULL, g_miss_content_source_type,
291 x_actual_content_source )
292 )
293 RETURNING contact_point_id INTO x_contact_point_id;
294
295 l_success := 'Y';
296
297 EXCEPTION
298 WHEN DUP_VAL_ON_INDEX THEN
299 IF INSTRB(SQLERRM, 'HZ_CONTACT_POINTS_U1') <> 0 OR
300 INSTRB(SQLERRM, 'HZ_CONTACT_POINTS_PK') <> 0
301 THEN
302 IF l_primary_key_passed THEN
303 fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
304 fnd_message.set_token('COLUMN', 'contact_point_id');
305 fnd_msg_pub.add;
306 RAISE fnd_api.g_exc_error;
307 END IF;
308
309 -- code to find a valid contact point ID.
310 DECLARE
311 l_count NUMBER;
312 l_dummy VARCHAR2(1);
313
314 CURSOR c_idseq IS
315 SELECT hz_contact_points_s.NEXTVAL
316 FROM dual;
317 CURSOR c_cpchk IS
318 SELECT 'Y'
319 FROM hz_contact_points
320 WHERE contact_point_id = x_contact_point_id;
321 BEGIN
322 l_count := 1;
323
324 WHILE l_count > 0 LOOP
325 OPEN c_idseq;
326 FETCH c_idseq INTO x_contact_point_id;
327
328 IF c_idseq%NOTFOUND THEN
329 CLOSE c_idseq;
330 RAISE NO_DATA_FOUND;
331 ELSE
332 CLOSE c_idseq;
333 END IF;
334
335 OPEN c_cpchk;
336 FETCH c_cpchk INTO l_dummy;
337
338 IF c_cpchk%NOTFOUND THEN
339 l_count := 0;
340 ELSE
341 l_count := 1;
342 END IF;
343
344 CLOSE c_cpchk;
345 END LOOP;
346 END;
347 ELSE
348 RAISE;
349 END IF;
350 END;
351 END LOOP;
352 END insert_row;
353
354 PROCEDURE update_row (
355 x_rowid IN OUT NOCOPY VARCHAR2,
356 x_contact_point_id IN NUMBER,
357 x_contact_point_type IN VARCHAR2,
358 x_status IN VARCHAR2,
359 x_owner_table_name IN VARCHAR2,
360 x_owner_table_id IN NUMBER,
361 x_primary_flag IN VARCHAR2,
362 x_orig_system_reference IN VARCHAR2,
363 x_attribute_category IN VARCHAR2,
364 x_attribute1 IN VARCHAR2,
365 x_attribute2 IN VARCHAR2,
366 x_attribute3 IN VARCHAR2,
367 x_attribute4 IN VARCHAR2,
368 x_attribute5 IN VARCHAR2,
369 x_attribute6 IN VARCHAR2,
370 x_attribute7 IN VARCHAR2,
371 x_attribute8 IN VARCHAR2,
372 x_attribute9 IN VARCHAR2,
373 x_attribute10 IN VARCHAR2,
374 x_attribute11 IN VARCHAR2,
375 x_attribute12 IN VARCHAR2,
376 x_attribute13 IN VARCHAR2,
377 x_attribute14 IN VARCHAR2,
378 x_attribute15 IN VARCHAR2,
379 x_attribute16 IN VARCHAR2,
380 x_attribute17 IN VARCHAR2,
381 x_attribute18 IN VARCHAR2,
382 x_attribute19 IN VARCHAR2,
383 x_attribute20 IN VARCHAR2,
387 x_edi_payment_format IN VARCHAR2,
384 x_edi_transaction_handling IN VARCHAR2,
385 x_edi_id_number IN VARCHAR2,
386 x_edi_payment_method IN VARCHAR2,
388 x_edi_remittance_method IN VARCHAR2,
389 x_edi_remittance_instruction IN VARCHAR2,
390 x_edi_tp_header_id IN NUMBER,
391 x_edi_ece_tp_location_code IN VARCHAR2,
392 x_eft_transmission_program_id IN NUMBER,
393 x_eft_printing_program_id IN NUMBER,
394 x_eft_user_number IN VARCHAR2,
395 x_eft_swift_code IN VARCHAR2,
396 x_email_format IN VARCHAR2,
397 x_email_address IN VARCHAR2,
398 x_phone_calling_calendar IN VARCHAR2,
399 x_last_contact_dt_time IN DATE,
400 x_timezone_id IN NUMBER,
401 x_phone_area_code IN VARCHAR2,
402 x_phone_country_code IN VARCHAR2,
403 x_phone_number IN VARCHAR2,
404 x_phone_extension IN VARCHAR2,
405 x_phone_line_type IN VARCHAR2,
406 x_telex_number IN VARCHAR2,
407 x_web_type IN VARCHAR2,
408 x_url IN VARCHAR2,
409 x_content_source_type IN VARCHAR2,
410 x_raw_phone_number IN VARCHAR2,
411 x_object_version_number IN NUMBER,
412 x_contact_point_purpose IN VARCHAR2,
413 x_primary_by_purpose IN VARCHAR2,
414 x_created_by_module IN VARCHAR2,
415 x_application_id IN NUMBER,
416 x_transposed_phone_number IN VARCHAR2,
417 x_actual_content_source IN VARCHAR2 DEFAULT NULL
418 ) IS
419
420 BEGIN
421
422 UPDATE hz_contact_points SET
423 contact_point_id = DECODE(x_contact_point_id,
424 NULL, contact_point_id,
425 fnd_api.g_miss_num, NULL,
426 x_contact_point_id),
427 contact_point_type = DECODE(x_contact_point_type,
428 NULL, contact_point_type,
429 fnd_api.g_miss_char, NULL,
430 x_contact_point_type),
431 status = DECODE(x_status,
432 NULL, status,
433 fnd_api.g_miss_char, 'A',
434 x_status),
435 owner_table_name = DECODE(x_owner_table_name,
436 NULL, owner_table_name,
437 fnd_api.g_miss_char, NULL,
438 x_owner_table_name),
439 owner_table_id = DECODE(x_owner_table_id,
440 NULL, owner_table_id,
441 fnd_api.g_miss_num, NULL,
442 x_owner_table_id),
443 primary_flag = DECODE(x_primary_flag,
444 NULL, primary_flag,
445 fnd_api.g_miss_char, 'N',
446 x_primary_flag),
447 orig_system_reference = DECODE(x_orig_system_reference,
448 NULL, orig_system_reference,
449 fnd_api.g_miss_char,
450 TO_CHAR(x_contact_point_id),
451 x_orig_system_reference),
452 last_update_date = hz_utility_v2pub.last_update_date,
453 last_updated_by = hz_utility_v2pub.last_updated_by,
454 creation_date = creation_date,
455 created_by = created_by,
456 last_update_login = hz_utility_v2pub.last_update_login,
457 request_id = hz_utility_v2pub.request_id,
458 program_application_id = hz_utility_v2pub.program_application_id,
459 program_id = hz_utility_v2pub.program_id,
460 program_update_date = hz_utility_v2pub.program_update_date,
461 attribute_category = DECODE(x_attribute_category,
462 NULL, attribute_category,
463 fnd_api.g_miss_char, NULL,
464 x_attribute_category),
465 attribute1 = DECODE(x_attribute1,
466 NULL, attribute1,
467 fnd_api.g_miss_char, NULL,
468 x_attribute1),
469 attribute2 = DECODE(x_attribute2,
470 NULL, attribute2,
471 fnd_api.g_miss_char, NULL,
472 x_attribute2),
473 attribute3 = DECODE(x_attribute3,
474 NULL, attribute3,
475 fnd_api.g_miss_char, NULL,
476 x_attribute3),
477 attribute4 = DECODE(x_attribute4,
478 NULL, attribute4,
479 fnd_api.g_miss_char, NULL,
480 x_attribute4),
481 attribute5 = DECODE(x_attribute5,
485 attribute6 = DECODE(x_attribute6,
482 NULL, attribute5,
483 fnd_api.g_miss_char, NULL,
484 x_attribute5),
486 NULL, attribute6,
487 fnd_api.g_miss_char, NULL,
488 x_attribute6),
489 attribute7 = DECODE(x_attribute7,
490 NULL, attribute7,
491 fnd_api.g_miss_char, NULL,
492 x_attribute7),
493 attribute8 = DECODE(x_attribute8,
494 NULL, attribute8,
495 fnd_api.g_miss_char, NULL,
496 x_attribute8),
497 attribute9 = DECODE(x_attribute9,
498 NULL, attribute9,
499 fnd_api.g_miss_char, NULL,
500 x_attribute9),
501 attribute10 = DECODE(x_attribute10,
502 NULL, attribute10,
503 fnd_api.g_miss_char, NULL,
504 x_attribute10),
505 attribute11 = DECODE(x_attribute11,
506 NULL, attribute11,
507 fnd_api.g_miss_char, NULL,
508 x_attribute11),
509 attribute12 = DECODE(x_attribute12,
510 NULL, attribute12,
511 fnd_api.g_miss_char, NULL,
512 x_attribute12),
513 attribute13 = DECODE(x_attribute13,
514 NULL, attribute13,
515 fnd_api.g_miss_char, NULL,
516 x_attribute13),
517 attribute14 = DECODE(x_attribute14,
518 NULL, attribute14,
519 fnd_api.g_miss_char, NULL,
520 x_attribute14),
521 attribute15 = DECODE(x_attribute15,
522 NULL, attribute15,
523 fnd_api.g_miss_char, NULL,
524 x_attribute15),
525 attribute16 = DECODE(x_attribute16,
526 NULL, attribute16,
527 fnd_api.g_miss_char, NULL,
528 x_attribute16),
529 attribute17 = DECODE(x_attribute17,
530 NULL, attribute17,
531 fnd_api.g_miss_char, NULL,
532 x_attribute17),
533 attribute18 = DECODE(x_attribute18,
534 NULL, attribute18,
535 fnd_api.g_miss_char, NULL,
536 x_attribute18),
537 attribute19 = DECODE(x_attribute19,
538 NULL, attribute19,
539 fnd_api.g_miss_char, NULL,
540 x_attribute19),
541 attribute20 = DECODE(x_attribute20,
542 NULL, attribute20,
543 fnd_api.g_miss_char, NULL,
544 x_attribute20),
545 edi_transaction_handling = DECODE(x_edi_transaction_handling,
546 NULL, edi_transaction_handling,
547 fnd_api.g_miss_char, NULL,
548 x_edi_transaction_handling),
549 edi_id_number = DECODE(x_edi_id_number,
550 NULL, edi_id_number,
551 fnd_api.g_miss_char, NULL,
552 x_edi_id_number),
553 edi_payment_method = DECODE(x_edi_payment_method,
554 NULL, edi_payment_method,
555 fnd_api.g_miss_char, NULL,
556 x_edi_payment_method),
557 edi_payment_format = DECODE(x_edi_payment_format,
558 NULL, edi_payment_format,
559 fnd_api.g_miss_char, NULL,
560 x_edi_payment_format),
561 edi_remittance_method = DECODE(x_edi_remittance_method,
562 NULL, edi_remittance_method,
563 fnd_api.g_miss_char, NULL,
564 x_edi_remittance_method),
565 edi_remittance_instruction= DECODE(x_edi_remittance_instruction,
566 NULL, edi_remittance_instruction,
567 fnd_api.g_miss_char, NULL,
568 x_edi_remittance_instruction),
569 edi_tp_header_id = DECODE(x_edi_tp_header_id,
570 NULL, edi_tp_header_id,
571 fnd_api.g_miss_num, NULL,
572 x_edi_tp_header_id),
573 edi_ece_tp_location_code = DECODE(x_edi_ece_tp_location_code,
574 NULL, edi_ece_tp_location_code,
575 fnd_api.g_miss_char, NULL,
579 fnd_api.g_miss_num, NULL,
576 x_edi_ece_tp_location_code),
577 eft_transmission_program_id=DECODE(x_eft_transmission_program_id,
578 NULL, eft_transmission_program_id,
580 x_eft_transmission_program_id),
581 eft_printing_program_id = DECODE(x_eft_printing_program_id,
582 NULL, eft_printing_program_id,
583 fnd_api.g_miss_num, NULL,
584 x_eft_printing_program_id),
585 eft_user_number = DECODE(x_eft_user_number,
586 NULL, eft_user_number,
587 fnd_api.g_miss_char, NULL,
588 x_eft_user_number),
589 eft_swift_code = DECODE(x_eft_swift_code,
590 NULL, eft_swift_code,
591 fnd_api.g_miss_char, NULL,
592 x_eft_swift_code),
593 email_format = DECODE(x_email_format,
594 NULL, email_format,
595 fnd_api.g_miss_char, NULL,
596 x_email_format),
597 --Bug 4355133
598 email_address = SUBSTRB(DECODE(x_email_address,
599 NULL, email_address,
600 fnd_api.g_miss_char, NULL,
601 x_email_address),1,320),
602 phone_calling_calendar = DECODE(x_phone_calling_calendar,
603 NULL, phone_calling_calendar,
604 fnd_api.g_miss_char, NULL,
605 x_phone_calling_calendar),
606 last_contact_dt_time = DECODE(x_last_contact_dt_time,
607 NULL, last_contact_dt_time,
608 fnd_api.g_miss_date, NULL,
609 x_last_contact_dt_time),
610 timezone_id = DECODE(x_timezone_id,
611 NULL, timezone_id,
612 fnd_api.g_miss_num, NULL,
613 x_timezone_id),
614 phone_area_code = DECODE(x_phone_area_code,
615 NULL, phone_area_code,
616 fnd_api.g_miss_char, NULL,
617 x_phone_area_code),
618 phone_country_code = DECODE(x_phone_country_code,
619 NULL, phone_country_code,
620 fnd_api.g_miss_char, NULL,
621 x_phone_country_code),
622 phone_number = DECODE(x_phone_number,
623 NULL, phone_number,
624 fnd_api.g_miss_char, NULL,
625 x_phone_number),
626 phone_extension = DECODE(x_phone_extension,
627 NULL, phone_extension,
628 fnd_api.g_miss_char, NULL,
629 x_phone_extension),
630 phone_line_type = DECODE(x_phone_line_type,
631 NULL, phone_line_type,
632 fnd_api.g_miss_char, NULL,
633 x_phone_line_type),
634 telex_number = DECODE(x_telex_number,
635 NULL, telex_number,
636 fnd_api.g_miss_char, NULL,
637 x_telex_number),
638 web_type = DECODE(x_web_type,
639 NULL, web_type,
640 fnd_api.g_miss_char, NULL,
641 x_web_type),
642 url = DECODE(x_url,
643 NULL, url,
644 fnd_api.g_miss_char, NULL,
645 x_url),
646 content_source_type = DECODE(x_content_source_type,
647 NULL, content_source_type,
648 fnd_api.g_miss_char, NULL,
649 x_content_source_type),
650 raw_phone_number = DECODE(x_raw_phone_number,
651 NULL, raw_phone_number,
652 fnd_api.g_miss_char, NULL,
653 x_raw_phone_number),
654 object_version_number = DECODE(x_object_version_number,
655 NULL, object_version_number,
656 fnd_api.g_miss_num, NULL,
657 x_object_version_number),
658 contact_point_purpose = DECODE(x_contact_point_purpose,
659 NULL, contact_point_purpose,
660 fnd_api.g_miss_char, NULL,
661 x_contact_point_purpose),
662 primary_by_purpose = DECODE(x_primary_by_purpose,
663 NULL, primary_by_purpose,
664 fnd_api.g_miss_char, 'N',
665 x_primary_by_purpose),
666 created_by_module = DECODE(x_created_by_module,
667 NULL, created_by_module,
671 NULL, application_id,
668 fnd_api.g_miss_char, NULL,
669 x_created_by_module),
670 application_id = DECODE(x_application_id,
672 fnd_api.g_miss_num, NULL,
673 x_application_id),
674 transposed_phone_number = DECODE(x_transposed_phone_number,
675 NULL, transposed_phone_number,
676 fnd_api.g_miss_char, NULL,
677 x_transposed_phone_number),
678 actual_content_source = DECODE(x_actual_content_source,
679 NULL, actual_content_source,
680 fnd_api.g_miss_char, NULL,
681 x_actual_content_source)
682 WHERE ROWID = x_rowid;
683
684 IF (SQL%NOTFOUND) THEN
685 RAISE NO_DATA_FOUND;
686 END IF;
687
688 END update_row;
689
690 PROCEDURE lock_row (
691 x_rowid IN OUT NOCOPY VARCHAR2,
692 x_contact_point_id IN NUMBER,
693 x_contact_point_type IN VARCHAR2,
694 x_status IN VARCHAR2,
695 x_owner_table_name IN VARCHAR2,
696 x_owner_table_id IN NUMBER,
697 x_primary_flag IN VARCHAR2,
698 x_orig_system_reference IN VARCHAR2,
699 x_last_update_date IN DATE,
700 x_last_updated_by IN NUMBER,
701 x_creation_date IN DATE,
702 x_created_by IN NUMBER,
703 x_last_update_login IN NUMBER,
704 x_request_id IN NUMBER,
705 x_program_application_id IN NUMBER,
706 x_program_id IN NUMBER,
707 x_program_update_date IN DATE,
708 x_attribute_category IN VARCHAR2,
709 x_attribute1 IN VARCHAR2,
710 x_attribute2 IN VARCHAR2,
711 x_attribute3 IN VARCHAR2,
712 x_attribute4 IN VARCHAR2,
713 x_attribute5 IN VARCHAR2,
714 x_attribute6 IN VARCHAR2,
715 x_attribute7 IN VARCHAR2,
716 x_attribute8 IN VARCHAR2,
717 x_attribute9 IN VARCHAR2,
718 x_attribute10 IN VARCHAR2,
719 x_attribute11 IN VARCHAR2,
720 x_attribute12 IN VARCHAR2,
721 x_attribute13 IN VARCHAR2,
722 x_attribute14 IN VARCHAR2,
723 x_attribute15 IN VARCHAR2,
724 x_attribute16 IN VARCHAR2,
725 x_attribute17 IN VARCHAR2,
726 x_attribute18 IN VARCHAR2,
727 x_attribute19 IN VARCHAR2,
728 x_attribute20 IN VARCHAR2,
729 x_edi_transaction_handling IN VARCHAR2,
730 x_edi_id_number IN VARCHAR2,
731 x_edi_payment_method IN VARCHAR2,
732 x_edi_payment_format IN VARCHAR2,
733 x_edi_remittance_method IN VARCHAR2,
734 x_edi_remittance_instruction IN VARCHAR2,
735 x_edi_tp_header_id IN NUMBER,
736 x_edi_ece_tp_location_code IN VARCHAR2,
737 x_eft_transmission_program_id IN NUMBER,
738 x_eft_printing_program_id IN NUMBER,
739 x_eft_user_number IN VARCHAR2,
740 x_eft_swift_code IN VARCHAR2,
741 x_email_format IN VARCHAR2,
742 x_email_address IN VARCHAR2,
743 x_phone_calling_calendar IN VARCHAR2,
744 x_last_contact_dt_time IN DATE,
745 x_timezone_id IN NUMBER,
746 x_phone_area_code IN VARCHAR2,
747 x_phone_country_code IN VARCHAR2,
748 x_phone_number IN VARCHAR2,
749 x_phone_extension IN VARCHAR2,
750 x_phone_line_type IN VARCHAR2,
751 x_telex_number IN VARCHAR2,
752 x_web_type IN VARCHAR2,
753 x_url IN VARCHAR2,
754 x_content_source_type IN VARCHAR2,
755 x_raw_phone_number IN VARCHAR2,
756 x_object_version_number IN NUMBER,
757 x_contact_point_purpose IN VARCHAR2,
758 x_primary_by_purpose IN VARCHAR2,
759 x_created_by_module IN VARCHAR2,
760 x_application_id IN NUMBER,
761 x_transposed_phone_number IN VARCHAR2,
762 x_actual_content_source IN VARCHAR2 DEFAULT NULL
763 ) IS
764
765 CURSOR c IS
766 SELECT *
767 FROM hz_contact_points
768 WHERE ROWID = x_rowid
769 FOR UPDATE NOWAIT;
770 recinfo C%ROWTYPE;
771
772 BEGIN
773
774 OPEN c;
775 FETCH c INTO recinfo;
776 IF (c%NOTFOUND) THEN
780 END IF;
777 CLOSE c;
778 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
779 app_exception.raise_exception;
781 CLOSE c;
782
783 IF (((recinfo.contact_point_id = x_contact_point_id)
784 OR ((recinfo.contact_point_id IS NULL)
785 AND (x_contact_point_id IS NULL)))
786 AND ((recinfo.contact_point_type = x_contact_point_type)
787 OR ((recinfo.contact_point_type IS NULL)
788 AND (x_contact_point_type IS NULL)))
789 AND ((recinfo.status = x_status)
790 OR ((recinfo.status IS NULL)
791 AND (x_status IS NULL)))
792 AND ((recinfo.owner_table_name = x_owner_table_name)
793 OR ((recinfo.owner_table_name IS NULL)
794 AND (x_owner_table_name IS NULL)))
795 AND ((recinfo.owner_table_id = x_owner_table_id)
796 OR ((recinfo.owner_table_id IS NULL)
797 AND (x_owner_table_id IS NULL)))
798 AND ((recinfo.primary_flag = x_primary_flag)
799 OR ((recinfo.primary_flag IS NULL)
800 AND (x_primary_flag IS NULL)))
801 AND ((recinfo.orig_system_reference = x_orig_system_reference)
802 OR ((recinfo.orig_system_reference IS NULL)
803 AND (x_orig_system_reference IS NULL)))
804 AND ((recinfo.last_update_date = x_last_update_date)
805 OR ((recinfo.last_update_date IS NULL)
806 AND (x_last_update_date IS NULL)))
807 AND ((recinfo.last_updated_by = x_last_updated_by)
808 OR ((recinfo.last_updated_by IS NULL)
809 AND (x_last_updated_by IS NULL)))
810 AND ((recinfo.creation_date = x_creation_date)
811 OR ((recinfo.creation_date IS NULL)
812 AND (x_creation_date IS NULL)))
813 AND ((recinfo.created_by = x_created_by)
814 OR ((recinfo.created_by IS NULL)
815 AND (x_created_by IS NULL)))
816 AND ((recinfo.last_update_login = x_last_update_login)
817 OR ((recinfo.last_update_login IS NULL)
818 AND (x_last_update_login IS NULL)))
819 AND ((recinfo.request_id = x_request_id)
820 OR ((recinfo.request_id IS NULL)
821 AND (x_request_id IS NULL)))
822 AND ((recinfo.program_application_id = x_program_application_id)
823 OR ((recinfo.program_application_id IS NULL)
824 AND (x_program_application_id IS NULL)))
825 AND ((recinfo.program_id = x_program_id)
826 OR ((recinfo.program_id IS NULL)
827 AND (x_program_id IS NULL)))
828 AND ((recinfo.program_update_date = x_program_update_date)
829 OR ((recinfo.program_update_date IS NULL)
830 AND (x_program_update_date IS NULL)))
831 AND ((recinfo.attribute_category = x_attribute_category)
832 OR ((recinfo.attribute_category IS NULL)
833 AND (x_attribute_category IS NULL)))
834 AND ((recinfo.attribute1 = x_attribute1)
835 OR ((recinfo.attribute1 IS NULL)
836 AND (x_attribute1 IS NULL)))
837 AND ((recinfo.attribute2 = x_attribute2)
838 OR ((recinfo.attribute2 IS NULL)
839 AND (x_attribute2 IS NULL)))
840 AND ((recinfo.attribute3 = x_attribute3)
841 OR ((recinfo.attribute3 IS NULL)
842 AND (x_attribute3 IS NULL)))
843 AND ((recinfo.attribute4 = x_attribute4)
844 OR ((recinfo.attribute4 IS NULL)
845 AND (x_attribute4 IS NULL)))
846 AND ((recinfo.attribute5 = x_attribute5)
847 OR ((recinfo.attribute5 IS NULL)
848 AND (x_attribute5 IS NULL)))
849 AND ((recinfo.attribute6 = x_attribute6)
850 OR ((recinfo.attribute6 IS NULL)
851 AND (x_attribute6 IS NULL)))
852 AND ((recinfo.attribute7 = x_attribute7)
853 OR ((recinfo.attribute7 IS NULL)
854 AND (x_attribute7 IS NULL)))
855 AND ((recinfo.attribute8 = x_attribute8)
856 OR ((recinfo.attribute8 IS NULL)
857 AND (x_attribute8 IS NULL)))
858 AND ((recinfo.attribute9 = x_attribute9)
859 OR ((recinfo.attribute9 IS NULL)
860 AND (x_attribute9 IS NULL)))
861 AND ((recinfo.attribute10 = x_attribute10)
862 OR ((recinfo.attribute10 IS NULL)
863 AND (x_attribute10 IS NULL)))
864 AND ((recinfo.attribute11 = x_attribute11)
865 OR ((recinfo.attribute11 IS NULL)
866 AND (x_attribute11 IS NULL)))
867 AND ((recinfo.attribute12 = x_attribute12)
868 OR ((recinfo.attribute12 IS NULL)
869 AND (x_attribute12 IS NULL)))
870 AND ((recinfo.attribute13 = x_attribute13)
871 OR ((recinfo.attribute13 IS NULL)
872 AND (x_attribute13 IS NULL)))
873 AND ((recinfo.attribute14 = x_attribute14)
874 OR ((recinfo.attribute14 IS NULL)
875 AND (x_attribute14 IS NULL)))
876 AND ((recinfo.attribute15 = x_attribute15)
877 OR ((recinfo.attribute15 IS NULL)
878 AND (x_attribute15 IS NULL)))
879 AND ((recinfo.attribute16 = x_attribute16)
880 OR ((recinfo.attribute16 IS NULL)
881 AND (x_attribute16 IS NULL)))
882 AND ((recinfo.attribute17 = x_attribute17)
883 OR ((recinfo.attribute17 IS NULL)
884 AND (x_attribute17 IS NULL)))
885 AND ((recinfo.attribute18 = x_attribute18)
886 OR ((recinfo.attribute18 IS NULL)
887 AND (x_attribute18 IS NULL)))
888 AND ((recinfo.attribute19 = x_attribute19)
889 OR ((recinfo.attribute19 IS NULL)
890 AND (x_attribute19 IS NULL)))
894 AND ((recinfo.edi_transaction_handling = x_edi_transaction_handling)
891 AND ((recinfo.attribute20 = x_attribute20)
892 OR ((recinfo.attribute20 IS NULL)
893 AND (x_attribute20 IS NULL)))
895 OR ((recinfo.edi_transaction_handling IS NULL)
896 AND (x_edi_transaction_handling IS NULL)))
897 AND ((recinfo.edi_id_number = x_edi_id_number)
898 OR ((recinfo.edi_id_number IS NULL)
899 AND (x_edi_id_number IS NULL)))
900 AND ((recinfo.edi_payment_method = x_edi_payment_method)
901 OR ((recinfo.edi_payment_method IS NULL)
902 AND (x_edi_payment_method IS NULL)))
903 AND ((recinfo.edi_payment_format = x_edi_payment_format)
904 OR ((recinfo.edi_payment_format IS NULL)
905 AND (x_edi_payment_format IS NULL)))
906 AND ((recinfo.edi_remittance_method = x_edi_remittance_method)
907 OR ((recinfo.edi_remittance_method IS NULL)
908 AND (x_edi_remittance_method IS NULL)))
909 AND ((recinfo.edi_remittance_instruction
910 = x_edi_remittance_instruction)
911 OR ((recinfo.edi_remittance_instruction IS NULL)
912 AND (x_edi_remittance_instruction IS NULL)))
913 AND ((recinfo.edi_tp_header_id = x_edi_tp_header_id)
914 OR ((recinfo.edi_tp_header_id IS NULL)
915 AND (x_edi_tp_header_id IS NULL)))
916 AND ((recinfo.edi_ece_tp_location_code = x_edi_ece_tp_location_code)
917 OR ((recinfo.edi_ece_tp_location_code IS NULL)
918 AND (x_edi_ece_tp_location_code IS NULL)))
919 AND ((recinfo.eft_transmission_program_id=x_eft_transmission_program_id)
920 OR ((recinfo.eft_transmission_program_id IS NULL)
921 AND (x_eft_transmission_program_id IS NULL)))
922 AND ((recinfo.eft_printing_program_id = x_eft_printing_program_id)
923 OR ((recinfo.eft_printing_program_id IS NULL)
924 AND (x_eft_printing_program_id IS NULL)))
925 AND ((recinfo.eft_user_number = x_eft_user_number)
926 OR ((recinfo.eft_user_number IS NULL)
927 AND (x_eft_user_number IS NULL)))
928 AND ((recinfo.eft_swift_code = x_eft_swift_code)
929 OR ((recinfo.eft_swift_code IS NULL)
930 AND (x_eft_swift_code IS NULL)))
931 AND ((recinfo.email_format = x_email_format)
932 OR ((recinfo.email_format IS NULL)
933 AND (x_email_format IS NULL)))
934 AND ((recinfo.email_address = x_email_address)
935 OR ((recinfo.email_address IS NULL)
936 AND (x_email_address IS NULL)))
937 AND ((recinfo.phone_calling_calendar = x_phone_calling_calendar)
938 OR ((recinfo.phone_calling_calendar IS NULL)
939 AND (x_phone_calling_calendar IS NULL)))
940 AND ((recinfo.last_contact_dt_time = x_last_contact_dt_time)
941 OR ((recinfo.last_contact_dt_time IS NULL)
942 AND (x_last_contact_dt_time IS NULL)))
943 AND ((recinfo.timezone_id = x_timezone_id)
944 OR ((recinfo.timezone_id IS NULL)
945 AND (x_timezone_id IS NULL)))
946 AND ((recinfo.phone_area_code = x_phone_area_code)
947 OR ((recinfo.phone_area_code IS NULL)
948 AND (x_phone_area_code IS NULL)))
949 AND ((recinfo.phone_country_code = x_phone_country_code)
950 OR ((recinfo.phone_country_code IS NULL)
951 AND (x_phone_country_code IS NULL)))
952 AND ((recinfo.phone_number = x_phone_number)
953 OR ((recinfo.phone_number IS NULL)
954 AND (x_phone_number IS NULL)))
955 AND ((recinfo.phone_extension = x_phone_extension)
956 OR ((recinfo.phone_extension IS NULL)
957 AND (x_phone_extension IS NULL)))
958 AND ((recinfo.phone_line_type = x_phone_line_type)
959 OR ((recinfo.phone_line_type IS NULL)
960 AND (x_phone_line_type IS NULL)))
961 AND ((recinfo.telex_number = x_telex_number)
962 OR ((recinfo.telex_number IS NULL)
963 AND (x_telex_number IS NULL)))
964 AND ((recinfo.web_type = x_web_type)
965 OR ((recinfo.web_type IS NULL)
966 AND (x_web_type IS NULL)))
967 AND ((recinfo.url = x_url)
968 OR ((recinfo.url IS NULL)
969 AND (x_url IS NULL)))
970 AND ((recinfo.content_source_type = x_content_source_type)
971 OR ((recinfo.content_source_type IS NULL)
972 AND (x_content_source_type IS NULL)))
973 AND ((recinfo.raw_phone_number = x_raw_phone_number)
974 OR ((recinfo.raw_phone_number IS NULL)
975 AND (x_raw_phone_number IS NULL)))
976 AND ((recinfo.object_version_number = x_object_version_number)
977 OR ((recinfo.object_version_number IS NULL)
978 AND (x_object_version_number IS NULL)))
979 AND ((recinfo.contact_point_purpose = x_contact_point_purpose)
980 OR ((recinfo.contact_point_purpose IS NULL)
981 AND (x_contact_point_purpose IS NULL)))
982 AND ((recinfo.primary_by_purpose = x_primary_by_purpose)
983 OR ((recinfo.primary_by_purpose IS NULL)
984 AND (x_primary_by_purpose IS NULL)))
985 AND ((recinfo.created_by_module = x_created_by_module)
986 OR ((recinfo.created_by_module IS NULL)
987 AND (x_created_by_module IS NULL)))
988 AND ((recinfo.application_id = x_application_id)
989 OR ((recinfo.application_id IS NULL)
990 AND (x_application_id IS NULL)))
991 AND ((recinfo.transposed_phone_number = x_transposed_phone_number)
992 OR ((recinfo.transposed_phone_number IS NULL)
993 AND (x_transposed_phone_number IS NULL)))
997 )
994 AND ((recinfo.content_source_type = x_content_source_type)
995 OR ((recinfo.content_source_type IS NULL)
996 AND (x_content_source_type IS NULL)))
998 THEN
999 RETURN;
1000 ELSE
1001 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1002 app_exception.raise_exception;
1003 END IF;
1004 END lock_row;
1005
1006 PROCEDURE select_row (
1007 x_contact_point_id IN OUT NOCOPY NUMBER,
1008 x_contact_point_type OUT NOCOPY VARCHAR2,
1009 x_status OUT NOCOPY VARCHAR2,
1010 x_owner_table_name OUT NOCOPY VARCHAR2,
1011 x_owner_table_id OUT NOCOPY NUMBER,
1012 x_primary_flag OUT NOCOPY VARCHAR2,
1013 x_orig_system_reference OUT NOCOPY VARCHAR2,
1014 x_attribute_category OUT NOCOPY VARCHAR2,
1015 x_attribute1 OUT NOCOPY VARCHAR2,
1016 x_attribute2 OUT NOCOPY VARCHAR2,
1017 x_attribute3 OUT NOCOPY VARCHAR2,
1018 x_attribute4 OUT NOCOPY VARCHAR2,
1019 x_attribute5 OUT NOCOPY VARCHAR2,
1020 x_attribute6 OUT NOCOPY VARCHAR2,
1021 x_attribute7 OUT NOCOPY VARCHAR2,
1022 x_attribute8 OUT NOCOPY VARCHAR2,
1023 x_attribute9 OUT NOCOPY VARCHAR2,
1024 x_attribute10 OUT NOCOPY VARCHAR2,
1025 x_attribute11 OUT NOCOPY VARCHAR2,
1026 x_attribute12 OUT NOCOPY VARCHAR2,
1027 x_attribute13 OUT NOCOPY VARCHAR2,
1028 x_attribute14 OUT NOCOPY VARCHAR2,
1029 x_attribute15 OUT NOCOPY VARCHAR2,
1030 x_attribute16 OUT NOCOPY VARCHAR2,
1031 x_attribute17 OUT NOCOPY VARCHAR2,
1032 x_attribute18 OUT NOCOPY VARCHAR2,
1033 x_attribute19 OUT NOCOPY VARCHAR2,
1034 x_attribute20 OUT NOCOPY VARCHAR2,
1035 x_edi_transaction_handling OUT NOCOPY VARCHAR2,
1036 x_edi_id_number OUT NOCOPY VARCHAR2,
1037 x_edi_payment_method OUT NOCOPY VARCHAR2,
1038 x_edi_payment_format OUT NOCOPY VARCHAR2,
1039 x_edi_remittance_method OUT NOCOPY VARCHAR2,
1040 x_edi_remittance_instruction OUT NOCOPY VARCHAR2,
1041 x_edi_tp_header_id OUT NOCOPY NUMBER,
1042 x_edi_ece_tp_location_code OUT NOCOPY VARCHAR2,
1043 x_eft_transmission_program_id OUT NOCOPY NUMBER,
1044 x_eft_printing_program_id OUT NOCOPY NUMBER,
1045 x_eft_user_number OUT NOCOPY VARCHAR2,
1046 x_eft_swift_code OUT NOCOPY VARCHAR2,
1047 x_email_format OUT NOCOPY VARCHAR2,
1048 x_email_address OUT NOCOPY VARCHAR2,
1049 x_phone_calling_calendar OUT NOCOPY VARCHAR2,
1050 x_last_contact_dt_time OUT NOCOPY DATE,
1051 x_timezone_id OUT NOCOPY NUMBER,
1052 x_phone_area_code OUT NOCOPY VARCHAR2,
1053 x_phone_country_code OUT NOCOPY VARCHAR2,
1054 x_phone_number OUT NOCOPY VARCHAR2,
1055 x_phone_extension OUT NOCOPY VARCHAR2,
1056 x_phone_line_type OUT NOCOPY VARCHAR2,
1057 x_telex_number OUT NOCOPY VARCHAR2,
1058 x_web_type OUT NOCOPY VARCHAR2,
1059 x_url OUT NOCOPY VARCHAR2,
1060 x_content_source_type OUT NOCOPY VARCHAR2,
1061 x_raw_phone_number OUT NOCOPY VARCHAR2,
1062 x_contact_point_purpose OUT NOCOPY VARCHAR2,
1063 x_primary_by_purpose OUT NOCOPY VARCHAR2,
1064 x_created_by_module OUT NOCOPY VARCHAR2,
1065 x_application_id OUT NOCOPY NUMBER,
1066 x_transposed_phone_number OUT NOCOPY VARCHAR2,
1067 x_actual_content_source OUT NOCOPY VARCHAR2
1068 ) IS
1069
1070 CURSOR c_cp IS
1071 SELECT NVL(contact_point_id, fnd_api.g_miss_num),
1072 NVL(contact_point_type, fnd_api.g_miss_char),
1073 NVL(status, fnd_api.g_miss_char),
1074 NVL(owner_table_name, fnd_api.g_miss_char),
1075 NVL(owner_table_id, fnd_api.g_miss_num),
1076 NVL(primary_flag, fnd_api.g_miss_char),
1077 NVL(orig_system_reference, fnd_api.g_miss_char),
1078 NVL(attribute_category, fnd_api.g_miss_char),
1079 NVL(attribute1, fnd_api.g_miss_char),
1080 NVL(attribute2, fnd_api.g_miss_char),
1081 NVL(attribute3, fnd_api.g_miss_char),
1082 NVL(attribute4, fnd_api.g_miss_char),
1083 NVL(attribute5, fnd_api.g_miss_char),
1084 NVL(attribute6, fnd_api.g_miss_char),
1085 NVL(attribute7, fnd_api.g_miss_char),
1086 NVL(attribute8, fnd_api.g_miss_char),
1087 NVL(attribute9, fnd_api.g_miss_char),
1088 NVL(attribute10, fnd_api.g_miss_char),
1089 NVL(attribute11, fnd_api.g_miss_char),
1090 NVL(attribute12, fnd_api.g_miss_char),
1091 NVL(attribute13, fnd_api.g_miss_char),
1092 NVL(attribute14, fnd_api.g_miss_char),
1093 NVL(attribute15, fnd_api.g_miss_char),
1094 NVL(attribute16, fnd_api.g_miss_char),
1098 NVL(attribute20, fnd_api.g_miss_char),
1095 NVL(attribute17, fnd_api.g_miss_char),
1096 NVL(attribute18, fnd_api.g_miss_char),
1097 NVL(attribute19, fnd_api.g_miss_char),
1099 NVL(edi_transaction_handling, fnd_api.g_miss_char),
1100 NVL(edi_id_number, fnd_api.g_miss_char),
1101 NVL(edi_payment_method, fnd_api.g_miss_char),
1102 NVL(edi_payment_format, fnd_api.g_miss_char),
1103 NVL(edi_remittance_method, fnd_api.g_miss_char),
1104 NVL(edi_remittance_instruction, fnd_api.g_miss_char),
1105 NVL(edi_tp_header_id, fnd_api.g_miss_num),
1106 NVL(edi_ece_tp_location_code, fnd_api.g_miss_char),
1107 NVL(x_eft_transmission_program_id, fnd_api.g_miss_num),
1108 NVL(x_eft_printing_program_id, fnd_api.g_miss_num),
1109 NVL(x_eft_user_number, fnd_api.g_miss_char),
1110 NVL(x_eft_swift_code, fnd_api.g_miss_char),
1111 NVL(email_format, fnd_api.g_miss_char),
1112 NVL(email_address, fnd_api.g_miss_char),
1113 NVL(phone_calling_calendar, fnd_api.g_miss_char),
1114 NVL(last_contact_dt_time, fnd_api.g_miss_date),
1115 NVL(timezone_id, fnd_api.g_miss_num),
1116 NVL(phone_area_code, fnd_api.g_miss_char),
1117 NVL(phone_country_code, fnd_api.g_miss_char),
1118 NVL(phone_number, fnd_api.g_miss_char),
1119 NVL(phone_extension, fnd_api.g_miss_char),
1120 NVL(phone_line_type, fnd_api.g_miss_char),
1121 NVL(telex_number, fnd_api.g_miss_char),
1122 NVL(web_type, fnd_api.g_miss_char),
1123 NVL(url, fnd_api.g_miss_char),
1124 NVL(content_source_type, fnd_api.g_miss_char),
1125 NVL(raw_phone_number, fnd_api.g_miss_char),
1126 NVL(contact_point_purpose, fnd_api.g_miss_char),
1127 NVL(primary_by_purpose, fnd_api.g_miss_char),
1128 NVL(created_by_module, fnd_api.g_miss_char),
1129 NVL(application_id, fnd_api.g_miss_num),
1130 NVL(transposed_phone_number, fnd_api.g_miss_char),
1131 NVL(actual_content_source, fnd_api.g_miss_char)
1132 FROM hz_contact_points
1133 WHERE contact_point_id = x_contact_point_id;
1134 BEGIN
1135 OPEN c_cp;
1136 FETCH c_cp
1137 INTO x_contact_point_id,
1138 x_contact_point_type,
1139 x_status,
1140 x_owner_table_name,
1141 x_owner_table_id,
1142 x_primary_flag,
1143 x_orig_system_reference,
1144 x_attribute_category,
1145 x_attribute1,
1146 x_attribute2,
1147 x_attribute3,
1148 x_attribute4,
1149 x_attribute5,
1150 x_attribute6,
1151 x_attribute7,
1152 x_attribute8,
1153 x_attribute9,
1154 x_attribute10,
1155 x_attribute11,
1156 x_attribute12,
1157 x_attribute13,
1158 x_attribute14,
1159 x_attribute15,
1160 x_attribute16,
1161 x_attribute17,
1162 x_attribute18,
1163 x_attribute19,
1164 x_attribute20,
1165 x_edi_transaction_handling,
1166 x_edi_id_number,
1167 x_edi_payment_method,
1168 x_edi_payment_format,
1169 x_edi_remittance_method,
1170 x_edi_remittance_instruction,
1171 x_edi_tp_header_id,
1172 x_edi_ece_tp_location_code,
1173 x_eft_transmission_program_id,
1174 x_eft_printing_program_id,
1175 x_eft_user_number,
1176 x_eft_swift_code,
1177 x_email_format,
1178 x_email_address,
1179 x_phone_calling_calendar,
1180 x_last_contact_dt_time,
1181 x_timezone_id,
1182 x_phone_area_code,
1183 x_phone_country_code,
1184 x_phone_number,
1185 x_phone_extension,
1186 x_phone_line_type,
1187 x_telex_number,
1188 x_web_type,
1189 x_url,
1190 x_content_source_type,
1191 x_raw_phone_number,
1192 x_contact_point_purpose,
1193 x_primary_by_purpose,
1194 x_created_by_module,
1195 x_application_id,
1196 x_transposed_phone_number,
1197 x_actual_content_source;
1198
1199 IF c_cp%NOTFOUND THEN
1200 CLOSE c_cp;
1201 fnd_message.set_name('AR', 'HZ_API_NO_RECORD');
1202 fnd_message.set_token('RECORD', 'contact_point_rec');
1203 fnd_message.set_token('VALUE', TO_CHAR(x_contact_point_id));
1204 fnd_msg_pub.add;
1205 RAISE fnd_api.g_exc_error;
1206 ELSE
1207 CLOSE c_cp;
1208 END IF;
1209
1210 END select_row;
1211
1212 PROCEDURE delete_row (x_contact_point_id IN NUMBER) IS
1213 BEGIN
1214
1215 DELETE FROM hz_contact_points
1216 WHERE contact_point_id = x_contact_point_id;
1217
1218 IF (SQL%NOTFOUND) THEN
1219 RAISE NO_DATA_FOUND;
1220 END IF;
1221
1222 END delete_row;
1223
1224 END hz_contact_points_pkg;